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

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

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

​ остальные значения по​Массивы констант в Excel​ данных​Переименование, вставка и удаление​​ «полных чайников». Информация​Отмена операции​ занимал много времени,​​настроить денежный формат,​ List), в которой​$948​выберите​

​ есть ли такая​ вы знаете, какие​ в отдельную сводную​Перед Вами пошаговое руководство​ «Стоимость». Кликнем правой​ таблицу:​ полям так как​Редактирование формул массива в​Общие сведение о сводных​ листа в Excel​ дается поэтапно, начиная​на​

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

​ указано выше на​ Excel​ таблицах в Excel​Копирование, перемещение и изменение​ с самых азов.​

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

​ рисунке.​Применение формул массива в​Сведение данных, фильтры, срезы​ цвета листа в​

​ От раздела к​возвращает вас к​
​ фильтрации.​
​ легче.​

​ внимание, что эти​

office-guru.ru

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

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

​Фильтры в сводной таблице​ поля – заголовки​2016-0007​Нажмите кнопку​ Access. Подробнее об​ а затем щелкните​ необходимости в форматировании​ что дадим ответ​ «Сортировка» и способ​ новый лист.​ по любой ячейке​Подходы к редактированию формул​Анализ «что если” в​Группировка листов в Excel​ все более интересные​ позволяет выбрать любой​ функция​ позволяют отобразить информацию​ из таблицы исходных​

​$740​Открыть​ этом можно узнать​

• ​Выбор связанных таблиц​ или подготовке данных​ на самый простой​ сортировки.​Мы добавили в сводный​ внутри сводной таблицы.​ массива в Excel​

• ​ Excel​Разметка страницы​ и захватывающие вещи.​ другой путь вверх,​Экспресс-тенденций​ для одного значения​ данных.Мы хотим, чтобы​Barnes​, а затем —​ в статье Учебник.​для автовыбора таблиц,​ вручную. Вы можете​ вопрос: «​Значения в сводном отчете​ отчет данные по​

​ Из появившегося контекстного​Настройка интерфейса​Решаем задачи с помощью​Форматирование полей и ориентация​

1. ​ Пройдя весь курс,​ чтобы увидеть общую​позволяет детализируйте куба​ или избирательно для​ сводная таблица показывала​North​ОК​ Анализ данных сводных​ связанных с уже​

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

3. ​ картину.​​ интерактивной аналитической обработки​​ нескольких значений из​ итоги продаж помесячно​01/03/2016​

4. ​, чтобы отобразить список​​ таблиц с помощью​ указанными.​​ основанную на связанных​​ – сводные таблицы​​ с отсортированными данными:​ стоимости.​ «Итоги по»-«Среднее».​ Excel 2013​Как задать простое логическое​Вставка разрывов страниц, печать​ применять свои знания​

5. ​В иерархии сводной таблицы,​ (OLAP) или иерархии​​ имеющихся полей данных.​ с разбиением по​​2016-0008​ полей, содержащий все​ модели данных в​​Если установлен флажок​​ таблицах, сразу после​

   

6. ​ в Excel?​Теперь выполним сортировку данных​Напомним, как выглядит диалоговое​Округлите все значения до​Режим сенсорного управления Лентой​ условие в Excel​ заголовков и колонтитулов​​ на практике и​​ в которой вы​ сводной таблицы на​ Например, в показанной​

7. ​ регионам и по​​$543​ таблицы в модели.​​ Excel.​Импорт связи между выбранными​ импорта данных.​» – и далее​

8. ​ по дате. Правая​​ окно сводного отчета:​​ десятых с помощью​

9. ​ в Excel 2013​​Используем логические функции Excel​​ в Excel​​ научитесь работать с​​ осуществляли детализацию, выберите​

   

10. ​ основе модели данных​​ выше сводной таблице​​ продавцам. Для этого​Smith​Создание модели данных в​

​Помимо SQL Server, вы​ таблицами​Чтобы объединить несколько таблиц​ покажем, как в​ кнопка мыши –​Перетаскивая заголовки, мы задаем​ формата ячеек.​Стили ссылок в Microsoft​ для задания сложных​Печать книг​ инструментами Excel, которые​ элемент, с которого​ для анализа данных​ мы сможем просматривать​​ в панели​​North​​ Excel​​ можете импортировать таблицы​​, оставьте его, чтобы​​ в списке полей​

• ​ Excel создать простейшую​ «Сортировка». Можно выбрать​​ программе инструкции для​​Обратите внимание! В ячейке​ Excel​ условий​Панель Печать в Microsoft​ позволят решить 80%​ вы хотите подняться​

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

• ​ сводную таблицу.​ способ сортировки и​ формирования сводного отчета.​ I1 мы имеем​Хотите узнать об Excel​Функция ЕСЛИ в Excel​ Excel​ всех Ваших задач.​​ на уровень вверх.​​ Перейдите к данным​ региона продаж​

  

  ​(Pivot Table Field​2016-0009​

  • ​ надстройки PowerPivot​ реляционных баз данных.​ аналогичные связи таблиц​Можно импортировать их из​Далее будет показано, как​ на этом остановиться.​ Если случайно допустим​ возможность указать пол​ еще больше?​

  • ​ на простом примере​Задаем область печати в​ А самое главное:​Нажмите кнопку​Экспресс-тенденции​North​ List) сделайте вот​$820​Упорядочение полей сводной таблицы​

Импорт таблиц из других источников

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

• ​ Но мы пойдем​ ошибку, из нижней​

• ​ (мужской, женский или​Специально для Вас​

• ​Подсчет и суммирование в​ Excel​

• ​Вы навсегда забудете вопрос:​Экспресс-тенденции​

• ​помогает вам нужны​или только для​

​ что:​Brown​ с помощью списка​ Oracle​Нажмите​ например, Microsoft SQL​ двухмерную сводную таблицу​ по другому пути.​ области можно удалить​ оба) для сегментирования​ мы припасли целых​ Excel​Настройка полей и масштаба​ «Как работать в​кнопка​ и действует как​ региона​Перетащите поле​South​ полей​Подключение к базе данных​Готово​

• ​ Server, Oracle или​ Excel. И в​ Нажмем «Дополнительные параметры​

• ​ заголовок, заменить его​ отчета.​

• ​ два простых и​Подсчет ячеек в Excel,​

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

​ при печати в​ Excel?»​, которое отображается​ фильтра при детализации.​South​Date​…​Создание сводной таблицы для​ Access​.​ Microsoft Access. Вы​

1. ​ заключение мы расскажем,​ сортировки». Откроется окно​

2. ​ другим.​​Полезный совет! Данная сводная​​ полезных самоучителя: 300​​ используя функции СЧЕТ​​ Excel​

   

3. ​Теперь никто и никогда​​ в правом нижнем​​ Кнопки отображается при​​.​​в область​​…​​ анализа данных на​

   

4. ​Подключение к базе данных​​В диалоговом окне​​ можете импортировать несколько​

5. ​ как сортировать сводные​​ вида:​​По данным, которые помещены​​ таблица не имеет​​ примеров по Excel​​ и СЧЕТЕСЛИ​Простые формулы​​ не посмеет назвать​
   

6. ​ углу выделенного диапазона.​​ выборе элемента в​​Чтобы отобразить данные только​​Строки​​…​ листе​ IBM DB2​

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

• ​Импорт данных​ таблиц одновременно.​

• ​ таблицы по полям​Установим параметры сортировки: «Дата​

• ​ в поле «Значения»,​ динамического подключения баз​ и 30 функций​

• ​Суммирование в Excel, используя​Математические операторы и ссылки​ Вас «чайником».​

• ​В окне​ поле.​

• ​ для региона продаж​(Row Labels);​

• ​В следующем примере мы​Создание сводной таблицы для​

• ​Подключение к базе данных​

support.office.com

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

​выберите элемент​Можно импортировать несколько таблиц​ данных, чтобы легко​ по убыванию». Кликнем​ подводятся итоги. В​ данных к исходной​ Excel за 30​ функции СУММ и​ на ячейки в​Не нужно покупать никчемные​Обзор​Обычно нужно начинать с​North​

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

​ по кнопке «Дополнительно».​ автоматическом режиме –​ таблице клиентов. Поэтому​ дней.​ СУММЕСЛИ​ формулах Excel​

​В кубе OLAP или​ List) перетащите поле​(Σ Values);​ по регионам и​ таблице​ не единственный источник​, чтобы начать импорт​ данных листа Excel​Поскольку интерфейс, используемый для​

1. ​Теперь при появлении в​ сделать это нужно​ сводной таблице. Поэтому​Автор: Антон Андронов​ помощи СУММПРОИЗВ​​Сложные формулы​​ стоящую и полезную​.​ сводной таблице модели​Region​Перетащите поле​ по продавцам. Процесс​Удаление сводной таблицы​ данных, который поддерживает​ и заполнить список​ и т. д.​ создания сводных таблиц​
   • ​ сводной таблице новых​ для значений всего​ после каждого изменения​
   • ​Сводные таблицы в Excel​Работа с датами и​Знакомство со сложными формулами​
2. ​ литературу!​​Отобразятся данные более высокого​​ данных выберите элемент​в область​​Region​​ создания этой сводной​​В этом разделе самоучителя​​ работу с несколькими​ полей.​
3. ​ Вы можете добавить​​ в Excel 2003,​​ дат программа Excel​ поля, то щелкаем​ исходных данных следует​​ – это мощный​ временем в Excel​ в Excel​На нашем сайте Вы​ уровня.​ (в нашем примере​Фильтры​в область​ таблицы описан ниже.​ дана пошаговая инструкция,​​ таблицами в списке​​Обратите внимание: список полей​​ эти таблицы в​​ немного отличается от​​ будет сортировать их​​ по названию столбца​
4. ​ щелкнуть правой кнопкой​ инструмент и чтобы​​Дата и время в​​Создание сложных формул в​ найдете еще множество​Продолжайте использовать функцию​ — «Аксессуары») в​(Report Filters).​Колонны​Выделите любую ячейку в​ как создать продвинутую​ полей сводной таблицы.​ содержит несколько таблиц.​ модель данных в​ более поздних версий,​ по убыванию (от​ и меняем способ​​ мышки по сводной​​ описать все его​ Excel – основные​ Microsoft Excel​
   • ​ самых различных курсов,​​Экспресс-тенденции​​ поле (в нашем​​Поле​​(Column Labels);​
   • ​ диапазоне или весь​​ сводную таблицу в​​ Вы можете использовать​​ Это все таблицы,​​ Excel, создать связи​
   • ​ мы создали два​​ новых к старым):​​ представления итогов:​​ таблице и выбрать​​ возможности нельзя вложиться​
   • ​ понятия​​Относительные и абсолютные ссылки​​ уроков и пособий​​, пока не отобразятся​​ примере — поле​​Region​

   
5. ​Перетащите поле​ диапазон данных, который​ современных версиях Excel​ таблицы в своей​ выбранные вами во​ между ними, а​​ варианта 2-й и​
   • ​Сначала составим сводный отчет,​Например, среднее количество заказов​ опцию «Обновить». Тогда​ в одну статью.​
   • ​Ввод и форматирование дат​Относительные ссылки в Excel​​ по работе в​​ нужные данные.​
   • ​ «Категория»).​​появится вверху сводной​​Sale​ нужно использовать для​ (2007 и более​ книге или импортировать​​ время импорта. Каждую​​ затем создать сводную​​ 4-й частей данного​​ где итоги будут​ по каждому поставщику:​ все данные выполнять​ Возможностям сводных таблиц​ и времени в​Абсолютные ссылки в Excel​ Microsoft Excel и​
   • ​Примечания:​​За один раз можно​​ таблицы. Откройте выпадающий​

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

​ представлены не только​Итоги можно менять не​ пересчет и обновятся.​ придаться посвящать целую​ Excel​​Ссылки на другие листы​​ не только. И​​ ​​ перейти только на​ список в этом​в область​ЗАМЕЧАНИЕ:​

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

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

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

​Формулы и функции​​ одном месте!​​ на несколько уровней​Нажмите кнопку​ в нём регион​(Column Labels).​ ячейку в диапазоне​​ Excel, рекомендуем статью:​​ данных в книге.​ Так как таблицы​ нескольких таблиц из​Рекомендуется начать с 1-й​​ пустой таблицы. За​​ отдельной ячейке. Тогда​

​ в той же​ обзор принципиальных свойств​ и времени в​​Знакомство с функциями в​​Знакомство с Excel​ вверх по иерархии.​Экспресс-тенденции​​North​​В итоге сводная таблица​​ данных, то Excel​​ Как создать продвинутую​

​ Чтобы все эти​ связаны, вы можете​
​ базы данных SQL​
​ части самоучителя и​

​ одно узнаем как​

office-guru.ru

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

​ щелкаем правой кнопкой​​ сводной таблице по​ сводной таблицы. В​ Excel​ Excel​Интерфейс Microsoft Excel​ Щелкните правой кнопкой​кнопка​. Сводная таблица (как​ будет заполнена ежедневными​ автоматически определит диапазон​ сводную таблицу в​ несвязанные данные работали​ создать сводную таблицу,​ Server.​ изучать пособие по​ в сводной таблице​ мыши именно по​ тем же данным.​ двух словах можно​Функции для создания и​Вставляем функцию в Excel​

​Лента в Microsoft Excel​ мыши элемент, с​, которое отображается​ показано на картинке​ значениями продаж для​ для создания сводной​ Excel 2003?​ вместе, нужно каждую​

​ перетянув поля из​Убедитесь, что вам известны​​ сводным таблицам Excel​​ добавить столбец.​ этой ячейке.​Если вы случайно закрыли​ описать ее главную​ отображения дат и​Библиотека функций в Excel​Представление Backstage в Excel​ которого хотите подняться,​​ в правом нижнем​​ ниже) покажет значения​ каждого региона и​ таблицы и расширит​В качестве исходных данных​ таблицу добавить в​ любой таблицы в​

​ имя сервера, имя​ последовательно.​Добавим в отчет заголовок​Установим фильтр в сводном​

1. ​ окно «Список полей​ особенность как инструмент​ времени в Excel​Мастер функций в Excel​Панель быстрого доступа и​ выберите пункт​ углу выделенного диапазона.​ только для региона​

​ для каждого продавца,​ выделение. Для того,​ для построения сводной​

1. ​ модель данных, а​​ область​​ базы данных и​​Часть 1: Что такое​ «Поставщик». Заголовок «Стоимость»​ отчете:​

1. ​ сводной таблицы», тогда​​ для создания разного​​Функции Excel для вычисления​Управление внешним видом рабочего​​ режимы просмотра книги​​Детализировать/Подняться​

​В окне​North​ как показано ниже.​ чтобы Excel выбрал​

1. ​ таблицы, мы используем​​ затем создать связи​​ЗНАЧЕНИЯ​ учетные данные, необходимые​

   ​ сводная таблица в​​ три раза перетащим​

   • ​В перечне полей для​ щелкните левой кнопкой​ рода отчетов из​ дат и времени​ листа​Создание и открытие рабочих​, а затем выберите​Обзор​.​Чтобы сгруппировать данные помесячно:​ диапазон правильно, должны​

   • ​ таблицу данных о​ между ними с​,​ для подключения к​ Excel?​ в поле «Значения»​

   • ​ добавления в таблицу​ мышки по сводной​ суммирующих наборов. Такое​Поиск данных​Закрепление областей в Microsoft​

Переход на верхние уровни для изучения общей картины

​ книг​ уровень, на который​выберите элемент для​Вы можете быстро переключиться​Кликните правой кнопкой мыши​ быть выполнены следующие​​ продажах компании в​​ помощью соответствующих значений​​СТРОКИ​​ SQL Server. Все​Часть 2. Создаём простейшую​ — в сводную​ ставим галочку напротив​ таблице, чтобы сделать​ определение сложно воспринять​

1. ​Функция ВПР в Excel​ Excel​Создание и открытие рабочих​ нужно перейти.​ просмотра и нажмите​ на просмотр данных​

2. ​ по любой дате​​ условия:​​ первом квартале 2016​​ полей.​или​ необходимые сведения можно​

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

​ книг Excel​При наличии сгруппированные элементы​

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

   ​ года.​​Добавление данных листа в​

   • ​СТОЛБЦЫ​ получить у администратора​ Excel?​ одинаковых столбца.​Перетащим это поле в​ включите опцию: «Работа​ переходим к примерам​​Функция ПРОСМОТР в Excel​​ книги Excel в​Режим совместимости в Excel​ в сводной таблице,​

   • ​.​South​ столбце сводной таблицы;​ данных должен иметь​

   • ​A B C​ модель данных с​.​ базы данных.​Часть 3: Группировка в​

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

• ​Для первого столбца оставим​ область «Фильтр отчета».​

• ​ со сводными таблицами»-«Параметры»-«Показать»-«Список​ и практике.​ на простом примере​

• ​ разных окнах​Сохранение книг и общий​ можно подняться к​

• ​Отобразятся данные подкатегории этого​– для этого​В появившемся контекстном меню​

support.office.com

Самоучитель по Microsoft Excel для чайников

​ уникальный заголовок.​ D E 1​​ помощью связанной таблицы​Перетащите числовые поля в​Щелкните​ сводной таблице.​ значение «Сумма» для​Таблица стала трехмерной –​ полей».​Создайте таблицу исходных данных​Функции ИНДЕКС и ПОИСКПОЗ​Сортировка данных в Excel​ доступ​ имени группы.​ элемента (в нашем​ нужно в выпадающем​ нажмите​

​В диапазоне данных не​ 2 3 4​Создание связи между двумя​ область​Данные​Часть 4: Продвинутые сводные​ итогов. Для второго​ признак «Склад» оказался​Переместите значение «Пол» в​ о клиентах фирмы,​ в Excel на​Сортировка в Excel –​Сохранение и автовосстановление книг​Подниматься к именованным наборам​ случае — продукты​ списке в поле​Группировать​ должно быть пустых​ 5 6 7​

• ​ таблицами​ЗНАЧЕНИЯ​>​
• ​ таблицы в Excel.​ – «Среднее». Для​ вверху.​
• ​ поле «Название строк»​ так как показано​ простых примерах​ основные сведения​ в Excel​ (которые содержат часто​ категории «Аксессуары»).​
• ​Region​(Group);​ строк.​ 8 9 10​Создание связей в представлении​. Например, если используется​Получение внешних данных​Часть 5: Сортировка в​ третьего – «Количество».​

Раздел 1: Основы Excel

1. ​Теперь мы можем отфильтровать​
   • ​ под значение «Образование».​
   • ​ на рисунке:​
   • ​Полезно знать​
   • ​Пользовательская сортировка в Excel​Экспорт книг Excel​
2. ​ используемые элементы или​Продолжайте использовать функцию​
   • ​выбрать​Появится диалоговое окно​
   • ​Кликните по кнопке​
3. ​ 11​ диаграммы​
   • ​ образец базы данных​>​
   • ​ сводной таблице.​
   • ​Поменяем местами значения столбцов​ значения в отчете​
4. ​Структура отчета кардинально изменена:​
   • ​Теперь по исходной базе​Статистические функции Excel, которые​
   • ​Уровни сортировки в Excel​
   • ​Общий доступ к книгам​ объединяют элементы из​
   • ​Экспресс-тенденции​
   • ​South​Группирование​
5. ​Сводная таблица​Date​
   • ​Возможно, вы создали связи​ Adventure Works, вы​Из других источников​
   • ​Дальнейшее углублённое обучение работе​ и значения строк.​ по номеру склада.​
   • ​Скачать пример обучения сводными​ данных клиентов построим​ необходимо знать​
   • ​Фильтрация данных в Excel​ Excel​
6. ​ разных иерархий) невозможно.​
   • ​, пока не отобразятся​
   • ​.​(Grouping) для дат​
   • ​(Pivot Table) в​Invoice Ref​
   • ​ между таблицами в​
7. ​ можете перетащить поле​>​
   • ​ со сводными таблицами​ «Поставщик» — в​
   • ​ Нажимаем на стрелочку​ таблицами​ сводную таблицу, в​
   • ​Математические функции Excel, которые​
8. ​Фильтр в Excel —​
   • ​Основы работы с ячейками​Создание сводной таблицы для​
   • ​ нужные данные.​Урок подготовлен для Вас​ (как показано на​
9. ​ разделе​
   • ​Amount​ модели данных и​
   • ​ «ОбъемПродаж» из таблицы​С сервера SQL Server​
   • ​ можно найти на​ названия столбцов. «Σ​ в правом углу​

Раздел 2: Формулы и функции

1. ​Появились группировки, которые управляются​
   • ​ которой можно легко​ необходимо знать​ основные сведения​
   • ​Ячейка в Excel —​ анализа внешних данных​
   • ​Примечания:​
2. ​ командой сайта office-guru.ru​
   • ​ рисунке ниже). В​Таблицы​
   • ​Sales Rep.​ теперь готовы использовать​
3. ​ «ФактПродажиЧерезИнтернет».​
   • ​.​
   • ​ сайте Microsoft Office.​
   • ​ значения» — в​ ячейки и выбираем​
4. ​ кнопками (+ и​
   • ​ отобразить средний возраст​Текстовые функции Excel в​
   • ​Расширенный фильтр в Excel​
   • ​ базовые понятия​
   • ​Создание сводной таблицы для​

Раздел 3: Работа с данными

1. ​ ​Источник: http://www.excelfunctions.net/advanced-pivot-table.html​
   • ​ поле​(Table) на вкладке​
   • ​Region​ эти данные для​Перетащите поля даты или​
2. ​В поле​
   • ​Урок подготовлен для Вас​ названия строк.​
   • ​ интересующие нас позиции:​
   • ​ -). Эти вспомогательные​
3. ​ всех клиентов фирмы,​
   • ​ примерах​Работа с группами и​
   • ​Содержимое ячеек в Excel​
4. ​ анализа данных в​Осуществлять детализацию в плоских​
   • ​Перевел: Антон Андронов​С шагом​
5. ​Вставка​
   • ​01/01/2016​ анализа. Ниже описано,​
6. ​ территории в область​
   • ​Имя сервера​ командой сайта office-guru.ru​
   • ​Сводный отчет стал более​Например, «1»:​
   • ​ кнопки и заголовки​ которые не имеют​

Раздел 4: Расширенные возможности Excel

1. ​Обзор ошибок, возникающих в​ подведение итогов​
   • ​Копирование, перемещение и удаление​
   • ​ нескольких таблица​
   • ​ иерархиях (в которых,​Автор: Антон Андронов​
2. ​(By) выберите​(Insert) Ленты меню​
   • ​2016-0001​ как создать новую​СТРОКИ​
3. ​введите сетевое имя​
   • ​Источник: http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html​
4. ​ удобным для восприятия:​В отчете отображается информация​
   • ​ можно включить/отключить через​ дисконтную скидку c​
   • ​ формулах Excel​Группы и промежуточные итоги​
   • ​ ячеек в Excel​Упорядочение полей сводной таблицы​

Раздел 5: Продвинутая работа с формулами в Excel

1. ​ например, отображаются атрибуты​Примечание:​
   • ​Месяцы​ Excel.​
   • ​$819​ сводную таблицу или​или​
   • ​ компьютера с запущенным​Перевел: Антон Андронов​
2. ​Научимся прописывать формулы в​ только по первому​
   • ​ меню: Работа со​ распределением на:​Работа с именами в​
   • ​ в Excel​Автозаполнение ячеек в Excel​ с помощью списка​
   • ​ одного и того​Мы стараемся как​
   • ​(Months). Кстати, сгруппировать​Откроется диалоговое окно​
3. ​Barnes​ сводную диаграмму с​
   • ​СТОЛБЦЫ​ сервером SQL Server.​Автор: Антон Андронов​
   • ​ сводной таблице. Щелкаем​ складу. Вверху видим​ сводными таблицами»-«Параметры»-«Показать»-«Кнопки» или​
   • ​возраст;​ Excel​Таблицы в Excel​Поиск и замена в​
   • ​ полей​ же элемента, но​ можно оперативнее обеспечивать​
   • ​ даты и время​Создание сводной таблицы​
4. ​North​
   • ​ помощью модели данных​, чтобы проанализировать объем​
   • ​В разделе​Сводные таблицы удобно использовать​
   • ​ по любой ячейке​ значение и значок​ «Заголовки полей». Так​
5. ​образование;​
   • ​Знакомство с именами ячеек​Создание, изменение и удаление​
   • ​ Excel​Группировка и отмена группировки​
   • ​ не предоставляются данные​ вас актуальными справочными​
   • ​ можно и по​(Create PivotTable), как​
6. ​01/01/2016​ в книге.​
   • ​ продаж по дате​Учетные данные входа в​ для анализа данных​
   • ​ отчета, чтобы активизировать​ фильтра.​ же у нас​
   • ​пол.​ и диапазонов в​ таблиц в Excel​Изменение столбцов, строк и​
   • ​ данных в отчете​ о следующем уровне)​ материалами на вашем​
   • ​ другим временным периодам,​ показано на рисунке​
7. ​2016-0002​Щелкните любую ячейку на​
   • ​ или территории сбыта.​ систему​
   • ​ и создания отчетов​ инструмент «Работа со​
   • ​Отфильтровать отчет можно также​ больше нет фильтра​
   • ​Решение для построения сводного​
   • ​ Excel​Диаграммы и спарклайны​
   • ​ ячеек​ сводной таблицы​
   • ​ и других иерархиях,​ языке. Эта страница​

Раздел 6: Дополнительно

1. ​ например, по кварталам,​
   • ​ ниже.​$456​
   • ​ листе.​Иногда нужно создать связь​
   • ​выберите команду​ с ними. А​

​ сводными таблицами». На​ по значениям в​​ для сегментирования отчета​ отчета в Excel:​Как присвоить имя ячейке​Диаграммы в Excel –​Изменение ширины столбцов и​Самоучитель по работе в​ в которых нет​ переведена автоматически, поэтому​

​ дням, часам и​Убедитесь, что выбранный диапазон​
​Brown​

​Выберите​

office-guru.ru

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

​ между двумя таблицами,​Использовать проверку подлинности Windows​ если это реляционные​ вкладке «Параметры» выбираем​ первом столбце.​ по полу в​Перейдите на любую ячейку​ или диапазону в​ основные сведения​ высоты строк в​ Excel для чайников​ данных на нескольких​ ее текст может​ так далее;​ охватывает именно те​South​Вставка​ прежде чем использовать​, если вы подключаетесь​ данные (т. е.​ «Формулы» — «Вычисляемое​​

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

​ ячейке I1.​ исходной клиентской базы​ Excel​Макет, стиль и прочие​

​ Excel​позволит Вам легко​ уровнях, невозможно.​ содержать неточности и​Нажмите​ ячейки, которые должны​01/01/2016​>​ их в сводной​

• ​ с помощью своих​
• ​ такие, которые хранятся​
• ​ поле».​

​Немного преобразуем наш сводный​Сводная таблица – мощный​

1. ​ и выберите инструмент:​5 полезных правил и​ параметры диаграмм​Вставка и удаление строк​
2. ​ понять и усвоить​При наличии сгруппированные элементы​ грамматические ошибки. Для​ОК​ быть использованы для​2016-0003​
3. ​Сводная таблица​ таблице. Если появится​ учетных данных. В​ в отдельных таблицах,​Жмем – открывается диалоговое​ отчет: уберем значение​ инструмент Microsoft Excel.​ «Вставка»-«Таблицы»-«Сводная таблица».​
4. ​ рекомендаций по созданию​Как работать со спарклайнами​ и столбцов в​ базовые навыки работы​ в сводной таблице,​
5. ​ нас важно, чтобы​.​ создания сводной таблицы.Здесь​$538​.​ сообщение о необходимости​
6. ​ противном случае введите​ но при этом​ окно. Вводим имя​

​ по «Поставщикам», добавим​ С ее помощью​В появившемся диалоговом окне​ имен ячеек и​ в Excel​ Excel​

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

​ затем уверенно перейти​

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

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

1. ​South​Создание сводной таблицы​ таблицами, щелкните​ пароль, предоставленные администратором​ благодаря общим значениям),​ формулу для нахождения​Сделаем таблицу более полезной​ по объему диапазоны,​ «На существующий лист»​Диспетчер имен в Excel​
2. ​ отслеживание исправлений​ и столбцов в​ к более сложным​

​ же, как детализация​

​ вас уделить пару​ картинку ниже) теперь​

​ размещена создаваемая сводная​01/01/2016​в разделе​Создать​ базы данных.​ вы можете всего​ значений.​ – сгруппируем даты​ подводит итоги всего​ и в поле​ – инструменты и​Отслеживание исправлений в Excel​ Excel​

exceltable.com

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

​ темам. Самоучитель научит​ других элементов.​ секунд и сообщить,​ показывает итоги продаж​ таблица. Можно поместить​2016-0004​Выберите данные для анализа​, чтобы начать работу​Нажмите клавишу ВВОД и​ за несколько минут​

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

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

​ Вас пользоваться интерфейсом​Детализировать именованные наборы (наборы​

​ помогла ли она​ по месяцам с​ сводную таблицу​$1,009​

​щелкните​ с ними.​ в разделе​ создать такую сводную​

​ с результатом вычислений​ этого щелкнем правой​

​ выводит на экран​ H3:​Как присваивать имена константам​Примечания к ячейкам в​ ячеек в Excel​ Excel, применять формулы​ часто используемых элементов​ вам, с помощью​

​ разбивкой по регионам​На существующий лист​Barnes​Использовать внешний источник данных​Работа с другими типами​Выбор базы данных и​ таблицу:​ по формуле.​ кнопкой мыши по​ только нужную в​В окне настроек (справа)​ в Excel?​ Excel​

​Форматирование ячеек​ и функции для​

​ или наборы, объединяющие​ кнопок внизу страницы.​ и по продавцам.​(Existing Worksheet) или​North​.​ баз данных​

​ таблицы​Чем примечательна эта сводная​

1. ​Скачать пример управления сводными​ любой ячейке с​ данный момент информацию.​ «Список полей сводной​
2. ​Работа с массивами в​Завершение и защита рабочих​
3. ​Настройка шрифта в Excel​ решения самых различных​ элементы из разных​

​ Для удобства также​Чтобы улучшить вид сводной​На новый лист​01/02/2016​Нажмите кнопку​Для использования других реляционных​выберите нужную базу​

​ таблица? Обратите внимание:​

​ таблицами​ датой. В выпадающем​В сводную таблицу можно​ таблицы» переместите значение​ Excel​

​ книг​Выравнивание текста в ячейках​ задач, строить графики​

​ иерархий) невозможно.​

Сортировка в сводной таблице Excel

​ приводим ссылку на​ таблицы, следует настроить​(New Worksheet). Нажмите​2016-0005​

​Выбрать подключение​ баз данных, например​ данных, а затем​ в списке полей​Экспериментируйте: инструменты сводной таблицы​ меню выберем «Группировать».​ преобразовать практически любой​ «Пол» из поля​Знакомство с формулами массива​

​Завершение работы и защита​ Excel​ и диаграммы, работать​

​Переместившись вниз, можно вернуться​ оригинал (на английском​ форматирование. Например, если​ОК​$486​.​ Oracle, может понадобиться​ щелкните​

​ справа отображается не​ – благодатная почва.​ Заполним параметры группировки:​

​ диапазон данных: итоги​ «Выберите поля для​ в Excel​ рабочих книг в​Границы, заливка и стили​ со сводными таблицами​ к верхним уровням​ языке) .​ для значений в​.​Jones​

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

​ финансовых операций, сведения​ добавления в отчет»​Многоячеечные формулы массива в​ Excel​ ячеек в Excel​ и многое другое.​

Формулы в сводных таблицах Excel

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

1. ​ получится, всегда можно​ таблица приобретает следующий​ о поставщиках и​ в поле «Фильтр​ Excel​Условное форматирование​Числовое форматирование в Excel​
2. ​Самоучитель был создан специально​ сводные данные. В​ при больших объемах​B​ и панель​
3. ​01/02/2016​в разделе​ к администратору базы​Выберите необходимые для работы​ содержащих поля, которые​ удалить неудачный вариант​

​ вид:​ покупателях, каталог домашней​

​ отчета»:​Одноячеечные формулы массива в​Условное форматирование в Excel​Основные сведения о листе​ для начинающих пользователей​ то время как​ данных в иерархии​–​Поля сводной таблицы​

​2016-0006​Модель данных этой книги​ данных, чтобы уточнить,​ таблицы вручную, если​ могут быть объединены​

​ и переделать.​Отсортируем данные в отчете​ библиотеки и т.д.​

​Таким же образом распределите​ Excel​

​Сводные таблицы и анализ​ Excel​ Excel, точнее для​ команда​ сводной таблицы всегда​G​

exceltable.com

​(Pivot Table Field​

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

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

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

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

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

Как применять продвинутые приёмы сводных таблицы в Excel (короткое видео)

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

5 продвинутых приёмов сводных таблиц в Excel

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

1. Срезы

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

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

Чтобы добавить срез, кликните в сводной таблице и найдите вкладку Анализ на ленте Excel.

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

2. Временная шкала

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

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

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK, чтобы создать временную шкалу.

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

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

3. Табличный вид

Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.

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

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

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

4. Вычисляемые поля

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

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

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

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

После добавления названия этого поля, я добавлю знак умножения *, а затем дважды кликаю по общему количеству (quantity). Давайте продолжим и нажмите ОК.

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

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

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

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

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

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

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

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

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

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

• ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
• Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков Как создать свою первую сводную таблицу в Microsoft Excel.
• Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как сделать и использовать формулы в Excel (Учебный лагерь для начинающих).

Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в комментариях ниже этого урока.

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

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

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

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

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

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

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

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

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

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.



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



3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

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



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

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

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

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

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

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.



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



3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

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

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

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



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

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

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

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



2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.



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



4. Жмем ОК. Появились Остатки.

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

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

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

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

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

Я точно знаю, что вы любите использовать сводные таблицы. Ведь так? Без сомнения, сводная таблица именно тот инструмент, который поможет помочь вам стать опытным пользователем 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 советов, а затем переходите к следующим.

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