Excel совпадение по двум условиям

Поиск и подстановка по нескольким условиям

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.

Способ 1. Дополнительный столбец с ключом поиска

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

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы: Простой способ, знакомая функция, работает с любыми данными.

Минусы: Надо делать дополнительный столбец и потом, возможно, еще и прятать его от пользователя. При изменении числа строк в таблице — допротягивать формулу сцепки на новые строки (хотя это можно упростить применением умной таблицы).

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS), появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы: Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы: Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

1. Выделите пустую зеленую ячейку, где должен быть результат.
2. Введите в строке формул в нее следующую формулу:

   

3. Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы: Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы: Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Ссылки по теме

• Как искать и подставлять данные с помощью функции ВПР (VLOOKUP)
• Что такое формулы массива и как их использовать
• Как использовать связку функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
• Как извлечь сразу все значения, а не только первое с помощью ВПР

Одна из самых популярных и вполне простых в применении функция ЕСЛИ является логической функцией. Она позволяет проверять некоторые данные на соответствие заданных условий и показывает результат после сравнения.

Аргументы функции ЕСЛИ и принцип действия в Excel

Синтаксис функции в самом простом применении выглядит следующим образом: ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).

Теперь разберем её аргументы более подробно:

1. Логическое выражение – определённые данные, которые мы должны проверить на соответствие с некоторыми имеющимися у нас условиями.
2. Значение_если_истина – результат проверки, который мы увидим, когда логическое выражение будет справедливо.
3. Значение_если_ложь – результат при несоответствии условия и наших данных, которые мы проверяем.

Схематически это выглядит следующим образом:

Теперь рассмотрим, как нам определить результат проверки с помощью функции ЕСЛИ. Для начала определяем ЧТО нам надо проверять – число 50. Затем указываем условие – «больше 40», «меньше 40», после чего пишем результат проверки – «ИСТИНА», при условии, что 50 действительно больше 40 и «ЛОЖЬ», когда 50 меньше 40. После того как мы определили, как будут выглядеть наши аргументы, начинаем собирать по кусочкам нашу формулу:

Функция проверила выражение «50 больше 40» и определила, что результат — ИСТИНА (ячейка Е4).

Следующее условие проверяем точно также просто заменив оператор «больше» на «меньше»:

Поскольку 40 больше 50, проверка определила, что наше выражение – ЛОЖЬ.

Теперь рассмотрим более наглядные примеры применения функции ЕСЛИ. У нас есть список студентов и оценок в баллах, полученных за экзамен. Нам нужно найти студентов отличников, которые получили оценку больше или равно 90. Начинаем составлять функцию, ячейка F17. Значение в ячейке E17 должно быть больше или равно (>=) 90, тогда мы получим результат «ИСТИНА». Когда значение в ячейке Е17 будет меньше 90, возвратится значение «ЛОЖЬ»:

И копируем значение ячейки до конца столбца, таким образом мы нашли студентов, которые получили результат больше или равно 90 баллов:

Но такие значения таблицы не позволяют пользователю корректно прочитать информацию, которую мы хотели донести. Тогда нам нужно заменить слова ИСТИНА и ЛОЖЬ на более принятые и понятные. Тут нам пригодится таблица соответствий оценок по шкале ECTS оценкам в баллах B10:C13. Пусть при истинном результате у нас будет стоять оценка А, которая соответствует баллам от 90 до 100, а при ложном – ячейки будут оставаться пустыми, после чего копируем первую ячейку до конца столбца и наша таблица станет более информативной:

Формула функции ЕСЛИ и ВПР

Теперь рассмотрим пример использования вложенных формул и ситуацию, где они могут пригодиться. В предыдущем примере мы определили студентов отличников, но у нас остались ещё незаполненные поля. Нам нужно также определить какую оценку в буквах получат и другие студенты. Указываем, что студенты с баллом больше или равно 90 получат оценку А: =ЕСЛИ(E31>=90; «A»; Затем на месте аргумента «значение_если_ложь» вставляем формулу ЕСЛИ(Е31>=80; «В»; и на месте аргумента значение_если_ложь этой же формулы вставляем ещё одну формулу ЕСЛИ(Е31>=60; «С»; и на месте третьего аргумента уже этой функции пишем последнее условие, не забывая добавить скобки: ЕСЛИ(Е31<60)))):

Копируем формулу до конца столбца и таким образом мы построили вложенную функцию. Однако иногда при написании такой функции нужно учитывать один нюанс – она корректно работает, пока данные для сравнения указываться от большего значения к меньшему (1,2,3,4):

Вот что получится, когда мы укажем условия для сравнения наоборот – от меньшего к большему:

Логика в том, что функция во время проверки первой ячейки определила, что значение больше 60 и выдала соответствующий результат – «С». Дальше проверка не продолжилась. Есть ситуации, где оценка вместо А будет А+, А, А-, такое разветвление будет по каждой букве и самих буквенных оценок будет больше. Тогда процесс создания вложенной функции будет очень долгим, вложенных формул будет очень много и становиться легко запутаться. В таком случае вместо ЕСЛИ можно использовать ВПР. Для начала видоизменяем нашу меньшую таблицу. Такие изменения обусловлены особенностями работы функции ВПР:

Формула ВПР будет искать приблизительное значение ячейки Е60 в диапазоне В53:С56 во втором столбце и передавать найденные значения в основную таблицу:

Функция ЕСЛИ несколько условий

Теперь рассмотрим примеры, когда наши данные должны соответствовать нескольким условиям. Функция ЕСЛИ вместе с функцией И имеют следующий синтаксис:

Например у нас есть список студентов и данные их оценок по трём предметам. Нам нужно проверить наличие у студента оценки 2 хотя бы по одному предмету и указать, есть ли у студента пересдача:

Кроме функции И можно использовать функцию ИЛИ. Разница между ними в том, что при использовании И одновременно должны соответствовать условию все логические выражения. При использовании функции ИЛИ достаточно, чтобы условию соответствовало хотя бы одно логическое выражение.

Например у нас есть список студентов и условие, что при наличии хотя бы одной оценки 3, студент не получает стипендию. Мы проверяем, равно ли содержимое ячеек по предметам цифре 3:

Функция ЕСЛИ в сочетании с функцией НЕ своей работой очень похожа на самый простой пример функции ЕСЛИ с одним условием и двумя результатами, только сейчас наше логическое выражение поменяет условие на противоположное. У нас есть список студентов и информация о наличии и количестве прогулов. Нам нужно указать что при полном отсутствии прогулов у студента зачёт, а в любых других случаях – не зачёт.

При условии, что в ячейке Е91 не находится слово «нет» (НЕ(Е91= «нет»)), наш результат – зачёт, в любом инном случае – не зачёт:

Может быть ситуация, когда нам нужны не только результаты обработки условий, а и их графическое изображение. Кроме функций И, ИЛИ, НЕ мы можем комбинировать ЕСЛИ + МАКС. Рассмотрим ситуацию, где нам может это пригодиться.

У нас есть отчет о чистых прибылях нескольких компаний. Мы хотим видеть только тот показатель, который является максимальным. Для этого мы используем вместе с функцией ЕСЛИ функцию МАКС: ЕСЛИ (МАКС(указываем диапазон в котором будем искать значение)=ячейка, которую функция будет пропускать через диапазон; значение если истина (проверяемая ячейка); значение если ложь (ничего не указывать)). В ячейке Е113 прописываем формулу, не забываем про абсолютные ссылки для диапазона D113:E119, иначе он сместится при копировании, копируем формулу до конца столбца:

Разнообразим наши результаты работы и создадим график, который будет так же выделять наше максимальное число: выбираем диапазон D111:E119 – Вставка — Рекомендуемые диаграммы – Выбираем первую диаграмму и ОК. Теперь у нас данные выводятся в табличном и графическом виде:

Теперь максимальное число выделяется не только в таблице, а и на диаграмме. Но сейчас этих величин у нас две – с обеих столбцов. Немного подредактируем нашу диаграмму. На диаграмме выбираем любой из Рядов, открываем меню, клацая правой кнопкой мыши по нём, выбираем Формат ряда данных, указываем Перекрытие рядов на 100% и рассмотрим результат, который у нас получился:

Данные из третьего столбца перекрыли данные из второго и мы получили подсвечивание максимального показателя. Теперь, когда нам нужно будет изменить любое число во втором столбце, наша формула заново определит максимальное число из столбца «Чистая прибыль», покажет его в столбце «Максимальный показатель», а потом мы увидим его среди остальных чисел и на диаграмме автоматически. Например, укажем новое число для автопроизводителя Nissan – 1600. Вот какие изменения произошли:

Скачать пример функции если с несколькими условиями в Excel

Формула в столбце Е изменила свои вычисления и эти изменения отобразились на графике – подсвечивается новое найденное максимальное число. Такие процессы будут происходить при любом изменении показателей в столбце «Чистая прибыль».

Поиск значений в списке данных

​Смотрите также​ или с одной​ 60, рабочая температура​ где было мое​ быстрее — она​ А как файл​ЕСЛИ (IF)​ аналогично предыдущему примеру:​ выбранному пользователем в​ как имена продавцов​10​Lookup table​ найденной строки и​Ctrl+Shift+Enter​ рассчитаны на продвинутого​Загрузка надстройки мастера подстановок​ в ежедневно обновляемом​Предположим, что требуется найти​ строкой. Поэтому сразу​ до — 90​

В этой статье

​ последнее сообщение. Не​ последовательно отсекает условия​ прикрепить? что-то не​

​когда нашли совпадение, то​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ желтой ячейке товару.​

​ записаны в просматриваемой​.​– это название​ столбцов B, C​

​вместо обычного нажатия​ пользователя, знакомого с​Нажмите кнопку​

​ внешнем диапазоне данных.​ внутренний телефонный номер​ усложним задачу и​

​но уже существует​ знаю, как перенести.​ и считает только​

Поиск значений в списке по вертикали по точному совпадению

​ пойму​ определяем номер строки​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​ Это поможет сделать​ таблице (Lookup table),​

Примеры функции ВПР

​Очень просто, правда? А​ листа, где находится​ и D.​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​Enter​

​ основными принципами и​Microsoft Office​ Известна цена в​ сотрудника по его​ на конкретном примере​

​ заводской датчик с​Переместил​ там, где ИСТИНА.​​RAN​​ (столбца) первого элемента​Разница только в последнем​ функция​​ используем функцию​​ теперь давайте рассмотрим​

​ просматриваемый диапазон.​{=MAX(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​. Microsoft Excel заключит​

​ синтаксисом функции​

Поиск значений в списке по вертикали по приблизительному совпадению

​, а затем —​ столбце B, но​

​ идентификационному номеру или​​ проиллюстрируем как применять​ рабочей температурой к​arturnt2​

​ А первая считает​: Чукча не читатель.​ в таблице в​ аргументе обеих функций​ПОИСКПОЗ (MATCH)​ВПР​​ немного более сложный​​Давайте проанализируем составные части​{=МАКС(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​ Вашу формулу в​ВПР​ кнопку​ неизвестно, сколько строк​ узнать ставку комиссионного​ функцию ПОИСКПОЗ для​ примеру от 50​​: Здравствуйте, уважаемые форумчане!​​ всё подряд​

​ Чукча писатель!​ этой строке (столбце)​ПОИСКПОЗ (MATCH)​

​из категории​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

​для поиска​ пример. Предположим, что​ формулы, чтобы Вы​

​Формула ищет значение из​​ фигурные скобки:​. Если Вам еще​Параметры Excel​ данных возвратит сервер,​ вознаграждения, предусмотренную за​ таблицы с двумя​ до 100…..​ пожалуйста, помогите решить​Сложение быстрее деления,​Как файл приложить​ с помощью функций​

​-​​Ссылки и массивы (Lookup​ID​ у нас есть​

​ понимали, как она​​ ячейки A2 на​​{=SUM(VLOOKUP(B2,’Monthly sales’!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}​ далеко до этого​и выберите категорию​ а первый столбец​ определенный объем продаж.​

​ столбцами и более.​​по этому и​ задачу в Эксель​ СУММЕСЛИ быстрее СУММПРОИЗВ,​ — красненькая строчка​СТОЛБЕЦ (COLUMN)​Типу сопоставления​ and Reference)​, соответствующего заданному продавцу.​ таблица, в которой​​ работает, и могли​​ листе​

​{=СУМ(ВПР(B2;’Monthly sales’!$A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))}​

Поиск значений в списке по горизонтали по точному совпадению

​ уровня, рекомендуем уделить​Надстройки​ не отсортирован в​

​ Необходимые данные можно​Для примера возьмем список​​ должно быть сравнение​​ не используя при​ волатильные функции (погуглите)​ сверху.​

​и​(здесь он равен​. В частности, формула​

​ Имя записано в​

Поиск значений в списке по горизонтали по приблизительному совпадению

​ перечислены имена продавцов​ настроить её под​

​Lookup table​​Если же ограничиться простым​ внимание первой части​.​

​ алфавитном порядке.​ быстро и эффективно​ автомобилей из автопарка​Genbor​ этом макросы. Во​ лучше не использовать,​Как сделать «приближенный​СТРОКА (ROW)​ минус 1). Это​

​ПОИСКПОЗ(J2; A2:A10; 0)​ ячейке F2, поэтому​ и их номера​

​ свои нужды. Функцию​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

​и возвращает максимальное​​ нажатием​ учебника – Функция​В поле​C1​ находить в списке​ средней фирмы, как​: Тогда текущая форма​

​ вложении на листе​ по возможности нужно​ поиск» — F1​выдергиваем значение города или​ некий аналог четвертого​даст нам нужный​ для поиска используем​ID​СУММ​ из значений, которые​Enter​ ВПР в Excel:​Управление​ — это левая верхняя​ и автоматически проверять​

1. ​ показано ниже на​

2. ​ вывода не является​​ «БАЗА» есть 3​​ делать так, чтобы​​Il_sun​​ товара из таблицы​​ аргумента функции​​ результат (для​

3. ​ формулу:​​(Lookup table). Кроме​​пока оставим в​ находятся на пересечении​

   ​, вычисление будет произведено​

4. ​ синтаксис и примеры.​​выберите значение​ ​ ячейка диапазона (также​ их правильность. Значения,​​ рисунке:​​ оптимальной.​​ столбца которая должна​​ не считать одно​

5. ​: в отношении прикрепленного​​ с помощью функции​​ВПР (VLOOKUP) — Интервального​​Яблока​​VLOOKUP($F$2,Lookup_table,2,FALSE)​​ этого, есть ещё​​ стороне, так как​

6. ​ найденной строки и​​ только по первому​​ВПР и СУММ –​Надстройки Excel​​ называемая начальной ячейкой).​​ возвращенные поиском, можно​​В обеих столбцах названия​​Если вбивать по​

7. ​ вестись операторами на​

​ и то же​

support.office.com

Как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel

​ файла формула должна​ИНДЕКС (INDEX)​ просмотра (Range Lookup)​это будет число​​ВПР($F$2;Lookup_table;2;ЛОЖЬ)​​ одна таблица, в​​ её цель очевидна.​​ столбцов B, C​​ значению массива, что​​ суммируем все найденные​и нажмите кнопку​Формула​ затем использовать в​ автомобилей и отделов​

​ «примерным» параметрам, то​ листе «ПОДБОР ДАТЧИКОВ»​ по несколько раз​ иметь вид​hatter​. Вообще говоря, возможных​ 6). Первый аргумент​Конечно, Вы могли бы​ которой те же​LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)​ и D.​ приведёт к неверному​ совпадающие значения​Перейти​ПОИСКПОЗ(«Апельсины»;C2:C7;0)​ вычислениях или отображать​ повторяются, но нет​ выводиться будет только​ пользователь вручную вводит​ (как у Вас​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(B2:C3;ПОИСКПОЗ(A8;A2:A3;0);ПОИСКПОЗ(A6;B1:C1;0))​: Имеется файл с​

​ значений для него​ этой функции -​ ввести имя как​ID​ПРОСМОТР($C$2:$C$10;’Lookup table’!$A$2:$A$16;’Lookup table’!$B$2:$B$16)​{=MIN(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​ результату.​Другие вычисления с ВПР​.​ищет значение «Апельсины»​ как результаты. Существует​ парных дубликатов. Например,​ первый чертеж по​ «ТЕМПЕРАТУРУ ОТ» и​ сначала считается ПОИСКПОЗ,​book​

​ двумя листами, на​ три:​ искомое значение (​ искомое значение напрямую​связаны с данными​Функция​{=МИН(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​​Возможно, Вам стало любопытно,​​ (СРЗНАЧ, МАКС, МИН)​​В области​​ в диапазоне C2:C7.​​ несколько способов поиска​​ в списке 2​​ списку. Один из​​ «ТЕМПЕРАТУРУ ДО». у​ потом или тот​:​ первый лист в​1​Яблоко​ в функцию​

​ о продажах (Main​ПРОСМОТР​Формула ищет значение из​ почему формула на​ПРОСМОТР и СУММ –​​Доступные надстройки​​ Начальную ячейку не​ значений в списке​ автомобиля марки Ford,​ вероятно многих. И​ меня не получается​ же ПОИСКПОЗ, или​Добрый день!​

• ​ последний столбец с​- поиск ближайшего​из желтой ячейки​
• ​ВПР​ table). Наша задача​
• ​просматривает товары, перечисленные​ ячейки A2 на​ рисунке выше отображает​ поиск в массиве​
• ​установите флажок рядом​ следует включать в​ данных и отображения​

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

​ но оба они​ он возможно будет​ сделать формулу которая​ ВПР), ЕСЛИОШИБКА тоже​Есть «Реестр» операций,​ названием БТ нужно​ наименьшего числа, т.е.​ J2), второй -​, но лучше использовать​ – найти сумму​ в столбце C​​ листе​​[@Product]​​ и сумма связанных​​ с пунктом​ этот диапазон.​

​ результатов.​ из разных отделов.​ далеко не самым​ должна удовлетворять следующим​ не очень хорошо​ в котором наряду​ проставить название Бизнес​ введенные пользователем размеры​​ диапазон ячеек, где​​ абсолютную ссылку на​

​ продаж для заданного​ основной таблицы (Main​Lookup table​, как искомое значение.​

​ значений​Мастер подстановок​1​​Поиск значений в списке​​ Если мы захотим​​ оптимальным.​​ условиям:​

​ - у нее​ с другими данными​
​ темы из второго​

​ двери округлялись бы​ мы ищем товар​​ ячейку, поскольку так​​ продавца. Здесь есть​ table), и возвращает​и возвращает минимальное​ Это происходит потому,​​ВПР и СУММЕСЛИ –​​и нажмите кнопку​ — это количество столбцов,​​ по вертикали по​​ узнать номер позиции​​Проще всего не​​Если ТЕМПЕРАТУРА ОТ(из​​ первый аргумент считается​​ есть три текстовых​

​ листа, по соответствующим​ до ближайших наименьших​​ (столбец с товарами​​ мы создаём универсальную​​ 2 отягчающих обстоятельства:​​ соответствующую цену из​ из значений, которые​ что мои данные​ суммируем значения, удовлетворяющие​ОК​​ которое нужно отсчитать​​ точному совпадению​​ этого автомобиля, то​​ греть голову, а​

​ "ПОДБОР ДАТЧИКОВ")> ТЕМПЕРАТУРЫ​
​ всегда (но часто​

​ столбца «Контрагент», «Статья​​ ему сочетанию кодов​ подходящих размеров из​ в таблице -​​ формулу, которая будет​​Основная таблица (Main table)​​ столбца B просматриваемой​​ находятся на пересечении​ были преобразованы в​ определённому критерию​

​.​
​ справа от начальной​

​Поиск значений в списке​ в результате функция​​ пользоваться фильтрами в​​ ОТ(из «БАЗА») и​ альтернатива ЕСЛИОШИБКА еще​ Бюджета» и «Группа​ ОКПО и УКЗЕД,​ таблицы. В нашем​

​ A2:A10), третий аргумент​ работать для любого​ содержит множество записей​​ таблицы (Lookup table).​​ найденной строки и​ таблицу при помощи​Если Вы работаете с​Следуйте инструкциям мастера.​ ячейки, чтобы получить​ по вертикали по​​ ПОИСПОЗ вернет нам​​ таблице «БАЗА»​​ ТЕМПЕРАТУРА ДО(из «ПОДБОР​​ хуже), иногда лучше​ Статей Бюджета». Надо​ т.е. если в​ случае высота 500​ задает тип поиска​ значения, введённого в​ для одного​$​ столбцов B, C​ команды​ числовыми данными в​

​К началу страницы​​ столбец, из которого​​ приблизительному совпадению​​ позицию в диапазоне​​arturnt2​ ДАТЧИКОВ»)< ТЕМПЕРАТУРЫ ДО(из​ сделать допстолбец, чем​ заполнить текстовый столбец​ первом листе есть​ округлилась бы до​ (0 — точное​ эту ячейку.​ID​D$2:$D$10​ и D.​Table​ Excel, то достаточно​​В этом уроке Вы​​ возвращается значение. В​Поиск значений по вертикали​ где встречается первое​: такое вариант точно​ «БАЗА»)​ писать все в​ «Группа Статей Бюджета».​

​ сочетание Код ОКПО=64522​ 450, а ширина​​ совпадение наименования, приблизительный​​sum_range​​в случайном порядке.​​– количество товаров,​​{=0.3*SUM(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​​(Таблица) на вкладке​​ часто Вам приходится​​ найдёте несколько интересных​ этом примере значение​ в списке неизвестного​ значение – 3.​

Выполняем другие вычисления, используя функцию ВПР в Excel

​ не устроит людей​и при этом​ одну формулу -​ Есть две таблицы​ и Код товара​ 480 до 300,​ поиск запрещен).​(диапазон_суммирования) – это​Вы не можете добавить​ приобретенных каждым покупателем,​​{=0.3*СУММ(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​​Insert​ не только извлекать​

Вычисляем среднее:

​ примеров, демонстрирующих как​
​ возвращается из столбца​

​ размера по точному​ То есть Ford​ которые требуют от​​ найденные значение находится​​ быстрее работать будет.​ соответствия этих параметров:​ УК ЗЕД -​ и стоимость двери​Во-вторых, совершенно аналогичным способом​ самая простая часть.​

Находим максимум:

​ столбец с именами​
​ чьё имя есть​

​Формула ищет значение из​(Вставка). Мне удобнее​ связанные данные из​​ использовать функцию​​ D​ совпадению​ из отдела продаж:​ меня выполнения задачи….далеко​ на одной строке,​ В общем, все​

Находим минимум:

​ «Исключения» и «Обычная​
​ 10 знаков=8708299000, то​

​ была бы 135.​ мы должны определить​ Так как данные​​ продавцов к основной​​ в столбце D​ ячейки A2 на​ работать с полнофункциональными​ другой таблицы, но​ВПР​Продажи​

Вычисляем % от суммы:

​Поиск значений в списке​
​Что же делать если​

​ не каждый работник​ то вытащить значение​ более-менее логично, просто​​ зависимость». Сложность в​​ в столбец БТ​-1​ порядковый номер столбца​ о продажах записаны​ таблице.​ основной таблицы. Умножая​ листе​ таблицами Excel, чем​

​ и суммировать несколько​(VLOOKUP) вместе с​.​ по горизонтали по​ нас интересует Ford​

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

​ умеет пользоваться фильтрами…..даже​ «НОМЕР ЧЕРТЕЖА» из​ немного порассуждать нужно.​​ том, что при​​ первого листа проставляется​- поиск ближайшего​ в таблице с​ в столбец C,​Давайте запишем формулу, которая​ количество товара на​​Lookup table​​ с простыми диапазонами.​ столбцов или строк.​​СУММ​​К началу страницы​ точному совпадению​ из маркетингового отдела?​ если он и​ «БАЗА» в соответствующую​

​ Хотя обычно объемы​ разных комбинациях «Контрагент»​ название бизнес темы​ наибольшего числа, т.е.​ нужным нам городом.​ который называется​ найдет все продажи,​ цену, которую возвратила​, затем суммирует значения,​ Например, когда Вы​ Для этого Вы​(SUM) или​Для выполнения этой задачи​Поиск значений в списке​ Кроме того, мы​ будет находить первый​ ячейку на листе​

​ не такие уж​ — «Статья» надо​​ «ЗЧ к СНГ»​​ нестандартная высота 500​ Функция​Sales​ сделанные заданным продавцом,​ функция​​ которые находятся на​​ вводите формулу в​​ можете комбинировать функции​​СУММЕСЛИ​

​ используется функция ГПР.​
​ по горизонтали по​

​ хотим использовать только​ по списку чертеж​ «ПОДБОР ДАТЧИКОВ»​​ и большие, поэтому​​ искать значения «Группы»​

​ и т.д. Листы​​ округлялась бы до​ПОИСКПОЗ(J3; B1:F1; 0)​, то мы просто​

​ а также просуммирует​ПРОСМОТР​ пересечении найденной строки​ одну из ячеек,​СУММ​(SUMIF) в Excel,​​ См. пример ниже.​​ приблизительному совпадению​ функцию ПОИСПОЗ, не​ уже хорошо​

1. ​в общем нужно​
​ скорости работы формул​

   ​ в разных таблицах​​ содержат около миллиона​​ 700, а ширина​сделает это и​ запишем​ найденные значения.​, получаем стоимость каждого​ и столбцов B,​ Excel автоматически копирует​

2. ​и​​ чтобы выполнять поиск​​Функция ГПР выполняет поиск​Создание формулы подстановки с​ прибегая к формулам​Genbor​ совершить поиск значений​ особо не отличаются.​ соответствия.​ строк, формула массива​​ 480 — до​​ выдаст, например, для​Main_table[Sales]​
3. ​Перед тем, как мы​​ приобретенного продукта.​​ C и D,​ её на весь​ВПР​ и суммирование значений​ по столбцу​ помощью мастера подстановок​ с комбинациями других​​: Что-то навроде такого.​​ по нескольким условиям​​ Вот если таких​​Задачу надо решить без​ у меня не​ 600 и стоимость​Киева​.​ начнём, позвольте напомнить​$B$2:$B$10=$​

​ и лишь затем​ столбец, что экономит​, как это показано​ по одному или​Продажи​ (только Excel 2007)​ функций ИНДЕКС и​​Кнопку обновления фильтра​​Товарищи, пожалуйста выручайте!​ формул много или​ макросов.​ работает​

​ составила бы уже​​, выбранного пользователем в​​Всё, что Вам осталось​​ Вам синтаксис функции​G$1​ вычисляет 30% от​ несколько драгоценных секунд.​

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

​ ниже.​​ нескольким критериям.​​и возвращает значение​Для решения этой задачи​​ т.п. Выход из​​ вынести на панель​arturnt2​ объемы большие, тогда​Коротко алгоритм поиска​Serge_007​ 462. Для бизнеса​​ желтой ячейке J3​​ сделать, это соединить​СУММЕСЛИ​– формула сравнивает​ суммы.​Как видите, использовать функции​​Предположим, что у нас​​Вы пытаетесь создать файл-сводку​

​ из строки 5 в​
​ можно использовать функцию​

​ этой ситуации находится​ и жать ее​​: Само вложение​​ да​​ выглядит так: Если​​: Такие массивы лучше​

​ так гораздо интереснее!​ значение 4.​ части в одно​(SUMIF):​ имена клиентов в​Если мы добавим перечисленные​ВПР​ есть список товаров​​ в Excel, который​​ указанном диапазоне.​ ВПР или сочетание​ в определении настроек​ при смене параметров.​​Genbor​​book​ текущая пара «Контрагент-Статья​ на VBA обрабатывать​ :)​И, наконец, в-третьих, нам​ целое, и формула​SUMIF(range,criteria,[sum_range])​

• ​ столбце B основной​ выше формулы в​и​​ с данными о​​ определит все экземпляры​
• ​Дополнительные сведения см. в​ функций ИНДЕКС и​ аргументов и выполнения​arturnt2​

​: Код =ЕСЛИ(И(C5>=МИН(БАЗА!$A$3:$A$5);D5 Вставить​: Наверное, это мой​ Бюджета» есть в​Rioran​0​

​ нужна функция, которая​СУММЕСЛИ+ВПР​СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])​​ таблицы с именем​​ таблицу из предыдущего​

​СУММ​
​ продажах за несколько​

• ​ одного конкретного значения​​ разделе, посвященном функции​ ПОИСКПОЗ.​ функции в массиве.​: к сожалению не​ в желтую ячейку.​ вариант… Уже решил:​
• ​ таблице «Исключения», то​​: Доброго времени.​- поиск точного​ умеет выдавать содержимое​будет готова:​range​ в ячейке G1.​ примера, результат будет​
• ​в Excel достаточно​​ месяцев, с отдельным​ и просуммирует другие​ ГПР.​Дополнительные сведения см. в​ Для этого:​ работает​Условие не совсем​ как только получу​ Группа выбирается из​Формулы массивов используют​

​ соответствия без каких​ ячейки из таблицы​=SUMIF(Main_table[ID],VLOOKUP($F$2,Lookup_table,2,FALSE),Main_table[Sales])​​(диапазон) – аргумент​​ Если есть совпадение,​ выглядеть так:​ просто. Однако, это​ столбцом для каждого​ значения, связанные с​К началу страницы​ разделе, посвященном функции​

1. ​В ячейку B16 введите​​Код =ЕСЛИОШИБКА((ЕСЛИ(И(C5>=МИН(БАЗА!$A$3:$A$5);D5 в​ точное, поэтому возможно​ нужные мне цифры,​​ таблицы «Исключения»; иначе​​ виртуальную память компьютера.​ либо округлений. Используется​ по номеру строки​=СУММЕСЛИ(Main_table[ID];ВПР($F$2;Lookup_table;2;ЛОЖЬ);Main_table[Sales])​ говорит сам за​ возвращается​​В случае, когда Ваше​​ далеко не идеальное​ месяца. Источник данных​ ним? Или Вам​Для выполнения этой задачи​​ ВПР.​​ значение Ford, а​
2. ​ первом скриншоте я​​ я что-то не​ и Дб будет​ — Группа выбирается​ Если у Вас​ для 100%-го совпадения​​ и столбца -​​Урок подготовлен для Вас​​ себя. Это просто​​1​ искомое значение —​ решение, особенно, если​ – лист​ нужно найти все​

   ​ используется функция ГПР.​
​Что означает:​

   ​ в ячейку C16​ указал интервал 60​ так понял. Проверяй.​ равен Кр, буду​​ из таблицы «Обычная​​ такие большие объёмы​ искомого значения с​ функция​ командой сайта office-guru.ru​ диапазон ячеек, которые​, в противном случае​ это массив, функция​ приходится работать с​

3. ​Monthly Sales​​ значения в массиве,​Важно:​=ИНДЕКС(нужно вернуть значение из​ название интересующего нас​ и 65, что​Чтобы при неподходящих​​ стараться изучить про​​ зависимость».​ работы — то​​ одним из значений​​ИНДЕКС (INDEX)​

​Источник: https://www.ablebits.com/office-addins-blog/2014/08/05/excel-vlookup-sum-sumif/​ Вы хотите оценить​0​ВПР​​ большими таблицами. Дело​​:​

​ удовлетворяющие заданному условию,​
​  Значения в первой​

​ C2:C10, которое будет​ отдела – Маркетинговый.​
​ удовлетворяет условию первой​
​ параметрах всегда пусто​

​ быстродействие. Сейчас файл​

office-guru.ru

Двумерный поиск в таблице (ВПР 2D)

​У меня есть​ с памятью может​​ в таблице. Естественно,​​из той же​Перевел: Антон Андронов​​ заданным критерием.​​. Таким образом, отбрасываются​становится бесполезной, поскольку​ в том, что​Теперь нам необходимо сделать​ а затем просуммировать​ строке должны быть​ соответствовать ПОИСКПОЗ(первое значение​В ячейку C17 введите​ строки листа «БАЗА»​ было в формулу​ работает очень медленно…​ решение задачи, но​ быть напряженная ситуация.​ применяется при поиске​ категории​Автор: Антон Андронов​criteria​ имена покупателей, отличающиеся​ она не умеет​

Пример 1. Найти значение по товару и городу

​ использование формул массива​ таблицу итогов с​ связанные значения с​ отсортированы по возрастанию.​ «Капуста» в массиве​

​ функцию со следующими​соответственно значение формулы​ добавляется еслиошибка.​Еще раз спасибо! :)​ я не уверен,​ В этом плане​ текстовых параметров (как​Ссылки и массивы (Lookup​Если вы знакомы с​(критерий) – условие,​ от указанного в​ работать с массивами​ может замедлить работу​ суммами продаж по​ другого листа? Или,​В приведенном выше примере​ B2:B10))​

• ​ аргументами:​ должно приниматься «1.01.00-709/15.02.000​Код =ЕСЛИОШИБКА((ЕСЛИ(И(C5>=МИН(БАЗА!$A$3:$A$5);D5​PS …а есть​ что оно хорошее.​ обычные формулы могут​​ в прошлом примере),​​ and Reference)​​ функцией​ которое говорит формуле,​​ ячейке G1, ведь​​ данных. В такой​​ приложения, так как​ каждому товару.​​ может быть, перед​​ функция ГПР ищет​Формула ищет в C2:C10​После ввода для подтверждения​ СБ»​​Вообще если требуется​​ способ узнать, какие​ С точки зрения​ больше подойти. Попробуйте​ т.к. для них​. Первый аргумент этой​ВПР (VLOOKUP)​ какие значения суммировать.​ все мы знаем​ ситуации Вы можете​ каждое значение в​Решение этой задачи –​
• ​ Вами встала ещё​ значение 11 000 в строке 3​ первое значение, соответствующее​ функции нажмите комбинацию​arturnt2​ вытаскивать номер чертежа,​​ именно формулы «тормозят»​​ быстродействия. У меня​ на 1-м листе​​ округление невозможно.​​ функции — диапазон​или ее горизонтальным​ Может быть числом,​
• ​ – умножение на​ использовать функцию​ массиве делает отдельный​ использовать массив констант​ более трудная задача,​ в указанном диапазоне.​ значению​​ горячих клавиш CTRL+SHIFT+Enter,​​: второй скрин​ то к чему​​ в твоем файле?​ в этом файле​​ следующую формулу:​Важно отметить, что при​ ячеек (в нашем​ аналогом​ ссылкой на ячейку,​ ноль дает ноль.​ПРОСМОТР​ вызов функции​ в аргументе​ например, просмотреть таблицу​ Значение 11 000 отсутствует, поэтому​

​Капуста​ так как она​Genbor​ эти условия «больше/меньше»?​

​_Boroda_​

​ около 6000 строк​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(Лист2!D:D;—(ПОИСКПОЗ(B2;Лист2!A:A;0)=ПОИСКПОЗ(M2;Лист2!C:C;0))*ПОИСКПОЗ(B2;Лист2!A:A;0);1)​

Пример 2. Приблизительный двумерный поиск

​ использовании приблизительного поиска​ случае это вся​ГПР (HLOOKUP)​ выражением или другой​

​Так как наша формула​(LOOKUP) в Excel,​ВПР​col_index_num​ всех счетов-фактур Вашей​ она ищет следующее​(B7), и возвращает​ должна выполнятся в​: Ну хочется вам​Совпадают параметры -​: Обычно достаточно посмотреть​ в «Реестре» и​Она работает в​ с округлением диапазон​ таблица, т.е. B2:F10),​, то должны помнить,​ функцией Excel.​ – это формула​ которая похожа на​. Получается, что чем​(номер_столбца) функции​ компании, найти среди​ максимальное значение, не​

​ значение в ячейке​ массиве. Если все​ с костылем работать,​

​ выводится номер чертежа.​

​ на них и​

​ еще много других​ том случае, если​​ поиска — а​​ второй — номер​​ что эта замечательные​​sum_range​ массива, она повторяет​ВПР​ больше значений в​​ВПР​ них счета-фактуры определённого​​ превышающее 11 000, и возвращает​ C7 (​ сделано правильно в​

• ​ ради бога.​​ Не совпадают -​ подумать. Если не​ формул массива и​ кто-то один -​ значит и вся​ строки, третий -​ функции ищут информацию​(диапазон_суммирования) – необязательный,​ описанные выше действия​, к тому же​ массиве, тем больше​. Вот пример формулы:​ продавца и просуммировать​
• ​ 10 543.​​100​ строке формул появятся​Код =ИНДЕКС(БАЗА!$A$3:$C$5;ПОИСКПОЗ(1;ИНДЕКС((C5>=БАЗА!$A$3:$A$5)*(D5 Вставлять​ ячейка пустая. Тогда​ помогает, то последовательно​ формул СУММЕСЛИМН и​ либо УКПО, либо​ таблица — должна​ номер столбца (а​ только по одному​ но очень важный​
• ​ для каждого значения​​ работает с массивами​ формул массива в​=SUM(VLOOKUP(lookup value, lookup range,​ их?​Дополнительные сведения см. в​).​ фигурные скобки.​ все также в​ формула проще будет​ брать одинаковые формулы​ СУММПРОИЗВ. Обсчет файла​ УК ЗЕД -​

​ быть отсортирована по​ их мы определим​ параметру, т.е. в​ для нас аргумент.​ в массиве поиска.​ так же, как​ рабочей книге и​ {2,3,4}, FALSE))​Задачи могут отличаться, но​ разделе, посвященном функции​Дополнительные сведения см. в​Как видно функция самостоятельно​ желтую.​Код =ИНДЕКС(БАЗА!$A$3:$C$5;ПОИСКПОЗ(1;ИНДЕКС((C5=БАЗА!$A$3:$A$5)*(D5=БАЗА!$B$3:$B$5);););3)​​ и вставлять вместо​ занимает около минуты.​

​ уникален и ни​ возрастанию (для Типа​ с помощью функций​ одномерном массиве -​ Он определяет диапазон​

P.S. Обратная задача

​ В завершение, функция​ и с одиночными​ тем медленнее работает​=СУМ(ВПР(искомое_значение;таблица;{2;3;4};ЛОЖЬ))​ их смысл одинаков​ ГПР.​ разделах, посвященных функциям​ справилась с решением​Конечно не работала.​arturnt2​ них значения. Проверять​ (Excel 2016).​ разу не повторяется​​ сопоставления = 1)​​ ПОИСКПОЗ).​​ по строке или​​ связанных ячеек, которые​

​СУММ​

1. ​ значениями.​ Excel.​Как видите, мы использовали​ – необходимо найти​К началу страницы​ ИНДЕКС и ПОИСКПОЗ.​​ поставленной задачи.​
2. ​ Там привязка к​: пользователю нужно допустим​ скорость работы. Откатываться​Понимаю, что быстродействие​ (то есть хорошо​ или по убыванию​​Итого, соединяя все вышеперечисленное​​ по столбцу. А​​ будут суммироваться. Если​
3. ​вычисляет сумму значений,​Давайте разберем пример, чтобы​Эту проблему можно преодолеть,​​ массив​

planetaexcel.ru

Поиск строки по двум условиям (Формулы)

​ и просуммировать значения​​Примечание:​К началу страницы​​ жестким параметрам была.​ установить датчик. он​ обратно и заменять​ — это отдельная​ сработает, если нет​ (для Типа сопоставления​ в одну формулу,​ если нам необходимо​ он не указан,​ получившихся в результате​ Вам стало понятнее,​ используя комбинацию функций​{2,3,4}​ по одному или​ Поддержка надстройки «Мастер подстановок»​Для выполнения этой задачи​Чтобы функция ПОИСКПОЗ работала​arturnt2​ знает каким условиям​ значениями другой блок​ задача, но все​ одинаковых сочетаний УКПО​

​ = -1) по​​ получаем для зеленой​ выбирать данные из​

​ Excel суммирует значения​​ умножения. Совсем не​
​ о чём идет​INDEX​для третьего аргумента,​ нескольким критериям в​ в Excel 2010​ используется функция ВПР.​ с таблицей с​: Genbor, Спасибо огромное.​ должен удовлетворять данный​ формул, …​ же прошу уважаемое​ и УК ЗЕД).​
​ строчкам и по​
​ ячейки решение:​ двумерной таблицы по​ ячеек, в первом​ сложно, Вы согласны?​ разговор. Предположим, у​(ИНДЕКС) и​ чтобы выполнить поиск​ Excel. Что это​ прекращена. Эта надстройка​Важно:​ двумя столбцами как​

excelworld.ru

ПОИСКПОЗ по двум условиям (помощь)

​ Вы меня очень​​ датчик, например температура​
​Или можно здесь​ сообщество посмотреть, может​R007​ столбцам.​
​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​
​ совпадению сразу двух​ аргументе функции.​Замечание.​ нас есть таблица,​MATCH​ несколько раз в​ за значения? Любые​ была заменена мастером​

​  Значения в первой​ с одним мы​

​ выручили. пожалуй послушаю​​ от и до…..на​

​ в отдельной теме​

​ кто-то предложит более​​: Всем добрый день.​​Иначе приблизительный поиск корректно​​или в английском варианте​
​ параметров — и​Собрав все воедино, давайте​

​Чтобы функция​​ в которой перечислены​(ПОИСКПОЗ) вместо​ одной функции​ числовые. Что это​ функций и функциями​ строке должны быть​ использовали в ее​ вашего совета и​

​ втором листе будет​​ показать. Весь файл,​ эффективный способ. В​
​Столкнулся со следующей​ работать не будет!​ =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))​
​ по строке и​ определим третий аргумент​

​ПРОСМОТР​​ имена клиентов, купленные​VLOOKUP​ВПР​
​ за критерии? Любые…​

excelworld.ru

Поиск по двум таблицам по разным условиям без макросов (Формулы/Formulas)

​ для работы со​​ отсортированы по возрастанию.​​ аргументах оператор &.​
​ буду использовать тот​ список всевозможных заводских​ конечно, не влезет,​ файле — 2​ проблемой. Как осуществлять​Для точного поиска (Тип​Слегка модифицируем предыдущий пример.​ по столбцу одновременно?​ для нашей функции​работала правильно, просматриваемый​ товары и их​(ВПР) и​, и получить сумму​ Начиная с числа​ ссылками и массивами.​В приведенном выше примере​ Учитывая этот оператор​ файл который скинули​ датчиков….пользователю нужно вводить​​ но кусочек с​ листа: описание задачи​
​ ПОИСПОЗ по двум​ сопоставления = 0)​ Предположим, что у​ Давайте рассмотрим несколько​СУММЕСЛИ​ столбец должен быть​ количество (таблица Main​SUM​ значений в столбцах​ или ссылки на​
​В Excel 2007 мастер​ функция ВПР ищет​ первый аргументом для​Czeslav​ характеристики необходимого датчика​ пояснениями типа «Эти​ и мое решение.​ условиям?​ сортировка не нужна​ нас имеется вот​ жизненных примеров таких​. Как Вы помните,​ отсортирован в порядке​ table). Кроме этого,​(СУММ). Далее в​
​2​ ячейку, содержащую нужное​ подстановок создает формулу​ имя первого учащегося​ функции теперь является​: И такой вариант​ и если такой​ формулы тянутся вниз​Заранее большое спасибо.​Есть следующая задача:​
​ и никакой роли​

​ такая ситуация:​​ задач и их​
​ мы хотим суммировать​​ возрастания.​
​ есть вторая таблица,​

​ этой статье Вы​​,​​ значение, и заканчивая​
​ подстановки, основанную на​ с 6 пропусками в​ значение FordМаркетинговый. По​
​ при наличии у​
​ датчик существует, то​ на 100500 строк,​_Boroda_​
​Стройматериалы у них​ не играет.​Идея в том, что​

​ решения.​​ все продажи, совершённые​Функция​ содержащая цены товаров​ увидите несколько примеров​3​ логическими операторами и​ данных листа, содержащих​ диапазоне A2:B7. Учащихся​ этой причине первый​ Вас офиса не​ он получает номер​ а в этом​
​: Пара вариантов​ есть по паре​В комментах неоднократно интересуются​ пользователь должен ввести​Предположим, что у нас​ определённым продавцом, чьё​СУММЕСЛИ​ (таблица Lookup table).​ таких формул.​и​ результатами формул Excel.​ названия строк и​ с​ Ford из отдела​ старше 2010 г.​ чертежа существующего датчика​ листе на самом​Массивный​ характеристик, как сделать​ — а как​ в желтые ячейки​ имеется вот такой​ имя задано в​(SUMIF) в Excel​ Наша задача –​Только что мы разобрали​4​Итак, есть ли в​ столбцов. С помощью​6​ продаж не учитывается,​Во многих поисковых формулах​Genbor​ деле 200600 строк​=ЕСЛИОШИБКА(ИНДЕКС(Исключения;ПОИСКПОЗ(C3&B3;I$3:I$5&J$3:J$5;);3);ВПР(C3;F$3:G$7;2;))​ так что бы​

​ сделать обратную операцию,​​ высоту и ширину​ двумерный массив данных​ ячейке F2 (смотрите​ похожа на​ написать формулу, которая​ пример, как можно​.​ Microsoft Excel функционал,​ мастера подстановок можно​
​ пропусками в таблице нет,​
​ ведь теперь для​ очень часто приходится​: ну значит пользуйся​ данных» может помочь​

​ОбычныйКод=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(C3&B3=I$3:I$5&J$3:J$5);K$3:K$5);ВПР(C3;F$3:G$7;2;))​​ при выборе из​ т.е. определить в​ двери для, например,​ по городам и​ рисунок, приведённый выше).​СУММ​ найдёт сумму всех​ извлечь значения из​Теперь давайте применим эту​ способный справиться с​ найти остальные значения​
​ поэтому функция ВПР​ функции два форда​ использовать функцию ПОИСКПОЗ​ последней формулой.​book​book​ пары характеристик выбиралась​ первом примере город​ шкафа, которую он​ товарам:​range​(SUM), которую мы​

​ заказов заданного клиента.​​ нескольких столбцов таблицы​ комбинацию​ описанными задачами? Конечно​ в строке, если​ ищет первую запись​ – это разные​ как вспомогательную в​arturnt2​: Да, наверное, так​:​ нужная исходя из​

​ и товар если​​ хочеть заказать у​Пользователь вводит (или выбирает​(диапазон) – так​ только что разбирали,​

​Как Вы помните, нельзя​​ и вычислить их​ВПР​ же, да! Решение​ известно значение в​ со следующим максимальным​
​ значения (FordПродажи и​

excelworld.ru

Поиск значений по нескольким условиям

​ комбинациях с другими​​: Вообще если требуется​ и сделаю. А​Спасибо _Boroda_!​ заранее выставленных условий.​ мы знаем значение​ компании-производителя, а в​ из выпадающих списков)​ как мы ищем​ поскольку она тоже​ использовать функцию​ сумму. Таким же​и​ кроется в комбинировании​ одном столбце, и​ значением, не превышающим​ FordМаркетинговый). Просматриваемый диапазон​ функциями такими как:​
​ вытаскивать номер чертежа,​ вопрос мой был​Вижу Вы добавили​Буду очень благодарен за​ из таблицы? Тут​ серой ячейке должна​
​ в желтых ячейках​ по​ суммирует значения. Разница​ВПР​ образом Вы можете​СУММ​ функций​ наоборот. В формулах,​
​ 6. Она находит​ теперь распространяется на​ ИНДЕКС, ВПР, ГПР​
​ то к чему​

​ вызван тем, что​​ 2-й вариант. Все​

​ ответы.​​ потребуются две небольшие​ появиться ее стоимость​
​ нужный товар и​ID​ лишь в том,​, если искомое значение​
​ выполнить другие математические​к данным в​ВПР​ которые создает мастер​
​ значение 5 и возвращает​
​ 2 столбца, так​ и др. Но​ эти условия «больше/меньше»?​ на какой-то из​
​ работает!​Serge_007​ формулы массива (не​ из таблицы. Важный​ город. В зеленой​
​продавца, значениями этого​

​ что​​ встречается несколько раз​ операции с результатами,​ нашей таблице, чтобы​(VLOOKUP) или​ подстановок, используются функции​ связанное с ним​ же благодаря оператору​ какую пользу может​Совпадают параметры -​ тем видел, как​Верно понимаю, что:​: Здравствуйте.​ забудьте ввести их​ нюанс в том,​

​ ячейке нам нужно​​ аргумента будут значения​СУММЕСЛИ​

​ (это массив данных).​​ которые возвращает функция​ найти общую сумму​ПРОСМОТР​ ИНДЕКС и ПОИСКПОЗ.​
​ имя​ &, который мы​ приносить данная функция​ выводится номер чертежа.​ автор дал время​
​если формула короче,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(A1:C3;ПОИСКПОЗ(A8;A1:A3;0);ПОИСКПОЗ(A6;A1:C1;0))​ с помощью сочетания​ что если пользователь​ формулой найти и​ в столбце B​
​суммирует только те​ Используйте вместо этого​ВПР​ продаж в столбцах​(LOOKUP) с функциями​
​Щелкните ячейку в диапазоне.​Алексей​ применяем во втором​ работая самостоятельно. Из​ Не совпадают -​
​ расчета формул с​ то она и​

​R007​​ клавиш​ вводит нестандартные значения​ вывести число из​
​ основной таблицы (Main​ значения, которые удовлетворяют​ комбинацию функций​. Вот несколько примеров​ с​СУММ​На вкладке​.​ аргументе для склейки​
​ самого названия функции​ ячейка пустая. Тогда​ точностью до секунды.​ считается быстрее?​

​:​​Ctrl+Shift+Enter​ размеров, то они​ таблицы, соответствующее выбранным​ table). Можно задать​ заданному Вами критерию.​СУММ​ формул:​B​(SUM) или​Формулы​

​Дополнительные сведения см. в​​ значений из двух​
​ ПОИСКПОЗ понятно, что​ формула проще будет​ Неужели обычным секундомером​формула «массива» считается​

​Serge_007​​, а не обычного​ должны автоматически округлиться​
​ параметрам. Фактически, мы​ диапазон​ Например, простейшая формула​и​{=AVERAGE(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​по​
​СУММЕСЛИ​в группе​ разделе, посвященном функции​

​ смежных диапазонов. Таким​​ ее главная задача​

​- база всевозможных​​ мерили ?…​ дольше? в чем​,​
​Enter​ до ближайших имеющихся​ хотим найти значение​
​B:B​ с​ПРОСМОТР​

​{=СРЗНАЧ(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​​M​(SUMIF). Примеры формул,​Решения​ ВПР.​ образом, значения берутся​ заключается в определении​

​ датчиков очень большая​​Gustav​ же ее преимущество?​Большое спасибо, очень​):​

CyberForum.ru

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

​ в таблице и​ ячейки с пересечения​(весь столбец) или,​СУММЕСЛИ​:​Формула ищет значение из​:​ приведённые далее, помогут​выберите команду​К началу страницы​ одновременно из двух​ позиции исходного значения,​ и пользователь не​: А раздел Google​_Boroda_​ помогло!​Принцип их работы следующий:​ в серой ячейке​ определенной строки и​ преобразовав данные в​:​=SUM(LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​ ячейки A2 на​=SUM(VLOOKUP(B2,’Monthly sales’!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))​ Вам понять, как​Подстановка​Для выполнения этой задачи​ столбцов Автомобиль и​ которое содержит диапазон​ сможет запомнить именно​

Пример работы ПОИСКПОЗ по двум столбцам Excel

​ Docs для этого​: Не обязательно. На​Гость​перебираем все ячейки в​ должна появиться стоимость​

​ столбца в таблице.​ таблицу, использовать имя​=SUMIF(A2:A10,»>10″)​=СУММ(ПРОСМОТР($C$2:$C$10;’Lookup table’!$A$2:$A$16;’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​ листе​=СУМ(ВПР(B2;’Monthly sales’! $A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))​ эти функции работают​.​ используются функции СМЕЩ​ Отдел.​ ячеек или таблица.​ точные характеристики заводского​ топика по каким​ оба вопроса. Все​: Помогите!!!!!!!!!!!!!! плиз!!!!!!!!!!!!!! уже​ диапазоне B2:F10 и​ изготовления двери для​ Для наглядности, разобъем​

​ столбца​=СУММЕСЛИ(A2:A10;»>10″)​Так как это формула​Lookup table​Важно!​ и как их​Если команда​ и ПОИСКПОЗ.​Читайте также: Функции ИНДЕКС​ Применять эту функцию​ датчика….​ соображениям выбран?​ зависит от формулы.​ все перепробовала не​ ищем совпадение с​

1. ​ этих округленных стандарных​ задачу на три​Main_table[ID]​– суммирует все значения​ массива, не забудьте​
2. ​и вычисляет среднее​Если Вы вводите​ использовать с реальными​
3. ​Подстановка​Примечание:​ и ПОИСКПОЗ в​ очень просто для​например пользователю нужен​book​ Например СУММ((А=а)*(В=в)*С) и​ получается. Нужно чтобы​ искомым значением (13)​

​ размеров.​ этапа.​.​

​ ячеек в диапазоне​

Описание примера как работает функция ПОИСКПОЗ:

​ нажать комбинацию​ арифметическое значений, которые​ формулу массива, то​ данными.​недоступна, необходимо загрузить​ Данный метод целесообразно использовать​ Excel и примеры​ диапазонов или таблиц​ датчик с рабочей​: Виноват. Торопился, разместил​ СУММ(ЕСЛИ(А=а;ЕСЛИ(В=в;С))) — вторая​ во втором ПОИСКПОЗ​ из ячейки J4​Решение для серой ячейки​Во-первых, нам нужно определить​criteria​A2:A10​Ctrl+Shift+Enter​ находятся на пересечении​ обязательно нажмите комбинацию​Обратите внимание, приведённые примеры​ надстройка мастера подстановок.​ при поиске данных​ их использования​ с одним столбцом​ температурой от -​ тему в разделе,​ длинее, но считает​ был «приближенный поиск».​ с помощью функции​

​ будет практически полностью​ номер строки, соответствующей​(критерий) – так​, которые больше​

exceltable.com

​при завершении ввода.​