Основные косметические изменения сводных таблиц в Excel Изменения в макете сводной таблицы. Использование стилей и тем для изменения вида сводной таблицы. Добавление и удаление промежуточных итогов. Несмотря на появление новых функций вычисления итогов в Excel , иногда просто невозможно получить данные, необходимые для формирования того или иного отчета. В подобных случаях вам понадобится промежуточная сводная таблица в качестве источника данных для формируемого отчета. Последней в списке стоит функция Индекс, название которой мало о чем говорит. Компания Microsoft уверяет, что этот тип вычислений описывает относительную важность ячейки в столбце. Она пригодится при установке взаимосвязи между продажей комплектующих и прибылью от обслуживания. Создайте сводную таблицу, которая будет сравнивать доходы в каждом направлении деятельности с прибылью от продажи и обслуживания филей, духовок и СВЧ-печей. Результат показан на рис. В Excel появились функции ранжирования данных. С помошью этих функций различным строкам в наборе данных присваиваются ранги 1, 2, 3 и т. Ранг 1 можно присвоить наибольшему либо наименьшему значению. Как показано на рис. В и в годах […]. Для использования функций Отличие и Приведенное отличие следует указать базовое поле и базовый элемент. Чаше всего в качестве базового элемента выбирается поле, содержащее значение за предыдущий период, чтобы увидеть изменения. Познавательный интернет-ресурс о возможностях и способах работы в среде табличного редактора Microsoft Excel. Работа с таблицами, данными, формулами и функциями. Правила построения графиков и диаграмм. Создание формул и пользовательских форматов. Организация информационного обеспечения малого бизнеса. Настройка сводной таблицы Основные косметические изменения сводных таблиц в Excel Выполнение практически невозможных вычислений в сводной таблице Excel с помощью функции ПОЛУЧИТЬ. Отслеживание относительной важности с помощью функции Индекс в сводной таблице Excel Сравнение строк сводной таблицы Excel с помощью функции Доля. Ранжирование данных в сводных таблицах Excel Отображение изменений по сравнению с предыдущим полем в сводной таблице Excel Как управлять расходами Как покупать компьютеры Необходимый софт Хранение информации Как защитить информацию? Организация документооборота Техника вокруг компьютера Локальная сеть малого офиса Интернет в офисе Связь в современном офисе Сервисы Google. Повышаем производительность Windows 7 Операции с файлами и папками Работа с фотографиями, аудио- и видеозаписями Настройки Windows 7 для всех Настройки сети и сетевых файлов Эффективность работы в Internet Explorer Безопасность работы в Интернете Настройки электронной почты Оптимизируем работу ноутбуков Защищаем данные компьютера скрытых параметров Windows 7. Настройка сохранения в Windows 7 Установка Windows 7 Интерфейс Windows 7 Производительность Windows 7 Резервное копирование Windows 7. Зачем знать показатели веб-трафика Методики и точность веб-анализа Функциональные возможности, преимущества и ограничения Google Analytics Интерфейс Google Analytics Описание отчетов Google Analytics Установка Google Analytics Как внедрить Google Analytics. Выпадающие списки Даты и время Работа с текстом. Диаграммы Методы Базы данных Администрирование Данные Сеть Критерии Расходы Интерфейс Создание Фильтр Анализ Оптимизация PowerPivot Инструменты Google Analytics Макросы Даты и время Безопасность Мастер Интернет Источник Форматирование Оснастка Информация Настройка Таблица Совместимость Поля Проекты Параметры Связь Web Сотрудники Файлы Система Бизнес Компьютеры Софт Элемент E-mail Структура Оборудование Практикум Функции.
Сводные таблицы - это один из мощнейших инструментов Excel. Они позволяют анализировать и подводить различные итоги для больших объёмов данных с помощью всего лишь нескольких щелчков мышью. В этой статье мы познакомимся со сводными таблицами, разберёмся, что они собой представляют, научимся создавать их и настраивать. При написании данной статьи был использован Excel Концепция сводных таблиц почти не изменялась долгие годы, но способ их создания немного различен в каждой новой версии Excel. Если у Вас версия Excel не года, то будьте готовы, что снимки экранов в данной статье будут отличаться от того, что Вы увидите на своём экране. На заре развития программ для создания электронных таблиц балом правил Lotus Его превосходство было настолько полным, что усилия Microsoft, направленные на разработку собственного программного обеспечения Excel , как альтернативы Lotus, казались пустой тратой времени. А теперь перенесёмся в год! Excel доминирует среди электронных таблиц более, чем Lotus кода-либо за всю свою историю, а число людей, которые до сих пор используют Lotus, стремится к нулю. Как это могло произойти? Что послужило причиной для такого драматического разворота событий? Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus и положили начало успеху Microsoft Excel. Говоря простым языком, сводные таблицы - это итоги каких-то данных, созданные для облегчения анализа этих данных. В отличие от созданных вручную итогов, сводные таблицы Excel интерактивны. После создания, Вы можете легко изменять их, если они не дали той картины, которую Вы надеялись получить. Всего лишь парой щелчков мышью итоги могут быть перевёрнуты таким образом, что заголовки столбцов становятся заголовками строк и наоборот. Со сводными таблицами можно проделать множество различных действий. Вместо того, чтобы пытаться описать на словах все возможности сводных таблиц, проще продемонстрировать это на практике…. Данные, которые Вы анализируете с помощью сводных таблиц, не могут быть какими попало. Это должны быть необработанные исходные данные, вроде какого-то списка. Например, это может быть список совершённых продаж в компании за последние шесть месяцев. Обратите внимание, что это не сырые исходные данные, поскольку для них уже подведены итоги. Где же тогда исходные данные? Где исходный список продаж, из которого этот итог за месяц был получен? Ясно, что кто-то совершил огромный труд по упорядочиванию и сортировке всех данных о продажах за последние шесть месяцев и превратил их в таблицу итогов, которую мы видим. Сколько, по-вашему, это заняло времени? Дело в том, что таблица, приведённая выше, это не сводная таблица. Она была создана вручную из исходных данных, сохранённых где-то ещё, и их обработка заняла минимум пару часов. Именно такую таблицу итогов можно создать, используя сводные таблицы, потратив на это всего лишь несколько секунд. Возможно, Вас удивит, что из этого списка торговых операций с помощью сводных таблиц и всего за несколько секунд, мы можем создать в Excel помесячный отчёт о продажах, что мы разбирали выше. Да, мы сможем сделать это и еще многое другое! Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций — самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: Затем на вкладке Insert Вставка выберите команду PivotTable Сводная таблица:. Появится диалоговое окно Create PivotTable Создание сводной таблицы с двумя вопросами для Вас:. Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: В данном примере мы выберем вариант — New Worksheet На новый лист:. Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List Поля сводной таблицы. Список полей в верхней части диалогового окна — это перечень всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как требуется обобщить данные. Пока эти области пусты, в таблице тоже ничего нет. Всё, что от нас требуется, это перетащить заголовки из верхней области в пустые области внизу. При этом автоматически формируется сводная таблица, в соответствии с нашими инструкциями. Если мы допустили ошибку, то можно удалить заголовки из нижней области либо перетащить другие им на замену. Область Values Значения , вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги сумма, среднее, максимум, минимум и т. Это, почти всегда, численные значения. Отличный кандидат на место в этой области — данные под заголовком Amount Стоимость нашей исходной таблицы. Перетащим этот заголовок в область Values Значения:. Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values Значения появилась запись Sum of Amount Сумма по полю Amount , указывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы. Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас. Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson Торговый представитель в область Row Labels Строки:. За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго. Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать. Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method Способ оплаты. Просто перетащите заголовок Payment Method в область Column Labels Колонны:. Перетащите заголовок Package Комплекс в область Report Filter Фильтры:. Это даёт нам возможность отфильтровать отчёт по признаку "Какой комплекс отдыха был оплачен". Например, мы можем видеть разбивку по продавцам и по способам оплаты для всех комплексов или за пару щелчков мышью изменить вид сводной таблицы и показать такую же разбивку только для заказавших комплекс Sunseekers. Итак, если Вы правильно это понимаете, то нашу сводную таблицу можно назвать трёхмерной. Если вдруг выясняется, что в сводной таблице должны выводится только оплата чеком и кредитной картой то есть безналичный расчёт , то мы можем отключить вывод заголовка Cash Наличными. Для этого рядом с Column Labels нажмите стрелку вниз и в выпадающем меню снимите галочку с пункта Cash:. Давайте посмотрим, на что теперь похожа наша сводная таблица. Как видите, столбец Cash исчез из нее. Очевидно, что сводные таблицы — это очень мощный инструмент, но до сих пор результаты выглядят как-то незамысловато и скучно. Например, цифры, которые мы суммируем, не похожи на суммы в долларах — это просто какие-то цифры. Велик соблазн сделать привычные в такой ситуации действия и просто выделить всю таблицу или весь лист и использовать стандартные кнопки форматирования чисел на панели инструментов, чтобы задать нужный формат. Нам же нужен способ сделать его почти постоянным. Во-первых, найдём запись Sum of Amount в области Values Значения и кликнем по ней. В появившемся меню выберем пункт Value Field Settings Параметры полей значений:. Нажмите кнопку Number Format Числовой формат , откроется диалоговое окно Format Cells Формат ячеек:. Из списка Category Числовые форматы выберите Accounting Финансовый и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК , чтобы вернуться назад к нашей сводной таблице. Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…. Откройте вкладку PivotTable Tools: Design Работа со сводными таблицами: Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles Стили сводной таблицы , чтобы увидеть обширную коллекцию встроенных стилей:. Иногда приходится фильтровать данные по датам. Например, в нашем списке торговых операций присутствует много-много дат. Excel предоставляет инструмент для группировки данных по дням, месяцам, годам и т. Давайте посмотрим, как это делается. Для начала уберите запись Payment Method из области Column Labels Колонны. Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked Дата бронирования:. Как видите, это временно сделало нашу сводную таблицу бесполезной. Excel cоздал отдельный столбец для каждой даты, в которую была совершена торговая операция. В итоге мы получили очень широкую таблицу! Чтобы исправить это, кликните правой кнопкой мыши по любой дате и выберите из контекстного меню пункт Group Группировать:. Появится диалоговое окно группировки. Мы выбираем Months Месяцы и жмём ОК:. Кстати, эта таблица практически идентична той, которая была показана в начале статьи, где итоги продаж были составлены вручную. Есть еще один очень важный момент, который необходимо знать! Вы можете создать не один, а несколько уровней заголовков строк или столбцов:. Для начала кликните на Sum of Amount и из появившегося меню выберите Value Field Settings Параметры полей значений:. В списке Summarize value field by Операция в диалоговом окне Value Field Settings Параметры поля значений выберите Average Среднее:. Заодно, пока мы здесь, давайте изменим Custom Name Пользовательское имя с Average of Amount Количество по полю Amount на что-нибудь покороче. Введите в этом поле что-нибудь вроде Avg:. Нажмите ОК и посмотрите, что получилось. Обратите внимание, все значения изменились с итоговых сумм на средние значения, а заголовок таблицы в левой верхней ячейке поменялся на Avg:. Если захотеть, то можно получить сразу сумму, среднее и количество продаж , размещённые в одной сводной таблице. Сводные таблицы Microsoft Excel содержат очень-очень много функций и настроек. В такой небольшой статье их все не охватить даже близко. Чтобы полностью описать все возможности сводных таблиц, потребовалась бы небольшая книга или большой веб-сайт. Смелые и любознательные читатели могут продолжить исследование сводных таблиц. Для этого достаточно щелкать правой кнопкой мыши практически на любом элементе сводной таблицы и смотреть, какие открываются функции и настройки. На Ленте Вы найдёте две вкладки: Options Анализ и Design Конструктор. Не бойтесь допустить ошибку, всегда можно удалить сводную таблицу и начать все заново. У Вас есть возможность, которой никогда не было у давних пользователей DOS и Lotus Урок подготовлен для Вас командой сайта office-guru. Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel. Оказываем помощь по MS Excel. Об авторе Правила сайта Блоги тестирование Контакты. Уроки MS Excel Самоучитель по Excel для чайников Уроки MS Word Каталог шрифтов Рекомендую. MS Excel Таблицы и сводные таблицы Работа со сводными таблицами в Microsoft Excel. Немного истории Что такое сводные таблицы? Как создать сводную таблицу? Настройка сводной таблицы Форматирование сводных таблиц Прочие настройки сводных таблиц Заключение Немного истории На заре развития программ для создания электронных таблиц балом правил Lotus Аналитики выделяют два основных фактора: Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows - это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus для Windows впрочем, только несколько лет , предсказывая, что DOS-версия их программного обеспечения - это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows. Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus , в котором их не было. Что такое сводные таблицы? Итак, как же лучше охарактеризовать, что собой представляют сводные таблицы? Вместо того, чтобы пытаться описать на словах все возможности сводных таблиц, проще продемонстрировать это на практике… Данные, которые Вы анализируете с помощью сводных таблиц, не могут быть какими попало. Посмотрите на данные, показанные на рисунке ниже: Давайте разберёмся, как… Если вернуться к исходному списку продаж, то он выглядел бы примерно так: Итак, запускаем Excel… и загружаем такой список… После того, как мы открыли этот список в Excel, можем приступить к созданию сводной таблицы. Выделите любую ячейку из этого списка: Затем на вкладке Insert Вставка выберите команду PivotTable Сводная таблица: Появится диалоговое окно Create PivotTable Создание сводной таблицы с двумя вопросами для Вас: Какие данные использовать для создания новой сводной таблицы? Куда поместить сводную таблицу? В данном примере мы выберем вариант — New Worksheet На новый лист: Excel создаст новый лист и разместит на нем пустую сводную таблицу: Перетащим этот заголовок в область Values Значения: Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson Торговый представитель в область Row Labels Строки: Наша сводная таблица начинает обретать форму… Видите преимущества? Настройка сводной таблицы Во-первых, мы можем создать двумерную сводную таблицу. Просто перетащите заголовок Payment Method в область Column Labels Колонны: Теперь сделаем трёхмерную таблицу. Как может выглядеть такая таблица? Давайте посмотрим… Перетащите заголовок Package Комплекс в область Report Filter Фильтры: Заметьте, где он оказался… Это даёт нам возможность отфильтровать отчёт по признаку "Какой комплекс отдыха был оплачен". Продолжим настраивать… Если вдруг выясняется, что в сводной таблице должны выводится только оплата чеком и кредитной картой то есть безналичный расчёт , то мы можем отключить вывод заголовка Cash Наличными. Для этого рядом с Column Labels нажмите стрелку вниз и в выпадающем меню снимите галочку с пункта Cash: Форматирование сводных таблиц в Excel Очевидно, что сводные таблицы — это очень мощный инструмент, но до сих пор результаты выглядят как-то незамысловато и скучно. В появившемся меню выберем пункт Value Field Settings Параметры полей значений: Появится диалоговое окно Value Field Settings Параметры поля значений. Нажмите кнопку Number Format Числовой формат , откроется диалоговое окно Format Cells Формат ячеек: Как видите, числа оказались отформатированы как суммы в долларах. Используем тот, что попроще… Откройте вкладку PivotTable Tools: Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles Стили сводной таблицы , чтобы увидеть обширную коллекцию встроенных стилей: Выберите любой подходящий стиль и посмотрите на результат в своей сводной таблице: Прочие настройки сводных таблиц в Excel Иногда приходится фильтровать данные по датам. Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked Дата бронирования: Чтобы исправить это, кликните правой кнопкой мыши по любой дате и выберите из контекстного меню пункт Group Группировать: Мы выбираем Months Месяцы и жмём ОК: От такой таблицы намного больше пользы: Вы можете создать не один, а несколько уровней заголовков строк или столбцов: Вернёмся к исходному виду таблицы и посмотрим, как вывести средние значения вместо сумм. Для начала кликните на Sum of Amount и из появившегося меню выберите Value Field Settings Параметры полей значений: В списке Summarize value field by Операция в диалоговом окне Value Field Settings Параметры поля значений выберите Average Среднее: Введите в этом поле что-нибудь вроде Avg: Обратите внимание, все значения изменились с итоговых сумм на средние значения, а заголовок таблицы в левой верхней ячейке поменялся на Avg: Вот пошаговая инструкция, как сделать это, начиная с пустой сводной таблицы: Перетащите заголовок Salesperson Торговый представитель в область Column Labels Колонны. Трижды перетащите заголовок Amount Стоимость в область Values Значения. Для первого поля Amount измените название на Total Сумма , а формат чисел в этом поле на Accounting Финансовый. Количество десятичных знаков равно нулю. Второе поле Amount назовите Averag e, операцию для него установите Average Среднее и формат чисел в этом поле тоже измените на Accounting Финансовый с числом десятичных знаков равным нулю. Общая сумма, среднее значение и количество продаж — всё в одной сводной таблице! Заключение Сводные таблицы Microsoft Excel содержат очень-очень много функций и настроек. Войдите или зарегистрируйтесь чтобы добавлять комментарии. Как сохранить в формате Excel Десятичные знаки в Excel. Как удалить дубликаты в Excel. Как в Excel сохранить диаграмму как картинку. Добавляем разрывы строк в Microsoft Excel. Увеличиваем скорость заполнения диапазона данными в Excel. Серии статей и самоучители. Самоучитель по Excel 30 функций Excel за 30 дней примеров по Excel Работа с макросами в Excel Самоучитель по Excel VBA Сводные таблицы в Excel. Интерфейс и настройка Листы и книги Ячейки и диапазоны Форматирование Диаграммы и графика Формулы и функции: Ссылки и массивы Математические Текстовые Логические Даты и времени Статистические Проверка свойств и значений. Оказываем помощь по MS Excel Подробнее.
https://gist.github.com/b26515ff02280eb8ed66d497a243efee
https://gist.github.com/d7107d2498384e6109e7782b885c123a
https://gist.github.com/1e13e3e1f3cf62a48fa46671c70879ea