Contents
Inā ʻoe i kamaʻāina i ka hana ʻO VPR (VLOOKUP) (inā ʻaʻole, a laila holo mua ma aneʻi), a laila pono ʻoe e hoʻomaopopo i kēia a me nā hana ʻē aʻe e like me ia (VIEW, INDEX and SEARCH, SELECT, etc.) hāʻawi mau i ka hopena. waiwai - ka helu, kikokikona a i ʻole lā a mākou e ʻimi nei ma ka papa i hāʻawi ʻia.
Akā, pehea inā, ma kahi o kahi waiwai, makemake mākou e kiʻi i kahi loulou ola, ma ke kaomi ʻana i hiki iā mākou ke lele koke i ka pāʻani i loaʻa ma kahi papa ʻē aʻe e nānā iā ia ma kahi ʻano ākea?
E ʻōlelo mākou he papa ʻaina kauoha nui kā mākou mea kūʻai aku ma ke ʻano he hoʻokomo. No ka maʻalahi (ʻoiai ʻaʻole pono kēia), ua hoʻohuli wau i ka papaʻaina i kahi pōkole keyboard "akamai" ikaika. Ctrl+T a haawi aku ma ka papa Kukahula (Hoʻolālā) kona inoa tabOrds:
Ma kahi pepa ʻokoʻa Hoʻohui Ua kūkulu au i kahi papa pivot (ʻoiai ʻaʻole pono ia he papa pivot - kūpono kēlā me kēia papaʻaina ma ke ʻano kumu), kahi, e like me ka ʻikepili mua, helu ʻia nā dynamics kūʻai ma nā mahina no kēlā me kēia mea kūʻai:
E hoʻohui i kahi kolamu i ka papa kauoha me kahi ʻano e nānā i ka inoa o ka mea kūʻai aku no ke kauoha o kēia manawa ma ka pepa. Hoʻohui. No kēia, hoʻohana mākou i ka puʻupuʻu maʻamau o nā hana INDEX (INDEX) и HOIKE HOU (PAʻO):
I kēia manawa e hoʻopili i kā mākou formula i kahi hana KOHAI (CELL), a mākou e noi ai e hōʻike i ka helu wahi o ka cell i loaʻa:
A i ka hopena, hoʻokomo mākou i nā mea a pau i lilo i hana HYPERLINK (HYPERLINK), i loko o Microsoft Excel hiki ke hana i kahi loulou ola i kahi ala i hāʻawi ʻia (address). ʻO ka mea wale nō i ʻike ʻole ʻia, pono ʻoe e hoʻopili i ka hōʻailona hash (#) ma ka hoʻomaka i ka helu i loaʻa i ʻike pono ʻia ka loulou e Excel ma loko (mai ka ʻaoʻao a i ka ʻaoʻao):
I kēia manawa, ke kaomi ʻoe i kekahi o nā loulou, e lele koke mākou i ke kelepona me ka inoa o ka hui ma ka pepa me ka papa pivot.
I mea e maikaʻi loa ai, e hoʻomaikaʻi iki i kā mākou ʻano hana i hiki ʻole ke hoʻololi i ka inoa o ka mea kūʻai aku, akā i kahi waiwai helu kikoʻī i loko o ke kolamu mahina i ka wā i pau ai ke kauoha pili. No ka hana ʻana i kēia, pono mākou e hoʻomanaʻo i ka hana INDEX (INDEX) ma Excel he maʻalahi loa a hiki ke hoʻohana ʻia, ma waena o nā mea ʻē aʻe, ma ke ʻano:
=INDEX( ka lālani XNUMXD; Helu_laina; Helu_ kolamu )
ʻO ia, e like me ka hoʻopaʻapaʻa mua, hiki iā mākou ke kuhikuhi ʻaʻole i ke kolamu me nā inoa o nā ʻoihana i ka pivot, akā ʻo ka ʻāpana ʻikepili holoʻokoʻa o ka papa pivot, a ʻo ke kolu o ka hoʻopaʻapaʻa, e hoʻohui i ka helu o ke kolamu e pono ai mākou. Hiki ke helu maʻalahi e ka hana MAHINA (MAHINA), e hoʻihoʻi i ka helu mahina no ka lā hana:
Hoʻomaikaʻi 2. Hōʻailona loulou nani
Hoʻopaʻapaʻa hana lua HYPERLINK - ka kikokikona i hōʻike ʻia i loko o ke kelepona me kahi loulou - hiki ke hoʻonani ʻia inā hoʻohana ʻoe i nā huaʻōlelo maʻamau mai Windings, Webdings fonts a me nā mea like ma kahi o nā hōʻailona banal ">>". No kēia hiki iā ʻoe ke hoʻohana i ka hana SYMBOL (CHAR), hiki ke hōʻike i nā huapalapala ma kā lākou code.
No laila, no ka laʻana, e hāʻawi mai ke code 56 i ka font Webdings iā mākou i kahi pua pālua maikaʻi no kahi loulou:
Hoʻomaikaʻi 3. Hōʻike i ka lālani o kēia manawa a me ka cell active
ʻAe, no ka lanakila hope o ka nani ma luna o ka noʻonoʻo maʻamau, hiki iā ʻoe ke hoʻopili i kā mākou faila i kahi mana maʻalahi o ka hōʻike ʻana i ka laina o kēia manawa a me ke kelepona a mākou e hahai ai i ka loulou. Pono kēia i kahi macro maʻalahi, a mākou e kau ai e mālama i ka hanana hoʻololi koho ma ka pepa Hoʻohui.
No ka hana ʻana i kēia, kaomi pololei ma ka ʻaoʻao ʻaoʻao ʻo Summary a koho i ke kauoha View Kanawai (Nānā code). Hoʻopili i kēia code i loko o ka puka makani hoʻoponopono Visual Basic e wehe ana:
Ka Pepa Haʻawina Kūʻokoʻa_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
E like me kāu e ʻike maʻalahi ai, eia mākou e wehe mua i ka hoʻopiha mai ka pepa holoʻokoʻa, a laila e hoʻopiha i ka laina holoʻokoʻa i ka hōʻuluʻulu me ka melemele (code color 6), a laila ʻalani (code 44) me ke kelepona o kēia manawa.
I kēia manawa, ke koho ʻia kekahi cell i loko o ka cell summary cell (ʻaʻole ia he mea nui - ma ka lima a i ʻole ma muli o ke kaomi ʻana i kā mākou hyperlink), e hōʻike ʻia ka lālani a me ka cell me ka mahina a mākou e pono ai:
Nani 🙂
PS E hoʻomanaʻo wale i ka mālama ʻana i ka faila ma kahi ʻano macro-enabled (xlsm a i ʻole xlsb).
- Ke hana nei i nā loulou i waho a i loko me ka hana HYPERLINK
- Ke hana nei i nā leka uila me ka hana HYPERLINK