今回は、ExcelのCHOOSECOLS関数を使い、横に長いデータから必要な列だけを選んで表示する方法を紹介します。
CHOOSECOLS関数とは
CHOOSECOLS関数は、指定した配列から必要な列番号だけを選び、別の場所へ返す関数です。元データの列を削除せず、確認や提出に必要な項目だけの一覧を作れます。
顧客台帳から会社名、担当者、電話番号だけを表示する、商品一覧からコードと価格だけを取り出すなど、用途別の表示表を作る場面に向いています。
返す列は一つだけでなく複数指定でき、元データと異なる順番にも並べられます。列の選択と並び替えを一つの数式で行える点が特徴です。
基本的な使い方
最初の引数へ元データの範囲や配列を指定し、その後に取り出したい列番号を指定します。元範囲の左端を1列目として数えます。
たとえばA列からF列の範囲で、1列目、3列目、6列目を指定すると、A列、C列、F列に相当する内容が三列で返ります。
結果は数式を入力したセルから右方向と下方向へスピルします。展開先へ別の値や結合セルがあるとエラーになるため、必要な広さを空けます。
列の順番を変更する
列番号は元データと同じ順に指定する必要はありません。6列目、1列目、3列目のように指定すれば、選んだ順番で結果が表示されます。
社内の入力表では管理番号を左端、提出用では会社名を左端にするなど、目的に合わせた並びを作れます。元データの構成を変更しないため、既存の数式や入力運用へ影響を与えにくい方法です。
同じ列番号を複数回指定すると、その列を複数回返すこともできます。ただし通常は重複表示の意図が分かりにくいため、見出しや用途を確認します。
右端から列を指定する
CHOOSECOLS関数では、負の列番号を使って右端から数える指定ができます。-1は右端、-2は右から二列目という考え方です。
列数が一定で、最後の列や最後から二番目の列を取り出したい場合に便利です。元範囲の左端から何列目かを数えなくても指定できます。
一方、元データへ新しい列を右端へ追加すると、-1が指す項目は変わります。常に「最終列」を取りたいのか、特定の項目を取りたいのかを区別します。
見出しを含める方法
元範囲へ見出し行を含めれば、選択した列の見出しも一緒に返せます。データと見出しの列対応がずれにくく、表示用一覧を簡単に作れます。
ただし、FILTER関数などでデータ行だけを処理する場合は、見出しとデータを分けたほうが数式を組み立てやすいことがあります。
見出しを固定文字で別に入力する場合は、元データの項目名が変わったときに表示側だけ古くならないよう注意します。元の見出しを参照するか、変更手順を決めます。
Excelテーブルと組み合わせる
元データをExcelテーブルにすると、行の追加へ自動的に対応できます。CHOOSECOLS関数の元配列へテーブルのデータ部分を指定すれば、新しい行も抽出結果へ反映されます。
テーブル名を使うと参照範囲の目的が分かりやすくなります。ただし、CHOOSECOLS関数で指定する列は位置番号なので、元テーブルの途中へ列を追加すると、同じ番号が別の項目を指す場合があります。
列構成を変更した後は、表示結果の見出しと内容を確認します。変更が多い表では、XMATCH関数で見出し名から列位置を求める方法も検討します。
見出し名で列位置を指定する
「電話番号が何列目か」のように、見出し名を基準に列位置を取得したい場合は、XMATCH関数などで見出し行を検索し、その結果をCHOOSECOLS関数へ渡せます。
これにより、元データの列順が変わっても、見出し名が同じなら目的列を探せます。複数列を選ぶ場合は、検索する見出し名を配列として用意します。
見出しの表記が変わると検索できません。「電話」「電話番号」「TEL」などの表記を統一します。複雑な数式になる場合は、必要列の一覧を別セルへ置き、どの項目を抽出しているか見えるようにします。
FILTER関数と組み合わせる
必要な行と列の両方を絞りたい場合は、FILTER関数とCHOOSECOLS関数を組み合わせます。先に条件に合う行を抽出し、その結果から必要列を選ぶ構成が分かりやすい方法です。
たとえば、状態が「対応中」の行だけを残し、案件名、担当者、期限の三列だけを表示できます。元表が横に長くても、確認用一覧を簡潔にできます。
該当行がない場合はFILTER関数の該当なし処理を設定します。文字列の案内を返した後にCHOOSECOLS関数へ渡すと列数が合わない場合があるため、エラー処理の位置を確認します。
SORTBY関数と組み合わせる
必要列だけを表示しながら、表示しない列を基準に並べ替えることもできます。まず元データをSORTBY関数で並べ、その結果からCHOOSECOLS関数で列を選びます。
たとえば、社内管理用の優先度列を表示せず、優先度順に案件名、担当者、期限だけを出せます。閲覧者に不要な内部項目を隠しつつ、業務上の順序を保てます。
ただし、非表示にした基準が分からないと並び順を理解できません。「優先度順」などの説明を一覧の近くへ記載します。
TAKE関数やDROP関数との違い
TAKE関数は先頭または末尾から指定数の列を取り出し、DROP関数は先頭または末尾から指定数の列を除外します。CHOOSECOLS関数は、離れた位置にある列を個別に選ぶ場合に向いています。
「先頭三列だけ」ならTAKE関数、「最初の管理列を除く」ならDROP関数、「1、4、7列目を選ぶ」ならCHOOSECOLS関数というように使い分けます。
目的に対して単純な関数を選ぶと、数式を見た人が処理を理解しやすくなります。複数の関数で同じ結果を作れる場合は、列構成の変更へどう対応したいかで選びます。
列番号エラーを防ぐ
元範囲の列数を超える番号や0を指定するとエラーになります。元データの列が削除された場合も、以前の列番号が範囲外になることがあります。
列番号をセルへ入力して利用者が変更できるようにする場合は、入力規則で有効な範囲を制限します。重複番号や空白が入った場合の結果も確認します。
数式を作る段階では、最初に一列だけを指定し、正しい列が返ることを確認してから列番号を追加します。見出し行を含めると、内容を目視しやすくなります。
提出用データを作る際の注意点
CHOOSECOLS関数で不要列を表示しなくても、元データはブック内に残っています。外部へ提出する際に元シートも含めて渡すと、非表示にした個人情報や内部項目が見られる可能性があります。
提出用の結果を新しいブックへ値として貼り付け、不要なシート、名前の定義、外部リンク、文書プロパティなどを確認します。
抽出結果をCSVへ保存する場合は、数式ではなく表示結果が出力されること、日付や先頭ゼロの形式が保たれることを確認します。
実務でのポイント
- 抽出する列と表示順を先に一覧化する
- 元表の列追加後は列番号が正しいか確認する
- 変更が多い表では見出し名から列位置を検索する
- 行条件はFILTER関数、並べ替えはSORTBY関数と役割を分ける
- 外部提出時は元データを含まない別ファイルを作る
数式を共有する場合は、「1列目、4列目」だけでなく、どの見出しを選んでいるかをメモへ残します。元データの仕様変更時に修正箇所を見つけやすくなります。
まとめ
ExcelのCHOOSECOLS関数は、横に長いデータから指定した列だけを選び、任意の順番で表示します。元表を変更せず、用途別の確認一覧や提出用データの土台を作れます。
FILTER関数で行を絞り、SORTBY関数で並べ替えると、必要な行と列を整理した動的な一覧になります。列番号は元範囲の構成変更で意味が変わるため、見出しとの対応を確認します。必要な項目と表示順を明確にし、元データを含めたまま外部へ渡さないことが重要です。