Skip to main content

 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

mboilgeog cheart gorm saighead 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?

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

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á!
Comments (26)
No ratings yet. Be the first to rate!
Ted
Ted
This comment was minimized by the moderator on the site
Can you do this with a MAXIFS function?
Reply
0
0
skyyang
skyyang     Ted
This comment was minimized by the moderator on the site
Hello, Ted,
In this case, here can't use the MAXIFS function. In Excel, the MAXIFS function is used to return the largest number in a column based on one or more criteria.
Please click to know more about this MAXIFS fucntion..., it explains this function clearly and detailedly.
Thank you!
Reply
Report
0
0
your name
your name
This comment was minimized by the moderator on the site
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
0
0
skyyang
skyyang     your name
This comment was minimized by the moderator on the site
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!
View Attachments (1 / 5)
Reply
Report
0
0
aj942e
aj942e
This comment was minimized by the moderator on the site
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
Report
0
0
saad101
saad101
This comment was minimized by the moderator on the site
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
Report
0
0
saad
saad
This comment was minimized by the moderator on the site
how can i modify this logic to show all the column header in a single row where the value is > 1?
Reply
0
0
Marwan
Marwan     saad
This comment was minimized by the moderator on the site
Did you figure this out
Reply
0
0
big dave
big dave     Marwan
This comment was minimized by the moderator on the site
yoh you are so late man .  year has passed XD
Reply
0
0
vireb
vireb
This comment was minimized by the moderator on the site
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
Report
0
0
Fernando
Fernando
This comment was minimized by the moderator on the site
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
0
0
Hemanta Das
Hemanta Das     Fernando
This comment was minimized by the moderator on the site
hi. having same issue. Pls do reply if u have found the way out.
Reply
0
0
sierranord@gmail.com
This comment was minimized by the moderator on the site
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
Report
0
0
Janice Matthies
Janice Matthies
This comment was minimized by the moderator on the site
Thank you so much!!!
Reply
0
0
Kirthika
Kirthika
This comment was minimized by the moderator on the site
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
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations