Каждая третья задача пользователя – это получение данных из табличного массива при заданных условиях:

Наиболее часто для решения подобных задач используют функции для работы с табличными данными, например: ПОИСКПОЗ, ИНДЕКС, ДВССЫЛ, СМЕЩ и т.д.

Однако, если список критериев уникален и при этом критерии располагаются в левом/правом столбце или верхней/нижней строке, то решение может быть совсем лаконичным.

1) Необходимо присвоить диапазонам имена – выделить таблицу так, чтобы в левом столбце и верхней строке были критерии, нажать клавиши CTRL+SHIFT+F3, чтобы создать имена по выделенным данным:

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

2) создать простую формулу пересечения диапазонов: ИмяДиапазона1(ПРОБЕЛ)ИмяДиапазона2

Однако, такая формула не имеет гибкости, т.к. вводить имена в этом случае нужно с клавиатуры снова и снова.

Усовершенствуем!

1) Создадим в 2-х ячейках Н4 и H5 списки для выбора критериев.

2) Напишем формулу, ссылаясь на эти ячейки: =H4 H5

Но результата в этом случае не будет. т.к. пересечение 2-х текстовых значений не может давать никакого результата. Значит, необходимо сделать так, чтобы значение, которое присутствует в ячейке Н4 (H5) становилось именем. С подобной задачей отлично справляется функция ДВССЫЛ [INDIRECT].

Таким образом, конечная формула будет: =ДВССЫЛ(H4) ДВССЫЛ(H5) или =INDIRECT(H4) INDIRECT(H5)

Теперь можно легко выбирать месяца и города в списках и результат не заставит себя ждать!

Предположим, у нас есть данные по результатам тестов нескольких учащихся. Допускалось пропустить максимум 2 теста. Необходимо проанализировать тенденцию – улучшение или ухудшение результатов по каждому учащемуся и выявить наиболее стойкого к тестам. Отсутствие данных не позволяет построить наглядный график для анализа, т.к. в этом случае пустоты программа ничем заполнить не может. И тем самым график получается прерывистым.

Немного поработаем с исходными данными и соединим все точки. Для этого необходимо построить вспомогательную таблицу с небольшой особенностью заполнения исходных данных: если результат теста (значение) есть, то берем его, а иначе – ничего. Чтобы ничего не стало нулем и могло быть показано на графике стоит ввести #Н/Д [#N/A].

И построенный график по такой табличке будет более наглядным!

Первый раз срезы (Slicer) появились в Excel 2010 версии как инструмент фильтрации только в сводных таблицах. В 2013 версии срезы можно использовать и при работе с таблицами. В 2016 версии немного расширили возможности выбора данных – добавили кнопку Выбор нескольких объектов. При первом беглом использовании может показаться, что это кто-то за нас держит клавиши Ctrl, чтобы нам было удобнее выбирать несколько значений. Однако это не всегда так. И в зависимости от того, какую последовательность действий мы выбираем, у нас и получается либо выбор нужных значений, либо исключение значений. Рассмотрим эти два варианта на примере фильтрации сводной таблицы:

Вариант 1. Исключение значений.

1) нажать кнопку Выбор нескольких значений [Multi-Select]

2) щелкнуть в срезе по значениям (например, набор “Красота” и набор “Радость”)

Результат:

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

Вариант 2. Выбор нужных значений.

1) выбрать значение в срезе (например, набор “Красота”)

2) нажать кнопку Выбор нескольких значений [Multi-Select]

3) щелкнуть в срезе по другому нужному значению (например, и набор “Радость”)

Результат:

В этом случае происходит именно выбор нужных значений.

Используйте срезы – это удобно и наглядно!