미해결 HOT 엑셀2019윈도우11

RAW에서 상위 10개 내림차순으로 불러오는법 알려주세요 ( 소정 사례해드리겠습니다 )

자

1월 19일 조회 1,378

안녕하세요 엑셀 왕초보입니다.

업무상 sumif , vlookup만 주로 사용해서 다른 함수는 잘 모르는데,, 갑자기 업무를 하다가 필요한 부분이 있어서

도움부탁드립니다. 도움주시는분 소정의 사례해드릴게요 ㅠ

하기 스크린샷 보시면 오른쪽에 지역코드(음영처리)칸에 왼쪽 rawdata 중 하나의 지역코드를 넣으면오른쪽 표에 rawdata로부터 해당 지역코드 소속의 검색명이 총매출액 높은순서 (내림차순)으로 하여 10개의 정보값을 불러오게 하고 싶습니다.지역코드를 바꿔서 입력할때마다 오른쪽 표에 그 지역코드에 소속된 검색명 값에 10개 내림차순으로 불러오고 싶습니다

sumif, vlookup 쓰다가 이거를 하려니 감을 못잡겠습니다.

인덱스나 해서 내림차순으로 불러와야할거같은데...

엑셀파일 첨부드리니 도움부탁드립니다.

0119자료.xlsx 57 KB · 다운로드

#초보질문 #필터·정렬 #SUMIF(S) 함수 #통계 함수

지

지친 토토로 1월 19일

이거 꼭 함수로 해야하나요??

아니면 그냥 원하시는 결과만 나오면 되는건가요?

sheet2 참고해보세요~

0119자료_정렬.xlsx 128 KB

자

자메이카콩 작성자 1월 19일

@지친 토토로 님 함수를 활용해서 해야합니다. 상기내용은 예제라서요 ㅠㅠㅎㅎ

지

지친 토토로 1월 19일

@자메이카콩 님 앗...;;

마

마법의손 1월 19일

삭제된 댓글입니다.

자

자메이카콩 작성자 1월 19일

@마법의손 님 감사합니다!!

자

자메이카콩 작성자 1월 19일

@지친 토토로 님 와 정렬로 어떻게 하신건가요 다들 도움을 많이주셔서 감사할따름입니다 ㅠ

지

지친 토토로 1월 22일

@자메이카콩 님 엇? 함수 안써도 되는건가요? ㅋㅋㅋ 피벗으로 원하는 필터를 선택 조건 중
총 매출액 TOP10만 보이게하고, 총 매출액 기준으로 정렬한 것입니다
피벗이면 간단한 건데... 함수를 쓰면 복잡해지는지라..;;

김

김재규 1월 19일

삭제된 댓글입니다.

TOP-n.xlsx 67 KB

김

김재규 1월 19일

첨부 참조 (M365)

S2 유효성검사: 코드 선택
V2 사용자 입력

Q5, S4, Y4 배열수식-->각각 Q5:Q16, S4:W15, Y4:AC99 자동 작성
(단 셀 서식은 사용자 몫!)

TOP-n.xlsx 68 KB

자

자메이카콩 작성자 1월 19일

@김재규 님 와 감사합니다 ! 보내주신 파일보고 따라해보려하는데 NO에 걸린함수 똑같이 따라쓰면 적용이 될까요 ..? 대단하십니다!

자

자메이카콩 작성자 1월 19일

@김재규 님 저는 진짜 무지했네요.. HSTACK 이란것도 첨들어봅니다...

김

김재규 1월 19일

@자메이카콩 님 반드시 M365에서 작업하세요. CHOOSECOLS, TAKE, SORT, HSTACK, VSTACK은 M365에서 도입된 함수입니다.

M365설치된 PC가 없으면 "Web Microsoft 365"를 Microsoft Edge에서 입력하고 무료가입한 후 파일 열기를 하세요.

김

김재규 1월 19일

@김재규 님 참고:

RAW Data: A2:O430

이름설정 data:=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-1,15)수식으로 인하여 자동적으로 A4:O429(하단부 합계란 제외)가 지정됨. 이경우 A430은 공백이어야함
향후 데이터 크기 (마지막 행)변동에 따라 자동적으로 data영역 재조정됨.

열 크기가 변동될 경우 data:=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-1,15) 마지막 15("O열")을 조정해 주어야 함. 가능한 "열" 크기 변동은 하지 마세요.

자

자메이카콩 작성자 1월 19일

@김재규 님 네 자세한 설명 감사드립니다.
한가지만 더 여쭙겠습니다.

제가드린 자료 보면 Rawdata 및 오른쪽에 조건값들어오는 표에 NO 칸이 있는데
양쪽 다 NO가 기입된 열을 지운상태에서 가능할까요..?

김

김재규 1월 19일

@자메이카콩 님 잠시 기다려 보세요

김

김재규 1월 19일

@김재규 님 Version2 : NO 제외==> 열크기 변동에 따라, R4, W4 수식 변동있음

TOP-n-v2.xlsx 60 KB

자

자메이카콩 작성자 1월 19일

@김재규 님 정말 대단히 감사드립니다. 약소하게 커피쿠폰이라도 사례드리고 싶은데 어떻게 드리면 될까요 ?

그리고 혹시 제가 여쭌자료를 필터함수로는 못할까요 ?
하기 링크 오빠두엑셀 필터 및 소트 함수로 하면 내림차순으로까지는 나오게 할 수 있는데,
상위 10개만 나오게 하는거 관련해서 링크내 내용으로는 못하는 것인지 궁금합니다...

https://www.oppadu.com/%ec%97%91%ec%85%80-filter-%ed%95%a8%ec%88%98/

자

자메이카콩 작성자 1월 19일

@자메이카콩 님 필터함수를 쓰면 상위값 10개만 정제가 되는지 , 또 RAW DATA 조건값이 기보내주신 자료처럼 검색명/조회수/게시물수/총매출액만 나올수 있는건지 궁금합니다

번거로우실텐데 피드백 주셔서 감사드립니다!

김

김재규 1월 19일

@자메이카콩 님 이미 FILTER함수가 사용되었습니다.

LET, FILTER함수는 Excel 2021에 도입된 것으로, 이미 올려준 파일들은 M365에서만 작동되는 CHOOSECOLS, TAKE, SORT, HSTACK, VSTACK함수들이 포함되어 있습니다. M365 UPGRADE를 추천합니다.

김

김재규 1월 20일

@김재규 님 Version3: Excel 2021이전용

TOP-n-v3.xlsx 60 KB

원

원조백수 1월 19일

M365용 Excel
=CHOOSECOLS( TAKE( SORT( FILTER(F3:O1000, C3:C1000*1 = R2*1, "-"), 3, -1), 10), 1,4, 8, 3)

모든버전용
먼저 총매출액부터 수식을 넣고 수식은 아래로 복사
총매출액 =AGGREGATE(14, 6, ($C$1:$C$428*1=$R$2*1)*($H$1:$H$428), ROW(A1))
검색명=INDEX( $F$1:$F$1000, 1/AGGREGATE(14, 6, ($C$1:$C$1000*1=$R$2*1)*($H$1:$H$1000=X4)/ROW($C$1:$C$1000), COUNTIF(X$3:X4, X4) ) )
조회수 =INDEX( $I$1:$I$1000, 1/AGGREGATE(14, 6, ($C$1:$C$1000*1=$R$2*1)*($H$1:$H$1000=X4)/ROW($C$1:$C$1000), COUNTIF(X$3:X4, X4) ) )
게시물 =INDEX( $M$1:$M$1000, 1/AGGREGATE(14, 6, ($C$1:$C$1000*1=$R$2*1)*($H$1:$H$1000=X4)/ROW($C$1:$C$1000), COUNTIF(X$3:X4, X4) ) )