Sut i ysgrifennu fformiwla yn Excel? Hyfforddiant. Y fformwlâu mwyaf eu hangen

Prynhawn da

Un tro, roedd ysgrifennu fformiwla ar eich pen eich hun yn Excel yn rhywbeth anhygoel i mi. Ac er fy mod yn aml yn gorfod gweithio yn y rhaglen hon, ni wnes i lenwi unrhyw beth ond y testun ...

Fel y digwyddodd, nid yw'r mwyafrif o fformiwlâu yn ddim byd cymhleth a gallwch chi weithio gyda nhw'n hawdd, hyd yn oed i ddefnyddiwr cyfrifiadur newydd. Yn yr erthygl, yn union, hoffwn ddatgelu'r fformwlâu mwyaf angenrheidiol, y mae'n rhaid i mi weithio gyda nhw amlaf ...

Felly, gadewch i ni ddechrau ...

1. Gweithrediadau a hanfodion sylfaenol. Dysgwch hanfodion Excel.

Bydd yr holl gamau gweithredu yn yr erthygl yn cael eu dangos yn fersiwn Excel 2007.

Ar ôl cychwyn y rhaglen Excel - mae ffenestr yn ymddangos gyda llawer o gelloedd - ein bwrdd. Prif nodwedd y rhaglen yw y gall ddarllen (fel cyfrifiannell) eich fformiwlâu rydych chi'n eu hysgrifennu. Gyda llaw, gallwch ychwanegu fformiwla i bob cell!

Rhaid i'r fformiwla ddechrau gyda'r arwydd "=". Mae hyn yn rhagofyniad. Yna byddwch chi'n ysgrifennu'r hyn sydd angen i chi ei gyfrifo: er enghraifft, "= 2 + 3" (heb ddyfynbrisiau) ac yn pwyso'r fysell Enter - o ganlyniad, fe welwch fod y canlyniad "5" yn ymddangos yn y gell. Gweler y screenshot isod.

Pwysig! Er gwaethaf y ffaith bod y rhif "5" wedi'i ysgrifennu yng nghell A1, fe'i cyfrifir yn ôl y fformiwla ("= 2 + 3"). Os yn y gell nesaf ysgrifennwch “5” mewn testun - yna pan fyddwch chi'n hofran dros y gell hon yn y golygydd fformiwla (llinell uchod, Fx) - fe welwch y rhif cysefin "5".

Nawr dychmygwch y gallwch chi ysgrifennu mewn cell nid yn unig y gwerth 2 + 3, ond nifer y celloedd y mae angen i chi ychwanegu eu gwerthoedd. Gadewch i ni ddweud "= B2 + C2".

Yn naturiol, rhaid bod rhai rhifau yn B2 a C2, fel arall bydd Excel yn dangos i ni yng nghell A1 mai'r canlyniad yw 0.

Ac un pwynt pwysicach ...

Pan fyddwch chi'n copïo cell lle mae fformiwla, er enghraifft A1 - a'i gludo i mewn i gell arall - nid y gwerth "5" sy'n cael ei gopïo, ond y fformiwla ei hun!

Ar ben hynny, bydd y fformiwla'n newid mewn cyfrannedd uniongyrchol: h.y. os copïir A1 i A2, yna'r fformiwla yng nghell A2 fydd "= B3 + C3". Mae Excel yn newid eich fformiwla ei hun yn awtomatig: os yw A1 = B2 + C2, yna mae'n rhesymegol bod A2 = B3 + C3 (cynyddodd yr holl rifau 1).

Mae'r canlyniad, gyda llaw, yn A2 = 0, oherwydd nid yw celloedd B3 a C3 wedi'u diffinio, ac felly'n hafal i 0.

Felly, gallwch ysgrifennu'r fformiwla unwaith, ac yna ei chopïo i bob cell o'r golofn a ddymunir - a bydd Excel yn cyfrifo ym mhob rhes o'ch tabl!

Os nad ydych chi am i B2 a C2 newid wrth gopïo a bod ynghlwm wrth y celloedd hyn bob amser, yna ychwanegwch yr eicon “$” atynt. Mae enghraifft isod.

Fel hyn, lle bynnag y byddwch chi'n copïo cell A1, bydd bob amser yn cyfeirio at y celloedd cysylltiedig.

2. Ychwanegu gwerthoedd mewn rhesi (fformwlâu SUMM a SUMMESLIMN)

Wrth gwrs, gallwch ychwanegu pob cell trwy wneud y fformiwla A1 + A2 + A3, ac ati. Ond er mwyn peidio â dioddef, mae fformiwla arbennig yn Excel sy'n adio'r holl werthoedd yn y celloedd rydych chi'n eu dewis!

Cymerwch enghraifft syml. Mae sawl math o nwyddau mewn stoc, ac rydyn ni'n gwybod faint mae pob cynnyrch yn unigol mewn kg. mewn stoc. Gadewch i ni geisio cyfrifo, ond faint yw'r cyfan mewn kg. cargo mewn stoc.

I wneud hyn, ewch i'r gell lle bydd y canlyniad yn cael ei arddangos ac ysgrifennwch y fformiwla: "= SUM (C2: C5)". Gweler y screenshot isod.

O ganlyniad, bydd yr holl gelloedd yn yr ystod a ddewiswyd yn cael eu crynhoi, a byddwch yn gweld y canlyniad.

2.1. Ychwanegiad at y cyflwr (gydag amodau)

Nawr dychmygwch fod gennym ni amodau penodol, h.y. adio nid yr holl werthoedd yn y celloedd (Kg, mewn stoc), ond dim ond yn sicr, dyweder, gyda phris (1 kg.) o lai na 100.

Mae fformiwla wych ar gyfer hyn:CRYNODEBEnghraifft ar unwaith, ac yna esboniad o bob symbol yn y fformiwla.

= CRYNODEB (C2: C5; B2: B5; "<100")lle:

C2: C5 - y golofn honno (y celloedd hynny) a fydd yn cael ei hychwanegu;

B2: B5 - y golofn ar gyfer gwirio'r cyflwr (h.y. pris, er enghraifft, llai na 100);

"<100" - yr amod ei hun, nodwch fod yr amod wedi'i ysgrifennu mewn dyfynodau.

Nid oes unrhyw beth cymhleth yn y fformiwla hon, y prif beth yw arsylwi cymesuredd: C2: C5; B2: B5 - dde; C2: C6; B2: B5 - anghywir. I.e. rhaid i'r ystod crynhoi ac ystod yr amodau fod yn gymesur, fel arall bydd y fformiwla'n dychwelyd gwall.

Pwysig! Gall fod llawer o amodau ar gyfer y swm, h.y. Gallwch wirio nid yn ôl y golofn 1af, ond erbyn 10 ar unwaith, gan osod llawer o amodau.

3. Cyfrif nifer y rhesi sy'n bodloni'r amodau (mae'r fformiwla'n WLADOL)

Tasg eithaf cyffredin: cyfrifo nid swm y gwerthoedd yn y celloedd, ond nifer y celloedd o'r fath sy'n bodloni rhai amodau. Weithiau, mae yna lawer o amodau.

Ac felly ... gadewch i ni ddechrau.

Yn yr un enghraifft, gadewch inni geisio cyfrifo nifer yr eitemau sydd â phris sy'n fwy na 90 (os edrychwch chi, gallwch chi ddweud bod 2 gynnyrch o'r fath: tangerinau ac orennau).

I gyfrif y nwyddau yn y gell a ddymunir, ysgrifennom y fformiwla ganlynol (gweler uchod):

= CYFRIFON (B2: B5; "> 90")lle:

B2: B5 - yr ystod y cânt eu gwirio drwyddi, yn ôl yr amod a osodwyd gennym ni;

">90" - mae'r cyflwr ei hun wedi'i amgáu mewn dyfynodau.

Nawr, gadewch i ni geisio cymhlethu ein hesiampl ychydig, ac ychwanegu cyfrif yn ôl un amod arall: gyda phris o fwy na 90 + mae'r maint yn y warws yn llai nag 20 kg.

Mae'r fformiwla ar ffurf:

= GWLAD (B2: B6; "> 90"; C2: C6; "<20")

Yma mae popeth yn aros yr un fath, heblaw am un cyflwr arall (C2: C6; "<20") Gyda llaw, gall fod llawer o amodau o'r fath!

Mae'n amlwg na fydd unrhyw un yn ysgrifennu fformwlâu o'r fath ar gyfer bwrdd mor fach, ond ar gyfer tabl o gannoedd o resi, mae hwn yn fater hollol wahanol. Er enghraifft, mae'r tabl hwn yn fwy na gweledol.

4. Chwilio ac amnewid gwerthoedd o un tabl i'r llall (fformiwla VLOOKUP)

Dychmygwch fod tabl newydd wedi dod atom ni, gyda thagiau prisiau newydd ar gyfer y cynnyrch. Wel, os yw'r eitemau'n 10-20, gallwch eu hailosod i gyd â llaw. Ac os oes cannoedd o eitemau o'r fath? Mae'n llawer cyflymach pe bai Excel i'w gael yn annibynnol yn yr enwau paru o un tabl i'r llall, ac yna copïo'r tagiau prisiau newydd i'n hen fwrdd.

Ar gyfer tasg o'r fath, defnyddir y fformiwla VPR. Ar un adeg, roedd yn “ddoeth” gyda’r fformwlâu rhesymegol “IF” nes iddo gwrdd â’r peth rhyfeddol hwn!

Felly, gadewch i ni ddechrau ...

Dyma ein hesiampl + tabl newydd gyda thagiau prisiau. Nawr mae angen i ni amnewid y tagiau prisiau newydd o'r tabl newydd yn awtomatig i'r hen un (mae'r tagiau prisiau newydd yn goch).

Rhowch y cyrchwr yng nghell B2 - h.y. yn y gell gyntaf, lle mae angen i ni newid y tag pris yn awtomatig. Nesaf, rydyn ni'n ysgrifennu'r fformiwla, fel yn y screenshot isod (ar ôl y screenshot bydd esboniad manwl ohoni).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)lle

A2 - y gwerth y byddwn yn edrych amdano er mwyn cymryd tag pris newydd. Yn ein hachos ni, rydyn ni'n chwilio am y gair "afalau" yn y tabl newydd.

$ D $ 2: $ E $ 5 - dewiswch ein tabl newydd yn llwyr (D2: E5, mae'r dewis yn mynd o'r gornel chwith uchaf i'r groeslin dde isaf), h.y. lle bydd y chwiliad yn cael ei berfformio. Mae'r arwydd "$" yn y fformiwla hon yn angenrheidiol fel na fyddwch chi'n newid pan fyddwch chi'n copïo'r fformiwla hon i gelloedd eraill - D2: E5!

Pwysig! Dim ond yng ngholofn gyntaf eich tabl a ddewiswyd y chwilir am y gair "afalau", yn yr enghraifft hon, chwilir "afalau" yng ngholofn D.

2 - Pan ddarganfyddir y gair "afalau", rhaid i'r swyddogaeth wybod o ba golofn o'r tabl a ddewiswyd (D2: E5) i gopïo'r gwerth a ddymunir. Yn ein enghraifft, copïwch o golofn 2 (E), oherwydd yn y golofn gyntaf (D) fe wnaethon ni chwilio. Os bydd y tabl a ddewiswyd gennych ar gyfer y chwiliad yn cynnwys 10 colofn, yna bydd y golofn gyntaf yn chwilio, ac o 2 i 10 colofn - gallwch ddewis y rhif i'w gopïo.

I y fformiwla = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) amnewid gwerthoedd newydd yn lle enwau cynhyrchion eraill - dim ond eu copïo i gelloedd eraill yn y golofn gyda thagiau pris y cynnyrch (yn ein enghraifft ni, copïo i gelloedd B3: B5). Bydd y fformiwla yn chwilio ac yn copïo'r gwerth o golofn y tabl newydd sydd ei angen arnoch yn awtomatig.

5. Casgliad

Yn yr erthygl hon, gwnaethom archwilio hanfodion gweithio gydag Excel, sut i ddechrau ysgrifennu fformwlâu. Fe wnaethant roi enghreifftiau o'r fformwlâu mwyaf cyffredin y mae'n rhaid i'r rhan fwyaf o bobl sy'n gweithio yn Excel weithio gyda nhw yn aml.

Gobeithio y bydd yr enghreifftiau wedi'u dadosod yn ddefnyddiol i rywun ac y byddant yn helpu i gyflymu ei waith. Cael arbrawf da!

PS

A pha fformiwlâu ydych chi'n eu defnyddio? A yw'n bosibl symleiddio'r fformwlâu a roddir yn yr erthygl rywsut? Er enghraifft, ar gyfrifiaduron gwan, pan fydd rhai gwerthoedd yn newid mewn tablau mawr lle mae cyfrifiadau'n cael eu perfformio'n awtomatig, mae'r cyfrifiadur yn rhewi am ychydig eiliadau, gan adrodd a dangos canlyniadau newydd ...