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

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

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

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

Для удобства, давайте заблаговременно создадим таблицу с начальными данными «умной» при помощи команды Основная — Форматировать как таблицу (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) . Как это создать можно поглядеть тут.

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

• Как сделать отчет при помощи сводной таблицы
• Как настроить вычисления в сводных таблицах
• Выборочный подсчет при помощи функций СУММЕСЛИМН, СЧЕТЕСЛИМН и т.п.

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

не так изощеренно, но работает.

Непревзойденно! Как постоянно, ловкое применение обычных инструментов дает красивый итог

Я бы решал эту задачку с помощью PowerPivot (это просто, когда знаешь :
для непосредственно таковой формы результата и начальных данных
:=VALUES(‘Поставки'[Контейнер]),
для вероятных повторений либо итогов
:=IF(HASONEVALUE(‘Поставки'[Контейнер]),VALUES(‘Поставки'[Контейнер]),CONCATENATEX(‘Поставки’,’Поставки'[Контейнер],», «))

Хороший денек. Благодаря Для вас, решил обширнее взглянуть на мир и решить эту задачку с помощью PowerPivot . Но ничего не вышло В общем не сложно, так как не понимаю. Я пишу =VALUES(‘Таблица’[Поле]) как формулу вычисляемого поля и ничего не выходит. Разумеется что-то не так делаю. Буду благодарен, если подробнее поясните как достигнуть цели.

Существует наиболее обычной метод получения столбца со перечнем кодов контейнеров.
При разработке пользовательского столбца ввести формулу: