Excel SUM COUNTIFS из разных таблиц и надежда на будущее

Практикую MS Excel. У меня есть рабочая тетрадь, в которой я хочу анализировать данные из разных таблиц.

Каждый рабочий лист содержит таблицу с информацией за год. Итак, на листе «2017» у меня есть таблица с именем «Table2017». У меня есть это на каждый год (начиная с 2015 года).

После некоторого исследования я наконец нашел способ подсчитать, сколько раз что-то происходило в определенном месте.

=SUM(COUNTIFS(Table2018[Place];B3;Table2018[Activity];{"Paid";"Awarded"}))
+SUM(COUNTIFS(Table2017[Place];B3;Table2017[Activity];{"Paid";"Awarded"}))
+SUM(COUNTIFS(Table2016[Place];B3;Table2016[Activity];{"Paid";"Awarded"}))
+SUM(COUNTIFS(Table2015[Place];B3;Table2015[Activity];{"Paid";"Awarded"}))

Это прекрасно работает. Он подсчитает, сколько раз для каждого места была оказана платная услуга или награжденная (подаренная / спонсируемая) услуга. В столбце B у меня есть список мест (отсюда ссылка на B3), поэтому после заполнения формулы я могу выбрать ячейку и увеличить / перетащить, чтобы скопировать ее в остальную часть столбца и применить для каждого места.

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

Есть ли способ это сжать? И в идеале, чтобы формула находила каждую таблицу, в которой есть соответствующая информация (например, «Таблица 20 ??» или «Таблица 20 *»), заходила и подсчитывала, сколько раз были найдены мои условия?

Надеюсь, мой вопрос достаточно ясен.

Заранее спасибо!

P.S. У меня нет опыта работы с VBA / VBS, поэтому я надеюсь реализовать это с помощью обычной формулы.


countif excel-formula
person Yogi    schedule 23.01.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Есть способы сделать его более компактным, но они обязательно усложнят функцию, так что это не будет легкой победой. Убедитесь сами: вам нужно иметь возможность циклически перемещаться по годам внутри формулы, не создавая собственных формул. Один из способов сделать это - использовать СТРОКУ внутри функции ДВССЫЛ. Таким образом вы можете заменить несколько

Table2015[Place]

с одной функцией массива, содержащей

INDIRECT("Table"&ROW($A$2015:$A$2018)&"[Place]")

поскольку это функция массива, она по существу будет циклически перебирать ячейки в функции ROW, создавая Table2015 [Place], Table2016 [Place], Table2017 [Place] и Table2018 [Place]. Вся ваша формула будет выглядеть примерно так

 =SUM(COUNTIFS(INDIRECT("Table"&ROW($A$2015:$A$2018)&"[Place]");B3;INDIRECT("Table"&ROW($A$2015:$A$2018)&"[Activity]");{"Paid";"Awarded"}))

и его необходимо ввести с помощью ctrl + shift + enter (вы увидите скобки {} вокруг функции). Это должно работать, чтобы сделать функцию меньше, и вам нужно будет только изменять ссылку на ячейку каждый год вместо добавления другой суммы, но вопрос в том, не легче ли читать и поддерживать отдельные суммы.

person Pavel_V    schedule 24.01.2018
comment
Спасибо, точно меньше. Это все еще не очень сложно читать. Любопытно, что делает $ A в год $ A $ - person Yogi; 24.01.2018
comment
он ссылается на ячейку, чтобы получить номер строки, поэтому я указал на A2015, поскольку столбец на самом деле не имеет значения. Я предполагаю, что вы могли бы использовать ROW (2015: 2018) или что-то в этом роде, но я действительно не проверял это ... Интересно - я всегда автоматически помещал туда ячейку, не особо задумываясь об этом. Я провел небольшой тест, и он действительно работает так же, как ROW (2015: 2018), что было бы легче читать. - person Pavel_V; 24.01.2018
comment
Я думал об этом снова. Поскольку эта формула возвращается в нескольких ячейках (для каждого места), и хотя не составляет большого труда обновлять одну ячейку каждый год и перетаскивать ее для копирования ... Что, если я создам отдельную таблицу, которая просто включает имена все таблицы, которые должны быть включены? Итак, если я добавлю в эту таблицу строку с 2019 годом в следующем году (после его создания), формулы будут автоматически обновляться везде? Могу ли я заменить ROW (2015: 2018) на ROW (TableYearsToInclude [Years])? - person Yogi; 25.01.2018
comment
без СТРОКИ - вам не нужна СТРОКА содержимого таблицы, вам нужно содержимое таблицы. Я создал таблицу TableYears со столбцом Years, а затем это работает в формуле INDIRECT (Table & TableYears [Years] & [Place]). Затем все, что вам нужно сделать, это создать новую таблицу Table20xx и добавить новое значение в таблицу TableYears, и вам вообще не придется прикасаться к формуле. - person Pavel_V; 25.01.2018