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

หน่วยที่ 6 การประยกุ ต์ใช้โปรแกรมตารางงานประเภทของสตู ร สตู รในโปรแกรม Excel หมายถงึ โจทย์หรอื การคานวณตา่ ง ๆ เชน่ 25+15+8 หรือ =B9-C4+D5เป็นตน้เมอื่ ป้อนสตู รคานวณในตารางเวิร์กชีตใหใ้ ช้เคร่อื งหมาย = (เทา่ กบั ) นาหน้าสูตรเสมอ มิฉะนนั้ โปรแกรม Excel จะถือว่าสง่ิ ทีป่ อ้ นนั้นเปน็ ขอ้ มลู ธรรมดาทไ่ี ม่ใชส่ ูตรโปรแกรม Microsoft Excel แบง่ ชนดิ ของสูตรออกเป็น 4 ชนดิ คอื1.) สูตรในการคานวณทางคณิตศาสตร์ (Arithmetic Formula)2.) สตู รในการเปรียบเทียบ (Comparison Formula)3.) เครือ่ งหมายในการเชอ่ื มขอ้ ความสองขอ้ ความหรอื มากกวา่ น้ัน (Text Formula)4.) สตู รในการอา้ งองิ (Text Formula)

วธิ ีการป้อนสูตร การคานวณคา่ ตัวเลขโดยวธิ ีการสร้างสตู รทาไดโ้ ดยการเลอื กเซลลท์ ต่ี อ้ งการให้ผลลัพธเ์ ซลล์แลว้ป้อนเคร่อื งหมายเทา่ กบั (=) แลว้ ตามด้วยตัวเลขท่ีใช้หรือตาแหน่งของเซลลท์ เี่ ก็บคา่ ของข้อมูลที่เป็นตวั เลขท่ีใช้ในการคานวณและเครอ่ื งหมายทางคณติ ศาสตร์ เช่นสูตร ผลลพั ธ์=2*5 คณู 2ด้วย5ไดผ้ ลลัพธ์คอื 10=25-5 ลบ5ออกจาก25ไดผ้ ลลัพธ์คือ20=10^2 หาค่ายกกาลัง2ของ10ไดผ้ ลลพั ธ์100=A5/B5 หาคา่ ของเซลล์ A5 ดว้ ยคา่ ของเซลล์ B5การคานวณโดยใช้สูตร การคานวณใน Microsoft Excel 2010 มีขน้ั ตอนดังน้ี 1.) พิมพ์เคร่อื งหมาย = 2.) อ้างองิ เซลล์ 3.) พมิ พ์เครอ่ื งหมายในการคานวณประเภทของฟังกช์ ันและฟังก์ชนั ที่ใชง้ านบอ่ ยประเภทของฟังกช์ ันฟังกช์ นั ในการคานวณที่ใช้งานสามารถแบ่งประเภทๆได้ดังนี้

1.) ฟังก์ชันทางคณิตศาสตร์ 2.) ฟงั ก์ชนั ทางตรรกศาสตร์ 3.) ฟังก์ชนั ท่ีเกีย่ วกับวันท่ี 4.) ฟังกช์ นั ทีเ่ กยี่ วกบั เวลา 5.) ฟังกช์ นั ที่เกย่ี วกบั การเงนิ 6.) ฟงั กช์ ันที่เกี่ยวกบั ตัวอกั ษร 7.) ฟงั กช์ ันทางสถติ ิ 8.) ฟงั ก์ชนั ในการค้นหาข้อมูล 9.) ฟังก์ชนั ทางด้านวิศวกรรม 10.) ฟงั ก์ชันในการจัดการฐานข้อมูลฟงั กช์ ันท่ใี ชง้ านบอ่ ย ในโปรแกรม Microsoft Office Excel 2013 จะมีฟงั ก์ชันมากมายและสามารถนาไปประยกุ ต์ใช้งานในด้านตา่ งๆรวมท้งั งานที่สลบั ซับซอ้ นได้เปน็ อยา่ งดี การใชง้ านจะทาไดอ้ ย่างสะดวกและรวดเรว็ ในทนี่ จ้ี ะอธบิ ายเฉพาะฟังก์ชนั ทใี่ ชง้ านบ่อยๆ ดังน้ี 1.) SUM (การหาผลรวมของข้อมูล) รูปแบบ > =SUM(Number 1,Number 2,...) Number เป็นกลุ่มของข้อมูลท่ีตอ้ งการหาผลรวมซึง่ สามารถจะใส่ได้มากกวา่ 1 กลุม่ โดยใช้เครื่องหมาย,คั่นกลางระหว่างแตล่ ะกลุ่มข้อมลู เช่น ใหาหาผลรวมตั้งแต่เซลล์ A1 ถึงเซลล์ A10 > =SUM(A1,.....,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)4.) MAX (การหาค่าสงู สดุ ของจานวน) รูปแบบ > =MAX(Number1,Number2,...) Number เป็นกลุ่มของขอ้ มลู ทีต่ อ้ งการหาคา่ สงู สุด เช่น ให้หาคา่ ต่าสุดของเซลล์ B2 ถึงเซลล์ F2 > =MIN(B2:F2)5.) AVERAGE (การหาค่าเฉล่ยี ของข้อมูล) รปู แบบ > =AVERAGE(Number1,Number2,...) Number เป็นกลุม่ ของข้อมุลท่ตี ้องการหาคา่ เฉลีย่ เชน่ ใหห้ าคา่ เฉล่ียของเซลล์ F1 ถึงเซลล์ F8 > =AVERAGE(F1,F8)6.) COUNT (การนับจานวนข้อมูลที่เป็นเฉพาะตัวเลข) รปู แบบ > =COUNT(Value1,Value2,...) Value ชว่ งของกลมุ่ เซลลท์ น่ี ามาใช้ในการนับจานวนเฉพาะตัวเลข เชน่ ให้นับจานวนผู้เรยี นท่ีมี คะแนนว่ามีก่ีคนจะไม่นบั นักเรียน ท่ี ขส. หรอื มส. โดยอ้างที่เซลล์ C2 ถึงเซลล์ C10 > =COUNT(C2:C10)7.) COUNTA (การนับจานวนข้อมูลท่เี ป็นทง้ั ขอ้ ความและตัวเลขปนกัน) รูปแบบ > =COUNT(Value1,Value2,...) Value ชว่ งของกลุ่มเซลลท์ ่ีนามาใช้ในการนบั จานวน เช่น ให้นบั จานวนผู้เรียนที่มีคะแนนว่ามี กี่คนจะไมน่ ับนกั เรียน ท่ี ขส. หรือ มส. โดยอา้ งทเ่ี ซลล์ C2 ถงึ เซลล์ C10 > =COUNT(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() ผลลพั ธคื อื 14/4/2013 12.) VLOOKUP (การคน้ หาและแสดงขอ้ มลู ) รูปแบบ > =VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup) Lookup_value คา่ ที่ใชใ้ นการคน้ หา Tanle_array ตารางขอ้ มลู ทีใ่ ช้สาหรับแสดงผลและค้นหาข้อมูล Col_index_num คอลัมนท์ ่ีให้แสดงข้อมลู ออกมาโดยคอลมั นแื รกมคี า่ เป็น 1 และคอลมั นต์ อ่ ไปจะ เป็น 2,3,..ตามลาดับ Range_lookup คา่ ทางตรรกะท่ีกาหนดในการค้นหามี 2 รูปแบบ False ใช้ค้นหาคา่ ท่ีตรงกบั คา่ ที่ใชใ้ นการคน้ หา True ใชค้ ้นหาค่าทม่ี คี ่านอ้ ยกวา่ หรือเท่ากับค่าท่ีใชใ้ นการคน้ หา เช่น ทเ่ี ซลล์ E4 ถงึ E10 คือ ตอ้ งการให้แสดงรายชื่อหนงั สือโดยอตั โนมตั เิ ม่ือได้มกี ารปอ้ นรหัสหนงั สือลงไปในเซลล์ D4 ถงึ D10การอา้ งองิ แบบสัมพันธ์ ในการคานวณในโปรแกรม Microsoft office Excel 2013 จะตอ้ งมีการคดั ลอกสูตรหรือฟังก์ชนั โดยการอ้างอิงเซลล์ซ่ึงจะมีวิธกี ารอ้างองิ เซลล์ 2 แบบ คือการอ้างองิ แบบสัมพนั ธ์ (Relative reference) และการอ้างอิงแบบสัมพนั ธ์ (Absolute reference) โดยมวี ิธีปฏบิ ตั ดิ งั นี้

เปน็ การคัดลอกสูตรท่ีเกดิ ขึ้นใหมต่ ามตาแหนง่ ของเซลล์โดยอตั โนมตั ิผลลพั ธ์ที่ไดจ้ ะเปลย่ี นไปตามตาแหนง่ของแถวและคอลัมน์ เชน่ เมือ่ ผูใ้ ช้สูตรในการคานวณแบบสมั พันธโ์ ดยที่ D2 ใหท้ าการใส่สตู รคือ =B2*C2 เม่ือทาการคดั ลอกสูตรนีไ้ ปที่ D3 สูตรก็จะเปลีย่ นเป็น =B3*C3 ให้โดยอตั โนมตั กิ ารคัดลอกสตู รนนั้ สามารถทาไดท้ งั้ ตามแนวตัง้ และตามแนวนอน การอ้างองิ แบบสัมบูรณ์(Absolute reference) เป็นการอ้างองิ ถึงเซลลใ์ ดเซลล์หนง่ึ เปน็ หลกั หรอื กลมุ่ หนง่ึ โดนเฉพาะซ่ึงจะใชเ้ คร่อื งหมาย ($) ในการกาหนดโดยจะใสน่ าหนา้ ตัวอกั ษรกากบั คอลมั น์ หรือ เลขกากบั แถวการอา้ งองิ แบบน้จี ะเปน็ การอา้ งองิ เซลล์เดิมไม่วา่ จะยา้ ยกรทางานไปท่ีเซลลใ์ ดก็ตามเชน่ =$D$2 (หากต้องการใสเคร่ืองหมาย $ อตั โนมัติใหก้ ดป่มุ F4)การคานวณโดยใช้ฟังกช์ ันการใชง้ านฟงั กช์ ันมีดว้ ยกัน 2 วธิ ี คือการใส่ฟังก์ชันด้วยตนเอง หรือการใส่ฟงั ก์ชนั วซิ ารด์ วธิ ีที่ 1 การใสฟ่ ังกช์ ันด้วยตนเอง 1.) คลกิ เลือกเซลล์ท่ตี อ้ งหาผลลพั ธโ์ ดยการใช้ฟงั กช์ นั 2.) ใสเ่ คร่ืองหมายเท่ากบั ( = ) ตามด้วยชือ่ ของฟังกช์ นั และใสว่ งเลบ็ ภายในขอบเขตของช่วงท่ตี ้องการหา 3.) กด Enter วิธีที่ 2 การใสฟ่ งั ก์ชันวิซารด์ 1.) คลิกทีค่ าสง่ั แทรก (Insert) บนเมนูบาร์ เลอื กคาสงั่ ฟงั ก์ชัน (Function) 2.) จะเกดิ กรอบโต้ตอบ แทรกฟังกช์ นั (Insert Functions)คลกิ เลอื กรูปแบบฟงั กช์ นั ทต่ี ้องการ 3.) สมมติถ้าเราต้องการเลอื กฟงั ก์ชนั SUM ซึ่งใชใ้ นการหาผลรวมของขอ้ มลู จะปรากฏดังภาพ 4.) ฟงั กช์ นั SUM จะมีรปู แบบคือ SUM(number1,number 2,…number 30) หรอื คลิปเมาส์ที่คลิกป่มุ ตกลงInsert Function (แทรกฟงั กช์ นั ) การใชป้ ุ่มคาส่ัง Insert Function (แทรกฟงั กช์ ัน) สว่ นใหญ่ผใุ้ ช้จะใช้เม่ือจาฟังกืชนั ไม่ได้หรอื ไมค่ ่อยใช้ฟงั กช์ ันนนั้ บอ่ ยๆ โดยมีวธิ ีการปฏิบตั ดิ ังน้ี 1.) คลิกเลือกเซลล์ท่ตี ้องการใส่ฟังกช์ ัน 2.) คลิกที่ปุ่มคาสัง่ Insert Function (แทรกฟงั กช์ นั ) หรอื กด Shift+F3 จะปรากฏ Dialog Box โดยมีรายละเอยี ดดงั น้ี > Search for a function : เลือกฟังก์ชนั้ โดยการคน้ หาจากคาท่ีตอ้ งการ > Or select a category : หรอื เลอื กประเภทของฟังก์ชนั > Select a function : เลอื กรปู แบบฟังกช์ นั ทต่ี อ้ งการคานวณ 3.) คลกิ ท่ีปมุ่ OK

4.) ทีช่ ่อง Number1 ในโปรแกรม Microsoft office Excel 2013 จะใส่ช่ือของเซลลม์ าให้โดย อตั โนมัติถา้ ไม่ตรงหรอื ไมถ่ กู ตอ้ งตามทีต่ อ้ งการใหใ้ ช้เมาสค์ ลิกเลอื กเซลลท์ ี่จะนามาคานวณหรอื พิมพ์ด้วยตนเอง 5.) หากยังมีการคานวณอกี กใ็ หใ้ สใ่ นช่องถดั ไปอาจจะใช้วิธกี ารพิมพ์ชอื่ เซลลห์ รือการใช้เมาสค์ ลิกเลือกเซลลเ์ องได้ 6.) คลกิ ที่ปุม่ OKข้อผดิ พลาดที่อาจเกดิ ขึ้นในการกาหนดฟงั กช์ ันและสตู ร ความผดิ พลาดของสูตรคานวณ (Error Message) เมือ่ มีการทางานเกี่ยวกบั สตู รหรอื ฟงั ก์ชนั ถา้ ป้อนค่าไม่ถกู ต้องโปรแกรมจะแสดงความผิดพลาด เพือ่ ใหแ้ ก้ไขให้ถกู ตอ้ ง ข้อความผดิ พลาด สาเหตุ######## ตัวเลขที่พิมพใ์ นเซลล์หรือผลจากสูตรในการคานวณยาวเกนิ กว่าท่ีจะแสดงใน#DIV/0 เซลล์#VALUE! เกิดจากตวั หารม่ีคา่ เป็น 0 คอื ตัวหารเป็นเซลลว์ า่ งๆ หรือคอื อาร์กวิ เมนต์#NAME? บางฟังกช์ ันมีค่าไมถ่ กู ตอ้ ง เกิดจาการใช้ Operand หรืออาร์กิวเมนต์ผดิ ประเภท เชน่ ฟงั กช์ นั ตอ้ งการ#N/A คา่ อารก์ ิวเมนต์เป็นตัวเลขแตก่ ลับใส่เปน็ ข้อความ(Not Available) เกดิ จากการอา้ งอิงช่อื ของเซลล์(Cell)ผิดพมิ พ์ชอื่ ของฟงั ก์ชันท่ใี ช้ในสตู รโดย#REF! สะกดไมถ่ ูกต้องอ้างองิ ถงึ เซลลเ์ ป็นช่วงแต่ไม่ใสเ่ คร่ืองหมาย : เช่น (A1 : A5)#NUM เมื่อมกี ารใชข้ ้อความในสตู รแล้วไม่ใส่เคร่ืองหมาย “ ” ครอ่ มข้อความนัน้ เกดิ จากการใส่อารก์ ิวเมนตใ์ หฟ้ งั ก์ชันไมค่ รบหรอื ใชค้ า่ อาร์กิวเมนตท์ ่ไี มถ่ กู กบั#NULL ฟังก์ชันกล่าวถงึ Cell ที่มคี า่ ในสตู ร เกดิ จากเซลลท์ อ่ี ้างอิงถึงในสตู รถกู ลบออกไปหรือถกู ข้อมลู ขากเซลล์อ่ืนยา้ ย มาทับแทนที่ หรอื อา้ งองิ ถงึ ข้อมลู ของโปรแกรมอื่นทไี่ ม่ได้ทางานอู่ในขณะนัน้ กาหนดอารก์ วิ เมนต์ผพิ ลาดผดิ ประเภททเี่ ก่ียวกับตัวเลขอาจกาหนดเป็นชนิด ขอ้ มลู อืน่ หรือผลรวมของสูตรคานวณท่ีไดจ้ ากตวั เลขมีค่ามากหรอื นอ้ ย เกินไป เกดิ จากการกาหนด Cell reference ผิดพลาด