Excel - Как связать формулу индекса / соответствия с косвенной формулой для динамической ссылки на столбец

Question

У меня есть лист, на котором я хочу использовать формулу индекса / соответствия, чтобы получить точные значения для уникального пересечения значений «Отдел» и «Счет» в отдельном листе данных.

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

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

Вот мои формулы сейчас:

=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "") 

Где _2015_De December_Act - это именованный диапазон на моей вкладке данных Essbase для значений в диапазоне от Q8:Q356.

а также:

=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")

Где A8 = Данные Essbase, а b8 = Q8:Q356 - этот диапазон представляет значения в декабре месяце в моей Таблице данных Essbase.

Что я делаю неправильно?

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

Answer 1

Essbase Data

| Field Name | Jan - 2016 | Feb - 2016 | Mar - 2016 |
| Key 1      | A          | B          | C          |
| Key 2      | D          | E          | F          |
| Key 3      | G          | H          | I          |
| Key 4      | J          | K          | L          |
| Key 5      | M          | N          | O          |

Report

| Date:        | Mar - 2016   |
| Tab:         | Essbase Data |
| Date range:  | ?            |
| Field range: | ?            |
| Data range:  | ?            |
| Column:      | ?            |
|              |              |
| Field Name   | Value        |
| Key 1        | ?            |
| Key 2        | ?            |
| Key 3        | ?            |
| Key 4        | ?            |
| Key 5        | ?            |
| ...          | ...          |

Определение ваших диапазонов

* Имена удаленных вкладок, содержащие пробелы, должны быть заключены в одинарные кавычки ('Tab name' вкладки » ). Это может быть плохой шрифт, но, с моей точки зрения, похоже, что они отсутствуют в ваших формулах выше.

Во-первых, вы хотите определить диапазон для сопоставления ваших фиксированных точек во времени. Диапазон дат:

="'" & B2 & "'!$1:$1"

Пример вывода будет 'Essbase Data'!$1:$1 Это будет использоваться для MATCH() желаемого столбца.

Во-вторых, вы хотите определить диапазон для совпадения имен ваших полей. Диапазон поля:

="'" & B2 & "'!$A:$A"

Пример вывода будет 'Essbase Data'!$A:$A Это будет использоваться для MATCH() желаемой строки.

Наконец, вы хотите определить диапазон всего набора данных. Диапазон данных:

="'" & B2 & "'!$A:$D"

Пример вывода будет 'Essbase Data'!$A:$D Это будет использоваться для INDEX() желаемого столбца и строки. Вы можете использовать COUNTA() и ADDRESS() для их динамического построения. т.е.

="'" & B2 & "'!$A$1:" & ADDRESS(COUNTA('Essbase Data'!A:A), COUNTA('Essbase Data'!1:1))

Пример вывода будет 'Essbase Data'!$A$1:$D$6

индексирование

Во-первых, вы захотите идентифицировать указанный вами столбец, используя диапазон дат в вашем примере. Колонка:

=MATCH(B1,INDIRECT(B3),0)

Пример вывода будет 4 . Где B1 - указанный заголовок, а B3 - указанный диапазон дат из таблицы Report выше.

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

=INDEX(
   INDIRECT($B$5), <-- Data range
   MATCH($A9, <-- Field lookup value
     INDIRECT($B$4), <-- Field range
     0
   ),
   $B$6 <-- Column number
 )

Перетащите вниз, и ваш вывод должен выглядеть так:

| Field Name   | Value        |
| Key 1        | C            |
| Key 2        | F            |
| Key 3        | I            |
| Key 4        | L            |
| Key 5        | O            |
| ...          | ...          |