Zamkatimu
Kupanga vuto
Tili ndi magulu angapo okhala ndi data yomwe ili ndi ma cell opanda kanthu:
Ntchito ndi kuchotsa maselo opanda kanthu, kusiya maselo okha ndi chidziwitso.
Njira 1. Zovuta komanso zofulumira
- Kusankha mtundu woyambirira
- Dinani fungulo F5, batani lotsatira yosangalatsa (Wapadera). Pazenera lomwe limatsegulidwa, sankhani Maselo opanda kanthu(Zopanda kanthu) ndipo dinani OK.
Maselo onse opanda kanthu mumtunduwo amasankhidwa.
- Timapereka lamulo mumenyu kuti tichotse maselo osankhidwa: dinani kumanja- Chotsani ma cell (Chotsani Maselo) ndi kusintha kwapamwamba.
Njira 2: Njira Yopangira
Kuti muchepetse, tiyeni titchule mayendedwe athu pogwiritsa ntchito Woyang'anira Dzina (Name Manager) tsamba chilinganizo (Mafomula) kapena, mu Excel 2003 ndi kupitilira apo, menyu Ikani - Dzina - Perekani (Lowetsani - Dzina - Tanthauzirani)
Tchulani mtundu wa B3:B10 Khalani ndi Empty, mtundu D3:D10 - PalibeEmpty. Miyendo iyenera kukhala yofanana kukula kwake, ndipo ikhoza kukhala paliponse.
Tsopano sankhani selo loyamba lamtundu wachiwiri (D3) ndikulowetsamo njira yowopsa iyi:
=IF(ROW() -ROW(NoEmpty)+1>NOTROWS(YesEmpty)-COUNTBLLANK(YesEmpty);””;INDIRECT(ADDRESS(CHOCHISIKA((IF(Chopanda<>“),ROW(Empty);ROW() + MIZINDIKIRO(Pali Chopanda))); Mzere()-ROW(Palibe)+1); NTHAWI (Pali Chopanda); 4)))
M'Chingerezi zikhala:
=IF(ROW()-ROW(NoEmpty)+1>ROWS(Empty)-COUNTBLANK(Zopanda),"",,INDIRECT(ADDRESS(Yang'ono((Ngati(Zopanda<>“",ROW(Zopanda),ROW() +MIZINDIKIRO(Mukhale ndiEmpty))),ROW()-ROW(Palibe)+1),COLUMN(HaveEmpty),4)))
Kuphatikiza apo, iyenera kulowetsedwa ngati njira yotsatsira, mwachitsanzo, kukanikiza pambuyo pa kusindikiza Lowani (monga mwachizolowezi) ndi Ctrl + Shift + Lowani. Tsopano chifanizirocho chikhoza kukopera pogwiritsa ntchito autocomplete (kokerani mtanda wakuda kumunsi kumanja kwa selo) - ndipo tidzapeza mndandanda wapachiyambi, koma opanda maselo opanda kanthu:
Njira 3. Mwambo ntchito mu VBA
Ngati pali kukayikira kuti nthawi zambiri mumayenera kubwereza njira yochotsera ma cell opanda kanthu m'mizere, ndiye kuti ndi bwino kuwonjezera ntchito yanu yochotsa ma cell opanda kanthu pamiyeso yomwe yakhazikitsidwa kamodzi, ndikuigwiritsa ntchito pazotsatira zonse.
Kuti muchite izi, tsegulani Visual Basic Editor (ALT + F11), ikani gawo latsopano lopanda kanthu (menu Ikani - Module) ndikukopera zolemba za ntchitoyi pamenepo:
Ntchito NoBlanks(DataRange As Range) Monga Kusiyana() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() Monga Kusiyana kwa Dim R Monga Utali Wa Dim C Monga Wautali MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 Kwa MaxCells, 1 Mpaka 1) Pa Rng Iliyonse Mu DataRange.Cells Ngati Rng.Value <> vbNullString Ndiye N = N + 1 Zotsatira(N, 1 ) = Rng.Value End Ngati Rng Yotsatira Kwa N2 = N + 1 Mpaka MaxCells Result(N2, 1) = vbNullString Next N2 Ngati Application.Caller.Rows.Count = 1 Ndiye NoBlanks = Application.Transpose(Result) Else NoBlanks = Zotsatira Mapeto Ngati Mapeto Ntchito
Musaiwale kusunga fayilo ndikusintha kuchokera ku Visual Basic Editor kupita ku Excel. Kuti tigwiritse ntchito izi mu chitsanzo chathu:
- Sankhani ma cell okwanira okwanira, mwachitsanzo F3:F10.
- Pitani ku menyu Insert - Ntchito (Ikani - Ntchito)kapena dinani batani Ikani ntchito (Ikani Ntchito) tsamba chilinganizo (Mafomula) m'matembenuzidwe atsopano a Excel. Mgulu Kumasulira Womasulira (Kutanthauzira Wogwiritsa) sankhani ntchito yathu NoBlanks.
- Tchulani mtundu wa gwero ndi voids (B3:B10) monga mtsutso wa ntchito ndikusindikiza Ctrl + Shift + Lowanikuti mulowetse ntchitoyi ngati fomula yofananira.
:
- Kuchotsa mizere yonse yopanda kanthu patebulo nthawi imodzi ndi macro osavuta
- Kuchotsa mizere yonse yopanda kanthu patsamba logwirira ntchito nthawi imodzi pogwiritsa ntchito chowonjezera cha PLEX
- Lembani mwachangu ma cell opanda kanthu
- Kodi macros ndi chiyani, momwe mungayikitsire macro code mu VBA