Найти номер столбца в Excel на основе имен заголовков, используя формулу

Question

У меня есть таблица в Excel со следующим 2 макет строки заголовка:

    A      B      C      D      E      F      G      H      I
1 qtr01, qtr02, qtr03, qtr04, qtr01, qtr02, qtr03, qtr04, qtr01
2 2013,  2013,  2013,  2013,  2012,  2012,  2012,  2012,  2015

Как найти номер столбца, который соответствует qtr02 и 2012 (т. Е. Столбец номер 6), используя формулу?

Примечание: столбцы и строки являются динамическими в пределах диапазона. Например, строка, содержащая информацию "qtr", может быть перемещена из строки 1 в 3, а заголовки, которые соответствуют qtr02 и 2012, могут быть перемещены в столбец G из столбца F из-за вставки новой записи столбца. Это динамическое изменение зависит от полученного исходного файла.

Answer 1

Чтобы получить номер столбца, используйте

=MATCH(1, (1:1="qtr02")*(2:2=2012), 0)

Это «формула массива», поэтому после ее ввода необходимо нажать Ctrl+Shift+Enter.

• 1:1 относится ко всей строке 1 .  Если вы предпочитаете, вы можете использовать A1:I1 для ссылки только на ячейки, которые имеют данные.
• (1:1="qtr02") ищет qtr02 в строке 1 (или назначенные ячейки, в зависимости от ситуации) и создает логический массив результатов.  В этом случае массивом является {FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE} поскольку второй и шестой столбцы содержат qtr02 .
• Аналогично, (2:2=2012) создает массив, в котором значения 2012 находятся в строке 2: а именно, {FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE} .
• * , конечно, это умножение.  Поскольку это арифметический оператор, он преобразует свои операнды в числа: FALSE → 0 и TRUE → 1 .  Затем он умножает два массива попарно.  Это приводит к 1 в каждой позиции, где каждый из массивов операндов имеет 1 (TRUE) и 0 другом месте - другими словами, столбцы, где оба условия выполняются.  Это эквивалентно логической функции AND .  В Excel есть функция AND() которая делает то же самое, но, к сожалению, она не работает с массивами.  Использование * - хитрый трюк, чтобы обойти это ограничение в функции AND() .
• И тогда, конечно, MATCH(1, array, 0) находит первый элемент в массиве, который содержит 1 .