Lucrări practice în informatică Dna. Excela. OLAP ușor! Lucrări practice despre tehnologia olap în excel

Prima interfață pentru Tabelele Pivot, numită și Rapoarte Pivot, a fost inclusă Compoziția Excelîn 1993 (Excel versiunea 5.0). În ciuda numeroaselor utile funcţionalitate, practic nu este folosit în muncă de majoritatea utilizatorilor Excel. Chiar și utilizatorii experimentați înțeleg adesea prin termenul „raport rezumat” ceva construit folosind formule complexe. Să încercăm să popularizăm utilizarea tabelelor pivot în munca zilnică economiști. Articolul discută baza teoretică pentru crearea rapoartelor rezumative, oferă recomandări practice pentru utilizarea acestora și oferă, de asemenea, un exemplu de accesare a datelor bazate pe mai multe tabele.

Termenii de analiză multivariată a datelor

Majoritatea economiștilor au auzit termenii „date multidimensionale”, „cub virtual”, „tehnologii OLAP”, etc. Dar o conversație detaliată, de obicei, se dovedește că aproape toată lumea nu are idee despre ce vorbește. despre care vorbim. Adică, oamenii înseamnă ceva complex și, de obicei, care nu este relevant pentru activitățile lor zilnice. De fapt, acest lucru nu este adevărat.

Date multidimensionale, măsurători

Este sigur să spunem că economiștii întâlnesc aproape constant date multidimensionale, dar încearcă să le prezinte într-o formă predefinită folosind foi de calcul. Multidimensionalitatea înseamnă aici capacitatea de a introduce, vizualiza sau analiza aceleași informații cu modificări aspect, folosind diverse grupări și sortarea datelor. De exemplu, un plan de vânzări poate fi analizat folosind următoarele criterii:

• tipuri sau grupuri de bunuri;
• mărci sau categorii de produse;
• perioade (lună, trimestru, an);
• cumpărători sau grupuri de cumpărători;
• regiuni de vânzare
• etc.

Fiecare dintre criteriile de mai sus se numește „dimensiune” în ceea ce privește analiza multidimensională a datelor. Putem spune că o măsurătoare caracterizează informația în funcție de un set specific de valori. Un tip special de măsurare a informațiilor multidimensionale este „date”. În exemplul nostru, datele planului de vânzări ar putea fi:

• volumul vânzărilor;
• pretul de vanzare;
• reducere individuală
• etc.

În teorie, datele pot fi, de asemenea, o dimensiune standard a informațiilor multidimensionale (de exemplu, puteți grupa datele după prețul de vânzare), dar datele sunt, de obicei, încă un tip special de valoare.

Astfel, putem spune că în munca practică economiștii folosesc două tipuri de informații: date multidimensionale ( numere reale și planificate care au multe caracteristici) și cărți de referință (caracteristici sau măsurători de date).

OLAP

Abrevierea OLAP (procesare analitică online) în traducerea literală sună ca „ prelucrare analiticăîn timp real.” Definiția nu este foarte specifică aproape orice raport al oricărui produs software. În sensul său, OLAP presupune o tehnologie de lucru cu rapoarte speciale, inclusiv software, pentru obținerea și analiza datelor structurate multidimensionale. Unul dintre produsele software populare care implementează tehnologiile OLAP este SQL Server Server de analiză. Unii chiar îl consideră în mod eronat singurul reprezentant al implementării software a acestui concept.

Cub de date virtual

„Cub virtual” (cub multidimensional, cub OLAP) este un termen special propus de unii furnizori de software specializati. Sistemele OLAP de obicei pregătesc și stochează datele în propriile structuri, iar interfețele speciale de analiză (cum ar fi rapoartele rezumative Excel) accesează datele din aceste cuburi virtuale. Mai mult, utilizarea unei astfel de stocări dedicate nu este deloc necesară pentru procesarea informațiilor multidimensionale. In general, cub virtual– aceasta este o serie de date multidimensionale special optimizate care sunt utilizate pentru a crea rapoarte rezumative. Poate fi obținut fie prin software specializat, fie prin simplu acces la tabelele bazei de date sau orice altă sursă, cum ar fi o foaie de calcul Excel.

Tabel pivot

Pivot Table este o interfață cu utilizatorul pentru afișarea datelor multidimensionale. Folosind această interfață, puteți grupa, sorta, filtra și rearanja datele pentru a obține diferite mostre analitice. Actualizarea raportului se face prin mijloace simple interfata utilizator, datele sunt agregate automat conform regulilor specificate, fără a necesita introducerea suplimentară sau repetată a vreunei informații. Interfață rezumat tabele Excel este poate cel mai popular produs software pentru lucrul cu date multidimensionale. Acceptă atât surse de date externe (cuburi OLAP și baze de date relaționale), cât și intervale de foi de calcul interne ca sursă de date. Începând cu versiunea 2000 (9.0), Excel acceptă și o formă grafică de afișare a datelor multidimensionale - o diagramă pivot.

Interfața Excel PivotTable vă permite să aranjați dimensiunile datelor multidimensionale într-o zonă a foii de lucru. Pentru simplitate, vă puteți gândi la un tabel pivot ca la un raport situat deasupra unui interval de celule (de fapt, există o anumită legare a formatelor de celule de câmpurile tabelului pivot). Un tabel pivot Excel are patru zone pentru a afișa informații: Filtru, Coloane, Rânduri și Date. Dimensiunile datelor sunt numite Câmpuri din tabel pivot. Aceste câmpuri au propriile proprietăți și format de afișare.

Încă o dată, aș dori să vă atrag atenția asupra faptului că tabelul pivot Excel este destinat exclusiv analizei datelor fără posibilitatea de a edita informațiile. Un sens mai apropiat ar fi utilizarea pe scară largă a termenului „raport pivot”, și exact așa a fost numită această interfață până în 2000. Dar din anumite motive, în versiunile ulterioare dezvoltatorii l-au abandonat.

Editarea tabelelor pivot

Prin definiția sa, tehnologia OLAP, în principiu, nu implică capacitatea de a schimba datele sursă atunci când lucrezi cu rapoarte. Cu toate acestea, pe piață s-a format o întreagă clasă sisteme software, implementând capabilitățile atât de analiză, cât și de editare directă a datelor în tabele multidimensionale. Practic, astfel de sisteme sunt axate pe rezolvarea problemelor de bugetare.

Folosind instrumentele de automatizare încorporate ale Excel, puteți rezolva multe probleme nestandard. Un exemplu de implementare de editare pentru tabelele pivot Excel pe baza datelor din foile de lucru poate fi găsit pe site-ul nostru web.

Pregătirea datelor multidimensionale

Să venim la aplicare practică Tabelele pivot. Să încercăm să analizăm datele vânzărilor în diverse direcții. Fişier pivottableexample.xls este format din mai multe foi. foaie Exemplu conține informații de bază despre vânzări pentru o anumită perioadă. Pentru a simplifica exemplul, vom analiza un singur indicator numeric – volumul vânzărilor în kg. Dimensiunile cheie ale datelor sunt: ​​produs, cumpărător și transportator (companie de transport). În plus, există mai multe dimensiuni de date suplimentare care sunt caracteristici ale produsului: tip, marcă, categorie, furnizor și client: tip. Aceste date sunt colectate pe foaia Directoare. În practică, pot exista mult mai multe astfel de măsurători.

foaie Exemplu conţine remediu standard analiza datelor – autofiltru. Privind exemplul de completare a tabelului, este evident că datele vânzărilor după dată (sunt aranjate în coloane) se pretează unei analize normale. În plus, folosind un filtru automat, puteți încerca să rezumați datele pe baza combinațiilor de unul sau mai multe criterii cheie. Nu există absolut nicio informație despre mărci, categorii și tipuri. Nu există nicio modalitate de a grupa datele cu însumare automată după o anumită cheie (de exemplu, de către clienți). În plus, setul de date este fix și nu va fi posibilă vizualizarea informațiilor rezumative pentru o anumită perioadă, de exemplu, 3 zile, folosind mijloace automate.

În general, având o locație de dată predefinită în în acest exemplu – dezavantajul principal mesele. Prin aranjarea datelor pe coloane, am predeterminat dimensiunea acestui tabel, privându-ne astfel de posibilitatea de a folosi analiza folosind tabele pivot.

În primul rând, trebuie să scăpăm de acest neajuns - de exemplu. eliminați locația predefinită a unuia dintre dimensiunile datelor sursă. Exemplu de tabel corect - foaie Vânzări.

Tabelul are forma unui jurnal de introducere a informațiilor. Aici, data este o dimensiune egală a datelor. De asemenea, trebuie remarcat faptul că pentru analiza ulterioară în tabelele pivot, poziția relativă a rândurilor unul față de celălalt (cu alte cuvinte, sortarea) este complet indiferentă. Înregistrări în baze de date relaționale date. Interfața tabelelor pivot vizează în primul rând analiza unor volume mari de baze de date. Prin urmare, trebuie să respectați aceste reguli atunci când lucrați cu o sursă de date sub formă de intervale de celule. În același timp, nimeni nu interzice utilizarea instrumentelor de interfață Excel în munca lor - tabelele pivot analizează numai datele, iar formatarea, filtrele, grupările și sortarea celulelor sursă pot fi arbitrare.

De la filtru automat la raport rezumat

Teoretic, este deja posibil să se efectueze analize în trei dimensiuni folosind datele din Fișa de vânzări: mărfuri, clienți și transportatori. Nu există date despre proprietățile produselor și ale clienților pe această fișă, ceea ce, în consecință, nu va permite ca acestea să fie afișate în tabelul rezumativ. În modul normal de creare a unui tabel pivot pentru sursă date Excel nu vă permite să legați date din mai multe tabele prin anumite domenii. Puteți ocoli această limitare software– vezi exemplul de supliment la acest articol pe site-ul nostru. Pentru a nu recurge la metode software procesarea informațiilor (mai ales că nu sunt universale), ar trebui să adăugați caracteristici suplimentare direct în formularul de intrare în jurnal - consultați fișa SalesAnalysis.

Utilizarea funcțiilor VLOOKUP facilitează completarea datelor originale cu caracteristici lipsă. Acum, folosind AutoFilter, puteți analiza datele în dimensiuni diferite. Dar rămâne problema nerezolvata grupuri. De exemplu, urmărirea sumei numai după marcă pentru anumite date este destul de problematică. Dacă vă limitați la formule Excel, atunci trebuie să creați selecții suplimentare folosind funcția SUMIF.

Acum să vedem ce capabilități oferă interfața tabelului pivot. Pe o foaie RezumatAnaliză a construit mai multe rapoarte bazate pe o serie de celule cu date din foi Analiza vânzărilor.

Primul tabel de analiză a fost construit prin interfața Excel 2007 Ribbon\Insert\PivotTable(în meniul Excel 2000-2003 Date\PivotTable).

Al doilea și al treilea tabel au fost create prin copiere și configurare ulterioară. Sursa de date pentru toate tabelele este aceeași. Puteți verifica acest lucru modificând datele sursă, apoi trebuie să actualizați datele raportului rezumat.

Din punctul nostru de vedere, avantajele în vizibilitatea informațiilor sunt evidente. Puteți schimba filtre, coloane și rânduri și puteți ascunde anumite grupuri de valori de orice dimensiune, utilizați manual drag and drop și sortarea automată.

Proprietăți și formatare

Pe lângă afișarea directă a datelor, există o gamă largă de opțiuni pentru afișarea aspectului tabelelor pivot. Puteți ascunde datele inutile folosind filtre. Pentru un singur element sau câmp este mai ușor să utilizați elementul din meniul contextual Şterge(în versiunea 2000-2003 Ascunde).

De asemenea, este recomandabil să setați afișarea altor elemente ale tabelului pivot nu prin formatarea celulelor, ci prin setarea unui câmp sau element al tabelului pivot. Pentru a face acest lucru, trebuie să mutați indicatorul mouse-ului la elementul dorit, să așteptați să apară o formă specială de cursor (sub forma unei săgeți), apoi să selectați elementul selectat cu un singur clic. După selecție, puteți schimba vizualizarea prin panglică, meniul contextual sau puteți apela dialogul format standard de celulă:

În plus, Excel 2007 a introdus multe stiluri de afișare PivotTable predefinite:

Observați că filtrele de control și zonele de glisare sunt active în diagramă.

Acces la date externe

După cum sa menționat deja, poate cel mai mare efect din utilizarea tabelelor pivot poate fi obținut la accesarea datelor din surse externe - cuburi OLAP și interogări de baze de date. Astfel de surse stochează de obicei cantități mari de informații și au, de asemenea, o structură relațională predefinită, ceea ce face ușoară definirea dimensiunilor datelor multidimensionale (câmpuri de tabel pivot).

Excel acceptă multe tipuri de surse de date externe:

Cel mai mare efect din utilizarea surselor externe de informații poate fi obținut prin utilizarea instrumentelor de automatizare (programe VBA) atât pentru obținerea de date, cât și pentru pretratamentîn tabelele pivot.

Lucrul cu cubul OLAP în MS Excel

1. Obțineți permisiunea de a accesa cubul OLAP SQL Server Analysis Services (SSAS).
2. MS Excel 2016 / 2013 / 2010 trebuie instalat pe computer (MS Excel 2007 este, de asemenea, posibil, dar nu este convenabil să lucrezi, iar funcționalitatea MS Excel 2003 este foarte slabă)
3. Deschideți MS Excel și rulați expertul pentru configurarea unei conexiuni la serviciul analitic:

3.1 Specificați numele sau adresa IP a serverului OLAP curent (uneori trebuie să specificați numărul portului deschis, de exemplu, 192.25.25.102:80); se utilizează autentificarea domeniului:

3.2 Selectați o bază de date multidimensională și un cub analitic (dacă aveți drepturi de acces la cub):

3.3 Setările pentru conectarea la serviciul analitic vor fi salvate într-un fișier .odc pe computer:

3.4 Selectați tipul de raport (tabel pivot/grafic) și indicați locația pentru plasarea acestuia:

Dacă o conexiune a fost deja creată într-un registru de lucru Excel, o puteți utiliza din nou: meniul principal „Date” -> „Conexiuni existente” -> selectați o conexiune în acest registru de lucru -> inserați un tabel pivot în celula specificată.

4. După conectarea cu succes la cub, puteți începe analiza interactivă a datelor:

Când începeți analiza interactivă a datelor, trebuie să determinați ce câmpuri vor participa la formarea rândurilor, coloanelor și filtrelor (paginilor) din tabelul pivot. În general, un tabel pivot este tridimensional și putem considera că a treia dimensiune este perpendiculară pe ecran și vedem secțiuni paralele cu planul ecranului și determinate de ce „pagină” este selectată pentru afișare. Filtrarea se poate face prin tragerea atributelor dimensiunilor corespunzătoare în zona de filtrare a raportului. Filtrarea limitează spațiul cubului, reducând încărcarea pe serverul OLAP, deci Este de preferat să instalați mai întâi filtrele necesare. Apoi plasați atribute de dimensiune în rândurile, coloanele și măsurile din zona de date PivotTable.

De fiecare dată când tabelul pivot se modifică, o instrucțiune MDX este trimisă automat către serverul OLAP, iar atunci când este executată, datele sunt returnate. Cu cât volumul datelor prelucrate și al indicatorilor calculați este mai mare și mai complex, cu atât timpul de executare a cererii este mai lung. Puteți anula executarea unei cereri apăsând tasta Evadare. Ultimele operații efectuate pot fi anulate (Ctrl+Z) sau revenite (Ctrl+Y).

De obicei, pentru cele mai utilizate combinații de atribute de dimensiune, cubul stochează date agregate precalculate, astfel încât timpul de răspuns al unor astfel de interogări este de câteva secunde. Cu toate acestea, este imposibil să se calculeze fiecare combinație posibilă de agregare, deoarece aceasta poate necesita mult timp și spațiu de stocare. Executarea de interogări masive împotriva datelor granulare poate necesita resurse semnificative de procesare a serverului și poate dura mult timp pentru a finaliza. După citirea datelor de pe unitățile de disc, serverul le plasează în memoria cache-ului RAM, ceea ce permite ca astfel de solicitări ulterioare să fie finalizate instantaneu, deoarece datele vor fi preluate din cache.

În cazul în care considerați că solicitarea dvs. va fi utilizată frecvent și timpul de execuție a acesteia este nesatisfăcător, puteți contacta serviciul de asistență pentru dezvoltare analitică pentru a optimiza executarea cererii.

După plasarea ierarhiei în zona de rânduri/coloane, este posibilă ascunderea nivelurilor individuale:

La atributele cheie(mai rar - pentru atributele mai înalte în ierarhie) dimensiunile pot avea proprietăți - caracteristici descriptive care pot fi afișate atât în ​​sfaturi cu instrumente, cât și ca câmpuri:

Dacă trebuie să afișați mai multe proprietăți de câmp simultan, puteți utiliza lista de dialog corespunzătoare:

Seturi definite de utilizator

Excel 2010 a introdus capacitatea de a crea interactiv propriile seturi (definite de utilizator) de membri ai dimensiunii:

Spre deosebire de seturile create și stocate central pe partea cubului, seturile personalizate sunt salvate local în registrul de lucru Excel și pot fi utilizate ulterior:

Utilizatorii avansați pot crea seturi folosind constructe MDX:

Setarea proprietăților tabelului pivot

Folosind elementul „Opțiuni tabel pivot...” din meniul contextual (clic dreapta în tabelul pivot), puteți configura tabelul pivot, de exemplu:
- Fila „Ieșire”, opțiunea „Aspect tabel pivot clasic” - tabelul pivot devine interactiv, puteți trage câmpuri (Drag&Drop);
- Fila „Ieșire”, opțiunea „Afișare elemente fără date în rânduri” - tabelul pivot va afișa rânduri goale care nu conțin o singură valoare de indicator pentru elementele de dimensiune corespunzătoare;
- Fila „Layout and Format”, opțiunea „Păstrați formatarea celulei la actualizare” - în tabelul pivot puteți suprascrie și salva formatul celulei la actualizarea datelor;

Creați diagrame pivot

Pentru un tabel pivot OLAP existent, puteți crea o diagramă pivot - plăcintă, bară, histogramă, grafic, scatter și alte tipuri de diagrame:

În acest caz, diagrama pivot va fi sincronizată cu tabelul pivot - dacă compoziția indicatorilor, filtrelor sau dimensiunilor din tabelul pivot se modifică, tabelul pivot este și el actualizat.

Crearea tablourilor de bord

Selectați tabelul pivot original, copiați-l în clipboard (Ctrl+C) și lipiți o copie a acestuia (Ctrl+V), în care schimbăm compoziția indicatorilor:

Pentru a gestiona simultan mai multe tabele pivot, vom introduce un slicer (o nouă funcționalitate disponibilă începând cu MS Excel 2010). Să conectăm Slicer-ul nostru la tabelele pivot - faceți clic dreapta în secțiune și selectați „Conexiuni la un tabel pivot...” în meniul contextual. Trebuie remarcat faptul că pot exista mai multe panouri de tăiere care pot servi simultan tabele pivot pe diferite foi, permițându-vă să creați un tablou de bord coordonat.

Panourile Slicer pot fi personalizate: trebuie să selectați panoul, apoi să vedeți elementele „Dimensiune și proprietăți...”, „Setări pentru felii”, „Atribuiți macro” în meniul contextual activat făcând clic dreapta pe mouse sau pe „Opțiuni”. " element din meniul principal. Astfel, este posibil să setați numărul de coloane pentru elementele slice (butoane), dimensiunile butoanelor slice și panoului, definiți schema de culori și stilul de design pentru slice din setul existent (sau creați propriul stil), definiți-vă propriul titlu de panou, atribuiți o macrocomandă de program prin care puteți extinde funcționalitatea panoului.

Executarea unei interogări MDX din Excel

1. În primul rând, trebuie să efectuați operația DRILLTHROUGH pe un indicator, de exemplu. coborâți la datele detaliate (datele detaliate sunt afișate pe o foaie separată) și deschideți lista de conexiuni;
2. Deschideți proprietățile conexiunii, accesați fila „Definiție”;
3. Selectați tipul de comandă implicit și plasați o comandă pregătită în prealabil în câmpul de text al comenzii MDX cerere;
4. Când faceți clic pe butonul, după verificarea corectitudinii sintaxei cererii și a disponibilității drepturilor de acces corespunzătoare, cererea va fi executată pe server, iar rezultatul va fi prezentat în foaia curentă sub forma unui tabel plat obișnuit.
   Puteți vizualiza textul interogării MDX generată de Excel instalând un add-on gratuit, care oferă și alte funcționalități suplimentare.

Traducerea în alte limbi

Cubul analitic acceptă localizarea în rusă și engleză (dacă este necesar, este posibilă localizarea în alte limbi). Traducerile se aplică numelor dimensiunilor, ierarhiilor, atributelor, folderelor, măsurilor, precum și elementelor ierarhiilor individuale, dacă sunt disponibile traduceri pentru acestea pe partea sistemelor contabile/depozitului de date. Pentru a schimba limba, trebuie să deschideți proprietățile conexiunii și să adăugați următoarea opțiune în linia de conectare:
Proprietăți extinse="Locale=1033"
unde 1033 este localizarea în engleză
1049 - localizare în rusă

Extensii suplimentare Excel pentru Microsoft OLAP

Capacitatea de a lucra cu cuburi Microsoft OLAP va crește dacă utilizați extensii suplimentare, de exemplu, extensii OLAP PivotTable, datorită cărora puteți utiliza o căutare rapidă după dimensiune:

site-ul web 11-01-2011 16:57:00Z Ultima modificare: 2017-10-15 16:33:59Z Public de vârstă: 14-70

Procesarea analitică online (OLAP) este o tehnologie care este utilizată pentru a organiza baze de date mari de afaceri și pentru a sprijini business intelligence. Bazele de date OLAP sunt împărțite în unul sau mai multe cuburi, iar fiecare cub este organizat de administratorul cubului pentru a se potrivi cu modul în care datele sunt preluate și analizate pentru a facilita crearea și utilizarea rapoartelor PivotTable și a rapoartelor PivotChart de care veți avea nevoie.

În acest articol

Ce este analiza de afaceri?

Un analist de afaceri dorește adesea să obțină o imagine mai amplă a afacerii pentru a vedea tendințele mai ample bazate pe date agregate, precum și pentru a vedea tendințele defalcate în orice număr de variabile. Business Intelligence este procesul de extragere a datelor dintr-o bază de date OLAP și de analizare a acestor date pentru a produce informații care pot fi utilizate pentru a lua decizii de afaceri informate și pentru a lua măsuri. De exemplu, folosind OLAP și analiza de afaceri, puteți răspunde la următoarele întrebări despre datele de afaceri.

Cum se compară vânzările totale ale tuturor produselor în 2007 cu vânzările din 2006?

Cum se compară acest lucru cu data și ora după perioada de beneficii din ultimii cinci ani?

Câți bani au cheltuit clienții pe 35 in anul trecutși cum s-a schimbat acest comportament în timp?

Câte produse au fost vândute în două țări/regiuni specifice luna aceasta, față de aceeași lună a anului trecut?

Pentru fiecare grupă de vârstă a clienților, care este defalcarea profitabilității (atât procentul marjei, cât și totalul) pe categorii de produse?

Căutați vânzători de top și de jos, distribuitori, furnizori, clienți, parteneri și clienți.

Ce este procesarea analitică online (OLAP)?

Bazele de date OLAP (Online Analytical Processing) simplifică interogările de business intelligence. OLAP este o tehnologie de baze de date optimizată pentru interogări și rapoarte, mai degrabă decât pentru procesarea tranzacțiilor. Sursa de date pentru OLAP este bazele de date de procesare a tranzacțiilor online (OLTP), care sunt de obicei stocate în depozite de date. Datele OLAP sunt extrase din aceste date istorice și combinate în structuri care permit analize complexe. Datele OLAP sunt, de asemenea, organizate ierarhic și stocate mai degrabă în cuburi decât în ​​tabele. Este o tehnologie complexă care utilizează structuri multidimensionale pentru a oferi acces rapid la date pentru analiză. În această organizație, un raport PivotTable sau un raport PivotChart poate afișa cu ușurință date rezumative la nivel înalt, cum ar fi totalul vânzărilor pentru o întreagă țară sau regiune și, de asemenea, poate afișa informații despre site-urile unde vânzările sunt deosebit de puternice sau slabe.

Bazele de date OLAP sunt concepute pentru a accelera încărcarea datelor. Din moment ce serverul OLAP, și nu Microsoft Office Excel calculează valorile agregate atunci când creați sau editați un raport, trebuie să trimiteți date mai mici la Excel. Această abordare vă permite să lucrați cu mai multe date brute decât dacă datele ar fi organizate într-o bază de date tradițională, unde Excel preia toate înregistrările individuale și calculează valorile agregate.

Bazele de date OLAP conțin două tipuri principale de date: măsuri, care sunt date numerice, cantități și medii care sunt utilizate pentru a lua decizii de afaceri informate și dimensiuni, care sunt categorii utilizate pentru a organiza acele măsuri. Bazele de date OLAP vă ajută să organizați datele prin mai multe niveluri de detaliu, folosind aceleași categorii pe care le cunoașteți pentru a analiza datele.

Următoarele secțiuni descriu fiecare componentă în detaliu mai jos.

Cub O structură de date care grupează măsurile în niveluri și ierarhii ale fiecărei dimensiuni pe care doriți să o analizați. Cuburile combină mai multe dimensiuni, cum ar fi timpul, geografia și liniile de produse, cu date rezumative, cum ar fi vânzările și inventarul. Cuburile nu sunt „Cuburi” în sensul strict matematic, deoarece nu au neapărat aceleași laturi. Cu toate acestea, ele reprezintă o metaforă potrivită pentru un concept complex.

Măsurătorile Un set de valori dintr-un cub care se bazează pe o coloană din tabelul de fapte al cubului și care este de obicei o valoare numerică. Măsurile sunt valorile centrale dintr-un cub care sunt preprocesate, procesate și analizate. Cele mai comune exemple sunt vânzările, veniturile, veniturile și cheltuielile.

Membru Un element dintr-o ierarhie care reprezintă una sau mai multe apariții de date. Un element poate fi fie unic, fie neunic. De exemplu, 2007 și 2008 reprezintă membri unici la nivelul anului unei dimensiuni de timp, în timp ce ianuarie reprezintă membri neunici la nivel de lună, deoarece există mai mult de un ianuarie într-o dimensiune de timp, deoarece conține date pentru mai mult de un an.

Element calculat Un membru de dimensiune a cărui valoare este calculată în timpul executării utilizând o expresie. Valorile membrilor calculati pot fi derivate din valorile altor membri. De exemplu, un element calculat, profitul, poate fi determinat scăzând valoarea elementului, plus costurile, din valoarea elementului, vânzări.

măsurare Un set de una sau mai multe ierarhii ordonate de niveluri Cube pe care utilizatorul le înțelege și le folosește ca bază pentru analiza datelor. De exemplu, o dimensiune geografică poate include niveluri de țară/regiune, stat/regiune și oraș. În plus, o dimensiune de timp poate include o ierarhie cu niveluri de an, trimestru, lună și zi. Într-un raport PivotTable sau un raport PivotChart, fiecare ierarhie devine un set de câmpuri pe care le puteți extinde și restrânge pentru a le afișa mai jos sau mai sus niveluri înalte.

Ierarhie O structură arborescentă logică care aranjează membrii unei dimensiuni astfel încât fiecare membru să aibă un membru părinte și zero sau mai mulți copii. Un copil este membru al unui grup anterior din ierarhie care este direct legat de membrul curent. De exemplu, într-o ierarhie de timp care conține nivelurile trimestrul, luna și ziua, ianuarie este un copil al lui Qtr1. Elementul părinte este membru al mai multor nivel scăzutîn ierarhie, direct legată de membrul curent. Valoarea părinte este de obicei consolidarea valorilor tuturor elementelor copil. De exemplu, într-o ierarhie de timp care conține nivelurile trimestrul, luna și ziua, Qtr1 este părintele lunii ianuarie.

NivelÎntr-o ierarhie, datele pot fi organizate în niveluri mai mici și mai mari de granularitate, cum ar fi ani, trimestre, luni și niveluri de zi într-o ierarhie de timp.

Funcții OLAP în Excel

Preluarea datelor OLAP Vă puteți conecta la surse de date OLAP la fel cum vă conectați la alte surse de date externe. Puteți lucra cu baze de date create folosind servicii Microsoft SQL Server OLAP versiunea 7.0, Microsoft SQL Server Analysis Services versiunea 2000 și Microsoft SQL Server Analysis Services versiunea 2005, server produse Microsoft OLAP Excel poate funcționa și cu produse OLAP terțe care sunt compatibile cu OLE-DB pentru OLAP.

Datele OLAP pot fi afișate numai ca raport PivotTable sau raport PivotChart sau într-o funcție de foaie de lucru convertită dintr-un raport PivotTable, dar nu ca un interval de date extern. Puteți salva rapoartele OLAP PivotTable și PivotChart în șabloane de rapoarte și puteți crea fișiere de conexiune la Date de birou(ODC) pentru a se conecta la bazele de date OLAP pentru interogări OLAP. Când deschideți un fișier ODC în Excel, vedeți un raport PivotTable gol, gata de plasat.

Crearea de fișiere cub pentru utilizare offline Puteți crea un fișier cub autonom (.cub) cu un subset de date din baza de date a serverului OLAP. Fișierele cub offline sunt folosite pentru a lucra cu date OLAP atunci când nu sunteți conectat la o rețea. Cu un cub, puteți lucra cu mai multe date dintr-un raport PivotTable sau un raport PivotChart decât ați face altfel și să obțineți datele mai rapid. Puteți crea fișiere cub numai dacă utilizați un furnizor OLAP, cum ar fi Microsoft SQL Analysis Services versiunea 2005, care acceptă această caracteristică.

Acțiuni de server O acțiune de server este o funcție opțională pe care un administrator de cub OLAP o poate defini pe un server care utilizează un element cub sau o măsură ca parametru într-o interogare pentru a prelua informații din cub sau pentru a lansa o altă aplicație, cum ar fi un browser. Excel acceptă adrese URL, rapoarte, seturi de rânduri, detaliere și detaliere pe partea de server, dar nu acceptă propriul set de date și declarații native.

KPI Un indicator cheie de performanță este o măsură specială calculată, definită pe server, care vă permite să urmăriți „indicatorii cheie de performanță”, inclusiv starea (valoarea curentă corespunde cu număr specific). și tendință (valori în timp). Când sunt afișate, serverul poate trimite pictograme corespunzătoare, similare cu noua pictogramă Excel, pentru a se alinia deasupra sau sub nivelurile de stare (de exemplu, pentru o pictogramă de oprire), precum și să rotească o valoare în sus sau în jos (de exemplu, o pictogramă săgeată direcțională).

Formatare pe server Administratorii cubului pot crea măsuri și membri calculați folosind formatarea culorilor, formatarea fontului și regulile de formatare condiționată care pot fi atribuite ca regulă de afaceri standard pentru întreprindere. De exemplu, formatul de server pentru venituri poate fi un format de număr de monedă, culoarea celulei este verde dacă valoarea este mai mare sau egală cu 30.000 și roșu dacă valoarea este mai mică de 30.000, iar stilul fontului este aldine dacă valoarea este mai mica sau egala cu 30.000 si daca valoarea este pozitiva – obisnuita. mai mare sau egal cu 30.000 Pentru mai multe informații, consultați .

Limba interfeței Office Administratorul cubului poate defini traduceri pentru date și erori de pe server pentru utilizatorii care trebuie să vadă informațiile din PivotTable într-o altă limbă. Această funcție este definită ca o proprietate de conexiune la fișier, iar localitatea și țara computerului utilizatorului trebuie să se potrivească cu limba interfeței.

Componentele software necesare pentru a accesa sursele de date OLAP

Furnizor OLAP Pentru a configura sursele de date OLAP pentru Excel, aveți nevoie de unul dintre următorii furnizori OLAP.

Furnizor Microsoft OLAP Excel include un driver de sursă de date și un software client pentru accesarea bazelor de date create cu serviciile Microsoft SQL Server olap versiunea 7.0, Microsoft SQL Server olap versiunea 2000 (8.0) și serviciile Microsoft SQL Server Analysis versiunea 2005 (9,0).

Furnizori OLAP terți Pentru alte produse OLAP trebuie să instalați drivere suplimentareși software-ul client. A folosi Capabilitati Excel Pentru a lucra cu datele OLAP, produsul terță parte trebuie să respecte standardul OLE-DB pentru OLAP și să fie compatibil cu Microsoft Office. Pentru informații despre instalarea și utilizarea unui furnizor OLAP terță parte, consultați administrator de sistem sau furnizorul dvs. de produse OLAP.

Baze de date server și fișiere cub Software-ul client Excel OLAP acceptă conexiuni la două tipuri de baze de date OLAP. Dacă baza de date de pe serverul OLAP este online, puteți prelua datele sursă direct de pe acesta. Dacă aveți un fișier cub autonom care conține date OLAP sau un fișier de definire a cubului, vă puteți conecta la acel fișier și puteți obține date sursă din acesta.

Surse de date O sursă de date oferă acces la toate datele dintr-o bază de date OLAP sau un fișier cub offline. Odată ce creați o sursă de date OLAP, puteți baza rapoarte pe aceasta și puteți returna datele OLAP în Excel ca raport PivotTable sau raport PivotChart sau într-o funcție de foaie de lucru convertită dintr-un raport PivotTable.

Microsoft Query Folosind Query puteți obține date de la bază externă date, cum ar fi Microsoft SQL sau Microsoft Access. Nu trebuie să utilizați o interogare pentru a prelua date dintr-un tabel pivot OLAP care este asociat cu un fișier cub. Informații suplimentare.

Diferențele între caracteristicile de date sursă OLAP și non-OLAP

Dacă lucrați cu rapoarte PivotTable și PivotCharts din date sursă OLAP și alte tipuri de date sursă, veți vedea unele diferențe de caracteristici.

Extragerea datelor Serverul OLAP returnează date noi în Excel ori de câte ori aspectul raportului se modifică. Cu alte tipuri de surse de date externe, interogați toate datele sursă simultan sau puteți specifica parametrii de interogați numai atunci când afișați diferite elemente de câmp de filtru de raport. În plus, aveți câteva alte opțiuni pentru actualizarea raportului.

În rapoartele bazate pe date sursă OLAP, opțiunile câmpului de filtru de raport nu sunt disponibile, interogarea de fundal nu este disponibilă și opțiunea de optimizare a memoriei nu este disponibilă.

Nota: De asemenea, opțiunea de optimizare a memoriei nu este disponibilă pentru sursele de date OLEDB și rapoartele PivotTable bazate pe o serie de celule.

Tipuri de câmpuri Date sursă OLAP. câmpurile de dimensiuni pot fi folosite numai ca rânduri (rânduri), coloane (categorie) sau câmpuri de pagină. Câmpurile de măsură pot fi folosite doar ca câmpuri de valoare. Pentru alte tipuri de date sursă, toate câmpurile pot fi utilizate în orice parte a raportului.

Acces la date detaliate Pentru datele sursă OLAP, serverul determină nivelurile disponibile forează și calculează valorile rezumative, astfel încât înregistrările detaliate care alcătuiesc valorile rezumate pot să nu fie disponibile. Cu toate acestea, serverul poate furniza câmpuri de proprietate pe care le puteți afișa. Alte tipuri de date sursă nu au câmpuri de proprietate, dar puteți afișa informații de bază pentru câmpurile de date și valorile elementelor și, de asemenea, puteți afișa elemente fără date.

Este posibil ca câmpurile de filtrare a raportului OLAP să nu aibă Toate elemente și echipa Afișați paginile de filtrare a raportului nu este disponibil.

Ordinea de sortare inițială Pentru datele sursă OLAP, elementele sunt afișate mai întâi în ordinea în care sunt returnate de serverul OLAP. Puteți sorta sau reordona manual articolele. Pentru alte tipuri de date sursă, elementele noului raport sunt mai întâi sortate după numele elementului, în ordine crescătoare.

Nimi Serverele OLAP furnizează valori rezumative direct raportului, astfel încât nu puteți modifica funcțiile de rezumat pentru câmpurile de valoare. Pentru alte tipuri de date sursă, puteți modifica funcția de agregare pentru un câmp de valoare și puteți utiliza mai multe funcții de rezumat pentru același câmp de valoare. Nu puteți crea câmpuri calculate și membri calculați în rapoarte cu date sursă OLAP.

SubtotaluriÎn rapoartele cu date sursă OLAP, nu puteți modifica funcția de rezumat pentru subtotaluri. Cu alte tipuri de date sursă, puteți modifica funcțiile totale pentru subtotaluri și puteți afișa sau ascunde subtotalurile pentru toate câmpurile rând și coloană.

Pentru datele sursă OLAP, puteți include sau exclude membri ascunși atunci când calculați subtotalurile și totalurile generale. Pentru alte tipuri de date sursă, puteți include elemente ascunse ale câmpurilor de filtrare a raportului în subtotaluri, dar elementele ascunse din alte câmpuri vor fi excluse în mod implicit.

Selectați un document din arhivă pentru a vizualiza:

18,5 KB mașini.xls

14 KBţări.xls

Excel pr.r. 1.docx

Bibliotecă
materiale

Lucrări practice 1

„Scopul și interfața MS Excel”

După finalizarea sarcinilor din acest subiect,:

1. Învață să rulezi foi de calcul;

2. Consolidarea conceptelor de bază: celulă, rând, coloană, adresa celulei;

3. Aflați cum să introduceți date într-o celulă și să editați bara de formule;

5. Cum se selectează rânduri întregi, o coloană, mai multe celule situate una lângă alta și întregul tabel.

Exercita: Familiarizați-vă cu elementele de bază ale ferestrei MS Excel.

Rulați programul Microsoft Excel. Aruncă o privire atentă la fereastra programului.

Documentele care sunt create folosindEXCELA , sunt numitecarnete de lucru și au o extensie. XLS. Noul registru de lucru are trei foi de lucru numite SHEET1, SHEET2 și SHEET3. Aceste nume sunt situate pe etichetele foilor din partea de jos a ecranului. Pentru a trece la o altă foaie, faceți clic pe numele acelei foi.

Acțiuni cu foi de lucru:

Redenumiți o foaie de lucru. Plasați indicatorul mouse-ului pe coloana foii de lucru și faceți dublu clic pe tasta din stânga sau apelați meniul contextual și selectați comanda Redenumire.Setați numele foii la „TRAINING”

Inserarea unei foi de lucru . Selectați fila „Foaie 2” înaintea căreia doriți să introduceți o nouă foaie și utilizând meniul contextualintroduceți o foaie nouă și dați numele „Probe” .

Ștergerea unei foi de lucru. Selectați comanda rapidă pentru foi "Sheet 2" și utilizând meniul contextualşterge .

Celule și intervale de celule.

Câmpul de lucru este format din rânduri și coloane. Rândurile sunt numerotate de la 1 la 65536. Coloanele sunt desemnate cu litere latine: A, B, C, ..., AA, AB, ..., IV, total - 256. La intersecția rândului și coloanei există este o celulă. Fiecare celulă are propria sa adresă: numele coloanei și numărul rândului la intersecția căruia se află. De exemplu, A1, SV234, P55.

Pentru a lucra cu mai multe celule, este convenabil să le combinați în „interval”.

Un interval este celulele aranjate într-un dreptunghi. De exemplu, A3, A4, A5, B3, B4, B5. Pentru a scrie un interval, utilizați „: „: A3:B5

8:20 – toate celulele din rândurile 8 până la 20.

A:A – toate celulele din coloana A.

H:P - toate celulele din coloanele H la R.

Puteți include numele foii de lucru în adresa celulei: Sheet8!A3:B6.

2. Selectarea celulelor în Excel

Ce scoatem in evidenta?

Acțiuni

O celulă

Faceți clic pe el sau mutați selecția cu tastele săgeți.

Şir

Faceți clic pe numărul liniei.

Coloană

Faceți clic pe numele coloanei.

Gama de celule

Trageți indicatorul mouse-ului din colțul din stânga sus al intervalului în dreapta jos.

Domenii multiple

Selectați primul, apăsați SCHIFT + F 8, selectați următorul.

Masa intreaga

Faceți clic pe butonul Selectați tot (butonul gol din stânga numelor coloanelor)

Puteți modifica lățimea coloanelor și înălțimea rândurilor trăgând marginile dintre ele.

Utilizați barele de defilare pentru a determina câte rânduri are tabelul și care este numele ultimei coloane.
Atenţie!!! Pentru a ajunge rapid la sfârșitul tabelului pe orizontală sau pe verticală, trebuie să apăsați combinațiile de taste: Ctrl+→ - sfârșitul coloanelor sau Ctrl+↓ - sfârșitul rândurilor. Revenire rapidă la începutul tabelului - Ctrl+Home.

În celula A3, introduceți adresa ultimei coloane a tabelului.

Câte rânduri sunt în tabel? Introduceți adresa ultimului rând din celula B3.

3. Următoarele tipuri de date pot fi introduse în EXCEL:

Numerele.

Text (de exemplu, titluri și material explicativ).

Funcții (de ex. sumă, sinus, rădăcină).

Formule.

Datele sunt introduse în celule. Pentru a introduce date, celula necesară trebuie să fie evidențiată. Există două moduri de a introduce date:

Doar faceți clic în celulă și introduceți datele necesare.

Faceți clic în celulă și în bara de formule și introduceți datele în bara de formule.

Apăsați ENTER.

Introduceți numele dvs. în celula N35, centrați-l în celulă și puneți-l îngroșat.
Introduceți anul curent în celula C5 folosind bara de formule.

4. Modificarea datelor.

Selectați celula și apăsați F 2 și modificați datele.

Selectați celula și faceți clic în bara de formule și modificați datele de acolo.

Pentru a schimba formulele, puteți utiliza doar a doua metodă.

Modificați datele dintr-o celulă N35, adauga numele tau de familie. folosind oricare dintre metode.

5. Introducerea formulelor.

O formulă este o aritmetică sau expresie logică, conform cărora se fac calcule în tabel. Formulele constau din referințe de celule, simboluri de operare și funcții. Doamna EXCEL are foarte set mare funcții încorporate. Cu ajutorul lor, puteți calcula suma sau media aritmetică a valorilor dintr-un anumit interval de celule, puteți calcula dobânda la depozite etc.

Introducerea formulelor începe întotdeauna cu un semn egal. După introducerea unei formule, rezultatul calculului apare în celula corespunzătoare, iar formula în sine poate fi văzută în bara de formule.

Acţiune

Exemple

+

Plus

A1+B1

-

Scădere

A1 - B2

*

Multiplicare

B3*C12

/

Diviziune

A1/B5

Exponentiație

A4 ^3

=, <,>,<=,>=,<>

Semne de relație

A2

Puteți folosi parantezele în formule pentru a schimba ordinea operațiilor.

Completare automată.

Un instrument foarte convenabil, care este utilizat numai în MS EXCEL, este completarea automată a celulelor adiacente. De exemplu, trebuie să introduceți numele lunilor din an într-o coloană sau un rând. Acest lucru se poate face manual. Dar există mult mai mult mod convenabil:

Introduceți luna dorită în prima celulă, de exemplu ianuarie.

Selectați această celulă. În colțul din dreapta jos al cadrului de selecție există un pătrat mic - un marcator de umplere.

Mutați indicatorul mouse-ului la marcatorul de umplere (va arăta ca o cruce), în timp ce țineți apăsat butonul stâng al mouse-ului, trageți marcatorul în direcția dorită. În acest caz, valoarea curentă a celulei va fi vizibilă lângă cadru.

Dacă trebuie să completați o serie de numere, atunci trebuie să introduceți primele două numere în cele două celule adiacente (de exemplu, introduceți 1 în A4 și 2 în B4), selectați aceste două celule și trageți zona de selecție folosind marker la dimensiunea dorită.

Document selectat pentru vizualizare Excel pr.r. 2.docx

Bibliotecă
materiale

Lucrări practice 2

„Introducerea datelor și formulelor în celulele din foaia de calcul MS Excel”

· Introduceți datele în celule diferite tipuri: text, numeric, formule.

Exercita: Introduceți datele necesare și calculele simple în tabel.

Tehnologia de executare a sarcinilor:

1. Rulați programulMicrosoft Excel.

2. La celulăA1 Foaie 2 introduceți textul: „Anul înființării școlii”. Înregistrați datele în celulă folosind orice metodă cunoscută de dvs.

3. La celulăB1 introduceți numărul – anul înființării școlii (1971).

4. La celulăC1 introduceți numărul – anul curent (2016).

Atenţie! Vă rugăm să rețineți că în MS Text Excel datele sunt aliniate la stânga, iar numerele și datele sunt aliniate la dreapta.

5. Selectați o celulăD1 , introduceți formula de la tastatură pentru a calcula vârsta școlară:= C1- B1

Atenţie! Introducerea formulelor începe întotdeauna cu un semn egal«=». Adresele celulelor trebuie introduse cu litere latine, fără spații. Adresele celulelor pot fi introduse în formule fără a utiliza tastatura, ci pur și simplu făcând clic pe celulele corespunzătoare.

6. Ștergeți conținutul unei celuleD1 și repetați introducerea formulei folosind mouse-ul. Într-o celulăD1 pune un semn«=» , apoi faceți clic pe celulăC1, Vă rugăm să rețineți că adresa acestei celule a apărut înD1, pune un semn«–» și faceți clic pe celulăB1 , apăsați(Intră).

7. La celulăA2 introduceți text— Vârsta mea.

8. La celulăB2 introduceți anul de naștere.

9. La celulăC2 introduceți anul curent.

10. Tastați în celulăD2 formula pentru a vă calcula vârsta în anul curent(= C2- B2).

11. Selectați o celulăC2. Introduceți numărul de anul viitor. Vă rugăm să rețineți, recalcularea în celulăD2 s-a întâmplat automat.

12. Stabiliți-vă vârsta în 2025. Pentru a face acest lucru, înlocuiți anul din celulăC2 pe2025.

Munca independentă

Exercita: Calculați, folosind ET, sunt suficiente 130 de ruble pentru a cumpăra toate produsele pe care mama le-a comandat pentru dvs. și este suficient să cumpărați jetoane pentru 25 de ruble?

Tehnologia exercițiilor:
o În celula A1 introduceți „Nu”.
o În celulele A2, A3 introduceți „1”, „2”, selectați celulele A2, A3, indicați spre colțul din dreapta jos (ar trebui să apară o cruce neagră), întindeți până la celula A6
o În celula B1 introduceți „Nume”
o În celula C1 introduceți „Prețul în ruble”
o În celula D1 introduceți „Cantitate”
o În celula E1 introduceți „Cost”, etc.
o În coloana „Cost”, sunt scrise toate formulele engleză!
o În formule, numele celulelor sunt scrise în loc de variabile.
o După apăsarea Enter, în locul formulei, apare imediat un număr - rezultatul calculului

o Calculați singur totalul.

Arată rezultatul profesorului tău!!!

Document selectat pentru vizualizare Excel pr.r. 3.docx

Bibliotecă
materiale

Lucrări practice 3

„MS Excel. Crearea și editarea unui document foaie de calcul”

Prin finalizarea sarcinilor din acest subiect, veți învăța:

 Creați și completați un tabel cu date;

 Formatați și editați datele într-o celulă;

 Folosiți formule simple în tabel;

 Copiați formule.

Exercita:

1. Creați un tabel care să conțină orarul trenurilor de la gara Saratov la gara Samara. Vederea generală a tabelului „Program” este prezentată în figură.

2. Selectați celulaA3 , înlocuiți cuvântul „Golden” cu „Great” și apăsați tastaIntră .

3. Selectați celulaA6 , faceți clic stânga pe el de două ori și înlocuiți „Ugryumovo” cu „Veselkovo”

4. Selectați celulaA5 mergeți la bara de formule și înlocuiți „Sennaya” cu „Sennaya 1”.

5. Completați tabelul „Orar” cu calculele orelor de oprire a trenurilor în fiecare localitate. (inserați coloane) Calculați timpul total de oprire, timpul total de călătorie, timpul petrecut de trenul care se deplasează dintr-o localitate în alta.

Tehnologia de executare a sarcinilor:

1. Mutați coloana Ora plecării din coloana C în coloana D. Pentru a face acest lucru, urmați acești pași:

Selectați blocul C1:C7; selectați echipaTăiați .
Plasați cursorul în celula D1;
Rulați comandaIntroduce ;
Aliniați lățimea coloanei pentru a se potrivi cu dimensiunea antetului.;

2. Introduceți textul „Parcare” în celula C1. Aliniați lățimea coloanei pentru a se potrivi cu dimensiunea antetului.

3. Creați o formulă care calculează timpul de parcare într-o zonă populată.

4. Trebuie să copiați formula în blocul C4:C7 folosind mânerul de umplere. Pentru a face acest lucru, urmați acești pași:
Există un cadru în jurul celulei active, în colțul căruia se află un mic dreptunghi, apucați-l și extindeți formula până la celula C7.

5. Introduceți textul „Timp de călătorie” în celula E1. Aliniați lățimea coloanei pentru a se potrivi cu dimensiunea antetului.

6. Creați o formulă care calculează timpul necesar unui tren pentru a călători dintr-un oraș în altul.

7. Modificați formatul numeric pentru blocurile C2:C9 și E2:E9. Pentru a face acest lucru, urmați acești pași:

Selectați blocul de celule C2:C9;
Acasă – Format – Alte formate de numere - Timp și parametri setați (ore:minute) .

Apăsați tastaBine .

8. Calculați timpul total de parcare.
Selectați celula C9;
Faceți clic pe butonulAutosumă pe bara de instrumente;
Confirmați selecția blocului de celule C3:C8 și apăsați tastaIntră .

9. Introduceți text în celula B9. Pentru a face acest lucru, urmați acești pași:

Selectați celula B9;
Introduceți textul „Timp total de parcare”. Aliniați lățimea coloanei pentru a se potrivi cu dimensiunea antetului.

10. Ștergeți conținutul celulei C3.

Selectați celula C3;
Executați comanda meniului principal Editare - Șterge sau faceți clicŞterge pe tastatură;
Atenţie! Calculatorul recalcula automat suma din celula C9!!!

Rulați comanda Anula sau faceți clic pe butonul corespunzător din bara de instrumente.

11. Introduceți textul „Timp total de călătorie” în celula D9.

12. Calculați timpul total de călătorie.

13. Decorează masa cu culoare și evidențiază marginile mesei.

Munca independentă

Calculați folosind o foaie de calculExcelacheltuielile școlarilor care plănuiesc să plece într-o excursie în alt oraș.

Document selectat pentru vizualizare Excel pr.r. 4.docx

Bibliotecă
materiale

Lucrări practice 4

"Legături. Funcții încorporate ale MS Excel."

Prin finalizarea sarcinilor din acest subiect, veți învăța:

Efectuați operațiuni de copiere, mutare și completare automată pe celule și intervale individuale.

Distingeți tipurile de legături (absolute, relative, mixte)

Utilizați instrumente matematice și statistice încorporate în calcule Funcții Excel.

MS Excel conține 320 de funcții încorporate. Cel mai simplu mod obținerea de informații complete despre oricare dintre ele înseamnă folosirea meniuluiReferinţă . Pentru comoditate, funcțiile din Excel sunt împărțite în categorii (matematice, financiare, statistice etc.).
Fiecare apel de funcție este format din două părți: numele funcției și argumentele dintre paranteze.

Masă. Funcții Excel încorporate

* Scris fără argumente.

Masă . Tipuri de link-uri

Exercita.

1. Costul de 1 kW/h este stabilit. electricitate și citiri ale contoarelor pentru lunile precedente și curente. Este necesar să se calculeze consumul de energie electrică în perioada trecută și costul energiei electrice consumate.

Tehnologia de lucru:

1. Aliniați textul în celule. Selectați celulele A3:E3. Acasă - Format - Format celulă - Aliniere: orizontal - în centru, vertical - în centru, afișare - deplasare prin cuvinte.

2. În celula A4 introduceți: Sq. 1, în celula A5 introduceți: Sq. 2. Selectați celulele A4:A5 și utilizați marcatorul de completare automată pentru a completa numerotarea apartamentelor, 7 inclusiv.

5. Completați celulele B4: C10 așa cum se arată.

6. În celula D4, introduceți formula pentru a afla consumul de energie electrică. Și completați rândurile de mai jos folosind marcatorul de completare automată.

7. În celula E4, introduceți formula pentru a afla costul energiei electrice=D4*$B$1. Și completați rândurile de mai jos folosind marcatorul de completare automată.

Fiţi atenți!
La completarea automată, adresa celulei B1 nu se schimbă,
deoarece set de legături absolute.

8. În celula A11, introduceți textul „Statistici”, selectați celulele A11:B11 și faceți clic pe butonul „Îmbinare și Centrare” din bara de instrumente.

9. În celulele A12:A15, introduceți textul afișat în imagine.

10. Faceți clic pe celula B12 și introduceți funcția matematicăSUMĂ , pentru a face acest lucru trebuie să faceți clic în bara de formuleprin semnfx și selectați funcția, precum și confirmați intervalul de celule.

11. Funcțiile sunt setate în mod similar în celulele B13:B15.

12. Ai efectuat calculele pe Foaia 1, redenumiți-o Electricitate.

Munca independentă

Exercițiul 1:

Calculați-vă vârsta din acest an până în 2030 folosind marcatorul de completare automată. Anul în care te-ai născut este referință absolută. Efectuați calcule pe Foaia 2. Redenumiți Foaia 2 în Age.

Exercițiul 2: Creați un tabel conform exemplului.În celuleeu5: L12 șiD13: L14 ar trebui să existe formule: AVERAGE, COUNTIF, MAX, MIN. CeluleleB3: H12 sunt completate cu informații de către dvs.

Document selectat pentru vizualizare Excel pr.r. 5.docx

Bibliotecă
materiale

Lucrări practice 5

Prin finalizarea sarcinilor din acest subiect, veți învăța:

 Tehnologii pentru crearea unui document foaie de calcul;

 Atribuiți un tip datelor utilizate;

 Crearea de formule și reguli pentru modificarea legăturilor din acestea;

 Utilizați funcțiile statistice încorporate din Excel pentru calcule.

Sarcina 1. Calculați numărul de zile trăite.

Tehnologia de lucru:

1. Lansați aplicația Excel.

2. În celula A1, introduceți data nașterii (zi, lună, an – 20/12/97). Înregistrați datele introduse.

3. Vizualizați diferite formate de date(Acasă - Format celulă - Alte formate de numere - Data) . Convertiți data în tipHH.LL.AAAA. Exemplu, 14.03.2001

4. Luați în considerare mai multe tipuri de formate de dată în celula A1.

5. Introduceți data de astăzi în celula A2.

6. În celula A3, calculați numărul de zile trăite folosind formula. Rezultatul poate fi prezentat ca o dată, caz în care ar trebui convertit într-un tip numeric.

Sarcina 2. Vârsta elevilor. Pe baza unei liste date de studenți și a datelor lor de naștere. Stabiliți cine s-a născut mai devreme (mai târziu), stabiliți cine este cel mai în vârstă (cel mai tânăr).

Tehnologia de lucru:

1. Obțineți fișierul Age. De retea locala: Deschideți folderul Network Neighborhood -Şeful–Acte generale– Clasa a IX-a, găsiți dosarul Vârstă. Copiați-l în orice mod pe care îl cunoașteți sau descărcați de pe această pagină din partea de jos a aplicației.

2. Să calculăm vârsta elevilor. Pentru a calcula vârsta, trebuie să utilizați funcțiaASTĂZI scoate în evidență cea de azi data curentă Data nașterii elevului este scăzută din ea, apoi numai anul este extras din data rezultată folosind funcția AN. Din numărul rezultat scădem 1900 de secole și obținem vârsta elevului. Scrieți formula în celula D3=AN(AZI()-С3)-1900 . Rezultatul poate fi prezentat ca dată, apoi ar trebui convertit întip numeric.

3. Să stabilim cea mai devreme zi de naștere. Scrieți formula în celula C22=MIN(C3:C21) ;

4. Să determinăm cel mai tânăr student. Scrieți formula în celula D22=MIN(D3:D21) ;

5. Să stabilim ultima zi de naștere. Scrieți formula în celula C23=MAX(C3:C21) ;

6. Să determinăm cel mai în vârstă student. Scrieți formula în celula D23=MAX(D3:D21) .

Munca independenta:
Sarcină. Faceți calculele necesare ale creșterii elevilor în unități diferite măsurători.

Document selectat pentru vizualizare Excel pr.r. 6.docx

Bibliotecă
materiale

Lucrări practice 6

„MS Excel. Funcţii statistice” Partea a II-a.

Sarcina 3. Folosind o foaie de calcul, procesați datele folosind funcții statistice. Sunt oferite informații despre elevii din clasă, inclusiv scorul mediu pentru trimestru, vârsta (anul nașterii) și sexul. Determinați scorul mediu al băieților, proporția elevilor excelenți în rândul fetelor și diferența dintre scorul mediu al elevilor de diferite vârste.

Soluţie:
Să umplem tabelul cu datele inițiale și să efectuăm calculele necesare.Acordați atenție formatului valorilor din celulele „GPA” (numerice) și „Data nașterii” (data).

Tabelul folosește coloane suplimentare care sunt necesare pentru a răspunde la întrebările puse în problemă -vârsta studentului și este studentulun elev excelent și o fată simultan.
Pentru a calcula vârsta, a fost utilizată următoarea formulă (folosind celula G4 ca exemplu):

=INTEGER((AZI()-E4)/365,25)

Să comentăm. Data nașterii elevului se scade din data de astăzi. Astfel, obținem numărul total de zile care au trecut de la nașterea elevului. Împărțind acest număr la 365,25 (numărul real de zile dintr-un an, 0,25 zile pentru un an normal este compensat cu un an bisect), obținem numărul total de ani ai studentului; în sfârșit, evidențiind întreaga parte – vârsta elevului.

Dacă o fată este o elevă excelentă este determinat de formula (folosind celula H4 ca exemplu):

=DACĂ(ȘI(D4=5;F4="w");1,0)

Să trecem la calculele de bază.
În primul rând, trebuie să determinați scorul mediu al fetelor. Conform definiției, este necesar să se împartă scorul total al fetelor la numărul lor. În aceste scopuri, puteți utiliza funcțiile corespunzătoare ale procesorului de masă.

=SUMĂDACA(F4:F15,"w";D4:D15)/COUNTIF(F4:F15;"w")

Funcția SUMIF vă permite să însumați valorile numai în acele celule din interval care îndeplinesc un anumit criteriu (în cazul nostru, copilul este un băiat). Funcția COUNTIF numără numărul de valori care îndeplinesc un criteriu specificat. Astfel obținem ceea ce ne trebuie.
Pentru a calcula ponderea elevilor excelente în rândul tuturor fetelor, vom lua numărul de fete excelente la numărul total de fete (aici vom folosi un set de valori dintr-una dintre coloanele auxiliare):

=SUM(H4:H15)/COUNTIF(F4:F15,"w")

În cele din urmă, vom determina diferența dintre scorurile medii ale copiilor de diferite vârste (vom folosi coloana auxiliară în calculeVârstă ):

=ABS(SUMĂDAC(G4:G15,15,D4:D15)/CONTĂRĂDAC(G4:G15,15)-
SUMIF(G4:G15,16,D4:D15)/COUNTIF(G4:G15,16))

Vă rugăm să rețineți că formatul datelor din celulele G18:G20 este numeric, cu două zecimale. Astfel, problema este complet rezolvată. Figura arată rezultatele soluției pentru un anumit set de date.

Document selectat pentru vizualizare Excel pr.r. 7.docx

Bibliotecă
materiale

Lucrări practice 7

„Crearea de diagrame folosind MS Excel”

Prin finalizarea sarcinilor din acest subiect, veți învăța:

 Efectuați operațiuni pentru a crea diagrame pe baza datelor introduse în tabel;

 Editați datele diagramei, tipul și designul acesteia.

Ce este o diagramă? O diagramă este concepută pentru a reprezenta datele grafic. Liniile, barele, coloanele, sectoarele și alte elemente vizuale sunt folosite pentru a afișa datele numerice introduse în celulele tabelului. Aspectul diagramei depinde de tipul acesteia. Toate diagramele, cu excepția diagramei circulare, au două axe: una orizontală – axa categoriei și una verticală – axa valorii. Când se creează diagrame 3-D, se adaugă o a treia axă - axa seriei. Adesea, o diagramă va conține elemente precum o grilă, titluri și o legendă. Grilele sunt o extensie a diviziunilor găsite pe axe, titlurile sunt folosite pentru a explica elementele individuale ale diagramei și natura datelor prezentate pe acesta, iar legenda ajută la identificarea seriei de date prezentate în diagramă. Există două moduri de a adăuga diagrame: încorporați-le în foaia de lucru curentă sau adăugați o foaie de diagramă separată. Dacă diagrama în sine este de interes, aceasta este plasată pe o foaie separată. Dacă trebuie să vizualizați simultan diagrama și datele pe care a fost construită, atunci este creată o diagramă încorporată.

Diagrama este salvată și tipărită împreună cu registrul de lucru.

Odată ce diagrama este generată, se pot face modificări. Înainte de a efectua orice acțiune asupra elementelor diagramei, selectați-le făcând clic stânga pe ele. După aceasta, apelați meniul contextual folosind butonul din dreapta mouse-ul sau utilizați butoanele corespunzătoareBara de instrumente pentru diagrame .

Sarcină: Utilizați o foaie de calcul pentru a reprezenta grafic funcția Y=3,5x–5. Unde X ia valori de la –6 la 6 în trepte de 1.

Tehnologia de lucru:

1. Lansați procesorul de foi de calcul Excel.

2. În celula A1 introduceți „X”, în celula B1 introduceți „Y”.

3. Selectați intervalul de celule A1:B1 și centrați textul în celule.

4. În celula A2, introduceți numărul -6, iar în celula A3, introduceți -5. Utilizați marcatorul de completare automată pentru a completa celulele de mai jos până la opțiunea 6.

5. În celula B2, introduceți formula: =3,5*A2–5. Utilizați marcatorul de completare automată pentru a extinde această formulă până la sfârșitul parametrilor de date.

6. Selectați întregul tabel pe care l-ați creat și acordați-i margini externe și interne.

7. Selectați antetul tabelului și completați zona interioară.

8. Selectați celulele rămase din tabel și completați zona interioară cu o culoare diferită.

9. Selectați întregul tabel. Selectați Inserare din bara de meniu -Diagramă , Tip: punct, Vedere: Punct cu curbe netede.

10. Mutați graficul sub tabel.

Munca independenta:

Reprezentați grafic funcția y=păcat(x)/ xpe segmentul [-10;10] cu un pas de 0,5.

Afișați graficul funcției: a) y=x; b) y=x 3 ; c) y=-x pe segmentul [-15;15] cu pasul 1.

Deschideți fișierul „Orașe” (mergeți în folderul de rețea - clasa a IX-a - Orașe).

Calculați costul unui apel fără reducere (coloana D) și costul unui apel ținând cont de reducere (coloana F).

Pentru o reprezentare mai clară, construiți două diagrame circulare. (1-diagramă a costului unui apel fără reducere; 2-diagramă a costului unui apel cu reducere).

Document selectat pentru vizualizare Excel pr.r. 8.docx

Bibliotecă
materiale

Lucrări practice 8

CONSTRUCȚIA GRAFICELOR ȘI A DESENELOR PRIN MIJLOACE MS EXCEL

1. Construcția desenului"UMBRELĂ"

Funcțiile ale căror grafice sunt incluse în această imagine sunt date:

y1= -1/18x 2 + 12, xО[-12;12]

y2= ​​​​-1/8x 2 +6, xО[-4;4]

y3= -1/8(x+8) 2 + 6, xО[-12; -4]

y4= -1/8(x-8) 2 + 6, xО

y5= 2(x+3) 2 – 9, xО[-4;0]

y6=1.5(x+3) 2 – 10, xО[-4;0]

- Lansați MS EXCEL

· - În celulăA1 introduceți desemnarea variabileiX

· - Completați intervalul de celule A2:A26 cu numere de la -12 la 12.

Vom introduce formule secvenţial pentru fiecare grafic al funcţiei. Pentru y1= -1/8x 2 + 12, xО[-12;12], pentru
y2= ​​​​-1/8x 2 +6, xО[-4;4] etc.

Procedură:

Plasați cursorul într-o celulăB1 si intray1

La celulăB2 introduceți formula=(-1/18)*A2^2 +12

Clic Intră pe tastatură

Valoarea funcției este calculată automat.

Întindeți formula la celula A26

Similar cu celulaC10 (deoarece găsim valoarea funcției doar pe segmentul x din [-4;4]) introduceți formula pentru graficul funcțieiy2= ​​​​-1/8x 2 +6. E.T.D.

Rezultatul ar trebui să fie următorul ET

După ce toate valorile funcției au fost calculate, putețiconstruiți grafice acestefuncții

Selectați intervalul de celule A1:G26

Pe bara de instrumente selectațiInserați meniul → Diagramă

În fereastra Chart Wizard, selectațiSpot → Selectați vizualizarea dorită → Faceți clic Bine .

Rezultatul ar trebui să fie următoarea figură:

Misiunea pentru munca individuala:

Construiți grafice ale funcțiilor într-un sistem de coordonate.x de la -9 la 9 în pași de 1 . Ia desenul.

1. „Ochelari”

2. „Pisica” Filtrarea (eșantionarea) datelor într-un tabel vă permite să afișați numai acele rânduri al căror conținut de celule îndeplinește o condiție specificată sau mai multe condiții. Spre deosebire de sortare, filtrarea nu reordonează datele, ci doar ascunde acele înregistrări care nu îndeplinesc criteriile de selecție specificate.

Filtrarea datelor se poate face în două moduri:folosind AutoFilter sau Advanced Filter.

Pentru a utiliza filtrul automat aveți nevoie de:

o plasați cursorul în interiorul tabelului;

o selectați o echipăDate - Filtru - AutoFilter;

o extinde lista coloanei prin care se va face selecția;

o selectați o valoare sau condiție și setați criteriul de selecție în caseta de dialogFiltru automat personalizat.

Pentru a restabili toate rândurile din tabelul sursă, trebuie să selectați toate rândurile din lista derulantă a filtrului sau să selectați comandaDate - Filtru - Afișează toate.

Pentru a anula modul de filtrare, trebuie să plasați cursorul în interiorul tabelului și să selectați din nou comanda de meniuDate - Filter - Autofilter (debifați caseta).

Filtrul avansat vă permite să creați mai multe criterii de selecție și să efectuați o filtrare mai complexă a datelor din foile de calcul prin specificarea unui set de condiții de selecție pe mai multe coloane. Filtrarea înregistrărilor folosind un filtru avansat se face cu ajutorul comenzii de meniuDate - Filtru - Filtru avansat.

Exercita.

Creați un tabel în conformitate cu exemplul prezentat în figură. Salvați-l ca Sort.xls.

Tehnologia de executare a sarcinilor:

1. Deschideți documentul Sort.xls

2.

3. Executați comanda de meniuDate - Sortare.

4. Selectați prima cheie de sortare „Ascendent” (Toate departamentele din tabel vor fi aranjate alfabetic).

Să ne amintim că în fiecare zi trebuie să tipărim o listă de mărfuri rămase în magazin (având un sold diferit de zero), dar pentru aceasta trebuie mai întâi să obținem o astfel de listă, de exemplu. filtrați datele.

5. Plasați cursorul cadru în interiorul tabelului de date.

6. Executați comanda de meniuDate - Filtru

7. Deselectați tabele.

8. Fiecare celulă de antet de tabel are acum un buton „Săgeată în jos”; acesta nu este imprimat; Dorim să lăsăm toate înregistrările cu un rest diferit de zero.

9. Faceți clic pe butonul săgeată care apare în coloanăCantitatea rămasă . Se va deschide o listă din care se va face selecția. Selectați liniaStare. Setați condiția: > 0. Faceți clicBine . Datele din tabel vor fi filtrate.

10. În loc de lista completa produse, vom primi o listă cu produsele vândute până în prezent.

11. Filtrul poate fi consolidat. Dacă selectați suplimentar un departament, puteți obține o listă de mărfuri nelivrate în funcție de departament.

12. Pentru a vedea din nou lista tuturor bunurilor nevândute pentru toate departamentele, trebuie să selectați criteriul „Toate” din lista „Departament”.

13. Pentru a evita confuzia în rapoartele dvs., introduceți o dată care se va schimba automat în funcție de ora sistemului computerului dvsFormule - Funcție de inserare - Data și ora - Azi .

Munca independentă

„MS Excel. Funcții statistice"

1 sarcină (generală) (2 puncte).

Folosind o foaie de calcul, procesați datele folosind funcții statistice.
1. Se oferă informații despre elevii clasei (10 persoane), inclusiv notele pentru o lună la matematică. Numărați numărul de cinci, patru, doi și trei, găsiți scorul mediu al fiecărui elev și scorul mediu al întregului grup. Creați o diagramă care ilustrează procentul de note dintr-un grup.

2.1 sarcină (2 puncte).

Patru prieteni călătoresc cu trei moduri de transport: tren, avion și navă. Nikolai a navigat 150 km cu barca, a călătorit 140 km cu trenul și a zburat 1100 km cu avionul. Vasily a navigat 200 km cu barca, 220 km cu trenul și 1160 km cu avionul. Anatoly a zburat 1200 km cu avionul, 110 km cu trenul și 125 km cu barca. Maria a parcurs 130 km cu trenul, a zburat 1500 km cu avionul și a navigat 160 km cu barca.
Creați o foaie de calcul pe baza datelor de mai sus.

Adăugați o coloană la tabel care va afișa numărul total de kilometri pe care i-a parcurs fiecare dintre băieți.

Calculați numărul total de kilometri pe care i-au parcurs copiii cu trenul, au zburat cu avionul și au navigat cu barca (pe fiecare tip de transport separat).

Calculați numărul total de kilometri ai tuturor prietenilor.

Stabiliți numărul maxim și minim de kilometri parcurși de prieteni folosind toate tipurile de transport.

Determinați numărul mediu de kilometri pentru toate tipurile de transport.

2.2 sarcină (2 puncte).

Creați un tabel „Lacurile Europei” folosind următoarele date privind suprafața (km pătrați) și cea mai mare adâncime (m): Ladoga 17.700 și 225; Onega 9510 și 110; Marea Caspică 371.000 și 995; Wenern 5550 și 100; Chudskoye cu Pskovsky 3560 și 14; Balaton 591 și 11; Geneva 581 și 310; Wettern 1900 și 119; Constanța 538 și 252; Mälaren 1140 și 64. Determinați cel mai mare și cel mai mic lac din zonă, cel mai adânc și cel mai puțin adânc.

2.3 sarcină (2 puncte).

Creați un tabel „Râurile Europei” folosind următoarea lungime (km) și suprafața bazinului (mii km pătrați): Volga 3688 și 1350; Dunărea 2850 și 817; Rinul 1330 și 224; Elba 1150 și 148; Vistula 1090 și 198; Loara 1020 și 120; Ural 2530 și 220; Don 1870 și 422; Sena 780 și 79; Tamisa 340 și 15. Determinați cel mai lung și cel mai scurt râu, calculați suprafața totală a bazinelor hidrografice, lungimea medie a râurilor din partea europeană a Rusiei.

Sarcina 3 (2 puncte).

Banca înregistrează oportunitatea plăților împrumuturilor acordate mai multor organizații. Sunt cunoscute suma împrumutului și suma deja plătită de organizație. Pentru debitori au fost stabilite penalități: dacă societatea a rambursat împrumutul cu mai mult de 70 la sută, amenda va fi de 10 la sută din valoarea datoriei, în caz contrar amenda va fi de 15 la sută. Calculați amenda pentru fiecare organizație, amenda medie, suma totală de bani pe care banca urmează să o primească suplimentar. Determinați amenda medie a organizațiilor bugetare.

Găsiți material pentru orice lecție,