MENU

Excelで同一顧客IDの予約IDを最小から第4最小まで抽出する方法｜MINIFSの限界を超えるSMALL・FILTER・TAKEの実践

Excel

Excel で「同一顧客 ID に対して最小～第 4 最小の予約 ID を取り出したい」という実務ニーズは、請求・出荷・予約順の把握などで頻出です。MINIFS は最小値 1 件しか返せず、上位 4 件を並べるには一工夫が必要。この記事では、Excel のバージョン別に “確実に動く式” を網羅し、配列数式・動的配列・テーブル（構造化参照）・重複除外・エラー対策まで実務視点で整理します。

目次

前提とゴール

以下のレイアウトを前提に解説します（列名やシート名はご自身の環境に合わせて置き換えてください）。

  • rawdata シートの 列 N に 予約 ID（数値推奨）
  • 同じく 列 O に 顧客 ID
  • 対象顧客 ID は任意シートの セル J4

求めたいのは、同一顧客 ID に紐づく 最小・第 2・第 3・第 4 最小 の予約 ID です。

サンプルデータ（イメージ）

行N列（予約ID）O列（顧客ID）
21005C001
31003C002
41001C001
51008C001
61002C003
71004C001
81006C002
91007C001

たとえば J4 に C001 が入っているなら、結果は 1001, 1004, 1005, 1008 の 4 件（昇順）になります。

なぜ MINIFS では足りないのか

=MINIFS(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38, $J$4) は条件一致の「最小値 1 件」しか返しません。第 2・第 3…の “順位付き最小” は SMALL の第 2 引数に順位を渡す か、FILTER → 並べ替え → 上位 4 件 といった手順が必要です。

バージョン別・用途別の最短解

状況別に “最短で使える式” を一覧化しました。以降の章で詳細解説します。

Excel バージョン推奨式ポイント
Excel 2019 以前=SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 1)
※第 2 引数を 1→2→3→4 に変更して 4 つ取得
配列数式：Ctrl+Shift+Enter 必須		SMALL＋IF の定番。非一致行は FALSE→無視。順位ごとに式を複製。
Microsoft 365 / Excel 2021 以降
（動的配列）		=SMALL(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4), 1)
※末尾 1→2→3→4		FILTER で絞り込み済み配列に対して SMALL。Ctrl+Shift+Enter 不要。
動的配列で 4 件を一括スピル=SMALL(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4), {1,2,3,4}){1,2,3,4} を渡して 1 回の式で 4 件を横方向にスピル。
代替（Microsoft 365 限定）=TAKE(SORT(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4)), 4)並べ替えて先頭 4 件を抜く。読みやすく、メンテしやすい。

Excel 2019 以前：SMALL＋IF の配列数式で確実に取る

動的配列がない環境では、次の配列数式が王道です。

  1. 結果を表示したいセル（例：K4）に次の式を入力し、Ctrl+Shift+Enter で確定します。
    =SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 1)
  2. 第 2 引数（順位）を 2、3、4 に変えて、K4 の右（L4、M4、N4 など）に複製します。

この方法は、非一致行が IF により FALSE となり、SMALL によって自動的に無視される仕組みです。

一括取得（横並び）

2019 以前でも、4 つのセルにそれぞれ順位を変えた式を入れれば横並び表示が可能です。
1 本の式で横にスピルさせるには動的配列が必要ですが、次のように「順位セル」を用意すると管理が楽になります。

セル内容
I4:L41 / 2 / 3 / 4（固定値）
K6（配列数式）=SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), I4) を右にコピー

エラー対策（件数不足）

対象顧客の件数が 4 未満だと #NUM! が出ます。表示を抑えたい場合は IFERROR で包みましょう。

=IFERROR(SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 4), "")

予約 ID が “文字列” のとき

列 N が文字列（例：先頭ゼロ付与やインポート直後）だと SMALL は正しく評価できません。VALUE で数値化してください。

=SMALL(IF(rawdata!$O$2:$O$38=$J$4, VALUE(rawdata!$N$2:$N$38)), 1)

配列確定不要の代替：AGGREGATE（2010 以降）

AGGREGATE 関数の「集計方法 15（SMALL）×オプション 6（エラー無視）」を使うと、配列確定なしで第 n 最小が取れます。

=AGGREGATE(15, 6, rawdata!$N$2:$N$38/(rawdata!$O$2:$O$38=$J$4), 1)

順位 2～4 は末尾の 1 を 2、3、4 に変更して横に並べます。

Microsoft 365 / Excel 2021 以降：FILTER を核に組み立てる

動的配列がある環境なら、読みやすさ・保守性ともに FILTER → SMALL または SORT → TAKE が最適です。

第 n 最小を個別に取得

=SMALL(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4), 1)

2～4 件目は末尾を 2、3、4 に。

4 件を 1 回の式で一括スピル

=SMALL(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4), {1,2,3,4})

横方向に 4 つの結果がスピルします。順位配列は固定の {1,2,3,4} のほか、動的に生成するなら次が便利です。

  • SEQUENCE(4)（365 のみ）
  • ROW(A1:A4)（互換重視／行方向コピーで自動増分）
=SMALL(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4), SEQUENCE(4))

より読みやすく：SORT＋TAKE

FILTER した結果を昇順に並べ替え、先頭から 4 件だけを切り出す形です。

=TAKE(SORT(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4)), 4)

縦に 4 行スピルします。横並びにしたい場合は次のように TRANSPOSE を追加します。

=TRANSPOSE(TAKE(SORT(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4)), 4))

件数不足の安全策（#NUM! や #VALUE! の抑制）

SMALL は 4 件未満だと #NUM!。TAKE は取り出す件数が配列長を超えるとエラーになり得ます。次のように LET で一旦配列を受け、MIN で件数を調整すると堅牢です。

=LET(a, FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4),
     IFERROR(TAKE(SORT(a), MIN(4, ROWS(a))), ""))

重複を除いて「ユニークな最小 4 件」にしたい場合

=LET(a, FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4),
     IFERROR(TAKE(SORT(UNIQUE(a)), MIN(4, ROWS(UNIQUE(a)))), ""))

予約 ID が重複していないことが前提なら UNIQUE は不要です。

構造化参照（テーブル化）でメンテナンスを最小化

生データ範囲をテーブル（挿入 > テーブル）に変換し、テーブル名を tblData、列見出しを 予約ID と 顧客ID にしておくと、行の追加・削除に自動追従します。式が劇的に読みやすくなり、属人化を防げます。

テーブル版（動的配列）

=SMALL(FILTER(tblData[予約ID], tblData[顧客ID]=$J$4), SEQUENCE(4))

テーブル版（SORT＋TAKE）

=TAKE(SORT(FILTER(tblData[予約ID], tblData[顧客ID]=$J$4)), 4)

テーブル版（Excel 2019 以前：配列数式）

=SMALL(IF(tblData[顧客ID]=$J$4, tblData[予約ID]), 1)

順位 2～4 は第 2 引数を変更、確定は Ctrl+Shift+Enter。

配置パターンと表示整形

  • 横並びで 4 件：=SMALL(..., {1,2,3,4}) または TRANSPOSE(TAKE(...,4))
  • 縦並びで 4 件：=TAKE(SORT(FILTER(...)), 4)
  • 未満件を空白：IFERROR(式, "")
  • 見やすい見出し：左に「最小」「第 2」「第 3」「第 4」と見出しセルを置くと運用時の事故を減らせます。

精度と再現性の注意点

  • 予約 ID は数値が望ましい：文字列の数値が混在すると並び順が期待と異なることがあります。必要に応じて VALUE や二重単項 -- で数値化します。
  • NULL/空白の扱い：SMALL は空白を無視します。意図せず空白ゼロが混ざると順位がずれるため、入力規則や Power Query で前処理しましょう。
  • 同値（タイ）の扱い：同一値が複数ある場合、SMALL も TAKE も “その回数分” を返します。重複を除きたい場合は UNIQUE を併用します。
  • 全列参照は避ける：$N:$N のような全列参照は速度低下の原因。テーブル化または適切な範囲指定が推奨です。
  • 更新タイミング：参照先のデータが編集・追加されるたびに再計算されます。スナップショットを残したい場合は値貼り付けを利用。

パフォーマンス最適化：LET で 1 回だけ評価する

広い範囲を複数回評価すると再計算が重くなります。LET で一時配列を束ねると、可読性も向上します。

=LET(
  ids,  rawdata!$N$2:$N$38,
  cust, rawdata!$O$2:$O$38,
  hit,  FILTER(ids, cust=$J$4),
  IFERROR(SMALL(hit, SEQUENCE(4)), "")
)

同様に SORT＋TAKE 版もまとめられます。

=LET(
  ids,  rawdata!$N$2:$N$38,
  cust, rawdata!$O$2:$O$38,
  hit,  FILTER(ids, cust=$J$4),
  IFERROR(TRANSPOSE(TAKE(SORT(hit), MIN(4, ROWS(hit)))), "")
)

「ユニーク上位 4 件」や「n 件を可変」に拡張する

ユニークな最小 4 件

=LET(a, FILTER(tblData[予約ID], tblData[顧客ID]=$J$4),
     IFERROR(TAKE(SORT(UNIQUE(a)), MIN(4, ROWS(UNIQUE(a)))), ""))

取得件数をセル指定で可変（例：J5 に件数）

=LET(a, FILTER(tblData[予約ID], tblData[顧客ID]=$J$4),
     IFERROR(TAKE(SORT(a), MIN($J$5, ROWS(a))), ""))

横 1 行で並べたい場合は TRANSPOSE( ... ) を追加してください。

よくあるトラブルと対処

症状原因対処
#NUM! が出る対象件数が順位より少ない（SMALL）IFERROR(SMALL(...), "") で抑制／MIN(4, ROWS(...)) を使う
#VALUE! が出るTAKE の件数が配列長を超える／文字列混入MIN(件数, ROWS(...)) で調整／VALUE で数値化
並び順がおかしい文字列数値として比較している--(tblData[予約ID]) や VALUE で明示的に数値化
新行を追加しても範囲が伸びない固定範囲参照テーブル化して構造化参照に置換（tblData[予約ID] 等）
重複を 1 回にしたい同じ予約 ID が複数行に存在UNIQUE を併用（SORT(UNIQUE(FILTER(...)))）

運用に効く 3 つのベストプラクティス

  1. テーブル化して構造化参照：生データに行追加が日常茶飯事なら必須。式の修正をゼロに。
  2. 順位は配列でまとめる：{1,2,3,4} または SEQUENCE(4) で 1 本の式に集約。ミスが減ります。
  3. IFERROR を最終段で：原因を隠しすぎるのは禁物ですが、出力面では最後にだけ入れるのが見やすさとデバッグのバランスが良いです。

コピペで使えるレシピ集

2019 以前（配列数式／順位 1～4 を横並び）

各セルに以下を入れて Ctrl+Shift+Enter。第 2 引数だけ変えます。

=IFERROR(SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 1), "")
=IFERROR(SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 2), "")
=IFERROR(SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 3), "")
=IFERROR(SMALL(IF(rawdata!$O$2:$O$38=$J$4, rawdata!$N$2:$N$38), 4), "")

365/2021：1 本で 4 件（横スピル）

=IFERROR(SMALL(FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4), {1,2,3,4}), "")

365/2021：ソートして上位 4 件（縦スピル→横にしたければ TRANSPOSE）

=LET(a, FILTER(rawdata!$N$2:$N$38, rawdata!$O$2:$O$38=$J$4),
 IFERROR(TAKE(SORT(a), MIN(4, ROWS(a))), ""))

テーブル版（構造化参照／横スピル）

=IFERROR(SMALL(FILTER(tblData[予約ID], tblData[顧客ID]=$J$4), SEQUENCE(4)), "")

ユニーク最小 4 件（365）

=LET(a, FILTER(tblData[予約ID], tblData[顧客ID]=$J$4),
 IFERROR(TRANSPOSE(TAKE(SORT(UNIQUE(a)), MIN(4, ROWS(UNIQUE(a))))), ""))

AGGREGATE（配列確定不要／2010 以降）

=IFERROR(AGGREGATE(15, 6, rawdata!$N$2:$N$38/(rawdata!$O$2:$O$38=$J$4), 1), "")
=IFERROR(AGGREGATE(15, 6, rawdata!$N$2:$N$38/(rawdata!$O$2:$O$38=$J$4), 2), "")
=IFERROR(AGGREGATE(15, 6, rawdata!$N$2:$N$38/(rawdata!$O$2:$O$38=$J$4), 3), "")
=IFERROR(AGGREGATE(15, 6, rawdata!$N$2:$N$38/(rawdata!$O$2:$O$38=$J$4), 4), "")

検証用：出力の見た目を整えるミニテンプレート

項目式（横 4 件）
最小～第 4 最小（365/2021）=IFERROR(SMALL(FILTER(tblData[予約ID], tblData[顧客ID]=$J$4), {1,2,3,4}), "")
縦 4 行（365/2021）=LET(a, FILTER(tblData[予約ID], tblData[顧客ID]=$J$4), IFERROR(TAKE(SORT(a), MIN(4, ROWS(a))), ""))
2019 以前（順位 1～4）=IFERROR(SMALL(IF(tblData[顧客ID]=$J$4, tblData[予約ID]), 1), "") ほか
ユニーク 4 件（365）=LET(a, FILTER(tblData[予約ID], tblData[顧客ID]=$J$4), IFERROR(TAKE(SORT(UNIQUE(a)), MIN(4, ROWS(UNIQUE(a)))), ""))

まとめ：どの式を選べばよいか

  • Excel 2019 以前：SMALL＋IF（配列数式）か AGGREGATE。安定・高速・広く互換。
  • Microsoft 365 / 2021：読みやすさ重視なら SORT＋TAKE、順位が必要なら SMALL(FILTER(...), {1,2,3,4}) を推奨。
  • 運用：テーブル化＋LET＋IFERROR で堅牢化。重複要件がある場合は UNIQUE を併用。

以上の型をそのままコピペすれば、同じ顧客 ID に対して 最小・第 2・第 3・第 4 最小の予約 ID を安定して抽出できます。バージョン差・データ品質・件数不足を見越した “エラーに強い式” を選ぶことが、現場運用では最短ルートです。

Excel
Excel 関数 filter 動的配列 SMALL MINIFS

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次