Skip i'r prif gynnwys

Sut i gyfyngu canlyniad fformiwla i'r gwerth uchaf neu isaf yn Excel?

Dyma rai celloedd sydd angen eu nodi, a nawr rydw i eisiau defnyddio fformiwla i grynhoi'r celloedd ond cyfyngu'r canlyniad i werth uchaf fel 100. Hynny yw, os yw'r crynhoad yn llai na 100, arddangoswch y crynhoad, fel arall arddangos 100.

Cyfyngu canlyniad fformiwla i werth uchaf neu isafswm

Cyfyngu canlyniad fformiwla i werth uchaf neu isafswm

I drin y dasg hon, dim ond yn Excel y mae angen i chi gymhwyso'r swyddogaeth Max neu Min.

Cyfyngu canlyniad fformiwla i'r gwerth mwyaf (100)

Dewiswch gell y byddwch chi'n gosod y fformiwla arni, teipiwch y fformiwla hon = MIN (100, (SUM (A5: A10))), A5: A10 yw'r amrediad celloedd y byddwch chi'n eu crynhoi, ac yn pwyso Rhowch. Nawr, os yw'r crynhoad yn fwy na 100, bydd yn arddangos 100, os na, yn arddangos y crynhoad.

Mae'r crynhoad yn fwy na 100, dangoswch 100
doc limi lleiafswm 1
Mae'r crynhoad yn llai na 100, dangoswch y crynhoad
doc limi lleiafswm 2

Cyfyngu canlyniad fformiwla i'r gwerth lleiaf (20)

Dewiswch gell rydych chi'n gosod y fformiwla arni, teipiwch hon = MaX (20, (SUM (A5: A10))), A5: A10 yw'r amrediad celloedd y byddwch chi'n eu crynhoi, ac yn pwyso Rhowch. Nawr, os yw'r crynhoad yn llai nag 20, bydd yn arddangos 20; os na, arddangoswch y crynhoad.

Mae'r crynhoad yn fach nag 20, sioe 20
doc limi lleiafswm 3
Mae'r crynhoad yn fwy nag 20, dangoswch y crynhoad
doc limi lleiafswm 4

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (23)
No ratings yet. Be the first to rate!
Ola
Ola
This comment was minimized by the moderator on the site
If the summation is more than this sum up the tale invoices below the target amount

Merchant Name Recoveries from Merchant
Baxter Plc ₦1,001,000.00 ₦4,634,642.50
Baxter Plc ₦59,197.50
Baxter Plc ₦641,000.00
Baxter Plc ₦1,751,000.00
Baxter Plc ₦101,000.00
Baxter Plc ₦1,021,000.00
Baxter Plc ₦101,000.00
Baxter Plc ₦746,000.00
Reply
0
0
Ola
Ola     Ola
This comment was minimized by the moderator on the site
=IF(SUMIFS('FR-4---Recoveries from Merchant'!$K:$K,'FR-4---Recoveries from Merchant'!$E:$E,$A15)>$C15,SUMPRODUCT(SMALL(INDEX(('FR-4---Recoveries from Merchant'!$K$8:$K$27)+('FR-4---Recoveries from Merchant'!$E$8:$E$27<>$A15)*1E+99,,),ROW($1:$7))),(SUMIFS('FR-4---Recoveries from Merchant'!$K:$K,'FR-4---Recoveries from Merchant'!$E:$E,$A15)))

That's final solution I was able to provide
Reply
0
0
Ola
Ola     Ola
This comment was minimized by the moderator on the site
My question is, if the summation exceeds the maximum, I would like to return value less than the maximum, say for instance, the total is #5,000,000 and I could only pay #4,634,642.50 as the available amount. then I would like to return value like Sum(₦1,001,000.00, ₦59,197.50,₦641,000.00,₦1,751,000.00,₦101,000.00,₦1,021,000.00), which is lower than the available amount. Also, for easy reconciliation, we could refer to the specific invoice numbers.
Reply
0
0
Sarah Fraser
Sarah Fraser
This comment was minimized by the moderator on the site
Hi! This instruction is awesome. Thank you! I have a further question: if the summation exceeds the maximum, I would like to return the value 0 instead of the maximum. Is there a way to do that?
Thank you!
Reply
0
0
Sunny
Sunny     Sarah Fraser
This comment was minimized by the moderator on the site
Hi, regarding this issue, please refer to this post Go to now
Reply
Report
0
0
Sarah Fraser
Sarah Fraser     Sunny
This comment was minimized by the moderator on the site
Thank you!
Reply
0
0
Marcos
Marcos
This comment was minimized by the moderator on the site
Q stresse.....ESSAS FÓRMULAS, não funionam aqui.
Reply
0
0
Sunny
Sunny     Marcos
This comment was minimized by the moderator on the site
Hi, the formula provided above is work in English Excel version, if you are in Portugues version, try formula:
=MÍNIMO(100;(SOMA(A5:A10)))
Reply
Report
0
0
V Rogers
V Rogers
This comment was minimized by the moderator on the site
I am trying to limit the amount in a cell to a max of 24. I am calculating the number of hours worked divided by 30 with this formula, for example
=F5/K1

F5 is the specific employees hours worked,
and K1 is a hidden cell with a value of 30
(because for every 30 hours they work, they earn 1 hour of sick time) but the max limit is 24 hours in a year and i don't know how to limit the total to a max of 24 hours earnable.
Any help?
Reply
0
0
Sunny
Sunny     V Rogers
This comment was minimized by the moderator on the site
Hi, V Rogers, try this formula: =MIN(1,SUM(E:E)/K1)
in the formula, E:E is the column that contains employees' work hours, you can change it as you need., the result cell (F5) needed to be formatted as 37:30:55 in the Format Cells dialog. See screenshot:
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-max-hours-1.png
Reply
Report
0
0
Sanchez
Sanchez
This comment was minimized by the moderator on the site
Alguém me ajuda
Qual fórmula uso na celula onde meu resultado limite é de 1000 após esse resultado ele voltar a 0 e continuar somando e sempre q atingir 1000 ele voltar a 0
Reply
0
0
Sunny
Sunny     Sanchez
This comment was minimized by the moderator on the site
Hi, try this formula: =IF(SUM(D1:D2)>1000,0,SUM(D1:D2))
D1 and D2 is the first two data that used to add.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-formula-01.png
Reply
Report
0
0
Nils
Nils
This comment was minimized by the moderator on the site
Good day,

does anyone have an idea how this works on multiplication formulas?

A1*A20= 100, but min value shall be 120

Is there a formula that always shows at least 120?
Reply
0
0
Sunny
Sunny     Nils
This comment was minimized by the moderator on the site
Hi, use formula like this: =MAX(120,(A1*A20))
Reply
Report
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
For anyone looking to have a min AND a max, use the following formula:

=MIN(+20,MAX(-20,SUM(A5:A10)))

This will limit the calculated sum to between -20 and +20, or any number of your choosing.
Reply
0
0
Marcos Dantas
Marcos Dantas     Andrew
This comment was minimized by the moderator on the site
Fera, como vc conseguiu isso? Aqui não funciona de jeito nenhum, no excel BR.
Reply
0
0
Oscar
Oscar
This comment was minimized by the moderator on the site
Hola, estoy intentando aplicar la fórmula pero no me funciona. En mi caso el valor min es en horas, no se si es por eso que me da error.

Lo que quiero hacer es una celda que repita el valor de la celda adyacente limitando a un máximo de 8 horas. A ver si me pueden ayudar, gracias.
Reply
0
0
Marco B
Marco B
This comment was minimized by the moderator on the site
Thank you, worked perfectly :)
Reply
0
0
JeanDeNivelle
JeanDeNivelle
This comment was minimized by the moderator on the site
OMG. Clean and simple ! thanks for this!
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