חילוץ שם גיליון ע"י שילוב הפונקציות - Cell, Find, Mid

במאמר זה נלמד כיצד לחלץ שם גיליון באופן אוטומטי ע"י שימוש בנוסחה. נוסחה זו שימושית כאשר אנו צריכים לחלץ את שם בגיליון עבור מספר גדול של גיליונות או כאשר אנו רוצים קשר פונקציונלי קבוע בין שם הגיליון לפעולה בגיליון עצמו.

(Mid(Cell("filename"), Find("]", Cell("filename")) + 1, 31

נושאי המאמר

5. חילוץ שם הגיליון ע"י שילוב הפונקציות Cell, Find, Mid

סרטון המאמר

פונקציית Cell

לקריאה נוספת על פונקציית Cell בדף התמיכה של Microsoft

הפונקצייה מחזירה מידע מסוגים שונים עבור תא נבחר.

תחביר פונקציית Cell:

יש לבחור את סוג המיעד שאנחנו מעוניינים לקבל ולהזין את התא עבורו אנו מעוניינים לקבל את המידע הנבחר.

Cell(Info_type, [reference])

Info_Type - סוג המידע שאנו מעוניינים לקבל

[reference] - התא עבורו אנו מעוניינים לקבל את המידע

לאחר שנקליד את שם הפונקציה תיפתח רשימה הכוללת את סוגי המידע השונים אותם ניתן לקבל (ראו תמונה מס' 1)

תמונה מס' 1

Cell, Find, Mid

במאמר זה אנו נתמקד נתמקד בקבלת שם הקובץ כלומר filename.

שם הקובץ אותו נקבל הוא ערך ייחודי הכולל את כתובת הקובץ (File Path) + שם הקובץ + שם הגיליון. B2

בתמונה מס' 2 מוצגת דוגמה עבור גיליון בשם "ינואר" בקובץ בשם "קובץ דוגמה" בתקיה בשם "דוגמה" בקונן C.

תמונה מס' 2

Cell, Find, Mid

שימו לב ששם הגיליון ממוקם בסוף השם לאחר הסוגר המרובע הימני. כדי לחלץ את שם הגיליון יש לאתר את מיקומו שכמובן שיכול להשתנות מקובץ לקובץ. נעשה זאת ע"י שימוש בפונקציית Find.

פונקציית Find

לקריאה נוספת על פונקציית Find בדף התמיכה של Microsoft

פונקציית Find מחפשת מחרוזת טקסט בתוך מחרוזת טקסט אחרת.

במידה והטקסט נמצא הפונקצייה תחזיר לנו את מיקומו במחרוזת החל מהתו הראשון שלו במידה ולא נמצא נקבל חזרה טעות מסוג Value.

דוגמה 1: נחפש את הטקסט "-" בתוך מספר הטלפון 050-9579096 התשובה שנקבל היא 4 - מיקומו של התו "-" בתוך הטקסט.

דוגמה 2: נחפש את הטקסט "אקסל" בתוך הטקסט "הגיע הזמן להתייעל באקסל" התשובה תהיה 20 שזה מיקום המילה אקסל בתוך הטקסט (כמובן שגם תווי הרווח נספרים).

דוגמה 3: נחפש את הטקסט "-" בתוך מספר הטלפון 0509579096 התשובה שנקבל היא Value מפני שהתו לא קיים במחרוזת.

תחביר פונקציית Find

Find(find_text, within_text, [start_num])

"-" הטקסט אותו אנו מעוניינים לאתר, לפי דוגמה מס 1 - find_text

הטקסט בו אנו מחפשים ולפי דוגמה מס' 1- 050-9579096 - within_text

ערך אופציונלי המייצג את המיקום ממנו נרצה להתחיל את החיפוש - start_num

ולפי דוגמה מס' 1

Find("-","050-9579096") = 4

בחזרה לחילוץ שם הגיליון - נרצה לאתר את התו "[" בתוך מחרוזת הטקסט המייצגת את שם הקובץ:

Find("]",Cell("filename"))

נוסחה זו תחזיר לנו את תמיד את המיקום בו נמצא הסימן "[" כל שנשאר הוא להוסיף 1 כדי לקבל את המיקום של שם הגיליון כלומר:

Find("]",Cell("filename")) + 1

בשלב זה כל שנשאר לנו הוא לחלץ את השם ואת זה נעשה ע"י שימוש בפונקציית Mid

פונקציית Mid

לקריאה נוספת על פונקציית Mid בדף התמיכה של Microsoft

פונקצייה זו מאפשרת לחלץ מספר מסויים של תווים ממחרוזת טקסט החל מתו מסויים.

דוגמה 1: במידה ונחלץ 5 תווים החל מהתו הרביעי במחרוזת הבאה "8 לינואר 2020" נקבל ינואר (יש לזכור שגם הרווח נספר)

דוגמה 2: במידה ונחלץ 7 תווים החל מהתו החמישי במחרוזת הבאה "050-9579096" נקבל 9579096

תחביר פונקציית Mid

Mid(Text, Start_num, Num_chars)

Text - מחרוזת הטקסט המכילה את התווים שאנו רוצים לחלץ. לפי דוגמה מס 1 "8 לינואר 2020"

Start_num - מספר המייצג את מיקום התו הראשון אשר ממנו נתחיל לחלץ את מחרוזת התווים שלנו. 4 לפי דוגמה מס' 1

Num_chars - מספר התווים אותם נרצה לחלץ מהטקסט שלנו. 5 לפי דוגמה מס' 1

לפי דוגמה מס' 1:

ינואר = (4,5,"8 לינואר 2020")Mid

חילוץ שם הגיליון ע"י שילוב הפונקציות Cell, Find, Mid

עכשיו נשלב את שלושת הפונקציות כדי לקבל את שם הגיליון הרצוי - במקרה שלנו הגיליון בו אנו כתובים הנוסחה.

נתחיל עם פונקציית Mid - מפני שאנו מבצעים פעולה של חילוץ מחרוזת תווים (שם הגיליון) ממחרוזת כלשהי (שם הקובץ)

Text:

Cell("filename")

Start_num:

Find("]",Cell("filename")) + 1

זכרו ששם הגיליון תמיד ימוקם לאחר הסוגר המרובע הימני בשם הקובץ (ראו תמונה מס' 2)
זכרו שהסוגר המרובע הימני מופיע רק פעם אחת בשם הקובץ ולכו זה שנאתר הוא בהכרח זה המייצג את מיקום שם הגיליון לאחר שנוסיף 1.

Num_chars:

31 שזהו מספר התווים המקסימלי לשם גיליון

לכן הנוסחה הסופית שלנו, המשלבת את הפונקציות Cell, Find, Mid היא:

Mid(Cell("filename"), Find("]", Cell("filename")) + 1, 31)