01 na 01
Ayyukan Kwaskwarimar Excel na Excel a cikin takardar Array
Ayyukan aikin Lissafi na Excel yana da nau'i biyu: siffar Vector da takardar Array .
Nau'in tsararren aikin aikin LOOKUP yana kama da sauran ayyukan bincike na Excel kamar VLOOKUP da HLOOKUP a cikin cewa za'a iya amfani da shi don gano ko duba abubuwa masu mahimmanci da ke cikin tebur na bayanai.
Yadda ya bambanta ita ce:
- Tare da VLOOKUP da HLOOKUP, za ka iya zaɓar wane shafi ko jere don dawo da bayanan kuɗi daga, yayin da LOOKUP ko da yaushe ya dawo darajar daga jere na ƙarshe ko shafi a cikin tsararren .
- A ƙoƙarin neman matsala don ƙimar da aka ƙayyade - da aka sani da Lookup_value - VLOOKUP ne kawai ke nema shafin farko na bayanai da HLOOKUP kawai jere na farko, yayin da aikin LOOKUP zai bincika ko dai jere na farko ko shafi dangane da siffar jeri .
HANYAR GASKIYA DA FASHIN KUMA
Halin tsararru - ko yana da murabba'i (adadin ginshiƙai da layuka) daidai da maɓallin rectangle (lambar adadin marasa daidaito da layuka) - rinjayar inda aikin LOOKUP ke nema don bayanai:
- Idan wani tsararraki yana da siffar fasali ko kuma idan yana da tsayi madaidaiciya (girmansa ya fi girma), LOOKUP ya ɗauka cewa an tsara bayanai a ginshiƙai kuma don haka nema nemo wani wasa da Lookup_value a cikin sashin farko na jeri.
- Idan tsararraki yana da madaidaiciya madaidaicin (ya fi tsayi fiye da shi), LOOKUP ya ɗauka cewa an tsara bayanai a cikin layuka kuma don haka nema nema don wasa da Lookup_value a jere na farko na tsararren.
Hanyoyin Gudanar da Ayyukan LOKACI DA GUDATARWA - Fassara Form
Rubutun da aka rubuta na Formar Array na aikin LOOKUP shine:
= Binciken (Lookup_value, Array)
Lookup_value (da ake bukata) - darajar da aikin ke nema a cikin tsararren. Ƙaƙidar Lookup_value na iya zama lamba, rubutu, ma'ana mai mahimmanci, ko sunan ko ƙirar salula wanda yana nufin darajar.
Array (da ake buƙata) - Kwayoyin jeri wanda aikin ke nema don gano Lookup_value. Bayanai na iya zama rubutu, lambobi, ko dabi'u masu mahimmanci.
Bayanan kula:
- Don aikin aikin LOOKUP ya yi aiki daidai, dole ne a rarraba hujjar Array cikin tsari mai girma (A zuwa Z ko mafi ƙanƙanta ga mafi girma ga lambobi)
- Idan aikin ba zai iya samo daidai matsala ga Lookup_value ba, zai zaɓi mafi girma a cikin Array wanda bai cancanta ba ko kuma daidai da darajar Lookup_value
- Idan watau Lookup_value ya ɓace ko žarfi fiye da duk dabi'u a cikin Array, aikin LOOKUP zai dawo da kuskuren N / A
Misali Yin Amfani da Fannin Rubuce-rubuce na Taswirar LOOKUP
Kamar yadda aka gani a cikin hoton da ke sama, wannan misali zai yi amfani da Array Form na aikin LOOKUP don samun farashin Whachamacallit a lissafin kaya.
Halin tsararren yana da tsayi mai mahimmanci . Sakamakon haka, aikin zai dawo da darajar da aka samo a cikin shafi na karshe na lissafin kaya.
Bayar da Bayanan
Kamar yadda aka nuna a cikin bayanan da ke sama, dole ne a ware jigilar bayanai a cikin tsararru don yin hakan don haka aikin LOOKUP zai yi aiki yadda ya dace.
Lokacin da zazzage bayanai a cikin Excel ya zama dole don farko zaɓi ginshiƙai da layuka na bayanai da za a rarraba. Yawanci wannan ya haɗa da rubutun shafi.
- Sanya lambobin A4 zuwa C10 a cikin takardun aiki
- Danna kan Data shafin na ribbon menu
- Danna maɓallin Zabin a cikin tsakiyar rubutun don buɗe Rubutun maganganu
- A ƙarƙashin ginshiƙan Shirin a cikin akwatin maganganun zaɓa don raba ta Sashe daga jerin zaɓuka na saukewa
- Idan ya cancanta, a ƙarƙashin Yaɗa a kan zaɓar Ƙimar daga zaɓuɓɓukan jerin zaɓuka
- Idan ya cancanta, a karkashin Dokar Sayi zaɓi A zuwa Z daga jerin zaɓuka na saukewa
- Danna Ya yi don warware bayanai da rufe akwatin maganganu
- Tsarin bayanan bayanai ya kamata ya dace da abin da aka gani a cikin hoto a sama
Sakamakon aikin haɗi Misali
Kodayake yana yiwuwa a yi kawai aikin aikin LOOKUP
= LOKACI (A2, A5: C10)
a cikin saitunan aiki, mutane da yawa sun fi sauƙi don amfani da akwatin maganganun aikin.
Maganar maganganu tana baka damar shigar da kowace gardama a kan rabaccen layi ba tare da damuwa game da haɗin aiki ba - irin su parenthesis da rabuwa tsakanin tsakanin jayayya.
Matakai da ke ƙasa dalla-dalla yadda aka shigar da aikin LOOKUP cikin cell B2 ta amfani da akwatin maganganu.
- Danna sel B2 a cikin takardun aiki don sanya shi tantanin halitta ;
- Danna maɓallin Formulas ;
- Zabi Duba da kuma Magana daga ribbon don buɗe jerin aikin sauke aikin;
- Danna kan LOOKUP a cikin jerin don kawo sama da Zaɓin zance zane-zane;
- Danna kan lookup_value, zaɓi tsararren a jerin;
- Danna Ya yi don gabatar da akwatin maganganu na Magana ;
- A cikin akwatin maganganu, danna kan layin Lookup_value ;
- Danna kan A2 a cikin takardun aiki don shigar da wannan tantanin halitta a cikin akwatin maganganu;
- Danna kan Array line a cikin maganganun maganganu
- Sanya siffofin A5 zuwa C10 a cikin takardun aiki don shigar da wannan tashar a cikin akwatin maganganun - wannan tashar yana dauke da duk bayanan da za a bincike ta wurin aikin
- Danna Ya yi don kammala aikin kuma rufe akwatin maganganu
- An sami kuskuren N / A a cikin cell E2 saboda har yanzu ba mu rubuta sunan ɓangaren cikin tantanin halitta D2 ba
Shigar da Darajar Bincike
- Danna kan salula A2, rubuta Whachamacallit kuma danna maɓallin Shigar da ke keyboard;
- Darajar $ 23.56 ya kamata ya bayyana cikin tantanin halitta B2 kamar yadda wannan farashi na Whachamacallit yake a cikin shafi na karshe na layin bayanai;
- Gwada aikin ta buga wasu sassan sunaye a cikin cell A2. Farashin ga kowane ɓangare a cikin lissafin zai bayyana a tantanin halitta B2;
- Lokacin da ka danna kan tantanin halitta E2 cikakken aikin = LITTAFI (A2, A5: C10) ya bayyana a cikin maɓallin tsari a sama da takardun aiki.