Acest articol va discuta despre cum să analizați datele folosind un tabel pivot. Pentru antrenament, puteți folosi tabelul disponibil la acest link (simple table.xlsx).
Primul lucru pe care poate fi necesar să îl analizați folosind un tabel pivot este să însumați subtotalurile. În exemplul nostru, aceasta poate fi necesitatea de a calcula volumele vânzărilor pentru toate magazinele pentru fiecare dată.
Pentru a face acest lucru, faceți clic pe orice antet de rând al tabelului pivot (în exemplul nostru, acestea sunt câmpurile Data, punctul de vânzareși Marca telefonului), și în filele deschise Lucrul cu tabele pivot accesați fila Opțiuni. Trebuie să apăsați butonul de pe el. Opțiuni de câmpîntr-un grup câmp activ.
În fereastra care se deschide, prima filă va fi fila.
Absența unui astfel de marcaj înseamnă că nu ați selectat antetul rândului, adică cursorul este plasat pe o celulă cu o valoare numerică.
Marcaj Subtotaluri și filtre Puteți selecta o condiție pentru afișarea subtotalurilor. Sunt oferite următoarele condiții:
Prin setarea subtotalului automat, obținem următorul tabel, care conține subtotaluri pentru fiecare condiție:
Dacă setați subtotaluri cu comanda Opțiuni de câmp, nu dă rezultate vizibile, verificați setările pentru afișarea subtotalurilor folosind comanda Subtotaluri grupuri Aspect file Constructor.
Să presupunem că vrem să afișăm subtotalurile numai pentru date, ascunzând subtotalurile pentru punctele de vânzare. Pentru a face acest lucru, faceți clic pe orice câmp al tabelului cu numele magazinului și apelați meniul contextual. În ea, trebuie să debifați starea Subtotal: punct de vânzare. După cum putem vedea, subtotalurile rămân doar pentru date:
Este adesea necesar să sortați datele într-un tabel pivot pentru a le înțelege mai bine. Pentru a face acest lucru, pur și simplu selectați câmpul după care doriți să sortați, accesați fila General, într-un grup Editare faceți clic pe butonul Sortați și filtrațiși setați condițiile de sortare de care aveți nevoie.
O caracteristică foarte utilă pentru analiza informațiilor dintr-un tabel pivot este capacitatea de a grupa datele. De exemplu, trebuie să ne grupăm vânzările în funcție de săptămâna din lună. Pentru a face acest lucru, selectați datele care sunt incluse în prima săptămână (15.05-21.05):
Vă rugăm să rețineți că, pentru a ușura selecția, am restrâns datele pentru magazinele individuale utilizând butonul + din partea stângă a celulei cu numele magazinului.
Apoi, trebuie să rulați comanda Grupați după selecție grupuri grup file Opțiuni. În tabel va apărea o nouă coloană, în care câmpul Grupa 1 va îmbina câmpurile pe care le-am selectat.
Tot ce rămâne este să redenumiți numele grupului prin simpla editare a celulei:
Pentru a degrupa, utilizați doar comanda Degrupați din același grup, după selectarea câmpului de degrupat. Rețineți că nu puteți degrupa un câmp pe care l-am inclus în condiția de construire a tabelului pivot - de exemplu, câmpul Punct de vânzare sau data.
Să luăm în considerare un alt mod de a afișa date care ne va ajuta să analizăm informațiile din tabelul nostru. De exemplu, trebuie să cunoaștem volumul veniturilor nu în termeni monetari, ci ca procent din veniturile totale pentru întreaga perioadă de vânzări.
Pentru a face acest lucru, selectați orice celulă din coloana Venituri a tabelului nostru pivot. După aceea, trebuie să rulați comanda Opțiuni de câmpîntr-un grup câmp activ file Opțiuni.
În caseta de dialog care se deschide, accesați fila Calcule suplimentareși selectați din meniul drop-down Procent din total pe coloană. După ce faceți clic pe butonul OK, tabelul nostru va arăta astfel:
Dacă datele din tabel nu vor fi afișate ca procente, verificați setările pentru formatul numeric al celulelor (acest lucru se poate face imediat în caseta de dialog Opțiuni de câmp făcând clic pe butonul Format numeric, sau apelând fereastra corespunzătoare din meniul contextual).
Pentru a analiza liste de date (tabele de date) în Excel 2007 care au multe rânduri sau înregistrări, se folosesc adesea instrumente precum PivotTables. Tabelele pivot facilitează vizualizarea, manipularea și rezumarea datelor din listele Excel 2007.
Pentru ca Excel să lucreze cu datele introduse ca un tabel de date și nu ca un simplu set de date, acesta trebuie formatat ca un tabel. Pentru a face acest lucru, faceți clic pe butonul Tabel din fila Inserare. În caseta de dialog Creare tabel (Figura 1), specificați intervalul dorit în care va fi plasat tabelul și bifați caseta de validare Tabel cu anteturi.
Orez. unu.
Formatarea va fi aplicată acestui interval, de ex. se aplică stilul rapid al tabelului, setat implicit, în timp ce sunt activate instrumentele contextuale sub denumirea generală „Lucrul cu tabele”, care sunt incluse în fila contextuală Design. De asemenea, puteți utiliza instrumentul Format ca tabel din fila Acasă pentru a formata un tabel.
Orez. 2.
Pentru a construi un raport pe acest tabel, este recomandabil să utilizați instrumentul puternic „Pivot Table”. Pentru a aplica acest instrument la liste de date sau tabele de date, una dintre celulele tabelului de date trebuie să fie activată, de exemplu, celula din tabelul „Stoc în stoc”. Apoi faceți clic pe butonul „Pivot Table”, care se află în fila „Insert” din grupul „Table” (Figura 3).
Orez. 3.
În caseta de dialog Creare tabel pivot care se deschide, trebuie să selectați (evidențiați) un tabel sau un interval și să indicați unde doriți să plasați raportul (de preferință pe o foaie nouă), apoi faceți clic pe OK. Se va deschide un expert special pentru tabel pivot (Figura 4).
Orez. patru.
Imaginea raportului (PivotTable1) este afișată în partea stângă a foii de lucru, iar instrumentele pentru crearea unui tabel pivot sunt situate în partea dreaptă a foii: patru zone goale și o listă de câmpuri. Pentru a crea un raport, în partea dreaptă, trageți câmpurile necesare în zonele corespunzătoare ale tabelului pivot: „Filtru de raport”, „Nume coloane”, „Nume rânduri” și „Valori”.
De exemplu, dacă selectați câmpurile: Număr depozit, Nume, Preț (UAH) și le trageți în zonele corespunzătoare: „Nume coloane”, „Nume rânduri” și „Valori”, atunci acestea vor fi afișate în aceste zone pe partea dreapta. În acest caz, un tabel pivot sau un raport va fi construit în partea stângă a foii de lucru (Fig. 5).
Orez. 5.
De remarcat faptul că în zona „Valori” se efectuează unele calcule matematice, de exemplu, însumarea (câmpul Sumă prin preț). Pentru a schimba tipul de calcul, în zona „Valori”, faceți clic stânga pe câmpul „Câmpul Sumă după preț” și selectați comanda „Setări câmpuri Valoare” din meniul care se deschide, apoi în caseta de dialog „Setări câmpuri Valoare” , selectați funcția dorită și faceți clic pe butonul OK.
Pentru a schimba structura tabelului pivot, în partea dreaptă a foii, trageți câmpurile în altă zonă a tabelului pivot sau ștergeți-le. Trebuie remarcat faptul că pentru a șterge un câmp, trebuie să îl trageți în afara tabelului.
Instrumentele puternice pentru analiza datelor în Excel 2007 sunt:
Instrumentele de analiză ce se întâmplă dacă sunt plasate în fila Date din grupul Instrumente de date și Găsiți soluții în fila Date din grupul Analiză.
Selectarea parametrilor oferă o soluție la problema selecției unui parametru pentru o funcție a unui argument. Scenario Manager este conceput pentru a crea scenarii (mai întâi adăugați mai multe scenarii pentru cazuri diferite), apoi revizuiți scenariile pentru a prezice procesul și a genera un raport asupra scenariului.
Programul „Căutare soluții” este destinat rezolvării sistemelor complexe de ecuații, problemelor de optimizare liniare și neliniare. Add-in-ul Solver se bazează pe metode iterative.
Excel nu este doar un editor de foi de calcul, ci și un instrument puternic pentru diferite calcule matematice și statistice. Aplicația are un număr mare de funcții concepute pentru aceste sarcini. Cu toate acestea, nu toate aceste funcții sunt activate în mod implicit. Setul de instrumente aparține unor astfel de funcții ascunse. "Analiza datelor". Să aflăm cum poate fi activat.
Pentru a profita de funcțiile oferite de funcție "Analiza datelor", trebuie să activați grupul de instrumente „Pachet de analiză” urmând anumiți pași din Preferințe Microsoft Excel. Algoritmul acestor acțiuni este aproape același pentru versiunile 2010, 2013 și 2016 ale programului și are doar diferențe minore în versiunea 2007.
După parcurgerea acestor pași, funcția specificată va fi activată, iar setul de instrumente al acesteia va fi disponibil pe panglica Excel.
Acum putem lansa oricare dintre instrumentele din grup "Analiza datelor".
Lucrul în fiecare funcție are propriul algoritm de acțiuni. Folosind unele dintre instrumentele grupului "Analiza datelor" descrise în lecții separate.
După cum puteți vedea, deși cutia de instrumente „Pachet de analiză”și nu este activat implicit, procesul de activare este destul de simplu. În același timp, fără a cunoaște un algoritm clar de acțiuni, este puțin probabil ca utilizatorul să poată activa rapid această funcție statistică foarte utilă.
SARCINA 1
Analiza statistică a datelor în MS Excel
Scopul lucrării: să învețe cum să procesezi datele statistice folosind funcțiile încorporate ale MS Excel; explorați caracteristicile pachetului de analiză și instrumentele sale: " Generarea numerelor aleatorii», „Diagramă cu bare” , „ Statisticile descriptive" pe exemplul măsurătorilor vitezei de procesare.
În conformitate cu instrucțiunile pentru lucrările de laborator „Măsurarea vitezei vehiculelor” (la disciplina „Cercetarea și proiectarea drumurilor”), procesați datele de măsurare experimentală folosind metode de statistică matematică în programul Excel. Pentru ce:
1. Calculați caracteristicile statistice folosind funcțiile încorporate: - valoarea minimă a vitezei de deplasare Vmin;
Valoarea maximă a vitezei de deplasare Vmax; - valoarea medie a vitezei de deplasare Vav;
Abateri standard;
Abaterea standard a mediei Sp;
Coeficientul Student (pentru a determina intervalul de încredere) t; - interval de încredere pentru Р = 0,95.
2. Obțineți caracteristici statistice folosind instrumentul "Statisticile descriptive„din pachetul suplimentar” Analiza datelor”.
3. Construiți o histogramă a distribuției vitezei.
4. Construiți o curbă cumulativă (curba de frecvență cumulativă).
5. Construiți o curbă teoretică pentru distribuția vitezei de mișcare.
Pentru a obține o cantitate suficientă de date inițiale (rezultate de măsurare a vitezei), utilizați un experiment de simulare folosind „ Generarea numerelor aleatorii» suplimente „Analiza datelor”.
La efectuarea p.p. 3 și 4, selectați intervalul de viteză („buzunarul” - în terminologia Excel), care vă permite să obțineți cea mai simetrică histogramă care demonstrează legea distribuției normale.
Un exemplu de execuție este dat în fișierul atașat BasicsPC1-Student.xls.
Instrucțiuni
Să presupunem că am făcut o serie de 10 experimente, măsurând o anumită valoare X. Tabelul 1. Vedere aproximativă a foii „Procesarea experimentului”
Intrările din coloanele D și E sunt indicii care vă vor ajuta să vă dați seama ce caracteristici vom calcula. Coloana F ar trebui să fie goală pentru moment, formulele noastre vor fi plasate în ea.
Să începem procesarea rezultatelor calculând numărul de experimente n.
Pentru a determina numărul de valori, se folosește o funcție specială numită COUNT. Pentru a introduce o formulă cu funcții, utilizați Function Wizard, care este lansat de comanda „Insert Function” prin meniul „Insert” - „Function” sau butonul din bara de instrumente etichetat f x .
Să facem clic pe celula F6, unde ar trebui să fie rezultatul și să pornim Expertul pentru funcții.
Primul pas de lucru (figura 1) servește la selectarea funcției dorite.
Funcțiile statistice sunt utilizate pentru procesarea datelor experimentale. Prin urmare, în primul rând, în lista de categorii, selectați categoria „ Statistică”. În a doua fereastră apare o listă de funcții statistice.
Lista de funcții este sortată alfabetic, ceea ce facilitează găsirea funcției COUNT de care avem nevoie („Numărează numărul de numere din lista de argumente”).
Cu această funcție evidențiată, apăsați butonul Ok și treceți la pasul 2.
Al doilea pas (Figura 2) este folosit pentru a seta argumentele funcției.
Funcția COUNT trebuie să specifice ce numere trebuie să recalculeze sau în ce celule se află aceste numere. Următoarele două etape de procesare a unei serii de experimente se desfășoară în mod similar.
În celula F7, folosind funcția MEDIE, se calculează valoarea medie a probei, în celula F8, abaterea standard a probei, folosind funcția STDEV. .
Aceeași gamă de celule servește drept argumente pentru aceste funcții.
Pentru a calcula intervalul de încredere este necesar să se determine coeficientul Student. Depinde de probabilitatea de eroare (cu o fiabilitate dată în mod obișnuit de 95%, probabilitatea de eroare este de 5%) și de numărul de grade de libertate n-1 ).
Pentru a afla coeficientul Student se foloseste functia statistica Excel STUDISTRIP („Repartitia Studentului invers”). O caracteristică a acestei funcții este că primul argument, numărul 5% (sau 0,05) este introdus în fereastra corespunzătoare de la tastatură. Pentru al doilea, specificați adresa celulei în care se află valoarea n, apoi adăugați „-1” în fereastră. Primim intrarea „F6-1”.
Formula obișnuită de înmulțire este utilizată pentru a găsi intervalul de încredere. Desigur, în loc de litere, ar trebui să existe adrese de celule unde se află coeficientul Studentului și abaterea standard a mediei. De regulă, valoarea intervalului de încredere este rotunjită la o cifră semnificativă, aceeași ordine a mediului ar trebui să fie pentru medie. Prin urmare, rezultatul final poate fi scris astfel: cu o fiabilitate de 95%, X = 14,80 ± 0,05. În concluzie, calculăm eroarea relativă în determinarea X: = CI / X cf (formula: „=F11/F7”). Valoarea erorii relative este de obicei exprimată procentual, avem 0,3%.
Sarcinile 2 și 3 sunt efectuate cu ajutorul add-on-ului „Pachet de analiză” (din meniul Instrumente .Analiza datelor Histogramă).
Pentru a instala add-in-ul, apelați meniul Tools Add-ons și din lista propusă de suplimente disponibile pentru instalare, selectați „Analysis Package” (vezi Fig. Instalarea suplimentelor
Excel pe computer.doc).
