☰

无需使用脚本即可动态更新 Excel 列表

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013 microsoft-excel-2016 google-spreadsheets
无需使用脚本即可动态更新 Excel 列表

我有点困惑，需要一些帮助。

我正在尝试根据其他单元格中的条件动态更新 Excel 中的列表。类似于（如果 A1 和 A2 中的单元格值与 A3 和 A4 中的单元格值匹配，则返回 A5）。我理想情况下希望只使用公式而不使用脚本，但不确定是否可行。

场景如下。Gina (A3) 有一个购物篮，里面有 6 件物品 (B3:B8)：柠檬、牙膏、布朗尼、发刷、葡萄、三明治 –

吉娜的篮子

吉娜的篮子

吉娜和她的朋友们喜欢互相交易东西，并且每次交易时都会做记录 (D3:F5) –

交易记录

交易记录

我想要做的是弄清楚如何在吉娜每次记录新的交易时更新她的购物篮。

此外，它还需要允许交易同一物品两次（每次交易不同的物品），只考虑该物品最近的交易记录——在本例中，她在篮子里收到了一个橙子——

在这种情况下，她在篮子里收到的是橙子，而不是纸杯蛋糕

在这种情况下，她在篮子里收到的是橙子，而不是纸杯蛋糕

我尝试了 IF、AND、MATCH 和 INDEX 的各种组合，其中第二列重复，或者陷入循环依赖，但我无法找到问题的根源。

以下是工作表的链接：https://docs.google.com/spreadsheets/d/17J-lX2V1Zs-K7WmsfruqcEJtmElM5rCQTeCLFh8FX1U/edit?usp=sharing

如果有人对如何解决这个问题有任何想法，我将不胜感激！

谢谢吉米

答案1

：： 警告 ：：

由于 OP 不需要基于 VBA MACRO 的解决方案，因此在这种情况下，我发现的可能性是使用一些 ARRAY (CSE) 公式和条件格式。也许其他人可能会建议更好的一个。

在此处输入图片描述

怎么运行的：

  • 交易物品和杂货商清单均为表格。

一旦您从表中添加或删除名称或项目，Excel 将自动更新相关数据。

  • 以外购物篮&交易物品，你需要创建杂货商清单也。

  • 一旦有人购买任何物品，你需要更新杂货店的餐桌。

查看旧物品列表，被GINA和其他人购买了。

  • 在单元格 P17 中创建一个下拉列表，目前只有 3 个名称，您可以添加更多。

如果您拥有大量买家，那么您可以使用任一公式来获取唯一列表。

  • 单元格 P28 中的数组（CSE）公式：

     {=IFERROR(INDEX($U$17:$U$30, MATCH(0, INDEX(COUNTIF($P$27:P27, $U$17:$U$30)+($U$17:$U$30=""), ), 0)), "")}

  • 对于更高版本，你可以在 P28 中使用这个：

    =UNIQUE(FILTER(U17:U30,U175:U30<>""))

然后在单元格 P17 中使用唯一使用买家列表进行下拉。

  • 现在在单元格 Q17 中使用此数组（CSE）公式：

    {=IFERROR(INDEX($T$17:$T$30, SMALL(IF(COUNTIF($P$17, $U$17:$U$30)*COUNTIF($U$17:$U$30,"<>"), ROW($T$17:$U$30)-MIN(ROW($T$17:$U$30))+1), ROW(A1)), COLUMN(A1)),"")}

  • 单元格 R17 中的数组（CSE）公式：

    {=IFERROR(INDEX($N$17:$N$22, SMALL(IF(COUNTIF($P$17, $L$17:$L$22)*COUNTIF($N$17:$N$22,"<>"), ROW($L$17:$N$22)-MIN(ROW($L$17:$N$22))+1), ROW(A1)), COLUMN(A1)),"")}

  • 在 Q17：Q25 应用条件格式，使用下面显示的公式作为新规则：

    =COUNTIF($N$17:$N$22,Q17)=0

注意：

  • 使用以下方式完成数组（CSE）公式Ctrl+Shift+Enter＆填写所需方向。

  • 新物品列表显示已交换（交易）的物品。

  • 旧清单中有红色的商品，是未交换的商品，您可以发现 GINA 购买了五件商品（查看 U 列中的客户清单），但只交换了两件，分别是鳄梨和雪茄。

  • 继续更改单元格 P17 中的名称，您就会得到结果。

  • 根据需要调整公式中的单元格引用。

相关内容