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

  • автор:

В качестве примера я подготовил небольшую таблицу, фрагмент которой показан на рис. 1. Таблица расположена на листе с именем «БД». Она представляет собой базу данных, где записаны подробные сведения о движении ТМЦ. В таблице восемь полей:

— «Дата» — дата реализации товара;

— «Склад» — место хранения, с которого был отпущен товар;

— «Покупатель» — контрагент, купивший товар;

— «ИНН» — идентификационный код покупателя;

— «НаимТов» — название проданного товара;

— «Кол-во» — объем реализации (в штуках);

— «Цена» — цена за единицу товара;

— «Сумма» — стоимость отгруженного товара.

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

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

И все будет нормально, пока нам не понадобится взять отдельные цифры из этой сводной таблицы и подставить их в другой отчет. Здесь сразу же возникнет проблема. В любом случае для решения задачи придется задействовать ссылки на ячейки сводной таблицы. Чтобы эти ссылки работали правильно, после каждой корректировки данных сводную таблицу придется обновлять. Но и это не все! При добавлении новых записей в базу размеры сводного отчета могут измениться. И тогда все ссылки придется полностью поменять… Отсюда следует вывод: сводная таблица хороша, если она используется сама по себе. Но как только итоги нужно посчитать и вставить в отчет произвольной формы, лучше оперировать формулами, обратившись к встроенным функциям MS Excel.

Таких функций существует много. Это и обработка баз данных, и определение промежуточных итогов, и многое другое. О них мы обязательно поговорим в одной из наших статей. Но сейчас я предлагаю ограничиться двумя вариантами — это функции «СУММПРОИЗВ()» и «СУММЕСЛИМН()». На мой взгляд, полезность этих функций для практикующего бухгалтера трудно переоценить. Приступим.

Определение итогов при помощи функции «СУММПРОИЗВ()»

В бухгалтерских расчетах функцию «СУММПРОИЗВ()» используют нечасто. Может быть, потому, что находится она в категории «Математические». А может, причина в том, что официальная справка по этой функции недостаточно хороша, — на этот вопрос я не готов ответить. Но в любом случае максимум, что делают при помощи «СУММПРОИЗВ()», — поэлементно перемножают строки (или колонки) и считают сумму полученных произведений.

Синтаксис формулы выглядит так: «=СУММПРОИЗВ(Массив1; Массив2; Массив3;…; МассивN)», где «МассивX» — блок ячеек на рабочем листе. Количество таких блоков не должно превышать 255. Размеры всех блоков должны быть одинаковыми.

Важно! Все параметры функции «=СУММПРОИЗВ()» должны быть или фрагментами строк, или частью колонок. Строки на столбцы функция перемножать не умеет.

Простейший пример использования функции «=СУММПРОИЗВ()» — найти сумму продаж, если известна цена и объем реализованного товара. Применительно к базе на рис. 1 такая формула будет выглядеть так: «=СУММПРОИЗВ(F:F;G:G)». С этим все понятно. Но на самом деле функция «=СУММПРОИЗВ(F:F;G:G)» обладает гораздо более широкими, даже уникальными возможностями. Для иллюстрации этих возможностей предлагаю вернуться к нашей задаче и построить отчет о движении товаров с детализацией по каждому складу предприятия. Для определенности будем считать объемы в количественном выражении. Теперь делаем так:

1. Открываем файл с базой данных, создаем новый лист. Я назвал его «СТ».

2. Щелкаем на ячейке «A1» и вводим текст «Наим. товара».

3. В ячейку «B1» пишем заголовок «Номер склада».

4. Выделяем блок ячеек «B1:D1».

5. Щелкаем в выделенном блоке правой кнопкой мыши. Из контекстного меню выбираем пункт «Формат ячеек…». Откроется одноименное окно «Формат ячеек», как на рис. 2.

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

7. В окне «Формат ячеек» нажимаем «ОК», — таким образом мы объединили ячейки «B1» и «D1».

8. В ячейки «B2», «C2», «D2» вводим названия мест хранения. У нас это будут значения «001», «002», «003».

9. В блоке ячеек «A3:A11» заносим наименования товаров.

11. Копируем формулу вправо и вниз на всю высоту сводного отчета (это блок ячеек с «B3» до «D11»).

12. В ячейку «E3» вводим формулу «=СУММ(B3:D3)». Копируем ее вниз до ячейки «E11». Результат нашей работы показан на рис. 3.

13. Сохраняем файл с именем «Отчет_1.xls», — он нам еще пригодится.

Вот и все! Мы построили сводный отчет при помощи встроенной функции «=СУММПРОИЗВ()» программы Excel. Преимущества этого подхода очевидны. При любых изменениях в исходной базе итоги Excel обновит автоматически. Формулы можно использовать в любом документе или перенести в произвольную ячейку рабочего листа, — в этом плане нет никаких ограничений.

Ключевым элементом формулы является функция «=СУММПРОИЗВ()». Первые два параметра функции работают как логические выражения.

Берем фрагмент формулы «БД!$B$2:$B$65536=B$2». Внутри функции «=СУММПРОИЗВ()» это выражение работает так. Каждое значение из блока «B2:B65536» основной базы (лист «БД») Excel сравнит с содержимым ячейки «B2» сводного отчета. Фактически он сравнит номер склада из колонки «B» базы данных со значением «001» (рис. 3). Если номер склада в базе (колонка «B») равен «001», результатом сравнения будет «ИСТИНА». В противном случае мы получим «ЛОЖЬ». По такой же схеме работает выражение «БД!$E$2:$E$65536=$A3. Только сравнивает оно наименования товаров из колонки «E» базы данных с названием в ячейке «A3» сводного отчета.

После обработки условий Excel перемножит полученные результаты. Если оба логических выражения вернут значение «ИСТИНА» (и склад, и название товаров совпадают с указанными в сводном отчете), результат умножения будет равен «1». Если хотя бы один из сомножителей окажется ложным, мы получим «0».

Этот результат Excel умножит на объем реализации из колонки «F» основной базы, после чего просуммирует полученные значения. В итоге все цифры, которые не попадают под условие отбора, будут умножены на «0». Оставшиеся объемы войдут в сумму с коэффициентом «1». А в результате формула найдет сумму всех объемов из диапазона «БД!$F$2:$F$65536», для которых номер склада и название товара совпадают с теми, которые указаны в ячейках «B2» и «A3» сводного отчета.

Подробный пример расчетов по указанному алгоритму для ячейки «M2» (склад «001», позиция «Карандаш») выглядит так:

№ строки

Склад

НаимТов

Усл.1

(Склад= «001»)

Усл.2 (ТМЦ=»Карандаш»)

Усл1*Усл2

Кол-во

Сумма

Скоросшиватель

ИСТИНА

ЛОЖЬ

Бумага оф.

ЛОЖЬ

ЛОЖЬ

Блокнот

ЛОЖЬ

ЛОЖЬ

Бумага оф.

ЛОЖЬ

ЛОЖЬ

Ластик канц.

ЛОЖЬ

ЛОЖЬ

Ластик канц.

ИСТИНА

ЛОЖЬ

Блокнот

ИСТИНА

ЛОЖЬ

Скрепка канц.

ЛОЖЬ

ЛОЖЬ

Скоросшиватель

ЛОЖЬ

ЛОЖЬ

Бумага оф.

ИСТИНА

ЛОЖЬ

Скрепка канц.

ИСТИНА

ЛОЖЬ

Ручка шар.

ЛОЖЬ

ЛОЖЬ

Блокнот

ЛОЖЬ

ЛОЖЬ

Файлик пласт.

ИСТИНА

ЛОЖЬ

Скоросшиватель

ЛОЖЬ

ЛОЖЬ

Скоросшиватель

ИСТИНА

ЛОЖЬ

Скотч

ЛОЖЬ

ЛОЖЬ

Файлик пласт.

ИСТИНА

ЛОЖЬ

Ручка шар.

ЛОЖЬ

ЛОЖЬ

Скоросшиватель

ЛОЖЬ

ЛОЖЬ

Файлик пласт.

ИСТИНА

ЛОЖЬ

Карандаш

ИСТИНА

ИСТИНА

Ручка шар.

ЛОЖЬ

ЛОЖЬ

Карандаш

ИСТИНА

ИСТИНА

Блокнот

ИСТИНА

ЛОЖЬ

Скотч

ЛОЖЬ

ЛОЖЬ

Ластик канц.

ИСТИНА

ЛОЖЬ

 

По такому же принципу при помощи функции «=СУММПРОИЗВ()» можно сформировать сводные отчеты практически неограниченной сложности. Все, что да этого нужно, — правильно написать условия для отбора и суммирования записей. В качестве примера я предлагаю построить таблицу, в которой показать динамику движения ТМЦ (по датам) по каждому складу. Иными словами, в этой таблице мы хотим отразить: сколько, какого товара и когда было отпущено с конкретного склада. Для этого делаем так.

1. В документе «Отчет_1.xls» создаем новый лист.

2. В ячейку «A1» этого листа пишем текст «Наим. товара».

3. В ячейку «B1» вводим дату «01.10.2012».

4. В «С1» вводим формулу «=B1+1», копируем ее вправо до ячейки «F1».

5. В ячейки «B2:F2» вводим текст «001» (номер первого склада).

6. В «G1» пишем текст «Итого» — здесь будет посчитана сумма реализации каждого товара.

7. Выделяем блок ячеек «A1:A2».

8. Щелкаем на выделенном блоке правой кнопкой мыши.

9. Из контекстного меню выбираем пункт «Формат ячеек…». Откроется окно «Формат ячеек», как на рис. 2.

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

11. В окне «Формат ячеек» нажимаем «ОК». Таким образом мы объединили ячейки «A1» и «A2».

12. Аналогичным образом объединяем ячейки «G1» и «G2».

13. Выделяем блок «B1:G2», копируем в буфер (комбинация «Ctrl+C») и вставляем дважды, начиная с ячеек «H1» и «N1».

14. В ячейках «H2:L2» и «N2:R2» изменяем номера мест хранения (рис. 4). Шапка отчета готова.

15. В ячейку «B3» вводим формулу «=СУММПРОИЗВ((БД!$A$2:$A$65536=B$1)* (БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3)*(БД!$F$2:$F$65536))».

16. Копируем эту формулу в ячейки «B3:F11», затем — в ячейки «H3:L11» и «N3:R11».

17. В «G3» вводим формулу суммирования «=СУММ(B3:F3)».

18. Копируем формулу из «G3» в ячейки «M3» и «S3».

19. Копируем все формулы вниз на всю высоту таблицы. Результат нашей работы показан на рис. 4.

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

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

1. Открываем документ «Отчет_1.xls».

2. Переходим на лист «СТ».

3. В ячейку «F1» вводим начальное значение интервала, пусть это будет «03.10.2012».

5. Копируем формулу в ячейки «B3:D11». Мы построили сводный отчет, включив в него данные только за «03/10/12».

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

1. В ячейку «F2» на листе «СТ» вводим значение «05.10.2012». Это будет конечная дата временного интервала. Дата начала находится в ячейке «F1», это «03.10.2012».

2. В «B3» вводим формулу: «=СУММПРОИЗВ((БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3) *(БД!$A$2:$A$65536>$F$1)*(БД!$A$2:$A$65536<$F$2)*(БД!$F$2:$F$65536))».

3. Копируем формулу в ячейки «B3:D11».

Мы построили сводный отчет, куда попадут данные только за «04/10/12». Изменяя значения в ячейках «F1» и «F2», мы сможем получить сводную таблицу для произвольного интервала времени. Между прочим, ввести такие гибкие условия отбора записей при работе с Мастером сводных таблиц проблематично. Так что использование формул в этом смысле имеет большие преимущества.

Расчет итогов при помощи функции «СУММЕСЛИМН()»

Среди новых возможностей программы Excel 2010 я хотел бы обратить ваше внимание на встроенную функцию «СУММЕСЛИМН()». Похожая функция была и в предыдущих версиях программы. Она называлась «СУММЕСЛИ()» и позволяла просуммировать данные из указанного диапазона по некоторому условию. В бухгалтерских расчетах функция «СУММЕСЛИ()» оказалась очень полезной. Например, с ее помощью можно найти сумму всех положительных чисел в указанном блоке ячеек. Или просуммировать данные за определенный промежуток времени и т.п. Однако у функции «СУММЕСЛИ()» было существенное ограничение: она могла оперировать всего одним условием. Этого недостатка лишена функция «СУММЕСЛИМН()». Синтаксис у нее такой: «СУММЕСЛИМН(ДСумм; ДУсл1; Усл1; ДУсл2; Усл2; …, ДУслN; УслN;)». У функции минимум три параметра:

— «ДСумм» — блок рабочего листа, откуда функция будет накапливать сумму;

— «ДУсл1» — блок значений для проверки условия суммирования;

— «Усл1» — выражение для проверки условия суммирования.

В таком варианте (с тремя параметрами) функции «СУММЕСЛИМН()» и «СУММЕСЛИ()» эквивалентны. Алгоритм работы «СУММЕСЛИМН()» очень прост. Функция просматривает значения в блоке «ДУсл1», сверяет их с выражением «Усл1». Если условие выполняется, она накапливает сумму из соответствующих ячеек в блоке «ДСумм». Кстати, блоки «ДСумм» и «ДУсл1» могут совпадать. Например, чтобы найти объем реализации товаров по складу «001» (база данных, как на рис. 1), можно воспользоваться одной из формул: «=СУММЕСЛИМН(F2:F28;B2:B28;1)» или «=СУММЕСЛИ(B2:B28;1;F2:F28)».

Важно! У функций «=СУММЕСЛИМН()» и «СУММЕСЛИ()» отличается порядок следования параметров. У «СУММЕСЛИ()» вначале идет блок значений для проверки, затем условие и после него — диапазон суммирования. В «=СУММЕСЛИМН()» первым указывают диапазон суммирования, а затем блок значений и логическое условие для отбора.

В функции «=СУММЕСЛИМН()» можно ввести несколько условий для выборочного суммирования. В этом случае они работают по принципу «И». То есть накопление суммы происходит, если выполняются все заданные условия для отбора значений.

Теперь посмотрим, как применить функцию «=СУММЕСЛИМН()» для формирования сводных отчетов в программе Excel 2010. Для этого с помощью «=СУММЕСЛИМН()» мы построим таблицу, изображенную на рис. 3. База данных у нас будет та же. А дальше делаем так.

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

2. Переходим на ячейку «В3». Вводим формулу: «=СУММЕСЛИМН(БД!$F:$F;БД!$B:$B;B$2; БД!$E:$E;$A3)».

3. Копируем эту формулу во все ячейки блока «B3:D11».

4. В ячейку «E3» вводим формулу «=СУММ(B3:D3)». Копируем ее вниз до ячейки «E11».

5. Сохраняем документ с именем «Отчет_2.xls».

Мы получили точную копию отчета, изображенного на рис. 3. В данном случае существенной разницы между функциями «=СУММЕСЛИМН()» и «=СУММПРОИЗВ()» нет. Правда (на мой взгляд), параметры у «=СУММЕСЛИМН()» выглядят проще и понятнее. Но здесь есть одна тонкость. Чтобы разобраться с ней, делаем так.

1. Открываем документ «Отчет_1.xls», как на рис. 3. Напомню, что в этой таблице для определения итогов мы использовали функцию «=СУММПРОИЗВ()».

2. Переходим на лист с базой данных (он называется «БД»).

3. Выделяем все данные на этом листе и переносим их в буфер обмена (иконка «Вырезать» на ленте главного меню или комбинация горячих клавиш «Ctrl+X»).

Важно! В данном случае нужно использовать именно операцию «Вырезать». Копирование данных не даст должного эффекта.

4. Создаем новый документ (комбинация «Ctrl+N»).

5. На свободный лист этого документа вставляем данные из буфера обмена.

6. Сохраняем документ с именем «База.xls».

7. Возвращаемся в документ «Отчет_1.xls».

8. Щелкаем правой кнопкой мыши на ярлычке листа «БД».

9. Из контекстного меню выбираем вариант «Удалить». MS Excel выдаст предупреждение, что удаление листов является необратимой операцией, отменить ее не удастся (рис. 5).

10. Соглашаемся с этим, нажимаем кнопку «Удалить».

11. Сохраняем документ с итоговым отчетом и закрываем его. При сохранении я указал имя «Отчет_11.xls».

Повторим то же самое с документом «Отчет_2.xls», где итоги посчитаны функцией «СУММЕСЛИМН()». То есть — открываем файл. Становимся на лист с базой данных. Переносим ее содержимое в новый файл и сохраняем его с тем же именем — «База.xls». Затем удаляем лист «БД» из файла «Отчет_2.xls» и сохраняем результат с именем «Отчет_12.xls». В результате мы получили два сводных отчета. Один из них использует функцию «=СУММПРОИЗВ()», а в другом мы применили «СУММЕСЛИМН()». Оба документа ссылаются на одну и ту же базу данных с именем «База.xls». А дальше делаем так.

1. Открываем файл «Отчет_11.xls». Появится окно с предложением обновить связи между базой и текущим документом (рис. 6).

2. В этом окне нажимаем «Обновить». На экране появится документ с итоговым отчетом, как на рис. 3.

Важно! Несмотря на то что файл с базой данных при этом закрыт, все итоги функция «=СУММПРОИЗВ()» посчитала правильно.

3. Закрываем документ (комбинация «Ctrl+F4»).

5. В этом окне нажимаем «Обновить». На экране появится документ с итоговым отчетом, как на рис. 3. Однако вместо итогов мы увидим текст «#ЗНАЧ!». Это означает, что данные для расчетов в данный момент недоступны.

6. Не закрывая документ «Отчет_12.xls», открываем базу данных. Для этого нажимаем комбинацию «Ctrl+O» в окне Проводника, выбираем файл «База.xls» и щелкаем на кнопке «Открыть».

7. Возвращаемся в документ «Отчет_12.xls». На месте итогов появятся правильные значения.

Важно! Функция «СУММЕСЛИМН()» не может получить данные из закрытого файла.

И последнее. В некоторых версиях MS Excel действует такое правило. Чтобы функция «=СУММПРОИЗВ()» смогла прочитать данные из закрытого файла, формулу нужно начать с символов «—» (два знака минус, т. н. двойное бинарное отрицание).

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

Приходилось ли вам когда-нибудь попадать в такую ситуацию?:

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

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

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

И так, что же такое Сводные Таблицы, и чем они отличаются от обычных таблиц?

Допустим, на основании выгруженных из учетной системы данных, мы «нарисовали» простенькую таблицу — продажи по Супервайзерам в разрезе Менеджеров и Типов клиентов, за какой-то период. Вбили формулы, «раскрасили», отформатировали… У нас получилось что-то вроде этого:

После того, как мы нарисовали такую таблицу, нашему руководителю или нам самим, кажется, что мы получили, что-то не то… Нам, на самом деле, было бы интересно посмотреть, как Менеджеры продавали Товарные категории, тому или иному Типу клиентов… Что мы делаем? Правильно, берем и «перерисовываем» эту таблицу… Сколько уйдет на это времени, прикидывайте сами… Но в итоге у нас получиться что-то вроде этого:

А теперь внимание!!! Я покажу как при использовании Сводных Таблиц, происходит превращение первой таблицы во вторую:

Перед Вами пошаговое руководство по созданию сводных таблиц в Excel.

Мы начнём с того, что дадим ответ на самый простой вопрос: «Что же это такое – сводные таблицы в Excel?” – и далее покажем, как в Excel создать простейшую сводную таблицу.

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

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

Рекомендуется начать с 1-й части самоучителя и изучать пособие по сводным таблицам Excel последовательно.

  • Часть 1: Что такое сводная таблица в Excel?
  • Часть 2. Создаём простейшую сводную таблицу в Excel?
  • Часть 3: Группировка в сводной таблице.
  • Часть 4: Продвинутые сводные таблицы в Excel.
  • Часть 5: Сортировка в сводной таблице.

Дальнейшее углублённое обучение работе со сводными таблицами можно найти на сайте Microsoft Office.

Урок подготовлен для Вас командой сайта office-guru.ru

Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

1

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

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

2

Эта таблица должна быть заполнена, то есть, надо узнать сумму по выручке с нужных регионов по каждой из товарных позиций. Эта задача легко выполняется, если использовать функцию СУММЕСЛИМН. Кроме того, нужно добавить итоги. После этого появится сводный отчет по каждой области.

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

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

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

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

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

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

3

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

4

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

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

5

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

6

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

7

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

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

Составные компоненты

В состав сводной таблицы входят такие компоненты:

  1. Область значений. Под ней подразумевается главная часть таблицы со значениями. Excel их получает с помощью агрегирования исходных данных тем методом, который выберет пользователь. Как правило, это делается с помощью Суммирования. Этот метод установлен автоматически при условии, что все данные в исходном диапазоне находятся в ячейке, имеющей числовой формат. Если же хотя бы в одной ячейке есть текст или отсутствует какая-либо информация, то автоматически будет осуществляться подсчет количества ячеек. Всего есть около 20 различных видов вычислений. наиболее просто изменить его с помощью правого клика по какой-угодно ячейке необходимого поля сводной таблицы и осуществить выбор метода агрегирования.
  2. Область строк. Собственно, сюда входят названия строк, находящихся в крайнем левом столбце. Это все значения столбца, уникальные в своем роде и которые были выбраны пользователем. Здесь может быть несколько полей. Такая таблица называется многоуровневой. Как правило, здесь находятся любые не количественные данные, такие как названия товаров, регионов и так далее.
  3. Область столбцов. То же самое, что и область строк, только касается столбцов. Сюда могут входить годы, месяцы, а также группы выпускаемой продукции.
  4. Область фильтра. Применяется для того, чтобы показывать лишь определенные значения, соответствующие конкретному признаку. Например, есть возможность отображения данных лишь по конкретной отрасли, за определенный период и так далее. В этом случае необходимо поместить поле фильтрации в область фильтра и выбрать необходимое значение в списке, который раскрывается.

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

8

После этого перед нами появится полноценная сводная таблица.

9

И что удивительно? Чтобы ее создать, потребовалось всего 10 секунд.

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

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

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

10

С помощью простого перетаскивания можно поменять местами товары и области.

11

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

12

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

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

Полезные советы по подбору источника данных

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

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

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

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

13

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

14

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

Как создать дашборд в Excel

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

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

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

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

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

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

  1. Фигуры и объекты Word Art. Они позволяют рисовать все, что угодно, вплоть до инженерных чертежей. Кроме этого, есть множество текстовых меток, которые позволяют описать любую составную часть дашборда.
  2. Использование сводных таблиц.
  3. Графики, которые могут в качестве данных также использовать исходный диапазон.

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

Выводы

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

Оцените качество статьи. Нам важно ваше мнение:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *