Applies ToExcel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016 Microsoft365.com «Мой Office» для iPhone
Консолидация данных представляет собой удобный способ объединения данных из нескольких источников в одном отчете. Например, если в каждом из региональных филиалов вашей компании есть сводная таблица расходов, с помощью консолидации данных можно объединить такие значения в корпоративный отчет о расходах. Такой отчет может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.
Для подведения итогов и обобщения результатов по данным в отдельных диапазонах можно консолидировать их в сводной таблице на главном листе. Диапазоны могут находиться в той же книге, что и главный лист, или в других книгах. Консолидированные данные легче обновлять и обобщать на регулярной основе или по мере необходимости.
Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».
Каждый из диапазонов данных следует преобразовать в формат перекрестной таблицы с совпадающими именами строк и столбцов для элементов, которые вы хотите объединить. В выбранные данные не следует включать итоговые строки и итоговые столбцы. В приведенном ниже примере показано четыре диапазона в формате перекрестной таблицы.
При консолидации данных можно использовать поля страницы, содержащие элементы, которые представляют один или несколько исходных диапазонов. Например, при консолидации данных бюджета отдела маркетинга, отдела продаж и производственного отдела поле страницы может содержать отдельный элемент с данными по каждому из этих отделов, а также элемент, содержащий сводные данные. В примере ниже показана сводная таблица, в которой выбраны одно поле страницы и несколько элементов.
Если велика вероятность того, что в следующий раз при консолидации данных исходный диапазон данных изменится (например, изменится число строк), рекомендуется задать имя для каждого из исходных диапазонов на разных листах. Эти имена можно использовать при консолидации диапазонов на главном листе. В случае расширения исходного диапазона перед обновлением сводной таблицы можно обновить диапазон на отдельном листе для указанного имени таким образом, чтобы включить в него новые данные.
В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).
Для консолидации нескольких диапазонов вы можете воспользоваться мастером сводных таблиц и диаграмм. В нем можно указать, сколько полей страницы будет использоваться: ни одного, одно или несколько.
Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:
-
Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
-
Щелкните стрелку рядом с панелью инструментов и выберите Другие команды.
-
Выберите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем выберите Дополнительные команды.
-
В списке Выбрать команды из выберите пункт Все команды.
-
В списке выберите мастер сводной таблицы и сводных диаграмм, нажмите кнопку Добавить, а затем нажмите кнопку ОК.
-
-
Выберите пустую ячейку (которая не является частью сводной таблицы) в книге.
-
Щелкните значок мастера таблицы и сводной диаграммы сводной таблицы на панели быстрого доступа.
-
На странице шаг 1 мастера выберите Несколько диапазонов консолидации, а затем нажмите кнопку Далее.
-
На странице мастера шаг 2a выберите Я создаю поля страницы, а затем нажмите кнопку Далее.
-
На странице Шаг 2б сделайте следующее:
-
Назад в книгу и выберите диапазон ячеек, а затем снова в мастере сводной таблицы и сводной диаграммы нажмите кнопку Добавить.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
-
В разделе Сколько полей страницы нужно? выберите 0, а затем нажмите кнопку Далее.
-
-
На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите кнопку Готово.
Чтобы включить одно поле страницы, содержащее элемент для каждого исходного диапазона, а также элемент для консолидации всех диапазонов, сделайте следующее:
-
Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
-
Щелкните стрелку рядом с панелью инструментов и выберите Другие команды.
-
Выберите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем выберите Дополнительные команды.
-
В списке Выбрать команды из выберите пункт Все команды.
-
В списке выберите мастер сводной таблицы и сводных диаграмм, нажмите кнопку Добавить, а затем нажмите кнопку ОК.
-
-
Выберите пустую ячейку (которая не является частью сводной таблицы) в книге .
-
На странице шаг 1 мастера выберите Несколько диапазонов консолидации, а затем нажмите кнопку Далее.
-
На странице шаг 2a мастера выберите Создать одностраничное поле для меня, а затем нажмите кнопку Далее.
-
На странице Шаг 2б сделайте следующее:
-
Назад в книгу и выберите диапазон ячеек, а затем снова в мастере сводной таблицы и сводной диаграммы нажмите кнопку Добавить.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
-
-
Выберите Далее.
-
На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите кнопку Готово.
Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:
-
Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:
-
Щелкните стрелку рядом с панелью инструментов и выберите Другие команды.
-
Выберите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем выберите Дополнительные команды.
-
В списке Выбрать команды из выберите пункт Все команды.
-
В списке выберите мастер сводной таблицы и сводных диаграмм, нажмите кнопку Добавить, а затем нажмите кнопку ОК.
-
-
Выберите пустую ячейку (которая не является частью сводной таблицы) в книге.
-
На странице шаг 1 мастера выберите Несколько диапазонов консолидации, а затем нажмите кнопку Далее.
-
На странице мастера шаг 2a выберите Я создаю поля страницы, а затем нажмите кнопку Далее.
-
На странице Шаг 2б сделайте следующее:
-
Назад в книгу и выберите диапазон ячеек, а затем снова в мастере сводной таблицы и сводной диаграммы нажмите кнопку Добавить.
Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.
-
В разделе Сколько полей страницы требуется? выберите количество полей страницы, которое вы хотите использовать.
-
В группе Затем выберите диапазон в списке и укажите метку элемента в каждом из доступных окон полей. Повторите операцию для каждого диапазона для каждого из полей страницы выберите диапазон ячеек, а затем укажите его имя.
Пример
-
Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».
-
Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».
-
-
Выберите Далее.
-
-
На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите кнопку Готово.
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В мире данных существует необходимость интеграции различной информации в обобщенный отчет. Если данные расположены на разных страницах документа, задача их интеграции кажется особенно сложной. Однако, современные инструменты позволяют сделать это эффективно и быстро, что значительно облегчает анализ и обработку большой объемной информации.
Процесс объединения информации начинается с подготовки. Важно определить, какие данные будут включены в итоговый отчет, и как будет осуществляться их консолидация. Для этого существует несколько методов, которые помогают интегрировать разные источники в единое целое. Каждый метод имеет свои особенности и подходит для определенных сценариев.
Один из главных инструментов для слияния данных – формулы. Например, функция VLOOKUP или MATCH может быть использована для поиска и объединения данных по ключевым значениям. Это позволяет не только интегрировать информацию, но и обеспечить точность и актуальность итоговых отчетов, что особенно важно для аналитиков.
Основы работы с данными в Excel
Работа с данными требует внимательности и организации. Поддержание порядка в информации позволяет легко их объединять и делать эффективным анализ. Изначально важно правильно организовать ваши записи, удобно структурируя и подготавливая для более сложных манипуляций.
Ниже приведены ключевые аспекты, которые помогут в начале работы с массивами чисел:
- Организация информации: Используйте фильтры для сортировки или выделения необходимых пунктов в наборе данных. Это сделает процесс анализа быстрым и минимизирует ошибки.
- Использование формул: При помощи встроенных возможностей можно проводить различные расчёты. Например, применяя
=SUM(B2:B10), можно вычислить сумму значений в определённом диапазоне. - Создание единой таблицы: Используйте возможность слияния данных из разных областей в одном листе. Функция
=VLOOKUP()пригодна для извлечения данных из другой таблицы на основе соответствия ключевых значений. - Форматирование: Грамотное оформление делает информацию понятной и доступной. Применяйте условное форматирование для визуального определения закономерностей и различных показателей.
Эти шаги помогут грамотно структурировать информацию и упростить их анализ. Внимательно следуйте рекомендациям, чтобы эффективно управлять вашей информацией!
Подготовка данных для сводной таблицы
Перед тем как приступить к анализу информации, важно обеспечить её корректность и упорядоченность. Подготовительный этап определяет успешность последующих шагов аналитического процесса. Внимательное отношение к исходному материалу поможет избежать ошибок и получить точные аналитические заключения.
Начните с тщательной проверки данных. Убедитесь, что все нужные значения верны и расположены в упорядоченном виде. Исходная информация должна быть четко структурирована, избегая лишних пробелов и пустот. Это основа для эффективной работы и создания аналитических отчетов.
При сведении информации из разных источников необходимо удостовериться, что все структуры данных взаимосвязаны. Например, объедините данные из различных листов, используя уникальный идентификатор, такой как ID клиента или Код товара. Это действие обеспечит целостность и связь данных между собой.
Также важно обратить внимание на одинаковый формат данных. Ваша задача – проверить, что все числовые и текстовые значения соответствуют единому стандарту. Например, даты должны быть в одинаковом формате ДД/ММ/ГГГГ, а числовые данные – приведены к нужной точности.
Если в исходной информации присутствуют избыточные или дублирующиеся записи, их необходимо удалить или объединить. Используйте инструменты для очистки данных, такие как Удалить дубликаты и ТЕКСТ для приведения форматов данных в строках к единому виду.
Не забудьте о проверке на наличие ошибок. Аномалии могут существенно повлиять на результаты анализа. Применяйте логические формулы, такие как ЕСЛИ, ВПР или СЧЁТЕСЛИ, чтобы убедиться в корректности данных. Это позволит выявить и устранить потенциальные проблемы еще до начала аналитической работы.
Таким образом, тщательная подготовка данных – это важный шаг, который напрямую влияет на качество создаваемых аналитических отчетов. Бережное отношение к исходному материалу обеспечивает точность и эффективность последующих действий.
Объединение данных из разных листов
Иногда требуется собрать информацию из различных источников в один удобный формат, чтобы сделать анализ более простым и эффективным. Достижение этой цели позволит лучше управлять потоком данных и поддерживать их актуальность и целостность. Эти шаги помогут объединить информацию правильно, используя мощные инструменты и функции, доступные в современных табличных процессорах.
Использование функции CONCATENATE или современного аналога TEXTJOIN предоставляет возможность при необходимости собрать текстовые данные. Например, функция TEXTJOIN(, , TRUE, Лист1!A1:A10, Лист2!B1:B10) объединит текстовые значения из указанных диапазонов.
В таблице ниже перечислены ключевые шаги, необходимые для успешного комбинирования данных:
| Шаг | Описание |
|---|---|
| 1 | Подготовка данных: Убедитесь, что все данные структурированы одинаково. |
| 2 | Использование формул: Для извлечения нужных ячеек применяется функция INDIRECT. |
| 3 | Создание диапазона: Определите области для объединения с помощью OFFSET. |
| 4 | Управление резервами данных: Добавьте новые данные в существующий диапазон без необходимости повторных действий. |
Для более сложных задач можно использовать массивы и функции INDEX и MATCH, обеспечивающие доступ к данным из разных источников. Например, INDEX(Лист1!A1:C10, MATCH(Данные!A1, Лист2!A1:A10, 0), 2) извлекает элемент, соответствующий определённому критерию, из таблицы данных.
Эти техники интегрируют отдельные фрагменты информации и помогают сделать результат более доступным для понимания и анализа. Умение управлять разнообразием данных усилит ваши навыки в аналитике и сделает работу более продуктивной.
Создание сводной таблицы с Power Query
Power Query предоставляет возможность эффективного анализа и интеграции информации из различных источников в одном окне, позволяя объединять разрозненные данные для последующего анализа. Эта технология помогает сделать информационный поток управляемым и структурированным, что существенно упрощает взаимодействие с разными наборами данных.
Для объединения данных с помощью Power Query начните с импорта нужных листов. Войдите в Раздел данных и выберите Из таблицы/диапазона. После загрузки всех необходимых данных, которые хотите сделать частью анализа, откройте редактор Power Query.
Объединение разных источников в единое целое начинается с использования опции Объединить запросы во вкладке Домашняя. Выберите Объединить и укажите, с какими именно листами или таблицами хотите работать в одном запросе. В открывшемся окне укажите связи между каждым источником и укажите столбцы для объединения.
В процессе объединения данных стоит обратить внимание на порядок и типы данных. Если требуется преобразовать их, воспользуйтесь функциями преобразования Power Query, такими как Трасформация типов или Заменить значения для корректировки информации. Также доступны функции фильтрации и группировки, которые помогут подготовить данные перед объединением для наибольшей эффективности.
После всех изменений не забудьте обновить данные в основном рабочем листе. Выберите Закрыть и загрузить из редактора Power Query. Это действие делает всю объединенную информацию доступной для анализа в единой интерактивной таблице.
Использование Power Query позволяет сделать процесс сборки и анализа больших объемов данных интуитивно понятным и простым в управлении. Воспользуйтесь этими возможностями для повышения качества и быстроты своих аналитических задач.
Настройки сводной таблицы для удобства анализа
Процесс работы с аналитическими данными можно сделать значительно эффективнее, если настроить рабочую область под свои нужды. После объединения данных из разных источников важно уделить внимание тонкостям их визуализации и удобному представлению результатов для облегчения их анализа.
Чтобы повысить эффективность анализа, рекомендуется использовать фильтры. Они помогают сосредоточиться на конкретных сегментах данных, которые важны в контексте текущего исследования, исключая лишнюю информацию. Для более детальной проработки результатов можно применять функции сортировки, позволяя выделять ключевые показатели.
Также стоит обратить внимание на изменение стиля и формы представления информации. Использование цветовых схем и условного форматирования способно не только сделать данные визуально привлекательнее, но и помочь быстрее выявлять тренды и аномалии. Опциональное объединение графиков или диаграмм с таблицами позволяет значительно расширить возможности анализа, превращая массив данных в наглядное и легко интерпретируемое представление.
Пользовательская настройка оформления и структуры рабочих элементов предоставляет возможность адаптации под индивидуальные требования аналитика, что значительно облегчает процесс обработки данных и их эффективное применение в дальнейших проектах.
Преимущества использования сводных таблиц в Excel
С их помощью пользователи могут быстро и эффективно обрабатывать большие объемы информации, сохраняя при этом гибкость в анализе. Главная цель – преобразование необработанных данных в информативные отчеты, что позволяет увидеть полную картину происходящего.
Часто специалисты сталкиваются с необходимостью объединения отдельных наборов данных. Это может быть информация о продажах за разные периоды или данные из различных департаментов компании. В Excel все это возможно в один отчёт, который затем можно фильтровать, сортировать и изучать напрямую через интерактивный интерфейс.
Кроме того, инструмент позволяет выполнить расчетные операции, которые облегчают детальный анализ. Например, можно использовать формулу =SUM(), чтобы получить сумму значений в группе, или =AVERAGE(), чтобы вычислить среднее. Эти формулы помогают за считанные минуты извлекать полезные инсайты.
Одним из главных преимуществ является визуализация данных. После трансформации информации можно создавать диаграммы и графики, которые облегчают восприятие результатов и помогают легче оценивать показатели.
Благодаря этим функциям у пользователей появляется возможность быстро адаптироваться к изменениям, улучшать качество анализа и в конечном итоге повышать производительность. Методы постижения массивов информации становятся существенно проще, снижая вероятность ошибок.
Комментарии
Наверняка в вакансиях вы видели похожее требование: «Знание Excel на уровне продвинутого пользователя (ВПР, сводные таблицы, формулы)». Звучит сложно, но на самом деле освоить эти функции довольно легко. Начнем со сводных таблиц: разберемся, что это такое, зачем они нужны и как их делать.
Что такое сводная таблица Excel и почему это удобно
Сводная таблица в Excel — это мощный инструмент для анализа и обработки больших объемов данных. Он помогает понять, как данные соотносятся друг с другом, выявить тенденции, закономерности, сгруппировать их по выбранным критериям и показать результат. Таблица позволяет быстро создать сводный отчет из сотен тысяч строк данных за несколько кликов.
Сводные таблицы создал разработчик Пито Салас в 1986 году. Разрабатывая программу для работы с электронными таблицами Lotus Improv, он начал замечать в данных закономерности, которые возникали при объединении разных таблиц. Он понял, что это может быть полезным инструментом для анализа данных, и создал сводные таблицы. Microsoft добавила функциональность сводных таблиц в Excel только в 1994 году. С тех пор на сводных таблицах держится многое в самых разных отраслях: финансах, маркетинге, продажах, закупках.
Например, маркетолог составляет маркетинговый план, в котором подробно описывает продвижение в разных каналах. Чтобы руководитель мог быстро и легко оценить эффективность этого плана, маркетолог создает сводную таблицу, которая объединяет информацию из всех задействованных документов. В этой таблице можно сравнить доходы, расходы и эффективность разных каналов в одном месте, без необходимости переключаться между документами.
Сводные таблицы можно использовать даже для анализа личного бюджета. Например, чтобы посмотреть, сколько денег потратили на каждую категорию товаров в течение года, вы можете создать сводную таблицу, где строки будут группироваться по категории товаров, а столбцы — по месяцам.
Как сделать простую сводную таблицу
Начинаем работу
Попробуем сделать простую сводную таблицу. В нашем примере будет небольшая IT-компания, которая занимается разработкой на заказ. Проектов много, поэтому без сводных таблиц не обойтись. В нашей таблице будут колонки «Название проекта», «Тип проекта», «Команда», которая занимается этим проектом, «Бюджет», который выделил заказчик, и «Срок (в месяцах)» — время, которое уйдет на выполнение проекта.
Чтобы создать сводную таблицу, перейдите в раздел «Вставка» и выберите «Сводная таблица».
Откроется окно создания сводной таблицы. По умолчанию оно выглядит так:
Выберем нужную нам таблицу. Чтобы это сделать, прописываем название листа (у нас «Проекты») и диапазон, который занимает таблица. Ячейки можно прописать вручную или выделить нужную область мышкой, тогда диапазон пропишется автоматически. Удобнее создать сводную таблицу на новом листе — так данные источника не будут вам мешать.
Нажимаем «Ок» и видим, что мы оказались на новом листе. Справа появился конструктор сводных таблиц. В основной области перечислены названия столбцов, которые были в нашей исходной таблице. Их можно выбирать — ставить галочки рядом или переносить мышкой. Фильтры помогут скрыть или отобразить нужные данные, столбцы и строки отвечают за внешний вид таблицы: данные будут разделены по столбцам или строкам соответственно, а значения нужны для вычисления данных.
Настраиваем сводную таблицу
Допустим, руководитель нашей компании хочет посмотреть, какие проекты ведут команды. Для этого он выбирает столбцы «Команда» и «Название проекта». По умолчанию они попадают в поле «Строки».
Теперь наша сводная таблица выглядит так:
Если выбрать сначала «Название проекта», а потом «Команда», таблица выглядит совсем по-другому. Порядок выбора столбцов важен.
Посмотрим на фильтры. Наша исходная таблица небольшая, команд немного, поэтому вывести всё и сразу довольно удобно. Если бы команд или проектов было больше, то такое отображение не подошло бы. Здесь выручат фильтры. Например, можно добавить «Команду» в поле «Фильтры».
Тогда все наши команды станут пунктами выпадающего меню, а проекты будут располагаться ниже и только для выбранной команды. Красиво, удобно, наглядно.
А что, если руководитель захочет посмотреть, сколько заработает каждая команда на проектах? Выбираем «Команда», выбираем «Бюджет». Обратите внимание, что «Команда», как обычно, оказалась в поле «Строки», а вот «Бюджет» попал в «Значения» автоматически, потому что в этом столбце были записаны числа.
Если мы перенесем «Команду» в «Столбцы», то увидим, что внешний вид таблицы изменился. Выбор внешнего вида зависит от личных предпочтений и от цели создания таблицы.
При работе с числовыми значениями часто применяются фильтры. Например, так наша IT-компания может узнать, какое из направлений ей приносит больше денег. Для этого нужно переместить «Тип проекта» в поле «Фильтры», а «Бюджет» сам попадет в «Значения». Теперь можно выбирать направление и смотреть, какое из них самое выгодное для компании.
Можно перенести «Тип проекта» в «Столбцы» и увидеть сразу все направления в одной таблице.
Так создаются простые сводные таблицы. Если необходимо выполнить более глубокий анализ данных, можно использовать более сложные варианты. Они позволяют работать с данными в различных измерениях, выполнять группировку, вычислять проценты, средние значения и многое другое.
Как обновить данные в сводной таблице
Что делать, если исходные данные изменились? Например, на какой-то проект назначили Team 2, а не Team 1? Перейдите в раздел «Анализ сводной таблицы» и нажмите «Обновить». Никаких подтверждающих окон не появится, но актуальные данные подтянутся в вашу сводную таблицу.
Если же ваша таблица изменилась существенно, например добавились новая строка или новый столбец, то есть изменился ее диапазон, нужно выбрать новый. Для этого перейдите в раздел «Анализ сводной таблицы» и нажмите «Источник данных». Вы окажетесь на листе с вашими исходными данными. Здесь появится окно, в котором вы сможете указать новый диапазон (или выделить нужную область мышкой). Нажмите «Ок» и продолжайте работу над сводной таблицей.
Как сделать сводную таблицу из нескольких листов
Сводные таблицы позволяют объединять данные из нескольких источников в одну таблицу для удобного анализа и сравнения. Например у вас есть информация, которая хранится на разных листах. В сводной таблице можно просматривать данные, не переключаясь между вкладками Excel.
Предположим, у нас есть еще одна таблица на другом листе — с тимлидами (руководителями команд). Компания у нас маленькая, поэтому руководители команд отвечают за проекты, которые ведет их команда.
Руководитель решает посмотреть, за сколько проектов сейчас отвечает каждый тимлид. Здесь пригодится сводная таблица. Но для начала нужно изменить наши таблицы — сделать их «умными». Для этого в разделе «Вставка» нужно выбрать «Таблица».
После этого — выделить нужный диапазон или прописать расположение данных вручную. Обязательно выбирайте опцию «Таблица с заголовками». Так вы сможете изменить название таблицы с системного «Таблица1» на свое.
Делаем это для обеих таблиц. Теперь они выглядят так:
После этого создаем сводную таблицу для наших исходных данных: таблицы с проектами. Обязательно ставим галочку в окошке «Добавить эти данные в модель данных». Мы видим уже привычный конструктор сводных таблиц. Но как же связать данные из наших двух таблиц? Обратимся к конструктору справа. Перейдем в раздел «Все».
Увидим обе наши таблицы! «Проекты» и «Тимлиды» — это названия, которые мы придумали.
Кликаем на каждый, раскрываем списки и видим названия столбцов. Чтобы посмотреть нагрузку на тимлидов, в списке «Проекты» мы выберем столбец «Название проекта», в списке «Тимлиды» — столбец «Тимлид». Разместим их в полях «Столбцы», чтобы придать таблице нужный вид, и в поле «Значение», чтобы автоматически посчитать количество проектов. У нас появляется желтое поле вверху.
Если выбрать «Автообнаружение», Excel сам поймет, что мы хотим сделать. Но при сложных процедурах это может не сработать. Выберем «Создать» и сделаем все вручную.
Открылось окошко создания отношений. В «Таблица» выберем основную таблицу (с проектами), в «Связанная таблица» — второй лист (с тимлидами). В «Столбец» и «Связанный столбец» должны быть одни и те же столбцы. Чтобы связать две таблицы, в них должен быть общий столбец, по которому и будет создано отношение. У нас это «Команда».
Вуаля! После нажатия «Ок» увидим желаемую таблицу и поймем, что тимлид Петров А. загружен меньше всех и мы можем дать ему еще проектов!
Полезные функции сводной таблицы
Расскажем про несколько функций, которые могут быть полезны при работе со сводными таблицами.
Вычисляемые поля
Эта функция нужна для дополнительных математических действий с данными сводной таблицы в Excel. Допустим, в компании принято, что 3% от всего бюджета команды идет на ее расходы: пятничные посиделки с пиццей, подарки на дни рождения и прочие маленькие радости. Это тоже можно посчитать в сводных таблицах. Сделаем распределение бюджетов по командам, так же как в прошлом примере.
В разделе «Анализ сводной таблицы» нужно найти выпадающее меню «Поля, элементы и наборы». Кликаем и выбираем «Вычисляемое поле».
Появляется окно, в котором можно написать имя нового столбца, у нас это «Расходы команды», и выбрать столбцы, которые понадобятся для расчета нового. Мы выбираем «Бюджет» (он сам подставится в поле «Формула») и дописываем умножение на 0,03 (3% от бюджета).
После нажатия «Ок» в нашей сводной таблице появился новый столбец. Все посчитано!
Рекомендуемые таблицы
Рядом с иконкой создания сводной таблицы есть и другая опция — «Рекомендуемые сводные таблицы». Эта функция предлагает шаблоны сводных таблиц для данных. Будет полезна тем, кто начинает работать со сводными таблицами. Можно не только сделать то, что нужно, но и посмотреть, в каких полях конструктора расположены разные столбцы, и глубже понять принцип работы сводных таблиц.
Подводя итог
В этой статье мы рассмотрели, как создать сводную таблицу в Excel. Если что-то не получилось сразу, не расстраивайтесь, попробуйте еще раз. И через некоторое время вы сможете сказать: «Я люблю Excel!».
Напоследок несколько советов, которые помогут вам использовать сводные таблицы более эффективно:
- Продумайте, какие данные вы хотите проанализировать. Что вы хотите узнать? Какая информация вам нужна для этого?
- Выберите правильные поля для вашей сводной таблицы. Они определяют, как будут группироваться ваши данные и какие вычисления будут выполняться.
- Используйте фильтры и сортировку, чтобы настроить свою сводную таблицу. Фильтры и сортировка могут помочь вам сосредоточиться на конкретных данных или увидеть данные в новом свете.
- Добавьте диаграммы и графики к своей сводке. Они помогут визуализировать данные и сделать их более понятными.
А если захотите пойти дальше в освоении Excel — можете познакомиться с курсом «Excel + Google-таблицы».
Как ни странно, но начиная с Excel 2007 пропала очевидная возможность создания сводной таблицы из нескольких листов. В Excel 2003 это можно было сделать во время создания простой сводной (Меню— Данные— Отчет сводной таблицы и диаграммы), выбрав в меню «Из нескольких диапазонов консолидации». Хоть на офф.сайте Microsoft для версии Excel 2007 и выше и предлагают сделать это при помощи клавиш: ALT+D+P у меня данное сочетание не вызвало никакой реакции со стороны самого Excel ни на русской раскладке, ни на английской. Скорее всего локализация ОС и Офиса так же имеет значение. Но я не стал далее ничего пытаться делать, т.к. знаю более короткий путь, чем изменение локализации. Чтобы создавать сводную из нескольких диапазонов для начала необходимо добавить кнопку на панель быстрого доступа:
- Excel 2010-2021— Файл (File)— Параметры (Options)— Панель быстрого доступа (Quick Access Toolbar)
- Excel 2007— Кнопка офис— Параметры Excel (Excel options)— Панель быстрого доступа (Quick Access Toolbar)
или непосредственно с панели быстрого доступа:
В списке «Выбрать команды из:» выбираем Все команды(All Commands) и ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard). Кнопкой Добавить(Add) переносим на панель быстрого доступа:
Забегая вперед скажу, что это совсем не та сводная, что на основе одного листа: нет толковых и понятных заголовков столбцов; набор функций ограничен; да и вообще функционал скуден по сравнению с обычной сводной(например, нет группировки дат и значений). Может быть поэтому в Microsoft посчитали правильным убрать данный пункт с глаз, чтобы не вводить в заблуждение.
Но тем не менее, несмотря на все недочеты, для кого-то возможность создания подобных таблиц может оказаться очень даже полезной. Например, если необходимо проанализировать данные по продажам за несколько лет и каждый год на отдельном листе или в отдельном диапазоне, как на рисунке ниже:
Жмем кнопку Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard)(которую мы только что добавили на панель быстрого доступа) — появится мастер создания сводных таблиц и диаграмм.
На Первом шаге необходимо указать в нескольких диапазонах консолидации(Multiple consolidation ranges):
Далее на Втором шаге Excel предложит создать одно поле страницы или создать свои поля страниц. Пока что совершенно неясно что это такое, но придется поверить мне на слово и выбрать второй пункт — Создать поля страницы (I will create the page fields)
Далее мы поймем зачем нам это и как использовать.
Третьим шагом необходимо будет указать все диапазоны, из которых планируется создать сводную (диапазоны могут быть как на одном листе, так и на разных листах и даже в разных книгах):
Количество полей страниц указываем 1 (How mane page fields do you want?). Становится активным окно для указания имени поля (Field one). Даем имена для полей, поочередно выделяя их в списке диапазонов:
- Лист3!$A$2:$N$6 — указываем 2013 год
- Лист3!$A$8:$N$11 — указываем 2012 год
- Лист3!$A$13:$N$17 — указываем 2011 год
Именно для этого мы и выбирали на втором шаге пункт с созданием своих полей страниц — чтобы можно было задать понятное имя каждому диапазону. Далее мы увидим зачем понятные имена и что будет, если на втором шаге выбрать другой пункт. И хоть создать можно будет не более 4-х полей страниц — как правило для большинства задач и одного хватает «за глаза», т.к. нужно это обычно лишь для того, чтобы задать понятные наименования для полей страницы.
Жмем Далее (Next), выбираем место создания сводной таблицы (существующий или новый лист), Готово (Finish).
Получаем более-менее привычный вид сводной таблицы:
С одной стороны мы вроде как работаем теперь со сводной таблицей, а с другой — есть нюансы, на которые стоит обратить внимание:
- На рисунке выше столбец Менеджер расположен в области значений и нет возможности перенести его в область строк или столбцов. Можно лишь убрать его из отображения, выключив в фильтре поля Столбец. Все дело в том, что сводные таблицы, созданные на основе нескольких диапазонов консолидации, содержат только три базовых области: Строка, Столбец и Значение. Поле Строка всегда создается на основе первого столбца указанного диапазона. Поле Столбец содержит все, что расположено правее первого столбца и комбинирует столбцы всех диапазонов в одно поле заголовков. Все, что правее области строк и ниже области столбцов — область значений.
- Т.к. столбец Менеджер состоит из текстовых данных — все поля значений нашей сводной по умолчанию будут отображать данные по количеству строк, а не суммы. Это легко изменить, поменяв функцию поля на Сумма(правая кнопка мыши в любой ячейке поля значений -Итоги по -Сумма). Однако после этого столбец Менеджер будет содержать нули и отобразить там конкретные фамилии менеджеров не получится никак потому, что этот столбец трактуется сводной изначально как значения и не может содержать текст. Это уже куда более печально, чем функция по умолчанию.
Эти моменты следует учитывать при подготовке данных для построения сводной таблицы на основе нескольких диапазонов консолидации.
Так для чего же мы давали понятные имена полям?
Скрины ниже продемонстрируют это более чем наглядно. Если перейти в поле фильтра и раскрыть его — там мы увидим как раз эти самые поля:
Имена элементов соответствуют тем значениям, которые мы задали на третьем шаге: 2011 год, 2012 год, 2013 год.
Если бы на втором шаге мы выбрали Создать одно поле страницы (Create a single page field for me), то получили бы малопонятные названия в фильтре Страниц (Объект1(Item1), Объект2(Item2), Объект3(Item3)):
Согласитесь, не очень информативно в нашем случае.
Кстати, сами поля страниц можно использовать для более гибкого анализа. Например, переместить их в область строк для анализа данных наглядно по годам:
В подобных случаях как раз и может понадобиться добавление более одного поля в область страниц.
Сами заголовки Страница1, Строка и Столбец можно переименовать в более удобные и понятные названия. Для этого необходимо лишь выделить ячейку с названием элемента и вписать нужный текст через строку формул.
Подводя итог я бы сказал, что совершенно не напрасно этот пункт был убран из основного меню создания сводных. Т.к. полноценной сводной это назвать нельзя даже с натяжкой. И полноценный анализ при помощи данной таблицы сделать вряд ли удастся. Но тем не менее возможность создания такой сводной все же лучше, чем ничего. Ведь сводную можно простым кликом мыши обновлять при внесении изменений в исходные данные — а это уже куда лучше, чем при каждом изменении собирать все заново.
Хотя я сам обычно советую сначала применить некий код (вроде того, что описан в статье Как собрать данные с нескольких листов или книг?), после чего на основе собранных данных создать нормальную сводную таблицу со всеми её возможностями и полным функционалом. Или действовать через базы данных(особенно, если данных очень много): Сводная из базы данных Access
СОЗДАНИЕ ПОЛНОЦЕННОЙ СВОДНОЙ ТАБЛИЦЫ ИЗ НЕСКОЛЬКИХ ЛИСТОВ
Но есть и более экзотический метод создания сводной из нескольких листов — через подключения. Создается подключение на языке запросов SQL, запрос подсовываем в кэш сводной — и готово, у нас есть сводная из нескольких листов со всем её функционалом.
'--------------------------------------------------------------------------------------- ' Module : mPTFromMultipleSheets ' DateTime : 07.08.2014 21:43 ' Author : The_Prist(Щербаков Дмитрий) ' http://www.excel-vba.ru ' Purpose : Процедура создания сводной таблицы из нескольких листов ' http://www.excel-vba.ru/chto-umeet-excel/svodnaya-tablica-iz-neskolkix-listov/ '--------------------------------------------------------------------------------------- Option Explicit Sub PTFromMultipleSheets() Dim oPTCache As PivotCache, oPT As PivotTable Dim sPath As String, sWbFulName As String, sTmpFileName As String Dim avSheets Dim sCols As String, sQuery As String, sCon As String Dim rRes As Range Dim li As Long sPath = ThisWorkbook.Path sWbFulName = ThisWorkbook.FullName If Right(sPath, 1) <> "\" Then sPath = sPath & "\" sTmpFileName = sPath & "TempWbForDB_" & Format(Now, "yyyymmddhhmmss") & ".xls" 'сюда можно добавить еще листы avSheets = Array("План", "Факт") 'например: Array("Январь", "Февраль", "Март", "Апрель") 'ниже перечисляются заголовки столбцов, на основе которых строится сводная 'столбцы могут быть в разном порядке, но иметь одинаковые заголовки sCols = "[Отделение],[Статья Расходов],[Сумма]" 'sCols = "*" ' - если необходимо включить все столбцы 'при этом шапка на всех листах должна быть полностью одинаковая, кол-во столбцов одинаковое 'данные будут в том порядке, в котором расположены столбцы Application.ScreenUpdating = False If Val(Application.Version) > 11 Then DelCon Set rRes = ThisWorkbook.Sheets(1).Cells rRes.Clear ThisWorkbook.Worksheets(avSheets).Copy With ActiveWorkbook .SaveAs sTmpFileName .Close End With 'создаем строку запроса For li = LBound(avSheets) To UBound(avSheets) If li > 0 Then sQuery = sQuery & " UNION SELECT " & sCols & " FROM [" & avSheets(li) & "$]" Else sQuery = "SELECT " & sCols & " FROM [" & avSheets(li) & "$]" End If Next li 'сначала создаем подключение к временному файлу 'это поможет избежать ошибок подключения к открытому файлу sCon = _ "ODBC;DSN=Excel Files;DBQ=" & sTmpFileName & ";" & _ "DefaultDir=" & sPath & ";DriverId=790;" & _ "MaxBufferSize=2048;PageTimeout=5" Set oPTCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal) With oPTCache .Connection = sCon .CommandType = xlCmdSql .CommandText = sQuery Set oPT = .CreatePivotTable(rRes(3, 1)) End With 'теперь изменяем в запросе сводной путь к файлу на текущий sCon = _ "ODBC;DSN=Excel Files;DBQ=" & sWbFulName & ";" & _ "DefaultDir=" & sPath & ";DriverId=790;" & _ "MaxBufferSize=2048;PageTimeout=5" ThisWorkbook.PivotCaches(1).Connection = sCon With oPT 'выставляем первоначальные настройки для сводной With .PivotFields(1) .Orientation = xlRowField .Position = 1 End With With .PivotFields(2) .Orientation = xlRowField .Position = 2 End With .AddDataField .PivotFields("Сумма"), "Сумма по полю Сумма", xlSum End With 'удаляем временный файл Kill sTmpFileName Set oPT = Nothing: Set oPTCache = Nothing Application.ScreenUpdating = True End Sub '--------------------------------------------------------------------------------------- ' Procedure : DelCon ' Purpose : Процедура удаляет подключения ' Требуется только для версий, выше 2003 '--------------------------------------------------------------------------------------- Private Sub DelCon() On Error Resume Next: ThisWorkbook.Connections(1).Delete: On Error GoTo 0 End Sub
Я специально привел пример именно с ODBC. Если строить на основе ADO(в сводную можно подставить результат запроса через ADO), то сводная хоть и будет полноценной, но при любом изменении в источнике данных придется перестраивать сводную заново. Что не очень удобно, если данных много и уже свою структуру сводной построили.
А такая сводная(через ODBC) имеет прямую связь с источником данных, т.к. подключение хранится в кэше сводной. Поэтому если данные на листах изменятся — надо будет лишь стандартно обновить сводную:
- выделить любую ячейку сводной таблицы →Правая кнопка мыши →Обновить(Refresh)
- или вкладка Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh).
Пара важных замечаний(ложек дегтя, если угодно):
- таблицы на листах должны иметь все перечисленные в запросе столбцы. Столбцы на листах могут располагаться в разном порядке, но иметь одинаковые заголовки. Если на одном листе заголовок столбца называется «Сумма», а на другом «Sum» — будет ошибка. Вы можете сами регулировать порядок столбцов и их кол-во, перечисляя их в запросе. За это отвечает строка:
sCols = «[Отделение],[Статья Расходов],[Сумма]»
Если в одном из листов будет отсутствовать какой-либо столбец — получите ошибку.
Если указать
sCols = «*»
То в сводную будут включены все столбцы таблиц на листах, что избавляет от процедуры их перечисления. Но в данном случае есть свои нюансы: все таблицы на листах могут иметь различное кол-во строк, но столбцы должны быть строго одинаковые — по кол-ву и по порядку. Иными словами шапка должна быть полностью идентична на всех листах. Если на каком-то листе будет лишний столбец — получите ошибку. Если на каком-то листе будет отсутствовать столбец — получите ошибку. Если имена столбцов где-то различаются — получите ошибку. - заголовки не должны содержать запятых, точек и прочих знаков препинания. Лучше использовать исключительно пробел и нижнее подчеркивание. Так же лучше давать короткие имена заголовков.
- лучше заголовки располагать в первой строке. Так же, как и для стандартной сводной — не должно быть объединенных ячеек.
- на листах не должно быть пустых строк и столбцов. Что имеется ввиду: если на листе нажать Ctrl+End, то выделяется последняя заполненная ячейка листа. По-хорошему это должна быть последняя ячейка таблицы данных. Если при нажатии на Ctrl+End выделяется пустая ячейка правее или(и) ниже таблицы данных — следует удалить эти пустые столбцы справа и строки снизу и сохранить файл.
- На листах не должно быть лишних таблиц. На одном листе должна быть только одна таблица.
- Лист для создания сводной таблицы должен находиться на первом листе. Если есть желание расположить его на каком-то другом, то надо в строке кода:
Set rRes = ThisWorkbook.Sheets(1).Cells
заменить 1 на номер листа для расположения сводной таблицы.
Количество строк в таблицах значения не имеет — оно может быть разным.
В прикрепленном файле я добавил процедуру обновления кэша без построения сводной заново. Может пригодиться, если подключение по каким-то причинам сбилось.
Скачать пример:
Tips_PT_PTFromMultipleSheets.xls (54,0 КиБ, 6 839 скачиваний)
На данный момент для пользователей Excel 2010 и новее есть еще одна возможность сбора данных с нескольких листов и файлов — при помощи бесплатной надстройки PowerQuery. Как это сделать можно почитать и посмотреть в статье: Собрать и просуммировать данные из разных файлов при помощи PowerQuery
Так же см.:
План-фактный анализ в Excel при помощи Power Query
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Собрать и просуммировать данные из разных файлов при помощи PowerQuery
#Руководства
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Подписывайтесь на наш телеграм-канал «Котлер любит котиков | Школа маркетинга и управления Skillbox». Здесь мы подкидываем идеи для нестандартной рекламы, дарим книги, обсуждаем новости и важные темы для диджитал-специалистов и руководителей.
А ещё постим мемчики и вакансии, которые могут вас заинтересовать. Например, недавно выкладывали реальную вакансию директора по расслабону с зарплатой 200 000 рублей в месяц ?
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и Ф. И. О. продавца.
Скриншот: Skillbox Media
В конце квартала планируется выдача премий. Нужно проанализировать, кто принёс больше прибыли салону. Для этого нужно сгруппировать все проданные автомобили под каждым менеджером, рассчитать суммы продаж и определить итоговый процент продаж за квартал.
Разберёмся пошагово, как это сделать с помощью сводной таблицы.
Создаём сводную таблицу
Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:
- у каждого столбца исходной таблицы есть заголовок;
- в каждом столбце применяется только один формат — текст, число, дата;
- нет пустых ячеек и строк.
Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».
Скриншот: Skillbox Media
Появляется диалоговое окно. В нём нужно заполнить два значения:
- диапазон исходной таблицы, чтобы сводная могла забрать оттуда все данные;
- лист, куда она перенесёт эти данные для дальнейшей обработки.
В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».
Скриншот: Skillbox Media
Excel создал новый лист. Для удобства можно сразу переименовать его.
Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.
Скриншот: Skillbox Media
Настраиваем сводную таблицу и получаем результат
В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».
Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:
- «Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
- «Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
- «Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.
Настроить сводную таблицу можно двумя способами:
- Поставить галочку напротив нужного поля — тогда Excel сам решит, где нужно разместить это значение в сводной таблице, и сразу заберёт его туда.
- Выбрать необходимые для сводной таблицы поля из перечня и перетянуть их в нужную область вручную.
Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.
В случае с нашим примером нужно, чтобы сводная таблица отразила Ф. И. О. менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.
Таблица получится наглядной, если фамилии менеджеров мы расположим построчно. Находим в верхней части панели поле «Продавец», зажимаем его мышкой и перетягиваем в область «Строки».
После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.
Скриншот: Skillbox
Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».
В левую часть листа добавился второй блок. При этом сводная таблица сама сгруппировала все автомобили по менеджерам, которые их продали.
Скриншот: Skillbox Media
Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.
Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».
Скриншот: Skillbox Media
Теперь мы видим, какие автомобили продал каждый менеджер, сколько и по какой цене, — сводная таблица самостоятельно сгруппировала всю эту информацию. Более того, напротив фамилий менеджеров можно посмотреть, сколько всего автомобилей они продали за квартал и сколько денег принесли автосалону.
По такому же принципу можно добавлять другие поля в необходимые области и удалять их оттуда — любой срез информации настроится автоматически. В нашем примере внесённых данных в сводной таблице будет достаточно. Ниже рассмотрим, как настроить фильтры для неё.
Настраиваем фильтры сводной таблицы
Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».
В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.
Скриншот: Skillbox Media
Для примера отфильтруем данные по году выпуска: настроим фильтр так, чтобы сводная таблица показала только проданные авто 2017 года.
В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:
Скриншот: Skillbox Media
В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.
Скриншот: Skillbox Media
Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.
Скриншот: Skillbox Media
Фильтры можно выбирать и удалять как удобно — в зависимости от того, какую информацию вы хотите увидеть в сводной таблице.
Проводим дополнительные вычисления
Сейчас в нашей сводной таблице все продажи менеджеров отображаются в рублях. Предположим, нам нужно понять, каков процент продаж каждого продавца в общем объёме. Можно рассчитать это вручную, а можно воспользоваться дополнениями сводных таблиц.
Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».
Скриншот: Skillbox
Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.
Скриншот: Skillbox Media
Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.
Скриншот: Skillbox Media
Чтобы снова раскрыть данные об автомобилях — нажимаем +.
Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».
Обновляем данные сводной таблицы
Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.
Скриншот: Skillbox
В сводную таблицу эти данные самостоятельно не добавятся — изменился диапазон исходной таблицы. Поэтому нужно поменять первоначальные параметры.
Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».
Скриншот: Skillbox Media
Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».
Скриншот: Skillbox Media
После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.
Скриншот: Skillbox Media
Когда в исходной таблице нужно изменить информацию в рамках текущего диапазона, данные в сводной таблице автоматически не изменятся. Нужно будет обновить их вручную.
Например, поменяем цены двух автомобилей в таблице с продажами.
Скриншот: Skillbox Media
Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».
Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».
Скриншот: Skillbox Media
Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:
Скриншот: Skillbox Media
Бесплатный курс: «Excel и „Google Таблицы“ для всех»
Начать учиться