今回は、大きな表から必要な列や行だけを取り出したいときに使えるExcelのCHOOSECOLS関数とCHOOSEROWS関数について、基本の使い方から他の関数と組み合わせるコツまで紹介します。
この記事は、Microsoft 365のExcelを前提にしています。CHOOSECOLS関数とCHOOSEROWS関数は、Microsoft 365版のExcelのほか、Excel 2024でも利用できる関数として案内されています。Excel 2021以前の買い切り版では使えないため、ファイルを共有する相手の環境にも注意してください。
CHOOSECOLS関数とCHOOSEROWS関数でできること
CHOOSECOLS関数は、指定した範囲（配列）の中から、番号で指定した列だけを取り出す関数です。同じように、CHOOSEROWS関数は番号で指定した行だけを取り出す関数です。
どちらも結果は複数のセルに広がって表示されるスピルの形で返されます。元の表を書き換えずに、別の場所へ「必要な部分だけの表」を作れるのが大きな特長です。
たとえば、次のような場面で役立ちます。
- 10列ある顧客一覧から、氏名・部署・連絡先の3列だけを抜き出したい
- 列の並び順を変えた一覧を、元の表とは別に作りたい
- データの先頭行や最終行など、特定の行だけを参照したい
- FILTER関数やSORT関数の結果から、必要な列だけを表示したい
CHOOSECOLS関数の基本
構文
CHOOSECOLS関数の書き方は次のとおりです。
- =CHOOSECOLS(配列, 列番号1, [列番号2], …)
- 配列：列を取り出す元の範囲（必須）
- 列番号1：取り出す最初の列の番号（必須）
- 列番号2以降：追加で取り出す列の番号（省略可）
列番号は、ワークシートの列（A列、B列…）ではなく、指定した範囲の左端を1とした番号で数えます。たとえば範囲がC2:G20なら、C列が1、D列が2になります。
基本の使用例
A1:E50に「社員番号・氏名・部署・入社日・内線番号」の5列が並んでいるとします。氏名と内線番号だけを取り出すには、次のように入力します。
- =CHOOSECOLS(A1:E50, 2, 5)
入力したセルを起点に、2列分・50行分の結果がスピルで表示されます。
列の並び順を入れ替える
列番号は、書いた順番どおりに並んで返されます。そのため、元の表の順番とは違う並びで列を取り出せます。
- =CHOOSECOLS(A1:E50, 3, 2, 1)：部署・氏名・社員番号の順に並べ替えて表示
同じ列番号を2回指定することもできるため、確認用に同じ列を左右に並べる、といった使い方も可能です。
負の数で後ろから数える
列番号にマイナスの数を指定すると、範囲の右端から数えた列を取り出せます。-1は最後の列、-2は最後から2番目の列です。
- =CHOOSECOLS(A1:E50, -1, -2)：最後の列、最後から2番目の列の順に表示
列が右側に追加されていく表で「常に最新の列を取り出したい」場合などに便利です。
CHOOSEROWS関数の基本
構文
CHOOSEROWS関数は、行を対象にする点以外はCHOOSECOLS関数と同じ考え方で使えます。
- =CHOOSEROWS(配列, 行番号1, [行番号2], …)
行番号も、ワークシートの行番号ではなく、指定した範囲の先頭行を1とした番号で数えます。
使用例
- =CHOOSEROWS(A2:E50, 1)：データの1行目だけを取り出す
- =CHOOSEROWS(A2:E50, -1)：データの最終行だけを取り出す
- =CHOOSEROWS(A2:E50, 1, 3, 5)：1・3・5行目を取り出す
行番号には、{1,3}のような配列定数を指定することもできます。取り出したい行が多い場合は、配列定数でまとめて書くと数式が読みやすくなります。
他の関数と組み合わせるテクニック
CHOOSECOLS関数とCHOOSEROWS関数は、単独で使うよりも、ほかの関数と組み合わせると使い道が広がります。
FILTER関数の結果から必要な列だけを表示する
FILTER関数で条件に合う行を抽出すると、元の表のすべての列が返されます。CHOOSECOLS関数で包むと、抽出結果のうち必要な列だけを表示できます。
- =CHOOSECOLS(FILTER(A2:E50, C2:C50=”営業部”), 2, 5)
この例では、部署が「営業部」の行を抽出し、その中から氏名と内線番号の2列だけを表示します。
SORT関数と組み合わせて並べ替えた一覧を作る
SORT関数で並べ替えた結果からCHOOSEROWS関数で行を取り出すと、上位の数件だけを表示する一覧を作れます。
- =CHOOSEROWS(SORT(A2:E50, 4, 1), 1, 2, 3)：4列目（入社日）の昇順に並べた結果から、先頭の3行を表示
取り出す件数が多い場合は、行番号にSEQUENCE関数を使うと、=CHOOSEROWS(SORT(A2:E50, 4, 1), SEQUENCE(5))のように1から5までをまとめて指定できます。
XMATCH関数で見出し名から列を指定する
列番号を数字で直接書くと、元の表に列が追加されたときに、取り出す列がずれてしまうことがあります。そこで、XMATCH関数で見出し名から列の位置を求める方法が役立ちます。
- =CHOOSECOLS(A1:E50, XMATCH({“氏名”,”内線番号”}, A1:E1))
XMATCH関数が見出し行の中から「氏名」と「内線番号」の位置を探し、その番号がCHOOSECOLS関数に渡されます。列の順番が変わっても、見出し名が同じであれば正しい列を取り出せるため、メンテナンスの手間を減らせます。
テーブルと組み合わせて範囲の拡張に対応する
元のデータをテーブルに変換しておき、配列の引数にテーブル名を指定すると、行が増えても範囲を書き直す必要がありません。
- =CHOOSECOLS(社員一覧, 2, 3)（「社員一覧」はテーブル名の例）
データが日々追加される表では、テーブルとの組み合わせを基本にしておくと管理しやすくなります。
エラーが出たときの確認ポイント
#VALUE!エラー
列番号や行番号に0を指定した場合や、範囲の列数・行数を超える番号を指定した場合は、#VALUE!エラーになります。マイナスの番号を使う場合も、絶対値が範囲の大きさを超えないように注意しましょう。範囲を変更したあとにエラーが出たときは、番号が範囲内に収まっているかを最初に確認します。
#SPILL!エラー
結果を表示する予定の範囲に、すでに値や数式が入っていると、#SPILL!エラーになります。スピルで結果が広がる先のセルを空けてから、もう一度確認してください。結合されたセルがある場所でも同じエラーになるため、出力先には何も入力していない領域を選ぶと安心です。
古いバージョンで開いた場合
CHOOSECOLS関数やCHOOSEROWS関数に対応していないバージョンでファイルを開くと、数式が正しく計算されません。社外や他部署へファイルを渡す場合は、値として貼り付けた版を別に用意するなどの配慮をしておくと、受け取った側が困らずに済みます。
使いこなすための小さなコツ
- 元データと抽出結果は別のシートに分けると、スピル範囲がぶつかりにくくなる
- 抽出結果の見出しは、同じ関数で見出し行を含めて取り出すか、見出し行だけを別に用意する
- 数式が長くなる場合は、LET関数で途中の結果に名前を付けると読みやすくなる
- 列番号を数字で書く場合は、どの列を指しているかをセルのメモなどに残しておく
まとめ
CHOOSECOLS関数は指定した列を、CHOOSEROWS関数は指定した行を、元の表から取り出す関数です。
- 番号は範囲の左端・先頭行を1として数え、マイナスの数で後ろから指定できる
- 指定した順番どおりに並ぶため、列や行の並べ替えにも使える
- FILTER関数、SORT関数、XMATCH関数と組み合わせると、必要な情報だけの一覧を数式で作れる
- #VALUE!エラーは番号の範囲外、#SPILL!エラーは出力先のセルを確認する
元の表を残したまま、用途に合わせた表をいくつも作れるのがこの2つの関数の強みです。Microsoft 365のExcelを使っている場合は、よく使う一覧表の抽出から取り入れてみてください。