將儲存格內容重複劃分為「n」個相等的值並重新指派到連續的儲存格中

2024-7-19 • 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) 時，底行將均勻地將該值拆分n次，直到所有分割的總和等於原始值。

如果可能的話，我寧願避免使用 VB。

編輯1：

你能保證你遇到的任何值都能被n整除嗎？

否。

你能保證有足夠的細胞進行n次分裂嗎？

不。

你能保證只有一個非零值嗎？

不，會有多個值。如果在頂行中看到另一個值且底行尚未完成拆分，則底行會將新舊拆分加在一起。

非零值會始終處於相同位置嗎？

不可以。

編輯2：

為了透明起見，我試著將巨額成本平均分攤到n年。

也許這會是一個更好的例子：預期輸入和預期輸出的時間表

注意：

分割輸入值的過程必須發生，直到各個分割的總和等於輸入值。
如果在我們尚未完成分割前一個輸入值時看到另一個輸入值，則輸出行將繼續對各個分割求和，直到每個輸入值滿足第一點中的條件。

答案1

編輯：雖然之前的公式不必要複雜（參見本次修訂）仍然有用，例如，如果需要將滑動縮放因子應用於輸入，我已在這個答案中將其替換為新的、更簡單、更容易理解的版本。

這是一個非 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)))是必需的 hack ^*以便強制 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值作為參數。
Sub 處理包含所選內容（左上角）的行。
Sub 不檢查儲存格中的值是否可以轉換為數字以進行比較0和除法n。
其他錯誤也沒有檢查。
當移動超過列限制時，移動的值將會遺失。
而且根本沒有優化。

使用此程式碼作為一個想法。

答案3

這是一個無需使用 VBA 的可行解決方案。

唯一的區別是您需要在某處添加一個表來單獨輸入每個成本。

我強烈鼓勵您這樣做，因為它大大簡化了計算，並且從財務角度講也增加了很多清晰度：您可以輕鬆添加成本參考、描述、伺服器類型等列。有多個商品）。您可以擴展它並產生有趣的報告，而且在我看來，它比編寫 VBA 容易得多。

黃色單元格代表輸入。

在 C2 中，為單元格命名以便可以輕鬆地在其他地方_N存取變數5 years
對於您的成本（範圍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]))

相關內容