Как найти значение ячейки в таблице excel

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

Описание

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

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

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

ИНДЕКС ПОИСКПОЗ как лучшая альтернатива ВПР

В этом руководстве показано, как использовать ИНДЕКС и ПОИСКПОЗ в Excel и чем они лучше ВПР.

В нескольких недавних статьях мы приложили немало усилий, чтобы объяснить основы функции ВПР новичкам и предоставить более сложные примеры формул ВПР опытным пользователям. А теперь я постараюсь если не отговорить вас от использования ВПР, то хотя бы показать вам альтернативный способ поиска нужных значений в Excel.

• Краткий обзор функций ИНДЕКС и ПОИСКПОЗ
• Как использовать формулу ИНДЕКС ПОИСКПОЗ
• ИНДЕКС+ПОИСКПОЗ вместо ВПР?
• Поиск справа налево
• Двусторонний поиск в строках и столбцах
• ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям
• Как найти среднее, максимальное и минимальное значение
• Что делать с ошибками поиска?

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

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

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

Функция ИНДЕКС

Функция ИНДЕКС (в английском варианте – INDEX) возвращает значение в массиве на основе указанных вами номеров строк и столбцов. Синтаксис функции ИНДЕКС прост:

ИНДЕКС(массив,номер_строки,[номер_столбца])

Вот простое объяснение каждого параметра:

• массив — это диапазон ячеек, именованный диапазон или таблица.
• номер_строки — это номер строки в массиве, из которого нужно вернуть значение. Если этот аргумент опущен, требуется следующий – номер_столбца.
• номер_столбца — это номер столбца, из которого нужно вернуть значение. Если он опущен, требуется номер_строки.

Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .

А вот пример формулы ИНДЕКС в самом простом виде:

=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.

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

Функция ПОИСКПОЗ

Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_совпадения])

• искомое_значение — числовое или текстовое значение, которое вы ищете.
• диапазон_поиска — диапазон ячеек, в которых будем искать.
• тип_совпадения — указывает, следует ли искать точное соответствие или наиболее близкое совпадение:
  • 1 или опущено — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
  • 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы чаще всего устанавливаете третий аргумент вашей функции в 0.
  • -1 — находит наименьшее значение, которое больше или равно искомому значению. Требуется сортировка массива поиска в порядке убывания.

Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:

=ПОИСКПОЗ(«лимоны»;B1:B3;0)

Дополнительные сведения см . в статье Функция ПОИСКПОЗ в Excel .

На первый взгляд полезность функции ПОИСКПОЗ может показаться сомнительной. Кого волнует положение значения в диапазоне? Что мы действительно хотим определить, так это само значение.

Однако, относительная позиция искомого значения (т. е. номера строки и столбца, в которых оно находится) — это именно то, что нам нужно указать для аргументов номер_строки и номер_столбца функции ИНДЕКС. Как вы помните, ИНДЕКС может найти значение на пересечении заданной строки и столбца, но сама не может определить, какую именно строку и столбец ей нужно выбрать.

Вот поэтому совместное использование ИНДЕКС и ПОИСКПОЗ открывает перед нами массу возможностей для поиска в Excel.

Как использовать формулу ИНДЕКС ПОИСКПОЗ в Excel

Теперь, когда вы знаете основы, я считаю, что вы уже начали понимать, как ПОИСКПОЗ и ИНДЕКС работают вместе. Короче говоря, ИНДЕКС извлекает нужное значение по номерам столбцов и строк, а ПОИСКПОЗ предоставляет ей эти номера. Вот и все!

Для вертикального поиска вы используете функцию ПОИСКПОЗ только для определения номера строки, указывая диапазон столбцов непосредственно в самой формуле:

ИНДЕКС ( столбец для возврата значения ; ПОИСКПОЗ ( искомое значение ; столбец для поиска ; 0))

Все еще не совсем понимаете эту логику? Возможно, будет проще разобрать на примере. Предположим, у вас есть список национальных столиц и их население:

Чтобы найти население определенной столицы, скажем, Индии, используйте следующую формулу ПОИСКПОЗ ИНДЕКС:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(“Индия”;A2:A10;0))

Теперь давайте проанализируем, что на самом деле делает каждый компонент этой формулы:

• Функция ПОИСКПОЗ ищет искомое значение «Индия» в диапазоне A2:A10 и возвращает число 2, поскольку это слово занимает второе место в массиве поиска.
• Этот номер поступает непосредственно в аргумент номер_строки функции ИНДЕКС, предписывая вернуть значение из этой строки.

Таким образом, приведенная выше формула превращается в ИНДЕКС(C2:C10;2), которая означает, что нужно искать в ячейках от C2 до C10 и извлекать значение из второй ячейки в этом диапазоне, то есть из C3, потому что мы начинаем отсчет со второй строки.

Но указывать название города в формуле не совсем правильно, так как для каждого нового поиска придется корректировать эту формулу. Введите его в какую-нибудь отдельную ячейку, скажем, F1, укажите ссылку на ячейку для ПОИСКПОЗ, и вы получите формулу динамического поиска:

=ИНДЕКС(C2:C10;ПОИСКПОЗ(F1;A2:A10;0))

Важное замечание! Количество строк в аргументе массив функции ИНДЕКС должно совпадать с количеством строк в аргументе просматриваемый_массив в ПОИСКПОЗ, иначе формула выдаст неверный результат.

Вы спросите: «А почему бы нам просто не использовать обычную формулу ВПР? Какой смысл тратить время на то, чтобы разобраться в хитросплетениях ИНДЕКС ПОИСКПОЗ в Excel?»

Вот как это будет выглядеть:

=ВПР(F1; A2:C10; 3; 0)

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

ИНДЕКС+ПОИСКПОЗ вместо ВПР?

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

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

4 основные причины использовать ИНДЕКС ПОИСКПОЗ вместо ВПР

1. Поиск справа налево. Как известно любому образованному пользователю, ВПР не может искать влево. Это означает, что искомое значение всегда должно находиться в крайнем левом столбце таблицы. А извлекать нужное значение мы будем из столбца, который находится правее. ИНДЕКС+ПОИСКПОЗ может легко выполнять поиск влево! Здесь это показано в действии: Как выполнить поиск значения слева в Excel .
2. Можно безопасно вставлять или удалять столбцы. Формулы ВПР не работают или выдают неверные результаты, когда новый столбец удаляется из таблицы поиска или добавляется в нее, поскольку синтаксис ВПР требует указания порядкового номера столбца, из которого вы хотите извлечь данные. Естественно, когда вы добавляете или удаляете столбцы, этот номер в формуле автоматически не меняется, а нужный столбец уже оказывается на новом месте.

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

1. Нет ограничений на размер искомого значения. При использовании функции ВПР общая длина ваших критериев поиска не может превышать 255 символов, иначе вы получите ошибку #ЗНАЧ!. Таким образом, если ваш набор данных содержит длинные строки, ИНДЕКС ПОИСКПОЗ — единственное работающее решение.
2. Более высокая скорость обработки. Если ваши таблицы относительно небольшие, вряд ли будет какая-то существенная разница в производительности Excel. Но если ваши рабочие листы содержат сотни или тысячи строк и, следовательно, сотни или тысячи формул, ИНДЕКС ПОИСКПОЗ будет работать намного быстрее, чем ВПР. Причина в том, что Excel будет обрабатывать только столбцы поиска и возврата, а не весь массив таблицы.

Влияние ВПР на производительность Excel может быть особенно заметным, если ваша книга содержит сложные формулы массива. Чем больше значений содержит ваш массив и чем больше формул массива содержится в книге, тем медленнее работает Excel.

ИНДЕКС ПОИСКПОЗ в Excel – примеры формул

Уяснив, почему все же стоит изучать ИНДЕКС ПОИСКПОЗ, давайте перейдем к самому интересному и посмотрим, как можно применить теоретические знания на практике.

Формула для поиска справа налево

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

Для этого примера мы добавим столбец «Ранг» слева от нашей основной таблицы и попытаемся выяснить, какое место занимает столица России по численности населения среди других перечисленных  столиц.

Записав искомое значение в G1, используйте следующую формулу для поиска в C2:C10 и возврата соответствующего значения из A2:A10:

=ИНДЕКС(A2:A10; ПОИСКПОЗ(G1;C2:C10;0))

Совет. Если вы планируете использовать формулу ПОИСКПОЗ ИНДЕКС более чем для одной ячейки, обязательно зафиксируйте оба диапазона абсолютными ссылками  (например, $A$2:$A$10 и $C$2:$C$10), чтобы они не изменялись при копировании формулы.

Двусторонний поиск в строках и столбцах

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

Это может показаться сложным, но формула очень похожа на базовую функцию ПОИСКПОЗ ИНДЕКС в Excel, но с одним отличием. 

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

ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))

А теперь, пожалуйста, взгляните на приведенную ниже таблицу и давайте составим формулу двумерного поиска, чтобы найти население (в миллионах) в данной стране за данный год.

С целевой страной в G1 (значение_поиска1) и целевым годом в G2 (значение_поиска2) формула принимает следующий вид:

=ИНДЕКС(B2:D11; ПОИСКПОЗ(G1;A2:A11;0); ПОИСКПОЗ(G2;B1:D1;0))

Как работает эта формула?

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

ПОИСКПОЗ(G1;A2:A11;0); – ищет в A2:A11 значение из ячейки G1 («США») и возвращает его позицию, которая равна 3.

ПОИСКПОЗ(G2;B1:D1;0) – просматривает диапазон B1:D1, чтобы получить позицию значения из ячейки G2 («2015»), которая равна 3.

Найденные выше номера строк и столбцов становятся соответствующими аргументами функции ИНДЕКС:

ИНДЕКС(B2:D11, 3, 3)

В результате вы получите значение на пересечении 3-й строки и 3-го столбца в диапазоне B2:D11, то есть из D4. Несложно? 

ИНДЕКС ПОИСКПОЗ для поиска по нескольким условиям

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

Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями:

{=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

Примечание. Это формула массива , которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter.

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

Следующая формула ИНДЕКС ПОИСКПОЗ отлично работает:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; (F1=A2:A10) * (F2=B2:B10); 0))

Где C2:C10 — это диапазон, из которого возвращается значение, F1 — это критерий1, A2:A10 — это диапазон для сравнения с критерием 1, F2 — это критерий 2, а B2:B10 — это диапазон для сравнения с критерием 2.

Не забудьте правильно ввести формулу, нажав Ctrl + Shift + Enter, и Excel автоматически заключит ее в фигурные скобки, как показано на скриншоте ниже:

Рис5

Если вы не хотите использовать формулы массива, добавьте в формулу в F4 еще одну функцию ИНДЕКС и завершите ее ввод обычным нажатием Enter:

=ИНДЕКС(C2:C10; ПОИСКПОЗ(1; ИНДЕКС((F1=A2:A10) * (F2=B2:B10); 0; 1); 0))

Разберем пошагово, как это работает.

Здесь используется тот же подход, что и в обычном сочетании ИНДЕКС ПОИСКПОЗ, где просматривается один столбец. Чтобы оценить несколько критериев, вы создаете два или более массива значений ИСТИНА и ЛОЖЬ, которые представляют совпадения и несовпадения для каждого отдельного критерия, а затем перемножаете соответствующие элементы этих массивов. Операция умножения преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно и создает массив, в котором единицы соответствуют строкам, которые удовлетворяют всем условиям. Функция ПОИСКПОЗ со значением поиска 1 находит первую «1» в массиве и передает ее позицию в ИНДЕКС, которая возвращает значение в этой позиции из указанного столбца.

Вторая формула без массива основана на способности функции ИНДЕКС работать с массивами. Второй вложенный ИНДЕКС имеет 0 в номер_строки , так что он будет передавать весь массив столбцов в ПОИСКПОЗ.

Среднее, максимальное и минимальное значение при помощи ИНДЕКС ПОИСКПОЗ

Microsoft Excel имеет специальные функции для поиска минимального, максимального и среднего значения в диапазоне. Но что, если вам нужно получить значение из другой ячейки, связанной с этими значениями? Например,  получить название города с максимальным населением или узнать товар с минимальными продажами? В этом случае используйте функцию МАКС , МИН или СРЗНАЧ вместе с ИНДЕКС ПОИСКПОЗ.

Максимальное значение.

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

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МАКС(C2:C10); C2:C10; 0))

Скриншот с примером находится чуть ниже.

Минимальное значение

Теперь найдём город с самым маленьким населением в списке. Чтобы найти наименьшее число в столбце С и получить соответствующее ему значение из столбца В:

=ИНДЕКС(B2:B10; ПОИСКПОЗ(МИН(C2:C10); C2:C10; 0))

Ближайшее к среднему

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

=ИНДЕКС(B2:B10; ПОИСКПОЗ(СРЗНАЧ(C2:C10); C2:C10; -1 ))

В зависимости от того, как организованы ваши данные, укажите 1 или -1 для третьего аргумента (тип_совпадения) функции ПОИСКПОЗ:

• Если ваш столбец поиска (столбец D в нашем случае) отсортирован по возрастанию , поставьте 1. Формула вычислит наибольшее значение, которое меньше или равно среднему значению.
• Если ваш столбец поиска отсортирован по убыванию , введите -1. Формула вычислит наименьшее значение, которое больше или равно среднему значению.
• Если ваш массив поиска содержит значение , точно равное среднему, вы можете ввести 0 для точного совпадения. Никакой сортировки не требуется.

В нашем примере данные в столбце D отсортированы в порядке убывания, поэтому мы используем -1 для типа соответствия. В результате мы получаем «Токио», так как его население (13 189 000) является ближайшим, превышающим среднее значение (12 269 006).

Что делать с ошибками поиска?

Как вы, наверное, заметили, если формула ИНДЕКС ПОИСКПОЗ в Excel не может найти искомое значение, она выдает ошибку #Н/Д. Если вы хотите заменить это стандартное сообщение чем-то более информативным, оберните формулу ПОИСКПОЗ ИНДЕКС в функцию ЕСНД . Например:

=ЕСНД(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Не найдено»)

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

Если вы хотите перехватывать все ошибки, а не только #Н/Д, используйте функцию ЕСЛИОШИБКА вместо ЕСНД:

=ЕСЛИОШИБКА(ИНДЕКС(C2:C10; ПОИСКПОЗ(F1;A2:A10;0)); «Что-то пошло не так!»)

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

Итак, еще раз об основных преимуществах формулы ИНДЕКС ПОИСКПОЗ.

• Возможен ли «левый» поиск?

• Повлияет ли на результат вставка и удаление столбцов?

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

• Возможен ли поиск по строкам и столбцам?

  Можно сначала найти подходящий столбец, а уж потом извлечь из него значение. Общий вид формулы:
  ИНДЕКС(массив; ПОИСКПОЗ(значение_поиска1 ; столбец_поиска ; 0); ПОИСКПОЗ(значение_поиска2 ; столбец_поиска ; 0))
  Подробную инструкцию смотрите здесь.

• Как сделать поиск ИНДЕКС ПОИСКПОЗ по нескольким условиям?

  Можно выполнять поиск по двум или более условиям без добавления дополнительных столбцов. Вот формула массива, которая решит проблему:
  {=ИНДЕКС( диапазон_возврата; ПОИСКПОЗ (1; ( критерий1 = диапазон1 ) * ( критерий2 = диапазон2 ); 0))}

Вот как можно использовать ИНДЕКС и ПОИСКПОЗ в Excel. Я надеюсь, что наши примеры формул окажутся полезными для вас.

Вот еще несколько статей по этой теме:

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

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

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

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

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

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

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

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

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

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

​Смотрите также​ с макросом)))​​. Вообще говоря, возможных​​Читайте также: Поиск значения​​ 4. Но так​​ они оказались перед​ отделяет ссылку на​Введите знак равенства «=»,​ экран. Как видим,​​ является подсчет ячеек​​ количества действий. Он​. Если оба критерия​ населённых стран мира.​Подсказка:​ беспокоясь о том,​Важно! Количество строк и​​, поскольку «London» –​​Этот учебник рассказывает о​

​genyaa​ значений для него​ в диапазоне таблицы​ как нам заранее​ листом 2 или​​ лист от ссылки​​ а затем — функцию.​ в выделенной колонке​ в выделенном диапазоне,​ позволяет подсчитать количество​ ложны, или выполняется​ Предположим, наша задача​​Правильным решением будет​​ что нужно будет​ столбцов в массиве,​ это третий элемент​ главных преимуществах функций​

​: А послений мой​ три:​ Excel по столбцам​​ не известен этот​​ после листа 6,​ на диапазон ячеек.​ Например, чтобы получить​ в 63 ячейках​ а в нашем​ ячеек, содержащих числовые​ только один из​ узнать население США​ всегда использовать абсолютные​​ исправлять каждую используемую​​ который использует функция​​ в списке.​​ИНДЕКС​ пример не подошел,​1​ и строкам​ номер мы с​​ Microsoft Excel вычтет​​Примечание:​

• ​ общий объем продаж,​ содержится слово​
• ​ случае в столбце,​ и текстовые данные.​
• ​ них – Вы​ в 2015 году.​
• ​ ссылки для​ функцию​
  • ​INDEX​=MATCH(«London»,B1:B3,0)​
  • ​и​ разве?​
  • ​- поиск ближайшего​По сути содержимое диапазона​
  • ​ помощью функции СТОЛБЕЦ​
  • ​ из суммы содержимое​ Если название упоминаемого листа​

Базовая информация об ИНДЕКС и ПОИСКПОЗ

​ нужно ввести «=СУММ».​«Мясо»​ который содержит числовые​​ Сделать это можно​​ получите​​Хорошо, давайте запишем формулу.​​ИНДЕКС​ВПР​(ИНДЕКС), должно соответствовать​=ПОИСКПОЗ(«London»;B1:B3;0)​ПОИСКПОЗ​

​Guest​ наименьшего числа, т.е.​ нас вообще не​ создаем массив номеров​ ячеек с перемещенных​ содержит пробелы или​​Введите открывающую круглую скобку​​.​​ значения. Синтаксис этой​​ просто взглянув на​​0​​ Когда мне нужно​

ИНДЕКС – синтаксис и применение функции

​и​​.​​ значениям аргументов​Функция​в Excel, которые​: Как раз сижу​ введенные пользователем размеры​ интересует, нам нужен​

​ столбцов для диапазона​
​ листов.​

​ цифры, его нужно​ «(«.​

• ​Давайте немного изменим задачу.​​ функции практически идентичен​ индикатор в строке​.​ создать сложную формулу​
• ​ПОИСКПОЗ​​3. Нет ограничения на​row_num​MATCH​ делают их более​ разбираю… Честно говоря​ двери округлялись бы​ просто счетчик строк.​​ B4:G15.​​Перемещение конечного листа​
• ​ заключить в апострофы​​Выделите диапазон ячеек, а​ Теперь посчитаем количество​ предыдущему оператору:​ состояния.​Теперь понимаете, почему мы​ в Excel с​, чтобы диапазоны поиска​​ размер искомого значения.​​(номер_строки) и​

​(ПОИСКПОЗ) имеет вот​ привлекательными по сравнению​​ так и не​​ до ближайших наименьших​ То есть изменить​Это позволяет функции ВПР​   . Если переместить лист 2​

​ (‘), например так:​​ затем введите закрывающую​​ ячеек в этой​

​=СЧЁТ(значение1;значение2;…)​
​Для выполнения данной задачи​

​ задали​ вложенными функциями, то​​ не сбились при​​Используя​column_num​​ такой синтаксис:​​ с​​ допер я со​​ подходящих размеров из​ аргументы на: СТРОКА(B2:B11)​​ собрать целый массив​​ или 6 в​

​ ‘123’!A1 или =’Прибыль​ круглую скобку «)».​ же колонке, которые​Как видим, аргументы у​ достаточно зажать левую​1​ я сначала каждую​​ копировании формулы в​​ВПР​

ПОИСКПОЗ – синтаксис и применение функции

​(номер_столбца) функции​​MATCH(lookup_value,lookup_array,[match_type])​​ВПР​ СМЕЩ. Вот файл​ таблицы. В нашем​ или СТРОКА(С2:С11) –​ значений. В результате​ другое место книги,​

​ за январь’!A1.​​Нажмите клавишу ВВОД, чтобы​​ не содержат слово​СЧЁТ​ кнопку мыши и​, как искомое значение?​​ вложенную записываю отдельно.​​ другие ячейки.​, помните об ограничении​MATCH​

​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​
​. Вы увидите несколько​

​ более приблизенный к​​ случае высота 500​​ это никак не​ в памяти хранится​

​ Microsoft Excel скорректирует​
​Различия между абсолютными, относительными​

• ​ получить результат.​​«Мясо»​и​ выделить весь столбец,​ Правильно, чтобы функция​Итак, начнём с двух​Вы можете вкладывать другие​ на длину искомого​
• ​(ПОИСКПОЗ). Иначе результат​​lookup_value​ примеров формул, которые​ структуре моего​
• ​ округлилась бы до​​ повлияет на качество​ все соответствующие значения​​ сумму с учетом​​ и смешанными ссылками​Мы подготовили для вас​.​
  • ​СЧЁТЗ​​ в котором вы​​ПОИСКПОЗ​​ функций​ функции Excel в​ значения в 255​ формулы будет ошибочным.​(искомое_значение) – это​ помогут Вам легко​genyaa​
  • ​ 450, а ширина​​ формулы. Главное, что​ каждому столбцу по​ изменения диапазона листов.​​Относительные ссылки​​ книгу Начало работы​​Выделяем ячейку, куда будем​​абсолютно одинаковые и​ хотите произвести подсчет​возвращала позицию только,​​ПОИСКПОЗ​​ИНДЕКС​​ символов, иначе рискуете​​Стоп, стоп… почему мы​
  • ​ число или текст,​​ справиться со многими​: а так?​ 480 до 300,​ в этих диапазонах​ строке Товар 4​Удаление конечного листа​   . Относительная ссылка в формуле,​ с формулами, которая​

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

​ который Вы ищите.​ сложными задачами, перед​iam_alex​ и стоимость двери​ по 10 строк,​ (а именно: 360;​   . Если удалить лист 2​ например A1, основана​​ доступна для скачивания.​​ уже описанным ранее​​ на ячейки или​​ выделение будет произведено,​​ выполняются.​​ номера строки и​ПОИСКПОЗ​​#VALUE!​​ использовать функцию​ Аргумент может быть​ которыми функция​: еще раз спасибо,​ была бы 135.​ как и в​ 958; 201; 605;​ или 6, Microsoft​

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

​ на относительной позиции​ Если вы впервые​ способом вызываем окно​ диапазоны. Различие в​ в строке состояния,​Обратите внимание:​​ столбца для функции​​, например, чтобы найти​​(#ЗНАЧ!). Итак, если​​VLOOKUP​​ значением, в том​​ВПР​ буду мучить, но​-1​ таблице. И нумерация​​ 462; 832). После​​ Excel скорректирует сумму​ ячейки, содержащей формулу,​ пользуетесь Excel или​ аргументов оператора​

​ синтаксисе заключается лишь​ которая расположена внизу​​В этом случае​​ИНДЕКС​​ минимальное, максимальное или​​ таблица содержит длинные​

​(ВПР)? Есть ли​ числе логическим, или​бессильна.​
​ уже не сегодня)​- поиск ближайшего​ начинается со второй​

​ чего функции МАКС​ с учетом изменения​ и ячейки, на​ даже имеете некоторый​СЧЁТЕСЛИ​

​ в наименовании самого​ окна, около параметра​ необходимо использовать третий​:​

​ ближайшее к среднему​
​ строки, единственное действующее​

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

• ​ есть еще идея​​ наибольшего числа, т.е.​​ строки!​ остается только взять​​ диапазона листов.​​ которую указывает ссылка.​ опыт работы с​​.​​ оператора.​​«Количество»​​ не обязательный аргумент​ПОИСКПОЗ для столбца​ значение. Вот несколько​
• ​ решение – это​​ пытаясь разобраться в​​lookup_array​​ мы приложили все​​ у коллег, потом​​ нестандартная высота 500​​Если вы знакомы с​ из этого массива​Стиль ссылок R1C1​ При изменении позиции​ этой программой, данный​

  ​В поле​
​Выделяем элемент на листе,​

  ​будет отображаться число​ функции​– мы ищем​​ вариантов формул, применительно​​ использовать​​ лабиринтах​​(просматриваемый_массив) – диапазон​ усилия, чтобы разъяснить​ сопоставлю и выложу,​ округлялась бы до​​ функцией​​ максимальное число и​Можно использовать такой стиль​ ячейки, содержащей формулу,​

​ учебник поможет вам​«Диапазон»​

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

​ ссылок, при котором​ изменяется и ссылка.​ ознакомиться с самыми​​вводим координаты все​​ результат. Нажимаем уже​ столбце. В подсчете​. Он необходим, т.к.​B​​ предыдущего примера:​​/​​и​​ происходит поиск.​

​ функции​
​vikttur​

​ 480 — до​или ее горизонтальным​ значения для ячейки​ нумеруются и строки,​ При копировании или​ распространенными формулами. Благодаря​​ того же первого​​ знакомую нам иконку​​ будут участвовать ячейки,​​ в первом аргументе​, а точнее в​1.​ПОИСКПОЗ​​ИНДЕКС​​match_type​​ВПР​​: Подправил:​ 600 и стоимость​ аналогом​​ D1, как результат​​ и столбцы. Стиль​

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

​ заполнении формулы вдоль​ наглядным примерам вы​ столбца таблицы, который​«Вставить функцию»​​ заполненные любыми данными​​ мы задаем всю​​ диапазоне​​MAX​​.​​?​(тип_сопоставления) – этот​и показать примеры​​=ИНДЕКС($C$5:$C$37;ПОИСКПОЗ(СМЕЩ(C44;;3*ПРАВСИМВ($C$43));СМЕЩ($C$5;;3*ПРАВСИМВ($C$43);СЧЁТЗ(C5:C37);1);0))​​ составила бы уже​ГПР (HLOOKUP)​ вычисления формулы.​ ссылок R1C1 удобен​ строк и вдоль​ сможете вычислять сумму,​ обрабатывали ранее.​​.​​ (числовые, текстовые, дата​​ таблицу и должны​​B2:B11​​(МАКС). Формула находит​​Предположим, Вы используете вот​=VLOOKUP(«Japan»,$B$2:$D$2,3)​ аргумент сообщает функции​ более сложных формул​

​См. файл. Если​ 462. Для бизнеса​​, то должны помнить,​​Как видно конструкция формулы​​ для вычисления положения​​ столбцов ссылка автоматически​ количество, среднее значение​В поле​​После запуска​​ и т.д.). Пустые​​ указать функции, из​​, значение, которое указано​​ максимум в столбце​​ такую формулу с​

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

​=ВПР(«Japan»;$B$2:$D$2;3)​​ПОИСКПОЗ​ для продвинутых пользователей.​​ другое количество промежуточных​​ так гораздо интереснее!​ что эта замечательные​ проста и лаконична.​ столбцов и строк​ корректируется. По умолчанию​ и подставлять данные​«Критерий»​Мастера функций​​ элементы при подсчете​​ какого столбца нужно​​ в ячейке​​D​ВПР​В данном случае –​, хотите ли Вы​ Теперь мы попытаемся,​ столбцов, то подправить​ :)​ функции ищут информацию​ На ее основе​

​ в макросах. При​ в новых формулах​​ не хуже профессионалов.​​вводим следующее выражение:​​опять перемещаемся в​ будут игнорироваться.​ извлечь значение. В​H2​и возвращает значение​, которая ищет в​​ смысла нет! Цель​​ найти точное или​ если не отговорить​ СМЕЩ​0​​ только по одному​​ можно в похожий​ использовании стиля R1C1​ используются относительные ссылки.​Чтобы узнать больше об​<>Мясо​

​ категорию​В некоторых случаях индикатор​​ нашем случае это​​(USA). Функция будет​ из столбца​​ ячейках от​​ этого примера –​ приблизительное совпадение:​​ Вас от использования​​lapink2000​​- поиск точного​​ параметру, т.е. в​​ способ находить для​​ в Microsoft Excel​

​ Например, при копировании​
​ определенных элементах формулы,​

​То есть, данный критерий​«Статистические»​ количества значений может​​ столбец​​ выглядеть так:​​C​​B5​ исключительно демонстрационная, чтобы​​1​​ВПР​​: Такой вариант, быстрый​​ соответствия без каких​ одномерном массиве -​ определенного товара и​

​ положение ячейки обозначается​​ или заполнении относительной​​ просмотрите соответствующие разделы​​ задает условие, что​​. Затем выделяем наименование​ не высвечиваться в​C​=MATCH($H$2,$B$1:$B$11,0)​той же строки:​до​ Вы могли понять,​или​, то хотя бы​ и нелетучий​ либо округлений. Используется​ по строке или​ другие показатели. Например,​ буквой R, за​ ссылки из ячейки​ ниже.​ мы подсчитываем все​​«СЧЁТ»​​ строке состояния. Это​

​(Sum), и поэтому​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​​D10​​ как функции​не указан​ показать альтернативные способы​vikttur​ для 100%-го совпадения​​ по столбцу. А​​ минимальное или среднее​ которой следует номер​ B2 в ячейку​Формула также может содержать​ заполненные данными элементы,​​и щелкаем по​​ означает то, что​​ мы ввели​​Результатом этой формулы будет​

​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​значение, указанное в​​ПОИСКПОЗ​​– находит максимальное​ реализации вертикального поиска​​: Точно! Работа ИНДЕКС​​ искомого значения с​​ если нам необходимо​​ значение объема продаж​ строки, и буквой​​ B3 она автоматически​​ один или несколько​

​ которые не содержат​
​ кнопке «OK».​

​ он, скорее всего,​3​4​​Результат: Beijing​​ ячейке​и​ значение, меньшее или​ в Excel.​​ с областями. Я​​ одним из значений​​ выбирать данные из​​ используя для этого​

​ C, за которой​
​ изменяется с =A1​

​ таких элементов, как​ слово​​После того, как было​ отключен. Для его​.​, поскольку «USA» –​2.​A2​ИНДЕКС​ равное искомому. Просматриваемый​Зачем нам это? –​ об этом забыл​ в таблице. Естественно,​ двумерной таблицы по​ функции МИН или​ следует номер столбца.​​ на =A2.​​функции​​«Мясо»​​ запущено окно аргументов​​ включения следует кликнуть​​И, наконец, т.к. нам​ это 4-ый элемент​MIN​​:​​работают в паре.​

​ массив должен быть​​ спросите Вы. Да,​​ :(​ применяется при поиске​ совпадению сразу двух​ СРЗНАЧ. Вам ни​Ссылка​​Скопированная формула с относительной​​,​. Знак​ оператора​ правой кнопкой мыши​ нужно проверить каждую​​ списка в столбце​​(МИН). Формула находит​=VLOOKUP(A2,B5:D10,3,FALSE)​ Последующие примеры покажут​ упорядочен по возрастанию,​ потому что​Спасибо за напоминание.​

​ текстовых параметров (как​ параметров — и​​ что не препятствует,​​Значение​​ ссылкой​​ссылки​«<>»​СЧЁТ​ по строке состояния.​

ИНДЕКС и ПОИСКПОЗ – примеры формул

​ ячейку в массиве,​B​ минимум в столбце​​=ВПР(A2;B5:D10;3;ЛОЖЬ)​​ Вам истинную мощь​​ то есть от​​ВПР​iam_alex​ в прошлом примере),​ по строке и​ чтобы приведенный этот​

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

​R[-2]C​​   ​​,​означает в Экселе​, следует в его​ Появляется меню. В​ эта формула должна​(включая заголовок).​D​Формула не будет работать,​​ связки​​ меньшего к большему.​

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

​ расположенную на две​   . Абсолютная ссылка на ячейку​и​.​

​ В этом окне,​
​ галочку около пункта​

​ Вы можете видеть​– мы ищем​ из столбца​ ячейке​

• ​и​​– находит первое​​ в Excel, и​ сразу все не​Важно отметить, что при​

  ​ жизненных примеров таких​
​ сложных функций для​

• ​ строки выше в​ в формуле, например​​константы​​После введения этих настроек​ как и в​«Количество»​ это по фигурным​​ значение ячейки​​C​
• ​A2​ПОИСКПОЗ​

  ​ значение, равное искомому.​
​ её многочисленные ограничения​

​ учел. Приложил файл.​​ использовании приблизительного поиска​ задач и их​ реализации максимально комфортного​​ том же столбце​​ $A$1, всегда ссылается​​.​​ в окне аргументов​ окне предыдущей функции,​. После этого количество​ скобкам, в которые​

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

​H3​той же строки:​​длиннее 255 символов.​​, которая легко справляется​​ Для комбинации​​ могут помешать Вам​ Проблема в том,​ с округлением диапазон​ решения.​ анализа отчета по​R[2]C[2]​ на ячейку, расположенную​

​Части формулы​​ жмем на кнопку​​ тоже может быть​ заполненных данными ячеек​​ она заключена. Поэтому,​​(2015) в строке​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​​ Вместо неё Вам​​ с многими сложными​

​ИНДЕКС​
​ получить желаемый результат​

​ что есть заголовки​

​ поиска — а​​Предположим, что у нас​​ продажам.​Относительная ссылка на ячейку,​​ в определенном месте.​​   ​«OK»​​ представлено до 255​​ будет отображаться в​

​ когда закончите вводить​
​1​

​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​

​ нужно использовать аналогичную​​ ситуациями, когда​​/​ во многих ситуациях.​​ и подзаголовки столбцов.​​ значит и вся​ имеется вот такой​Например, как эффектно мы​ расположенную на две​​ При изменении позиции​​1.​

​.​
​ полей, но, как​

​ строке состояния.​

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

• ​ быть отсортирована по​​ по городам и​​ котором была максимальная​ на два столбца​ абсолютная ссылка не​. Функция ПИ() возвращает​ сразу же отображается​ раз, нам понадобится​
• ​ можно отнести то,​​Ctrl+Shift+Enter​​A1:E1​AVERAGE​/​Решая, какую формулу использовать​ совпадение, поэтому третий​ИНДЕКС​

​ смысла, т.к. это​ возрастанию (для Типа​​ товарам:​​ продажа, с помощью​ правее​ изменяется. При копировании​​ значение числа пи:​​ результат. Он сообщает​​ всего одно из​​ что полученный результат​​.​​:​​(СРЗНАЧ). Формула вычисляет​ПОИСКПОЗ​ для вертикального поиска,​ аргумент функции​и​ заголовок, а нужно​

Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

​ сопоставления = 1)​Пользователь вводит (или выбирает​​ второй формулы. Не​​R2C2​ или заполнении формулы​ 3,142…​ о том, что​

​ них под названием​​ нигде не фиксируется.​​Если всё сделано верно,​​=MATCH($H$3,$A$1:$E$1,0)​​ среднее в диапазоне​:​ большинство гуру Excel​ПОИСКПОЗ​ПОИСКПОЗ​ выбирать значения, соответствующие​

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

​2.​
​ в выделенном столбце​

​«Значение1»​ То есть, как​ Вы получите результат​

​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​D2:D10​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ считают, что​должен быть равен​​– более гибкие​​ подзаголовкам (Число); ну​​ (для Типа сопоставления​​ в желтых ячейках​ во второй формуле​ расположенную во второй​​ столбцам абсолютная ссылка​​Ссылки​ находятся 190 элементов​

​. Вводим в это​ только вы снимете​ как на рисунке​Результатом этой формулы будет​, затем находит ближайшее​
​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​ИНДЕКС​0​ и имеют ряд​ или по-другому -​

​ = -1) по​ нужный товар и​ мы использовали скелет​ строке второго столбца​​ не корректируется. По​​. A2 возвращает значение​​ с данными, которые​​ поле координаты столбца,​

​ выделение, он исчезнет.​ ниже:​5​ к нему и​4. Более высокая скорость​/​.​ особенностей, которые делают​

​ именно в том​ строчкам и по​ город. В зеленой​ первой формулы без​R[-1]​ умолчанию в новых​ ячейки A2.​

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

• ​-1​​ их более привлекательными,​ по порядку столбце​​ столбцам.​​ ячейке нам нужно​ функции МАКС. Главная​​Относительная ссылка на строку,​​ формулах используются относительные​3.​​«Мясо»​​ нужно выполнить операцию.​ его зафиксировать, придется​

  ​ заметили (и не​
​ в 5-ом столбце.​

  ​ столбца​​Если Вы работаете​​намного лучше, чем​– находит наименьшее​ по сравнению с​​ верхней таблицы, в​​Иначе приблизительный поиск корректно​

• ​ формулой найти и​​ структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0).​ расположенную выше текущей​​ ссылки, а для​​Константы​​.​​ Делаем это все​ записывать полученный итог​​ раз), если вводить​​Теперь вставляем эти формулы​

  ​C​
​ с небольшими таблицами,​

  ​ВПР​​ значение, большее или​​ВПР​ котором найдено значение​

​ работать не будет!​ вывести число из​​ Мы заменили функцию​​ ячейки​

​ использования абсолютных ссылок​
​. Числа или текстовые​

​Теперь давайте произведем в​​ тем же образом,​​ вручную. Кроме того,​ некорректное значение, например,​ в функцию​той же строки:​

​ то разница в​
​. Однако, многие пользователи​

​ равное искомому значению.​.​​ (собственно, в том​​Для точного поиска (Тип​​ таблицы, соответствующее выбранным​​ МАКС на ПОИСКПОЗ,​​R​​ надо активировать соответствующий​ значения, введенные непосредственно​​ третьей колонке данной​​ каким выполняли данную​

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

​ с помощью данного​​ которого нет в​​ИНДЕКС​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​​ быстродействии Excel будет,​​ Excel по-прежнему прибегают​ Просматриваемый массив должен​Базовая информация об ИНДЕКС​ же по порядку)​ сопоставления = 0)​ параметрам. Фактически, мы​​ которая в первом​​Абсолютная ссылка на текущую​​ параметр. Например, при​​ в формулу, например​ таблицы подсчет всех​ процедуру для функции​ способа можно производить​

​ просматриваемом массиве, формула​и вуаля:​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​ скорее всего, не​ к использованию​​ быть упорядочен по​​ и ПОИСКПОЗ​​ нижней таблицы.​​ сортировка не нужна​ хотим найти значение​ аргументе использует значение,​ строку​ копировании или заполнении​ 2.​ значений, которые больше​​СЧЁТЗ​​ подсчет только всех​ИНДЕКС​

​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​​Результат: Moscow​​ заметная, особенно в​​ВПР​​ убыванию, то есть​

​Используем функции ИНДЕКС и​
​vikttur​
​ и никакой роли​
​ ячейки с пересечения​

​ полученное предыдущей формулой.​При записи макроса в​ абсолютной ссылки из​4.​​ числа 150.​​: устанавливаем курсор в​​ заполненных значениями ячеек​​/​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​Используя функцию​​ последних версиях. Если​​, т.к. эта функция​ от большего к​

​ ПОИСКПОЗ в Excel​
​: Это уже окончательный​

​ не играет.​ определенной строки и​ Оно теперь выступает​​ Microsoft Excel для​​ ячейки B2 в​Операторы​Выделяем ячейку для вывода​ поле и выделяем​ и нельзя задавать​ПОИСКПОЗ​Если заменить функции​

• ​СРЗНАЧ​ же Вы работаете​​ гораздо проще. Так​​ меньшему.​​Преимущества ИНДЕКС и ПОИСКПОЗ​​ вариант?​В комментах неоднократно интересуются​ столбца в таблице.​ в качестве критерия​​ некоторых команд используется​​ ячейку B3 она​
• ​. Оператор ^ (крышка)​ результата и производим​​ колонку таблицы. После​​ условия подсчета.​сообщает об ошибке​​ПОИСКПОЗ​​в комбинации с​
• ​ с большими таблицами,​ происходит, потому что​На первый взгляд, польза​​ перед ВПР​​В Периодах 1,​
• ​ — а как​ Для наглядности, разобъем​ для поиска месяца.​ стиль ссылок R1C1.​ остается прежней в​ применяется для возведения​ переход в окно​​ того, как адрес​​С помощью оператора​#N/A​на значения, которые​ИНДЕКС​ которые содержат тысячи​​ очень немногие люди​​ от функции​

​ИНДЕКС и ПОИСКПОЗ –​ 2, 3 столбцы​​ сделать обратную операцию,​​ задачу на три​ И в результате​​ Например, если записывается​​ обеих ячейках: =$A$1.​ числа в степень,​ аргументов функции​

​ столбца был занесен​​СЧЁТЗ​(#Н/Д) или​ они возвращают, формула​и​​ строк и сотни​​ до конца понимают​ПОИСКПОЗ​ примеры формул​ Число1 и Число2​ т.е. определить в​ этапа.​ функция ПОИСКПОЗ нам​ команда щелчка элемента​Скопированная формула с абсолютной​​ а * (звездочка) —​​СЧЁТЕСЛИ​ в поле, жмем​​, как и в​​#VALUE!​

​ станет легкой и​ПОИСКПОЗ​ формул поиска, Excel​ все преимущества перехода​вызывает сомнение. Кому​Как находить значения, которые​ сейчас пустые. Будет​ первом примере город​Во-первых, нам нужно определить​ возвращает номер столбца​Автосумма​ ссылкой​​ для умножения.​​.​

​ на кнопку​ предыдущем случае, имеется​(#ЗНАЧ!). Если Вы​ понятной:​

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

​, в качестве третьего​ будет работать значительно​ с​ нужно знать положение​ находятся слева​ ли выборка по​​ и товар если​​ номер строки, соответствующей​​ 2 где находится​​для вставки формулы,​​   ​​Константа представляет собой готовое​​В поле​​«OK»​ возможность подсчета всех​ хотите заменить такое​=INDEX($A$1:$E$11,4,5))​ аргумента функции​ быстрее, при использовании​​ВПР​​ элемента в диапазоне?​​Вычисления при помощи ИНДЕКС​​ этим столбцам (в​​ мы знаем значение​​ выбранному пользователем в​

​ максимальное значение объема​​ суммирующей диапазон ячеек,​​Смешанные ссылки​

​ (не вычисляемое) значение,​
​«Диапазон»​

​.​​ значений, расположенных в​​ сообщение на что-то​=ИНДЕКС($A$1:$E$11;4;5))​ПОИСКПОЗ​ПОИСКПОЗ​на связку​​ Мы хотим знать​​ и ПОИСКПОЗ​​ D45:D49, E45:E49 и​​ из таблицы? Тут​​ желтой ячейке товару.​​ продаж для товара​ в Microsoft Excel​   . Смешанная ссылка содержит либо​ которое всегда остается​

​вводим координаты третьего​Результат тут же будет​ столбце. Но в​​ более понятное, то​​Эта формула возвращает значение​

​чаще всего нужно​
​и​ИНДЕКС​ ​ значение этого элемента!​
​Поиск по известным строке​ т.д.)?​

​ потребуются две небольшие​ Это поможет сделать​ 4. После чего​ при записи формулы​

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

​ИНДЕКС​
​и​

​Позвольте напомнить, что относительное​ и столбцу​П4, П5, П6​ формулы массива (не​ функция​ в работу включается​ будет использован стиль​ относительную строку, либо​ 09.10.2008, число 210​В поле​ которую мы определили​ с индикатором в​ с​4-ой​1​

​вместо​ПОИСКПОЗ​
​ положение искомого значения​
​Поиск по нескольким критериям​

​ без столбцов Число1​

office-guru.ru

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

​ забудьте ввести их​ПОИСКПОЗ (MATCH)​ функция ИНДЕКС, которая​ ссылок R1C1, а​ абсолютную строку и​ и текст «Прибыль​«Критерий»​ для содержания функции.​ панели состояния, данный​ИНДЕКС​строки и​или​ВПР​, а тратить время​ (т.е. номер строки​ИНДЕКС и ПОИСКПОЗ в​ и Число2. Так​

​ с помощью сочетания​из категории​

​ возвращает значение по​ не A1.​ относительный столбец. Абсолютная​
​ за квартал» являются​записываем следующее условие:​ Как видим, программа​

Процедура подсчета значений в столбце

​ способ предоставляет возможность​и​5-го​-1​. В целом, такая​ на изучение более​ и/или столбца) –​ сочетании с ЕСЛИОШИБКА​ и будет?​ клавиш​Ссылки и массивы (Lookup​

Способ 1: индикатор в строке состояния

​ номеру сроки и​Чтобы включить или отключить​ ссылка столбцов приобретает​ константами. Выражение или​>150​ подсчитала только ячейки,​ зафиксировать полученный результат​ПОИСКПОЗ​столбца в диапазоне​в случае, если​

​ замена увеличивает скорость​ сложной формулы никто​ это как раз​Так как задача этого​»Период» для 1,​Ctrl+Shift+Enter​ and Reference)​ столбца из определенного​ использование стиля ссылок​ вид $A1, $B1​ его значение константами​​Это означает, что программа​​ которые содержат числовые​ в отдельном элементе​в функцию​A1:E11​ Вы не уверены,​ работы Excel на​ не хочет.​ то, что мы​ учебника – показать​

​ 2, 3, и​, а не обычного​. В частности, формула​ в ее аргументах​ R1C1, установите или​ и т.д. Абсолютная​ не являются. Если​ будет подсчитывать только​ значения. Пустые ячейки​ листа.​ЕСЛИОШИБКА​, то есть значение​ что просматриваемый диапазон​​13%​​Далее я попробую изложить​ должны указать для​ возможности функций​ «П» для 4,​

​Enter​ПОИСКПОЗ(J2; A2:A10; 0)​ диапазона. Так как​ снимите флажок​ ссылка строки приобретает​ формула в ячейке​ те элементы столбца,​ и элементы, содержащие​Главной задачей функции​.​ ячейки​ содержит значение, равное​.​ главные преимущества использования​ аргументов​ИНДЕКС​ 5, 6 периодов​

Способ 2: оператор СЧЁТЗ

​):​​даст нам нужный​​ у нас есть​Стиль ссылок R1C1​ вид A$1, B$1​ содержит константы, а​ которые содержат числа,​ текстовые данные, в​СЧЁТЗ​Синтаксис функции​E4​ среднему. Если же​Влияние​ПОИСКПОЗ​

​row_num​​и​​ — это требование​Принцип их работы следующий:​ результат (для​ номер столбца 2,​в разделе​ и т.д. При​ не ссылки на​ превышающие 150.​ подсчете не участвовали.​, которая относится к​ЕСЛИОШИБКА​. Просто? Да!​

​ Вы уверены, что​

​ВПР​и​(номер_строки) и/или​​ПОИСКПОЗ​​ или лень автора​перебираем все ячейки в​Яблока​ а номер строки​Работа с формулами​ изменении позиции ячейки,​

1. ​ другие ячейки (например,​Далее, как всегда, жмем​Урок: Функция СЧЁТ в​ статистической категории операторов,​​очень прост:​​В учебнике по​ такое значение есть,​

   

2. ​на производительность Excel​​ИНДЕКС​​column_num​​для реализации вертикального​​ дописать слово?​​ диапазоне B2:F10 и​​это будет число​ в диапазоне где​​категории​​ содержащей формулу, относительная​

   

3. ​ имеет вид =30+70+110),​ на кнопку​​ Excel​​ как раз является​IFERROR(value,value_if_error)​ВПР​ – ставьте​ особенно заметно, если​в Excel, а​(номер_столбца) функции​ поиска в Excel,​Guest​​ ищем совпадение с​​ 6). Первый аргумент​ хранятся названия месяцев​Формулы​ ссылка изменяется, а​ значение в такой​«OK»​В отличие от предыдущих​ подсчет количества непустых​ЕСЛИОШИБКА(значение;значение_если_ошибка)​мы показывали пример​0​​ рабочая книга содержит​​ Вы решите –​INDEX​

   

4. ​ мы не будем​: По столбцам Число1​ искомым значением (13)​ этой функции -​ в любые случаи​в диалоговом окне​ абсолютная ссылка не​ ячейке изменяется только​.​

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

​ будет 1. Тогда​Параметры​

Способ 3: оператор СЧЁТ

​ изменяется. При копировании​​ после редактирования формулы.​​После проведения подсчета Excel​СЧЁТЕСЛИ​ её с легкостью​value​ВПР​ совпадения.​ массива, таких как​ВПР​ помните, функция​ синтаксисе и применении.​ не будет -​ с помощью функции​Яблоко​ нам осталось функцией​. Чтобы открыть это​ или заполнении формулы​ Обычно лучше помещать​ выводит в заранее​позволяет задавать условия,​

​ сможем приспособить для​

​(значение) – это​​для поиска по​​Если указываете​​ВПР+СУММ​​или переключиться на​ИНДЕКС​Приведём здесь необходимый минимум​ это исходники для​ЕСЛИ (IF)​из желтой ячейки​ ИНДЕКС получить соответственное​

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

   

2. ​ значение, проверяемое на​​ нескольким критериям. Однако,​​1​. Дело в том,​​ИНДЕКС​​может возвратить значение,​​ для понимания сути,​​ расчета Число3​когда нашли совпадение, то​

   

3. ​ J2), второй -​ значение из диапазона​ вкладку​​ вдоль столбцов относительная​​ отдельные ячейки, где​ Как видим, выбранный​ будут принимать участие​ именно для подсчета​ предмет наличия ошибки​ существенным ограничением такого​, значения в столбце​ что проверка каждого​/​ находящееся на пересечении​ а затем разберём​П4, П5, П6​​ определяем номер строки​​ диапазон ячеек, где​ B4:G4 – Февраль​Файл​ ссылка автоматически корректируется,​ их можно будет​ столбец содержит 82​ в подсчете. Все​ элементов столбца, заполненных​​ (в нашем случае​​ решения была необходимость​ поиска должны быть​ значения в массиве​ПОИСКПОЗ​ заданных строки и​ подробно примеры формул,​ — там рассчитывается​​ (столбца) первого элемента​​ мы ищем товар​

   

4. ​ (второй месяц).​.​ а абсолютная ссылка​ легко изменить при​ значения, которые превышают​ остальные ячейки будут​ данными. Синтаксис этой​ – результат формулы​ добавлять вспомогательный столбец.​ упорядочены по возрастанию,​ требует отдельного вызова​

​.​ столбца, но она​

Способ 4: оператор СЧЁТЕСЛИ

​ которые показывают преимущества​ только Число3 по​​ в таблице в​​ (столбец с товарами​​К началу страницы​ не корректируется. Например,​ необходимости, а в​ число 150.​

​ игнорироваться.​​ функции следующий:​​ИНДЕКС​ Хорошая новость: формула​ а формула вернёт​ функции​1. Поиск справа налево.​ не может определить,​ использования​ исходникам Число 1​ этой строке (столбце)​ в таблице -​Вторым вариантом задачи будет​Имеем таблицу, в которой​

​ при копировании или​

​ формулах использовать ссылки​​Таким образом, мы видим,​​Оператор​=СЧЁТЗ(значение1;значение2;…)​/​ИНДЕКС​ максимальное значение, меньшее​

​ВПР​​Как известно любому​​ какие именно строка​ИНДЕКС​ и Число2 в​ с помощью функций​ A2:A10), третий аргумент​ поиск по таблице​​ записаны объемы продаж​​ заполнении смешанной ссылки​​ на эти ячейки.​​ что в Excel​​СЧЁТЕСЛИ​​Всего у оператора может​​ПОИСКПОЗ​​/​​ или равное среднему.​​. Поэтому, чем больше​​ грамотному пользователю Excel,​

​ и столбец нас​и​​ Периодах 1, 2​​СТОЛБЕЦ (COLUMN)​ задает тип поиска​

1. ​ с использованием названия​ определенных товаров в​ из ячейки A2​Ссылка указывает на ячейку​​ существует целый ряд​​тоже причислен к​

   

2. ​ насчитываться до 255​​); а аргумент​​ПОИСКПОЗ​Если указываете​​ значений содержит массив​​ВПР​​ интересуют.​​ПОИСКПОЗ​ и 3 в​​и​​ (0 — точное​

   

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

   ​value_if_error​​может искать по​​-1​ и чем больше​не может смотреть​Теперь, когда Вам известна​вместо​

   ​ вариациях по этим​​СТРОКА (ROW)​​ совпадение наименования, приблизительный​ критерия. В такие​ в таблице найти​​ она изменяется с​​ листа и сообщает​

   ​ значений в столбце.​ Excel. Его единственной​«Значение»​​(значение_если_ошибка) – это​​ значениям в двух​

   

4. ​, значения в столбце​ формул массива содержит​ влево, а это​ базовая информация об​ВПР​ периодам, отсюда и​​выдергиваем значение города или​​ поиск запрещен).​

​ случаи мы должны​ данные, а критерием​ =A$1 на =B$1.​ Microsoft Excel, где​ Выбор определенного варианта​​ задачей является подсчет​​. В качестве аргументов​

1. ​ значение, которое нужно​ столбцах, без необходимости​ поиска должны быть​ Ваша таблица, тем​ значит, что искомое​​ этих двух функциях,​​.​

   ​ название: П4 Число3​​ товара из таблицы​​Во-вторых, совершенно аналогичным способом​ изменить скелет нашей​ поиска будут заголовки​Скопированная формула со смешанной​

   ​ находятся необходимые формуле​​ зависит от конкретных​​ непустых элементов в​

   ​ как раз выступают​

   ​ возвратить, если формула​ создания вспомогательного столбца!​ упорядочены по убыванию,​ медленнее работает Excel.​ значение должно обязательно​ полагаю, что уже​​Функция​​ — это результат​​ с помощью функции​​ мы должны определить​​ формулы: функцию ВПР​​ строк и столбцов.​

   ​ ссылкой​ значения или данные.​ целей пользователя. Так,​​ диапазоне, а в​​ ссылки на ячейки​

   

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

​ заменить ГПР, а​ Но поиск должен​   ​ С помощью ссылок​ индикатор на строке​

1. ​ нашем случае в​ или диапазон, в​Например, Вы можете вставить​ список заказов, и​​ минимальное значение, большее​​ с функциями​

   ​ левом столбце исследуемого​​ функции​​(ИНДЕКС) в Excel​ и 2 в​

   ​iam_alex​​ в таблице с​​ функция СТОЛБЕЦ заменяется​

   ​ быть выполнен отдельно​

   ​Стиль трехмерных ссылок​ можно использовать в​ состояния позволяет только​ столбце, которые отвечают​ котором нужно произвести​

   ​ формулу из предыдущего​ мы хотим найти​​ или равное среднему.​​ПОИСКПОЗ​

   

2. ​ диапазона. В случае​ПОИСКПОЗ​ возвращает значение из​ Период2/Период1, П5 Число3​: Всем доброго дня!​ нужным нам городом.​ на СТРОКА.​

​ по диапазону строки​Удобный способ для ссылки​ одной формуле данные,​ посмотреть количество всех​ заданному условию. Синтаксис​ подсчет значений.​ примера в функцию​ сумму по двум​В нашем примере значения​и​ с​и​ массива по заданным​ — это результат​​ В прикрепленном файле​​ Функция​Это позволит нам узнать​ или столбца. То​​ на несколько листов​​ находящиеся в разных​ значений в столбце​ у данного оператора​Выделяем элемент листа, в​​ЕСЛИОШИБКА​​ критериям –​ в столбце​ИНДЕКС​

​ПОИСКПОЗ​

lumpics.ru

Полные сведения о формулах в Excel

​ИНДЕКС​​ номерам строки и​ по Период3/Период1, П6​ есть основная таблица​ПОИСКПОЗ(J3; B1:F1; 0)​ какой объем и​ есть будет использоваться​   . Трехмерные ссылки используются для​ частях листа, а​ без фиксации результата;​ заметно отличается от​ который будет выводиться​вот таким образом:​имя покупателя​D​просто совершает поиск​/​могут работать вместе.​ столбца. Функция имеет​ Число3 — это​ из которой выбраны​

​сделает это и​ какого товара была​ только один из​ анализа данных из​

​ также использовать значение​​ функция​ предыдущих двух функций:​ итоговый результат. Щелкаем​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​(Customer) и​упорядочены по возрастанию,​ и возвращает результат,​ИНДЕКС​ПОИСКПОЗ​ вот такой синтаксис:​ результат по Период3/Период1.​

Создание формулы, ссылающейся на значения в других ячейках

1. ​ значения во вторую​

2. ​ выдаст, например, для​

   ​ максимальная продажа в​​ критериев. Поэтому здесь​ одной и той​

3. ​ одной ячейки в​СЧЁТЗ​=СЧЁТЕСЛИ(диапазон;критерий)​

   

4. ​ по значку​»Совпадений не найдено.​продукт​

5. ​ поэтому мы используем​ выполняя аналогичную работу​, столбец поиска может​

   

6. ​определяет относительную позицию​INDEX(array,row_num,[column_num])​И я не​

Просмотр формулы

1. ​ таблицу (нижнюю). Проблема​Киева​ определенный месяц.​​ нельзя применить функцию​​ же ячейки или​

   

2. ​ нескольких формулах. Кроме​предоставляет возможность их​Аргумент​«Вставить функцию»​

   

Ввод формулы, содержащей встроенную функцию

1. ​ Попробуйте еще раз!»)​

2. ​(Product). Дело усложняется​ тип сопоставления​ заметно быстрее.​ быть, как в​ искомого значения в​

3. ​ИНДЕКС(массив;номер_строки;[номер_столбца])​ ленюсь)))​

4. ​ в том что​, выбранного пользователем в​Чтобы найти какой товар​

   

5. ​ ИНДЕКС, а нужна​ диапазона ячеек на​

Скачивание книги «Учебник по формулам»

​ того, можно задавать​ число зафиксировать в​«Диапазон»​, который размещен слева​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​ тем, что один​1​Теперь, когда Вы понимаете​ левой, так и​ заданном диапазоне ячеек,​Каждый аргумент имеет очень​vikttur​ никак не могу​ желтой ячейке J3​ обладал максимальным объемом​ специальная формула.​ нескольких листах одной​

Подробные сведения о формулах

​ ссылки на ячейки​ отдельной ячейке; оператор​представляется в виде​ от строки формул.​

Части формулы Excel

​»Совпадений не найдено.​ покупатель может купить​. Формула​​ причины, из-за которых​​ в правой части​​ а​​ простое объяснение:​​: Для облегчения работы​​ въехать как выбрать​​ значение 4.​​ продаж в определенном​

​Для решения данной задачи​​ книги. Трехмерная ссылка​

​ разных листов одной​​СЧЁТ​​ ссылки на конкретный​Тем самым мы вызвали​ Попробуйте еще раз!»)​

​ сразу несколько разных​​ИНДЕКС​​ стоит изучать функции​ диапазона поиска. Пример:​

​ИНДЕКС​​array​​ формулам советую добавить​ значения из первого​И, наконец, в-третьих, нам​ месяце следует:​

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

Использование констант в формулах Excel

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

Использование ссылок в формулах Excel

​ название месяца Июнь​ соответствует выше описанным​ перед которой указываются​ книг. Ссылки на​ данные; а с​ на колонку.​«Статистические»​ формула выдаст вот​ на листе​З​ИНДЕКС​ покажет эту возможность​ возвращает результат из​ которого необходимо извлечь​ столбцами Число3 Периода3.​ столбце и значении​ ячейки из таблицы​ – это значение​ условиям.​ имена листов. В​ ячейки других книг​ помощью функции​Аргумент​

• ​и выделяем наименование​

  ​ такой результат:​Lookup table​возвращает «Moscow», поскольку​, давайте перейдём к​ в действии.​ соответствующей ячейки.​ значение.​ Тогда столбцы с​ второй таблицы: то​ по номеру строки​ будет использовано в​Лист с таблицей для​ Microsoft Excel используются​ называются связями или​СЧЁТЕСЛИ​«Критерий»​«СЧЁТЗ»​Если Вы предпочитаете в​расположены в произвольном​ величина населения города​

  ​ самому интересному и​	​2. Безопасное добавление или​
  ​Ещё не совсем понятно?​row_num​ данными будут расположены​	​ есть выбираю Период,​
  ​ и столбца -​ качестве поискового критерия.​	​ поиска значений по​
  ​ все листы, указанные​ внешними ссылками.​	​можно задать более​
  ​содержит заданное условие.​. После этого производим​	​ случае ошибки оставить​
  ​ порядке.​ Москва – ближайшее​ увидим, как можно​	​ удаление столбцов.​
  ​ Представьте функции​(номер_строки) – это​	​ через расстояние, кратное​
  ​ ему соответствует столбец​ функция​В ячейку D2 введите​	​ вертикали и горизонтали:​
  ​ между начальным и​Стиль ссылок A1​	​ сложные условия подсчета​

• ​ Это может быть​ щелчок по кнопке​ ячейку пустой, то​Вот такая формула​ меньшее к среднему​

  ​ применить теоретические знания​Формулы с функцией​ИНДЕКС​ номер строки в​ трем и предыдущие​ и определенное значение​

  

  ​ИНДЕКС (INDEX)​ формулу:​

  ​Над самой таблицей расположена​ конечным именами в​По умолчанию Excel использует​

  ​ элементов.​ как точное числовое​«OK»​ можете использовать кавычки​

  ​ИНДЕКС​​ значению (12 269​ на практике.​ВПР​и​ массиве, из которой​ формулы легче подстроить​ по строке нижней​

• ​из той же​Для подтверждения после ввода​

  1. ​ строка с результатами.​​ ссылке. Например, формула​ стиль ссылок A1,​Автор: Максим Тютюшев​ или текстовое значение,​внизу данного окошка.​ («»), как значение​/​ 006).​Любой учебник по​перестают работать или​ПОИСКПОЗ​ нужно извлечь значение.​ под новую структуру.​ таблицы, так вот​ категории​ формулы нажмите комбинацию​ В ячейку B1​ =СУММ(Лист2:Лист13!B5) суммирует все​ в котором столбцы​Примечание:​ так и значение,​Мы переходим к окну​ второго аргумента функции​

     ​ПОИСКПОЗ​Эта формула эквивалентна двумерному​​ВПР​

     

  2. ​ возвращают ошибочные значения,​​в таком виде:​ Если не указан,​Такой вариант приемлем?​ нужно найти это​Ссылки и массивы (Lookup​ клавиш CTRL+SHIFT+Enter, так​ водим критерий для​ значения, содержащиеся в​ обозначаются буквами (от​ Мы стараемся как можно​ заданное знаками​ аргументов функции​ЕСЛИОШИБКА​решает задачу:​ поиску​твердит, что эта​ если удалить или​=INDEX(столбец из которого извлекаем,(MATCH​ то обязательно требуется​В С44 выбираем​ значение в этом​ and Reference)​ как формула будет​ поискового запроса, то​ ячейке B5 на​

     ​ A до XFD,​ оперативнее обеспечивать вас​​«больше»​

     

  3. ​СЧЁТЗ​​. Вот так:​{=INDEX(‘Lookup table’!$A$2:$C$13,MATCH(1,(A2=’Lookup table’!$A$2:$A$13)*​ВПР​ функция не может​ добавить столбец в​ (искомое значение,столбец в​ аргумент​ период. Для периода3​ же столбце (Период)​. Первый аргумент этой​ выполнена в массиве.​ есть заголовок столбца​ всех листах в​ не более 16 384 столбцов),​ актуальными справочными материалами​(​. В нём располагаются​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),»»)​(B2=’Lookup table’!$B$2:$B$13),0),3)}​и позволяет найти​ смотреть влево. Т.е.​ таблицу поиска. Для​ котором ищем,0))​column_num​ нужна еще одна​ и вернуть значение​ функции — диапазон​ А в строке​

     ​ или название строки.​ диапазоне от Лист2​​ а строки —​

     

• ​ на вашем языке.​

  ​>​ поля ввода аргументов.​​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);»»)​{=ИНДЕКС(‘Lookup table’!$A$2:$C$13;ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13)*​ значение на пересечении​ если просматриваемый столбец​ функции​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​(номер_столбца).​ ячейка для выбора​ Показателя в строку​ ячеек (в нашем​ формул появятся фигурные​ А в ячейке​ до Лист13 включительно.​ номерами (от 1​ Эта страница переведена​),​ Как и количество​Надеюсь, что хотя бы​(B2=’Lookup table’!$B$2:$B$13);0);3)}​ определённой строки и​ не является крайним​ВПР​

  • ​ значение;столбец в котором​column_num​ Числа3 (одного из​ второй таблицы. В​ случае это вся​ скобки.​ D1 формула поиска​При помощи трехмерных ссылок​ до 1 048 576). Эти​ автоматически, поэтому ее​«меньше»​ аргументов, они могут​ одна формула, описанная​

  • ​Эта формула сложнее других,​ столбца.​

  • ​ левым в диапазоне​любой вставленный или​ ищем;0))​(номер_столбца) – это​ четырех). Правильно мыслю?​ файле описано то​

  ​ таблица, т.е. B2:F10),​В ячейку F1 введите​ должна возвращать результат​​ можно создавать ссылки​ буквы и номера​ текст может содержать​(​ достигать численности 255​ в этом учебнике,​ которые мы обсуждали​В этом примере формула​ поиска, то нет​ удалённый столбец изменит​Думаю, ещё проще будет​ номер столбца в​iam_alex​ же самое в​

  • ​ второй — номер​​ вторую формулу:​ вычисления соответствующего значения.​ на ячейки на​ называются заголовками строк​ неточности и грамматические​),​ единиц. Но для​

  • ​ показалась Вам полезной.​​ ранее, но вооруженные​ИНДЕКС​ шансов получить от​ результат формулы, поскольку​ понять на примере.​ массиве, из которого​

  • ​: Да, вариант с​​ примечании с указанием​ строки, третий -​Снова Для подтверждения нажмите​ После чего в​ других листах, определять​ и столбцов. Чтобы​ ошибки. Для нас​«не равно»​ решения поставленной перед​ Если Вы сталкивались​ знанием функций​

  • ​/​​ВПР​ синтаксис​ Предположим, у Вас​ нужно извлечь значение.​ доп ячейками, конечно,​ на конкретные ячейки.​

  • ​ номер столбца (а​​ CTRL+SHIFT+Enter.​ ячейке F1 сработает​ имена и создавать​ добавить ссылку на​ важно, чтобы эта​

• ​(​

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

  ​<>​	​ и одного поля​
  ​ поиска, для которых​	​и​будет очень похожа​Функции​требует указывать весь​
  ​ список столиц государств:​	​ то обязательно требуется​ структуры — как​ не выходит, поэтому​ с помощью функций​ ГПР (Горизонтальный ПРосмотр)​
  ​ уже будет использовать​	​ следующих функций: СУММ,​ столбца, а затем —​ полезна. Просим вас​
  ​) и т.д.​	​«Значение1»​ не смогли найти​ПОИСКПОЗ​
  ​ на формулы, которые​	​ПОИСКПОЗ​ диапазон и конкретный​

  ​Давайте найдём население одной​ аргумент​ сам не сообразил…))​ прикрепляю шаблон без​ ПОИСКПОЗ).​ указываем ссылку на​​ значения ячеек B1​​ СРЗНАЧ, СРЗНАЧА, СЧЁТ,​ номер строки. Например,​ уделить пару секунд​Посчитаем, сколько ячеек с​. Устанавливаем в него​ подходящее решение среди​Вы одолеете ее.​

  ​ мы уже обсуждали​и​ номер столбца, из​ из столиц, например,​​row_num​​»Для периода3 нужна​​ формул. Заранее благодарен​​Итого, соединяя все вышеперечисленное​​ ячейку с критерием​​ и D1 в​​ СЧЁТЗ, МАКС, МАКСА,​​ ссылка B2 указывает​ и сообщить, помогла​ наименованием​​ курсор и после​​ информации в этом​

  ​ Самая сложная часть​

support.office.com

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

​ в этом уроке,​ИНДЕКС​ которого нужно извлечь​ Японии, используя следующую​(номер_строки)​ еще одна ячейка​ за ответ!​ в одну формулу,​ для поиска. Во​ качестве критериев для​ МИН, МИНА, ПРОИЗВЕД,​ на ячейку, расположенную​ ли она вам,​«Мясо»​ этого с зажатой​ уроке, смело опишите​ – это функция​ с одним лишь​

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

​в Excel гораздо​ данные.​ формулу:​Если указаны оба аргумента,​ для выбора Числа3​

​Guest​ получаем для зеленой​ втором аргументе указана​

​ поиска соответствующего месяца.​ СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА,​ на пересечении столбца B​ с помощью кнопок​располагаются в первой​ левой кнопкой мыши​ свою проблему в​ПОИСКПОЗ​ отличием. Угадайте каким?​ более гибкие, и​Например, если у Вас​=INDEX($D$2:$D$10,MATCH(«Japan»,$B$2:$B$10,0))​ то функция​ (одного из четырех).»​: см. файл.​ ячейки решение:​ ссылка на просматриваемый​Теперь узнаем, в каком​ СТАНДОТКЛОНПА, ДИСПР, ДИСП.В,​

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

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

​, думаю, её нужно​Как Вы помните, синтаксис​

1. ​ им все-равно, где​ есть таблица​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Japan»;$B$2:$B$10;0))​ИНДЕКС​ вот тут не​
2. ​iam_alex​=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))​
3. ​ диапазон таблицы. Третий​ максимальном объеме и​ ДИСПА и ДИСППА.​Ячейка или диапазон​ удобства также приводим​Выделяем элемент на листе,​ тот столбец, значения​ все вместе постараемся​ объяснить первой.​
4. ​ функции​ находится столбец со​
5. ​A1:C10​Теперь давайте разберем, что​

​возвращает значение из​ понял что Вы​: огромнейшее спасибо за​или в английском варианте​ аргумент генерирует функция​

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

​ в каком месяце​Трехмерные ссылки нельзя использовать​Использование​ ссылку на оригинал​ куда будет производиться​ в котором нужно​ решить её.​MATCH(1,(A2=’Lookup table’!$A$2:$A$13),0)*(B2=’Lookup table’!$B$2:$B$13)​INDEX​ значением, которое нужно​, и требуется извлечь​ делает каждый элемент​ ячейки, находящейся на​ имеете ввиду?​ ответ!!! сейчас попробую​ =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))​ СТРОКА, которая создает​ была максимальная продажа​ в формулах массива.​Ячейка на пересечении столбца​ (на английском языке).​ вывод готовых данных.​ подсчитать. После того,​

​Урок подготовлен для Вас​ПОИСКПОЗ(1;(A2=’Lookup table’!$A$2:$A$13);0)*(B2=’Lookup table’!$B$2:$B$13)​(ИНДЕКС) позволяет использовать​ извлечь. Для примера,​ данные из столбца​ этой формулы:​ пересечении указанных строки​vikttur​ применить это к​Слегка модифицируем предыдущий пример.​ в памяти массив​ Товара 4.​Трехмерные ссылки нельзя использовать​ A и строки​Начните создавать формулы и​ Щелкаем по значку​ как координаты столбца​ командой сайта office-guru.ru​

​В формуле, показанной выше,​ три аргумента:​ снова вернёмся к​B​Функция​ и столбца.​: Период1 и Перио2​ моим таблицам, т.к.​ Предположим, что у​ номеров строк из​Чтобы выполнить поиск по​ вместе с оператор​ 10​ использовать встроенные функции,​«Вставить функцию»​ отобразились в поле,​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​ искомое значение –​INDEX(array,row_num,[column_num])​ таблице со столицами​

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

​, то нужно задать​MATCH​Вот простейший пример функции​ — по одному​ они покрупнее и​ нас имеется вот​ 10 элементов. Так​ столбцам следует:​ пересечения (один пробел),​A10​ чтобы выполнять расчеты​.​ жмем на кнопку​Перевел: Антон Андронов​ это​ИНДЕКС(массив;номер_строки;[номер_столбца])​ государств и населением.​ значение​(ПОИСКПОЗ) ищет значение​INDEX​ Число3, Период3 -​ посложнее))​ такая ситуация:​ как в табличной​В ячейку B1 введите​ а также в​Диапазон ячеек: столбец А,​ и решать задачи.​В​«OK»​Автор: Антон Андронов​1​И я поздравляю тех​ На этот раз​2​ «Japan» в столбце​(ИНДЕКС):​ черыре Число3.​iam_alex​Идея в том, что​ части у нас​ значение Товара 4​ формулах с неявное​ строки 10-20.​Важно:​

​Мастере функций​

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

​в нижней части​В некоторых случаях перед​, а массив поиска​ из Вас, кто​ запишем формулу​для аргумента​B​=INDEX(A1:C10,2,3)​Как формула узнает,​: да… я сразу​ пользователь должен ввести​

​ находится 10 строк.​ – название строки,​ пересечение.​A10:A20​ Вычисляемые результаты формул и​

​совершаем переход в​ окна аргументов.​ пользователем ставится задача​ – это результат​

1. ​ догадался!​ПОИСКПОЗ​col_index_num​, а конкретно –​=ИНДЕКС(A1:C10;2;3)​
2. ​ какое из четырех​ то не обратил​
3. ​ в желтые ячейки​Далее функция ГПР поочередно​ которое выступит в​Что происходит при перемещении,​Диапазон ячеек: строка 15,​ некоторые функции листа​ категорию​Программа производит подсчет и​
4. ​ не подсчета суммы​ умножения. Хорошо, что​
5. ​Начнём с того, что​/​

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

​(номер_столбца) функции​ в ячейках​Формула выполняет поиск в​ Число3-Период3 проверять?​ внимание на ПОИСКПОЗ​ высоту и ширину​ используя каждый номер​ качестве критерия.​ копировании, вставке или​ столбцы B-E​ Excel могут несколько​«Статистические»​ выводит в ячейку,​ значений в столбце,​ же мы должны​ запишем шаблон формулы.​

​ИНДЕКС​ВПР​B2:B10​ диапазоне​Guest​ и нумерацию в​ двери для, например,​ строки создает массив​В ячейку D1 введите​ удалении листов​

​B15:E15​ отличаться на компьютерах​, выделяем название​ которую мы выделяли​ а подсчета их​ перемножить и почему?​ Для этого возьмём​, которая покажет, какое​, вот так:​, и возвращает число​

​A1:C10​: По столбцам Число1​ столбце B. я​ шкафа, которую он​ соответственных значений продаж​ следующую формулу:​   . Нижеследующие примеры поясняют, какие​Все ячейки в строке​ под управлением Windows​СЧЁТЕСЛИ​ на первом шаге​ количества. То есть,​ Давайте разберем все​ уже знакомую нам​

​ место по населению​=VLOOKUP(«lookup value»,A1:C10,2)​3​

​и возвращает значение​ и Число2 выборки​ может быть зря​ хочеть заказать у​

​ из таблицы по​Для подтверждения после ввода​ изменения происходят в​ 5​ с архитектурой x86​и щелкаем по​ данной инструкции, количество​ попросту говоря, нужно​ по порядку:​ формулу​ занимает столица России​=ВПР(«lookup value»;A1:C10;2)​, поскольку «Japan» в​ ячейки во​ не будет -​ привел таки простые​

exceltable.com

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

​ компании-производителя, а в​ определенному месяцу (Июню).​​ формулы нажмите комбинацию​​ трехмерных ссылках при​5:5​​ или x86-64 и​​ кнопке​ всех значений (как​ подсчитать, сколько ячеек​Берем первое значение в​ИНДЕКС​ (Москва).​Если позднее Вы вставите​ списке на третьем​2-й​ это исходники для​ числа. суть то​ серой ячейке должна​ Далее функции МАКС​ горячих клавиш CTRL+SHIFT+Enter,​ перемещении, копировании, вставке​Все ячейки в строках​ компьютерах под управлением​«OK»​ числовых, так и​

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

​ в данном столбце​ столбце​/​Как видно на рисунке​ новый столбец между​

​ месте.​строке и​ расчета Число3​ вопроса как раз​ появиться ее стоимость​ осталось только выбрать​ так как формула​ и удалении листов,​ с 5 по​ Windows RT с​.​ текстовых), содержащихся в​ заполнено определенными числовыми​A​ПОИСКПОЗ​ ниже, формула отлично​ столбцами​

• ​Функция​3-м​П4, П5, П6​ со множеством разных​ из таблицы. Важный​ максимальное значение из​​ должна быть выполнена​​ на которые такие​​ 10​ архитектурой ARM. Подробнее​​Производится активация окошка аргументов​​ целевом столбце.​​ или текстовыми данными.​(Customer) на листе​​и добавим в​​ справляется с этой​A​INDEX​столбце, то есть​​ — там рассчитывается​​ чисел, как в​ нюанс в том,​ этого массива.​ в массиве. Если​ ссылки указывают. В​5:10​ об этих различиях.​ функции​Как видим, в отличие​ В Экселе существует​Main table​
• ​ неё ещё одну​ задачей:​и​(ИНДЕКС) использует​ из ячейки​ только Число3 по​​ следующем прикремленном примере…​​ что если пользователь​Далее немного изменив первую​​ все сделано правильно,​​ примерах используется формула​Все ячейки в столбце​Выделите ячейку.​
• ​СЧЁТЕСЛИ​ от предыдущего способа,​ целый ряд инструментов,​и сравниваем его​ функцию​=INDEX($A$2:$A$10,MATCH(«Russia»,$B$2:$B$10,0))​B​​3​​C2​ исходникам Число 1​​ я эти индекс​ вводит нестандартные значения​​ формулу с помощью​ в строке формул​ =СУММ(Лист2:Лист6!A2:A5) для суммирования​ H​Введите знак равенства «=».​. Как видим, окно​ данный вариант предлагает​ которые способны решить​ со всеми именами​ПОИСКПОЗ​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​

​, то значение аргумента​для аргумента​.​ и Число2 в​

​ смещ впр гпр​

​ размеров, то они​ функций ИНДЕКС и​

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

​ появятся фигурные скобки.​ значений в ячейках​H:H​Примечание:​

​ имеет два поля,​ выводить результат в​ указанную проблему. Рассмотрим​ покупателей в таблице​, которая будет возвращать​Теперь у Вас не​ придется изменить с​row_num​Очень просто, правда? Однако,​ Периодах 1, 2​ поиспоз уже замучил​ должны автоматически округлиться​ ПОИСКПОЗ, мы создали​В ячейку F1 введите​ с A2 по​Все ячейки в столбцах​ Формулы в Excel начинаются​ которые соответствуют аргументам​ конкретный элемент листа​ каждый из них​ на листе​ номер столбца.​ должно возникать проблем​

​2​(номер_строки), который указывает​ на практике Вы​

​ и 3 в​

​ (или они меня),​

​ до ближайших имеющихся​ вторую для вывода​​ вторую формулу:​​ A5 на листах​​ с H по​​ со знака равенства.​ функции.​ с возможным его​ в отдельности.​​Lookup table​=INDEX(Ваша таблица,(MATCH(значение для вертикального​​ с пониманием, как​на​ из какой строки​

• ​ далеко не всегда​​ вариациях по этим​ но так и​ в таблице и​ названия строк таблицы​Снова Для подтверждения нажмите​ со второго по​ J​Выберите ячейку или введите​В поле​ сохранением там. Но,​Скачать последнюю версию​(A2:A13).​ поиска,столбец, в котором​
• ​ работает эта формула:​​3​ нужно возвратить значение.​ знаете, какие строка​ периодам, отсюда и​ не нашел нужного​ в серой ячейке​ по зачиню ячейки.​ комбинацию клавиш CTRL+SHIFT+Enter.​ шестой.​H:J​ ее адрес в​
• ​«Диапазон»​​ к сожалению, функция​ Excel​Если совпадение найдено, уравнение​ искать,0)),(MATCH(значение для горизонтального​Во-первых, задействуем функцию​, иначе формула возвратит​ Т.е. получается простая​ и столбец Вам​ название: П4 Число3​ решения.​ должна появиться стоимость​ Название соответствующих строк​

​Найдено в каком месяце​Вставка или копирование.​Диапазон ячеек: столбцы А-E,​ выделенной.​тем же способом,​СЧЁТЗ​Читайте также: Как посчитать​ возвращает​ поиска,строка в которой​MATCH​ результат из только​ формула:​ нужны, и поэтому​ — это результат​​iam_alex​ изготовления двери для​

​ (товаров) выводим в​ и какая была​    Если вставить листы между​ строки 10-20​Введите оператор. Например, для​

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

​ который мы уже​все-таки не позволяет​ количество строк в​1​ искать,0))​(ПОИСКПОЗ), которая находит​ что вставленного столбца.​=INDEX($D$2:$D$10,3)​ требуется помощь функции​ по Числам 1​: =ПОИСКПОЗ(ИНДЕКС(C16:F16;0;B15);D5:D9;0) — в​ этих округленных стандарных​ F2.​​ наибольшая продажа Товара​​ листами 2 и​​A10:E20​​ вычитания введите знак​

​ не раз описывали​

1. ​ задавать условия отбора​ Excel​(ИСТИНА), а если​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​ положение «Russia» в​Используя​​=ИНДЕКС($D$2:$D$10;3)​
2. ​ПОИСКПОЗ​ и 2 в​ ячейке b15 номер​ размеров.​ВНИМАНИЕ! При использовании скелета​ 4 на протяжении​​ 6, Microsoft Excel​​Создание ссылки на ячейку​​ «минус».​
3. ​ выше, вводим координаты​ значений.​Как посчитать количество​​ нет –​

planetaexcel.ru

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

​ поиска,столбец, в котором​​ списке:​ПОИСКПОЗ​Формула говорит примерно следующее:​.​ Период2/Период1, П5 Число3​ столюца таблицы​Решение для серой ячейки​ формулы для других​ двух кварталов.​ прибавит к сумме​ или диапазон ячеек​Выберите следующую ячейку или​ первого столбца таблицы.​Урок: Мастер функций в​ заполненных ячеек в​0​ искать,0)),(MATCH(значение для горизонтального​=MATCH(«Russia»,$B$2:$B$10,0))​/​ ищи в ячейках​Функция​ — это результат​этим я могу​ будет практически полностью​ задач всегда обращайте​В первом аргументе функции​ содержимое ячеек с​ с другого листа​ введите ее адрес​В поле​ Excel​ Экселе​(ЛОЖЬ).​ поиска,строка в которой​

​=ПОИСКПОЗ(«Russia»;$B$2:$B$10;0))​​ИНДЕКС​

​ от​​MATCH​ по Период3/Период1, П6​ узнать на какой​ аналогично предыдущему примеру:​ внимание на второй​ ВПР (Вертикальный ПРосмотр)​

​ A2 по A5​​ в той же​ в выделенной.​«Критерий»​С помощью оператора​В зависимости от целей​Далее, мы делаем то​ искать,0))​Далее, задаём диапазон для​, Вы можете удалять​D2​(ПОИСКПОЗ) в Excel​ Число3 — это​ строке нужная мне​=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))​ и третий аргумент​ указывается ссылка на​ на новых листах.​ книге​Нажмите клавишу ВВОД. В​

​нам нужно задать​​СЧЁТ​ пользователя, в Экселе​ же самое для​
​Обратите внимание, что для​ функции​ или добавлять столбцы​до​ ищет указанное значение​ результат по Период3/Период1.​ цифра, но не​=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))​ поисковой функции ГПР.​ ячейку где находится​Удаление​

​В приведенном ниже примере​​ ячейке с формулой​

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

​ значений столбца​​ двумерного поиска нужно​INDEX​ к исследуемому диапазону,​

​D10​​ в диапазоне ячеек​И я не​ могу заставить узнать​Разница только в последнем​ Количество охваченных строк​ критерий поиска. Во​   .  Если удалить листы​

​ функция СРЗНАЧ вычисляет​​ отобразится результат вычисления.​

​ туда слово​​ только числовых значений​ всех значений в​B​ указать всю таблицу​(ИНДЕКС), из которого​ не искажая результат,​и извлеки значение​

​ и возвращает относительную​​ ленюсь))){/post}{/quote}​
​ в нужном столбце​
​ аргументе обеих функций​ в диапазоне указанного​ втором аргументе указывается​ между листами 2​

​ среднее значение в​​При вводе в ячейку​«Мясо»​

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

​ из третьей строки,​​ позицию этого значения​В Периоде3 одно​ (Период) — это​ПОИСКПОЗ (MATCH)​ в аргументе, должно​ диапазон ячеек для​ и 6, Microsoft​ диапазоне B1:B10 на​ формула также отображается​.​ Он игнорирует текстовые​ данных и тех,​Затем перемножаем полученные результаты​array​ В нашем случае​ непосредственно столбец, содержащий​ то есть из​ в диапазоне.​ Число3. Остальные -​ главный вопрос, остальное​-​

​ совпадать с количеством​​ просмотра в процессе​ Excel не будет​
​ листе «Маркетинг» в​ в​После того, как вышеуказанные​ значения и не​ которые соответствуют определенному​ (1 и 0).​(массив) функции​ это​
​ нужное значение. Действительно,​ ячейки​Например, если в диапазоне​ это П4, П5,​
​ можно как мне​Типу сопоставления​ строк в таблице.​ поиска. В третьем​ использовать их значения​ той же книге.​строке формул​

​ настройки выполнены, жмем​​ включает их в​ заданному условию. Давайте​ Только если совпадения​INDEX​A2:A10​
​ это большое преимущество,​D4​B1:B3​ П6​ мидится прикрутить с​(здесь он равен​ А также нумерация​ аргументе функции ВПР​ в вычислениях.​1. Ссылка на лист​.​ на кнопку​ общий итог. Данная​ рассмотрим, как решить​ найдены в обоих​(ИНДЕКС).​.​ особенно когда работать​
​, так как счёт​содержатся значения New-York,​

​vikttur​​ помощью других функций.​ минус 1). Это​ должна начинаться со​ должен указываться номер​Перемещение​ «Маркетинг».​Чтобы просмотреть формулу, выделите​«OK»​ функция также относится​ поставленные задачи различными​ столбцах (т.е. оба​
​А теперь давайте испытаем​
​Затем соединяем обе части​ приходится с большими​ начинается со второй​ Paris, London, тогда​: Так в чем​Guest​

​ некий аналог четвертого​​ второй строки!​ столбца, из которого​   . Если листы, находящиеся между​2. Ссылка на диапазон​ ячейку, и она​
​.​ к категории статистических​ способами.​ критерия истинны), Вы​ этот шаблон на​ и получаем формулу:​ объёмами данных. Вы​

​ строки.​​ следующая формула возвратит​ проблема?​: Тогда так.​ аргумента функции​
​Скачать пример поиска значения​ следует взять значение​ листом 2 и​

​ просмотра (Range Lookup)​​ строке Excel​ с именем Товар​
​ таким образом, чтобы​3. Восклицательный знак (!)​

planetaexcel.ru

​Выделите пустую ячейку.​

Имеем таблицу, в которой записаны объемы продаж определенных товаров в разных месяцах. Необходимо в таблице найти данные, а критерием поиска будут заголовки строк и столбцов. Но поиск должен быть выполнен отдельно по диапазону строки или столбца. То есть будет использоваться только один из критериев. Поэтому здесь нельзя применить функцию ИНДЕКС, а нужна специальная формула.

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

Для решения данной задачи проиллюстрируем пример на схематической таблице, которая соответствует выше описанным условиям.

Лист с таблицей для поиска значений по вертикали и горизонтали:

Над самой таблицей расположена строка с результатами. В ячейку B1 водим критерий для поискового запроса, то есть заголовок столбца или название строки. А в ячейке D1 формула поиска должна возвращать результат вычисления соответствующего значения. После чего в ячейке F1 сработает вторая формула, которая уже будет использовать значения ячеек B1 и D1 в качестве критериев для поиска соответствующего месяца.

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

Теперь узнаем, в каком максимальном объеме и в каком месяце была максимальная продажа Товара 4.

Чтобы выполнить поиск по столбцам следует:

1. В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
2. В ячейку D1 введите следующую формулу:
3. Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.



4. В ячейку F1 введите вторую формулу:
5. Снова Для подтверждения нажмите комбинацию клавиш CTRL+SHIFT+Enter.

Найдено в каком месяце и какая была наибольшая продажа Товара 4 на протяжении двух кварталов.

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

В первом аргументе функции ВПР (Вертикальный ПРосмотр) указывается ссылка на ячейку где находится критерий поиска. Во втором аргументе указывается диапазон ячеек для просмотра в процессе поиска. В третьем аргументе функции ВПР должен указываться номер столбца, из которого следует взять значение на против строки с именем Товар 4. Но так как нам заранее не известен этот номер мы с помощью функции СТОЛБЕЦ создаем массив номеров столбцов для диапазона B4:G15.

Это позволяет функции ВПР собрать целый массив значений. В результате в памяти хранится все соответствующие значения каждому столбцу по строке Товар 4 (а именно: 360; 958; 201; 605; 462; 832). После чего функции МАКС остается только взять из этого массива максимальное число и возвратить в качестве значения для ячейки D1, как результат вычисления формулы.

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

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

Например, как эффектно мы отобразили месяц, в котором была максимальная продажа, с помощью второй формулы. Не сложно заметить что во второй формуле мы использовали скелет первой формулы без функции МАКС. Главная структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0). Мы заменили функцию МАКС на ПОИСКПОЗ, которая в первом аргументе использует значение, полученное предыдущей формулой. Оно теперь выступает в качестве критерия для поиска месяца. И в результате функция ПОИСКПОЗ нам возвращает номер столбца 2 где находится максимальное значение объема продаж для товара 4. После чего в работу включается функция ИНДЕКС, которая возвращает значение по номеру сроки и столбца из определенного в ее аргументах диапазона. Так как у нас есть номер столбца 2, а номер строки в диапазоне где хранятся названия месяцев в любые случаи будет 1. Тогда нам осталось функцией ИНДЕКС получить соответственное значение из диапазона B4:G4 – Февраль (второй месяц).



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

Вторым вариантом задачи будет поиск по таблице с использованием названия месяца в качестве критерия. В такие случаи мы должны изменить скелет нашей формулы: функцию ВПР заменить ГПР, а функция СТОЛБЕЦ заменяется на СТРОКА.

Это позволит нам узнать какой объем и какого товара была максимальная продажа в определенный месяц.

Чтобы найти какой товар обладал максимальным объемом продаж в определенном месяце следует:

1. В ячейку B2 введите название месяца Июнь – это значение будет использовано в качестве поискового критерия.
2. В ячейку D2 введите формулу:
3. Для подтверждения после ввода формулы нажмите комбинацию клавиш CTRL+SHIFT+Enter, так как формула будет выполнена в массиве. А в строке формул появятся фигурные скобки.



4. В ячейку F1 введите вторую формулу:
5. Снова Для подтверждения нажмите CTRL+SHIFT+Enter.

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

В первом аргументе функции ГПР (Горизонтальный ПРосмотр) указываем ссылку на ячейку с критерием для поиска. Во втором аргументе указана ссылка на просматриваемый диапазон таблицы. Третий аргумент генерирует функция СТРОКА, которая создает в памяти массив номеров строк из 10 элементов. Так как в табличной части у нас находится 10 строк.

Далее функция ГПР поочередно используя каждый номер строки создает массив соответственных значений продаж из таблицы по определенному месяцу (Июню). Далее функции МАКС осталось только выбрать максимальное значение из этого массива.

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

ВНИМАНИЕ! При использовании скелета формулы для других задач всегда обращайте внимание на второй и третий аргумент поисковой функции ГПР. Количество охваченных строк в диапазоне указанного в аргументе, должно совпадать с количеством строк в таблице. А также нумерация должна начинаться со второй строки!

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

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

По сути содержимое диапазона нас вообще не интересует, нам нужен просто счетчик строк. То есть изменить аргументы на: СТРОКА(B2:B11) или СТРОКА(С2:С11) – это никак не повлияет на качество формулы. Главное, что в этих диапазонах по 10 строк, как и в таблице. И нумерация начинается со второй строки!