仕事でよく使う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)))
file

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

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

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

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

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

ワイルドカード

アスタリスクを生やす

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

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

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

これを

Title
C:\Hoge\Foo\Src\Hoge001.txt
100,1: aaaa
101,1: bbb
C:\Hoge\Foo\Src\Hoge002.txt
10,1: aaaa
11,1: bbb
18,8: cccc

こうしたい

Title Section
C:\Hoge\Foo\Src\Hoge001.txt Hoge001
100,1: aaaa Hoge001
101,1: bbb Hoge001
C:\Hoge\Foo\Src\Hoge002.txt Hoge002
10,1: aaaa Hoge002
11,1: bbb Hoge002
18,8: cccc Hoge002

手法

Sectionを抜く列を作る

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

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

file

Section名を抜く列を作る

C2以降に設定、タイトル行ならD2E2の値を元に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))))

file

複数条件での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で取れる便利な子