Haɓaka dabarun Excel don sarrafa tebur kamar pro

Kuna so ku koya fHaɓaka dabarun Excel don sarrafa tebur kamar pro? Idan kuna aiki tare da bayanai akai-akai, tabbas kun riga kun san cewa Microsoft Excel yana ɗaya daga cikin mafi yawan kayan aiki da ƙarfi don sarrafa bayanai. Ko kuna nazarin bayanan kuɗi, tsara kaya, ko ayyukan bin diddigin, Excel na iya zama mafi kyawun abokin ku. Duk da haka, da key Don samun mafi kyawun wannan kayan aiki, ya zama dole don ƙware ƙira da ayyuka na ci-gaba. A yau muna ba ku cikakken jagora don sarrafa allon kamar ƙwararrun ƙwararrun gaske.

A cikin wannan labarin, za mu bincika manyan dabarun Excel waɗanda zasu iya taimaka muku inganta ayyukanku, inganta tafiyar matakai y sarrafa bayanai ta hanya mafi inganci. Daga ayyuka masu ma'ana don bincike mai ƙarfi zuwa waɗanda ke sauƙaƙe ayyuka masu maimaitawa, zaku sami duk abin da kuke buƙata don ɗaukar ƙwarewar ku ta Excel zuwa mataki na gaba anan.

Ayyukan Hankali: Kayan aikin Yanke shawara

Ayyuka masu ma'ana suna da mahimmanci lokacin da kuke buƙata kimanta yanayi da kuma yanke shawara ta hanyar bayanai. Wasu daga cikin hanyoyin da aka fi amfani da su sun haɗa da:

• EE: Wannan aikin yana kimanta yanayi kuma yana mayar da ƙimar ɗaya idan gaskiya ne, wani kuma idan ƙarya ce. Misali, zaku iya amfani da = IF (A1>100, "Ya wuce", "Ba Ya Wuce") don bincika ko ƙimar ta zarce kofa.
• EH. KUSKURE: Mafi dacewa don kama kurakurai a cikin dabara. Misali, =IFERROR(A2/B2, "Error") yana hana saƙon kamar #DIV/0 fitowa.
• YS.SET: Yana ba da damar kimanta yanayi da yawa ba tare da buƙatar kafa su ba, yana sauƙaƙe bincike mai haske.

Duk da haka, dole ne mu gaya muku cewa a cikin Tecnobits Muna amfani da Microsoft Excel don ayyuka da yawa, shi ya sa muke da jagora irin wannan akan yadda ake samun kashi a cikin Excel. Amma ba haka kawai ba, Idan kayi amfani da injin bincike kuma shigar da kalmar Excel Za ku sami ƙarin jagora na nau'in yadda ake kirga sel da rubutu a cikin Excel.

Bincika da hanyoyin bincike

Ɗaya daga cikin mafi kyawun iyawar Excel shine ikon sa bincika kuma gano takamaiman bayanai cikin manyan bayanan bayanai. Ga wadanda suka fi fice:

• VLOOKUP: Ana amfani da shi don nemo ƙima a cikin ginshiƙi na farko na kewayo da dawo da bayanai masu alaƙa. Ko da yake yana da mashahuri sosai, yana da iyaka kamar bincike a tsaye kawai.
• INDEX da MATCH: Mafi sauƙaƙan madadin VLOOKUP, yana ba da damar bincike na tsaye da a kwance.
• XLOOKUP: Ƙarin aikin zamani wanda ya haɗu da mafi kyawun VLOOKUP da INDEX + MATCH, yana ba da ƙarin sassauci da sauƙi. daidaito.

Binciken bayanai tare da ayyukan ƙididdiga

Ga wadanda suke aiki tare ƙididdigar ƙididdigaExcel yana ba da kayan aikin ƙididdiga masu ƙarfi. Wasu mabuɗin ƙira sun haɗa da:

• MATAKI.IF: Yana dawo da matsakaicin sel waɗanda suka cika takamaiman ma'auni. Misali, = AVERAGEIF(A2: A10, ">100") yana ƙididdige matsakaicin ƙimar fiye da 100.
• SUM IDAN SATA: Mafi dacewa don ƙara ƙimar da suka dace da ma'auni da yawa. Mai amfani don ƙarin cikakkun bayanai kamar tallace-tallace ta yanki ko takamaiman lokuta.
• DARAJA: Yana rarraba ƙima dangane da wasu a cikin kewayon.

Manipulation rubutu

Sarrafa bayanai a tsarin rubutu shima aiki ne na gama gari a Excel. Waɗannan dabarun suna taimaka muku sarrafa su da kyau:

• CONCAT: Yana haɗa abubuwan da ke cikin sel da yawa zuwa ɗaya. Misali, = CONCAT (A1, »«, B1) yana haɗa dabi'u a cikin A1 da B1 tare da sarari tsakanin su.
• CIGABA: Yana ba da damar cire takamaiman haruffa daga zaren rubutu. Da amfani don tsaftace bayanan "datti".
• LURA: Yana dawo da tsayin rubutu, gami da sarari.

Nagartattun Formula don Teburan Pivot

Teburan pivot ba zai zama da amfani ba tare da taimakon ci-gaba da dabaru wanda ke inganta aikin su. Wasu daga cikin mafi amfani sune:

• MURUWA: Yana taimakawa aiki tare da jeri mai ƙarfi, musamman idan girman bayanan ku yana canzawa koyaushe.
• SUMPRODUCT: Haɓaka madaidaitan dabi'u a cikin jeri kuma ƙara sakamako, manufa don hadadden bincike na kudi.
• KWANAKI: Yana ƙididdige bambanci a cikin kwanaki tsakanin kwanakin biyu.

A wannan lokacin za ku sami ƙarin sani game da Excel, amma sama da duka, kuna da kaɗan kaɗan don gama koyon komai game da manyan hanyoyin Excel don sarrafa tebur kamar ƙwararru.

 

Automation tare da Macros da VBA

Idan ya zo ga maimaita matakai, macros da VBA (Visual Basic for Applications) harshe sune kayan aiki masu mahimmanci don ƙara yawan aiki:

• Ƙirƙirar macro: Yana ba ku damar yin rikodin ayyukan maimaitawa da aiwatar da su tare da dannawa ɗaya.
• Na ci gaba ta atomatik: VBA na iya tsara ayyuka masu rikitarwa kamar ƙirƙirar rahotanni ko samar da sigogi na al'ada.
• Tabbatar da bayanai: Tabbatar cewa kimar da aka yi rikodi sun bi bayani dalla-dalla da ake bukata

Ƙirƙirar waɗannan ayyuka na Excel ba kawai inganta ikon sarrafa bayanai ba, har ma yana taimaka muku yi ƙarin bayani yanke shawara y madaidaici. Ko wane fanni na aikin ku, haɗa waɗannan kayan aikin cikin ayyukan yau da kullun zai ba ku damar yin fice a cikin ayyukanku.

Kar a dade don samun kwarewa ikon ci-gaba na Excel a cikin ayyukanku. Muna fatan cewa a cikin wannan labarin akan manyan hanyoyin Excel don sarrafa tebur kamar ƙwararrun kun koyi abin da kuka sani. Mu hadu a labari na gaba Tecnobits!