Akuyona imfihlo ukuthi abasebenzisi abaningi be-Excel, lapho benza amatafula emashidini, bacabanga kuqala ngokunethezeka kwabo kanye nokunethezeka kwabo. Yile ndlela amatafula amahle, anemibala futhi anzima azalwa ngayo "izihloko" eziyinkimbinkimbi, lapho, ngesikhathi esifanayo, angeke ahlungwe noma ahlungwe nhlobo, futhi kungcono ukungacabangi ngombiko ozenzakalelayo onetafula le-pivot nhlobo.
Ngokushesha noma kamuva, umsebenzisi wetafula elinjalo ufika esiphethweni sokuthi "kungase kungabi kuhle kangako, kodwa kungasebenza" futhi iqala ukwenza lula ukuklama kwetafula lakhe, ihambisane nezincomo zakudala:
- unhlokweni olula womugqa owodwa, lapho ikholomu ngayinye izoba negama layo elihlukile (igama lenkundla)
- umugqa owodwa - umsebenzi owodwa oqediwe (idili, ukuthengisa, ukuthumela, iphrojekthi, njll.)
- awekho amaseli ahlanganisiwe
- ngaphandle kwamakhefu ngendlela yemigqa namakholomu angenalutho
Kodwa uma wenza unhlokweni womugqa owodwa ngaphandle kwamazinga amaningi noma uhlukanisa ikholomu eyodwa ibe amaningana, kulula kakhulu, khona-ke ukwakhiwa kabusha kwetafula kungathatha isikhathi esiningi (ikakhulukazi kumasayizi amakhulu). Kusho isimo esilandelayo:
| Of |  | do |  |
Ngokwemininingwane yolwazi, ithebula elifanele livame ukubizwa ngokuthi flat (flat) - ngokuya ngamathebula anjalo ukuthi kungcono kakhulu ukwakha imibiko yamathebula e-pivot (amatafula e-pivot) futhi uqhube izibalo.
Ungakwazi ukuguqula itafula elinezinhlangothi ezimbili libe itafula eliyisicaba usebenzisa i-macro elula. Vula i-Visual Basic Editor ngethebhu Unjiniyela - I-Visual Basic (Unjiniyela — Isihleli Esiyisisekelo Esibonakalayo) noma isinqamuleli sekhibhodi Alt+F11. Faka imojuli entsha (Faka – Imojula) bese ukopisha umbhalo wale macro lapho:
I-Sub Redesigner() Dim i As Long Dim hc As Integer, hr As Integer Dim ns As As Worksheet hr = InputBox("I-InputBox("I-Сколько строк с подписями сверху?") hc =I-InputBox("Сколько столбцов песни) Isicelo ==Uhlelo lokusebenza? Amanga i = 1 Setha i-inpdata = Isethi Yokukhetha ns = Amakhasi Okusebenzela.Engeza Kwe-r = (hr + 1) Ukuze ufake idatha.Imigqa.Bala Ukuze c = (hc + 1) Ku-inpdata.Amakholomu.Bala Ngokuthi j = 1 Ku-hc ns. Amaseli(i, j) = inpdata.Amaseli(r, j) Okulandelayo j Ku-k = 1 Kuya ku-hr ns.Amaseli(i, j + k - 1) = inpdata.Amaseli(k, c) Okulandelayo k ns.Amaseli( i, j + k - 1) = inpdata.Amaseli(r, c) i = i + 1 Okulandelayo c Okulandelayo r End Sub Ungakwazi ke ukuvala umhleli we-VBA bese ubuyela ku-Excel. Manje sesingakwazi ukukhetha ithebula langempela (ngokuphelele, elinenhlokweni kanye nekholomu yokuqala enezinyanga) futhi sisebenzise i-macro yethu Unjiniyela - Macros (Unjiniyela - Amamakhro) noma inhlanganisela yokucindezela Alt+F8.
Imakhro izofaka ishidi elisha encwadini bese idala inguqulo entsha, eyakhiwe kabusha yethebula elikhethiwe kuyo. Ungasebenza ngetafula elinjalo "ngokugcwele", usebenzisa yonke i-arsenal yamathuluzi e-Excel ukucubungula nokuhlaziya izinhlu ezinkulu.
- Ayini ama-macros, lapho ungafaka khona ikhodi ye-macro ku-VBA, ungayisebenzisa kanjani
- Ukudala Imibiko ngama-PivotTables
- Ithuluzi lokuklama kabusha amatafula angu-XNUMXD abe ayisicaba asuka kusengezo se-PLEX