Ma formula 15 apamwamba mu Excel

Excel ndi imodzi mwamapulogalamu ofunikira kwambiri. Zapangitsa moyo wa ogwiritsa ntchito ambiri kukhala wosavuta. Excel imakupatsani mwayi wowerengera ngakhale zovuta kwambiri, ndipo uwu ndiye mwayi waukulu wa pulogalamuyi.

Monga lamulo, wogwiritsa ntchito wamba amagwiritsa ntchito magawo ochepa okha, pomwe pali njira zambiri zomwe zimakulolani kuti mugwiritse ntchito zomwezo, koma mwachangu.

Izi zitha kukhala zothandiza ngati nthawi zonse mumayenera kuchita zinthu zambiri zamtundu womwewo zomwe zimafuna ntchito zambiri.

Zakhala zosangalatsa? Kenako landirani kuwunikanso kwamitundu yothandiza kwambiri ya 15 Excel.

Ma terminology ena

Musanayambe kuwunika ntchito mwachindunji, muyenera kumvetsetsa chomwe chiri. Lingaliro ili limatanthauza chilinganizo chokhazikitsidwa ndi omanga, malinga ndi zomwe mawerengedwe amachitidwa ndipo zotsatira zina zimapezedwa pazotulutsa. 

Ntchito iliyonse ili ndi zigawo ziwiri zazikulu: dzina ndi mkangano. Fomula imatha kukhala ndi ntchito imodzi kapena zingapo. Kuti muyambe kulemba, muyenera kudina kawiri pa selo lofunika ndikulemba chizindikiro chofanana.

Gawo lotsatira la ntchitoyi ndi dzina. Kwenikweni, ndi dzina la fomula, lomwe lingathandize Excel kumvetsetsa zomwe wosuta akufuna. Amatsatiridwa ndi mikangano m’makolo. Izi ndi magawo a ntchito omwe amaganiziridwa kuti agwire ntchito zina. Pali mitundu ingapo ya mfundo: manambala, malemba, zomveka. Komanso, m'malo mwa iwo, nthawi zambiri amagwiritsidwa ntchito pofotokoza za maselo kapena gulu linalake. Mtsutso uliwonse umasiyanitsidwa ndi wina ndi semicolon.

Syntax ndi imodzi mwamaganizidwe akuluakulu omwe amawonetsa ntchito. Mawuwa amatanthauza template yoyika zinthu zina kuti ntchitoyo igwire ntchito.

Ndipo tsopano tiyeni tifufuze zonsezi muzochita.

Fomula 1: VLOOKUP

Ntchitoyi imapangitsa kuti zitheke kupeza zofunikira patebulo, ndikuwonetsa zotsatira zomwe zabwezedwa mu selo linalake. Chidule cha dzina la ntchitoyi ndikuyimira "Vertical View".

Syntax

Iyi ndi njira yovuta kwambiri yomwe ili ndi mfundo 4, ndipo kugwiritsa ntchito kwake kumakhala ndi zambiri.

Chidule chake ndi:

=VLOOKUP(kuyang'ana_mtengo, tebulo, nambala_nambala, [kufufuza_kufufuza])

Tiyeni tione mwatsatanetsatane mikangano yonse:

1. Phindu loyang'ana mmwamba.
2. Table. Ndikofunikira kuti pagawo loyamba pakhale mtengo wowerengera, komanso mtengo womwe wabwezedwa. Yotsirizirayi ili paliponse. Wogwiritsa akhoza kusankha yekha komwe angaike zotsatira za fomula. 
3. Nambala yazanja.
4. Kuwonera kwakanthawi. Ngati izi siziri zofunikira, ndiye kuti mutha kusiya kufunika kwa mkanganowu. Ndi mawu a boolean omwe akuwonetsa kuchuluka kwa machesi omwe ntchitoyo iyenera kupeza. Ngati chizindikiro "Zowona" chatchulidwa, ndiye kuti Excel idzayang'ana mtengo wapafupi kwambiri ndi womwe umatchulidwa ngati mtengo wofufuzira. Ngati parameter ya "Zabodza" yatchulidwa, ndiye kuti ntchitoyi idzafufuza zokhazokha zomwe zili mugawo loyamba.

Mu chithunzithunzi ichi, tikuyesera kuti tiwone kuchuluka kwa malingaliro omwe adapangidwa pafunso la "kugula piritsi" pogwiritsa ntchito fomula.

Fomula 2: Ngati

Ntchitoyi ndiyofunikira ngati wogwiritsa ntchito akufuna kukhazikitsa chikhalidwe chomwe mtengo wake uyenera kuwerengedwa kapena kutulutsa. Zitha kutenga njira ziwiri: zoona ndi zabodza.

Syntax

Ndondomeko ya ntchitoyi ili ndi mfundo zazikulu zitatu, ndipo zikuwoneka motere:

=IF(mawu_womveka, “mtengo_ngati_zoona”, “mtengo_ngati_zabodza”).

Apa, mawu omveka amatanthauza chilinganizo chomwe chimafotokoza mwachindunji muyeso. Ndi chithandizo chake, deta idzafufuzidwa kuti igwirizane ndi chikhalidwe china. Chifukwa chake, mtsutso wa "mtengo ngati wabodza" umapangidwira ntchito yomweyo, kusiyana kokhako ndikuti ndi galasi losiyana ndi tanthauzo. M'mawu osavuta, ngati chikhalidwecho sichinatsimikizidwe, ndiye kuti pulogalamuyo imachita zinthu zina.

Palinso njira ina yogwiritsira ntchito IF - magwiridwe antchito. Pakhoza kukhala zina zambiri pano, mpaka 64. Chitsanzo cha kulingalira kofanana ndi ndondomeko yomwe yaperekedwa mu chithunzithunzi ndi motere. Ngati selo A2 ndi yofanana ndi awiri, muyenera kusonyeza mtengo "Inde". Ngati ili ndi mtengo wosiyana, muyenera kuyang'ana ngati selo D2 ndi yofanana ndi ziwiri. Ngati inde, ndiye kuti muyenera kubwezera mtengo "ayi", ngati apa mkhalidwewo umakhala wabodza, ndiye kuti ndondomekoyi iyenera kubwezera mtengo "mwina".

Sitikulimbikitsidwa kugwiritsa ntchito zisa nthawi zambiri, chifukwa ndizovuta kugwiritsa ntchito, zolakwika ndizotheka. Ndipo zidzatenga nthawi yaitali kuti akonze. 

ntchito IF angagwiritsidwenso ntchito kudziwa ngati selo linalake lilibe kanthu. Kuti mukwaniritse cholinga ichi, ntchito ina iyenera kugwiritsidwa ntchito - ISBLANK.

Nayi syntax ndi:

=IF(ISBLANK( nambala yafoni),"Chopanda kanthu", "Chopanda kanthu").

Komanso, n'zotheka kugwiritsa ntchito m'malo mwa ntchito ISBLANK gwiritsani ntchito fomula yokhazikika, koma fotokozani kuti poganiza kuti mulibe zofunikira mu cell.

NGATI - iyi ndi imodzi mwazinthu zodziwika bwino zomwe ndizosavuta kugwiritsa ntchito ndipo zimakupatsani mwayi womvetsetsa momwe zikhalidwe zina zimakhalira, kupeza zotsatira zamitundu yosiyanasiyana, ndikuwunikanso ngati selo linalake lilibe kanthu.

Ntchitoyi ndiye maziko amitundu ina. Tsopano tipenda zina mwa izo mwatsatanetsatane.

Fomula 3: SUMIF

ntchito SUMMESLI amakulolani kufotokoza mwachidule deta, malinga ndi kutsata kwawo ndi zina.

Syntax

Ntchitoyi, monga yapitayi, ili ndi mfundo zitatu. Kuti mugwiritse ntchito, muyenera kulemba chilinganizo choterocho, kulowetsa zofunikira m'malo oyenera.

=SUMIF(mtundu, chikhalidwe, [sum_range])

Tiyeni timvetsetse mwatsatanetsatane zomwe mkangano uliwonse uli:

1. Mkhalidwe. Mtsutso uwu umakulolani kuti mudutse ma cell ku ntchitoyo, yomwe imakhudzidwanso ndi kumasulira.
2. Chidule cha mitundu. Mtsutsowu ndiwosasankha ndipo umakupatsani mwayi wofotokozera ma cell kuti muwerenge ngati vutolo ndilabodza.

Chifukwa chake, munthawi iyi, Excel idapereka chidule cha zomwe zafunsidwa pomwe kuchuluka kwa zosintha kumapitilira 100000.

Fomula 4: SUMMESLIMN

Ngati pali zikhalidwe zingapo, ndiye kuti ntchito yofananira imagwiritsidwa ntchito SUMMESLIMN.

Syntax

Fomula ya ntchitoyi ikuwoneka motere:

=SUMIFS(chidule_range, condition_range1, condition1, [condition_range2, condition2], ...)

Mfundo zachiwiri ndi zachitatu ndizofunikira, zomwe ndi "Range of condition 1" ndi "Range of condition 1".

Fomula 5: COUNTIF ndi COUNTIFS

Ntchitoyi imayesa kudziwa kuchuluka kwa maselo opanda kanthu omwe amafanana ndi zomwe zaperekedwa mumtundu womwe wogwiritsa ntchito walowa.

Syntax

Kuti mulowetse ntchitoyi, muyenera kufotokoza zotsatirazi:

= COUNTIF (osiyanasiyana, njira)

Kodi mikangano yoperekedwayo ikutanthauza chiyani?

1. A range ndi gulu la ma cell omwe mawerengedwe akuyenera kuchitidwa.
2. Zofunikira - chikhalidwe chomwe chimaganiziridwa posankha maselo.

Mwachitsanzo, mu chitsanzo ichi, pulogalamuyo inawerengera chiwerengero cha mafunso ofunika, pomwe chiwerengero cha kudina mu injini zosaka chimaposa chikwi zana. Zotsatira zake, fomula idabweza nambala 3, zomwe zikutanthauza kuti pali mawu osakira atatu.

Kulankhula za ntchito zogwirizana COUNTIFS, ndiye, mofanana ndi chitsanzo chapitachi, imapereka mphamvu yogwiritsira ntchito njira zingapo nthawi imodzi. Fomula yake ndi motere:

=COUNTIFS(condition_range1, condition1, [condition_range2, condition2],…)

Ndipo mofanana ndi nkhani yapitayi, "Condition Range 1" ndi "Condition 1" ndizofunikira zotsutsana, pamene zina zikhoza kuchotsedwa ngati palibe chosowa chotero. Ntchito yayikulu imapereka mwayi wogwiritsa ntchito mpaka ma 127 osiyanasiyana ndi zikhalidwe.

Fomula 6: IFERROR

Ntchitoyi imabweretsanso mtengo womwe watchulidwa ndi wogwiritsa ntchito ngati kukumana ndi vuto pakuwunika fomula. Ngati mtengo wake ndi wolondola, amasiya.

Syntax

Ntchitoyi ili ndi mfundo ziwiri. Syntax ndi iyi:

=IFERROR(mtengo;mtengo_ngati_kulakwitsa)

Kufotokozera za mikangano:

1. Mtengo ndi chilinganizo palokha, kufufuzidwa nsikidzi.
2. Mtengo ngati cholakwika ndi chotsatira chomwe chikuwoneka cholakwikacho chitazindikirika.

Ngati tilankhula za zitsanzo, ndiye kuti chilinganizochi chidzawonetsa mawu akuti "Kulakwitsa powerengera" ngati kugawanika sikungatheke.

Fomula 7: KULEFT

Ntchitoyi imapangitsa kuti zitheke kusankha nambala yofunikira ya zilembo kuchokera kumanzere kwa chingwe.

Syntax yake ndi iyi:

=KULEFT(mawu,[nambala_chars])

Zokangana zotheka:

1. Zolemba - chingwe chomwe mukufuna kupeza chidutswa china.
2. Chiwerengero cha zilembo ndi chiwerengero cha zilembo zomwe ziyenera kuchotsedwa.

Kotero, mu chitsanzo ichi, mukhoza kuona momwe ntchitoyi ikugwiritsidwira ntchito kuti muwone momwe mitu yamasamba idzawoneka. Ndiko kuti, kaya chingwecho chidzakwanira mu chiwerengero cha zilembo kapena ayi.

Fomu 8: PSTR

Ntchitoyi imapangitsa kuti mupeze chiwerengero chofunikira cha zilembo kuchokera palemba, kuyambira ndi khalidwe linalake mu akaunti.

Syntax yake ndi iyi:

=MID(mawu, chiyambi_malo, chiwerengero_cha_zilembo).

Kukulitsa mikangano:

1. Mawu ndi chingwe chomwe chili ndi deta yofunikira.
2. Malo oyambira ali mwachindunji malo a munthu ameneyo, omwe amakhala ngati poyambira potulutsa mawuwo.
3. Chiwerengero cha zilembo - chiwerengero cha zilembo zomwe fomula iyenera kuchotsedwa palemba.

Pochita izi, ntchitoyi ingagwiritsidwe ntchito, mwachitsanzo, kuti muchepetse mayina a maudindo pochotsa mawu omwe ali pachiyambi.

Fomula 9: PROPISN

Izi zimayika zilembo zazikulu zilembo zonse zomwe zili mu chingwe china. Syntax yake ndi iyi:

=KUFUNIKA(mawu)

Pali mkangano umodzi wokha - malembawo, omwe adzakonzedwa. Mutha kugwiritsa ntchito zolembera zama cell.

Fomula 10: Otsika

Kwenikweni ntchito yosinthira yomwe imatsitsa zilembo zamtundu uliwonse kapena selo.

Mawu ake ndi ofanana, pali mkangano umodzi wokha wokhala ndi mawu kapena adilesi yama cell.

Fomula 11: FUFUZANI

Ntchitoyi imapangitsa kuti tipeze chinthu chofunikira pakati pamagulu osiyanasiyana ndikupereka malo ake.

Template ya formula iyi ndi:

=MATCH(kuyang'ana_mtengo, kuyang'ana_kufanana, mtundu_wofanana)

Mikangano iwiri yoyambirira ndiyofunikira, yomaliza ndiyosankha.

Pali njira zitatu zofananira:

1. Zochepera kapena zofanana ndi 1.
2. Zokwanira - 0.
3. Mtengo wocheperako, wofanana kapena wokulirapo kuposa -1.

Muchitsanzo ichi, tikuyesera kudziwa kuti ndi mawu ati omwe amatsatiridwa ndi kudina mpaka 900, kuphatikiza.

Njira 12: DLSTR

Ntchitoyi imapangitsa kuti zitheke kudziwa kutalika kwa chingwe choperekedwa.

Mawu ake ndi ofanana ndi am'mbuyomu:

=DLSTR(zolemba)

Chifukwa chake, chitha kugwiritsidwa ntchito kudziwa kutalika kwa kufotokozera kwa nkhaniyo pamene SEO-kutsatsa tsambalo.

Ndibwinonso kuphatikiza ndi ntchitoyo IF.

Fomula 13: Lumikizanani

Ntchitoyi imapangitsa kupanga mizere ingapo kuchokera kumodzi. Komanso, ndizololedwa kufotokoza m'makambirano onse ma adilesi a cell ndi mtengo womwewo. The chilinganizo zimathandiza kulemba zinthu 255 ndi okwana kutalika zilembo zosaposa 8192, zokwanira kuchita.

Chidule chake ndi:

=CONCATENATE(text1,text2,text3);

Njira 14: PROPNACH

Izi zimasintha zilembo zazikulu ndi zazing'ono.

Syntax ndiyosavuta:

= PROPLAN(mawu)

Fomula 15: PRINT

Fomulayi imapangitsa kuti zitheke kuchotsa zilembo zonse zosawoneka (mwachitsanzo, zoduka mizere) m'nkhaniyi.

Syntax yake ndi iyi:

=PRINT(mawu)

Monga mkangano, mukhoza kufotokoza adiresi ya selo.

Mawuwo

Zachidziwikire, izi sizinthu zonse zomwe zimagwiritsidwa ntchito mu Excel. Tinkafuna kubweretsa zina zomwe munthu aliyense wogwiritsa ntchito spreadsheet sanazimvepo kapena sazigwiritsa ntchito kawirikawiri. Powerengera, ntchito zomwe zimagwiritsidwa ntchito kwambiri ndi kuwerengera ndi kupeza mtengo wapakati. Koma Excel ndi yoposa pulogalamu ya spreadsheet. Mmenemo, mukhoza automate mwamtheradi ntchito iliyonse. 

Ndikukhulupirira kuti zidatheka, ndipo mwaphunzira zinthu zambiri zothandiza kwa inu nokha.