아무거나질문

엑셀 - 중복데이터 갯수를 구하고 순위대로 나열하기 4

2016-03-25 10:54:25 218.♡.159.141
직퀘

1.jpg

그림에 나와있는 동일한 품명(D열)의 수량(I열)을 모두 구하고 순위(M열)를 정하여 순위(M열) 기준으로 품명(N열),수량(O열)을 나열하는 방법을 찾고 있습니다.

 

데이터를 추가 입력할때마다 순위는 자동으로 변경이 될 수 있도록 말입니다.

 

도와주세요. ㅠㅠ

 

 

추신 - 품명의 종류가 너무 많아 전부 기록할수가 없어 countif를 사용하지는 못할것 같습니다. 자동으로 품명이 N열에 입력되어야합니다.

직퀘님의 게시글 댓글
kylon
IP 175.♡.149.18
03-25 2016-03-25 13:17:41 / 수정일: 2017-04-30 22:24:17
·
D열을 다른 시트에 복사하고 중복제거 한번 하시구요
그럼 모든 품목이 한번씩 빠짐엇이 들어가니까 그걸 N열에 넣으시고
sumif로 각각 수량 구하시구요
내림차순 정렬 하시면 되겠네요
DSFord
IP 112.♡.204.252
03-25 2016-03-25 13:18:22 / 수정일: 2017-04-30 22:24:17
·
한줄정도 더 쓰면 함수로 가능할것같네요. 모바일이라 좀 걸릴것같으니 대충되면 쪽지보내드릴게요.

다만 함수로하면 그만큼의 용량은 써야합니다.
어느정도까진 미리 함수를 입력해야할테니까요.
DSFord
IP 112.♡.204.252
03-25 2016-03-25 14:23:30 / 수정일: 2017-04-30 22:24:17
·
https://www.dropbox.com/s/3vbtx9qy5zv2hn4/sumrank.xlsx?dl=0

참고파일입니다.
함수로 하는만큼 K, M~O열은 넉넉하게 채우기핸들이든 뭐든 해서 미리 수식이 입력되어 있어야 합니다.


[K6]
=IFERROR(IF(MATCH(D6,D:D,0)=ROW(),SUMIF(D:D,D6,I:I)+1-10^-ROUNDDOWN(LOG10(COUNTA(D:D))+1,0)*ROW(),""),"")

=IFERROR( IF(중복제거,수량합+중복제거용소수부분,"") ,"")
크게 3부분으로 나눠서 설명하면

MATCH(D6,D:D,0)=ROW()
중복여부를 확인하는 부분입니다. MATCH함수는 특성상 중복된 항목이 있어도 먼저찾은걸 답으로 내놓기때문에 그렇게 나온 숫자와 현재 행번호가 일치하는것만 뽑으면 중복없이 하나씩만 뽑아올 수 있습니다.

SUMIF(D:D,D6,I:I)
수량의 합을 구합니다. 이걸 기준으로 순위를 찾을겁니다.

+1-10^-ROUNDDOWN(LOG10(COUNTA(D:D))+1,0)*ROW()
엑셀에서 순위를 표시할 때 골치아픈것 중 하나가 "중복"일때 처리입니다. 차후 순위를 기준으로 품목과 수량을 가져와야 하는데 수량이 같아 순위가 같아져 버리면 이걸 수식에서 구분할 방법이 없거든요. 저는 개인적으로 소수부분을 이용해 중복된 수량이 없도록 하려고 합니다. 물론 수량은 자연수여야 겠지요. 다만 저렇게 복잡하게 할건없고 +1-0.0000001*row() 같이 소수이하 자리를 충분히 줘도 됩니다. 위의 수식은 혹여 문제생길걸 방지해 자동으로 소수자리수를 조절하려고 복잡하게 했습니다.

또, 1에서 빼는 방식이라 중복수가 나오면 먼저 나온게 순위가 더 높도록 해놨습니다.

IFERROR는 에러방지차원에서 쓴겁니다.


[M6] 순위
=IF(ROW(M1)<=COUNT(K:K),ROW(M1),"")

기본적으론 순위기준 정렬이니 1,2,3,... 을 필요한 만큼만 쓰면 되겠죠.
K열의 숫자개수 보다 크면 공백으로 나오게 합니다.
참고로 M~O가 나중에 수식을 미리 입력안해두기 딱 좋은데요, 이를테면 M4에 =COUNT(K:K)=COUNT(O:O) 식으로 해둬서 FALSE가 뜨면 채우기핸들을 더 해주면 되겠죠.

[N6] 품목
=IFERROR(INDEX(D:D,MATCH(LARGE(K:K,M6),K:K,0)),"")

[L6] 수량
=IFERROR(ROUNDDOWN(INDEX(K:K,MATCH(N6,D:D,0)),0),"")

품목과 수량은 기본적으로 INDEX+MATCH조합으로 같습니다. 다만 수량은 K열쪽을 가져오니 중복을 피하기 위해 쓴 소수부분을 없애기 위해 ROUNDDOWN함수를 쓰고, 전체적으로 IFERROR함수를 써서 좌측열이 공백이면 (에러가 나므로) 역시 공백으로 나오게 했습니다.

수량은 저렇게 안해도 좌측에 값이 있으면 SUMIF로 다시 합계를 구하는 방식을 쓰셔도 무방합니다.
삭제 되었습니다.
노아이뎌
IP 183.♡.37.119
03-26 2016-03-26 07:31:11 / 수정일: 2017-04-30 22:24:17
·
가장 간단하게 하시려면 입력될 범위로 넓게 피벗테이블 만들어 놓으시고, 정렬을 갯수 기준으로 하신 후, 내용 추가 입력되면 업데이트만 하면 될 것 같은데요.
목록으로
글쓰기
글쓰기
목록으로 댓글보기