얼마 전, 기관에서 배포하는 공시 자료를 확인할 일이 생겨 정말 오랜만에 엑셀 작업을 해봤습니다.
이전에 관련 자격증을 취득한 후 엑셀과 액세스를 완벽히 다룰 수 있다 생각했는데 실상은 그게 아니더군요.
정말 간단한 정보만 기록된 문서였는데 함수나 서식, 필터 등등.. 열심히 공부했던 것들이 전부 기억이 나질 않았습니다.
해서, 일상에서 흔히 사용될만한 내부 함수와 필터, 조건부 서식 등 사용법을 몇 가지 다뤄보려 합니다.
본 포스팅은 복습 겸 유용한 정보를 공유하고자 작성하는 것이며 첨부된 이미지와 자료는 직접 제작했습니다.
자료는 추후 추가되거나 삭제될 수 있으며 엑셀 기능 활용법과 예시를 먼저 작성해봤습니다.
목차
● 1. 함수
·········· 1-1. 설명
·········· 1-2. 연산자와 연결자
·········· 1-3. 배열 수식
·········· 1-4. 함수 마법사
·········· 1-5. 함수 목록
● 2. 조건부 서식
·········· 2-1. 설명
·········· 2-2. 규칙 만들기
● 3. 고급 필터
·········· 3-1. 설명
·········· 3-2. 고급 필터 적용
● 4. 실무 함수 예제
·········· IF 함수(조건문)
함수
▶ 1-1. 설명
엑셀 내에서 사용하는 표준 함수는 인수를 전달받아 지정된 순서/구조에 맞춰 계산됩니다.
함수의 형태는 비슷하지만, 전달받는 인수의 타입에 차이가 있으며 일반적으로 다음과 같은 형태를 띠고 있습니다.
함수명( 인수(논리식), 인수(표현식), 인수(데이터) )
함수명( 인수(범위), 인수(기준) )
함수명( 인수(기준), 인수(표현식), 인수(표현식) )
위에서 볼 수 있듯이 함수를 호출하는 방식은 모두 동일하지만, 전달해야 될 인수의 타입은 함수에 따라 다르며 인수 타입에 대한 자세한 설명이 필요한 경우 함수 마법사나 함수 목록을 참고하시면 됩니다.
▶ 1-2. 연산자와 연결자
| 산술연산자 | 뜻 | 비교연산자 | 뜻 |
| + | 더하기 | = | 같음 |
| - | 뺴기(기호) | <> | 같지 않음 |
| / | 나누기 | >, >= | 보다 큼 / 크거나 같음 |
| * | 곱하기 | <, <= | 보다 작음 / 작거나 같음 |
| ^ | 지수(승, 제곱) | ... |
엑셀에서 사용되는 연산자를 표로 정리했습니다.
일상에서도 흔히 접할 수 있는 수학 기호와 비교 연산자로 이뤄져 있어 추가 설명은 필요하지 않겠지만,
표에 추가하지 못한 텍스트 연결자 앰퍼샌드(&)와 특별한 상황에서의 연산자 사용 방법에 대해 예시를 작성해뒀으니 궁금하신 분들은 아래 글을 확인해보시기 바랍니다.
연산자 예시(1)
위에서 말한 특별한 상황은 참조 셀이 없는 상황에서 비교 연산자를 사용한 경우를 말합니다.
아래 예시는 시험 점수가 70점이 넘는 학생의 수를 구한다고 가정했을 때 간단하게 작성할 수 있는 식이며,
함수는 COUNTIF를 사용했습니다.
※ COUTIF : 지정한 범위 내에서 기준을 만족하는 셀의 개수를 반환하는 함수
COUNTIF문의 형태는 아래와 같습니다.
= COUNTIF( range, criteria )
미리 정해둔 조건으로 식을 작성하면 아래와 같습니다.
조건 : 시험 점수가 70점이 넘는 학생
풀이 : =COUNTIF( E5:E34, ">="&70 )
해설 : =COUNTIF( 참조 범위, 기준(조건) )
여기서, 풀이를 자세히 보면 비교 연산자를 겹 따옴표로 감싸고 있는 것을 볼 수 있습니다.
이는 앞에서 말했듯 비교 연산자의 특성 때문인데 관계 연산자의 경우 좌우에 피연산자를 필요로 하는 이항 연산자로 구분됩니다.
즉, 위 예시와 같이 값을 직접적으로 비교할 참조 셀이 없다면 겹 따옴표로 묶어 텍스트(문자)로 변환시켜야 하며, 함수가 실행된 이후에 값을 비교하게끔 만들어줘야 합니다.
뒤에 나오는 앰퍼샌드(&)는 텍스트 연결자이며 좌우에 입력된 값의 형태를 텍스트(문자)로 변환하여 이어 붙입니다.
▶ 1-3. 배열 수식
엑셀 내에서 표준 함수만으론 표현할 수 없는 복잡한 계산을 할 때 흔히 배열 수식을 사용합니다.
배열이란 컴퓨터 과학에서 자주 사용되는 기초적인 자료 구조로 서로 대응하는 데이터의 집합을 뜻합니다.
이러한 집합을 계산 기호로 연결한 것이 배열 수식이며, 정형화되지 않은 문제의 값을 도출할 때 특히 유용하게 사용됩니다.
배열 수식에 대한 설명은 아래 예시에서 확인하실 수 있습니다.
배열 수식_예시(1)
| / | A | B | C | D |
| 1 | / | 수량 | 가격 | 합계 |
| 2 | 만화책 | 1200 | 3000 | {=SUM(B2:B3*C2:C3)} ↓ =SUM({3600000;2800000;}) |
| 3 | 단편 소설 | 400 | 7000 |
위 표에서 합계 부분을 자세히 봅시다.
중괄호로 감싸 져 있는 { =SUM( B2:B3*C2:C3 ) }은 얼핏 보면 일반적인 SUM함수를 사용한 것 같습니다.
하지만, 식을 완성한 후 반환되는 실제 결과 값은 6400000이며 이는 SUM(B2*C2, B3*C3)과 같은 결과를 출력합니다.
앞에서 말했듯이 배열은 서로 대응하는 구조 즉, 표 테이블과 같은 데이터 필드에서 동일 선상에 있는 값을 말합니다.
더 자세한 설명을 위해 다음 예시도 보겠습니다.
배열 수식_예시(2)
이전 연산자 예시에서 조건을 한 가지 더 추가한 후 배열 수식으로 작성한 것입니다.
조건 : 시험 점수가 70점이 넘는 민들레반 학생
풀이 : { =SUM( ( (C5:C34)="민들레반" ) * ( (E5:E34)>=70) ) }
해설 : =SUM( Number 1, Number 2...)
위 풀이를 예시(1)과 똑같이 적용해보면 어떠한 결과를 반환할지 예상할 수 있습니다.
↓
=SUM( ( (C5)="민들레반") * ( (E5) >= 70) ) ··· ∴ 1
=SUM( ( (C6)="민들레반") * ( (E6) >= 70) ) ··· ∴ 0
=SUM( ( (C7)="민들레반") * ( (E7) >= 70) ) ··· ∴ 0
...
=SUM( ( (C34)="민들레반") * ( (E34) >= 70) ) ··· ∴ 1
예시(1)처럼 C5와 E5를, C6과 E6를, C7과 E7을 각각 대응시켜 값을 계산하게 되고 결국, 두 조건을 만족할 때에만 참을 의미하는 숫자 1을 반환하게 됩니다.
만약, 둘 중 하나의 조건이라도 거짓이 될 경우 0을 반환하므로 이는 1 * 0 또는 0 * 1과 같으며 학생의 수(셀 개수)를 계산하는 데에 적합한 식이 됩니다.
▶ 1-4. 함수 마법사
함수 마법사는 엑셀에서 지원하는 함수의 전체 목록을 확인하거나 상세한 도움말이 필요한 경우 종종 사용됩니다.
함수 마법사를 이용하면 해당 함수를 작성하는 데에 필요한 요소와 자세한 설명을 함께 표시하여 보다 쉽게 조건식을 작성할 수 있습니다.
※ 함수 마법사 단축키 : {SHIFT} + {F3}
▶ 1-5. 함수 목록
*** 함수 전체 목록과 자세한 설명은 아래 링크에서도 확인하실 수 있습니다. ***
Excel 함수(사전순)
support.microsoft.com
조건부 서식
▶ 2-1. 설명
새 규칙 만들기
- 조건부 서식은 시각적 요소를 추가하여 사용자의 편의를 돕는 기능으로, 데이터 간 구분이나 비교 시 사용됩니다.
배경색, 글꼴, 테두리 등 원하는 요소를 편집할 수 있으며 위 예시는 민들레반 학생들의 성적에 노란색 배경을 추가하여 데이터를 구분한 것입니다.
또 다른 예로, 데이터 간 점수 비교를 위해 데이터 막대나 아이콘 집합을 사용할 수 있습니다.
다음 예시를 보겠습니다.
데이터 막대 / 아이콘 집합
데이터 막대로 표시하면 위와 같이 학생들의 평균 점수를 직관적으로 비교할 수 있습니다.
데이터 막대나 아이콘 집합의 경우 지정한 범위 내에 적용할 수 있는 조건을 자동으로 설정해주며
사용자 지정 서식 즉, 새 규칙 만들기에선 사용자가 데이터 간 구분을 위해 조건식을 직접 작성하게 됩니다.
조건부 서식의 적용 방법은 [2-2. 규칙 만들기]를 참고해주시기 바랍니다.
▶ 2-2. 규칙 만들기
조건부 서식의 규칙을 만들 때에는 적용할 데이터의 범위를 지정한 후 실행하셔야 됩니다.
위 사진에서처럼 서식을 적용할 데이터 필드를 마우스로 드래그하여 범위를 선택하고 규칙을 설정하시면 됩니다.
조건부 서식_예시(1)
조건 : 민들레반 학생의 데이터에만 노란색 배경 추가
풀이 : =( ($C$5:$C$34)="민들레반" )
해설 : 위 풀이의 반환 값이 True(1)를 반환하는 셀에만 서식 적용
- 서식을 적용할 데이터의 범위를 마우스로 드래그하여 선택합니다.
- [서식] 메뉴에서 [조건부 서식]을 클릭하여 대화 상자를 엽니다.
- 대화 상자 하단의 [새 규칙 만들기]를 선택하고 조건식을 작성합니다.
- 배경색이나 글꼴 등 서식을 선택합니다.
- 완료 버튼을 눌러 적용하면 위 사진과 같이 선택한 필드 내 데이터에 사용자 지정 서식이 적용됩니다.
↓
＊＊＊ 참고 이미지 ＊＊＊
고급 필터
▶ 3-1. 설명
고급 필터는 주로, 표 테이블(데이터 베이스)에서 특정 조건으로 자료를 검색하거나 추출할 때 사용됩니다.
결과를 새 시트에 붙여 넣을 수 있고, 분류 기준을 사용자가 지정할 수 있다는 장점이 있습니다.
또, 자동 필터와 달리 기존 데이터베이스를 직접 편집하지 않는다는 특징을 갖고 있습니다.
고급 필터_예시(1)
조건 : 구입일이 7일이 경과하고 판매가가 20,000원 이상인 도서명
풀이 : =AND( DAYS360( F4, $H$1, 0 )>7, G4>=20000)
해설 :
1. AND함수는 작성한 조건을 모두 만족할 때 TRUE를 반환
2. DAYS360 함수는 두 날짜 사이의 날짜 수 즉, 차이를 반환함
위 사진은 고급 필터를 실행한 결과입니다.
고급 필터도 조건부 서식과 마찬가지로 데이터베이스를 필요로 하기 때문에 범위를 선택한 후 실행하셔야 됩니다.
기준 · 조건식은 동일 시트 내에 아무 셀에나 작성하고, 고급 필터 대화 상자에서 참조 셀로 지정해주시면 됩니다.
고급 필터를 적용하는 방법은 아래 [3-2. 적용 방법]을 확인해주시기 바랍니다.
▶ 3-2. 적용 방법
1. 고급 필터를 실행하기 전, 데이터 구분을 위한 기준식(C12)을 작성합니다.
* 위 사진에선 C11:C12셀을 조건 범위로 지정했으나, 식으로 구분되지 않는 C11셀은 계산에 포함되지 않습니다.
2. 필요한 데이터는 "도서명"이므로, 열 머리글(D3)을 복사하여 아무 위치에(C14) 붙여 넣습니다.
3. 데이터 범위(C3:H9) 전체를 지정한 후 고급 필터를 실행합니다.
4. 고급 필터 대화 상자에서 필요한 내용을 입력하고 확인 버튼을 누릅니다.
- 필터 결과를 덮어쓰려면 [ 현재 위치에 필터(F) ]를, 다른 위치에 복사하려면 [ 다른 장소에 복사(O) ]를 선택합니다.
- [ 목록 범위(L) ] : 고급 필터 실행 시 자동 지정됩니다.
- [ 조건 범위(C) ] : 기존에 작성해둔 조건식 셀을 드래그하여 선택해줍니다.
- [ 복사 위치(T) ] : "도서명"만을 추출할 것이므로, 복사 위치를 C14셀로 지정합니다.
실무 함수 예제
▶ 1-1. IF 함수
IF 함수는 뜻 그대로 "만약 ~하면" 또는, " ~이면"과 같습니다. 예로, '~이면 ~의 합을 구하라'와 같이 기준 식이 있고 표현할 값(합)이 있는 경우 IF문을 사용할 수 있습니다.
*** 아래 예시 참고 ***
| IF 함수 * IF ( Logical_Test, [ Value_IF_True ], [ Value_IF_False ] ) → IF ( 논리 기준식, [ 참일 때 값 ], [ 거짓일 때 값 ] ) |
| 예시 1. 판매가가 10000원 이상이고, 제품명의 첫 두글자가 "가정"으로 시작하는 물품들의 원가를 합하여라. 단, 조건을 만족하지 않는 경우 공백으로 처리하라. = SUM( IF( ( $F$4:$F$9>=10000 ) * ( LEFT($C$4:$C$9, 2)="가정" ), $D$4:$D$9, "" ) ) 풀이 : - 조건이 두 개 이상이기 때문에 소괄호와 논리연산자 " * "를 이용하여 AND 조건으로 묶어줍니다. - 조건을 만족하는 경우 반환할 값과 만족하지 않을 때의 값을 각각 지정해줍니다. * 참일때 값 : 합계를 구할 범위 * 거짓일때 값 : "" (공백 처리) 참고 : * LEFT( Text, [ Num_Chars ] ) → LEFT ( 추출하려는 문자/문자열, [ 추출할 문자 수 ] ) * SUM( number1, [ number2 ] ... ) → SUM( 값 or 셀 or 범위, [ 값2 or 셀2 or 범위2 ] ... ) |
댓글