Excelで複数のシートに分かれている商品一覧やデータを、1つの一覧表にまとめて並べ替えたい場合があります。SORT関数やUNIQUE関数、FILTER関数を使えば、手作業でコピーすることなく自動で整理できます。
この記事では、シート1とシート2のA列にある商品名をまとめ、重複を除外しながらシート3へ昇順・降順で表示する方法を解説します。
SORT関数だけでは複数シートのデータを扱えない理由
SORT関数は指定した範囲内のデータを並べ替える関数です。そのため、1つの範囲であれば以下のような数式で利用できます。
=SORT(UNIQUE(FILTER(A3:A30,A3:A30<>"")),,,TRUE)
しかし、この数式ではシート1のA列だけを参照しているため、シート2のデータは含まれません。複数シートの情報をまとめるには、まずデータを結合する処理が必要になります。
複数シートの商品名を結合する基本方法
Excelでは、複数の範囲を縦につなげる場合にVSTACK関数を利用できます。Microsoft 365やExcel 2021以降であれば、以下のような数式で対応できます。
=SORT(UNIQUE(FILTER(VSTACK(シート1!A3:A30,シート2!A3:A30),VSTACK(シート1!A3:A30,シート2!A3:A30)<>"")),,,TRUE)
この数式では、以下の順番で処理しています。
- VSTACK関数でシート1とシート2の商品名を縦に結合する
- FILTER関数で空白セルを除外する
- UNIQUE関数で重複を削除する
- SORT関数で商品名を並べ替える
結果として、シート3のA列には2つのシートの商品名をまとめた一覧が自動生成されます。
VSTACK関数が使えない場合の代替方法
古いExcelではVSTACK関数が利用できない場合があります。その場合は、配列を組み合わせる方法を使用します。
例えば以下のような数式でも同じような処理が可能です。
=SORT(UNIQUE(FILTER((シート1!A3:A30;シート2!A3:A30),(シート1!A3:A30;シート2!A3:A30)<>"")),,,TRUE)
ただし、この書き方はExcelのバージョンによって動作が異なる場合があります。安定して管理する場合は、可能であればVSTACK関数を利用する方法がおすすめです。
商品一覧を別シートへ自動表示する具体例
例えば、シート1に「りんご・みかん・バナナ」、シート2に「ぶどう・りんご・メロン」が登録されている場合、VSTACKで結合すると以下のようなデータになります。
結合前。
- シート1：りんご、みかん、バナナ
- シート2：ぶどう、りんご、メロン
UNIQUE関数によって「りんご」の重複が削除され、SORT関数によって商品名順に並び替えられた一覧がシート3へ表示されます。
商品マスターや在庫管理表など、複数担当者が入力したデータをまとめる場合にも便利な方法です。
数式を作成するときの注意点
シート名に日本語や空白が含まれている場合は、参照するときにシングルクォーテーションで囲む必要があります。
例えばシート名が「商品一覧 1」の場合は、以下のように記述します。
='商品一覧 1'!A3:A30
また、商品名の前後に不要なスペースがある場合、見た目が同じでもUNIQUE関数では別データとして扱われることがあります。その場合はTRIM関数などで余分な空白を削除してから処理すると正しく重複削除できます。
まとめ
複数シートの商品名を1つの一覧にまとめて並べ替える場合は、SORT関数だけではなく、VSTACK関数・FILTER関数・UNIQUE関数を組み合わせることがポイントです。
Microsoft 365などの新しいExcelであれば、=SORT(UNIQUE(FILTER(VSTACK(シート1!A3:A30,シート2!A3:A30),VSTACK(シート1!A3:A30,シート2!A3:A30)<>"")),,,TRUE)のような形で自動化できます。
この方法を使えば、複数のデータ元を管理しながら、常に最新の商品一覧や名簿を別シートへ自動表示できます。
コメント