17 고급 Excel 공식 – 모든 전문가가 알아야 할 사항

게시 됨: 2019-07-28

Excel에서 수동으로 작업을 수행하기 위해 손을 자주 구부리고 시간을 낭비하는 자신을 발견하면 올바른 방법으로 Excel을 사용하는 방법을 안다면 Excel이 얼마나 강력한지 놓치고 있는 것입니다. Microsoft Excel은 프로세스에 많은 수동 개입 없이도 셀에 있는 많은 수의 데이터에 대해 효율적으로 작업할 수 있는 공식으로 유명합니다.

길고 지루한 수동 작업을 몇 초 만에 변환하는 놀라운 능력을 가진 17개의 고급 Excel 공식을 편집했습니다. 이것은 모든 전문가가 자신의 소중한 시간을 절약하거나 중요한 순간에 상사에게 깊은 인상을 남기기 위해 손끝에서 가지고 있어야 하는 공식입니다.

목록을 살펴보고 가장 좋아하는 것을 알려주십시오!

학습자는 평균 58%의 급여 인상을 받으며 최고는 최대 400%입니다.

1. 인덱스 매치

공식 = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

조회 작업을 수행하는 데 몇 가지 단점이 있는 Excel의 VLOOKUP 또는 HLOOKUP 수식에 대한 훌륭한 대안입니다. INDEX MATCH는 2개의 개별 함수의 조합 공식입니다.

INDEX: 이 수식은 열과 행 번호를 기반으로 테이블의 셀 값을 반환합니다.

MATCH: 이 수식은 행 또는 열에서 셀의 위치를 반환합니다.

INDEX 및 MATCH 공식 결합의 예는 다음과 같습니다. 이름을 기반으로 한 사람의 키를 조회하고 반환할 때 이 공식을 사용하여 INDEX MATCH 공식을 사용하여 두 변수(이 경우 이름 및 키)를 모두 변경할 수 있습니다. .

원천

단계별로 분해해 보겠습니다.

INDEX와 MATCH를 결합하는 방법

유형 색인
인덱스할 영역 선택
F4로 영역 잠그기
데이터를 찾고자 하는 행 찾기
일련의 정보와 함께 MATCH를 입력하고 F4로 영역을 잠급니다.
정확히 일치하려면 0을 입력하고 대괄호를 닫습니다.
엔터를 치다

흥미로운 데이터 과학 프로젝트 아이디어

이제 시트에 완전히 동적이고 기능적인 열과 행 집합이 생겼으며 여기에서 올바른 데이터에 대한 셀과 행의 모든 결합이 자동으로 수행됩니다.

2. 평균

숫자 범위의 평균을 찾기 위해 평균 공식을 수행하려면 다음 단계를 따라야 합니다.

형식으로 계산할 값, 셀 또는 셀 크기를 입력합니다.

공식은 =AVERAGE(숫자1, 숫자 2 등)로 시작해야 합니다.

시트에 있는 셀 범위의 평균을 수행하려는 경우 따라야 할 단계는 다음과 같습니다.

위에서 했던 것처럼 형식으로 계산하려는 값, 셀 또는 셀의 크기를 입력합니다.
공식은 =AVERAGE(시작 값: 끝 값)와 같습니다.

셀 영역에 대한 값을 입력하는 방법이 궁금한 경우 시트에서 마우스로 영역을 선택하고 F4 키를 눌러 잠글 수 있습니다. 이렇게 하면 Excel에서 그룹의 항목 수로 그림의 합과 나누기를 수행하지 않고 나머지 작업을 수행합니다.

3. 수미프

엑셀에서 이 수식은 SUMIF(범위, 기준, [합계 범위])로 표시됩니다. 이렇게 하면 사용자가 설정한 요구 사항을 충족하는 원하는 셀 범위 내 값의 합계가 생성됩니다. 예를 들어 =SUMIF(C3: C12, ">70,000)은 값이 70,000보다 큰 셀에서만 C3과 C12 사이의 값 합계를 반환합니다.

재정, 급여 또는 비용 계산의 경우 사용자가 설정한 조건에 따라 결정되는 특정 직원과 연결된 리드의 값이 필요합니다. 이 작업을 수동으로 수행하면 시간이 많이 걸리고 작업 속도가 느려집니다.

위 조건에 대한 공식은 =SUMIF(범위, 기준, [sum_range])일 수 있으며, 여기서 범위는 값을 선택해야 하는 시트의 범위로 정의할 수 있습니다.

[sum_range]는 입력한 첫 번째 범위 외에 추가하려는 추가 또는 선택적 범위로 정의됩니다.

다음은 예입니다.

원천

4. SUM과 결합된 오프셋

단독으로는 OFFSET 함수가 편리하지 않을 수 있지만, 다른 서비스와 함께 사용하면 다양한 수의 셀을 합산할 수 있는 동적 역할, 즉 일반 SUM 공식을 생성하려는 경우 더 빠른 결과를 제공하는 복잡한 공식을 얻을 수 있습니다. static은 OFFSET 기능과 결합해야 합니다.

따라서 변수 셀의 값 합계를 찾으려면 수식은 다음과 같아야 합니다.

=SUM(B4:오프셋(B4,0,E2-1))

원천

여기서 SUM 함수의 끝 참조는 OFFSET 함수로 대체됩니다. B4에서 시작하는 SUM 공식은 변수로 끝나고 B4에서 시작하여 E2("3")의 값에서 1을 뺀 값으로 계속되는 OFFSET 공식입니다. 이렇게 하면 메서드가 두 개의 셀을 이동하고 3년 간의 데이터를 합산하는 데 도움이 됩니다. 위의 참조에서 셀 F7에 셀 B4의 합인 D4 = 15가 포함되어 있음을 알 수 있습니다.

5. 만약 그리고

이 공식은 데이터 힙을 샅샅이 뒤지기 위해 특정 조건을 생성해야 하는 상황에서 유용합니다. IF 문은 이미 존재하는 트랙에서 이러한 조건을 기반으로 새 필드를 생성하기 위해 고급 Excel IF 기능을 사용하는 데 도움이 됩니다.

예를 들어 급여가 50K 이상이고 직원 ID가 3 이상인 직원을 표시하려는 경우 공식은 다음과 같습니다.

IF(그리고(E4>3,F4>50000)1,0)

원천

위의 데이터에는 실제 사례가 없으므로 수식은 값 0을 반환합니다.

6. 연결

데이터시트에 많은 텍스트 문자열이 있고 데이터를 한 줄에 표시하려는 경우 이 공식이 적합합니다. 예를 들어 단일 열에 직원 ID와 직원 이름을 나타내려면 다음과 같은 방법을 사용해야 합니다.

=연결(B3, C3)

원천

7. 오후

이 기능은 특정 이자율, 원금 및 대출 기간이 주어지면 대출에 대한 향후 지불금을 파악하는 데 도움이 됩니다. 시작해야 할 사항은 다음과 같습니다.

대출 기간
대출의 시작 원금(돈)
미래가치
대출의 종류

이제 원금에 대한 월별 지불액을 찾으려면 공식은 다음과 같습니다.

= PMT(비율, 당, PV, [fv], [유형])

예를 들어 이것을 이해합시다.

원천

=PMT(C2/12.B2.A2)

그리고 가장 좋은 점은 PMT 셀의 오른쪽 하단 모서리를 필드 위로 드래그하여 모든 필드의 월별 지불 금액을 자동으로 계산할 수 있다는 것입니다!

인기있는 데이터 과학 직업 유형

8. 트림

이것은 필드에서 불필요한 공간을 정리하는 Excel에서 가장 많이 사용되는 공식 중 하나입니다. 예를 들면: 이름의 시작 부분에서 공백을 제거해야 하는 경우 TRIM 함수를 사용하여 제거할 수 있습니다.

=트림(C6)

원천

이렇게 하면 앞이나 끝에 추가 공백 없이 찬단 케일의 값을 반환합니다.

9. 렌

이것은 텍스트 문자열의 문자 수를 알려주는 함수입니다. 이를 사용하는 가장 흥미로운 방법 중 하나는 예를 들어 기부 열의 자릿수를 계산하여 $1,000 이상을 기부한 기부자를 강조 표시하려는 경우 공식은 다음과 같습니다.

=렌(B2)

원천

기부 열의 모든 셀을 강조 표시하려면 다음을 수행해야 합니다.

메뉴에서 홈 탭을 선택합니다.
조건부 서식(도구 모음에서)을 클릭합니다.
수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.

원천

여기에서 조건을 ">3"으로 설정하면 $1,000를 초과하는 모든 항목에 고유한 형식이 지정되며 형식 옵션을 클릭하여 선택할 수 있습니다.

10. 선택

재무 모델링에서 시나리오 분석을 위한 훌륭한 기능입니다. 특정 수의 옵션 중에서 선택하고 선택한 "선택 사항"을 반환할 수 있습니다. 예를 들어 가정에 따라 내년 매출 증가에 대해 세 가지 다른 값이 있는 경우 CHOOSE 함수를 사용하여 요구 사항에 따라 금액을 반환할 수 있습니다.

공식은 다음과 같습니다.

=선택(C7,D3,D4,D5)

원천

11. 셀, 왼쪽, 중간, 오른쪽

위의 모든 기능은 여러 가지 방법으로 결합하여 고급 공식을 얻을 수 있습니다.

CELL 함수는 셀 내용에 대한 다양한 유형의 정보를 반환하는 데 사용됩니다.
LEFT 서비스는 셀의 시작 부분부터 텍스트를 바꾸는 데 사용됩니다.
MID 함수는 셀의 시작점에서 텍스트를 반환할 수 있습니다.
RIGHT 함수는 셀 끝의 텍스트만 반환합니다.

원천

12. XNPV 및 XIRR

투자 은행, 주식 연구 또는 현금 흐름 할인이 필요한 기업 금융의 기타 영역을 접하는 모든 분석가의 경우 이러한 공식을 사용하면 많은 시간과 불평을 절약할 수 있습니다!

데이터 과학 인터뷰 질문 및 답변

예를 들어, 지정된 할인율과 불규칙한 간격으로 발생하는 현금 흐름을 사용하여 투자에 대한 현재 순 가치(NPV)를 계산하려면 다음 함수를 사용하십시오.

=XNPV(할인율, 현금흐름, 날짜)

원천

반면에 XIRR 함수는 다음과 같이 불규칙한 간격으로 발생하는 일련의 현금 흐름에 대한 내부 수익률(유출 = 유입)을 알려줍니다.

13. 카운타 ( )

분석가는 종종 값(숫자, 오류, 텍스트, 논리 값)이 있는 셀과 비어 있는 셀의 수를 찾기 위해 발버둥치는 모습을 볼 수 있습니다. COUNTA( ) 함수는 선택한 범위에서 비어 있지 않은 셀의 이름을 찾는 데 도움이 될 수 있습니다. 예를 들어 A1-A10 열이 있는 데이터 시트의 값을 계산하는 공식은 다음과 같습니다.

=COUNTA(A1: A10)