데이터 분석 훈련소 8호 학습일지(1/6)
1. 왜 엑셀을 배워야 할까요?
ERP(전사적자원관리시스템)에 데이터가 어떻게 축적되든 그 결과는 보통 엑셀을 이용해 추출해 처리한다. 따라서 엑셀의 기능을 이해하고 사용법을 익혀 업무를 빠르고 정확하게 수행하는 것이 매우 중요합니다. 엑셀을 잘 활용한다는 것은 사용자 입장에서 보고 목표에 맞게 데이터를 정리하는 것을 의미합니다.
→ Excel 사용의 장점 요약
– 사용하기 쉬운
– Fast Processing (빠른 처리 데이터)
– 어디서나 접근 가능
2. 엑셀 활용에 필요한 기본 개념 정리
1. Excel에서 참조 개념 구성
| 참조 방법 | 예 | 설명하다 |
| 상대 참조 | A1 | 열과 행 모두 고정되지 않습니다.수식이 자동으로 위, 아래, 왼쪽, 오른쪽으로 채워지면 해당 셀도 이동됩니다. |
| 절대 참조 | $A$1 | 모든 열과 행은 고정되어 있습니다.수식이 자동으로 위, 아래, 왼쪽, 오른쪽으로 채워져도 해당 셀은 항상 고정 |
| 혼합 참조(열 절대 참조) | $A1 | 열만 고정합니다. 행 번호(1,2,3)는 수식을 위아래로 자동 채울 때 변경되지만 열 번호(A)는 왼쪽과 오른쪽으로 자동 채울 때에도 동일하게 유지됩니다. |
| 혼합 참조(선 절대 참조) | 1 호주 달러 | 행만 고정됩니다.수식이 자동으로 위아래로 채워지면 열번호(A,B,C)가 바뀌는데 자동으로 위아래로 채워도 열번호(1)는 고정 |
2. 표시 형식 이해 및 사용
표시 형식은 셀 형식 중 하나로 데이터가 표시되는 방식을 설정하는 기능입니다. 단축키(Ctrl+1)로 셀 서식 창으로 이동하거나, 사용자가 지정한 기호로 설정할 수 있습니다.
1) 맞춤 거래 기호(기본값)
| 상징 | 중요성 | 예 |
| # | 숫자의 대표값(중요 0 생략) | 001 → # → 1 0 → # → (공백) |
| 0 | 숫자의 대표값(의미 없는 0도) | 001 → 0 → 001 0 → 0 → 0 |
| @ | 캐릭터의 대표 가치 | 클라이언트 → @”your” → 클라이언트 |
| , | 숫자 사이: 1000분의 1마다 쉼표 숫자 끝: 1000으로 반올림 |
1000500 → #,##0 → 1,000,500 1000500 → #,##0, → 1,001 |
2) 맞춤 기호(고급)
(1) 양수일 때, 음수일 때, 0일 때, 텍스트가
입력 값의 예: (red)#,##0; (blue)#,##0; 0; “번호 확인”
→ 값이 양수일 경우 텍스트 색상은 빨간색이고 쉼표는 1,000 단위마다 사용됩니다.
→ 값이 음수일 경우 글자색은 파란색이고 쉼표는 1,000 단위마다 사용됩니다.
→ 값이 0일 때 0으로 표시
→ 값이 문자인 경우 “숫자 확인” 표시
(2) 첫 번째 조건이 충족될 때, 두 번째 조건이 충족될 때, 어느 조건도 충족되지 않을 때
입력값 예시: (red)(>=2000)#,##0; (blue)(>=1000)#,##0; #,##0
→ 값이 2,000보다 크거나 같으면 텍스트 색상이 빨간색 / 1,000 단위마다 쉼표
→ 값이 2000보다 작고 1000보다 크거나 같을 때 1000 단위마다 텍스트 색상을 파란색/쉼표로 설정
→ 값이 1,000 미만일 때 1,000 단위마다 쉼표(모든 조건이 충족되지 않은 경우)
삼. 함수의 원리와 기본기능
함수는 복잡한 수식이나 계산을 간단한 명령과 함께 사용할 수 있게 해주는 도구입니다. 함수를 호출하여 각 함수에 필요한 데이터를 입력하고 해당 결과 값을 출력하여 사용합니다.
1) COUNT 함수 계열
(1) 카운트 기능: 특정 범위에서 “디지털 데이터”를 포함하는 셀의 수를 카운트
입력 예: =count(C5:C12)
(2) counta 함수: 특정 범위의 데이터를 포함하는 셀(비어 있지 않은 셀)의 개수를 센다.
입력 예: =counta(C5:C12)
(3) countblank 함수: 특정 범위에서 빈 데이터가 있는 셀의 개수 찾기
입력 예: =countblank(C5:C12)
(4) countif 함수: 특정 범위에서 조건을 만족하는 셀의 개수를 센다.
입력 예 1: =countif(C5:C12, “A”)
입력 예 2: =countif(C5:C12, “>=5”)
✓ 일반적으로 함수 내에서 부등식을 사용할 때는 “”를 붙이지 않지만 countif!
2) IF함수를 통해 데이터를 분류하고 IF함수를 중첩
IF 함수는 부등식/등호(>, <, =)를 이용하여 조건을 가정하고 조건을 만족하는 값과 그렇지 않은 값을 다르게 표시하는 함수입니다. IF함수는 한 번 사용할 때마다 데이터가 두 가지로 분류되기 때문에 데이터를 분류하는데 사용된다.
Q) 백화점 고객매출액은 $5000 이상이면 “Platinum”, $2000 이상이면 “Gold”, 나머지는 “Silver”로 분류됩니다.
입력 값: =if(C5 >= 5000, “플래티넘”, if(C5 >= 2000, “골드”, “실버”))
→ IF 함수를 두 번 사용하므로 데이터를 세 가지 범주로 나눕니다.
3) vlookup 기능으로 데이터 불러오기
이 함수는 공통 기준 열을 기반으로 n번째 데이터를 검색합니다. 방대하고 다양한 데이터 중에서 원하는 데이터를 불러오는 것이 작업의 시작이기 때문에 실제 작업 과정에서 가장 많이 사용되는 기능 중 하나입니다.
입력값 : =vlookup(조회할 참조 데이터, 데이터 범위(공통 참조 컬럼부터), 조회할 데이터 컬럼 번호, 0)
입력 예: =vlookup(D5,Employee information list!$D:$J,3,0
※ 이용시 주의사항
– 현재 생성 중인 테이블과 원본 데이터 사이에 공통 참조 열(예: 열 4)이 있어야 합니다.
– 가져오려는 데이터는 원본 데이터의 일반 기준 열 오른쪽에 있어야 합니다.
– 공통 기준 컬럼에 중복된 데이터가 없어야 함
4) 인덱싱 및 매칭 기능으로 데이터 로드
(1) 일치 기능: 찾고 있는 값의 행/열 번호를 알려줍니다.
입력 값: =match(lookup_value, lookup array, (일치 유형))
→ 결과는 숫자여야 합니다(어떤 숫자)
(2) 인덱스 기능: 특정 데이터 범위 내에서 필요한 행 번호와 열 번호 검색
입력값: =index(array, row_num, (column_num))
(3) index&match 함수 사용: index 함수에서 row_num과 column_num에 해당하는 데이터에 대해 match 함수를 이용하여 필요한 값을 자동으로 구하는 과정
입력값: =index(array, match(lookup_value, lookup array), match(lookup_value, lookup array))