שתף קטע נבחר

מדריך Excel 2007: ביצוע חישובים בנתונים

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

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

 

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

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

 

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

 

הענקת שמות לקבוצות נתונים

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

 


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

 

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

 

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

 

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

 ובקבוצה שמות מוגדרים, ללחוץ על יצירה מתוך קטע נבחר כדי להציג את תיבת הדו-שיח יצירת שמות מבחירה (Create Names from Selection). בתיבת הדו-שיח יצירת שמות מבחירה, סמנו את תיבת הסימון המייצגת את מיקום התווית ביחס לתאי הנתונים ולחצו אישור.

 

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

 

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

  


כשתלחצו על טווח בעל שם, Excel יציג את התאים שהוא מכיל בשדה מפנה אל (Refers to). לחיצה על עריכה מציגה את תיבת הדו-שיח עריכת שם, שהיא גירסה שונה של תיבת הדו-שיח שם חדש, ומאפשרת לשנות את הגדרות הטווח. כדי למחוק שם טווח, יש לסמן אותו, ללחוץ על מחק (Delete), וללחוץ על אישור בתיבת האישור שמופיעה.

 

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

 

יצירת נוסחאות לחישוב ערכים

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

 

כדי לכתוב נוסחת Excel, התחילו את תוכן התא בסימן שוויון (=); כאשר Excel קורא סימן כזה, הוא יודע שיש לחשב את הביטוי שלאחריו כחישוב כלשהו, ולא כטקסט. אחרי סימן השוויון, הקלידו את הנוסחה. לדוגמה, תוכלו למצוא את סכום המספרים שבתאים C2 ו-C3 באמצעות הנוסחה =C2+C3.

 

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

 

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

 

הקלדת הפניות ל-15 או 20 תאים בנוסחה יכולה להיות מייגעת, אבל ב-Excel קל מאוד לטפל בחישובים מסובכים. קודם עליך לוודא שיש חוברת עבודה פתוחה. כדי ליצור חישוב חדש, לחץ על הכרטיסייה נוסחאות ברצועת הכלים, ובקבוצה ספריית פונקציות (Function Library), לחץ הוספת פונקציה (Insert Function). תיבת הדו-שיח הוספת פונקציה מופיעה, ובה רשימה של פונקציות, או נוסחאות מוגדרות מראש, ממנה תוכלו לבחור.

  


הפונקציות השימושיות ביותר

  • SUM - מחשבת את סכום המספרים בתאים המוגדרים.
  • AVERAGE - מחשבת את ממוצע המספרים בתאים המוגדרים.
  • COUNT - מחשבת את מספר הערכים בתאים המוגדרים.
  • MAX - מחשבת את הערך הגבוה ביותר בתאים המוגדרים.
  • MIN - מחשבת את הערך הנמוך ביותר בתאים המוגדרים.

 

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

 

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

PMT(rate, nper, pv, fv, type).

 

סיכום הארגומנטים בפונקציה PMT().

  • rate - שיעור הריבית החודשי. אם יש לך ריבית שנתית, עליך לחלק אותה ב- 12.
  • nper - המספר הסופי של התשלומים להחזרת ההלוואה.
  • pv - סכום ההלוואה.
  • fv - הסכום שיישאר בסוף מחזור התשלומים (לרוב נשאר ריק, כלומר 0).
  • type - הספרה 0 או 1, המורה האם התשלומים יבוצעו בתחילת החודש או בסופו (ברירת המחדל היא אפס).

 

אם חברה רוצה ללוות 2,000,000 שקלים בריבית שנתית של 6% ולהחזיר את ההלוואה במשך 24 חודשים, ניתן להשתמש בפונקציה PMT() כדי לחשב את התשלום החודשי. במקרה הזה הפונקציה תיכתב באופן הבא: =PMT(6%/12, 24, 2000000), ותיחשב תשלום חודשי של 88,641.22 שקלים.

 

ניתן גם להשתמש בשמות של טווחי תאים שהגדרתם כדי לספק ערכים בנוסחה. לדוגמה, אם הטווח בעל השם ציונים_יוסי מתייחס לתאים C4:E4, ניתן לחשב את ממוצע התאים C4:E4 באמצעות הנוסחה (ציונים_יוסי=Average(. בגרסאות קודמות של Excel הייתם צריכים להקליד את שם הנוסחה בעצמכם.

 

Excel 2007 מאפשר לכם להוסיף נוסחאות, שמות טווחים והפניות לטבלאות לנוסחאות שלך בצורה יעילה יותר באמצעות היכולת השלמה אוטומטית של נוסחה (Formula AutoComplete). בדיוק באותה הדרך בה השלמה אוטומטית מציעה למלא ערך טקסט של תא כאשר Excel מזהה שהערך שאתה מקליד תואם לערך קודם, השלמה אוטומטית של נוסחה מציעה מילוי נוסחה, טווח בעל שם או הפניה לטבלה, כאשר אתה מקליד נוסחה.

 

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

 


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

 

כדי ליצור נוסחה שמוצאת סכום באמצעות שימוש בנוסחה SUM, התחל להקליד =SU. כאשר תקליד את האות S, ההשלמה האוטומטית של נוסחה תיצור רשימת נוסחאות המתחילות באות S; כשתקליד את האות U, Excel יצמצם את הרשימה לנוסחאות המתחילות באותיות SU.

 

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

 

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

 

לאחר שתיצור נוסחה, תוכל להעתיק אותה ולהדביקה לתוך תא אחר. כאשר תעשה כן, Excel מנסה לשנות את הנוסחה כך שתפעל בתאים החדשים. לדוגמה, נניח שיש לך גיליון עבודה שבו התא D8 מכיל את הנוסחה =SUM(C2:C6).

 

לחיצה על תא D8, העתקת תוכן התא והדבקת התוצאה בתא D16, תכתוב את הנוסחה =SUM(C10:C14) בתא D16. Excel פירש מחדש את הנוסחה כך שתתאים לתאים שמסביבה. Excel יודע שביכולתו לפרש מחדש את התאים ששימשו לנוסחה כיוון שהנוסחה משתמשת בהפניה יחסית, או הפניה שניתנת לשינוי אם הנוסחה מועתקת לתא אחר. הפניות יחסיות נכתבות עם המספרים של שורת התא ואותיות העמודה (לדוגמה, C14).

 

אם תרצו שהפניה תישאר קבועה למרות שתעתיק את הנוסחה לתא אחר, תוכל להשתמש בהפניה מוחלטת. כדי לכתוב הפניה מוחלטת לתא, הקלד $ לפני שם השורה והעמודה. אם תרצה שהנוסחה בתא D16 תראה את סכום הערכים בתאים C10:C14 בלי כל קשר לתא שבו נדביק את הנוסחה, תוכל להקליד את הנוסחה בדרך הבאה: =SUM($C$10:$C$14).

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

 

קטע מהספר "Excel 2007 - צעד אחר צעד" בהוצאת "הוד עמי"

 

למדריכי אופיס נוספים

 

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