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

Здравствуйте!  

  Подскажите, пожалуйста.  

  Например, у меня есть сводная таблица на листе 2, строки и столбцы которой у меня отсортированы в нужном мне порядке и последовательности. На листе 1 соответственно находятся исходные данные для моей сводной. В дополнение, на лист с исходными данными добавлен макрос, который автоматически пересчитывает сводную, если исходные данные меняются.  

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

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

  Заранее большое спасибо!

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

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

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

Так к чему это я? Если вы хотите прокачать свои навыки Excel, нужно стать круче в сводных таблицах. И лучший способ — иметь список советов и приемов, которые вы точно сможете освоить. Давайте начнем.

Сначала прочтите это

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

Подготовка исходных данных для сводной таблицы

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

1. Нет пустых столбцов и строк в исходных данных

Один из пунктов, которые вы должны контролировать в исходных данных, — это отсутствие пустых строк и столбцов. Если в исходнике есть пустая строка или столбец, то при создании сводной таблицы Excel будет принимать данные только до этой строки или столбца.

Поэтому убедитесь, что вы удалили эту пустую строку или столбец.

2. Нет пустых ячеек в столбце значений

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

Основная причина этой проверки в том, что если у вас есть пустая ячейка в столбце поля значений, Excel будет применять счет (количество) в сводной вместо суммы значений.

3. Данные должны быть в правильном формате

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

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

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

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

1. Выберите ваши данные целиком или любую из ячеек.
2. Нажмите сочетание клавиш Ctrl + T.
3. Нажмите ОК.

Excel преобразует ваши данные в таблицу, а затем вы можете создать сводную таблицу с этими данными.

5. Удалить итоги из данных

И последнее, но не менее важное: обязательно удалите итоговую сумму из источника данных.

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

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

Советы, которые помогут вам при создании сводной таблицы

Как только вы привели исходные данные в порядок, создание сводной таблицы — это пара пустяков. Но…

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

1. Рекомендуемые сводные таблицы

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

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

2. Создание сводной таблицы из быстрого анализа

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

Быстрый анализ ➜ Таблицы ➜ Сводная таблица.

3. Внешняя рабочая книга как источник сводной таблицы

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

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

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

• После этого перейдите на вкладку «Выбрать подключение», нажмите «Найти другие».

• Найдите файл, который вы хотите использовать и выберите его.
• Нажмите ОК.
• Теперь выберите лист, на котором у вас есть данные.

• Нажмите ОК (дважды).

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

4. Мастер сводных таблиц и диаграмм

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

Единственное, что мне нравится в мастере, — это возможность объединить несколько таблиц перед созданием сводной таблицы.

Чтобы открыть мастер нужно:

• Щелкаем кнопку настройки панели быстрого доступа и нажимаем «Другие команды».

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

• Вот так выглядит значок после добавления

5. Поиск полей

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

Когда вы начинаете вводить текст в поле поиска, он начинает фильтровать столбцы.

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

Существует опция, которую вы можете использовать для изменения стиля окна «Поля сводной таблицы».

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

7. Порядок сортировки вашего списка полей

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

Нажмите на значок шестеренки в правом верхнем углу и выберите «Сортировать от А до Я». По умолчанию поля сортируются по исходным данным.

8. Скрыть / Показать список полей

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

Для этого вам просто нужно нажать кнопку «Список полей» на вкладке Анализ.

9. Наименование сводной таблицы

После того, как вы создадите сводную таблицу, я думаю, что вам нужно сделать следующее: назвать ее.

Для этого вы можете перейти на вкладку «Анализ» ➜ «Сводная таблица» и затем ввести новое имя.

10. Создайте сводную таблицу в Excel Online версии

Недавно в онлайн-приложении Excel (ограниченные параметры) была добавлена ​​возможность создания сводной таблицы.

Это так же просто, как создать сводную таблицу привычном Excel:

• На вкладке «Вставка» нажмите кнопку «Сводная таблица»
• Затем выберите диапазон данных источника

• Укажите лист, куда вы хотите вставить таблицу
• Нажмите ОК.

11. Код VBA для создания сводной таблицы в Excel

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

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

Форматирование сводной таблицы как PRO

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

1. Изменение стиля сводной таблицы или создание нового стиля

В сводной таблице есть несколько стандартных стилей в Excel, которые можно применить одним щелчком мыши. На вкладке Конструктор вы можете найти «Стили сводной таблицы». Раскрыв это поле, вы можете просто выбрать стиль, который вам понравится.

Если вы хотите создать новый стиль, индивидуальный, вы можете сделать это, используя опцию «Создать стиль сводной таблицы».

После того, как вы сделали свой собственный стиль, сохраните его для дальнейшего использования.

2. Сохраняйте форматирование ячеек при обновлении сводной таблицы

Это нужно делать с каждой созданной вами сводной таблицей.

• Щелкните правой кнопкой мыши по сводной таблице и перейдите к параметрам сводной таблицы

• Отметьте «Сохранять форматирование ячеек при обновлении».

Преимущество этих опций заключается в том, что при обновлении сводной таблицы вы не потеряете имеющееся у вас форматирование.

3. Отключите автоматическое обновление ширины сводной таблицы

Для этого перейдите в «Параметры сводной таблицы» и снимите флажок «Автоматически изменять ширину столбцов при обновлении».

Нажмите ОК после этого.

4. Повторите подписи элементов

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

• Выберите сводную таблицу и перейдите на вкладку «Конструктор».
• Найдите кнопку «Макет» ➜ «Макет отчета» ➜ «Повторять все подписи элементов».

5. Форматирование значений

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

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

6. Изменить стиль шрифта

Одна из моих любимых особенностей форматирования — это изменение стиля шрифта для сводной таблицы. Вы можете использовать опцию форматирования, но самый простой способ это сделать — перейти на вкладку «Главная».

Выберите всю сводную таблицу, а затем выберите стиль шрифта.

7. Скрыть/показать промежуточные итоги

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

В такой ситуации вы можете скрыть их, выполнив следующие действия:

• Нажмите на сводную таблицу и перейдите на вкладку Конструктор.
• Перейдите в Макет ➜ Промежуточные итоги ➜ Не показывать промежуточные суммы.

8. Скрыть / показать общий итог

Так же, как промежуточные итоги, вы можете скрывать и показывать общие итоги. Ниже приведены простые шаги, чтобы сделать это.

• Нажмите на сводную таблицу и перейдите на вкладку Конструктор.
• Выберите «Макет» ➜ «Общие итоги» ➜ «Отключить для строк и столбцов».

9. Два формата чисел в одной сводной таблице

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

Для этого вам нужно использовать пользовательское форматирование.

10. Применение темы к сводной таблице

В Excel есть стандартные цветовые темы, которые можно использовать со сводными таблицами. Перейдите на вкладку «Разметка страницы» и нажмите на выпадающий список «Темы».

Существует более 32 тем, которые вы можете применить одним щелчком мыши. Также вы можете сохранить текущий стиль форматирования в качестве темы.

11. Изменение макета сводной таблицы

Для каждой сводной таблицы можно выбрать макет. В Excel (после версии 2007) есть три разных макета.

На вкладке «Конструктор» перейдите к «Макету» ➜ «Макет отчета» и выберите макет, который вы хотите применить.

12. Чередующиеся строки и столбцы

Первое, что я делаю, когда создаю сводную таблицу, применяю «Чередующиеся строки и столбцы». Для этого перейдите на вкладку «Конструктор» и отметьте галочкой «Чередующиеся столбцы» и «Чередующиеся сроки».

Фильтрация данных в сводной таблице

Что делает сводную таблицу одним из самых мощных инструментов анализа данных? Это фильтры.

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

1. Отключить / включить фильтры

Как и с обычным фильтром, вы можете включать и выключать фильтры в сводной таблице.

На вкладке «Анализ» можно нажать кнопку «Заголовки полей», чтобы включить или выключить фильтры.

2. Сохранить отфильтрованные значения

Если вы хотите использовать значения в ячейках, полученные после применения фильтра, нужно сделать следующее:

• Примените фильтр к таблице
• Щелкните правой кнопкой мыши
• Перейдите в Фильтр
• Выберите «Сохранить только выделенные элементы

3. Скрыть выделенные элементы

Отфильтрованные значения можно не только сохранить, но и скрыть.

Для этого перейдите в «Фильтр» и после этого выберите «Скрыть выделенные элементы».

4. Фильтр по подписи и значению

Помимо обычных фильтров, вы можете использовать фильтр по подписи и значению. Можно вводить определенное значение или использовать критерии для фильтрации.

Фильтр по подписи:

Фильтр по значению:

5. Используйте фильтры подписи и значений вместе

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

• Прежде всего, откройте «Параметры сводной таблицы» и перейдите на вкладку «Итоги и фильтры».
• Отметьте «Разрешить несколько фильтров для поля».

• После этого нажмите ОК.

6. Фильтр 10 первых значений

Один из моих любимых параметров в фильтрах — фильтровать «10 лучших значений». Этот параметр фильтра полезен при создании мгновенного отчета.

Для этого вам нужно перейти к «Фильтру по значению», нажать «Первые 10», а затем нажать «ОК».

7. Фильтруйте из окна «Поля сводной таблицы»

Если вы хотите фильтровать при создании сводной таблицы, вы можете сделать это из окна «Поля сводной таблицы».

Чтобы отфильтровать значения, вы можете нажать на стрелку вниз с правой стороны и отфильтровать значения, как вам нужно.

8. Добавить срез

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

• Перейдите на вкладку «Анализ»
• В группе «Фильтр» нажмите кнопку «Вставить срез»

• После этого выберите поле, для которого вы хотите вставить срез
• Нажмите кнопку ОК.

9. Форматирование среза и других параметров

Вставив срез, вы можете изменить его стиль и формат:

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

Помимо стилей, вы можете изменить настройки: нажмите кнопку «Настройка среза», чтобы открыть окно настроек.

Изменяйте настройки, как вам нужно, и нажмите ОК в конце.

10. Один срез для всех сводных таблиц

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

Следуйте этим шагам:

• Прежде всего, вставьте срез.
• Щелкните правой кнопкой мыши на срезе и выберите «Подключения к отчетам».

• В диалоговом окне выберите все таблицы и нажмите ОК.

Теперь вы можете просто отфильтровать все сводные таблицы с помощью одного среза.

11. Добавить временную шкалу

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

Чтобы вставить шкалу, все, что вам нужно сделать:

• Перейти на вкладку «Анализ»
• В группе «Фильтр» нажать кнопку «Вставить временную шкалу».

• После этого выберите «Дата» и нажмите OK.

12. Форматирование временной шкалы

После того, как вы вставите временную шкалу, вы можете изменить ее стиль и формат.

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

Помимо стилей, вы можете изменить настройки на этой же вкладке:

13. Фильтр с использованием подстановочных знаков

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

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

14. Очистить все фильтры

Если вы применили фильтры к нескольким полям, вы можете удалить все эти фильтры на вкладке «Анализ» ➜ «Действия» ➜ «Очистить» ➜ «Очистить фильтры».

Как улучшить сводную таблицу

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

1. Обновить сводную таблицу вручную

Сводные таблицы динамичны. Поэтому, когда вы добавляете новые данные или обновляете исходные, вам необходимо обновить и сводную.

Обновить сводную таблицу очень просто:

• Первый способ: щелкните правой кнопкой мыши на таблице и выберите «Обновить».

• Второй способ: перейдите на вкладку «Анализ» и нажмите кнопку «Обновить».

2. Обновлять сводную таблицу при открытии файла

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

• Прежде всего, щелкните правой кнопкой мыши на сводной таблице и перейдите к «Параметры сводной таблицы».
• После этого перейдите на вкладку «Данные» и отметьте галочкой «Обновить при открытии файла».

• В конце нажмите ОК.

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

3. Обновлять данные через определенный интервал времени

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

Как это сделать?

• Прежде всего, при создании сводной таблицы в окне «Создание сводной таблицы» установите флажок «Добавить эти данные в модель данных».

• Как только вы создадите сводную таблицу, выберите любую из ячеек и перейдите на вкладку «Анализ».
• Далее выберите «Данные» ➜ «Источник данных» ➜ «Свойства подключения».

• Теперь в «Свойствах подключения» на вкладке «Использование» отметьте галочкой «Обновлять каждые» и введите минуты.

• В конце нажмите ОК.

Теперь после определенного периода, который вы ввели, ваша сводная будет автоматически обновляться.

4. Заменить ошибки значением

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

• Прежде всего, щелкните правой кнопкой мыши на своей сводной таблице и откройте ее параметры.
• Теперь в «Макет и формат», отметьте «Для ошибок отображать» и введите значение в поле ввода.

• Нажмите ОК.

Теперь вместо ошибок у вас будет указанное вами значение.

5. Замените пустые ячейки

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

Для этого нужно выполнить:

• Щелкните правой кнопкой мыши на вашей сводной таблице и откройте ее параметры.
• В «Макет и формат», отметьте галочкой «Для пустых ячеек отображать» и введите значение в поле ввода.

• В конце нажмите ОК.

Теперь вместо пустых ячеек у вас будет указанное вами значение.

6. Определите числовой формат

Чтобы быстро поменять формат чисел, необходимо сделать следующее:

• Щелкните правой кнопкой мыши на вашей сводной таблице
• Теперь выберите «Числовой формат»

• В открывшемся окне найдите нужный вам формат
• Нажмите ОК

7. Добавьте пустую строку после каждого элемента

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

• Выберите сводную таблицу и перейдите на вкладку «Конструктор».
• Далее «Макет» ➜ «Пустые строки» ➜ «Вставить пустую строку после каждого элемента».

8. Перетащите элементы в сводную таблицу

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

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

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

Допустим, если у вас есть 4 продукта в сводной, вы можете создать 4 различных рабочих листа одним щелчком мыши.

Как это сделать:

• Выберите свою таблицу и перейдите на вкладку Анализ
• Перейдите в сводную таблицу ➜ Параметры ➜ Отобразить страницы фильтра отчета

Теперь у вас есть четыре сводные таблицы на четырех отдельных листах.

10. Вариант расчета стоимости

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

• Выберите ячейку в столбце значений и щелкните правой кнопкой мыши
• Откройте «Параметры поля значений»

• Выберите тип расчета из списка, который вы хотите отобразить в сводной таблице.

11. Столбец с нарастающим итогом в сводной таблице

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

Вот шаги:

• Добавьте Сумму два раза в поле Значение
• Щелкните правой кнопкой мыши и выберите «Параметры поля значений».
• В раскрывающемся списке «Дополнительные вычисления» выберите «С нарастающим итогом в поле».

• В конце нажмите ОК.

12. Добавить ранги в сводную таблицу

Ранжирование дает вам отличный способ анализа. Для вставки столбца ранга в сводную таблицу необходимо:

• Прежде всего, вставьте одно и то же поле данных дважды в сводную таблицу.
• Щелкните по столбцу второго поля нему правой кнопкой мыши и откройте «Параметры поля значений».
• Перейдите на вкладку «Дополнительные вычисления» и выберите «Ранг от самого большого до самого маленького».

• Нажмите ОК.

13. Добавьте долю в процентах

У вас есть сводная таблица с данными продаж. Вы хотите рассчитать процентную долю всех продуктов в общем объеме продаж.

Для этого нужно:

• Вставьте одно и то же поле данных дважды в сводную таблицу.
• Щелкните по столбцу второго поля правой кнопкой мыши и откройте «Параметры поля значений».
• Перейдите на вкладку «Дополнительные вычисления» и выберите «% от общей суммы».

• Нажмите ОК.

14. Переместите сводную таблицу на новый лист

При создании сводной таблицы Excel может добавить ее на существующий или новый лист. А что если нужно переместить уже существующую таблицу на новый лист?

Для этого перейдите на вкладку «Анализ» ➜ Действия ➜ Переместить.

15. Отключить GetPivotData

Бывают ситуации, когда вам нужно сослаться на ячейку в сводной. При этом может возникнуть проблема, потому что когда вы ссылаетесь на ячейку в сводной таблице Excel, автоматически используйте функцию GetPivotData. Лучше всего отключить ее, и вот шаги:

• Перейдите на вкладку «Файл» ➜ «Параметры».
• Далее к пункту «Формулы» ➜ «Работа с формулами» и снимите флажок «Использовать функции GetPivotData для ссылок в сводной таблице».

Вы также можете использовать код VBA для этого:

Sub deactivateGetPivotData()
Application.GenerateGetPivotData = False
End Sub

16. Группы даты в сводной таблице

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

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

Используйте следующие шаги:

• Сначала вам нужно вставить дату в качестве элемента строки в сводную таблицу.

• Щелкните правой кнопкой мыши на сводной таблице и выберите «Группировать…».

• Выберите «Месяц» и нажмите «ОК».

Excel сгруппирует все даты в месяцы.

17. Группировка числовых данных

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

• Щелкните правой кнопкой мыши на сводной таблице и выберите «Группировать…».
• Введите значения для создания диапазона групп, поле «с шагом» и нажмите «ОК».

18. Группировка столбцов

Чтобы сгруппировать столбцы так же, как строки, вы можете использовать те же шаги. Но вам нужно сначала выбрать заголовок столбца.

19. Разгруппировать строки и столбцы

Если вам не нужны группы в сводной таблице, вы можете просто разгруппировать ее, щелкнув правой кнопкой мыши и выбрав «Разгруппировать».

20. Использование расчета в сводной таблице

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

В этой ситуации вместо создания отдельного столбца в сводной таблице можно вставить вычисляемое поле.

21. Список используемых формул

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

Для этого просто перейдите на вкладку «Анализ» ➜ Вычисления ➜ Поля, элементы и наборы ➜ Вывести формулы.

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

22. Получить список уникальных значений

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

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

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

23. Показать элементы без данных

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

• Щелкните правой кнопкой мыши на поле и откройте «Параметры поля».
• Перейдите в «Разметка и печать» и отметьте «Отображать пустые элементы» и нажмите «ОК».

Все элементы, у которых нет данных, будут показаны в сводной таблице.

24. Отличие от предыдущего значения

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

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

Вот шаги:

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

• После этого для второго поля откройте «Параметры поля значений» и «Дополнительные вычисления».
• Теперь из выпадающего списка выберите «Отличие» и выберите «Кварталы» и «(назад)» в Элемент.
• В конце нажмите ОК.

Excel мгновенно преобразует столбец значений в столбец с отличием от предыдущего.

25. Отключить отображение деталей

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

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

26. Сводная таблица в PowerPoint

Ниже приведены простые шаги для вставки сводной таблицы в слайд PowerPoint.

• Выберите сводную таблицу и скопируйте ее.
• После этого перейдите к слайду PowerPoint и откройте Специальную вставку.

• Теперь в диалоговом окне выберите «Объект Microsoft Excel» и нажмите «ОК».
• Чтобы внести изменения в сводную таблицу, необходимо дважды щелкнуть по ней.

27. Добавить сводную таблицу в документ Word

Чтобы добавить сводную таблицу в Microsoft Word, необходимо выполнить те же действия, что и в PowerPoint.

28. Развернуть / свернуть заголовки полей

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

Вам нужно нажать на кнопку «+», чтобы развернуть, и кнопку «-«, чтобы свернуть.

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

29. Скрыть / показать кнопки

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

30. Считать только числа

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

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

31. Сортировка элементов

Да, вы можете сортировать по соответствующим значениям. Все, что вам нужно сделать:

• Открыть фильтр и выбрать «Дополнительные параметры сортировки».

• Затем выбрать «по возрастанию (от A до Я) по полю:»

• Выбрать столбец для сортировки и нажать «ОК».

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

32. Пользовательский порядок сортировки

Да, вы можете использовать пользовательский порядок сортировки для вашей сводной таблицы. Для этого:

• Откройте «Дополнительные параметры сортировки»

• Нажмите «Дополнительно»

• Снимите флажок «Автоматическая сортировка при каждом обновлении отчета».

• После этого выберите порядок сортировки и нажмите ОК в конце.

Вам необходимо создать новый пользовательский порядок сортировки, а затем создать его на вкладке «Файл» ➜ «Параметры» ➜ «Дополнительно» ➜ «Общие» ➜ «Создать списки для сортировки и заполнения: Изменить списки».

33. Отсроченный макет

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

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

34. Изменение имени поля

Когда вы вставляете поле значения, имя, которое присваивается полю, выглядит примерно так: «Сумма по полю» или «Количество по полю».

Но иногда (на самом деле, всегда) вам нужно изменить это имя на «Сумма» и «Количество». Для этого все, что вам нужно сделать, это удалить все кроме слов «Количество» или «Сумма» из ячейки и добавить пробел в конце имени.

35. Выберите всю сводную таблицу

Если вы хотите выбрать всю сводную таблицу сразу:

Выберите любую из ячеек в сводной таблице и используйте сочетание клавиш Ctrl + A.

Или же…

Перейдите на вкладку «Анализ» ➜ Выделить ➜ Всю сводную таблицу.

36. Преобразовать в значения

Если вы хотите преобразовать сводную таблицу в значения, вам нужно сделать следующее:

• Выбрать всю сводную таблицу
• Используйте Ctrl + C, чтобы скопировать ее
• Затем вставьте с помощью Специальной вставки только значения.

37. Используйте сводную таблицу на защищенном листе

Когда вы защищаете лист, на котором у вас есть сводная таблица, поставьте галочку: «Использовать сводную таблицу и сводную диаграмму» из раздела «Разрешить всем пользователям этого листа:».

38. Дважды щелкните, чтобы открыть настройки поля значения

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

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

Сводные таблицы являются одним из наиболее эффективных и простых способов создания отчетов. И мы довольно часто делимся отчетами с другими.

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

1. Уменьшите размер отчета сводной таблицы

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

Так какой в ​​этом смысл?

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

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

2. Удалите исходные данные

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

А если кому-то понадобятся исходные данные, можно получить их, щелкнув по общей сумме сводной таблицы.

3. Сохраните сводную таблицу как веб-страницу [HTML]

Еще один способ поделиться сводной таблицей с кем-то — это создать веб-страницу. Да, простой HTML-файл со сводной таблицей. Для этого:

• Сохраните рабочую книгу, как веб-страницу .

• В окне «Публикация веб-страницы» выберите сводную таблицу и нажмите «Опубликовать».

Теперь вы можете отправить эту HTML-страницу любому, и получатель сможет просматривать сводную таблицу (не редактируемую) даже на своем мобильном телефоне.

4. Создание сводной таблицы с помощью рабочей книги с веб-адреса

Допустим, у вас есть веб-ссылка на файл Excel, как показано ниже:

https://excelpedia.ru/book1.xlsx

В этой книге у вас есть данные, из которых вам необходимо создать сводную таблицу. Ответ к этой задаче: POWER QUERY.

Прежде всего, перейдите на вкладку «Данные» ➜ «Получение внешних данных» ➜ Из Интернета.

• Теперь в диалоговом окне «Из Интернета» введите веб-адрес книги и нажмите «ОК».
• После этого выберите рабочий лист и нажмите «Импорт».
• Далее выберите отчет сводной таблицы и нажмите ОК.

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

Условное форматирование в сводной таблице

Для меня условное форматирование — это разумное форматирование. Я думаю, вы согласны с этим. Но когда дело доходит до сводной таблицы условное форматирование работает магия.

1. Применение общих параметров УФ

Есть много опции Условного форматирования, доступные для использования со сводной таблицей.

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

2. Выделите 10 лучших значений

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

Нужны следующие шаги:

• Выберите любую из ячеек в столбце значений в сводной таблице.
• Перейдите на вкладку «Главная» ➜ «Стили» ➜ «Условное форматирование».
• Теперь в условном форматировании перейдите в раздел «Правила отбора первых и последних значений» ➜ Первые 10 элементов.

• Выберите цвет в окошке.

• В конце нажмите ОК.

3. Удалить Условное форматирование из сводной таблицы

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

• Выберите любую из ячеек в сводной таблице.
• Перейдите на вкладку «Главная» ➜ «Стили» ➜ «Условное форматирование» ➜ «Удалить правила» ➜ «Удалить правила из этой сводной таблицы».

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

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

Я большой поклонник сводной диаграммы. Если вы знаете, как правильно использовать сводную диаграмму, в ваших руках один из лучших инструментов Excel.

Вот некоторые приемы, овладев которыми вы сможете стать профи сводной диаграммы в кратчайшие сроки.

1. Вставка сводной диаграммы

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

• Выберите ячейку из сводной таблицы и перейдите на вкладку «Анализ».
• На вкладке «Анализ» нажмите «Сводная диаграмма».

Excel мгновенно создаст сводную диаграмму из имеющейся у вас сводной таблицы.

2. Создание гистограммы с использованием сводной диаграммы и сводной таблицы

Сводная таблица и сводная диаграмма — мой любимый способ создания гистограммы в Excel.

3. Отключите кнопки из сводной диаграммы

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

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

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

4. Добавить сводную диаграмму в PowerPoint

Следуй этим простейшим шагам, чтобы вставить сводную диаграмму в слайд PowerPoint.

• Выберите сводную диаграмму и скопируйте ее.
• После этого перейдите к слайду PowerPoint и откройте Специальную вставку.

• Теперь в диалоговом окне специальной вставки выберите «Объект Диаграмма Microsoft Excel» и нажмите «ОК».

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

Сочетания клавиш для работы со сводной таблицей

Мы все любим горячие клавиши. Я прав?

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

1. Создать сводную таблицу

Alt + N + V

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

2. Сгруппируйте выбранные элементы сводной таблицы

Alt + Shift + стрелка вправо

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

3. Разгруппировать выбранные элементы сводной таблицы

Alt + Shift + стрелка влево

Точно так же, как вы группировали ячейки, это сочетание поможет вам разгруппировать эти элементы.

4. Скрыть выбранный элемент или поле

Ctrl + —

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

5. Откройте окно вычисляемого поля

Ctrl + Shift + =

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

6. Откройте список полей активной ячейки

Alt + Стрелка вниз

Это сочетание открывает список полей.

7. Вставьте сводную диаграмму из сводной таблицы

Alt + F1

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

F11

И, если вы хотите вставить диаграмму на новый лист, нужно использовать только вышеуказанную клавишу.

Заключение

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

Чтобы лучше усвоить материал, сначала начните внедрять хитрости в свою работу как минимум с 10 советов, а затем переходите к следующим.

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

Создание сводной таблицы для анализа данных листа

​Смотрите также​ не только текущей,​ таблицами​ же статье будет​ «Продажи». Здесь мы​Гораздо сложнее сделать сводную​

​ОК​Данные не должны содержать​

Создание сводной таблицы

1. ​ подробно описано, как​Чтобы сгруппировать данные помесячно:​ по регионам и​

   ​Часть 1: Что такое​​Сумма по полю​Нажмите кнопку​ окне​ область​ а фактические данные​

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

   

3. ​ таблицу на основе​​.​​ пустых строк.​​ создать сводную таблицу​​Кликните правой кнопкой мыши​

   

4. ​ по продавцам. Процесс​​ сводная таблица в​​, а поле​

5. ​ОК​​Параметры поля​строк​​ не изменяются.​​ инструмент для вычисления,​​ связанных таблиц:​ кнопками (+ и​ сводной таблицы. В​ получит магазин от​​ разных по структуре​​Сводная таблица будет заполнена​Кликаем кнопку​ в Excel. Данная​

6. ​ по любой дате​​ создания этой сводной​​ Excel?​

Настройка сводной таблицы

1. ​Заменить​. Excel создаст пустую​на вкладке​, поля значений даты​Если у вас недостаточно​​ сведения и анализа​​Если попытаться забросить в​

   ​ -). Эти вспомогательные​​ двух словах можно​ реализации товара. Воспользуемся​ исходных таблиц. Например,​ итогами продаж по​​Сводная таблица​​ статья написана для​ в крайнем левом​ таблицы описан ниже.​​Часть 2. Создаём простейшую​​оставить пустым.​ сводную таблицу и​​Дополнительные вычисления​​ и времени — в​

   

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

​ описать ее главную​

• ​ формулой – цена​ таких:​ каждому продавцу, как​(Pivot Table) в​ версии Excel 2007​ столбце сводной таблицы;​Выделите любую ячейку в​

• ​ сводную таблицу в​Дополнительные вычисления​ выведет список​.​ область​ сводными таблицами или​ поиск сравнений, закономерностей​ из разных таблиц,​ можно включить/отключить через​ особенность как инструмент​​ за 1 *​​Первая таблица – приход​ показано на рисунке​ разделе​ (а также для​В появившемся контекстном меню​

• ​ диапазоне или весь​ Excel?​Значения также можно выводить​Поля сводной таблицы​Отображение значения как результата​столбцов​ вы не знаете,​

• ​ и тенденций.​ то в списке​ меню: Работа со​ для создания разного​ количество проданных единиц.​

Создание сводной таблицы

​ товара. Вторая –​ выше.​Таблицы​ более поздних версий).​ нажмите​ диапазон данных, который​​Часть 3: Группировка в​​ в процентах от​.​ вычисления и как​, а числовые поля —​ с чего начать,​Windows macOS Online​ полей появится подсказка​ сводными таблицами»-«Параметры»-«Показать»-«Кнопки» или​ рода отчетов из​Переходим на лист с​ количество проданных единиц​Если необходимо отобразить объемы​(Tables) на вкладке​ Инструкции для более​Группировать​

​ нужно использовать для​ сводной таблице.​ значения поля. В​

Работа со списком полей сводной таблицы

​ в любые поля​​ подходящий макет, сопоставляя​​Примечание:​Если вы строите сводную​ по полу в​ и практике.​ диапазон информации, которая​ чтобы проиллюстрировать остатки,​ Самый простой способ​​ окно​​ в Excel 2003?​ (как показано на​ данных, то Excel​​Дальнейшее углублённое обучение работе​​.​ в сводную таблицу.​​ правой кнопкой мыши​​ сводной таблицы. Если​ данные с наиболее​ Ваши данные не должны​ с использованием Data​ ячейке I1.​Создайте таблицу исходных данных​ должна войти в​ продажи по магазинам,​ сделать это –​Создание сводной таблицы​В качестве примера рассмотрим​ рисунке ниже). В​ автоматически определит диапазон​ со сводными таблицами​Вы можете настроить такие​

Значения в сводной таблице

• ​ подходящими областями в​

  ​ содержать пустых строк​ Object Model, то​На вкладке​​ о клиентах фирмы,​​ сводную таблицу.​ выручку и т.п.​​ выделить ячейки, формат​​(Create PivotTable), как​ следующую таблицу, в​ поле​​ для создания сводной​​ можно найти на​ параметры в диалоговом​ поля добавляются в​Параметры поля​ нужен, просто перетащите​ сводной таблице. Это​ или столбцов. Они​ в списке функций​Вставка (Insert)​ так как показано​Если бы мы добавили​​Мастер сводных таблиц при​​ которых нужно настроить,​

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

  ​(By) выберите​​ выделение. Для того,​Урок подготовлен для Вас​Параметры поля значений​​строк​​ параметры​ списка полей или​ точку для дальнейших​ однострочный заголовок.​ (правой кнопкой мыши​Рекомендуемые сводные таблицы (Recommended​Теперь по исходной базе​ таблицы, достаточно было​​ выдаст ошибку. Так​​Денежный​​Убедитесь, что выбранный диапазон​​ за первый квартал​​Месяцы​​ чтобы Excel выбрал​​ командой сайта office-guru.ru​​на вкладке​​, поля значений даты​​Суммировать по​

• ​ снимите его флажок.​

  ​ экспериментов. После создания​На вкладке​ по полю -​ Pivot Tables)​ данных клиентов построим​ обновить сводную таблицу.​​ как нарушено одно​​(Currency) в разделе​

  

  ​ соответствует диапазону ячеек,​ 2016 года:​(Months). Кстати, сгруппировать​​ диапазон правильно, должны​​Источник: http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html​​Дополнительные вычисления​​ и времени — в​

• ​и​ Возможность перестановки элементов —​ рекомендуемой сводной таблицы​

  ​Вставка​Параметры поля​​, предлагающая несколько подходящих​​ сводную таблицу, в​После изменения диапазона в​ из главных условий​​Число​​ который должен быть​A B C​​ даты и время​​ быть выполнены следующие​​Перевел: Антон Андронов​​.​ область​

Обновление сводных таблиц

​Дополнительные вычисления​ одна из особенностей​ вы можете изучить​нажмите кнопку​) добавится еще одна новая​ сводных таблиц разного​ которой можно легко​​ сводке появилось поле​​ консолидации – одинаковые​(Number) на вкладке​ использован для создания​​ D E 1​​ можно и по​ условия:​Автор: Антон Андронов​диалоговое окно «Дополнительные​столбцов​для каждой из​​ сводной таблицы, благодаря​​ различные ориентации и​​Сводная таблица​​ — ​ типа на выбор​​ отобразить средний возраст​​ «Продажи».​​ названия столбцов.​​Главная​

Удаление сводной таблицы

​ сводной таблицы. Здесь​ 2 3 4​ другим временным периодам,​Каждый столбец в диапазоне​​В этом разделе самоучителя​​ вычисления»» />​, а числовые поля —​ копий.​ которой можно быстро​ изменить порядок полей​.​Число различных элементов (Distinct Count)​ в зависимости от​ всех клиентов фирмы,​Иногда пользователю недостаточно данных,​Но два заголовка в​(Home) Ленты меню​

​ же можно указать,​ 5 6 7​ например, по кварталам,​ данных должен иметь​

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

1. ​В разделе​. Она вычислит не​

2. ​ вида исходных данных:​​ которые не имеют​​ содержащихся в сводной​​ этих таблицах идентичны.​​ Excel (как показано​

3. ​ куда должна быть​ 8 9 10​​ дням, часам и​​ уникальный заголовок.​ как создать продвинутую​ вычисления и как​

   

4. ​значений​​ в источник необходимо​ ее вид.​​ результатов.​​Выберите данные для анализа​​ общее количество непустых​​Для тех, кто точно​​ дисконтную скидку c​​ таблице. Менять исходную​​ Поэтому мы можем​ ниже).​ вставлена создаваемая сводная​

   ​ 11​​ так далее;​В диапазоне данных не​ сводную таблицу в​

5. ​ процента​​. Вы также можете​​ обновить все сводные​Список полей сводной таблицы​Вы также можете скачать​​установите переключатель​​ элементов, как обычная​

Работа со списком полей сводной таблицы

​ знает, что хочет​​ распределением на:​​ информацию не имеет​ объединить данные, а​В результате сводная таблица​ таблица. Можно выбрать​Date​Нажмите​ должно быть пустых​​ современных версиях Excel​​Просто перетащите элемент в​ вручную перетаскивать элементы​ таблицы, созданные на​​Соответствующие поля в сводной​​ интерактивный учебник Создание​Выбрать таблицу или диапазон​​ функция​​ увидеть в своем​возраст;​ смысла. В таких​ потом создать сводный​ примет вот такой​ существующий лист, чтобы​Invoice Ref​ОК​ строк.​ (2007 и более​ раздел​ в любые поля​ его основе. Чтобы​ таблице​ первой сводной таблицы.​

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

• ​образование;​

  ​ ситуациях лучше добавить​ отчет.​ вид:​​ вставить на него​​Amount​.​​Кликните по кнопке​​ новых). Для тех,​Значения​ сводной таблицы. Если​​ обновить одну сводную​​Суммировать по​Рекомендуемые сводные таблицы​В поле​, а количество неповторяющихся​ делать его самостоятельно​пол.​ вычисляемое (пользовательское) поле.​В ячейке-мишени (там, куда​сводная таблица до настройки​ сводную таблицу, либо​Sales Rep.​​Как и требовалось, наша​​Сводная таблица​

  

  ​ кто работает в​дважды, а затем​​ элемент больше не​​ таблицу, можно​По умолчанию поля сводной​Создание сводной таблицы вручную​Таблица или диапазон​ представителей.​ — штука бесполезная,​​Решение для построения сводного​​Это виртуальный столбец, создаваемый​ будет переноситься таблица)​ числового формата​ вариант –​Region​ сводная таблица (смотрите​(Pivot Table) в​​ более ранних версиях​​ настройте параметры​

  

  ​ нужен, просто перетащите​​щелкнуть правой кнопкой мыши​ таблицы, которые находятся​Щелкните ячейку в диапазоне​​проверьте диапазон ячеек.​​В приведенном выше примере​ но для начинающих​ отчета в Excel:​ в результате вычислений.​ ставим курсор. Пишем​сводная таблица после установки​На новый лист​01/01/2016​​ картинку ниже) теперь​​ разделе​​ Excel, рекомендуем статью:​​Операция​​ его за пределы​​в любом месте​​ в области​​ исходных данных и​​В разделе​​ для сравнения подсчитывается​

• ​ пользователей — приятная​

  ​Перейдите на любую ячейку​ В нем могут​ = — переходим​ денежного формата​(New worksheet). Кликаем​2016-0001​​ показывает итоги продаж​​Таблицы​

  

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

  ​ общее количество проданных​ помощь. Из готовых​

• ​ исходной клиентской базы​ отображаться средние значения,​ на лист с​

  ​Обратите внимание, что формат​ОК​​$819​​ по месяцам с​(Table) на вкладке​​ сводную таблицу в​​Дополнительные вычисления​​ снимите его флажок.​​ выбрать команду​, обобщаются с помощью​

Обновление сводных таблиц

​На вкладке​ отчет сводной таблицы​ товаров и количество​ вариантов выбрать иногда​ и выберите инструмент:​ проценты, расхождения. То​ переносимыми данными –​ валюты, используемый по​.​Barnes​​ разбивкой по регионам​

Удаление сводной таблицы

​Вставка​ Excel 2003?​для каждой из​ Возможность перестановки элементов —​Обновить​ функции​Вставка​установите переключатель​ уникальных наименований по​ проще.​ «Вставка»-«Таблицы»-«Сводная таблица».​ есть результаты различных​ выделяем первую ячейку​ умолчанию, зависит от​Появится пустая сводная таблица,​North​ и по продавцам.​

support.office.com

Сводные таблицы в Excel – самоучитель в примерах

​(Insert) Ленты меню​В качестве исходных данных​ копий.​

​ одна из особенностей​. При наличии нескольких​СУММ​нажмите кнопку​​На новый лист​ каждому городу.​К возможности фильтровать сводные​​В появившемся диалоговом окне​ формул. Данные вычисляемого​ столбца, который копируем.​ настроек системы.​

​ а также панель​01/01/2016​Чтобы улучшить вид сводной​ Excel.​ для построения сводной​При добавлении новых данных​ сводной таблицы, благодаря​ сводных таблиц сначала​. Если Excel считает​Рекомендуемые сводные таблицы​, чтобы поместить сводную​Тем, кто работает со​

​ таблицы срезами (slicers),​ переключите на опцию​ поля взаимодействуют с​ Ввод. «Размножаем» формулу,​В последних версиях Excel​Поля сводной таблицы​2016-0002​ таблицы, следует настроить​Откроется диалоговое окно​ таблицы, мы используем​ в источник необходимо​

​ которой можно быстро​ выберите любую ячейку​ данные текстом, к​.​ таблицу на новый​

• ​ сводными таблицами, известна​ появившейся в Excel​ «На существующий лист»​
• ​ данными сводной таблицы.​ протягивая вниз за​ (Excel 2013 или​
• ​(Pivot Table Field​$456​
• ​ форматирование. Например, если​Создание сводной таблицы​
• ​ таблицу данных о​ обновить все основанные​

​ и просто изменять​ в любой сводной​ ним применяется функция​»Рекомендуемые сводные таблицы»​

​ лист. Можно также​ простая, но крайне​
​ 2010, добавили​
​ и в поле​

​Инструкция по добавлению пользовательского​

office-guru.ru

Продвинутые сводные таблицы в Excel

​ правый нижний угол​ более поздних) на​ List) с несколькими​Brown​ для значений в​(Create PivotTable), как​ продажах компании в​ на нем сводные​ ее вид.​ таблице, а затем​СЧЁТ​ для автоматического создания​ выбрать вариант​

​ полезная функция: если​Временную шкалу (Timeline)​ «Диапазон» укажите значение​ поля:​ ячейки.​ вкладке​ полями данных. Обратите​

​South​ столбцах​ показано на рисунке​ первом квартале 2016​ таблицы. Чтобы обновить​Список полей сводной таблицы​

​ как в этом​ сводной таблицы.​ в области значений,​ вкладке​ таблицы» переместите значение​ сводной таблицы вычисляемое​ двух таблиц получаем​(Recommended Pivot Tables).​В панели​

1. ​Jones​настроить денежный формат,​ ячейки, которые должны​ 2 3 4​ месте ее диапазона​​По умолчанию поля сводной​​, щелкните стрелку под​ Вы можете изменить​ примере:​Нажмите кнопку​ то на отдельный​Анализ (Analysis)​ «Пол» из поля​ поле должно ссылаться.​ одну общую.​ Этот инструмент на​Поля сводной таблицы​
   • ​South​ то прочесть сводную​ быть использованы для​
   • ​ 5 6 7​ и выбрать команду​ таблицы, которые находятся​
2. ​ кнопкой​​ функцию, которая по​​Выберите сводную таблицу, которая​ОК​​ лист будет выведена​​ . Выделив любой временной​​ «Выберите поля для​​ Допустим, нам нужны​Теперь создадим сводный отчет.​
3. ​ основе выбранных исходных​​(Pivot Table Field​​01/01/2016​ таблицу станет гораздо​ создания сводной таблицы.Здесь​​ 8 9 10​Обновить​ в области​Обновить​ умолчанию используется для​ вам понравилась, и​.​ детализация по этой​ интервал на оси,​ добавления в отчет»​​ остатки по группам​​ Вставка – сводная​​ данных предлагает возможные​​ List):​​2016-0004​​ легче.​
4. ​ же можно выбрать,​ 11​​Если вам больше не​​Значения​и выберите команду​ вычислений. Для этого​ нажмите кнопку​Чтобы добавить поле в​ ячейке. Это позволяет оперативно​ мы получим в​ в поле «Фильтр​ товаров.​ таблица – указываем​ форматы сводных таблиц.​Перетаскиваем​$1,009​Фильтры в сводной таблице​​ где должна быть​​Date​ нужна сводная таблица,​, обобщаются с помощью​
   • ​Обновить все​​ щелкните стрелку справа​​ОК​​ сводную таблицу, установите​​ проверить исходные данные​
   • ​ привязанной к ней​​ отчета»:​​Работа со сводными таблицами​​ диапазон и место​​ Примеры можно посмотреть​
   • ​Sales Rep.​​Barnes​​ позволяют отобразить информацию​​ размещена создаваемая сводная​​Invoice Ref​
   • ​ просто выделите ее​​ функции​​.​​ от имени поля​​. Excel создаст сводную​​ флажок рядом с​

   

5. ​ и понять откуда получился такой​ сводной результаты именно​Таким же образом распределите​ – Параметры –​ – ОК.​ на сайте Microsoft​​в область​
   • ​North​ для одного значения​ таблица. Можно поместить​Amount​
   • ​ и нажмите клавишу​СУММ​​Если вам больше не​​ и выберите команду​
   • ​ таблицу на новом​​ именем поля в​​ результат.​ за выбранный период:​ остальные значения по​ Формулы – Вычисляемое​​Открывается заготовка Сводного отчета​​ Office.​​Строки​​01/02/2016​ или избирательно для​ сводную таблицу​Sales Rep.​ DELETE. Это не​. Если Excel считает​ нужна сводная таблица,​
   • ​Параметры поля​​ листе и выведет​​ области​

​Логическим продолжением и развитием​ ​ полям так как​ поле.​ со Списком полей,​Урок подготовлен для Вас​(Row Labels);​

​2016-0005​ нескольких значений из​На существующий лист​Region​ повлияет на другие​​ данные текстом, к​​ просто выделите ее​​.​​ список​Поля сводной таблицы​ этой идеи стала​В Excel 2013 впервые​

Фильтры в сводной таблице

​ указано выше на​В открывшемся меню вводим​ которые можно отобразить.​ командой сайта office-guru.ru​Перетаскиваем​$486​ имеющихся полей данных.​(Existing Worksheet) или​01/01/2016​ данные и сводные​ ним применяется функция​​ и нажмите клавишу​​Затем измените функцию в​Поля сводной таблицы​​.​​ новая функция​

​ реализована возможность строить​ рисунке.​​ название поля. Ставим​​Покажем, к примеру, количество​​Источник: http://www.excelfunctions.net/create-pivot-table.html​​Amount​Jones​​ Например, в показанной​​На новый лист​​2016-0001​​ таблицы или диаграммы.​

​СЧЁТ​​DELETE​​ разделе​.​Примечание:​Экспресс-просмотр (Quick Explore)​ сводные таблицы не​​Щелкните правой кнопкой мышки​​ курсор в строку​ проданного товара.​Перевел: Антон Андронов​в​​South​​ выше сводной таблице​

​(New Worksheet). Нажмите​$819​ Если сводная таблица​​. Именно почему так​​. Это не повлияет​Суммировать по​Щелкните ячейку в диапазоне​​ Выбранные поля будут добавлены​​в сводных таблицах​​ на основе классического​​ по любой ячейке​

​ «Формула». Инструмент «Вычисляемое​Можно выводить для анализа​
​Автор: Антон Андронов​
​Значения​

​01/02/2016​

office-guru.ru

Как создать простейшую сводную таблицу в Excel?

​ мы сможем просматривать​ОК​Barnes​ находится на отдельном​ важно не использовать​ на другие данные​. Обратите внимание на​ исходных данных и​ в области по​ Excel 2013.​ механизма кэша, как​ внутри сводной таблицы.​ поле» не реагирует​ разные параметры, перемещать​

​Пользователи создают сводные таблицы​(Values);​2016-0006​ данные только для​.​North​

​ листе, где больше​ разные типы данных​ и сводные таблицы​ то, что при​ таблицы.​ умолчанию: нечисловые поля —​

​Округлите все значения до​ имеет смысла. Из​ Excel не заканчивается:​ инструмент Excel позволяет​ значение​01/02/2016​ региона​(Pivot Table Field​

1. ​Brown​ Так проще всего​ умолчанию используется для​ где больше нет​ раздел​​Сводная таблица​​ и времени — в​Экспресс-просмотра​ установить в нижней​ десятых с помощью​ предполагаемого списка выбираем​ возможности инструмента многообразны.​ произвести фильтрацию и​Сумма по полю Amount​2016-0007​South​
   • ​ List), в которой​South​ избавиться от сводной​
   • ​ вычислений. Для этого​ нужных данных, вы​
2. ​Пользовательское имя​​.​​ область​. Нажав на него,​​ части диалога неприметную​​ формата ячеек.​​ категории, которые нужны​​​ группировку информации, изобразить​
3. ​(Sum of Amount),​$740​​.​​ уже содержатся несколько​01/01/2016​ таблицы.​​ щелкните стрелку справа​ можете просто удалить​(например, «Сумма по​Если вы используете Excel​столбцов​ можно выбрать нужную нам​ галочку​Обратите внимание! В ячейке​ в расчете. Выбрали​Из отчета (см.выше) мы​ ее в различных​ а не​Barnes​​Чтобы отобразить данные только​​ полей данных. Обратите​​2016-0003​​Перед Вами пошаговое руководство​
4. ​ от имени поля​ этот лист. Так​​ полю имя_поля»), но​​ для Mac 2011 или​, а числовые поля —​ таблицу (если сводная​Добавить эти данные в​ I1 мы имеем​ – «Добавить поле».​
5. ​ видим, что продано​​ разрезах (подготовить отчет).​​Количество по полю Amount​North​
   • ​ для региона продаж​​ внимание, что эти​​$538​​ по созданию сводных​​ и выберите​
   • ​ проще всего избавиться​​ вы можете изменить​​ более ранней версии,​​ в область​​ строится по нескольким​
   • ​ модель данных (Add​​ возможность указать пол​​ Дописываем формулу нужными​ ВСЕГО 30 видеокарт.​​Исходный материал – таблица​​(Count of Amount).​01/03/2016​​North​​ поля – заголовки​

   ​Jones​ таблиц в Excel.​​Параметры полей значений​​ от сводной таблицы.​ это имя. Чтобы​​ кнопка «Сводная таблица»​​значений​ таблицам) и интересующее нас​​ to Data Object​​ (мужской, женский или​ арифметическими действиями.​ Чтобы узнать, какие​​ с несколькими десятками​​В данном примере в​2016-0008​, в панели​ из таблицы исходных​South​​Мы начнём с того,​​.​Теперь сводную таблицу можно​ изменить числовой формат​ находится на вкладке​.​

   • ​ поле для детализации.​​ Model)​​ оба) для сегментирования​Жмем ОК. Появились Остатки.​​ данные были использованы​​ и сотнями строк,​ столбце​​$543​​Поля сводной таблицы​
   • ​ данных.Мы хотим, чтобы​​01/01/2016​​ что дадим ответ​Затем измените функцию в​​ вставить в электронную​​ для всего поля,​
   • ​Данные​​Чтобы переместить поле из​​ Так, например, если​

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

​Amount​Smith​(Pivot Table Field​ сводная таблица показывала​2016-0004​ на самый простой​ разделе​ таблицу в Excel​ нажмите кнопку​в группе​​ одной области в​​ я выделю ячейку​​ для построения сводной​​Полезный совет! Данная сводная​​ на товар в​​ значения, щелкаем два​ одной книге, несколько​содержатся числовые значения,​

​North​ List) перетащите поле​ итоги продаж помесячно​

​Число…​Анализ​ другую, перетащите его​ с результатами продаж​

Рекомендуемые сводные таблицы в последних версиях Excel

​ «движок» Power Pivot,​ таблица не имеет​ разные годы. Сколько​ раза мышкой по​​ файлов. Напомним порядок​​ поэтому в области​​01/03/2016​​Region​ с разбиением по​Barnes​Что же это такое​. Обратите внимание на​Важно:​.​.​

​ в целевую область.​ менеджера​
​ т.е. по-сути полноценную​
​ динамического подключения баз​

​ было затрачено средств​

office-guru.ru

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

​ цифре «30». Получаем​ создания: «Вставка» –​Σ Значения​2016-0009​в область​ регионам и по​North​ – сводные таблицы​ то, что при​

​ Не рекомендуется создавать сводные​Совет:​Появится диалоговое окно​Подготовка​Иванова​ базу данных, встроенную​ данных к исходной​ в 2012, 2013,​ детальный отчет:​

​ «Таблицы» – «Сводная​(Σ Values) будет​$820​Фильтры​

Как сделать сводную таблицу из нескольких файлов

​ продавцам. Для этого​01/02/2016​ в Excel?​ изменении метода вычисления​ таблицы и работать​ Так как при изменении​Создание сводной таблицы​Данные должны быть представлены​и выберу поле​ в Excel:​ таблице клиентов. Поэтому​ 2014 и 2015.​Если мы изменим какой-либо​ таблица».​

​ по умолчанию выбрано​Brown​(Report Filters).​ в панели​2016-0005​» – и далее​ Excel автоматически добавляет​ над ними в​ способа вычисления в​, в котором указан​

​ в виде таблицы,​Заказчик​ ​ любое изменение в​

​ Группировка по дате​ параметр в исходной​А в данной статье​Сумма по полю Amount​South​

​Поле​Поля сводной таблицы​$486​ покажем, как в​ его название в​ электронной таблице одновременно​ разделе​ ваш диапазон или​ в которой нет​для детализации:​

​Именно объектная модель позволяет​ исходной таблице не​ в сводной таблице​ таблице либо добавим​ мы рассмотрим, как​(Sum of Amount).​…​

​Region​(Pivot Table Field​Jones​ Excel создать простейшую​ раздел​ с другими пользователями.​

1. ​Суммировать по​ имя таблицы. В​ пустых строк или​… то получу на​ сделать один из​ обновляются автоматически в​ Excel выполняется следующим​ новую запись, в​ работать со сводными​ Если же в​…​появится вверху сводной​
2. ​ List) сделайте вот​South​ сводную таблицу.​Пользовательское имя​Выделите таблицу или диапазон​
3. ​обновляется имя поля​ этом случае мы​ столбцов. Рекомендуется использовать​ выходе новую сводную​ самых впечатляющих трюков​

​ сводной таблице. Поэтому​ образом. Для примера​ сводном отчете эта​

​ таблицами в Excel.​ столбце​

​…​ таблицы. Откройте выпадающий​ что:​01/02/2016​Далее будет показано, как​(например, «Сумма по​ в электронной таблице.​

​ сводной таблицы, не​

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

​ используем таблицу «таблица_СемейныеРасходы».​ таблицу Excel, как​ таблицу с подробными​ этой версии -​ после каждого изменения​ сделаем простую сводную​ информация не отобразится.​Первый этап – выгрузить​Amount​Для начала создадим очень​

Как обновить данные в сводной таблице Excel?

​ список в этом​Перетащите поле​2016-0006​ создать более продвинутую​ полю имя_поля»), но​На вкладке​ рекомендуется переименовывать поля​В разделе​

​ в примере выше.​

​ результатами продаж именно​ построить сводную сразу​ исходных данных следует​

​ по дате поставки​

​ Такое положение вещей​ информацию в программу​

​будут содержаться нечисловые​ простую сводную таблицу,​ поле и выберите​Date​

1. ​$948​ двухмерную сводную таблицу​ вы можете изменить​Вставка​ сводной таблицы до​
2. ​Укажите, куда следует поместить​
3. ​Таблицы — это отличный источник​Иванова​ по нескольким диапазонам​ щелкнуть правой кнопкой​

Изменение структуры отчета

​ и сумме.​ нас не устраивает.​

1. ​ Excel и привести​ или пустые значения,​ которая покажет общий​ в нём регион​в область​Smith​ Excel. И в​ имя. Чтобы изменить​нажмите кнопку​
2. ​ завершения ее настройки.​ отчет сводной таблицы​ данных для сводных​по всем заказчикам:​ данных.​ мышки по сводной​Щелкаем правой кнопкой мыши​Обновление данных:​

​ ее в соответствие​ то в сводной​ объем продаж каждого​North​

​Строки​North​ заключение мы расскажем,​

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

​ числовой формат для​Сводная таблица​ Вместо того чтобы​установите переключатель​ таблиц, так как​Из замеченного в процессе​На вкладке​

​ таблице и выбрать​ по любой дате.​Курсор должен стоять в​ с таблицами Excel.​ таблице по умолчанию​ из продавцов по​. Сводная таблица (как​(Row Labels);​01/02/2016​

​ как сортировать сводные​ всего поля, нажмите​

1. ​.​ вручную изменять имена,​На новый лист​ строки, добавляемые в​ работы:​Данные (Data)​ опцию «Обновить». Тогда​ Выбираем команду «Группировать».​
2. ​ любой ячейке сводного​ Если наши данные​ может быть выбрано​ данным таблицы, приведённой​
3. ​ показано на картинке​Перетащите поле​2016-0007​ таблицы по полям​ кнопку​В Excel появится диалоговое​ можно выбрать пункт​или​ таблицу, автоматически включаются​В сводных таблицах построенных​появилась кнопка​ все данные выполнять​В открывшемся диалоге задаем​ отчета.​ находятся в Worde,​
4. ​Количество по полю Amount​

Группировка данных в сводном отчете

​ выше. Для этого​ ниже) покажет значения​Amount​$740​ данных, чтобы легко​Числовой формат​ окно​Найти​На существующий лист​ в сводную таблицу​ на основе Объектной​Отношения (Relationships)​ пересчет и обновятся.​

​ параметры группировки. Начальная​Либо:​ мы переносим их​

​(Count of Amount).​ необходимо сделать следующее:​ только для региона​в область​Barnes​ извлекать нужную информацию.​

​.​Создание сводной таблицы​

​(​

​. При выборе варианта​ при обновлении данных,​ модели не работает​, позволяющая связать нескольких​

exceltable.com

Обучение сводным таблицам Excel на примерах

​​ и конечная дата​Правая кнопка мыши –​ в Excel и​ Если так случилось,​Выбираем любую ячейку из​North​Σ Значения​North​ Каждый раздел самоучителя​Совет:​, в котором будет​в меню «Изменить»​На существующий лист​ а все новые​ группировка. Это хоть​ таблиц по ключевым​Теперь поменяем структуру отчета​ диапазона выводятся автоматически.​ обновить.​ делаем таблицу по​ то Вы можете​

Работа со сводными таблицами в Excel

​ диапазона данных или​.​(Σ Values);​01/03/2016​

​ проиллюстрирован примерами сводных​ Так как при изменении​ указан ваш диапазон​), в поле​вам потребуется указать​ столбцы добавляются в​ и не критично,​ полям (столбцам) с​ в той же​

• ​ Выбираем шаг –​
• ​Чтобы настроить автоматическое обновление​
• ​ всем правилам Excel​

​ изменить количество на​ весь диапазон, который​

1. ​Вы можете быстро переключиться​Перетащите поле​2016-0008​ таблиц.​
2. ​ способа вычисления в​ или имя таблицы.​Найти​ ячейку для вставки​ список​ но печально. Будем​
3. ​ помощью вот такого​ сводной таблице по​ «Годы».​ сводной таблицы при​ (даем заголовки столбцам,​ сумму следующим образом:​ будет использоваться в​ на просмотр данных​
4. ​Region​$543​Поскольку интерфейс, используемый для​ разделе​В разделе​
5. ​ввести​ сводной таблицы.​Поля сводной таблицы​ надеяться, что исправят.​ диалога:​ тем же данным.​
6. ​Получаем суммы заказов по​ изменении данных, делаем​ убираем пустые строки​

​В области​ сводной таблице.​ только для региона​в область​Smith​ создания сводных таблиц​

​Операция​Укажите, куда следует поместить​Сумма по полю​Нажмите кнопку​. В противном случае​Иногда названия столбцов в​ ​Если вы случайно закрыли​ годам.​ по инструкции:​ и т.п.).​Σ Значения​ВНИМАНИЕ:​South​Колонны​North​ в Excel 2003,​

​обновляется имя поля​

Пример настройки сводных отчетов

​ отчет сводной таблицы​, а поле​ОК​ необходимо вручную обновлять​

1. ​ списке полей не​Нюанс в том, что​ окно «Список полей​Скачать пример работы​Курсор стоит в любом​Дальнейшая работа по созданию​(Σ Values) кликаем​Если выбрать одну​– для этого​(Column Labels);​
2. ​01/03/2016​ немного отличается от​ сводной таблицы, не​

​установите переключатель​

​Заменить​. Excel создаст пустую​

​ диапазон исходных данных​ соответствуют реальности, т.е.​ подчиненная (вторая) таблица​ сводной таблицы», тогда​По такой же схеме​ месте отчета. Работа​ сводной таблицы из​ на​ ячейку из диапазона​ нужно в выпадающем​Перетащите поле​2016-0009​ более поздних версий,​

exceltable.com

Новые возможности сводных таблиц в Excel 2013

Рекомендуемые сводные таблицы

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

​ нескольких файлов будет​Количество по полю Amount​ данных, Excel автоматически​ списке в поле​Sale​$820​ мы создали два​ сводной таблицы до​или​Дополнительные вычисления​ выведет список​

Фильтрация с помощью Временной Шкалы

​ динамического именованного диапазона.​ ячеек шапки, а​ уникальные значения элементов​ мышки по сводной​​ в сводной таблице​​ – Параметры –​ зависеть от типа​(Count of Amount)​ определит и выберет​​Region​​s Rep.​Brown​ варианта 2-й и​ завершения ее настройки.​На существующий лист​Значения также можно выводить​

​Поля сводной таблицы​

Новый механизм расчета сводных таблиц (Data Object Model)

​Все данные в столбце​ непонятно откуда. Помогает​ в ключевом поле​ таблице, чтобы сделать​ по другим параметрам.​ Сводная таблица.​ данных. Если информация​ и выбираем опцию​ весь диапазон данных​выбрать​в область​South​​ 4-й частей данного​ Вместо того чтобы​. При выборе варианта​ в процентах от​​.​ должны иметь один​ предварительное форматирование исходных​ — иначе связь​ ее активной. Затем​Сводные таблицы в Excel​

​Параметры.​

​ однотипная (табличек несколько,​Параметры полей значений​ для сводной таблицы.​South​Колонны​…​ учебника. Выберите ту,​

Связи и сводная по нескольким таблицам

​ вручную изменять имена,​​На существующий лист​​ значения поля. В​​В области​​ и тот же​ диапазонов как «умных»​ создать не получится.​ включите опцию: «Работа​ – это мощный​

​В открывшемся диалоге –​

​ но заголовки одинаковые),​(Value Field Settings);​ Для того, чтобы​.​(Column Labels).​…​ которая подходит для​ можно нажать кнопку​вам потребуется указать​ приведенном ниже примере​Имя поля​ тип. Например, не​ таблиц.​ Если сначала связать две​ со сводными таблицами»-«Параметры»-«Показать»-«Список​ инструмент и чтобы​ Данные – Обновить​

​ то Мастер сводных​На вкладке​ Excel выбрал диапазон​Урок подготовлен для Вас​В итоге сводная таблица​…​ Вашей версии Excel.​Найти и заменить​

​ ячейку для вставки​

Функция подсчета количества уникальных элементов

​ мы изменили сумму​вверху установите флажки​ следует вводить даты​Для выгрузки исходных данных​ таблицы, а потом​ полей».​ описать все его​​ при открытии файла​​ таблиц – в​Операция​​ правильно, должны быть​​ командой сайта office-guru.ru​ будет заполнена ежедневными​В следующем примере мы​Рекомендуется начать с 1-й​​(​​ сводной таблицы.​ расходов на​

​ для полей, которые​ и текст в​ из Объектной модели​ начать строить сводную​Переместите значение «Пол» в​ возможности нельзя вложиться​

Экспресс-просмотр

​ – ОК.​ помощь.​(Summarise Values By)​ выполнены следующие условия:​Источник: http://www.excelfunctions.net/advanced-pivot-table.html​ значениями продаж для​ создадим сводную таблицу,​ части самоучителя и​CTRL+H​Примечание:​% от общей суммы​ вы хотите добавить​ одном столбце.​ приходится использовать команду​ по одной из​

​ поле «Название строк»​ в одну статью.​Добавим в сводную таблицу​​Мы просто создаем сводный​​ выбираем операцию​Каждый столбец в диапазоне​

​Перевел: Антон Андронов​ каждого региона и​ которая показывает итоги​ изучать пособие по​​), в поле​​ Указанная ячейка должна находиться​.​ в сводную таблицу.​Сводные таблицы применяются к​Данные — Подключения (Data​ них, то в​ под значение «Образование».​ Возможностям сводных таблиц​ новые поля:​ отчет на основе​​Сумма​​ данных должен иметь​​Автор: Антон Андронов​​ для каждого продавца,​

​ продаж помесячно за​ сводным таблицам Excel​Найти​ за пределами таблицы​​Вы можете настроить такие​​ По умолчанию нечисловые​

Недостатки

​ моментальному снимку данных,​ — Connections)​

• ​ списке полей будут​Структура отчета кардинально изменена:​ придаться посвящать целую​На листе с исходными​ данных в нескольких​(Sum);​ своё уникальное название;​
• ​В этой части самоучителя​ как показано ниже.​ год с разбивкой​ последовательно.​ввести​ или диапазона.​ параметры в диалоговом​ поля добавляются в​ который называется кэшем,​
• ​.​ отображаться заголовки столбцов​Скачать пример обучения сводными​​ книгу. В этой​ данными вставляем столбец​​ диапазонах консолидации.​

planetaexcel.ru

​Кликаем​

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

Видеоурок: Как создать сводную таблицу в Excel

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:

Она содержит данные:

• Даты заказов;
• Регион в котором расположен клиент;
• Тип клиента;
• Клиент;
• Количество продаж;
• Выручка;
• Прибыль.

Теперь, представим, что наш руководитель поставил задачу вычислить:

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.

Ниже мы разберем, как в решении этих задач нам поможет сводная таблица.

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

Для создания таблицы выполните следующие действия:

• Выделите любую ячейку в таблице с данными;
• Нажмите на вкладку «Вставка» => «Сводная таблица»:

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

• Нажмите «ОК».

После нажатия кнопки «ОК» таблица будет создана.

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

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

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

• Кэш
• Область «Значения»
• Область «Строки»
• Область «Столбцы»
• Область «Фильтры»

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

Область «Значения»

Область «Значения» включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области «Значения».

На примере выше создана таблица, в которой отражены данные продаж по регионам с разбивкой по месяцам.

Область «Строки»

Заголовки таблицы, размещенные слева от значений, называются строками. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:

Область»Столбцы»

Заголовки вверху значений таблицы называются «Столбцы».

На примере ниже красным выделены поля «Столбцы», в нашем случае это значения месяцев.

Область «Фильтры»

Область «Фильтры» используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные «Тип клиента» — «Продуктовый магазин» и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

• Какой объем выручки у региона Север за 2017 год?;
• ТОП пять клиентов по выручке;
• Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле «Значения». Как только вы представили каким образом вы видите итоговую таблицу — начинайте её создание.

В окне «Поля сводной таблицы» размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» — это место, где вы размещаете элементы таблицы.

Перенос полей из области в область представляет собой удобный интерфейс, в котором, при перемещении, данные автоматически обновляются.

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

Пример 1. Какой объем выручки у региона Север?

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

• создать сводную таблицу и поле «Регион» перенести в область «Строки»;
• поле «Выручка» разместить в области «Значения»
• задать финансовый числовой формат ячейкам со значениями.

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

• переместить поле «Клиент» в область «Строки»;
• поле «Выручка» разместить в области «Значения»;
• задать финансовый числовой формат ячейкам со значениями.

У нас получится следующая таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню «Сортировка» => «Сортировка по убыванию»:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

Для этого:

• поместим поле «Регион» в область «Строки»;
• поместим поле «Клиент» в область «Строки» под поле «Регион»;
• зададим финансовый числовой формат ячейкам со значениями.

После перемещения элемента «Регион» и «Клиент» в области «Строки» друг под другом , система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

• поле «Выручка» разместим в область «Значения».

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

Для сортировки данных выполните следующие шаги:

• кликните правой кнопкой на любой из строчек с данными выручки;
• перейдите в меню «Сортировка» => «Сортировка по убыванию»:

В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле «Регион» в область «Фильтры» и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе « Сводные таблицы в Excel«. Успей зарегистрироваться по ссылке!