쓰고 담다

엑셀에서 수식은 아주 널리 쓰이는 유용한 기능 중 하나다.

# 사용자 지정 수식 만들기

[수식]-[이름 정의]를 누르고 지정된 셀을 클릭하면 사용자가 지정한 수식을 만들 수 있다.

예를 들어

기존에 원주율은 =pi를 입력해야지 값이 나왔다. 하지만 위 기능을 이용하여,

'파이'라는 새 이름을 지정해주면 그 후로는 =파이 입력 시 원주율이 뜬다.

하나의 값뿐만 아니라 행들에 있는 데이터들을 통째로 지정할 수도 있다.

위 방법으로 A열을 데이터 1로 지정하고, B열을 데이터 2로 지정한 뒤

함수 SUMPRODUCT를 이용면, '데이터 1'이라는 글자 자체를 함수 요소처럼 쓸 수 있게 된다.

이때 SUMPRODUCT함수 특성상 데이터 1과 데이터 2의 행길이는 같아야 한다.

위 기능을 이용하여 조금 더 응용해보자.

  - H열의 매출액에 "매출액"이라는 이름을 정의

  - J열의 수익률에 "수익률"이라는 이름을 정의

  - 정의된 이름의 데이터를 이용하여 J열의 총 매출액과 총 수익금 계산

  - G열을 "판매수량"이라 정의하고 J열의 총 판매량 계산

이 방법으로 수식을 작성하게 되면 눈으로 보기에도 훨씬 쉽고 수식도 비교적 간단하게 만들 수 있다.

단 이름은 문자나 밑줄로 시작하고, 숫자, 마침표, 밑줄을 사용할 수 있다. 공백이나 이외의 기호는 사용할 수 없다.

# 수식 참조되는 셀 추적하는 법

[수식]-[수식 분석]-[참조되는 셀 추적]

수식이 복잡한 경우 어떤셀에서 수식이 왔는지 확인하기 어렵다.

이로 인해 수식이 과연 맞는지 틀린 지 구분하기 힘들고 수식 특성상, 사소한 작은 실수 하나가,

데이터 전체를 망칠 수도 있다. 위 기능을 사용하면 그림처럼 쉽게 수식에 사용된 셀들을 알 수 있다.

총수익금은 SUM(매출액)*수익률로 구했으므로,

사전에 이름 정의한 매출액에 해당되는 셀들과 수익률 셀을 곱한 값에서 비롯된 것임을 확인할 수 있다.

파란색 작은 동그라미는 선택된 셀을 의미한다.

[참조하는 셀 추적] 기능으로 현재 셀이 어떤 수식에 참조가 되는지도 확인할 수 있다.
[수식 표시] 기능으로는 현제 시트에 있는 모든 셀에 있는 수식들을 표시해준다. 어떤 수식이 사용되었는지 한눈에 보고 싶을 때 유용한 기능이다.

#숫자와 문자가 혼합된 셀 숫자만 계산되게 하는 법

SUM함수나 다른 사칙연산 함수 사용 시 만약 셀 안에 '50개'라 고 표시된 경우 '개'라는 문자 때문에 숫자 데이터가 아니게 된다,

그래서 함수에 오류가 생긴다. 하지만 단위를 꼭 표시해야 하는 경우 사용하는 방법이다.

[홈]-[표시 형식]에서 표시 형식 글자 옆 작은 화살 표를 누르면 위와 같은 화면이 뜬다.

날짜, 개, 돈 단위 등등 다양한 숫자 수식어를 쓸 수 있고, 이 문자들은 실제로 함수 사용 시 없는 것으로 취급되기 때문에, 함수사용시 오류가 발생하지 않는다. 뿐만 아니라 사용자가 직접 문자를 지정할 수도 있다 예를 들면 '번' 같은 경우이다. 

현재 목록에는 없지만 직접 타이핑하여, 사용할 수 있다.

% 오류의 종류

##### : 셀의 너비가 좁아 내용을 모두 표현하지 못함.

#DIV/0! : 0으로 나눗셈

#N/A : 수식 참조 위치에 데이터가 존재하지 않음. 또는 수식의 결괏값이 두 개 이상 발생하는데 어떤 값을 표현해야 할지 모름.

#NAME? : 정의되지 않은 이름을 사용하거나, 범위 참조 시 colon이 누락된 경우

#NULL! : 두 개 이상의 셀 범위 값을 사용하는 수식에서 잘못된 연산자가 사용된 경우

#NUM! : 식에 숫자 인수를 잘못 사용하거나, Excel의 최대 숫자 범위를 초과하는 경우

#REF! : 다른 수식에서 참조하고 있는 셀의 내용이 삭제된 경우 #VALUE! : 잘못된 인수나 값을 사용하는 경우.

#다른 수식 옵션

[수식]-[수식 분석]-[수식 계산]: 여러 단계의 계산을 수행하는 식의 계산 과정을 단계적으로 볼 수 있다.

[수식]-[계산]-[계산 옵션]

자동(default): 계산에 필요한 데이터가 바뀔 때마다 새로 계산   - 계산량이 많고, 이 계산에 필요한 여러 데이터를 수정해야 한다면 많은 시간이 소요된다.

데이터 표만 수동: 데이터 표 이외의 사항이 바뀔 때는 바로 계산을 update 하지만, 데이터 표 내용이 바뀔 때에는 하지 않는다.

수동: 통합 문서를 저장할 때, 또는 "지금 계산" 버튼을 누를 때 계산을 수행한다.

[수식]-[수식 분석]-[조사식 창] : 수식이 들어 있는 셀들에서 조사식을 추가해 놓으면, 모든 계산 결과와 수식을 조사식 창에 보여줌. 큰 워크시트에서 수식들이 연결되어 있는 경우 편리

엑셀을 사용하다 보면, 단위를 변환해야 할 때가 많다.

이때 네이버를 켜서 직접 변환하면, 시간이 많이 걸리므로,

훨씬 편한 엑셀 함수를 이용하자.

CONVERT함수를 이용해서 마일 단위를 미터로 바꿔보자.

엑셀 자체에서 mi는 마일로 m은 미터 단위로 자동으로 인식된다. 

A2: 바꿀 숫자.

A3:기존 단위.

B3:바꿀 단위.

하면, 위의 결과처럼 변환된 값이 나온다.

굳이 네이버에 들어가서 따로 단위 환산할 필요 없이.

convert함수를 이용하면 편리하게 단위를 바꿀 수 있다.

뿐만 아니라,

그램, 톤, 파운드, 미터, 마일, 해리, 인치, 뉴턴, 마력, 테슬라, 섭시, 화씨, 절대 온도, 갤런, 리터 등등 다양한 단위들이 있으니 활용하자.

#HLOOKUP

수식: =HLOOKUP("1분기",D4:F5,2)

원하는 열의 정보를 원할 때 쓰는 함수.

"1분기"라는 글자가 들어간 열의 정보를 원한다, D4:F5 데이터가 위치해있는 범위, 2 나타낼 정보의 행위치.

표에 있는 1분기라는 열의 두 번째 행의 정보를 표시한다.

#INDEX

수식: =INDEX(D4:F6,3,1)

D4:F6 범위의 3번째 행, 1번째 열의 정보를 표시한다.

#MATCH

수식: =MATCH(G5,F5:F7,0)

G5: 원하는 데이터

F5:F7: 범위

1 : 보다 작음

0 : 같음

-1 : 보다 큼

범위 안의 원하는 데이터가 있는 행의 위치를 알고 싶을 때 쓴다 85보다 같은 경우 0을 넣고 85보다 작은 데이터의 경우 1 85보다 큰 경우 -1을 입력한다.

#VLOOKUP

수식: =VLOOKUP("1분기",B2:C4,2)

"1분기" : 구하고자 하는 목표.

B2:C4 : 범위.

2 : 데이터가 위치한 열 번호.

VLOOKUP의 단점은 구하고자 하는 부분이 반드시 앞쪽에 위치해야 한다는 것이다. 이런 단점을 보안하기 위해 위에서 알아보았던, INDEX와 MATCH를 복합적으로 쓰는 것이다.

만약 우표의 열의 위치가 앞뒤로 바뀌었을 경우를 생각해보자.

위와 같이 응용하면 된다.

#SUMIF

수식: =SUMIF(C2:C5,"신입",D2:D5)

원하는 항목만 골라서 더하고 싶을 때 쓴다.

C2:C5 : 항목이 위치한 열의 범위.

"신입" : 항목.

D2:D5 : 더할 데이터가 위치한 열의 범위.

#SUMIFS

수식: =SUMIFS(B3:B10,C3:C10,"=신입*",D3:D10,"서울")

SUMIF의 경우 조건이 하나만 선택 가능하지만, SUMIFS의 경우 여러 가지 조건을 설정할 수 있다.

B3:B10 : 더할 데이터 범위

C3:C10 : 첫 번째 조건 범위.

"=신입*": 첫번째 조건.

D3:D10 : 두 번째 조건 범위.

"서울": 두번째 조건.

수식 사용 시 신입 중 서울 거주자의 수 15명의 표시된다.

#SUMPRODUCT

수식: =SUMPRODUCT(B3:B7,C3:C7)

각 해의 숫자들을 곱한 것을 모두 더한 값을 구할 때 쓴다.

B3:B7 : 곱할 첫 번째 데이터들의 범위.

C3:C7 : 곱할 두 번째 데이터들의 범위.

#AVERAGE

수식: =AVERAGE(B2:B6)

B2:B6 : 원하는 데이터들이 위치한 셀의 범위.

평균을 구할 때 쓴다.

#AVERAGEIF

수식: =AVERAGEIF(C2:C5,"서울",B2:B5)

원하는 조건을 가진 항목들의 평균을 구할 때 쓴다.

C2:C5 : 원하는 조건이 위치한 범위.

"서울": 원하는 조건.

B2:B5 : 평균을 낼 데이터들이 위치한 범위.

#=AVERAGEIFS

수식: =AVERAGEIFS(B3:B10,C3:C10,"=경력*",D3:D10,"서울")

조건이 하나 이상인 항목의 평균 구하는 법.

B3:B10 : 평균을 구할 데이터들이 위치해 있는 범위.

C3:C10 : 첫 번째 조건이 위치한 범위.

"=경력*": 첫번째 조건.

D3:D10 : 두 번째 조건이 위치한 범위.

"서울" : 두번째 조건.

수식은 경력이면서 서울에 사는 사람들의 평균수로 결괏값은 16이다.

#COUNT

수식: =COUNT(B2:D10)

선택된 범위의 셀 중 숫자가들어간 셀의 수.

#COUNTA

수식: =COUNTA(B2:D10)

선택된 범위의 셀중 데이터가 들어간 셀의 수.

#COUNTBLANK

수식: =COUNTBLANK(B2:D10)

선택된 범위의 셀 중 데이터가 없는 셀의 수.

#COUNTIF

수식: =COUNTIF(B2:D10,"<15")

특정한 조건을 만족하는 셀의 수.

B2:D10 : 범위.

"<15": 조건.

#COUNTIFS

수식: =COUNTIFS(B2:B10,"<15",D2:D10,"서울")

여러 조건을 만족하는 셀의 수.

B2:B10 : 첫 번째 조건이 위치한 셀의 범위.

"<15" : 첫 번째 조건.

D2:D10 : 두 번째 조건이 위치한 셀의 범위.

"서울": 두번째 조건.

15보다 크면서 서울을 포함한 셀의 수.

#FREQUENCY

수식: =FREQUENCY(B2:B5,D2:D4)

빈도를 나타내는 함수.

B2:B5 : 목표하는 데이터들의 범위.

D2:D4 : 빈도를 구할 각 데이터들.

배열이기 때문에 수식 입력 후 CTRL+SHITR+ENTER을 눌러야 한다.

#RANK

수식:=RANK(5,A1:A8)

5: 몇 번째로 큰 숫자인지 구할 데이터.

A1:A8 : 순위를 구할 범위.