Hvordan lage et beregnet felt i excel. Beregnede kolonner i Transact-SQL Hvordan lage en kalkulert kolonne

I denne artikkelen vil vi snakke om beregnede felt i Tilgang forespørsler. En spørring, som en tabell, kan utføre beregninger på numeriske, streng- eller datoverdier for hver post ved å bruke data fra ett eller flere felt. Resultatet av beregningen danner et nytt beregnet felt i spørretabellen. I motsetning til beregnede tabellfelt, oppretter ikke beregnede felt i kildedatabasetabeller nye felt. Hver gang spørringen utføres, blir det gjort beregninger basert på gjeldende feltverdier.

Beregnede feltuttrykk kan bruke konstanter og funksjoner i tillegg til feltnavn. Som et resultat av å behandle et uttrykk, kan bare én verdi oppnås.

Oppgave 1. PRODUCT-tabellen har feltene PRICE og RATE_VAT. Beregn prisen inkludert MVA og sammenlign den med prisen som er oppnådd i det beregnede feltet i Prisen inkludert MVA-tabellen.

1. Opprett en utvalgsspørring i designmodus for PRODUKT-tabellen. Dra feltene NAME_TOV, PRICE, RATE_VAT og Price with VAT inn i forespørselsskjemaet (fig. 4.6).
2. For å beregne prisen inkludert MVA, lag et beregnet felt ved å skrive uttrykket [PRICE]+[PRICE]*[RATE_VAT] i den tomme cellen i Felt-raden.
3. For å velge poster med en verdi større enn 5000 i det beregnede feltet, skriv inn > 5000 på linjen Kriterier
4. Etter å ha lagt inn et uttrykk, genererer systemet som standard navnet på det beregnede feltet Uttrykk 1, som blir kolonneoverskriften i tabellen med resultatene av spørringen. Dette navnet settes inn foran uttrykket [PRICE]+[PRICE]*[VAT_RATE]. For hvert nytt beregnet felt i spørringen øker uttrykkstallet med én. Navnet på det beregnede feltet er atskilt fra uttrykket med et kolon. For å endre navnet, plasser musepekeren i det beregnede feltet i forespørselsskjemaet og klikk med høyre museknapp. Velg fra den kontekstsensitive menyen Egenskaper(Egenskaper)-feltet og i bildetekstlinjen skriv inn et nytt feltnavn - Pris inkludert mva1. Nå i tabellen med resultatene av spørringen, vil dette navnet vises i overskriften til den beregnede kolonnen. Feltnavnet kan også rettes direkte i forespørselsskjemaet.
5. For å vise resultatet av spørringen, klikk på knappen Henrette(Løp) i gruppen resultater(Resultater). Det beregnede feltet i tabellen og spørringen har de samme verdiene.
6. Endre prisen på produktet i en av forespørselspostene. Verdiene i begge beregnede feltene vil umiddelbart bli beregnet på nytt.
7. For å danne et komplekst uttrykk i et beregnet felt eller utvalgsbetingelse, er det tilrådelig å bruke uttrykksbyggeren. Byggeren lar deg velge feltnavnene som trengs i uttrykket fra tabeller, spørringer, operasjonstegn og funksjoner. Fjern uttrykket i det beregnede feltet og bruk byggmesteren til å generere det.
8. Ring opp uttrykksbyggeren ved å klikke på knappen Bygger(Byggmester) i gruppen Spørringsoppsett(Query Setup) bånddesign, eller ved å velge Bygge(Bygg) i den kontekstsensitive menyen. Musepekeren må være plassert tidligere i uttrykksinndatacellen.
9. På venstre side av vinduet Uttrykksbygger(Uttrykksbygger) (Fig. 4.7) velg PRODUKT-tabellen som spørringen er basert på. En liste over feltene vil vises til høyre. Velg sekvensielt de nødvendige feltene og operatorene ved å dobbeltklikke dem inn i uttrykket. Uttrykket vil dannes øverst i vinduet. Vær oppmerksom på at byggherren indikerte navnet på tabellen den tilhører før feltnavnet og skilte det fra feltnavnet med et utropstegn.
10. Fullfør prosessen med å konstruere et uttrykk i et beregnet felt ved å klikke på OK-knappen.
11. Lagre forespørselen under navnet ― Pris med mva og lukk den.
12. Kjør den lagrede filen ved å velge den i navigasjonsområdet og velge Åpne fra hurtigmenyen.

Oppgave 2. Du kan bruke innebygde funksjoner i beregnede felt og filterforhold. Access har over 150 funksjoner definert.
La det være nødvendig å velge alle fakturaer som ble sendt for i en gitt måned. I FAKTURA er forsendelsesdatoen lagret i DATE_OTG-feltet med datatypen Dato/klokkeslett.

1. Opprett en utvalgsspørring i designmodus for INVOICE-tabellen. Dra feltene NOM_NAKL og CODE_SK inn i skjemaet (fig. 4.8).
2. Opprett et beregnet felt i en tom radcelle Felt(Felt) ved å skrive et av uttrykkene der: Format([INVOICE]![DISPOSITION_DATE];"mmmm") - denne funksjonen vil returnere hele månedens navn
   eller Format([INBOOK]![DATE_DATE];"mm") - denne funksjonen vil returnere månedsnummeret.
3. For å velge fakturaer utstedt i en gitt måned, i det beregnede feltet i linjen Utvalgsbetingelse (Kriterier), skriv inn navnet på måneden, for eksempel mars (fig. 4.8), eller månedsnummeret, for eksempel 3 i henhold til parameter i Format-funksjonen.
4. Fullfør forespørselen din ved å klikke på knappen Henrette(Løp) i gruppen resultater(Resultater) på båndfanen Arbeide med forespørsler | Konstruktør(Spørreverktøy | Design).
5. Skriv funksjonen Måned(INVOICE!DATE_OTG) i det beregnede feltet og sørg for at denne funksjonen returnerer månedsnummeret hentet fra datoen.
6. For å velge alle rader relatert til andre kvartal, i Kriterier-raden, skriv inn operatoren Mellom 4 og 6, som bestemmer om verdien av uttrykket faller innenfor det angitte intervallet.
7. Skriv uttrykket MonthName(Month(INVOICE!DATE_OTG)) i det beregnede feltet og sørg for at MonthName-funksjonen konverterer månedsnummeret til dets fulle navn.

For å forsterke dette, se videoopplæringen.

Når du analyserer data ved hjelp av pivottabeller, må du ofte inkludere verdier i rapporten som er et resultat av beregninger som ble utført utenfor det opprinnelige datasettet. Excel-program gir brukeren midler til å utføre beregninger i pivottabeller ved å bruke beregnede felt og beregnede elementer. I fig. Figur 1 viser hvordan et beregnet felt kan presentere data fra et annet perspektiv. Pivottabellen viser salgsvolum og tid (i timer) brukt på å selge utstyr i hvert marked. Et beregnet felt som beregner gjennomsnittlig inntekt per time forbedrer muligheten til å analysere data i en pivottabell.

Ris. 1. Du må opprette et beregnet felt i en eksisterende pivottabell Gjennomsnittlig inntekt per time, som vil gi ny mening til de analyserte dataene

Last ned notatet i eller format, eksempler i format

Et beregnet felt er et datafelt som er opprettet som et resultat av beregninger basert på eksisterende pivottabellfelt. Det beregnede feltet legges til datasettet som en virtuell kolonne. Denne kolonnen er ikke inkludert i kildedataene, inneholder verdier bestemt av en formel, og samhandler med pivottabelldata på samme måte som andre pivottabellfelt.

Med beregnede felt kan du sette inn en formel i en pivottabell, slik at du kan lage ditt eget felt. De nyopprettede dataene vil bli en del av pivottabellen og samhandle med eksisterende data. Når du oppdaterer pivottabeller, blir beregnede felt beregnet på nytt, og selve pivottabellen er fylt med informasjon som ikke var i det opprinnelige datasettet.

Se nå på fig. 5.1 og spør deg selv: Hvorfor trengte du å legge til beregnede felt? Hvorfor ikke bruke vanlige celleformler eller utføre de nødvendige beregningene direkte på kildetabellen for å få informasjonen du trenger?» For å svare på disse spørsmålene, la oss se på de forskjellige metodene du kan bruke for å lage det beregnede feltet vist i figur 1.

Metode 1: Legg til et beregnet felt i datakilden

Du kan legge til et beregnet felt i datakilden, som vist i figuren. 2, slik at pivottabellen kan bruke dette feltet som et standard datafelt. Hvis arrangementet av informasjon på datakildearket gjør at dette kan gjøres, hva kan da være den beste løsningen. Forresten, å legge til en ny kolonne krever ikke bare å oppdatere pivottabellen, men å endre omfanget som pivoten er basert på. For å gjøre dette, klikk på sammendrag og gå gjennom menyen Analyse → Datakilde → Datakilde.

Jeg prøver å gjøre de originale dataene om til en tabell (noe som er gjort i fig. 2 og i den vedlagte Excel-filen). I dette tilfellet utheves for det første overskriftene, for det andre vises knapper med filtre, noe som ofte er nyttig for datautforskning, for det tredje farges radene én om gangen, og til slutt, det viktigste, når man legger til rader og kolonner er det ikke nødvendig å endre dataområdet for en pivottabell. Området spesifisert som sådan er ikke et rektangulært område, men navnet på tabellen. I vårt tilfelle - Kilde(Fig. 3).

Ris. 3. Dannelse av et sammendrag basert på et spesielt Excel-verktøy – Tabell

Men denne metoden har også ulemper: For det første er det ikke alltid praktisk å legge til en kolonne i kildedataene, og for det andre er mulighetene begrenset hvis strukturen til kildedataene endres (for eksempel eksporterer du data fra 1C, og den nye eksporten har en kolonne til; denne nye kolonnen vil overskrive beregningskolonnen).

Metode 2: Bruk en formel utenfor pivottabellen for å lage et beregnet felt

Du kan legge til et beregnet felt ved siden av en pivottabell. I fig. 4 hver celle i en kolonne Gjennomsnittlig inntekt per time utstyrt med en formel som refererer til en pivottabell. Forresten, hvis, når du prøver å referere til en pivottabellcelle, GET.PIOTTABLE.DATA-funksjonen dukker opp i formelen din, vil du ikke kunne "dra" formelen. For å overvinne denne vanskeligheten, les notatet.

Ris. 4. Når du skriver inn en formel etter å ha opprettet en pivottabell, legger du i hovedsak til et beregnet felt som endres når selve pivottabellen oppdateres

Selv om denne metoden legger til et beregnet felt som oppdateres når pivottabellen oppdateres, kan eventuelle endringer i pivottabellstrukturen gjøre formelen ubrukelig. For eksempel, som et resultat av å dra Salgsmarked-feltet inn i FILTER-området i listen over felt i en pivottabell, endres strukturen til rapporten, som et resultat av at en feilmelding vises i det beregnede feltet (fig. 5). Hvis du legger til et annet felt i KOLONNER-området i pivottabellen, vil kolonnen Gjennomsnittlig inntekt per time vil bli overskrevet.

Metode 3: Sette inn et beregnet felt direkte i pivottabellen

Å sette inn et beregnet felt direkte i en pivottabell er ofte den beste løsningen. Hvis du bruker denne metoden, trenger du ikke å justere formlene. Det vil også være mulig å justere tabellen automatisk i henhold til endret datakilde. Nødvendig grad av fleksibilitet vil oppnås for å sikre automatisk omberegning av felt ved endringer i måleenheter.

En annen fordel med denne metoden er at hvis du bruker den, kan du endre strukturen til pivottabellen og til og med datafeltene for de forskjellige måleenhetene som brukes i de beregnede feltene. Samtidig kan du være sikker på at det ikke vil være feil i formler og cellereferanser vil ikke gå tapt.

Pivottabellrapporten vist i figur 6 viser en oppsummeringstabell, som er vist i fig. 1, bortsett fra at den er rekonstruert for å vise gjennomsnittlig inntekt per time for hver tjeneste og hvert marked.

Ris. 6. Det tidligere opprettede beregnede feltet forblir "gyldig" selv om strukturen til pivottabellen endres, noe som resulterer i visning av gjennomsnittlig inntekt per time for hver tjeneste og hvert salgsmarked

Lag et beregnet felt

Før du kan opprette et beregnet felt, må du åpne eller generere en pivottabell. Vårt eksempel bruker pivottabellen vist i fig. 1, men uten kolonne D. For å lage et beregnet felt, aktiver dialogboksen Sette inn et beregnet felt. For å gjøre dette, klikk på pivottabellen for å aktivere gruppen med kontekstuelle faner Arbeide med pivottabeller. Gå til fanen Analyse, til gruppen Beregninger, klikk på knappen Felt, elementer og sett og velg kommandoen Kalkulert felt fra menyen (fig. 7).

En dialogboks vises på skjermen Sette inn et beregnet felt(Fig. 8). Det er to tekstfelt øverst i dialogboksen: Navn Og Formel. I disse feltene må du angi navnet på det beregnede feltet og lage en formel ved å angi de nødvendige datafeltene og matematiske operatorene. Det beregnede feltet får et beskrivende navn Gjennomsnittlig inntekt per time. Dette navnet bør velges for nøyaktig å beskrive typen matematisk operasjon som utføres. Standard tekstfelt Formel dialogboks Sette inn et beregnet felt inneholder uttrykket = 0. Før du skriver inn formelen, må du fjerne nullen. Velg ett av feltene i området Enger; i vårt tilfelle - Salgsvolum og klikk Legg til et felt. Feltnavnet vil vises på linjen Formel. Skriv inn divisjonstegnet / og velg deretter det andre feltet – Salgsperiode (i timer). Klikk Legg til, og så OK for å aktivere det nye beregnede feltet.

Et nytt beregnet felt vil vises i pivottabellen Gjennomsnittlig inntekt per time(se fig. 1). Et nytt beregnet felt har nettopp blitt lagt til i en pivottabell. Du kan nå endre innstillingene for dette nye feltet på samme måte som du ville endret innstillingene for alle andre felt (for eksempel feltnavn, tallformat eller farge).

Betyr dette at du har lagt til kolonnen i datakilden? Nei. Beregnede felt er som elementer Delsum Og totale mengden Pivottabellfunksjoner er standard fordi de alle er matematiske funksjoner som beregner data på nytt når pivottabellen endres eller oppdateres. Beregnede felt etterligner ganske enkelt eksplisitt definerte felt i datakilden. Du kan dra dem rundt, endre feltinnstillinger og bruke dem sammen med andre beregnede felt.

Vær oppmerksom på at i fig. 8 Formatet på formelen du skrev inn, ligner det som brukes på standardlinjen Excel-formler. Hovedforskjellen er at i stedet for å bruke strenge cellereferanser eller numeriske verdier, refererer du til pivottabelldatafelt for å tilordne verdier til argumenter som brukes i beregninger.

Opprette beregnede elementer

Et beregnet element er i hovedsak en virtuell rad med data som legges til som et resultat av at beregninger utføres på andre rader i samme felt. Svært ofte kan et resultat som ligner det som oppnås når du oppretter en beregnet vare oppnås ved å gruppere dataene. Faktisk, i mange tilfeller gir gruppering et utmerket alternativ til beregnede medlemmer.

I sammendragstabellen vist i fig. 9, er det totale salgsvolumet for visse rapporteringsperioder angitt (felt Rapporteringsperiode). Tenk deg at du vil sammenligne gjennomsnittlig salgsvolum for de siste seks periodene med gjennomsnittlig salgsvolum for de foregående syv periodene. For å være mer presis må vi bestemme gjennomsnittet for periodene P01–P07 og sammenligne det med gjennomsnittet for periodene P08–P13.

Plasser pekeren i en hvilken som helst celle i feltet Rapporteringsperiode gå til den kontekstuelle båndfanen Analyse til gruppen Databehandling, og klikk på knappen Felt, elementer og sett. Velg kommandoen i menyen som åpnes Beregnet objekt(Fig. 10).

En dialogboks åpnes (Fig. 11). Legg merke til at det øverst i dialogboksen angir hvilket felt du jobber med. I dette tilfellet dette feltet Rapporteringsperiode. I tillegg inneholder listen alle elementene i feltet Rapporteringsperiode. Gi det beregnede elementet et navn, og lag deretter en formel med den nødvendige kombinasjonen av dataelementer og operatorer som gir det riktige resultatet. Formelen legges inn i feltet Formel, og de nødvendige dataelementene som brukes i den, velges i listen Elementer. Klikk på knappen OK, og det beregnede elementet vil bli lagt til pivottabellen (fig. 12).

Ris. 11. Dialogboks Sette inn et beregnet element

På samme måte oppretter du en beregnet vare som vil representere gjennomsnittlig salg for periodene P08–P13. Skjul deretter de enkelte rapporteringsperiodene, og la bare to beregnede poster stå igjen. Med litt formatering vil våre beregnede varer brukes til å sammenligne gjennomsnittlig salg for de foregående og påfølgende seks månedene (Figur 13).

Hvis du ikke skjuler dataelementene som brukes i beregningen av beregnede poster, kan delsummer og totalsummer være feil.

Fordeler og ulemper med pivottabellberegninger

Det er også visse ulemper ved å bruke beregnede felt og elementer. Det er viktig å forstå hva som skjer når du beregner data i pivottabeller, men det er viktigere å forstå begrensningene til beregnede felt og beregnede elementer for å forhindre feil når du analyserer dataene dine.

Prioritering av operasjoner. Som i regneark kan enhver operator brukes i formler for beregnede elementer og felt: +, –, *, /, %, ^, etc. Dessuten, i likhet med regneark, utføres beregninger i pivottabeller i henhold til operatørprioritet. Når du utfører en beregning som kombinerer flere operatorer, for eksempel (2+3) *4/50 %, evaluerer Excel uttrykket og utfører beregningen i en bestemt rekkefølge. Å kjenne til denne bestillingen vil spare deg for mange feil.

Så rekkefølgen på operasjoner i Excel er som følger:

• bearbeide uttrykk i parentes;
• rekkeviddebehandling(:);
• behandling av kryss (områder);
• bearbeiding slutter (;);
• utføre en negasjonsoperasjon;
• konvertering av prosentverdier (for eksempel konverteres 50% til 0,50);
• eksponentiering (^);
• multiplikasjon (*) og divisjon (/); disse operasjonene har samme prioritet;
• addisjon (+) og subtraksjon (–); disse operasjonene har samme prioritet;
• å kombinere tekstdata (&) kalles denne operasjonen også sammenkobling;
• utføre sammenligningsoperasjoner (=,<>, <=, >=).

Ekvivalente operatorer i samme uttrykk utføres alltid i rekkefølge (fra venstre til høyre).

La oss se på et enkelt eksempel. Som du vet, gir uttrykket (2+3)*4 resultatet 20. Hvis du fjerner parentesene og lar uttrykket være 2+3*4, så vil Excel beregne: 3*4 = 12 + 2 = 14.

La oss se på et annet eksempel. Hvis du skriver inn uttrykket 10^2 som en formel, dvs. Hvis du spesifiserer å kvadrere tallet 10, vil programmet returnere verdien 100 som svar. Hvis du skriver inn uttrykket –10^2, kan du forvente at verdien returnerer –100. Excel vil imidlertid igjen returnere verdien 100. Årsaken er at programmet utfører negasjonsoperasjonen før eksponentiering, dvs. verdien 10 konverteres først til -10, og resultatet av -10*-10 er faktisk 100. Bruk av parenteser i formelen -(10^2) sikrer at programmet utfører eksponentieringen først og deretter negasjonen, og returnerer den forventede resultat - 100.

Cellereferanser og navngitte områder. Cellereferanser eller navngitte områder kan ikke brukes når du oppretter beregnede felt og elementer, fordi når du oppretter beregnede objekter i en pivottabell, jobber du i hovedsak utenfor objektområdet. De eneste dataene som er tilgjengelige for brukere, er i pivottabellbufferen. Fordi du ikke kan gå utover hurtigbufferen, kan du ikke referere til celler eller navngitte områder i formelen du oppretter.

Arbeidsarkfunksjoner. Du kan bruke hvilken som helst regnearkfunksjon som ikke bruker cellereferanser eller navngitte objekter som argumenter. Faktisk kan du bruke hvilken som helst regnearkfunksjon som ikke krever cellereferanser eller navngitte objekter. Funksjoner som COUNT, AVERAGE, IF, AND, NO og OR faller inn under denne kategorien.

Konstanter. Du kan bruke alle konstanter i beregninger utført i pivottabeller. Konstanter er statiske verdier som ikke endres over tid. For eksempel, i følgende formel er tallet 5 en konstant: [Selgte enheter] *5. Selv om meningen Enheter solgt kan endres basert på tilgjengelige data, vil tallet 5 alltid være det samme.

Lenker til totaler. Formler som utfører beregninger kan ikke referere til pivottabellundersummer eller totaler. Du kan med andre ord ikke bruke resultatet av en løpende eller total beregning som en variabel eller argument i et beregnet felt.

Spesielle regler for beregnede felt. Beregninger i beregnede felt utføres alltid på totaler, ikke enkeltdataposter. Enkelt sagt, Excel beregner alltid datafelt, delsummer og totaler før det evalueres et beregnet felt. Dette betyr at det beregnede feltet ditt alltid brukes på totalsummene for kildedataene. Eksempelet vist i fig. 14 viser hvordan dette kan påvirke dataanalysen.

Ris. 14. Selv om et beregnet felt er gyldig for individuelle dataelementer, er det ikke matematisk riktig å bruke det for delsummer

I hvert kvartal må du få de endelige salgsvolumene for hvert produkt ved å multiplisere antall solgte enheter med enhetsprisen for det produktet. Hvis du først ser på dataene for første kvartal, vil du umiddelbart identifisere problemet. I stedet for å beregne summen av 220+150+220+594, som er 1184, multipliseres summen av antall enheter med summen av prisene på varene, noe som resulterer i en feil verdi. Dessverre kan dette problemet ikke løses, men det kan løses. Eliminer delsummer og delsummer fra pivottabellen, og beregn deretter en ny total nederst utenfor pivottabellen.

Spesielle regler for bruk av kalkulerte medlemmer. En pivottabell kan ikke bruke beregnede elementer som beregner gjennomsnitt, standardavvik eller avvik. Omvendt kan du ikke bruke midler, standardavvik eller avvik i en pivottabell som inneholder et beregnet element. Du vil ikke kunne bruke et sidefelt til å opprette et beregnet element, eller du vil ikke kunne flytte beregnede elementer inn i rapportfilterområdet. Du kan ikke legge til et beregnet element i en rapport som inneholder et gruppert felt, og du kan ikke gruppere noe felt i en pivottabell som inneholder et beregnet element. Når du oppretter en beregnet vareformel, kan du ikke referere til en vare fra et eksternt felt.

Administrer og støtte beregninger i pivottabeller

Når du arbeider med pivottabeller, oppstår det ofte situasjoner der det ikke gir mening å beholde pivottabellen lenger enn det tar å kopiere individuelle verdier. Imidlertid er det ofte situasjoner hvor det er mer lønnsomt å lagre pivottabellen og alle dens elementer uendret. Hvis du vedlikeholder og administrerer en pivottabell ved å endre krav og øke volumet av kildedata, må du administrere beregnede felt og beregnede medlemmer.

Redigering og sletting av beregninger i pivottabeller. Hvis du endrer beregningsinnstillingene eller ikke lenger trenger et beregnet felt eller beregnet medlem, kan du åpne den tilsvarende dialogboksen for å redigere eller slette beregningen. Aktiver dialogboksen Sette inn et beregnet felt eller Sette inn et beregnet element(se beskrivelse til fig. 7 og 10) og velg nedtrekkslisten i den Navn(Fig. 15).

Endre rekkefølgen beregningene utføres i i beregnede felt. Hvis verdien av en celle i en pivottabell avhenger av resultatet av beregningen av flere beregnede elementer, kan du endre rekkefølgen som operasjonene i de beregnede feltene utføres i. Du kan med andre ord spesifisere rekkefølgen som individuelle beregninger skal utføres i. For å åpne dialogboksen , plasser pekeren hvor som helst i pivottabellen og klikk på ikonet Felt, elementer og sett. I rullegardinmenyen velger du kommandoen Beregningsrekkefølge (fig. 16).

Ris. 16. Åpne dialogboksen Beregningsrekkefølge

I dialogboksen Beregningsrekkefølge(Fig. 17), vises alle beregnede elementer som legges til pivottabellen. Velg et hvilket som helst beregnet element i listen og bruk knappene Opp, Ned Og Slett. Rekkefølgen formlene vises i listen bestemmer den nøyaktige rekkefølgen beregningene utføres i i pivottabellen.

Dokumentasjon av formler. Excel gir deg et utmerket verktøy som viser de beregnede feltene og de beregnede elementene som brukes i en pivottabell, samt hvordan du utfører beregninger og bruker formler. Dette verktøyet er veldig nyttig for å analysere en tredjeparts pivottabell eller når du raskt må finne ut hvilke beregninger som brukes i den og hvilke felt og elementer de påvirker. For å lage en rapport om pivottabellberegninger, hold pekeren hvor som helst i pivottabellen og klikk Felt, elementer og sett og velg kommandoen fra rullegardinmenyen Utlede formler. Excel vil lage en rapport om beregnede felt og elementer på et eget ark (fig. 18).

Ris. 18. Lag Utlede formler lar deg raskt og enkelt dokumentere beregninger i en pivottabell

Notatet ble skrevet basert på boken av Jelen, Alexander. . Kapittel 5.

Ordet Tabell er stort fordi det ikke bare er en tabell, men et eget Excel-verktøy.

Den første måten å lage et beregnet felt på er basert på at skjemaet er basert på en spørring som inkluderer beregnede kolonner. Skjemaet oppretter et tekstfelt som spesifiserer en beregnet spørringskolonne som datakilde. Feltet er låst slik at brukeren ikke kan endre dataene i det. Data beregnes under kjøring av spørringer. For å låse et felt i modus Designer i vinduet Egenskaper beregnet felt på Data-fanen på rad Låse Tast inn JA.

I det andre tilfellet er skjemaet bygget på grunnlag av en tabell. Et nytt felt opprettes i formen, der et uttrykk er spesifisert som datakilde, for eksempel = [Enhetspris]*Antall. Ved henvisning til felt i uttrykk er det obligatorisk å bruke hakeparenteser dersom feltnavnet består av mer enn ett ord. Å legge inn et uttrykk direkte i en kontroll (felt) er tillatt hvis den er kort og lengden på feltet tillater det. Ellers legges uttrykket inn i egenskapscellen Data vindu Egenskaper. Du kan bruke uttrykksbyggeren for å gjøre dette. Hvis uttrykket ikke passer inn i egenskapscellen, må du trykke på +-tastene og åpne vinduet Inndataområde. Om nødvendig bør du angi feltformatet når det vises på skjermen.

6. Opprette et knappeskjema

Velg menykommando Service/Utilities/Pushbutton Form Manager.

I dialogboksen som spør om du vil opprette et knappeskjema, klikker du JA.

Et vindu vises på skjermen Button Form Manager. Dette vinduet inneholder en liste over knappeskjemasider. I begynnelsen av arbeidet er vinduet en side - Hovedknappskjema. Klikk på knappen i vinduet Endring.

En dialogboks åpnes Endre en knappeskjemaside. Rediger navnet på skjemaet.

Klikk på knappen Skape. Et vindu vises Endre et knappeskjemaelement.

I felt Tekst Skriv inn navnet på knappen for å opprette. Navnet på knappen skal være assosiert av brukeren med objektene han har til hensikt å jobbe med. Andre felt Team lar deg definere handlingen som skal utføres etter å ha klikket på denne knappen. Velg kommandoen som kreves for applikasjonen din fra rullegardinlisten.

Legg til knapper for alle skjemaer og rapporter, strukturer dem etter type.

Legg til en knapp for å avslutte applikasjonen, samt en knapp til rask tilgang Til Knappeskjemabehandler.

Sett opp applikasjonen til Hovedknappskjemaåpnet når du åpner databasen. For å gjøre dette, bruk menykommandoen Verktøy/Oppstartsalternativer.

EKSEMPEL:

Ris. 28. Toppnivå knappeskjema

Lab 3 Opprette spørringer

Målet med arbeidet - få praktiske ferdigheter i å utvikle databasespørringer.

Trening:

Test databaseskjemaet ved å bruke typiske spørringer. Du må opprette minst én forespørsel av hver type beskrevet nedenfor. I rapporten skal spørringer presenteres på naturlig språk, i SQL, og bør også gi et eksempel på resultatet av å utføre spørringen.

Arbeidssekvens

1.Opprett en spørring for å velge én tabell ved hjelp av spørringsbyggeren

I databasevinduet klikk på bokmerket Forespørsler.

Klikk på knappen Skape.

I vinduet Ny forespørsel plukke ut Konstruktør og trykk på knappen OK.

I vinduet Legger til en tabell Velg fra listevinduet tabellen (eller spørringen) som spørringen skal baseres på, og klikk på knappen Legg til. Lukk gjeldende vindu. Deretter vises et vindu på skjermen Spørringsbygger.

Legg til et felt i forespørselen. For dette formålet i kildetabell, plassert øverst i vinduet prøveforespørsel, velg ønsket felt. Dobbeltklikk med venstre museknapp på det uthevede feltet. Samtidig, nederst i vinduet Forespørsler en kolonne som tilsvarer det valgte feltet vil vises i forespørselsskjemaet.

Du kan også legge til et felt i en forespørsel som følger: klikk venstre knapp musen på det valgte feltet og, uten å slippe det, dra feltet til ønsket sted på forespørselsskjemaet; Klikk på et cellefelt i forespørselsskjemaet for å vise en rullegardinliste med felt og velg det nødvendige feltet fra det; Klikk på cellefeltet i forespørselsskjemaet og skriv inn feltnavnet.

For å legge til alle tabellfelt i en spørring, velg feltet angitt med en stjerne (*). Stjernen gjør arbeidet med å konstruere en spørring enklere, men gjør sortering og inntasting av betingelser for å velge felt mer komplisert.

Som standard er ikke alle forespørselsfelt sortert. Om nødvendig kan du spesifisere i linjen Sortering spørringsskjema, hvordan du viser kolonneverdier: stigende eller synkende. Når du angir en sorteringsrekkefølge i flere felt, sorteres postene etter kolonnen lengst til venstre først, og deretter etter hver påfølgende kolonne fra venstre mot høyre.

På linje Utgang på display merk av i boksene du vil se i postsettet etter å ha kjørt spørringen. Som standard vises alle feltene som er inkludert i forespørselsskjemaet.

I forespørselsskjemaet i linjen Utvalgsbetingelse og i køen Eller spesifisere betingelsene for prøvetaking fra databasen. Forhold er logiske uttrykk.

En logisk operasjon utføres på utvalgsbetingelser plassert på én linje OG. Flere valgbetingelser for ett felt kan angis på én av to måter: skriv inn alle betingelsene i én celle i raden Utvalgsbetingelse, koble dem med en logisk operatør ELLER, eller angi hver betingelse i en separat celle i raden Eller. Flytt til neste rad med celleområdet ELLER med nøkkel<>. Uttrykk i en celle i forespørselsskjemaet legges inn fra tastaturet eller brukes til å lage dem. Uttrykksbygger, som startes av kommandoen Bygge fra kontekstmenyen knyttet til linjen Utvalgsbetingelse(eller du kan trykke på knappen Bygge på verktøylinjen Adgang).

Se søkeresultatene ved å klikke på knappen Lansering(knapp med utropstegn) eller knapp Utsikt på verktøylinjen.

I situasjoner der du trenger å endre en spørringsegenskap, dobbeltklikker du på det tomme området øverst i vinduet Forespørselskonstruktør - et vindu åpnes Be om egenskaper.

Forespørsler opprettet i moduser Designer Og mestere, generere kommandoer i SQL-språk. Du kan se disse kommandoene ved å velge fra menyen Utsikt alternativ modusSQL.

EKSEMPEL:

Velg informasjon om produkter i mengder større enn 100, men mindre enn 300 og koster mindre enn 100 rubler.

Ris. 10. Spørringskonstruktør med tilstand

Ris. 11. Spørreresultat med tilstand

2. Indre skjøt på ett felt

Velg en fane Forespørsler databasevinduet, klikk på knappen Skape. En dialogboks åpnes Ny forespørsel. Velg et alternativ Konstruktør.

I vinduet Legger til en tabell velg sekvensielt flere relaterte tabeller som spørringen skal bygges på. På toppen Spørringsbygger relaterte tabeller vises.

Fyll ut forespørselsskjemaet.

Klikk på knappen Lansering eller Utsikt for å vise resultatene av spørringen.

EKSEMPEL:

Velg navnene på produktene som det er inngått en kontrakt for, og angi navnene på produsentene og leveringsvolumet.

Ris. 12. Multi-table spørring designer

Ris. 13. Resultat av en flertabellspørring

3. Eksterne tilkoblinger

Følg trinn 1-3 i avsnittet Intern sammenføyning på ett felt.

Etabler et forhold mellom tabellene hvis det ikke tidligere har vært registrert i Opplegg DB.

Klikk på feltsammenslåingslinjen for å markere den.

Velg kommandoer fra menyen Utsikt alternativ Alternativer for sammenslåing. En dialogboks vises Sammenslåingsalternativer.

Velg sammenføyningstype, klikk på knappen OK. Etter denne operasjonen vil en pil vises på sammenføyningslinjen som indikerer type tilkobling.

4. Inkluderer en gruppetransaksjon i forespørselsskjemaet

Klikk på knappen Konserndrift, signere  på verktøylinjen Spørringsbygger. I forespørselsskjemaet over linjen Sortering linjen vises Gruppedrift.

Bytt ut i kø Gruppedrift installasjon Gruppering for det nødvendige gruppedrift ved å velge det fra kombinasjonsboksen: Sum, Gj.sn, Telle, Mi, Maks, StDev, Var, Først, Siste, Uttrykk(uttrykk),Hvor(betingelse),Gruppe av(gruppering).

Uttrykk lar deg skrive inn et uttrykk i stedet for et feltnavn i en celle , for eksempel for å beregne verdiområdet for en bestemt kolonne X:Maks([ X]) – Min([ X]).

Betingelse indikerer at dette feltet vil bli brukt til å angi betingelsene for valg av poster. Selve utvalgsbetingelsen skrives til radcellen Utvalgsbetingelse.

3. Fullfør forespørselen.

EKSEMPEL:

Velg det totale antallet produkter fra hver produsent, og angi navnet.

Ris. 14. Spørringskonstruktør med gruppering av poster

Ris. 15. Resultat av en spørring med gruppering av poster

5. Inkluderer beregnede felt i et forespørselsskjema

Spørringsbygger .

Klikk i linjen Felt i henhold til den tomme kolonnen i forespørselsskjemaet. Skriv inn kolonnenavnet, etterfulgt av et kolon, etterfulgt av et uttrykk. For å legge inn et uttrykk, klikk på knappen Bygge, å utnytte Uttrykksbygger.

På toppen Uttrykksbygger Lag et uttrykk i inndataområdet.

Flytt markøren til raden i en beregnet feltkolonne Gruppedrift og velg fra rullegardinlisten Uttrykk(hvis du ikke velger alternativet Uttrykk, når den utføres, åpner forespørselen en dialogboks Angi parameterverdi eller returnerer en feilmelding).

Kjør forespørselen.

EKSEMPEL:

Beregn det totale ordrebeløpet for hver kontrakt (definert som produktet av varemengden og prisen pluss transportkostnader).

Ris. 16. Spørringskonstruktør med et beregnet felt

Ris. 17. Resultat av en spørring med et beregnet felt

6.Opprette en parametrisk spørring

Opprett et utvalgsspørring i modus Spørringsbygger.

Skriv inn strenger i en av cellene Utvalgsbetingelse en parameter, ikke en verdi. For eksempel, hvis du velger poster etter etternavn, da parametrisk spørring på banen Etternavn i valgbetingelsene kan setningen [skriv inn etternavn] skrives i hakeparenteser.

Velg fra menyen Be om alternativ Alternativer. I dialogboksen Be om parametere i venstre kolonne skriver du inn parameteren som samsvarer med parameteren i hakeparenteser forespørsel fra Spørringsbygger(parameteren kan kopieres fra forespørselsskjemaet). Angi datatypen i høyre kolonne: trykk på tasten , velg en datatype fra listen.

Kjør utførelsesforespørselen. Adgang vil vise et vindu Angi parameterverdi, der skriv inn parameterverdien du ønsker og trykk på knappen OK.

5. Kjør utførelsesforespørselen.

EKSEMPEL:

Velg en liste over produkter hvis utløpsdato overskrider den angitte verdien.

Ris. 18. Parametrisk spørringsbygger

Ris. 19. Angi utløpsdatoen til produktet

Ris. 20. Resultat av en parametrisk spørring

7. Opprette kryssforespørsler

1. Lag med Designer ny forespørsel og inkludere de nødvendige tabellene i den.

2. Velg feltene hvis verdier skal brukes til å lage rader og kolonner i den dynamiske tabellen, og dra dem inn i spørringsskjemaet.

3. Velg en kommando fra menyen Spørring/kryss. Overskrift Designer vil endre seg fra Spørsmål 1: per prøve på Spørring 1: Kryssspørring . Forespørselsskjemaet vil vise linjen Kryssbord .

4. For felt som skal være krysstabellrader, åpne rullegardinlisten Rad Kryssbord velg alternativ Radoverskrifter. Hvert felt som er en linjeoverskrift må ha en gruppeoperasjonsinnstilling i linjen Gruppering.

5. For felt som skal være krysstabellkolonner, avhengig av spørringen, er følgende handlinger mulig:

på linje Kryssbord velg alternativ Kolonneoverskrifter og i kø Gruppedrift installasjon gruppering, eller i kø Gruppedrift – Betingelse, i linjer Utvalgsbetingelse Og ELLER skrive ned logisk uttrykk.

6. Angi feltet som den totale verdien skal opprettes på grunnlag av. På linje Kryssbord velge Betydning. På linje Gruppedrift velg den siste funksjonen eller Uttrykk. I sistnevnte tilfelle, i cellen Felt skriv ned uttrykket.

7. Kjør utførelsesforespørselen.

EKSEMPEL:

Velg det totale antallet bestilte produkter av hver type produsert av hver produsent. Presenter resultatet i form av en tabell, der radene er navn på produkttyper og kolonnene er navn på produsenter.

Ris. 21. Cross Query Builder

Ris. 22. Resultat av kryssspørring

8.Spørsmål om å lage en tabell

Følg disse trinnene for å opprette en tabell fra resultatene av en spørring som ble brukt til å velge poster i relaterte tabeller:

Lag en kopi av spørringen for å velge poster fra tabeller (eller opprett en ny).

Åpne den opprettede forespørselen i modus designer, ved å markere navnet i listen over spørringer i databasevinduet og klikke på knappen Konstruktør.

Velg en kommando fra menyen Spørr/opprett tabell.En dialogboks åpnes Opprette en tabell. Skriv inn navnet på det nye bordet, la radioknappen stå på plass I gjeldende database.

Klikk på knappen OK. Select-spørringen konverteres til en tabellopprettingsspørring.

Kjør spørringen ved å dobbeltklikke på spørringsnavnet i DB-vinduet. Før spørringen kjøres, vises en melding som advarer deg om at endringer vil bli gjort i den nye tabellen (selv om den ennå ikke er opprettet).

Klikk på knappen Ja. En annen advarsel vises om antall poster som vil bli plassert i den nye tabellen som et resultat av spørringen. Klikk på knappen Ja.

Utvid kategorien Tabeller i DB-vinduet; En ny tabell skal vises i listen.

9.Forespørsel om å oppdatere poster

Opprett en ny spørring og ta med den opprettede kopien av tabellen i den.

Velg en kommando fra menyen Spørring/oppdatering. I linjeforespørselsskjemaet Sortering Og Utgang på display vil bli erstattet av strengen Oppdater.

På linje Oppdater angi et uttrykk som representerer den nye verdien for det gjeldende feltet.

Hvis informasjon ikke er oppdatert i alle poster i tabellen, men bare i deler av den, angi en betingelse for valg av poster.

Fullfør forespørselen. Sjekk at postene er riktig oppdatert.

EKSEMPEL:

Oppdater kostnadene for produkter hvis mengde overstiger 500 enheter, og sett den til 90 % av den opprinnelige kostnaden.

Ris. 23. Oppdater forespørselskonstruktør

10.Forespørsel om å slette poster

Lag en kopi av tabellen der postene skal oppdateres.

Opprett en ny spørring og ta med den aktuelle tabellen i den.

Dra og slipp felt i forespørselsskjemaet hvis verdier vil bli brukt til å velge felt. På linje Utvalgsbetingelser angi utvalgskriteriene.

Kjør en utvalgsspørring for å vise postene som skal slettes.

Åpen databasevinduet. Velg en fane Tabeller.

Aktiver den opprettede valgforespørselen og gå til modusen Spørringskonstruktør.

Velg en kommando fra menyen Be om/slett. I linjeforespørselsskjemaet Sortering Og Utgang på display vil bli erstattet av strengen Fjerning.

Klikk på knappen Lansering verktøylinjer. Det vises en meldingsboks som ber om bekreftelse for å slette oppføringene.

Sletting av poster i tabellen " en", for hvilke i tabellen " mange" det er relaterte poster, bryter dataintegritetsregelen. Derfor kan dataintegritetsalternativet som er angitt for et forhold mellom tabeller, forhindre at poster slettes. En kaskadesletting bør brukes.

EKSEMPEL:

Ris. 24. Slett forespørselskonstruktør

11. Forespørsel om å legge til poster

En append-spørring brukes ofte til å kopiere data fra en tabell til en annen.

Velg en fane Forespørsler databasevinduet, klikk på knappen Skape. En dialogboks åpnes Ny forespørsel. Velg et alternativ Konstruktør.

I vinduet Legger til en tabell velg tabellen som dataene skal hentes fra.

Flytt til linje Felt forespørselsskjema for felt hvis verdier vil bli inkludert i en annen tabell.

Velg menykommandoer Be om/legg til. Et vindu vises Addisjon, hvor du må spesifisere navnet på tabellen du skal legge til data i, og hvilken database den tilhører. Hvis databasen ikke er den nåværende, men en annen, må du spesifisere hele navnet på filen som inneholder databasen.

I forespørselsskjemaet etter linjen Sortering linjen vil bli inkludert Addisjon, der du må angi tabellfeltene der data legges til. Hvis feltnavnene er de samme ENMedcess vil automatisk settes inn i linjen Addisjon feltnavn.

Kjør utførelsesforespørselen. Se hvordan tabellen har endret seg med nye rader lagt til.

Hvis i en forespørsel om Addisjon det er felt som Disk, så kan du jobbe med slike felt på to måter:

ikke inkludere typefelt Disk i feltene som må legges til; så i tabellen hvor dataene er lagt til, vil nummereringen av datasekvensen fortsette, dvs. hvis det er en tabell over ansatte med tall fra 1 til 100 og legg til den nye oppføringer, så vil nummereringen deres starte fra nummer 101;

inkludere felt av typen Disk til forespørselslisten som lagt til felt, vil verdiene som de hadde i den opprinnelige tabellen bli lagret i feltene til de lagte postene; hvis slike verdier allerede finnes i feltene i tabellen som poster legges til, kan dette resultere i en feil. En feilmelding vises på skjermen før forespørselen utføres. Hvis det er en feil, velg knappen Avbryt og fikse feilen.

Metode 2: Høyreklikk på verdiene til ønsket felt i ΣValues-området og velg:

I tillegg, for å velge en annen funksjon fra den foreslåtte listen, konfigurer feltformatet og endre feltnavnet.

For å utnytte effektene. I dag skal vi lære deg. Merk cellene du vil tegne. Leksjon 3: Tabeller og pivotdiagrammer. La oss si at vi har et sett å analysere. For å bruke en pivottabell, velg en hvilken som helst celle på regnearket og velg Pivottabell fra Sett inn-menyen.

Da kan du få informasjonen. Når du lager diagrammer, spiller det ingen rolle om tabellen har en kantlinje eller andre formateringselementer. Introduksjon til formler og funksjoner Å utføre beregninger, enten enkle eller komplekse, kan være kjedelig og tidkrevende.

 Ytterligere beregninger, og velg deretter ønsket beregningsalternativ.

Gjenbruk av et originalfelt i en pivottabellrapport

Du kan gjøre flere beregninger ved å bruke samme kildefelt. For å gjøre dette må du legge til det opprinnelige feltet fra listen over felt i pivottabellen til verdiområdet Σ Verdier [Σ

Verdier] flere ganger, og juster deretter feltparametrene. Dette vil tillate deg å samtidig se både selve beregningen og beregningen utført basert på den.

Legg til beregnede felt i en pivottabell

Du kan ikke legge til nye rader eller kolonner i en pivottabellrapport for å sette inn formler eller legge til flere beregninger, men du kan legge til et beregnet felt som lar deg legge inn formler for å beregne nye data basert på eksisterende data.

Databaser Arbeidsark 1 Databaser Arbeidsark 1 Opprett en "Databaser"-mappe for å lagre alle kursdata. Oppretter en spørring som viser titler og beskrivelser. Dialogboksen Administrer database vises. Ved å bruke dette vinduet administrerer brukeren databasen og alle dens elementer.

Hva er et regneark? Et regneark er et program for å utføre matematiske beregninger. Med dens hjelp kan vi også gjøre lesbare beregninger i skjemaet. For å dra nytte av spesifikke handlinger. Den mest arbeidskrevende delen er dataforberedelse. Oppgave 1 Mattegruppe 2 Følg disse trinnene etter tur. Kjør bordet.

Å sette inn et beregnet felt direkte i en pivottabell er beste løsningen. Det er ikke nødvendig å administrere formler eller bekymre deg for tabellutvidbarhet ettersom datakilden vokser eller redigeres, og pivottabellen forblir fleksibel nok til å endre feltdefinisjoner.

En annen stor fordel med denne metoden er at du kan endre strukturen til pivottabellen og til og med beregne andre datafelt for beregnede felt uten å bekymre deg for formelfeil eller ødelagte cellereferanser.

West Pomeranian University of Technology i Szczecin Fakultet for marin teknologi og transport Institutt for strukturer, mekanikk og teknologi Skip i et operativt forskningslaboratorium. Pivottabeller brukes til å analysere data raskt. For å bruke pivottabeller må vi ha dataene ordentlig forberedt med overskrifter.

For å unngå slike situasjoner bør du formatere dataene våre som en tabell. For å formatere data som en tabell. Dette vil velge alle overskrifter. . Det neste valget er plasseringen av pivottabellen, og for å opprettholde lesbarheten til dataene bør du forlate alternativet Nytt ark, som vil sette inn nytt løv til bordet.

Microsoft Excel 2010. Nivå 2. Avanserte funksjoner

Så fordelene ved å legge til dine egne beregnede felt i en pivottabell er klare:

 Eliminer potensielle feil i formler og cellereferanser.

 Muligheten til å legge til og fjerne data fra en pivottabell uten å endre de opprinnelige beregningene.

Det er et nytt pivottabellark som er tomt. På høyre side er det en liste over felt, som inneholder alle overskriftene fra tabellen vår, og en plan med områdene. La oss si at vi vil se hvem som var bestselgeren. Velg i dette tilfellet Selger-feltet og Beløp-feltet, for etter salget vil vi sjekke hvem som var best. Rapporten vil se slik ut.

Standard er kolonne. Pivottabellene kan enkelt endres, og hvis du ønsker å sjekke det mest solgte landet, kansellere kjøpet og merke det med selgeren. Straks mottar vi en ny rapport der du kan lese resultatet.

 Muligheten til automatisk å beregne data på nytt når du endrer eller oppdaterer en pivottabell.

 Gir fleksibilitet til å endre beregninger når elementdefinisjoner endres.

 Evne til å effektivt administrere beregninger.

Lag et beregnet felt:

1. Velg en pivottabellcelle;

Vi kan også legge til en underkategori til selgeren ved å merke av i denne boksen. Salgsrapporten oppsummerer automatisk verdiene den viser, men disse summene gjør ofte analyser vanskelig. Vi kan også kvitte oss med totalen og endre rapportoppsettet.

For å raskt endre rapporten og se hvordan han solgte Dobrovalsky til andre land, endre rekkefølgen på forholdene i feltene ved å fange selgeren og dra ham gjennom staten. Bortsett fra Irland og Tyskland er det tydelig at salgsresultatene ikke er de beste. Når du klikker på selg i Irland, vil du se et nytt ark med en tabell som inneholder alle Dobrovalskys transaksjoner i Irland.

2. På fanen Alternativer, i Beregninger-gruppen, utvider du Felt-listen.

elementer og sett og velg Beregnet felt.

3. I vinduet Sette inn et beregnet felt:

 I feltet Navn skriver du inn navnet på det nye feltet.

 Opprett en formel i Formel-feltet, start med =-tegnet og velg

tilgjengelige felt i feltlisten (for å sette inn, kan du dobbeltklikke på et felt eller velge et felt og deretter klikke på Legg til felt).

Hvis vi ønsker å skrive ut detaljene våre, tar vi Selger-feltet fra Linjer-området og flytter det til Kolonne-området. Dette vil føre til at bordet spres over hele arket. I dette vinduet kan vi også spesifisere tallformatet som vises i alle cellene i feltet. Bare bruk "Tallformat"-knappen og velg riktig valuta, for eksempel.

Filtre er også nyttige. Når det legges til i dette området, vises leverandørfelt i øverste venstre hjørne av en nedtrekksliste der du kan velge ett eller flere alternativer. La oss se hvilken måned som var det beste salget. Nedenfor velger vi kategorien som vi ønsker å gruppere dataene etter.

 Klikk på Legg til.

Innstilling av utseendet til et beregnet felt, som alle andre felt, skjer i vinduet

Verdifeltparametere.

Redigere et beregnet felt

1. På fanen Alternativer, i Beregninger-gruppen, utvider du Felt-listen.

elementer og sett, velg Beregnet felt.

Vi vet alle hvordan man bruker pivottabeller. De har mange fordeler, men det er også mange ulemper. Mange tror at hovedproblemet med pivottabeller er at det ikke er noen måte å gjøre beregninger på - dette er en misforståelse. Hvorfor kan dette være nødvendig? La oss si at du oppretter et budsjett ved hjelp av pivottabeller. Dataene i den opprinnelige tabellen presenteres på kvartalsbasis, for budsjettformål vil det være nødvendig å legge disse dataene til et årlig beløp.

Vi feirer månedene og omfavner dem. Vi vil motta en rapport sortert etter måned. Du kan også dele dataene inn i flere kategorier. Går tilbake til grupperingen av konfiguratorer og velger "Kvartal og måned", får vi utseendet til rapporten. Gruppering kan også brukes for tall.

Når du åpner kontekstmenyen, Rader-feltet og alternativet Grupper viser et annet vindu enn hvis du grupperte datoer. Tallene er gruppert i områder, som vi definerer ved begynnelsen, slutten og volum av intervallet. I dette tilfellet setter vi den til 500, så den første gruppen vil inneholde alle transaksjoner verdt mellom 0 og 500 zloty.

Faktisk er det en slik mulighet, og den realiseres på en av tre måter.

Du kan friske opp kunnskapen din om det grunnleggende om pivottabeller.

Første vei. Gjør beregninger i en tabell med originaldata. Metoden, ærlig talt, er ikke en fontene – gjennom feil sted.

Andre vei.Å konstruere beregninger ved siden av en pivottabell er, igjen, ikke den beste metoden. Hvis pivottabellen endres (og det er verdt å bruke pivottabeller akkurat når vi forventer endringer i tabellen).

Tredje vei. Bygg inn beregnede felt i selve pivottabellen. Men dette er en god måte. Dette gjøres som følger. Når pivottabellen allerede er bygget, plasser markøren på pivottabellen (to faner relatert til denne pivottabellen vises på båndet) og klikk på fanen som vises Alternativer, deretter Enger,varer, &Settene fra Beregningsblokken, velg fra rullegardinmenyen Regnet utfelt. I Office 2007 var denne knappen på den samme fanen Alternativer, bare knappen var separat.

I vinduet som vises, i feltet Navn Skriv inn ønsket feltnavn. I felt Formel skriv inn formelen du trenger ved å sette inn felt fra listen nedenfor og klikke på knappen Sett innfelt.

Du får en tabell med et nytt beregningsfelt. Bordet er klart!

Excel-filen kan lastes ned herfra.

Interessant fakta. Når du bruker den tredje metoden, gjør programmet faktisk beregningen for hver rad og bruker deretter den nødvendige parameteren: summering, gjennomsnitt, etc.

Siste nytt

• Avansert Excel: Hvorfor jeg sluttet å bruke V-funksjonen...

  Jeg har allerede skrevet om det faktum at funksjonen VLOOKUP (VLOOKUP) sannsynligvis er den mest nyttige funksjonen etter enkle aritmetiske operasjoner...

• Hvordan Excel beregner en arbeidsbok på nytt og hvorfor volatilitet bør unngås...

  Hvis du ofte jobber med store filer som utfører et stort antall beregninger som avhenger av hverandre, o...

• Slik bygger du raskt et fossefalldiagram

  Excel har ikke standard Waterfall-diagrammer. Derfor, for å lage denne typen diagrammer, brukes vanligvis gis...

• Kontrollvindu for sporing av resultater

  Det hender ofte at vi jobber med store tabeller som til slutt beregner 1-2 indikatorer, men er avhengig av...

• Punktdiagram for å sammenligne planlagte indikatorer og faktiske...

  Ofte er det behov for å sammenligne de viktigste økonomiske og økonomiske indikatorene med de planlagte, for eksempel med det formål å...

• Tatsuo Horiuchi er en 73 år gammel kunstner som tegner i Excel.

  "Jeg har aldri brukt Excel på jobben, men jeg har sett andre lage noen ganske fine diagrammer og grafer i det ...

• Hvordan heve et tall til en potens og trekke ut roten

  Visste du at for å heve et tall til en potens i Excel er det spesiell karakter^ (på seks på engelsk...

Beregnede felt er designet for å vise verdiene til uttrykk basert på kildedata i rapporter. Beregnede felt i rapporter kan brukes til å innhente totaler eller utføre spesielle beregninger, for eksempel for å vise totalprisen på en ordre ved å beregne produktet av mengden av en vare og prisen på en vare. La oss se på fremgangsmåten for å lage beregnede felt ved å bruke eksemplet på rapporten "Vare i lager" som ble opprettet tidligere.

Slik legger du til et beregnet felt "Beløp" i en rapport:

1. Åpne rapporten Elementer i lager i designmodus.
2. Klikk på knappen i verktøykassen Inskripsjon(Etikett) og plasser den nye teksten til høyre for "Enheter på lager" i overskriften. Skriv inn ordet "Sum" som etiketttekst.
3. For å opprette et Sum beregnet felt, klikk på knappen Felt(Tekstboks) i verktøykassen og legg til en tekstboks til høyre for UnitsInStock-tekstboksen i datadelen. Fjern etiketten til det nye tekstfeltet.
4. Klikk inne i tekstfeltet og skriv inn følgende uttrykk som verdi:

   = [På lager]* [Pris] (= * ).

   Åpne egenskapsvinduet feltene ved å dobbeltklikke på det, og på fanen Oppsett(Format) i egenskapskombinasjonsboksen Feltformat(Format) velg en verdi Monetære(Valuta).

Kommentar

Det er flere måter å legge inn et uttrykk i tekstfeltet: Velg det og trykk på tastekombinasjonen +for å gå inn i redigeringsmodus for feltets innhold, eller dobbeltklikk på feltet for å vise en dialogboks Egenskaper(Egenskaper) og skriv deretter inn uttrykket som egenskapsverdien Data(Kilde).

Ris. 6,27.

1. Gjenta trinn 3 og 4 for å lage et tekstfelt i kommentarfeltet i kategorigruppen, men skriv inn følgende som subtotaluttrykk: =Sum([InStock] * [Price]) (=Sum( * )). Klikk på knappen Dristig(Fet) i formatlinjen for å sette teksten i det nye feltet til fet skrift. Åpne dialogboksen Egenskaper(Egenskaper) og åpne fanen i den Annen(Annet), i felten Navn(Navn) Skriv inn et navn for tekstboksen Sum. Utvid deretter fanen Oppsett(Format) og i kombinasjonsboksen egenskaper Feltformat(Format) velg en verdi Monetære(Valuta).
2. Siden bredden på rapportens dataområde har økt, må du redusere bredden på sidemargene slik at alle feltene får plass på én side. For å gjøre dette, velg kommandoen Fil, Sideoppsett(Fil, Sideoppsett) og endre standard venstre og høyre margbredde til 10 mm (Se "Vise og skrive ut rapporter" i dette kapittelet.)
3. Klikk på knappen Utsikt(Vis) på verktøylinjen Rapportdesigner(Rapportdesign) for å evaluere resultatene av arbeidet ditt. Rapporten vil se ut som vist i fig. 6,27. Til de forrige dataene ble kolonnen lengst til høyre lagt til med verdiene av kostnadene for hvert produkt i tilgjengelig mengde på lageret og delsummer for varekostnadene for hver kategori.