从动态数组中提取Excel单元格值

Question

我需要从Excel中的动态数组中提取一个值。我只希望从表中的"FRAM_abundance_harvest“列中高亮显示橙色的数字。

​

​

下面的filter函数获取整行数据，但我只希望返回值(356,034)。

`=FILTER(FRAM_abundance_harvest,((FRAM_abundance_harvest[Source]="Ocean") *
(FRAM_abundance_harvest[Abundance_Harvest]="Abundance") *
(FRAM_abundance_harvest[Mark status]="Marked") *
(FRAM_abundance_harvest[Run type]="Late") *
(FRAM_abundance_harvest[Production type]="Hatchery")))`

Answer 1

将整个公式放入INDEX()函数中，如下图所示，

​

​

=INDEX(FILTER(FRAM_abundance_harvest,
((FRAM_abundance_harvest[Source]="Ocean") *
(FRAM_abundance_harvest[Abundance_Harvest]="Abundance") *
(FRAM_abundance_harvest[Mark Status]="Marked") *
(FRAM_abundance_harvest[Run Type]="Late") *
(FRAM_abundance_harvest[Production Type]="Hatchery")),""),,6)

或者，请注意，下面的函数也需要O365，下降()函数仍然在内部人的中

​

​

=DROP(FILTER(FRAM_abundance_harvest,
((FRAM_abundance_harvest[Source]="Ocean") *
(FRAM_abundance_harvest[Abundance_Harvest]="Abundance") *
(FRAM_abundance_harvest[Mark Status]="Marked") *
(FRAM_abundance_harvest[Run Type]="Late") *
(FRAM_abundance_harvest[Production Type]="Hatchery")),""),,5)