[[20230918091625]] 『複数のファイル(3個以上)を1つに集約したい。』(きょーー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数のファイル(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


すみません。もう1点追加で。
項目名?のパターンはあらかじめ予想できますか?
 (最終的な項目行があらかじめ想定できますか?)

(もこな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


なかなかうまくいきません、、、
(きょーー) 2023/09/18(月) 15:10:29

>なかなかうまくいきません、、、
どの部分が"うまく"いきませんか?
現状のコードを見せていただければアドバイスできることがあるかもしれません。

なお、"質問"ではなく"作成依頼"だということなら、その旨明示頂いたほうがお互いの考えにすれ違いが無くてよいと思います。

(もこな2) 2023/09/18(月) 18:07:28


 >・優先ルールは最新版を採用したいです。ファイル名の後者です。
 >例:ファイル1よりファイル2を優先。ファイル2よりファイル3を優先

 実際のファイル名も連番なのでしょうか?
 そうでない場合、何をもって最新版と判断するのでしょうか。

 ファイル数は増減するのでしょうか。
 各ファイルのシート名はきまっているのでしょうか。

(マナ) 2023/09/18(月) 18:15:41


どの部分が"うまく"いきませんか? パワークエリを利用しているのですが、なにをどうすればよいのかという意味でうまくいっていません、、、
(きょーー) 2023/09/19(火) 23:00:33

>実際のファイル名も連番なのでしょうか?
連番です。ファイル1、ファイル2という名前にしています。

>ファイル数は増減するのでしょうか。
減少するとこはありませんが、増えることはあります。

>各ファイルのシート名はきまっているのでしょうか。
とくに設定していません。何にでも変更可能です。

(きょーー) 2023/09/19(火) 23:02:47


「サンプルファイルの変換」を編集してください
https://ascii.jp/elem/000/004/136/4136779/3

(マナ) 2023/09/20(水) 08:24:14



404 Not Found
ページが見つかりませんでした
(?) 2023/09/20(水) 08:46:09

 検証、不十分ですが....時間切れなのでアップします。
 「★対象フォルダ名」の部分を、自身の環境に合わせて修正してください。
 最新版の定義が、あいまいなのでファイルの更新日時順としました。

 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


h ttps://excelfactory.net/excelboard/exgeneral/excel.cgi?mode=all&namber=133973&rev=0
↑と同じ方ですかね。。
まずは、シートで試してみてはいかがでしょう。
 Sheet1をまとめファイルのシートに見立てる
 Sheet2をファイル名「1」にある物と見立てる
 Sheet3をファイル名「2」にある物と見立てる

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


私は、本来解答例そのものを掲載するのは好きではないのですが、
VBAのサンプルコードが掲載されたので、
私の作ったワークシート関数のものも掲載しておきます。

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

>2021で VSTACK は使えないと思いますよ。
大変失礼いたしました。
ご指摘ありがとうございます。

(匿名) 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.