☰

Как повторять вычисления до тех пор, пока не будет достигнуто пороговое значение в Google Таблицах?

tag-icon • tag-icon spreadsheet google-spreadsheets
Как повторять вычисления до тех пор, пока не будет достигнуто пороговое значение в Google Таблицах?

У меня есть Google Sheet, который я использую для оценки финансовых целей. В этом листе у меня 8 ячеек. Первые две ячейки используются для ввода параметров, которые используются формулами в других ячейках.

Первая ячейка — это параметр, который генерирует результат в ячейке 7. Вторая ячейка — это порог, которого я хочу достичь. По сути, я хочу взять значение в ячейке 1 и увеличить его на 1, пока результат, видимый в ячейке 7, не пересечет значение в ячейке 2. Пример выглядит так:

  A                        B
1 Current Total            $10000
2 Target Amount            $1000
3 Current Amount           =DIVIDE(B1, 100)
4 Result A                 =PRODUCT(B3, 0.35)
5 Result B                 =PRODUCT(B3, 0.50)
6 Result C                 =PRODUCT(B3, 0.15)
7 TOTAL                    =SUM(B4:B6)
8 Total Needed             ?

Используя приведенный выше пример, я хочу создать формулу, которая увеличивает значение B1 на 1 до тех пор, пока значение в B7 не пересечет значение в B2. Я хочу поместить сумму, используемую для пересечения порога, в ячейку B8.

Я бы предпочел сделать эту повторную операцию "за кулисами", чтобы в ячейке B8 отображался только результат. Я не хочу показывать всю "работу". Есть ли способ сделать это в Google Sheet? Если да, то как?

решение1

Немного поздновато, но для тех, кого это может касаться, Total Neededбудет иметь ценность .

=(B1 * (B2/B3)) - B1

Я бы предпочел сделать это повторнооперация "за кулисами" так что только результат появится в ячейке B8. Я не хочу показывать всю "работу". Есть ли способ сделать это в Google Sheet? Если да, то как?

Это можно сделать с помощью Меню -> Расширения -> Скрипт Google Apps ->Добавить пользовательскую функцию. Но в любом случае вы и никто другой ничего не увидите из расчета, потому что он достаточно быстрый.

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

B3 составляет 1% от текущей суммы. Разделите, Target Amount by Current Amountчтобы получить 1% от целевой суммы. Это простая математика.

B8 = B2/B3

Умножьте результат на Текущую сумму. Это даст вам, сколько раз 1% от Целевой суммы имеет в Текущей сумме

B8 = (B2/B3) * B1

Далее вычтите Текущую сумму из результата. Это необходимо, поскольку нам нужно знать - сколько раз нам нужно добавить 1, после добавленного значения Текущей суммы,

пока результат в ячейке 7 не пересечет значение в ячейке 2

B8 = ((B2/B3) * B1) - B1

   A                  B
1 Current Total      10000
2 Target Amount       1000
3 Current Amount     = B1 / 100
4 Result A           = B3 * 0.35
5 Result B           = B3 * 0.50
6 Result C           = B3 * 0.15
7 TOTAL              = SUM(B4:B6)
8 Total Needed       = ((B2/B3) * B1) - B1

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

Исключить все значения, которые уже были рассчитаны.

Current Amountсоставляет 100% от текущего итога, B3 = B1/100. Так что подумайте, эта ячейка всегда будет содержать это значение, когда бы вы ни производили вычисления в этом случае - оно всегда будет равно =B1/100.

Results A, B and Cтакие же, как Текущая сумма. Они также являются частями Текущей суммы. Result Aсоставляет 35% от Текущей суммы... и так далее. Каждый из них всегда будет иметь одинаковое значение.

TOTALвсегда будет равна Текущей сумме, потому что она суммирует ее части. Это также жестко закодированное значение.

Теперь отделите известные свойства от остальных. Они все представляют одно и то же значение — части Текущего итога — и вам не нужно думать об их расчете, потому что они имеют жестко закодированные значения.

   A                        B
1 Current Total                              
2 Target Amount            __________________
3 Current Amount           = B1 / 100
4 Result A                 = B3 * 0.35
5 Result B                 = B3 * 0.50
6 Result C                 = B3 * 0.15
7 TOTAL                    = SUM(B4:B6)
8 Total Needed             __________________

Теперь осталось поработать со следующими значениями: Текущая сумма и Целевая сумма.


Используя приведенный выше пример, я хочу создать формулу, котораяувеличивает значение B1 на 1 до тех пор, пока значение в B7 не пересечет значение в B2Я хочу поместить сумму, использованную для пересечения порога, в ячейку B8.

Вариант 2 Увеличьте значение B1 на 1, только самостоятельно, вручную.

Проверьте с помощью D1 в столбце C, если C7=C2, результат заполнит C8, в противном случае вам придется увеличить или уменьшить D1:

   A                  B                 C                      D 
1 Current Total      10000             = B1+D1            --> 90000 <--
2 Target Amount      ____________      1000
3 Current Amount     = B1 / 100        = C1/100
4 Result A           = B3 * 0.35       = C3*0.35
5 Result B           = B3 * 0.50       = C3*0.5
6 Result C           = B3 * 0.15       = C3*0.15
7 TOTAL              = SUM(B4:B6)      = SUM(C4:C6)
8 Total Needed       ____________      = IF(C7=C2; D1; "Change D1")

Связанный контент