Contents
Idan kun kasance aƙalla saba da aikin VPR (VLOOKUP) (idan ba haka ba, sai a fara gudu a nan), to ya kamata ku fahimci cewa wannan da sauran ayyuka masu kama da shi (VIEW, INDEX da SEARCH, SELECT, da dai sauransu) koyaushe suna bayarwa a sakamakon haka. darajar - lamba, rubutu ko kwanan wata da muke nema a cikin tebur da aka ba.
Amma idan, maimakon ƙima, muna so mu sami hyperlink mai rai, ta danna abin da za mu iya tsalle zuwa wasan da aka samo a cikin wani tebur don duba shi a cikin mahallin gaba ɗaya?
Bari mu ce muna da babban tebur na oda don abokan cinikinmu azaman shigarwa. Don saukakawa (ko da yake wannan ba lallai ba ne), na canza tebur zuwa gajeriyar hanyar maballin “smart” mai ƙarfi. Ctrl+T kuma ya ba a kan tab Constructor (Zane) sunanta tabOrders:
A kan takardar daban Ƙarfafa Na gina tebur pivot (ko da yake ba dole ba ne ya zama ainihin tebur mai mahimmanci - kowane tebur ya dace da ka'ida), inda, bisa ga bayanan farko, ana ƙididdige ƙimar tallace-tallace ta watanni ga kowane abokin ciniki:
Bari mu ƙara ginshiƙi zuwa teburin oda tare da dabarar da ke duba sunan abokin ciniki don tsari na yanzu akan takardar. Ƙarfafa. Don wannan muna amfani da gungu na ayyuka na gargajiya INDEX (INDEX) и MORE BAYYANA (MATSAYI):
Yanzu bari mu kunsa tsarin mu cikin aiki Sanarwa (CELL), wanda za mu nemi a nuna adireshin tantanin halitta da aka samo:
Kuma a ƙarshe, mun sanya duk abin da ya juya ya zama aiki HYPERLINK (HYPERLINK), wanda a cikin Microsoft Excel zai iya ƙirƙirar hyperlink mai rai zuwa hanyar da aka ba (adireshi). Abin da kawai ba a bayyane yake ba shine cewa dole ne ku manne alamar zanta (#) a farkon zuwa adireshin da aka karɓa don a gane hanyar haɗin daidai ta hanyar Excel azaman na ciki (daga takarda zuwa takarda):
Yanzu, lokacin da kuka danna kowane ɗayan hanyoyin haɗin gwiwa, nan take za mu yi tsalle zuwa tantanin halitta tare da sunan kamfani akan takardar tare da tebur pivot.
Don yin shi da kyau sosai, bari mu ɗan inganta tsarin mu ta yadda canjin ya faru ba zuwa sunan abokin ciniki ba, amma zuwa takamaiman ƙimar ƙima daidai a cikin ginshiƙi na wata lokacin da aka kammala tsari mai dacewa. Don yin wannan, dole ne mu tuna cewa aikin INDEX (INDEX) a cikin Excel yana da yawa kuma ana iya amfani dashi, a tsakanin sauran abubuwa, a cikin tsari:
=INDEX( XNUMXD_ kewayon; Layin_lambar; Lambar_lambar )
Wato, a matsayin hujja na farko, ba za mu iya ƙayyade ginshiƙan tare da sunayen kamfanoni a cikin pivot ba, amma duk yankin bayanai na tebur pivot, kuma a matsayin hujja na uku, ƙara yawan adadin da muke bukata. Ana iya ƙididdige shi cikin sauƙi ta aikin MONTH (wata), wanda ke mayar da lambar wata don kwanan wata yarjejeniya:
Ingantawa 2. Kyawawan alamar haɗin gwiwa
Hujjar aiki ta biyu HYPERLINK - rubutun da aka nuna a cikin tantanin halitta tare da hanyar haɗi - za a iya sanya shi mafi kyau idan kun yi amfani da haruffa marasa daidaituwa daga Windings, Webdings fonts da makamantansu maimakon alamun banal ">>". Don wannan zaka iya amfani da aikin SYMBOL (CHAR), wanda zai iya nuna haruffa ta lambar su.
Don haka, alal misali, lambar haruffa 56 a cikin font na Webdings zai ba mu kyakkyawar kibiya mai ninki biyu don haɗin haɗin gwiwa:
Haɓakawa 3. Hana layin yanzu da tantanin halitta mai aiki
Da kyau, don nasarar ƙarshe na kyakkyawa akan hankali, zaku iya haɗawa da fayil ɗin mu sauƙaƙan sigar nuna layin yanzu da tantanin halitta da muke bin hanyar haɗin gwiwa. Wannan yana buƙatar macro mai sauƙi, wanda za mu rataya don gudanar da taron canjin zaɓi akan takardar Ƙarfafa.
Don yin wannan, danna dama akan shafin Takaitawa kuma zaɓi umarnin view code (Duba code). Manna lambar da ke biyowa a cikin taga mai gyara Visual Basic wanda ke buɗewa:
Canje-canje na Sub Worksheet_SelectionChange (ByVal Target As Range) Sel.Interior.ColorIndex = -4142 Kwayoyin (ActiveCell.Row, 1).Mai girman (1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 Ƙarshen Ƙarshe
Kamar yadda kake gani cikin sauƙi, a nan za mu fara cire cika daga dukan takardar, sannan mu cika layin gaba ɗaya a cikin taƙaitaccen bayanin tare da rawaya (launi 6), sannan orange (code 44) tare da tantanin halitta na yanzu.
Yanzu, lokacin da aka zaɓi kowane tantanin halitta a cikin tantanin halitta (ba komai - da hannu ko sakamakon danna kan hanyar haɗin yanar gizon mu), gabaɗayan layin da tantanin halitta tare da watan da muke buƙata za a haskaka:
Beauty 🙂
PS Kawai tuna don adana fayil ɗin a cikin tsarin macro-enabled (xlsm ko xlsb).
- Ƙirƙirar hanyoyin haɗin waje da na ciki tare da aikin HYPERLINK
- Ƙirƙirar imel tare da aikin HYPERLINK