실용적인 Excel 기술 공유: 전문가들 사이에서 가장 일반적으로 사용되는 10가지 수식

이 글은 직장에서 모두가 가장 일반적으로 사용하는 10가지 Excel 수식을 정리했습니다. 여러분의 문제를 해결하는 데 도움이 되기를 바랍니다.

이 방법을 모르면 정말 비효율적입니다. 하지만 수식과 함수가 너무 많아서 한꺼번에 익히기가 불가능하다는 것이 많은 전문가들이 겪는 딜레마입니다.

오늘은 여러분과 공유하기 위해 자주 사용되는 10가지 공식을 요약했습니다. 이 10가지 공식을 배우면 직장에서도 성공할 수 있다고 믿습니다.

공식 1: 조건부 계산

조건부 계산은 Excel 응용 프로그램에서 매우 일반적입니다. 예를 들어 통계학자 목록에 있는 여성의 수는 조건부 계산을 대표합니다.

조건부 계산에는 COUNTIF 함수를 사용해야 합니다. 함수 구조는 =COUNTIF(통계 영역, 조건)입니다. 이 예에서 첫 번째 수식은 =COUNTIF(B: B,G2), B:B는 통계 영역, G2는 조건, 수식 결과는 B열에 "여성" 데이터가 14개 있음을 나타냅니다. =COUNTIF(统计区域,条件)，在本例第一个公式=COUNTIF(B:B,G2)中，B:B就是统计区域，G2是条件，公式结果表示B列中为“女”的数据有14个。

第二参数条件可以不使用单元格引用，直接用具体内容作为条件，当条件为文本时，需要在条件两边添加英文状态的双引号，比如第二个公式=COUNTIF(B:B,"女")就是如此。 

公式2：快速标注重复数据

平时的工作中也经常会遇到标注重复值的问题，例如在一份销售明细表中，对重复的销售人员姓名进行标注。

首先使用公式=COUNTIF(A:A,A2)计算出每个姓名出现的次数，当结果大于1就表示姓名重复，进而使用IF函数得到最终的结果。

公式为：=IF(COUNTIF(A:A,A2)=1,"","重复")，结果如图所示。

判断姓名是否重复还有一种情况：第一次出现不算重复，从第二次起才算重复。

遇到这种情况，只需要修改COUNTIF函数的条件区域即可，公式为：=IF(COUNTIF($A:A2,A2)=1,"","重复")，结果如图所示。

公式3：多条件计数

如果要对多个条件进行统计，就要用到COUNTIFS函数，例如需要统计出男性中学历为本科的人数。

COUNTIFS的函数结构为=COUNTIFS(数据区域1,条件1,数据区域2,条件2,……)，最多可以有127组条件。

本例的第一个公式=COUNTIFS(B:B,G2,C:C,G3)中，共有两组条件，B列是对性别进行判断，C列是对学历进行判断。

同样，条件可以引用单元格，也可以直接使用具体内容，这与COUNTIF函数完全一致。

公式4：条件求和

除了条件计数，条件求和的应用也很广泛，例如在销售明细表中统计出电视销量的合计。

按条件求和时需要用到SUMIF函数，函数的结构为=SUMIF(条件区域,条件,求和区域)，在本例中，条件区域是B列，求和区域是C列，条件可以使用单元格也可以直接使用具体内容，要统计出电视销量的合计就有两种写法：=SUMIF(B:B,"电视",C:C)和=SUMIF(B:B,B2,C:C)。

公式5：多条件求和

有条件求和就会有多条件求和，例如要按照销售人员和商品名称两个条件进行求和时，就要用到多条件求和函数SUMIFS了。

SUMIFS函数的结构为=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……)，在本例中，求和区域是D列的销售数量，第一个条件区域是B列的销售人员，第二个条件区域是C列的商品名称，因此最终的公式为：

=SUMIFS(D:D,B:B,"沈伊杰",C:C,"壁挂空调")

두 번째 매개변수 조건은 셀 참조를 사용할 수 없지만 특정 내용을 조건으로 직접 사용할 수 있습니다. 조건이 텍스트인 경우 두 번째 수식 =와 같이 조건 양쪽에 영어 큰따옴표를 추가해야 합니다. COUNTIF(B: B,"female")그게 다입니다. 🎜🎜🎜공식 2: 반복되는 데이터를 빠르게 표시🎜🎜🎜🎜판매 세부정보 테이블 등 일상 업무에서 반복되는 값을 표시하는 문제에 자주 직면합니다. , 중복된 영업사원 이름을 표시하세요. 🎜🎜 🎜🎜먼저 =COUNTIF(A:A,A2) 공식을 사용하여 각 이름이 나타나는 횟수를 계산하고 결과가 1보다 크면 이름이 반복된다는 의미입니다. IF 함수를 사용하여 최종 결과를 얻습니다. 🎜🎜공식은 =IF(COUNTIF(A:A,A2)=1,"","반복")이고, 결과는 그림과 같습니다. 🎜🎜 🎜🎜이름이 반복되는지 확인하는 또 다른 방법이 있습니다. 첫 번째 발생은 반복으로 간주되지 않고 두 번째 발생만 반복으로 간주됩니다. 🎜🎜이 경우 COUNTIF 함수의 조건부 영역만 수정하면 됩니다. 수식은 =IF(COUNTIF($A$1:A2,A2)=1,"","Repeat"입니다. ) code> 결과는 그림과 같습니다. 🎜🎜<img border="0" style="max-width:90%" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/c45ec2b9145c73bfae5a34b18f7d3137-3.png" alt="실용적인 Excel 기술 공유: 전문가들 사이에서 가장 일반적으로 사용되는 10가지 수식" > 🎜🎜🎜<span style="font-size: 18px;">수식 3: 다중 조건 계산 🎜🎜🎜🎜여러 조건을 계산하려면 COUNTIFS 함수를 사용해야 합니다. 예를 들어 계산해야 합니다. 고등학교 교육을 받은 남성의 수. 학부생의 수. 🎜🎜<img border="0" style="max-width:90%" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/c45ec2b9145c73bfae5a34b18f7d3137-4.png" alt="실용적인 Excel 기술 공유: 전문가들 사이에서 가장 일반적으로 사용되는 10가지 수식" > 🎜🎜COUNTIFS의 함수 구조는 =COUNTIFS(데이터 영역 1, 조건 1, 데이터 영역 2, 조건 2,...)이며 최대 127개의 조건 집합이 있을 수 있습니다. 🎜🎜이 예제의 첫 번째 수식 =COUNTIFS(B:B,G2,C:C,G3)에는 두 가지 조건 세트가 있으며 B열은 성별을 판단하는 데 사용되고 C열은 학력을 판단하는 데 사용됩니다. 🎜🎜마찬가지로 조건은 셀을 참조하거나 특정 내용을 직접 사용할 수 있으며 이는 COUNTIF 함수와 완전히 동일합니다. 🎜🎜🎜<span style="font-size: 18px;">수식 4: 조건부 합산🎜🎜🎜🎜조건부 집계 외에도 TV 매출을 매출 내역 합계에 집계하는 등 조건부 합산도 널리 사용됩니다. 🎜🎜<img border="0" style="max-width:90%" style="max-width:90%" src="https://img.php.cn/upload/article/000/000/024/4227d766cdfc201c232b681ae1d6f6d5-5.png" alt="실용적인 Excel 기술 공유: 전문가들 사이에서 가장 일반적으로 사용되는 10가지 수식" > 🎜🎜조건별로 합산할 때는 SUMIF 함수를 사용해야 합니다. 함수의 구조는 =SUMIF(조건 영역, 조건, 합산 영역)입니다. 이 예에서는 조건 영역이 B열이고 합산 영역이 C열입니다. 조건은 셀을 직접 사용하여 특정 콘텐츠를 사용할 수도 있습니다. 총 TV 판매량을 계산하려면 =SUMIF(B:B,"TV",C:C) 및 <code>=SUMIF를 작성하는 방법이 있습니다. (B:B,B2,C:C). 🎜🎜🎜공식 5: 다중 조건 합 🎜🎜🎜🎜조건부 합이 있는 경우 다중 조건 합이 있습니다. 예를 들어 두 조건을 기반으로 합니다. 영업사원 및 제품명의 조건 합산을 수행할 때 다중 조건 합산 기능 SUMIFS를 사용합니다. 🎜🎜 🎜🎜SUMIFS 함수의 구조는 =SUMIFS(합계 영역, 조건 영역 1, 조건 1, 조건 영역 2, 조건 2,...)입니다. 이 예에서 합산 ​​영역은 D열의 판매 수량입니다. 첫 번째 조건 영역은 B열의 영업사원이고 두 번째 조건 영역은 C열의 제품 이름이므로 최종 수식은 🎜🎜=SUMIFS(D:D,B:B,"Shen Yijie" ,C:C ,"벽걸이형 에어컨")🎜

한 가지 주의할 점은 SUMIFS의 합산 영역 위치가 SUMIF의 위치와 다르다는 점입니다. SUMIFS의 합산 영역은 첫 번째 매개변수에 있는 반면, SUMIF의 합산 영역은 세 번째 매개변수에 있습니다. . 혼란스러워하지 마세요!

공식 6: 주민등록번호를 기준으로 생년월일을 계산하세요

주민등록번호로 생년월일을 구하려면 이 문제가 인사관리직에 근무하는 분들에게는 익숙할 문제이며, 그 공식은

=TEXT(MID(A2,7,8),"0-00-00")은 그림에 표시된 대로 필요한 결과를 얻습니다.

이 공식의 원리를 이해하려면 , 먼저 ID 카드를 알아야 합니다. 현재 사용되는 ID 카드는 기본적으로 18자리이며, 7번째 자리부터 시작하는 8자리는 생년월일을 나타냅니다.

이 수식에는 두 가지 함수가 포함됩니다. 먼저 MID 함수에는 세 가지 매개변수가 있습니다. 형식은 =MID(추출할 단어, 가져올 단어 수)입니다.

MID(A2,7,8)은 셀 A2의 7번째 숫자부터 8자리를 가로채는 것을 의미합니다. 효과는 그림과 같습니다.

생년월일을 추출한 후에는 효과가 없습니다. 이때 필요한 것은 마술사 TEXT 함수입니다. TEXT 함수에는 매개변수가 2개만 있고 형식은 =TEXT(처리할 내용, "어떤 형식으로 표시할지")입니다. 는 MID 함수의 일부이고 표시 형식은 "0-00-00"입니다. 물론 "0年00月00日" 형식으로 표시할 수 있으므로 수식을 =TEXT(MID)로 변경해야 합니다. (A2,7,8),"0年00月00日" " Y")

여기에는 숨겨진 Excel 함수 DATEDIF가 사용됩니다. 이 함수에는 세 개의 매개 변수가 필요하며 기본 구조는 =DATEDIF(시작 날짜)입니다. , 마감일, 계산 방법). 이 예의 시작 날짜는 B2를 첫 번째 매개 변수로 사용하고 마감일은 오늘이며 두 번째 매개 변수로 TODAY() 함수를 사용하여 "Y"를 사용하여 연도별로 계산합니다. 세 번째 매개변수 . ID 번호에서 직접 나이를 계산해야 하는 경우 공식은 다음과 같이 작성할 수 있습니다.

=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(), "Y" )

공식 8: 간격에 따라 다른 결과가 나옵니다

이러한 유형의 문제는 주로 성과 평가에서 나타납니다. 예를 들어 회사에서 직원의 성과 평가를 수행하면 이를 결정해야 합니다. 평가 결과에 따른 보상 수준은 다음과 같습니다. 50점 미만은 E, 50~65점(포함)은 D, 65~75점(포함)은 C, 점수는 다음과 같습니다. 75~90점(포함)은 B, 90점 이상은 A입니다.

공식 =LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"})를 사용하여 보상을 받을 수 있습니다. 각 직원 레벨의 결과는 그림과 같습니다.

이 공식의 원리를 설명하기는 어렵습니다. LOOKUP 함수와 관련된 이전 튜토리얼을 참조할 수 있습니다. 사실 이런 종류의 문제를 해결하려면 루틴을 기억하는 것만으로도 충분합니다. LOOKUP은 간격별로 해당 결과를 반환합니다: =LOOKUP(점수, {하한 값 목록}, {보상 수준 목록}), 그리고 하한값 사이의 사용 포인트는 세미콜론으로 구분되며, 보상 수준도 세미콜론으로 구분됩니다.

점수 하한과 보상 수준 간의 대응 관계를 표에 입력할 수도 있습니다. 수식은 =LOOKUP(E2,$I$2:$J$6)으로 수정할 수 있으며 결과는 다음과 같습니다. 그림에서.

공식 9: 단일 조건 매칭 데이터

직장을 장악하고 싶은데 매칭 방법을 모르면 어떻게 해야 할까요? VLOOKUP 없이 단일 조건 일치를 어떻게 수행할 수 있나요?

VLOOKUP 함수의 기본 구조는 =VLOOKUP(찾을 항목, 찾을 위치, 찾을 열, 찾는 방법)입니다. 예를 들어 이름으로 최고 학력을 찾으려면 =VLOOKUP(G2,B:E,4,0) 공식을 사용하여 필요한 결과를 얻을 수 있습니다.

이 함수를 사용할 때 알아야 할 두 가지 핵심 사항이 있습니다. ①찾고자 하는 콘텐츠는 검색 범위의 첫 번째 열에 있어야 합니다. 예를 들어 이름으로 검색할 경우 검색 범위는 A열이 아닌 B열부터 시작됩니다.

②열 번호는 표의 열이 아닌 검색 범위의 열을 의미합니다. 예를 들어, 최고 학력을 찾으려면 검색 범위의 5번 열이 아닌 4번째 열에 있습니다. 탁자.

공식 10: 여러 조건으로 데이터 일치

여러 조건으로 데이터를 일치시키는 방법을 배우면 정말 무적입니다!

그림과 같이 이름과 제품 이름이라는 두 가지 조건으로 판매량을 일치시키는 예를 들어보세요.

공식은 =LOOKUP(1,0/(($A:$A=E2)*($B:$B=F2)),$C:$C)

다중 조건 일치를 위해 LOOKUP 함수를 사용하는 루틴은 다음과 같습니다. LOOKUP(1,0 /((검색 범위 1 = 검색 값 1) * (검색 범위 2 = 검색 값 2) *... * (검색 범위 n = 검색 값 n)), 결과 범위) 주의사항 여러 검색 조건 간의 관계는 곱셈 관계이므로 분모 0/과 동일한 괄호 세트에 배치되어야 합니다.

좋아요, 가장 일반적으로 사용되는 10가지 공식을 여기에서 공유합니다. 한번 사용하면 직장을 장악할 수 있습니다!

관련 학습 권장 사항: excel 튜토리얼

위 내용은 실용적인 Excel 기술 공유: 전문가들 사이에서 가장 일반적으로 사용되는 10가지 수식의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!