Biscuitがよく取ってくる数式

条件に合う行を数える

A列で「Denver」と完全一致するセルの数を返します。

ワイルドカードで数える

部分一致にはアスタリスクを使います。ExcelとGoogle Sheetsで動きます。

複数条件で数える

各条件と範囲のペアはANDです。OR条件にしたいなら、COUNTIFSを2つ足します。

一致する行だけ合計

A列が「Denver」に等しい行の、B列の値を合計します。

複数条件で合計

SUMIFSでは値の範囲が最初、それから条件のペア。うっかり逆にしがちです。

累計

下にドラッグ。アンカーが始点を固定して、相対参照の終点が下がっていきます。

基本のVLOOKUP

ProductsのA列からA2を探して、3列目を返します。FALSEは完全一致の意味です。

INDEX/MATCH (こっちのほうがいい)

Biscuitはこっち派です。列の挿入に強くて、左向きの検索もできます。

XLOOKUP (新しめのExcel)

モダンな置き換え。すっきりしていて、エラー処理もうまいです。Google Sheetsはまだ未対応。

近似一致 (ソート済みデータ)

TRUEはA2以下で最大の値を探します。税率ブラケットに便利です。

二方向検索

行も列も動的にマッチさせます。

空白を取り除く

前後のスペースと、内部の重複したスペースを消します。

タイトルケース

「hello world」が「Hello World」になります。

区切り文字で連結

TRUEで空白を無視します。

メールからドメインを抜き出す

FINDで@の位置を探して、MIDでその後ろを全部取ります。

Regex extract (Google Sheets)

どんなごちゃごちゃからでも、最初のメールアドレスを取り出します。

Regex replace

あらゆる空白を1個のスペースにまとめます。

今日の日付

シートが再計算されるたびに更新されます。

2つの日付の間の日数

日付は内部的には数値です。引き算がそのまま使えます。

日付から月名

「January」「February」などを返します。

今月の1日

EOMONTHは「月末」の意味。1か月戻して1日足します。

ISO週の始まり (月曜)

WEEKDAYのタイプ3は月曜で0を返すので、引き算すれば月曜に着地します。

日付間の営業日

土曜、日曜、オプションの祝日を除外します。

列全体に数式を適用

何千行もドラッグしなくていいです。

SQL風の構文のQUERY

SQLを書いているつもりで。本当に動きます。

FILTER

一致する行を返します。見苦しい配列数式のトリックは要りません。

UNIQUE

A列のユニークな値を全部返します。見つけた順で、アルファベット順ではありません。

SORT

2列目で降順にソートします。

アクティブ以外のシートを全部隠す

Sub HideOthers()
  Dim s As Worksheet
  For Each s In ThisWorkbook.Worksheets
    If s.Name <> ActiveSheet.Name Then s.Visible = xlSheetHidden
  Next s
End Sub

ブックを今触っているシートだけにさっと縮めたいときに。

使用範囲の空行を削除

Sub KillEmpties()
  Dim r As Long
  For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
  Next r
End Sub

後ろから走っているので、削除しても行番号がずれません。

フォルダ内の全ファイルを開く

Sub LoopFolder()
  Dim f As String
  f = Dir("C:\path\to\folder\*.xlsx")
  Do While f <> ""
    Workbooks.Open "C:\path\to\folder\" & f
    f = Dir()
  Loop
End Sub

クラシックなDirループ。月次レポートが入ったフォルダにぴったりです。