실용적인 Excel 기술 공유: 다단계 순위 통계표를 만드는 방법을 알아보세요.

이전 글 "실용적인 엑셀 기술 공유: 표에 배경 이미지를 추가하는 두 가지 방법"에서는 표 배경 이미지를 사용하는 방법을 배웠습니다. 오늘은 통계표에 대해 이야기하고 월드컵을 보면서 다요소 순위 통계표 작성 방법을 배워보겠습니다.

4년마다 열리는 월드컵의 치열한 전쟁이 본격화되고 있습니다. 모든 팬은 자신이 좋아하는 팀을 응원하고 이 팀이 소속된 그룹의 성적에 더욱 관심을 갖습니다. 1라운드가 끝나면 조별 예선은 최고조에 달하며, 같은 조 소속 팀이 득점하는 골 하나하나가 전체 결과에 영향을 미칠 수 있다. 미디어 정보가 많이 발전한 지금, 최신 뉴스와 발전 전망은 어디에서나 볼 수 있지만, 데이터를 입력한 후 그룹 순위 결과를 계산하고 예측할 수 있는 스프레드시트가 있으면 매우 편리하다는 점은 의심할 여지가 없습니다. 오늘은 이런 게임 성능 통계표입니다.

표의 최종 효과는 다음과 같습니다.

기능: 노란색 영역에 대회 결과를 입력하면 각종 지표와 최종 순위가 자동으로 계산됩니다.

값:

1전투 데이터의 실시간 통계

2예측 순위

물론, 더 중요한 의미는 계산 문제에 직면했다면 이 테이블의 디자인 아이디어를 통해 많은 세부적인 지식 포인트를 배운다는 것입니다. 귀하의 작업에 대한 종합 순위 모든 질문은 참고용으로 사용될 수 있습니다. 구체적인 제작과정을 살펴보겠습니다.

각 그룹의 구조는 완전히 동일하므로 설명을 위해 그룹 A의 데이터만 예로 사용합니다. 전체 테이블의 제작은 전체 아이디어 결정, 레이아웃 디자인 및 공식 디자인의 세 단계로 나뉩니다.

1. 전체적인 아이디어 결정

테이블을 디자인하기 전에 관련 규칙과 요구 사항을 정리하고, 어떤 기본 데이터가 제공될 수 있는지, 어떤 최종 결과가 필요한지 명확히 한 후 테이블을 디자인해야 합니다. 중간 링크.

이 경기력 통계표에서 기본 데이터는 각 경기 후의 두 숫자(양 팀이 득점한 골 수)입니다. 필요한 최종 결과는 그룹 순위입니다.

각 게임의 결과에 따라 순위를 얻는 방법은 순위 규칙도 이해해야 합니다.

위 그림은 공식 설명으로, 비교적 복잡한 계산 규칙을 ​​요약할 수 있습니다.

①각 게임의 필요에 따라 각 팀의 결과를 판단합니다. 승리는 3점, 무승부는 0점으로 계산됩니다.

②득점이 동일할 경우 골득실차에 따라 순위가 결정됩니다(골득실차는 득점수 - 실점골수). 득점한 골 수.

IV 득점 수가 여전히 동일한 상황에서는 경기 내 파울 상황을 기준으로 판단해야 합니다.

우리의 기본 데이터는 각 경기에서 양측이 득점한 골 수뿐이기 때문에 포인트 1~3은 달성할 수 있지만 포인트 4는 달성할 수 없습니다.

다음 단계는 이 정보를 바탕으로 양식을 디자인하는 것입니다.

2. 조판 디자인

1. 숫자를 별도로 저장

조판 디자인의 첫 번째 원칙은 하나의 셀에 한 가지 유형의 데이터만 저장한다는 것입니다. 예를 들어 5:2라는 점수를 하나의 셀에 넣으면 두 숫자를 따로 추출한 뒤 나중에 통계 분석할 때 계산을 해야 하는데, 이는 매우 불편하다. 따라서 테이블을 디자인할 때 이 문제를 고려해야 합니다.

모든 사람이 쉽게 이해할 수 있도록 셀 테두리를 표시합니다. 실제로 각 악보에 세 개의 셀이 사용되어 점수를 나누는 것을 명확하게 볼 수 있습니다. 결과는 중간에 슬래시가 있고 네 번째 행에 팀 이름이 있는 4개의 셀이 병합된 셀을 사용하여 처리됩니다.

병합된 셀을 합리적으로 사용함으로써 숫자를 별도로 저장해야 하는 요구 사항을 충족할 뿐만 아니라 시각적 요구도 충족하며 게임 결과를 매우 직관적으로 확인할 수 있습니다.

2. 보조란을 합리적으로 활용하세요

조판 디자인의 두 번째 원칙은 입력 단계를 단순화하고 계산 과정을 단순화하는 것입니다. 예를 들어, 이 표에서 대각선 아래 노란색 영역에 점수를 입력해야 합니다. 공식은

입니다.

각 팀의 승리를 계산하기 위해 필드 수, 일반 필드 및 음수 필드, 일부 보조 열이 사용됩니다.

보조 열에 대해 일부 친구들은 너무 번거롭다고 생각하고 항상 수식을 사용하여 직접 결과를 얻기를 바랍니다. 사실 이것은 오해입니다. 복잡한 논리적 관계나 계산 단계가 많은 일부 템플릿 테이블에서 보조 열을 잘 활용하면 수식의 어려움을 단순화하고 계산량을 줄일 수 있으며 계산 프로세스가 모두 다음을 통해 구현되므로 사용 중 테이블 유지 관리가 용이합니다. 보조 열. 어딘가에서 계산 방법을 조정해야 하는 경우 해당 위치만 변경하면 됩니다.

위는 이 성능 통계표의 디자인에 대한 설명입니다. 많아 보이지만 실제로는 어렵지 않습니다. 다음으로 이 표에 어떤 수식과 함수가 사용되는지 살펴보겠습니다.

3. 포뮬러 디자인

1. 승리 판정: =IF(F5>H5,1,0)=IF(F5>H5,1,0)

利用IF函数直接比较F5和H5，当F5大于H5为胜，得到1，反之得到0。

胜场次数计算：=SUM(O5:Q5)

直接对前面的数据求和。

2、平场的判断：=IF(AND(F5=H5,F5" "),1,0)

与胜场有点区别，平场判断时还需要加一个条件，也就是进球数不为空的时候才去比较，因此加了AND函数去做判断。同时满足进球数相等并且不为空这两个条件时才算打平，记为1，反之为0。

平场次数计算：

同样是直接求和。

3、负场的判断：=IF(F5

与胜场的判断方法一样，只是公式中把大于符号改成小于符号。

负场次数计算：

直接求和即可。

以上这部分内容是将每场比赛结果变成具体的数字，看起来有点麻烦，但是没什么难度。接下来将辅助列隐藏，仅显示胜场、平场和负场的统计次数即可。

4、进球数、失球数和净胜球数的统计：

进球数：=SUM(F5,I5,L5)

失球数：=SUM(H5,K5,N5)

净胜球数：=AA5-AB5

这部分公式非常简单，都是加减运算。

5、积分的计算

按照规则，胜场记3分，平场记1分，负场记0分，积分的公式就是：=R5*3+V5*1

至此，计算排名的各项指标我们都算出来了，分别是：积分、净胜球和进球数。

在根据这三项指标计算排名之前，还需要做一个过渡，将三项指标根据各自的优先级（权重）进行量化，变成可以比较的数字，可以在AF列使用这样的一个公式来实现：=AD5+AC5%+AA5%%

F5와 H5를 직접 비교하려면 IF 함수를 사용하세요. F5일 때 H5보다 크면 승리하고 1을 얻고, 그렇지 않으면 0을 얻습니다.

승률 계산: =SUM(O5:Q5)

🎜이전 데이터를 직접 합산합니다. 🎜🎜🎜2. 플랫 필드의 판단: 🎜=IF(AND(F5=H5,F5" "),1,0)🎜🎜🎜🎜이기는 게임과는 조금 다르고, 플랫게임 판단시에도 필요합니다. 즉, 골수가 비어있지 않은 경우에만 비교가 이루어지도록 조건을 추가하여 판단을 내릴 수 있도록 AND 기능을 추가하였습니다. 득점 개수가 동일하고 비어 있지 않은 두 가지 조건이 동시에 충족되는 경우에만 동점으로 간주하여 1로 기록하고, 그렇지 않으면 0으로 기록합니다. 🎜🎜플랫 타임 계산: 🎜🎜🎜🎜도 직계합입니다. 🎜🎜🎜3. 음수 필드 판단: 🎜=IF(F5🎜🎜🎜🎜승판정 방법은 수식에서 보다 큼 기호가 보다 작음 기호로 변경된다는 점만 제외하면 동일합니다. 🎜🎜계산 부정적인 게임 수: 🎜🎜🎜🎜직접 합산합니다. 네. 🎜🎜위 부분은 각 게임의 결과를 특정 숫자로 바꾸는 부분인데 조금 번거로워 보이지만 어렵지는 않습니다. 다음으로 보조 열을 숨기고 숫자만 표시합니다. 승리, 무승부, 패배 통계 "실용적인 Excel 기술 공유: 다단계 순위 통계표를 만드는 방법을 알아보세요."/>🎜 🎜🎜4. 득점, 실점 및 골 차이 통계: 🎜🎜🎜득점 수: =SUM(F5,I5,L5)🎜🎜Number 허용된 득점 수: =SUM(H5,K5,N5)🎜🎜골 차이: =AA5-AB5🎜🎜공식의 이 부분은 매우 간단합니다. 덧셈과 뺄셈 연산 🎜🎜🎜🎜🎜5. 포인트 계산🎜🎜🎜규칙에 따르면 승리는 3점, 패배는 0점입니다. 포인트 공식은 =R5입니다. *3+V5*1🎜🎜🎜🎜이 시점에서 우리는 순위에 대한 다양한 지표를 계산했습니다. 즉, 점수, 골 차이 및 골 수 등 모두 계산됩니다. 🎜🎜이 세 가지 지표를 기반으로 순위를 계산하기 전에 세 가지 지표를 각각의 우선순위(가중치)에 따라 정량화하려면 전환이 필요하며, 이는 AF 열에서 다음 공식을 사용하여 달성할 수 있습니다. =AD5+AC5%+AA5% % 결과는 다음과 같습니다. 🎜🎜이 공식이 이상하다고 생각하시나요? 백분율 기호는 무엇을 의미하나요? 🎜🎜여기서는 AD5(득점), AC5(골 차이), AA5(골 수)의 세 가지 셀을 사용하여 설명하겠습니다. 🎜🎜🎜🎜

랭킹 규칙에는 포인트가 먼저 사용된다고 명시되어 있습니다. 포인트가 동일할 경우 골득실차로 판단하고, 동일할 경우 득점 수로 판단합니다. 그렇다면 여러 데이터의 순차적 비교를 단 하나의 데이터 비교로 변환하는 방법은 무엇일까요? 다중 요인 순위를 계산하기 위해 가중치를 사용하는 방법을 참조할 수 있습니다. 예를 들어 A지표 가중치가 50%, B지표 가중치가 30%, C지표 가중치가 20%인 경우 종합점수는 A*0.5+B*0.3+C*0.2가 됩니다. 이므로 A, B, C 세 가지 순위 요소가 있지만 종합 점수만 비교하면 순위를 얻을 수 있습니다. A*0.5+B*0.3+C*0.2，如此虽然有A、B、C三个排名因素，但只需要比较综合得分即可获得排名。

这时这就需要人为的指定权重，将需要先后比较的数据转化为一个数据。

公式=AD5+AC5%+AA5%%原本的写法应该是：=AD5*100+AC5*10+AA5，在AG列输入公式，结果如下。

也就是将每个指标按照优先级分别扩大100倍、10倍、1倍后相加，使每个指标不会在同一个数位上，而是分别位于百位、十位和个位，如此既保证了各指标的优先等级，又将多个指标化成了一个数据。（注：当前倍数设置只适合积分、净胜球、进球数据都是小于10的情况。）

在足球比赛中，数字都比较小（一般都不满10），如果要计算的数据量级比较大的话，仅仅差10倍是不够的，经常会遇到相差100倍或者10000倍的情况。在这种这种情形下，公式里就会有很多个0，此时可以变个思路，将扩大改为缩小，%的作用就是将数据缩小100倍，%%的作用就是将数据缩小10000倍，这就是=AD5+AC5%+AA5%%的由来。

经过了这么多步骤，终于到了计算最终排名的时候，一起来看看排名公式吧。

6、排名的计算

公式非常简单，就是rank函数的标准用法：=RANK(AF5,$AF:$AF)

再来复习一下rank函数的含义：=rank（要计算名次的数据，参与排名的区域，升序或降序）

이때, 연속해서 비교해야 하는 데이터를 하나의 데이터로 변환하려면 가중치를 수동으로 지정해야 합니다.

공식 =AD5+AC5%+AA5%%의 원래 내용은 다음과 같아야 합니다: =AD5*100+AC5*10+AA5, 열에 공식을 입력하세요 AG, 그 결과는 다음과 같다.

또한 즉, 각 지표를 우선순위에 따라 100배, 10배, 1배로 확장한 후 합산하여 각 지표가 같은 자리에 위치하지 않고 백, 십, 일 단위로 위치하게 됩니다. 이를 통해 각 지표의 우선순위가 결정되고 여러 지표가 하나의 데이터로 변환됩니다. (참고: 현재 승수 설정은 득점, 골 차이, 골 데이터가 모두 10 미만인 경우에만 적합합니다.) 축구 경기에서는 숫자가 상대적으로 작습니다(일반적으로 데이터가 10 미만). 상대적으로 크다면 10배, 100배의 차이로는 부족할 때가 많습니다. 이 경우에는 수식에 0이 많이 나올 것입니다. 이때 생각을 바꿔서 확장을 축소로 바꾸면 됩니다. %의 기능은 데이터를 100배로 줄이는 것입니다. 데이터를 10,000배로 줄여보세요. 이것이 =AD5+AC5%+AA5%%의 유래입니다. 많은 단계를 거쳐 드디어 최종 순위를 계산할 차례입니다. 순위 공식을 살펴보겠습니다.

6 순위 계산 공식

은 매우 간단합니다. 이는 순위 함수의 표준 사용법입니다: =RANK(AF5,$AF$5:$AF$8)

의 의미를 검토해 보겠습니다. 순위 함수:=순위(순위 계산을 위한 데이터, 순위에 참여하는 영역, 오름차순 또는 내림차순), 세 번째 매개변수가 생략되면 순위는 내림차순으로, 가장 높은 점수가 1순위가 됩니다.

🎜드디어 보조 열을 모두 숨기면 성능 통계 테이블이 완성됩니다. 🎜🎜🎜🎜🎜🎜🎜 IV. 요약 🎜🎜🎜🎜이런 템플릿을 디자인하는 것이 정말 번거롭다고 느끼실 수도 있습니다. 보조열을 사용하지 않고 수식을 하나씩 채워야 하는 곳이 있는데, 수식을 직접 끌어내릴 수 있는 방법이 있나요? 🎜🎜분명히 하나는 있지만 배열 수식과 복잡한 함수를 많이 사용하므로 사용하기 불편할 수 있습니다. 물론 이 형태에 관한 한 지속적인 최적화 가능성은 분명히 존재합니다. 베테랑은 판매 순위 계산을 위한 보고서 템플릿을 개선하고 그 과정에서 반복적으로 테스트하고 수정하는 데 일주일을 보냈습니다. 하지만 기본 데이터만 입력하면 가장 원하는 결과를 볼 수 있기 때문에 사용자 경험은 매우 좋습니다. 중간 계산 프로세스는 모두 보조 열(시트에 별도로 저장됨)이며 비교적 간단한 기능을 사용합니다. 수정이 필요한 경우 사용자가 직접 해결할 수 있습니다. 🎜🎜보고서 템플릿의 경우 디자인 시간이 품질의 척도가 아니라 사용 편의성과 유지 관리 용이성을 측정하는 기준입니다. 오늘의 예를 통해 작업을 더욱 효율적으로 만들고 양식을 더욱 스마트하게 만들 수 있는 템플릿 디자인에 대한 몇 가지 아이디어를 배울 수 있기를 바랍니다. 🎜🎜관련 학습 권장 사항: 🎜excel 튜토리얼🎜🎜

위 내용은 실용적인 Excel 기술 공유: 다단계 순위 통계표를 만드는 방법을 알아보세요.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!