Есть ли способ сложить произвольное количество ячеек на основе определенных критериев в Excel?

Question

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

Прямо сейчас я делаю это, имея формулу базовой суммы. Реальная электронная таблица намного больше, но в примере C3 будет = сумма (J3+C13+J13), D3 будет иметь = сумма (K3+D13+K13) и так далее. Как вы можете себе представить, это PITA для добавления новых графиков или удаления существующих.

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

Таким образом, ячейка E5 будет считать все ячейки в листе, где число - это три ячейки ниже III и три ячейки справа от N/A. Или что-то, что выполняет то же самое.

Есть ли способ сделать это без изменения макета моей таблицы?

(игнорируйте N/As, я просто скопировал и вставил, чтобы сделать более простой пример изображения без изменения формул, которые ломались, когда я это делал).

Answer 1

Короче говоря, нет способа сделать это без изменения макетов диаграммы, что не лучше, чем указание гораздо большего количества ячеек, чем вы когда-либо будете использовать. В моем примере выше я заканчиваю ячейку J500003 . Предположим, что ваши графики заканчиваются в строке 470. Это не является проблемой. Пустые ячейки считаются 0 и не влияют на сумму. Если вы начнете с произвольно высокой формулы, вам больше никогда не придется изменять формулу.

Если вы открыты для небольшого дополнения к макету диаграмм, формула может быть короче, но все равно в конечном итоге будет указано большое количество неиспользуемых ячеек. Если вы добавите "Заголовок" (или "Статус" или что-то еще) в ячейку над Открытым, то вы можете рассматривать эти столбцы как базу данных и использовать DSUM

Например, вот новый макет:

    A       B       C   D   E   F
12 Header           I   II  II  Total
13 Open             1   75  21  97
14 Closed           21  312 39  372
15 N/A              2   47  7   56
16 Unreviewed                   
17 Total possible                   
18 Total Reviewed   24  434 67  525
19 Percent Complete                 
20
21                  
22                  I   II  II  Total
23 Open             1   75  21  97
24 Closed           21  312 39  372
25 N/A              2   47  7   56
26 Unreviewed                   
27 Total possible                   
28 Total Reviewed   24  434 67  525
29 Percent Complete                 

(Вам действительно нужно только добавить строку текста в ячейки A12 и H2. Я использовал "Заголовок" в моем примере макета.)

Теперь в A1 укажите столбец, который вы хотите, а в A2 укажите значение для сопоставления в этом столбце. Например, я хочу добавить все "я", которые являются "открытыми", поэтому A1 - это "заголовок" (потому что это имя, которое я дал столбцу меток), а A2 будет "открыто", и тогда формула будет be =DSUM(A12:F140000,"I",A1:A2)+DSUM(H2:M140000,"I",A1:A2) Это дает гораздо более короткую формулу, но она не является более или менее эффективной и до сих пор ссылается на потенциально неиспользуемые клетки.

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

Answer 2

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

Нужны изменения форматирования:

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

Если у вас есть суммирования вне основного тела, следующая формула будет работать нормально. Это будет в C4 а затем скопировать в другие ячейки. (Формула является формулой массива и должна вводиться с помощью комбинации клавиш CTRL-SHIFT-ENTER)

{=SUM(IF(COLUMN($A$12:$M$25)=(IF($A$12:$M$12=C$3,COLUMN($A$12:$M$12))),
IF(ROW($A$12:$M$25)=IF($A$12:$A$25=$A4,ROW($A$12:$A$25)),$A$12:$M$25)))}

Разверните $A$12:$M$25 до большего массива, который вы хотите покрыть, и убедитесь, что $A$12:$M$12 является всей первой строкой массива, а $A$12:$A$25 - всей длиной массива ,

Эта формула будет работать только до тех пор, пока вы сохраняете шаблон сетки, потому что она смотрит только на I, II, III в первой строке и на "open" "closed" and "N/A" в первом столбце, а затем вычисляет пересечения.

Чтобы объяснить формулу немного больше:

1. Встроенные операторы if: IF($A$12:$M$12=C$3 находит соответствующие столбцы, а оператор IF($A$12:$A$25=$A4 находит соответствующую строку).

2. Внешние операторы if: IF(COLUMN($A$12:$M$25) и IF(ROW($A$12:$M$25) возвращают, какие ячейки соответствуют обоим критериям