אם אתה לפחות מכיר את הפונקציה VPR (VLOOKUP) (אם לא, אז קודם תריצו כאן), אז כדאי להבין שפונקציה זו ועוד פונקציות דומות לה (VIEW, INDEX ו-SEARCH, SELECT וכו') תמיד נותנות כתוצאה מכך ערך – המספר, הטקסט או התאריך שאנו מחפשים בטבלה הנתונה.
אבל מה אם, במקום ערך, נרצה לקבל היפר-קישור חי, על ידי לחיצה עליו נוכל לקפוץ מיידית להתאמה שנמצאה בטבלה אחרת כדי להסתכל עליה בהקשר כללי?
נניח שיש לנו טבלת הזמנות גדולה עבור הלקוחות שלנו כקלט. מטעמי נוחות (למרות שזה לא הכרחי), המרתי את הטבלה לקיצור מקשים דינמי "חכם" Ctrl+T ונתן על הכרטיסייה בנאי (לְעַצֵב) שמה tabOrders:
על גיליון נפרד מאוחד בניתי טבלת ציר (למרות שהיא לא חייבת להיות בדיוק טבלת ציר – כל טבלה מתאימה עקרונית), שבה לפי הנתונים הראשוניים מחושבת דינמיקת המכירות לפי חודשים לכל לקוח:
בואו נוסיף עמודה לטבלת ההזמנות עם נוסחה שמחפשת את שם הלקוח עבור ההזמנה הנוכחית בגיליון מאוחד. לשם כך אנו משתמשים בחבורת הפונקציות הקלאסית מדד (אינדקס) и יותר חשוף (התאמה):
עכשיו בואו נעטוף את הנוסחה שלנו לפונקציה תאים (תָא), שנבקש להציג את הכתובת של התא שנמצא:
ולבסוף, שמנו את כל מה שהתברר לפונקציה היפר קישור (HYPERLINK), אשר ב-Microsoft Excel יכול ליצור היפר-קישור חי לנתיב (כתובת) נתון. הדבר היחיד שלא מובן מאליו הוא שתצטרכו להדביק את סימן ה-hash (#) בהתחלה לכתובת המתקבלת כדי שהקישור ייתפס נכון באקסל כפנימי (מגיליון לגיליון):
כעת, כאשר תלחץ על כל אחד מהקישורים, אנו נקפוץ מיד לתא עם שם החברה על הגיליון עם טבלת הציר.
כדי שזה יהיה ממש טוב, בואו נשפר מעט את הנוסחה שלנו כך שהמעבר יתרחש לא לשם הלקוח, אלא לערך מספרי ספציפי בדיוק בעמודת החודש שבה הושלמה ההזמנה המקבילה. כדי לעשות זאת, עלינו לזכור כי הפונקציה מדד (אינדקס) ב-Excel הוא מאוד תכליתי וניתן להשתמש בו, בין היתר, בפורמט:
=INDEX( XNUMXD_טווח; מספר קו; מספר_עמודה )
כלומר, בתור הארגומנט הראשון, אנחנו יכולים לציין לא את העמודה עם שמות החברות בציר, אלא את כל אזור הנתונים של טבלת הציר, וכארגומנט שלישי, להוסיף את מספר העמודה שאנחנו צריכים. ניתן לחשב אותו בקלות על ידי הפונקציה חוֹדֶשׁ (חוֹדֶשׁ), שמחזירה את מספר החודש עבור תאריך העסקה:
שיפור 2. סמל קישור יפה
ארגומנט פונקציה שני היפר קישור – הטקסט המוצג בתא עם קישור – יכול להיעשות יפה יותר אם אתה משתמש בתווים לא סטנדרטיים מגופני Windings, Webdings וכדומה במקום הסימנים הבנאליים “>>”. בשביל זה אתה יכול להשתמש בפונקציה סמל (לְהַשְׁחִיר), שיכול להציג תווים לפי הקוד שלהם.
אז, למשל, קוד תו 56 בגופן Webdings ייתן לנו חץ כפול נחמד להיפר-קישור:
שיפור 3. הדגש את השורה הנוכחית ואת התא הפעיל
ובכן, לניצחון הסופי של היופי על השכל הישר, תוכלו גם לצרף לקובץ שלנו גרסה פשוטה של הדגשת השורה הנוכחית והתא שאליו אנו עוקבים אחר הקישור. זה ידרוש מאקרו פשוט, אותו נתלה כדי לטפל באירוע שינוי הבחירה בגיליון מאוחד.
לשם כך, לחץ לחיצה ימנית על לשונית הגיליון סיכום ובחר את הפקודה לצפיה קוד (נוף קוד). הדבק את הקוד הבא בחלון עורך Visual Basic שנפתח:
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 Worksheet
כפי שניתן לראות בקלות, כאן אנו מסירים תחילה את המילוי מהגיליון כולו, ולאחר מכן ממלאים את כל השורה בסיכום בצהוב (קוד צבע 6), ולאחר מכן כתום (קוד 44) בתא הנוכחי.
כעת, כאשר נבחר תא כלשהו בתוך תא הסיכום (זה לא משנה – ידנית או כתוצאה מלחיצה על ההיפר-קישור שלנו), כל השורה והתא עם החודש שאנו צריכים יודגשו:
יופי 🙂
PS רק זכרו לשמור את הקובץ בפורמט המאפשר מאקרו (xlsm או xlsb).
- יצירת קישורים חיצוניים ופנימיים עם פונקציית HYPERLINK
- יצירת מיילים עם פונקציית HYPERLINK