1

Что я имею:

n = 5
0, 0, 0, 0, 1000, 0, 0, 0, 0

Что я хочу:

0, 0, 0, 0, 200, 200, 200, 200, 200, 0, 0...

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

Я бы предпочел избегать использования VB, если это возможно.

EDIT1:

Гарантируете ли вы, что любое значение, с которым вы столкнетесь, будет поровну делиться на n? Нет, однако предположим, что если сумма <1, продолжайте

Вы гарантированы, что будет достаточно клеток для n делений? нет

Вы гарантировано, что будет только одно ненулевое значение? Нет, это будет кратно, если в верхнем ряду будет видно другое значение, а нижний ряд не завершит разбиение, нижний ряд добавит старые + новые разбиения

Будет ли ненулевое значение всегда находиться в одной и той же позиции? Нет может появиться в любой позиции, кроме первой

EDIT2:

Чтобы быть прозрачным, я пытаюсь равномерно распределить большие долларовые затраты на n лет, может быть, это будет лучшим примером:график ожидаемого ввода и ожидаемого результата

Обратите внимание, что:

  • Процесс расщепления входного значения должен происходить до тех пор, пока сумма соответствующих расщеплений не станет равной соответствующему входному значению.

  • Если другое входное значение будет видно, когда мы еще не закончим разбиение, строка «Выход» будет продолжать суммировать разбиения, пока не будет выполнено условие в первой точке для каждого входного значения.

|источник

3 ответа3

3

РЕДАКТИРОВАТЬ: Хотя предыдущая, излишне сложная, формула (см. Этот пересмотр) будет по-прежнему полезна, например, если потребуется применить скользящий масштабный коэффициент для применения к входам, я заменил его в этом ответе новым , проще, гораздо проще понять версию.

Вот решение не VBA, которое не требует вспомогательных строк / столбцов или дополнительных таблиц:

Снимок экрана рабочего листа

Array-введите следующую формулу в B3 и затем заполните ее справа:

{=SUM(IFERROR(INDEX($2:$2,N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1))))/$A$5,0))}

Объяснение:

Предварительно подтвержденный вариант формулы выглядит следующим образом:

{=
SUM(
  IFERROR(
    INDEX(
      ($2:$2),
      N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)))
    )/$A$5,
    0
  )
)}

Формула будет намного легче понять, если учесть, что для n = 5 второй аргумент первого INDEX() фактически эквивалентен:

COLUMN()-{0,1,2,3,4}


Он в основном работает путем генерации массива смещений переменной длины (на основе n, хранящегося в моем примере в A5 ) для доступа к предыдущему n-1 плюс текущие входные значения.

Пройдя по формуле в I3 следует прояснить вышесказанное:

  • (COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1) → {1,2,3,4,5}-1 → {0,1,2,3,4}
  • COLUMN()-{0,1,2,3,4} → {9}-{0,1,2,3,4} → {9,8,7,6,5}
  • N(IF(1,{9,8,7,6,5})) → N({9,8,7,6,5}) → {9,8,7,6,5}
  • (INDEX($2:$2,{9,8,7,6,5})/$A$5 → {100000,0,0,0,7}/5 → {20000,0,0,0,1.4}
  • SUM(IFERROR({20000,0,0,0,1.4},0)) → 20001.40

INDEX($2:$2,N(IF(1,expression))) является обязательным хаком * , чтобы заставить Excel возвращать массив для expression поскольку второй аргумент INDEX() оценивается в одно значение по умолчанию. Использование только INDEX($2:$2,expression) в I3 приведет к

INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)) → INDEX($2:$2,9-(COLUMN($A$3:$E$3)-1)) → INDEX($2:$2,9-(1-1)) → INDEX($2:$2,9) → 100000

поскольку внутри выражения, возвращающего одно значение, COLUMN(multi-cell-range) возвращает столбец первой ячейки диапазона.

Функция IFERROR() требуется в том случае, если формула существует в ячейке рядом с левой стороной листа, что приводит либо к доступу к заголовку строки, либо к попытке доступа к ячейке слева от столбца A

Заметки:

  • Предварительно подтвержденная формула действительно работает, если введена.
  • Квадратные скобки ($2:$2) в предварительно проверенной версии требуют, чтобы $2:$2 оставался на своей линии.

* Объяснение того, почему хак работает, придется подождать, пока я сам не пойму ;-)

|источник
1

Код:

Sub Divide(n As Integer)
Dim temp(), i As Integer, j As Integer, cnt As Integer, tmp

' Copy values from row to array
temp = Rows(Selection.Row).Value

' Search for the last cell. Use SpecialCells instead is the better variant.
For i = UBound(temp, 2) To LBound(temp, 2) Step -1
    If Not IsEmpty(temp(1, i)) Then
        cnt = i
        Exit For
    End If
Next i

' Perform main operation
For i = cnt To 1 Step -1
    If Rows(Selection.Row).Cells(1, i).Value <> 0 Then
        tmp = Rows(Selection.Row).Cells(1, i).Value
        Rows(Selection.Row).Cells(1, i).Delete Shift:=xlShiftToLeft
        For j = 1 To n
            Rows(Selection.Row).Cells(1, i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows(Selection.Row).Cells(1, i).Value = tmp / n
        Next
    End If
Next

End Sub

  • Sub принимает значение n в качестве параметра.

  • Sub обрабатывает строку с выделением (левый верхний угол) в.

  • Sub не проверяет, могут ли значения в ячейках быть преобразованы в число для сравнения с 0 и делиться на n или нет.

  • Другие ошибки тоже не проверяются.

  • Смещенные значения будут потеряны при перемещении за пределы столбцов.

  • И никакой оптимизации вообще.

Используйте этот код как идею.

|источник
1

Вот рабочее решение без необходимости использовать VBA.

Разница лишь в том, что вам нужно было бы где-нибудь добавить таблицу для ввода каждой стоимости в отдельности.

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

Желтые клетки представляют входные данные.

  1. В C2, назовите ячейку _N чтобы иметь простой способ получить доступ к переменной 5 years другом месте
  2. Для ваших расходов (диапазон A5:D10), вставьте таблицу, которую вы можете назвать Costs для облегчения ссылок

Вот формулы для использования и, при необходимости, для перетаскивания / копирования вправо (синим на скриншоте):

=IFERROR([@[Real Cost]]/_N;0)

=IF([@From]>0,[@From]+_N-1,"")

=SUMPRODUCT((B$13=Costs[From])*(Costs[Real Cost]))

=SUMPRODUCT((B$13>=Costs[From])*(B$13<=Costs[Until])*(Costs[Yearly Cost]))
|источник

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