Содержание:
Сводные таблицы всем хороши — они быстро рассчитываются, гибко настраиваются, а дизайн в них можно элегантно накрутить, если потребуется. Но есть и несколько ложек дегтя, в частности, невозможность создать сводку, где область значений должна содержать не цифры, а текст.
Попробуем обойти это ограничение и придумать «пару костылей» в подобной ситуации.
Предположим, наша компания перевозит свою продукцию в контейнерах в несколько городов Нашей Страны и Казахстана. Контейнеры отправляются не чаще одного раза в месяц. Каждый контейнер имеет буквенно-цифровой номер. В качестве исходных данных имеется стандартная таблица со списком доставок, из которой нужно сделать некую сводку, чтобы наглядно видеть количество отправленных контейнеров в каждый город и каждый месяц:
Для удобства заранее сделаем таблицу с исходными данными «умной» с помощью команды Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу) и дай ей имя Поставки таб Конструктор (Дизайн). В будущем это упростит жизнь, т.к. можно будет использовать имя таблицы и ее столбцов непосредственно в формулах.
Способ 1. Самый простой — использовать Power Query.
Power Query — это сверхмощный инструмент для загрузки и преобразования данных в Excel. Эта надстройка встроена в Excel по умолчанию с 2016 года. Если у вас Excel 2010 или 2013, вы можете скачать и установить ее отдельно (совершенно бесплатно).
Весь процесс, для наглядности, я пошагово разобрал в следующем видео:
Если нет возможности использовать Power Query, то можно пойти другим путем — через сводную таблицу или формулы.
Способ 2. Вспомогательное резюме
Добавим в нашу исходную таблицу еще один столбец, где по простой формуле посчитаем количество каждой строки в таблице:
Очевидно, что -1 необходим, потому что в нашей таблице есть однострочный заголовок. Если ваша таблица находится не в начале листа, то можно использовать чуть более сложную, но универсальную формулу, вычисляющую разницу номеров текущей строки и заголовка таблицы:
Теперь стандартным образом построим сводную таблицу нужного типа на основе наших данных, но в поле значения оставим поле Номер строчки вместо того, что мы хотим контейнер:
Поскольку у нас нет нескольких контейнеров в одном городе в одном месяце, наша сводка будет, по сути, выдавать не количество, а номера строк нужных нам контейнеров.
Кроме того, вы можете отключить общие и промежуточные итоги на вкладке Конструктор – Общие итоги и подытоги (Оформление — Общие итоги, Промежуточные итоги) и там же переключаем сводку на более удобный табличный вид кнопкой Макет отчета (Макет отчета).
Таким образом, мы уже на полпути к результату: у нас есть таблица, где на пересечении города и месяца стоит номер строки в исходной таблице, где лежит нужный нам код контейнера.
Теперь скопируем сводку (на тот же или другой лист) и вставим ее как значения, а затем в область значений введем нашу формулу, которая извлечет код контейнера по номеру строки, найденному в сводке:
Функция IF (ЕСЛИ)в данном случае проверяет, не пуста ли следующая ячейка в сводке. Если пусто, то вывести пустую текстовую строку «», т.е. оставить ячейку пустой. Если не пусто, то извлечь из столбца Container исходная таблица Поставки содержимое ячейки по номеру строки с использованием функции ИНДЕКС (ПОКАЗАТЕЛЬ).
Пожалуй, единственный не совсем очевидный момент здесь — это двойное слово Container в формуле. Такая странная форма написания:
Запасы[[Контейнер]:[Контейнер]]
… необходим только для ссылки на столбец Container была абсолютной (как ссылка со знаком $ для обычных «неумных» таблиц) и не скатывалась на соседние столбцы при копировании нашей формулы вправо.
В дальнейшем при изменении данных в исходной таблице Поставки, мы должны не забыть обновить нашу вспомогательную сводку номерами строк, щелкнув по ней правой кнопкой мыши и выбрав команду Обновить и сохранить (Обновить).
Способ 3. Формулы
Этот метод не требует создания промежуточной сводной таблицы и ручного обновления, а использует «тяжелое оружие» Excel — функцию СУММЕСЛИМН (СУМИФС). Вместо поиска номеров строк в сводке вы можете вычислить их по следующей формуле:
При некоторой внешней громоздкости, по сути, это стандартный вариант использования функции выборочного суммирования. СУММЕСЛИМНA, который суммирует номера строк для данного города и месяца. Опять же, поскольку у нас нет нескольких контейнеров в одном городе в одном месяце, наша функция будет, по сути, выдавать не сумму, а сам номер строки. А дальше уже знакомая по предыдущему методу функция ИНДЕКС Вы также можете извлечь коды контейнеров:
Конечно, в этом случае уже не нужно думать об обновлении сводки, но на больших таблицах функция СУММЕСЛИ может быть заметно медленным. Тогда вам придется отключить автоматическое обновление формул или воспользоваться первым способом — сводной таблицей.
Если внешний вид сводки не очень подходит вашему отчету, то извлекать из нее номера строк в итоговую таблицу можно не напрямую, как мы это делали, а с помощью функции GET.PIVOT.TABLE.DATA (ПОЛУЧИТЬ.ПИВОТ.ДАННЫЕ). Как это сделать, можно узнать здесь.
- Как создать отчет с помощью сводной таблицы
- Как настроить расчеты в сводных таблицах
- Выборочный подсчет с помощью SUMIFS, COUNTIFS и т. д.