Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 56880
Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами

Извлечение уникальных значений формулами

Предположим, что у Вас есть вот такой файл по продажам региональных менеджеров:




Из него Вам необходимо извлечь все уникальные фамилии продавцов. Т.е. должен получиться такой список:
 

Козлов
Смирнов
Кузнецов
Сидоров
Петров
 Иванов

 


ПРОБЛЕМА: Как формулами извлечь уникальные значения?

РЕШЕНИЕ: Формула массива (вводится нажатием Ctrl+Shift+Enter):

 

Code
=ИНДЕКС(B$2:B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ(B$2:B$16;B$2:B$16;0)=СТРОКА(A$1:A$15);СТРОКА(A$1:A$15));СТРОКА(A1)))


В английской версии:

 

Code
=INDEX(B$2:B$16,LARGE(IF(MATCH(B$2:B$16,B$2:B$16,0)=ROW(A$1:A$15),ROW(A$1:A$15)),ROW(A1)))

 


КАК ЭТО РАБОТАЕТ: Функция ПОИСКПОЗ, сравнивающая два массива возвращает ИСТИНА только в том случае, если вхождение искомого значения в массив является первым.

В результате работы ПОИСКПОЗ мы получаем массив: {ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ и т.д.}.
С помощью функции ЕСЛИ мы сравниваем получившийся массив с массивом {1:2:3:4:5:6:7:8 и т.д.}, полученном в результате работы функции СТРОКА

с заданным диапазоном и в случае если ПОИСКПОЗ вернул ИСТИНА получаем номер строки вхождения.


Собственно говоря задача решена. Теперь остаётся только оформить итог списком. Для этого используем функцию НАИБОЛЬШИЙ, которая создаст ряд

сначала из чисел, потом из значений ЛОЖЬ и функцию ИНДЕКС, которая вернёт нам необходимые текстовые значения из соответствующего массива. В жёлтых ячейках итог:


МИНУСЫ: Формулы массивов сильно замедляют скорость пересчёта листа.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

ПРИМЕЧАНИЯ: Для устранения значения ошибки можно использовать проверку на ошибку согласно Вашей версии Excel или использовать Условное Форматирование



 

 

Категория: Приёмы работы с формулами | Добавил: Serge_007 (24.02.2011)
Просмотров: 62667 | Комментарии: 31 | Рейтинг: 4.9/12


Всего комментариев: 301 2 »
Спам-сообщение скрыто. Показать
0   Спам
1    Евгений   (03.11.2011 21:00) [ Материал]
   А если необходимо просто найти последнее значение по столбцу или строке ? Например в регулярно обновляющемся списке.

Спам-сообщение скрыто. Показать
0   Спам
2    Serge_007   (03.11.2011 21:14) [ Материал]
   Тогда Вам на форум smile

Спам-сообщение скрыто. Показать
+1   Спам
3    Пытливый   (21.04.2012 13:28) [ Материал]
   Блин, кладезь информации
Спасибо за сайт, этот способ мне нравиться, раньше я с доп. столбцом делал

+1   Спам
4    Staniiislav   (07.06.2012 14:57) [ Материал]
   Если Вы не против, немного переделал ваше решение:
Код
=ИНДЕКС(ДВССЫЛ("$B$2:$B$"&СЧЁТЗ($B$2:$B$100));НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ(ДВССЫЛ("$B$2:$B$"&СЧЁТЗ($B$2:$B$100));ДВССЫЛ("$B$2:$B$"&СЧЁТЗ($B$2:$B$100));0)
=СТРОКА(ДВССЫЛ("$A$1:$A$"&СЧЁТЗ($B$2:$B$100)-1));СТРОКА(ДВССЫЛ("$A$1:$A$"&СЧЁТЗ($B$2:$B$100)-1)));СТРОКА($A1)))


если Вам конечно будет интересен такой вариант.
С Ув. Станислав

Спам-сообщение скрыто. Показать
0   Спам
5    Hanifi   (22.08.2012 06:30) [ Материал]
   What a pleausre to meet someone who thinks so clearly

Спам-сообщение скрыто. Показать
0   Спам
6    Vinkelman   (18.12.2012 11:45) [ Материал]
   Если не использовать столбец А, результат тот же:
Код
=ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($B$1:$B$15);СТРОКА($B$1:$B$15));СТРОКА(B1)))

Спам-сообщение скрыто. Показать
0   Спам
7    Яна   (15.02.2013 12:48) [ Материал]
   Добрый день, очень интересные у Вас формулы. А нет каких нибудь уникальных? Я работаю аудитором, и приходиться работать с 1с, используем выгрузки из нее.

Спам-сообщение скрыто. Показать
0   Спам
8    AndreTM   (25.05.2013 17:19) [ Материал]
   Странно... Если столбец с данными (B) сам является результатом расчета некоторых формул (пусть даже и немассивных, но извлекающих данные из массива - например НАИБОЛЬШИЙ(), МАКС(), ИНДЕКС(<массив>;СЛУЧМЕЖДУ(1;15)) и т.п. - формула не может получить данные получившегося набора и выдает #Н/Д...

Спам-сообщение скрыто. Показать
0   Спам
9    Serge_007   (25.05.2013 19:11) [ Материал]
   Приведи пожалуйста пример. Я не смог получить значения ошибки по описанным тобой условиям

Спам-сообщение скрыто. Показать
0   Спам
10    Ann   (20.12.2013 21:47) [ Материал]
   Легче с расширенным фильтром

Спам-сообщение скрыто. Показать
0   Спам
11    Александр   (15.01.2014 18:11) [ Материал]
   Здравствуйте!
НЕобходимо сделать следующую операцию :
Есть вкладка, в которой 1 столбец - категория товара, 2 столбец товары , 3 столбец цена (1 рубль, 2 рубля, 3 рубля, 5 рублей)
Необходимо сделать во второй вкладке выборку из первой, чтобы так же было 3 столбца со строками, категориями и ценами, но только цена не больше 2 рублей.

Спам-сообщение скрыто. Показать
0   Спам
12    Serge_007   (16.01.2014 00:09) [ Материал]
   Какое отношение Ваш пост имеет к этой статье?

Спам-сообщение скрыто. Показать
0   Спам
13    moldavan01   (13.02.2014 16:33) [ Материал]
   добрый день.нужна помощь в создании формулы,которая значения нескольких ячеек объединяет в одной с пробелом между ними(значения ячеек цифровые)

Спам-сообщение скрыто. Показать
0   Спам
14    1   (13.02.2014 16:39) [ Материал]
   23423

Спам-сообщение скрыто. Показать
0   Спам
15    1   (13.02.2014 16:40) [ Материал]
   moldavan01, решение Вашей задачи есть на сайте
Воспользуйтесь поиском

Спам-сообщение скрыто. Показать
0   Спам
16    MaestroSVK   (02.04.2014 11:19) [ Материал]
   Может формул д.б. такой?
=ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($A$1:$A$15);СТРОКА($A$1:$A$15));СТРОКА($A$1:$A$15)))

Спам-сообщение скрыто. Показать
0   Спам
17    Танилла   (30.07.2014 11:36) [ Материал]
   а подскажите пожалуйста как эта функция называется по английски, я что-то не могу её найти.

0  
18    Serge_007   (30.07.2014 19:30) [ Материал]
   Какая функция?

Спам-сообщение скрыто. Показать
0   Спам
19    AlexKontev   (06.11.2014 18:37) [ Материал]
   можно свои пять копеек? %)
так сказать, для разнообразия.

=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ($B$2:$B$16;$J$1:J1;0));СТРОКА($A$1:$A$15);"");1))

Спам-сообщение скрыто. Показать
0   Спам
20    Евгений   (09.01.2015 17:04) [ Материал]
   Где я ошибся:

=ИНДЕКС(M1:M2803;ПОИСКПОЗ("*E2*";M1:M2436;0);0)

?

0  
21    Serge_007   (10.01.2015 00:23) [ Материал]
   Евгений, а какое отношение имеет Ваша формула к этой статье?

Спам-сообщение скрыто. Показать
0   Спам
22    tanya   (12.01.2015 21:13) [ Материал]
   Вы можете мне помочь решить задачу?
Учтите дискретность транспортных расходов на доставку разного количества товаров: пусть для
каждого филиала доставка товара в количестве
 не более 0.3max( ) ij x стоит pi
 более 0.3max( ) ij x , но меньше, 0.5max( ) ij x чем обходится в 2pi
 более 0.5max( ) ij x – 3pi
прибыль
8516 6445094120 52875 136012 70644 44680 113350 132486 31998
813 4155 3448 3946 4139 3902 3992 2069 138 3931 93920
250 3618 211 1344 3077 759 1114 1918 3195 3948 17489
2656 3819 2006 1280 3070 3826 4356 590 3857 978 74439
3134 2756 1004 2003 1779 4638 2067 2963 3696 3515 28643
243 3445 435 1736 1411 1091 3112 4425 2976 2399 18197

себестоимость
677 1523 2702 3713 767 2290 2203 4250 2282 1538 143964
1845 141 4706 3824 2099 3717 643 1850 2091 3329 120563
3366 3462 1143 3473 4215 4707 1408 638 477 3217 110363
2873 3536 3111 3379 877 1727 214 2553 4946 3557 117660
2441 1731 4643 472 1111 4948 1812 2310 165 2905 67655

0  
23    Serge_007   (12.01.2015 21:26) [ Материал]
   tanya, какое отношение имеет Ваша задача к этой статье?

Спам-сообщение скрыто. Показать
0   Спам
24    Digital   (14.06.2017 10:18) [ Материал]
   Супер hands

0   Спам
25    Aptus   (27.08.2017 08:14) [ Материал]
   У меня не работает ни одна из этих формул (2003).
Обшарил множество форумов - не нашёл нужную мне формулу.
Задача такая же, практически.
Есть столбик с датами, столбик с пробегом в эти даты. Нужно выбрать даты в том же порядке, исключая даты без пробега (в столбик прохождения медосмотра).
Пока состряпал вот такую кракозябру :-)

[/b]=ЕСЛИ(A20=A39;A40;ЕСЛИ(СМЕЩ(A39;0;10)>0;СМЕЩ(A39;0;0);ЕСЛИ(СМЕЩ(A39;1;10)>0;СМЕЩ(A39;1;0);ЕСЛИ(СМЕЩ(A39;2;10)>0;СМЕЩ(A39;2;0);""))))[/color][/size]

Частично удовлетворяет
Буду благодарен за более оптимальную формулу.

1-25 26-30
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!