У меня есть рабочий лист, в котором я хочу использовать формулу индекса/соответствия, чтобы извлечь точные значения для уникального пересечения значений отдела и счета на отдельном листе данных.
Однако мне нужно, чтобы массив индексов был гибким, поскольку я ищу одно и то же «уникальное» пересечение значений отделов и счетов в нескольких столбцах (столбцы представляют месяцы в моей таблице данных).
Я пытался использовать косвенную формулу, ссылаясь на диапазон значений в таблице данных, я также пробовал другой вариант косвенной формулы, называя свой диапазон - обе формулы не возвращают результаты, хотя я знаю, что значения существуют в моей точке пересечения. Если я жестко закодирую свой массив индексов, формула работает, и я получаю результаты для заполнения на моем рабочем листе.
Вот мои формулы на данный момент:
=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Где _2015_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.
Что я делаю не так?
Я пытаюсь автоматизировать обновление ссылки на массив, чтобы отчет можно было обновлять ежемесячно с минимальными ручными обновлениями индексного массива.
решение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 |
| ... | ... |