英文:
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))
拖动下来会调整范围,只计算上方的单元格。
否则,你可以使用 FILTER
和 ROW
创建一个更健壮的解决方案,尽管由于使用了动态数组公式,计算时间会更长。
=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) <= ROW()))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论