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

12.08.2020
Комментариев нет

Вычисляемые поля в сводной таблице — Сводные таблицы Excel

Настройка вычислений в сводных таблицах
Вычисление значений в отчете сводной таблицы
Сводные таблицы Excel

Попробуйте Microsoft Edge быстрый и безопасный браузер, разработанный для Windows 10 Нет Начало работы. В отчетах сводных таблиц можно использовать функции сведения в полях значений для объединения значений из исходных данных. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете сводной таблицы. В отчетах сводных таблиц можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в отчетах сводных таблиц и сводных диаграмм. Для вычисления значений в отчете сводной таблицы можно использовать любые из описанных ниже методов. Функции сведения в полях значений. В отчете сводной таблицы в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:. Отчет сводной таблицы и отчет сводной диаграммы выглядят, как показано на рисунке ниже. Если создать отчет сводной диаграммы на основе данных из отчета сводной таблицы, то значения на диаграмме будут соответствовать вычислениям в базовом отчете сводной таблицы. В отчете сводной таблицы поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север , Юг , Восток и Запад. В отчете сводной диаграммы поле Регион может представлять собой поле категорий, в котором элементы Север , Юг , Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март , Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять своим положением на вертикальной оси, т. Ниже перечислены функции сведения, с помощью которых можно вычислять поля значений. Эти функции доступны для всех типов исходных данных, кроме OLAP. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых. Действует аналогично функции СЧЁТ. Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Служат для отображения значений на основе других элементов или ячеек в области данных. Например, можно отобразить значения в поле данных Сумма продаж как процент от продаж за месяц Март или как нарастающий итог по элементам в поле Месяц. Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду. Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории. Значения в процентах от значения базового элемента в соответствующем базовом поле. Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. Значение в виде нарастающего итога для последовательных элементов в базовом поле. Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете. Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP. Вычисления на основе исходных данных OLAP. При создании отчетов сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В отчете сводной таблицы невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты. Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений VBA и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP. Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов. Вычисления на основе исходных данных не из базы данных OLAP. Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле. Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из базы данных OLAP. В отчетах, основанных на базе данных OLAP, формулы не используются. При использовании формул в отчетах сводных таблиц нужно учитывать описанные ниже правила синтаксиса и поведения формул. Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива. Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3: C9 используется имя поля Молоко. В отчете сводной диаграммы имена полей отображаются в списке полей сводной таблицы, а имена элементов можно просмотреть в каждом раскрывающемся списке полей. Не следует путать эти имена с теми, которые отображаются в подсказках к диаграммам и соответствуют именам рядов и точек данных. Формулы работают с итоговыми суммами, а не с отдельными записями. Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Формулы для вычисляемых объектов оперируют отдельными записями. Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6: Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки. Имена полей в ссылках на элементы. Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Используйте этот формат, чтобы избежать ошибок ИМЯ? Например, если в отчете есть два элемента с именем «Мясо» в полях «Тип» и «Категория», можно избежать появления ошибок ИМЯ? Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе. Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку ССЫЛКА!. Чтобы использовать формулы в отчете сводной диаграммы, их нужно создать в связанном отчете сводной таблицы. Там вы увидите отдельные значения, из которых состоят данные, а затем сможете посмотреть на результаты в графическом представлении в отчете сводной диаграммы. Например, в этом отчете сводной диаграммы представлены данные о продажах для каждого продавца по регионам:. Однако вычисляемый объект, созданный в поле «Продавец», будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории. Создание формул в отчете сводной таблицы Важно: Создать формулу в отчете сводной таблицы, подключенном к источнику данных OLAP, невозможно. Прежде всего определитесь, что нужно создать в поле: Если вы хотите использовать в формуле данные из другого поля, создайте вычисляемое поле. Если нужно использовать в формуле данные из одного или нескольких конкретных элементов в определенном поле, создайте вычисляемый объект. В случае вычисляемых объектов в отдельных ячейках можно вводить разные формулы. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор. Чтобы использовать в формуле данные из другого поля, щелкните его в списке Поля и нажмите кнопку Добавить поле. Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать. Чтобы использовать в формуле данные из имеющегося элемента, щелкните его в списке Элементы и нажмите кнопку Добавить элемент он должен находиться в том же поле, что и вычисляемый объект. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки. Щелкните одну из формул и нажмите кнопку Вверх или Вниз. Перед тем как редактировать формулу, определите, к чему она относится: Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него. В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Найдите в списке формулу, которую нужно изменить. Она может находиться в разделе «Вычисляемое поле» или «Вычисляемый объект». Если для одного вычисляемого объекта задано несколько формул, то формулой по умолчанию, введенной при его создании, является та, для которой в столбце B указано его имя. Для остальных формул в столбце B указывается не только имя самого вычисляемого объекта, но и имена элементов, на пересечении которых используется формула. Предположим, что есть формула по умолчанию для вычисляемого объекта с именем МойЭлемент , а также другая формула для этого объекта с именем МойЭлемент Январь Продажи. В отчете сводной таблицы вы увидите эту формулу в ячейке «Продажи» для строки «МойЭлемент» и столбца «Январь». В списке Имя выберите вычисляемое поле, для которого нужно изменить формулу. Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений. Если удалить формулу из сводной таблицы, восстановить ее будет невозможно. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент, перетащив его за пределы отчета сводной таблицы. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях. Просмотр всех формул, используемых в отчете сводной таблицы Чтобы отобразить список всех формул, которые используются в текущем отчете сводной таблицы, выполните указанные ниже действия. Определите, к чему относится формула: Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него, выполнив указанные ниже действия. Редактирование одной формулы для вычисляемого объекта. Редактирование отдельных формул для конкретных ячеек вычисляемого объекта. Если есть несколько вычисляемых объектов или формул, измените порядок вычислений, выполнив указанные ниже действия. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент. Чтобы скрыть поле, перетащите его за пределы отчета. Поиск в справке Office Нет результатов. Импорт и анализ данных. Вычисление значений в отчете сводной таблицы. Дополнительные сведения о вычислении значений в отчете сводной таблицы. Доступные методы вычислений Для вычисления значений в отчете сводной таблицы можно использовать любые из описанных ниже методов. Рассмотрим пример с такими исходными данными: Функция Сведение данных Сумма Сумма значений. Функция по умолчанию для числовых данных. Количество чисел Количество числовых значений. Стандартное отклонение Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Несмещенное отклонение Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. Дисперсия Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. Несмещенная дисперсия Дисперсия генеральной совокупности, которая содержит все сводимые данные. Для настраиваемых вычислений в полях значений доступны перечисленные ниже функции. Функция Результат Без вычислений Значение, введенное в данное поле. Доля Значения в процентах от значения базового элемента в соответствующем базовом поле. Отличие Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. Приведенное отличие Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. С нарастающим итогом в поле Значение в виде нарастающего итога для последовательных элементов в базовом поле. Индекс Рассчитывает значения следующим образом: Влияние типа источника данных на вычисления Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP. Использование формул в отчетах сводных таблиц Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из базы данных OLAP. Выберите отчет сводной таблицы. Редактирование формулы в сводной таблице Определите, к чему относится формула: Выполните одно из указанных ниже действий. В поле Формула измените формулу. В поле Имя выберите вычисляемый объект. Щелкните ячейку, для которой нужно изменить формулу. В строке формул внесите изменения в формулу. Повторите эти действия для других формул, чтобы расположить их в нужном порядке. Удаление формулы из сводной таблицы Совет: Первоочередный доступ к новым возможностям. Были ли сведения полезными? Что мы могли бы улучшить? Спасибо за ваш отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office. Обращение в службу поддержки. Обучение Windows Office Skype Outlook OneDrive MSN. Устройства Microsoft Surface Xbox Устройства на Windows Microsoft Lumia. Магазин Microsoft Адресная книга Заказы Возврат товаров Продажа и поддержка. Загрузки Центр загрузок Microsoft Загрузки для Windows Windows 10 Apps Office Apps Microsoft Lumia Apps Internet Explorer. Ценности Правила обеспечения конфиденциальности корпорации Майкрософт Образование. Компания Карьера в Microsoft Microsoft Новости и публикации Продукты Политика по персональным данным. Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. Дисперсия генеральной совокупности, которая содержит все сводимые данные. Значения в процентах от общей суммы всех значений или точек данных в отчете. Рассчитывает значения следующим образом:

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

Бывают случаи, когда вам нужно добавить чуть больше информации в сводную таблицу по сравнению с тем, на что она рассчитывалась, но при этом нет смысла изменять исходные данные, включая туда эту самую информацию. В таких редких случаях может быть полезным добавить в сводную таблицу пользовательские вычисляемые поля. Такие пользовательские поля можно добавить, например, для отображения средних значений, процентов, расхождения или даже минимального или максимального значения поля. Читайте инструкции по добавлению пользовательских полей в сводную таблицу, чтобы вы могли получить нужную вам информацию, не прилагая особых усилий. Сообщество Наугад Про нас Категории Свежие правки. Написать статью Категоризировать статьи Другие идеи Откройте в Excel книгу, содержащую исходные данные, и сводную таблицу, с которой вы будете работать. Выберите лист, который содержит сводную таблицу, и сделайте его активным, просто кликнув по нему. Определите, какое пользовательское поле вам нужно, и на какие другие поля оно будет ссылаться для получения желаемого результата. Отобразите меню «PivotTable Tools» «Работа со сводными таблицами» , просто кликнув в любом месте сводной таблицы. Выберите вкладку «Options» «Параметры» и затем выберите пункт «Calculated Field» «Вычисляемое поле» в меню «Formulas» «Формулы». Во всплывающем окне введите название своего пользовательского поля. В текстовом поле окна «Formula» «Формула» введите формулу для вычисления значения пользовательского поля. Вычисляемые поля в формулах сводных таблиц не поддерживают диапазоны. Поэтому в формулах вместо диапазона вам следует использовать имя соответствующей колонки. Щелкните внутри поля для ввода текста «Formula» «Формула» и из предлагаемого списка выберите поле, используемое в расчете. Щелкните «Insert Field» «Добавить поле» , чтобы вставить правильное имя колонки в свою формулу. Допишите формулу, добавив нужные вычисления. Например, вы хотите вычислить 6-процентный налог на продажи в сводной таблице, в которой показываются продажи продукта в различных регионах. Имя колонки «Регион» находится в разделе «Columns» «Колонки» , название «Продажи» находится в разделе «Values» «Значения» , а название «Продукт» находится в разделе «Rows» «Строки». Нажмите кнопку «Add» «Добавить» и затем нажмите «OK», чтобы закрыть окно. Убедитесь, что в мастере сводных таблиц в разделе «Values» «Значения» появилось ваше вычисляемое поле. Если нет, проверьте все еще раз по шагам и повторите попытку. Если вы хотите, вы можете изменить порядок следования полей в разделе «Values» «Значения». Советы Изменить формулу в вычисляемом поле намного проще, чем создавать и редактировать формулу в таблице исходных данных. Это удобно, если число полей, участвующих в расчете вашего вычисляемого поля, часто меняется. Помните, что вычисляемые поля сводной таблицы рассчитываются на основе объединенных итогов, а не целых строк. Если вам нужно вычисление по конкретным строкам, то в формулу в исходных данных вам нужно вставить колонку и формулу для ее расчета. Источники и ссылки http: Информация о статье Категории: Компьютеры и электроника На других языках: Была ли эта статья полезной? Куки помогают сделать WikiHow лучше. Продолжая использовать наш сайт, вы соглашаетесь с нашими куки правилами. Главная страница Про wikiHow Terms of Use RSS Карта сайта Войти. Весь текст размещен под лицензией Creative Commons. Сделано с помощью Mediawiki.

Настройка вычислений в сводных таблицах
Где находится код регистрациив паспорте фото
Шеллакна рукахи ногах фото дизайн
Н носов рассказы веселая семейка
Круг все косы твои текст
Перевод английских текстов 10 класс spotlight
Сколько длится рабочий день во франции

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

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

В примере вычисляемым поле является поле с валовым доходом с НДС.

Предположим, нам необходимо изменить расчет в поле и перевести в валовый доход БЕЗ НДС.

Для упрощения, определим ставку НДС=18% для всех групп товаров в таблице.

Для этого:

1. Кликнем на любом элементе сводной таблицы и в группе меню «Параметры» на ленте.


2. Кликнем на «Формулы» (или «Поля, элементы, наборы») и выберем «Вычисляемое поле».

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

4. Выберем поле, по которому необходимо изменить расчет и откорректируем формулу в поле «Формула».
В примере мы переведем Валовый доход в значения без НДС.

Вместо

=’Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’

укажем

=(‘Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’)/1,18

5. Переименуем поле и нажмем кнопку «Изменить». Закроем модуль.

Итогом будет пересчитанный, уже по новой формуле, столбец.

Для удаления поля, необходимо выбрать имя поля в модуле и нажать «Удалить».

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

В предыдущих публикациях я поделился информацией о том, как импортировать данные из Google Analytics в различные процессоры электронных таблиц (мы рассматривали импорт в Google таблицы и Excel). Но импорт — это только первый шаг на пути к полноценному анализу данных. После загрузки всей необходимой вам информации в редактор электронных таблиц, ее необходимо визуализировать и только потом анализировать.

Самый удобный и быстрый инструмент для преобразования массива данных в информативный отчет — сводные таблицы. В основе сводных таблиц лежит технология OLAP, описанная в 1993 году известным исследователем баз данных и автором реляционной модели данных Эдгаром Коддом. По сути, сводная таблица — это инструмент обработки данных для их группировки и обобщения.

Любая сводная таблица строится на основе определенной базы данных. В виде базы данных выступает массив, состоящий из полей и записей. Каждая строка (запись) в базе данных представляет собой информацию об отдельном случае, объекте или состоянии изучаемого объекта, а каждый столбец (поле) является параметром, свойством или признаком всех исследуемых объектов. Например, параметром может быть источник трафика, описание информации об источнике. В поле «источник трафика» будет, например, Google. Все поля базы данных разделяются на два типа: измерения («параметры» в терминологии Google Analytics) и меры («показатели» в терминологии Google Analytics). Измерением — это название или свойство объекта, в разрезе которых мы можем анализировать различные количественные показатели.

Каждое измерение содержит ряд элементов. Например, элементами измерения «Источник трафика» будут: Google, Yandex и любые другие возможные источники трафика. Мерой являются количественные показатели, которые мы сравниваем между элементами измерений. Например: количество кликов, количество транзакций, сумма дохода. С общим понятием, что такое сводная таблица, мы ознакомились. Далее рассмотрим, как строятся сводные таблицы в различных процессорах электронных таблиц.

Безусловный лидер в реализации всего возможного функционала сводных таблиц — Microsoft Excel. По функциональным возможностям с этим инструментом могут соревноваться только такие гиганты BI индустрии, как QlikView и Tableau, но в связи с тем, что данные платформы достаточно дорого стоят и в русскоязычном сегменте пока не успели получить особую популярность, в этой статье рассматриваться не будут.

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

Сводные таблицы в Microsoft Excel 2013

Для построения сводной таблицы в Microsoft Excel 2013 вам необходимо открыть скачанный ранее csv файл. После чего, установив курсор на любой из ячеек таблицы, нажмите Ctrl+A — этим действием вы выделите всю базу данных. На её основе мы будем строить сводную таблицу. Далее перейдите на вкладку «Вставка» и в группе «Таблицы» нажмите «Сводная таблица». В открывшимся диалоговом окне «Создание сводной таблицы» ничего не меняем и жмем ОК. На этом процесс создания закончен и мы приступаем к работе со сводной таблицей. После создания таблицы в книге Excel будет создан новый лист. Он будет выглядеть так: Как вы видите на скриншоте, этот лист состоит из самой сводной таблицы и конструктора сводной таблицы. Конструктор состоит, в свою очередь, из пяти частей:

  • список полей;
  • фильтры;
  • колонны;
  • строки;
  • значения.

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

  1. Поле Date с помощью правой кнопки мыши перетащим в область строк.
  2. Поле Device category перетащим в область колонн.
  3. Поле User type — в область фильтров.
  4. Поле Session — в область значений.

В итоге должно получиться так: В результате четырех произведенных действий мы построили перекрестную таблицу, которую в Google Analytics построить нельзя. В строках мы видим даты, а по столбцам у нас располагается информация о количестве сеансов за определенную дату по каждому типу устройств. Это получилось, потому что мы перенесли поле Date в область строк. В отчете сводной таблицы в каждой строке появилась определенная дата.

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

Перенеся поле Sessions в область значений, мы заполнили область значений сводной таблицы информацией о количестве сеансов за каждый день по каждому типу устройств. В полях, которые были добавлены в область значений конструктора сводной таблицы, можно изменять агрегирующую функцию. По умолчанию для числовых полей применяется суммирование, но вы можете выбрать любую другую из предложенных агрегирующих функций. Для этого достаточно в области значений сводной таблицы кликнуть правой кнопкой мыши и в выпадающем меню «Итоги по» выбрать нужную функцию. Остается вопрос: а что же нам дал перенос поля User type в фильтры? Чтобы это понять, давайте применим фильтр сводной таблицы и выведем в отчет информацию только по новым пользователям.

  1. Откройте перечень элементов поля User type, нажав на ярлык с изображением воронки в области фильтров сводной таблицы.
  2. Выберите элемент New user.
  3. Нажмите ОК.

Теперь в область значений сводной таблицы выводится информация о количестве сеансов, совершенных только новыми пользователями в разрезе дат и типов устройств. Выше показан пример простейшей сводной таблицы, но на самом деле это далеко не весь функционал, который вы можете использовать для построения отчетов сводных таблиц. Любые параметры могут быть выведены в отчет в виде определенной иерархии. Для примера перенесем в область строк поле Week таким образом, чтобы оно находилось выше, чем поле Date. Отчет сводной таблицы будет сгруппирован не только по датам, но и по неделям. К тому же к каждой неделе будут прикреплены промежуточные итоги для более удобного анализа отображаемой информации. Теперь сводная таблица приобрела следующий вид: Такие иерархии можно строить и в области колонн. Вы можете смотреть информацию на уровне недель и при необходимости детализировать информацию по количеству сеансов до уровня дат простым нажатием на иконку +/−. Очень полезная функция сводных таблиц в Microsoft Excel — возможность дополнительных вычислений над любыми выведенными в таблицу показателями.

Например, мы хотим посмотреть не количество сеансов за каждую дату в разбивке по типам устройств, а какую долю сеансов в каждой дате занимает каждый тип устройств. Для этого достаточно кликнуть правой кнопкой мыши в области значений сводной таблицы, и в контекстном меню выбрать пункт «Дополнительные вычисления» => «% от суммы по строке». Теперь сводная таблица показывает долю каждого типа устройства в общем объеме сеансов за день. Зачастую при анализе данных нам необходимо получить какой либо относительный расчетный показатель. При правильном построении базы данных такие показатели в ней не содержатся, но информация о полях, необходимых для вычисления какого-либо расчетного показателя, как правило, в базе данных есть. Приведем пример. Нам надо вывести в таблицу показатель отказов для каждого типа устройств.

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

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

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

В нашем случае формула вычисляемого поля будет выглядеть как «=Bounces / Sessions». Дальше жмем OК. В область значений отчета сводной таблицы будет добавлено поле «Показатель отказов»: После добавления нового расчетного поля в таблице были автоматически созданы четыре новые колонны. И если сейчас посмотреть на конструктор сводной таблицы, мы увидим, что в область «Колонны» добавлено поле «Значения». Это поле появляется автоматически, если в область «Значения» добавлено более одного поля. Вы можете перенести поле «Значения» в строки или колонны, в область фильтров и значений данное поля перенести нельзя.

По сути, это поле содержит название всех показателей (полей), выведенных в область значений. Для закрепления материала и преобразования сводной таблицы в более читабельный вид перетянем поле «Значения» в область строк под поле Date. Теперь в таблице каждая дата разбита на две строки. В первой выводится доля сеансов по каждому типу устройства, во второй — показатель отказов для каждого типа устройств. Иногда в ходе анализа нам требуется каким-либо образом объединить некоторые элементы измерения в группы. Например, в данном случае нам может понадобиться объединить типы устройств mobile и tablet в одну группу и назвать ее Other.

Таким образом мы можем проанализировать различие между настольными устройствами и всеми остальными. Все, что нам необходимо сделать для группировки измерения, — это выделить нужные его элементы с помощью мыши, после чего вызвать контекстное меню правым кликом мыши и выбрать пункт «Группировать». Элементы mobile и tablet будут объединены в группу, которой по умолчанию присваивается название «Группа 1». Чтобы переименовать группу, просто перейдите в ячейку с названием и введите новое Other. После группировки некоторых элементов измерения область столбцов отчета сводной таблицы стала двухуровневой, так же, как и область строк после добавления в нее поля Week. Теперь вы можете анализировать информацию по типам устройств на двух уровнях, переключаясь между ними с помощью +/−. Далее вы можете настроить внешний вид вашей сводной таблицы, используя готовые шаблонные стили, либо выбрав свой собственный. Для создания кастомного стиля с помощью мыши выберите любую ячейку, которая входит в область отчета сводной таблицы, и, перейдя на вкладку «Конструктор», выберите один из предложенных стилей оформления. Также существует возможность добавления срезов и временных шкал. Хочу заметить, что этот функционал не доступен в старых версиях Microsoft Excel, возможность добавления срезов появилась в 2010 версии, а временные шкалы добавили только в 2013 году. Срез выполняет ту же функцию что и фильтр, который мы построили, добавив поле User type при построении сводной таблицы в область фильтров.

Единственное отличие заключается в том, что срез имеет более удобную визуализацию. Давайте добавим срез по Source. Для этого выделите любую ячейку, относящуюся к области отчета сводной таблицы, перейдите на вкладку «Анализ» и в группе «Фильтры» нажмите иконку «Вставить срез». Откроется диалоговое окно «Вставка срезов», в котором вам необходимо выбрать, по каким полям вы хотите создать срезы. В нашем случае это поле Source. После нажатия ОК на рабочий лист будет добавлен срез. Использовать срез можно так же, как и обычный фильтр. Давайте для примера в созданном нами срезе выберем элемент «google» и тем самым в отчет сводной таблицы выведем информацию о доле каждого типа устройств по каждой дате только по сеансам, совершенным из источника «google». Зажав левый Ctrl, вы можете выбрать любое количество элементов среза, информацию по которым планируете вывести в отчет. Временная шкала работает по такому же принципу, как и срез, но строить ее можно только на основе полей, содержащих данные в формате даты. Создается она на вкладке «Анализ» с помощью кнопки «Вставить временную шкалу», находящейся в группе «Фильтры».

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

Для тех, кто решил всерьез углубиться в изучение этого инструмента, советую ознакомиться с материалами, изложенными одним из ведущих специалистов по программному обеспечению электронных таблиц Биллом Джеленом в своей книге «Сводные таблицы в Microsoft Excel 2013» (если интересно, у меня есть электронная версия «Сводных таблиц в Microsoft Excel 2010»).

Истории бизнеса и полезные фишки

Сводные таблицы в Google Spreadsheets (Google таблицы)

Основное преимущество Google Spreadsheets — доступ к ним есть всегда и везде при условии наличия подключения к интернету. Именно это делает продукт наиболее удобным при совместной работе нескольких пользователей. Для построения сводной таблицы в Google Spreadsheets необходимо создать новую таблицу в своем Google Диске, перейдя по . После этого импортируйте в созданную таблицу скачанную ранее базу данных ( csv файл с базой данных). Чтобы загрузить базу данных в таблицу, в меню «Файл» выберите пункт «Импорт». В открывшемся диалоговом окне переходим на вкладку «Загрузка» и жмем кнопку «Выберите файл на компьютере», после чего выбираем скачанный ранее файл pivotTableDB.csv. В диалоговом окне «Импорт файла» устанавливаем переключатель «Действие после импорта» в положение «Заменить текущий лист», а переключатель «Разделитель» устанавливаем в положение «Другое». В качестве разделителя вводим точку с запятой. После нажатия кнопки «Импортировать» данные будут загружены в вашу таблицу на «Лист1». Теперь можно приступить непосредственно к созданию сводной таблицы. Для этого надо выделить весь загруженный массив данных. Наиболее быстрым способом в этом случае будет перейти в ячейку A1 и последовательно использовать сочетание клавиш Ctrl + Shift + Стрелка вправо, затем Ctrl + Shift + Стрелка вниз.

Далее переходим в меню «Данные» и жмем на пункт «Сводная таблица». Далее в Google таблице, так же как и в Microsoft Excel, будет создан новый лист с названием «Сводная таблица 1», областью сводной таблицы и редактором отчетов. Редактор отчет так же состоит из четырех областей: «Строки», «Столбцы», «Значения», «Фильтры». Давайте пройдем путь построения сводной таблицы, описанный в примере выше. Для этого произведем те же четыре действия.

  1. В область «Строки» добавляем поле Date.
  2. В область «Столбцы» добавляем поле Device Category.
  3. В область «Значения» добавляем поле Sessions.
  4. В область «Фильтры» добавляем поле User type.

Сводная таблица приобрела знакомый из описания Microsoft Excel вид: Так же, как и в Microsoft Excel, для полей, добавленных в область значений в Google таблицах, вы можете изменять агрегирующую функцию. Сделать это можно, выбрав нужную функцию из выпадающего списка в области значений редактора отчетов «Суммировать по». На данный момент единственое наиболее заметным отличие в том, что в область сводной таблицы не выводятся поля, добавленные в область фильтра редактора отчетов. Применить фильтр в сводных таблицах Google Spreadsheets можно только используя редактор отчета. Давайте отфильтруем сводную таблицу по новым пользователям.

Для этого в области фильтра редактора отчетов в выпадающем списке «Показать» отмечаем галочкой элемент New Visitor. Далее, чтобы добавить возможность детализировать информацию не только по датам, но и по неделям, необходимо в редакторе отчетов в область строки добавить поле Week и перетащить его на уровень выше, чем поле Date. Теперь в отчете сводной таблицы представлены два уровня детализации, по неделям и датам. Для подсчета показателя отказов с помощью расчетного поля в области значений редактора отчетов нажмите «Добавить поле» и выберите пункт «Рассчитываемое поле». Далее необходимо ввести название расчетного поля и формулу. В нашем случае названием поля будет «Показатель отказов», а формулой для расчета «=bounces / sessions».

В отличие от Microsoft Excel, в данном случае названия полей в формуле расчета можно ввести только с клавиатуры. Это важно.

Теперь сводная таблица имеет следующий вид: Для каждого типа устройства добавлен дополнительный столбец с информацией о показателе отказов. Если хотите привести таблицу в более читабельный вид и показатель отказов выводить второй строкой для каждой даты, а не вторым столбцом для каждого типа устройств, в редакторе отчетов в области значений переставьте переключатель в положение «как: Строки». Отчет примет вид, в котором на каждую дату приходится две строки данных. В первой будет количество сеансов, во второй — показатель отказов. На этом функциональные возможности Google таблиц заканчиваются. В целом, этого достаточно для построения визуализации данных для проведения анализа.

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

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

Для создания сводной таблицы в меню «Файл» LibreOffice выберите пункт «Открыть», после чего укажите скачанный ранее файл pivotTableDB.csv. В диалоговом окне «Импорт текста» установите переключатель «Параметры разделителя» в положение «Разделитель», и в качестве разделителя установите «Точка с запятой». После нажатия ОК необходимая таблица данных будет загружена в документ. Теперь, когда у вас есть база данных, необходимо выделить ее нажатием Ctrl+A, и в в группе «Сводная таблица» (меню «Данные») нажать кнопку «Создать». В качестве источника в диалоговом окне «Выбрать источник» устанавливаем переключатель в положение «Выбранное выделение». Нажимаем ОК. Далее появится диалоговое окно «Разметка сводной таблицы». Это своеобразный конструктор сводной табицы из приведенных выше примеров с Microsoft Excel и Google Spreadsheets. Чтобы построить сводную таблицу, аналогичную двум предыдущим примерам, сделайте следующие действия.

1. В область «Поля страниц» перетащите поле User type, так как в LibreOffice область «Поля страниц» — это область фильтров сводной таблицы.

2. В область столбцов перенесите поле Device category. По умолчанию область столбцов уже будет содержать поле с именем «Данные». С помощью этого поля вы можете изменять положение рассчитываемых метрик. Примерно так же, как мы делали это в Excel и Google Таблицах. Тогда мы располагали информацию так, чтобы каждая дата содержала две строки: одну с данными о сессиях, а вторую — о показателе отказов. Так же и в LibreOffice вы можете менять расположение вычисляемых данных, отображая их в строках или столбцах.

3. В область «Поля строк» перенесите сначала поле Week, после чего под этим полем расположите поле Date.

4. В область «Поля данных» перетащите поле Sessions. Для того, чтобы изменить агрегирующую функцию либо настроить дополнительное вычисление для рассчитываемого поля, достаточно дважды кликнуть на него левой кнопкой мыши и выбрать из списка нужную функцию либо дополнительное вычисление. Ранее мы в примере с Microsoft Excel устанавливали в качестве дополнительного вычисления «Процент от суммы по строке». Чтобы настроить подобное вычисление в LibreOffice после двойного клика по полю Sessions, расположенного в области данных, в открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК.

5. Для быстрого изменения уровня детализации откройте подменю «Параметры», находящееся в нижней части диалогового окна «Разметка сводной таблицы», и установите там флажки «Добавить фильтр» и «Разрешить переход к деталям». Нажмите OK. В ваш документ будет добавлена сводная таблица, аналогичная приведенным выше примерам. С одним исключением: в данной сводной таблице мы не вывели поле «Показатель отказов», так как на момент написания статьи LibreOffice не поддерживает функционал рассчитываемых полей. У вас получится такая сводная таблица:

Предлагаю сравнить функционал Microsoft Excel, Google Spreadsheets и OpenOffice по работе со сводными таблицами. В данной таблице перечислен только тот функционал, который был описан в данной статье. Относительно Microsoft Excel, это даже не половина всех существующих возможностей по построению и использованию сводных таблиц.

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

Страницы ← предыдущая следующая → 1 2 3 4 5 6 7 8. Работа со сводными таблицами Цель работы Изучить возможности сводных таблиц Microsoft Excel для анализа данных. Теоретические основы Создание сводной таблицы на основе списка Excel В процессе работы большинства предприятий накапливаются различные данные, например, для торгового предприятия это могут быть данные о продажах товаров, для предприятий сотовой связи — статистика нагрузки на базовые станции. Очень часто менеджменту предприятия необходима аналитическая информация, которая генерируется на основе необработанных данных, например, посчитать вклад каждого вида товара в доходы предприятия или качество обслуживания в зоне данной станции. Из необработанной информации такие сведения извлечь очень тяжело: нужно выполнять сложные SQL-запросы, которые обрабатываются долго и часто мешают текущей работе. Поэтому в настоящее время необработанные данные сводятся вначале в хранилище архивных данных Data Warehouse, а затем — в кубы OLAP, которые очень удобны для интерактивного анализа. Можно представить кубы OLAP как многомерные таблицы, в которых вместо стандартных двух измерений (столбцы и строки, как в обычных таблицах) измерений может быть очень много. Обычно для описания измерений в кубе используется термин «в разрезе». Например, отделу маркетинга может быть нужна информация во временном разрезе, в региональном разрезе, в разрезе типов продукта, в разрезе каналов продаж и т. п. При помощи кубов, в отличие от стандартных SQL-запросов, очень просто получать ответы на такие вопросы, как «сколько товаров такого-то типа было продано в четвертом квартале прошлого года в Северо-Западном регионе через региональных дистрибьюторов». Для работы с такими кубами в Excel встроен специальный клиент – Pivot Table (Сводная таблица), который доступен на вкладке Вставка в группе Таблицы. Таким образом, сводная таблица представляет собой интерактивную таблицу, с помощью которой можно быстро объединять и анализировать большие объемы данных. Она позволяет получать динамические представления данных, объединять большие массивы, переупорядочить их, подводить итоги по различным полям. Отображая разные страницы, можно выполнять следующие действия: − осуществлять фильтрацию данных, − детализировать информацию, − автоматически получать промежуточные или общие итоги, 51 − добавлять формулы в вычисляемые поля или элементы полей. Независимо от назначения и оформления все сводные таблицы имеют элементы (области): область строк; область столбцов; фильтр отчета и область данных. В областях строк, столбцов и фильтре отчета находятся поля, для элементов которых производятся вычисления над элементами полей, помещенных в область данных. Обычно область данных содержит значения, которые обрабатываются определенной функцией. Поля представляют собой категорию (группу) данных, извлекаемых из одного столбца исходной таблицы. Название поля извлекается из верхней ячейки столбца исходной таблицы. Области сводной таблицы могут иметь любое количество полей. Для того чтобы сводная таблица выполняла свои функции, область данных должна иметь хотя бы одно поле. Области строк, столбцов и фильтр отчета могут не иметь полей. Вычисление значений в отчете сводной таблицы Для вычисления полей значений используются следующие итоговые функции: Функция Вычисляемое значение СУММ Сумма значений. Это функция для числовых данных по умолчанию. СРЗНАЧ Среднее набора значений. МАКС Наибольшее значение. МИН Наименьшее значение. ПРОИЗВЕД Произведение значений. Кол-во чисел Количество числовых значений. Итоговая функция «Кол-во чисел» работает так же, как функция СЧЁТ. СТАНДОТКЛОН Оценка стандартного отклонения совокупности, в которой примером является подмножество всей совокупности. СТАНДОТКЛОНП Стандартное отклонение совокупности, где совокупностью являются все складываемые данные. ДИСП Оценка изменчивости совокупности, в которой примером является подмножество всей совокупности. ДИСПР Изменчивость совокупности, где совокупностью являются все складываемые данные. Новые возможности сводных таблиц в Excel 2010 Кроме вычисления суммы или количества в сводной таблице можно подсчитывать различные итоги, включая различные виды долей (от общей суммы, от суммы по строке или столбцу, относительно заданного элемента), нарастающие итоги, динамику изменений (отличие и приведенное отличие) и т.д. В 2010 версии Excel к этому набору добавились несколько новых функций, доступных через контекстное меню (для их использования 52 щелкните правой кнопкой мыши по полю в таблице и выберите Дополнительные вычисления): • % от суммы по родительской строке (столбцу) — позволяет посчитать долю относительно промежуточного итога по строке или столбцу. В прошлых версиях можно было вычислять долю только относительно общего итога. • % от суммы нарастающим итогом — работает аналогично функции суммирования нарастающим итогом, но отображает результат в виде доли, т.е. в процентах. Удобно считать, например, процент выполнения плана или исполнения бюджета. • сортировка от минимального к максимальному и наоборот — функция ранжирования, вычисляющая порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете. Срезы Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации. Самый простой способ для этого – поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения. Неудобства такого способа очевидны: • при выборе нескольких элементов — их не видно, а видно текст «(несколько элементов)», что затрудняет просмотр; • один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц, то для каждой придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы. В Excel 2010 отбор требуемых элементов это можно делать с помощью срезов (slicers). Срезы – это графическое представление интерактивных фильтров для сводной таблицы или диаграммы. Если используются несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных (см. рис. 8.1). Рис. 8.1 Использование срезов для отображения данных 53 Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается на листе, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer), как показано на рис. 8.2: Рис. 8.2 Вставить срез При выделении или снятии выделения с элементов среза можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительная особенность в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице. Вставка вычисляемого поля или вычисляемого элемента Поскольку сводная таблица является особым типом диапазона данных, в нее нельзя вставлять новые строки или столбцы. Это означает, что нельзя вставить в сводную таблицу формулы, которые будут выполнять операции над данными. Если итоговые функции и настраиваемые вычисления не дают нужный результат, можно создать собственные формулы в вычисляемых полях и вычисляемых элементах. Например, можно добавить вычисляемый элемент с формулой для комиссионных сборов. В отчет сводной таблицы будут автоматически включены комиссионные сборы в промежуточные и общие итоги. Особенности применения: − Вычисляемое поле используется для подстановки в формулу данных из другого поля. − Вычисляемый элемент применяется для подстановки в формулу данных из одного или нескольких специальных элементов внутри поля. − Для вычисляемых элементов можно вводить различные формулы в ячейки по очереди. Например, если для всех месяцев в вычисляемом элементе Продажи содержится формула =Продажи*0,25, ее можно заменить формулой =Продажи*0,5 для июня, июля и августа месяца. 54 − При наличии нескольких вычисляемых элементов или формул можно изменить порядок их вычисления. Изменение формулы сводной таблицы Перед изменением формулы определите, где она находится: в вычисляемом поле или в вычисляемом элементе. Если формула находится в вычисляемом элементе, определите, является ли она единственной для этого элемента. Учитывайте, что в вычисляемых элементах можно изменять отдельные формулы в конкретных ячейках. Для определения, где находится формула: в вычисляемом поле или вычисляемом элементе можно использовать команду Вывести формулы на вкладке Параметры в группе Вычисления нажмите кнопку Поля, элементы и наборы. Для изменения формулы вычисляемого поля или элемента следует: − открыть соответствующее окно Вставка вычисляемого поля (или объекта), − выбрать вычисляемое поле (или элемент), для которого необходимо изменить формулу, в поле с раскрывающимся списком Имя, − нажать кнопку Изменить и внести правку в формулу. Удаление формулы сводной таблицы Удаление формулы сводной таблицы является необратимой операцией. Если формулу не требуется удалять окончательно, можно скрыть поле или элемент, перетащив его за пределы отчета. Определите, где хранится формула: в вычисляемом поле или вычисляемом элементе. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые элементы отображаются как элементы в других полях. Выполните одну из следующих процедур: − чтобы удалить вычисляемое поле, щелкните в любом месте отчета сводной таблицы. − чтобы удалить вычисляемое поле, в отчете сводной таблицы щелкните поле, содержащее элемент, который требуется удалить. При этом на экране появится элемент «Работа со сводными таблицами» с добавленными вкладками Параметры и Проектирование. На вкладке Параметры в группе Вычисления нажмите кнопку Поля, элементы и наборы и выберите команду Вычисляемое поле или Вычисляемый объект. В поле Имя выберите требуемое поле или элемент и нажмите кнопку Удалить. 55 Создание сводной таблицы на основе данных, находящихся в нескольких диапазонах Если данные, которые вы намерены проанализировать имеют одинако- вые заголовки строк и столбцов, но размещены в разных списках, на разных листах или в разных файлах, можно использовать метод создания сводной таблицы на основе данных, находящихся в нескольких диапазонах консолидации. В версиях до 2003 включительно в мастере сводных таблиц была опция «построить сводную по нескольким диапазонам консолидации». В версиях Excel 2007/2010 эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц, и она доступна только через настраиваемую кнопку «Мастер сводных таблиц» (Pivot Table Wizard), которую можно добавить на панель быстрого доступа. Содержание работы Основная часть работы состоит в решении практических задач, использующих возможности сводных таблиц Excel для анализа данных. Порядок выполнения Построение сводной таблицы 1. Постройте сводную таблицу, показанную в книге Список мебели.xlsx на листе Сводная таблица. 2. Добавьте промежуточные итоги по каждому году. Для этого следует на вкладке Конструктор в группе Макет воспользоваться кнопкой Промежуточные итоги и выбрать команду Показывать все итоги в нижней части группы (пример показан на листе Сводная таблица_Итоги). Создание вычисляемого поля в сводной таблице Откройте книгу Список мебели_Сводная таблица_вычисляемое поле.xlsx. На листе Сводная_поле изучите построенную сводную таблицу. Предположим, что необходимо постоянно отслеживать остаток продукции на складе. Для этого необходимо вычесть из поля кол-во на складе поле кол-во продано. Результат будет храниться в новом (вычисляемом) поле сводной таблицы. Чтобы создать такое поле, выполните следующие действия: 1. Поместите курсор в область сводной таблицы. При этом на ленте появится вкладка Работа со сводными таблицами с добавленными вкладками Параметры и Конструктор. 2. На вкладке Параметры в группе Вычисления нажмите кнопку Поля, элементы и наборы и выберите команду Вычисляемое поле 56 (Calculated Field). Появится диалоговое окно Вставка вычисляемого поля (Insert Calculated Field in…). 3. В поле Имя введите имя данного поля – Остаток. 4. В поле Формула введите выражение, с использованием других полей: = кол-во на складе – кол-во продано. Чтобы использовать в формуле данные из другого поля, выберите это поле в списке Поля, а затем нажмите кнопку Добавить поле. 5. Щелкните на кнопке Добавить (Add) для добавления нового поля, затем на кнопке ОК, чтобы закрыть диалоговое окно. 6. Если итоги по новому полю добавились в область Названия столбцов, то для лучшей наглядности перенесите поле в область Названия строк. 7. Примерный вид итоговой таблицы можно посмотреть на листе Сводная_поле (ответ). Вставка вычисляемого элемента Откройте книгу Список мебели_Сводная таблица_вычисляемый элемент.xlsx и перейдите на лист Сводная_элемент. Сводная таблица содержит текстовое поле с именем Мебель. В эту таблицу можно добавить вычисляемые элементы (назовем их, например, Мягко и Жестко), которые будут содержать сумму объемов продаж мебели различного вида. Чтобы просуммировать данные в строках с помощью вычисляемого элемента, выполните следующие действия: 1. Поместите курсор в область сводной таблицы. В нашем случае установите курсор в любой элемент мебели. При этом на ленте появится вкладка Работа со сводными таблицами с добавленными вкладками Параметры и Конструктор. 2. На вкладке Параметры в группе Вычисления нажмите кнопку Поля, элементы и наборы и выберите команду Вычисляемый объект (Calculated Item…). Появится диалоговое окно Вставка вычисляемого элемента (Insert Calculated Item in…). 3. Введите имя вычисляемого элемента в поле с раскрывающимся списком Имя – Мягко. 4. Введите формулу для элемента Мягко в поле Формула: =диван + стул. Чтобы использовать в формуле данные элемента поля, выберите этот элемент в списке Элементы, а затем нажмите кнопку Добавить элемент (элемент должен находиться в одном поле с вычисляемым элементом). Нажмите кнопку Добавить. 5. Введите формулу для элемента Жестко в поле Формула: =стол + тумба + шкаф. Нажмите кнопку Добавить и закройте окно. 6. Примерный вид итоговой таблицы можно посмотреть на листе Сводная_элемент (ответ). 57 Все созданные вычисляемые элементы будут автоматически добавлены в сводную таблицу, причем новые элементы отобразились после элементов поля Мебель. Чтобы вставить вычисляемые поля между исходными элементами, можно перетащить их на требуемое место. Вычисляемый элемент можно создать только на основе не сгруппированных элементов. Для вычисляемых элементов можно вводить различные формулы в разные ячейки. Для этого выберите ячейку, в которой требуется изменить формулу, и измените ее в строке формул. При наличии нескольких вычисляемых элементов или формул можно изменить порядок их вычисления. Для этого на вкладке Параметры в группе Вычисления нажмите кнопку Поля, элементы и наборы и выберите команду Порядок вычислений. Выберите формулу и нажмите кнопку Вверх или Вниз. Продолжайте выполнять предыдущее действие, пока формулы не окажутся на нужных местах. Можно просмотреть все формулы, используемые в текущем отчете сводной таблицы. Чтобы отобразить список всех формул на вкладке Параметры в группе Вычисления нажмите кнопку Поля, элементы и наборы и выберите команду Вывести формулы. В результате добавиться новый лист с отчетом. Объединение данных с помощью сводной таблицы Откройте книгу Автосалон_сводная консолидация.xlsx. Требуется на листе Summary_Tab построить сводную таблицу на основе данных, расположенных на листах Jan, Feb и Mar. Для построения сводной таблицы выполните следующие действия: 1. Добавьте кнопку «Мастер сводных таблиц» (Pivot Table Wizard) на панель быстрого доступа. 2. Перейдите на лист Summary_Tab и установите курсор в ячейку E10. 3. Щелкните кнопку «Мастер сводных таблиц», откроется мастер сводных таблиц и диаграмм. 4. На первом шаге работы мастера сводных таблиц выберите опцию В нескольких диапазонах консолидации (Multiple consolidation ranges). 5. На шаге 2а укажите, должны ли создаваться в сводной таблице страничные поля. Выберите вариант Создать поля страницы (I will create the page fields). 6. На шаге 2б введите адреса диапазонов консолидации. Для этого укажите адрес диапазона (A6:C11) на листе Jan и щелкните кнопку Добавить (Add). Затем введите адреса остальных диапазонов. 7. Определите количество полей (How many page fields) равное 1 и для каждого диапазона в поле для ввода меток элементов Первое поле (Field one) укажите имена Январь, Февраль и Март соответственно. Нажмите Далее. 8. Укажите местоположение таблицы – на существующем листе. При необходимости измените адрес ячейки вывода таблицы. Нажмите 58 Готово (Finish). На листе в указанном месте построится сводная таблица. Примерный вид итоговой таблицы можно посмотреть на листе Summary_Tab_Res. Отчётность по работе После выполнения работы обучаемый представляет отчет. Отчёт должен содержать: 1. Название и цель работы. 2. Результаты решения примеров, описанных в теоретической части. 3. Результаты решения контрольных заданий. 4. Выводы по результатам работы. Контрольные вопросы 1. Перечислите основные задачи, решаемые с помощью сводных таблиц. 2. Какую роль играют при построении сводных таблиц срезы? 3. Каким образом можно добавить новое поле в сводную таблицу? 4. Каким образом можно добавить вычисляемый элемент в сводную таблицу? 5. Какие методы обычно используются при анализе данных в сводной таблице? 59 Список литературы 1. Афонин А.Ю. Макарычев П. П. Оперативный и интеллектуальный анализ данных – Пермь: ПГУ, 2010 – 142 с. 2. Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2010. – М.: Вильямс, 2011 – 464 с. 3. Джон Уокенбах. Microsoft Excel 2010. Библия пользователя – М.: Диалектика, 2013 – 912 с. 4. Джон Уокенбах. Формулы Microsoft Excel 2010. – М.: Диалектика, 2011 – 704 с. 5. Конрад Карлберг. Бизнес-анализ с использованием Excel. – М.: Вильямс, 2012 – 576 с. 6. Шумейко А.А. Интеллектуальный анализ данных (Введение в Data Mining)/ А.А. Шумейко, С.Л. Сотник. – Днепропетровск: Белая Е.А., 2012. – 212 с. 60 Страницы ← предыдущая следующая → 1 2 3 4 5 6 7