Как найти необходимое значение в таблице

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

Описание

В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций 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).

Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
 

Использование функции СМЕЩ

5 способов – поиск значения в массиве Excel

В статье предлагается несколько различных формул для выполнения поиска в двумерном массиве значений 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. Назовите столбцы и строки

Самый быстрый способ назвать каждую строку и каждый столбец в вашей таблице:

1. Выделите всю таблицу (в нашем случае A1:E11).
2. На вкладке « Формулы » в группе « Определенные имена » щелкните « Создать из выделенного » или нажмите комбинацию клавиш  Ctrl + Shift + F3.
3. В диалоговом окне « Создание имени из выделенного » выберите « в строке выше » и « в столбце слева» и нажмите «ОК».

Это автоматически создает имена на основе заголовков строк и столбцов. Однако есть пара предостережений:

• Если ваши заголовки столбцов и/или строк являются числами или содержат определенные символы, которые не разрешены в именах Excel, то имена для таких столбцов и строк не будут созданы. Чтобы просмотреть список созданных имен, откройте Диспетчер имен (Ctrl + F3). Если некоторые имена отсутствуют, определите их вручную.
• Если некоторые из ваших заголовков строк или столбцов содержат пробелы, то они будут заменены символами подчеркивания, например, Неделя_1.

Шаг 2. Создание формулы поиска по матрице

Чтобы получить значение из матрицы на пересечении определенной строки и столбца, просто введите одну из следующих общих формул в пустую ячейку:

=имя_строки имя_столбца

Или наоборот:

=имя_столбца имя_строки

Например, чтобы получить продажу Sprite в 3-й неделе, используйте выражение:

=Sprite неделя_3

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

Если кому-то нужны более подробные инструкции, опишем весь процесс пошагово:

1. В ячейке, в которой вы хотите отобразить результат, введите знак равенства (=).
2. Начните вводить имя целевой строки, Sprite. После того, как вы введете пару символов, Excel отобразит все существующие имена, соответствующие вашему вводу. Дважды щелкните нужное имя, чтобы ввести его в формулу.
3. После имени строки введите пробел , который в данном случае работает как оператор пересечения.
4. Введите имя целевого столбца ( в нашем случае неделя_3 ).
5. Как только будут введены имена строки и столбца, Excel выделит соответствующую строку и столбец в вашей таблице, и вы нажмете Enter, чтобы завершить ввод:

Ваш поиск нужной ячейки в массиве выполнен, найден результат 87.

Вот какими способами можно выполнять поиск в массиве значений – в строках и столбцах таблицы Excel. Я благодарю вас за чтение и надеюсь еще увидеть вас в нашем блоге.

Еще несколько материалов по теме:

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

Поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям – В статье показано, как выполнять быстрый поиск с несколькими условиями в Excel с помощью ИНДЕКС и ПОИСКПОЗ. Хотя Microsoft Excel предоставляет специальные функции для вертикального и горизонтального поиска, опытные пользователи…

ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР – В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР. В нескольких недавних статьях мы приложили немало усилий, чтобы объяснить основы функции ВПР новичкам и предоставить…

Поиск в массиве при помощи ПОИСКПОЗ – В этой статье объясняется с примерами формул, как использовать функцию ПОИСКПОЗ в Excel.  Также вы узнаете, как улучшить формулы поиска, создав динамическую формулу с функциями ВПР и ПОИСКПОЗ. В Microsoft…

Функция ИНДЕКС в Excel — 6 примеров использования – В этом руководстве вы найдете ряд примеров формул, демонстрирующих наиболее эффективное использование ИНДЕКС в Excel. Из всех функций Excel, возможности которых часто недооцениваются и используются недостаточно, ИНДЕКС определенно занимает место…

Функция СУММПРОИЗВ с примерами формул – В статье объясняются основные и расширенные способы использования функции СУММПРОИЗВ в Excel. Вы найдете ряд примеров формул для сравнения массивов, условного суммирования и подсчета ячеек по нескольким условиям, расчета средневзвешенного значения…

Средневзвешенное значение — формула в Excel – В этом руководстве демонстрируются два простых способа вычисления средневзвешенного значения в Excel – с помощью функции СУММ (SUM) или СУММПРОИЗВ (SUMPRODUCT в английском варианте). В одной из предыдущих статей мы…

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

​Смотрите также​ периодам, отсюда и​ СМЕЩ. Вот файл​ таблицу (нижнюю). Проблема​ нестандартная высота 500​ функция​ параметру, т.е. в​ нас вообще не​ на СТРОКА.​ (а именно: 360;​ схематической таблице, которая​ соответствовать значения начинающиеся​.​ если вы переходите​найти ячейку с ссылкой​ ссылками и массивами.​ связанное с ним​Предположим, что требуется найти​ название: П4 Число3​ более приблизенный к​

В этой статье

​ в том что​ округлялась бы до​ИНДЕКС (INDEX)​

​ одномерном массиве -​ интересует, нам нужен​Это позволит нам узнать​

​ 958; 201; 605;​ соответствует выше описанным​ с фраз дрелью,​Перечень найденных значений будем​

​ на другую страницу.​ в формуле Excel,​В Excel 2007 мастер​

​ имя​ внутренний телефонный номер​ – это результат​

​ структуре моего​ никак не могу​ 700, а ширина​

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

​из той же​ по строке или​ просто счетчик строк.​ какой объем и​ 462; 832). После​

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

​ условиям.​ дрел23 и т.п.​ помещать в отдельный​

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

​ С помощью этого​

​чтобы заменить ссылку,​ подстановок создает формулу​Алексей​ сотрудника по его​ по Числам 1​

​genyaa​ въехать как выбрать​ 480 – до​​ категории​​ по столбцу. А​ То есть изменить​ какого товара была​​ чего функции МАКС​​Лист с таблицей для​

​Критерий вводится в ячейку​ столбец.​ окна можно производить​

​ смотрите статью «Поменять​

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

​ подстановки, основанную на​.​

​ идентификационному номеру или​​ и 2 в​: а так?​ значения из первого​

​ 600 и стоимость​Ссылки и массивы (Lookup​ если нам необходимо​ аргументы на: СТРОКА(B2:B11)​ максимальная продажа в​ остается только взять​​ поиска значений по​​G2​Найдем все названия инструментов,​ поиск на любой​ ссылки на другие​ данных листа, содержащих​Дополнительные сведения см. в​ узнать ставку комиссионного​ Период2/Период1, П5 Число3​iam_alex​​ столбца основной таблицы​​ составила бы уже​

​ and Reference)​ выбирать данные из​ или СТРОКА(С2:С11) –​

​ определенный месяц.​

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

​ из этого массива​ вертикали и горизонтали:​и выглядит так:​

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

​ это никак не​​Чтобы найти какой товар​ максимальное число и​Над самой таблицей расположена​

​ «?дрель?». В этом​​начинаются​​ его активизировать на​ Excel».​ столбцов. С помощью​ ВПР.​ определенный объем продаж.​

​ по Период3/Период1, П6​​ буду мучить, но​ столбце и значении​ так гораздо интереснее!​ функции – диапазон​ совпадению сразу двух​ повлияет на качество​ обладал максимальным объемом​ возвратить в качестве​ строка с результатами.​​ случае будут выведены​​с фразы дрел​

​ открытой странице. Для​

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

​Найти в Excel ячейки​ мастера подстановок можно​К началу страницы​

​ Необходимые данные можно​ Число3 – это​​ уже не сегодня)​​ второй таблицы: то​ :)​ ячеек (в нашем​

​ параметров – и​ формулы. Главное, что​ продаж в определенном​

​ значения для ячейки​

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

​ В ячейку B1​ все значения,​

​ и​​ этого нажать курсор​ с примечанием​ найти остальные значения​

​Для выполнения этой задачи​ быстро и эффективно​ результат по Период3/Период1.​ есть еще идея​ есть выбираю Период,​0​ случае это вся​ по строке и​ в этих диапазонах​

​ месяце следует:​ D1, как результат​ водим критерий для​

​содержащие​

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

​длина строки​​ на строке “найти”.​-​ в строке, если​ используются функции СМЕЩ​ находить в списке​И я не​ у коллег, потом​

​ ему соответствует столбец​- поиск точного​ таблица, т.е. B2:F10),​ по столбцу одновременно?​ по 10 строк,​В ячейку B2 введите​ вычисления формулы.​ поискового запроса, то​слово дрель, и​которых составляет 5​Для более расширенного​статья “Вставить примечание​ известно значение в​ и ПОИСКПОЗ.​ и автоматически проверять​

1. ​ ленюсь)))​

2. ​ сопоставлю и выложу,​​ и определенное значение​​ соответствия без каких​​ второй – номер​​ Давайте рассмотрим несколько​​ как и в​​ название месяца Июнь​

3. ​Как видно конструкция формулы​​ есть заголовок столбца​​ у которых есть​ символов.​

   ​ поиска нажмите кнопку​

4. ​ в Excel” тут​​ одном столбце, и​ ​Примечание:​ их правильность. Значения,​​vikttur​​ если интересно…​​ по строке нижней​​ либо округлений. Используется​

5. ​ строки, третий -​​ жизненных примеров таких​​ таблице. И нумерация​​ – это значение​​ проста и лаконична.​​ или название строки.​​ перед ним и​

6. ​Критерий будет вводиться в​​ “Параметры” и выберите​​ .​ наоборот. В формулах,​​ Данный метод целесообразно использовать​​ возвращенные поиском, можно​​: Для облегчения работы​​vikttur​

7. ​ таблицы, так вот​

​ для 100%-го совпадения​

support.office.com

Поиск в Excel.

​ номер столбца (а​​ задач и их​​ начинается со второй​​ будет использовано в​​ На ее основе​ А в ячейке​ после него как​ ячейку​ нужный параметр поиска.​​Для быстрого поиска​ ​ которые создает мастер​ при поиске данных​
​ затем использовать в​ формулам советую добавить​: Подправил:​​ нужно найти это​ искомого значения с​ их мы определим​ решения.​ строки!​ качестве поискового критерия.​
​ можно в похожий​ D1 формула поиска​ минимум 1 символ.​​С2​Например, выберем – “Значение”.​ существует сочетание клавиш​ подстановок, используются функции​ в ежедневно обновляемом​ вычислениях или отображать​ по два пустых​
​=ИНДЕКС($C$5:$C$37;ПОИСКПОЗ(СМЕЩ(C44;;3*ПРАВСИМВ($C$43));СМЕЩ($C$5;;3*ПРАВСИМВ($C$43);СЧЁТЗ(C5:C37);1);0))​​ значение в этом​ одним из значений​​ с помощью функций​Предположим, что у нас​Как использовать функцию​В ячейку D2 введите​ способ находить для​
​ должна возвращать результат​Для создания списка, содержащего​ ​и выглядеть так:​​ Тогда будет искать​ –​ ИНДЕКС и ПОИСКПОЗ.​
​ внешнем диапазоне данных.​ как результаты. Существует​ столбца между четырьмя​​См. файл. Если​​ же столбце (Период)​ в таблице. Естественно,​ ПОИСКПОЗ).​ имеется вот такой​
​ВПР (VLOOKUP)​ формулу:​ определенного товара и​ вычисления соответствующего значения.​ найденные значения, воспользуемся​
​ «дрел?». Вопросительный знак​ и числа, и​Ctrl + F​Щелкните ячейку в диапазоне.​ Известна цена в​ несколько способов поиска​ столбцами Число3 Периода3.​ другое количество промежуточных​ и вернуть значение​ применяется при поиске​Итого, соединяя все вышеперечисленное​
​ двумерный массив данных​для поиска и​Для подтверждения после ввода​ другие показатели. Например,​ После чего в​ формулой массива:​ является подстановочным знаком.​ номер телефона, т.д.​. Нажимаем клавишу Ctrl​На вкладке​ столбце B, но​ значений в списке​
​ Тогда столбцы с​ столбцов, то подправить​ Показателя в строку​ текстовых параметров (как​ в одну формулу,​ по городам и​ выборки нужных значений​ формулы нажмите комбинацию​ минимальное или среднее​ ячейке F1 сработает​
​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​​Для реализации этого варианта​Если нужно найти​ и, удерживая её,​​Формулы​​ неизвестно, сколько строк​
​ данных и отображения​
​ данными будут расположены​ СМЕЩ​ второй таблицы. В​ в прошлом примере),​ получаем для зеленой​ товарам:​ из списка мы​ клавиш CTRL+SHIFT+Enter, так​ значение объема продаж​ вторая формула, которая​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($G$2;Список);СТРОКА(Список)-СТРОКА($A$4);НД());””);​ поиска требуется функция​ все одинаковес слова,​ нажимаем клавишу F.​в группе​ данных возвратит сервер,​ результатов.​ через расстояние, кратное​
​lapink2000​ файле описано то​ т.к. для них​ ячейки решение:​Пользователь вводит (или выбирает​ недавно разбирали. Если​ как формула будет​ используя для этого​ уже будет использовать​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​ позволяющая использовать подстановочные​ но в падежах​ Появится окно поиска.​
​Решения​ а первый столбец​Поиск значений в списке​ трем и предыдущие​
​: Такой вариант, быстрый​ же самое в​ округление невозможно.​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​
​ из выпадающих списков)​ вы еще с​ выполнена в массиве.​ функции МИН или​ значения ячеек B1​)​ знаки: используем функцию​ (молоко, молоком, молоку,​Ещё окно поиска​
​выберите команду​ не отсортирован в​​ по вертикали по​ формулы легче подстроить​ и нелетучий​ примечании с указанием​Важно отметить, что при​или в английском варианте​ в желтых ячейках​
​ ней не знакомы​ А в строке​ СРЗНАЧ. Вам ни​ и D1 в​Часть формулы ПОИСК($G$2;Список) определяет:​ ПОИСК(). Согласно критерию​ т.д.), то напишем​
​ можно вызвать так​Подстановка​ алфавитном порядке.​ точному совпадению​ под новую структуру.​vikttur​ на конкретные ячейки.​ использовании приблизительного поиска​ =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))​
​ нужный товар и​ – загляните сюда,​ формул появятся фигурные​ что не препятствует,​ качестве критериев для​

excel-office.ru

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть2. Подстановочные знаки

​содержит​ «дрел?» (длина 5​ формулу с подстановочными​ – на закладке​.​C1​Поиск значений в списке​Такой вариант приемлем?​: Точно! Работа ИНДЕКС​ Пробовал всячески -​ с округлением диапазон​Слегка модифицируем предыдущий пример.​ город. В зеленой​ не пожалейте пяти​

​ скобки.​ чтобы приведенный этот​ поиска соответствующего месяца.​​ли значение из​​ символов) – должны​

Задача

​ знаками. Смотрите об​ “Главная” нажать кнопку​Если команда​ — это левая верхняя​ по вертикали по​В С44 выбираем​ с областями. Я​

А. Найти значения, которые начинаются с критерия и содержат определенное количество символов

​ не выходит, поэтому​ поиска – а​ Предположим, что у​​ ячейке нам нужно​​ минут, чтобы сэкономить​

​В ячейку F1 введите​ скелет формулы применить​Теперь узнаем, в каком​

​ диапазона​ быть выведены 3​​ этом статью “Подстановочные​​ “Найти и выделить”.​Подстановка​​ ячейка диапазона (также​​ приблизительному совпадению​ период. Для периода3​

​ об этом забыл​ прикрепляю шаблон без​​ значит и вся​​ нас имеется вот​ формулой найти и​ себе потом несколько​

​ вторую формулу:​ с использованием более​ максимальном объеме и​A5:A13​ значения: Дрель, дрель,​ знаки в Excel”.​На вкладке «Найти» в​недоступна, необходимо загрузить​ называемая начальной ячейкой).​Поиск значений по вертикали​

​ нужна еще одна​ :(​ формул. Заранее благодарен​
​ таблица – должна​
​ такая ситуация:​
​ вывести число из​
​ часов.​

​Снова Для подтверждения нажмите​​ сложных функций для​​ в каком месяце​фразу «?дрел?». Критерию​​ Дрели.​​Функция в Excel “Найти​
​ ячейке «найти» пишем​ надстройка мастера подстановок.​​Формула​​ в списке неизвестного​​ ячейка для выбора​​Спасибо за напоминание.​

​ за ответ!​ быть отсортирована по​Идея в том, что​ таблицы, соответствующее выбранным​Если же вы знакомы​ CTRL+SHIFT+Enter.​ реализации максимально комфортного​ была максимальная продажа​ также будут соответствовать​Для создания списка, содержащего​ и выделить”​ искомое слово (можно​Загрузка надстройки мастера подстановок​ПОИСКПОЗ(“Апельсины”;C2:C7;0)​

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

​ размера по точному​​ Числа3 (одного из​​iam_alex​Guest​ возрастанию (для Типа​ пользователь должен ввести​ параметрам. Фактически, мы​​ с ВПР, то​​В первом аргументе функции​ анализа отчета по​​ Товара 4.​​ значения содержащие фразы​

​ найденные значения, воспользуемся​поможет не только​ часть слова) и​
​Нажмите кнопку​
​ищет значение “Апельсины”​
​ совпадению​
​ четырех). Правильно мыслю?​

​: Господа, Вы уж​​: см. файл.​​ сопоставления = 1)​ в желтые ячейки​​ хотим найти значение​​ – вдогон -​ ГПР (Горизонтальный ПРосмотр)​ продажам.​Чтобы выполнить поиск по​ 5дрел7, Адрелу и​

В. Найти значения, у которых слово дрель находится в середине строки

​ формулой массива:​​ найти данные, но​​ нажимаем «найти далее».​Microsoft Office​ в диапазоне C2:C7.​Поиск значений в списке​​iam_alex​​ простите меня -​iam_alex​ или по убыванию​ высоту и ширину​ ячейки с пересечения​

​ стоит разобраться с​ указываем ссылку на​Например, как эффектно мы​
​ столбцам следует:​
​ т.п.​
​=ИНДЕКС(Список;​
​ и заменить их.​

​ Будет найдено первое​​, а затем —​​ Начальную ячейку не​ по горизонтали по​​: Да, вариант с​​ сразу все не​: огромнейшее спасибо за​ (для Типа сопоставления​ двери для, например,​ определенной строки и​

Г. Найти значения, которые заканчиваются на слово дрель или дрели

​ похожими функциями:​​ ячейку с критерием​​ отобразили месяц, в​В ячейку B1 введите​Критерий вводится в ячейку​НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ((ПОИСК($C$2;Список)=1)*(ДЛСТР($C$2)=ДЛСТР(Список))=1;СТРОКА(Список)-СТРОКА($A$4);НД());””);​​ Смотрите статью “Как​​ такое слово. Затем​ кнопку​

​ следует включать в​

​ точному совпадению​ доп ячейками, конечно,​ учел. Приложил файл.​
​ ответ!!! сейчас попробую​
​ = -1) по​
​ шкафа, которую он​
​ столбца в таблице.​

​ИНДЕКС (INDEX)​​ для поиска. Во​ котором была максимальная​​ значение Товара 4​​I2​​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​ скопировать формулу в​ нажимаете «найти далее»​Параметры Excel​ этот диапазон.​

​Поиск значений в списке​
​ приемлем для сохранения​ Проблема в том,​ применить это к​ строчкам и по​ хочеть заказать у​ Для наглядности, разобъем​и​

excel2.ru

Поиск значения в столбце и строке таблицы Excel

​ втором аргументе указана​ продажа, с помощью​ – название строки,​и выглядит так:​)​ Excel без изменения​ и поиск перейдет​и выберите категорию​1​ по горизонтали по​ структуры – как​ что есть заголовки​ моим таблицам, т.к.​ столбцам.​ компании-производителя, а в​ задачу на три​ПОИСКПОЗ (MATCH)​ ссылка на просматриваемый​

Поиск значений в таблице Excel

​ второй формулы. Не​ которое выступит в​ «дрел?». В этом​Часть формулы ПОИСК($C$2;Список)=1 определяет:​ ссылок” здесь.​

​ на второе такое​Надстройки​ — это количество столбцов,​

​ приблизительному совпадению​ сам не сообразил…))​ и подзаголовки столбцов.​ они покрупнее и​Иначе приблизительный поиск корректно​ серой ячейке должна​ этапа.​, владение которыми весьма​ диапазон таблицы. Третий​ сложно заметить что​ качестве критерия.​ случае будут выведены​начинается​Как убрать лишние​ слово.​.​ которое нужно отсчитать​Создание формулы подстановки с​”Для периода3 нужна​

Поиск значения в строке Excel

​ И искать по​ посложнее))​ работать не будет!​ появиться ее стоимость​Во-первых, нам нужно определить​

​ облегчит жизнь любому​ аргумент генерирует функция​

1. ​ во второй формуле​В ячейку D1 введите​ все значения,​ли значение из​ пробелы, которые мешают​
2. ​А если надо показать​В поле​
3. ​ справа от начальной​ помощью мастера подстановок​ еще одна ячейка​ наименованиям Периода нет​iam_alex​Для точного поиска (Тип​ из таблицы. Важный​ номер строки, соответствующей​ опытному пользователю Excel.​
4. ​ СТРОКА, которая создает​ мы использовали скелет​
5. ​ следующую формулу:​заканчивающиеся​

​ диапазона​ обрабатывать данные в​ сразу все такие​Управление​ ячейки, чтобы получить​

Принцип действия формулы поиска значения в строке Excel:

​ (только Excel 2007)​ для выбора Числа3​ смысла, т.к. это​: да… я сразу​ сопоставления = 0)​ нюанс в том,​ выбранному пользователем в​ Гляньте на следующий​ в памяти массив​ первой формулы без​Для подтверждения после ввода​на слова дрель​A5:A13​ таблице, читайте в​ слова, то нажимаем​выберите значение​ столбец, из которого​Для решения этой задачи​ (одного из четырех).”​ заголовок, а нужно​ то не обратил​ сортировка не нужна​ что если пользователь​

​ желтой ячейке товару.​ пример:​ номеров строк из​ функции МАКС. Главная​ формулы нажмите комбинацию​ или дрели.​с фразы «дрел?».​ статье “Как удалить​ кнопку «найти все»​Надстройки Excel​ возвращается значение. В​ можно использовать функцию​ вот тут не​ выбирать значения, соответствующие​ внимание на ПОИСКПОЗ​ и никакой роли​ вводит нестандартные значения​ Это поможет сделать​

​Необходимо определить регион поставки​ 10 элементов. Так​ структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0).​ горячих клавиш CTRL+SHIFT+Enter,​ ​Часть формулы ДЛСТР($C$2)=ДЛСТР(Список)​ лишние пробелы в​ и внизу поискового​и нажмите кнопку​ этом примере значение​ ВПР или сочетание​ понял что Вы​ подзаголовкам (Число); ну​ и нумерацию в​ не играет.​ размеров, то они​ функция​ по артикулу товара,​ как в табличной​ Мы заменили функцию​

Как получить заголовки столбцов по зачиню одной ячейки?

​ так как формула​Для создания списка, содержащего​ определяет:​ Excel” тут.​ окошка появится список​Перейти​ возвращается из столбца​ функций ИНДЕКС и​ имеете ввиду?​ или по-другому -​ столбце B. я​В комментах неоднократно интересуются​ должны автоматически округлиться​ПОИСКПОЗ (MATCH)​ набранному в ячейку​ части у нас​ МАКС на ПОИСКПОЗ,​ должна быть выполнена​ найденные значения, воспользуемся​равна ли длина строки​В Excel можно​ с указанием адреса​.​ D​ ПОИСКПОЗ.​vikttur​ именно в том​ может быть зря​ – а как​ до ближайших имеющихся​из категории​ C16.​ находится 10 строк.​ которая в первом​ в массиве. Если​ формулой массива:​значения из диапазона​ найти любую информацию​ ячейки. Чтобы перейти​В области​Продажи​Дополнительные сведения см. в​: Период1 и Перио2​ по порядку столбце​ привел таки простые​

​ сделать обратную операцию,​

Поиск значения в столбце Excel

​ в таблице и​Ссылки и массивы (Lookup​Задача решается при помощи​Далее функция ГПР поочередно​ аргументе использует значение,​ все сделано правильно,​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​A5:A13​ не только функцией​ на нужное слово​Доступные надстройки​

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

​ т.е. определить в​ в серой ячейке​ and Reference)​ двух функций:​

1. ​ используя каждый номер​ полученное предыдущей формулой.​ в строке формул​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2)));СТРОКА(Список)-СТРОКА($A$4);НД());””);​5 символам?​
2. ​ “Поиск” или формулами,​ в таблице, нажимаем​
3. ​установите флажок рядом​К началу страницы​ ВПР.​ Число3, Период3 -​ котором найдено значение​ вопроса как раз​ первом примере город​ должна появиться стоимость​
4. ​. В частности, формула​=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)​
5. ​ строки создает массив​ Оно теперь выступает​

Принцип действия формулы поиска значения в столбце Excel:

​ появятся фигурные скобки.​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​Знак * (умножить) между​ но и функцией​ нужное слово в​ с пунктом​Для выполнения этой задачи​Что означает:​ черыре Число3.​ (собственно, в том​ со множеством разных​ и товар если​ изготовления двери для​ПОИСКПОЗ(J2; A2:A10; 0)​Функция​ соответственных значений продаж​

​ в качестве критерия​В ячейку F1 введите​)​ частями формулы представляет​ условного форматирования. Читайте​ списке окна поиска.​Мастер подстановок​ используется функция ГПР.​=ИНДЕКС(нужно вернуть значение из​Как формула узнает,​

​ же по порядку)​ чисел, как в​ мы знаем значение​ этих округленных стандарных​даст нам нужный​ПОИСКПОЗ​ из таблицы по​ для поиска месяца.​ вторую формулу:​Часть формулы ПОИСК($I$2;ПРАВСИМВ((Список);ДЛСТР($I$2))) определяет:​

​ условие И (значение​ об этом статью​Если поиск ничего не​и нажмите кнопку​ См. пример ниже.​ C2:C10, которое будет​ какое из четырех​ нижней таблицы.​ следующем прикремленном примере…​ из таблицы? Тут​ размеров.​ результат (для​ищет в столбце​ определенному месяцу (Июню).​

​ И в результате​Снова Для подтверждения нажмите​совпадают ли последние 5​

​ должно начинаться с​ “Условное форматирование в​ нашел, а вы​ОК​

​Функция ГПР выполняет поиск​ соответствовать ПОИСКПОЗ(первое значение​ Число3-Период3 проверять?​vikttur​ я эти индекс​ потребуются две небольшие​Решение для серой ячейки​Яблока​D1:D13​ Далее функции МАКС​ функция ПОИСКПОЗ нам​ комбинацию клавиш CTRL+SHIFT+Enter.​ символов​ дрел и иметь​ Excel” здесь.​ знаете, что эти​

exceltable.com

Поиск нужных данных в диапазоне

​.​​ по столбцу​​ “Капуста” в массиве​Guest​: Это уже окончательный​ смещ впр гпр​ формулы массива (не​ будет практически полностью​это будет число​значение артикула из​ осталось только выбрать​ возвращает номер столбца​Найдено в каком месяце​

​значений из диапазона​ такую же длину,​Ещё прочитать о​ данные точно есть,​Следуйте инструкциям мастера.​​Продажи​​ B2:B10))​​: По столбцам Число1​​ вариант?​ поиспоз уже замучил​ забудьте ввести их​ аналогично предыдущему примеру:​ 6). Первый аргумент​

​ ячейки​ максимальное значение из​ 2 где находится​ и какая была​

​A5:A13​ как и критерий,​

​ функции “Найти и​

​ то попробуйте убрать​​К началу страницы​​и возвращает значение​​Формула ищет в C2:C10​​ и Число2 выборки​В Периодах 1,​​ (или они меня),​​ с помощью сочетания​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ этой функции -​C16​ этого массива.​ максимальное значение объема​ наибольшая продажа Товара​с фразой «дрел?».​ т.е. 5 букв).​ выделить” можно в​

​ из ячеек таблицы​​Часто возникает вопрос​​ из строки 5 в​​ первое значение, соответствующее​​ не будет -​ 2, 3 столбцы​ но так и​ клавиш​​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​​ искомое значение (​. Последний аргумент функции​Далее немного изменив первую​

planetaexcel.ru

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

​ продаж для товара​ 4 на протяжении​​ Критерию также будут​​ Критерию также будут​ статье “Фильтр в​​ отступ. Как убрать​​«​ указанном диапазоне.​ значению​ это исходники для​ Число1 и Число2​ не нашел нужного​Ctrl+Shift+Enter​Разница только в последнем​Яблоко​ 0 – означает​ формулу с помощью​ 4. После чего​ двух кварталов.​ соответствовать значения заканчивающиеся​ соответствовать такие несуразные​ Excel”.​ отступ в ячейках,​Как найти в Excel​Дополнительные сведения см. в​

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

​Капуста​ расчета Число3​ сейчас пустые. Будет​ решения.​, а не обычного​

​ аргументе обеих функций​из желтой ячейки​ поиск точного (а​ функций ИНДЕКС и​ в работу включается​В первом аргументе функции​ на фразы дрела,​ значения как дрел5,​Найдем текстовые значения, удовлетворяющие​ смотрите в статье​»?​ разделе, посвященном функции​(B7), и возвращает​П4, П5, П6​ ли выборка по​iam_alex​Enter​

• ​ПОИСКПОЗ (MATCH)​ J2), второй -​ не приблизительного) соответствия.​ ПОИСКПОЗ, мы создали​ функция ИНДЕКС, которая​ ВПР (Вертикальный ПРосмотр)​​ дрел6 и т.п.​​ дрелМ и т.п.​​ заданному пользователем критерию.​ “Текст Excel. Формат”.​​В Excel можно​​ ГПР.​​ значение в ячейке​ – там рассчитывается​​ этим столбцам (в​​: =ПОИСКПОЗ(ИНДЕКС(C16:F16;0;B15);D5:D9;0) – в​):​-​ диапазон ячеек, где​​ Функция выдает порядковый​​ вторую для вывода​ возвращает значение по​ указывается ссылка на​СОВЕТ:​ (если они содержатся​ Критерии заданы с​Поиск числа в Excel​ найти любую информацию:​К началу страницы​ C7 (​ только Число3 по​
• ​ D45:D49, E45:E49 и​ ячейке b15 номер​Принцип их работы следующий:​Типу сопоставления​ мы ищем товар​ номер найденного значения​​ названия строк таблицы​​ номеру сроки и​ ячейку где находится​​О поиске текстовых​​ в списке).​ использованием подстановочных знаков.​требует небольшой настройки​
• ​ текст, часть текста,​Для выполнения этой задачи​100​ исходникам Число 1​ т.д.)?​ столюца таблицы​перебираем все ячейки в​​(здесь он равен​​ (столбец с товарами​ в диапазоне, т.е.​​ по зачиню ячейки.​ столбца из определенного​​ критерий поиска. Во​ значений с учетом​Критерий вводится в ячейку​ Поиск будем осуществлять​ условий поиска -​ цифру, номер телефона,​ используется функция ГПР.​).​ и Число2 в​П4, П5, П6​этим я могу​

​ диапазоне B2:F10 и​ минус 1). Это​ в таблице -​ фактически номер строки,​

​ Название соответствующих строк​

​ в ее аргументах​ втором аргументе указывается​

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

​ РЕгиСТра читайте в​E2​ в диапазоне с​ применим​

​ эл. адрес​Важно:​Дополнительные сведения см. в​ Периодах 1, 2​ без столбцов Число1​ узнать на какой​ ищем совпадение с​ некий аналог четвертого​ A2:A10), третий аргумент​ где найден требуемыый​ (товаров) выводим в​ диапазона. Так как​ диапазон ячеек для​ статье Поиск текстовых​и выглядит так:​ повторяющимися значениями. При​расширенный поиск в Excel​,​  Значения в первой​ разделах, посвященных функциям​ и 3 в​ и Число2. Так​ строке нужная мне​

​ искомым значением (13)​ аргумента функции​ задает тип поиска​

​ артикул.​

​ F2.​

​ у нас есть​ просмотра в процессе​​ значений в списках.​​ «дрел??». В этом​​ наличии повторов, можно​​.​фамилию, формулу, примечание, формат​ строке должны быть​ ИНДЕКС и ПОИСКПОЗ.​​ вариациях по этим​ и будет?​​ цифра, но не​ из ячейки J4​ВПР (VLOOKUP) – Интервального​

• ​ (0 – точное​​Функция​ВНИМАНИЕ! При использовании скелета​ номер столбца 2,​ поиска. В третьем​ Часть3. Поиск с​ случае будут выведены​ ожидать, что критерию​Совет.​ ячейки, т.д.​ отсортированы по возрастанию.​К началу страницы​ периодам, отсюда и​”Период” для 1,​
• ​ могу заставить узнать​​ с помощью функции​ просмотра (Range Lookup)​ совпадение наименования, приблизительный​ИНДЕКС​ формулы для других​ а номер строки​ аргументе функции ВПР​ учетом РЕГИСТРА.​ все значения, в​ будет соответствовать несколько​Если вы работаете​
• ​Найти ячейку на пересечении​​В приведенном выше примере​Для выполнения этой задачи​ название: П4 Число3​ 2, 3, и​ в нужном столбце​ЕСЛИ (IF)​. Вообще говоря, возможных​ поиск запрещен).​выбирает из диапазона​ задач всегда обращайте​ в диапазоне где​ должен указываться номер​

​Имеем таблицу, в которой​ которые​ значений. Для их​ с таблицей продолжительное​ строки и столбца​ функция ГПР ищет​ используется функция ВПР.​ – это результат​ “П” для 4,​ (Период) – это​когда нашли совпадение, то​ значений для него​Во-вторых, совершенно аналогичным способом​A1:G13​​ внимание на второй​ хранятся названия месяцев​

​ столбца, из которого​ записаны объемы продаж​начинаются​ вывода в отдельный​ время и вам​

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

​ Excel​ значение 11 000 в строке 3​Важно:​ по Числам 1​ 5, 6 периодов​ главный вопрос, остальное​ определяем номер строки​ три:​ мы должны определить​значение, находящееся на​ и третий аргумент​ в любые случаи​ следует взять значение​​ определенных товаров в​​с текста-критерия (со​​ диапазон удобно использовать​​ часто надо переходить​

​– смотрите статью​

1. ​ в указанном диапазоне.​  Значения в первой​ и 2 в​ – это требование​ можно как мне​ (столбца) первого элемента​​1​
2. ​ порядковый номер столбца​ пересечении заданной строки​ поисковой функции ГПР.​ будет 1. Тогда​ на против строки​ разных месяцах. Необходимо​​ слова дрел) и​​ формулы массива.​​ к поиску от​
3. ​ «Как найти в​ Значение 11 000 отсутствует, поэтому​ строке должны быть​​ Период2/Период1, П5 Число3​

planetaexcel.ru

поиск значений в таблице excel

​ или лень автора​​ мидится прикрутить с​ в таблице в​- поиск ближайшего​ в таблице с​ (номер строки с​ Количество охваченных строк​ нам осталось функцией​ с именем Товар​ в таблице найти​длиной как минимум​Пусть Исходный список значений​ одного слова к​ Excel ячейку на​ она ищет следующее​ отсортированы по возрастанию.​ – это результат​ дописать слово?​ помощью других функций.​ этой строке (столбце)​ наименьшего числа, т.е.​ нужным нам городом.​ артикулом выдает функция​ в диапазоне указанного​ ИНДЕКС получить соответственное​ 4. Но так​ данные, а критерием​6 символов.​ (например, перечень инструментов)​ другому. Тогда удобнее​ пересечении строки и​ максимальное значение, не​В приведенном выше примере​ по Период3/Период1, П6​Guest​

​Guest​​ с помощью функций​

​ введенные пользователем размеры​​ Функция​ПОИСКПОЗ​ в аргументе, должно​ значение из диапазона​ как нам заранее​ поиска будут заголовки​

​Для создания списка, содержащего​​ находится в диапазоне​ окно поиска не​ столбца» (функция “ИНДЕКС”​ превышающее 11 000, и возвращает​ функция ВПР ищет​ Число3 – это​: По столбцам Число1​: Тогда так.​СТОЛБЕЦ (COLUMN)​ двери округлялись бы​ПОИСКПОЗ(J3; B1:F1; 0)​) и столбца (нам​ совпадать с количеством​ B4:G4 – Февраль​ не известен этот​ строк и столбцов.​ найденные значения, воспользуемся​A5:A13.​ закрывать каждый раз,​

​ в Excel).​​ 10 543.​ имя первого учащегося​ результат по Период3/Период1.​
​ и Число2 выборки​iam_alex​и​ до ближайших наименьших​сделает это и​ нужен регион, т.е.​ строк в таблице.​ (второй месяц).​ номер мы с​ Но поиск должен​ формулой массива:​

​См. Файл примера.​​ а сдвинуть его​

​Найти и перенести в​​Дополнительные сведения см. в​ с 6 пропусками в​

​И я не​​ не будет -​: кажется получилось… правда​СТРОКА (ROW)​

​ подходящих размеров из​​ выдаст, например, для​ второй столбец).​ А также нумерация​​ помощью функции СТОЛБЕЦ​ быть выполнен отдельно​=ИНДЕКС(Список;НАИМЕНЬШИЙ(​

​Выведем в отдельный диапазон​​ в ту часть​

​ другое место в​​ разделе, посвященном функции​ диапазоне A2:B7. Учащихся​ ленюсь))){/post}{/quote}​ это исходники для​ с макросом)))​выдергиваем значение города или​ таблицы. В нашем​

​Киева​​Если вы знакомы с​
​ должна начинаться со​
​Вторым вариантом задачи будет​ создаем массив номеров​ по диапазону строки​ЕСЛИОШИБКА(ЕСЛИ(ПОИСК($E$2;Список)=1;СТРОКА(Список)-СТРОКА($A$4);НД());””);​

​ значения, которые удовлетворяют​​ таблицы, где оно​ Excel​

​ ГПР.​​ с​В Периоде3 одно​ расчета Число3​genyaa​
​ товара из таблицы​

​ случае высота 500​​, выбранного пользователем в​ функцией​ второй строки!​ поиск по таблице​ столбцов для диапазона​ или столбца. То​СТРОКА(ДВССЫЛ(“A1:A”&ЧСТРОК(Список))))​ критерию, причем критерий​ не будет мешать.​(например, в бланк)​К началу страницы​6​ Число3. Остальные -​П4, П5, П6​: А послений мой​ с помощью функции​ округлилась бы до​ желтой ячейке J3​ВПР (VLOOKUP)​Скачать пример поиска значения​ с использованием названия​

​ B4:G15.​​ есть будет использоваться​)​
​ задан с использованием​ Сдвинуть можно ниже​ несколько данных сразу​Примечание:​ пропусками в таблице нет,​ это П4, П5,​ – там рассчитывается​ пример не подошел,​
​ИНДЕКС (INDEX)​ 450, а ширина​ значение 4.​или ее горизонтальным​
​ в столбце и​ месяца в качестве​Это позволяет функции ВПР​ только один из​Часть формулы ПОИСК($E$2;Список)=1 определяет:​ подстановочных знаков (*,​ экрана, оставив только​

​ – смотрите в​​ Поддержка надстройки “Мастер подстановок”​ поэтому функция ВПР​ П6​ только Число3 по​ разве?​
​iam_alex​ 480 до 300,​И, наконец, в-третьих, нам​ аналогом​ строке Excel​ критерия. В такие​ собрать целый массив​ критериев. Поэтому здесь​начинается​ ?). Рассмотрим различные​ ячейку ввода искомого​ статье «Найти в​ в Excel 2010​ ищет первую запись​vikttur​ исходникам Число 1​Guest​: Всем доброго дня!​
​ и стоимость двери​ нужна функция, которая​

​ГПР (HLOOKUP)​​Читайте также: Поиск значения​ случаи мы должны​ значений. В результате​ нельзя применить функцию​ли значение из​ варианты поиска.​ слова («найти») и​ Excel несколько данных​ прекращена. Эта надстройка​ со следующим максимальным​: Так в чем​
​ и Число2 в​
​: Как раз сижу​ В прикрепленном файле​ была бы 135.​ умеет выдавать содержимое​, то должны помнить,​ в диапазоне таблицы​

​ изменить скелет нашей​​ в памяти хранится​ ИНДЕКС, а нужна​ диапазона​Для удобства написания формул​ нажимать потом Enter.​
​ сразу» здесь (функция​ была заменена мастером​ значением, не превышающим​ проблема?​ Периодах 1, 2​ разбираю… Честно говоря​ есть основная таблица​

​-1​​ ячейки из таблицы​ что эта замечательные​ Excel по столбцам​ формулы: функцию ВПР​
​ все соответствующие значения​ специальная формула.​A5:A13​

​ функция СТОЛБЕЦ заменяется​​ строке Товар 4​ проиллюстрируем пример на​
​ Критерию также будут​A5:A13​

planetaexcel.ru

​ на экране, даже​

Поиск нужных данных в диапазоне

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

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

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 – означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

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

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

• Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
• Улучшенная версия функции ВПР (VLOOKUP)
• Многоразовый ВПР

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

Поиск значения в массиве Excel

Схема решения задания выглядит примерно таким образом:

• в ячейку B1 мы будем вводить интересующие нас данные;
• в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
• в ячейке B3 будет отображается название строки, которая содержит значение ячейки B1.

Фактически необходимо выполнить поиск координат в Excel. Для чего это нужно? Достаточно часто нам нужно получить координаты таблицы по значению. Немного напоминает обратный анализ матрицы. Конкретный пример в двух словах выглядит примерно так. Поставленная цель в цифрах является исходным значением, нужно определить кто и когда наиболее приближен к этой цели. Для примера используем простую матрицу данных с отчетом по количеству проданных товаров за три квартала, как показано ниже на рисунке. Важно, чтобы все числовые показатели совпадали. Если нет желания вручную создавать и заполнять таблицу Excel с чистого листа, то в конце статьи можно скачать уже с готовым примером.

Последовательно рассмотрим варианты решения разной сложности, а в конце статьи – финальный результат.

Поиск значения в столбце Excel

Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:

1. В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
2. В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
3. После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки { }.

В ячейку C2 формула вернула букву D – соответственный заголовок столбца листа. Как видно все сходиться, значение 5277 содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки.

Поиск значения в строке Excel

Теперь получим номер строки для этого же значения (5277). Для этого в ячейку C3 введите следующую формулу:

После ввода формулы для подтверждения снова нажимаем комбинацию клавиш CTRL+SHIFT+Enter и получаем результат:

Формула вернула номер 9 – нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.



Как получить заголовок столбца и название строки таблицы

Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению 5277 вместо D9 получить заголовки:

• для столбца таблицы – Март;
• для строки – Товар4.

Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:

1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter:



2. Для строки вводим похожую, но все же немного другую формулу:

В результате получены внутренние координаты таблицы по значению – Март; Товар 4:

На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда могут возникнуть проблемы с ошибками! Рекомендуем также посмотреть альтернативное решение для поиска столбцов и строк по значению.

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

Чтобы проконтролировать наличие дубликатов среди значений таблицы создадим формулу, которая сможет информировать нас о наличии дубликатов и подсчитывать их количество. Для этого в ячейку E2 вводим формулу:

Более того для диапазона табличной части создадим правило условного форматирования:

1. Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».



2. В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.



3. В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали (с лева на право). А формула для получения названия (номера) строки берет номер с первого дубликата по вертикали (сверху вниз). Для исправления данного решения есть 2 пути:

1. Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу на:

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



2. Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует изменить формулу на:

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

Здесь правильно отображаются координаты первого дубликата по вертикали (с верха в низ) – I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера.

Поиск ближайшего значения в диапазоне Excel

Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка – #ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:

После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. Выберите: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»-«Изменить правило». И здесь в параметрах укажите F1 вместо B1. Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: 8000. Это приведет к завершающему результату:

Теперь можно вводить любое исходное значение, а программа сама подберет ближайшее число, которое содержит таблица. После чего выводит заголовок столбца и название строки для текущего значения. Например, если ввести число 5000 получаем новый результат:

Скачать пример поиска значения в диапазоне Excel

Наша программа в Excel нашла наиболее близкое значение 4965 для исходного – 5000. Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т.п. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel.