נוסחאות בגוגל שיטס ובאקסל עם VLOOKUP, IF ופיבוט
רוב העבודה היומיומית בגיליון נשענת על כמה נוסחאות: VLOOKUP כדי למשוך נתון מטבלה אחרת, IF כדי להחליט לפי תנאי, וטבלת פיבוט כדי לסכם הרבה שורות בטבלה קצרה. הנוסחאות כמעט זהות בגוגל שיטס ובאקסל. כאן תמצאו דוגמאות להעתקה, איך מקבעים תאים, איך מחשבים שעות עבודה, ומתי כדאי לעבור מגיליון ל-CRM.
לפני שמתחילים פסיק או נקודה-פסיק
הנוסחאות כתובות כאן עם פסיק בין הארגומנטים. בחלק מההגדרות האזוריות (שפה ומדינה של הקובץ או של המחשב) המפריד הוא נקודה-פסיק. אם נוסחה מחזירה שגיאה, החליפו , ב-;:
=IF(A2>100, "גבוה", "נמוך")
=IF(A2>100; "גבוה"; "נמוך")בגוגל שיטס את ההגדרה האזורית מחפשים בהגדרות הגיליון.
פונקציית VLOOKUP
VLOOKUP מחפשת ערך בעמודה הראשונה של טבלה ומחזירה ערך מעמודה אחרת באותה שורה.
=VLOOKUP(search_key, range, index, FALSE)דוגמה: בגיליון "לקוחות" עמודה A היא טלפון ועמודה C היא שם הנציג. רוצים להביא את הנציג לפי טלפון שבתא A2:
=VLOOKUP(A2, לקוחות!$A:$C, 3, FALSE)3היא העמודה השלישית בטווח (C)FALSEהיא התאמה מדויקת. כמעט תמיד זה מה שצריך
אם הערך לא נמצא מקבלים #N/A.
פונקציית IF עם VLOOKUP
משלבים את השתיים כדי להחליט לפי מה שנמצא. למשל: אם סטטוס הלקוח (עמודה 4) הוא "פעיל", הצג "לשלוח חשבונית":
=IF(VLOOKUP(A2, לקוחות!$A:$D, 4, FALSE)="פעיל", "לשלוח חשבונית", "")ואם רוצים הודעה ברורה במקום #N/A:
=IFERROR(VLOOKUP(A2, לקוחות!$A:$C, 3, FALSE), "לא נמצא")בגרסאות חדשות של אקסל ובגוגל שיטס יש גם XLOOKUP, שמחפשת בכל עמודה ולא רק בראשונה:
=XLOOKUP(A2, לקוחות!$A:$A, לקוחות!$C:$C, "לא נמצא")פונקציות IF באקסל ובשיטס
תנאי פשוט
=IF(B2>=1000, "לקוח גדול", "לקוח רגיל")כמה תנאים יחד (AND ו-OR)
=IF(AND(B2>=1000, C2="פעיל"), "VIP", "")
=IF(OR(C2="בוטל", C2="הוקפא"), "לא פעיל", "פעיל")כמה מדרגות עם IFS
=IFS(B2>=5000, "A", B2>=1000, "B", TRUE, "C")ספירה וסכום לפי תנאי
=COUNTIF(C:C, "פעיל")
=SUMIF(C:C, "פעיל", B:B)נעילת תאים וקיבוע תאים באקסל
יש כאן שלושה דברים שונים שקוראים להם באותו שם.
1 קיבוע הפניה בנוסחה (סימן דולר)
כשגוררים נוסחה למטה, ההפניות זזות. סימן $ מקבע אותן:
| כתיב | מה מקובע |
|---|---|
A1 | כלום |
$A$1 | עמודה ושורה |
$A1 | רק העמודה |
A$1 | רק השורה |
דוגמה: אחוז מע"מ בתא F1 ומחיר בעמודה B:
=B2*$F$1באקסל בווינדוס, מקש F4 כשהסמן על ההפניה מחליף בין המצבים.
2 הקפאת שורות ועמודות
כדי ששורת הכותרות תישאר גלויה בגלילה, מחפשים את אפשרות ההקפאה בתפריט התצוגה, באקסל ובגוגל שיטס.
3 נעילת תאים מפני עריכה
כדי למנוע מאחרים לערוך תאים, מגדירים הגנה על הגיליון או על טווח. באקסל זה נעשה בהגדרות עיצוב התאים ובהגנת הגיליון, ובגוגל שיטס בתפריט הנתונים תחת גיליונות וטווחים מוגנים. שמות התפריטים משתנים בין גרסאות ושפות, ולכן כדאי לחפש את האפשרות לפי השם.
פיבוט באקסל ובגוגל שיטס
טבלת פיבוט מסכמת אלפי שורות בכמה קליקים, בלי נוסחאות.
איך יוצרים
- מסמנים את הטבלה (עם שורת כותרות)
- בתפריט ההוספה או הנתונים (לפי התוכנה) בוחרים ליצור טבלת פיבוט
- גוררים שדות: "שורות" (למשל נציג), "ערכים" (למשל סכום עסקה), "מסננים" (למשל חודש)
דוגמה: טבלת לידים עם עמודות מקור, נציג וסטטוס. פיבוט עם "מקור" בשורות ו"סטטוס" בעמודות מראה מיד מאיזה ערוץ מגיעים הלידים שנסגרים.
חישוב שעות עבודה בגוגל שיטס
עמודה B שעת כניסה, עמודה C שעת יציאה (בפורמט שעה, למשל 08:30).
שעות כמספר עשרוני
=(C2-B2)*24משמרת שעוברת חצות
=MOD(C2-B2, 1)*24סיכום שעות שעובר 24
מסכמים כרגיל ומעצבים את התא כמשך זמן:
=SUM(D2:D31)בגוגל שיטס בוחרים בעיצוב המספר את הסוג "משך זמן". באקסל משתמשים בעיצוב מותאם אישית [h]:mm.
תשלום לפי שעה (תעריף בתא F1)
=(C2-B2)*24*$F$1ניהול משימות באקסל או בשיטס
תבנית פשוטה שעובדת. העמודות: משימה, אחראי, תאריך יעד, סטטוס ובאיחור? בעמודה "באיחור?":
=IF(AND(C2<TODAY(), D2<>"בוצע"), "באיחור", "")מוסיפים עיצוב מותנה שצובע באדום את "באיחור", ורשימה נפתחת לסטטוס (אימות נתונים בתפריט הנתונים).
מתי גיליון כבר לא מספיק: חיבור ל-CRM באוטומציה
הגיליון מצוין להתחלה. הוא מתחיל להיתקע כשכמה אנשים עורכים יחד, כשצריך תזכורות שקורות לבד, או כשהלידים מגיעים מכמה מקומות.
איך מחברים גיליון ל-CRM ב-3 שלבים:
1 טריגר
שורה חדשה בגוגל שיטס (למשל מטופס) מפעילה תרחיש ב-Make.
2 לוגיקה
Make בודק אם הלקוח כבר קיים ב-CRM לפי טלפון או מייל.
3 פעולה
יוצר או מעדכן את הלקוח ב-CRM, ופותח משימה לנציג.
כך אפשר להמשיך לעבוד עם הגיליון שאתם מכירים, והנתונים נכנסים לבד למערכת. רוצים ללמוד לבנות את זה בעצמכם? ראו קורס אוטומציה.
שאלות ותשובות
למה VLOOKUP מחזירה #N/A?
כי הערך לא נמצא בעמודה הראשונה של הטווח. בדקו רווחים מיותרים, מספר ששמור כטקסט, ושהשתמשתם ב-FALSE להתאמה מדויקת.
מה ההבדל בין VLOOKUP ל-XLOOKUP?
VLOOKUP מחפשת רק בעמודה הראשונה של הטווח ומחזירה עמודה לפי מספר. XLOOKUP מחפשת בכל עמודה, מחזירה מכל עמודה, ומאפשרת ערך ברירת מחדל כשלא נמצא.
למה הנוסחה שלי לא עובדת עם פסיקים?
כנראה ההגדרה האזורית משתמשת בנקודה-פסיק כמפריד. החליפו את הפסיקים ב-;.
מה ההבדל בין קיבוע תאים לנעילת תאים?
קיבוע (סימן דולר) שומר על הפניה בנוסחה כשגוררים אותה. נעילה מונעת מאחרים לערוך את התא כשהגיליון מוגן.
איך מחשבים שעות עבודה כשהמשמרת עוברת חצות?
משתמשים ב-=MOD(C2-B2, 1)*24. הפונקציה MOD מתקנת את ההפרש השלילי.
האם הנוסחאות בגוגל שיטס זהות לאקסל?
ברוב המקרים כן: VLOOKUP, IF, SUMIF ו-COUNTIF נכתבות אותו דבר. יש פונקציות שקיימות רק באחד מהם, כמו QUERY בגוגל שיטס.
מתי לעבור מגיליון ל-CRM?
כשכמה אנשים מטפלים באותם לקוחות, כשלידים נופלים כי אין תזכורות, או כשהגיליון נהיה איטי ומבולגן. ראו הטמעת CRM.
קבעו שיחת ייעוץ חינמית
רוצים שהגיליון יעבוד לבד מול ה-CRM? ספרו לנו על העסק.
ייעוץ ראשוני חינם.
טלפון: 03-3301553
מייל: ofir@digi-transform.com
השאירו פרטים ונחזור אליכם
השיחה הראשונה בחינם ובלי התחייבות. ספרו לנו מה קורה בעסק, ונגיד לכם מה אפשר לעשות.
מעדיפים לדבר עכשיו? 03-3301553 או וואטסאפ
לא בטוחים מה מתאים לכם? שאלון התאמה של 3 דקות ←