Mundarija
Agar siz hech bo'lmaganda funktsiya bilan tanish bo'lsangiz VPR (KO'RISH) (agar yo'q bo'lsa, avval bu erda ishga tushiring), unda siz ushbu va unga o'xshash boshqa funktsiyalar (KO'RING, INDEX va SEARCH, SELECT va boshqalar) har doim natija berishini tushunishingiz kerak. qiymati - berilgan jadvalda biz qidirayotgan raqam, matn yoki sana.
Agar qiymat o'rniga biz jonli giperhavola olishni istasak, uni bosish orqali uni umumiy kontekstda ko'rish uchun darhol boshqa jadvaldagi topilgan moslikka o'tishimiz mumkin bo'lsa-chi?
Aytaylik, bizda mijozlarimiz uchun katta buyurtma jadvali mavjud. Qulaylik uchun (garchi bu kerak bo'lmasa ham) men jadvalni dinamik "aqlli" klaviatura yorlig'iga aylantirdim. Ctrl+T va yorliqda berdi konstruktor (Dizayn) uning ismi tabBuyurtmalar:
Alohida varaqda Birlashtirilgan Men pivot jadvalini qurdim (garchi u aniq pivot jadval bo'lishi shart emas - har qanday jadval printsipial jihatdan mos keladi), bu erda dastlabki ma'lumotlarga ko'ra, har bir mijoz uchun oylar bo'yicha savdo dinamikasi hisoblanadi:
Keling, varaqdagi joriy buyurtma uchun mijozning ismini qidiradigan formula bilan buyurtma jadvaliga ustun qo'shamiz. Birlashtirilgan. Buning uchun biz klassik funktsiyalar to'plamidan foydalanamiz INDEKS (INDEKS) и KO'PROQ (MATCH):
Endi formulamizni funksiyaga aylantiramiz JONIY (hujayra), biz topilgan katakning manzilini ko'rsatishni so'raymiz:
Va nihoyat, biz funktsiyaga aylangan hamma narsani joylashtiramiz HYPERLINK (HYPERLINK), bu Microsoft Excelda berilgan yo'lga (manzilga) jonli giperhavola yaratishi mumkin. Aniq bo'lmagan yagona narsa shundaki, havola Excel tomonidan ichki (varaqdan varaqgacha) to'g'ri qabul qilinishi uchun olingan manzilning boshida xesh belgisini (#) yopishtirishingiz kerak bo'ladi:
Endi havolalardan birini bosganingizda, biz bir zumda pivot jadvali bo'lgan varaqdagi kompaniya nomi ko'rsatilgan katakchaga o'tamiz.
Buni chindan ham yaxshi qilish uchun, keling, formulamizni biroz yaxshilaymiz, shunda mijozning nomiga emas, balki tegishli buyurtma tugallangandan so'ng oy ustunidagi ma'lum bir raqamli qiymatga o'tish sodir bo'ladi. Buni amalga oshirish uchun biz funktsiyani unutmasligimiz kerak INDEKS (INDEKS) Excelda juda ko'p qirrali va boshqa narsalar qatorida quyidagi formatda foydalanish mumkin:
=INDEX( XNUMXD_diapazon; Qator_raqami; Ustun_raqami )
Ya'ni, birinchi argument sifatida biz pivotdagi kompaniyalar nomlari bilan ustunni emas, balki pivot jadvalining butun ma'lumotlar maydonini ko'rsatishimiz mumkin va uchinchi argument sifatida bizga kerak bo'lgan ustun raqamini qo'shamiz. Uni funktsiya orqali osongina hisoblash mumkin MONTH (OY), bitim sanasi uchun oy raqamini qaytaradi:
Yaxshilash 2. Chiroyli havola belgisi
Ikkinchi funktsiya argumenti HYPERLINK - havolasi bo'lgan katakchada ko'rsatiladigan matn - ">>" banal belgilari o'rniga Windings, Webdings shriftlari va shunga o'xshash nostandart belgilardan foydalansangiz yanada chiroyli bo'lishi mumkin. Buning uchun funksiyadan foydalanishingiz mumkin SYMBOL (CHAR), bu belgilarni kodlari bo'yicha ko'rsatishi mumkin.
Shunday qilib, masalan, Webdings shriftidagi 56 belgi kodi bizga giperhavola uchun yaxshi qo'sh o'qni beradi:
Yaxshilash 3. Joriy qator va faol katakchani belgilang
Xo'sh, go'zallikning aql-idrok ustidan yakuniy g'alabasi uchun siz bizning faylimizga havola bo'yicha amal qiladigan chiziq va katakchani ajratib ko'rsatishning soddalashtirilgan versiyasini ham qo'shishingiz mumkin. Buning uchun oddiy makros kerak bo'ladi, biz uni varaqdagi tanlovni o'zgartirish hodisasini boshqarish uchun osib qo'yamiz Birlashtirilgan.
Buning uchun varaq yorlig'ini Xulosa ustiga sichqonchaning o'ng tugmachasini bosing va buyruqni tanlang ko'rinish kodi (Ko'rinish kod). Ochilgan Visual Basic muharriri oynasiga quyidagi kodni joylashtiring:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Hujayra(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex Sub = 44
Osonlik bilan ko'rib turganingizdek, bu erda biz birinchi navbatda butun varaqdan to'ldirishni olib tashlaymiz, so'ngra xulosadagi butun qatorni sariq (rang kodi 6), so'ngra to'q sariq (kod 44) joriy katak bilan to'ldiramiz.
Endi, xulosa katakchasi ichidagi istalgan katak tanlanganda (bu muhim emas – qo‘lda yoki bizning giperhavolani bosish natijasida), bizga kerak bo‘lgan oy bilan butun qator va katak ajratib ko‘rsatiladi:
Go'zallik 🙂
PS Faylni makro-faol formatda (xlsm yoki xlsb) saqlashni unutmang.
- HYPERLINK funksiyasi yordamida tashqi va ichki havolalar yaratish
- HYPERLINK funksiyasi bilan elektron pochta xabarlarini yaratish