Excelメモ01【複数条件からの抽出】
Excelの関数って何回使っても忘れるのでメモ
なにげに数式いれたままでも行列の追加削除には対応するけど、「切り取り貼り付け」するとそこを参照してたところが「# N/A」になるとか知らないとハマる罠とかある。
まずは「複数条件」で抽出するメモ
COUNTIFS
複数条件でなければ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はAVERAGEIFSがあるので複数条件での抽出が可能ですが、他のものはない。ので、色んなサイトで見ていてよくわからなかった「{}」関数の出番です。そうです、みんながキライな「配列」数式です。セルに「=数式」を入れた後、「Enter」でなく「Ctrl+Shift+Enter」(MacはCommnd+Shift+Enterかな?)を押すと「{=数式}」となって囲まれるので使うのなんてことないよ。
※入れた後むやみにそのセルにカーソルをあててEnterしたら「{}」取れちゃうので注意
複数条件は「IF」の入れ子でOKなのだが見た目わる。他ないのか探し中。
例として、受注商品Aの受注までの日数の中央値(下記例)
例) MEDIAN(IF(x_受注フラグ=1,IF(x_商品=[@商品A],x_受注までの日数)))
メモ01はこんなところかな。
すぐ忘れるので備忘録メモ。
間違ってたら修正する(予定)