נוסחת העלייה באחוזים באקסל היא =(new-old)/old. כשהערך של השנה שעברה ב-B2 והערך של השנה ב-C2, הקלידו =(C2-B2)/B2 ועצבו את התא כאחוז. תוצאה חיובית היא עלייה, תוצאה שלילית היא ירידה.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Last year | This year | Change |
| 2 | Coffee | $12,400 | $14,880 | 20.0% |
| 3 | Tea | $8,600 | $7,740 | -10.0% |
| 4 | Juice | $5,200 | $5,460 | 5.0% |
| 5 | Water | $3,100 | $4,650 | 50.0% |
Coffee גדל ב-20.0% ו-Tea ירד ב-10.0%, ומוצג כ--10.0%. Water גדל ב-50.0%. שנו מספר בעמודה C והאחוז מתעדכן. הסוגריים חשובים: בלעדיהם, =C2-B2/B2 מחלקת קודם ומחסרת 1 מהמכירות של השנה.
שתי דרכים לכתוב את אותה נוסחה
=(C2-B2)/B2 the change divided by the old value
=C2/B2-1 the new value as a share of the old, minus 100%
שתיהן נותנות אותה תוצאה. הראשונה נקראת כמו ההגדרה, ולכן קל יותר לבדוק אותה אחר כך. בכל מקרה, הערך הישן הוא תמיד זה שמחלקים בו. חילוק בערך החדש הוא הטעות הנפוצה ביותר, והוא נותן מספר אחר: מ-80 ל-100 זה +25%, אבל 20 חלקי 100 הם 20%.
כששום מספר אינו הישן, כמו שתי חנויות שמושוות זו לצד זו, חלקו את הפער בממוצע של השניים. זה ההפרש באחוזים, ועבור 80 ו-100 הוא נותן 22.2% לא משנה מי מהם בא קודם:
=ABS(B2-C2)/AVERAGE(B2,C2)
שינוי באחוזים מחודש לחודש
ברשימה לאורך זמן, משווים כל שורה לשורה שמעליה. לחודש הראשון אין למה להשוות, ולכן הנוסחה מתחילה בשורה השנייה.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Visitors | Change |
| 2 | Jan | 4200 | |
| 3 | Feb | 4620 | 10.0% |
| 4 | Mar | 4389 | -5.0% |
| 5 | Apr | 5047 | 15.0% |
| 6 | May | 4795 | -5.0% |
| 7 | Jun | 5754 | 20.0% |
פברואר עלה ב-10.0% לעומת ינואר ומרץ ירד ב-5.0%. כדי להשוות כל חודש לינואר במקום זאת, נעלו את הבסיס בסימני דולר: =(B3-$B$2)/$B$2. הפניות מוחלטות מסבירות את ה-$.
שינוי באחוזים מאפס
כשהערך הישן הוא 0, הנוסחה מחלקת באפס ומחזירה #DIV/0!. לעלייה מכלום אין אחוז, אז החליטו מה התא צריך להציג במקום ובדקו את המקרה:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Last year | This year | Plain | Checked |
| 2 | Coffee | 12400 | 14880 | 20.0% | 20.0% |
| 3 | Cocoa | 0 | 2100 | #DIV/0! | new |
| 4 | Soda | 0 | 640 | #DIV/0! | new |
D3 ו-D4 מציגים #DIV/0!; העמודה הבדוקה מציגה "new". =IFERROR((C2-B2)/B2,"new") נותנת כאן אותה תוצאה, אבל היא מסתירה גם טעויות אמיתיות, כמו טקסט בעמודת המכירות. העמוד על שגיאת החילוק באפס מסביר את זה באופן כללי.
תרגול: כתבו את השינוי באחוזים
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Old price | New price | Change |
| 2 | Bread | $2.40 | $2.76 |
תורכם: ב-D2, חשבו את השינוי באחוזים מהמחיר הישן ב-B2 למחיר החדש ב-C2. התא כבר מעוצב כאחוז.
שינוי באחוזים מול נקודות אחוז
כשהערכים עצמם הם אחוזים, שתי תשובות שונות נקראות שתיהן "השינוי". שיעור המרה שעולה מ-4% ל-5% עלה בנקודת אחוז אחת, והוא עלה ב-25%.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Page | Before | After | Points | Percent change |
| 2 | Signup | 4.0% | 5.0% | 1.0% | 25.0% |
D2 מחסר את השיעורים ומציג 1.0%, שעליו הייתם מדווחים כ"נקודת אחוז אחת". E2 מציג 25.0%. אמרו למה אתם מתכוונים, כי "השיעור עלה ב-1%" יכול לומר כל אחד מהשניים.
ערכים ישנים שליליים
כשהערך הישן שלילי, כמו הפסד שהופך לרווח, הנוסחה הרגילה נותנת סימן שגוי: מ--200 ל-100, =(C2-B2)/B2 מחזירה -150%. חלקו במקום זאת בערך המוחלט של המספר הישן:
=(C2-B2)/ABS(B2)
מ--200 ל-100 זה מחזיר 150%, עלייה. לצמיחה לאורך כמה שנים, שיעור שנתי ממוצע שימושי יותר מאחוז גדול אחד: =(C2/B2)^(1/5)-1 הופכת שינוי של חמש שנים לשיעור צמיחה שנתי מצטבר (CAGR).
שאלות נפוצות
מה הנוסחה לעלייה באחוזים באקסל?
=(C2-B2)/B2, כש-B2 הוא הערך הישן ו-C2 החדש, והתא מעוצב כאחוז. =C2/B2-1 נותנת את אותה תוצאה. מ-80 ל-100 התוצאה היא 25%.
איך מחשבים ירידה באחוזים באקסל?
משתמשים באותה נוסחה, =(C2-B2)/B2. כשהערך החדש קטן יותר, התוצאה שלילית: מ-100 ל-80 היא -20%. באקסל אין נוסחה נפרדת לירידה.
איך מחשבים שינוי באחוזים כשהערך הישן הוא 0?
לשינוי מ-0 אין אחוז, ו-=(C2-B2)/B2 מחזירה #DIV/0!. הציגו משהו אחר במקרה הזה: =IF(B2=0,"n/a",(C2-B2)/B2).
איך מחשבים שינוי באחוזים עם מספרים שליליים?
מחלקים בערך המוחלט של המספר הישן: =(C2-B2)/ABS(B2). הפסד של -200 שהופך לרווח של 100 מוצג אז כ-+150%, עלייה, במקום ה--150% המטעה שהנוסחה הרגילה נותנת.
