Ke wehe nei i nā lālani hakahaka a me nā kolamu i ka ʻikepili

Hiki ke pilikia nā lālani a me nā kolamu i nā papa i nā manawa he nui. ʻO nā hana maʻamau no ka ʻohi ʻana, kānana, hōʻuluʻulu, hana i nā papa pivot, a me nā mea ʻē aʻe. Inā he nui nā āpau, a laila hiki i ka lawe lima ʻana iā lākou ke kumukūʻai nui loa, a ʻaʻole pono e wehe i nā mea āpau i ka manawa hoʻokahi "ma ka nui" me ka hoʻohana ʻana i ka kānana, no ka mea, e "hina" ka kānana i nā haki.

E nānā kākou i kekahi mau ala e hoʻoponopono ai i kēia pilikia.

Kaʻina 1. E ʻimi i nā keena hakahaka

ʻAʻole paha kēia ka mea maʻalahi, akā ʻo ke ala maʻalahi ke kūpono e haʻi ʻia.

Inā mākou e pili ana i kahi papaʻaina i loaʻa nā lālani ʻole a me nā kolamu i loko (i hōʻike ʻia no ka maopopo):

Inā paha ua maopopo iā mākou aia ka inoa o ke kūlanakauhale ma ka kolamu mua o kā mākou papa ʻaina (koluma B). A laila, he hōʻailona nā pūnaewele hakahaka i kēia kolamu no nā lālani hakahaka ʻole. No ka wehe koke ʻana iā lākou a pau, e hana i kēia:

  1. E koho i ka laulā me nā kūlanakauhale (B2:B26)
  2. Kaomi i ke kī F5 a laila kaomi Kahiāuli Ho'ā (Hele i Special) a i ʻole koho ma ka pā Home — E ʻimi a koho — E koho i kahi pūʻulu pūnaewele (Home — E ʻimi a koho — E hele i kahi kūikawā).
  3. Ma ka puka aniani e wehe ai, koho i ke koho Pūnaehana hakahaka (Pākole) a ua pilikia OK - pono e koho ʻia nā keʻena hakahaka a pau ma ke kolamu mua o kā mākou papaʻaina.
  4. I kēia manawa koho ma ka pā Home E kauoha aku Holoi - Holoi i nā lālani mai ka pepa (Delete — Holoi i na lalani) a i ʻole kaomi i ka ʻaoʻao pōkole Ctrl+hua - a ua hoʻoholo ʻia kā mākou hana.

ʻOiaʻiʻo, hiki iā ʻoe ke hoʻopau i nā kolamu ʻole ma ke ʻano like, me ka hoʻohana ʻana i ke poʻo papa ma ke kumu.

Ka Papa Hana 2: Huli i nā lālani hakahaka

E like me kāu i noʻonoʻo ai, e hana wale ke ʻano hana ma mua inā loaʻa i kā mākou ʻikepili nā lālani a me nā kolamu i hoʻopiha piha ʻia, hiki ke hoʻopili ʻia i ka wā e ʻimi nei i nā cell blank. Akā, pehea inā ʻaʻohe hilinaʻi like ʻole, a i loko o ka ʻikepili i loko o nā cell hakahaka pū kekahi?

E nānā i ka papaʻaina ma lalo nei, no ka laʻana, no ka hihia like.

Eia ke ala e ʻoi aku ka maʻalahi:

  1. E hoʻokomo i loko o ka cell A2 i ka hana KANAWAI (COUNTA), ka mea e helu ai i ka helu o nā cell i hoʻopiha ʻia ma ka lālani ma ka ʻākau a kope i kēia ʻano hoʻohālike i lalo i ka papaʻaina holoʻokoʻa:
  2. E koho i ka cell A2 a hoʻohuli i ka kānana me ke kauoha ʻIkepili – kānana (ʻIkepili — kānana) a i ʻole ka pōkole papa keyboard Ctrl+Shift+L.
  3. E kānana i nā zeros ma ke kolamu i helu ʻia, ʻo ia hoʻi nā lālani a pau ʻaʻohe ʻikepili.
  4. Ke waiho nei i ke koho ʻana i nā laina kānana a kāpae iā lākou me ke kauoha Home — Holoi -' Holoi i nā lālani mai ka pepa (Home — Holoi — Holoi i na lalani) a i ʻole ka pōkole papa keyboard Ctrl+hua.
  5. Hoʻopau mākou i ka kānana a loaʻa kā mākou ʻikepili me nā laina ʻole.

ʻO ka mea pōʻino, ʻaʻole hiki ke hana hou ʻia kēia hoʻopunipuni me nā kolamu - ʻaʻole i aʻo ʻo Excel i ke kānana ʻana e nā kolamu.

Ke Kaʻina Hana 3. ʻO ka Macro e wehe i nā lālani ʻole a me nā kolamu ma kahi pepa

Hiki iā ʻoe ke hoʻohana i kahi macro maʻalahi e hoʻokaʻawale i kēia hana. E kaomi i ka ʻaoʻao pōkole Alt+F11 a i ʻole koho mai ka pā mea haku polokalamu — ʻIke ʻIke (Ka mea hoʻomohala - Visual Basic Lunahooponopono). Inā nā tabs mea haku polokalamu ʻAʻole ʻike ʻia, hiki iā ʻoe ke hoʻohana iā ia ma o Kōnae - Nā koho - Hoʻonohonoho Ribbon (Kōnae — Koho — Hoʻopilikino Ribbon).

Ma ka puka makani hoʻoponopono Visual Basic e wehe ana, koho i ke kauoha menu Hoʻokomo - Module a i loko o ka module hakahaka e ʻike ʻia, kope a paʻi i kēia mau laina:

   Sub DeleteEmpty() Dim r Like Long, rng Like Range 'удаляем пустые строки No r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If ʻAʻohe rng A laila Hoʻonohonoho rng = Lālani(r) Hoʻonohonoho ʻē aʻe rng = Union(rng, Lālani(r)) Hoʻopau inā ʻAʻole r inā ʻaʻole rng ʻAʻohe mea A laila rng.Delete 'удаляем пустые столбцы Hoʻonohonoho rng = ʻAʻohe mea No r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) Hoʻopau inā aʻe r inā ʻaʻole rng ʻaʻohe mea a laila rng.Delete End Sub

E pani i ka mea hoʻoponopono a hoʻi i Excel. 

I kēia manawa e pā i ka hui ʻana Alt+F8 a i ʻole pihi macros uku pila mea haku polokalamu. ʻO ka puka makani e wehe ʻia e papa inoa i nā macros āpau i loaʻa iā ʻoe e holo, me ka macro āu i hana ai. Holoi ʻAʻohe. E koho a kaomi i ke pihi holo (holo) - e holoi koke ʻia nā lālani ʻole a me nā kolamu ma ka pepa.

Hanana 4: Nīnau Mana

ʻO kahi ala ʻē aʻe e hoʻoponopono ai i kā mākou pilikia a me kahi hiʻohiʻona maʻamau ka wehe ʻana i nā lālani ʻole a me nā kolamu i Power Query.

ʻO ka mua, e hoʻouka i kā mākou papaʻaina i ka Luna Hoʻoponopono Query Query. Hiki iā ʻoe ke hoʻololi iā ia i kahi "akamai" ikaika me ka pōkole keyboard Ctrl+T a i ʻole e koho wale i kā mākou kikowaena data a hāʻawi iā ia i inoa (no ka laʻana. 'Ikepili) i ka papa formula, e hoʻololi ana i ka inoa:

I kēia manawa, hoʻohana mākou i ka ʻikepili kauoha - E kiʻi i ka ʻikepili - Mai ka papaʻaina / ākea (Data - E kiʻi i ka ʻikepili - Mai ka papa / ʻāpana) a hoʻouka i nā mea āpau i ka Power Query:

A laila ua maʻalahi nā mea a pau:

  1. Holoi mākou i nā laina ʻole me ke kauoha Home - Hoʻemi i nā laina - Holoi i nā laina - Holoi i nā laina ʻole (Home - Wehe i nā lālani - Wehe i nā lālani ʻole).
  2. Kaomi ʻākau ma ke poʻo o ke kolamu City mua a koho i ke kauoha Unpivot Other Columns mai ka papa kuhikuhi. ʻO kā mākou papaʻaina, e like me ka mea i kapa pololei ʻia, maʻamau - hoʻololi ʻia i ʻekolu kolamu: kūlanakauhale, mahina a me ka waiwai mai ka hui ʻana o ke kūlanakauhale a me ka mahina mai ka papa kumu. ʻO ka hiʻohiʻona o kēia hana ma Power Query ʻo ia ka lele ʻana i nā cell hakahaka i ka ʻikepili kumu, ʻo ia ka mea e pono ai mākou:
  3. I kēia manawa, hana mākou i ka hana hoʻohuli - hoʻohuli mākou i ka papa ʻaina i ʻelua-dimensional i mea e hoʻihoʻi ai i kona ʻano kumu. E koho i ke kolamu me nā mahina a ma ka pā loli koho i kahi hui kolamu pivot (Hoʻololi - Kona Pivot). Ma ka puka aniani e wehe ai, e like me ke kolamu o nā waiwai, koho i ka hope (Value), a ma nā koho holomua - ka hana. Mai hōʻuluʻulu (Mai hōʻuluʻulu):
  4. Ke waiho nei ia e hoʻouka i ka hopena i Excel me ke kauoha Home — Pani a hoʻouka — Pani a hoʻouka i… (Home — Hoʻopili a hoʻouka — Hoʻopili a hoʻouka iā…)

  • He aha ka macro, pehea e hana ai, kahi e kope ai i ka kikokikona o kahi macro, pehea e holo ai i kahi macro?
  • E hoʻopiha ana i nā cell hakahaka āpau i ka papa inoa me nā waiwai o nā cell makua
  • Wehe ʻia nā cell hakahaka a pau mai kahi pae i hāʻawi ʻia
  • Ke wehe nei i nā lālani hakahaka a pau i loko o ka pepa haʻawina me ka mea hoʻohui PLEX

Waiho i ka Reply