פונקציית XNPV באקסל

[מטאור2001]

פונקציית XNPV באקסל

שלום רב,

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

לצורך העניין, אם הפונקציה היא:
=XNPV(E6,$A$6:$A$26,$B$6:$B$26)

יש אפשרות לבצע הפנייה כלשהיא כך שבמקום A26 בחלק השני וB26 בחלק השלישי, הערך יוכל להשתנות, נניח פעם אחת הוא יהיה A26/B26, פעם שניה הוא יהיה A29/B29, כתלות בפרמטר אחר?

תודה!

נתנאל

 

[מטאור2001]

הכוונה היא

להעתיק את הנוסחא לגליונות הנוספים בלי לשנות ידנית את הטווח בכל גיליון

 

[iyyi]

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

 

[מטאור2001]

הסבר

תודה על התגובה, מצ"ב.
יש לי רשימת חוזים שמתעדכנת מדי חודש.
יש תזרים מזומנים (טור A וB), ומנגד התחייבות ונכס (חכירה מימונית למי שמכיר).

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

השאלה אם יש דרך (חוץ מלעבור גיליון גיליון ולשנות כל חודש) לעשות נוסחא קבועה (בתא D3) שתתעדכן לפי משך התזרים הצפוי.
כלומר שאם יש תזרים של 18 תקופות / חודשים (אפשר לראות את כמות התקופות ששאני צריך בעזרת COUNTIF בתא D9, או אפילו איך ליצור את תא היעד האחרון (פונקציית ADDRESS) בתא D10), שהXNPV ייקח טווח של 18 שורות.
(אופיס 2016 בעברית)

תודה ענקית מראש!

נתנאל

 

[iyyi]

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

אתה יכול לעשות זאת בעזרת פונקציית OFFSET כאשר את מספר השורות אתה מגדיר בעזרת COUNTIF
הנוסחה הכוללת את הטווחים הדינמיים היא:
=XNPV(E6,OFFSET(A6,,,COUNTIF(A6:A70,">0")),OFFSET(B6,,,COUNTIF(A6:A70,">0")))

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

 

[מטאור2001]

אלוף!!! תודה!!!!

 

[מטאור2001]

ואם הטווח צריך להתחיל מתא משתנה (גדול מ-0)?

לצורך העניין, פה החישוב של התזרים צריך להתחיל משורה A10 (התשלום הראשון) ולא מתא A6 (וזה משתנה מגיליון לגיליון).

 

[iyyi]

טווח דינמי אפשר להגדיר גם בעזרת INDIRECT

בקובץ המצורף נוסחת המערך בתא D9 מגדירה את התחום של הסכומים.
="A"&MATCH(TRUE,(A6:A70>0),)+5&":A"&COUNTIF(A6:A70,">0")+MATCH(TRUE,(A6:A70>0),)+4

יש לסיים את הכנסת הנוסחה בצירוף המקשיםCtrl+Shift +Enter

בתא D10 יש את הנוסחה לתחום התאריכים. להלן הנוסחה:
="B"&MATCH(TRUE,(A6:A70>0),)+5&":B"&COUNTIF(A6:A70,">0")+MATCH(TRUE,(A6:A70>0),)+4

ש לסיים את הכנסת הנוסחה בצירוף המקשיםCtrl+Shift +Enter

נוסחת הXNPV בתא D6 משתמשת ב-INDIRECT ונהיית יותר פשוטה (לאחר הגדרת התחומים). להלן הנוסחה (נוסחה רגילה - לא נוסחת מערך)
=XNPV(E6,INDIRECT(D9),INDIRECT(D10))

 

[מטאור2001]

תודה!!!