Создание сводной таблицы для анализа данных листа
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций. Сводные таблицы работают немного по-разному в зависимости от платформы, используемой для запуска Excel.
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Данные должны быть упорядочены по столбцам с одной строкой заголовка. Дополнительные сведения см. в разделе Советы и рекомендации по формату данных.
На вкладке Вставка нажмите кнопку Сводная таблица.
При этом создается сводная таблица на основе существующей таблицы или диапазона.
Примечание: Если выбрать Добавить эти данные в модель данных, таблица или диапазон, используемые для этой сводной таблицы, добавляются в модель данных книги. Дополнительные сведения.
Выберите место размещения отчета сводной таблицы. Выберите Новый лист, чтобы разместить сводную таблицу на новом листе или существующем листе, а затем выберите место, где будет отображаться новая сводная таблица.
Нажмите кнопку ОК.
Щелкнув стрелку вниз на кнопке, можно выбрать из других возможных источников для сводной таблицы. Помимо использования существующей таблицы или диапазона, для заполнения сводной таблицы можно выбрать еще три источника.
Примечание: В зависимости от ит-параметров вашей организации в списке может отображаться имя вашей организации. Например, «Из Power BI (Майкрософт)».
Получение из внешнего источника данных
Получение из модели данных
Используйте этот параметр, если книга содержит модель данных и вы хотите создать сводную таблицу из нескольких таблиц, улучшить сводную таблицу с помощью настраиваемых мер или работать с очень большими наборами данных.
Получение из Power BI
Используйте этот параметр, если ваша организация использует Power BI и вы хотите обнаружить и подключиться к рекомендуемых облачных наборах данных, к которым у вас есть доступ.
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля добавляются в области по умолчанию: нечисловые поля добавляются в строки, иерархии даты и времени добавляются в столбцы, а числовые поля добавляются в значения.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить только одну сводную таблицу, щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы, а затем выберите Обновить. Если у вас несколько сводных таблиц, сначала выберите любую ячейку в любой сводной таблице, а затем на ленте перейдите к разделу Анализ сводной таблицы > щелкните стрелку под кнопкой Обновить , а затем выберите Обновить все.
По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выберите параметр Параметры поля значения .
Затем измените функцию в разделе Операция. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. Если выбран параметр Числовой формат, можно изменить числовой формат для всего поля.
Совет: Так как при изменении вычисления в разделе Суммирование значений по изменяется имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы до завершения настройки сводной таблицы. Один из способов заключается в использовании функции Поиска & Замены (CTRL+H) >Найти то, что > «Сумма«, а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Открыв диалоговое окно Параметр поля значений , вы можете сделать выбор на вкладке Показать значения как .
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем задайте параметры Суммировать значения по и Показать значения как для каждого из них.
Выберите таблицу или диапазон данных на листе и выберите Вставить > сводную таблицу , чтобы открыть панель Вставка сводной таблицы.
Вы можете вручную создать собственную сводную таблицу или выбрать рекомендуемую сводную таблицу для создания. Выполните одно из указанных ниже действий.
В карта Создание собственной сводной таблицы выберите Новый лист или Существующий лист, чтобы выбрать назначение сводной таблицы.
В рекомендуемой сводной таблице выберите Новый лист или Существующий лист, чтобы выбрать назначение сводной таблицы.
Примечание: Рекомендуемые сводные таблицы доступны только подписчикам Microsoft 365.
Вы можете изменить источникданных для данных сводной таблицы при его создании.
В области Вставка сводной таблицы выберите текстовое поле в разделе Источник. При изменении источника карточки в области будут недоступны.
Выберите данные в сетке или введите диапазон в текстовое поле.
Нажмите клавишу ВВОД на клавиатуре или кнопку, чтобы подтвердить выбор. Панель обновляется с новыми рекомендуемыми сводных таблицами на основе нового источника данных.
Получение из Power BI
Используйте этот параметр, если ваша организация использует Power BI и вы хотите обнаружить и подключиться к рекомендуемых облачных наборах данных, к которым у вас есть доступ.
В области Поля сводной таблицы выберите поле проверка для любого поля, которое вы хотите добавить в сводную таблицу.
По умолчанию нечисловые поля добавляются в область Строк , поля даты и времени добавляются в область Столбцы , а числовые — в область Значения .
Вы также можете вручную перетащить любой доступный элемент в любое из полей сводной таблицы . Если элемент в сводной таблице больше не нужен, перетащите его из списка или снимите флажок.
По умолчанию поля сводной таблицы в области Значения отображаются в виде СУММ. Если Excel интерпретирует данные как текст, они отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений.
Измените вычисление по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав параметр Суммировать значения по .
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Щелкните правой кнопкой мыши любое значение в столбце, для который нужно отобразить значение. В меню выберите Показать значения как . Отобразится список доступных значений.
Сделайте выбор из списка.
Чтобы отобразить в процентах от родительского итога, наведите указатель мыши на этот элемент в списке и выберите родительское поле, которое вы хотите использовать в качестве основы вычисления.
При добавлении новых данных в источник данных сводной таблицы необходимо обновить все сводные таблицы, созданные на основе этого источника данных. Щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы и выберите обновить.
Если вы создали сводную таблицу и решили, что она больше не нужна, выберите весь диапазон сводной таблицы и нажмите кнопку Удалить. Это не повлияет на другие данные, сводные таблицы или диаграммы вокруг них. Если сводная таблица находится на отдельном листе, где нет других данных, которые вы хотите сохранить, удаление листа — это быстрый способ удаления сводной таблицы.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. В идеале можно использовать таблицу Excel.
Таблицы являются отличным источником данных сводной таблицы, так как строки, добавленные в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы включаются в список Полей сводной таблицы. В противном случае необходимо либо изменить исходные данные для сводной таблицы, либо использовать динамическую именованную формулу диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы можно просмотреть различные ориентации и изменить порядок полей для достижения желаемых результатов. Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
Выберите ячейку в диапазоне исходных данных или таблицы.
На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.
Excel анализирует данные и предоставляет несколько вариантов, как в этом примере с использованием данных о расходах домашних хозяйств.
Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создает сводную таблицу на новом листе и отображает список Полей сводной таблицы .
Выберите ячейку в диапазоне исходных данных или таблицы.
На вкладке Вставка нажмите кнопку Сводная таблица.
В Excel отображается диалоговое окно Создание сводной таблицы с выбранным именем диапазона или таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
Нажмите кнопку ОК, и Excel создаст пустую сводную таблицу и отобразит список Полей сводной таблицы .
Список полей сводной таблицы
В области Имя поля вверху выберите поле проверка для любого поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк , поля даты и времени добавляются в область столбца , а числовые — в область Значений . Вы также можете вручную перетащить любой доступный элемент в любое из полей сводной таблицы. Если элемент в сводной таблице больше не нужен, просто перетащите его из списка Поля или снимите флажок. Возможность переупорядочения элементов поля — одна из функций сводной таблицы, которая упрощает изменение ее внешнего вида.
Список полей сводной таблицы
Суммировать по
По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выбрав параметр Параметры поля .
Затем измените функцию в разделе Суммировать по. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. При выборе параметра Число. можно изменить формат чисел для всего поля.
Совет: Так как изменение вычисления в разделе Суммирование по изменяет имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы, пока не завершите настройку сводной таблицы. Один из способов заключается в том, чтобы выбрать Заменить (в меню Правка) >Найти, что > «Сумма«, а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Открыв диалоговое окно Параметры поля , вы можете сделать выбор на вкладке Показать данные как .
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните правой кнопкой мыши значение и выберите Параметры поля, а затем задайте параметры Суммировать по и Показать данные для каждого из них.
При добавлении новых данных в источник данных сводной таблицы необходимо обновить все сводные таблицы, созданные на основе этого источника данных. Чтобы обновить только одну сводную таблицу, щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы, а затем выберите Обновить. Если у вас несколько сводных таблиц, сначала выберите любую ячейку в любой сводной таблице, а затем на ленте перейдите к разделу Анализ сводной таблицы > щелкните стрелку под кнопкой Обновить , а затем выберите Обновить все.
Если вы создали сводную таблицу и решили, что она больше не нужна, можно просто выбрать весь диапазон сводной таблицы, а затем нажать кнопку Удалить. Это не влияет на другие данные, сводные таблицы или диаграммы вокруг него. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Советы и рекомендации по форматированию данных
Используйте чистые табличные данные для достижения наилучших результатов.
Упорядочение данных по столбцам, а не по строкам.
Убедитесь, что все столбцы имеют заголовки с одной строкой уникальных, непустых меток для каждого столбца. Избегайте двойных строк заголовков или объединенных ячеек.
Отформатируйте данные как таблицу Excel (выберите в любом месте данных, а затем на ленте выберите Вставить > таблицу ).
Если у вас есть сложные или вложенные данные, используйте Power Query для их преобразования (например, для отмены сворачивания данных), чтобы они были упорядочены по столбцам с одной строкой заголовка.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Рекомендации сводной таблицы являются частью работы с подключением в Microsoft 365 и анализируют данные с помощью служб искусственного интеллекта. Если вы решите отказаться от работы с подключением в Microsoft 365, данные не будут отправляться в службу искусственного интеллекта, и вы не сможете использовать рекомендации сводной таблицы. Дополнительные сведения см. в заявлении о конфиденциальности Майкрософт.
Как сделать сводные таблицы в Excel: пошаговая инструкция со скриншотами
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Зачем нужны сводные таблицы и когда их используют
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и ФИО продавца.
В конце квартала планируется выдача премий. Нужно проанализировать, кто принёс больше прибыли салону. Для этого нужно сгруппировать все проданные автомобили под каждым менеджером, рассчитать суммы продаж и определить итоговый процент продаж за квартал.
Разберёмся пошагово, как это сделать с помощью сводной таблицы.
Шаг 1
Создаём сводную таблицу
Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:
- у каждого столбца исходной таблицы есть заголовок;
- в каждом столбце применяется только один формат — текст, число, дата;
- нет пустых ячеек и строк.
Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».
Появляется диалоговое окно. В нём нужно заполнить два значения:
- диапазон исходной таблицы, чтобы сводная могла забрать оттуда все данные;
- лист, куда она перенесёт эти данные для дальнейшей обработки.
В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».
Excel создал новый лист. Для удобства можно сразу переименовать его.
Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.
Шаг 2
Настраиваем сводную таблицу и получаем результат
В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».
Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:
-
«Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Настроить сводную таблицу можно двумя способами:
- Поставить галочку напротив нужного поля — тогда Excel сам решит, где нужно разместить это значение в сводной таблице, и сразу заберёт его туда.
- Выбрать необходимые для сводной таблицы поля из перечня и перетянуть их в нужную область вручную.
Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.
В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.
Таблица получится наглядной, если фамилии менеджеров мы расположим построчно. Находим в верхней части панели поле «Продавец», зажимаем его мышкой и перетягиваем в область «Строки».
После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.
Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».
В левую часть листа добавился второй блок. При этом сводная таблица сама сгруппировала все автомобили по менеджерам, которые их продали.
Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.
Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».
Теперь мы видим, какие автомобили продал каждый менеджер, сколько и по какой цене, — сводная таблица самостоятельно сгруппировала всю эту информацию. Более того, напротив фамилий менеджеров можно посмотреть, сколько всего автомобилей они продали за квартал и сколько денег принесли автосалону.
По такому же принципу можно добавлять другие поля в необходимые области и удалять их оттуда — любой срез информации настроится автоматически. В нашем примере внесённых данных в сводной таблице будет достаточно. Ниже рассмотрим, как настроить фильтры для неё.
Шаг 3
Настраиваем фильтры сводной таблицы
Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».
В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.
Для примера отфильтруем данные по году выпуска: настроим фильтр так, чтобы сводная таблица показала только проданные авто 2017 года.
В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:
В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.
Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.
Фильтры можно выбирать и удалять как удобно — в зависимости от того, какую информацию вы хотите увидеть в сводной таблице.
Шаг 4
Проводим дополнительные вычисления
Сейчас в нашей сводной таблице все продажи менеджеров отображаются в рублях. Предположим, нам нужно понять, каков процент продаж каждого продавца в общем объёме. Можно рассчитать это вручную, а можно воспользоваться дополнениями сводных таблиц.
Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».
Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.
Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.
Чтобы снова раскрыть данные об автомобилях — нажимаем +.
Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».
Шаг 5
Обновляем данные сводной таблицы
Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.
В сводную таблицу эти данные самостоятельно не добавятся — изменился диапазон исходной таблицы. Поэтому нужно поменять первоначальные параметры.
Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».
Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».
После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.
Когда в исходной таблице нужно изменить информацию в рамках текущего диапазона, данные в сводной таблице автоматически не изменятся. Нужно будет обновить их вручную.
Например, поменяем цены двух автомобилей в таблице с продажами.
Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».
Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».
Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:
Другие материалы Skillbox Media для менеджеров
- Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
- Статья с разбором диаграммы Ганта — что должен знать каждый менеджер
- Подборка советов, как превратить хороший проект в великий, из книги Коллинза Good to Great
- Рассказ о модели VUCA и о том, как она помогает процветать в хаосе
- Подборка одиннадцати типичных ошибок при создании презентации
Исходная таблица — данные, которые сводная таблица собирает, группирует и формирует в отчёт.
Метод моделирования «Свод данных»
Свод данных ( Data Vault) как метод моделирования данных для ХД был предложен в конце 2002 года Dan Linstedt [57]. Метод моделирования «Свод данных» — это методология проектирования, разработанная для глобальных ХД масштаба предприятия и имеющая в основе набор связанных нормализованных таблиц, ориентированных на поддержку функциональных областей бизнеса с возможностью отражения истории. Метод удачно сочетает требования нормализации и возможности схемы » звезда «.
Использование этого метода предполагает наличие у проектировщика ХД базового уровня знаний в области моделирования данных, т.е. понимание таких терминов, как таблица ( table ), взаимосвязь ( relationship ), родитель ( parent ), потомок ( child ), ключ (primary/foreign key), измерение ( dimension ) и факт ( fact ).
Исследователи в области обработки данных постоянно ищут структуры данных для приложений искусственного интеллекта ( artificial intelligence — AI) и извлечения знаний ( data mining — DM). Большинство технологий DM предполагает импорт данных из подающих информационных систем в плоский файл ( flat file ) для того, чтобы объединить форму представления данных с функцией извлечения знаний . Поскольку объем данных в ХД растет быстро, экспорт информации для приложений DM становится затруднительным. Таким образом, возникает разрыв между формой представления (структурой), функцией (AI) и выполнением (DM).
Такой разрыв между формой, функцией и выполнением снижает эффективность использования методов AI и DM. Поэтому задача разработки структур данных, которые математически позволяют использовать технологии AI непосредственно в базах данных, остается очень актуальной. С точки зрения моделирования структур данных метод Data Vault основан на математических принципах, которые позволяют эффективно управлять большими объемами информации. Особенно этот метод эффективен для создания структур данных для динамического управления изменениями во взаимосвязях между данными как единицами представления информации в компьютерных системах. Он позволяет динамически управлять изменением взаимосвязей между данными в системе в процессе эволюции сохраняемых в ней данных.
Метод моделирования «Свод данных» (Data Vault)
Определение метода проектирования «Свод данных» (Data Vault)
Свод данных (Data Vault), по определению, является ориентированным на детали набором нормализованных связанных таблиц, которые обеспечивают информационную поддержку одной или более предметных областей деятельности организации. Этот подход является комбинацией методики реляционного проектирования (до третьей нормальной формы — 3NF ) и методики многомерного проектирования. Метод моделирования «Свод данных» был разработан для создания моделей данных глобальных ХД масштаба предприятия. Он основан на математических принципах, которые поддерживают нормализованные модели данных. По существу модель «Свод данных» соответствует нормализованной до 3NF схеме «звезда», включая измерения, связи «многие ко многим» и таблицы стандартной структуры. Различие лежит в более детальном представлении взаимосвязей и элементов данных, структурированных и детализованных во временном изменении. Этот метод проектирования был разработан, чтобы объединить гибкость структур обработки данных OLTP-систем с мощностью аналитической обработки данных в OLAP-системах. Он является масштабируемым и легко адаптируемым методом разработки структур данных для решения задач анализа данных в масштабах предприятия.
Проблемы моделирования данных для хранилищ данных
Обычно применение известных методик проектирования к разработке модели ХД масштаба предприятия, например, таких как нормализация, сталкивается с рядом трудностей.
В частности, использование 3NF для структур данных приводит к следующему.
- Временная зависимость в первичном ключе (time-driven primary key) приводит к увеличению сложности поддержки отношения «родитель-потомок» и учету влияния каскадных изменений в таких отношениях.
- Сложно обеспечить высокую производительность при загрузке данных в реальном времени в структуру в 3NF .
- Во многих случаях усложняется доступ к данным при обработке запросов.
- Возникают проблемы при использовании анализа на основе свертки-развертки данных ( drill -down analysis).
На рис. 18.1 показана попытка адаптировать структуру данных в 3NF к использованию в ХД. Одна из проблем этой структуры связана с размещением временной метки (data/ time stamp ) в первичном ключе родительской таблицы, для того чтобы представить изменения детальных данных во времени. Это проблема масштабируемости и гибкости структуры. Если данные добавляются в родительскую таблицу, изменения каскадно распространяются через все подчиненные таблицы . Например, когда новая строка вставляется с родительским ключом (parent key), у которого изменяется только поле временной метки, все дочерние строки должны быть переназначены на новый родительский ключ. Этот каскадный эффект имеет отрицательное влияние на обработку данных в таких таблицах, причем чем сложнее и больше структура, тем сильнее влияние каскадного эффекта. Для модели данных масштаба предприятия это создает трудности в расширении и сопровождении модели данных, и, как следствие, усложняется процесс проектирования.
Существует проблема и для взаимосвязанных киосков данных ( conformed data marts ). Такая архитектура глобального ХД представляет собой набор таблиц фактов, которые связаны между собой посредством первичных и внешних ключей или, другими словами, набор взаимосвязанных схем «звезда». При такой реализации ХД возникает ряд проблем, таких как изолированное представление предметно-ориентированных областей, возможное дублирование данных ( data redundancy ), различие представления таблиц фактов по уровню структурированности (детализуемости или гранулированности) данных , синхронизация данных во время загрузки в реальном времени, ограниченность использования технологии DM в масштабах предприятия и др. Схема «звезда» является типичной архитектурой, которая проектируется и реализуется по методологии «снизу вверх», и взаимосвязанные киоски данных создаются на основе подхода «снизу вверх», а реализуются на основе подхода «сверху вниз».
Одной из наиболее сложных проблем взаимосвязанных киосков данных является выбор правильного уровня гранулированности данных ( grain ) для таблиц фактов. Это означает, что агрегирование данных во всех таблицах будет согласованным по измерению времени, а структура каждой таблицы фактов не будет изменяться с точки зрения добавления новых измерений. Такой подход к проектированию ограничивает масштабируемость и гибкость модели данных. Другой проблемой могут быть вспомогательные таблицы в измерениях, которые обслуживают ссылки для взаимоотношений между измерениями. Гранулированность и стабильность измерений являются важными факторами успешного проектирования ХД.
Например, если гранулированность факта «Суммарный доход» таблицы «Суммарный доход» ( рис. 18.2) изменяется, то это должно привести к дублированию таблицы фактов с добавлением дополнительных атрибутов. Предположим, что таблицы фактов связаны между собой только посредством одних и тех же ключей измерений. При добавлении нового измерения к одной из таблиц фактов (например, к таблице фактов «Суммарный доход» добавим измерение «Контракт») факты в таблице «История контрактов» также должны измениться. Таблицы фактов не изменятся, только если они имеют одну и ту же гранулированность .
Среди практиков-разработчиков ХД сложилось мнение, что архитектура ХД должна проектироваться на основе методологии «сверху вниз», а реализация выполняться на основе методологии «снизу вверх». Такой подход позволяет максимально приблизить архитектуру к пониманию задач предметной области ХД, в то время как реализация может поэтапно включать фрагменты предметной области в общее ХД, не нарушая миссию и видение системы складирования данных . Подходы к проектированию и разработке архитектуры ХД должны быть гибкими, чтобы быстро адаптироваться к росту объема данных и расширению или изменению предметных областей в системе.
Одним из подходов к решению задач разработки типовых моделей и архитектур данных является определенная нормализация структур данных. Так же, как и структуры БД OLTP-систем ( 1NF , 2NF , 3NF ; 4NF , 5NF ), БД ХД должны иметь определенную степень нормализации структуры данных. Модель «Свод данных» и является одной из таких нормализованных структур данных для ХД. Она включает методы построения структур данных для отношений «многие ко многим», ссылочной целостности, минимизации дублирования данных и установления семантических связей между ключевыми бизнес-функциями предметной области через концентраторы (hubs).
Элементы модели «Свод данных»
Модель проектирования «Свод данных», аналогично методам многомерного моделирования или » сущность-связь «, содержит ряд структурных компонент, новыми из которых являются сущности-концентраторы , или хабы, сущности-связи и сущности-сателлиты . Проектирование этим методом фокусируется на функциональных предметных областях деятельности организации. Каждая такая область характеризуется бизнес-ключом и представляется в концентраторе первичным ключом. Сущности-связи обеспечивают интеграцию операций между хабами. Сущности-сателлиты обеспечивают контекст первичного ключа хаба. Каждая из этих сущностей сконструирована для обеспечения максимальной гибкости и масштабируемости модели данных ХД масштаба предприятия.
Сущности-концентраторы (Hub Entities). Сущности-концентраторы , или просто хабы (hubs), являются таблицей, которая содержит минимальный список бизнес-ключей (натуральных ключей). Это ключи, которые используются организацией в каждой ежедневной операции: например, номер счета, табельный номер сотрудника, номер покупателя, номер изделия и номер автомобиля. Если в процессе деятельности такой ключ был потерян, то, как правило, теряются и ссылка на контекст, и сопутствующая информация. Помимо натуральных ключей (на рис. 18.3 – атрибут «Номер покупателя в источнике данных»), концентраторы могут иметь следующие атрибуты:
- суррогатный ключ – опциональный атрибут, который является обычно членом числовой последовательности. На рис. 18.3 – атрибут «Номер»;
- временная метка загрузки (Load Data/ Time Stamp ) – это дата и время, когда ключ впервые появился в БД. На рис. 18.3 — атрибут «Время загрузки из источника»;
- источник данных (Record Source) – записывается для трассировки данных. На рис. 18.3 — атрибут «Наименование источника данных».
Рис. 18.3 показывает пример сущности «Концентратор для покупателей». В этой сущности атрибут «Номер покупателя в источнике данных» является первичным бизнес- ключом, а атрибут «Номер» является суррогатным ключом, назначенным для покупателей внутри системы. В табл. 18.1 приведен пример контекста для сущности «Концентратор для покупателей».
| Номер | Номер покупателя в источнике данных | Время загрузки из источника | Наименование источника данных |
|---|---|---|---|
| 1 | 1234 | 23.01.2009 | Продажи |
| 2 | 1235 | 24.01.2009 | Контракты |
| 3 | 2266 | 26.01.2009 | Финансы |
| 4 | 2344 | 28.01.2009 | Продажи |
Cущности-концентраторы не могут быть связаны отношением «один ко многим» (родитель-потомок). Для построения взаимосвязей между концентраторами используются сущности-связи .
Связывающая сущность, или сущность-связь (Link Entitiy) . Сущности-связи являются физическим представлением взаимосвязи «многие ко многим» в 3NF. Связь представляет собой взаимоотношение или операцию между двумя или более бизнес-компонентами или бизнес-ключами. Сущности-связи содержат следующие атрибуты (см. рис. 18.4):
- суррогатный ключ – опциональный атрибут, который используется при связывании более двух концентраторов (на рис. 18.4 не показан);
- ключи концентраторов (Hub Key) – ключи концентраторов, которые мигрируют в сущность-связь для формирования составного ключа , связывающего эти концентраторы;
- временная метка загрузки (Load Data/ Time Stamp ) – дата и время записи связи в БД;
- источник данных (Record Source) – используется для трассировки данных.
Этот компонент модели предназначен для разрешения проблемы отношения «многие ко многим» для ХД. Вместе с сущностями-концентраторами связывающие сущности описывают поток данных предметной области ХД. Табл. 18.2 иллюстрирует содержание соответствующих сущностям таблиц БД.
Что такое свод в Excel?
Сводная таблица — это таблица сгруппированных значений, которая объединяет отдельные элементы более обширной таблицы в рамках одной или нескольких отдельных категорий. Эта сводка может включать суммы, средние значения или другие статистические данные, которые сводная таблица группирует вместе с помощью выбранной функции агрегирования, применяемой к сгруппированным значениям.
Что такое сводная таблица и для чего она используется?
Сводная таблица — это интерактивный способ быстрого обобщения больших объемов данных Сводную таблицу можно использовать для подробного анализа числовых данных и ответов на непредвиденные вопросы о ваших данных. Сводная таблица специально разработана для: выполнения запросов к большим объемам данных многими удобными для пользователя способами.
Для чего используется сводка в Excel?
Сводная таблица в Excel — это извлечение или резюме исходной таблицы с исходными данными. Сводная таблица может дать быстрые ответы на вопросы о вашей таблице, на которые в противном случае можно ответить только с помощью сложных формул.
Что означает поворот в Excel?
Сводная таблица используется для обобщения, сортировки, реорганизации, группировки, подсчета, суммирования или среднего значения данных, хранящихся в таблице. Это позволяет нам преобразовывать столбцы в строки и строки в столбцы. Это позволяет группировать по любому полю (столбцу) и использовать для них расширенные вычисления.
Как сделать сводную таблицу в Excel?
Чтобы вставить сводную таблицу, выполните следующие шаги
- Щелкните любую ячейку внутри набора данных.
- На вкладке «Вставка» в группе «Таблицы» нажмите «Сводная таблица». Появится следующее диалоговое окно. Excel автоматически выбирает данные для вас. Местом по умолчанию для новой сводной таблицы является Новый рабочий лист.
- Нажмите OK.
Learn Pivot Tables in 6 Minutes (Microsoft Excel)
Рекомендуемые:
Что такое совместное использование книги в Excel?
Важно: «Общие рабочие книги» — это более старая функция, которая позволяет вам совместно работать над книгой с несколькими людьми Эта функция имеет множество ограничений и была заменена на совместное редактирование.. Совместное редактирование Excel доступно в настольном клиенте Excel для приложений Microsoft 365 и в Excel для Интернета для подписчиков Microsoft 365 .
Где кошачий угловой свод?
Catty Corner — это область, разделенная на две части дорогой. К югу находится заправочная станция, а к северу — свалка. Вам нужно пойти на северную сторону, затем на запад, вы найдете хранилище, на первом этаже здания, обращенное на север .
Что такое ребристый свод?
Ребристый свод или ребристый свод — это архитектурная особенность для покрытия широкого пространства, такого как церковный неф, состоящая из каркаса из скрещенных или диагональных арочных ребер. Вариации использовались в римской архитектуре, византийской архитектуре, исламской архитектуре, романской архитектуре и особенно в готической архитектуре.
Что такое поперечный свод?
Поперечный свод – это один из трех сводов стопы своды стопы Своды стопы, образованные предплюсневыми и плюсневыми костями, укрепленные связками и сухожилиями, позволяют стопе поддерживать вес тела в прямом положении снаименьшим весом. Они подразделяются на продольные и поперечные арки.
Почему на стыке полов и стен требуется свод?
Когда очистка выполняется распылением или смывом, требуется покрытие и герметизация стыков пола и стены для обеспечения поверхности, подходящей для смыва водой . Для чего используется напольное покрытие? Что такое покрытие? Покрытие — это материал для пола, который можно найти у основания стен и выступов оборудования, включая основания прилавков, шкафов, салат-баров и другого оборудования, устанавливаемого на полу.