엑셀 데이터 정리 끝판왕, TOCOL 함수 완벽 가이드
엑셀 TOCOL 함수 사용법 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 81강 엑셀 데이터 관리 끝판왕, TOCOL 함수 완벽 정리 - 예제파일예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
엑셀 TOCOL 함수 기초 사용법
효율적인 데이터 관리는 이제 모든 직장인의 필수 역량입니다. 특히, 엑셀에서 비효율적인 피벗된 구조로 데이터를 관리하고 있다면, 오늘 소개해드리는 TOCOL 함수를 사용해서 편리하게 데이터를 정리할 수 있습니다.
= TOCOL ( 범위, [제외옵션], [읽기방향] )
' 범위의 데이터를 세로 방향의 단일 열로 변환합니다.
· 범위 : 변환하려는 데이터 범위를 지정합니다.
· 제외옵션 : [선택인수] 제외할 값을 지정합니다.
· 읽기방향 : [선택인수] 데이터를 읽는 방향을 설정합니다.- TOCOL 함수 기초 예제 : TOCOL 함수는 범위의 데이터를 세로 방향의 단일 열로 변환하는 함수입니다. 예제파일을 실행한 후, [TOCOL 기초] 시트에서 목록의 시작셀인 F7셀을 선택합니다. 이후 아래 수식을 입력하면 장보기 목록의 값이 세로 방향의 단일 열로 변환됩니다.
=TOCOL(B7:D12)
장보기 목록을 TOCOL 함수로 변환합니다. - 만약 범위에서 비어있는 셀을 제외하려면, 함수의 두번째 인수인 '제외 옵션' 인수를 변경합니다. 다음과 같이 제외 인수를 공백 무시(=1)로 입력하면, 빈 칸을 제외한 값이 출력됩니다.
=TOCOL(B7:D12,1)
공백을 무시해서 변환할 수 있습니다. - 읽기 방향도 변경할 수 있습니다. TOCOL 함수는 기본값으로 행 방향(가로 방향)을 우선으로 데이터를 읽으며, [읽기 방향] 인수를 TRUE로 하면 열 방향(세로 방향)으로 데이터를 읽습니다.
=TOCOL(B7:D12,1,TRUE)
읽기 방향을 행방향(가로) 에서 열방향(세로)로 변경합니다. - 고유값 목록상자 만들기 : TOCOL 함수로 변환한 데이터에 UNIQUE 함수를 활용하면, 중복값을 제외한 목록상자를 간편하게 만들 수 있습니다. F7셀의 수식을 다음과 같이 작성하면, 장보기 항목의 고유값 목록이 만들어집니다.
=UNIQUE(TOCOL(B7:D12,1,TRUE))
UNIQUE 함수를 활용해 고유 목록으로 변환합니다. - 이제 H7셀을 선택한 후, [데이터] 탭 - [데이터 유효성 검사] 에서 제한 대상으로 '목록'을 선택한 후, 원본 범위로 다음과 같이 입력하면 F7셀에 작성한 고유 목록 범위가 동적으로 선택됩니다.
=$F$7#
해시(#) 기호로 분산 범위를 참조합니다 오빠두Tip : 해시(#) 기호를 활용한 동적범위(분산범위) 활용법의 자세한 설명은 아래 기초 입문 강의를 참고하세요!😊
- 이제 장보기 목록에 값을 추가하거나 삭제한 후, 적용한 목록 상자를 확인해보세요. 실시간으로 업데이트 되는 것을 확인할 수 있습니다.
고유 목록의 목록상자가 완성되었습니다.
IFNA 함수를 활용한 동적 데이터 취합 예제
TOCOL 함수와 IFNA 함수를 함께 사용하면, 실무에서 필요한 대부분의 기초 데이터 가공을 정말 편리하게 할 수 있습니다.
= IFNA ( 수식, NA대체값 )
' 수식으로 #N/A오류 발생 시, 오류 대신 대체값을 출력합니다.
' 수식이 오류가 아니라면, 값을 그대로 유지합니다.- TOCOL + IFNA 활용 예제 : 예제파일의 [TOCOL 기초] 시트에서 O7셀을 선택한 후, 다음과 같이 TOCOL 함수를 작성해 점수 데이터를 세로 방향의 1열 데이터로 변환합니다.
=TOCOL(K7:M10)
TOCOL 함수로 점수 범위를 세로 배열로 변환합니다. - 이후 과목을 출력할 P7셀을 선택한 후, 다음과 같이 IFNA 함수를 작성합니다. IFNA 함수의 '오류가 아닐 경우 기존 값을 유지'하는 동작을 응용하면, 수식 범위의 데이터를 대체값으로 지정한 범위의 개수만큼 확장할 수 있습니다.
=IFNA(K6:M6,J7:J10)
IFNA 함수를 활용해 머리글 범위를 개수만큼 반복 출력합니다. - 방금 작성한 수식을 TOCOL 함수로 묶어서 변환하면, 과목 범위 데이터가 세로 방향의 1열 데이터로 출력됩니다.
=TOCOL(IFNA(K6:M6,J7:J10))
TOCOL 함수로 머리글 범위를 세로 배열로 변환합니다. - 이름을 출력할 Q7셀에도 다음과 같이 수식을 입력해서 이름 범위를 세로 방향 1열 데이터로 변환합니다.
=TOCOL(IFNA(J7:J10,K6:M6))
동일한 과정으로 레이블 범위를 세로 배열로 변환합니다.
여러 범위를 한 번에 취합하는 방법
TOCOL 함수의 첫번째 인수인 Array(배열)는 괄호 안에 여러 범위를 선택하여 한 번에 입력할 수 있습니다. 이는 M365에 새롭게 추가된 일부 Array를 인수로 받아오는 함수에 새롭게 추가된 기능인데요. 이 기능을 활용하면 여러 범위의 데이터를 편리하게 취합할 수 있습니다.
- 여러 범위 취합하기 : 예제파일에서 [TOCOL 응용] 시트로 이동한 후, 지출 내역을 취합할 시작셀인 B20셀을 선택하고 다음과 같이 TOCOL 함수를 작성합니다.
=TOCOL((B8:B16,F8:F16,K8:K16),1)
' 괄호 안에 여러 범위를 쉼표로 구분하여 한 번에 입력할 수 있습니다.
TOCOL 함수의 첫번째 인수로 괄호안에 여러 범위를 입력할 수 있습니다. - 함수를 입력하면 각 지출 내역의 날짜 데이터가 한 번에 취합됩니다.
여러 범위 데이터를 하나로 합칩니다. - 이제 작성한 수식을 오른쪽으로 자동채우기하면 각 날짜, 지출내역, 금액 데이터 취합이 완료됩니다.
오빠두Tip : 천단위 구분기호가 들어간 숫자 서식은 단축키 Ctrl + Shift + 1 로 편리하게 적용할 수 있습니다. - TOCOL 데이터 취합 주의사항 : 단, TOCOL 함수의 두번째 인수인 '제외 옵션'으로 공백이나 오류를 무시하여 데이터를 취합할 경우에는 한가지 주의사항이 있습니다. 아래 그림과 같이, 빈 칸이 포함된 '비고' 범위를 TOCOL 함수로 취합하면, 다른 필드와 어긋납니다. 이러한 경우에는 VSTACK 함수를 활용하여 데이터를 취합해야 합니다.
중간에 공백이 있는 데이터는 취합 시 주의합니다. 오빠두Tip : VSTACK 함수의 자세한 사용법은 아래 기초 강의에서 알기 쉽게 정리했습니다.😊
TOCOL + WRAPROWS 로 데이터 가공하기
TOCOL 함수로 범위를 세로 방향의 '단일 열'로 변환하는 것은, 데이터를 정제하기 전 '기초 공사'를 하는 것이라고 볼 수 있습니다. 이렇게 기초 공사를 마친 데이터는, WRAPROWS 함수로 묶어 다시 올바른 데이터로 변환할 수 있습니다.
= WRAPROWS ( 범위, 나눌개수,[채울값] )- TOCOL + WRAPROWS 함수 예제 : 예제파일에서 TOCOL 실전 시트로 이동하면, A:B 범위에 네이버 증권에서 받아온 주식 데이터가 정리되어 있습니다. 이번에는 인터넷에서 복사한 잘못된 구조의 데이터를 TOCOL + WRAPROWS 공식으로 빠르게 가공해보겠습니다.
네이버 증권에서 받아온 주식 데이터를 올바른 데이터로 가공합니다. - G3셀을 선택한 후, 먼저 TOCOL 함수를 사용해 다음과 주식 데이터를 세로 방향의 1열로 변환합니다.
=TOCOL(A3:B26,1)
먼저 TOCOL 함수를 사용해 세로 배열로 변환합니다. - 세로로 변환한 데이터는, 각 종목별로 7행씩 나뉘어진 것을 확인할 수 있습니다. 이제 이 데이터를 WRAPROWS 함수를 사용해, 각 7개 행마다 묶인 범위로 변환합니다.
- G3셀에 작성된 수식을 다음과 같이 WRAPROWS 함수로 묶어서 작성하면, 네이버 증권 데이터 변환이 완료됩니다.
=WRAPROWS(TOCOL(A3:B26,1),7)
WRAPROWS 함수로 7행마다 묶인 범위로 변환합니다. 오빠두Tip : 각 종목코드, 등락률, 시가총액에 적용된 셀 서식은 아래 셀 서식 기초 입문 강의에서 확인하세요!😊
- 변환된 데이터 중, [등락]의 경우 같은 값이 두번씩 반복되어 출력됩니다. 이 데이터를 변환하는 과정은 위캔두 멤버십 라이브 전체 영상을 확인해주세요!👇
3D 참조로 여러 시트 데이터 취합하기
TOCOL 함수에는 3D 참조로 범위를 입력할 수 있습니다. 이를 활용하면 여러 시트로 나뉘어서 작성된 데이터를 편리하게 취합할 수 있습니다.
= TOCOL ('시작시트:마지막시트'!범위)- 예제파일에서 [취합] 시트로 이동한 후, A2셀에 TOCOL 함수를 입력합니다.
취합을 시작할 시작셀에 TOCOL 함수를 입력합니다. - 이후 첫번째 인수를 입력하기 위해 [1월] 시트를 선택한 후, Shift 키를 누른 상태로 [3월] 시트를 선택하면 다음과 같이 시트가 범위 형태로 입력됩니다.
1월부터 3월시트를 3D 참조로 동시에 선택합니다. - 이제 시트에서 취합할 데이터의 시작셀인 A2셀부터 A100 (또는 A1048576 까지도 가능) 까지 범위를 넉넉하게 선택해서 입력하고, 제외 옵션으로 공백무시(=1)를 선택 후 함수를 입력합니다.
=TOCOL('1월:3월'!A2:A100,1)
여러 시트에서 취합할 범위를 넉넉히 선택합니다. 오빠두Tip : 날짜 서식은 단축키 Ctrl + Shift + 3 으로 빠르게 적용할 수 있습니다. - 이제 작성한 수식을 오른쪽으로 자동채우기해서, 모든 시트의 데이터를 한 번에 취합할 수 있습니다.
작성한 수식을 자동채우기하면 여러 시트 데이터 취합이 완료됩니다. - 자동채우기 한 데이터 중, 5번째 필드는 지운 후, 항목이 두번씩 반복되는 6번째 필드 데이터(F열)를 WAPROWS 함수로 묶어서 정리합니다. 다음과 같이 F열에 적용한 TOCOL 함수를 WRAPROWS 함수로 묶어서 작성하면 값과 성장률이 올바르게 구분된 데이터가 완성됩니다.
=WRAPROWS(TOCOL('1월:3월'!F2:F100,1),2)
여러번 반복되는 데이터는 WRAPROWS 함수로 묶어서 데이터 취합을 완료합니다.
- TOCOL 함수 기초 예제 : TOCOL 함수는 범위의 데이터를 세로 방향의 단일 열로 변환하는 함수입니다. 예제파일을 실행한 후, [TOCOL 기초] 시트에서 목록의 시작셀인 F7셀을 선택합니다. 이후 아래 수식을 입력하면 장보기 목록의 값이 세로 방향의 단일 열로 변환됩니다.
