В некоторых ситуациях может потребоваться поиск на основе значений в двух столбцах (рис. 122.1). Таблица содержит марки и модели автомобилей и соответствующие коды для каждого из них. Метод, описанный здесь, позволяет искать значение, основываясь на марке и модели автомобиля.
Рис. 122.1. В таблице выполняется поиск с использованием информации из двух столбцов (D и Е)
Лист использует именованные диапазоны, как показано в табл. 122.1.
| Диапазон | Название |
|---|---|
| F2:F12 | Код |
| B1 | Марка |
| B2 | Модель |
| D2:D12 | Диапазон1 |
| E2:E12 | Диапазон2 |
Следующая формула массива отображает соответствующий код для марки и модели автомобиля:
=ИНДЕКС(Код;ПОИСКПОЗ(Марка&Модель;Диапазон1&Диапазон2;0))
При вводе формулы массива нажмите Ctrl+Shift+Enter (а не просто Enter).
Формула объединяет содержимое диапазонов Марка и Модель, а затем находит этот текст в массиве, состоящем из соответствующего объединенного текста, в диапазонах Диапазон1 и Диапазон2.
Альтернативный подход заключается в создании новой таблицы поиска из двух столбцов, как показано на рис. 122.2. Таблица содержит ту же информацию, что и исходная таблица, но столбец Н включает объединенные данные из столбцов D и Е.
Рис. 122.2. Избегайте поиска по двум столбцам, комбинируя два столбца в один
После создания новой таблицы вы можете использовать простую формулу для выполнения поиска:
=ВПР(Марка&Модель;H2:I12;2)
Если вы знакомы с функцией ВПР (VLOOKUP) или ее горизонтальным аналогом ГПР (HLOOKUP), то должны помнить, что эта замечательные функции ищут информацию только по одному параметру, т.е. в одномерном массиве – по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по совпадению сразу двух параметров – и по строке и по столбцу одновременно? Давайте рассмотрим несколько жизненных примеров таких задач и их решения.
Пример 1. Найти значение по товару и городу
Предположим, что у нас имеется вот такой двумерный массив данных по городам и товарам:
Пользователь вводит (или выбирает из выпадающих списков) в желтых ячейках нужный товар и город. В зеленой ячейке нам нужно формулой найти и вывести число из таблицы, соответствующее выбранным параметрам. Фактически, мы хотим найти значение ячейки с пересечения определенной строки и столбца в таблице. Для наглядности, разобъем задачу на три этапа.
- Во-первых, нам нужно определить номер строки, соответствующей выбранному пользователем в желтой ячейке товару. Это поможет сделать функция ПОИСКПОЗ (MATCH) из категории Ссылки и массивы (Lookup and Reference). В частности, формула ПОИСКПОЗ(J2; A2:A10; 0) даст нам нужный результат (для Яблока это будет число 6). Первый аргумент этой функции – искомое значение (Яблоко из желтой ячейки J2), второй – диапазон ячеек, где мы ищем товар (столбец с товарами в таблице – A2:A10), третий аргумент задает тип поиска (0 – точное совпадение наименования, приблизительный поиск запрещен).
- Во-вторых, совершенно аналогичным способом мы должны определить порядковый номер столбца в таблице с нужным нам городом. Функция ПОИСКПОЗ(J3; B1:F1; 0) сделает это и выдаст, например, для Киева, выбранного пользователем в желтой ячейке J3 значение 4.
- И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца – функция ИНДЕКС (INDEX) из той же категории Ссылки и массивы (Lookup and Reference). Первый аргумент этой функции – диапазон ячеек (в нашем случае это вся таблица, т.е. B2:F10), второй – номер строки, третий – номер столбца (а их мы определим с помощью функций ПОИСКПОЗ).
Итого, соединяя все вышеперечисленное в одну формулу, получаем для зеленой ячейки решение:
=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))
или в английском варианте
=INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))
Пример 2. Приблизительный двумерный поиск
Слегка модифицируем предыдущий пример. Предположим, что у нас имеется вот такая ситуация:
Идея в том, что пользователь должен ввести в желтые ячейки высоту и ширину двери для, например, шкафа, которую он хочеть заказать у компании-производителя, а в серой ячейке должна появиться ее стоимость из таблицы. Важный нюанс в том, что если пользователь вводит нестандартные значения размеров, то они должны автоматически округлиться до ближайших имеющихся в таблице и в серой ячейке должна появиться стоимость изготовления двери для этих округленных стандарных размеров.
Решение для серой ячейки будет практически полностью аналогично предыдущему примеру:
=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))
=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))
Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH) – Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) – Интервального просмотра (Range Lookup). Вообще говоря, возможных значений для него три:
- 1 – поиск ближайшего наименьшего числа, т.е. введенные пользователем размеры двери округлялись бы до ближайших наименьших подходящих размеров из таблицы. В нашем случае высота 500 округлилась бы до 450, а ширина 480 до 300, и стоимость двери была бы 135.
- -1 – поиск ближайшего наибольшего числа, т.е. нестандартная высота 500 округлялась бы до 700, а ширина 480 – до 600 и стоимость составила бы уже 462. Для бизнеса так гораздо интереснее! 🙂
- 0 – поиск точного соответствия без каких либо округлений. Используется для 100%-го совпадения искомого значения с одним из значений в таблице. Естественно, применяется при поиске текстовых параметров (как в прошлом примере), т.к. для них округление невозможно.
Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска – а значит и вся таблица – должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам. Иначе приблизительный поиск корректно работать не будет!
Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.
P.S. Обратная задача
В комментах неоднократно интересуются – а как сделать обратную операцию, т.е. определить в первом примере город и товар если мы знаем значение из таблицы? Тут потребуются две небольшие формулы массива (не забудьте ввести их с помощью сочетания клавиш Ctrl+Shift+Enter, а не обычного Enter):
Принцип их работы следующий:
- перебираем все ячейки в диапазоне B2:F10 и ищем совпадение с искомым значением (13) из ячейки J4 с помощью функции ЕСЛИ (IF)
- когда нашли совпадение, то определяем номер строки (столбца) первого элемента в таблице в этой строке (столбце) с помощью функций СТОЛБЕЦ (COLUMN) и СТРОКА (ROW)
- выдергиваем значение города или товара из таблицы с помощью функции ИНДЕКС (INDEX)
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Динамическая выборка из списка функциями ИНДЕКС и ПОИСКПОЗ
- Улучшаем функцию ВПР (VLOOKUP2)
- ВПР (VLOOKUP) с учетом регистра
- Многоразовый ВПР для вывода сразу всех значений
В Excel есть два столбца, в первом “Да” и “Нет”, во втором пустые ячейки и какие-то значения. Как посчитать количество “Нет”, напротив которых пустые ячейки второго столбца?
Андрей Д.
15 июля 2020 · 8,5 K
Используйте СЧЕТЕСЛИМН
=СЧЕТЕСЛИМН(первый столбец;нужное значение в 1 столбце;второй столбец;нужное значение в втором столбце… ну и так далее). Подсчитает количество строк, где везде содержатся нужные значения.Для вашего случая:
=СЧЁТЕСЛИМН(B2:B16;”да”;C2:C16;””)
в B2:B16 ищет слово “да”, и если найдет, то проверяет, пустая ли ячейка рядом в диапазоне C2:C16. Если это так, то эта строчка, а значит и ячейка с словом “да” будет подсчитана. И НИКАКИХ ДОПОЛНИТЕЛЬНЫХ СТОЛБЦОВ И ДЛИННЫХ ФОРМУЛ
https://office-study.xyz
718
Комментировать ответ…Комментировать…
На видео показываю два способа
1 – через вспомогательный столбец, в котором пишем формулу
=ЕСЛИ(И(A2=”нет”;B2=””);”да”;””)
2 – через… Открыть описание
3,4 K
Комментировать ответ…Комментировать…
Просто решил “прокачаться” в Excel и помочь кому могу) · 16 июл 2020
Думаю вам нужна формула “=СЧЁТЕСЛИМН”, первым указываете столбец в котором указано “Нет”, далее указываете второй столбец и что ищем.. “” – пустота,и готово Читать далее
424
Комментировать ответ…Комментировать…
Download Article
Download Article
Excel remains one of the most powerful tools in the Microsoft Office Suite, but it can be understandably daunting as well. Fortunately, we have broken down one of Excel’s most essential features into just a few simple steps. This wikiHow article will teach you how to find matching values in two columns in Excel.
-
1
Select the columns you would like to compare. Using conditional formatting in Excel will allow you to automatically highlight any matching values across multiple columns. Click and drag your mouse over the columns you would like to compare.
- If the two columns are not side by side, simply hold down Ctrl and select whichever columns you need.
-
2
Click Conditional Formatting from the “Home” tab. This will open up a drop-down menu with various additional options.
Advertisement
-
3
Select Highlight Cells Rule and then Duplicate Values. This setting tells Excel that you want your conditional formatting to detect values that are duplicated (i.e., match) across your selected columns. [1]
-
4
Click OK on the pop-up window. After selecting your conditional formatting settings, Excel will show you a pop-up window. Ensure the window reads Duplicate in the left-hand box, and click “OK.”
- The other box in the pop-up window allows you to change the colors Excel uses to indicate duplicates. The default is “Light Red Fill with Dark Red Text”, but you may choose whichever you prefer.
-
5
Identify the matching values. Excel will now highlight any duplicates with the formatting you chose in the previous pop-up box. Look for this colored formatting and identify any matches.
- Using conditional formatting to find matching values is a handy way to find matches that may not be in the same row.
Advertisement
-
1
Create a third column next to your two columns of data. The VLOOKUP function involves using a specific formula to find matching values. You’ll need a third column to input the formula and display any matches.
-
2
Enter the VLOOKUP formula into the first row of the third column. Assuming your data begins from the top-left corner of your spreadsheet, the formula is as follows: =VLOOKUP(B1,$A$1:$A$17,1,FALSE).
- The “17” in the formula indicates 17 rows of data. Change the number to fit however many rows of data you have.
- The “FALSE” value at the end of the formula is what tells Excel to look for an exact match in value. Replace it with “TRUE” to search for the nearest match that is less than or equal to the corresponding data point (represented in this case by B1). [2]
- Just entering “=VLOOKUP” in Excel will pull up the full formula, which you can reference in populating each field with the necessary info.
-
3
Copy the VLOOKUP formula all the way down. Drag down from the corner of the first box to your final row of data to copy the formula. Excel will automatically change the first value to the corresponding data point in that row. [3]
-
4
Look for matching values in your third column. If there are any matching values, they will display as a number in your spreadsheet’s third column. If there are no matching values, the VLOOKUP formula will simply turn up “#N/A”.
Advertisement
-
1
Create a third column next to your two columns of data. This method involves using a specific formula to find matching values. You’ll need a third column to input the formula and display its results.
-
2
Enter the TRUE/FALSE formula into the third column. Assuming your data begins from the top-left corner of your spreadsheet, the formula is as follows: =A1=B1.
-
3
Copy the formula all the way down. Drag down from the corner of the first box to your final row of data to copy the formula. Excel will automatically change the values to the corresponding data points in that row.
-
4
Look for a “TRUE” or “FALSE” assessment in the third column. Matching values will turn up a “TRUE” value. If there is no match, the box in the third column will read “FALSE.”
Advertisement
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
Advertisement
About This Article
Article SummaryX
1. Use conditional formatting to highlight matching values.
2. Use VLOOKUP or a TRUE/FALSE formula to display matching values in a new column.
Did this summary help you?
Thanks to all authors for creating a page that has been read 44,947 times.
Is this article up to date?
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
|
A |
B |
C |
D |
E |
||
|
1 |
Имя |
Правитель |
Возраст |
Поиск значения |
||
|
2 |
Анри |
501 |
Плот |
Иванов |
||
|
3 |
Стэн |
201 |
19 |
|||
|
4 |
Иванов |
101 |
максималь |
|||
|
5 |
Ларри |
301 |
составляет |
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
|
Термин |
Определение |
Пример |
|
Массив таблиц |
Вся таблица подстановки |
A2: C5 |
|
Превышающ |
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица». |
E2 |
|
Просматриваемый_массив |
Диапазон ячеек, которые содержат возможные значения подстановки. |
A2: A5 |
|
Номер_столбца |
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение. |
3 (третий столбец в инфо_таблица) |
|
Ресулт_аррай |
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор. |
C2: C5 |
|
Интервальный_просмотр |
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение. |
ЛОЖЬ |
|
Топ_целл |
Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?. |
|
|
Оффсет_кол |
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение “5” в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки). |
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе “образец”.
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как “Мария” находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Использование функции Просмотр в Excel
ВПР ()
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе “образец”.
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется “3” в качестве Колумн_индекс (столбец C). Так как “Мария” находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
Как найти точное совпадение с помощью функций ВПР или ГПР
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе “образец”.
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как “Мария” находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
Обратите внимание Если ни одна из ячеек в аргументе “число” не соответствует искомому значению (“Мария”), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:
Поиск данных в таблице с помощью функции индекс
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе “образец”.
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как “Мария” находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Использование функции СМЕЩ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
