[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数のファイル(3個以上)を1つに集約したい。』(きょーー)
質問です。
複数のファイルの情報を1つに集約したいです。
ただし、複数のファイルの中のデータには名前が一緒でそれ以外が違うものもあります。その場合はそのデータを結合したいです。
ファイル1
名前 住所 電話
A 〇〇 〇〇
B △△ △△
ファイル2
名前 住所 大学
A 〇〇 〇〇
C □□ □□
この複数ファイルを以下のようまとめたい。
↓
まとめファイル
名前 住所 電話 大学
A 〇〇 〇〇 〇〇
B △△ △△
C □□ □□
最後にファイル1やファイル2にデータを追加したらまとめファイルにも反映できるとなお嬉しいです。また、新しい情報が更新されたらそちらを優先したいです。(例:Aさんの住所がファイル3で変更になったら、これを優先したい。)
< 使用 Excel:Excel2021、使用 OS:Windows11 >
>名前が一緒でそれ以外が違う
【同姓同名】はいないという理解でよいですか?
>複数のファイル
例示のように、ファイル1とファイル2の両方にAさんがいて、【住所が違っていたら】どちらを優先するとかルールはありますか?
(もこな2) 2023/09/18(月) 09:42:19
(最終的な項目行があらかじめ想定できますか?)
(もこな2) 2023/09/18(月) 09:45:21
・同姓同名はいません。
・優先ルールは最新版を採用したいです。ファイル名の後者です。
例:ファイル1よりファイル2を優先。ファイル2よりファイル3を優先
・最終的な項目行は決まっているので、想定できます。
(きょーー) 2023/09/18(月) 10:03:56
ファイルの新しい順にブックを開き データ側の項目行に足りない分の項目を追加(重複しなければ順番は問わない) 作業用のシートに【フィルタオプション】で最終的な項目順でデータを抽出 ↑を集積用のシートに転記
全部集積し終わったら集積用のシートの名前列をキーに【重複の削除】を実行
のようにすれば目的は達成できそうな気がします。
(もこな2) 2023/09/18(月) 11:24:52
達成できない気がします。
(??) 2023/09/18(月) 14:20:28
なお、"質問"ではなく"作成依頼"だということなら、その旨明示頂いたほうがお互いの考えにすれ違いが無くてよいと思います。
(もこな2) 2023/09/18(月) 18:07:28
>・優先ルールは最新版を採用したいです。ファイル名の後者です。 >例:ファイル1よりファイル2を優先。ファイル2よりファイル3を優先
実際のファイル名も連番なのでしょうか? そうでない場合、何をもって最新版と判断するのでしょうか。
ファイル数は増減するのでしょうか。 各ファイルのシート名はきまっているのでしょうか。
(マナ) 2023/09/18(月) 18:15:41
どの部分が"うまく"いきませんか? パワークエリを利用しているのですが、なにをどうすればよいのかという意味でうまくいっていません、、、
(きょーー) 2023/09/19(火) 23:00:33
>ファイル数は増減するのでしょうか。
減少するとこはありませんが、増えることはあります。
>各ファイルのシート名はきまっているのでしょうか。
とくに設定していません。何にでも変更可能です。
(きょーー) 2023/09/19(火) 23:02:47
(マナ) 2023/09/20(水) 08:24:14
検証、不十分ですが....時間切れなのでアップします。 「★対象フォルダ名」の部分を、自身の環境に合わせて修正してください。 最新版の定義が、あいまいなのでファイルの更新日時順としました。
let BinaryToTableData = (file as binary,TableName as text,最新順 as number,optional IsHeader as logical) as table=> let header=if IsHeader=false then false else true, xlsx = Excel.Workbook(file, header, true),//データ型を自動判別しない ret = xlsx{[Item=TableName,Kind="Table"]}[Data], AddColumn = Table.AddColumn(ret, "最新順", each 最新順) in AddColumn, Dir="C:\Users\xxxx\Desktop\新しいフォルダー", //★対象フォルダ名 ソース = Folder.Files(Dir), 並べ替えられた行 = Table.Sort(ソース,{{"Date modified", Order.Descending}}), 追加されたインデックス = Table.AddIndexColumn(並べ替えられた行, "最新順", 1, 1, Int64.Type), AddColumn = Table.AddColumn(追加されたインデックス, "Data", each BinaryToTableData([Content],"テーブル1",[最新順])), TB=Table.Combine(AddColumn[Data]), ColumnNames=List.RemoveItems(Table.ColumnNames(TB), {"名前","最新順"}), グループ化された行 = Table.Group(TB, {"名前"}, {{"Group", each [ tmp=Table.RemoveColumns(_,{"名前"}), Sort = Table.Sort(tmp,{{"最新順", Order.Ascending}}), Remove=Table.RemoveColumns(Sort,"最新順"), ret=Table.FirstN(Remove,1)][ret] , type table}}), Expand = Table.ExpandTableColumn(グループ化された行, "Group", ColumnNames, ColumnNames) in Expand (まる2021) 2023/09/20(水) 08:50:04
肝心なことを、書くのを忘れました。
各ファイルのデーターはテーブル化して、 全て同じ名前「テーブル1」であることを前提にしてます。 (まる2021) 2023/09/20(水) 08:53:00
>「サンプルファイルの変換」を編集してください >https://ascii.jp/elem/000/004/136/4136779/3
最後の3をとったURL https://ascii.jp/elem/000/004/136/4136779/ Power QueryでExcelの複数ファイルを一括で整形して結合する方法 というページになるがこれだろうか? (ねむねむ) 2023/09/20(水) 08:53:55
(マナ) 2023/09/20(水) 09:03:36
>「サンプルファイルの変換」を編集してください
具体例: ・1行目をヘッダーとして使用 ・[名前]列を選んで「その他の列のピボット解除」 ・適用されたステップで「変更された型」を削除
これで、↓の感じで結合できます。
Source.Name 名前 属性 値 ファイル1.xlsx A 住所 〇〇 ファイル1.xlsx A 電話 〇〇 ファイル1.xlsx B 住所 △△ ファイル1.xlsx B 電話 △△ ファイル2.xlsx A 住所 〇〇 ファイル2.xlsx A 大学 〇〇 ファイル2.xlsx C 住所 □□ ファイル2.xlsx C 大学 □□
(マナ) 2023/09/20(水) 09:41:53
4列5行くらいまでのサンプルデータを入力する。
実際にワークシート関数などを使ってやってみる。
UNIQUE
VSTACK
XLOOKUP
COUNTA
INDIRECT
FILTER
Sheet2とSheet3を実際のファイルパスに置き換える。
私は上記の様なテストデータを作成し、
実施テストをした結果を確認して、
別サイトに回答をつけています。
この様に、まずは「小さく作って小さく実行」をする方が、
早く解決する事も多いと思います。
(匿名) 2023/09/20(水) 10:13:52
マナさんのヒントを見てわかったので、試しにやってみました
let ソース = Folder.Files("d:\test"), /* フォルダ名は実際に合わせて修正 */ ファイル名で降順に並べ替えられた行 = Table.Sort(ソース,{{"Name", Order.Descending}}), 読み込まれたSheet1 = Table.TransformColumns(ファイル名で降順に並べ替えられた行,{"Content",each Excel.Workbook(_){[Item="Sheet1",Kind="Sheet"]}[Data]}), /* Sheet1を読む */ 削除されたContent以外の列 = Table.SelectColumns(読み込まれたSheet1,{"Content"}), 昇格されたヘッダー = Table.TransformColumns(削除されたContent以外の列,{"Content",each Table.PromoteHeaders(_, [PromoteAllScalars=true])}), ピボット解除されたテーブル = Table.TransformColumns(昇格されたヘッダー,{"Content",each Table.UnpivotOtherColumns(_, {"名前"}, "属性", "値")}), 展開されたContent = Table.ExpandTableColumn(ピボット解除されたテーブル, "Content", {"名前", "属性", "値"}, {"Content.名前", "Content.属性", "Content.値"}), 削除された重複 = Table.Distinct(展開されたContent, {"Content.名前", "Content.属性"}), ピボットされた列 = Table.Pivot(削除された重複, List.Distinct(削除された重複[Content.属性]), "Content.属性", "Content.値") in ピボットされた列 (´・ω・`) 2023/09/20(水) 14:00:23
どの程度Power Queryを使ったことがあるか不明なので追記
今回の例では「ファイルから/フォルダから」で、 単純に結合したのでは、手詰まりで処理を続けられません。 なので「サンプルファイルの変換」を編集することで 各ファイルのデータをピボット解除してから結合します。
これで後続の処理が簡単になります。 具体的な操作は、こんな感じです。
・[Source.Name]列を選び、置換で"ファイル"を削除 ・[Source.Name]列を選び、置換で".xlsx"を削除 ・[Source.Name]列の型を整数に変換 ・[Source.Name]列で、降順ソート ・インデックス列の追加 ・[名前][属性]列で、重複の削除 ・[名前][属性][値]列を選び、「他の列の削除」 ・[属性]列を選び、「列のピボット」 ・閉じて読み込む 。 この方法なら、コードを直接書き換える必要もなく、 プレビュー画面で確認しながら 操作するだけでできます。 殆ど使ったことないのであれば それでも苦労するかもしれません。 (マナ) 2023/09/20(水) 23:35:01
理解でいなくてよい。コピペして動けばそれでよい。 ということであれば、 Power Queryでなく、マクロで。 ・まとめファイル.xlsmと、ファイル1〜3.xlsxを同じフォルダに保存 ・まとめファイル.xlsmの1行目に見出しを入力しておく ・まとめファイル.xlsmに、下記マクロをコピペし実行
Sub test() Dim srtl As Object, dicX As Object, dicY As Object Dim p As String, fn As String Dim n As Long Dim ws As Worksheet Dim e, v, w() As String Dim i As Long, j As Long, key As String Dim posX2 As Long, posX3 As Long, posY As Long
Set srtl = CreateObject("system.collections.sortedlist")
p = ThisWorkbook.Path & "\" fn = Dir(p & "ファイル*.xlsx")
Do While fn <> "" n = Val(Replace(fn, "ファイル", "")) If n > 0 Then With Workbooks.Open(p & fn) srtl(n) = .Sheets(1).Range("A1").CurrentRegion.Value .Close False End With End If fn = Dir() Loop
Set dicX = CreateObject("scripting.dictionary") Set dicY = CreateObject("scripting.dictionary")
Set ws = ThisWorkbook.Sheets(1) ws.UsedRange.Offset(1).ClearContents
For Each e In ws.Range("A1").CurrentRegion.Value dicX(e) = dicX.Count Next
For i = 1 To srtl.Count v = srtl.getbyindex(i - 1) ReDim Preserve w(dicX.Count, dicY.Count + UBound(v)) posX2 = dicX(v(1, 2)) posX3 = dicX(v(1, 3))
For j = 2 To UBound(v) key = v(j, 1) If Not dicY.exists(key) Then dicY(key) = dicY.Count posY = dicY(key) w(0, posY) = key w(posX2, posY) = v(j, 2) w(posX3, posY) = v(j, 3) Next Next
ws.Range("A2").Resize(dicY.Count, dicX.Count).Value _ = Application.Transpose(w)
End Sub (マナ) 2023/09/21(木) 00:13:30
(きょーー) 2023/09/21(木) 21:57:54
Sheet2
A列 B列 1行 1 A 2行 2 B 3行 3 C
Sheet3
A列 B列 1行 1 あ 2行 2 い 3行 6 う 4行 7 え
Sheet2のA1セルに数値の「1」、B1セルに文字の"A"
Sheet3のA1セルに数値の「1」、B1セルに文字の"あ"
という様に、上記のようなサンプルデータを作成します。
Sheet1のA1セル
=UNIQUE(VSTACK(FILTER(INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A))<>"",""),FILTER(INDIRECT("Sheet3!A1:A"&COUNTA(Sheet3!A:A)),INDIRECT("Sheet3!A1:A"&COUNTA(Sheet3!A:A))<>"","")))
Sheet1のB1セル
=XLOOKUP(A1#,INDIRECT("Sheet2!A1:A"&COUNTA(Sheet2!A:A)),INDIRECT("Sheet2!B1:B"&COUNTA(Sheet2!A:A)),"")
Sheet1のC1セル
=XLOOKUP(A1#,INDIRECT("Sheet3!A1:A"&COUNTA(Sheet3!A:A)),INDIRECT("Sheet3!B1:B"&COUNTA(Sheet3!A:A)),"")
そして、参照先のシートをファイル名を含めた文字列にしたら出来ました。
考え方は、
キーを作らないといけないな。
キーを作るために
VSTACKで複数列を1列にできるかな?(使ったことない)。できた
FILTERで空欄を排除しないとけないな。(使ったことある)。できた
UNIQUEで一意にできるな。(使ったことある)。できた
で試す。
キーができたので、
検索するために
XLOOKUPでできるな。(使ったことある)
とやっているうちに、
最終行の取得ができないといけないや。調べてみよ。
COUNTAでできるんだ。
小さく適当なデータを作ってテスト。できた。
INDIRECTを使わないといけないや。
小さく適当なデータを作ってテスト。できた。
あとはガッチャンコするだけ。
できた。
って感じです。
(匿名) 2023/09/22(金) 09:16:05
2021で VSTACK は使えないと思いますよ。
以上、参考まで (笑) 2023/09/22(金) 13:19:23
(匿名) 2023/09/22(金) 18:54:15
(きょーー) 2023/09/24(日) 11:30:15
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.