☰

只有當 Excel 中每行的數字總和 >0 時，如何計算數組中的行數？

tag-icon • tag-icon microsoft-excel worksheet-function
只有當 Excel 中每行的數字總和 >0 時，如何計算數組中的行數？

我在 Excel 中有一個 150 列 x 360 行的數組，其中包含隨機數（例如 A2 到 ET361）。

如何計算每列（即從儲存格 B1 到 ET1）之前的列有多少行大於零？

標準：

B1 需要計算 >0 的儲存格數量（A2 到 A361）。
C1 需要計算行數（A2:B2、A3:B3，...，到 A361:B361），其中每行的總和 >0。
D1 需要計算行數（A2:C3，...，到 A361:C361），其中每行的總和 >0。

我嘗試使用 COUNTIF 公式，但它只傳回單元格數，而不傳回行數。
我想我需要一個嵌套的 ROWS() 和 IF() 公式？我也不想創建另一個 150 x 360 矩陣來處理這個問題，因為我想節省 Excel 檔案中的空間。

我也不想使用巨集和 VBA，因為它們使我的電子表格變得複雜。

我給整個方程式增加了複雜性，因此小計函數不起作用。

我需要矩陣中的每個單元格計算其上方的行數，其中每行的列總和大於零。巴里的解決方案在這種情況下不起作用（我已經測試過），因為“小計”公式不適用於具有“小計”公式的單元格。

我們還有其他選擇嗎？

答案1

雖然我無法想出一個單一的公式解決方案（也許其他人會！），但我確實想出了一些比另一個 150 x 360 矩陣佔用更少電子表格空間的方法。

基本概念是計算每一行中一列資料的累積總數，然後在資料表中使用該資料（「假設分析」）來產生所有列的計數。

起點是單一資料列中的行的計算列。

如下圖所示，我設定了一個包含 10 列資料的工作表。

輔助欄

在資料的右側，我設定了輔助列 L。

儲存格 L1 包含COUNTIF該列中總和大於零的行。

OFFSET對於行總和，我使用函數傳回的範圍的總和，而不是每行中的列的簡單總和（同樣，僅針對 A 列） 。該函數的形式為

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

單元格 L3 具有第一個表達式SUM(OFFSET(...))。它計算從儲存格 A2 向下 0 行、向右 0 列、高度為 1 行、寬度等於儲存格 L2 中的值的範圍的行總和。在這種情況下，L2 的值為 1。

公式向下複製 360 行，在每種情況下計算 1 行高和寬度由儲存格 L2 中的值決定的範圍的總和。

例如，如果 L2 中的值變更為 2，則該列中的公式將計算 360 行中每一行的 A 列和 B 列中的值的按行總和。儲存格 L1 將顯示 A2:B361 範圍內總和大於 0 的行數。

計算單一資料列的行數

數據表

Excel 的資料表功能可以快速確定改變計算的一個（或兩個）輸入值對計算的影響。它是透過功能區選項卡部分What-If Analysis中的按鈕進行設定的。Data ToolsData

附圖顯示了數據表設定。

資料表將在 R1:S10 範圍內建立。在表格的頂部，儲存格 S1 中是結果儲存格，其輸入將會變更。在這種情況下，結果儲存格會儲存公式，它只是對輔助列 L 頂部公式=L1的參考。COUNTIF

我在儲存格 R2:R10 中預先輸入了「假設」值。顯示的值 - 1、2、...、9 - 表示 OFFSET 將傳回的範圍的寬度。 「列輸入儲存格」是 cell L1，該儲存格決定在輔助列中求和的行的寬度。

簡而言之，我們輸入寬度 1-9（相當於列「A」、「A:B」、「A:C」等），資料表計算總和大於 0 的行數對於每個列跨度。

數據表設定

最後一張圖顯示了最終結果。資料表計算了輸入資料每列的行計數，即大於 0 的（前列）逐行總和的計數。我使用該函數將計數轉移到原始資料的第一行TRANSPOSE。

最終結果

包含所有計算的範例工作表可用這裡。

答案2

如果我正確理解您的要求，您希望頂行顯示每列的總數單一細胞其之前的所有列的值都 > 0。正確的？

如果是這樣，那麼使用CountIf符號$來鎖定參考就非常簡單了。

在儲存格 B1 中，輸入=CountIf($A2:A361,">0").單擊並拖曳到右側。此$符號鎖定A，以便它始終計算 A 列和目前列之間的所有內容。當您將其拖過時，公式將如下所示：

  • C1=Countif($A2:B361,">0")
  • D1：=Countif($A2:C361,">0")
  • E1:=Countif($A2:D361,">0")
  • ETC...

CountIf可以對整個範圍進行計數，您不必一次只選擇一個儲存格或公式。因此，透過這種方式使用它，您可以輕鬆計算當前列左側的每個儲存格。

答案3

OFFSET函數允許您分離出一個範圍內的各個行...然後您可以對每行求和SUBTOTAL並用 計算 >0 的行SUMPRODUCT，因此 B1 中複製的這個公式應該可以在沒有輔助單元的情況下完成這項工作

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

使用類似於所描述的技術這裡[這裡沒有過濾，但仍需要使用 SUBTOTAL 對 OFFSET 產生的每個範圍進行求和]

這會給你與 chuff 的解決方案相同的結果

相關內容