Ɗaya daga cikin mafi yawan waɗanda aka yi amfani da ita a cikin masu amfani da Excel shine aikin MATCH. Ayyukansa shine don ƙayyade lambar matsayi na kashi a cikin tsararren bayanai. Yana kawo mafi amfani idan aka yi amfani da shi tare da wasu masu aiki. Bari mu ga yadda aikin yake MATCHda kuma yadda za a iya amfani dashi a cikin aiki.
APPLICATION OF MATCH OPERATOR
Mai sarrafawa MATCH yana da nau'i na ayyuka "Hanyoyin sadarwa da zane-zane". Yana bincika takaddamaccen ƙayyadaddun cikin ƙayyadaddun lissafin kuma yana ɗaukar lambar sa a cikin wannan kewayon a cikin tantanin salula. A gaskiya, ko da sunansa ya nuna wannan. Har ila yau, idan aka yi amfani da shi tare da wasu masu aiki, wannan aikin ya sanar da su game da matsayin matsayi na takamaiman mahimmanci don aiwatar da wannan bayanan.
Mai amfani da haɗin gwiwar MATCH kama da wannan:
= MATCH (darajar bincike; duba jeri; [match_type])
Yanzu la'akari da waɗannan daga cikin waɗannan muhawara uku dabam.
"Sakamakon darajar" - Wannan shine kashi wanda ya kamata a samu. Zai iya samun rubutun rubutu, nau'i nau'i, kuma ya ɗauki darajar ma'ana. Wannan hujja zata iya kasancewa mai nunawa ga tantanin halitta wanda ya ƙunshi kowane nau'ikan da aka sama.
"Viewed array" shi ne adreshin kewayon inda darajar ta kasance. Matsayi ne na wannan kashi a cikin wannan tsararren da mai aiki ya kamata ya bayyana. MATCH.
"Tsarin Mapping" yana nuna ainihin matsala don bincika ko kuskure. Wannan hujja na iya samun dabi'u uku: "1", "0" kuma "-1". Idan "0" Mai aiki kawai yana nema ainihin wasa. Idan darajar ita ce "1", idan babu ainihin wasa MATCH ya ba da kashi mafi kusa da shi a cikin tsari mai saukowa. Idan darajar ita ce "-1", to, idan ba a samo ainihin wasa ba, aikin zai dawo da kashi mafi kusa da shi a cikin tsari mai girma. Yana da mahimmanci idan ba ka nemo ainihin daidai ba, amma kimanin ɗaya, don haka za a umarci tsararren da kake kallo a tsari mai girma (nau'in matching "1") ko saukowa (siffar mapping "-1").
Magana "Tsarin Mapping" ba da ake bukata ba. Ana iya rasa idan ba a buƙata ba. A wannan yanayin, darajarta ita ce "1". Aiwatar da gardama "Tsarin Mapping"Da farko, yana da hankali ne kawai idan ana kirkiro dabi'un lambobi, ba ƙimar rubutu ba.
Idan akwai MATCH tare da saitunan da aka ƙayyade ba za su iya samun abun da ake so ba, mai aiki ya nuna ɓata a cikin tantanin halitta "# N / A".
Lokacin gudanar da bincike, mai aiki ba ya bambanta tsakanin alamar takarda. Idan akwai matakan da suka dace a cikin tsararren, MATCH nuna matsayi na farko a cikin tantanin halitta.
Hanyarka 1: Nuna wurin da take a cikin kewayon bayanan rubutu
Bari mu dubi misali na mafi sauƙi, lokacin amfani MATCH Zaka iya ƙayyade wurin da aka ƙayyade a cikin tsararren bayanan rubutu. Nemo ko wane matsayi a cikin kewayon sunayen sunayen kaya, kalmar ne "Sugar".
- Zaɓi tantanin halitta wanda za'a samar da sakamakon sakamakon. Danna kan gunkin "Saka aiki" kusa da wannan tsari.
- Kaddamarwa Ma'aikata masu aiki. Bude wani jinsi "Jerin jerin jerin sunayen" ko "Hanyoyin sadarwa da zane-zane". A cikin jerin masu aiki muna neman sunan "MATCH". Gano da kuma zaɓar shi, danna maballin. "Ok" a kasan taga.
- An kunna maɓallin ƙarar aiki mai aiki. MATCH. Kamar yadda kake gani, a cikin wannan taga ta yawan adadin muhawara akwai matakan uku. Dole mu cika su.
Tunda muna bukatar mu sami matsayi na kalma "Sugar" a cikin iyakar, sa'annan ka fitar da wannan sunan a fagen "Sakamakon darajar".
A cikin filin "Viewed array" kana buƙatar saka bayanin haɗin kewayon kanta. Ana iya motsa shi da hannu, amma ya fi sauƙi a sanya siginan kwamfuta a cikin filin kuma zaɓi wannan tsararren a kan takardar, yayin riƙe da maɓallin linzamin hagu. Bayan haka, ana nuna adireshinsa a cikin muhawarar gardama.
A filin na uku "Tsarin Mapping" sanya lambar "0", tun da za muyi aiki tare da bayanan rubutu, sabili da haka muna buƙatar ainihin sakamakon.
Bayan an saita bayanai, danna kan maballin. "Ok".
- Shirin yana aiwatar da lissafin kuma ya nuna matsayin matsayi "Sugar" a cikin tsararren tsararrakin da muka ƙayyade a farkon mataki na wannan umarni. Matsayin matsayi zai daidaita "4".
Darasi: Maɓallin aiki na Excel
Hanyar 2: Yi amfani da na'ura na MATCH ta atomatik
A sama, munyi la'akari da yanayin da ta fi amfani da mai amfani MATCH, amma har ma ana iya sarrafa shi.
- Don saukakawa, za mu ƙara ƙarin filayen biyu a kan takardar: "Set Point" kuma "Lambar". A cikin filin "Set Point" muna motsawa cikin sunan da ake buƙatar samun. Bari a kasance a yanzu "Naman". A cikin filin "Lambar" saita siginan kwamfuta kuma je zuwa cikin taga na muhawarar masu aiki kamar yadda aka tattauna a sama.
- A cikin aikin gwaji a filin "Sakamakon darajar" saka adireshin tantanin halitta wanda aka shigar da kalmar "Naman". A cikin filayen "Viewed array" kuma "Tsarin Mapping" muna nuna irin wannan bayanan kamar yadda aka saba a baya - adireshin kewayon da lambar "0" bi da bi. Bayan haka, danna maballin "Ok".
- Bayan mun yi ayyukan da ke sama, a filin "Lambar" Kalmar kalma tana nunawa "Naman" a cikin zaɓin da aka zaba. A wannan yanayin, shi ne "3".
- Wannan hanya ce mai kyau saboda idan muna so mu san matsayi na wani suna, to ba zamu buƙatar sake sakewa ba ko canza wannan tsari a kowane lokaci. Kawai isa a filin "Set Point" shigar da sabon kalmar bincike maimakon na baya. Tsarin aiki da bayarwa na sakamakon bayan wannan zai faru ta atomatik.
Hanyar 3: Yi amfani da mai kula da MATCH don maganganun lambobi
Yanzu bari mu dubi yadda zaka iya amfani da su MATCH don aiki tare da maganganun lambobi.
Ɗawainiyar shine samo samfurin da ya dace da rubobi 400 ko mafi kusa da wannan adadin a cikin tsari mai girma.
- Da farko, muna buƙatar raba abubuwan a cikin shafi "Adadin" saukowa. Zaɓi wannan shafi kuma je shafin "Gida". Danna kan gunkin "Tsara da tace"wanda yake samuwa a kan tef a cikin toshe Ana gyara. A cikin jerin da aka bayyana, zaɓi abu "Yaɗa daga matsakaicin zuwa mafi girma".
- Bayan an gama fassarar, zaɓi tantanin tantanin halitta inda za a nuna sakamakon, sannan kuma kaddamar da maɓallin hujja kamar yadda aka bayyana a cikin hanyar farko.
A cikin filin "Sakamakon darajar" muna motsa cikin lamba "400". A cikin filin "Viewed array" saka ainihin haɗin shafi "Adadin". A cikin filin "Tsarin Mapping" saita darajar "-1"kamar yadda muke nema daidai da mafi girma daga wanda ake so. Bayan yin duk saituna danna maballin "Ok".
- Sakamakon aiki yana nunawa a cikin cell da aka ƙayyade. Wannan shine matsayi "3". Ya dace da "Dankali". Hakika, adadin kudaden shiga daga sayarwa wannan samfurin ita ce mafi kusa da lamba 400 a tsari mai girma da yawa zuwa 450 rubles.
Hakazalika, zaku iya nemo matsayi mafi kusa "400" saukowa. Sai kawai don haka kana buƙatar tace bayanai a cikin tsari mai girma, da kuma a filin "Tsarin Mapping" Ayyukan muhawara na nuna darajar "1".
Darasi: Kashe da tace bayanai a Excel
Hanyar 4: amfani da haɗin tare da wasu masu aiki
Wannan aikin yana da tasiri sosai don amfani da wasu masu aiki kamar wani ɓangare na tsari mai mahimmanci. Yawancin lokaci ana amfani dashi tare da aikin INDEX. Wannan hujja ta fitowa ga ƙayyadaddun tantanin halitta abinda ke ciki na kewayon da aka ƙayyade ta lamba ta jere ko shafi. Bugu da ƙari, ƙidayar, kamar yadda yake dangane da mai aiki MATCH, an yi ba zumunta ga dukan takardar ba, amma a cikin kewayon kawai. Haɗin aikin don wannan aiki shine kamar haka:
= INDEX (layi; line_number; column_number)
Bugu da ƙari, idan tashe-tashen hankula ya zama nau'i ɗaya, to sai kawai ɗaya daga cikin muhawara biyu za a iya amfani dashi: "Lambar layi" ko "Lambar shafi".
Fayil na aiki na ayyuka INDEX kuma MATCH shi ne cewa za a iya amfani da wannan karshen a matsayin hujja na farko, wato, don nuna matsayin jere ko shafi.
Bari mu dubi yadda za a iya yin haka a aikace, ta yin amfani da ɗaya tebur ɗaya. Ayyukan mu shine mu kawo cikin takarda "Samfur" sunan kaya, yawan adadin kuɗin daga wanda ya kasance daidai da 350 rubles ko mafi kusa da wannan darajar a cikin tsari. Wannan jayayya an ƙayyade a filin. "Adadin yawan kudaden shiga da takarda".
- Tsara abubuwa a cikin wani shafi "Adadin kudaden shiga" hawa. Don yin wannan, zaɓi shafi da ake buƙata kuma, kasancewa a cikin shafin "Gida", danna kan gunkin "Tsara da tace"sannan kuma a cikin menu da aka bayyana a kan abu "Yaɗa daga ƙananan zuwa iyakar".
- Zaɓi tantanin halitta a filin "Samfur" kuma kira Wizard aikin a hanyar da ta saba ta hanyar maɓallin "Saka aiki".
- A cikin taga wanda ya buɗe Ma'aikata masu aiki a cikin category "Hanyoyin sadarwa da zane-zane" nemi sunan INDEXzaɓi shi kuma danna maballin "Ok".
- Na gaba, taga yana buɗewa yana bada zaɓi na zaɓin ayyukan. INDEX: don tsararru ko don tunani. Muna buƙatar zaɓi na farko. Saboda haka, za mu bar dukkanin saitunan da ke cikin wannan taga kuma danna maballin "Ok".
- Ƙungiyar shawara ta buɗewa ta buɗe. INDEX. A cikin filin "Array" saka adreshin kewayon inda mai aiki INDEX za su bincika sunan samfurin. A yanayinmu, wannan shafi ne. "Sunan Samfur".
A cikin filin "Lambar layi" Ayyukan aikin nested za su kasance MATCH. Dole ne a kori ta cikin hannu ta hanyar amfani da haɗin da aka ambata a farkon labarin. Nan da nan rubuta sunan aikin - "MATCH" ba tare da fadi ba. Sa'an nan kuma bude sashi. Shawarar farko ta wannan afaretan ita ce "Sakamakon darajar". An samo a kan takardar a filin. "Adadin kudaden shiga". Saka bayanai akan tantanin halitta dauke da lambar 350. Mun sanya semicolon. Shawara ta biyu ita ce "Viewed array". MATCH za su duba iyakar da yawan kudaden shiga ke samo kuma neman mafi kusa da 350 rubles. Saboda haka, a cikin wannan yanayin, mun ƙayyade daidaitattun shafi "Adadin kudaden shiga". Bugu da muka sanya salo. Tambaya ta uku ita ce "Tsarin Mapping". Tun da za mu nema lamba daidai da wanda aka ba ko mafi kusa, za mu saita lambar a nan. "1". Rufe madatsai.
Tambaya na uku INDEX "Lambar shafi" bar blank. Bayan haka, danna maballin "Ok".
- Kamar yadda kake gani, aikin INDEX tare da taimakon mai aiki MATCH a cikin wayar da aka riga aka ƙayyade suna nuna sunan "Tea". Hakika, adadin daga sayar da shayi (300 rubles) shi ne mafi kusa da saukowa zuwa kashi 350 rubles daga dukkan dabi'u a cikin tebur ana sarrafawa.
- Idan muka canza lambar a filin "Adadin kudaden shiga" zuwa wani, za a sauke bayanan ta atomatik bisa ga yadda ya dace. "Samfur".
Darasi: Ayyukan Excel a Excel
Kamar yadda ka gani, mai aiki MATCH aiki ne mai matukar dace don ƙayyade lamba na lambar da aka ƙayyade cikin lissafin bayanai. Amma amfaninta yana kara muhimmanci idan an yi amfani dashi a cikin tsari mai mahimmanci.
