英文:
Excel Simplify multiple lines of date ranges for matching criteria
问题
我试图使用Excel返回最高和最低日期选项，以便可以返回真正的生效/到期日期而不是多个行。范例：
| 费率 | 州 | 生效日期 | 到期日期 |
|---|---|---|---|
| $5.00 | AZ | 2022/01/01 | 2022/01/31 |
| $5.00 | AZ | 2022/02/01 | 2022/02/30 |
| $6.00 | AZ | 2022/03/01 | 2022/03/31 |
| $6.00 | WI | 2022/01/01 | 2022/01/31 |
| $6.00 | WI | 2022/02/01 | 2022/02/30 |
在上面的示例中，AZ的$5.00费率在2022/01/01-2022/02/30期间保持不变，然后变为$6.00，所以我想要一个简化的返回结果如下：
| 费率 | 州 | 生效日期 | 到期日期 |
|---|---|---|---|
| $5.00 | AZ | 2022/01/01 | 2022/02/30 |
| $6.00 | AZ | 2022/03/01 | 2022/03/31 |
| $6.00 | WI | 2022/01/01 | 2022/02/30 |
我尝试使用XLOOKUP，但是当XLOOKUP找到多个匹配项时，它只返回第一个选项。我一直在试图找到一种方法使其返回最小或最大选项，但这行不通。
我还尝试了使用INDEX/MATCH并取得了类似的结果。
在这两种情况下，计划是创建重复的行，然后在另一端删除多余的行。
英文:
I'm trying to use Excel to return the highest & lowest date option so I can return the true effective/expiration dates without multiple lines. Example of ranges/rates:
| Rate | State. | Effective | Expired |
|---|---|---|---|
| $5.00 | AZ | 01/01/2022 | 1/31/2022 |
| $5.00 | AZ | 02/01/2022 | 2/30/2022 |
| $6.00 | AZ | 03/01/2022 | 3/31/2022 |
| $6.00 | WI | 01/01/2022 | 01/31/2022 |
| $6.00 | WI | 02/01/2022 | 02/30/2022 |
In the example above, AZ's rate of $5.00 stays the same from 01/01/2022-02/30/2022, then changes to $6.00, so i'd like a simplified return to look like this:
| Rate | State | Effective | Expired |
|---|---|---|---|
| $5.00 | AZ | 01/01/2022 | 02/30/2022 |
| $6.00 | AZ | 03/01/2022 | 03/31/2022 |
| $6.00 | WI | 01/01/2022 | 02/30/2022 |
I tried using an XLOOKUP, but when the XLOOKUP finds multiple matches, it only returns the first option. I've been trying to find a way to make it return the MIN or MAX option and this doesn't work.
I also tried an INDEX/MATCH with similar results.
In both situations the plan was to create duplicate lines then delete the excess ones on the other end.
答案1
得分: 1
[
]1
F2 中的公式是
=LET(rates,A2:A6,states,B2:B6,effdates,C2:C6,expdates,D2:D6,dat,UNIQUE(CHOOSE({1,2},rates,states)),one,INDEX(dat,,1),two,INDEX(dat,,2),CHOOSE({1,1,2,3},dat,MINIFS(effdates,states,two,rates,one),MAXIFS(expdates,states,two,rates,one)))
这对于你的有限数据集产生了期望的结果，但它在很大程度上取决于你的数据内容和布局，例如，如果你的样本的最后一行是关于 4 月 1 日到 4 月 30 日的 AZ 的 $5 费率，那么总结的结果会产生 AZ 费率的 4 月 30 日到期日，尽管三月份没有选项数据！
英文:
The formula in F2 is
=LET(rates,A2:A6,states,B2:B6,effdates,C2:C6,expdates,D2:D6,dat,UNIQUE(CHOOSE({1,2},rates,states)),one,INDEX(dat,,1),two,INDEX(dat,,2),CHOOSE({1,1,2,3},dat,MINIFS(effdates,states,two,rates,one),MAXIFS(expdates,states,two,rates,one)))
This produces the desired results for your limited dataset, but is very much dependent on the content and layout of your data, e.g. if the last line of your sample were for a $5 rate for AZ from 1st to 30th April then the summary result would yield an expiry date of April 30th for the $5 AZ rate, despite there being no option data for March(!).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论