txheem
Thaum hais txog databases (DB), thawj yam uas los rau hauv lub siab, tau kawg, yog txhua yam buzzwords xws li SQL, Oracle, 1C, lossis tsawg kawg Access. Tau kawg, cov no muaj zog heev (thiab kim heev rau feem ntau) cov kev pabcuam uas tuaj yeem ua haujlwm ntawm lub tuam txhab loj thiab nyuaj nrog ntau cov ntaub ntawv. Qhov teeb meem yog tias qee zaum xws li lub zog tsuas yog tsis xav tau. Koj lub lag luam yuav me me thiab nrog cov txheej txheem kev lag luam yooj yim, tab sis koj kuj xav ua kom nws siv tau. Thiab nws yog rau cov tuam txhab me me uas qhov no feem ntau yog qhov muaj sia nyob.
Pib nrog, cia peb tsim TOR. Nyob rau hauv feem ntau, ib tug database rau accounting, piv txwv li, classic muag yuav tsum tau:
- kom nyob rau hauv cov lus qhia ntawm cov khoom (nqi), ua tiav kev lag luam thiab cov neeg siv khoom thiab txuas cov ntxhuav no rau ib leeg
- muaj kev nplij siab cov ntaub ntawv nkag cov ntaub ntawv (nrog cov npe drop-down, thiab lwm yam)
- cia li sau ib co ntaub ntawv cov ntawv luam tawm (kev them nyiaj, nqi, thiab lwm yam)
- muab qhov tsim nyog lus ceeb toom los tswj tag nrho cov txheej txheem kev lag luam los ntawm qhov pom ntawm tus thawj tswj hwm
Microsoft Excel tuaj yeem ua txhua yam ntawm no nrog kev siv zog me ntsis. Cia peb sim ua qhov no.
Kauj ruam 1. Thawj cov ntaub ntawv nyob rau hauv daim ntawv ntawm cov ntxhuav
Peb yuav khaws cov ntaub ntawv hais txog cov khoom lag luam, kev muag khoom thiab cov neeg siv khoom hauv peb lub rooj (ntawm tib daim ntawv lossis ntawm lwm qhov sib txawv - nws tsis muaj teeb meem). Nws yog ib qho tseem ceeb uas yuav tsum tau tig lawv mus rau hauv "ntse ntxhuav" nrog nws pib-loj, yog li tsis txhob xav txog nws yav tom ntej. Qhov no ua tiav nrog cov lus txib Format ua ib lub rooj tab Tsev (Lub Tsev — Format as Table). Ntawm lub tab uas ces tshwm constructor (Tsim) muab cov lus piav qhia cov npe hauv daim teb Lub npe rau kev siv tom qab:
Nyob rau hauv tag nrho, peb yuav tsum tau txais peb "smart tables":
Thov nco ntsoov tias cov rooj yuav muaj cov ntaub ntawv qhia meej ntxiv. Yog li, piv txwv li, peb nqemuaj cov ntaub ntawv ntxiv txog pawg (cov khoom lag luam, ntim, qhov hnyav, thiab lwm yam) ntawm txhua yam khoom, thiab lub rooj Client - lub nroog thiab cheeb tsam (chaw nyob, TIN, cov ntsiab lus hauv txhab nyiaj, thiab lwm yam) ntawm lawv txhua tus.
rooj muag yuav siv los ntawm peb tom qab nkag mus ua tiav kev hloov pauv rau hauv nws.
Kauj ruam 2. Tsim ib daim ntawv sau npe
Tau kawg, koj tuaj yeem sau cov ntaub ntawv muag ncaj qha rau hauv lub rooj ntsuab muag, tab sis qhov no tsis yog ib txwm yooj yim thiab entails cov tsos ntawm qhov yuam kev thiab typos vim "tib neeg yam". Yog li ntawd, nws yuav zoo dua los ua ib daim ntawv tshwj xeeb rau kev nkag mus rau cov ntaub ntawv ntawm ib daim ntawv cais ntawm qee yam zoo li no:
Hauv cell B3, kom tau txais qhov hloov tshiab hnub tim-lub sij hawm, siv cov haujlwm Lub TDATA (Tam sim no). Yog tias tsis xav tau sijhawm, ces hloov Lub TDATA tuaj yeem siv tau HNUB NO (Hnub no).
Hauv cell B11, nrhiav tus nqi ntawm cov khoom xaiv hauv kab thib peb ntawm lub rooj ntse nqe siv lub luag haujlwm VPR (VLOOKUP). Yog koj tseem tsis tau ntsib dua ua ntej koj nyeem thiab saib daim video no.
Hauv cell B7, peb xav tau ib daim ntawv teev npe nrog cov khoom los ntawm daim ntawv teev nqi. Rau qhov no koj tuaj yeem siv cov lus txib Cov ntaub ntawv - Data validation (Cov ntaub ntawv - validation), qhia raws li kev txwv daim ntawv teev (Npe) thiab ces nkag mus rau hauv lub teb Tau qhov twg los (Tau qhov twg los) txuas rau kab lub npe los ntawm peb lub rooj ntse nqe:
Ib yam li ntawd, ib daim ntawv teev npe nrog cov neeg siv khoom raug tsim, tab sis qhov chaw yuav nqaim dua:
=INDIRECT("Cov neeg siv khoom[Client]")
muaj nuj nqi TSEEM CEEB (NTSEEG) yog xav tau, qhov no, vim Excel, hmoov tsis, tsis nkag siab ncaj qha mus rau cov ntxhuav ntse hauv qhov chaw. Tab sis tib qhov txuas "pob" hauv kev ua haujlwm TSEEM CEEB Nyob rau tib lub sijhawm, nws ua haujlwm nrog bang (ntau ntxiv txog qhov no yog nyob rau hauv tsab xov xwm hais txog kev tsim cov npe poob nrog cov ntsiab lus).
Kauj ruam 3. Ntxiv ib qho kev muag khoom nkag macro
Tom qab ua tiav daim ntawv, koj yuav tsum ntxiv cov ntaub ntawv nkag mus rau hauv nws mus rau qhov kawg ntawm lub rooj muag. Siv cov kev sib txuas yooj yim, peb yuav tsim ib txoj kab ntxiv rau hauv qab daim ntawv:
Cov. cell A20 yuav muaj qhov txuas rau = B3, cell B20 yuav muaj qhov txuas rau = B7, thiab lwm yam.
Tam sim no cia peb ntxiv 2-kab theem pib macro uas theej cov hlua tsim thiab ntxiv rau hauv Lub Rooj Muag Khoom. Txhawm rau ua qhov no, nias qhov sib xyaw ua ke Alt + F11 los yog khawm Visual Basic tab tsim tawm (Tus tsim tawm). Yog tias qhov tab no tsis pom, ces qhib nws ua ntej hauv qhov chaw Cov ntaub ntawv – Kev xaiv – Ribbon teeb (Cov ntaub ntawv - Kev xaiv - Customize Ribbon). Hauv Visual Basic editor qhov rais uas qhib, ntxig ib qho tshiab khoob module los ntawm cov ntawv qhia zaub mov Ntxig - Module thiab sau peb macro code muaj:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy the data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . Kab 'txiav txim siab tus lej ntawm kab kawg hauv lub rooj. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'paste rau hauv kab tom ntej khoob Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'clear end sub form Tam sim no peb tuaj yeem ntxiv ib lub pob rau peb daim ntawv los khiav cov tsim macro siv cov npe dropdown Insert tab tsim tawm (Tus tsim tawm - Ntxig - Khawm):
Tom qab koj kos nws, tuav lub pob laug nas, Excel yuav nug koj seb koj xav tau dab tsi rau nws - xaiv peb cov macro Add_Sell. Koj tuaj yeem hloov cov ntawv ntawm ib lub pob los ntawm txoj cai-nias ntawm nws thiab xaiv cov lus txib Hloov cov ntawv.
Tam sim no, tom qab ua tiav daim ntawv, koj tuaj yeem nyem rau ntawm peb lub pob, thiab cov ntaub ntawv nkag mus yuav raug muab tso rau hauv lub rooj muag, thiab tom qab ntawd daim ntawv raug tshem tawm kom nkag mus rau qhov kev cog lus tshiab.
Kauj Ruam 4 Txuas Cov Kab
Ua ntej tsim daim ntawv tshaj tawm, cia peb txuas peb cov ntxhuav ua ke kom tom qab ntawd peb tuaj yeem suav sai sai rau kev muag khoom los ntawm cheeb tsam, cov neeg siv khoom, lossis qeb. Nyob rau hauv cov laus versions ntawm Excel, qhov no yuav tsum tau siv ntau lub zog. VPR (VLOOKUP) rau kev hloov cov nqi, pawg, cov neeg siv khoom, lub nroog, thiab lwm yam rau lub rooj muag. Qhov no yuav tsum tau siv sijhawm thiab siv zog los ntawm peb, thiab tseem "noj" ntau ntawm Excel cov peev txheej. Pib nrog Excel 2013, txhua yam tuaj yeem siv tau yooj yim dua los ntawm kev teeb tsa kev sib raug zoo ntawm cov rooj.
Txhawm rau ua qhov no, ntawm lub tab Cov ntaub ntawv (Hnub) nias Txheeb Ze (Kev sib raug zoo). Hauv qhov rai uas tshwm, nyem lub pob tsim (tshiab) thiab xaiv los ntawm lub drop-down teev cov ntxhuav thiab kab npe uas lawv yuav tsum muaj feem xyuam nrog:
Lub ntsiab lus tseem ceeb: cov ntxhuav yuav tsum tau teev nyob rau hauv qhov kev txiav txim no, piv txwv li cov lus txuas (nqe) yuav tsum tsis muaj nyob rau hauv kab tseem ceeb (lub npe) duplicate cov khoom, raws li nws tshwm sim nyob rau hauv lub rooj muag. Hauv lwm lo lus, lub rooj sib txuas yuav tsum yog ib qho uas koj yuav tshawb nrhiav cov ntaub ntawv siv VPRyog tias nws tau siv.
Tau kawg, lub rooj sib txuas ua ke zoo ib yam muag nrog rooj Client los ntawm kab ke Cov neeg yuav khoom:
Tom qab teeb tsa cov kev sib txuas, lub qhov rais rau kev tswj cov kev sib txuas tuaj yeem raug kaw; koj tsis tas yuav rov ua cov txheej txheem no.
Kauj Ruam 5. Peb tsim cov ntawv qhia siv cov ntsiab lus
Tam sim no, txhawm rau txheeb xyuas kev muag khoom thiab taug qab qhov kev hloov pauv ntawm cov txheej txheem, cia peb tsim, piv txwv li, qee yam kev tshaj tawm siv lub rooj pivot. Teem lub active cell rau lub rooj muag thiab xaiv lub tab ntawm lub ribbon Insert – PivotTable (Insert — Pivot Table). Hauv qhov rai uas qhib, Excel yuav nug peb txog cov ntaub ntawv (piv txwv li rooj muag) thiab ib qho chaw xa daim ntawv tshaj tawm (zoo dua rau ntawm daim ntawv tshiab):
Lub ntsiab lus tseem ceeb yog tias nws yuav tsum ua kom lub checkbox Ntxiv cov ntaub ntawv no rau cov qauv ntaub ntawv (Ntxiv cov ntaub ntawv rau Data Model) nyob rau hauv qab ntawm lub qhov rais kom Excel nkag siab tias peb xav tsim ib daim ntawv qhia tsis tau tsuas yog nyob rau hauv lub rooj tam sim no, tab sis kuj siv tag nrho cov kev sib raug zoo.
Tom qab txhaj rau OK ib lub vaj huam sib luag yuav tshwm nyob rau sab xis ib nrab ntawm lub qhov rais Pivot rooj tebqhov twg nyem qhov txuas Tag nrhopom tsis tau tsuas yog tam sim no, tab sis tag nrho cov "smart tables" uas nyob rau hauv phau ntawv ib zaug. Thiab tom qab ntawd, ib yam li hauv cov lus pivot classic, koj tuaj yeem rub cov teb peb xav tau los ntawm cov rooj sib cuam tshuam rau hauv cheeb tsam. Lim, Loj, Stolbtsov or qhov tseem ceeb - thiab Excel yuav tam sim tsim ib daim ntawv qhia peb xav tau ntawm daim ntawv:
Tsis txhob hnov qab tias cov lus pivot yuav tsum tau hloov kho tsis tu ncua (thaum cov ntaub ntawv hloov pauv) los ntawm txoj cai-nias ntawm nws thiab xaiv cov lus txib Hloov tshiab & Txuag (Refresh), vim nws tsis tuaj yeem ua nws tau.
Tsis tas li ntawd, los ntawm kev xaiv ib lub xov tooj ntawm tes hauv cov ntsiab lus thiab nias lub pob Pivot Daim Ntawv (Pivot Chart) tab Analysis (Kev tsom xam) or tsis (Kev xaiv) koj tuaj yeem pom sai sai pom cov txiaj ntsig suav hauv nws.
Kauj Ruam 6. Sau cov ntawv luam tawm
Lwm txoj haujlwm tseem ceeb ntawm txhua qhov chaw khaws ntaub ntawv yog qhov tsis siv neeg sau ntawm ntau cov ntawv luam tawm thiab cov ntaub ntawv (invoices, invoices, acts, thiab lwm yam). Kuv twb tau sau txog ib txoj hauv kev los ua qhov no. Ntawm no peb siv, piv txwv li, sau daim foos los ntawm tus lej account:
Nws yog xav tias hauv cell C2 tus neeg siv yuav nkag mus rau tus lej (kab tus lej hauv lub rooj muag, qhov tseeb), thiab tom qab ntawd cov ntaub ntawv peb xav tau yog rub los siv cov haujlwm uas twb paub lawm VPR (VLOOKUP) thiab nta INDEX (INDEX).
- Yuav ua li cas siv VLOOKUP muaj nuj nqi los saib thiab saib cov txiaj ntsig
- Yuav hloov VLOOKUP li cas nrog INDEX thiab MATCH ua haujlwm
- Tsis siv neeg sau cov ntawv thiab cov ntawv nrog cov ntaub ntawv los ntawm lub rooj
- Tsim Cov Ntawv Qhia nrog PivotTables