Jak pracovat se vzorci v aplikaci Microsoft Excel: Průvodce pro začátečníky

V této příručce se budeme zabývat několika metodami, které vám pomohou efektivně vypočítat procenta v Excelu, a také si osvojíte základní vzorce procent, které z vašich výpočtů odstraní dohady. Naučíte se rychlý způsob výpočtu procent v Excelu, najdete základní vzorec procent a několik dalších vzorců pro výpočet procentuální změny, procenta z celkového počtu a další.

Existuje mnoho situací, kdy potřebujete vypočítat úrok, ať už jde o provizi prodejce, vaše daně z příjmu nebo úrokovou sazbu z půjčky. Řekněme, že se vám poštěstí získat 25% slevu na nový televizor. Je to dobrý obchod? A kolik budete muset nakonec zaplatit? Rozeberme si procentuální výpočty a použijeme k tomu Excel.

• Základní vzorec pro výpočet procenta z čísla.
• Jak vypočítat procento mezi čísly ve sloupcích.
• Jak vypočítat procento podle řádků.
• Podíl v procentech.
• Vypočítáme procento slevy
• Procentuální odchylka pro záporná čísla
• Odečítání procent
• Jak se vyhnout rozdělení podle nulové chyby

Co je to procento?

Jak si možná pamatujete z hodin matematiky ve škole, procento je zlomek 100, který se vypočítá vydělením dvou čísel a vynásobením výsledku 100.

Základní procentuální vzorec vypadá takto:

(část / celek) * 100 % = procento

Pokud jste například měli 20 jablek a dali jste 5 svým přátelům, kolik jste dali v procentech? Po provedení jednoduchého výpočtu = 5/20*100 % , Dostanete odpověď – 25 %.

Tak se obvykle počítají procenta ve škole i v běžném životě. Výpočet procent v aplikaci Microsoft Excel je ještě jednodušší, protože část práce udělá za vás automaticky.

Univerzální vzorec pro výpočet procent v Excelu, který by pokryl všechny možné případy, bohužel neexistuje. Pokud se někoho zeptáte: „Jaký procentuální vzorec používáte, abyste dosáhli požadovaného výsledku?“ pravděpodobně dostanete odpověď jako: „Záleží na tom, jaký přesný výsledek chcete získat.“

Dovolte mi tedy ukázat vám několik jednoduchých vzorců pro výpočet procent v Excelu.

Výpočet procent v Excelu.

Základní vzorec pro výpočet procenta z čísla v Excelu je stejný jako ve všech oblastech života:

Část / celek = procento

Pokud to porovnáte se základním matematickým vzorcem pro procenta, který jsme vám ukázali výše, všimnete si, že ve vzorci procent v Excelu chybí část *100. Při výpočtu procent v Excelu nemusíte výsledný zlomek vůbec násobit 100, protože to program provede automaticky, když je na buňku aplikován formát procent.

A pokud v Excelu zadáte vzorec s procenty, nemusíte mentálně převádět procenta na desetinné zlomky a dělit procento 100. Stačí zadat číslo s %.

To znamená, že pro výpočet 10 % v Excelu, například místo =A1*0,1 nebo =A1*10/100, jednoduše zapište procentuální vzorec =A1*10 %.

I když z matematického hlediska jsou možné všechny 3 možnosti a všechny dají správný výsledek.

Nyní se podíváme na to, jak můžete použít procentuální vzorec v Excelu pro reálná data. Řekněme, že máte v excelové tabulce součet 3.objednaného zboží ve sloupci F a opuštěné zboží ve sloupci G. Pro výpočet procenta dodaného zboží postupujte takto:

• Do buňky H2 zadejte vzorec =G2/F2 a zkopírujte jej na tolik řádků, kolik potřebujete.
• Klikněte na tlačítko Procentuální styl (nabídkaHlavní» > skupina «Číslo”) zobrazíte výsledné desetinné zlomky jako procenta.
• V případě potřeby nezapomeňte zvýšit počet desetinných míst ve výsledku.
• Připraveno!

Stejnou posloupnost kroků je třeba dodržet při použití jakéhokoli jiného procentuálního vzorce v Excelu.

Na níže uvedeném snímku obrazovky můžete vidět zaokrouhlené procento dodaných položek bez desetinných míst.

Pro stanovení procenta dodávky vydělíme množství dodaného zboží množstvím objednávek. A v buňce používáme formát procent, v případě potřeby zobrazujeme desetinná místa.

Zadejte vzorec do nejvyšší buňky sloupce výpočtu a poté přetáhněte úchyt automatického vyplňování dolů ve sloupci. Spočítali jsme tedy procenta v celém sloupci.

Jak zjistit procento mezi čísly ze dvou sloupců?

Předpokládejme, že máme údaje o prodeji čokolády za 2 měsíce. Je nutné zjistit, jaké změny nastaly v implementaci. Nejjednodušší a nejnázornější způsob vyjádření odchylek prodeje je procentuální vyjádření.

Pro výpočet procentuálního rozdílu mezi hodnotami A a B použijte následující vzorec:

Při aplikaci tohoto vzorce na reálná data je důležité správně určit, která hodnota je A a která B. Například včera jste měli 80 jablek a dnes jich máte 100. To znamená, že nyní máte o 20 jablek více než dříve, což je nárůst o 25 %. Pokud jste měli 100 jablek a nyní jich máte 90, pak se počet jablek, které máte, snížil o 10, tedy o 10 %.

Vzhledem k výše uvedenému má náš Excel vzorec pro procentuální změnu následující podobu:

Nyní se podívejme, jak můžete tento vzorec procentuální změny použít ve svých tabulkách.

Tento vzorec procentuální změny vypočítá procentuální nárůst (nebo pokles) v únoru (sloupec E) ve srovnání s lednem (sloupec B).

A pak pomocí úchytu výplně jej zkopírujeme dolů ve sloupci. Nezapomeňte použít formát procent.

Záporná procenta přirozeně znamenají pokles tržeb, kladná procenta znamenají nárůst tržeb.

Podobným způsobem můžete vypočítat procentuální změnu cen za určité období.

Jak zjistit procento mezi čísly ze dvou řádků?

Používá se tento výpočet? Pokud máme hodně dat o změně nějakého ukazatele. A my chceme sledovat, jak se jeho hodnota v průběhu času měnila. Pojďme si to vysvětlit na příkladu.

Řekněme, že máme údaje o prodeji čokolády za 12 měsíců. Je potřeba sledovat, jak se prodeje měnily měsíc od měsíce. Čísla ve sloupci C ukazují procentuální nárůst nebo pokles tržeb v aktuálním měsíci oproti předchozímu měsíci.

Upozorňujeme, že první buňku C2 necháváme prázdnou, protože leden ji prostě nemá s čím srovnávat.

V C3 napíšeme vzorec:

Můžete také použít jinou možnost:

Obsah této buňky zkopírujeme po sloupci na konec tabulky.

Pokud potřebujeme porovnat tržby každého měsíce ne s předchozím, ale s nějakým základním obdobím (například s lednem aktuálního roku), pak mírně změníme náš vzorec s použitím absolutního odkazu na lednové tržby:

Absolutní odkaz na $B$2 zůstane nezměněn, když je vzorec zkopírován do C4 a níže:

A odkaz na B3 se změní na B4, B5 atd.

Dovolte mi připomenout, že ve výchozím nastavení se výsledky zobrazují jako desetinná čísla. Chcete-li zobrazit procenta, použijte na sloupec formát procent. Chcete-li to provést, klikněte na příslušné tlačítko na pásu nabídek nebo použijte kombinaci kláves Ctrl + Shift + % .

Desetinné číslo se automaticky zobrazuje v procentech, takže je nemusíte násobit 100.

Výpočet podílu v procentech (měrná hmotnost).

Podívejme se na několik příkladů, které vám pomohou rychle vypočítat procento z celkového počtu v aplikaci Excel pro různé sady dat.

Příklad 1. Součet je na konci tabulky v konkrétní buňce.

Velmi častým scénářem je situace, kdy máte součet v jedné buňce na konci tabulky. V tomto případě bude vzorec podobný tomu, o kterém jsme právě hovořili. Jediný rozdíl je v tom, že odkaz na buňku ve jmenovateli je absolutní odkaz (se znaménkem $). Znak dolaru ukotvuje odkaz na výslednou buňku, takže se při kopírování vzorce přes sloupec nezmění.

Vezměme si data o prodeji čokolády a spočítejme si podíl (procento) každého kupujícího na celkovém prodeji. Pro výpočet procenta z celkové částky můžeme použít následující vzorec:

Pro buňku G2 použijete relativní odkaz na buňku, protože chcete, aby se změnil při kopírování vzorce do jiných buněk ve sloupci G. Ale zadáte $G$13 jako absolutní odkaz, protože chcete zachovat jmenovatele pevně na G13 při kopírování vzorce dolů do řádku 12.

Rada. Chcete-li ze jmenovatele udělat absolutní odkaz, zadejte znak dolaru ($) ručně nebo klikněte na odkaz na buňku v řádku vzorců a stiskněte F4.

Rada. Chcete-li ze jmenovatele udělat absolutní odkaz, zadejte znak dolaru ($) ručně nebo klikněte na odkaz na buňku v řádku vzorců a stiskněte F4.

Níže uvedený snímek obrazovky ukazuje výsledky vrácené vzorcem. sloupec“Procento z celku» formátován pomocí procentuálního formátu.

Příklad 2. Část celkové částky je umístěna na několika řádcích.

Ve výše uvedeném příkladu řekněme, že máte v tabulce více záznamů pro stejný produkt a chcete vědět, jaká část z celkové částky pochází ze všech objednávek tohoto konkrétního produktu.

V tomto případě můžete pomocí funkce SUMIF nejprve sečíst všechna čísla spojená s daným produktem a poté toto číslo vydělit celkovou částkou objednávky:

=SUMIF( rozsah, kritéria, rozsah_součtu ) / Celkem

Vzhledem k tomu, že sloupec D obsahuje všechny názvy produktů, sloupec F uvádí odpovídající částky, buňka I1 obsahuje položku, která nás zajímá, a celková částka je v buňce F13, může váš výpočet vypadat takto:

Název produktu samozřejmě můžete zadat přímo ve vzorci, například:

Ale to není úplně správné, protože tento vzorec bude muset být často upravován. To je ale časově náročné a plné chyb.

Pokud chcete vědět, jakou část celkové částky představuje několik různých položek, sečtěte výsledky vrácené několika funkcemi SUMIF a vydělte toto číslo celkovou částkou. Například pro výpočet podílu tmavé a super tmavé čokolády se používá následující vzorec:

=(SUMIF(D2:D12,“Horká čokoláda“;F2:F12)/$F$13 + =SUMIF(D2:D12,“Super hořká čokoláda“;F2:F12)) / $F$13

Přirozeně je lepší nahradit textové názvy produktů odkazy na odpovídající buňky.

Další informace o funkci podmíněného součtu naleznete v následujících kurzech:

• Jak používat funkci SUMIF v Excelu
• SUMIFS a SUMIF v Excelu s více kritérii

Procentní smyk

Procentní vzorce se budou hodit při výpočtu úrovně slevy. Takže až půjdete nakupovat, pamatujte na následující:

Sleva v % = (zvýhodněná cena – běžná cena) / běžná cena

Sleva v % = zvýhodněná cena / běžná cena – 1

Ve výsledcích výpočtu se procento slevy zobrazí jako záporná hodnota, protože nová zlevněná cena je nižší než stará běžná cena. Pro výstup výsledku jako kladné číslo , zabalte vzorce do funkce ABS. Například:

Tohle bude mnohem známější.

Jak vypočítat procentní odchylku pro záporná čísla

Pokud jsou některé ze zdrojových hodnot záporná čísla, výše uvedené vzorce nebudou fungovat.

Běžným řešením je vytvořit jako jmenovatele ve vzorci kladné číslo. K tomu použijte funkci ABS:

Se starou hodnotou v B2 a novou hodnotou v C2 vypadá vzorec takto:

Jak vidíte, funguje to zcela správně s širokou škálou kombinací kladných a záporných čísel.

Kladné procento znamená nárůst, záporné procento znamená pokles hodnoty ukazatele.

Odečítání procent.

Často se stává, že znáte procento slevy na produkt. A musíte si spočítat, jaké procento z původních nákladů budete muset zaplatit. Jak jsme již řekli, procento v Excelu je běžné číslo. Proto jsou zde pravidla výpočtu stejná jako u běžných čísel.

Výpočtový vzorec v Excelu bude vypadat takto:

Jako obvykle nezapomeňte na procentuální formát buněk.

Zabránit rozdělení nulovými chybami #DIV/0

Pokud chcete vypočítat procento čísla v tabulce a vaše datová sada obsahuje násobek nula hodnoty, uzavřete vzorce do funkce IFERROR, abyste zabránili dělení nulovými chybami (#DIV/0! nebo #DIV/0!).

Zde je návod, jak vypočítat procento z čísla v Excelu. A i když práce s procenty nikdy nebyla vaším oblíbeným druhem matematiky, pomocí těchto základních procentuálních vzorců můžete přimět Excel, aby to udělal za vás.

To je pro dnešek vše, děkuji za přečtení!

Vzorec, známý také jako funkce, je jednou z hlavních součástí tabulek vytvořených pomocí aplikace Microsoft Excel. Vývojáři přidali obrovské množství různých funkcí určených k provádění jednoduchých i složitých výpočtů. Kromě toho je uživateli umožněno samostatně provádět matematické operace, které lze také nazvat jakousi implementací vzorců. Právě o práci s těmito komponentami se budeme dále zabývat.

Proberu základy práce se vzorci a užitečné „triky“, které mohou zjednodušit proces interakce s tabulkami.

Vyhledání seznamu dostupných funkcí v Excelu

Pokud s Microsoft Excel teprve začínáte, je užitečné vědět, jaké funkce existují, k čemu se používají a jak se vytvářejí. K tomuto účelu má program k dispozici grafické menu zobrazující celý seznam vzorců a stručný popis postupu výpočtu.

1. poutko „vzorce“ a klikněte na tlačítko „Vložit funkci“ nebo rozbalte seznam o kategorii funkcí, které se vám líbí.
2. Místo toho můžete vždy kliknout na ikonu obrázku „Fx“ otevřít okno „Vložit funkci“.
3. V tomto okně přepněte kategorii na „Kompletní abecední seznam“pro zobrazení všech dostupných vzorců v Excelu v níže uvedeném seznamu v abecedním pořadí.
4. Vyberte libovolný řádek levým tlačítkem myši a přečtěte si krátký popis níže. V závorkách je uvedena syntaxe funkce, kterou je třeba dodržet při jejím psaní, aby se všechny argumenty a hodnoty shodovaly a výpočty proběhly správně. Klikněte „Nápověda k této funkci“, pokud chcete otevřít stránku o tom v oficiální dokumentaci společnosti Microsoft.
5. V prohlížeči uvidíte velké množství informací o vybraném vzorci v textovém i video formátu, což vám umožní samostatně pochopit princip jeho fungování.

Chtěl bych upozornit, že dostupnost takových informací v ruštině a v tak podrobné podobě ještě více usnadňuje proces seznamování se softwarem, zejména pokud jde o přechod ke složitějším funkcím, které fungují ne zcela zjevným způsobem. Nestyďte se a zajděte si na zmíněné stránky nechat pomoci od odborníků a dozvědět se něco nového, co alespoň minimálně nebo i výrazně urychlí pracovní proces.

Komunita je nyní na telegramu
Přihlaste se k odběru a zůstaňte informováni o nejnovějších IT zprávách

Vložení funkce do tabulky

Nyní pojďme zjistit, jak nastavit vzorec v Excelu, to znamená přidat jej do tabulky, což zajistí výpočet určitých hodnot. Funkce můžete psát sami tak, že jejich název uvedete za „=“, a použijte grafické menu, jehož přechod se provádí výše uvedeným způsobem. Komunita již má článek „Jak vložit vzorec do Excelu“, takže doporučuji kliknout na zvýrazněný odkaz a přečíst si užitečný materiál.

Použití matematických operací v Excelu

Pokud potřebujete provádět matematické operace s buňkami nebo konkrétními čísly, Excel také vytvoří vzorec, protože všechny položky, které začínají „=“ v buňce jsou považovány za funkce. Všechny znaky pro matematické operace jsou standardní, to znamená „*„- násobit,“/“ – rozdělit a tak dále. Je třeba poznamenat, že znak “ se používá k povýšení na moc.^Podívejme se krátce na deklaraci takových funkcí.

Vyberte libovolnou prázdnou buňku a napište do ní znak „.=„, čímž deklarujete funkci. Jako hodnotu můžete vzít libovolné číslo, zapsat číslo buňky (pomocí písmen a číselných hodnot vlevo a nahoře) nebo jej vybrat levým tlačítkem myši. Na následujícím snímku obrazovky můžete vidět jednoduchý příklad =B2*C2, to znamená, že výsledkem funkce bude vzájemné násobení zadaných buněk.

Po vyplnění údajů klikněte vstoupit a podívejte se na výsledek. Pokud je syntaxe funkce správná, ve vybrané buňce se místo chybové zprávy zobrazí číslo.

Zkuste sami používat různé matematické operace, přidávejte závorky, střídejte čísla a buňky, abyste rychle pochopili všechny možnosti matematických operací a v případě potřeby je v budoucnu použili.

Protahovací funkce a zápis konstant

Práce se vzorci v Excelu také zahrnuje provádění složitějších akcí souvisejících s vyplňováním řádků celé tabulky a propojováním několika různých hodnot. V této části článku zkombinuji dvě různá témata najednou, protože spolu úzce souvisí a obě zjednodušují interakci s projektem otevřeným v programu.

Začněme roztažením funkce. Chcete-li to provést, musíte jej zadat do jedné buňky a ujistit se, že získáte správný výsledek. Poté podržte tečku v pravém dolním rohu buňky a přetáhněte ji dolů.

V důsledku toho byste měli vidět, že funkce byla roztažena do vybraného rozsahu a hodnoty v ní byly automaticky nahrazeny. Původní funkce tedy měla podobu =B2*C2, ale po roztažení dolů byly následující hodnoty nahrazeny automaticky (od B3*C3 na B13*C13, jak je vidět na následujícím obrázku). Protahování funguje stejným způsobem s SOUČET a další jednoduché vzorce, které používají více argumentů.

Konstanta nebo absolutní odkaz je označení, které připíná konkrétní buňku, sloupec nebo řádek, takže když se funkce roztáhne, vybraná hodnota se nenahradí, ale zůstane stejná.

Nejprve zjistíme, jak nastavit konstantu. Jako příklad udělejme jak řádek, tak sloupec konstantní, to znamená, že buňku opravíme. Chcete-li to provést, vložte znak „$» v blízkosti písmene i čísla buňky, aby výsledný pravopis odpovídal následujícímu obrázku.

Roztáhněte funkci a všimněte si, že konstantní hodnota zůstává stejná, to znamená, že byl nahrazen pouze první argument. Nyní se to může zdát složité, ale jakmile takový úkol sami implementujete, vše bude křišťálově čisté a v budoucnu si budete pamatovat, že pomocí takového triku můžete plnit konkrétní úkoly.

Pro upevnění tématu se podívejme na tři konstanty, které lze určit při psaní funkce:

• $ B $ 2 – při roztahování nebo kopírování zůstává sloupec a řádek konstantní.
• 2 $ – řádek zůstává nezměněn.
• $ B2 – konstanta platí pouze pro sloupec.

Grafické funkce

Funkční grafy jsou tématem, které nepřímo souvisí s používáním vzorců v Excelu, protože nezahrnuje jejich přidání do tabulky, ale přímo vytvoření tabulky pomocí vzorce, aby se z ní následně vytvořil graf nebo spojnicový graf. Nebudeme se nyní podrobně zabývat tímto tématem, ale pokud vás to zajímá, klikněte na odkaz níže a přečtěte si můj další článek na toto téma.

V tomto článku jste se dozvěděli, jaké funkce jsou v Excelu, jak vytvořit vzorec a používat užitečné funkce programu, které usnadňují proces interakce s tabulkami. Aplikovat získané znalosti pro samostatnou praxi a zadané úkoly, které vyžadují výpočty a jejich automatizaci.