5 Základy programu Excel (výučba): ako napísať vzorec, ako vypočítať súčet, podmienené sčítanie, počet riadkov atď.

Ahoj!

Mnohí, ktorí nepoužívajú program Excel, ani nevedia, aké príležitosti tento program poskytuje! ☝

Len sa zamyslite: automaticky pridajte hodnoty z jedného vzorca do ostatných, vyhľadajte požadované riadky v texte, vytvorte si vlastné podmienky atď. - všeobecne v skutočnosti mini-programovací jazyk na riešenie „úzkych“ problémov (úprimne povedané, sám som Excel dlho ako program dlho nepovažoval a takmer vôbec som ho nepoužíval).

V tomto článku chcem ukázať niekoľko príkladov, ako môžete rýchlo vyriešiť každodenné kancelárske úlohy: niečo pridať, niečo odčítať, vypočítať sumu (aj s podmienkou), nahradiť hodnoty z jednej tabuľky do druhej atď.

To znamená, že tento článok bude niečím ako miniprievodcom pre výučbu najpotrebnejších vecí pre prácu (presnejšie povedané, začať používať Excel a naplno využívať tento produkt!).

Je možné, že keby som si podobný článok prečítal pred 17-20 rokmi, sám by som začal používať Excel oveľa rýchlejšie (a ušetril by som si veľa času na riešenie „jednoduchých“ úloh ... ?

*

Výukový program pre základy programu Excel: Bunky a čísla

Poznámka: Všetky snímky obrazovky uvedené nižšie pochádzajú z aplikácie Excel 2016 (ako jedna z najnovších).

Mnoho začínajúcich používateľov si po spustení Excelu položí jednu zvláštnu otázku: „No, kde je tu tabuľka?“ ... Medzitým sú všetky bunky , ktoré uvidíte po spustení programu, jeden veľký stôl !

Teraz k hlavnej veci: akákoľvek bunka môže obsahovať text, nejaké číslo alebo vzorec. Napríklad nasledujúca snímka obrazovky zobrazuje jeden ilustratívny príklad:

  • vľavo: bunka (A1) obsahuje prvočíslo „6“. Upozorňujeme, že keď vyberiete túto bunku, vo vzorcovom riadku (Fx) sa zobrazí číslo „6“.
  • vpravo: v bunke (C1) to vyzerá ako jednoduché číslo „6“, ale ak vyberiete túto bunku, uvidíte vzorec „= 3 + 3“  - toto je dôležitá funkcia v programe Excel!

Iba číslo (vľavo) a vypočítaný vzorec (vpravo)

? Záverom je, že Excel môže počítať ako kalkulačka, ak vyberiete niektorú bunku a potom napíšete vzorec, napríklad „= 3 + 5 + 8“ (bez úvodzoviek). Výsledok nemusíte písať - Excel si ho sám vypočíta a zobrazí v bunke (ako v bunke C1 v príklade vyššie)!

Môžete však písať do vzorcov a pridávať nielen čísla, ale aj čísla, ktoré už boli spočítané v iných bunkách. Na snímke obrazovky nižšie v bunke A1 a B1 čísla 5 a 6. V bunke D1 chcem získať ich súčet - vzorec môžete napísať dvoma spôsobmi:

  • prvý: "= 5 + 6" (nie príliš výhodné, predstavte si, že v bunke A1 - naše číslo sa tiež počíta podľa iného vzorca a mení sa. Nebudete zakaždým nahradiť číslo namiesto 5?!) ;
  • druhá: "= A1 + B1" - ale to je ideálne, stačí pridať hodnotu buniek A1 a B1 (aj keď sú v nich čísla!).

Pridávajú sa bunky, ktoré už majú čísla

Rozšírenie vzorca na ďalšie bunky

V príklade vyššie sme pridali dve čísla do stĺpca A a B v prvom riadku. Máme ale 6 riadkov a najčastejšie pri skutočných problémoch musíte do každého riadku pridať čísla! Môžete to urobiť takto:

  1. do riadku 2 napíš vzorec "= A2 + B2", do riadku 3 - "= A3 + B3" atď. (je to zdĺhavé a zdĺhavé, táto možnosť sa nikdy nepoužíva);
  2. vyberte bunku D1 (ktorá už má vzorec), potom posuňte ukazovateľ myši do pravého rohu bunky tak, aby sa objavil čierny krížik (pozri snímku obrazovky nižšie). Potom podržte ľavé tlačidlo a roztiahnite vzorec na celý stĺpec. Pohodlné a rýchle! (Poznámka: pre vzorce môžete tiež použiť kombináciu Ctrl + C a Ctrl + V (kopírovanie a vkladanie)) .

Preťahovanie formuly

Mimochodom , venujte pozornosť tomu, že program Excel nahradil vzorce v každom riadku. To znamená, že ak teraz vyberiete bunku, povedzme D2, uvidíte vzorec "= A2 + B2" (tj. Excel automaticky nahradí vzorce a okamžite vráti výsledok) .

Ako nastaviť konštantu (bunka, ktorá sa pri kopírovaní vzorca nezmení)

Vo vzorcoch (keď ich kopírujete) sa často vyžaduje, aby sa určitá hodnota nezmenila. Povedzme jednoduchú úlohu: prepočítajte ceny v dolároch na ruble. Cena rubľa je stanovená v jednej bunke, v mojom príklade nižšie je to G2.

Ďalej do bunky E2 napíš vzorec "= D2 * G2" a získaj výsledok. Ak ale natiahneme vzorec, ako sme to urobili predtým, neuvidíme výsledok v ďalších riadkoch, pretože Excel umiestni vzorec „D3 * G3“ do riadku 3, do štvrtého riadku: „D4 * G4“ atď. Je nevyhnutné, aby G2 zostal všade G2 ...

Za týmto účelom - stačí zmeniť bunku E2 - vzorec bude vyzerať ako „= D2 * $ G $ 2“. Tých. znak dolára $ - umožňuje nastaviť bunku, ktorá sa pri kopírovaní vzorca nezmení (t. j. dostaneme konštantu, príklad nižšie).

Bunka konštantná / vo vzorci sa nezmení

*

Ako vypočítať sumu (vzorce SUM a SUMIFS)

Vzorce môžete samozrejme vytvárať manuálne napísaním „= A1 + B1 + C1“ a podobne. Ale Excel má rýchlejšie a jednoduchšie nástroje.

Jedným z najjednoduchších spôsobov pridania všetkých vybratých buniek je použitie možnosti Automatický súčet (Excel zapíše vzorec sám a vloží ho do bunky).

Čo musíte urobiť, aby ste vypočítali súčet určitých buniek:

  1. najskôr vyberte bunky (pozri obrazovku nižšie ?);
  2. potom otvorte sekciu „Vzorce“;
  3. ďalším krokom je stlačenie tlačidla „AutoSum“. Výsledok pridania sa zobrazí pod bunkami, ktoré ste vybrali;
  4. ak vyberiete bunku s výsledkom (v mojom prípade je to bunka E8 ), uvidíte vzorec "= SUM (E2: E7)".
  5. teda napísaním vzorca „= SUM (xx)“ , kde namiesto xx vložte (alebo vyberte) ľubovoľné bunky, môžete spočítať najrôznejšie rozsahy buniek, stĺpcov, riadkov ...

Autosum vybraných buniek

Ako vypočítať sumu s určitou podmienkou

Pri práci nemusíte často potrebovať iba súčet celého stĺpca, ale aj súčet určitých riadkov (t. J. Selektívne). Predpokladajme jednoduchý problém: musíte získať sumu zisku od nejakého pracovníka (samozrejme prehnane, ale príklad je viac ako skutočný).

V mojej tabuľke použijem iba 7 riadkov (kvôli prehľadnosti), zatiaľ čo skutočná tabuľka môže byť oveľa väčšia. Predpokladajme, že musíme vypočítať všetok zisk dosiahnutý programom "Sasha". Ako bude vyzerať vzorec:

  1. " = SUMIF (F2: F7; A2: A7;" Sasha ") " - (poznámka: dávajte pozor na úvodzovky stavu - mali by byť také, aké sú na snímke obrazovky nižšie, a nie také, aké som teraz napísal na svojom blogu) . Pamätajte tiež na to, že program Excel pri začiatku na začiatku vzorca (napríklad „SUM ...“) sám vyzve a nahradí možné možnosti - a v programe Excel sú stovky vzorcov!;
  2. F2: F7  je rozsah, cez ktorý sa sčítajú čísla z buniek (sčítané);
  3. A2: A7  je stĺpec, podľa ktorého sa bude kontrolovať náš stav;
  4. „Sasha“ je podmienka, pridajú sa tie riadky, v ktorých bude stĺpec A obsahovať „Sasha“ (všimnite si ilustračnú snímku obrazovky nižšie).

Súčet s podmienkou

Poznámka: Môže existovať niekoľko podmienok, ktoré je možné skontrolovať pre rôzne stĺpce.

*

Ako počítať počet riadkov (s jednou, dvoma alebo viacerými podmienkami)

Celkom typická úloha: nepočítať v bunkách súčet, ale počet riadkov, ktoré vyhovujú akejkoľvek podmienke.

Napríklad to, koľkokrát sa v dolnej tabuľke objaví názov „Sasha“ (pozri snímku obrazovky). Je zrejmé, že dvakrát (je to však preto, lebo tabuľka je príliš malá a slúži ako ilustratívny príklad). Ako to vypočítať ako vzorec?

Vzorec:

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

  • A2: A7  - rozsah, v ktorom budú riadky kontrolované a čítané;
  • A2  - podmienka je nastavená (všimnite si, že bolo možné napísať podmienku ako „Sasha“, alebo môžete jednoducho určiť bunku).

Výsledok sa zobrazuje na pravej strane snímky obrazovky nižšie.

Počet riadkov s jednou podmienkou

Teraz si predstavte pokročilejší problém: musíte počítať riadky, kde sa vyskytuje názov „Sasha“ a kde sa v stĺpci „B“ objaví číslo „6“. Do budúcnosti poviem, že existuje iba jeden takýto riadok (snímka obrazovky s príkladom uvedeným nižšie).

Vzorec bude vyzerať takto:

= COUNTIFS (A2: A7; A2; B2: B7; "6")   - (poznámka: všimnite si úvodzovky - mali by byť ako na snímke obrazovky nižšie, nie ako moje), kde:

A2: A7; A2  - prvý rozsah a podmienka vyhľadávania (podobné ako v príklade vyššie);

B2: B7; „6“  - druhý rozsah a podmienka vyhľadávania (podmienku je možné nastaviť rôznymi spôsobmi: buď zadajte bunku, alebo napíšte text / číslo do úvodzoviek).

Počíta sa riadok s dvoma alebo viacerými podmienkami

*

Ako vypočítať percento zo sumy

To je tiež dosť častá otázka, s ktorou sa často stretávam. Všeobecne, pokiaľ si viem predstaviť, vyskytuje sa najčastejšie - kvôli tomu, že ľudia sú zmätení a nevedia, z čoho hľadajú percento (a všeobecne téme záujmu príliš nerozumejú) (aj keď sám nie som veľký matematik a stále ... ☝)).

? Pomoc!

Ako vypočítať percentá : z čísla, zo súčtu čísel atď. [V mysli, na kalkulačke a pomocou programu Excel] - poznámka pre začiatočníkov

Najjednoduchší spôsob, ako sa zameniť jednoducho nedá, je použitie štvorcového pravidla alebo proporcií.

Celý bod je zobrazený na snímke obrazovky nižšie: ak máte celkovú sumu, povedzme v mojom príklade toto číslo je 3060 - bunka F8 (to znamená, že ide o 100% zisk a časť z toho vytvoril „Sasha“, musíte nájsť ktorý ...).

Pomerne bude vzorec vyzerať takto:  = F10 * G8 / F8 (t. J. Krížikmi: najskôr vynásobíme dve známe čísla pozdĺž uhlopriečky a potom vydelíme zvyšným tretím číslom).

V zásade je pri použití tohto pravidla takmer nemožné zamieňať sa s percentami ?.

Ukážka riešenia problémov so záujmom

*

PS 

Týmto vlastne uzatváram tento článok. Nebojím sa povedať, že keď si osvojíte všetko, čo je vyššie napísané (a tu sú uvedené iba „päty“ vzorcov), budete sa potom môcť samostatne učiť Excel, listovať v pomoci, sledovať, experimentovať a analyzovať. ?

Poviem ešte viac, všetko, čo som popísal vyššie, pokryje veľa úloh a umožní vám vyriešiť všetky najbežnejšie, nad ktorými si často lámete hlavu (ak nepoznáte možnosti Excelu) a ani neviete ako rýchlo sa to dá ... ✔

*

Dodatky k tejto téme sú vítané!

Veľa štastia!

?

Prvé zverejnenie: 21. 1. 2017

Oprava: 1. 5. 2020