Praktischer Austausch von Excel-Kenntnissen: Erfahren Sie, wie Sie eine statistische Multi-Faktor-Ranking-Tabelle erstellen?

Im vorherigen Artikel „Praktisches Teilen von Excel-Kenntnissen: Zwei Methoden zum Hinzufügen von Hintergrundbildern zu Tabellen“ haben wir gelernt, wie man Tabellenhintergrundbilder verwendet. Heute werden wir über die Statistiktabelle sprechen und lernen, eine Multi-Faktor-Ranking-Statistiktabelle zu erstellen, indem wir uns die Weltmeisterschaft ansehen. Schauen Sie sich das an!

Der harte Wettbewerb bei der alle vier Jahre stattfindenden Weltmeisterschaft ist in vollem Gange. Jeder Fan feuert seine Lieblingsmannschaft an und macht sich mehr Sorgen um die Leistung seiner Mannschaft. Mit dem Ende der ersten Runde erreicht die Gruppenphase ihren Höhepunkt und jedes von einer Mannschaft derselben Gruppe erzielte Tor kann sich auf das Gesamtergebnis auswirken. Obwohl die Medieninformationen mittlerweile sehr weit entwickelt sind, sind aktuelle Nachrichten und Entwicklungsprognosen überall zu finden. Es ist jedoch zweifellos sehr praktisch, wenn Sie über eine Tabelle verfügen, mit der Sie nach Eingabe der Daten die Ergebnisse des Gruppenrankings berechnen und vorhersagen können heute eine solche Spielleistungsstatistiktabelle.

Der endgültige Effekt der Tabelle ist wie folgt:

Funktion: Solange Sie die Wettbewerbsergebnisse im gelben Bereich eingeben, werden verschiedene Indikatoren und die endgültige Rangliste automatisch berechnet.

Wert:

①Echtzeitstatistiken der Kampfdaten

②Vorhergesagte Ranglisten

Natürlich ist die wichtigere Bedeutung: Lernen Sie viele detaillierte Wissenspunkte durch die Designideen dieser Tabelle, wenn Sie auf das Problem der Berechnung stoßen das umfassende Ranking in Ihrer Arbeit Alle Fragen können als Referenz verwendet werden. Werfen wir einen Blick auf den konkreten Produktionsprozess.

Da die Struktur jeder Gruppe völlig gleich ist, nehmen wir zur Erläuterung nur die Daten der Gruppe A als Beispiel. Die Herstellung der gesamten Tabelle gliedert sich in drei Phasen: Festlegung der Gesamtidee, Layoutgestaltung und Formelgestaltung.

1. Bestimmen Sie die Gesamtidee

Bevor Sie das Formular entwerfen, müssen Sie die relevanten Regeln und Anforderungen klären, klären, welche Basisdaten verwendet werden können und welche Ergebnisse am Ende benötigt werden, und dann Entwerfen Sie die Zwischenglieder.

In dieser Leistungsstatistiktabelle sind unsere Grunddaten die beiden Zahlen nach jedem Spiel (die Anzahl der von beiden Seiten erzielten Tore); das erforderliche Endergebnis ist die Gruppenwertung.

Um die Rangliste basierend auf den Ergebnissen jedes Spiels zu erhalten, müssen Sie auch die Ranglistenregeln verstehen:

Das obige Bild ist die offizielle Erklärung, bei der es sich um eine relativ komplizierte Berechnungsregel handelt in die folgenden Punkte:

①Beurteilen Sie das Ergebnis jedes Teams anhand der Bedürfnisse jedes Spiels. Ein Sieg wird mit 3 Punkten gewertet, ein Unentschieden wird mit 1 Punkt gewertet und eine Niederlage wird mit 0 Punkten gewertet

②Wenn die Punkte gleich sind, basiert die Rangfolge auf der Tordifferenz (die Tordifferenz ist Anzahl der erzielten Tore - Anzahl der kassierten Tore);

③Wenn die Tordifferenz gleich ist, werden die Teams nach der Tordifferenz eingestuft Anzahl der erzielten Tore.

④ Bei Situationen, in denen die Anzahl der Tore immer noch gleich ist, muss die Beurteilung auf der Grundlage der Foulsituation im Spiel erfolgen.

Da es sich bei unseren Grunddaten lediglich um die Anzahl der von beiden Seiten in jedem Spiel erzielten Tore handelt, können die Punkte 1 bis 3 erreicht werden, Punkt 4 jedoch nicht.

Der nächste Schritt besteht darin, das Formular basierend auf diesen Informationen zu entwerfen. 2. Satzdesign Wenn beispielsweise die Punktzahl 5:2 in einer Zelle platziert wird, müssen die beiden Zahlen separat extrahiert und dann während der statistischen Analyse berechnet werden, was sehr unpraktisch ist. Daher muss dieses Problem beim Entwerfen von Tabellen berücksichtigt werden. Werfen wir einen Blick darauf, wie es gemacht wird:

Um es für alle verständlicher zu machen, zeigen wir die Zellränder an. Es ist deutlich zu erkennen, dass für jede Punktzahl tatsächlich drei Zellen verwendet werden, die die teilen zwei Teams Die Ergebnisse werden getrennt vom Doppelpunkt gespeichert. Die vier Zellen mit dem Schrägstrich in der Mitte und dem Teamnamen in der vierten Zeile werden mithilfe zusammengeführter Zellen verarbeitet.

Durch die rationale Verwendung zusammengeführter Zellen wird nicht nur die Anforderung erfüllt, Zahlen separat zu speichern, sondern auch die visuellen Anforderungen erfüllt, und Sie können die Ergebnisse des Spiels sehr intuitiv sehen.

2. Hilfsspalten sinnvoll nutzen

Das zweite Prinzip des Satzdesigns besteht darin, die Eingabeschritte zu vereinfachen und den Berechnungsprozess zu vereinfachen. Dies kann mit Hilfe von Hilfsspalten und Formeln erreicht werden. In dieser Tabelle müssen Sie beispielsweise die Punktzahl in den gelben Bereich unter der diagonalen Linie eintragen. Auf die Punktzahl über der diagonalen Linie wird verwiesen:

Um die Siege jedes Teams zu zählen. Anzahl Felder, flache Felder und negative Felder, werden einige Hilfsspalten verwendet:

Einige Freunde finden es möglicherweise zu mühsam und hoffen immer, die Ergebnisse direkt mithilfe von Formeln zu erhalten. Tatsächlich ist dies ein Missverständnis. In einigen Vorlagentabellen mit komplexen logischen Beziehungen oder vielen Berechnungsschritten kann die sinnvolle Verwendung von Hilfsspalten die Schwierigkeit von Formeln vereinfachen, die Anzahl der Berechnungen reduzieren und die Pflege der Tabelle während der Verwendung erleichtern, da der Berechnungsprozess vollständig implementiert ist Hilfsspalten: Wenn Sie die Berechnungsmethode irgendwo anpassen müssen, müssen Sie nur die entsprechende Stelle ändern.

Das Obige ist eine Erklärung des Designs dieser Leistungsstatistiktabelle. Es scheint viel zu sein, ist aber eigentlich nicht schwierig. Schauen wir uns als Nächstes an, welche Formeln und Funktionen in dieser Tabelle verwendet werden.

3. Formelentwurf

1. Beurteilung des Sieges: =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%%

Verwenden Sie die IF-Funktion, um F5 und H5 direkt zu vergleichen. Wenn F5 größer als H5 ist, gewinnt und erhält 1, andernfalls erhält es 0.

Berechnung der Gewinne: =SUM(O5:Q5)

🎜Summieren Sie die vorherigen Daten direkt. 🎜🎜🎜2. Beurteilung des flachen Feldes: 🎜=IF(AND(F5=H5,F5" "),1,0)🎜🎜🎜🎜Es ist ein wenig anders, als Spiele zu gewinnen, und das ist es auch Wird auch bei der Beurteilung eines flachen Spiels benötigt. Fügen Sie eine Bedingung hinzu. Das heißt, der Vergleich wird nur durchgeführt, wenn die Anzahl der Tore nicht leer ist. Daher wird die UND-Funktion hinzugefügt, um die Beurteilung vorzunehmen. Es gilt nur dann als Unentschieden, wenn die beiden Bedingungen „gleiche Anzahl an Toren“ und „Nicht-Leerstand“ gleichzeitig erfüllt sind, und wird als 1 gewertet, andernfalls ist es 0. 🎜🎜Berechnung der Pauschalzeiten: 🎜🎜🎜🎜 ist auch eine direkte Summe. 🎜🎜🎜3. Beurteilung des negativen Feldes: 🎜=IF(F5🎜🎜🎜🎜Die Methode zur Bewertung von Gewinnen ist dieselbe, außer dass das Größer-als-Symbol in der Formel in ein Kleiner-als-Symbol geändert wird. 🎜🎜Berechnung der Anzahl der negativen Spiele: 🎜🎜🎜🎜Direkt summieren. Ja. 🎜🎜Der obige Teil besteht darin, die Ergebnisse jedes Spiels in bestimmte Zahlen umzuwandeln, was etwas umständlich erscheint, aber nicht schwierig ist. Als nächstes blenden Sie die Hilfsspalte aus und zeigen nur die an Statistiken zu Siegen, Unentschieden und Niederlagen "Praktischer Austausch von Excel-Kenntnissen: Erfahren Sie, wie Sie eine statistische Multi-Faktor-Ranking-Tabelle erstellen?"/>🎜 🎜🎜4. Statistik der erzielten Tore, kassierten Tore und Tordifferenz: 🎜🎜🎜Anzahl der erzielten Tore: =SUM(F5,I5,L5)🎜🎜Anzahl der kassierten Gegentore: =SUM(H5,K5,N5)🎜🎜Tordifferenz: =AA5-AB5🎜🎜Dieser Teil der Formel ist sehr einfach, das ist alles Additions- und Subtraktionsoperationen 🎜🎜🎜🎜🎜5. Punkteberechnung🎜🎜🎜Gemäß den Regeln ist ein Sieg 3 Punkte wert, ein Unentschieden ist 1 Punkt wert und eine Niederlage ist 0 Punkte wert. Die Formel für Punkte lautet: =R5 *3+V5*1🎜🎜🎜🎜An dieser Stelle haben wir die verschiedenen Indikatoren für die Rangliste berechnet. Sie werden alle berechnet, nämlich: Punkte, Tordifferenz und Anzahl der Tore. 🎜🎜Bevor wir die Rangliste anhand dieser drei Indikatoren berechnen, a Es muss ein Übergang vorgenommen werden, um die drei Indikatoren entsprechend ihrer jeweiligen Prioritäten (Gewichte) zu quantifizieren. Dies kann durch die Verwendung einer solchen Formel in der AF-Spalte erreicht werden: =AD5+AC5%+AA5%. %, die Ergebnisse sind wie folgt. 🎜🎜Finden Sie diese Formel seltsam? Was bedeutet das Prozentzeichen? 🎜🎜Konzentrieren wir uns auf die Erläuterung dieser Formel. Hier werden drei Zellen verwendet: AD5 (Punkte), AC5 (Tordifferenz) und AA5 (Anzahl der Tore)🎜🎜🎜🎜

In den Ranglistenregeln ist klar festgelegt, dass bei Punktegleichheit zunächst die Tordifferenz und dann bei Punktegleichheit die Anzahl der Tore gewertet wird. Wie lässt sich dieser sequentielle Vergleich mehrerer Daten in einen Vergleich nur einzelner Daten umwandeln? Sie können sich auf die Methode zur Verwendung von Gewichten zur Berechnung von Multi-Faktor-Rankings beziehen. Wenn beispielsweise die Gewichtung von Indikator A 50 %, die Gewichtung von Indikator B 30 % und die Gewichtung von Indikator C 20 % beträgt, beträgt die Gesamtpunktzahl A*0,5+B*0,3+C*0,2 , also gibt es zwar drei Ranking-Faktoren A, B, C, aber nur die Gesamtpunktzahl muss verglichen werden, um das Ranking zu erhalten. 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（要计算名次的数据，参与排名的区域，升序或降序）

Zu diesem Zeitpunkt müssen Sie das Gewicht manuell angeben, um die Daten, die nacheinander verglichen werden müssen, in einen Datenwert umzuwandeln.

Die ursprüngliche Schreibweise der Formel =AD5+AC5%+AA5%% sollte lauten: =AD5*100+AC5*10+AA5, geben Sie die Formel in die Spalte ein AG, und das Ergebnis ist wie folgt.

Auch Das bedeutet, jeden Indikator je nach Priorität um das 100-fache, 10-fache bzw. 1-fache zu erweitern und sie dann zu addieren, sodass sich jeder Indikator nicht auf derselben Ziffer befindet, sondern sich in den Hundertern, Zehnern und Zehnern befindet Dies stellt sicher, dass die Priorität jedes Indikators bestimmt wird und mehrere Indikatoren in einen Datenwert umgewandelt werden. (Hinweis: Die aktuelle Multiplikatoreinstellung ist nur für Fälle geeignet, in denen Punkte, Tordifferenz und Tordaten alle weniger als 10 betragen.) Bei Fußballspielen sind die Zahlen relativ klein (im Allgemeinen weniger als 10). Wenn die Größenordnung relativ groß ist, reicht ein Unterschied von nur dem 10-fachen nicht aus. Ein Unterschied von 100-fach oder 10.000-fach ist häufig anzutreffen. In diesem Fall enthält die Formel viele Nullen. Zu diesem Zeitpunkt können Sie die Erweiterung in eine Reduktion ändern. Die Funktion von % besteht darin, die Daten um das Hundertfache zu reduzieren Reduzieren Sie die Daten um das 10.000-fache. Dies ist der Ursprung von =AD5+AC5%+AA5%%. Nach so vielen Schritten ist es endlich an der Zeit, das endgültige Ranking zu berechnen.

6. Die Berechnungsformel für das Ranking

ist die Standardverwendung der Rangfunktion: =RANK(AF5,$AF$5:$AF$8)

Lassen Sie uns die Bedeutung von überprüfen die Rangfunktion:=rank (Daten zur Berechnung des Rankings, am Ranking beteiligter Bereich, aufsteigende oder absteigende Reihenfolge), wenn der dritte Parameter weggelassen wird, erfolgt das Ranking in absteigender Reihenfolge und das mit dem Die höchste Punktzahl wird zuerst gewertet.

🎜Schließlich alle Hilfsspalten ausblenden und eine Leistungsstatistiktabelle ist fertig: 🎜🎜🎜🎜🎜🎜🎜 IV. Sie haben vielleicht das Gefühl, dass es wirklich mühsam ist, eine solche Vorlage zu entwerfen. Es gibt viele Hilfsspalten, und die Formeln können an einigen Stellen nicht heruntergezogen werden. Sie müssen einzeln ausgefüllt werden. Gibt es eine Möglichkeit, die Formel direkt herunterzuziehen, ohne Hilfsspalten zu verwenden? 🎜🎜Es gibt definitiv eine, aber sie ist möglicherweise nicht bequem zu verwenden. Sie verwendet viele Array-Formeln und komplexe Funktionen. Selbstverständlich besteht bei dieser Form durchaus die Möglichkeit einer weiteren Optimierung. Der Veteran verbrachte einmal eine Woche damit, eine Berichtsvorlage zur Berechnung von Verkaufsrankings zu verbessern und während des Prozesses wiederholt zu testen und zu modifizieren. Die Benutzererfahrung ist jedoch sehr gut, da Sie nur die Grunddaten eingeben müssen und die am meisten gewünschten Ergebnisse sehen können. Der Zwischenberechnungsprozess besteht aus allen Hilfsspalten (separat in einem Blatt gespeichert) und verwendet sogar relativ einfache Funktionen Wenn Änderungen erforderlich sind, können Benutzer diese selbst lösen. 🎜🎜Bei Berichtsvorlagen ist die Länge der Entwurfszeit kein Maß für die Qualität, sondern die Benutzerfreundlichkeit und Wartungsfreundlichkeit. Ich hoffe, Sie können aus dem heutigen Beispiel einige Ideen für die Gestaltung von Vorlagen lernen, die Ihre Arbeit effizienter und Ihre Formulare intelligenter machen können. 🎜🎜Verwandte Lernempfehlungen: 🎜Excel-Tutorial🎜🎜

Das obige ist der detaillierte Inhalt vonPraktischer Austausch von Excel-Kenntnissen: Erfahren Sie, wie Sie eine statistische Multi-Faktor-Ranking-Tabelle erstellen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!