היי

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

דוגמה 1 – מיון טבלת מספרים או תאריכים באופן דינאמי

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

רשימה של תאריכים למיון

המיון ייעשה באמצעות פונקציה LARGE או SMALL. לשתיהן יש פרמטר שני שהוא האינדקס של הנתון בתוך הרשימה הממוינת. לפיכך, האינדקס תלוי בעצמו במיון. אולם הערך הראשון יקבל אינדקס 1.

עמודה ראשונה היא אינדקס של הערך הממוין ועמודה שניה היא התוצאה

 

הנוסחה ליצירת אינדקס מבוססת בעצמה על עמודה D. להלן הנוסחה שיישמתי בתא C3 שמניב ערך 2:

IF(SUM(C2)=0,1,COUNTIFS($A$2:$A$9,D2)+SUM(C2))

אם האינדקס הוא הראשון אחרי הכותרת, התנאי של הפונקציה IF מתקיים, האינדקס יהיה 1. אחרת, הפונקציה סופרת את מספר ההופעות של התוצאה בשורה הקודמת (במקרה של תא C3, הבדיקה היא על הערך בתא D2). לאחר מכן היא מחברת את התוצאה של COUNTIFS עם האינדקס בשורה הקודמת (במקרה של תא C3, האינדקס הקודם נמצא בתא C2).

בעמודה D מופיעה פונקציה SMALL, הממיינת מנמוך לגבוה. הנמוך ביותר הוא ערך עם אינדקס 1. הנוסחה בתא D3 שמניבה ערך 01/02/2015 היא:

SMALL($A$2:$A$9,C3)

 

דוגמה 2 – מיון טבלה עם ערכי טקסט ומציאת ערכים ייחודיים

בוגמה זו צריך לסכם לכל מחלקה את הסכומים שלה למעט מחלקת "חוץ":

טבלת נתונים של טקסט וסכומים

בשלב הראשון יזוהה אם המחלקה בעמודה A כבר הופיעה מקודם. אם לאו – יינתן לה מספר סידורי בעמודה C. "חוץ" תקבל סידורי 0.

יצירת סידורי וטבלה ערכים יחודיים עם סיכומים

הנוסחה שיושמה בעמודה C, תא C18:

IF(A18=חוץ",0",IF(COUNTIFS($A$16:$A18,A18)=1,MAX($C$16:$C17)+1,VLOOKUP(A18,$A$16:$C17,3,0)))

הנוסחה מבוססת על טווח אשר מקובע על שורת הכותרת ומסתיים בשורה בה נמצאת הנוסחה השוטפת. הבדיקה היא אם הערך בעמודה A הופיע לפני כן. אם הופיע, הרי שתוצאת COUNTIFS עולה על 1. המספר הסידורי הבא נוצר על ידי  1 + פונקציה MAX עם טווח המתחיל מהכותרת ועד לשורה הקודמת. אם הערך חוזר על עצמו, אז צריך לאתר את המספר הסידורי שלו. זה נעשה באמצעות VLOOKUP שגם בה הטווח הוא החל מהכותרת ועד לשורה הקודמת.

אחרי שנבנה סדר רץ, צריך לבנות טבלה המייצרת את התוצאות. הנוסחה בעמודה E דומה לנוסחה בעמודה C בדוגמה הקודמת. עמודת הערכים הסידוריים שבנינו היא העמודה המספרית שתמוין. האינדקס הראשון הוא 1. הפעם המיון נעשה באמצעות פונקציה LARGE, כך שסידורי 0 (שאותו קיבלה "חוץ") יהיה האחרון ברשימה ואים הוא לא צריך להופיע בטבלה, אפשר פשוט להשמיט שורה זו. הנוסחה בעמודה F דומה לנוסחה בעמודה D בדוגמה הוקדמת, רק במקום SMALL הפונקציה היא LARGE.

בהתבסס על תוצאה בעמודה F, אפשר עתה לאתר את הערך המתאים מתוך עמודה A ולסכם על בסיס F את הערכים בעמודה B. הנוסחאות עבור שורה 18:

IF(SUM(E17)=0,1,COUNTIFS($C$17:$C$26,F17)+E17) E18 =
LARGE($C$17:$C$26,E18) F18 =
INDEX($A$17:$A$26,MATCH(F18,$C$17:$C$26,0),1) G18 =
SUMIFS($B$17:$B$26,$C$17:$C$26,F18) H18 =

 

סיימנו.

תיהנו.


היי

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

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

בטבלה יש שתי עמודות של תנאים, עמודת מחלקה ושם, ועמודת הנתונים אותם צריך לשאוב לטבלת הדיווח. ניתן לראות שבעמודת התנאי השני, "Name", ישנם מקרים שהתנאי אינו קיים (חלק מהתאים מלאים וחלק ריקים). משמע בטבלת הדיווח צריך לברר אם ישנו תנאי שני ואם ישנו – לבדוק אותו. להלן הצגה של שתי הטבלאות:

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

הפונקציה בעמודה Result צריכה לבדוק אם יחד עם תנאי הראשון, "Department", ישנו תנאי שני, "Name", ואם ישנו – לבדוק אותו. השתמשתי בפונקציה SUMIFS:

הצגת הנוסחה ששואבת נתונים מעמודת נתונים לעמודת תוצאות

הנוסחה:

SUMIFS(data_t[Amount], data_t[Department], [@Department], data_t[Name], IF( IFERROR( VLOOKUP([@Department], data_t[[Department]:[Name]], 2, 0), "")<>"", [@Name], ""))

האופן בו הנוסחה בודקת קיומו של תנאי שני היא באמצעות VLOOKUP. הפונקציה מחפשת לפי עמודת "Department" את התוצאה בעמודה "NAME". אם הפונקציה מחזירה ערך כגון "דורין" או "דני", משמע התנאי השני קיים. אם היא מחזירה "", משמע אין תנאי שני. אם מחזירה N/A, משמע לא נמצא ערך בעמודה "Department". בשביל מקרה זה נוסף IFERROR עם תוצאה "". אם התוצאה של IF היא "", פונקציה SUMIFS תחפש "" כתנאי שני ותמצא מקרים בהם מתקיים התנאי הראשון אך אין תנאי שני.

תיהנו.


היי.

נניח ארצה לשלוף את הערכים הייחודיים מתוך עמודה של נתונים.

לשם הדגמה נניח בסיס נתונים שלהלן. הערכים הייחודיים אותם ארצה לשלוף נמצאים בעמודה "שם", עמודה A:

טבלת נתונים המורכבת משתי עמודות. בעמודה הראשונה נמצאים הערכים שאותם נרצה לשלוף

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

פתרון המצריך מיון:

  1. מיינתי את הטבלה הנ"ל לפי עמודה "שם".
  2. הוספתי בעמודה C פונקציה IF, אשר תתן ערך סידורי אם ערך באותה השורה בעמודה A שונה ביחס לערך בתא שמעליו. אחרת 0.
  3. את הערך הסידורי ייצרתי באמצעות COUNTIFS (בעמודה C) וכחלופה – באמצעות SUM (בעמודה D).

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

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

באמצעות פונקציה COUNTIFS: הטווח הנבדק הוא הטווח שמעל לתא הנדון. התנאי של הפונקציה הוא "0<". כלומר, ביקשתי לספור את כל הערכים שגדולים מאפס בעמודת C שמעל לתא הנדון ולהוסיף לתוצאה 1. נניח שאני נמצא בתא C2. בטווח C1:C1 אין אף תוצאת פונקציה IF שגדולה מ-0. (בתא C1 הערך הוא "אינדקס1" והוא לא ערך מספרי מעל 0). אם אני נמצא בתא C8, בטווח C1:C7 ישנו ערך מספרי 1 בתא C2 וערך מספרי 2 בתא C5 והיתר הם אפסים או טקסט. משמע, תוצאה של COUNTIFS תהיה 2. משמע, האינדקס בתא C8 הוא 2+1 = 3.

באמצעות בפונקציה SUM: סכום האינדקסים מעל לתא שבנדון, חילוץ ספירתם והוספת 1. סכום האינדקסים הרצים, לכאורה, הוא סכום של סדרה חשבונאית שערכיה גדלים ב-1. כלומר, 1, 2, 3, 4, …

S = (2*1+(n-1)*1)*n/2

כאשר n הוא מספר הערכים הגדולים מאפס בעמודה C עד לתא הנדון. חילוץ של n דורש פתרון משוואה ריבועית ונראה שתוצאתה היא:

n=((8*S+1)^0.5-1)/2

וזה שימש כפרמטר השני של פונקציה IF בעמודה D בתוספת 1.

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

בעמודה F בניתי עמודה של אינדקס רץ. בעמודה G לכל אינדקס אותר הערך הנמצא בעמודה "שם". עשיתי זאת באמצעות שילוב של INDEX ו-MATCH:

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

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

פתרון ללא מיון:

התבססתי על פונקציה IF. בדוגמה שבתמונה שמטה, נניח שאנו נמצאים בתא C4, כתנאי של IF בדקתי את מיקומו של הערך שבתא A4 בטווח A1:A3. איתור המיקום נעשה באמצעות MATCH. אם MATCH לא מצאה את הערך שמחפשים היא מחזירה #N/A. פונקציה ISNA מחזירה TRUE אם הפרמטר שלה הוא #N/A ו-FALSE אם אחרת. לפיכך, אם ערך שבתא A4 טרם הופיע בטווח A1:A3, הרי שפונקציה MATCH תחזיר #N/A. בעקבות זאת, פונקציה ISNA תחזיר TRUE וזה יהיה תנאי הבדיקה בפונקציה IF. ההמשך כמו בפתרון הקודם. ספרתי אינדקסים בטווח שמעל לתא שבנדון, C1:C3, והוספתי לו 1. אם תוצאה של ISNA היא FALSE, אז , IF צריכה להחזיר 0.

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

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

פתרון באמצעות טבלת ציר (Pivot Table):

יצרתי טבלת ציר, כאשר באזור "שורות" בטלה שמתי את השדה "שם":

יצירת רשימת ערכים ייחודיים באמצעות טבלת ציר. שמתי את השדה "שם" באזור "שורה" של טבלת ציר

סיימנו.

תיהנו.


היי

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

נניח טווח של נתונים:

טבלה של נתונים ל6 חודשים עוקבים עם שתי עמודות סיכומים רבעוניים, אחד אחרי 3 חודשים ושני אחרי 6 חודשים. בתחתית הטבלה ישנם סיכומים כוללים לכל עמודה. לעמודות רבעוניות הסיכום באמצעות SUBTOTAL

בשורה 5 ישנם סיכומים של 3 התאים מעליהם. אולם בתא E5 ובתא I5 הסיכום נעשה באמצעות פונקציה SUBTOTAL ולא באמצעות SUM. עתה אסכם את שורה 5 באמצעות SUM ובאמצעות SUBTOTAL:

טבלה קודמת ובנוסף בתא B9 נוסף סיכום באמצעות פונקציה SUM של שורה 5. תוצאותו סיכום הן של הנתונים והן של סיכומי ביניים. בתא B10 נוסף סיכום באמצעות SUBTOTAL, תוצאתו סיכום של הנתונים בלבד ללא סיכומי ביניים.

ניתן לראות שפונקציה SUM סיכמה הכל, כולל סיכומי ביניים. פונקציה SUBTOTAL התעלמה מסיכומים שנעשו באמצעות פונקציה SUBTOTAL.

סיימנו.

תיהנו.


היי

בישראל התאריך נרשם לעתים קרובות עם הפרדה באמצעות נקודות בין הספרות, כגון 22.11.15. אקסל מקבל תאריך המופרד בקווים נטויים, כגון 22/11/2015.

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

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

  1. נחליף נקודות (.) בקווים נטויים (/) באמצעות SUBSTITUTE.
  2. נמיר את התוצאה מטקסט לתאריך על ידי DATEVALUE

להלן ההדגמה:

המרת תאריך מנקודות לקווים נטוים

סיימנו.

תיהנו.


היי

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

מציאת מילה אחרונה במשפט

 

שלב א': בתא A2 נמצא המשפט "שרה שרה שיר שמח". בתא C2 נחשב את אורך המשפט באמצעות פונקציה LEN.

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

שלב ג': נספור את הטקסט החדש בתא C3. ההפרש בין ספירה ראשונה לספירה שניה הוא מספר הרווחים. מופיע בתא C4.

שלב ד': נחליף את הרווח השלישי בתו אחר שבטוח אינו קיים במשפט. במקרה זה החלפתי בתו "A". ההחלפה שוב באמצעות SUBSTITUTE, כאשר מספר מופע להחלפה הוא מספר הרווחים שנמצא.

שלב ה': נאתר את מיקומו של התו החדש במשפט החדש באמצעות SEARCH. המיקום שיימצא הוא תחילתה של המילה האחרונה.

שלב ו': נשלוף את המילה האחרונה באמצעות פונקציה RIGHT, אשר שולפת טקסט באורך מוגדר מסוף המשפט. אורך המילה האחרונה הוא ההפרש בין תוצאה בתא C2 לבין תוצאה בתא C5.

תוצאת העבודה מופיעה בתא A6.

סיימנו.

תיהנו.

 


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

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

שתי טבלאות למיזוג

בטבלאות לעיל לא לכל העובדים יש עלות בכל המחלקות וכי בטבלה השניה לא קיימים כל החודשים 1.2014 – 4.2014. הזהות היחידה בין שתי הטבלאות היא שישנן מחלקות, חודשים ותעודות זהות.

ניתן ליצור טבלת ציר משתי טבלאות או יותר. אשף PivotTable ו-PivotChart > טווחי איחוד מרובים > צור שדה עמוד בודד עבורי > בוחרים טבלה 1 ולוחצים הוסף לאחר מכן בוחרים טבלה 2 ולוחצים הוסף. התוצאה שקיבלתי:

איחוד שני טווחים בטבלת ציר

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

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

טווח נתונים עם עמודת שילוב שתשמש כטווית

עתה יצרתי טבלת ציר על שתי העמודות האחרונות בלבד – על עמודת שילוב ועל עמודת נתונים (עלות וש.נ.):

טבלת ציר הבנויה על עמודת שילוב ועמודת נתונים

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

t1_t[[#All],[שילוב]:[עלות]]

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

סיימנו

תיהנו