데이터를 수집하는 엑셀 매크로

Question

저는 windows-7 sp1에서 ms-excel 2016을 사용하고 있습니다.

나는 보통 각각 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

다음은 파워 쿼리를 사용하는 예입니다.

이는 예제에 표시된 것처럼 중복된 열에서 전화를 제외하고 모두 동일하다고 가정합니다. 그렇지 않은 경우 여러 열에 대해 이 절차를 수행해야 할 수도 있습니다.

주석이 달린 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(phone2)를 복사하여 붙여넣습니다.
5. 데이터 리본에서 "중복 제거"를 누르고 ID 열만 선택하면 완료됩니다.

• 안타깝게도 3단계에는 작은 문제가 있습니다. 이전 병합에서 E열/전화 2열에 이미 데이터가 있는 경우입니다. 이 문제를 해결하려면 머리글 행에 필터를 설정하고 공백만 필터링하세요. 그런 다음 수식을 복사하여 빈 셀에만 붙여넣습니다. 또는 F에 새 전화기 2 열을 만들고 F2 =IF(A2=A3, D3, E2)에 입력합니다.