☰

需要一個 Excel 公式，用於從列中最舊的值中減去最新的 5 個值

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
需要一個 Excel 公式，用於從列中最舊的值中減去最新的 5 個值

我是一名體能教練，需要一些幫助來分析我的運動員評估結果。我需要幫助編寫一個公式，用最新的結果減去 5 個測試結果中最舊的結果。表格有近 3000 行，每位運動員每人有 5 行（第一學期、第二學期、第三學期、第四學期和夏季），無論他們實際參與上述測試如何。因此，公式基本上需要沿著列向下，從 5 個儲存格中取出第一個可用值，然後從 5 個儲存格中的最後一個可用值中減去它。該表設定如下：

螢幕截圖

請幫忙！如果這沒有提供足夠的信息，我很抱歉，我是該網站的新手。

答案1

我有一個公式，當您將其放入任何標題為“更改”的灰色行時，該公式就會起作用

這是一個 doozie，它看起來像這樣：

=INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), ROWS(Values)) - ROW(StartValue) + 1,1)- INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), COUNTIF(Values, "")+1) - ROW(StartValue)+1,1)

在哪裡

  • Values是值的範圍，在我的範例中是H5:H9
  • StartValue是值範圍中的第一個單元格，對我來說這是H5

怎麼運作的

  • 該語句INDEX((Values<>0)*ROW(Values), 0)傳回一個數組，0當單元格為空時傳回該數組，當單元格不為空時傳回該單元格的行號
  • 該SMALL()函數按升序對它們進行排序，然後傳回數組中與其第二個參數對應的值

因此，在下面的範例電子表格中，INDEX((Values<>0)*ROW(Values), 0)返回

0
6
7
8
0

SMALL()將其排序為如下所示：

0
0
6  <- Row of first nonzero value, second argument to SMALL(): =COUNTIF(Values, "")+1 = 3
7
8  <- Row of last nonzero value, second argument to SMALL(): ROWS(Values) = 5
  • 現在我們知道了行，我們可以用來INDEX()取得值。該行- ROW(StartValue) + 1將由 傳回的行轉換SMALL()為索引
  • 因此，我們留下了第一次呼叫中最近的非零值，INDEX()並從第二次呼叫中減去最遠的非零值INDEX()

螢幕截圖

計算更改行

連結到電子表格

這裡是下載此範例電子表格的連結。我通常使用 FormulaChop 來解釋這些公式，但它不適用於 ROW() 函數。 （全面披露：我寫了 FormulaChop）。因此，我包含了一個有效的公式版本，您可以在 FormulaChop 標籤上看到它的分解。

答案2

使用 A 列中的日期 (A2:A3001)、B 列中的名稱和 C 列中的值，將要尋找的名稱放入 F2 中，並在 G2 中使用此公式。

=SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(14, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))-
 SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(15, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))

答案3

希望這就是您正在尋找的東西？

[ 圖片1]

在圖中，我們有兩個使用者。黃色資料是您工作表中的資料（我認為有很多使用者），其他欄位可以使用簡單的公式來評估這些資料。

使用的公式如下圖所示

答案4

這可能就是您正在尋找的 顯示結果的圖片

公式是這樣的，使用起來非常簡單 顯示公式的圖片

如何建立公式？按 Alt+F11 並複製貼上以下內容

公用函數 Diff2(n1 作為雙精度型，n2 作為雙精度型，n3 作為雙精度型，n4 作為雙精度型，n5 作為雙精度型)

索引 1 = 0 索引 2 = 0

'找出第一個數字If n1 > 0 then FirstNum = n1 index1 = 1 ElseIf n2 > 0 then FirstNum = n2 index1 = 1 ElseIf n3 > 0 then FirstNum = n3 index1 = 3 ElseIf n4 > 0 then FirstNum = n4 index1 = 44 index1 = 44 index1 = 44 index1 = 44 index1 = 44 index1 = End If

'找第二個數字If n5 > 0 then SecNum = n5 index2 = 5 ElseIf n4 > 0 then SecNum = n4 index2 = 4 ElseIf n3 > 0 then SecNum = n3 index2 = 3 ElseIf n2 > 0 then SecNum = n2 End If

'錯誤檢查 If (index1 = 0) Or (index2 = 0) Or (index1 - index2 = 0) then Diff2 = "ERROR" Else Diff2 = SecNum - FirstNum End If

結束功能

圖片顯示 Excel VBA

相關內容