Cum se creează un câmp calculat în excel. Coloane calculate în Transact-SQL Cum se creează o coloană calculată

În acest articol vom vorbi despre câmpurile calculate în Interogări de acces. O interogare, cum ar fi un tabel, poate efectua calcule pe valori numerice, șir sau date pentru fiecare înregistrare folosind date din unul sau mai multe câmpuri. Rezultatul calculului formează un nou câmp calculat în tabelul de interogări. Spre deosebire de câmpurile de tabel calculate, câmpurile calculate din tabelele bazei de date sursă nu creează câmpuri noi. De fiecare dată când se execută interogarea, se fac calcule pe baza valorilor câmpului curent.

Expresiile de câmp calculate pot folosi constante și funcții în plus față de numele câmpurilor. Ca rezultat al procesării unei expresii, se poate obține o singură valoare.

Sarcina 1. Tabelul PRODUCT are câmpurile PREț și RATE_TVA Calculați prețul cu TVA și comparați-l cu prețul obținut în câmpul calculat din tabelul Preț cu TVA.

1. Creați o interogare de selectare în modul proiectare pentru tabelul PRODUCT. Trageți câmpurile NUME_TOV, PREȚ, RATE_TVA și Preț cu TVA în formularul de solicitare (Fig. 4.6).
2. Pentru a calcula prețul inclusiv TVA, creați un câmp calculat scriind expresia [PRț]+[PRț]*[RATE_VAT] în celula goală a rândului Câmp.
3. Pentru a selecta înregistrări cu o valoare mai mare de 5000 în câmpul calculat, introduceți > 5000 în linia Criterii
4. După introducerea unei expresii, sistemul generează implicit numele câmpului calculat Expresia 1, care devine antetul coloanei din tabel cu rezultatele interogării. Acest nume va fi inserat înaintea expresiei [PRICE]+[PRICE]*[VAT_RATE]. Pentru fiecare câmp calculat nou din interogare, numărul expresiei crește cu unu. Numele câmpului calculat este separat de expresie prin două puncte. Pentru a schimba numele, plasați cursorul mouse-ului în câmpul calculat al formularului de solicitare și faceți clic pe butonul dreapta al mouse-ului. Din meniul contextual, selectați Proprietăți(Proprietăți) și în linia Legendă introduceți un nou nume de câmp - Pret cu TVA1. Acum, în tabelul cu rezultatele interogării, acest nume va fi afișat în antetul coloanei calculate. Numele câmpului poate fi corectat și direct în formularul de solicitare.
5. Pentru a afișa rezultatul interogării, faceți clic pe butonul A executa(Alergă) în grup rezultate(Rezultate). Câmpul calculat al tabelului și interogarea au aceleași valori.
6. Modificați prețul produsului într-una dintre înregistrările de solicitare. Valorile din ambele câmpuri calculate vor fi recalculate instantaneu.
7. Pentru a forma o expresie complexă într-un câmp calculat sau într-o condiție de selecție, este recomandabil să utilizați generatorul de expresii. Generatorul vă permite să selectați numele câmpurilor necesare în expresie din tabele, interogări, semne de operație și funcții. Eliminați expresia din câmpul calculat și utilizați generatorul pentru ao genera.
8. Apelați Expression Builder făcând clic pe butonul Constructor(Constructor) în grup Configurare interogare(Configurare interogare) panglică Design sau selectând Construi(Build) în meniul contextual. Cursorul mouse-ului trebuie să fie poziționat anterior în celula de introducere a expresiei.
9. În partea stângă a ferestrei Generator de expresii(Expression Builder) (Fig. 4.7) selectați tabelul PRODUCT pe care se bazează interogarea. O listă a câmpurilor sale va fi afișată în partea dreaptă. Selectați secvențial câmpurile și operatorii necesari făcând dublu clic pe ele în expresie. Expresia va fi formată în partea de sus a ferestrei. Vă rugăm să rețineți că constructorul a indicat numele tabelului căruia îi aparține înaintea numelui câmpului și l-a separat de numele câmpului cu un semn de exclamare.
10. Finalizați procesul de construire a unei expresii într-un câmp calculat făcând clic pe butonul OK.
11. Salvați cererea sub numele ― Preț cu TVA și închideți-o.
12. Executați fișierul salvat selectându-l în zona de navigare și selectând Deschidere din meniul contextual.

Sarcina 2. Puteți utiliza funcții încorporate în câmpuri calculate și condiții de filtrare. Accesul are peste 150 de funcții definite.
Să fie necesar să selectați toate facturile pentru care s-a efectuat expedierea într-o anumită lună. În FACTURA, data expedierii este stocată în câmpul DATE_OTG cu tipul de date Data/Ora.

1. Creați o interogare de selectare în modul de proiectare pentru tabelul INVOICE. Trageți câmpurile NOM_NAKL și CODE_SK în formular (Fig. 4.8).
2. Creați un câmp calculat într-o celulă de rând goală Camp(Câmp) scriind acolo una dintre expresiile: Format([FACTURA]![DATA_DISPOSIȚIEI];"mmmm") - această funcție va returna numele complet al lunii
   sau Format([INBOOK]![DISPOSITION_DATE];"mm") - această funcție va returna numărul lunii.
3. Pentru a selecta facturile emise într-o anumită lună, în câmpul calculat din linia Condiție de selecție (Criterii), introduceți numele lunii, de exemplu martie (Fig. 4.8), sau numărul lunii, de exemplu 3 în conformitate cu parametrul în funcția Format.
4. Completează-ți solicitarea făcând clic pe butonul A executa(Alergă) în grup rezultate(Rezultate) pe fila panglică Lucrul cu cereri | Constructor(Instrumente de interogare | Design).
5. Scrieți funcția Month(INVOICE!DATE_OTG) în câmpul calculat și asigurați-vă că această funcție returnează numărul lunii extras din dată.
6. Pentru a selecta toate rândurile aferente celui de-al doilea trimestru, în rândul Criterii, introduceți operatorul Între 4 și 6, care determină dacă valoarea expresiei se încadrează în intervalul specificat.
7. Scrieți expresia MonthName(Month(INVOICE!DATE_OTG)) în câmpul calculat și asigurați-vă că funcția MonthName convertește numărul lunii în numele complet.

Pentru a consolida acest lucru, urmăriți tutorialul video.

Atunci când analizați date folosind PivotTables, adesea trebuie să includeți în raport valori care rezultă din calcule care au fost efectuate în afara setului de date inițial. programul Excel oferă utilizatorului mijloacele de a efectua calcule în tabele pivot folosind câmpuri calculate și articole calculate. În fig. Figura 1 arată cum un câmp calculat poate prezenta date dintr-o perspectivă diferită. Tabelul pivot afișează volumul vânzărilor și timpul (în ore) petrecut vânzând echipamente pe fiecare piață. Un câmp calculat care calculează venitul mediu pe oră îmbunătățește capacitatea de a analiza datele într-un tabel pivot.

Orez. 1. Trebuie să creați un câmp calculat într-un tabel pivot existent Venitul mediu pe oră, care va da un nou sens datelor analizate

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

Un câmp calculat este un câmp de date care este creat ca rezultat al calculelor bazate pe câmpurile PivotTable existente. Câmpul calculat este adăugat la setul de date ca o coloană virtuală. Această coloană nu este inclusă în datele sursă, conține valori determinate de o formulă și interacționează cu datele PivotTable în același mod ca și alte câmpuri PivotTable.

Cu câmpurile calculate, puteți insera o formulă într-un tabel pivot, permițându-vă să vă creați propriul câmp. Datele nou create vor deveni parte a tabelului pivot, interacționând cu datele existente. Când actualizați tabelele pivot, câmpurile calculate sunt recalculate, iar tabelul pivot însuși este umplut cu informații care nu se aflau în setul de date original.

Acum uitați-vă la fig. 5.1 și întrebați-vă: De ce a trebuit să adăugați câmpuri calculate? De ce să nu folosiți formule obișnuite pentru celule sau să efectuați calculele necesare direct pe tabelul sursă pentru a obține informațiile de care aveți nevoie? Pentru a răspunde la aceste întrebări, să ne uităm la diferitele metode pe care le puteți utiliza pentru a crea câmpul calculat prezentat în Figura 1.

Metoda 1: Adăugați un câmp calculat la sursa de date

Puteți adăuga un câmp calculat la sursa dvs. de date, așa cum se arată în figură. 2, permițând Tabelului Pivot să utilizeze acest câmp ca câmp de date standard. Dacă aranjarea informațiilor pe foaia sursei de date permite acest lucru, atunci care ar putea fi cea mai bună soluție. Apropo, adăugarea unei noi coloane va necesita nu doar actualizarea tabelului pivot, ci și schimbarea domeniului pe care se bazează pivotul. Pentru a face acest lucru, faceți clic pe rezumat și parcurgeți meniul Analiză → Sursă de date → Sursă de date.

Încerc să transform datele originale într-un Tabel (ceea ce se face în Fig. 2 și în fișierul Excel atașat). În acest caz, în primul rând, titlurile sunt evidențiate, în al doilea rând, apar butoane cu filtre, ceea ce este adesea util pentru explorarea datelor, în al treilea rând, rândurile sunt colorate pe rând și, în sfârșit, cel mai important lucru, atunci când adăugați rânduri și coloane nu este necesar să se modifice zona de date pentru un tabel pivot. Zona specificată ca atare nu este un interval dreptunghiular, ci numele Tabelului. În cazul nostru - Sursă(Fig. 3).

Orez. 3. Formarea unui rezumat pe baza unui instrument special Excel – Tabel

Dar această metodă are și dezavantaje: în primul rând, nu este întotdeauna convenabil să adăugați o coloană la datele sursă și, în al doilea rând, posibilitățile sunt limitate dacă structura datelor sursă se modifică (de exemplu, exportați date din 1C și noul export are încă o coloană; această nouă coloană va suprascrie coloana de calcul).

Metoda 2: Utilizați o formulă în afara Tabelului Pivot pentru a crea un câmp calculat

Puteți adăuga un câmp calculat lângă un tabel pivot. În fig. 4 fiecare celulă într-o coloană Venitul mediu pe oră prevăzut cu o formulă care face referire la un tabel pivot. Apropo, dacă, atunci când încercați să vă referiți la o celulă din tabel pivot, funcția GET.PIOTTABLE.DATA apare în formula dvs., atunci nu veți putea „trage” formula. Pentru a depăși această dificultate, citiți nota.

Orez. 4. Când introduceți o formulă după crearea unui Tabel Pivot, în esență adăugați un câmp calculat care se modifică atunci când Tabelul Pivot în sine este actualizat

Deși această metodă adaugă un câmp calculat care este actualizat atunci când Tabelul Pivot este reîmprospătat, orice modificare a structurii Tabelului Pivot poate face formula inutilă. De exemplu, ca urmare a tragerii câmpului Piața de vânzări în zona FILTRE a listei de câmpuri dintr-un tabel pivot, structura raportului se modifică, în urma căreia apare un mesaj de eroare în câmpul calculat (Fig. . 5). Dacă adăugați un alt câmp în zona COLUMS din tabelul pivot, coloana Venitul mediu pe oră va fi suprascris.

Metoda 3: Inserarea directă a unui câmp calculat în PivotTable

Inserarea unui câmp calculat direct într-un tabel pivot este adesea cea mai bună soluție. Dacă utilizați această metodă, nu va trebui să ajustați formulele. De asemenea, va fi posibilă ajustarea automată a tabelului în conformitate cu sursa de date modificată. Se va atinge gradul necesar de flexibilitate pentru a asigura recalcularea automată a câmpurilor în cazul modificărilor unităților de măsură.

Un alt avantaj al acestei metode este că, dacă o utilizați, puteți modifica structura tabelului pivot și chiar câmpurile de date pentru diferitele unități de măsură utilizate în câmpurile calculate. În același timp, puteți fi sigur că nu vor exista erori în formule și că referințele la celule nu se vor pierde.

Raportul PivotTable prezentat în figura 6 prezintă un tabel rezumativ, care este prezentat în Fig. 1, cu excepția faptului că este reconstruit pentru a afișa veniturile medii pe oră pentru fiecare serviciu și fiecare piață.

Orez. 6. Câmpul calculat anterior creat rămâne „valid” chiar dacă structura tabelului pivot este modificată, rezultând afișarea veniturilor medii pe oră pentru fiecare serviciu și fiecare piață de vânzare

Creați un câmp calculat

Înainte de a putea crea un câmp calculat, trebuie să deschideți sau să generați un tabel pivot. Exemplul nostru folosește tabelul pivot prezentat în Fig. 1, dar fără coloana D. Pentru a crea un câmp calculat, activați caseta de dialog Inserarea unui câmp calculat. Pentru a face acest lucru, faceți clic pe tabelul pivot pentru a activa grupul de file contextuale Lucrul cu tabelele pivot. Accesați fila Analiză, către grup Calcule, faceți clic pe butonul Câmpuri, elemente și seturiși selectați comanda Câmp calculat din meniu (Fig. 7).

Pe ecran va apărea o casetă de dialog Inserarea unui câmp calculat(Fig. 8). Există două câmpuri de text în partea de sus a casetei de dialog: NumeȘi Formulă. În aceste câmpuri, trebuie să specificați numele câmpului calculat și să creați o formulă prin specificarea câmpurilor de date și a operatorilor matematici necesari. Câmpului calculat i se dă un nume descriptiv Venitul mediu pe oră. Acest nume ar trebui să fie ales pentru a descrie cu exactitate tipul de operație matematică efectuată. Câmp de text implicit Formulă căsuță de dialog Inserarea unui câmp calculat conține expresia = 0. Înainte de a introduce formula, trebuie să eliminați zero. Selectați unul dintre câmpurile din zonă Câmpuri; în cazul nostru - Volumul vânzărilorși faceți clic Adăugați un câmp. Numele câmpului va apărea în rând Formulă. Introduceți semnul de împărțire / și apoi selectați al doilea câmp - Perioada de vânzare (în ore). Clic Adăuga, și apoi Bine pentru a activa noul câmp calculat.

Un nou câmp calculat va apărea în PivotTable Venitul mediu pe oră(vezi fig. 1). Un nou câmp calculat tocmai a fost adăugat cu succes la un tabel pivot. Acum puteți modifica setările pentru acest câmp nou la fel cum ați modifica setările pentru orice alt câmp (de exemplu, numele câmpului, formatul numărului sau culoarea).

Înseamnă asta că ați adăugat coloana la sursa de date? Nu. Câmpurile calculate sunt ca elementele SubtotalȘi valoare totală Funcțiile PivotTable sunt implicite, deoarece toate sunt funcții matematice care recalculează datele atunci când PivotTable este schimbat sau actualizat. Câmpurile calculate imită pur și simplu câmpurile definite în mod explicit în sursa de date. Puteți să le trageți, să modificați setările câmpului și să le utilizați împreună cu alte câmpuri calculate.

Vă rugăm să rețineți că în fig. 8 Formatul formulei introduse este similar cu cel utilizat în linia standard formule Excel. Principala diferență este că, în loc să utilizați referințe stricte de celule sau valori numerice, faceți referire la câmpurile de date PivotTable pentru a atribui valori argumentelor utilizate în calcule.

Crearea articolelor calculate

Un element calculat este în esență un rând virtual de date care este adăugat ca rezultat al calculelor efectuate pe alte rânduri din același câmp. Foarte des, un rezultat similar cu cel obținut la crearea unui articol calculat poate fi obținut prin gruparea datelor. Într-adevăr, în multe cazuri, gruparea oferă o alternativă excelentă la membrii calculati.

În tabelul rezumativ prezentat în fig. 9, este indicat volumul total de vânzări pentru anumite perioade de raportare (câmp Perioadă de raportare). Imaginați-vă că doriți să comparați volumul mediu al vânzărilor din ultimele șase perioade cu volumul mediu al vânzărilor din ultimele șapte perioade. Pentru a fi mai precis, trebuie să determinăm media pentru perioadele P01–P07 și să o comparăm cu media pentru perioadele P08–P13.

Plasați indicatorul în orice celulă a câmpului Perioadă de raportare accesați fila panglică contextuală Analiză la grup Tehnica de calcul,și faceți clic pe butonul Câmpuri, elemente și seturi. În meniul care se deschide, selectați comanda Obiect calculat(Fig. 10).

Se va deschide o casetă de dialog (Fig. 11). Observați că în partea de sus a casetei de dialog indică câmpul cu care lucrați. În acest caz acest câmp Perioadă de raportare. În plus, lista conține toate elementele câmpului Perioadă de raportare. Dați un nume articolului calculat și apoi creați o formulă cu combinația necesară de elemente de date și operatori care vor produce rezultatul corect. Formula este introdusă în câmp Formulă, iar elementele de date necesare utilizate în acesta sunt selectate în listă Elemente. Faceți clic pe butonul Bine, iar elementul calculat va fi adăugat la tabelul pivot (Fig. 12).

Orez. 11. Caseta de dialog Inserarea unui articol calculat

În mod similar, creați un articol calculat care va reprezenta vânzările medii pentru perioadele P08–P13. Apoi ascundeți perioadele de raportare individuale, lăsând doar două elemente calculate. Cu o mică formatare, articolele noastre calculate vor fi folosite pentru a compara vânzările medii pentru cele șase luni anterioare și cele ulterioare (Figura 13).

Dacă nu ascundeți elementele de date utilizate în calculul articolelor calculate, subtotalurile și totalurile generale pot fi incorecte.

Avantajele și dezavantajele calculelor PivotTable

Există, de asemenea, anumite dezavantaje în utilizarea câmpurilor și elementelor calculate. Este important să înțelegeți ce se întâmplă atunci când calculați date în tabelele pivot, dar este mult mai important să înțelegeți limitările câmpurilor calculate și ale elementelor calculate pentru a ajuta la prevenirea erorilor pe măsură ce analizați datele.

Prioritatea operațiunilor. Ca și în foile de calcul, orice operator poate fi folosit în formule pentru articolele și câmpurile calculate: +, –, *, /, %, ^ etc. Mai mult, ca și foile de calcul, calculele în tabelele pivot sunt efectuate în funcție de prioritatea operatorului. Când efectuați un calcul care combină mai mulți operatori, cum ar fi (2+3) *4/50%, Excel evaluează expresia și efectuează calculul într-o anumită ordine. Cunoașterea acestei comenzi te va scuti de multe greșeli.

Deci, ordinea operațiilor în Excel este următoarea:

• procesarea expresiilor din paranteze;
• procesare interval(:);
• procesarea intersecțiilor (zonelor);
• procesarea îmbinărilor (;);
• efectuarea unei operații de negație;
• conversia valorilor procentuale (de exemplu, 50% este convertit la 0,50);
• exponentiație (^);
• înmulțirea (*) și împărțirea (/); aceste operațiuni au prioritate egală;
• adunare (+) și scădere (–); aceste operațiuni au prioritate egală;
• combinând datele text (&) această operație se mai numește și concatenare;
• efectuarea de operații de comparare (=,<>, <=, >=).

Operatorii echivalenti din aceeasi expresie sunt intotdeauna executati in ordine (de la stanga la dreapta).

Să ne uităm la un exemplu simplu. După cum știți, expresia (2+3)*4 returnează rezultatul 20. Dacă eliminați parantezele și lăsați expresia 2+3*4, atunci Excel va calcula: 3*4 = 12 + 2 = 14.

Să ne uităm la un alt exemplu. Dacă introduceți expresia 10^2 ca formulă, i.e. Dacă specificați numărul 10 la pătrat, programul va returna valoarea 100 ca răspuns. Dacă introduceți expresia –10^2, vă puteți aștepta ca valoarea să returneze –100. Cu toate acestea, Excel va returna din nou valoarea 100. Motivul este că programul efectuează operația de negație înainte de exponențiere, adică. valoarea 10 este mai întâi convertită în -10, iar rezultatul lui -10*-10 este de fapt 100. Folosirea parantezelor în formula -(10^2) asigură că programul efectuează mai întâi exponențiarea și apoi negația, returnând valoarea așteptată. rezultat - 100.

Referințe de celule și intervale denumite. Referințele de celule sau intervalele denumite nu pot fi utilizate atunci când creați câmpuri și elemente calculate, deoarece atunci când creați obiecte calculate într-un tabel pivot, lucrați în esență în afara spațiului obiect. Singurele date disponibile pentru utilizatori se află în memoria cache a tabelului pivot. Deoarece nu puteți depăși memoria cache, nu puteți face referire la celule sau intervale denumite în formula pe care o creați.

Funcțiile foii de lucru. Puteți utiliza orice funcție de foaie de lucru care nu utilizează referințe de celule sau obiecte denumite ca argumente. De fapt, puteți utiliza orice funcție de foaie de lucru care nu necesită referințe de celule sau obiecte denumite. Funcții precum COUNT, AVERAGE, IF, AND, NO și OR se încadrează în această categorie.

constante. Puteți utiliza orice constante în calculele efectuate în tabelele pivot. Constantele sunt valori statice care nu se modifică în timp. De exemplu, în următoarea formulă, numărul 5 este o constantă: [Unități vândute] *5. Chiar dacă sensul Unități vândute se poate modifica pe baza datelor disponibile, numărul 5 va fi întotdeauna același.

Link-uri către totaluri. Formulele care efectuează calcule nu pot face referire la subtotaluri sau totaluri din Tabel Pivot. Cu alte cuvinte, nu puteți utiliza rezultatul unui calcul rulant sau total ca variabilă sau argument într-un câmp calculat.

Reguli speciale pentru câmpurile calculate. Calculele în câmpurile calculate sunt întotdeauna efectuate pe totaluri, nu pe elemente de date individuale. Mai simplu spus, Excel calculează întotdeauna câmpurile de date, subtotalurile și totalurile înainte de a evalua un câmp calculat. Aceasta înseamnă că câmpul dvs. calculat este întotdeauna aplicat totalurilor pentru datele sursă. Exemplul prezentat în Fig. 14 demonstrează modul în care acest lucru poate afecta analiza datelor.

Orez. 14. Deși un câmp calculat este valid pentru elemente de date individuale, nu este corect din punct de vedere matematic să îl utilizați pentru subtotaluri

În fiecare trimestru, trebuie să obțineți volumele finale de vânzări pentru fiecare produs prin înmulțirea numărului de unități vândute cu prețul unitar al produsului respectiv. Dacă vă uitați mai întâi la datele din primul trimestru, veți identifica imediat problema. În loc să se calculeze suma 220+150+220+594, care este 1184, suma numărului de unități este înmulțită cu suma prețurilor mărfurilor, rezultând o valoare incorectă. Din păcate, această problemă nu poate fi rezolvată, dar poate fi rezolvată. Eliminați subtotalurile și subtotalurile din tabelul Pivot, apoi calculați un nou total în partea de jos în afara tabelului pivot.

Reguli speciale pentru utilizarea membrilor calculati. Un tabel pivot nu poate folosi elemente calculate care calculează medii, abateri standard sau variații. În schimb, nu puteți utiliza medii, abateri standard sau variații într-un tabel pivot care conține un articol calculat. Nu veți putea folosi un câmp de pagină pentru a crea un articol calculat sau nu veți putea muta elementele calculate în zona de filtrare a raportului. Nu puteți adăuga un articol calculat la un raport care conține un câmp grupat și nu puteți grupa niciun câmp dintr-un tabel pivot care conține un articol calculat. Când creați o formulă de articol calculată, nu puteți face referire la un articol dintr-un câmp extern.

Gestionați și sprijiniți calculele în tabele pivot

Când lucrați cu tabelele pivot, apar adesea situații când nu are rost să stocați tabelul pivot mai mult decât este necesar pentru a copia valori individuale. Cu toate acestea, există adesea situații în care este mai profitabil să stocați tabelul pivot și toate elementele sale neschimbate. Dacă mențineți și gestionați un tabel pivot modificând cerințele și mărind volumul de date sursă, trebuie să gestionați câmpurile calculate și membrii calculati.

Editarea și ștergerea calculelor din tabelele pivot. Dacă modificați setările de calcul sau nu mai aveți nevoie de un câmp calculat sau membru calculat, puteți deschide caseta de dialog corespunzătoare pentru a edita sau șterge calculul. Activați caseta de dialog Inserarea unui câmp calculat sau Inserarea unui articol calculat(vezi descrierea Fig. 7 și 10) și selectați lista derulantă din ea Nume(Fig. 15).

Modificați ordinea în care sunt efectuate calculele în câmpurile calculate. Dacă valoarea unei celule dintr-un PivotTable depinde de rezultatul calculului mai multor elemente calculate, puteți modifica ordinea în care sunt efectuate operațiunile din câmpurile calculate. Cu alte cuvinte, puteți specifica ordinea în care vor fi efectuate calculele individuale. Pentru a deschide caseta de dialog , poziționați indicatorul oriunde în tabelul pivot și faceți clic pe pictogramă Câmpuri, elemente și seturi. În meniul derulant, selectați comanda Ordine de calcul (Fig. 16).

Orez. 16. Deschideți caseta de dialog Ordinea de calcul

În caseta de dialog Ordinea de calcul(Fig. 17), sunt afișate toate elementele calculate care sunt adăugate la tabelul pivot. Selectați orice element calculat din listă și utilizați butoanele Sus, JosȘi Șterge. Ordinea în care apar formulele în listă determină ordinea exactă în care sunt efectuate calculele în PivotTable.

Documentarea formulelor. Excel vă oferă un instrument excelent care afișează câmpurile calculate și elementele calculate utilizate într-un tabel pivot, precum și cum să efectuați calcule și să aplicați formule. Acest instrument este foarte util pentru analiza unui tabel pivot terț sau atunci când trebuie să determinați rapid ce calcule sunt utilizate în el și ce câmpuri și elemente le afectează. Pentru a crea un raport despre calculele din tabelul Pivot, plasați cursorul oriunde în Tabelul Pivot și faceți clic Câmpuri, elemente și seturiși selectați comanda din meniul derulant Deduceți formule. Excel va crea un raport privind câmpurile și elementele calculate pe o foaie separată (Fig. 18).

Orez. 18. Echipa Deduceți formule vă permite să documentați rapid și ușor calculele într-un tabel pivot

Nota a fost scrisă pe baza cărții lui Jelen, Alexander. . Capitolul 5.

Cuvântul Tabel este scris cu majuscule deoarece nu este doar un tabel, ci un instrument Excel separat.

Prima modalitate de a crea un câmp calculat se bazează pe faptul că formularul se bazează pe o interogare care include coloane calculate. Formularul creează un câmp de text care specifică o coloană de interogare calculată ca sursă de date. Câmpul este blocat, astfel încât utilizatorul să nu poată modifica datele din acesta. Datele sunt calculate în timpul executării interogării. Pentru a bloca un câmp în modul Designer La fereastră Proprietăți câmp calculat în fila Date într-un rând Lacăt introduce DA.

În al doilea caz, formularul este construit pe baza unui tabel. Este creat un câmp nou sub formă, în care este specificată o expresie ca sursă de date, de exemplu, = [Preț unitar]*Cantitate. Când se face referire la câmpuri din expresii, este obligatoriu să se folosească paranteze drepte dacă numele câmpului constă din mai mult de un cuvânt. Introducerea unei expresii direct într-un control (câmp) este permisă dacă este scurtă și lungimea câmpului o permite. În caz contrar, expresia este introdusă în celula de proprietate Date fereastră Proprietăți. Puteți utiliza generatorul de expresii pentru a face acest lucru. Dacă expresia nu se încadrează în celula de proprietate, trebuie să apăsați tastele + și să deschideți fereastra Zona de intrare. Dacă este necesar, ar trebui să setați formatul câmpului atunci când este afișat pe ecran.

6. Crearea unui formular de buton

Selectați comanda de meniu Manager de formulare de servicii/utilități/pushbutton.

În caseta de dialog care vă întreabă dacă doriți să creați un formular de buton, faceți clic DA.

Pe ecran va apărea o fereastră Buton Form Manager. Această fereastră conține o listă de pagini cu formular de buton. La începutul lucrării, fereastra este de o pagină - Formularul butonului principal. În fereastră, faceți clic pe butonul Schimbare.

Se deschide o casetă de dialog Schimbarea unei pagini de formular de buton. Editați numele formularului.

Faceți clic pe butonul Crea. Apare o fereastră Modificarea unui element de formular buton.

În câmp Text Introduceți numele butonului de creat. Numele butonului trebuie asociat de către utilizator cu obiectele cu care intenționează să lucreze. Al doilea câmp Echipă vă permite să definiți acțiunea care va fi efectuată după ce faceți clic pe acest buton. Selectați comanda necesară pentru aplicația dvs. din lista derulantă.

Adăugați butoane pentru toate formularele și rapoartele, structurându-le după tip.

Adăugați un buton pentru a ieși din aplicație, precum și un buton pentru acces rapid La Manager formular buton.

Configurați aplicația pentru Formularul butonului principal deschis la deschiderea bazei de date. Pentru a face acest lucru, utilizați comanda de meniu Instrumente/Opțiuni de pornire.

EXEMPLU:

Orez. 28. Forma de buton de nivel superior

Laboratorul 3 Crearea de interogări

Scopul lucrării - dobândiți abilități practice în dezvoltarea interogărilor de baze de date.

Exercițiu:

Testați schema bazei de date utilizând interogări tipice. Trebuie să creați cel puțin o cerere de fiecare tip descris mai jos. În raport, interogările ar trebui să fie prezentate în limbaj natural, în SQL și ar trebui să ofere, de asemenea, un exemplu de rezultat al executării interogării.

Secvența de lucru

1.Creați o interogare de selectare cu un singur tabel utilizând Generatorul de interogări

ÎN fereastra bazei de date faceți clic pe marcaj Cereri.

Faceți clic pe butonul Crea.

La fereastră Cerere nouă Selectați Constructorși apăsați butonul Bine.

La fereastră Adăugarea unui tabel Selectați din fereastra listă tabelul (sau interogarea) pe care se va baza interogarea și faceți clic pe butonul Adăuga. Închide fereastra curentă. În continuare, pe ecran va apărea o fereastră Generator de interogări.

Adăugați un câmp la cerere. În acest scop în tabel sursă, situat în partea de sus a ferestrei cerere de mostra, selectați câmpul dorit. Faceți dublu clic pe butonul stâng al mouse-ului pe câmpul evidențiat. În același timp, în partea de jos a ferestrei Cereriîn formularul de solicitare va apărea o coloană corespunzătoare câmpului selectat.

De asemenea, puteți adăuga un câmp la o solicitare după cum urmează: faceți clic butonul din stanga mouse-ul pe câmpul selectat și, fără a-l elibera, trageți câmpul în locul dorit din formularul de solicitare; Faceți clic pe un câmp de celulă din formularul de solicitare pentru a afișa o listă derulantă de câmpuri și selectați câmpul necesar din acesta; Faceți clic pe câmpul pentru celulă din formularul de solicitare și introduceți numele câmpului.

Pentru a adăuga toate câmpurile de tabel la o interogare, selectați câmpul indicat cu un asterisc (*). Asteriscul facilitează munca de construire a unei interogări, dar face sortarea și introducerea condițiilor pentru selectarea câmpurilor mai complicate.

În mod implicit, toate câmpurile de solicitare nu sunt sortate. Dacă este necesar, puteți specifica în linie Triere formular de interogare, cum să afișați valorile coloanei: crescător sau descendent. Când specificați o ordine de sortare în mai multe câmpuri, înregistrările sunt sortate mai întâi după coloana din stânga și apoi după fiecare coloană ulterioară de la stânga la dreapta.

În linie Ieșire pe afișaj bifați casetele pe care doriți să le vedeți în setul de înregistrări după rularea interogării. În mod implicit, sunt afișate toate câmpurile incluse în formularul de solicitare.

În formularul de cerere din rând Condiție de selecție iar în linie Sau specificați condițiile de prelevare din baza de date. Condițiile sunt expresii logice.

Se efectuează o operație logică în condiții de selecție situate într-o singură linie ȘI. Mai multe condiții de selecție pentru un câmp pot fi setate în unul din două moduri: introduceți toate condițiile într-o celulă a rândului Condiție de selecție, conectându-le cu un operator logic SAU sau introduceți fiecare condiție într-o celulă separată a rândului Sau. Treceți la rândul următor al zonei celulei SAU prin cheie<>. Expresiile dintr-o celulă a formularului de solicitare sunt introduse de la tastatură sau folosite pentru a le crea. Generator de expresii, care este lansat de comandă Construi din meniul contextual asociat liniei Condiție de selecție(sau puteți apăsa butonul Construi pe bara de instrumente Acces).

Vizualizați rezultatele interogării făcând clic pe butonul Lansa(buton cu semnul exclamării) sau butonul Vedere pe bara de instrumente.

În situațiile în care trebuie să modificați o proprietate de interogare, faceți dublu clic pe zona goală din partea de sus a ferestrei Constructor de solicitări - se va deschide o fereastră Solicitați proprietăți.

Interogări create în moduri DesignerȘi masterat, generați comenzi în limbaj SQL. Puteți vizualiza aceste comenzi selectând din meniu Vedere opțiune modulSQL.

EXEMPLU:

Selectați informații despre produse în cantități mai mari de 100, dar mai mici de 300 și care costă mai puțin de 100 de ruble.

Orez. 10. Constructor de interogări cu condiție

Orez. 11. Rezultatul interogării cu condiție

2. Îmbinare interioară pe un câmp

Selectați o filă Cereri fereastra bazei de date, faceți clic pe butonul Crea. Se va deschide o casetă de dialog Cerere nouă. Selecteaza o optiune Constructor.

La fereastră Adăugarea unui tabel selectați secvențial mai multe tabele înrudite pe care va fi construită interogarea. În vârf Generator de interogări sunt afișate tabele aferente.

Completați formularul de cerere.

Faceți clic pe butonul Lansa sau Vedere pentru a afișa rezultatele interogării.

EXEMPLU:

Selectați denumirile produselor pentru furnizarea cărora s-a încheiat un contract, indicând numele producătorilor acestora și volumul livrării.

Orez. 12. Designer de interogări cu mai multe tabele

Orez. 13. Rezultatul unei interogări cu mai multe tabele

3. Conexiuni externe

Urmați pașii 1-3 din secțiune Unire internă pe un câmp.

Stabiliți o relație între tabele dacă nu a fost înregistrată anterior în Sistem DB.

Faceți clic pe linia de îmbinare a câmpurilor pentru a o evidenția.

Selectați comenzile din meniu Vedere opțiune Opțiuni de îmbinare. Apare o casetă de dialog Opțiuni de îmbinare.

Selectați tipul de alăturare, faceți clic pe butonul Bine. După această operație, pe linia de unire va apărea o săgeată care indică tipul de conexiune.

4. Includerea unei tranzacții de grup în formularul de solicitare

Faceți clic pe butonul Operațiuni de grup, semn  pe bara de instrumente Generator de interogări. În formularul de cerere de deasupra liniei Triere va apărea linia Operare de grup.

Înlocuiți în linie Operare de grup instalare Gruparea pentru necesar operare de grup selectând-o din caseta combinată: Sumă, Mediu, Numara, Mîn, Max, StDev, Var, Primul, Ultimul, Expresie(expresie),Unde(condiție),grup de(grupare).

Expresie vă permite să introduceți o expresie în locul unui nume de câmp într-o celulă , de exemplu, pentru a calcula intervalul de valori pentru o anumită coloană X:Max([ X]) – Min([ X]).

Condiție indică faptul că acest câmp va fi folosit pentru a seta condițiile de selectare a înregistrărilor. Condiția de selecție în sine este scrisă în celula rândului Condiție de selecție.

3. Completați cererea.

EXEMPLU:

Selectați numărul total de produse de la fiecare producător, indicând numele acestuia.

Orez. 14. Constructor de interogări cu gruparea înregistrărilor

Orez. 15. Rezultatul unei interogări cu gruparea înregistrărilor

5. Includerea câmpurilor calculate într-un formular de solicitare

Generator de interogări .

Faceți clic în linie Camp conform coloanei goale din formularul de cerere. Introduceți numele coloanei, urmat de două puncte, urmat de o expresie. Pentru a introduce o expresie, faceți clic pe butonul Construi, a profita Generator de expresii.

În vârf Generator de expresiiÎn zona de introducere, creați o expresie.

Într-o coloană de câmp calculată, mutați cursorul pe rând Operare de grupși selectați din lista derulantă Expresie(dacă nu selectați opțiunea Expresie, când este executată, cererea deschide o casetă de dialog Introduceți valoarea parametrului sau returnează un mesaj de eroare).

Rulați cererea.

EXEMPLU:

Calculați suma totală a comenzii pentru fiecare contract (definită ca produsul dintre cantitatea de mărfuri și prețul plus costurile de transport).

Orez. 16. Constructor de interogări cu un câmp calculat

Orez. 17. Rezultatul unei interogări cu un câmp calculat

6.Crearea unei interogări parametrice

Creați o interogare de selectare în modul Generator de interogări.

Introduceți șiruri într-una dintre celule Condiție de selecție un parametru, nu o valoare. De exemplu, dacă selectați înregistrări după nume de familie, atunci interogare parametrică pe teren Nume de familie în condițiile de selecție, expresia [introduceți numele] poate fi scrisă între paranteze drepte.

Selectați din meniu Cerere opțiune Opțiuni. În caseta de dialog Solicitați parametriîn coloana din stânga, introduceți între paranteze pătrate parametrul care se potrivește cu parametrul din formular de cerere Generator de interogări(parametrul poate fi copiat din formularul de cerere). În coloana din dreapta, specificați tipul de date: apăsați tasta , selectați un tip de date din listă.

Rulați cererea de execuție. Acces va afișa fereastra Introduceți valoarea parametrului, în care introduceți valoarea parametrului dorit și apăsați butonul Bine.

5. Rulați cererea de execuție.

EXEMPLU:

Selectați o listă de produse a căror dată de expirare depășește valoarea specificată.

Orez. 18. Parametric Query Builder

Orez. 19. Introducerea datei de expirare a produsului

Orez. 20. Rezultatul unei interogări parametrice

7. Crearea de interogări încrucișate

1. Creați cu Designer cerere nouăși includeți în el tabelele necesare.

2. Selectați câmpurile ale căror valori vor fi folosite pentru a crea rândurile și coloanele tabelului dinamic și trageți-le în formularul de interogare.

3. Selectați o comandă din meniu Interogare/Încrucișare. Titlu Designer se va schimba de la Interogarea 1: per probă pe Interogare 1: interogare încrucișată . Formularul de cerere va afișa linia Masă încrucișată .

4. Pentru câmpurile care vor fi rânduri încrucișate, deschideți lista derulantă Rând Masă încrucișată selectați opțiunea Antete de rând. Fiecare câmp care este un antet de linie trebuie să aibă o setare de operare de grup în linie Gruparea.

5. Pentru câmpurile care vor fi coloane încrucișate, în funcție de interogare, sunt posibile următoarele acțiuni:

în linie Masă încrucișată selectați opțiunea Titluri de coloaneși în linie Operare de grup instalare grupare, sau în linie Operare de grup – Condiție,în rânduri Condiție de selecțieȘi SAU scrie expresie logică.

6. Setați câmpul pe baza căruia va fi creată valoarea totală. În linie Masă încrucișată alege Sens.În linie Operare de grup selectați funcția finală sau Expresie.În acest din urmă caz, în celulă Camp notează expresia.

7. Rulați cererea de execuție.

EXEMPLU:

Selectați cantitățile totale de produse comandate de fiecare tip produse de fiecare producător. Prezentați rezultatul sub forma unui tabel, unde rândurile sunt numele tipurilor de produse, iar coloanele sunt numele producătorilor.

Orez. 21. Cross Query Builder

Orez. 22. Rezultatul interogării încrucișate

8.Interogare pentru a crea un tabel

Pentru a crea un tabel din rezultatele unei interogări care a fost folosită pentru a selecta înregistrări din tabelele asociate, urmați acești pași:

Faceți o copie a interogării pentru a selecta înregistrări din tabele (sau creați una nouă).

Deschideți cererea creată în modul designer, prin evidențierea numelui acestuia în lista de interogări din fereastra bazei de date și făcând clic pe butonul Constructor.

Selectați o comandă din meniu Interogare/Creează tabel.Se va deschide o casetă de dialog Crearea unui tabel. Introduceți numele noului tabel, lăsați butonul radio pe poziție În baza de date curentă.

Faceți clic pe butonul Bine. Interogarea de selectare este convertită într-o interogare de creare a tabelului.

Rulați interogarea făcând dublu clic pe numele interogării din fereastra DB. Înainte de rularea interogării, apare un mesaj care vă avertizează că se vor face modificări noului tabel (chiar dacă acesta nu a fost încă creat).

Faceți clic pe butonul da. Un al doilea mesaj de avertizare apare despre numărul de înregistrări care vor fi plasate în noul tabel ca urmare a interogării. Faceți clic pe butonul da.

Extindeți fila Tabele din fereastra DB; Un nou tabel ar trebui să apară în listă.

9.Solicitare de actualizare a înregistrărilor

Creați o nouă interogare și includeți în ea copia creată a tabelului.

Selectați o comandă din meniu Interogare/Actualizare. În formularul de cerere de linie TriereȘi Ieșire pe afișaj va fi înlocuit cu șirul Actualizați.

În linie Actualizați specificați o expresie care reprezintă noua valoare pentru câmpul curent.

Dacă informațiile nu sunt actualizate în toate înregistrările din tabel, ci doar în parte din acesta, introduceți o condiție pentru selectarea înregistrărilor.

Completați cererea. Verificați dacă înregistrările sunt actualizate corect.

EXEMPLU:

Actualizați costul produselor a căror cantitate depășește 500 de unități, stabilindu-l la 90% din costul inițial.

Orez. 23. Actualizați constructorul cererii

10.Solicitare de ștergere a înregistrărilor

Creați o copie a tabelului în care ar trebui să fie actualizate înregistrările.

Creați o nouă interogare și includeți tabelul corespunzător în ea.

Trageți și plasați câmpurile în formularul de solicitare ale căror valori vor fi folosite pentru selectarea câmpurilor. În linie Conditii de selectie indicați criteriile de selecție.

Rulați o interogare de selectare pentru a afișa înregistrările de șterse.

Deschis fereastra bazei de date. Selectați o filă Mese.

Activați cererea de selecție creată și accesați modul Constructor de solicitări.

Selectați o comandă din meniu Interogați/Ștergeți. În formularul de cerere de linie TriereȘi Ieșire pe afișaj va fi înlocuit cu șirul Îndepărtarea.

Faceți clic pe butonul Lansa barele de instrumente. Apare o casetă de mesaj care solicită confirmarea ștergerii intrărilor.

Ștergerea înregistrărilor din tabelul „ unu", pentru care în tabel „ mulți" există înregistrări aferente, încalcă regula de integritate a datelor. Prin urmare, opțiunea de integritate a datelor setată pe o relație între tabele poate împiedica ștergerea înregistrărilor. Ar trebui aplicată o ștergere în cascadă.

EXEMPLU:

Orez. 24. Ștergeți constructorul cererii

11. Solicitați adăugarea înregistrărilor

O interogare de adăugare este adesea folosită pentru a copia date dintr-un tabel în altul.

Selectați o filă Cereri fereastra bazei de date, faceți clic pe butonul Crea. Se va deschide o casetă de dialog Cerere nouă. Selecteaza o optiune Constructor.

La fereastră Adăugarea unui tabel selectați tabelul din care vor fi preluate datele.

Mutați la linie Camp formular de cerere pentru câmpurile ale căror valori vor fi incluse într-un alt tabel.

Selectați comenzile din meniu Solicitați/Adăugați. Va apărea o fereastră Plus, unde trebuie să specificați numele tabelului la care ar trebui să adăugați date și cărei baze de date îi aparține. Dacă baza de date nu este cea actuală, ci alta, atunci va trebui să specificați numele complet al fișierului care conține baza de date.

În formularul de cerere după rând Triere linia va fi inclusă Plus,în care trebuie să indicați câmpurile tabelului în care se adaugă datele. Dacă numele câmpurilor sunt aceleași ACucess se va introduce automat în linie Plus nume de câmpuri.

Rulați cererea de execuție. Vedeți cum s-a schimbat tabelul cu noi rânduri adăugate.

Dacă într-o cerere de Plus sunt domenii ca Tejghea, atunci puteți lucra cu astfel de câmpuri în două moduri:

nu include câmpuri de tip Tejgheaîn câmpurile care trebuie adăugate; apoi în tabelul în care se adaugă datele, numerotarea secvenței de date va continua, adică. dacă există un tabel de angajați cu numere de la 1 la 100 și adăugați la acesta intrări noi, atunci numerotarea lor va începe de la numărul 101;

include câmpuri de tip Tejghea la lista de solicitări ca câmpuri adăugate, apoi valorile pe care le aveau în tabelul inițial vor fi salvate în câmpurile înregistrărilor adăugate; dacă astfel de valori există deja în câmpurile tabelului la care se adaugă înregistrările, aceasta poate duce la o eroare. Un mesaj de eroare este afișat pe ecran înainte de executarea cererii. Dacă există o eroare, selectați butonul Anulare și remediați eroarea.

Metoda 2: faceți clic dreapta pe valorile câmpului dorit din zona ΣValues ​​​​și selectați:

În plus, pentru a selecta o funcție diferită din lista sugerată, configurați formatul câmpului și schimbați numele câmpului.

Pentru a profita de efecte. Astăzi vă vom învăța. Marcați celulele pe care doriți să le desenați. Lecția 3: Tabele și diagrame pivot. Să presupunem că avem un set de analizat. Pentru a utiliza un PivotTable, selectați orice celulă din foaia de lucru și alegeți PivotTable din meniul Inserare.

Apoi puteți obține informațiile. Când creați diagrame, nu contează dacă tabelul are un chenar sau alte elemente de formatare. Introducere în formule și funcții Efectuarea calculelor, fie ele simple sau complexe, poate fi obositoare și consumatoare de timp.

 Calcule suplimentare, apoi selectați opțiunea de calcul dorită.

Reutilizarea unui câmp original într-un raport PivotTable

Puteți face mai multe calcule folosind același câmp sursă. Pentru a face acest lucru, trebuie să adăugați câmpul original din lista de câmpuri din tabelul pivot la intervalul de valori Σ Valori ​​[Σ

Valori] de mai multe ori, apoi ajustați parametrii câmpului. Acest lucru vă va permite să vedeți simultan atât calculul în sine, cât și calculul efectuat pe baza acestuia.

Adăugați câmpuri calculate la un tabel pivot

Nu puteți adăuga noi rânduri sau coloane într-un raport PivotTable pentru a insera formule sau pentru a adăuga calcule suplimentare, dar puteți adăuga un câmp calculat care vă permite să introduceți formule pentru a calcula date noi pe baza datelor existente.

Foaie de lucru baze de date 1 Foaie de lucru baze de date 1 Creați un folder „Băzuri de date” pentru a stoca toate datele cursului. Creează o interogare care afișează titluri și descrieri. Apare caseta de dialog Manage Database. Folosind această fereastră, utilizatorul gestionează baza de date și toate elementele acesteia.

Ce este o foaie de calcul? O foaie de calcul este un program pentru efectuarea de calcule matematice. Cu ajutorul lui putem face și calcule lizibile în formular. Pentru a profita de acțiuni specifice. Cea mai intensivă parte a muncii este pregătirea datelor. Problema 1 Matematică Grupa 2 Urmați acești pași pe rând. Rulați masa.

Inserarea unui câmp calculat direct într-un tabel pivot este cea mai bună soluție. Nu este nevoie să gestionați formulele sau să vă faceți griji cu privire la extensibilitatea tabelului pe măsură ce sursa de date crește sau se editează, iar tabelul pivot rămâne suficient de flexibil pentru a modifica definițiile câmpurilor.

Un alt avantaj uriaș al acestei metode este că puteți modifica structura tabelului pivot și chiar puteți calcula alte câmpuri de date pentru câmpurile calculate fără să vă faceți griji cu privire la erorile de formulă sau referințele la celule rupte.

Universitatea de Tehnologie din Pomerania de Vest din Szczecin Facultatea de Tehnologie Marină și Transport Departamentul de Structuri, Mecanică și Tehnologie Navă într-un laborator de cercetare operațională. Tabelele pivot sunt folosite pentru a analiza rapid datele. Pentru a folosi tabele pivot, trebuie să avem datele pregătite corespunzător cu anteturi.

Pentru a evita astfel de situații, ar trebui să formatați datele noastre ca tabel. Pentru a formata datele ca tabel. Aceasta va selecta toate anteturile. . Următoarea alegere este locația tabelului pivot și, pentru a menține lizibilitatea datelor, ar trebui să părăsiți opțiunea Foaie nouă, care va insera frunză nouă la masă.

Microsoft Excel 2010. Nivelul 2. Caracteristici avansate

Deci, beneficiile adăugării propriilor câmpuri calculate la un tabel pivot sunt clare:

 Eliminați erorile potențiale în formule și referințe de celule.

 Abilitatea de a adăuga și elimina date dintr-un tabel pivot fără a modifica calculele inițiale.

Există o nouă foaie PivotTable care este goală. În partea dreaptă există o listă de câmpuri, care conține toate titlurile din tabelul nostru și un plan cu zonele sale. Să presupunem că vrem să vedem cine a fost cel mai bine vândut. În acest caz, selectați câmpul Vânzător și câmpul Sumă, deoarece după vânzare vom verifica cine a fost cel mai bun. Raportul va arăta așa.

Valoarea implicită este coloana. Tabelele pivot pot fi modificate cu ușurință și dacă doriți să verificați țara cel mai bine vândută, pur și simplu anulați achiziția și marcați-o cu vânzătorul. Imediat primim un nou raport din care puteti citi rezultatul.

 Capacitatea de a recalcula automat datele atunci când schimbați sau actualizați un tabel pivot.

 Oferiți flexibilitate pentru modificarea calculelor atunci când definițiile elementelor se modifică.

 Abilitatea de a gestiona eficient calculele.

Creați un câmp calculat:

1. Selectați o celulă din tabel pivot;

De asemenea, putem adăuga o subcategorie la Vânzător bifând această casetă. Raportul de vânzări rezumă automat valorile pe care le afișează, dar aceste totaluri îngreunează adesea analiza. De asemenea, putem scăpa de total și putem modifica aspectul raportului.

Pentru a schimba rapid raportul și a vedea cum l-a vândut pe Dobrovalsky în alte țări, pur și simplu schimbați ordinea condițiilor în câmpuri prin capturarea Vânzătorului și trăgându-l prin stat. În afară de Irlanda și Germania, este clar că rezultatele sale de vânzări nu sunt cele mai bune. Când faceți clic pe vinde în Irlanda, veți vedea o nouă foaie cu un tabel care conține toate tranzacțiile lui Dobrovalsky în Irlanda.

2. În fila Opțiuni, în grupul Calcule, extindeți lista Câmpuri.

elemente și seturi și selectați Câmp calculat.

3. În fereastră Inserarea unui câmp calculat:

 În câmpul Nume, introduceți numele noului câmp.

 În câmpul Formula, creați o formulă, începând cu semnul = și selectând

câmpurile disponibile în lista Câmpuri (pentru a insera, puteți face dublu clic pe un câmp sau puteți selecta un câmp, apoi faceți clic pe Adăugare câmp).

Dacă dorim să ne tipărim detaliile, vom lua câmpul Agent de vânzări din zona Linii și îl vom muta în zona Coloane. Acest lucru va face ca masa să se răspândească pe întreaga foaie. În această fereastră putem specifica și formatul numărului afișat în toate celulele câmpului. Pur și simplu utilizați butonul „Format număr” și selectați, de exemplu, moneda corespunzătoare.

Filtrele sunt de asemenea utile. Când sunt adăugate în această zonă, câmpurile furnizor apar în colțul din stânga sus al unei liste derulante din care puteți selecta una sau mai multe opțiuni. Să vedem care lună a fost cea mai bună vânzare. Mai jos selectăm categoria în funcție de care dorim să grupăm datele.

 Faceți clic pe Adăugare.

Setarea aspectului unui câmp calculat, ca orice alt câmp, are loc în fereastră

Parametrii câmpului valoric.

Editarea unui câmp calculat

1. În fila Opțiuni, în grupul Calcule, extindeți lista Câmpuri.

elemente și seturi, selectați Câmp calculat.

Cu toții știm cum să folosim tabelele pivot. Au o mulțime de avantaje, dar există și o mulțime de dezavantaje. Mulți oameni cred că principala problemă cu tabelele pivot este că nu există nicio modalitate de a face calcule - aceasta este o concepție greșită. De ce ar putea fi necesar acest lucru? Să presupunem că creați un buget folosind tabele pivot. Datele din tabelul original sunt prezentate trimestrial, în scopuri bugetare va fi necesară adăugarea acestor date la o sumă anuală.

Sărbătorim lunile și le îmbrățișăm. Vom primi un raport sortat pe lună. De asemenea, puteți împărți datele în mai multe categorii. Revenind la gruparea configuratorilor și selectând „Trimestru și lună”, obținem aspectul raportului. Gruparea poate fi folosită și pentru numere.

Când deschizi meniul contextual, câmpul Rânduri și opțiunea Grup afișează o fereastră diferită decât dacă ați grupa datele. Numerele sunt grupate în intervale, pe care le definim prin începutul, sfârșitul și volumul intervalului. În acest caz, l-am setat la 500, deci primul grup va conține toate tranzacțiile în valoare între 0 și 500 de zloți.

De fapt, există o astfel de posibilitate și se realizează într-unul din trei moduri.

Puteți să vă îmbunătățiți cunoștințele despre elementele de bază ale tabelelor pivot.

Prima cale. Faceți calcule într-un tabel cu datele originale. Metoda, sincer vorbind, nu este o fântână - prin locul greșit.

A doua cale. Construirea de calcule lângă un tabel pivot nu este, din nou, cea mai bună metodă. Dacă tabelul pivot se modifică (și merită să folosiți tabelele pivot exact când ne așteptăm la schimbări în tabel).

A treia cale.Încorporați câmpuri calculate în tabelul pivot propriu-zis. Dar aceasta este o modalitate bună. Acest lucru se face după cum urmează. Când tabelul pivot a fost deja construit, plasați cursorul pe tabelul pivot (două file legate de acest tabel pivot apar pe panglică) și faceți clic pe fila care apare Opțiuni, apoi Câmpuri,articole șiSeturi din blocul Calcule, selectați din meniul derulant Calculatcamp. În Office 2007, acest buton era în aceeași filă Opțiuni, doar butonul era separat.

În fereastra care apare, în câmp Nume Introduceți numele câmpului dorit. În câmp Formulă introduceți formula de care aveți nevoie inserând câmpuri din lista de mai jos și făcând clic pe butonul Introducecamp.

Veți obține un tabel cu un nou câmp de calcul. Masa este gata!

Fișierul Excel poate fi descărcat de aici.

Fapt interesant. Când se utilizează a treia metodă, programul realizează calculul pentru fiecare rând și apoi aplică parametrul necesar: însumare, medie etc.

Ultimele stiri

• Excel avansat: de ce am încetat să mai folosesc funcția V...

  Am scris deja despre faptul că funcția VLOOKUP (VLOOKUP) este probabil cea mai utilă funcție după operații aritmetice simple...

• Cum recalculează Excel un registru de lucru și de ce ar trebui evitată volatilitatea...

  Dacă lucrați adesea cu fișiere mari care efectuează un număr mare de calcule care depind unul de celălalt, o...

• Cum să construiți rapid o diagramă în cascadă

  Excel nu are diagrame standard în cascadă. Prin urmare, pentru a crea acest tip de diagrame, se folosește de obicei gis...

• Fereastra de control pentru urmărirea rezultatelor

  Se întâmplă adesea să lucrăm cu tabele mari care calculează până la urmă 1-2 indicatori, dar depind de...

• Diagramă cu marcatori pentru compararea indicatorilor planificați cu...

  Adesea este nevoie de a compara principalii indicatori financiari și economici cu cei planificați, de exemplu, în scopul...

• Tatsuo Horiuchi este un artist în vârstă de 73 de ani care desenează în Excel.

  „Nu am folosit niciodată Excel la serviciu, dar am văzut alți oameni făcând diagrame și grafice destul de frumoase în el...

• Cum să ridici un număr la o putere și să extragi rădăcina

  Știați că pentru a ridica un număr la putere în Excel există caracter special^ (pe șase în engleză...

Câmpurile calculate sunt concepute pentru a afișa valorile expresiilor pe baza datelor sursă din rapoarte. Câmpurile calculate din rapoarte pot fi folosite pentru a obține totaluri sau pentru a efectua calcule speciale, de exemplu, pentru a afișa prețul total al unei comenzi prin calcularea produsului dintre cantitatea unui articol și prețul unui articol. Să ne uităm la procedura de creare a câmpurilor calculate folosind exemplul raportului „Articole în depozit” creat anterior.

Pentru a adăuga un câmp calculat „Suma” la un raport:

1. Deschideți raportul Articole în depozit în modul Proiectare.
2. În Caseta de instrumente, faceți clic pe butonul Inscripţie(Etichetă) și plasați noul text în dreapta „Unități în stoc” în secțiunea antet. Introduceți cuvântul „Suma” ca text de etichetă.
3. Pentru a crea un câmp Sumă calculată, faceți clic pe butonul Camp(Text Box) în Caseta de instrumente și adăugați o casetă de text în dreapta casetei de text UnitsInStock din secțiunea de date. Eliminați eticheta noului câmp de text.
4. Faceți clic în interiorul câmpului de text și introduceți următoarea expresie ca valoare:

   = [Stoc]* [Preț] (= * ).

   Deschide fereastra de proprietăți câmpuri făcând dublu clic pe el și pe filă Aspect(Format) în caseta combinată de proprietate Format câmp(Format) selectați o valoare Monetar(Valută).

cometariu

Există mai multe moduri de a introduce o expresie în câmpul de text: selectați-o și apăsați combinația de taste +pentru a intra în modul de editare pentru conținutul câmpului sau faceți dublu clic pe câmp pentru a afișa o casetă de dialog Proprietăți(Proprietăți) și apoi introduceți expresia ca valoare a proprietății Date(Sursă).

Orez. 6.27.

1. Repetați pașii 3 și 4 pentru a crea un câmp de text în secțiunea de comentarii a grupului Categorie, dar introduceți următoarele ca expresie subtotală: =Sum([InStock] * [Preț]) (=Sum( * )). Faceți clic pe butonul Îndrăzneţ(Aldin) în bara de format pentru a seta textul din noul câmp la aldine. Deschide caseta de dialog Proprietăți(Proprietăți) și deschideți fila din ea Alte(Altele), în câmp Nume(Nume) Introduceți un nume pentru caseta de text Sumă. Apoi extindeți fila Aspect(Format) și în caseta combinată de proprietăți Format câmp(Format) selectați o valoare Monetar(Valută).
2. Deoarece lățimea zonei de date a raportului nostru a crescut, reduceți lățimea marginilor paginii, astfel încât toate câmpurile să încapă pe o singură pagină. Pentru a face acest lucru, selectați comanda Fișier, Configurare pagină(Fișier, Configurare pagină) și modificați lățimea implicită a marginilor din stânga și din dreapta la 10 mm (Consultați „Vizualizarea și tipărirea rapoartelor” din acest capitol.)
3. Faceți clic pe butonul Vedere(Vizualizare) pe bara de instrumente Designer de rapoarte(Report Design) pentru a evalua rezultatele muncii dvs. Raportul va arăta ca în fig. 6.27. La datele anterioare s-a adăugat coloana din dreapta cu valorile costului fiecărui produs în cantitatea disponibilă din depozit și subtotalurile pentru costul mărfurilor pentru fiecare categorie.