Cire layuka marasa komai da ginshiƙai a cikin bayanai

Layukan wofi da ginshiƙai na iya zama zafi a cikin tebur a yawancin lokuta. Madaidaitan ayyuka don rarrabuwa, tacewa, taƙaitawa, ƙirƙirar teburi pivot, da sauransu. suna ganin layuka da ginshiƙai marasa komai a matsayin hutun tebur, ba tare da ɗaukar bayanan da ke gaba a bayansu ba. Idan akwai irin wannan gibba da yawa, to, cire su da hannu zai iya zama mai tsada sosai, kuma ba zai yi aiki ba don cirewa gaba ɗaya "a cikin girma" ta amfani da tacewa, saboda tace kuma za ta "yi tuntuɓe" a lokacin hutu.

Bari mu dubi hanyoyi da yawa don magance wannan matsala.

Hanyar 1. Bincika sel mara komai

Wannan bazai zama mafi dacewa ba, amma tabbas hanya mafi sauƙi ta cancanci a ambaci.

A ce muna ma'amala da irin wannan tebur mai ɗauke da layuka da ginshiƙai da yawa a ciki (wanda aka haskaka don bayyanawa):

A ce mun tabbata cewa ginshiƙin farko na teburin mu (shafi B) koyaushe yana ɗauke da sunan birni. Sannan sel mara komai a cikin wannan ginshiƙi zasu zama alamar layuka mara amfani. Don cire su duka da sauri, yi waɗannan:

  1. Zaɓi kewayo tare da birane (B2:B26)
  2. Latsa madannin F5 sannan kuma latsa haskaka (Je zuwa Special) ko zaɓi a kan shafin Gida - Nemo kuma Zaɓi - Zaɓi ƙungiyar sel (Gida - Nemo & Zaɓi - Je zuwa na musamman).
  3. A cikin taga da yake buɗewa, zaɓi zaɓi Kwayoyin fanko (Blanks) kuma latsa OK – duk sel mara komai a cikin ginshiƙin farko na teburin mu yakamata a zaɓi.
  4. Yanzu zaɓi kan shafin Gida umurnin Share - Share layuka daga takarda (Goge - Share layuka) ko danna gajeriyar hanyar madannai Ctrl+debe – kuma an warware aikin mu.

Tabbas, zaku iya kawar da ginshiƙan fanko a daidai wannan hanya, ta yin amfani da taken tebur azaman tushe.

Hanyar 2: Nemo layuka marasa komai

Kamar yadda wataƙila kun riga kuka fayyace, hanyar da ta gabata za ta yi aiki ne kawai idan bayananmu sun ƙunshi cikakkun layuka da ginshiƙai, waɗanda za a iya haɗa su yayin neman sel mara komai. Amma idan babu irin wannan amincewa fa, kuma bayanan na iya ƙunsar sel mara komai kuma?

Dubi tebur mai zuwa, alal misali, don irin wannan yanayin:

Anan tsarin zai zama ɗan wayo:

  1. Shigar da aikin a cikin cell A2 COUNT (COUNTA), wanda zai lissafta adadin cike da sel a jere zuwa dama kuma ya kwafi wannan dabara zuwa ga teburin duka:
  2. Zaɓi cell A2 kuma kunna tacewa tare da umarni Bayanai - Tace (Bayanai - Tace) ko gajeriyar hanyar keyboard Ctrl+Motsi+L.
  3. Bari mu tace sifili ta hanyar ginshiƙi mai ƙididdigewa, watau duk layuka inda babu bayanai.
  4. Ya rage don zaɓar layin da aka tace kuma share su tare da umarni Gida - Share -' Share layuka daga takarda (Gida - Share - Share layuka) ko gajeriyar hanyar keyboard Ctrl+debe.
  5. Muna kashe tacewa kuma mu sami bayanan mu ba tare da layukan komai ba.

Abin takaici, wannan dabarar ba za a iya yin ta da ginshiƙai - Excel bai riga ya koyi yadda ake tace ta ginshiƙai ba.

Hanyar 3. Macro don cire duk layuka marasa komai da ginshiƙai akan takarda

Hakanan zaka iya amfani da macro mai sauƙi don sarrafa wannan aikin. Danna gajeriyar hanyar madannai alt+F11 ko zaɓi daga shafin developer - Kayayyakin Basic (Mai Haɓakawa - Editan Kayayyakin gani). Idan tabs developer ba a bayyane, kuna iya kunna ta ta hanyar Fayil - Zaɓuɓɓuka - Saitin Ribbon (Fayil - Zaɓuɓɓuka - Keɓance Ribbon).

A cikin taga Editan Kayayyakin Kayayyakin da ke buɗewa, zaɓi umarnin menu Saka - Module kuma a cikin madaidaicin tsarin da ya bayyana, kwafi da liƙa waɗannan layuka masu zuwa:

   Sub ShareEmpty() Dim r As Long, rng As Range' удаляем пустые строки For r = 1 Zuwa ActiveSheet.Amfani da Range.Row - 1 + ActiveSheet.Amfani da Range.Rows.Count Idan Application.CountA(Rws(r))) = 0 To Idan rng Ba Komai ba Sai Saita rng = Layuka(r) Else Set rng = Union(rng, Rows(r)) Karshen Idan Na gaba r Idan Ba ​​rng Ba Komai ba Sai rng.Delete 'удаляем пустые столбцы Set rng = Babu Komai Don r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Kidaya Idan Application.CountA(Columns(r)) = 0 To Idan rng Ba Komai Ba Sai Saita rng = Rukunin (r) Else Set rng = Union(rng, Columns) r)) Karshe Idan Na Gaba r Idan Ba ​​rng Ba Komai ba Sai rng.Delete End Sub

Rufe editan kuma komawa zuwa Excel. 

Yanzu danna hade alt+F8 ko button Macros tab developer. Tagar da ke buɗewa za ta jera duk macro a halin yanzu da za ku iya aiki, gami da macro da kuka ƙirƙira. ShareBacky. Zaɓi shi kuma danna maɓallin Run (gudu) – duk layuka da ginshiƙai da ke kan takardar za a share su nan take.

Hanyar 4: Tambayar Wuta

Wata hanya don magance matsalarmu kuma yanayin gama gari shine cire layuka da ginshiƙai marasa komai a cikin Query Query.

Da farko, bari mu loda teburin mu cikin Editan Query Query. Kuna iya canza shi zuwa "mai hankali" mai ƙarfi tare da gajeriyar hanyar keyboard Ctrl+T ko kawai zaɓi kewayon bayanan mu kuma ba shi suna (misali. data) a cikin mashigin dabara, yana juyawa zuwa mai suna:

Yanzu muna amfani da umarnin Data - Sami bayanai - Daga tebur / kewayon (Bayani - Samu Bayanai - Daga tebur / kewayon) kuma mu loda komai cikin Tambayar Wuta:

Sannan komai yana da sauki:

  1. Muna share layukan wofi tare da umarnin Gida - Rage layi - Share Lines - Share layin da ba komai (Gida - Cire Layuka - Cire layuka marasa komai).
  2. Danna-dama a kan kan jigon ginshiƙin birni na farko kuma zaɓi umarnin Unpivot Sauran Rukunoni daga menu na mahallin. Teburin mu zai kasance, kamar yadda aka kira shi daidai, bisa al'ada – canza zuwa ginshiƙai uku: birni, wata da ƙima daga mahadar birni da wata daga tebur na asali. Mahimmancin wannan aiki a cikin Query Query shine cewa yana tsallake sel mara komai a cikin bayanan tushen, wanda shine abin da muke buƙata:
  3. Yanzu muna yin aikin baya - muna juya teburin da aka samo baya zuwa mai girma biyu don mayar da shi zuwa asalinsa. Zaɓi shafi tare da watanni kuma akan shafin Sake Kama zabi tawagar Rukunin Pivot (Canza - Pivot Column). A cikin taga wanda ya buɗe, a matsayin ginshiƙi na dabi'u, zaɓi na ƙarshe (Value), kuma a cikin zaɓuɓɓukan ci gaba - aiki Kada ku tara (Kada a tara):
  4. Ya rage don mayar da sakamakon zuwa Excel tare da umarnin Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe & Load - Rufe & Loda zuwa…)

  • Mene ne macro, ta yaya yake aiki, inda za a kwafi rubutun macro, yadda ake gudanar da macro?
  • Cika duk sel mara komai a cikin jeri tare da ƙimar sel mahaifa
  • Cire duk sel mara komai daga kewayon da aka bayar
  • Cire duk layuka marasa komai a cikin takardar aiki tare da ƙari na PLEX

Leave a Reply