Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Home : Excel 2016)
複数帳票における相違キーの検索方法
投稿日時: 21/06/22 05:18:34
投稿者: kou5355

顧客管理を複数の帳票で行っています。
 
@顧客コード一覧表.xlsx
 
001 A商事
002 B産業
003 C建設
 
A顧客契約額一覧表.xlsx
 
001 A商事 10,000円
002 B産業 20,000円
003 C建設 15,000円
004 D鉄鋼 30,000円
 
 
上記の2帳票があるとし、@はマスタデータとして顧客が
増えるたび新しく追記採番します。Aは当初、@のマスタデータをもとに
作成するので3行目(C建設)までしか行が存在しませんが、
表を利用する部門が異なるため、4行目「D鉄鋼」が挿入されることがあります。
(総務がマスタを作る前に営業担当が実績を入力してしまったイメージ)
Aの表は定期的に@の表を管理する部門が回収・確認しています。
 
 
 
このような状況において、
 「A顧客契約額一覧表.xlsx」に存在し、
 「@顧客コード一覧表.xlsx」に存在しない顧客
 
 「A顧客契約額一覧表.xlsx」に存在せず、
 「@顧客コード一覧表.xlsx」に存在する顧客
 
をそれぞれ顧客コードをキーに判断するためにはどのような
方法が好ましいでしょうか。
 
 
※実際のそれぞれの表は項目数、行数ともに多いため1つの表にまとめて
運用するということは不可能です。また、データの保管場所の都合上
それぞれの表を参照させるという機能は利用できません。

回答
投稿日時: 21/06/22 06:32:27
投稿者: Mike

》 それぞれの表を参照させるという機能は利用できません
そういうことなら、当然のことながら、次の歌に従わざるを得ない!
https://www.youtube.com/watch?v=NYmp8orHja0

回答
投稿日時: 21/06/22 10:11:41
投稿者: んなっと

E:\office\Excel\顧客コード一覧表.xlsxSheet1ID
 
   A   B
1  ID  名前
2 001 A商事
3 002 B産業
4 003 C建設

E:\office\Excel\顧客契約額一覧表.xlsx
Sheet1ID
 
   A   B     C
1  ID  名前   金額
2 001 A商事 10,000円
3 002 B産業 20,000円
4 003 C建設 15,000円
5 004 D鉄鋼 30,000円

で比較する場合。新規ブックで
 
 データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分を全選択して削除
→以下の文字列を貼り付け[赤字部分はそちらの状況に合わせて変更]
  
let
    Src = Excel.Workbook(File.Contents("E:\office\Excel\顧客コード一覧表.xlsx"), null, true),
    Sht = Src{[Item="Sheet1",Kind="Sheet"]}[Data],
    Prt = Table.PromoteHeaders(Sht, [PromoteAllScalars=true]),
    Src2 = Excel.Workbook(File.Contents("E:\office\Excel\顧客契約額一覧表.xlsx"), null, true),
    Sht2 = Src2{[Item="Sheet1",Kind="Sheet"]}[Data],
    Prt2 = Table.PromoteHeaders(Sht2, [PromoteAllScalars=true]),
    Mrg = Table.NestedJoin(Prt2, {"ID"}, Prt, {"ID"}, "クエリ1", JoinKind.LeftAnti),
    Del = Table.RemoveColumns(Mrg,{"クエリ1"})
in
    Del
  
→完了
→閉じて読み込む
 
 
 
●上と同様にして、もうひとつ
 
let
    Src = Excel.Workbook(File.Contents("E:\office\Excel\顧客コード一覧表.xlsx"), null, true),
    Sht = Src{[Item="Sheet1",Kind="Sheet"]}[Data],
    Prt = Table.PromoteHeaders(Sht, [PromoteAllScalars=true]),
    Src2 = Excel.Workbook(File.Contents("E:\office\Excel\顧客契約額一覧表.xlsx"), null, true),
    Sht2 = Src2{[Item="Sheet1",Kind="Sheet"]}[Data],
    Prt2 = Table.PromoteHeaders(Sht2, [PromoteAllScalars=true]),
    Mrg = Table.NestedJoin(Prt, {"ID"}, Prt2, {"ID"}, "クエリ1", JoinKind.LeftAnti),
    Del = Table.RemoveColumns(Mrg,{"クエリ1"})
in
    Del
 
●元のブックに増減があったら
 データ
→すべて更新

投稿日時: 21/06/22 12:16:09
投稿者: kou5355

Mike さんの引用:
》 それぞれの表を参照させるという機能は利用できません
そういうことなら、当然のことながら、次の歌に従わざるを得ない!
https://www.youtube.com/watch?v=NYmp8orHja0

 
了解しました。ありがとうございます。

トピックに返信