I denne artikkelen vil du lære om 5 nyttige egenskaper ved pivottabeller som vil hjelpe deg raskt og i detalj med å analysere situasjonen (ved hjelp av eksemplet på å analysere kundebasen). Du vil lære:

1. Hvordan gruppere data;
2. Hvilke indikatorer kan beregnes når data kombineres;
3. Hvordan beregne samtidig flere indikatorer for en parameter når man kombinerer data;
4. Hvilke ekstra beregningsalternativer kan du bruke når du kombinerer data?
5. Om muligheten for sortering.

Og basert på denne analysen vil vi berøre en kraftig teknikk for å planlegge salgsfremmende aktiviteter i FMCG-markedene.

La oss først lage en pivottabell. La oss ta en enkel oversikt over salg til kunder om dagen.

Plasser markøren i øvre venstre hjørne av bordet vårt, gå deretter til "Sett inn" -menyen og klikk på "Pivottabell" -knappen:

Klikk OK i dialogboksen Opprett pivottabell:

Fikk et pivottabell på et nytt ark:

Første nyttige egenskapen til en pivottabell for forretningsanalyse - datagruppering

Så vi har forsendelser til kunder om dagen, vi vil forstå i hvilket utvalg av forsendelser vi har maksimalt salg. For å gjøre dette må vi gruppere forsendelser i områder.

Dra feltet "Forsendelsesbeløp" til pivottabellområdet "Radnavn" (hold nede feltet "Forsendelsesbeløp" med venstre museknapp og dra det til pivottabellen "Radnavn"):

Vi har vist alle forsendelsene i venstre kolonne i sammendraget. Nå setter vi markøren for forsendelsene våre (som på bildet):

Gå til Excel-menyen "Data" og klikk på "Group" -knappen

I dialogboksen som vises, angir du grupperingstrinnet "5000" (du kan angi hvilket som helst) og klikker "OK"

Vi får de grupperte salgsvolumene med et gitt trinn:

For å gruppen så vakker ut og oppfattet, trykker vi igjen "Gruppe" -knapp og sett like verdier med hendene, for verdien "begynner med" - "-15.000" (under minimumsverdien, multiplum av 5000) "til" - "45.000" (mer enn den maksimale gruppen, multiplum av 5000).

Vi får de grupperte dataene etter forsendelsesbeløpet:

Andre nyttige egenskap for pivottabeller for forretningsanalyse -
muligheten til å beregne forskjellige oppsummeringsparametere etter felt fra kildetabellen

Så forsendelsene er gruppert, la oss nå se hvilke salgsvolumer som faller på hvert forsendelsesområde. For å gjøre dette, la oss oppsummere forsendelsene i sammendraget.

Venstreklikk på feltet "Forsendelsesbeløp" og dra det til sammendragsfeltet "Verdier":

Pivot som standard beregnet "Mengde etter felt Forsendelse_total", dvs. antall poster i vår opprinnelige tabell på databladet. Fordi tabellen vår inneholder informasjon om salg til kunder etter dag, så er indikatoren "Mengde i feltet Forsendelse_total" antall forsendelser til kunder.

Som et resultat ser vi i pivottabellen antall forsendelser til kunder i forskjellige fraktområder:

Hvordan kan vi få mengden forsendelser fra antall forsendelser?

Venstreklikk på feltet "Mengde etter felt Fraktbeløp" i pivottabellområdet "Verdier", og velg "Parametere for verdifelter ..." i rullegardinmenyen.

I vinduet som åpnes velger du datareduksjonsoperasjonen som er av interesse for oss (Sum, mengde, gjennomsnitt, maksimum, minimum ...). Velg "mengde" -operasjonen vi trenger, og klikk "OK".

Vi får totalt salg for hvert forsendelsesområde:

De. vi ser hvilket salgsvolum som faller på forsendelser i området fra 0 til 5000 rubler, fra 5000 til 10 000 rubler. etc. Og det kan sees at det maksimale volumet av forsendelser faller i området fra null til 5000 rubler.

3-egenskap - muligheten for ett felt til å beregne forskjellige dataflakeringsoperasjoner

Nå vil vi se hvor mange forsendelser og hvilke gjennomsnittlige forsendelser vi har for hvert av områdene. For å gjøre dette vil vi bruke en pivottabell for å beregne antall forsendelser og gjennomsnittlige forsendelser.

I området av pivottabellen "Verdier", drar du feltet "Forsendelse_total" to ganger til og i parameterne for verdifeltet for det andre velger du "antall" og for det tredje feltet velger du "gjennomsnitt".

Vi får for hvert utvalg av forsendelser salgsvolum, antall forsendelser og gjennomsnittlig forsendelse:

Nå kan vi se i hvilket utvalg av forsendelser maksimalt salg og maksimalt antall forsendelser. I vårt eksempel er dette i området fra 0 til 5000 rubler. og salgsvolumet og antall forsendelser er maksimalt.

Fjerde egenskap av pivottabeller - muligheten til å utføre flere beregninger

For å få en klarere dataanalyse vil vi legge til 2 parametere til - "Dele etter salgsvolum for hver gruppe" og "Andel av antall forsendelser for hver gruppe".

For å gjøre dette, i feltet av pivottabellen "Verdier", drar du feltet "Forsendelsesbeløp" to ganger til

Videre, for en parameter i "Verdifeltparametere" -menyen (), vil vi velge "mengde" -operasjon, og for den andre "mengde" -operasjonen.

Vi får en tabell med følgende skjema:

Gå nå til "Verdifeltparametere" igjen og skriv inn fanen "Ytterligere beregninger":

Velg i feltet "Tilleggsberegninger" elementet "Andel av totalbeløpet"

Vi får en tabell der vi for hvert utvalg av forsendelser til kunder ser salgsvolum, antall forsendelser, gjennomsnittlig forsendelse, andel av salg for hver gruppe og andel av antall forsendelser for hver gruppe:

5 nyttige egenskaper - sortering

Nå, for klarhetens skyld, fra maksimum til minimumsgruppe etter salgsvolum, vil vi sortere det. For å gjøre dette, plasser markøren i feltet med salgsvolumet etter grupper og klikk på "sorter fra maksimum til minimum" -knappen:

Det kan sees at den maksimale gruppen når det gjelder salgsvolum og antall forsendelser er gruppen "fra 0 til 5000 rubler." gjennomsnittlig salg i denne gruppen er 1971 rubler.

Merk! Gjennomsnittlige forsendelser på tvers av alle kunder er vesentlig forskjellige fra 86% av forsendelsene. Og det skiller seg betydelig

• for alle grupper er den gjennomsnittlige forsendelsen 2 803 rubler. (i linjen totalsummen).
• Og for 86% av forsendelsene 1.971 rubler.

Dette er en alvorlig forskjell, og hvis vi stimulerer salg basert på 86% av forsendelsene og gjennomsnittet for dem - 1.971 rubler, vil våre handlinger være mer nøyaktige, og effekten er mye høyere, fordi vi vil kunne interessere maksimalt antall kunder.

Dette eksemplet viser den kraftigste teknikken for markedsføring og masseprognoser som kan hjelpe deg med å gjøre en forskjell og gjøre en forskjell.

Hvis du har spørsmål, kan du kontakte.

Nøyaktige spådommer for deg!

Bli med oss!

Last ned gratis prognoser og forretningsanalyseapps:

• Novo Prognose Lite - Automatisk prognoseberegning på utmerke.
• 4analytics - ABC-XYZ analyse og analyse av utslipp i Utmerke.
• Qlik Sense Desktop og QlikViewPersonal Edition - BI-systemer for dataanalyse og visualisering.

Test evnen til betalte løsninger:

• Novo Prognose PRO - prognoser i Excel for store datasett.

SE OPP etter to eller flere kriterier

Sikkert alle som er kjent med VLOOKUP-funksjonen vet at den leter etter spesifiserte verdier utelukkende i venstre kolonne i den angitte tabellen (for mer informasjon om VLOOKUP, se artikkelen: Hvordan finne en verdi i en annen tabell eller VLOOKUP-kraften). Mange vet også at VLOOKUP bare søker etter én verdi.

Hjalp artikkelen? Del lenken med vennene dine! Videoleksjoner

("Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; bakgrunnsfarge: # 333333; opasitet: 0,6; filter: a lpha (opacity \u003d 60); "," titlecss ":" display: block; posisjon: relativ; font: fet 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; posisjon: relativ; font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; farge: #fff; margin-top: 8px; "," buttoncss ":" display: block; posisjon: relativ; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Dette er et kapittel fra boka: Michael Girwin. Ctrl + Skift + Enter. Mestring av matriseformler i Excel.

Prøver basert på en eller flere forhold.En rekke Excel-funksjoner bruker sammenligningsoperatorer. For eksempel SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF og AVERAGEIFS. Disse funksjonene gjør valg basert på en eller flere forhold (kriterier). Problemet er at disse funksjonene bare kan legge til, telle og gjennomsnitt. Hva om du vil stille vilkår for søket ditt, for eksempel maksimal verdi eller standardavvik? I disse tilfellene, siden det ikke er noen innebygd funksjon, må du finne på en matriseformel. Dette er ofte på grunn av bruken av array-sammenligningsoperatøren. Det første eksemplet i dette kapittelet viser hvordan man beregner minimumsverdien under en betingelse.

La oss bruke IF-funksjonen til å velge matriseelementer som oppfyller en betingelse. I fig. 4.1 i venstre tabell er det en kolonne med bynavn og en kolonne med tid. Det kreves å finne minimumstiden for hver by og plassere denne verdien i den tilsvarende cellen i høyre tabell. Betingelsen for utvalget er navnet på byen. Hvis du bruker MIN-funksjonen, kan du finne minimumsverdien for kolonne B. Men hvordan velger du bare tallene som er spesifikke for Oakland? Og hvordan kopierer du formlene nedover i kolonnen? Siden Excel ikke har en innebygd MINESLI-funksjon, må du skrive en originalformel som kombinerer IF- og MIN-funksjonene.

Figur: 4.1. Formålet med formelen: velg minimum tid for hver by

Last ned et notat i format eller format

Som vist i fig. 4.2, bør du begynne å skrive inn formelen i celle E3 med MIN-funksjonen. Men du kan ikke argumentere nummer1 alle verdier i kolonne B!? Du vil bare velge de verdiene som er spesifikke for Auckland.

Som vist i fig. 4.3, i neste trinn, skriv inn IF-funksjonen som et argument nummer1 i MIN. Du setter IF i MIN.

Ved å plassere markøren der argumentet er lagt inn logguttrykk funksjon IF (fig. 4.4), velger du et område med bynavn A3: A8, og trykker deretter på F4 for å gjøre cellehenvisninger absolutt (se for eksempel for flere detaljer). Deretter skriver du inn sammenligningsoperatøren, likhetstegnet. Til slutt velger du cellen til venstre for formelen - D3, og holder referansen til den relativt. Den formulerte tilstanden lar deg bare velge Aucklands når du ser på området A3: A8.

Figur: 4.4. Opprett matriseoperatør på argument logguttrykk funksjoner HVIS

Så du har opprettet en matriseoperatør ved hjelp av sammenligningsoperatøren. Når som helst under behandlingen av en matrise er arrayoperatøren en sammenligningsoperator, slik at resultatet av operasjonen blir en matrise med SANNE og FALSE verdier. For å bekrefte dette, velg matrisen (for å gjøre dette, klikk i verktøytipsen på argumentet logguttrykk) og trykk på F9 (fig. 4.5). Du bruker vanligvis ett argument logguttrykk, returnerer enten SANT eller FALSK; her vil den resulterende matrisen returnere flere SANNE og FALSE verdier, så MIN-funksjonen vil bare velge minimumstallet for byer som samsvarer med SANN-verdien.

Figur: 4.5. For å se en rekke sanne og falske verdier, klikk på argumentet i verktøytipsen logguttrykk og trykk på F9

Tema: Beslutningstaking på flere kriterieindikatorer.

I praksis er det vanligvis nødvendig å velge en ledelsesbeslutning ikke etter ett kriterium, men av flere. Derfor er deres verdier i sammenlignende vurdering av multidireksjonell karakter, dvs. alternativet vinner i ett mål og taper i det andre.

Under disse forholdene er det nødvendig å redusere det vurderte systemet for vurdering av indikatorer til ett kompleks, på grunnlag av hvilket en beslutning vil bli tatt.

For å bygge en omfattende vurdering er det nødvendig å løse to problemer:

Det første problemet er at kriterieindikatorene som vurderes har ulik betydning;

Det andre problemet er preget av det faktum at indikatorer evalueres i forskjellige måleenheter, og for å bygge en omfattende vurdering er det nødvendig å bytte til et enkelt mål.

Det første problemet løses ved å bruke en av de fire modifikasjonene av ekspertvurderingsmetoden, nemlig metoden for paret sammenligning, som gjør det mulig å kvantifisere betydningen. Essensen av den sammenkoblede sammenligningsmetoden er at en ekspert (spesialist, potensiell investor, forbruker) foretar sammenkoblet vurdering av kriterieindikatorene som vurderes, og definerer selv graden av betydning i form av en poengscore. Etter å ha utført riktig behandling av den mottatte informasjonen, beregnes betydningskoeffisienten for hver av kriterieindikatorene som blir vurdert.

Det andre problemet løses ved å bruke et enkelt mål for private indikatorer. Oftest brukes poengpoeng som et slikt mål. I dette tilfellet utføres vurderingen på to måter:

- første tilnærming brukt i fravær av statistiske data om verdien av indikatorene som vurderes,

- andre tilnærming den brukes i nærvær av statistiske data (endringsgrenser) om verdien av de vurderte indikatorene.

Når du bruker den første tilnærmingen, gjøres omregningen til poeng som følger: den beste verdien av indikatoren som er vurdert er tatt lik 1 poeng, og de verste verdiene i brøkdeler av dette punktet. Denne tilnærmingen er enkel, gir en objektiv vurdering, men tar samtidig ikke hensyn til de beste prestasjonene som ligger utenfor de vurderte alternativene.

For å eliminere denne ulempen, er det behov for informasjon om grensene for endring av indikatoren som vurderes. Hvis tilgjengelig, brukes den andre tilnærmingen. I dette tilfellet er en konverteringsskala bygget for å konvertere til poeng. I dette tilfellet blir poengsummen valgt med bestemmelsene i statistikkteorien i henhold til Sturges-formelen:

n = 1 + 3,322 lGN, Hvor

N er antall statistiske observasjoner;

n er det aksepterte poengscoresystemet oppnådd ved hjelp av avrundingsregler.

Konvertering til poeng utføres på grunnlag av den konstruerte konverteringsskalaen ved bruk av prosedyren for interpolering av tabelldata.

Oppgaven:

Av de 6 alternativene for alternative løsninger, som hver vurderes av 5 kriterieindikatorer, er det nødvendig å velge det beste alternativet.

Utfør vurderingen ved hjelp av to tilnærminger:

i mangel av statistiske data om verdien av indikatorene som vurderes;

hvis tilgjengelig.

Grensene for endring av indikatorer er etablert for følgende antall observasjoner (N):

for jevne alternativer N \u003d 8;

Vurderingen av betydning bør utføres på grunnlag av en sammenkoblet vurdering etter utøverens mening.

Tabell 1.

Jobbalternativer

Tabell 2.

Innledende data:

Fra det må du finne ut hvor mye blyanter selges av salgsrepresentant Ivanov på januar.

PROBLEM: Hvordan oppsummere data etter flere kriterier ??

BESLUTNING: Metode 1:

BDSUMM (A1: G16; F1; I1: K2)

DSUM (A1: G16, F1, I1: K2)

Fra databasen vi spesifiserte A1: G16 funksjon BDSUM henter og oppsummerer kolonnedata Nummer (argument " Felt" = F1i henhold til gitt i celler I1: K2 (Selger \u003d Ivanov; Produkter \u003d blyanter; Måned \u003d januar) kriterier.

Ulemper: Liste over kriterier skal være på arket.

MERKNADER: Antall summasjonskriterier er begrenset av RAM.

SØKNADSOMRÅDE: En hvilken som helst versjon av Excel

Metode 2:

SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)

SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)

HVORDAN DET FUNGERER:

SUMPRODUCT-funksjonen danner matriser fra SANNE og FALSE verdier, i henhold til de valgte kriteriene, i Excel-minne.

Hvis beregningene ble utført i cellene på arket (for klarhetens skyld, vil jeg demonstrere hele formelarbeidet som om beregningene foregår på arket, og ikke i minnet), vil matriser se slik ut:

Det er åpenbart at hvis for eksempel D2 \u003d blyanter, vil verdien være SANT, og hvis D3 \u003d Mapper, deretter FALSE (siden kriteriet for å velge et produkt i vårt eksempel er verdien Blyantene).

Når vi vet at den SANNE verdien alltid er 1, og FALSK alltid er lik 0, fortsetter vi å jobbe med matriser som med tall 0 og 1.
Ved å multiplisere de oppnådde verdiene til matriser sekvensielt, får vi EN matrise med nuller og ener. Der alle tre utvalgskriteriene ble oppfylt, ( IVANOV, PENSLER, JANUAR) dvs. alle forhold tok på seg verdiene SANT, vi får 1 (1 * 1 * 1 \u003d 1), hvis minst en betingelse ikke er oppfylt, får vi 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).

Nå gjenstår det bare å multiplisere den resulterende matrisen med en matrise som inneholder data som vi trenger å oppsummere som et resultat (rekkevidde F2: F16) og faktisk oppsummere det som ikke multipliseres med 0.

Sammenlign nå matriser oppnådd ved hjelp av formelen og under trinnvis beregning på arket (uthevet med rødt).

Jeg tror alt er klart :)

MINUSER: SUMPRODUCT - "tung" matriseformel. Ved beregning av store dataområder øker omberegningstiden merkbart.

MERKNADER

SØKNADSOMRÅDE: En hvilken som helst versjon av Excel

Metode 3: Array Formula

SUM (HVIS ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))

SUM (HVIS ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))

HVORDAN DET FUNGERER: På samme måte som metode 2. Det er bare to forskjeller - denne formelen angis ved å trykke Ctrl + Skift + Enteri stedet for bare å trykke Tast inn og 0-th og 1-q matriseringen multipliseres ikke med summeringsområdet, men velges ved hjelp av IF-funksjonen.

MINUSER: Arrayformler når du beregner store dataområder øker omberegningstiden merkbart.

MERKNADER: Antallet behandlede matriser er begrenset til 255.

SØKNADSOMRÅDE: En hvilken som helst versjon av Excel

Metode 4:

SUMIF (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)