Где изменить источник сводной таблицы
Иногда может возникнуть ситуация, когда в исходную таблицу добавились новые столбцы, которые не попали в сводную таблицу. Сразу оговорюсь, что если вы использовали умную таблицу в качестве источника, то такой проблемы не будет, новые столбцы будут добавляться в сводную таблицу автоматически при обновлении.
Предположим, в нашей исходной таблице добавился столбец Продавец. Этот столбец нужно добавить в сводную таблицу, чтобы иметь возможность выводить данные по продавцам.
Для этого щелкнем в любом месте сводной таблицы и далее идем на вкладку Анализ — Источник данных.
Откроется лист с исходной таблицей, в котором бегущим выделением будут выделены данные, которые попали в сводную таблицу, а в окне — источник данных.
Далее необходимо в открывшемся окне в поле Таблица или диапазон указать новый диапазон для сводной таблицы: просто выделить необходимый диапазон левой кнопкой мыши (полностью диапазон, включая новый столбец, а не только новый столбец). Нажимаете Ок.
Вернувшись на лист сводной таблицы, вы увидите, что новое поле Продавец появилось в списке полей. Теперь его можно перетаскивать в блоки СТРОКИ, СТОЛБЦЫ или ФИЛЬТРЫ.
Для чего в Excel нужны сводные таблицы
В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.
Если же требуется создавать сложные отчёты, используя фильтры и меняя исходные данные в нужной последовательности, сделать это стандартными средствами Excel если и можно, то очень сложно. А вот благодаря сводным таблицам такие отчёты создавать гораздо проще, как и различные диаграммы, позволяя только с помощью мышки группировать строки и столбцы, менять их местами, применять различные элементы управления.
Большинство регулярных пользователей Excel даже не догадываются о наличии такого мощного инструмента. Для наглядности приведём небольшой и показательный пример использования сводных таблиц Excel «для чайников».
Допустим, вы менеджер компании и отвечаете за ведение документации по продажам. Все сделки заносятся в таблицу с указанием всех деталей (продавец, покупатель, количество, сумма, дата продажи, адрес покупателя и т. д.). Руководитель просит отчёт о продажах по регионам.
Как мы поступаем? Создаём макет новой таблицы, в шапке которой указываем весь ассортимент реализуемых товаров, а в строках – наименования регионов. Для этого копируем из исходной таблицы столбец с товарами, удалив дубликаты. Используя режим специальной вставки, транспонируем этот столбец в шапку сводной страницы. Аналогичным образом поступаем с регионами, но, поскольку они располагаются в сводной таблице по вертикали, просто копируем без транспонирования.
В каждую ячейку суммируем выручку, используя функцию СУММЕСЛИМН. Наконец, внизу добавляем итоговые результаты и отправляем отчёт руководителю. Руководитель просит регионы расположить вверху, а товары – по строкам. Вы делаете новый отчёт за 5 минут, и получаете новое задание: предоставить данные по прибыли, а не выручке, то есть с учётом затрат. Что ж, немного изменяем формулу, и опять результат готов за считанные минуты. Наконец, поступает просьба подготовить такие отчёты по каждому продавцу, чтобы оценить их эффективность. И это для сводной таблицы не проблема, если знать, где располагаются нужные исходные данные!
Между тем любой пользователь Excel знает, насколько сложно выполнять подобные манипуляции в рамках исходной таблицы.
Даты в сводных таблицах
Ну и последняя важная тема — это даты. Пока мы вообще не трогали поле Дата, но сводные таблицы позволяют очень гибко выводить информацию, связанную с датами и сейчас я это продемонстрирую.
Создадим еще одну сводную таблицу, в которой выведем выручку за все время.
В исходной таблице указывалась конкретная дата каждой сделки, а сводная таблица автоматически сгруппировала даты при этом не только по годам, но и по кварталам и месяцам. При этом в области Строки поле Дата было автоматически преобразовано в три — Годы, Кварталы и Дата.
Если такая группировка не нужна, то можно ее отменить через контекстное меню.
Также с помощью контекстного меню можно вернуть группировку (пункт Группировать), указав необходимые группы. Здесь можно выбрать сразу несколько, например, месяцы и года.
Сортировка и фильтрация по датам работает также, как и с другими данными. Например, можно отключить какой-то временной период.
Для дат существует свой формат срезов — временная шкала.
Она также в интерактивном режиме позволяет выбирать только интересующие вас временные интервалы.
Таким образом на базе сводной таблицы можно создать интерактивный отчет, в котором с помощью срезов и временной шкалы можно очень тонко фильтровать данные. Ну а преобразовав данные сводной таблицы в диаграммы или графики можно получить отличный дашборд, с помощью которого легко можно анализировать или демонстрировать информацию.
Ну а сводные таблицы — это очень обширная и увлекательная тема, которой я посвятил отдельный очень подробный видеокурс, который так и называется «Сводные таблицы».
________________________________________
Ссылки на мои ресурсы по Excel
★ YouTube-канал Excel Master
★ Серия видеокурсов «Microsoft Excel Шаг за Шагом»
★ Авторские книги и курсы
Проверка правильности выставленных коммунальных счетов
С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.
Для начала предлагаем составить сводную таблицу тарифов по всем коммунальным платежам. Для разных городов данные будут свои.
Для примера мы сделали сводную табличку тарифов для Москвы:
Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.
Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:
= тариф * количество человек / показания счетчика / площадь
Для удобства рекомендуем сделать промежуточный столбец, в который будут заноситься показания по счетчикам (переменная составляющая).
Если при расчете коммунальных платежей применяются льготы, их тоже можно внести в формулы. Информацию по начислениям требуйте в бухгалтерии своей обслуживающей организации. Когда меняются тарифы – просто измените данные в ячейках.
Форматирование ячеек по условию
Возможности условного форматирования появились в Excel уже давно, но к версии 2010 года были значительно усовершенствованы. Возможно, вам даже не придётся разбираться в тонкостях создания правил, т.к. разработчики предусмотрели множество заготовок. Давайте посмотрим, как пользоваться условным форматированием в Excel 2010.
Первое, что нужно сделать – выделить ячейки. Далее, на Главной
вкладке нажмите кнопку Условное форматирование
и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.
Рисунок 25 – Выбираем заготовку условного форматирования
Гистограммы выглядят достаточно интересно и хорошо отражают суть информации о цене – чем она выше, тем длиннее отрезок.
Цветовые шкалы и наборы значков можно использовать для индикации различных состояний, например, переходы от критических затрат к допустимым (см. рис. 26).
Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым
Вы можете комбинировать гистограммы, шкалы и значки в одном диапазоне ячеек. Например, гистограммы и значки на рисунке 27 показывают допустимую и чрезмерно низкую производительность устройств.
Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств
Чтобы удалить условное форматирование ячеек, выделите их и в меню условного форматирования выберите пункт Удалить правила из выделенных ячеек
(см. рис. 28).
Рисунок 28 – Удаляем правила условного форматирования
Excel 2010 использует заготовки для быстрого доступа к возможностям условного форматирования, т.к. настройка собственных правил для большинства людей является далеко не очевидной. Однако, если предусмотренные разработчиками шаблоны вас не устраивают, вы можете создать свои правила оформления ячеек по различных условиям. Полное описание данного функционала выходит за рамки текущей статьи.
VBA
VBA (Visual Basic for Applications) – это название языка программирования для Microsoft Office. В данном разделе описаны возможности и примеры использования VBA применительно к Excel.
- Создание макроса: При помощи VBA Вы можете автоматизировать задачи в Excel, записывая так называемые макросы. В этом разделе Вы научитесь создавать простой макрос.
- MsgBox: MsgBox – это диалоговое окно в VBA, при помощи которого можно показывать информационные сообщения пользователям Вашей программы.
- Объекты Workbook и Worksheet: Узнайте больше об объектах Workbook и Worksheet в VBA.
- Объект Range: Объект Range – это ячейка (или ячейки) Вашего листа. Это самый важный объект VBA.
- Переменные: В этом разделе Вы научитесь объявлять, инициализировать и отображать переменную в VBA.
- Выражение «If Then»: Используйте выражение «If Then» в VBA, чтобы выполнять строки кода, если встречается определённое условие.
- Цикл: Циклы – это мощнейшая из техник программирования. Цикл в VBA позволяет Вам обрабатывать диапазон ячеек снова и снова, написав для этого всего лишь несколько строк кода.
- Ошибки макросов: Этот раздел научит справляться с ошибками макросов в Excel.
- Операции со строками: В этом разделе Вы найдёте сведения о самых важных функциях для работы со строковыми элементами в VBA.
- Дата и время: Научитесь работать с датой и временем в VBA.
- События: События – это действия, совершаемые пользователями, которые запускают выполнение кода VBA.
- Массив: Массив – это группа переменных. В VBA Вы можете сослаться на определённую переменную (элемент) массива, используя для этого имя массива и числовой индекс элемента.
- Function и Sub: В Visual Basic for Applications функция (Function) может возвращать значение, в то время как процедура (Sub) – не может.
- Объект Application: Главным в иерархии объектов является сам Excel. Мы называем его объект Application. Объект Application открывает доступ ко многим параметрам, связанным с самим приложением Excel.
- Элементы управления ActiveX: Научитесь создавать элементы управления ActiveX, такие как кнопки, текстовые поля, поля со списком и так далее.
- Userform: В этой главе Вы узнаете, как создавать пользовательские формы в VBA.
Оцените качество статьи
Нам важно ваше мнение:
Список полей и области сводной таблицы
Обратите внимание, что в списке полей отобразились все столбцы исходной таблицы с данными. Поэтому проследите, что все столбцы таблицы имеют заголовки, в противном случае Excel выдаст ошибку о недопустимости имени при попытке создать таблицу
Поэтому проследите, что все столбцы таблицы имеют заголовки, в противном случае Excel выдаст ошибку о недопустимости имени при попытке создать таблицу.
Также необходимо, чтобы в таблице отсутствовали пустые строки со столбцами и объединенные ячейки, в этом случае Excel не понимает структуру исходных данных и может свести данные некорректно.
Обычно в строки и столбцы помещают текстовые поля, а в значения числовые, так как именно область значений является вычисляемой и там мы можем увидеть сводный результат вычислений.
Область фильтра позволяет нам придать нашей таблице еще одну размерность, условно говоря сделать из двумерной таблицы трехмерную, так как фильтр позволяет увидеть одну и ту же таблицу в зависимости от параметра (изменяемого в фильтре).
С элементами разобрались, теперь перейдем непосредственно к построению.
Как применять простые формулы в программе?
Чтобы понять, как работают формулы в программе, можно сначала рассмотреть легкие примеры. Одним из таких примеров является сумма двух значений.
Для этого необходимо ввести в одну ячейку одно число, а во вторую – другое.
Например, В Ячейку А1 – число 5, а в ячейку В1 – 3. Для того чтобы в ячейке А3 появилось суммарное значение необходимо ввести формулу:
Вычисление суммарного значения двух чисел
Определить сумму чисел 5 и 3 может каждый человек, но вводить число в ячейку С1 самостоятельно не нужно, так как в этом и замысел расчета формул.
После введения итог появляется автоматически.
При этом если выбрать ячейку С1, то в верхней строке видна формула расчета.
Если одно из значений изменить, то перерасчет происходит автоматически.
Например, при замене числа 5 в ячейке В1 на число 8, то менять формулу не нужно, программа сама просчитает окончательное значение.
На данном примере вычисление суммарных значений выглядит очень просто, но намного сложнее найти сумму дробных или больших чисел.
Сумма дробных чисел
В Excel можно производить любые арифметические операции: вычитание «-», деление «/», умножение «*» или сложение «+».
В формулы задается вид операции, координаты ячеек с исходными значениями, функция вычисления.
Любая формула должна начинаться знаком «=».
Если вначале не поставить «равно», то программа не сможет выдать необходимое значение, так как данные введены неправильно.
