엑셀 데이터 1차 정규화 공식 사용법
엑셀 데이터 정규화 목차 바로가기
함수 공식
=INDIRECT("R"&(ROW()+(데이터시작행번호-입력시작셀행번호)-(데이터행개수*ROUNDDOWN((ROW()-입력시작셀행번호)/데이터행개수,0)))&"C"&(ROUNDDOWN((ROW()-입력시작셀행번호)/데이터행개수,0)+데이터시작열번호),0)
인수 설명
| 인수 | 설명 |
| 데이터시작행번호 | 1차 정규화를 진행할 데이터의 좌측상단 시작셀 행 번호입니다. |
| 데이터시작열번호 | 1차 정규화를 진행할 데이터의 좌측상단 시작셀 열 번호입니다. |
| 데이터행개수 | 정규화를 진행할 데이터의 행 개수(높이)입니다. |
| 입력시작셀행번호 | 해당 공식이 입력되는 셀의 행 번호입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 엑셀 데이터 정규화 공식 :: 가로로 된 데이터 세로로 나열하기예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 데이터 정규화 공식 알아보기
공식 설명
본 공식은 가로로 길게 나열된 정규화되어 있지 않은 데이터를 1차 정규화하는 함수입니다. 데이터를 정규화하게 되면 엑셀의 다양한 함수나 기능을 보다 빠르고 편리하게 사용할 수 있으며 엑셀 업무처리 속도 향상에 큰 도움을 줍니다.
정규화는 총 6단계(크게는 5단계)로 나누어져 있으며, 일반적으로 3단계까지만 진행해도 대부분의 데이터를 체계적으로 정리할 수 있습니다. 그 중, 엑셀의 피벗테이블 기능을 사용하기 위해서는 데이터가 반드시 1차 정규화 되어있어야 하며, 그렇지 않을 경우 올바르지 않은 피벗테이블이 생성됩니다.
엑셀 정규화에 대한 보다 자세한 설명은 아래 기초 레벨업 정규화 관련 강의를 참고하세요.
해당 공식의 실전 사용법은 아래 '엑셀 데이터 정규화 공식 사용법'영상강의에서 단계별로 자세히 설명해 드렸습니다.
본 포스트에서는 매일 입고되는 재고를 가로방향으로 길게 나열하여 관리하는 재고입고대장 양식을 예제로 진행합니다. 재고입고대장 이외에 비슷한 형식으로 작성되는 매입매출관리대장, 재고입출고대장, 출퇴근관리대장 등 다양한 형태의 데이터를 정규화하는데 사용할 수 있습니다.
공식의 동작원리
- ROW 함수로 활성화 된 셀의 행 번호를 반환합니다.
=ROW(E18)
=18 '활성화된 셀의 행 번호를 반환합니다. 인수를 빈칸으로 입력하면 수식이 입력된 셀의 행 번호를 반환합니다. - INDIRECT 함수를 사용하여 R1C1으로 입력된 셀 주소를 참조합니다.
ROW 함수를 통해 매 행 번호가 증가하면서 참조되는 셀 주소가 변경됩니다.행 번호를 반환하는 부분
'예: ROW() = 23일 경우,
=(ROW()+(6-23)-(12*ROUNDDOWN((ROW()-23)/12,0)))
=(23-17-(12*ROUNDDOWN(23-23)/12,0)))
=(23-17-12*0)
=6열 번호를 반환하는 부분
'예: ROW() = 23일 경우,
=(ROUNDDOWN((ROW()-23)/12,0)+5)
=(ROUNDDOWN((23-23)/12,0)+5)
=0+5
=5INDIRECT 함수 결과값
=INDIRECT("R"&(ROW()+(6-23)-(12*ROUNDDOWN((ROW()-23)/12,0)))&"C"&(ROUNDDOWN((ROW()-23)/12,0)+5),0)
=INDIRECT("R"&6&"C"&5)
=INDIRECT("R6C5",0)
=0 'E6셀의 값을 반환합니다.
파워쿼리를 이용한 정규화방법도 조만간 준비해드리겠습니다.
좋은 의견 감사드립니다.
초보자도 버튼 한 번 눌러서 쉽게 정규화 할 수 있도록~~
좋은 자료 감사합니다.
그럴 경우 함수에 들어가는 셀 참조를 아래와 같이 INDIRECT 함수로 해결해보세요.
머리글이 여러줄인 경우, M365 에 새롭게 추가된 배열 수식을 사용하면 편리합니다.
이전 버전일 경우 함수만으로는 정규화 구현이 어려울 수 있습니다.
아래 영상을 참고해보세요.
https://www.oppadu.com/unpivot-%EC%99%84%EB%B2%BD-%EC%A0%95%EB%A6%AC/