Contents
Hoʻokumu i ka pilikia
Loaʻa iā mākou ka laulā o nā cell me ka ʻikepili i loaʻa nā cell blank:
ʻO ka hana, ʻo ia ka wehe ʻana i nā pūnaewele ʻole, waiho wale nā pūnaewele me ka ʻike.
Keʻano 1. ʻOʻoleʻa a wikiwiki
- Ke koho ʻana i ka laulā kumu
- Kaomi i ke kī F5, pihi aʻe Kahiāuli Ho'ā (Kūikawā). Ma ka puka aniani e wehe ai, koho Pūnaehana hakahaka(Pākole) a kaomi OK.
Ua koho ʻia nā cell hakahaka a pau i ka laulā.
- Hāʻawi mākou i kahi kauoha i ka papa kuhikuhi e holoi i nā cell i koho ʻia: kaomi ʻākau- Holoi nā pūnaewele (Hoʻopau i nā Pūnaewele) me ka nee ana iluna.
Ke Kaʻina Hana 2: Array Formula
No ka maʻalahi, e inoa i kā mākou papa hana me ka hoʻohana ʻana Luna Hoʻonohonoho inoa (Luna inoa) uku pila haʻilula (Nā Kumu) a i ʻole, ma Excel 2003 a ʻoi aku, ka papa kuhikuhi Hoʻokomo - inoa - hāʻawi (Hoʻokomo - inoa - wehewehe)
E inoa i ka laulā B3:B10 Loaʻaʻole, ka laulā D3:D10 – ʻAʻohe. Pono e like ka nui o nā pae, a hiki ke loaʻa ma nā wahi āpau e pili ana i kekahi.
I kēia manawa, koho i ka cell mua o ka lua (D3) a hoʻokomo i kēia ʻano hoʻoweliweli i loko:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLANK(YesEmpty);"";INDIRECT(ADDRESS(LOWEST((IF(Empty<>"",ROW(Empty);ROW()) + ROWS(Aia ʻAʻohe Kaʻawale))); LINE()-ROW(ʻAʻohe Kaʻawale)+1); COLUMN(Aia ʻAʻohe Kaʻawale); 4)))
Ma ka ʻōlelo Pelekania, penei:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(HAWA ʻole),", INDIRECT(ADDRESS(LIʻI((IF(Hōʻokoʻa<>"",ROW(Hōʻokoʻa), ROW()) +ROWS(HaveEmpty))),ROW()-ROW(ʻAʻoheKoho)+1),COLUMN(HaveEmpty),4)))
Eia kekahi, pono e hoʻokomo ʻia ma ke ʻano he papa kuhikuhi, ʻo ia hoʻi, kaomi ma hope o ka hoʻopili ʻana komo (e like me ka mea mau) a Ctrl + Shift + Kākau. I kēia manawa hiki ke kope ʻia ke ʻano me ka hoʻohana ʻana i ka autocomplete (kauo i ke keʻa ʻeleʻele ma ke kihi ʻākau haʻahaʻa o ke kelepona) - a e loaʻa iā mākou ka pae kumu, akā me ka ʻole o nā cell nele:
Hana 3. Hana maʻamau ma VBA
Inā loaʻa ka manaʻo kānalua e hana pinepine ʻoe i ke kaʻina hana no ka wehe ʻana i nā cell blank mai nā pae, a laila ʻoi aku ka maikaʻi o ka hoʻohui ʻana i kāu hana ponoʻī no ka wehe ʻana i nā cell blank i hoʻokahi manawa, a hoʻohana iā ia i nā hihia a pau.
No ka hana ʻana i kēia, wehe i ka Visual Basic Editor (ALT + F11), e hoʻokomo i kahi module hakahaka hou (menu Hoʻokomo - Module) a kope i ka kikokikona o kēia hana ma laila:
Hana NoBlanks(DataRange Like Range) Like Variant() Dim N Like Long Dim N2 Like Long Dim Rng Like Range Dim MaxCells Like Long Dim Result() Like Variant Dim R Like Long Dim C Like Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) No kēlā me kēia Rng In DataRange.Cells If Rng.Value <> vbNullString Then N = N + 1 Result(N, 1 ) = Rng.Value End Ina A'e Rng No N2 = N + 1 To MaxCells Result(N2, 1) = vbNullString Next N2 If Application.Caller.Rows.Count = 1 NoBlanks = Application.Transpose(Result) Other NoBlanks = Result Hoʻopau inā pau ka hana
Mai poina e mālama i ka faila a hoʻi mai ka Visual Basic Editor i Excel. E hoʻohana i kēia hana i kā mākou hiʻohiʻona:
- E koho i ka laulā kūpono o nā keena hakahaka, no ka laʻana F3:F10.
- E hele i ka papa kuhikuhi Hoʻokomo - Hana (Hookomo — Hana)a i ʻole kaomi i ke pihi Hoʻokomo i ka hana (Hookomo i ka hana) uku pila haʻilula (Nā Kumu) i nā mana hou o Excel. Ma ka mahele Hoʻohālikelike ʻia ka mea hoʻohana (Ka mea hoʻohana i wehewehe) koho i kā mākou hana NoBlanks.
- E wehewehe i ka laula kumu me nā voids (B3:B10) ma ke ʻano he hoʻopaʻapaʻa hana a pēhi Ctrl + Shift + Kākaue hoʻokomo i ka hana ma ke ʻano he papa kuhikuhi.
:
- Holoi i nā lālani hakahaka a pau i ka papaʻaina me ka macro maʻalahi
- Ke wehe nei i nā lālani ʻole a pau i ka pepa haʻawina me ka hoʻohana ʻana i ka mea hoʻohui PLEX
- Hoʻopiha wikiwiki i nā keena ʻole a pau
- He aha nā macros, kahi e hoʻokomo ai i ka code macro ma VBA