היי.

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

לשם הדגמה נניח בסיס נתונים שלהלן. הערכים הייחודיים אותם ארצה לשלוף נמצאים בעמודה "שם", עמודה 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],[שילוב]:[עלות]]

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

סיימנו

תיהנו


היי

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

03/06/2015 12:59:00

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

במקרה זה אפעיל פונקציה INT כדי לקבל ערך שלם שהוא התאריך (תא B5 בתמונה) וההפרש בין הנתון לבין הערך השלם הוא השעה (תא B6 בתמונה). לאחר מכן ניתן לעצב את התא עם התאריך כתאריך (תא B8 בתמונה) והתא עם השעה – כשעה (תא B9 בתמונה).
להלן תמונה עם ההדגמה:

הפרדת הנתון לתאריך ושעה

סיימנו

תיהנו.


היי

ישנו מספר שנרצה לאתר את הכפולה שלו בין שני מספרים אחרים. האם הכפולה אכן נמצאת ביניהם? האם ישנן מספר כפולות ביניהם.

לדוגמה, נניח (לשם הדוגמה בלבד) שרכב כלשהו צריך לעבור טיפול קטן כל 15000 ק"מ וטיפול גדול כל 30000 ק"מ. האם הוא אמור היה לעבור טיפול בין 40000 ל- 50000 ק"מ?

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

INT(50000/15000) – INT(40000/15000)

להלן ההדגמה:

הנחות המנות

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

חישוב מספר הכפולות

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

INT(35000/30000) – INT(25000/30000) = 1

צריך לשים לב שהתוצאה המפיעה בקצה הגבוה של הטווח. כלומר בתא F7 כאשר הטווח הוא E6-E7.

בעמודה G, נוסחה דומה, הפעם עם 15000 והפחתה של מקרים בהם נעשה טיפול בינוני. לדוגמה, בתא G8 הנוסחה היא

INT(50000/15000) – INT(35000/15000) – 0 = 1

 

נוסחאות לשני סוגי החישוב

סיימנו,

תיהנו.