Сводная таблица Excel: как сделать и работать? Работа со сводными таблицами Excel

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

сводная таблица excel

К счастью, сравнимо не так давно возник MS Office 2010-2013, который не только лишь включает в себя ряд освеженных программ для обработки текстовых файлов, таблиц, баз данных и презентаций, да и дозволяет работать с ними сразу нескольким работникам, что в корпоративной среде просто бесценно.

Почему конкретно новейшие версии?

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

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

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

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

Что же все-таки это такое и для чего же она нужна?

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

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

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

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

Интересно почитать: Excel условное форматирование по формуле

Создаем подходящий документ

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

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

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

Форматирование таблицы

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

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

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

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

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

Создание и группировка временных рядов

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

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

Применяемые формулы

Для примера вновь сделанные столбцы можно именовать «Год», «Месяц», «Месяцы-Годы». Чтоб получить интересующие нас данные, в любой из их придется прописать отдельную формулу для расчетов:

В «Годичный» вставляем формулу вида: «=ГОД» (ссылаясь при всем этом на дату).
Месяц необходимо дополнить выражением: «=МЕСЯЦ» (также со ссылкой на дату).
В 3-ий столбик вставляем формулу вида: «=СЦЕПИТЬ» (ссылаясь при всем этом на год и на месяц).

Интересно почитать: Формула впр в excel не работает формула

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

Как проводить анализ продаж по годам

Опять зажимаем левой кнопкой мыши пункт «Год», опосля чего же тащим его в «Заглавие столбцов», опосля чего же сводная таблица начинает показывать результаты реализации определенного вида продукта по всем годам. А если нужно так же проанализировать месячный сбыт? Буквально так же зажимаем ЛКМ «Месяц», перетаскивая его под годичный столбец.

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

Сумма продаж определенного продукта за весь год.
Динамику продаж всякого из их по годам.

Убираем данные из выдачи

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

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

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

Рассчитываем прогнозы

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

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

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

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

Интересно почитать: Формула в excel если ячейка пустая то равна 0

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

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

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

Функция слияния в поздних версиях «Кабинета» фактически не поменялась в сопоставлении с Office 2003. Тем не наименее запросы к базам данных для выбора источника данных могут производиться с применением Microsoft Query. Чтоб получить данные сводной таблицы Excel, нужно наличие 2-ух объектов:

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

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

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

Применяемые группы

Также тут можно употреблять категорию ASK, что означает «запросить», и FILLIN, что означает «заполнить», NEXT и NEXTIF, ELSE. Также можно произвести настройку характеристик тех пт прогноза, которые будут отображены в готовой таблице. Отыскать нужную запись можно с помощью специального фильтра либо же методом обычной сортировки данных, о чем мы уже гласили выше.

Когда все готово для сотворения составного документа, окно диалога Merge предоставляет юзеру возможность выявить и убрать отдельные ошибки до их возникновения. Когда все испытано, то можно смело нажимать на клавишу «Соединить». Если же слияние обязано выполняться через электрическую почту, то выйдет запрос на установку почтовой программки (если у вас она не установлена).

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

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