Dîtina hejmara herî nêzîk

Contents

Di pratîkê de, pir caran carinan carinan hene ku ez û hûn hewce ne ku di nav komek (tabloyek) de li gorî hejmarek diyar nirxa herî nêzîk bibînin. Ew dikare bibe, wek nimûne:

Hesabkirina dakêşanê li gorî qebareyê.
Hesabkirina mîqdara bonusan bi pêkanîna planê ve girêdayî ye.
Hesabkirina rêjeyên barkirinê li gorî dûrbûnê.
Hilbijartina konteynerên maqûl ji bo tiştan, hwd.

Digel vê yekê, li gorî rewşê, dibe ku dorpêkkirin hem jor û hem jî jêrîn hewce bike.

Gelek rê hene - eşkere û ne ewqas eşkere - ji bo çareserkirina pirsgirêkek weha. Ka em bi rêz li wan binêrin.

Ji bo destpêkê, bila em dabînkerek xeyal bikin ku li ser firotanê dakêşan dide, û rêjeya dakêşanê bi hêjmara tiştên kirîn ve girêdayî ye. Mînakî, dema ku ji 5 perçeyan bêtir bikirin, ji sedî 2 erzanî tê dayîn, û dema ku ji 20 perçeyan bikirin - jixwe %6, hwd.

Meriv çawa bi lez û bez ji sedî dakêşanê dihejmêre dema ku hejmara kelûpelên kirîn têkevin?

Rêbaz 1: IF-yên nested

Rêbazek ji rêzefîlmê "çi heye ku meriv bifikire - pêdivî ye ku hûn bazdin!". Karanîna fonksiyonên hêlînkirî IF (GER) da ku bi rêzê vekolin ka nirxa hucreyê dikeve nav her navberê û ji bo rêza têkildar erzaniyek nîşan dide. Lê formula di vê rewşê de dikare bibe pir dijwar:

Dîtina hejmara herî nêzîk

Ez difikirîm ku eşkere ye ku jêbirina "kuçikek cinawir" an jî hewldana zêdekirina çend şert û mercên nû li wê piştî demekê xweş e.

Wekî din, Microsoft Excel ji bo fonksiyona IF-ê sînorek hêlînê heye - 7 caran di guhertoyên kevn de û 64 caran di guhertoyên nû de. Heke hûn bêtir hewce ne?

Rêbaz 2. VLOOKUP bi dîtina navberê

Ev rêbaz pir tevlihevtir e. Ji bo hesabkirina rêjeya dakêşanê, fonksiyona efsanewî bikar bînin VPR (VLOOKUP) di moda lêgerînê ya nêzîk de:

Dîtina hejmara herî nêzîk

B4 - nirxa mîqdara kelûpelan di danûstendina yekem de ya ku em li erzaniyê digerin
$G$4:$H$8 - lînka tabloya dakêşanê - bêyî "serî" û bi navnîşanên ku bi nîşana $-ê hatine rastkirin.
2 - Hejmara rêzî ya stûna di tabloya dakêşanê de ku em dixwazin nirxa dakêşanê jê bistînin
RAST - li vê derê "kûçik" tê veşartin. Ger wekî argumana fonksiyona dawî VPR diyar bikin DEREWAN (ŞAŞ) an 0, wê hingê fonksiyon dê lê binêre maça hişk di stûna hejmarê de (û di rewşa me de ew ê xeletiyek #N/A bide, ji ber ku di tabloya dakêşanê de nirxa 49 tune). Lê eger li şûna DEREWAN nivîsîn RAST (RAST) an 1, wê hingê fonksiyon dê ne rast, lê lê binêre herî nêzîk ya herî biçûk nirx dike û dê rêjeya dakêşana ku em hewce ne bide me.

Nebaşiya vê rêbazê ev e ku pêdivî ye ku tabloya dakêşanê bi rêza hilkişînê li gorî stûna yekem were rêz kirin. Ger celebek wusa tune (an jî ew bi rêzek berevajî tête kirin), wê hingê formula me dê nexebite:

Dîtina hejmara herî nêzîk

Li gorî vê yekê, ev nêzîkatî tenê dikare were bikar anîn da ku nirxa herî piçûk a herî nêzîk bibîne. Heke hûn hewce ne ku herî nêzîktirîn bibînin, wê hingê pêdivî ye ku hûn nêzîkatiyek cûda bikar bînin.

Rêbaz 3. Bi karanîna fonksiyonên INDEX û MATCH ya herî nêzik bibînin

Niha em ji aliyê din ve li pirsgirêka xwe binêrin. Bifikirin ku em çend modelên pompeyên pîşesaziyê yên kapasîteyên cihêreng difroşin. Tabloya firotanê li milê çepê hêza ku ji hêla xerîdar ve tê xwestin nîşan dide. Pêdivî ye ku em pompek ji hêza herî nêzîk an wekhev hilbijêrin, lê ne kêmtir ji ya ku ji hêla projeyê ve tê xwestin.

Fonksiyona VLOOKUP dê li vir ne alîkar be, ji ber vê yekê hûn neçar in ku analoga wê bikar bînin - komek fonksiyonên INDEX (NAVEROK) û Zêdetir Eşkere kirin (WEKWÎ):

Dîtina hejmara herî nêzîk

Li vir, fonksiyona MATCH bi argumana paşîn -1 di moda dîtina nirxa herî nêzîk de dixebite, û fonksiyona INDEX paşê navê modela ku em hewce ne ji stûna cîran derdixe.

Rêbaz 4. Fonksiyona nû DÎMEN (XLOOKUP)

Ger we guhertoyek Office 365 heye ku hemî nûvekirinên hatine saz kirin, wê hingê li şûna VLOOKUP (VLOOKUP) hûn dikarin analoga wê bikar bînin - fonksiyona VIEW (XLOOKUP), ku min berê bi berfirehî analîz kir:

Dîtina hejmara herî nêzîk

Vir:

B4 - nirxa destpêkê ya mîqdara hilbera ku em ji bo dakêşanê digerin
$G$4:$G$8 - range ku em lê digerin maçên
$H$4:$H$8 - Rêjeya encamên ku hûn dixwazin dakêşanê vegerînin
argumana çarem (-1) lêgerîna jimara herî nêzik a ku em dixwazin li şûna berhevokek rastîn vedihewîne.

Feydeyên vê rêbazê ev e ku ne hewce ye ku tabloya dakêşanê birêkûpêk bike û ger hewce bike, ne tenê nirxa herî piçûk, lê di heman demê de nirxa herî nêzîk a herî nêzîk jî lêgerîn heye. Argumana paşîn di vê rewşê de dê bibe 1.

Lê, mixabin, hîna her kes ne xwediyê vê taybetmendiyê ye - tenê xwediyên kêfxweş ên Office 365.

Rêbaz 5. Query Power

Heke hûn hîn bi pêveka Power Query ya hêzdar û bi tevahî belaş a ji bo Excel-ê nizanin, wê hingê hûn li vir in. Ger hûn jixwe nas in, wê hingê em hewl bidin ku wê bikar bînin da ku pirsgirêka xwe çareser bikin.

Werin em pêşî hin xebata amadekariyê bikin:

Werin em tabloyên çavkaniya xwe bi karanîna kurtebirek klavyeyê veguherînin dînamîk (aqilmend). Ctrl+T an tîm Mal - Wek tabloyê format bikin (Mal - Format wek Tablo).
Ji bo zelaliyê, em navên wan bidin. Sales и Tenzîlat bo firotana tab Kêrker (Mînakkirin).
Bi karanîna bişkojkê her yek ji tabloyan bi dorê li Power Query bar bikin Ji Tablo/Range tab Jimare (Daneyên - Ji tabloyê / rêzê). Di guhertoyên dawî yên Excel de, navê vê bişkokê hate guherandin Bi pelan (Ji pelê).
Ger tabloyan navên stûnên cihêreng ên bi mîqtaran hene, wek mînaka me ("Hejmara kelûpelan" û "Hejmara ji ..."), wê hingê divê ew di Query Query de bêne guheztin û bi heman navî bêne nav kirin.
Piştî wê, hûn dikarin bi hilbijartina fermana di pencereya edîtorê Power Query de vegerin Excel Mal - Girtin û Barkirin - Girtin û Li… (Mal - Girtin & Barkirin - Girtin & Barkirin ...) û paşê vebijêrk Tenê pêwendiyek çêbikin (Tenê pêwendiyê çêbikin).
Piştre ya herî balkêş dest pê dike. Ger ezmûna we di Power Query de hebe, wê hingê ez texmîn dikim ku rêza ramanê ya din divê di rêça hevgirtina van her du tabloyan de bi pirsek tevlêbûnê (hevkirin) a la VLOOKUP be, wekî ku di rêbaza berê de bû. Bi rastî, em ê hewce bikin ku di moda lêzêdekirinê de, ku di nihêrîna pêşîn de qet ne diyar e, bikin yek. Di tabloya Excel de hilbijêrin Dane - Daneyan bistînin - Daxwazan berhev bikin - Zêde bikin (Daneyên - Daneyên Bistînin - Lêpirsînan berhev bikin - Pêvek) û paşê maseyên me Sales и Tenzîlat bo firotana di pencereya ku xuya dike de:
Piştî hilweşînê OK maseyên me dê di nav yek yekane de - di bin hev û din de werin zeliqandin. Ji kerema xwe bala xwe bidin stûnên ku di van tabloyan de hêjmara kelûpelan di bin hev de ne, ji ber ku. wan heman navî hene:
Ger rêzika orîjînal a rêzan di tabloya firotanê de ji we re girîng e, wê hingê ji bo ku hûn piştî hemî veguheztinên paşerojê hûn wê hingê vegerînin, stûnek hejmarkirî bi karanîna fermanê li tabloya me zêde bikin. Zêdekirina Stûnek - Stûna Indeksê (Stûn lê zêde bike - Stûna nîşanê). Ger rêza rêzikan ji we re ne girîng be, wê hingê hûn dikarin vê gavê derbas bikin.
Naha, bi karanîna navnîşa dakêşanê ya di serê tabloyê de, wê li gorî stûnê rêz bikin Jimarî Hilkişîn:
Û hîleya sereke: li ser serê stûnê rast-klîk bikin Kêmkirinî tîmek hilbijêre Dagirtin - Bin (Tije - jêr). Hucreyên vala bi null bixweber bi nirxên dakêşanê yên berê ve tê dagirtin:
Dimîne ku rêzika orîjînal a rêzikan bi rêzkirina li gorî stûnan were vegerandin Naverok (hûn dikarin paşê bi ewlehî jêbirin) û bi parzûnekê ji xetên nehewce xilas bibin null bi stûnê Koda danûstandinê: