Skip to main content

Conas an luach uasta nó íos a fháil bunaithe ar chritéir in Excel?

doc-find-max-value-with-critéar-1

Ag glacadh leis go bhfuil an raon sonraí seo a leanas agam, tá ainmneacha an táirge i gcolún A, agus tá na cainníochtaí ordaithe ag colún B, anois, ba mhaith liom uasluach ordaithe an táirge KTE a fháil mar an scáileán a leanas a thaispeántar. In Excel, conas a d’fhéadfaimis an luach max nó min a bhaint bunaithe ar chritéar amháin nó níos mó?

Faigh an luach Max nó Min bunaithe ar chritéar amháin

Faigh an luach Max nó Min bunaithe ar chritéir iolracha

mboilgeog cheart gorm saighead Faigh an luach Max nó Min bunaithe ar chritéar amháin

Chun an luach max nó min a thabhairt ar ais le critéar amháin, déanfaidh an fheidhm MAX fabhar duit.

1. Iontráil an fhoirmle seo: =MAX((A2:A13=D2)*B2:B13) isteach i gcill ar leith a theastaíonn uait, féach an scáileán:

doc-find-max-value-with-critéar-2

Leideanna: San fhoirmle seo: A2: A13 is na cealla raon ina bhfuil na critéir, D2 an critéar ar mhaith leat an luach uasta a fháil bunaithe ar, B2: B13 is é an raon a thugann an luach comhfhreagrach ar ais.

2. Ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile chun uasluach KTE a fháil, féach an pictiúr:

doc-find-max-value-with-critéar-3

nótaí： Chun an luach íosta a fháil bunaithe ar an gcritéar sonrach, iontráil an fhoirmle seo le do thoil =MIN(IF(A2:A13=D2,B2:B13)), tar éis duit an fhoirmle a chlóscríobh, ní mór duit brú Ctrl + Shift + Iontráil eochracha le chéile, ansin gheobhaidh tú an toradh seo a leanas:

doc-find-max-value-with-critéar-4

mboilgeog cheart gorm saighead Faigh an luach Max nó Min bunaithe ar chritéir iolracha

Más mian leat an luach is mó nó an luach is lú a fháil le ilchritéir, is féidir leat na foirmlí seo a leanas a úsáid:

Tóg na sonraí seo a leanas mar shampla, ní mór dom uas-ordú nó ordú KTE a fháil i mí Eanáir:

doc-find-max-value-with-critéar-5

1. Cuir an fhoirmle seo isteach i gcill a theastaíonn uait cuir an toradh: =MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))), féach ar an scáileán:

doc-find-max-value-with-critéar-7

Leideanna: San fhoirmle seo: A2: A13 is iad na sonraí ina bhfuil na critéir1, B2: B13 an raon sonraí ina bhfuil na critéir2, F1 agus F2 an bhfuil na critéir ar a bhfuil tú bunaithe, C2: C13 tagraíonn sé don raon is mian leat an uasluach a thabhairt ar ais.

2. Ansin brúigh Ctrl + Shift + Iontráil eochracha ag an am céanna, an luach uasta ina bhfuil an luach comhfhreagrach i A2: A13 is ionann é agus an luach i F1, agus an luach comhfhreagrach i B2: B13 is ionann é agus an luach i F2 ar ais.

doc-find-max-value-with-critéar-8

nótaí: Chun an luach íosta a fháil bunaithe ar na critéir seo, cuir an fhoirmle seo i bhfeidhm le do thoil: =MIN(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))), agus cuimhnigh brú Ctrl + Shift + Iontráil eochracha le chéile.

Sraitheanna Comhcheangail Casta: (Faigh an luach max nó min bunaithe ar eochaircholún)

Le Kutools le haghaidh Excel'S Sraitheanna Comhcheangail Casta utiltiy, is féidir leat sraitheanna iolracha dúblacha a chur le chéile go tapa i dtaifead amháin bunaithe ar eochaircholúin, agus féadann sé roinnt ríomhanna a chur i bhfeidhm ar nós suim, meán, comhaireamh agus mar sin de i gcás colúin eile.

  • 1. Sonraigh an príomhcholún ar mhaith leat an luach max nó min a fháil ar an gcolún eile sin bunaithe air;
  • 2. Roghnaigh ríomh amháin a theastaíonn uait.

doc-find-max-value-with-critéar-9

Kutools le haghaidh Excel: le níos mó ná 200 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 60 lá. Íoslódáil agus triail saor in aisce Anois!

Earraí gaolmhara:

Conas uas sonraí / luach a roghnú le feidhm max in Excel?

Conas an luach is airde agus an luach is ísle a roghnú 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 (10)
No ratings yet. Be the first to rate!
EXCEL GOD
EXCEL GOD
This comment was minimized by the moderator on the site
Tip: use MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) or MINIFS...


The solution presented here is stupid and doesn't really work.
Reply
0
0
MJ
MJ
This comment was minimized by the moderator on the site
This was super helpful, thank you so much!
Reply
0
0
Hazi
Hazi
This comment was minimized by the moderator on the site
i'm finding min value from each cell (which not in a range), and it give me answer '-'. how to solve this? =min(A3, B3, D3, G3). but the B3 is zero.
Reply
0
0
Katya
Katya
This comment was minimized by the moderator on the site
Hello,

Similar to above example, where the MAX array formula finds the max value for KTE and Jan (two conditions) --> i am trying to do almost same, but i need to return not just the maximum value, but the name of the row which contains this maximum value... Imagine, if there is one more column between B and C, which contains the name of the person who did the order, for example... I was trying to use INDEX formula, but it doesn't really work. Like this: =INDEX(C2:C13,MAX(IF(A2:A13=F1,IF(B2:B13=F2,D2:D13)),0) What can be the solution here?
Reply
0
0
narindrankumar@gmail.com
This comment was minimized by the moderator on the site
Hi,

I would like to get help to retrieve a value from a table based on an Object's Name (Multiple objects with multiple data from a same objects which differentiate by date) to get the latest data based on specified date ( more latest data may be available than the specified data).

No. Well Start Date End Date Oil Rates (stb/d)
1 BT-101L 1/1/2017 0:00 1/2/2017 0:00 59
2 BT-106L 1/7/2017 0:00 1/8/2017 0:00 124
3 BT-106S 1/8/2017 0:00 1/9/2017 0:00 132
4 BT-101L 1/9/2017 0:00 1/10/2017 0:00 138
5 BT-201S 1/10/2017 0:00 1/11/2017 0:00 144
6 BT-203S 1/11/2017 0:00 1/12/2017 0:00 150
7 BT-101L 1/29/2017 0:00 1/30/2017 0:00 269

In the results mode, I need to populate the data based on latest available data.

Date BT-101L
1-Jan 59
2-Jan 59
3-Jan 59
4-Jan 59
5-Jan 59
6-Jan 59
7-Jan 59
8-Jan 132
9-Jan 132
10-Jan 132
11-Jan 132
12-Jan 132
13-Jan 132
14-Jan 132
15-Jan 132
16-Jan 132

Can anyone help?
Reply
Report
0
0
Garnster
Garnster
This comment was minimized by the moderator on the site
Similar question but based on reversed information. I work in land investment. I have a list of counties and corresponding values. The lower the value the more I need to work in the area. If I plug say "County A" with a value of 100, then County B with a value of 85, then County C with a value of 66, what statement can I use to identify County C as the one I need to work on?
Reply
0
0
Virtualcoyright
Virtualcoyright
This comment was minimized by the moderator on the site
Hi, when I try the MIN formula it doesn't work and just gives me 0??
Reply
0
0
Gadi Bizinyan
Gadi Bizinyan     Virtualcoyright
This comment was minimized by the moderator on the site
Virtualcoyright - Do you have blanks in the range you are using the MIN for? If so, it will return with the following formula 0:

=MIN(IF(A2:A13=D2,B2:B13))

To prevent it getting 0 if blanks exist in the range B2:B13, write the formula in the following manner:

=MIN(IF(A2:A13=D2,IF(B2:B13<>0,B2:B13)))
Reply
0
0
aamostafa87@gmail.com
    Gadi Bizinyan
This comment was minimized by the moderator on the site
Sir you have just saved my bacon ! I was facing a problem that i didn't have any blanks & all values where > 0 but the minimum formula resulted in 0 while i wanted the least positive value and your modified formula did just that. I think they should modify the article to include your modified formula.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations