Сводные таблицы — это механизм отчетности, встроенный в Excel. Это лучший инструмент в Excel для анализа данных без формул. Вы можете создать базовую сводную таблицу примерно за одну минуту и начать интерактивный анализ ваших данных.
Ниже приведены более 20 советов, как получить максимальную отдачу от этого гибкого и мощного инструмента.
1. Как построить Сводную таблицу за одну минуту?
Многие люди думают, что создание сводной таблицы сложно и отнимает много времени, но это просто неправда. По сравнению с тем, сколько времени вам потребуется для создания эквивалентного отчета вручную, сводные таблицы невероятно быстрые. Если у вас есть хорошо структурированные исходные данные, вы можете создать сводную таблицу менее чем за минуту.
Начните с выбора любой ячейки в исходных данных:
Пример исходных данных
Далее выполните следующие четыре шага:
- На вкладке «Вставка» ленты нажмите кнопку «Сводная таблица».
- В диалоговом окне «Создание сводной таблицы» проверьте данные и нажмите кнопку «ОК».
- Перетащите поле (например, Товар) в область «Строки»
- Перетащите числовое поле в область «Значения» (например, Продажи).
Базовая сводная таблица за 30 секунд
Сводная таблица выше показывает общий объем продаж по продуктам, но вы можете легко изменить порядок полей, чтобы показать общий объем продаж по регионам, категориям, месяцам и т.д.
2. Какие должны быть идеальные исходные данные?
Чтобы минимизировать проблемы в будущем, всегда используйте исходные данные хорошего качества, организованные в виде таблицы. «Идеальные» исходные данные выглядят так:
- не содержат пустых строк или столбцов
- не содержат промежуточных итогов
- каждый столбец имеет уникальное имя (только в одной строке)
- каждое поле имеет значение в каждой строке
- столбцы не содержат повторяющиеся группы данных (например, названия месяцев, названия регионов и т.д.).
Идеальные исходные данные для сводной таблицы
3. Как проверить правильно ли работает Сводная таблица?
Когда вы создаете сводную таблицу в первый раз, используйте ее сначала для генерации простого счета, чтобы убедиться, что сводная таблица обрабатывает данные в соответствии с вашими ожиданиями.
Для этого просто добавьте любое текстовое поле в качестве поля Значение.
Вы увидите очень маленькую сводную таблицу, в которой отображается общее количество записей, то есть общее количество строк в ваших данных.
Если это число имеет смысл для вас, с таблицей можно работать. Если число не имеет смысла, возможно, сводная таблица неправильно читает данные или данные не были определены правильно.
300 имен означает, что у нас 300 сотрудников. Проверьте.
4. Как правильно спланировать работу со сводной таблицей?
Хоть это и очень увлекательно перетаскивать поля сводной таблицы и наблюдать за тем, как Excel выдает еще одно необычное представление данных, можно легко обнаружить множество непроизводительных кроличьих ям. Через час станет уже не так весело.
Прежде чем приступить к созданию таблицы, запишите, что вы пытаетесь измерить или понять, и нарисуйте несколько простых отчетов в блокноте. Эти простые заметки помогут вам пропустить огромное количество вариантов, которые есть в вашем распоряжении. Не усложняйте и сосредоточьтесь на вопросах, на которые вам нужно ответить.
5. Как создать «динамический диапазон»?
Если вы используете таблицу Excel для исходных данных вашей сводной таблицы, вы получите очень приятное преимущество: ваш диапазон данных становится «динамическим».
Динамический диапазон автоматически расширяет и сжимает таблицу при добавлении или удалении данных, поэтому не нужно беспокоиться о том, что в сводной таблице отсутствуют последние данные.
Когда вы используете таблицу, как исходник, сводная таблица всегда будет синхронизирована с вашими данными.
Чтобы использовать таблицу для сводной таблицы:
- Выберите любую ячейку в данных с помощью сочетания клавиш Ctrl + T, чтобы создать таблицу
- Нажмите кнопку Сводная таблица на вкладке Конструктор
- Создайте свою сводную таблицу
- Преимущество: данные, которые вы добавляете в таблицу, автоматически отображаются в сводной таблице при обновлении.
Создание простой таблицы из данных с помощью (Ctrl+T) 
Теперь, когда у нас есть таблица, мы можем создать сводную
6. Как используйте сводную таблицу для подсчета?
По умолчанию в сводной таблице будет учитываться любое текстовое поле. Это может пригодиться во многих ситуациях. Например, предположим, у вас есть список сотрудников и вы хотите получить их количество по отделам?
Чтобы получить разбивку по отделам, выполните следующие действия:
- Создайте сводную таблицу
- Добавьте отдел в поле Строки
- Добавьте поле Имя в качестве значения
- Сводная таблица будет отображать количество сотрудников по отделам
Распределение сотрудников по отделам
7. Как показать итоги в процентах?
Во многих сводных таблицах вам нужно показывать процент, а не количество. Например, возможно, вы хотите показать разбивку продаж по продуктам. Но вместо того, чтобы показывать общие продажи для каждого продукта, вы хотите показать продажи в процентах от общего объема продаж.
Предполагая, что у вас есть поле с именем Продажи в ваших данных, просто выполните следующие действия:
- Добавьте товар в сводную таблицу в поле Строки
- Добавьте Продажи в сводную таблицу в качестве значения
- Щелкните правой кнопкой мыши поле «Продажи» и установите «Дополнительные вычисления» на «% от общей суммы»
Изменение отображаемого значения на % от общего 
Сумма продаж отображается в % от общего числа
8. Как использовать сводную таблицу для построения списка уникальных значений?
Поскольку сводные таблицы суммируют данные, их можно использовать для поиска уникальных значений в поле. Это хороший способ быстро увидеть все значения, которые появляются в поле, а также найти опечатки и другие несоответствия.
Например, предположим, что у вас есть данные о продажах, и вы хотите увидеть список каждого проданного товара. Чтобы создать список продуктов:
- Создайте сводную таблицу
- Добавьте товар в поле Строки
- Сводная таблица покажет список всех товаров, которые появляются в данных о продажах
Каждый товар из данных указан в списке (включая опечатку)
9. Как создать автономную сводную таблицу?
Когда вы создали сводную таблицу из данных на том же листе, вы можете удалить данные, если хотите, и сводная таблица продолжит работать в обычном режиме. Это связано с тем, что в сводной таблице есть сводный кэш, содержащий точную копию данных, использованных для создания сводной таблицы.
- Обновите сводную таблицу, чтобы обеспечить актуальность кэша (Данные > Обновить все)
- Удалите лист, содержащий данные
- Используйте вашу сводную таблицу как обычно
10. Как сгруппировать сводную таблицу вручную?
Хотя сводные таблицы автоматически группируют данные разными способами, вы также можете группировать элементы вручную в свои собственные пользовательские группы.
Например, предположим, что у вас есть сводная таблица, которая показывает распределение сотрудников по отделам. Вы хотите еще больше сгруппировать отделы: Продажи, Закуп и Маркетинг в группу 1, а Эксплуатацию и Бухгалтерию — в группу 2. Группа 1 и группа 2 не отображаются в данных, это ваши собственные пользовательские группы.
Чтобы сгруппировать сводную таблицу в специальные группы, нужно:
- Удерживая клавишу «Ctrl», выберите каждый элемент в первой группе.
- Щелкните правой кнопкой мыши один из элементов и выберите в меню «Группа».
- Excel создает новую группу «Группа1»
- Выберите Эксплуатация и Бухгалтерия в столбце B и сгруппируйте, как указано выше.
- Excel создает другую группу, «Группа2»
Начало группировки вручную Половина ручной группировки — группа 1 выполнена Законченная группировка вручную
11. Как сгруппировать числовые данные в диапазоны?
Одна из наиболее интересных и мощных функций, которые есть в каждой сводной таблице, — это возможность группировать числовые данные в диапазоны или сегменты.
Источник: https://excelpedia.ru/bez-rubriki/svodnye-tablicy-excel
Редизайн таблицы: быстрое преобразование сводных таблиц в плоский список в Excel
Сводные таблицы также называют двумерными (2D) таблицами или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк.
Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д.
Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.
Надстройка «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:
- Редизайн сводной таблицы в список в секунды
- Преобразование сложных таблиц с многоуровневыми заголовками
- Корректный редизайн таблиц с объединёнными или пустыми ячейками
- Сохранение заголовков столбцов
- Сохранение форматирования ячеек
Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)
Добавить «Редизайн таблицы» в Excel 2019, 2016, 2013, 2010
Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).
Скачать надстройку XLTools
Как работать с надстройкой:
Как преобразовать сводную таблицу Excel в плоский список
- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
-
Выделите сводную таблицу, включая заголовки.
Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически. -
Укажите размер заголовков:
В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1 -
Укажите, следует ли поместить результат на новый или на существующий лист.
Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая). -
Нажмите ОК > Готово.
Надстройка автоматически подберёт ширину столбцов для плоского списка.
Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:
- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
-
Выделите сводную таблицу, включая заголовки.
Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически. -
Укажите размер заголовков:
- Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
- Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
- Укажите, следует ли поместить результат на новый или на существующий лист.
- Нажмите ОК > Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.
Как выполнить редизайн таблицы с пустыми ячейками
Если в вашей сводной таблице имеются пустые ячейки, то и в соотвествующие ячейки плоского списка также будут пустыми. При этом пустые значения в плоском списке не несут значимой информации для анализа. Поэтому мы рекомендуем следующее:
- Если пустые ячейки находятся в заголовке: перед редизайном заполните ячейки заголовков.
- Если пустые ячейки находятся в теле таблицы: вы можете пропустить соответствующие строки в плоском списке:
- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
- Выделите сводную таблицу, включая заголовки.
- Укажите размер заголовков.
- Отметьте флажком «Пропустить пустые значения».
- Укажите, куда поместить результат.
- Нажмите ОК > Готово.
Как выполнить редизайн таблицы с объединёнными ячейками
- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
- Выделите сводную таблицу, включая заголовки.
- Укажите размер заголовков.
-
Отметьте флажком «Дублировать значение в объединённых ячейках»:
- Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
- Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
- Укажите, куда поместить результат.
- Нажмите ОК > Готово.
- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
- Выделите сводную таблицу, включая заголовки.
- Укажите размер заголовков.
-
Отметьте флажком «Сохранить заголовки»:
- Где это возможно, надстройка продублирует заголовки из сводной таблицы.
- Категориям таблицы будет автоматически присвоен заголовок «Категория».
- Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».
- Укажите, куда поместить результат.
- Нажмите ОК > Готово.
Как выполнить редизайн таблицы с сохранением формата ячеек
- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
- Выделите сводную таблицу, включая заголовки.
- Укажите размер заголовков.
-
Отметьте флажком «Сохранить формат ячеек»:
Каждая ячейка сохранит своё форматирование в результирующем плоском списке, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д. - Укажите, куда поместить результат.
- Нажмите ОК > Готово.
Внимание: обработка больших таблиц с множеством форматов займёт больше времени.
Какие таблицы обрабатывает надстройка «Редизайн таблицы»
Редизайн таблицы по сути означает, что данные исходной таблицы копируются и преобразуются так, чтобы сформировать плоский список. Ваши исходные таблицы не изменяются. Чтобы избежать искажения данных, вместо ссылок на ячейки, функций или формул в исходной таблице, в результирующий плоский список надстройка XLTools вставляет их значения.
Термином «Таблица» в Excel часто обозначают разные понятия:
- «Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
- Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
- Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.
Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.
Появились вопросы или предложения? Оставьте комментарий ниже.
Источник: https://xltools.net/ru/excel-unpivot-table/
Сводные таблицы MS Excel
Что такое сводные таблицы?
Приходилось ли вам когда-нибудь попадать в такую ситуацию?:
Вы очень долго «рисовали» сложную таблицу с множеством строк и столбцов и большим содержанием данных, например, таблицу — отчет по продажам в разрезе Подразделений, Городов, Менеджеров, Типов клиентов, с группировкой по кварталам, по количеству и суммам, с вычислением процентов и долей… И когда уже все готово и «раскрашено», вы показываете эту таблицу, например, руководителю, а он говорит: «Все круто, вот только бы добавить сюда еще разрез по Товарным категориям, вообще было бы замечательно».
После этих слов, вы готовы его убить, а сами готовы повеситься, так как понимаете, что вам сейчас нужно потратить еще полдня, чтобы все это реализовать…
В данной статье я расскажу — как с помощью Сводных Таблиц это можно сделать быстро, буквально за несколько секунд, и при этом не переходить к членовредительству.
Как построить сводную таблицу?
Сводная таблица MS Excel — это мощный инструмент анализа данных.
Данный инструмент позволяет извлекать из большого массива данных информацию, в каком угодно разрезе, делая при этом всего несколько кликов мышкой.
В этой статье я постараюсь рассказать на простом и понятном языке, по шагам, что и как нужно делать, чтобы получить в свое распоряжение этот необычайно удобный инструмент MS Excel.
Как проводить вычисления в сводной таблице?
Сводные таблицы способны проводить вычисления, гораздо более сложные, чем простые суммирования данных. В данной статье я расскажу, как можно заставить Сводную таблицу «считать».
Как быстро преобразовать таблицу в массив для сводной таблицы?
Еще одна типичная проблема для обработки данных с помощью Сводных таблиц — это «нарисованные» руками «Мега-таблицы», которые не поддаются анализу в силу специфики их структуры. В этой статье я расскажу, как быстро преобразовать такое «горе-творчество» в массив для построения сводной таблицы.
Как быстро построить сводную таблицу из отчета 1C или SAP?
Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.
В данной статье я покажу, как можно преобразовать такой «горе-отчет» в массив для анализа с помощью Сводной таблицы.
Как отфильтровать столбец сводной таблицы?
На тренингах я часто задаю вопрос участникам: Кто знает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это невозможно! В сводных таблицах нет такой возможности! Вопрос настолько древний, что люди уже перепробовали всякие варианты и не найдя решения сделали вывод — невозможно. Хотя решение существует.
Как построить сводную таблицу по нескольким массивам (листам)?
Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?
Однако решение существует! И оно не очень сложное.
Генератор примеров (массивы, таблицы, отчеты).
Когда я начинал читать тренинги по MS Excel то «стер» пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы «Сводные таблицы».
Решил я эту проблему просто — создал надстройку, которая мне эти примеры генерировала. Когда люди увидели это «чудо» на очередном тренинге они очень сильно «возбудились».
Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для «студента».
Источник: https://e-xcel.ru/index.php/zaklinaniya/svodnye-tablitsy
Загрузка...
