היי

נניח יש לנו עמודה של ערכים שנרצה לחלקה לקבוצות בהתאם לגודל. כגון קבוצה 1 ציונים מ-0 עד 40, קבוצה 2 ציונים מ-40 עד 60 וכד'. לאחר מכן נרצה לנתח כל קבוצה, כגון מספר ערכים בה, ממוצע, סטיית תקן וכו'.

לצורך ההדגמה נניח עמודה של יתרות חוב עם 564 ערכים:

הקבוצות יהיו כדקלמן:

הדרך לדעת מהו הסף התחתון שצריך לתפוס היא פיקנציה MIN על כל טווח הנתונים פחות 1. כך שהערך שיתקבל יהיה בוודאות קטן יותר מהערך הנמוך ביותר בטווח הנתונים. כך התקבל המספר 937.616-.

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

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

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

סיימנו.

תיהנו.

מודעות פרסומת

היי

מסתבר שתרשים PIVOT CHART מסוגל לספק מענה לדרישות שתרשים רגיל לא יכול לספק.

1. גרף מתרחב אוטומטית

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

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

להלן בסיס הנתונים שלנו:

עתה נצור שם לבסיס נתונים זה, אשר יהיה דינאמי. זאת נעשה עם OFFSET ו- COUNTA:

הוספה > שם > הגדרה. נקרא לו data1. פונקציה OFFSET לוקחת טווח של נתונים. על מנת שהוא יהיה דינאמי, גובה הטווח יקבע על ידי פונקציה COUNTA על העמודה A (טווח A:A). ההבדל בין COUNT ל- COUNTA הוא שהראשונה אינה סופרת טקסט והשניה כן. אנו רוצים שהטווח יכלול את כל השורות, גם הכותרת וגם השורה האחרונה. לכן צריך COUNTA. תשימו לב ל-$ משמואל ל-A.

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

באשף טבלת ציר או תרשים ציר נרשום כבסיס הנתונים את השם data1:

בניתי טבלת ציר שנראית כמו בסיס הנתונים המקורי:

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

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

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

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

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

השגנו את מבוקשנו. הגרף התרחב מייד כדי לכלול את הנתונים החדשים.

2. תווית נקודה

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

להלן בסיס הנתונים שלי. העמודה ממוצע הינה פרי פונקציה  AVERAGE על הטווח C2:C10.

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

עתה נוסיף גרף PIVOT CHART, כפי שהראתי לעיל. למרות שבחרתי בסוג תרשים "קווים על שני צירים", אוכל למקם את שני הגרפים על ציר אחד, על ידי קליק ימני על גרף הממוצע > עיצוב סדרת נתונים > ציר > ציר ראשי.

עתה נוסיף את התוויות לגרף שווי שוק. נעשה זאת על ידי קליק ימני על גרף שווי שוק > עיצוב סדרת נתונים >  תוויות נתונים > לשים דגל ב-"שם קטגוריה".

התוצאה נראית כך:

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

 

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

סיימנו.

תיהנו.

 


היי

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

במצב עיצוב רגיל אקסל מעלים את האפסים המקדימים ומציג את המספר כמספר רגיל.

נוכל בכל זאת לגרום לו להציג את המספר עם האפסים המקדימים באמצעות עיצוב תאים > מספר > מותאם אישית

וכאן רושמים פשוט:

 

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

 

תיהנו.


היי

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

כשלב ראשון, ייתכן שמספר זהות (ללא ספרת ביקורת) שברשותנו נמוך מ-8 ספרות. על מנת להמשיך הלאה, אנו נשלים את המספר ל-8 ספרות, על ידי הוספת אפסים מקדימים.

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

בתמונה להלן, בתא B1 מופיע מספר ת.ז. שברשותנו. בתא B2 נעשית ההשלמה ל-8 ספרות.

עתה, נסדר את ספרות תעודת זהות בת 8 ספרות ב-8 תאים, שכל תא יכלול ספרה אחת. לצורך סידור הספרות ועל מנת שאוכל לגרור, בניתי בשורה 4 מספר סידורי רץ של ספרות. בשורה 5 אני גוזר ספרה אחת מתוך מספר ת.ז. לפי מספר סידורי המופיע בשורה 4. הגזירה נעשית באמצעות פונקציה MID, כאשר הפרמטר השני שלה הוא המספר הסידורי שבשורה 4.

עתה כל אחד מהערכים שבשורה 5 יש להכפיל ב-1 או 2 לפי הסדר המופיע בשורה 6:

עתה בכל מכפלה שהיא מספר דו ספרתי, יש לחבר את הספרות. התוצאה מופיעה בשורה 8. ניתן לראות שהשארית של חלוקה ב-9 שווה לסכום ספרות של המספר. כמובן, למעט המקרה שבו המספר הדו ספרתי הוא כפולה של 9 בעצמו (שאז סכום הספרות הוא 9) או שהמספר הוא 0. פונקציה MOD מחשבת שארית.

עתה יש לחבר את כל סכומי הספרות. הסיכום מופיע בתא B9. ספרת הביקורת היא המשלים של הסכום שהתקבל לכפולה של 10 הקרובה ביותר אליו והגדולה ממנו. לדוגמה, אם הסכום הוא 26, כפולה של 10 הקרובה אליו והגדולה ממנו היא 30. ההפרש בין השניים הוא 4. בפועל האם זה 26 ו-30 או 16 ו-20, אינו חשוב. מה שחשוב זה הספרה 6 וההפרש בינה לבין 10. ולכן הנוסחה להלן מבוססת על רעיון זה. ההסרה של הספרה 2 היא על ידי חישוב שארית של חלוקת הסכום ב-10.

סיימנו.

תיהנו.

מקורות:

  1. http://he.wikipedia.org/wiki/%D7%A1%D7%A4%D7%A8%D7%AA_%D7%91%D7%99%D7%A7%D7%95%D7%A8%D7%AA
  2. http://www.orianit.edu-negev.gov.il/elishevapc/sites/homepage/iris%5CregFiles%5C%D7%90%D7%9C%D7%92%D7%95%D7%A8%D7%99%D7%AA%D7%9D%D7%91%D7%93%D7%99%D7%A7%D7%AA%20%D7%AA%D7%A7%D7%99%D7%A0%D7%95%D7%AA%20%D7%A9%D7%9C%20%D7%A1%D7%99%D7%A4%D7%A8%D7%AA%20%D7%94%D7%91%D7%99%D7%A7%D7%95%D7%A8%D7%AA.doc

היי

שני פתרונות שמשמשים אותי ויעזרו לכם, אם טרם הכרתם:

1. אם יש לכם פונקציה שמקבלת טווח של תאים, כגון MATCH, אזי במקום לבחור את הטווח על ידי גרירה של העכבר, ניתן להשתמש בצירוף של SHIFT+CTRL+חץ לכיוון המתאים, או HOME או END. יש לשים לב לכך שקודם לוחצים על SHIFT, ממשיכים להחזיק ולוחצים על CTRL ולאחר מכן על החץ. אפשר להמשיך בהרחבת הטווח על ידי לחיצה נוספת על החץ.

2. הדבקה מהירה של נוסחה שבנינו על גבי טווח – גרירה לעיתים מעיקה ושתי לחיצות על הפינה השמאלית התחתונה של התא לעתים מדביקה יותר מדי ולעתים קצת מדי (אם אקסל מסתכל על העמודה הימנית הסמוכה והיא לא רציפה בנתונים). הפתרון המוצע הוא ללכת לתא האחרון בטווח ההדבקה. לרשום בו משהו, נניח ספרה 1. לחזור לתא עם הנוסחה, לעשות העתק, לבחור את טווח ההדבקה על ידי צירוף של CTRL+SHIFT+חץ לכיוון המתאים. הטוח יתפוס הכל עד לתא שבו רשמנו 1. עתה לעשות הדבקה.

סיימנו.

תיהנו.


היי

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

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

נניח בסיס נתונים של שתי עמודות, כדלקמן:

אני מעוניין לסכם את הערכים בעמודת "סכום ההנחה" אשר "קוד ההנחה" שלהם הוא 61, 62 או 63. אני בונה בעמודה D את הערכים הנדרשים:

עתה נשתמש בפונקציה MATCH. פונקציה MATCH מחזירה את מיקומו של הפרמטר אותו מחפשים בטווח בו מחפשים. לדוגמה, אם אני מחפש ערך 62 בטווח D1:D4, אנו נקבל תוצאה 3. הפרמטר השלישי של הפונקציה הוא האופן בו מחפשים: ערך 0 אומר שאני מחפש התאמה מדוייקת, 1 או 1- נותנים התאמה מקורבת מלמטה או מלמעלה. לענייננו, אני צריך התאמה מדוייקת. במקרה שהערך לא נמצא, הפונקציה תחזיר N/A#. זו תהיה התוצאה אם נחפש ערך 71 וזו התכונה שאנו צריכים. הפונקציה MATCH שנבנה בתא F2 תחפש את הערך בתא A2 בטווח D1:D4. אם הערך נמצא בטווח זה, MATCH תחזיר את מיקומו, אם לא נמצא – תחזיר N/A#.

פונקציה ISNA מקבלת פרמטר אחד בלבד ונותנת TRUE אם הפרמטר שהוזן לה הוא N/A#, אחרת – FALSE.

צירוף של שתי הפונצקיות נותן את התוצאה הבאה: אם הערך בתא A2 הוא אחד מבין הערכים הנדרשים לי הנמצאים בטווח D1:D4, הפונקציה תחזיר FALSE. אם הוא לא נמצא שם – הפונקציה תחזיר TRUE. למרות שכעת הפונקציה עובדת עם הגיון הפוך, לענייננו זה לא משנה. אנו נכניס את כל זה ל- IF ונמקם נכון את התוצאה הרצויה במקרה של TRUE ו- FALSE של הפונקציה IF.

לצורך הדגמה, אני מעוניין שהערך בעמודה F יהיה 1 אם הערך בעמודה A הוא 61, 62 או 63. אחרת – 0.

ניתן לראת שכל פעם שבעמודה A יש אחד מהערכים הנמצאים בטווח D1:D4, תוצאה של הנוסחה היא 1. בשאר המקרים – 0. אם המטרה הסופית שלי לסכם את הערכים בעמודה B אשר הערך המקביל שלהם בעמודה A הוא 61, 62 או 63,  הרי שאנו נמצאים בצעד אחד מהפתרון. אפשר לעשות הכפלה של הערך בעמודה B בערך מקביל בעמודה F בעמודה G. לחילופין, ניתן לעשות זאת באמצעות פונקציה SUMPRODUCT, של הטווחים B2:B17 ו- F2:F17.

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

נניח את בסיס הנתונים הבא:

אני עמוניים לסכם את הערכים בעמוה D, כאשר בעמודה A הערך הוא 3, בעמודה B הוא 101, 102, 115 או 210 והערך בעמודה C הוא 61, 62 או 63. ללא הפתרון המוצע של MATCH, סינון כזה ידרוש את טווח הקריטריונים בגודל של 3*4 – 4 קריטריונים השייכים לעמודה B ו-3 קריטריונים בעמוה C.

לצורך ההדגמה השתמשתי בפונקציה DSUM, אשר מסכמת את הנתונים תחת כותרת "סכום ההנחה" בטווח A1:D17. הקריטריונים מופיעים בטווח A20:C32.

בדומה לדגומה הקודמת אני אבנה בעמודה H את עמודת התנאים. אני מרחיק אותם מהטווח כי בשתי העמודות הסמוכות לטווח הערכים אני אמקם את עמודות הבדיקה (עמודות E ו-F).

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

כמו בדוגמה הקודמת הנוסחה בעמודה E תהיה:

בניית DSUM המתבססת על עמודה E מפשטת את טווח הקריטריונים ל-4 שורות:

הפעם בסיס הנתונים הורחב עד לעמודה E.

אפשר לקצר את טווח הקריטריונים לשורה אחד, על ידי בניית עמודה "בדיקה משולבת" (במקום "בדיקה"). הנוסחה שלה:

הנוסחה היא שילוב פשוט של שתי פונקציות IF, אשר הראשונה מתבססת על ISNA ו- MATCH הבודקת את התקיימות התנאי בטווח H2:H4 והשניה בטווח H7:H10, באותו האופן.

בניית DSUM המתבססת על עמודה F מפשטת את טווח הקירטריונים לשורה אחת:

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

סיימנו.

תיהנו.


היי

לאקסל 2003 ו-2007 אין פונקציה מובנית לממוצע משוקלל. פיתרון פשוט הוא שילוב של פונקציות SUMPRODUCT ו- SUM.

SUMPRODUCT מסכמת מכפלות. SUM עושה סכימה של תאים.

לדוגמה:

עמודה C "מכפלה" הינה מכפלה של עמודה A בעמודה B בכל שורה. סיכום של העמודה C נמצא בתא C8.

במקום לבנות את עמודה C, ניתן היה להשתמש ב- SUMPRODUCT:

מיקמתי את הפונקציה בתא C10. רואים שהתוצאה שלה זהה לתוצאה בתא C8.

זה המונה של נוסחת הממוצע המשוקלל. המכנה של ממוצע משוקלל יהיה סיכום של המשקלות בעמודה A. במקרה זה הוא 100.

השילוב של SUMPRODUCT ו-SUM ייראה כך:

כמובן שאם משתמשים ב-SUMPRODUCT, העמודה C "מכפלה" כבר אינה דרושה.

תיהנו.