TOP 15 dabaru a cikin Excel

Tabbas Excel yana ɗaya daga cikin shirye-shirye masu mahimmanci. Ya sauƙaƙa rayuwar masu amfani da yawa. Excel yana ba ku damar sarrafa kansa har ma da ƙididdiga masu rikitarwa, kuma wannan shine babban fa'idar wannan shirin.

A matsayinka na mai mulki, mai amfani mai amfani yana amfani da ƙayyadaddun ayyuka kawai, yayin da akwai ƙididdiga masu yawa waɗanda ke ba ka damar aiwatar da ayyuka iri ɗaya, amma da sauri.

Wannan na iya zama da amfani idan koyaushe kuna yin ayyuka da yawa iri ɗaya waɗanda ke buƙatar ayyuka masu yawa.

Ya zama mai ban sha'awa? Sa'an nan maraba da sake dubawa na mafi amfani 15 Excel formulations.

Wasu kalmomi

Kafin ka fara nazarin ayyukan kai tsaye, kana buƙatar fahimtar abin da yake. Wannan ra'ayi yana nufin tsarin da masu haɓakawa suka shimfida, bisa ga abin da ake yin lissafin kuma ana samun wani sakamako a wurin fitarwa. 

Kowane aiki yana da manyan sassa biyu: suna da hujja. Ƙididdiga na iya ƙunshi aiki ɗaya ko da yawa. Don fara rubuta shi, kuna buƙatar danna sau biyu akan tantanin halitta da ake buƙata kuma rubuta alamar daidai.

Bangare na gaba na aikin shine sunan. A zahiri, shine sunan dabarar, wanda zai taimaka Excel fahimtar abin da mai amfani yake so. Yana biye da gardama a cikin baka. Waɗannan sigogin ayyuka ne waɗanda ake la'akari da su don yin wasu ayyuka. Akwai nau'ikan muhawara da yawa: lamba, rubutu, ma'ana. Hakanan, maimakon su, ana amfani da nassoshi ga sel ko takamaiman kewayon. Kowace gardama ta rabu da ɗayan tare da wani yanki.

Syntax ɗaya ne daga cikin manyan ra'ayoyin da ke siffanta aiki. Wannan kalmar tana nufin samfuri don saka wasu ƙididdiga don sa aikin yayi aiki.

Kuma yanzu bari mu duba duk wannan a aikace.

Formula 1: VLOOKUP

Wannan aikin yana ba da damar samun bayanan da ake buƙata a cikin tebur, da nuna sakamakon da aka dawo a cikin takamaiman tantanin halitta. Gajartawar sunan aikin tana nufin “Kallo a tsaye”.

ginin kalma

Wannan dabara ce mai rikitarwa wacce ke da mahawara guda 4, kuma amfani da ita yana da fasali da yawa.

Haɗin aikin shine:

= VLOOKUP (darajar dubawa, tebur, lamba_lambar, [range_lookup])

Bari mu yi dubi a tsanake a kan dukkan hujjojin:

1. Darajar duba sama.
2. Tebur. Wajibi ne cewa akwai ƙimar dubawa a cikin ginshiƙi na farko, da kuma ƙimar da aka dawo. Na karshen yana a ko'ina. Mai amfani zai iya yanke shawarar inda zai saka sakamakon dabara da kansa. 
3. Lambar ginshiƙi.
4. Kallon tazara. Idan wannan bai zama dole ba, to zaku iya barin ƙimar wannan hujja. Maganar boolean ce da ke nuna madaidaicin matakin da aikin ya kamata ya samu. Idan an ƙayyade ma'anar "Gaskiya", to Excel zai nemi ƙimar mafi kusa da wanda aka ƙayyade azaman ƙimar bincike. Idan an ƙayyade ma'aunin "Ƙarya", to aikin zai bincika kawai waɗannan dabi'un da ke cikin shafi na farko.

A cikin wannan hoton hoton, muna ƙoƙarin gano ra'ayoyi nawa aka samar don tambayar "sayi kwamfutar hannu" ta amfani da dabara.

Formula 2: Idan

Wannan aikin yana da mahimmanci idan mai amfani yana son saita takamaiman yanayin da yakamata a ƙididdige ƙima ko fitarwa. Yana iya ɗaukar zaɓuɓɓuka biyu: gaskiya da ƙarya.

ginin kalma

Tsarin wannan aikin yana da manyan dalilai guda uku, kuma yayi kama da haka:

=IF(ma'ana_expression, "darajar_if_gaskiya", "daraja_if_ƙarya").

Anan, magana ta hankali tana nufin dabara da ke bayyana ma'auni kai tsaye. Tare da taimakonsa, za a bincika bayanan don biyan wani yanayi. Saboda haka, hujjar "darajar idan ƙarya" an yi niyya ne don aiki ɗaya, tare da kawai bambanci shi ne cewa madubi ne akasin ma'ana. A cikin kalmomi masu sauƙi, idan ba a tabbatar da yanayin ba, to shirin yana yin wasu ayyuka.

Akwai wata hanya ta amfani da aikin IF – gida ayyuka. Za a iya samun ƙarin sharuɗɗa da yawa a nan, har zuwa 64. Misalin dalilin da ya dace da dabarar da aka bayar a cikin hoton hoto shine kamar haka. Idan cell A2 yayi daidai da biyu, to kuna buƙatar nuna darajar "Ee". Idan yana da wata ƙima daban, to kuna buƙatar bincika idan tantanin halitta D2 yayi daidai da biyu. Idan eh, to kuna buƙatar dawo da ƙimar "a'a", idan a nan yanayin ya zama ƙarya, to dabarar yakamata ta dawo da ƙimar "wataƙila".

Ba a ba da shawarar yin amfani da ayyukan gida sau da yawa ba, saboda suna da wahalar amfani da su, kurakurai suna yiwuwa. Kuma zai dauki lokaci mai tsawo kafin a gyara su. 

aiki IF Hakanan ana iya amfani da su don tantance ko wani tantanin halitta ba komai. Don cimma wannan burin, ana buƙatar ƙarin aiki guda ɗaya - ISBLANK.

Anan maganar ita ce:

=IF (ISBLANK (lambar cell),"Ba komai","Ba komai ba").

Bugu da ƙari, yana yiwuwa a yi amfani da shi maimakon aikin ISBLANK yi amfani da daidaitaccen tsari, amma saka cewa a ɗauka cewa babu ƙima a cikin tantanin halitta.

IDAN - Wannan yana daya daga cikin ayyuka na yau da kullum wanda yake da sauƙin amfani kuma yana ba ku damar fahimtar yadda wasu dabi'u suke gaskiya, samun sakamako don ma'auni daban-daban, da kuma ƙayyade ko wani tantanin halitta fanko ne.

Wannan aikin shine ginshiƙin wasu ƙididdiga. Yanzu za mu yi nazarin wasu daga cikinsu dalla-dalla.

Formula 3: SUMIF

aiki SUMMESLI yana ba ku damar taƙaita bayanan, dangane da bin ƙa'idodinsu.

ginin kalma

Wannan aikin, kamar wanda ya gabata, yana da gardama guda uku. Don amfani da shi, kuna buƙatar rubuta irin wannan dabarar, canza dabi'un da ake buƙata a wuraren da suka dace.

= SUMIF (kewaye, yanayi, [ jimlar_range ])

Bari mu fahimci dalla-dalla menene kowace hujjar:

1. Sharadi. Wannan gardamar tana ba ku damar wuce sel zuwa aikin, waɗanda ke da ƙari ga taƙaitawa.
2. Kewayon taƙaitawa. Wannan hujja na zaɓi ne kuma yana ba ku damar tantance sel don taƙaita idan yanayin karya ne.

Don haka, a cikin wannan yanayin, Excel ya taƙaita bayanai akan waɗannan tambayoyin inda adadin canjin ya wuce 100000.

Formula 4: SUMMESLIMN

Idan akwai sharuɗɗa da yawa, to ana amfani da aikin da ke da alaƙa SUMMESLIMN.

ginin kalma

Tsarin wannan aikin yayi kama da haka:

=SUMIFS(takaitaccen kewayon, yanayin_range1, yanayin1, [sharadi_range2, yanayin2],…)

Ana buƙatar muhawara ta biyu da ta uku, wato "Range of condition 1" da "Range of condition 1".

Formula 5: COUNTIF da COUNTIFS

Wannan aikin yana ƙoƙarin ƙayyade adadin ƙwayoyin da ba na wofi ba waɗanda suka dace da yanayin da aka bayar a cikin kewayon da mai amfani ya shigar.

ginin kalma

Don shigar da wannan aikin, dole ne ku ƙididdige wannan dabara:

= COUNTIF (fanni, sharudda)

Menene dalilan da aka bayar ke nufi?

1. Kewayo shine saitin sel waɗanda za a gudanar da ƙidayar tsakanin su.
2. Ma'auni - yanayin da aka yi la'akari lokacin zabar sel.

Misali, a cikin wannan misali, shirin ya ƙidaya adadin mahimman tambayoyin, inda adadin dannawa a cikin injin bincike ya wuce dubu ɗari. Sakamakon haka, dabarar ta dawo da lamba 3, wanda ke nufin akwai irin waɗannan kalmomi guda uku.

Magana game da aikin da ke da alaƙa COUNTIFS, sannan shi, kamar misalin da ya gabata, yana ba da damar yin amfani da ma'auni da yawa a lokaci ɗaya. Tsarinsa shine kamar haka:

=COUNTIFS(sharadi_range1, yanayin1, [sharadi_range2, yanayin2],…)

Kuma kamar na baya, "Sharadi na 1" da "Sharadi na 1" ana buƙatar muhawara, yayin da wasu za a iya tsallake su idan babu irin wannan bukata. Matsakaicin aikin yana ba da damar yin amfani da har zuwa jeri 127 tare da yanayi.

Formula 6: IFERROR

Wannan aikin yana dawo da ƙayyadaddun ƙimar mai amfani idan an sami kuskure yayin kimanta ƙima. Idan darajar da aka samu daidai ne, ta bar shi.

ginin kalma

Wannan aikin yana da dalilai guda biyu. Ma'anar kalmar ita ce:

= IFERROR (darajar; darajar_if_error)

Bayanin jayayya:

1. Ƙimar ita ce dabarar kanta, an bincika don kwari.
2. Ƙimar idan kuskure shine sakamakon da ke bayyana bayan an gano kuskuren.

Idan muka yi magana game da misalai, to wannan dabarar za ta nuna rubutun "Kuskure a lissafin" idan rarraba ba zai yiwu ba.

Formula ta 7: HAGU

Wannan aikin yana ba da damar zaɓar adadin haruffan da ake buƙata daga hagu na kirtani.

Ma'anarsa shine kamar haka:

=LEFT(rubutu, [lambar_chars])

Hujjoji masu yiwuwa:

1. Rubutu - kirtani daga abin da kake son samun takamaiman guntu.
2. Adadin haruffa kai tsaye adadin haruffan da za a ciro.

Don haka, a cikin wannan misalin, zaku iya ganin yadda ake amfani da wannan aikin don ganin yadda taken shafukan yanar gizon za su kasance. Wato, ko igiyar za ta dace da takamaiman adadin haruffa ko a'a.

Formula 8: PSTR

Wannan aikin yana ba da damar samun adadin haruffan da ake buƙata daga rubutun, farawa da wani hali a cikin asusun.

Ma'anarsa shine kamar haka:

= MID (rubutu, matsayi_farawa, adadin_haruffa).

Fadada hujja:

1. Rubutu kirtani ce mai ɗauke da bayanan da ake buƙata.
2. Matsayin farawa shine kai tsaye matsayin wannan hali, wanda ke aiki azaman farkon cire rubutun.
3. Adadin haruffa – adadin haruffan da tsarin ya kamata ya ciro daga rubutun.

A aikace, ana iya amfani da wannan aikin, alal misali, don sauƙaƙe sunayen lakabi ta hanyar cire kalmomin da suke a farkon su.

Formula 9: PROPISN

Wannan aikin yana ƙara girman duk haruffan da ke ƙunshe a cikin wata kirtani ta musamman. Ma'anarsa shine kamar haka:

= ANA BUKATA (rubutu)

Akwai hujja ɗaya kawai - rubutun da kansa, wanda za a sarrafa shi. Kuna iya amfani da bayanin tantanin halitta.

Formula 10: LOWER

Ainihin aikin juzu'i wanda ke sassauta kowane harafi na rubutu ko tantanin halitta.

Tsarinsa yana kama da haka, akwai gardama ɗaya kaɗai mai ɗauke da rubutu ko adireshin salula.

Formula 11: NEMAN

Wannan aikin yana ba da damar samun abin da ake buƙata a tsakanin kewayon sel kuma ya ba da matsayinsa.

Samfurin wannan tsari shine:

= MATCH (darajar dubawa, duba_array, nau'in wasa)

Ana buƙatar muhawara biyu na farko, na ƙarshe na zaɓi ne.

Akwai hanyoyi guda uku don daidaitawa:

1. Kasa da ko daidai da 1.
2. Daidai - 0.
3. Ƙimar mafi ƙanƙanta, daidai da ko mafi girma fiye da -1.

A cikin wannan misalin, muna ƙoƙarin tantance wanne daga cikin kalmomin da aka bi har zuwa dannawa 900, gami da.

Formula 12: DLSTR

Wannan aikin yana ba da damar tantance tsawon kirtani da aka bayar.

Ma'anarsa yayi kama da na baya:

=DLSTR(rubutu)

Don haka, ana iya amfani da shi don ƙayyade tsawon bayanin bayanin lokacin SEO-ci gaba na shafin.

Hakanan yana da kyau a haɗa shi tare da aikin IF.

Formula 13: HADA

Wannan aikin yana ba da damar yin layi da yawa daga ɗaya. Bugu da ƙari, yana halatta a ƙayyade a cikin mahawara duka adiresoshin tantanin halitta da ƙimar kanta. Tsarin yana ba da damar rubuta har zuwa abubuwa 255 tare da jimlar tsayin da bai wuce haruffa 8192 ba, wanda ya isa yin aiki.

Haɗin aikin shine:

= CONCATENATE (rubutu1, rubutu2, rubutu3);

Formula 14: MAGANA

Wannan aikin yana musanya manyan haruffa da ƙananan haruffa.

Ma'anar kalma mai sauqi ce:

= PROPLAN(rubutu)

Formula 15: BUGA

Wannan dabarar tana ba da damar cire duk haruffa marasa ganuwa (misali, karya layi) daga labarin.

Ma'anarsa shine kamar haka:

=PRINT(rubutu)

A matsayin hujja, zaku iya tantance adireshin tantanin halitta.

karshe

Tabbas, waɗannan ba duk ayyukan da ake amfani da su ba ne a cikin Excel. Mun so mu kawo wasu waɗanda matsakaitan mai amfani da maƙunsar bayanai ba su ji ba ko kuma ba safai suke amfani da su ba. A kididdiga, ayyukan da aka fi amfani da su sune don ƙididdigewa da samun matsakaicin ƙima. Amma Excel ya wuce tsarin maƙunsar rubutu kawai. A ciki, zaku iya sarrafa kowane aiki ta atomatik. 

Ina fatan gaske cewa ya yi aiki, kuma kun koyi abubuwa masu amfani da yawa da kanku.