SUMIF с 3D Reference в Excel

Это выглядит сложно, но это не так.

«‘» name_range_of_sheet_names «‘»: * Это ссылка: / excel-range-name-all-about-excel-named-range-excel-range-name [named range], которая содержит имена листов. Это очень важно.

«диапазон_критериев»: * Это текстовая ссылка на критерии, содержащие диапазон. (Он должен быть одинаковым на всех листах для 3-D справочной работы.)

критерии: * Это просто условие, которое вы хотите поставить для суммирования. Это может быть текст или ссылка на ячейку.

«диапазон_суммы»: * Это текстовая ссылка на диапазон сумм. (Он должен быть одинаковым на всех листах для 3-D справочной работы.)

Хватит теории, давайте сделаем 3D-реферирование с работающей функцией СУММЕСЛИ. === Пример: Сумма по регионам из нескольких листов с использованием трехмерной привязки Excel: *

Мы берем те же данные, что и в link: / excel-generals-3d-reference-in-excel [простой пример трехмерной привязки]. В этом примере у меня есть пять разных листов, содержащих похожие данные. Каждый лист содержит данные за месяц. В главном листе я хочу получить сумму единиц и коллекцию по регионам со всех листов. Давайте сначала сделаем это для единиц. На всех листах единицы находятся в диапазоне D2: D14.

Теперь, если вы используете обычную трехмерную привязку с функцией СУММЕСЛИ, = СУММЕСЛИ (Янв: Апр! A2: A14, Мастер! B4, Янв: Апр! D2: D14)

Он вернет #VALUE! ошибка. Поэтому мы не можем его использовать. Мы будем использовать общую формулу, упомянутую выше.

Используя приведенную выше универсальную трехмерную формулу СУММЕСЛИ для Excel, запишите эту формулу в ячейку C3:

Здесь месяцы — это ссылка: / excel-range-name-all-about-excel-named-range-excel-range-name [named range], которая содержит имена листов. Это очень важно.

Когда вы нажимаете Enter, вы получаете точный результат.

How does it work? The core of the formula is the INDIRECT function and the named range. Here the string»‘»&Months&»‘!» & «A2:A14″translates to an array of range references of each sheet in the named range.*

Этот массив содержит текстовую ссылку * на диапазоны, а не на фактические диапазоны. Теперь, поскольку это текстовая ссылка, она может использоваться функцией ДВССЫЛ, чтобы преобразовать их в фактические диапазоны. Это происходит для обеих функций КОСВЕННО. После разрешения текстов внутри функций КОСВЕННО (удерживайте) формула выглядит так:

Теперь вступает в действие функция СУММЕСЛИ (не КОСВЕННАЯ, как вы могли догадаться). Условию соответствует первый диапазон «Янв! A2: A14». Здесь функция ДВССЫЛЫЙ работает динамически и преобразует этот текст в реальный диапазон (_ поэтому, если вы попытаетесь решить ДВССЫЛНЫЙ сначала с помощью клавиши F9, вы не получите результат_). Далее суммируются совпавшие значения в диапазоне «Янв! D2: D14». * Это происходит для каждого диапазона в массиве. Наконец, у нас будет массив, возвращаемый функцией СУММЕСЛИ.

Теперь СУММПРОИЗВ делает то, что умеет лучше всего. Он суммирует эти значения, и мы получаем работу нашей функции 3D СУММЕСЛИ.

Так что да, ребята, вот как вы можете реализовать функцию 3D СУММЕСЛИ. Это немного сложно, я согласен с этим. В этой трехмерной формуле много ошибок. Я бы посоветовал вам использовать функцию СУММЕСЛИ на каждом листе в определенной ячейке, а затем использовать link: / excel-generals-3d-reference-in-excel [обычные 3D-ссылки] для суммирования этих значений.

Надеюсь, я достаточно объяснил. Если у вас есть какие-либо сомнения относительно ссылки Excel на любой другой запрос, связанный с Excel / VBA, задайте вопрос в разделе комментариев ниже.

link: / excel-generals-relative-and-absolute-reference-in-excel [Относительная и абсолютная ссылка в Excel] | Ссылки в Excel — важная тема для каждого новичка. Даже опытные пользователи Excel делают ошибки при ссылках.

link: / lookup-formulas-dynamic-workheet-reference [Справочник по динамическому рабочему листу] | Предоставляйте справочные листы динамически, используя функцию ДВССЫЛ в excel. Это просто …​

link: / excel-range-name-expanding-links-in-excel [Расширение ссылок в Excel] | Расширяющаяся ссылка расширяется при копировании вниз или вправо. Для этого мы используем знак $ перед номером столбца и строки. Вот один пример …​

link: / excel-range-name-absolute-reference-in-excel [Все об абсолютной ссылке] | Тип ссылки по умолчанию в Excel является относительным, но если вы хотите, чтобы ссылка на ячейки и диапазоны была абсолютной, используйте знак $. Вот все аспекты абсолютных ссылок в Excel.

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-вашу-продуктивность [50 комбинаций клавиш Excel для повышения вашей продуктивности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.

link: / формулы-и-функции-введение-функции-vlookup [Функция ВПР в Excel] | Это одна из самых используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов.

link: / tips-countif-in-microsoft-excel [COUNTIF в Excel 2016] | Подсчитайте значения с условиями, используя эту удивительную функцию. Вам не нужно фильтровать данные для подсчета определенного значения.

Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.