Меню

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

Построение сводных таблиц в SQL

04.09.2020 | Сергей Дровников, г. Воронеж |

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

Из вышеуказанной таблицы мы хотим получить сводную таблицу вида:

Вариант 1: Использование оператора CASE.

Для замены значения Null на обычный «0» достаточно добавить в конструкцию CASE WHEN оператор ELSE:

Не хватает итогов под таблицей. Для этого мы будем использовать оператор GROUP BY ROLLUP:

А для того, чтобы под колонкой «product» вместо значения «NULL» вывести всем понятное «Total_sum» нам понадобится оператор COALESCE.

Вариант 2: Использование оператора GROUP BY CUBE

Для быстрой группировки данных по операторам и вывода итоговых значений по каждому продукту без перечисления в коде всех операторов удобно использовать оператор GROUP BY CUBE:

Для отображения вместо NULL в колонке operator названия «Total_sum» воспользуемся оператором COALESCE

Вариант 3: Использование оператора разворота таблиц PIVOT

Перед использованием этого оператора нам необходимо получить агрегированную таблицу. Для этого мы будем использовать ранее подготовленную с использованием оператора GROUP BY CUBE таблицу, используя предыдущий фрагмент кода как подзапрос (выделено цветом).

Здесь мы «поворачиваем» таблицу из прошлого запроса, используя агрегатную функцию суммы sum (Summa). При этом заголовки столбцов мы берём из поля operator, а с помощью in («A», «B», «C», “D”, «total_sum») указываем какие конкретно операторы должны быть выведены (total_sum отвечает за столбец с итогами по строкам). При этом заголовки столбцов обязательно берем в двойные кавычки.

Для разворота таблицы в разрезе продуктов по каждому оператору меняем аргумент в операторе PIVOT.

Вариант 4: Динамический SQL

Запрос с PIVOT выглядит короче, чем изначальный с CASE, но названия поставщиков все ещё необходимо вносить вручную. Но что делать, если поставщиков много? Или если их список регулярно обновляется? Хотелось бы выбирать их автоматически. Здесь чистого SQL недостаточно. Он подразумевает статическую типизацию: для создания плана запроса СУБД нужно заранее указать число столбцов. Поэтому синтаксис PIVOT не позволяет использовать подзапрос. Но это ограничение легко обойти с помощью динамического SQL. Для этого названия столбцов необходимо преобразовать в строку формата «элемент_1», «элемент_2», …, «элемент_n», и использовать их в запросе.

Для генерации строки, мы можем использовать оператор STUFF:

Полученную строку включаем в окончательный запрос:

Еще больше статей на тему автоматизации и анализа данных в SQL ищите по ссылке

2021 год

Источник



SQL запросы в Microsoft Excel

SQL в Microsoft Excel

SQL – популярный язык программирования, который применяется при работе с базами данных (БД). Хотя для операций с базами данных в пакете Microsoft Office имеется отдельное приложение — Access, но программа Excel тоже может работать с БД, делая SQL запросы. Давайте узнаем, как различными способами можно сформировать подобный запрос.

Создание SQL запроса в Excel

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

Способ 1: использование надстройки

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

    После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.

Принятие лицензиного соглашение на использование компонента Microsoft NET Framework 4

Загрузка обязательных файлов

Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».

Окно подтверждения согласия на установку надстройки

Затем начинается процедура установки непосредственно самой надстройки.

Установка надстройки

После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».

Закрытие окна установщика надстройки

Окно лицензии надстройки XLTools

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

Окно активации пробного периода надстройки XLTools

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

Активация пробной лицензии надстройки XLTools

После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.

Переход к созданию умной таблицы в Microsoft Excel

Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».

Окно форматирования таблицы в Microsoft Excel

После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.

Наименование таблицы по умолчанию в Microsoft Excel

При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.

Измененное наименование таблицы в Microsoft Excel

После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».

Переход во вкладку XLTools в Microsoft Excel

После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».

Переход в окно выполнения SQL надстройки XLTools в программе Microsoft Excel

Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.

Читайте также:  Все таблицы чемпионатов 2013 2014

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

Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:

  • ORDER BY – сортировка значений;
  • JOIN – объединение таблиц;
  • GROUP BY – группировка значений;
  • SUM – суммирование значений;
  • DISTINCT – удаление дубликатов.

Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

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

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

Окно выполнения SQL запроса надстройкой XLTools в Microsoft Excel

Способ 2: использование встроенных инструментов Excel

Существует также способ создать SQL запрос к выбранному источнику данных с помощью встроенных инструментов Эксель.

    Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».

Переход во вкладку Данные в Microsoft Excel

В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».

Переход в Мастер подключения данных в Microsoft Excel

Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».

Окно Мастера подключения данных в Microsoft Excel

Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».

Окно выбора типа источника Мастера подключения данных в Microsoft Excel

Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».

Окно выбора базы данных в Microsoft Excel

Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».

Окно выбора таблицы базы данных в Microsoft Excel

После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».

Окно сохранения файла подключения данных в Microsoft Excel

  • На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
    • Таблица;
    • Отчёт сводной таблицы;
    • Сводная диаграмма.

    Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.

    После того, как все настройки импорта указаны, жмем на кнопку «OK».

    Окно импорта данных в Microsoft Excel

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

    Переход в окно подключений в Microsoft Excel

    После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.

    Переход в свойства базы данных в Microsoft Excel

    Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».

    Окно свойств подключения в Microsoft Excel

  • После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.
  • Отправка запроса к базе данных в окне подключения к книге в Microsoft Excel

    Способ 3: подключение к серверу SQL Server

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

      Запускаем программу Excel и переходим во вкладку «Данные». После этого щелкаем по кнопке «Из других источников», которая размещается на ленте в блоке инструментов «Получение внешних данных». На этот раз из раскрывшегося списка выбираем вариант «С сервера SQL Server».

    Переход к окну подключения к серверу SQL Server в Microsoft Excel

  • Происходит открытие окна подключения к серверу баз данных. В поле «Имя сервера» указываем наименование того сервера, к которому выполняем подключение. В группе параметров «Учетные сведения» нужно определиться, как именно будет происходить подключение: с использованием проверки подлинности Windows или путем введения имени пользователя и пароля. Выставляем переключатель согласно принятому решению. Если вы выбрали второй вариант, то кроме того в соответствующие поля придется ввести имя пользователя и пароль. После того, как все настройки проведены, жмем на кнопку «Далее». После выполнения этого действия происходит подключение к указанному серверу. Дальнейшие действия по организации запроса к базе данных аналогичны тем, которые мы описывали в предыдущем способе.
  • Окно Мастера подключения данных в программе Microsoft Excel

    Как видим, в Экселе SQL запрос можно организовать, как встроенными инструментами программы, так и при помощи сторонних надстроек. Каждый пользователь может выбрать тот вариант, который удобнее для него и является более подходящим для решения конкретно поставленной задачи. Хотя, возможности надстройки XLTools, в целом, все-таки несколько более продвинутые, чем у встроенных инструментов Excel. Главный же недостаток XLTools заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

    Читайте также:  Классификация нарушений ода таблица

    ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

    Помимо этой статьи, на сайте еще 12001 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

    Источник

    Внешние источники данных для создания сводной таблицы

    Программа Excel является прекрасным средством обработки и анализа данных. По сути, сводные таблицы сами по себе являются доказательством аналитической мощи Excel. Однако, говоря обо всех достоинствах программы, нельзя не упомянуть и об одном существенном упущении. Программа Excel построена на относительно простой платформе управления данными, обладающей тремя недостатками. [1]

    • Размер набора данных сильно влияет на скорость обработки данных в сводной таблице. Это накладывает серьезные ограничения на эффективность использования сводных таблиц как самодостаточных структур. Причина подобного поведения программы — в специфике управления оперативной памятью. Файл при открытии в Excel полностью перемещается в оперативную память для более быстрой обработки и доступа к данным. Но при этом в программе не реализован надежный механизм оптимального управления оперативной памятью при извлечении из нее даже небольшого фрагмента данных. Несмотря на то что в Excel 2013 предусматривается использование до 1 млн. строк и 16 тыс. столбцов, даже средние по размеру наборы данных приводят к значительным задержкам при обработке.
    • Отсутствие реляционной модели данных вынуждает нас использовать «плоские» таблицы, которые хранят избыточные данные и увеличивают вероятность появления ошибок.
    • Отсутствие индексации полей данных в Excel для оптимизации процесса извлечения больших объемов данных.

    Скачать заметку в формате Word или pdf, скачать примеры в формате Excel, скачать базу данных Access в формате архива (по соображениям безопасности провайдер не позволяет размещать файлы в формате .accdb)

    Именно поэтому в серьезных организациях для управления данными используется не Excel, а такие СУБД, как Microsoft Access и SQL Server. Эти СУБД применяются для хранения миллионов записей, которые можно быстро находить и извлекать. При подобном разделении производственных задач формируется уровень управления данными (сама база данных) и уровень приложения (Excel). Трудность заключается в том, чтобы найти наилучший способ извлечения информации из уровня управления данными и передачи ее на уровень приложения для эффективного использования в сводной таблице.

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

    Создание сводных таблиц на основе данных Microsoft Access

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

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

    В Excel 2013 поддерживается удивительно простой способ извлечения данных из файлов Access, не требующий создания двух их копий. Просто запустите Excel и откройте пустую рабочую книгу. Далее перейдите на вкладку ленты Данные и в группе Получение внешних данных щелкните на кнопке Из Access (рис. 1).

    Рис. 1. Получение данных из Access

    Рис. 1. Получение данных из Access

    На экране появится диалоговое окно с просьбой указать базу данных, из которой будет извлекаться информация. Укажите исходную базу данных. (Ссылка на базу данных, используемую в данном примере, приведена в начале заметки; вам потребуется сохранить базу данных на локальном диске). После выбора базы данных на экране появится диалоговое окно, подобное показанному на рис. 2. В нем перечислены все таблицы и запросы, присутствующие в указанной базе данных. В нашем примере выбран запрос Sales By Employee. Щелкните на кнопке ОК.

    Рис. 2. Выберите запрос или таблицу, данные которой нужно проанализировать

    Рис. 2. Выберите запрос или таблицу, данные которой нужно проанализировать

    В диалоговом окне Выбор таблицы имеется столбец Тип. В Access существуют два типа объектов, которые могут импортироваться в Excel: представление (VIEW) и таблица (TABLE). Представления соответствуют запросам Access. В нашем примере Sales_By_Employee — это запрос Access. Таким образом, в сводную таблицу будет импортироваться результат выполнения запроса. Это очень важный аспект обработки данных. Выборку необходимых данных, соответствующих запросу, выполняет программа Access, a Excel всего лишь выполняет анализ выбранных данных.

    Следующим на экране появляется диалоговое окно Импорт данных. В нем указывается формат, в котором будут импортироваться данные (рис. 3). Можно импортировать исходные данные как таблицу, сводную таблицу или сводную таблицу с соответствующей диаграммой. Кроме того, нужно указать Excel, куда именно следует поместить данные. Щелкните ОК. На листе появится панель Поля сводной таблицы. Можно начинать работать с только что созданной сводной таблицей (рис. 4).

    Рис. 3. Установите переключатель Отчет сводной таблицы

    Рис. 3. Установите переключатель Отчет сводной таблицы

    Рис. 4. Сводная таблица готова к использованию

    Рис. 4. Сводная таблица готова к использованию

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

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

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

    Читайте также:  Сводные таблицы excel практика

    Создание сводных таблиц на основе данных SQL Server

    В связи с популяризацией коллективных вычислений в Excel 2013 были серьезно усовершенствованы средства подключения к транзакционным базам данных, таким как SQL Server. С помощью новых, встроенных в Excel инструментов создание сводных таблиц на основе баз данных SQL Server становится предельно простым занятием. Начните с перехода на вкладку ленты Данные. Щелкните на кнопке Из других источников и в раскрывающемся списке выберите команду С сервера SQL Server (рис. 5).

    Рис. 5. Выберите в раскрывающемся списке команду С сервера SQL Server

    Рис. 5. Выберите в раскрывающемся списке команду С сервера SQL Server

    Тем самым вы запустите мастер подключения к данным (рис. 6). С его помощью в Excel настраивается ссылка на внешние данные, расположенные на сервере.

    Рис. 6. Введите регистрационные данные и щелкните на кнопке Далее

    Рис. 6. Введите регистрационные данные и щелкните на кнопке Далее

    В рассматриваемом случае файл примера отсутствует. Здесь проиллюстрирована процедура взаимодействия Excel и SQL Server. Действия, которые вы будете выполнять для подключения к собственной базе данных, полностью повторяют описанные ниже. На первом шаге мастера нужно предоставить Excel регистрационные данные. Как видно на рис. 6, от вас требуется ввести имя сервера, а также имя пользователя и пароль доступа к данным. При вводе регистрационных данных в Windows достаточно установить переключатель Использовать проверку подлинности Windows.

    Далее укажите нужную базу данных, выбрав ее в раскрывающемся меню, в котором перечисляются все базы данных, хранящиеся на соответствующем сервере (рис. 7). В примере выбрана база MyServer66. Выбор этой базы данных приводит к отображению в нижней части окна всех ее таблиц и представлений. Вам осталось только указать анализируемую таблицу или представление, а затем щелкнуть на кнопке Далее.

    Рис. 7. Укажите исходную базу данных, а затем выберите таблицу или представление

    Рис. 7. Укажите исходную базу данных, а затем выберите таблицу или представление

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

    Рис. 8. Описательная информация о создаваемом подключении

    Чаще всего в этом диалоговом окне используются такие параметры.

    • Имя файла. В этом поле можно изменить имя файла с расширением .ode (Office Data Connection), генерируемого с целью хранения параметров создаваемого подключения.
    • Сохранить пароль в файле. Этот флажок, расположенный под полем ввода имени файла, обеспечивает хранение пароля доступа к внешнему источнику в файле, содержащем описание параметров конфигурации подключения. Имейте в виду, что пароль не зашифрован, поэтому любой пользователь может узнать ваш пароль, просто просмотрев файл в текстовом редакторе.
    • Описание. В этом поле вводится краткое описание назначения устанавливаемого подключения.
    • Понятное имя. В качестве понятного (для пользователей) имени обычно используется собственное название внешнего источника данных. Это название должно быть более значимым для вас, чем то, которое дал ему его создатель.

    Завершив ввод всей необходимой информации, щелкните на кнопке Готово. Вы увидите на экране последнее диалоговое окно — Импорт данных (такое же, как на рис. 3). Установите переключатель Отчет сводной таблицы и щелкните ОК, после чего переходите к непосредственному управлению отчетом сводной таблицы.

    Практикум: создание модели данных, включающей несколько внешних таблиц

    Предположим, что в вашем распоряжении имеется база данных Access из примера выше. Вы хотите проанализировать данные из этой базы в Excel. Выполните следующие действия.

    1. Выделите вкладку Данные и перейдите в группу Получение внешних данных. Щелкните на кнопке Из Access (см. рис. 1).

    2. Найдите базу Access и откройте ее. На экране появится диалоговое окно Выбор источника данных. Установите флажок Разрешить выбор нескольких таблиц. Отметьте флажками таблицы, которые будут включаться во внутреннюю модель данных. Щелкните ОК (рис. 9).

    Рис. 9. Отметьте флажками таблицы, которые будут включены во внутреннюю модель данных

    Рис. 9. Отметьте флажками таблицы, которые будут включены во внутреннюю модель данных

    3. На экране появится диалоговое окно Импорт данных. Установите переключатель Отчет сводной таблицы. Щелкните на стрелке раскрывающегося списка, находящейся справа от кнопки Свойства, и снимите флажок Импорт связей между таблицами (рис. 10). Это исключит вероятность появления ошибки в случае некорректной интерпретации связей между таблицами. Это действие требуется в том случае, если вы собираетесь создавать связи между таблицами самостоятельно. Щелкните на кнопке ОК для создания базовой сводной таблицы

    Рис. 10. Снимите флажок Импорт связей между таблицами

    Рис. 10. Снимите флажок Импорт связей между таблицами

    4. Выберите вкладку ленты Данные и в области Работа с данными щелкните на кнопке Отношения. На экране появится диалоговое окно Управление связями. Кликните Создать. Откроется окно Изменение связи (рис. 11). Используя раскрывающиеся списки создайте нужные связи через поля, представляющие собою первичные ключи. В нашем примере – это поле Employee_Number. Щелкните OK, чтобы закрыть окно Изменение связи.Щелкните Закрыть, чтобы закрыть окно Управление связями (рис. 12).

    Рис. 11. Создайте требуемые связи для только что импортированных таблиц

    Рис. 11. Создайте требуемые связи для только что импортированных таблиц

    Рис. 12. Окно Управление связями с только что созданной связью

    Рис. 12. Окно Управление связями с только что созданной связью между таблицами Sales_by_Employeeи Employee_Master

    5. В вашем распоряжении появилась сводная таблица, основанная на внешних данных, которые были импортированы в модель данных. Можно, например, проанализировать продажи по рынкам сбыта и ФИО торговых представителей (рис. 13).

    Рис. 13. Сводная таблица на основе нескольких внешних таблиц

    Рис. 13. Сводная таблица на основе нескольких внешних таблиц

    [1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.

    Источник

    Создаем сводные таблицы на C# в Excel

    Сегодня программируем сводные таблицы на C#. Для этого надо будет иметь заготовленную таблицу с данными (обычную).

    Заготовленная таблица будет иметь данные о телефонных звонках.

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

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

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

    Откроем Excel документ с таблицей:

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

    Excel.Range pivotDestination = sheetPivot.get_Range(«A1», «A1»);

    Объявляем диапазон сводной таблицы:

    Excel.Range all = sheetPivot.get_Range(«A1», «D1000»);

    Пишем код создания сводной таблицы

    Код для создания сводной таблицы из обычной таблицы с данными имеет такой вид:

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

    Далее надо определить, какие поля будут отвечать за значения, за столбцы и строки.

    Для этого создаем экземпляры полей на основе заголовков столбцов таблицы:

    Создаем сводную таблицу без исходной таблицы с данными

    Описываю, как создать сводную таблицу на основе SQL-запроса к БД.

    Для этого понадобится библиотека ADODB (adodb.dll можно скачать в интернете).

    Подключим adodb.dll к проекту:

    Объявим строку подключения (драйвер SQLOLEDB.1):

    Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User Catalog=DBName;Data Source=serverNameOrIp

    Далее пишем код для работы со сводной таблицей:

    Источник

    Adblock
    detector