엑셀 조건별 세로 데이터 가로 변환 공식
엑셀 조건별 세로 데이터 가로 변환 목차 바로가기
함수 공식
{ =IFERROR(INDEX($출력범위, MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0)), "") }
'본 공식은 배열수식이므로 365 이전버전 사용자는 Ctrl + Shift + Enter 로 입력합니다.
'본 공식은 배열수식이므로 365 이전버전 사용자는 Ctrl + Shift + Enter 로 입력합니다.
인수 설명
| 인수 | 설명 |
| $출력범위 | 출력할 값이 입력된 세로 방향의 범위입니다. 절대참조로 입력합니다. |
| $참조범위 | 참조셀의 값을 참조할 범위입니다. 절대참조로 입력합니다. |
| $참조셀 | 참조셀입니다. 절대참조로 입력합니다. |
| 참조셀 | 참조셀입니다. 상대참조로 입력합니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 엑셀 조건별 세로 데이터 가로 변환 공식예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 조건별 세로 데이터 가로 변환 공식 알아보기
공식 설명
이번 포스트의 공식을 사용하면 세로로 나열된 데이터에서 조건을 참고하여 각 조건을 만족하는 경우의 값을 가로방향으로 나열할 수 있습니다. 공식을 응용하면 여러 조건을 만족하는 경우에 값을 가로 방향으로도 나열할 수 있는데요. 두 조건을 병합한 별도의 열을 만든 뒤, 두 조건이 병합된 열과 조건을 참고하여 공식을 사용합니다.
본 공식은 VLOOKUP 여러개 값 불러오기 함수의 응용 버전입니다. 기존의 VLOOKUP 함수 공식은 고정된 셀을 참고하여 여러개 값을 불러오는 반면, 본 공식은 자동채우기가 되며 변하는 참조셀을 INDIRECT 함수로 참조하여 동적으로 여러개의 값을 반환합니다. VLOOKUP 여러개 값 불러오기 공식에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.
공식의 동작원리
- 참조셀을 기준으로 공식을 한칸씩 오른쪽, 아래로 자동채우기 하면서, 이전에 출력된 값 중에 출력범위의 값이 존재하는 지 확인합니다. 출력범위의 값이 이미 존재할 경우 1을 반환합니다.
=COUNTIF($참조셀:참조셀,$출력범위)
- 현재 참조하는 셀과 참조범위의 값이 같을 경우 0, 그렇지 않으면 1을 반환합니다.
=IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0)
- [1]과 [2]의 계산결과를 더합니다. 참조셀의 조건과 같으면서 기존에 값이 없을 경우에만 계산결과로 0이 반환됩니다.
=COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0)
- MATCH 함수로 범위에서 첫번째로 0이 존재하는 순번을 찾습니다. (첫번째 0의 순번 = 조건이 같으면서 기존 값이 없는 경우)
=MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0)
- MATCH 함수로 구한 순번의 값을 출력범위에서 반환합니다.
=INDEX($출력범위, MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0))
- 오류가 출력될 경우, IFERROR 함수를 사용하여 오류 대신 빈칸을 반환합니다.
=IFERROR(INDEX($출력범위, MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0)), "")
- 참조셀을 기준으로 공식을 한칸씩 오른쪽, 아래로 자동채우기 하면서, 이전에 출력된 값 중에 출력범위의 값이 존재하는 지 확인합니다. 출력범위의 값이 이미 존재할 경우 1을 반환합니다.
엄청난 노력과 시간이 소요되는 공간을 잘 만들어 주어서 공부에 많은 도움이 되고 있습니다.
감사합니다.
제 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/
감사합니다.