Haisi chakavanzika kuti vazhinji vashandisi veExcel, pavanenge vachigadzira matafura pamapepa, funga kutanga nezve yavo yekunyaradza uye nyore. Aya ndiwo maitiro akanaka, ane mavara uye anorema matafura ane "misoro" yakaoma anozvarwa, ayo, panguva imwe chete, haigone kusefa kana kurongeka zvachose, uye zviri nani kusafunga nezve otomatiki rondedzero ine pivot tafura zvachose.
Nenguva isipi, mushandisi wetafura yakadaro anosvika pakugumisa kuti "inogona kunge isina kunaka, asi inogona kushanda" uye inotanga kurerutsa dhizaini yetafura yake, ichiita kuti ienderane nemazano echinyakare:
- Musoro wemutsara wakapfava, uko koromo yega yega ichange iine zita rayo rakasiyana (zita remunda)
- mutsara mumwe - imwe yakapera kushanda (dhiri, kutengesa, kutumira, chirongwa, nezvimwewo)
- hapana maseru akabatanidzwa
- pasina kuputsika muchimiro chemitsara isina chinhu uye makoramu
Asi kana iwe ukagadzira mutsara wemusoro kubva kune-multi-level kana kupatsanura imwe koramu kuita akati wandei, zviri nyore, saka kuvakwazve kwetafura kunogona kutora nguva yakawanda (kunyanya pahukuru hukuru). Zvinoreva mamiriro anotevera:
| Of |  | do |  |
Mukutaura kwezvinyorwa, tafura yakarurama inowanzonzi flat (flat) - zvinoenderana nematafura akadaro kuti zvakanakisisa kuvaka mishumo yepivot tables (pivot tables) uye kuita analytics.
Iwe unogona kushandura tafura-maviri-dimensional tafura yakafuratira uchishandisa yakapusa macro. Vhura iyo Visual Basic Mharidzo kuburikidza neiyo tab Mugadziri - Visual Basic (Mugadziri - Visual Basic Edhita) kana nzira yekudimbudzira keyboard alt+F11. Isa module itsva (Pinza – Module) uye kopira zvinyorwa zveiyi macro ipapo:
Sub Redesigner() Dim i As Long Dim hc As Integer, hr As Integer Dim ns As Worksheet hr = InputBox("Сколько строк с подписями сверху?") hc = InputBox("Сколько столбико столбцов столбасов столбега) Application Nhema i = 1 Set inpdata = Selection Set ns = Worksheets.Add For r = (hr + 1) To inpdata.Rows.Count For c = (hc + 1) To inpdata.Columns.Count For j = 1 Ku hc ns. Masero(i, j) = inpdata.Masero(r, j) Next j For k = 1 Kusvika hr ns.Masero(i, j + k - 1) = inpdata.Masero(k, c) Next k ns.Masero( i, j + k - 1) = inpdata.Masero(r, c) i = ini + 1 Next c Next r End Sub Unogona kuvhara VBA mupepeti wodzokera kuExcel. Iye zvino tinogona kusarudza tafura yepakutanga (zvakazara, ine musoro uye koramu yekutanga nemwedzi) uye tomhanyisa macro yedu kuburikidza. Mugadziri - Macros (Mugadziri - Macros) kana kudzvanya musanganiswa alt+F8.
Iyo macro ichaisa pepa nyowani mubhuku uye kugadzira nyowani, yakagadziridzwa vhezheni yetafura yakasarudzwa pairi. Iwe unogona kushanda netafura yakadaro "yakazara", uchishandisa arsenal yose yeExcel zvishandiso zvekugadzirisa uye kuongorora mazita makuru.
- Chii macros, kupi kwekuisa macro kodhi muVBA, maitiro ekuishandisa
- Kugadzira Mishumo nePivotTables
- Chishandiso chekugadzirisa patsva matafura eXNUMXD kune akafuratira kubva kuPLEX yekuwedzera