3つの表を結合するとき、1つの表にしかないIDも残すには？
3表すべてのIDを残すため、各表のID列を集めて重複のない一覧を作ります。その一覧を左側の基準表として元の3表と結合し、名前・点数・地域を1つの結果にまとめます。
3表のうち1つを基準にして「左のシートの全行を保持」で結合すると、基準の表にないIDは結果から抜けます。そこで、各表のID列だけを集め、重複を除いた全ID一覧を作ります。この一覧を新しい結合の基準表にするため、一度Excelに保存して読み込み直します。一覧を左側に置いて元の3表をそれぞれ結合すれば、1つの表にしかないIDも残せます。
どの元表にも全IDはそろっていません
この架空の例では、NamesにU1・U2、ScoresにU2・U3、AreasにU3・U4があります。各表の中ではIDの重複も空欄もありません。全ID一覧には、U1からU4までの4つをすべて含める必要があります。
表中の∅は空欄を示す説明用の記号で、入力する文字ではありません。
Names
| NameID | Name |
|---|---|
| U1 | Ari |
| U2 | Bo |
Scores
| ScoreID | Score |
|---|---|
| U2 | 0 |
| U3 | 7 |
Areas
| AreaID | Area |
|---|---|
| U3 | North |
| U4 | West |
ID列だけをコピーし、列名をそろえます
ExcelやGoogle Sheetsで、NameID・ScoreID・AreaIDの各列を見出し行と一緒に別々のシートへコピーします。元表はそのまま残し、コピー先の見出しだけをすべてIDに変えてください。値は重複も含めて残します。ここで行う準備は列の取り出しと列名の統一で、一覧の結合と重複の削除はJOIN SHEETで行います。
| 貼り付けて作るシート | 列名 | コピーする値 |
|---|---|---|
| NamesIDs | ID | U1, U2 |
| ScoresIDs | ID | U2, U3 |
| AreasIDs | ID | U3, U4 |
JOIN SHEETで重複のないID一覧を作ります
- Namesから取り出したID列を見出し行まで含めてコピーし、JOIN SHEETの「コピーしたデータを貼り付け」を押します。貼り付け後のプレビューでID列が1つ、データが2行あることを確認します。「シート名」にNamesIDsと入力し、「シートを作成」を押してください。同じ手順でScoresIDsとAreasIDsも作ります。
- NamesIDsのシート名をクリックし、結果の基準に選びます。NamesIDsの下の＋ポイントからScoresIDsの上の＋ポイントへドラッグします。続けてScoresIDsの下の＋ポイントとAreasIDsの上の＋ポイントをつなぎます。
- 2本の接続線のメニューで、それぞれ「完全に同じ行を1行だけ残す」を選びます。ID列しかないため、IDが同じなら行全体も同じになります。名前・点数・地域の列まで含めると、それらの値も重複判定の対象になります。
- 「結果のプレビュー」で、NamesIDsを基準にした結果を確認します。ID列が1つ、U1・U2・U3・U4の4行になっていれば、入力6行からU2とU3が1行ずつ減っています。表示順は異なっていても構いません。
この記事では、作成した全ID一覧をAllIDsと呼びます。結果を説明するための名前です。
| ID |
|---|
| U1 |
| U2 |
| U3 |
| U4 |
一覧を保存して読み込み直し、元表を結合します
ここまでのAllIDsはプレビューに表示された計算結果です。次の結合でシートとして使えるよう、Excelに保存して読み込み直します。
- 「結果を保存」を押し、全ID一覧をExcelファイルに保存します。
- 「ファイルを追加」で、保存したID一覧と元表のNames・Scores・Areasを読み込みます。ID一覧を左に、3つの元表を右に置きます。接続する前に、保存したID一覧のシート名をクリックし、結果の基準に選んでください。
- ID列の右側の接続ポイントを、NameID・ScoreID・AreaIDの左側の接続ポイントにそれぞれつなぎます。3つとも「左のシートの全行を保持」を選んでください。AllIDsから各元表へ直接つなぐことで、どれかの表に情報がなくてもIDを残せます。
- 「結果のプレビュー」で、基準のシートが保存して読み込んだID一覧であることを確認し、U1からU4までの4つのIDがあるかを見ます。ID一覧を作るときに使った縦結合のグループとは、別の接続です。
結果で確認する点
NamesとScoresにないU4も残り、AreaにWestが付きます。U2のScoreは0、U1は空欄です。Namesだけを基準にした場合はU3・U4が抜けるので、この2つのIDが残っているかを確認してください。
| ID | NameID | Name | ScoreID | Score | AreaID | Area |
|---|---|---|---|---|---|---|
| U1 | U1 | Ari | ∅ | ∅ | ∅ | ∅ |
| U2 | U2 | Bo | U2 | 0 | ∅ | ∅ |
| U3 | ∅ | ∅ | U3 | 7 | U3 | North |
| U4 | ∅ | ∅ | ∅ | ∅ | U4 | West |
適用範囲と注意点
新しいIDが増えたら、最新のID列で縦結合と重複の削除をやり直します。更新した一覧を保存し、結合の基準に使ってください。IDが空欄なら、元表でどの対象を指すのかを先に確認します。Names・Scores・Areasの中でIDが重複すると結果の行数が増えるため、その明細をすべて残す必要があるかも確認してください。異なる列の値は、結果でも別々の列に残ります。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
How do I merge 3 datasets into one, using a common ID variable that each dataset shares (Reddit r/excel)
原文と後続の議論は、最初の表にないIDを失わずに3表を横に結合する問題を扱っています。 上の表と手順は、この質問を参考に別途作成した架空の例です。質問者の実データや製品の利用体験ではありません。
