Conas an luach is airde a fháil i gceannteideal colún as a chéile agus ar ais in Excel?

San Airteagal seo, labhróidh mé faoi conas ceanntásc an cholúin den luach is mó i ndiaidh a chéile a chur ar ais in Excel. Mar shampla, tá an raon sonraí seo a leanas agam, is é colún A an bhliain, agus tá na huimhreacha ordaithe ó Eanáir go Bealtaine i gcolún B go F. Agus anois, ba mhaith liom ainm na míosa den luach is mó a fháil i ngach ró.

doc-fháil-colún-header-1

Faigh an luach is airde i ndiaidh a chéile agus ceanntásc an cholúin ar ais leis an bhfoirmle

Faigh an luach is airde i ndiaidh a chéile agus ceanntásc an cholúin ar ais leis an bhfoirmle

Chun ceanntásc an cholúin den luach is mó i ndiaidh a chéile a aisghabháil, is féidir leat teaglaim d’fheidhmeanna INDEX, MATCH agus MAX a chur i bhfeidhm chun an toradh a fháil. Déan mar a leanas le do thoil:

1. Iontráil an fhoirmle seo i gcill bhán atá uait: =INDEX($B$1:$F$1,0,MATCH(MAX($B2:$F2),$B2:$F2,0)), agus ansin brúigh Iontráil eochair chun ainm na míosa a fháil a mheaitseálann an luach is mó i ndiaidh a chéile. Féach an pictiúr:

doc-fháil-colún-header-2

2. Ansin roghnaigh an cill agus tarraing an láimhseáil líonta go dtí an raon a theastaíonn uait an fhoirmle seo a bheith ann, féach an pictiúr:

doc-fháil-colún-header-3

nótaí: San fhoirmle thuas: B1: F1 an bhfuil an tsraith ceanntásca ar mhaith leat filleadh, B2: F2 an raon sonraí ina bhfuil an luach is mó a theastaíonn uait a fháil.

Airteagal gaolmhar:

Conas an luach is airde agus an luach cille cóngarach a fháil in Excel?

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

24 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
your name · 2 months ago
What if there are 2 same highest value, how can i return both column headers, for example the sales for both april and august are 30k, how can i return the name of both april and august
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 months ago
  
  Hello,
  To return all the column headers with multiple higest values, you should apply the below User Defined Function.
  Function getmax(rngRst As Range, rngVal As Range) As String Dim i As Integer Dim xNum As Double Dim xStr As String xNum = Application.WorksheetFunction.Max(rngVal) For i = 1 To rngVal.Count If rngVal(i).Value = xNum Then xStr = xStr & rngRst(i).Value & "," End If Next getmax = Left(xStr, Len(xStr) - 1) End Function
  Please copy the above code into the VBA module, and then use the below formula to get the first result, and then drag the fill handle down to return other column headers.
  =getmax($B$1:$H$1,B2:H2)
  
  Please try, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
aj942e · 1 years ago
Hi, I have attendance sheet. First column heading is names and rest columns for dates.
Names columns shows names in rows and dates columns shows p (present) H (weekoff) L (leave)
It's for 23 people.
and at last I have made total presents in a day, then total leaves and then total weekoffs.
"Now I am searching the way where I can click on total present numbers and get list of all names who are present in a particular day."
I tried many ways but no luck.
first columns names, second shows if they were present or leave or on week offs.
Is there anyway where I can get list of all names who are present or on leave?
Any formula?
- Reply
To post as a guest, your comment is unpublished.
saad101 · 1 years ago
how can i modify this logic to show me all the records in one row where a value in all selected columns are greater than 1
- Reply
To post as a guest, your comment is unpublished.
saad · 1 years ago
how can i modify this logic to show all the column header in a single row where the value is > 1?
- Reply
- To post as a guest, your comment is unpublished.
  Marwan · 7 months ago
  
  Did you figure this out
  
  Reply
  
  To post as a guest, your comment is unpublished.
  big dave · 2 months ago
  
  yoh you are so late man . year has passed XD
  
  Reply
To post as a guest, your comment is unpublished.
vireb · 2 years ago
Thank for this. It helped. Does it work the same way for a range of cells instead of just a row? I am trying to get the formula to return the column header (month) of the largest and smallest value in a range of cells? This is what I tried =INDEX($B$4:$M$4,0,MATCH(MAX($B5:$M42),$B5:$M42,0)) but it gives a #N/A answer
- Reply
To post as a guest, your comment is unpublished.
Fernando · 2 years ago
What happens if you have duplicate values in a row and you want the latest value's header. Is this possible?
The current formula only returns the first match, but in my case I need to know the latest one
- Reply
- To post as a guest, your comment is unpublished.
  Hemanta Das · 2 years ago
  
  hi. having same issue. Pls do reply if u have found the way out.
  
  Reply
To post as a guest, your comment is unpublished.
sierranord@gmail.com · 2 years ago
You're Aces Kid !!! ... this example worked perfect for my application: =INDEX($B$1:$F$1,0,MATCH(MAX($B2:$F2),$B2:$F2,0))

ISA 40:31
- Reply
To post as a guest, your comment is unpublished.
Janice Matthies · 4 years ago
Thank you so much!!!
- Reply
To post as a guest, your comment is unpublished.
Kirthika · 4 years ago
Hi, am trying to find lowest value in a row and the highest value on the corresponding column. Is there a formula or shortcut to find this. Please guide
- Reply
To post as a guest, your comment is unpublished.
Sean · 4 years ago
Thank you! very helpful!
- Reply
To post as a guest, your comment is unpublished.
dave · 4 years ago
pardon: think my message dissappeard (2nd copy?)
hi, i am trying to find the column number of a max number in 1 row of 5 cells. if i start from M9 to Q9 and the max number is in the middle cell: O9 the result looking for would be: 3. i cannot change the header cells to have: 1 2 3 4 5 as they get values pasted over.. if this is the same formula as above. how to i modify it to use column locations instead of header values. thanks:

=INDEX(M1:Q1,0,MATCH(MAX(M9:Q9),M9:Q9,0))
- Reply
To post as a guest, your comment is unpublished.
dave · 4 years ago
hi, i am having trouble finding an "unquote" :) column number for where the maximun number is. if i use columns M9 thru Q9 and the max is in the middle column: O9, that is the 3rd column in that range. i cannot put 1 2 3 4 5 in the header cells as they get values pasted over. if this is the same formula as above, how do i modify it to get a result eg: 3 for 3rd column in. thanks.

=INDEX(M1:Q1,0,MATCH(MAX(M9:Q9),M9:Q9,0))
- Reply
To post as a guest, your comment is unpublished.
Stephanie · 4 years ago
Hi, i want to make a list with names and grades and i want to find the 3 highest grades and the names that go with them i used the formula =INDEX(A3:A13,MATCH(LARGE(K3:K13,2),K3:K13,0)) but if two people have the same grade what happens??? can someone help me????
- Reply
- To post as a guest, your comment is unpublished.
  Victoria · 4 years ago
  
  Hello, Did you find a solution?
  
  Reply
To post as a guest, your comment is unpublished.
Liz · 5 years ago
How can I do this if I want to return a row header rather than column header? I would also like to base the row number on a value in another cell that matches the column header. (So, look up column header and max value in that column, return the row header for that max value).
- Reply
To post as a guest, your comment is unpublished.
THANK YOU · 5 years ago
OMFG THIS ARTICLE JUST SAVED ME HOURS OF WORK TODAY

THANK YOU SO MUCH FOR THIS SOLUTION. YOU ARE TRULY A GENIUS AND THIS METHOD IS MUCH APPRECIATED!!!!

10/10
- Reply
To post as a guest, your comment is unpublished.
Joseph · 5 years ago
Is it possible to have this function work with multiple tabs on the same spreadsheet?
- Reply
To post as a guest, your comment is unpublished.
MikeS · 5 years ago
Great thank you, I'd have strugled creating that formula myself, kind of you to share.
- Reply
- To post as a guest, your comment is unpublished.
  Murtaza · 5 years ago
  
  Thanks. I would like to know how can this function work if there are multiple cells in a row with the same value ? Is it possible to return multiple column headers in the target cell ?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Siba · 7 months ago
  
  i have same requirement, pls let me know if you found the answer, thx
  
  Reply

Logáil isteach do chuntas

Athshocraigh Pasfhocal