Excel配列をTAKE・INDEXで制限する方法

=TAKE(
    FILTER('P&L'!C5:O250,'P&L'!B5:B250="基本ケース"),
    12,
    13
)

この数式は、P&Lシートで「基本ケース」に一致したデータから、先頭12行・13列を返します。構文や細かな動作は異なりますが、ExcelでGoogle SheetsのARRAY_CONSTRAINに最も近い関数です。

ただし、出力を12行に制限しただけでは、その12行が正しいとは限りません。元データが不足していても、並べ替えが間違っていても、表の見た目は整ってしまいます。四半期決算報告や投資家向け資料で使う場合は、次のようなチェックも併設してください。

予定件数と実際の出力件数
抽出条件に該当した総件数
並べ替えの基準と方向
対象期間の開始月と終了月
財務三表や元帳との一致

本記事では、配列を小さくする数式だけでなく、レビューに耐えるための検証方法までセットで解説します。

ExcelでARRAY_CONSTRAINに相当する関数は？

Google Sheetsでは、配列のサイズを次のように制限します。

=ARRAY_CONSTRAIN(source_range,number_of_rows,number_of_columns)

Excelで直接対応させるなら、次の書き方が基本です。

=TAKE(source_range,number_of_rows,number_of_columns)

たとえば、DCFモデルの出力から先頭20行・6列だけを投資家向けプレゼンテーションに掲載する場合は、次のように記述します。

=TAKE('DCF Output'!B6:P65,20,6)

不要なセルをIFで空白にしたり、過大な範囲をグラフの参照元にしたりするより、出力サイズを数式で明示できるため、モデルの意図が伝わりやすくなります。

一方で、TAKEに指定した行数や列数が元の配列を上回る場合、通常は取得可能な配列全体が返ります。たとえば8社しかない配列にTAKE(...,10)を適用しても、エラーではなく8社分が表示されます。

そのため、「10社を表示する予定だったが、実際には8社しか抽出できなかった」という不足を表だけで把握することはできません。予定件数との差を別セルで検証します。

=LET(
    data,FILTER('Comparable Companies'!A6:H100,'Comparable Companies'!H6:H100="採用"),
    expected,10,
    "予定 "&expected&"社 / 実績 "&ROWS(data)&"社"
)

不足を明確な警告として表示するなら、次のようにします。

=LET(
    data,FILTER('Comparable Companies'!A6:H100,'Comparable Companies'!H6:H100="採用"),
    expected,10,
    IF(ROWS(data)<expected,"要確認：比較対象が"&expected-ROWS(data)&"社不足","OK")
)

MicrosoftのTAKE関数のドキュメントでは、配列の先頭または末尾から、指定した数の連続する行・列を返す関数と説明されています。

Excelで配列を制限する方法の比較

方法	主な対応環境	サイズの動的変更	末尾から取得	主な用途
`TAKE`	Microsoft 365、Excel 2024	可	可	取締役会資料、KPIダッシュボード
`DROP`＋`TAKE`	Microsoft 365、Excel 2024	可	可	ローリングフォーキャスト
`INDEX`＋`SEQUENCE`	動的配列対応版	可	要調整	行列番号を動的に組み立てるモデル
終点を`INDEX`で指定	旧デスクトップ版を含む	限定的	不可	固定レイアウト、旧版との共有
Excelテーブル参照	Excel 2007以降	可	不可	定期更新する取引・SKUデータ
固定範囲を手動指定	全バージョン	不可	不可	構造が変わらないテンプレート

互換性を考慮する必要がなければ、TAKEが第一候補です。金融機関やグループ会社など、旧バージョンのExcelを使う配布先が含まれる場合は、INDEXによる代替も検討してください。

固定範囲は単純ですが、年度計画の途中で予測月が追加されたり、四半期決算会議の直前に比較列が増えたりすると、集計漏れの原因になります。固定範囲を採用する場合は、想定する最終月や最終行をチェックセルに表示しておくと安全です。

="対象終了月："&TEXT(INDEX(B4:M4,1,COLUMNS(B4:M4)),"yyyy年m月")

Excel配列の行数と列数を制限する方法

TAKEの第2引数で行数、第3引数で列数を指定します。列数は省略できます。

=TAKE('Returns Analysis'!B8:Q67,12,8)

この数式は、左上を起点として12行×8列を返します。

負の数を指定すると、配列の下端または右端から取得できます。

=TAKE('Cash Flow'!B6:M65,-12,-4)

この場合は、末尾12行・右端4列が返ります。月次でデータが増える資金繰り表から、直近期間だけを表示する場合に便利です。

行数または列数に0を指定すると、空の配列を返せないため#CALC!エラーになります。前提条件シートから取得する値が0になる可能性がある場合は、0を許容するのか、最低1行を表示するのかを業務要件として決めてください。

最低1行を表示する設計なら、次のように下限を設定できます。

=TAKE(
    'Operating Model'!B7:P66,
    MAX(1,Assumptions!$B$12),
    8
)

ただし、MAX(1,...)はデータ不足を解決する式ではありません。予定行数と実績行数の検証は別に行います。

=LET(
    expected,Assumptions!$B$12,
    actual,ROWS('Operating Model'!B7:B66),
    IF(actual<expected,"要確認："&expected-actual&"行不足","OK")
)

列数も含めて出力形状を確認する場合は、スピル参照を利用できます。出力数式がB7にあるなら、次のチェックを追加します。

=ROWS(B7#)&"行 × "&COLUMNS(B7#)&"列"

これにより、レビュー担当者は数式を開かなくても、実際の出力サイズを確認できます。

FILTERの結果を指定行数に制限する方法

TAKEは、FILTER、SORT、SORTBYなどの外側に配置すると効果的です。内側で対象データを抽出・並べ替えし、最後に報告シートへ渡す件数を制限します。

たとえば、186件のSKU別限界利益一覧から、限界利益率が基準を下回るSKUを抽出し、そのうち利益率が低い10件を取締役会資料に掲載するとします。

=LET(
    data,FILTER(
        'SKU Economics'!A6:H191,
        'SKU Economics'!H6:H191<Assumptions!$B$18
    ),
    sorted,SORTBY(data,CHOOSECOLS(data,8),1),
    TAKE(sorted,10,8)
)

Assumptions!$B$18に38.5%が設定されている場合、基準未満のSKUを抽出し、限界利益率が低い順に並べたうえで、最大10行・8列を返します。

関数の順序は重要です。SORTBYより先にTAKEを適用すると、元データの先頭10行を取得し、その10行だけを並べ替えることになります。目的と異なる結果でも表は整って見えるため、レビューで発見しにくいミスです。

下位10SKUに追加すべき監査チェック

表示用の表とは別に、少なくとも次の4項目を確認します。

チェック項目	数式例
元データ件数	`=COUNTA('SKU Economics'!A6:A191)`
基準未満件数	`=COUNTIF('SKU Economics'!H6:H191,"<"&Assumptions!$B$18)`
出力件数	`=ROWS(B6#)`
10件目の限界利益率	`=IFERROR(INDEX(B6#,10,8),"10件未満")`

さらに、出力が昇順になっているかを確認するチェックも有効です。出力先をB6#とした場合は、次のように検証できます。

=LET(
    rates,CHOOSECOLS(B6#,8),
    IF(AND(DROP(rates,1)>=DROP(rates,-1)),"OK","要確認：並べ替え順")
)

同率のSKUがある場合は、売上高やSKUコードなどの第2順位を明示してください。そうしないと、更新のたびに10件目が入れ替わり、取締役会資料の説明が不安定になります。

=LET(
    data,FILTER(
        'SKU Economics'!A6:H191,
        'SKU Economics'!H6:H191<Assumptions!$B$18
    ),
    sorted,SORTBY(
        data,
        CHOOSECOLS(data,8),1,
        CHOOSECOLS(data,1),1
    ),
    TAKE(sorted,10,8)
)

TAKEで期間データを切り出す方法

月次モデルや四半期サマリーでは、件数だけでなく、期間の開始と終了が正しいかを検証する必要があります。

たとえば、日付とP&L金額を条件で抽出し、先頭12期間だけを返す場合は次のように記述します。

=TAKE(
    FILTER(
        'P&L'!B5:C250,
        ('P&L'!B5:B250>=Assumptions!$B$3)*
        ('P&L'!B5:B250<=Assumptions!$B$4)
    ),
    12,
    2
)

出力先がB8の場合、開始月と終了月を次のチェックセルに表示します。

="開始："&TEXT(INDEX(B8#,1,1),"yyyy年m月")

="終了："&TEXT(INDEX(B8#,ROWS(B8#),1),"yyyy年m月")

前提条件との一致を一つの式で検証することもできます。

=LET(
    result,B8#,
    actual_start,INDEX(result,1,1),
    actual_end,INDEX(result,ROWS(result),1),
    expected_start,Assumptions!$B$3,
    expected_end,Assumptions!$B$4,
    IF(
        AND(actual_start=expected_start,actual_end=expected_end),
        "OK",
        "要確認：対象期間が前提と不一致"
    )
)

月次データの欠落も確認してください。開始月と終了月が一致していても、途中の月が抜けている可能性があります。

=LET(
    dates,CHOOSECOLS(B8#,1),
    expected,DATEDIF(MIN(dates),MAX(dates),"m")+1,
    actual,ROWS(dates),
    IF(actual=expected,"OK","要確認："&expected-actual&"か月不足")
)

配列を表示するのではなく集計値を求める場合は、SUMIFSなどで母集団を直接集計します。

=SUMIFS(
    'P&L'!C:C,
    'P&L'!B:B,">="&Assumptions!$B$3,
    'P&L'!B:B,"<="&Assumptions!$B$4
)

TAKEが制御するのは出力範囲です。集計対象となる母集団や財務ロジックを制限するものではありません。

TAKEを使わずにExcel配列を制限する方法

動的配列に対応した別の方法として、INDEXとSEQUENCEを組み合わせることもできます。

=INDEX(
    'DCF Output'!B6:P65,
    SEQUENCE(MIN(20,ROWS('DCF Output'!B6:P65))),
    SEQUENCE(,MIN(6,COLUMNS('DCF Output'!B6:P65)))
)

この例のMINは、指定数が元の配列を超えた際のTAKEエラーを避けるためではありません。SEQUENCEで存在しない行番号や列番号を生成し、INDEXが範囲外を参照することを防ぐために使っています。

旧バージョンとの互換性を優先する場合は、INDEXで範囲の右下セルを指定できます。

='DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6)

この数式は、B6から元範囲内の20行目・6列目までを参照範囲として定義します。SUMなど、範囲を引数に取る関数の中でも利用できます。

=SUM('DCF Output'!B6:INDEX('DCF Output'!B6:P65,20,6))

ただし、終点をINDEXで指定する方法は、最新のスピル配列と同じ表示動作を保証するものではありません。配布先が使用しているExcelのバージョンで実際に開き、数式、表示範囲、グラフ参照を確認してください。

DCF出力に追加すべき監査チェック

DCFや企業価値評価では、切り出した表示範囲だけでなく、元モデルとの一致も検証します。

=IF(
    SUM(B6:G25)=SUM('DCF Output'!B6:G25),
    "OK",
    "要確認：DCF出力と不一致"
)

浮動小数点による微差を考慮する場合は、許容差を設定します。

=IF(
    ABS(SUM(B6:G25)-SUM('DCF Output'!B6:G25))<1,
    "OK",
    "要確認：DCF出力と不一致"
)

表示列に割引現在価値や継続価値が含まれる場合は、どこまでを投資家向け資料に掲載し、どこからを内部計算列とするのかも明示してください。

TAKEとDROPでローリング期間を切り出す方法

TAKEは配列の端から必要な部分を取得し、DROPは配列の端を除外します。両者を組み合わせると、配列の中間部分を切り出せます。

たとえば、月次モデルに過去12か月と将来60か月の予測が並んでいるとします。予測2年目の12か月を取得するには、先頭24列を除外してから次の12列を取得します。

=TAKE(DROP('Monthly Model'!C10:BZ45,,24),,12)

MicrosoftのDROP関数のドキュメントでは、負の値を指定すると、配列の末尾から行や列を除外できると説明されています。

四半期の取締役会資料では、月次モデルから8四半期分を切り出すために同じパターンを利用できます。

=TAKE(
    DROP(
        'Quarterly Summary'!B5:AZ30,
        ,
        Assumptions!$B$20
    ),
    26,
    8
)

ここでは、Assumptions!$B$20で開始位置を制御しています。前提を更新しても、出力は最大26行×8四半期に保たれます。

ローリング期間に追加すべき監査チェック

開始列の番号だけでは、実際に何年度・何四半期が表示されているか分かりにくいため、出力ヘッダーを検証します。出力先をB5とした場合は、次のように表示できます。

="対象期間："&
TEXT(INDEX(B5#,1,1),"yyyy年m月")&
" ～ "&
TEXT(INDEX(B5#,1,COLUMNS(B5#)),"yyyy年m月")

8四半期が連続していることも確認します。

=LET(
    headers,DROP(TAKE(B5#,1,),,0),
    expected,EDATE(DROP(headers,,-1),3),
    actual,DROP(headers,,1),
    IF(AND(expected=actual),"OK","要確認：四半期に欠落または重複")
)

ヘッダーが文字列の場合は、元モデル側に検証用の日付行を用意するほうが安全です。「2026年度Q1」のような表示用ラベルと、2026年6月30日のような実日付を分けると、期間チェックを安定して自動化できます。

配列を制限しても計算が速くなるとは限らない

TAKEで表示結果を小さくすると、後続の数式が参照するスピル範囲や、ダッシュボード上で確保すべき領域を小さくできます。ただし、内側にあるFILTERやSORTBYの計算対象まで自動的に小さくなるわけではありません。

次の数式では、最終出力は10行ですが、FILTERとSORTBYは先に元データ全体を処理します。

=TAKE(
    SORTBY(
        FILTER(A2:H50000,H2:H50000<38.5%),
        FILTER(H2:H50000,H2:H50000<38.5%),
        1
    ),
    10
)

したがって、処理速度を改善したい場合は、次の3点を分けて考える必要があります。

入力範囲
列全体ではなく、実データを含む範囲やExcelテーブルを参照します。
中間計算
同じFILTERを複数回計算している場合は、LETで結果を再利用します。
最終出力
TAKEで表示件数を制限し、後続の数式やグラフが参照する範囲を小さくします。

たとえば、同じ抽出結果を再利用する数式は次のように書けます。

=LET(
    data,FILTER(A2:H50000,H2:H50000<38.5%),
    sorted,SORTBY(data,CHOOSECOLS(data,8),1),
    TAKE(sorted,10)
)

MicrosoftのExcelの仕様と制限によると、ワークシートは1,048,576行・16,384列まで扱えます。しかし、実際の明細が4,800件しかない場合に、すべての計算で列全体を参照する必要はありません。

TAKEはあくまで最終出力を制御する関数です。処理速度の改善には、元の参照範囲、重複計算、揮発性関数、シート間参照まで含めた見直しが必要です。

配列制限で#SPILL!を防ぐ

動的配列の出力先に別の値があると、#SPILL!エラーが発生します。Microsoftの動的配列とスピル動作の解説でも、スピル範囲が遮られている場合の動作が説明されています。

更新後に9行から43行へ増える可能性がある無制限のFILTERより、KPIダッシュボード用に12行と定義した出力のほうがレイアウトを管理しやすくなります。

ただし、表示上限を12行にするなら、13行目以降が存在することを知らせるチェックも必要です。

=LET(
    data,FILTER('KPI Detail'!A5:F500,'KPI Detail'!F5:F500="要対応"),
    shown,MIN(12,ROWS(data)),
    hidden,MAX(0,ROWS(data)-shown),
    IF(hidden=0,"全件表示","ほか"&hidden&"件あり")
)

これにより、ダッシュボードのレイアウトを維持しながら、表示されていない対象があることも明示できます。

財務モデルで配列の範囲制限が重要な理由

配列の制限には、レイアウト調整以上の意味があります。四半期決算報告や投資家向けプレゼンテーションでは、「なぜこの期間、この会社、このSKUを表示したのか」を説明できなければなりません。

たとえば、企業価値評価のサマリーに比較対象企業8社とEBITDA倍率の中央値14.2倍を掲載する場合、表示を8行に制限するだけでは不十分です。少なくとも次の点を明確にします。

採用対象となった企業の総数
除外条件と除外件数
表示する8社の選定基準
8社の中央値と元データ全体の中央値
前回資料から採用企業が変わった理由

同様に、予実管理で差異上位10項目を表示するなら、金額差異なのか差異率なのか、プラスとマイナスのどちらを優先するのかを固定する必要があります。

=LET(
    data,FILTER(A6:F200,A6:A200<>""),
    sorted,SORTBY(data,ABS(CHOOSECOLS(data,6)),-1),
    TAKE(sorted,10)
)

この式なら、差異の絶対額が大きい順に10件を表示します。出力件数、全差異の合計、表示10件の差異合計も併記すると、表示範囲が全体に占める重要度を確認できます。

=SUM(CHOOSECOLS(B6#,6))/SUM('Variance Detail'!F6:F200)

Excel配列を制限すべきでないケース

不完全な元データや誤った計算ロジックを隠す目的でTAKEを使ってはいけません。

たとえば、資金残存期間の感応度分析で参照範囲が6行不足し、新規採用者の人件費が除外されている場合、最終出力をきれいに制限しても問題は解決しません。まず母集団と計算ロジックを修正する必要があります。

網羅性が重要な監査・照合表にも、安易な上限を設けるべきではありません。

借入金残高の照合
グループ会社間取引の消去
財務三表の整合性チェック
固定資産台帳と総勘定元帳の照合
売掛金・買掛金の残高確認

これらは管理対象の全件を含める必要があります。スピル範囲を整えるために、4,200万円のリース債務を貸借対照表から漏らしては本末転倒です。

上位・下位10件の出力にも注意してください。TAKE(FILTER(...),10)が返すのは、元データの並び順で最初に条件を満たした最大10件です。金額が大きい取引先、利益率が低いSKU、返済期限が近い借入金などを抽出したい場合は、TAKEの前に必ず並べ替えを行います。

投資家向け資料に使う前のチェックリスト

取締役会用の表示タブとして問題なく見えても、外部開示や投資家向け資料に転記する前には、次の項目を確認してください。

出力件数が予定件数に達しているか
予定件数未満の場合、その理由が表示されているか
元データ件数と条件該当件数を確認できるか
並べ替えの項目、方向、同率時の第2順位が明確か
対象期間の開始日と終了日が前提条件に一致するか
月や四半期に欠落・重複がないか
表示値の合計が元モデルや財務三表と一致するか
表示上限を超えた非表示データの件数が分かるか
Excelの配布先バージョンで数式が動作するか
数式エラーをIFERRORで隠していないか

稟議資料や決算説明資料では、チェック結果を非表示シートに追いやるのではなく、作成者とレビュー担当者が確認できる管理欄にまとめると運用しやすくなります。

よくある質問

ExcelにARRAY_CONSTRAIN関数はありますか？

ExcelにはARRAY_CONSTRAINという名前の関数はありません。Microsoft 365またはExcel 2024では、TAKE(array,rows,columns)を使うと、ほぼ同じ目的を実現できます。

Excelで配列の先頭10行だけを返すにはどうすればよいですか？

次のように記述します。

=TAKE(array,10)

列数も制限する場合は、第3引数を追加します。

=TAKE(array,10,5)

この数式は、先頭から最大10行・5列を返します。

TAKEで10行を指定して、元データが8行しかない場合はどうなりますか？

通常は、取得可能な8行すべてが返ります。10行に満たないことを示すエラーは発生しないため、予定件数との比較を別セルで行ってください。

=IF(ROWS(B6#)<10,"要確認："&10-ROWS(B6#)&"件不足","OK")

TAKEで#CALC!エラーが発生するのはどのような場合ですか？

代表的なのは、取得する行数または列数に0を指定した場合です。Excelでは空の配列を返せないため、#CALC!になります。

=TAKE(A2:H100,0,5)

前提条件から取得する値が0になる場合は、0件時に何を表示すべきかを決め、IFなどで分岐してください。

Excelで配列の末尾から行や列を取得できますか？

取得できます。TAKEに負の数を指定します。

=TAKE(A2:H100,-12,-4)

この数式は、配列の末尾12行・右端4列を返します。

FILTERの結果を下位10件に制限するにはどうすればよいですか？

まずSORTまたはSORTBYで順位を確定し、その外側にTAKEを配置します。

=LET(
    data,FILTER(A2:H200,H2:H200<38.5%),
    sorted,SORTBY(data,CHOOSECOLS(data,8),1),
    TAKE(sorted,10)
)

出力件数、条件該当件数、10件目の値も別セルで確認してください。

TAKEで指定した行数が元の配列より多い場合、MINは必要ですか？

TAKEでは通常、指定数が配列サイズを上回っても、取得可能な配列全体が返るため、その目的でMINを追加する必要はありません。

一方、INDEXとSEQUENCEを組み合わせる場合は、存在しない行番号や列番号を生成しないようにMINを使うことがあります。

=INDEX(
    A2:H100,
    SEQUENCE(MIN(20,ROWS(A2:H100))),
    SEQUENCE(,MIN(6,COLUMNS(A2:H100)))
)

Excel 2021以前でTAKEを使えない場合の代替方法は？

INDEXで右下の終点を指定します。たとえば、B6:P65から20行・6列を参照する場合は、次のように記述します。

=B6:INDEX(B6:P65,20,6)

この参照をSUMなどの関数内で使えば、旧バージョンでも固定サイズの範囲を扱えます。

=SUM(B6:INDEX(B6:P65,20,6))

TAKEはExcelブックの処理速度を改善しますか？

必ず改善するとは限りません。TAKEは最終的なスピル範囲を小さくできますが、内側のFILTERやSORTBYは元の入力範囲を処理します。

速度を改善するには、列全体参照を避ける、Excelテーブルを使う、LETで中間結果を再利用するなど、入力範囲と中間計算も見直してください。

TAKEで隠れた行があることを表示できますか？

TAKE単体では表示されません。抽出結果の総件数と表示上限を比較して、非表示件数を別セルに表示します。

=LET(
    data,FILTER(A2:H200,H2:H200<38.5%),
    limit,10,
    IF(ROWS(data)>limit,"ほか"&ROWS(data)-limit&"件あり","全件表示")
)

まとめ：Excel配列は出力と検証をセットで設計する

Microsoft 365またはExcel 2024では、配列の行数・列数を制限する方法としてTAKEが最も簡潔です。ローリング期間にはDROPと組み合わせ、抽出結果の上位・下位件数を制限する場合は、FILTERとSORTBYの外側に配置します。

Excel 2021以前との互換性が必要なら、INDEXで範囲の終点を指定する方法を検討してください。

ただし、TAKEはデータ不足や並べ替えの誤りを警告してくれる関数ではありません。8社しか抽出できなかった表を、予定どおりの10社に見せることもありませんが、不足を目立たせることもありません。

実務では、配列を制限する数式とともに、次の監査情報を表示することが重要です。

予定件数と実績件数
元データ件数と条件該当件数
順位基準と境界値
開始期間と終了期間
非表示となった件数
元モデルや財務三表との一致

「直近12か月」「8四半期」「利益率下位10SKU」「比較対象8社」といった報告目的を明確にし、その目的どおりの結果になっていることをチェックできる設計にしましょう。

Google Sheetsで同様のワークフローを自動化するなら、ModelMonkeyのプランを選ぶことができます。