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

Вход

Регистрация

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

 

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

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

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

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 35792
Главная » Статьи » Эффективная работа в 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)
Просмотров: 35623 | Комментарии: 24 | Рейтинг: 4.9/11


Всего комментариев: 24
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    Serge_007   (18.12.2012 12:42)
   В формуле
Code
=ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($A$1:$A$15);СТРОКА($A$1:$A$15));СТРОКА(A1)))

столбец А НЕ ИСПОЛЬЗУЕТСЯ

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

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

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

0   Спам
11    Ann   (20.12.2013 21:47)
   Легче с расширенным фильтром

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

0   Спам
13    Serge_007   (16.01.2014 00:09)
   Какое отношение Ваш пост имеет к этой статье?

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

0   Спам
15    1   (13.02.2014 16:39)
   23423

0   Спам
16    1   (13.02.2014 16:40)
   moldavan01, решение Вашей задачи есть на сайте
Воспользуйтесь поиском

0   Спам
17    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   Спам
18    Танилла   (30.07.2014 11:36)
   а подскажите пожалуйста как эта функция называется по английски, я что-то не могу её найти.

0  
19    Serge_007   (30.07.2014 19:30)
   Какая функция?

0   Спам
20    AlexKontev   (06.11.2014 18:37)
   можно свои пять копеек? %)
так сказать, для разнообразия.

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

0   Спам
21    Евгений   (09.01.2015 17:04)
   Где я ошибся:

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

?

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

0   Спам
23    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  
24    Serge_007   (12.01.2015 21:26)
   tanya, какое отношение имеет Ваша задача к этой статье?

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