Contents
Kuna da allunan da ke da bayanai a cikin Excel waɗanda za a iya daidaita su, watau adadin layuka (ginshiƙai) na iya ƙaruwa ko raguwa a yayin aikin? Idan girman tebur "suna iyo", to dole ne ku ci gaba da saka idanu a wannan lokacin kuma ku gyara shi:
- hanyoyin haɗin kai a cikin dabarun rahoto waɗanda ke komawa teburin mu
- farkon jeri na pivot tebur waɗanda aka gina bisa ga teburin mu
- farkon jeri na sigogi da aka gina bisa ga teburin mu
- jeri don zazzagewa waɗanda ke amfani da teburin mu azaman tushen bayanai
Duk wannan gaba ɗaya ba zai bari ka gundura 😉
Zai zama mafi dacewa kuma daidai don ƙirƙirar kewayon "roba" mai ƙarfi, wanda zai daidaita girman kai tsaye zuwa ainihin adadin layuka da ginshiƙan bayanai. Don aiwatar da wannan, akwai hanyoyi da yawa.
Hanyar 1. Smart tebur
Haskaka kewayon sel kuma zaɓi daga shafin Gida - Tsarin azaman Tebur (Gida - Tsarin azaman Tebur):
Idan baku buƙatar ƙirar ɗigon da aka ƙara zuwa teburin azaman sakamako na gefe, to zaku iya kashe shi akan shafin da ya bayyana. Mai Gine-gine (Zane). Kowane tebur da aka ƙirƙira ta wannan hanyar yana karɓar suna wanda za'a iya maye gurbinsa tare da mafi dacewa a wuri ɗaya akan shafin Mai Gine-gine (Zane) a cikin filin Sunan tebur (Sunan Tebu).
Yanzu za mu iya amfani da hanyoyin haɗin kai masu ƙarfi zuwa “smart tebur”:
- Table 1 - hanyar haɗi zuwa teburin duka ban da layin kai (A2: D5)
- Tebur 1[#All] - haɗi zuwa dukan tebur (A1: D5)
- Table1[Bitrus] - nuni ga kewayon ginshiƙi ba tare da rubutun tantanin halitta na farko ba (C2: C5)
- Tebur 1[# Headers] - hanyar haɗi zuwa "kai" tare da sunayen ginshiƙan (A1: D1)
Irin waɗannan nassoshi suna aiki sosai a cikin ƙididdiga, misali:
= SUM (Table1[Moscow]) - lissafin jimlar ga shafi "Moscow"
or
= VPR (F5;Table 1;3;0) - bincika a cikin tebur na watan daga cell F5 kuma ku fitar da jimlar St. Petersburg don shi (menene VLOOKUP?)
Ana iya samun nasarar amfani da irin waɗannan hanyoyin haɗin gwiwa yayin ƙirƙirar tebur mai mahimmanci ta zaɓi akan shafin Saka - Tebur Pivot (Saka - Tebur Pivot) da shigar da sunan tebur mai wayo azaman tushen bayanai:
Idan ka zaɓi guntu irin wannan tebur (misali, ginshiƙai biyu na farko) kuma ka ƙirƙiri zane na kowane nau'in, sannan lokacin ƙara sabbin layi, za a ƙara su kai tsaye zuwa zane.
Lokacin ƙirƙirar jerin zaɓuka, ba za a iya amfani da hanyoyin haɗin kai kai tsaye zuwa abubuwan tebur masu wayo ba, amma zaka iya samun sauƙin wannan iyakance ta amfani da dabarar dabara - yi amfani da aikin. GASKIYA (GASKIYA), wanda ke juya rubutun zuwa hanyar haɗi:
Wadancan. hanyar haɗi zuwa tebur mai wayo a cikin nau'in zaren rubutu (a cikin alamomin zance!) yana juya zuwa cikakkiyar hanyar haɗin yanar gizo, kuma jerin abubuwan da aka saukar akai-akai suna gane shi.
Hanyar 2: Tsayin mai suna mai ƙarfi mai ƙarfi
Idan juya bayanan ku a cikin tebur mai wayo ba a so don wasu dalilai, to, zaku iya amfani da ɗan ƙaramin rikitarwa, amma mafi dabara da dabara - ƙirƙirar kewayon mai ƙarfi mai ƙarfi a cikin Excel wanda ke nufin teburin mu. Sa'an nan, kamar yadda a cikin hali na mai kaifin baki tebur, za ka iya yardar kaina amfani da sunan kewayon halitta a kowace dabara, rahotanni, charts, da dai sauransu Bari mu fara da wani sauki misali:
Task: Yi kewayo mai ƙarfi mai ƙarfi wanda zai koma zuwa jerin biranen kuma ta atomatik mikewa da raguwa yayin ƙara sabbin birane ko share su.
Za mu buƙaci ginanniyar ayyukan Excel guda biyu da ake samu a kowace siga - POICPOZ (MATSAYI) don ƙayyade tantanin halitta na ƙarshe na kewayon, da INDEX (INDEX) don ƙirƙirar hanyar haɗi mai ƙarfi.
Nemo tantanin halitta ta ƙarshe ta amfani da MATCH
MATCH (darajar dubawa, kewayon, nau'in wasa_match) – aikin da ke nemo ƙimar da aka bayar a cikin kewayo (jeri ko ginshiƙi) kuma yana mayar da ainihin lambar tantanin halitta inda aka samo shi. Misali, dabarar MATCH (“Maris”; A1:A5;0) zai dawo da lamba 4 a sakamakon haka, saboda kalmar “Maris” tana cikin tantanin halitta na hudu a shafi A1: A5. Hujjar aiki ta ƙarshe Match_Type = 0 yana nufin muna neman daidai daidai. Idan ba a ƙayyade wannan hujja ba, to, aikin zai canza zuwa yanayin bincike don mafi ƙarancin ƙima - wannan shine ainihin abin da za a iya samun nasarar amfani da shi don nemo tantanin halitta na ƙarshe a cikin tsararrun mu.
Ma'anar dabara mai sauƙi ne. MATCH yana neman sel a cikin kewayon daga sama zuwa ƙasa kuma, a ka'idar, yakamata ya tsaya lokacin da ya sami mafi ƙarancin ƙima ga wanda aka bayar. Idan ka ƙididdige ƙimar da babu shakka mafi girma fiye da kowane samuwa a cikin tebur a matsayin ƙimar da ake so, to MATCH zai kai ƙarshen teburin, sami komai kuma ya ba da jerin jerin tantanin halitta da aka cika. Kuma muna bukata!
Idan akwai lambobi kawai a cikin tsararrun mu, to muna iya ƙididdige lamba a matsayin ƙimar da ake so, wanda a fili ya fi kowane ɗayan waɗanda ke cikin tebur:
Don garanti, zaku iya amfani da lambar 9E + 307 (sau 9 10 zuwa ikon 307, watau 9 tare da sifili 307) - matsakaicin adadin da Excel zai iya aiki da shi bisa manufa.
Idan akwai darajar rubutu a cikin rukuninmu, to, a matsayin daidai da mafi girman lambar da za a iya, za ku iya saka ginin REPEAT ("i", 255) - kirtani na rubutu wanda ya ƙunshi haruffa 255 "i" - harafin ƙarshe na haruffa. Tunda Excel a zahiri yana kwatanta lambobin haruffa lokacin bincike, kowane rubutu a cikin tebur ɗinmu zai zama “ƙananan” a zahiri fiye da layin “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”)
Ƙirƙirar hanyar haɗi ta amfani da INDEX
Yanzu da muka san matsayi na ƙarshe mara komai a cikin tebur, ya rage don samar da hanyar haɗi zuwa gaba ɗaya kewayon mu. Don wannan muna amfani da aikin:
INDEX(kewaya; row_num; column_num)
Yana ba da abun ciki na tantanin halitta daga kewayon layi da lambar shafi, watau misali, aikin = INDEX (A1: D5; 3; 4) a cikin teburin mu tare da birane da watanni daga hanyar da ta gabata za ta ba 1240 - abun ciki daga layi na 3 da shafi na 4, watau sel D3. Idan ginshiƙi ɗaya ne kawai, to ana iya barin lambarsa, watau dabarar INDEX (A2: A6; 3) za ta ba da “Samara” a hoton da ya gabata.
Kuma akwai wanda ba a bayyane yake ba: idan INDEX ba kawai ya shiga cikin tantanin halitta ba bayan alamar =, kamar yadda aka saba, amma ana amfani dashi azaman ɓangaren ƙarshe na nunin kewayon bayan ciwon, to, ba zai sake ba da baya ba. abinda ke cikin tantanin halitta, amma adireshinsa! Don haka, dabara kamar $A$2:INDEX($A$2:$A$100;3) za ta ba da nuni ga kewayon A2:A4 a wurin fitarwa.
Kuma wannan shine inda aikin MATCH ya shigo, wanda muke sakawa a cikin INDEX don tantance ƙarshen jerin:
=$A$2:INDEX($A$2:$A$100; MATSALAR (REP ("I";255);A2:A100))
Ƙirƙiri kewayon mai suna
Ya rage don tattara shi duka cikin gaba ɗaya. Bude shafi tsari (Formula) Kuma danna Mai sarrafa suna (Name Manager). A cikin taga da ya buɗe, danna maɓallin Create (sabon), shigar da sunan kewayon mu da dabara a cikin filin range (Magana):
Ya rage don dannawa OK kuma ana iya amfani da kewayon shirye-shiryen a cikin kowace dabara, jerin abubuwan da aka saukar ko ginshiƙi.
- Amfani da aikin VLOOKUP don haɗa tebur da ƙimar bincike
- Yadda ake ƙirƙirar jerin zaɓuka masu yawan jama'a ta atomatik
- Yadda ake ƙirƙirar tebur pivot don tantance adadi mai yawa na bayanai