Zamkatimu
Ngati mumadziwa bwino ntchitoyi VPR (VLOOKUP) (ngati sichoncho, ndiye choyamba thamangani apa), ndiye muyenera kumvetsetsa kuti izi ndi ntchito zina zofanana ndi izo (VIEW, INDEX ndi SEARCH, SELECT, etc.) nthawi zonse zimapereka zotsatira zake. mtengo - nambala, malemba kapena tsiku lomwe tikuyang'ana pa tebulo lomwe laperekedwa.
Koma bwanji ngati, m'malo mwa mtengo, tikufuna kupeza cholumikizira chamoyo, podina pomwe titha kulumphira pamasewera omwe apezeka patebulo lina kuti tiwone momwe zilili?
Tinene kuti tili ndi tebulo lalikulu lamakasitomala athu ngati zolowetsa. Kuti zitheke (ngakhale izi sizofunikira), ndidasinthira tebulo kukhala njira yachidule ya kiyibodi ya "smart" Ctrl+T ndipo anapereka pa tabu Constructor (Kapangidwe) dzina lake tabOrders:
Pa pepala losiyana Kuphatikiza Ndinapanga tebulo la pivot (ngakhale siliyenera kukhala ndendende tebulo la pivot - tebulo lililonse ndiloyenera), kumene, malinga ndi deta yoyambirira, malonda a malonda ndi miyezi kwa kasitomala aliyense amawerengedwa:
Tiyeni tiwonjezeko gawo patebulo loyitanitsa ndi fomula yomwe imayang'ana dzina la kasitomala pazomwe zili patsambali. Kuphatikiza. Kwa ichi timagwiritsa ntchito gulu lachikale la ntchito INDEX (INDEX) и ZAMBIRI ZOVUTIKA (MATCH):
Tsopano tiyeni timangire fomula yathu kukhala ntchito Cell (SELU), yomwe tidzafunsa kuti iwonetse adilesi ya cell yomwe yapezeka:
Ndipo pamapeto pake, timayika zonse zomwe zakhala zikugwira ntchito HYPERLINK (HYPERLINK), yomwe mu Microsoft Excel imatha kupanga cholumikizira chamoyo kunjira yopatsidwa (adilesi). Chokhacho chomwe sichidziwikiratu ndichakuti muyenera kumata chizindikiro cha hashi (#) koyambira ku adilesi yomwe mwalandira kuti ulalowo uwoneke bwino ndi Excel ngati wamkati (kuchokera papepala kupita papepala):
Tsopano, mukadina maulalo aliwonse, tidzalumphira kuselo nthawi yomweyo ndi dzina la kampani papepala lomwe lili ndi pivot table.
Kuti zikhale zabwino kwambiri, tiyeni tiwongolere pang'ono fomula yathu kuti kusinthaku kusakhale ku dzina la kasitomala, koma pamtengo wa manambala ndendende pamndandanda wa mwezi pomwe dongosolo lofananiralo lidamalizidwa. Kuti tichite izi, tiyenera kukumbukira kuti ntchito INDEX (INDEX) mu Excel ndi yosinthika kwambiri ndipo ingagwiritsidwe ntchito, mwa zina, mwanjira:
=INDEX( XNUMXD_range; Mzere_nambala; Nambala_gawo )
Ndiko kuti, monga mkangano woyamba, sitingatchule mzati ndi mayina amakampani omwe ali mu pivot, koma gawo lonse la data la tebulo la pivot, ndipo monga mkangano wachitatu, onjezerani chiwerengero cha gawo lomwe tikufuna. Ikhoza kuwerengedwa mosavuta ndi ntchito MONTH (MWEZI), yomwe imabwezera nambala ya mwezi wa tsiku la mgwirizano:
Kupititsa patsogolo 2. Chizindikiro chokongola cholumikizira
Mkangano wachiwiri wa ntchito HYPERLINK - mawu omwe amawonetsedwa mu cell yokhala ndi ulalo - amatha kukhala okongola ngati mugwiritsa ntchito zilembo zosagwirizana ndi Windings, mafonti a Webdings ndi zina zotere m'malo mwa zikwangwani ">>". Kwa izi mungagwiritse ntchito SYMBOL (CHAR), yomwe imatha kuwonetsa zilembo ndi ma code awo.
Kotero, mwachitsanzo, code code 56 mu Webdings font idzatipatsa mivi iwiri yabwino ya hyperlink:
Kupititsa patsogolo 3. Onetsani mzere wamakono ndi selo yogwira ntchito
Chabwino, kuti mupambane kukongola komaliza pa nzeru wamba, mutha kulumikiza ku fayilo yathu mtundu wosavuta wowunikira mzere wapano ndi selo lomwe timatsata ulalo. Izi zidzafuna ma macro osavuta, omwe tidzapachikidwa kuti tithandizire kusintha kosintha papepala Kuphatikiza.
Kuti muchite izi, dinani kumanja pa pepala tabu Chidule ndikusankha lamulo View kachidindo (Onani kodi). Matani kachidindo zotsatirazi pawindo la Visual Basic editor lomwe limatsegula:
Private Sub Worksheet_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 Sub
Monga momwe mukuonera, apa timachotsa choyamba chodzaza pa pepala lonse, ndiyeno lembani mzere wonse mu chidule ndi chikasu (mtundu code 6), ndiyeno lalanje (code 44) ndi selo yamakono.
Tsopano, cell iliyonse mkati mwa chidule cha cell yasankhidwa (zilibe kanthu - pamanja kapena chifukwa cha kuwonekera pa hyperlink), mzere wonse ndi selo limodzi ndi mwezi womwe tikufuna zidzawunikiridwa:
Kukongola 🙂
PS Ingokumbukirani kusunga fayiloyo mumtundu wothandizidwa ndi macro (xlsm kapena xlsb).
- Kupanga maulalo akunja ndi amkati ndi ntchito ya HYPERLINK
- Kupanga maimelo ndi ntchito ya HYPERLINK