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

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

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

1. מילוי מהיר (Flash Fill): שרביט הקסמים לניקוי נתונים

אם אי פעם ביליתם שעות בפיצול ידני של שמות פרטיים ושמות משפחה, בחילוץ שמות דומיין מכתובות דוא"ל או בעיצוב מחדש של מספרי טלפון, מילוי מהיר (Flash Fill) ידהים אתכם. התכונה, שהוצגה ב-Excel 2013, משתמשת בלמידת מכונה כדי לזהות דפוסים בהזנת הנתונים שלכם ומשלימה אוטומטית את שאר העמודה.

כיצד להשתמש במילוי מהיר:

1. נניח שיש לכם רשימה של שמות מלאים בעמודה A (לדוגמה, "John Doe").
2. בתא B2, הקלידו את השם הפרטי שברצונכם לחלץ: "John".
3. לחצו על Enter כדי לעבור לתא B3.
4. הקישו על קיצור המקלדת CTRL + E.

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

2. INDEX MATCH: השדרוג ל-VLOOKUP

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

הנה מבט על סט נתונים פשוט:

מזהה עובד (עמודה A)	שם (עמודה B)	מחלקה (עמודה C)
1001	שרה ג'נקינס	שיווק
1002	מייקל צ'אנג	כספים
1003	דייוויד סמית'	תפעול

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

=INDEX(B2:B4, MATCH(1002, A2:A4, 0))

פונקציית MATCH מוצאת את מספר השורה של המזהה "1002" (שהוא שורה 2), ופונקציית INDEX מחזירה את הערך בשורה 2 של עמודת השם ("מייקל צ'אנג"). לצלילה עמוקה יותר לשאלה מדוע שילוב זה מועדף על אנליסטים, קראו את המדריך שלנו על INDEX MATCH: שיטת החיפוש העדיפה.

3. Power Query לייבוא נתונים אוטומטי

האם אתם מורידים בקביעות קובצי CSV ממסד נתונים, מוחקים את אותן חמש עמודות, מסננים שורות ריקות ומשנים פורמטים של תאריכים? הפסיקו לעשות זאת באופן ידני. Power Query הוא כלי ETL (חילוץ, המרה, טעינה) מובנה שמתעד את שלבי המרת הנתונים שלכם והופך אותם לאוטומטיים לתמיד.

שלבים לאוטומציה באמצעות Power Query:

1. עברו לכרטיסיית נתונים (Data) ברצועת הכלים של אקסל.
2. לחצו על קבל נתונים (Get Data) ובחרו את המקור שלכם (לדוגמה, מקובץ > מטקסט/CSV).
3. לחצו על המר נתונים (Transform Data) כדי לפתוח את עורך ה-Power Query.
4. בצעו את ניקוי הנתונים שלכם (הסרת עמודות, סינון שורות, שינוי שמות כותרות). אקסל מתעד כל שלב בחלונית הצדדית.
5. לחצו על סגור וטען (Close & Load).

בחודש הבא, כשתקבלו קובץ CSV חדש, פשוט דורסו את הקובץ הישן, לחצו קליק ימני על טבלת האקסל שלכם, ולחצו על רענן (Refresh). הנתונים שלכם ינוקו מיד. שלטו בכלי זה, ותוכלו באמת להשתמש ב-Power Query: ייבוא והמרת נתונים כמו מקצוענים.

4. עיצוב מותנה עם נוסחאות מותאמות אישית

רוב המשתמשים יודעים כיצד להשתמש בעיצוב מותנה (Conditional Formatting) כדי להדגיש תאים שגדולים ממספר מסוים. אך ניתן להשתמש בנוסחאות מותאמות אישית כדי להדגיש שורות שלמות על סמך הערך של תא בודד.

לדוגמה, אם ברצונכם להחיל "פסים כמו זברה" (הדגשת כל שורה שנייה כדי להקל על קריאת טבלאות רחבות) מבלי להסתמך על סגנונות הטבלה של אקסל, תוכלו להשתמש בפונקציות MOD ו-ROW.

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

=MOD(ROW(), 2)=0

בחרו צבע מילוי ולחצו על אישור (OK). כעת, כל שורה זוגית בגיליון שלכם תודגש אוטומטית. זוהי רק דרך אחת למנף את ה-עיצוב המותנה: הדמיית נתונים עם צבעים לשיפור הדוחות שלכם.

5. כלי פריסה בטבלת ציר ללוחות מחוונים אינטראקטיביים

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

כדי להוסיף כלי פריסה, לחצו בכל מקום בתוך טבלת הציר הקיימת שלכם. עברו לכרטיסיית ניתוח טבלת ציר (PivotTable Analyze) ולחצו על הוסף כלי פריסה (Insert Slicer). סמנו את התיבות של השדות שברצונכם לסנן לפיהם (למשל, "אזור" או "קטגוריית מוצר"). קבוצה של כפתורים מעוצבים תופיע בגיליון שלכם. תוכלו אפילו לחבר כלי פריסה אחד למספר טבלאות ציר על ידי לחיצה ימנית עליו ובחירה ב-חיבורי דוחות (Report Connections).

6. תצוגות מותאמות אישית לשמירת ההגדרות שלכם

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

הגדירו את הגיליון שלכם בדיוק כפי שאתם אוהבים לראות אותו (החילו את המסננים הספציפיים שלכם, הסתירו את העמודות שאינכם צריכים, והגדירו את הזום ל-85%). עברו אל כרטיסיית תצוגה (View) ולחצו על תצוגות מותאמות אישית (Custom Views). לחצו על הוספה (Add), וקראו לזה "התצוגה שלי". כעת, לא משנה באיזה מצב עמית לעבודה ישאיר את הגיליון, תוכלו לשחזר באופן מיידי את הפריסה האישית שלכם בשתי לחיצות בלבד.

7. קווי זעיר (Sparklines) לתרשימי מיני בתוך תאים

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

כדי להשתמש בהם, בחרו בתא הריק שבו תרצו שהתרשים יופיע. עברו אל כרטיסיית הוספה (Insert), חפשו את הקבוצה קווי זעיר (Sparklines), ובחרו קו או עמודה. אקסל יבקש מכם את "טווח הנתונים" (בחרו את התאים המכילים את הנתונים ההיסטוריים). לחצו על אישור, ותראו קו מגמה זעיר שיופיע ישירות בתוך התא.

8. רשימות נפתחות לאימות נתונים למניעת שגיאות

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

כיצד ליצור רשימה נפתחת:

1. בחרו את התאים שברצונכם להגביל.
2. עברו אל כרטיסיית נתונים (Data) ולחצו על אימות נתונים (Data Validation).
3. תחת התפריט הנפתח "אפשר", בחרו רשימה (List).
4. בתיבה "מקור", תוכלו לבחור טווח תאים המכיל את הרשימה המאושרת שלכם, או להקליד את הפריטים מופרדים בפסיקים (לדוגמה, Pending, Approved, Rejected).
5. לחצו על אישור.

כעת, המשתמשים יכולים לבחור רק מתוך האפשרויות המוגדרות מראש, מה שמבטיח עקביות מושלמת עבור נוסחאות ה-SUMIF וה-COUNTIF שלכם בהמשך.

9. הדבקה מיוחדת: בצע חילוף (Transpose)

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

פשוט סמנו את הנתונים שאתם רוצים להפוך והעתיקו אותם (CTRL + C). לחצו קליק ימני על תא היעד שבו תרצו שהפריסה החדשה תתחיל, רחפו מעל הדבקה מיוחדת (Paste Special), ולחצו על הסמל עם שני החצים המצביעים ימינה ולמטה, או סמנו במפורש את התיבה שאומרת בצע חילוף (Transpose). השורות שלכם יהפכו לעמודות, והעמודות שלכם יהפכו לשורות.

10. חתירה למטרה לניתוח "מה-אם" מיידי

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

תארו לעצמכם שיש לכם מודל רווח פשוט. הרווח שלכם מחושב כ-(Price * Units Sold) - Fixed Costs. אתם יודעים את המחיר והעלויות הקבועות שלכם, ואתם רוצים לדעת בדיוק כמה יחידות עליכם למכור כדי להגיע ליעד רווח של 50,000$.

עברו אל כרטיסיית נתונים (Data), לחצו על ניתוח מה-אם (What-If Analysis), ובחרו חתירה למטרה (Goal Seek). תופיע תיבת דו-שיח קטנה עם שלושה קלטים:

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

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

קחו את כישורי האקסל שלכם לשלב הבא

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

אם אתם מוצאים את עצמכם מתקשים לכתוב פונקציית IF מקוננת או להבין חיפוש מסובך, אל תבהו בשורת נוסחאות ריקה. עם ExcelGPT, אתם יכולים פשוט להקליד את מה שאתם רוצים להשיג בשפה פשוטה, והמערכת תיצור עבורכם את הנוסחה המושלמת ונטולת השגיאות באופן מיידי. אימוץ של בינה מלאכותית מהווה קיצור דרך עצום - למדו כיצד להשתמש ב-ChatGPT לאקסל: כתבו נוסחאות עם AI כדי להאיץ את זרימת העבודה שלכם אף יותר.

שאלות נפוצות

מהו הטריק הטוב ביותר באקסל למתחילים לחלוטין?

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

האם INDEX MATCH באמת טוב יותר מ-VLOOKUP?

כן. בעוד ש-VLOOKUP קל יותר ללמידה בהתחלה, INDEX MATCH עדיף בהרבה מכיוון שהוא יכול לחפש לכל כיוון, הוא מעבד סטים גדולים של נתונים מהר יותר, והכי חשוב - הנוסחה לא תישבר אם תוסיפו או תמחקו עמודות בטבלת העזר שלכם.

האם תצוגות מותאמות אישית ישנו את הנתונים עצמם?

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

האם בינה מלאכותית יכולה לעזור לי ללמוד טריקים באקסל מהר יותר?

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