실용 엑셀 기술 공유: 다양한 기준으로 1인자를 찾는 방법

순위는 간단하지만, 프로젝트 카테고리가 여러 개이고 성과가 동일한 경우 각 공유에서 1위를 차지한 사람을 빠르게 찾는 방법은 무엇인가요? 원하는 숫자 1을 찾으려면 여러 조건의 일치가 필요합니다. 여기에는 두 가지 솔루션이 제공되지만 어느 것도 완벽하지 않습니다. 이를 개선할 수 있습니까?

연례 표창회가 곧 시작됩니다. 올해는 어떤 동료가 영업 챔피언이 될까요? 함께 찾아보자!

한 회사의 전자상거래 플랫폼에 있는 각종 가전제품의 판매 데이터는 그림과 같습니다.

데이터에는 판매 주문 번호, 제품명, 담당자 이름 및 판매량만 있습니다. 아래 챔피언과 같은 형식으로 제품 유형별 판매량을 집계해야 합니다.

이 질문을 보니 어떤 방법이 생각나는지 궁금하네요? 피벗 테이블, MAX 함수 또는 VLOOKUP...

베테랑은 모든 사람에게 두 가지 방법을 권장합니다. 첫 번째는 보조 열 + 수식이고 두 번째는 피벗 테이블 + 수식입니다.

방법 1: 보조 컬럼 + 수식

1단계: 보조 컬럼 추가

먼저 각 개인의 매출을 제품명별로 요약합니다. 조건에 따라 합계를 계산합니다. 여기서는 SUMIFS 함수가 통계에 사용됩니다. 피벗 테이블을 사용해도 동일한 결과를 얻을 수 있지만 피벗 테이블에서는 원하는 최종 효과를 한 번에 얻을 수 없으므로 보조 열을 사용하는 것이 더 편리합니다.

공식:

=SUMIFS(D:D,C:C,C2,B:B,B2)=SUMIFS(D:D,C:C,C2,B:B,B2)

公式格式：=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2……)

SUMIFS是一个多条件求和函数，第一参数是要求和的数据所在的列，后面的参数两个一组，构成一组条件。在这个例子中，第一组条件是业务人员，因此条件区域1就是C列，条件1是C2；第二组条件是产品名称，条件区域2就是B列，条件2是B2。

有了辅助列，下一步就可以找到每个品类中最高的销售额是多少了。这里需要注意的是，统计结果表里销售冠军姓名在前销售额在后。实际统计时并非必须按这样的先后顺序统计，哪个方便我们就先统计哪个。

第2步：统计最高销售额

通常一说最大值，首先想到的就是MAX函数。这个函数的用法和SUM很像，只需要给出一组数或者一个数据区域，就能得到这一组数中最大的值。

在今天这个例子中，因为我们要得到的是同一个品类中的最大值，也就是按条件统计最大值，所以无法直接用MAX函数得到结果，

这类按条件统计最大值的有固定的套路公式：

=MAX(数据区域*（条件区域1=条件1）*（条件区域2=条件2）……)

本例只有一个条件，就是产品名称，因此公式为：=MAX($E:$E0*($B:$B0=G2))

 使用这个公式套路需要注意三个地方：

（1）范围要准确，不建议选择整列作为计算区域；

（2）公式涉及数组运算，在输入公式后需要按Ctrl+Shift+Enter键，按键后会自动在公式中添加一对大括号；

（3）因为公式要下拉，为了避免计算区域发生改变，所以涉及到的范围需要使用绝对引用。

这个公式具体原理涉及到逻辑值和数组的计算原理，以后我们会专门进行讲解。

到这一步，再找出每类产品下最高销售额对应的业务人员就完成了全部的统计。

第3步：找出冠军人员

根据销售额查人员，这实际上就是一个查找引用，使用VLOOKUP或者INDEX等引用函数都可以完成。

接近成功，现在要削苹果了。削苹果的特点就是细、准。

第一个细节：数据源中的累计销售额位于业务人员的右侧。

如果用VLOOKUP，我们就得使用反向查找的套路，公式相对还是比较复杂。如果用INDEX与MATCH组合倒是可以，公式也不难：

=INDEX($C:$C0,MATCH(I2,$E:$E0,0))

수식 형식: =SUMIFS(합계 영역, 조건 영역 1, 조건 1, 조건 영역 2, 조건 2...)

SUMIFS는 다중 조건 합산 함수입니다. 첫 번째 매개변수는 합산할 데이터가 있는 열이고, 다음 매개변수는 쌍으로 그룹화되어 세트를 형성합니다. 조건의. 이 예에서 첫 번째 조건 집합은 비즈니스 직원이므로 조건 영역 1은 C열이고, 조건 1은 C2입니다. 두 번째 조건 집합은 제품 이름이고, 조건 영역 2는 B열이고, 조건 2는 B2입니다. 보조 컬럼을 통해 다음 단계는 각 카테고리에서 가장 높은 판매량이 무엇인지 알아내는 것입니다. 여기서 주목해야 할 점은 통계 결과표에서 판매 챔피언의 이름이 먼저 나오고 판매량이 마지막에 나온다는 점입니다. 실제 통계에서는 이 순서대로 계산할 필요는 없습니다. 어느 쪽이든 편리한 것을 먼저 계산하겠습니다.

🎜🎜🎜2단계: 가장 높은 매출 계산🎜🎜🎜🎜보통 최대값을 이야기할 때 가장 먼저 떠오르는 것은 MAX 기능입니다. 이 함수의 사용법은 SUM과 매우 유사합니다. 이 숫자 집합에서 최대값을 얻으려면 숫자 집합이나 데이터 영역만 제공하면 됩니다. 🎜🎜오늘의 예에서 구하고 싶은 것은 같은 카테고리의 최대값, 즉 조건에 따른 최대값을 계산하기 때문에 MAX 함수를 직접 사용하여 결과를 구할 수는 없습니다. 🎜🎜이런 종류의 계산은 불가능합니다. 조건에 따른 최대값에는 다음이 포함됩니다. 고정 루틴 공식: 🎜🎜=MAX(데이터 영역*(조건 영역 1=조건 1)*(조건 영역 2=조건 2)...)🎜🎜이것은 예에는 제품 이름이라는 조건이 하나만 있으므로 수식은 다음과 같습니다. =MAX($E$2:$E$750*($B$2:$B$750=G2))🎜🎜🎜🎜 세 가지에 주목해야 합니다 이 수식을 사용할 때의 사항: 🎜🎜 (1) 범위는 정확해야 하며 전체 열을 계산 영역으로 선택하는 것은 권장되지 않습니다. 🎜🎜(2) 수식에 배열 연산이 포함됩니다. Ctrl+Shift+Enter 키를 누르면 수식에 중괄호 한 쌍이 자동으로 추가됩니다. 🎜🎜(3 ) 계산 영역이 변경되는 것을 방지하려면 수식을 아래로 내려야 하기 때문입니다. , 관련 범위에 대해 절대 참조를 사용해야 합니다. 🎜🎜이 공식의 구체적인 원리는 논리값과 배열의 계산 원리와 관련이 있는데, 이에 대해서는 앞으로 구체적으로 설명하겠습니다. 🎜🎜이 단계에서는 제품 유형별 최고 매출에 해당하는 사업 인력을 찾아 모든 통계를 완성합니다. 🎜🎜🎜🎜3단계: 챔피언 인력 찾기 🎜🎜🎜🎜매출을 기준으로 인원을 확인하는 작업으로, VLOOKUP이나 INDEX 등의 참고 기능을 활용하여 완성할 수 있습니다. 🎜🎜거의 성공했습니다. 이제 사과 껍질을 벗길 차례입니다. 사과 껍질의 특징은 얇음과 정확성입니다. 🎜🎜🎜첫 번째 세부사항: 데이터 소스의 누적 매출은 사업자 오른쪽에 있습니다. 🎜🎜🎜VLOOKUP을 사용하면 역검색 루틴을 사용해야 하는데, 공식이 비교적 복잡합니다. INDEX와 MATCH를 결합하는 것이 가능하며 공식은 어렵지 않습니다: 🎜🎜=INDEX($C$2:$C$750,MATCH(I2,$E$2:$E$750,0))🎜 🎜🎜🎜🎜🎜두 번째 디테일: 최대 판매량은 동일할 수 있습니다. 🎜🎜

이 두 기능의 결합은 고전적인 파트너십이라고 할 수 있습니다. 그러나 여전히 세부 사항이 있습니다. 두 제품 범주의 최대 판매량이 동일할 가능성을 배제할 수 없습니다. 서로 다른 카테고리의 최대 판매량이 동일한 경우 발생할 수 있는 검색 오류를 방지하려면 제품 이름과 판매량의 두 가지 조건을 기준으로 일치해야 합니다. 수식은

=INDEX($C$2:$C)입니다. $750,MATCH(G2&I2,$B$2:$B$750&$E$2:$E$750,0))=INDEX($C:$C0,MATCH(G2&I2,$B:$B0&$E:$E0,0))

多条件匹配常用套路之一就是用连接符号&把多个条件串在一起组成一个新的条件来查询，当然查询区域也需要用&串在一起。

当然，像这种多条件查找，并且不愿意利用Vlookup反相查找的话，也可以用LOOKUP函数来完成：

=LOOKUP(1,0/(($E:$E0=I2)*($B:$B0=G2)),$C:$C0)

多条件匹配常用套路之二就是把多个条件各自用等号＝与查找区域建立起表达式，然后把表达式进行相乘。

公式的套路是：=LOOKUP(1,0/(条件区域=条件),目标区域)，如果是多个条件的话，可以直接将套路升级为：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*(条件区域3=条件3)……,目标区域)

方法二：透视表+公式

第1步：统计业绩并排名

将产品名称和业务人员拖入行区域，销售额拖两次到值区域，然后按照部落窝教育去年的教程《嘿，鼠标拖两下一次搞定业绩统计和排名！》设置销售额2的值显示方式为“降序排列”，基本字段为“业务人员”获得按产品分类的销售业绩统计和排名。

第2步，整理透视表

单击透视表，点击“设计”选项卡“布局”选项组“报表布局”下拉菜单中的“以表格形式显示”和“重复所有项目标签”命令。接着在透视表上右击，选择“分类汇总“业务人员””，取消表格中的分类汇总项。表格变成下方模样：

第３步，输入公式获取冠军姓名和业绩

在G2单元格中输入公式：

=INDEX(L:L0,MATCH($G2&1,$K:$K0&$N:$N0,0))

다중 조건 일치를 위한 일반적인 루틴 중 하나는 연결 기호 &를 문자열에 사용하는 것입니다. 여러 조건을 함께 쿼리하려면 새 조건을 형성해야 합니다. 물론 쿼리 영역도 &로 연결해야 합니다.

물론이죠. like 이런 종류의 다중 조건 검색이며 Vlookup을 사용하여 검색을 되돌리고 싶지 않은 경우 LOOKUP 기능을 사용하여 완료할 수도 있습니다.

=LOOKUP(1,0/(($E$2 :$E$750=I2)*( $B$2:$B$750=G2)),$C$2:$C$750)

다중 조건 일치의 두 번째 일반적인 루틴은 등호를 사용하는 것입니다. = 여러 조건의 경우 검색 영역으로 표현식을 만든 다음 표현식을 곱합니다.

수식 루틴 It 조건은 =LOOKUP(1,0/(conditionarea=condition), targetarea)입니다. 조건이 여러 개인 경우 루틴을 =LOOKUP(1,0)으로 직접 업그레이드할 수 있습니다. /((조건부 1=조건 1)*(조건부 2=조건 2)*(조건부 3=조건 3)..., 대상 영역)

방법 2: 피벗 table +Formula

1단계: 통계 및 순위

제품 이름 및 업체 드래그 사람을 행 영역으로 끌어서 판매량을 값 영역으로 두 번 드래그한 다음 "야, 성과 통계를 얻으려면 마우스를 두 번 드래그하고 순위를 한번에!" , 기본필드는 "사업인사"로 제품 카테고리별 판매 실적 통계 및 순위를 얻을 수 있습니다

🎜🎜단계 2, 관점 테이블 구성🎜🎜피벗 테이블을 클릭하고 "레이아웃"의 "보고서 레이아웃" 드롭다운 메뉴에서 "테이블로 표시" 및 "모든 항목 레이블 반복" 명령을 클릭합니다. "디자인" 탭의 " 옵션 그룹을 선택합니다. 그런 다음 피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 "하위 분류 및 요약 "사업 인사""를 선택하고 테이블의 하위 분류 및 요약 항목을 취소합니다. 테이블은 다음과 같습니다. 🎜 🎜🎜🎜3단계, 챔피언의 이름과 성적을 가져오는 수식을 입력하세요🎜🎜 셀 G2의 수식: 🎜🎜=INDEX(L$2:L$200,MATCH( $G2&1,$K$2:$K$200&$N$2:$N$200,0))🎜🎜이후 입력을 끝내려면 Ctrl+Shift+Enter를 누르세요. 🎜🎜🎜🎜🎜 그런 다음 마우스 오른쪽 버튼을 클릭하고 수식을 드롭다운하세요. 🎜🎜🎜🎜🎜 오늘 튜토리얼에서는 SUMIFS, MAX, INDEX, MATCH, LOOKUP과 같은 여러 기능을 배웠고, 비슷한 문제가 발생하면 직접 사용할 수 있는 두 가지 다중 조건 매칭 루틴도 배웠습니다. 🎜🎜그러나 오늘의 해결책은 불완전합니다. 우리는 "사과 오줌"을 묻고 튜토리얼의 세부 사항에 주의를 기울였음에도 불구하고 여전히 매우 중요한 세부 사항을 놓쳤습니다. 유사한 제품의 최고 판매량은 동일할 수 있습니다. 🎜🎜관련 학습 권장 사항: 🎜excel 튜토리얼🎜🎜

위 내용은 실용 엑셀 기술 공유: 다양한 기준으로 1인자를 찾는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!