Nws tsis pub leejtwg paub tias cov neeg siv Excel feem ntau, thaum tsim cov ntxhuav ntawm nplooj ntawv, xav ua ntej ntawm lawv tus kheej kev nplij siab thiab yooj yim. Qhov no yog qhov zoo nkauj, muaj yeeb yuj thiab cumbersome cov rooj nrog complex "headers" tau yug los, uas, tib lub sijhawm, tsis tuaj yeem lim lossis txheeb xyuas tag nrho, thiab nws yog qhov zoo dua tsis txhob xav txog cov ntawv qhia tsis siv neeg nrog lub rooj pivot txhua.
Tsis ntev los sis tom qab, tus neeg siv ntawm lub rooj no los txog qhov xaus tias "nws yuav tsis zoo nkauj heev, tab sis nws tuaj yeem ua haujlwm" thiab pib ua kom yooj yim rau kev tsim nws lub rooj, coj nws mus rau hauv txoj kab nrog cov lus pom zoo classic:
- ib qho yooj yim ib-kab header, qhov twg txhua kab yuav muaj nws tus kheej lub npe (lub npe teb)
- ib kab - ib qho kev ua tiav (deal, muag, tshaj tawm, project, thiab lwm yam)
- tsis muaj cell sib koom ua ke
- tsis so nyob rau hauv daim ntawv ntawm khoob kab thiab kab
Tab sis yog tias koj ua ib lub taub hau ib kab tawm ntawm ntau theem ib lossis faib ib kem rau hauv ob peb, nws yooj yim heev, ces lub rooj rov tsim dua tuaj yeem siv sijhawm ntau (tshwj xeeb ntawm qhov loj me). Nws txhais tau tias cov xwm txheej hauv qab no:
| Of |  | do |  |
Nyob rau hauv cov nqe lus ntawm databases, lub rooj zoo feem ntau yog hu ua tiaj tus (flat) - nws yog raws li cov ntxhuav uas nws yog qhov zoo tshaj plaws los tsim cov lus ceeb toom ntawm cov ntxhuav pivot (cov lus pivot) thiab ua analytics.
Koj tuaj yeem hloov lub rooj ob sab mus rau lub rooj tiaj tus siv lub macro yooj yim. Qhib Visual Basic Editor ntawm lub tab Tus tsim tawm - Visual Basic (Tus tsim tawm - Visual Basic Editor) los yog keyboard shortcut Alt+F11. Ntxig ib qho tshiab module (Ntxig - Module) thiab luam cov ntawv ntawm no macro muaj:
Sub Redesigner() Dim i As Long Dim hc As Integer, hr As Integer Dim ns As Worksheet hr = InputBox("Сколько строк с подписями сверху?") hc = InputBox("о пколькоца сто? creenUpdating = False i = 1 Teem inpdata = Xaiv Teeb ns = Worksheets.Add For r = (hr + 1) To inpdata.Rows.Count For c = (hc + 1) To inpdata.Columns.Count For j = 1 To hc ns. Cells(i,j) = inpdata.Cells(r,j) Next j For k = 1 To hr ns.Cells(i, j + k - 1) = inpdata.Cells(k, c) Next k ns.Cells( i, j + k - 1) = inpdata.Cells(r, c) i = i + 1 Next c Next r End Sub Koj tuaj yeem kaw VBA editor thiab rov qab mus rau Excel. Tam sim no peb tuaj yeem xaiv cov lus qub (ua tiav, nrog lub taub hau thiab thawj kab nrog lub hli) thiab khiav peb cov macro los ntawm Tus tsim tawm - Macros (Tus tsim tawm - Macros) los yog nias ua ke Alt+F8.
Lub macro yuav ntxig ib daim ntawv tshiab rau hauv phau ntawv thiab tsim ib qho tshiab, reconstructed version ntawm lub rooj xaiv ntawm nws. Koj tuaj yeem ua haujlwm nrog cov lus zoo li no "tag nrho", siv tag nrho cov arsenal ntawm Excel cov cuab yeej rau kev ua thiab tshuaj xyuas cov npe loj.
- Dab tsi yog macro, qhov twg los ntxig macro code hauv VBA, siv lawv li cas
- Tsim Cov Ntawv Qhia nrog PivotTables
- Cov cuab yeej los kho dua tshiab XNUMXD cov rooj rau cov tiaj tus los ntawm PLEX add-on