สูตร Excel ขั้นสูง - 10 สูตรที่คุณต้องรู้!

นักวิเคราะห์ทางการเงินทุกคน The Analyst Trifecta® Guide สุดยอดคำแนะนำในการเป็นนักวิเคราะห์การเงินระดับโลก คุณต้องการเป็นนักวิเคราะห์การเงินระดับโลกหรือไม่? คุณต้องการปฏิบัติตามแนวทางปฏิบัติที่ดีที่สุดในอุตสาหกรรมและโดดเด่นกว่าใคร? กระบวนการของเราเรียกว่า The Analyst Trifecta®ประกอบด้วยการวิเคราะห์การนำเสนอและทักษะที่อ่อนนุ่มใช้เวลาใน Excel มากกว่าที่พวกเขาจะยอมรับได้ จากประสบการณ์หลายปีเราได้รวบรวมสูตร Excel ที่สำคัญและล้ำหน้าที่สุดที่นักวิเคราะห์การเงินระดับโลกทุกคนต้องรู้จัก

1. การจับคู่ดัชนี

สูตร: = INDEX (C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))

นี่เป็นทางเลือกขั้นสูงสำหรับสูตร VLOOKUP หรือ HLOOKUP (ซึ่งมีข้อบกพร่องและข้อ จำกัด หลายประการ) INDEX MATCH Index Match Formula การรวมฟังก์ชัน INDEX และ MATCH เป็นสูตรการค้นหาที่มีประสิทธิภาพมากกว่า VLOOKUP เรียนรู้วิธีใช้ INDEX MATCH ในบทช่วยสอน Excel นี้ ดัชนีจะส่งคืนค่าของเซลล์ในตารางตามคอลัมน์และหมายเลขแถวและการจับคู่จะส่งกลับตำแหน่งของเซลล์ในแถวหรือคอลัมน์ เรียนรู้วิธีการเหล่านี้ในคู่มือนี้เป็นการผสมผสานที่มีประสิทธิภาพของสูตร Excel ที่จะยกระดับการวิเคราะห์ทางการเงินและการสร้างแบบจำลองทางการเงินของคุณไปอีกขั้น

INDEX ส่งคืนค่าของเซลล์ในตารางตามคอลัมน์และหมายเลขแถว

MATCH ส่งคืนตำแหน่งของเซลล์ในแถวหรือคอลัมน์

นี่คือตัวอย่างของสูตร INDEX และ MATCH ที่รวมเข้าด้วยกัน ในตัวอย่างนี้เราค้นหาและส่งกลับความสูงของบุคคลตามชื่อของพวกเขา เนื่องจากชื่อและความสูงเป็นตัวแปรในสูตรเราจึงเปลี่ยนทั้งคู่ได้!

สูตร Excel ขั้นสูง - การจับคู่ดัชนีIndex Match Formula การรวมฟังก์ชัน INDEX และ MATCH เป็นสูตรการค้นหาที่มีประสิทธิภาพมากกว่า VLOOKUP เรียนรู้วิธีใช้ INDEX MATCH ในบทช่วยสอน Excel นี้ ดัชนีจะส่งคืนค่าของเซลล์ในตารางตามคอลัมน์และหมายเลขแถวและการจับคู่จะส่งกลับตำแหน่งของเซลล์ในแถวหรือคอลัมน์ เรียนรู้วิธีการเหล่านี้ในคู่มือนี้

สำหรับคำอธิบายทีละขั้นตอนหรือวิธีใช้สูตรนี้โปรดดูคำแนะนำฟรีของเราเกี่ยวกับวิธีใช้ INDEX MATCH ใน Excel Index Match Formula การรวมฟังก์ชัน INDEX และ MATCH เป็นสูตรการค้นหาที่มีประสิทธิภาพมากกว่า VLOOKUP เรียนรู้วิธีใช้ INDEX MATCH ในบทช่วยสอน Excel นี้ ดัชนีจะส่งคืนค่าของเซลล์ในตารางตามคอลัมน์และหมายเลขแถวและการจับคู่จะส่งกลับตำแหน่งของเซลล์ในแถวหรือคอลัมน์ เรียนรู้วิธีการเหล่านี้ในคู่มือนี้

2. ถ้ารวมกับ AND / OR

สูตร: = IF (AND (C2> = C4, C2 <= C5), C6, C7)

ใครก็ตามที่ใช้เวลาอย่างมากในการทำแบบจำลองทางการเงินประเภทต่างๆประเภทของแบบจำลองทางการเงินแบบจำลองทางการเงินที่พบบ่อยที่สุด ได้แก่ แบบจำลองงบ 3 รูปแบบ DCF แบบจำลอง M&A แบบจำลอง LBO แบบจำลองงบประมาณ ค้นพบ 10 อันดับแรกที่รู้ว่าสูตร IF ที่ซ้อนกันอาจเป็นฝันร้าย การรวม IF เข้ากับฟังก์ชัน AND หรือ OR อาจเป็นวิธีที่ยอดเยี่ยมในการทำให้การตรวจสอบสูตรง่ายขึ้นและผู้ใช้รายอื่นเข้าใจได้ง่ายขึ้น ในตัวอย่างด้านล่างนี้คุณจะเห็นวิธีที่เราใช้แต่ละฟังก์ชันร่วมกันเพื่อสร้างสูตรขั้นสูงขึ้น

สำหรับรายละเอียดโดยละเอียดเกี่ยวกับวิธีการใช้ฟังก์ชันนี้ใน Excel โปรดดูคำแนะนำฟรีของเราเกี่ยวกับวิธีใช้ IF กับ AND / หรือคำสั่ง IF ระหว่างสองตัวเลขดาวน์โหลดเทมเพลตฟรีนี้สำหรับคำสั่ง IF ระหว่างตัวเลขสองตัวใน Excel ในบทช่วยสอนนี้เราจะแสดงวิธีคำนวณ IF ด้วยคำสั่ง AND ทีละขั้นตอน เรียนรู้วิธีการสร้างคำสั่ง IF ที่จะทดสอบว่าเซลล์มีค่าระหว่างตัวเลขสองตัวหรือไม่จากนั้นจะแสดงผลลัพธ์ที่คุณต้องการหากตรงตามเงื่อนไขนั้นสูตร Excel ขั้นสูง - IF ANDคำสั่ง IF ระหว่างตัวเลขสองตัวดาวน์โหลดเทมเพลตฟรีสำหรับคำสั่ง IF ระหว่างตัวเลขสองตัวใน Excel ในบทช่วยสอนนี้เราจะแสดงวิธีคำนวณ IF ด้วยคำสั่ง AND ทีละขั้นตอน เรียนรู้วิธีการสร้างคำสั่ง IF ที่จะทดสอบว่าเซลล์มีค่าระหว่างตัวเลขสองตัวหรือไม่จากนั้นจะแสดงผลลัพธ์ที่คุณต้องการหากตรงตามเงื่อนไขนั้น

3. OFFSET รวมกับ 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 ซึ่งเป็นสิ่งที่สูตรออฟเซ็ตและผลรวมให้เรา

เรียนรู้วิธีสร้างสูตรนี้ทีละขั้นตอนในหลักสูตร Excel ขั้นสูงของเรา

4. เลือก

สูตร: = CHOOSE (ตัวเลือก option1 ตัวเลือก 2 ตัวเลือก 3)

ฟังก์ชัน CHOOSE ฟังก์ชัน CHOOSE ฟังก์ชัน CHOOSE ถูกจัดหมวดหมู่ภายใต้ฟังก์ชันการค้นหาและการอ้างอิงของ Excel มันจะส่งคืนค่าจากอาร์เรย์ที่ตรงกับหมายเลขดัชนีที่ให้ไว้ ฟังก์ชันจะส่งคืนรายการที่ n ในรายการที่กำหนด ในฐานะนักวิเคราะห์ทางการเงินฟังก์ชัน CHOOSE มีประโยชน์ในการเลือกจากชุดข้อมูลที่กำหนด ตัวอย่างเช่นเราเหมาะอย่างยิ่งสำหรับการวิเคราะห์สถานการณ์ในการสร้างแบบจำลองทางการเงิน ช่วยให้คุณสามารถเลือกระหว่างตัวเลือกจำนวนหนึ่งและส่งคืน "ตัวเลือก" ที่คุณเลือก ตัวอย่างเช่นสมมติว่าคุณมีสมมติฐานที่แตกต่างกันสามข้อสำหรับการเติบโตของรายได้ในปีหน้า: 5%, 12% และ 18% การใช้สูตร CHOOSE คุณสามารถคืนค่า 12% หากคุณบอก Excel ว่าคุณต้องการทางเลือก # 2

อ่านเพิ่มเติมเกี่ยวกับการวิเคราะห์สถานการณ์จำลองใน Excel Scenario Analysis การวิเคราะห์สถานการณ์เป็นเทคนิคที่ใช้ในการวิเคราะห์การตัดสินใจโดยการคาดเดาผลลัพธ์ที่เป็นไปได้ต่างๆในการลงทุนทางการเงิน ในการสร้างแบบจำลองทางการเงินนี้

ฟังก์ชัน CHOOSE

หากต้องการดูวิดีโอสาธิตโปรดดูหลักสูตร Advanced Excel Formulas ของเรา

5. XNPV และ XIRR

สูตร: = XNPV (อัตราคิดลดกระแสเงินสดวันที่)

หากคุณเป็นนักวิเคราะห์ที่ทำงานในวาณิชธนกิจวาณิชธนกิจเส้นทางอาชีพวาณิชธนกิจ - วางแผนเส้นทางอาชีพ IB ของคุณ เรียนรู้เกี่ยวกับเงินเดือนวาณิชธนกิจวิธีการได้รับการว่าจ้างและสิ่งที่ต้องทำหลังจากทำงานใน IB แผนกวาณิชธนกิจ (IBD) ช่วยให้รัฐบาล บริษัท และสถาบันต่างๆระดมทุนและดำเนินการควบรวมและซื้อกิจการ (M&A) , การวิจัยตราสารทุน, การวางแผนและการวิเคราะห์ทางการเงิน (นักวิเคราะห์ FP&A FP&A มาเป็นนักวิเคราะห์ FP&A ที่ บริษัท เราสรุปเงินเดือนทักษะบุคลิกภาพและการฝึกอบรมที่คุณต้องการสำหรับงาน FP&A และอาชีพด้านการเงินที่ประสบความสำเร็จนักวิเคราะห์ผู้จัดการและกรรมการของ FP&A รับผิดชอบในการให้ผู้บริหารได้รับการวิเคราะห์และข้อมูลที่พวกเขาต้องการ) หรือด้านอื่น ๆ ของการเงินขององค์กรที่ต้องการลดกระแสเงินสดสูตรเหล่านี้ช่วยชีวิตได้!

พูดง่ายๆคือ XNPV และ XIRR ช่วยให้คุณสามารถใช้วันที่เฉพาะเจาะจงกับกระแสเงินสดแต่ละรายการที่ลดราคาได้ ปัญหาเกี่ยวกับสูตร NPV และ IRR พื้นฐานของ Excel คือถือว่าช่วงเวลาระหว่างกระแสเงินสดเท่ากัน เป็นประจำในฐานะนักวิเคราะห์คุณจะมีสถานการณ์ที่กระแสเงินสดไม่ได้ถูกกำหนดเวลาอย่างสม่ำเสมอและสูตรนี้คือวิธีที่คุณแก้ไขปัญหานั้น

สูตรการเงินขั้นสูง XNPV ใน Excel

สำหรับรายละเอียดเพิ่มเติมโปรดดูคู่มือสูตร IRR vs XIRR ฟรีของเรา XIRR vs IRR เหตุใดจึงต้องใช้ XIRR vs IRR XIRR กำหนดวันที่ที่เฉพาะเจาะจงให้กับกระแสเงินสดแต่ละรายการทำให้แม่นยำกว่า IRR เมื่อสร้างแบบจำลองทางการเงินใน Excel เช่นเดียวกับคู่มือ XNPV ของเรา

6. SUMIF และ COUNTIF

สูตร: = COUNTIF (D5: D12,”> = 21″)

สูตรขั้นสูงทั้งสองนี้เป็นการใช้ฟังก์ชันเงื่อนไขอย่างดีเยี่ยม SUMIF จะเพิ่มเซลล์ทั้งหมดที่ตรงตามเกณฑ์ที่กำหนดและ COUNTIF จะนับเซลล์ทั้งหมดที่ตรงตามเกณฑ์ที่กำหนด ตัวอย่างเช่นสมมติว่าคุณต้องการนับเซลล์ทั้งหมดที่มากกว่าหรือเท่ากับ 21 (อายุการดื่มตามกฎหมายในสหรัฐอเมริกา) เพื่อดูว่าคุณต้องการแชมเปญกี่ขวดสำหรับงานของลูกค้า คุณสามารถใช้ COUNTIF เป็นโซลูชันขั้นสูงดังที่แสดงในภาพหน้าจอด้านล่าง

สูตร COUNTIF

ในหลักสูตร Excel ขั้นสูงของเราเราจะแจกแจงสูตรเหล่านี้โดยละเอียดยิ่งขึ้น

7. PMT และ IPMT

สูตร: = PMT (อัตราดอกเบี้ย # งวดมูลค่าปัจจุบัน)

หากคุณทำงานในธุรกิจการธนาคารพาณิชย์การธนาคารพาณิชย์ประวัติอาชีพเส้นทางอาชีพนักธนาคารพาณิชย์คือการให้บริการลูกค้าด้วยผลิตภัณฑ์สินเชื่อเช่นเงินกู้ระยะยาววงเงินสินเชื่อหมุนเวียนสิ่งอำนวยความสะดวกที่รวบรวมบริการจัดการเงินสดและผลิตภัณฑ์ตราสารหนี้อื่น ๆ ในฐานะนักวิเคราะห์สินเชื่อหรือผู้จัดการบัญชีคุณให้คำแนะนำทางการเงินอสังหาริมทรัพย์ FP&A FP&A Financial Planning and Analysis (FP&A) เป็นหน้าที่ที่สำคัญของ บริษัท ผู้เชี่ยวชาญของ FP&A สนับสนุนการตัดสินใจของผู้บริหารสำหรับ CEO, CFO และคณะกรรมการด้วยการวางแผนการวิเคราะห์และการสร้างแบบจำลอง เรียนรู้ว่างานของนักวิเคราะห์ผู้จัดการหรือผู้อำนวยการของ FP&A นั้นเกี่ยวข้องกับอะไร - เงินเดือนข้อกำหนดการศึกษาทักษะหรือตำแหน่งนักวิเคราะห์การเงินใด ๆ ที่เกี่ยวข้องกับตารางหนี้คุณจะต้องเข้าใจสูตรโดยละเอียดทั้งสองนี้

สูตร PMT ให้มูลค่าของการชำระเงินที่เท่ากันตลอดอายุของเงินกู้ คุณสามารถใช้ร่วมกับ IPMT (ซึ่งจะบอกการจ่ายดอกเบี้ยสำหรับเงินกู้ประเภทเดียวกัน) จากนั้นแยกการชำระเงินต้นและดอกเบี้ย

นี่คือตัวอย่างวิธีใช้ฟังก์ชัน PMT เพื่อรับเงินค่าจำนองรายเดือนสำหรับการจำนอง 1 ล้านดอลลาร์ที่ 5% เป็นเวลา 30 ปี

8. LEN และ TRIM

สูตร: = LEN (ข้อความ) และ = TRIM (ข้อความ)

สูตรเหล่านี้เป็นสูตรที่ไม่ค่อยธรรมดา แต่มีความซับซ้อนมาก แอปพลิเคชั่นเหล่านี้เหมาะอย่างยิ่งสำหรับนักวิเคราะห์ทางการเงิน The Analyst Trifecta® Guide สุดยอดคำแนะนำในการเป็นนักวิเคราะห์การเงินระดับโลก คุณต้องการเป็นนักวิเคราะห์การเงินระดับโลกหรือไม่? คุณต้องการปฏิบัติตามแนวทางปฏิบัติที่ดีที่สุดในอุตสาหกรรมและโดดเด่นกว่าใคร? กระบวนการของเราเรียกว่า The Analyst Trifecta®ประกอบด้วยการวิเคราะห์การนำเสนอและทักษะที่อ่อนนุ่มซึ่งต้องจัดระเบียบและจัดการข้อมูลจำนวนมาก น่าเสียดายที่ข้อมูลที่เราได้รับมักไม่ได้รับการจัดระเบียบอย่างสมบูรณ์แบบและบางครั้งอาจมีปัญหาเช่นช่องว่างเพิ่มเติมที่จุดเริ่มต้นหรือจุดสิ้นสุดของเซลล์

ในตัวอย่างด้านล่างคุณสามารถดูว่าสูตร TRIM ล้างข้อมูล Excel ได้อย่างไร

Excel ขั้นสูง - TRIM

9. เข้มข้น

สูตร: = A1 & "ข้อความเพิ่มเติม"

Concatenate ไม่ได้มีหน้าที่ในตัวมันเอง แต่เป็นเพียงวิธีใหม่ในการรวมข้อมูลจากเซลล์ต่างๆและทำให้แผ่นงานมีไดนามิกมากขึ้น นี่เป็นเครื่องมือที่มีประสิทธิภาพมากสำหรับนักวิเคราะห์ทางการเงินที่ดำเนินการสร้างแบบจำลองทางการเงิน (ดูคู่มือการสร้างแบบจำลองทางการเงินฟรีของเราคู่มือการสร้างแบบจำลองทางการเงินฟรีคู่มือการสร้างแบบจำลองทางการเงินนี้ครอบคลุมเคล็ดลับของ Excel และแนวทางปฏิบัติที่ดีที่สุดเกี่ยวกับสมมติฐานไดรเวอร์การคาดการณ์การเชื่อมโยงงบทั้งสามการวิเคราะห์ DCF และข้อมูลเพิ่มเติม เรียนรู้เพิ่มเติม).

ในตัวอย่างด้านล่างคุณจะเห็นว่าข้อความ“ New York” บวก“,“ เข้ากับ“ NY” เพื่อสร้าง“ New York, NY” ได้อย่างไร สิ่งนี้ช่วยให้คุณสร้างส่วนหัวและป้ายกำกับแบบไดนามิกในแผ่นงาน ตอนนี้แทนที่จะอัปเดตเซลล์ B8 โดยตรงคุณสามารถอัปเดตเซลล์ B2 และ D2 ได้อย่างอิสระ ด้วยชุดข้อมูลขนาดใหญ่นี่เป็นทักษะที่มีค่าที่คุณควรมี

เชื่อมต่อสูตร Excel ตัวอย่าง

10. ฟังก์ชัน CELL, LEFT, MID และ RIGHT

ฟังก์ชัน Excel ขั้นสูงเหล่านี้สามารถรวมกันเพื่อสร้างสูตรขั้นสูงและซับซ้อนที่จะใช้ ฟังก์ชัน CELL สามารถส่งคืนข้อมูลที่หลากหลายเกี่ยวกับเนื้อหาของเซลล์ (เช่นชื่อตำแหน่งแถวคอลัมน์และอื่น ๆ ) ฟังก์ชัน LEFT สามารถส่งคืนข้อความจากจุดเริ่มต้นของเซลล์ (จากซ้ายไปขวา) MID จะส่งคืนข้อความจากจุดเริ่มต้นของเซลล์ (จากซ้ายไปขวา) และ RIGHT จะส่งคืนข้อความจากท้ายเซลล์ (ขวาไปซ้าย)

ด้านล่างนี้เป็นภาพประกอบของสูตรทั้งสามนี้ที่ใช้งานจริง

หากต้องการดูว่าสิ่งเหล่านี้สามารถรวมเข้าด้วยกันอย่างมีประสิทธิภาพด้วยฟังก์ชัน CELL ได้อย่างไรเราจะแยกย่อยให้คุณทีละขั้นตอนในคลาสสูตร Excel ขั้นสูงของเรา

สูตร CELL, LEFT, MID และ RIGHT

การฝึกอบรมสูตร Excel เพิ่มเติม

เราหวังว่าสูตร Excel ขั้นสูง 10 อันดับแรกเหล่านี้จะเป็นประโยชน์สำหรับคุณ พวกเขาควรจะไปได้ไกลในการปรับปรุงการวิเคราะห์ทางการเงินและทักษะการสร้างแบบจำลองทางการเงินทักษะการสร้างแบบจำลองทางการเงินเรียนรู้ทักษะการสร้างแบบจำลองทางการเงินที่สำคัญที่สุด 10 ประการและสิ่งที่จำเป็นสำหรับการสร้างแบบจำลองทางการเงินใน Excel ทักษะที่สำคัญที่สุด: การบัญชี

ด้านล่างนี้เป็นแหล่งข้อมูลทางการเงินเพิ่มเติมเพื่อช่วยให้คุณเป็นผู้ใช้ระดับสูงของ Excel:

  • แผ่นโกงสูตร Excel สูตร Excel แผ่นโกงสูตรการเงินของ Excel จะให้สูตรที่สำคัญที่สุดทั้งหมดในการวิเคราะห์ทางการเงินและการสร้างแบบจำลองในสเปรดชีต Excel หากคุณต้องการเป็นผู้เชี่ยวชาญด้านการวิเคราะห์ทางการเงินของ Excel และผู้เชี่ยวชาญในการสร้างแบบจำลองทางการเงินคุณมาถูกที่แล้ว
  • แป้นพิมพ์ลัด Excel ทางลัด Excel ทางลัด PC Mac Excel ทางลัด - รายการทางลัด MS Excel ที่สำคัญที่สุดและทั่วไปสำหรับผู้ใช้พีซีและ Mac การเงินวิชาชีพบัญชี แป้นพิมพ์ลัดเร่งทักษะการสร้างแบบจำลองของคุณและประหยัดเวลา เรียนรู้การแก้ไขการจัดรูปแบบการนำทาง Ribbon การวางแบบพิเศษการจัดการข้อมูลการแก้ไขสูตรและเซลล์และทางลัดอื่น ๆ
  • หลักสูตรความผิดพลาดของ Excel ฟรี
  • หลักสูตร Excel ขั้นสูง
  • Excel สำหรับการสร้างแบบจำลองทางการเงิน