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

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

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

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

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

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

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

Источник

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) вернуть, какие клетки соответствуют обоим критериям

Источник

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

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

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

    ABCDEF
12 Заголовок I II II Всего
13 открыто 1   75  21  97
14 Закрыто 21  312 39  372
15 N/A              2   47  7   56
16 Непросмотренный                   
17 Всего возможно                   
18 Всего проверено 24  434 67  525
19 процентов завершено                 
20
21                  
22                  I   II  II Всего
23 открыто 1   75  21  97
24 Закрыто 21  312 39  372
25 N/A              2   47  7   56
26 Не просмотрены                   
27 Всего возможно                   
28 Всего проверено 24  434 67  525
29 процентов завершено

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

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

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

Источник
Другие вопросы по тегам