การใช้สูตรและฟังก์ชั่น

119 โปรแกรมตารางงาน ภาควชิ าคอมพวิ เตอรธ์ ุรกิจ วอศ.มค. ภาควชิ โาปครอแมกพรวิมเตาอรราธ์ งุรงกานิจ ว: อคศรูจ.มุฬคา.ลักษณ์ ถาไชย โปรแกรลมาตารางงาน : ครูจฬุ าลักษณ์ ถาไชยลา โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

120 ใบความรูท้ ี่ 5 การใชส้ ูตรและฟังกช์ ันในการคานวณ ผู้สอน : จฬุ าลักษณ์ ถาไชยลา ***************** 1. โครงสร้าง สญั ลกั ษณ์ และลาดับความสาคัญของเคร่อื งหมายท่ใี ช้ในการคานวณ 1.1 โครงสร้าง การใช้สตู รคานวณจะตอ้ งพมิ พ์เครอ่ื งหมายเท่ากบั (=) นาหนา้ เสมอตามด้วยตัวแปร และตวั ดาเนินการ ตัวแปรนี้อาจเป็นคา่ คงที่ ตาแหน่งเซลล์ หรอื ฟังกช์ นั กไ็ ด้ โดยผลลพั ธ์จะอยู่บนเซลล์ ใดเซลลใ์ ดเซลลห์ นง่ึ ทเ่ี ลือกไว้ ยกตัวอย่างเช่น =A1+B1, =C2-D3, =(D3/C2)*( A1+B1), =(G3-B1)- (F4*G4), =2*3 เปน็ ต้น 1.2 สัญลักษณ์ สญั ลกั ษณท์ ใ่ี ชเ้ ป็นเครื่องหมายในการคานวณมดี ังนี้ โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

121 1.3 ลาดบั ความสาคัญของเครอ่ื งหมายในการคานวณ การคานวณนั้นจะมลี าดบั ความสาคญั ของเคร่ืองหมายการคานวณตา่ งกัน ซ่งึ โปรแกรม จะคานวณจากลาดับความสาคัญแรกไปยงั ลาดบั ความสาคญั รองลงมาตาม ลาดับ แต่ถ้าเคร่อื งหมายคานวณอย่ใู นระดบั เดียวกัน โปรแกรมจะคานวณจากซ้ายไปขวา ลาดบั ความสาคญั ของเครอ่ื งหมายในการคานวณ ตวั ดาเนินการในการคานวณ เครอ่ื งหมายหรือตวั ดาเนนิ การคือ องค์ประกอบหนึง่ ในสตู รโดยจะระบชุ นิดของการคานวณท่ีต้องการ ซงึ่ Excel จะแบง่ ตวั ดาเนนิ การออกเปน็ 4 ประเภท คือ ตัวดาเนินการทางคณิตศาสตร์ ตัวดาเนินการ เปรียบเทยี บ ตวั ดาเนินการกบั ข้อความ และตัวดาเนนิ การการอ้างองิ 1. ตวั ดาเนนิ การทางคณิตศาสตร์ เมื่อต้องการดาเนนิ การคานวณด้วยวธิ ีทางคณิตศาสตร์ขัน้ พืน้ ฐาน เช่น การบวก การลบ การคูณ รวมตัวเลข และการหาผลลพั ธเ์ ป็นตัวเลขตา่ งๆ คุณสามารถใชต้ ัวดาเนนิ การทางคณิตศาสตร์ต่อไปนี้ ตัวดาเนนิ การเลขคณิต ความหมาย ตวั อย่าง + (เคร่ืองหมายบวก) การบวก 5+8 การลบ 9–4 - (เครอ่ื งหมายลบ) นเิ สธ –1 การคูณ 6*7 * (เคร่ืองหมายดอกจัน) การหาร 8/5 / (เครื่องหมายทับ) เปอรเ์ ซ็นต์ 50% % (เคร่ืองหมายเปอรเ์ ซน็ ต์) เลขยกกาลัง ^ (เครือ่ งหมายหมวก) เลขชีก้ าลงั (4^2) โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

122 2. ตวั ดาเนินการตอ่ ขอ้ ความ ใช้เครือ่ งหมาย 'and' (&) เพ่ือรวมหรือเช่ือมขอ้ ความ 2 ขอ้ ความข้นึ ไป เพ่อื รวมเป็นขอ้ ความเดียวกนั ตวั ดาเนนิ การขอ้ ความ ความหมาย ตัวอย่าง & (เครอื่ งหมาย 'and') เชอื่ มตอ่ หรือตอ่ ค่าสองคา่ เพอ่ื รวมเปน็ ค่าขอ้ ความต่อเน่อื งกนั (\"Excel\"&\"2016\") 3. ตวั ดาเนินการเปรยี บเทียบ ใช้เปรยี บเทยี บค่าสองค่าด้วยตัวดาเนนิ การต่อไปน้ี เมอ่ื ทาการเปรยี บเทียบค่าสองค่าโดยใช้ตวั ดาเนินการ เหลา่ นี้ ผลลัพธ์ทไี่ ด้จะเป็นค่าตรรกะ TRUE (จริง) หรือ FALSE (เทจ็ ) ตัวดาเนินการเปรยี บเทียบ ความหมาย ตัวอย่าง = (เครื่องหมายเทา่ กบั ) เทา่ กับ A1=B1 > (เครอื่ งหมายมากกวา่ ) มากกวา่ A1>B1 < (เคร่อื งหมายน้อยกวา่ ) นอ้ ยกวา่ A1<B1 >= (เคร่ืองหมายมากกวา่ หรอื เทา่ กับ) มากกว่าหรอื เท่ากับ A1>=B1 <= (เครือ่ งหมายน้อยกว่าหรือเท่ากับ) น้อยกว่าหรือเท่ากับ A1<=B1 <> (เครอ่ื งหมายไม่เทา่ กับ) ไม่เท่ากบั A1<>B1 4. ตวั ดาเนินการการอ้างองิ ใชร้ วมช่วงของเซลล์สาหรับการคานวณด้วยตวั ดาเนนิ การตอ่ ไปนี้ ตัวดาเนนิ การอ้างองิ ความหมาย ตัวอยา่ ง : (Colon) ตัวดาเนินการช่วง ซงึ่ สรา้ งการอา้ งองิ ไปยังเซลล์ทงั้ หมดที่อยู่ระหว่าง B5:B15 การอา้ งองิ สองค่า รวมทงั้ การอ้างองิ ทั้งสองคา่ นั้นดว้ ย , (Comma) ตัวดาเนนิ การยูเนียน ซึ่งเป็นการรวมหลายๆ การอ้างองิ เข้าดว้ ยกนั SUM(B5:B15,D5:D15) เป็นหนึง่ การอา้ งองิ (ช่องวา่ ง) ตัวดาเนนิ การอนิ เตอรเ์ ซกชนั ซ่งึ จะสร้างการอา้ งองิ ไปยังเซลล์ที่มีอยู่ B7:D7 C6:C8 ในทง้ั สองการอ้างอิง โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

123 2. ข้อผิดพลาดท่อี าจเกดิ ขึ้น ขอ้ ความ สาเหตุ วธิ ีแก้ปญั หา ##### จะเกดิ ขึ้นเมือ่ ตวั เลขในเซลล์ยาวกว่า แก้ไขไดโ้ ดยการขยายขนาด ขนาดของเซลล์ ความกวา้ งของเซลล์ #VALUE! จะเกดิ ขน้ึ เมื่อใช้สูตรผิดหลักไวยากรณข์ องสูตร แก้ไขไดโ้ ดยการสารวจดวู า่ ประเภทของข้อมลู เชน่ การนาตัวเลขไปบวกกับตวั อักษร ถกู ตอ้ งตามหลักคณิตศาสตรห์ รอื ไม่ #DIV/0! จะเกิดเมื่อใช้ 0 เป็นตวั หาร เช่น 10/0 ซึง่ ทาไมไ่ ด้ แก้ไขไดโ้ ดยการใชต้ วั เลขอืน่ ๆ เป็นตวั หาร แทน #NAME? จะเกดิ เม่อื ในสตู รมขี อ้ ความที่ Excel ไมส่ ามารถบอกได้ แก้ไขได้โดยการตรวจสอบสตู รดูว่ามขี ้อความ ว่าคืออะไร อะไร เช่น A1+A โดยที่ตวั อกั ษร A แปลกปลอมเข้าไปหรอื ไม่ ไม่ไดเ้ กี่ยวข้องในชที น้ัน #N/A จะเกดิ ขนึ้ เมอ่ื กรอกตัวแปรผิดประเภทให้กบั ฟงั ก์ชัน แก้ไขได้โดยการตรวจสอบวา่ ประเภทตวั แปร เช่น ฟังกช์ นั ต้องการเซลลเ์ ดียว แต่เราใส่เปน็ หลาย ๆ ของฟงั กช์ นั คอื อะไร แลว้ เปลย่ี นให้ถูกต้อง เซลล์ #REF! จะเกิดขนึ้ เม่อื โปรแกรมไม่สามารถคน้ หาตาแหนง่ อ้างอิง แก้ไขไดโ้ ดยการตรวจสอบตาแหนง่ อา้ งอิง เซลล์ที่ใช้ เซลล์ ในสูตรได้ มกั พบเม่ือหายไปอ้างเซลล์ขา้ มชีทหรือข้าม สมุดงาน #NULL! จะเกดิ ข้นึ เมื่อกาหนดพน้ื ทเ่ี ซลล์สองเซลล์ที่ไมไ่ ด้มสี ว่ นใด แก้ไขไดโ้ ดยการใสเ่ ครอ่ื งหมายคั่นให้ถกู ตอ้ ง ตอ่ กัน แต่ลมื แบ่งแยกดว้ ยเคร่อื งหมายคั่น (,)เช่น SUM(A1:B2,C2:D5) เขียนผิดเป็น SUM(A1:B2 C2:D5) โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

124 3. การสร้างสูตรคานวณ รปู แบบการคานวณด้วย Excel โปรแกรม Excel สรา้ งตารางคานวณได้หลายรูปแบบ การนาสูตรและฟงั ก์ชันมาใช้ การคานวณ อัตโนมัติ การสรา้ งสูตร การใช้ฟังก์ชัน หรือการแกป้ ัญหาในกรณที เ่ี กิดความผิดพลาดในการเขียนสูตร ถ้า เขา้ ใจหลกั การตา่ ง ๆ แลว้ Excel จะช่วยใหท้ างานได้สะดวกขน้ึ และการใชส้ ตู รไม่ยาก การคานวณอตั โนมัติ บางขณะท่ีป้อนตัวเลขในตาราง อาจจาป็นทีจ่ ะต้องตรวจสอบตวั เลขหรอื ดผู ลลัพธ์วา่ ถูกต้องหรอื ไม่ โดยไม่ต้องการใหแ้ สดงผลลพั ธ์ในตารางนัน้ ๆ สามารถใช้การคานวณอตั โนมัติ ซึง่ จะทาให้สามารถทราบ ผลลพั ธไ์ ดท้ นั ที โดยทีไ่ มต่ ้องปอ้ นสูตรหรอื ฟังกช์ นั ใด ๆ ด้วยการดูผลลพั ธท์ ่ี Status Bar (แถบสถานะ) ซึ่งมี วิธีการใช้ดังน้ี 1. แดรกเมาส์เลอื กชว่ งเซลล์ที่ตอ้ งการใหค้ านวณ 2. คลิกขวาที่ แถบสถานะ จะปรากฎกล่องแสดงคาสง่ั 3. เลอื กคาส่ังทต่ี ้องการ (ในท่นี ี่เลือกคาสั่ง ผลรวม) 4. จะปรากฏผลลพั ธ์ท่ี แถบสถานะ การคานวณโดยใช้สตู ร การใชส้ ูตรเป็นวธิ ที ่ีใช้ในการคานวณทีน่ ิยมมากทสี่ ุด เพราะทาให้ได้ผลลัพธ์ทร่ี วดเร็ว และสูตร บางอยา่ งยงั สามารถช่วยคานวณข้อมูลตัวเลขที่ซับซอ้ นได้อีก แต่ละสูตรมรี ูปแบบการใช้ท่แี ตกต่างกันออกไป โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

125 การใชง้ านสูตรรปู แบบปกติ (Formula) สูตรรปู แบบปกติจะเป็นสตู รท่ีเปน็ สมการทใ่ี ชด้ าเนินการกับข้อมลู ในชีทดว้ ยการ ใช้สัญลักษณท์ างคณิตศาสตร์ เชน่ เครอื่ งหมายบวก ลบ คณู หาร การอา้ งอิงเซลลอ์ ่ืน หรือสูตรทีใ่ ช้รวมข้อความ เปน็ ตน้ การใช้งานสตู รแบบฟงั กช์ นั (Function) เราสามารถใช้สูตรแบบฟังกช์ ันชว่ ยคานวณขอ้ มลู ทซ่ี ับซ้อนหรือมดปี ริมาณมากได้ ซง่ึ ฟงั กช์ ัน ใน Excel เปน็ สตู รท่กี าหนดไวล้ ว่ งหน้าและถูกสร้างให้เหมาะกบั งานเฉพาะอยา่ ง การใช้งานสูตรแบบอารเ์ รย์ (Array) การใช้สูตรแบบอารเ์ รยส์ ามารถทาหลาย ๆ การคานวณให้คนื คา่ เปน็ ผลลพั ธ์เดียวหรือหลายผลลัพธ์ โดยสูตรอาร์เรย์ตั้งแต่ 2 ชุดขน้ึ ไป สตู รแบบต่าง ๆ จะมคี วามยากงา่ ยแตกต่างกนั ออกไป ซงึ่ การสรา้ งสูตรน้ัน เราจะต้องทราบหลกั การ ทางานของเครอ่ื งหมายและสญั ลกั ษณ์ในสูตร รวมถงึ ลาดบั การคานวณ เพราะสงิ่ เหล่านมี้ ีผลต่อการแสดง ผลลพั ธ์ทงั้ สนิ้ โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

126 4. การใช้สูตรและการแกไ้ ขสูตร การคานวณ ใน Excel ทาได้ โดยการปอ้ นตัวเลขเข้าไปในช่อง เซลล์ แตล่ ะช่อง จากนน้ั จึงกาหนดให้ นาตวั เลขในแต่ละชอ่ ง มา บวก ลบ คณู หาร กัน ลองดตู ัวอยา่ ง และทาตามตอ่ ไปน้ี การบวกเลข 1. คลิกท่ี ชอ่ ง A1 2. พมิ พเ์ ลข 50 3. กด Enter เคอรเ์ ซอร์ จะเล่อื นมาที่ ชอ่ ง เซลล์ A2 4. คลกิ ทีช่ อ่ ง A2 และพิมพเ์ ลข 80 แล้วกด Enter 5. เคอรเ์ ซอร์ จะเลื่อนมาทชี่ อ่ ง A3 ใหพ้ มิ พ์ =A1+A2 6. เครอื่ งหมายเท่ากบั ข้างหนา้ เป็นการบอก Excel วา่ กาลังใช้สูตร ไมใ่ ช่เป็นการพมิ พ์ข้อความธรรมดา และจะสังเกตสีของ ขอ้ ความ A1 และ A2 แตกต่างไปจากสีธรรมดา และเม่ือขณะพมิ พ์ A1 จะมีกรอบ เกดิ ข้ึนทีช่ ่อง A1 ดว้ ย และขณะพิมพ์ A2 กจ็ ะมกี รอบเกดิ ท่ชี ่อง A2 แสดงขอบเขตท่ีถกู เลือก ดังน้ี 7. เมอ่ื พมิ พ์เสร็จแลว้ ใหก้ ดปมุ่ Enter กรอบจะเลอ่ื นไปยังตาแหนง่ A4 และจะได้ผลลพั ธ์เท่ากบั 130 ใน ช่อง A3 ดงั ภาพ โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

127 การลบ 1. คลกิ ที่ ช่อง C1 2. พมิ พเ์ ลข 60 3. กด Enter เคอรเ์ ซอร์ จะเล่ือนมาท่ี ชอ่ ง เซลล์ C2 4. คลกิ ทีช่ ่อง C2 และพิมพเ์ ลข 40 แล้วกด Enter 5. เคอรเ์ ซอร์ จะเลือ่ นมาที่ช่อง C3 ให้พิมพ์ =C1-C2 6. เม่อื พิมพ์เสรจ็ แลว้ ให้กดป่มุ Enter กรอบจะเล่ือนไปยงั ตาแหนง่ C4 และจะไดผ้ ลลพั ธเ์ ท่ากบั 20 ใน ช่อง C3 ดังภาพ การคูณ 1. คลิกท่ี ชอ่ ง E1 2. พิมพ์เลข 8 3. กด Enter เคอรเ์ ซอร์ จะเลอื่ นมาท่ี ช่อง เซลล์ E2 4. คลิกท่ีช่อง E2 และพมิ พ์เลข 9 แลว้ กด Enter 5. เคอร์เซอร์ จะเลอ่ื นมาท่ีช่อง E3 ให้พมิ พ์ =E1*E2 6. เมื่อพมิ พ์เสรจ็ แลว้ ให้กดป่มุ Enter กรอบจะเล่อื นไปยังตาแหนง่ E4 และจะไดผ้ ลลัพธ์เท่ากับ 72 ใน ชอ่ ง E3 ดังภาพ โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

128 การหาร 1. คลิกท่ี ชอ่ ง G1 2. พมิ พ์เลข 200 3. กด Enter เคอร์เซอร์ จะเลอ่ื นมาที่ ชอ่ ง เซลล์ G2 4. คลกิ ที่ช่อง G2 และพมิ พ์เลข 8 แล้วกด Enter 5. เคอร์เซอร์ จะเลอ่ื นมาท่ีช่อง G3 ให้พิมพ์ =G1/G2 6. เมื่อพมิ พเ์ สร็จแลว้ ใหก้ ดปุ่ม Enter กรอบจะเลอ่ื นไปยงั ตาแหน่ง G4 และจะได้ผลลัพธ์เท่ากบั 25 ใน ช่อง G3 ดงั ภาพ โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

129 การแก้ไขสูตร วธิ ีท่ี 1 แกไ้ ขบนแถบสูตร 1. ใชเ้ มาสค์ ลกิ เลอื กเซลล์สูตรทต่ี ้องการแกไ้ ข 2. ใชเ้ มาส์คลกิ แถบสตู รแลว้ ทาการแกไ้ ขสตู รให้ถูกต้อง วธิ ที ่ี 2 ใช้ฟงั ก์ชัน่ F2 ใชเ้ มาสค์ ลกิ เลอื กเซลล์สตู รที่ตอ้ งการแกไ้ ขกด ฟงั ก์ช่นั F2 ทาการแก้ไขสูตรให้ถกู ต้อง วธิ ที ี่ 3 ดับเบิ้ลคลิกในเซลลส์ ตู ร ใชเ้ มาสด์ ับเบิลคลกิ เซลลส์ ูตรทตี่ ้องการแกไ้ ข แล้วทาการแกไ้ ขสตู รให้ถูกต้อง โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

130 5. การคัดลอกสูตร 1. เลือกเซลล์ทม่ี สี ูตร 2. วางเมาสไ์ ว้ที่ด้านขวาล่างของเสน้ ขอบเซลล์ที่ไฮไลต์ และค่อยๆ เลอ่ื นเมาส์ลงมาจนกวา่ ตวั ช้ีจะเปลย่ี นเป็น สญั ลกั ษณร์ ูปกากบาท 3. ในขณะทีก่ ดเมาสค์ ้างไว้ ให้ลากเมาสล์ งมาหรือไปทางขวาใหค้ รอบคลมุ เซลล์ทง้ั หมดทค่ี ุณต้องการคัดลอก สูตร 4. เม่ือปล่อยปมุ่ เมาส์ สตู รจะถูกคัดลอกลงในเซลล์และปรบั เปล่ียนโดยอตั โนมัติ ใช้คาส่ังคดั ลอก 1. เลอื กเซลลสูตรต้นฉบับ 2. เลอื กคดั ลอก 3. เลือกตาแหนง่ วาง โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

131 6. การอา้ งองิ ตาแหนง่ เซลล์ การอา้ งองิ ตาแหน่งเซลลเ์ พื่อใช้ในการคานวณมีรปู แบบการอา้ งอิง 3 รูปแบบดังนี้ 1. การอ้างองิ เซลลแ์ บบสมั พัทธ์ การอ้างองิ เซลลแ์ บบสัมพัทธ์ (Relative Reference) เม่ือเซลล์ถกู คดั ลอกไปตาแหนง่ เซลล์อนื่ ตาแหน่ง เซลลจ์ ะถูกเปลย่ี นตามโดยอัตโนมตั ิ เช่น เซลล์ E4 ใส่สูตร =C4*D4 เมอ่ื คดั ลอกสูตรไปตาแหน่ง E5 สตู ร จะเปลยี่ นเปน็ =C5*D5 ให้โดยอตั โนมัติ ยกตัวอย่างเชน่ โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

132 2. การอา้ งองิ เซลล์แบบสมบูรณ์ การอ้างอิงเซลล์แบบสมบูรณ์ (Absolute Reference) เปน็ การการกาหนดค่าของตาแหน่งเซลล์ไม่ใหม้ ี การเปลยี่ นแปลง เช่น $D$4 หมายถึง เซลล์ D4 จะคงทไ่ี มเ่ ปลี่ยนแปลงไมว่ ่าจะคดั ลอกสตู รไปเซลลใ์ ดก็ ตาม จะมคี ่าเหมือนเดมิ ถ้าตอ้ งการใส่เครือ่ งหมาย $ ใหอ้ ัตโนมตั ใิ หค้ ลุมดาดาแลว้ กด F4 ยกตวั อยา่ งเช่น โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

133 3. การอา้ งอิงเซลลแ์ บบผสม การอา้ งอิงเซลล์แบบผสม (Mixed Reference) เปน็ การผสมระหวา่ งการอา้ งอิงแบบสมั พทั ธ์และ แบบสมบรู ณ์ จะใช้ในกรณีทต่ี อ้ งการให้ตาแหน่งเซลล์เปล่ยี นบ้างหรอื คงท่บี ้าง เช่น F$2 หมายถึง F จะ เปลี่ยนแปลงเม่ือมกี ารคดั ลอกสตู รไปคอลมั นอ์ ืน่ แต่ 2 จะคงทีไ่ มเ่ ปล่ียนแปลงตามหมายเลขบรรทดั ของแถวที่ ถกู คดั ลอก 7. การคานวณอ้างอิงตาแหนง่ เซลลข์ า้ มแผ่นงาน โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

134 8. ส่วนประกอบของฟังกช์ นั การใชฟ้ งั กช์ ัน (Function) คานวณค่าต่างๆ เปน็ การคานวณทมี่ ีความสะดวกรวดเรว็ เพยี งแค่ พมิ พฟ์ งั ก์ชัน และใสค่ ่า อาร์กิวเมนต์ (Argument) กส็ ามารถคานวณไดแ้ ล้วโปรแกรมตารางคานวณ (Microsoft Office Excel 2013) มีฟงั กช์ ันให้ใช้งานมากมาย แต่ละฟงั กช์ นั ใชง้ านแตกต่างฟังกช์ ันมสี ว่ นประกอบดงั น้ี=ช่ือฟังก์ชนั (คา่ Argument1, คา่ Argument2, ค่า Argument) เชน่ =SUM(A1+D2), =MAX(B2:B9), =AVERAGE(D4:F6) เป็นต้น 9. ประเภทของฟังกช์ นั การใชฟ้ งั ก์ชนั ในการคานวณคือการคานวณในรปู แบบทเี่ ปน็ ตัวช่วยในการคานวณของโปรแกรม Microsoft office Excel ของทกุ เวอร์ชัน โดยทกุ เวอรช์ ่นั ของ Microsoft office Excel จะมฟี งั กช์ นั เสริมเพื่อชว่ ยในความ สะดวกต่อการคานวณและใช้งานมากยงิ่ ข้ึน ซง่ึ ฟงั ก์ชั่นท่จี ะใช้เรียนในบทเรยี นน้ี ได้แก่ 1. SUM หมายถึง การหาผลรวม 2. MAX หมายถึง การหาคา่ สูงสุด 3. MIN หมายถงึ การหาคา่ ต่าสุด 4. AVERAGE หมายถึง การหาคา่ เฉล่ีย 5. IF หมายถงึ การหาข้อมูลในรูปแบบที่เปน็ เงื่อนไข 1. SUM ( ) sum หมายถึง การหาผลรวมในรูปแบบหลายจานวนและแต่ละจานวนจะมคี า่ มากหรอื นอ้ ยกข็ ึ้นอยู่กับ ขอ้ มลู เหลา่ นั้น โดยการคานวนหาผลรวมในรปู แบบฟังก์ชันน้ี จะเขียนสตู รได้ดงั นี้ =sum จากนนั้ ตามดว้ ยวงเล็บเปิด และจากนน้ั เลือกข้อมลู ท่จี ะคานวณแล้ววงเลบ็ ปดิ แล้วกด Enter ดงั ตวั อยา่ ง โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

135 2. MAX max คอื การหาผลลพั ธท์ ม่ี ีคา่ สงู สุดของข้อมูลเหล่านน้ั เมอ่ื ข้อมลู มีจานวนหลายชุด และแต่ละชุดจะมคี า่ ที่ ต่างกนั ออกไป และหากตอ้ งการทราบคา่ ทม่ี ากหรอื สงู ทสี่ ุดน้นั จึงจาเปน็ ต้องใช้ฟงั กช์ นั่ max เขา้ มาช่วยในการหา ผลลัพธ์ เช่น หากขอ้ มูลมีจานวน 5 ชุด ปรากฏดังน้ี 12 14 11 15 และ 9 ค่าท่ีมคี ่ามากหรือสงู ท่ีสุดกค็ ือ 15 แนน่ อนวา่ มองด้วยตาเปลา่ ก็สามารถตอบได้ แต่ถ้าหากขอ้ มลู มมี ากหลายชุดล่ะ กจ็ ะทาให้การมองด้วยตามกี าร ผิดพลาดได้ ฉะน้ัน ฟงั ก์ช่นั max สามารถแก้ปญั หาได้ และมีความแม่นยาและถกู ตอ้ งดว้ ย ดงั ภาพตัวอยา่ ง ตวั อย่างภาพการหาคา่ สูงสุดด้วยฟังกช์ นั่ max 3. MIN การหาคา่ min น้ันจะตรงขา้ มกบั ค่า max กลา่ วคอื min คือการหาผลลพั ธ์ท่มี ีคา่ ต่าสดุ ของข้อมลู เหลา่ นน้ั เมื่อขอ้ มลู มจี านวนหลายชุด และแต่ละชดุ จะมีค่าที่ต่างกนั ออกไป และหากตอ้ งการทราบค่าที่นอ้ ยหรือต่าทส่ี ุดนนั้ จึงจาเป็นตอ้ งใช้ฟังกช์ ่นั min เข้ามาช่วยในการหาผลลัพธ์ เช่น หากขอ้ มูลมีจานวน 5 ชุด ปรากฏดังน้ี โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

136 12 14 11 15 และ 9 ค่าท่มี ีค่านอ้ ยหรอื ต่าที่สุดก็คือ 9 แนน่ อนวา่ มองดว้ ยตาเปล่ากส็ ามารถตอบได้ แต่ถา้ หาก ขอ้ มูลมีมากหลายชดุ ล่ะ ก็จะทาใหก้ ารมองดว้ ยตามกี ารผดิ พลาดได้ ฉะน้นั ฟงั ก์ชน่ั min สามารถแกป้ ญั หาได้ และ มีความแมน่ ยาและถกู ตอ้ งด้วย ดังภาพตวั อยา่ ง ตัวอยา่ งภาพการหาคา่ สูงสุดด้วยฟังกช์ ่นั min 4. AVERAGE average คือการหาผลลพั ธ์ในรปู แบบของค่าเฉลีย่ โดยผลลพั ธ์ทอ่ี อกมานั้นจะอยใู่ นรปู แบบเลขทศนยมิ หรอื ไมใ่ ช่เลขทศนิยมกไ็ ด้ ข้นึ อยกู่ บั ข้อมลู ทีจ่ ะนามาคานวณ โดยวิธีคิดวา่ เฉลีย่ ก็คอื หากมขี ้อมูลมา 5 ชุด ไดแ้ ก่ 14 17 11 15 และ 12 ให้นาขอ้ มูลทั้งหมดน้มี าบวกกนั เมื่อไดผ้ ลลพั ธจ์ ากการทน่ี ามารวมกันแล้วจากน้ันจงึ หาร ดว้ ย 5 น้นั คือจานวนชดุ ของขอ้ มลู ซ่ึงในรูปแบบค่าเฉล่ียของฟังกช์ ั่นจะเขยี นได้ดังน้ี =average ตามด้วยวงเล็บเปิด แลว้ เลือกขอ้ มูลทง้ั หมดท่ีตอ้ งการจะหาผลลพั ธจ์ ากน้นั ก็ใส่วงเล็บปดิ และ Enter กจ็ ะได้ผลลัพธ์ดังภาพตัวอยา่ ง โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

137 5. IF if คอื การหาผลลพั ธ์ในรูปแบบของการต้ังเง่อื นไข เป็นการต้ังเงือ่ นไขในรปู แบบเกณฑต์ ามที่เราไดก้ าหนดไว้ หรอื ตามทโี่ จทย์กาหนดไว้ โดยเงอื่ นไขนน้ั จะมอี ยหู่ ลายอย่าง ขึ้นอยู่กบั โจทยท์ จ่ี ะได้มา ซึ่งในที่น้ี เราจะเรยี นรู้เกียว เร่อื ง IF และ IF ซ้อน IF แตก่ อ่ นทเี่ ราจะเรยี นเก่ยี วกบั เรื่อง IF เรามาทาความรู้จกั กับเครอื่ งหมายที่ใช่ร่วมกบั IF กนั กอ่ น 1. > หมายถงึ มากกวา่ 2. < หมายถึง นอ้ ยกว่า 3. >= หมายถึง มากกวา่ หรอื เท่ากับ 4. <= หมายถึง นอ้ ยกวา่ หรอื เทา่ กบั 5. = หมายถึง เท่ากับ 6. < > หมายถงึ ไมเ่ ทา่ กับ 7. \" หมายถงึ quotation marks หรอื ฟนั หนู 8. , หมายถงึ comma หรอื จลุ ภาค หรือ จุดลูกนา้ 5.1 IF if คือการหาผลลัพธ์แบบเงื่อนไข โดยลกั ษณะการเขียนสตู รของฟังก์ชน่ั if ในทีน่ ี้ จะเขียนไดว้ า่ =if ตามดว้ ยวงเลบ็ เปิด จากนัน้ กท็ าการใสข่ อ้ มูลและเงื่อนไขท่ีตอ้ งการจะใชแ้ ล้วจึงใสว่ งเล็บปิดและกด Enter ตัวอย่างเช่น หากมีการสอบเก็บคะแนนโดยมคี ะแนนเตม็ คือ 20 และเกณฑก์ ารสอบผ่านคอื 15 หากไม่ถงึ 15 คือไม่ผา่ น จะเขยี นสูตรฟงั ก์ช่นั ได้ดงั น้ี =if(ช่องคะแนนทไี่ ด้>=15,\"ผา่ น\",\"ไม่ผา่ น\") ดังภาพตัวอยา่ ง โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

138 จากภาพการคานวณด้วยฟงั ก์ชนั เงือ่ นไข if สตู รฟงั ก์ชั่นทเ่ี ขียนมา ให้ความหมายภายในวงเล็บไดว้ า่ c3 คือ ช่องคะแนนทน่ี ักเรียนสอบได้ ตอ้ งมากกวา่ หรือเท่ากบั >=15 จงึ จะใหแ้ สดงผลว่าผา่ น \"ผ่าน\" สว่ นหากได้คะแนนตา่ กว่า 15 ให้แสดงผลว่าไมผ่ ่าน ซง่ึ ในการท่จี ะปอ้ นคาส่งั แต่ละคาส่งั จะตอ้ งมเี ครอ่ื งหมาย , (comma) เพื่อเป็นการ บอกใหร้ ู้ว่า เปน็ คาสั่งคนละส่วนกัน โดยตารางคะแนนนคี้ นท่ี 1 ไดค้ ะแนน คอื 18 นัน่ หมายความว่า ผา่ น ทา ให้ผลการคานวณจึงรันไปหาเงือ่ นไขใหต้ รงกบั ข้อมูล จงึ ทาใหผ้ ลลัพธ์ออกมาคอื ผ่าน คนท่ี 3 ได้คะแนนต่ากวา่ 15 ผลการคานวณจงึ รันไปหาเง่อื นไขทต่ี รงกบั ขอ้ มูลจึงทาใหไ้ มผ่ า่ น 5.2 IF ซอ้ น IF if คอื การหาผลลัพธแ์ บบเงือ่ นไขท่ีมมี ากกวา่ 1 เง่อื นไข หรือเรียกว่า if ซ้อน if โดยลักษณะการ เขยี นสตู รของฟงั ก์ชั่น if ในท่นี ้ี จะเขยี นได้ว่า =if ตามด้วยวงเลบ็ เปิด จากน้นั กท็ าการใสข่ ้อมูลเพอื่ วางเงื่อนไขและ หากเง่ือนไขมกี ารซอ้ นกนั มากกวา่ 1 เงื่อนไข จึงจาเปน็ ตอ้ งมี , (comma) เพื่อขนั้ ระว่างแตล่ ะเงอ่ื นไขและเพิ่มวงเลบ็ ตามเงอื่ นไขทีเ่ พ่ิมมา และวงเล็บปิดก็จะตอ้ งมีเท่ากับวงเล็บเปิด ดงั ตวั อยา่ งเช่น ตารางการตดั เกรด ซึ่งการให้เกรด แตล่ ะตวั จะมเี กณฑก์ าหนดไว้ คือ - ถ้าไดค้ ะแนน 80 ขน้ึ ไป ไดเ้ กรด 4 - ถ้าได้คะแนน 70 ขึ้นไป ได้เกรด 3 - ถา้ ได้คะแนน 60 ขึน้ ไป ไดเ้ กรด 2 - ถา้ ได้คะแนน 50 ขน้ึ ไป ได้เกรด 1 - ถา้ ไดค้ ะแนนน้อยกวา่ 49 ไดเ้ กรด 0 เม่อื มีเกณฑ์กาหนดหรอื มเี ง่อื นไขมาให้ เราก็สามารถใชส้ ูตรฟงั กช์ ่ัน if ซอ้ น if ได้ เพราะมเี งือ่ นไขมากกวา่ 1 เง่อื นไข ดงั ภาพตวั อย่าง โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

139 จากภาพตารางการตัดเกรดโดยใชฟ้ ังกช์ ั่น if ซ้อน if จะเหน็ ได้วา่ มวี งเล็บทั้งหมด 4 วงเลบ็ เขา้ ดว้ ยกนั ซง่ึ วงเล็บท่ี 1 เขียนไดว้ ่า c11>=80,\"4\" หมายถงึ ถ้าขอ้ มูลของ c11 มคี า่ มากกวา่ หรอื เท่ากับ 80 ได้เกรด 4 วงเลบ็ ท่ี 2 เขียนไดว้ า่ c11>=70,\"3\" หมายถึง ถ้าข้อมูลของ c11 มคี า่ มากกว่าหรอื เท่ากับ 70 ได้เกรด 3 วงเล็บท่ี 3 เขยี นไดว้ า่ c11>=60,\"2\" หมายถงึ ถ้าขอ้ มูลของ c11 มีคา่ มากกว่าหรือเท่ากบั 60 ได้เกรด 2 วงเล็บท่ี 4 เขยี นได้ว่า c11>=50,\"3\" หมายถึง ถ้าข้อมลู ของ c11 มีคา่ มากกวา่ หรือเท่ากบั 50 ได้เกรด 1 และขอ้ มูลสุดทา้ ย คือ หากน้อยวา่ 49 ไดเ้ กรด 0 จะอยู่รวมกบั วงเล็บสุดท้าย โดยไม่มคี าสง่ั ใดๆกากับไว้ แตจ่ ะมี เครื่องหมาย , (comma) ขน้ั ไวเ้ พือ่ บอกให้รูว้ ่า หากมคี าสัง่ ทน่ี อกเหนือจากขอ้ มูลที่กล่าวมาก่อนหน้าน้ี ให้แสดงผล เป็น 0 ดังภาพตัวอย่างต่อมา ฟงั กช์ ่นั รวมFunction ในการคานวณของ Excel AVERAGE COUNT ประโยชน์ DATE ใชห้ าค่าเฉลีย่ ของกลุม่ ขอ้ มูลท่เี ปน็ ตวั เลข IF ใช้ในการนับจานวนทร่ี ะบุ LARGE ชว่ ยกรอกข้อมูลทเ่ี ป็นวนั ทแี่ ละสามารถนาไปคานวณระยะเวลา MAX ใชท้ ดสอบเง่ือนไข MIN หาคา่ ตัวเลขสงู สดุ ในกลุม่ ของข้อมลู โดยระบุชว่ งที่ตอ้ งการ ROUND หาคา่ ที่สูงสุดในกลมุ่ ของตวั เลข หาคา่ ท่ตี ่าสดุ ในกลุ่มของตัวเลข ปัดเศษจดุ ทศนยิ มตามหลกั คณิตศาสตร์ โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

140 SUM หาค่าผลรวมของตัวเลข VLOOKUP ช่วยคน้ หาและแสดงขอ้ มลู จากตารางทก่ี าหนด DATE ช่วยเกบ็ ขอ้ มลู วันท่ี และจะเปลีย่ นใหเ้ ป็นเลขจานวนหนง่ึ เพ่อื นาไปใช้ คานวณหาระยะเวลา DATEVALUE เปล่ยี นข้อมูล เดือน วนั ที่ ปี ทเ่ี ขียนเปน็ ตัวอักษรใหเ้ ป็นเลขจานวนหนงึ่ เพ่อื นาไปใช้คานวณหาระยะเวลา DAYS 365 คานวณระยะเวลาเป็นจานวนวน โดยถือตามหลักของปที างธรุกจิ คือ 1 ปี มี 365 วัน NOW ใหผ้ ลเป็นวนั ทแ่ี ละเวลาที่มอี ยู่ในเครือ่ งคอมพิวเตอร์ โดยรวมกนั ออกมา เป็นเลขจานวนหน่งึ TIME ใหผ้ ลเปน็ ตัวเลขจานวนหนึ่งจากข้อมลู ชั่วโมง นาที และวินาที ที่ กาหนดให้ TIME VALUE ให้ผลเปน็ ตวั เลขท่ีจานวนหน่ึงจากขอ้ มลู ชัว่ โมง นาที และวนิ าที ซึ่งเขยี น TODAY ในเชิงตัวอักษร DAY ใหว้ นั ท่เี ปน็ ตวั เลขจานวนหน่งึ ตามท่ีไดม้ าจากเครื่องคอมพวิ เตอร์ HOUR แยกขอ้ มูลส่วนท่ีเปน็ วันทอ่ี อกมาจากขอ้ มลู วันท่ี, เดือน, ปี MINUTE บอกเวลาเฉพาะส่วนทีเ่ ป็นช่ัวโมง MONTH บอกเวลาเฉพาะสว่ นทเี่ ปน็ นาที SECOND : แยกขอ้ มูลส่วนทีเ่ ปน็ วันทอ่ี อกมาจากขอ้ มูล วันท่ี, เดอื น, ปี ผลออกมา WEEKDAY จะไดต้ วั เลขที่นาไปคานวณไดด้ ว้ ย YEAR แยกขอ้ มูลส่วนท่เี ปน็ วนิ าทอี อกมา จากเวลาทก่ี าหนด ABS ให้ผลลพั ธ์ว่าเปน็ วนั อะไร จากขอ้ มูลวนั ที่ เดอื น ปี SCOS ใหผ้ ลลัพธ์วา่ ตรงกับปี ค.ศ. อะไร จากข้อมลู วนั ท่ี เดอื น ปี หรอื ตวั เลข ACOSH จานวนหนึง่ ASIN หาค่าสมั บรู ณ์ของตัวเลข (Absolute Value) ตัวเลขทไ่ี มม่ ีการตดิ ลบ ASINH หาคา่ Arc Cosine คือ สว่ นกลับของคา่ Cosine ตามหลกั ของตรโี กณมติ ิ ATAN หาคา่ สว่ นกลบั ของ Hyperbolic Cosine ATAN2 หาค่า Arc Sine ATANH หาค่าสว่ นกลับของ Hyperbolic Sine หาคา่ Arc Tangent หาค่าความลาดเอยี ง (Slope) ของเสน้ ตรงทล่ี ากมาจากจุดพกิ ดั (แบบ รูป กราฟ) มายังจุดกาเนิด (Origin) คือ จดุ ตัดเสน้ แกน X,Y หาคา่ สว่ นกลบั ของ Hyperbolic Tangent โปรแกรมตารางงาน : ครูจฬุ าลกั ษณ์ ถาไชยลา

141 BASE แปลงหาค่าตวั เลขฐานสิบให้เปน็ ฐานอ่นื ทตี่ ้องการ ปัดคา่ ใหเ้ ปน็ เลขจานวนเตม็ ซ่งึ มคี ่าใกลเ้ คยี งกบั เลขที่กาหนดใหม้ ากท่ีสุด CEILING โดยปดั ค่าตัวเลขเปน็ บวกขนึ้ และปัดลงหากเป็นลบ คานวณหาจานวนวิธกี ารจดั กล่มุ (Combination) ตามหลกั คณิตศาสตร์ COMBIN หาค่า Cosine ตามหลักของตรีโกณมิติ COS หาค่า Hyperbolic Cosine COSH ปัดค่าใหเ้ ปน็ เลขจานวนเต็มรปู แบบเลขคู่ ซึง่ มีค่าใกลเ้ คียงกบั เลขท่ี กาหนดใหม้ ากที่สุด โดยปดั ข้นึ สาหรบั ค่าบวกและปัดลงสาหรบั คา่ ลบ EVEN หาค่า Exponential หาค่า Factorial EXP หาค่า Factorial แตเ่ ปน็ การลดค่าของตัวคูณลงทีละ 2 FACT ปัดค่าใหเ้ ป็นเลขจานวนเต็มรปู ซึง่ มีคา่ ใกล้เคียงกับเลขที่กาหนดให้มากทีส่ ุด FACTDOUBLE โดยปดั ข้นึ สาหรบั ค่าลบและปัดลงสาหรบั ค่าบวก หาคา่ ตัวคณู ร่วมน้อย (ค.ร.น.) ของกล่มุ เลขจานวนหนึ่ง FLOOR ปัดเศษทศนยิ มท้งิ เหลือไว้แตเ่ ลขหน้าจดุ ทศนยิ มเท่านนั้ หาคา่ ตวั คณู ร่วมมาก(ค.ร.ม.) ของกลุม่ เลขจานวนหนึ่ง GCD หาค่าของ Natural Logarithm จากตัวเลขที่กาหนดให้ INT หาคา่ Logarithm ของตวั เลขจากฐาน (Base) ท่ีกาหนดให้ LCM หาค่า Logarithm ของตัวเลขจากฐาน 10 LN หาคา่ Determinant ของ Matrix ซง่ึ มจี านวนแถวและจานวนคอลมั น์ LOG เทา่ กัน LOG10 หาคา่ Inverse ของ Matrix ซงึ่ มีจานวนแถวและจานวนคอลมั น์เทา่ กนั หาผลคณู ของ Matrix สองชุด MDETERM หาค่า Modulo คือ เศษท่ไี ด้จากการหารของตวั เลขสองจานวน และจะ ให้เครอ่ื งหมายเดยี วกนั ของตัวหาร MNVERSE หาอตั ราสว่ นระหวา่ ง ผลบวก Factorial กับผลคูณ Factorial ของ MMULT จานวนหนง่ึ ท่กี าหนดให้ ปดั ค่าใหเ้ ป็นเลขจานวนเต็มแบบเลขคี่ ซง่ึ มคี ่าใกล้เคยี งกับเลขที่กาหนดให้ MOD มากท่สี ดุ โดยปัดขึ้นสาหรับค่าบวกและปัดลงสาหรับคา่ ลบ ให้ค่าคงท่ีของ P1 คือ 3.141592 (โดยประมาณ) MULTINOMIAL หาผลคณู ของตัวเลขจานวนหนึ่งทีก่ าหนดให้ หาคา่ ท่ไี ด้จากการหารของตัวเลขสองจานวน โดยปดั เศษทงิ้ ทัง้ หมด ODD ใหค้ า่ ตวั เลขแบบสมุ่ ๆ ซึง่ จะมีคา่ ระหว่าง 0-0.9999 PI PRODUCT QUOTIENT RAND โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา

142 RANDBETWEEN ใหค้ า่ ตัวเลขแบบส่มุ ๆ โดยสามารถกาหนดชอ่ งของตัวเลขทตี่ ้องการ ปัดเศษทศนยิ มให้มีจานวนหลกั เท่าทีต่ อ้ งการ โดยพจิ ารณาตามหลัง ROUND คณิตศาสตร์ ถ้าเกิน 5 ปัดขนึ้ ตา่ กว่า 5 ปัดทิง้ SERIESSUM หาคา่ ผลรวมของเลขอนุกรมชุดหนงึ่ แทน ความหมายของคา่ ตัวเลขทกี่ าหนดให้ด้วย 1 หรือ 0 ดังนี*้ ถา้ ตวั เลข SIGN ท่กี าหนดให้มีคา่ เปน็ บวก จะไดเ้ ลข 1*ถา้ ตวั เลขท่ีกาหนดใหม้ คี า่ เป็นศูนย์ จะได้เลข 0*ถา้ ตวั เลขท่ีกาหนดให้มีค่าติดลบ จะได้เลข –1 SIN หาคา่ Sine ตามหลักของตรีโกณมิติ SINH หาค่า Hyperbolic Sine SQRT หาค่ารากที่สองของตวั เลขที่กาหนดให้ SUM หาคา่ ผลรวมของตัวเลขจานวนหนงึ่ SUMPRODUCT หาค่าผลรวมระหว่างผลคูณของตวั เลขหลายกล่มุ SUMSQ หาค่าผลรวมของตัวเลขยกกาลัง SUMX2MY2 หาคา่ ผลรวมของตวั เลขยกกาลังสอง 2 จานวนลบกัน SUMX2PY2 หาค่าผลรวมของตัวเลขยกกาลังสอง 2 จานวนบวกกนั SUMXMY2 หาค่าผลรวมของผลตา่ งยกกาลงั สอง 2 จานวน TAN หาค่า Tangent ตามหลักตรโี กณมติ ิ TANH หาคา่ Hyperbolic Tangent TRUNC ปัดเศษทศนิยมใหม้ ีจานวนหลักเท่าที่ตอ้ งการ โดยไมม่ กี ารพิจารณาใด BAHTTEXT แปลงตัวเลขใหเ้ ปน็ ขอ้ ความ ********************* โปรแกรมตารางงาน : ครูจุฬาลกั ษณ์ ถาไชยลา