Tsim Microsoft Excel dashboard suav nrog rub cov ntaub ntawv los ntawm ntau qhov chaw kom koj tuaj yeem saib cov ntaub ntawv ntawd hauv ib qho chaw. Excel muaj ntau txoj hauv kev los ua qhov no, uas ua rau nws yog ib qho tseem ceeb tshaj plaws dashboard qhia cov cuab yeej koj siv tau.
Cov ntaub ntawv hauv phau ntawv qhia no siv rau Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, thiab Excel rau Mac.
Excel Dashboard yog dab tsi?
A cov ntaub ntawv dashboard yog ib qho cuab yeej uas pab koj pom qhov muag saib thiab txheeb xyuas cov ntsuas uas tseem ceeb tshaj plaws rau koj. Koj tuaj yeem siv lub dashboard los saib xyuas koj txoj kev ua lag luam, kev ua haujlwm ntawm cov txheej txheem tsim khoom, lossis kev ua haujlwm ntawm cov neeg ua haujlwm hauv koj lub chaw muag khoom.
Txawm hais tias koj siv lub dashboard li cas, lub tswv yim yeej ib txwm zoo ib yam: Cov ntaub ntawv keeb kwm yav dhau los rub cov ntaub ntawv los ntawm cov ntaub ntawv, cov kev pabcuam, lossis API txuas rau databases thiab lwm qhov chaw. Daim ntawv tseem ceeb qhia cov ntaub ntawv los ntawm ntau nplooj ntawv nyob rau hauv ib qho chaw, qhov twg koj tuaj yeem tshuaj xyuas nws tag nrho ntawm ib sab.
Cov ntsiab lus ntawm cov ntaub ntawv dashboard hauv Excel suav nrog:
- kab ntawv
- Graphs
- Gauges
- Maps
Koj tuaj yeem tsim ob hom dashboards. Rau kev tshaj tawm dashboard, koj tuaj yeem tsim static dashboardlos ntawm cov ntaub ntawv hauv lwm nplooj ntawv uas koj tuaj yeem xa mus rau ib tus neeg hauv Lo Lus lossis PowerPoint tsab ntawv ceeb toom. A dynamic dashboard yog ib tus neeg tuaj yeem saib hauv Excel, thiab nws hloov kho thaum twg cov ntaub ntawv ntawm lwm nplooj ntawv tau hloov kho.
Nqa cov ntaub ntawv rau hauv Excel Dashboard
Thawj theem ntawm kev tsim Excel dashboard yog import cov ntaub ntawv rau hauv cov ntawv nthuav tawm los ntawm ntau qhov chaw.
Cov peev txheej los import cov ntaub ntawv rau hauv Excel suav nrog:
- Lwm cov ntaub ntawv Excel ua haujlwm
- Text, CSV, XML, lossis JSON cov ntaub ntawv
- SQL database
- Microsoft Access
- Azure Data Explorer
- Facebook thiab lwm nplooj ntawv web
- Ib yam lwm yam database uas txhawb ODBC lossis OLDB
- Web sources (txhua lub vev xaib uas muaj cov ntaub ntawv)
Nrog ntau cov ntaub ntawv muaj peev xwm, qhov ua tau ntawm cov ntaub ntawv twg koj tuaj yeem nqa mus rau hauv Excel los tsim cov dashboards muaj txiaj ntsig yog qhov txwv tsis pub.
Nyob rau hauv cov ntaub ntawv:
-
Qhib ib daim ntawv ua haujlwm Excel. Xaiv lub Data ntawv qhia zaub mov, thiab hauv Tau cov ntaub ntawv cov ntawv qhia zaub mov, xaiv hom ntaub ntawv koj xav tau, thiab tom qab ntawd xaiv cov ntaub ntawv qhov chaw.
Image -
Xav mus rau cov ntaub ntawv lossis lwm qhov chaw koj xav import thiab xaiv nws. Xaiv Import.
Image -
Nyob ntawm cov ntaub ntawv qhov chaw koj xaiv, koj yuav pom cov thawv sib tham sib txawv los hloov cov ntaub ntawv mus rau hauv Excel spreadsheet hom.
Image -
Daim ntawv yuav populate nrog tag nrho cov ntaub ntawv los ntawm cov ntaub ntawv sab nraud lossis database.
Image -
Txhawm rau kho cov ntaub ntawv kom nws tsis tu ncua upload cov kev hloov pauv hauv cov ntaub ntawv sab nraud, xaiv lub Refresh icon nyob rau sab xis ntawm Cov lus nug & Kev sib txuas pane.
Image -
Xaiv peb lub dots nyob ib sab ntawm EDIT txuas rau hauv qab ntawm lub qhov rais tshiab, thiab xaiv Properties.
Image -
Txhim kho cov ntaub ntawv kom rov ua dua tshiab los ntawm cov ntaub ntawv hauv lub sijhawm tsis tu ncua los ntawm kev teeb tsa Refresh txhua xx feeb rau lub sijhawm twg koj xav hloov kho cov ntaub ntawv.
Refreshing cov ntaub ntawv los ntawm lwm qhov chaw yog qhov muaj txiaj ntsig, tab sis nws tuaj yeem haus lub sijhawm CPU yog tias koj ua tus nqi refresh ntau dhau. Xaiv tus nqi tshiab uas ua kom cov ntaub ntawv hloov kho ntau zaus raws li nws tau hloov pauv ntawm qhov chaw, tab sis tsis ntau zaus uas koj tsuas yog luam tib cov ntaub ntawv txhua zaus.
Image - Rov ua cov txheej txheem saum toj no hauv cov ntawv tshiab, cais cov ntawv ua haujlwm kom txog thaum koj tau xa tag nrho cov ntaub ntawv koj xav siv rau hauv koj lub dashboard tshiab.
- Thaum kawg, tsim ib daim ntawv ua haujlwm tshiab, tso nws ua thawj daim ntawv ua haujlwm hauv phau ntawv ua haujlwm, thiab hloov npe nws Dashboard.
Yuav ua li cas los tsim ib qho Excel Dashboard
Tam sim no koj muaj tag nrho cov ntaub ntawv koj xav tau hauv koj phau ntawv ua haujlwm Excel, thiab tag nrho cov ntaub ntawv ntawd rov ua dua tshiab, nws yog lub sijhawm los tsim koj lub sijhawm tiag tiag Excel dashboard.
Tus piv txwv dashboard hauv qab no yuav siv cov ntaub ntawv huab cua los ntawm cov vev xaib los ntawm thoob plaws hauv internet.
Qee zaum, thaum koj import cov ntaub ntawv los ntawm lwm qhov chaw, koj tsis tuaj yeem teev cov ntaub ntawv tuaj yeem. Txoj kev kho rau qhov no yog los tsim ib daim ntawv nthuav dav tshiab thiab hauv txhua lub xovtooj, ntaus =hloov pauv (thiab xaiv cov ntaub ntawv los ntawm cov ntawv xa tawm. Thiab tom qab ntawd sau tag nrho cov ntawv nrog tib lub luag haujlwm kom tag nrho cov ntaub ntawv tau theej rau hauv daim ntawv tshiab thiab hloov mus rau cov lej koj tuaj yeem siv hauv ntau cov kab kos uas koj yuav tsim rau koj lub dashboard.
-
Create a Bar Chart los tso saib ib qho ntsiab lus ntawm cov ntaub ntawv. Piv txwv li, kom pom cov av noo tam sim no (los ntawm 0 mus rau 100 feem pua), koj yuav tsim ib daim ntawv qhia bar nrog 0 feem pua ntawm qhov qis tshaj plaws thiab 100 feem pua ntawm qhov siab tshaj plaws. Ua ntej, xaiv qhov Insert ntawv qhia, thiab tom qab ntawd xaiv qhov 2D Clustered Kem daim ntawv qhia.
Image -
In the Chart Design menu, from the Data pawg, xaiv Xaiv cov ntaub ntawv.
Image -
Nyob rau hauv Xaiv cov ntaub ntawv qhov rai uas tshwm sim, nyem lub Daim duab kab ntawvteb, thiab tom qab ntawd xaiv lub xovtooj ntawm cov ntaub ntawv spreadsheet uas koj xav tso saib nrog daim duab bar no.
Image -
Hloov lub npe ntawm daim duab kom phim cov ntaub ntawv koj tab tom nthuav tawm. Hloov kho cov axis ciam teb los ntawm 0 mus rau 100 feem pua. Tom qab ntawd txav daim duab mus rau thaj tsam ntawm qhov khiav ceev uas koj xav pom nws.
Image -
Rov ua tib yam cov kauj ruam saum toj no los tsim cov kab kos rau lwm cov ntaub ntawv ib leeg uas koj xav sau. Ua cov axis qhov tsawg kawg nkaus thiab siab tshaj plaws rau cov kev ntsuas ntawd. Piv txwv li, qhov zoo barometric siab yuav yog 28 txog 32.
Xaiv cov ntaub ntawv raug yog qhov tseem ceeb vim tias yog tias koj tsuas yog siv lub neej ntawd, qhov ntsuas yuav loj dhau rau cov ntaub ntawv, tawm hauv feem ntau cov kab kos tsis muaj. Hloov chaw, khaws qhov tsawg kawg nkaus thiab siab kawg ntawm qhov axis nplai tsuas yog qis me ntsis thiab siab dua qhov muaj txiaj ntsig zoo ntawm koj cov ntaub ntawv.
Image -
Create a Line Chart los tso saib cov ntaub ntawv sib txawv. Piv txwv li, los tso saib keeb kwm ntawm qhov kub thiab txias hauv cheeb tsam rau koj cheeb tsam hauv cheeb tsam, koj yuav tsum tsim cov kab kos uas npog cov hnub kawg ntawm cov ntaub ntawv koj tuaj yeem import los ntawm huab cua lub vev xaib. Ua ntej, xaiv qhov Insert menu xaiv qhov 2D Area daim ntawv.
Image -
In the Chart Design menu, from the Data pab pawg, xaiv Xaiv cov ntaub ntawv.
Image -
Nyob rau hauv Xaiv Cov Ntaub Ntawv qhov rai uas tshwm sim, nyem rau Daim duab kab ntawvteb, thiab tom qab ntawd xaiv cov hlwb hauv cov ntaub ntawv spreadsheet koj xav tso saib nrog kab ntawv no.
Image -
Hloov lub npe ntawm daim duab kom phim cov ntaub ntawv koj tab tom nthuav tawm thiab txav daim ntawv mus rau thaj tsam ntawm qhov khiav ceev uas koj xav tso tawm.
Cov kab kos hloov tau yooj yim thaum muab tso rau hauv dashboard. Koj tuaj yeem hloov qhov chaw nrog rau qhov loj thiab cov duab ntawm daim duab widget. Siv qhov yooj yim no los tsim cov dashboards uas muab cov ntaub ntawv ntau npaum li cas rau cov neeg siv hauv qhov tsawg tshaj plaws ntawm qhov chaw.
Image -
Tsim ib Textbox los tso saib cov ntaub ntawv ntawm cov nplooj ntawv koj tau import. Piv txwv li, txhawm rau pom kev ceeb toom huab cua hloov tshiab ntawm koj lub dashboard, txuas cov ntsiab lus ntawm lub thawv ntawv mus rau lub xov tooj ntawm tes hauv cov ntaub ntawv xa tawm. Txhawm rau ua qhov no, xaiv qhov Insert ntawv qhia zaub mov, xaiv Text, thiab tom qab ntawd xaiv lub Textbox
Image -
Tso tus nas tus cursor rau hauv daim foos teb, ntaus =thiab tom qab ntawd xaiv lub xovtooj ntawm tes hauv cov ntaub ntawv xa tuaj uas muaj cov ntaub ntawv uas koj xav tso tawm.
Image -
Xaiv lub thawv ntawv thiab siv lub Format Shape qhov rai ntawm sab xis los format cov ntawv nyeem hauv cheeb tsam hauv koj lub dashboard.
Image -
Koj tuaj yeem sib piv ob cov ntsiab lus hauv koj cov ntaub ntawv xa tuaj uas siv daim duab ncuav qab zib Piv txwv li, koj yuav xav tso cov av noo nyob rau hauv daim ntawv qhia ncuav qab zib. Ua ntej, xaiv cov ntaub ntawv koj xav tso saib, thiab hauv Insert ntawv qhia zaub mov, xaiv 2D Pie daim ntawv.
Daim duab ncuav qab zib piv ob lossis ntau qhov txiaj ntsig. Yog tias koj tab tom pom ib feem pua zoo li cov av noo, koj yuav tsum tau manually tsim lwm lub xov tooj ntawm tes uas rho tawm tus nqi ntawm 100% rau tus nqi thib ob los piv rau. Qhov no yuav ua rau daim ntawv qhia ncuav qab zib uas qhia txog qhov feem pua tus nqi raws li ib feem ntawm tag nrho cov ua tau 100 feem pua.
-
Hloov lub npe ntawm daim ntawv kom phim cov ntaub ntawv koj tab tom nthuav tawm, thiab tom qab ntawd txav daim ntawv mus rau thaj tsam ntawm qhov khiav ceev uas koj xav tso tawm.
Image - Ntxiv ntau yam ntaub ntawv qhia hom, koj tuaj yeem tsim lub dashboard muaj txiaj ntsig uas qhia txhua hom ntaub ntawv hauv ib qho yooj yim dashboard.
Ntxiv Kev Pom Kev Pom Zoo thiab Cov Ntsiab Lus Nrog Xim
Lwm txoj hauv kev ntxiv kom pom tseeb rau koj lub dashboard yog los ntawm kev muab koj cov kab kos kab kos ua cov gradient sau uas qhia cov xim ceeb toom xws li liab rau thaj tsam ntawm cov ntaub ntawv uas tej zaum yuav tsis zoo.
Piv txwv li, yog tias koj xav pom tias qhov txheeb ze av noo ntau dua 75% tsis xis nyob, koj tuaj yeem hloov qhov gradient sau ntawm ib daim ntawv qhia raws li. Nov yog li cas.
-
Txoj cai-nias ntawm tus ciam teb sab nraud ntawm daim ntawv qhia bar thiab xaiv Format Daim Ntawv Thaj Tsam.
Image -
Xaiv tus Fill icon nyob rau hauv Format Chart Area pane thiab hloov qhov kev xaiv rau Gradient sau.
Image -
Xaiv txhua theem icon raws kab gradient sau thiab hloov xim thiab tsaus kom haum li 'zoo' lossis 'phem' qib ntawd. Hauv qhov piv txwv no, cov av noo nyob hauv siab yuav ploj mus rau xim liab.
Image - Rov ua cov txheej txheem no rau txhua daim ntawv qhia qhov twg ntxiv cov ntsiab lus xim rau hauv daim ntawv ua rau kev nkag siab rau cov ntaub ntawv ntawd.
Yuav ua li cas Excel Dashboards Hloov Kho Tsis Siv Neeg
Thaum koj tsim lub dashboard, koj tsis tas yuav ua dab tsi los hloov kho cov duab. Tag nrho cov ntaub ntawv hauv cov kab kos thiab widgets hloov tshiab raws li hauv qab no:
- Cov nplooj ntawv nrog cov ntaub ntawv xa tuaj tshiab tshiab ntawm hnub uas koj tau teeb tsa thaum koj xub tsim cov ntaub ntawv ntshuam.
- Txhua nplooj ntawv ntxiv uas koj tsim los kho lossis hloov kho cov ntaub ntawv los ntawm cov nplooj ntawv xa tuaj yuav hloov kho nrog cov ntaub ntawv tshiab hauv cov nplooj ntawv.
- Txhua tus widget hauv koj lub dashboard tau hloov kho tshiab los tso saib cov ntaub ntawv tshiab hauv cov nplooj ntawv tshiab rau cov kab uas koj xaiv thaum koj tsim cov kab kos.
Cov kev hloov tshiab no tshwm sim tau ntev npaum li Excel qhib.
Yuav siv Excel Dashboards li cas
Tsim dashboards hauv Excel tuaj yeem pab tau rau ntau yam sib txawv. Txawm li cas los xij, nws yog ib qho tseem ceeb los tsim lawv raws li qhov xav tau tshwj xeeb, tsis yog sim tsim ib lub dashboard uas ua txhua yam.
Piv txwv li, yog tias koj yog tus thawj coj muag khoom thiab koj txaus siab saib xyuas kev ua haujlwm ntawm koj pab neeg muag khoom, ces tus thawj tswj hwm kev muag khoom yuav tsum tsom mus rau Cov Ntsuas Kev Ua Haujlwm Zoo (KPIs) ntsig txog kev muag khoom.
Cov dashboard zoo li no yuav tsum tsis txhob suav nrog cov ntaub ntawv tsis cuam tshuam txog kev muag khoom lossis lwm yam dashboard tuaj yeem ua rau cluttered. Lub dashboard cluttered ua rau nws nyuaj rau pom cov ntaub ntawv kev sib raug zoo thiab cov qauv uas tseem ceeb.
Lwm qhov kev txiav txim siab thaum tsim cov dashboards:
- Siv cov kab kos kom raug rau cov ntaub ntawv raug.
- Tsis txhob siv ntau xim thoob plaws lub dashboard.
- Tau tawm lub dashboard nrog cov ntaub ntawv zoo sib xws thiab cov hom kab kos hauv cov blocks.
- Ua kom txhua daim ntawv qhia cov ntawv yooj yim thiab tsis cluttered.
- Txhim cov widgets nyob rau hauv hierarchy ntawm qhov tseem ceeb, nrog rau cov ntaub ntawv tseem ceeb tshaj plaws nyob rau sab sauv-sab laug ntawm dashboard.
- Siv cov hom kev cai kom paub tseeb tias thaum tus lej phem lawv liab, thiab thaum lawv zoo lawv ntsuab.
tseem ceeb tshaj plaws, siv lub tswv yim los tsim cov dashboards uas qhia tau zoo thiab nthuav dav siv.
