บทที่ 6 การประยุกต์ใช้โปรแกรมตารางงาน

การประยกุ ตใ์ ช้โปรแกรม Microsoft Excel 2016 สามารถใช้ได้กบั งานหลายประเภท การศึกษาการใช้ สูตรและฟังก์ชนั ให้มีความเข้าใจตัง้ แต่ขนั้ พื้นฐานจึงมีความจาเป็ น การสร้างงานด้วยเคร่ืองมือ คาสงั่ สูตรและฟังก์ชันจะช่วยให้เกิดทักษะจนสามารถพฒั นาไปประยุกต์ใช้กบั งานต่างๆ ได้อย่างมีประสิทธิภาพ และมีประโยชน์

การใช้สูตรและฟังก์ชันของโปรแกรม จะต้องมีความเข้าใจพ้ืนฐานของการใช้งานสูตรและฟังก์ชัน โดยโปรแกรมจดั หมวดหมเู่ พ่อื ใหม้ คี วามเขา้ ใจและใชง้ านไดง้ า่ ยดงั น้ี โปรแกรม Excel แบง่ ชนิดของสตู รออกเป็น 4 ประเภทคอื 1. สตู รในการคานวณทางคณิตศาสตร์ (Arithmetic Formula)

2. สตู รในการเปรียบเทียบ (Comparision Formula) 3. เคร่ืองหมายในการเชื่อมขอ้ ความสองข้อความหรือมากกวา่ นัน้ (Text Formula)

4. สตู รในการอ้างอิง (Text Formula) ลาดบั ขอ้ มลู ในสตู ร

หมายเหตุ : ในกรณีท่สี ูตรมเี คร่อื งหมายมากกว่าหน่ึงการคานวณจะอาศยั การเรยี งลาดบั ในการคานวณ ก่อนหลงั ดงั ต่อไปน้ี เอกซ์โพเนนเชยี ลการคณู และการหาร การบวกและการลบแต่ถา้ ต้องการเปล่ยี นลาดบั การ คานวณใหใ้ สว่ งเลบ็ ครอบสว่ นทต่ี อ้ งการคานวณกอ่ นเชน่ » 2*2+2-2 มคี า่ เทา่ กบั 4 » 2*(2+2)-2 มคี า่ เทา่ กบั 6 เพราะต้องเอา 2 บวกกบั 2 ก่อนซ่งึ ไดค้ ่าเท่ากบั 4 แล้วจงึ นาค่า 4 ไปคูณกบั 2 จะได้ 8 แลว้ จงึ นา 2 ไปลบออกจะไดค้ า่ 6

เราสามารถคานวณค่าตวั เลขโดยวธิ ีการสร้างสูตรทาได้โดยการเลือกเซลล์ท่ตี ้องการให้ผลลพั ธ์ ปราก ในเซลลน์ ัน้ แลว้ ป้อนเคร่อื งหมายเทา่ กบั (=) แล้วตามดว้ ยตวั เลขทใ่ี ชใ้ นการคานวณหรอื ตาแหน่งของเซลล์ทเ่ี กบ็ คา่ ของขอ้ มลู ทเ่ี ป็นตวั เลขทใ่ี ชใ้ นการคานวณและเคร่อื งหมายทางคณติ ศาสตรเ์ ชน่

การคานวณใน Microsoft Excel 2016 มขี นั้ ตอนดงั น้ี 1. พมิ พเ์ ครอ่ื งหมาย = 2. อา้ งองิ เซลล์ 3. พมิ พเ์ คร่อื งหมายในการคานวณ



ฟังกช์ นั ในการคานวณทใ่ี ชง้ านสามารถแบง่ ประเภทไดด้ งั น้ี 1. ฟังกช์ นั ทางคณิตศาสตร์ 2. ฟังกช์ นั ทางตรรกศาสตร์ 3. ฟังกช์ นั ทเ่ี กย่ี วกบั วนั ท่ี 4. ฟังกช์ นั ทเ่ี กย่ี วกบั เวลา 5. ฟังกช์ นั ทเ่ี กย่ี วกบั การเงนิ 6. ฟังกช์ นั ทเ่ี กย่ี วกบั ตวั อกั ษร 7. ฟังกช์ นั ทางสถติ ิ 8. ฟังกช์ นั ในการคน้ หาขอ้ มลู 9. ฟังกช์ นั ทางดา้ นวศิ วกรรม 10. ฟังกช์ นั ในการจดั การฐานขอ้ มลู

ฟังกช์ นั ทใ่ี ชง้ านบอ่ ย ในโปรแกรม Microsoft Excel 2016 จะมฟี ังกช์ นั มากมายและสามารถนาไปประยกุ ตใ์ ชง้ านในดา้ นตา่ งๆ รวมทงั้ งานทส่ี ลบั ซบั ซอ้ นไดเ้ ป็นอยา่ งดี การใชง้ านจะทาไดอ้ ยา่ งสะดวกและรวดเรว็ ในทน่ี ้ีจะอธบิ ายเฉพาะฟังกช์ นั ทใ่ี ชง้ านบอ่ ยๆ ดงั น้ี 1. SUM (การหาผลรวมของข้อมูล) รปู แบบ » =SUM(Number 1,Number 2,…) Number เป็นกลุม่ ของขอ้ มลู ทต่ี อ้ งการหาผลรวมซง่ึ สามารถจะใสไ่ ดม้ ากกวา่ 1 กล่มุ โดยใชเ้ ครอ่ื งหมาย, คนั่ กลางระหวา่ งแต่ละกลมุ่ ขอ้ มลู เชน่ ใหห้ าผลรวมตงั้ แตเ่ ซลล์ A1 ถงึ เซลล์ A10 » =SUM(A1, A2, ....., A10) แต่ถา้ ขอ้ มลู อยตู่ ดิ กนั จะนยิ มใช้ » =SUM(A1:A10)

2. SUMIF (การหาผลรวมแบบมีเง่อื นไข) รปู แบบ » =SUMIF(Range,Criteria,Sum_range) Range ขอบเขตของขอ้ มลู ทต่ี อ้ งการตรวจสอบตามเงอ่ื นไข Criteria เง่อื นไขทก่ี าหนดใหค้ านวณหาผลรวม Sum_range ชว่ งของเซลลท์ ต่ี รวจสอบตามเงอ่ื นไขเพอ่ื นามาคานวณ เชน่ ใหห้ าผลรวมเฉพาะพนกั งาน บญั ชโี ดยเซลลท์ อ่ี า้ งคอื เซลล์ B1 ถงึ เซลล์ B12 และใหอ้ า้ งเซลลร์ าคาของเสอ้ื สฟี ้าทเ่ี ซลล์ C1 ถงึ เซลล์ C12 » =SUMIF(B1:B12,“พนักงานบญั ช”ี ,C1:C12) 3. MIN (การหาค่าตา่ สดุ ของจานวน) รปู แบบ » =MIN(Number1,Number2,…) Number เป็นกล่มุ ของขอ้ มลู ทต่ี อ้ งการหาคา่ ต่าสดุ เชน่ ใหห้ าคา่ ต่าสดุ ของเซลล์ B3 ถงึ เซลล์ B12 » =MIN(B3:B12)

5. AVERAGE (การหาค่าเฉลี่ยของข้อมลู ) รปู แบบ » =AVERAGE(Number1,Number2,..) Number เป็นกลมุ่ ของขอ้ มลู ทต่ี อ้ งการหาคา่ เฉลย่ี เชน่ ใหห้ าคา่ เฉลย่ี ของเซลล์ F1 ถงึ เซลล์ F8 » =AVERAGE(F1:F8) 6. COUNT (การนับจานวนขอ้ มลู ท่ีเป็นเฉพาะตวั เลข) รปู แบบ » =COUNT(Value1,Value2,…) Value ชว่ งของกลุม่ เซลลท์ น่ี ามาใชใ้ นการนบั จานวนเฉพาะตวั เลข เชน่ ใหน้ ับจานวนผเู้ รยี นทม่ี คี ะแนน วา่ มกี ค่ี นจะไมน่ บั นกั เรยี นท่ี ขส. หรอื มส. โดยอา้ งทเ่ี ซลล์ C2 ถงึ เซลล์ C10 » =COUNT(C2:C10)

7. COUNTA (การนับจานวนข้อมลู ที่เป็นทงั้ ขอ้ ความและตวั เลขปนกนั ) รปู แบบ » =COUNTA(Value1,Value2,…) Value ชว่ งของกลมุ่ เซลลท์ น่ี ามาใชใ้ นการนบั จานวน เชน่ ใหน้ ับจานวนผเู้ รยี นทม่ี ีคะแนนว่ามกี ่คี นรวม ผเู้ รยี นท่ี ขส. หรอื มส. โดยอา้ งทเ่ี ซลล์ C2 ถงึ เซลล์ C10 » =COUNTA(C2:C10) 8. COUNTIF (การนับจานวนขอ้ มูลแบบมีเง่ือนไข) รปู แบบ » =COUNTIF(Range,Criteria) Range ชว่ งของเซลลท์ ต่ี อ้ งการนบั ตามเง่อื นไข Criteria เง่ือนไขท่ีใช้ตรวจสอบและนับจานวนของเซลล์ตามเง่ือนไข เช่น ให้นับเฉพาะผู้เรียน ทส่ี อบผา่ นโดยอา้ งองิ ทเ่ี ซลล์ D2 ถงึ เซลล์ D10 » =COUNTIF(D2:D10,“ผา่ น”)

9. IF (การหาค่าจริงหรือเทจ็ จากเงอ่ื นไขท่ีระบุ) รปู แบบ » =IF(Logical,Value_if_true,Value_if_false) Logical เง่อื นไขทใ่ี ชใ้ นการเปรยี บเทยี บหรอื ตรวจสอบขอ้ มลู Value_if_true คา่ ของเงอ่ื นไขทถ่ี กู ตอ้ ง (จรงิ ) Value_if_false คา่ ของเงอ่ื นไขทไ่ี มถ่ ูกตอ้ ง (เทจ็ ) เชน่ ถา้ ทเ่ี ซลล์ B2 มากกวา่ หรอื เทา่ กบั 60 ใหแ้ สดง ผา่ นถา้ ทเ่ี ซลล์ B2 น้อยกวา่ 60 ใหแ้ สดงไมผ่ า่ น » =IF(B2>=60,“ผา่ น”,“ไมผ่ า่ น”) 10. NOW (การหาวนั ที่และเวลาปัจจุบนั ) รปู แบบ » =NOW() เช่น =NOW() ผลลพั ธค์ อื 15/4/2013 14:24

11. TODAY (การหาวนั ที่ปัจจบุ นั ) รปู แบบ » =TODAY() เช่น =Today() ผลลพั ธค์ อื 15/4/2013 12. VLOOKUP (การค้นหาและแสดงข้อมลู ) รปู แบบ » =VLOOKUP(Lookup_value,Table_array,Col_index_num, Range_lookup) Lookup_value คา่ ทใ่ี ชใ้ นการคน้ หา Table_array ตารางขอ้ มลู ทใ่ี ชส้ าหรบั แสดงผลและคน้ หาขอ้ มลู Col_index_num คอลมั น์ทใ่ี หแ้ สดงขอ้ มูลออกมาโดยคอลมั น์แรกมคี า่ เป็น 1 และคอลมั น์ต่อไปจะเป็น 2,3,... ตามลาดบั Range_lookup คา่ ทางตรรกะทก่ี าหนดในการคน้ หามี 2 รปู แบบ False ใชค้ น้ หาคา่ ทต่ี รงกบั คา่ ทใ่ี ชใ้ นการคน้ หา True ใชค้ น้ หาคา่ ทม่ี คี า่ น้อยกวา่ หรอื เทา่ กบั คา่ ทใ่ี ชใ้ นการคน้ หา เชน่ ทเ่ี ซลล์ E4 ถงึ E10 คอื ตอ้ งการให้ แสดงรายชอ่ื หนงั สอื โดยอตั โนมตั เิ มอ่ื ไดม้ กี ารป้อนรหสั หนังสอื ลงไปในเซลล์ D4 ถงึ D10

ในการคานวณในโปรแกรม Microsoft Excel 2016 จะตอ้ งมกี ารคดั ลอกสตู รหรอื ฟังกช์ นั โดยการอา้ งองิ เซลล์ ซง่ึ จะมวี ธิ กี ารอ้างองิ เซลล์ 2 แบบ คอื การอา้ งองิ แบบสมั พทั ธ์ (Relative reference)และการอา้ งองิ แบบสมั บูรณ์ (Absolute reference) โดยมวี ธิ กี ารป บิ ตั ดิ งั น้ี การอา้ งองิ แบบสมั พทั ธ์ (Relative reference) เป็นการคดั ลอกสตู รทเ่ี กดิ ขน้ึ ใหม่ตามตาแหน่งของเซลล์โดยอตั โนมตั ผิ ลลพั ธท์ ไ่ี ดจ้ ะเปลย่ี นไปตามตาแหน่ง ของแถวและคอลมั น์ เชน่ เม่อื ผใู้ ชม้ กี ารใชส้ ตู รในการคานวณแบบสมั พทั ธโ์ ดยท่ี D3 ใหท้ าการใสส่ ตู รคอื =B2*C2 เมอ่ื ทาการคดั ลอกสตู รน้ีไปท่ี D2 สตู รกจ็ ะเปลย่ี นเป็น =B3*C3 ใหโ้ ดยอตั โนมตั กิ ารคดั ลอกสูตรนัน้ สามารถทาได้ ทงั้ ตามแนวตงั้ (Column) และตามแนวนอน (Row)

การอา้ งองิ แบบสมั บรู ณ์ (Absolute reference) เป็นการอ้างอิงถึงเซลล์ใดเซลล์หน่ึงเป็นหลักหรือกลุ่มใดกลุ่มหน่ึงโดยเฉพาะซ่ึงจะใช้เคร่ืองหมาย ($) ในการกาหนด โดยจะใส่นาหน้าตวั อกั ษรกากบั คอลัมน์หรอื เลขกากบั แถวการอ้างอิงแบบน้ีจะเป็นการอ้างอิงเซลล์เดมิ ไมว่ า่ จะยา้ ยการทางานไปทเ่ี ซลลใ์ ดกต็ ามเชน่ =$D$2 (หากตอ้ งการใสเ่ ครอ่ื งหมาย $ ใหอ้ ตั โนมตั ใิ หก้ ดป่มุ F4) ทเ่ี ซลล์ D2 ใส่สตู ร =B2*C2 จากนัน้ ใหก้ ดป่มุ ฟังชนั ก์ F4 จะเป็นการตรงึ ค่าเซลล์ C2 จากนัน้ ใหท้ าการคดั ลอกสตู ร เซลล์ B2 จะเปลย่ี นแปลงไปเป็น B3 และ B4 สว่ นเซลล์ C2 จะคงทไ่ี มไ่ ดเ้ ปลย่ี นแปลงไปเม่อื ทาการคดั ลอกสตู ร

หมายเหตุ : » กด F4 จานวน 1 ครงั้ จะไดผ้ ลดงั น้ี =$C$2 » กด F4 จานวน 2 ครงั้ จะไดผ้ ลดงั น้ี =C$2 » กด F4 จานวน 3 ครงั้ จะไดผ้ ลดงั น้ี =$C2 » กด F4 จานวน 4 ครงั้ จะไดผ้ ลดงั น้ี =C2 (จะกลบั มาเป็นเหมอื นเดมิ )

การกาหนดฟังกช์ นั ดว้ ยตนเอง เป็นการพมิ พส์ ตู รในการคานวณลงไปเองในเซลลโ์ ดยมวี ธิ กี ารป บิ ตั ดิ งั น้ี 1. คลิกเลอื กเซลล์ทต่ี ้องการจะใส่สตู รและพมิ พเ์ คร่อื งหมาย = และตามดว้ ยฟังก์ชนั ท่ตี ้องการคานวณเช่น =SUM (ชว่ งของกล่มุ เซลลท์ ต่ี อ้ งการคานวณ) กดป่มุ Enter 2. คดั ลอกสตู รแนวนอนตงั้ แตเ่ ซลล์ D2:D6 3. พมิ พส์ ตู รผลรวมแนวตงั้ =SUM (D2:D6) ทเ่ี ซลล์ D7

การเขยี นฟังกช์ นั โดยใชแ้ ทบ็ Formulas (สตู ร) เลอื กป่มุ คาสงั่ ฟังกช์ นั นนั้ บอ่ ยๆ โดยมวี ธิ กี ารป บิ ตั ดิ งั น้ี 1. คลกิ เลอื กเซลลท์ ต่ี อ้ งการใสฟ่ ังกช์ นั 2. คลิกท่ปี ุ่มคาสงั่ Insert Function (แทรกฟังก์ชนั ) หรอื กด Shift+F3 จะปราก Dialog Box โดยมี รายละเอยี ดดงั น้ี » Search for a function : เลอื กฟังกช์ นั โดยการคน้ หาจากคาทต่ี อ้ งการ » Or select a category : หรอื เลอื กประเภทของฟังกช์ นั » Select a function : เลอื กรปู แบบฟังกช์ นั ทต่ี อ้ งการคานวณ 3. คลกิ ทป่ี ่มุ OK 4. ท่ชี ่อง Number1 ในโปรแกรม Microsoft Excel 2016 จะใส่ช่ือของเซลล์มาให้โดยอตั โนมัติถ้าไม่ตรง หรอื ไมถ่ กู ตอ้ งตามทต่ี อ้ งการใหใ้ ชเ้ มาสค์ ลกิ เลอื กเซลลท์ จ่ี ะนามาคานวณหรอื พมิ พด์ ว้ ยตนเอง

5. หากยงั มกี ารคานวณอกี กใ็ หใ้ สใ่ นชอ่ งถดั ไปอาจจะใชว้ ธิ กี ารพมิ พช์ ่อื เซลลห์ รอื การใชเ้ มาสค์ ลิกเลอื กเซลลเ์ องกไ็ ด้ 6. คลกิ ทป่ี ่มุ OK

ผลลพั ธท์ ่ีได้

ความผดิ พลาดของสูตรคานวณ (Error Message) เม่อื มกี ารทางานเก่ยี วกบั สูตรหรอื ฟังก์ชนั ถ้าป้อนคา่ ไม่ถูกต้อง โปรแกรมจะแสดงขอ้ ความแสดงความผดิ พลาด เพ่อื ใหแ้ กไ้ ขใหถ้ กู ตอ้ ง

การใชส้ ตู รและฟังก์ชนั ของโปรแกรม Microsoft Excel 2016 ในงานประเภทต่างๆ ตอ้ งมคี วามเขา้ ใจคาสงั่ เคร่อื งมอื สตู รและฟังกช์ นั เพ่อื นาไปประยุกตใ์ ชใ้ นงานใหเ้ กดิ ประสทิ ธภิ าพ โปรแกรมแบ่งประเภทของสตู รเป็น สูตรในการคานวณทางคณิตศาสตร์ สูตรในการเปรียบเทยี บ สูตรในการเช่อื มข้อความและสูตรในการอา้ ง องิ และโปรแกรมยงั เตรียมฟังก์ชนั ท่ีเปรียบเสมือนสูตรสาเร็จท่ีได้จัดเตรียมไว้แล้ว โดยแบ่งตาม การใช้งาน เชน่ ฟังกช์ นั ทางสถติ ิ ฟังกช์ นั ทางตรรกะ ฟังกช์ นั ทใ่ี ชใ้ นการคน้ หาและอา้ งองิ เป็นตน้ ในการคานวณโดยใชส้ ตู ร และฟังกช์ นั จะตอ้ งเลอื กเซลล์ และป้อนเครอ่ื งหมาย = ตามดว้ ยสตู รหรอื ฟังกช์ นั ในการคานวณ เม่อื มกี ารป้อนสตู ร หรือฟังก์ชันไม่ถูกต้องโปรแกรมจะแจ้งข้อผิดพลาดในการใช้งานเพ่ือจะได้แก้ไขให้ถูกต้อง โปรแ กรม มคี วามสามารถและมคี วามยดื หย่นุ ทใ่ี หผ้ ูใ้ ชง้ านสามารถสรา้ งสตู รการคานวณต่างๆ ไดใ้ หต้ รงกบั ความต้องการ จึงมกี ารนาไปประยุกต์ใช้งานทางด้านธุรกิจมากมาย เช่น งานด้านการตลาด งานด้านบญั ชี รวมถึงงานด้านการศึกษา เชน่ การนาไปใชใ้ นการประเมนิ ผลการเรยี นการสอน