Excel raportointi

1. Excel raportointi Webropol

2. Hyödyllisiä funktioita Sum (Range) Vakiofunktio. Laskee tulosjoukon summan. Ei huomioi tietojen suodattamista! SumNH(Range) Webropol-funktio. Kuten Sum(), mutta huomioi tietojen suodattamisen. NH=[row] Not Hidden

3. Hyödyllisiä funktioita Count (Range) Vakiofunktio. Laskee tulosjoukon alkioiden lukumäärän. Ei huomioi tietojen suodattamista! CountNH(Range) Webropol-funktio. Kuten Count(), mutta huomioi tietojen suodattamisen. NH=[row] Not Hidden

4. Hyödyllisiä funktioita Average (Range) Vakiofunktio. Laskee tulosjoukon alkioiden keskiarvon. Ei huomioi tietojen suodattamista! AverageNH(Range) Webropol-funktio. Kuten Average(), mutta huomioi tietojen suodattamisen. NH=[row] Not Hidden

5. Hyödyllisiä funktioita CountIf (Range, Criteria) Vakiofunktio. Laskee tulosjoukon niiden alkioiden lukumäärän, jotka täyttävät annetun ehdon. Ei huomioi tietojen suodattamista! CountIfNH(Range, Criteria) Webropol-funktio. Kuten CountIf(), mutta huomioi tietojen suodattamisen. NH=[row] Not Hidden

6. Hyödyllisiä funktioita AverageIfNH(Range, CriteriaRange, Criteria) Webropol-funktio. Laskee tulosjoukon niiden alkioiden keskiarvon, jotka täyttävät annetun ehdon. Ehtojoukko voi olla eri sarakkeessa kuin tulosjoukko. Huomioi tietojen suodattamisen. Esim. Lasketaan C-sarakkeen keskiarvo niiltä riveiltä, joiden arvo B-sarakkeessa on male. AverageIfNH(C1:C100;B1:B100;”male”)

7. Hyödyllisiä funktioita AverageFourNH(Range, Axis) Webropol-funktio. Laskee nelikenttä -tyyppisten vastausten keskiarvon ja palauttaa sen muodossa x tai y. Huomioi tietojen suodattamisen. Esim. Lasketaan C-sarakkeessa olevien nelikenttävastausten keskiarvo AverageFourNH(C1:C100;”x”) Palauttaa esim. 200 (max 500) 2/5 vasemmasta reunasta

8. Webropol-funktioiden käyttöönotto Funktiot sijaitsevat WebropolFunctions.xla lisämakrojen tiedostossa. Tiedosto tulee olla Excelin ulottuvilla. Tiedostossa olevat funktiot otetaan käyttöön tekemällä niihin viittaus Excelissä. Tools – Add_ins... – Browse – [choose] – Ok (Excel 2007) Office Button – Excel Options – Add_ins – Go – Browse – [choose] – Ok

9. Webropol-funktioiden käyttöönotto Viittauksen jälkeen funktiot löytyvät User Defined ryhmän alta ja niitä voi käyttää aivan samoin kuin Excelin valmiita funktioita. Viittaus tarvitsee tehdä vain kerran. Jos haluat lähettää kyselysi tuloksia muille, muista että funktiot toimivat vain niissä Exceleissä, jotka ovat linkattu Webropol-functions.xla tiedostoon!

10. Soluviittaustyypin vaihto Webropolin tiedostot käyttävät oletusarvoisesti R1C1 –tyyppistä solujen viittaustapaa, mutta moni haluaa kuitenkin käyttää A1 –tyypin viittausta. Vaihda soluviittaustapa seuraavasti: Tools – Options – General(välilehti) poista R1C1 Reference Style

11. Kopiointilukitukset Kertauksena kopiointilukitukset (F4) Suhteellinen viittaus; =A1 Kopioitaessa suhteellinen Sekaviittaus; =$A1 Kopioitaessa sarake absoluuttinen, rivi suhteellinen Sekaviittaus; =A$1 Kopioitaessa sarake suhteellinen, rivi absoluuttinen Absoluuttinen viittaus; =$A$1 Kopioitaessa absoluuttinen

12. Harjoitus I Ota Webropol-funktiot käyttöön. Tiedosto sijaitsee osoitteessa .../WebropolFunctions.xla Opettaja kertoo sijainnin

13. Harjoitus I Tools – Add_Ins.. Excel 2007 Office Button – Excel Options – Add_ins – Go

14. Harjoitus I Browse - Ok

15. Funktioharjoituksia Avaa tiedosto WebExcel1.xls Sarakkeessa B arvioidaan palveluja asteikolla 1-5 Piirretään tuloksista palkkikaavio

16. Funktioharjoituksia Tehdään otsikkorivin(2) ja 1.datarivin(3) väliin 10 tyhjää riviä. Näin saamme laskennalle paikan, johon tietojen suodattaminen ei vaikuta. Valikoi rivi 3 – hiiren oikea – Insert Ctrl+Y (toista*9) Kirjoita alueelle A3:A7 arvot 1-5

17. CountIf() Lasketaan soluun B3 tulosjoukon arvoltaan 1 olevien lukujen lukumäärä: =COUNTIF(B14:B136;A3) Alueen saa helpoiten valittua valitsemalla ensin alueen 1.solun ja sitten painamalla Shift+Ctrl+ArrDown Tulos on 29, eli 29 ihmistä on antanut palvelulle arvosanan 1. Entä muiden arvosanojen frekvenssit?

18. CountIf() Kopioidaan kaava arvosanaan 5 asti Ennen kopiointia haluamme varmistua siitä, että viittaus tulosjoukkoon pysyy ennallaan Kopioinnin suunta on tällä kertaa alaspäin, joten meillä on tarve lukita vain rivit =COUNTIF(B$14:B$136;A3)

19. CountIf() Kopiointi onnistuu helpoiten Drag’n Drop menetelmällä Valitse kopioita solu ja mene solun oikeaan alareunaan. Kun kursori muuttuu mustaksi plussaksi, paina hiiri pohjaan ja maalaa kohdealue. Laske hiirestä irti ja kopiointi on valmis.

20. CountIf() Nyt tiedämme jokaisen arvosanan lukumäärän Tehdään palkkikaavio datan perusteella Maalaa data-alue A2:B7 Paina Chart Wizard nappia Valikoi kaaviotyyppi, nyt Bar – Next

21. CountIf() Series välilehdeltä poista ”-” sarja, Remove Next Legend välilehdeltä poista Show legend Next Tallennetaa kaavio uudelle sivulle ja nimetään sivu ”Frekvenssit”

22. CountIf() Lisätään kaavioon vielä sarake C –Toimitusajat Nyt haluamme kopioida kaavaa myös vaakasuunnassa, joten lukitaan ehdon sarake =COUNTIF(B$14:B$136;$A3) Kopioidaan ensi alas ja sitten oikealle

23. CountIf() Päivitetään kaavio Klikkaa hiiren oikealla kaaviota – Source Data - Ok Valitse alue B2:C7 Saimme toiset vaakapylväät, mutta kumpi on kumpi? Klikkaa hiiren oikealla kaavioa – Chart Options Data labels välilehdeltä – Series name – Ok Siirretään sarjojen nimet vielä pylväiden sisään Klikkaa sarjan nimeä (esim.Toimitusajat) Aligment välilehdeltä Label Position asetus Inside End Toista kaikille sarjoille

24. CountIf()

25. CountIf() Harjoitus Lisätään kaavioon vielä arvio kilpailukykyisyydestä, eli sarake I Ctrl mahdollistaa eri alueiden samanaikaisen valinnan Päivitä kaavion lähdedata Päivitä sarjojen nimet pylväiden sisälle

26. Tietojen suodatus Tietoja voidaan Excelissä suodattaa mm. automaattisen filterin läpi Filterin käyttö on helppoa Valitse dataa edeltävä tyhjä rivi (13) Data – Filter – AutoFilter Jokaisen sarakkeen yläpuolelle ilmestyy alasvetovalikko, josta löytyy sarakkeen kaikkien yksilöllisten solujen arvot

27. Tietojen suodatus Suodata taulukko siten, että siinä näkyy vain ne asiakkaat, jotka ovat olleet asiakkaina alle 1 vuoden. Suodatus piilottaa ne rivit, jotka eivät kuulu ehdolliseen tulosjoukkoon Piilotus tapahtuu käytännössä siten, että rivin korkeudeksi asetetaan nolla

28. Tietojen suodatus Kokeile mitä kaaviolle tapahtuu suodattamalla dataa COUNTIF() funktio ei kykene erottelemaan tulosjoukossa olevaa dataa sen perusteella, että onko kyseinen rivin korkeus nolla vaiko ei Tämän takia on olemassa COUNTIFNH()-Webropol funktio NH = [row] Not Hidden

29. CountIfNH() Muutetaan työkirjan kaikki COUNTIF() funktiot COUNTIFNH() funktioiksi Helpoiten tämä tapahtuu Ctrl+H näppäinyhdistelmällä – Replace All

30. CountIfNH() Kokeile mitä kaaviolle nyt tapahtuu suodattamalla dataa Kaavio elää dynaamisesti suodatusten ehtojen mukaisesti Ole varovainen, hallitsemattomien suodatusten riski on olemassa Luulet käsitteleväsi koko dataa, vaikka joitakin suodatuksia on päällä!

31. CountIfNH() Tehdään kaavio, joka kertoo kuinka tyytyväisiä vanhat (yli 5 v.) asiakkaamme ovat tuotteisiimme Käytä tällä kertaa piirakkatyyppistä kaaviota Kopioi COUNTIFNH() funktio sarakkeeseen D Luo kaavio omalle sivulleen ”Tuotteet” Muokkaa kaavion asetuksia, jotta saat seuraavalla sivulla olevan esimerkin luotua

32. CountIfNH()

33. CountIfNH() Edellisen kaavion tulkinta Vain 62% vanhoista asiakkaistamme antaa tuotteillemme hyvän tai erinomaisen arvosanan Peräti 24% vanhoista asiakkaistamme pitää tuotteitamme suorastaan huonoina Tuotteidemme laatua tulee nostaa Prosenttiosuuksiin tai keskiarvoihin perustuva grafiikka on informatiivista!

34. Average() Lasketaan 1.kysymyksen kaikille 8 sarakkeelle keskiarvot ja luodaan siihen sopiva kaavio Soluun B8 =AVERAGE(B14:B136) Shift+Ctrl+ArrDown Kopioi kaava sivuittain soluun I8 asti Pyöristä luvut 1 desimaaliin

35. Average() Maalaa otsikko- ja data-alueet Ctrl pohjassa Valikoi palkkikaavio Valitse Series in Columns – Next Ota X-akselin nimi pois (Axes välilehti) Poista Legend (Legend välilehti) Lisää palkkeihin sarjojen nimet (Data Lebels vl) Tallenna kaavio omalle sivulle nimellä Keskiarvot

36. Average() Muutetaan x-akselin skaalaus asteikolle 1-5 Tuplaklikkaa vaaka-akselia Säädä Scale välilehdeltä minimi ja maksimiarvot minimi=1 maksimi=5 Vie sarjojen nimet palkkien sisään Klikkaa sarjan nimeä (esim.Toimitusajat) Aligment välilehdeltä Label Position asetus ”Inside End” Toista kaikille sarjoille

37. Average() Kokeile mitä kaaviolle tapahtuu suodattamalla dataa AVERAGE() funktio ei kykene erottelemaan tulosjoukossa olevaa dataa sen perusteella, että onko kyseinen rivin korkeus nolla vaiko ei Tämän takia on olemassa AVERAGENH()-Webropol funktio NH = [row] Not Hidden

38. AverageNH() Huomioidaksesi suodatukset korvaa kaikki AVERAGE() funktiot AVERAGENH() funktiolla Ctrl+H Kaavio elää dynaamisesti suodatusten ehtojen mukaisesti [Kokeile myös kaaviotyyppiä radar]

39. AverageNH() Otetaan kaavioon vielä mukaan koko tulosjoukon suodatettu keskiarvo

40. AverageNH() Valmista keskiarvoviivaa ei ole, mutta se saadaan aikaiseksi hieman virittämällä Laske soluun B9 koko tulosjoukon ka =AVERAGENH($B14:$I136) Kopioi kaava sarakkeeseen I asti Päivitä kaavion lähdedata Muuta Data Range -> Rows

41. AverageNH() Tuplaklikkaa yhtä keskiarvopalkeista Format Data Series lomake aukeaa Options välilehdeltä Overlap = 100 Gap Width = 0 Patterns välilehdeltä Border = None Area = None Ok

42. AverageNH() Klikkaa hiiren oikealla jotakin palkkia ja valitse Add Trendline Type välilehdellä Based on Series -> Series2 Options välilehdellä Forecast Forward 0,5 Backward 0,5 Ok

43. AverageNH() Poista keskiarvopalkkien Data Labelit Aseta muut labelit palkkien sisälle, esim. Inside Base asetukselle Kokeile suodattaa dataa ja tarkista että myös keskiarvoviiva elää datan mukana Palkkien monivärjäys: Tuplaklikki palkkiin Patterns – Fill Effects - .. - Ok

44. AverageIfNH() Tehdään kaavio, jossa näkyy kunkin asiakasryhmän keskimääräinen tyytyväisyys palveluumme Asiakkaanaoloaika

45. AverageIfNH() AverageIfNH(Range, CriteriaRange, Criteria) Kirjoita soluihin A10:A12 lajittelun arvot Soluun B10 =AVERAGEIFNH(B$14:B$136;A$14:A$136;A10) Kopioi kaava riville 12 asti

46. AverageIfNH() Maalaa data-alue A10:B12 ja tee kaavio normaalisti Tallenna kaavio sivulle AsTyytyväisyys Harjoitus: Suodata näkyviin vain ne rivit, joissa asiakas on antanut tuotteillemme arvosanan 4 tai 5

47. Harjoitus Avaa WebExcel2.xls tiedosto Tee kaavio, joka kertoo mitä asiakkaat ajattelevat palvelustamme ja hinta-laatusuhteestamme jaoteltuna vastaajan sukupuolen mukaisesti

48. Harjoitus Tee kaavio, joka kertoo kuinka tärkeänä uudet asiakkaat näkevä hinta-laatusuhteen

49. Harjoitus Tee kaavio, joka kertoo kuinka tärkeänä naispuoliset vakioasiakkaat näkevä palvelun, ystävällisyyden ja hinta-laatusuhteen Pinottu palkkikaavio

50. AverageFourNH() Webropol-funktio. Laskee nelikenttä -tyyppisten vastausten keskiarvon ja palauttaa sen muodossa x/y. Huomioi tietojen suodattamisen. Origo (0,0) sijaisee vasemmassa yläkulmassa ja suurin arvo (500,500) oikeassa alakulmassa. Oikea yläkulma on (500,0) Vasen alakulma on (0,500)