Excel 수식 치트 시트: 중급 가이드

이제 Excel의 기본 사항 을 마스터하셨습니다 . 칭찬합니다! 이제 한 단계 더 발전된 기술에 뛰어들기로 결정하셨죠? 글쎄, 우리는 당신을 위해 편리한 중급 Excel 치트 시트를 구성했기 때문에 운이 좋습니다!

이 Microsoft Excel 중급 치트 시트는 수치 분석, 텍스트 처리, 복잡한 날짜 및 시간 논리에 대한 기술 수준을 높일 수 있는 광범위한 주제를 다룹니다.

Excel Intermediate Cheat Sheet를 데스크톱 참조로 사용하면 Microsoft Excel의 더 강력한 수식과 기능에 대해 자세히 알아볼 수 있습니다.

이제 계속 읽으면서 아래 치트 시트를 인쇄하고 저장하십시오!

시작하자!

Excel-formulas-cheat-sheet-intermediate-1 다운로드

목차

• 중급 수학 공식
  • 조건부 논리
• 중간 통계 공식
  • 1. 미나와 막사
  • 2. COUNTA 및 COUNTIF
• 재무 분석을 위한 Excel 수식
  • 1. 제품 기능
  • 2. QUOTIENT 및 LOG 함수
  • 투자 시나리오를 위한 기능
• 중급 Excel 날짜 및 시간 수식
  • 1. 중월 기능
  • 2. 중급 기능
  • 3. 중간 시간 함수
• 조건부 및 논리 대수를 위한 Excel 함수
  • 1. IF 함수
  • 2. AND 기능
  • 3. OR 함수
  • 4. NOT 기능
• 셀 참조
  • 1. 간접 함수
  • 2. 인덱스 기능
  • 3. 오프셋 기능
• 중간 텍스트 수식
  • 1. TEXTJOIN 기능
  • 2. REPLACE 기능
  • 3. 대체 기능
• 중간 조회 수식
• Microsoft Excel 오류 처리 공식
  • 1. IFERROR 함수
  • 2. IFNA 기능
• 수식 및 함수 단축키
• 중간 셀 서식
• 마지막 생각들

중급 수학 공식

초보자 치트 시트는 SUM 함수와 같은 가장 일반적인 초보자 수학 함수를 다룹니다. 중급 사용자는 다음과 같은 보다 복잡한 기능에 익숙해져야 합니다.

• ABS()는 숫자의 절대값을 반환합니다.

• SQRT()는 숫자의 제곱근을 반환합니다.

• RAND()는 0과 1 사이의 난수를 제공합니다.

조건부 논리

중급 사용자는 다음 함수를 사용하여 여러 조건을 기반으로 수학 계산을 수행하는 방법도 알고 있어야 합니다.

• SUMIFS(합계_범위, 기준_범위1, 기준1, [….])

• COUNTIFS(기준_범위1, 기준1, [기준_범위2, 기준2], …)

• AVERAGEIFS(기준_범위1, 기준1, [기준_범위2, 기준2], …)

함수는 제공된 기준을 평가하고 TRUE 또는 FALSE의 논리적 값을 생성합니다.

A, B, C 열에 다음과 같은 판매 데이터가 있다고 가정해 보겠습니다.

빨간색 티셔츠의 판매 합계를 계산하려면 SUMIFS 공식을 사용하세요.

=SUMIFS(C2:C6, A2:A6, "티셔츠", B2:B6, "빨간색")

빨간색 티셔츠가 있는 행 수를 계산하려면 COUNTIFS 공식을 사용합니다.

=COUNTIFS(C2:C6, A2:A6, "티셔츠", B2:B6, "빨간색")

빨간색 티셔츠의 평균 판매를 계산하려면 AVERAGEIFS 공식을 사용하십시오.

=AVERAGEIFS(C2:C6, A2:A6, "티셔츠", B2:B6, "빨간색")

중간 통계 공식

MIN 및 MAX와 같은 기본 기능 외에도 Excel에는 중급 사용자를 위한 보다 광범위한 통계 수식이 있습니다. 다음은 가장 유용한 몇 가지입니다.

• 미나

• MAXA

• 카운타

• 카운티

1. 미나와 막사

보다 일반적으로 사용되는 MIN 및 MAX 함수는 텍스트 값을 무시합니다.

대체 MINA 및 MAXA 함수는 최고값 또는 최저값을 찾을 때 텍스트와 숫자를 모두 고려합니다. 텍스트 값은 0인 것처럼 평가됩니다.

차이점은 아래 그림에 나와 있습니다. 첫 번째 행은 최소값 10을 평가하고 두 번째 행은 텍스트 값이 있으므로 0으로 평가합니다.

2. COUNTA 및 COUNTIF

COUNTA 함수는 범위에서 비어 있지 않은 셀의 수를 계산하는 데 사용됩니다.

COUNTIF 함수는 특정 조건이나 기준을 충족하는 범위 내의 셀 수를 계산하는 데 사용되는 덜 구체적입니다.

이러한 함수를 사용하여 열의 고유 값 수를 계산할 수도 있습니다. 이 비디오는 그렇게 하는 방법을 보여줍니다.

재무 분석을 위한 Excel 수식

투자 예측과 같은 재무 분석을 수행할 때 사용할 수 있는 몇 가지 공식이 있습니다.

• PRODUCT(숫자1, [숫자2…])

• QUOTIENT(분자, 분모)

• LOG(숫자, [진수])

1. 제품 기능

5년 후(셀 B3) 연이율 4%(셀 B2)로 $1,000의 초기 투자(셀 B1)의 미래 가치를 계산한다고 가정합니다.

PRODUCT 함수를 사용하여 다음 수식으로 미래 가치를 계산합니다.

=B1 * 제품(1 + B2)^B3

이 수식은 투자의 미래 가치를 반환합니다.

2. QUOTIENT 및 LOG 함수

다음 단계는 주어진 이자율에서 투자 가치가 두 배가 되는 데 몇 년이 걸리는지 계산하는 것입니다.

QUOTIENT 함수를 LOG 함수와 함께 사용하여 다음과 같이 계산합니다.

=QUOTIENT(로그(2) / 로그(1 + B2), 1)

이 수식은 샘플 데이터와 함께 17을 반환하며, 이는 연간 이자율 4%에서 투자 가치가 두 배가 되는 데 17년이 걸린다는 것을 나타냅니다.

이 그림은 Excel 워크시트의 수식을 보여줍니다 .

투자 시나리오를 위한 기능

이들은 특정 재무 시나리오에 적합한 일부 기능입니다. 그 중 하나가 정확히 필요한 것일 수 있습니다.

• NPV – NPV (순 현재 가치) 기능은 할인율을 기반으로 일련의 미래 현금 흐름을 기반으로 투자의 순 현재 가치를 계산합니다.

• ACCRINT – ACCRINT 함수는 정기적으로 이자를 지급하는 유가 증권의 발생 이자를 계산합니다. 이는 마지막 결제일부터 지정된 결제일까지 유가 증권에서 발생한 이자를 결정하는 데 유용합니다.

• INTRATE – INTRATE 함수는 완전히 투자된 보안에 대한 이자율을 계산합니다.

• PMT – PMT 함수는 부채 담보에 대한 총 지불액을 계산합니다.

• IRR – IRR 기능은 내부 수익률을 제공합니다.

• YIELD – YIELD 기능은 이자율, 액면가 및 만기를 기준으로 유가 증권의 수익률을 제공합니다.

중급 Excel 날짜 및 시간 수식

Excel의 기본 날짜 및 시간 함수에는 현재 날짜에 대한 NOW 및 TODAY 함수가 포함됩니다. 중급 사용자는 다음을 사용하여 지정된 날짜에서 구성 요소를 추출하는 방법도 알고 있어야 합니다.

• 요일(날짜)

• 월(날짜)

• YEAR(날짜)

=MONTH("2023년 4월 23일") 수식은 4번째 달에 대해 4라는 결과를 반환합니다. 마찬가지로 DAY 및 YEAR 함수는 각각 23과 2023을 반환합니다.

1. 중월 기능

중급 사용자는 때때로 날짜에 월을 더하거나 빼거나 월말을 찾는 작업을 처리합니다 .

• EDATE(시작_날짜, number_of_months)

• EOMONTH(시작일, number_of_months)

예를 들어 수식 =EDATE("2023년 4월 23일", 2)는 2개월 후 날짜를 계산합니다.

수식 =EOMONTH(2023년 4월 23일”, 2)는 2개월 후 월말을 계산합니다.

결과는 "2023년 6월 30일"이며 6월이 30일밖에 남지 않았다는 점을 고려합니다. 3개월을 지정하면 결과는 "2023년 7월 31일"이 됩니다.

2. 중급 기능

SUM 및 WEEKDAY 함수의 조합을 사용하여 날짜 범위 내의 전체 근무일 수를 계산할 수 있습니다.

WEEKDAY 함수는 주어진 날짜의 요일을 반환합니다. 두 번째 매개변수는 번호 매기기 시스템을 결정하며 기본값은 일요일부터 토요일까지입니다.

셀 범위가 B1:B40인 일련의 날짜가 있는 경우 다음 수식을 사용하여 근무일을 계산합니다.

=SUM(–(평일(B1:B40,2)>5))

3. 중간 시간 함수

YEARFRAC 함수는 두 날짜 사이의 1년 비율을 계산합니다.

TIMEVALUE 함수는 텍스트로 표현된 시간을 하루 24시간의 비율을 나타내는 십진수로 변환하는 데 사용됩니다.

결과는 Excel 일련 번호이며 여기서 1은 24시간 전체를 나타내고, 0.5는 12시간을 나타내고, 0.25는 6시간을 나타냅니다.

셀 A1에 "17:45"라는 텍스트 형식의 시간이 있고 이를 10 진수 로 변환하려고 한다고 가정합니다 . 공식은 다음과 같습니다.

=시간값(A1)

값 0.74는 하루 24시간 중 6시 이전 15분을 경과한 비율을 나타냅니다.

시간 셀을 시간 형식에서 텍스트 형식으로 변경하려면 에 가져가십시오. 더 명확하게 하기 위해 백분율로 형식을 지정할 수 있습니다.

조건부 및 논리 대수를 위한 Excel 함수

조건부 및 논리 함수는 Excel에서 의사 결정에 필수적입니다. 강력한 논리를 위해 다음 함수를 보다 기본적인 Excel 수식과 결합할 수 있습니다.

1. IF(조건, value_if_true, value_if_false):

2. AND(조건1, 조건2, ...)

3. OR(조건1, 조건2, ...)

4. NOT(조건1, 조건2, ...)

IF 함수는 조건을 평가하고 조건이 참인지 거짓인지에 따라 다른 값을 반환합니다.

AND, OR 및 NOT 함수는 어떤 조건이 참인지 확인하고 그에 따라 결정을 내릴 수 있도록 합니다.

다음은 이전에 사용한 판매 데이터를 기반으로 한 몇 가지 예입니다.

1. IF 함수

항목의 판매량이 15개 미만인 경우 "낮은 판매"라는 텍스트를 표시하고 싶다고 가정합니다. 판매량이 더 많은 경우 새 열에 "높은 판매"를 표시하려고 합니다.

이 수식을 사용하여 다른 행에 복사합니다.

=IF(C2<15, "낮은 매출", "높은 매출")

2. AND 기능

15회 이상 판매된 티셔츠에 대해 TRUE 또는 FALSE를 표시한다고 가정합니다. 이 공식을 사용하십시오.

=AND(A3=”티셔츠”, C3>=15)

3. OR 함수

여러 조건을 테스트하고 조건 중 하나라도 충족되면 TRUE를 반환하고 그렇지 않으면 FALSE를 반환한다고 가정하면 OR 함수를 사용합니다.

예를 들어 셀 A1과 B1에 테스트 점수가 있고 점수가 80 이상인지 알고 싶다면 = OR(A1>80, B1>80) 을 사용할 수 있습니다 . 점수(또는 둘 다)가 80을 초과하면 TRUE를 반환하고 둘 다 80 이하이면 FALSE를 반환합니다.

4. NOT 기능

항목이 후드티가 아닌지 확인하고 싶다고 가정합니다. 이 공식을 사용하십시오.

=NOT(A2="후드티")

셀 참조

초보자는 $A$1 또는 B2와 같은 절대 및 상대 참조 사용에 빠르게 익숙해질 것입니다. 중급 사용자는 간접 참조, 색인 및 오프셋 사용에 익숙해져야 합니다.

1. 간접(ref_text)

2. INDEX(범위, 행_번호, 열_번호)

3. OFFSET(참조_셀, 행, 열):

1. 간접 함수

INDIRECT 함수는 텍스트로 입력된 지정된 셀 참조의 값을 반환합니다. B3 셀을 참조하려면 다음 수식을 사용하십시오.

=간접("B3")

간단한 셀 참조를 사용하는 것보다 이점은 이 함수가 동적 참조를 제공한다는 것입니다. 이는 행이 삭제되는 등 스프레드시트의 구조가 변경될 때 Excel이 참조를 자동으로 업데이트함을 의미합니다.

2. 인덱스 기능

INDEX 함수는 행 및 열 번호를 기준으로 지정된 범위 내의 셀을 참조하는 데 사용됩니다.

6개 행으로 구성된 표에서 B3 셀을 참조하려면 다음 수식을 사용하십시오.

=INDEX(B1:B6, 3)

3. 오프셋 기능

OFFSET 함수는 참조 셀에서 지정된 수의 행과 열만큼 떨어져 있는 셀 또는 범위를 반환합니다.

A1에서 가로로 두 셀, 아래로 한 셀인 셀 값을 표시하려면 다음 수식을 사용합니다.

=오프셋(A1, 2, 1)

작동하는 공식은 다음과 같습니다.

중간 텍스트 수식

초보자는 문자열의 왼쪽에서 하나 이상의 문자를 추출하는 LEFT와 같은 텍스트 함수를 알아야 합니다. 고급 사용자는 다음과 같은 기능에 익숙해야 합니다.

1. TEXTJOIN(구분 기호, ignore_empty, text1, [text2, …])

2. REPLACE(old_text, start_num, num_chars, new_text)

3. SUBSTITUTE(텍스트, 이전_텍스트, 새_텍스트, [인스턴스_번호])

1. TEXTJOIN 기능

TEXTJOIN 함수는 사용자가 제공한 구분 기호로 셀을 연결합니다. 빈 셀을 무시할 위치를 지정할 수도 있습니다.

열 A에 있는 일부 항목의 쉼표로 구분된 목록을 생성하려면 다음 수식을 사용하십시오.

=TEXTJOIN(", ", 참, A2:A4)

2. REPLACE 기능

바꾸기 기능을 사용하면 대상으로 바꾸려는 문자열의 시작 위치와 길이를 지정할 수 있습니다.

문자열의 처음 두 문자를 "XX"로 바꾸려면 다음 수식을 사용하십시오.

=TEXTJOIN(A2, 1, 2, "XX")

3. 대체 기능

대체 기능은 다른 텍스트 문자열 내에서 지정된 텍스트 문자열을 새 텍스트 문자열로 바꿉니다.

"Jeans"라는 단어를 "Leggings"로 바꾸려면 다음 수식을 사용하십시오.

=SUBSTITUTE(A3, "청바지", "레깅스")

작동하는 공식은 다음과 같습니다.

중간 조회 수식

VLOOKUP 및 MATCH와 같은 일반적인 조회 기능은 초보자용 치트 시트에서 다룹니다.

중급 사용자는 CHOOSE 조회 기능에 익숙해져야 합니다.

• CHOOSE(인덱스_번호, 값1, [값2, …])

CHOOSE 함수는 지정된 인덱스 번호를 기반으로 값 목록에서 값을 반환합니다.

1, 2 또는 3으로 레이블이 지정된 티셔츠 크기의 열이 있다고 가정합니다. 동일한 행에 소형, 중형 및 대형 범주를 각각 표시하려고 합니다. 이 공식을 사용하십시오.

=CHOOSE(A1, "소형", "중형", "대형")

Microsoft Excel 오류 처리 공식

초보자 치트 시트에는 Excel로 작업할 때 표시되는 일반적인 오류 메시지가 나열되어 있습니다. 중급 사용자는 수식을 사용하여 오류를 정상적으로 처리할 수 있어야 합니다.

1. IFERROR(값, if_error)

2. IFNA(값, 값_if_na)

1. IFERROR 함수

0 으로 나누기 오류는 일반적이지만 Excel의 기본 표시는 #DIV/0! 모든 사용자에게 명확하지 않습니다.

IFERROR 함수를 사용하여 "0으로 나눌 수 없음"이 있는 표준 오차를 다음 수식으로 바꿉니다.

=IFERROR(A1/B1, "0으로 나눌 수 없습니다")

이 그림은 세 번째 행의 원시 오류와 네 번째 행을 처리하는 IFERROR 함수를 보여줍니다.

2. IFNA 기능

Excel의 IFNA 함수는 #N/A 오류를 포착하고 처리하는 데 사용됩니다.

이 치트 시트의 다른 섹션에는 CHOOSE 기능이 나와 있습니다. 인덱스 번호가 정수가 아닌 경우 이 함수는 N/A 오류를 반환합니다.

티셔츠 사이즈가 1.5로 입력되었고 사용자가 이것이 소형과 중형 사이의 범주를 표시할 것이라고 잘못 생각했다고 가정합니다. 대신 #N/A 오류 가 표시됩니다 .

보다 유용한 오류를 제공하려면 다음 공식을 사용하십시오.

=IFNA(CHOOSE(A2, "낮음", "중간", "높음"), "잘못된 인덱스")

수식 및 함수 단축키

워크시트 작업을 보다 효율적으로 수행할 수 있는 몇 가지 바로 가기 키가 있습니다.

• F2 : 활성 셀을 편집하고 셀 내용의 끝에 삽입점을 배치합니다.

• F9 : 수식에서 선택한 부분의 결과를 계산하고 표시합니다.

• Ctrl + Shift + Enter : 배열 수식을 입력합니다.

• Shift + F3 : 함수 삽입 대화 상자를 엽니다.

• Esc : 수식 입력을 취소하고 원래 셀 내용으로 되돌립니다.

이러한 Excel 바로 가기 중 일부는 모든 언어 또는 키보드 레이아웃에서 사용하지 못할 수 있습니다.

중간 셀 서식

재무 분석에서는 숫자, 날짜 및 통화가 포함된 셀의 올바른 서식이 중요합니다. 셀 서식을 지정하려면 다음 단계를 따르세요.

1. 서식을 지정할 셀을 선택합니다.

2. 선택한 셀을 마우스 오른쪽 버튼으로 클릭하고 "셀 서식"을 선택합니다.

3. 적절한 범주(예: 숫자, 통화, 날짜)를 선택하고 원하는 형식을 적용합니다.

금융에서 고려해야 할 몇 가지 일반적인 서식 유형은 다음과 같습니다.

• 통화 : $1,234.56

• 비율 : 12.34%

• 회계 : ($1,234.00)

• 날짜 : 2023년 5월 10일

마지막 생각들

이 치트 시트 에 강조 표시된 다양한 공식과 기능에 익숙해지면 점점 더 복잡해지는 작업과 프로젝트를 더 잘 처리할 수 있게 됩니다.

연습은 모든 기술을 습득하는 데 핵심이므로 일상 업무에서 이러한 공식을 실험하는 것을 두려워하지 마십시오. 그들은 당신의 전문 지식을 초급 수준에서 중급 수준으로 가져올 것입니다.

이것은 Excel의 중간 기능이 작업을 단순화하고 분석을 능률화하며 데이터에서 통찰력을 발견하는 데 도움이 되는 기능의 맛보기일 뿐입니다.

치트 시트의 기능을 결합하고 고유한 요구 사항에 맞게 조정할 때 진정한 마법이 일어납니다. 그러니 여기서 멈추지 마세요. 계속 탐색하고 실험하면 Excel이 단순한 도구가 아니라 게임 체인저라는 사실을 알게 될 것입니다!

다음 단계로 나아가고 싶습니까? Excel Advanced Formulas Cheat Sheet를 확인하십시오 .