技术问询：如何计算2013-2015年各国板球单场平均得分

Hey Nick, let's work through this step by step—calculating per-match averages and updating your chart is totally doable with a few simple steps, depending on what tool you're using to handle your cricket data.

First, the key formula you need is straightforward:

单场平均得分 = 年度总得分 ÷ 该年度赛事场次

You already have the match counts per year (6 for 2013, 13 for 2014, 1 for 2015), so you just need to pair each year's total score with its corresponding match count to get the average.

1. Excel/Google Sheets (Quick & No-Code) #

If you're using a spreadsheet tool, here's how to implement this fast:

• Assume your data is laid out with:
  • Column A: Years (2013, 2014, 2015)
  • Column B: Total yearly scores
  • Column C: Match counts (6, 13, 1)
• In cell D2, enter the formula =B2/C2, then drag the fill handle down to apply it to the other rows. This will populate the per-match averages.
• To make the chart: Select columns A (years) and D (averages), then insert a bar/column chart. Tweak labels and titles to match your needs, and you're set.

2. Python (For Large Datasets) #

If your dataset is too big for spreadsheets, use Python with pandas and matplotlib to automate the calculation and charting:

import pandas as pd
import matplotlib.pyplot as plt

# Load your data (replace with your actual data source)
df = pd.read_csv("your_cricket_data.csv")

# Map each year to its match count
year_to_matches = {2013: 6, 2014: 13, 2015: 1}
df["matches_played"] = df["year"].map(year_to_matches)

# Calculate per-match average
df["avg_score_per_match"] = df["total_yearly_score"] / df["matches_played"]

# Generate the updated bar chart
plt.figure(figsize=(8, 5))
plt.bar(df["year"], df["avg_score_per_match"], color="#1f77b4")
plt.xlabel("Year")
plt.ylabel("Average Score per Match")
plt.title("Australia & Other Nations: Avg Score per Match (2013-2015)")
plt.show()

3. SQL (If Data Lives in a Database) #

If your cricket data is stored in a SQL database, run this query to compute the averages directly:

SELECT
    year,
    total_yearly_score,
    -- Define match counts per year
    CASE
        WHEN year = 2013 THEN 6
        WHEN year = 2014 THEN 13
        WHEN year = 2015 THEN 1
    END AS matches_played,
    -- Calculate average
    total_yearly_score / CASE
        WHEN year = 2013 THEN 6
        WHEN year = 2014 THEN 13
        WHEN year = 2015 THEN 1
    END AS avg_score_per_match
FROM
    cricket_scores
WHERE
    year IN (2013, 2014, 2015);

Export the query results, then use a tool like Tableau or Power BI to build your chart.

Pro Tip #

If your dataset includes individual match scores (not just yearly totals), you can skip manual match counts entirely by calculating the average directly from raw match data:

• In Excel: Use =AVERAGEIF(A:A, 2013, B:B) to get the average score for all 2013 matches.
• In Python: df.groupby("year")["individual_match_score"].mean()
• In SQL: SELECT year, AVG(individual_match_score) FROM cricket_scores WHERE year IN (2013,2014,2015) GROUP BY year;

This avoids any risk of typos when entering match counts manually!

内容的提问来源于stack exchange，提问作者Nick Bohl