Применить любую формулу n раз без использования VBA?

Question

У меня есть следующие клетки:

A1:

justsometext

B1:

С1:

=DOSOMETHING(A1)

Я хочу применить формулу в C1 n раз (n равно 3, значение в B1), поэтому в этом случае это будет означать:

С1:

=DOSOMETHING(DOSOMETHING(DOSOMETHING(A1)))

Есть ли возможность сделать это без макроса, может быть, с помощью формул массива?

ОБНОВИТЬ:

Количество повторений не всегда будет 3, но будет меняться со временем и / или различаться от строки к строке.

Вот простой пример того, как это должно выглядеть:

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

score 5 · Answer 1 · 2011-03-09T19:38:42

Тогда я бы использовал = Value & REPT("_checked", NoOfExecutions) .

Если вам всегда нужно делать что-то похожее на пример (объединять строки), это работает довольно хорошо.

Если вам нужно использовать другие формулы, вот о чем я могу подумать:

мы разделяем функцию, которую вам нужно будет использовать: начало (все, что должно идти перед основным аргументом) и конец (все, что следует за аргументом, включая дополнительные аргументы). Например, если мы используем функцию LEFT(value, 2) , LEFT( перейдет в начало , 2) в конец.
мы строим формулу в виде текста с конкатенацией и REPT . Как показано в примере на рисунке, формула в ячейке C6 будет иметь вид: = "=" &REPT($B$2,B6) & $A6 & REPT($B$3,$B6)
Затем вам нужно скопировать ячейку и вставить ее как значения в ячейку D6; затем нажмите на формулу в строке формул и нажмите Enter на клавиатуре.

Это несколько шагов, но он избегает VBA.

score 4 · Answer 2 · 2011-03-09T19:38:42

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

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

Самое близкое к общему решению вы можете переставить ячейки, встроить формулу DOSOMETHING в специальную формулу-обертку и использовать вспомогательные столбцы.

Для вашего предоставленного примера листа:

Переставь это так:

Введите следующую формулу в D2 и ctrl-enter/copy-paste/fill-down & right/auto-fill в остальные столбцы таблицы:

=IF(COLUMN()-COLUMN($C2)>$A2,"§",C2&"_checked")

Введите следующую формулу в B2 и ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:

=INDEX(C2:INDEX(2:2,1,COLUMNS(2:2)),MATCH("§",C2:INDEX(2:2,1,COLUMNS(2:2)),0)-1)

Обратите внимание, что количество требуемых вспомогательных столбцов является максимально допустимым значением n плюс один. Если их недостаточно для введенного значения, возникает ошибка:

Объяснение:

Обобщенная формула оболочки для вспомогательных столбцов:

=IF(COLUMN()-COLUMN($C2)>$A2,"§",DOSOMETHING(C2))

где DOSOMETHING(C2) - любая формула, основанная только на C2 (например, LEFT(C2,LEN(C2)-1) которая постепенно удаляет последний символ).

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

Часть IF(COLUMN()-COLUMN($C2)>$A2,"§", использует индексы столбцов для обратного отсчета числа раз, когда формула DOSOMETHING вкладывается, и как только число раз, указанное в столбце A Достигнуто это выводит строки терминатора. Эти строки не обязательно должны быть § . Они просто должны быть чем-то, что никогда не будет результатом оценки любого числа вложенных формул для любого допустимого Value .

Формула Result выглядит сложнее. Однако части C2:INDEX(2:2, 1, COLUMNS(2:2)) являются просто поддиапазоном строки 2 справа от столбца Result .

Таким образом, формула по существу такая же, как:

=INDEX(2:2,MATCH("§",2:2,0)-1)

что облегчает понимание.

(Обратите внимание, что эта формула действительно работает, если включены итеративные вычисления.)

Глядя на эту более простую формулу, становится ясно, что формула возвращает результат вложенной функции DOSOMETHING n-уровня.

score 0 · Answer 3 · 2011-03-09T19:38:42

Чтобы применить формулу в ячейке `C1 'n Число раз, вам нужно применить итерацию.

Как это устроено:

Нажмите Файл, Опция, затем Формула.
Установите флажок «Включить итеративный расчет» и просто отметьте его.
Для максимальных итераций запишите значение, например 5.
Запишите эту формулу в ячейке C1

=B1+C1

Вы обнаружите, что Excel рассчитывает формулу в C1 пять раз.

Вы можете установить новое значение столько раз, сколько вам нужно, выполнив шаги с 1 to 3.

score 0 · Answer 4 · 2011-03-09T19:38:42

Это можно сделать с помощью функций Evaluate и Rept.

Оценка может быть вызвана только через менеджер имен, см.
оценить функцию. Evalute оценивает строку как формулу, поэтому все, что можно построить как строку, можно использовать в качестве формулы.

Нажмите Ctrl+F3, нажмите Новый ...
В поле «Имя» назовите свою функцию (например, «Повторитель»).
В поле «Ссылка» напишите свою формулу, используя Rept: = Evaluate (rept («sin (», b2) & a2 & rept («)», b2))
и в своей ячейке вы используете = Repeater и указываете количество повторений в B2, а параметр в A2

Это немного сложно, поэтому пользовательская формула в VBA может быть проще

score 0 · Answer 5 · 2011-03-09T19:38:42

Это может показаться немного сумасшедшим, но это хак, который может помочь.

Предположим, что в столбце A содержатся все текстовые значения, а в столбце B - необходимое количество итераций. Также предположим, что существует максимальное значение для столбца B, например 4, для пояснения. Если вы не можете установить максимальное значение для столбца B, это не сработает.

Сначала добавьте строку вверху с номерами 1 - 4 вверху столбцов C, D, E, F. В ячейку C2 поместите формулу, подобную этой: =IF(C$1=$B2,DOSOMETHING(A2),"") (для последней части этой формулы укажите ,"") если вы работаете со строками, и ,0) если вы работаете с числами).

Затем в D2 положим =IF(C$1=$B2,DOSOMETHING(DOSOMETHING(A2)),"") . И так далее для E2 и F2. Теперь, если вы скопируете ячейки C2 - F2 и вставите их в столбцы C - F, вы получите эту странную матрицу, где все пусто (или ноль), за исключением столбцов, где у вас есть желаемое значение на основе количества итераций в колонке Б.

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

Как это:

score 0 · Answer 6 · 2011-03-09T19:38:42

Я думаю, что это возможно в общем случае (с рекурсией)!

По сути, нам нужно настроить систему, в которой ячейка счетчика (со ссылкой на себя) будет подсчитывать количество вызовов функции, и каждый раз, когда число меняется, функция запускается снова.

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

Первый шаг - сделать счетчик - столбец Current State содержит

=IFS(
    resetSwitch,
    [@[Counter Initial State]],
    [@[Current State]] >= [@[Count To]],
    [@[Current State]],
    TRUE,
    [@[Current State]] + 1
)

Который читает

Если resetSwitch имеет значение True, перейдите в исходное состояние, в противном случае продолжайте увеличивать значение этой ячейки, пока мы не достигнем конечного значения ([@[Count To]])

Теперь, чтобы определить, следует ли применять формулу (в столбце Рекурсор), нам нужно знать, увеличивается ли счетчик (Текущее состояние <Количество к), и если мы применяем формулу. Один из способов сделать это - получить предыдущее состояние счетчика, и, если оно отличается от текущего, счетчик растет, если не счетчик достиг своего окончательного значения.

Столбец « Previous State содержит

=IFS(
    [@[Current State]] = [@[Counter Initial State]],
    [@[Counter Initial State]],
    [@[Current State]] < [@[Count To]],
    [@[Current State]],
    AND(
        [@[Count To]] = [@[Current State]],
        [@[Previous State]] < [@[Current State]] - 1
    ),
    [@[Previous State]] + 1,
    TRUE,
    [@[Count To]]
)

Который читает:

Если счетчик находится в исходном состоянии, он должен был быть ранее. Если счетчик в настоящее время меньше, чем его окончательное значение, его предыдущее состояние было на единицу меньше, чем его текущее. Если счетчик находится в своем конечном состоянии, а текущее значение для предыдущего состояния - нет, то предыдущее состояние все еще должно увеличиваться. Если текущее значение предыдущего состояния является конечным значением, то счетчик стабилен в своем конечном состоянии.

Я думаю, что это требует мышления, отличного от Excel, но думаю, что это работает. По существу, Previous State - это одна итерация за Current State (как следует из названия)

Наконец, нам нужно применять формулу всякий раз, когда предыдущее состояние <> текущего состояния счетчика (счетчик в настоящее время увеличивается). Это дает формулу в Recursor

=IFS(
    resetSwitch,
    [@[Reursor Initial State]],
    [@[Previous State]] < [@[Current State]],
    [@Recursor] & "_checked",
    TRUE,
    [@Recursor]
)

Где здесь формула, которая будет применяться n раз - это [@Recursor] & "_checked" , но может быть любым f([@Recursor]) .

Установка resetSwitch в FALSE заставляет все работать, в результате чего

Сейчас выбран русский

Применить любую формулу n раз без использования VBA?

6 ответов6

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

Применить любую формулу n раз без использования VBA?

6 ответов6

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

Похожие