Kutafuta nambari iliyo karibu zaidi

Kwa mazoezi, mara nyingi kuna kesi wakati wewe na mimi tunahitaji kupata dhamana ya karibu zaidi katika seti (meza) kuhusiana na nambari fulani. Inaweza kuwa, kwa mfano:

• Uhesabuji wa punguzo kulingana na kiasi.
  
• Uhesabuji wa kiasi cha mafao kulingana na utekelezaji wa mpango.
  
• Uhesabuji wa viwango vya usafirishaji kulingana na umbali.
• Uchaguzi wa vyombo vinavyofaa kwa bidhaa, nk.

Kwa kuongezea, kuzunguka kunaweza kuhitajika juu na chini, kulingana na hali hiyo.

Kuna njia kadhaa - dhahiri na sio wazi sana - kutatua shida kama hiyo. Wacha tuziangalie kwa kufuatana.

Kuanza, hebu fikiria muuzaji ambaye anatoa punguzo kwa jumla, na asilimia ya punguzo inategemea wingi wa bidhaa zilizonunuliwa. Kwa mfano, wakati wa kununua vipande zaidi ya 5, punguzo la 2% hutolewa, na wakati wa kununua kutoka vipande 20 - tayari 6%, nk.

Jinsi ya kuhesabu haraka na kwa uzuri asilimia ya punguzo wakati wa kuingiza wingi wa bidhaa zilizonunuliwa?

Njia ya 1: IFs zilizowekwa

Njia kutoka kwa mfululizo "ni nini cha kufikiria - unahitaji kuruka!". Kwa kutumia vitendaji vilivyowekwa IF (KAMA) ili kuangalia kwa kufuatana ikiwa thamani ya seli inaangukia katika kila vipindi na kuonyesha punguzo kwa masafa yanayolingana. Lakini formula katika kesi hii inaweza kuwa ngumu sana: 

Nadhani ni dhahiri kwamba kutatua "doli kubwa" kama hiyo au kujaribu kuongeza hali kadhaa mpya kwake baada ya muda fulani ni jambo la kufurahisha.

Kwa kuongeza, Microsoft Excel ina kikomo cha kuota kwa kazi ya IF - mara 7 katika matoleo ya zamani na mara 64 katika matoleo mapya. Je, ikiwa unahitaji zaidi?

Njia ya 2. VLOOKUP yenye mwonekano wa muda

Njia hii ni ngumu zaidi. Ili kukokotoa asilimia ya punguzo, tumia chaguo za kukokotoa za hadithi VPR (VLOOKUP) katika takriban hali ya utafutaji:

ambapo

• B4 – thamani ya wingi wa bidhaa katika muamala wa kwanza ambao tunatafuta punguzo
  
• $G$4:$H$8 - kiungo cha jedwali la punguzo - bila "kichwa" na anwani zilizowekwa na ishara ya $.
  
• 2 - nambari ya kawaida ya safu wima katika jedwali la punguzo ambalo tunataka kupata thamani ya punguzo
• KWELI - hii ndio ambapo "mbwa" huzikwa. Ikiwa kama hoja ya mwisho ya kukokotoa VPR taja KUSEMA UONGO (UONGO) au 0, basi kazi itatafuta mechi kali katika safu ya wingi (na kwa upande wetu itatoa hitilafu ya #N/A, kwani hakuna thamani 49 kwenye jedwali la punguzo). Lakini ikiwa badala yake KUSEMA UONGO kuandika KWELI (KWELI) au 1, basi kazi haitatafuta halisi, lakini karibu ndogo thamani na itatupatia asilimia ya punguzo tunalohitaji.

Upande wa chini wa njia hii ni hitaji la kupanga jedwali la punguzo kwa mpangilio wa kupanda kwa safu ya kwanza. Ikiwa hakuna upangaji kama huo (au unafanywa kwa mpangilio wa nyuma), basi fomula yetu haitafanya kazi:

Ipasavyo, mbinu hii inaweza kutumika tu kupata thamani ndogo iliyo karibu zaidi. Ikiwa unahitaji kupata kubwa zaidi ya karibu, basi unapaswa kutumia mbinu tofauti.

Njia ya 3. Kupata kubwa zaidi kwa kutumia vitendaji vya INDEX na MATCH

Sasa hebu tuangalie shida yetu kutoka upande mwingine. Tuseme tunauza mifano kadhaa ya pampu za viwanda za uwezo mbalimbali. Jedwali la mauzo lililo upande wa kushoto linaonyesha nguvu zinazohitajika na mteja. Tunahitaji kuchagua pampu ya upeo wa karibu au nguvu sawa, lakini si chini ya kile kinachohitajika na mradi huo.

Kitendaji cha VLOOKUP hakitasaidia hapa, kwa hivyo itabidi utumie analogi yake - rundo la vitendaji vya INDEX. (INDEX) na WAZI ZAIDI (MECHI):

Hapa, chaguo la kukokotoa la MATCH na hoja ya mwisho -1 hufanya kazi katika hali ya kupata thamani kubwa iliyo karibu zaidi, na chaguo la kukokotoa la INDEX kisha linatoa jina la kielelezo tunalohitaji kutoka kwa safu wima iliyo karibu.

Mbinu ya 4. TAZAMA mpya ya utendaji kazi (XLOOKUP)

Ikiwa una toleo la Office 365 na masasisho yote yamesakinishwa, basi badala ya VLOOKUP (VLOOKUP) unaweza kutumia analog yake - kazi ya VIEW (XLOOKUP), ambayo tayari nimeshaichambua kwa kina:

hapa:

• B4 - thamani ya awali ya wingi wa bidhaa ambayo tunatafuta punguzo
• $G$4:G$8 - safu ambapo tunatafuta mechi
  
• $H$4:$H$8 - anuwai ya matokeo ambayo ungependa kurudisha punguzo
• hoja ya nne (-1) inajumuisha utafutaji wa nambari ndogo iliyo karibu zaidi tunayotaka badala ya inayolingana kabisa.
  

Faida za njia hii ni kwamba hakuna haja ya kupanga meza ya punguzo na uwezo wa kutafuta, ikiwa ni lazima, sio tu ndogo zaidi, lakini pia thamani kubwa zaidi ya karibu. Hoja ya mwisho katika kesi hii itakuwa 1.

Lakini, kwa bahati mbaya, sio kila mtu ana kipengele hiki bado - wamiliki tu wenye furaha wa Ofisi ya 365.

Njia ya 5. Swala la Nguvu

Ikiwa bado haujafahamu programu jalizi ya Power Query yenye nguvu na isiyolipishwa kabisa ya Excel, basi uko hapa. Ikiwa tayari umejulikana, basi hebu tujaribu kuitumia kutatua tatizo letu.

Wacha tufanye kazi ya maandalizi kwanza:

1. Wacha tubadilishe jedwali letu la chanzo hadi dynamic (smart) kwa kutumia njia ya mkato ya kibodi Ctrl+T au timu Nyumbani - Fomati kama meza (Nyumbani - Umbizo kama Jedwali).
2. Kwa uwazi, tuwape majina. Mauzo и Punguzo tab kuujenga (Ubunifu).
3. Pakia kila jedwali kwa zamu katika Hoja ya Nishati kwa kutumia kitufe Kutoka kwa Jedwali/Safu tab Data (Takwimu - Kutoka kwa jedwali / safu). Katika matoleo ya hivi karibuni ya Excel, kitufe hiki kimepewa jina jipya Na majani (Kutoka kwa karatasi).
   
4. Ikiwa majedwali yana majina ya safu wima tofauti na idadi, kama katika mfano wetu ("Kiasi cha bidhaa" na "Kiasi kutoka ..."), basi lazima yapewe jina jipya katika Hoja ya Nguvu na ipewe jina sawa.
5. Baada ya hapo, unaweza kurudi kwa Excel kwa kuchagua amri katika dirisha la mhariri wa Hoja ya Nguvu Nyumbani - Funga na Pakia - Funga na Pakia ndani... (Nyumbani — Funga&Pakia — Funga&Pakia kwa…) na kisha chaguo Unda tu muunganisho (Unda muunganisho pekee).

   
   

6. Kisha ya kuvutia zaidi huanza. Ikiwa una uzoefu katika Hoja ya Nguvu, basi nadhani kuwa mstari zaidi wa mawazo unapaswa kuwa katika mwelekeo wa kuunganisha jedwali hizi mbili na swali la kuunganisha (unganisha) la VLOOKUP, kama ilivyokuwa katika njia ya awali. Kwa kweli, tutahitaji kuunganisha katika hali ya kuongeza, ambayo sio dhahiri kabisa kwa mtazamo wa kwanza. Chagua kwenye kichupo cha Excel Data - Pata Data - Unganisha Maombi - Ongeza (Data — Pata Data — Unganisha maswali — Ongeza) na kisha meza zetu Mauzo и Punguzo kwenye dirisha inayoonekana:

   

7. Baada ya kubonyeza OK meza zetu zitaunganishwa kwa moja - chini ya kila mmoja. Tafadhali kumbuka kuwa nguzo na wingi wa bidhaa katika meza hizi zilianguka chini ya kila mmoja, kwa sababu. wana jina moja:

   

8. Ikiwa mlolongo wa asili wa safu kwenye jedwali la mauzo ni muhimu kwako, basi ili baada ya mabadiliko yote yanayofuata uweze kurejesha, ongeza safu iliyohesabiwa kwenye meza yetu kwa kutumia amri. Kuongeza Safu wima - Safu wima ya Fahirisi (Ongeza safu wima - safu wima ya faharasa). Ikiwa mlolongo wa mistari haujalishi kwako, basi unaweza kuruka hatua hii.
9. Sasa, kwa kutumia orodha kunjuzi kwenye kichwa cha jedwali, panga kulingana na safu wima wingi Kupanda:

   

10. Na hila kuu: bonyeza-click kwenye kichwa cha safu Ofa chagua timu Jaza - Chini (Jaza - Chini). Sanduku tupu na null kujazwa kiotomatiki na maadili ya punguzo la hapo awali:

    

11. Inabakia kurejesha mlolongo wa awali wa safu kwa kupanga kwa safu index (unaweza kuifuta kwa usalama baadaye) na uondoe mistari isiyo ya lazima na chujio null kwa safu Nambari ya shughuli:

    
    

• Kwa kutumia kitendakazi cha VLOOKUP kutafuta na kutafuta data
• Kutumia VLOOKUP (VLOOKUP) ni nyeti kwa kadiri
• VLOOKUP XNUMXD (VLOOKUP)