Nemo lamba mafi kusa - Abincin Lafiya kusa da Ni

Contents

A aikace, sau da yawa akwai lokuta lokacin da ni da kai muna buƙatar nemo mafi kusancin ƙima a cikin saiti (tebur) dangane da lambar da aka bayar. Yana iya zama, misali:

Lissafi na rangwame dangane da girma.
Lissafi na adadin kari dangane da aiwatar da shirin.
Lissafin farashin jigilar kaya ya danganta da nisa.
Zaɓin kwantena masu dacewa don kaya, da dai sauransu.

Bugu da ƙari, ana iya buƙatar zagaye biyu sama da ƙasa, dangane da yanayin.

Akwai hanyoyi da yawa - a bayyane kuma ba a bayyane ba - don magance irin wannan matsala. Mu duba su a jere.

Da farko, bari mu yi tunanin mai samar da kayayyaki wanda ke ba da rangwame a kan jumloli, kuma adadin ragi ya dogara da adadin kayan da aka saya. Misali, lokacin siyan fiye da guda 5, ana ba da rangwamen 2%, kuma lokacin siyan daga guda 20 - riga 6%, da dai sauransu.

Yadda za a ƙididdige adadin ragi cikin sauri da kyau yayin shigar da adadin kayan da aka saya?

Hanyar 1: Ƙimar IFs

Hanyar daga jerin "abin da za ku yi tunani - kuna buƙatar tsalle!". Amfani da ayyukan gida IF (IF) don bincika bi-da-biyu idan ƙimar tantanin halitta ta faɗi cikin kowane tazara da nuna ragi don kewayon daidai. Amma dabarar a cikin wannan yanayin na iya zama mai wahala sosai:

Neman lamba mafi kusa

Ina tsammanin a bayyane yake cewa gyara irin wannan “ dodon dolar dodo” ko ƙoƙarin ƙara wasu sabbin yanayi a ciki bayan ɗan lokaci yana da daɗi.

Bugu da ƙari, Microsoft Excel yana da iyakacin ƙayyadaddun ƙayyadaddun ƙayyadaddun aikin IF - sau 7 a cikin tsofaffin juzu'i da sau 64 a cikin sababbin sigogi. Idan kuna buƙatar ƙarin fa?

Hanyar 2. VLOOKUP tare da kallon tazara

Wannan hanya ta fi dacewa. Don ƙididdige adadin ragi, yi amfani da aikin almara VPR (VLOOKUP) a cikin madaidaicin yanayin bincike:

Neman lamba mafi kusa

inda

B4 - darajar adadin kayayyaki a farkon ciniki wanda muke neman rangwame
$G$4:$H$8 - hanyar haɗi zuwa teburin rangwame - ba tare da "kai" ba kuma tare da adiresoshin da aka gyara tare da alamar $.
2 - lambar ma'auni na ginshiƙi a cikin tebur mai rahusa wanda muke son samun ƙimar ragi
GASKIYA - a nan ne aka binne "kare". Idan a matsayin hujjar aikin ƙarshe VPR saka KARYA (KARYA) ko 0, to aikin zai nema m wasa a cikin adadi mai yawa (kuma a cikin yanayinmu zai ba da kuskuren #N / A, tun da babu darajar 49 a cikin tebur mai rahusa). Amma idan maimakon haka KARYA rubuta GASKIYA (GASKIYA) ko 1, to aikin ba zai duba ba don ainihin, amma mafi kusa mafi ƙanƙanta darajar kuma zai ba mu adadin rangwamen da muke buƙata.

Ƙarƙashin wannan hanyar ita ce buƙatar daidaita teburin rangwame a cikin tsari mai tasowa ta hanyar farko. Idan babu irin wannan rarrabuwa (ko kuma an yi shi ta hanyar juyawa), to tsarin mu ba zai yi aiki ba:

Neman lamba mafi kusa

Saboda haka, wannan hanyar za a iya amfani da ita kawai don nemo mafi ƙarancin ƙima. Idan kana buƙatar nemo mafi kusa, to dole ne ka yi amfani da wata hanya ta daban.

Hanyar 3. Nemo mafi kusa ta amfani da ayyukan INDEX da MATCH

Yanzu bari mu kalli matsalarmu ta daya bangaren. A ce muna sayar da nau'ikan famfo na masana'antu da yawa na iyakoki daban-daban. Teburin tallace-tallace a gefen hagu yana nuna ikon da abokin ciniki ke buƙata. Muna buƙatar zaɓar famfo mafi kusa ko daidai ikon, amma ba ƙasa da abin da aikin ke buƙata ba.

Ayyukan VLOOKUP ba zai taimaka a nan ba, don haka dole ne ku yi amfani da analog ɗin sa - gungu na ayyukan INDEX. (INDEX) da MORE EXPOSED (MATSAYI):

Neman lamba mafi kusa

Anan, aikin MATCH tare da hujja ta ƙarshe -1 yana aiki a cikin yanayin gano mafi girman ƙimar mafi kusa, kuma aikin INDEX sannan ya fitar da sunan samfurin da muke buƙata daga shafi na gaba.

Hanyar 4. Sabon aikin VIEW (XLOOKUP)

Idan kuna da sigar Office 365 tare da duk abubuwan da aka shigar, to maimakon VLOOKUP (VLOOKUP) Kuna iya amfani da analog ɗinsa - aikin VIEW (XLOOKUP), wanda na riga na yi nazari dalla-dalla:

Neman lamba mafi kusa

Nan:

B4 - ƙimar farko na yawan samfurin da muke neman rangwame
$G$4:$8 - kewayon inda muke neman ashana
$H$4:$H$8 - kewayon sakamakon da kuke son dawo da rangwamen
hujja ta hudu (-1) ya haɗa da neman mafi ƙarancin lamba mafi kusa wanda muke so maimakon daidai daidai.

Amfanin wannan hanya shine cewa babu buƙatar rarraba tebur mai rahusa da ikon bincike, idan ya cancanta, ba kawai mafi ƙanƙanta mafi kusa ba, amma har ma mafi girma mafi girma. Hujja ta ƙarshe a cikin wannan harka zata kasance 1.

Amma, abin takaici, ba kowa bane ke da wannan fasalin tukuna - masu farin ciki kawai na Office 365.

Hanyar 5. Tambayar Wuta

Idan har yanzu ba ku saba da ƙaƙƙarfan ƙarar Query Query kyauta don Excel ba, to kuna nan. Idan kun riga kun saba, to bari muyi ƙoƙarin amfani da shi don magance matsalarmu.

Bari mu fara yin wasu ayyukan shiri:

Bari mu canza teburin tushen mu zuwa mai ƙarfi (mai wayo) ta amfani da gajeriyar hanyar madannai Ctrl+T ko tawaga Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur).
Domin a fayyace, bari mu ba su sunaye. Tallace-tallace и rangwamen kudi tab Constructor (Zane).
Load da kowane tebur ɗin bi da bi zuwa Tambayar Wuta ta amfani da maɓallin Daga Tebura/Range tab data (Data - Daga tebur / kewayon). A cikin sigogin Excel na kwanan nan, an sake sanya wa wannan maɓallin suna zuwa Tare da ganye (Daga takarda).
Idan allunan suna da sunaye daban-daban tare da adadi, kamar a cikin misalinmu (“Yawan kaya” da “Yawan daga…”), to dole ne a sake sanya su cikin Query Query kuma a sanya su iri ɗaya.
Bayan haka, zaku iya komawa zuwa Excel ta zaɓar umarni a cikin taga editan Query Query Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe & Load - Rufe & Loda zuwa…) sannan zabin Kawai ƙirƙirar haɗi (ƙirƙiri haɗi kawai).
Sa'an nan mafi ban sha'awa fara. Idan kuna da gogewa a cikin Query Power, to ina ɗauka cewa ƙarin layin tunani yakamata ya kasance cikin hanyar haɗa waɗannan tebur biyu tare da tambayar haɗawa (haɗe) a la VLOOKUP, kamar yadda ya faru a cikin hanyar da ta gabata. A zahiri, muna buƙatar haɗawa cikin yanayin ƙarawa, wanda ba a bayyane yake ba a kallon farko. Zaɓi a cikin Excel tab Bayanai - Samun Bayanai - Haɗa Buƙatun - Ƙara (Bayani - Samun Bayanai - Haɗa tambayoyin - Ƙara) sannan kuma teburin mu Tallace-tallace и rangwamen kudi a cikin taga da ya bayyana:
Bayan danna kan OK Tables ɗinmu za a manne su cikin guda ɗaya - ƙarƙashin juna. Lura cewa ginshiƙan tare da adadin kayayyaki a cikin waɗannan tebur sun faɗi ƙarƙashin juna, saboda. suna iri daya:
Idan ainihin jerin layuka a cikin teburin tallace-tallace suna da mahimmanci a gare ku, don haka bayan duk canje-canje masu zuwa za ku iya dawo da shi, ƙara ginshiƙi mai lamba zuwa teburinmu ta amfani da umarnin. Ƙara Rukunin Rukunin Ƙaƙwalwar Ƙira (Ƙara shafi - Rukunin Fihirisa). Idan jerin layin ba su da mahimmanci a gare ku, to zaku iya tsallake wannan matakin.
Yanzu, ta amfani da jerin zaɓuka a cikin taken tebur, tsara shi ta shafi yawa Hawan hawa:
Kuma babban dabara: danna-dama akan taken shafi Discount zabi tawagar Cika - Kasa (Cika - Down). Kwayoyin fanko tare da null cike ta atomatik tare da ƙimar rangwamen da ta gabata:
Ya rage don dawo da ainihin jerin layuka ta hanyar jerawa ta shafi index (zaku iya share shi lafiya daga baya) kuma ku kawar da layin da ba dole ba tare da tacewa null ta shafi Lambar ciniki: