Примеры документов с таблицами excel

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

Что такое сводные таблицы в Excel? Cводные таблицы в Excel для чайников

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:

В таблице указаны:

Даты заказов;
Регион в котором расположен клиент;
Тип клиента;
Клиент;
Количество продаж;
Выручка;
Прибыль.

Теперь, представим, что наш руководитель поставил задачи вычислить:

ТОП пять клиентов по выручке;

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

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

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

Как сделать сводную таблицу в Excel

Для того чтобы создать сводную таблицу в Excel выполните следующие действия:

Выделите любую ячейку в таблице с данными, на основе которых вы хотите сделать сводную таблицу;
Нажмите на вкладку “Вставка” => “Сводная таблица”:

Во всплывающем диалоговом окне система автоматически определит границы данных, на основе которых вы сможете создать сводную таблицу. Этот способ в большинстве случаев работает по умолчанию. Рекомендую при каждом создании сводной таблицы убедиться в том, что система правильно определила параметры ее создания:
– Таблица или диапазон: Система автоматически определяет границы данных, для создания сводной таблицы. Они будут корректными при том условии, что в таблице нет пробелов в заголовках и строках. При необходимости вы можете скорректировать диапазон данных.
Система по-умолчанию создает таблицу в новой вкладке файла Excel. Если вы хотите создать её в конкретном месте на определенном листе, то вы можете указать границы для создания в графе “На существующий лист”.

Нажмите “ОК”.

После того, как вы нажали кнопку “ОК” сводная таблица будет создана.

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

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

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

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

Ниже вы узнаете подробней об областях:

Кэш сводной таблицы
Область “Значения”
Область “Строки”
Область “Столбцы”
Область “Фильтры”

Что такое кэш сводной таблицы

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

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

Важно помнить о том, что кэш данных увеличивает размер Excel-файла.

Область “Значения”

Область “Значения” содержит в себе все значения таблицы и может быть выведена в качестве основной составляющей сводной таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи) с помощью сводной таблицы. Значения закрашенные желтым цветом на изображении ниже и являются значениями, которые мы указываем в сводной таблице в области “Значения”.

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

Область “Строки”

Заголовки таблицы, размещенные слева от значений, представляют собой строки. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.

Область “Фильтры”

Область “Фильтры” используется опционально и позволяет задать уровень детализации данных сводной таблицы. Например, мы можем в качестве фильтра указать данные “Тип клиента” – “Продуктовый магазин” и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.

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

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса из начала этой статьи:

Какой объем выручки у региона Север за 2017 год?;
ТОП пять клиентов по выручке;
Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле “Значения”. Как только вы представили каким образом вы видите итоговую сводную таблицу – начинайте её создание.

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения в сводной таблице:

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

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

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

Пример 1. Какой объем выручки у региона Север?

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

создать сводную таблицу и поле “Регион” перенести в область “Строки”;
поле “Выручка” разместить в области “Значения”

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

в сводной таблице переместить поле “Клиент” в область “Строки”;
поле “Выручка” разместить в области “Значения”;
задать финансовый числовой формат ячейкам сводной таблицы со значениями.

У нас получится следующая сводная таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для того чтобы отсортировать данные по объему продаж выполните следующие действия:

перейдите в меню “Сортировка” => “Сортировка по убыванию”:

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

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

Для расчета места по объему выручки клиента Лудников ИП в регионе Восток предлагаю сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.

Для этого:

поместим поле сводной таблицы “Регион” в область “Строки”;
поместим поле “Клиент” в область “Строки” под поле “Регион”;
зададим финансовый числовой формат ячейкам со значениями.

Как только вы разместите поля “Регион” и “Клиент” друг под другом в области “Строки”, система поймет каким образом вы хотите отобразить данные и предложит подходящий вариант.

поле “Выручка” разместим в область “Значения”.

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

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

кликните правой кнопкой на любой из строчек с данными выручки на сводной таблице;
перейдите в меню “Сортировка” => “Сортировка по убыванию”:

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

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле “Регион” в область “Фильтры” и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

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

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

Подробная информация о базовых функциях и возможностях табличного процессора MS Excel. Описание основных элементов документа и инструкция для работы с ними в нашем материале.

Работа с ячейками. Заполнение и форматирование

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

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

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

Рис. 1 – пример заполнения ячеек

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

Выберем ячейку «Доля акций». Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое. Формул у этой клетки нет, поэтому они не показываются.

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

Рис. 2 – контекстное меню ячейки и ее основные свойства

Сортировка данных

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

Перед вами уже заполненная таблица (как её создать разберемся дальше в статье). Представьте, что вам нужно отсортировать данные за январь по возрастанию. Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.

Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:

Выделить таблицу или блок информации;
Открыть кладку «Данные»;
Кликнуть на иконку «Сортировка»;

Рис. 3 – вкладка «Данные»

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

Рис. 4 – настройка параметров сортировки

Произойдет автоматическая сортировка данных:

Рис. 5 – результат сортировки цифр в столбце «Январь»

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

Математические расчеты

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

Для этого, вам необходимо кликнуть на третью ячейку, в которую будет вписан конечный результат вычислений. Затем нажмите на значок функции f(x), как показано на рисунке ниже. В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее. Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.

Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ».

Рис. 6 – выбор функции «СУММ»

В окне аргументов функции есть два поля: «Число 1» и «Число 2». Выберите первое поле и кликните мышкой на ячейку с цифрой «2». Её адрес запишется в строку аргумента. Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно. Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3», «Число 4» и так далее.

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

Рис. 7 – результат выполнения подсчетов

Создание таблиц

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

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

Чтобы создать таблицу, следуйте инструкции:

Откройте вкладку «Вставка». В левой части панели опций выберите пункт «Таблица». Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
Нажмите ОК, чтобы подтвердить действие.

Рис. 8 – создание стандартной таблицы

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

Рис. 9 – форматирование таблицы

Результат заполнения таблицы данными:

Рис. 10 – заполненная таблица

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

Добавление графиков/диаграмм

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

Чтобы создать диаграмму/график, нужно:

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

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

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

Тематические видеоролики:

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

Подготовить данные для отчетов;
Рассчитать различные показатели;
Сгруппировать данные;
Отфильтровать и проанализировать интересующие показатели.

А также сэкономить вам кучу времени.

Из данной статьи вы узнаете:

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

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

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

Товар
Продажи в руб.

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

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

Появится диалоговое окно, в котором:

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

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

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

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

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

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

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

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

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

Вставленные столбцы называем "Год", "Месяц", "Год-Месяц".

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

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

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

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

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

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

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

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

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

продажи по каждому товару в сумме за целый год (строка с названием товара);
более подробно продажи по каждому товару в каждом месяце в динамике за 4 года.

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

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

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

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

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

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

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

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

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