21 Zothandiza za Excel Kwa Otsatsa Paintaneti

Kutsatsa kwapaintaneti ndi gawo lopindulitsa kwambiri pantchito za anthu, makamaka posachedwapa, pomwe bizinesi iliyonse ikuyenda pa intaneti ngati kuli kotheka. Ndipo ngakhale kuti njira zambiri zamabizinesi zimayendetsedwa ndi mapulogalamu apadera, munthu samakhala ndi bajeti yokwanira kuti azipeza, komanso nthawi yoti azidziwa bwino.

Ndipo yankho la vutoli ndi losavuta kwambiri - Excel yakale yabwino, momwe mungasungire nkhokwe zotsogola, mindandanda yamakalata, kusanthula magwiridwe antchito, kukonza bajeti, kuchita kafukufuku ndikuchita ntchito zina zofunika pantchito yovutayi. Lero tidziwa ntchito za 21 Excel zomwe zingagwirizane ndi wotsatsa aliyense pa intaneti. Tisanayambe, tiyeni timvetsetse mfundo zazikuluzikulu:

1. Syntax. Izi ndizo zigawo za ntchitoyo ndi momwe zimalembedwera komanso momwe zigawozi zimapangidwira. Kawirikawiri, syntax ya ntchito iliyonse imagawidwa m'magawo awiri: dzina lake lokha ndi zotsutsana - zosiyana zomwe ntchitoyi imavomereza kuti ipeze zotsatira kapena kuchitapo kanthu. Musanalembe chilinganizo, muyenera kuyika chizindikiro chofanana, chomwe mu Excel chimatanthawuza mawonekedwe ake.
2. Zotsutsana zimatha kulembedwa m'magulu onse a manambala ndi malemba. Komanso, mutha kugwiritsa ntchito othandizira ena ngati mikangano, yomwe imakulolani kuti mulembe ma aligorivimu athunthu mu Excel. Mtsutso womwe watenga mtengo umatchedwa ntchito parameter. Koma nthawi zambiri mawu awiriwa amagwiritsidwa ntchito ngati mawu ofanana. Koma kwenikweni, pali kusiyana pakati pawo. Mtsutso umayamba ndi bulaketi yotseguka, yolekanitsidwa ndi semicolon, ndipo mkangano umatha ndi bulaketi yotsekedwa.

Chitsanzo chokonzekera ntchito - =KUCHULUKA(A1:A5). Chabwino, tiyambe?

Ntchito ya VLOOKUP

Ndi gawoli, wogwiritsa ntchito amatha kupeza chidziwitso chomwe chikugwirizana ndi mfundo zina ndikuchigwiritsa ntchito mwanjira ina kapena kulemba mu selo lina. VPR ndi chidule chomwe chimayimira "Vertical View". Iyi ndi njira yovuta kwambiri yomwe ili ndi mfundo zinayi:

1. Mtengo wofunidwa. Uwu ndiye mtengo womwe kusaka kwa chidziwitso chomwe tidzafuna kudzachitidwa. Imakhala ngati adilesi ya selo kapena mtengo wake wokha kapena wobwezedwa ndi fomula ina.
2. Table. Izi ndizomwe muyenera kufufuza zambiri. Mtengo wofunikira uyenera kukhala mugawo loyamba la tebulo. Mtengo wobwezera ukhoza kukhala mu selo iliyonse yomwe ili mumtundu uwu.
3. Nambala yazanja. Iyi ndi nambala ya ordinal (chidziwitso - osati adilesi, koma nambala ya ordinal) ya gawo lomwe lili ndi mtengo.
4. Kuwonera kwakanthawi. Uwu ndi mtengo wa boolean (ndiko kuti, apa muyenera kuyika fomula kapena mtengo womwe umatulutsa WOONA or KUnama), zomwe zimasonyeza momwe chidziwitsocho chiyenera kukhalira. Ngati mupambana mkangano uwu mtengo WOONA, ndiye kuti zomwe zili m’maselo ziyenera kulembedwa m’njira imodzi mwa njira ziŵiri: motsatira zilembo kapena kukwera. Pankhaniyi, fomulayo ipeza mtengo womwe uli wofanana kwambiri ndi womwe ukufufuzidwa. Ngati mufotokoza ngati mkangano KUnama, ndiye kuti mtengo weniweniwo ndi womwe udzafufuzidwe. Munthawi imeneyi, kusanja kwazomwe zili mgululi sikofunikira kwambiri.

Mtsutso womaliza siwofunika kwambiri kugwiritsa ntchito. Tiyeni tipereke zitsanzo za momwe ntchitoyi ingagwiritsire ntchito. Tiyerekeze kuti tili ndi tebulo lomwe limafotokoza kuchuluka kwa kudina kwamafunso osiyanasiyana. Tiyenera kudziwa kuti ndi angati omwe adachitidwa pa pempho la "kugula piritsi".

Mu ndondomeko yathu, tinali kuyang'ana mawu oti "tablet", omwe timayika ngati mtengo womwe tikufuna. Mkangano wa "tebulo" apa ndi gulu la maselo omwe amayamba ndi selo A1 ndipo amatha ndi selo B6. Nambala yazambiri mwathu ndi 2. Titalowa magawo onse ofunikira mu fomula, tili ndi mzere wotsatirawu: =VLOOKUP(C3;A1:B6;2).

Titazilemba m'chipindamo, tidapeza zotsatira zofananira ndi kuchuluka kwa zopempha zogula piritsi. Mutha kuziwona mu chithunzi pamwambapa. M'malo athu, tidagwiritsa ntchito VPR ndi zizindikiro zosiyana za mkangano wachinayi.

Apa tinalowa nambala 900000, ndipo chilinganizo basi anapeza mtengo wapafupi kwambiri ndipo anapereka funso "kugula galimoto". Monga tikuonera, mkangano wa "interval lookup" uli ndi phindu WOONA. Ngati tifufuza ndi mfundo yomweyi yomwe ili FALSE, ndiye kuti tikuyenera kulemba nambala yeniyeni monga mtengo wakusaka, monga momwe zilili pachithunzichi.

Monga tikuonera, ntchito VPR ali ndi kuthekera kwakukulu, koma, ndithudi, n'zovuta kumvetsa. Koma milunguyo sinatenthe miphika.

Ngati ntchito

Ntchitoyi ndiyofunika kuti muwonjezere zinthu zina zamapulogalamu pa spreadsheet. Imalola wogwiritsa ntchito kuwona ngati kusintha kumakwaniritsa zofunikira zina. Ngati inde, ndiye kuti ntchitoyi imagwira ntchito imodzi, ngati sichoncho, ina. Chiganizo cha ntchitoyi chili ndi mfundo zotsatirazi:

1. Direct boolean expression. Ichi ndiye muyezo womwe uyenera kuyang'aniridwa. Mwachitsanzo, kaya kunja kunja kuli pansi pa ziro kapena ayi.
2. Deta yoti ichitike ngati muyeso uli wowona. Maonekedwewo sangakhale manambala okha. Mukhozanso kulemba chingwe chomwe chidzabwezeredwa ku fomula ina kapena kulembedwa ku selo. Komanso, ngati mtengowo ndi wowona, mutha kugwiritsa ntchito fomula yomwe ingawerengerenso. Mukhozanso kugwiritsa ntchito zipangizo NGATI, zomwe zimalembedwa ngati mfundo za ntchito ina IF. Pankhaniyi, tikhoza kukhazikitsa ndondomeko yokhazikika: ngati chiwerengerocho chikugwirizana ndi chikhalidwecho, ndiye kuti timachitapo kanthu 1, ngati sichoncho, ndiye kuti timayang'ana kutsata ndondomeko ya 2. Komanso, palinso nthambi. Ngati pali maunyolo ambiri otere, ndiye kuti wogwiritsa ntchito akhoza kusokonezeka. Chifukwa chake, tikulimbikitsidwabe kugwiritsa ntchito ma macros kuti mulembe ma aligorivimu ovuta.
3. Mtengo ngati wabodza. Izi ndi zofanana pokhapokha ngati mawuwo sakufanana ndi zomwe zaperekedwa mkangano woyamba. Pankhaniyi, mutha kugwiritsa ntchito mfundo zofanana ndendende ndi zomwe zidachitika m'mbuyomu.

Kuti timvetse bwino, tiyeni titenge chitsanzo chaching’ono.

Njira yomwe yasonyezedwa pachithunzichi imayang'ana ngati ndalama zatsiku ndi tsiku ndizoposa 30000. Ngati inde, ndiye kuti selo likuwonetsa zambiri zomwe dongosololo linamalizidwa. Ngati mtengo uwu ndi wocheperapo kapena wofanana, ndiye kuti chidziwitso chikuwonetsedwa kuti dongosololi silinamalizidwe. Dziwani kuti nthawi zonse timayika zingwe zamawu m'mawu. Lamulo lomweli limagwiranso ntchito pamitundu ina yonse. Tsopano tiyeni tipereke chitsanzo chosonyeza momwe tingagwiritsire ntchito zisa zambiri IF.

Tikuwona kuti pali zotsatira zitatu zomwe zingatheke pogwiritsa ntchito ndondomekoyi. Chiwerengero chochulukira cha zotsatira zomwe fomula yokhala ndi ma nested function ili ndi malire NGATI - 64. Mukhozanso kufufuza ngati selo ilibe kanthu. Kuchita mtundu uwu wa cheke, pali chilinganizo chapadera chotchedwa EPUSTO. Zimakuthandizani kuti musinthe ntchito yayitali IF, yomwe imayang'ana ngati selo ilibe kanthu, ndi njira imodzi yosavuta. Pankhaniyi, formula idzakhala:

ntchito ISBLANK kubwerera kumatenga selo ngati mkangano, ndipo nthawi zonse kumabweretsa mtengo wa boolean. Ntchito IF ili pachimake pa zinthu zina zambiri zomwe tiwonanso, chifukwa zimagwira ntchito yayikulu pakutsatsa. M'malo mwake, alipo ambiri, koma tiwona atatu lero: SUMMESLI, COUNTIF, IFERROR.

SUMIF ndi SUMIFS ntchito

ntchito SUMMESLI zimapangitsa kuti zitheke kuwerengetsa deta yokhayo yomwe imakwaniritsa mulingo winawake ndipo ili pamndandanda. Ntchitoyi ili ndi zifukwa zitatu:

1. Mtundu. Awa ndi gulu la ma cell omwe amayenera kufufuzidwa kuti awone ngati pali ma cell pakati pake omwe akufanana ndi zomwe zatchulidwa.
2. Criterion. Uwu ndi mkangano womwe umatchula magawo enieni omwe ma cell adzafotokozedwe mwachidule. Mtundu uliwonse wa data ukhoza kukhala ngati muyeso: selo, zolemba, nambala, ngakhale ntchito (mwachitsanzo, yomveka). Ndikofunika kulingalira kuti njira zomwe zili ndi malemba ndi masamu ziyenera kulembedwa m'ma quotation marks.
3. Chidule cha mitundu. Mtsutsowu siwofunika kutchulidwa ngati kuchuluka kwake kuli kofanana ndi kuchuluka kwake kuti tiyese muyeso.

Tiyeni titenge chitsanzo chaching'ono kuti tifotokoze. Apa, pogwiritsa ntchito ntchitoyi, taphatikiza zopempha zonse zomwe zili ndi kusintha kopitilira XNUMX.

Palinso mtundu wachiwiri wa ntchitoyi, yomwe imalembedwa ngati SUMMESLIMN. Ndi chithandizo chake, njira zingapo zitha kuganiziridwa nthawi imodzi. Mawu ake amasinthasintha ndipo amatengera kuchuluka kwa mfundo zomwe zikuyenera kugwiritsidwa ntchito. Fomula yofananira ikuwoneka motere: =SUMIFS(chidule_range, condition_range1, condition1, [condition_range2, condition2], …). Mfundo zitatu zoyambirira ziyenera kufotokozedwa, ndiyeno zonse zimadalira momwe munthu akufuna kukhazikitsa.

COUNTIF ndi COUNTIFS ntchito

Izi zimatsimikizira kuchuluka kwa ma cell omwe amafanana ndi chikhalidwe china. Ma syntax a ntchito ali ndi mfundo zotsatirazi:

1. Mtundu. Izi ndizomwe zidzatsimikiziridwe ndikuwerengedwa.
2. Criterion. Izi ndi zomwe deta iyenera kukwaniritsa.

Muchitsanzo chomwe tikupereka tsopano, ntchitoyi idatsimikiza makiyi angati okhala ndi masinthidwe opitilira XNUMX. Zinapezeka kuti panali makiyi atatu okha.

Kuchuluka kotheka kwa zofunikira mu ntchitoyi ndi chikhalidwe chimodzi. Koma mofanana ndi njira yapitayi, mungagwiritse ntchito ntchitoyi COUNTIFSkukhazikitsa mfundo zambiri. Syntax ya ntchitoyi ndi: COUNTIFS(condition_range1, condition1, [condition_range2, condition2], …).

Chiwerengero chachikulu cha mikhalidwe ndi magawo oti awonedwe ndikuwerengedwa ndi 127.

ERROR ntchito

Ndi ntchitoyi, selo lidzabwezera mtengo wotchulidwa ndi wogwiritsa ntchito ngati cholakwika chichitika chifukwa cha kuwerengera kwa ntchito inayake. Syntax ya ntchitoyi ili motere: =IFERROR(mtengo;mtengo_ngati_zolakwika). Monga mukuonera, ntchitoyi imafuna zifukwa ziwiri:

1. Tanthauzo. Apa muyenera kulemba chilinganizo, malinga ndi zomwe zolakwika zidzakonzedwa, ngati zilipo.
2. Mtengo ngati cholakwika. Uwu ndiye mtengo womwe udzawonetsedwe muselo ngati fomula ikulephera.

Ndipo chitsanzo kufotokoza. Tiyerekeze kuti tili ndi tebulo loterolo.

Tikuwona kuti kauntala sikugwira ntchito pano, kotero kulibe alendo, ndipo kugula 32 kudapangidwa. Mwachibadwa, zinthu zoterezi sizingachitike m’moyo weniweni, choncho tiyenera kukonza cholakwikacho. Tinachitadi zimenezo. Tinagoletsa muntchito IFERROR mkangano mu mawonekedwe a chilinganizo chogawa chiwerengero cha zogula ndi chiwerengero cha alendo. Ndipo ngati cholakwika chikachitika (ndipo apa ndikugawikana ndi ziro), chilinganizocho chimalemba "kuyambiranso". Ntchitoyi ikudziwa kuti kugawanika ndi ziro sikungatheke, kotero kumabweretsa mtengo woyenera.

LEFT ntchito

Ndi ntchitoyi, wogwiritsa ntchito amatha kupeza nambala yofunikira ya zilembo zachingwe, zomwe zili kumanzere. Ntchitoyi ili ndi mfundo ziwiri. Nthawi zambiri, formula ndi motere: =KULEFT(mawu,[nambala_ya_zilembo]).

Zotsutsana za ntchitoyi zikuphatikizapo chingwe cha malemba kapena selo yomwe ili ndi zilembo zomwe ziyenera kubwezedwa, komanso chiwerengero cha zilembo zomwe ziyenera kuwerengedwa kuchokera kumanzere. Potsatsa, izi zikuthandizani kuti mumvetsetse momwe mitu yamawebusayiti imawonekera.

Pamenepa, tasankha zilembo 60 kuchokera kumanzere kwa chingwe chomwe chili mu selo A5. Tinkafuna kuyesa momwe mutu wachidule ungawonekere.

PTR ntchito

Ntchitoyi ndi yofanana ndi yapitayi, imangokulolani kusankha poyambira pomwe mungayambe kuwerengera zilembo. Syntax yake ili ndi mfundo zitatu:

1. Chingwe cholemba. Mwamwayi, mutha kulemba mzere apa mwachindunji, koma ndikothandiza kwambiri kupereka maulalo kumaselo.
2. Poyambira. Uwu ndiye khalidwe lomwe kuwerengera kwa chiwerengero cha zilembo zomwe zafotokozedwa mkangano wachitatu zimayambira.
3. Chiwerengero cha zilembo. Mtsutso wofanana ndi womwe wagwiritsidwa ntchito m'mbuyomu.

Ndi ntchitoyi, mwachitsanzo, mukhoza kuchotsa chiwerengero cha zilembo kumayambiriro ndi kumapeto kwa chingwe cholembera.

Kwa ife, tinawachotsa kuyambira pachiyambi.

UPPER ntchito

Ngati mukuyenera kuwonetsetsa kuti mawu onse mu chingwe chomwe chili mu cell inayake alembedwa ndi zilembo zazikulu, ndiye kuti mutha kugwiritsa ntchito ntchitoyi. WOPEREKA. Zimatengera mkangano umodzi wokha, chingwe cholemba kuti chikhale chachikulu. Itha kugundidwa mwachindunji mu bulaketi, kapena mu cell. Pomaliza, muyenera kupereka ulalo kwa izo.

Ntchito ya LOWER

Ntchitoyi ndi yosiyana ndendende ndi yapitayi. Ndi chithandizo chake, mukhoza kupanga zilembo zonse mu chingwe kukhala zazing'ono. Zimatengeranso mkangano umodzi wokha ngati chingwe cholemba, chomwe chimafotokozedwa mwachindunji ngati mawu kapena kusungidwa mu cell inayake. Nachi chitsanzo cha momwe tinagwiritsira ntchito ntchitoyi kusintha dzina la "Date of Birth" kukhala pomwe zilembo zonse ndi zazing'ono.

SEARCH ntchito

Ndi ntchitoyi, wogwiritsa ntchito amatha kudziwa kupezeka kwa chinthu china mu mtengo wamtengo wapatali ndikumvetsetsa komwe kuli. Lili ndi mfundo zingapo:

1. Mtengo wofunidwa. Uwu ndiye mndandanda wamawu, nambala, yomwe iyenera kufufuzidwa mumtundu wa data.
2. Mndandanda womwe umawonedwa. Seti ya data yomwe imafufuzidwa kuti ipeze mtengo womwe uli mumtsutso wam'mbuyo.
3. Mtundu wamapu. Mtsutsano uwu ndi wosankha. Ndi izo, mungapeze deta molondola. Pali mitundu itatu yofananira: 1 - mtengo wocheperako kapena wofanana (tikunena za manambala, ndipo mndandanda womwewo uyenera kusanjidwa mwadongosolo lokwera), 2 - machesi enieni, -1 - mtengo wokulirapo kapena wofanana.

Kuti zimveke, chitsanzo chaching'ono. Apa tidayesa kumvetsetsa kuti ndi ndani mwa zopempha zomwe zili ndi masinthidwe angapo ochepera kapena ofanana ndi 900.

Fomula idabweza mtengo wa 3, womwe si nambala yeniyeni ya mzere, koma wachibale. Izi ndizo, osati ndi adilesi, koma ndi nambala yokhudzana ndi chiyambi cha deta yosankhidwa, yomwe ingayambe kulikonse.

DLSTR ntchito

Ntchitoyi imapangitsa kuti zitheke kuwerengera kutalika kwa chingwe cholemba. Zimatengera mkangano umodzi - adilesi ya selo kapena chingwe cholembera. Mwachitsanzo, pakutsatsa, ndi bwino kugwiritsa ntchito kuti muwone kuchuluka kwa zilembo zomwe zafotokozedwa.

CONCATENATE ntchito

Ndi wogwiritsa ntchito uyu, mutha kuphatikizira zolemba zingapo mu chingwe chimodzi chachikulu. Zotsutsazo ndi maselo kapena zingwe zolunjika m'mawu olekanitsidwa ndi koma. Ndipo apa pali chitsanzo chaching'ono chogwiritsa ntchito ntchitoyi.

Ntchito PROP

Wogwiritsa ntchitoyu amakupatsani mwayi kuti zilembo zonse zoyamba za mawu ziziyamba ndi zilembo zazikulu. Zimatengera chingwe cholemba kapena ntchito yomwe imabweza imodzi ngati mkangano wake wokha. Ntchitoyi ndiyoyenera kulemba mindandanda yomwe ili ndi mayina ambiri kapena zochitika zina zomwe zingakhale zothandiza.

NTCHITO NTCHITO

Wogwiritsa ntchitoyu amapangitsa kuti zitheke kuchotsa zilembo zonse zosawoneka pamndandanda wamawu. Zimatengera mkangano umodzi wokha. Mu chitsanzo ichi, malembawo ali ndi zilembo zosasindikizidwa zomwe zinachotsedwa ndi ntchitoyi.

Izi ziyenera kugwiritsidwa ntchito ngati wogwiritsa ntchito adakopera mawu kuchokera ku pulogalamu ina ndipo zilembo zosasindikiza zasamutsidwa ku Excel spreadsheet.

TRIM ntchito

Ndi wogwiritsa ntchito uyu, wogwiritsa ntchito amatha kuchotsa mipata yonse yosafunikira pakati pa mawu. Zimaphatikizapo adilesi ya selo, yomwe ndi mkangano wokhawo. Pano pali chitsanzo chogwiritsa ntchito ntchitoyi kusiya malo amodzi okha pakati pa mawu.

FIND ntchito

Ndi ntchitoyi, wogwiritsa ntchito amatha kupeza zolemba mkati mwazolemba zina. Izi ndizovuta kwambiri. Choncho, zilembo zazikulu ndi zazing'ono ziyenera kulemekezedwa. Ntchitoyi imatenga mfundo zitatu:

1. Mawu ofunidwa. Ichi ndi chingwe chomwe chikufufuzidwa.
2. Mawu omwe akufufuzidwa ndi momwe kufufuza kumachitikira.
3. Poyambira ndi mkangano wosankha womwe umatchula zilembo zoyambirira zomwe mungasaka.

INDEX ntchito

Ndi ntchitoyi, wogwiritsa ntchito amatha kupeza mtengo womwe akufuna. Lili ndi mfundo zitatu zofunika:

1. Gulu. Mitundu ya data yomwe ikuwunikidwa.
2. Nambala ya mzere. Nambala ya ordinal ya mizere iyi. Chenjerani! Osati adilesi, koma nambala ya mzere.
3. Nambala yazanja. Zofanana ndi mkangano wam'mbuyomu, pagawo lokha. Mtsutsowu ukhoza kusiyidwa wopanda kanthu.

EXACT ntchito

Wogwiritsa ntchitoyu atha kugwiritsidwa ntchito kudziwa ngati zingwe ziwiri ndi zofanana. Ngati ali ofanana, amabwezera mtengo WOONA. Ngati ali osiyana - KUnama.

OR ntchito

Ntchitoyi imakulolani kuti muyike kusankha kwa chikhalidwe 1 kapena chikhalidwe 2. Ngati chimodzi mwa izo chiri chowona, ndiye kuti mtengo wobwezera ndi - WOONA. Mutha kufotokozera mpaka 255 zaboolean.

Ntchito ndi

Ntchitoyi imabweza mtengo WOONAngati mikangano yake yonse imabweza mtengo womwewo.

Uwu ndiye mkangano wofunikira kwambiri womwe umakupatsani mwayi wokhazikitsa zinthu zingapo nthawi imodzi, zomwe ziyenera kuwonedwa nthawi imodzi.

OFFSET ntchito

Ntchitoyi imakupatsani mwayi woti mupeze zolozera kumitundu yomwe imasinthidwa ndi mizere ina ndi mizere kuchokera pazogwirizanitsa zoyambirira. Kukangana: kutchula selo loyamba la mulingo, mizere ingati yoti musunthe, ndi mizere ingati yoti musunthe, kutalika kwake ndi kotani komanso m'lifupi mwake.

Kutsiliza

Mothandizidwa ndi ntchito za Excel, wotsatsa amatha kusanthula momwe tsamba likuyendera, kutembenuka, ndi zizindikiro zina. Monga mukuwonera, palibe mapulogalamu apadera omwe amafunikira, ofesi yabwino yakale ndi yokwanira kukhazikitsa lingaliro lililonse.