Excel帮助中的索引组合-匹配和摘要? [英] Excel help on combination of Index - match and sumifs?

问题描述

我有这三个表，如下图所示: SalesOrderTable，OrderAdjustmentTable和TotalSalesTable.

I have these three tables as shown in this image below: SalesOrderTable, OrderAdjustmentTable, and TotalSalesTable.

SalesOrderTable

SalesOrderTable中的每个订单号代表同一张发票中产品的订单号.如果发票是为一种以上的产品制成的，则有A，B或C，但如果仅是一种产品的发票则没有. 例如:订单1703有两个产品，因此每个订单号的末尾都有A和B.订单1704仅具有Apple订单，因此末尾没有任何字母.

Each order number in the SalesOrderTable represent an order number for a product from the same invoice. It has A,B, or C if an invoice is made for more than one product, but not if it is only made for one product. E.g.: Order 1703 has two products, so it has A and B at the end of each order number. Order 1704 has an order for Apple only, so it doesn’t have any letter at its end.

OrderAdjustmentTable

每个订单号如有任何调整，请在调整栏中手动输入.

Should there be any adjustments for each order number, they are inputted manually in the adjustment columns.

TotalSalesTable

在此表中，所有总销售额和同一订单号的调整额总计.因此，对1705的订购是1705A，1705B，1705C的组合.

In this table, all amount of total sales and adjustments for the same order number are totaled. So order for 1705 are combinations of 1705A, 1705B, 1705C.

这是我当前的公式，所有公式都会产生错误消息或计算不正确.

Here are my current formulas, all of them produce error messages or don’t calculate correctly.

订单总额栏:

=SUMIF(SalesOrderTable[Order Number], LEFT(G4,LEN(SalesOrderTable[Order Number])-1),SalesOrderTable[Order Sales Amount])

订单总额调整列:

=sumifs(OrderAdjustmentTable[#All],OrderAdjustmentTable[Order Number],MATCH(B19,LEFT(SalesOrderTable[Order Number],LEN(SalesOrderTable[Order Number])-1), 0), "*Adjustment",OrderAdjustmentTable[#All])

我知道这可能与match和sumifs有关，但是我做的所有公式都导致出现错误消息. 任何人都可以帮助我解决我在上述公式中遇到的问题，以及如何解决这些问题?我在这里尽我所能，将不胜感激.非常感谢！

I know it may have something to do with match and sumifs, but all the formulas I made led to error messages. Anybody can help me with what I did wrong with these formulas above, and how do I fix them? I am at my wits end here and will appreciate any advice given. Thanks a lot!

我想要实现的是填充:

TotalSalesTable中的订单总金额列，所有订单均具有相同的订单号，无论其ABC是多少.因此，1705的总金额将是1705A，1705B，1705C的订单金额之和.

The order total amount column in TotalSalesTable with all orders that have the same order number, irrespective of their ABC. So Total amount for 1705 will be the sum total of order amount for 1705A, 1705B, 1705C.

对于订单总计调整，我希望从OrderAdjustmentTable获得TotalSalesTable中订单号列的所有调整值，而不论其ABC是多少.因此，TotalSalesTable中1705的总调整将为1705B和1705C.

For the order total adjustment, I want to have all the adjustments value for the order number column in TotalSalesTable from OrderAdjustmentTable, irrespective of their ABC. So total adjustments for 1705 in TotalSalesTable will be 1705B and 1705C.

推荐答案

只要不存在带有字母和不带字母的订单(例如，具有记录1703、1703A和1703B的SalesOrderTable)，以下公式应该可以在单元格C19中工作，您可以从中复制下来:

As long as there are no orders that overlap with and without letters (for instance, the SalesOrderTable having records for 1703, 1703A, and 1703B), the following formula should work in cell C19, from where you can copy it down:

订单总金额:

=SUMIFS(SalesOrderTable[Order Sales Amount],SalesOrderTable[Order Number],$B19)+SUMIFS(SalesOrderTable[Order Sales Amount],SalesOrderTable[Order Number],$B19&"*")

此公式将精确匹配和近似匹配相加在一起，但是就像我说的那样，如果同时有和缺少字母的任何订单都可能会重复/重复计算.您还应该能够复制订单总额调整项"列的公式.

This formula adds together the exact and approximate matches, but like I said if any orders both have and are lacking a letter something will likely get duplicated/double counted. You should also be able to replicate the formula for the Order Total Adjustment Column.

这篇关于Excel帮助中的索引组合-匹配和摘要?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！