仕事でよく使うExcel数式

いつも使う時に忘れるので自分用の備忘録がてら。組み合わせることでGrep結果の集計とか、ある程度パターン化されているエクセル設計書の集計とかに使える

こんな地獄のような数式使わないに越したことはないと思うんですが、世の中それで済んだらExcelなんて要らないんですよね！

パターンで文字切り出し

A2に<hoge>あいうえお</hoge>みたいな文字があるとして

D2に<hoge>, E2に</hoge>があるとする場合、B2辺りに以下の数式を入れると特定の文字列で囲まれた文字列を切り出せる

=MID(A2,FIND(D$2,A2)+LEN(D$2),(FIND(E$2,A2)-FIND(D$2,A2)-LEN(D$2)))

条件付き書式を複数列反映

D列の値を条件として同一行複数列に書式設定するものとする

条件式で$D1="HOGE"のように列名をロックした式を設定し、範囲をA:Gのようにすると、常にD列を参照した状態で範囲的に設定できる

最初に出現する行位置の特定

=MATCH("hoge",A:A,0)

ワイルドカード

アスタリスクを生やす

=MATCH("*hoge",A:A,0)

セクション行のグルーピング

Grepとかで抜き出した結果をエクセルに貼り付けてグルーピングする時に使えるやつ

これを

こうしたい

手法

Sectionを抜く列を作る

B2だけは決め打ちで2を入れておき、B3以降に設定

=IF(ISERROR(FIND(E$2,A3)),B2,ROW())

Section名を抜く列を作る

C2以降に設定、タイトル行ならD2とE2の値を元にA列の内容を切り出し、タイトル行でなければB列の値を行番号とし、C列の行を当てに行くロジック

=IF(ISERROR(FIND(D$2,A2)),INDIRECT("C"&B2),MID(A2,FIND(D$2,A2)+LEN(D$2),(FIND(E$2,A2)-FIND(D$2,A2)-LEN(D$2))))

複数条件でのCOUNTIF

COUNTIF()で複数条件を実現するには文字列結合した列を作る事があると思いますが、COUNTIFS()ならその必要はない

=COUNTIFS(条件列1, 条件1, 条件列2, 条件2, ...)

便利関数メモ

よく使うやつ

• IF(), AND(), OR(), NOT()
  • 条件分岐でよく使う
• SUM()
  • 集計用。SUMIFS()はあんま使ったことない
• COUNTIF(), COUNTIFS()
  • 条件付き集計
• COUNT(), COUNTA(), COUNTBLANK()
  • 個数カウント
• VLOOKUP(), MATCH()
  • 検索用
• ADDRESS(), INDIRECT(), INDEX()
  • MATCH()結果からセルを当てる時に有用
  • INDIRECT()はシート名を書いておくとシート名の変更時に追従しやすい（その際シート名管理用のセルをどっかに作っておく）
• FIND(), LEN(),MID()
  • 文字列切り出しの鉄板

まれに使うやつ

• DATEDIF()

  • DATEの差分がINTで取れる便利な子