英文:
Finding month and year of the maximum and minimum values in a excel sheet
问题
我有一个关于投资基金2022年和2023年租金收益率的表格。
我正在尝试编写一个公式，它将给我“Fundo”行的最大和最小值的日期（以mm/YY格式显示）。年份是左边的合并单元格，月份是表格顶部的一行。
编辑：我只想要整体最大和最小值的日期（从06/2022到06/2023）。
提前感谢
编辑2：这是表格。抱歉没有早点发布。
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Jan | Fev | Mar | Abr | Mai | Jun | Jul | Ago | Set | Out | Nov | Dez | Ano | ||
| 2023 | Fundo | -6,51% | -4,70% | -7,33% | -4,95% | -5,88% | -4,45% | -29,43% | ||||||
| %CDI | -579,59% | -512,37% | -623,71% | -539,61% | ######## | -624,48% | -595,48% | |||||||
| CDI | 1,12% | 0,92% | 1,17% | 0,92% | 0,71% | 0,71% | 4,94% | |||||||
| 2022 | Fundo | -4,23% | -10,61% | -15,81% | -11,71% | -9,38% | -9,54% | -7,76% | -51,89% | |||||
| %CDI | #DIV/0! | #DIV/0! | #DIV/0! | -2301,19% | -918,52% | ######## | ######## | -1393,60% | ||||||
| CDI | 0,51% | 1,02% | 1,02% | 1,12% | 3,72% |
你可以使用以下数据重新制定答案吗？之前的公式在我的表格上不起作用。我认为这是因为我的Excel使用“;”作为分隔符。
英文:
I have a table of the rentability of a investment fund through 2022 and 2023.
I'm trying to write a formula that will give me a date (in format mm/YY) of the maximum and minimum values of the lines "Fundo". The year is a merged cell on the left and the months are a row on top of the tables.
Edit: I only want the date of the maximum and minimum overall (from 06/2022 to 06/2023).
Thanks in advance
Edit 2: Here's the table. Sorry for not posting it early.
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Jan | Fev | Mar | Abr | Mai | Jun | Jul | Ago | Set | Out | Nov | Dez | Ano | ||
| 2023 | Fundo | -6,51% | -4,70% | -7,33% | -4,95% | -5,88% | -4,45% | -29,43% | ||||||
| %CDI | -579,59% | -512,37% | -623,71% | -539,61% | ######## | -624,48% | -595,48% | |||||||
| CDI | 1,12% | 0,92% | 1,17% | 0,92% | 0,71% | 0,71% | 4,94% | |||||||
| 2022 | Fundo | -4,23% | -10,61% | -15,81% | -11,71% | -9,38% | -9,54% | -7,76% | -51,89% | |||||
| %CDI | #DIV/0! | #DIV/0! | #DIV/0! | -2301,19% | -918,52% | ######## | ######## | -1393,60% | ||||||
| CDI | 0,51% | 1,02% | 1,02% | 1,12% | 3,72% |
Could you redo the answer with this data? The previous formula isn't working on my table. I think it's because my excel uses ";" as separator.
答案1
得分: 2
这个公式以mm/yy的格式获取月份和年份（即使在重复的情况下，如下面的示例）：
=LET(fundo, VSTACK(C4:N4,C8:N8),
max, IF(fundo=MAX(fundo),1,NA()),
TEXT(TOCOL(REPT(C2:N2,max),3),"00")&"-"&RIGHT(TOCOL(REPT(VSTACK(A4,A8),max),3),2))
这会获取所有2022和2023的值，并且如果匹配最大值，则返回标题和年份。
对于编辑后的版本：
=LET(fundo, VSTACK(C3:N3,C6:N6),
max, IF(fundo=MAX(fundo),1,NA()),
TEXT(TOCOL(REPT(C1:N1,max),3),"00")&"-"&RIGHT(TOCOL(REPT(VSTACK(A3,A6),max),3),2))
英文:
This formula gets the month and year in format mm/yy (also in case of duplicates, as in example below):
=LET(fundo, VSTACK(C4:N4,C8:N8),
max, IF(fundo=MAX(fundo),1,NA()),
TEXT(TOCOL(REPT(C2:N2,max),3),"00")&"-"&RIGHT(TOCOL(REPT(VSTACK(A4,A8),max),3),2))
This takes all values of 2022 and 2023 and returns the header(s) and year(s) if it matches the max value.
And for edited version:
=LET(fundo, VSTACK(C3:N3,C6:N6),
max, IF(fundo=MAX(fundo),1,NA()),
TEXT(TOCOL(REPT(C1:N1,max),3),"00")&"-"&RIGHT(TOCOL(REPT(VSTACK(A3,A6),max),3),2))
答案2
得分: 0
=LET(A,IFERROR(MATCH(Max([基金单元 2023]),[基金单元 2023],0),0),
B,IFERROR(MATCH(Max([基金单元 2022]),[基金单元 2022],0),0),
DATE(IF(A>0,2023,2022),MAX(A,B),1))
英文:
Try
=LET(A,IFERROR(MATCH(Max([Fundo cells 2023]),[Fundo cells 2023],0),0),
B,IFERROR(MATCH(Max([Fundo cells 2022]),[Fundo cells 2022],0),0),
DATE(IF(A>0,2023,2022),MAX(A,B),1))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论