5 Exceli põhitõed (õppimine): kuidas kirjutada valem, kuidas arvutada summa, tingimuslik liitmine, ridade arv jne.

Tere!

Paljud, kes ei kasuta Exceli, ei tea isegi, milliseid võimalusi see programm pakub! ☝

Mõelge vaid: lisage ühe valemi väärtused automaatselt teistele, otsige tekstist nõutavad read, looge oma tingimused jne. - üldiselt tegelikult miniprogrammeerimiskeel "kitsaste" probleemide lahendamiseks (ausalt öeldes ei ole ma ise pikka aega Exceli programmina pidanud ega kasutanud seda peaaegu kunagi).

Selles artiklis tahan näidata mitmeid näiteid selle kohta, kuidas saate igapäevaseid kontoritöid kiiresti lahendada: lisada midagi, lahutada midagi, arvutada summa (sh tingimusega), asendada väärtused ühest tabelist teise jne.

See tähendab, et see artikkel on midagi mini-guide for õpetamise kõige vajalikum asju töö (täpsemalt hakata kasutama Excel ja täit võimsust selle kauba kohta!).

Võimalik, et kui ma oleksin sarnast artiklit lugenud 17–20 aastat tagasi, oleksin ka ise hakanud Exceli kasutama palju kiiremini (ja oleksin palju aega säästnud "lihtsate" ülesannete lahendamiseks ... ?

*

Exceli põhitõde: lahtrid ja numbrid

Märkus: kõik allpool olevad ekraanipildid pärinevad Excel 2016-st (kui ühed uuemad).

Paljud algajad kasutajad küsivad pärast Exceli käivitamist ühe kummalise küsimuse: "Noh, kus on siin tabel?" ... Vahepeal on kõik lahtrid , mida pärast programmi käivitamist näete, üks suur tabel !

Nüüd peamise asja juurde: mis tahes lahter võib sisaldada teksti, mõnda numbrit või valemit. Näiteks alloleval ekraanipildil on üks illustreeriv näide:

  • vasakul: lahter (A1) sisaldab algarvu "6". Pange tähele, et selle lahtri valimisel kuvatakse valemireal number (6).
  • paremal: lahtris (C1) näeb see välja lihtsa arvuna "6", kuid kui valite selle lahtri, näete valemit "= 3 + 3"  - see on Exceli oluline funktsioon!

Lihtsalt arv (vasakul) ja arvutatud valem (paremal)

? Alumine rida on see, et Excel saab loendada nagu kalkulaator, kui valite mõne lahtri ja kirjutate seejärel valemi, näiteks "= 3 + 5 + 8" (ilma jutumärkideta). Tulemust pole vaja kirjutada - Excel arvutab selle ise ja kuvab selle lahtris (nagu ülaltoodud näite lahtris C1)!

Kuid võite kirjutada valemitesse ja lisada mitte ainult numbreid, vaid ka numbreid, mis on juba teistes lahtrites loendatud. Alloleval ekraanipildil lahtrites A1 ja B1 vastavalt numbrid 5 ja 6. Lahtris D1 soovin saada nende summa - valemi saate kirjutada kahel viisil:

  • esimene: "= 5 + 6" (pole eriti mugav, kujutage ette, et lahtris A1 - ka meie arv arvutatakse mõne muu valemi järgi ja see muutub. Te ei asenda iga kord numbrit 5 asemel?!) ;
  • teine: "= A1 + B1" - aga see on ideaalne, lisage lihtsalt lahtrite A1 ja B1 väärtus (isegi kui neis on numbreid!).

Lahtrite lisamine, millel on juba numbrid

Valemi laiendamine teistele lahtritele

Ülaltoodud näites lisasime esimeses reas veergudes A ja B kaks numbrit. Kuid meil on 6 rida ja kõige sagedamini tõelistes probleemides peate igale reale lisama numbreid! Selleks saate teha järgmist.

  1. 2. reale kirjuta valem "= A2 + B2", 3. reale - "= A3 + B3" jne. (see on pikk ja tüütu, seda võimalust ei kasutata kunagi);
  2. valige lahter D1 (millel on juba valem), seejärel liigutage hiirekursorit lahtri paremasse nurka, nii et ilmub must rist (vt allpool olevat pilti). Seejärel hoidke vasakut nuppu all ja venitage valem kogu veeruni. Mugav ja kiire! (Märkus: valemite jaoks saate kasutada ka kombinatsioone Ctrl + C ja Ctrl + V (vastavalt kopeerida ja kleepida)) .

Valemi venitamine

Muide , pöörake tähelepanu asjaolule, et Excel on asendanud valemid igas reas. See tähendab, et kui valite nüüd lahtri, näiteks D2, näete valemit "= A2 + B2" (st Excel asendab valemid automaatselt ja tagastab kohe tulemuse) .

Konstandi määramine (lahter, mis valemi kopeerimisel ei muutu)

Üsna sageli nõutakse valemites (kui kopeerite), et mõni väärtus ei muutuks. Ütleme, et lihtne ülesanne: teisendada hinnad dollarites rubladeks. Rubla maksumus määratakse ühes lahtris, minu allpool toodud näites on see G2.

Järgmisena kirjutage lahtrisse E2 valem "= D2 * G2" ja saate tulemuse. Kuid kui me valemit venitame, nagu varemgi, siis me ei näe tulemust teistel ridadel, sest Excel paneb valemi "D3 * G3" 3. reale 4. reale: "D4 * G4" jne. On vaja, et G2 jääks kõikjale G2 ...

Selleks - lihtsalt muutke lahtrit E2 - näeb valem välja järgmine: "= D2 * $ G $ 2". Need. dollari märk $ - võimaldab teil määrata lahtri, mis valemi kopeerimisel ei muutu (st saame konstandi, näide allpool).

Konstant / valemis lahter ei muutu

*

Summa arvutamine (valemid SUM ja SUMIFS)

Loomulikult saate valemeid käsitsi luua, tippides "= A1 + B1 + C1" jms. Kuid Excelis on kiiremad ja lihtsamad tööriistad.

Üks lihtsamaid viise kõigi valitud lahtrite lisamiseks on kasutada suvandit AutoSum (Excel kirjutab valemi ise ja kleepib selle lahtrisse).

Mida peate tegema teatud lahtrite summa arvutamiseks:

  1. kõigepealt valige lahtrid (vt allolevat ekraani ?);
  2. seejärel avage jaotis "Valemid";
  3. järgmine samm on vajutada nuppu "AutoSum". Lisamise tulemus ilmub teie valitud lahtrite alla;
  4. kui valite tulemusega lahtri (minu puhul on see lahter E8 ), siis näete valemit "= SUM (E2: E7)".
  5. seega kirjutades valemi "= SUM (xx)" , kus xx-i asemel panete (või valige) mis tahes lahtrid, saate loendada kõige erinevamaid lahtrivahemikke, veerge, ridu ...

Valitud lahtrite autosum

Kuidas arvutada summa mõne tingimusega

Sageli pole töötamisel vaja mitte ainult kogu veeru summat, vaid teatud ridade summat (st valikuliselt). Oletame, et lihtne probleem: peate saama mõnelt töötajalt kasumi summa (muidugi liialdatud, kuid näide on rohkem kui reaalne).

Ma kasutan oma tabelis ainult 7 rida (selguse huvides), samas kui tegelik tabel võib olla palju suurem. Oletame, et peame arvutama kogu "Sasha" teenitud kasumi. Kuidas valem välja näeb:

  1. " = SUMIF (F2: F7; A2: A7;" Sasha ") " - (märkus: pöörake tähelepanu tingimuse hinnapakkumistele - need peaksid olema nagu alloleval ekraanipildil, mitte nii, nagu ma nüüd oma blogisse kirjutasin) . Pange tähele ka seda, et Excel valemi alguses sõites (näiteks "SUM ...") küsib ja asendab ise võimalikke valikuid - ja Excelis on sadu valemeid!
  2. F2: F7  on vahemik, kuhu lahtrite arvud liidetakse (summeeritakse);
  3. A2: A7  on veerg, mille abil meie seisundit kontrollitakse;
  4. "Sasha" on tingimus, lisatakse need read, mille veerg A sisaldab "Sasha" (pange tähele allpool toodud illustreerivat pilti).

Summa tingimusega

Märkus: tingimusi võib olla mitu ja neid saab kontrollida erinevate veergude jaoks.

*

Ridade arvu lugemine (ühe, kahe või enama tingimusega)

Üsna tüüpiline ülesanne: loendada mitte lahtrites olevat summat, vaid ridade arvu, mis vastavad mis tahes tingimusele.

Noh, näiteks mitu korda ilmub nimi "Sasha" allolevas tabelis (vt ekraanipilti). Ilmselgelt 2 korda (kuid seda seetõttu, et tabel on liiga väike ja võetud illustreerivaks näiteks). Kuidas seda valemina arvutada?

Valem:

" = COUNTIF (A2: A7, A2) " - kus:

  • A2: A7  - vahemik, milles ridu kontrollitakse ja loetakse;
  • A2  - seatud on tingimus (pange tähele, et oli võimalik kirjutada tingimus nagu "Sasha" või võite lihtsalt lahtrit määrata).

Tulemust näidatakse allpool asuva ekraanipildi paremas servas.

Ühe tingimusega ridade arv

Kujutage nüüd ette keerulisemat probleemi: peate loendama read, kus esineb nimi "Sasha" ja kus veerus "B" ilmub number "6". Tulevikku vaadates ütlen, et selliseid ridu on ainult üks (ekraanipilt allpool toodud näitega).

Valem näeb välja selline:

= COUNTIFS (A2: A7; A2; B2: B7; "6")   - (märkus: pange tähele jutumärke - need peaksid olema nagu alloleval ekraanipildil, mitte nagu minu omad), kus:

A2: A7; A2  - esimene vahemik ja otsingutingimus (sarnane ülaltoodud näitega);

B2: B7; "6"  - teine ​​vahemik ja otsingu tingimus (pange tähele, et tingimust saab seada erineval viisil: kas määrake lahter või kirjutatakse lihtsalt tekst / number jutumärkidesse).

Kahe või enama tingimusega loendusjooned

*

Kuidas arvutada summa protsent

See on ka üsna tavaline küsimus, millega tihti kokku puutun. Üldiselt, nii palju kui ma ette kujutan, toimub see kõige sagedamini - tulenevalt asjaolust, et inimesed on segaduses ega tea, mida nad protsenti otsivad (ja üldiselt ei saa nad huvitavat teemat väga hästi aru (kuigi ma ise pole suur matemaatik ja ikkagi ... ☝)).

? Aita!

Protsentide arvutamine : arvult, numbrite summalt jne

Lihtsaim viis, kus segadusse ajamine on lihtsalt võimatu, on kasutada "ruudu" reeglit ehk proportsioone.

Kogu punkt on näidatud alloleval ekraanipildil: kui teil on kogusumma, siis ütleme, et minu näites on see arv 3060 - lahter F8 (see tähendab, et see on 100% kasum ja osa sellest tegi "Sasha", peate leidma, milline neist ...).

Proportsionaalselt näeb valem välja selline:  = F10 * G8 / F8 (s.t rist ristiga: kõigepealt korrutame kaks teadaolevat arvu piki diagonaali ja jagame seejärel ülejäänud kolmanda numbriga).

Põhimõtteliselt on seda reeglit kasutades protsentide osas pea võimatu segadusse sattuda ?.

Näide probleemide lahendamisest huviga

*

PS 

Tegelikult lõpetan selle artikli siin. Ma ei karda öelda, et olles õppinud kõike, mis on ülalpool kirjutatud (ja siin on ainult valemite "kontsad"), saate seejärel iseseisvalt Exceli õppida, abi sirvida, vaadata, katsetada ja analüüsida. ?

Ma ütlen veelgi rohkem, kõik, mida ma eespool kirjeldasin, hõlmab paljusid ülesandeid ja võimaldab teil lahendada kõik kõige tavalisemad, mille üle te sageli mõistatate (kui te ei tea Exceli võimalusi) ega tea isegi kiiresti saab hakkama ... ✔

*

Teemalised täiendused on teretulnud!

Edu!

?

Esimene väljaanne: 21.01.2017

Parandus: 01.05.2020