条件に一致する最終行の値を関数で抽出する数式の説明１

本記事はアフィリエイト広告を含みます

このページは「条件が合うもののうち最終行の値を抽出する方法」を解説している３ページ目です。

ここでは数式の意味を説明します。

まず始めに、計算式のおさらいです。

　ちょっと見づらいですが、セルB１６には次のような計算式が入っています。

{=INDEX($A$3:$E$12,MATCH(LARGE(($D$3:$D$12="A")*1/ROW(B3:B12),COUNTIF($D$3:$D$12,"A")),1/ROW(B3:B12),0),COLUMN(B1))}

この計算式から、条件に合った最終行の「セルB11の値」が取り出せるまでの計算の流れを見てみましょう。

１．配列数式とは何かを理解する

　まず大前提としてこの計算式は「配列数式」になっており、「配列数式」とは、「同じ計算を複数セル（配列）に対して行えるもの」です。

言葉で説明すると分かりずらいので、例でみてみましょう。

　上の表では、３品目の仕入金額の合計を求めています。

この時、D列で「仕入単価」×「入り数」の値を出し、それを合計（SUM関数）するやり方が一般的ですが、これだとD列を計算用セルとして使わなければならなくなります。

これに対して配列数式は、計算用のセルが必要ありません。

{=SUM((B2:B4)*(C2*C4))}

と計算式を入れることで、答えが求められるのです。

イメージとしては「配列に計算途中の値（ここでは品目ごとの「仕入単価」×「入り数」の結果）を格納する」で、配列は何かとなるとまた複雑になるので、ここでは語りません。

まとめると、『計算用のセルを使わなくても答えが出せる』。

それが配列数式なのです。

これを今回「MATCH(LARGE(($D$3:$D$12="A")*1/ROW(B3:B12),COUNTIF($D$3:$D$12,"A")),1/ROW(B3:B12),0)」の所で使っていますが、詳しくは後で説明します。

　ではいよいよ計算式の説明ですが、計算は内側から行われていきますので、ここでも最も内側の部分である「($D$3:$D$12="A")*1」の所から説明していきます。

「($D$3:$D$12="A")」の計算結果は、以下の表のF列の通りです。

まず、配列数式なので、D3からD12までそれぞれ条件に対して真か偽かを判定されます。

具体的には、「＝A」であれば真となり数値の「１」が、「<>総務部」であれば偽であり数値の「０」が立つのです（原則として真は「1」、偽は「0」と判定される）。

そして後半の「*1」は、条件が一つの時に必ず付けるものです。

なお、複数条件の時には「*1」は必要なく、

「($C$3:$C$12="青森県")*($D$3:$D$12="A")」

のような、「（　）*（　）」の形になります（何個条件を付けてもOK）。

　そして次は「/ROW(B3:B12)」の部分です。

ROWは、「セルの行番号を返す」関数です（ROW関数の基本を知りたい方はコチラ）。

ここも配列数式なので、「ROW(B3:B12)」において例えば行番号３「〇〇研修」のセルでは「３」が返ります。

つまり、下の表のG列のようになるのです。

これらの結果、「($D$3:$D$12="A")*1/ROW(B3:B12)」はそれぞれ「F列÷G列」の値となり、下の表のH列のようになります。

つまり、

　① 条件に合わない行の値は０

　② 条件に合う行の値は下に行くほど小さくなり、ダブりはない

という状態になります。

またまだ計算式は続きますが、長くなるのでこの後は次のページで説明します。