Сводная таблица с текстом в значениях

Всем хороши сводные таблицы - и считают быстро, и настраиваются гибко, и дизайн можно накрутить в них нарядный, если требуется. Но есть и несколько ложек дегтя, в частности, невозможность создать сводную, где в области значений должны быть не числа, а текст.

Давайте попробуем обойти это ограничение и придумать "пару костылей" в подобной ситуации.

Допустим, наша компания возит в несколько городов России и Казахстана свою продукцию в контейнерах. Контейнеры отправляются не чаще, чем раз в месяц. Каждый контейнер имеет буквенно-цифровой номер. В качестве исходных данных имеется стандартная таблица с перечислением поставок, из которой нужно сделать некое подобие сводной, чтобы наглядно видеть номера контейнеров, отправленных в каждый город и каждом месяце:

Исходные данные и результат

Для удобства, давайте заранее сделаем таблицу с исходными данными "умной" с помощью команды Главная - Форматировать как таблицу (Home - Format as Table) и дадим ей имя Поставки на вкладке Конструктор (Design). В дальнейшем, это упростит жизнь, т.к. можно будет использовать имя таблицы и ее столбцов прямо в формулах.

Способ 1. Самый простой - используем Power Query

Power Query - это супермощный инструмент для загрузки и преобразований данных в Excel. Эта надстройка по умолчанию встроена в Excel начиная с 2016-й версии. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (совершенно бесплатно).

Весь процесс, для наглядности, я пошагово разобрал в следующем видео:

Если нет возможности использовать Power Query, то можно пойти другими путями - через сводную таблицу или формулы. 

Способ 2. Вспомогательная сводная

Добавим к нашей исходной таблице еще один столбец, где с помощью простой формулы вычислим номер каждой строки в таблице:

Добавляем номер строки

Очевидно, что -1 нужен, поскольку у нас в таблице есть однострочная шапка. Если ваша таблица лежит не в начале листа, то можно использовать чуть более сложную, но универсальную формулу, которая вычисляет разницу в номерах текущей строки и шапки таблицы:

Универсальная формула расчета номера строки

Теперь стандартным образом построим на основе наших данных сводную таблицу желаемого вида, но в поле значений закинем поле Номер строки вместо нужного нам Контейнера:

Сводная с номерами строк

Поскольку у нас не бывает нескольких контейнеров в одном и том же городе в один и тот же месяц, то наша сводная выдаст, фактически, не сумму, а номера строк нужных нам контейнеров.

Дополнительно можно отключить общие и промежуточные итоги на вкладке Конструктор - Общие итоги и Промежуточные итоги (Design - Grand Totals, Subtotals) и там же переключить сводную в более удобный табличный макет кнопкой Макет отчета (Report Layout).

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

Теперь скопируем сводную (на этот же лист или на другой) и вставим как значения, а затем введем в область значений свою формулу, которая будет извлекать код контейнера по номеру строки, найденному в сводной:

Извлекаем код контейнера по номеру строки в сводной

Функция ЕСЛИ (IF), в данном случае, проверяет, чтобы очередная ячейка в сводной была не пустой. Если пустая, то выводим пустую текстовую строку "", т.е. оставляем ячейку незаполненной. Если не пустая, то извлекаем из столбца Контейнер исходной таблицы Поставки содержимое ячейки по номеру строки с помощью функции ИНДЕКС (INDEX).

Пожалуй, единственным не очень очевидным моментом тут является задвоенное слово Контейнер в формуле. Такая странная форма записи:

Поставки[[Контейнер]:[Контейнер]]

... нужна лишь для того, чтобы ссылка на столбец Контейнер была абсолютной (как ссылка со знаками $ для обычных "не умных" таблиц) и не сползала на соседние столбцы при копировании нашей формулы вправо.

В будущем, при изменении данных в исходной таблице Поставки, необходимо не забыть обновить нашу вспомогательную сводную с номерами строк, щелкнув по ней правой кнопкой мыши и выбрав команду Обновить (Refresh).

Способ 3. Формулы

Этот способ не требует создания промежуточной сводной таблицы и ручного обновления, а использует "тяжелое оружие Excel" - функцию СУММЕСЛИМН (SUMIFS). Вместо поиска номеров строк в сводной их можно вычислить с помощью вот такой формулы:

Ищем номера строк функцией СУММЕСЛИМН

При некоторой внешней громоздкости, на самом деле, это стандартный вариант использования функции выборочного суммирования СУММЕСЛИМН, которая суммирует номера строк для заданного города и месяца. Опять же, поскольку у нас не бывает нескольких контейнеров в одном и том же городе в один и тот же месяц, то наша функция выдаст, фактически, не сумму, а сам номер строки. А затем уже знакомой по предыдущему способу функцией ИНДЕКС можно извлечь и коды контейнеров:

Сводная с текстом в области значений

Само-собой, в этом случае уже не нужно думать про обновление сводной, но на больших таблицах функция СУММЕСЛИ может ощутимо тормозить. Тогда придется отключать автоматическое обновление формул или же воспользоваться первым способом - сводной таблицей.

Если внешний вид сводной вам не очень подходит для отчета, то можно вытаскивать из нее номера строк в финальную таблицу не напрямую, как мы делали, а с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA). Как это сделать можно посмотреть здесь.

Ссылки по теме


16.11.2016 19:46:48
А если вместо столбца с номером строки поставить слияние первых трех столбцов (РоссияМоскваянв), то можно привычно через впр подтянуть номера контейнеров.

не так изощеренно, но работает.
18.11.2016 22:47:40
Как всегда шикарно, Николай! ))
21.11.2016 09:07:39
Отлично! Как всегда, ловкое применение стандартных инструментов дает прекрасный результат :)

Я бы решал эту задачу при помощи PowerPivot (это просто, когда знаешь :) :
для конкретно такой формы результата и исходных данных
:=VALUES('Поставки'[Контейнер]),
для возможных повторений или итогов
:=IF(HASONEVALUE('Поставки'[Контейнер]),VALUES('Поставки'[Контейнер]),CONCATENATEX('Поставки','Поставки'[Контейнер],", "))
05.03.2017 21:18:50
Добрый день. Благодаря Вам, решил шире глянуть на мир и решить эту задачу при помощи PowerPivot . Но ничего не получилось :(  В общем не мудрено, поскольку не знаю. Я пишу =VALUES('Таблица'[Поле]) как формулу вычисляемого поля и ничего не получается. Очевидно что-то не так делаю. Буду признателен, если подробнее поясните как достичь цели.
26.01.2017 15:35:34
Здравствуйте, спасибо Вам за детальное обьяснение!
Данные способы я так понимаю работают только в случае отсутствия нескольких значений, которые соответствуют одному и тому же городу и месяцу (как в этом примере).
А что делать, если есть все-таки несколько значений? буду Вам очень благодарна за помощь!!
15.07.2017 15:59:57
Способ 1, который с Power Query может и несколько значений вывести через запятую - см. видео.
14.02.2017 08:20:41
Раньше текст в данных сводной можно было реализовать в старом MS Access
Жаль что эта функция не получила дальнейшего развития
12.04.2017 12:07:51
Использую вложение функции поискпозиции: =ИНДЕКС(отгрузки!A3:I1411;ПОИСКПОЗ(H5&I4;отгрузки!A3:A1411&отгрузки!I3:I1411;0);6), где A:I - массив, H&I - критерии поиска; 0 - точное совпадение; 6 - номер поля искомого значения. Формула массива, поэтому жмём Ctrl+Shift+Enter.
11.07.2017 09:08:31
Подскажите, может есть идеи , как сделать такую же таблицу, но если на одно значение "Россия/москва"  приходится несколько январей с разными контейнерами? Я уже голову себе сломала(
15.07.2017 15:59:02
Ядвига, записал видео про то, как это сделать с помощью Power Query - посмотрите Способ 1 в статье.
11.07.2017 09:33:47
и значения повторяющиеся должны идти в стоке по порядку, то есть на 1 строку "Россия/москва" в следующем столбце - 1 ящик, в 3 столбце - 2й ящик и так далее
17.07.2017 16:56:52
Подскажите, пожалуйста, если при нажатии на значёк в названии пользовательского столбца (со списком ), для извлечения значений списка автоматически разворачивается список, и не появляется меню с возможностью выбора разделителя. Это проблема в формате данных или самого экселя (установлен 2016) и есть ли другие возможности объединить подобным образом записи для двух и более строк имеющих общие данные (как в примере когда в одна страна - разные контейнеры)?
08.12.2017 14:13:18
Существует более простой способ получения столбца со списком кодов контейнеров.
При создании пользовательского столбца ввести формулу:

=Text.Combine([Поставки][Контейнер], ", ")

За данный способ выражаю благодарность Максиму Зеленскому