נוסחאות לגוגל שיטס

לשאלות, הערות והמלצות לשיפור מוזמנים לשלוח הודעת ווצאפ.

לרוב נבנה את הגיליון שלנו בצורה שנרצה שהנוסחאות שלנו ירוצו לאורך כל העמודה, על ידי שימוש בנוסחת Arrayformula עושים את זה נכון:

שימו לב שהנוסחה צריכה לכלול את הטווח של הריצה.

למשל בדוגמא הבאה נדגים חיבור של הטקסט בעמודה A עם הטקסט בעמודה B עם רווח ביניהם. 

הנוסחה תרוץ החל מעמודה A בשורה 1 עד סוף עמודה A, וכן מתחילת עמודה B בשורה 1 עד סוף עמודה B.

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

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

אז יש אופציה לשלב נוסחה יחד עם כותרת, זה נראה ככה:

במקרה הזה שם העמודה יהיה 'NAME column' והתוכן של הנוסחה יופיע תחתיו בשורה 2.

עכשיו נעלה שלב:

אחרי שלמדנו לעבוד עם נוסחת arrayformula ולמדנו לשים נוסחה יחד עם כותרת, נוכל לשלב ביניהם ולהחיל נוסחה על כל העמודה ישר בשורת הכותרת, ללא חשש שהנוסחה תיפגע.

נשתמש בנוסחה הקודמת ונשלב אותה כך:

שימו לב שבמקרה כזה הטווח שנגדיר לarrayformula יתחיל משורה 2 ולא משורה 1.

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

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

בשביל זה כדאי לשלב את נוסחת isBlank, שבודקת אם התא ריק ומחזירה True/Flase.

נשלב את הנוסחה יחד עם IF על הטווח שיקבע אם להחיל את הarrayformula שלנו בשורה הזאת או לא.

דוגמה לשימוש:

הנוסחה תבדוק אם יש ערך בעמודה A בשורה, אם לא (כלומר הIF שעוטף את isBlank הוא אמת) הנוסחה תחזיר ריק (הערך שבין הפסיקים שבמקרה שלנו אנחנו רוצים שיהיה ריק), ובמידה ויש תוכן בעמודה A יחזור לנו הטקסט 'מלא'.

מומלץ תמיד לשלב את isBlank בנוסחאות arrayformula.

הנוסחה הבאה תריץ לנו תאריכים לאורך כל העמודה, כשהתאריך הראשון הוא התאריך של היום, בשורה הבאה התאריך של מחר, שורה אחריה זה מחרתיים וכן הלאה.

הנוסחה לוקחת את היום (today) ומוסיפה לו מספר ימים כפי מספר השורה. בגלל שהתחלנו משורה שניה נפחית בשתיים כדי לקבל את התאריך של היום בשורה השניה אחרי הכותרת.

ניתן לשחק עם המספר האחרון לפי הצורך.

ניתן להשתמש באופרטור '<>' כדי לבדוק שני ערכים אם הם לא שווים. לדוגמה, הנוסחה הבאה תבדוק אם הערך בעמודה A לא שווה לערך בעמודה B ותחזיר ערך בוליאני:

כמובן אפשר לעטוף את הנוסחה בIF ולערוך את התוצאה בהתאם.

האופרטור '+' מסמל 'או' והאופרטור '*' מסמל 'בנוסף'.

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

בנוסחה הראשונה אנחנו בודקים אם A2 שווה ל-5 או ל-6 ומחזירים טקסט בהתאם.

בנוסחה השנייה אנחנו בודקים אם A2 שווה 5 או B2 שווה 6, במידה ואחד התנאים מתקיים נקבל 'שווה'.

כדי לבדוק האם טקסט מסוים מכיל טקסט אחר נשתמש בנוסחה הבאה:

הנוסחה תבדוק את כל המקומות בהם הערך שכתוב בעמודה B נמצא בעמודה C ותחזיר True/False.

כדי לקחת רק חלק מהטקסט הקיים, אנחנו יכולים להשתמש בנוסחאות Left/Right.

באמצעות Left ניקח חלק מתחילת הטקסט, ובאמצעות Right ניקח מסוף הטקסט.

הנוסחה לעיל תיקח את 5 התווים הראשונים בתא B2.

הנוסחה לעיל תיקח את 5 התווים האחרונים בתא B2.

כמובן אפשר לעטוף את הנוסחה בarrayformula וכך להריץ אותה לאורך כל העמודה בצורה הזאת:

כעת הנוסחה תרוץ לאורך כל העמודה.

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

נוכל לקבל את מספר היום בשבוע מתאריך נתון באמצעות הנוסחה הבאה:

לחלק מהנוסחאות הכתובות כאן והמשמשות אותי ביום-יום התוודעתי דרך איתמר מאייר האלוף, תודה!

שי פתאל

Shay digital services ©