엑셀 데이터 정리 끝판왕, TOCOL 함수 사용법 | 완벽 가이드

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행되는 라이브 강의 풀영상을 확인하실 수 있습니다.

엑셀 TOCOL 함수 기초 사용법

효율적인 데이터 관리는 모든 직장인에게 필수 역량으로 자리잡았습니다. 엑셀에서 비효율적인 피벗된 구조로 데이터를 관리하고 있다면, 오늘 소개해드리는 TOCOL 함수를 활용해 흩어진 데이터를 한 줄로 깔끔하게 정리할 수 있습니다.

1. TOCOL 함수 기초 예제 : TOCOL 함수는 지정한 범위의 데이터를 세로 방향의 단일 열로 변환하는 함수입니다. 예제파일을 실행한 후, [TOCOL 기초] 시트에서 결과를 출력할 시작셀인 F7셀을 선택합니다. 이어서 아래 수식을 입력하면 장보기 목록의 값이 세로 방향의 단일 열로 변환됩니다.
   =TOCOL(B7:D12)

   

2. 범위에서 비어 있는 셀을 결과에서 제외하려면, 두번째 인수인 '제외 옵션'을 변경합니다. 다음과 같이 제외 인수를 공백 무시(=1)로 입력하면, 빈 칸이 제외된 값만 출력됩니다.
   =TOCOL(B7:D12,1)

   

3. 읽기 방향도 변경할 수 있습니다. TOCOL 함수는 기본값으로 행(가로) 방향을 우선해 데이터를 읽으며, [읽기 방향] 인수를 TRUE로 지정하면 열(세로) 방향으로 데이터를 읽어옵니다.
   =TOCOL(B7:D12,1,TRUE)

   

4. 고유값 목록상자 만들기 : TOCOL 함수로 변환한 데이터에 UNIQUE 함수를 결합하면, 중복값을 제외한 동적 목록상자를 손쉽게 구성할 수 있습니다. F7셀의 수식을 다음과 같이 작성하면, 장보기 항목의 고유값 목록이 만들어집니다.
   =UNIQUE(TOCOL(B7:D12,1,TRUE))

   

5. 이어서 H7셀을 선택한 후, [데이터] 탭 - [데이터 유효성 검사]에서 제한 대상으로 '목록'을 선택합니다. 원본 범위로 다음과 같이 입력하면 F7셀에 작성한 고유 목록 범위가 동적으로 연결됩니다.
   =$F$7#

   

   오빠두Tip : 해시(#) 기호를 활용한 동적범위(분산범위) 활용법의 자세한 설명은 아래 기초 입문 강의를 참고하세요!😊
   
6. 이제 장보기 목록에 값을 추가하거나 삭제한 후, 적용한 목록 상자를 확인해보세요. 입력 데이터의 변경 사항이 실시간으로 반영되는 것을 확인할 수 있습니다.

   

IFNA 함수를 활용한 동적 데이터 취합 예제

TOCOL 함수와 IFNA 함수를 함께 사용하면, 실무에서 자주 마주치는 기초 데이터 가공 작업의 대부분을 간결한 수식으로 처리할 수 있습니다.

1. TOCOL + IFNA 활용 예제 : 예제파일의 [TOCOL 기초] 시트에서 O7셀을 선택한 후, 다음과 같이 TOCOL 함수를 작성해 점수 데이터를 세로 방향의 한 열로 변환합니다.
   =TOCOL(K7:M10)

   

2. 이어서 과목을 출력할 P7셀을 선택한 후, 다음과 같이 IFNA 함수를 작성합니다. IFNA 함수의 '오류가 아닐 경우 원래 값을 유지'하는 동작을 응용하면, 수식 범위의 데이터를 대체값 범위의 개수만큼 자연스럽게 확장할 수 있습니다.
   =IFNA(K6:M6,J7:J10)

   

3. 방금 작성한 수식을 TOCOL 함수로 한 번 더 묶어서 변환하면, 과목 범위 데이터가 세로 방향의 1열 데이터로 출력됩니다.
   =TOCOL(IFNA(K6:M6,J7:J10))

   

4. 이름을 출력할 Q7셀에도 다음과 같이 수식을 입력해 이름 범위를 세로 방향 1열 데이터로 변환합니다.
   =TOCOL(IFNA(J7:J10,K6:M6))

   

여러 범위를 한 번에 취합하는 방법

TOCOL 함수의 첫번째 인수인 Array(배열)에는 괄호 안에 여러 범위를 동시에 입력할 수 있습니다. 이는 M365 에 새롭게 추가된 일부 배열 인수 함수에서 지원되는 기능으로, 이를 활용하면 여러 범위에 흩어진 데이터를 하나의 결과로 손쉽게 취합할 수 있습니다.

1. 여러 범위 취합하기 : 예제파일에서 [TOCOL 응용] 시트로 이동한 후, 지출 내역을 취합할 시작셀인 B20셀을 선택하고 다음과 같이 TOCOL 함수를 작성합니다.
   =TOCOL((B8:B16,F8:F16,K8:K16),1)
   ' 괄호 안에 여러 범위를 쉼표로 구분하여 한 번에 입력할 수 있습니다.

   

2. 함수를 입력하면 각 지출 내역의 날짜 데이터가 한 번에 취합됩니다.

   

3. 이제 작성한 수식을 오른쪽으로 자동채우기하면 날짜·지출내역·금액 데이터의 취합이 모두 완료됩니다.
   
   오빠두Tip : 천 단위 구분기호가 포함된 숫자 서식은 단축키 Ctrl + Shift + 1 로 빠르게 적용할 수 있습니다.
4. TOCOL 데이터 취합 주의사항 : 단, TOCOL 함수의 두번째 인수인 '제외 옵션'으로 공백이나 오류를 무시하여 데이터를 취합할 때는 한 가지 주의가 필요합니다. 아래 그림과 같이, 빈 칸이 포함된 '비고' 범위를 TOCOL 함수로 취합하면 다른 필드와 행 위치가 어긋납니다. 이러한 경우에는 VSTACK 함수를 활용해 데이터를 취합해야 합니다.

   

   오빠두Tip : VSTACK 함수의 자세한 사용법은 아래 기초 강의에서 알기 쉽게 정리했습니다.😊
   

TOCOL + WRAPROWS 로 데이터 가공하기

TOCOL 함수로 범위를 세로 방향의 '단일 열'로 변환하는 작업은, 데이터를 정제하기 전 일종의 '기초 공사'에 해당합니다. 이렇게 정돈된 데이터는 WRAPROWS 함수로 다시 묶어 원하는 형태의 표로 재배열할 수 있습니다.

1. TOCOL + WRAPROWS 함수 예제 : 예제파일에서 [TOCOL 실전] 시트로 이동하면, A:B 범위에 네이버 증권에서 받아온 주식 데이터가 정리되어 있습니다. 이번에는 인터넷에서 복사한 비정형 데이터를 TOCOL + WRAPROWS 공식으로 빠르게 가공해보겠습니다.

   

2. G3셀을 선택한 후, 먼저 TOCOL 함수를 사용해 다음과 같이 주식 데이터를 세로 방향의 1열로 변환합니다.
   =TOCOL(A3:B26,1)

   

3. 세로로 변환한 데이터를 살펴보면, 각 종목별로 7행씩 묶여 있는 것을 확인할 수 있습니다. 이제 이 데이터를 WRAPROWS 함수로 7개 행마다 한 묶음씩 정렬해보겠습니다.
4. G3셀에 작성된 수식을 다음과 같이 WRAPROWS 함수로 한 번 더 감싸 작성하면, 네이버 증권 데이터의 변환이 완료됩니다.
   =WRAPROWS(TOCOL(A3:B26,1),7)

   

   오빠두Tip : 각 종목코드, 등락률, 시가총액에 적용된 셀 서식은 아래 셀 서식 기초 입문 강의에서 확인하세요!😊
   
5. 변환된 데이터 중, [등락]의 경우 같은 값이 두 번씩 반복되어 출력됩니다. 이 부분을 정리하는 과정은 위캔두 멤버십 라이브 전체 영상에서 확인해주세요!👇
   

3D 참조로 여러 시트 데이터 취합하기

TOCOL 함수에는 3D 참조로 범위를 입력할 수 있습니다. 이를 활용하면 여러 시트로 나뉘어 작성된 데이터를 한 번에 취합할 수 있습니다.

1. 예제파일에서 [취합] 시트로 이동한 후, A2셀에 TOCOL 함수를 입력합니다.

   

2. 이어서 첫번째 인수를 입력하기 위해 [1월] 시트를 선택한 후, Shift 키를 누른 상태로 [3월] 시트를 클릭하면, 다음과 같이 시트 범위가 자동으로 입력됩니다.

   

3. 이제 시트에서 취합할 데이터의 시작셀인 A2셀부터 A100 (또는 A1048576 까지도 가능) 까지 범위를 넉넉하게 선택해 입력하고, 제외 옵션으로 공백 무시(=1)를 적용한 후 함수를 마무리합니다.
   =TOCOL('1월:3월'!A2:A100,1)
   

   

   오빠두Tip : 날짜 서식은 단축키 Ctrl + Shift + 3 으로 빠르게 적용할 수 있습니다.
4. 이제 작성한 수식을 오른쪽으로 자동채우기하면, 모든 시트의 데이터를 한 번에 취합할 수 있습니다.

   

5. 자동채우기를 마친 데이터에서 5번째 필드를 삭제한 후, 항목이 두 번씩 반복되는 6번째 필드(F열) 데이터를 WRAPROWS 함수로 묶어 정리합니다. 다음과 같이 F열에 적용한 TOCOL 함수를 WRAPROWS 함수로 한 번 더 감싸 작성하면 값과 성장률이 올바르게 구분된 데이터가 완성됩니다.
   =WRAPROWS(TOCOL('1월:3월'!F2:F100,1),2)