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

19.04.2019

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

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

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

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

Основные требования

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

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

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

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

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

Инструкция по созданию

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

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

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

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

Также необходимо указать, где пользователь желает создать сводный отчёт:

на том же самом листе, где имеются основные показатели;
на новом листе.

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

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

В завершение остаётся сделать последнюю область сводного отчёта «Значения». В неё переносится поле, являющееся ключевым для анализа, например, общая масса, общее количество.

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

Использование сводных отчётов

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

Применение фильтра

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

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

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

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

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

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

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Результат:

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат )

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

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать - стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

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

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

При щелчке на неё появляется диалоговое окно:

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

В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:

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

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» - два вхождения аббревиатуры МТС)

В данном примере в столбце A есть несколько текстовых строк, и наша задача - выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

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

ДЛСТР (LEN) - вычисляет длину текста, единственный аргумент - текст. Пример: ДЛСТР (“машина”) = 6.
ПОДСТАВИТЬ (SUBSTITUTE) - заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст ). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
ПРОПИСН (UPPER) - заменяет все символы в строке на прописные. Единственный аргумент - текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» - тот, число вхождений которого нас интересует):

=(ДЛСТР(текст )-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст );ПРОПИСН(искомый );“”)))/ДЛСТР(искомый )

В нашем примере формула выглядит следующим образом:

=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)

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

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

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

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

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

Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

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

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

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

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

Обратите внимание, как расположились эти данные в нижней области панели настройки.

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

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

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

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

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

В параметрах полей значений вы найдете множество вариантов для анализа.

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

Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.

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

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

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

Также можно выбрать и значения для отдела.

Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.

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

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

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».

Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

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

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

Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит.

Обновите и вы получите новый перечень полей в области настройки.

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

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

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

Выберите все команды.

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

Значок появится сверху.

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

Нам нужно несколько полей, а не одно.

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист (щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

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

Щелкайте «Далее» и создавайте на новом листе.

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

Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.

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

Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

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

Вам откроются на выбор три типа для структуризации информации:

Сжатая форма

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

Структурированная форма

Все показатели подаются иерархично: от малого к большому.

Табличная форма

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

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

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

Отличного Вам дня!

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

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

Создание отчета с помощью мастера сводных таблиц

У нас есть тренировочная таблица с данными:

Каждая строка дает нам исчерпывающую информацию об одной сделке:

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

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

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

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

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

Готовый отчет можно форматировать, изменять.

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

Это можно сделать вручную и автоматически.

Настройка автоматического обновления при изменении данных:

На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

Некоторые секреты форматирования

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

Группировка по дате в сводной таблице Excel:

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

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

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

Работа с итогами

У нас есть сводный отчет такого вида:

Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.

Как в сводной таблице сделать итоги сверху:

Уже нет той перегруженности, которая затрудняла восприятие информации.

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Получим отчет без дополнительных сумм:

Детализация информации

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

Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

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

В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.

Жмем ОК – сводная таблица меняется.

Создание отчета с помощью мастера сводных таблиц

У нас есть тренировочная таблица с данными:

Каждая строка дает нам исчерпывающую информацию об одной сделке:

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

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

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

Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:

Готовый отчет можно форматировать, изменять.

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

Это можно сделать вручную и автоматически.

Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).

Настройка автоматического обновления при изменении данных:

На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

Некоторые секреты форматирования

Группировка по дате в сводной таблице Excel:

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

Работа с итогами

У нас есть сводный отчет такого вида:

Как в сводной таблице сделать итоги сверху:

«Работа со сводными таблицами» - «Конструктор».
На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
Получается следующий вид отчета:

Уже нет той перегруженности, которая затрудняла восприятие информации.

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Получим отчет без дополнительных сумм:

Детализация информации

В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:
На новом листе откроется таблица с данными о продажах товара.

Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.
Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.
Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.

Жмем ОК – сводная таблица меняется.

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

Сводная таблица в Excel 2010 используется для:

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

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

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

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

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

2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».

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

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

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

5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.

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

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

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

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается. Откройте вкладку «Файл» - «Параметры» - «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.

Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.

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

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

2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».

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

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

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

При выборе внешнего источника данных используется приложение Microsoft Query, входящее в комплект поставки Excel 2010 или, если требуется подключиться к данным Office, используются опции вкладки «Данные».
Если в документе уже присутствует отчет сводной таблицы или сводная диаграмма - в качестве источника можно использовать их. Для этого достаточно указать их расположение и выбрать нужный диапазон данных, после чего будет создана новая сводная таблица.

Вам понравился материал?
Поделитeсь:

Поставьте оценку:

из 5, оценок:

Вернуться в начало статьи Как создать сводную таблицу в excel 2010

А сейчас приступим к выполнению практикума, благодаря которому вы получите первый опыт обработки данных в PowerPivot. В рассматриваемом примере будет создан отчет, основанный на 1,8 млн строк CSV-файла, содержащего данные о складах. На самом деле количество записей может быть неограниченным (в отличие от Excel, где допускается до 1 048 000 записей).

Исходная таблица включает 1,8 млн записей, находящихся в файле CSV под названием demo.csv. Часть содержимого этого файла, отображенного в окне программы Блокнот, показано на рис. 10.2. Обратите внимание на то, что заголовки столбцов находятся в первой строке CSV-файла. Учтите, что может потребоваться удаление нестандартных строк, находящихся в верхней части демофайла, во избежание проблем при их обработке с помощью надстройки PowerPivot.

Рис. 10.2. Этот файл, содержащий 1.8 млн строк, слишком велик для Excel

Импорт текстового файла

Для импорта файла, содержащего 1,8 млн строк, в PowerPivot выполните следующие действия.

В окне Excel 2010 выберите вкладку PowerPivot.
Щелкните на значке Окно PowerPivot (PowerPivot Window). На экране появится окно приложения PowerPivot. включающее две вкладки: Главная (Ноmе) и Проект (Design). Вкладка Главная показана на рис. 10.3.
Сначала нужно импортировать основную таблицу. Эта таблица создается на основе большого CSV-файла, показанного на рис. 10.2. В группе команд Получение внешних данных (Get External Data) щелкните на кнопке Из текста (From Text). На экране появится диалоговое окно Мастер импорта таблиц (Table Import Wizard).
Введите в поле Понятное имя соединения (Friendly Connection Name) имя для вашего соединения, например Sales History. Щелкните на кнопке Обзор (Browse) и найдите текстовый файл. Надстройка PowerPivot по умолчанию не использует в качестве заголовков столбцов содержимое первой строки, поэтому в процессе предварительного просмотра имена столбцов отображаются в следующем не слишком понятном формате: Fl, F2, F3 и т.д. (рис. 10.4).
Проверьте, чтобы в качестве разделителя была выбрана именно точка с запятой. В раскрывающемся списке Разделитель столбцов отображается ряд стандартных разделителей, таких как запятая, точка с запятой, вертикальная черта и т.д.
Установите флажок Использовать первую строку для заголовков столбцов (Use First Row as Column Headers). В окне предварительного просмотра отобразятся реальные заголовки столбцов.
Если хотите отказаться от импорта какого-либо столбца, отмените установку соответствующих флажков. Текстовый файл готов к загрузке в оперативную память. Обратите внимание на то, что можно существенно уменьшить объем используемой оперативной памяти, если исключить лишние столбцы, особенно если они включают длинные текстовые значения. На рис. 10.5 показано, как выглядят данные в результате отмены установки флажка.
После щелчка на кнопке Готово (Finish) PowerPivot начинает загружать файл в память. При этом отображается количество строк, загруженное в настоящий момент времени (рис. 10.6).
После завершения импорта файла отображается количество загруженных строк - 1,8 млн записей (рис. 10.7). Щелкните на кнопке Закрыть (Close) для возврата в окно PowerPivot.
В окне PowerPivot отображается 1,8 млн записей. Для их просмотра можно воспользоваться вертикальной полосой прокрутки. Можно также выполнить сортировку, изменить числовой формат либо применить фильтр (рис. 10.8).

Привет всем! Сегодняшний материал для тех, кто продолжает осваивать работу с программами-приложениями, и не знает, как сделать сводную таблицу в excel.

Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

Создание сводной Эксель таблицы требует соблюдения определенных условий:

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

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

Для создания сводной таблицы необходимо:

Создался пустой лист, где видно списки областей и полей. Заголовки стали полями в нашей новой таблице. Сводная таблица будет формироваться путем перетаскивания полей.

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

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

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

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

Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.

Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.

Указываем периоды даты и шаг. Подтверждаем выбор.

Видим такую таблицу.

Сделаем перенос поля «Сумма» к области «Значения».

Стало видно отображение чисел, а нам необходим именно числовой формат

Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».

Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».

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

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

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

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

Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.

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

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

На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.

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

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

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

В программе Excel 2007 (MS Excel 2010|2013) сводная таблица используется, в первую очередь, для составления математического или экономического анализа данных.

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

Анализ данных документа способствует более быстрому и правильному решению поставленных задач.

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

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

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

Выбор окна вставки для дальнейшего создания

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

Совет! Дополнительные макеты сводных таблиц можно скачать с официального сайта компании «Майкрософт».

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

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

Пример отфильтрованной таблицы

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

В пустой шаблон необходимо добавить поля, формулы для расчета, фильтры.

Пустая форма заполняется путем перетаскивания на отдельные области необходимых элементов данных.

Форма создания персональной пользовательской таблицы-сводки

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

Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.

Проводить анализ внешних данных тоже можно с помощью сводных таблиц.

Макросы в Excel - Инструкция по использованию
Выпадающий список в Excel - Инструкция по созданию
Как закрепить строку в Excel - Подробные инструкции.