חיפושים, תרשימים, סטטיסטיקה, לוחות פיבוט

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

בית הספר ניווט

1. למה אתה צריך נוסחאות ותפקידים?
2. הגדרת ויצירת נוסחה
3. התייחסות ייחוס מוחלטת תא, עיצוב
4. פונקציות שימושיות אתה צריך להכיר
5. חיפושים, תרשימים, סטטיסטיקה, לוחות פיבוט

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

VLOOKUP ו HLOOKUP

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

כדי לחקור זאת, הבה נחזור לגיליון האלקטרוני "Maker Decision" שלנו בפרק 4, שבו ג 'יין מנסה להחליט מה ללבוש לבית הספר. היא כבר לא מתעניין במה שהיא לובשת, מאז היא נחת החבר החדש, אז היא עכשיו ללבוש בגדים אקראיים ונעליים.

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

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

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

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

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

הגיליון האלקטרוני עובד כך לתלבושות:

Excel בוחרת מספר אקראי מאחד לשלוש, שכן יש לה שלוש אפשרויות לתלבושת.

הבא הנוסחה מתרגמת את המספר לטקסט באמצעות VLOOKUP = (B11, A2: B4,2), אשר משתמש במספר אקראי הערך מ B11 להסתכל בטווח A2: B4. לאחר מכן הוא נותן את התוצאה (C11) מן הנתונים המפורטים בעמודה השנייה.

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

דוגמה: סטטיסטיקה בסיסית

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

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

אז איך היינו, או אוניברסיטה, למדוד ולפרש ציונים SAT? להלן ציונים SAT עבור חמישה תלמידים החל 1,870 ל 2,230.

המספרים החשובים שיש להבין הם:

ממוצע - ממוצע נקרא גם "מתכוון".

סטיית תקן (STD או σ) - מספר זה מראה כיצד פיזור נרחב סדרה של מספרים. אם סטיית התקן גדולה, אז המספרים רחוקים זה מזה ואם הוא אפס, כל המספרים זהים. ניתן לומר כי סטיית התקן היא ההפרש הממוצע בין הערך הממוצע לבין הערך הנצפה, כלומר 1,998 וכל ציון SAT. שים לב, מקובל לקצר סטיית תקן באמצעות סמל סיגמא יוונית "σ."

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

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

STD	אחוז ציונים	טווח ציוני ה- SAT
1	68%	1,854-2,142
2	95%	1,711-2,285
3	99.73%	1,567-2,429
4	99.994%	1,424-2,572

כפי שניתן לראות, הסיכוי שכל ציון SAT נמצא מחוץ ל- 3 STD הוא כמעט אפסי, מכיוון ש -99.73% מהציונים נמצאים בתוך 3 STD.

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

עכשיו אנחנו מסבירים את הנוסחאות:

= ממוצע (B2: B6)

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

= STDEV.P (B2: B6)

סטיית התקן מעל טווח B2: B6. ה- "P" פירושו STDEV.P משמש על פני כל הציונים, כלומר, את כל האוכלוסייה ולא רק קבוצת משנה.

= PERCENTRANK.EXC = ($ B $ 2: $ B $ 6, B2)

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

גרף את התוצאות

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

התלמידים נמצאים על הציר האופקי וציוני ה- SAT שלהם מוצגים כגרף עמוד כחול בסולם (ציר אנכי) מ -1,600 ל -2,300.

הדירוג באחוזים הוא הציר האנכי הימני בין 0 ל -90 אחוזים, והוא מיוצג על ידי הקו האפור.

כיצד ליצור תרשים

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

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

מהתפריט "הכנס" בחר "תרשימים" - "תרשימים מומלצים":

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

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

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

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

בתיבת הדו-שיח 'בחר מקור נתונים', לחץ על 'ציון' תחת 'ערכי Legend (Series)' ולחץ על 'ערוך', ושנה אותו כדי לומר 'ציון'.

ואז לשנות את הסדרה 2 ("אחוזון") ל "אחוז".

חזור לתרשים שלך ולחץ על "כותרת כותרת" ולשנות אותו "ציונים SAT." עכשיו יש לנו תרשים מלא. יש לו שני צירים אופקיים: אחד עבור ציון SAT (כחול) ואחד עבור אחוז מצטבר (כתום).

דוגמה: בעיית התחבורה

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

לפני שנתחיל עם הדוגמה הזו עלינו להפעיל את "Excel Solver".

אפשר התוספת של Solver

בחר "קובץ" -> "אפשרויות" -> "תוספות". בתחתית אפשרויות התוספות, לחץ על הלחצן "עבור" לצד "נהל: תוספים של Excel".

בתפריט המתקבל, לחץ על תיבת הסימון כדי להפעיל את "Solver Add-in" ולחץ על "אישור".

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

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

מחיר הספינה כל iPad הוא המרחק מהמפעל למרכז ההפצה למפעל מחולק 20,000 ק"מ. לדוגמה, הוא 8,024 ק"מ משנחאי למלבורן אשר 8,024 / 20,000 או $ 0.40 לכל iPad.

השאלה היא איך אנחנו שולחים את כל אלה iPads אלה שלושה צמחים אלה ארבעה יעדים במחיר הנמוך ביותר האפשרי?

כפי שאתה יכול לדמיין, להבין שזה יכול להיות קשה מאוד ללא נוסחה כלשהי כלי. במקרה זה אנחנו צריכים לשלוח 462,000 (F12) סך iPads. למפעלים יש יכולת מוגבלת של 500,250 יחידות (G12).

בגיליון האלקטרוני, כך שתוכל לראות איך זה עובד, יש לנו הקלדת 1 לתוך תא B10 כלומר אנחנו רוצים לשלוח 1 iPad משנחאי למלבורן. מאז עלויות ההובלה לאורך מסלול זה הם $ 0.40 לכל iPad העלות הכוללת (B17) היא $ 0.40.

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

ו "shipped" הם טווח B9: E11:

SUMPRODUCT מכפיל "עלויות" פעמים טווח "shipped" (B14). זה נקרא "כפל מטריקס".

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

באמצעות Solver

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

אנחנו צריכים הספינה מה כל מרכז ההפצה דורש. שמנו את זה קבוע לתוך פותר ככה: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. משמעות הדבר היא שסכום הפריטים שנשלחו, כלומר, הסיכומים בתאים $ B $ 12: $ E $ 12, חייב להיות גדול או שווה למה שכל מרכז הפצה דורש ($ B $ 13: $ E $ 13).

אנחנו לא יכולים לשלוח יותר ממה שאנחנו מייצרים. אנו כותבים אילוצים כאלה: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

עכשיו ללכת לתפריט "נתונים" ולחץ על כפתור "Solver". אם כפתור "Solver" אינו קיים, עליך להפעיל את התוספת Solver.

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

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

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

כאן Excel אומר כי הוא מצא פתרון. לחץ על "אישור" כדי לשמור את הפתרון ולחזור לגיליון האלקטרוני.

דוגמה: ערך נוכחי נקי

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

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

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

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

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

לאחר 13 שנים, NPV הוא $ 3,945,074 דולר, כך שהפרויקט יהיה רווחי. לדברי אנליסטים פיננסיים "תקופת התשלום בחזרה" הוא 13 שנים.

יצירת טבלת ציר

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

דוגמה: דוחות מכירות

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

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

כדי ליצור דוחות אלה, אנו יוצרים טבלת ציר:

בחר "הכנס -> ציר ציר, הוא בצד שמאל של סרגל הכלים:

בחר את כל השורות והעמודות (כולל שם המוכר) כפי שמוצג להלן:

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

אם נלחץ על כל ארבעת השדות בתיבת הדו-שיח 'טבלת ציר' (רבעון, שנה, מכירות, איש מכירות) Excel מוסיף דוח לגיליון האלקטרוני שאינו הגיוני, אך מדוע?

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

הנה זה נותן לנו את הסכום של השנה 2014 + 2014 + 2014 + 2014 = 24,168, שהוא שטויות. כמו כן הוא נתן את הסכום של הרבעונים 1 + 2 + 3 + 4 = 10 * 3 = 3 0. אנחנו לא צריכים את המידע הזה, ולכן אנו מבטלים את השדות האלה כדי להסיר אותם מהציר פיבוט שלנו.

"סכום המכירות" (סה"כ מכירות) רלוונטי, עם זאת, לכן אנו לתקן את זה.

דוגמה: מכירות של איש מכירות

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

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

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

דוגמה: מכירות של סוכן מכירות ורבעון

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

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

עם אלה מיומנויות טריות בראש, עכשיו אתה יכול ליצור שולחנות ציר מן הנתונים שלך!

סיכום

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

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

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

זכור, אם אתה רוצה לחזור לקחת את הכיתה שוב, אתה יכול להתחיל טרי עם שיעור 1!