Финансы в Excel

Главная Статьи Сводные таблицы Редактирование сводной таблицы

Для работы примера требуется подключение макросов VBA. В Excel 2002-2003 может потребоваться предварительно изменить безопасность макросов до среднего или низкого уровня (Сервис \ Макросы \ Безопасность). В Excel 2007 щелкните на строку сообщения под лентой, а затем подтвердите операцию.

Без подключенных макросов пример будет работать в стандартном режиме отображения деталей (drill-down) при двойном клике в области данных сводной таблицы.

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

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

1. Устанавливается свойство сводной таблицы, отменяющее стандартное поведение на двойной клик.
2. На уровне листа, на котором располагается сводная таблица, перехватывается событие двойного клика в области данных.
3. Проверяется, не является ли поле вычисляемым. Создается пустой лист для фильтрации исходных данных.
4. Формируется значения фильтра через проверку диапазонов областей строк, столбцов и страниц сводной таблицы. Эти значения записываются на служебный лист.
5. С помощью операции «Расширенный фильтр» фильтруется исходный диапазон данных.
6. Создается новое окно, в которое выводится отфильтрованный диапазон исходных данных.
7. Включается событие на активизацию окна Excel. При возврате в окно со сводной таблицей, второе окно с исходными данными закрывается.

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

Как скрыть столбцы в Экселе плюсом?

Выделите один или несколько столбцов и нажмите клавишу CTRL, чтобы выделить другие несмежные столбцы. Щелкните выделенные столбцы правой кнопкой мыши и выберите команду Скрыть .

Советы и рекомендации при работе с группировкой в Эксель

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

Видеоинструкция

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

Дополнительные вычисления в сводных таблицах. Настройка вычислений в сводных таблицах

1. Войти во вкладку «Рецензирование» на панели инструментов.
2. Кликнуть левой кнопкой мыши по пиктограмме «Защитить лист» .
3. Нажать на кнопку «ok».
4. Теперь столбцы будут защищены от отображения.

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

Источник данных сводной таблицы Excel

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

1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.

2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.

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

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

Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец – отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.

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

• Выбор нового рабочего листа поместит его на новый лист, начиная с ячейки A1.
• Выбор существующего листа разместит в указанном вами месте на существующем листе. В поле «Диапазон» выберите первую ячейку (то есть, верхнюю левую), в которую вы хотите поместить свою таблицу.

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

Группировка выделенных элементов

Можно также выделить и сгруппировать определенные элементы:

Выделите в сводной таблице несколько элементов для группировки: щелкните их, удерживая нажатой клавишу CTRL или SHIFT.

Щелкните правой кнопкой мыши выделенные элементы и выберите команду Группировать .

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

В полях, упорядоченных по уровням, можно группировать только элементы, имеющие одинаковые следующие уровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя группировать города из разных стран.

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

Как сводные таблицы Excel помогут сэкономить ваше время? Блог SF Education

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

Установка собственных параметров сортировки

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

Щелкните ячейку в строке или столбце, которые требуется отсортировать.

Щелкните стрелку на вкладке Метки строк или Метки столбцов, а затем выберите Дополнительные параметры сортировки.

В диалоговом окне Сортировка выберите необходимый тип сортировки:

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

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

Щелкните По возрастанию (от А до Я) по полю или По убыванию (от Я до А) по полю и выберите поле для сортировки.

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

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

В группе Сортировка по первому ключу выберите настраиваемый порядок сортировки. Этот параметр доступен только в том случае, если снят флажок Автоматическая сортировка при каждом обновлении отчета.

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

Примечание: Сортировка по настраиваемым спискам не сохраняется после обновления данных в сводной таблице.

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

Совет: Чтобы восстановить исходный порядок элементов, выберите вариант Как в источнике данных. Он доступен только для источника данных OLAP.

Ниже описано, как можно быстро отсортировать данные в строках и столбцах.

Щелкните ячейку в строке или столбце, которые требуется отсортировать.

Щелкните стрелку на списке Названия строк или Названия столбцов, а затем выберите нужный параметр сортировки.

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

Чтобы отсортировать данные в порядке возрастания или убывания, выберите пункт Сортировка от А до Я или Сортировка от Я до А.

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

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

Подготовка исходной таблицы

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

Форматирование диапазона в виде таблицы

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

Чтобы отформатировать диапазон в виде таблицы, выберите диапазон ячеек и нажмите «Вставить»> «Таблица».

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

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

Щелкните вкладку «Дизайн» в разделе «Работа с таблицами» и введите имя в поле в начале ленты. Эта таблица получила название «Продажи».

Вы также можете изменить здесь стиль таблицы, если хотите.

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

Как сделать сворачивание строк в Excel?

Эта операция тоже не требует большой последовательности действия: выделим область, где находится таблица (чтобы сэкономить немного времени воспользуемся клавишами Ctrl + A — выделить все), и нажмем delete (или на правую кнопку мыши, а затем на «Удалить» в появившемся списке)

Как обновлять таблицу

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

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

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

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

Удаление Сводной таблицы

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

Эта операция тоже не требует большой последовательности действия: выделим область, где находится таблица (чтобы сэкономить немного времени воспользуемся клавишами Ctrl + A — выделить все), и нажмем delete (или на правую кнопку мыши, а затем на «Удалить» в появившемся списке)

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

Добавление новых столбцов/таблиц

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

А что делать, когда в Сводную таблицу требуется добавить дополнительную колонку с данными?

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

Вот мы добавили к источнику столбик «Цена с НДС». Теперь заходим в раздел «Анализ» и открываем «Источник данных».

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

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

Заключение

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

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

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

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

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

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

Если осталось что-то, на чем мы не заострили внимание, а вам бы очень хотелось это узнать, пишите нам об этом, и мы не оставим ваш интерес неудовлетворенным.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

В окошке, отведенном под Сводную таблицу, появились три строки с названиями отделов (левый столбик) и стоимостью товара по каждому отделу. Помимо суммы можно считать максимум, минимум, среднее значение и делать другие математические операции. Итоговый результат по всем трем отделам указывается в самом низу таблицы.

Форматирование диапазона в виде таблицы

• во-первых, необходимо озаглавить все столбцы;
• во-вторых, нужно проследить, чтобы не было пустых ячеек и строк (иначе при создании Сводной таблицы не исключены ошибки при автоматическом заполнении пробелов, что может исказить информацию);
• в-третьих, в столбце могут находиться значения только одного формата (например, в столбце «Дата покупки» допустимо значение лишь типа Дата, «Название» будет содержать только текстовые строки и так далее);
• в-четвертых, в ячейках не должно быть перечисления (то есть неправильно записывать в ячейку адрес в виде перечисления: «Город, улица, дом, квартира». Можно создать столбцы для каждого значения или оставить что-то одно, например, город, если этой информацией можно обойтись).

Если вы выберете только Месяцы в окне Группирование, будут объединены месяцы ИЗ разных лет. Например, элемент июнь отобразит продажи за 2008 и 2009 годы. Обратите внимание на то, что окно Группирование содержит и другие элементы, основанные на времени. Например, можно сгруппировать данные по кварталам (рис. 171.5).