17 สูตร Excel ขั้นสูง – มืออาชีพทุกคนต้องรู้
เผยแพร่แล้ว: 2019-07-28หากคุณพบว่าตัวเองมักงอมือและเสียเวลาหลายชั่วโมงในการทำสิ่งต่างๆ ด้วยตนเองใน Excel คุณจะพลาดว่า Excel มีประสิทธิภาพเพียงใดหากคุณรู้วิธีใช้งานอย่างถูกต้อง Microsoft Excel ขึ้นชื่อในเรื่องสูตรที่สามารถทำงานได้อย่างมีประสิทธิภาพกับข้อมูลจำนวนมากในเซลล์ โดยไม่ต้องเข้าไปยุ่งเกี่ยวกับกระบวนการด้วยตนเองมากนัก
เราได้รวบรวม 17 สูตรขั้นสูงของ excel ที่มีความสามารถที่แปลกประหลาดในการแปลงงานทางโลกที่ใช้เวลานานและเป็นงานที่ทำด้วยตนเองให้กลายเป็นงานเพียงไม่กี่วินาที นี่คือสูตรที่มืออาชีพทุกคนควรมีไว้ใช้เพียงปลายนิ้วสัมผัส เพื่อประหยัดเวลาอันมีค่าของตนเอง หรือเพื่อสร้างความประทับใจให้เจ้านายในช่วงเวลาสำคัญ
ผ่านรายการและบอกรายการโปรดของคุณ!
ผู้เรียนได้รับการขึ้นเงินเดือนโดยเฉลี่ย 58% โดยสูงสุดอยู่ที่ 400%สารบัญ
1. ดัชนี MATCH
สูตร = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))
ทางเลือกที่ยอดเยี่ยมสำหรับสูตร VLOOKUP หรือ HLOOKUP บน Excel ที่มีข้อเสียบางประการสำหรับการดำเนินการค้นหา INDEX MATCH เป็นสูตรผสมของ 2 ฟังก์ชันที่แยกจากกัน:
INDEX: สูตรนี้คืนค่าของเซลล์ในตารางโดยยึดตามคอลัมน์และหมายเลขแถว
MATCH: สูตรนี้ส่งคืนตำแหน่งของเซลล์ในแถวหรือคอลัมน์
ตัวอย่างของการผสมผสานสูตร INDEX และ MATCH คือ เมื่อคุณค้นหาและส่งคืนส่วนสูงของบุคคลตามชื่อ คุณสามารถใช้สูตรนี้เพื่อเปลี่ยนทั้งตัวแปร (ในกรณีนี้ คือ ชื่อ และส่วนสูง) โดยใช้สูตร INDEX MATCH .
แหล่งที่มา
ให้เราทำลายมันลงทีละขั้นตอน:
วิธีรวม INDEX และ MATCH
- พิมพ์ INDEX
- เลือกพื้นที่ที่คุณต้องการ INDEX
- ล็อคพื้นที่ด้วยF4
- ค้นหาแถวที่คุณต้องการค้นหาข้อมูลจาก
- พิมพ์ MATCH พร้อมชุดข้อมูลและล็อคพื้นที่ด้วย F4
- พิมพ์ 0 สำหรับการจับคู่แบบตรงทั้งหมด ปิดวงเล็บ
- กด Enter
ตอนนี้คุณมีชุดคอลัมน์และแถวในชีตที่มีไดนามิกและใช้งานได้อย่างสมบูรณ์ โดยจะทำการรวมเซลล์และแถวสำหรับข้อมูลที่ถูกต้องโดยอัตโนมัติ
2. เฉลี่ย
ในการทำสูตรค่าเฉลี่ยเพื่อหาค่าเฉลี่ยของช่วงของตัวเลขใดๆ ต่อไปนี้คือขั้นตอนที่คุณต้องปฏิบัติตาม:
ป้อนค่า เซลล์ หรือมาตราส่วนของเซลล์ที่คุณกำลังคำนวณในรูปแบบ
สูตรจะเริ่มต้นด้วย =AVERAGE(number1, number 2, etc.)
หากคุณต้องการหาค่าเฉลี่ยของช่วงของเซลล์ในชีต ให้ทำตามขั้นตอนต่อไปนี้:
- ป้อนค่า เซลล์ หรือมาตราส่วนของเซลล์ที่คุณกำลังคำนวณในรูปแบบ เช่นเดียวกับที่คุณทำด้านบน
- สูตรจะเป็นเช่น =AVERAGE(ค่าเริ่มต้น: End Value)
หากคุณสงสัยว่าจะป้อนค่าสำหรับพื้นที่ของเซลล์ได้อย่างไร คุณสามารถเลือกพื้นที่บนแผ่นงานของคุณด้วยเมาส์และล็อกไว้ด้วย F4 สิ่งนี้จะทำให้ Excel ทำงานที่เหลือให้คุณโดยไม่ต้องทำผลรวมและหารตัวเลขด้วยจำนวนรายการในกลุ่ม
3.SUMIF
สูตรนี้ใน excel จะแสดงเป็น SUMIF(ช่วง, เกณฑ์, [ช่วงผลรวม]) ซึ่งจะส่งผลให้ผลรวมของค่าภายในช่วงของเซลล์ที่ต้องการซึ่งจะเป็นไปตามข้อกำหนดที่คุณตั้งไว้ ตัวอย่างเช่น =SUMIF(C3: C12, “>70,000) จะส่งคืนผลรวมของค่าระหว่างเซลล์ของ C3 และ C12 จากเซลล์ที่มีค่ามากกว่า 70,000 เท่านั้น
ในกรณีของการคำนวณการเงิน เงินเดือน หรือแม้แต่ต้นทุน คุณต้องมีค่าของลูกค้าเป้าหมายที่เกี่ยวข้องกับพนักงานเฉพาะซึ่งกำหนดโดยเงื่อนไขที่คุณกำหนด การทำเช่นนี้ด้วยตนเองใช้เวลานานมากและทำให้สิ่งต่างๆ ช้าลง
สูตรสำหรับเงื่อนไขข้างต้นสามารถเป็น =SUMIF(range, criteria, [sum_range]) โดยที่ Range สามารถกำหนดเป็น Range ของชีตที่คุณต้องการเลือกค่าได้
[sum_range] ถูกกำหนดเป็นช่วงเพิ่มเติมหรือช่วงที่ไม่บังคับที่คุณจะเพิ่มนอกเหนือจากช่วงแรกที่ป้อนนี่คือตัวอย่าง:
แหล่งที่มา
4. ออฟเซ็ตรวมกับ SUM
เพียงอย่างเดียว ฟังก์ชัน OFFSET อาจไม่สะดวก แต่เมื่อรวมกับบริการอื่น คุณจะได้สูตรที่ซับซ้อนพร้อมผลลัพธ์ที่เร็วขึ้น หากคุณต้องการสร้างบทบาทแบบไดนามิกที่สามารถรวมจำนวนตัวแปรของเซลล์ใดก็ได้ สูตร SUM ปกติคือ สแตติกจะต้องรวมกับฟังก์ชัน OFFSET
ดังนั้น ในการหาผลรวมของค่าในเซลล์ตัวแปร สูตรควรเป็นดังนี้
=SUM(B4:OFFSET(B4,0,E2-1))
แหล่งที่มา
ที่นี่การอ้างอิงสิ้นสุดของฟังก์ชัน SUM จะถูกแทนที่ด้วยฟังก์ชัน OFFSET สูตร SUM ที่เริ่มต้นใน B4 ลงท้ายด้วยตัวแปรและเป็นสูตร OFFSET เริ่มต้นที่ B4 ดำเนินการต่อด้วยค่าใน E2 (“3”) ลบหนึ่ง วิธีนี้ช่วยให้วิธีการย้ายไปยังสองเซลล์และรวมข้อมูลเป็นเวลาสามปี ในข้อมูลอ้างอิงด้านบน คุณจะเห็นว่าเซลล์ F7 มีผลรวมของเซลล์ B4: D4 = 15
5. ถ้าและ
สูตรนี้มีประโยชน์ในสถานการณ์เมื่อคุณต้องการสร้างเงื่อนไขบางอย่างเพื่อรวมข้อมูลจำนวนมาก คำสั่ง IF ช่วยในการใช้ฟังก์ชัน excel IF ขั้นสูงสำหรับการสร้างฟิลด์ใหม่ตามเงื่อนไขเหล่านี้บนแทร็กที่มีอยู่แล้ว
ตัวอย่างเช่น หากคุณต้องการทำเครื่องหมายพนักงานที่มีเงินเดือนมากกว่า 50K และรหัสพนักงานมากกว่า 3 สูตรจะเป็นดังนี้:
IF(และ(E4>3,F4>50000)1,0)
แหล่งที่มา
เนื่องจากไม่มีกรณีจริงในข้อมูลข้างต้น สูตรจะส่งคืนค่า 0
6. เชื่อมต่อ
ถ้าคุณมีสตริงข้อความจำนวนมากในแผ่นข้อมูลของคุณ และต้องการทำให้ข้อมูลปรากฏในบรรทัดเดียว สูตรนี้เหมาะสำหรับคุณ ตัวอย่างเช่น ถ้าคุณต้องการแสดงรหัสพนักงานและชื่อพนักงานในคอลัมน์เดียว วิธีการควรเป็น:
= เชื่อมต่อ (B3, C3)
แหล่งที่มา
7. PMT
ฟังก์ชันนี้จะช่วยให้คุณทราบการชำระเงินในอนาคตสำหรับเงินกู้ โดยพิจารณาจากอัตราดอกเบี้ย จำนวนเงินต้น และระยะเวลาของเงินกู้ นี่คือสิ่งที่คุณต้องเริ่มต้น:
- ระยะเวลาเงินกู้
- เงินต้นเริ่มต้น (เงิน) ของเงินกู้
- มูลค่าในอนาคต
- ประเภทเงินกู้
ตอนนี้ ถ้าคุณต้องการหาการชำระเงินรายเดือนสำหรับเงินต้น สูตรเดียวกันจะเป็นดังนี้:
= PMT (อัตรา ต่อ PV [fv] [ประเภท])
ให้เราเข้าใจสิ่งนี้ด้วยตัวอย่าง:
แหล่งที่มา
=PMT(C2/12.B2.A2)
และส่วนที่ดีที่สุดคือ คุณสามารถลากมุมล่างขวาของเซลล์ PMT ข้ามช่องต่างๆ เพื่อคำนวณจำนวนเงินที่ชำระรายเดือนสำหรับเขตข้อมูลทั้งหมดโดยอัตโนมัติ
งาน Data Science ประเภทยอดนิยม8. ทริม
นี่เป็นหนึ่งในสูตรที่ใช้มากที่สุดใน excel ซึ่งจะล้างพื้นที่ที่ไม่ต้องการในฟิลด์ ตัวอย่างเช่น หากคุณต้องการลบช่องว่างที่จุดเริ่มต้นของชื่อ คุณสามารถทำได้โดยใช้ฟังก์ชัน TRIM:
= ทริม(C6)
แหล่งที่มา
สิ่งนี้จะคืนคุณด้วยคุณค่าของ Chandan Kale โดยไม่ต้องมีช่องว่างเพิ่มเติมที่ด้านหน้าหรือปลาย
9. เลน
นี่คือฟังก์ชันที่บอกจำนวนอักขระในสตริงข้อความ วิธีที่น่าตื่นเต้นที่สุดวิธีหนึ่งในการใช้วิธีนี้คือ ตัวอย่างเช่น หากคุณต้องการเน้นย้ำผู้บริจาคที่บริจาคมากกว่า 1,000 ดอลลาร์ด้วยการนับจำนวนหลักในคอลัมน์การบริจาค สูตรจะเป็นดังนี้:
=เลน(B2)
แหล่งที่มา
และถ้าคุณต้องการเน้นเซลล์ทั้งหมดในคอลัมน์การบริจาค คุณต้อง:
- เลือกแท็บหน้าแรกในเมนู
- คลิกที่ Conditional Formatting (ในแถบเครื่องมือ)
- เลือกใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ
แหล่งที่มา
ที่นี่ หากคุณสร้างเงื่อนไข “>3” อะไรก็ตามที่มากกว่า $1,000 จะได้รับการจัดรูปแบบเฉพาะ ซึ่งคุณสามารถเลือกได้โดยคลิกตัวเลือกรูปแบบ
10. เลือก
นี่เป็นฟังก์ชันที่ยอดเยี่ยมสำหรับการวิเคราะห์สถานการณ์จำลองในการสร้างแบบจำลองทางการเงิน ช่วยให้คุณสามารถเลือกระหว่างตัวเลือกจำนวนหนึ่งและส่งคืน "ตัวเลือก" ที่คุณเลือก ตัวอย่างเช่น หากคุณมีค่าที่แตกต่างกันสามค่าสำหรับการเติบโตของรายได้ในปีหน้าโดยอิงตามสมมติฐาน โดยใช้ฟังก์ชัน CHOOSE คุณสามารถส่งคืนจำนวนเงินได้ตามความต้องการของคุณ
สูตรคือ:
=เลือก(C7,D3,D4,D5)
แหล่งที่มา
11. เซลล์ ซ้าย กลาง และขวา
ฟังก์ชันทั้งหมดข้างต้นสามารถรวมกันได้หลายวิธีเพื่อให้ได้สูตรขั้นสูง
- ฟังก์ชัน CELL ใช้เพื่อส่งคืนข้อมูลประเภทต่างๆ เกี่ยวกับเนื้อหาของเซลล์
- บริการ LEFT ใช้เพื่อแทนที่ข้อความจากจุดเริ่มต้นของเซลล์
- ฟังก์ชัน MID สามารถส่งคืนข้อความจากจุดเริ่มต้นเซลล์ใดก็ได้
- ฟังก์ชัน RIGHT จะส่งกลับเฉพาะข้อความจากจุดสิ้นสุดของเซลล์
แหล่งที่มา
12. XNPV และ XIRR
สำหรับนักวิเคราะห์ทุกคนที่พบกับวาณิชธนกิจ การวิจัยตราสารทุน หรือด้านอื่น ๆ ของการเงินองค์กรที่ต้องการลดกระแสเงินสด สูตรเหล่านี้จะช่วยให้คุณประหยัดเวลาและบ่นได้มาก!
คำถามและคำตอบสัมภาษณ์วิทยาศาสตร์ข้อมูลตัวอย่างเช่น หากคุณต้องการคำนวณมูลค่าสุทธิปัจจุบัน (NPV) สำหรับการลงทุนโดยใช้อัตราการคิดลดที่ระบุและกระแสเงินสดที่เกิดขึ้นเป็นช่วงที่ไม่ปกติ ให้ใช้ฟังก์ชันต่อไปนี้
=XNPV(อัตราส่วนลด กระแสเงินสด วันที่)
แหล่งที่มา
ในทางกลับกัน ฟังก์ชัน XIRR จะบอกคุณถึงอัตราผลตอบแทนภายใน (โดยที่การไหลออก = การไหลเข้า) สำหรับชุดของกระแสเงินสดที่เกิดขึ้นในช่วงเวลาที่ไม่ปกติ เช่น
13. COUNTA ( )
นักวิเคราะห์มักจะมีปัญหาในการหาจำนวนเซลล์ที่มีค่า (ตัวเลข ข้อผิดพลาด ข้อความ ค่าตรรกะ) และเซลล์ที่ว่างเปล่า ฟังก์ชัน COUNTA( ) สามารถช่วยคุณค้นหาชื่อเซลล์ที่ไม่ว่างในช่วงที่เลือก ตัวอย่างเช่น สูตรสำหรับการนับค่าสำหรับแผ่นข้อมูลที่มีคอลัมน์ A1-A10 คือ:
=COUNTA(A1: A10)
แหล่งที่มา
14. FV
สูตรนี้มีประโยชน์หากคุณต้องการลงทุนในบางสิ่งบางอย่างและรู้ว่ามันคุ้มค่า ข้อกำหนดของสูตร FV คือ:
- อัตราดอกเบี้ยเงินกู้
- จำนวนการชำระเงิน
- การชำระเงินในแต่ละงวด
- ยอดเงินเริ่มต้นปัจจุบัน
- ประเภทสินเชื่อ
ตัวอย่างเช่น หากคุณต้องการเปรียบเทียบซีดีเปล่าหลายแผ่น และคุณมีมรดก $20,000 เพื่อลงทุนในซีดี อัตราดอกเบี้ยจะแสดงในรูปแบบทศนิยม การชำระเงินเป็นศูนย์ สูตรสำหรับสถานการณ์สมมติจะเป็น:
=FV(A2/12,B2,C2,D2)
แหล่งที่มา
ผลลัพธ์จะเป็น:
แหล่งที่มา
15. RANDBETWEEN
ฟังก์ชันนี้ช่วยในการเลือกตัวเลขภายในช่วงตัวเลขที่กำหนดไว้ล่วงหน้าแบบสุ่ม เมื่อคุณใส่ตัวเลขต่ำสุดและสูงสุดในสูตรแล้ว Excel สามารถเลือกข้อมูลที่ถูกต้องจากฟิลด์ที่แนบชื่อในช่วงและสุ่มเลือกจากฟิลด์เหล่านี้ วิธีการสำหรับสถานการณ์คือ:
=RANDBETWEEN(จุดเริ่มต้น จุดสิ้นสุด)
แหล่งที่มา
16. เล็ก
ฟังก์ชัน SMALL ใน Excel จะส่งคืนค่าตัวเลขตามตำแหน่งของค่าในรายการซึ่งจัดอันดับตามความสำคัญ ฟังก์ชันนี้ช่วยดึง "ค่าที่น้อยที่สุดที่ n" จากอาร์เรย์หรือช่วงของเซลล์ เช่น ค่าที่น้อยที่สุด ค่าต่ำสุดที่ 2 ค่าต่ำสุดที่ 3 เป็นต้น
ไวยากรณ์สำหรับสูตรคือ
= เล็ก (ครั้ง, ช่วง)
ตัวอย่างเช่น,
แหล่งที่มา
เนื่องจากฟังก์ชัน SMALL เป็นฟังก์ชันอัตโนมัติ คุณต้องระบุช่วงและจำนวนเต็มสำหรับ 'nth' เพื่อระบุค่าที่จัดอันดับ ชื่อทางการของอาร์กิวเมนต์เหล่านี้คือ 'array' และ 'k'
17. QUARTILE
ฟังก์ชันนี้จะคืนค่าควอร์ไทล์ (แต่ละกลุ่มจากสี่กลุ่มที่เท่ากัน) ในชุดข้อมูลที่กำหนด และสามารถคืนค่าต่ำสุด ควอร์ไทล์แรก ค่าสูงสุดของควอร์ไทล์ที่สอง ฟังก์ชันนี้นำจำนวนควอร์ไทล์ของฟิลด์ในอาร์เรย์ ฟังก์ชันส่งกลับค่าตัวเลขตามเปอร์เซ็นไทล์ที่ร้องขอ
ไวยากรณ์: =QUARTILE (อาร์เรย์, ควอร์ต)
แหล่งที่มา
บทสรุป:
Microsoft Excel ค่อนข้างหลีกเลี่ยงไม่ได้เมื่อพูดถึงสถานที่ทำงานแห่งศตวรรษที่ 21 แต่เคล็ดลับก็คือไม่ต้องยุ่งยากขนาดนั้น เป็นเพื่อนกับคุณ และดูผลงานของคุณพุ่งสูงขึ้น!