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

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

ПОИСК ЗНАЧЕНИЯ В МАССИВЕ EXCEL

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

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

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

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

Интересно почитать: Как закрепить верхнюю строку в excel при прокрутке

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

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

1. В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон голубым цветом для читабельности поля ввода (дальше будем вводить в ячейку B1 остальные числа, чтоб экспериментировать с новенькими значениями).

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

3. Опосля ввода формулы для доказательства жмем комбинацию жарких кнопок CTRL+SHIFT+Enter, потому что формула обязана быть выполнена в массиве. Если все изготовлено верно в строке формул по бокам покажутся фигурные скобки < >.

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

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

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

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

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

КАК ПОЛУЧИТЬ ЗАГОЛОВОК СТОЛБЦА И НАЗВАНИЕ СТРОКИ ТАБЛИЦЫ

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

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

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

Для заголовка столбца. В ячейку D2 введите формулу:

Сейчас опосля ввода формулы для доказательства нажимаем как по традиции просто Enter:

Интересно почитать: Как посчитать строки в excel

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

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

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

ПОИСК ОДИНАКОВЫХ ЗНАЧЕНИЙ В ДИАПАЗОНЕ EXCEL

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Автоподбор высоты строки в excel не работает

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

ПОИСК БЛИЖАЙШЕГО ЗНАЧЕНИЯ В ДИАПАЗОНЕ EXCEL

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

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

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

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