Автоинкрементные ячейки для именованных строк и именованных столбцов

Question

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

У меня есть эта таблица:

  A   B   C   D   E   F   G   H
1     jan feb mar apr may sum sum2
2 car 1   2   3   4   5   
3 bot 3   4   5   6   7
4 top 10  20  30  40  50

Таким образом, "автомобиль" - это именованный диапазон B2:F2, а "jan" - это названный диапазон для B2:B4.

В идеальном мире я вижу себя так:

G2 = car jan

Затем, когда я копирую и вставляю приведенную выше формулу в H2, Excel автоматически увеличивает "jan" до "feb", и я получаю

G3 = car feb

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

Если это абсолютно невозможно, я был бы рад, по крайней мере, иметь возможность ссылаться на ячейки по имени строки и букве столбца. Так что я могу сделать:

G2 = SOMEFORMULA(car, C)

Answer 1

С именованными диапазонами, как вы говорите, напишите в G2 следующую перетаскиваемую формулу:

=INDEX($A$1:$F$4,ROW(INDIRECT($A2)),COLUMN(INDIRECT(B$1)))

И, конечно, вы сможете написать что-то вроде

=INDEX($A$1:$F$4,ROW(car),COLUMN(feb))

но эта формула не будет перетаскиваемой.

(Обратите внимание, что $ A $ 1:$ F $ 4 - ваша ссылка на набор данных)

Надеюсь, что это работает!

Answer 2

Вот решение VBA. Использование макросов для чего-то, что может быть сделано с формулами рабочего листа, не является наилучшей практикой, однако дает вам необходимую функциональность. Создайте модуль в своей книге и добавьте этот код в модуль. Не забудьте сохранить ваш файл как .xlsm

Function SOMEFORMULA(item As Variant, month As Variant)

Dim rng As Range

'set your lookup range
Set rng = Worksheets("Sheet1").Range("A1:F4")

SOMEFORMULA = Application.WorksheetFunction.Index(rng, WorksheetFunction.Match(item, WorksheetFunction.Index(rng, 0, 1), 0), WorksheetFunction.Match(month, WorksheetFunction.Index(rng, 1, 0), 0))

End Function

Я рекомендую использовать именованный диапазон для вашего массива данных, такой, чтобы эта строка:

Set rng = Worksheets("Sheet1").Range("A1:F4")

становится:

Set rng = Worksheets("Sheet1").Range("named_range")

И может быть отредактировано из рабочей книги.

Вы также можете изменить имя формулы с «SOMEFORMULA ()» на что-то меньшее / более подходящее.

Результаты приведены ниже: