Question

就背景而言,我的工作要求我提供大量模拟数据。象100万几行一样,阵列方案似乎是扩大这些活动的最佳选择。问题。

I have 2 arrays

see here

The first is a list of Accounts in one column and the Rep that owns the account in another. My second array is just random array of Reps. In the second array I want to choose one of the accounts that each rep owns - based on the assignments in the first array. These arrays are different sizes with the second being much larger given its just a random selection of reps with every rep being repeated multiple times.

两个阵列的所有栏目都来自其他动态阵列功能,使用编号来参照和建造阵列。

This is the formula I originally came up with. In essence i m trying to produce a filtered array of accounts that the lookup rep owns and then pick a random account from that filtered array.

=LET(
acctlist,$L$2#,
ownerlist,$M$2#,
replookuparray,Q2#,
filterbyowner,FILTER(acctlist,ownerlist=replookuparray),
INDEX(filterbyowner,RANDBETWEEN(1,ROWS(filterbyowner))))

我从这一公式中看到了一个价值错误。如果我从Replookuparray的Q2#中删除这一编号,就会产生正确的结果,但我希望它能为整个第2阵列带来结果,因此我不必复制整个系列的过去(所有这些阵列的产序号都动荡不定,因此,它们经常发生变化,而且有一个非动力阵列使档案更加有用)。

是否有办法使这一逻辑适用于第二阵列,以便我能够继续使用动态阵列?

Answer 1

使用ISNUMBER(MATCH)

FILTER(acctlist,ISNUMBER(MATCH(ownerlist,replookuparray,0))

总计:

=LET(
acctlist,$L$2#,
ownerlist,$M$2#,
replookuparray,Q2#,
filterbyowner,FILTER(acctlist,ISNUMBER(MATCH(ownerlist,replookuparray,0)),
INDEX(filterbyowner,RANDBETWEEN(1,ROWS(filterbyowner))))

Answer 2

这似乎行之有效。

=BYROW(Q2#,LAMBDA(replookuparray,
LET(
acctlist,$L$2#,
ownerlist,$M$2#,
filterbyowner,FILTER(acctlist,ISNUMBER(MATCH(ownerlist,replookuparray,0))),
INDEX(filterbyowner,RANDBETWEEN(1,ROWS(filterbyowner))))))

友情链接