Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2019)
セルの値を関数に使いたい(INDIRECT以外で)
投稿日時: 22/08/30 17:19:49
投稿者: ラングドシャ

よろしくお願いいたします。
  
c:\リスト に返品リスト.xlsxがあります。
c:\リスト\DL にダウンロードした返品詳細_220801.xlsxがあり、毎週ダウンロードして
いきます。※青字部分は可変でダウンロード日が入ります。
  
返品詳細_220801.xlsxは週ごとのデータなので、返品リスト.xlsxにまとめていきたいのですが
なんとかある程度まで自動でできないかと思案しています。
  
<返品リスト.xlsx>

	A	B	C	D
1	品番	220801	220808	
2	1001	5		
3	2001		8	
4	3001		3	
5	A001	1		

 
  
B2:D5はあらかじめ品番を検索値に使うVLOOKUP 又は INDEX・MATCHの関数が入っており
上表の場合、D1にダウンロードファイル名と同じ220815を入力したら、数字が表示されるように
したいです。(ファイル名:返品詳細_220815.xlsxとする)
INDIRECT関数で考えましたが、元ファイルを開かないといけないので実用的ではないと
思いました。
  
元ファイルを開かず、セルの値を関数に使う方法はINDIRECT関数しかないでしょうか?
何かアドバイスいただけると幸いです。

投稿日時: 22/08/30 17:35:59
投稿者: ラングドシャ

変な文章になっていました。
>元ファイルを開かず、セルの値を関数に使う方法はINDIRECT関数しかないでしょうか?
 ↓
元ファイルを開かず、セルの値を関数に使う方法はINDIRECT関数以外にはないでしょうか?

回答
投稿日時: 22/08/30 18:54:21
投稿者: んなっと

PowerQueryならフォルダー内の同形式ブックを統合できます。
希望とは違いますが、もしかしたら使えるかもしれません。
  
●返品詳細_220801.xlsx
   A   B
1 品番 個数
2 1001   5
3 A001   1
  
●返品詳細_220808.xlsx
   A   B
1 品番 個数
2 2001   8
3 3001   3
  
上のような形式だとして...
  
●返品リスト.xlsx で
  
 データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分を全選択して削除
→以下の文字列を貼り付け
  
let
    Src = Folder.Files("C:\リスト\DL"),
    Add = Table.AddColumn(Src, "Tbl", each Excel.Workbook([Content],true)),
    Exp = Table.ExpandTableColumn(Add, "Tbl", {"Data", "Item"}),
    Ren = Table.AddColumn(Exp, "Name2", each Text.BetweenDelimiters([Name], "_", "."), type text),
    Del = Table.SelectColumns(Ren,{"Name2", "Data"}),
    Exp2 = Table.ExpandTableColumn(Del, "Data", Table.ColumnNames(Del{0}[Data])),
    Piv = Table.Pivot(Exp2, List.Distinct(Exp2[Name2]), "Name2", "個数")
in
    Piv
 
→完了
→閉じて読み込む
  
   A    B    C
1 品番 220801 220808
2 1001    5    
3 2001        8
4 3001        3
5 A001    1    
  
→C:\リスト\DL に追加されたら データ→すべて更新

回答
投稿日時: 22/08/30 22:04:38
投稿者: WinArrow
投稿者のウェブサイトに移動

ラングドシャ さんの引用:

元ファイルを開かず、セルの値を関数に使う方法はINDIRECT関数以外にはないでしょうか?

 
元ファイルを開いては池に理由は_

投稿日時: 22/08/31 10:25:54
投稿者: ラングドシャ

んなっと様
ありがとうございます。
パワークエリって本当にすごいですね。
私が考えいた方法よりずっとスマートです。しかもファイルサイズも軽いですし。
実際のダウンロードファイルに、項目がもう少しありましたが列の削除でうまくいきました。
 
すみません、一つだけ教えてください。
 
ダウンロード名をうろ覚えで書いてしまったのですが
返品詳細_999999_220801.xls
といった感じで、アンダーバーが2つでした。
 
詳細エディターの
    Ren = Table.AddColumn(Exp, "Name2", each Text.BetweenDelimiters([Name], "_", "."), type text),
この部分が取得ファイル名かと思うのですが、「二つ目のアンダーバー」と「ピリオド」の間
と書き換えるにはどのようにすればよろしいでしょうか?
 
追加質問申し訳ありません。
よろしくお願いいたします。

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

Text.BetweenDelimiters([Name], "_", ".")

Text.BetweenDelimiters([Name], "_", ".",1)
に変更。
 
赤字の部分は、
0[または省略]が1個目の"_"
1が2個目の"_"
2が3個目の"_" に対応します。

投稿日時: 22/08/31 13:36:49
投稿者: ラングドシャ

んなっと様
ありがとうございました。
Text.BetweenDelimiters([Name], "_", ".",1)
に変更後、きちんと反映されました。
 
たびたび申し訳ございません。もう一つ教えてください。
仮に
 
●返品詳細_999999_220801.xlsx
   A   B
1 品番 個数
2 1001   5
3 A001   1
   
●返品詳細_999999_220808.xlsx
   A   B
1 品番 個数
2 2001   8
3 3001   3
4 A001   2
 
これで実行すると、以下の様に同じ品番が2行になってしまいました。
   A    B    C
1 品番 220801 220808
2 1001    5    
3 2001        8
4 3001        3
5 A001    1    
6 A001        2
 
下のようにしたいのですが
   A    B    C
1 品番 220801 220808
2 1001    5    
3 2001        8
4 3001        3
5 A001    1    2  
 
 
テストとして、1つのダウンロードファイルをコピーしてファイル名を変更して実行した時は、下の表の
ように同じ品番は1行にまとまっていたので、これは元データの問題でしょうか?
元データの確認すべき点などありますか?
何度もすみません、よろしくお願いいたします。

回答
投稿日時: 22/08/31 14:34:30
投稿者: んなっと

●余計な半角スペースが付いていれば
   A    B    C
1 品番 220801 220808
2 1001    5    
3 2001        8
4 3001        3
5 A001    1    
6 A001␣       2
 
最後の手前で 品番列の上で右クリック→変換→トリミング の操作をすればよいだけです。
let
    Src = Folder.Files("C:\リスト\DL"),
    Add = Table.AddColumn(Src, "Tbl", each Excel.Workbook([Content],true)),
    Exp = Table.ExpandTableColumn(Add, "Tbl", {"Data", "Item"}),
    Ren = Table.AddColumn(Exp, "Name2", each Text.BetweenDelimiters([Name], "_", ".",1,0), type text),
    Del = Table.SelectColumns(Ren,{"Name2", "Data"}),
    Exp2 = Table.ExpandTableColumn(Del, "Data", Table.ColumnNames(Del{0}[Data])),
    Trim = Table.TransformColumns(Exp2,{{"品番", Text.Trim, type text}}),
    Piv = Table.Pivot(Trim, List.Distinct(Trim[Name2]), "Name2", "個数")
in
    Piv
のようになります。
 

引用:
実際のダウンロードファイルに、項目がもう少しありましたが列の削除でうまくいきました。

ほかの項目が残っている場合も考えられますが
 
   A    B    C   D
1 品番  担当 220801 220808
2 1001   あ    5    
3 2001   い        8
4 3001   い        3
5 A001   あ    1     
6 A001   い        2 
 
さすがにこれなら気が付きますよね。
 
●そちらの元データの構成も不明ですし、原因はわかりません。
無理ならあきらめて別の方法を探してください。

投稿日時: 22/08/31 15:13:32
投稿者: ラングドシャ

んなっと様
何度もありがとうございました。
ダウンロードファイルをいろいろ調べてみましたが原因がわかりません。
 
取り急ぎ、作成したクエリをピボットテーブルにすることにしました。
ピボットテーブルでは同じ品番は1行にまとめられています。
 
ワンクッション作業が多くなりますが、きれいにまとまりました。
引き続き、元ファイルは調べますが、当面これで運用してみます。
 
ありがとうございました。