问题描述
我能够找到下面链接的问题的答案，但是现在我有兴趣添加另一个调整项.
I was able to find an answer to the question linked below, but now I am interested in adding one more tweak.
我有一个时间范围内所有订单的列表，并且正在尝试计算每个客户的第一订单和第二订单之间的差额.我在另一个标签上列出了唯一的客户ID，并列出了每个客户的首次订购日期.我可以使用和AGGREGATE公式找到第一笔订单日期和第二笔订单日期之间的差异，但是现在我也想过滤掉订单总额"为0的所有订单.
I had a list of all orders placed in a time range and was am trying to calculate the difference between each customer's first and second order. I had a list of unique customer IDs on another tab and the first order date for each. I was able to find the difference between first and second order date with the and AGGREGATE formula, but now I would like to also filter out all orders where the "Order Total" is 0.
=AGGREGATE(15,6,Sheet1!C1:C4/(Sheet1!A1:A4 = 153950),2)
原始问题:
推荐答案
尝试
=AGGREGATE(15, 6, Sheet1!C1:C4/((Sheet1!A1:A4=153950)*(Sheet1!B1:B4<>0)), 2)
这里的想法是您要创建一个简单的数学公式，该公式将#DIV/0！在AGGREGATE忽略任何错误时会忽略任何错误，从而将这些错误从考虑中丢弃. False(出于所有意图和目的)为零，任何乘以零的值均为零；除以零的值是#DIV/0!.除以1的任何数字(例如true x true)保持不变.
The idea here is that you want to create a simple maths formula that will #DIV/0! on anything you don't want while AGGREGATE ignores errors thus discarding those from consideration. False (for all intents and purposes) is zero and anything multiplied by zero is zero; anything divided by zero is #DIV/0!. Any number divided by 1 (e.g. true x true) remains unchanged.
这篇关于使用AGGREGATE函数过滤掉$ 0的所有订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！