実践的な Excel スキルの共有: 多要素ランキング統計表の作成方法を学びますか?

前回の記事「実践的な Excel スキルを共有: 表に背景画像を追加する 2 つの方法」では、表の背景画像の使用方法を学びました。今日は統計表について話し、ワールドカップを見て多要素ランキング統計表の作成方法を学びます。

4年に一度開催されるワールドカップの激戦が佳境を迎える中、ファンは誰もが贔屓のチームを応援するとともに、各国の状況をより心配している。このチームのグループ。第 1 ラウンドが終了すると、グループ ステージはクライマックスを迎え、同じグループのチームが得点するすべてのゴールが全体の結果に影響を与える可能性があります。メディア情報が非常に発達した現在、最新のニュースや発展予測が至る所で見つかりますが、データを入力した後にグループのランキング結果を計算して予測できるスプレッドシートがあれば非常に便利であることは間違いありません。今日はそんなゲーム成績統計表です。

テーブルの最終的な効果は以下の通りです:

#機能: 黄色のエリアにゲーム結果を入力する限り、さまざまなインジケーターが表示されます。そして最終ランキング。

価値:

①戦闘データのリアルタイム統計

②順位予測

もちろん、より重要な意義は、「ゲームの設計思想を通して学ぶこと」です。この表には、仕事で総合ランキングを計算する際に問題が発生した場合に学ぶことができる詳細な知識が数多くあります。具体的な制作工程を見てみましょう。

各グループの構造は全く同じであるため、グループ A のデータのみを例として説明します。テーブル全体の制作は、全体構想の決定、レイアウト設計、配合設計の3段階に分かれます。

1. 全体的なアイデアを決定する

テーブルを設計する前に、関連するルールや要件を整理し、どのような基盤を明確にする必要があります。使用できるデータ、最終的にどのような結果が必要になるか、そして中間リンクを設計します。

このパフォーマンス統計表では、基本データは各試合後の 2 つの数字 (両チームが得点したゴール数) であり、必要な最終結果はグループ ランキングです。

各ゲームの結果に基づいてランキングを取得する方法、ランキングのルールも理解する必要があります:

上の画像は公式の説明です。計算ルールは以下のように要約できます:

①各試合のニーズに応じて各チームの勝敗を判定し、勝った場合は勝ち点 3 を獲得します。引き分けの場合は 1 点、負けの場合は 0 点となります ;

② 同点の場合は得失点差で順位を決定します (得失点差は得点数です)得点数 - 失点数);

③ 得失点差が同じ場合は得点数に基づいて順位を決定します。

④ゴール数が同じ場合は、試合のファウル状況に基づいて判断する必要があります。

基本データは各試合の両チームのゴール数のみであるため、ポイント 1 ～ 3 は達成できますが、ポイント 4 は達成できません。

次に、この情報に基づいてフォームを設計する必要があります。

2. 写植デザイン

1. 数字を分けて保存する

写植でマスターすべきこと設計 第 1 の原則は、セルには 1 種類のデータのみが保存されるということです。たとえば、5:2 のスコアが 1 つのセルに配置されている場合、2 つの数値を別々に抽出し、後の統計分析で計算する必要があり、非常に不便です。したがって、テーブルを設計する際には、この問題を考慮する必要があります。

皆さんの理解を容易にするために、セルの境界線を表示します。実際、各スコアが 3 であることがはっきりとわかります。セルは 2 つのチームの結果をコロンとは別に保存するために使用され、中央にスラッシュがあり、4 行目にチーム名が含まれる 4 つのセルはすべて結合されたセルを使用して処理されます。

結合されたセルを合理的に使用することで、数値を個別に保存するという要件を満たすだけでなく、視覚的なニーズも満たし、ゲームの結果を直感的に見ることができます。

2. 補助列の合理的な使用

植字設計の 2 番目の原則は、入力ステップを簡素化し、計算プロセスを簡素化することです。これは、補助列と式を使用して実現できます。たとえば、この表では、対角線の下の黄色の領域にスコアを入力する必要があります。対角線より上のスコアは、次の式で参照されます:

各チームの勝ち数、引き分け数、負け数を数えるために、いくつかの補助列が使用されます:

補助列に関しては、友人の中には面倒に感じて、いつも数式を使って直接結果を求める人もいるかもしれませんが、実はこれは誤解です。複雑な論理関係や多くの計算ステップを持つ一部のテンプレート テーブルでは、補助列をうまく利用すると、計算プロセスがすべて補助列。どこかの計算方法を調整する必要がある場合は、対応する場所を変更するだけで済みます。

以上がこの性能統計表の設計の説明です。難しそうに見えますが、実際はそれほど難しくありません。次に、この表でどのような式や関数が使用されているかを見てみましょう。

3. 計算式の設計

1. 勝利の判定: =IF(F5>H5 , 1,0)

IF 関数を使用して F5 と H5 を直接比較します。F5 が H5 より大きい場合は勝ちとなり、1 が得られます。それ以外の場合は 0 が返されます。

勝利数の計算: =SUM(O5:Q5)

前のデータを直接合計します。

2. フラットフィールドの判定: =IF(AND(F5=H5,F5" "),1,0)

# 勝ち試合とは少し異なり、引き分け判定の場合はゴール数が空でない場合のみ比較するという条件を追加する必要があります。そこでAND関数を加えて判定します。同数のゴールと空でないという 2 つの条件が同時に満たされた場合にのみ引き分けとみなされ、1 として記録され、それ以外の場合は 0 と記録されます。

フラット フィールドの数の計算:

も直接和です。

3. 負け試合の判定: =IF(F5

と勝ち試合の判定方法同じことですが、数式内の大なり記号を小なり記号に変更するだけです。

負のフィールドの数の計算:

合計するだけです。

上記の部分は、各試合の結果を具体的な数値に変換する部分です。少し面倒に思えますが、難しくはありません。次に、補助列を非表示にして、勝ち数の統計のみを表示します。

4. 得点、失点、得失点差の統計:

ゴール数: =SUM(F5,I5,L5)

失点数: =SUM(H5,K5,N5)

得失点差: = AA5-AB5

式のこの部分は非常に単純で、すべての加算と減算の演算です。

5. 積分の計算

ルールによれば、勝利は 3 ポイント、引き分けは 1 ポイント、負けは 0 ポイントとなります。ポイントの計算式は次のとおりです: =R5*3 V5 *1

この時点で、順位を計算するためのすべての指標、つまり勝ち点、得失点差、ゴール数が計算されました。 ## これら 3 つの指標に基づいてランキングを計算する場合、以前は 3 つの指標をそれぞれの優先順位 (重み) に応じて数値化し、比較可能な数値にする必要がありましたが、AF 列でこのような計算式を使用することで実現できます。 :

=AD5 AC5% AA5 %%、結果は次のとおりです。

この式は奇妙だと思いますか? パーセント記号は何を意味しますか?

焦点を当てましょうここでは 3 つのセルが使用されています: AD5 (勝ち点)、AC5 (得失点差)、AA5 (ゴール数)

ランキングルールには、まず勝ち点を重視し、同勝ち点の場合は得失点差で判定し、その後に得点数で判定することが明記されている。では、この複数のデータの逐次比較を 1 つのデータのみの比較に変換するにはどうすればよいでしょうか?重みを使用して多要素ランキングを計算する方法を参照できます。たとえば、指標 A の重みが 50%、指標 B の重みが 30%、指標 C の重みが 20% である場合、総合スコアは A*0.5 B*0.3 C*0.2## となります。 # なので、A、B、C の 3 つのランキング要素がありますが、ランキングを取得するには合計スコアのみを比較する必要があります。

このとき、重みを手動で指定し、連続して比較する必要があるデータを一つのデータに変換する必要があります。

式

=AD5 AC5% AA5%%元の書き方は次のようになります: =AD5*100 AC5*10 AA5、AG列に式を入力し、その結果を入力します。以下のとおりであります。

つまり、各指標が優先度に応じて 100 倍、10 倍、1 倍に拡張され、各指標が一致しないように合計されます。これにより、各インジケーターの優先順位が保証されるだけでなく、複数のインジケーターが 1 つのデータに変換されます。 (注: 現在の乗数設定は、勝ち点、得失点差、ゴール データがすべて 10 未満である状況にのみ適しています。)

サッカーの試合では、数値は比較的小さい (通常は 10 未満)。データの大きさが比較的大きい場合は、10 倍の差では不十分で、100 倍や 10,000 倍の差が生じることもよくあります。この場合、式中にゼロが多くなります。このとき、考え方を変えて、拡張を縮小に変更します。% の関数はデータを 100 倍に縮小することであり、%% の関数は、データを 100 倍にすることです。データを 10,000 倍に削減します。これが

=AD5 AC5% AA5%% の起源です。

多くのステップを経て、いよいよ最終的なランキングを計算します。ランキングの計算式を見てみましょう。

6. ランキングの計算

式は非常に簡単で、ランク関数の標準的な使用法です:

=RANK(AF5,$AF$5 :$AF$8)

ランク関数の意味を確認しましょう:

=rank (ランキングを計算するためのデータ、ランキングに参加しているエリア、昇順または降順) 。3 番目のパラメータを省略した場合のランキングは降順であり、スコアが最も高いものが最初にランクされます。

最後にすべての補助列を非表示にすると、パフォーマンス統計テーブルが完成します。

# 4. 概要

このようなテンプレートを設計するのは非常に面倒に感じられるかもしれませんが、補助列が非常に多く、数式が所々プルダウンできず、入力する必要があります。 1. 補助柱は必要ありませんか? 直接プルダウン方式についてはどうですか?

確かに存在しますが、多くの配列数式と複雑な関数を使用するため、使いにくいかもしれません。もちろん、この形式に関する限り、継続的に最適化が行われる可能性は間違いなくあります。このベテランはかつて、売上ランキングを計算するためのレポート テンプレートを改善するのに 1 週​​間を費やし、プロセス中にテストと修正を繰り返しました。ただし、基本的なデータを入力するだけで、最も望ましい結果が表示されるため、ユーザー エクスペリエンスは非常に優れています。中間の計算プロセスはすべて補助列 (シートに個別に保存) であり、比較的単純な関数を使用します。変更が必要な場合は、ユーザーが自分で解決できます。

レポート テンプレートの場合、設計時間の長さは品質の尺度ではなく、使いやすさとメンテナンスの容易さの尺度になります。今日の例から、作業を効率化し、フォームをよりスマートにする、テンプレートをデザインするためのアイデアをいくつか学んでいただければ幸いです。

関連する学習の推奨事項:

Excel チュートリアル

以上が実践的な Excel スキルの共有: 多要素ランキング統計表の作成方法を学びますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。