Excel 2016: формула для получения данных из другой электронной таблицы

Question

помощь с этой сложной формулой очень ценится.

  ='[DATA.xlsm]2018'!$G$6   *this formula works, but . . .

Электронная таблица с формулой Электронная таблица с данными

Мне нужна формула, чтобы определить, где найти данные. Смотрите изображение. Формула в столбце ЗНАЧЕНИЕ В. Информация QTR в столбце А указывает, где искать данные на изображении.

Четырьмя совпадающими полями QTR являются D1, I1, N1, S1. Тогда значение OFFSET равно 3 столбцам (например, D1 = 1Q18, столбец G - столбец данных, столбец L для 2Q18 и т.д.), А строка OFFSET равна 5 (имеется в виду строка 6). {Надеюсь, смещение (5,3) правильно}

В строке 10 мне нужна формула в B10, чтобы прочитать 1Q18 в A10 и {знать}, чтобы перейти к «[DATA.xlsx] 2018» и найти, что "1Q18" - это D1, а затем использовать Смещение (5,3), чтобы найти значение "1,80"

Когда мы дойдем до строки 14 на листе FORMULA, он увидит "1Q19" в A14 и {знает}, чтобы перейти к «[DATA.xlsx] 2019». Каждый год имеет новую вкладку, названную четырехзначным годом.

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

Answer 1

Вы довольно близко, вам нужно OFFSET. Но вы также должны MATCH квартал вы , глядя вверх. Я разделил формулу на 2 ячейки, чтобы вы могли видеть ее по частям, но как только вы ее поняли, вы легко можете объединить их.

Я подделал поддельный набор данных (я использовал лист с именем "Данные" в той же книге): ,

Тогда я назвал другой лист "Отчет". В отчете вы сначала хотите MATCH квартал, в котором вы ищите позицию в вашем листе данных. MATCH имеет определение:

MATCH(lookup_value, lookup_array, [match_type])

Таким образом, мы устанавливаем lookup_value на четверть, которую вы ищете, lookup_array на набор данных, а match_type на 0 (точное совпадение). Обратите внимание, что это работает только для одного массива (строки или столбца), вы не можете предоставить 2D сетку ячеек. Это даст нам относительную позицию в массиве, 1 проиндексирован (первый элемент возвращает 1, а не 0).

Затем мы используем эту информацию с OFFSET . Определение для смещения это:

OFFSET(ссылка, строки, столбцы, [высота], [ширина])

В этом случае ссылка - это отдельная ячейка. Вам не нужно предоставлять массив или сетку ячеек. Так что в моем случае я передаю Data!$B$1 . Для rows нам нужно сместить 2 строки вниз. Количество столбцов по хитрее. Поскольку функция MATCH в качестве контрольной позиции, которая начинается с 1, мы уже переходим ко второму месяцу. Таким образом, для cols нам нужны результаты MATCH плюс 2 .

Итак, наконец мы получаем отчетный лист, который выглядит примерно так: