Domov › Služby › Analýza dat v Excelu. Doplněk Analýza dat v Excelu Nástroje Excelu pro analýzu dat a řešení optimalizačních problémů

Analýza dat v Excelu. Doplněk Analýza dat v Excelu Nástroje Excelu pro analýzu dat a řešení optimalizačních problémů

Zdrojová data pro analýzu mohou být prezentována na pracovním listu jako seznam hodnot. K identifikaci pole hodnot se používají názvy sloupců – štítky – a vytvářejí se pojmenované bloky.

Pro zpracování numerických dat použijte Analysis Package. Nejprve je nutné jej nakonfigurovat

v Excelu 2003: zadejte příkaz Tools -> Add-ins a zaškrtněte políčko vedle Analysis Pack. Nyní se v nabídce Nástroje objeví příkaz Analýza dat.

v Excelu 2007: klikněte na tlačítko Office a poté klikněte na tlačítko Možnosti Vynikat VybratDoplňky, ve spodní části okna v poli Ovládání vyberte Doplňky Vynikat, klikněte na tlačítko Přejít, zaškrtněte políčko vedle Balíček analýzy yu Na kartě Data se ve skupině Analýza zobrazí příkaz Analýza dat

Při provádění příkazu Analýza dat otevře dialogové okno Analýza dat, ve kterém je vybrán režim Popisné statistiky (obr. 23); Ve stejnojmenném dialogovém okně se zadávají následující nastavení:

rýže. Dialogové okno režimu popisné statistiky.

Možnosti v dialogovém okně Popisná statistika jsou následující.

Vstupní rozsah je blok buněk obsahující hodnoty studovaného indikátoru. Musíte zadat odkaz na buňky obsahující analyzovaná data. Odkaz se musí skládat alespoň ze dvou souvislých rozsahů dat uspořádaných jako sloupce nebo řádky.

Seskupení určuje orientaci bloku zdrojových dat na listu. Chcete-li jej určit, musíte nastavit přepínač na Podle sloupců nebo Podle řádků v závislosti na umístění dat ve vstupním rozsahu.

Štítky - přítomnost jmen v bloku buněk. Pro jeho definování je potřeba nastavit přepínač na pozici Popisky v prvním řádku (sloupci), pokud první řádek (sloupec) ve vstupním rozsahu obsahuje názvy sloupců. Pokud vstupní rozsah neobsahuje popisky, pak se potřebné nadpisy ve výstupním rozsahu vytvoří automaticky.

Úroveň spolehlivosti udává procento spolehlivosti dat pro výpočet intervalu spolehlivosti. Pro jeho definování je potřeba zaškrtnout políčko a zadat do pole požadovanou hodnotu. Například hodnota 95 % vypočítá úroveň spolehlivosti průměru s významností 0,05.

Kth největší - pořadové číslo největší po maximální hodnotě. Zaškrtněte toto políčko, pokud chcete, aby výstupní tabulka obsahovala řádek pro k-tou největší hodnotu pro každý rozsah dat. Do příslušného okna zadejte číslo k. Pokud je k 1, bude tento řádek obsahovat maximum datové sady.

Kth nejmenší - pořadové číslo nejmenšího za minimální hodnotou. Zaškrtněte toto políčko, pokud chcete, aby výstupní tabulka obsahovala řádek pro k-tou nejmenší hodnotu pro každý rozsah dat. Do příslušného okna zadejte číslo k. Pokud je k 1, bude tento řádek obsahovat minimum datové sady.

Popisné statistiky se zobrazují na místě uvedeném v poli Rozsah výstupu. Zde je třeba zadat odkaz na levou horní buňku výstupního rozsahu. Tento analytický nástroj zobrazuje dva sloupce informací pro každou sadu dat. Levý sloupec obsahuje popisky statistických dat; pravý sloupec obsahuje statistické údaje. Pro každý sloupec (řádek) vstupního rozsahu bude vydán dvousloupcový rozsah statistik v závislosti na poloze přepínače seskupení.

Chcete-li změnit místo zobrazení výsledků, můžete výběrem přepínače Nový list otevřít nový list a vložit výsledky počínaje buňkou A1. Název nového listu můžete zadat do pole naproti odpovídající poloze přepínače. Pokud vyberete přepínač Nový sešit, otevře se nový sešit a výsledky se vloží do buňky A1 na prvním listu v tomto sešitu.

Souhrnná statistika - plný výstup popisné statistiky. Chcete-li ji definovat, musíte zaškrtnout políčko, pokud ve výstupním rozsahu potřebujete získat jedno pole pro každý z následujících typů statistických dat: Průměr, Standardní chyba (střední hodnoty), Medián, Režim, Směrodatná odchylka, Rozptyl vzorku , Kurtosis, Šikmost, Interval, Minimum, Maximum, Částka, Účet, Nejvyšší (#), Nejnižší (#), Úroveň spolehlivosti.

Balíček analýzy je doplněk, tedy program, který je k dispozici při instalaci sady Microsoft Office nebo Excel. Chcete-li použít doplněk v aplikaci Excel, musíte si jej nejprve stáhnout. Jak stáhnout tento balíček pro Microsoft Excel 2013, Microsoft Excel 2010, Microsoft Excel 2007.

Pomocí analytického balíčku Microsoft Excel 2013

Při provádění složitých statistických nebo inženýrských analýz můžete proces zjednodušit a ušetřit čas pomocí doplňku Analysis Package. Chcete-li provést analýzu pomocí tohoto balíčku, musíte zadat vstupní data a vybrat parametry. Výpočet bude proveden pomocí vhodné statistické nebo inženýrské makro funkce a výsledek bude umístěn do výstupního rozsahu. Některé nástroje umožňují graficky prezentovat výsledky analýzy.

Funkce analýzy dat lze použít pouze na jednom listu. Pokud se analýza dat provádí ve skupině skládající se z několika listů, pak se výsledky zobrazí na prvním listu a prázdné oblasti obsahující pouze formáty se zobrazí na zbývajících listech. Chcete-li analyzovat data na všech listech, opakujte postup pro každý list zvlášť.

Nástroje zahrnuté v analytickém balíčku jsou popsány níže. Chcete-li se k nim dostat, klikněte na tlačítko Analýza dat ve skupině Analýza na kartě Data. Pokud tým Analýza dat není k dispozici, musíte si stáhnout doplněk Analysis Pack.

Stáhněte a aktivujte analytický balíček

Otevřete kartu Soubor, zmáčknout tlačítko Možnosti a vyberte kategorii Doplňky.
V rozevíracím seznamu Řízení vybrat předmět Excel doplňky a stiskněte tlačítko Jít.
V okně Doplňky zaškrtněte políčko Balíček analýzy a potom klepněte na tlačítko OK.

Li Balíček analýzy není v seznamu polí Dostupné doplňky, zmáčknout tlačítko Posouzení provést vyhledávání.
Pokud se zobrazí zpráva oznamující, že balíček analýzy není ve vašem počítači nainstalován, klepněte na tlačítko Ano jej nainstalovat.

Stáhněte si Microsoft Excel 2010 Analysis Pack

Balíček analýzy je doplněk (Doplněk. Pomocný program, který se používá k přidávání speciálních příkazů nebo funkcí do sady Microsoft Office.) Microsoft Excel, tj. program, který bude dostupný po instalaci sady Microsoft Office nebo Excel. Chcete-li však doplněk použít v aplikaci Excel, musíte si jej nejprve stáhnout.

Otevřete kartu Soubor a vyberte Možnosti.
Vyberte tým Doplňky a pak do terénu Řízení vybrat předmět Excel doplňky.
Klepněte na tlačítko Jít.
V okně Dostupné doplňky zaškrtněte políčko Balíček analýzy a potom klepněte na tlačítko OK.

Rada. Pokud doplněk Balíček analýzy není v seznamu polí Dostupné doplňky, zmáčknout tlačítko Posouzení najít ji.
Pokud se zobrazí zpráva oznamující, že balíček analýzy není na vašem počítači nainstalován, klepněte na Ano jej nainstalovat.

Analýza na kartě Data Analýza dat.

Poznámka. Chcete-li do analytického balíčku zahrnout funkce jazyka Visual Basic for Applications (VBA), můžete si stáhnout doplněk Analysis Pack – VBA. Chcete-li to provést, musíte provést stejné kroky jako při stažení balíčku analýzy. V okně Dostupné doplňky zaškrtněte políčko Balíček analýzy - VBA a potom klepněte na tlačítko OK.

Stáhněte si balíček Microsoft Excel 2007 Statistical Analysis Package

Balíček analýzy je doplněk (Add-in. Pomocný program, který se používá k přidávání speciálních příkazů nebo funkcí do sady Microsoft Office.), tj. program, který je k dispozici při instalaci sady Microsoft Office nebo Excel. Chcete-li použít doplněk v aplikaci Excel, musíte si jej nejprve stáhnout.

Vyberte tým Doplňky a v okně Řízení vybrat předmět Excel doplňky.
Klepněte na tlačítko Jít.
V okně Dostupné doplňky zaškrtněte políčko Balíček analýzy a potom klepněte na tlačítko OK.

Rada. Li Balíček analýzy není v seznamu polí Dostupné doplňky a poté pro vyhledávání klikněte na tlačítko Posouzení.

Pokud se zobrazí zpráva, že balíček statistické analýzy není na vašem počítači nainstalován a vyzve vás k jeho instalaci, klikněte na tlačítko Ano.

Po stažení analytického balíčku ve skupině Analýza na kartě Data příkaz bude dostupný Analýza dat.

Poznámka. Chcete-li do svého analytického balíčku zahrnout funkce VBA, můžete si stáhnout doplněk Analysis ToolPak - VBA. Chcete-li to provést, postupujte podle stejných kroků jako při stažení balíčku analýzy. V okně Dostupné doplňky zaškrtněte políčko Analytické nástroje - VBA a potom klepněte na tlačítko OK.

Kupujte produkty

Při provádění složitých analytických úloh ve statistice (například korelační a variační analýza, výpočty pomocí Fourierova algoritmu, vytváření prediktivního modelu) se uživatelé často zajímají o to, jak přidat analýzu dat do Excelu. Určený balík funkcí poskytuje všestranné analytické nástroje užitečné v řadě profesionálních oblastí. Nevztahuje se však na nástroje, které jsou ve výchozím nastavení obsaženy v Excelu a zobrazují se na pásu karet. Pojďme zjistit, jak povolit analýzu dat v aplikaci Excel 2007, 2010, 2013.

Pro Excel 2010, 2013

Podívejme se na analýzu dat v Excelu: jak ji povolit a jak se bude postup aktivace lišit u jiných verzí. Ve většině verzí programu se postup provádí stejným způsobem. Posloupnost akcí uvedená v této části je proto vhodná pro většinu verzí, včetně verzí 2013 a 2016.

Povolení Toolbox

Dotyčný balíček patří do kategorie doplňků, tedy komplexních analytických doplňků. Chcete-li tedy balíček aktivovat, přejděte do nabídky doplňků. Tento postup se provádí následovně:

přejděte na kartu „Soubor“ umístěnou v horní části pásu karet rozhraní;
na levé straně úvodní nabídky najděte sekci „Možnosti aplikace Excel“ a klikněte na ni;
podívejte se na levou stranu okna, otevřete kategorii doplňků (druhá zespodu v seznamu), vyberte příslušnou položku;
v dialogovém menu, které se objeví, najděte položku „Správa“, klikněte na ni myší;
kliknutím se zobrazí dialogové okno, vyberte sekci doplňků, pokud je hodnota nastavena na něco jiného než „Doplňky aplikace Excel“, změňte ji na uvedenou;
Klikněte na tlačítko obrazovky „Go“ v sekci doplňků. Na pravé straně se zobrazí seznam doplňků, které program nainstaluje.

Aktivace

Podívejme se, jak aktivovat analytické funkce poskytované doplňkem balíčku:

V seznamu doplňků, který se zobrazí po sekvenčním provedení předchozích operací, musí uživatel zaškrtnout políčko vedle části „Analytický balíček“.
Po výběru aktivace balíčku musíte stisknout tlačítko „OK“ umístěné v pravé horní části dialogového okna.
Po kliknutí na tlačítko se balíček zobrazí na pásu funkcí. Pro přístup k němu vyberte záložku „Data“ v rozhraní programu. Na pravé straně nabídky je „Sekce analýzy“. Zde uživatel najde ikonu možnosti „Analýza dat“.

Spuštění funkcí skupiny „Analýza dat“.

Analytický balík pracuje s velkou sadou nástrojů, které optimalizují řešení statistických problémů. Některá z čísel:

operace se vzorky;
vytvoření histogramu - typ sloupcového grafu, který demonstruje rozložení různých hodnot určitého parametru ve formě sloupců, jejichž oblasti spolu korelují stejným způsobem jako specifické váhy různých skupin v uvažovaném výběr;
generování náhodných čísel;
pořadové a procentuální řazení;
variace regrese, disperze, korelace, kovarianční analýza;
analýza pomocí Fourierova algoritmu;
exponenciální vyhlazování je metoda matematických transformací s cílem identifikovat určitý trend nebo tendenci v časové řadě. Metoda se používá k vytváření předpovědí.

Chcete-li použít jednu nebo druhou možnost, postupujte podle níže uvedeného algoritmu:

Klikněte na tlačítko analyzovat na pásu karet.
Klikněte na název funkce požadované uživatelem.
Stiskněte tlačítko „OK“ umístěné vedle pravého horního rohu okna.
V dialogovém okně zadejte datová pole použitá k vyřešení aktuálního problému.

Funkce obsažené v balíčku jsou navrženy pro použití čísel pouze z jednoho listu Excelu. Pokud jsou požadované statistické hodnoty umístěny na několika listech, budete muset nejprve vytvořit kontingenční tabulku tak, že tam zkopírujete požadované parametry.

Pro Excel 2007

Algoritmus pro povolení analýzy dat v Excelu 2007 se od ostatních liší tím, že hned na začátku (pro přístup k možnostem Excelu) uživatel místo tlačítka „Soubor“ klikne na čtyřbarevný symbol Microsoft Office. Jinak je sled operací stejný jako u ostatních verzí.

Kontingenční tabulky

K analýze seznamů dat (datových tabulek) v aplikaci Excel 2007, které mají mnoho řádků nebo záznamů, často používáte nástroje, jako jsou kontingenční tabulky. Kontingenční tabulky výrazně usnadňují zobrazení, manipulaci a shrnutí dat v seznamech aplikace Excel 2007.

Aby Excel mohl zacházet se zadanými daty jako s tabulkou dat, nikoli jako s jednoduchou sadou dat, musíte je naformátovat jako tabulku. Chcete-li to provést, klikněte na tlačítko Tabulka umístěné na kartě Vložit. V dialogovém okně Vytvořit tabulku (obrázek 1) zadejte očekávaný rozsah, ve kterém bude tabulka umístěna, a zaškrtněte políčko Tabulka se záhlavími.

Rýže. 1.

Na tento rozsah bude aplikováno formátování, tzn. použije se výchozí expresní styl tabulky a aktivují se kontextové nástroje pod obecným názvem „Práce s tabulkami“, které jsou obsaženy v kontextové záložce Návrh. K formátování tabulky můžete také použít nástroj Formátovat jako tabulku na kartě Domů.

Rýže. 2.

Pro vytvoření sestavy na této tabulce je vhodné použít výkonný nástroj kontingenční tabulky. Chcete-li použít tento nástroj na datové seznamy nebo datové tabulky, musíte aktivovat jednu z buněk datové tabulky, například buňku tabulky "Skladové položky ve skladu". Poté klikněte na tlačítko „Kontingenční tabulka“, které se nachází na kartě „Vložit“ ve skupině „Tabulka“ (obrázek 3).

Rýže. 3.

V dialogovém okně "Vytvořit kontingenční tabulku", které se otevře, musíte vybrat (vybrat) tabulku nebo rozsah a určit, kam umístit sestavu (nejlépe na nový list), a poté kliknout na OK. Otevře se speciální průvodce kontingenční tabulkou (obrázek 4).

Rýže. 4.

Levá strana listu zobrazuje obrázek sestavy (PivotTable1) a pravá strana listu obsahuje nástroje pro vytvoření kontingenční tabulky: čtyři prázdné oblasti a seznam polí. Chcete-li vytvořit sestavu, musíte přetáhnout požadovaná pole na pravé straně do odpovídajících oblastí kontingenční tabulky: „Filtr sestav“, „Názvy sloupců“, „Názvy řádků“ a „Hodnoty“.

Pokud například vyberete pole: Číslo skladu, Název, Cena (UAH) a přetáhnete je do odpovídajících oblastí: „Názvy sloupců“, „Názvy řádků“ a „Hodnoty“, zobrazí se na pravé straně v těchto oblastech. V tomto případě se na levé straně listu vytvoří kontingenční tabulka nebo sestava (obr. 5).

Rýže. 5.

Je třeba poznamenat, že v oblasti "Hodnoty" se provádějí některé matematické výpočty, například sumace (pole Suma podle ceny). Chcete-li změnit typ výpočtů, v oblasti „Hodnoty“ klikněte levým tlačítkem myši na pole „Součet podle ceny“ a v nabídce, která se otevře, vyberte příkaz „Možnosti pole hodnoty“ a poté v části „Možnosti pole hodnoty“ dialogovém okně vyberte požadovanou funkci a klikněte na tlačítko OK.

Chcete-li změnit strukturu kontingenční tabulky, musíte pole na pravé straně listu přetáhnout do jiné oblasti kontingenční tabulky nebo je odstranit. Je třeba poznamenat, že chcete-li odstranit pole, musíte je přetáhnout mimo tabulku.

Excelové nástroje pro analýzu dat a optimalizační problémy

Mezi výkonné nástroje pro analýzu dat aplikace Excel 2007 patří:

analýza „co kdyby“, která zahrnuje: výběr parametrů a správce scénářů;
doplněk "Hledat řešení" (add-on Solver).

Analytické nástroje What-if jsou umístěny na kartě Data ve skupině Datové nástroje a Hledání řešení se nachází na kartě Data ve skupině Analýza.

Výběr parametrů poskytuje řešení problému výběru parametrů pro funkci jednoho argumentu. Správce scénářů je navržen tak, aby vytvářel scénáře (nejprve přidal několik scénářů pro různé případy) a poté scénáře prohlížel, aby předpověděl proces a vytvořil zprávu o scénáři.

Program Search for Solutions je určen k řešení složitých soustav rovnic, lineárních a nelineárních optimalizačních úloh. Plugin Solver je založen na iteračních metodách.