E-Book เรื่อง การใช้สูตรและฟังก์ชั่นในการคำนวณ

1หน่วยที่ 3การใชส้ ตู รและฟงั กช์ ันในการคานวณสาระสาคญั การใชโ้ ปรแกรม Excel อีกความสามารถหนึ่งที่สาคัญก็คือ การคานวณข้อมลู ในตารางโดยใชส้ ูตรและฟังก์ชัน การคานวณที่สามารถให้ผลลพั ธ์ไดอ้ ยา่ งแมน่ ยาและรวดเรว็หวั ข้อเรื่อง 3.1 การใช้สูตรคานวณ 3.2 การใชฟ้ งั กช์ นั คานวณจุดประสงคเ์ ชงิ พฤติกรรม 1. ปอ้ นสตู รคานวณในโปรแกรมได้ 2. แก้ไขสตู รคานวณได้ 3. ยา้ ยและคดั ลอกสตู รได้ 4. ปอ้ นสูตรแบบ Relative Addressing และ Absolute Addressing ได้ 5. ใช้สูตรคานวณกบั ข้อมูลทอ่ี ยตู่ า่ งชีตได้ 6. เขยี นสตู รฟงั กช์ นั การคานวณได้ 7. แกไ้ ขสตู รฟังกช์ นั การคานวณได้ 8. ใช้ฟงั ก์ชนั Autosum ในการหาผลรวมได้ 9. ใช้ฟังก์ชันทางคณติ ศาสตรท์ ่สี าคัญได้ 10. ใช้ฟงั ก์ชนั เกี่ยวกับตวั อกั ษรทีส่ าคัญได้ 11. ใช้ฟงั กช์ ันเกี่ยวกบั วันท่ีและเวลาทีส่ าคัญได้ 12. แกไ้ ขข้อผดิ พลาดของฟงั กช์ นั จากข้อผิดพลาดท่แี จง้ ได้เนอื้ หา3.1 การใช้สูตรคานวณ การคานวณของโปรแกรม Excel ไม่ได้มีเพียงการบวกลบคูณหารเหมือนโปรแกรมทั่วไป แต่มีความพิเศษคือ มีตาแหน่งของช่องตารางมาเก่ียวข้อง ช่วยเหลือผู้ใช้ในการแก้ไขหรืออ้างอิงข้อมูลจากเซลล์ต่าง ๆ ทม่ี าใชร้ ่วมในการคานวณได้อยา่ งแมน่ ยาและถูกต้อง

2 1. เข้าใจการใช้สูตรคานวณ เมื่อเราปอ้ นสูตรคานวณในตารางให้ใช้เครอ่ื งหมาย = (เทา่ กบั ) นาหนา้เสมอ มิฉะน้ันโปรแกรม Excel จะถือว่าสูตรท่ีป้อนเข้าไปน้ันเป็นข้อความธรรมดา และจะไม่นาไปคานวณเคร่ืองหมายที่ใช้ในการเขียนสูตร ได้แก่ เคร่ืองหมาย บวก (+) ลบ (-) คูณ (*) หาร (/) ยกกาลัง (^) และเครอื่ งหมายเปอร์เซน็ ต์ (%) เม่ือโปรแกรม Excel นาสูตรที่เราเขียนไปคานวณ ลาดับการคานวณจะเกิดข้ึนตามลาดับความสาคัญของเครือ่ งหมายดงั น้ี ตวั อยา่ ง สตู ร =(2*3)^2/4+7 จะมีลาดบั ขน้ั ตอนการคานวณตามลาดับดังน้ี =(2*3) = 6 =6^2 = 36 =36/4 = 9 =9+7 = 16Note ถ้าเคร่ืองหมายท่ใี ช้ในสตู รคานวณ มีลาดับความสาคญั เท่ากนั โปรแกรม Excel จะคานวณโดยกระทาจากทางซ้ายไปทางขวาของสูตร 2. เร่ิมต้นการป้อนสูตรคานวณ การคานวณ ใน Excel ทาได้ โดยการป้อนตวั เลขเข้าในเซลล์ แต่ละเซลล์จากนั้น จงึ กาหนดใหน้ าตัวเลขในแต่ละเซลล์ มา บวก ลบ คณู หาร กนั เร่มิ ตน้ ด้วยเคร่ืองหมาย = (เทา่ กับ)ลองดูตวั อย่าง และทาตามต่อไปน้ี การบวก 1. คลิกที่ตาแหนง่ A1 2. พิมพ์เลข 20 3. แล้วกด Enter เคอร์เซอร์ จะเลอื่ นมาท่ี เซลล์ A2 4. ท่ีตาแหน่ง A2 พิมพ์เลข 30 แลว้ กด Enter 5. เคอร์เซอร์ จะเล่อื นมาที่ A3 ใหพ้ มิ พ์ =A1+A2

3 6. เคร่ืองหมายเท่ากบั ข้างหน้า เปน็ การบอก Excel ว่า เป็นการใช้สูตร ไมใ่ ชเ่ ปน็ การพิมพ์ข้อความธรรมดา และจะสงั เกตสขี อง ข้อความ A1 และ A2 แตกต่างไปจากสธี รรมดา และเมื่อขณะพิมพ์ A1จะมีกรอบเกิดข้ึนที่ตาแหนง่ A1 ดว้ ย และขณะพิมพ์ A2 กจ็ ะมีกรอบเกิดทต่ี าแหน่ง A2 แสดงขอบเขตท่ีถูกเลอื ก ดงั นี้ 7. เม่อื พิมพ์เสรจ็ แล้ว ให้กดปมุ่ Enter กรอบจะเลื่อนไปยังตาแหนง่ A4 และจะไดผ้ ลลพั ธ์เทา่ กบั 50ในตาแหนง่ เซลล์ A3 ดังภาพ การลบ 1. คลิกท่ี ตาแหนง่ C1 พิมพ์เลข 30 แลว้ กด Enter 2. ที่ตาแหน่ง C2 และพมิ พ์เลข 20 แล้วกด Enter 3. ที่ตาแหนง่ C3 ให้พมิ พ์ =C1-C2 4. กดปมุ่ Enter จะไดผ้ ลลพั ธ์ คือผลตา่ ง ระหว่าง 30 และ 20 ซ่งึ เท่ากับ 10 ในตาแหน่งเซลล์ C3 การคณู 1. คลกิ ท่ี ตาแหน่ง E1 พมิ พเ์ ลข 3 แลว้ กด Enter 2. ท่ตี าแหน่ง E2 พมิ พ์เลข 2 แลว้ กด Enter 3. ท่ตี าแหนง่ E3 ให้พิมพ์ =E1*E2

4 4. กดปมุ่ Enter จะได้ผลลัพธ์ ซ่งึ เปน็ ผลคูณ ของ 3 และ 2 ซึง่ เท่ากับ 6 ในตาแหน่งเซลล์ E3 การหาร 1. คลกิ ที่ ตาแหน่ง G1 พิมพ์เลข 30 แลว้ กด Enter 2. ทต่ี าแหนง่ G2 พิมพเ์ ลข 5 แลว้ กด Enter 3. ท่ตี าแหนง่ G3 ใหพ้ ิมพ์ =G1/G2 4. กดปุ่ม Enter จะได้ผลลัพธ์ 30 หารดว้ ย 5 ซง่ึ เทา่ กบั 6 ในตาแหน่งเซลล์ G3 3. การแก้ไขสูตรคานวณ หากต้องการเปล่ียนแปลงสตู รท่ีกาหนดไว้ใหม่ เราสามารถแก้ไขสตู รคานวณในเซลล์ได้โดยดับเบิ้ลคลกิ ทเี่ ซลล์น้ัน และเข้าไปแก้ไขสูตรได้ทนั ที ดงั น้ี ดบั เบล้ิ คลกิ เซลลท์ ม่ี สี ตู ร  ทาการแก้ไขแล้วกดปุ่ม <Enter>ที่ตอ้ งการแกไ้ ข หรือกดปุ่ม F2 อกี วิธหี น่ึงในการแกไ้ ข คลิกเมาส์แก้ไขที่แถบสตู ร เมื่อทาการแกไ้ ขแล้วกดปุ่ม <Enter>ดังภาพ

5 4. การย้ายและคัดลอกสูตร เราสามารถย้ายหรือคัดลอกสูตรท่ีป้อนในตารางได้ โดยใช้วิธีเดียวกับการย้ายหรือคัดลอกข้อมูลธรรมดา แต่เพ่ือให้ได้ผลลัพธ์ที่ถูกต้องจากการคานวณ เราต้องทาความเข้าใจการคัดลอกแบบสัมพันธ์กับตาแหน่ง (relative addressing) และการคัดลอกแบบยึดตาแหน่งเดิม (absoluteaddressing) การคัดลอกแบบสัมพันธ์กับตาแหน่ง (relative addressing) เม่ือมีการคัดลอกหรือเคล่ือนย้ายสูตรไปยังเซลลอ์ ่ืน ผลลัพธ์ท่ีได้จากการคานวณจะเปลี่ยนไป เพราะโปรแกรม Excel จะเปลี่ยนตาแหน่งอ้างองิของเซลลใ์ นสูตรเป็นสัดส่วนตามระยะทางทเ่ี ซลลถ์ กู ย้ายหรือคดั ลอก ดงั ภาพ สูตรเม่อื ถูกคดั ลอกจะเปลีย่ นเซลล์ อ้างอิง ทาใหผ้ ลลัพธ์ท่ีได้ถูกต้อง การคัดลอกแบบยึดตาแหน่งเดิม (absolute addressing) เมื่อเราต้องการคัดลอกสูตรในตารางจะพบว่าวิธี relative addressing ไมส่ ามารถใช้ไดท้ กุ กรณี เพราะเราอาจจะไม่ต้องการให้ตาแหน่งอ้างอิงของเซลลเ์ ปล่ียนดังตวั อย่าง ดงั น้ี ผลลัพธ์ในเซลล์ B2:L13 ไดม้ าจากการคานวณของตวั เลขแถวท่ี 1 คูณกับตัวเลขของคอลัมน์ Aเริม่ แสดงการคานวณเซลลแ์ รกทีเ่ ซลล์ B2 สูตรคานวณ =B1*A2 ผลลัพธใ์ นเซลล์ต่อไปไดจ้ ากการคดั ลอก แต่เน่อื งจากค่าตวั เลขแถวท่ี 1 กบั คอลัมน์ A อยคู่ งที่ แต่การคดั ลอกผลลพั ธต์ ้องอ้างอิงตาแหน่งเซลล์ ดงั น้นั จะทาการคัดลอกแบบ relative addressing ไม่ได้

6 การอ้างองิ เซลล์ต้องใช้แบบ absolute addressing โดยใชเ้ ครอื่ งหมาย $ นาหน้าชอ่ื แถวหรอื ช่อืคอลัมน์ท่ีเราไม่ตอ้ งการให้เปลี่ยน ดังภาพNote ถ้าเราต้องการพิมพ์เครื่องหมาย $ หน้าช่ือคอลัมน์และหน้าชื่อแถว เราไม่ต้องพิมพ์เคร่ืองหมาย $เอง ใหก้ ดคยี ์ <F4> 5. การใชส้ ตู รกบั ข้อมลู ท่อี ยตู่ า่ งชตี ขอบเขตของการอา้ งอิงเซลล์ในสตู รไมไ่ ด้ถูกจากัดเฉพาะในแผ่นงานใดแผ่นงานหน่งึ เทา่ นั้น แตเ่ ราสามารถอ้างองิ เซลลท์ อ่ี ย่ตู า่ งแผน่ งานได้ การอ้างอิงเซลล์ท่ีอยู่ต่างแผ่นงาน สามารถทาได้โดยใช้ชื่อของแผ่นงานนาหน้าตาแหน่งของเซลล์ท่ีต้องการอ้างอิงในสูตร เช่น ในตัวอย่างเก็บรายละเอียดค่าสาธารณูปโภคของแต่ละเดือน โดยแยกเก็บเป็นแผ่นงาน ชอื่ “มิถุนายน” “กรกฎาคม” และ “สิงหาคม” ดงั รูปแผน่ งานชื่อ “มถิ ุนายน” แผน่ งานชอื่ “กรกฎาคม”

7 เม่ือเราต้องการหายอดรวมคา่ สาธารณปู โภค ท้งั 3 เดอื น บันทึกไวใ้ นแผ่นงานชอ่ื “สรุป” เราตอ้ งป้อนสูตรคานวณท่อี า้ งองิ ในแตล่ ะ แผน่ งาน ดังน้ีแผ่นงานชอ่ื “สงิ หาคม”3.2 การใช้ฟังกช์ นั คานวณ งานดา้ นสถติ ิ การเงนิ วันทีแ่ ละเวลา เปน็ งานท่ีตอ้ งการความแมน่ ยาสงูโปรแกรม Excel มชี ุดฟังก์ชนั จานวนมาก ตั้งแต่ฟงั กช์ ันการคานวณพนื้ ฐานไปจนถงึ ฟังกช์ ันทใ่ี ชเ้ ฉพาะสาขางานทผ่ี ใู้ ชส้ ามารถกาหนดขึน้ เองได้ 1. ส่วนประกอบของฟังก์ชัน ในโปรแกรม Excel มีฟังก์ชนั มากกว่า 300 ฟงั ก์ชนั สาหรับทาหนา้ ที่ต่าง ๆ อาทิเชน่ การคานวณตัวเลข การวเิ คราะหข์ ้อมูลสถิติการเงนิ และการจัดการฐานข้อมูล ช่อื ฟังกช์ นั แต่ละตวั นน้ั อาจมรี ายละเอียดการใชง้ านทแี่ ตกตา่ งกันไป แต่พอสรุปส่วนประกอบของฟังก์ชันได้ดงั นี้ชื่อฟงั กช์ นั ต้องเป็นตามท่ี Excel ร้จู กั เคร่อื งหมายจุลภาคใชแ้ บ่งคา่ argument ออกจากกัน=ช่อื ฟงั ก์ชัน(ค่า argument1,ค่า argument2,...)เมอื่ เราต้องการเขียนฟงั ก์ชนั เราตอ้ งข้นึ ต้นด้วยเคร่อื งหมาย ขอ้ มูลท่เี ราปอ้ นเพอื่ ใหฟ้ ังกช์ ันนาไปใชใ้ นการคานวณ“=” เช่นเดียวกบั การเขยี นสตู รคานวณทั่วไป ซึง่ ฟงั ก์ชนั อาจมีค่า argument ไดห้ ลายค่า สาหรับการป้อนค่า argument ในฟังก์ชัน เราจะต้องทราบก่อนว่าฟังก์ชันท่ีเรากาลังใช้งานอยู่น้ันรับคา่ argument แบบใดบ้าง ซึ่งเราอาจใสข่ ้อมูลที่เป็นตวั เลขเข้าไปได้โดยตรง เชน่ =SUM(200,800,1200) เพื่อ

8หาผลรวมของ 3 ค่า สาหรับฟังก์ชันบางประเภทเราต้องป้อนค่า argument ที่เป็นข้อความ เวลา หรือวันท่ีโดยจะตอ้ งอยรู่ ะหวา่ งเคร่ืองหมาย “ ” เสมอ 2. วิธีใช้งานสตู รของ Excel Excel มสี ตู ร หรือ ฟงั กช์ ัน สาหรบั การคานวณต่าง ๆ จานวนมากเช่น การบวก การหาคา่ สงู สุด การพมิ พ์ตวั เลขเป็นตวั หนงั สือ การหาค่าเฉลย่ี เปน็ ต้น การเรียกใช้งานสตู รของExcel ทาได้ 2 ลักษณะคอื 1. พมิ พ์สูตรโดยตรงท่ีแถบสูตร 2. เรยี กใช้งานสตู รจากหนา้ จอของ Excel ตวั อยา่ งตอ่ ไปนี้ จะเป็นการเรียกใชฟ้ งั กช์ ัน SUM ซง่ึ เปน็ คาส่ังให้นาข้อมูลมารวมกนั ดังนี้ 1. พิมพ์ 5 ในตาแหน่ง B1 แลว้ กด Enter 2. พิมพ์ 2 ในตาแหน่ง B2 แลว้ กด Enter 3. พมิ พ์ 6 ในตาแหนง่ B3 แลว้ กด Enter 4. ทตี่ าแหน่ง A4 พมิ พ์คาว่า ผลรวม 5. คลิกท่ีตาแหนง่ B4 เพ่ือนาผลรวมมาแสดงที่ตาแหน่งนี้ 6. คลกิ ท่ี ปุ่มแทรกฟังกช์ ัน ( ) ท่แี ถบสตู ร ดงั ภาพ 7. จะเห็นมี หนา้ จอ แทรกฟังกช์ นั หรือ Insert Function เกิดขึ้น ดังภาพ เพ่ือใหค้ ลกิ เลือกฟงั กช์ ันที่ต้องการนามาใช้ โดยในหนา้ จอน้ี มตี วั เลือกใหแ้ สดงสูตรของ Excel ในลักษณะต่าง ๆ เช่น การระบุคา แสดงฟังก์ชันทใ่ี ช้ไปลา่ สดุ แสดงตามประเภทการใช้งาน หรือแสดงทงั้ หมด เปน็ ตน้

9 8. ในท่ีนี้ เราต้องการการรวม จงึ เลือกฟังก์ชนั SUM แล้วกด OK 9. โปรแกรมจะเปดิ หน้าจอ อารก์ ิวเมนต์ของฟังกช์ ัน เพื่อให้ผใู้ ช้เลือกข้อมูลจากแผน่ งานมาใช้ในฟังกช์ ัน อารก์ ิวเมนต์ของฟงั ก์ชนั คือข้อมลู ท่ีจะให้ฟงั ก์ชันนาไปคานวณหาค่าทีต่ ้องการ 9. หนา้ จออารก์ วิ เมนต์ของฟังก์ชนั น้ี ใช้กบั ทุกฟงั กช์ นั เพอื่ ใหผ้ ใู้ ชเ้ ลอื กข้อมูลจากแผน่ งานมาใชใ้ นฟังก์ชันที่กาลังเรยี กใช้งาน สาหรบั ค่าที่จะนามาใช้ จะแตกต่างกนั ไปตามฟังก์ชนั ท่ีใช้สาหรบั ฟังก์ชนั SUM มีส่วนต่าง ๆ ที่นา่ สนใจ ดังนี้ o Number1 ข้อมลู ท่โี ปรแกรมจะนามารวมกัน จากภาพจะเหน็ วา่ โปรแกรมเดาเอาไวก้ ่อนวา่ เราตอ้ งการนาคา่ ขา้ งบนมารวมกัน จงึ ระบเุ ปน็ ข้อมูลที่อยู่ตาแหนง่ B1:B3 o ถา้ เราตอ้ งการเปลี่ยนค่าทโ่ี ปรแกรมเลือก ให้คลิกทปี่ ่มุ ตวั เลือก ( ) หนา้ จอ อาร์กิวเมนต์ ของฟงั ก์ชนั จะย่อลง เพ่อื ให้ผู้ใช้สามารถใช้เมาส์คลิกเลือกบรเิ วณข้อมูลบนแผน่ งานได้ o ถา้ มขี ้อมูลทจ่ี ะรวมอีกชุด กส็ ามารถเลอื กได้โดยคลิกหรอื พิมพใ์ นช่อง Number2

10 10. ในกรณนี ้ี ข้อมลู ถูกต้องแล้ว ใหค้ ลกิ OK 11. จะไดผ้ ลรวม ดงั ภาพ 3. แนะนาฟงั กช์ ันที่สาคญั โปรแกรม Excel มีฟงั กช์ นั การคานวณมากมาย แต่คงไมส่ ามารถกลา่ วถึงได้ครบทุกตวั จงึ ขออธิบายเฉพาะฟงั ก์ชนั ที่มีความสาคัญท่ีเราสามารถนาไปใชไ้ ด้ในงานทวั่ ไป หากเราต้องการรายละเอยี ดการใชฟ้ ังก์ชนั เพิ่มเติมขอคาแนะนาไดจ้ าก Helpฟังกช์ นั ทางคณติ ศาสตร์ฟงั กช์ นั รปู แบบการใชง้ าน ความหมายAVERAGE Average(กลุ่มเซลล์) หาคา่ เฉลี่ยของกลุ่มข้อมลู ตวั เลขMAX MAX(กลุม่ เซลล)์ หาค่าสงู สุดจากกลมุ่ ข้อมลู ตวั เลขMIN MIN(กล่มุ เซลล์) หาคา่ ต่าสดุ จากกลมุ่ ข้อมูลตวั เลขROUND ROUND(ตวั เลข,จานวนหลกั ) ปัดเศษตวั เลขทศนิยมให้มหี ลักทศนยิ มตามที่กาหนดSUM SUM(กลมุ่ เซลล)์ หาผลรวมของกลมุ่ ขอ้ มูลตัวเลข ตวั อย่างตอ่ ไปน้ี เป็นการใช้ฟังกช์ นั AVERAGE และ MAX เพอื่ หาค่าเฉลีย่ และยอดเงนิ สูงสุดของสรปุค่าสาธารณูปโภคเดอื นมิถุนายน-สิงหาคม 2559 โดยนาจานวนเงนิ ในเซลล์ C3-C7 มาใช้ในการคานวณ ดังนี้ =AVERAGE(C3-C7) =MAX(C3-C7)

11ฟังก์ชนั ทางตรรกศาสตร์ IF ทดสอบเงื่อนไขที่กาหนดและเลือกคาตอบทีถ่ ูกต้องรปู แบบการใชง้ าน=IF(เง่อื นไข,ส่วนทกี่ ระทาถ้าเงือ่ นไขเปน็ จริง,สว่ นทกี่ ระทาถา้ เงือ่ นไขเป็นเทจ็ ) ตัวอยา่ ง ความหมาย =IF(F4>10,1,2) ตอบค่า 1 ถา้ เซลล์ F4 มคี า่ มากกวา่ 10 ตอบค่า 2 ถา้ เซลล์ F4 มคี า่ ต้ังแต่ 10 ลงมา =IF(F4>50,“PASS”,“FAIL”) ตอบ PASS ถา้ เซลล์ F4 มีค่ามากกวา่ 50 ตอบ FAIL ถา้ เซลล์ F4 มีค่าตั้งแต่ 50 ลงมา ตวั อย่างต่อไปน้เี ป็นการใชฟ้ ังกช์ ัน IF แสดงผลการพิจารณาผลสอบสมั ภาษณ์พนักงานขาย โดยใช้ฟงั กช์ นั IF ช่วยตรวจสอบวา่ ถ้าค่าในเซลล์ C3-C7 ค่าคะแนนตั้งแต่ 60 ขน้ึ ไป ผลการสอบ ผ่าน นอกนั้น ไม่ผ่านฟังก์ชนั ทางสถิติฟงั กช์ ัน รูปแบบการใช้งาน ความหมายCOUNT COUNT(กลมุ่ เซลล์) นับจานวนเซลลท์ ่มี ขี อ้ มูลตวั เลขในกลุ่มเซลล์ที่เลือกCOUNTA COUNTA(กลุ่มเซลล์) นับจานวนเซลลท์ มี่ ีข้อมูลในกลุม่ เซลล์ทีเ่ ลอื กCOUNTIF COUNTIF(กลุ่มเซลล์,เงอื่ นไข) นับจานวนเซลล์ท่ีตรงตามเงื่อนไขในกลุ่มเซลล์ทเี่ ลือก

12ตัวอย่าง เราสามารถใชฟ้ ังก์ชัน COUNTIF หาจานวนพนักงานขายที่สอบสัมภาษณ์ “ผ่าน” ดังน้ีฟงั ก์ชันทางการเงินฟังกช์ นั รูปแบบการใช้งาน ความหมาย หาจานวนเงินท่ีตอ้ งผ่อนชาระต่องวด สาหรบั เงินกู้ทีม่ ีPMT PMT(อตั ราดอกเบย้ี ตอ่ เดือน, จานวนงวดผ่อนชาระ และอัตราดอกเบี้ยคงท่ี จานวนงวด,เงินตน้ ) ตวั อยา่ งต่อไปนเ้ี ปน็ การใชฟ้ ังกช์ นั PMT คานวณหาจานวนเงินทเี่ ราต้องการผ่อนชาระคอนโดมเิ นียมมลู ค่า 650,000 บาทในแตล่ ะเดือน โดยใชอ้ ตั ราดอกเบี้ยทีแ่ ตกตา่ งกันฟังกช์ นั ในการคน้ หา ฟังกช์ นั VLOOKUP ใช้สาหรับคน้ หาข้อมลู ทต่ี ้องการจากตารางข้อมลู ท่ีกาหนด โดยคน้ หาขอ้ มลู จากบนลงลา่ ง ผลลพั ธ์ทไี่ ดข้ องการคน้ หาจะไดจ้ ากคอลัมน์ทตี่ ้องการรปู แบบการใช้งาน =VLOOKUP(คา่ ท่ใี ชใ้ นการคน้ หา,ตารางข้อมูลทใ่ี ช้ในการค้นหา,คอลมั น์ทตี่ ้องการข้อมูล, รปู แบบการคน้ หา)

13 ค่าทีใ่ ชใ้ นการคน้ หา คือขอ้ มูลทจ่ี ะนาไปคน้ หาเปรยี บเทยี บกับข้อมลู ในตาราง โดยจะคน้ หาในคอลมั น์ แรกของตาราง ระบุชอื่ เป็นเซลลห์ รือข้อมูลกไ็ ด้ เชน่ D4 หรอื ระบุเป็น C เป็นตน้ ตารางขอ้ มลู ทีใ่ ช้ในการ คอื กลุม่ เซลลท์ ตี่ อ้ งการค้นหาข้อมลู ระบเุ ป็นกลุ่มเซลล์ เช่น H4:H7 หรอื หากมกี ารต้งั ค้นหา ชอ่ื เซลลแ์ ลว้ สามารถระบุเป็นช่ือเซลลไ์ ดเ้ ชน่ อัตราโบนสั คอลัมนท์ ี่ตอ้ งการ คอื ตาแหน่งของคอลมั น์ทีเ่ ราต้องการขอ้ มลู ในตารางการค้นหา ระบุเปน็ ตัวเลข เรม่ิ ขอ้ มลู ต้ังแต่ 1 แตไ่ ม่เกนิ จานวนคอลมั นข์ องตารางข้อมลู ท่ใี ชใ้ นการค้นหา รปู แบบการค้นหา ระบุรูปแบบการคน้ หาวา่ คา่ ท่ใี ช้ในการค้นหาจะต้องตรงกับขอ้ มลู ในตารางท่ีตอ้ งการ ค้นหาหรอื ไม่ มีค่านอ้ ยกว่า หรือเทา่ กับกไ็ ด้ ซงึ่ สามารถระบไุ ด้ 2 รปู แบบคือ  False ค้นหาค่าในตารางที่ตรงกับคา่ ทใี่ ช้ในการค้นหาเทา่ นัน้ ข้อมูลในตาราง ไมจ่ าเปน็ ต้องเรยี งลาดบั ข้อมลู  True คน้ หาค่าในตารางทม่ี ีคา่ นอ้ ยกว่าหรือเท่ากับค่าที่ใชใ้ นการคน้ หา โดยจะ หาค่าทใ่ี กลเ้ คยี งทส่ี ดุ และขอ้ มลู ในตารางต้องเรยี งลาดับข้อมลู จากนอ้ ยไปมาก หากไมม่ ีการกาหนดไวใ้ นฟังก์ชันจะหมายถึงคา่ True ตวั อย่างการใช้งานฟังก์ชนั VLOOKUP โดยใชค้ ้นหาอตั ราโบนสั เพ่ือนามาคานวณคา่ โบนัสของพนกั งาน ตารางข้อมลู ทใ่ี ช้ในการค้นหา คอื กล่มุ เซลล์ C14:D18 (ตัง้ ช่ือเป็นอัตราโบนัส) คอลัมนท์ ่ีต้องการข้อมูล คือคอลัมน์ที่ 2 ของตารางข้อมูลท่ีใช้ในการคน้ หา คา่ ท่ีใชใ้ นการคน้ หา ตารางข้อมลู ทใี่ ช้ในการค้นหา จากฟงั ก์ชัน =VLOOKUP(D4,$C$14:$D$18,2,FALSE)*E4 ขอ้ มลู ในเซลล์ D4 คอื C นน่ั คือจะค้นค่า C ในกล่มุ เซลล์ C14:D18 โดยจะค้นหาค่าในคอลมั น์ท่ี 1 คือค้นหาจากเซลล์ C14:D18 และคอลัมนท์ ี่ต้องการข้อมลู คือคอลมั นท์ ี่ 2 จากตวั อย่างจะคน้ หาขอ้ มลู C และขอ้ มูลทตี่ ้องการในคอลมั น์ 2 คือ 1.5 แต่ผลลพั ธท์ เี่ ราตอ้ งการคานวณคอื โบนัส เมอ่ื คน้ หาได้อตั ราโบนสั 1.5 แล้วนาเปน็ คูณจากค่าเงินเดือน สาเหตทุ ี่ต้องระบตุ าแหนง่ แบบ Absolute Addressing เน่ืองจากไม่ต้องการให้ตาแหน่งเซลล์เปล่ยี นเมื่อทาการคดั ลอกข้อมูล

14ฟังกช์ นั เก่ียวกับวนั ทีแ่ ละเวลาฟังกช์ นั รูปแบบการใช้งาน ความหมาย ให้ค่าผลลพั ธ์เปน็ วันท่ีปจั จบุ ันTODAY TODAY() ให้คา่ ผลลพั ธเ์ ชงิ อันดบั ของวนั ท่ี แปลงขอ้ ความที่อยู่ในรปู ของวันที่ ใหเ้ ป็นคา่ ตวั เลขเชงิDATE DATE(ปี,เดอื น,วนั ) อนั ดบั ของวนั ที่ ใหค้ ่าผลลัพธ์เปน็ คา่ วันทจ่ี ากตัวเลขเชิงอันดับDATEVALUE DATEVALUE(ข้อความในรูป ใหค้ ่าผลลพั ธเ์ ปน็ คา่ ของเดอื นจากตัวเลขเชิงอนั ดับ ให้ค่าผลลพั ธเ์ ปน็ ค่าของปีจากตัวเลขเชงิ อนั ดับ วันท่ี) ใหค้ า่ ผลลพั ธ์เป็นวันทแ่ี ละเวลาปัจจุบัน ใหค้ า่ เป็นชวั่ โมงจากตวั เลขเชิงอนั ดบัDAY DAY(ตัวเลขเชงิ อนั ดบั ) ใหค้ า่ เปน็ นาทีจากตวั เลขเชิงอันดบั ใหค้ ่าเป็นวินาทจี ากตัวเลขเชิงอันดับMONTH MONTH(ตวั เลขเชิงอันดบั ) ให้ค่าผลลพั ธเ์ ชิงอันดบั ของเวลาYEAR YEAR(ตวั เลขเชงิ อันดบั )NOW NOW()HOUR HOUR(ตัวเลขเชงิ อันดบั )MINUTE MINUTE(ตัวเลขเชงิ อันดับ)SECOND SECOND(ตวั เลขเชงิ อันดับ)TIME TIME(ชว่ั โมง,นาที,วนิ าท)ีNOTE ตวั เลขเชิงอนั ดับ คือรหสั เวลา – วันท่ี ซ่ึงใชใ้ นโปรแกรม Excel เพือ่ ใชใ้ นการคานวณวันทแ่ี ละเวลา ซง่ึอาจอยู่ในรปู แบบของข้อความที่กาหนด วนั ท่ีหรือเวลา เช่น “15-6-2016” หรอื “16-June-2016” หรือ“2:45:30 PM” เปน็ ต้นตวั อยา่ งการใชง้ าน ได้ผลลพั ธ์ 10/6/2016 (วันทปี่ จั จุบนั ขณะนั้น) ได้ผลลพั ธ์ 9/6/2016 (ยอ้ นหลังจากวันทป่ี ัจจบุ นั 1 วัน) =TODAY() ไดผ้ ลลพั ธ์ 15/6/2016 =TODAY()-1 ได้ผลลัพธ์ 29/6/2016 =DATE(2016,6,15) ได้ผลลัพธ์ 7 =DATEVALUE(“29/6/2016”) ไดผ้ ลลพั ธ์ 6 =DAY(“7/6/2016”) ไดผ้ ลลพั ธ์ 2016 =MONTH(“4 June 12”) ไดผ้ ลลัพธ์ 10/6/2016 (วนั ทแี่ ละเวลาปัจจบุ นั ขณะนั้น) =YEAR(“15-6-16”) ได้ผลลพั ธ์ 19 =NOW() ไดผ้ ลลพั ธ์ 30 =HOUR(“7:30:45 PM”) ได้ผลลพั ธ์ 18 =MINUTE(“7:30:45 PM”) ไดผ้ ลลพั ธ์ 12:10 PM =SECOND(“12:10:18”) =TIME(12,10,18)

15ฟงั ก์ชนั ทเี่ ก่ียวขอ้ งกบั ค่าเงินบาท BahtText ใชแ้ ปลงหมายเลขให้เปน็ ตัวอักษรค่าภาษาไทย เช่น =BahtText(500) ได้ผลลพั ธ์เป็น“หา้ รอ้ ยบาทถ้วน” 4. การแกไ้ ขฟงั กช์ นั สาหรบั การแก้ไขฟงั กช์ ันนนั้ คลา้ ยกับการแก้ไขสูตรคานวณ แต่มีบางเทคนิคที่ขอแนะนาเพิม่ เติมท่จี ะชว่ ยให้สะดวกรวดเรว็ ยิ่งขนึ้ เปล่ียนเซลล์ท่ีอ้างอิงในสูตร การใช้ฟังก์ชันมักมีการอ้างอิงกลุ่มเซลล์ เมื่อใดที่เราดับเบิ้ลคลกิ เซลล์ที่มีฟังก์ชันที่ต้องการแก้ไข Excel จะแสดงฟังก์ชันท่ีใช้ และแสดงกรอบสีล้อมรอบเซลล์ในตารางท่ีถูกอ้างอิงในฟงั กช์ นั ถ้าเราตอ้ งการเปลยี่ นเซลลท์ อ่ี า้ งอิงใหเ้ ลอ่ื น ไปท่ีกรอบสีจากนน้ั ให้คลกิ และลากเมาส์ไปยังเซลล์ใหมท่ ่ีต้องการอา้ งอิงแทน ดังภาพ คลกิ เมาส์ที่กรอบสลี ้อมรอบเซลลท์ ีฟ่ ังกช์ ันอา้ งอิง ดบั เบล้ิ คลกิ ฟังกช์ นั ทต่ี ้องการแก้ไข ลากเมาสเ์ ลอื่ นกรอบสีไปเซลล์ใหมท่ ี่ต้องการอา้ งองิ กด <Enter> ฟังก์ชันที่เราแก้ไขเปล่ียนไปอา้ งองิ เซลล์ตามขอบเขตทกี่ าหนด 5. การใช้ AutoSum เนือ่ งจาก SUM เปน็ ฟงั กช์ นั ทมี่ ีการใช้งานมาก โปรแกรม Excel จึงมีAutoSum ท่สี ามารถหาผลรวมไดโ้ ดยมขี ัน้ ตอนน้อยกว่า ทาใหเ้ ราไมต่ ้องเสียเวลาเขียนฟังกช์ ัน SUM ทุกครงั้ ท่ีต้องการหาผลรวมตวั เลขในกลมุ่ เซลล์

16 ไปทแี่ ท็บสตู ร (Formulas) และ คลกิ ผลรวมอตั โนมัติ (AutoSum) คลิกเซลลท์ ต่ี ้องการผลลพั ธ์ ผลลพั ธท์ ี่ได้จากการท่ีคลกิ เมาสผ์ ลรวมอัตโนมตั ิ กด <Enter>ถ้าเราต้องการเปล่ยี นการคานวณเปน็ อยา่ งอ่นื นอกจากการหาผลรวม ใหค้ ลิกป่มุ ที่ดงั น้ี คลิกแทบ็ หน้าแรก  คลกิ ที่

17รูปแบบการคานวณมรี ายละเอยี ดดง้ นี้รูปแบบ ความหมายSum หาผลรวมของตัวเลขในกลมุ่ เซลลท์ เี่ ลือกAverage หาค่าเฉล่ียของตัวเลขในกลมุ่ เซลลท์ เี่ ลือกCount Nums นบั จานวนเซลล์ทเี่ ลือก โดยเฉพาะเซลลท์ ่ีมขี ้อมลู ตัวเลขMax หาค่าสงู สุดของตวั เลขในกลมุ่ เซลล์ทเี่ ลือกMin หาคา่ ตา่ สุดของตัวเลขในกลมุ่ เซลล์ทเ่ี ลือก 6. แสดงขอ้ ผดิ พลาดจากการเขียนสตู รและฟงั ก์ชันผิด ถ้าเราเขยี นสูตรคานวณหรือฟังกช์ ันผิดโปรแกรม Excel จะพยายามเสนอแนวทางแก้ไขข้อผดิ พลาดให้โดยอัตโนมัติ เช่น ถ้าเราพมิ พส์ ตู ร“=COUNTIF(D3:D7,\"ผา่ น) เมื่อเรากด <Enter> โปรแกรม Excel จะแจง้ ให้เราทราบวา่ สูตรท่ีเขยี นนน้ั ผิดและจะเสนอแนวทางแก้ไขที่คาดว่าถูกใหด้ ว้ ย ถา้ ต้องการให้ Excel แก้ไขสูตรทเ่ี ราเขยี นตามนนั้ ให้คลกิ ปุ่ม“ใช่” แต่ถา้ ต้องการแก้ไขสูตรเอง ใหค้ ลิกป่มุ “ไมใ่ ช่” เขยี นสูตรคานวณผดิ และกด <Enter> Excel จะแจง้ ให้ทราบว่าสูตรท่พี ิมพ์ ผิดและเสนอแนวทางแก้ไขทคี่ าดวา่ ถูกให้ ถ้าต้องใหค้ ลกิ ปุ่ม “ใช่” แต่ถ้าตอ้ งการ แกไ้ ขสตู รเอง ให้คลกิ ปุ่ม “ไม่ใช่” ในกรณีสตู รคานวณ หรอื ฟังก์ชนั ทเ่ี ราเขยี นถกู ต้องตามหลักไวยกรณ์ แตเ่ กดิ ปญั หาเม่อื ถกู นามาคานวณ โปรแกรม Excel จะแสดงขอ้ ความบอกความผดิ พลาดทแ่ี ตกต่างกันไปตามลักษณะของปญั หาที่พบซึง่ ขอ้ ความผดิ พลาดทแี่ สดงแตล่ ะแบบมีความหมายและแนวทางแก้ไข ดังนี้

18ขอ้ ความ ความหมาย แนวทางแก้ไข#DIV/0! ตวั หารทีใ่ ช้ในสูตร มีคา่ เปน็ 0 ซง่ึ ปัญหานีอ้ าจเกดิ จาก ตรวจสอบค่าตวั หารท่ีใช้ในสตู ร และ การใช้ค่า 0 เป็นตวั หารตรง ๆ หรอื อาจเกดิ จากผลลัพธ์ แก้ไขให้ถูกตอ้ ง#N/A ที่ได้จากการคานวณในสตู รทีท่ าใหไ้ ด้ค่าตวั หารเป็น 0#NAME? นอกจากนั้น ปัญหาน้ียังเกดิ ได้จากการอ้างองิ เซลลเ์ ปล่า ตรวจสอบคา่ argument ใดบ้างที่#NULL! เป็นตวั หาร เรายงั ไมไ่ ด้ใส่ในฟังกช์ นั#NUM ย่อมาจาก “No value is available” หมายความวา่#REF เรายังระบคุ า่ argument ที่ใชก้ บั ฟงั ก์ชันไมค่ รบ ตรวจสอบว่าเราอ้างองิ ชื่อเซลล์ต่าง ๆ#VALUE! นอกจากนั้น ปัญหานอี้ าจเกดิ จากการท่ีสูตรอ้างอิงเซลล์ ถูกตอ้ งหรอื ไม่ และตรวจสอบการ ทีม่ คี า่ เป็น #N/A อยู่แลว้ พมิ พ์ช่ือฟงั กช์ นั ท่ีใชว้ ่าถกู ต้องหรือไม่ เกดิ จากการอ้างอิงช่ือฟงั กช์ ันผดิ เชน่ เราตอ้ งการใช้ ฟงั กช์ ัน SUM หาผลรวมตัวเลขในกลมุ่ เซลล์ C3:C9 ตรวจสอบการอา้ งองิ กลุ่มเซลลใ์ น แทนท่จี ะพิมพ์ ““=SUM(C3:C9)”เราพมิ พผ์ ดิ เปน็ สตู ร “=SAM(C3:C9)” ความผิดพลาดนเ้ี กิดเม่อื มกี ารอ้างอิงกลมุ่ เซลล์ที่ไม่ ตรวจสอบค่าตวั เลขท่ใี ชเ้ ป็นคา่ ถูกต้องเชน่ การเขยี นสูตร “SUM(C3:D3 C4:D4)” ซึ่ง argument ในฟงั ก์ชัน และการ ในกลุม่ เซลลท์ ีอ่ ้างอิงมที ั้งคา่ ตัวเลขและข้อความ ทาให้ อา้ งองิ เซลลใ์ นสตู รคานวณเพ่ือไม่ให้ เกิดข้อความ #NULL! เกดิ การคานวณแบบวนรอบ ความผิดพลาดนเี้ กดิ ได้จากหลายสาเหตุ เช่นใช้ฟังก์ชัน .ใช้ Undo ยกเลิกการลบเซลล์ทีส่ ตู ร หารากท่สี อง SQRT เรากาหนดใหห้ าค่าตดิ ลบ หรือใน คานวณอา้ งอิง หรือแก้ไขสตู รให้ การหาผลลัพธส์ ูตรคานวณเกดิ การวนรอบ ทาให้ Excel อ้างอิงเซลล์อ่นื หาผลลัพธ์ไมไ่ ด้ ตรวจสอบคา่ argument ท่ใี ชใ้ น เซลลท์ ่ีสูตรคานวณอา้ งอิงถกู ลบทาใหโ้ ปรแกรม Excel ฟงั ก์ชัน และชนดิ ขอ้ มลู ทใ่ี ชใ้ นสตู ร หาผลลพั ธไ์ ม่ได้ คานวณใหถ้ กู ตอ้ ง มกี ารใช้คา่ argument ในฟงั ก์ชนั หรือกาหนดชนิด ข้อมูลทใ่ี ช้ในสตู รคานวณผดิ ประเภท เช่น เรากาหนดให้ นาข้อมลู ท่ีเปน็ ข้อความมาคานวณกบั ขอ้ มลู ท่ีเปน็ ตวั เลข