Ootuspäraselt saab MS Excelis lahendatut rakendada ka Google'i arvutustabelites. Kuid arvukad katsed oma lemmikotsingumootori abil probleeme lahendada tõid ainult uusi küsimusi ja peaaegu null vastust.
Seetõttu otsustati teiste elu kergendada ja ennast ülistada.

Lühidalt peamise kohta

Selleks, et Excel või arvutustabel (Google'i arvutustabel) saaksid aru, et kirjutatu on valem, peate valemiribale panema märgi "\u003d" (joonis 1).

• tähtnumbriline (TÄHT \u003d VEERG; NUMBER \u003d RIDA) nt "A1".
• stiilis R1C1 tähistatakse süsteemis R1C1 nii read kui veerud numbritega.

Lahtri "B3" aadress näeb sellises süsteemis välja nagu R3C2 (R \u003d rida \u003d rida, C \u003d veerg \u003d veerg). Näiteks skriptide jaoks kasutatakse mõlemat stiili.
Kuhu kirjutame "\u003d valem", näiteks \u003d SUM (A1: A10) ja kuvatakse meie väärtus.
RC-valemite üldine tööpõhimõte on näidatud joonisel 2.

Pilt 2
Nagu näete jooniselt 3, on lahtri väärtused selle lahtri suhtes, kuhu võrdusmärgiga valem kirjutatakse. Valemite esteetilise väljanägemise säilitamiseks sisaldavad need sümboleid, mida saab välja jätta: RC \u003d RC.

Joonis 3
Jooniste 2 ja 3 erinevus seisneb selles, et joonis 3 on üldine ravimvorm, mis ei ole seotud ridade ja veergudega (vaadake ridade ja veergude väärtusi), mida ei saa öelda joonise 2 kohta. Kuid RC stiil arvutustabelit kasutatakse peamiselt javascripti skriptide kirjutamiseks.

Lingitüübid (adresseerimistüübid)

Lahtritele viitamiseks kasutatakse viiteid, mis on kolme tüüpi:

• Suhtelised lingid (näide, A1);
• Absoluutsed lingid (näiteks $ A $ 1);
• Segalingid (näiteks $ A1 või A $ 1, need on pooleldi suhtelised, pooleldi absoluutsed).

Siin märk $ tähistab lingi tüüpi. Erinevate linkide tüüpide erinevusi näete, lohistades aktiivse lahtri automaatse täitmise käepideme või lahtrivahemiku, mis sisaldab linkidega valemit.

Suhtelised lingid

Suhteline link "mäletab", millisel kaugusel (ridades ja veergudes) klõpsasite RELATIVE lahtrisse, kuhu panite "\u003d" (ridade ja veergude nihe). Seejärel tõmmake automaatse täitmise käepide alla ja see valem kopeeritakse kõikidesse lahtritesse, mille läbi sirutasime.

Absoluutsed lingid

Nagu eespool mainitud, kui lohistate automaatse täitmise markeril suhtelisi linke sisaldava valemi, arvutab tabel nende aadressid ümber. Kui valem sisaldab absoluutseid linke, jääb nende aadress muutumatuks. Lihtsamalt öeldes - absoluutne viide osutab alati samale lahtrile.
Suhtelise viite absoluutseks saamiseks pange lihtsalt veerutähe ja rea \u200b\u200baadressi ette märk "$", näiteks $ A $ 1. Veel kiire viis - valige suhteline link ja vajutage üks kord klahvi "F4", samal ajal kui arvutustabel ise paneb märgi "$" alla. Kui vajutate "F4" teist korda, muutub link segatüübiks A $ 1, kui kolmas kord - $ A1, kui neljas kord - muutub link jälle suhteliseks. Ja nii ringis.

Segalingid

Segalingid on pooleldi absoluutsed ja pooled suhtelised. Dollarimärk neis ilmub kas veeru tähe ette või rea numbri ette. See on kõige raskemini mõistetav lingitüüp. Näiteks sisaldab lahter valemit "\u003d A $ 1". Viide A $ 1 on veerus A suhteline ja absoluutne real 1. Kui lohistame selle valemi automaatse täitmise markeril alla või üles, osutavad kõigi kopeeritud valemite lingid lahtrile A1, see tähendab, et nad käituvad absoluutsetena. Kui lohistame siiski paremale või vasakule, käituvad lingid suhtelisena, see tähendab, et arvutustabel hakkab oma aadressi ümber arvutama. Seega kasutavad automaatse täitmise abil loodud valemid sama rea \u200b\u200bnumbrit ($ 1), kuid veeru otsene väärtus (A, B, C ...) muutub.

Vaatame näidet lahtrite summeerimisest korrutamisel kindla koefitsiendiga.

Selles näites eeldatakse koefitsiendi väärtuse olemasolu igas arvutatud lahtris (lahtrid D8, D9, D10 ... E8, F8 ...). (Joonis 4).
Punased nooled näitavad valemi täitekäepidemega venitamise suunda, mis asub lahtris C2. Pange valemis tähele muutust lahtris D8. Alla venitatuna muutub ainult stringi sümboliseeriv number. Paremale sirutades muudetakse ainult veergu.

Joonis 4
Lihtsustame näidet, kasutades märki $ (joonis 5).

Joonis 5
Kuid alati ei ole vaja kõiki veerge ja ridu külmutada, mõnikord kasutatakse ainult rida või ainult veeru külmutamist (joonis 6)

Joonis 6
Kõigi valemite kohta saate lugeda ametlikul veebisaidil support.google.com
Tähtis: valemites töödeldavaid andmeid ei tohiks olla erinevad dokumendid, seda saab teha ainult skriptide abil.

Valemivead

Kui valesti valesti kirjutasite, teavitatakse teid kommentaarist valemi süntaksivea kohta (joonis 7).

Joonis 7
Kuigi vead võivad olla mitte ainult süntaktilised, vaid ka näiteks matemaatilised, näiteks jagamine 0-ga (joonis 7) ja teised (joonised 7.1, 7.2, 7.3). Märkme kuvamiseks, milline viga ilmnes, hõljutage kursorit vea paremas ülanurgas oleva punase kolmnurga kohal.

Joonis 7.1

Joonis 7.2

Joonis 7.3
Tabeli lugemise mugavuse huvides värvime kõik lahtrid valemitega lillaga.
Valemite reaalajas nägemiseks peate vajutama kiirklahvi Ctrl + või valima ülamenüüst Kuva (vaade)\u003e Kõik valemid. (Joonis 8).

Joonis 8

Kuidas valemeid kirjutatakse

Erinevusi on teatmeteose valemite sõnastuses ja hetkel töötamiseks kasutatavates valemites. Need seisnevad selles, et paljudes valemites varem kasutatud "koma" asemel on juba kasutusel "semikoolon" (muudatused toimusid üle kuue kuu tagasi).
Selleks, et näha, millele valem sellel lehel viitab (joonis 9), peate klõpsama valemiribal sildist Fx paremal (Fx asub peamenüü all vasakul).

Joonis 9
TÄHTIS: Valemite korrektseks toimimiseks tuleb need kirjutada ladina tähtedega. Vene (kirillitsa) “A” või “C” ja ladina keeles “A” või “C” on valemi jaoks kaks erinevat tähte.

Valemid

Aritmeetilised valemid.

Muidugi ei kirjelda keegi liitmise, lahutamise jne igaveseid toiminguid, kuid need aitavad mõista põhitõdesid. Mõned näited näitavad teile, kuidas nad selles keskkonnas töötavad. Kõik valemid on toodud dokumendis, mille link on toodud artikli lõpus, kuid peatume lihtsalt ekraanipiltidel.

Liitmine, lahutamine, korrutamine, jagamine.

• Kirjeldus: liitmise, lahutamise, korrutamise ja jagamise valemid.
• Valemi tüüp: „Lahter_1 + lahter_2”, „Lahter_1-lahter_2”, „Lahter_1 * Lahter_2”, „Lahter_1 / lahter_2”
• Valem ise: \u003d E22 + F22, \u003d E23-F23, \u003d E24 * F24, \u003d E25 / F25.

Esialgsed andmed on meil vahemikus E22: H25 ja tulemus veerus D. Joonisel 10 on esitatud kõigi kasutatavate andmete päis.

Joonis 10

Edenemine.

• Kirjeldus: valem kõigi järgnevate lahtrite suurendamiseks ühe võrra (ridade ja veergude nummerdamine).
• Valemi tüüp: \u003d eelmine lahter + 1.
• Valem ise: \u003d D26 + 1

Tuletame meelde, et kui soovite kasutada vahemikku, summeerib see kõik lahtrid järjest ja kui peate lahtrid summeerima kindlas järjekorras, siis peate need määrama tähega „;” õiges järjekorras. Meil on raku D26 progresseerumise algandmed ja tulemus lahtrites E26: H26 (joonis 11), mida kasutatakse ridade ja veergude nummerdamiseks.

Joonis 11

Ümardamine.

• Kirjeldus: valem arvu ümardamiseks lahtris.
• Valemi tüüp: \u003d ROUND (arvuga lahter); loendur (mitu numbrit tuleb pärast koma ümardada).
• Valem ise: \u003d RING (E28; 2).

Esialgsed andmed on meil lahtris E28 ja tulemus lahtris D28 (joonis 12)

Joonis 12
“ROUND” ümardatakse matemaatilised seadused, kui pärast komakohta on number 5 või rohkem, siis suurendatakse kogu osa ühe võrra, kui 4 või vähem, jääb see muutumatuks, saate ümardada ka menüü FORMAT abil -\u003e Numbrid -\u003e "1000 , 12 "2 kohta pärast koma (joonis 13). Kui vajate rohkem märke, peate klõpsama valikut FORMAT -\u003e Numbers -\u003e Personalized decimal -\u003e Ja täpsustama märkide arvu.

Joonis 13

Summa, kui lahtrid pole järjestikused.

Ilmselt kõige tuttavam omadus

• Kirjeldus: erinevates lahtrites olevate arvude summa.
• Valemi tüüp: \u003d SUM (arv_1; arv_2; ... arv_30).
• Valem ise: "\u003d SUM (E30; H30)" kirjuta läbi ";" kui erinevad lahtrid.

Esialgsed andmed on meil lahtrites E30 ja H30 ning tulemused lahtris D30

(Joonis 14).
Summa, kui lahtrid on järjestikused.

• Kirjeldus: üksteisele järgnevate arvude liitmine (järjestikku).
• Valemi tüüp: \u003d SUM (number_1: number_N).
• Valem ise: \u003d SUM (E31: H31) "kirjuta läbi": "kui see on pidev vahemik.
• Esialgsed andmed on meil rakkude E31: H31 vahemikus ja tulemused lahtris D31 (joonis 15).

Joonis 15

Keskmine.

• Kirjeldus: arvude vahemik summeeritakse ja jagatakse vahemikus olevate lahtrite arvuga.
• Valemi tüüp: \u003d KESKMINE (lahtri number või number_1; lahtri number või number_2; ... lahtri number või number_30).
• Valem ise: \u003d KESKMINE (E32: H32)

Esialgsed andmed on meil lahtrite E32: H32 vahemikus ja tulemused lahtris D32 (joonis 16).

Joonis 16
Muidugi on ka teisi, kuid läheme kaugemale.

Tekstivalemid.

Suurest hulgast tekstivalemitest, millega saate tekstiga teha kõike, mida soovite, on minu arvates populaarseim tekstiväärtuste "liimimise" valem. Selle rakendamiseks on mitu võimalust:

Teksti väärtuste (valem) liimimine.

• Kirjeldus: tekstiväärtuste liimimine (variant A).
• Valemi tüüp: \u003d CONCATENATE (lahtri number / tekst või tekst_1; lahter numbri / teksti või tekstiga_2;…, lahter numbri / tekstiga või tekstiga_30).
• Valem ise: \u003d CONCATENATE (E36; F36; G36; H36).

Esialgsed andmed on meil rakkude E36: \u200b\u200bH36 vahemikus ja tulemused lahtris D36 (joonis 17).
Google Docsi abil viivad nad läbi töötajate küsitlusi või koostavad arvamusküsitlusi Google Formsi kaudu (need on spetsiaalsed vormid, mida saab luua menüü Lisa-\u003e Vorm kaudu. Pärast vormi täitmist esitatakse andmed tabelis. Ja siis nad kasutavad andmetega töötamiseks erinevaid valemeid, näiteks täisnime kleepimiseks).

Joonis 17

Arvuliste väärtuste liimimine.

• Kirjeldus: tekstiväärtuste “liimimine” käsitsi, ilma erifunktsioone kasutamata (variant B - valemi käsitsi kirjutamine, valemi keerukus on ükskõik milline.).
• Valemi tüüp: \u003d lahter numbri / tekstiga 1 & "" & lahter numbriga / tekstiga 2 & "" & lahter numbriga / tekstiga 3 & "" & lahter numbri / tekstiga 3 & "" & lahter numbriga / tekstiga 4 ("" - tühik ja märk tähendab kleepimist , kõik tekstiväärtused kirjutatakse jutumärkides “”).
• Valem ise: \u003d E37 & "" & F37 & "" & G37 & "" & H37.

Esialgsed andmed on meil lahtrite E37: H37 vahemikus ja tulemused lahtris D36 (joonis 18 - liimitud numbrid).

Joonis 18

Numbriliste ja tekstiliste väärtuste liimimine.

• Kirjeldus: tekstiväärtuste "liimimine" käsitsi, ilma erifunktsioone kasutamata (variant C - segatüüp, valemi keerukus on ükskõik milline).
• Valemi tüüp: \u003d "text_1" & cell_1 & "text_2" & cell_2 & "text_3" & cell_3
• Tähtis: kogu tekst, mis kirjutatakse tähega “”, ei muutu valemi jaoks.
• Valem ise: \u003d "veel 1" & E38 & "use" & F38 & "like US" & G38.

Esialgsed andmed „veel 1”, „kasutamine”, „nagu USA” ja lahtrite vahemikus E38: G38 on olemas, seega on soovitatav kasutada sellist valemit ja tulemus on lahtris D36 (joonis 19) .
Liimige tekst ja arvväärtused kokku.

Joonis 19

LOOGILISED JA MUUD

Andmete edastamine sama faili mis tahes lehtedelt.

Oleme jõudnud minu arvates kõige huvitavamate funktsioonideni: Loogilised ja muud.
Üks kõige kasulikumaid valemeid:

• Kirjeldus: andmete ülekandmine sama faili mis tahes lehtedelt (Exceli jaoks saate teisaldada nii ühe töövihiku lehelt teise sama töövihiku lehele kui ka ühe töövihiku lehelt teise töövihiku lehele).
• Valemi tüüp: \u003d "Sheet_Name"! lahter_1
• Valem ise: \u003d Andmed! A15 (Andmed on leht, A15 on selle lehe lahter).

Esialgsed andmed on meil andmelehel, lahtris A15 (joonis 20) ja tulemus valemi lehel lahtris D41 (joonis 20.1).

Joonis 20

Joonis 20.1

Valemite massiiv.

Enamik tabelarvutusprogramme sisaldab kahte tüüpi massiivivalemeid: "multi-cell" ja "single-cell".
Google'i arvutustabelid eraldab need tüübid kaheks funktsiooniks: JÄTKA ja ARRAYFORMULA.
Mitme lahtriga massiivvalemid võimaldavad valemil tagastada mitu väärtust. Saate neid kasutada isegi teadmata, lihtsalt sisestades valemi, mis tagastab mitu väärtust.
Ühe lahtriga massiivivalemid võimaldavad teil kirjutada valemeid väljundi asemel massiivi sisendi abil. Kui lisate funktsiooni \u003d ARRAYFORMULA valemi, saate massiivid või vahemikud edastada funktsioonidele ja operaatoritele, mis tavaliselt kasutavad ainult mittemassiivseid argumente. Neid funktsioone ja operaatoreid rakendatakse üks massiivi iga kirje jaoks ja tagastab uue massiivi koos kogu väljundiga.
Kui soovite probleemi lähemalt uurida, peaksite külastama saiti support.google.
Andmemassiive tagastavate valemitega töötamiseks peate süntaksivigade vältimiseks lisama need valemite massiivi.

IF tingimustega lahtrite liitmine.

Et töötada loogiliste valemitega, mis tavaliselt sisaldavad suuri andmemassiive, paigutatakse need valemite massiivi ARRAYFORMULA (valem).

• Kirjeldus: IF-tingimusega lahtrid (valem SUMIF).
• Valemi tüüp: \u003d SUMIF (‘Sheet’! Range; kriteeriumid; ‘Leaf’! Total_range)

Valemi selgitamiseks vaatleme lähemalt näidet: 3 ostjat said ülesandeks osta tooteid nimekirjast, kuid maksta ühes summas. Pärast toidukaupade purunemist kassas oli veerus A toodete loetelu (joonis 21) ja veerus B nende kogus.
Ülesanne, missugune on eelarvekviitung pärast printimist (peate lihtsalt lisama 3 ostja tooted ja teada saama toodete arvu kokku igas ametikohas)?

Joonis 21
Esialgsed andmed on meil andmelehel (joonis 21) ja tulemus valemi lehel veerus D (joonis 22). Veerud E, F, G näitavad valemis kasutatud argumente ja veerg H näitab valemi üldvaadet, mis asub veerus D ja arvutab tulemuse.

Joonis 22
Ülaltoodud näide näitab üldist vaadet selle kohta, kuidas valem „Sum If” töötab ühe tingimusega, kuid kõige sagedamini kasutatakse „Sum IF” (mitme tingimusega).

Lahtrite summeerimine, kui on palju tingimusi.

Jätkame erineval tasemel toodete probleemiga arvestamist.
Pidu alles algab ja pärast sõpradele helistamist hakkate mõistma, et alkoholi jääb väheks. Ja peate selle ostma. Iga su sõber peaks kaasa võtma kange joogi. Peate välja selgitama, mitu õllepudelit peate kaasa võtma, ja andke oma sõpradele ülesanne.

• Kirjeldus: IF summa (mitme tingimusega).
• Valemi tüüp: \u003d SUMIF (‘Andmed’! Vahemik_1 & ‘Andmed’! Vahemik_2; kriteeriumid_1 ja kriteerium_2; ‘Andmed’! Kokku_vahemik).
• Valem ise: \u003d (ARRAYFORMULA (SUMIF ((andmed! E: E ja andmed! F: F); (B53 ja C53); andmed! G: G)))

Esialgsed andmed on meil andmelehel (joonis 23).

Joonis 23
Oletame, et lehel Vormel peaks lahtris B53 (kriteerium_1 \u003d Õlu) olema joogi nimi ja lahter C53 (kriteerium_2 \u003d 2) on sõprade arv, kes õlut toovad. Selle tulemusena sisaldab lahter D53 tulemust, et peame ostma 15 pudelit õlut. (Joonis 23.1), see tähendab, et valem määrab summa kahe kriteeriumi põhjal - õlu ja sõprade arv.

Joonis 23.1
Kui selliseid positsioone on rohkem, jooned 16 ja 21 (joonis 24), siis summeeritakse veerus G olevate mullide arv (joonis 24.1).

Joonis 24
Kokku:

Joonis 24.1

Nüüd toome huvitavama näite:

Ha ... pidu jätkub ja te mäletate, et vajate kooki, kuid mitte lihtsat ja super-megakooki, millel on erinevad vürtsid, mis õnneks on ka numbritega krüpteeritud. Väljakutse on osta vürtse igast vürtsist õiges koguses kotte. Kokk krüpteeris vajaliku koguse tabeli (joonis 25.1) veergudes A ja B (kõrvalolevates veergudes teeme arvutused).
Igal vürtsil on oma seerianumber: 1,2,3,4. (Joonis 25).

Joonis 25
Meie ülesandeks on korrata korduvate väärtuste arvu, meie puhul on need veerus B olevad numbrid 1–4 ja määrata iga vürtse protsent.

• Kirjeldus: ühesuguste numbrite loendamine suurtes massiivides täiendavatel tingimustel.
• Valemitüüp: COUNT IF ('valem'! Vahemik_A55: A61 + 'valem'! Vahemik_B55: B61; tingimusA “vürtsid” + tingimusB ”number 1–4”; leht “valem”! Vahemik_B55: B61) / tingimusB ”number alates 1 kuni 4 ")

• Valem ise: \u003d ((ARRAYFORMULA (SUMIF ("Valem"! $ A $ 55: $ A $ 61 & "Valem"! $ B $ 55: $ B $ 61; $ F $ 55 & $ E59; "Valem") ! $ B $ 55: $ B $ 61))) / $ E59)

Esialgsed andmed on meil vahemikus A55: B61, valimistingimuse valivad lahtrid F55 ja E59: E62 ning tulemus jääb lahtrite F59: F62 vahemikku (loendades tingimustes arvväärtuste korduste arvu) vaste).

• Kirjeldus: Arvutab vürtside protsendi.
• Valemi tüüp: Summa * 100% / Kogusumma
• Valem ise: \u003d F58 * $ G $ 56 / F $ 56

Joonis 25.1
Lõppkokkuvõttes on meil korduste arv ja protsent.
Valemi õigeks kirjutamiseks peate täielikult mõistma, mis teil on, mida soovite saada ja millisel kujul. Selleks peate võib-olla muutma algandmete välimust.
Edasi järgmise näite juurde

Väärtuste loendamine ühendatud lahtrites.

Kui valemites kasutatakse väärtusi "ühendatud lahtrites", näidatakse ühendatud andmete esimene lahter, meie juhul on see veerg F ja lahter F65 (joonis 26)



Joonis 26.
Ja lõpuks jõudsime kõige halvemate valemiteni.

Loendab argumentide loendis olevate arvude arvu.

Selliseid arvutusi on mitut tüüpi, need sobivad suurte tabelite jaoks, milles peate loendama identsete sõnade arvu või numbrite arvu. Kuid nende valemite õige mõistmise korral saate teha nendega selliseid imesid nagu näiteks: lugedes sõnu, arvestamata erandite sõnu. Vaadake näiteid allpool.

• Kirjeldus: Loendab lahtrite arvu, mis sisaldavad numbreid ilma tekstimuutujateta.
• Valemi tüüp: COUNT (väärtus_1; väärtus_2; ... väärtus_30)
• Valem ise: \u003d COUNT (E45; F45; G45; H45)

Esialgsed andmed on meil lahtrite E70: H70 vahemikus ja tulemuseks on lahter D70 (joonis 27 - arvulisi väärtusi sisaldavate lahtrite loendamine vahemikus, mis sisaldab tekstiga lahtreid).

Joonis 27.
Samuti ei loeta lahtreid, mis sisaldavad teksti ja numbreid.

Joonis 27.1.

Tekstimuutujatega numbreid sisaldavate lahtrite loendamine.

• Kirjeldus: Loendab tekstimuutujatega numbreid sisaldavate lahtrite arvu.
• Valemi tüüp: COUNTA (väärtus_1; väärtus_2;… väärtus_30)
• Valem ise: \u003d COUNTA (E46: H46)

Esialgsed andmed on meil lahtrite E71: H71 vahemikus ja tulemused lahtris D71 (joonis 28 - loendades kõiki vahemiku väärtusi).

Joonis 28.
Samuti loeb valem lahtrid, mis sisaldavad ainult kirjavahemärke, vahelehti, kuid ei loe tühje lahtreid.

Joonis 28.1

Väärtuste asendamine tingimustel.

• Kirjeldus: Väärtuste asendamine tingimustel.
• Valemi tüüp: "\u003d IF (AND ((tingimus1); (tingimus2)); tulemus on 0, kui tingimused 1 ja 2 on täidetud; kui ei, siis on tulemus 1)"
• Valem ise: "\u003d IF (AND ((F73 \u003d 5); (H73 \u003d 5)); 0; 1)"

Esialgsed andmed on meil lahtrites F73 ja H73 ning tulemus lahtris D73 (kui F73 \u003d 5 ja H73 \u003d 5, siis D73 \u003d 0 kõigil muudel juhtudel 1) (joonis 29).

Joonis 29.

Joonis 29.1
Keerulisem näide.
Loendage lahtrite arv, millesse ajavahemik on kirjutatud, välja arvatud sõnad "automaatvastus", "hõivatud", "-".

• Valemi tüüp: "\u003d COUNTA (Range_A) -COUNTIF (Range_A;" automaatne vastus ") - COUNTIF (Range_A;" - ") - COUNTIF (Range_A;" hõivatud ")"

• Valem ise: \u003d COUNTA ($ E74: $ H75) -COUNTIF ($ E74: $ H75; "vastus") - COUNTIF ($ E74: $ H75; "-") - COUNTIF ($ E74: $ H75; "hõivatud ")

Esialgsed andmed on meil rakkude E74: H75 vahemikus ja tulemused lahtris D74 (joonis 30).

Joonis 30
Nii oleme jõudnud oma väikese Google SpreadSheet'i valemitega seotud õppeprogrammi lõppu ja mul on suured lootused, et olen valemitega analüütilise töö mõningaid aspekte valgustanud.
Kui aus olla, siis vormelid olid sõna otseses mõttes raskesti võidetud. Igaüks neist on aja jooksul loodud. Loodan, et teile meeldis minu artikkel ja selles olevad näited.
Ja lõpuks kingituseks. Ja andku arendajad mulle andeks!

Valem "DOCUMENT KILLER".

Kui teil on vaja dokument igaveseks varjata uudishimulike pilkude eest, on see valem mõeldud teile.
Valem ise: "\u003d (ARRAYFORMULA (SUMIF ($ A: $ A & $ C: $ C; $ H: $ H & F $ 2; $ C: $ C)))". $ H: $ H reguleerib valemi jaotust. Pärast fomlula käivitamist (joonis 31) hakkab see lahtrites allpool korrutama järgmist funktsiooni CONTINUE (lahter; rida; veerg).

Joonis 31
Valem lisab tsükliliselt kogu valemi veeru. Dokumendi tapmiseks peate natuke proovima, looma N-nda lahtrite arvu ja kirjutama valemi N-i veergude arvu esimestesse lahtritesse. Kõik! Keegi teine \u200b\u200bei saa dokumenti parandada ja kontrollida!
Siin on see, mida Google'i abileht ütleb töökoormuse ja piirangute kohta -

Arvutuste korraldus 2007 tabeleid, Suhteline viide, Absoluutne viide, Segatud viide, Sisseehitatud funktsioon, Loogiline funktsioon, Tingimuslik funktsioon, 9. informaatika Bosov, 9. informaatika

Arvutustabelite peamine eesmärk on igasuguste arvutuste korraldamine. Teate seda juba:
- Arvutamine on valemitega arvutamise protsess;
- Valem algab võrdusmärgiga ja võib sisaldada operatsioonimärke, numbreid, viiteid ja sisseehitatud funktsioone. Mõelgem kõigepealt arvutustabelite arvutuste korraldamisega seotud küsimustele.

3.2.1. Suhtelised, absoluutsed ja segalülid
Viide tähistab lahtrit või lahtrivahemikku, mida soovite valemis kasutada. Lingid võimaldavad:
- kasutada ühes valemis arvutustabeli erinevates osades;
- kasutage ühe lahtri väärtust mitmes valemis. Linke on kahte peamist tüüpi:
1) suhteline - olenevalt valemi positsioonist;
2) absoluutne - ei sõltu valemi positsioonist.
Suhteliste ja absoluutsete viidete vahe ilmneb siis, kui kopeerite valemi praegusest lahtrist teistesse lahtritesse.
Suhtelised lingid. Suhteline viide valemis määrab andmeelemendi asukoha selle lahtri suhtes, kuhu valem on kirjutatud. Valemit sisaldava lahtri positsiooni muutmisel viide muutub.
Vaatleme lahtrisse A2 kirjutatud valemit \u003d A1 ^ 2. See sisaldab suhtelist A1-viidet, mida tajutakse lauaprotsessor järgmiselt: lahtri sisu, mis on ühe rea võrra kõrgem sellest, milles valem asub, peaks olema ruut.
Valemi kopeerimisel piki veergu ja rida korrigeeritakse suhtelist viidet automaatselt järgmiselt:
- ühe veeruga nihutatuna muutub veeru nimes ühe tähe viide;
- ühe joone nihe viib rea numbri viite muutumiseni ühe võrra.
Näiteks kui kopeerite valemi lahtrist A2 lahtritesse B2, C2 ja D2, muutub suhteline viide automaatselt ja ülaltoodud valemiks saab: \u003d B1 ^ 2, \u003d C1 ^ 2, \u003d D1 ^ 2. Sama valemi kopeerimisel lahtritesse AZ ja A4 saame vastavalt \u003d A2 ^ 2, \u003d AZ ^ 2 (joonis 3.4).

Näide 1. 8. klassis käsitlesime teatud linna elanikkonna probleemi, kasvades igal aastal 5%. Arvutame arvutustabelites välja linna hinnangulise elanike arvu järgmise 5 aasta jooksul, kui käesoleval aastal on see 40 000 inimest.
Sisestame tabelisse originaali, sisestame suhteliste linkidega lahtrisse OT valemi \u003d B2 + 0,05 * B2; kopeerige valem lahtrist OT lahtrite B4: B7 vahemikku (joonis 3.5).



Meie (vastavalt probleemi tingimusele) viisime iga-aastase populatsiooni arvutamise läbi sama valemi järgi, mille esialgsed olid alati samas veerus asuvas lahtris, kuid arvutusvalemist ühe rea võrra kõrgemal. Suhtelisi viiteid sisaldava valemi kopeerimisel tehti vajalikud muudatused automaatselt.
Absoluutsed lingid. Absoluutne viide valemis viitab alati lahtrile kindlas (fikseeritud) asukohas. Absoluutviites paigutatakse iga tähe ja numbri ette $, näiteks $ A $ 1. Valemit sisaldava lahtri asukoha muutmine absoluutset viidet ei muuda. Valemi kopeerimisel mööda jooni ja veerge absoluutset viidet ei korrigeerita (joonis 3.6).

Näide 2. Kindel kodanik avab pangakonto hinnaga 10 000 rubla. Talle teatati, et iga kuu suureneb hoiuse summa 1,2%. Võimaliku summa ja hoiusumma juurdekasvu selgitamiseks 1, 2, ..., 6 kuu möödudes tegi kodanik järgmised arvutused (joonis 3.7).

Segalingid. Segatud viide sisaldab kas täiesti adresseeritavat veergu ja suhteliselt adresseeritavat stringi ($ A1) või suhteliselt adresseeritavat veergu ja absoluutselt adresseeritavat stringi (A $ 1). Valemit sisaldava lahtri positsiooni muutmisel muutub aadressi suhteline osa, kuid aadressi absoluutne osa ei muutu.
Valemite kopeerimisel või täitmisel mööda ridu ja veerge korrigeeritakse suhtelist viidet automaatselt ja absoluutset viidet ei korrigeerita (joonis 3.8).

Lingi teisendamiseks suhtelisest absoluutseks ja vastupidi saate selle sisestusreal valida ja vajutada klahve F4 (Microsoft Office Excel) või Shift + F4 (OpenOffice.org Calc). Kui valite suhtelise viite, näiteks A1, siis esimest korda seda klahvi (klahvikombinatsiooni) vajutades määratakse nii rea kui ka veeru absoluutsed viited ($ A $ 1). Teisel klõpsul saab absoluutse lingi ainult string (A $ 1). Kolmandal klõpsamisel saab absoluutse viite ainult veerg ($ A1). Kui vajutate uuesti klahvi F4 (klahvikombinatsioon Shift + F4), määratakse veeru ja rea \u200b\u200bjaoks uuesti suhtelised lingid (A1).
Näide 3. Esimese kümne numbrite lisamiseks on vaja koostada tabel, see tähendab järgmise vormi tabeli täitmiseks:

Selle tabeli suvalise lahtri täitmisel liidetakse veeru A ja rea \u200b\u200b1 lahtrite vastavad väärtused. Teisisõnu, esimene termin jääb veeru nimeks muutmata (peaksite sellele absoluutselt viitama ), kuid rea number muutub (peaksite sellele suhtelise viite andma); teine \u200b\u200btermin muudab veeru numbrit (suhteline viide), kuid rea number (absoluutne viide) jääb muutumatuks.
Sisestage lahtrisse B2 valem \u003d $ A2 + B $ 1 ja kopeerige see kogu vahemikku B2: J10. Teil peaks olema täiendav tabel, mida iga esimese klassi laps tunneb.


3.2.2. Sisseehitatud funktsioonid
Arvutustabelites andmete töötlemisel saate kasutada sisseehitatud funktsioone - eelnevalt määratletud valemeid. Funktsioon tagastab argumentidena toimivate väärtuste toimingute tegemise tulemuse. Funktsioonide kasutamine võimaldab teil valemeid lihtsustada ja arvutusprotsessi selgemaks muuta.
Arvutustabelites rakendatakse mitusada sisseehitatud funktsiooni, mis jagunevad: matemaatilisteks, statistilisteks, loogilisteks, tekstilisteks, finantsilisteks jne.
Igal funktsioonil on ainulaadne nimi, mida kasutatakse selle kutsumiseks. Nimi on tavaliselt funktsiooni nime loomulik keeleline lühend. Tabelarvutuste tegemisel kasutatakse sageli järgmisi funktsioone:
SUM (SUM) - argumentide summa;
MIN (MIN) - väikseima väärtuse määramine argumentide loendist;
MAX (MAX) - määrake argumentide loendist suurim väärtus.
Funktsiooniviisardi dialoogiboks lihtsustab valemite loomist ning minimeerib kirjavigu ja süntaksivigu. Funktsiooni valemisse sisestamisel kuvatakse dialoogiboksis Funktsioonide viisard funktsiooni nimi, kõik selle argumendid, funktsiooni kirjeldus ja kõik argumendid, funktsiooni praegune tulemus ja kogu valem.
Näide 4. Rahvusvahelistel võistlustel ühel spordialal kohtuniku määramise reeglid on järgmised:
1) iga sportlase sooritust hindab N kohtunikku;
2) heidetakse iga sportlase maksimum- ja miinimumhind (üks, kui neid on mitu);
3) sportlase jaoks võetakse arvesse järelejäänud märkide aritmeetilist keskmist.
Teave võistluse kohta on esitatud tabelis:



See on kohustatud arvutama kõigi võistlusel osalejate hinned ja määrama võitja hinde. Selle jaoks:
1) lahtritesse A10, A1, A12 ja A14 sisestame tekstid "Maksimaalne tulemus", "Minimaalne tulemus", "Lõplik tulemus", "Võitja skoor";
2) lahtrisse B10 sisestame valemi \u003d MAX (OT: B8); kopeerige lahtri B10 sisu lahtritesse C10: F10;
3) lahtrisse В11 sisestame valemi \u003d MIN (ВЗ: В8); kopeerige lahtri B10 sisu lahtritesse C11: F11;
4) lahtrisse B12 sisestame valemi \u003d (SUM (OT: B8) -B10-B11) / 4; kopeerige lahtri B12 sisu lahtritesse C12: F12;
5) lahtrisse B14 sisestame valemi \u003d MAKC (B12: F12).

3.2.3. Loogikafunktsioonid
Eelmise materjali uurimisel olete korduvalt kohanud loogilisi toiminguid EI, JA, VÕI (EI, JA, VÕI). Nende abil loodud loogilisi väljendeid kasutasite andmebaasides otsingute korraldamisel, mitmesuguste arvutusprotsesside programmeerimisel.
Loogikaoperatsioone rakendatakse ka arvutustabelites, kuid siin esitatakse need funktsioonidena: kõigepealt kirjutatakse nimi loogiline operatsioonja siis on sulgudes loetletud loogilised operandid.
Näiteks kahepoolsele ebavõrdsusele 0 vastav loogiline avaldis<А1<10, в электронных таблицах будет записано как И(А1>0; A1<10).
Pidage meeles, kuidas me andmebaaside ja Pascali programmeerimiskeelega tutvudes kirjutasime sarnase loogilise avaldise.
Näide 5. Arvutame arvutustabelites kõigi selles sisalduvate loogiliste muutujate võimalike väärtuste loogilise avaldise EI A ja EI B väärtused.

Selle probleemi lahendamisel järgisime tuntud algoritmi loogilise avaldise tõetabeli koostamiseks. Arvutused lahtrivahemikes SZ: C6, D3: D6, EZ: E6 viiakse läbi arvuti vastavalt meie määratletud valemitele.

Tingimuste kontrollimiseks arvutustabelites arvutuste tegemisel rakendatakse loogilist IF-funktsiooni, mida nimetatakse tingimusfunktsiooniks.
Tingimusfunktsioonil on järgmine struktuur:
KUI A (<условие>; <действие1>; <действие2>)
Siin<условие> - Boole'i \u200b\u200bavaldis, see tähendab mis tahes operatsioonide ja loogiliste toimingute abil loodud avaldis, mis saab väärtuse TRUE või FALSE.
Kui tõeväärtus on tõene, määratakse selle lahtri väärtus, kuhu tingimusfunktsioon on kirjutatud<действие1>kui vale -<действие2>.
Mis meenutab teile tingimuslikku funktsiooni?
Näide 6. Mõelge kooli korvpallimeeskonda vastuvõtmise probleemile: õpilane võib sellesse meeskonda vastu võtta, kui tema pikkus on vähemalt 170 cm.
Teave taotlejate kohta (nimi, kõrgus) esitatakse arvutustabelis.

Tingimusfunktsiooni kasutamine lahtrivahemikus СЗ: С8 võimaldab teil teha otsuse (aktsepteeritud / mitte heaks kiidetud) iga taotleja kohta.
Funktsioon COUNTIF võimaldab teil loendada teatud tingimustele vastavate lahtrite arvu vahemikus. See funktsioon arvutab lahtris C9 meeskonda valitud taotlejate arvu.

matemaatiliste valemite kirjutamine

Valemiredaktori üldised omadused ja käivitamine

Valemite kirjutamine ja redigeerimine Wordis toimub valemiredaktori Microsoft Equation 3.0 abil, mis sisaldab umbes 120 malli. See võimaldab teil oma dokumenti lisada matemaatilisi märke ja väljendeid, sealhulgas murdarvud, astmed, integraalid jne. Valemi kirjutamisel rakendatakse selle erinevatele komponentidele automaatselt sobivad stiilid (eksponentide vähendatud fondisuurus, muutujate kursiiv jne).

Näide. Valemiredaktori käivitamine.

1. Asetage kursor valemi sisestamise ja muutmise kohta.

2. Menüüs Sisestamäärake käsk Objekt…, avage dialoogiboks Objekti sisestamine.

3. Vahekaardil Olendvaldkonnas Objekti tüüp:valime MicrosoftEquation3.0.

4. Klõpsake nuppu OK.

See avab valemiredaktoriga töötamise dialoogi.

Valemiredaktori käivitamiseks olemasoleva valemi redigeerimiseks topeltklõpsake valemisväljal.

Valemi redigeerimise või kirjutamise lõpuleviimine toimub väljaspool valemi sisestamise kasti.

Vormeliredaktori liides

Pärast valemiredaktori käivitamist avaneb valemiredaktori aken, millel on oma tööriistariba. See paneel koosneb kahest reast nuppe:

 juurdepääs tähemärkidele,

 juurdepääs mallide komplektidele.

Klaviatuurilt saate valemisse sisestada vene ja ladina tähestiku tähed, samuti märgid lihtsamatest matemaatilistest toimingutest (+, -, /).

Numbririda tähemärkide juurde pääsemiseks võimaldab valemisse sisestada matemaatilisi sümboleid (kreeka tähestiku operatsioonimärgid ja tähed).

Järgmised märgistikud asuvad tööriistariba ülemisel real vasakult paremale:

 suhete sümbolid;

 intervallid ja punktid;

 matemaatilised erinevused;

 operatsioonimärgid;

 noolemärgid;

• hulga teooria sümbolid;

 loogilised märgid;

 erinevad sümbolid;

 kreeka tähed.

Tööriistariba mallide abil saate valemisse lisada hulga matemaatiliste toimingute märke, määrata integraalide, summade, toodete sümboleid. Lisaks võimaldavad mallid määrata matemaatilise avaldise vormi (murd, aste, indeks, maatriks jne) matemaatiliste sümbolite järgnevaks sisestamiseks malli abil saadud toorikusse.

Tööriistariba alumine rida sisaldab järgmisi mallide komplekte vasakult paremale:

 piirangute mallid;

 murdude ja juurte mallid;

 tellimuste ja ülaindeksite loomine;

 integraalid;

 joonib alla ja alla;

 tähistatud nooled;

 Hulgateooria teosed ja mallid;

 Maatriksimallid.

Valemimärkide sisestamisel on kursor tähemärkide "või" kujul. Valemisse sisestatud märk asetatakse vertikaalsest ribast paremale või vasakule ja sisendkursori horisontaaljoone kohale.

Valemite kirjutamine ja redigeerimine

Valemi kirjutamisel ja redigeerimisel saab järgmise märgi selle lõppu sisestada põhisisestusreale - sisestatud märgi koht märgitakse automaatselt piluga (ristkülik punktiirjoonega). Kui peate sisestama summa, integraali või muu keeruka valemistruktuuri sümboli, peaksite hiirega valima vastavasse mallikomplekti vastava ikooni.

Mallide abil saadud toorikud saab sisestada pesa keskele. Seega luuakse mitmeastmelised valemid.

Olemasoleva valemi muutmine hõlmab selle üksikute elementide kustutamist ja uute sisestamist valemiredaktori abil.

Näide. Valemi fragmendi kirjutamine.

Tutvustame vormi valemi fragmenti: .

1. Klõpsake, et avada alammenüü koos summamallide komplektiga.

2. Valige hiirega klõpsates ülemise ja alumise piiriga summamall (ülemises reas parempoolseim mall).

Selle tulemusel ilmub valemi redigeerimise aknasse tühi koht: .

3. Sisestame pärast sisestuskursori sinna asetamist igasse pilusse vajaliku sümboli, numbri või avaldise ning valemi fragment saab soovitud kuju.

Näide. Vormelelemendi kustutamine.

1. Valige hiirega klõpsates kustutatav element.

2. Vajutage klahvi .

Kui valemielement on malli abil loodud fragmendi osa, siis pärast selle kustutamist sisendpesa. Sisendpesa saab kustutada ainult koos malliga, kuhu see kuulub.

Mõnel juhul võib pärast valemi elementide kustutamist mõne selle ülejäänud elemendi graafiline kujutis olla katki. Valemi normaalse välimuse taastamiseks käivitage käsk Joonista ümbermenüü Vaade.

Näide. Uute üksuste lisamine valemisse.

1. Asetage sisestuskursor valemis soovitud kohta.

2. Tutvustame vajalikku sümbolite jada.

3. Vajadusel sisestage malli abil blanket ja täitke seejärel selle pesad vajalike sümbolitega.

Näide. Valemi kirjutamine murdjoonega.

.

1. Asetage kursor valemi asukohta.

3. Klaviatuuri abil valemi sisestamise pesasse sisestage valemi algus "

».

4. Komplektis Murd- ja juuremustridklõpsake mallil

(ülemine vasak mall).

See sisestab murdosa lugeja ja nimetaja malli kahe piluga.

5. Sisestage nimetaja pesasse avaldis

ja lugeja pesas -

.

6. Mallide komplektis Abikirjade ja ülaindeksite loominevalige mall, mis määrab ülemise parema indeksi loomise.

7. Sisestage ilmuvas pesas avaldis kraadi "n-1" kohta.

8. Asetage kursor valemi juba sisestatud osa lõppu.

9. Mallide komplektis Abikirjade ja ülaindeksite loominevali mall.

10. Sisestage peapesa ilmunud tühja kohta sümbol „ e", Ja parema ülaindeksi pilusse sisestame kraadi avaldise" - nt».

Klõpsates valemiväljal, sulgege valemi redigeerimise dialoog.

Maatriksivalemite kirjutamine

Maatriksivalemite kirjutamiseks tööriistariba alumisse ritta on komplekt Maatriksimallid.

Näide. Lokkisulgudega valemi kirjutamine.

Kaaluge vormi valemi kirjutamist:

2. Avage valemiredaktori aken.

3. Klaviatuurilt valemi sisestamise pesasse sisestage y= ».

4. Komplektis Piiraja mallidklõpsa mallil.

See sisestab lokkis traksid, millel on pilu paremal.

5. Asetage kursor nimetatud pesasse.

6. Valige Matrixi mallide komplektis mall :.

Selle tulemusena muudetakse lokkis traksist paremal asuv pilu kaheks üksteise kohal asuvaks piluks. See suurendab proportsionaalselt lokkis trakside enda suurust.

7. Sisestage ülemisse ja alumisse pilusse vastavad valemilaused.

8. Sulgege hiire klõpsamisega valemi loomise dialoog.

Näide. Maatriksi valemi kirjutamine.

Mõelge 3. järgu determinandi valemi kirjutamise näitele:

.

1. Asetage kursor valemi asukohta.

2. Avage valemiredaktori aken.

3. Klaviatuurilt valemi sisestamise raami pesasse sisestage " \u003d".

4. Avage komplekt Maatriksimallidja valige mall:

5. Avaneb dialoogiboks Maatriks... Määrame maatriksi ridade arvu ja veergude arvu.

6. Klõpsates akna maatrikskujutisest vasakule ja paremale, klõpsake vertikaalsed jooned piki maatriksi servi.

7. Lülitite rühmas Veergude joondaminevalige lüliti Keskus.

8. Valige raadionuppude reas Rea joondamine raadionupp Põhiliinil... Klõpsake nuppu OK.

See sisestab maatriksitooriku, millel on kolm rida, kolm veergu ja vertikaalsed jooned külgedel.

9. Sisestage esimese rea esimesse pilusse sümbol „ ja».

10. Valige mallide komplektis Loo alam- ja ülaindeksid mall, mis määrab parempoolse alaindeksi loomise.

11. Sisestame selle "11".

12. Täitke ülejäänud pilud samamoodi.

13. Klõpsates valemiväljal, sulgege valemi loomise dialoog.

Valemi suuruse muutmine ja valemi teisaldamine

Valemi suuruse muutmine ja teisaldamine toimub otse Wordi dokumendi peaaknas. Enne nende toimingute tegemist tuleb valem valida hiireklõpsuga.

Näide. Valemi suuruse muutmine.

1. Valige valem hiirega klõpsates.

2. Asetage hiirekursor ühele kaheksast valikukasti käepidemest ja lohistage seda, kuni saate soovitud suuruse.

Kui muudate valemi suurust ebaproportsionaalselt, võib elementide suhteline asukoht olla rikutud.

Valemi skaala muutmiseks valige valem ja valige Muuda | Objekt | Valem | Avatud... Seejärel valige menüüst sobiv skaala (25% kuni 400%) Vaade.

Näide. Liigutage valemit.

1. Valige valem hiirega klõpsates.

2. Viige hiirekursor valemi kohale nii, et see oleks vasakule suunatud noole kujul.

3. Vajutage hiire vasakut nuppu ja lohistage valem dokumendis soovitud kohta.

4. Valemi horisontaalse asukoha muutmiseks määrake käsk Lõige…menüü Vormindusja määrake valemi abil lõigu parameetrite jaoks nõutavad väärtused.

See on peatükk raamatust: Michael Girwin. Ctrl + Tõst + Enter. Massiivivalemite valdamine Excelis.

See postitus on mõeldud neile, keda huvitavad keerulised massiivivalemid. Kui teil on vaja ainulaadsete väärtuste loend lihtsalt üks kord kätte saada, on palju lihtsam kasutada täpsemat filtrit või PivotTable-liigendtabelit. Valemite kasutamise peamised eelised on automaatsed värskendused lähteandmete või valikukriteeriumide muutmisel / lisamisel. Enne lugemist on soovitatav täiendada eelmistes materjalides sisalduvaid ideid:

• (11. peatükk);
• (13. peatükk);
• (15. peatükk);
• (17. peatükk).

Joonis: 19.1. Ainulaadsete kirjete hankimine Optioni abil Täpsem filter

Laadige märkus alla vormingus või näited vormingus

Eemaldage unikaalne loend ühest veerust, kasutades valikut Täpsem filter

Joonisel fig. 19.1 näitab andmekogumit (vahemik A1: C9). Teie eesmärk on saada nimekiri ainulaadsetest võidusõiduradadest. Kuna peate säilitama algandmed, ei saa te seda võimalust kasutada Eemaldage duplikaadid (menüü ANDMED –> Töö andmed –> Eemaldage duplikaadid). Kuid võite kasutada Täpsem filter... Dialoogiboksi avamiseks Täpsem filter, mine läbi menüü ANDMED –> Sorteeri ja filtreeri –> Lisaksvõi hoidke all klahvi Alt ja vajutage seejärel järjestikku S, L (Exceli 2007 või uuema versiooni puhul).

Avatud dialoogiboksis Täpsem filter (joonis 19.1) määrake valik kopeerige tulemus teise asukohta, märkige ruut Ainult ainulaadsed kirjed, määrake piirkond, kust unikaalsed väärtused hangitakse ($ B $ 1: $ B $ 9), ja esimene lahter, kuhu toodud andmed paigutatakse ($ E $ 1). Joonisel fig. 19.2 näitab tulemuseks ainulaadset loendit (vahemik E1: E6). Kui te ei sisesta välja nime Algne vahemik Dialoogikast Täpsem filter (Selle asemel, et joonisel 19.1 määratleda $ B $ 2: $ B $ 9) Excel käsitleb vahemiku esimest rida välja nimena ja võite saada duplikaadi. Joonisel fig. 19.3 näitab unikaalse loendi ühte paljudest võimalikest kasutusvõimalustest.

Ainulaadse loendi toomine kriteeriumi alusel koos valikuga Täpsem filter

Viimases näites otsisite unikaalse loendi ühest veerust. Täpsem filter võib kriteeriumi abil hankida ka ainulaadse kirjekomplekti (st kogu algse tabeli rea). Joonisel fig. 19.4 ja 19.5 näitavad olukorda, kus soovite eraldada ainulaadseid kirjeid vahemikust A1: D10, mille ettevõtte nimi on ABC. Hiljem selles peatükis näete, kuidas seda tööd teha valemi abil. Kuid kui te ei vaja, et protsess oleks automaatne, võite seda kasutada Täpsem filter, mis on kindlasti lihtsam kui valem.

Joonis: 19.4. Teil on vaja ainulaadseid kirjeid ABC ettevõtte jaoks; pildi suurendamiseks paremklõpsake seda ja valige Ava pilt uuel vahelehel

Joonis: 19.5. Kasutamine Täpsem filter kriteeriumide alusel kordumatute kirjete hankimine on valemimeetodist palju lihtsam. Kuid kriteeriumide või lähteandmete muutumisel ei värskendata allalaaditud kirjeid automaatselt.

Ainulaadse loendi toomine ühest veerust pöördtabeli abil

Kui kasutate juba pöördtabeleid, siis teate, et iga kord, kui panete piirkonda mis tahes välja Keelpillid või Veerud (Joonis 19.6), saate automaatselt ainulaadse loendi. Joonisel fig. Joonisel 19.6 on näidatud, kuidas saate kiiresti luua ainulaadse võidusõiduradade loendi ja seejärel lugeda nende külastuste arvu. Ehkki pöördtabel on unikaalse loendi ühest veerust allalaadimiseks kasulik, pole see tõenäoliselt kriteeriumide alusel ainulaadsete kirjete toomiseks kasulik.

Joonis: 19.6. Sa võid kasutada kokkuvõtlik tabelkui vajate unikaalset loendit ja selle põhjal järgnevat arvutust

Eemaldage unikaalne loend ühest veerust valemite ja abiveergude abil

Abistaja veeru kasutamine muudab ainulaadsete andmete hankimise lihtsamaks kui massiivivalemite kasutamine (joonis 19.7). Selles näites kasutatakse tehnikaid, mille olete õppinud (funktsiooni COUNTIF abil) ja (abistaja veergu kasutades). Kui muudate nüüd algandmeid vahemikus B2: B9, kajastavad valemid neid muudatusi automaatselt piirkonnas D15: D21.

Massiivivalem: eraldage funktsioonist VÄIKE ainulaadne loend ühest veerust

Kuna selles jaotises kasutatud massiivvalemite mõistmine on üsna keeruline, jaotatakse nende loomine etappideks: esimene on fragment, mis loeb ainulaadseid väärtusi (17. peatükk); teine \u200b\u200bon kriteeriumipõhine andmete väljavõtmine (15. peatükk). Joonisel fig. Joonisel 19.8 on toodud unikaalsete väärtuste arvutamise valem (kuna see on massiivivalem, saate selle sisestada, vajutades klahvikombinatsiooni Ctrl + Tõst + Enter). Pange tähele selle valemi järgmisi aspekte:

1. Funktsioon FREQUENCY tagastab arvude massiivi (joonis 19.9): võistlusraja esmakordsel ilmumisel tagastatakse selle esinemiste arv algandmetes; iga järgneva võistlusraja korral tagastatakse null (vt). Näiteks on Sumner massiivi esimesel ja viiendal positsioonil. Esimeses positsioonis tagastab funktsioon FREQUENCY 2 - suve koguarv vahemikus B2: B9, viiendal positsioonil - 0.
2. Funktsioon FREQUENCY asub argumendis logi väljendus funktsioon IF, nii et funktsioon IF tagastab mis tahes nulli väärtuse puhul TÕENE ja mistahes nullist erinev väärtus FALSE.
3. Argument väärtus_tõene Funktsioon IF sisaldab 1, nii et funktsioon SUM loeb selliste arvude arvu.

Joonis: 19.8. Funktsioon FREQUENCY asub argumendis logi väljendus toimib IF

Joonis: 19.9. (1) funktsioon FREQUENCY tagastab arvude massiivi; (2) funktsioon IF tagastab nullarvude korral 1 ja nullide korral FALSE

Nüüd loome valemi unikaalse loendi hankimiseks. Joonisel fig. 19.10 näitab argumenti paigutatud suhteliste positsioonide massiivi massiiv toimib VÄIKESEKS.

Eelmises näites (joonis 19.9) argumendis väärtus_tõene IF-funktsioonil oli üks eraldatud, nii et IF-funktsioon tagastas ühe ja FALSE. Siin (joonis 19.10) argument väärtus_tõene sisaldab: LINE ($ B $ 2: $ B $ 9) -LINE ($ B $ 2) +1. Seetõttu tagastab IF-funktsioon (funktsiooni SMALL sees) suhtelise positsiooni numbri ainulaadse võistlusrajaga vahemikus ehk FALSE for take (joonis 19.11).

Joonis: 19.11. Funktsioon IF tagastab relatiivse positsiooninumbri ainulaadse võistlusrajaga vahemikus või FALSE for take

Joonisel fig. 19.12 näitavad valemi tulemusi. Joonisel fig. 19.13 näitab, et niipea kui algandmed muutusid, kajastasid valemid neid muudatusi kohe. Aga mis siis, kui lisate uued kirjed? Järgmisena näete, kuidas luua dünaamilise vahemiku valemeid.

Joonis: 19.13. Kui algandmed muutuvad, värskendatakse valemit kohe. Filtrit ja täpsemat filtrit ei saa automaatselt värskendada ilma VBA-koodi kirjutamata

Massiivivalem: unikaalse loendi eraldamine ühest veerust dünaamilise vahemiku abil

Lisame veel viimane näide mida olete õppinud valemite kohta, mis kasutavad dünaamilisel vahemikul põhinevaid konkreetseid nimesid (). Joonisel fig. 19.14 on nime määramise valem Rada... Selles valemis eeldatakse, et te ei sisesta kunagi 51. rea järel kirjet.

Joonis: 19.14. Nime määratlus Rada valemi põhjal

Kui olete nime tuvastanud, saate seda kasutada mis tahes valemis. Joonisel fig. Joonisel 19.15 on näidatud, kuidas nime abil ainulaadsete väärtuste arv loendada (võrrelge joonisega 19.8). Ja joonisel fig. 19.16 näitab valemit, mis eraldab võistlusradade loendist unikaalsed väärtused ise. Pange tähele, et jupi asemel vahemik<>"" (Nagu see oli joonistel 19.8 ja 19.10), kasutatakse funktsiooni ETEXT (mis tahes tekst tagastab TÕENE väärtuse). Kui sisestate ETEXTi kasutamisel numbri (nagu lahtris B11) või mõne muu tekstivälise teksti, ignoreerib valem seda väärtust. Joonisel fig. 19.17 näitab, et valem eraldab kõik uued jäljenimed automaatselt, ignoreerides numbreid.

Joonis: 19.16. Dünaamilise vahemiku põhjal ainulaadse joondusnime hankimine

Looge rippmenüü jaoks unikaalsete väärtuste valem

Äsja nähtud näite põhjal määratleme keskmine nimi - TrackList, mis põhineb ka dünaamilisel vahemikul, kuid viidates nüüd ainulaadsete jälgede loendile (vahemik E5: E14, joonis 19.18). Kuna vahemik E5: E14 sisaldab ainult teksti ja tühje väärtusi (nullpikkusega teststringid - ""), siis argumendis otsingu_väärtus funktsioon MATCH saab kasutada metamärke *? (mis tähendab vähemalt ühte märki). Ja vaidluses match_type funktsioon MATCH peaks kasutama väärtust –1, et leida veerus viimane tekstielement, mis sisaldab vähemalt ühte märki. Nagu on näidatud joonisel fig. 19.18, siis saate väljal kasutada konkreetset nime Allikas aken Sisendväärtuste kinnitamine (täpsemat teavet rippmenüü loomise kohta vt). Rippmenüü saab laieneda ja kokku tõmmata, kui veerus B lisatakse või eemaldatakse uusi andmeid.

Seal, kus metamärke tuleb kohelda nagu tavalisi tähemärke

Nagu te õppisite, tuleb mõnikord metamärke käsitleda sümbolitena. Joonisel fig. 19.18 näitab, kuidas saate selliste juhtumite valemeid muuta. Lisate tilde enne argumentide vahemikku otsingu_väärtus Funktsioon OTSI ja lisage argumendis vahemiku tagaküljele tühi string otsingu_vajutus.

Kriteeriumide alusel ainulaadsete kirjete hankimiseks abistaja veeru või massiuvalemi kasutamine

Selle postituse alguses näidati, et ainulaadsete kirjete hankimiseks kriteeriumide alusel töötab see suurepäraselt Täpsem filter... Kui vajate aga kiiret värskendust, võite kasutada abistaja veergu (joonis 19.20) või massiivivalemeid (joonis 19.21).

Dünaamilised valemid klientide nimede ja müügi väljavõtmiseks

Valemid on näidatud joonisel fig. 19.22. Näiteks kui lisate uue kirje TTVeoautod liinil 17 , lahtris F15 olev valem SUMIF lisab uue väärtuse automaatselt. Kui lisate veergu B uue kliendi, kuvatakse see kohe veerus E ja veerus F olev valem SUMIF näitab uut koguarvu.

Joonis: 19.22. Kindla nime ja kahe massiivivalemi kasutamine ainulaadsete klientide ja müügi hankimiseks

Pange tähele, et argumendis funktsioon SUMIF summa_vahemiksisaldab ühte lahtrit - $ C $ 10. Siin on see, mida SUMIF-i valemiabi sellel teemal ütleb: argument summa_vahemik ei pruugi olla sama suur kui argument vahemik... Reaalselt lisatavate lahtrite määramisel kasutatakse algusrakuna argumendi ülemist vasakut lahtrit summa_vahemikja seejärel summeeritakse argumendi suurusele vastava vahemiku osa lahtrid vahemik... Lahtritesse E15 ja F15 sisestatud valemid kopeeritakse mööda veerge.

Numbriliste väärtuste sortimine

Numbrite sorteerimise valemid on üsna lihtsad, kuid segatud andmete sortimiseks on need meeletult keerukad. Seega, kui te ei vaja kohest värskendamist, siis on parem teha valemiteta, kasutades valikut Sorteerimine... Joonisel fig. 19.23 näitab kahte sortimisvalemit.

Joonisel fig. 19.24 näitab, kuidas saate numbrite sortimiseks kasutada abistaja veergu. Kuna funktsioon RANK ei sorteeri samu numbreid (andes neile sama auastme), on nende eristamiseks lisatud funktsioon COUNTIF. Pange tähele, et funktsioonil COUNTIF on laiendatud vahemik, mis alustab ühte rida. See on vajalik selleks, et mis tahes numbri esmakordne ilmumine ei aitaks. Numbri teine \u200b\u200bilmumine tõstab auastet ühe võrra. See järjestikune numeratsioon määrab järjestuse, milles funktsioonid INDEX ja SEARCH saavad kirjeid vahemikus A8: B12.

Kui saate lubada luua andmete ekstraktimispiirkonnas abiveergu (vahemik A10: A14 joonisel 19.25), on mugav rakendada ülalkirjeldatud numbrite sorteerimist funktsiooni SMALL alusel ja selle põhjal välja võtta massiivi funktsiooni abil.

Joonis: 19.25. Kui te ei saa abistaja veergu kasutada, rakendage SMALL sortimist (lahtris A11) ja massiivi valemit (lahtris B11)

Sageli on ettevõtluses ja spordis nõutav N parima väärtuse ja nende väärtustega seotud nimede väljavõtmine. Alustage lahendit valemiga COUNTIF (lahter A11 joonisel 19.26), mis määrab kuvatavate kirjete arvu. Pange tähele, et argument kriteerium funktsioonis COUNTIF lahtris A11 - enam-vähem väärtus lahtris D8. See võimaldab teil kuvada kõik piirväärtused (meie näites, ehkki soovite kuvada Top 3, on neli sobivat väärtust).

Joonis: 19.26. Esikolmiku tulemuste ja neile vastavate nimede hankimine. Kui lahtris D8 muutub N, uuendatakse ala A15: B21

Tekstiväärtuste sortimine

Kui saate kasutada abikolonni, pole ülesanne nii keeruline (joonis 19.27). Võrdlusoperaatorid töötlevad tekstimärke märkidele omistatud numbriliste ASCII-koodide põhjal. Lahtris C3 tagastab esimene funktsioon COUNTIF nulli ja teine \u200b\u200blisab ühe. C4: 2 + 1, C5: 0 + 2, C6: 3 + 1.

Segatud andmete sorteerimine

Valem, mis võimaldab teil segatud andmetest unikaalsed väärtused välja tõmmata ja need siis sortida, on väga suur (joonis 19.28). Selle loomisel kasutati ideid, millega selles raamatus varem kokku puututi. Alustame valemi uurimist, vaadates, kuidas töötab tavaline Exceli sortimisfunktsioon.

Excel sorteerib tulemused järgmises järjekorras: kõigepealt numbrid, seejärel tekst (sh nullpikkused stringid), FALSE, TRUE, veaväärtused nende ilmumise järjekorras, tühjad lahtrid. Kogu sortimine toimub vastavalt ASCII koodidele. ASCII-koode on 255, millest igaüks vastab arvule 1 kuni 255:

Näiteks 5 on ASCII 53 ja S on ASCII 83. Kui sorteerida need kaks väärtust, 5 ja S, madalamast suurimani, siis on 5 suurem kui S, kuna 53 on väiksem kui 83.

Vahemikus A2: A5 (joonis 29) olevad andmekogumid teisendatakse vastavalt sortimisreeglitele vahemikuks E2: E5. Sorteerimispõhimõtete paremaks mõistmiseks kaaluge väärtusi vahemikus C2: C5. Näiteks kui esitate küsimuse "Kui palju ma olen auastmest kõrgemal?" lahtris A2 (54678) olevale ID-le on vastus , sest järjestatud loendis on ID 54678 kõige ülemine. SD-987-56 peal on kolm ID-d. Väärtuste saamiseks vahemikku C2: C5 vajate valemit.

Kõigepealt valige vahemik E1: H1 ja sisestage valemiribale \u003d TRANSPOSE (A2: A5), sisestage valem, vajutades Ctrl + Shift + Enter (joonis 19.30). Järgmisena valige valemiribal vahemik E2: H5, tippige \u003d A2: A5\u003e E1: H1 ja sisestage valem, vajutades klahvikombinatsiooni Ctrl + Shift + Enter (joonis 19.31). Joonisel fig. Joonis 19.32 näitab tulemust TRUE ja FALSE väärtuste ristkülikukujulise massiivina, mis vastavad saadud massiivi igale lahtrile, vastusena küsimusele "Kas rea pealkiri on suurem kui veeru pealkiri?"

Joonis: 19.30. Valige vahemik E1: H1 ja sisestage massiivivalemid

Joonis: 19.31. Sisestage vahemikku E2: H5 massiivi valem \u003d A2: A5\u003e E1: H1

Joonis: 19.32. Iga vahemik E2: H5 lahter sisaldab vastust küsimusele "Kas rea pealkiri on suurem kui veeru pealkiri?"

Näiteks esitab lahter E3 küsimuse: SD-987-56\u003e 54678. Kuna 54678 on väiksem kui SD-987-56, on vastus TÕSI. Pange tähele, et vahemik E3: H3 sisaldab kolme TRUE ja ühte FALSE väärtust. Vaadates tagasi joon. 19.29 näete, et number kolm on lahtris C3.

Nagu on näidatud joonistel 19.33 ja 19.34, saate TRUE ja FALSE väärtused teisendada ühedeks ja nullideks, lisades massiivi valemile topeltnegatiivid. Kuna algse massiivi (E2: H5) mõõt on 4 × 4 ja soovite tulemust 4 × 1 massiivi kujul, kasutage funktsiooni MULTIFUNCTION (vt joonised 19.35 ja). Funktsioon MULTIPLE on massiivi funktsioon, seega sisestage see, vajutades klahvikombinatsiooni Ctrl + Shift + Enter (joonis 19.36). Selle asemel, et kasutada vahemikku E2: H5, lisage valemi sisse sobivad elemendid (joonis 19.37).

Joonis: 19.36. Valides vahemiku C2: C5 ja sisestades massiivi funktsiooni MULTIPLE, saate arvude veeru, mis ütleb, kui palju ID-sid on valitud järjestatud loendis

Joonis: 19.37. Abivahemiku E2: H5 kasutamise asemel lisatakse vastavad elemendid valemi sisse

Joonisel fig. 19.38 näitab, kuidas saab asendada hulga konstande väärtusega STRING ($ A $ 2: $ A $ 5) ^ 0.

Joonis: 19.39. Võimalike tühjade lahtritega toimetulekuks tuleks kõiki A2: A5 esinemisi täiendada IF-ga (A2: A5<>"", A2: A5); funktsioon STRING sellist lisamist ei vaja, kuna funktsioon töötab lahtriaadressiga, mitte selle sisuga

Kuna lõplikku valemit kasutatakse mujal, peate kõik vahemikud absoluutseks tegema (joonis 19.40). Joonisel fig. 19.41 näitab saadud väärtusi.

Joonis: 19.40. Vahemikud A2: A5 muutusid absoluutseks

Kuna seda elementi kasutatakse tulevikus kaks korda, saate selle konkreetse nime alla salvestada. Nagu näidatud dialoogiboksis (joonis 19.42), on valemi nimeks SZB - mitu väärtust on rohkem.

1. Argument massiiv funktsioon INDEX viitab algsele vahemikule A2: A5.
2. Esimene funktsioon MATCH annab funktsioonile INDEX märku massiivi A2: A5 elemendi suhtelisest asendist.
3. Kuigi argument otsingu_väärtus funktsioon OTSI jääb tühjaks.
4. Määratud nimi (SZB) argumendis otsingu_vajutus võimaldab teil kõigepealt juurde pääseda elemendile, mille väärtus on 0, seejärel 2 ja lõpuks 3.
5. Null argumentatsioonis match_type määrab täpse vaste, mis välistab viited duplikaatidele.

Joonis: 19.43. Lahtris A11 andmete ekstraktimiseks ja sortimiseks alustate valemit. Argument otsingu_väärtus funktsioon SEARCH on praegu tühi

Enne argumendi loomist otsingu_väärtus funktsioon OTSI, pidage meeles, mida tegelikult vajate. On kolm unikaalset ID-d, mis tuleb sortida, seega vajate argumendis kolme numbrit otsingu_väärtus kui valem kopeeritakse alla. Need numbrid võimaldavad teil leida suhtelise positsiooni massiivist A2: A5, mille peate esitama funktsioonile INDEX:

1. Lahtris A11 tagastab funktsioon MATCH 0, mis vastab 1 suhtelisele positsioonile määratud MSB nimes.
2. Kui valem kopeeritakse lahtrisse A12, peaks funktsioon MATCH tagastama arvu 2 ja suhtelise positsiooni \u003d 4 MSB-s.
3. Lahtris A13 peaks funktsioon MATCH tagastama 3 ja suhteline positsioon \u003d 2 MSB-s.

Pilt tekib, kui mõelda, mis argument otsingu_väärtus valemi allapoole kopeerimisel peab päring vastama: "Andke minimaalne väärtus SZB konkreetse nime sees, mida pole veel kasutatud." Nagu on näidatud joonisel fig. 19.44 valemielement MIN (IF (UND (OTSI ($ A $ 2: $ A $ 5; A $ 10: A10; 0)); SZB)) tagastab valemi alla kopeerimisel minimaalse väärtuse, vastates päringule täpselt. Põhjus, miks see töötab, on see, et UNM-i fragmendis (SEARCH ($ A $ 2: $ A $ 5; A $ 10: A10; 0)) võrreldakse kahte loendit (vt). Pange tähele argumendis laienevat vahemikku A $ 10: A10 otsingu_vajutus... Lahtris A11 aitab UND ja MATCH kombinatsioon SZB-st välja tuua kõik unikaalsed numbrid ja anda neile funktsioon MIN. Kui kopeerite valemi lahtrisse A12, on lahtrisse A11 eraldatud ID jälle laiendatud vahemikus ja leitakse jälle vahemikust $ A $ 2: $ A $ 5. Kuid UND tagastab FALSE ja MWB-st ei võeta ühtegi väärtust. Selle nägemiseks sisestage joonisel 19.44 massiivivalem, vajutades Ctrl + Shift + Enter ja kopeerige see alla.

Joonis: 19.44. Valemi element argumendis otsingu_väärtus funktsioon MATCH vastab päringule: "Sisestage konkreetse SZB nime minimaalne väärtus, mida pole veel kasutatud"

Joonisel fig. 19.45 näitab, et argument otsingu_vajutus teine \u200b\u200bfunktsioon OTSI vahemikku A $ 10: A10 on laienenud väärtusele A $ 10: A11. Selle valemi toimimise mõistmiseks valige järjestikku selle fragmendid ja klõpsake F9 (joonised 19.46-19.49).

Joonis: 19.45. Laiendatav vahemik A $ 10: A11 sisaldab nüüd (lahtris A12) esimest ID-d (54678)

Joonis: 19.46. UND ja teise otsingu funktsioonide kombinatsioon annab hulga booleansi; kaks FALSE väärtust välistavad nullväärtused konkreetsest MSB nimest

Joonis: 19.47. Nullid on välistatud ja alles jäävad numbrid 3 ja 2; number 2 on miinimum, seega tuleks see välja tõmmata järgmiselt

Joonis: 19.48. Funktsioon MIN valib numbri 2; nüüd saab funktsioon MATCH leida funktsiooni INDEX õige suhtelise positsiooni

Joonis: 19.49. Funktsioon INDEX saab väärtuse 2, mis vastab ID suhtelisele neljandale positsioonile vahemikus A2: A5

Naastes lahtrisse A11, saate lisada veel ühe tingimuse, nii et tühjad lahtrid valemit ei mõjutaks (joonis 19.50).

Joonis: 19.50. MIN-funktsiooni sees on kaks tingimust; esimene: "lahtrid pole tühjad?", teine: "väärtust pole veel kasutatud?"

Joonisel fig. 19.51 on lõplik valem. Lisatud on tingimus, et vahemiku A11: A15 read jäävad tühjaks pärast sorteeritud unikaalsete väärtuste hankimist. Joonisel fig. 19.52 näitab, mis juhtub, kui lahter A3 tühjaks teha. Meie täiendus tühjade lahtrite kontrollimiseks töötas.

See polnud lihtne. Aga kui olete nii kaugele lugenud, loodan, et teile meeldis.

ARVUTUSED ELEKTRONIKA TABELITES

2.1. V o d f o r m u l s

Lahtri väärtuse arvutamine toimub valemi sisestamise teel. Valemid algavad alati võrdusmärgiga “ = ”.

Valemid võimaldavad teil teha töölehe lahtrite väärtustega tavalisi matemaatilisi toiminguid. Näiteks peate lisama väärtused lahtritesse B1 ja B2 ning kuvama nende summa lahtris B5. Selleks asetage kursor lahtrisse B5 ja sisestage valem “\u003d B1 + B2”.

Vormeli sisestus kuvatakse nii tabeli lahtris kui ka valemiribal. Kui nuppu vajutatakse Sisenema arvutused tehakse ja tulemus saadakse aktiivses lahtris.

Valemites saab kasutada järgmisi operaatoreid:

a r i f m e t i c e -

w a n i -

t e c t a -

& - tekstiväärtuste liitmine.

Valemite arvutamisel tabelis rakendatakse operatsioonide aritmeetilist järjekorda.

2.2. VORMIDE LOOMINE PALJU

Lahtrikoordinaadid saab valemis sisestada, suunates kursori soovitud lahtrile. Valemi käsitsi sisestamisel on oht eksida. Seda saab vältida järgmiselt:

asetage kursor lahtrisse, kuhu soovite valemi sisestada;

sisestage võrdusmärk “\u003d”;

asetage kursor lahtrisse, mille koordinaadid peaksid olema valemi alguses, ja klõpsake hiire nuppu;

sisestage operaator (näiteks märk +) või muu märk, mis jätkab valemit;

viige kursor lahtrisse, mille koordinaate soovite valemis kasutada, ja klõpsake hiire nuppu;

teostage neid toiminguid kuni valemi lõppemiseni.

2.3. Ab s o luut ja umbes s ja t e l n e c e c

Aadresse (linke) on kolme peamist tüüpi: suhteline, absoluutne ja segatud.

Erinevused suhteliste ja absoluutsete viidete vahel ilmnevad valemite kopeerimisel ja teisaldamisel ühest lahtrist teise.

Kui teisaldate või kopeerite, absoluutsed lingid valemites ei muutu ja suhtelisi linke värskendatakse automaatselt uue positsiooni põhjal.

Näiteks lahter A1 sisaldab konstanti 4, lahtrid B1 kuni B10 sisaldavad väärtusi 0,1 kuni 1 sammuga 0,1. Tulemuse saamiseks lahtrites D1: D10 vastavalt valemile 4b i, kus i \u003d 1, 2,…, 10, peate sisestama lahtrisse D1 “\u003d $ A $ 1 * B1” ja valemi kopeerima rakud D2, D3,…, D10. Sel juhul sisaldab D2 fraasi "$ A $ 1 * B2", D3-s - "$ A $ 1 * B3" jne, kus $ A $ 1 sisu ei muutu, kuna aadress (link ) on absoluutne ja B1 muutub B2, B3, ..., B10, kuna aadress on suhteline.

Lahtrite vahemiku valemites märkimiseks kasutage sümbolit : ”, Näiteks: A2 : A5.

Märkimata lahtrite rühma tähistamiseks kasutage sümbolit ; ”, Näiteks: A2; B5; E10.

2.4. MUUDATUSVORM

Valemeid redigeeritakse samamoodi nagu lahtrite sisu.

Esiteksth viisil... Peate valima soovitud lahtri, klõpsama valemiribal ja seda muutma.

Teine tee... Topeltklõpsake lahtrit ja muutke valemit otse lahtris.

2.5. Ja funktsiooni kasutades

EXCELi üks kõige kasulikumaid omadusi on selle lai valik funktsioone, mis võimaldavad teil teha erinevat tüüpi arvutusi. Igal funktsioonil on süntaks kirjutamiseks:

FUNKTSIOONI NIMI (argument 1; argument 2; ...).

Funktsiooni argumendid võivad olla arvud, tekstid, loogilised väärtused, veaväärtused, lingid, massiivid. Kümnendnumbrite korral eraldatakse täisarv murdmärgist “,”, näiteks: –30.003.

Tekstiväärtused peavad olema ümbritsetud jutumärkidega. Kui tekst ise sisaldab topelt jutumärke, siis tuleks need kahekordistada.

Loogiline väärtus on TRUE ja FALSE. Boole'i \u200b\u200bargumendid võivad olla ka võrdlusväljendid, mille puhul saab TÕENE või VALE hinnata, näiteks: B10\u003e 20.

Näiteks arvutab funktsioon AVERAGE väärtuste rea aritmeetilise keskmise. Avaldis “\u003d KESKMINE (6; 12; 15; 16)” annab tulemuseks 12,75. Kui lahtritesse B10 - B15 salvestatakse väärtused 6, 12, 15, 16, siis saab valemi kirjutada järgmiselt: “\u003d KESKMINE (B10: B15)”.

Funktsiooni SUM kasutatakse väärtuste summa määramiseks, näiteks: “\u003d SUM (B10: B15)”. Numbrid 6, 12, 15, 16 võetakse kokku.

Funktsiooni on valemisse mugav sisestada kasutades Funktsioonivõlurid ... Funktsioonide viisard võimaldab teil oma loodud valemisse funktsiooni sisestada. Selleks tehke järgmist.

asetage kursor lahtrisse, kuhu soovite funktsiooni sisestada;

standardsel tööriistaribal klõpsake nuppu funktsiooniviisard ¦ x või käsku täita Sisesta + funktsioon ;

loendis kuvatavas dialoogiboksis Kategooriad valige soovitud funktsioonikategooria. Pärast seda nimekirjas Funktsioon ilmuvad valitud kategooria funktsioonid;

loendis Funktsioon valige funktsioon ja klõpsake nuppu Okei ;

ilmub dialoogiboks sõltuvalt valitud funktsiooni tüübist;

sisestage funktsiooni argumentide jaoks soovitud väärtused või lahtrivahemikud;

klõpsake dialoogiboksi nuppu Okei .

2.6. Automaatsed s u m i r o v ja e

Lihtsaim meetod tabeli kokkuvõtmiseks on automaatne liitmine. Selleks asetage kursor veeru alla või reast paremale jäävasse lahtrisse, mille väärtused tuleb kokku võtta, ja klõpsake standardse tööriistariba nupul Automaatne liitmine (see näitab sümbolit å ”). Seejärel vajutage nuppu Sisenema .

Maatriksielementide summeerimisel veergude ja ridade kaupa on mugav valida maatriksirakud täiendava rea \u200b\u200bja veeruga ning seejärel vajutada nuppu “ å ”. Maatriksi kõigi ridade ja veergude summa saadakse automaatselt.

2.7. F o rm uly tööks m a s i-ga m i-s

Massiivivalemid (tabelivalemid) võimaldavad teil teha palju arvutusi, kirjutades ühe valemi. Näiteks peate korrutama veeru A2: A6 väärtused vastava B2: B6 väärtusega. Salvestage tulemus C2: C6 valemit kopeerimata.

Peate tegema järgmist.

vali tulemuse C2: C6 lahtrid;

sisestage märk “\u003d”;

valige lahtrid A2: A6;

sisestage märk *;

esiletõst B2: B6;

vajutage klaviatuuriklahve Tõstuklahv + Ctrl + sisestusklahv .

Valemiribal kuvatakse valem “(\u003d A2: A6 * B2: B6)” ja tulemus saadakse kõigis lahtrites C2: C6.

Z A D A N I E 2

1) Käivitage programm EXCEL.

2) Looge oma kataloogis fail nimega “lab_2.хls”.

3) Nimetage töövihiku esimene leht „Lab. Nr 2 (valemite sisestamine) ”.

4) Lahtrisse A1 kirjutage oma perekonnanimi.

5) Koostage näidistabeli järgi aruandekaart. 2. Koguge igas veerus ja igas reas olevad üksused. Arvutage keskmine skoor valemite abil.

Tabel 2

	Matemaatika	Majandus	Informaatika	Keskmine tulemus
Ivanov	5
Petrov	4
Sidorov	3
Jakovlev	4
Keskmine tulemus	4

6) Loendage igas õppeaines "5" märkide arv. Printige välja õpilaste nimekiri, kelle GPA on suurem kui 4.

7) Arvutage y \u003d 2 x 2 + 3 x + 5, kus argument x muutub 0,1 sammuga 1-st 1-ni. Kasutage konstantide 2, 3, 5 absoluutviiteid ja x-i suhtelisi viiteid.

8) 4x4 maatriksi jaoks arvutage selle pöördmaatriks, ruutige ruut ja leidke tabelivalemite abil transponeeritud maatriks.

9) Salvestage töövihiku sisu.