Обратитесь к пронумерованному столбцу без использования изменчивых функций

Question

Я пытаюсь использовать COUNTIF для подсчета количества ячеек в столбце, которые содержат данную строку. Проверяемый столбец определяется ячейкой, содержащей номер.

Вот упрощенная версия того, что у меня есть на данный момент.

Число 3 в ячейке G1 указывает, что столбец для поиска - это 3-й столбец справа от столбца "Имя", который озаглавлен "R3" (строка D). Формула в ячейке G2 правильно подсчитывает количество ячеек в столбце D, которые содержат "Y".

=COUNTIF(OFFSET(A:A,0,G1),"Y")

Проблема с этой формулой состоит в том, что функция OFFSET является изменчивой, что означает, что каждый раз, когда я открываю книгу, формула пересчитывается. Если я пытаюсь закрыть книгу без внесения каких-либо изменений, я получаю диалоговое окно с вопросом, хочу ли я сохранить свои (несуществующие) изменения.

Есть ли альтернативная формула, которая будет вычислять количество "Y" в столбце без использования функции volatile? Если это помогает, настоящие заголовки столбцов для интересующих меня столбцов - это действительно R1, R2, R3 и т.д. Я мог бы преобразовать данные в таблицу, если это поможет.

Answer 1

Вы можете использовать формулу массива:

=SUM((COLUMN(B1:D1)-1=G1)*(B1:D9="Y"))

Введите эту формулу, нажав CTRL+SHIFT+ENTER. Куда:

B1:D1 представляют заголовки столбцов "R".
B1:D9 представляют данные столбца "R".
G1 представляет раунд для поиска.

Эта формула, по сути, создает массив из 1, где есть "Y", но умножает его на 1, если столбец соответствует вашему поиску, или 0, если его нет. Эти элементы, умноженные на 0, эффективно подавляются, и у вас остаются только те, которые соответствуют как флагу "Y", так и правильному номеру столбца.

Преимущество использования этого метода заключается в том, что его очень легко развернуть, и, предполагая, что имена в столбце A никогда не будут "Y", можно даже внедрить его в структуру таблицы для автоматического расширения с помощью формулы:

=SUM((COLUMN(Table1[#Headers])-1=G1)*(Table1="Y"))

Answer 2

Я нашел альтернативный способ расчета числа. Он использует функцию функции INDEX (которая не является изменчивой), которую я пропустил. Если вы введете 0 для аргумента строки и что-то (назовем его col) в качестве аргумента столбца, INDEX вернет весь столбец col в виде массива. Массив столбцов может затем быть найден с помощью COUNTIF

=COUNTIF(INDEX($B:$D,0,G1),"Y")

Конечно, вы также можете ввести 0 для столбца вместо строки, чтобы INDEX возвращал всю строку в виде массива.

Answer 3

Эта энергонезависимая формула поможет вам избавиться от проблемы.

=COUNTIF(CHOOSE($G$2,$B$2:$B$10,$C$2:$C$10,$D$2:$D$10),"Y")

Замечания:

• Чтобы избежать ссылок на ячейки, вы можете использовать Named/Dynamic Named Range в формуле.
• При необходимости измените ссылки на ячейки в формуле.