Kupeza nambala yapafupi

Zamkatimu

M'zochita, nthawi zambiri pamakhala nthawi yomwe inu ndi ine timafunikira kupeza mtengo wapafupi kwambiri mu seti (tebulo) pokhudzana ndi nambala yoperekedwa. Zitha kukhala, mwachitsanzo:

Kuwerengera kuchotsera kutengera voliyumu.
Kuwerengera kuchuluka kwa mabonasi kutengera kukhazikitsidwa kwa dongosolo.
Kuwerengera mitengo yotumizira kutengera mtunda.
Kusankha zotengera zoyenera za katundu, etc.

Komanso, kuzungulira kungafunike mmwamba ndi pansi, kutengera momwe zinthu ziliri.

Pali njira zingapo - zoonekeratu komanso zosadziwikiratu - zothetsera vutoli. Tiyeni tiyang'ane pa iwo motsatizana.

Poyamba, tiyeni tiyerekeze wogula amene amapereka kuchotsera pa malonda aakulu, ndipo kuchuluka kwa kuchotsera kumadalira kuchuluka kwa katundu wogulidwa. Mwachitsanzo, pogula zidutswa zoposa 5, kuchotsera 2% kumaperekedwa, ndipo pogula zidutswa 20 - kale 6%, ndi zina zotero.

Momwe mungawerengere mwachangu komanso mokongola kuchotsera polowa kuchuluka kwa zinthu zomwe mwagula?

Njira 1: Ma IF Okhazikika

Njira yochokera ku mndandanda wakuti "ndi chiyani chomwe mungaganize - muyenera kudumpha!". Kugwiritsa ntchito zida zankhondo IF (NGATI) kuti muwone motsatizana ngati mtengo wa selo ukugwera munthawi iliyonse ndikuwonetsa kuchotsera kwa mtundu wofananira. Koma ndondomekoyi ingakhale yovuta kwambiri:

Kupeza nambala yapafupi

Ndikuganiza kuti ndizodziwikiratu kuti kuchotsa "chidole cha chilombo" chotere kapena kuyesa kuwonjezera mikhalidwe yatsopano pakapita nthawi kumakhala kosangalatsa.

Kuphatikiza apo, Microsoft Excel ili ndi malire a zisa pa ntchito ya IF - nthawi 7 m'matembenuzidwe akale ndi nthawi 64 m'matembenuzidwe atsopano. Nanga bwanji ngati mukufuna zina?

Njira 2. VLOOKUP yokhala ndi mawonekedwe apakati

Njira iyi ndiyophatikizana kwambiri. Kuti muwerengere kuchuluka kwa kuchotsera, gwiritsani ntchito nthano VPR (VLOOKUP) mukusakasaka pafupifupi:

Kupeza nambala yapafupi

kumene

B4 - mtengo wa kuchuluka kwa katundu pakugulitsa koyamba komwe tikufuna kuchotsera
$G$4:$H$8 - cholumikizira ku tebulo lochotsera - popanda "mutu" komanso ndi ma adilesi okhazikika ndi chizindikiro cha $.
2 - Nambala ya ordinal ya mzati patebulo lochotsera komwe tikufuna kuchotserako mtengo
WOONA - apa ndi pamene "galu" amaikidwa. Ngati mkangano womaliza wa ntchito VPR tchulani KUnama (ZABODZA) kapena 0, ndiye ntchitoyo idzayang'ana machesi okhwima mugawo la kuchuluka (ndipo kwa ife idzapereka #N/A cholakwika, popeza palibe mtengo 49 patebulo lochotsera). Koma ngati m'malo mwake KUnama kulemba WOONA (ZOONA) kapena 1, ndiye ntchitoyo idzayang'ana osati yeniyeni, koma chaching'ono kwambiri mtengo ndipo adzatipatsa kuchuluka kwa kuchotsera komwe tikufuna.

Choyipa cha njirayi ndikufunika kokonza tebulo lochotsera pokwera ndi gawo loyamba. Ngati palibe kusanja koteroko (kapena kuchitidwa motsatira dongosolo), ndiye kuti njira yathu sigwira ntchito:

Kupeza nambala yapafupi

Mogwirizana ndi izi, njira iyi ingagwiritsidwe ntchito kupeza mtengo wapafupi kwambiri. Ngati mukufuna kupeza chapafupi kwambiri, ndiye kuti muyenera kugwiritsa ntchito njira ina.

Njira 3. Kupeza chapafupi kwambiri pogwiritsa ntchito INDEX ndi MATCH ntchito

Tsopano tiyeni tione vuto lathu kuchokera mbali ina. Tiyerekeze kuti timagulitsa mitundu ingapo yamapampu amakampani osiyanasiyana. Gome logulitsa kumanzere likuwonetsa mphamvu zomwe kasitomala amafunikira. Tiyenera kusankha pampu ya mphamvu yapafupi kwambiri kapena yofanana, koma osachepera zomwe zimafunidwa ndi polojekitiyi.

Ntchito ya VLOOKUP sithandiza pano, chifukwa chake muyenera kugwiritsa ntchito analogue yake - gulu la ntchito za INDEX. (INDEX) ndi ZAMBIRI ZOVUTIKA (MATCH):

Kupeza nambala yapafupi

Apa, ntchito ya MATCH yokhala ndi mkangano womaliza -1 imagwira ntchito munjira yopezera mtengo wapafupi kwambiri, ndipo ntchito ya INDEX imatulutsa dzina lachitsanzo lomwe tikufunikira kuchokera pagawo loyandikana nalo.

Njira 4. Ntchito yatsopano VIEW (XLOOKUP)

Ngati muli ndi mtundu wa Office 365 wokhala ndi zosintha zonse, ndiye kuti m'malo mwa VLOOKUP (VLOOKUP) mutha kugwiritsa ntchito analogue yake - ntchito ya VIEW (XLOOKUP), zomwe ndazisanthula kale mwatsatanetsatane:

Kupeza nambala yapafupi

Pano:

B4 - mtengo woyamba wa kuchuluka kwa mankhwala omwe tikufuna kuchotsera
$G$4:$G$8 - gawo lomwe tikufunafuna machesi
$H4:$H$8 - kuchuluka kwa zotsatira zomwe mukufuna kubweza kuchotsera
mkangano wachinayi (-1) kumaphatikizapo kusaka nambala yapafupi kwambiri yomwe tikufuna m'malo mofanana ndendende.

Ubwino wa njirayi ndikuti palibe chifukwa chosinthira tebulo lochotsera ndikutha kufufuza, ngati kuli kofunikira, osati kokha kakang'ono kwambiri, komanso mtengo wapafupi kwambiri. Mtsutso womaliza pankhaniyi ukhala 1.

Koma, mwatsoka, si onse omwe ali ndi izi - eni ake okondwa a Office 365 okha.

Njira 5. Funso la Mphamvu

Ngati simunadziwebe zowonjezera zamphamvu komanso zaulere za Power Query za Excel, ndiye kuti muli pano. Ngati mukudziwa kale, tiyeni tiyesetse kugwiritsa ntchito kuthetsa vuto lathu.

Tiyeni tiyambe kukonzekera:

Tiyeni tisinthe magwero athu kukhala amphamvu (anzeru) pogwiritsa ntchito njira yachidule ya kiyibodi Ctrl+T kapena timu Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table).
Kuti zimveke, tiyeni tiwapatse mayina. Sales и kuchotsera tsamba Constructor (Kapangidwe).
Kwezani tebulo lililonse motsatana ndi Power Query pogwiritsa ntchito batani Kuchokera pa Table/Range tsamba Deta (Deta - Kuchokera patebulo/mitundu). M'mitundu yaposachedwa ya Excel, batani ili lasinthidwa kukhala Ndi masamba (Kuchokera pa pepala).
Ngati matebulo ali ndi mayina a magawo osiyanasiyana okhala ndi kuchuluka kwake, monga momwe ziliri mu chitsanzo chathu ("Kuchuluka kwa katundu" ndi "Kuchuluka kuchokera ku ..."), ndiye kuti ayenera kusinthidwanso mu Power Query ndikutchulidwanso chimodzimodzi.
Pambuyo pake, mutha kubwereranso ku Excel posankha lamulo pawindo la Power Query editor Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku…) ndiyeno mwina Ingopangani kulumikizana (Pangani kulumikizana kokha).
Kenako chidwi kwambiri chimayamba. Ngati muli ndi chidziwitso mu Power Query, ndiye ndikuganiza kuti mzere wowonjezera uyenera kukhala wophatikizira matebulo awiriwa ndi funso lolumikizana (kuphatikiza) la VLOOKUP, monga momwe zinalili munjira yapitayi. M'malo mwake, tidzafunika kuphatikiza mumachitidwe owonjezera, omwe sakuwonekera konse poyang'ana koyamba. Sankhani mu Excel tabu Deta - Pezani Zambiri - Phatikizani Zopempha - Onjezani (Deta - Pezani Zambiri - Phatikizani mafunso - Ikani) ndiyeno matebulo athu Sales и kuchotsera pawindo lomwe likuwoneka:
Pambuyo pang'anani OK matebulo athu adzakhala glued mu lonse - pansi pa mzake. Chonde dziwani kuti mizati ndi kuchuluka kwa katundu mu matebulo anagwa pansi wina ndi mzake, chifukwa. ali ndi dzina lomweli:
Ngati mizere yoyambirira yamizere patebulo yogulitsa ndiyofunikira kwa inu, ndiye kuti pambuyo pakusintha konseko mutha kubwezeretsanso, onjezani gawo lowerengera patebulo lathu pogwiritsa ntchito lamulo. Kuwonjezera Mzere - Mndandanda wa Mndandanda (Onjezani ndime - index column). Ngati mndandanda wa mizere ilibe kanthu kwa inu, ndiye kuti mutha kudumpha sitepe iyi.
Tsopano, pogwiritsa ntchito mndandanda wotsikira pansi womwe uli pamutu wa tebulo, sankhani motsatira mzere kuchuluka Kukwera:
Ndipo chinyengo chachikulu: dinani kumanja pamutu wagawo kuchotsera sankhani gulu Dzazani - Pansi (Dzazani - Pansi). Maselo opanda kanthu ndi null adadzaza zokha ndi zochotsera zam'mbuyomu:
Ikutsalira kukonzanso mizere yoyambirira ya mizere posanja potengera ndime Index (mutha kuzichotsa bwino pambuyo pake) ndikuchotsa mizere yosafunikira ndi fyuluta null pa ndime Khodi yamalonda: