tirotiro
Te whakatakoto i te raruraru
He awhe o nga pūtau me nga raraunga kei roto nga pūtau putua:
Ko te mahi he tango i nga pūtau kau, ka waiho ko nga pūtau me nga korero.
Tikanga 1. He taratara me te tere
- Te whiriwhiri i te awhe taketake
- Patohia te ki F5, patene muri Miramira (Motuhatu). I te matapihi e tuwhera ana, tohua Pūtau Putua(Patea) a pāwhiri OK.
Kua tohua nga pūtau putua katoa o te awhe.
- Ka hoatu he whakahau ki te tahua ki te muku i nga pūtau kua tohua: pawhiri-matau- Muku pūtau (Muku Pūtau) me te neke whakarunga.
Tikanga 2: Tātai Huanga
Hei whakangawari ake, me whakaingoa a maatau awhe mahi ma te whakamahi Kaiwhakahaere Ingoa (Kaiwhakahaere Ingoa) ripa Tauira (Tairangi) ranei, i roto i te Excel 2003 me te pakeke ake, te tahua Kōkuhu – Ingoa – Tautapa (Kōkuhu — Ingoa — Tautuhia)
Whakaingoatia te awhe B3:B10 Kua Putua, awhe D3:D10 – Korekau. Me tino rite te rahi o nga awhe, ka taea te whakanoho ki nga waahi katoa e pa ana ki a raatau.
Tīpakohia te pūtau tuatahi o te awhe tuarua (D3) ka tāuruhia tēnei tātai whakamataku ki roto:
=IF(ROW() -ROW(NoPutua)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty) + ROWS(Kei te Putea))); LINE()-ROW(Kare he Putua)+1); COLUMN(He Putea); 4)))
I roto i te putanga Ingarihi ka penei:
=IF(ROW()-ROW(NoPutua)+1>ROWS(Votua)-COUNTBLANK(Votua),””,AROHI(WAHIRANGA(PITI((I(I(Whatu<>“”,) ROW(Putea), ROW() +ROWS(Kua Putua))),ROW()-ROW(KoreKoha)+1), COlumN(Kua Putua),4)))
I tua atu, me whakaurua hei tauira huinga, ara, pehi i muri i te whakapiri E tomo (rite tikanga) me Ctrl + Shift + Tomo. Inaianei ka taea te kape i te tauira ma te whakaoti aunoa (toia te ripeka pango ki te kokonga matau o raro o te pūtau) - ka whiwhi tatou i te awhe taketake, engari karekau he pūtau kau:
Tikanga 3. Mahi ritenga i roto i te VBA
Mena he whakapae he maha nga wa me whakahoki ano e koe te tikanga mo te tango i nga pūtau kau mai i nga awhe, he pai ake te taapiri i a koe ake mahi mo te tango i nga pūtau kau ki te huinga paerewa kotahi, ka whakamahi i nga keehi katoa ka whai ake.
Hei mahi i tenei, whakatuwheratia te Visual Basic Editor (ALT + F11), whakauruhia he kōwae putua hou (tahua Kōkuhu – Kōwae) ka kape i te tuhinga o tenei mahi ki reira:
Taumahi NoBlanks(Raraunga Raraunga Hei Awhe) Hei Rerekē() Ataahua N Hei Roa Moama N2 Hei Roa Moama Rng Hei Awhe Momo Puutau Maamaa Ko te Hua Pouri Roa () Hei Rerekē Rereke R He Roa Pouri C He Roa MaxCells = Application.WorksheetFunction.Max( _ Taupānga.Caller.Cells.Count, RaraungaRaraunga.Cells.Count) ReDim Result(1 Ki MaxCells, 1 Ki te 1) Mo Ia Rng I Roto Raraunga.Cells Mena Rng.Value <> vbNullString Na N = N + 1 Hua(N, 1 ) = Rng.Value Mutunga Mēna Rng Panuku Mo N2 = N + 1 Ki MaxCells Hua(N2, 1) = vbNullString Panuku N2 Mena Application.Caller.Rows.Count = 1 Na NoBlanks = Taupānga.Transpose(Hua) Atu NoBlanks = Hua Whakamutua Ki te Whakamutu Mahi
Kaua e wareware ki te tiaki i te konae ka huri whakamuri mai i te Visual Basic Editor ki Excel. Hei whakamahi i tenei mahi i roto i ta maatau tauira:
- Tīpakohia te awhe rawaka o nga pūtau putua, hei tauira F3:F10.
- Haere ki te tahua Kōkuhu – Mahi (Kōkuhu — Taumahi)paatohia ranei te paatene Kōkuhu mahi (Kōkuhu Taumahi) ripa Tauira (Tairangi) i roto i nga putanga hou o Excel. I roto i te kāwai Kua tautuhia te Kaiwhakamahi (Tautuhi Kaiwhakamahi) whiriwhiria ta maatau mahi NoPatea.
- Tauwhāitihia te awhe pūtake whai korekore (B3:B10) hei tautohe mahi ka pēhi Ctrl + Shift + Tomoki te whakauru i te mahi hei tauira huānga.
:
- Te muku i nga rarangi putua katoa i te ripanga i te wa kotahi me te tonotono ngawari
- Te tango i nga rarangi putu katoa i roto i te raumahi i te wa kotahi ma te whakamahi i te taapiri PLEX
- Kia tere te whakakī i ngā pūtau kau katoa
- He aha nga tonotono, kei hea te whakauru waehere tonotono ki te VBA