Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Функции поиска данных в таблицах Excel 2007
Поиск нужных данных в диапазоне
Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов. Проводить визуальный анализ таких таблиц крайне сложно. А одним из заданий по работе с отчетом является — анализ данных относительно заголовков строк и столбцов касающихся определенного месяца. На первый взгляд это весьма простое задание, но его нельзя решить, используя одну стандартную функцию. Да, конечно можно воспользоваться инструментом: Или же создать для таблицы правило условного форматирования. Но тогда нельзя будет выполнить дальнейших вычислений с полученными результатами. Поэтому необходимо создать и правильно применить соответствующую формулу. Фактически необходимо выполнить поиск координат в Excel. Для чего это нужно? Достаточно часто нам нужно получить координаты таблицы по значению. Немного напоминает обратный анализ матрицы. Конкретный пример в двух словах выглядит примерно так. Поставленная цель в цифрах является исходным значением, нужно определить кто и когда наиболее приближен к этой цели. Для примера используем простую матрицу данных с отчетом по количеству проданных товаров за три квартала, как показано ниже на рисунке. Важно, чтобы все числовые показатели совпадали. Если нет желания вручную создавать и заполнять таблицу Excel с чистого листа, то в конце статьи можно скачать уже с готовым примером. Последовательно рассмотрим варианты решения разной сложности, а в конце статьи — финальный результат. Сначала научимся получать заголовки столбцов таблицы по значению. Для этого выполните следующие действия:. В ячейку C2 формула вернула букву D - соответственный заголовок столбца листа. Как видно все сходиться, значение содержится в ячейке столбца D. Рекомендуем посмотреть на формулу для получения целого адреса текущей ячейки. Теперь получим номер строки для этого же значения Для этого в ячейку C3 введите следующую формулу:. Формула вернула номер 9 — нашла заголовок строки листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9. Теперь научимся получать по значению координаты не целого листа, а текущей таблицы. Одним словом, нам нужно найти по значению вместо D9 получить заголовки:. Чтобы решить данную задачу будем использовать формулу с уже полученными значениями в ячейках C2 и C3. Для этого делаем так:. На первый взгляд все работает хорошо, но что, если таблица будет содержат 2 одинаковых значения? Тогда могут возникнуть проблемы с ошибками! Рекомендуем так же посмотреть альтернативное решение для поиска столбцов и строк по значению. Чтобы проконтролировать наличие дубликатов среди значений таблицы создадим формулу, которая сможет информировать нас о наличии дубликатов и подсчитывать их количество. Для этого в ячейку E2 вводим формулу:. Как видно при наличии дубликатов формула для заголовков берет заголовок с первого дубликата по горизонтали с лева на право. А формула для получения названия номера строки берет номер с первого дубликата по вертикали сверху вниз. Для исправления данного решения есть 2 пути:. В данном случаи изменяем формулы либо одну либо другую, но не две сразу. Стоит напомнить о том, что в ячейке С3 должна оставаться старая формула:. Здесь правильно отображаются координаты первого дубликата по вертикали с верха в низ — I7 для листа и Август; Товар2 для таблицы. Оставим такой вариант для следующего завершающего примера. Данная таблица все еще не совершенна. Ведь при анализе нужно точно знать все ее значения. Если введенное число в ячейку B1 формула не находит в таблице, тогда возвращается ошибка — ЗНАЧ! Идеально было-бы чтобы формула при отсутствии в таблице исходного числа сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц в ячейку F1 введите новую формулу:. После чего следует во всех остальных формулах изменить ссылку вместо B1 должно быть F1! Так же нужно изменить ссылку в условном форматировании. И здесь в параметрах укажите F1 вместо B1. Чтобы проверить работу программы, введите в ячейку B1 число которого нет в таблице, например: Это приведет к завершающему результату:. Теперь можно вводить любое исходное значение, а программа сама подберет ближайшее число, которое содержит таблица. После чего выводит заголовок столбца и название строки для текущего значения. Например, если ввести число получаем новый результат:. Наша программа в Excel нашла наиболее близкое значение для исходного — Такая программа может пригодится для автоматического решения разных аналитических задач при бизнес-планировании, постановки целей, поиска рационального решения и т. А полученные строки и столбцы позволяют дальше расширять вычислительные возможности такого рода отчетов с помощью новых формул Excel. Скачать пример поиска значения в диапазоне Excel. Excel TABLE работа с таблицами. Поиск значения в диапазоне таблицы Excel по столбцам и строкам Допустим ваш отчет содержит таблицу с большим количеством данных на множество столбцов.
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива. Пусть Исходный список значений например, перечень инструментов находится в диапазоне A Выведем в отдельный диапазон значения, которые удовлетворяют критерию. Рассмотрим различные варианты поиска. Для удобства создадим именованный диапазон Список. Диапазон может охватить в том числе и незаполненные ячейки перечня. В дальнейшем пользователь может расширить перечень инструментов, указанные ниже формулы автоматически учтут новые значения. Выведем в отдельный диапазон все значения Исходного списка, в которых содержится текст-критерий например, слово дрель. Критерий вводится в ячейку С6. Для создания списка, содержащего найденные значения, воспользуемся формулой массива:. Алгоритм работы формулы следующий для просмотра промежуточных шагов работы формулы воспользуйтесь клавишей F В предельном случае м. Вышеуказанная формула массива будет возвращать несколько значений , поэтому перед вводом формулы нужно выделить сразу весь диапазон, то есть ячейки С Для скрытия ошибок ССЫЛКА! С19 применено правило Условного форматирования. В этом случае будут выведены все значения, которые совпадают с критерием без учета РЕгиСТра. Критерий вводится в ячейку E 6. Для создания списка, содержащего найденные значения, воспользуемся формулой массива: В этом случае будут выведены все значения, которые начинаются или совпадают с критерием. Критерий вводится в ячейку G 6. В этом случае будут выведены все значения, которые заканчиваются или совпадают с критерием. Критерий вводится в ячейку I 6. О поиске текстовых значений с использованием подстановочных знаков читайте в статье Поиск текстовых значений в списках. Перейти к основному содержанию. Справочник по функциям Карта сайта О сайте Ваш кабинет. Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Задача Выведем в отдельный диапазон значения, которые удовлетворяют критерию. Связанные статьи Похожие задачи Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач. Вывод отобранных значений в отдельный диапазон. В статье использовались следующие функции Excel. Выбрав любую из нижеуказанных функций Excel, Вы сможете более подробно ознакомиться с ее применением в различных примерах и задачах. Функция СТРОКА в MS EXCEL. Функция ИНДЕКС в MS EXCEL. Функция ДВССЫЛ в MS EXCEL. Функция ЧСТРОК в MS EXCEL. Функция ЕСЛИ в MS EXCEL. Функция ДЛСТР в MS EXCEL. Функция ПРАВСИМВ в MS EXCEL. Функция ПОИСК в MS EXCEL. В статье использовались следующие стандартные средства Excel. Выбрав из нижеуказанного списка, Вы можете подробнее ознакомиться с этим средством. F9 — пошаговое вычисление формулы. Выберите оценку Give Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Комментарии trioseo 23 июл - Спасибо за хорошую подборку. А где можно скачать "Файл примера"? Creator 25 июл - Файл примера загружен, ссылка внизу статьи.
Электричка сочи красная поляна расписание
Заплати сколько хочешь
Как украсить льняное платье своими руками
Афиша берлин пенза расписание
Шведская крона где обменять на рубли