Что такое сводные таблицы Excel? Вероятно, непосвященный человек предположит самое очевидное - это таблицы, в которые собраны (сведены) данные из нескольких других разрозненных источников, отвечающие некоторому запросу. На самом деле это заключение не совсем верно.
Сводные таблицы Excel – это инструмент, который собирает по заданному алгоритму в удобные таблицы-отчеты информацию чаще всего из одной таблицы базы данных ! (Возможность использования в качестве источника нескольких диапазонов консолидации существует, но в этой статье рассматриваться не будет.)
При помощи сводных таблиц из хаотичного набора строк-записей базы данных можно за считанные секунды извлечь нужные, отсечь в них лишнюю информацию, добавить недостающую и представить пользователю в красивом и удобном для восприятия виде.
Если вы постоянно работаете в программе MS Excel с большими объемами информации и ничего не знаете о сводных таблицах, то считайте, что вы «заколачиваете гвозди» калькулятором, не зная его истинного предназначения!
Когда следует применять сводные таблицы?
Во–первых, тогда, когда работаешь с большим объемом статистических данных, анализировать которые очень трудоемко при помощи сортировки и фильтров.
Во-вторых, тогда, когда приходится через определенные промежутки времени повторять (пересчитывать) отчеты в связи с изменениями источника – таблицы базы данных.
В-третьих, при анализе изменений данных базы в разрезе различных временных периодов или иных критериев.
Это основные ситуации, при которых раскрывается вся действительно волшебная сила инструмента сводные таблицы Excel.
Создание сводных таблиц и работа с ними никак не влияет на содержание источника – базу данных!!! Сводные таблицы Excel позволяют взглянуть на эти данные под разными ракурсами, не изменяя ничего в базе!!!
Это заключительная и самая важная статья обзорного цикла о хранении информации и управлении большими табличными объемами данных.
В предыдущих статьях цикла мы создали , научились , применять и , вводить .
Создание шаблона сводной таблицы.
Сводную таблицу будем создавать в той же книге Excel , где расположен источник информации — таблица базы данных, но на другом листе.
Продолжаем работу с учебной мини-базой БД2 «Выпуск металлоконструкций участком №2», с которой работали во всех статьях этого цикла.
1. Открываем в MS Excel файл .
2. Активируем («щелкаем мышкой») любую ячейку внутри таблицы базы.
3. Выполняем команду главного меню программы «Данные» - «Сводная таблица…». Эта команда запускает работу мини-сервиса «Мастер сводных таблиц».
4. Не долго размышляя над вариантами выбора положений переключателей в выпадающих окнах «Мастера…», настраиваем их (точнее – не трогаем их) так, как показано ниже на снимках экрана, двигаясь между окнами с помощью кнопок «Далее».
На втором шаге «Мастер…» сам выберет диапазон, если вы правильно подготовили базу данных и выполнили п.2 этого раздела статьи.
5.
На третьем шаге «Мастера…» нажимаем кнопку «Готово». Шаблон сводной таблицы сформирован и размещен на новом листе того же файла, где расположена база данных!
Кроме пока пустого шаблона на вновь созданном листе файла Excel вы видите панель «Сводные таблицы» со своим инструментарием и окно «Список полей сводной таблицы», появляющееся при активации любой ячейки внутри сводной таблицы. Появляются они автоматически, я их просто расположил рядом с шаблоном и подкорректировал размеры. Если панели «Сводные таблицы» вы не видите, то вызовите ее через главное меню командой «Вид» — «Панели инструментов» — «Сводная таблица»
В MS Excel 2007 и более новых версиях «Мастер…» упразднен потому, что 99% процентов пользователей никогда не меняют предложенных настроек переключателей и проходят эти три шага, просто соглашаясь с предложенными вариантами (мы тоже так поступили).
В MS Excel 2007 по команде «Сводная таблица» выпадает диалоговое окно «Создание сводной таблицы», в котором достаточно указать источник данных для анализа и место расположения создаваемой сводной таблицы.
Создание рабочих сводных таблиц Excel.
В созданном шаблоне имеются четыре зоны, в которые для создания сводной таблицы следует поместить названия полей-столбцов таблицы базы данных, перетащив их при помощи левой кнопки мыши из окна «Список полей сводной таблицы». Напоминаю, что в базе данных полем называется столбец с названием.
Внимание!!!
Элементами окна «Список полей сводной таблицы» являются заголовки полей базы данных!
1. Если поместить один из элементов «Списка полей сводной таблицы» в самую верхнюю зону шаблона, где он станет «Полем страниц» , то мы получим удобный фильтр записей этого поля .
2. Если перетащить один элемент (или два, реже – три и более) «Списка полей сводной таблицы» в левую зону шаблона, где он станет «Полем строк» заголовки строк сводной таблицы.
3. Если перетащить один элемент (или два, реже – три и более) «Списка …» в верхнюю зону шаблона, где он станет «Полем столбцов» , то мы получим из записей этого поля таблицы базы данных заголовки столбцов сводной таблицы.
4. Если перетащить один элемент (или два, реже – три и более) «Списка …» в центральную область шаблона, где он станет «Элементом данных» , то мы получим из записей этого поля базы данных значения сводной таблицы.
Значения – элементы данных – расположатся в строгом соответствии с правилами двухмерных таблиц, то есть на пересечении соответствующих заголовков строк и заголовков столбцов!
Не очень понятно? Перейдем к практическим примерам — все станет ясно!
Задача №9:
Сколько всего тонн металлоконструкций изготовлено по каждому заказу?
Для ответа на этот вопрос выполним всего два действия! Схема этих действий показана на предыдущем рисунке синими стрелками.
1. Перетащим мышью элемент «Заказ» из окна «Список полей сводной таблицы» в зону «Поля строк» пустого шаблона.
2.
Перетащим мышью элемент «Общая масса, т» из окна «Список полей сводной таблицы» в зону «Элементы данных» шаблона. Результат – на снимке экрана слева. Думаю, пояснения не требуются.
Заголовок «Элементов данных» «Сумма по полю Общая масса, т» расположился выше заголовка «Поля строк» и левее места, где может расположиться заголовок «Поля столбцов». Обратите на это внимание!
Задача №10:
Сколько тонн металлоконструкций изготовлено по каждому заказу по датам?
Продолжаем работу.
3. Для ответа на вопрос задачи №10 достаточно добавить в нашу сводную таблицу элемент «Дата» из окна «Список полей сводной таблицы» в зону «Поля столбцов» шаблона.
4.
Записи можно сгруппировать, например, по месяцам. Для этого на панели «Сводные таблицы» нажимаем вкладку «Сводная таблица» и выбираем «Группа и структура» — «Группировать…». В окне «Группирование» делаем настройки в соответствии со скриншотом слева.
Так как все записи нашей базы данных сделаны в апреле, то после группировки мы видим всего два столбца – «апр» и «Общий итог». Когда в исходной базе данных появятся записи, датированные маем и июнем, в сводной таблице добавятся соответствующие два столбца.
Задача №11:
Когда и сколько тонн и штук марок изготовлено всего по заказу №3?
5. Для начала перетащим поле «Заказ» из окна «Список полей сводной таблицы» или прямо из самой сводной таблицы в самый верх листа в зону «Поля страниц» шаблона.
7. Поле «Количество, шт» добавим в область «Элементы данных».
Необходимая для ответа на вопрос задачи конфигурация сводной таблицы Excel сформирована.
8. Нажмем на кнопку со стрелкой вниз в ячейке B1 и в выпавшем списке вместо записи «(Все)» выберем «3», соответствующую интересующему нас заказу №3 и закроем список, нажав кнопку «OK».
Ответ на вопрос задачи на снимке экрана ниже этого текста.
Форматирование сводной таблицы.
Если сводную таблицу нужно распечатать и показать кому-нибудь, или просто немного порадовать самого себя, то ее нужно отформатировать – придать достойный внешний вид и переформулировать некоторые «неадекватные» заголовки.
Заголовки «Сумма по полю Общая масса, т» и «Сумма по полю Кол-во, шт» звучат, вроде, и понятно, но как-то не по-русски. Переименуем их в более благозвучные «Общая масса изделий, т» и «Количество изделий, шт».
1. Продолжим работу с созданной таблицей. Активируем мышью ячейку B5.
2. На панели инструментов «Сводная таблица» нажимаем кнопку «Параметры поля» (выделена справа на снимке, расположенном ниже).
3. В выпавшем окне «Вычисление поля сводной таблицы» меняем имя и жмем кнопку «OK».
4.
По аналогичному алгоритму переименовываем и второй заголовок поля, предварительно «встав» мышью на ячейку B6.
5. На панели «Сводная таблица» нажимаем кнопку «Формат отчета» (выделена слева на снимке, расположенном выше — над п.3).
6. В появившемся окне «Автоформат» выбираем из предложенных вариантов форматирования, например, «Отчет 6» и нажимаем «OK» .
Внешний вид нашей сводной таблицы существенно преобразился.
Заключение.
Обращаю ваше внимание на несколько очень важных моментов!
При изменении источника (например, добавление очередной записи в базу данных) в самой сводной таблице изменения автоматически не наступят!!!
Для того чтобы изменения в базе отражались в сводной таблице, необходимо каждый раз «вручную» нажимать кнопку «Обновить данные» (значок кнопки – « ! ») на панели «Сводные таблицы»!!!
Помещайте в область «Полей страниц» поля, содержащие текстовую информацию , которая может стать заголовком для формируемой сводной таблицы .
В области "Полей строк" и «Полей столбцов» размещайте поля, содержащие текстовую информацию , которая может стать заголовками для строк и столбцов создаваемой сводной таблицы.
В зоне «Элементов данных» располагайте преимущественно числовую информацию.
Действуйте, располагая поля так, как вы действовали бы, делая это на листе бумаге! Не бойтесь ошибиться. Все легко исправляется.
Созданные двумя-тремя движениями мыши в одном из предыдущих разделов этой статьи сводные таблицы очень быстро дали ответ на весьма непростые вопросы! Если заказы изготавливаются в течение нескольких месяцев, а число наименований марок превышает несколько тысяч, то, сколько вам потребуется времени для решения рассмотренных выше задач? Час? День? Сводные таблицы Excel делают это мгновенно, многократно и без ошибок!!!
О сводных таблицах Excel написано много хороших «толстых» и «тонких» книг. В первую очередь это книги Билла Джелена («Мистера Excel») и Майкла Александера. Тем, кто заинтересовался этой темой, рекомендую их прочитать.
Конечно, подробно рассказать об этом чудесном инструменте в короткой статье очень сложно, практически невозможно. С одной стороны мне хотелось затронуть больше практически важных аспектов, но с другой – в ознакомительной статье не хотелось «залезать в дебри», отпугивая пользователей объемом и сложностью информации. Если этот «волшебный инструмент» MS Excel вызовет интерес у читателей блога, то в будущем эта тема будет продолжена выпуском небольшой брошюры с важными и ценными практическими рекомендациями.
Обязательно возьмите себе на вооружение сводные таблицы Excel! Ваша ценность как специалиста увеличится многократно!
На этом цикл из шести статей, представляющих собой общий обзор темы обработки больших объемов информации в Excel завершен.
Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!
Уважаемые читатели, вопросы, отзывы, и замечания оставляйте в комментариях внизу страницы.
Моя знакомая, экономист на предприятии, владеет Экселем, иногда посматривает на форумы, но 5 лет не могла подступиться к самому, на мой взгляд, полезному инструменту Excel – сводным таблицам. Что же это такое? Сводные таблицы – это результат выборки из правильно построенных данных. Преимуществ у инструмента много, я описывать не буду, просто скажу, что без нее многим приходилось бы осваивать не только Excel, но и Microsoft Access.
Мое мнение – этот инструмент надо изучать самим и в этой статье я расскажу нюансы, об которые спотыкаются новички, которые сами хотят освоить этот инструмент.
СТ требует правильно построенных данных. Идеальный вид – одной операции в строчку соответствуют разные поля свойств записи.
Я сваял простую таблицу, которая возникает на любом хозяйственном складе. Склад отгружает 4-м магазинам в течение года стройматериалы и каждая операция отгрузки товара имеет свою запись.
Как видите, здесь нет никакой группировки или суммирования данных, я называю это простыней, т.к., хотя столбцов может быть много, но строк тут гораздо больше. Здесь нет разрывов, что тоже важно.
Excel предложит диапазон, поэтому просто кликайте Ок.
Здесь 4 варианта: строки, столбцы, значения и фильтр отчета.
Остановимся поподробнее. Строки и столбцы в общем-то одинаковые по сути категории. Вы можете сюда поставить те поля, которые являются признаковыми – например, наименование, месяц, категорию. Можно поставить два в строки и ни одного в столбец, в этом случае ваша таблица будет иметь группировку в строках.
Значения – сюда ставим только цифровые значения.
Фильтр отчета . Как вы поняли, вы сами строите каркас и какие-то поля вы можете проигнорировать, какие-то поставить горизонтально, какие-то вертикально. Свои фильтры будут и у строк и у столбцов, но фильтр отчета позволяет более удобно отбирать те данные, которые вам нужно показать в этой таблице.
Теперь на примере моих данных.
Какой вид может принять таблица:
Как видите, вариаций много.
1) Хотя сводная таблица вытаскивает очень наглядно все срезы имеющейся информации, в ней работать не всегда просто, она не поддается редактированию, только read-only . Если у вас много разрезов информации, то проще сделать таблицу и заполнить ее, используя функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и правила работы с относительными ссылками.
Формат функции прост («Значения»; «Сводная таблица»; «Поле1»; «Элемент1»; «Поле2»; «Элемент2» и т.д.)
Т.е., можно вручную заменить значения Элементов на относительные ссылки вашей таблицы.
2) Чтобы постоянно обновлять данные и не трогать макет, добавляйте данные внизу простыни. Тогда у вас ничего не поползет, а обновление произойдет одной кнопкой обновить по самой СТ. Плюс в источнике данных для СТ укажите или все столбцы простыни (ленивый вариант) или имя диапазона, размеры которого будете менять произвольно, не заходя в настройки СТ.
«Глаза боятся, а руки делают»
Многие офисные пользователи зачастую сталкиваются с целым рядом проблем при попытке создания и редактирования каких-либо офисных документов. Зачастую связано это с тем, что в компаниях используется несколько офисных программ, принципы работы в которых могут сильно различаться. Особенно много затруднений может создать сводная таблица Excel.
К счастью, сравнительно недавно появился MS Office 2010-2013, который не только включает в себя ряд обновленных программ для обработки текстовых файлов, таблиц, баз данных и презентаций, но и позволяет работать с ними одновременно нескольким работникам, что в корпоративной среде просто бесценно.
Почему именно новые версии?
Чтобы разобраться в этом вопросе, нужно представлять себе все наиболее значимые изменения, которые произошли с этим офисным пакетом.
Как и раньше, именно Excel является второй по востребованности программой, которая позволяет не только создавать простые таблицы, но даже создавать довольно сложные базы данных. Как и в прочие компоненты, в него была добавлена кнопка «Офис», нажав на которую вы можете сохранить документ в требуемом вам формате, изменить его или указать требуемые меры по его защите. Был значительно обновлен модуль защиты документа.
Что же касается наиболее значимых изменений в Excel, то следует отметить исправление множества ошибок в формулах, из-за чего в прошлых версиях нередко возникали довольно грубые ошибки при расчетах.
Нужно сказать, что новая версия пакета — это не просто набор офисных программ, но и мощнейший инструмент, подходящий для решения даже очень сложных задач. Как вы могли понять, сегодня нами будет рассмотрена сводная таблица Excel.
Что это такое и для чего она нужна?
Наиболее значимым изменением в новых версиях «Офиса» является их полностью перекроенный интерфейс, который был назван создателями Ribbon (лента). В лентах все 1500 команд удобно сгруппированы по категориям, а потому вам не придется их долго искать. Чтобы полностью соответствовать этим параметрам, разработчики добавили в Excel еще и улучшенные сводные таблицы.
Сегодня рассмотрим Excel «для чайников». Сводные таблицы - это специальный инструмент, в котором наглядно группируются результаты какого-нибудь процесса. Проще говоря, с их помощью можно увидеть, сколько тех или иных товаров продал каждый продавец за свою рабочую смену. Кроме того, их используют в тех случаях, когда необходимо:
- Подготовить аналитические выкладки для написания итоговых отчетов.
- Высчитать каждый их показатель в отдельности.
- Произвести группировку данных по их типам.
- Произвести фильтрацию и углубленный анализ полученных данных.
В этой статье мы рассмотрим простые способы их создания. Вы узнаете, как сводная таблица Excel помогает создавать и анализировать временные ряды, создать и разобрать подробнее прогноз.
Создаем нужный документ
Так как создать сводную таблицу в Excel? Сперва нужно составить простой документ, в который мы вносим данные для их последующего анализа. На один столбец должен приходиться один параметр. Возьмем простейший пример:
- Время продажи.
- Проданный товар.
- Стоимость всего сбыта.
Таким образом, между всеми параметрами в каждом конкретном столбце образуется связь: предположим, что кроссовки были проданы в 9 часов утра, причем прибыль составила n-рублей.
Форматирование таблицы
Приготовив все исходные сведения, ставите курсор в первую ячейку первого же столбца, открываете вкладку «Вставка», после чего кликаете по кнопке «Сводная таблица». Сразу появится в котором вы можете проделать следующие операции:
- Если вы сразу же нажмете на кнопку «ОК», то сводная таблица Excel сразу же будет выведена на отдельный лист.
- Полностью настроить выведение.
В последнем случае разработчики дают нам возможность определить диапазон ячеек, в который будет выводиться искомая информация. После этого пользователь должен определить, где конкретно будет создана новая таблица: на уже существующем или же на вновь созданном листе. Нажав «ОК», вы тут же увидите перед собой готовую таблицу. На этом создание сводных таблиц в Excel практически закончено.
В правой части листа расположены те области, с которыми вам предстоит работать. Поля можно перетаскивать в отдельные области, после чего данные из них будут отображены в таблице. Соответственно, в левой части рабочей области будет расположена сама сводная таблица.
Зажав при нажатой левой кнопке мыши поле «Товар», отправляем его в «Название строк», а пункт «Сумма всех продаж» таким же образом переправляем в «Значения» (в левой части листа). Вот так можно получить сумму продаж за весь анализируемый период.
Создание и группировка временных рядов
Чтобы проанализировать продажи по конкретным временным отрезкам, необходимо вставить соответствующие пункты в саму таблицу. Для этого необходимо перейти в лист «Данные» и после этого вставить три новых столбика сразу после даты. Выделяем столбец с после чего жмем на кнопку «Вставить».
Очень важно, дабы все вновь создаваемые столбцы находились внутри уже существующей таблицы с исходными данными. В этом случае создание сводных таблиц в Excel заново не потребуется. Вы просто будете добавлять новые поля с требуемыми вам данными.
Используемые формулы
Для примера вновь созданные столбцы можно назвать «Год», «Месяц», «Месяцы-Годы». Чтобы получить интересующие нас данные, в каждый из них придется прописать отдельную формулу для расчетов:
- В «Годовой» вставляем формулу вида: «=ГОД» (ссылаясь при этом на дату).
- Месяц нужно дополнить выражением: «=МЕСЯЦ» (также со ссылкой на дату).
- В третий столбик вставляем формулу вида: «=СЦЕПИТЬ» (ссылаясь при этом на год и на месяц).
Соответственно, получаем три столбика со всеми исходными данными. Сейчас необходимо перейти в меню «Сводная», щелкаем по любому свободному месту в таблице правой клавишей мыши, после чего в открывшемся выбираем пункт «Обновить». Обратите внимание! Внимательно пишите формулы в сводных таблицах Excel, так как любая ошибка приведет к неточности прогноза.
Как проводить по годам?
Снова зажимаем левой клавишей мыши пункт «Год», после чего тащим его в «Название столбцов», после чего сводная таблица начинает отображать результаты продажи конкретного вида товара по всем годам. А если необходимо так же проанализировать месячный сбыт? Точно так же зажимаем ЛКМ «Месяц», перетаскивая его под годовой столбец.
Чтобы получить наглядную динамику месячных изменений продаж по годам, поле «Месяц» нужно перетащить прямо в годовой столбец, ранее уже перемещенный в «Название столбцов». Выполнив эту операцию, вы увидите таблицу в следующем представлении:
- Сумма продаж конкретного товара за весь год.
- Динамику продаж каждого из них по годам.
Убираем данные из выдачи
Предположим, что из выдачи нам нужно убрать данные по октябрю, так как у нас нет полной статистики по нему. В сводной таблице ищем «Фильтр отчета», после чего перетаскиваем на него «Год - месяц».
Над таблицей появляется фильтр, в котором нужно поставить флажок напротив пункта «Выделить несколько элементов». После этого в появившемся списке снимаем флажок с октября, после чего нажимаем на «ОК».
Таким образом, можно добавлять и убирать из фильтра элементы, формируя действительно актуальные и необходимые вам анализы. Программа автоматически просчитает показатели по тем месяцам, которые вас интересуют, наглядно отображая динамику изменения показателей.
Рассчитываем прогнозы
Попробуем спрогнозировать результаты продаж на некоторое время вперед. Для этого предварительно следует отключить общие итоги, чтобы они не внесли неточности в создаваемый прогноз.
Чтобы сделать это, ставим курсор в поле «Общий итог», после чего кликаем по кнопке «Удалить общий итог». После этого выполняем сводной таблицы Excel способом, описанным нами выше (жмем на правую клавишу мыши, выбираем «Обновить»).
В результате эти данные пропадают из нашей сводной таблицы. Чтобы построить наглядное отображение прогнозов по продажам, ставим курсор на тот год, от которого планируем отталкиваться, после чего кликаем левой клавишей мыши по кнопке «График Модель прогноза».
После этого появляется красивый и информативный график, на котором в наглядной форме представлены прогнозируемые результаты покупательской активности по интересующему вас прогнозу. Это позволит сэкономить ваши силы и сохранить деньги, которые можно направить на закупку действительно необходимых товаров.
Используем прочие источники данных для формирования сводной таблицы
Пора рассмотреть более сложные уроки Excel. Сводная таблица может быть создана на основе документов, которые были отредактированы другими пользователями.
В этом нам поможет функция «Слияние». Эта программа позволяет автоматически объединить текст документа и необходимые адреса, имена или другую необходимую информацию для создания персонализированных прогнозов, что в нашем случае и требуется.
Функция слияния в поздних версиях «Офиса» практически не изменилась в сравнении с Office 2003. Тем не менее запросы к базам данных для выбора источника данных могут выполняться с применением Microsoft Query. Чтобы получить данные сводной таблицы Excel, необходимо наличие двух объектов:
- Основной документ, содержащий нужную вам информацию, а также поля, указывающие программе Excel, какие данные следует вставлять в документ. Основная таблица является стандартным документом табличного процессора, однако необходимо указать в окне диалога Слияние (Mail Merge Helper), что именно он должен использоваться как основной.
- Источник данных, содержащий ту информацию, которая необходима для составления прогноза. Можно создать новую таблицу тем способом, который мы указывали выше.
Когда происходит процесс создания сводных документов, то программа создает одну копию для каждой из записей, которые содержатся в файле источника данных. Эти файлы могут быть показаны в виде отдельных разделов нового документа или же использованы в качестве разделов вашей сводной таблицы.
Стоит только кликнуть по нужному вам полю левой клавишей мыши, и вы сможете посмотреть данные источника вместо имен полей, которые отображаются в основном документе. Поля можно вставлять в любое место документа.
Используемые категории
Также здесь можно использовать категорию ASK, что значит «запросить», и FILLIN, что значит «заполнить», NEXT и NEXTIF, ELSE. Также можно произвести настройку параметров тех пунктов прогноза, которые будут отображены в готовой таблице. Найти необходимую запись можно при помощи специального фильтра или же путем простой сортировки данных, о чем мы уже говорили выше.
Когда все готово для создания составного документа, окно диалога Merge предоставляет пользователю возможность выявить и устранить отдельные ошибки до их появления. Когда все проверено, то можно смело жать на кнопку «Объединить». Если же слияние должно производиться через электронную почту, то выйдет запрос на установку почтовой программы (если у вас она не установлена).
В случае когда в процессе слияния возникает какая-то ошибка, то это говорит о некорректно указанном поле в чужом документе, или же используемые данные представлены в неподдерживаемом формате. Поэтому эту процедуру стоит еще раз пересмотреть.
Вообще, Excel (работа со сводными таблицами в котором нами рассматривается) позволяет не только проводить сравнительно сложные анализы покупательской способности, но и создавать на их основе прогнозы.
Сводные таблицы Excel – это особый тип построения, который подразумевает наличие функции моментального формирования отчета по документу.
С их помощью можно легко обобщить некоторые однотипные данные.
В программе Excel 2007 (MS Excel 2010|2013) сводная таблица используется, в первую очередь, для составления математического или экономического анализа данных.
Как сделать сводную таблицу в Excel
Анализ данных документа способствует более быстрому и правильному решению поставленных задач.
Табличный процессор Эксель может справиться даже с самыми объемными и сложными документами. Составить отчет и обобщить данные не составит труда для программы.
Чтобы создать саму простую таблицу-сводку, следуйте нижеприведенным указаниям:
- С помощью вкладки «вставка», которая находится в главном меню панели инструментов программы, выберите клавишу создания рекомендуемых сводных таблиц;
- В открывшемся диалоговом окошке программы нажмите на понравившийся вам каркас таблички. Таким образом макет станет доступным для предварительного пользовательского просмотра.
Выберите наиболее подходящий вариант, который в полной мере сможет отобразить всю информацию;
Совет! Дополнительные макеты сводных таблиц можно скачать с официального сайта компании «Майкрософт ».
- Нажмите клавишу ОК, и программа сразу добавит выбранную таблицу (или пустой макет) на открытый лист документа. Также программа автоматически определит порядок расположения строк, согласно представляемой информации;
- Чтобы выделить элементы таблицы и упорядочить их вручную, отсортируйте содержимое. Также данные можно фильтровать. По сути, сводная табличка – это прототип небольшой базы данных.
Фильтрация крайне необходима, когда появляется необходимость быстрого просмотра только определенных колонок и строчек. Ниже приведен пример сводной таблицы по продажам после фильтрования содержимого.
Таким образом можно быстро просмотреть объемы продаж в отдельных регионах (в нашем случае, запад и Юг);
Желательно пользоваться уже созданными макетами, однако, пользователь может выбрать пустую таблицу и наполнить ее самостоятельно.
В пустой шаблон необходимо добавить поля, формулы для расчета, фильтры.
Пустая форма заполняется путем перетаскивания на отдельные области необходимых элементов данных.
Также можно создавать связанные таблицы-сводки на нескольких листах документа одновременны.
Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.
Проводить анализ внешних данных тоже можно с помощью сводных таблиц.
Сводные расчеты в Microsoft Excel - Формулы
При составлении отчетов в созданной сводной таблице, можно использовать большое количество статистических функций и формул для того, чтобы проанализировать полученные значения и базовые входящие данные.
В таблицу можно добавить, к примеру, элемент для вычисления комиссии для платежа или любой другой вид расчетного элемента.
Все формулы в столбики и строки добавляются с помощью поля «Вставка».
С помощью данной вкладки можно создать график, рассчитать элементы по формуле и построить гистограмму зависимости.
Еще интересный материал на тему:
Функции для сводных таблиц в Экселе - Виды
Сводные таблицы в табличном процессоре могут выполнять следующие функции:
- Получение данных по запросу пользователя. Редактор документа может сослаться на отдельную ячейку таблички и получить из нее необходимые данные;
- Фильтрация исходной информации. Такая возможность позволяет быстро просмотреть искомую информацию для нескольких сущностей таблицы одновременно;
- Сводка по нескольким диапазонам сразу. Пользователь может выбрать как уже имеющиеся типы сводок, так и создать свою личную с помощь макросов;
- Группировка данных с определенным шагом. То есть редактор документа может объединять в отдельные группы информацию, которая соответствует определенному временному промежутку (данные за месяц, год и прочее);
- Создание полноценных отчетов с возможностью их распечатки. Данная функция позволяет сэкономить время на составление текстового отчета после создания таблицы.
Чтобы укомплектовать данные в таблице наиболее удобным способом и всегда иметь возможность быстрого доступа к ним, попробуйте сделать сводную таблицу в программе Microsoft Office Excel. Составление такого типа таблицы займёт у вас пару минут, а её использование сохранит драгоценное время в дальнейшем.
Зайдите в программу Эксель. В шапке программы среди множества разделов найдите “Вставка” и кликните по ней. Вам понадобится инструмент “Сводная таблица”, если данные вами уже введены и отформатированы. Если же нет, приступите к вводу.
Это особенно удобно, когда вы не уверены на сто процентов, что не будете менять ваши значения в ячейках. Вы просто создадите таблицу на следующем листе, а при исправлениях, сможете удалить её без потери информации.
Значение суммы будет отображено отдельно под каждым столбцом автоматически, справа снизу вы увидите некоторые параметры для редактирования таблицы.
Теперь вы без труда можете создавать сводные таблицы в программе от Microsoft – Excel. В ней есть опции помещения данных из совершенно другой таблицы, даже на другом листе. А выделяя разные столбцы, вы имеете возможность комбинировать множество таблиц в одной.
Такой тип таблиц лучше использовать с очень объёмными и большими таблицами, так как вам не придётся вручную задавать значение суммы для каждого столбца.
