Как объединить данные из нескольких листов с помощью 3D-ссылок в Excel
Excel 3D Reference – это короткая и гибкая формула, которая позволяет выполнять вычисления, ссылаясь на одну и ту же ячейку или диапазон ячеек на нескольких листах. Использование трехмерных формул избавляет от необходимости ссылаться на каждую ячейку, что упрощает вычисления. Когда вы применяете трехмерную формулу один раз, она будет включать данные из листов, которые вы добавите позже.
В этой статье будет рассмотрено, как объединение данных на одном листе может помочь сократить время, необходимое для расчета данных на нескольких листах. Управление большими объемами информации на одном листе может быть простым и эффективным, что позволяет получать точные результаты при минимальных требованиях.
Различные подходы к суммированию данных в Excel
В Excel традиционный способ суммирования информации на нескольких листах – это вручную сложить каждую ячейку с помощью функции суммы. Напротив, добавление их вручную может стать все более проблематичным, если у вас есть большой набор данных о ежемесячных продажах в течение длительного времени.
Это также не динамический метод, поэтому вам придется снова изменить формулы, если вы добавите дополнительный лист. Давайте быстро посмотрим, как объединение данных на нескольких листах в один лист с помощью Excel 3D Reference помогает быстро и точно суммировать данные.
Синтаксис справочника Excel 3D
Взгляните на синтаксис формулы трехмерной привязки, которая суммирует значения в ячейках B4 для четырех листов с именами январь, февраль, март и апрель.
=SUM(January:April!B4)Как показано выше, в формуле Excel 3D есть два основных элемента. Первый относится к именам первого и последнего листов, разделенных точкой с запятой. Во второй части на всех четырех листах есть ссылка на одну и ту же ячейку или диапазон ячеек.
В этом случае используется СУММ. Вы также можете использовать любую другую формулу при расчете. Однако синтаксис 3D-ссылки остается прежним. Чтобы использовать 3D-справочник Excel, данные должны быть согласованы на всех четырех листах.
Справочный пример Excel 3D
Теперь давайте разберемся с принципом работы функции Excel 3D. Чтобы оценить это, давайте применим это на практике, используя набор данных.
Взгляните на образцы данных, которые содержат продажи за четыре месяца, количество акций, проданные запасы, купленные запасы и запасы на конец каждого месяца.
Данные за январь:
Данные за февраль:
Данные за март:
Данные за апрель:
На первом этапе оцените данные, доступные на четырех листах, чтобы убедиться, что они последовательные. Только после этого можно будет реализовать 3D-ссылки.
Реализация 3D-ссылки
Чтобы рассчитать общий объем продаж за четыре месяца, выберите ячейку, в которой появится цифра.
В этом примере стоимость продажи находится в ячейке B3 для всех четырех листов; справочные данные на четырех листах будут в одной ячейке. Диапазон будет с января по апрель, потому что оба заданы имена для начального и конечного листов соответственно.
1. Добавьте ячейку B3 в качестве ячейки ссылки .
2. С января по апрель выберите используемый диапазон .
3. Нажмите Enter, чтобы выполнить формулу.
Чтобы убедиться, что формула была применена правильно, вы можете вручную суммировать значения для определенного элемента в наборе данных. Как только вы узнаете, что это правильно, вы можете ожидать, что остальные расчеты будут правильными, если значения согласованы.
Тот же подход для расчета общей стоимости продаж за четыре месяца применяется к запасам, купленным, проданным и закрывающимся запасам в конце каждого месяца.
Диапазон остается неизменным в течение четырех месяцев, а ячейки, на которые указывает ссылка, будут меняться в зависимости от их расположения на разных листах. Суммируем данные по запасам за четыре месяца.
1. Добавьте ячейку B4 в качестве ячейки ссылки .
2. Выберите диапазон с января по апрель .
Перетащите его, чтобы реализовать формулу трехмерной суммы для других ячеек в строке, используя последовательность автозаполнения .
Таким же образом вы можете применять 3D-ссылки к диапазону ячеек на разных листах. В этом случае выбор листов остается прежним.
Однако вам нужно выбрать весь диапазон, а не одну ячейку в качестве ссылки. Кроме того, убедитесь, что данные согласованы на всех листах.
Добавление нового листа в книгу
Справка Excel 3D автоматически обновляет данные при добавлении одного или нескольких новых листов, если эти листы находятся между диапазонами, выбранными в 3D-формуле. Посмотрим, как это работает.
Как показано ниже, общий объем продаж за четыре месяца составляет 2 50000 за период с января по апрель.
Добавим новый лист с данными за середину февраля.
Включите продажную стоимость листа в середине февраля. Данные автоматически появятся на основном листе четырехмесячного анализа.
Как показано выше, общие продажи за все месяцы увеличиваются на 10 000. Таким образом, без изменения формулы результат обновился автоматически.
Эта гибкость пригодится при отслеживании продаж за весь год по массивным наборам данных. Давайте добавим данные о запасах, проданном количестве, покупаемом количестве и конечном запасе в таблицу на середину февраля.
Посмотрите, как вышеуказанное дополнение повлияет на данные за четыре месяца анализа.
Таким образом, справочная формула успешно обновила все данные на основном листе.
Сделайте данные согласованными для применения трехмерных ссылок
Если вы планируете объединить данные из нескольких листов в один, убедитесь, что они соответствуют номерам ячеек. Если, например, приобретенный товар находится в определенной ячейке на листе 1, необходимо разместить его в этой же ячейке на всех остальных листах. Кроме того, при добавлении нового листа правильно вводите данные в ячейки.
Упростите расчеты с помощью 3D-привязки Excel
Реализация трехмерных формул может показаться сложной для реализации, но их легко поддерживать, если вы выполняете их правильно. Он позволяет добавлять новые данные каждый месяц, не выполняя вычислений с самого начала.
Трехмерная формула также исключает вероятность дублирования или пропуска продаж за любой конкретный месяц. Эта функция будет наиболее полезна для бухгалтеров, которые обновляют пакеты данных каждый месяц.