Как сделать так, что бы данные из нескольких Google-таблиц собирались в один отчет — Финансы на vc.ru

{«id»:13917,»url»:»\/distributions\/13917\/click?bit=1&hash=aad2fedf5b8241c98b567cd4ef2ae24670b07ffc15dbdd7460b77593010736e9″,»title»:»\u041a\u0430\u043a \u043f\u0440\u0435\u0432\u0440\u0430\u0442\u0438\u0442\u044c \u0442\u0440\u0430\u0444\u0438\u043a \u0432 \u043f\u0440\u043e\u0434\u0430\u0436\u0438 «,»buttonText»:»»,»imageUuid»:»»,»isPaidAndBannersEnabled»:false}

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

113 403
просмотров

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

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

Прикрепляю небольшой видеообзор итогового решения, что бы было понятней о чем речь)

Обзор кейса

Вот ссылка на папку с таблицами, которые будет описаны далее.

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

Вид таблицы сотрудника

И есть таблица руководителя, содержащая форму отчета.

Вид таблицы руководителя

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

Настройка представления отчета

Шаг 1

Для начала нужно сделать так, что бы данные, которые водятся в таблицы сотрудников появлялись в таблице руководителя.

Для этого создаем в таблице руководителя ровно столько листов, сколько таблиц сотрудников. Данные будем подтягивать формулой IMPORTRANGE()

Посмотрите короткий обзор применения формулы в данной ситуации.

IMPORTRANGE

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

Подробнее о формуле:

=IMPORTRANGE(«Ссылка на таблицу»; «Диапазон, который нужно отобразить из этой таблицы»)

Обязательно заключайте аргументы в кавычки!

Пример функции:

=importrange(«https://docs.google.com/spreadsheets/d/14GIMUMieJBNiUGFEcukx0q0VwhdjzzQ_6R_JSWXbzNs/edit #gid=0″;»Данные!b:g»)

Для самопроверки воспользуйтесь готовой формулой

Шаг 2

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

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

Для этого хорошо подходить формула СУММЕСЛИМН()

Подробнее о формуле:

=СУММЕСЛИМН(диапазон суммирования;диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

И эту формулу нужно приписать относительно каждой таблицы.

Вид формулы в ячейке отчета:

= СУММЕСЛИМН1+СУММЕСЛИМН2+СУММЕСЛИМН3…СУММЕСЛИМНN

Про формулу СУММЕСЛИМН и про то как ее использовать для разных вычислений напишу статью немного позже.

А пока прикрепляю запись вебинара, где мы очень подробно разбираем IMPORTRANGE, СУММЕСЛИМН, ЕСЛИ, FILTER для создания модели отчета.

Если хотите получить записи курса по Гугл таблицам, то можно посмотреть информацию на этой страничке.

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

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

Примечание: Другой способ консолидации данных — использование Power Query. Дополнительные сведения см. в справке по Power Query для Excel.

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

Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».

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

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

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

В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).

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

Чтобы объединить данные всех диапазонов и создать консолидированный диапазон без полей страницы, сделайте следующее:

1. org/ListItem»>

   Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

   1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

   2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.

   3. В списке Выбрать команды из выберите пункт Все команды.

   4. org/ListItem»>

      Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

2. В книге щелкните пустую ячейку, которая не является частью сводной таблицы.

3. Щелкните значок мастера на панели быстрого доступа.

4. На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

5. org/ListItem»>

   На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

6. На странице Шаг 2б сделайте следующее:

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

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

   2. В разделе Во-первых, укажите количество полей страницы сводной таблицы введите 0, а затем нажмите кнопку Далее.

7. На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

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

1. Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

   1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

   2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.

   3. В списке Выбрать команды из выберите пункт Все команды.

   4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

2. В книге щелкните пустую ячейку, которая не является частью сводной таблицы.

3. На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

4. На странице Шаг 2а выберите параметр Создать одно поле страницы, а затем нажмите кнопку Далее.

5. На странице Шаг 2б сделайте следующее:

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

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

6. Нажмите кнопку Далее.

7. На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

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

1. Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

   1. Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

   2. Нажмите Настроить панель быстрого доступа (…) в левом нижнем углу под лентой, а затем нажмите Дополнительные команды.

   3. org/ListItem»>

      В списке Выбрать команды из выберите пункт Все команды.

   4. Выберите в списке пункт Мастер сводных таблиц и диаграмм и нажмите кнопку Добавить, а затем — кнопку ОК.

2. В книге щелкните пустую ячейку, которая не является частью сводной таблицы.

3. На странице Шаг 1 мастера выберите параметр в нескольких диапазонах консолидации, а затем нажмите кнопку Далее.

4. org/ListItem»>

   На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

5. На странице Шаг 2б сделайте следующее:

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

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

   2. В разделе Во-первых, укажите количество полей страницы сводной таблицы щелкните число полей, которые вы хотите использовать.

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

      
      Пример

      • Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».

      • Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».

   4. Нажмите кнопку Далее.

6. На странице Шаг 3 мастера выберите между добавлением сводной таблицы на новый или существующий лист, а затем нажмите Готово.

Консолидация данных в Google Sheets help

Эта страница поможет вам выполнить 3 простых шага надстройки Consolidate Sheets. К концу все ваши таблицы будут не только объединены, но и рассчитаны в один сводный отчет Google Sheets. И у вас будет этот отчет либо в виде значений, либо в виде формулы, которая автоматически будет отражать все будущие изменения, внесенные в исходные листы.

• Видео: как объединить данные из нескольких листов Google
• Прежде чем начать
• Как использовать надстройку Consolidate Sheets
  • Начать объединение листов
  • Шаг 1. Выберите листы для объединения
  • Шаг 2. Выберите параметры консолидации
  • Шаг 3: Выберите дополнительные параметры
  • Получить результат
    • Объединение нескольких файлов с формулой
      • Ограничения стандартных функций, используемых в формуле
• Связанные страницы

Видео: как объединить данные из нескольких листов Google

Перед тем, как начать

При выборе листов для объединения помните, что электронная таблица с результатом не должна превышать ограничение Google Sheets на 10 миллионов ячеек. Если это произойдет, надстройка сообщит вам об этом и не будет объединять ваши данные.

Как использовать надстройку Consolidate Sheets

Запустить Consolidate Sheets

Запустите инструмент из меню Google Sheets: Extensions > Consolidate Sheets > Start :

Совет. Дополнение также можно найти в Слияние и объединение Группа электроинструментов:

Шаг 1: Выберите листы для объединения

На этом шаге вам нужно определить листы, которые вы хотите объединить и рассчитать:

1. Отметьте флажок Sheets выбрать сразу все листы из древовидного списка и сложить их все.
2. Чтобы объединить еще больше листов Google, нажмите Добавить файлы с Диска .

   Совет. Для быстрого поиска файлов доступен быстрый поиск. Просто введите часть имени файла в поле Найдите поле в окне Импорт файлов с диска и нажмите Найти . Consolidate Sheets проверит весь ваш Диск и покажет вам список всех частичных и полных совпадений имен.

   Если вы не уверены, где находятся найденные электронные таблицы, просто наведите на них курсор мыши, и вы увидите подсказку с полным путем:

   Совет. Надстройка достаточно умна, чтобы позволить вам открывать несколько файлов с Диска одновременно. Держите нажатой клавишу Ctrl и щелкните каждый файл, который вам нужен. Как только они будут выбраны (вы увидите, что они станут голубыми), нажмите Добавить :

   Чтобы отменить выбор при выборе файлов, щелкните электронную таблицу еще раз.

3. Если вы передумаете в отношении какой-либо таблицы, выберите ее и нажмите кнопку Исключить . Это удалит файл из списка консолидации.
4. Для каждого выбранного листа можно указать диапазон для суммирования. Нажмите Все данные справа от имени листа (в столбце Диапазон ) и выполните одно из следующих действий:
   • Введите диапазон с клавиатуры.
   • Щелкните значок Выбрать диапазон и вручную выделите нужные ячейки на листе.
   • Щелкните значок Выберите диапазон , выберите любую ячейку в нужной таблице и нажмите Автоматический выбор . Инструмент определит используемый диапазон — ячейки с данными до первого пустого столбца и строки — автоматически:

Когда нужные листы выбраны и диапазоны определены, нажмите Следующий .

Шаг 2: Выберите параметры консолидации

Здесь вы должны решить, какие параметры консолидации подходят вам лучше всего:

1. Выберите одну из 11 широко известных функций в раскрывающемся меню для объединения и расчета ваших таблиц: СУММ, СЧЁТ, СРЕДНИЙ, МАКС, МИН, ПРОДУКТ, ЧИСЛА СЧЁТА, СТАНДОТКЛОН, СТАНДОТКЛОН, VAR, VARP .
2. Выберите способ суммирования данных:
   • Объединить по метке — это вариант, когда в ваших таблицах одинаковые заголовки расположены в другом порядке:
     • Чтобы учитывать заголовки в верхней строке, установите флажок Использовать метку заголовка :
     • Чтобы добавить записи на основе заголовков в первом столбце, отметьте галочкой Использовать метку левого столбца :
     • Если верхняя строка так же важна, как и левая колонка, вы можете выбрать оба варианта для консолидации как по столбцам, так и по строкам:

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

   • Используйте параметр Консолидировать по позиции , если ваши таблицы имеют идентичную структуру и метки не имеют большого значения:

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

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

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

Выберите способ переноса данных с листов

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

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

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

Совет. На данный момент это бета-функция: мы с нетерпением ждем ваших отзывов и очень ценим их!

Решите, куда поместить результат

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

Нажмите Объединить , чтобы объединить и сложить ячейки в Google Таблицах.

Получить результат

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

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

Объединение нескольких файлов с помощью формулы

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

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

Наш аддон предлагает как это сделать правильно:

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

   Наконечник. Если вы поместите результат в новую электронную таблицу, нажмите кнопку Открыть новую электронную таблицу 9.0047 для перехода прямо на вкладку Подключить файлы . Сначала запустите Consolidate Sheets, чтобы включить наши пользовательские функции, а затем выполните следующие действия.

   Если вы поместите результат в текущий файл, лист Connect files станет активным автоматически.

3. Вкладка Подключить файлы всегда будет содержать следующее:
   • описание соединения листов
   • напоминание о запуске Consolidate Sheets (в этом нет необходимости, если вы не поместите результат в новую электронную таблицу, где также должны быть включены наши собственные пользовательские функции)
   • список всех электронных таблиц, к которым необходимо получить доступ по формуле
   • статус доступа

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

4. Как только вы это сделаете, Статус доступа будет говорить Подключено и ячейки станут зелеными:

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

Ограничения функций, используемых в формуле

Наряду с нашими собственными пользовательскими функциями, полученная формула использует пару стандартных: ЗАПРОС и ИМПОРТРАНЖ. Вот некоторые вещи, которые вы должны знать о результате, возвращаемом формулой:

1. В настоящее время формулу можно создать, только если на шаге 2 вы выбрали суммирование (СУММ) или подсчет (СЧЁТ) данных. Другие агрегатные функции не поддерживаются.
2. Формула всегда проверяет, содержат ли консолидируемые столбцы всех листов числовые значения в первых двух строках (исключая строку заголовка). Если в первых двух строках какой-либо таблицы вообще нет числовых записей, формула будет построена, но ничего не вернет в результат.
3. Если вы поместите формулу в новую электронную таблицу, наши пользовательские функции (CONSIDER_HEADERS_CONS, AGGREGATE_CONS, COLUMN_LIST_CONS) не начнут работать, пока вы не запустите Consolidate Sheets один раз . Поскольку этот файл создан заново, таким образом вы активируете в нем наши пользовательские функции.

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

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

Связанные страницы

• Объединить данные из нескольких листов в один
• Объединить данные из двух электронных таблиц Google
• Vlookup несколько совпадений на основе нескольких критериев

Консолидация данных из нескольких листов с помощью формулы в Google Sheets

Многие думают, что Excel Data Consolidation не имеет альтернативы в Google Sheets . Конечно, такой команды нет. Но в Google Таблицах вы можете объединять данные из нескольких таблиц с помощью формулы.

Не списывайте Google Sheets со счетов! В Google Sheets есть мощная функция, позволяющая гораздо лучше консолидировать данные! Вы знаете, что это за функция/формула?

Я говорю о функции запроса. В Google Sheets вы можете консолидировать данные из нескольких листов, используя формулу, основанную на запросе. Это просто узнать!

Мы можем использовать функцию запроса в Google Sheets для объединения нескольких листов и консолидации данных. Что еще! Консолидация данных таким образом не ограничивается листами в одном файле. В этом методе вы можете использовать листы даже из разных файлов.

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

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

Объяснение: Объединение данных с нескольких листов с помощью формулы

Я объединяю данные с нескольких листов в один лист, а затем суммирую их. Здесь несколько листов означают несколько вкладок в одном файле. Как насчет двух независимых файлов Google Sheets?

Это я объяснил после нескольких абзацев ниже.

Как уже упоминалось выше, для консолидации данных в Google Docs Sheets не требуется скрипт или плагин.

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

Можем ли мы консолидировать данные только из нескольких листов из одного файла?

Ответ – НЕТ! Вы также можете объединить несколько листов из разных файлов электронных таблиц.

Для этого вы должны использовать функцию Google Sheets IMPORTRANGE вместе с Query. Эту часть я сейчас пропускаю. Я попытаюсь написать еще один учебник по Google Таблицам для этого. В противном случае вы запутаетесь.

Преимущество использования функции запроса для консолидации данных в Google Таблицах

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

1. Консолидация данных без использования скриптов означает более высокую производительность электронных таблиц.
2. Если мы используем какой-либо плагин (я не знаю ни одного такого плагина, доступного для консолидации данных), мы не можем гарантировать доступность этого плагина всегда. Разработчик может или не может удалить плагин в будущем. Кроме того, с ним могут быть связаны риски безопасности.
3. При использовании формулы для консолидации данных вы получаете больше гибкости. Консолидированные данные будут автоматически обновляться при изменении исходных данных.
4. Наконец, это быстро, а также вы можете один раз настроить формулу и забыть об этом.

Как объединить данные из нескольких листов с помощью формулы в Google Таблицах

Пожалуйста, следуйте пошаговым инструкциям ниже.

Ниже вы видите четыре листа. Первые два листа, я имею в виду вкладки листов, содержат образцы данных, которые мы сначала объединим на третьем листе и, наконец, суммируем их на четвертом листе .

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

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

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

Первые два листа содержат некоторые образцы данных. См. ниже. Название моего первого листа — «junesheet».

Теперь посмотрите данные на втором листе, который называется «июльский лист».

Как объединить данные из нескольких листов с помощью формулы запроса в Google Doc Sheets

Теперь давайте посмотрим, как объединить два вышеупомянутых листа в один лист в одном и том же файле Google Sheets. Я имею в виду третий лист под названием «Комбинированный».

Ниже приведена формула запроса для объединения данных двух вышеуказанных листов в один лист.

 =query({junesheet!A2:H5;julysheet!A2:H5},"Выберите *, где Col1 не равен нулю") 

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

1. Вы должны изменить диапазон данных A2:A5 на фактический диапазон данных.
2. Измените имена листов на свои имена листов. Я использовал два листа для объединения.

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

 {июньский лист!A2:H5;июльский лист!A2:H5;расширенный лист!A2:H5} 

Важно! Объединяйте только похожие типы данных. Никогда не объединяйте текстовый столбец со столбцом даты или числовым столбцом.

Я имею в виду, что если ваш столбец A содержит текстовые строки на первом листе и числа на втором листе, не объединяйте их. Это против консолидации данных с использованием Query. Запрос будет возвращать неправильный вывод при смешанных типах данных в столбцах.

Вышеупомянутые изменения требуются только при объединении нескольких собственных листов . Мои образцы данных уже оправдывают приведенные выше правила.

Снова проверьте изображение ниже, чтобы понять формулу запроса, которую я использовал для объединения нескольких листов в Google Sheets.

Бесконечный диапазон в консолидации

При желании вы можете использовать в формуле бесконечные диапазоны, такие как A2:H. Тогда формула будет такой.

 =query({junesheet!A2:H;julysheet!A2:H},"Выберите *, где Col1 не равен нулю") 

Теперь мы хотим обобщить или консолидировать данные, которые мы только что объединили. Ниже приведены шаги. Перед этим сначала посмотрите объединенные данные ниже.

Как суммировать или консолидировать данные из нескольких листов с помощью формулы в таблицах Google

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

Просто используя еще одну формулу запроса на последнем листе под названием «Консолидация», мы можем добиться требуемого результата.

 =запрос(Combined!A1:h20,"выбрать D, сумма(H) где A не является нулевой группой по D") 

См. объяснение этой формулы с помощью изображения ниже.

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

В нашем образце данных столбец D содержит описание элемента. Я обобщил данные на основе этого поля элемента.

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

Консолидированные данные будут выглядеть следующим образом.

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

Как я и обещал в начале, вот единственная формула, которая объединяет и обобщает данные. Это комбинация двух приведенных выше формул запроса, но с некоторыми изменениями.

 =query({junesheet!A2:H;julysheet!A2:H},"выберите Col4, sum(Col8), где Col1 не является нулевой группой по Col4") 

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

Образец консолидации данных

Что делать, если количество столбцов на каждой вкладке разное?

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

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

This entry was posted in Популярное