Koyi yadda ƙwarewa zai iya sauƙaƙa aiki a Excel
Tsarin yana kewayon ko rukuni na dabi'u masu dangantaka. A cikin shirye-shiryen sassauki irin su Excel da Google Spreadsheets, ana adana dabi'u a cikin tsararraki a cikin sel.
Amfani da Arrays
Za a iya amfani da jeri a cikin nau'i-nau'i guda biyu (tsararren tsari) kuma a matsayin muhawara don ayyuka kamar siffofin tsararru na ayyukan LOOKUP da ayyukan INDEX .
Nau'in Arrays
Akwai nau'ukan iri biyu a Excel:
- Ɗauki ɗaya mai girma, wanda aka sani da kayan aiki na vector ko vector. Bayanin yana samuwa:
- a cikin jere guda (daidaitaccen tsararren kwalliya).
- a cikin ɗigon ɗigon shafi guda ɗaya (ɗayan tsararren gefe ɗaya).
- Tsarin abubuwa biyu, wanda aka fi sani da matrix
- Ana samo bayanan a cikin ginshiƙai masu yawa ko layuka.
- Shafi na Table - Lissafin tebur yana ɗaya daga cikin muhawarar da aka yi amfani da su a aikace-aikace na Excel, kamar VLOOKUP da HLOOKUP.
- Ayyukan na LOOKUP suna nema kan layin tebur don neman bayani na musamman.
- Don VLOOKUP (neman nuni), table_array dole ne ya ƙunshi akalla biyu ginshiƙai na bayanai .
- Don HLOOKUP (neman kwance a kwance), table_array dole ne ya ƙunshi akalla layuka guda biyu na bayanai.
Array Formula Overview
Tsarin tsararren tsari shine wata hanyar da take fitar da ƙididdiga - kamar ƙari, ko ƙaddamarwa - a kan dabi'un a cikin ɗaya ko fiye da kayan aiki fiye da adadin bayanai ɗaya.
Tsarin tsafi:
- Yi amfani da ma'anar wannan kalma kamar tsari na yau da kullum (duk suna farawa tare da alamar misali (=) misali).
- Yi amfani da wannan ma'aikatan lissafi .
- kuma bi wannan tsari na aiki.
Ayyukan Array da Ayyuka na Excel
Yawancin aikace-aikacen da Excel ya gina - irin su SUM, AVERAGE, ko COUNT - ana iya amfani dashi a cikin tsari.
Har ila yau, akwai wasu ayyuka - irin su aikin TRANSPOSE - wanda dole ne a shigar dashi a matsayin tsararru domin ya yi aiki yadda ya dace.
Amfani da ayyuka da yawa kamar INDEX da MATCH ko MAX da kuma IF za a iya kara ta ta amfani dasu tare a cikin tsari.
CSE Formulas
A cikin Excel, madauran tsari yana kewaye da takalmin gyare-gyare " {} ". Wadannan takalmin ba za a iya tattake kawai ba amma dole ne a kara su zuwa wata takarda ta latsa Ctrl, Shift, kuma Shigar da mabuɗan bayan buga rubutu a cikin tantanin halitta ko kwayoyin.
Saboda wannan dalili, wani mahimman tsari ne a wasu lokutan ana kiransa azaman CSE a Excel.
Baya ga wannan ka'ida shi ne lokacin da ake amfani da takalmin gyare-gyare don shigar da tsararrakin azaman gardama don aikin da ya ƙunshi kawai ƙira ɗaya ko tantancewar sel .
Alal misali, a cikin koyas da ke ƙasa da ke amfani da VLOOKUP da kuma aikin da zaɓaɓɓen aikin don ƙirƙirar daftarin binciken hagu, an halicci tsararren don aikin binciken na Index_num ta hanyar buga takalmin gyare-gyare a cikin jeri.
Matakai na Halitta Tsarin Rage
- Shigar da dabara.
- Riƙe maɓallin Ctrl da Shift a kan keyboard.
- Latsa kuma saki maɓallin Shigar don ƙirƙirar lissafi.
- Saki Ctrl da makullin Shift .
Idan aka yi daidai, zancen ƙira zai kewaye ta da takalmin gyare-gyare kuma kowace tantanin halitta da ke riƙe da wannan tsari zai ƙunshi wani sakamako dabam.
Editing wani Formula Array
Duk lokacin da aka samarda lissafin tsararren gyare-gyaren gyaran gyare-gyaren gyare-gyare ya ɓace daga keɓaɓɓun tsari.
Don dawo da su, dole ne a shigar da tsarin da aka tsara ta danna maɓallin Ctrl, Shift, kuma Shigar da maimaita kamar yadda a lokacin da aka fara kirkira tsari.
Nau'in Formats na Array
Akwai nau'i biyu na tsararren tsari:
- Tsarin tsararraki na maha-cell, inda irin wannan tsari yake samuwa a cikin salula fiye da ɗaya a cikin takardun aiki;
- Kayan tsari guda ɗaya, inda wata takarda ta haifar da ƙididdiga masu yawa a cikin ɗayan ɗigon ɗawainiya ɗaya.
Tsarin Mulki na Tsarin Multi-Cell
Kamar sunansu yana nunawa, waɗannan matakan tsararren suna samuwa a cikin ɗakunan Kayan aiki masu yawa sannan kuma sun dawo da tsararren azaman amsar.
A wasu kalmomi, irin wannan tsari yana samuwa a cikin ƙwayoyin biyu ko fiye kuma ya karbi amsoshi daban-daban a kowace tantanin halitta.
Yaya ake yin wannan ko wane kwafi ko misali na lissafin jigidar yana yin lissafi a cikin kowane tantanin da yake cikin, amma kowane misali na tsari yayi amfani da bayanai daban-daban a cikin lissafi kuma, sabili da haka, kowane misali yana samar da sakamakon daban-daban.
Misali na tsararren tsararren tsararren tsararren tsararrakin halitta shine:
{= A1: A2 * B1: B2}
Kwayoyin Kayan Kwayoyin Cell guda
Wannan nau'i na biyu na tsararren tsari yana amfani da aiki - irin su SUM, AVERAGE, ko COUNT - don hada da fitarwa daga tsarin mahaɗin tantanin halitta a cikin nau'i ɗaya a cikin tantanin halitta daya.
Misali na tsarin tsararren salula ɗaya shine:
{= SUM (A1: A2 * B1: B2)}