Повторение вложенных функций в Excel

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

У меня есть следующие три ряда:
Ряд 91: 10|Пшеница|60,00|0,00
Ряд 92: 11|Банан|91,20|1,00
Ряд 93: 12|Молоко |200,00|182,00

Где | представляет собой разделение между каждым столбцом, названным по букве (как это делает Excel по умолчанию). Таким образом, значение ячейки A92 будет равно 11. Теперь столбец D создается с использованием следующей формулы: =((C92/100)*D11), в основном берется значение ячейки C92 (91,20), делится на 100, а затем умножается на любое значение, хранящееся в ячейка D11 (тот же столбец, что и тот, в котором я генерирую значение, но с использованием значения из столбца A для справки о том, на какое значение умножать).

Теперь, видя, что мне нужно использовать эту формулу во многих строках, я хочу в основном сделать что-то вроде этого: =VALUE(=CONCATENATE("D"; ROW();)/100)*CELL("address")), но это недопустимая формула. Чтобы выразить это словами:

  1. Получить значение ячейки D+текущий номер строки (например, D9 в строке 9 и т. д.)
  2. Разделите это на 100
  3. Умножьте это на значение текущей буквы столбца + номер текущей строки (E69 в ячейке E69 и т. д.)

Я надеюсь, что у кого-то есть идея, как этого добиться.


excel excel-formula
person Neil    schedule 18.10.2011    source источник
comment
Вы не можете выполнить условие 3 с помощью формулы, поскольку она требует циклической ссылки.   -  person Excellll    schedule 19.10.2011
comment
Вы можете использовать ДВССЫЛ() для решения #3. Вы также можете использовать его для # 1, но это кажется ненужным: просто перетащите формулу вниз, и Excel автоматически скорректирует ссылку. Однако вы должны знать, что использование ДВССЫЛ может вызвать проблемы с пересчетом: ваши ячейки, на которые косвенно ссылаются, не будут помечены Excel как прецеденты, поэтому, если они изменятся, ваша формула может не быть пересчитана.   -  person Tim Williams    schedule 19.10.2011
comment
что не так с =($C92/100)*$D$11 вместо =((C92/100)*D11). $ привязывают те части ссылок, которые не изменяются при копировании формулы.   -  person chris neilsen    schedule 19.10.2011
comment
@Time с использованием INDIRECT делает формулу Volitile, что означает, что она вычисляется каждый раз, когда лист пересчитывается.   -  person chris neilsen    schedule 19.10.2011

Ответы (2)


arrow_upward
0
arrow_downward

Вы можете сделать это с помощью OFFSET, но вы вполне можете получить циклические ссылки или ошибки, если ваши данные не являются чистыми.
=(C92/100)*OFFSET(D92,$A92-ROW(),0,1,1)
OFFSET говорит: Получите значение диапазона 1 строки 1 столбца, которое равно ( значение в A92 - номер текущей строки) строки со смещением от этой строки, но в текущем столбце), который для ваших данных равен D11

person Charles Williams    schedule 19.10.2011
comment
Я немного подправил ваше решение, чтобы получить: =($C92/100)*OFFSET(D92;OFFSET($A92;0;0;)-ROW();0;1;1) что генерирует 200,64 в ячейке D92 (для значения 220 в ячейке D11 это дает (91,2/100)*220, что равно 200,64). Также принял ваш ответ как наиболее близкий к решению, которое я искал. - person Neil; 19.10.2011
comment
Не думайте, что вам нужен OFFSET ($ A92; 0; 0), это то же самое, что и просто $ A92. - person Charles Williams; 19.10.2011

arrow_upward
0
arrow_downward

Я думаю, что это должно сделать вашу работу:

Sub Test()
    Cells(ActiveCell.Row, "D").FormulaR1C1 = "=RC" & ActiveCell.Column & "*RC3/100"
End Sub
person Marco    schedule 18.10.2011
comment
@Neil: это макрос, который вы можете сохранить в файле Excel и вызывать, когда захотите. Если вы назначите ему ярлык, эта операция будет выполняться быстрее. - person Marco; 19.10.2011
comment
Это возможно только с помощью макроса? Я в некоторой степени зависим от поддержания совместимости и очень предпочел бы скомпилировать ее в формулу, которую я мог бы воспроизвести на других языках кода. Кроме того, использование/производительность памяти не является проблемой, поскольку всего строк не так много. - person Neil; 19.10.2011