Скриптовый листинг в Excel

Question

Можно ли обновить номер строки на первом листе из тех же ячеек в остальной части рабочей книги?

У меня есть индексный лист (Index) со ссылками на 53 других листа в течение недель, от Wk 1 до Wk 53 недель. Мне нужно извлечь данные из Index:A2 до Wk1:C19 , Index:A3 до Wk2:C19 , Index:A4 до Wk3:C19 и т.д.

Я пробовал вариации на Index!$A(wk1!sheet()) чтобы вернуть ссылку на строку без успеха.

Единственная вещь, которая, кажется, работает, это написать абсолютную ссылку в каждом C19 на всех 53 листах Wk .

Есть лучший способ сделать это?

Answer 1

Вам нужно использовать INDIRECT() для ссылки на меняющееся местоположение ячейки и CELL("имя файла"), чтобы получить имя рабочей таблицы, чтобы вы знали, на какую неделю / строку должна ссылаться ячейка.

=INDIRECT("Index!A"
    &RIGHT(
        CELL("filename")
        ,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2)
    +1)

Давайте разберем это:

1. Давайте определим рабочий лист, на котором вы работаете каждую неделю. Перейдите в ячейку C19 на вкладке Wk1. Enter =CELL("filename") . На этом этапе необходимо сначала сохранить книгу, в противном случае Excel не сможет распознать имя файла.

   Вы должны получить что-то вроде этого:

   C:\Users\yourname\Documents\[yourworkbook.xlsx]Wk1

2. Теперь нам нужно сократить большую часть этого, чтобы мы сохраняли только соответствующий бит информации, то есть номер недели. Поскольку номер недели всегда является последней частью строки, мы можем использовать функцию RIGHT() чтобы получить ее.

   Если вы просто введете =RIGHT(CELL("filename"),1) вы получите неделю номер 1, но это работает, только если у вас меньше 10 недель. Поэтому нам нужно сделать функцию гибкой и определить, сколько цифр мы хотим сохранить. 1 или 2?

   Мы можем использовать LEN() для определения общего количества символов в CELL("filename") . Затем мы можем определить количество символов только в имени листа, вычитая длину строки до ] из общей длины, например, так:

   =LEN(CELL("filename"))-SEARCH("]",CELL("filename"))

   Это дает вам значение 3 или 4, но мы не хотим включать два символа, составляющие "Wk" часть имени листа, поэтому давайте вычтем 2, чтобы мы также удалили "Wk":

   =LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2

   Итак, добавив это к нашей функции RIGHT() мы имеем:

   RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2))

   Последнее - на вашем индексном листе неделя 1 начинается со строки 2, поэтому давайте добавим 1, чтобы убедиться, что мы выводим правильный номер строки:

   RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2))+1

3. INDIRECT() позволяет нам использовать вычисления в ссылке на ячейку. Мы добавляем наш номер строки к статической ссылке на «Индекс!A ", объединяя два с помощью & .

Собрав все воедино, это:

=INDIRECT("Index!A"
    &RIGHT(
        CELL("filename")
        ,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2)
    +1)