האתגר של מחירונים דינמיים

כל חברה שמנהלת מחירים משתנים לאורך זמן מכירה את הבעיה: לקוח מזמין מוצר היום, אבל מה המחיר הנכון? האם הוא זה שהיה תקף בחודש שעבר? החודש? מתי בדיוק?

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

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

התרחיש העסקי

דמיינו טבלת מחירון עם הנתונים הבאים:

כפי שרואים, למוצר A01 יש ארבעה מחירים שונים לאורך השנה. בנוסף, המחיר האחרון "פתוח" – אין לו תאריך סיום, מה שמשמעותו שהוא תקף מ-01/07/2024 ואילך ללא הגבלת זמן.

האתגר: כשמגיעה הזמנה חדשה, כיצד Excel "יודע" איזה מחיר להביא אוטומטית?

הפתרון: הנוסחה המלאה

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

=XLOOKUP(1,(B3=PriceList[ProductCode])*(C3>=PriceList[StartDate])*((C3<=PriceList[EndDate])+(PriceList[EndDate]="")),PriceList[Price],"Not Found")

למראה הנוסחה אל תירתעו – מיד נפרק אותה לחלקים פשוטים.

פירוק הנוסחה – צעד אחר צעד

שלב 1: זיהוי קוד המוצר

(B2=PriceList[ProductCode])

הנוסחה עוברת על כל שורה בטבלת המחירון ובודקת: האם קוד המוצר בתא B2 תואם לקוד המוצר בשורה זו? כל שורה תקבל TRUE (1) או FALSE (0).

שלב 2: בדיקת תאריך ההתחלה

(C2>=PriceList[StartDate])

בודקת שתאריך ההזמנה (C2) מאוחר או שווה לתאריך תחילת תוקף המחיר. זה מוודא שאנחנו לא מביאים מחיר שעדיין לא נכנס לתוקף.

שלב 3: בדיקת תאריך הסיום – החלק המתוחכם!

((C2<=PriceList[EndDate])+(PriceList[EndDate]=""))

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

• חלק א: C2<=PriceList[EndDate] – בודק שתאריך ההזמנה לפני תאריך סיום המחיר
• חלק ב: PriceList[EndDate]="" – בודק אם תאריך הסיום ריק (מחיר פתוח לנצח)

אם אחד מהם מתקיים – המחיר רלוונטי. זה הפתרון לבעיית המחירים ה"פתוחים"!

שלב 4: חיבור כל התנאים

(תנאי1)*(תנאי2)*(תנאי3)

הכפל (*) פועל כמו "וגם" – כל שלושת התנאים חייבים להתקיים. אם כולם TRUE, התוצאה היא 1. כל תנאי שהוא FALSE מביא את התוצאה ל-0.

שלב 5: XLOOKUP מוצא את ה-1

XLOOKUP מחפש את הערך 1 במערך התנאים. ברגע שנמצא 1 – כלומר שורה שבה כל התנאים מתקיימים – הוא מחזיר את המחיר המתאים. אם אין התאמה, הוא מחזיר "Not Found" במקום שגיאה מבאישה.

דוגמאות מעשיות

דוגמה 1: הזמנה בתוך טווח תאריכים רגיל

דוגמה 2: הזמנה במחיר פתוח (ללא תאריך סיום)

דוגמה 3: הזמנה שאין לה מחיר

אם תאריך ההזמנה הוא 15/12/2023 (לפני שהמחירון בכלל התחיל), אף שורה בטבלה לא תחזיר 1, והנוסחה תחזיר "Not Found" – הודעה ברורה שאין מחיר תקף.

שגיאות נפוצות ואיך להימנע מהן

להלן שלוש השגיאות הנפוצות ביותר בשימוש בנוסחה זו:

שאלות ותשובות נפוצות

ש: מה קורה אם שני מחירים תקפים לאותו תאריך?

ת: XLOOKUP מחזירה תמיד את ההתאמה הראשונה שעומדת בקריטריונים – כלומר את השורה העליונה ביותר שנמצאה בטווח החיפוש. לכן חשוב מאוד למיין את טבלת המחירון לפי קוד מוצר ואז לפי תאריך התחלה, ולוודא שאין חפיפות בין תקופות. לאיתור חפיפות ניתן להוסיף עמודת COUNTIFS שסופרת כמה רשומות קיימות עבור כל שילוב של קוד מוצר + תאריך. ערך גדול מ־1 מצביע על תקלה שיש לטפל בה.

ש: האם הנוסחה עובדת עם Excel 2019?

ת: לא. הפונקציה זמינה רק ב־Excel 365 וב־Excel 2021. בגרסאות קודמות יש להשתמש בשילוב INDEX+MATCH שמדמה את ההתנהגות, אבל עם נוסחאות מסורבלות יותר. מומלץ לעבוד בגרסה תומכת – XLOOKUP מוסיפה יציבות, קריאות ויכולות שלא קיימות בפתרונות הישנים.

ש: האם אפשר להשתמש בשעות ולא רק תאריכים?

ת: כן, בהחלט. Excel מייצג תאריכים ושעות כמספרים עשרוניים – החלק השלם הוא התאריך, והחלק העשרוני הוא השעה. לכן הנוסחה תעבוד גם עם ערכי תאריך+שעה מלאים, כמו 01/01/2024 14:30. רק הקפידו שכל התאים בשתי הטבלאות מפורמטים באותה שיטה.

ש: מה אם אני רוצה לראות גם את טווח התאריכים של המחיר שנמצא?

ת: XLOOKUP יכולה להחזיר מספר עמודות בבת אחת. במקום PriceList[Price] בלבד, תשתמשו בטווח:

PriceList[StartDate]:PriceList[Price]

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

ש: האם הנוסחה עובדת גם ב-Google Sheets?

ת: כן! Google Sheets תומכת ב-XLOOKUP עם תחביר זהה לגמרי. הנוסחה תעבוד ללא שינויים, אם כי ייתכנו הבדלים קלים בפורמט התאריכים בין הסביבות. בדקו שהתאריכים מוכרים כנכון גם ב-Sheets.

ש: איך אני מוסיף מחיר חדש בלי לפגוע בנוסחה?

ת: כאשר הנתונים נמצאים בתוך טבלת Excel אמיתית (Ctrl+T), כל הוספת שורה בתחתית הטבלה מרחיבה את הטווח אוטומטית. כדי להוסיף מחיר חדש: יוצרים שורה חדשה עם קוד מוצר, תאריך התחלה, תאריך סיום (אם יש), ומחיר. חשוב לסגור את התקופה הקודמת על ידי מילוי תאריך סיום לרשומה הישנה.

טיפים חשובים לעבודה יעילה

• השתמשו בטבלאות Excel (Ctrl+T) – הנוסחאות מתעדכנות אוטומטית כשמוסיפים נתונים
• מיינו את המחירון לפי קוד מוצר ואחר כך לפי תאריך התחלה
• הוסיפו Conditional Formatting לשורות עם EndDate ריק – קל לזיהוי
• גבו את הקובץ לפני כל עדכון מחירים גדול
• הוסיפו עמודת LastUpdate למחירון לתיעוד שינויים
• בדקו תמיד כמה תוצאות ידנית כדי לוודא שהנוסחה עובדת נכון

סיכום

הנוסחה שהצגנו פותרת בצורה אלגנטית ויעילה את אחת הבעיות הנפוצות ביותר בניהול מחירונים. היא מאפשרת:

• שליפה אוטומטית של המחיר הנכון לפי קוד ותאריך
• טיפול חכם במחירים פתוחים ללא תאריך סיום
• הודעת שגיאה ברורה כשאין מחיר תקף
• הרחבה קלה לתרחישים מורכבים יותר (לקוח, כמות, מטבע)