☰

Excel で別のシートの行を自動的に入力する

tag-icon • tag-icon microsoft-excel worksheet-function
Excel で別のシートの行を自動的に入力する

注文した製品を追跡するために使用しているワークブックがあります。現在、2 つのシートがあります。1 つは各アイテムの在庫リストで、各列にすべての詳細 (製品 ID、最小/最大数量、説明、価格など) が行ごとに整理されています。2 つ目のシートでは、アイテムが注文された日時と発注書番号を追跡しています。現在、在庫シートから注文するアイテムの行をコピーし、発注書番号、日付、注文数量とともに 2 つ目のシートに貼り付けています。これを実行中のリストとして保存し、傾向や注文履歴を確認できるように追加し続けています。2 つ目のシートで製品 ID を入力し、Enter キーを押すと、在庫シートの同じ製品 ID の情報が行に入力される方法はありますか? 何千もの製品 ID とその情報があるため、コピー アンド ペーストをなくすと、膨大な時間を節約できます。

ありがとう。

答え1

そのサイズのコレクションの場合、Access またはその他のデータベース ソリューションの使用を検討する必要があるかもしれません。それでも、やってみます。

Excel の VLOOKUP 関数を使用します。例:

Sheet1のレイアウトは次のようになります

     |     A      |   B   |   C   |   D   |   E   |
1    | Product ID | Max Q | Min Q | Desc. | Price | 
2    |  ######### |   ### |    ## | xxxxx | 34.29 |
...
2359 |  ######### |   ### |    ## | xxxxx | 54.28 |

Sheet2のレイアウトは次のようになります

     |     A     |     B     |    C    |     D     |     E      |   F   |   G   |
1    |Order date | Recv Date | Order Q | P.O. Num. | Product ID | Desc. | Price |
2    |           |           |         |           |            |       |       |

Sheet2 のセル E2 に、Sheet1 の列 A の ID と一致する製品 ID を入力します。Sheet2 のセル F2 に数式を入力し=VLOOKUP(E2,Sheet1!$A$2:$E$2359,4,TRUE)、G2 に数式を入力します=VLOOKUP(A1,Sheet1!$A$1:$H$2359,5,TRUE)。範囲参照は絶対参照な$ので、数式がページをコピーされても変更されません。3 番目のパラメータは、必要な値を持つ範囲内の列です。たとえば、Sheet1 が列 H から始まる場合、範囲は次のように変更されますが、Sheet1!$H$2:$L$2Desc. の 4 と Price の 5 は次のように変更されます。ない変更します。名前付き範囲を使用すると、製品を追加するたびに数式の範囲を更新する手間を省くことができます。詳細については、Excel のヘルプを参照してください。製品 ID 列は、報告されたデータセットのサイズに基づいて最適な効果を得るために並べ替える必要がありますが、VLOOKUP では必須ではありません。また、数式を 2 行目から必要な数式までコピーする必要があります (行数が何千行になるかは関係ありません)。シートに名前がある場合は、次のように一重引用符で囲みます=VLOOKUP(E2,'Inventory List'!$A$2:$E$2359,4,TRUE)。数式では、1 番目の引数E2は一致させるセル、2 番目の引数Sheet1!$A$1:$H$2359は検索する範囲です。検索はのみ行われた初め列、3 番目の引数は返されるデータの範囲内の列であり、4 番目の引数はTRUE完全一致を強制します。

真剣に考えてみましょう。このような大規模なデータセットをデータベースに移行することを検討してください。今は作業が増えますが、長期的には、手間をかけずに簡単に傾向を見つけられるなど、はるかに良い結果が得られます。

関連情報