100+ скрытых трюков со сводными таблицами

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

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

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

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

Инструмент быстрого анализа ➜ Таблицы ➜ Пустая сводная таблица.

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

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

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

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

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

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

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

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

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

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

Простой способ открыть этот мастер — использовать сочетание клавиш: Alt + D + P.

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

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

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

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

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

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

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

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

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

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

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

На вкладке «Вставка» нажмите кнопку «Сводная таблица» в группе таблиц…

…затем выберите диапазон исходных данных…

…и таблицу, куда вы хотите ее вставить…

…и в конце нажмите ОК.

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

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

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

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

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

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

Перейдите к параметрам сводной таблицы (щелкните правой кнопкой мыши сводную таблицу и выберите «Параметры сводной таблицы») и установите флажок «Сохранять форматирование ячеек при обновлении».

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

Помимо форматирования, вам также необходимо сохранить его, а именно «Ширину столбца».

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

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

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

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

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

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

Одна из моих любимых особенностей форматирования — изменение стиля шрифта сводной таблицы.

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

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

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

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

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

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

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

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

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

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

Для каждой сводной таблицы вы можете выбрать макет.

В Excel (если вы используете версию 2007 или выше) вы можете использовать три разных макета. На вкладке «Дизайн» перейдите в раздел «Отчет о макете» ➜ «Макет» и выберите макет, который хотите применить.

Первое, что я делаю при создании сводной таблицы, — это применяю «Фирменную строку и столбец».

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

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

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

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

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

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

Фильтр меток:

Фильтр значений:

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

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

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

Для этого вам необходимо перейти в «Фильтр значений», нажать «Топ-10», а затем нажать «ОК».

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

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

Одна из лучших вещей, которые я нашел для фильтрации данных в сводной таблице, — это использование «Среза».

Чтобы вставить срез, вам просто нужно перейти на вкладку «Анализ» и в группе «Фильтр» нажать кнопку «Вставить срез», после этого выбрать поле, для которого вы хотите вставить срез, затем нажать «ОК».

Связанный: Excel SLICER — полное руководство по фильтрации данных с его помощью

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Во-первых, щелкните правой кнопкой мыши по сводной точке и выберите «Обновить».
• Во-вторых, перейдите на вкладку «Анализ» и нажмите кнопку «Обновить».

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

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

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

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

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

…Вот как это сделать.

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

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

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

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

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

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

Лучший способ — заменить эти ошибки значимым значением.

Ниже приведены шаги, которые необходимо выполнить:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ознакомьтесь с этими шагами:

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

Самое лучшее в этом варианте — то, что он дает более четкое представление о вашем отчете.

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

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

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

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

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

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

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

Когда вы добавляете столбец значений в поле значения, он показывает СУММУ или СЧЕТ (иногда), но здесь можно вычислить еще несколько вещей:

Чтобы открыть параметры «Настройки значений», выберите ячейку в столбце значений и щелкните правой кнопкой мыши.

И в контекстном меню откройте «Настройки поля значений», затем нажмите

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

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

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

Вот шаги:

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

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

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

Ранжирование дает вам лучший способ сравнивать вещи друг с другом…

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

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

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

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

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

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

Шаги по использованию:

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

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

Это также идеальный вариант для создания быстрого отчета.

Когда вы создаете сводную таблицу, Excel просит вас добавить новый лист для сводной таблицы…

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

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

Существует ситуация, когда вам нужно сослаться на ячейку в сводной таблице.

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

Лучше всего то, что вы можете отключить его, и вот шаги:

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

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

СубдисаблеGetPivotData()

Application.GenerateGetPivotData = False

Окончание субтитров

Ознакомьтесь с этим ➜ 100 лучших полезных кодов Excel VBA + файл PDF.

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

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

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

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

• Во-первых, вам нужно вставить дату как элемент строки в сводную таблицу.

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

• В разделе «По» выберите «Месяц» и нажмите «ОК».

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

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

Шаги просты.

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

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

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

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

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

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

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

➜ полное руководство по созданию вычисляемого элемента и поля в сводной таблице

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

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

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

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

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

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

Вам не нужно создавать его снова и снова.

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

Вы можете активировать опцию «Показать элементы без данных» из поля.

• Сначала щелкните правой кнопкой мыши по полю и откройте «Настройки поля».
• Теперь перейдите в «Макет и печать», установите флажок «Показать элементы без данных» и нажмите «ОК».

Бум! Любые элементы, по которым у вас нет данных, будут отображаться в сводной таблице.

Это один из моих любимых вариантов сводной таблицы.

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

Допустим, у вас есть сводная таблица со значениями месяца,…

…тогда, с этим вариантом…

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

Вот шаги:

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

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

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

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

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

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

Затем нажмите ОК.

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

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

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

Вставить изображение

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

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

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

Вам нужно нажать кнопку +, чтобы развернуть, и кнопку –, чтобы свернуть…

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

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

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

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

Да, вы можете сортировать по совпадению значений.

• Все, что вам нужно сделать, это открыть фильтр и выбрать «Дополнительные параметры сортировки».

• Затем выберите «Доступ (от А до Я) по:», выберите столбец для сортировки и нажмите «ОК».

Примечание:

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

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

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

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

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

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

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

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

Но иногда (ну постоянно) нужно изменить это имя на имя без «Суммы» или «Счетчика».

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

Да это оно.

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

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

Или…

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

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

Используйте Control + C, чтобы скопировать его, затем «Специальная вставка» ➜ «Значения».

При защите листа, содержащего сводную таблицу, обязательно проверьте:

«Используйте сводную таблицу и сводную диаграмму»

от «Разрешить всем пользователям этой таблицы:».

Если вы хотите открыть «Настройки значений» для определенного столбца значений…

…ТАК…

…Лучший способ — дважды щелкнуть заголовок столбца.

Если вы думаете так: когда вы создаете сводную таблицу с нуля, Excel создает сводный кеш.

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

Так какой смысл?

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

Но, Пунит, как я мог это сделать?

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

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

Ваша сводная таблица по-прежнему будет работать правильно.

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

Другой способ поделиться сводной таблицей с кем-либо — создать веб-страницу.

Да, простой HTML-файл со сводной таблицей.

• Для этого просто сохраните книгу как веб-страницу [html].

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

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

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

 https://exceladvisor.org/book1.xlsx

В этой книге у вас есть данные, и на основе этих данных вам нужно создать сводную таблицу.

Ключом к этому является POWER QUERY.

Посмотрите это: Примеры Power Query + советы и подсказки

• Сначала перейдите на вкладку «Данные» ➜ «Получение и преобразование данных» ➜ «Из Интернета».

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

• После этого выберите таблицу и нажмите «Загрузить в».

• Затем выберите отчет сводной таблицы и нажмите «ОК».

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

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

Для использования со сводной таблицей доступны все параметры CF.

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

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

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

Шаги ниже:

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

• Выберите цвет в имеющемся у вас окне.

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

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

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

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

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

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

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

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

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

Вот пошаговое руководство для этого.

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

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

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

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

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

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

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

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

Альт + Н + В

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

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

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

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

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

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

Ctrl + –

Эта комбинация клавиш просто скроет выбранные ячейки.

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

Ctrl + –

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

И когда вы нажимаете эту горячую клавишу, открывается окно « Вычисляемое поле » .

Альт + Д и П

При использовании этого сочетания клавиш после этого вам придется нажимать клавиши.

Альт + стрелка вниз

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

Альт+Ф1

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

F11

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