☰

列内の5つの値のうち最新のものを最も古いものから減算するExcelの数式が必要です

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
列内の5つの値のうち最新のものを最も古いものから減算するExcelの数式が必要です

私は筋力とコンディショニングのコーチで、アスリートの評価結果を分析するのに助けが必要です。5 つのテスト結果のうち最も古いものを最新のものから減算する数式の作成に助けが必要です。シートには約 3000 行あり、各アスリートにはそれぞれ 5 行 (第 1 学期、第 2 学期、第 3 学期、第 4 学期、夏期) があり、実際のテストへの参加に関係なく割り当てられます。したがって、数式は基本的に列を下って 5 つのセルから最初の利用可能な値を取得し、5 つのセルから最後の利用可能な値から減算する必要があります。シートは次のように設定されています。

スクリーンショット

助けてください！情報が足りなかったらごめんなさい。このサイトは初めてです。

答え1

「CHANGE」というタイトルの灰色の行に入力すると機能する数式があります。

それはすごいですね、こんな感じです:

=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()関数はそれらを昇順に並べ替え、2番目の引数に対応する配列の値を返します。

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()、2番目の呼び出しからの最も遠い非ゼロ値が減算されます。INDEX()

スクリーンショット

CHANGE行を計算する

スプレッドシートへのリンク

ここは、このサンプル スプレッドシートをダウンロードするためのリンクです。私は通常、これらの数式を説明するために 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]

図には 2 人のユーザーがいます。黄色のデータはシートにあるデータ (多くのユーザーがいると思います) で、他の列は簡単な数式を使用してこれらを評価するためにあります。

使用した式は下の図の通りです

答え4

これはあなたが探しているものかもしれません 結果がどのようなものかを示す画像

式は次のようになります。使い方は非常に簡単です。 数式を示す画像

数式を作成するには？Alt+F11を押して、次のコードをコピーして貼り付けます。

パブリック関数 Diff2(n1 は Double、n2 は Double、n3 は Double、n4 は Double、n5 は Double)

インデックス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 = 4 End If

'2 番目の数値を検索します。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 index2 = 2 End If

'エラーチェック If (index1 = 0) Or (index2 = 0) Or (index1 - index2 = 0) Then Diff2 = "ERROR" Else Diff2 = SecNum - FirstNum End If

終了関数

Excel VBA を示す画像

関連情報