엑셀/엑셀기초_원데이챌린지

[챕터2]엑셀에서 '데이터 관리'가 중요한 이유!(1)

Code.Jaram 2025. 6. 19. 17:42
오빠두 엑셀
https://www.oppadu.com/challenges/excel-basic-1day/

 

 

[1교시] 파일 공유와 자료 관리가 쉬워지는 엑셀 활용법

1. 목록상자

1-1. 문자 제한

제품명에 정해진 라면 종류만 기입하게 하려면 목록상자를 활용하면 된다.

데이터를 입력할 범위를 드래그한 후 데이터 탭 > 데이터 유효성 검사를 클릭하고

제품명이 정리된 범위를 선택하여 목록으로 설정하면 정해진 제품명만 선택하게 할 수 있다.

 

[TIP]

목록상자 데이터 선택 단축키 : ALT+ ↓

※ ALT+ ↓: 필터에서도 사용가능. 일반 셀에서도 위의 데이터를 자동으로 인식해 데이터를 선택할 수 있음

 

 

1-2. 숫자 제한

수량을 쓸 때 숫자만 입력하게 하려면 마찬가지로 데이터 유효성 검사에서

제한대상을 '정수'로 선택한 후 최소값과 최대값을 설정하면 된다.

 

 

 

1-3. 오류 메시지 설정

추가로 오류메시지를 설정하지 않으면 설정한 값 외에 다른 값을 입력했을 때 아래와 같은 기본메시지가 출력되는데,

파일을 공유한 사람에게 자세한 설명을 하고 싶다면 오류메시지를 설정하면 된다.

 

 

이렇게 오류 메시지를 설정하면 잘못 입력한 사람이 어떻게 데이터를 입력해야 하는지 이해할 수 있다.

 

 

 

 

 

1-4. 동적으로 확장하는 목록상자

 

목록상자를 설정한 후 참조할 범위에 데이터를 추가하면 목록상자에 자동으로 반영되지 않는다.

절대참조방식으로 범위를 선택했기 때문에 그 아래 셀이 포함되어 있지 않기 때문이다.

 

 

이런 점을 해결하기 위해서는 '표' 기능을 활용하면 좋다.

원가표 범위를 선택한 후 Ctrl+T를 눌러 표를 설정하면 데이터를 추가했을 때 표의 범위가 동적으로 확장된다.

 

 

 

표 이름은 표에 커서를 두어 테이블 디자인 탭을 활성화시켜 '표 이름'에서 설정해주면 된다.

 

표 이름을 만들면 범위를 선택할 때 셀 주소를 입력하거나 범위를 드래그하는 게 아니라

구조적 참조방식으로 '=원가표[제품명]'과 같이 간단하게 범위를 선택할 수 있다.

 

 

이를 응용하면 목록상자에서 원가표의 데이터가 추가되었을 때 새로운 데이터도 선택할 수 있게 된다.

동일하게 데이터 유효성 검사를 클릭하고 원본 범위를 아래와 같이 INDIRECT 함수를 사용해서 설정하면

참조한 표에서 새로운 데이터가 추가되었을 때 목록상자에 자동으로 반영할 수 있다.

INDIRECT("표이름[필드명]")

 

 

 

2. VLOOKUP 함수

 

[VLOOKUP 함수 제한사항]

  • 찾을 값이 범위의 맨 왼쪽 열에 있어야 함 예)이름을 기준으로 검색하면 범위의 맨 왼쪽에 이름 열이 있어야 함
  • 일치하는 값이 여러 개 있으면 맨 위에 있는 값만 검색가능 예)박동주 학생이 2개 있으면 맨 처음 박동주 학생의 값만 불러옴

 

그런데 만약 2가지 조건을 만족하는 값을 불러오고 싶으면 어떻게 해야할까?

 

VLOOKUP함수는 조건에 만족하는 첫번째 값만 불러오므로 이럴 때는 고유값을 만들어주면 된다.

 

아래와 같이 이름과 시험 구분에 일치하는 평균을 구하려고 한다면

원본데이터에 이름과 구분을 묶어서 고유값을 만들어주고 점수조회표에도 고유값을 만들어준다.

 

 

 

이때, 찾을 값은 범위의 맨 왼쪽에 있어야 하므로 고유값 열을 Shift+드래그(방사형 십자가 모양)로 이름 열 왼쪽으로 옮겨준다.

(셀의 왼쪽이나 오른쪽 끝에 커서를 두면 이동포인터  활성화됨)                                                                

 

 

그리고 고유값을 기준으로 평균을 구해주면 된다.

=VLOOKUP(O5,C5:H22,6,0)

 

 

 

3. SUMIF 함수

대분류별 금액을 구하려고 할 때는 SUMIF 함수를 사용할 수 있다.

먼저 원본데이터에서 대분류 값을 복사한 뒤 중복항목 제거로 집계표 대분류를 작성한다.(최신버전에서는 UNIQUE함수 활용)

 

그리고 SUMIF(원본 대분류 범위, 집계표 대분류, 원본 금액 범위)를 입력하면 된다.

SUMIF(조건범위, 조건, 집계범위)

 

 

마지막으로 미니맵을 활용하여 원본데이터 금액합산과 집계표 금액 합산이 일치하는지 검산한다.

 

참고로, 같은 표이지만 아래와 같이 대분류를 셀병합했을 경우 SUMIF 함수를 사용했을 때 

금액이 바르게 계산되지 않을 수 있다.

 

 

왜냐하면 첫번째 셀에만 대분류 값이 입력되고 그 아래 셀들은 비어있기 때문이다.

따라서 셀병합된 표는 셀병합을 해제한 후 값을 다 채워줘야 한다.

 

셀 병합된 표를 일반 표로 변경할 때는 아래와 같이 진행한다.

1. 셀 병합 해제
2. 범위 전체 선택 후 Ctrl+G 입력-옵션 클릭-빈 셀 선택
3. 이름상자에서 활성화된 셀 확인(L5)
4. 활성화된 셀에서 윗 셀 불러오기(=L4)
5. Ctrl+Enter

 

빈 셀에 값을 일괄적으로 입력하기 위해 이동옵션으로 빈셀을 찾아준 후 

활성화된 셀에 윗 셀을 불러오는 수식을 입력한다.

 

빈 셀의 첫번째 셀은 문자가 입력된 윗 셀을 불러오고 그 다음 빈 셀은 값을 불러왔던 윗 셀을 참조하면

일괄적으로 값을 입력하게 된다. 이때 수식을 입력한 후 Ctrl+Enter를 입력해야 빈 셀에 같은 수식을 입력할 수 있다.

 

 

마지막으로 방금 입력한 값은 수식이어서 값이 변경되는 걸 막기 위해 수식을 값으로 변경해준다.

(Ctrl+C, Ctrl+Alt+V)