2

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

У меня есть лист, который представляет собой сводку других листов в книге, каждый лист компонента представляет месяц года. Мне нужно заполнить сводный лист, скопировав ячейки со ссылками на другие листы. Столбцы формулы должны отражать связанные столбцы из фиксированной строки на каждом листе, в то время как строка формулы должна отражать выбор листа. Названия листов основаны на названии месяца.

В рабочей книге по одному листу на каждый месяц года, январь, февраль и т.д. Каждый из этих листов идентичен, и я извлекаю значения из строки итоговых месяцев (строка 4) каждого месяца. Строка сводки содержит итоги месяцев для разных категорий учета в последовательных столбцах, начиная с столбца E.

Каждая строка сводного листа содержит итоговую строку из соответствующего месяца.

Другими словами, у меня есть это: ячейка E5 is =IF(Jan!E4>0,Jan!E4," ") и я хочу, чтобы ячейка под ней (E6) была =IF(Feb!E4>0,Feb!E4," ") .

Тогда ячейка 'F5' (справа от E5) будет =IF(Jan!F4>0,Jan!F4," ") .

Я прилагаю скриншоты сводной страницы и одного из ежемесячных листов.

Январь лист

Резюме лист

3 ответа3

0

При таком подходе требуется список сокращенных названий месяцев, которые я помещаю в ячейки с A1 по A12 листа с именем ListMo , например:

|    |  A  |
|---:|:---:|
|  1 | Jan |
|  2 | Feb |
|  3 | Mar |
|  4 | Apr |
|  5 | May |
|  6 | Jun |
|  7 | Jul |
|  8 | Aug |
|  9 | Sep |
| 10 | Oct |
| 11 | Nov |
| 12 | Dec |

Вот формула, которая войдет в вашу ячейку E7:

=IF(INDIRECT(INDEX(ListMo!$A$1:$A$12,ROW()-6,) & "!E" & COLUMN()-1)>0,INDIRECT(INDEX(ListMo!$A$1:$A$12,ROW()-6,) & "!E" & COLUMN()-1)," ")

объяснение

INDEX(ListMo!$A$1:$A$12,ROW()-6,)

Index позволяет выбрать один элемент из массива / списка. Row() просто возвращает номер строки, в которой находится ваша формула, а -6 - это смещение, необходимое для получения строки 7, равной первому элементу в списке, "Jan".

COLUMN()-1

Как и Row() , Column() вернет номер столбца, в котором находится ваша формула, а -1 - это смещение, необходимое для получения столбца E или 5, равного 4 в E4 .

Indirect позволяет вам поместить весь этот текст вместе с & а затем прочитать его как ссылку на ячейку.

В случае ячейки E7 это решило бы как это:

INDIRECT(INDEX(ListMo!$A$1:$A$12,1) & "!E" & 4) который тогда равнялся бы Jan!E4 .

Indirect формула должна быть сделана дважды, потому что ссылка на эту ячейку используется дважды в вашей формуле.

Все это позволяет вам перетаскивать формулу вправо и, тем не менее, перемещать ссылку на ячейку вниз, что не является поведением по умолчанию при перетаскивании формул в Excel. А перетаскивание формулы вниз теперь выбирает имя следующего листа, и имена листов не могут быть увеличены с помощью перетаскивания по умолчанию.

0

Ответ Рей Джуны объясняет, как это сделать с помощью справочной таблицы. Вот альтернативный подход для получения ссылочных адресов без таблицы поиска. Общая структура формулы аналогична. Во многом это продиктовано переводом местоположений ваших формул в ссылки на ячейки (обратите внимание, что переводы местоположений в моих формулах теперь могут немного отличаться от ссылок Рея, поскольку описание в вопросе немного изменилось).

  • Вы хотите, чтобы строка формулы 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") .
-1

Знак $ в адресе ячейки фиксирует ссылку на ячейку или строку. Например, если вы имеете в виду ссылку на ячейку в строке 4 и скопировать эту формулу, то вы можете сослаться на ячейку E $ 4. Когда это будет скопировано вниз по столбцу, это даст всегда E $ 4. Копирование по строке дает E $ 4, F $ 4, G $ 4.

Если вы строите что-либо со ссылкой на столбец месяцев (вне графика), вы можете сослаться на что-то вроде (IF $ A1 = E $ 4 ...). Это при копировании вниз изменит 1 чтобы указывать на A2, A3, ... который будет содержать месяцы. E4 будет продолжать действовать как тестовая переменная, но в следующем столбце (когда столбец копируется) это будет F $ 4, указывая на новый тест.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .