☰

Разделить содержимое ячеек на n одинаковых значений несколько раз и распределить по последовательным ячейкам

tag-icon • tag-icon microsoft-excel worksheet-function
Разделить содержимое ячеек на n одинаковых значений несколько раз и распределить по последовательным ячейкам

Что у меня есть:

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

Что я хочу:

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

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

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

ПРАВКА1:

Гарантируете ли вы, что любое встретившееся вам значение будет делиться на n без остатка?

Нет. Однако, как только разница между суммой и исходным значением станет < 1, продолжайте.

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

Нет.

Гарантируете ли вы, что будет только одно ненулевое значение?

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

Будет ли ненулевое значение всегда находиться в одном и том же месте?

Нет. Он может появиться в любой позиции, кроме первой.

ПРАВКА2:

Чтобы быть честным, я пытаюсь равномерно распределить большие расходы понгоды.

Возможно, это будет лучшим примером: Хронология ожидаемого ввода и ожидаемого вывода

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

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

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

решение1

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

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

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

Массив — введите следующую формулу 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()по умолчанию оценивается как одно значение. Использование just in приведет к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оставаться на своей строке.

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

решение2

Код:

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значение в качестве параметра.

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

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

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

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

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

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

решение3

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

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

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

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

введите описание изображения здесь

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

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