Создание сводной таблицы на основе внешнего источника данных (на примере MS Access) - Сводные таблицы - Эффективная работа в Excel - Статьи об Excel

Главная » Статьи » Эффективная работа в Excel » Сводные таблицы

Создание сводной таблицы на основе внешнего источника данных (на примере MS Access)

Прежде всего, следует разобраться, как создать подключение к внешнему источнику данных. Это можно сделать несколькими способами Раз речь идет о сводных таблицах, то логично использовать Мастер сводных таблиц. В Excel 2003 Мастер запускается сразу при попытке создания сводной таблицы через меню Данные -- Сводная таблица. Начиная с Excel 2007 для использования Мастера сводных таблиц, нужно вынести на Панель быстрого доступа одноимённую кнопку. На первом шаге Мастера сводных таблиц выбираем вариант Во внешнем источнике данных -- Далее На втором шаге Мастера для создания нового подключения нажимаем кнопку Получить данные... После нажатия кнопки Получить данные... в открывшемся диалоговом окне предлагается выбрать источник данных для будущей сводной таблицы. Дальнейшая работа будет рассмотрена на примере файла Access. Если Вы импортируете данные из файла .mdb, то следует выбрать вариант База данных MS Access, а если более поздних версий .accdb, - то MS Access Database. Флажок Использовать Мастер запросов должен быть установлен После нажатия ОК в раскрывшемся диалоговом окне выбираем файл-источник данных В качестве примера используем простую базу данных Отпуск товара со склада, которая имеет следующую схему данных После выбора файла и нажатия ОК получаем следующее окно, в котором предлагается выбрать таблицы, запросы и, соответственно, поля, которые нужны для будущей сводной таблицы В левой части окна мы видим перечень имеющихся в базе данных таблиц и запросов. В правой части будут отображаться поля, выбранные для сводной таблицы. Перенести поле из левой части в правую можно кнопкой >. Причём, если слева выделена таблица или запрос, то при нажатии кнопки > перенесутся все поля из этой таблицы. Если все поля не нужны, нажимаем кнопку со значком + слева от названия таблицы, раскрывается список полей, и мы можем переносить поля по одному. Кнопки < и << предназначены для обратного перемещения: < - одно выделенное поле, << - все поля сразу. Предположим, мы хотим отобразить в сводной таблице информацию о заказчиках и количестве отпущенного им товара. То есть нам нужны поля Наименование заказчика (таблица Заказчик), Наименование товара (таблица Товар), Количество товара (таблица Заявки). Добавим для пущей информативности ещё поле Цена (таблица Товар) и Дата отпуска (таблица Заявки). Порядок следования полей не имеет значения, поэтому берем первую по счёту таблицу Заказчик, нажимаем кнопку +, в списке полей выделяем Наименование заказчика и нажимаем кнопку >. Дальше раскрываем список полей таблицы Заявки и с помощью той же кнопки > переносим нужные поля в правую часть окна. Аналогично действуем с таблицей Товар. В итоге окно должно выглядеть так Следует обратить внимание, что если слева выделено поле, то при нажатии кнопки Просмотр можно увидеть, какие значения это поле содержит. Нажимаем кнопку Далее и переходим к следующему шагу, на котором можно задать критерии отбора для полей Критерии отбора очень похожи на условия фильтра Excel и могут объединяться как через логическое И, так и через ИЛИ. Критерии можно не задавать, тогда в сводную таблицу попадут все значения. На следующем шаге можно задать сортировку как простую, так и многоуровневую И, наконец, после нажатия кнопки Далее мы видим заключительное окно, завершающее процесс формирования запроса Здесь возможны два варианта: первый возвращает нас на лист Excel в Мастер сводных таблиц (мы ведь всё ещё на втором шаге), а второй вариант открывает окно MS Query, где можно задать дополнительные условия отбора, добавить таблицы или запросы, а также редактировать SQL запрос вручную. Остановимся на первом варианте, нажимаем Готово и видим окно Мастера сводных таблиц с информацией, что данные получены Нажимаем Далее и на третьем шаге Мастера задаём место размещения сводной таблицы, нажимаем Готово. Дальше работаем со сводной таблицей, как обычно: размещаем поля по строкам и столбцам и получаем суммарные итоги А вот теперь начинается самое интересное. Как быть, если Вы "забыли" (не учли, не подумали, изменилась задача) включить некоторые поля в запрос при создании подключения? Можно ли добавить поля к уже существующему запросу? Остановимся на этом подробнее. Пример 1. Предположим, что мы хотим в сводной таблице видеть, кроме всего прочего, еще название поставщика. Наша задача добавить поле Наименование поставщика из таблицы Поставщик в сводную таблицу. Для этого придется вернуться к этапу Получить данные: курсор ставим в сводную таблицу и запускаем Мастер сводных таблиц. Так как активна сводная таблица, попадаем на третий шаг, где нажимаем кнопку Назад В следующем окне нажимаем Получить данные В раскрывшемся диалоговом окне нажимаем + слева от таблицы Поставщик, выделяем поле Наименование поставщика и нажимаем кнопку > Нажимаем Далее. Тут возможны два исхода: если в базе данных Access поля связи, по которым таблица Поставщик связана с таблицей Товар, имеют одинаковые названия, то данные успешно передаются и после нажатия кнопки Готово поле Наименование поставщика попадает в сводную таблицу, и нам остаётся только добавить его в область строк. Если же поля связи имеют разные названия (как в нашем примере, см. схему данных выше), то мы получим такое предупреждение Не стоит его пугаться, просто мы должны будем вручную связать разноимённые поля. Нажимаем ОК и переходим в окно редактора MS Query Здесь мы видим таблицы, которые участвуют в запросе. Обращаем внимание, что поля связи таблиц Заказчик, Заявка и Товар имеют одинаковые названия, поэтому MS Query связал их автоматически. Если бы это было не так, мы бы столкнулись с данным предупреждением гораздо раньше. Чтобы связать поля Поставщик и КодПоставщика, подведем мышку к одному из этих полей, зажмём левую кнопку и протянем до другого поля. Таким образом, поля оказываются связанными. Для того, чтобы новое поле попало в нашу сводную таблицу, в меню Файл выбираем команду Вернуть результат в MS Excel и Готово. Замечание. В некоторых случаях удобнее в исходной базе данных создать запрос, включающий необходимые поля из разных таблиц, а уже затем использовать этот запрос в качестве источника данных для сводной таблицы. Пример 2. Предположим, что мы хотим в сводной таблице помимо цены и количества товара видеть стоимость. То есть результат умножения цены на количество. Вычисляемое поле можно создать в MS Query. Чтобы вернуться в окно MS Query, установим курсор в сводную таблицу -- Мастер сводных таблиц -- Назад -- Получить данные -- Отмена. После этих действий должно появиться окно Нажимаем Да и попадаем в окно MS Query. В этом окне в свободном столбце вручную вводим формулу *Цена[Количество товара]. Названия полей должны в точности совпадать с названиями из базы данных. Если имя поля состоит из нескольких слов, то его надо взять в квадратные скобки. После нажатия Enter мы получим вычисленные значения стоимости. Если мы хотим, чтобы поле имело своё название, следует дважды щёлкнуть мышкой по заголовку столбца и в раскрывшемся диалоговом окне ввести новое имя, например, Стоимость, нажать ОК Далее в меню Файл выбираем команду Вернуть данные в MS Excel и Готово*. Замечание.* Те, кто знаком с SQL, могут писать и корректировать запросы сразу на этом языке. Для этого на Панели инструментов MS Query нужно нажать кнопку SQL и в открывшемся диалоговом окне составить SQL-запрос. В частности, для добавления вычисляемого поля Стоимость, достаточно было добавить в перечень полей *Цена`Количество товара` AS 'Стоимость' Те, кто при прочтении статьи дошёл до этого места, могут возразить, что вычисление стоимости можно произвести непосредственно в сводной таблице, создав вычисляемое поле. Давайте сравним результаты. Создадим в нашей сводной таблице вычисляемое поле Стоимость1, рассчитанное по формуле = Цена* 'Количество товара' На рисунке жёлтым цветом выделены расхождения в расчетах стоимости. Получились они из-за особенностей расчёта вычисляемых полей, созданных непосредственно в сводной таблице, при группировке. Также неверно подсчитан общий итог. Избежать этих ошибок встроенными средствами сводной таблицы не удаётся. Приложение. Назначение кнопок на Панели инструментов MS Query 1 - Создать запрос 2 - Открыть запрос, сохраненный ранее в формате .dqy 3 - Сохранить запрос в формате .dqy 4 - Вернуть данные в MS Excel 5 - Текст запроса на SQL 6 - Показать/Скрыть область таблиц 7 - Показать/Скрыть область условий. Условия используются для отбора данных по критерию. Например, на рисунке показано условие отбора по наименованию заказчика. Добавлять условия можно через диалоговое окно, которое открывается при выборе пункта меню Условия -- Добавить условие**. Напомню, что добавить условие отбора можно также на этапе получения данных 8 - Добавить таблицу. Используется, если требуется к имеющимся полям добавить новые из других таблиц или запросов 9 - Фильтр по выделенному 10 - Групповые операции. Выполняется группировка по одинаковым значениям полей, расположенных левее выделенного поля. Вид операции изменяется циклически при нажатии кнопки 11 - Сортировка по возрастанию. Напомню, что способ сортировки можно задать также на этапе получения данных 12 - Сортировка по убыванию 13 - Выполнить запрос 14 - Автоматическое выполнение запроса при изменении параметров 15 - Справка по MS Query

1 2 3 4 5 Категория: Сводные таблицы \| Добавил: Pelena (18.11.2013)

Просмотров: 52548 \| Теги: связь данных, SQL, Access, Сводные таблицы, pivot table, внешние данные, создание сводной таблицы \| Рейтинг: 5.0/3