Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как добавить несколько полей в сводную таблицу?

Сводные таблицы в SQL

Перекрестная таблица — это один из самых основных типов аналитики. Многие считают, что ее невозможно создать с помощью инструментов SQL. Конечно, это не так.

Предположим, у нас есть таблица с данными о закупках нескольких видов товаров (Товар 1, 2, 3, 4) у разных поставщиков (A, B, C):

Типичная задача — определить важность закупок по поставщикам и по продуктам, т.е. построить сводную таблицу. Пользователи MS Excel привыкли получать этот тип анализа с помощью нескольких щелчков мыши:

Процесс немного медленнее в SQL, но решения, как правило, тривиальные.

1. Оператор CASE и аналоги

Самый простой и очевидный способ получить сводную таблицу — это жестко закодировать ее с помощью оператора CASE. Например, для поставщика А можно рассчитать размер поставки как сумму(case when t.supplier = ‘A’ then t.volume end ). Чтобы получить объем поставок различных продуктов, достаточно добавить группировку по полю продукта:

Если вы добавите else 0 , вместо нулей будут введены нули для позиций, для которых не было поставок:

Если мы продублируем код для всех поставщиков (которых у нас три — A, B, C), мы получим нужную нам сводную таблицу:

К нему можно добавить линейный итог (например, десятидолларовую сумму). sum(t.volume) )

Как и добавление итогов по столбцам, это несложно. Необходимо использовать оператор ROLLUP для добавления суммирующей строки. В большинстве баз данных используется синтаксис rollup(t.product); однако иногда доступен альтернативный синтаксис t.product with rollup (например, SQL Server).

Результат можно сделать еще более красивым, заменив NULL на собственную сигнатуру total. Для этого можно использовать функцию coalesce() : coalesce(t.product, ‘total_sum’) или любой аналог, специфичный для СУБД (например, nvl() в Oracle). Результат будет выглядеть следующим образом:

Ваша СУБД должна работать с костылями, если она настолько стара, что не может справиться с рулонами. Например, вот так:

Можно (но вряд ли стоит) использовать одну из функций, специфичных для производителя, вместо стандартной функции CASE. Например, в PostgreSQL и SQLite доступен оператор FILTER:

Особенностью FILTER является то, что он является частью стандарта (SQL:2003), но фактически поддерживается только в PostgreSQL и SQLite.

Другие базы данных имеют ряд нестандартных эквивалентов CASE: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+ и т.д. В большинстве случаев от их использования нет никакой пользы, и это только усложняет сопровождение вашего кода в будущем.

2. Использование PIVOT (SQL Server и Oracle)

Описанный выше подход вряд ли можно назвать красивым. Самое меньшее, что мы хотим сделать, — это не дублировать код между категориями поставщиков, а просто перечислить их. Добиться этого можно с помощью PIVOT (PIVOT) таблицы, доступного в SQL Server и Oracle. СУБД предлагают одинаковый синтаксис, несмотря на то, что этот оператор не существует в стандарте SQL.

Для начала нам нужна таблица, содержащая агрегированную статистику, которую мы будем «отображать». Кажется, достаточно взять суммы по каждому продукту и по каждому поставщику:

Читайте так же:
Как добавить линию ряда в диаграмму в Excel?

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

Этот запрос можно значительно упростить, используя оператор CUBE :

Если мы хотим получить сигнатуру итогов в виде ‘total_sum’ вместо NULL, то запрос нужно немного подкорректировать:

PIVOT уже можно применить к этому результату:

Таблица поворачивается с помощью агрегатной функции sum(agg). Мы берем заголовки столбцов из поля поставщика и используем в («A», «B», «C», «total_sum»), чтобы указать, какие поставщики должны быть отображены (total_sum отвечает за столбец с итоговыми значениями строк).

3. Common table expression

В принципе, для «запуска» таблицы нам не нужен оператор PIVOT как таковой. Этот запрос можно легко переписать, используя стандартный синтаксис: комбинацию CTE (common table expression) и соединений. Для этого мы будем использовать тот же запрос, что и для PIVOT :

Из результатов, полученных в cte, нам необходимы только уникальные значения товаров:

. к которому можно поочередно добавить объем закупок для каждого отдельного поставщика:

Использование левого соединения здесь обусловлено тем, что некоторые продукты могут отсутствовать у поставщика.

Окончательное приложение будет выглядеть примерно так:

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

4. Функция CROSSTAB (PostgreSQL)

В PostgreSQL есть функция CROSSTAB, которая примерно эквивалентна PIVOT в SQL Server или Oracle. Для ее использования необходимо расширение tablefunc:

C ROSSTAB принимает в качестве основного аргумента запрос в виде текста sql . PIVOT будет работать так же, как и PIVOT, но потребуется сортировка:

В отличие от PIVOT, для «отображения» таблицы мы должны указать не только имена столбцов, но и типы данных. Например: «product» varchar, «A» bigint, «B» bigint, «C» bigint, «sum_total» bigint.

Еще один нюанс заключается в том, что CROSSTAB заполняет строки слева направо, игнорируя значения NULL. Например, запрос, подобный этому:

…не дадут нам того, что мы хотим:

Справа вы можете увидеть, где были значения NULL. Например, в первой строке для Продукта1 в столбце для поставщика С должен был стоять итог строки, а в столбце для поставщика В (который не осуществлял поставки) должен был стоять итог для поставщика С. Только Продукт3 является правильным, потому что все поставщики имели значения. Другими словами, если бы не были введены значения NULL, запрос вернул бы нужный результат.

Чтобы избежать такого поведения в CROSSTAB, необходимо использовать вариант функции с двумя параметрами. Второй параметр должен содержать запрос, который выводит все столбцы результата. В нашем случае это все имена поставщиков из таблицы + «total_sum» для итогов:

. и полный запрос будет выглядеть следующим образом:

5. Динамический SQL (на примере SQL Server)

Первоначальный запрос с помощью CASE (или CTE) уже более функционален, но вам все еще нужно вводить названия поставщиков вручную после PIVOT или CROSSTAB. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Я хотел бы автоматически выбирать их из test_supply (или из словаря, если он есть).

Читайте так же:
Как добавить текстовое поле в диаграмму в Excel?

Чистого SQL здесь недостаточно. Он предполагает статическую типизацию: СУБД должна заранее указать количество столбцов для создания плана запроса. Так, например, синтаксис PIVOT не позволяет использовать подзапросы. Однако это ограничение можно легко обойти с помощью динамического SQL. Для этого имена столбцов необходимо преобразовать в «element_1», «element_2», …, «element_n» и использовать в запросе.

Например, в SQL Server можно использовать STUFF, чтобы получить следующую строку

. и затем включить его в окончательный запрос:

Динамический SQL также может быть применен к первому решению CASE. Например, вот так:

Здесь используется цикл для перебора доступных поставщиков в таблице test_supply (ее можно заменить словарем, если он есть), после чего формируется соответствующий запрос:

Подобные решения имеются во многих СУБД. Однако мы уже слишком далеко ушли от чистого SQL. Любое использование динамического SQL подразумевает углубление в специфику конкретной СУБД (и соответствующего процедурного расширения SQL).

В итоге вы можете легко создать сводную таблицу с помощью инструментов SQL. Лучший метод для вашей СУБД может быть выбран из множества доступных вариантов — просто выберите тот, который вам больше нравится.

Создание модели данных

Для создания сводной таблицы может быть использована любая из существующих таблиц.

В меню Вставка выберите Сводная таблица. В указанном диалоговом окне мы видим опцию Добавить эти данные в модель данных. Мы могли бы выбрать это, но я рекомендую другой, более удобный способ. Просто нажмите кнопку OK.

В появившейся панели перекрестных вкладок появится опция ДРУГИЕ ТАБЛИЦЫ.

Давайте нажмем на нее. Появится вопрос:

Вы увидите, что все наши таблицы будут добавлены в список полей, когда вы ответите Yes:

Если вы начнете выбирать поля, через некоторое время в списке полей появится кнопка «Создать».

Щелкните его и создайте связи между нашими таблицами. Создание этой связи позволяет связать OrderLines и Orders друг с другом. Связи один-к-одному в Excel создаются легко, но связи один-ко-многим тоже вполне осуществимы. Причем сначала нужно указать таблицу со «многими», иначе Excel ругнется и предложит поменять их местами.

Создайте другие ссылки таким же образом.


Доступ к диалоговому окну управления отношениями можно получить через ленту АНАЛИЗ команды Отношения

Если вы хотите увидеть больше полей на панели «Поля сводной таблицы», вы можете выбрать этот вид с помощью кнопки Инструменты (значок шестеренки):

Результат будет следующим:

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

Просто и удобно!

Читайте также:

Введение в сводные таблицы

Автоматизация форматирования сводных таблиц

0

Проблема, поднятая Николаем очень правильная. Тут действительно не всё так просто. Поэтому подумал, что мой ответ будет интересен и другим читателям этой статьи:
————————————-
Николай,здравствуйте.

Читайте так же:
Как заменить все числа в ячейках на X в Excel?

Я понимаю ваши затруднения. Например, чтобы посчитать стоимость какого-либо
товара в заказе, надо [OrderLines].[количество]
умножить на [Goods].[Цена]. Это делается при помощи
вычисляемого поля, которое вы создать в меню Анализ сводной таблицы не можете,
так как эта таблица построена на основе Модели данных, а это уже часть PowerPivot функционала. Добавлять
вычисляемый столбец надо через модуль PowerPivot,
который у вас в Excel будет
только в версии Prof Plus. Речь идёт про MS Office 2013.

0

Получил такое письмо:
——————————-
Денис, здравствуйте,
спасибо за вашу статью про сводные таблицы по нескольким диапазонам.
http://perfect-excel.ru/publ. -1-0-67

не могли бы вы подсказать, как с помощью сводных таблиц узнать стоимость
заказов из вашего примере.

Подумайте о построении таких отчетов.

— вид продукта — общая стоимость согласно заказам
— клиент — общая сумма заказов
— заказ № — стоимость заказа
и т.п.

именно через сводные таблицы, а не модификацией текущих четырех таблиц.
Заранее большое спасибо.

Группируем по дате

Аналогичные приемы можно использовать при работе с датами. В сводной таблице щелкните правой кнопкой мыши дату в столбце или строке и выберите Группировать. Помимо выбора шага группировки, вы также можете установить начальный и конечный интервал. Для группировки по году, кварталу или месяцу интервал фиксирован и равен 1, а для группировки по дням вы можете установить свой собственный интервал, например 7 дней, для группировки данных по неделям. Для этого выберите «Дни» в качестве шага группировки, установите число дней равным 7, укажите для параметра «Начало в» дату, приходящуюся на начало недели, и нажмите OK. Информация группируется по неделям.

Группировка в сводных таблицах Excel

Большой объем данных сгруппирован по неделям (по 7 дней в каждой), и первым днем является воскресенье, день недели, соответствующий дате, указанной в параметре Starting At.

Структура сводной таблицы с несколькими диапазонами консолидации

Когда вы погрузитесь в новую перекрестную таблицу, вы заметите несколько интересных вещей. Список полей таблицы включает строку, столбец, значение и страницу1.

Сводные таблицы, содержащие несколько диапазонов консолидации в качестве источников данных, могут содержать только три основных поля: Строка, Столбец и Значение. В дополнение к этим базовым полям можно создать до четырех страничных полей. Помните об этом, особенно если ваш бизнес обслуживает компьютеры организации и консультирует пользователей.

Обратите внимание, что поля, автоматически создаваемые перекрестной таблицей, имеют описательные имена (Строка, Столбец и Значение). Вы можете изменить настройки полей, переименовать и отформатировать их соответствующим образом. Более подробную информацию о настройках полей можно найти в разделе Настройка сводной таблицы.

Поле Строка

Поле Row всегда создается на основе первого столбца источника данных. Обратите внимание, что на рис. 7.1 первым столбцом источника данных был столбец Activity. Соответственно, поле Row представляет направления деятельности в новой сводной таблице.

Рис. 7.11. Элементы данных в поле Столбец интерпретируются как один объект. Замена функции количество поля Столбец на функцию сумм выполняется по отношению ко всем элементам поля

Рисунок 7.11. Данные поля Column рассматриваются как один объект. Замена функции числа поля Column на функцию суммы производится по отношению ко всем элементам поля

Читайте так же:
Как выделить последнюю строку / ячейку в Excel?

Поле Столбец

Поле Столбец (Column) содержит остальные столбцы источника данных. Сводные таблицы, использующие несколько диапазонов консолидации, комбинируют все поля из исходных наборов данных (без первого столбца, который используется полем Строка) в некое «суперполе» с именем Столбец. Поля исходных наборов данных становятся элементами данных поля Столбец. В сводной таблице, представленной на рис. 7.11, в поле Столбец изначально применяется функция КОЛИЧЕСТВО (COUNT). Если задать для поля Столбец функцию СУММ (SUMM), это повлияет на все элементы данных поля Столбец.

Поле Значение

Поле Value содержит значения для всех данных в поле Column. Даже поля с изначально текстовыми значениями в наборе данных будут рассматриваться как числа. Примером может служить поле Destination Manager (см. столбец N на рисунке 7.11). Хотя в этом поле содержались имена и фамилии менеджеров из исходного набора данных, в сводной таблице эти записи теперь рассматриваются как числа.

Как упоминалось ранее, сводные таблицы, использующие несколько полей консолидации, объединяют поля исходных наборов данных (кроме первого поля), превращая их в элементы данных в поле столбца. Поэтому, хотя вы можете определить такие поля, как Directional, как текстовые поля с собственными элементами данных, они больше не будут хранить эти данные. Эти поля будут преобразованы в элементы данных со значениями. В результате поля, которые изначально содержали текст или даты, будут отображать бессмысленные числовые значения в сводной таблице. Обычно лучше скрыть эти поля, чтобы избежать конфликтов. Дополнительные сведения о настройке полей см. в разделе Группировка, сортировка и фильтрация данных в сводной таблице.

Поля страницы

Вы можете управлять только полями страниц непосредственно в сводных таблицах с несколькими диапазонами консолидации. Для создания и определения на каждой странице доступны четыре поля. Вы также можете перетаскивать их в поля строк или столбцов, чтобы добавить новые функции в сводную таблицу. Поле Page1 в перекрестной таблице показано на рис. Используя 7.11, вы можете фильтровать данные по региону. Однако, как показано на рис. 7.12, перетащив Page1 в область строк сводной таблицы, можно создать моментальный снимок данных по региону.

Рис. 7.12. При перетаскивании поля Страница1 в область строк в сводную таблицу добавляется новый слой, который обеспечивает представление всех данных отдельного региона

Рисунок 7.12. Перетаскивание поля Page1 в область строк электронной таблицы добавляет новый слой, который обеспечивает представление всех данных по отдельному региону.

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

Переопределение сводной таблицы

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

Читайте так же:
Как вывести все содержимое ячейки в Excel?

Как создать диаграмму в сводной Google таблице.

При использовании диаграмм и графиков наши данные становятся еще более наглядными. Диаграмму можно добавить в электронную таблицу двумя способами.

Вы можете установить курсор на любую ячейку сводной таблицы, чтобы указать, что это данные, которые вы хотите использовать. В главном меню нажмите Вставка -> Диаграмма. После выбора типа диаграммы вы можете выбрать ее макет. Затем нажмите кнопку Вставить, и диаграмма будет помещена на лист со сводной таблицей.

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

диаграмма в сводной таблице

Существует и второй способ создания диаграммы.

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

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

Если мы вернемся к вопросам, которые задавали ранее, то получим график, который не только показывает объем покупок для каждого покупателя, но и дает представление о том, какой тип шоколада предпочитает покупатель.

анализ данных сводной таблицы Google

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

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

Проверка правильности выставленных коммунальных счетов

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

Для начала мы предлагаем вам собрать тарифы на все ваши коммунальные платежи. Эти цифры варьируются от города к городу.

В качестве примера мы подготовили таблицу, в которой приведены тарифы по Москве:

Тарифы коммунальных платежей.

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

Первый столбец = первый столбец из сводной таблицы. Вторая — формула для расчета дисплея:

= тариф * количество человек / показания счетчиков / площадь

Для удобства рекомендуется сделать промежуточную колонку, в которую будут заноситься показания счетчиков (переменная составляющая).

Сводная таблица тарифов по коммунальным платежам.

Наши формулы относятся к листу с прейскурантом.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector