将INDEX MATCH公式更改为EXCEL中的数组公式-Java 学习之路

我正在尝试创建一个 INDEX MATCH 公式，该公式搜索包含 jpegs 列表的列，并返回以特定字符串开头的所有 jpegs 并将它们转换为超链接 .

目前我的公式只返回第一个实例，但我希望它返回所有匹配项 .

jpegs列表位于工作簿的 Sheet 2 的 column F (F1:F1000) 中 . 搜索中使用的字符串是 column A, sheet 1 中的产品SKU .

这是我在 C2 sheet 1 输入的工作非阵列版本并填写：

=IFERROR(
    HYPERLINK(
         CONCATENATE(sku_url,INDEX(Sheet2!$F$1:$F$1000,
             MATCH(A2&"*",Sheet2!$F$1:$F$1000,0),1))),
    "image not found")

这适用于 column C ，但如何将此公式填充到右侧，以便 column D 包含每个sku的第二个图像， E 包含第三个，依此类推 . 我计划每个SKU的图像不超过六张，因此我已将 columns C 至 H 分配给产品图片网址 . 如果SKU没有六个图像，则这些额外列应为空 .

2 回答

1

假设使用Excel 2010或更高版本：

=IF(COLUMNS($A:A)>COUNTIF(Sheet2!$F$1:$F$1000,$A2&"*"),"",IFERROR(HYPERLINK(CONCATENATE(sku_url,INDEX(Sheet2!$F:$F,AGGREGATE(15,6,ROW(Sheet2!$F$1:$F$1000)/(LEFT(Sheet2!$F$1:$F$1000,LEN($A2))=$A2),COLUMNS($A:A))))),"imagenotfound"))

作为解释的方式，最初的 IF 条款，即：

IF(COLUMNS($A:A)>COUNTIF(Sheet2!$F$1:$F$1000,$A2&"*"),""

很简单：

COUNTIF(Sheet2!$F$1:$F$1000,$A2&"*")

只需计算与该条件匹配的总行数，从而：

COLUMNS($A:A)

等于1，并在连续复制到右边时变为：

COLUMNS($A:B)

（等于2）

COLUMNS($A:C)

（等于3）

等等，本节将等同于连续列：

IF(1>COUNTIF(Sheet2!$F$1:$F$1000,$A2&"*"),""

IF(2>COUNTIF(Sheet2!$F$1:$F$1000,$A2&"*"),""

IF(3>COUNTIF(Sheet2!$F$1:$F$1000,$A2&"*"),""

等等，因此在初始条款为 TRUE 的单元格中将返回空白 .

唯一需要注意的另一个条款是在满足此条件时生成连续行数的数组 . 不幸的是，由于技术原因，上述 COUNTIF 声明在我们的 AGGREGATE 构造中无法使用 .

幸运的是，我们可以使用 LEFT 的另一个设置重现 COUNTIF 语句的结果 .

暂时从F1：F1000到F1：F10减少有问题的范围以帮助解释，这部分：

LEFT(Sheet2!$F$1:$F$10,LEN($A2))=$A2

将简单地为F1：F10中的每个条目生成一个布尔 TRUE / FALSE 返回数组的结果 . 例如，我们可能会：

{FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

然后，当我们用这个布尔数组回报每个条目的等效行数时，即执行：

ROW(Sheet2!$F$1:$F$10)/(LEFT(Sheet2!$F$1:$F$10,LEN($A2))=$A2)

我们有：

{1;2;3;4;5;6;7;8;9;10}/{FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

并且由于，当通过任何合适的数学运算（其中除法为1）强制执行时，布尔值 TRUE / FALSE 值被强制转换为它们的数值等价物（ TRUE = 1， FALSE = 0），以上变为：

{#DIV/0!;2;#DIV/0!;4;5;#DIV/0!;7;#DIV/0!;9;#DIV/0!}

从 AGGREGATE 开始，第一个参数为15被指示在数组中找到最小值，并且第二个参数为6被指示忽略该数组中的任何错误值，剩下的就是设置第四个参数 . function，k，确定是否应返回第一个最小值，第二个最小值等 .

再次，通过使用：

COLUMNS($A:A)

对于这个参数，我们知道将生成一系列连续的整数（1,2,3等）复制到右边，因此我们保证将所需的行号返回给每个版本的公式 .

问候

回复于 2024-04-24T14:06:06+08:00
0
将AGGREGATE¹ function与SMALL子功能（ 15 ）一起使用 . 向右填充时，调整k参数以增加COLUMN .

B2中的标准（非阵列）公式是，
```
=IFERROR(
    HYPERLINK(
        CONCATENATE(sku_url, INDEX(Sheet2!$F:$F,
            AGGREGATE(15, 6, ROW($1:$999)/(LEFT(Sheet2!$F$1:$F$999, LEN($A2))=$A2), COLUMN(A:A))))),
    "image not found")
```
必要时填写 .

¹AGGREGATE功能是在Excel 2010中引入的 . 它在早期版本中不可用 .
回复于 2024-04-24T14:06:06+08:00

将INDEX MATCH公式更改为EXCEL中的数组公式

2 回答

相关问题