Pagpangita sa labing duol nga numero

Sa praktis, kanunay adunay mga kaso kung kinahanglan nimo ug ako pangitaon ang labing duol nga kantidad sa usa ka set (talaan) nga may kalabotan sa usa ka gihatag nga numero. Mahimo kini, pananglitan:

• Pagkalkula sa diskwento depende sa gidaghanon.
  
• Pagkalkula sa kantidad sa mga bonus depende sa pagpatuman sa plano.
  
• Pagkalkula sa mga rate sa pagpadala depende sa gilay-on.
• Pagpili sa angay nga mga sudlanan alang sa mga butang, ug uban pa.

Dugang pa, ang pag-rounding mahimo’g kinahanglan pataas ug paubos, depende sa sitwasyon.

Adunay daghang mga paagi - klaro ug dili kaayo klaro - aron masulbad ang ingon nga problema. Atong tan-awon sila sa sunodsunod nga paagi.

Sa pagsugod, atong mahanduraw ang usa ka supplier nga naghatag diskwento sa pakyawan, ug ang porsyento sa diskwento nagdepende sa gidaghanon sa mga butang nga gipalit. Pananglitan, kung mopalit og labaw sa 5 ka piraso, usa ka 2% nga diskwento ang gihatag, ug kung mopalit gikan sa 20 ka piraso - na 6%, ug uban pa.

Sa unsa nga paagi nga dali ug matahum nga pagkalkulo sa porsyento sa diskwento sa pagsulod sa gidaghanon sa gipalit nga mga butang?

Pamaagi 1: Mga Nested IF

Usa ka pamaagi gikan sa serye nga "unsay naa sa paghunahuna - kinahanglan ka nga molukso!". Paggamit sa mga nested function IF (KUNG) sa sunodsunod nga pagsusi kon ang cell value nahulog sa matag usa sa mga interval ug ipakita ang usa ka diskwento alang sa katugbang nga range. Apan ang pormula sa kini nga kaso mahimong labi ka lisud: 

Sa akong hunahuna kini klaro nga ang pag-debug sa ingon nga usa ka "monster nga monyeka" o pagsulay sa pagdugang usa ka pares nga bag-ong mga kondisyon niini pagkahuman sa pila ka oras makalingaw.

Dugang pa, ang Microsoft Excel adunay limitasyon sa nesting para sa function sa IF - 7 ka beses sa mas daan nga mga bersyon ug 64 ka beses sa mas bag-ong mga bersyon. Unsa kaha kung kinahanglan nimo ang dugang?

Pamaagi 2. VLOOKUP nga adunay interval view

Kini nga pamaagi mas compact. Aron makalkulo ang porsyento sa diskwento, gamita ang legendary function VPR (VLOOKUP) sa gibanabana nga search mode:

diin

• B4 – ang bili sa gidaghanon sa mga butang sa unang transaksyon diin kita nangita og diskwento
  
• $G$4:$H$8 – usa ka sumpay sa lamesa sa diskwento – walay “header” ug uban sa mga adres nga gitakda nga adunay $ sign.
  
• 2 — ang ordinal nga numero sa kolum sa lamesa sa diskwento diin gusto namon makuha ang kantidad sa diskwento
• TINUOD – dinhi gilubong ang “iro”. Kung ingon ang katapusan nga argumento sa function VPR paghingalan BAKAK (SAYOK) o 0, unya pangitaon ang function higpit nga duwa sa kolum sa gidaghanon (ug sa among kaso maghatag kini og #N/A error, tungod kay walay value 49 sa discount table). Apan kon sa baylo BAKAK isulat TINUOD (Tinuod) o 1, unya ang function mangita dili alang sa eksakto, apan labing duol nga pinakagamay bili ug mohatag kanato sa porsiyento sa diskwento nga atong gikinahanglan.

Ang downside niini nga pamaagi mao ang panginahanglan sa paghan-ay sa diskwento lamesa sa ascending han-ay sa unang kolum. Kung walay ingon nga paghan-ay (o kini gihimo sa reverse order), nan ang among pormula dili molihok:

Tungod niini, kini nga pamaagi magamit lamang sa pagpangita sa labing duol nga labing gamay nga kantidad. Kung kinahanglan nimo pangitaon ang labing duol nga pinakadako, nan kinahanglan nimo nga mogamit usa ka lahi nga pamaagi.

Pamaagi 3. Pagpangita sa labing duol nga pinakadako gamit ang INDEX ug MATCH functions

Karon atong tan-awon ang atong problema gikan sa pikas nga bahin. Ibutang ta nga nagbaligya kami og daghang mga modelo sa mga bomba sa industriya nga lainlain ang kapasidad. Ang lamesa sa pagpamaligya sa wala nagpakita sa gahum nga gikinahanglan sa kustomer. Kinahanglan nga mopili kami og bomba sa labing duol nga maximum o parehas nga gahum, apan dili moubos sa gikinahanglan sa proyekto.

Ang VLOOKUP function dili makatabang dinhi, mao nga kinahanglan nimo nga gamiton ang analogue niini - usa ka hugpong sa mga function sa INDEX (INDEX) ug MAS EXPOSED (PAGTABANG):

Dinhi, ang MATCH function nga adunay katapusang argumento -1 nagtrabaho sa paagi sa pagpangita sa labing duol nga pinakadako nga kantidad, ug ang INDEX function dayon gikuha ang modelo nga ngalan nga kinahanglan namon gikan sa kasikbit nga kolum.

Pamaagi 4. Bag-ong function VIEW (XLOOKUP)

Kung ikaw adunay usa ka bersyon sa Office 365 nga adunay tanan nga mga update nga na-install, unya imbes nga VLOOKUP (VLOOKUP) mahimo nimong gamiton ang analogue niini - ang VIEW function (XLOOKUP), nga akong gisusi na sa detalye:

Dinhi:

• B4 – ang inisyal nga bili sa gidaghanon sa produkto diin kita nangitag diskwento
• $G$4:$G$8 – ang han-ay diin kita nangita alang sa mga posporo
  
• $H$4:$H$8 – ang han-ay sa mga resulta nga gusto nimong ibalik ang diskwento
• ikaupat nga argumento (-1) naglakip sa pagpangita sa labing duol nga pinakagamay nga numero nga atong gusto imbes sa eksaktong tugma.
  

Ang mga bentaha sa kini nga pamaagi mao nga dili kinahanglan nga ihan-ay ang lamesa sa diskwento ug ang katakus sa pagpangita, kung kinahanglan, dili lamang ang labing duol nga labing gamay, apan usab ang labing duol nga pinakadako nga kantidad. Ang katapusang argumento niini nga kaso mao ang 1.

Apan, ikasubo, dili pa tanan ang adunay kini nga bahin - malipayon ra ang mga tag-iya sa Office 365.

Pamaagi 5. Power Query

Kung dili ka pa pamilyar sa gamhanan ug hingpit nga libre nga Power Query add-in alang sa Excel, nan ania ka. Kung pamilyar ka na, nan atong sulayan nga gamiton kini aron masulbad ang atong problema.

Himoon una nato ang pipila ka buluhaton sa pagpangandam:

1. Atong i-convert ang atong source tables ngadto sa dynamic (smart) gamit ang keyboard shortcut Ctrl+T o team Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan).
2. Alang sa katin-awan, hatagan nato sila og mga ngalan. sales и diskwento tab Magtutukod (Laraw).
3. I-load ang matag usa sa mga lamesa ngadto sa Power Query gamit ang buton Gikan sa Table/Range tab Data (Data — Gikan sa table/range). Sa bag-o nga mga bersyon sa Excel, kini nga buton giilisan sa ngalan sa Uban sa mga dahon (Gikan sa sheet).
   
4. Kung ang mga lamesa adunay lainlain nga mga ngalan sa kolum nga adunay mga gidaghanon, sama sa among pananglitan ("Gidaghanon sa mga butang" ug "Gidaghanon gikan sa ..."), nan kinahanglan nga ilisan kini sa ngalan sa Power Query ug parehas nga ngalan.
5. Human niana, makabalik ka sa Excel pinaagi sa pagpili sa command sa Power Query editor window Panimalay — Pagsira ug Pag-load — Pagsira ug Pag-load sa… (Balay — Close&Load — Close&Load to…) ug unya kapilian Paghimo lang og koneksyon (Paghimo lang og koneksyon).

   
   

6. Unya ang labing makaiikag magsugod. Kung ikaw adunay kasinatian sa Power Query, nan akong hunahunaon nga ang dugang nga linya sa panghunahuna kinahanglan nga sa direksyon sa paghiusa niining duha ka mga lamesa nga adunay usa ka pag-apil nga pangutana (paghiusa) a la VLOOKUP, sama sa nahitabo sa miaging pamaagi. Sa tinuud, kinahanglan naton nga i-merge sa add mode, nga dili gyud klaro sa una nga pagtan-aw. Pagpili sa tab sa Excel Data - Pagkuha Data - Paghiusa sa mga Pangayo - Pagdugang (Data — Pagkuhag Data — Paghiusa sa mga pangutana — Idugang) ug dayon ang among mga lamesa sales и diskwento sa bintana nga makita:

   

7. Human sa pag-klik OK ang among mga lamesa ipapilit sa usa ka tibuuk - sa ilawom sa usag usa. Palihug timan-i nga ang mga kolum uban sa gidaghanon sa mga butang niini nga mga lamesa nahulog sa ilalum sa usag usa, tungod kay. parehas silag ngalan:

   

8. Kung ang orihinal nga pagkasunod-sunod sa mga laray sa lamesa sa pagpamaligya hinungdanon kanimo, unya aron pagkahuman sa tanan nga sunud nga mga pagbag-o mahimo nimo kini ibalik, pagdugang usa ka numero nga kolum sa among lamesa gamit ang mando Pagdugang ug Column – Index Column (Idugang ang column — Index column). Kung ang pagkasunod-sunod sa mga linya dili hinungdanon kanimo, mahimo nimong laktawan kini nga lakang.
9. Karon, gamit ang drop-down list sa header sa lamesa, paghan-ay kini sa kolum gidaghanon Pagsaka:

   

10. Ug ang nag-unang lansis: pag-right-click sa ulohan sa kolum discount pagpili og team Pun-a - Ubos (Pun-an - Down). Walay sulod nga mga selula nga adunay bili Awtomatikong napuno sa miaging mga kantidad sa diskwento:

    

11. Nagpabilin kini aron mapasig-uli ang orihinal nga han-ay sa mga laray pinaagi sa paghan-ay sa kolum index (mahimo nimong luwas nga mapapas kini sa ulahi) ug kuhaa ang dili kinahanglan nga mga linya nga adunay usa ka filter bili pinaagi sa kolum Code sa transaksyon:

    
    

• Gamit ang VLOOKUP function sa pagpangita ug pagpangita sa datos
• Ang paggamit sa VLOOKUP (VLOOKUP) kay case-sensitive
• XNUMXD VLOOKUP (VLOOKUP)