엑셀 함수를 이용한 값,영역의 자동 복사 (자동화 완성본 파일 공유)
1. 엑셀 함수를 이용한 값,영역의 자동화란?
특정 시트에 Data가 매번 누적이 되서 갱신되거나, 삭제가 될때 다른 시트(관리용 시트)에 해당 Data가 자동으로 변환되게 하는 것을 매우 간단한 함수로 자동화가 가능합니다.
일반적으로 SUMIF함수를 통해 특정 조건하의 값들의 합계를 자동화하거나, IF함수를 통한 조건 지정, 조건에 따른 값의 자동화는 많이 알고계실텐데요, 이번 포스팅에서는 그런 함수가 아닌 특정 영역을 그대로 반영하는 동적 영역이라는 자동화를 알려드리겠습니다.
하기 이미지를 보시면, 좌측에 번호, 이름, 점수가 있습니다. 이를 원래 데이터라고 생각하고, 오른쪽을 함수로 자동화한 복사된 값이라고 생각합시다. 이때 오른쪽의 자동화 복사값은 원래 데이터가 자동으로 삭제되거나, 추가되면 값이 반영되도록 함수를 걸었습니다. 이 함수를 사용하면 다른 시트, 다른 파일의 원래 데이터가 변환되면 함수로 표현된 자동화 복사값에도 변환되는 것을 확인할 수 있습니다.
2. 엑셀 함수를 이용한 값, 영역 자동화 원리
일반적으로 함수로 특정 영역을 표현할 때는 하기와 같이 "좌측상단셀:우측하단셀"으로 표현할 수 있습니다.
그렇다면 함수를 써서 자동으로 원래 데이터가 변환되면 값이 변하게 하려면 우측하단셀을 자동으로 바뀌도록 설정해주면 됩니다. 여기서 좌측상단셀은 시작점, 즉 고정값이므로 바꿀 필요가 없기 때문에 우측하단셀을 원래 데이터값이 변하면 좌표가 자동으로 바뀌도록 하는 것으로 값,영역의 자동변환 함수가 완성됩니다.
방법은 간단합니다.
① 좌측상단셀:우측하단셀에서 "좌측상단셀:은 그대로 남겨둔다. 우측하단셀 부분을 선택하여 지운다.
② 우측하단셀 부분에 INDEX(우측하단셀의 모든 행영역, COUNTA(값이 모두 있는 특정 항목의 모든 행영역)을 지정합니다.
②-1* INDEX함수는 INDEX(목록, 행번호, 열번호)순으로 특정 목록으로 지정한 영역에서 지정한 행번호의 값, 열번호의 값을 반환하는데요. 여기서 중요한 것은 목록은 우측하단셀(즉 하기 예시에서는 점수의 모든 행)의 모든 영역이고, 행번호에는 COUNTA라는 함수를 사용하는데, COUNTA는 값이 있는 셀만 세려서 숫자를 반환하는 함수이므로 반드시 값이 있는 항목(하기 예시에서는 번호, 이름, 점수 모두 값이 있으므로 점수로 지정, 하지만 점수에 공백이 있을 경우는 다른 항목을 지정해도 무방함)을 선택합니다.
이렇게 함수를 만들어주면 자동으로 원데이터 값이 변환되면 함수로 표시된 영역도 변환되는 자동화가 완성됩니다.
3. 엑셀 함수를 이용한 값, 영역 자동화 활용(완성본 파일 공유)
이 자동화 함수를 다른 시트, 혹은 다른 파일에서 걸어주는 것으로 간단하게 원데이터가 변환되면 자동으로 함수로 표기된 영역이 변환되는 자동화가 이뤄집니다. 이를통해서 매번 데이터를 복사,붙여넣기하는 번거로움에서 해방될 수 있습니다.
아울러 이 함수를 잘 활용하면 자동으로 그래프를 표현하거나, 심지어 대쉬보드를 만드는 등의 활용방안이 있으니, 해당 내용은 다음 포스팅에서 공유하겠습니다. 감사합니다.