Поиск значения в спектре таблицы Excel по столбцам и строчкам
Допустим ваш отчет содержит таблицу с огромным количеством данных на огромное количество столбцов. Проводить зрительный анализ таковых таблиц очень трудно. А одним из заданий по работе с отчетом является – анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На 1-ый взор это очень обычное задание, но его недозволено решить, используя одну обычную функцию. Да, естественно можно пользоваться инвентарем: «ГЛАВНАЯ»-«Редактирование»-«Отыскать» CTRL+F, чтоб вызвать окно поиска значений на листе Excel. Либо же сделать для таблицы правило условного форматирования. Но тогда недозволено будет выполнить последующих вычислений с приобретенными плодами. Потому нужно сделать и верно применить подобающую формулу.
ПОИСК ЗНАЧЕНИЯ В МАССИВЕ EXCEL
Схема решения задания смотрится приблизительно таковым образом:
- в ячейку B1 мы будем вводить интересующие нас данные;
- в ячейке B2 будет отображается заголовок столбца, который содержит значение ячейки B1
- в ячейке B3 будет отображается заглавие строчки, которая содержит значение ячейки B1.
Практически нужно выполнить поиск координат в Excel. Для что это необходимо? Довольно нередко нам необходимо получить координаты таблицы по значению. Мало припоминает оборотный анализ матрицы. Определенный пример в 2-ух словах смотрится приблизительно так. Поставленная цель в цифрах является начальным значением, необходимо найти кто и когда более приближен к данной нам цели. Для примера используем ординарную матрицу данных с отчетом по количеству проданных продуктов за три квартала, как показано ниже на рисунке. Принципиально, чтоб все числовые характеристики совпадали. Если нет желания вручную создавать и заполнять таблицу 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:
Для строчки вводим похожую, но все таки мало другую формулу:
В итоге получены внутренние координаты таблицы по значению – Март; Продукт 4:
На 1-ый взор все работает отлично, но что, если таблица будет содержат 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.