Срок доставки товара в течении 1-3 дней !!!
|
|
Общие сведения о сводных таблицах в Excel. Таблицы эксель примеры
Сводные таблицы | Excel для финансиста
Cводные таблицы – мощный и очень удобный инструмент Excel для анализа больших объёмов данных. С помощью таблиц можно легко получать сводные отчёты, видоизменяя и настраивая их несколькими щелчками мыши. Рассмотрим сразу на практическом примере.
Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:
- Дата совершения продажи;
- Наименование товара;
- Наименование покупателя товара;
- Сумма сделки.
Относительно этих данных может возникнуть множество вопросов:
- Какая общая сумма продаж?
- Кто самый активный покупатель?
- Какой самый популярный товар по общей сумме сделки?
- Как распределены продажи в течение года, есть ли сезонность у товаров?
- Растут или падают продажи в течение нескольких лет?
На все эти вопросы помогают ответить сводные таблицы.
Обратите внимание на вспомогательные столбцы Год, Месяц, День листа данные в файле с примером. В этих столбцах хранятся соответствующие значения даты, которые будут нужны для некоторых видов отчётов.
Создание сводной таблицы
Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае – вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.
Поля сводной таблицы
На новом листе слева появилась заготовка сводной таблицы, справа окно со списком полей и четырьмя окошками: фильтр отчёта, названия столбцов, названия строк, суммарные значения.
Напомним, сводная таблица должна давать ответы на поставленные вопросы. Например, ответим на три первых вопроса: о сумме продаж, о самом активном покупателе и самом популярном товаре. Для этого нужно отметить в окне справа поля Наименование товара, Покупатель, Сумма. Программа разместит поле Сумма в окошко Суммарные значения (в самом низу справа), а остальные два поля – в окошко Названия строк. Перетащите одно из полей в окошко Названия столбцов. Получится примерно так:
Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировки – По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.
Как правило, данные анализируются за определённый период или несколько периодов. Структурируем данные по временным периодам. Например, можно узнать динамику продаж продуктов по годам. Для этого нужно отметить поле Год и перенести это поле в окошко Названия строк, убрав оттуда поле Покупатель обратно в окно списка полей. Получится наглядная таблица, данные которой удобно использовать для построения графика.
Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.
Скачать пример сводных таблиц Excel: svodnie-tablici
Практический пример со сводными таблицами: Экспорт данных из 1С в Excel на примере отчёта о движении денежных средств
Предыдущая глава: Глава 6. Основные функции Excel
Содержание учебника: Содержание
finexcel.ru
Готовые таблицы Excel. - EXCEL-OFFICE.RU
Диапазон таблицы Excel. 
Готовые таблицы Excel. Рассмотрим, как сделать таблицу в Excel, чтобы автоматически увеличивался размер таблицы, чтобы формулы в столбцах автоматически копировались, чтобы автоматически добавлялся фильтр в новых столбцах, т.д. В Excel есть много приемов, как сделать таблицу в Excel.Как начертить простую таблицу в Excel, смотрите в статье «Как сделать таблицу в Excel».Как сделать сводную таблицу, смотрите в статье «Сводные таблицы Excel». Итак, сделаем динамическую таблицу в Excel.У нас такие данные на листе Excel. Количество строк и столбцов в таблице может быть очень много.
В диалоговом окне, которое появится, когда мы нажмем на кнопку определенного стиля, ставим галочку у слов «Таблица с заголовком».
Можно сделать свой стиль таблицы функциями, расположенными внизу этого окна стилей.Нажимаем «ОК». Получилось так.
Всё, наша таблица готова. Рассмотрим преимущества этой таблицы.Таблице автоматически присваивается имя. Смотрим в диспетчере имен на закладке «Формулы» в разделе «Определенные имена». Наша таблица называется «Таблица5». Имя таблицы можно изменить в диспетчере имен или функцией на закладке «Конструктор». Подробнее об этом читайте в статье «Присвоить имя в Excel ячейке, диапазону, формуле» тут.
Нажимаем «ОК». Получилось так.
Новый столбец автоматически вошел в диапазон таблицы, появился фильтр в этом столбце.В ячейке I2 пишем формулу расчета НДС. Нажимаем «Enter». Формула автоматически скопировалась на весь столбец. Добавим строку. Получилась такая таблица.
Диапазон нашей таблицы увеличился на строку и столбец, диапазон стал таким - А2:I7. Если удалим строки или столбцы в таблице, то диапазон уменьшится. Шапка таблицы закреплена. Заголовки таблицы при прокрутке большой таблицы, остаются в поле видимости.
При прокрутке большой таблице, чтобы появились заголовки, нужно нажать на любую ячейку таблицы. Заголовки столбцов таблицы появятся в строке адреса столбцов.В таблицу можно вставить строку итогов. На закладке «Конструктор» в разделе «Параметры стилей таблиц» ставим галочку у функции «Строка итогов».
Внизу таблицы появляется итоговая строка. 
Динамический диапазон таблицы можно настроить по другому. Смотрите в статье "Чтобы размер таблицы Excel менялся автоматически". С помощью таких таблиц можно сделать связвнные выпадающие списки. Что это такое и как это сделать, читайте в статье "Как сделать связанные выпадающие списки в Excel легко" здесь. В Excel есть много способов сложить данные в таблицах. Подробнее о них читайте в статье "Суммирование в Excel".
Как сделать сводные таблицы в Excel, примеры
В редакторе Excel 2007 сводную таблицу используют в основном для составления математических или экономических анализов данных.
Как создать сводную таблицу в Эксель?
Сканирование информации документа обладает возможностью быстро и правильно решать поставленные задачи. Процессор таблицы Excel без осложнений может работать даже со сложными и объемными файлами. Написать отчет и обобщить информацию не займет много времени у редактора.
- В появившемся окне редактора, требуется нажатие на любой из представленных каркасов таблички. После этого, появится возможность предварительно просмотреть выбранный макет. Следует выбирать наиболее оптимальный вариант, чтобы в будущем вся информация отображалась верно. Если же вам не понравился ни один из представленных макетов, тогда пройдите на официальный сайт Mircosoft и скачайте любой другой;
- Далее, зажимаем кнопку OK, после чего редактор Excel мгновенно добавит указанную таблицу или же пустой макет на чистый лист. К тому же, программа самостоятельно выберет необходимый порядок для строк, опираясь на представленную информацию;- Для выделения элементов таблицы и их равномерного наложения вручную, следует отсортировать содержимое или же провести фильтрацию. Вообще, сводная таблица является прототипом маленькой базы данных, проводить фильтрацию которой можно, при появлении необходимости быстрого просмотра лишь нужных колонок и строк. Ниже на рисунке, можно увидеть пример сводной таблицы после фильтрации содержимого. Благодаря этому, появляется возможность быстро просмотреть объем продаж в указанных регионах.
Рекомендуется использовать лишь уже созданные макеты, но при желании пользователь может выбрать чистую табличку и заполнить данными вручную. В чистый шаблон требуется добавление полей, фильтров и формул для расчета. К тому же, созданный пользователем макет можно сохранить, добавив с список доступных шаблонов для дальнейшего использования. Чистую форму следует заполнять путем перемещения на отдельные области важные элементы данных.
Кроме всего прочего, можно создать связанные сводные таблицы, используя несколько страниц документа одновременно, в результате чего появляется возможность сканирования данных всего документа или даже нескольких. Процесс анализа внешних данных проводится с использованием сводных табличек.
Формулы. Сводные расчеты.
При работе с отчетами в сводной таблице, в большинстве случаях опытные пользователи используют огромное количество статистических функции и формул, для анализа полученный значений и базовых входящих данных.
Например, в сводную таблицу можно добавить любой расчетный элемент или же платеж с элементов вычисления комиссии. Добавление формул в столбцы и строки происходит с помощью кнопки "Вставка". Используя данную вкладку можно провести создание графика, рассчитав при этом элементы по формуле, а после построить гистограмму зависимости.
Возможности сводных таблиц в Excel.
Сводные таблицы обладают следующими функциями:- Открытые данные для пользователя. Программа имеет возможность ссылаться на определенную ячейку таблицы, после чего можно получить содержимые данные;- Процесс фильтрации данных. Данная опция дает возможность быстрого просмотра искомой информации для некоторых сущностей таблички сразу;- Сводка нескольких диапазонов одновременно. Пользователю позволяют не только выбрать уже имеющие сводки, но и создать собственные, используя макросы;- Объединение в группы данных с указанным шагом. Другими словами, программа имеет возможность группировать информацию, соответствующие временному периоду;- Создание полных отчетов с последующей распечаткой. Эта функция сэкономить ваше драгоценной время для составления текстовых отчетов после открытия таблиц.
vgtk.ru
Как в MS Excel сделать сводную таблицу
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Сводные таблицы – это также один из самых недооцененных инструментов эксель. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
- Можно ли отчет сделать не по выручке, а по прибыли?
- Можно ли товары показать по строкам, а регионы по столбцам?
- Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы владеете сводными таблицами, то ответите: да, мне нужно 5 минут, возможно меньше.
Вот как это делается. Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, т.к. вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Очень часто ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Перед тем, как перейти к настройкам, познакомимся с интерфейсом и основными понятиями. Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд. Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле "Менеджер" в область фильтров.
На все про все ушло несколько секунд. Вот, как на самом деле легко работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но это уже более продвинутое использование сводных таблиц.
Исходные данные
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты, например, по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление сводной таблицы
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
иличерез команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Поэтому, чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кешем, где находится моментальный снимок исходных данных.
Используя сводные таблицы даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных. Если что-то осталось непонятным, напишите в комментариях.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Скачать файл с примером.
P.S. Углубленные знания о сводных таблицах можно получить в онлайн курсе "Продвинутое использование MS Excel".
statanaliz.info
Общие сведения о сводных таблицах в Excel
Сводные таблицы – одно из мощнейших средств Microsoft Excel. С помощью сводных таблиц Вы можете в считанные секунды преобразовать тысячи строк с данными в компактный отчет. В этом уроке мы вкратце разберем общие сведения, а также проследим пример создания сводной таблицы в Excel.
При наличии большого количества данных на листе Excel, могут возникнуть затруднения при ее анализе. Сводные таблицы способны сделать листы удобными в управлении при помощи сведения данных и возможности их обработки различными способами.
Использование сводных таблиц для ответа на вопросы
Предположим, мы хотим ответить на вопрос: «Какова общая сумма продаж каждого из продавцов?» применительно к данным на примере ниже. Поиск ответа на этот вопрос может оказаться затруднительным и занять очень много времени, поскольку данные по каждому продавцу разбросаны по многим строкам. Конечно же, можно воспользоваться командой Промежуточный итог, чтобы подвести итог отдельно по каждому продавцу, однако, нам все еще предстоит работать с большим объемом информации.
К счастью, сводная таблица способна мгновенно вычислить и подытожить данные, чтобы их можно было легко читать и обрабатывать. По завершении, сводная таблица примет приблизительно такой вид:
Создав сводную таблицу, Вы можете использовать её для ответа на различные вопросы с помощью перегруппировки или сведения данных. Например, если необходимо ответить на вопрос: «Какова общая сумма продаж по месяцам?», мы можем придать нашей сводной таблице следующий вид:
Создание сводной таблицы
- Выделите таблицу или ячейки (включая заголовки столбцов), содержащие данные, которые необходимо использовать.
- На вкладке Вставка щелкните команду Сводная таблица.
- В появившемся диалоговом окне Создание сводной таблицы введите необходимые настройки, а затем нажмите OK. В нашем примере мы воспользуемся Таблицей1 в качестве источника данных и поместим сводную таблицу на новом листе.
- На новом листе появится пустая сводная таблица и список полей.
- После создания сводной таблицы, Вам потребуется решить какие поля необходимо в нее добавить. Каждое поле является заголовком столбца источника данных. В списке полей сводной таблицы установите флажок для каждого поля, которое необходимо добавить. В нашем примере мы хотим подсчитать общую сумму продаж каждого из продавцов, поэтому установим флажки в полях Продавец и Сумма заказа.
- Выбранные поля будут добавлены в одну из четырех областей, которые расположены под списком полей. В нашем примере поле Продавец было добавлено в область Строки, в то время как Сумма заказа – в область Значения. Кроме этого, Вы можете щелкнуть по нужному полю и, удерживая левую кнопку мыши, перетащить его в требуемую область.
- Сводная таблица вычислит и подведет итоги для выбранных полей. В нашем примере сводная таблица подсчитала общую сумму продаж каждого из продавцов.
Как и при работе с обычными списками в Excel, Вы можете сортировать данные в сводной таблице, используя команду Сортировка и фильтр, которая находится на вкладке Главная. Кроме этого, Вы можете применить любой тип числового форматирования, например, изменить числовой формат на Денежный. Правда, некоторые типы форматирования могут пропасть при изменении сводной таблицы.
Если Вы измените какие-либо данные на рабочем листе источника данных, сводная таблица не обновится автоматически. Чтобы обновить ее вручную, выберите сводную таблицу, перейдите на вкладку Анализ и нажмите Обновить.
Оцените качество статьи. Нам важно ваше мнение:
office-guru.ru
Волшебные сводные таблицы 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 завершен.
Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!
Уважаемые читатели, вопросы, отзывы, и замечания оставляйте в комментариях внизу страницы.
Другие статьи автора блога
На главную
Статьи с близкой тематикой
Отзывы
al-vo.ru
Умные таблицы Excel 2007-2013
Видео
Постановка задачи
Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера - вот такого вида:
Размер - от нескольких десятков до нескольких сотен тысяч строк - не важен. Задача - всячески упростить и облегчить себе жизнь, превратив эти ячейки в "умную" таблицу.
Решение
Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):
В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:
В результате после такого преобразования диапазона в "умную" Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):
- Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
- Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки - она растянется ниже, если добавить новые столбцы - разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:
- В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).
- При добавлении новых строк в них автоматически копируются все формулы.
- При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.
- При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):
- Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:
- К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
- =Таблица1[#Все] - ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
- =Таблица1[#Данные] - ссылка только на данные (без строки заголовка)
- =Таблица1[#Заголовки] - ссылка только на первую строку таблицы с заголовками столбцов
- =Таблица1[#Итоги] - ссылка на строку итогов (если она включена)
- =Таблица1[#Эта строка] - ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] - будет ссылаться на значение НДС из текущей строки таблицы.
(В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).
P.S.
В Excel 2003 было что-то отдаленно похожее на такие "умные" таблицы - называлось Списком и создавалось через меню Данные - Список - Создать список (Data - List - Create list). Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.
www.planetaexcel.ru
