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. แกไ้ ขข้อผิดพลาดของฟงั กช์ ันจากข้อผดิ พลาดทีแ่ จ้งได้

2เนื้อหา3.1 การใช้สูตรคานวณ การคานวณของโปรแกรม Excel ไม่ได้มีเพียงการบวกลบคูณหารเหมอื นโปรแกรมทั่วไป แต่มีความพิเศษคือ มีตาแหน่งของช่องตารางมาเกี่ยวข้อง ช่วยเหลือผู้ใช้ในการแก้ไขหรืออ้างอิงข้อมูลจากเซลล์ต่าง ๆ ทมี่ าใช้รว่ มในการคานวณได้อย่างแม่นยาและถกู ต้อง 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 3. แลว้ กด Enter เคอร์เซอร์ จะเลอ่ื นมาท่ี เซลล์ A2 4. ที่ตาแหนง่ A2 พิมพ์เลข 30 แลว้ กด Enter 5. เคอรเ์ ซอร์ จะเล่อื นมาที่ A3 ใหพ้ มิ พ์ =A1+A2 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 ขึ้นไป ผลการสอบ ผ่าน นอกนั้น ไม่ผา่ นฟังกช์ ันทางสถิติฟงั กช์ ัน รูปแบบการใช้งาน ความหมายCOUNTCOUNTA COUNT(กล่มุ เซลล)์ นับจานวนเซลลท์ ่มี ขี ้อมูลตัวเลขในกลุม่ เซลล์ที่เลอื กCOUNTIF COUNTA(กลุ่มเซลล์) นบั จานวนเซลลท์ มี่ ขี อ้ มลู ในกล่มุ เซลล์ทเ่ี ลือก 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 ในฟงั ก์ชัน หรือกาหนดชนดิ ข้อมลู ท่ีใชใ้ นสตู รคานวณผดิ ประเภท เช่น เรากาหนดให้ นาข้อมลู ทเ่ี ปน็ ขอ้ ความมาคานวณกับขอ้ มลู ท่เี ป็นตวั เลข