쓰고 담다

# 행렬의 단순 연산

벡터의 계산과 마찬가지로 배열 수식을 쓴다. 수식 정의에서 행렬 A, B, D를 정의한 뒤.

수식을 입력한 뒤, [CTRL]-[SHIFT]-[ENTER]를 입력한다.

# 전치 행렬, 역행렬, 행렬식 구하기

각 행렬의 전치행렬을 구해보자.

=TRANSPOSE(A),=TRANSPOSE(B) 한 후 [CTRL]-[SHIFT]-[ENTER]를 입력한다.

각행렬의 역행렬을 구해보자.

=MINVERSE(A),=MINVERSE(B) 한 후 [CTRL]-[SHIFT]-[ENTER]를 입력한다.

역행렬의 결괏값과 원래 행렬의 곱은 0 행렬이 나옴을 확인할 수 있다. 행렬 B의 경우 역행렬이 없는 특이 행렬이다.

각 행렬의 행렬식은

=MDETERM(A),=MDETERM(B) 한 후 엔터를 입력한다.

# 역행렬을 이용하여 방적식 풀기.

=MMULT(MINVERSE(A), G6:G8)를 입력하면, 각 미지수의 값을 구할 수 있다.

#Gram-Schmidt 직교화 과정

그램 슈미트 직교화를 엑셀을 이용해서 해보자. 먼저 식에 의해서 v1는 벡터 A이고 각 벡터의 단위 벡터들을 수식 정의를 이용하여 ua, ub, uc로 정의한다. v1벡터와 v1벡터의 단위 벡터를 구한 뒤 v2벡터를 식에 표시된 공식을 이용하여 구한다.

이때 수식 =B-E87:E89를 쓴다. 전에 먼저 구했던 projection B on A를 이용한다.

v2의 단위 벡터를 구하고 그를 바탕으로 v3를 구한다. 수식 =D-G87:G89-SUMPRODUCT(D,D108:D110)*D108:D110 를 사용한다. 벡터는 결괏값이 실수와는 다르므로 앞에서 와마찬가지로 반드시 [Ctrl]-[Shift]-[Enter]를 한다.

각 벡터 v1,v2,v3를 구했으면 각 벡터들의 단위 벡터들을 내 접한 값이 0 임을 확인하다. 엑셀 프로그램의 오차로 인해 아주 작은 숫자가 생기지만 0으로 봐도 무방할 정도로 작으므로 값은 0으로 취급할 수 있다.

액셀에서 벡터를 계산하는 방법을 알아보자.

엑셀에서 실수를 계산할 때와는 달리 어떤 차이점이 있을까?

#벡터의 합과 차

그림처럼 3개의 벡터가 있을때 벡터의 연산 방법을 알아보자. 가장 먼저 앞에서 활용하였던 수식 정의 기능을 이용하여 벡터 A, B, D를 정의하자.

A+B를 계산할때 데이터를 입력할 셀을 드래그한다. 그림처럼 A7, A8, A9를 드래그하고 수식 =A+B를 입력한 뒤 그냥 엔터를 누르지 말고, [CTRL]+[SHIFT]+[ENTER]를 동시에 누르자.

결괏값이 그림처럼 도출된다. A-B의 경우에도 같은 방법으로 하자. 

이때 주의 해야할점은 백터의 정렬 방식이 동일해야 한다는 것이다. 그림에서 벡터의 나열 방법은 위에서 아래 방향이다. 그러므로 벡터를 연산할 때는 같은 배열의 벡터끼리만 계산 가능하다.

벡터를 스칼라 값과 곱할때는 =C*A수식을 입력한 뒤 마찬가지로 [CTRL]+[SHIFT]+[ENTER]를 누른다.

#벡터의 내적

벡터의 내적은 위의 그림처럼 대응하는 각요소들의 값들을 곱한 뒤 더한 값이다.

=SUMPRODUCT(A,B) 수식을 이용하면 벡터의 내적 값을 알 수 있다.

#벡터의 외적

벡터의 외적을 구하기 위해서는, 위의 식이 필요하다.

위의 식을 셀에 대입하기 위하여 전에 학습하였던 INDEX함수를 쓸 것이다.

i행에 들어가야 할 수식은

=INDEX(A,2)*INDEX(B,3)-INDEX(A,3)*INDEX(B,2) 이다.

벡터 A의 2번째 행에 있는 숫자를 추출하여 벡터 B의 2번째 행에있는 숫자를 곱하고 그 값을 다시 벡터 A의 3번째 행의 숫자와 벡터 B의 2번째 행의 숫자와 곱한 수를 뺀 결괏값이 나온다.

마찬가지로 다른 행의 값들도 구한다.

벡터의 외적 i, j, k값을 구했다면, 외적과 벡터의 관계를 이용하여 검산할 수 있다. 구해진 벡터의 외적에 각 벡터들을 내적 하면 값은 0이나 온다.

# 벡터의 크기와 단위 벡터

먼저 벡터의 크기 먼저 구해보자 그림에서 처럼 벡터의 크기를 구하는 공시를 바탕으로 수식을 작성한다.

=SQRT(SUMPRODUCT(A,A)) 식을 쓰면 벡터 A의 크기를 구할 수 있다.

같은 방법으로 벡터 B, D의 크기를 구할 수 있다.

구한 벡터의 크기를 이용하여 각 벡터의 방향벡터를 구한다. 방향벡터를 구할 때 앞서 쓴 것처럼 배열식[Ctrl]+[shift]+[Enter]를 입력해야 한다.

수식은 =A/B10

# 두 벡터의 사잇각

벡터 사잇각을 구할 때 위의 공식을 활용하여 수식을 만들어보자.

cosθ를 먼저 구해야 하므로 수식

=SUMPRODUCT(A,B)/SQRT(SUMPRODUCT(A,A)*SUMPRODUCT(B,B)) 를 대입한다.

=acos()를 이용하여 세타의 값을 구한 뒤 라디안으로 표기된 데이터를 =degree를 이용하여 각도 단위로 변환한다.

#벡터의 성분

벡터의 성분을 구해보자. 벡터의 성분을 구하는 식은 위의 공식과 같다.

다음 공식을 엑셀에 수식으로 표현하여 각 성분을 구해보자.

벡터 A가 벡터 B위에 있을 때

=SUMPRODUCT(A,I6:I8)

벡터 B가 벡터 A위에 있을 때

=SUMPRODUCT(B,G6:G8)

벡터 D가 벡터 A위에 있을 때

=SUMPRODUCT(D,G6:G8)

를 대입하면, 각 상황에서의 성분이 나온다. 이때 데이터는 스칼라 값이다.

# 벡터의 사영

벡터의 사영을 구해보자 성분과는 다르게 사영의 결괏값은 벡터이다.

위의 사영을 구하는 공식을 바탕으로 엑셀에 수식을 세운다.

벡터 A가 벡터 B위에 있을 때

=SUMPRODUCT(A,I6:I8)*I6:I8

다른 경우에도 마찬가지로 적는다. 결괏값이 벡터이므로 반드시 배열 꼴을 써야 한다. 수식을 쓴 뒤 [CTRL]+[SHIFT]+[ENTER]를 하자.

# 복소수의 지수함수

복소수가 자연로그의 지수일 때

수식 =IMEXP()를 쓴다.

각각 다른 꼴에 대한 수식을 알아보자.

두 번째 행의 경우

=IMEXP(IMPRODUCT(COMPLEX(-1,0),A))

지수 부분의 복소수 자리에 실수 –1과 복소수를 곱 한식이 들어가게 된다. 실수와 복소수를 함께 계산하기 위해선 COMPLEX함수로 실수도 복소수 형태로 지정해주어야 한다..

세 번째 행의 경우

=IMEXP(IMPRODUCT(COMPLEX(0,1),A))

복소수 A와 복소수 허수 자리만 존재하는 I가 곱해진 것이 지수이므로, 지수 자리에 위와 같은 수식을 넣는다.

네 번째 행의 경우

=IMEXP(IMPRODUCT(COMPLEX(0,-1),A))

세 번째 경우와 마찬가지로 수식을 사용한다.

#복소수의 로그함수

복소수의 로그함수를 구하고 싶을 경우

=IMLN() 꼴을. 이때 복소수 로그 함수의 공식으로 인해

logA의 실수부는 ln(복소수 A의 크기)와 같고, 허수부의 숫자는 복소수 A의A 방향각의 크기와 같다.

#복소수의 삼각함수

복소수의 삼각함수를 계산해보자.

sinA : =IMSIN(A)

cosA : =IMCOS(A)

tanA :　=IMTAN(A)

cotA : =IMCOT(A)

secA : =IMCOT(A)

cscA : =IMCSC(A)

식 sinA=(e^iA-e^-iA)/2i를 사용해 검산해볼 수 있다. 그림에서처럼 수식=IMDIV(IMSUB(Sheet1! E77, Sheet1! E78), COMPLEX(0,2))를=IMDIV(IMSUB(Sheet1!E77,Sheet1!E78),COMPLEX(0,2)) 넣으면 결과 같은 것을 확인할 수 있다.

# 2차 방정식의 해구 하기

ax^2+bx+c=0

꼴의 이차방정식의 해를 근의 공식을 이용해서 구해보자.

먼저 수식 정의를 통해 a,b,c,Da, b, c, D를 각각 cd,cb,cc,cdcd, cb, cc, cd로 정의한다. [수식]-[이름 관리자]에서수식]-[이름관리자] 확인할 수 있다. 여기서 D는 b^2-4ac이므로 수식을 넣고,

두 개의 근 x1,x2x1, x2에 근의 공식 수식을 대입한다. 이때 전에 포스팅했던 수식 활용에서IF문을 이용해서 근이 실수일 때와 복소수일 때 모두 결괏값이 나오게 할 수 있다. x1에.x1 아래 수식을 대입하면,

=IF(cd>=0,(-Cb+SQRT(cd))/2/Ca,COMPLEX(-Cb/(2*Ca),SQRT(-cd)/2/Ca))

에서 D값이 0보다 크거나 같을 때는 근인 공식에 의해 실수 범위의 앞쪽의 결괏값이 오고, 0보다 작을 때에는 복소수 범위의 뒤쪽 값이 온다.

x2에는 같은 수식에 부호만 바뀐 수식을 넣으면 값을 구할 수 있다.

=IF(cd>=0,(-Cb-SQRT(cd))/2/Ca,COMPLEX(-Cb/(2*Ca),-SQRT(-cd)/2/Ca))

엑셀에서 복소수의 연산은 정수를 계산하는 일반적인 방법을 사용할 수는 없다.

복소수를 정의하는 법부터 연산 활용하는법을 알아보자.

# 복소수의 정의

먼저 복소수를 정의한 후 뒤에서 할 계산에 사용하기 위해서 복소수 함수를 이용한다.

D2셀에 복소수를 정의할떄,

실수부가 1이고 허수부가 1인 복소수

=COMPLEX(A2, B3) 수식을 입력 시 해당 셀에 복소수가 정의된다.

마찬가지로 실수부가 3이고 허수부가 -2인 복소수도 정의한다.

=COMPLEX(A3,B3)

생성된 각 복소수들을 수식 활용하기에서 배운 방법으로 [수식]-[선택영역에서 만들기]를 통하여,

새로운 수식으로 정의한다.

이 과정을 끝내면 복소수 1+i, 3-2i는 각각 수식 A, B로 정의된다.

이때 i는 엑셀에서 문자가 아닌 복소수의 허수로 인식한다. 복소수를 입력하고 싶을 때 위의 방법으로 입력하지 않으면,

복소수로 인식하지 않으니 함수를 꼭 숙지하도록 하자.

이제 복소수를 사용해 보자.

생성된 복소수의 실수부 허수부 데이터를 뽑아오는 방법을 알아보자.

복소수의 실수부의 데이터를 원하는 경우

=IMREAL(A)로 실수부의 데이터를 불러온다. 이때 A는 사전에 정의해놓았던 복소수 수식이다.

복소수의 허수부의 데이터를 원하는 경우

=IMAGINARY(A)로 허수부의 데이터를 불러온다.

# 복소수의 연산

복소수를 연산할 때, 일반적으로 사칙연산에 사용하는 SUM함수나. +, - /로는 연산이 불가능하다.

연산을 위한 수식을 따로 써야 한다.

각 사칙연산에 따른 수식들을 알아보자.

덧셈 A+B

=IMSUM(A,B)

뺄셈 A-B

=IMSUB(A,B)

곱셈 A*B

=IMPRODUCT(A,B)

나눗셈 A/B

=IMDIV(A,B)

실수 2와 복소수 A의 곱

=IMPRODUCT(COMPLEX(2,0),A)

D4 셀처럼 만약, 연산 결과의 소수점 숫자가 너무 길다면 줄일 수 있다.

=ROUND(IMREAL(D4),4) 를 통하여 복소수 실수부 데이터를 소수점 넷째 자리까지만 표시하게끔 지정할 수 있다.

마찬가지로

=ROUND(IMAGINARY(D4),4) 를 통하여 복소수 허수부 데이터를 소수점 넷째 자리까지만 표시할 수 있다.

각각 줄인 데이터들을 다시

=COMPLEX(F4,G4)로 복소수 형태로 정의하면 된다.

# 켤레 복소수

기존에 정의했던 복소수의 켤레 복소수를 정의하고 싶을 때 쓰는 함수에 대해 알아보자.

A복소수에 대한 켤레 복소수는

=IMCONJUGATE(A) 이다.

연산 결과로 나온 복소수의 켤레 복소수를 구하고 싶을 때는, 위의 함수 괄호 안에 해당 연산 수식을 써넣으면 된다.

A*B에 대한 켤레 복소수를 구해야 할 경우

=IMCONJUGATE(IMRODUCT(A,B))를 통해 구할 수 있다.

마찬가지로 다른 연산에 대해서도 같은 방법을 적용한다.

# 복소수의 기하학적 해석

복소수의 기하학적 해석을 해보자.

복소수의 절댓값을 구하는 방법.

=IMABS(A)

복소수의 방향각을 구하는 방법

=IMARGUMENT(A)

이때 각도의 단위는 라디안이므로 따로 단위 변환을 위한 함수를 쓴다.

=DEGREES(D2)

오일러 공식을 엑셀 함수를 사용하여 증명해보자.

전에 배웠던 수식들을 통하여 삼감 함숫값을 구할 수 있다. 구한 값으로 오일러 공식의 우변을 완성할 수 있다.

좌변은 엑셀의 수식을 이용하여 푼다.

=IMEXP(COMPLEX(0,B열) 하면 오일러 방정식의 좌변의 수식이 완성되고,

결괏값으로 나온 값이 기존에 구하였던 우변의 값과 어떤 차이가 있는지 확인한다.

좌변과 우변이 같음을 알 수 있다.

# 복소수의 멱함수

복소수의 제곱처럼 복소수의 급수를 구하는 방법을 알아보자.

=IMPOWER(A,2)

위 수식의 결과 값은 복소수 A의 2 제곱한 결괏값이 나온다. 괄호 안 두 번째 자리는 지수 자리이다.

위 수식을 바탕으로 DeMoirvre공식을 증명해보자.

오일러 공식을 증명한 방법과 마찬가지로 좌변의 값을 수식으로 계산한 후 우변의 값을 넣으면 둘이 같음을 알 수 있다.

이 때 화면의 수식으로는 =IMPOWER(COMPLEX(D10,E10),3)

 우변의 수식으로는 =COMPLEX(J열,K열)을 한다.

둘의 값이 같음을 확인할 수있 다.

 #차트 만들기

[삽입]-[차트]-[추천 차트]

차트 옆의 + 모양을 누르면 다음과 같이 차트에 더표시할 데이터를 추가로 선택할 수 있다.

차트에 있는 정보를 선별하여서 보고 싶으면, +표시 밑에 있는 필터 모양을 눌러, 원하는 데이터를 선별적으로,

볼 수 있다.

만약, 새로 축에 있는 값들의 간격을 서 촘촘히 하고 싶거나, 다른 설정 변경을 하고 싶으면, 더블 클릭 후 축 서식에서 수정할 수 있다.

데이터 각 축에 있는 데이터들의 위치를 바꾸고 싶을 때, 현재 월 자리와 지역 자리를 바꾸고 싶을 경우,

[차트 클릭]-[마우스 우클릭]-[데이터 선택]-[행/열 전환 클릭]

으로 실행하면 데이터 위치가 바뀐다.

계열 차트 종류 변경: 전체 데이터 계열 중 일부만 다른 종류로 변경 변경하고자 하는 계열의 그래프를 선택한 후, 마우스 우측 버튼 클릭.

추세선 적용: 추세선을 추가하고자 하는 계열의 그래프 선택 후 마우스 우측 버튼 클릭.

보조축: 데이터 계열 간 값차이가 커서 별도의 스케일로 표시하고자 할 경우 계열 그래프 선택 후 마우스 우측 버튼 클릭. 

#이미지 형태의 그래프로부터 데이터 읽어오기.

그래프가 이미지 형태로 있을 때 엑셀 차트로 변환하는 방법이다. 데이터를 따로 모은 i, j열의 데이터를 드래그하면 차트가 생성된다. 먼저 이미지에 보이는 축의 범위(y축 60~180, x축 0~50)를 새로 만들 차트의 축 서식에서 범위를 지정해준다. 차트에 보이는 파란점들이 각 점에서의 데이터의 위치이다.

차트를 생성했으면, 왼쪽 기존의 이미지 파일을 그림판에서 숫자는 없애고 그래프선 부분만 잘라내기 한다.

잘라낸 이미지를 따로 저장한 뒤, 엑셀 차트로 돌아와서 [차트 중앙을 클릭] - [오른쪽 버튼 클릭] - [채우기]에서 그림을 선택한 후에 저장했던 그림을 삽입한다.

이 모든 과정이 끝나면 위 사진처럼 세팅된다. 이때 파란 점을 의 위치를 빨간 곡선 위에 오도록 수동으로 수정해준다.

그러면 엑셀 차트가 완성된다.

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

# 사용자 지정 수식 만들기

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

예를 들어

기존에 원주율은 =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 : 순위를 구할 범위.

피벗 테이블

피벗 테이블은 기존에 방대한 데이터로부터,

원하는 데이터를 분류된 형태로 다시 정리하기 위해 사용하는 기능이다.

[삽입]-[표]-[피벗 테이블]

왼쪽 표에 있는 자료들을 원하는 형태로 새로 만들고 싶을 경우, , 왼쪽 데이터 표들을 모두 드래그한 뒤,

위의 경로대로 피벗테이블을 만든다. 테이블에서 필터 하고 싶은 항목을 '필터', 행에 넣을 항목을 '행', 열에 넣을 항목을 '열'에 넣는다. 값을 보고 싶은 항목을 선택하고 평균값으로 볼지 총합으로 볼지 아니면 그 외 다양한 방법으로 볼지 선택할 수 있다.

[피벗 테이블 도구]-[분석]-[필드 설정]

에서 위 사진에 나오듯이 설정을 바꿔 볼 수 있다.

만약 출신지역에 따른 결괏값들을 각각의 시트로 나누어서 보고 싶다면,

[분석]-[옵션]-[보고서 필터 페이지  표시]를 클릭하면,

화면처럼 경기, 대전, 부산, 서울 지역이 각각의 시트로 나뉜다.

피벗 차트

피벗 테이브로 가 마찬가지로, 원하는 값들을 넣으면 한눈에 파악 가능한 차트로 변환하여 나타내어준다.