ממוצעים ופיקים

yash1983

New member
ממוצעים ופיקים

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

מיכאל אבידן

Member
מנהל
שימוש בפונקציה: AVERAGEIF וב'עיצוב מותנה'. . .


1. נוסחת חישוב הממוצע המותנה - מוצעת בתמונה שלהלן.

2. קרא אודות 'עיצוב מותנה' ב"גוגל" (Conditional Formatting).

*** צבעתי (ידנית) את רקע כל הטבלה באפור כהה ואז החלתי 'עיצוב מותנה' כדי להבליט ולהחריג את התאים השווים/גדולים מערך הפיק - בצהוב.

 

iyyi

New member
אם תרצה להגדיר את החריגה באחוזים...

תוכל להשתמש בנוסחאות בקובץ המצורף.
נוסחת הממוצע בעמודה Q היא נוסחת מערך שיש לסיים את הכנסתה בצירוף המקשים Ctrl+Shift+Enter.
הנוסחה בודקת האם מדובר במכירה שאינה חורגת מאחוז הקלט בתא C7 מעל הממוצע, אם היא לא חורגת היא נכללת בחישוב הממוצע המנוטרל.
סימון התאים החורגים נעשה ע"י החלת העיצוב המותנה על התחום C2:N2 ושימוש בנוסחה:
=C2/$P2>=(1+$C$7)
ובחירת עיצוב רצוי

מצורף קובץ הדגמה

 

yash1983

New member
סידור החודשים

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

iyyi

New member
אתה יכול לבצע מיון לפי תוכן השורה לפי צילום המסך

סמן את האזור C2:N5 ובחר בתפריט ב:
נתונים>מיון>אפשרויות>מיון מימין לשמאל>אישור
לאחר מכן בחר במיון לפי שורה 1 ואשר

 
למעלה