Paghahanap ng pinakamalapit na numero

Sa pagsasagawa, madalas na may mga kaso kung kailan kailangan mong hanapin ang pinakamalapit na halaga sa isang set (talahanayan) na may kaugnayan sa isang naibigay na numero. Maaaring ito ay, halimbawa:

• Pagkalkula ng diskwento depende sa dami.
  
• Pagkalkula ng halaga ng mga bonus depende sa pagpapatupad ng plano.
  
• Pagkalkula ng mga rate ng pagpapadala depende sa distansya.
• Pagpili ng mga angkop na lalagyan para sa mga kalakal, atbp.

Bukod dito, maaaring kailanganin ang pag-round up at pababa, depende sa sitwasyon.

Mayroong ilang mga paraan - halata at hindi masyadong halata - upang malutas ang gayong problema. Tingnan natin ang mga ito nang sunud-sunod.

Upang magsimula, isipin natin ang isang supplier na nagbibigay ng mga diskwento sa pakyawan, at ang porsyento ng diskwento ay nakasalalay sa dami ng mga kalakal na binili. Halimbawa, kapag bumili ng higit sa 5 piraso, binibigyan ng 2% na diskwento, at kapag bumili mula sa 20 piraso - 6% na, atbp.

Paano mabilis at maganda kalkulahin ang porsyento ng diskwento kapag ipinasok ang dami ng biniling kalakal?

Paraan 1: Mga Nested IF

Isang paraan mula sa serye na "ano ang dapat isipin - kailangan mong tumalon!". Paggamit ng mga nested function IF (KUNG) upang sunud-sunod na suriin kung ang halaga ng cell ay nahuhulog sa bawat isa sa mga pagitan at magpakita ng diskwento para sa kaukulang hanay. Ngunit ang formula sa kasong ito ay maaaring maging napakahirap: 

Sa palagay ko ay halata na ang pag-debug ng tulad ng isang "halimaw na manika" o sinusubukang magdagdag ng ilang bagong kundisyon dito pagkatapos ng ilang oras ay masaya.

Bilang karagdagan, ang Microsoft Excel ay may nesting na limitasyon para sa IF function – 7 beses sa mas lumang mga bersyon at 64 beses sa mas bagong mga bersyon. Paano kung kailangan mo pa?

Paraan 2. VLOOKUP na may interval view

Ang pamamaraang ito ay mas compact. Upang kalkulahin ang porsyento ng diskwento, gamitin ang maalamat na function VPR (VLOOKUP) sa tinatayang mode ng paghahanap:

saan

• B4 – ang halaga ng dami ng mga kalakal sa unang transaksyon kung saan kami ay naghahanap ng diskwento
  
• $G$4:$H$8 – isang link sa talahanayan ng diskwento – walang “header” at may mga address na naayos na may $ sign.
  
• 2 — ang ordinal na numero ng column sa talahanayan ng diskwento kung saan gusto naming makuha ang halaga ng diskwento
• TRUE – dito inililibing ang “aso”. Kung bilang ang huling function argument VPR tukuyin KASINUNGALINGAN (MALI) o 0, pagkatapos ay hahanapin ng function mahigpit na laban sa column ng dami (at sa aming kaso ay magbibigay ito ng #N/A error, dahil walang value 49 sa discount table). Ngunit kung sa halip KASINUNGALINGAN magsulat TRUE (TOTOO) o 1, pagkatapos ay ang function ay hindi tumingin para sa eksaktong, ngunit pinakamalapit na pinakamaliit halaga at ibibigay sa amin ang porsyento ng diskwento na kailangan namin.

Ang downside ng pamamaraang ito ay ang pangangailangan na pag-uri-uriin ang talahanayan ng diskwento sa pataas na pagkakasunud-sunod ayon sa unang column. Kung walang ganoong pag-uuri (o ginagawa ito sa reverse order), hindi gagana ang aming formula:

Alinsunod dito, magagamit lamang ang diskarteng ito upang mahanap ang pinakamalapit na pinakamaliit na halaga. Kung kailangan mong hanapin ang pinakamalapit na pinakamalaking, kailangan mong gumamit ng ibang diskarte.

Paraan 3. Paghahanap ng pinakamalapit na pinakamalaking gamit ang INDEX at MATCH function

Ngayon tingnan natin ang aming problema mula sa kabilang panig. Ipagpalagay na nagbebenta kami ng ilang mga modelo ng mga pang-industriyang bomba ng iba't ibang mga kapasidad. Ang talahanayan ng pagbebenta sa kaliwa ay nagpapakita ng kapangyarihan na kinakailangan ng customer. Kailangan nating pumili ng bomba ng pinakamalapit na maximum o katumbas na kapangyarihan, ngunit hindi bababa sa kung ano ang kinakailangan ng proyekto.

Ang VLOOKUP function ay hindi makakatulong dito, kaya kailangan mong gamitin ang analogue nito - isang grupo ng mga function ng INDEX (INDEX) at MAS EXPOSED (MATCH):

Dito, gumagana ang MATCH function na may huling argumento -1 sa mode ng paghahanap ng pinakamalapit na pinakamalaking halaga, at pagkatapos ay kinukuha ng INDEX function ang pangalan ng modelo na kailangan namin mula sa katabing column.

Paraan 4. Bagong function na VIEW (XLOOKUP)

Kung mayroon kang bersyon ng Office 365 na naka-install ang lahat ng update, sa halip na VLOOKUP (VLOOKUP) maaari mong gamitin ang analogue nito - ang VIEW function (XLOOKUP), na nasuri ko nang detalyado:

Dito:

• B4 – ang paunang halaga ng dami ng produkto kung saan kami ay naghahanap ng diskwento
• $G$4:$G$8 – ang hanay kung saan kami naghahanap ng mga tugma
  
• $H$4:$H$8 – ang hanay ng mga resulta kung saan mo gustong ibalik ang diskwento
• ikaapat na argumento (-1) kasama ang paghahanap para sa pinakamalapit na pinakamaliit na numero na gusto namin sa halip na isang eksaktong tugma.
  

Ang mga bentahe ng pamamaraang ito ay hindi na kailangang pag-uri-uriin ang talahanayan ng diskwento at ang kakayahang maghanap, kung kinakailangan, hindi lamang ang pinakamalapit na pinakamaliit, kundi pati na rin ang pinakamalapit na pinakamalaking halaga. Ang huling argumento sa kasong ito ay magiging 1.

Ngunit, sa kasamaang-palad, hindi pa lahat ay may ganitong feature – tanging ang mga masayahing may-ari ng Office 365.

Paraan 5. Power Query

Kung hindi ka pa pamilyar sa malakas at ganap na libreng Power Query add-in para sa Excel, narito ka na. Kung pamilyar ka na, subukan nating gamitin ito upang malutas ang ating problema.

Gumawa muna tayo ng ilang gawaing paghahanda:

1. I-convert natin ang ating source table sa dynamic (smart) gamit ang keyboard shortcut Ctrl+T o pangkat Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan).
2. Para sa kalinawan, bigyan natin sila ng mga pangalan. Bintahan и Diskuwento tab Tagagawa (Disenyo).
3. I-load ang bawat isa sa mga talahanayan sa Power Query gamit ang button Mula sa Table/Range tab data (Data — Mula sa talahanayan/saklaw). Sa mga kamakailang bersyon ng Excel, ang button na ito ay pinalitan ng pangalan sa May mga dahon (Mula sa sheet).
   
4. Kung ang mga talahanayan ay may iba't ibang mga pangalan ng column na may mga dami, tulad ng sa aming halimbawa ("Dami ng mga kalakal" at "Dami mula sa ..."), dapat silang palitan ng pangalan sa Power Query at pareho ang pangalan.
5. Pagkatapos nito, maaari kang bumalik sa Excel sa pamamagitan ng pagpili sa command sa window ng editor ng Power Query Tahanan — Isara at I-load — Isara at I-load sa… (Home — Isara&I-load — Isara&I-load sa…) at pagkatapos ay opsyon Gumawa lang ng koneksyon (Gumawa lamang ng koneksyon).

   
   

6. Pagkatapos ay magsisimula ang pinaka-kawili-wili. Kung mayroon kang karanasan sa Power Query, ipinapalagay ko na ang karagdagang linya ng pag-iisip ay dapat na sa direksyon ng pagsasama ng dalawang talahanayan na ito sa isang join query (pagsamahin) a la VLOOKUP, tulad ng nangyari sa nakaraang pamamaraan. Sa katunayan, kakailanganin nating pagsamahin sa add mode, na hindi naman halata sa unang tingin. Piliin sa tab na Excel Data – Kumuha ng Data – Pagsamahin ang Mga Kahilingan – Magdagdag (Data — Kumuha ng Data — Pagsamahin ang mga query — Idugtong) tapos yung mga table namin Bintahan и Diskuwento sa lalabas na window:

   

7. Pagkatapos ng pag-click sa OK ang aming mga talahanayan ay ididikit sa isang solong kabuuan - sa ilalim ng bawat isa. Pakitandaan na ang mga column na may dami ng mga kalakal sa mga talahanayang ito ay nahulog sa ilalim ng bawat isa, dahil. pareho sila ng pangalan:

   

8. Kung ang orihinal na pagkakasunud-sunod ng mga hilera sa talahanayan ng pagbebenta ay mahalaga sa iyo, kung gayon pagkatapos ng lahat ng kasunod na pagbabagong-anyo ay maaari mo itong ibalik, magdagdag ng may bilang na column sa aming talahanayan gamit ang command Pagdaragdag ng Column – Index Column (Magdagdag ng column — Index column). Kung hindi mahalaga sa iyo ang pagkakasunud-sunod ng mga linya, maaari mong laktawan ang hakbang na ito.
9. Ngayon, gamit ang drop-down na listahan sa header ng talahanayan, ayusin ito ayon sa column dami Pag-akyat:

   

10. At ang pangunahing lansihin: i-right-click sa header ng column Diskuwento pumili ng isang pangkat Punan - Pababa (Punan - Pababa). Walang laman ang mga cell na may walang halaga awtomatikong napunan ng nakaraang mga halaga ng diskwento:

    

11. Ito ay nananatiling ibalik ang orihinal na pagkakasunud-sunod ng mga hilera sa pamamagitan ng pag-uuri ayon sa hanay Index (maaari mong ligtas na tanggalin ito sa ibang pagkakataon) at alisin ang mga hindi kinakailangang linya na may filter walang halaga ayon sa hanay Code ng transaksyon:

    
    

• Gamit ang VLOOKUP function upang maghanap at maghanap ng data
• Ang paggamit ng VLOOKUP (VLOOKUP) ay case-sensitive
• XNUMXD VLOOKUP (VLOOKUP)