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

Вход

Регистрация

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

 

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

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

КАТЕГОРИИ РАЗДЕЛА
Приёмы работы с книгами, листами, диапазонами, ячейками [6]
Приёмы работы с формулами [14]
Настройки Excel [3]
Инструменты Excel [4]
Интеграция Excel с другими приложениями [5]
Форматирование [1]
Выпадающие списки [2]
Примечания [1]
Сводные таблицы [1]
Гиперссылки [1]
Excel и интернет [1]
Excel для Windows и Excel для Mac OS [2]

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
[ Результаты · Все опросы ]
Всего ответов: 57559
Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами
ВПР по двум (и более) критериям
Предположим что Вы директор по продажам. 
И у Вас есть вот такой ежедневный отчёт по продажам ваших менеджеров:



Из него Вам необходимо узнать сколько упаковок сыра продал Иванов. Понятно что ВПР тут не поможет, она просто вернёт значение из указанного столбца и первой сверху строки совпадения по фамилии (или по продукту).

ПРОБЛЕМА: Как искать значения по двум (и более) критериям?

РЕШЕНИЕ: Формула массива (вводится нажатием Ctrl+Shift+Enter):
Code
=ИНДЕКС(C2:C6;ПОИСКПОЗ(E2&G2;A2:A6&B2:B6;0))

В английской версии:
Code
=INDEX(C2:C6,MATCH(E2&G2,A2:A6&B2:B6,0))




КАК ЭТО РАБОТАЕТ: Амперсанд & сцепляет (конкатенирует) искомые значения "Иванов" и "Сыр" в одно "ИвановСыр" и просматриваемый  массив A2:A6 и B2:B6 в "ИвановМолоко":"ПетровРыба":"СидоровКефир" и т.д. 
Функция ПОИСКПОЗ находит номер строки вхождения точного соответствия "ИвановСыр" в получившемся массиве, функция ИНДЕКС возвращает "Кол-во" из соответствующей строки таблицы.



МИНУСЫ: Конкатенация массивов сильно "утяжеляет" формулу. На больших массивах будет длительный пересчёт, на очень больших - возможно зависание файла (зависит от параметров компьютера, в первую очередь от объёма оперативной памяти, потому как массив, получившийся в результате конкатенации хранится не на листе, а в памяти).

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

ПРИМЕЧАНИЯ: Так же можно искать по трём, четырём и более (неограниченно) критериям.






Категория: Приёмы работы с формулами | Добавил: Serge_007 (06.02.2011)
Просмотров: 185703 | Комментарии: 73 | Теги: впр, формулы эксель, несколько критериев, ПОИСКПОЗ, ВПР по двум критериям, формулы Excel | Рейтинг: 4.7/13


Всего комментариев: 721 2 3 »
Спам-сообщение скрыто. Показать
0   Спам
1    Serge_007   (09.04.2011 17:40) [ Материал]
   http://www.excelworld.ru/publ....-1-0-30

Спам-сообщение скрыто. Показать
0   Спам
2    Оксана   (22.06.2011 13:22) [ Материал]
   Почему-то не работает, выдает #Знач!
в чем может быть дело?

очень нужен отбор по двум значениям! Помогите!
Спасибо)

Спам-сообщение скрыто. Показать
0   Спам
3    Ирина   (11.07.2011 17:18) [ Материал]
   У меня тоже не работает. Пришлось формулой СЦЕПИТЬ соединить все массивы в один и указать его как просматриваемый. :)

Спам-сообщение скрыто. Показать
0   Спам
4    Serge_007   (14.07.2011 14:56) [ Материал]
   Формулы массива вводится нажатием Ctrl+Shift+Enter.
Об этом есть в статье.

Амперсанд и функция СЦЕПИТЬ делают абсолютно одно и тоже.

Спам-сообщение скрыто. Показать
0   Спам
5    magistr663   (15.09.2011 21:24) [ Материал]
   

Спам-сообщение скрыто. Показать
0   Спам
6    Александр   (06.04.2012 02:48) [ Материал]
   Хм, на первый взгляд формула SUMIFS решает ту же задачу, проще, удобнее и намного быстрее так как не работает с массивами. Прошу поправить если я не прав.

Спам-сообщение скрыто. Показать
0   Спам
7    Александр   (06.04.2012 02:51) [ Материал]
   =SUMIFS(C:C;A:A;E2;B:B;G2) - Пример формулы для конкретно этой задачи

Спам-сообщение скрыто. Показать
-1   Спам
8    Пытливый   (21.04.2012 13:20) [ Материал]
   SUMIFS подходит только для суммированмия чисел. Если надо найти текст по двум условиям - она не поможет

Спам-сообщение скрыто. Показать
0   Спам
9    Oleg   (16.08.2012 12:38) [ Материал]
   А как сделать, чтобы ексель по 2-м критериям выдавал все возможные варианты?

Спам-сообщение скрыто. Показать
0   Спам
10    Serge_007   (16.08.2012 13:20) [ Материал]
   Обратиться на форум smile

Спам-сообщение скрыто. Показать
0   Спам
11    Oleg   (16.08.2012 17:25) [ Материал]
   Обратился, Вы не поможете Serge?

Спам-сообщение скрыто. Показать
0   Спам
12    Joao   (19.08.2012 16:58) [ Материал]
   Wow! Great to find a post knocknig my socks off!

Спам-сообщение скрыто. Показать
0   Спам
13    Александр_86   (01.10.2012 21:38) [ Материал]
   Почему когда даже просто прописываю функция ПОИСКПОЗ у меня в итоге #ЗНАЧ! ?. Делаю как описано в статье.

Спам-сообщение скрыто. Показать
0   Спам
14    Полковник МВД   (01.10.2012 22:03) [ Материал]
   Формулы массива вводится нажатием Ctrl+Shift+Enter.
Об этом есть в статье.

Спам-сообщение скрыто. Показать
0   Спам
15    YUrafast   (01.11.2012 15:52) [ Материал]
   Формулы массива вводится нажатием Ctrl+Shift+Enter.
Об этом есть в статье.
Подскажите если после нажатия ни чего не исходит( не появляется какое-то окно и т.п.) что это может значить?

Спам-сообщение скрыто. Показать
0   Спам
16    Serge_007   (01.11.2012 16:37) [ Материал]
   Это значит что всё сделано правильно

Спам-сообщение скрыто. Показать
0   Спам
17    Vinkelman   (27.11.2012 11:15) [ Материал]
   Чтобы не было #ЗНАЧ! надо добавить в формулу проверку и тогда она будет выглядеть так:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(E2&F2;$A$2:$A$6&$B$2:$B$6;0));"";ИНДЕКС($C$2:$C$6;ПОИСКПОЗ(E2&F2;$A$2:$A$6&$B$2:$B$6;0)))

Спам-сообщение скрыто. Показать
0   Спам
18    velmur   (06.12.2012 16:12) [ Материал]
   Здравствуйте! У меня по данной формуле возникла следующая ситуация:
1) в строке 32 у меня занесены следующие данные столбец B Profi Mass bag столбец С 900 g;
2) в строке 33 столбец B Profi Mass столбец C Profi Mass 2800 g.

Из анализируемого диапазона формула берет цену за Profi Mass bag 900 g и ставит ее напротив Profi Mass 2800 g?

Спам-сообщение скрыто. Показать
0   Спам
19    Денис   (11.01.2013 07:08) [ Материал]
   Предложу свой вариант поиска по 2м критериям, работает безотказно:

Код
=ЕСЛИ(И((A6=ИНДЕКС(Данные!A1:A3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);1));(B6=ИНДЕКС(Данные!B1:B3500;ПОИСКПОЗ(B6;Данные!B1:B3500;0);1)));ИНДЕКС(Данные!A1:F3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);4);"Ошибка. Проверьте формулу")

Спам-сообщение скрыто. Показать
0   Спам
20    Денис   (11.01.2013 07:12) [ Материал]
   разложу формулу для наглядности:

=ЕСЛИ(

И(
(A6=ИНДЕКС(Данные!A1:A3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);1));
(B6=ИНДЕКС(Данные!B1:B3500;ПОИСКПОЗ(B6;Данные!B1:B3500;0);1)));

ИНДЕКС(Данные!A1:F3500;ПОИСКПОЗ(A6;Данные!A1:A3500;0);4);
"Ошибка. Проверьте формулу")

Спам-сообщение скрыто. Показать
0   Спам
21    SanDen   (25.01.2013 12:15) [ Материал]
   я вот столкнулся с другой проблемой.
у меня есть табличка, в которой идет отбор с помощью СУММЕСЛИМН, она все отлично обсчитывает, и выдает результаты, но вот незадача, работает только при открытых исходных файлах.
какой функцией можно заменить, если вариант ВРП не проходит по случаю двух условий, а индекс и поискпоз мне вроде бы не нужен, потому что данные только числовые? (да и утяжеляет он как говорят.)
для примера формула:
Код
=СУММЕСЛИМН('[файл1.xls]наличные'!$G$2148:$G$2297;'[файл1.xls]наличные'!$A$2148:$A$2297;A10;'[файл1.xls]наличные'!$B$2148:$B$2297;A$1)

А10 - ссылка на дату конкретную по которой ищем
А$1 - выпадающий список с выбором наименования кассы.

как мне это решать?, помогите плиз.

Спам-сообщение скрыто. Показать
0   Спам
22    Татьяна   (23.02.2013 00:03) [ Материал]
   Здравствуйте! Помогите пожалуйста! У меня массив - девять столбцов и шесть строк. Как данные массива записать в одну строку, желательно без пустых ячеек? Подскажите формулу.

Спам-сообщение скрыто. Показать
0   Спам
23    Сказлажоп   (10.07.2013 05:12) [ Материал]
   Мужики, спасибо за формулу, жить стало легче! =)

Только один вопрос, а как сделать так, чтоб выводилось максимальное значение. Т.к. у меня, по одному и тому же критерию поиска, несколько сумм, а нужна максимальная.

Заранее спасибо ;)

Спам-сообщение скрыто. Показать
0   Спам
24    Serge_007   (10.07.2013 22:08) [ Материал]
   Вопрос не имеет отношения к данной статье - задавайте его на форуме

0   Спам
25    Павел   (16.09.2013 08:47) [ Материал]
   Скажите, у меня большая таблица на нескольких листах, после проведения функции ставит #ЗНАЧ
Скажите из за чегоэтоможет быть

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