Kreiranje Microsoft Excel kontrolne table uključuje povlačenje informacija iz više izvora kako biste te podatke mogli vidjeti na jednom mjestu. Excel nudi razne načine za to, što ga čini jednim od najmoćnijih alata za izvještavanje na kontrolnoj tabli koje možete koristiti.
Informacije u ovom vodiču odnose se na Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 i Excel za Mac.
Šta je Excel kontrolna tabla?
Kontrolna tabla podataka je alat koji vam pomaže da vizuelno nadgledate i analizirate metrike koje su vam najvažnije. Možete koristiti kontrolnu tablu da nadgledate svoj poslovni učinak, efikasnost proizvodnog procesa ili učinak osoblja u vašem odjelu prodaje.
Bez obzira na to kako koristite kontrolnu tablu, koncept je uvijek isti: pozadinske proračunske tablice povlače podatke iz datoteka, usluga ili API veza do baza podataka i drugih izvora. Glavni list prikazuje podatke iz tih više listova na jednom mjestu, gdje možete pregledati sve na prvi pogled.
Tipični elementi kontrolne table podataka u Excel-u uključuju:
- Charts
- Grafovi
- Gauges
- Mape
Možete kreirati dvije vrste nadzornih ploča. Za izvještavanje sa kontrolne table, možete kreirati statičku kontrolnu tablu od podataka u drugim listovima koje možete poslati nekome u Word ili PowerPoint izvještaju. dinamička kontrolna tabla je ono što ljudi mogu vidjeti u Excelu, a ažurira se svaki put kada se ažuriraju podaci na drugim listovima.
Unos podataka u Excel kontrolnu tablu
Prva faza kreiranja Excel kontrolne table je uvoz podataka u proračunske tabele iz različitih izvora.
Potencijalni izvori za uvoz podataka u Excel uključuju:
- Ostali fajlovi Excel radne sveske
- Tekstualni, CSV, XML ili JSON fajlovi
- SQL baza podataka
- Microsoft Access
- Azure Data Explorer
- Facebook i druge web stranice
- Bilo koja druga baza podataka koja podržava ODBC ili OLEDB
- Web izvori (bilo koja web lokacija koja sadrži tabele podataka)
S toliko potencijalnih izvora podataka, mogućnost koje podatke možete unijeti u Excel za kreiranje korisnih nadzornih ploča je neograničena.
Za unos izvora podataka:
-
Otvorite prazan Excel radni list. Odaberite Data meni, a u Get Data padajućem izborniku odaberite željeni tip podataka, a zatim odaberite izvor podataka.
Image -
Potražite fajl ili drugi izvor podataka koji želite da uvezete i izaberite ga. Odaberite Uvoz.
Image -
U zavisnosti od tipa izvora podataka koji odaberete, vidjet ćete različite dijaloške okvire za transformaciju podataka u Excel format tabele.
Image -
Tablica će se popuniti svim podacima iz eksterne datoteke ili baze podataka.
Image -
Da biste osvježili podatke tako da redovno uploaduju sve promjene napravljene u vanjskom izvoru podataka, odaberite Refresh ikonu na desnoj strani Upiti & Connections okno.
Image -
Izaberite tri tačke pored veze EDIT na dnu prozora za osvježavanje i odaberite Properties.
Image -
Konfigurirajte podatke za osvježavanje iz izvora podataka u redovnim intervalima postavljanjem Osvježavanje svakih xx minuta na bilo koji interval u kojem želite ažurirati podatke.
Osvježavanje podataka iz vanjskih izvora je korisno, ali može potrošiti CPU vrijeme ako frekvenciju osvježavanja učinite prečestom. Odaberite brzinu osvježavanja koja održava podatke ažuriranima otprilike onoliko često koliko se mijenjaju na izvoru, ali ne tako često da svaki put kopirate samo iste podatke.
Image - Ponovite gornji proces u novim, odvojenim radnim listovima dok ne uvezete sve podatke koje želite koristiti u vašoj novoj kontrolnoj tabli.
- Konačno, kreirajte novi radni list, stavite ga kao prvi radni list u radnu svesku i preimenujte ga Dashboard.
Kako kreirati Excel kontrolnu tablu
Sada kada imate sve podatke koji su vam potrebni u Excel radnoj svesci, a svi ti podaci se automatski osvježavaju, vrijeme je da kreirate svoju Excel kontrolnu tablu u realnom vremenu.
Primjer kontrolne table ispod će koristiti podatke o vremenu sa web lokacija širom interneta.
Ponekad, kada uvozite podatke iz eksternih izvora, ne možete ucrtati uvezene podatke. Popravka za ovo je da kreirate novu tabelu i u svakoj ćeliji ukucate =convert(i izaberete podatke iz uvezene tabele. Za parametre jedinice, samo izaberite iste parametre za prethodno i poslije. Ispunite cijeli list istom funkcijom tako da se svi podaci kopiraju na novi list i konvertuju u brojeve koje možete koristiti u različitim grafikonima koje ćete kreirati za svoju kontrolnu tablu.
-
Kreirajte Bar Chart za prikaz jedne tačke podataka. Na primjer, da biste prikazali trenutnu relativnu vlažnost (od 0 do 100 posto), kreirali biste trakasti grafikon sa 0 posto kao najnižom tačkom i 100 posto kao najvišom tačkom. Prvo odaberite Insert meni, a zatim odaberite 2D grupisane kolone trakasti grafikon.
Image -
U Chart Design meniju, iz grupe Data, izaberite Select Data.
Image -
U prozoru Odaberi izvor podataka koji se pojavi, kliknite na polje Raspon podataka grafikona, a zatim odaberite ćeliju u podacima tabela koju želite da prikažete sa ovim trakastim grafikonom.
Image -
Promijenite naslov grafikona da odgovara podacima koje prikazujete. Ažurirajte granice ose da budu od 0 do 100 posto. Zatim premjestite grafikon na područje crtice gdje želite da ga prikažete.
Image -
Ponovite gore navedene korake da kreirate trakaste grafikone za bilo koje druge pojedinačne tačke podataka koje želite da napravite grafikon. Neka raspon osi bude minimalni i maksimalni za ta mjerenja. Na primjer, dobar raspon barometrijskog tlaka bi bio od 28 do 32.
Odabir pravog raspona podataka je važan jer ako koristite samo zadani, skala može biti prevelika za podatke, ostavljajući uglavnom prazne trakaste grafikone. Umjesto toga, držite minimalni i maksimalni kraj skale osi samo malo niži i viši od ekstremno mogućih vrijednosti vaših podataka.
Image -
Kreirajte linijski grafikon za prikaz trenda podataka. Na primjer, da biste prikazali historiju lokalnih temperatura za vaše lokalno područje, kreirali biste linijski grafikon koji pokriva posljednji broj dana podataka koje možete uvesti iz tabele web stranice o vremenu. Prvo izaberite meni Insert izaberite 2D Area grafikon.
Image -
U Chart Design meniju, iz grupe Data, izaberite Select Data.
Image -
U prozoru Odaberi izvor podataka koji se pojavi, kliknite na polje Raspon podataka grafikona, a zatim odaberite ćelije u podacima tabela koju želite da prikažete sa ovim linijskim grafikonom.
Image -
Promijenite naslov grafikona da odgovara podacima koje prikazujete i premjestite grafikon na područje crtice gdje želite da ga prikažete.
Grafikoni su veoma fleksibilni kada se postavljaju na kontrolnu tablu. Možete promijeniti lokaciju kao i veličinu i oblik widgeta grafikona. Koristite ovu fleksibilnost da dizajnirate organizirane kontrolne ploče koje pružaju što više informacija korisniku na najmanjoj količini prostora.
Image -
Kreirajte Textbox za prikaz string podataka iz listova koje ste uvezli. Na primjer, da vidite ažuriranja vremenskih upozorenja na kontrolnoj tabli, povežite sadržaj okvira za tekst sa ćelijom u uvezenoj tablici podataka. Da biste to učinili, odaberite Insert meni, odaberite Text, a zatim odaberite Textbox
Image -
Postavite kursor miša u polje formule, otkucajte =, a zatim izaberite ćeliju u tabeli uvezenih podataka koja sadrži niz podataka koje želite da prikažete.
Image -
Izaberite okvir za tekst i koristite prozor Format Shape na desnoj strani da formatirate oblast za prikaz teksta na vašoj kontrolnoj tabli.
Image -
Također možete uporediti dvije tačke podataka u vašim uvezenim podacima koristeći pie charts Na primjer, možda ćete htjeti prikazati relativnu vlažnost u obliku tortnog grafikona. Prvo izaberite podatke koje želite da prikažete, a zatim u Insert meniju izaberite 2D Pie grafikon.
Tortni grafikoni upoređuju dvije ili više vrijednosti. Ako prikazujete postotak poput relativne vlažnosti, možda ćete morati ručno kreirati drugu ćeliju koja oduzima tu vrijednost od 100% za drugu vrijednost da biste je uporedili. Ovo će rezultirati tortnim grafikonom koji prikazuje vrijednost procenta kao dio od ukupno mogućih 100 posto.
-
Promijenite naslov grafikona da odgovara podacima koje prikazujete, a zatim premjestite grafikon na područje crtice gdje želite da ga prikažete.
Image - Dodavanjem različitih tipova grafikona podataka, možete kreirati korisnu kontrolnu tablu koja prikazuje sve vrste podataka na jednoj prikladnoj kontrolnoj tabli.
Dodajte vizuelnu privlačnost i kontekst sa bojom
Drugi način da dodate jasnoću vašoj kontrolnoj tabli je da vašim trakastim dijagramima date gradijentnu ispunu koja prikazuje boju upozorenja poput crvene za regije podataka koji možda nisu dobri.
Na primjer, ako želite da pokažete da je relativna vlažnost od preko 75% neugodna, možete u skladu s tim promijeniti popunu gradijenta jednostrukog grafikona. Evo kako.
-
Kliknite desnim tasterom na vanjsku granicu trakastog grafikona i odaberite Formatiraj područje grafikona.
Image -
Odaberite ikonu Fill u oknu Format Chart Area i promijenite izbor u Gradient fill.
Image -
Odaberite svaku ikonu nivoa duž linije gradijenta i promijenite boju i tamu kako bi odgovarali tome koliko je taj nivo 'dobar' ili 'loš'. U ovom primjeru, visoka relativna vlažnost blijedi u tamnocrvenu.
Image - Ponovite ovaj proces za svaki grafikon gdje dodavanje konteksta boja na grafikon ima smisla za tu tačku podataka.
Kako se Excel kontrolne table automatski ažuriraju
Kada kreirate kontrolnu tablu, ne morate ništa da radite da biste ažurirali grafiku. Svi podaci u tim grafikonima i widgetima se ažuriraju na sljedeći način:
- Tablice sa uvezenim podacima osvježavaju se na datum koji ste postavili kada ste prvi put kreirali uvoz podataka.
- Svi dodatni listovi koje ste kreirali da popravite ili preformatirate podatke iz uvezenih listova ažurirat će se novim podacima u tim listovima.
- Svaki widget na vašoj kontrolnoj tabli se automatski ažurira da prikaže nove podatke unutar ovih ažuriranih listova za raspone koje ste odabrali kada ste kreirali te grafikone.
Ova ažuriranja se dešavaju automatski sve dok je Excel otvoren.
Kako koristiti Excel kontrolne table
Kreiranje kontrolne table u Excel-u može biti korisno iz mnogo različitih razloga. Međutim, važno je kreirati ih na osnovu specifične potrebe, umjesto da pokušavate napraviti jednu kontrolnu ploču koja radi sve.
Na primjer, ako ste menadžer prodaje i zainteresirani ste za praćenje učinka vašeg prodajnog tima, tada bi se kontrolna tabla menadžera prodaje trebala fokusirati na ključne pokazatelje učinka (KPI) koji se odnose na performanse prodaje.
Ova vrsta kontrolne table ne bi trebala sadržavati informacije koje nisu povezane s prodajnim učinkom ili bi u suprotnom kontrolna tabla mogla biti previše pretrpana. Pretrpana kontrolna tabla otežava sagledavanje odnosa podataka i obrazaca koji su važni.
Ostala razmatranja prilikom izrade kontrolne table:
- Koristite prave grafikone za prave podatke.
- Ne koristite previše boja na kontrolnoj tabli.
- Postavite kontrolnu tablu sa sličnim podacima i tipovima grafikona u uobičajenim blokovima.
- Uvjerite se da svaki grafikon prikazuje jednostavne oznake i da nije previše pretrpan.
- Organizirajte widgete u hijerarhiji važnosti, sa najvažnijim informacijama u gornjem lijevom dijelu kontrolne table.
- Koristite uslovno oblikovanje da biste bili sigurni da su brojevi loši crveni, a kada su dobri zeleni.
Najvažnije, koristite kreativnost da dizajnirate kontrolne table koje su informativne i zanimljive za korištenje.
