Домашняя страница 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 Вы пользуетесь?
[ Результаты · Все опросы ]
Всего ответов: 56972
Главная » Статьи » Эффективная работа в 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)
Просмотров: 184091 | Комментарии: 73 | Теги: впр, формулы эксель, несколько критериев, ПОИСКПОЗ, ВПР по двум критериям, формулы Excel | Рейтинг: 4.7/13


Всего комментариев: 72« 1 2 3 »
0   Спам
26    Serge_007   (16.09.2013 09:33) [ Материал]
   Павел, читайте статью внимательнее, ответ на Ваш вопрос в шестой строке

0   Спам
27    Павел   (16.09.2013 10:20) [ Материал]
   если вы про ввод Ctrl+Shift+Enter, это я сделал, выходит ошибка #Н/Д
Прошу помочь, вчера всю ночь карпео ниче не получилось(

0   Спам
28    Serge_007   (16.09.2013 10:24) [ Материал]
   И чем можно помочь не видя что Вы неправильно сделали?

Спам-сообщение скрыто. Показать
0   Спам
29    derosa   (16.09.2013 10:40) [ Материал]
   вот тут ссылка на скачивание файла:
http://yadi.sk/d/Rzl8V0-a9LbFG

нужно что значение коэф, вставлялось по искомому значению маршрута.

0   Спам
30    Serge_007   (16.09.2013 10:42) [ Материал]
   derosa, для решений задач существует форум

0   Спам
31    антон   (24.10.2013 18:09) [ Материал]
   подскажите а как просумировать данные соответствующии выбранным условиям, т.е у меня несколько позиций соответствуют условию а мне нужна сумма а не первое значение, заранее спасибо

0   Спам
32    Serge_007   (24.10.2013 19:26) [ Материал]
   Вопрос не имеет отношения к данной статье - задавайте его на форуме

0   Спам
33    Алевтина   (07.11.2013 12:29) [ Материал]
   Помогите, пожалуйста, сломала голову уже. Какую формулу вместо ВПР надо прописать, чтобы получить нужный результат? Есть 2 вкладки, надо во вкладку "Общ" проставить в столбец "Тип спецификации" по каждому Заказу из вкладки SpecificationRegistry.rdl, столбец "Тип спецификации", исходя из условия, что номера Заказов совпадают и Вид спецификации тоже. В моем случае это "Fix price".
Сам файл могу прислать на почту, тк не знаю, как подтянуть сюда.

0   Спам
34    Serge_007   (07.11.2013 13:35) [ Материал]
   Вопрос не имеет отношения к данной статье - задавайте его на форуме

0   Спам
35    ПАМ   (26.11.2013 15:48) [ Материал]
   Уважаемый, Администратор Serge_007. Подскажите пожалуйста: как искать по нескольким критериям. У меня похожий пример, только столбцов больше: т.е (как в примере) Товара 3 столбца и количество соответственно тоже 3. Заранее спасибо

0   Спам
36    Serge_007   (26.11.2013 20:17) [ Материал]
   Конкатенируйте нужное количество столбцов и искомых данных

0   Спам
37    ПАМ   (27.11.2013 08:39) [ Материал]
   А как это можно сделать? С помощью функции "И" или еще как то? Спасибо

0   Спам
38    Serge_007   (27.11.2013 10:41) [ Материал]
   Вы статью-то читали?
В ней же подробно описано как это делается

0   Спам
39    ПАМ   (27.11.2013 13:05) [ Материал]
   Прочитал, но не очень хорошо понял, я наверно слишком мало понимаю эксель

0   Спам
40    Serge_007   (27.11.2013 17:38) [ Материал]
   В E2&G2 и A2:A6&B2:B6 добавьте нужные Вам ячейки и диапазоны через амперсанд, например так: E2&G2&F2 и A2:A6&B2:B6&C2:C6

0   Спам
41    ПАМ   (28.11.2013 09:15) [ Материал]
   Спасибо! Очень Вам благодарен!

0   Спам
42    Ирина   (26.01.2014 17:02) [ Материал]
   Ставлю формулу {=ИНДЕКС($O$2:$O$4179;ПОИСКПОЗ(A2&C2;$I$2:$I$4179&$K$2:$K$4179;0))}, считает правильно
протягиваю формулу по массиву, но конечное значение не изменяется (т.е. в ячейки показывает формулу, но конечное значение не меняется)
в чем может быть причина? excel 2003

0   Спам
43    Serge_007   (26.01.2014 19:32) [ Материал]
   Смените формат ячеек с текстового на тот, который нужен Вам

0   Спам
44    Iberia   (28.01.2014 14:43) [ Материал]
   Необходимо провести отбор по двум значениям. приведённая в примере формула не подходит :-(, так как надо именно вернуть значение из определённой ячейки отобранной строки.
Отбор по двум значениям необходимо ввести в следующую формулу
=ЕСЛИ(ЕНД(ВПР(A5:A372;ИнтМестаНазвание;2;ЛОЖЬ));" ";ВПР(A5:A372;ИнтМестаНазвание;2;ЛОЖЬ))
как это сделать? Сейчас идёт отбор по одному значению и возвращается текст из второго столбца.
Надо добавить дополнительное условие для отбора, только при соблюдении обоих условий должно возвращаться значение из второго столбца найденной строки.

0   Спам
45    Serge_007   (28.01.2014 15:31) [ Материал]
   Вопрос не имеет отношения к данной статье - задавайте его на форуме

0   Спам
46    Андрей   (22.08.2014 10:15) [ Материал]
   Добрый день.
Подскажите, пожалуйста, а если первое значение идёт по вертикали, а второе по горизонтали, формула работать не будет?

0  
47    Serge_007   (22.08.2014 10:33) [ Материал]
   Будет работать
Только формулу надо будет предварительно подкорректировать

0   Спам
48    Andrey_Spb   (22.08.2014 10:59) [ Материал]
   Serge_007, подскажите, пожалуйста, как, уже 1,5 часа мучаюсь... Можно в данном примере, я названия товаров разместил по горизонтали, Буду очень благодарен за помощь.

0  
49    Serge_007   (22.08.2014 18:23) [ Материал]
   
Цитата Andrey_Spb
я названия товаров разместил по горизонтали
Как именно? Предлагаете мне угадать? Или узреть телепатически?

Цитата Andrey_Spb
Буду очень благодарен за помощь
Здесь вообще то просто комментарии к статье и никто никому помощь не оказывает. Для помощи специально создан форум

-1   Спам
50    VEKTORVSFREEMAN   (24.09.2014 12:09) [ Материал]
   Доброго времени суток! А что в данной формуле означает "Тип сопоставления" и как его определить?

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