פונקציות מסד נתונים 2012-02-13T15:45:12+00:00

&nbsp

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

דוגמאות פשוטות: כמה נשים בחברה מרוויחות מעל 6,000 ₪ ? מה הממוצע ומהי סטיית התקן של משכורות הגברים בחברה? כמה עובדים בחברה הם מעל גיל 40? אילו דגימות שוהות במעבדה יותר מחודש ללא תשובה? וכדומה וכדומה…

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

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

נתחיל בפונקציה הסופרת נתונים, או בעברית יפה – מ וׁנָ ה.

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

פונקצית מסדי נתונים 1

מבנה הפונקציה הוא כזה:

=DCOUNTA( היכן הקריטריונים , איזו עמודה למנות? , היכן הטבלה )

היכן הטבלה? – טווח התאים המכילים את בסיס הנתונים, כולל שורת הכותרת.

איזו עמודה למנות?    – מיקום של כותרת העמודה שעליה אנו מעוניינים לבצע חישוב, במקרה של פונקצית DCOUNTA החישוב הוא – ספירה, מניה של נתונים.

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

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

בדוגמא הנוכחית שלנו זה נראה כך :

פונקצית מסדי נתונים2

לקחנו את טווח התאים I1:I2 והגדרנו אותו כטווח הקריטריונים. רשמנו בכותרת (כלומר בתא I1) את כותרת העמודה ובתא שמתחתיה רשמנו את הקריטריון. במקרה הזה, הקריטריון היה שהמילה "נקבה" תופיע. אקסל בדק לגבי כל שורה ושורה האם המילה "נקבה" מופיעה בעמודת המין שלה, ובמידה ומצא שכן – הכניס אותה לחישוב. וכך ניתן לראות את תוצאת הפונקציה בתחתית התמונה – ישנן 6 נשים בחברת ז'וז'ו סופט.

דוגמאות לקריטריונים וכיצד מחברים מספר קריטריונים יחד?

בחלק זה של השיעור נראה סוגים שונים של קריטריונים לוגים באקסל. משתמשי אקסל מנוסים שעבדו כבר עם פונקציות תנאי כגון IF או OR/AND יכולים לקרוא אותו ברפרוף היות וצורת כתיבת הקריטריונים היא אותה צורה.

אקסל – סימנים קבועים לקריטריונים

 פונקצית מסדי נתונים3

עתה נראה דוגמאות ממשיות:

כדי למנות את כל העובדים שמעל גיל 30, נרשום באזור הקריטריונים את הכותרת "גיל" (כמו שהיא רשומה בטבלה) ומתחתיה את הקריטריון 30<.

פונקצית מסדי נתונים4

 דוגמא נוספת, מה מספר העובדים בחברה שמשכורתם מתחת או שווה ל- 9,000 ₪ ?

במקרה זה שורת הכותרת תהיה "שכר" ובקריטריון נרשום 9000=>

כך זה נראה באקסל:

פונקצית מסדי נתונים5

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

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

משתמשי/ות אקסל המכירים כבר את AND ו-OR יכולים לדלג על הקטע הבא.

מה פירוש יחס בין קריטריונים ? נסביר זאת באמצעות דוגמא – אם נרצה שאקסל ימנה עבורנו את כל העובדים/ות בחברה שגילם מתחת ל-30 וגם משכורתם מעל 9000 ₪ . במקרה כזה, ישנם בשאלה שלנו 2 קריטריונים, כאשר היחס בינהם הוא גם. כלומר אנחנו מבקשים מאקסל שימנה רק שורות שמקיימות את הקריטריון הראשון וגם את הקריטריון השני.
לעומת זאת , אם נרצה שאקסל ימנה עבורנו את כל העובדים/ות שגילם מעל 40 או משכורתם מעל 10,000 ₪ , במקרה זה ישנם 2 קריטריונים אך היחס בינהם נקרא או, כיון שאנו מבקשים שאקסל ימנה כל עובד/ת שמקיים את התנאי הראשון או  את התנאי השני.

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

בדוגמא הבאה ביקשנו מתוכנת אקסל למנות עבורנו כל עובד/ת בחברה שגילם מתחת ל-30 וגם משכורתם מעל 9000 ₪

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

פונקצית מסדי נתונים6

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

עתה נדגים יחס של או בין שני קריטריונים. אנו מבקשים מאקסל למנות את כל העובדים/ות שגילם מעל 20 או משכורתם מעל 10,000 ₪. לשם כך נרשום את הקריטריונים בשורות נפרדות, וכך זה יראה בגיליון אקסל:

פונקצית מסדי נתונים7

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

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

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

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

פונקצית מסדי נתונים טבלה

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

[do_widget_area widget_area_class=none]

page