ERP（TROPOS/Epicor）のExcel出力では、検査の種類は「CHARACTERISTIC DESCRIPTION」、値は「TEST RESULT」に縦持ちで並ぶことがあります。GRN/LOT/PCODEごとにColour・Haze・Density…を横に展開する、FILTER＋CHOOSECOLS＋LOOKUPの実装手順と注意点を解説します。
ERPの検査結果が「縦持ち」で出てくると何が起きる？
ERPからの品質検査・分析結果のエクスポートは、見た目としては「データベースに近い形式」で出力されることが多いです。つまり、1行に1つの測定項目（Characteristic）が入り、どの測定なのかは説明列（CHARACTERISTIC DESCRIPTION）で区別し、測定値は結果列（TEST RESULT）に入ります。
この形式は保存や連携には強い一方で、Excelで「個体（ロットやGRN）ごとにColour/Haze/Densityを横に並べて比較したい」「Power BIで項目別に可視化したい」という場面では、そのままでは扱いづらくなります。
| 列の例 | 意味 | よくある値 | この問題での役割 |
|---|---|---|---|
| GRN / LOT / PCODE | 個体ID（キー） | GRN12345、LOT2025-001、PCODE-A | 横展開の「行キー」 |
| CHARACTERISTIC DESCRIPTION | 測定項目の説明 | Colour、Haze、Density… | 横展開の「列見出し」 |
| TEST RESULT | 測定値 | 10.2、PASS、0.012… | 横展開で表示したい値 |
| DATE / REV / UPDATE… | 更新日時・版など | 2025/12/01 10:30… | 重複（更新）時の優先順位に影響 |
完成形のイメージ（横展開テーブル）
今回のゴールは、Sheet1側に次のような「1行＝1個体」の表を作ることです。列は「説明（Characteristic）」の種類ぶん増えます。
| 個体ID（GRN/LOT/PCODE） | Colour | Haze | Density | … |
|---|---|---|---|---|
| GRN12345 | Yellow | 0.12 | 1.03 | … |
| GRN12346 | Amber | 0.08 | 1.02 | … |
なぜXLOOKUPで「先頭の値」問題が起きるのか
XLOOKUP（やVLOOKUP）は、基本的に「検索キーに一致する最初の1件」を返す設計です。ERPの縦持ちデータでは、同じ個体IDに対して複数の測定項目が並ぶため、IDだけで検索すると「Colourの行なのか」「Hazeの行なのか」を区別できず、結果としてそのIDの最初に出てきたTEST RESULTが返り続けます。
もちろん、IDと説明を結合した「複合キー」を作ってXLOOKUPする方法もあります。ただし、ERP出力の行数が多い場合は結合キー列を作るだけで重くなったり、説明名の揺れ（余計なスペース、全角半角、改行）で一致しなかったりします。そこでおすすめなのが、次の考え方です。
解決の考え方：IDで絞った“部分表（サブ配列）”を作ってから引く
ポイントはとてもシンプルで、「まずIDで行を絞る」→「必要列だけを抜く」→「説明で結果を引く」という二段階に分けることです。
| 処理 | 使う関数 | 狙い |
|---|---|---|
| IDの行だけ抽出 | FILTER | その個体の行群（サブ配列）だけにする |
| 説明列＋結果列だけ抜く | CHOOSECOLS | 検索対象を最小化し、列番号ミスも把握しやすくする |
| 説明→結果を取得 | VLOOKUP / XLOOKUP | Colour列にはColourの結果、Haze列にはHazeの結果を返す |
Excelでの実装手順（FILTER＋CHOOSECOLS＋LOOKUP）
ここからは、ERP出力が入ったシートをQuery1、横展開先をSheet1として説明します。なお、以下の式に出てくる xxx は Query1 の最終行番号に置き換えてください（ここがズレると、重くなる／結果が欠ける／開くだけで固まる、の原因になりやすいです）。
説明（CHARACTERISTIC DESCRIPTION）の一覧をヘッダーとして展開する
まず、横展開テーブルの列見出しを自動生成します。説明名が増減しても列が自動で追従するので、保守が楽になります。
| セル | 式 | 意味 |
|---|---|---|
| A1 | =Query1!C1:E1 | IDなど固定の見出しを持ってくる（運用に合わせて調整） |
| D1 | =TRANSPOSE(SORT(UNIQUE(Query1!H2:Hxxx))) | 説明列のユニーク一覧を作り、横方向に並べる |
このとき、説明名に余計なスペースが混じると「同じ項目なのに別物扱い」になりがちです。もし説明名が揺れる場合は、別列に正規化した説明（TRIMなど）を作ってからUNIQUEにかけるのが安定します。
個体ID（GRN/LOT/PCODE）の一覧を作る
次に、1行=1個体となるように、個体IDのユニーク一覧を作ります。
| セル | 式 | 意味 |
|---|---|---|
| A2 | =UNIQUE(Query1!C2:Cxxx) | 個体IDのユニーク一覧（ここではC列が個体キーの想定） |
もしGRN/LOT/PCODEが別々の列にあり、3列セットで個体を一意にしたい場合は、Query1側で「結合キー列」を作ってからそれをC列として扱うのが分かりやすいです。
例：Query1の空き列（例：B列）に結合キーを作る
=C2&"|"&D2&"|"&E2この結合キーをSheet1側のA列に使えば、複合キーでも同じ手順で横展開できます。
“その個体だけ”のサブ配列から固定項目を引く
Sheet1に「LOT」や「PCODE」など固定情報も持たせたい場合は、IDでFILTERした配列から該当列を引きます。固定情報は通常、同一IDなら同じ値なので、先頭行の値を取れれば十分です。
=VLOOKUP($A2, FILTER(Query1!$C$2:$L$xxx, Query1!$C$2:$C$xxx=$A2), 2)上の式は「IDで絞った行群」から2列目を取る例です。どの列を取るかはQuery1の列構成に合わせて調整してください。
横展開の核心：説明（列見出し）をキーにしてTEST RESULTを返す
いよいよColour/Haze/Density…の列を埋める式です。D2に次の式を入れ、右方向にコピーしてから、必要行まで下方向にコピーします。
=VLOOKUP(D$1, CHOOSECOLS(FILTER(Query1!$C$2:$L$xxx, Query1!$C$2:$C$xxx=$A2), 6, 7), 2, 0)この式がやっていることを、日本語にすると次の通りです。
- FILTER：Query1から「IDがA2の行」だけを抜き出す
- CHOOSECOLS：抜き出した行群から「説明列（6列目）」「結果列（7列目）」だけに絞る
- VLOOKUP：列見出し（D1の説明名）に一致する行の「結果」を返す
エラー（該当項目が存在しないなど）を空欄にしたい場合は、次のようにIFERRORで包むと表が見やすくなります。
=IFERROR(VLOOKUP(D$1, CHOOSECOLS(FILTER(Query1!$C$2:$L$xxx, Query1!$C$2:$C$xxx=$A2), 6, 7), 2, 0), "")CHOOSECOLSの列番号がズレる理由と、確実な合わせ方
CHOOSECOLSは「FILTERで作った配列の左端を1列目として数える」ため、FILTERの開始列を変えると列番号も全部変わります。ここを勘で合わせると、存在しない列を指して壊れたり、別の列を引いて気づけなかったりします。
たとえば、FILTER範囲を Query1!$C$2:$L$xxx とした場合、列番号対応は次のようになります。
| FILTER範囲内の列番号 | 実際の列 | 例（よくある項目） |
|---|---|---|
| 1 | C | 個体ID（GRN/LOT/PCODE） |
| 2 | D | LOT など |
| 3 | E | PCODE など |
| 6 | H | CHARACTERISTIC DESCRIPTION（説明） |
| 7 | I | TEST RESULT（結果） |
自分のデータで「説明がH列、結果がI列」ではない場合は、上の表の考え方で列番号を組み替えてください。列番号が合っているか不安なときは、まず
=CHOOSECOLS(FILTER(Query1!$C$2:$L$xxx, Query1!$C$2:$C$xxx=$A2), 6, 7)だけを別セルに入れて、説明と結果の2列が出ているか目視で確認すると、事故が減ります。
同一ID＋同一説明が複数行ある場合（更新で重複が出る）
要件が「先頭（元データ）だけ取れればOK」であれば、今回の構成は相性が良いです。なぜならVLOOKUPは一致候補が複数あっても先に見つかった1件を返すからです。
ただし、「先頭」がどれかは並び順に依存します。ERP出力が更新日時順で並んでいるのか、抽出条件で順序が変わるのか、は運用によって変わります。先頭の定義を安定させたい場合は、FILTERで抜いた後にSORT/SORTBYで並び替えてからLOOKUPするのが安全です。
| やりたいこと | 考え方 | 実装の方向性 |
|---|---|---|
| 最初（元）を取りたい | 古い日時が先になるように整列してから先頭を使う | SORTBYで日時昇順 → VLOOKUP（先頭一致） |
| 最新を取りたい | 新しい日時が先になるように整列してから先頭を使う | SORTBYで日時降順 → VLOOKUP（先頭一致） |
例として、更新日時がFILTER範囲内の「8列目」にあるなら、概念的には次のように組み立てます（列番号は実データに合わせて調整してください）。
=LET(
id, $A2,
sub, FILTER(Query1!$C$2:$L$xxx, Query1!$C$2:$C$xxx=id),
sorted, SORTBY(sub, CHOOSECOLS(sub, 8), -1),
IFERROR(VLOOKUP(D$1, CHOOSECOLS(sorted, 6, 7), 2, 0), "")
)LETを使うと「FILTERした結果を何度も計算し直す」無駄を減らせるため、データが多い場合ほど効果が出やすいです（Excel 365/2021以降が前提）。
実運用で重くなりやすい理由と、まず効く対策
この手の横展開を数式だけでやると、データ規模によっては計算量が爆発します。とくに「行数が多い」「説明の種類が多い（Colour/Haze/Density…が200種類など）」「Sheet1側のセル数が膨大（ID数×説明数）」の3点が揃うと、再計算が極端に遅くなったり、ファイルを開くだけで固まったりします。
まず効きやすい対策を、現実的な順に並べます。
| 対策 | 効果 | 具体例 |
|---|---|---|
| 全列参照（A:Aなど）をやめる | 計算範囲を減らして劇的に軽くなる | Query1!H:HではなくQuery1!H2:Hxxx |
| Query1をExcelテーブル化する | 最終行xxx管理から解放され、参照も安定 | Ctrl+Tでテーブル化し、tQuery[TEST RESULT]のように参照 |
| 説明の種類を絞る／分割する | 列数が減り、セル総数が減る | ITEM TYPE別にシートを分ける、必要項目だけヘッダーに採用 |
| 計算方法を見直す | 再計算タイミングを制御できる | 手動計算、Power Pivot/Power BIに寄せる |
テーブル参照に置き換えると、式が読みやすく壊れにくい
xxx行の管理に悩まされる場合は、Query1の範囲をテーブル化し、テーブル名を（例）tERPとして、列名参照に置き換えるのがおすすめです。列名は環境に合わせて読み替えてください。
=TRANSPOSE(SORT(UNIQUE(tERP[CHARACTERISTIC DESCRIPTION])))=UNIQUE(tERP[個体ID])=IFERROR(
VLOOKUP(D$1,
CHOOSECOLS(
FILTER(tERP, tERP[個体ID]=$A2),
1, 2
),
2, 0),
"")この場合、FILTERにテーブルそのものを渡し、CHOOSECOLSで「説明列」「結果列」を抜くイメージです。列番号は「FILTER後のテーブル配列の中で何列目か」で数えるため、テーブルの列順を固定しておくとより安全です。
大量データならPower Queryでピボットする方が安定しやすい
数式での横展開は便利ですが、数十万行規模になると限界が来ることがあります。そういうときは、ExcelのPower Query（データの取得と変換）で「縦持ち→横持ち」を作ってしまう方が、結果的に安定します。
Power Queryでの基本手順（概要）
- Query1の範囲をテーブル化（Ctrl+T）
- データタブ → 「テーブルまたは範囲から」
- 不要列を削除（最初はID・説明・結果・更新日時だけ残すのが安全）
- 重複がある場合は、更新日時で並べ替えてから「重複の削除」またはグループ化で先頭行を残す
- 変換タブ → 「列のピボット」：ピボットする列＝説明、値の列＝結果
- 読み込み先をSheet1やデータモデルに指定して完了
| アプローチ | 向いているケース | 注意点 |
|---|---|---|
| 数式（FILTER＋LOOKUP） | 行数が少ない〜中規模／手早く作りたい／説明が頻繁に変わる | セル数が増えると再計算が重くなる |
| Power Query（ピボット） | 行数が多い／更新頻度が高い／安定運用したい | 最初の設定に少し慣れが必要（更新はワンクリック） |
よくあるつまずきとチェックリスト
最後に、同じ構成でハマりやすいポイントをまとめます。エラーが出たときは、上から順に確認すると復旧が早いです。
| 症状 | 原因として多いもの | 対処 |
|---|---|---|
| #N/A が多発する | 説明名が一致していない（スペース、表記ゆれ）／その個体に項目が存在しない | 説明列をTRIMで正規化／IFERRORで空欄化／ヘッダー側のUNIQUE元を見直す |
| 全て同じ値が入る | LOOKUPの検索キーがIDだけになっている／D$1参照がずれている | FILTERでID絞り込み→説明で引く形になっているか確認／$の位置を確認 |
| 別の列の値が返る | CHOOSECOLSの列番号がズレている | FILTER範囲の左端を1として列番号を再計算／CHOOSECOLSだけ単体で出力して確認 |
| ファイルが重い・固まる | 全列参照／xxxが過大／ID×説明のセル数が多すぎる | 参照範囲を実データに限定／テーブル化／Power Queryへ切替／項目を分割 |
まとめ
ERP（TROPOS/Epicor）から出力された「説明列＋結果列」の縦持ちデータは、Excel側で横展開できると分析が一気に楽になります。XLOOKUPが先頭値しか返らないときは、IDでFILTERしたサブ配列を作ってから、説明名をキーに結果を引くのが最短ルートです。
小〜中規模なら数式で十分、データが大きくなったらPower Queryを選ぶ、という住み分けにすると、保守性とパフォーマンスのバランスが取りやすくなります。まずは範囲（xxx）と列番号（CHOOSECOLS）を正しく合わせるところから始めてみてください。
コメント