Поиск значений в списке данных
Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что необходимо сделать
-
Точное совпадение значений по вертикали в списке
-
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
-
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
-
Точное совпадение значений по горизонтали в списке
-
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
-
Создание формулы подступа с помощью мастера подметок (только в Excel 2007)
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ
Дополнительные сведения см. в этой информации.
Примеры индексов и совпадений
Что означает:
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее значению «Ольга» (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению «Ольга».
Дополнительные сведения см. в функциях ИНДЕКС иФУНКЦИЯ MATCH.
К началу страницы
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Для этого используйте функцию ВЛВП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.
Дополнительные сведения см. в этой информации.
К началу страницы
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.
Примечание: Используйте этот подход, если данные в диапазоне внешних данных обновляются каждый день. Вы знаете, что цена находится в столбце B, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (также называемые начальной).
MATCH(«Оранжевая»;C2:C7;0) ищет «Оранжевые» в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.
1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.
К началу страницы
Точное совпадение значений по горизонтали в списке
Для этого используйте функцию ГГПУ. См. пример ниже.
Г ПРОСМОТР ищет столбец «Продажи» и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
К началу страницы
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Для этого используйте функцию ГГПУ.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
К началу страницы
Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )
Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).
В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.
-
Щелкните ячейку в диапазоне.
-
На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
-
Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов.
Загрузка надстройки «Мастер подстройок»
-
Нажмите кнопку Microsoft Office
, выберите Параметры Excel и щелкните категорию Надстройки. -
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
-
В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
-
Следуйте инструкциям мастера.
, выберите Параметры Excel и щелкните категорию Надстройки.К началу страницы
Нужна дополнительная помощь?
Skip to content
В статье предлагается несколько различных формул для выполнения поиска в двумерном массиве значений Excel. Просмотрите эти варианты и выберите наиболее для вас подходящий.
При поиске данных в электронных таблицах Excel чаще всего вы будете искать вертикально в столбцах или горизонтально в строках. Но иногда вам нужно просматривать сразу два условия – как строки, так и столбцы. Другими словами, вы стремитесь найти значение на пересечении определенной строки и столбца. Это называется матричным поиском (также известным как двумерный или поиск в диапазоне). Далее показано, как это можно сделать различными способами.
- Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ
- Формула ВПР и ПОИСКПОЗ для поиска в диапазоне
- Функция ПРОСМОТРX для поиска в строках и столбцах
- Формула СУММПРОИЗВ для поиска по строке и столбцу
- Поиск в матрице с именованными диапазонами
Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ
Самый популярный способ выполнить двусторонний поиск в Excel — использовать комбинацию ИНДЕКС с двумя ПОИСКПОЗ. Это разновидность классической формулы ПОИСКПОЗ ИНДЕКС , к которой вы добавляете еще одну функцию ПОИСКПОЗ, чтобы получить номера строк и столбцов:
ИНДЕКС( массив_данных ; ПОИСКПОЗ( значение_вертикальное ; диапазон_поиска_столбец ; 0), ПОИСКПОЗ( значение_горизонтальное ; диапазон_поиска_строка ; 0))
В этом способе, как и во всех остальных, мы используем поиск по двум условиям. Первое из них должно обнаружить совпадение в определенном столбце (в заголовках строк), а второе – в определенной строке (то есть, в заголовках столбцов). В результате мы имеем строку и столбец, которые соответствуют заданным условиям. А на пересечении их как раз и будут находиться искомые данные.
В качестве примера составим формулу для получения количества проданного товара за определённый период времени из таблицы, которую вы можете видеть ниже. Для начала определим все аргументы:
- Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
- Значение_вертикальное — H1 (целевой товар)
- Диапазон_поиска_столбец – A2:A11 (заголовки строк: названия напитков)
- Значение_горизонтальное — H2 (целевой период)
- Диапазон_поиска_строка — B1:E1 (заголовки столбцов: временные периоды)
Соедините все аргументы вместе, и вы получите следующую формулу для поиска числа в диапазоне:
=ИНДЕКС(B2:E11; ПОИСКПОЗ(H1;A2:A11;0); ПОИСКПОЗ(H2;B1:E1;0))
Как работает эта формула?
Хотя на первый взгляд это может показаться немного сложным, логика здесь простая. Функция ИНДЕКС извлекает значение из массива данных на основе номеров строк и столбцов, а две функции ПОИСКПОЗ предоставляют ей эти номера:
ИНДЕКС( B2:E11; номер_строки ; номер_столбца )
Здесь мы используем способность ПОИСКПОЗ возвращать относительную позицию значения в искомом массиве .
Итак, чтобы получить номер строки, мы ищем нужный нам товар (H1) в заголовках строк (A2:A11):
ПОИСКПОЗ(H1;A2:A11;0)
Чтобы получить номер столбца, мы ищем нужную нам неделю (H2) в заголовках столбцов (B1:E1):
ПОИСКПОЗ(H2;B1:E1;0)
В обоих случаях мы ищем точное совпадение, присваивая третьему аргументу значение 0.
В этом примере первое ПОИСКПОЗ возвращает 2, потому что нужный товар (Sprite) находится в ячейке A3, которая является второй по счёту в диапазоне A2:A11. Второй ПОИСКПОЗ возвращает 3, так как «Неделя 3» находится в ячейке D1, которая является третьей ячейкой в B1:E1.
С учетом вышеизложенного формула сводится к:
ИНДЕКС(B2:E11; 2 ; 3 )
Она возвращает число на пересечении второй строки и третьего столбца в матрице B2:E4, то есть в ячейке D3.
Думаю, вы понимаете, что аналогичным образом можно производить поиск в двумерном массиве Excel не только числа, но и текста. Тип данных здесь не имеет значения.
Формула ВПР и ПОИСКПОЗ для поиска в диапазоне
Другой способ выполнить матричный поиск в Excel — использовать комбинацию функций ВПР и ПОИСКПОЗ:
ВПР( значение_вертикальное ; массив_данных ; ПОИСКПОЗ( значение_горизонтальное , диапазон_поиска_строка , 0), ЛОЖЬ)
Для нашего образца таблицы формула принимает следующий вид:
=ВПР(H1; A2:E11; ПОИСКПОЗ(H2;A1:E1;0); ЛОЖЬ)
Где:
- Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
- Значение_вертикальное — H1 (целевой товар)
- Значение_горизонтальное — H2 (целевой период)
- Диапазон_поиска_строка — А1:E1 (заголовки столбцов: временные периоды)
Основой формулы является функция ВПР, настроенная на точное совпадение (последний аргумент имеет значение ЛОЖЬ). Она ищет заданное значение (H1) в первом столбце массива (A2:E11) и возвращает данные из другого столбца в той же строке. Чтобы определить, из какого столбца вернуть значение, вы используете функцию ПОИСКПОЗ, которая также настроена на точное совпадение (последний аргумент равен 0):
ПОИСКПОЗ(H2;A1:E1;0)
ПОИСКПОЗ ищет текст из H2 в заголовках столбцов (A1:E1) и указывает относительное положение найденной ячейки. В нашем случае нужная неделя (3-я) находится в D1, которая является четвертой по счету в массиве поиска. Итак, число 4 идет в аргумент номер_столбца функции ВПР:
=ВПР(H1; A2:E11; 4; ЛОЖЬ)
Далее ВПР находит точное совпадение H1 со значением в A3 и возвращает значение из 4-го столбца в той же строке, то есть из ячейки D3.
Важное замечание! Чтобы формула работала корректно, диапазон_поиска (A2:E11) функции ВПР и диапазон_поиска (A1:E1) функции ПОИСКПОЗ должны иметь одинаковое количество столбцов. Иначе число, переданное в номер_столбца, будет неправильным (не будет соответствовать положению столбца в массиве данных).
Функция ПРОСМОТРX для поиска в строках и столбцах
Недавно Microsoft представила еще одну функцию в Excel, которая призвана заменить все существующие функции поиска, такие как ВПР, ГПР и ИНДЕКС+ПОИСКПОЗ. Помимо прочего, ПРОСМОТРX может смотреть на пересечение определенной строки и столбца:
ПРОСМОТРX( значение_вертикальное ; диапазон_поиска_столбец ; ПРОСМОТРX( значение_горизонтальное ; диапазон_поиска_строка ; массив_данных ))
Для нашего примера набора данных формула выглядит следующим образом:
=ПРОСМОТРX(H1; A2:A11; ПРОСМОТРX(H2; B1:E1; B2:E11))
Примечание. В настоящее время ПРОСМОТРX — это функция, доступная только подписчикам Office 365 и более поздних версий.
В формуле используется функция ПРОСМОТРX для возврата всей строки или столбца. Внутренняя функция ищет целевой период времени в строке заголовка и возвращает все значения для этой недели (в данном примере для 3-й). Эти значения переходят в аргумент возвращаемый_массив внешнего ПРОСМОТРX:
=ПРОСМОТРX(H1; A2:A11; {544:87:488:102:87:433:126:132:111:565})
Внешняя функция ПРОСМОТРX ищет нужный товар в заголовках столбцов и извлекает значение из той же позиции из возвращаемого_массива.
Формула СУММПРОИЗВ для поиска по строке и столбцу
Функция СУММПРОИЗВ чрезвычайно универсальна — она может делать множество вещей, выходящих за рамки ее предназначения, особенно когда речь идет об оценке нескольких условий.
Чтобы найти значение на пересечении определенных строки и столбца, используйте эту общую формулу:
СУММПРОИЗВ ( диапазон_поиска_столбец = значение_вертикальное ) * ( диапазон_поиска_строка = значение_горизонтальное), массив_данных )
Чтобы выполнить поиск данных в массиве по строке и столбцу в нашем наборе данных, формула выглядит следующим образом:
=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2); B2:E11)
Приведенный ниже вариант также будет работать:
=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2)*B2:E11)
Теперь поясним подробнее. В начале мы сравниваем два значения поиска с заголовками строк и столбцов (целевой товар в H1 со всеми наименованиями в A2: A11 и целевой период времени в H2 со всеми неделями в B1: E1):
(A2:A11=H1)*(B1:E1=H2)
Это дает нам два массива значений ИСТИНА и ЛОЖЬ, где ИСТИНА означает совпадения:
{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}) * ({ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}
Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 и создает матрицу из 4 столбцов и 10 строк (строки разделяются двоеточием, а каждый столбец данных — точкой с запятой):
{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0}
Функция СУММПРОИЗВ умножает элементы приведенного выше массива на элементы B2:E4, находящихся в тех же позициях:
{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0; 0;0:0;0;0;0:0;0;0;0:0;0;0;0} * {455;345;544;366:65;77;87;56:766; 655;488;865:129;66;102;56:89;141;87;89:566;511;433;522:154; 144;126; 162:158;165;132;155:112;143;111; 125:677;466;565;766})
И поскольку умножение на ноль дает в результате ноль, остается только элемент, соответствующий 1 в первом массиве:
=СУММПРОИЗВ({0;0;0;0:0;0;87;0:0;0;0;0:0;0;0;0:0;0;0;0:0; 0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0})
Наконец, СУММПРОИЗВ складывает все элементы результирующего массива и возвращает значение 87.
Примечание . Если в вашей таблице несколько заголовков строк и/или столбцов с одинаковыми именами, итоговый массив будет содержать более одного числа, отличного от нуля. И все эти числа будут суммированы. В результате вы получите сумму значений, удовлетворяющую обоим критериям. Это то, что отличает формулу СУММПРОИЗВ от ПОИСКПОЗ и ВПР, которые возвращают только первое найденное совпадение.
Поиск в матрице с именованными диапазонами
Еще один достаточно простой способ поиска в массиве в Excel — использование именованных диапазонов. Рассмотрим пошагово:
Шаг 1. Назовите столбцы и строки
Самый быстрый способ назвать каждую строку и каждый столбец в вашей таблице:
- Выделите всю таблицу (в нашем случае A1:E11).
- На вкладке « Формулы » в группе « Определенные имена » щелкните « Создать из выделенного » или нажмите комбинацию клавиш Ctrl + Shift + F3.
- В диалоговом окне « Создание имени из выделенного » выберите « в строке выше » и « в столбце слева» и нажмите «ОК».
Это автоматически создает имена на основе заголовков строк и столбцов. Однако есть пара предостережений:
- Если ваши заголовки столбцов и/или строк являются числами или содержат определенные символы, которые не разрешены в именах Excel, то имена для таких столбцов и строк не будут созданы. Чтобы просмотреть список созданных имен, откройте Диспетчер имен (
Ctrl + F3). Если некоторые имена отсутствуют, определите их вручную. - Если некоторые из ваших заголовков строк или столбцов содержат пробелы, то они будут заменены символами подчеркивания, например, Неделя_1.
Шаг 2. Создание формулы поиска по матрице
Чтобы получить значение из матрицы на пересечении определенной строки и столбца, просто введите одну из следующих общих формул в пустую ячейку:
=имя_строки имя_столбца
Или наоборот:
=имя_столбца имя_строки
Например, чтобы получить продажу Sprite в 3-й неделе, используйте выражение:
=Sprite неделя_3
То есть, имена диапазонов здесь разделены пробелом, который в данном случае является оператором пересечения массивов.
Если кому-то нужны более подробные инструкции, опишем весь процесс пошагово:
- В ячейке, в которой вы хотите отобразить результат, введите знак равенства (=).
- Начните вводить имя целевой строки, Sprite. После того, как вы введете пару символов, Excel отобразит все существующие имена, соответствующие вашему вводу. Дважды щелкните нужное имя, чтобы ввести его в формулу.
- После имени строки введите пробел , который в данном случае работает как оператор пересечения.
- Введите имя целевого столбца ( в нашем случае неделя_3 ).
- Как только будут введены имена строки и столбца, Excel выделит соответствующую строку и столбец в вашей таблице, и вы нажмете Enter, чтобы завершить ввод:
Ваш поиск нужной ячейки в массиве выполнен, найден результат 87.
Вот какими способами можно выполнять поиск в массиве значений – в строках и столбцах таблицы Excel. Я благодарю вас за чтение и надеюсь еще увидеть вас в нашем блоге.
Еще несколько материалов по теме:
Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft… Поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям — В статье показано, как выполнять быстрый поиск с несколькими условиями в Excel с помощью ИНДЕКС и ПОИСКПОЗ. Хотя Microsoft Excel предоставляет специальные функции для вертикального и горизонтального поиска, опытные пользователи… ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР — В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР. В нескольких недавних статьях мы приложили немало усилий, чтобы объяснить основы функции ВПР новичкам и предоставить… 
Поиск в массиве при помощи ПОИСКПОЗ — В этой статье объясняется с примерами формул, как использовать функцию ПОИСКПОЗ в Excel. Также вы узнаете, как улучшить формулы поиска, создав динамическую формулу с функциями ВПР и ПОИСКПОЗ. В Microsoft… 
Функция ИНДЕКС в Excel — 6 примеров использования — В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место… 
Функция СУММПРОИЗВ с примерами формул — В статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, расчета средневзвешенного значения… 
Средневзвешенное значение — формула в Excel — В этом руководстве демонстрируются два простых способа вычисления средневзвешенного значения в Excel — с помощью функции СУММ (SUM) или СУММПРОИЗВ (SUMPRODUCT в английском варианте). В одной из предыдущих статей мы…
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Пусть Исходный список значений (например, перечень инструментов) находится в диапазоне
A10:A19
(см.
Файл примера
).
Задача
Выведем в отдельный диапазон значения, которые удовлетворяют критерию. Рассмотрим различные варианты поиска.
А. Найти значения, которые содержат критерий
Для удобства создадим
именованный диапазон
Список
.
Диапазон может охватить в том числе и незаполненные ячейки перечня. В дальнейшем пользователь может расширить перечень инструментов, указанные ниже формулы автоматически учтут новые значения.
Выведем в отдельный диапазон все значения Исходного списка, в которых
содержится
текст-критерий (например, слово
дрель
). Критерий вводится в ячейку
С6
.
Для создания списка, содержащего найденные значения, воспользуемся
формулой массива
:
=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))
Алгоритм работы формулы следующий (для просмотра промежуточных шагов работы формулы воспользуйтесь
клавишей
F9
):
-
Функция
ПОИСК()
, перебирая элементы исходного списка, определяет, содержится ли в нем значение-критерий. Если значение не содержится, то возвращается ошибка #ЗНАЧ! В противном случае возвращается числовое значение, соответствующее номеру начальной позиции вхождения критерия в значение из списка (здесь нам не важен номер позиции, важно, что это число); -
Функция
ЕСЛИОШИБКА()
используется для подавления ошибки #ЗНАЧ! заменяя ее на число 0; -
Функция
ЕСЛИ()
заменяет числовые значения, возвращенные функцией
ПОИСК()
, на номер позиции значения в списке. Если значение =0 (соответствует ошибке #ЗНАЧ!), то возвращается число 30. В принципе, вместо 30 можно указать любое число, которое больше номера последней заполненной позиции Исходного списка (это нужно для правильной сортировки функцией
НАИМЕНЬШИЙ()
); -
Функция
НАИМЕНЬШИЙ()
сортирует массив номеров строк по возрастанию; -
Функция
ДВССЫЛ()
возвращаетмассив последовательных чисел
;
-
Функция
ИНДЕКС()
возвращает текстовые значения из Исходного списка, из строк, номера которых были получены на предыдущем шаге.
В предельном случае м.б. найдено столько же значений, сколько содержится в исходном списке (когда все значения удовлетворяют критерию). Поэтому
формулу массива
нужно распространять на диапазон той же размерности, что и исходный список. Вышеуказанная
формула массива будет возвращать несколько значений
, поэтому перед вводом формулы нужно выделить сразу весь диапазон, т.е. ячейки
С10:С19
, ввести формулу в
Строке формул
и нажать
CRTL+SHIFT+ENTER
.
Для скрытия ошибок #ССЫЛКА!, возвращаемой формулой массива, к диапазону
С10:С19
применено правило
Условного форматирования
.
Б. Найти значения, которые совпадают с критерием (точное совпадение)
В этом случае будут выведены все значения, которые совпадают с критерием (без
учета РЕгиСТра
). Критерий вводится в ячейку
E
6
.
Для создания списка, содержащего найденные значения, воспользуемся
формулой массива
:
=ИНДЕКС(Список; НАИМЕНЬШИЙ( ЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))
B. Найти значения, которые начинаются с критерия
В этом случае будут выведены все значения, которые начинаются или совпадают с критерием. Критерий вводится в ячейку
G
6
.
Для создания списка, содержащего найденные значения, воспользуемся
формулой массива
: =
ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))
Г. Найти значения, которые заканчиваются на критерий
В этом случае будут выведены все значения, которые заканчиваются или совпадают с критерием. Критерий вводится в ячейку
I
6
.
Для создания списка, содержащего найденные значения, воспользуемся
формулой массива
:
=ИНДЕКС(Список;НАИМЕНЬШИЙ( ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30); СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))
СОВЕТ:
О поиске текстовых значений с использованием
подстановочных знаков
читайте в статье
Поиск текстовых значений в списках. Часть2. Подстановочные знаки
. В статье
Выделение ячеек c ТЕКСТом с применением Условного форматирования
приведено решение аналогичной задачи с использованием
Условного форматирования
.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Смотрите также Excel: определение в каких сделано правильно в (столбца) первого элемента округлялась бы до получаем для зеленойВо-первых, нам нужно определить
в столбце и название месяца Июнь
анализа отчета по
появятся фигурные скобки.
-
аналогичной задачи с значений, поэтому перед найденные значения, воспользуемся небольшой список, в. Формула возвращает ценуДля поиска значения вЕсли необходимо изменить условия именно строках диапазона
-
строке формул появятся в таблице в 700, а ширина ячейки решение:
номер строки, соответствующей строке Excel – это значение продажам.В ячейку F1 введите использованием Условного форматирования. вводом формулы нужно
-
формулой массива: котором искомое значение на другую деталь, большом списке можно формулы так, чтобы
находится каждое минимальное фигурные скобки. этой строке (столбце) 480 — до=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0)) выбранному пользователем вЧитайте также: Поиск значения будет использовано вНапример, как эффектно мы вторую формулу:Имеем таблицу, в которой выделить сразу весь=ИНДЕКС(Список; НАИМЕНЬШИЙ( (Воронеж) не находится потому что функция использовать функцию просмотра. можно было в значение. Это намОбратите внимание ниже на
с помощью функций 600 и стоимостьили в английском варианте желтой ячейке товару. в диапазоне таблицы качестве поискового критерия. отобразили месяц, вСнова Для подтверждения нажмите записаны объемы продаж диапазон, т.е. ячейкиЕСЛИ(ЕСЛИОШИБКА(ПОИСК($C$6;Список);0)>0;СТРОКА(Список)-СТРОКА($A$9);30); в крайнем левом ВПР нашла ближайшее Функция ВПР часто Excel выбрать первое необходимо по причине рисунок, где вСТОЛБЕЦ (COLUMN) составила бы уже =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0)) Это поможет сделать Excel по столбцам
В ячейку D2 введите котором была максимальная комбинацию клавиш CTRL+SHIFT+Enter. определенных товаров вС10:С19СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))) столбце. Поэтому мы число, меньшее или используется, но можно максимальное, но меньше определения именно первого ячейку B3 былаи 462. Для бизнесаСлегка модифицируем предыдущий пример. функция и строкам формулу:
продажа, с помощьюНайдено в каком месяце разных месяцах. Необходимо, ввести формулу вАлгоритм работы формулы следующий не можем использовать равное указанному (2345678). задействовать и функции чем 70: наименьшего значения. Реализовывается введена данная формулаСТРОКА (ROW) так гораздо интереснее!
Предположим, что уПОИСКПОЗ (MATCH)По сути содержимое диапазонаДля подтверждения после ввода
Использование функции ГПР
второй формулы. Не и какая была в таблице найти Строке формул и (для просмотра промежуточных функцию ВПР. Для Эта ошибка может ГПР, ИНДЕКС и
Одновременное использование функций ИНДЕКС и ПОИСКПОЗ
=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ class=’formula’> данная задача с в массиве:выдергиваем значение города или :) нас имеется вотиз категории нас вообще не формулы нажмите комбинацию сложно заметить что наибольшая продажа Товара данные, а критерием нажать шагов работы формулы поиска значения «Воронеж» привести к неправильному
ПОИСКПОЗ.Как в Excel выбрать помощью функции СТРОКА,Выборка соответственного значения с товара из таблицы0 такая ситуация:Ссылки и массивы (Lookup интересует, нам нужен клавиш CTRL+SHIFT+Enter, так во второй формуле 4 на протяжении поиска будут заголовкиCRTL+SHIFT+ENTER воспользуйтесь клавишей в диапазоне B1:B11 выставлению счета клиенту.Общий вид функции ВПР первое минимальное значение она заполняет элементы первым наименьшим числом: с помощью функции
Еще о функциях поиска
-
- поиск точногоИдея в том, что
-
and Reference) просто счетчик строк.
-
как формула будет мы использовали скелет
двух кварталов.
support.office.com
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
строк и столбцов..F9 будет использоваться функцияЕсли для аргумента «приблизительное и ее аргументов: кроме нуля: массива в памятиС такой формулой намИНДЕКС (INDEX) соответствия без каких пользователь должен ввести
. В частности, формула То есть изменить выполнена в массиве. первой формулы безВ первом аргументе функции
Задача
Но поиск долженДля скрытия ошибок #ССЫЛКА!,): ПОИСКПОЗ. Оно найдено
А. Найти значения, которые содержат критерий
соответствие» указано значение=ВПР(;;;)
Как легко заметить, эти программы номерами строк удалось выбрать минимальноеЕсли приходиться работать с либо округлений. Используется в желтые ячейкиПОИСКПОЗ(J2; A2:A10; 0) аргументы на: СТРОКА(B2:B11)
А в строке функции МАКС. Главная ВПР (Вертикальный ПРосмотр) быть выполнен отдельно возвращаемой формулой массива,Функция ПОИСК(), перебирая элементы в строке 4. ЛОЖЬ или 0,Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).
формулы отличаются между листа. Но сначала значение относительно чисел.
большими таблицами определенно
для 100%-го совпадения
высоту и ширину
даст нам нужный или СТРОКА(С2:С11) – формул появятся фигурные структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). указывается ссылка на по диапазону строки
- к диапазону исходного списка, определяет, Затем функция ИНДЕКС а точного совпаденияПервый аргумент (часть, необходимая собой только функциями от всех этих Далее разберем принцип найдете в них искомого значения с двери для, например, результат (для это никак не скобки. Мы заменили функцию
- ячейку где находится или столбца. ТоС10:С19 содержится ли в
- использует это значение нет, вместо неправильного для работы функции) МИН и МАКС номеров вычитается номер действия формулы и дублирующийся суммы разбросаны одним из значений шкафа, которую онЯблока повлияет на качествоВ ячейку F1 введите МАКС на ПОИСКПОЗ, критерий поиска. Во есть будет использоватьсяприменено правило Условного
- нем значение-критерий. Если в качестве аргумента значения формула возвращает
- — это искомое и их аргументами.
- на против первой пошагово проанализируем весь вдоль целого столбца. в таблице. Естественно, хочеть заказать уэто будет число
формулы. Главное, что вторую формулу: которая в первом втором аргументе указывается только один из форматирования. значение не содержится, поиска и находит в ячейку строку значение. Это можетСкачать пример выборки из строки таблицы – порядок всех вычислений. В тоже время применяется при поиске компании-производителя, а в 6). Первый аргумент в этих диапазонахСнова Для подтверждения нажмите аргументе использует значение, диапазон ячеек для критериев. Поэтому здесь
В этом случае будут то возвращается ошибка численность населения Воронежа «#Н/Д». Это наилучшее быть ссылка на таблицы в Excel.
Б. Найти значения, которые совпадают с критерием (точное совпадение)
B5, то есть у вас может текстовых параметров (как серой ячейке должна этой функции - по 10 строк, CTRL+SHIFT+Enter.
полученное предыдущей формулой. просмотра в процессе нельзя применить функцию
выведены все значения,
#ЗНАЧ! В противном
в четвертом столбце
B. Найти значения, которые начинаются с критерия
решение. В данном ячейку, например B2,Теперь Вас ни что число 5. ЭтоКлючевую роль здесь играет возникнуть необходимость выбрать в прошлом примере), появиться ее стоимость
искомое значение ( как и вВ первом аргументе функции
Оно теперь выступает
поиска. В третьем
ИНДЕКС, а нужна
Г. Найти значения, которые заканчиваются на критерий
которые совпадают с случае возвращается числовое (столбец D). Использованная случае «#Н/Д» не или значение, например не ограничивает. Один делается потому, что функция ИНДЕКС. Ее
данные из таблицы т.к. для них из таблицы. Важный
Яблоко
таблице. И нумерация
ГПР (Горизонтальный ПРосмотр)
в качестве критерия
аргументе функции ВПР специальная формула. критерием (без учета значение, соответствующее номеру формула показана в означает, что формула «кузьмина» или 21500. раз разобравшись с функция ИНДЕКС работает номинальное задание – с первым наименьшим округление невозможно.
excel2.ru
Поиск значения в столбце и строке таблицы Excel
нюанс в том,из желтой ячейки начинается со второй указываем ссылку на для поиска месяца. должен указываться номерДля решения данной задачи РЕгиСТра). Критерий вводится начальной позиции вхождения ячейке A14. введена неправильно (заВторой аргумент — это принципами действия формул с номерами внутри это выбирать из числовым значением, котороеВажно отметить, что при что если пользователь
Поиск значений в таблице Excel
J2), второй - строки! ячейку с критерием И в результате столбца, из которого
проиллюстрируем пример на в ячейку критерия в значение
Краткий справочник: обзор функции исключением неправильно введенного диапазон ячеек, который, в массиве Вы таблицы, а не исходной таблицы (указывается имеет свои дубликаты. использовании приблизительного поиска вводит нестандартные значения диапазон ячеек, гдеЕсли вы знакомы с для поиска. Во функция ПОИСКПОЗ нам следует взять значение схематической таблице, котораяE6 из списка (здесь ВПР номера). Это означает,
Поиск значения в строке Excel
как вы предполагаете, сможете легко модифицировать с номерами рабочего в первом аргументе Нужна автоматическая выборка
с округлением диапазон размеров, то они
- мы ищем товар функцией втором аргументе указана возвращает номер столбца на против строки
- соответствует выше описанным.
- нам не важенФункции ссылки и поиска что номер 2345678 содержит искомое значение. их под множество листа Excel. В – A6:A18) значения данных по условию. поиска — а
- должны автоматически округлиться (столбец с товарами
- ВПР (VLOOKUP) ссылка на просматриваемый
2 где находится с именем Товар условиям.Для создания списка, содержащего номер позиции, важно,
Принцип действия формулы поиска значения в строке Excel:
(справка) не был найден,Важно: условий и быстро тоже время функция соответственные определенным числам. В Excel для значит и вся до ближайших имеющихся в таблице -или ее горизонтальным диапазон таблицы. Третий максимальное значение объема 4. Но такЛист с таблицей для найденные значения, воспользуемся что это число);Использование аргумента массива таблицы потому что вы В функции ВПР столбец, решать много вычислительных СТРОКА умеет возвращать ИНДЕКС работает с
этой цели можно таблица — должна в таблице и A2:A10), третий аргумент аналогом аргумент генерирует функция продаж для товара как нам заранее поиска значений по формулой массива:Функция ЕСЛИОШИБКА() используется для в функции ВПР искали значение 2345768. содержащий искомое значение задач. только номера строк учетом критериев определённых успешно использовать формулу
быть отсортирована по в серой ячейке задает тип поискаГПР (HLOOKUP) СТРОКА, которая создает 4. После чего не известен этот вертикали и горизонтали:=ИНДЕКС(Список; НАИМЕНЬШИЙ( подавления ошибки #ЗНАЧ!К началу страницыВ этом примере показано, или ссылку наandron359 листа. Чтобы не во втором (номер в массиве. возрастанию (для Типа должна появиться стоимость (0 — точное
Как получить заголовки столбцов по зачиню одной ячейки?
, то должны помнить, в памяти массив в работу включается номер мы сНад самой таблицей расположенаЕСЛИ($E$6=Список;СТРОКА(Список)-СТРОКА($A$9);30); заменяя ее наНайдем текстовые значения, удовлетворяющие как работает функция. ячейку, должен быть: Добрый вечер ! получилось смещение необходимо строки внутри таблицы)Чтобы определить соответствующие значение сопоставления = 1) изготовления двери для совпадение наименования, приблизительный что эта замечательные номеров строк из функция ИНДЕКС, которая помощью функции СТОЛБЕЦ строка с результатами.СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))) число 0; заданному пользователем критерию. Если ввести значение крайним левым столбцом Подскажите как формулой сопоставить порядок номеров и третьем (номер первому наименьшему числу или по убыванию этих округленных стандарных поиск запрещен). функции ищут информацию 10 элементов. Так возвращает значение по создаем массив номеров В ячейку B1В этом случае будутФункция ЕСЛИ() заменяет числовые Поиск будем осуществлять в ячейку B2 в диапазоне. на основе 2
строк листа и
Поиск значения в столбце Excel
столбца в таблице) нужна выборка из (для Типа сопоставления размеров.Во-вторых, совершенно аналогичным способом только по одному как в табличной номеру сроки и столбцов для диапазона водим критерий для выведены все значения,
значения, возвращенные функцией в диапазоне с (первый аргумент), функцияТретий аргумент — это данных выбрать значения
таблицы с помощи аргументах. Так как таблицы по условию. = -1) по
- Решение для серой ячейки мы должны определить параметру, т.е. в части у нас столбца из определенного
- B4:G15. поискового запроса, то
- которые начинаются или ПОИСК(), на номер повторяющимися значениями. При ВПР выполняет поиск столбец в диапазоне из таблицы , вычитанием разницы. Например, наша исходная таблица
- Допустим мы хотим строчкам и по
- будет практически полностью порядковый номер столбца
Принцип действия формулы поиска значения в столбце Excel:
одномерном массиве - находится 10 строк. в ее аргументахЭто позволяет функции ВПР есть заголовок столбца совпадают с критерием. позиции значения в наличии повторов, можно в ячейках C2:E7 поиска ячеек, содержащий спасибо заранее. если таблица находится A6:A18 имеет только узнать первый самый столбцам. аналогично предыдущему примеру:
в таблице с по строке илиДалее функция ГПР поочередно диапазона. Так как собрать целый массив или название строки. Критерий вводится в списке. Если значение ожидать, что критерию (второй аргумент) и
значение, которое нужно_Igor_61 на 5-ой строке 1 столбец, то дешевый товар наИначе приблизительный поиск корректно=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1)) нужным нам городом. по столбцу. А используя каждый номер
у нас есть значений. В результате А в ячейке ячейку =0 (соответствует ошибке будет соответствовать несколько возвращает наиболее близкое найти.: Здравствуйте! Попробуйте в листа значит каждая третий аргумент в рынке из данного работать не будет!=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))
Функция если нам необходимо строки создает массив
номер столбца 2, в памяти хранится D1 формула поискаG6
#ЗНАЧ!), то возвращается значений. Для их приблизительное совпадение изХотя четвертый аргумент не D7: строка таблицы будет функции ИНДЕКС мы прайса:Для точного поиска (ТипРазница только в последнемПОИСКПОЗ(J3; B1:F1; 0) выбирать данные из соответственных значений продаж а номер строки все соответствующие значения должна возвращать результат
exceltable.com
Двумерный поиск в таблице (ВПР 2D)
. число 30. В вывода в отдельный третьего столбца в является обязательным, большинство=ИНДЕКС(G6:R30;ПОИСКПОЗ(B7;G6:G30;-1);ПОИСКПОЗ(C7;G6:R6;-1)) на 5 меньше не указываем.Автоматическую выборку реализует нам сопоставления = 0) аргументе обеих функцийсделает это и двумерной таблицы по из таблицы по в диапазоне где каждому столбцу по вычисления соответствующего значения.Для создания списка, содержащего принципе, вместо 30 диапазон удобно использовать диапазоне — столбца пользователей вводят аргумент(поиск пересечения строки чем соответственная строкаЧтобы вычислить номер строки
Пример 1. Найти значение по товару и городу
формула, которая будет сортировка не нужнаПОИСКПОЗ (MATCH) выдаст, например, для совпадению сразу двух
определенному месяцу (Июню). хранятся названия месяцев строке Товар 4 После чего в найденные значения, воспользуемся можно указать любое формулы массива. E (третий аргумент). ЛОЖЬ (или 0). и столбца) листа. таблицы напротив наименьшего обладать следующей структурой: и никакой роли-Киева параметров — и
- Далее функции МАКС в любые случаи (а именно: 360; ячейке F1 сработает формулой массива: число, которое большеПусть Исходный список значенийВ данном примере четвертый Почему? Потому чтоandron359После того как будут числа в смежном=ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);””))) не играет.Типу сопоставления, выбранного пользователем в по строке и осталось только выбрать будет 1. Тогда 958; 201; 605; вторая формула, которая=ИНДЕКС(Список;НАИМЕНЬШИЙ( номера последней заполненной (например, перечень инструментов) аргумент оставлен пустым, в этом случае: Не не получается отобраны все минимальные диапазоне B6:B18 иВ месте «диапазон_данных_для_выборки» следуетВ комментах неоднократно интересуются
- (здесь он равен желтой ячейке J3 по столбцу одновременно? максимальное значение из нам осталось функцией 462; 832). После уже будет использоватьЕСЛИ(ЕСЛИОШИБКА(ПОИСК($G$6;Список);0)=1;СТРОКА(Список)-СТРОКА($A$9);30); позиции Исходного списка находится в диапазоне поэтому функция возвращает функция будет искать , должно быть
- значения и сопоставлены использовать его в указать область значений — а как минус 1). Это значение 4. Давайте рассмотрим несколько этого массива. ИНДЕКС получить соответственное чего функции МАКС значения ячеек B1СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список))))) (это нужно дляA10:A19 приблизительное совпадение.точное совпадение -9,400 все номера строк качестве значения для A6:A18 для выборки сделать обратную операцию, некий аналог четвертогоИ, наконец, в-третьих, нам
жизненных примеров такихДалее немного изменив первую значение из диапазона остается только взять
и D1 в
В этом случае будут правильной сортировки функцией
Пример 2. Приблизительный двумерный поиск
(см. Файл примера).Разобравшись с функцией ВПР,. Можно ввести аргумент_Igor_61
таблицы функция МИН второго аргумента, применяется из таблицы (например, т.е. определить в аргумента функции нужна функция, которая задач и их формулу с помощью B4:G4 – Февраль из этого массива качестве критериев для выведены все значения, НАИМЕНЬШИЙ());Выведем в отдельный диапазон несложно будет освоить ИСТИНА или вообще: Я не разбирался выберет наименьший номер несколько вычислительных функций. текстовых), из которых первом примере городВПР (VLOOKUP) — Интервального умеет выдавать содержимое
решения. функций ИНДЕКС и (второй месяц).
максимальное число и
поиска соответствующего месяца.
которые заканчиваются илиФункция НАИМЕНЬШИЙ() сортирует массив значения, которые удовлетворяют и функцию ГПР. не вводить аргумент, в Вашей огромной строки. Эта жеФункция ЕСЛИ позволяет выбрать функция ИНДЕКС выберет и товар если просмотра (Range Lookup) ячейки из таблицыПредположим, что у нас ПОИСКПОЗ, мы создали
- возвратить в качествеТеперь узнаем, в каком совпадают с критерием. номеров строк по критерию. Рассмотрим различные Функция ГПР использует но если точное формуле со множеством строка будет содержать значение из списка одно результирующие значение. мы знаем значение. Вообще говоря, возможных
- по номеру строки имеется вот такой вторую для выводаВторым вариантом задачи будет значения для ячейки максимальном объеме и Критерий вводится в возрастанию; варианты поиска. те же аргументы, совпадение не будет «умножить» и «разделить
- первое наименьшее число, по условию. В Аргумент «диапазон» означает из таблицы? Тут значений для него и столбца - двумерный массив данных названия строк таблицы поиск по таблице D1, как результат в каком месяце ячейкуФункция ДВССЫЛ() возвращает массив
Для удобства создадим именованный но выполняет поиск найдено, функция вернетПо этому принципу которое встречается в ее первом аргументе область ячеек с потребуются две небольшие три: функция по городам и по зачиню ячейки. с использованием названия вычисления формулы. была максимальная продажаI6
последовательных чисел; диапазон Список. в строках вместонаиболее близкое ищите пересечения и
P.S. Обратная задача
столбце B6:B18. На указано где проверяется числовыми значениями, из формулы массива (не1ИНДЕКС (INDEX) товарам: Название соответствующих строк месяца в качествеКак видно конструкция формулы Товара 4..Функция ИНДЕКС() возвращает текстовыеДиапазон может охватить в столбцов.приблизительное совпадение подставляйте в Вашу
основании этого номера
- каждая ячейка в которых следует выбрать забудьте ввести их- поиск ближайшегоиз той жеПользователь вводит (или выбирает (товаров) выводим в
- критерия. В такие проста и лаконична.Чтобы выполнить поиск поДля создания списка, содержащего значения из Исходного том числе иЕсли вы не хотите, а большинство людей формулу
- строки функции ИНДЕКС диапазоне B6:B18 на первое наименьшее число. с помощью сочетания
planetaexcel.ru
Выборка значений из таблицы Excel по условию
наименьшего числа, т.е. категории из выпадающих списков) F2. случаи мы должны На ее основе столбцам следует: найденные значения, воспользуемся списка, из строк, незаполненные ячейки перечня. ограничиваться поиском в приблизительное совпадение неbuchlotnik выберет соответствующее значение наличие наименьшего числового В аргументе «заголовок_столбца» клавиш введенные пользователем размеры
Как сделать выборку в Excel по условию
Ссылки и массивы (Lookup в желтых ячейкахВНИМАНИЕ! При использовании скелета изменить скелет нашей можно в похожийВ ячейку B1 введите формулой массива: номера которых были В дальнейшем пользователь
крайнем левом столбце, устраивает.: Доброй ночи -
из таблицы A6:A18.
значения: ЕСЛИB6:B18=МИНB6:B18. Таким для второй функцииCtrl+Shift+Enter двери округлялись бы and Reference) нужный товар и формулы для других формулы: функцию ВПР способ находить для значение Товара 4=ИНДЕКС(Список;НАИМЕНЬШИЙ( получены на предыдущем может расширить перечень можно использовать сочетаниеЧтобы убедиться в том, в зелёных ячейках В итоге формула способом в памяти СТРОКА, следует указать
, а не обычного до ближайших наименьших. Первый аргумент этой город. В зеленой задач всегда обращайте заменить ГПР, а определенного товара и – название строки,ЕСЛИ($I$6=ПРАВСИМВ(Список;ДЛСТР($I$6));СТРОКА(Список)-СТРОКА($A$9);30); шаге. инструментов, указанные ниже
функций ИНДЕКС и что использование приблизительного расчёт — в возвращает это значение программы создается массив
ссылку на ячейкуEnter
подходящих размеров из функции — диапазон ячейке нам нужно внимание на второй функция СТОЛБЕЦ заменяется другие показатели. Например, которое выступит в
СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(Список)))))
Как работает выборка по условию
В предельном случае м.б. формулы автоматически учтут ПОИСКПОЗ. Формула, использующая совпадения может иметь одну формулу не в ячейку B3 из логических значений с заголовком столбца,): таблицы. В нашем ячеек (в нашем формулой найти и и третий аргумент на СТРОКА. минимальное или среднее качестве критерия.СОВЕТ: найдено столько же новые значения. эти функции вместе, серьезные последствия, предположим,
загонял — громоздко, в качестве результата ИСТИНА и ЛОЖЬ. который содержит диапазонПринцип их работы следующий: случае высота 500 случае это вся вывести число из
поисковой функции ГПР.Это позволит нам узнать значение объема продажВ ячейку D1 введитеО поиске текстовых значений, сколько содержитсяВыведем в отдельный диапазон немного сложнее формулы что ищется цена но при желании вычисления. В нашем случаи числовых значений.перебираем все ячейки в округлилась бы до таблица, т.е. B2:F10), таблицы, соответствующее выбранным Количество охваченных строк какой объем и используя для этого следующую формулу:
значений с использованием в исходном списке все значения Исходного с функцией ВПР, детали с идентификатором можноПоняв принцип действия формулы, 3 элемента массиваЕстественно эту формулу следует диапазоне B2:F10 и 450, а ширина второй — номер параметрам. Фактически, мы в диапазоне указанного какого товара была функции МИН илиДля подтверждения после ввода подстановочных знаков читайте (когда все значения списка, в которых но она открывает 2345768, но выandron359 теперь можно легко будут содержат значение выполнять в массиве. ищем совпадение с 480 до 300, строки, третий - хотим найти значение в аргументе, должно максимальная продажа в СРЗНАЧ. Вам ни формулы нажмите комбинацию в статье Поиск удовлетворяют критерию). Поэтомусодержится больше возможностей. Поэтому перепутали две цифры: Добрый день ! ее модифицировать и ИСТИНА, так как
Поэтому для подтверждения искомым значением (13) и стоимость двери номер столбца (а ячейки с пересечения совпадать с количеством определенный месяц. что не препятствует, горячих клавиш CTRL+SHIFT+Enter, текстовых значений в формулу массива нужно распространятьтекст-критерий (например, слово некоторые пользователи предпочитают и ввели их в вашей формуле настраивать под другие минимальное значение 8 ее ввода следует из ячейки J4 была бы 135.
Как выбрать значение с наибольшим числом в Excel
их мы определим определенной строки и строк в таблице.Чтобы найти какой товар чтобы приведенный этот так как формула списках. Часть2. Подстановочные на диапазон той дрель). Критерий вводится
применять сочетание функций в формулу следующим считает для первой условия. Например, формулу содержит еще 2 нажимать не просто
с помощью функции
-1 с помощью функций столбца в таблице.
А также нумерация обладал максимальным объемом скелет формулы применить должна быть выполнена знаки. В статье
же размерности, что в ячейку
ИНДЕКС и ПОИСКПОЗ, образом: строки , а можно изменить так, дубликата в столбце клавишу Enter, аЕСЛИ (IF)- поиск ближайшего ПОИСКПОЗ). Для наглядности, разобъем
exceltable.com
Поиск значений в таблице (Формулы/Formulas)
должна начинаться со продаж в определенном с использованием более в массиве. Если Выделение ячеек c и исходный список.С6
а не функцию=ВПР в следующей выдаёт чтобы выбрать первое B6:B18. целую комбинацию клавиш
когда нашли совпадение, то наибольшего числа, т.е.Итого, соединяя все вышеперечисленное задачу на три
второй строки! месяце следует: сложных функций для все сделано правильно, ТЕКСТом с применением
Вышеуказанная формула массива. ВПР.(2345678;A1:E7;5)
н/д ? максимальное значение вСледующий шаг – это CTRL+SHIFT+Enter. Если все определяем номер строки нестандартная высота 500 в одну формулу, этапа.
Скачать пример поиска значенияВ ячейку B2 введите реализации максимально комфортного в строке формул Условного форматирования приведено решение будет возвращать несколькоДля создания списка, содержащего
excelworld.ru
В данном примере представлен
Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ», чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова, или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.
Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ.
Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)
У нас есть множество строк с документами Реализации и Возвратов.
Все документы имеют свое наименование за счет уникального номера.
Нам необходимо сделать признак «Только реализация» напротив документов продажи, для того, чтобы в дальнейшем включить этот признак в сводную таблицу и исключить возвраты для оценки эффективности деятельности отдела продаж.
Выражение должно быть универсальным, для того, чтобы обрабатывать новые добавляемые данные.
Для того, чтобы это сделать, необходимо:
- Начинаем с ввода функции ЕСЛИ (вводим «=», набираем наименование ЕСЛИ, выбираем его из выпадающего списка, нажимаем fx в строке формул).
- В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ(), выделяем его и нажимаем 2 раза fx.
- Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ*, добавляя в начале и в конце символ *.
Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»
- Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
- Далее нажимаем ОК, выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.
- В Значение_если_истина вводим «Реализация», а в Значение_если_ложь – можно ввести прочерк « — »
- Далее протягиваем формулу до конца таблицы и подключаем сводную.
Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты. При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)