Jak vytvářet vzorce v excelu. Jak psát vzorce v Excelu. Vytvářejte složité vzorce pomocí pracovního postupu
Dobré odpoledne.
Kdysi bylo pro mě vlastní psaní vzorce v Excelu něco neuvěřitelného. A i když jsem v tomto programu musel často pracovat, nepsal jsem nic kromě textu...
Jak se ukázalo, většina vzorců není složitá a může s nimi snadno pracovat i začínající uživatel počítače. V tomto článku bych rád prozradil ty nejnutnější vzorce, se kterými musíte nejčastěji pracovat...
Takže, začněme…
1. Základní operace a základy. Školení základů Excelu.
Všechny akce v článku se zobrazí v aplikaci Excel verze 2007.
Po spuštění programu Excel se objeví okno s mnoha buňkami - naše tabulka. hlavní rys Program spočívá v tom, že dokáže vypočítat (jako kalkulačka) vaše vzorce, které napíšete. Mimochodem, do každé buňky můžete přidat vzorec!
Vzorec musí začínat znakem „=“. To je nutnost. Dále napíšete, co potřebujete vypočítat: například „=2+3“ (bez uvozovek) a stiskněte klávesu Enter - v důsledku toho uvidíte, že se v buňce objeví výsledek „5“. Viz snímek obrazovky níže.
Důležité! Přestože je v buňce A1 zapsáno číslo „5“, vypočítá se pomocí vzorce („=2+3“). Pokud jednoduše napíšete „5“ do další buňky, pak když na tuto buňku najedete kurzorem, v editoru vzorců (řádek nahoře, Fx) - uvidíte prvočíslo „5“.
Nyní si představte, že do buňky můžete napsat nejen hodnotu 2+3, ale i čísla buněk, jejichž hodnoty je třeba sečíst. Řekněme „=B2+C2“.
Přirozeně musí být v B2 a C2 nějaká čísla, jinak nám Excel v buňce A1 ukáže výsledek rovný 0.
A ještě jedna důležitá poznámka...
Když zkopírujete buňku, která obsahuje vzorec, například A1, a vložíte ji do jiné buňky, nezkopíruje se hodnota „5“, ale samotný vzorec!
Navíc se vzorec bude měnit přímo úměrně: tzn. pokud je A1 zkopírován do A2, pak vzorec v buňce A2 bude „=B3+C3“. Excel automaticky změní váš vzorec: pokud A1=B2+C2, pak je logické, že A2=B3+C3 (všechna čísla se zvětší o 1).
Výsledek je mimochodem v A2=0, protože Buňky B3 a C3 nejsou specifikovány, což znamená, že se rovnají 0.
Tímto způsobem můžete vzorec napsat jednou a poté jej zkopírovat do všech buněk požadovaného sloupce - a Excel sám provede výpočet v každém řádku vaší tabulky!
Pokud nechcete, aby se B2 a C2 měnily při kopírování a byly vždy spojeny s těmito buňkami, pak k nim jednoduše přidejte znak „$“. Příklad níže.
Tímto způsobem, bez ohledu na to, kam zkopírujete buňku A1, bude vždy odkazovat na propojené buňky.
2. Přidávání hodnot do řádků (vzorec SUM a SUMIFS)
Můžete samozřejmě přidat každou buňku a vytvořit vzorec A1+A2+A3 atd. Ale abyste se tomuto problému vyhnuli, v Excelu existuje speciální vzorec, který sečte všechny hodnoty ve vybraných buňkách!
Vezměme si jednoduchý příklad. Skladem je několik položek zboží a my víme kolik od každého produktu zvlášť v kg. Na skladě. Zkusme si spočítat, kolik je v kg. náklad ve skladu.
Chcete-li to provést, přejděte do buňky, ve které se zobrazí výsledek, a napište vzorec: „=SUM(C2:C5)“. Viz snímek obrazovky níže.
V důsledku toho se sečtou všechny buňky ve vybraném rozsahu a uvidíte výsledek.
2.1. Přidání s podmínkou (s podmínkami)
Nyní si představme, že máme určité podmínky, tzn. Nemusíte sčítat všechny hodnoty v buňkách (kg, skladem), ale pouze některé, řekněme, s cenou (1 kg) nižší než 100.
Existuje na to úžasný vzorec" SUMIFS". Okamžitě příklad a poté vysvětlení každého symbolu ve vzorci.
=SUMIFS(C2:C5,B2:B5,“<100» ) , kde:
C2:C5 - sloupec (tyto buňky), který se bude sčítat;
B2:B5- sloupec, podle kterého bude podmínka kontrolována (tj. cena, například méně než 100);
«<100»
- podmínka samotná, podmínka je psána v uvozovkách.
V tomto vzorci není nic složitého, hlavní věcí je zachovat proporcionalitu: C2:C5;B2:B5 - správně; C2:C6;B2:B5 - nesprávně. Tito. Rozsah součtu a rozsah podmínek musí být proporcionální, jinak vzorec vrátí chybu.
Důležité! Pro částku může být mnoho podmínek, tzn. Můžete zkontrolovat ne podle 1. sloupce, ale podle 10 najednou, nastavením mnoha podmínek.
3. Počítání počtu řádků, které splňují podmínky (vzorec COUNTIFS)
Poměrně běžný úkol: nepočítat součet hodnot v buňkách, ale počet takových buněk, které splňují určité podmínky. Někdy je podmínek hodně.
Takže, začněme.
Pro započítání zboží do požadované buňky napište následující vzorec (viz výše):
=COUNTIFS(B2:B5,“>90”), kde:
B2:B5- rozsah, ve kterém budou kontrolovat, podle námi nastavené podmínky;
">90"- samotná podmínka je uzavřena v uvozovkách.
Nyní zkusme náš příklad trochu zkomplikovat a přidat fakturu ještě za jednu podmínku: s cenou větší než 90 + skladové množství menší než 20 kg.
Vzorec má tvar:
=COUNTIFS(B2:B6,“>90″,C2:C6 ;“<20» )
Zde zůstává vše stejné, kromě další podmínky ( C2:C6;"<20″ ). Mimochodem, takových podmínek může být spousta!
Je jasné, že pro tak malou tabulku takové vzorce nikdo nenapíše, ale pro tabulku o několika stovkách řádků je to úplně jiná věc. Jako příklad je tato tabulka více než přehledná.
4. Vyhledávání a nahrazování hodnot z jedné tabulky do druhé (vzorec VLOOKUP)
Představme si, že jsme dostali novou tabulku s novými cenovkami ke zboží. Je dobré, když je 10-20 položek, můžete je všechny „doplnit“ ručně. Co když takových jmen jsou stovky? Bylo by mnohem rychlejší, kdyby Excel nezávisle našel odpovídající názvy z jedné tabulky do druhé a pak zkopíroval nové cenovky do naší staré tabulky.
Pro takový problém se používá vzorec SVYHLEDAT. Svého času jsem si tropil triky s logickými formulkami „KDYBYCH“, dokud jsem nenarazil na tuto úžasnou věc!
Takže, začněme…
Zde je náš příklad + nová tabulka s cenovkami. Nyní musíme automaticky nahradit nové cenovky z nové tabulky do staré (nové cenovky jsou červené).
Umístěte kurzor do buňky B2 - tzn. do první buňky, kde potřebujeme automaticky změnit cenovku. Dále napíšeme vzorec jako na snímku obrazovky níže (po snímku obrazovky bude jeho podrobné vysvětlení).
=VLOOKUP(A2 ;$D$2:$E$5 ;2 ), Kde
A2- hodnota, kterou budeme hledat, abychom získali novou cenovku. V našem případě hledáme v nové tabulce slovo „jablka“.
$D$2:$E$5- vyberte naši novou tabulku kompletně (D2:E5, výběr jde z levého horního rohu do pravého dolního diagonálně), tzn. kde bude pátrání probíhat. Znak „$“ v tomto vzorci je nezbytný, aby se při kopírování tohoto vzorce do jiných buněk - D2:E5 nezměnil!
Důležité! Slovo „jablka“ se bude hledat pouze v prvním sloupci vámi vybrané tabulky, v tomto příkladu se „jablka“ vyhledá ve sloupci D.
2 - Když je nalezeno slovo „jablka“, funkce musí vědět, ze kterého sloupce vybrané tabulky (D2:E5) má zkopírovat požadovanou hodnotu. V našem příkladu zkopírujte ze sloupce 2 (E), protože v prvním sloupci (D) jsme provedli vyhledávání. Pokud se vámi vybraná tabulka pro vyhledávání skládá z 10 sloupců, prohledá se první sloupec a ze sloupců 2 až 10 můžete vybrat číslo, které chcete zkopírovat.
Na vzorec =VLOOKUP(A2;$D$2:$E$5;2) nahrazeny novými hodnotami pro jiné názvy produktů - stačí je zkopírovat do jiných buněk sloupce s cenovkami produktů (v našem příkladu zkopírovat do buněk B3:B5). Vzorec automaticky vyhledá a zkopíruje hodnotu ze sloupce, který potřebujete v nové tabulce.
5. Závěr
V tomto článku jsme se podívali na základy práce s Excelem, počínaje tím, jak začít psát vzorce. Uvedli příklady nejběžnějších vzorců, se kterými musí často pracovat většina lidí pracujících v Excelu.
Doufám, že rozebrané příklady budou někomu užitečné a pomohou urychlit práci. Hodně štěstí při experimentování!
Jaké používáte vzorce Je možné nějak zjednodušit vzorce uvedené v článku? Například na slabých počítačích při změně některých hodnot ve velkých tabulkách, kde se výpočty provádějí automaticky, počítač na několik sekund zamrzne, přepočítá a zobrazí nové výsledky...
Excel je nezbytný, když potřebujete uspořádat, zpracovat a uložit velké množství informací. Pomůže to zautomatizovat výpočty, takže budou jednodušší a spolehlivější. Vzorce v Excelu umožňují provádět libovolně složité výpočty a okamžitě získávat výsledky.
Jak napsat vzorec v Excelu
Než se to naučíte, je třeba pochopit několik základních principů.
- Každý začíná znakem „=“.
- Hodnoty a funkce buněk se mohou účastnit výpočtů.
- Operátory se používají jako známé matematické symboly pro operace.
- Když vložíte záznam, buňka standardně zobrazí výsledek výpočtu.
- Design si můžete prohlédnout v řádku nad tabulkou.
Každá buňka v Excelu je nedělitelná jednotka s vlastním identifikátorem (adresou), který je označen písmenem (číslo sloupce) a číslem (číslo řádku). Adresa se zobrazí v poli nad tabulkou.
Jak tedy vytvoříte a vložíte vzorec v Excelu? Postupujte podle následujícího algoritmu:
Označení Význam
Přidání
- Odečítání
/ Divize
* Násobení
Pokud potřebujete zadat číslo místo adresy buňky, zadejte ji z klávesnice. Chcete-li zadat záporné znaménko ve vzorci aplikace Excel, klikněte na "-". 
Jak zadávat a kopírovat vzorce v Excelu
Zadávají se vždy po kliknutí na „=“. Co ale dělat, když podobných výpočtů existuje mnoho? V tomto případě můžete jeden určit a pak jej jednoduše zkopírovat. Chcete-li to provést, zadejte vzorec a poté jej „natáhněte“ v požadovaném směru, abyste jej vynásobili.
Umístěte ukazatel nad buňku, kterou chcete zkopírovat, a přesuňte ukazatel myši do pravého dolního rohu (nad čtverec). Měl by mít podobu jednoduchého kříže se stejnými stranami. 
Stiskněte levé tlačítko a přetáhněte. 
Uvolněte, když potřebujete zastavit kopírování. V tuto chvíli se zobrazí výsledky výpočtu. 
Můžete ho také natáhnout doprava.
Přesuňte ukazatel na sousední buňku. Uvidíte stejný záznam, ale s různými adresami. 
Při kopírování tímto způsobem se čísla řádků zvýší, pokud dojde k posunu dolů, nebo se zvýší čísla sloupců, pokud se posune doprava. Tomu se říká relativní adresování.
Do tabulky zadáme hodnotu DPH a vypočítáme cenu včetně daně. 
Cena včetně DPH je kalkulována jako cena*(1+DPH). Zadáme sekvenci do první buňky. 
Zkusme záznam zkopírovat. 
Výsledek byl zvláštní. 
Zkontrolujeme obsah ve druhé buňce. 
Jak je vidět, při kopírování se posunula nejen cena, ale i DPH. A potřebujeme, aby tato buňka zůstala pevná. Pojďme to ukotvit absolutním odkazem. Chcete-li to provést, přesuňte ukazatel na první buňku a klikněte na adresu B2 v řádku vzorců. 
Stiskněte F4. K adrese bude připojen znak „$“. Toto je znak absolutní buňky. 
Nyní po zkopírování zůstane adresa B2 nezměněna.
Pokud jste omylem zadali data do nesprávné buňky, jednoduše je přesuňte. Chcete-li to provést, přesuňte ukazatel myši na libovolný okraj, počkejte, až bude myš vypadat jako kříž se šipkami, stiskněte levé tlačítko a táhněte. Na požadovaném místě jednoduše uvolněte manipulátor.
Použití funkcí pro výpočty
Excel nabízí velké množství funkcí, které jsou rozděleny do kategorií. Úplný seznam můžete zobrazit kliknutím na tlačítko Fx vedle řádku vzorců nebo otevřením sekce „Vzorce“ na panelu nástrojů. 
Pojďme se bavit o některých funkcích.
Jak nastavit vzorce "Pokud" v Excelu
Tato funkce umožňuje nastavit podmínku a provést výpočet v závislosti na její pravdivosti či nepravdivosti. Pokud je například množství prodaného zboží více než 4 balení, měli byste zakoupit více.
Pro vložení výsledku v závislosti na podmínce přidáme do tabulky další sloupec. 
V první buňce pod záhlavím tohoto sloupce nastavte ukazatel a klikněte na položku „Boolean“ na panelu nástrojů. Zvolme funkci "Pokud". 
Stejně jako při vkládání jakékoli funkce se vám otevře okno pro vyplnění argumentů. 
Naznačíme stav. Chcete-li to provést, klikněte na první řádek a vyberte první buňku „Prodáno“. Dále vložte znak „>“ a uveďte číslo 4. 
Do druhého řádku napíšeme „Nákup“. Tato zpráva se zobrazí u položek, které byly vyprodané. Poslední řádek může zůstat prázdný, protože pokud je podmínka nepravdivá, neprovedeme žádnou akci. 
Klikněte na OK a zkopírujte záznam pro celý sloupec. 
Aby se v buňce nezobrazovalo „FALSE“, spusťte funkci znovu a opravte ji. Umístěte ukazatel na první buňku a stiskněte Fx vedle řádku vzorců. Vložte kurzor na třetí řádek a mezi uvozovky vložte mezeru. 
Poté OK a znovu zkopírujte. 
Nyní vidíme, který produkt by měl být zakoupen.
Text vzorce v Excelu
Tato funkce umožňuje použít formát na obsah buňky. V tomto případě je jakýkoli typ dat převeden na text, což znamená, že je nelze použít pro další výpočty. Přidejme sloupec pro formátování součtu. 
Do první buňky zadejte funkci (tlačítko „Text“ v části „Vzorce“). 
V okně argumentů označíme odkaz na buňku celkového množství a nastavíme formát na „#rub“. 
Klikněte na OK a zkopírujte. 
Pokud se pokusíme tuto částku použít ve výpočtech, zobrazí se nám chybová zpráva. 
"VALUE" znamená, že výpočet nelze provést.
Příklady formátů můžete vidět na snímku obrazovky. 
Vzorec data v Excelu
Excel poskytuje mnoho možností pro práci s daty. Jeden z nich, DATE, umožňuje sestavit datum ze tří čísel. To se hodí, pokud máte tři různé sloupce – den, měsíc, rok. 
Umístěte ukazatel na první buňku čtvrtého sloupce a vyberte funkci ze seznamu Datum a čas. 
Podle toho uspořádejte adresy buněk a klepněte na OK. 
Zkopírujte záznam. 
AutoSum v Excelu
V případě, že potřebujete sečíst velké množství dat, Excel nabízí funkci SUM. Spočítejme si například částku za prodané zboží.
Umístěte ukazatel do buňky F12. Vypočítá celkovou částku. 
Přejděte na panel Vzorce a klikněte na AutoSum. 
Excel automaticky zvýrazní nejbližší číselný rozsah. 
Můžete vybrat jiný rozsah. V tomto příkladu provedl Excel vše správně. Klepněte na tlačítko OK. Věnujte pozornost obsahu buňky. Funkce SUM byla nahrazena automaticky. 
Při vkládání rozsahu se zadává adresa první buňky, dvojtečka a adresa poslední buňky. ":" znamená "Vezměte všechny buňky mezi první a poslední. Pokud potřebujete uvést více buněk, oddělte jejich adresy středníkem:
SOUČET (F5;F8;F11)
Práce v Excelu se vzorci: příklad
Řekli jsme vám, jak vytvořit vzorec v Excelu. To jsou znalosti, které se mohou hodit i v běžném životě. Můžete spravovat svůj osobní rozpočet a kontrolovat výdaje. 
Snímek obrazovky ukazuje vzorce, které se zadávají pro výpočet částek příjmů a výdajů, a také výpočet zůstatku na konci měsíce. Přidejte listy do sešitu pro každý měsíc, pokud nechcete všechny listy na jednom. Chcete-li to provést, jednoduše klikněte na „+“ ve spodní části okna. 
Chcete-li list přejmenovat, poklepejte na něj a zadejte název. 
Stůl lze vyrobit ještě detailněji.
Excel je velmi užitečný program a jeho výpočty poskytují téměř neomezené možnosti.
Měj krásný zbytek dne!
Microsoft Excel Office má mnoho praktických funkcí, které vám pomohou vyhnout se zdlouhavým výpočtům před zadáním dat do tabulek nebo grafů. Pro výpočet některých hodnot, jako je sčítání, odčítání, poměr a další, můžete použít původně zadané vzorce. Můžete je přidat sami nebo vybrat ze seznamu existujících. V tomto článku se dozvíte, jak vložit vzorec krok za krokem.
Otevřete Excel. Před vámi bude pracovní oblast, ujistěte se, že je otevřená karta „Domů“. V prázdném řádku umístěném mezi hlavičkou programu a jeho pracovní oblastí uvidíte pole pro zadání vzorce, který budete potřebovat po zadání argumentů, tedy čísel, která je třeba vypočítat.
Nyní přesně víte, jak vytvořit vzorce v Excelu a uvidíte jejich dekódování v Průvodci funkcí.
Vzorec je výraz, který vypočítá hodnotu buňky. Funkce jsou předdefinované vzorce a jsou již zabudovány do Excelu.
Například na obrázku pod buňkou A3 obsahuje vzorec, který přidává hodnoty buněk A2 A A1.
Ještě jeden příklad. Buňka A3 obsahuje funkci SOUČET(SUM), který vypočítá součet rozsahu A1:A2.
SOUČET(A1:A2)
=SUM(A1:A2)
Zadání vzorce
Chcete-li zadat vzorec, postupujte podle následujících pokynů:
Rada: Místo ručního psaní A1 A A2, stačí kliknout na buňky A1 A A2.
Úprava vzorců
Když vyberete buňku, Excel zobrazí hodnotu nebo vzorec v buňce na řádku vzorců.
- Chcete-li upravit vzorec, klikněte na řádek vzorců a změňte vzorec.
Operační priorita
Excel používá vestavěné pořadí, ve kterém se provádějí výpočty. Je-li část vzorce v závorkách, bude vypočítána jako první. Poté se provede násobení nebo dělení. Excel pak bude sčítat a odečítat. Viz příklad níže:
První Excel násobí ( A1*A2), pak přidá hodnotu buňky A3 k tomuto výsledku.
Další příklad:
Excel nejprve vypočítá hodnotu v závorkách ( A2+A3), pak vynásobí výsledek velikostí buňky A1.
Kopírovat/vložit vzorec
Když zkopírujete vzorec, Excel automaticky upraví odkazy pro každou novou buňku, do které se vzorec zkopíruje. Abyste tomu porozuměli, postupujte takto:
Vložení funkce
Všechny funkce mají stejnou strukturu. Například:
SOUČET(A1:A4)
SOUČET(A1:A4)
Název této funkce je SOUČET(SOUČET). Výraz mezi závorkami (argumenty) znamená, že jsme zadali rozsah A1:A4 jako vstup. Tato funkce přidává hodnoty do buněk A1, A2, A3 A A4. Zapamatovat si, které funkce a argumenty použít pro každý konkrétní úkol, není snadné. Naštěstí má Excel příkaz Vložit funkci(funkce vložení).
Chcete-li vložit funkci, postupujte takto:
Poznámka: Místo použití " Funkce vložení", stačí napsat =COUNTIF(A1:C2,">5″). Když zadáte „=COUNTIF(“, místo psaní „A1:C2“, ručně vyberte tento rozsah myší.
