Как найти значение в ячейках электронной таблице

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

Описание

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

​Смотрите также​ с макросом)))​​. Вообще говоря, возможных​​Читайте также: Поиск значения​​ 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

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

Параграф 3.2 Организация вычислений в электронных таблицах

---

---

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

Перейти

---

2. Назовите основные типы ссылок.

Два основных типа ссылок:

1) относительные – зависящие от положения формулы
2) абсолютные – не зависящие от положения формулы

---

3. Охарактеризуйте относительный тип ссылок.

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

---

4. По данным электронной таблицы определите значение в ячейке С1.

---

5. Дан фрагмент электронной таблицы. Определите значения в ячейках С2 и СЗ после копирования в них формулы из ячейки С1.

---

6. Дан фрагмент электронной таблицы. Определите значения в ячейках диапазона D1:D3 после копирования в них формулы из ячейки СЗ.

---

7. Охарактеризуйте абсолютный тип ссылок.

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

---

8. Дан фрагмент электронной таблицы. Определите значения в ячейках С2 и СЗ после копирования в них формулы из ячейки С1.

---

9. Охарактеризуйте смешанный тип ссылок.

Смешанная ссылка содержит либо абсолютно адресуемый столбей и относительно адресуемую строку ($A1), либо относительно адресуемый столбей и абсолютно адресуемую строку (A$1). Абсолютная часть адреса не меняется, а относительная изменяется.

---

10. Дан фрагмент электронной таблицы. Определите значения в ячейках диапазона C1:D3 после копирования в них формулы из ячейки С1.

Получившиеся формулы в ячейках:
C2 = $A2 + B$1
C3 = $A3 + B$1
D1 = $A1 + C$1
D2 = $A2 + C$1
D3 = $A3 + C$1

---

11. Как можно изменить тип ссылки?

---

12. Дайте развернутый комментарий про знак доллара, который “замораживает” весь адрес или ее часть.

Это означает, что при изменении позиции ячейки, когда ссылка абсолютная (1), которая содержит формулу, абсолютная ссылка не изменится. А смешанная ссылка содержит только абсолютно адресуемый столбец ($A1) или абсолютно адресуемую строку (A$1), то есть при изменении позиции ячейки с формулой, ее абсолютная часть адреса не меняется.

---

13. Для чего нужны встроенные функции?

Для упрощения формул и для более понятного процесса вычислений.

---

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

Категории встроенных функций в Excel 2019:
– Финансовые;
– Дата и время;
– Математические;
– Статистические;
– Ссылки и массивы;
– Работа с базой данных;
– Текстовые;
– Логические;
– Проверка свойств и значений;
– Инженерные;
– Аналитические;
– Совместимость;
– Интернет.

---

15. Дан фрагмент электронной таблицы. Определите значение в ячейке D3.

Ответ: 10

---

16. Какая из формул не содержит ошибок?

а) =ЕСЛИ ((C4>4) И (C5>4));”Принят!”;”Не принят”) – Неверно
Исправленный вариант: =ЕСЛИ(И(C4>4;C5>4);”Принят!”;”Не принят”)

б) =ЕСЛИ (И(D2=0;B2/4);D3-A1;D3+A1) – Верно
B2/4 – Когда B2 равен нулю, то ЛОЖЬ, иначе ИСТИНА

в) =ЕСЛИ ((A4=0 И D1<0);1;0) – Неверно
Исправленный вариант: =ЕСЛИ(И(A4=0;D1<0);1;0)

г) =ЕСЛИ (ИЛИ(A2>10;C2>10);1;”ура!”) – Верно

---

17.

В ячейке А5 электронной таблицы находится суммарная стоимость товаров, заказанных Иваном А. в интернет-магазине. Формула, позволяющая подсчитать полную стоимость заказа, с учётом стоимости его доставки, имеет вид:
=ЕСЛИ(A5>=2000;A5;A5+150).
По данной формуле постройте блок-схему. Определите, какие льготы предоставляются покупателю в случае, если суммарная стоимость заказанных им товаров превышает 2000.

При суммарной стоимости заказанных товаров превышает или равен 2000, то стоимость доставки бесплатная.

---

18.

Оплата за аренду конференц-зала вычисляется по следующим правилам: каждый из первых четырёх часов аренды стоит 1000 рублей, каждый последующий час — 750 рублей. В ячейке В8 электронной таблицы находится количество полных часов аренды зала. Какая из формул позволяет подсчитать полную стоимость аренды зала?
Ответ: =ЕСЛИ(B8<=4;B8*1000;4000 +(B8-4)*750)
По формуле:
Если количество часов равен или меньше 4 часам, то просто умножаем количество часов на 1000 рублей(B8*1000), так как нет скидок.
Если же больше 4, то за первые 4 часа мы платим 4000 рублей и прибавляем сумму с учетом скидки, которая вычисляет так: из количества часов вычитаем 4 часа и полученный результат умножаем на 750.

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

В статье предлагается несколько различных формул для выполнения поиска в двумерном массиве значений Excel.  Просмотрите эти варианты и выберите наиболее для вас подходящий.

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

• Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ
• Формула ВПР и ПОИСКПОЗ для поиска в диапазоне
• Функция ПРОСМОТРX для поиска в строках и столбцах
• Формула СУММПРОИЗВ для поиска по строке и столбцу
• Поиск в матрице с именованными диапазонами

Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ

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

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

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

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

• Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
• Значение_вертикальное — H1 (целевой товар)
• Диапазон_поиска_столбец – A2:A11 (заголовки строк: названия напитков)
• Значение_горизонтальное — H2 (целевой период)
• Диапазон_поиска_строка — B1:E1 (заголовки столбцов: временные периоды)

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

=ИНДЕКС(B2:E11; ПОИСКПОЗ(H1;A2:A11;0); ПОИСКПОЗ(H2;B1:E1;0))

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

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

ИНДЕКС( B2:E11; номер_строки ; номер_столбца )

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

Итак, чтобы получить номер строки, мы ищем нужный нам товар (H1) в заголовках строк (A2:A11):

ПОИСКПОЗ(H1;A2:A11;0)

Чтобы получить номер столбца, мы ищем нужную нам неделю (H2) в заголовках столбцов (B1:E1):

ПОИСКПОЗ(H2;B1:E1;0)

В обоих случаях мы ищем точное совпадение, присваивая третьему аргументу значение 0.

В этом примере первое ПОИСКПОЗ возвращает 2, потому что нужный товар (Sprite) находится в ячейке A3, которая является второй по счёту в диапазоне ​​A2:A11. Второй ПОИСКПОЗ возвращает 3, так как «Неделя 3» находится в ячейке D1, которая является третьей ячейкой в ​​B1:E1.

С учетом вышеизложенного формула сводится к:

ИНДЕКС(B2:E11; 2 ; 3 )

Она возвращает число на пересечении второй строки и третьего столбца в матрице B2:E4, то есть в ячейке D3.

Думаю, вы понимаете, что аналогичным образом можно производить поиск в двумерном массиве Excel не только числа, но и текста. Тип данных здесь не имеет значения.

Формула ВПР и ПОИСКПОЗ для поиска в диапазоне

Другой способ выполнить матричный поиск в Excel — использовать комбинацию функций ВПР и ПОИСКПОЗ:

ВПР( значение_вертикальное ; массив_данных ; ПОИСКПОЗ( значение_горизонтальное , диапазон_поиска_строка , 0), ЛОЖЬ)

Для нашего образца таблицы формула принимает следующий вид:

=ВПР(H1; A2:E11; ПОИСКПОЗ(H2;A1:E1;0); ЛОЖЬ)

Где:

• Массив_данных — B2:E11 (ячейки данных, не включая заголовки строк и столбцов)
• Значение_вертикальное — H1 (целевой товар)
• Значение_горизонтальное — H2 (целевой период)
• Диапазон_поиска_строка — А1:E1 (заголовки столбцов: временные периоды)

Основой формулы является функция ВПР, настроенная на точное совпадение (последний аргумент имеет значение ЛОЖЬ). Она ищет заданное значение (H1) в первом столбце массива (A2:E11) и возвращает данные из другого столбца в той же строке. Чтобы определить, из какого столбца вернуть значение, вы используете функцию ПОИСКПОЗ, которая также настроена на точное совпадение (последний аргумент равен 0):

ПОИСКПОЗ(H2;A1:E1;0)

ПОИСКПОЗ ищет текст из H2 в заголовках столбцов (A1:E1) и указывает относительное положение найденной ячейки. В нашем случае нужная неделя (3-я) находится в D1, которая является четвертой по счету в  массиве поиска. Итак, число 4 идет в аргумент номер_столбца функции ВПР:

=ВПР(H1; A2:E11; 4; ЛОЖЬ)

Далее ВПР находит точное совпадение H1 со значением в A3 и возвращает значение из 4-го столбца в той же строке, то есть из ячейки D3.

Важное замечание! Чтобы формула работала корректно, диапазон_поиска (A2:E11) функции ВПР и диапазон_поиска (A1:E1) функции ПОИСКПОЗ должны иметь одинаковое количество столбцов. Иначе число, переданное в номер_столбца, будет неправильным (не будет соответствовать положению столбца в массиве данных).

Функция ПРОСМОТРX для поиска в строках и столбцах

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

ПРОСМОТРX( значение_вертикальное ; диапазон_поиска_столбец ; ПРОСМОТРX( значение_горизонтальное ; диапазон_поиска_строка ; массив_данных ))

Для нашего примера набора данных формула выглядит следующим образом:

=ПРОСМОТРX(H1; A2:A11; ПРОСМОТРX(H2; B1:E1; B2:E11))

Примечание. В настоящее время ПРОСМОТРX — это функция, доступная только подписчикам Office 365 и более поздних версий.

В формуле используется функция ПРОСМОТРX для возврата всей строки или столбца. Внутренняя функция ищет целевой период времени в строке заголовка и возвращает все значения для этой недели (в данном примере для 3-й). Эти значения переходят в аргумент возвращаемый_массив внешнего ПРОСМОТРX:

=ПРОСМОТРX(H1; A2:A11; {544:87:488:102:87:433:126:132:111:565})

Внешняя функция ПРОСМОТРX ищет нужный товар в заголовках столбцов и извлекает значение из той же позиции из возвращаемого_массива.

Формула СУММПРОИЗВ для поиска по строке и столбцу

Функция СУММПРОИЗВ чрезвычайно универсальна — она может делать множество вещей, выходящих за рамки ее предназначения, особенно когда речь идет об оценке нескольких условий.

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

СУММПРОИЗВ ( диапазон_поиска_столбец = значение_вертикальное ) * ( диапазон_поиска_строка = значение_горизонтальное), массив_данных )

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

=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2); B2:E11)

Приведенный ниже вариант также будет работать:

=СУММПРОИЗВ((A2:A11=H1)*(B1:E1=H2)*B2:E11)

Теперь поясним подробнее. В начале мы сравниваем два значения поиска с заголовками строк и столбцов (целевой товар в H1 со всеми наименованиями в A2: A11 и целевой период времени в H2 со всеми неделями в B1: E1):

(A2:A11=H1)*(B1:E1=H2)

Это дает нам два массива значений ИСТИНА и ЛОЖЬ, где ИСТИНА означает совпадения:

{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}) * ({ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}

Операция умножения преобразует значения ИСТИНА и ЛОЖЬ в 1 и 0 и создает матрицу из 4 столбцов и 10 строк (строки разделяются двоеточием, а каждый столбец данных — точкой с запятой):

{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0}

Функция СУММПРОИЗВ умножает элементы приведенного выше массива на элементы B2:E4, находящихся в тех же позициях:

{0;0;0;0:0;0;1;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0; 0;0:0;0;0;0:0;0;0;0:0;0;0;0} * {455;345;544;366:65;77;87;56:766; 655;488;865:129;66;102;56:89;141;87;89:566;511;433;522:154; 144;126; 162:158;165;132;155:112;143;111; 125:677;466;565;766})

И поскольку умножение на ноль дает в результате ноль, остается только элемент, соответствующий 1 в первом массиве:

=СУММПРОИЗВ({0;0;0;0:0;0;87;0:0;0;0;0:0;0;0;0:0;0;0;0:0; 0;0;0:0;0;0;0:0;0;0;0:0;0;0;0:0;0;0;0})

Наконец, СУММПРОИЗВ складывает все элементы результирующего массива и возвращает значение 87.

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

Поиск в матрице с именованными диапазонами

Еще один достаточно простой способ поиска в массиве в Excel — использование именованных диапазонов. Рассмотрим пошагово:

Шаг 1. Назовите столбцы и строки

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

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

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

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

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

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

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

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

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

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

=Sprite неделя_3

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

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

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

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

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

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

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

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

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

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

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

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

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

На чтение 3 мин. Просмотров 124 Опубликовано 21.05.2021

Как правило, вы получаете значение ячейки, когда используете формулу для поиска значения в Excel. Но здесь я представлю несколько формул для поиска значения и возврата относительного адреса ячейки.

Найдите значение и верните адрес ячейки с формулой

---

Содержание

1. Найти значение и вернуть адрес ячейки с формулой
2. Пакетное преобразование ссылки формулы в абсолютное, относительное, абсолютное значение столбца или абсолютное значение строки
3. Относительные статьи

Найти значение и вернуть адрес ячейки с формулой

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

Формула 1 Чтобы вернуть ячейку абсолютная ссылка

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

1. Выберите ячейку и введите в нее AA, здесь я ввожу AA в ячейку A26. См. Снимок экрана:

2. Затем введите эту формулу = ЯЧЕЙКА (“адрес”, ИНДЕКС ($ A $ 18: $ A $ 24, ПОИСКПОЗ (A26, $ A $ 18: $ A $ 24,1))) в ячейке рядом с ячейкой A26 (ячейка, которую вы ввели AA ), затем нажмите клавиши Shift + Ctrl + Enter , и вы получите относительную ссылку на ячейку. См. Снимок экрана:

Совет:

1. В приведенной выше формуле A18: A24 – это диапазон столбцов, в котором находится ваше значение поиска, A26 – значение поиска.

2. Эта формула может найти только первый относительный адрес ячейки, который соответствует значению поиска.

Формула 2 Чтобы вернуть номер строки значения ячейки в таблице

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

1. Введите BB в ячейку, здесь я ввожу BB в ячейку A10. См. Снимок экрана:

2. В ячейке, смежной с ячейкой A10 (ячейка, в которую вы ввели BB), введите эту формулу = МАЛЕНЬКИЙ (ЕСЛИ ($ A $ 10 = $ A $ 2: $ A $ 8, ROW ($ A $ 2: $ A $ 8) -ROW ($ A $ 2) +1), ROW (1: 1)) и нажмите клавиши Shift + Ctrl + Enter , затем перетащите дескриптор автозаполнения вниз, чтобы применить эту формулу, пока не появится # ЧИСЛО! . см. снимок экрана:

3. Затем вы можете удалить # ЧИСЛО !. См. Снимок экрана:

Советы:

1. В этой формуле A10 указывает значение поиска, а A2: A8 – это диапазон столбцов, в котором находится ваше значение поиска.

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

Пакетное преобразование ссылки формулы в абсолютное, относительное, абсолютное значение столбца или абсолютное значение строки

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

Kutools для Excel: с более чем 300 удобными надстройками Excel, попробуйте бесплатно, без ограничений в течение 30 дней.

---

Относительные статьи

• ВПР и возврат нескольких значений по горизонтали
• ВПР и возврат наименьшего значения
• ВПР и вернуть ноль вместо # Н/Д

---