עיגול הצגת שבר למספר שלם (100%) - אקסל 2016

[melt123]

עיגול הצגת שבר למספר שלם (100%) - אקסל 2016

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

יש לי טבלה (מסומנת באות A) שבה מחושב חלק יחסי של משימה בציון הסופי (שיעורי בית/ מבחנים).
החלק בציון באחוזים (עמודה H) הוא תחשיבי והוא מדוייק נסגר בדיוק ל- 100%.
אבל אני מעוניין שתהיה דרך הצגה לא רק באחוזים אלא עמודה נוספת בשבר (עמודה I).

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


אולם בפועל האקסל מעגל את השבר, ושבוחנים את החלק שניתן בשבר באופן ידני, מגיעים לחוסר/ עודף תלוי במספר הספרות שקבענו בשבר (ככל שהוא גדול יותר החוסר כמובן נהיה קטן יותר) - דוגמה לכך ניתן לראות בטבלה C, שסה"כ החלק בשבר הוא 100.00002% במקום 100%.

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

 

[u333]

יש כמה אפשרויות

1. לעצב את כל עמודה I כמספר עם 5 ספרות אחרי הנקודה. או כאחוזים עם 3 ספרות אחרי הנקודה.
2. להכניס לתא I24 את הנוסחה (ROUND(SUM(I20:I23),5=

במקרה הראשון זה רק שינוי עיצוב.
במקרה השני זה ממש עיגול התוצאה.

 

[melt123]

מצויין באמת שיניתי את העיצוב

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

שאלה נוספת, איך אני בודק את התוצאה של השבר לא באופן ידני?
כלומר בדוגמה שהעלתי איך אני רואה את התוצאה של השבר שמוצג עיצובית בתא I4 לדוגמה, כחלוקה אמיתית של המספר שמוצג (115/916) כמו שחישבתי בתא I20, ולא כעיגול של האקסל.

המטרה היא לסכום את השבר שיוצג אחרי השינוי העיצובי ולראות האם באמת השברים שמוצגים סה"כ שווים ל- 100%.

 

[u333]

לא כל כך הבנתי את השאלה - האם לזה התכוונת ?

 

[melt123]

שלילי, אנסה להיות יותר ברור

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

בטבלה A, מוצג בעמודה I השבר שהמיר האקסל, לדוגמה 12.55% הוא המיר ל 1/8.
אולם האקסל בפועל אינו מתייחס לשבר כערך של 1/8 אלא רק מציג אותו טקסטואלית כך.
ולכן כאשר סוכמים בטבלה B את השברים בנוסחאת SUM מקבלים תוצאה של 100%.

בפועל אם נסכום את השברים שהאקסל הציג טקסטואלית, באופן ידני, לא בצורה תחשיבית דרך SUM נגלה שהתשובה סה"כ הסכימה של השברים שהציג האקסל לשיעורי הבית לדוגמה היא בפועל 102.778% ולא 100% כמוצג בטבלה B.

מכאן, עולה השאלה כיצד אני יכול לסכום את הערך המוצג (לא המחושב) של השברים בעמודה I.
דהיינו, לחשב לגבי שיעורי הבית 1/8+1/8+1/3+4/9 ולגלות את התשובה האמיתית מבלי שאני אצטרך לפרק ידנית את השבר?

מקווה שזה יותר ברור

 

[iyyi]

הנוסחה לחישוב המנה של I4 היא...

=LEFT(TEXT(H4,"###/###"),FIND("/",TEXT(H4,"###/###"))-1)/RIGHT(TEXT(H4,"###/###"),3)

 

[melt123]

הבנת למה אני מתכוון - תודה

רק אני מניח שהתכוונת בנוסחא לתא I4 ולא H4.
בכל מקרה זה עובד באופן טוב.
הבעייה שהטבלה שפה היא טבלה פשוטה, אבל בפועל מדובר בטבלה ענקית עם מאות שברים שלא כולם הם עם 3 מספרים, יש שברים שהם 1 (1/1) ויש שברים שהם גם עם 8 מספרים בשבר.

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

 

[iyyi]

לטבלה המקורית כמו שאתה מסביר עכשיו...

אפשר להציע פתרון עם UDF (פונקציה המוגדרת ע"י המשתמש).
בקובץ המצורף הוספתי בעמודה J פונקציה בשם FractionCalc
הפונקציה לוקחת את השבר (עם כל מספר ספרות שהוא) ומחשבת את התוצאה של חלוקת המונה במכנה.
להלן קוד המאקרו (ה-UDF)

Function FractionCalc(c As Range)
n = Left(c.Text, InStr(c.Text, "/") - 1)
d = Mid(c.Text, InStr(c.Text, "/") + 1, 99)
FractionCalc = n / d
End Function

מודגם בקובץ המצורף
שים לב שהקובץ הוא בפורמט xlsm

 

[melt123]

עובד מדהים - תודה רבה!!

 

[מיכאל אבידן]

ועדיין, IYYI, תוצאת ה- UDF בעבור המבחנים . . .

(שמס' הספרות במונה/מכנה שבשברים איננו זהה/קבוע) איננה מסתכמת ל- 100% בלי שום

שארית.

והרי אמרנו שערכה של כל שארית, גם המזערית ביותר, "לא יסולא בפז..."

 

[melt123]

כן ולא

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

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

 

[מיכאל אבידן]

אשאיר לי את ה'כן' ולך את ה'לא' . . .

&nbsp
אמש התעקשת על 'פרומילים מסכנים' ("אתה יודע כמה כסף זה") וכעת עיגול ל-5 מקומות אחרי הנקודה מספק אותך.
&nbsp
בהצלחה, חבוב...
&nbsp

 

[melt123]

אני עדיין מתעקש על עיגול

התוצאה הנוכחית יוצאת לפחות במקרה שלי מדוייקת לגמרי.

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

 

[מיכאל אבידן]

וזהו בדיוק המקרה של ה"מיליונים" . . .

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

 

[melt123]

לא זה לא המקרה של המיליונים

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

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

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

 

[מיכאל אבידן]

תראה לי עוד דו"ח רשמי ורציני

אחד . . .

&nbsp
בו התעסקו ב'חלוקת מונים במכנים' של שברי פשוטים...

 

[melt123]

כל דו"ח כזה שקיים בשוק.

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

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

נ.ב אפילו במיקמורת לא מזמן השתמשו בדו"ח כזה. (הנה משימה לשבת).

 

[iyyi]

לדעתי אי אפשר - יש כאן בעיה לוגית

במספר 12.55456% שהוא בעצם 0.1255456 יש 7 ספרות אחרי הנקודה.
הצגת התוצאה כשבר שבמכנה שלו יש 3 ספרות, משנה את התוצאה למשהו קרוב אך שונה.
סיכום המנות של חלוקת המונה במכנה יהיה שונה מסכום המספרים עם 7 ספרות אחרי הנקודה.
מאחר ו-100% מתקבל רק מ-7 ספרות אחרי הנקודה, אז יש בעיה.
הפתרון לדעתי הוא לוותר על ההצגה בשבר, שעם מכנה של 3 ספרות הוא מאוד לא אינטואיטיבי ולא אומר הרבה.

 

[מיכאל אבידן]

כמה אנרגיה אתה (ואחרים) מוכנים להשקיע . . .

&nbsp
והכל בשביל 0.00028%...

 

[melt123]

אתה לא רוצה שאני אגיד לך

כמה כסף שווים ה0.00028% האלה, בטוח שגם לך זה היה משנה.

הטבלה שפה היא טבלה פשוטה כדי להמחיש את הסוגיה.