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

1071
stpn

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

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

 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)
0

2 ответа на вопрос

1
Angel

With the ranges named as you say write in G2 the next draggable formula:

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

And of course you would be able to write something like

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

but that formula wouldn't be draggable.

(Note $A$1:$F$4 is your dataset reference)

Hope it works!

1
CallumDA

Here is a VBA solution. Using macros for something that can be done with worksheet formulas isn't the best practice, However, it gives you the functionality you want. Create a module in your workbook and add this code to the module. Don't forget to save your file as .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

My recommendation is to use a named range for your data array, such that this line:

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

Becomes:

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

And can be edited from the workbook.

You might also want to change the formulate name from "SOMEFORMULA()" to something smaller/ more appropriate.

The results are as follows:

enter image description here

Я только что попробовал, но когда я копирую = SOMEFORMULA ("car", "feb") в следующую ячейку, например sum2 - я все равно получаю = SOMEFORMULA ("car", "feb"), а не = SOMEFORMULA ("car") , "мар"). То есть он не будет автоматически увеличиваться до имени следующего столбца? stpn 9 лет назад 0
Нет, но вы можете использовать ссылки на ячейки, такие как `= SOMEFORMULA ($ A2, C $ 1)`, чтобы заменить `= SOMEFORMULA (" car "," mar ")`, после чего вы можете перетаскивать его вдоль или вниз. Вы обнаружите, что если вы хотите перетаскивать формулу, вам всегда нужны ссылки на ячейки, и в этот момент вы теряете желаемую читаемость. CallumDA 9 лет назад 0

Похожие вопросы