我的公式的输出如何跨越多个单元格?

huangapple go评论74阅读模式
英文:

How can the output of my formula span across multiple cells?

问题

在Excel中,您可以使用以下公式来实现您的要求:

=IF(COUNTIF(A:A, A1)=5, INDEX({"a","b","c","d","e"}, ROW(A1)-MIN(ROW(A:A))+1), IF(COUNTIF(A:A, A1)=4, INDEX({"b","c","d","e"}, ROW(A1)-MIN(ROW(A:A))+1), ""))

将这个公式放在B列的相应单元格,它将根据列A中的值和出现的次数,输出所需的字母。

请将这个公式应用到B列的每个单元格中,以获得您所需的结果。

英文:

In excel, I want to create a formula that has output a,b,c,d,e in cells Bx, Bx+1, Bx+2, Bx+3, Bx+4 respectively, if the cell value is duplicated 5 times (in this case '1' and '3' appear 5 times), and has output b,c,d,e in cells Bx, Bx+1, Bx+2, Bx+3 if the cell value is duplicated 4 times (in this case '2' appears 4 times).

In the below, column B is the output

Column A Column B
1 a
1 b
1 c
1 d
1 e
2 b
2 c
2 d
2 e
3 a
3 b
3 c
3 d
3 e

How can I create this formula in Excel?

I suppose it'd be some variation of this formula:

=IF(COUNTIF(A:A,A1)=5, (paste values a,b,c,d,e in cells B1, B2, B3, B4, B5), (paste values b,c,d,e in cells B1, B2, B3, B4))

However I don't know how to fill in the value_if_true and value_if_false part of the above formula

答案1

得分: 1

=LET(a,TOCOL(A:A,1),
     b,MMULT(--(TOROW(a)=UNIQUE(a)),SEQUENCE(ROWS(a),,,0)),
DROP(
     REDUCE("", b,
     LAMBDA(x,  y,
     VSTACK(x,
            SORT(
                 INDEX({"e","d","c","b","a"},
                 SEQUENCE(y)))))),
     1))
英文:
=LET(a,TOCOL(A:A,1),
     b,MMULT(--(TOROW(a)=UNIQUE(a)),SEQUENCE(ROWS(a),,,0)),
DROP(
     REDUCE("", b,
     LAMBDA(x,  y,
     VSTACK(x,
            SORT(
                 INDEX({"e","d","c","b","a"},
                 SEQUENCE(y)))))),
     1))

This formula first stores a counter for each unique value in column A.
Then it creates an array of indexing e to a with a sequence of the counter (so if less than 5 are found, it will list the number of strings starting from e). This result is stacked to the previous result and iterates to the end.

答案2

得分: 1

以下是翻译好的部分:

使用频率来获取每个数字的计数,而不使用 Lambda 表达式的另一种方法:

=LET(numbers, TOCOL(A:A,1),
 rows, COUNT(numbers),
 letters, "abcde",
 unique, UNIQUE(numbers),
 freq, DROP(FREQUENCY(numbers, unique), -1),
 indices, SEQUENCE(rows) - XMATCH(numbers, numbers) + 1,
 MID(letters, indices + 5 - XLOOKUP(numbers, unique, freq), 1)
)

我的公式的输出如何跨越多个单元格?

英文:

Another take on this, using Frequency to get counts for each number and without using a lambda:

=LET(numbers,TOCOL(A:A,1),
 rows,COUNT(numbers),
 letters,"abcde",
 unique,UNIQUE(numbers),
 freq,DROP(FREQUENCY(numbers,unique),-1),
 indices,SEQUENCE(rows)-XMATCH(numbers,numbers)+1,
 MID(letters,indices+5-XLOOKUP(numbers,unique,freq),1)
)

我的公式的输出如何跨越多个单元格?

答案3

得分: 0

=CHAR(97) 输出 "a;" 之后的字母可以通过递增来输出。谨慎使用相对引用可以快速修复。将 =CHAR(96 + COUNTIFS(A$1:A1, A1)) 拖动下来会调整范围,只计算上方的单元格。

否则,你可以使用 FILTERROW 创建一个更健壮的解决方案,尽管由于使用了动态数组公式,计算时间会更长。

=CHAR(96 + COUNTA(FILTER(A:A, (A:A=A1) * (ROW(A:A) <= ROW()))))

英文:

CHAR(97) outputs "a;" subsequent letters can be output by incrementing upwards. Careful use of relative references can be a quick fix. Dragging =CHAR(96 + COUNTIFS(A$1:A1, A1)) down should adjust the range to only the cells above the row being calculated.

Otherwise, you ran use FILTER and ROW to create a more robust solution, although it will take longer to calculate due to the use of dynamic array formulas.

=CHAR(96 + COUNTA(FILTER(A:A, (A:A=A1) * (ROW(A:A) &lt;= ROW()))))

huangapple
  • 本文由 发表于 2023年6月12日 19:38:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/76456293.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定