Ответ Рей Джуны объясняет, как это сделать с помощью справочной таблицы. Вот альтернативный подход для получения ссылочных адресов без таблицы поиска. Общая структура формулы аналогична. Во многом это продиктовано переводом местоположений ваших формул в ссылки на ячейки (обратите внимание, что переводы местоположений в моих формулах теперь могут немного отличаться от ссылок Рея, поскольку описание в вопросе немного изменилось).
- Вы хотите, чтобы строка формулы 5 ссылалась на январь (месяц 1), поэтому нам нужно вычесть 4 из строки формулы.
- Вы хотите получить данные столбец за столбцом, начиная со столбца E, но всегда из строки 4 целевого листа.
Описание в вопросе изменилось, поэтому я буду использовать немного другой метод с INDIRECT. INDIRECT имеет функцию, которая позволяет ссылаться на ячейки в так называемом формате R1C1, что удобно для такого рода требований. Вы можете легко указывать номера строк и столбцов и выполнять относительную адресацию.
INDIRECT имеет необязательный последний параметр, используемый для указания стиля ссылок на ячейки. Если это ЛОЖЬ или 0 , это указывает на адресацию стиля R1C1. В строке R4C[0] ссылается на строку 4 и тот же столбец, что и формула (смещение нуля).
В противном случае главное отличие заключается в том, как получить имена листов с помощью формулы вместо поиска.
Ключом к этому является эта формула:
TEXT((ROW()-4)*28,"mmm")
Строка минус 4 пояснялась выше, переводя местоположение формулы в номер месяца. Нам нужно превратить номер месяца в дату, которая попадает в этот месяц (который может быть в любом году, нам просто нужен день года). Умножение на 28 делает это. Все месяцы, кроме февраля, в течение не високосного года имеют более 28 дней, но этого достаточно, чтобы гарантировать, что номер полученного дня будет в правильном месяце.
(Обратите внимание, что этот трюк работает для перевода 1-12 на январь-декабрь, но его нужно настроить, если ваш начальный месяц отличается от января или у вас несколько последовательных лет; вы не можете просто настроить смещение строки. См. Приложение ниже.)
Функция TEXT форматирует результат как трехбуквенное сокращение месяца.
Собирая это вместе, фактическая формула:
=IF(INDIRECT(TEXT((ROW()-4)*28,"mmm")&"!R4C[0]",0)>0,INDIRECT(TEXT((ROW()-4)*28,"mmm")&"!R4C[0]",0),"")
Вы можете скопировать и вставить эту формулу в ячейку E5, а затем просто скопировать и вставить или перетащить, чтобы заполнить матрицу. Вам не нужно корректировать формулу, если макет рабочего листа не изменится.
Приложение: Если ваши месяцы отличаются от января-декабря (например, финансовые годы), и / или у вас есть несколько последовательных лет, вот еще один прием, чтобы использовать приведенное выше преобразование числа месяца в сокращение месяца.
- Выполните настройку первой строки, чтобы преобразовать номер строки в номер начального месяца. Скажем, ваша первая строка формулы - 5, а ваш начальный месяц - октябрь, поэтому вы должны использовать ROW()+5.
- Оберните это с помощью функции MOD, чтобы оставить остаток после деления на 12 (декабрь получится нулевым, но это все еще работает):
MOD(ROW()+5,12) . В результате каждая строка указывает на правильный номер месяца.
- Используйте это в функции ТЕКСТ:
TEXT(MOD(ROW()+5,12)*28,"mmm") .
