파워 쿼리로 중복 항목에 순번 부여하기

1. 문제

왼쪽 키 값을 보면 A가 2개, B가 2개이고, D는 1개입니다.

이와 같은 경우 2개 이상이면 하이픈에 순번을 연결해서 보여주고, 1개면 키값만 표시하도록 하려고 합니다.

중복 항목에 순번 부여하기.xlsx

0.02MB

 

2. 해법

가. 함수 이용

함수를 이용하면 간단하게 구할 수 있습니다.

항목이 같은 것이 있는지 세서 2개 이상이면 하이픈에 순번을 연결하도록 하고, 1개면 키 값만 표시하도록 CountIf와 &연산자를 이용해 만들면 됩니다.

=IF(COUNTIF(표2[키],표2[@키])>=2,표2[@키]&"-"&COUNTIF($A$2:A2,A2),A2)

위 수식에서 표2[키]는 $A$2:$A$15이며, 표2[@키]는 A2이고,

$A$2:A2라고 쓴 것은 A2셀은 고정하고, 아래로 내려가면서 범위가 늘어나도록 하기 위한 것입니다. 다시 말해, 한 칸 내려가면 $A$2:A3가 되니까 A가 2가 되고, & 연산자로 연결하면 A-2가 되는 것입니다.

=IF(COUNTIF($A$2:$A$15,A2)>=2,A2&"-"&COUNTIF($A$2:A2,A2),A2)라고 셀 주소 바꿔도 결과는 같습니다.

 

나 파워 쿼리 이용

파워 쿼리를 이용하면 전체 개수를 구하고, 중복 항목에 순번을 부여하고, 전체 개수에 따라 키값과 순번을 연결하는 작업을 순서대로 해야 합니다.

아래 동영상에서 힌트를 얻었습니다.

https://youtu.be/7ypdbuCeBtk?si=YjAQWr8gmZFbt5PC

 

​(1) 표를 복제해서 하나 더 만듦 -> 표(2) 생성

 

(2) 표에서 작업

① 그룹화를 하는데, 모든 행으로 그룹화. 그러면 키 구분별로 키가 들어 있는 테이블이 만들어짐

​

② 그룹별로 인덱스 열을 추가함

 

③ 인덱스 테이블을 확장해서 인덱스 열(일련번호)만 남겨 둠

 

(3) 표(2)에서 작업

키별(그룹별) 개수를 알아내기 위해 그룹화하는데 이번에는 개수로 함

​

(4) 표와 표(2)를 병합

병합하고, 표(2)를 확장한 후 개수만 남겨 둠

(5) 조건에 따라 키와 일련번호를 &로 결합

[키]는 문자이고, [일련번호]는 숫자라 숫자를 문자로 변환하기 위해 Text.From 함수를 사용함

​

불필요한 필드는 제거한 다음 표로 변환하면 되는데,

이해하기 쉽도록 이 과정은 진행하지 않았습니다.

 

(6) 연결만 만들기

홈 탭에서 '닫기 및 로드'아래 '닫기 및 다음으로 로드'를 누르고,

엑셀에서 연결만 만들기를 선택하고 확인 버튼을 누릅니다.

 

(7) 표로 만들기

쿼리 및 연결창에서 병합1에서 마우스 오른쪽 버튼을 누르고,

다음으로 로드를 누른 다음

표와 기존 워크시트의 H1셀을 클릭한 다음 확인 버튼을 누르면

 

아래와 같이 키와 키와 일련번호가 연결된 열이 보입니다.

 

 

중복 항목에 순번 부여하기(Power Query).xlsx

0.02MB