如何获取另一个数组中一个数组的列号中的每个值? [英] How to get for each value in one array column number in another array?

问题描述

请查看示例工作表. "sheet1"是一个逐渐填充的表.在"A"列中，每个实体都有某种ID.这些标识符有时会重复.结果，我只想知道每个ID的外观编号.

Please look the example sheet. The 'sheet1' is a gradually filled table. In column "A" some kind of ID for each entity. These identifiers are sometimes repeated. In the result I just want to know the appearance number of each ID.

为解决这个问题，我创建了"sheet2".在"sheet2"列的"A"列中，列出"sheet1"中的唯一值.然后，使用单元格"sheet2！B2"中的庞大公式，我从"sheet1"中获得了每个唯一值的所有行号，这些行号由列分隔.最后，我想从"sheet2"获取每个行号的"sheet1"列号.

To solve this I created 'sheet2'. in 'sheet2' column 'A' list of unique values from 'sheet1'. Then with huge formula in cell 'sheet2!B2' I get all row numbers for each unique value from 'sheet1' separated by columns. And finally I want to get in 'sheet1' column numbers for each rownumber from 'sheet2'.

我想使用匹配"命令，但是没有"arrayformula"就可以正常使用，就像您在"sheet1"列E中看到的一样.

I want to use 'match' comand, but it perfectly works without 'arrayformula' like you can see in 'sheet1' column E.

=IF(ISBLANK(A2);;MATCH(B2;INDIRECT("'sheet2'!B"&C2&":Z"&C2)))

当我尝试添加"arayformula"时，我不明白"sheet1"列D出了什么问题

And i don't understand whats going wrong in 'sheet1' column D, when I try to add 'arayformula'

=ARRAYFORMULA(IF(ISBLANK(A2:A);;MATCH(B2:B;INDIRECT("'sheet2'!B"&C2:C&":Z"&C2:C))))

我阅读了许多有关这些命令的文章，但这些文章并没有像我想要的那样协同工作.必须有其他方法.但是我的想象力结束了.请帮忙.

I read many posts about these commands doesn't work together like I want. there must be some another way. But my imagination is over. Please help.

预期结果:

value 1 |   1   |(first appearance of 'value 1')

value 2 |   1   |(first appearance of 'value 2')

value 1 |   2   |(second appearance of 'value 1')

value 3 |   1   |(first appearance of 'value 3')

value 4 |   1   |(first appearance of 'value 4')

value 5 |   1   |(first appearance of 'value 5')

value 3 |   2   |(second appearance of 'value 3')

value 3 |   3   |(third appearance of 'value 3')

value 4 |   2   |(second appearance of 'value 4')

value 5 |   2   |(second appearance of 'value 5')

推荐答案

在G2中，我输入了此公式

In G2 I entered this formula

=ArrayFormula(iferror(SORT(ROW(A2:A);SORT(ROW(A2:A);A2:A;1);1)-MATCH(A2:A;SORT(A2:A);0)-ROW()+2))

看看这是否适合您?

这篇关于如何获取另一个数组中一个数组的列号中的每个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！