כיצד לחשב אחוז שינוי באקסל [נוסחה]

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

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

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

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

חישוב אחוז שינוי באקסל

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

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

=(new_value – original_value) / original_value

אוֹ

=(ערך חדש / ערך מקורי) – 1

• ערך חדש - הוא הערך הנוכחי/סופי של שני המספרים שאתה משווה.
• ערך_קודם - הוא הערך ההתחלתי של שני המספרים שאתה משווה לחישוב אחוז השינוי.

דוגמא:

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

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

=(C2-B2)/B2

הנוסחה שלעיל תוזן בתא D2 כדי למצוא את אחוז השינוי בין C2 (new_value) ל-B2 (original_value). לאחר הקלדת הנוסחה בתא, הקש Enter כדי לבצע את הנוסחה. אתה מקבל את אחוז השינוי בערכים עשרוניים כפי שמוצג להלן.

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

הערך העשרוני יומר לאחוז.

חשב אחוז שינוי בין שתי עמודות של מספרים

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

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

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

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

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

כפי שאתה יכול לראות, אחוז השינוי עבור 'תפוחים' גדל ב-50%, כך שהערך חיובי. האחוז של 'אבוקדו' ירד ב-14%, ומכאן שהתוצאה שלילית (-14%).

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

כדי לעצב את התאים, בחר את התאים שברצונך לעצב, לחץ לחיצה ימנית ובחר באפשרות 'עיצוב תאים'.

בחלון עיצוב תאים שמופיע, לחץ על 'מותאם אישית' בתחתית התפריט השמאלי והקלד את הקוד שלהלן בתיבת הטקסט 'סוג:':

0.00%;[אדום]-0.00%

לאחר מכן, לחץ על כפתור 'אישור' כדי להחיל את העיצוב.

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

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

=(C2/B2)-1

נוסחה זו מחלקת את new_value (C2) בערך המקורי (B2) ומפחיתה '1' מהתוצאה כדי למצוא את אחוז השינוי.

חישוב אחוז שינוי לאורך זמן

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

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

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

=((Current_value/Original_value)/Original_value)*N

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

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

=((C2-B2)/B2)/5

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

חישוב אחוז הצמיחה/שינוי בין שורות

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

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

=(B3-B2)/B2

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

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

ניתן גם לחשב את אחוז השינוי עבור כל חודש בהשוואה לינואר (B2). כדי לעשות זאת, עליך להפוך את התא הזה להפניה מוחלטת על ידי הוספת הסימן $ להפניה לתא, למשל. $C$2. אז הנוסחה תיראה כך:

=(B3-$B$2)/$B$2

ממש כמו קודם, אנחנו מדלגים על התא הראשון ומכניסים את הנוסחה בתא C3. כאשר אתה מעתיק את הנוסחה לשאר התאים, ההפניה המוחלטת ($B$2) לא משתנה, בעוד שההפניה היחסית (B3) תשתנה ל-B4, B5 וכן הלאה.

תקבל שיעור אינפלציה או דפלציה באחוזים עבור כל חודש בהשוואה לינואר.

חישוב עלייה באחוזים באקסל

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

תחביר:

עלייה באחוזים = (מספר_חדש - מספר_מקורי) / מספר_מקורי

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

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

=(C2-B2)/B2

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

אם אתה מקבל תוצאה חיובית ($24.00%), האחוז גדל בין החודשיים, ואם אתה מקבל תוצאה שלילית (למשל -$13.33%), אז האחוז למעשה ירד ולא גדל.

חישוב ירידה באחוזים באקסל

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

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

תחביר:

ירידה באחוזים = (מספר_מקורי - מספר_חדש) / מספר_מקורי

נניח שיש לך רשימה זו של התקני אחסון ומחיריהם למשך שנתיים (2018 ו-2020).

כך למשל, מחירי מכשירי האחסון היו גבוהים יותר בשנת 2018, והמחירים ירדו בשנת 2020. מה אחוז הירידה במחירים לשנת 2020 לעומת 2018? אתה יכול להשתמש בנוסחה זו כדי לגלות:

=(B2-C2)/B2

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

אם אתה מקבל תוצאה חיובית ($20.00%), האחוז ירד בין השנתיים, ואם אתה מקבל תוצאה שלילית (למשל -$13.33%), אז האחוז למעשה גדל ולא יורד.

שגיאות נפוצות שאתה מקבל בעת שימוש בנוסחאות לעיל

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

• #DIV/0!: שגיאה זו מתרחשת כאשר אתה מנסה לחלק מספר באפס (0) או בתא ריק. לְמָשָׁל. הערך B6 בנוסחה '=(B6-C6)/B6' שווה לאפס, ומכאן ה-#DIV/0! שְׁגִיאָה.

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

• #ערך: Excel זורק שגיאה זו כאשר הזנת ערך שאינו מסוג נתמך או כאשר תאים נותרים ריקים. לעתים קרובות זה יכול להתרחש כאשר ערך קלט מכיל רווחים, תווים או טקסט במקום מספרים.

• NUM!: שגיאה זו מתרחשת כאשר נוסחה מכילה מספר לא חוקי שגורם למספר גדול מדי או קטן מדי להצגה ב-Excel.

המר שגיאה לאפס

אבל יש דרך שבה נוכל להיפטר מהשגיאות הללו, ולהציג '0%' במקומו כאשר השגיאה מתרחשת. כדי לעשות זאת, עליך להשתמש בפונקציה 'IFERROR', אשר מחזירה תוצאה מותאמת אישית כאשר נוסחה מייצרת שגיאה.

תחביר של פונקציית IFERROR:

=IFERROR(value, value_if_error)

איפה,

• ערך הוא הערך, ההפניה או הנוסחה שיש לבדוק.
• value_if_error הוא הערך שאנו רוצים להציג אם הנוסחה מחזירה ערך שגיאה.

בואו ליישם את הנוסחה הזו באחת מדוגמאות השגיאה (#DIV/0! error):

=IFERROR((B6-C6)/B6,0%)

בנוסחה זו, הארגומנט 'ערך' הוא נוסחת אחוז השינוי והארגומנט 'ערך_אם_שגיאה' הוא '0%'. כאשר נוסחת השינוי באחוזים נתקלת בשגיאה (#DIV/0! error), הפונקציה IFERROR תציג '0%' כפי שמוצג להלן.

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