Ke kānana kolamu horizontal ma Excel

Inā ʻaʻole ʻoe he mea hoʻohana novice, a laila ua ʻike mua ʻoe he 99% o nā mea a pau ma Excel i hoʻolālā ʻia e hana me nā papa kuʻuna, kahi e hele ai nā ʻāpana a i ʻole nā ​​​​ʻano (nā kahua) i nā kolamu, a aia ka ʻike e pili ana i nā mea a i ʻole nā ​​hanana. i nā laina . Pivot tables, subtotals, copy formula with a double click – hoʻonohonoho pono ʻia nā mea a pau no kēia hōpili ʻikepili.

Eia nō naʻe, ʻaʻohe kānāwai me ka ʻole o nā ʻokoʻa a me ke alapine maʻamau e nīnau ʻia iaʻu i ka mea e hana ai inā ʻike ʻia kahi papa me ka hoʻohālikelike semantic ākea, a i ʻole kahi papaʻaina i like ke kaumaha o nā lālani a me nā kolamu i ka manaʻo.

A inā ʻike mau ʻo Excel i ka hoʻokaʻawale ʻana (me ke kauoha ʻIkepili - Hoʻopili - Koho - Hoʻokaʻawale i nā kolamu), a laila ʻoi aku ka maikaʻi o ke kūlana me ka kānana - ʻaʻohe mea i kūkulu ʻia no ka kānana kolamu, ʻaʻole nā ​​lālani ma Excel. No laila, inā ʻoe e kū nei i kahi hana, pono ʻoe e hele mai me nā workarounds o nā pae like ʻole o ka paʻakikī.

Hana 1. Hana hou FILTER

Inā ʻoe ma ka mana hou o Excel 2021 a i ʻole kahi inoa inoa Excel 365, hiki iā ʻoe ke hoʻohana pono i ka hiʻohiʻona hou. FILT (FILTER), hiki ke kānana i ka ʻikepili kumu ʻaʻole wale ma nā lālani, akā ma nā kolamu pū kekahi. No ka hana ʻana, pono kēia hana i kahi laina laina hoʻokahi-dimensional auxiliary, kahi e hoʻoholo ai kēlā me kēia waiwai (TRUE a FALSE) inā hōʻike mākou a i ʻole, hūnā i ke kolamu aʻe i ka papaʻaina.

E hoʻohui i kēia laina ma luna o kā mākou papaʻaina a kākau i ke kūlana o kēlā me kēia kolamu i loko.

• E ʻōlelo mākou e makemake mau mākou e hōʻike i nā kolamu mua a me nā kolamu hope (nā poʻo a me nā huina), no laila, no lākou i nā cell mua a me hope o ka array mākou e hoʻonoho ai i ka waiwai = TRUE.
• No nā kolamu i koe, ʻo nā mea i loko o nā cell pili e lilo i kumu e nānā i ke kūlana e pono ai mākou e hoʻohana i nā hana. И (A ME) or OR (MAU). No ka laʻana, aia ka huina ma waena o 300 a 500.

Ma hope o kēlā, koe wale nō ka hoʻohana ʻana i ka hana FILT e koho i nā kolamu ma luna aʻe i loaʻa i kā mākou pūʻulu kōkua he waiwai TRUE:

Pēlā nō, hiki iā ʻoe ke kānana i nā kolamu ma kahi papa inoa i hāʻawi ʻia. I kēia hihia, e kōkua ka hana KĀNĀWAI (COUNTIF), nāna e nānā i ka helu o nā hanana o ka inoa kolamu aʻe mai ke poʻo papa ma ka papa inoa i ʻae ʻia:

Ke Kaʻina 2. Pivot table ma kahi o ka mea maʻamau

I kēia manawa, ua kūkulu ʻia ʻo Excel i kāna kānana ʻāpae e nā kolamu i nā papa pivot wale nō, no laila inā hoʻokele mākou e hoʻololi i kā mākou papa ʻaina mua i kahi papa pivot, hiki iā mākou ke hoʻohana i kēia hana i kūkulu ʻia. No ka hana ʻana i kēia, pono kā mākou papa kumu e hoʻokō i kēia mau kūlana:

• loaʻa i kahi laina poʻomanaʻo "pololei" me ka ʻole o nā pūnaewele i hoʻohui ʻia - i ʻole, ʻaʻole ia e hana i kahi papa pivot;
• ʻaʻole i loaʻa nā kope i loko o nā lepili o nā lālani a me nā kolamu - e "hāʻule" lākou i ka hōʻuluʻulu ʻana i kahi papa inoa o nā waiwai kūʻokoʻa;
• Loaʻa i nā helu wale nō i ka laulā o nā waiwai (ma ka hui ʻana o nā lālani a me nā kolamu), no ka mea, e hoʻopili pono ka papa pivot i kekahi ʻano hana hoʻohui iā lākou (sum, average, etc.)

Inā hoʻokō ʻia kēia mau ʻano a pau, a laila no ke kūkulu ʻana i kahi papa pivot e like me kā mākou papaʻaina mua, pono ia (ka mea mua) e hoʻonui ʻia mai ka crosstab i kahi pālahalaha (normalized). A ʻo ke ala maʻalahi loa e hana i kēia me ka Power Query add-in, kahi mea hana hoʻololi ʻikepili ikaika i kūkulu ʻia i Excel mai 2016. 

Eia kēia mau mea:

1. E hoʻololi kākou i ka papaʻaina i kauoha ikaika “akamai”. Home - Hōʻano ma ke ʻano he papaʻaina (Home — Hōpili e like me ka Papa).
2. Ke hoʻouka nei i Power Query me ke kauoha ʻIkepili – Mai ka Papa / Laulā (ʻIkepili – Mai ka Papa / Laulā).
3. Hoʻopili mākou i ka laina me nā huina (e loaʻa i ka hōʻuluʻulu nā huina ponoʻī).
4. E kaomi ʻākau ma ke poʻomanaʻo kolamu mua a koho Wehe i nā kolamu ʻē aʻe (Wehe i nā kolamu ʻē aʻe). Hoʻololi ʻia nā kolamu āpau i koho ʻole ʻia i ʻelua - ka inoa o ka limahana a me ka waiwai o kāna hōʻailona.
5. Kānana i ke kolamu me nā huina i komo i loko o ke kolamu kaila.
6. Hoʻokumu mākou i kahi papa pivot e like me ka hopena paʻa (normalized) papa 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ā…).

I kēia manawa hiki iā ʻoe ke hoʻohana i ka hiki ke kānana i nā kolamu i loaʻa i nā papa pivot - nā māka maʻamau ma mua o nā inoa a me nā mea. Nā kānana pūlima (Nā kānana lepili) or Kānana ma ka waiwai (Kānana Waiwai):

A ʻoiaʻiʻo, i ka wā e hoʻololi ai i ka ʻikepili, pono ʻoe e hōʻano hou i kā mākou nīnau a me ka hōʻuluʻulu ʻana me kahi pōkole keyboard Ctrl+Alt+F5 a hui paha ʻIkepili – Hoʻohou i nā mea a pau (ʻIkepili — Hoʻohou i nā mea a pau).

Hana 3. Macro ma VBA

ʻO nā ʻano hana mua a pau, e like me kāu e ʻike maʻalahi ai, ʻaʻole i kānana pololei - ʻaʻole mākou e hūnā i nā kolamu i ka papa inoa kumu, akā hana i kahi papaʻaina hou me kahi pūʻulu o nā kolamu i hāʻawi ʻia mai ka mea kumu. Inā makemake ʻia e kānana (huna) i nā kolamu i ka ʻikepili kumu, a laila pono kahi ala ʻokoʻa, ʻo ia hoʻi, kahi macro.

Inā makemake mākou e kānana i nā kolamu ma ka lele kahi e hoʻokō ai ka inoa o ka luna ma ke poʻo papaʻaina i ka mask i kuhikuhi ʻia ma ka cell melemele A4, no ka laʻana, hoʻomaka me ka leka "A" (ʻo ia hoʻi, e kiʻi iā "Anna" a me "Arthur. " ma ka hopena). 

E like me ke kaʻina hana mua, hoʻokō mua mākou i kahi laina laina kōkua, kahi i loko o kēlā me kēia cell e nānā ʻia kā mākou criterion e kahi formula a e hōʻike ʻia nā waiwai logical TRUE a i ʻole FALSE no nā kolamu ʻike ʻia a huna ʻia, kēlā me kēia:

A laila e hoʻohui i kahi macro maʻalahi. Kaomi ʻākau ma ka ʻaoʻao pepa a koho i ke kauoha kahi (Hoʻopā'ālua). E kope a paʻi i kēia code VBA i loko o ka puka makani e wehe ai:

Private Sub Worksheet_Change(ByVal Target As Range) Inā Target.Address = "$A$4" A laila no kēlā me kēia cell In Range("D2:O2") Inā cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Hoʻopau ʻoiaʻiʻo inā hoʻopau ke kelepona hope inā hoʻopau sub  

Penei kona manao.

• Ma keʻano laulā, he mea mālama hanana kēia Pepa Haʻawina_Hoʻololi, ʻo ia hoʻi, e holo ʻokoʻa kēia macro ma kekahi hoʻololi i kekahi kelepona ma ka pepa o kēia manawa.
• ʻO ka kuhikuhi ʻana i ka cell i hoʻololi ʻia e mau i ka loli pale umauma hoʻokahi.
• ʻO ka mea mua, nānā mākou ua hoʻololi pololei ka mea hoʻohana i ke kelepona me ka criterion (A4) - hana ʻia kēia e ka mea hoʻohana. if.
• A laila hoʻomaka ka pōʻaiapuni Mahalo… e hoʻomaʻamaʻa ma luna o nā keena hina (D2:O2) me nā waiwai hōʻailona TRUE / FALSE no kēlā me kēia kolamu.
• Inā TRUE (ʻoiaʻiʻo) ka waiwai o ke kelepona hina e hiki mai ana, a laila ʻaʻole hūnā ʻia ke kolamu, inā ʻaʻole mākou e hūnā iā ia (waiwai. hūnāʻia).

•  Hoʻohana ʻia nā hana ʻenehana mai ke Office 365: FILTER, SORT, a me UNIC
• Pivot papa me ke poʻomanaʻo multiline me ka Power Query
• He aha nā macros, pehea e hana ai a hoʻohana iā lākou