Как пропустить ячейки при создании массива в Excel?

Question

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

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

В настоящее время центральная таблица - это то, что создает массив для вывода, это приводит к дублированию сумм в строках, где номер задачи для подэлементов. Я не могу (не могу) удалить номер задачи для этих подпунктов.

Таблица справа - это то, что у меня есть в настоящее время, с формулой в каждом из заполненных полей суммы. Однако, поскольку эта формула на самом деле состоит из нескольких сумм для разных таблиц данных, я бы хотел обновить только одну ячейку и нажать CTRL+SHIFT+ENTER, чтобы обновить весь столбец ячеек. В этой таблице также указаны ячейки, в которые я бы хотел поместить массив.

Формула из фактического листа:

=SUMIFS('April 18'!S:S,'April 18'!M:M,Summary!A:A,'April 18'!O:O,"4.2018")+SUMIFS('May 18'!T:T,'May 18'!N:N,Summary!A:A,'May 18'!P:P,"4.2018")+SUMIFS('July 18'!$U:$U,'July 18'!$N:$N,Summary!$A:$A,'July 18'!$Q:$Q,"4.2018")+SUMIFS('June 18'!$U:$U,'June 18'!$N:$N,Summary!$A:$A,'June 18'!$Q:$Q,"4.2018")

Answer 1

Допущения и уточнения.

1. Ваша оригинальная формула не проверена. В приведенном ниже примере используются упрощенные данные и базовая формула с использованием SUMIFS.
2. Предполагается, что вы используете Right Indent для отступа подпунктов. Если вы случайно используете пробелы, используйте кнопку «Правый отступ» для выравнивания подпунктов. Также предполагается, что все ваши предметы в основном выровнены по левому краю в соответствующих ячейках.

В этом примере примерная таблица ввода находится в ячейках A1:A13, а таблица вывода - F2:F14.

Откройте редактор VBA, нажав ALT + F11. Вставьте модуль (из меню «Вставка») и вставьте в него следующий код.

Function IndLevl(cell As Range)

    Application.Volatile
    IndLevl = cell.IndentLevel

End Function 

Это создает пользовательскую функцию (UDF), которая принимает ячейку как диапазон и возвращает уровень отступа как число. Нет проверок, поэтому избегайте передачи диапазона ячеек, таких как A1:A4, в качестве входных данных. Просто передайте одну ячейку в качестве параметра eg =IndLevl(A4)

При использовании Excel 2007 или выше сохраните файл как .xlsm (Macro Enabled Excel Sheet)

Теперь в вашей выходной таблице деформируйте формулу в IF, сначала проверив уровень отступа, а затем решив, следует ли выполнить формулу или просто оставить ее пустой.

=IF(IndLevl(G3)>0,"",SUMIFS($C:$C,$A:$A,F3,$B:$B,1.2))

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

Обратите внимание, что Application.Volatile утверждение делает формулу изменчивой. Тем не менее, он не будет выполнен сам, если вы просто измените уровень отступа позже. В таких случаях просто нажмите F9, чтобы пересчитать лист.

Смотрите скриншот ниже.