Contents
Jiya a gudun marathon 30 Excel yana aiki a cikin kwanaki 30 mun gano cikakkun bayanai game da yanayin aikin mu tare da aikin takardunku (SANARWA) kuma ta gano cewa ba za ta iya taimaka mana da matsalolin ƙwaƙwalwa ba. Ba namu ba, ko ƙwaƙwalwar Excel!
A rana ta biyar na marathon, za mu yi nazarin aikin zabi (ZABI). Wannan aikin yana cikin rukuni Nassoshi da tsararru, yana dawo da ƙima daga lissafin yiwuwar zaɓuka bisa ga ma'aunin lamba. Ya kamata a lura cewa a mafi yawan lokuta yana da kyau a zabi wani aiki, misali, INDEX (INDEX) da MATCH (MORE EXPOSED) ko KYAUTA (VPR). Za mu rufe waɗannan fasalulluka daga baya a cikin wannan tseren marathon.
Don haka, bari mu juya ga bayanin da muke da shi da kuma misalai akan aikin zabi (ZABI), bari mu gan shi a aikace, kuma mu lura da rauni. Idan kuna da wasu nasihu da misalai na wannan fasalin, da fatan za a raba su a cikin sharhi.
Aiki 05: ZABI
aiki zabi (SELECT) yana dawo da ƙima daga jeri, yana zaɓar ta bisa ga ma'aunin lamba.
Ta yaya za ku yi amfani da aikin ZABI?
aiki zabi (SELECT) na iya mayar da abin da ke cikin lissafin a takamaiman lamba, kamar haka:
- Ta lambar wata, mayar da lambar kwata na kasafin kuɗi.
- Dangane da ranar farawa, lissafta ranar Litinin mai zuwa.
- Ta lambar shagon, nuna adadin tallace-tallace.
Syntax ZABI
aiki zabi (SELECT) yana da ma'ana mai zuwa:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- index_num (index_number) dole ne ya kasance tsakanin 1 zuwa 254 (ko 1 zuwa 29 a cikin Excel 2003 da baya).
- index_num (index_number) za a iya shigar da shi cikin aiki azaman lamba, dabara, ko nuni ga wata tantanin halitta.
- index_num (index_number) za a haɗa shi zuwa lamba mafi kusa.
- muhawara darajar (darajar) na iya zama lambobi, nassoshin tantanin halitta, jeri mai suna, ayyuka, ko rubutu.
Tarko ZABI (ZABI)
A cikin Excel 2003 da baya, aikin zabi (SELECT) yana goyan bayan hujjoji 29 kawai darajar (ma'ana).
Ya fi dacewa a bincika ta jeri akan takardar aiki fiye da shigar da duk abubuwan da ke cikin dabara. Tare da ayyuka KYAUTA (VLOOKUP) ko MATCH (MATCH) Kuna iya komawa zuwa lissafin ƙimar da ke cikin takaddun aikin Excel.
Misali 1: Kudi na kwata ta lambar wata
aiki zabi (SELECT) yana aiki lafiya tare da sauƙin lissafin lambobi azaman ƙima. Misali, idan cell B2 ya ƙunshi adadin watan, aikin zabi (SELECT) na iya ƙididdige ko wane kwata na kasafin kuɗi ya ke. A cikin misali mai zuwa, shekarar kasafin kuɗi ta fara a watan Yuli.
Ƙididdigar ta lissafa ƙima 12 daidai da watanni 1 zuwa 12. Shekarar kasafin kuɗi tana farawa a watan Yuli, don haka watanni 7, 8, da 9 sun fada cikin kwata na farko. A cikin teburin da ke ƙasa, zaku iya ganin lambar kwata na kasafin kuɗi a ƙarƙashin kowane lambar kowane wata.
A cikin aiki zabi (SELECT) Dole ne a shigar da lambar kwata a cikin tsarin da suka bayyana a cikin tebur. Misali, a cikin lissafin ƙimar ayyuka zabi (SELECT) a matsayi 7, 8 da 9 (Yuli, Agusta da Satumba) yakamata su zama lamba 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
Shigar da lambar wata a cikin cell C2, da aikin zabi (SELECT) zai lissafta lambar kwata na kasafin kuɗi a cikin tantanin halitta C3.
Misali 2: Lissafin ranar Litinin mai zuwa
aiki zabi (SELECT) na iya aiki a hade tare da aikin Mako (DAYWEEK) don lissafta kwanakin gaba. Misali, idan kai memba ne a kulob din da ke haduwa a duk ranar Litinin da yamma, to ta hanyar sanin ranar yau, za ka iya lissafin ranar Litinin mai zuwa.
Hoton da ke ƙasa yana nuna jerin lambobin kowace rana ta mako. Rukunin H na kowace rana ta mako ya ƙunshi adadin kwanakin da za a ƙara zuwa kwanan wata don samun Litinin mai zuwa. Misali, kuna buƙatar ƙara kwana ɗaya kawai zuwa Lahadi. Idan kuma yau litinin ne, to sauran kwana bakwai sai ranar litinin mai zuwa.
Idan kwanan wata na yanzu yana cikin cell C2, to tsarin da ke cikin cell C3 yana amfani da ayyuka Mako (DAY) kuma zabi (SELECT) don lissafta ranar Litinin mai zuwa.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Misali 3: Nuna adadin tallace-tallace na kantin da aka zaɓa
Kuna iya amfani da aikin zabi (SELECT) a hade tare da sauran ayyuka kamar SUM (SUM). A cikin wannan misali, za mu sami jimlar tallace-tallace don takamaiman kantin sayar da ta hanyar tantance lambar sa a cikin aikin zabi (SELECT) azaman hujja, da kuma jera jeri na bayanai na kowane kantin sayar da kayayyaki don ƙididdige jimlar.
A cikin misalinmu, an shigar da lambar kantin (101, 102, ko 103) a cikin cell C2. Don samun ƙima kamar 1, 2, ko 3 maimakon 101, 102, ko 103, yi amfani da dabarar: = C2-100.
Bayanan tallace-tallace na kowane kantin sayar da yana cikin wani shafi daban kamar yadda aka nuna a ƙasa.
Ciki aiki SUM Za a fara aiwatar da aikin (SUM). zabi (SELECT), wanda zai dawo da kewayon da ake so wanda ya dace da kantin da aka zaɓa.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
Wannan misali ne na yanayin da ya fi dacewa don amfani da wasu ayyuka kamar INDEX (INDEX) da MATCH (NENE). Daga baya a cikin marathon, za mu ga yadda suke aiki.