תיקנון סטטיסטי באקסל

[Seven013]

תיקנון סטטיסטי באקסל

Windows 10, Exel 2016

שלום,
יצרתי קובץ אקסל ובו מס' עמודות: עיר, מדינה, 6 קטגוריות נושאיות ועמודת סה"כ (המחברת את נתוני 6 העמודות הנושאיות). כל שורה מייצגת נתונים של עיר מסויימת (יש 404 שורות). מדובר על דירוג ערים לפי קטגוריות על פי כמות איזכורים תקופה מסוימת (2008-2018).

קובץ האקסל המדובר (מצורף גם כקובץ מצורף): http://www.upfile.co.il/file/676882136.html

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

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

תודה.

 

[iyyi]

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

1. בכל תחום מבצע דירוג של מיקום הערים מהנמוך לגבוה. העיר הנמוכה ביותר מקבלת את הציון 1 והגבוהה ביותר מקבלת את 381 (מספר הערים).
2. מכיוון שאני מניח שיש חשיבות שונה לתחומים, הייתי בונה טבלה של משקלי התחומים שתסתכם ב-100. הטבלה בתאים M3:N8 היא לצורך הדגמה בלבד. ניתן לשנות כמובן לפי החלטתך.
3. את הציון בכל תחום הייתי מכפיל בשקלול שלו בטבלה
4. סיכום של הציונים המשוקללים נותן את הציון הכללי של העיר. זה מבוצע בעמודה J
5. בעמודה K יש דירוג של הציונים הכלליים הפעם מהגבוה לנמוך

להלן הנוסחה הארוכה שבתא J3
=SUM(RANK(C3,C$3:C$404,1)*VLOOKUP(C$2,M$3:N$8,2,)+RANK(D3,D$3:D$404,1)*VLOOKUP(D$2,$M$3:$N$8,2,)+RANK(E3,E$3:E$404,1)*VLOOKUP(E$2,$M$3:$N$8,2,)+RANK(F3,F$3:F$404,1)*VLOOKUP(F$2,$M$3:$N$8,2,)+RANK(G3,G$3:G$404,1)*VLOOKUP(G$2,$M$3:$N$8,2,)+RANK(H3,H$3:H$404,1)*VLOOKUP(H$2,$M$3:$N$8,2,))
מצורף קובץ הדגמה

 

[Seven013]

תודה! עזרת לי מאוד

 

[Seven013]

האם ניתן לקבל את הציון המשוקלל בתצורה של 0-100?

 

[iyyi]

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

את המדד המשוקלל בונים כך שבכל תחום משקללים את הציון מ-0 ל-100 ע"י חלוקת הציון של העיר בציון הגבוה ביותר בתחום. בצורה כזאת העיר שקבלה את הציון הגבוה ביותר תקבל 100 ועיר שלא קיבלה ציון תקבל 0.
גם כאן מכפילים את הציון מ-0 ל-100 במשקל שלו בטבלת המשקלים.
שקלול כזה יוצר באופן אוטומטי ציון משוקלל בין 0 ל-100.
נוסחת הציון המשוקלל בתא J3 היא:
=SUM(C3/MAX(C:C)*VLOOKUP(C$2,N$3:O$8,2,)+D3/MAX(D:D)*VLOOKUP(D$2,$N$3:$O$8,2,)+E3/MAX(E:E)*VLOOKUP(E$2,$N$3:$O$8,2,)+F3/MAX(F:F)*VLOOKUP(F$2,$N$3:$O$8,2,)+G3/MAX(G:G)*VLOOKUP(G$2,$N$3:$O$8,2,)+H3/MAX(H:H)*VLOOKUP(H$2,$N$3:$O$8,2,))*100

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

 

[Seven013]

תודה! שמח ששמת לב לנתון של עזה (הנתון מבוסס דרך אגב)

 

[Seven013]

למה בעצם ניו יורק קיבלה ציון 95.6 ולא 100?

 

[Seven013]

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

כלומר, במקום לדוגמה 3808 בפוליטיקה של ניו יורק- להראות את הציון של התא? תודה.

 

[iyyi]

שכפל את העמודות של הציונים וכתוב בכל עמודה את ציון 0-100

למשל עבור הפוליטיקה זה יהיה:
C3/MAX(C:C)*100

בקשר לשלאלה הקודמת. מה שדופק את הציון של ניו יורק זה הציון ב"עולם" בו היא מקבלת 56 לעומת מוסקבה שמקבלת 100 (לאחר התקנון של 0-100) במשקל של 10% זה 5.6 נקודות כאשר בכל המקצעות האחרים היא מקבלת 100. שבמשקל של 90% זה 90 נקודות. החיבור של שני המספרים נותן 95.6

 

[Seven013]

תודה. אגיד לך לאן אני חותר- דיאגרמה

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