엑셀 조건별 가로 데이터 세로 변환 공식 사용법 및 예제

엑셀에서 세로로 입력된 데이터에서 각 조건별 값을 가로방향으로 나열하는 공식의 사용법 및 동작원리를 살펴봅니다.

홈페이지 » 엑셀 조건별 가로 데이터 세로 변환 공식

엑셀 조건별 가로 데이터 세로 변환 공식

엑셀 조건별 가로 데이터 세로 변환 목차 바로가기
함수 공식
{ =IFERROR(INDEX($출력범위, MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0)), "") }
'본 공식은 배열수식이므로 365 이전버전 사용자는 Ctrl + Shift + Enter 로 입력합니다.
인수 설명
엑셀 조건별 세로 데이터 가로 변환 공식 설명
조건별 세로 데이터 가로 변환 공식에 사용된 인수
인수 설명
$출력범위 출력할 값이 입력된 세로 방향의 범위입니다. 절대참조로 입력합니다.
$참조범위 참조셀의 값을 참조할 범위입니다. 절대참조로 입력합니다.
$참조셀 참조셀입니다. 절대참조로 입력합니다.
참조셀 참조셀입니다. 상대참조로 입력합니다.

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀공식] 엑셀 조건별 세로 데이터 가로 변환 공식
    예제파일

호환성
운영체제 호환성
Windows 버전 모든 엑셀 버전에서 사용 가능합니다.
Mac 버전 모든 엑셀 버전에서 사용 가능합니다.
사용된 기초 함수
선택된 범위에서 하나의 조건을 만족하는 셀의 개수를 계산합니다.
선택한 범위에서 원하는 위치의 값을 반환합니다.
Loading...

엑셀 조건별 가로 데이터 세로 변환 공식 알아보기

공식 설명

이번 포스트의 공식을 사용하면 세로로 나열된 데이터에서 조건을 참고하여 각 조건을 만족하는 경우의 값을 가로방향으로 나열할 수 있습니다. 공식을 응용하면 여러 조건을 만족하는 경우에 값을 가로 방향으로도 나열할 수 있는데요. 두 조건을 병합한 별도의 열을 만든 뒤, 두 조건이 병합된 열과 조건을 참고하여 공식을 사용합니다.

본 공식은 VLOOKUP 여러개 값 불러오기 함수의 응용 버전입니다. 기존의 VLOOKUP 함수 공식은 고정된 셀을 참고하여 여러개 값을 불러오는 반면, 본 공식은 자동채우기가 되며 변하는 참조셀을 INDIRECT 함수로 참조하여 동적으로 여러개의 값을 반환합니다. VLOOKUP 여러개 값 불러오기 공식에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.

공식의 동작원리
  1. 참조셀을 기준으로 공식을 한칸씩 오른쪽, 아래로 자동채우기 하면서, 이전에 출력된 값 중에 출력범위의 값이 존재하는 지 확인합니다. 출력범위의 값이 이미 존재할 경우 1을 반환합니다.
    =COUNTIF($참조셀:참조셀,$출력범위)
  2. 현재 참조하는 셀과 참조범위의 값이 같을 경우 0, 그렇지 않으면 1을 반환합니다.
    =IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0)
  3. [1]과 [2]의 계산결과를 더합니다. 참조셀의 조건과 같으면서 기존에 값이 없을 경우에만 계산결과로 0이 반환됩니다.
    =COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0)
  4. MATCH 함수로 범위에서 첫번째로 0이 존재하는 순번을 찾습니다. (첫번째 0의 순번 = 조건이 같으면서 기존 값이 없는 경우)
    =MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0)
  5. MATCH 함수로 구한 순번의 값을 출력범위에서 반환합니다.
    =INDEX($출력범위, MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0))
  6. 오류가 출력될 경우, IFERROR 함수를 사용하여 오류 대신 빈칸을 반환합니다.
    =IFERROR(INDEX($출력범위, MATCH(0, COUNTIF($참조셀:참조셀,$출력범위)+IF($참조범위<>INDIRECT("R"&ROW(참조셀)&"C"&COLUMN($참조셀),0), 1, 0), 0)), "")
4.5 2 투표
게시글평점
guest
4 댓글
Inline Feedbacks
모든 댓글 보기
행복사랑
행복사랑
2021년 2월 14일 11:39 오후
게시글평점 :
     

유듀브를 시청하다 들려 돌아보고 방대한 자료에 놀랐습니다.
엄청난 노력과 시간이 소요되는 공간을 잘 만들어 주어서 공부에 많은 도움이 되고 있습니다.
감사합니다.

ywkoc
ywkoc
2021년 2월 17일 10:43 오후
게시글평점 :
     

안녕하세요.. 정말 수고 많으십니다. 제 PC에서 예제파일 다운후 확인시 셀값이 나타나지 않길래 f9키로 확인해보니 아래 수식이 오류값으로 반환되어 나타나는데요IF($B$8:$B$20<>INDIRECT("R"&ROW(E8)&"C"&COLUMN($E$8),0), 1, 0) 임의로 숫자배열을 입력해보니 값이 제대로 산출됩니다.. 제가 보기에도 수식에는… 더보기 »

늦게배운엑셀
늦게배운엑셀
2021년 6월 10일 5:16 오후
답글 남기기  ywkoc

INDIRECT 함수가 Value! 오류를 반환하네요. INDIRECT 함수에 의한 간접참조 대신 비교 셀을지정하는 직접 참조방식으로 해결됩니다.
INDIRECT("R"&ROW(E8)&"C"&COLUMN($E$8),0)부분을 삭제하고 참조셀을 직접 지정하되 열고정 형식($E8)으로 바꾸시면 해결됩니다.

게으른양
게으른양
2021년 3월 31일 11:25 오전
게시글평점 :
     

안녕하세요. 제가 지금 하려는 것과 완전 동일한 과제인데,
수식을 보고 따라해도.. 예제파일을 돌려봐도 잘 안되네요.
예제파일에 함수셀에도 결과가 안나온 것으로 되어있는데, 한번 확인해 주실 수 있을까요?

4
0
여러분의 생각을 댓글로 남겨주세요.x