データを収集するための Excel マクロ

Question

私は Windows 7 SP1 で ms-excel 2016 を使用しています。

私は通常、1つまたは複数のシートを含むファイルワークブックを取得します

これらのファイル/シートからデータを1つのシートのマスターワークブックに収集したい

ほとんどすべてのデータがコピーされますが、一部の列では異なる順序でコピーされます。

シートには次のようなデータがあります:

id, name, address, telephone,branch,area, ...

マスターシートには次の内容が含まれます。

id, name,adress,telephone-1, telephone-2,area,branch

ほとんどの場合、顧客が 2 台の電話機を持っている場合、データ入力システムは次のように 2 行で入力します。

id, name, address, telephone,branch,area
g13, jake, 7 st., 23456343, ,
k12,johne,ca st., 142635, ,
k12,johne,ca st.,150979, ,

マスターシートでは次のようになります:

id, name, address, telephone-1,telephone-2,area,branch,verified_id
g13, jake, 7 st.,456343, , ,
k12,johne,ca st.,142635,150979, , ,k12

このデータが同じ顧客 ID からコピーされたことを確認するために、最後に ID を追加します。

視覚障害者なのでスクリーンショットを見せることができないので、うまく説明できたと思います。

Answer 1

Power Query を使用した例を次に示します。

これは、例で示したように、重複した列では、電話を除いてすべてが同一であると想定しています。そうでない場合は、複数の列でこの手順を実行する必要があるかもしれません。

注釈付き Mコード

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"name", type text}, {"address", type text}, {"telephone", Int64.Type}, {"branch", type any}, {"area", type any}}),
    
    //Group by all the rows EXCEPT "telephone"
    //Do not aggregate (select all rows)
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "name", "address", "branch", "area"}, {{"Grouped", each _, type table [id=nullable text, name=nullable text, address=nullable text, telephone=nullable number, branch=text, area=text]}}),
   
   //extract the phone numbers as a "List"
   #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Telephone", each Table.Column([Grouped],"telephone")),

   //remove unwanted column and put the phone number column where it belongs
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"id", "name", "address", "Telephone", "branch", "area"}),

    //add the verified_id column
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "verified_id", each if List.Count([Telephone]) = 1 then null else [id]),

    //extract the individual phone numbers from the list, and split the columnt
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Telephone", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Telephone", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Telephone.1", "Telephone.2"}),
    
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Telephone.1", Int64.Type}, {"Telephone.2", Int64.Type}})
in
    #"Changed Type1"

Answer 2

マクロは (少なくとも上級者ではないユーザー/プログラマーにとっては)、データに対してまったく同じアクションを実行し、かつデータがまったく同じレイアウトになっている場合にのみ役立ちます。

このためのマクロを作成することは可能だと思いますが、必須ではありません。

私はデータのある最初の行の最後のセルに移動するだけです（B8としましょう）

「B8=B1」と入力すると、B1 の ID が表示されます。

次に、B8 セルをコピーし、データがある B 列の残りのセルをすべて選択して、数式を貼り付けます。

これで、両端に一致する ID が設定されます。

ありがとう、

マイク

Answer 3

半手動のソリューション:

1. すべてを手動でマスターシートに結合します。
2. IDで並べ替え
3. 行 E2 に入力: =IF(A2=A3, D2, "") *
4. 次に、列E（電話2）をコピーして値として貼り付けます。
5. データリボンの「重複を削除」をクリックし、ID列のみを選択すれば完了です。

• 残念ながら、ステップ 3 には小さな問題があります。以前の結合から列 E / 電話 2 に既にデータがある場合です。これを解決するには、ヘッダー行にフィルターを設定し、空白のみをフィルターします。次に、数式をコピーして空白セルのみに貼り付けます。または、F に新しい電話 2 列を作成し、F2 =IF(A2=A3, D3, E2) と入力します。