Блог

Это сложная задача!

Лист, над которым я работаю: https://docs.google.com/spreadsheets/d/1fIBFq4q179k6-5YuhoFZ-3X84tMMkLmIUeV9-KpjN9I/edit?usp=sharing

На вкладке «Отчет» в ячейках C22 и D22 я пытаюсь выяснить, как суммировать сумму, которая была выплачена в ноябре и октябре. Для всех вкладок (и для включения будущих вкладок по мере их добавления).

Вы можете видеть на отдельных страницах с номерами акций (пример ‘PT-1010!’ K14: K), что платежи уже разбиты на общее количество за месяц. Как мне выполнить поиск по всем страницам, узнать, написано ли там «Ноябрь-20», и сосчитать их все — и выдать одно число?

Мой мозг болит, лол — любая помощь очень ценится!!

Это может помочь. (Смотрите Мой примерный лист здесь)

Это захватывает массив всех платежей с датой меньше, чем сейчас, используя косвенную адресацию из списка номеров акций со страницы клиентов. Я помещаю этот список в местоположение (U2 в моем тестировании), используя эту формулу:

 =query(Customers!A5:A,"where A<>'' order by A",0)
 

Таким образом, это всегда текущий / динамический список.

Затем основная формула использует косвенную адресацию для извлечения названий вкладок из этого списка и собирает все данные. Я добавил фильтры, чтобы уменьшить количество собираемых строк, поскольку у вас много будущих дат.

Затем я использую запрос для фильтрации по желаемому месяцу, используя косвенную адресацию к ячейкам даты в отчете. Это позволяет использовать одну и ту же формулу как для C22 (ноябрь), так и для D22 (октябрь).).

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

 =query({filter(indirect(U3 amp;"!B7:E"),indirect(U3 amp;"!B7:B")<Now());
  filter(indirect(U4 amp;"!B7:E"),indirect(U4 amp;"!B7:B")<Now());
  filter(indirect(U5 amp;"!B7:E"),indirect(U5 amp;"!B7:B")<Now());
  filter(indirect(U6 amp;"!B7:E"),indirect(U6 amp;"!B7:B")<Now());
  filter(indirect(U7 amp;"!B7:E"),indirect(U7 amp;"!B7:B")<Now());
  filter(indirect(U8 amp;"!B7:E"),indirect(U8 amp;"!B7:B")<Now());
  filter(indirect(U9 amp;"!B7:E"),indirect(U9 amp;"!B7:B")<Now());
  filter(indirect(U10 amp;"!B7:E"),indirect(U10 amp;"!B7:B")<Now());
  filter(indirect(U11 amp;"!B7:E"),indirect(U11 amp;"!B7:B")<Now());
  filter(indirect(U12 amp;"!B7:E"),indirect(U12 amp;"!B7:B")<Now());
  filter(indirect(U13 amp;"!B7:E"),indirect(U13 amp;"!B7:B")<Now());
  filter(indirect(U14 amp;"!B7:E"),indirect(U14 amp;"!B7:B")<Now());
  filter(indirect(U15 amp;"!B7:E"),indirect(U15 amp;"!B7:B")<Now());
  filter(indirect(U16 amp;"!B7:E"),indirect(U16 amp;"!B7:B")<Now());
  filter(indirect(U17 amp;"!B7:E"),indirect(U17 amp;"!B7:B")<Now());
  filter(indirect(U18 amp;"!B7:E"),indirect(U18 amp;"!B7:B")<Now());
  filter(indirect(U19 amp;"!B7:E"),indirect(U19 amp;"!B7:B")<Now())  },
  "select sum(Col4) where Col4 >0 and Col1 >= date '"amp; text(indirect(address(row()-3,column()  ,4,1)),"yyyy-mm-dd") amp;"' 
                                  and Col1 <  date '"amp; text(indirect(address(row()-3,column()-1,4,1)),"yyyy-mm-dd") amp;"' label sum(Col4) '' ",0)
 

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

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

Дайте мне знать, если это поможет.

Если вы удалите критерии даты в последних строках и измените «сумма (Col4)» на «Col1, порядок Col4 по Col1», вы получите все записи данных с платежами (столбец E на каждой вкладке, правильно?) и сможете проверить результат.