Светлана

Добавлено 07.01.18, 11:16 в УРОКИ EXCEL

Создание сводной таблицы в Excel Как создать аналитический отчет сводной таблицы в Microsoft Excel. Понятно, пошагово и с подробностями. Статья и файл-пример http://www.planetaexcel.ru/techn...  Подробнее »

 

Сохранили к себе

Текст из видео:
  • 00:01: здравствуйте меня зовут николай павлов и я тренер и эксперт по продуктам microsoft office и по excel в частности а также автор сайта планета x elle.ru в этом видео уроке я хотел бы наглядно показать и рассказать как использовать один из самых мощных и красивых инструментов microsoft excel сводные таблицы рассмотрим следующий пример предположим у вас есть вот подобного вида таблица в которой каждая строка
  • 00:30: представляет из себя сделку продажу или от кружку обратите внимание что строка полностью описывает весь процесс какой товар из какой категории когда в каком квартале каком месяце в каком городе и кому мы отгрузили так же в таблице содержится информация о том кто из наших менеджеров из менеджеров продаж отвечает за эту сделку и естественно на какую сумму было отгрузка вниз таблица
  • 01:03: весьма и весьма объемная поскольку содержит информацию по всем продажам за целый год если я нажму на клавиатуре kontrol стрелка вниз то хорошо будет видно что в таблице почти полторы тысячи строк в реальности естественно могло быть и заметно больше задача построить по этой таблице годовой итоговый отчет
  • 01:30: из которого было бы наглядно видно какие товары в каком городе на какую сумму и кому мы на продавали то есть вот такую вот таблицу обратите внимание что по строчкам у нашей таблице идут товары сгруппированные в категории причем категории можно сворачивать по столбцам идут города в которых мы
  • 02:01: торговали на пересечении строки и столбца итоговая выручка или сумму всех сделок из исходной таблице по этому товару в этом городе кроме промежуточных итогов и общего итога по всей таблицы здесь так же есть возможность выбирать заказчика то есть таблица на самом деле не 2а трехмерная это можно сделать в ячейке b1 при помощи раскрывающегося списка можно выбрать как одного заказчика
  • 02:31: так и нескольких если поставить вот этот флажок и разрешить мульти выбор вот это есть пример простой сводную таблицу давайте я по шагам медленно и подробно расскажу как ее можно создать и так все начинается с исходной таблице обратите внимание что
  • 03:01: таблица достаточно простая по конструкции имеет простую 1 строчную шапку без разрывов иди пустых строк и столбцов и без объединенных ячейку это необходимое условие для успешного построения сводной таблицы microsoft excel дополнительным бонусом будет возможность преобразовать эту таблицу в так называемую динамическую автора стягивающую сеяли умную таблицу для того чтобы потом не думать про ее размеры
  • 03:31: и не отслеживать изменение количества строк и столбцов в этой таблице в будущем это можно легко сделать если вы установите активную ячейку внутри списка и на главной вкладке выберите команду форматировать как таблицу вариант дизайна который вы выберете роли не играет можно выбрать любой и после нажатия на ok наша таблица станет динамической или умный то есть при записывании менее
  • 04:00: новых строк и столбцов они автоматически будут включаться в базу данных и попадать из итоговый отчет обратите внимание что на вкладке конструктор которая появилась после преобразования таблицы в умна у вас есть имя таблицы таблица 1 именно она будет использоваться для построения отчета сводной таблицы естественно ими можно поменять при желании можно использовать любое имя без пробелов и начинающиеся с буквы
  • 04:31: давайте назовем ее допустим банально правда теперь идем на вкладку вставка и создаем сводную таблицу обратить внимание на то что в английской версии это будет звучать как pivot тайбл нажимаю на кнопку сводная таблица появляется окно создания сводные
  • 05:00: таблицы обратить внимание на то что excel сам зацепился за наш именованный динамический диапазон продажи если бы это кстати не случилось можно было бы набрать его ему сюда вручную и спросил куда выгрузить итоговый отчет действительно лучше это сделать на новый отдельный лист нажимаем ok и попадаем на новый чистый лист на котором будет вот такая вот конструкция
  • 05:30: в левой половине будет строиться наш с вами сводная таблица а в правый мы сейчас видим список полей сводные таблицы надеюсь вы узнаете все заголовки из шапки вот для этого кстати нужна простая шапка и 4 области снизу которые представляют собой четыре основных элемента или четыре составляющих из которых в принципе может состоять сводная таблица эта область строк у меня в исходной таблице которые вам демонстрировал там
  • 06:00: были наименование категории область столбцов там если помните были города область значений здесь как раз вычислялось стоимость или прибыль и фильтр отчета позволял выбирать заказчик вся наша дальнейшая работа заключается в простом перетаскивание мышью полей из списка в эти четыре области сводная таблица будет при этом строится полностью автоматически поехали наименование бросаем в область
  • 06:31: строк обратите внимание что происходит на листья город бросали в бой столбцов сумму бросаю в область значений область значений кстати это единственная область где возможны и математические вычисления поэтому обычно сюда как раз и забрасывают какие-то денежные поля вроде суммы выручки прибыли веса
  • 07:00: количество товара и так далее но и фильтр отчеты можно забросить заказчика для полноты картины обратите внимание на то что у нас получилось по строчкам идут все наименования тогда я без повторов по алфавиту сортированы по столбцам уйдут все города которых мы торговали на пересечении идет суммарная выручка в этом городе по этому товару плюс ко всему дополнительно я могу фильтровать и товары
  • 07:30: и города и заказчиков при помощи простых фильтров чтобы быстро привести числовую часть в порядок в частности убрать копейки и привести числа привычному и приятному виду можно использовать простую функцию щелкаем правой кнопкой мышки в любое число и выберем команду не формат ячеек это более привычный путь но он требует выделение определенного диапазона будет завязан на этот диапазон а лучше выбрать
  • 08:01: команду числовой формат поля таким образом мы сразу одним движением другим формат для всего поля сводной таблицы для поле сумма и все числа в сводной таблице сразу примут приятный вид выберем команду числовой формат дальше будет привычное окно выбираем числовой формат копейки не хотим и разделить в группу разрядов позволит нам делить числа по три для наглядности при
  • 08:30: больших суммах это очень хорошо нажимаем на ok как видите вся сводная таблица сразу привилась приличный вид и для полноты картины на можно добавить какие-то дополнительные параметры в частности товары группируются по категориям фрукты-овощи ага доселе не так далее для наглядности отображения этих групп сводных таблиц а можно забросить категорию туда же где уже лежит наименование в
  • 09:00: область строк причем обратите внимание играет роль выше или ниже это будет закинуть например если забросу категорию ниже наименование то внешний вид будет далек от идеала но если поменять их местами поскольку категория более глобальное понятие на должна идти 1 выше чем наименование то внешний вид таблицы сразу меняются она становится на порядок лучше тоже самое можно проделать из со столбцами допустим
  • 09:30: город я могу свободно перенести в фильтр отчета а в столбцы заброшу квартал а под квартал месяц получу детализацию по месяцам с промежуточными итогами по квартала по каждому товару и по каждой категории красивый отчет наглядный понятный и сколько подобную вещь делать вручную честно говоря страшно
  • 10:01: подумать для полноты картины и для оформления внешнего вида можно использовать вкладку конструктор которая появится при создании сводной таблицы на ней есть большое количество готовых форматов для сводных таблиц например можно использовать любое приятно и форматирование и для наглядного отображения чисел можно использовать гистограммы для этого я выделю несколько ячеек с числами и на главные вкладки
  • 10:30: используя кнопку условное форматирование де 100 грамм и эта команда позволит применить к выделенным детей com заливку ширина которой зависит от числа чем больше число чем шире заливка своего рода мини диаграммы или микро графики прямо внутри ячеек чтобы применить подобное форматирование не только к выделенным ячейкам а сразу ко всей сводная таблица можно воспользоваться вот этим значком параметров форматирования он позволяет применить
  • 11:00: созданное правило не только к выделенным ко всем ячейкам исходной таблице кроме итогов это на порядок добавляет наглядности и сразу понятно где у нас числа большие а где маленькие это конечно не все что умеет сводные таблицы но для начала я думаю будет вполне достаточно когда знаешь все просто