ExcelのDROP関数は、配列の先頭または末尾から指定した行数・列数を「除外」する関数です。TAKE関数とは「抜き出す」「除外する」で対をなしますが、エラーの発生条件は対称ではありません。この記事ではDROP関数を主役に、TAKEとの違い、空配列で発生する #CALC! の発生原則、IFNAでは拾えないラッパー使い分けまでを、Excel 2024 Build 19929 の実機画面で解説します。
TAKE関数の基本動作とランキング・末尾N件の応用は TAKE関数の使い方｜先頭N件・末尾N件のデータを取り出す方法 で詳しく解説しています。本記事はDROP関数の挙動と、TAKE / DROP のエラー仕様(空配列要求で #CALC!)に焦点を絞っています。
TAKE関数とDROP関数は Microsoft 365 および Excel 2024 以降で利用できます。Excel 2021 / 2019 / 2016 では使えず、同じ数式を入れると #NAME? エラーになります。
サンプルファイル(xlsx)
本記事のDROP例・エラー実例・ラッパー比較を 1 ブックにまとめた配布用サンプルです。Excel 2024 / Microsoft 365 でそのまま開いて確認できます。
結論: DROPは除外、TAKEは抜き出し、エラーは空配列で#CALC!
DROP関数は配列の先頭/末尾から N 行・N 列を除外し、残りを返します。TAKE関数の逆操作です。両関数の主要なエラーは #CALC! ですが、これは「結果が空配列を返す要求」が来たときに発生します。古い情報源が「行数に 0 を渡すと #VALUE!」「要素数を超えると #NUM!」と書く挙動は、Excel 2024 実機ではいずれも発生せず、空配列要求のみが #CALC! として現れます。
DROP関数の構文と基本動作
DROP関数の構文はTAKE関数と同じです。第2引数で行数、第3引数で列数を指定し、第3引数は省略可能です。
=DROP(配列, 行数, [列数])| 引数 | 意味 | 例 |
|---|---|---|
| 配列 | 除外元の範囲(複数列OK) | A1:D11 |
| 行数 | 正の数で先頭から、負の数で末尾から除外 | 1 / -2 |
| 列数 | 同じく正で左から、負で右から除外(省略可) | 1 / -1 |
もっとも実務で使うのは「ヘッダー行を除外して、データ部分だけを取り出す」パターンです。次の数式は1行目(見出し)を除外して、2行目以降の10行を返します。
=DROP(A1:D11, 1)負の数を指定すると末尾から除外します。たとえば =DROP(A2:D11, -5) は末尾5行を除外して先頭5行だけを返します(これは =TAKE(A2:D11, 5) と同じ結果です)。
列の除外も同じ書き方です。先頭列を除外したい場合は次のように書きます。
=DROP(A2:D11, , 1)DROP関数のネスト(ヘッダーと末尾を同時に除外)
「ヘッダー行を除外したうえで、さらに末尾2行も除外する」のような複合操作は、DROP関数を入れ子にすると素直に書けます。
=DROP(DROP(A1:D11, 1), -2)ヘッダー除外後の先頭3件、のように TAKE と DROP を組み合わせるパターンもよく使います。
=TAKE(DROP(A1:D11, 1), 3)FILTER関数と組み合わせて条件抽出+先頭N件を取る
DROPの代わりにTAKEを使った「条件に合う先頭3件」のシンプルな例です。FILTER関数の結果に対してTAKEを被せます。
=TAKE(FILTER(A2:D11, B2:B11="周辺"), 3)
「条件に合う先頭3件の売上合計」を1セルで求めるには、TAKE関数の結果をそのままSUM関数に渡せます。
=SUM(TAKE(FILTER(C2:C11, B2:B11="周辺"), 3))真に「上位3件(売上の高い順)」を取りたい場合は、間に SORT を挟みます。
=SUM(TAKE(SORT(FILTER(C2:C11, B2:B11="周辺"), 1, -1), 3))SORT / SORTBY と TAKE を組み合わせたランキング作成パターンは TAKE関数の使い方 でも具体例を扱っています。
DROP関数のエラー実機マップ(TAKEと挙動が逆転するケースに注意)
DROP関数のエラー挙動はTAKE関数とは 非対称です。「0指定」と「超過指定」で発生条件が逆になります。Excel 2024 Build 19929 の実機検証結果です。
| 数式 | 実機結果 | TAKEとの比較 |
|---|---|---|
=DROP(A2:D11, 0) | エラーなし(全10行返し) | TAKE 0行は#CALC! |
=DROP(A2:D11, , 0) | エラーなし(全4列返し) | TAKE 0列は#CALC! |
=DROP(A2:D11, 999) | #CALC! | TAKE 999行はエラーなし |
=DROP(A2:D11, -999) | #CALC! | TAKE -999もエラーなし |
=DROP(A2:D11, 10) | #CALC! | 全行除外 = 結果が空配列 |
=DROP(A2:D11, , 4) | #CALC! | 全列除外 = 結果が空配列 |
=DROP(A2:D11, 1.7) | エラーなし(切り捨てて1行除外) | 非整数は切り捨て(同じ) |
=DROP(A2:D11, "abc") | #VALUE! | 数値変換できない文字列(同じ) |
| 出力先にブロッカー有り | #SPILL! | 同じ |
DROP固有の罠: 「0指定」と「超過指定」が TAKE と逆転する
TAKEに慣れた感覚で「0なら何も出ない」「超過ならエラー」と思い込むと、DROPでは逆になります。=DROP(...,0) なら除外なし(全件返し)、=DROP(...,999) は全部除外(空配列で #CALC!)です。
TAKE関数のエラー(主要パターン)
TAKE関数のエラー挙動は TAKE関数の使い方 でも扱っていますが、本記事のDROPと対称化して比較できるよう、主要パターンを掲載します。
| 数式 | 実機結果 | 備考 |
|---|---|---|
=TAKE(A2:D11, 0) | #CALC! | 0行抽出 = 空配列要求 |
=TAKE(A2:D11, , 0) | #CALC! | 0列抽出 = 空配列要求 |
=TAKE(FILTER(...,該当なし), 3) | #CALC! | FILTER空配列の伝播 |
=TAKE(A2:D11, 999) | エラーなし(全10行返し) | 超過は#NUM!にならず全件返し |
=TAKE(A2:D11, 1.7) | エラーなし(切り捨てて1行) | 非整数は切り捨て |
=TAKE(A2:D11, "5") | エラーなし(数値変換で5行) | 数字文字列は自動変換 |
=TAKE(A2:D11, "abc") | #VALUE! | 数値変換できない文字列 |
| 出力先にブロッカー有り | #SPILL! | 通常通りスピル阻害 |
補足: #VALUE! と #NUM! はいつ出るか
TAKE / DROPで #VALUE! が出るのは、行数・列数に数値変換できない文字列("abc" など)を渡した場合です。0 や超過では出ません。Microsoft公式ドキュメントは「配列が大きすぎる場合は #NUM! を返す」とも案内しているため、数万行〜数十万行のような極端に大きな配列を扱う場合は念頭に置いておくと安全です。
空配列要求が#CALC!を生む(共通原則)
TAKEとDROPで #CALC! が出るケースは、一見バラバラに見えても、ひとつの原則に集約できます。#CALC!エラー全般の発生条件 も合わせて参考にしてください。
共通原則: 結果が空配列を返す要求が来たとき、TAKE / DROP は #CALC! を返す
- TAKE 0行 → 0行抽出 = 空配列 →
#CALC! - TAKE FILTER空 → 空配列を抜き出し要求 →
#CALC! - DROP 全行除外 → 残り0行 = 空配列 →
#CALC! - DROP 999行除外 → 全部超過除外 = 空配列 →
#CALC! - TAKE 999行(超過) → 全件返し ≠ 空配列 → エラーなし
- DROP 0行 → 全件残り ≠ 空配列 → エラーなし
この原則を理解すれば、TAKE / DROP の #CALC! 発生条件を個別に暗記する必要はなくなります。「結果が空配列になるか」だけ判定すればよいことになります。
エラーラッパーの使い分け(IFNAでは#CALC!を拾えない)
FILTER + TAKEで「該当なしの場合に文字列を返したい」というケースは頻出します。ここで IFNA を使うと 失敗します。IFNAは#N/A専用のため、TAKE / DROPの主要エラーである#CALC!を拾えません。
| ラッパー | 結果 | 判定 |
|---|---|---|
素のまま =TAKE(FILTER(...空), 3) | #CALC! | 失敗例 |
=IFNA(TAKE(FILTER(...空), 3), "該当なし") | #CALC! | 使えない(IFNAは#N/A専用) |
=IFERROR(TAKE(FILTER(...空), 3), "該当なし") | 該当なし | 使える |
=TAKE(FILTER(..., 該当なし, "該当なし"), 3) | 該当なし | 推奨(意図が明確、数式が短い) |
もっともすっきり書けるのは FILTER関数の第3引数 [空の場合] を使う方法です。エラーラッパーで包むよりも意図が読み手に伝わりやすく、入れ子も浅くなります。
=TAKE(FILTER(A2:D11, B2:B11="該当なし", "該当なし"), 3)関連関数マップ(CHOOSEROWS / CHOOSECOLS / EXPAND との使い分け)
Excel 2024には配列の行・列を操作する関数群が複数追加されています。混同しやすいので用途別に整理します。Excel 2024 で追加された新関数の一覧 も参考にしてください。
| 関数 | 用途 | 使い分け |
|---|---|---|
TAKE / DROP | 連続した N 行・N 列を抜き出し / 除外 | 「先頭/末尾の N 件」 |
CHOOSEROWS | 指定した行番号だけ抜き出し(飛び石) | 「1, 3, 5 行目だけ」 |
CHOOSECOLS | 指定した列番号だけ抜き出し(飛び石) | 「1列目と3列目だけ」 |
EXPAND | 配列を指定サイズに拡張(不足は埋める) | 「TAKE の逆ではなく拡張」 |
CHOOSEROWSとCHOOSECOLSは「TAKE / DROPで連続切り出しでは届かない、飛び石抽出」のときに使います。EXPANDは名前から「TAKEの逆」と誤解されがちですが、実体は「配列を指定サイズに拡張する」関数で、用途は別物です。
やってはいけない5パターン
| NG | 結果 | 正しい書き方 |
|---|---|---|
=DROP(A2:D11, 10) | #CALC! | 全行除外は意図不明、行数が全件以上になる場合は IF で分岐 |
=DROP(A2:D11, 999) | #CALC! | 除外行数を ROWS で動的に決め、空配列を避ける |
=IFNA(TAKE(FILTER(...,空),3), "該当なし") | #CALC! のまま | FILTER の第3引数 [空の場合] で先回り対策、または IFERROR で包む |
| 出力先にデータが残ったまま | #SPILL! | 出力先範囲を空にする |
| Excel 2021 / 2019 / 2016 で開く | #NAME? | Excel 2021 は INDEX + SEQUENCE 代替式、Excel 2019 / 2016 は OFFSET など別アプローチで置き換え |
配布サンプルファイルの使い方
配布サンプルは8つのシートで構成されています。
- 使い方 シートに各シートの目次があります
- データ シートが他シートの参照元です(編集すると他シートに反映されます)
- TAKE基本 / DROP基本 で先頭・末尾の抜き出し・除外例を確認できます
- FILTER組合わせ で実用パターン(条件抽出+先頭N件、ランキング)が試せます
- エラー実例 で
#CALC!/#VALUE!/#SPILL!の発生条件を一画面で確認できます - ラッパー比較 でIFNA / IFERROR / FILTER第3引数の違いが見られます
- 代替式 シートは旧バージョン互換の参考用(Excel 2021 向けの INDEX + SEQUENCE と Excel 2019 / 2016 向けの OFFSET 例)。詳しい解説は TAKE関数の使い方 を参照してください
- 速度計測 は1,000行 / 10,000行のサンプル(F9キーで再計算)
TAKE 関数の引数仕様・エラー6種・書式継承・対応バージョン (Excel 2024 / 2021 / 365) などの詳細は TAKE関数 完全リファレンス も参照してください。
DROP 関数の引数仕様・#CALC! の発生条件・整数変換規則・FILTER との二段伝播・対応バージョン (Excel 2024 / 2021 / 365) などの詳細は DROP関数 完全リファレンス も参照してください。
まとめ
- DROP関数で配列の先頭/末尾から N 行・N 列を除外し、TAKE関数で抜き出す
- DROPは「0指定でエラーなし、超過指定で
#CALC!」と TAKE と挙動が逆転する #CALC!の発生条件は「結果が空配列を返す要求」で統一される- 数値変換できない文字列(
"abc"など)を渡すと#VALUE!、極端に大きな配列では#NUM!の可能性もある - IFNA関数では
#CALC!を拾えないため、IFERROR か FILTER の第3引数を使う - Excel 2021 / 2019 / 2016 では
#NAME?になる。Excel 2021 なら INDEX + SEQUENCE、Excel 2019 / 2016 なら OFFSET 等の旧来関数で代替する - 対象は Microsoft 365 または Excel 2024 以降
TAKE関数の基本動作・SORTBY/FILTERランキング・SUM+TAKE合計・スピル参照(#)・旧バージョン向け代替式の詳細は TAKE関数の使い方｜先頭N件・末尾N件のデータを取り出す方法 を参照してください。スピル系の関数群を一通り押さえたい場合は スピル配列関数(データ結合・操作編) も合わせてご覧ください。
TEXTSPLIT で分割した結果から「2 番目」「最後」「N 番目」の要素を TAKE / DROP で取り出したい場合は、Excel TEXTSPLITで2番目・最後・N番目の要素を取り出す（6パターン） もあわせてご覧ください。CHOOSECOLS / INDEX / TAKE+DROP / TEXTBEFORE+AFTER / BYROW など 6 パターンで使い分ける方法をまとめています。
IFERROR は #CALC! を含むエラー 9 種類を捕捉できます。TAKE / DROP での空配列エラーの仕様詳細と、IFNA との使い分け・スピル範囲での挙動については、IFERROR 関数 完全リファレンス で実機検証つきで詳しく解説しています。
コメント