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

Procesarea analitică analitică (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 PivotChart de care veți avea nevoie.

În acest articol

Ce este business intelligence?

Un analist de afaceri dorește adesea să obțină o imagine de ansamblu asupra unei afaceri, să vadă tendințe mai ample pe baza datelor agregate și să vadă 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 oferi informații care pot fi utilizate pentru a lua decizii de afaceri informate și pentru a lua măsuri. De exemplu, cu OLAP și business intelligence, 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 pentru perioada de beneficii din ultimii cinci ani?

Câți bani au cheltuit clienții pentru 35 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 care este optimizată pentru interogare și raportare, nu 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 sofisticată care utilizează structuri multidimensionale pentru a oferi acces rapid la datele pentru analiză. În această organizație, pentru un raport PivotTable sau un raport PivotChart, puteți 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, puteți afișa detalii 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, nu Microsoft Office Excel calculează valorile agregate, mai puține date trebuie trimise către Excel atunci când creați sau editați un raport. Această abordare vă permite să lucrați cu o cantitate mai mare de date sursă decât dacă datele ar fi organizate într-o bază de date tradițională în care 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 organizarea acestor măsuri. Bazele de date OLAP vă ajută să vă organizați datele cu mai multe niveluri de detaliu folosind aceleași categorii pe care le cunoașteți pentru analiza datelor.

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

Cub O structură de date care combină măsurile pe 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ători 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 valori centrale într-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 nivel de an al dimensiunii de timp, în timp ce ianuarie reprezintă membri neunici la nivel de lună, deoarece există mai mult de un ianuarie în dimensiunea de timp, deoarece conține date pentru mai mult de un an.

Membru calculat Un membru al unei dimensiuni a cărei valoare este evaluată în timpul executării utilizând o expresie. Valorile membrilor calculati pot fi derivate din valorile altor membri. De exemplu, elementul calculat „profit” poate fi determinat prin scăderea valorii articolului, precum și a costurilor, din valoarea articolului, vânzări.

măsurare Un set de una sau mai multe ierarhii ordonate la nivel de cub 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 care pot fi extinse și restrânse pentru a 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 părinte și zero sau mai mulți copii. Un element copil este un membru al unui grup anterior din ierarhie care este direct legat de membrul curent. De exemplu, într-o ierarhie de timp care conține niveluri de trimestru, lună și zi, ianuarie este un copil al lui Qtr1. Elementul părinte este membrul de nivel inferior din ierarhie care este direct legat de membrul curent. Valoarea părinte este de obicei o consolidare a valorilor tuturor elementelor copil. De exemplu, într-o ierarhie temporală care conține niveluri trimestriale, luni și zile, Qtr1 este părintele lunii ianuarie.

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

Funcții OLAP în Excel

Obținerea datelor OLAP Vă puteți conecta la sursele de date OLAP în același mod în care vă puteți conecta la alte surse de date externe. Puteți lucra cu baze de date create cu serviciile Microsoft SQL Server OLAP versiunea 7.0, Microsoft SQL Server Analysis Services versiunea 2000 și Microsoft SQL Server Analysis Services versiunea 2005, produse server 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 un raport PivotTable sau PivotChart sau într-o funcție de foaie de lucru convertită dintr-un raport PivotTable, nu ca un interval de date extern. Puteți salva rapoarte OLAP PivotTable și PivotChart în șabloane de rapoarte și puteți crea fișiere Office Data Connection (ODC) pentru a vă conecta la bazele de date OLAP pentru interogări OLAP. Când deschideți un fișier .odc în Excel, este afișat un raport PivotTable gol și este gata pentru a fi plasat.

Creați fișiere cub pentru utilizare offline Puteți crea un fișier cub autonom (.cub) cu un subset de date dintr-o bază de date de server 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 dintr-un 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 acțiuni detaliate ale serverului, dar nu acceptă propriul operator nativ și setul de date.

KPI Un KPI este o măsură specifică, calculată, definită pe server, care vă permite să urmăriți „Indicatorii cheie de performanță”, inclusiv starea (valoarea curentă corespunde cu un anumit număr). și tendință (valori în timp). Când sunt afișate, serverul poate trimite pictogramele 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ă deruleze valoarea în sus sau în jos (pentru 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, un format pe partea de server pentru venit ar putea fi un format de monedă numerică, 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 mică de 30.000, iar dacă valoarea este pozitivă - obișnuit. mai mare sau egal cu 30.000. Informații suplimentare poate fi găsit.

Limba interfeței Office Un administrator de cub poate defini traduceri pentru datele și erorile de pe server pentru utilizatorii care trebuie să vizualizeze informațiile din PivotTable într-o altă limbă. Această caracteristică este definită ca o proprietate de conexiune la fișier, iar localitatea și țara computerului utilizatorului trebuie să corespundă cu limba interfeței.

Componentele software necesare pentru a accesa sursele de date OLAP

Furnizorul 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 client software pentru a accesa bazele de date create cu 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 Alte produse OLAP necesită drivere suplimentare și software client pentru a fi instalate. Pentru a utiliza capabilitățile de manipulare a datelor OLAP ale Excel, un produs 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 un furnizor 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 disponibilă în rețea, puteți obține date sursă direct din aceasta. 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 un raport PivotTable sau PivotChart sau ca funcție de foaie convertită dintr-un raport PivotTable.

Microsoft Query Cu 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 asociat cu un fișier cub. Informații suplimentare .

Diferențele între funcțiile OLAP și datele sursă non-OLAP

Dacă lucrați cu rapoarte PivotTable și Pivot Charts 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 seta parametrii pentru interogare numai atunci când afișați diferite elemente de câmp ale filtrului de raport. În plus, aveți mai multe opțiuni pentru actualizarea raportului.

În rapoartele bazate pe datele sursă OLAP, opțiunile de câmp pentru filtrul de raport nu sunt disponibile, interogarea în fundal nu este disponibilă și opțiunea de optimizare a memoriei nu este disponibilă.

Notă: 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 utilizate numai ca rânduri (rânduri), coloane (categorie) sau câmpuri de pagină. Câmpurile de măsură pot fi folosite numai 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 OLAP sursă, serverul determină nivelurile disponibile de detaliu și calculează valorile rezumative, astfel încât înregistrările de detalii 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 puteți afișa elemente fără date.

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

Ordine inițială de sortare 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 elementele. 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 este posibilă modificarea funcțiilor de rezumat pentru câmpurile de valoare. Pentru alte tipuri de date sursă, puteți modifica funcția de agregare pentru câmpul 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 de rezumat pentru subtotaluri și puteți afișa sau ascunde subtotalurile pentru toate câmpurile de rând și coloană.

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

Problemele de analiză, OLAP, depozite de date prezintă un interes din ce în ce mai mare pentru specialiștii IT ruși. Până în prezent, presa noastră informatică și internetul au publicat o mulțime de materiale bune, academice în ceea ce privește prezentarea materialelor pe această temă, inclusiv cele introductive. Vă aducem în atenție un articol în care încercăm în mod deliberat să explicăm OLAP „pe degete”, folosind un exemplu concret. Practica arată că o astfel de explicație este necesară pentru unii profesioniști IT și mai ales pentru utilizatorii finali.

Deci, OLAP *1 ca primă aproximare, „pe degete”, poate fi definit ca o modalitate specială de analiză a datelor și generare de rapoarte. Esența sa este de a oferi utilizatorului un tabel multidimensional care rezumă automat datele în diverse secțiuni și vă permite să gestionați interactiv calculele și forma raportului. Acest articol va vorbi despre tehnologia și operațiunile de bază ale OLAP folosind exemplul de analiză a facturilor pentru o întreprindere angajată în comerțul cu ridicata cu produse alimentare.

*1. OLAP - Procesare analitică on-line, analiza datelor operaționale.

Ca instrument, va fi luat în considerare un sistem OLAP din cea mai simplă și mai ieftină clasă, un client OLAP *1. De exemplu, se alege cel mai simplu produs din numărul de clienți OLAP - „Contour Standard” de la Intersoft Lab. (Pentru claritate, mai târziu în articol, termenii OLAP general acceptați vor fi indicați cu caractere aldine și însoțiți de omologii englezi.)

*1. Pentru mai multe informații despre clasificarea sistemelor OLAP, consultați articolul „OLAP Made in Russia” din PC Week/RE, Nr. 3/2001.

Deci, să începem cu sistemul. Mai întâi trebuie să descrii sursa de date (sursa de date) - calea către tabel și câmpurile acestuia. Aceasta este sarcina utilizatorului care cunoaște implementarea fizică a bazei de date. Pentru utilizatorii finali, acesta traduce numele tabelului și câmpurile acestuia în termeni de domeniu. În spatele „sursei de date” se află un tabel local, un tabel sau o vedere sau o procedură stocată de server SQL.

Cel mai probabil, într-o anumită bază de date, facturile sunt stocate nu într-unul, ci în mai multe tabele. În plus, este posibil ca unele câmpuri sau înregistrări să nu fie utilizate pentru analiză. Prin urmare, este creat apoi un Sample (set de rezultate sau interogare), în care sunt configurate următoarele: algoritmul de combinare a tabelelor după câmpuri cheie, condiții de filtrare și setul de câmpuri returnate. Să numim selecția noastră „Facturi” și să punem în ea toate câmpurile sursei de date „Facturi”. Astfel, un specialist IT, prin crearea unui strat semantic, ascunde de utilizatorul final implementarea fizică a bazei de date.

Apoi raportul OLAP este configurat. Acest lucru poate fi făcut de un expert în domeniu. În primul rând, câmpurile unui eșantion de date plat sunt împărțite în două grupuri - fapte (fapte sau măsuri) și măsurători (dimensiuni). Faptele sunt numere, iar măsurătorile sunt „tăieri” în care faptele vor fi rezumate. În exemplul nostru, dimensiunile vor fi: „Regiune”, „Oraș”, „Client”, „Produs”, „Data”, iar faptul va fi unul - câmpul „Suma” al facturii. De fapt, trebuie selectați unul sau mai mulți algoritmi de agregare. OLAP este capabil nu numai să rezume totalurile, ci și să efectueze calcule mai complexe, până la analize statistice. Selectarea mai multor algoritmi de agregare va crea fapte virtuale, calculate. În exemplu, este selectat un algoritm de agregare - „Suma”.

O proprietate specială a sistemelor OLAP este generarea de dimensiuni și date pentru perioade de timp mai vechi de la o dată și calcularea automată a totalurilor pentru aceste perioade. Să selectăm perioadele „An”, „Trimestru” și „Lună”, în timp ce nu vor exista date pentru fiecare zi în raport, dar vor apărea parametrii generați „Anul”, „Trimerul” și „Luna”. Să denumim raportul „Analiza vânzărilor” și să-l salvăm. Lucrările de creare a interfeței aplicației analitice sunt finalizate.

Acum, când rulează această interfață zilnic sau lunar, utilizatorul va vedea un tabel și un grafic în care facturile sunt rezumate pe articol, client și perioadă.

Pentru ca manipularea datelor să fie intuitivă, instrumente de management tabel dinamic sunt elementele tabelului în sine - coloanele și rândurile sale. Utilizatorul poate muta, șterge, filtra și efectua alte operațiuni OLAP. Tabelul calculează automat noile subtotaluri și totaluri finale.

De exemplu, trăgând (operațiune de mutare) coloana „Produs” pe primul loc, vom obține un raport de comparație - „Comparație a volumelor de vânzări ale produselor pentru anul”. Pentru a agrega datele pentru un an, trebuie doar să trageți coloanele „Trimestru” și „Lună” în partea de sus a tabelului - „zona de măsurători inactive”. Dimensiunile „Sfert” și „Lună” transferate în această zonă vor fi închise (operațiune „dimensiune închidere”), adică excluse din raport; faptele vor fi rezumate pentru anul. În ciuda faptului că dimensiunile sunt închise, puteți seta anumiți ani, trimestre și luni pentru filtrarea datelor (operațiune „filtru”).

Pentru o mai mare claritate, să schimbăm tipul de grafic care ilustrează tabelul OLAP și locația acestuia pe ecran.

Explorarea datelor (operațiunea „drill down”) vă permite să obțineți informații mai detaliate despre vânzările produsului care ne interesează. Făcând clic pe semnul „+” de lângă produsul „Cafea”, vom vedea volumele vânzărilor sale pe regiune. După ce am deschis regiunea Ural, vom obține volume de vânzări în contextul orașelor din regiunea Ural, aprofundând în datele despre Ekaterinburg, vom putea vizualiza date despre cumpărătorii angro ai acestui oraș.

De asemenea, puteți utiliza măsurători deschise pentru a seta filtre. Pentru a compara dinamica vânzărilor de bomboane la Moscova și Ekaterinburg, să setăm filtre pentru dimensiunile „Produs” și „Oraș”.

Închideți măsurătorile inutile și selectați tipul de grafic „Linie”. Pe graficul rezultat, puteți urmări dinamica vânzărilor, puteți evalua fluctuațiile sezoniere și relația dintre scăderi și creșterea vânzărilor de mărfuri în diferite orașe.

Astfel, am fost convinși că tehnologia OLAP permite utilizatorului să elibereze zeci de tipuri de rapoarte diverse dintr-o singură interfață, gestionând un tabel OLAP dinamic cu mouse-ul. Sarcina unui programator care deține un astfel de instrument nu este codificarea de rutină a formularelor de raportare, ci configurarea unui client OLAP pentru baze de date. În același timp, metodele de gestionare a raportului sunt intuitive pentru utilizatorul final.

Într-adevăr, OLAP este o extensie și o dezvoltare naturală a ideii de foi de calcul. În esență, interfața vizuală OLAP este și o foaie de calcul, dar echipată cu un motor de calcul puternic și un standard special pentru prezentarea și gestionarea datelor. Mai mult, unii clienți OLAP sunt implementați ca suplimente pentru MS Excel. Prin urmare, cea de-a miliona armată de „gulere albe”, care au încredere în foile de calcul, stăpânesc rapid și instrumentele OLAP. Pentru ei, aceasta este o „revoluție de catifea” care oferă noi oportunități, dar nu implică nevoia de recalificare.

Dacă cititorul, după ce a citit acest articol, nu și-a pierdut interesul pentru OLAP, se poate referi la materialele menționate la început. Colecții de astfel de materiale sunt postate pe o serie de site-uri de pe Internet, inclusiv pe site-ul Intersoft lab - www.iso.ru. Din acesta puteți descărca și o versiune demo a sistemului Kontur Standard cu exemplul descris în articol.

Standard masă rotativă datele originale sunt stocate pe hard disk-ul local. Astfel, le poți gestiona și reorganiza oricând, chiar dacă nu ai acces la rețea. Dar acest lucru nu se aplică în niciun caz la OLAP PivotTables. În OLAP PivotTables, memoria cache nu este niciodată stocată pe hard disk-ul local. Prin urmare, imediat după deconectare de la retea locala tabelul pivot va eșua. Nu veți putea muta niciunul dintre câmpurile din acesta.

Dacă mai trebuie să analizați datele OLAP după ce ați fost offline, creați un cub de date offline. Cubul de date offline este dosar separat, care este un cache PivotTable care stochează date OLAP care sunt vizualizate după ce au fost deconectate de la rețeaua locală. Datele OLAP copiate într-un tabel pivot pot fi tipărite, site-ul http://everest.ua descrie acest lucru în detaliu.

Pentru a crea un cub de date autonom, creați mai întâi un tabel pivot OLAP. Plasați cursorul în tabelul Pivot și faceți clic pe butonul Instrumente OLAP din fila contextuală Instrumente, care face parte din grupul de file contextuale Instrumente PivotTable. Selectați comanda Offline OLAP (Fig. 9.8).

Orez. 9.8. Creați un cub de date offline

Apare caseta de dialog Offline OLAP Data Cube Settings. Faceți clic pe butonul Creare fișier de date offline. Ați pornit Expertul Creare fișier cub de date. Faceți clic pe butonul Următorul pentru a continua procedura.

Mai întâi trebuie să specificați dimensiunile și nivelurile care vor fi incluse în cubul de date. În caseta de dialog, trebuie să selectați datele care vor fi importate din baza de date OLAP. Ideea este de a specifica doar acele dimensiuni care vor fi necesare după ce computerul este deconectat de la rețeaua locală. Cu cât specificați mai multe dimensiuni, cu atât va fi mai mare cubul de date offline.

Faceți clic pe butonul Următorul pentru a trece la următoarea casetă de dialog a expertului. Vă oferă posibilitatea de a specifica membri sau elemente de date care nu vor fi incluse în cub. În special, nu veți avea nevoie de măsura Vânzărilor pe Internet-Sumă extinsă, așa că va fi debifată în listă. O casetă de selectare debifată indică faptul că elementul specificat nu va fi importat și va ocupa spațiu suplimentar pe hard disk-ul local.

În ultimul pas, specificați locația și numele cubului de date. În cazul nostru, fișierul cub va fi numit MyOfflineCube.cub și va fi localizat în folderul Work.

Fișierele cub de date au extensia .pui

După un timp, Excel salvează cubul de date offline în folderul specificat. Pentru a-l testa, faceți dublu clic pe fișier, care va genera automat un registru de lucru Excel care conține un PivotTable asociat cu cubul de date selectat. Odată creat, puteți distribui cubul de date offline tuturor utilizatorilor interesați care lucrează în modul LAN offline.

Odată conectat la rețeaua locală, puteți deschide fișierul cub de date offline și îl puteți actualiza, precum și tabelul de date corespunzător. Principiul principal este că cubul de date offline este folosit doar pentru lucru atunci când rețeaua locală este deconectată, dar este obligatoriu să îl actualizați după ce conexiunea este restabilită. Încercarea de a actualiza un cub de date offline după ce conexiunea este întreruptă va eșua.

Lucrul cu fișiere cub offline

Un fișier cub offline (.cub) stochează date sub forma unui cub OLAP (Online Analytical Processing). Aceste date pot face parte din baza de date OLAP de pe serverul OLAP sau pot fi generate independent de baza de date OLAP. Utilizați un fișier cub offline pentru a continua să lucrați cu rapoartele PivotTable și PivotChart atunci când serverul nu este disponibil sau când sunteți offline.

Notă de siguranță: Aveți grijă când utilizați sau distribuiți un fișier cub offline care conține informații sensibile sau personale. În locul unui fișier cub, se recomandă să stocați datele într-un registru de lucru, astfel încât să puteți controla accesul la date utilizând gestionarea drepturilor. Pentru mai multe informații, consultați Gestionarea drepturilor la informații în Office.

Când lucrați cu un raport PivotTable sau PivotChart care se bazează pe datele sursă OLAP Server, puteți utiliza Offline Cube Wizard pentru a copia datele sursă într-un fișier cub offline separat de pe computer. Crearea acestor fișiere offline necesită un furnizor de date OLAP care acceptă această capacitate, cum ar fi MSOLAP de la Microsoft SQL Server Analysis Services, instalat pe computer.

Notă: Crearea și utilizarea fișierelor cub offline de la Microsoft SQL Server Analysis Services sunt supuse termenului și licenței Instalări Microsoft SQL Server. Examinați informațiile relevante de licență ale ediției SQL Server.

Lucrul cu Offline Cube Wizard

Pentru a crea un fișier cub offline, puteți selecta un subset de date dintr-o bază de date OLAP utilizând expertul cub offline și apoi salvați acel subset. Raportul nu trebuie să includă toate câmpurile incluse în fișier și nici nu trebuie să selecteze niciunul dintre ele și câmpurile de date disponibile în baza de date OLAP. Pentru a menține fișierul la minimum, puteți include numai datele pe care doriți să apară în raport. Puteți omite toate dimensiunile și, pentru majoritatea tipurilor de dimensiuni, puteți, de asemenea, să excludeți detalii și membri de nivel inferior. nivel superior care nu trebuie afișate. Pentru toate elementele pe care le includeți, câmpurile de proprietate disponibile în baza de date pentru acele elemente sunt stocate și în fișierul offline.

Preluarea datelor offline și reconectarea acestora

Pentru a face acest lucru, mai întâi trebuie să creați un raport PivotTable sau PivotChart bazat pe baza de date a serverului, apoi creați un fișier cub autonom din raport. După aceea, puteți comuta oricând raportul între baza de date server și fișierul offline. De exemplu, dacă utilizați laptop pentru călătorii acasă și video, apoi reconectați computerul la rețea.

Următorii sunt pașii principali de urmat durata de viata a bateriei cu datele, apoi transferați datele înapoi pe Internet.

Creați sau deschideți un raport PivotTable sau PivotChart pe baza datelor OLAP pe care doriți să le accesați offline.

Creați un fișier cub offline pe computer. În capitolul Creați un fișier cub offline dintr-o bază de date de server OLAP(mai jos în acest articol).

Deconectarea de la rețea și lucrul cu un fișier cub offline.

Conectați-vă și atașați din nou fișierul cub offline. Consultați secțiunea Reconectarea fișierului cub offline la baza de date a serverului OLAP(mai jos în acest articol).

Actualizați fișierul cub offline cu date noi și regenerați fișierul cub offline. Consultați secțiunea actualizați și recreați fișierul cub offline(mai jos în acest articol).

BLOG

Doar postări de calitate

Ce sunt tabelele pivot Excel și cuburile OLAP

Urmărește videoclipul pentru articol:

OLAP- asta e engleza. Procesare analitică online, tehnologie analitică prelucrarea datelor în timp real. într-un limbaj simplu- un stocare cu date multidimensionale (Cube), si mai simplu - doar o baza de date din care poti obtine date in Excel si analiza cu ajutorul instrumentului Excel - PivotTables.

Tabele pivot- Acest interfața cu utilizatorul pentru a afișa date multidimensionale. Cu alte cuvinte - un tip special de tabele, cu care puteți face aproape orice raport.

Pentru a fi clar, să comparăm „Tabelul obișnuit” cu „Tabelul Pivot”

Masa obisnuita:

Masă rotativă:

Diferența principală Tabele pivot este prezența unei ferestre Lista câmpurilor din tabel pivot”, din care poți selecta câmpurile dorite și obține automat orice tabel!

Cum se utilizează

Deschis fisier Excel, care este conectat la cubul OLAP, de exemplu „BIWEB”:

Acum, ce înseamnă asta și cum să-l folosești?

Trageți și plasați câmpurile necesare pentru a obține, de exemplu, următorul tabel:

« Plusuri» vă permit să detaliați raportul. În acest exemplu, „Brand” este detaliat până la „Nume abreviate” și „Trimest” până la „Lună”, de exemplu. Asa de:

Funcții analitice în Excel (funcții cub)

Microsoft adaugă în mod constant noi funcții la Excel în ceea ce privește analiza și vizualizarea datelor. Lucrul cu informații în Excel poate fi reprezentat ca trei straturi relativ independente:

• date sursă organizate „corect”.
• matematica (logica) prelucrării datelor
• prezentarea datelor

Orez. 1. Analiza datelor în Excel: a) date brute, b) măsură c Power Pivot, c) tabloul de bord; Pentru a mări o imagine, faceți clic dreapta pe ea și selectați Deschide imaginea într-o filă nouă

Descărcați nota în format Word sau pdf, exemple în format Excel

Funcții cub și tabele pivot

Cele mai simple și în același timp foarte puternice mijloace de prezentare a datelor sunt tabelele pivot. Acestea pot fi construite din date conținute în: a) o foaie de lucru Excel, b) un cub OLAP sau c) un model de date Power Pivot. În ultimele două cazuri, pe lângă tabelul pivot, puteți folosi funcții analitice (funcții cub) pentru a genera un raport pe o foaie Excel. Tabelele pivot sunt mai ușoare. Funcțiile cubului sunt mai complexe, dar oferă mai multă flexibilitate, în special în raportare, așa că sunt utilizate pe scară largă în tablourile de bord.

Următoarea discuție se aplică formulelor cuburilor și tabelelor pivot bazate pe modelul Power Pivot și, în câteva cazuri, pe baza cuburilor OLAP.

O modalitate ușoară de a obține funcțiile cuburilor

Când (dacă) ați început să învățați codul VBA, ați învățat că cel mai simplu mod de a obține codul este înregistrarea unei macrocomenzi. În plus, codul poate fi editat, pot fi adăugate bucle, verificări etc.. În mod similar, cea mai ușoară modalitate de a obține un set de funcții cub este prin convertirea tabelului pivot (Fig. 2). Stați pe orice celulă a tabelului pivot, accesați fila Analiză, faceți clic pe butonul Facilităţi OLAP, și apăsați Convertiți în formule.

Orez. 2. Conversia unui tabel pivot într-un set de funcții cub

Numerele vor fi salvate, iar acestea nu vor fi valori, ci formule care extrag date din modelul de date Power Pivot (Fig. 3). Puteți formata tabelul rezultat. În special, puteți șterge și introduce rânduri și coloane în interiorul tabelului. Secțiunea rămâne și afectează datele din tabel. Când datele originale sunt actualizate, numerele din tabel vor fi de asemenea actualizate.

Orez. 3. Tabel bazat pe formule cub

Funcția CUBEVALUE().

Aceasta este probabil funcția principală a cuburilor. Este echivalent cu zona Valori masă rotativă. CUBEVALUE preia date dintr-un cub sau model Power Pivot și le afișează în afara unui tabel pivot. Aceasta înseamnă că nu sunteți limitat de limitele tabelului pivot și că puteți crea rapoarte cu nenumărate posibilități.

Scrierea unei formule de la zero

Nu trebuie să convertiți tabelul pivot finit. Puteți scrie orice formulă de cub de la zero. De exemplu, următoarea formulă este introdusă în celula C10 (Fig. 4):

Orez. 4. Funcția CUBEVALUE() din celula C10 returnează vânzările de biciclete pentru toți anii, ca în tabelul pivot

Mic truc. Pentru a facilita citirea formulelor cuburilor, este de dorit ca pe fiecare linie să fie plasat un singur argument. Puteți micșora fereastra Excel. Pentru a face acest lucru, faceți clic pe pictogramă Rulează pe fereastră situat în colțul din dreapta sus al ecranului. Și apoi reglați dimensiunea ferestrei pe orizontală. Opțiune alternativă– forțați textul formulei pentru a se încheia linie nouă. Pentru a face acest lucru, în bara de formule, plasați cursorul în locul în care doriți să efectuați un transfer și apăsați Alt + Enter.

Orez. 5. Minimizați fereastra

Sintaxa funcției CUBEVALUE().

Ajutorul Excel este complet precis și complet inutil pentru începători:

CUBEVALUE(conexiune, [element_expression1], [element_expression2], ...)

Conexiune este un argument necesar; un șir de text reprezentând numele conexiunii la cub.

expresie_element– argument optional; un șir de text reprezentând un MDX care returnează un element sau un tuplu în cub. În plus, „element_expression” poate fi un set definit folosind funcția CUBESET. Utilizați „member_expression” ca o felie pentru a determina partea din cub pentru care doriți să returnați o valoare agregată. Dacă nu este specificată nicio măsură în element_expression, va fi utilizată măsura implicită pentru acel cub.

Înainte de a continua să explicați sintaxa funcției CUBEVALUE, câteva cuvinte despre cuburi, modele de date și criptic tuplu.

Câteva informații despre cuburile OLAP și modelele de date Power Pivot

cuburi de date OLAP ( O n l ine A analitice P procesare - analiza datelor operaționale) au fost dezvoltate special pentru prelucrarea analitică și extracție rapidă dintre ele date. Imaginează-ți un spațiu tridimensional, în care axele sunt perioade de timp, orașe și bunuri (Fig. 5a). Nodurile unei astfel de grile de coordonate conțin valorile diferitelor măsuri: volumul vânzărilor, profitul, costurile, numărul de unități vândute etc. Acum imaginați-vă că există zeci, sau chiar sute de măsurători ... și există, de asemenea, un o mulțime de măsuri. Acesta va fi cubul OLAP multidimensional. Crearea, configurarea și actualizarea cuburilor OLAP este afacerea profesioniștilor IT.

Orez. 5a. cub 3D OLAP

Formulele analitice Excel (formule cub) extrag titlurile axelor (de exemplu, Timp), denumiri ale elementelor de pe aceste axe (august, septembrie), valori ale măsurilor la intersecția coordonatelor. Această structură permite tabelelor pivot și formulelor cuburilor să fie atât de flexibile și să se adapteze nevoilor utilizatorilor. Tabelele pivot bazate pe foile de lucru Excel nu folosesc măsuri, deci nu sunt la fel de flexibile în scopul analizei datelor.

Power Pivot este o caracteristică Microsoft relativ nouă. Acesta este un mediu Excel încorporat și oarecum independent, cu o interfață familiară. Power Pivot este cu mult superior tabelelor pivot standard. În același timp, dezvoltarea cuburilor în Power Pivot este relativ simplă și, cel mai important, nu necesită participarea unui specialist IT. Microsoft își realizează sloganul: „Business analytics – pentru mase!”. Deși modelele Power Pivot nu sunt 100% cuburi, ele pot fi numite și cuburi (vezi cursul introductiv Power Pivot al lui Mark Moore și ediția mai lungă a lui Rob Colley, Power Pivot DAX Formulas pentru mai multe detalii).

Componentele principale ale unui cub sunt dimensiunile, ierarhiile, nivelurile, elementele (sau membrii; în engleză membri) și măsurile (măsurile). Măsurare - caracteristica principală a datelor analizate. De exemplu, categoria de produs, perioada de timp, geografia vânzărilor. O dimensiune este ceva ce putem pune pe una dintre axele tabelului pivot. Fiecare dimensiune, pe lângă valorile unice, include un element care reunește toate elementele acestei dimensiuni.

Măsurătorile se bazează pe ierarhie. De exemplu, o categorie de produse poate fi împărțită în subcategorii, apoi în modele și în final în nume de produse (Fig. 5b).Ierarhia vă permite să creați date rezumative și să le analizați la diferite niveluri de structură. În exemplul nostru, ierarhia Categorie include 4 nivel.

Elemente(membri individuali) sunt prezenți la toate nivelurile. De exemplu, nivelul Categorie are patru elemente: Accesorii, Biciclete, Îmbrăcăminte, Componente. Alte niveluri au propriile lor elemente.

măsuri sunt valori calculate, cum ar fi volumul vânzărilor. Măsurile în cuburi sunt stocate în propria dimensiune, numită (vezi Figura 9 de mai jos). Măsurile nu au ierarhii. Fiecare măsură calculează și stochează o valoare pentru toate dimensiunile și toți membrii și secțiunile în funcție de membrii dimensiunii pe care îi plasăm pe axă. De asemenea, ei spun ce coordonate vom seta sau ce context de filtru vom seta. De exemplu, în fig. 5a în fiecare cub mic, se calculează aceeași măsură - Profit. Iar valoarea returnată de măsură depinde de coordonate. În dreapta, în figura 5a, se arată că Profit (în trei coordonate) pentru Moscova în octombrie pe mere = 63.000 de ruble. Măsura poate fi interpretată și ca una dintre măsurători. De exemplu, în fig. 5a în loc de o osie Bunuri, axa de plasare Măsuri cu elemente Volumul vânzărilor, Profit, Unități vândute. Apoi, fiecare celulă va avea o valoare, de exemplu, Moscova, septembrie, volumul vânzărilor.

Tuplu- mai multe elemente de dimensiuni diferite, precizând coordonatele de-a lungul axelor cubului, în care calculăm măsura. De exemplu, în fig. 5a Tuplu= Moscova, octombrie, mere. De asemenea, un tuplu valid este Perm, mere. Un altul este merele, august. Dimensiunile care nu sunt incluse în tuplu sunt implicit prezente în tuplu și sunt reprezentate de membrul implicit . Astfel, o celulă dintr-un spațiu multidimensional este întotdeauna definită de un set complet de coordonate, chiar dacă unele dintre ele sunt omise din tuplu. Nu puteți include două elemente de aceeași dimensiune într-un tuplu, sintaxa nu va permite acest lucru. De exemplu, tuplul invalid Moscova și Perm, mere. Pentru a implementa o astfel de expresie multidimensională, aveți nevoie de un set de două tupluri: Moscova și mere + Perm și mere.

Set de elemente– mai multe elemente de aceeași dimensiune. De exemplu, mere și pere. Set de tupluri- mai multe tupluri, fiecare fiind format din aceleași dimensiuni în aceeași succesiune. De exemplu, un set de două tuple: Moscova, mere și Perm, banane.

Completare automată pentru salvare

Să revenim la sintaxa funcției CUBEVALUE. Să folosim completarea automată. Începeți să introduceți o formulă într-o celulă:

Excel va sugera toate conexiunile disponibile în registrul de lucru Excel:

Orez. 6. Conexiunea la modelul de date Power Pivot se numește întotdeauna ThisWorkbookDataModel

Orez. 7. Conexiuni la cuburi

Să continuăm introducerea formulei (în cazul nostru, pentru modelul de date):

Completarea automată va sugera toate tabelele disponibile și măsurile modelului de date:

Orez. 8. Elemente disponibile ale primului nivel - nume de tabel și un set de măsuri (evidențiate)

Alege o pictograma Măsuri. Arata spre:

CUBEVALUE(» ThisWorkbookDataModel » ; » .

Completarea automată va sugera toate măsurile disponibile:

Orez. 9. Elemente disponibile ale celui de-al doilea nivel în setul de măsuri

Alegeți o măsură. Adăugați ghilimele, închiderea parantezei, apăsați Enter.

CUBEVALUE(" Acest model de date caiet de lucru " ; " . ")

Orez. 10. Formula CUBEVALUE într-o celulă Excel

În mod similar, puteți adăuga un al treilea argument la formulă:

VBA în Excel Excel.Obiect PivotTable și lucrul cu tabele pivot și cuburi OLAP în Excel

10.8 Lucrul cu tabele pivot (obiect PivotTable)

Obiect Excel.PivotTable, program de lucru cu tabele pivot și cuburi OLAP în Excel folosind VBA, obiectul PivotCache, creând un aspect de tabel pivot

În timpul funcționării majorității întreprinderilor, se acumulează așa-numitele date brute despre activități. De exemplu, pentru o întreprindere comercială, datele privind vânzările de mărfuri pot fi acumulate - pentru fiecare achiziție separat, pentru întreprinderi comunicare celulară- încărcați statistici pe stații de bazăși așa mai departe. Foarte des, conducerea unei întreprinderi are nevoie de informații analitice care sunt generate pe baza informațiilor brute - de exemplu, pentru a calcula contribuția fiecărui tip de produs la veniturile întreprinderii sau calitatea serviciului în zona unui anumit statie. Este foarte dificil să extrageți astfel de informații din informații brute: trebuie să executați interogări SQL foarte complexe care durează mult timp și adesea interferează cu munca în curs. Prin urmare, din ce în ce mai multe date brute în prezent sunt adunate mai întâi în Data Warehouse, iar apoi în cuburi OLAP, care sunt foarte convenabile pentru analiza interactivă. Cel mai simplu mod de a gândi cuburile OLAP este ca tabele multidimensionale, în care, în loc de cele două dimensiuni standard (coloane și rânduri, ca în tabelele obișnuite), pot exista o mulțime de dimensiuni. Termenul „secțional” este folosit în mod obișnuit pentru a descrie dimensiunile dintr-un cub. De exemplu, un departament de marketing poate avea nevoie de informații în funcție de timp, regiune, tip de produs, canal de vânzare și așa mai departe. Folosind cuburi (spre deosebire de interogările standard SQL), este foarte ușor să obțineți răspunsuri la întrebări precum „câte produse de acest tip au fost vândute în trimestrul IV al anului trecut în regiunea Nord-Vest prin distribuitori regionali.

Desigur, nu puteți crea astfel de cuburi în baze de date obișnuite. Cuburile OLAP necesită specializare produse software. SQL Server vine cu o bază de date OLAP de la Microsoft numită Analysis Services. Există soluții OLAP de la Oracle, IBM, Sybase etc.

Pentru a lucra cu astfel de cuburi, în Excel este încorporat un client special. În rusă se numește masă rotativă(pe ecran grafic este disponibil prin meniu Date -> masă rotativă), iar în engleză - masă rotativă. În consecință, obiectul pe care îl reprezintă acest client se numește PivotTable. Trebuie remarcat faptul că poate funcționa nu numai cu cuburi OLAP, ci și cu date obișnuite din tabele sau baze de date Excel, dar multe caracteristici se pierd.

PivotTable și obiectul PivotTable sunt produse software de la Panorama Software care au fost achiziționate de Microsoft și integrate în Excel. Prin urmare, lucrul cu obiectul PivotTable este oarecum diferit de lucrul cu alte obiecte Excel. Adeseori este dificil să-ți dai seama ce să faci. Prin urmare, este recomandat să utilizați în mod activ reportofonul macro pentru a primi indicii. În același timp, atunci când lucrează cu tabele pivot, utilizatorii trebuie adesea să efectueze aceleași operațiuni repetitive, astfel încât automatizarea este necesară în multe situații.

Cum arată să lucrezi programatic cu un tabel pivot?

Primul lucru pe care trebuie să-l facem este să creăm un obiect PivotCache care va reprezenta setul de înregistrări preluate din sursa OLAP. Foarte condiționat, acest obiect PivotCache poate fi comparat cu QueryTable. Numai un singur obiect PivotCache poate fi utilizat pentru fiecare obiect PivotTable. Obiectul PivotCache este creat folosind metoda Add() a colecției PivotCaches:

Dim PC1 ca PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches este o colecție standard, iar dintre metodele care merită o atenție detaliată, numai metoda Add() poate fi numită în ea. Această metodă ia doi parametri:

• SourceType- obligatoriu, definește tipul sursei de date pentru tabelul pivot. Puteți alege să creați un tabel pivot pe baza unui interval în Excel, date dintr-o bază de date, o sursă de date externă, un alt tabel pivot și așa mai departe. În practică, de obicei are sens să utilizați OLAP numai atunci când există o mulțime de date - în consecință, este nevoie de stocare externă specializată (de exemplu, Microsoft Analysis Services). În această situație, este selectat xlExternal.
• Date sursă- obligatoriu în toate cazurile, cu excepția cazului în care valoarea primului parametru este xlExternal. Strict vorbind, definește intervalul de date pe baza căruia va fi creat PivotTable. De obicei ia un obiect Range.

Următoarea sarcină este să configurați parametrii obiectului PivotCache. După cum sa menționat deja, acest obiect este foarte asemănător cu QueryTable, iar setul său de proprietăți și metode este foarte asemănător. Unele dintre cele mai importante proprietăți și metode sunt:

• ADOConnection- capacitatea de a returna un obiect ADO Connection care este creat automat pentru a se conecta la o sursă de date externă. Este folosit pentru setari aditionale proprietățile conexiunii.
• conexiune- funcționează exact la fel ca proprietatea obiectului QueryTable cu același nume. Poate accepta un șir de conexiune, un obiect Recordset gata făcut, fisier text, cerere web. fișier Microsoft interogare. Cel mai adesea, atunci când lucrați cu OLAP, un șir de conexiune este scris direct (din moment ce nu are sens să primiți un obiect Recordset, de exemplu, pentru a schimba datele - sursele de date OLAP sunt aproape întotdeauna doar pentru citire). De exemplu, setarea acestei proprietăți pentru a se conecta la baza de date Foodmart (baza de date de probă Analysis Services) pe serverul LONDRA ar putea arăta astfel:

PC1.Conexiune = "OLEDB; Furnizor=MSOLAP.2; Sursa datelor=LONDRA1; Catalog inițial = FoodMart 2000"

• proprietăți Tip de comandăȘi textul de comandă descrieți tipul de comandă care este trimisă către serverul bazei de date și textul comenzii în sine în același mod. De exemplu, pentru a accesa cubul de vânzări și a-l stoca în întregime în cache pe client, puteți utiliza cod similar

• proprietate LocalConnection vă permite să vă conectați la un cub local (fișier *.cub) creat de Excel. Desigur, este foarte descurajat să folosiți astfel de fișiere pentru a lucra cu volume de date „de producție” - numai în scopul creării de machete etc.
• proprietate MemoryUsed returnează numărul memorie cu acces aleator, folosit de PivotCache. Dacă PivotTable bazat pe acest PivotCache nu a fost încă creat și deschis, returnează 0. Poate fi folosit pentru a verifica dacă aplicația dvs. va funcționa pe clienți slabi.
• proprietate OLAP returnează True dacă PivotCache este conectat la serverul OLAP.
• OptimizeCache- capacitatea de a optimiza structura cache-ului. Încărcarea inițială a datelor va dura mai mult, dar apoi viteza de lucru poate crește. Pentru sursele OLE DB nu funcționează.

Proprietățile rămase ale obiectului PivotCache sunt aceleași cu cele ale obiectului QueryTable și, prin urmare, nu vor fi discutate aici.

Metoda principală a obiectului PivotCache este metoda CreatePivotTable(). Cu ajutorul acestei metode, se realizează următoarea etapă - crearea unui tabel pivot (obiect PivotTable). Această metodă ia patru parametri:

• TableDestination este singurul parametru necesar. Acceptă un obiect Range, în colțul din stânga sus al căruia va fi plasat tabelul pivot.
• numele tabelului- Numele tabelului pivot. Dacă nu este specificat, atunci numele formularului „PivotTable1” va fi generat automat.
• citiți datele- dacă este setat la True, atunci tot conținutul cubului va fi memorat automat în cache. Trebuie să fiți foarte atenți cu acest parametru, deoarece utilizarea lui incorectă poate crește dramatic sarcina asupra clientului.
• DefaultVersion- Această proprietate nu este de obicei specificată. Vă permite să specificați versiunea tabelului pivot care este creată. În mod implicit, este utilizată cea mai recentă versiune.

Crearea unui tabel pivot în prima celulă a primei foi a registrului de lucru ar putea arăta astfel:

PC1.CreatePivotTable Range ("A1")

Tabelul pivot a fost creat, dar imediat după creare este gol. Oferă patru zone în care puteți plasa câmpuri din sursă (pe ecranul grafic, toate acestea pot fi configurate fie folosind fereastra Lista câmpurilor din tabel pivot- se deschide automat sau cu un buton Aspect pe ultimul ecran al expertului PivotTable):

• zona coloanei- conține acele dimensiuni („secțiune” în care vor fi analizate datele), ai căror membri sunt mai puțini;
• zona liniei- acele dimensiuni, ai căror membri sunt mai mulți;
• zona paginii- acele măsurători prin care este necesar doar să se filtreze (de exemplu, să se afișeze date doar pentru cutare și cutare regiune sau doar pentru cutare și cutare an);
• zona de date- de fapt, partea centrală a mesei. Acele date numerice (de exemplu, valoarea vânzărilor) pe care le analizăm.

Este dificil să te bazezi pe utilizator pentru a plasa corect elementele în toate cele patru zone. De asemenea, poate dura anumit timp. Prin urmare, este adesea necesar să aranjați datele într-un PivotTable în mod programatic. Această operație este efectuată folosind obiectul CubeField. Proprietatea principală a acestui obiect este Orientarea, aceasta determină unde va fi amplasat acest sau acel câmp. De exemplu, să punem dimensiunea Clienți în zona coloanei:

PT1.CubeFields("").Orientation = xlColumnField

Apoi - dimensiunea Timp în zona șirurilor:

PT1.CubeFields(""). Orientare = xlRowField

Apoi - dimensiunea Produs în zona paginii:

PT1.CubeFields(""). Orientare = xlPageField

Și, în sfârșit, indicatorul (date numerice pentru analiză) Vânzări unitare:

PT1.CubeFields(".").Orientation = xlDataField

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”

Prin completarea acestui subiect, veți:

1. Aflați cum să rulați foi de calcul;

2. Fixați conceptele 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.

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

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

Documente create cuEXCELA , sunt numitecarnete de lucru și au extensia. XLS. Noul registru de lucru are trei foi de lucru numite SHEET1, SHEET2 și SHEET3. Aceste nume sunt listate în filele de foi din partea de jos a ecranului. Pentru a comuta la o altă foaie, faceți clic pe numele acestei foi.

Acțiuni din foaia de lucru:

Redenumirea unei foi de lucru. Plasați indicatorul mouse-ului pe coloana foii de lucru și faceți dublu clic pe butonul din stânga sau apelați meniul contextualși selectați Redenumire.Denumiți foaia „ANTRENAMENT”

Introduceți o foaie 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 denumiți-o „Eșantion” .

Ștergerea unei foi de lucru. Selectați fila „Foaie 2” și folosind meniul contextualșterge .

Celule și intervale de celule.

Spațiul de lucru este format din rânduri și coloane. Rândurile sunt numerotate de la 1 la 65536. Coloanele sunt notate cu litere latine: A, B, C, ..., AA, AB, ..., IV, în total - 256. Există o celulă la intersecția unui rând și o coloană. Fiecare celulă are propria sa adresă: numele coloanei și numărul rândului la intersecția căruia se află. De exemplu, A1, CB234, 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 de la 8 la 20.

A:A - toate celulele din coloana A.

N:R - toate celulele din coloanele de la H la R.

Adresa celulei poate include numele foii de lucru: Sheet8!A3:B6.

2. Selectați celule în Excel

Ceea ce subliniem

Acțiuni

o celulă

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

şir

Făcând clic pe un număr de linie.

Coloană

Făcând clic pe numele unei coloane.

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

Făcând clic pe butonul „Selectează toate” (butonul gol din stânga numelor coloanelor)

Puteți modifica lățimea coloanei și înălțimea rândului 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 capătul 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 ultimei rânduri în celula B3.

3. În EXCEL, puteți introduce următoarele tipuri de date:

Numerele.

Text (cum ar fi titluri și materiale explicative).

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

Formule.

Datele sunt introduse în celule. Pentru a introduce date, trebuie selectată celula dorită. Există două moduri de a introduce date:

Doar faceți clic într-o celulă și introduceți datele dorite.

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

Apasa Enter.

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

4. Modificați datele.

Selectați o celulă ș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 expresie aritmetică sau logică prin care se fac calcule într-un tabel. Formulele constau din referințe de celule, semne de operație și funcții. Doamna EXCEL are un set foarte mare de 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.

Formulele încep întotdeauna cu un semn egal. După introducerea formulei în celula corespunzătoare, apare rezultatul calculului, 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

/

Divizia

A1 / B5

Exponentiație

A4 ^3

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

semne de relație

A2

Puteți folosi parantezele în formule pentru a schimba ordinea acțiunilor.

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ă o modalitate mult mai 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 se află un pătrat mic - mânerul de umplere.

Deplasați cursorul mouse-ului peste mânerul de umplere (se va transforma într-o cruce) în timp ce țineți apăsat butonul din stanga mouse-ul, 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 întindeți zona de selecție cu marker la dimensiunea dorită.

Documentul selectat de vizualizat 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 tip diferit: text, numeric, formule.

Exercițiu: Efectuați introducerea datelor necesare și calcule 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”. Remediați datele din celulă în orice mod pe care îl cunoașteți.

3. La celulăÎN 1 introduceți numărul - anul înființării școlii (1971).

4. La celulăC1 introduceți un număr - 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. Evidențiați o celulăD1 , utilizați tastatura pentru a introduce formula pentru calcularea vârstei școlii:=C1-B1

Atenţie! Formulele încep î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 cu mouse-ul.

6. Ștergeți conținutul unei celuleD1 și reintroduceți formula folosind mouse-ul. Într-o celulăD1 instalați semnul«=» , apoi faceți clic pe celulăC1, rețineți că adresa acestei celule a apărutD1, pune un semn«–» și faceți clic pe celulăB1 , faceți clic(Introduce).

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 de calcul al vârstei în anul curent(=C2-B2).

11. Evidențiați o celulăC2. Introduceți numărul de anul viitor. Observaț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.

Muncă independentă

Exercițiu: Calculați, folosind ET, 130 de ruble sunt suficiente pentru a cumpăra toate produsele pe care mama le-a comandat pentru dvs. și va fi 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 Limba engleză!
o În formule, în loc de variabile, se scriu numele celulelor.
o După apăsarea Enter în locul formulei, apare imediat un număr - rezultatul calculului

o Calculați singur totalul.

Arată-i profesorului rezultatul!

Documentul selectat de vizualizat Excel pr.r. 3.docx

Bibliotecă
materiale

Lucrări practice 3

MS Excel. Creați și editați document foaie de calcul»

Prin completarea acestui 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.

Exercițiu:

1. Creați un tabel care să conțină orarul trenurilor de la gara Saratov la gara Samara. Forma generală tabelul „Program” este afișat în figură.

2. Selectați celulaA3 , schimbați cuvântul „Golden” în „Great” și apăsați tastaintroduce .

3. Selectați celulaA6 , faceți clic stânga pe el de două ori și înlocuiți „Gloomy” 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 orei opririlor de tren în fiecare localitate. (inserați coloane) Calculați timpul total de oprire, timpul total de călătorie, timpul necesar cu trenul pentru a călători de la unul localitate altcuiva.

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 o echipăA tăia .
Plasați cursorul în celula D1;
Executați comandaIntroduce ;
Justificaț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 localitate.

4. Trebuie să copiați formula pentru a bloca 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, apucându-l, extindeți formula până la celula C7.

5. Introduceți în celula E1 textul „Timp călătorie”. 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-o locație în alta.

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 - Ora și setați parametri (ore:minute) .

apasa 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 tastaintroduce .

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 recalculeaza automat suma din celula C9!!!

Executați comanda Anulare 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. Colorează tabelul și evidențiază marginile tabelului.

Muncă independentă

Calculați cu o foaie de calculexcelacheltuielile şcolarilor care urmează să plece în excursie în alt oraş.

Documentul selectat de vizualizat Excel pr.r. 4.docx

Bibliotecă
materiale

Lucrări practice 4

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

Prin completarea acestui subiect, veți învăța:

Efectuați operațiuni de copiere, mutare și completare automată a celulelor și intervalelor individuale.

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

Utilizați elementele matematice și statistice încorporate Funcții Excel.

MS Excel conține 320 de funcții încorporate. Cel mai simplu mod primind informatii complete despre oricare dintre ele este să folosești meniulReferinţă . Pentru comoditate, funcțiile din Excel sunt împărțite în categorii (matematice, financiare, statistice etc.).
Apelul către fiecare funcție constă din două părți: numele funcției și argumentele dintre paranteze.

Masa. Funcții încorporate în Excel

* Scris fără argumente.

Masa . Tipuri de linkuri

Exercițiu.

1. Costul de 1 kWh este stabilit. electricitate și citiri ale contoarelor pentru lunile precedente și curente. Este necesar să se calculeze consumul de energie electrică pentru 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 - centrat, vertical - centrat, afișare - împachetare 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 autocompletare pentru a completa numerotarea apartamentelor până la 7 inclusiv.

5. Completați celulele B4:C10 conform imaginii.

6. În celula D4, introduceți formula pentru aflarea consumului de energie electrică/energie. Și completați rândurile de mai jos cu simbolul 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 cu simbolul de completare automată.

Notă!
La completarea automată, adresa celulei B1 nu se schimbă,
deoarece se stabilește o referință absolută.

8. În celula A11, introduceți textul „Date statistice”, selectați celulele A11:B11 și faceți clic pe butonul „Merge and Center” din bara de instrumente.

9. În celulele A12:A15, introduceți textul prezentat în figură.

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

11. În mod similar, funcțiile sunt setate în celulele B13:B15.

12. Ați efectuat calculele pe Foaia 1, redenumiți-o în Electricitate.

Muncă independentă

Exercitiul 1:

Calculați-vă vârsta din anul curent până în 2030 folosind simbolul de completare automată. Anul nașterii tale este o 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ă fie formule: AVERAGE, COUNTIF, MAX, MIN. celuleB3: H12 sunt completate cu informații de către dvs.

Documentul selectat de vizualizat Excel pr.r. 5.docx

Bibliotecă
materiale

Lucrări practice 5

Prin completarea acestui 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.

Exercitiul 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). Remediați introducerea datelor.

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 reprezentat ca dată, caz în care ar trebui convertit într-un tip numeric.

Sarcina 2. Vârsta elevilor. Conform unei liste date de studenți și a datei 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. Prin LAN: deschideți folderul Locațiile mele din rețea -Șeful-Acte generale - Clasa a 9-a, găsiți fișierul Vârstei. 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. Calculați vârsta elevilor. Pentru a calcula vârsta, trebuie să utilizați funcțiaASTĂZI selectați data curentă de astăzi, din ea se scade data nașterii elevului, apoi din data rezultată folosind funcția AN, din dată se extrage doar anul. Din numărul rezultat, scădeți 1900 - secol și obțineți vârsta elevului. În celula D3, scrieți formula=AN(AZI()-S3)-1900 . Rezultatul poate fi prezentat ca dată, caz în care ar trebui convertit întip numeric.

3. Stabiliți cea mai devreme zi de naștere. În celula C22, scrieți formula=MIN(C3:C21) ;

4. Determinați cel mai tânăr student. În celula D22, scrieți formula=MIN(D3:D21) ;

5. Stabiliți ultima zi de naștere. În celula C23, scrieți formula=MAX(C3:C21) ;

6. Determinați cel mai în vârstă elev. În celula D23, scrieți formula=MAX(D3:D21) .

Muncă independentă:
Sarcină. Faceți calculele necesare ale creșterii elevilor în diferite unități de măsură.

Documentul selectat de vizualizat 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 clasei, inclusiv punctajul 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:
Completați tabelul cu datele inițiale și efectuați calculele necesare.Acordați atenție formatului valorilor din celulele „GPA” (numeric) și „Data nașterii” (data)

Tabelul folosește coloane suplimentare care sunt necesare pentru a răspunde la întrebările puse în sarcină -vârsta elevului și este studentulelevă și fată excelentă simultan.
Pentru a calcula vârsta, a fost utilizată următoarea formulă (folosind exemplul celulei G4):

=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 final, evidenţiind întreaga parte, - vârsta elevului.

Dacă o fată este o elevă excelentă este determinat de formula (folosind exemplul celulei H4):

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

Să începem cu calculele de bază.
În primul rând, este necesar să se determine 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 foi de calcul.

=SUMĂDĂ(F4:F15; „L”, D4:D15)/NUMĂRĂDAC(F4:F15, „W”)

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

=SUM(H4:H15)/COUNTIF(F4:F15;"W")

În cele din urmă, determinăm 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.

Documentul selectat de vizualizat Excel pr.r. 7.docx

Bibliotecă
materiale

Lucrări practice 7

„Crearea diagramelor folosind MS Excel”

Prin completarea acestui 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 aspectul acesteia.

Ce este o diagramă. Graficul este destinat reprezentării grafice a datelor. Liniile, barele, coloanele, sectoarele și alte elemente vizuale sunt folosite pentru a afișa datele numerice introduse în celulele tabelului. Aspectul unei diagrame depinde de tipul acesteia. Toate diagramele, cu excepția diagramei circulare, au două axe: cea orizontală este axa categoriei și cea verticală este axa valorii. La crearea diagramelor 3D, se adaugă o a treia axă - axa seriei. Adesea, o diagramă conține elemente precum o grilă, titluri și o legendă. Grilele sunt extensii ale diviziunilor găsite pe axe, titlurile sunt folosite pentru a explica elementele individuale ale diagramei și natura datelor prezentate pe acesta, legenda ajută la identificarea seriei de date prezentate pe diagramă. Există două moduri de a adăuga diagrame: încorporați-le în foaia de lucru curentă și adăugați o foaie de diagramă separată. În cazul în care diagrama în sine este de interes, atunci aceasta este plasată pe o foaie separată. Dacă trebuie să vizualizați simultan diagrama și datele pe baza cărora a fost construită, atunci este creată o diagramă încorporată.

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

După ce diagrama este generată, va fi posibilă modificarea acesteia. Înainte de a efectua orice acțiune cu elementele diagramei, selectați-le făcând clic pe ele cu butonul stâng al mouse-ului. După aceea, apelați meniul contextual cu butonul drept al mouse-ului sau utilizați butoanele corespunzătoareBara de instrumente pentru diagrame .

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

Tehnologia de lucru:

1. Porniți foaia de calcul Excel.

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

3. Selectați intervalul de celule A1:B1, aliniați textul din celule la centru.

4. În celula A2, introduceți -6, iar în celula A3, introduceți -5. Completați celulele de mai jos cu marcatorul de completare automată până la parametrul 6.

5. În celula B2, introduceți formula: =3,5*A2–5. Utilizați simbolul 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 setați-i limitele exterioare și interioare.

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

8. Selectați restul celulelor tabelului și umpleți zona interioară cu o culoare diferită.

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

10. Mutați graficul sub tabel.

Muncă independentă:

Trasează 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-Orase).

Calculați costul conversației fără reducere (coloana D) și costul conversației cu reducere (coloana F).

Pentru o reprezentare vizuală, construiți două diagrame circulare. (1- diagrama costului unei conversații fără reducere; 2- diagramă a costului unei conversații cu reducere).

Documentul selectat de vizualizat Excel pr.r. 8.docx

Bibliotecă
materiale

Lucrări practice 8

CONSTRUIRE GRAFICE ȘI DESENE CU MIJLOCURI MS EXCEL

1. Construirea unui desen"UMBRELĂ"

Iată funcțiile ale căror grafice sunt implicate în această imagine:

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, хО

y5= 2(X+3) 2 – 9, хн[-4;0]

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

- Rulați MS EXCEL

- Într-o celulăA1 introduceți desemnarea variabileiX

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

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

Ordinea acțiunilor:

Setați cursorul pe o celulăÎN 1 si intray1

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

Clic introduce pe tastatură

Valoarea funcției este calculată automat.

Extindeți formula în celula A26

La fel și într-o celulăC10 (deoarece găsim valoarea funcției doar pe intervalul x din [-4; 4]) introducem formula pentru graficul funcțieiy2= ​​​​-1/8x 2 +6. ETC.

Rezultatul ar trebui să fie următorul ET

După ce toate valorile funcțiilor sunt 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țiLocalizare → Selectare vedere dorită→ Apăsați Bine .

Rezultatul ar trebui să fie următoarea imagine:

Sarcina pentru munca individuala:

Reprezentați graficele funcțiilor într-un sistem de coordonate.x de la -9 la 9 în pași de 1 . Ia un desen.

1. „Puncte”

2. „Pisica” Filtrarea (selectarea) datelor în tabel vă permite să afișați numai acele rânduri, al căror conținut al celulelor îndeplinește condiția specificată sau mai multe condiții. Spre deosebire de sortare, datele nu sunt reordonate la filtrare, ci doar acele înregistrări care nu îndeplinesc criteriile de selecție specificate sunt ascunse.

Filtrarea datelor se poate face în două moduri:folosind filtrul automat sau filtrul avansat.

Pentru a utiliza filtrul automat aveți nevoie de:

o setați cursorul în interiorul tabelului;

o alege o echipaDate - Filter - Autofilter;

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

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

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

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

Un filtru 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 criterii de selecție pe mai multe coloane. Filtrarea înregistrărilor folosind filtrul avansat se realizează cu ajutorul comenzii de meniuDate - Filtru - Filtru avansat.

Exercițiu.

Creați un tabel conform exemplului prezentat în figură. Salvați-l ca Sort.xls.

Tehnologia de executare a sarcinilor:

1. Deschideți documentul Sort.xls

2.

3. Executați o comandă de meniuDate - Sortare.

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

Amintiți-vă că trebuie să tipărim o listă de articole rămase în magazin (având un sold diferit de zero) în fiecare zi, dar pentru a face acest lucru, trebuie mai întâi să obținem o astfel de listă, de exemplu. filtrați datele.

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

6. Executați o comandă de meniuDate - Filtru

7. Deselectați tabelele.

8. Fiecare celulă din antetul tabelului are acum un buton „Săgeată în jos”, acesta nu este imprimat, permițându-vă să setați criteriile de filtrare. Dorim să lăsăm toate intrările cu un rest diferit de zero.

9. Faceți clic pe butonul săgeată care apare în coloanăSuma ramasa . Se va deschide o listă din care puteți selecta. Selectați liniaCondiție. Setați condiția: > 0. Faceți clicBine . Datele din tabel vor fi filtrate.

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

11. Filtrul poate fi îmbunătățit. 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 mărfurilor nevândute pentru toate departamentele, trebuie să selectați criteriul „Toate” din lista „Departament”.

13. Pentru a nu vă încurca în rapoartele dvs., introduceți o dată care se va schimba automat în funcție de timpul sistemului calculatorFormule - Funcție de inserare - Data și ora - Azi .

Muncă 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 dintr-o clasă (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 barca. Nikolai a înotat 150 km pe un vapor cu aburi, a călătorit 140 km într-un tren și a zburat 1.100 km într-un avion. Vasily a navigat 200 km cu un vapor, a călătorit 220 km într-un tren și a zburat 1.160 km într-un avion. Anatoly a zburat 1200 km cu un avion, a călătorit 110 km într-un tren și a navigat 125 km pe un vapor cu aburi. 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 parcurși de fiecare dintre băieți.

Calculați numărul total de kilometri pe care băieții i-au parcurs în tren, au zburat în avion și au navigat pe barcă (fiecare mod 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 pentru toate modurile de transport.

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

2.2 sarcină (2 puncte).

Creați un tabel „Lacurile Europei” folosind următoarele date pentru suprafață (km pătrați) și adâncimea cea mai mare (m): Ladoga 17.700 și 225; Onega 9510 și 110; Marea Caspică 371.000 și 995; Venern 5550 si 100; Chudskoye cu Pskov 3560 și 14; Balaton 591 și 11; Geneva 581 și 310; Vättern 1900 și 119; Constanța 538 și 252; Mälaren 1140 și 64. Determinați cel mai mare și cel mai mic lac din punct de vedere al suprafeței, cel mai adânc și cel mai puțin adânc lac.

2.3 sarcină (2 puncte).

Creați un tabel „Râurile Europei” folosind următoarele date privind lungimea (km) și zona 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; Seine 780 și 79; Tamisa 340 și 15. Determinați cele mai lungi și scurte râuri, calculați suprafața totală a bazinelor hidrografice, lungimea medie a râurilor din partea europeană a Rusiei.

3 sarcini (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 se stabilesc penalități: dacă firma a rambursat împrumutul cu mai mult de 70 la sută, atunci amenda va fi de 10 la sută din datorie, î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,