Skip to main content

Conas an luach is airde nó is ísle in Excel a roghnú?

Am éigin b’fhéidir go mbeidh ort na luachanna is airde nó is ísle i scarbhileog nó i roghnú a fháil amach agus a roghnú, amhail an méid díolacháin is airde, an praghas is ísle, srl. Conas a dhéileálann tú leis? Tugann an t-alt seo roinnt leideanna fánacha duit chun na luachanna is airde agus na luachanna is ísle i roghnúcháin a fháil amach agus a roghnú.


Faigh amach an luach is airde nó is ísle i rogha le foirmlí


Chun an líon is mó nó an líon is lú a fháil i raon:

Níl ort ach an fhoirmle thíos a iontráil i gcill bhán ar mhaith leat an toradh a fháil:

Get the largest value: =Max (B2:F10)
Get the smallest value: =Min (B2:F10)

Agus ansin brúigh Iontráil eochair chun an uimhir is mó nó an líon is lú a fháil sa raon, féach an pictiúr:


Chun na 3 uimhir nó na 3 uimhir is lú a fháil i raon:

Uaireanta, b’fhéidir gur mhaith leat na 3 uimhir is mó nó is lú a fháil ó bhileog oibre, an chuid seo, tabharfaidh mé foirmlí isteach duit chun an fhadhb seo a réiteach, déan mar a leanas le do thoil:

Iontráil thíos an fhoirmle seo i gcill:

Get the largest 3 values: =LARGE(B2:F10,1)&", "&LARGE(B2:F10,2)&", "&LARGE(B2:F10,3)
Get the smallest 3 values: =SMALL(B2:F10,1)&", "&SMALL(B2:F10,2)&", "&SMALL(B2:F10,3)

  • Leid: Más mian leat na 5 uimhir is mó nó is lú a fháil, níl le déanamh agat ach an fheidhm LARGE nó SMALL mar seo a úsáid:
  • =LARGE(B2:F10,1)&", "&LARGE(B2:F10,2)&", "&LARGE(B2:F10,3)&","&LARGE(B2:F10,4) &","&LARGE(B2:F10,5)

Leideanna: Ró-dheacair na foirmlí seo a mheabhrú, ach má tá an Téacs Auto gné de Kutools le haghaidh Excel, cabhraíonn sé leat gach foirmle a theastaíonn uait a shábháil, agus iad a athúsáid ag áit ar bith ag am ar bith is mian leat.     Cliceáil chun Kutools a íoslódáil le haghaidh Excel!


Faigh agus aibhsigh an luach is airde nó is ísle i rogha le Formáidiú Coinníollach

De ghnáth, bíonn an Formáidiú Coinníollach is féidir le gné cabhrú freisin chun na luachanna n is mó nó is lú a aimsiú agus a roghnú as raon cealla, déan mar seo le do thoil:

1. Cliceáil Baile > Formáidiú Coinníollach > Rialacha Barr / Bun > Na 10 Mír is Fearr, féach ar an scáileán:

2. Sa an Na 10 Mír is Fearr bosca dialóige, iontráil líon na luachanna is mó a theastaíonn uait a fháil, agus ansin formáid amháin a roghnú dóibh, agus aibhsíodh na n luachanna is mó, féach an scáileán:

  • Leid: Chun na n luachanna is ísle a fháil agus aird a tharraingt orthu, ní gá duit ach Cliceáil Baile > Formáidiú Coinníollach > Rialacha Barr / Bun > Bun 10 Mír.

Roghnaigh gach ceann den luach is airde nó is ísle i rogha le gné chumhachtach

An Kutools le haghaidh Excel's Roghnaigh Cealla le Luach Max & Min cabhróidh sé leat ní amháin na luachanna is airde nó is ísle a fháil amach, ach iad go léir a roghnú le chéile i roghnúcháin.

Leid:Chun é seo a chur i bhfeidhm Roghnaigh Cealla le Luach Max & Min gné, ar dtús, ba cheart duit an Kutools le haghaidh Excel, agus ansin an ghné a chur i bhfeidhm go tapa agus go héasca.

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar seo le do thoil:

1. Roghnaigh an raon a n-oibreoidh tú leis, ansin cliceáil Kutools > Roghnaigh > Roghnaigh Cealla le Luach Max & Min ..., féach ar an scáileán:

3. Sa an Roghnaigh Cealla le Luach Max & Min bosca dialóige:

  • (1.) Sonraigh an cineál cealla atá le cuardach (foirmlí, luachanna, nó iad araon) sa Feach isteach bosca;
  • (2.) Ansin seiceáil an Uasluach or Luach íosta de réir mar is gá duit;
  • (3.) Agus sonraigh an raon feidhme a bhfuil an ceann is mó nó is lú bunaithe air, anseo, roghnaigh le do thoil Cell.
  • (4.) Agus ansin más mian leat an chéad chill mheaitseála a roghnú, ní gá ach an An chéad chill amháin rogha, chun na cealla meaitseála go léir a roghnú, roghnaigh le do thoil Gach cealla rogha.

4. Agus ansin cliceáil OK, roghnóidh sé na luachanna is airde nó na luachanna is ísle sa roghnú, féach na screenshots seo a leanas:

Roghnaigh na luachanna is lú

Roghnaigh na luachanna is mó

Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!


Roghnaigh an luach is airde nó is ísle i ngach ró nó colún le gné chumhachtach

Más mian leat an luach is airde nó is ísle i ngach ró nó colún a fháil agus a roghnú, beidh an Kutools le haghaidh Excel is féidir leat fabhar a dhéanamh freisin, déan mar a leanas:

1. Roghnaigh an raon sonraí a theastaíonn uait an luach is mó nó an luach is lú a roghnú. Ansin cliceáil Kutools > Roghnaigh > Roghnaigh Cealla le Luach Max & Min chun an ghné seo a chumasú.

2. Sa an Roghnaigh Cealla le Luach Max & Min bosca dialóige, socraigh na hoibríochtaí seo a leanas de réir mar a theastaíonn uait:

4. Ansin cliceáil Ok cnaipe, roghnaítear an luach is mó nó is lú i ngach ró nó colún ag an am céanna, féach scáileáin scáileáin:

An luach is mó i ngach ró

An luach is mó i ngach colún

Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!


Roghnaigh nó aibhsigh gach cealla leis na luachanna is mó nó is lú i raon cealla nó i ngach colún agus as a chéile

Le Kutools le haghaidh Excel's Roghnaigh Cealla le Max & Min Values gné, is féidir leat gach ceann de na luachanna is mó nó is lú a roghnú nó a aibhsiú go tapa ó raon cealla, gach ró nó gach colún de réir mar is gá duit. Féach an taispeántas thíos le do thoil.    Cliceáil chun Kutools a íoslódáil le haghaidh Excel!


Earraí is coibhneasta nó an luach is lú:

  • Faigh agus Faigh an Luach is Mó Bunaithe ar Chritéir Il in Excel
  • In Excel, is féidir linn an fheidhm max a chur i bhfeidhm chun an líon is mó a fháil chomh tapa agus is féidir linn. Ach, uaireanta, b’fhéidir go mbeidh ort an luach is mó a fháil bunaithe ar chritéir áirithe, conas a d’fhéadfá déileáil leis an tasc seo in Excel?
  • Faigh agus Faigh an Naoú Luach is Mó Gan Dúbailtí in Excel
  • I mbileog oibre Excel, is féidir linn an luach is mó, an dara luach is mó nó an naoú luach is mó a fháil tríd an bhfeidhm Mhór a chur i bhfeidhm. Ach, má tá luachanna dúblacha ar an liosta, ní scarfaidh an fheidhm seo na dúblacha agus an naoú luach is mó á bhaint. Sa chás seo, conas a d’fhéadfá an naoú luach is mó a fháil gan dúbailtí in Excel?
  • Faigh an Naoú Luach uathúil is mó / is lú in Excel
  • Má tá liosta uimhreacha agat ina bhfuil roinnt dúblacha, chun an naoú luach is mó nó is lú a fháil i measc na n-uimhreacha seo, tabharfaidh an ghnáthfheidhm Móra agus Beag an toradh ar ais lena n-áirítear na dúblacha. Conas a d’fhéadfá an naoú luach is mó nó is lú a thabhairt ar ais gan neamhaird a dhéanamh ar na dúblacha in Excel?
  • Aibhsigh an Luach is Mó / Is Ísle i ngach Rae nó Colún
  • Má tá iliomad sonraí colún agus sraitheanna agat, conas a d’fhéadfá aird a tharraingt ar an luach is mó nó is ísle i ngach ró nó colún? Beidh sé tedious má shainaithníonn tú na luachanna ceann ar cheann i ngach ró nó colún. Sa chás seo, is féidir leis an ngné Formáidithe Coinníollach in Excel fabhar a thabhairt duit. Léigh níos mó le do thoil chun na sonraí a bheith ar eolas agat.
  • Suim 3 Luachan is Mó nó is Lú i Liosta Excel
  • Is gnách dúinn raon uimhreacha a chur suas tríd an bhfeidhm SUM a úsáid, ach uaireanta, caithfimid na huimhreacha 3, 10 nó n is mó nó is lú i raon a achoimriú, b’fhéidir gur tasc casta é seo. Sa lá atá inniu tugaim roinnt foirmlí isteach duit chun an fhadhb seo a réiteach.

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 (39)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hi, any idea why this formula does not work: =MAX(B5,B8,B11,B14)-MIN(B5,B8,B11,B14)

=MAX(B5:B14)-MIN(B5:B14) works but I want just the four cells, not the whole range...
This comment was minimized by the moderator on the site
Hi there,

The formula should work. Is there anything wrong with the data type?

Amanda
This comment was minimized by the moderator on the site
Hi, can u help me. i got some problem on how to retrieve which outlet got lowest growth percentage since the percentage got duplicate value

for example

PEN001 -83.33%
PEN002 -83.33%
PEN003 -92.31%
PEN004 -100.00%
PEN005 -100.00%

I'm using index match min (lowest) & small (for 2nd lowest), but the result will return PEN004 for both condition which is the lowest and 2nd lowest
This comment was minimized by the moderator on the site
Hi,

To ignore the duplicate values and only count unique values, you can use the formula below:
=INDEX(A1:A6,MATCH(SMALL(IF(ISNUMBER(B1:B6),IF(ROW(B1:B6)=MATCH(B1:B6,B1:B6,0),B1:B6)),2),B1:B6,0))

In the above formula, A1:A6 is the list of PEN00N, B1:B6 is the list of percentages, 2 means to get the 2nd lowest value.
https://www.extendoffice.com/images/stories/comments/ljy-picture/2nd_unique_lowest_value.png

Note that this formula works for unique values only, which means it will only return the first value if there are duplicates.

Amanda
This comment was minimized by the moderator on the site
Cześć,

Potrzebuje poznać sposób na rozbudowanie komendy max/min by w docelowej komórce pokazywała wartość wraz z kolorem komórki.

Przykład
mam 3 dostawców z różnymi cenami tych samych produktów (produkty pionowo, dostawcy poziomo)
Dostawcy są przykładowo w kolumnach E,F,G i każdemu przydzieliłem inny kolor, który obowiązuje również ceny danego dostawcy.
W komórkach H chciałbym by pokazała się najniższa cena wraz z kolorem danego dostawcy.

dzięki!
This comment was minimized by the moderator on the site
Hi there,

Let's say the table is as shown below, you could use Excel's Conditional formatting to get the result you want:
https://www.extendoffice.com/images/stories/comments/ljy-picture/example.png

1. Enter =MIN(E1:G1,I1) in cell H1, and then copy the formula to below cells.
https://www.extendoffice.com/images/stories/comments/ljy-picture/get-min.png
2. Selet the list H1: H5, and then on the Home tab, click Conditional Formatting > New rule.
3. In the dialog box, selet Use a formula to determine which cells to format; Enter =$H1=$E1; And then click on Format to choose the background color of cell E1.
https://www.extendoffice.com/images/stories/comments/ljy-picture/create-rule.png
4. Repeat the step 3 to create rules for other background colors: =$H1=$F1 > light blue; =$H1=$G1 > light green; ...

Now, your table should look like this:
https://www.extendoffice.com/images/stories/comments/ljy-picture/result.png

Amanda
This comment was minimized by the moderator on the site
I have a table and I need to sum the columns(which I have done) then I need to find the lowest value (which I have done) how do I display the column title of the lowest value?

Thanks
This comment was minimized by the moderator on the site
Hi, could you please show me a screenshot of your data?

Amanda
This comment was minimized by the moderator on the site
Bonjour Amanda

Ca marche !!!! 😀 🤩 👌
Merci vraiment

Puis-je vous demander de nouveau votre aide ?
Voici la formule que je recherche

A1 A2 A3 A4
Chien Colibri Renard OK
Chat Renard Chien OK
Souris Chien Lion Non
Colibri Ecureuil Marmotte Non
Eléphant Colibri Chat OK

Si en A3 on retrouve les mots des colonnes A1 et A2 alors la colonne A4 indique OK
Si en A3 on ne retrouve pas les mots des colonnes A1 et A2 alors la colonne A4 indique Non

Merci encore pour votre aide très précieuse pour une débutante en excel

Bonne journée
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi, I don't quite understand why did you asign OK to the first and second rows, since I don't see that the third value is the same as the first or second one.

Amanda
This comment was minimized by the moderator on the site
Bonjour Amanda,

Je vous ai renvoyé le fichier à l'adresse mail indiquée : à l'instant

Merci encore une fois de votre aide.

Bien cordialement
This comment was minimized by the moderator on the site
Hi there,

I tried your excel file in the French enviorment, please use =IF(A1=MAX($A$1:$A$4);1;0).
The seperator should be ";" instead of "," 😅
(If the formula does not work, use SI instead of IF)

Amanda
This comment was minimized by the moderator on the site
Bonjour Amanda

Ne pouvons joindre de fichier sur le site, j'ai répondu au mail que vous m'avez envoyé et j'y ai joins le fichier excel
Vous pourrez voir ainsi que cela me mets en erreur.

Merci encore de votre aide

Bien cordialement
This comment was minimized by the moderator on the site
Hi there,

Did you send the excel file to ?
I did not receive any messages about the issue. Could you please send that again?
Also, I think the .xlsx file is supported to upload here. If you want, you can just upload it here in a comment.

Amanda
This comment was minimized by the moderator on the site
Bonjour,
En premier lieu merci pour tout ça j'y ai trouvé plein plein de soluces.
Ma question est la suivante
J'ai une colonne avec des chiffres

26.56250
25.10400
26.29101
27.66667

Et je voudrais en parallèle une formule qui va me dire non seulement le nombre le plus élevé (ça j'ai trouvé dans vos explications) mais que cette colonne indique 1 pour le chiffre le plus élevé.
Voici un exemple

26.56250 = 0
25.10400 = 0
26.29101 = 0
27.66667 = 1

Pensez-vous que cela soit possible et si oui via quelle formule ?

Je vous remercie encore et par avance en + 😄

Bien cordialement
This comment was minimized by the moderator on the site
Hi there,

Let's say the four values are in range A1:A4, you can enter the below formula in B1:
=IF(A1=MAX($A$1:$A$4),1,0)
And then drag the fill handle down to apply the formula to below cells.
https://www.extendoffice.com/images/stories/comments/ljy-picture/return-1-if-max.png

Amanda
This comment was minimized by the moderator on the site
Bonjour Amandine
Tout d'abord merci pour cette réponse.
Malheureusement cela ne fonctionne pas <font style="vertical-align: inherit;"><font style="vertical-align: inherit;">😞</font></font>

J'ai le message d'erreur habituel
Pouvez-vous m'aider encore une fois ?

Merci
This comment was minimized by the moderator on the site
Hi there,

Can tell me what error it is? I noticed that you are using French, so please try SI instead of IF: =SI(A1=MAX($A$1:$A$4),1,0)

Amanda
This comment was minimized by the moderator on the site
Good day
I have difficulty with excel and don't know the exact names of what I want but will give it a try ?

1. I have 4 Colom's A, B, C, D, =MAX()
9, 2, 4, 1,
I want a formula that include the A,B,C,D, for instance, want the formula to include the "A" and "9" as well like A9?


2 A, B, C, D,
9, 8, 9, 2,
I also need a formula for if there is 2 (or 3 or even 4) equals highs, where I can "A9" and "C9" selected as highest ?

3 base on this information how can I create an automated report for the login user once they have selected the highest value ?

4. Maybe this is not the place to ask, but how can I create a time limit for the usage of the excel workbook that expires after 30 minutes and automatically email the report
to the login user?

5. lastly the report that the user will receive is in word format

Thank you so much
Have a wonderful day
This comment was minimized by the moderator on the site
How do I write a formula to find the smallest percentage in these 3 columns and return the value as LAG Alt or GDF or DEL?
LAG ALT -5% GDF -32% DEL 28%

In example above LAG ALT is the smallest percent/Choice I would want
This comment was minimized by the moderator on the site
Hi, the value to be compared and text string have to be in two cells for excel to compre. I have wrote a formula to get the result you want: =INDEX(A2:C2,MATCH(MIN(ABS(A3:C3)),ABS(A3:C3),0)) Please refer to the screenshot to see how it works. For more details about the formula, please click the link below. I only added ABS function (to get the absolute value) to the formula listed in the article: https://www.extendoffice.com/excel/formulas/excel-get-information-corresponding-to-minimum-value.html
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations