Консолидация данных нескольких таблиц в Excel

КАК БЫСТРО ПОЛУЧИТЬ СВОДНЫЕ ИТОГИ ПО НУЖНЫМ ПОЗИЦИЯМ

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

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

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

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

Таблица 1 довольно большая — 511 строк. Обработать вручную такой массив показателей не так просто, а для полноценного анализа нужны сводные данные о продажах в разрезе продавцов. Чтобы получить их, ставим курсор на первую ячейку с информацией, которую требуется объединить. Это будет ячейка «Продавец».

Выбираем: Вставка → Сводная таблица. Откроется меню «Создание сводной таблицы» (рис. 1).

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

После этого возникнет меню, в котором для завершения формирования новой таблицы нужно выбрать интересующие поля (рис. 2).

В нашем случае это поле «Продавец» и поле «Сумма продаж». Ставим соответствующие галочки и получаем следующий готовый результат (рис. 3).

Общий итог продаж за период составляет 158 690,04 руб. Выведены и данные продаж по каждому продавцу.

КАК КОНСОЛИДИРОВАТЬ ДАННЫЕ ИЗ НЕСКОЛЬКИХ ТАБЛИЦ

Объединить данные из нескольких таблиц можно и путем консолидации. Для этого нужно задействовать одноименную функцию: Данные → Консолидация.

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

Предположим, у нас есть три таблицы, содержащие поквартальные данные о зарплате работников (табл. 2–4). Они расположены на разных листах одного файла.

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

Займемся консолидацией. Выбираем диапазон каждой таблицы и нажимаем кнопку «Добавить» (рис. 4).

Также делаем активными функции «подписи верхней строки» и «значения левого столбца».

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

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

После проделанных действий получим сводную таблицу (рис. 5).

В консолидированной таблице корректно обобщены все необходимые данные.

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2024.

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

Сводные таблицы Excel | Эксель Практик

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

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

Мое мнение – этот инструмент надо изучать самим и в этой статье я расскажу нюансы, об которые спотыкаются новички, которые сами хотят освоить этот инструмент.

СТ требует правильно построенных данных. Идеальный вид – одной операции в строчку соответствуют разные поля свойств записи.

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

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

Дальше, из любого места этих данных вызываете команду на вкладке Вставка – Сводная таблица.

Excel предложит диапазон, поэтому просто кликайте Ок.

Дальше на новом листе конструктор предложит сформировать будущий каркас сводной таблицы:

Здесь 4 варианта: строки, столбцы, значения и фильтр отчета.

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

Фильтр отчета. Как вы поняли, вы сами строите каркас и какие-то поля вы можете проигнорировать, какие-то поставить горизонтально, какие-то вертикально. Свои фильтры будут и у строк и у столбцов, но фильтр отчета позволяет более удобно отбирать те данные, которые вам нужно показать в этой таблице.

1) Хотя сводная таблица вытаскивает очень наглядно все срезы имеющейся информации, в ней работать не всегда просто, она не поддается редактированию, только read-only . Если у вас много разрезов информации, то проще сделать таблицу и заполнить ее, используя функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и правила работы с относительными ссылками.

Формат функции прост («Значения»; «Сводная таблица»; «Поле1»; «Элемент1»; «Поле2»; «Элемент2» и т.д.)

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

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

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

Excel сводная таблица вычисляемое поле формулы • Вэб-шпаргалка для интернет предпринимателей!

В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.