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

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

Пусть в диапазоне А4:В15 имеется таблица с перечнем сотрудников и их зарплат (фамилии сотрудников не повторяются).

Требуется, введя в ячейку D4 фамилию сотрудника, вывести в другой ячейке его зарплату. Решение приведено в файле примера .

Алгоритм решения задачи следующий:

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

Решение практически аналогично поиску числового значения из статьи Поиск позиции ЧИСЛА с выводом соответствующего значения из соседнего столбца. Для этого типа задач в EXCEL существует специальная функция ВПР() , но для ее решения можно использовать и другие функции (про функцию ВПР() см. эту статью).

Решение

Если несколько значений удовл. критерию

берется первое сверху

= ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2))

берется последнее сверху

если столбец отсортирован по возрастанию, то берется последнее сверху, если нет, то результат непредсказуем

соответствующие значения суммируются

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

Задача подразумевает, что диапазон поиска содержит неповторяющиеся значения. В самом деле, если критерию удовлетворяет сразу несколько значений, то из какой строки выводить соответствующее ему значение из соседнего столбца? Если все же диапазон поиска содержит повторяющиеся значения, то второй столбец из таблицы выше поясняет какое значение будет выведено (обычно возвращается первое значение, удовлетворяющее критерию).

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

Совет: Если в диапазон поиска постоянно вводятся новые значения, то для исключения ввода дубликатов следует наложить определенные ограничения (см. статью Ввод неповторяющихся значений). Для визуальной проверки наличия дубликатов можно использовать Условное форматирование (см. статью Выделение повторяющихся значений).

Для организации динамической сортировки пополняемого диапазона поиска можно использовать идеи из статьи Сортированный список.

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

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

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

В этой статье

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

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций индекс и ПОИСКПОЗ.

Примеры использования функции ВПР

Дополнительные сведения можно найти в разделе функция ВПР.

Примеры ИНДЕКСов и СОВПАДЕНИй

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))

Формула ищет первое значение в ячейке C2: C10, соответствующее капусты (в B7), и возвращает значение в C7 (100) — первое значение, соответствующее капусты.

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

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

Для этого используйте функцию ВПР.

Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.

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

Дополнительные сведения можно найти в разделе функция ВПР.

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

Для выполнения этой задачи используйте функции СМЕЩ и ПОИСКПОЗ.

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

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

Match («апельсины»; C2: C7; 0) ищет оранжевый цвет в диапазоне C2: C7. Не следует включать начальную ячейку в диапазон.

1 — количество столбцов справа от начальной ячейки, для которых должно быть возвращено возвращаемое значение. В нашем примере возвращаемое значение находится в столбце D, Sales.

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

Для выполнения этой задачи используется функция ГПР. Ниже приведен пример.

Функция ГПР выполняет поиск по столбцу Sales и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Для выполнения этой задачи используется функция ГПР.

Важно: Убедитесь, что значения в первой строке отсортированы в возрастающем порядке.

В приведенном выше примере функция ГПР ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение, которое меньше 1100 и возвращает число 10543.

Дополнительные сведения можно найти в разделе функции ГПР.

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

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

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

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

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

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

Читать еще: Как в excel скрыть часть таблицы

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

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

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

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

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

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

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

В ячейку B1 введите значение Товара 4 – название строки, которое выступит в качестве критерия.
В ячейку D1 введите следующую формулу:
Для подтверждения после ввода формулы нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно, в строке формул появятся фигурные скобки.
В ячейку F1 введите вторую формулу:
Снова Для подтверждения нажмите комбинацию клавиш 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

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

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

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

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

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

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

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

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

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

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

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

Общая информация о ВПР (VLOOKUP)

Уровень сложности по шкале BRP ADVICE — 3 из 7 .

Чтобы разобраться с ВПР (VLOOKUP), сначала надо разобраться с тем, как работают все функции ссылок и массивов.

В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах вы можете ссылаться на ячейку, чтобы получить ее значение или использовать в расчетах. Обычно, в функциях вы указываете ссылки на ячейки вида A1, B17, G34, Z52. Некоторые привыкли работать со ссылками вида R1C1, R17C2, R34C7, R52C26. При этом вы указываете номер строки и букву/номер столбца с начала листа. Указав ячейку, вы даете программе точное указание, что вам нужно значение именно этой ячейки, находящейся на пересечении нужной строки и столбца. То есть выглядит все это примерно так:

Функции ссылок и массивов работают немного по-другому. В функциях ссылок и массивов вы задаете новую таблицу на листе, которая может находиться, где угодно. Назовем эту новую таблицу — внутренняя таблица. Вся нумерация строк и столбцов в этой внутренней таблице начинается заново. От 1 и до последней строки, от 1 и до последнего столбца. При этом совершенно не важно, где начинается внутренняя таблица: в самом начале листа, в его середине или ближе к концу. Всегда первая ячейка этой таблицы образует пересечение первой строки и первого столбца. Вот как это можно схематически изобразить:

Читать еще: Как в эксель составить таблицу

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

Что же происходит в этой внутренней таблице? Каждая ячейка получает новый адрес, который состоит из номера строки и номера столбца. Именно так: сначала номер строки, затем номер столбца.

Самый простой способ разобраться с ВПР (VLOOKUP) — это рассмотреть его на примерах. Рассмотрим один пример с точным поиском, второй пример — с приблизительным поиском.

Ссылка на файл-пример приведена в конце описания этого примера.

Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?

После этого программа вернет вам ответ 21 000.

1. Искомое значение. В нашем примере это «Иванов С.А.» — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.

2. Таблица. В нашем примере это C3:D9. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

4. Интервальный просмотр. В нашем примере это ЛОЖЬ (FALSE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». То есть ИСТИНА (TRUE) означает, что нам подойдет и Иванов С.А., и Иванов С.И., и Иванова О.П., и может быть кто-то еще (кого из них выберет Excel, смотрите в Примере № 2). Сейчас нам нужен именно Иванов С.А., поэтому мы поставили ЛОЖЬ (FALSE).

Что именно делает ВПР (VLOOKUP) в этом примере?

А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим.

Пример 2 — приблизительный поиск

Допустим, у вас в компании менеджер по продажам получает премию в зависимости от объема продаж. Если продажи за месяц больше 100, то премия составляет 4% от продаж. Если продажи больше 200, то премия — 5%. Больше 300 — 6%. Больше 400 — 7%. И при любых продажах больше 500 — 8%. При продажах меньше 100, менеджер премию не получает. При помощи ВПР (VLOOKUP) можно быстро узнать, какую премию получит менеджер при его фактических продажах.

Основным отличием от предыдущего примера является то, что фактические продажи Иванова могут быть не только ровно 100, 200, 300, 400 или 500. Они могут находиться между указанными нами значениями. Например, фактические продажи Иванова составят 350.

После этого программа вернет вам ответ 6%. Все что вам остается сделать, это перемножить продажи 350 и ставку премии 6%. Это и будет премия Иванова С.А. при продажах равных 350.

1. Искомое значение. В нашем примере это 350 — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.

2. Таблица. В нашем примере это C3:D8. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

4. Интервальный просмотр. На этот раз нам нужна ИСТИНА (TRUE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». Мы используем ИСТИНА (TRUE), потому что нам нужно найти не только значения 100, 200 и другие, прямо указанные в таблице, но и все, что находится между ними. То есть ИСТИНА (TRUE) означает, что нам подойдет и 300, и 350, и 380 и так далее, и может быть что-то еще (какое из них выберет Excel, смотрите ниже).

Что именно делает ВПР (VLOOKUP) в этом примере?

100, указанное в таблице, меньше 350? Да, — отвечает ВПР (VLOOKUP), — тогда запоминаем строку 1 и смотрим следующее значение.

300 меньше 350? Да, тогда забываем строку 2, запоминаем 3 и смотрим следующее.

И, наконец, ВПР (VLOOKUP) возвращает значение, указанное на пересечении 3 строки (то, что он нашел) и 2 столбца (то, что мы указали в функции).

1. Это ошибки, связанные с неверной работой с 4 аргументом, с интервальным просмотром. Часто путаются в разнице между ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Запомните такой вопрос: «Мы ищем приблизительно такое же значение, верно?», пусть это будет вам подсказкой.

Первый случай, когда вы работаете с ВПР (VLOOKUP) как будто в нем всего 3 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

В этом случае Excel решает, что надо использовать значение 4 аргумента по умолчанию и подставляет в качестве него ИСТИНА (TRUE). Тогда вы можете найти не Иванова С.А., а кого-то чья фамилия будет примерно похожа на его (смотри как работает ВПР (VLOOKUP) с приблизительным поиском).

Наш совет, чтобы не допускать таких ошибок, всегда указывайте последний, четвертый, аргумент функции ВПР (VLOOKUP). И пишите его словом, не заменяйте его на цифры, так вашу формулу будет легче прочитать и понять, что же она делает.

3. Иногда неправильно указывают номер столбца (третий аргумент ВПР (VLOOKUP)). Номер столбца не может быть меньше 1 и не может быть больше, чем столбцов во внутренней таблице. Если номер столбца указан неверно, то ВПР (VLOOKUP) возвращает ошибку #ССЫЛКА! (#REF!). Когда вы видите такую ошибку, пересчитайте количество столбцов в таблице, которую вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР (VLOOKUP).

В случаях, когда Excel , Google Sheets, LibreOffice, OpenOffice не может найти точное совпадение при 4 аргументе ЛОЖЬ (FALSE), ВПР (VLOOKUP) возвращает ошибку #Н/Д ( #N/A). Такую же ошибку ВПР (VLOOKUP) вернет, если вы используете приблизительный поиск и ваша таблица начинается со значения, которое больше искомого.

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

В-третьих, если вы все сделали правильно, но во внутренней таблице нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так:

или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:

В этом случае Excel , Google Sheets, LibreOffice, OpenOffice вместо #Н/Д (#N/A) будут писать, что такого сотрудника нет. Это поможет сделать ваши расчеты более информативными и надежными.

Дополнить ВПР (VLOOKUP) можно функцией ЕСЛИОШИБКА (IFERROR) и функцией ПОИСКПОЗ (MATCH). В особо сложных случаях возможно использование в комбинации с функцией СМЕЩ (OFFSET).

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

Читать еще: Как в таблице excel найти нужное наименование

Пример 1 — применение ВПР с точным поиском

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.

Желаем вам успешной работы!

и команда Учебного центра BRP ADVICE.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно».
В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК.
В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

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

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

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

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

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

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

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

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

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