엑셀을 사용한 대용량 데이터 관리: 이름 범위 활용 | 진짜쓰는 실무엑셀

빠른 핵심 요약

✅ 이름 범위 등록하기: [수식] > [선택 영역에서 만들기] 클릭

✅ 목록 상자 만드는 방법: [데이터] > [데이터 유효성 검사] > [목록] > 범위 선택

✅ 겹치는 셀 반환 방법: 수식/이름범위 띄어쓰기 수식/이름범위

✅ [수식] > [이름 관리자] 클릭 > 사용한 '이름범위' 삭제 가능

---

엑셀 빅데이터 분석 & 관리 문제

빅데이터를 다룰 때 엑셀은 데이터 범위 선택이나 함수 작성이 번거로울 수 있습니다. 특히 대량의 데이터를 다룰 때는 더욱 그렇습니다. 데이터 범위 선택, 드래그하는데 오래 걸리며, 엑셀 함수를 작성하는데 어려울 수 있습니다.

이를 해결하기 위해 엑셀의 이름 범위 기능을 활용할 수 있습니다. 이름을 사용하여 데이터를 쉽게 선택하고 함수에서 해당 이름을 활용하여 데이터를 관리하고 빅테이터 분석을 할 수 있습니다.

 

엑셀에서 한 번에 이름 범위 등록하기

데이터 관리를 위해 엑셀 [이름범위]를 한 번에 등록하는 방법을 알아보겠습니다.

•  첫행 1개와 왼쪽 열 1개로 이루어진 표 = 머릿글이 한 줄로 정리된 표에서 엑셀 이름범위를 등록이 가능합니다. 즉, 첫 행에는 하나의 항목이, 왼쪽 열에는 하나의 항목이 있으면 됩니다. 
• 데이터의 크기와 관계 없이 이 작업은 항상 가능합니다. (머릿글의 위치에 관계없이 행과 열로 한 줄씩 정리되어 있다면 모두 엑셀의 이름범위를 사용할 수 있습니다.)

엑셀 [이름범위] 등록 단계

1. 머리글을 포함하여 데이터 범위를 선택합니다.
2. [수식] 탭을 클릭한 후 [선택 영역에서 만들기]를 선택합니다.
3. 왼쪽 열과 첫 번째 행을 체크합니다.
4. 이제 데이터 분석을 위한 준비 완료합니다.

* 머리글이 다른 쪽에 있을 경우 다른 옵션을 선택해 동일한 방법으로 [이름 범위]를 만들 수 있습니다.

머리글을 포함하여 범위 선택 > 수식 > 선택 영역에서 만들기 클릭 > 왼쪽열, 첫번째 행 체크

 

엑셀의 이름 범위 기능을 활용한 빅데이터 분석

일본 매출 합계 구하기

=sum(일본)

엑셀의 이름 범위 기능을 사용하여 빅 데이터를 분석하는 방법은 편리하고 효율적입니다. "일본"이라는 이름 범위를 정의하고, 이를 활용하여 매출 합계를 구할 수 있습니다.

1. 이름 범위 정의: 데이터가 포함된 범위를 위에서 설명한 것처럼 이름범위를 지정하면 해당 범위에 이름이 자동으로 부여됩니다.
2. 합계 구하기: 그런 다음, 합계를 구하고자 하는 셀에 "=SUM(일본)"과 같은 수식을 입력합니다. 이렇게 하면 해당 범위에 있는 값들의 합계가 계산됩니다.

이제 "일본"이라는 이름 범위를 통해 합계를 구할 수 있습니다. 미국, 캐나다, 중국, 영국 등도 같은 방법으로 합계를 구할 수 있습니다. 

 

➕SUM함수: 수학 함수

설명: 선택된 인수, 셀, 범위의 합계를 계산

구문: =SUM(범위1, [범위2], …)

셀에 =sum(일본) 입력

 

클로저 평균 매출 구하기

=AVERAGE(클로저)

클로저 평균 매출을 구하는 방법은 간단합니다. 먼저, "클로저"라는 이름 범위를 정의해야 합니다. 이 이름 범위는 클로저 지역의 매출이 기록된 데이터 범위를 가리킵니다.

1. 이름 범위 정의: 데이터 범위를 선택하고 표에 이름범위를 지정하면 해당 범위에 "클로저"라는 이름이 부여됩니다.
2. 평균 구하기: 평균을 구하고자 하는 셀에 "=AVERAGE(클로저)"와 같은 수식을 입력합니다. 이렇게 하면 클로저 지역의 매출 데이터의 평균이 계산됩니다.

이름 범위를 활용하여 수식이나 함수없이 평균 매출을 쉽게 구할 수 있습니다.

 

➕AVERAGE 함수: 통계함수

설명: 선택한 범위의 산술평균을 계산

구문: =AVERAGE(범위1, [범위2], …)

=AVERAGE(클로저)입력

 

VLOOKUP 함수 없이 '이름범위'로 데이터 구하기 

 

이름범위로 미국 내 카트라이더 매출 구하기

수식: =카트라이더(띄어쓰기)미국

1. 빈셀 클릭합니다.
2. 카트라이더쓰고 띄어쓰기하고 미국 입력합니다.
3. 두 범위를 띄어쓰기하면 겹치는 셀을 반환됩니다. (스샷 참고)

엑셀은 두 범위를 띄어쓰기하면 겹치는 셀을 반환됨

 

 

 

나라별, 게임별 매출을 구하기 전 목록 상자 만드는 방법

먼저, 국가명과 게임명을 선택할 수 있는 목록 상자를 만들어야 합니다.

 

엑셀 목록상자 만들기

[데이터] > [데이터 유효성 검사] > [목록] > 국가/게임 이름 범위 선택> 목록 상자 만들어짐

1. 엑셀에서 [데이터] 탭을 클릭합니다.
2. [데이터 도구] 섹션에서 [데이터 유효성 검사]를 선택합니다.
3. [목록]을 선택한 후, 국가 입력 범위와 게임 이름 범위를 각각 선택하여 목록 상자를 만듭니다.

[데이터] > [데이터 유효성 검사] > [목록] > 국가 입력 범위 선택> 목록 상자 만들어짐

[데이터] > [데이터 유효성 검사] > [목록] > 게임 이름 범위 선택>목록 상자 만들어짐

 

국가명, 게임명, 매출 구하기

매출을 구하기 위해선 국가와 게임명을 목록에서 선택했을 때 겹치는 셀이 매출 칸에 반환되도록 하면 됩니다.

​

1 국가명, 게임명을 선택합니다.

2 매출 셀 선택합니다.

3 수식 입력합니다.: =INDIRECT(국가가 입력된 셀 주소) 띄어쓰기 INDIRECT(게임명이 입력된 셀 주소)

😎예제파일 완성 수식: INDIRECT(m8) INDIRECT(m9)

위의 수식은 서로 겹치는 범위를 반환하도록 합니다.

4 엔터키를 입력합니다.

 

이 방법을 사용하면 엑셀에서 국가명과 게임명에 따라 매출을 쉽게 확인할 수 있습니다. 목록 상자를 활용하면 실시간으로 데이터가 변경되어 편리하게 분석할 수 있고, 복잡한 함수 없이 간단한 분석 보고서를 만들 수 있습니다. 

 

➕INDIRECT 함수: 참조 함수

설명: 텍스트로 입력된 주소로 셀 또는 범위를 참조

구문: =INDIRECT(참조범위, [참조방식])

1 국가명, 게임명을 선택 2 매출 셀 선택 3 =INDIRECT(국가가 입력된 셀 주소) 띄어쓰기 INDIRECT(게임명이 입력된 셀 주소) 입력 완성 수식: INDIRECT(m8) INDIRECT(m9)

목록에서 선택해서 실시간 매출 구하기: INDIRECT(m8) INDIRECT(m9)

 

 

엑셀 '이름 관리자'를 사용한 이름 범위 관리 방법

엑셀 이름 관리자 열기

[수식] > [이름 관리자] 클릭: [수식] 탭을 클릭합니다 > [이름 관리자]를 클릭합니다.

• 머리글을 참조하여 만든 이름값을 확인할 수 있습니다.
• 필요에 따라 삭제 버튼을 통해 이름값을 삭제할 수 있습니다.

'이름 관리자'에서 '이름범위' 삭제 방법

• 연속된 항목 선택: Shift 키를 누른 상태에서 클릭하여 연속된 항목을 선택합니다.
• 모두 선택 후 삭제: 모든 항목을 선택한 후 '이름 범위'를 삭제합니다.

'이름 정의' 사용 시 주의사항

• 숫자로 시작 불가: 이름은 숫자로 시작할 수 없습니다.
• 띄어쓰기 포함 불가: 이름에 띄어쓰기를 포함할 수 없습니다.
• 언더바(_) 사용: 숫자나 띄어쓰기를 포함하려면 언더바(_)를 사용합니다.
• 셀 주소로 정의 불가: 이름을 셀 주소로 정의할 수 없습니다.
• 대소문자 구분 없음: 이름 정의 시 대소문자를 구분하지 않습니다.

[수식] > [이름 관리자] 클릭 > 머리글을 참조해서 만든 이름값 확인 가능 > 삭제 버튼을 통해서 이름 값 삭제도 가능

 

 

 

 

 

공부 영상 제목 👇

엑셀 데이터 업무가 정말 편해지는, "이름 범위" 실전 사용법! | 정말 편리합니다 | 진짜 쓰는 실무엑셀 2-3-3