Newcomposers.ru

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

Объединить две сводные таблицы в excel

Объединить две сводные таблицы в excel

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы , Строки заказов , Товары , Клиенты .

Таблица Строк заказов:

Исходные таблицы оформлены в виде умных таблиц: Orders , OrderLines , Goods и Clients .

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа , таблицы Orders и Clients — по полю ID_клиента , таблицы OrderLines и Goods — по полю ID_товара .

Скачать пример

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

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

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

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

Нажмём её. Появится такой вопрос:

Отвечаем Да и видим, что в список полей добавились все наши таблицы:

Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ.

Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines . Обратите внимание, что Excel умеет создавать связь типа » один к одному » или » один ко многим «. Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.

Аналогично создаём другие связи.


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

Чтобы видеть больше полей на панеле Поля сводной таблицы , можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:

Результат будет таким:

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

Как сделать сводную таблицу из нескольких листов/диапазонов Excel

Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).

Такую таблицу можно выполнить двумя разными способами.

  1. Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
  2. Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.

Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.

Шаг первый.

Необходимо добавить в ленту инструментов кнопку мастера создания сводных таблиц и диаграмм.

Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

Настройка ленты

либо войти во вкладку

«Файл» => «Параметры» => «Настройка ленты».

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

Из списка выбираем «Мастер сводных таблиц и диаграмм»

Добавление мастера сводных таблиц

В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».

Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».

После нажмите «Ок».

Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».

Мастер сводных на панели

Шаг второй. Построение сводной таблицы из нескольких источников данных.

  • Кликнуть по кнопке мастера построения сводных таблиц.
  • На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

Консолидация диапазонов
Во втором окне выбрать «Создать одно поле страницы»

Поле сводной
В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).

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

На существующий лист

  • Нажать кнопку «Готово».
  • Второй способ.

    Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

    Данный способ заключается в использовании запроса надстройки Power Query.

    О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

    Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

    Шаг первый.

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

    Шаг два.

    Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

    Power Query из таблицы

    Шаг три.

    Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

    Слияние запросов

    Шаг четыре.

    Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

    Закрыть и загрузить

    Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

    Объединить сводные таблицы в Excel

    Helen Bradley рассказывает, как группировать данные в сводной таблице Excel для их дальнейшего обобщения и анализа.

      Группируем по значению

    Группируем по значению

    Представьте себе такую ситуацию: Вы анализируете подборку счетов, собранных в сводной таблице. Вы можете обобщать счета, объединяя их в группы по 5 — 10 или даже 100 счетов. Давайте рассмотрим пример, где идентификатор строки OrderID — это числовое поле. В данный момент каждому счёту соответствует отдельная строка, и результат получается довольно громоздким. Мы можем сгруппировать эти данные, чтобы упростить таблицу.

    В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно.

    Для этого кликните правой кнопкой мыши по одному из OrderID данной сводной таблицы и в появившемся меню выберите Group (Группировать). Так как OrderID это числовое поле, то диалоговое окно уже будет подготовлено для численной группировки с полями Starting At (Начиная с), Ending At (По) и By (С шагом). В данной ситуации Excel предлагает Вам группировать элементы по 10 штук.

    По умолчанию Excel предлагает в качестве значения Starting At (Начиная с) первый элемент OrderID: 10248. Тем не менее, Вы можете настроить точнее или создать другую группировку, установив собственное значение параметра Starting At (Начиная с). Чтобы начать с элемента 10240, введите это значение в поле Starting At (Начиная с) — Вы можете установить любое стартовое значение, даже такое, которого нет в данных.

    Мы настраиваем параметры группировки данных — они будут сгруппированы по полю OrderID по десять последовательно пронумерованных заказов.

    Итак, мы настроили значение Starting At (Начиная с), оставили значение Ending At (По) таким, как предложил Excel, и оставили значение By (С шагом) равным 10, поскольку оно нам подходит. Вы можете делать группы меньше или больше, изменяя это значение — например: 5, 20, 50 или 100. Теперь жмём ОК. Данные будут сгруппированы по значению OrderID в более удобные для обработки группы.

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

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

    Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Чтобы сгруппировать информацию по датам, кликните правой кнопкой мыши по дате в столбце или строке Вашей сводной таблицы и выберите Group (Группировать). Вы можете выбрать шаг группировки — Seconds (Секунды), Minutes (Минуты), Hours (Часы), Days (Дни), Months (Месяцы), Quarters (Кварталы) или Years (Годы), и установить начальный и конечный момент времени. Для группировки по годам, кварталам и месяцам интервал зафиксирован равным 1, а для группировки по дням Вы можете установить собственный интервал, например, равный 7 дням, чтобы сгруппировать данные по неделям. Для этого выберите Days (Дни) как шаг группировки, установите количество дней равным 7, укажите для параметра Starting At (Начиная с) дату, которая выпадает на начало недели, и нажмите ОК. Информация будет сгруппирована по неделям.

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

    Группируем данные вручную

    Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Иногда группа становится группой просто потому, что Вы так решили. В этом случае можно создать свою собственную группировку. Откройте лист со сводной таблицей, кликните по первому элементу данных, который нужно включить в группу и далее, с нажатой клавишей Ctrl, последовательно щелкните по всем элементам, которые также нужно включить в эту группу. Затем на вкладке PivotTable / Options (Работа со сводными таблицами / Анализ) нажмите Group Selection (Группировка по выделенному) и элементы будут добавлены в новую группу. Таким же образом Вы можете выделить другие элементы и создать еще одну группу.

    Выделив несколько полей, Вы можете объединить их в группы так, как будет удобно.

    При помощи этого инструмента Вы можете группировать данные в небольшие подборки, с которыми удобно работать. Так как по умолчанию группы названы именами Group 1 (Группа 1) и Group 2 (Группа 2), Вам придётся самостоятельно переименовать их. Для этого кликните ячейку, содержащую имя группы, и в строке формул введите более подходящее имя для группы.

    После того, как группы созданы, Вы вправе переименовывать их, как угодно.

    Вы можете развернуть или свернуть группу, нажав символы плюс или минус (+/-) слева от имени группы.

    Исправляем ошибки

    Иногда, пытаясь сгруппировать выделенные данные, Вы получите сообщение об ошибке, уведомляющее, что выбранные данные не могут быть объединены в группу. Есть целый ряд причин, почему это может произойти. Одна из причин: если Вы пытаетесь создать собственную группировку, то должны выделить два или более элемента для создания группы — нельзя создать группу из одной записи.

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

    Детализация групп

    Одним из преимуществ, которые даёт группировка данных, является то, что Вы можете извлечь подмножество сгруппированных данных на новый лист. Чтобы сделать это, выберите группу, и дважды щелкните по ячейке с интересующим Вас итоговым значением. Данные, из которых было собрано это итоговое значение, будут извлечены на новый лист текущей рабочей книги Excel.

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

    Группировка и несколько диапазонов консолидации

    Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов, например, из данных на разных листах рабочей книги. Для этого в Excel 2007 и 2010 на Панель быстрого доступа необходимо добавить команду PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм).

    Чтобы сделать это, кликните выпадающее меню Панели быстрого доступа, выберите пункт More Commands (Другие команды), в открывшемся диалоговом окне установите группу All Commands (Все команды) и в списке команд найдите PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм). Нажмите кнопку Add (Добавить), чтобы добавить кнопку на Панель быстрого доступа.

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

    Эта команда исключена из меню Excel 2010, но Вы можете добавить её на Панель быстрого доступа самостоятельно.

    Создайте новый лист в Вашей рабочей книге Excel, кликните только что добавленную кнопку PivotTable and PivotChart Wizard (Мастер сводных таблиц и диаграмм), выберите пункты Multiple Consolidation Ranges (В нескольких диапазонах консолидации) и PivotTable (Сводная таблица), а затем нажмите Next (Далее). Выберите I will create the page fields (Создать поля страницы) и снова нажмите Next (Далее). Теперь Вам нужно указать каждый из диапазонов. Зайдите на первый лист, выберите все данные, включая заголовки таблицы, и нажмите Add (Добавить). Повторите шаги, чтобы добавить информацию со следующего листа и так далее, пока не добавите данные со всех листов, которые планировали использовать в сводной таблице.

    Мастер сводных таблиц и диаграмм позволяет объединять сразу несколько диапазонов в одной сводной таблице.

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

    Далее укажите количество полей, которые нужно добавить в сводную таблицу — вероятнее всего, Вам понадобится одно или два. Теперь выберите один из диапазонов в поле немного выше и введите для него имя группы. Затем выберите следующий диапазон и проделайте для него те же самые шаги. И так далее для всех диапазонов. Если имя группы уже существует, Вы можете не вводить его, а выбрать из выпадающего списка.

    Так, например, диапазон, содержащий даты, может быть частью группы, которая разделяет месяцы по кварталам, а также частью группы, которая разделяет месяцы по сезонам: Winter (Зима), Spring (Весна), Summer (Лето) и Fall (Осень). Группировки могут быть по любому признаку, который Вы посчитаете подходящим для своих данных. Нажмите Next (Далее), кликните по ячейке, в которой должен будет находиться верхний левый угол сводной таблицы и нажмите Finish (Готово).

    В Мастере сводных таблиц и диаграмм Вы можете связать диапазоны со страницами, как показано на рисунке выше — Quarters (Кварталы) и Seasons (Сезоны).

    Сводная таблица будет сформирована автоматически, группы будут созданы как поля Report Filter (Фильтры) в списке полей сводной таблицы, но Вы, если пожелаете, можете перетащить эти поля в область Row Labels (Строки) или Column Labels (Колонны).

    На рисунке выше показано: мы переместили страницу Page 2 (Сезоны) из области Report Filter (Фильтры) в область Row Labels (Строки).

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

    Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.ablebits.com/office-addins-blog/2011/10/06/excel-group-pivottable-data/ Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel

    Как объединить две таблицы Excel по частичному совпадению ячеек

    Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.

    Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

    Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345” и “12345-новый_суффикс“. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

    И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания” в одной таблице может превратиться в “ЗАО «Некоторая Компания»” в другой таблице, а “Новая Компания (бывшая Некоторая Компания)” и “Старая Компания” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

    Выход есть всегда, читайте далее и Вы узнаете решение!

    Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.

    Выберите подходящий пример, чтобы сразу перейти к нужному решению:

    Ключевой столбец в одной из таблиц содержит дополнительные символы

    Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

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

    Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:

    • Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
    • Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
    • Даём столбцу имя SKU helper.
    • Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

    Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а 5 – количество символов, которое будет извлечено.

  • Копируем эту формулу во все ячейки нового столбца.
  • Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

    Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

    Другие формулы

    • Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:

    =ПРАВСИМВ(A2;6)
    =RIGHT(A2,6)
    Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

    =ПСТР(A2;8;4)
    =MID(A2,8,4)
    Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:

    Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.

    Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

    Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

    Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

    В ячейке C2 запишем такую формулу:

    Здесь A2 – это адрес ячейки, содержащей код группы; символ “” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

    Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

    Данные в ключевых столбцах не совпадают

    Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

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

    Хорошая новость: Это придётся сделать только один раз, и получившуюся вспомогательную таблицу можно будет сохранить для дальнейшего использования. Далее Вы сможете объединять эти таблицы автоматически и сэкономить таким образом массу времени

    1. Создаём вспомогательную таблицу для поиска.

    Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

    Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

    В результате мы имеем вот такую таблицу:

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

    В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

    Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

    Столбец Supp.SKU заполняется оригинальными кодами производителя.

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

    3. Переносим данные из таблицы поиска в главную таблицу

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

    При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp.SKU.

    Вот пример обновлённых данных в столбце Wholesale Price:

    Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

    Использование нескольких таблиц для создания сводной таблицы

    Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:

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

    Чтобы объединить несколько таблиц в списке полей сводной таблицы:

    Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.

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

    Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.

    Убедитесь, что вам известны имя сервера, имя базы данных и учетные данные, необходимые для подключения к SQL Server. Все необходимые сведения можно получить у администратора базы данных.

    Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.

    В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.

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

    Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.

    Выберите необходимые для работы таблицы вручную, если вы знаете, какие именно нужны вам. Или же выберите одну или две, а затем щелкните Выбор связанных таблиц для автовыбора таблиц, связанных с уже указанными.

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

    В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.

    Нажмите кнопку ОК, чтобы начать импорт и заполнить список полей.

    Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.

    Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».

    Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.

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

    Работа с другими типами баз данных

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

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

    Импорт таблиц из других источников

    Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.

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

    Использование модели данных для создания новой сводной таблицы

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

    Щелкните любую ячейку на листе.

    Выберите Вставка > Сводная таблица.

    В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.

    Выберите вариант Выбрать подключение.

    На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.

    Нажмите кнопку Открыть, а затем — ОК, чтобы отобразить список полей, содержащий все таблицы в модели.

    Читать еще:  Вставка раскрывающегося списка в excel
    Ссылка на основную публикацию
    Adblock
    detector