Zobrazují se příspěvky se štítkemFunkce. Zobrazit všechny příspěvky
Zobrazují se příspěvky se štítkemFunkce. Zobrazit všechny příspěvky

sobota 28. prosince 2013

Řazení čísel pomocí funkce

Příklad

Potřebuji seřadit čísla v tabulce. Nechci ale (nebo nemohu) to udělat řazením - potřebuji použít funkci.

Návod

Připravím si někde sloupeček, kde budou čísla od jedničky do tolika, kolik je čísel. Pak do vedlejších sloupečku zapíšu funkci LARGE (řazení od největšího), resp. SMALL (řazení od nejmenšího).
Tato funkce bude mít v prvním parametru oblast, kde jsou původní neseřazená čísla, a ve druhém číslo z vedlejšího sloupce. Toto číslo vyjadřuje, kolikáté největší (nejmenší) číslo se právě na této pozici má zobrazit.


Pokud bych se chtěl obejít bez pomocného sloupečku, mohl by zápis funkce vypadat např. takto:
=LARGE(A:A;ŘÁDEK()-1)
V následujícím příkladu je v praxi použita kombinace funkce SMALL a SVYHLEDAT pro seřazení jmen závodníků podle času, kterého dosáhli:
Zápis funkce pak může vypadat např. takto:
=SVYHLEDAT(SMALL($A$2:$A$7;ŘÁDEK()-1);A:B;2;0)

čtvrtek 7. listopadu 2013

Funkce FORECAST

Do článku o funkcích pro regresní analýzu jsem doplnil popis funkce FORECAST. Funkce FORECAST slouží k tomu, aby se, na základě lineární regresní závislosti, dopočetla hodnota závislé proměnné (y) na základě hodnoty nezávislé proměnné (x).
Funkce FORECAST má syntaxi FORECAST(hodnota závislé proměnné;oblast s hodnotami x;, oblast s hodnotami y).
Jinými slovy. Dejme tomu, že řeším závislost počtu prodaných kopečků zmrzliny na průměrné denní teplotě. Mám údaje o tom, jak se při kterých teplotách zmrzlina prodávala dříve, a teď zjišťuji, kolik kopečků se prodá při denní teplotě 22 stupňů.
Pak je nezávislou proměnnou teplota a závislou počet kopečků a FORECAST se zapíše takto:

  • =FORECAST(22;oblast s údaji o prodaných kopečcích z minulosti;oblast s údaji o teplotě z minulosti)

Více v článku.

úterý 24. září 2013

Fígl s kombinací funkcí INDEX a POZVYHLEDAT (MATCH)

Nejspíše někdy používáte funkci VLOOKUP (SVYHLEDAT). Je to funkce velmi užitečná, má ale jednu nevýhodu, na kterou občas narazíte.

Příklad

V následujícím příkladu potřebuji doplnit k zaměstnancům jméno jejich nadřízeného. Na základě toho, do kterého oddělení zaměstnanec patří - protože každé oddělení má svého jednoho šéfa.



Mohl bych použít VLOOKUP. Ale to by musely sloupečky v tabulce vpravo mít obrácené pořadí. Funkce VLOOKUP totiž vyžaduje, aby v tabulce, na kterou se odkazuji, bylo v prvním sloupci to, podle čeho se obě tabulky propojují. Jinými slovy - tabulky jsou propojené přes název oddělení, proto v tabulce vpravo musí být sloupec s názvy oddělení vlevo od jména šéfa.

Návod

Sloupce bych mohl prohodit, ale ne vždy je to možné.
Proto použiji kombinaci funkcí INDEX a MATCH (POZVYHLEDAT), která mě toto umožní. Zápis do buňky C2, který pak roztáhnu, bude vypadat takto:
=INDEX(E:E;MATCH(B2;F:F;0))
Logika je taková, že:
  • Nejprve funkce MATCH zjistí, kolikátá je určitá hodnota ve sloupci. V našem případě zjistí, že hodnota "HR" je ve sloupci F na druhém místě. Výstupem vnořené funkce je tedy dvojka.
  • Pak funkce INDEX zjistí, co je na tomto místě v určitém sloupci. V našem případě zjistí, že na druhém místě je ve sloupci E hodnota "Hanka". A výstupem funkce, čili přiřazením nadřízeného pro Adélu, je správně "Hanka".
Vzorec roztáhnu a mám ošetřené všechny hodnoty / všechny zaměstnance.

Upozornění

Aby tato kombinace funkcí fungovala, musí být hodnoty v prohledávaném sloupci seřazené podle abecedy. Jinak se výsledky tváří, že fungují, ale nefungují.


pátek 6. září 2013

Refresh návodu VLOOKUP / SVYHLEDAT

Návod na použití VLOOKUP/SVYHLEDAT je druhým nejčtenějším návodem na tomto blogu. Takže si zasloužil kritické přečtení, odstranění nejasností a zpřehlednění.
Nová verze ke shlédnutí tady.

sobota 6. července 2013

Nahrazování znaků v Excelu

Při práci s Excelem se docela často dostanete do situace, kdy potřebujete nahrazovat nějaký text jiným. V tomto příkladu nahrazují čárky tečkami, nicméně velmi podobně to funguje s jakýmikoliv textovými řetězci. Mám v zásadě dvě možnosti - buď použít Najít / nahradit, nebo funkci SUBSTITUTE / DOSADIT.

Najít / nahradit

Dřevní, ale často velmi efektivní metoda.
Prostě stisknete Ctrl + F a vyberete, co za co se má nahradit.
V tomto případě mám sloupec datumů, kde jsou dny a měsíce oddělené čárkou místo tečky.


Mně se ale lépe pracuje s tečkami. Abych čárky na tečky změnil, použiji Ctrl + F, nastavím že se mění čárky na tečky a takto vypadá výsledek:

Najít / nahradit je velmi užitečná funkce, má ale jednu zásadní nevýhodu - funguje jednorázově. Tedy kdybych např. do tabulky uvedené nahoře přidal další datum s čárkami, tak se mi na tečky už nezmění do doby, než znovu použiji Najít / nahradit.
Pokud mi toto vadí, pomůže mi funkce SUBSTITUTE / DOSADIT.

SUBSTITUTE / DOSADIT

Tato funkce nahrazuje ve vybraném textu určitý text jiným textem.
Pokud bych ji chtěl použít v předchozím případě, vypadal by zápis takto:
=DOSADIT(A1;",";".")

  • První argument je text, se kterým pracuji
  • Druhý argument je text, který se má najít
  • Třetí argument je text, kterým se má text ze druhého argumentu nahradit
  • Čtvrtý, nepovinný argument je číslo výskytu, na které se má výměna použít. Např. zápis DOSADIT("tadydadyda";"a";"X";2) vyhodí "tadydXdyda - protože se nahradilo druhé áčko velkým ikskem.