如何在 Excel 中找到符合我的条件的平均值？

Question

假设这是我的 2020 年 7 月银行对账单：

序号	陈达	退出	订金	余额
1	2020 年 1 月 7 日		100	100
2	2020 年 2 月 7 日	5		95
3	2020 年 3 月 7 日		500	595
4	2020 年 6 月 7 日	50		545
5	2020 年 6 月 7 日		8000	8545
6	2020 年 6 月 7 日	3000		5545
7	2020 年 7 月 31 日	5		5540
8	2020 年 7 月 31 日		10000	15540

现在我想知道我的每月平均余额。

为了找到它，我必须添加所有日终余额然后除以天数。但是，银行对账单没有每日余额，只列出交易及其各自的日期。对账单中没有提到没有发生交易的日期。

现在，假设我有一张银行对账单的 Excel 表。我如何找到每月平均余额？我读了一些关于 AVERAGEIF 的内容，但我不确定它对我的情况是否有用。

---

这是我求平均值的方法（但这是一个漫长的过程）：

1. 我查看当月的银行对账单，然后找出每天的最后一笔交易，这将有助于我找到每日的余额。

2. 现在我将打开一个新的 Excel 表并输入每日余额，如下所示：

   日期	日终余额
   2020 年 1 月 7 日	100
   2020 年 2 月 7 日	95
   2020 年 3 月 7 日	595
   2020 年 4 月 7 日	595
   2020 年 5 月 7 日	595
   2020 年 6 月 7 日	5545
   2020 年 7 月 7 日	5545
   2020 年 8 月 7 日	5545
   2020 年 9 月 7 日	5545
   2020 年 10 月 7 日	5545
   2020 年 7 月 11 日	5545
   2020 年 7 月 12 日	5545
   2020 年 7 月 13 日	5545
   2020 年 7 月 14 日	5545
   2020 年 7 月 15 日	5545
   2020 年 7 月 16 日	5545
   2020 年 7 月 17 日	5545
   2020 年 7 月 18 日	5545
   2020 年 7 月 19 日	5545
   2020 年 7 月 20 日	5545
   2020 年 7 月 21 日	5545
   2020 年 7 月 22 日	5545
   2020 年 7 月 23 日	5545
   2020 年 7 月 24 日	5545
   2020 年 7 月 25 日	5545
   2020 年 7 月 26 日	5545
   2020 年 7 月 27 日	5545
   2020 年 7 月 28 日	5545
   2020 年 7 月 29 日	5545
   2020 年 7 月 30 日	5545
   2020 年 7 月 31 日	15540

3. 最后，我将使用 AVERAGE 函数。因此，7 月份的平均值是 5036.935484

这是一个相当繁琐的过程。有没有什么方法可以让它变得简单一些？

Answer 1

你的方法就是需要做的。

一种更简单的方法是使用 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

原始数据

结果

Answer 2

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

• 确保日期 B 列中的单元格格式正确为日期。
• 在单元格 H2:H13 中写入每个月的第一个日期（1/1/2020、1/2/2020... 1/12/2020）
• 将单元格 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 个单元格，但范围为相应年份。