Сводная таблица с текстом в значениях
Всем хороши сводные таблицы - и считают быстро, и настраиваются гибко, и дизайн можно накрутить в них нарядный, если требуется. Но есть и несколько ложек дегтя, в частности, невозможность создать сводную, где в области значений должны быть не числа, а текст.
Давайте попробуем обойти это ограничение и придумать "пару костылей" в подобной ситуации.
Допустим, наша компания возит в несколько городов России и Казахстана свою продукцию в контейнерах. Контейнеры отправляются не чаще, чем раз в месяц. Каждый контейнер имеет буквенно-цифровой номер. В качестве исходных данных имеется стандартная таблица с перечислением поставок, из которой нужно сделать некое подобие сводной, чтобы наглядно видеть номера контейнеров, отправленных в каждый город и каждом месяце:
Для удобства, давайте заранее сделаем таблицу с исходными данными "умной" с помощью команды Главная - Форматировать как таблицу (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). Как это сделать можно посмотреть здесь.
Ссылки по теме
- Как создать отчет с помощью сводной таблицы
- Как настроить вычисления в сводных таблицах
- Выборочный подсчет с помощью функций СУММЕСЛИМН, СЧЕТЕСЛИМН и т.п.
не так изощеренно, но работает.
Я бы решал эту задачу при помощи PowerPivot (это просто, когда знаешь
для конкретно такой формы результата и исходных данных
:=VALUES('Поставки'[Контейнер]),
для возможных повторений или итогов
:=IF(HASONEVALUE('Поставки'[Контейнер]),VALUES('Поставки'[Контейнер]),CONCATENATEX('Поставки','Поставки'[Контейнер],", "))
Данные способы я так понимаю работают только в случае отсутствия нескольких значений, которые соответствуют одному и тому же городу и месяцу (как в этом примере).
А что делать, если есть все-таки несколько значений? буду Вам очень благодарна за помощь!!
Жаль что эта функция не получила дальнейшего развития
При создании пользовательского столбца ввести формулу:
За данный способ выражаю благодарность
однако столкнулся с ограничением в Power Query на размеры таблицы.
как с этим бороться?
1. возможно ли (и каким образом) при переводе в excel, разместить данные на каждой строке (НЕ ячейке)? При формировании таблицы в Power Query, это возможно сделать на этапе "Извлечения значения", указав при выборе разделителя спец.символ "Перевод строки". Но при загрузке в Excel, все данные встают в одну строку, без разделителя.
2. каким образом можно собрать таблицу, находящуюся на n-ом кол-ве листов, для дальнейшей работы с ней через PowerQuery
2. В Power Query и собрать. Данные - Создать запрос - Из файла Excel - выбрать нужные листы.
Не совсем понятно правильно ли я делаю сбор таблицы. Таблица состоит из листов (1 лист - 1 месяц). Чтобы собрать, я перехожу в Power Query, в меню на Главной выбираю "Создать источник" и выбираю файл, из которого необходимо сделать выгрузку. Далее через "Добавить запрос" выбираю нужное кол-во таблиц и создаю единую базу. И уже потом могу работать с образованной объеденной базой. Но есть вопрос:
Как выгрузить только объеденную базу? У меня при загрузке в Excel выгружаются все листы из которых создавалась база + новая созданная единая база.
Нажимаю параметры поля, там есть вкладка "Разметка и печать" и когда добавляю строки в сводную таблицу, у каждого поля на этой вкладке отмечаю "В виде таблицы" и все даты выводятся датами (при необходимости словами) и слова выводятся текстом.
или я что-то не так сделала...
предлагает только два варианты загрузки
1) Таблица
2) Только создать подключение
сводную таблицу выгрузить не предлагает
Excel 2016
А можно на данную таблицу ещё наложить фильтр по какому-то столбцу, чтобы пользователь мог фильтровать какую-то категорию и данные менялись?
Expression.Error: Не удается преобразовать значение null в тип Text.
Подробные сведения:
Value=
Type=Type
Могу я с это как-то побороть?
1) добавляем таблицу в модель денных
2) делаем сводную
3 ) создаем меру в которую записываем следующую формулу на dax :
=CONCATENATEX(Values(Table[Column]), Table[Column], ", ")
где table - название сводной, column название столбца откуда нужно вывести текст в область значений
4) добавляем меру в область значений.
Хотела бы уточнить, данный способ позволяет из каких-либо столбцов делать фильтр (как в сводной таблице) или нет?
Хотелось бы, например, в своде увидеть фильтр по стране в левом углу и возможностью выбрать страну.