Excel диапазон по условию
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции ВЫБОР в Microsoft Excel.
Описание
Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента "номер_индекса".
Синтаксис
Аргументы функции ВЫБОР описаны ниже.
Номер_индекса — обязательный аргумент. Номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, возвращается значение2 и так далее.
Если номер_индекса представляет собой дробь, то он усекается до меньшего целого.
Значение1,значение2. — аргумент "значение1" является обязательным, следующие за ним — нет. От 1 до 254 аргументов-значений, из которых функция ВЫБОР, используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.
Замечания
Если номер_индекса является массив, то при выполнении функции ВЫБОР вычисляется каждое значение.
Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями.
которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.
В этом примере сначала вычисляется функция ВЫБОР, которая возвращает ссылку на интервал B1:B10. Затем вычисляется функция СУММ, причем в качестве аргумента используется интервал B1:B10, то есть результат функции ВЫБОР.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Если для фильтрации данных требуются сложные условия (например, Тип = "Фрукты" ИЛИ Продавец = "Егоров"), можно использовать диалоговое окно Расширенный фильтр.
Чтобы открыть диалоговое окно Расширенный фильтр, щелкните Данные > Дополнительно.
Расширенный фильтр
Продавец = "Егоров" ИЛИ Продавец = "Грачев"
Тип = "Фрукты" И Продажи > 1000
Тип = "Фрукты" ИЛИ Продавец = "Грачев"
(Продавец = "Егоров" И Продажи >3000) ИЛИ
(Продавец = "Грачев" И Продажи > 1500)
Продавец = имя со второй буквой "г"
Обзор расширенных условий фильтра
Действие команды Дополнительно отличается от действия команды Фильтр в некоторых важных аспектах.
Она отображает диалоговое окно Расширенный фильтр, а не меню "Автофильтр".
Вы вводите расширенные условия в отдельном диапазоне условий на листе над диапазоном ячеек или таблицей, которую нужно отфильтровать. В качестве источника расширенных условий в Microsoft Office Excel используется отдельный диапазон условий в диалоговом окне Расширенный фильтр.
Образец данных
Этот пример данных используется для всех процедур, описанных в этой статье.
Эти данные включают четыре пустые строки над диапазоном списка, которые будут использоваться как диапазон условий (A1:C4) и диапазон списка (A6:C10). Диапазон условий содержит названия столбцов и по крайней мере одну пустую строку между значениями условий и диапазоном списка.
Для работы с этими данными выделите их в следующей таблице, скопируйте, а затем вставьте в ячейку A1 на новом листе Excel.
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
Меньше или равно
Использование знака равенства для ввода текста или значения
При вводе текста или значения в ячейке знак равенства (=) используется для обозначения формулы, поэтому Excel вычисляет то, что вы вводите. Однако при этом вы можете получить неожиданные результаты фильтрации. Чтобы указать оператор сравнения "равно" для текста или значения, введите условия в виде строкового выражения в соответствующей ячейке в диапазоне условий.
=''= ввод ''
где ввод — искомый текст или значение. Например:
Вводится в ячейку
Вычисляется и отображается
Учет регистра
При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. в разделе Условия с подстановочными знаками.
Использование заранее определенных имен
Вы можете назвать диапазон Условия, и ссылка на диапазон автоматически появится в поле Диапазон условий. Вы также можете указать имя База данных для диапазона списка, который будет фильтроваться, и имя Извлечение для области, в которой вы собираетесь вставлять строки. Эти диапазоны автоматически появятся в полях Исходный диапазон и Поместить результат в диапазон соответственно.
Создание условий с помощью формулы
В качестве условия можно использовать значение, вычисленное с помощью формулы. Обратите внимание на важные моменты, указанные ниже.
Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''= ввод ''
Не используйте название столбца в качестве названия условия. Либо оставьте название условия пустым, либо используйте название, не являющееся названием столбца в диапазоне списка (в последующих примерах: "Среднее арифметическое" и "Точное совпадение").
В формуле, которая используется для условий, необходимо использовать относительную ссылку для ссылки на соответствующую ячейку в первой строке данных.
Все остальные ссылки в формуле должны быть абсолютными.
Несколько условий, один столбец, любое из условий истинно
Логическое выражение: (Продавец = "Егоров" ИЛИ Продавец = "Грачев")
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.
Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько условий, несколько столбцов, все условия истинны
Логическое выражение: (Тип = "Фрукты" И Продажи > 1000)
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.
Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько условий, несколько столбцов, любое из условий истинно
Логическое выражение: (Тип = "Фрукты" ИЛИ Продавец = "Грачев")
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет: При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько наборов условий, один столбец во всех наборах
Логическое выражение: ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет: При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$D$3.
Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Несколько наборов условий, несколько столбцов в каждом наборе
Логическое выражение: ( (Продавец = "Егоров" И Продажи > 3000) ИЛИ (Продавец = "Грачев" И Продажи > 1500) )
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельных столбцах или строках. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте.
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3. Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .
Используя пример, получим следующий отфильтрованный результат для диапазона списка:
Условия с подстановочными знаками
Логическое выражение: Продавец = имя со второй буквой "г"
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
Чтобы найти строки, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства (=). Например, если ввести условие Бел, будут найдены строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
Воспользуйтесь подстановочными знаками.
Используйте
Чтобы найти
Любой символ (один)
Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"
Любое количество символов
Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"
~ (тильда), за которой следует ?, * или ~
Вопросительный знак, звездочку или тильду
Пример: условию "ан91~?" соответствует результат "ан91?"
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
В строках под названиями столбцов введите условия, которым должен соответствовать результат. Используя пример, введите:
Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выполните одно из следующих действий:
Чтобы отфильтровать диапазон списка, скрыв строки, не отвечающие условиям, выберите вариант Фильтровать список на месте
Чтобы отфильтровать список, скопировав строки, не отвечающие условиям, в другую область листа, выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон, а затем щелкните левый верхний угол области, в которой нужно вставить строки.
Совет: При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно .
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом из них. Таблица очень угрюмая, поэтому, возможно, вам кажется, что это сложная задача. С функцией подытов на самом деле это довольно просто.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "кузьмина" или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подытовки, прежде чем попробовать их с собственными данными, вот примеры данных.
Пример работы с ВЛОКОНПОМ
Скопируйте следующие данные в пустую таблицу.
Совет: Прежде чем врезать данные в Excel, установите для столбцов A–C ширину в 250 пикселей и нажмите кнопку "Перенос текста" (вкладка "Главная", группа "Выравнивание").
Автоматическое определение диапазона "от-до" в исходной таблице моно применять для автоматизации многих задач связанных с динамической выборкой значений. Рассмотрим один из простых для понимания способов реализации данной задачи.
Как автоматически выделять диапазоны для выборки ячеек из таблицы?
Задание является следующим. В одном из столбцов в разных ячейках находятся какие-то значения (в данном случае текстовые строки "граница"). Они определяют начало и конец секторов (диапазонов). Эти значения вставлены автоматически и могут появляться в разных ячейках. Их размеры и количество в них ячеек также может быть разным. Например, на рисунке ниже выбран сектор данных (диапазон) номер 2.
Все, что следует сейчас сделать - это создать возможность легкого и быстрого выбора диапазона, который нас интересует (вписывая номер диапазона в одну из ячеек для выбора).
Динамическое определение границ выборки ячеек
Для наглядности приведем решение этой задачи с использованием вспомогательного столбца. В первую ячейку в вспомогательном столбце (A7) вводим формулу:
и копируем ее вниз в оставшиеся ячейки. Везде, где в ячейках соседнего столбца находится значение "граница", функция возвращает номер строки. В противном случае возвращает пустую строку.
Следующим шагом будет динамическое определение адреса для выборки диапазона данных из исходного списка в соответствии с критерием отбора.
Изменяемое значение критерия для управления выборкой данных из таблицы будет указано в ячейке C1. Там мы указываем порядковый номер диапазона, данные которого нас интересуют в конкретный момент.
Как получить адрес диапазона ячеек в Excel?
После чего динамически определим адрес начальной ячейки, с которой будет начинаться диапазон. В C2 вводим следующую формулу:
Во втором аргументе функции НАИМЕНЬШИЙ указывается ссылка на ячейку C1, где находится порядковый номер интересующего нас сектора данных (диапазона). А для функции НАИМЕНЬШИЙ – это значение является порядковым номером наименьшего числа в диапазоне вспомогательного столбца $A$7:$A$22 (первый аргумент).
Аналогичным образом динамически определяем адрес последней ячейки, где должна заканчивается выборка. Для этого в C3 водим формулу:
Как не сложно догадаться во втором аргументе функции НАИМЕНЬШИЙ мы прибавляем единицу чтобы получить следующее по порядку наименьшее значение в вспомогательном столбце $A$7:$A$22. Все просто и красиво – такая должна быть магия!
Это практически все. Дальше используйте свое воображение для применения этой полезной функции автоматической выборки диапазонов данных из таблицы по условию пользователя. Например, воспользуемся условным форматированием.
Автоматическая подсветка цветом диапазонов ячеек по условию
Будем подсвечивать цвет диапазона, который соответствует порядковому номеру указанном в критериях выборки C1.
- Выделите диапазон ячеек C7:C22 и выберите иснтрумент «ГЛАВНАЯ»-«Условное фомратирование»-«Создать правило».
- В появившемя окне выберите опцию «Использовать формулу для определения форматируемых ячеек». Там же в поле ввода введите такую фомрулу:
- Нажмите на кнопку формат и укажите цвет для подсветки соответствующих ячеек. Например, зеленый.
Теперь мы изменим критерий выборки, например, на 1. Автоматически подсветился зеленым цветом весь первый диапазон. Обратите внимание в нем на одну ячейку больше чем во втором, но все работает безошибочно.
Проверка вводимых значений в Excel на ошибки
Наконец, вы можете предупредить ошибку в случае ввода неверных (не соответствующего формата) значений (числа меньшего или равного нулю, большего, чем общее количество диапазонов) в качестве номера диапазона, который вы ищете. Все это можете сделать при помощи проверки данных. Перейдите на ячейку для ввода критериев выборки C1 и выберите инструмент: «ДАННЫЕ»-«Проверка данных».
Соответствующая формула «обеспечивающая безопасность» могла бы выглядеть так:
0;$D$7
Нажмите ОК, после внесения всех изменений как показано выше на рисунке.
Теперь при попытке ввода в критерий выборки числа больше чем количество диапазонов в пределах «границ», будет предупреждение для пользователя:
Такая выборка может использоваться в автоматизации других различного рода интересных задач. Здесь же приведен только базовый пример возможностей динамической выборки данных из исходной таблицы.
Читайте также: