Как Раскрыть Все Плюсы в Excel в Сводной Таблице

Начиная с Excel 2007 появилась возможность использовать так называемые «умные» таблицы Excel .

Для того чтобы преобразовать обычную таблицу в «умную» нужно выделить в обычной таблице любую ячейку, на вкладке меню Главная развернуть список Форматировать как таблицу и выбрать один из понравившихся стилей (см. рисунок выше) , либо использовать команды меню Вставка -> Таблица .

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

• преобразование вашей таблицы в «умную» придаст ей презентабельный «полосатый» внешний вид;
• «умная» таблица автоматически увеличивается размерах при добавлении данных правее последнего столбца или ниже последней строки. Причем при добавлении строк формулы в ячейках сохраняются. Еще один способ добавить в таблицу строки и столбцы — просто «потащить» ее за правый нижний угол;
• можно мгновенно создать строку итогов с результатами на ваш выбор (сумма, количество, среднее, минимум, максимум и т.д.);
• Excel автоматически присваивает имена столбцам «умной» таблицы, которые можно использовать в формулах.
• с умной таблицей очень удобно работать в надстройке Power Query . На мой взгляд это главное достоинство умных таблиц.

Недостатки «умной» таблицы – это продолжение ее достоинств:

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

Кстати, если вам так хочется «полосатости», это легко можно сделать и для обычной таблицы. Достаточно задать для вашей таблицы правило условного форматирование вот с такой формулой: =ЕЧЁТН(СТРОКА()

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

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

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

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

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

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

Вот пример не самой длинной формулы в ячейке на листе «Контрагенты» в программе ES: “Финансовое планирование и учет” , которая рассчитывает сальдо по контрагенту:

Если вместо имен диапазонов были бы использованы имена столбцов, автоматически присвоенные «умной таблицей», длина формулы оказалась бы в 2-3 раза больше и разобраться в ней визуально стало бы намного сложнее.

• На защищенных листах функция ставки строк растаскивание за уголок или автоматическое расширение при добавлении данных не работает. Вставить строки внутри «умной» таблицы на защищенном листе также невозможно. И если для обычных таблиц есть «костыль» – использование методов Insert , Delete в макросах VBA, то умные таблицы Excel эти методы не поддерживают и как решить проблему вставки строк с помощью макросов пока непонятно.

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

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

[expert_bq id=»1570″]В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен таблица, которую можно менять, крутить, демонстрировать в разных проекциях. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.

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

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

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

Как сделать сводную таблицу, сгруппировать временной ряд?

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

• Как сделать сводную таблицу;
• Как с помощью сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, дням.
• Как рассчитать прогноз с помощью сводной таблицы и Forecast4AC PRO;

Для начала научимся делать сводные таблицы.

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

И в каждой строке 3-м параметра связаны между собой, т.е. например, 01.02.2010 года Товар 1 продали на 422 656 руб.

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

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

1. Диапазон с данными, которые будут выведены в сводную таблицу;
2. Куда вывести сводную (в новый лист или на существующий (если выберите на существующий, то необходимо будет указать ячейку, в которую вы хотите поместить сводную таблицу)).

Нажимаем «ОК», сводная таблица готова и выведена в новый лист. Назовем лист «Сводная».

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

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

Группировка и фильтрация временных рядов в сводной таблице

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

Для этого переходим в лист «Данные», и после даты вставляем 3 пустых столбца. Выделяем столбец «Товар» и нажимаем «Вставить».

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

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

• В столбец «Год» добавляем формулу =ГОД(со ссылкой на дату);
• В столбец «Месяц» добавляем формулу =МЕСЯЦ(со ссылкой на дату);
• В столбец «Год — Месяц» добавляем формулу =СЦЕПИТЬ(ссылка на год;» «;ссылка на месяц).

Получаем 3 столбца с годом, месяцем и годом и месяцем:

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

Теперь давайте проанализируем продажи по годам.

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

Теперь мы хотим еще более глубже «опуститься» на уровень месяцев и проанализировать продажи по годам и по месяцам. Для этого в «название столбцов» перетаскиваем поле «месяц» под год:

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

Следующая задача, мы хотим убрать из анализа продажи за какой-то месяц (например, октябрь 2012 года), т.к. данные о продажах у нас еще не за полный месяц.
Для этого в область сводной «Фильтр отчета» перетащим «Год — месяц»

Нажимаем на появившейся над сводной фильтр и ставим галочку «Выделить несколько элементов». Затем в списке с годами и номерами месяцев снимаем галочку с 2012 10 и нажимаем ОК.

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

Расчет проноза с помощью сводной таблицы и Forecast4AC PRO

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

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

Для расчета прогноза с помощью Forecast4AC PRO устанавливаем курсор в 1 января 2009 года

и нажимаем кнопку «График Модель прогноза» в меню Forecast4AC PRO

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

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Тестируйте возможности платных решений:

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

[expert_bq id=»1570″]Чтобы удалить поле, перетащите его имя из области, в которой оно находится в текущий момент ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ или ЗНАЧЕНИЯ , в любое другое место. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]

1. Щелкните на имени поля таблицы (1), содержащего слова “Сумма по полю”, за которыми следует имя поля. Перейдите на вкладку Анализ (2) набора контекстных вкладок Работа со сводными таблицами, и щелкните на кнопке Параметры поля (3).

Сводные таблицы в Excel: Создание, Фильтрация, Форматирование

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

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

Сводная таблица Excel: создание, работа с данными, удаление

Для чего в Excel нужны сводные таблицы

В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.

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

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

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

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

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

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

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

• область значений является главной частью СТ, поскольку именно здесь указывается, каким образом обрабатывать столбцы и строки. Обычно это суммирование – этот способ агрегирования будет установлен по умолчанию при условии, что все ячейки диапазона принимают числовые значения. При наличии хотя бы одной пустой ячейки или с данными в формате, отличном от числового, вместо суммы значений ячеек будет подсчитано их количество, так что этот факт нужно обязательно учитывать. Для нашего примера суммирование будет производиться по каждому товару в соответствующем регионе. Кроме суммирования, можно задать любой другой способ вычислений, которых имеется около двух десятков (вычисление среднего значения, доли и пр.). Проще всего это сделать непосредственно в сводной таблице, кликнув ПКМ по любой ячейке и выбрав необходимый способ вычислений;
• область строк – состоит из уникальных значений ячеек, расположенных в левом столбце. Если вставить сюда несколько полей, сводная таблица будет уже не плоской, а многоуровневой;
• область столбцов также содержит значения полей, но уже относительно верхней строки исходной таблицы;
• область фильтра позволяет накладывать ограничения на условия вычислений значений ячеек. Обычно сюда помещают поле, по которому должна фильтроваться выборка, заданная в трёх других областях.

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

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

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

• из перечня полей в сводной таблице перетаскиваем поле «Выручка» в область значений;
• поле «Область» (регион нахождения покупателя) перетаскиваем в область строк;
• поле «Товар» помещаем в область столбцов.

Всего три действия – и сводная таблица, пригодная для отправки начальству, готова! На её составление у нас ушло порядка 10 секунд.

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

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

Чтобы получить результаты по каждому менеджеру, достаточно в область фильтра поместить поле «Менеджер», тогда вверху появится селектор, в котором нужно указать нужную фамилию для получения отчёта. Имеется возможность объединять отчёт по нескольким продавцам.

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

Об источнике данных

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

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

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

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

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

Обновить данные в нашей сводной таблице Excel можно двумя способами:

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

Добавление в СТ Excel столбца или строки

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

Формирование сводной таблицы из нескольких файлов (таблиц) Excel

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

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

Запускаем мастер. Допустим, нам нужно объединить данные из двух листов, майского и за июнь. Главное условие – они должны совпадать по структуре. Если это так, то в первом окне мастера выбираем опции «Сводная таблица» и следующий параметр – «Несколько диапазонов консолидации».

После нажатия кнопки «Готово» откроется новое окно, в котором указываем «Новый лист» и снова жмём кнопку «Готово».

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

Как удалить СТ

Самый простой случай – когда вы создали сводную таблицу, отослали результаты шефу, и она вам больше не нужна. Если вы в этом уверены, просто выбираем таблицу и жмём клавишу Delete. Просто и эффективно.

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

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

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

• снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
• выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
• щёлкаем ПКМ внутри выделенной области;
• из контекстного меню выбираем пункт «Скопировать»;
• переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
• выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».

В итоге сводная таблица будет стёрта с сохранением результатов.

СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.

Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:

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

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

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

Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.

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

[expert_bq id=»1570″]Помещайте в область Полей страниц поля, содержащие текстовую информацию , которая может стать заголовком для формируемой сводной таблицы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] 8. Нажмем на кнопку со стрелкой вниз в ячейке B1 и в выпавшем списке вместо записи «(Все)» выберем «3», соответствующую интересующему нас заказу №3 и закроем список, нажав кнопку «OK».

Как сделать сводную таблицу в Excel — способы создания

• из перечня полей в сводной таблице перетаскиваем поле «Выручка» в область значений;
• поле «Область» (регион нахождения покупателя) перетаскиваем в область строк;
• поле «Товар» помещаем в область столбцов.

Так как все записи нашей базы данных сделаны в апреле, то после группировки мы видим всего два столбца – «апр» и «Общий итог». Когда в исходной базе данных появятся записи, датированные маем и июнем, в сводной таблице добавятся соответствующие два столбца.