Jwenn nimewo ki pi pre a

contents

Nan pratik, trè souvan gen ka lè ou menm ak mwen bezwen jwenn valè ki pi pre nan yon seri (tablo) an relasyon ak yon nimewo bay yo. Li ta ka, pou egzanp:

Kalkil rabè depann sou volim.
Kalkil kantite bonis depann sou aplikasyon plan an.
Kalkil nan pousantaj anbake depann sou distans la.
Seleksyon kontenè apwopriye pou machandiz, elatriye.

Anplis, awondi ka mande tou de monte ak desann, tou depann de sitiyasyon an.

Gen plizyè fason - evidan ak pa tèlman evidan - pou rezoud yon pwoblèm konsa. Ann gade yo an sekans.

Pou kòmanse, ann imajine yon founisè ki bay rabè sou en, ak pousantaj rabè a depann sou kantite machandiz yo achte. Pou egzanp, lè w ap achte plis pase 5 moso, yo bay yon rabè 2%, ak lè w ap achte nan 20 moso - deja 6%, elatriye.

Ki jan yo byen vit ak trè byen kalkile pousantaj rabè lè w ap antre nan kantite machandiz yo achte?

Metòd 1: IF anbrike

Yon metòd ki soti nan seri a "sa ki la pou panse - ou bezwen sote!". Sèvi ak fonksyon enbrike IF (SI) pou tcheke sekans si valè selil la tonbe nan chak nan entèval yo epi montre yon rabè pou seri ki koresponn lan. Men, fòmil la nan ka sa a ka vin trè ankonbran:

Jwenn nimewo ki pi pre a

Mwen panse ke li evidan ke debogaj tankou yon "poupe mons" oswa ap eseye ajoute yon koup nan nouvo kondisyon nan li apre kèk tan se plezi.

Anplis de sa, Microsoft Excel gen yon limit nidifikasyon pou fonksyon IF - 7 fwa nan vèsyon ki pi ansyen ak 64 fwa nan vèsyon ki pi nouvo. E si ou bezwen plis?

Metòd 2. VLOOKUP ak View entèval

Metòd sa a se pi plis kontra enfòmèl ant. Pou kalkile pousantaj rabè a, sèvi ak fonksyon lejand la VPR (VCHACHUP) nan mòd rechèch apwoksimatif:

Jwenn nimewo ki pi pre a

ki kote

B4 – valè kantite machandiz nan premye tranzaksyon nou ap chèche pou yon rabè
$G$4:$H$8 - yon lyen ki mennen nan tablo rabè a - san yon "header" ak adrès yo fiks ak siy $ la.
2 — nimewo ordinal kolòn nan tablo rabè kote nou vle jwenn valè rabè a
VERITE - sa a se kote "chen an" antere l '. Si kòm dènye agiman fonksyon an VPR presize MANTI (FO) oswa 0, Lè sa a, fonksyon an ap chèche pou match strik nan kolòn kantite a (ak nan ka nou an li pral bay yon erè #N/A, paske pa gen okenn valè 49 nan tablo rabè a). Men, si pito MANTI ekri VERITE (VRÈ) oswa 1, Lè sa a, fonksyon an ap gade pa egzak la, men pi pre pi piti valè epi yo pral ba nou pousantaj rabè nou bezwen an.

Dezavantaj nan metòd sa a se bezwen nan sòt tab la rabè nan lòd monte pa premye kolòn nan. Si pa gen okenn klasman sa yo (oswa li fè nan lòd ranvèse), Lè sa a, fòmil nou an pa pral travay:

Jwenn nimewo ki pi pre a

An konsekans, apwòch sa a ka itilize sèlman pou jwenn valè ki pi pre ki pi piti a. Si ou bezwen jwenn pi pre a pi gwo, Lè sa a, ou gen yo sèvi ak yon apwòch diferan.

Metòd 3. Jwenn pi pre ki pi gwo a lè l sèvi avèk fonksyon ENDEX ak MATCH

Koulye a, kite a gade nan pwoblèm nou an soti nan lòt bò a. Sipoze nou vann plizyè modèl ponp endistriyèl ak divès kalite kapasite. Tablo lavant sou bò gòch la montre pouvwa kliyan an mande. Nou bezwen chwazi yon ponp ki pi pre a maksimòm oswa egal pouvwa, men pa mwens pase sa ki mande pa pwojè a.

Fonksyon VLOOKUP pa pral ede isit la, kidonk w ap oblije sèvi ak analòg li yo - yon pakèt fonksyon INDEX. (ENDEX) ak plis ekspoze (MATCH):

Jwenn nimewo ki pi pre a

Isit la, fonksyon MATCH ak dènye agiman an -1 ap travay nan mòd pou jwenn pi gwo valè ki pi pre a, epi fonksyon INDEX la retire non modèl nou bezwen an nan kolòn adjasan a.

Metòd 4. Nouvo fonksyon VIEW (XLOOKUP)

Si ou gen yon vèsyon nan Biwo 365 ak tout mizajou enstale, Lè sa a, olye pou yo VLOOKUP (VCHACHUP) ou ka itilize analòg li yo - fonksyon VIEW la (XLOOKUP), ke mwen te deja analize an detay:

Jwenn nimewo ki pi pre a

Isit la:

B4 – valè inisyal la nan kantite a nan pwodwi a pou ki nou ap chèche pou yon rabè
$G$4:$G$8 – seri a kote nou ap chèche pou alimèt
$H$4:$H$8 – seri rezilta ou vle retounen rabè a
katriyèm agiman (-1) gen ladan rechèch la pou pi pre nimewo ki pi piti ke nou vle olye de yon matche egzak.

Avantaj ki genyen nan metòd sa a se ke pa gen okenn bezwen sòt tab la rabè ak kapasite nan rechèch, si sa nesesè, pa sèlman pi pre ki pi piti a, men tou ki pi pre pi gwo valè a. Dènye agiman an nan ka sa a pral 1.

Men, malerezman, se pa tout moun ki gen karakteristik sa a ankò - se sèlman pwopriyetè ki kontan nan Office 365.

Metòd 5. Rekèt pouvwa

Si ou poko abitye ak pwisan ak konplètman gratis Power Query ajoute nan pou Excel, Lè sa a, ou la. Si ou deja abitye, ann eseye sèvi ak li pou rezoud pwoblèm nou an.

Ann fè kèk travay preparasyon an premye:

Ann konvèti tab sous nou an dinamik (entelijan) lè l sèvi avèk yon chemen kout klavye Ctrl+T oswa ekip Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo).
Pou klè, ann ba yo non. Vann и Rabè tab Constructor (Design).
Chaje chak tab yo an vire nan Power Query lè l sèvi avèk bouton an Soti nan Tablo / Ranje tab Done (Done - Soti nan tab/ranje). Nan dènye vèsyon Excel, bouton sa a te chanje non Ak fèy (Soti nan fèy).
Si tab yo gen non kolòn diferan ak kantite, tankou nan egzanp nou an ("Kantite machandiz" ak "Kantite soti nan ..."), Lè sa a, yo dwe chanje non yo nan Power Query ak non yo menm.
Apre sa, ou ka retounen tounen nan Excel lè w chwazi lòd la nan fenèt editè Power Query Kay — Fèmen epi chaje — Fèmen epi chaje nan… (Kay — Fèmen&Chaje — Fèmen&Chaje sou…) ak Lè sa a, opsyon Jis kreye yon koneksyon (Sèlman kreye koneksyon).
Lè sa a, pi enteresan an kòmanse. Si ou gen eksperyans nan Power Query, Lè sa a, mwen sipoze ke liy lan plis nan panse yo ta dwe nan yon direksyon ki nan fusion de tab sa yo ak yon rechèch rantre (fusion) yon la VLOOKUP, jan sa te ka a nan metòd anvan an. An reyalite, nou pral bezwen rantre nan mòd ajoute, ki se pa nan tout evidan nan premye gade. Chwazi nan tab Excel Done – Jwenn Done – Konbine Demann – Ajoute (Done — Jwenn Done — Konbine demann — Mete) epi apre tab nou yo Vann и Rabè nan fenèt la ki parèt:
Apre klike sou OK tab nou yo pral kole nan yon sèl antye - youn anba lòt. Tanpri sonje ke kolòn yo ak kantite machandiz nan tablo sa yo tonbe anba youn ak lòt, paske. yo gen menm non:
Si sekans orijinal la nan ranje nan tab lavant la enpòtan pou ou, Lè sa a, se konsa ke apre tout transfòmasyon ki vin apre ou ka retabli li, ajoute yon kolòn nimewote nan tablo nou an lè l sèvi avèk lòd la. Ajoute yon kolòn - Kolòn endèks (Ajoute kolòn — kolòn endèks). Si sekans liy lan pa enpòtan pou ou, Lè sa a, ou ka sote etap sa a.
Koulye a, lè l sèvi avèk lis drop-desann nan tèt la nan tablo a, sòt li pa kolòn kantite Asandan:
Ak Trick prensipal la: dwa-klike sou tèt kolòn nan Rabè chwazi yon ekip Ranpli – Desann (Ranpli — Desann). Vide selil ak nil otomatikman ranpli ak valè rabè anvan yo:
Li rete retabli sekans orijinal la nan ranje pa klasman pa kolòn endèks (ou ka san danje efase li pita) epi debarase m de liy ki pa nesesè ak yon filtè nil pa kolòn Kòd tranzaksyon: