כל אנליסט פיננסי מדריך האנליטיקאים Trifecta® המדריך האולטימטיבי כיצד להיות אנליסט פיננסי ברמה עולמית. האם אתה רוצה להיות אנליסט פיננסי ברמה עולמית? האם אתה מחפש לפעול לפי שיטות עבודה מומלצות מובילות בתעשייה ולהתבלט מהקהל? התהליך שלנו, שנקרא The Analyst Trifecta®, מורכב מניתוח, מצגת וכישורים רכים מבלה יותר זמן ב- Excel ממה שהם יעניינו להודות. בהתבסס על ניסיון של שנים ושנים, ריכזנו את נוסחאות האקסל החשובות והמתקדמות ביותר שכל אנליסט פיננסי ברמה עולמית חייב להכיר.
1. התאמת אינדקס
נוסחה: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))
זוהי אלטרנטיבה מתקדמת לנוסחאות VLOOKUP או HLOOKUP (שיש להן מספר חסרונות ומגבלות). INDEX MATCH נוסחת התאמת אינדקס שילוב של פונקציות INDEX ו- MATCH היא נוסחת חיפוש חזקה יותר מ- VLOOKUP. למד כיצד להשתמש ב- INDEX MATCH במדריך זה של Excel. אינדקס מחזיר את הערך של תא בטבלה על בסיס העמודה ומספר השורה והתאמה מחזירה את המיקום של תא בשורה או בעמודה. למד כיצד אותם במדריך זה הוא שילוב רב עוצמה של נוסחאות Excel שיעבירו את הניתוח הפיננסי והמודלים הפיננסיים שלך לשלב הבא.
INDEX מחזיר את הערך של תא בטבלה בהתבסס על העמודה ומספר השורה.
MATCH מחזיר את המיקום של תא בשורה או בעמודה.
הנה דוגמה לנוסחאות INDEX ו- MATCH המשולבות יחד. בדוגמה זו, אנו מסתכלים מעלה ומחזירים את גובה האדם על פי שמו. מכיוון ששם וגובה הם שני משתנים בנוסחה, אנו יכולים לשנות את שניהם!
נוסחת התאמה לאינדקס שילוב של פונקציות INDEX ו- MATCH היא נוסחת בדיקה חזקה יותר מ- VLOOKUP. למד כיצד להשתמש ב- INDEX MATCH במדריך זה של Excel. אינדקס מחזיר את הערך של תא בטבלה על בסיס העמודה ומספר השורה והתאמה מחזירה את המיקום של תא בשורה או בעמודה. למד כיצד להם במדריך זה
לקבלת הסבר שלב אחר שלב או כיצד להשתמש בנוסחה זו, עיין במדריך החינמי שלנו כיצד להשתמש ב- INDEX MATCH ב- Excel התאמת אינדקס הנוסחה שילוב של פונקציות INDEX ו- MATCH היא נוסחת בדיקה חזקה יותר מ- VLOOKUP. למד כיצד להשתמש ב- INDEX MATCH במדריך זה של Excel. אינדקס מחזיר את הערך של תא בטבלה על בסיס העמודה ומספר השורה והתאמה מחזירה את המיקום של תא בשורה או בעמודה. למד כיצד להם במדריך זה.
2. אם בשילוב עם AND / OR
נוסחה: = IF (AND (C2> = C4, C2 <= C5), C6, C7)
כל מי שבילה זמן רב בעשיית סוגים שונים של מודלים פיננסיים סוגי מודלים פיננסיים הסוגים הנפוצים ביותר של מודלים פיננסיים כוללים: מודל 3 הצהרות, מודל DCF, מודל M&A, מודל LBO, מודל תקציב. גלה את עשרת הסוגים המובילים יודע שנוסחאות IF מקוננות יכולות להיות סיוט. שילוב IF עם פונקציית AND או OR יכול להיות דרך נהדרת לשמור על נוסחאות קלות יותר לביקורת וקל יותר להבנת משתמשים אחרים. בדוגמה שלהלן תראה כיצד השתמשנו בפונקציות הבודדות בשילוב כדי ליצור נוסחה מתקדמת יותר.
לפירוט מפורט כיצד לבצע פונקציה זו ב- Excel, עיין במדריך החינמי שלנו לשימוש ב- IF עם AND / OR IF משפט בין שני מספרים. הורד תבנית חינם זו לקבלת משפט IF בין שני מספרים ב- Excel. במדריך זה אנו מראים לך שלב אחר שלב כיצד לחשב IF עם AND משפט. למד כיצד לבנות משפט IF אשר יבדוק אם תא מכיל ערך בין שני מספרים ואז להפיק את התוצאה הרצויה אם תנאי זה מתקיים. 
הצהרת IF בין שני מספרים הורד תבנית חינמית זו להצהרת IF בין שני מספרים ב- Excel. במדריך זה אנו מראים לך שלב אחר שלב כיצד לחשב IF עם AND משפט. למד כיצד לבנות משפט IF שיבדוק אם תא מכיל ערך בין שני מספרים ואז להפיק את התוצאה הרצויה אם התנאי הזה מתקיים
3. קיזוז בשילוב SUM או AVERAGE
נוסחה: = SUM (B4: OFFSET (B4,0, E2-1))
פונקציית ה- OFFSET פונקציית OFFSET פונקציית ה- OFFSET מסווגת תחת פונקציות חיפוש והפניה של Excel. OFFSET יחזיר טווח תאים. כלומר, הוא יחזיר מספר מוגדר של שורות ועמודות מהטווח הראשוני שצוין. כשלעצמו אינו מתקדם במיוחד, אך כאשר אנו משלבים אותו עם פונקציות אחרות כמו SUM או AVERAGE אנו יכולים ליצור נוסחה די מתוחכמת. נניח שתרצה ליצור פונקציה דינמית שיכולה לסכם מספר משתנה של תאים. עם נוסחת ה- SUM הרגילה, אתה מוגבל לחישוב סטטי, אך על ידי הוספת OFFSET אתה יכול להזיז את הפניה לתא.
איך זה עובד: כדי לגרום לנוסחה זו לעבוד, אנו מחליפים את תא הייחוס הסופי של פונקציית SUM עם פונקציית OFFSET. זה הופך את הנוסחה לדינמית והתא המכונה E2 הוא המקום שבו אתה יכול להגיד ל- Excel כמה תאים רצופים אתה רוצה להוסיף. עכשיו יש לנו כמה נוסחאות מתקדמות של Excel!
להלן צילום מסך של הנוסחה המעט מתוחכמת הזו בפעולה.
כפי שאתה רואה, הנוסחה SUM מתחילה בתא B4, אך היא מסתיימת במשתנה, שהוא הנוסחה OFFSET המתחילה ב- B4 וממשיכה בערך ב- E2 ("3"), מינוס אחד. זה מעביר את סוף נוסחת הסכום על פני 2 תאים, ומסכם 3 שנות נתונים (כולל נקודת ההתחלה). כפי שאתה יכול לראות בתא F7, סכום התאים B4: D4 הוא 15, וזה מה שנוסחת הקיזוז והסכום נותנת לנו.
למד כיצד לבנות נוסחה זו שלב אחר שלב בקורס האקסל המתקדם שלנו.
4. בחר
נוסחה: = בחר (בחירה, אפשרות 1, אפשרות 2, אפשרות 3)
פונקציית הבחירה פונקציית הבחירה פונקציית הבחירה מסווגת תחת פונקציות חיפוש והפניה של Excel. הוא יחזיר ערך ממערך המתאים למספר האינדקס שסופק. הפונקציה תחזיר את הערך התשיעי ברשימה נתונה. כאנליסט פיננסי, פונקציית CHOOSE שימושית בבחירה מתוך מערכת נתונים נתונה. לדוגמא, אנו מעולים לניתוח תרחישים בדוגמנות פיננסית. זה מאפשר לך לבחור בין מספר ספציפי של אפשרויות ולהחזיר את "הבחירה" שבחרת. לדוגמה, דמיין שיש לך שלוש הנחות שונות לצמיחת הכנסות בשנה הבאה: 5%, 12% ו- 18%. באמצעות הנוסחה CHOOSE תוכל להחזיר 12% אם תגיד ל- Excel שאתה רוצה בחירה מס '2.
קרא עוד על ניתוח תרחישים בניתוח תרחישים של Excel ניתוח תרחישים הוא טכניקה המשמשת לניתוח החלטות על ידי השערת תוצאות אפשריות שונות בהשקעות פיננסיות. בדוגמנות פיננסית, זה.
לצפייה בהדגמת וידאו, עיינו בקורס הנוסחאות המתקדמות שלנו בנוסחאות Excel.
5. XNPV ו- XIRR
נוסחה: = XNPV (שיעור היוון, תזרימי מזומנים, תאריכים)
אם אתה אנליסט העובד בבנקאות להשקעות בנקאות השקעות - מסלול קריירה בנקאי השקעות - תכנן את מסלול הקריירה שלך ב- IB. למד על משכורות בנקאיות להשקעות, כיצד להתקבל לעבודה ומה לעשות לאחר קריירה ב- IB. חטיבת בנקאות ההשקעות (IBD) מסייעת לממשלות, לתאגידים ולמוסדות לגייס הון ולהשלים מיזוגים ורכישות (M&A). , מחקר הון, תכנון פיננסי וניתוח (FP&A FP&A Analyst הפוך לאנליסט FP&A בתאגיד. אנו מתארים את השכר, הכישורים, האישיות וההכשרה הדרושים למשרות FP&A ולקריירת מימון מוצלחת. אנליסטים, מנהלים ומנהלים של FP&A הם האחראי לספק למנהלים את הניתוח והמידע שהם זקוקים לו), או כל תחום אחר במימון חברות הדורש ניכיון תזרימי מזומנים,אז הנוסחאות הללו הן הצלת חיים!
במילים פשוטות, XNPV ו- XIRR מאפשרים לך להחיל תאריכים ספציפיים על כל תזרים מזומנים בודד שמנוכה. הבעיה בנוסחאות NPV ו- IRR הבסיסיות של Excel היא שהן מניחות שתקופות הזמן בין תזרים המזומנים שוות. באופן שגרתי, כאנליסט, יהיו לך מצבים שבהם תזרימי המזומנים אינם מתוזמנים באופן שווה, ונוסחה זו היא כיצד לתקן זאת.
לקבלת פירוט מפורט יותר, עיין במדריך הנוסחאות החינמי של IRR לעומת XIRR מדריך XIRR לעומת IRR מדוע להשתמש ב- XIRR לעומת IRR. XIRR מקצה תאריכים ספציפיים לכל תזרים מזומנים בודד מה שהופך אותו למדוייק יותר מ- IRR בבניית מודל פיננסי ב- Excel. כמו גם מדריך ה- XNPV שלנו.
6. SUMIF ו- COUNTIF
נוסחה: = COUNTIF (D5: D12, ”> = 21 ″)
שתי הנוסחאות המתקדמות הללו הן שימושים נהדרים של פונקציות מותנות. SUMIF מוסיף את כל התאים העומדים בקריטריונים מסוימים ו- COUNTIF סופרת את כל התאים העומדים בקריטריונים מסוימים. לדוגמא, דמיין שאתה רוצה לספור את כל התאים הגדולים או שווים ל- 21 (גיל השתייה החוקי בארה"ב) כדי לגלות כמה בקבוקי שמפניה אתה זקוק לאירוע לקוח. אתה יכול להשתמש ב- COUNTIF כפתרון מתקדם, כפי שמוצג בצילום המסך למטה.
במהלך קורס האקסל המתקדם שלנו אנו מפרטים את הנוסחאות הללו ביתר פירוט.
7. PMT ו- IPMT
נוסחה: = PMT (ריבית, מספר תקופות, ערך נוכחי)
אם אתה עובד בבנקאות מסחרית בנקאות מסחרית פרופיל קריירה מסלול בנקאי מסחרי הוא לספק ללקוחות מוצרי אשראי כגון הלוואות לתקופה, קווי אשראי מסתובבים, מתקנים מסונכרדים, שירותי ניהול מזומנים ומוצרים אחרים עם הכנסה קבועה. כמנתח אשראי או כמנהל חשבונות, אתה מעניק ייעוץ פיננסי, נדל"ן, FP&A FP&A תכנון וניתוח פיננסי (FP&A) הם תפקיד חשוב בתאגיד. אנשי FP&A תומכים בקבלת החלטות מנהלים למנכ"ל, סמנכ"ל הכספים והדירקטוריון בתכנון, ניתוח ודוגמנות. למד מה כוללת תפקידו של אנליסט, מנהל או דירקטור FP&A - משכורת, דרישות, השכלה, כישורים או כל תפקיד אנליסט פיננסי העוסק בלוחות הזמנים של חובות, תרצה להבין את שתי הנוסחאות המפורטות הללו.
נוסחת PMT נותנת לך את הערך של תשלומים שווים לאורך חיי ההלוואה. אתה יכול להשתמש בה בשילוב עם IPMT (המסביר לך את תשלומי הריבית עבור אותו סוג הלוואה) ואז להפריד תשלומי קרן וריבית.
הנה דוגמה כיצד להשתמש בפונקציית PMT כדי לקבל את תשלום המשכנתא החודשי עבור משכנתא של מיליון דולר בשיעור 5% למשך 30 שנה.
8. LEN ו- TRIM
נוסחאות: = LEN (טקסט) ו- = TRIM (טקסט)
אלה נוסחאות קצת פחות נפוצות, אך בהחלט מתוחכמות מאוד. יישומים אלה נהדרים עבור אנליסטים פיננסיים המדריך האנליסט טריפקטה® המדריך האולטימטיבי כיצד להיות אנליסט פיננסי ברמה עולמית. האם אתה רוצה להיות אנליסט פיננסי ברמה עולמית? האם אתה מחפש לפעול לפי שיטות עבודה מומלצות מובילות בתעשייה ולהתבלט מהקהל? התהליך שלנו, שנקרא The Analyst Trifecta® מורכב מניתוח, מצגת וכישורים רכים שצריכים לארגן ולבצע מניפולציות בכמויות גדולות של נתונים. למרבה הצער, הנתונים שאנו מקבלים לא תמיד מסודרים בצורה מושלמת ולעיתים יכולות להיות בעיות כמו רווחים נוספים בתחילת או בסוף התאים
בדוגמה שלהלן תוכל לראות כיצד נוסחת TRIM מנקה את נתוני Excel.
9. קונצנט
נוסחה: = A1 & "טקסט נוסף"
שרשור הוא לא באמת פונקציה בפני עצמה - זו רק דרך חדשנית להצטרף למידע מתאים שונים ולהפוך את דפי העבודה לדינמיים יותר. זהו כלי עוצמתי מאוד עבור אנליסטים פיננסיים המבצעים דוגמנות פיננסית (עיין במדריך המודלים הפיננסי שלנו בחינם. מדריך דוגמנות פיננסי חינם. מדריך פיננסי זה מכסה טיפים של Excel ושיטות עבודה מומלצות לגבי הנחות, נהגים, חיזוי, קישור שלושת הדוחות, ניתוח DCF ועוד למד עוד).
בדוגמה שלהלן, תוכל לראות כיצד הטקסט "ניו יורק" פלוס "," מצטרף עם "ניו יורק" ליצירת "ניו יורק, ניו יורק". זה מאפשר לך ליצור כותרות דינמיות ותוויות בגליונות העבודה. כעת, במקום לעדכן את תא B8 ישירות, תוכלו לעדכן את התאים B2 ו- D2 באופן עצמאי. עם מערך נתונים גדול זו מיומנות חשובה שעומדת לרשותכם.
10. פונקציות תא, שמאל, אמצע וימין
ניתן לשלב פונקציות Excel מתקדמות אלו ליצירת נוסחאות מתקדמות ומורכבות מאוד לשימוש. הפונקציה CELL יכולה להחזיר מגוון מידע על תוכן התא (כגון שמו, המיקום, השורה, העמודה ועוד). הפונקציה LEFT יכולה להחזיר טקסט מתחילתו של תא (משמאל לימין), MID מחזיר טקסט מכל נקודת התחלה של התא (משמאל לימין), ו- RIGHT מחזיר טקסט מקצה התא (מימין לשמאל).
להלן איור של שלוש הנוסחאות הללו בפעולה.
כדי לראות כיצד ניתן לשלב את אלה בצורה עוצמתית עם פונקציית CELL, אנו מפרטים אותה עבורך שלב אחר שלב בשיעור הנוסחאות המתקדם של Excel.
הכשרה נוספת בנוסחאות אקסל
אנו מקווים כי עשר הנוסחאות הראשונות המתקדמות של Excel עזרו לך. עליהם לעשות דרך ארוכה לשיפור מיומנויות הניתוח הפיננסי שלך והמודלים הפיננסיים. מיומנויות הדוגמנות הפיננסיות למדו את 10 מיומנויות הדוגמנות הפיננסיות החשובות ביותר ומה נדרש כדי להיות טובים במודל פיננסי ב- Excel. המיומנויות החשובות ביותר: הנהלת חשבונות.
להלן משאבי מימון נוספים שיעזרו לך להפוך למשתמש כוח ב- Excel:
- נוסחאות אקסל של גיליון רמאות Excel נוסחאות של גיליון צ'אט גיליון הנוסחאות של אוצר של אוצר ייתן לך את כל הנוסחאות החשובות ביותר לביצוע ניתוחים ודוגמנות פיננסיים בגליונות האלקטרוניים של Excel. אם אתה רוצה להיות מאסטר בניתוח פיננסי של אקסל ומומחה לבניית מודלים פיננסיים, הגעת למקום הנכון.
- קיצורי מקשים של Excel קיצורי דרך של Excel PC Mac קיצורי דרך של Excel - רשימת קיצורי הדרך החשובים והנפוצים ביותר של MS Excel למשתמשי PC ו- Mac, פיננסים, מקצועות חשבונאות. קיצורי מקשים מזרזים את כישורי הדוגמנות שלכם וחוסכים זמן. למדו עריכה, עיצוב, ניווט, סרטים, הדבקה מיוחדת, מניפולציה בנתונים, עריכת נוסחאות ותאים וקיצורי דרך אחרים
- קורס קריסה בחינם באקסל
- קורס אקסל מתקדם
- אקסל לדוגמנות פיננסית