조회, 차트, 통계 및 피벗 테이블
기본 함수, 셀 참조 및 날짜 및 시간 함수를 검토 한 후에 Microsoft Excel의 고급 기능 중 일부에 대해 살펴 보았습니다. 재무, 판매 보고서, 운송 비용 및 통계의 고전적인 문제를 해결하는 방법을 제시합니다..
학교 이동- 왜 공식과 함수가 필요한가요??
- 수식 정의 및 작성
- 상대 및 절대 셀 참조 및 서식 지정
- 유용한 기능 알아야 할 사항
- 조회, 차트, 통계 및 피벗 테이블
이러한 기능은 비즈니스, 학생 및 더 많은 것을 배우고 자하는 사람들에게 중요합니다..
VLOOKUP 및 HLOOKUP
다음은 수직 조회 (VLOOKUP) 및 수평 조회 (HLOOKUP) 기능을 설명하기위한 예입니다. 이 함수는 숫자 또는 다른 값을 이해할 수있는 것으로 변환하는 데 사용됩니다. 예를 들어, VLOOKUP을 사용하여 부품 번호를 가져 와서 품목 설명을 반환 할 수 있습니다.
이를 조사하기 위해 제인이 학교에 입을 것을 결정하려고하는 제 4 부의 "의사 결정자"스프레드 시트로 돌아가 봅시다. 그녀는 새로운 남자 친구를 데려 왔기 때문에 더 이상 입는 옷에 관심이 없습니다. 그래서 이제는 무작위로 입는 의상과 신발을 입을 것입니다..
제인의 스프레드 시트에서, 그녀는 세로 칼럼과 신발에있는 의상, 가로 칼럼.
그녀는 스프레드 시트를 열고 함수 RANDBETWEEN (1,3)은 착용 할 수있는 3 가지 유형의 의상에 해당하는 1과 3 사이의 숫자를 생성합니다.
그녀는 5 가지 유형의 신발 중에서 RANDBETWEEN (1,5) 기능을 사용합니다..
Jane은 숫자를 쓸 수 없기 때문에이를 이름으로 변환해야하므로 조회 함수를 사용합니다.
VLOOKUP 함수를 사용하여 복식 번호를 복장 이름으로 변환합니다. HLOOKUP은 신발 번호에서 다양한 종류의 신발을 번역합니다..
스프레드 시트는 의상 용으로 다음과 같이 작동합니다.
그녀는 세 가지 의상 옵션을 가지고 있기 때문에 Excel은 1에서 3까지 임의의 숫자를 선택합니다..
다음 수식은 = VLOOKUP (B11, A2 : B4,2)을 사용하여 숫자를 텍스트로 변환합니다.이 숫자는 B11의 값을 임의의 숫자로 사용하여 A2 : B4 범위를 봅니다. 그런 다음 두 번째 열에 나열된 데이터에서 결과 (C11)를 제공합니다..
우리는 같은 기술을 사용하여 신발을 선택합니다. 이번에는 HLOOKUP 대신 VOOKUP을 사용합니다..
예 : 기본 통계
거의 모든 사람들이 평균 통계에서 하나의 공식을 알고 있지만 비즈니스에 중요한 다른 통계가 있습니다 : 표준 편차.
예를 들어, 대학에 진학 한 많은 사람들이 SAT 점수를 고민했습니다. 그들은 다른 학생들과 비교하여 자신의 순위를 알고 싶을 수도 있습니다. 많은 대학, 특히 권위있는 대학이 SAT 점수가 낮은 학생들을 뒤엎 기 때문에 대학은이 사실을 알고 싶어합니다..
그렇다면 우리는 SAT 점수를 어떻게 측정하고 해석합니까? 아래는 1,870 명에서 2,230 명까지 다섯 명의 학생에 대한 SAT 점수입니다..
이해해야 할 중요한 숫자는 다음과 같습니다.
평균 - 평균은 "평균"이라고도합니다.
표준 편차 (STD 또는 σ) - 이 숫자는 일련의 숫자가 얼마나 광범위하게 분산되어 있는지 보여줍니다. 표준 편차가 큰 경우 숫자가 멀리 떨어져 있고 0이면 모든 숫자가 동일합니다. 표준 편차는 평균값과 관측 값, 즉 1,998과 각 SAT 점수의 평균 차이라고 말할 수 있습니다. 그리스어 기호 σ "σ"를 사용하여 표준 편차를 줄이는 것이 일반적입니다.
백분위 순위 - 학생이 높은 점수를 받으면 최고 99 백분위 수 또는 그와 비슷한 것을 자랑 할 수 있습니다. "백분위 수 순위"는 점수의 비율이 특정 점수보다 낮다는 것을 의미합니다..
표준 편차와 확률은 밀접하게 연관되어 있습니다. 각 표준 편차에 대해 그 수의 표준 편차 안에있는 확률 또는 가능성은 다음과 같습니다.
| 성병 | 점수의 백분율 | SAT 점수의 범위 |
| 1 | 68 % | 1,854-2,142 |
| 2 | 95 % | 1,711-2,285 |
| 삼 | 99.73 % | 1,567-2,429 |
| 4 | 99.994 % | 1,424-2,572 |
보시다시피, 모든 성취 점수가 3 성병의 범위를 벗어날 확률은 실질적으로 0입니다. 왜냐하면 99.73 퍼센트의 점수가 3 성병.
이제 스프레드 시트를 다시보고 어떻게 작동하는지 설명해 보겠습니다..
이제 우리는 공식을 설명합니다 :
= 평균 (B2 : B6)
B2 : B6 범위의 모든 점수의 평균. 구체적으로 말하자면, 모든 점수의 합계를 시험에 응시 한 사람들의 수로 나눈 값.
= STDEV.P (B2 : B6)
B2 : B6 범위의 표준 편차. ".P"는 STDEV.P가 모든 점수, 즉 모집단 전체에 사용된다는 의미이며 단지 부분 집합이 아닙니다.
= PERCENTRANK.EXC ($ B $ 2 : $ B $ 6, B2)
B2 : B6 범위의 누적 백분율을 SAT 점수 (이 경우 B2)를 기준으로 계산합니다. 예를 들어 점수의 83 퍼센트가 Walker의 점수보다 낮습니다..
결과 그래프
결과를 그래프로 표시하면 결과를 더 쉽게 이해할 수 있으며 프레젠테이션에 표시하여 더 명확하게 이해할 수 있습니다..
학생들은 가로 축에 있고 SAT 점수는 1,600에서 2,300 사이의 눈금 (세로 축)에 파란색 막대 그래프로 표시됩니다..
백분위 순위는 0에서 90 % 사이의 오른쪽 세로 축이며 회색 선으로 표시됩니다.
차트를 만드는 방법
차트를 만드는 것은 그 자체로 화제이지만 위 차트가 어떻게 생성되었는지 간략하게 설명 할 것입니다.
먼저 차트에 포함될 셀 범위를 선택하십시오. 이 경우 A2에서 C6까지 우리는 학생의 이름과 숫자를 원하기 때문에.
"삽입"메뉴에서 "차트"-> "권장 차트"를 선택하십시오.
컴퓨터는 "Clustered-Column, Secondary Axis"차트를 권장합니다. "보조 축"부분은 두 개의 수직 축을 그립니다. 이 경우이 차트는 우리가 원하는 차트입니다. 우리는 다른 것을 할 필요가 없다..
차트를 원하는 위치로 이동할 때까지 차트를 움직여서 크기를 조정할 수 있습니다. 만족되면 차트를 스프레드 시트에 저장할 수 있습니다..
차트를 마우스 오른쪽 단추로 클릭 한 다음 "데이터 선택"을 선택하면 범위에 대해 선택된 데이터가 표시됩니다.
"권장 차트"기능을 사용하면 포함 할 데이터, 레이블 지정 방법 및 좌우 세로 축 지정 방법과 같은 복잡한 세부 사항을 다루지 않아도됩니다..
"데이터 소스 선택"대화 상자에서 "범례 항목 (시리즈)"아래의 "점수"를 클릭하고 "편집"을 클릭하여 "점수"라고 말하십시오.
그런 다음 시리즈 2 (백분위 수)를 "백분위 수"로 변경하십시오.
차트로 돌아가서 "차트 제목"을 클릭하고 "SAT 점수"로 변경하십시오. 이제 차트가 완성되었습니다. 두 개의 가로 축이 있습니다. 하나는 SAT 점수 (파란색)이고 다른 하나는 누적 백분율 (주황색)입니다..
예 : 교통 문제
운송 문제는 "선형 프로그래밍"이라고하는 일종의 수학의 고전적인 예입니다.이를 통해 특정 제약 조건에 따라 값을 최대화하거나 최소화 할 수 있습니다. 다양한 비즈니스 문제에 많은 응용 프로그램이 있으므로 작동 방식을 익히는 것이 좋습니다.
이 예제를 시작하기 전에 "Excel Solver"를 활성화해야합니다.
해 찾기 추가 기능 사용
"파일"-> "옵션"-> "추가 기능"을 선택하십시오. 추가 기능 옵션의 맨 아래에서 "관리 : Excel 추가 기능"옆에있는 "이동"단추를 클릭하십시오.
결과 메뉴에서 확인란을 클릭하여 "Solver Add-in"을 활성화하고 "확인"을 클릭하십시오.
예 : 최저 iPad 배송비 계산
우리가 iPads를 운송 중이며 가능한 가장 저렴한 운송 비용으로 유통 센터를 채우려 고합니다. 우리는 트럭, 항공 회사와 상하이, 베이징 및 홍콩의 iPads를 아래의 배포 센터로 발송하기로 합의했습니다..
각 iPad를 출하하는 가격은 공장에서 유통 센터까지의 거리를 공장에 20,000 킬로미터로 나눈 값입니다. 예를 들어 상하이에서 멜버른까지는 8,024km이며, iPad 당 8,024 / 20,000 또는 $ .40입니다..
문제는 이러한 3 개 공장의 모든 iPads를 가장 저렴한 비용으로이 네 가지 목적지로 배송하는 방법입니다.?
상상할 수있는 것처럼, 이것을 이해하는 것은 공식과 도구가 없으면 매우 어려울 수 있습니다. 이 경우 총 462,000 (F12)의 iPads를 배송해야합니다. 식물은 500,250 (G12) 단위의 한정된 수용량을 가지고 있습니다.
스프레드 시트에서 작동 방식을 볼 수 있도록 셀 B10에 1을 입력하여 상하이에서 멜버른으로 iPad 1 개를 배송하려고합니다. 해당 경로의 운송 비용은 iPad 당 $ 0.40이므로 총 비용 (B17)은 $ 0.40입니다..
수는 = SUMPRODUCT (비용, 출하) 기능을 사용하여 계산되었습니다. "비용"은 범위 B3 : E5입니다..
그리고 "선적 된"범위는 B9 : E11 :
SUMPRODUCT는 "비용"에 "배송 된"시간을 곱한 값입니다 (B14). 이를 "행렬 곱셈"이라고합니다.
SUMPRODUCT가 제대로 작동하려면 비용과 출하시의 두 행렬이 동일한 크기 여야합니다. 이 제한을 극복하기 위해 추가 비용과 운송 열과 행을 0으로 지정하여 배열 크기가 동일하고 총 비용에 영향을 미치지 않도록 할 수 있습니다.
해 찾기 사용
우리가해야 할 일이 너무 복잡하지 않은 "비용"시간이 "선적 된"행렬을 곱하면된다. 그러나 우리는 거기에서도 제약 조건을 처리해야한다..
우리는 각 물류 센터가 요구하는 것을 출하해야합니다. 이 상수를 $ B $ 12 : $ E $ 12> = $ B $ 13 : $ E $ 13과 같이 해석합니다. 즉, 셀 B $ 12 : $ E $ 12에있는 합계가 각 배포 센터에서 요구하는 것보다 크거나 같아야합니다 ($ B $ 13 : $ E $ 13)..
우리는 우리가 생산하는 것보다 더 많이 출하 할 수 없습니다. 우리는 다음과 같은 제약 조건을 작성합니다 : $ F $ 9 : $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
이제 "데이터"메뉴로 가서 "해 찾기"버튼을 누르십시오. "해 찾기"버튼이 없으면 해 찾기 추가 기능을 활성화해야합니다.
앞에서 설명한 두 가지 제약 조건을 입력하고 Excel에서 계산할 숫자의 범위 인 "Shipments"범위를 선택하십시오. 또한 기본 알고리즘 "Simplex LP"를 선택하고 "목표 설정"이라고 표시된 셀 B15 ( "총 운송 비용")를 "최소화"하고자 함을 나타냅니다.
"Solve"를 누르면 Excel이 결과를 스프레드 시트에 저장합니다. 이 시나리오를 저장하여 다른 시나리오에서도 놀 수 있습니다..
컴퓨터가 솔루션을 찾을 수 없다고 말하면 논리적이지 않은 작업을 수행했습니다. 예를 들어 식물이 생산할 수있는 것보다 더 많은 iPads를 요청했을 수 있습니다..
여기서 Excel은 솔루션을 찾았다 고 말합니다. 솔루션을 유지하고 스프레드 시트로 돌아가려면 "확인"을 누르십시오..
예 : 순 현재 가치
회사는 새로운 프로젝트에 투자할지 여부를 어떻게 결정합니까? "순 현재 가치"(NPV)가 양수이면 투자 할 것입니다. 이것은 대부분의 재무 분석가가 취하는 표준 접근법입니다..
예를 들어 Codelco 광산 회사가 Andinas 구리 광산을 확장하려고한다고 가정합니다. 프로젝트 진행 여부를 결정하는 표준 접근법은 순 현재 가치를 계산하는 것입니다. NPV가 0보다 크면, 프로젝트는 2 개의 입력 (1) 시간과 (2) 자본 비용.
평범한 영어로, 자본 비용은 방금 은행에 남겨 두었을 때 그 돈이 얼마를 버는가를 의미합니다. 현금 가치를 현재 가치로 할인하는 데 자본 비용을 사용합니다. 즉, 5 년 중 $ 100은 오늘 $ 80 일 수 있습니다.
첫 해에 4500 만 달러가이 프로젝트를위한 자금으로 할당되었습니다. 회계사들은 자본 비용이 6 %.
그들은 광업을 시작하면서 회사가 생산하는 구리를 발견하고 팔 때 현금이 들어 오기 시작합니다. 분명히, 그들이 더 많이 광산을 만들고, 더 많은 돈을 벌고, 그들의 예측은 그들의 현금 흐름이 매년 9 백만 달러에이를 때까지 증가한다는 것을 보여줍니다.
13 년 후, NPV는 3,945,074 달러로 프로젝트가 수익을 올릴 것입니다. 재무 분석가에 따르면 "지불 기간"은 13 년입니다..
피벗 테이블 만들기
"피벗 테이블"은 기본적으로 보고서입니다. 피벗 테이블이라고 부르는 이유는 전체 보고서를 새로 만들 필요없이 한 보고서 유형을 다른 보고서 유형으로 쉽게 전환 할 수 있기 때문입니다. 그래서 그들은 피벗 그 자리에. 기본 개념을 가르치는 기본 예제를 보여 드리겠습니다..
예 : 영업 보고서
판매원은 경쟁이 치열합니다 (판매원이 됨). 분기말 및 연말에 수수료를 내고 수수료를 얼마나 많이 지불하는지 자연스럽게 알고 싶어합니다..
카를로스, 프레드, 줄리 등 세 명의 판매원이 모두 석유를 판매한다고 가정 해 보겠습니다. 2014 년 회계 연도 달러 당 판매액은 아래 스프레드 시트에 표시됩니다..
이러한 보고서를 생성하기 위해 피벗 테이블을 만듭니다.
"삽입 -> 피벗 테이블"을 선택하면 툴바의 왼쪽에 있습니다.
아래 그림과 같이 모든 행과 열 (영업 사원 이름 포함)을 선택하십시오.
피벗 테이블 대화 상자가 스프레드 시트의 오른쪽에 나타납니다..
피벗 테이블 대화 상자 (Quarter, Year, Sales 및 Salesperson)의 네 필드를 모두 누르면 Excel이 스프레드 시트에 보고서를 추가합니다.?
보시다시피, 네 개의 필드를 모두 선택하여 보고서에 추가했습니다. Excel의 기본 동작은 텍스트 필드별로 행을 그룹화 한 다음 나머지 행을 모두 합하는 것입니다..
여기에 우리는 2014 + 2014 + 2014 + 2014 = 24,168의 해를 제공합니다. 이것은 말도 안됩니다. 또한 이것은 1 + 2 + 3 + 4 = 10 * 3 = 3의 1/4의 합계입니다.이 정보는 필요하지 않으므로이 필드의 선택을 취소하여 피벗 테이블에서 정보를 제거합니다.
"판매 합계"(총 매출액)는 적절하지만,.
예 : Salesman 판매
"Total Sales"라고 말하는 "Sum of Sales"를 편집 할 수 있습니다. 또한 다른 셀을 포맷하는 것처럼 통화로 셀을 포맷 할 수 있습니다. 먼저 "판매 합계"를 클릭하고 "값 필드 설정"을 선택하십시오.
결과 대화 상자에서 이름을 "Total Sales"로 변경 한 다음 "Number Format"을 클릭하고이를 "Currency"로 변경합니다.
그런 다음 피벗 테이블에서 자신의 작업을 볼 수 있습니다.
예 : 세일즈맨 및 분기별로 판매
이제 각 분기마다 부분 합계를 추가해 보겠습니다. 소계를 추가하려면 "분기"필드를 마우스 왼쪽 버튼으로 클릭하고 "행"섹션으로 드래그하십시오. 아래 스크린 샷에서 결과를 볼 수 있습니다.
이제는 "분기 합계"값을 제거해 보겠습니다. 화살표를 클릭하고 '필드 제거'를 클릭하기 만하면됩니다. 스크린 샷에서 각 영업 사원의 매출을 분기별로 세분화 한 '분기'행이 추가 된 것을 볼 수 있습니다..
이러한 기술을 염두에두고 이제 자신의 데이터로 피벗 테이블을 만들 수 있습니다!
결론
마무리, Microsoft Excel의 공식 및 기능의 일부 기능을 소개하여 비즈니스, 학술 또는 기타 요구 사항에 Microsoft Excel을 적용 할 수 있습니다..
지금까지 보셨 듯이 Microsoft Excel은 많은 기능을 갖춘 막대한 제품으로 대부분의 사람들, 심지어 고급 사용자들도 모든 기능을 모를 수 있습니다. 어떤 사람들은 그것을 복잡하게 만든다고 말할지도 모릅니다. 우리는 그것이보다 포괄적이라고 느낀다..
Microsoft Excel에서 사용할 수있는 기능뿐 아니라 통계, 선형 프로그래밍, 차트 작성, 임의 숫자 사용 및 다른 아이디어를 채택 할 수있는 방법에 대해 설명해주었습니다. 학교에서 또는 직장에서 사용.
다시 돌아가 수업을 다시 듣고 싶다면 1 단원으로 시작하면됩니다.!
