Contents
Hoʻokumu i ka pilikia
Inā paha he papaʻaina kā mākou e "hula" me kēlā me kēia lā:
To whom the table seems small – mentally multiply it twenty times by area, adding a couple more blocks and two dozen large cities.
ʻO ka hana ka wehe ʻana i nā lālani pale a me nā kolamu i kēia manawa ʻaʻole pono no ka hana, ʻo ia hoʻi,
- hūnā i nā kikoʻī ma ka mahina, waiho wale i nā hapaha
- huna i ka huina ma na mahina a me na hapaha, e waiho wale ana i ka huina no ka hapalua makahiki
- hūnā i nā kūlanakauhale pono ʻole i kēia manawa (Ke hana nei au ma Moscow - no ke aha wau e ʻike ai iā St. Petersburg?), etc.
I ke ola maoli, aia ke kai o nā hiʻohiʻona o ia mau papa.
Hanana 1: Hūnā lālani a me nā kolamu
ʻO ke ala, ʻoiaʻiʻo, he primitive a ʻaʻole maʻalahi, akā hiki ke ʻōlelo ʻia ʻelua mau huaʻōlelo e pili ana. Hiki ke hūnā ʻia nā lālani a i ʻole nā kolamu i koho mua ʻia ma ka pepa ma ke kaomi ʻana i ke kolamu a i ʻole ke poʻo lālani a koho i ke kauoha mai ka papa kuhikuhi. Hūnā i (Hūnā):
No ka hōʻike hoʻohuli, koho i nā lālani / kolamu pili a, ma ke kaomi ʻākau, koho mai ka papa kuhikuhi, kēlā me kēia, make (Hōʻike).
ʻO ka pilikia, pono ʻoe e hana i kēlā me kēia kolamu a me ka lālani pākahi, ʻaʻole kūpono.
Ka Papa Hana 2. Huihui
Inā koho ʻoe i nā lālani a i ʻole nā kolamu he nui a laila koho mai ka papa kuhikuhi ʻIkepili - Pūʻulu a me ka Hoʻolālā - Pūʻulu (ʻIkepili - Pūʻulu a me Outline - Pūʻulu), a laila e hoʻopaʻa ʻia lākou i kahi bracket square (hui pū). Eia kekahi, hiki ke hoʻopili ʻia nā pūʻulu i kekahi i kekahi (a hiki i 8 mau pae nesting e ʻae ʻia):
ʻO kahi ala ʻoi aku ka maʻalahi a me ka wikiwiki, ʻo ia ka hoʻohana ʻana i kahi pōkole keyboard e hui pū i nā lālani a i ʻole nā kolamu i koho mua ʻia. Alt+Shift+Puʻu ʻākau, a no ka wehe ʻana Alt+Shift+Arrow Arrow, kēlā me kēia.
ʻOi aku ka maʻalahi o kēia ʻano o ka hūnā ʻana i ka ʻikepili pono ʻole - hiki iā ʻoe ke kaomi i ke pihi me ka "+"Ai 'ole"-", a i ʻole ma nā pihi me kahi pae hui helu ma ka ʻaoʻao hema hema o ka lau - a laila e hiolo a hoʻonui ʻia nā hui āpau o ka pae i makemake ʻia.
no hoi, inā loaʻa i kāu papa ʻaina nā lālani a i ʻole nā kolamu me ka hana o ka hōʻuluʻulu ʻana i nā cell pili, ʻo ia hoʻi, kahi manawa (ʻaʻole 100% ʻoiaʻiʻo) kēlā Excel e hana ʻo ia i nā hui pono a pau i ka papaʻaina me hoʻokahi neʻe - ma o ka papa kuhikuhi ʻIkepili - Pūʻulu a me ka hoʻolālā - E hana i ka hoʻolālā (ʻIkepili - Pūʻulu a me ka Outline - Hana i ka Outline). ʻO ka mea pōʻino, hana ʻole ia hana a i kekahi manawa e hoʻopau i nā mea lapuwale ma nā papa paʻakikī. Akā hiki iā ʻoe ke hoʻāʻo.
Ma Excel 2007 a me nā mea hou aku, aia kēia mau hauʻoli āpau ma ka pā 'Ikepili (Lā) ma ka hui 'ole (Okuhi):
Hana 3. Huna i nā lālani i kaha ʻia me kahi macro
ʻO kēia ʻano hana paha ka mea maʻalahi loa. E hoʻohui i kahi lālani kaʻawale a me kahi kolamu ʻole i ka hoʻomaka o kā mākou pepa a e kaha me kekahi ikona i kēlā mau lālani a me nā kolamu a mākou e makemake ai e hūnā:
I kēia manawa e wehe mākou i ka Visual Basic Editor (ALT + F11), e hoʻokomo i kahi module hou ʻole i loko o kā mākou puke (menu Hoʻokomo - Module) a kope i ka kikokikona o ʻelua macros maʻalahi ma laila:
Sub Hūnā() Dim cell As Range Application.ScreenUpdating = False 'E hoʻopau i ka hoʻonui ʻana i ka pale no ka wikiwiki ʻana no kēlā me kēia cell In ActiveSheet.UsedRange.Rows(1). Nā Pūnaewele 'E hoʻololi i nā cell āpau i ka lālani mua Inā cell.Value = "x " A laila cell .EntireColumn.Hidden = ʻOiaʻiʻo 'inā i loko o ka cell x - huna kolamu Next No kēlā me kēia cell In ActiveSheet.UsedRange.Columns(1). Hele nā pūnaewele i nā pūnaewele a pau o ke kolamu mua Inā cell.Value = "x" A laila cell.EntireRow.Hidden = ʻOiaʻiʻo 'inā i loko o ke kelepona x - hūnā i ka lālani Next Application.ScreenUpdating = True End Sub Sub Show() Columns.Hidden = Hoʻopunipuni 'hoʻopau i nā lālani huna a me nā kolamu Rows.Hidden = False End Sub
E like me kāu e manaʻo ai, ʻo ka macro Hūnā i huna a me ka macro hoike - Hōʻike i nā lālani i hōʻailona ʻia i hope a me nā kolamu. Inā makemake ʻia, hiki ke hāʻawi ʻia nā macros i nā kī wela (Alt + F8 a me ka pihi kiko'î), a i ʻole e hana pololei i nā pihi ma ka ʻaoʻao e hoʻomaka iā lākou mai ka pā Mea hoʻomohala - Hoʻokomo - Pihi (Ka mea hoʻomohala — hoʻokomo — pihi).
Ke Kaʻina Hana 4. Ka hūnā ʻana i nā lālani / kolamu me kahi kala i hāʻawi ʻia
E ʻōlelo kākou ma ka laʻana i luna, makemake mākou e hūnā i nā huina, ʻo ia hoʻi nā lālani poni a ʻeleʻele a me nā kolamu melemele a me ka ʻōmaʻomaʻo. A laila pono e hoʻololi iki ʻia kā mākou macro mua ma ka hoʻohui ʻana, ma kahi o ka nānā ʻana i ka hiki ʻana mai o "x", kahi hōʻoia no ka hoʻohālikelike ʻana i ka waihoʻoluʻu piha me nā cell sample i koho ʻia:
ʻO Sub HideByColor() Poʻomanaʻo ke kelepona e like me ka hoʻohana ʻana i ka pae. = ʻOiaʻiʻo inā cell.Interior.Color = Range("K2").Interior.Color Then cell.EntireColumn.Hidden = ʻOiaʻiʻo aʻe no kēlā me kēia cell In ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Range ("D2") Eia nō naʻe, ʻaʻole pono mākou e poina e pili ana i hoʻokahi caveat: hana wale kēia macro inā hoʻopiha ʻia nā keʻena o ka papa kumu i ke kala me ka lima, a ʻaʻole hoʻohana i ka formatting conditional (he palena kēia o ka waiwai Interior.Color). No laila, no ka laʻana, inā ʻoe e hōʻike maʻalahi i nā kuʻikahi āpau i kāu papaʻaina kahi i emi iho ai ka helu ma mua o 10 me ka hoʻohana ʻana i ka hoʻohālikelike kūlana:
... a makemake ʻoe e hūnā iā lākou i hoʻokahi neʻe, a laila e "hoʻopau" ka macro mua. Inā loaʻa iā ʻoe ka Excel 2010-2013, a laila hiki iā ʻoe ke hele i waho me ka hoʻohana ʻana i ka waiwai Kalaina waiwai DisplayFormat.Interior, ka mea e hoʻopuka i ke kala o ke kelepona, me ka nānā ʻole i ke ʻano o ka hoʻonohonoho ʻana. Penei paha ka macro e hūnā ai i nā laina polū:
Sub HideByConditionalFormattingColor() Dim cell as Range Application.ScreenUpdating = False No kēlā me kēia cell In ActiveSheet.UsedRange.Columns(1).Cells If cell.DisplayFormat.Interior.Color = Range("G2").DisplayFormat.Interior.Color Then cell .EntireRow.Hidden = ʻOiaʻiʻo ka palapala aʻe.ScreenUpdating = ʻOiaʻiʻo End Sub Lawe ʻia ʻo G2 ma ke ʻano he laʻana no ka hoʻohālikelike kala. Minamina ka waiwai Hōʻike Hōʻike ʻike ʻia ma Excel e hoʻomaka wale ana mai ka mana 2010, no laila inā loaʻa iā ʻoe ka Excel 2007 a ʻoi paha, pono ʻoe e hana i nā ala ʻē aʻe.
- He aha ka macro, kahi e hoʻokomo ai i ka code macro, pehea e hoʻohana ai
- ʻO ka hui ʻakomi ma nā papa inoa multilevel