Yuav ua li cas ua ib daim ntawv qhia kev ua haujlwm ntawm cov haujlwm hauv Excel hauv 10 kauj ruam

2024 Tus sau: Malcolm Clapton | [email protected]. Kawg hloov kho: 2023-12-17 03:58

Siv cov cuab yeej li niaj zaus ntawm MS Excel spreadsheet editor uas nrov tshaj plaws, koj tuaj yeem kos duab kos duab ntawm kev ua haujlwm ntawm cov haujlwm, uas yuav pab koj taug qab seb txhua txoj haujlwm tau nce mus li cas thiab ntau npaum li cas yuav tsum tau ua.

Yog tias koj tab tom tswj hwm cov haujlwm, nws yog ib qho tseem ceeb kom nco ntsoov tias koj muaj pes tsawg txoj haujlwm thiab thaum twg lawv yuav tsum ua kom tiav. Thiab yog tias koj kos ib daim duab kos duab, nws yuav yooj yim dua los piav qhia cov ntsiab lus tseem ceeb hauv lub neej ntawm cov haujlwm rau pawg neeg koom tes hauv kev ua haujlwm, nrog rau lwm cov neeg koom nrog.

Cov kev pabcuam tshwj xeeb feem ntau siv los tswj cov haujlwm, tab sis koj tuaj yeem tsim lub sijhawm meej thiab siv tau hauv MS Excel paub.

Daim duab kos duab ua haujlwm qhia tau hais tias cov xwm txheej thiab cov haujlwm cuam tshuam li cas rau ib leeg thaum lub sijhawm. Daim duab tsim tau zoo qhia txog cov ntaub ntawv tseem ceeb txog kev ua tiav ntawm ib txoj haujlwm tshwj xeeb thiab kev txhim kho ntawm qhov project tag nrho, pab kom pom cov duab loj.

Txhawm rau tsim ib daim ntawv qhia hauv MS Excel (versions 2007 thiab tom qab), koj yuav tsum ua 10 kauj ruam xwb.

Dab tsi yog dab tsi hauv daim duab no? Cov ntsiab lus tseem ceeb tau qhia nrog cov kab xim thiab cov phiaj xwm:

• kab ntawm hnub tam sim no - kab liab ntsug uas qhia tias koj nyob qhov twg hnub no;
• Lub sijhawm ua haujlwm - grey kab rov tav kab uas qhia tias nws yuav siv sijhawm ntev npaum li cas los ua kom tiav txhua txoj haujlwm;
• kev npaj ntawm cov dej num - cov kab kab kab ntsuab uas qhia tau hais tias nyob rau theem twg txoj haujlwm tau ua tiav thiab yuav tsum tau ua ntau npaum li cas.

Hauv kev sib txawv ntawm MS Excel, cov kev ua yuav txawv me ntsis, tab sis ib txoj kev los yog lwm txoj kev yuav yog universal, thiab txhua tus neeg siv tuaj yeem tiv nrog kev teem sijhawm.

Koj tuaj yeem saib cov lus qhia ntxaws ntxaws (ua lus Askiv), nws yuav siv sijhawm ib nrab teev ntawm koj lub sijhawm.

Kauj Ruam 1. Pib, xav txog qhov tshwm sim

Txhawm rau tiv thaiv txoj haujlwm yam tsis muaj teeb meem, luam lossis kos rau ntawm daim ntawv teev sijhawm raws li nws yuav tsum saib thaum kawg. Tuav daim ntawv nyob rau hauv pem hauv ntej ntawm koj los sib piv txhua kauj ruam nrog ib tug qauv. Daim duab kos tsis tas yuav tsum tau ua tib zoo kos, tsuas yog kho cov duab ntawm daim duab thiab khaws cia, nws yuav yog ib qho kev ceeb toom zoo.

Kauj ruam 2. Tsim ib lub rooj nrog cov ntaub ntawv

Txhawm rau tsim ib daim duab, koj yuav tsum xub kos ib lub rooj. Hauv qhov piv txwv, qhov project tau muab faib ua plaub theem, txhua qhov ua haujlwm.

Tsim ib lub rooj hauv qab daim duab. 30th kab muaj cov kab ntawv kab lus.

Cia peb xam seb cov ntaub ntawv hauv lub rooj txhais li cas:

• Theem: "Theem 1", "Theem 2", "Theem 3", "Theem 4" lossis "Hnub no". Qhia tias theem twg ntawm qhov project qhov kev tshwm sim sib raug rau.
• Hom ntawm: "Phase", "Task", "Hnub no". Qhia txog hom kev tshwm sim, delimiting cov dej num thiab theem ntawm kev ua tiav qhov project.
• Pib hnub: pib hnub ntawm qhov kev tshwm sim.
• Kev tshwm sim: qhia lub npe ntawm qhov xwm txheej hauv daim duab.
• Sijhawm: qhia tias nws yuav siv sij hawm ntev npaum li cas los ua kom tiav txoj haujlwm.
• Feem pua kev npaj: qhia txog qib ntawm kev ua tiav rau txhua txoj haujlwm thiab theem, 0% - kev ua haujlwm tsis tau pib, 100% - ua tiav. Qhov ntsuas no rau qeb "Phase" tsis muaj feem cuam tshuam nrog kev ua tiav ntawm cov dej num thiab tau teeb tsa raws li kev kwv yees. Txhawm rau txiav txim siab qhov feem pua ntawm ib theem ua tiav, koj yuav tsum siv koj tus kheej cov cai.
• Npaj txhij rau hnub: lub sij hawm uas qhov ntsuas qhov kev npaj npaj tau mus txog qhov feem pua. Rau ib txoj haujlwm uas siv 10 hnub thiab ua tiav 50%, daim duab no yuav yog tsib hnub (10 hnub × 50%).
• Qhov siab: qhov siab tus nqi yog qhov tseem ceeb rau kev kho kom zoo nkauj vim nws txiav txim siab npaum li cas los yog qis qhov kev tshwm sim yuav tshwm sim nyob rau hauv daim ntawv qhia hnub. Koj tuaj yeem hais meej txhua qhov tseem ceeb, tab sis nws yog qhov zoo dua los coj tus lej me me ze rau xoom. Piv txwv siv qhov ntau ntawm +25 txog -25. Yog tias qhov siab yog qhov zoo, qhov kev tshwm sim yuav tshwm sim ntawm daim duab saum toj no ntawm kab rov tav axis, yog tias tsis zoo - hauv qab ntawm axis.

Kauj ruam 3. Tsim ib daim ntawv nrog X thiab Y axes, ntxiv thawj cov ntaub ntawv los ntawm kab "Tsev Sijhawm" rau nws

Nws yog lub sijhawm los tsim ib daim ntawv qhia kom pom cov ntaub ntawv tshwm sim los ntawm lub rooj ua hauv cov kauj ruam dhau los. Siv cov phiaj xwm tawg paj nrog X thiab Y axes vim tias nws tso cai rau koj tso cai rau lub hauv paus kev tswj hwm ntawm ob axes.

Ua raws li cov lus qhia:

• Xaiv "Ntxig" tab hauv cov ntawv qhia zaub mov tseem ceeb.
• Tom qab ntawd xaiv "Tshaj" → "Scatter" → "Scatter with markers", ib daim ntawv khoob yuav tshwm nyob rau hauv pem hauv ntej ntawm koj.
• Hook cov npoo ntawm daim ntawv thiab hloov qhov loj me thiab hloov pauv daim ntawv qhia kom daim duab qhia npog thaj tsam ntawm cell B4 mus rau cell K26 (nias thiab tuav Alt tus yuam sij kom ua raws li daim ntawv qhia cheeb tsam raws nraim ntawm cov ciam teb ntawm tes).
• Txoj nyem rau ntawm daim duab thiab xaiv "Xaiv cov ntaub ntawv" cov ntawv qhia zaub mov.

Ntxiv cov ntaub ntawv ntau ntxiv rau qhov ntau yam los ntawm txhaj rau ntawm "Ntxiv" khawm hauv lub dialog box uas qhib.

1. Txhawm rau hloov lub npe "Series lub npe" muab tso rau hauv qhov chaw sib txuas tus nqi ntawm tes E30 "Hnub".
2. Rau tus nqi X-axis, xaiv qhov tsim nyog teb thiab xaiv cov hlwb C33: C46 Pib Hnub.
3. Rau tus nqi Y-axis, xaiv qhov tsim nyog teb thiab xaiv cov hlwb H33: H46 Qhov siab.
4. Nyem OK kaw lub qhov rais ntxiv cov ntaub ntawv.
5. Nyem OK kaw lub qhov rais xaiv cov ntaub ntawv.

Cov kauj ruam no yuav muab rau peb ib qho yooj yim scatter daim ntawv nrog tsis siv neeg formatting.

Cia peb ua cov cim kev tshwm sim ntsuab:

• Txoj cai-nias rau ntawm ib qho kev xaiv cov ntaub ntawv cim, xaiv hloov "Cov ntaub ntawv series hom".
• Hauv daim ntawv uas qhib rau sab laug, xaiv "Marker parameters" tab, tom qab ntawd xaiv cov cim cim "Crystal". Ntxiv qhov loj mus rau 10 pt.
• Ntawm Marker Fill tab, xaiv ib qho khoom sau. Hloov cov xim puv rau ntsuab.

Tam sim no peb muaj ib daim phiaj xwm txheej sawv cev rau ncua sij hawm. Txog tam sim no, cov cim ntsuab tsuas yog qhia txog kev pib ua haujlwm.

Hloov lub npe ntawm daim duab: ob npaug nias rau ntawm daim ntawv teb nrog lub npe thiab sau koj lub npe.

Peb yuav rov qab mus rau lub sij hawm xaiv nyob rau hauv kauj ruam 5, ntxiv kab rov tav kab raws X-axis, tab sis rau tam sim no txuas ntxiv formatting daim ntawv.

Kauj Ruam 4. Muab tso rau hauv kev txiav txim kom pom cov duab kos thiab ntxiv cov npe ntawm cov xwm txheej

Peb tshem tawm yam peb tsis xav tau hauv daim duab.

Y-axis … Nkaum ntsug axis, vim nws tsis nqa cov ntaub ntawv load. Xaiv ib daim duab, hauv cov ntawv qhia zaub mov tseem ceeb ntawm "Ua haujlwm nrog daim duab" tab, xaiv "Layout". Tom qab ntawd xaiv Axes → Main Vertical Axis → Tsis Qhia.

Kab rov tav qhia … Lawv kuj tsis muaj txiaj ntsig. Xaiv daim ntawv qhia, hauv cov ntawv qhia zaub mov tseem ceeb, mus rau Layout tab, tom qab ntawd xaiv Daim phiaj → Kab rov tav Gridlines Raws Loj Axis → Tsis Qhia.

Daim ntawv dab neeg … Peb mam li hloov nws nrog ib qho zoo nkauj dua, tab sis tam sim no lov tes taw nws: "Layout" → "Legend" → "Tsis yog".

Thaum kawg, peb tso saib cov ntawv sau rau txhua tus cim. Hauv cov ntawv qhia zaub mov tseem ceeb ntawm "Layout" tab, xaiv "Cov Ntaub Ntawv Kos Npe" → "Sab laug".

Yuav ua li cas hloov cov ntaub ntawv hauv token kos npe

Nkag mus rau cov ntaub ntawv hais txog txhua qhov xwm txheej hauv daim duab yog ntev thiab nyuaj, vim tias koj yuav tsum xaiv txhua tus cim cais thiab hloov npe nws.

Tab sis cov txheej txheem no tuaj yeem nrawm plaub zaug:

• Nyem rau ntawm lub npe ntawm thawj tus cim (saum toj sab laug ntawm daim duab) xaiv tag nrho cov txiaj ntsig ib zaug. Tsis txhob nyem rau ntawm lub cim ntsuab, qhov no yuav xaiv lwm yam khoom!
• Nyem dua ntawm lub cim kab lus los hloov lub npe ib leeg.
• Nyob rau hauv lub formula input teb, ntaus lub cim =.
• Nyem rau ntawm tes D33, nws muaj qhov xwm txheej "Phase 1", nias Enter.
• Rov ua thawj plaub kauj ruam rau cov ntawv sau ntxiv rau txhua tus cim.

Yuav ua li cas pin thaj chaw

Nws yog qhov pab tau rau pin cov cheeb tsam ntawm kab 28 kom koj tuaj yeem pom daim duab txhua lub sijhawm thaum ua haujlwm ntawm daim ntawv. Thawj 27 kab yuav nyob rau hauv pem hauv ntej ntawm koj ob lub qhov muag, txawm hais tias koj scroll los ntawm tag nrho cov kab.

Kom khov thaj chaw:

• Xaiv lub xov tooj A28.
• Xaiv lub "Saib" tab los ntawm cov ntawv qhia zaub mov tseem ceeb.
• Xaiv Qhov rai → Kho qhov chaw.
• Xaiv "Qhib Thaj Chaw" los ntawm cov ntawv qhia zaub mov.

Kab rov tav dub yuav tshwm saum kab 28th. Txhua yam saum toj no yog tam sim no docked thiab tsuas yog cov kab hauv qab yuav txav mus thaum scrolling.

Kauj ruam 5. Ntxiv cov ntawv yuam kev los tso saib ncua sij hawm kom tig daim ntawv mus rau hauv lub sijhawm pom

Kab rov tav kab teeb meem ntawm daim duab yuav qhia tias nws siv sijhawm ntev npaum li cas los ua kom tiav txhua txoj haujlwm. Ua lawv:

• Teeb pom kev zoo.
• Los ntawm cov ntawv qhia zaub mov tseem ceeb, xaiv Layout tab.
• Hauv daim ntawv teev npe poob rau hauv kaum sab laug sab sauv (thaj tsam thaj tsam), xaiv "Tsev Sijhawm" cov ntaub ntawv series.
• Ntawm Layout tab, xaiv yuam kev Bars → Advanced Error Bars Settings. Ib lub dialog box yuav qhib.
• Nyob rau hauv daim ntawv teev-down nyob rau sab laug ces kaum, xaiv X-axis yuam kev tuav los format daim ntawv.
• Nyob rau hauv lub dialog box hloov kab rov tav bar, xaiv lub checkbox rau tus Plus pin. Xaus Style → Tsis muaj Point.
• Nyob rau hauv daim ntawv "Yuam kev tus nqi", xaiv cov khoom "Custom" thiab nias lub pob "Specify tus nqi". Nyob rau hauv lub qhov rais tshiab "Txhim kho qhov yuam kev bar" uas qhib, qhia qhov "Positive yuam kev nqi". Nyem OK.

Tam sim no cov kab grey txuas ntxiv los ntawm cov cim ntsuab mus rau sab xis, lawv qhia tias koj yuav tsum siv sijhawm ntau npaum li cas rau qhov no lossis txoj haujlwm ntawd. Lawv yuav tsum tau formatted rau clarity:

• Hauv Format yuam kev Bars qhov rais, mus rau Kab Xim tab. Xaiv Cov Kab. Ua kom lub ntsej muag grey.
• Ntawm Linetype tab, nce kab dav rau 4 pt.

Txhawm rau ua kom pom daim duab zoo dua, kos cov kab ntsug nyias los ntawm cov cim. Txhawm rau ua qhov no, hauv daim ntawv teev npe hauv lub kaum sab laug sab saud, xaiv "Yuam kev tuav raws li Y axis". Lub dialog box qhia txog cov kab yuam kev ntsug. Xaiv cov kev taw qhia rho tawm, kawg Style → Tsis muaj taw tes, yuam kev → Tus nqi txheeb ze, sau 100% hauv daim teb no. Xaiv cov xim thiab thickness ntawm cov kab koj tus kheej.

Kauj ruam 6. Ntxiv tus nqi npaj rau daim ntawv

Nws yog lub sijhawm los ntxiv cov ntaub ntawv rau hauv daim ntawv qhia uas qhia txog feem pua ntawm cov haujlwm ua tiav.

Yog tias ib txoj haujlwm siv sijhawm 10 hnub thiab ua tiav 50%, qhov bar rau kev ua tiav yuav yog ib nrab puv. Qhov no yog ib qho kev ntsuas rau kev taug qab ua haujlwm thiab tsis suav nrog hnub so thiab hnub so. Txhawm rau ntxiv cov ntaub ntawv tshiab rau daim duab, ua raws li cov lus qhia hauv qab no.

• Txoj cai-nias ntawm daim ntawv qhia cheeb tsam, xaiv "Xaiv cov ntaub ntawv" los ntawm cov ntawv qhia zaub mov.
• Nyem lub "Ntxiv" khawm, nyob rau hauv lub qhov rais uas tshwm, txhais lub "Law npe" los ntawm xaiv cell G30. Cov ntaub ntawv ntau rau X thiab Y axes yog tib yam li hauv kauj ruam # 3.
• Cov cim liab yuav tshwm rau ntawm daim duab, uas yuav zais cov xim ntsuab.

Kauj ruam 7. Formatting cov ntaub ntawv tshiab metrics

Txhawm rau ua kom daim duab qhia txog tib yam, thiab cov ntaub ntawv tsis sib tshooj, peb tsim cov cim:

• Nyob rau hauv lub drop-down daim ntawv teev nyob rau hauv sab sauv ces kaum, xaiv "Ready Row". Tam sim ntawd hauv qab daim ntawv teev npe muaj ib lub pob "Hom ntawv ntawm cov seem uas tau xaiv". Nyem rau nws, lub dialog box yuav qhib.
• Hloov cov cim cim. Xaiv hom "Crystal" hom, teeb qhov loj me rau 10 pt, thiab xaiv cov khoom ntsuab sau rau lub cim. Kaw lub dialog.
• Yog tias tsis tshem cov kev xaiv ntawm cov cim, hauv cov ntawv qhia zaub mov tseem ceeb ntawm "Layout" tab, nyem lub pob "Data labels", xaiv cov khoom "Tsis txhob qhia" hauv cov ntawv qhia zaub mov uas tshwm.

Kauj Ruam 8. Ntxiv cov kab yuam kev txhawm rau taug qab qhov feem pua ntawm kev ua tiav cov haujlwm

Tam sim no koj yuav tsum tau ntxiv cov phiaj xwm tshiab kom koj tuaj yeem pom ntawm ib nrais muag seb cov haujlwm nyob rau theem twg.

• Xaiv Kab Npaj dua los ntawm daim ntawv teev npe ntawm sab laug. Nias lub pob " yuam kev bars" nyob rau hauv lub ntsiab zaub mov. Xaiv "Advanced Options" los ntawm cov ntawv qhia zaub mov nco-down. Ib lub dialog box yuav qhib. Teem cov slats ntsug mus rau tus nqi ruaj khov los ntawm kev nkag mus rau xoom hauv qhov chaw sib thooj.
• Hloov mus rau qhov tsis sib xws ntawm cov phiaj xwm sib npaug mus rau X-axis, Teem cov kev taw qhia rau "Plus". Xaus Style → Tsis muaj taw tes, Qhov yuam kev → Custom. Nyob rau hauv lub Positive Error Value dialog box, qhia qhov ntau ntawm cov hlwb G33-G47.
• Hloov cov xim ntawm cov kab mus rau ntsuab los ntawm xaiv cov khoom sau. Teem kab dav rau 7 pt.

Kab rov tav ntsuab qhia seb cov haujlwm tau ua tiav ib feem lossis tag nrho.

Kauj ruam 9. Ntxiv ib txoj kab ntsug sawv cev rau hnub tam sim no

Txoj kab liab qhia tias hnub twg yog hnub twg, thiab ua kom pom tseeb tias qhov project tag nrho haum rau hnub kawg.

• Hu rau cov ntawv qhia zaub mov ntsiab lus rau daim duab nrog lub khawm sab xis. Xaiv "Xaiv cov ntaub ntawv".
• Nyem "Ntxiv" khawm, sau lo lus "Hnub no" hauv "Series npe" teb. Nkag mus rau qhov ntau ntawm cov hlwb C31-C32 rau X-axis tus nqi thiab H31-H32 rau tus nqi Y-axis.

Ob lub cim tshiab tau tshwm sim ntawm daim duab. Sau npe rau lawv: xaiv cov ntawv sau cov ntaub ntawv thiab sau lo lus "Hnub no" nyob rau hauv lub formula bar. Txoj cai-nias ntawm qhov kos npe thiab xaiv cov khoom "Cov Ntaub Ntawv Hom" hauv cov ntawv qhia zaub mov. Muab qhov chaw ntawm lub npe "Saib saum toj".

• Mus rau lub ntsiab zaub mov ntawm lub "Layout" tab.
• Xaiv "Hnub No Kab" nyob rau hauv daim ntawv teev npe nyob rau sab laug ces kaum.
• Ntxiv cov kab yuam kev nrog cov kev txwv ntxiv raws li cov lus qhia raws li hauv qib tsib. Koj yuav xav tau ib lub plank raws txoj kab ntsug, nrog rau qhov kev taw qhia Minus, qhov kawg style ntawm Tsis muaj taw tes, nrog tus nqi txheeb ze ntawm 100%.

Tam sim no koj muaj kab ntsug sawv cev rau hnub tam sim no. Hloov cov xim ntawm kab mus rau liab, nce qhov loj rau 3 pt.

Hauv cov qauv, hnub tim tam sim no raug kho, yog li nws yuav tsis hloov. Tab sis rau tej yaam num uas nquag tsim, kev sib txawv ntawm kev sau cov ntaub ntawv raug pom zoo. Txhawm rau ua qhov no, muab hnub tim rau hauv cell B2 los ntawm kev nkag mus rau lub mis = TODAY () thiab qhia rau hauv cov ntaub ntawv xaiv dialog box rau tus nqi ntawm X axis cell B2 es tsis txhob ntawm qhov ntau yam uas koj tau teev ua ntej.

Kauj ruam 10. Ntxiv cov ntawv thaiv

Tam sim no daim duab tsis tshua muaj cov lus dab neeg rau cov lus dab neeg kom nws tuaj yeem nkag siab los ntawm tag nrho pab neeg.

• Xaiv daim duab, mus rau cov ntawv qhia zaub mov tseem ceeb ntawm "Insert" tab. Xaiv "Lettering".
• Muab qhov chaw daim ntawv lo rau ntawm lub ces kaum sab xis ntawm daim ntawv. Xaiv ciam teb ntawm lub thawv ntawv nrog tus nas khawm sab xis, xaiv cov khoom "Format Shape" hauv cov ntawv qhia zaub mov. Nyob rau hauv lub qhov rais uas qhib, xaiv lub sau hom "Solid", xim yog grey. Format daim ntawv lo. Koj tuaj yeem ntxig ib tus lej ntawm cov blocks.

Tam sim no koj tuaj yeem ua daim duab hauv 20-30 feeb siv tsuas yog koj nyiam editor MS Excel.