Sib l-eqreb numru

Werrej

Fil-prattika, ħafna drabi jkun hemm każijiet meta jien u int għandna bżonn insibu l-eqreb valur f'sett (tabella) fir-rigward ta 'numru partikolari. Jista' jkun, pereżempju:

Kalkolu ta' skont skont il-volum.
Kalkolu ta 'l-ammont ta' bonuses skond l-implimentazzjoni tal-pjan.
Kalkolu tar-rati tat-tbaħħir skont id-distanza.
Għażla ta’ kontenituri adattati għall-merkanzija, eċċ.

Barra minn hekk, l-arrotondament jista' jkun meħtieġ kemm 'il fuq kif ukoll 'l isfel, skond is-sitwazzjoni.

Hemm diversi modi - ovvji u mhux daqshekk ovvji - biex issolvi problema bħal din. Ejja nħarsu lejhom b'mod sekwenzjali.

Biex tibda, ejja nimmaġinaw fornitur li jagħti skontijiet bl-ingrossa, u l-perċentwal ta 'l-iskont jiddependi fuq il-kwantità ta' oġġetti mixtrija. Pereżempju, meta tixtri aktar minn biċċiet 5, jingħata skont ta '2%, u meta tixtri minn biċċa 20 - diġà 6%, eċċ.

Kif tikkalkula malajr u b'mod sabiħ il-persentaġġ ta 'skont meta ddaħħal il-kwantità tal-oġġetti mixtrija?

Metodu 1: IFs imdaħħla

Metodu mis-serje "x'hemm biex taħseb - trid taqbeż!". Jużaw funzjonijiet nested IF (JEKK) biex tiċċekkja b'mod sekwenzjali jekk il-valur taċ-ċellula jaqax f'kull wieħed mill-intervalli u turi skont għall-firxa korrispondenti. Iżda l-formula f'dan il-każ tista 'tirriżulta li tkun ingombranti ħafna:

Sib l-eqreb numru

Naħseb li huwa ovvju li debugging tali "pupa mostru" jew tipprova żżid ftit kundizzjonijiet ġodda wara xi żmien huwa pjaċevoli.

Barra minn hekk, Microsoft Excel għandu limitu ta 'nest għall-funzjoni IF - 7 darbiet f'verżjonijiet eqdem u 64 darba f'verżjonijiet aktar ġodda. X'jiġri jekk għandek bżonn aktar?

Metodu 2. VLOOKUP b'vista ta 'intervall

Dan il-metodu huwa ħafna aktar kompatt. Biex tikkalkula l-persentaġġ ta 'skont, uża l-funzjoni leġġendarja VPR (LOOKUP) fil-mod ta' tfittxija approssimattiv:

Sib l-eqreb numru

fejn

B4 – il-valur tal-kwantità tal-merkanzija fl-ewwel tranżazzjoni li għaliha qed infittxu skont
$G$4:$H$8 – link għat-tabella tal-iskont – mingħajr "header" u bl-indirizzi ffissati bis-sinjal $.
2 — in-numru ordinali tal-kolonna fit-tabella tal-iskont li minnha rridu nġibu l-valur tal-iskont
VERU - dan huwa fejn il-"kelb" huwa midfun. Jekk bħala l-aħħar argument tal-funzjoni VPR speċifika GIDDEB (FALZ) jew 0, allura l-funzjoni se tfittex taqbila stretta fil-kolonna tal-kwantità (u fil-każ tagħna se tagħti żball #N/A, peress li m'hemm l-ebda valur 49 fit-tabella ta 'skont). Imma jekk minflok GIDDEB jiktbu VERU (VERU) jew 1, allura l-funzjoni se tfittex mhux għall-eżatt, iżda l-eqreb l-iżgħar valur u se tagħtina l-persentaġġ ta 'skont li għandna bżonn.

L-iżvantaġġ ta 'dan il-metodu huwa l-ħtieġa li tissortja t-tabella ta' skont f'ordni axxendenti mill-ewwel kolonna. Jekk ma jkunx hemm tali għażla (jew isir f'ordni inversa), allura l-formula tagħna ma taħdimx:

Sib l-eqreb numru

Għaldaqstant, dan l-approċċ jista' jintuża biss biex jinstab l-eqreb valur iżgħar. Jekk għandek bżonn issib l-eqreb l-akbar, imbagħad inti għandek tuża approċċ differenti.

Metodu 3. Sib l-eqreb akbar bl-użu tal-funzjonijiet INDIĊI u MATCH

Issa ejja nħarsu lejn il-problema tagħna min-naħa l-oħra. Ejja ngħidu li nbiegħu diversi mudelli ta 'pompi industrijali ta' diversi kapaċitajiet. It-tabella tal-bejgħ fuq ix-xellug turi l-qawwa meħtieġa mill-klijent. Għandna bżonn nagħżlu pompa tal-eqreb qawwa massima jew ugwali, iżda mhux inqas minn dak meħtieġ mill-proġett.

Il-funzjoni VLOOKUP mhux se tgħin hawn, għalhekk ikollok tuża l-analogu tagħha - mazz ta 'funzjonijiet INDIĊI (INDIĊI) u AKTAR ESPOSTI (TAQBIL):

Sib l-eqreb numru

Hawnhekk, il-funzjoni MATCH bl-aħħar argument -1 taħdem fil-mod li ssib l-eqreb l-akbar valur, u l-funzjoni INDIĊI mbagħad estratti l-isem tal-mudell li neħtieġu mill-kolonna maġenb.

Metodu 4. Funzjoni ġdida VIEW (XLOOKUP)

Jekk għandek verżjoni ta' Office 365 bl-aġġornamenti kollha installati, imbagħad minflok VLOOKUP (LOOKUP) tista 'tuża l-analogu tagħha - il-funzjoni VIEW (XLOOKUP), li diġà analizzajt fid-dettall:

Sib l-eqreb numru

Hawnhekk:

B4 – il-valur inizjali tal-kwantità tal-prodott li qed infittxu skont
$G$4:$G$8 – il-firxa fejn qed infittxu logħbiet
$H$4:$H$8 – il-firxa ta’ riżultati li minnha trid tirritorna l-iskont
ir-raba' argument (-1) jinkludi t-tfittxija għall-eqreb numru iżgħar li rridu minflok taqbila eżatta.

Il-vantaġġi ta 'dan il-metodu huma li m'hemmx bżonn li tissortja t-tabella ta' skont u l-abbiltà li tfittex, jekk meħtieġ, mhux biss l-eqreb iżgħar, iżda wkoll l-eqreb l-akbar valur. L-aħħar argument f'dan il-każ se jkun 1.

Iżda, sfortunatament, mhux kulħadd għadu din il-karatteristika - sidien kuntenti biss ta 'Office 365.

Metodu 5. Mistoqsija dwar l-Enerġija

Jekk għadek m'intix familjari mal-power Query add-in b'saħħtu u kompletament ħieles għal Excel, allura qiegħed hawn. Jekk inti diġà familjari, allura ejja nippruvaw nużawha biex issolvi l-problema tagħna.

Ejja nagħmlu xi xogħol preparatorju l-ewwel:

Ejja nikkonverti t-tabelli tas-sors tagħna għal dinamiċi (intelliġenti) billi tuża shortcut tat-tastiera Ctrl+T jew tim Home – Format bħala tabella (Id-dar — Format bħala Tabella).
Għaċ-ċarezza, ejja nagħtuhom ismijiet. bejgħ и skontijiet tab kostruttur (Disinn).
Tagħbija kull waħda mit-tabelli f'Power Query billi tuża l-buttuna Minn Tabella/Firxa tab Data (Data — Minn tabella/firxa). F'verżjonijiet reċenti ta' Excel, din il-buttuna ngħatat isem ġdid għal Bil-weraq (Mill-folja).
Jekk it-tabelli għandhom ismijiet ta 'kolonni differenti bi kwantitajiet, bħal fl-eżempju tagħna ("Kwantità ta' oġġetti" u "Kwantità minn ..."), allura għandhom jingħataw isimhom mill-ġdid f'Power Query u jisimhom l-istess.
Wara dan, tista 'terġa' lura għal Excel billi tagħżel il-kmand fit-tieqa tal-editur tal-Power Query Id-dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija fi... (Dar — Agħlaq&Tagħbija — Agħlaq&Tagħbija għal...) u mbagħad għażla Oħloq biss konnessjoni (Oħloq konnessjoni biss).
Imbagħad jibda l-aktar interessanti. Jekk għandek esperjenza fil-Power Query, allura nassumi li l-linja ta 'ħsieb ulterjuri għandha tkun fid-direzzjoni li tgħaqqad dawn iż-żewġ tabelli ma' query join (għaqda) a la VLOOKUP, kif kien il-każ fil-metodu preċedenti. Fil-fatt, ser ikollna bżonn ningħaqdu fil-modalità żid, li ma hija ovvja xejn mal-ewwel daqqa t'għajn. Agħżel fit-tab Excel Dejta – Ikseb id-Dejta – Għaqqad it-Talbiet – Żid (Data — Ikseb id-Data — Għaqqad il-mistoqsijiet — Ehmeż) u mbagħad it-tabelli tagħna bejgħ и skontijiet fit-tieqa li tidher:
Wara li tikklikkja fuq OK it-tabelli tagħna se jkunu inkollati f'wieħed sħiħ - taħt xulxin. Jekk jogħġbok innota li l-kolonni bil-kwantità ta 'oġġetti f'dawn it-tabelli waqgħu taħt xulxin, għaliex. għandhom l-istess isem:
Jekk is-sekwenza oriġinali ta 'ringieli fit-tabella tal-bejgħ hija importanti għalik, allura sabiex wara t-trasformazzjonijiet sussegwenti kollha tista' mbagħad tirrestawraha, żid kolonna numerata mat-tabella tagħna billi tuża l-kmand Żieda ta’ Kolonna – Kolonna tal-Indiċi (Żid kolonna — Kolonna tal-indiċi). Jekk is-sekwenza tal-linji ma jimpurtax għalik, allura tista 'taqbeż dan il-pass.
Issa, billi tuża l-lista drop-down fl-intestatura tat-tabella, issortjaha skont il-kolonna kwantità Jogħla:
U l-trick prinċipali: ikklikkja dritt fuq l-intestatura tal-kolonna Discount agħżel tim Imla – 'l isfel (Imla — 'l isfel). Ċelloli vojta bil null mimlija awtomatikament bil-valuri ta' skont preċedenti:
Fadal li terġa 'tiġi restawrata s-sekwenza oriġinali ta' ringieli billi tissortja skond il-kolonna indiċi (tista 'tħassarha b'mod sikur aktar tard) u teħles minn linji mhux meħtieġa b'filtru null bil-kolonna Kodiċi tat-transazzjoni: