278206 Application of Software Package in Office

278206Application of Software Package in Office การใช้งานโปรแกรมตารางคำนวณ (ต่อ) (Spread Sheet) อ.ธารารัตน์ พวงสุวรรณ thararat@buu.ac.th

การคำนวณโดยใช้ฟังก์ชันของ โปรแกรม Excel • โครงสร้างของฟังก์ชัน =ชื่อฟังก์ชัน(ค่าargument1,ค่าargument2,…) • สำหรับการป้อนค่า argument เราต้องทราบก่อนว่าฟังก์ชันนั้นรับค่า argument แบบใดบ้าง • อาจใส่ข้อมูลตัวเลขเข้าไปโดยตรง เช่น =SUM(1700,9800,7200)เพื่อให้หาผลรวม • หรืออาจกำหนดให้ฟังก์ชันอ้างอิงค่าในเซลล์ก็ได้ เช่น =SUM(E4:E7) • สำหรับการใช้ฟังก์ชันบางประเภทเราอาจต้องป้อนค่า argument ที่เป็นข้อความ เวลา หรือ วันที่โดยจะอยู่ภายในเครื่องหมาย “” เสมอ

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

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

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

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

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

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

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

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

ตัวอย่างการใช้งาน FunctionSumproduct ใน Microsoft Excel 2007

ฟังก์ชันการนับ Function • Function ในการนับจำนวนเซลล์ ใช้ในการนับจำนวนเซลล์ภายในพื้นที่ที่กำหนดภายใต้เงื่อนไขใดๆ • เช่น • COUNT • COUNTA • COUNTBLANK • และ COUNTIF

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

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

ฟังก์ชัน COUNTA/ COUNTBLANK • COUNTA • เป็นคำสั่งนับจำนวนของเซลล์ที่ไม่ว่างภายในช่วงที่ระบุ • COUNTBLANK • เป็นคำสั่งนับจำนวนของเซลล์ที่ว่างภายในช่วงที่ระบุ

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

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

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

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

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

ฟังก์ชัน Vlookup แบบค่าเป็นช่วง (Approximate Match)

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

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

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

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

ฟังก์ชันที่เกี่ยวกับการเงิน • PMT ช่วยในการคิดคำนวณเงินผ่อนชำระ • FV คำนวณมูลค่าในอนาคตของการลงทุน • NPER คำนวณจำนวนงวดทั้งหมดในการผ่อนชำระสำหรับการลงทุน • PV คำนวณมูลค่าปัจจุบันของการลงทุน • ฯลฯ Function

ฟังก์ชัน PMT • Function PMT ช่วยในการคิดคำนวณเงินผ่อนชำระ • สำหรับการคำนวณหาจำนวนเงินที่ต้องผ่อนชำระคืนในแต่ละงวด • เช่น กรณีเรากู้เงินจากธนาคารมาเพื่อซื้อบ้าน • ฟังก์ชันนี้จะคำนวณจำนวนเงินที่เราต้องผ่อนธนาคารในแต่ละเดือน Function

ฟังก์ชันที่เกี่ยวกับการเงิน ฟังก์ชันที่เกี่ยวกับการเงิน Function • รูปแบบ • - Rate อัตราดอกเบี้ยต่อเดือน (ถ้ากำหนดเป็นต่อปีต้องเอา 12 ไปหาร) • - Nperจำนวนเดือนในการผ่อนชำระ (ถ้ากำหนดเป็นรายปี ต้องเอา 12 ไปคูณ) • - Pvจำนวนเงินที่กู้มาจากธนาคาร • - Fv มูลค่าอนาคตของเงินกู้ หรือจำนวนเงินที่ต้องการให้คงเหลือหลังจากชำระงวดสุดท้าย ถ้าไม่ใส่จะถือว่าเป็น 0 • - Type ตัวเลขระบุวันครบกำหนดชำระเงิน 0 คือ ชำระเมื่อสิ้นงวด 1 คือชำระเงินเมื่อต้นงวด ถ้าไม่ใช่จะถือว่าเป็น 0

ตัวอย่างการใช้งาน PMT *** ผลลัพธ์ที่ได้จะติดลบ แสดงถึงเงินที่เราต้องจ่ายออกไป *** เวลาใช้งานจริง นิยมใส่เงินต้นในสูตรเป็นค่าติดลบแทน เพื่อทำให้ผลลัพธ์เป็นบวก

ตัวอย่างการใช้งาน PMT

ฟังก์ชัน Upper • ใช้ในการแปลงสายอักขระข้อความให้เป็นตัวอักษรพิมพ์ใหญ่ทั้งหมด • รูปแบบ =upper()

ฟังก์ชัน BAHTTEXT • แปลงค่าตัวเลขให้เป็นตัวอักษรแสดงจำนวนเงินในภาษาไทย • และเพิ่มต่อคำท้ายว่า “บาท” • รูปแบบ BAHTTEXT(จำนวน) • จำนวน คือตัวเลขที่ต้องการแปลงเป็นข้อความ หรือจะเป็นการอ้างอิงไปยังเซลล์ที่มีตัวเลขอยู่ หรือเป็นสูตรที่ได้ประเมินค่าออกมาเป็นตัวเลขก็ได้

การกำหนดคุณสมบัติเฉพาะให้กับเซลล์ในการแสดงค่าหรือทั้งกลุ่มของเซลล์ที่ต้องการการกำหนดคุณสมบัติเฉพาะให้กับเซลล์ในการแสดงค่าหรือทั้งกลุ่มของเซลล์ที่ต้องการ • การกำหนดคุณสมบัติเฉพาะให้กับเซลล์ เพื่อให้เซลล์นั้นรับข้อมูลที่ถูกต้องตามชนิดของข้อมูลและทำให้การแสดงผลทางหน้าจอมีความถูกต้องเหมาะสมตามที่ต้องการ มีขั้นตอนปฏิบัติดังนี้ • ทำการ Drag เลือกกลุ่มเซลล์ที่ต้องการ หรือคลิกเซลล์ที่ต้องการเพื่อให้เกิดกรอบสี่เหลี่ยมเข้มล้อมรอบเซลล์ที่ต้องการ • คลิกเมนู Format -> Cells จะเกิด Format Cells Dialog ดังรูป

การกำหนดคุณสมบัติเฉพาะให้กับเซลล์ในการแสดงค่าหรือทั้งกลุ่มของเซลล์ที่ต้องการการกำหนดคุณสมบัติเฉพาะให้กับเซลล์ในการแสดงค่าหรือทั้งกลุ่มของเซลล์ที่ต้องการ จาก Format Cells Dialog สามารถกำหนดคุณสมบัติเฉพาะให้กับเซลล์ในการแสดงค่าหรือทั้งกลุ่มของเซลล์ ได้ดังนี้ • การปรับคุณสมบัติในการแสดงตัวเลขให้เป็นแบบต่างๆ • การปรับคุณสมบัติในการจัดรูปแบบของข้อมูลในเซลล์ • การปรับคุณสมบัติของตัวอักษรที่แสดงในเซลล์ • ปรับคุณสมบัติของเซลล์ กำหนดกรอบ (Border) ให้แก่เซลล์ • การปรับคุณสมบัติของเซลล์ กำหนดสีพื้นให้แก่เซลล์

การปรับคุณสมบัติในการแสดงตัวเลขให้เป็นแบบต่างๆการปรับคุณสมบัติในการแสดงตัวเลขให้เป็นแบบต่างๆ • คลิกเมนูย่อย Number • ในส่วน Category เลือกรูปแบบการแสดงตัวเลขต่างๆ มีดังนี้

สำหรับการกำหนดรูปแบบการแสดงข้อมูลประเภทตัวเลข เราสามารถกำหนดได้จาก Formatting Toolbar • แสดงเครื่องหมายสกุลเงินนำหน้า • แปลงค่าตัวเลขให้เป็น % • แสดงเครื่องหมาย , คั่นตัวเลขที่เลือกทุกๆ 3 หลัก และมีทศนิยม 2 ตำแหน่ง • เพิ่มจำนวนหลักทศนิยม 1 หลัก • ลดจำนวนหลักทศนิยม 1 หลัก

การกำหนดรูปแบบตัวเลข • มีหลายรูปแบบ เช่น ทั่วไป ตัวเลข บัญชี หรือ กำหนดเอง • กำหนดเอง จะเห็นรูปแบบตัวเลขแสดงด้วยเครื่องหมาย ดังนี้ • # แทนตัวเลข เช่น #,### • 0 แทนเลขศูนย์ • _ เพิ่มช่องว่าง ถ้าใส่เป็น _) ค่าบวกจะเรียงตรงอยู่ในแถวเดียวกันกับค่าลบ • ; คั่นค่าบวก และค่าลบ • $ แสดงสกุลเงิน • และสามารถกำหนดสีให้กับการแสดงผลของตัวเลขได้ เช่น [แดง]

การกำหนดรูปแบบตัวเลข • เช่น [Blue]#,##0.00; [Red](#,##0.00) หมายถึง • ค่าบวก จะแสดง , เมื่อมีตัวเลขหลักพันขึ้นไป • ถ้าไม่มีตัวเลขใดๆใน cell หรือ range จะแสดง 0.00 • ค่าทั้งหมดจะแสดงด้วยตัวอักษรสีแดง • ค่าลบ จะแสดง , เมื่อมีตัวเลขหลักพันขึ้นไป • ถ้าไม่มีตัวเลขใดๆใน cell หรือ range จะแสดง 0.00 • ค่าทั้งหมดจะแสดงอยู่ภายในวงเล็บเล็ก และมีตัวอักษรเป็นสีเป็นแดง

การกำหนดเงื่อนไขให้รูปแบบตัวเลขการกำหนดเงื่อนไขให้รูปแบบตัวเลข • ทำได้โดยใส่เงื่อนไขไว้ในวงเล็บ [ ] เช่น • กำหนดให้จำนวนมากกว่า 5000 ใช้ตัวอักษรสีเขียว จำนวนต่ำกว่าหรือเท่ากับ 5000 ใช้ตัวอักษรสีแดง • จะได้เป็น [เขียว][>5000];[แดง][<=5000]

การจัดรูปแบบข้อมูลตามเงื่อนไขการจัดรูปแบบข้อมูลตามเงื่อนไข

การเปลี่ยนเงื่อนไขหรือรูปแบบที่กำหนดการเปลี่ยนเงื่อนไขหรือรูปแบบที่กำหนด

278206 Application of Software Package in Office