Excel子集列使用公式数组 [英] Excel subset column to an array using formula

问题描述

我需要将帐号转换成列选择中的1表示的数组。预期结果为 - {FD_002_17，FD_004_17}。我打算在名称范围内使用这个。

I need to get the Account Numbers into an array which is indicated as 1 on the column select. Results expected is - {FD_002_17,FD_004_17}. I am planning to use this in a Name Range. Table of interest to subset

I tried using

=INDEX(B2:B6,MATCH(1,A2:A6),1)

But this fails as Match does not return an array.

解决方案

Using the post (https://stackoverflow.com/a/6755513/4050510) in the SO question that Hugs referred to i came up with the following formula for your need.

Its a array formula that you enter into your first cell, and then fill it downwards using the little handle in the corner of the selected cell.

=IFERROR(INDEX($B$2:$B$6;SMALL(IF($A$2:$A$6=1;ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1;"");ROW(A1)));"")

这篇关于Excel子集列使用公式数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！