如何在 Excel 中找到我的情况的平均值？

假设您的表格从 A1 开始并以 E9 结束，但可能会更长很多：

• 确保日期列 B 中的单元格格式正确为日期。
• 在单元格 H2:H13 中写下每个月的第一个日期（2020 年 1 月 1 日、2020 年 1 月 2 日... 2020 年 1 月 12 日）
• 格式化单元格 H2:H13 以改为显示月份（自定义 -> mmmm）。现在 H2:H13 单元格应该显示月份名称。
• 在单元格 I3 中，编写以下公式： =IFERROR(AVERAGEIFS($E$2:$E$3000,$B$2:$B$3000,">="&$H2,$B$2:$B$3000,"<="&EOMONTH ($H2,0)),"-")
• 在下面的单元格中复制公式 I4:I13

现在，我认为我在从 E2 添加到 E3000 和从 B2 添加到 B3000 的公式中添加了足够的行，但是如果需要，如果您有更多行，您可以将这些“3000”更改为更多...

此外，根据您的 Excel 版本和区域设置，您可能需要将公式 (,) 中的每个逗号替换为分号 (;)。我见过一些版本的 excel 需要分号而不是逗号。

我在把它放在这里之前测试了这个公式，我确认它有效。对于缺失的月份或没有任何数据，公式将显示“-”。

注意：如果您提取的年份更多，则需要将范围修改为只有 1 年，否则它将计算 9 月的平均值，例如多年。对于每一年，使用相同的公式在 H 列中再添加 12 个单元格，但具有相应年份的范围。

你的方法是需要做的。

一种更容易的方法是使用 VBA 宏或 Power Query 来做同样的事情。

这是一个 Power Query 解决方案：

使用 Power Query

• 在数据表中选择一些单元格
• Data => Get&Transform => from Table/Range
• 当 PQ 编辑器打开时：Home => Advanced Editor
• 记下第 2 行中的表名
• 粘贴下面的 M 代码代替您看到的内容
• 将第 2 行中的表名称更改回最初生成的名称。
• 阅读评论并探索Applied Steps以了解算法

M代码

编辑1：更好的运行总算法编辑2
：按年份区分
我们所做的就是在生成年份的步骤中将年份添加到月份名称中

let

//Replace Table Name in the next line with actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table30"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Sl. No.", Int64.Type}, 
        {"Tran Date", type date}, 
        {"Withdrawal", Currency.Type}, {"Deposit", Currency.Type}, {"Balance Amount", Currency.Type}
    }),

//Replace nulls with 0 because cannot add nulls
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Withdrawal", "Deposit"}),

//get starting balance. Should be last entry of previous month
//but for here I will set it to 0
    startingBalance = 0,

//Group by dates and get net of Withdrawal/Deposits for each date
    group = Table.Group(#"Replaced Value","Tran Date",{
        {"Net Change", each List.Sum([Deposit]) - List.Sum([Withdrawal]), Currency.Type}
    }),

//create list of all dates in the statement
    monthDates = Table.FromList(
            let 
        mnthStart = Date.StartOfMonth(List.Min(#"Replaced Value"[Tran Date])),
        mnthEnd = Date.EndOfMonth(List.Max(#"Replaced Value"[Tran Date]))
            in List.Dates(mnthStart,Duration.Days(mnthEnd - mnthStart)+1, #duration(1,0,0,0)),
                Splitter.SplitByNothing(),{"Month"}),

//Merge with the dates from the grouped table
// and sort to ensure proper date order
    allDates = Table.Join(group,"Tran Date", monthDates,"Month",JoinKind.RightOuter),
    #"Sorted Rows" = Table.Sort(allDates,{{"Month", Order.Ascending}}),

//Remove partial date column 
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Tran Date"}),

//Add running total column = end of day balance
bl = List.Buffer(List.ReplaceValue(#"Removed Columns"[Net Change],null,0,Replacer.ReplaceValue)),
RT = List.Generate(
        ()=>[rt=bl{0}, idx=0],
        each [idx] < List.Count(bl),
        each [rt = [rt] + bl{[idx]+1}, idx = [idx]+1],
        each [rt]
),
rtTable = Table.FromColumns(
            {#"Removed Columns"[Month],RT},
            {"Date", "EOD Bal"}),

//Convert date to month name
//Add year to differentiate by year
    #"Added Custom1" = Table.AddColumn(rtTable, "MonthName", each Date.MonthName([Date]) & Date.ToText([Date]," yyyy")),

//Group by monthName and aggregate by average
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"MonthName"}, {{"Average Daily", each List.Average([EOD Bal]), type number}}),

//set data types
    typed = Table.TransformColumnTypes(#"Grouped Rows",{{"MonthName", Text.Type},{"Average Daily", Currency.Type}})
in
    typed

原始数据

结果