Excelメモ01【複数条件からの抽出】
Excelの関数って何回使っても忘れるのでメモ
なにげに数式いれたままでも行列の追加削除には対応するけど、「切り取り貼り付け」するとそこを参照してたところが「# N/A」になるとか知らないとハマる罠とかある。
まずは「複数条件」で抽出するメモ
COUNTIFS
COUNTIFS (条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2],…)
条件範囲 1：
必ず指定します。 対応する条件による評価の対象となる最初の範囲を指定します。
条件 1 ：
必ず指定します。 計算の対象となるセルを定義する条件を数値、式、セル参照、または文字列で指定します。 たとえば、条件は 32、">32"、B4、"Windows"、または "32" のようになります。
条件範囲 2, 条件 2,... 省略可能です。 追加の範囲と対応する条件です。 最大 127 組の範囲/条件のペアを指定できます。
複数条件でなければcountif、複数条件ならcountifs。いいところは文字列の検索が完全一致でなくて「*キーワード*」とできるところ。気を付けるのは「数字も””（ダブルコート）でくくる」ところ。
よく使う。めっちゃ使う。その際、範囲から指定する場合、キーとなる値から位置を設定するので、一緒に「indexとmatch」をよく使う。
※例）yシートの表から「yシートのID」と「指定セルのID」が一致する受注日
例） index(x_受注日,match([@ID],y_ID,0),1)ちなみに範囲が列そのものの場合、位置が変わらないのであれば「名前」を設定すると使いやすい。各行ごとに指定列の値を使用する場合、「B2」とかにするとこの値ってなんの項目の値だっけ？ってなるので、その場合は「PowerQuery」でCSV読込して使用すると「[@列名]」となるので数式がわかりやすい（上記例）。
日付とかを条件にする例として、2021/12/1 をセルに入れておいて、形式を「2021年12月」とかにしておき、そこのセルを参照してその月のみの受注を抽出する場合、「edate」をよく使う（下記例）。edateはマイナス使えないとこ注意。
例） countifs(x_受注日,">="&[@2021_12],x_受注日,"<"&EDATE([@2021_12],1))平均値をとる
こちら。中心傾向を表すのに浮かぶものを独断とアンコンシャスバイアス（偏見）でピックアップします。
平均値：AVERAGE
中央値：MEDIAN
異常値割合除外平均：TRIMMEAN
標準偏差：STDEVP
（※今後の新しい関数：「STDEV.P 関数」）
AVERAGE(数値 1, [数値 2], ...)
引数の平均 (算術平均) を返します。 たとえば、セル A1:A20 範囲 に数値が含まれている場合、数式 =AVERAGE(A1:A20) はそれらの数値の平均値を返します。（例：=AVERAGE(A2:C2)）
MEDIAN(数値 1, [数値 2], ...)
引数リストに含まれる数値のメジアン (中央値) を返します。 メジアンとは、一連の数値の中央にくる数値のことです。（例：=MEDIAN(A2:A6)）
TRIMMEAN(配列, 割合)
データの中間項の平均を返します。 TRIMMEAN 関数は、データ全体の上限と下限から一定の割合のデータを切り落とし、残りの項の平均値を返します。 この関数は、範囲外のデータを分析対象から排除する場合に使用できます。（例：=TRIMMEAN(A2:A12,0.2)）
MODE(数値 1,[数値 2],...)
MODE は、データの配列または範囲で最も頻繁に発生する 、または繰り返し発生する値を返します。（例：=MODE(A2:A7)
※今後の新しい関数：「MODE.MULT」および「MODE.SNGL」
STDEVP(数値 1,[数値 2],...)
引数を母集団全体であると見なして、母集団の標準偏差を計算します。 標準偏差とは、統計的な対象となる値がその平均からどれだけ広い範囲に分布しているかを計測したものです。（例：=STDEVP(A3:A12)）
※今後の新しい関数：「STDEV.P 関数」
さて、上記、AVERAGEはAVERAGEIFSがあるので複数条件での抽出が可能ですが、他のものはない。ので、色んなサイトで見ていてよくわからなかった「{}」関数の出番です。そうです、みんながキライな「配列」数式です。セルに「=数式」を入れた後、「Enter」でなく「Ctrl＋Shift＋Enter」（MacはCommnd+Shift+Enterかな？）を押すと「{=数式}」となって囲まれるので使うのなんてことないよ。
※入れた後むやみにそのセルにカーソルをあててEnterしたら「{}」取れちゃうので注意
複数条件は「IF」の入れ子でOKなのだが見た目わる。他ないのか探し中。
例として、受注商品Aの受注までの日数の中央値（下記例）
例） MEDIAN(IF(x_受注フラグ=1,IF(x_商品=[@商品A],x_受注までの日数)))メモ01はこんなところかな。
すぐ忘れるので備忘録メモ。
間違ってたら修正する（予定）
この記事が気に入ったらサポートをしてみませんか？