Волшебные сводные таблицы Excel!
Опубликовано 12 Июн 2014
Рубрика: Справочник Excel | 13 комментариев
Что такое сводные таблицы Excel? Вероятно, непосвященный человек предположит самое очевидное - это таблицы, в которые собраны (сведены) данные из нескольких других разрозненных источников, отвечающие некоторому запросу. На самом деле это заключение не совсем верно.
Сводные таблицы Excel – это инструмент, который собирает по заданному алгоритму в удобные таблицы-отчеты информацию чаще всего из одной таблицы базы данных! (Возможность использования в качестве источника нескольких диапазонов консолидации существует, но в этой статье рассматриваться не будет.)
При помощи сводных таблиц из хаотичного набора строк-записей базы данных можно за считанные секунды извлечь нужные, отсечь в них лишнюю информацию, добавить недостающую и представить пользователю в красивом и удобном для восприятия виде.
Если вы постоянно работаете в программе MS Excel с большими объемами информации и ничего не знаете о сводных таблицах, то считайте, что вы «заколачиваете гвозди» калькулятором, не зная его истинного предназначения!
Когда следует применять сводные таблицы?
Во–первых, тогда, когда работаешь с большим объемом статистических данных, анализировать которые очень трудоемко при помощи сортировки и фильтров.
Во-вторых, тогда, когда приходится через определенные промежутки времени повторять (пересчитывать) отчеты в связи с изменениями источника – таблицы базы данных.
В-третьих, при анализе изменений данных базы в разрезе различных временных периодов или иных критериев.
Это основные ситуации, при которых раскрывается вся действительно волшебная сила инструмента сводные таблицы Excel.
Создание сводных таблиц и работа с ними никак не влияет на содержание источника – базу данных!!! Сводные таблицы Excel позволяют взглянуть на эти данные под разными ракурсами, не изменяя ничего в базе!!!
Это заключительная и самая важная статья обзорного цикла о хранении информации и управлении большими табличными объемами данных.
В предыдущих статьях цикла мы создали базу данных в Excel, научились сортировать данные, применять автофильтр и расширенный фильтр, вводить информацию через форму.
Создание шаблона сводной таблицы.
Описанные далее действия относятся к MS Excel 2003, но и в более новых версиях программы все почти аналогично.
Сводную таблицу будем создавать в той же книге Excel, где расположен источник информации — таблица базы данных, но на другом листе.
Продолжаем работу с учебной мини-базой БД2 «Выпуск металлоконструкций участком №2», с которой работали во всех статьях этого цикла.
1. Открываем в MS Excel файл database.xls.
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. Для начала перетащим поле «Заказ» из окна «Список полей сводной таблицы» или прямо из самой сводной таблицы в самый верх листа в зону «Поля страниц» шаблона.
6. Далее поместим поле «Изделие» в область «Поля строк».
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 завершен.
Уважаемые читатели, вопросы, отзывы, и замечания оставляйте в комментариях внизу страницы.
Статьи с близкой тематикой
Отзывы
13 комментариев на «Волшебные сводные таблицы Excel!»
Ваш отзыв
Посетители: 2,1 млн
Если сводные таблицы Excel
вызовут интерес у читателей блога, то в будущем эта тема будет продолжена выпуском
небольшой брошюры с важными и ценными практическими рекомендациями.
Это была бы бесценная брошюра, Александр!
Спасибо за блог
Светлана
Спасибо! Достаточно доступно и просто
Я тоже хочу эту брошюру!
Сводные таблицы. И еще, Саша, мы как то говорили...расчет редукторов или усилий на реечную передачу? Расчет редуктора нужен...
(tehnika-evm.ucoz.com)
Вадим, напишите подробнее и по-понятнее через страницу «обратная связь» или по e-mail.
Здравствуйте! Я студент экономического факультета и недавно стал изучать возможности Excel для работы с данными и Ваши статьи мне очень помогли в этом процессе. Спасибо за Ваш труд.
Удачи Вам, Роман!
А как сделать одну сводную таблицу на основании нескольких листов с данными?
Сама по себе статья познавательная.
Автору респект и уважение!
(antonov.su)
В Excel2003, которым я продолжаю пользоваться, это легко сделать при создании сводной таблицы в Мастере на первом шаге указав источником данных «в нескольких диапазонах консолидации».
В более новых версиях программы Excel сделать это, как мне кажется, сложнее.
Однако, всегда данные недолго консолидировать и ручками...
У меня есть одна таблица (таб1) в которую заносятся данные,а на основании этой таблицы уже делаются несколько других.
Например в одну из таблиц (таб2) заполняются практически те же строки что и в таб1, но только с определенным параметром в одном из столбцов.
Подскажите как лучше сделать таб2, используя сводные таблицы или возможно хватит только расширенного фильтра, ведь он позволяет копировать данные на другой лист?
Николай, по той информации, что Вы написали нельзя ответить на вопрос: «как лучше сделать таб2?»
На него вообще нельзя ответить. Откуда я могу знать «как лучше», если не знаю что и для чего Вы делаете.
Скопировать на другой лист в таб2 отобранные строки из таб1, попробовал использовать расширенный фильтр, похоже он такого не умеет...
Кто чего не умеет? Отфильтровываете, копируете, вставляете. Результат — в новой таблице отфильтрованные данные из старой. Не понимаю, что у Вас не получается. Шлите файл с подробным описанием задачи, которую нужно решить.