21 endri-javatra Excel mahasoa ho an'ny mpivarotra an-tserasera

Ny varotra amin'ny Internet dia sehatry ny asan'ny olombelona, ​​​​indrindra fa tato ho ato, rehefa misy orinasa mivezivezy amin'ny Internet raha azo atao. Ary na dia eo aza ny zava-misy fa maro ny raharaham-barotra dia tantanan'ny fandaharana manokana, ny olona iray dia tsy manana teti-bola ampy mba hahazoana azy ireo, ary koa ny fotoana mba hifehy azy ireo.

Ary ny vahaolana amin'ity olana ity dia tena tsotra - Excel taloha tsara, izay ahafahanao mitazona angon-drakitra fitarihana, lisitry ny mailaka, manadihady ny fahombiazan'ny varotra, manomana teti-bola, manao fikarohana ary manao asa hafa ilaina amin'ity asa sarotra ity. Androany isika dia hifankazatra amin'ny fiasa Excel 21 izay mifanaraka amin'ny mpivarotra Internet rehetra. Alohan'ny hanombohantsika, andeha hojerentsika ireo hevi-dehibe vitsivitsy:

1. Syntax. Ireo no ampahany mandrafitra ny asa sy ny fomba nanoratana azy ary ny filaharan'ireo singa ireo. Amin'ny ankapobeny, ny syntax amin'ny asa rehetra dia mizara roa: ny anarany sy ny tohan-kevitra - ireo fari-pahalalana izay eken'ny asa mba hahazoana vokatra na hanao hetsika iray. Alohan'ny hanoratanao formula dia mila mametraka marika mitovy ianao, izay manondro ny toetran'ny fampidirana azy ao amin'ny Excel.
2. Ny tohan-kevitra dia azo soratana amin'ny endrika nomerika sy lahatsoratra. Ankoatra izany, azonao atao ny mampiasa mpandraharaha hafa ho tohan-kevitra, izay ahafahanao manoratra algorithm feno amin'ny Excel. Ny tohan-kevitra izay naka sanda iray dia antsoina hoe paramètre fonction. Matetika anefa ireo teny roa ireo no ampiasaina ho mitovy hevitra. Saingy raha ny marina dia misy fahasamihafana eo amin'izy ireo. Ny sakana argument dia manomboka amin'ny bracket misokatra, misaraka amin'ny semicolon ary mifarana amin'ny bracket mihidy ny sakana miady hevitra.

Ohatra asa vonona − =SUM(A1:A5). Hanomboka ve isika?

Fiasa VLOOKUP

Miaraka amin'io endri-javatra io, ny mpampiasa dia afaka mahita fampahalalana mifanaraka amin'ny fepetra sasany ary mampiasa izany amin'ny formula hafa na manoratra izany amin'ny sela hafa. VPR dia fanafohezana ny hoe "Vertical View". Ity dia raikipohy somary sarotra izay misy hevitra efatra:

1. Ny sanda irina. Izany no sandan'ny fanatanterahana ny fikarohana ny vaovao ilaintsika. Izy io dia adiresin'ny sela na sanda na ho azy na averina amin'ny alàlan'ny formula hafa.
2. latabatra. Ity no isan-karazany ilainao hikaroka fampahalalana. Ny sanda ilaina dia tsy maintsy ao amin'ny tsanganana voalohany amin'ny latabatra. Ny sandan'ny fiverenana dia mety ho tanteraka amin'ny sela rehetra izay tafiditra ao anatin'ity sehatra ity.
3. Laharan'ny tsanganana. Ity no laharana ordinal (tafiditra - tsy ny adiresy, fa ny laharana ordinal) amin'ny tsanganana misy ny sandany.
4. Fijerena elanelam-potoana. Ity dia sanda boolean (izany hoe, eto ianao dia mila miditra ny formula na ny sanda mamokatra MARINA or ny lainga), izay manondro ny fomba tokony handrafetana ny vaovao. Raha mandalo ity hevitra ity ianao dia misy sanda MARINA, dia tsy maintsy alahatra amin'ny iray amin'ireo fomba roa ny ao anatin'ny sela: araka ny abidy na miakatra. Amin'ity tranga ity, ny formula dia hahita ny sanda mitovy indrindra amin'ilay tadiavina. Raha tondroinao ho tohan-kevitra ny lainga, dia ny sanda marina ihany no hokarohana. Amin'ity toe-javatra ity dia tsy dia zava-dehibe loatra ny fanasokajiana ny angona tsanganana.

Tsy dia zava-dehibe loatra ny fampiasana ny hevitra farany. Andeha isika hanome ohatra vitsivitsy amin'ny fomba azo ampiasana an'io asa io. Eritrereto hoe manana latabatra isika izay mamaritra ny isan'ny kitika ho an'ny fanontaniana samihafa. Mila fantarina hoe firy no natao ho an'ny fangatahana "mividy takelaka".

Ao amin'ny formulay, dia nitady fotsiny ny teny hoe "tablette" izahay, izay napetrakay ho sanda irina. Ny tohan-kevitra "latabatra" eto dia andiana sela izay manomboka amin'ny sela A1 ary mifarana amin'ny sela B6. Ny laharan'ny tsanganana amin'ny tranga misy antsika dia 2. Rehefa avy nampiditra ny masontsivana ilaina rehetra ao amin'ny formula isika dia nahazo izao andalana manaraka izao: =VLOOKUP(C3;A1:B6;2).

Rehefa avy nanoratra izany tao amin'ny sela izahay, dia nahazo valiny mifanaraka amin'ny isan'ny fangatahana hividy takelaka. Hitanao amin'ny pikantsary etsy ambony. Amin'ny toe-javatra misy anay dia nampiasa ny fiasa izahay VPR miaraka amin'ny famantarana samy hafa momba ny hevitra fahefatra.

Eto izahay dia niditra ny isa 900000, ary ny formula dia nahita ny sanda akaiky indrindra tamin'izany ary namoaka ny fanontaniana hoe "mividy fiara". Araka ny hitantsika, ny tohan-kevitra "interval lookup" dia misy ny sandany MARINA. Raha mitady hevitra mitovy amin'ny FALSE isika, dia mila manoratra ny isa marina amin'ny sandan'ny fikarohana, toy ny amin'ity pikantsary ity.

Araka ny hitantsika, asa iray VPR manana fahafahana midadasika indrindra, saingy mazava ho azy fa sarotra takarina. Tsy nodoran’ireo andriamanitra anefa ny vilany.

IF function

Ilaina ity fiasa ity mba hanampiana singa fandaharana sasany amin'ny takelaka. Mamela ny mpampiasa hanamarina raha mahafeno fepetra sasany ny fari-piainana iray. Raha eny, ny asa dia manao hetsika iray, raha tsy izany, hafa. Ny syntax ho an'ity asa ity dia ahitana ireto hevitra manaraka ireto:

1. Fiteny boolean mivantana. Io no criterion tsy maintsy hamarinina. Ohatra, na ambany aotra na tsia ny andro any ivelany.
2. Ny angon-drakitra hokarakaraina raha marina ny fepetra. Ny endrika dia mety tsy amin'ny isa ihany. Azonao atao koa ny manoratra tady an-tsoratra izay haverina amin'ny formula hafa na soratana amin'ny sela. Ary koa, raha marina ny sanda, azonao atao ny mampiasa formula iray izay hanao kajy fanampiny. Azonao atao koa ny mampiasa functions RAHA, izay voasoratra ho tohan-kevitra amin'ny asa hafa IF. Amin'ity tranga ity, afaka mametraka algorithm feno isika: raha mahafeno ny fepetra ny fepetra, dia manao hetsika 1 isika, raha tsy izany, dia manamarina ny fanarahana ny fepetra 2. Ho setrin'izany, misy sampana ihany koa. Raha be dia be ny rojo toy izany, dia mety ho very hevitra ny mpampiasa. Noho izany, mbola asaina mampiasa macro hanoratana algorithm sarotra.
3. Sanda raha diso. Toy izany ihany koa raha tsy mifanaraka amin'ny fepetra voalaza ao amin'ny tohan-kevitra voalohany ny fomba fiteny. Amin'ity tranga ity, azonao atao ny mampiasa tohan-kevitra mitovy tanteraka amin'ny tranga teo aloha.

Mba hanazavana izany, andeha isika haka ohatra kely.

Ny raikipohy aseho amin'ity pikantsary ity dia manamarina raha mihoatra ny 30000 ny vola miditra isan'andro. Raha eny, dia asehon'ny sela fa vita ny drafitra. Raha latsaka na mitovy io sanda io, dia misy fampahafantarana aseho fa tsy vita ny drafitra. Mariho fa ampidirinay ao anaty quotes foana ny tady lahatsoratra. Toy izany koa no mihatra amin'ny formulas hafa rehetra. Andeha isika hanome ohatra iray mampiseho ny fomba fampiasana fonctions nested maro IF.

Hitantsika fa misy vokatra telo azo atao amin'ny fampiasana ity formula ity. Isan'ny vokatra faran'izay voafetra izay voafetra amin'ny rôlôjia misy fiasa mianaka RAHA- 64. Azonao atao ihany koa ny manamarina raha misy sela iray foana. Mba hanatanterahana io karazana fisavana io dia misy ny formula manokana antsoina hoe EPUSTA. Mamela anao hanolo asa lava IF, izay manamarina raha banga ny sela, miaraka amin'ny formula tsotra iray. Amin'ity tranga ity, ny formula dia:

asa ISBLANK miverina maka sela ho tohan-kevitra, ary mamerina sanda boolean foana. asa IF no fototry ny endri-javatra maro hafa izay hojerentsika manaraka, satria manana anjara toerana lehibe amin'ny varotra izy ireo. Raha ny marina dia maro izy ireo, fa hojerentsika ny telo anio: SUMMESLI, COUNTIF, IFERROR.

SUMIF sy SUMIFS miasa

asa SUMMESLI ireo angon-drakitra mifanaraka amin'ny fepetra sasany ary ao anatin'ny salan'isa ihany no azo atao. Ity asa ity dia manana hevitra telo:

1. Sarona. Ity dia andiana sela mila hamarinina raha toa ka misy sela ao anatin'izany mifanaraka amin'ny fepetra voafaritra.
2. Criterion. Ity dia tohan-kevitra izay mamaritra ny mari-pamantarana marina izay hamintinana ireo sela. Ny karazana angon-drakitra rehetra dia azo ampiasaina ho fepetra: sela, lahatsoratra, isa, ary na dia asa (ohatra, lojika). Zava-dehibe ny mandinika fa ny fepetra misy soratra sy marika matematika dia tsy maintsy soratana amin'ny marika teny.
3. Saran'ny famintinana. Tsy mila faritana io tohan-kevitra io raha toa ka mitovy amin'ny salan'isa hitsapana ny mari-pahaizana ny salan'isa famintinana.

Andeha isika haka ohatra kely hanazavana izany. Eto, amin'ny fampiasana ny fiasa, dia nampidirinay ny fangatahana rehetra izay manana fifindrana mihoatra ny zato.

Misy ihany koa ny dikan-teny faharoa amin'ity fiasa ity, izay nosoratana ho SUMMESLIMN. Miaraka amin'ny fanampiany, ny fepetra maromaro dia azo raisina indray mandeha. Ny syntax dia miovaova ary miankina amin'ny isan'ny hevitra hampiasaina. Ny formula generic dia toy izao: =SUMIFS(famintinana_faritra, fepetra_faritra1, fepetra1, [fepetra_faritra2, fepetra2], …). Ny hevitra telo voalohany dia tsy maintsy voafaritra, ary avy eo ny zava-drehetra dia miankina amin'ny habetsaky ny fepetra tian'ilay olona apetraka.

COUNTIF sy COUNTIFS miasa

Io asa io no mamaritra ny isan'ny sela ao anatin'ny elanelana mifanaraka amin'ny fepetra iray. Ny syntax function dia ahitana ireto hevitra manaraka ireto:

1. Sarona. Ity ny angon-drakitra izay hohamarinina sy isaina.
2. Criterion. Izany no fepetra tsy maintsy fenoin'ny angona.

Ao amin'ny ohatra omenay izao, io fiasa io dia mamaritra ny isan'ny fanalahidy misy fifindrana mihoatra ny iray hetsy. Hita fa telo ihany no lakile toy izany.

Fepetra iray ny isa ambony indrindra azo atao amin'ity asa ity. Fa toy ny teo aloha safidy, dia afaka mampiasa ny asa COUNTIFSmba hametraka fepetra bebe kokoa. Ny syntax ho an'ity asa ity dia: COUNTIFS(condition_range1, condition1, [condition_range2, condition2], …).

127 ny isan'ny fepetra sy elanelana azo jerena sy kajy.

ERROR asa

Miaraka amin'io fiasa io, ny sela dia hamerina ny sanda voatondron'ny mpampiasa raha misy hadisoana mitranga vokatry ny kajy ho an'ny asa iray. Ny syntax amin'ity asa ity dia toy izao manaraka izao: =IFERROR(value;value_if_error). Araka ny hitanao, ity asa ity dia mitaky hevitra roa:

1. Midika. Eto ianao dia mila manoratra ny formulaire, araka ny tokony ho izy ny fahadisoana, raha misy.
2. Ny sanda raha misy hadisoana. Ity no sanda izay haseho ao amin'ny sela raha tsy mahomby ny fomba fiasa.

Ary ohatra iray haseho. Aoka hatao hoe manana latabatra toy izany isika.

Hitanay fa tsy mandeha ny kaontera eto, ka tsy misy mpitsidika, ary 32 ny fividianana. Mazava ho azy, ny toe-javatra toy izany dia tsy mety hitranga amin'ny tena fiainana, noho izany dia mila mandinika izany fahadisoana izany. Izany ihany no nataonay. Nahazo baolina izahay tamina hetsika iray IFERROR hevitra amin'ny endrika raikipohy mizara ny isan'ny fividianana amin'ny isan'ny mpitsidika. Ary raha misy hadisoana (ary amin'ity tranga ity dia mizara amin'ny aotra), ny formula dia manoratra "recheck". Ity asa ity dia mahafantatra fa ny fizarana amin'ny zero dia tsy azo atao, noho izany dia mamerina ny sanda mety.

asa ankavia

Miaraka amin'io fiasa io, ny mpampiasa dia afaka mahazo ny isan'ny tarehin-tsoratra irina amin'ny tady lahatsoratra, izay hita eo amin'ny ilany havia. Ny fiasa dia misy hevitra roa. Amin'ny ankapobeny, ny formula dia toy izao manaraka izao: =LEFT(soratra, [isa_ny_toetra]).

Ny tohan-kevitra amin'ity asa ity dia misy tady na sela misy ny tarehintsoratra ho alaina, ary koa ny isan'ny tarehintsoratra hoisaina avy amin'ny ilany havia. Amin'ny marketing, ity endri-javatra ity dia ahafahanao mahafantatra ny endriky ny lohatenin'ny pejy web.

Amin'ity tranga ity, dia nisafidy tarehintsoratra 60 avy amin'ny ankavian'ny tady voarakitra ao amin'ny sela A5 izahay. Te-hitsapa ny mety ho endriky ny lohateny fohy izahay.

Vidin'ny PTR

Ity fiasa ity dia mitovy amin'ny teo aloha, mamela anao hisafidy toerana hiaingana hanombohana manisa tarehintsoratra. Ny syntax dia misy hevitra telo:

1. Tady lahatsoratra. Ara-teorika tsotra izao, azonao atao ny manoratra tsipika mivantana eto, fa mahomby kokoa ny manome rohy mankany amin'ny sela.
2. Toerana fanombohana. Io no toetra iatombohan'ny fanisana ny isan'ny tarehintsoratra voalaza ao amin'ny tohankevitra fahatelo.
3. Isan'ny tarehintsoratra. Tohan-kevitra mitovy amin'ilay tamin'ny asa teo aloha.

Amin'ity fiasa ity, ohatra, dia azonao atao ny manala tarehintsoratra maromaro eo am-piandohana sy fiafaran'ny tady lahatsoratra.

Raha ny anay dia efa hatrany am-boalohany ihany no nesorinay.

asa UPPER

Raha mila maka antoka ianao fa voasoratra amin'ny litera lehibe ny teny rehetra ao anaty tady lahatsoratra iray ao anaty sela manokana, dia azonao atao ny mampiasa ilay asa. FAMPIANARANA. Tohan-kevitra iray ihany no ilaina, tady lahatsoratra iray mba ho lehibe. Azo fehezina mivantana amin'ny bracket izy io, na ao anaty sela. Amin'ity tranga farany ity dia tsy maintsy manome rohy mankany aminy ianao.

asa ambany

Ity fiasa ity dia mifanohitra tanteraka amin'ny teo aloha. Miaraka amin'ny fanampiany dia azonao atao ny manao ny litera rehetra ao anaty tady ho kely. Mila hevitra iray ihany koa izy io ho tady lahatsoratra, na aseho mivantana ho lahatsoratra na voatahiry ao anaty sela manokana. Ity misy ohatra iray amin'ny nampiasanay ity fiasa ity hanovana ny anaran'ny tsanganana “Daty nahaterahana” ho lasa iray izay kely ny litera rehetra.

FIKARIHANA

Miaraka amin'io fiasa io, ny mpampiasa dia afaka mamaritra ny fisian'ny singa iray ao amin'ny sanda napetraka ary mahatakatra tsara ny toerana misy azy. Misy hevitra maromaro:

1. Ny sanda irina. Ity ny tady lahatsoratra, ny isa, izay tokony hikaroka ao amin'ny salan'isa.
2. Ny array no jerena. Ny fitambaran'ny angona karohina mba hahitana ny sanda voarakitra ao amin'ny tohan-kevitra teo aloha.
3. Karazana sarintany. Tsy voatery io hevitra io. Miaraka amin'izany dia afaka mahita ny angona marina kokoa ianao. Misy karazany telo ny fampitahana: 1 - sanda latsaky na mitovy (miresaka momba ny angon-drakitra isa isika, ary ny laharan-tariby mihitsy dia tsy maintsy alahatra amin'ny filaharana miakatra), 2 - mifanandrify tsara, -1 - sanda lehibe kokoa na mitovy.

Mba hanazavana, ohatra kely. Eto izahay dia nanandrana ny hahatakatra hoe iza amin'ireo fangatahana no manana fiovana maromaro latsaky ny 900 na mitovy.

Ny formula dia namerina ny sanda 3, izay tsy isa andalana tanteraka, fa iray havana. Izany hoe, tsy amin'ny adiresy, fa amin'ny isa mifandraika amin'ny fiandohan'ny angon-drakitra voafantina, izay afaka manomboka na aiza na aiza.

Vidin'ny DLSTR

Io fiasa io dia ahafahana manao kajy ny halavan'ny tady lahatsoratra. Mila tohan-kevitra iray - ny adiresin'ny sela na tady lahatsoratra. Ohatra, amin'ny marketing, tsara ny mampiasa azy io mba hijerena ny isan'ny litera ao amin'ny famaritana.

Ny asa CONCATENATE

Miaraka amin'ity operator ity, azonao atao ny mampifandray ireo soatoavina lahatsoratra maromaro ho tady lehibe iray. Ny tohan-kevitra dia sela na tady an-tsoratra mivantana amin'ny marika teny misaraka amin'ny faingo. Ary ity misy ohatra kely amin'ny fampiasana an'io fiasa io.

Fampiasa PROP

Ity mpandraharaha ity dia ahafahanao manao ny litera voalohany amin'ny teny manomboka amin'ny kapitaly. Mila tady lahatsoratra na asa iray izay mamerina ny iray ho tohan-kevitra tokana. Ity fiasa ity dia mety tsara amin'ny fanoratana lisitra izay ahitana anarana maromaro na toe-javatra hafa mety hahasoa azy.

FAMPANDROSOANA

Ity mpandraharaha ity dia ahafahana manala ireo tarehintsoratra tsy hita maso amin'ny tady lahatsoratra. Mandray hevitra iray ihany. Amin'ity ohatra ity, ny lahatsoratra dia misy endri-tsoratra tsy azo pirinty izay nesorin'ny fiasa.

Ity endri-javatra ity dia tokony hampiasaina amin'ny toe-javatra misy ny mpampiasa nandika lahatsoratra avy amin'ny programa hafa ary ireo tarehin-tsoratra tsy azo pirinty dia nafindra ho azy amin'ny takelaka Excel.

Ny asan'ny TRIM

Miaraka amin'ity operator ity, ny mpampiasa dia afaka manaisotra ny habaka tsy ilaina rehetra eo anelanelan'ny teny. Ahitana ny adiresin'ny sela, izay hany tohan-kevitra. Ity misy ohatra iray amin'ny fampiasana an'io fiasa io mba hamelana toerana iray eo anelanelan'ny teny.

FIND asa

Miaraka amin'io fiasa io, ny mpampiasa dia afaka mahita lahatsoratra ao anaty lahatsoratra hafa. Ity fiasa ity dia saro-pady. Noho izany dia tsy maintsy hajaina ny litera lehibe sy kely. Ity asa ity dia manana hevitra telo:

1. Ny lahatsoratra tiana. Io no tady tadiavina.
2. Ny lahatsoratra hojerena dia ny salan'isa nanaovana ny fikarohana.
3. Ny toeran'ny fanombohana dia tohan-kevitra azo atao izay mamaritra ny toetra voalohany hikaroka.

Ny asan'ny INDEX

Miaraka amin'io fiasa io, ny mpampiasa dia afaka mahazo ny sanda tadiaviny. Manana hevitra telo takiana izy io:

1. Array. Ny isan'ny angon-drakitra hodinihina.
2. Laharana andalana. Ny laharana ordinal an'ny andalana amin'ity salan'isa ity. Attention! Tsy adiresy fa laharana andalana.
3. Laharan'ny tsanganana. Mitovy amin'ny hevitra teo aloha, ho an'ny tsanganana ihany. Ity hevitra ity dia azo avela ho banga.

EXACT asa

Ity operator ity dia azo ampiasaina hamaritana raha mitovy ny tady lahatsoratra roa. Raha mitovy izy ireo dia mamerina ny sandany MARINA. Raha samy hafa izy ireo - ny lainga.

OR asa

Ity fiasa ity dia ahafahanao mametraka ny safidin'ny fepetra 1 na fepetra 2. Raha farafaharatsiny iray amin'izy ireo no marina, dia ny sanda miverina dia - MARINA. Azonao atao ny mamaritra ny sanda boolean hatramin'ny 255.

Function ary

Ny asa dia mamerina sanda iray MARINAraha toa ka mitovitovy ny sandany avokoa ny hevitra rehetra ao aminy.

Io no hevitra lojika manan-danja indrindra izay ahafahanao mametraka fepetra maromaro indray mandeha, izay tsy maintsy tandremana miaraka.

OFFSET asa

Ity fiasa ity dia ahafahanao mahazo reference amin'ny faritra iray izay omen'ny andalana sy tsanganana maromaro avy amin'ny fandrindrana tany am-boalohany. Tohan-kevitra: fanondroana ny sela voalohany amin'ny isan-karazany, firy ny andalana tokony hafindra, firy ny tsanganana tokony hafindra, inona ny haavon'ny faritra vaovao ary inona ny sakan'ny faritra vaovao.

Famaranana

Miaraka amin'ny fanampian'ny asa Excel, ny mpivarotra dia afaka mamakafaka mora kokoa ny fahombiazan'ny tranokala, ny fiovam-po ary ny tondro hafa. Araka ny hitanao, tsy misy programa manokana takiana, ny birao taloha tsara suite dia ampy hampihatra saika hevitra rehetra.