Подсчитайте значения на другом листе в электронной таблице Google, где имя листа является значением ячейки, используя ARRAYFORMULA

#google-sheets

Вопрос:

У меня есть электронная таблица Google с несколькими листами. Я не могу поделиться образцом, потому что это G-Suite Enterprise, и он заблокирован. При крайней необходимости я могу создать фиктивный файл в своем личном Gmail.

На одном из листов есть список значений поиска и имен листов. Я пытаюсь придумать, ARRAYFORMULA который будет подсчитывать количество Lookup Value показов в столбце A of Lookup Sheet Name .

 | Lookup Value | Lookup Sheet Name | Count |
|--------------|-------------------|-------|
| one          | Primary           | ...   |
| two          | Secondary         | ...   |
| three        | Stuff             | ...   |
| ...          | Primary           | ...   |
| ...          | ...               | ...   |

Я придумал эту формулу для C2 , но она не работает. Я точно не знаю, почему, но оно отображается 1 для каждого значения поиска или только для одной строки 1 .

 =ArrayFormula(IF(A2:A <> "", COUNTIF(INDIRECT(B2:B amp; "!A2:A"), A2:A), ))

Я не хочу использовать скрипт Google Apps для решения этой проблемы.

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

2. Да, я так и думал. Но я подумал, что попробую опубликовать JIC, у кого-то есть умное решение.

Ответ №1:

Вот одно из возможных решений, которое объединяет значения разных листов в один, добавляя второй столбец с постоянным значением (например, имя seet).

 =ArrayFormula(
  IF(A:A<>"",
    COUNTIFS(
      {
        FILTER('sheet A'!A:A,NOT(ISBLANK('sheet A'!A:A)));
        FILTER('sheet B'!A:A,NOT(ISBLANK('sheet B'!A:A)))
      },"="amp;A:A,
      {
        FILTER(IF(ISBLANK('sheet A'!A:A),,"sheet A"),NOT(ISBLANK('sheet A'!A:A)));
        FILTER(IF(ISBLANK('sheet B'!A:A),,"sheet B"),NOT(ISBLANK('sheet B'!A:A)))
      },"="amp;B:B
    ),
  )
)

1. Это может сработать, но у меня будет произвольное количество листов. :/

2. @IMTheNachoMan В этом случае вы можете создать сценарий, который запускается при создании / удалении листа и который изменяет эту формулу, перечисляя все доступные листы.

3. Да, надеясь избежать каких-либо сценариев для этого. Я буду продолжать искать, и если я не смогу что-то найти, я использую скрипт.