Contents
Kwanan nan mun ƙaddamar da labarin zuwa ɗaya daga cikin ayyukan Excel mafi amfani da ake kira VPR kuma ya nuna yadda za a iya amfani da shi don cire bayanan da ake buƙata daga rumbun adana bayanai zuwa cikin tantanin halitta. Mun kuma ambata cewa akwai lokuta biyu masu amfani don aikin VPR kuma daya kawai daga cikinsu yana mu'amala da tambayoyin bayanai. A cikin wannan labarin, za ku koyi wata ƙaramar sanannun hanyar yin amfani da aikin VPR a cikin Excel.
Idan baku yi wannan ba tukuna, to tabbas ku karanta labarin ƙarshe game da aikin VPR, domin duk bayanan da ke ƙasa suna ɗauka cewa kun riga kun saba da ƙa’idodin da aka kwatanta a talifi na farko.
Lokacin aiki tare da bayanan bayanai, ayyuka VPR an wuce wani mai ganowa na musamman, wanda ake amfani da shi don gano bayanan da muke son samu (misali, lambar samfur ko lambar tantance abokin ciniki). Dole ne wannan lambar ta musamman ta kasance a cikin ma'ajin bayanai, in ba haka ba VPR zai ba da rahoton kuskure. A cikin wannan labarin, za mu dubi wannan hanyar yin amfani da aikin VPRlokacin da babu id a cikin database kwata-kwata. Kamar dai aikin VPR canza zuwa yanayin ƙima, kuma ya zaɓi abin da bayanai don samar mana lokacin da muke son nemo wani abu. A wasu yanayi, wannan shine ainihin abin da ake buƙata.
Misali daga rayuwa. Mun saita aikin
Bari mu misalta wannan labarin tare da misali na rayuwa na gaske - ƙididdige kwamitocin dangane da ma'aunin tallace-tallace da yawa. Za mu fara da zaɓi mai sauƙi, sa'an nan kuma za mu rikita shi a hankali har sai kawai hanyar da za ta magance matsalar ita ce amfani da aikin. VPR. Labarin farko na aikin mu na almara shine kamar haka: idan mai siyarwa ya sami fiye da $ 30000 a cikin tallace-tallace a cikin shekara, to hukumarsa shine 30%. In ba haka ba, hukumar ta kasance kawai 20%. Bari mu sanya shi a cikin sigar tebur:
Mai sayarwa yana shigar da bayanan tallace-tallacen su a cikin tantanin halitta B1, kuma tsarin da ke cikin tantanin halitta B2 yana ƙayyade ƙimar hukumar daidai wanda mai sayarwa zai iya tsammanin. Hakanan, ana amfani da ƙimar da aka samu a cikin tantanin halitta B3 don ƙididdige jimlar hukumar da mai siyarwa ya kamata ya karɓa (kawai ninka sel B1 da B2).
Mafi ban sha'awa na tebur yana ƙunshe a cikin tantanin halitta B2 - wannan shine ma'auni don ƙayyade ƙimar hukumar. Wannan dabarar ta ƙunshi aikin Excel da ake kira IF (IF). Ga masu karatu waɗanda ba su san wannan aikin ba, zan bayyana yadda yake aiki:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Yanayin hujjar aiki ce da ke ɗaukar ƙimar ko wanne KODA GASKIYA (GASKIYA), ko KARYA (KARYA). A cikin misalin da ke sama, kalmar B1
Shin gaskiya ne cewa B1 bai kai B5 ba?
Ko kuma kuna iya faɗin ta daban:
Shin gaskiya ne cewa jimillar adadin tallace-tallace na shekara bai kai ƙimar kofa ba?
Idan muka amsa wannan tambayar YES (GASKIYA), sannan aikin ya dawo darajar idan gaskiya ne (darajar idan GASKIYA). A cikin yanayinmu, wannan zai zama darajar tantanin halitta B6, watau ƙimar hukumar lokacin da jimillar tallace-tallace ke ƙasa da bakin kofa. Idan muka amsa tambayar NO (KARYA) sai ya dawo darajar idan ƙarya (darajar idan KARYA). A cikin yanayinmu, wannan shine ƙimar tantanin halitta B7, watau ƙimar hukumar lokacin da jimillar tallace-tallace ke sama da bakin kofa.
Kamar yadda kake gani, idan muka ɗauki jimlar tallace-tallace na $ 20000, muna samun ƙimar kwamiti na 2% a cikin tantanin halitta B20. Idan muka shigar da darajar $40000, to, ƙimar hukumar za ta canza da 30%:
Haka teburin mu ke aiki.
Muna dagula aikin
Bari mu ƙara ɗan wahala abubuwa. Bari mu saita wani kofa: idan mai siyarwar ya sami fiye da $ 40000, to ƙimar hukumar ta ƙaru zuwa 40%:
Komai yana da sauƙi kuma a sarari, amma tsarin mu a cikin tantanin halitta B2 ya zama sananne sosai. Idan ka dubi dabarar, za ka ga cewa hujja ta uku na aikin IF (IF) ya juya zuwa wani cikakken aiki IF (IF). Ana kiran wannan ginin nesting ayyuka cikin juna. Excel da farin ciki yana ba da damar waɗannan gine-gine, kuma har ma suna aiki, amma sun fi wahalar karantawa da fahimta.
Ba za mu shiga cikin cikakkun bayanai na fasaha ba - me yasa kuma yadda yake aiki, kuma ba za mu shiga cikin nuances na rubuta ayyukan gida ba. Bayan haka, wannan labarin ne da aka sadaukar don aikin VPR, ba cikakken jagora ga Excel ba.
Ko yaya lamarin yake, tsarin yana samun rikitarwa! Menene idan muka gabatar da wani zaɓi don ƙimar kwamiti na 50% ga waɗanda masu siyar da suka yi sama da $ 50000 a tallace-tallace. Kuma idan wani ya sayar da fiye da $ 60000, shin zai biya 60% Commission?
Yanzu dabarar da ke cikin tantanin halitta B2, ko da an rubuta shi ba tare da kurakurai ba, ya zama ba za a iya karantawa ba. Ina tsammanin cewa akwai 'yan kaɗan waɗanda suke so su yi amfani da ƙididdiga tare da matakan 4 na gida a cikin ayyukan su. Dole ne a sami hanya mafi sauƙi?!
Kuma akwai irin wannan hanya! Aikin zai taimake mu VPR.
Muna amfani da aikin VLOOKUP don magance matsalar
Bari mu canza zane na teburin mu kadan. Za mu adana duk fage da bayanai iri ɗaya, amma shirya su ta sabuwar hanya mafi ƙaranci:
Ɗauki ɗan lokaci kuma tabbatar da sabon tebur Teburin ƙimar ya ƙunshi bayanai iri ɗaya da tebur ɗin da ya gabata.
Babban ra'ayin shine amfani da aikin VPR don ƙayyade adadin kuɗin fito da ake so bisa ga tebur Teburin ƙimar dangane da girman tallace-tallace. Lura cewa mai siyarwa na iya siyar da kaya akan adadin da bai kai ɗaya daga cikin ƙofofin biyar a cikin tebur ba. Misali, zai iya siyar akan $34988, amma babu irin wannan adadin. Bari mu ga yadda aikin yake VPR zai iya magance irin wannan yanayin.
Saka aikin VLOOKUP
Zaɓi cell B2 (inda muke son saka tsarin mu) kuma nemo KYAUTA (VLOOKUP) a cikin Laburaren Ayyuka na Excel: dabarbari (Formula) > Ma'aikatar Ayyuka (Laburare Aiki)> Nemo & Bayani (Nassoshi da tsararru).
Akwatin maganganu ya bayyana Hujjojin Aiki (Hujjojin aiki). Muna cika kimar muhawara ɗaya bayan ɗaya, farawa da Duba_darajar (Lookup_value). A cikin wannan misali, wannan shine jimlar adadin tallace-tallace daga tantanin halitta B1. Saka siginan kwamfuta a cikin filin Duba_darajar (Lookup_value) kuma zaɓi cell B1.
Na gaba, kuna buƙatar ƙayyade ayyukan VPRinda ake neman bayanai. A cikin misalinmu, wannan tebur ne Teburin ƙimar. Saka siginan kwamfuta a cikin filin Table_array (Table) kuma zaɓi dukan tebur Teburin ƙimarsai dai kai.
Na gaba, muna buƙatar tantance ko wane ginshiƙi ne don fitar da bayanai daga amfani da dabararmu. Muna sha'awar ƙimar hukumar, wanda ke cikin shafi na biyu na tebur. Saboda haka, don jayayya Col_index_num (Column_number) shigar da ƙimar 2.
Kuma a ƙarshe, mun gabatar da hujja ta ƙarshe - Duban kewayon (Duba_Tazara).
Muhimmi: yin amfani da wannan hujja ne ya haifar da bambanci tsakanin hanyoyin biyu na amfani da aikin VPR. Lokacin aiki tare da bayanan bayanai, hujja Duban kewayon (range_lookup) dole ne ya kasance yana da ƙima koyaushe KARYA (KARYA) don bincika daidai daidai. A cikin amfani da aikin VPR, Dole ne mu bar wannan filin fanko, ko shigar da ƙima KODA GASKIYA (GASKIYA). Yana da matuƙar mahimmanci a zaɓi wannan zaɓi daidai.
Don ƙarin bayani, za mu gabatar KODA GASKIYA (GASKIYA) a cikin filin Duban kewayon (Duba_Tazara). Kodayake, idan kun bar filin babu komai, wannan ba zai zama kuskure ba, tunda KODA GASKIYA ita ce tsohuwar darajarsa:
Mun cika dukkan sigogi. Yanzu muna danna OK, kuma Excel ya haifar mana da dabara tare da aiki VPR.
Idan muka gwada da dama daban-daban dabi'u don jimlar tallace-tallace adadin, sa'an nan za mu tabbatar da cewa dabara aiki daidai.
Kammalawa
Lokacin da aikin VPR yana aiki tare da bayanan bayanai, gardama Duban kewayon (range_lookup) dole ne a karɓa KARYA (KARYA). Kuma darajar shiga kamar yadda Duba_darajar (Lookup_value) dole ne ya wanzu a cikin bayanan. A takaice dai, yana neman daidai daidai gwargwado.
A cikin misalin da muka duba a cikin wannan labarin, babu buƙatar samun daidaitaccen wasa. Wannan shi ne yanayin lokacin da aikin VPR dole ne ya canza zuwa yanayin da ya dace don mayar da sakamakon da ake so.
Misali: Muna so mu ƙayyade adadin kuɗin da za a yi amfani da shi a cikin lissafin hukumar don mai sayarwa tare da adadin tallace-tallace na $ 34988. Aiki VPR ya dawo mana da darajar kashi 30%, wanda hakan yayi daidai. Amma me yasa dabarar ta zaɓi layin da ke ɗauke da daidai 30% ba 20% ko 40% ba? Menene ma'anar bincike kusanta? Mu fito fili.
Lokacin da jayayya Duban kewayon (interval_lookup) yana da ƙima KODA GASKIYA (GASKIYA) ko tsallake, aiki VPR yana maimaita ta cikin ginshiƙi na farko kuma yana zaɓar ƙimar mafi girma wacce ba ta wuce ƙimar nema ba.
Muhimmiyar maimaita: Don wannan makirci ya yi aiki, dole ne a jera ginshiƙin farko na tebur a cikin tsari mai hawa.