SUMIFS 和 SUMPRODUCT 一起使用

发布于 2025-01-18 21:53:20 字数 1352 浏览 2 评论 0原文

我正在开展一个项目，其中多个账户和多种不同货币的支出被合并为单一货币现金流量表，该报表每月将支出按类别汇总。

该表将每月更新货币汇率和现有账户的新支出。新帐户也会随着时间的推移而添加。

所有帐户表将放置在选项卡>>>之间。 <<<<并将继续采用完全相同的布局。

我已经成功地解决了这个问题，方法是将所有支出转换为通用货币（英镑），以防选项卡之间的所有帐户>>> <<<<在 H 列中作为第一步，使用

SUM($D5:$E5)/INDEX(GBPEUR,MATCH(DATE(YEAR($B5),MONTH($B5),DAY($B5)-1),Date_Currency ,1))

，然后作为第二步，对选项卡 >>> 之间的每个帐户使用以下内容将每月支出汇总为类别<<<<

SUMIFS('2 (£)'!$H$5:$H$1000,'2 (£)'!$G$5:$G$1000,$B7,'2 (£)'!$B$5:$ B$1000,">"&EOMONTH(C$4,-1),'2 (£)'!$B$5:$B$1000,"<="&C$4)

这是一个不完美的解决方案，因为 1) 它不是未来的证明（如果添加另一个帐户，则代码需要更新），2）公式冗长且难以操作，3）电子表格中将添加大量账户和支出，并且电子表格的大小会膨胀，因此请尽量避免添加不必要的步骤。

我想知道是否有一个更优雅的解决方案来解决该问题，也许可以在一个步骤中同时使用 SUMIFS 和 SUMPRODUCT ？

目标是

对于选项卡>>>之间的所有工作表（帐户） <<<<
该公式应使用支出之日适当的货币汇率将所有支出转换为单一货币 (£)
该公式应根据单元格 B2 中突出显示的货币自动选择适当的汇率（对于每张表格 (帐户）在选项卡>>>>>之间<<<<
该公式应在单元格 C7:J91 的“现金流”选项卡上一步将每个类别和每月的所有转换支出相加。

Excel 电子表格可在此链接。货币汇率数据位于“汇率”选项卡上类别位于单元格 C7:C91 中的“现金流”选项卡上

原文

I am working on a project where expenditure across multiple accounts and multiple different currencies are consolidated into a single currency cash flow statement that aggregates the expenditure into categories on a monthly basis.

The table will be updated with currency exchange rates and new expenditures on existing accounts on a monthly basis. New accounts will be added as well overtime.

All the account sheets will be placed between tabs >>> <<< and will continue to have the exact same layout.

I have managed to solve the problem by converting all expenditures into a common currency (£) in case of all accounts between tabs >>> <<< in column H as a first step using

SUM($D5:$E5)/INDEX(GBPEUR,MATCH(DATE(YEAR($B5),MONTH($B5),DAY($B5)-1),Date_Currency,1))

and then aggregating the expenditure into categories per month as a second step using the below for each of the accounts between tabs >>> <<<

SUMIFS('2 (£)'!$H$5:$H$1000,'2 (£)'!$G$5:$G$1000,$B7,'2 (£)'!$B$5:$B$1000,">"&EOMONTH(C$4,-1),'2 (£)'!$B$5:$B$1000,"<="&C$4)

This is an imperfect solution though because 1) it is not future proof (if another account is added then the code needs to be updated), 2) the formula is lengthy and unwieldy, 3) there will be a lot of accounts and expenditures added into the spreadsheet and it will balloon in size, so try to avoid unnecessary steps being added.

I wonder whether there is a more elegant solution to the problem perhaps using SUMIFS and SUMPRODUCT together in a single step?

The objective is

For all sheets (accounts) between tabs >>> <<<
The formula shall convert to a single currency (£) all expenditure using the appropriate currency exchange rate on the date of the expenditure
The formula shall automatically choose the appropriate exchange rate based on the currency highlighted in cell B2 in case of each of the sheets (accounts) between tabs >>> <<<
The formula shall sum all the converted expenditures per categories and per month in one single step on "Cashflow" tab in cells C7:J91.

The excel spreadsheet is available on this link.
Currency exchange rate data is on the "Exchange Rate" tab
Categories are on the "Cashflow" tab in cells C7:C91

分享到QQ

分享到微博