엑셀 조건별 세로 데이터 가로 변환 공식
지원 버전 자세히 보기
조건에 맞는 고유값을 세로 목록에서 가로로 펼치는 공식입니다.
인수 설명
012021 이후 FILTER 방식
=TRANSPOSE ( UNIQUE ( FILTER ( 출력범위, ( 참조범위=참조셀 ) * ( 출력범위<>"" ), "" ) ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02모든 버전 INDEX 배열 방식
{=IFERROR ( INDEX ( 출력범위, MATCH ( 0, COUNTIF ( 기준셀:이전셀, 출력범위 ) + IF ( 참조범위<>기준셀, 1, 0 ), 0 ) ), "" )}인수 설명 자세히 보기
동작 원리
012021 이후 FILTER 방식
FILTER 함수가 조건에 맞는 값을 고르고 UNIQUE 함수가 중복을 제거하면 TRANSPOSE 함수가 결과 배열을 가로로 전환합니다.
2021 이후 FILTER 방식의 예제 시트에서 E2:G2에 세 품목이 스필되는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
FILTER 함수가 조건에 맞는 품목을 고릅니다
A2:A8이 D2의 문구와 같고 B2:B8이 빈칸이 아닌 행만 남깁니다. 문구에 해당하는 품목은 볼펜, 노트, 파일, 볼펜입니다.
=FILTER ( $B$2:$B$8, ( $A$2:$A$8=D2 ) * ( $B$2:$B$8<>"" ), "" )
분류가 문구인 네 행의 품목을 반환합니다. = {"볼펜";"노트";"파일";"볼펜"}
원본 순서를 유지한 세로 배열입니다. UNIQUE 함수가 중복된 품목을 제거합니다
UNIQUE 함수는 두 번 나온 볼펜을 한 번만 남겨 세 개의 고유값을 반환합니다.
=UNIQUE ( {"볼펜";"노트";"파일";"볼펜"} )
중복된 볼펜을 한 번만 남깁니다. = {"볼펜";"노트";"파일"}
중복을 제거한 세로 배열입니다. TRANSPOSE 함수가 배열을 가로로 전환합니다
TRANSPOSE 함수는 세로 배열의 방향을 바꿔 E2부터 오른쪽으로 세 값을 스필합니다.
=TRANSPOSE ( {"볼펜";"노트";"파일"} )
세로 배열의 행과 열을 바꿉니다. = {"볼펜","노트","파일"}
E2:G2에 표시되는 가로 배열입니다. 02모든 버전 INDEX 배열 방식
COUNTIF 함수와 IF 함수가 이전 결과와 조건을 검사하고 MATCH 함수와 INDEX 함수가 다음 고유값을 차례대로 반환합니다.
모든 버전 INDEX 배열 방식의 예제 시트에서 G2 셀이 세 번째 결과인 파일을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
두 검사가 다음 후보만 0으로 남깁니다
G2를 계산할 때 COUNTIF 함수는 D2:F2에 이미 있는 문구, 볼펜, 노트를 B2:B8과 비교해 {1;0;1;0;0;0;1}을 반환합니다. IF 함수는 절대참조한 D2의 문구와 A2:A8을 비교해 분류가 다른 행을 1로 바꿔 {0;1;0;1;0;1;0}을 반환합니다. 두 배열을 더하면 아직 출력하지 않은 문구 품목인 다섯 번째 행만 0으로 남습니다.
=COUNTIF ( $D2:F2, $B$2:$B$8 ) + IF ( $A$2:$A$8<>$D$2, 1, 0 )
이전 결과 검사와 분류 조건 검사를 더합니다. = {1;1;1;1;0;1;1}
다섯 번째 위치의 파일만 다음 후보로 남습니다. MATCH 함수가 0의 위치를 찾습니다
MATCH 함수는 검사 배열에서 첫 번째 0을 정확히 검색합니다. 0은 다섯 번째 위치에 있으므로 5를 반환합니다.
=MATCH ( 0, {1;1;1;1;0;1;1}, 0 )
아직 출력하지 않은 첫 번째 일치값의 위치를 찾습니다. = 5
B2:B8에서 파일의 상대 위치입니다. INDEX 함수가 다음 고유값을 반환합니다
INDEX 함수는 B2:B8의 다섯 번째 값인 파일을 반환합니다. 더 이상 0이 없으면 MATCH 함수가 오류를 반환하고 IFERROR 함수가 그 오류를 빈칸으로 바꿉니다.
=IFERROR ( INDEX ( $B$2:$B$8, 5 ), "" )
다섯 번째 품목을 G2에 반환합니다. = "파일"
G2에 표시되는 세 번째 고유 품목입니다. 
엄청난 노력과 시간이 소요되는 공간을 잘 만들어 주어서 공부에 많은 도움이 되고 있습니다.
감사합니다.
제 PC에서 예제파일 다운후 확인시 셀값이 나타나지 않길래
f9키로 확인해보니 아래 수식이 오류값으로 반환되어 나타나는데요IF($B$8:$B$20<>INDIRECT("R"&ROW(E8)&"C"&COLUMN($E$8),0), 1, 0)
임의로 숫자배열을 입력해보니 값이 제대로 산출됩니다..
제가 보기에도 수식에는 오류가 없어보이는데요 왜 그런걸까요?
제 엑셀에 문제일까요?
수고 하세요..
INDIRECT("R"&ROW(E8)&"C"&COLUMN($E$8),0)부분을 삭제하고 참조셀을 직접 지정하되 열고정 형식($E8)으로 바꾸시면 해결됩니다.
수식을 보고 따라해도.. 예제파일을 돌려봐도 잘 안되네요.
예제파일에 함수셀에도 결과가 안나온 것으로 되어있는데, 한번 확인해 주실 수 있을까요?
혹시 이거 역으로 가로 데이타를 세로로 변환하는것도 가능할까요 ??
본 포스트에서 소개해드린 공식이 가로 -> 세로로 변환하는 공식입니다.
다시 한번 확인해보시겠어요?
세로 데이타를 가로로 변환이 가능한지 여쭤봤어야했는데요...
제목에는 가로데이터 세로변환 공식으로 되어 있는데 예제나 내용은 세로데이터 가로변환 인것 같아요
가로데이터 세로변환 공식을 알수 있을까요?
아래 공식을 한번 사용해보시겠어요?^^
=IFERROR(INDEX($출력범위,MATCH(0,COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW($참조셀)&"C"&COLUMN(참조셀),0),1,0),0)),"")
예를 들어 위의 인수 설명에 나온 예제에서 값 1칸에 가만 출력 되는 것이 아니라 가,나,다,라,마 이런씩으로 출력이 되게 할 수 있을까요?
네 TEXTJOIN 함수를 한번 사용해보세요.
https://www.oppadu.com/엑셀-textjoin-함수/
말씀하신 상황은 파워쿼리의 '피벗 해제' 기능을 사용하면 가능합니다.
아래 관련 강의를 한번 확인해보세요.
https://www.oppadu.com/%ec%97%91%ec%85%80-%eb%8d%b0%ec%9d%b4%ed%84%b0-%ea%b4%80%eb%a6%ac-%ea%b7%9c%ec%b9%99/
감사합니다.