excel - 使用集合/字典或类进行乘法查找
问题描述
我的问题如下,我有一个试算表(运行了多个月的帐户列表,其期末余额以各种货币表示)和第二张表显示了这些月的美元汇率转换率。第一张纸可能有数十万行,而第二张纸大约有 150-200 行。这两张纸的快速摘录如下所示:
第一张:
A - Month; B - Currency; C - ending balance; Col D (USD); Col E (USD per Dec-18 rate)
Dec-18 EUR 12000
Dec-18 GBP 55000
Dec-18 SEK 1500000
Mar-19 EUR 60000
Mar-19 GBP 1700
Mar-19 SEK 500
第二张:
A - Month; B - Currency; C - fxrate
Dec-18 EUR 0.9
Dec-18 GBP 0.7
Dec-18 SEK 9.3
Mar-19 EUR 0.85
Mar-19 GBP 0.75
Mar-19 SEK 9.1
我的目标是:
- 通过将 Col C 与来自第二张表的 Col C 的 fxrate 值相乘,从第一张表中填写 Col D 并为匹配的月份执行此操作。因此,在 D1 中,我想找出 12 月 18 日的欧元汇率为 (0.9),然后将 12000 乘以该值。但是对于 D4,我想知道 3 月 19 日欧元的汇率(0.85)并将 60000 乘以该值
- 通过预先选择我想从哪个月份开始使用外汇汇率,从第一张表格中填写 COl E(无论第一张表格中的 Col A 中说明了哪个月份)。因此,例如,我可以分配一些变量“Dec-18”,然后仅使用 Dec-18 的 fxrates 来获取第一张表上的整个项目列表。所以这次在 E2 中,我将 12000 乘以 0.9(与上面相同),但是对于 E4,我将 6000 乘以 0.9(不是 0.85)。
我看过关于常规 vlookup、字典和课程的帖子,但我无法回答这个问题。任何人都可以帮助并至少指导我找到我可以尝试增强的部分解决方案吗?
解决方案
你可以做一个技巧并使用SUMIFS
. 因为我们可以假设 Sheet2 中的每个Month
-Currency
组合都是唯一的,它将返回实际fxrate
值,因为只有一个唯一条目的总和是值本身:
这个公式将返回fxrate
=SUMIFS(Sheet2!C:C,Sheet2!A:A,Sheet1!A:A,Sheet2!B:B,Sheet1!B:B)
你只需要将它与 C 列相乘
=SUMIFS(Sheet2!C:C,Sheet2!A:A,Sheet1!A:A,Sheet2!B:B,Sheet1!B:B)*C:C
得到你的结果(使用这个公式 D 列)。
对于 E 列,它几乎是相同的公式,您只需在某处写下您的固定月份值并在SUMIFS
标准中使用此单元格而不是 A 列。
=SUMIFS(Sheet2!C:C,Sheet2!A:A,$I$2,Sheet2!B:B,Sheet1!B:B)*C:C
我使用了单元格$I$2
(见下例中的红色部分):
推荐阅读
- postman - 上传 pdf - 使用 newman 运行我的 POSTMAN 测试时未通过测试
- python - 如何使通过多处理通信运行的功能
- pandas - dask 中数组的笛卡尔积
- python - 如何在 csv 文件中写入和附加数据并将其存储在列表中
- php - 不能使用'对象作为类名,因为它是保留的 Cake 2.2.x
- javascript - Odoo pos ReceiptScreenWidget _super() 功能无法正常工作
- ios - 使用 UIImage.renderingMode() 创建图像
- ios - 无法在 python 中使用 kivy 在 iOS 上写入文件
- ios - 所有 UITextField 和 UITextView 中缺少键盘语言
- visual-studio-code - Visual Studio Code 可以使用 GDB 附加到没有“程序”属性的进程吗?