איך לבנות תרשים מפל

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

מרחוק זה באמת נראה כמו מפל של מפלים על נהר הררי או גשר תלוי - מי רואה מה 🙂

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

• אנו רואים בבירור את הערך הראשוני והסופי של הפרמטר (העמודה הראשונה והאחרונה).
• שינויים חיוביים (צמיחה) מוצגים בצבע אחד (בדרך כלל ירוק), ושליליים (ירידה) לאחרים (בדרך כלל אָדוֹם).
  
• לפעמים התרשים עשוי להכיל גם עמודות ביניים (אפורנחת על עמודי ציר ה-X).
  

בחיי היומיום, דיאגרמות כאלה משמשות בדרך כלל במקרים הבאים:

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

ישנן מספר דרכים לבנות תרשים כזה - הכל תלוי בגרסה שלך של Microsoft Excel.

שיטה 1: הקלה ביותר: סוג מובנה ב-Excel 2016 ואילך

אם יש לך Excel 2016, 2019 או מאוחר יותר (או Office 365), אז בניית תרשים כזה לא קשה - בגרסאות אלה של Excel כבר יש סוג זה מובנה כברירת מחדל. יהיה צורך רק לבחור טבלה עם נתונים ולבחור בכרטיסייה הַבלָעָה (לְהַכנִיס) פיקוד מדורג (מפל מים):

כתוצאה מכך, נקבל דיאגרמה כמעט מוכנה:

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

אם אתה צריך להוסיף עמודות עם סכומי ביניים או סכום עמודה סופי לתרשים, הכי נוח לעשות זאת באמצעות פונקציות סיכומי משנה (סיכומים) or יחידה (לְקַבֵּץ). הם יחשבו את הסכום שנצבר מתחילת הטבלה, תוך הרחקת ממנו את הסכומים הדומים הממוקמים למעלה:

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

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

העמודה שנבחרה תנחת על ציר ה-X ותשנה אוטומטית צבע לאפור.

זה, למעשה, הכל - תרשים המפל מוכן:

שיטה 2. אוניברסלי: עמודות בלתי נראים

אם יש לך Excel 2013 או גרסאות ישנות יותר (2010, 2007 וכו'), אז השיטה המתוארת לעיל לא תעבוד עבורך. תצטרך להסתובב ולגזור את תרשים המפל החסר מהיסטוגרמה רגילה מוערמת (מסכמת את הפסים זה על גבי זה).

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

כדי לבנות תרשים כזה, עלינו להוסיף עוד כמה עמודות עזר עם נוסחאות לנתוני המקור:

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

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

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

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

שיטה 3. אם ניכנס למינוס הכל קשה יותר

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

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

שיטה 4. אקזוטי: להקות למעלה למטה

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

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

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

כדי ליצור "מפל", עליך לבחור עמודה עם חודשים (עבור חתימות לאורך ציר X) ושתי עמודות נוספות תזמן 1 и תזמן 2 ולבנות גרף רגיל בתור התחלה באמצעות הוספה - גרף (הוסף - קו Сhart):

עכשיו בואו נוסיף להקות למעלה למטה למצעד שלנו:

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

לאחר מכן, התרשים ייראה בערך כך:

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

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

בגירסאות ישנות יותר של Excel, היה עליך להשתמש בפקודה Visual Basic כדי לתקן זאת:

1. הדגש את הדיאגרמה הבנויה
2. לחץ על מקש הקיצור אחר+F11כדי להיכנס לעורך Visual Basic
3. לחץ על קיצור מקשים Ctrl+Gכדי לפתוח את קלט הפקודה הישירה ולוח ניפוי הבאגים מיידי (בדרך כלל ממוקם בתחתית).

4. העתק והדבק שם את הפקודה הבאה: ActiveChart.ChartGroups(1).GapWidth = 30 ולחץ זן:

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

• כיצד לבנות תרשים תבליטים ב- Excel כדי להמחיש KPI  
• מה חדש בתרשימים ב-Excel 2013
• כיצד ליצור תרשים "חי" אינטראקטיבי באקסל