Brian Curtis
1 
1370
74
Mnoho lidí považuje aplikaci Excel za nástroj, který je vhodný pouze pro obchodní aplikace. 3 Crazy Excel Formulas, které dělají úžasné věci 3 Crazy Excel Formulas, které dělají úžasné věci Excel formulace mají výkonný nástroj v podmíněném formátování. Tento článek popisuje tři způsoby, jak zvýšit produktivitu s MS Excel. , ale když uvidíte níže uvedené jednoduché a užitečné vzorce, uvidíte, že Excel vám také pomůže vyřešit problémy skutečného života doma.
Ať už nakupujete nový úvěr na auto, chcete zjistit, která investice do podílového fondu je pro vás nejlepší, nebo pokud se jen pokoušíte dát smysl ze svého bankovního účtu, Excel je mocný nástroj, který vám může pomoci.
Těchto 15 vzorců níže bylo pečlivě vybráno jak pro jednoduchost, tak pro to, jak silné jsou, když pomáhají běžným lidem při řešení složitých problémů. Pokud jste učitel, který se snaží třídit studentské známky, rodič se snaží získat informace o výdajích na domácnost, nebo mladý pár, který nakupuje první půjčku na bydlení, níže uvedené vzorce mohou pomoci.
Finanční vzorce
Nakupování nového domu a zmatení všemi hypotečními žargony? Při hledání nového automobilu a zmatení z podmínek půjčky na auto prodejce na vás pořád hází?
Nemít strach. Než začnete nakupovat, proveďte svůj průzkum pomocí Excelu vedle sebe!
PMT - platba
Kdykoli jste porovnání jakýchkoli podmínek půjčky a chcete rychle zjistit svou skutečnou měsíční platbu s ohledem na různé rozdíly v termínech, využít výhod výkonných (a jednoduchých) Vzorec PMT.
Zde je potřeba použít tento vzorec:
- procentní úroková sazba půjčky
- doba trvání půjčky (kolik plateb?)
- výchozí princip úvěru
- budoucí hodnota, bude-li z nějakého důvodu půjčka považována za splacenou dříve, než dosáhne nulové hodnoty (nepovinné)
- typ úvěru - 0, pokud jsou platby splatné na konci každého měsíce, nebo 1, pokud jsou splatné na začátku (volitelné)
Zde je skvělý způsob, jak rychle porovnat různé půjčky a zjistit, jak budou vypadat vaše platby; stačí vytvořit list Excelu, ve kterém jsou uvedeny všechny potenciální půjčky a všechny dostupné informace o nich. Poté vytvořte “Platby” sloupec a použijte vzorec PMT.
Stačí uchopit pravý dolní roh buňky PMT, kterou jste právě vytvořili, a přetáhnout ji dolů, aby vypočítal celkovou částku platby pro všechny úvěrové podmínky uvedené v listu.
Jak vidíte, když můžete porovnat podmínky půjčky vedle sebe, ukazují se některé ostré skutečnosti. Například pro půjčku na auto ve výši 19 500 $, pokud si uděláte čas na to, abyste se rozhlédli a získali půjčku, která je jen o pár úrokových bodů nižší, mohlo by vám to každý měsíc ušetřit spoustu peněz a tisíce dolarů za život půjčka.
(Velmi velké poděkování patří Markovi Jonesovi (@redtexture na Twitteru), který zdůraznil, že u vzorců PMT a FV musíte být opatrní při používání stejného období - v tomto případě vyžaduje měsíční platby rozdělení úrokového období o 12 měsíců)
@rdube
Vážné chyby: vzorec PMT a FV počítá procento za měsíc, nikoli za rok. Úroková sazba 12x.https: //t.co/wrC5eXFzNw- Mark Jones (@redtexture) 26. listopadu 2015
To je důvod, proč jsou naši čtenáři tak skvělí - děkujeme za pomoc s touto opravnou značkou!
FV - budoucí hodnota
Když jste, příští vzorec se hodí chtějí investovat nějaké peníze na něco jako CD (vkladový certifikát) a chcete vědět, co bude CD na konci období užitečné.
Zde je to, co potřebujete vědět, abyste mohli používat FV vzorec:
- úroková sazba úvěru
- počet plateb (nebo investiční doba v měsících)
- platba za každé období (obvykle měsíčně)
- aktuální počáteční zůstatek (volitelné)
- typ úvěru - 0, pokud jsou platby splatné na konci každého měsíce, nebo 1, pokud jsou splatné na začátku (volitelné)
Pojďme tedy porovnat několik bankovních CD pomocí podmínek, které znáte z informací, které vám banky poskytly. V níže uvedeném příkladu řekněme, že máte investici do CD 20 000 dolarů.
Jak vidíte, úrokové sazby jsou opět reprezentovány v desítkovém formátu (vezměte úrokovou sazbu, kterou vám banka dala, a vydělte 100) a platby jsou nulové, protože CD jsou obvykle založena na počáteční hodnotě a budoucí hodnotě vyplacené. Jak vypadá srovnání, když použijete FV vzorec pro každé uvažované CD.
Bezpochyby CD s vyšším zájmem po delší dobu vyplatí mnohem více. Jedinou nevýhodou je samozřejmě to, že se nemůžete dotknout žádné ze svých peněz tři celé roky. Pro mnoho lidí je to jen trochu příliš dlouhé, ale to je povaha investování!
Logické funkce (IF nebo AND)
Většina bank v těchto dnech vám dává možnost stahovat téměř roční hodnotu bankovních transakcí do formátu, jako je CSV. Toto je perfektní formát pro analýzu vašich výdajů pomocí Excelu, ale někdy jsou data, která obdržíte od bank, velmi narušená.
Použití logických funkcí je skvělý způsob přehnané místo.
V ideálním případě banka buď automaticky kategorizuje vaše výdaje, nebo jste nastavili svůj účet tak, aby věci byly zařazeny do výdajových kategorií. Například všechny restaurace, které jdeme označit “StravováníOut” označení.
Díky tomu je snadné použít logickou funkci k identifikaci, kdykoli jsme šli jíst a utratili více než 20 $.
Chcete-li to provést, vytvořte logickou funkci v novém sloupci a vyhledejte jakoukoli hodnotu, kde je sloupec kategorie “StravováníOut” a sloupec transakce je větší než - 20 $ (níže uvedené srovnání) “<“, less than, because the values in column C are all negative. Here's what that looks like:
Použití IF a AND společně v jednom vzorci vypadá složitě, ale pokud o tom přemýšlíte, dává to dokonalý smysl. Příkaz IF vydá částku dolaru (C2), pokud je příkaz AND pravdivý, nebo FALSE, pokud tomu tak není. Příkaz AND zkontroluje, zda je kategorie “StravováníOut” a transakce je větší než 20 $.
Tady to máš! Aniž byste museli ručně procházet všechny tyto transakce, nyní přesně víte, kdy jste v určité kategorii přešli. Jako obvykle, Excel vzorce se ukázaly být masivním šetřičem času.
Vytváření smyslů pro seznamy
Seznamy jsou velkou součástí každodenního života. Pokud spravujete domácnost, seznamy používáte neustále.
Možná jste ve Výboru pro rodiče a poslali jste dlouhý seznam komunitních darů. Je na vás, abyste zjistili, kdo v komunitě daroval konkrétní školní projekt a kolik. Nebo možná provozujete rodinný podnik a platíte mzdu za půl tuctu zaměstnanců, které zaměstnáváte, a musíte shrnout své individuální náklady.
COUNT a COUNTIF
Ať už je seznam jakýkoli, Excel vám pomůže rychle uspořádat a třídit hodnoty. Vezměme si příklad PTC. Zde je seznam darů od členů komunity.
Pokud chcete vidět, kolikrát se jméno osoby v seznamu zobrazuje, můžete kombinovat vzorec COUNT s vzorcem IF. Nejprve tedy vytvořte sloupec a zkontrolujte, zda je daná osoba Michelle. Pokud ano, udělejte buňku 1. Pokud tomu tak není, udělejte z ní mezeru ” “.
Dále vytvořte další sloupec, který počítá, kolikrát jste v seznamu našli Michelle Johnson.
Tím získáte počet všech míst ve sloupci E, kde je místo jedné místo prázdné místo.
Jedná se tedy o nejjednodušší způsob, jak to udělat, ale vyžaduje to dva kroky.
Pokud vám nevadí použití trochu pokročilejší funkce, můžete zvážit použití jedné z mnoha kombinovaných funkcí “LI” funguje jako SUMIF, COUNTIF, nebo AVERAGEIF. Umožňují provádět funkci (COUNT, SUM nebo AVERAGE) pouze tehdy, je-li splněna logická podmínka. Takto to funguje pomocí výše uvedeného příkladu.
Tento vzorec se dívá na sloupec A, který obsahuje všechna jména dárců, a pokud buňka v rozsahu odpovídá kritériím v uvozovkách, pak se počítá jednou. To vám poskytne počet všech dob, kdy se jméno dárce rovná “Michelle Johnson” v jednom kroku.
Je to mnohem rychlejší než použití dvou sloupců, ale pro některé lidi to může být příliš složité - proto použijte přístup, který nejlépe vyhovuje vaší situaci.
Funkce LEN
Další funkcí, kterou můžete kreativně použít, je funkce LEN. Tato funkce vám řekne, kolik znaků je v řetězci textu. Úspora času s textovými operacemi v Excelu Úspora času s textovými operacemi v Excelu Excel umí magii s čísly a dokáže stejně dobře zpracovávat znaky. Tato příručka ukazuje, jak analyzovat, převádět, nahrazovat a upravovat text v tabulkách. Tyto základy vám umožní provádět komplexní transformace. .
Jedním zajímavým způsobem, jak to použít ve výše uvedeném příkladu, by bylo upozornit na dárce, kteří darovali více než 1 000 $, spočítáním počtu číslic ve sloupci darování.
Pak, pokud chcete, můžete dokonce naformátovat další sloupec na základě hodnoty ve sloupci E; výsledek funkce LEN.
Chcete-li to provést, musíte zvýraznit všechny buňky ve sloupci Darování a vyberte Domov v nabídce klikněte na Podmíněné formátování na panelu nástrojů. Poté vyberte Pomocí vzorce určete, které buňky se mají formátovat.
Nastavte rozsah pod Formátujte hodnoty, kde je tento vzorec pravdivý: do sloupce / rozsahu, kde jsou zobrazeny všechny vaše funkční výstupy LEN.
V tomto příkladu, pokud uděláte podmínku “> 3”, potom cokoli nad 1 000 $ obdrží speciální formátování. Nezapomeňte kliknout na ikonu Formát… a vyberte, jaké speciální formátování chcete pro ně.
Také rychlá poznámka. Zjistíte, že můj rozsah je definován jako “$ E2: $ E11”, ne “$ E $ 2: $ E $ 11”. Z nějakého důvodu, když vyberete rozsah, je výchozí pro první, který nebude fungovat. Musíte použít relativní adresování, jak je znázorněno na obrázku výše. Poté bude podmíněné formátování fungovat na základě stavu druhého rozsahu.
Organizování bankovních a finančních stahování
Někdy, když stahujete informace od podniků - ať už je to vaše banka, zdravotní pojišťovna nebo kdekoli jinde, kde podnikáte, formát příchozích dat neodpovídá vždy tomu, co potřebujete, aby bylo.
Řekněme například, že v exportovaných datech z vaší banky máte datum ve standardním formátu.
Pokud chcete přidat nový vlastní sloupec s vlastním, který je před rokem předsazený a obsahuje informace o příjemci platby (pro vaše vlastní účely třídění), extrahování částí informací ze sloupce je opravdu snadné.
Vpravo, VLEVO, TEXT a KONCATENÁT
Rok můžete vytáhnout z textu v tomto sloupci pomocí ŽE JO funkce.
Výše uvedená funkce říká Excelu, aby převzal text ve sloupci D a extrahoval čtyři znaky z pravé strany. CONCATENATE funkce spojí tyto čtyři číslice společně s textem Příjemce z následujícího sloupce.
Nezapomeňte, že pokud chcete extrahovat text z data, budete jej muset převést do textového formátu (místo data) pomocí “= TEXT (D2,”mm / dd / rrrr”)” funkce. Poté můžete pomocí funkce RIGHT vytáhnout rok.
CONCATENATE se opravdu hodí, když máte text z několika různých sloupců, které chcete spojit do jednoho dlouhého řetězce. Můžete například zřetězit jméno, příjmení, adresu, město a stát osoby do pole, které obsahuje celou adresu osoby v jednom sloupci.
Sbíráme náhodná jména z klobouku
Jedna poslední zábavná funkce je ta, kterou můžete použít, pokud musíte udělat něco jako vybrat některá jména z klobouku. Jak získat bezplatnou šafránovou vstupenku pro Microsoft Word Jak získat bezplatnou šafránovou lístek pro Microsoft Fundraising je hodně práce. Sundejme si něco z vašich ramen a ukážeme vám, jak najít šablony a přizpůsobit vstupenky zdarma tombola. Vše, co potřebujete, je textový procesor. na vánoční večírek. Klobouk a ty kousky papíru odložte a místo toho vytáhněte notebook a spusťte Excel!
Pomocí funkce RANDBETWEEN, můžete nechat Excel náhodně vybrat číslo mezi rozsahem čísel, které určíte.
Dvě hodnoty, které musíte použít, jsou nejnižší a nejvyšší čísla, která by měla být na konci rozsahu čísel použitých pro jméno každé osoby.
Jakmile stisknete klávesu Enter, funkce náhodně vybere jedno z čísel v rozsahu.
Je to asi tak náhodné a odolné proti neoprávněné manipulaci, jak jen můžete. Místo toho, abyste vybrali číslo z klobouku, místo toho vyberte číslo z Excelu!
Používání Excelu pro každodenní problémy
Jak vidíte, Excel není jen pro guru pro analýzu dat a obchodní profesionály. Mnoho funkcí, které najdete v Excelu, může mít kdokoli.
Poskytl vám tento článek nějaké kreativní nápady, jak můžete použít Excel k řešení problémů ve vašem vlastním životě? Už jste někdy některé z těchto funkcí Excelu použili dříve kreativním způsobem? Podělte se o své vlastní nápady a příběhy v sekci komentářů níže, rád bych je slyšel!
Obrázek: Goodluz přes Shutterstock.com