Kontakta författare

Använda Excel för att hitta relativ distribution

I studien av affärsstatistik är en av de första saker du lär dig skillnaden mellan kvalitativ och kvantitativ data. Skillnaden mellan de två är en av etiketter och siffror. Kvalitativa data är data som består av namn eller kategorier av liknande objekt. Kvantitativa data å andra sidan är data som består av antalet objekt i en lista med data etc.

För det andra är det nästa du kanske läser något som kallas frekvensfördelning. Frekvensfördelning är hur många gånger ett objekt dyker upp i en lista med data. Objektet i sig skulle betraktas som kvalitativa data, men dess frekvens inom en lista skulle vara antalet gånger den artikeln dyker upp som anses vara kvantitativ.

Med det ovan nämnda kommer jag att visa dig hur du kan ta en lista med kvalitativa data importerade till Excel och ta reda på hur många gånger varje objekt dyker upp i listan med hjälp av Excel's COUNTIF-funktion. För detta projekt använder jag EXCEL 2010; detta bör dock fungera för alla senare versioner av EXCEL.

Först några fler definitioner

Innan jag visar hur man använder COUNTIF-funktionen kommer jag att utöka två nya definitioner som härrör från ämnet frekvensfördelning.

Först är det vad föremålet för affärsstatistik kallar relativ frekvensfördelning . Relativt betyder att för varje objekt eller kategori i en lista med data finns det antalet gånger som det objektet visas i listan.

För det andra finns det något som kallas procent frekvensfördelning som är ungefär detsamma som relativ frekvens men ges i en procent. I princip är den procentuella fördelningen det relativa antalet artiklar multiplicerat med 100.

För att visa hur man använder ovanstående definitioner i Excel kommer jag att låtsas att jag arbetar på en Volkswagen bilhandlare och jag får en lista över olika modeller som såldes under de senaste månaderna. Målet är att skapa en frekvensfördelningsanalys från Excel som gör att jag kan ta reda på vad bilar säljer mest. I sin tur kommer jag att kunna använda den här informationen för att beställa fler bilar att sälja från tillverkningen inom en snar framtid.

Hur man använder Excel's COUNTIF-funktion

Först och främst, jag vet att jag arbetar med det här företaget och vi säljer bara fem olika modeller av bilar. I det här fallet säljer vi Volkswagens Golf Mk6, Jetta, EOS, Passat B6 och Passat B7. Vi har också vår lista över alla bilar som sålts under de senaste månaderna importerade till Excel. Så allt vi behöver göra nu är att skapa våra fem kategorier, en för varje modell, och använda COUNTIF-funktionen för att få en frekvens av gånger varje modell dyker upp i listan. Med det sagt, här är våra Excel-kalkylblad som anger COUNTIF-funktionen för vår första modell (EOS):

Figur 1

För att få den relativa fördelningen av varje objekt i en lista använder du Excel-funktionen COUNTIF ().

Som ni ser av bilden ovan kommer vi att använda COUNTIF-funktionen för att ta reda på hur många gånger vi sålde en EOS-modell. Inom COUNTIF-funktionen märkte du två argument. Det första argumentet är de faktiska listorna över objekt och det andra argumentet berättar för funktionen att räkna alla objekt med samma namn på det som skrivs in i cell C2. Genom att göra detta går funktionen igenom hela listan och räknar bara hur många gånger den ser ordet EOS.

Med det sagt kommer vi också att behöva infoga ytterligare fyra funktioner för de andra modellerna. Observera att om du kopierar och klistrar in formeln ovan; du kan komma med fel resultat. För att lindra detta problem kan du också skriva den första formeln enligt följande:

COUNTIF ($ A $ 2: $ A $ 40, C2)

Genom att skriva formeln som ovan, kan du kopiera och klistra in formeln för de återstående kategorierna utan att orsaka ett logiskt fel i dina beräkningar.

När du har skrivit in COUNTIF-formeln för varje modell måste du också få den totala mängden bilar som finns på din lista. För att få detta totalt beslutade jag att använda Sum-funktionen. Här är vårt Excel-kalkylblad hittills.

figur 2

När du har fått det totala antalet för varje modell, använd sedan funktionen SUM () för de totala artiklarna i listan.

Nu när vi har frekvensen för varje objekt i listan måste vi hitta dess relativa frekvensfördelning och senare procentens frekvensfördelning. För att få varje objekt relativ fördelning, allt vi behöver göra är att ange för varje objekt formeln Item / Total . När det gäller EOS-modellen skulle denna formel vara D2 / $ D $ 8:

Figur 3

Dela det relativa frekvensnumret med de totala artiklarna i listan för att få den relativa frekvensfördelningen.

Figur 4

Tryck på knappen i fliknumret för att få numret ner till två decimaler.

Återigen använde jag $ för cellen D8 så jag kan kopiera och klistra in formeln för de andra modellerna. Om du inte gör det kommer Excel att öka antalet varje gång du klistrar in formeln i en annan cell.

När du har använt den här formeln kanske du också märker att du får ett decimaltal som går ut till åtta eller nio platser. Önskan är att bara gå ut totalt två decimaler. Så för att fixa detta justerar jag bara numret i Excel: s huvudverktygsfält på fliken Number.

Jag använde också SUM-funktionen igen för att få en summa för kolumnen. Anledningen till att göra detta är att se om summan blir 1. Om den inte gör det, finns det ett fel någonstans i dina data.

Så när du har kopierat och klistrat in formeln till de andra modellerna måste du nu ange formeln för procentuell frekvens. För att få till det, allt du behöver göra är att multiplicera resultaten från kolumnen för relativ frekvens med 100, och jag använde SUM-funktionen en gång till för att se till att den totala procenten verkligen är 100%:

Figur 5

För att få procentfrekvensen multiplicerar du bara den relativa frekvensen med 100.

När allt är gjort skulle följande kalkylblad bli resultatet:

Figur 6

Resultat efter analysen har gjorts.

Som du kan se, genom att titta på kalkylbladet, är EOS den mest sålda bilen medan Golf Mk6, Jetta och Passat B6 är en nära sekund. Passat B7 verkar dock inte sälja så mycket. Hur som helst, denna analys ger dig en bra uppfattning om hur många av varje modell du ska köpa från tillverkaren för framtida sälj.

Till sist vill jag göra en sak till vår analys och det är att skapa ett stapeldiagram. För att göra det behöver du bara markera kategorierna Model och Frekvens och klicka på infoga-fliken för att skapa stapeldiagrammet:

Figur 7

Om du vill skapa ett stapeldiagram från dina data markerar du modellerna och kolumnen för relativ frekvens och klickar sedan på Infoga-fliken och sedan diagram.

Då får du de slutliga resultaten:

Figur 8

Sista arbetsblad med stapeldiagram.

Slutsats

Nu räcker det. Nu vet du hur du tar en lista med data i Excel och får ett antal av varje objekt i listan med COUNTIF- funktionen. Vi markerade också användningen av SUM-funktionen för att få det totala antalet som i sin tur gav oss den relativa och procentuella fördelningen av varje objekt i listan.

Detta fungerar bra om du bara måste spåra några få artiklar och du vill veta vilka objekt som säljer bra och vilka inte. Excel är ett utmärkt verktyg för statistisk analys och har många funktioner som hjälper dig att fatta mer sunda beslut.

Fantastiska priser på Amazon

The Learning Company - Oregon Trail 5th Edition The Learning Company - Oregon Trail 5th Edition Köp nu

Microsoft excel

Använder du Excel mycket på arbetsplatsen?

  • Ja
  • Nej
Se resultat