Kubvisa maseru asina chinhu kubva muchikamu

Kugadziriswa kwedambudziko

Tine huwandu hwemasero ane data rine maseru asina chinhu:

 

Basa nderekubvisa masero asina chinhu, achisiya masero chete ane ruzivo.

Nzira 1. Yakashata uye inokurumidza

  1. Kusarudza rudzi rwepakutanga
  2. Dhinda kiyi F5, bhatani rinotevera Highlight (Special). Muhwindo rinovhurwa, sarudza Masero asina chinhu(Zvisina basa) uye baya OK.

    Kubvisa maseru asina chinhu kubva muchikamu

    Masero ese asina chinhu muchikamu anosarudzwa.

  3. Isu tinopa murairo mumenyu kudzima masero akasarudzwa: kurudyi-tinya- Delete masero (Delete Cells) nekukwira kumusoro.

Nzira 2: Array Formula

Kurerutsa, ngatipei mazita enzvimbo dzedu dzekushanda tichishandisa Zita reManeja (Maneja weZita) Tab Formula (Maumbirwo) kana, muExcel 2003 nepamusoro, menyu Pinza – Zita – Govera (Isa - Zita - Define)

 

Doma huwandu hweB3:B10 HaveEmpty, kureba D3:D10 - HapanaEmpty. Ranges dzinofanirwa kunge dzakanyanya kuenzana saizi, uye inogona kuwanikwa chero inoenderana neimwe.

Zvino sarudza sero rekutanga reyechipiri renji (D3) uye isa iyi inotyisa formula mairi:

=KANA(MUTSARA() -MUROW(HapanaChinhu)+1>NOTROWS(YesEmpty)-COUNTBLLANK(YesEmpty);””;INDIRECT(ADDRESS(YEKUPEDZISIRA((KANA(Isina<>“”),MUTSARA(Chisina);ROW() + mitsara(Kune Chisina))); MUTSARA()-MUTSARA(Hapana Chisina)+1); Koramu(Pane Zvisina); 4)))

Mushanduro yeChirungu ichave:

=KANA(MUTSARA()-MUROW(Hapana Chisina)+1>MITSARA(isina)-COUNTBLANK(Haisina),””,INDIRECT(KERO(DIKI((KANA(isina<>“”,MUTSARA(Chisina),MUROW() +MITSARA(HaveEmpty))),MUTSARA()-MUTSARA(Hapana Chisina)+1),COLUMN(HaveEmpty),4)))

Uyezve, inofanirwa kuiswa seyakarongwa fomula, kureva kudzvanya mushure mekunamira pinda (semazuva ose) uye Ctrl + Shift + Pinda. Ikozvino fomula yacho inogona kukopwa pasi uchishandisa autocomplete (dhonza muchinjiko mutema mukona yekurudyi yesero) - uye isu tichawana iyo yekutanga renji, asi isina masero asina chinhu:

 

Nzira 3. Tsika basa muVBA

Kana paine fungidziro yekuti iwe kazhinji unozofanirwa kudzokorora maitiro ekubvisa masero asina chinhu kubva kumarenji, saka zviri nani kuwedzera yako pachako basa rekubvisa masero asina chinhu kune yakajairwa yakatarwa kamwe chete, uye shandisa iyo mune ese anotevera zviitiko.

Kuti uite izvi, vhura iyo Visual Basic Editor (ALT + F11), isa nyowani isina chinhu module (menu Pinza – Module) uye kopira zvinyorwa zvebasa iri ipapo:

Function NoBlanks(DataRange As Range) As Variant() Dim N As Long Dim N2 As Long Dim Rng As Range Dim MaxCells As Long Dim Result() As Variant Dim R As Long Dim C As Long MaxCells = Application.WorksheetFunction.Max( _ Application.Caller.Cells.Count, DataRange.Cells.Count) ReDim Result(1 To MaxCells, 1 To 1) For Every Rng In DataRange.Cells Kana Rng.Value <> vbNullString Then N = N + 1 Result(N, 1 ) = Rng.Kukosha Kupera Kana Inotevera Rng YeN2 = N + 1 KuMaxCells Result(N2, 1) = vbNullString Next N2 Kana Application.Caller.Rows.Count = 1 Zvadaro NoBlanks = Application.Transpose(Result) Else NoBlanks = Result End Kana End Basa

Usakanganwa kuchengetedza faira uye chinja kubva kuVisual Basic Mharidzo kuenda kuExcel. Kushandisa basa iri mumuenzaniso wedu:

  1. Sarudza huwandu hwakakwana hwemasero asina chinhu, semuenzaniso F3: F10.
  2. Enda kumenyu Pinza – Basa (Isa - Basa)kana kuti tinya bhatani Isa basa (Isa Basa) Tab Formula (Maumbirwo) mune zvitsva zveExcel. Muchikwata Mushandisi Anotsanangurwa (Mushandisi Anotsanangurwa) sarudza basa redu NoBlanks.
  3. Rondedzera mabviro enzvimbo ine voids (B3:B10) sebasa nharo uye dzvanya Ctrl + Shift + Pindakupinda basa sefomura yakarongwa.

:

  • Kudzima mitsara yese isina chinhu mutafura kamwechete ine macro akareruka
  • Kubvisa mitsara yese isina chinhu mupepa rekushanda kamwechete uchishandisa iyo PLEX yekuwedzera
  • Kurumidza kuzadza maseru asina chinhu
  • Chii macros, kupi kwekuisa macro kodhi muVBA

 

Leave a Reply