Advance Excel

1. Advance Excel

2. Function IF Function IF เป็นคำสั่งในเพื่อใช้ในการสร้างเงื่อนไข เพื่อทำการตรวจสอบค่าในเซลล์ที่เราต้องการว่าเป็นจริง หรือไม่รูปแบบ logical_testหมายถึง เงื่อนไขเพื่อทำการตรวจสอบค่า value_if_trueหมายถึง ค่าที่ใช้สำหรับแสดงผล เมื่อเงื่อนไขเป็นจริง value_if_falseหมายถึง ค่าที่ใช้สำหรับแสดงผล เมื่อเงื่อนไขเป็นเท็จ

3. ตัวอย่างการใช้งาน Function if ใน Microsoft Excel 2007

4. กรณีมีหลายเงื่อนไข

5. Function Function ในการหาผลรวมของข้อมูลปกตินิยมใช้ ฟังชั่น Sum กรณีที่ต้องหาผลรวมแบบมีเงื่อนไขสามารถใช้ฟังชั่นอื่นได้เช่น SumifหรือSumproduct

6. เครื่องหมายในการเปรียบเทียบเครื่องหมายในการเปรียบเทียบ < น้อยกว่า ใช้กับ น้อยกว่า,ไม่ถึง,ต่ำกว่า,ก่อน> มากกว่า ใช้กับ มากกว่า,หลัง<= น้อยกว่าหรือเท่ากับ ใช้กับ ไม่เกิน >= มากกว่าหรือเท่ากับ ใช้กับ ตั้งแต่...ขึ้นไป= เท่ากับ ใช้กับ เท่ากัน,เป็น,คือ<> ไม่เท่ากับ ใช้กับ ไม่เท่ากัน,ไม่ใช่,ยกเว้น

7. SumIF SumIFเป็นคำสั่งในหาผลรวมในการสร้างเงื่อนไข เพื่อทำการตรวจสอบค่าในเซลล์ที่เราต้องการว่าเป็นจริง หรือไม่รูปแบบ range หมายถึง ช่วงข้อมูลที่ใช้ในการเปรียบเทียบ criteria หมายถึง เงื่อนไขที่ใช้ในการเปรียบเทียบกับ range Sum_rangeหมายถึง ช่วงของข้อมูลที่ใช้ในการรวมเมื่อเงื่อนไขเป็นจริง

8. ตัวอย่างการใช้งาน Function Sumif ใน Microsoft Excel 2007 ตัวอย่างการหาผลรวมยอดขายแต่ละประเภทสินค้า

9. Sumproduct Sumproductเป็นเป็นสูตรที่ใช้สำหรับ การหาผลคูณของคอลัมน์ตั้งแต่ 2 คอลัมน์ขึ้นไป และเอาผลที่ได้จากการคูณนั้น มารวมกัน รูปแบบ array1หมายถึง ช่วงข้อมูล1 array2 หมายถึง ช่วงข้อมูล2 array3หมายถึง ช่วงข้อมูล3

10. ตัวอย่างการใช้งาFunctionSumproductตัวอย่างการใช้งาFunctionSumproduct ใน Microsoft Excel 2007 ตัวอย่างการหาผลรวมเงินของราคาสินค้าแบบมีเงื่อนไข

11. กรณีมีหลายเงื่อนไข

12. Function Function ในการนับจำนวนเซลล์ ฟังก์ชันการนับ (Counting) ใช้ในการนับจำนวนเซลล์ภายในพื้นที่ที่กำหนดภายใต้เงื่อนไขใด ๆ ส่วนใหญ่จะใช้ร่วมกับฟังก์ชันอื่น ๆ เช่น COUNT,COUNTA,COUNTBLANK และ COUNTIF

13. CountIF CountIFเป็นคำสั่งในนับจำนวนของเซลล์ภายในช่วงที่ตรงตามเงื่อนไขที่คุณระบุ รูปแบบ range หมายถึง ช่วงข้อมูลที่ใช้ในการเปรียบเทียบ criteria หมายถึง เงื่อนไขที่ใช้ในการเปรียบเทียบกับ range เมื่อต้องการนับจำนวนเซลล์ที่ว่างหรือเซลล์ที่ไม่ว่าง ให้ใช้ฟังก์ชัน COUNTA และ COUNTBLANK

14. ตัวอย่างการใช้งาFunction Conutif ใน Microsoft Excel 2007 ตัวอย่างการนับพนักงานที่มีเงินเดือนน้อยกว่า 10000

15. กรณีมีหลายเงื่อนไข

16. Subtotal Subtotal ส่งกลับค่าผลรวมย่อยของรายการหรือฐานข้อมูล โดยทั่วไปแล้วเป็นการง่ายที่จะสร้างรายการพร้อมกับผลรวมย่อย โดยการใช้คำสั่ง ผลรวมย่อย ในกลุ่ม เค้าร่าง ในแท็บข้อมูล หลังจากสร้างรายการผลรวมย่อยแล้ว คุณสามารถปรับเปลี่ยนโดยการแก้ไขที่ฟังก์ชัน SUBTOTAL

17. รูปแบบ Function_numคือตัวเลข 1 ถึง 11 (รวมค่าที่ซ่อน) หรือ 101 ถึง 111 (ไม่นับค่าที่ซ่อน) ซึ่งระบุว่าจะใช้ฟังก์ชันใดในการคำนวณค่าผลรวมย่อยในรายการ Ref1, ref2คือช่วง 1 ถึง 254 หรือการอ้างอิงที่คุณต้องการหาผลรวมย่อย

18. ตัวอย่างการใช้งาน Subtotal ใน Microsoft Excel 2007 ตัวอย่างการหาผลรวมจำนวนเงินของประเภทสินค้า

19. กรณีใช้จากแถบ Ribbon Data ตัวอย่างการหาผลรวมจำนวนเงินของประเภทสินค้า(จัดเรียงข้อมูลที่ต้องการก่อน

20. การทำผลสรุปย่อย การเรียงลำดับของระเบียนที่ผ่านมา จะทำให้เกิดการจับกลุ่มของข้อมูลเกิดขึ้น ตัวอย่างเช่น ถ้าเรียงลำดับของระเบียนสาขา ก็จะได้กลุ่มของสาขาเกิดขึ้น 4 กลุ่ม คือ 1,2,3 และ 4 เป็นต้น และเมื่อมีการจับกลุ่มเกิดขึ้นแล้ว ก็สามารถทำผลสรุปของกลุ่มย่อยเหล่านี้ได้

21. ให้เรียงลำดับของระเบียนที่ต้องการจับกลุ่มให้เรียงลำดับของระเบียนที่ต้องการจับกลุ่ม คลิกเมาส์ช่องใดของฐานข้อมูลก็ได้ คลิกเมนู Data เลือกคำสั่งSubtotals คลิกเลือกกลุ่มที่ต้องการในช่องของ At Each Change in คลิกเลือกฟังก์ชั่นในช่องของ Use Function เช่น Sum, Average,Max, Min เป็นต้น เป็นต้นคลิกเลือกข้อมูลที่ต้องการหาตามฟังก์ชั่นในช่องของ Add Subtotals to

22. - Replace Current Subtotals : ผลสรุปใหม่จะแทนที่ของเก่าทุกครั้ง - Page Break Between Groups : มีเส้นแบ่งหน้าให้ระหว่างกลุ่มเพื่อให้มีการขึ้นหน้าใหม่เมื่อสั่งพิมพ์ลงกระดาษ - Summary Below Data : กำหนดให้ยอดสรุปสุดท้ายปรากฏที่ด้านล่างของข้อมูล - ถ้าต้องการดูเฉพาะผลสรุปของแต่ละกลุ่ม ให้กดเมาส์ที่ปุ่ม 2

23. - ถ้าต้องการดูเฉพาะผลสรุปสุดท้าย ให้กดเมาส์ที่ปุ่ม 1 - ถ้าต้องการดูรายละเอียดทั้งหมดอีกครั้ง ให้กดเมาส์ที่ปุ่ม 3 หรือปุ่ม + 7. ถ้ายกเลิกการทำผลสรุปของกลุ่มย่อยทั้งหมดให้คลิกเมนู Data เลือกคำสั่ง Subtotals และคลิกปุ่ม Remove All

24. Function Function ในการค้นหา ฟังก์ชันการค้นหา (LOOKUP) ใช้ในการค้นหาข้อมูล ซึ่งประกอบด้วย ค่าที่ต้องการค้นหา ช่วงของข้อมูลที่จะค้นหา และเงื่อนไขอื่น ๆ ขึ้นอยู่กับรูปแบบของฟังก์ชัน เช่น VLOOKUP, HLOOKUP และINDEX

25. Vlookup Vlookupใช้สำหรับหาค่าในตารางข้อมูลแนวตั้ง โดยหาค่า (ที่เหมือน หรือ ใกล้เคียง) จากคอลัมน์แรกของตาราง และคืนค่าเป็นข้อมูลที่อยู่ในแถวเดียวกัน จากคอลัมน์ที่ระบุลงไปใน Argument ของฟังก์ชั่น ฟังก์ชั่น VLOOKUP จะไปหารหัสที่ต้องการจากตาราง และก็คืนค่าของคอลัมน์ใดๆ ในตารางนั้นๆ ที่ต้องการมา V ย่อมาจาก Verticleซึ่ง VLOOKUP จะใช้กับตารางข้อมูลแนวตั้ง ซึ่งเป็นลักษณะของตารางที่ใช้กันตามปกติ  โดยคอลัมน์ที่ต้องการเอารหัสไปเปรียบเทียบต้องอยู่ด้านซ้ายสุดของตารางหรือพื้นที่ของตารางที่เลือก

26. รูปแบบ - Lookup_valueเป็นค่าที่ต้องการหาสามารถเป็นได้ทั้ง ตัวเลข หรือตัวอักษร หรือเซลล์อ้างอิง โดยตัวพิมพ์เล็กและตัวพิมพ์ใหญ่มีค่าเหมือนกัน (Non-case-sensitive)- Table_arrayเป็นตารางที่เราต้องการไปหาค่า อาจเป็นช่วงเซลล์ หรือ Range Name ก็ได้ โดยคอลัมน์แรกของตารางต้องเป็นเลขรหัสที่ต้องการให้ Lookup_valueมาเทียบค่า Table_array- Col_index_numเป็นเลขลำดับคอลัมน์ของตาราง (Table_array) ซึ่งเป็นคอลัมน์ที่ต้องการให้ดึงค่ามา- Range_lookupถ้าเป็น TRUE หรือละไว้ จะเอาค่าที่ใกล้เคียงมา ถ้าเป็น FALSE หรือ 0 จะนำค่าที่ตรงกันมา)

27. ตัวอย่างการใช้งาน Vlookup ใน Microsoft Excel 2007 ตัวอย่างการหา VLOOKUP แบบตรงตัว (Exact Match)

28. แบบค่าเป็นช่วง (Approximate Match) จะใช้หาค่าที่ตกอยู่ในช่วง เช่น การคำนวณเกรด หรือภาษี โดยดูเงินได้เทียบกับช่วงของอัตราภาษีระดับต่างๆ โดยจะละเงื่อนไขใน Range_lookupหรือจะใส่เป็น TRUE ก็ได้ - การใช้งาน VLOOKUP แบบนี้จะใช้กับหาค่าที่เป็นช่วง เช่น การตัดเกรด หรือ การคำนวณช่วงอัตราภาษี - การใช้ฟังก์ชั่น VLOOKUP แบบนี้ ค่าในคอลัมน์แรกต้องเรียงตามลำดับจากน้อยไปมาก - ฟังก์ชั่น VLOOKUP จะไปหาค่าที่เหมือนกันก่อน ถ้าไม่เจอก็จะไปหาค่าที่มากที่สุด แต่น้อยกว่าค่าที่ต้องการหา แล้วก็จะไปนำค่าของคอลัมน์ที่เราต้องการมาแสดง

29. ตัวอย่างการใช้งาน Vlookup ใน Microsoft Excel 2007 ตัวอย่างการหา VLOOKUP แบบค่าเป็นช่วง (Approximate Match)

30. Hlookup Hlookup ใช้สำหรับหาค่าในตารางข้อมูลแนวนอน โดยหาค่า (ที่เหมือน หรือ ใกล้เคียง) จากแถวบนสุดของตาราง และคืนค่าเป็นข้อมูลที่อยู่ในคอลัมน์เดียวกัน จากแถวที่ระบุลงไปใน Argument ของฟังก์ชั่น ฟังก์ชั่น HLOOKUP (H ย่อมาจาก Horizontal) มีลักษณะโครงสร้างสูตรที่เหมือนกับ VLOOKUPเพียงแต่ตารางที่ใช้อ้างอิงนั้นจะเป็นตารางแบบแนวนอน แต่ฟังก์ชั่น HLOOKUP ไม่ค่อยได้ใช้กัน เนื่องจากตารางส่วนใหญ่เป็นตารางข้อมูลในแนวตั้ง (จะใช้ VLOOKUPเป็นส่วนใหญ่)

31. รูปแบบ - Lookup_valueเป็นค่าที่ต้องการหา สามารถเป็นได้ทั้ง ตัวเลข หรือตัวอักษร หรือเซลล์อ้างอิง โดยตัวพิมพ์เล็กและตัวพิมพ์ใหญ่มีค่าเหมือนกัน (Non-case-sensitive)- Table_arrayเป็นตารางที่เราต้องการไปหาค่า อาจเป็นช่วงเซลล์ หรือ Range Name ก็ได้ โดยแถวแรกของตารางต้องเป็นเลขรหัสที่ต้องการให้ Lookup_valueมาเทียบค่า Table_array- Row_index_numเป็นเลขลำดับแถวของตาราง (Table_array) ซึ่งเป็นแถวที่ต้องการให้ดึงค่ามา- Range_lookupถ้าเป็น TRUE หรือละไว้ จะเอาค่าที่ใกล้เคียงมา ถ้าเป็น FALSE หรือ 0 จะนำค่าที่ตรงกันมา

32. ตัวอย่างการใช้งาน Hlookup ใน Microsoft Excel 2007 ตัวอย่างการหา HLOOKUP(ไม่เป็นที่นิยมใช้กันเท่าไรนัก)

33. ตัวอย่างการใช้งาน Hlookup ใน Microsoft Excel 2007 ตัวอย่างการหา HLOOKUP(ไม่เป็นที่นิยมใช้กันเท่าไรนัก)

34. Index ส่งกลับค่าหรือการอ้างอิงไปยังค่าจากภายในตารางหรือช่วง ฟังก์ชัน INDEX มีอยู่สองฟอร์มด้วยกัน ได้แก่ ฟอร์ม อาร์เรย์ (อาร์เรย์: ใช้ในการสร้างสูตรที่จะให้ผลลัพธ์หลายค่า หรือใช้กับกลุ่มของอาร์กิวเมนต์ที่จัดเรียงอยู่ในรูปแถวและคอลัมน์ ช่วงอาร์เรย์จะใช้สูตรเดียวกัน ส่วนค่าคงที่อาร์เรย์ คือ กลุ่มของค่าคงที่ที่ใช้เป็นอาร์กิวเมนต์) และฟอร์มการอ้างอิง

35. รูปแบบ • - Arrayคือช่วงของเซลล์หรือค่าคงที่อาร์เรย์ • ถ้าอาร์เรย์มีเพียงหนึ่งแถวหรือหนึ่งคอลัมน์เท่านั้น อาร์กิวเมนต์row_numหรือ column_numที่สอดคล้องจะเป็นตัวเลือก • ถ้าอาร์เรย์มีมากกว่าหนึ่งแถว และมากกว่าหนึ่งคอลัมน์ และมีการใช้เฉพาะ row_numหรือ column_numฟังก์ชัน INDEX จะส่งกลับค่าอาร์เรย์ของทั้งแถวหรือทั้งคอลัมน์ในอาร์เรย์ • - Row_numเลือกแถวในอาร์เรย์ที่จะส่งกลับค่า ถ้า row_numถูกละ จะต้องมี column_num • - Column_numเลือกคอลัมน์ในอาร์เรย์ที่จะส่งกลับค่า ถ้า column_numถูกละ จะต้องมี row_num

36. ตัวอย่างการใช้งาน Index ใน Microsoft Excel 2007 ตัวอย่างการหา Index(Array)

37. Function ฟังก์ชันที่เกี่ยวกับการเงิน ในโปรแกรม Microsoft Excel จะมี Function PMT นี้ช่วยในการคิดคำนวณเงินผ่อนชำระ เพื่อหาจำนวนเงินที่ต้องผ่อนชำระรายเดือน ซึ่งใช้งานง่ายมากๆ เพียงกรอกตัวเลขดังรายละเอียดต่อไปนี้ เท่านั้นเอง

38. PMT รูปแบบ - rateหมายถึง อัตราดอกเบี้ยต่องวด - nperหมายถึง จำนวนงวดทั้งหมด - pvหมายถึง จำนวนเงินกู้ - fvหมายถึง  มูลค่าอนาคต ถ้าไม่ใส่จะมีค่าเป็น 0 - typeหมายถึง ตัวเลข 0 (จ่ายทุกสิ้นเดือน)หรือ 1(จ่ายทุกต้นเดือน) สำหรับกำหนดวันครบกำหนดชำระเงิน หากไม่ระบุ จะมีค่าเท่ากัน 0 

39. ตัวอย่างการใช้งาน PMT ใน Microsoft Excel 2007 ตัวอย่างการคำนวณเงินกู้ เพื่อผ่อนชำระ

40. FV FV (Future value เป็นมูลค่าในอนาคต) ของการลงทุน ค่า FV นี้ถูกคำนวณโดยมีพื้นฐานอยู่บนการชำระเป็นงวด ยอดการชำระเงินที่คงที่ และอัตราดอกเบี้ยต่อคาบเวลาที่คงที่ เงินสดที่คุณจ่ายออกไป เช่น เงินฝากเพื่อการออม จะถูกแสดงด้วยตัวเลขที่เป็นจำนวนลบ ส่วนเงินสดที่คุณได้รับ เช่น เช็คเงินปันผล จะถูกแสดงด้วยจำนวนบวก

41. รูปแบบ - Rateคืออัตราดอกเบี้ยต่องวด - Nperคือจำนวนงวดทั้งหมดของการชำระเงินรายปี - Pmt คือการชำระเงินในแต่ละงวด ไม่สามารถเปลี่ยนแปลงได้ในระหว่างช่วงเวลาของเงินรายปี ตามปกติ pmt จะประกอบด้วยเงินต้นและดอกเบี้ย แต่ไม่รวมถึงค่าธรรมเนียมหรือภาษีอื่นๆ ถ้า pmt ถูกละ คุณจะต้องใส่อาร์กิวเมนต์pv - Pvคือค่าปัจจุบัน หรือมูลค่ารวมทั้งหมด ณ เวลาปัจจุบันของจำนวนเงินในทุกงวดที่จะต้องชำระในอนาคต ถ้า pvถูกละ จะถือว่าค่านี้เป็น 0 (ศูนย์) และคุณจะต้องใส่อาร์กิวเมนต์pmt - Typeคือตัวเลข 0 หรือ 1 และจะแสดงว่าการชำระเงินครบกำหนดเมื่อใด ถ้า type ถูกละ จะถือว่าค่านี้เป็น 0

42. ตัวอย่างการใช้งาน FV ใน Microsoft Excel 2007 ตัวอย่างการคำนวณยอดชำระและอัตราดอกเบี้ยคงที่

43. NPER จำนวนงวดทั้งหมดในการผ่อนชำระสำหรับการลงทุนซึ่งมียอดการชำระเงินคงที่เป็นงวดๆ และมีอัตราดอกเบี้ยต่องวดคงที่

44. รูปแบบ • - Rate คืออัตราดอกเบี้ยต่องวด • Pmt เป็นการชำระเงินในแต่ละงวด ไม่สามารถเปลี่ยนได้ตลอดระยะเวลาของเงิน • รายปี โดยทั่วไป pmt จะประกอบด้วยเงินต้นและดอกเบี้ย แต่ไม่มีค่าธรรมเนียมอื่นๆ • หรือภาษี • - Pvคือมูลค่าปัจจุบันหรือเงินก้อนที่มีค่าเท่ากับการชำระในอนาคตแต่ละงวดรวมกัน • Fv คือมูลค่าอนาคต หรือดุลเงินสดที่คุณต้องการให้เป็นหลังจากได้ชำระเงินงวด • สุดท้ายแล้ว ถ้าละไว้จะถือว่า fv เท่ากับ 0 (ตัวอย่างเช่น มูลค่าอนาคตของหนี้สินเท่ากับ • 0) • - Type คือตัวเลข 0 หรือ 1 ซึ่งใช้บ่งชี้กำหนดชำระเงิน

45. ตัวอย่างการใช้งาน NPER ใน Microsoft Excel 2007 ตัวอย่างการคำนวณยอดชำระและอัตราดอกเบี้ยคงที่

46. Pv PV ( present value หรือมูลค่าปัจจุบัน ) ของการลงทุน: มูลค่าในปัจจุบันของการชำระเงินทุกงวดทั้งหมดที่จะทำในอนาคต ตัวอย่างเช่น เมื่อขอยืมเงิน จำนวนเงินกู้เป็นมูลค่าปัจจุบันสำหรับผู้ให้ยืม

47. รูปแบบ - Rateคืออัตราดอกเบี้ยต่องวด ตัวอย่างเช่น ถ้ากู้เงินเพื่อยานยนต์ในอัตราดอกเบี้ยปีละ 10 เปอร์เซ็นต์ และชำระเป็นรายเดือน อัตราดอกเบี้ยต่อเดือนของคุณจะเท่ากับ 10%/12 หรือ 0.83% คุณจะต้องใส่ 10%/12 หรือ 0.83% หรือ 0.0083 ลงในช่องอัตราดอกเบี้ยของสูตร - Nperคือจำนวนงวดผ่อนชำระทั้งหมดในรอบปี ตัวอย่างเช่น ถ้ากู้เงินมาซื้อรถยนต์ในระยะเวลาสี่ปี และผ่อนชำระรายเดือน เงินกู้ของคุณจะเท่ากับ 4*12 (หรือ 48) งวด คุณจะต้องใส่ 48 ลงในช่อง nperของสูตร - Pmtคือการชำระเงินในแต่ละงวดและไม่สามารถเปลี่ยนแปลงได้ตลอดระยะเวลาหนึ่งปี โดยทั่วไป

48. รูปแบบ pmt ประกอบด้วยเงินต้นและดอกเบี้ย แต่ไม่รวมค่าธรรมเนียมอื่นๆ หรือภาษี ตัวอย่างเช่น การผ่อนชำระรายเดือนสำหรับเงินกู้เพื่อซื้อรถยนต์ $10,000 เป็นระยะเวลาสี่ปีโดยมีดอกเบี้ย 12 เปอร์เซ็นต์จะเท่ากับ $263.33 จะใส่ -263.33 ลงในช่อง pmt ของสูตร ถ้าไม่ระบุ pmt จะต้องใส่อาร์กิวเมนต์fv ไว้ด้วย - Fvเป็นมูลค่าในอนาคตหรือยอดเงินสดที่ต้องการสะสมให้ถึงหลังจากที่ชำระงวดสุดท้าย ถ้าไม่ระบุ fv จะถือว่าเป็น 0 (ตัวอย่างเช่น มูลค่าในอนาคตของเงินกู้เท่ากับ 0) เช่น ถ้าคุณต้องการเก็บเงิน $50,000 เพื่อชำระในโครงการพิเศษเป็นเวลา 18 ปี เงิน $50,000 ก็ถือว่าเป็นมูลค่าในอนาคต สามารถคาดเดาอัตราดอกเบี้ยได้ด้วยวิธีเดิม แล้วระบุว่าคุณต้องเก็บเงินเดือนละเท่าไร ถ้าไม่ระบุ fv คุณจะต้องรวมอาร์กิวเมนต์pmt ไว้ด้วย

49. ตัวอย่างการใช้งาน PV ใน Microsoft Excel 2007 ตัวอย่างการคำนวณยอดชำระและอัตราดอกเบี้ยคงที่

50. Rate ฟังก์ชัน RATE จะคำนวณโดยการวนซ้ำและสามารถมีการแก้ปัญหาได้ 0 วิธีหรือหลายวิธี ถ้าผลลัพธ์ของฟังก์ชัน RATE ที่ต่อเนื่องไม่เข้าใกล้ค่า 0.0000001 หลังจากมีการทำซ้ำถึง 20 ครั้ง ฟังก์ชัน RATE จะส่งกลับค่าความผิดพลาด #NUM!