Microsoft Excel เพื่องานธุรกิจ

ดร.สำวติ รี บญุ มี สว่ นท่ี 3 กำรประยกุ ตใ์ ชฟ้ งั กช์ ัน Microsoft Excel ในงำนธุรกิจ 135 SUMIF(range, criteria, [sum_range]) เพ่ือหำผลรวมของคำ่ ตำ่ งๆ ในช่วงซึ่งเปน็ ไปตำมเกณฑ์ท่ีระบุ ตัวอย่ำงเชน่ สมมตวิ ่ำในคอลัมนห์ นึ่ง ที่มีตัวเลขต่ำงๆ เช่น ต้องกำรหำผลรวมเฉพำะค่ำที่มำกกว่ำ 5 สำมำรถใช้สูตรดังต่อไปนี้ =SUMIF(A1:A20,\">5\") ในตัวอย่ำงน้ี เกณฑ์น้ีถูกนำไปใช้กับค่ำท่ีเท่ำกันท่ีกำลังหำผลรวม นอกจำกนี้ยัง สำมำรถนำเกณฑ์นี้ไปใช้กับช่วงหนึ่ง แล้วหำผลรวมของค่ำท่ีตรงกันในช่วงอ่ืนได้ถ้ำต้องกำร ตัวอย่ำงเช่น สูตร =SUMIF(A1:A20, \"ปกติ\", C1:C20) หำผลรวมเฉพำะค่ำในช่วง C1:C20 เฉพำะเซลล์ที่ตรงกันในช่วง A1:A20 ทม่ี ีคำ่ เทำ่ กบั \"ปกติ\"  range คอื ช่วงของเซลล์ทีต่ ้องกำรให้ประเมินตำมเกณฑ์ เซลลใ์ นแตล่ ะชว่ งต้องเป็นตวั เลขหรือ ชอื่ อำเรย์ หรอื กำรอำ้ งอิงซึง่ ประกอบด้วยตัวเลข คำ่ วำ่ งและขอ้ ควำมจะถูกละเวน้  criteria คือ เกณฑ์ในรูปแบบของตัวเลข นิพจน์ กำรอ้ำงอิงเซลล์ 1 เซลล์ หรือสตริงข้อควำมท่ี เงื่อนไขในกำรนับ เช่น 32, \">32\", B4, \"แอปเป้ิล\" หรือ \"32\" สำมำรถใช้อักขระตัวแทน เช่น เคร่ืองหมำยคำถำม (?) แทนอกั ขระหนง่ึ ตัว และเครอื่ งหมำยดอกจนั (*) แทนอักขระหลำยตวั ใน เกณฑ์ได้ ถ้ำเกณฑ์เป็นตัวอักษรหรือสัญลักษณ์ต้องอยู่ในเคร่ืองหมำยอัญประกำศ (\"\") ถ้ำเกณฑ์เป็นตัวเลข หรอื ชื่อเซลล์ ไม่จำเป็นตอ้ งมเี ครื่องหมำยอญั ประกำศ  sum_range คือ เซลล์จริงท่ีจะใช้หำค่ำผลรวม ถ้ำเป็นเซลล์อื่นท่ีนอกเหนือจำกเซลล์ที่ระบุไว้ ในอำร์กิวเมนต์ range ถ้ำอำร์กิวเมนต์ sum_range ถูกละไว้ Excel จะใช้ช่วงเซลล์ ที่ถูกระบุใน อำรก์ วิ เมนต์ range มำหำผลรวมแทน  ตัวอย่ำงกำรใช้ฟังกช์ นั =SUMIF(A1:A20,100) หำผลรวมจำก A1:A20 ที่มีค่ำเท่ำกับ 100 =SUMIF(A1:A20,”>100”,B1:B20) หำผลรวมจำก B1:B20 เฉพำะ เซลล์ทต่ี รงกบั ชว่ งเซลล์ A1:A20 ค่ำที่มำกกวำ่ 100 =SUMIF(A1:A20,C1,B1:B20) หำผลรวมจำก B1:B20 เฉพำะเซลล์ท่ี ตรงกบั ชว่ งเซลล์ A1:A20 เฉพำะค่ำทเ่ี หมือนค่ำในเซลล์ C1 =SUMIF(A1:A20,”นักศึกษำ”,B1:B20) หำผลรวมจำก B1:B20 เฉพำะเซลลท์ ตี่ รงกบั ช่วงเซลล์ A1:A20 เฉพำะคำว่ำ นักศกึ ษำ =SUMIF(A1:A20,”*ก*”,B1:B20) หำผลรวมจำก B1:B20 เฉพำะ เซลลท์ ่ตี รงกบั ช่วงเซลล์ A1:A20 เฉพำะคำที่ม“ี ก”ในข้อควำม จำกตัวอยำ่ ง ถำ้ ตอ้ งกำรหำผลรวมของสนิ ค้ำประเภทหนังสือ =SUMIF(C2:C11,\"หนงั สือ\",F2:F11) ถำ้ ตอ้ งกำรหำผลรวมของสินค้ำคงเหลอื ทีม่ ีรำคำขำยมำกกวำ่ 100 บำท

136 บทท่ี 6 กำรใชฟ้ ังกช์ นั คณิตศำสตร์และสถติ เิ บื้องต้นเพอ่ื สรปุ ข้อมลู ทำงธุรกิจ =SUMIF(E2:E11,\">100\",F2:F11) หรือตำมภำพที่ 6.14 ภำพท่ี 6.14 หนำ้ ต่ำงฟังก์ชัน SUMIF AVERAGE(number1, [number2],...) หำคำ่ เฉลีย่ (คำ่ เฉลยี่ เลขคณิต) ของอำรก์ ิวเมนต์  Number1, number2,... คืออำร์กิวเมนต์ 1 ถึง 30 ที่ต้องกำรหำผลค่ำเฉล่ีย สำมำรถเป็นได้ ท้ังเซลลเ์ ดยี วหรือช่วงของเซลล์ก็ได้ คำ่ ทใ่ี ส่ในอำร์กวิ เมนตน์ ต้ี ้องเป็นรูปแบบตวั เลขเท่ำนนั้  ตัวอย่ำงกำรใชฟ้ ังก์ชัน =AVERAGE(A1:A5), =AVERAGE(A1:A5, C8:C15, D10:D20) จำกตัวอยำ่ ง ถำ้ ตอ้ งกำรหำรำคำขำยเฉลีย่ ของสินคำ้ ทงั้ หมด =AVERAGE(E2:E11) หรือตำมภำพท่ี 6.15 ภำพท่ี 6.15 หนำ้ ตำ่ งฟงั กช์ นั AVERAGE

ดร.สำวติ รี บญุ มี ส่วนที่ 3 กำรประยุกต์ใชฟ้ งั กช์ ัน Microsoft Excel ในงำนธุรกิจ 137 AVERAGEIF(range,criteria,average_range) หำค่ำเฉลยี่ (ค่ำเฉลย่ี เลขคณิต) ของเซลล์ท้งั หมดในช่วงทต่ี รงกบั เกณฑ์ทใี่ ห้  Range ช่วงของเซลล์ที่ต้องกำรให้ประเมินตำมเกณฑ์ เซลล์ในแต่ละช่วงต้องเป็นตัวเลข หรือช่ืออำเรย์ หรือกำรอ้ำงอิงซง่ึ ประกอบดว้ ยตวั เลข คำ่ วำ่ งและข้อควำมจะถกู ละเวน้  criteria คอื เกณฑ์ในรูปแบบของตัวเลข นพิ จน์ กำรอำ้ งอิงเซลล์ 1 เซลล์ หรอื สตริงข้อควำม ที่เง่ือนไขในกำรนับ เช่น 32, \">32\", B4, \"แอปเปิ้ล\" หรือ \"32\" สำมำรถใช้อักขระตัวแทน เช่น เครื่องหมำยคำถำม (?) แทนอักขระหน่ึงตัว และเคร่ืองหมำยดอกจัน (*) แทนอักขระ หลำยตวั ในเกณฑไ์ ด้ ถ้ำเกณฑ์เป็นตัวอักษรหรือสัญลักษณ์ต้องอยู่ในเคร่ืองหมำยอัญประกำศ (\"\") ถ้ำเกณฑ์เป็น ตัวเลขหรือชอ่ื เซลล์ ไม่จำเป็นตอ้ งมีเครอื่ งหมำยอญั ประกำศ  average_range เซลล์จริงที่จะใช้หำค่ำเฉล่ีย ถ้ำเป็นเซลล์อ่ืนทน่ี อกเหนือจำกเซลล์ท่ีระบุ ไว้ในอำร์กิวเมนต์ range ถ้ำอำร์กิวเมนต์ average_range ถูกละไว้ Excel จะใช้ช่วงเซลล์ ที่ ถูกระบุในอำร์กิวเมนต์ range มำหำค่ำเฉลี่ยแทน  ตวั อยำ่ งกำรใช้ฟงั กช์ ัน =AVERAGEIF(A1:A20,100) หำคำ่ เฉล่ยี จำก A1:A20 ทม่ี คี ่ำเทำ่ กับ 100 =AVERAGEIF(A1:A20,”>100”,B1:B20) หำค่ำเฉล่ียจำก B1:B20 เฉพำะเซลลท์ ตี่ รงกบั ช่วงเซลล์ A1:A20 คำ่ ท่ีมำกกว่ำ 100 =AVERAGEIF(A1:A20,C1,B1:B20) หำค่ำเฉลี่ยจำก B1:B20 เฉพำะ เซลล์ทตี่ รงกับชว่ งเซลล์ A1:A20 เฉพำะค่ำทเ่ี หมือนคำ่ ในเซลล์ C1 =AVERAGEIF(A1:A20,”นักศึกษำ”,B1:B20) หำค่ำเฉลีย่ จำก B1:B20 เฉพำะเซลลท์ ่ตี รงกับช่วงเซลล์ A1:A20 เฉพำะคำวำ่ นกั ศึกษำ =AVERAGEIF(A1:A20,”*ก*”,B1:B20) หำค่ำเฉลี่ยจำก B1:B20 เฉพำะเซลล์ท่ตี รงกบั ชว่ งเซลล์ A1:A20 เฉพำะคำที่มี“ก”ในข้อควำม จำกตัวอยำ่ ง ถำ้ ตอ้ งกำรหำรำคำขำยเฉลยี่ ของสินคำ้ ประเภทหนังสอื =AVERAGEIF(C2:C11,\"หนงั สือ\",E2:E11) ถำ้ ตอ้ งกำรหำรำคำขำยเฉลยี่ ของสินคำ้ ที่มจี ำนวนคงเหลือมำกกว่ำ 50 =AVERAGEIF(F2:F11,\">50\",E2:E11) หรอื ตำมภำพที่ 6.16

138 บทที่ 6 กำรใชฟ้ ังกช์ ันคณิตศำสตรแ์ ละสถติ เิ บื้องต้นเพ่อื สรปุ ข้อมูลทำงธุรกิจ ภำพท่ี 6.16 หน้ำตำ่ งฟงั ก์ชนั AVERAGEIF MAX (number1,number2,...) หำคำ่ มำกสดุ จำกในชดุ ของคำ่ ทร่ี ะบุ  Number1, number2, ... คือตัวเลข 1 ถึง 255 หรือชื่อ อำร์เรย์ หรือกำรอ้ำงอิงที่มี ตัวเลข สำมำรถเป็นได้ทั้งเซลล์เดียวหรือช่วงของเซลล์ก็ได้ ค่ำที่ใส่ในอำร์กิวเมนต์นี้ต้องเป็น รปู แบบตัวเลขเทำ่ นนั้  ตัวอย่ำงกำรใชฟ้ งั ก์ชัน =MAX(A1:A5), =MAX(A1:A5,C8:C15,D10:D20) จำกตวั อยำ่ ง ถ้ำต้องกำรหำรำคำสนิ คำ้ ทีส่ ูงทส่ี ุด =MAX(E2:E11) หรือตำมภำพท่ี 6.17 ภำพที่ 6.17 หนำ้ ต่ำงฟังกช์ ัน MAX

ดร.สำวติ รี บญุ มี สว่ นที่ 3 กำรประยกุ ตใ์ ช้ฟังก์ชนั Microsoft Excel ในงำนธรุ กิจ 139 MIN (number1,number2,...) หำค่ำนอ้ ยสดุ จำกในชุดของค่ำทร่ี ะบุ  Number1, number2, ... คือตัวเลข 1 ถึง 255 หรือชื่อ อำร์เรย์ หรือกำรอ้ำงอิงท่ีมี ตัวเลข สำมำรถเป็นได้ท้ังเซลล์เดียวหรือช่วงของเซลล์ก็ได้ ค่ำที่ใส่ในอำร์กิวเมนต์นี้ต้องเป็น รปู แบบตวั เลขเทำ่ นั้น  ตัวอยำ่ งกำรใชฟ้ งั ก์ชนั =MIN(A1:A5), =MIN(A1:A5,C8:C15,D10:D20) จำกตัวอยำ่ ง ถ้ำตัองกำรหำรำคำตน้ ทุนสินคำ้ ท่ีต่ำทสี่ ุด =MIN(D2:D11) หรือตำมภำพที่ 6.18 ภำพที่ 6.18 หน้ำต่ำงฟงั กช์ ัน MIN RANK.EQ(number,ref,[order]) หรือ RANK(number,ref,[order]) หำคำ่ ลำดับท่ขี องตวั เลขซงึ่ อยู่ในรำยกำรตวั เลข  Number คอื ตัวเลขท่ีมลี ำดบั ท่ีต้องกำรคน้ หำ เป็นคำ่ หน่งึ คำ่ หรือเซลลห์ น่งึ เซลล์  Ref คือ ช่วงเซลล์ หรืออำร์เรย์ หรือกำรอ้ำงอิงถึงรำยกำรตัวเลข ค่ำที่ไม่ใช่ตัวเลขใน ref จะถูกละเว้น  Order คือ ตัวเลขเงอ่ื นไขที่ระบุวำ่ จะจดั ลำดบั ตวั เลขอย่ำงไร ถ้ำ order เป็น 0 (ศนู ย์) หรือละเว้น ถือว่ำเรยี งจำกมำกไปหำนอ้ ย ถำ้ order เท่ำกับคำ่ อื่นทีไ่ ม่ใช่ 0 ถอื ว่ำเรยี งจำกน้อยไปหำมำก  ตัวอย่ำงกำรใช้ฟังก์ชัน =RANK.EQ(A1,$A$1:$A$20,0) หำค่ำลำดับที่ของค่ำในเซลล์ A1 จำกชว่ ง $A$1:$A$20 โดยเรียงจำกมำกไปน้อย คอื ค่ำท่ีมำกทีส่ ดุ จะได้ผลลพั ธเ์ ป็น 1 จำกตวั อยำ่ ง ถ้ำต้องกำรลำดบั สนิ ค้ำตำมรำคำจำกสงู ท่ีสุดไปต่ำทส่ี ุด

140 บทท่ี 6 กำรใช้ฟังกช์ นั คณิตศำสตร์และสถิติเบื้องต้นเพอ่ื สรปุ ข้อมูลทำงธรุ กจิ =RANK.EQ(E2,$E$2:$E$11,0) หรือตำมภำพท่ี 6.19 ภำพท่ี 6.19 หน้ำต่ำงฟงั ก์ชัน RANK.EQ ปรับปรุงจำก: http://office.microsoft.com/th-th/excel-help/CH010064502.aspx?CTT=97 6.3.2 ตวั อยา่ งการใช้ฟังก์ชัน 1) เปิดไฟล์ชือ่ ch6basicfunction.xlsx แผ่นงำนชื่อ “ข้อมูลสินค้ำ” ภำพที่ 6.20 แผน่ งำน “ข้อมูลสินคำ้ ” 2) สรำ้ งสูตรคำนวณตำมคำอธิบำยกอ่ นหนำ้ โดยใช้ฟังก์ชันคณิตศำสตรแ์ ละสถิติ 3) เปรียบเทยี บคำตอบท่คี ำนวณด้วยมือและคำตอบท่ีคำนวณด้วยฟังก์ชันใน Excel และเวลำที่ใช้ใน กำรคำนวณ

ดร.สำวติ รี บญุ มี สว่ นที่ 3 กำรประยุกตใ์ ชฟ้ งั กช์ นั Microsoft Excel ในงำนธรุ กิจ 141 ภำพที่ 6.21 ตัวอยำ่ งกำรใช้ฟังก์ชนั คณิศำสตรแ์ ละสถิตเิ บ้ืองต้น 6.4 สรุป หน่ึงในจุดเด่นทท่ี ำให้ Excel ได้รับควำมนิยมคอื ควำมสำมำรถในกำรทำงำนกับข้อมูลจำนวนมำกโดย ใช้ฟังก์ชันที่กำหนดมำล่วงหน้ำโดยไมโครซอฟท์ ในบทนี้ได้กล่ำวถึงวิธีกำรเรียกใช้ฟังก์ชัน องค์ประกอบ ชองฟังก์ชัน และกำรทำควำมเข้ำใจอำร์กิวเมนต์ของฟังก์ชันใน Excel โดยฟังก์ชันท่ีอธิบำยในบทน้ี เป็น ฟังก์ชันพ้ืนฐำนท่ีใช้บ่อยและจำเป็นต้องรู้ ประกอบด้วย 11 ฟังก์ชัน คือ ฟังก์ชัน COUNT สำหรับนับ จำนวนท่ีเป็นตัวเลข COUNTA สำหรับนับจำนวนทุกรูปแบบ COUNTBLANK สำหรับนับเซลล์ว่ำง COUNTIF สำหรับนบั เซลลต์ ำมเงอ่ื นไขที่กำหนด SUM สำหรบั หำผลรวมตัวเลข SUMIF สำหรบั หำผลรวม ตัวเลขตำมเง่ือนไข AVERAGE สำหรับหำค่ำเฉล่ีย AVERAGEIF สำหรับหำค่ำเฉล่ียตำมเง่ือนไข MAX สำหรับหำค่ำสูงสุด MIN สำหรับหำค่ำต่ำสุด และ RANK.EQ สำหรับหำลำดับท่ีของค่ำ โดยฟังก์ชันเหล่ำนี้ สำมำรถนำไปประยุกต์ใช้ได้หลำกหลำยสถำนกำรณ์ ซึ่งแต่ละฟังก์ชันมีอำร์กิวเมนต์ที่แตกต่ำงกันออกไป กำรทำควำมเข้ำใจและคุ้นเคยกับอำร์กิวเมนต์ของแต่ละฟังก์ชันจะทำให้ใช้งำนได้อย่ำงรวดเร็วโดยไม่ต้อง เปิดหน้ำต่ำงฟังกช์ นั

142 บทที่ 6 กำรใช้ฟังกช์ นั คณิตศำสตรแ์ ละสถติ เิ บ้ืองตน้ เพอื่ สรปุ ข้อมูลทำงธรุ กจิ แบบฝึกหดั ทา้ ยบทท่ี 6 ตอนท่ี 1 ฝกึ ปฏบิ ตั คิ านวณขอ้ มลู สรุปนกั ศกึ ษาโดยใช้ฟงั ก์ชันคณติ ศาสตรแ์ ละสถิติ 1. เปดิ แผน่ งำนชอ่ื “function1” 2. กรอกสตู รที่ใชแ้ ละผลลพั ธ์จำกกำรคำนวณลงในตำรำงต้องเปน็ สตู รทค่ี ัดลอกไปยังเซลล์อน่ื ๆ ได้ คาถาม สูตร ผลลัพธ์ จำนวนนกั เรยี นทั้งหมด =COUNT(F3:F117) 115 จำนวนนกั เรียนชำย รวม (L8) จำนวนนักเรยี นตำมหม่เู ลือด A รวม (L15) สว่ นสูงเฉลย่ี นำ้ หนักเฉลี่ย อำยุเฉลย่ี สว่ นสงู เฉล่ียของนักเรียนชำย น้ำหนกั เฉล่ียของนักเรยี นชำย อำยุเฉลยี่ ของนักเรยี นชำย ส่วนสูงของนักเรยี นท่สี ูงท่สี ดุ นำ้ หนกั ของนักเรยี นท่เี บำทีส่ ดุ อำยขุ องนกั เรยี นท่ีมำกท่สี ุด

ดร.สำวิตรี บญุ มี ส่วนท่ี 3 กำรประยุกต์ใชฟ้ งั ก์ชัน Microsoft Excel ในงำนธุรกจิ 143 ตอนท่ี 2 ฝึกปฏิบัติคานวณขอ้ มลู สรุปคะแนนโดยใช้ฟงั กช์ ันคณติ ศาสตรแ์ ละสถิติ 3. เปดิ แผ่นงำนชื่อ “คะแนน” 4. กรอกสตู รท่ใี ช้และผลลพั ธ์จำกกำรคำนวณลงในตำรำงตอ้ งเป็นสตู รท่คี ัดลอกไปยังเซลล์อืน่ ๆ ได้ ผลลัพธ์ คาถาม สูตร 134 ลำดบั ท่ี (G3) จำนวนนกั เรียนทตี่ ก ค่ำเฉลยี่ คะแนนรวม (column D) ของนักเรยี นกล่มุ ท่ีตก คะแนนรวมสูงสดุ (J33) เปอร์เซน็ ตต์ ่ำสดุ (J36) ตอนที่ 3 ฝกึ ปฏิบัตคิ านวณขอ้ มลู สรปุ คะแนนโดยใช้ฟงั กช์ ันคณติ ศาสตร์และสถติ ิ 5. เปิดแผ่นงำนชื่อ “กลมุ่ อำชีพ”

144 บทที่ 6 กำรใช้ฟังกช์ ันคณิตศำสตรแ์ ละสถติ ิเบอื้ งต้นเพอ่ื สรปุ ขอ้ มลู ทำงธุรกจิ 6. กรอกสตู รที่ใชแ้ ละผลลพั ธจ์ ำกกำรคำนวณลงในตำรำงตอ้ งเป็นสูตรที่คดั ลอกไปยังเซลล์อืน่ ๆ ได้ ผลลพั ธ์ คาถาม สตู ร 134 ลำดบั ท่ี (H6) ผลรวม (D46) คาถาม สูตร ผลลัพธ์ ยอดรวมปรมิ ำณยอดขำยกลมุ่ อำชพี ปี 2550 (C4) ยอดรวมปรมิ ำณยอดขำยกลุ่มประเภทสนิ คำ้ อำหำร (C8) จำนวนกลมุ่ อำชพี ทม่ี รี ำยได้ (C15) จำนวนกลมุ่ อำชพี ทไ่ี มม่ รี ำยได้ (C16) จำนวนกลุม่ อำชพี แยกตำมประเภทสินค้ำอำหำร (C20)

บทที่ 7 การใชฟ้ ังกช์ นั IF เพอื่ การตดั สินใจทางธรุ กจิ เนื่องจากการดาเนินธุรกิจมักจะมีทางเลือกในการตัดสินใจอยู่เสมอ จึงเป็นส่ิงที่หลีกเลี่ยงไม่ได้ท่ี แบบจาลองทางธรุ กจิ จะต้องสามารถช่วยสนับสนุนการตดั สินใจเมอ่ื มีทางเลอื กได้ 7.1 การตดั สนิ ใจทางธุรกจิ (Business decision making) การตัดสินใจทางธรุ กิจแบ่งออกได้ เป็น 3 ประเภท (O'Brien J.A. และ Markus G.M., 2011) คือ 1) การตัดสินใจแบบไม่มีโครงสร้าง มักจะเป็นการตัดสินใจเก่ียวกับการวางแผนกลยุทธ์ทางธุรกิจ ระยะยาว โดยผ้บู ริหารระดบั สงู ไมใ่ ช่การตัดสนิ ใจทเ่ี กิดขึ้นเป็นประจา 2) การตัดสินใจแบบก่ึงโครงสร้าง มักจะเป็นการตัดสินใจที่สามารถระบุวิธีการล่วงหน้าได้บ้าง แต่ก็ ไม่สามารถเลือกทางเลือกท่ีเหมาะสมได้อย่างชัดเจนหรือแน่นอนทางใดทางหน่ึง โดยผู้บริหาร ระดับกลาง 3) การตัดสินใจแบบมีโครงสร้าง มักจะเป็นการตัดสินใจท่ีเกิดข้ึนประจา สามารถกาหนดวิธีการหรือ ขน้ั ตอนในการตดั สินใจไดล้ ่วงหน้า โดยผบู้ รหิ ารระดับปฏิบตั ิการ จากการตัดสินใจทั้ง 3 รูปแบบจะเห็นได้ว่า การตัดสินใจแบบมีโครงสร้างและก่ึงโครงสร้าง สามารถนาเทคโนโลยีสารสนเทศมาประยุกต์ใช้ช่วยในการตัดสินใจเม่ือมีทางเลือกตั้งแต่สองทางเลือกขึ้น ไปได้ โดยในกรณขี อง Excel นั้น สามารถนาฟังก์ชัน IF มาชว่ ยในการแสดงทางเลือกจากเง่ือนไขท่ีกาหนด ไวล้ ว่ งหนา้ ของการตัดสนิ ใจแบบมีโครงสร้างได้  ในบทน้ีใช้ไฟล์ประกอบเนื้อหาเพื่อใช้ในการฝึกปฏิบัติ ช่ือ ch7if.xlsx เปิดจาก CD หรือ สามารถดาวนโ์ หลดไฟล์ได้จากเวบ็ ไซต์ academic.udru.ac.th/sawitree 7.2 ฟังกช์ นั IF และอารก์ ิวเมนต์ ฟังก์ชัน If จะส่งกลับค่าหนึ่งค่า ถ้าเงื่อนไขท่ีระบุประเมินได้ว่าเป็น TRUE และส่งกลับค่าอีกค่าหน่ึง ถา้ เงอื่ นไขทรี่ ะบปุ ระเมินไดว้ า่ เป็น FALSE ไวยากรณ์ IF(logical_test,value_if_true,value_if_false) ฟงั กช์ นั IF ประกอบด้วย 3 อาร์กิวเมนต์ ดงั นี้  Logical_test คอื คา่ หรอื นพิ จน์ใดๆ ท่สี ามารถประเมินไดว้ า่ เปน็ TRUE หรือ FALSE ตัวอย่างเช่น B2>=50 เป็นนิพจน์แบบตรรกะเพื่อตรวจสอบเงื่อนไข ถ้าค่าในเซลล์ B2 มากกว่าหรือ เท่ากับ 50 นิพจน์จะประเมินว่าเป็น TRUE มิฉะน้ัน นิพจน์จะประเมินว่าเป็น FALSE เช่น ถ้า B2 มีค่า 100 นิพจน์จะมีค่าเป็น TRUE ถ้า B2 มีค่า10 นิพจน์จะมีค่าเป็น FALSE อาร์กิวเมนต์นี้สามารถใช้ ตัว

146 บทที่ 7 การใชฟ้ ังก์ชนั IF เพ่อื การตัดสนิ ใจทางธุรกจิ ดาเนินการคานวณการเปรียบเทียบ ใดๆ ก็ได้ และสามารถใช้ตรวจสอบเง่ือนไขหลายๆ เง่ือนไขพร้อมกัน ไดโ้ ดยใชร้ ่วมกบั ฟังกช์ นั AND(), OR()  Value_if_true คือค่าท่ีถูกสง่ กลบั ถ้า logical_test เป็น TRUE ตัวอย่างเช่น ถ้าอาร์กิวเมนต์นี้เป็นสตริงข้อความ ”ผ่าน” และใน logical_test ประเมินค่าเป็น TRUE (เช่น B2 มีค่า100 จากนิพจน์ B2>=50) จากน้ันฟังก์ชัน IF จะแสดงข้อความ ”ผ่าน” แต่ถ้า ไม่ ระบุอะไรไว้ในอาร์กิวเมนต์นี้ ฟังก์ชันจะแสดงผลคาตอบเป็น 0 (ศูนย์) Value_if_true สามารถเป็นสูตร คานวณอ่ืนๆ หรือฟงั กช์ ันอืน่ ได้  Value_if_false คือค่าทถี่ ูกสง่ กลบั ถ้า logical_test เปน็ FALSE ตัวอย่างเช่น ถ้าอาร์กิวเมนต์น้ีเป็นสตริงข้อความ ”ตก” และใน logical_test argument ประเมินค่าเป็น FALSE (เช่น B2 มีค่า10 จากนิพจน์ B2>=50) จากน้ันฟังก์ชัน IF จะแสดงข้อความ ” ตก” แตถ่ ้า ไม่ระบุอะไรไว้ในอาร์กิวเมนต์น้ี (คือ ไมม่ ีเครอ่ื งหมายจลุ ภาคหลังจาก value_if_true) ฟงั ก์ชัน จะแสดงผลคาตอบเป็น FALSE ถ้า logical_test เป็น FALSE และ value_if_false ว่างเปล่า (คือ มี เครื่องหมายจุลภาคตามด้วยเคร่ืองหมายวงเล็บปิดอยู่หลัง value_if_true) ค่า 0 (ศูนย์) จะถูกแสดงเป็น คาตอบ ทง้ั น้ี Value_if_false สามารถเป็นสตู รอืน่ ได้  คาแนะนาเพ่ิมเติม สามารถใช้ฟังก์ชัน IF ซ้อนกันเป็นอาร์กิวเมนต์ value_if_true และ value_if_false ได้เพียงเจ็ดระดับ เพื่อให้ได้การทดสอบที่ซับซ้อนย่ิงขึ้นถ้าต้องการทดสอบ มากกวา่ เจด็ เง่อื นไข ใหพ้ ิจารณาใชฟ้ งั กช์ ัน LOOKUP, VLOOKUP, หรือ HLOOKUP  ตวั อยา่ งการใช้ฟงั ก์ชัน ตรวจสอบเงอ่ื นไขเซลล์ B2 มคี า่ คะแนนมากกว่าหรือเท่ากับ 50 ถา้ เปน็ จรงิ ผลการเรียน คอื “ผา่ น” ถ้าเงื่อนไขเป็นเทจ็ ผลการเรยี น คอื “ตก” =IF(B2>=50,”ผ่าน”,”ตก”) หนา้ ต่างฟังกช์ ัน IF มลี กั ษณะตามภาพท่ี 7.1 ภาพท่ี 7.1 หนา้ ตา่ งฟังกช์ ัน IF

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยุกตใ์ ชฟ้ งั ก์ชัน Microsoft Excel ในงานธรุ กิจ 147 7.3 การใช้งานฟงั กช์ ัน IF เนื่องจากฟังก์ชัน IF แม้จะดูเป็นฟังก์ชันท่ีเหมือนจะใช้งานไม่ยาก แต่ยังมีผู้ใช้ท่ีสับสนกับการใช้งาน ดังนั้นในหัวข้อการใช้งานฟังก์ชัน IF น้ีจะแบ่งเป็น 3 หัวข้อ คือ การตรวจสอบเงื่อนไขของฟังก์ชัน IF เพ่ือ ใช้เข้าใจการทางานของแต่ละอาร์กิวเมนต์ และสามารถแปลผลลัพธ์ของฟังก์ชัน IF ได้ จากนั้นจึงฝึกการ แปลงกฏทางธุรกิจให้เปน็ วธิ ีการเขียนฟังก์ชนั IF แล้วจงึ ฝกึ สรา้ งฟงั กช์ นั IF ใน Excel 7.3.1 การตรวจสอบเงื่อนไขด้วยฟังกช์ นั IF หัวข้อการตรวจสอบเง่ือนไชด้วยฟังก์ชัน IF มีวัตถุประสงค์เพ่ือสร้างความคุ้นเคยกับ อาร์กวิ เมนต์ของฟังกช์ นั และการประมวลผลลพั ธ์ กอ่ นการเร่มิ ใชฟ้ งั กช์ ัน จากคาอธิบายในหัวข้อ 8.2 ฟังก์ชัน IF และอาร์กิวเมนต์สามารถสรุปสั้น ๆ ได้ว่าฟังก์ชัน IF ประกอบด้วย 3 อาร์กิวเมนต์ ซ่ึงเมื่อประมวลผล Excel จะประมวลผลจากซ้ายไปขวา คือเร่ิมจากชื่อ ฟังกช์ นั จากนนั้ Excel จะพจิ ารณาอารก์ วิ เมนต์แรกคือเงือ่ นไขวา่ เปน็ จรงิ หรอื เทจ็ ถ้าเป็นจริงจะทางานใน อาร์กิวเมนต์ถัดไป ถ้าเป็นเท็จจะข้ามอาร์กิวเมนต์ท่ีสองและข้ามไปทาอาร์กิวเมนต์ท่ีสามทันที ดังตัวอย่าง ตอ่ ไปนี้ ตัวอยา่ งที่ 1 =IF(อายุ>=18,”มสี ทิ ธิเลอื กต้ัง”,”ยังไมม่ สี ทิ ธเิ ลือกตั้ง”) การประมวลผล: พบช่ือฟังก์ชัน IF แรก พจิ ารณาอายวุ ่ามากกวา่ หรอื เท่ากบั 18 จรงิ หรือไม่ ถา้ เป็นจริงจะส่งกลับข้อความ “มีสิทธิเลอื กตงั้ ” ถา้ ไมจ่ รงิ หรอื เป็นเท็จ จะส่งกลับข้อความ “ยงั ไม่มีสิทธเิ ลือกต้ัง” ถา้ อายุ 19 ตรวจสอบ 19 >= 18 เปน็ ความจริง ผลลัพธ์ มีสิทธเิ ลือกตัง้ ถ้าอายุ 18 ตรวจสอบ 18 >= 18 เป็นความจรงิ ผลลพั ธ์ มีสทิ ธเิ ลอื กต้งั ถา้ อายุ 17 ตรวจสอบ 17 >= 18 เปน็ เทจ็ ผลลพั ธ์ ยงั ไม่มีสทิ ธเิ ลอื กต้ัง ตวั อยา่ งท่ี 2 =IF(เลขท่ี>=100,”หอ้ งสอบท่ี 3”, IF(เลขท>ี่ =50,”หอ้ งสอบท่ี 2”,”หอ้ งสอบท่ี 1”)) การประมวลผล: พบชอ่ื ฟงั ก์ชนั IF แรก พิจารณาเลขทีว่ ่ามากกวา่ หรอื เท่ากับ 100 จรงิ หรอื ไม่ ถา้ เปน็ จรงิ จะสง่ กลับข้อความ “ห้องสอบท่ี 3” ถา้ ไมจ่ รงิ หรอื เปน็ เทจ็ หรอื เลขท่ีดงั กลา่ วมคี า่ นอ้ ยกวา่ 100 เทา่ น้ัน จากน้นั ในส่วนอารก์ ิวเมนตท์ ส่ี ามสาหรับเป็นเทจ็ พบช่อื ฟงั ก์ชัน IF ท่สี อง พจิ ารณาเลขทว่ี า่ มากกวา่ หรอื เท่ากับ 50 จรงิ หรือไม่ ถ้าเปน็ จรงิ จะสง่ กลับขอ้ ความ “หอ้ งสอบท่ี 2” ถ้าเป็นเท็จหมายถงึ เลขทนี่ ้อยกวา่ 50 จะส่งกลับข้อความ “หอ้ งสอบท่ี 1” ถา้ เลขที่ 11 ตรวจสอบ 11>=100 เปน็ เทจ็ ตรวจสอบ 11>= 50 เป็นเทจ็ ผลลพั ธ์ ห้องสอบที่ 1 ถ้าเลขที่ 111 ตรวจสอบ 111>=100 เป็นจรงิ ผลลัพธ์ ห้องสอบท่ี 3

148 บทที่ 7 การใช้ฟังก์ชนั IF เพ่อื การตัดสนิ ใจทางธุรกจิ ลักษณะการเขยี นฟงั กช์ ัน IF แบบนเ้ี ปน็ การสร้างจากประกาศในรปู แบบดังภาพที่ 7.2 ประกาศห้องสอบ เลขท่ี ห้องสอบ 1 - 49 หอ้ งสอบที่ 1 50 - 99 หอ้ งสอบที่ 2 100 ขนึ้ ไป หอ้ งสอบที่ 3 ภาพท่ี 7.2 ตวั อย่างประกาศห้องสอบ ดังน้ัน จะเห็นได้ว่าฟังก์ชัน IF เป็นการสร้างมาจาก กฎทางธุรกิจ (Business rules) ที่แต่ละองค์กรมี ข้อกาหนดในการดาเนินงานตา่ งกันไป ผู้ใช้จงึ ควรสามารถนาฟังก์ชนั IF ไปใช้ได้ในสถานการณ์ท่แี ตกต่างกัน 7.3.2 การแปลงกฎทางธรุ กจิ ใหเ้ ป็นฟังกช์ ัน IF ฟงั กช์ นั IF มโี ครงสรา้ ง ดงั ตอ่ ไปน้ี IF(logical_test,value_if_true,value_if_false) หมายถึง IF( นิพจน์ตรวจสอบเงื่อนไข,ค่าหรือการคานวณเมื่อนิพจน์เป็นจริง,ค่าหรือการ คานวณเม่ือนพิ จนเ์ ปน็ เท็จ) การแปลงกฏทางธรุ กจิ ใหเ้ ปน็ ฟงั กช์ นั IF มขี ้ันตอน ดงั นี้ 1) พิจารณาผลลัพธ์ว่ามที างเลือกทต่ี อ้ งการก่ีทางเลือก 2) พิจารณาเกณฑท์ ่ใี ช้แบง่ ค่า หรอื ข้อมลู ไดเ้ ป็น 2 ทางเลือกเทา่ นัน้ เช่น สถานะหอ้ ง ถา้ ผลลพั ธ์มมี ากกว่า 2 ทางเลอื ก ให้แบ่งเปน็ 2 ทางเลอื กก่อนแล้วจงึ นาผลลพั ธ์ทางเลือก ใดทางเลอื กหน่ึงมาทางการแบง่ ทางเลือกตามข้นั ตอนท่ี 2) อีกครัง้ 3) เม่ือได้เกณฑ์แล้ว นามาเขียนให้อยู่ในรูปนิพจน์ โดยใช้ตัวดาเนินการเปรียบเทียบ ได้แก่ =, <>, >. >=, <, หรือ <= นิพจน์ท่ีสร้างต้องได้ผลลัพธ์เชงิ ตรรกะ คือ จริง (True) หรือ เท็จ (False) เท่านั้น เช่น A1 = “ว่าง”, B1 < 20, C1 <>”” เป็นต้น โดยนิพจน์ท่ีสร้าง ในขน้ั ตอนน้ี จะใสเ่ ปน็ อาร์กิวเมนต์ logical_test 4) จากนั้นพิจารณาเม่ือเง่ือนไขในนิพจน์เป็นจริง ต้องการผลลัพธ์เช่นใด เช่น ข้อความ ตัวอักษร หรือ ตัวเลข หรือ สูตรคานวณ โดยผลลัพธ์ที่สร้างในข้ันตอนน้ี จะใส่เป็น อารก์ วิ เมนต์ value_if_true 5) จากน้ันพิจารณาเมื่อเง่ือนไขในนิพจน์เป็นจริง ต้องการผลลัพธ์เช่นใด เช่น ข้อความ ตัวอักษร หรือ ตัวเลข หรือ สูตรคานวณ โดยผลลัพธ์ท่ีสร้างในข้ันตอนน้ี จะใส่เป็น อาร์กิวเมนต์ value_if_false ตัวอยา่ งของการแปลงกฏทางธูรกิจเปน็ ฟงั ก์ชัน IF มีดงั ตอ่ ไปนี้

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยกุ ตใ์ ช้ฟงั ก์ชนั Microsoft Excel ในงานธรุ กจิ 149 ตวั อยา่ งท่ี 3 ถ้าไดค้ ะแนนต้งั แต่ 50 คะแนนขึน้ ไป สอบ\"ผ่าน\" ถ้านอ้ ยกว่า สอบ \"ตก\" 1) จานวนทางเลอื ก 2 ทางเลอื ก สอบผา่ น หรอื สอบตก 2) เกณฑ์ คะแนนต้งั แต่ 50 คะแนน 3) นพิ จน์เกณฑ์ คะแนน >=50 4) คา่ เมอ่ื นิพจน์เป็นจรงิ แสดงคาว่า “ผ่าน” สาหรับคนทค่ี ะแนนมากกวา่ หรอื เทา่ กับ 50 5) ค่าเมอ่ื นพิ จนเ์ ป็นเท็จ แสดงคาว่า “ตก” สาหรบั คนท่ีคะแนนนอ้ ยกวา่ 50 สตู รทไี่ ด้ =IF(คะแนน >= 50, “ผา่ น”, “ตก”) สังเกตวา่ เนือ่ งจากมี 2 ทางเลือกจงึ สามารถกลับเคร่ืองหมายตัวดาเนนิ การเปรียบเทยี บเป็นตรงข้าม และสลับค่าเม่อื เป็นจรงิ กับค่าเม่อื เป็นเท็จ ฟังก์ชนั IF จะยังคงให้ผลลัพธเ์ ชน่ เดียวกัน สตู รทีไ่ ด้ =IF(คะแนน < 50, “ตก”, “ผา่ น”) ตวั อย่างท่ี 4 ถ้ามีเกรดเฉล่ียไม่ถงึ 2.00 \"ไม่จบ\" ถ้าเกิน \"จบ\" การศกึ ษา 1) จานวนทางเลือก 2 ทางเลือก 2) เกณฑ์ เกรดเฉลย่ี น้อยกว่า 2.00 3) นิพจน์เกณฑ์ เกรดเฉลย่ี < 2 4) ค่าเมอื่ นพิ จน์เป็นจรงิ แสดงขอ้ ความ “ไม่จบ” สาหรับคนที่เกรดเฉลย่ี น้อยกว่า 2.00 5) คา่ เมอื่ นิพจน์เปน็ เท็จ แสดงขอ้ ความ “จบ” สาหรบั คนที่เกรดเฉลย่ี มากกว่าเท่ากับ 2.00 สูตรท่ีได้ =IF(เกรดเฉล่ีย < 2,“ไมจ่ บ”, “จบ”) หรอื =IF(เกรดเฉลีย่ >= 2,“จบ”, “ไมจ่ บ”) ตัวอยา่ งที่ 5 ถ้าวนั น้ีเปน็ วัน \"อาทิตย\"์ แสดงวา่ เปน็ \"วันหยดุ \" ถ้าไม่ใช่แสดงว่าเป็น \"วันทางาน\" 1) จานวนทางเลอื ก 2 ทางเลอื ก 2) เกณฑ์ วนั นี้ คือ วันอาทิตย์ 3) นพิ จน์เกณฑ์ วนั นี้=“อาทิตย”์ 4) ค่าเมอ่ื นพิ จนเ์ ป็นจรงิ แสดงข้อความ “วันหยดุ ” สาหรบั วนั น้ที ่ีเทา่ กับอาทิตย์ 5) ค่าเมื่อนิพจน์เปน็ เท็จ แสดงข้อความ “วนั ทางาน” สาหรับวนั นี้ทเ่ี ป็นข้อความอืน่ สตู รทไ่ี ด้ =IF(วนั น=ี้ “อาทิตย”์ ,“วันหยดุ ”,“วันทางาน”) หรือ =IF(วันน้ี<>“อาทิตย์”,“วนั ทางาน” ,“วันหยดุ ”) ตวั อย่างท่ี 6 ถ้าไดเ้ กิดท\"่ี ประเทศไทย\" เป็น \"คนไทย\" ถ้าเป็นประเทศอื่นๆ เป็น \"คนตา่ งชาต\"ิ 1) จานวนทางเลือก 2 ทางเลือก 2) เกณฑ์ ประเทศทีเ่ กดิ คอื ประเทศไทย 3) นิพจน์เกณฑ์ ประเทศ=“ประเทศไทย”

150 บทที่ 7 การใช้ฟังกช์ ัน IF เพอื่ การตดั สนิ ใจทางธุรกจิ 4) ค่าเมอ่ื นพิ จน์เป็นจรงิ แสดงข้อความ “คนไทย” สาหรบั ข้อความประเทศเทา่ กบั ประเทศไทย 5) คา่ เมือ่ นิพจน์เปน็ เทจ็ แสดงขอ้ ความ “คนต่างชาติ” สาหรับข้อความอื่นๆทไ่ี ม่ใช่ประเทศไทย สูตรทไี่ ด้ =IF(ประเทศ=“ประเทศไทย”,“คนไทย”,“คนตา่ งชาติ”) หรือ =IF(ประเทศ<>“ประเทศไทย”,“คนตา่ งชาติ” ,“คนไทย”) ตวั อยา่ งท่ี 7 ถ้าจานวนคนไมเ่ กนิ 12 คน เดนิ ทางด้วย \"รถต้\"ู ถา้ เกิน เดินทางดว้ ย \"รถบสั \" 1) จานวนทางเลอื ก 2 ทางเลือก 2) เกณฑ์ จานวนคนไมเ่ กิน 12 คน 3) นิพจนเ์ กณฑ์ จานวนคน <= 12 4) ค่าเมอ่ื นิพจนเ์ ป็นจรงิ แสดงขอ้ ความ “รถต”ู้ สาหรบั จานวนคนน้อยกวา่ เท่ากบั 12 คน 5) คา่ เมื่อนพิ จนเ์ ป็นเทจ็ แสดงขอ้ ความ “รถบสั ” สาหรับจานวนคนมากกว่า 12 คน สตู รทีไ่ ด้ =IF(จานวนคน <= 12,“รถต”ู้ ,“รถบัส”) หรือ =IF(จานวนคน > 12,“รถบสั ” ,“รถต”ู้ ) ตวั อย่างที่ 8 ถ้าซื้อเกนิ 500 บาท จะ \"ได้ส่วนลด\" ถ้าไม่เกนิ จะ \"ไมไ่ ดส้ ่วนลด\" 1) จานวนทางเลือก 2 ทางเลือก 2) เกณฑ์ ยอดซอ้ื เกิน 500 บาท 3) นพิ จน์เกณฑ์ ยอดซ้ือ > 500 4) คา่ เม่ือนพิ จนเ์ ป็นจริง แสดงขอ้ ความ “ได้สว่ นลด” สาหรบั ยอดซ้ือทีม่ ากกวา่ 500 บาท 5) ค่าเมอื่ นพิ จน์เป็นเทจ็ แสดงขอ้ ความ “ไมไ่ ด้ส่วนลด” สาหรบั ยอดซอ้ื น้อยกว่าเทา่ กบั 500 บาท สูตรทไี่ ด้ =IF(ยอดซื้อ>500,“ไดส้ ว่ นลด”,“ไม่ไดส้ ่วนลด”) หรือ =IF(ยอดซือ้ <=500,“ไมไ่ ด้ส่วนลด” ,“ได้สว่ นลด”) ตวั อยา่ งท่ี 9 ถา้ ซอื้ เกนิ 500 บาท จะไดส้ ว่ นลด = ยอดซ้อื x 10% ถ้าไมถ่ งึ จะไดส้ ว่ นลด = ยอดซ้อื x5% 1) จานวนทางเลอื ก 2 ทางเลือก 2) เกณฑ์ ยอดซ้อื เกิน 500 บาท 3) นิพจนเ์ กณฑ์ ยอดซ้อื > 500 4) คา่ เมือ่ นิพจน์เป็นจรงิ แสดงผลลัพธ์จากการคานวณ ยอดซือ้ x 10% 5) คา่ เมื่อนพิ จน์เป็นเทจ็ แสดงผลลัพธจ์ ากการคานวณ ยอดซอื้ x 5% สูตรท่ไี ด้ =IF(ยอดซอื้ >500,ยอดซอื้ *10%,ยอดซอื้ *5%) หรือ =IF(ยอดซอ้ื <=500,ยอดซ้ือ*5%,ยอดซ้ือ*10%)

ดร.สาวิตรี บญุ มี ส่วนท่ี 3 การประยุกตใ์ ช้ฟังกช์ ัน Microsoft Excel ในงานธรุ กจิ 151 ตวั อย่างท่ี 10 ถ้าสูงเกิน 100 ซม. ค่าโดยสารจ่ายเต็ม 40 บาท ถ้าไม่ถึงได้ลดคร่ึงราคา (ราคาเต็ม x 50%) 1) จานวนทางเลอื ก 2 ทางเลือก 2) เกณฑ์ ความสูงเกิน 100 ซม. 3) นพิ จนเ์ กณฑ์ ความสงู >100 4) ค่าเม่อื นพิ จน์เป็นจริง แสดงค่า 40 5) ค่าเมื่อนิพจน์เป็นเทจ็ แสดงผลลัพธจ์ ากการคานวณ 40x50% สูตรทไ่ี ด้ =IF(ความสงู >100,40,40*0.5) หรือ =IF(ความสงู <=100,40*0,5,40) ตัวอย่างท่ี 11 ถ้าซ้ือ 10 ช้ินขึ้นไป คิดราคาชิ้นละ 8 บาท ถ้าไม่ถึง 10 ช้ิน คิดราคาชิ้นละ 10 บาท (คานวณหาราคารวม) 1) จานวนทางเลอื ก 2 ทางเลอื ก 2) เกณฑ์ จานวน 10 ชนิ้ ขนึ้ ไป 3) นพิ จน์เกณฑ์ จานวน >= 10 4) ค่าเมื่อนิพจนเ์ ป็นจริง แสดงผลลพั ธ์จากการคานวณ จานวน x 8 5) คา่ เม่ือนิพจนเ์ ป็นเทจ็ แสดงผลลัพธ์จากการคานวณ จานวน x 10 สตู รทีไ่ ด้ =IF(จานวน>=10,จานวน*8,จานวน*10) หรือ =IF(จานวน<10,จานวน*10,จานวน*8) 7.3.3 การสรา้ งฟงั ก์ชนั IF ใน Excel การสร้างฟังก์ชัน IF ใน Excel นั้นมีลักษณะเดียวกับฟังก์ชันท่ีเขียนข้ึนจากการแปลงกฏทาง ธรุ กจิ เปน็ ฟังกช์ นั IF เพยี งแค่แทนท่ีค่าที่ใชเ้ ปรียบเทียบกับเกณฑ์ในเง่ือนไขดว้ ยชื่อเซลล์ เชน่ จากตวั อย่าง ที่ 3 =IF(คะแนน>=50,“ผา่ น”,“ตก”) ถา้ ค่าคะแนนอยูใ่ นเซลล์ B2 สูตรใน Excel จะมีลักษณะดงั นี้ =IF(B2>=50,“ผา่ น”,“ตก”) หรอื ถ้าใชว้ ธิ ีการสร้างจากหน้าต่างฟงั ก์ชัน จะมีลกั ษณะการใสอ่ าร์กิวเมนต์ ดงั ภาพท่ี 7.3

152 บทท่ี 7 การใชฟ้ ังก์ชนั IF เพอื่ การตัดสนิ ใจทางธรุ กิจ ภาพที่ 7.3 ตัวอย่างการสรา้ งฟงั ก์ชนั IF ดว้ ยหนา้ ต่างฟังกช์ นั 1) จากไฟล์ ch7if.xlsx เปิดแผน่ งาน “IF” ภาพท่ี 7.4 แผน่ งาน “IF” 2) จากตัวอยา่ งทผ่ี ่านมา สามารถสรา้ งสูตรด้วยฟังก์ชัน IF เพอ่ื หาผลัพธไ์ ด้ดงั นี้ สูตร เซลล์ C2 =IF(B2>=50,\"ผา่ น\",\"ตก\") สตู ร เซลล์ C3 =IF(B3<2,\"ไมจ่ บ\",\"จบ\") สูตร เซลล์ C4 =IF(B4=\"อาทติ ย์\",\"วนั หยดุ \",\"วันทางาน\") สตู ร เซลล์ C5 =IF(B5=\"ประเทศไทย\",\"คนไทย\",\"คนต่างชาติ\") สูตร เซลล์ C6 =IF(B6<=12,\"รถตู\"้ ,\"รถบสั \") สูตร เซลล์ C7 =IF(B7>500,\"ได้ส่วนลด\",\"ไมไ่ ด้ส่วนลด\") สูตร เซลล์ C8 =IF(B8>500,B8*0.1,B8*0.05) สตู ร เซลล์ C9 =IF(B9>100,B9,B9*0.5) สูตร เซลล์ C10 =IF(B10>=10,B10*8,B10*10)

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยุกต์ใชฟ้ งั ก์ชัน Microsoft Excel ในงานธุรกิจ 153 7.4 การใชฟ้ ังกช์ นั ซ้อนฟงั ก์ชนั (Nested functions) จากตัวอย่างท่ีผ่านมาจะเห็นว่าฟังก์ชัน IF สามารถใช้ตรวจสอบเงื่อนไขเม่ือมีทางเลือกสองทาง คือ ทางเลือกเมื่อเป็นจริง และทางเลือกเมื่อเป็นเท็จ แต่ในการดาเนินธุรกิจโดยปกตินั้นมักจะมีทางเลือก มากกว่าสองทางเลอื กเสมอ ทาให้การใช้ฟังก์ชัน IF แบบธรรมดาเพียงชน้ั เดียวไม่สามารถครอบคลุมได้ทกุ ทางเลือก ดังน้ัน เม่ือมีทางเลือกมากกว่าสองทางเลือก จาเป็นต้องใช้ IF มากกว่า 1 ช้ัน เช่น ถ้ามี 3 ทางเลือก ต้องใช้ IF สองช้ัน โดยวธิ กี ารใช้จะเปน็ การใชฟ้ งั ก์ชนั ซ้อนฟังกช์ นั เช่น =IF(A1 >= 10000, “HD”,IF(A1 >= 5000 ,”D”,”C”)) ซึ่งอธิบายได้ว่า ถ้า ค่าในเซลล์ A1 มีค่าต้ังแต่ 10000 ขึ้นไปให้แสดงผลลัพธ์เป็นข้อความว่า HD แต่ ถ้าน้อยกว่า 10000 หรือเป็นเท็จให้ทาการตรวจสอบอีกเงื่อนไข คือ ถ้าค่าในเซลล์ A1 มีค่าต้ังแต่ 5000 ข้ึนไป (แต่น้อยกว่า 10000 เน่ืองจากไม่ผ่านเง่ือนไขแรก) ให้แสดงผลลพั ธ์เป็นข้อความวา่ D ถ้าค่ายังน้อย กว่า5000 อีก หรือเปน็ เท็จ ใหแ้ สดงผลลพั ธเ์ ปน็ ขอ้ ความว่า C ซ่ึงในกรณีในการใช้ IF ซ้อนกันหลายชัน้ นั้น อาจทาให้เกิดความผิดพลาด หรือสับในในการแก้ไขสูตร ควรลองพจิ ารณาฟงั กช์ นั อื่นที่สามารถใช้ทดแทนได้ เชน่ VLOOKUP ซง่ึ จะกลา่ วถึงในบทถัดไป นอกเหนือจากการใช้ฟังก์ชันซ้อนฟังก์ชัน เช่น IF ซ้อน IF เพื่อตรวจสอบทางเลือกแล้ว อาจจะเป็น การใชฟ้ งั ก์ชันอนื่ ๆ ซ้อนกันเพ่อื ร่วมกนั หาผลลัพธท์ ต่ี อ้ งการกไ็ ด้เชน่ กัน เชน่ =IF(A1 >=10000, SUM(B1:F1), COUNT(B1:F1)) ซ่ึงอธิบายได้ว่า ถ้า ค่าในเซลล์ A1 มีค่าตั้งแต่ 10000 ขึ้นไปให้คานวณหาผลรวมของค่าในเซลล์ B1:F1 แต่ถ้าน้อยกวา่ 10000 หรอื เปน็ เท็จให้ทาการคานวณนับจานวนของค่าในเซลล์ B1:F1 ตัวอยา่ งท่ี 12 ถ้าอายุไมถ่ งึ 5 ขวบเสียค่าโดยสาร 20 บาท ถ้าอายุไมเ่ กิน 60 ปี เสยี ค่าโดยสาร 40 บาท ถา้ เกิน 20 บาท 1) จานวนทางเลือกทัง้ หมด 3 ทางเลือก (2 เกณฑ)์ 2) เกณฑ1์ อายุไมถ่ งึ 5 ขวบ 3) นิพจนเ์ กณฑ1์ อายุ<5 4) ค่าเมื่อนิพจนเ์ ปน็ จรงิ 1 แสดงค่าตัวเลข 20 สาหรบั อายุนอ้ ยกว่า 5 5) ค่าเมื่อนิพจน์เปน็ เทจ็ 1 เหลือ 2 ทางเลอื ก สาหรับอายุมากกว่าเท่ากบั 5 เกณฑ2์ อายุไมเ่ กนิ 60 นพิ จน์เกณฑ2์ อายุ<60 ค่าเมือ่ นิพจน์เป็นจริง2 แสดงตวั เลข 40 สาหรบั อายมุ ากกว่าเท่ากบั 5 แต่นอ้ ยกวา่ 60 ค่าเม่ือนพิ จน์เป็นเทจ็ 2 แสดงตวั เลข 20 สาหรบั อายมุ ากกวา่ เทา่ กบั 60

154 บทท่ี 7 การใช้ฟังกช์ นั IF เพ่อื การตดั สินใจทางธรุ กจิ สูตรที่ได้ =IF(อายุ<5,20,IF(อายุ<60,40,20)) หรือ =IF(อายุ>=60,20,IF(อายุ>=5,40,20)) ตัวอย่างที่ 13 ถ้าจานวนคนตั้งแต่ 13 คนขึ้นไป เดินทางด้วย\"รถบัส\" ถ้าไม่ถึง 13 แต่มากกว่า 4 คน เดนิ ทางดว้ ยรถตู้ นอกนัน้ เดนิ ทางดว้ ย\"รถเกง๋ \" 1) จานวนทางเลือกทัง้ หมด 3 ทางเลือก (2 เกณฑ์) 2) เกณฑ1์ จานวนคนตั้งแต่ 13 คนขึ้นไป 3) นิพจนเ์ กณฑ1์ จานวนคน>=13 4) ค่าเมื่อนิพจน์เป็นจริง1 แสดงขอ้ ความ “รถบัส” สาหรับจานวนคนมากกวา่ เทา่ กับ 13 5) ค่าเมื่อนพิ จน์เปน็ เทจ็ 1 เหลือ 2 ทางเลอื ก สาหรับจานวนคนน้อยกว่า 13 เกณฑ2์ จานวนคนมากกวา่ 4 คน นิพจนเ์ กณฑ2์ จานวนคน>4 ค่าเมื่อนิพจน์เป็นจริง2 แสดงข้อความ “รถตู้” สาหรับจานวนคนน้อย กวา่ 13 แต่มากกวา่ 4 คน ค่าเมื่อนิพจน์เป็นเท็จ2 แสดงข้อความ “รถเก๋ง” สาหรับจานวนคน น้อยกว่าเทา่ กับ 4 คน สูตรท่ไี ด้ =IF(จานวนคน>=13,“รถบสั ”,IF(จานวนคน>4,“รถต”ู้ ,“รถเก๋ง”) หรือ =IF(จานวนคน<=4,“รถเก๋ง”,IF(จานวนคน<13,“รถต”ู้ ,“รถบสั ”) ตัวอยา่ งท่ี 14 ถ้าซอื้ เกิน 5000 บาท จะได้ส่วนลด = ยอดซอ้ื x 20%ซอื้ เกนิ 1000 บาท จะไดส้ ่วนลด = ยอดซ้อื x 10% ถ้าไม่ถึงจะไดส้ ่วนลด = ยอดซอื้ x5% 1) จานวนทางเลือกทงั้ หมด 3 ทางเลือก (2 เกณฑ)์ 2) เกณฑ1์ ซือ้ เกิน 5000 บาท 3) นิพจน์เกณฑ1์ ยอดซอื้ >5000 4) คา่ เมอื่ นิพจน์เปน็ จรงิ 1 แสดงผลลพั ธ์การคานวณ ยอดซอ้ื *20% สาหรบั ยอดซอ้ื มากกวา่ 5000 5) ค่าเมื่อนพิ จน์เปน็ เท็จ1 เหลอื 2 ทางเลือก สาหรับยอดซ้ือน้อยกวา่ เทา่ กับ 5000 เกณฑ2์ ยอดซื้อเกิน 1000 นพิ จนเ์ กณฑ2์ ยอดซื้อ>1000 ค่าเมื่อนิพจน์เป็นจริง2 แสดงผลลัพธ์การคานวณ ยอดซ้ือ*10% สาหรบั ยอดซ้ือน้อยกวา่ เทา่ กับ 5000 แต่มากกวา่ 1000 ค่าเมอ่ื นิพจน์เปน็ เทจ็ 2 แสดงผลลพั ธก์ ารคานวณ ยอดซือ้ *5% สาหรบั ยอดซอื้ น้อยกวา่ เทา่ กบั 1000 สตู รที่ได้ =IF(ยอดซ้ือ>5000,ยอดซือ้ *20%,IF(ยอดซอ้ื >1000,ยอดซื้อ*20%,ยอดซ้ือ*20%))

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยุกต์ใชฟ้ งั กช์ นั Microsoft Excel ในงานธุรกิจ 155 หรอื =IF(ยอดซ้อื <=1000,ยอดซอ้ื *5%,IF(ยอดซอ้ื <=5000,ยอดซื้อ*10%,ยอดซอ้ื *50%)) 7.4.1 การสรา้ งฟงั ก์ชนั IF ซ้อน IF ใน Excel 1) เปิดแผ่นงาน “IF” 2) จากตัวอยา่ งทผ่ี า่ นมาตามภาพท่ี 7.5 สร้างสตู รด้วยฟงั ก์ชัน IF เพ่อื หาคาตอบ สตู ร เซลล์ C14 =IF(B14<A16,B16,IF(B14<A17,B17,B18)) สตู ร เซลล์ C21 =IF(B21>=A23,B23,IF(B21>A24,B24,B25)) สูตร เซลล์ C29 =IF(B29>A31,B31,IF(B29>A32,B32,B33))*B29 หรอื =IF(B29>A31,B31*B29,IF(B29>A32,B32*B29,B33*B29)) สูตร เซลล์ D29 =B29-C29 ภาพที่ 7.5 แผน่ งาน IF ฝึกฟังก์ชันซ้อนฟงั ก์ชัน

156 บทท่ี 7 การใช้ฟังก์ชนั IF เพ่ือการตดั สินใจทางธุรกิจ 7.4.2 ตวั อย่างการประยกุ ต์ใช้ฟังก์ชัน IF ในการหาผลการเรียน 1) เปิดแผ่นงาน คะแนน ในชีทคะแนนจงสร้างสูตรคานวณหาผลการเรียน และสูตรต้องสามารถคัดลอก ไปยังเซลลอ์ นื่ ได้ ภาพท่ี 7.6 แผ่นงาน “คะแนน” โดยตรวจสอบคะแนนต้ังแต่ 90% ถา้ เปน็ จริงไดร้ ับเกรด “ผา่ นยอดเยี่ยม” ถา้ เปน็ เท็จ ใหต้ รวจสอบ ว่ามากกว่าหรอื เท่ากบั 50% หรือไม่ ถ้าเปน็ จรงิ ได้รบั เกรด “ผา่ น” ถา้ เป็นเท็จไดร้ บั ผลการเรียน “ตก” 1) จานวนทางเลือกทั้งหมด 3 ทางเลอื ก (2 เกณฑ์) 2) เกณฑ1์ คะแนน(E3) ต้ังแต่ 90% (H7) 3) นิพจน์เกณฑ1์ คะแนน(E3)>=90 (H7) 4) ค่าเม่อื นิพจน์เปน็ จริง1 แสดงข้อความ“ผ่านยอดเยย่ี ม”(I7) สาหรับคะแนนมากกว่าเทา่ กบั 90 5) คา่ เมื่อนิพจนเ์ ปน็ เท็จ1 เหลอื 2 ทางเลือก สาหรับคะแนนน้อยกว่า 90 เกณฑ2์ คะแนน(E3) ตงั้ แต่ 50% (H6) นพิ จนเ์ กณฑ2์ คะแนน(E3)>=50% (H6) ค่าเมื่อนิพจน์เป็นจริง2 แสดงข้อความ “ผ่าน” (I6) สาหรับคะแนน นอ้ ยกว่า 90 แตม่ ากกว่าเท่ากบั 50 คา่ เมือ่ นพิ จนเ์ ป็นเท็จ2 แสดงข้อความ “ตก” (I5) สาหรบั คะแนนน้อย กว่า 50 สูตร เซลล์ F3 =IF(E3>=$H$7,$I$7,IF(E3>=$H$6,$I$6,$I$5)) ในตวั อย่างนต้ี ้องมีการยึดเซลลเ์ น่อื งจากตารางเกณฑอ์ ยู่ในชว่ งเซลล์ H5:I7 เม่ือมีการคดั ลอกสูตรการ อา้ งองิ เซลล์มาตารางนี้จงึ ไม่ควรเปลีย่ นแปลงหรือเคล่ือนที่

ดร.สาวิตรี บญุ มี สว่ นที่ 3 การประยุกตใ์ ช้ฟังกช์ นั Microsoft Excel ในงานธรุ กิจ 157 7.5 การประยกุ ตใ์ ช้ฟังก์ชนั IF ในการบริหารสินค้าคงเหลือ บริหารสินค้าคงเหลือ (Inventory management) หมายถึง การควบคุมสินค้าคงเหลือให้เกิด ประสิทธิภาพ (ฐาปนา ฉ่ินไพศาล, 2560) เนื่องจากหากเก็บรักษาสินค้าคงเหลือไว้น้อยเกินกว่าความ ตอ้ งการของลูกค้าอาจจะทาให้เกดิ ต้นทุนคา่ เสยี โอกาสในการขายสนิ ค้า และยังต้องเสยี ค่าใช้จ่ายในการสั่ง สินค้า (Ordering cost) บ่อยคร้ัง เช่น ค่าส่งสินค้า ที่เมื่อสินค้าหมดเร็ว จาต้องส่ังสินค้าบ่อยครั้งขึ้นทาให้ ค่าใช้จ่ายในการส่ังสินค้ามากข้ึนตามจานวนคร้ังท่ีสั่ง ในทางกลับกัน ถ้าเก็บสินค้าคงเหลือมากเกินไป ก็ อาจทาให้เกิดต้นทุนในการเก็บรักษา (Carrying cost) มากข้ึน เช่น ค่าไฟฟ้า ค่าเช่าโกดังเก็บสินค้า เป็น ต้น ดังนั้น การบริหารสินค้าคงเหลือจะต้องคานึงถึงปริมาณการส่ังซ้ือที่ประหยัดที่สุด (Economic order quantity) และจดุ สัง่ ซอื้ (Reorder point) ปริมาณการส่ังซ้ือที่ประหยัดที่สุด (Economic order quantity) หมายถึง จานวนสินค้าที่ทา ให้ผลรวมของค่าใช้จ่ายในการเก็บรักษาและคา่ ใช้จา่ ยในการส่งั ซื้อตา่ ท่ีสุด สตู ร EOQ =√2SCO โดย S คอื จานวนสนิ คา้ ทต่ี อ้ งการ หรอื ประมาณการยอดขาย/ปี O คอื ค่าใชจ้ ่ายในการสงั่ ซื้อต่อคร้งั C คอื ค่าใช้จา่ ยในการเกบ็ รักษาตอ่ หนว่ ย ตัวอย่าง สินค้ารหัส LOT1101011 มีค่าใช้จ่ายในการเก็บรักษา 29 บาท/หน่วย ค่าใช้จ่ายในการ สัง่ ซอื้ ต่อครงั้ 627 บาท ประมาณการยอดขาย 19,065 หน่วย/ปี ปริมาณการสง่ั ซ้ือที่ประหยัดทส่ี ุด คอื EOQ =√2x1902695x627 = 907.96 หนว่ ย จดุ ส่ังซอื้ (Reorder point) คือ จานวนสินค้าคงเหลือท่ธี ุรกจิ ควรดาเนนิ การสั่งซ้อื หรอื อีกนัยหน่ึง คือจานวนสินค้าที่ต้องใช้ในช่วงที่รอสินค้าดาเนินการส่งจากผู้ขายมายังธุรกิจ โดยประมาณการจาก ระยะเวลานาสง่ (lead time) และจานวนสินคา้ ทตี่ ้องใช้ต่อวัน สตู ร Reorder Point = ยอดขายเฉล่ียต่อวนั X ระยะเวลานาสง่ ยอดขายเฉลีย่ ต่อวัน คือ ประมาณการยอดขาย/ปี หารด้วย จานวนวนั ในหนง่ึ ปี ระยะเวลานาสง่ คือ จานวนวนั ท่ีผู้ขายนาส่งสนิ คา้ จนมาถงึ มือธุรกิจ ตัวอย่าง สินค้ารหัส LOT1101011 มีประมาณการยอดขาย 19,065 หน่วย/ปี และระยะเวลา นาสง่ 2 วัน ถ้า 1 ปี มี 365 วัน จุดสัง่ ซอ้ื คือ = 104.47 หนว่ ย จุดสั่งซ้ือ = 19065 x 2 365

158 บทท่ี 7 การใช้ฟังกช์ ัน IF เพอ่ื การตัดสินใจทางธุรกจิ 1) เปิดแผน่ งาน EOQ ภาพที่ 7.7 แผ่นงาน EOQ 2) สรา้ งสูตรเพื่อคานวณตามสตู รต่อไปน้ี สูตร เซลล์ G3 =SQRT(2*E3*D3/C3) EOQ =√2SCO Reorder Point = ยอดขายเฉล่ียตอ่ วัน X ระยะเวลานาส่ง สตู ร เซลล์ H3 =E3/365*F3 สถานะสนิ คา้ = ถ้าปรมิ าณสินคา้ คงเหลือต่ากว่าจุดสั่งซื้อ ให้แสดงสถานะ “ถงึ จุดสั่งซ้ือ” ถา้ เป็นเท็จ ให้แสดงสถานะ “ปกต”ิ สูตร เซลล์ I3 =IF(B3<H3,\"ถงึ จุดสั่งซอื้ \",\"ปกต\"ิ ) 7.6 สรุป การตัดสินใจเป็นสิ่งคู่กับการดาเนินงานทางธุรกิจ เม่ือประยุกต์ใช้ Excel เพ่ือช่วยสร้างแบบจาลอง ทางธุรกิจน้ัน การใช้ฟังก์ชัน IF จึงเป็นสิ่งที่หลีกเล่ียงไม่ได้ ฟังก์ชัน IF ประกอบด้วย 3 อาร์กิวเมนต์ คือ ส่วนตรวจสอบเงื่อนไข ส่วนแสดงผลเมื่อเงื่อนไขเป็นจริง และ ส่วนแสดงผลเมื่อเงือ่ นไขเป็นเท็จ ซึ่งในส่วน แสดงผลท้ังเป็นจริงหรือเท็จน้ันสามารถนาไปใช้ร่วมกับฟังก์ชันอ่ืน ๆ ได้อีก รวมถึงฟังก์ชัน IF ด้วยกันเอง เรียกว่าการใชฟ้ ังก์ชนั ซอ้ นฟังก์ชัน นอกจากนี้ฟังก์ชัน IF ยังเหมาะสมในการใช้รว่ มกันการยดึ เซลลด์ ้วยปุ่ม ฟังก์ชนั F4 เพือ่ ใหเ้ กิดประสิทธิภาพในการใชง้ านมากขึ้น โดยถา้ มี 2 ทางเลือกสามารถใช้IF เพยี งช้ันเดียว ถา้ มี 3 ทางเลอื กต้องใข้ IF ซ้อนกนั 2 ช้ัน ถา้ มี 8 ทางเลอื กตอ้ งใช้ IF ซอ้ นกันถึง 7 ช้ัน แต่อยา่ งไงกต็ ามถ้า มีทางเลือกมากกว่า 8 ทางเลือก Excel ไม่อนุญาตให้ซ้อนฟังก์ชันได้มากกว่า 7 ชั้น ดังนั้นในบาง สถานการณ์ฟังกช์ นั VLOOKUP สามารถใชง้ านทดแทนได้ ดังจะอธิบายในบทถดั ไป

ดร.สาวิตรี บญุ มี สว่ นท่ี 3 การประยุกตใ์ ช้ฟงั กช์ ัน Microsoft Excel ในงานธุรกิจ 159 แบบฝกึ หัดทา้ ยบทที่ 7 ตอนท่ี 1 หาผลลัพธ์จากฟังก์ชั่น IF ตอ่ ไปน้ี 1. =IF(จานวนลูกค้า>=20,”4 เคา้ เตอร์”,”2 เคา้ เตอร์”) ถ้าจานวนลกู คา้ 18 คน ผลลพั ธ์ ถ้าจานวนลูกค้า 30 คน ผลลัพธ์ 2. =IF(ความดัน<=80,”ปกต”ิ ,”ไมป่ กต”ิ ) ถา้ ความดัน 72 ผลลพั ธ์ ถ้าความดนั 100 ผลลัพธ์ 3. =IF(เงือนไข=”ขายปลกี ”,20,10) ถ้าเง่ิอนไข “ขายปลกี ” ผลลัพธ์ ถ้าเงอ่ื นไข “ขายส่ง” ผลลัพธ์ 4. =IF(จานวนลกู ค้า>=20,”4 เคา้ เตอร์”,IF(จานวนลกู ค้า>=10,”3 เคา้ เตอร์”,”2 เค้าเตอร์”)) ถ้าจานวนลูกคา้ 5 คน ผลลัพธ์ ถา้ จานวนลูกคา้ 20 คน ผลลพั ธ์ 5. =IF(เงือนไข=”ขายปลกี ”,20, IF(เงอื นไข=”ขายส่ง”,10,”เง่อื นไขไม่ถกู ต้อง”)) ถา้ เงิอ่ นไข “ขายเช่อื ” ผลลัพธ์ ถ้าเงอื่ นไข “ขายสง่ ” ผลลพั ธ์ ตอนท่ี 2 จงฝกึ เขียนฟงั กช์ ั่น IF จากเงอ่ื นไขต่อไปน้ี 1. ถา้ มนี ักกีฬาตงั้ แต่ 11 คน ข้นึ ไป สามารถ “แข่งฟุตบอลได้” “ไมล่ งแข่ง” 2. ถ้านกั ศกึ ษาสมัครเรียนนอ้ ยกว่า 20 คน จะ “ไม่เปิดสอน” ถา้ 20 คนข้นึ ไป “เปิดสอน” 3. ถา้ นักศกึ ษาลงชอื่ ไปดงู าน 20 คน ขนึ้ ไป จะ “ไปดงู าน” ถา้ ไมถ่ งึ “ยกเลกิ ดงู าน” 4. ถา้ ซ้ือสนิ ค้าต้งั แต่ 2 ชน้ิ ได้แสตมป์ 10 บาท ถา้ ซื้อไมถ่ ึง ไดแ้ สตมป์ 3 บาท 5. ถ้าซอื้ ปากกาตง้ั แต่ 2 ดา้ มข้ึนไป ลด 25% นอกนั้นไม่ลดราคา

160 บทที่ 7 การใชฟ้ ังก์ชนั IF เพื่อการตัดสินใจทางธุรกิจ 6. ถา้ ซื้อปากกา 1 ด้าม ลด 20% ซอ้ื ปากกา 2 ดา้ ม ลด 25% ซื้อปากกา 3 ด้ามข้ึนไป ลด 30% 7. ถ้าจานวนลูกค้าจองไม่เกิน 2 คน ให้พัก “ห้องคู่” ถ้าเกิน 2 คน แต่ไม่เกิน 6 คน พัก “ห้องรวม” ถ้า เกนิ 6 คน พัก “บ้าน” 8. ถ้าจองคอนเสิรต์ โซน A ราคาต๋ัว 5,000 บาท โซน B ราคาต๋วั 4,000 บาท โซน C ราคาตว๋ั 3,000 บาท โซน D ราคาตวั๋ 2,000 บาท โซน E ราคาตว๋ั 1,000 บาท อ่ืนๆ “เลอื กโซนไม่ถูกตอ้ ง” ตอนที่ 3 ฝึกการประยุกต์ใชฟ้ งั กช์ ั่น IF หาผลการเรียน จากแบบฝกึ ปฏบิ ัติการประยุกตใ์ ช้ฟังกช์ ่ัน IF หาผลการเรยี น ให้เปลี่ยนใช้เกณฑ์ผลการเรียนแบบ 8 เกรด ในกล่องดา้ นล่าง ในการหาผลการเรยี น โดยสูตรท่ีสรา้ งขึน้ ตอ้ งสามารถคดั ลอกไปยังเซลลอ์ นื่ ๆ ได้ สตู ร เซลล์ F3

บทท่ี 8 การใชฟ้ ังก์ชันในการค้นหาเพื่อจัดทาใบเสร็จรบั เงนิ ธุรกิจขนาดกลางและขนาดเล็กบางครัง้ ไม่ได้นาโปรแกรมจัดการฐานข้อมูลมาใช้จดั การข้อมูลของ องค์กรแต่ใช้ Microsoft Excel ในการจัดเก็บข้อมูลแทน ซ่ึง Excel อนุญาตให้เก็บข้อมูลได้ถึง 16,384 คอลัมน์ และ 1,048,576 ระเบียนหรือแถว เมื่อมีการเก็บข้อมูลจานวนมาก Excel จึงมีฟังก์ช่ันเพ่ือใช้ สาหรบั การค้นหาขอ้ มูลเพ่อื นามาใชใ้ ห้สะดวกย่งิ ขน้ึ ฟังก์ช่ันที่ใช้ในการค้นหาของ Excel น้ัน จะอยู่ในหมวดการค้นหาและอ้างอิง (Lookup and reference) เช่น LOOKUP, MATCH, INDEX ตามภาพที่ 8.1 แต่ทใี่ ชบ้ ่อยทีส่ ดุ คอื VLOOKUP ภาพที่ 8.1 ตัวอย่างฟังกช์ นั่ ค้นหาและอา้ งองิ การจัดทาใบเสร็จรับเงินน้ัน สามารถใช้ฟังก์ชัน VLOOKUP โดยนารหัสสินค้าไปค้นหาช่ือและ ราคาขายสินค้าจากแผ่นงาน หรือตารางที่เก็บข้อมูลสินค้าไว้อย่างเช่นภาพที่ 8.2 มาแสดง ซึ่งจะช่วย ประหยดั เวลาในการพิมพ์ข้อมลู สินค้าท้ังหมดใหม่ และการค้นหาราคาเพื่อจัดทาใบเสร็จรับเงนิ นอกจากนี้ ยังสามารถค้นหาเงื่อนไขส่วนลดตามยอดขายมาใช้ในการคานวณส่วนลดให้ลูกค้าได้อีกด้วยดังภาพท่ี 8.3 โดยลักษณะท่ีสาคัญคือ ข้อมูลในตารางจะต้องเกบ็ ตามแนวต้ัง หรือ อีกนัยหนึ่งหมายถึงต้องเป็นการค้นหา ข้อมลู จากบนลงล่างเท่าน้ัน  ในบทน้ีใช้ไฟล์ประกอบเนื้อหาเพื่อใช้ในการฝึกปฏิบัติ ชื่อ ch8Invoice.xlsx จาก CD หรือ สามารถดาวน์โหลดไฟล์ได้จากเว็บไซต์ academic.udru.ac.th/sawitree

162 บทที่ 8 การใช้ฟังก์ชนั ในการค้นหาเพือ่ จดั ทาใบเสร็จรับเงนิ ภาพที่ 8.2 ตวั อยา่ งตารางสินค้าที่ใช้ในการค้นหาข้อมลู ภาพท่ี 8.3 ตวั อยา่ งตารางเงื่อนไขสว่ นลด จากภาพที่ 8.2 ข้นั ตอนปกติของผูใ้ ช้ในการคน้ หาขอ้ มูลราคาต่อหน่วยจากตารางสนิ คา้ เป็นดงั น้ี 1) ผูใ้ ชร้ ะบรุ หัสสินคา้ ท่ตี อ้ งการหาข้อมูล เช่น T1005 ข้อมลู ที่ตอ้ งการคอื ราคาต่อหน่วย 2) ผู้ใช้ค้นหาคอลัมนท์ ่มี ีรหสั สินค้าในตาราง ซง่ึ ในภาพตัวอยา่ งคอื คอลมั น์แรกจากซ้ายมือ หรือคอลมั น์ A 3) ผู้ใช้ไล่หารหัสสินค้าจากคอลัมน์ดังกล่าวจากบนลงล่างจนกระท่ังเจอข้อมูลที่ต้องการ ในตัวอย่างคือ แถวท่ี 6 (รวมหัวตารางในแถวที่ 1) 4) ผู้ใชห้ าคอลมั น์ที่มีราคาต่อหน่วย ซ่งึ อยใู่ นคอลัมน์ C หรอื คอลมั น์ท่ี 3 นบั จากคอลัมนท์ ี่มรี หัสสินค้า 5) จากนน้ั ผใู้ ช้ไล่หาราคาต่อหน่วยในแถวที่ตรงกบั รหสั สนิ ค้าท่ตี อ้ งการคือแถวที่ 6 ได้คาตอบคือ 15,900 จากข้ันตอนดังกล่าวจะมีความสอดคล้องกับการทางานของฟังก์ชัน VLOOKUP ตามท่ีจะอธิบายใน หัวขอ้ ต่อไป

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยุกต์ใช้ฟงั ก์ชัน Microsoft Excel ในงานธรุ กจิ 163 8.1 รูปแบบการค้นหาของ VLOOKUP ซงึ่ จากตัวอย่างทย่ี กมาน้ันสามารถแบ่งวธิ ีการคน้ หาของ VLOOKUP ได้เปน็ 2 วิธี คือ การค้นหาแบบ ตรงกันทกุ ประการ และการค้นหาแบบค่าใกล้เคียง วิธที ี่ 1 การค้นหาแบบตรงกันทุกประการ เป็นการคน้ หาข้อมูลจากตารางท่ีเฉพาะตรงกับค่าที่นาไป ค้นหาเท่าน้ัน ต้องตรงกันทุกอักขระ เช่น ถ้าต้องการนารหัสสินค้า T1005 ไปค้นหาในคอลัมน์แรกของ ตารางตามภาพท่ี 8.2 หากมีการเว้นวรรคด้านหน้าหรือด้านหลังคาที่ค้นหา อย่างเช่น “ T1005” หรือ “T1005 ” จะทาใหค้ ้นหาไม่พบ และฟงั ก์ชนั จะสง่ ค่า Error เป็น #N/A กลบั มาใหผ้ ู้ใช้ ซงึ่ หมายถึง ความ ผิดพลาดจากการไม่พบข้อมูล วิธีการน้ีน้ัน VLOOKUP จะทาการค้นหาจากแถวแรกไปทีละแถวจนกว่าจะ พบข้อมูลที่ตรงกันทุกประการ ซึ่งสามารถเทียบได้กับข้ันตอนที่ 3) ของขั้นตอนปกติของผู้ใช้ในการค้นหา ข้อมูลในหัวข้อก่อนหน้า ซ่ึงจะมีรูปแบบวิธีการเขียนสูตร ดังน้ี โดยสมมติให้เซลล์ E1 มีรหัสสินค้าคือ T1005 ท่ีต้องการคน้ หา =VLOOKUP(E1,$A$2:$C$10,3,FALSE) สตู รท่ี 9-1 ภาพที่ 8.4 การคน้ หาดว้ ย VLOOKUP แบบตรงกันทกุ ประการ วิธีท่ี 2 การค้นหาแบบค่าใกล้เคียง เป็นการค้นหาข้อมูลตัวเลขเท่าน้ัน โดยการเปรียบเทียบกับ เกณฑ์ที่เป็นช่วงตัวเลขและแสดงผลลัพธ์ท่ีใกล้เคียงเกณฑ์มากท่ีสุด จะเห็นได้ว่าวิธีการนี้ค่าที่ค้นหาไม่ จาเปน็ ตอ้ งตรงกนั ทุกประการกบั ในตารางทีม่ ีขอ้ มลู โดยฟงั กช์ นั VLOOKUP จะแสดงผลัลพธท์ ่ใี กลเ้ คยี งให้ เสมอ ยกเวน้ ค่าท่ีต้องการหาน้อยกว่าคา่ ทนี่ ้อยทส่ี ดุ ในตาราง ฟงั ก์ชันจะสง่ ค่า Error เปน็ #N/A กลบั มาให้ ผูใ้ ช้ ซ่ึงสามารถเปรยี บเทยี บได้กับการหาผลลัพธจ์ ากการใช้เงื่อนไขของฟังกช์ ัน IF และยังสามารถใช้แทน กนั ได้ ตัวอยา่ งการค้นหาที่ใชว้ ธิ นี ้ี เช่น การพิจารณาใหส้ ่วนลดแก่ลูกค้าตามภาพท่ี 8.3 ซึ่งมีช่วงของเกณฑ์ การใหส้ ว่ นลด ถา้ ลกู คา้ มียอดขายไม่ถงึ 10,000 บาทจะไมไ่ ด้รับสว่ นลด 10,000 – 29,999 บาทได้สว่ นลด 5% ยอด 30,000 – 49,999 บาท ได้ส่วนลด 10% และ 50,000 บาทข้ึนไป ได้ส่วนลด 15% ซึ่งถ้านามา เขยี นเป็นฟังกช์ นั IF จะเปน็ สูตรดังน้ี สมมตใิ ห้ เซลล์ E1 เกบ็ ยอดขายใหล้ ูกค้า

164 บทท่ี 8 การใชฟ้ ังกช์ ันในการคน้ หาเพ่อื จดั ทาใบเสรจ็ รับเงนิ =IF(E1>=$B$5,$C$5,IF(E1>=$B$4,$C$4,IF(E1>=$B$3,$C$3,$C$2))) สูตรท่ี 9-2 ภาพที่ 8.5 การตรวจสอบเงื่อนไขดว้ ย IF ซ่ึงจะเห็นได้ว่าต้องใช้ IF ซ้อนกันถึง 3 ช้ัน และถ้ามีเง่ือนไขมากขึ้นก็จะมีจานวนช้ันซ้อนกันของ ฟังก์ชันมากข้ึนด้วย ในขณะท่ีฟังก์ชัน VLOOKUP นั้นจะมีวิธีการทางานที่ต่างออกไปคือ VLOOKUP จะ นาค่าตัวเลขที่ต้องการค้นหา ไปเทียบกับเกณฑ์ขั้นต่าในตาราง (เซลล์ช่วง B2:B5) เช่น ยอดขายให้ลูกค้า คือ 25,000 บาท VLOOKUP จะเริ่มจากนาค่า 25,000 ไปเทียบกับค่า 0 ในเซลล์ B2 เมื่อพบว่าค่า 25,000 มากกว่า 0 มนั จะนาค่าลงไปเทียบกับแถวถดั ไปคือ คา่ 10,000 ในเซลล์ B3 เมือ่ พบวา่ ค่า 25,000 มากกว่า 10,000 มันจะนาค่าลงไปเทียบกับแถวถัดไปคือ ค่า 30,000 ในเซลล์ B4 เม่ือพบว่าค่า 25,000 น้อยกว่า 30,000 มันจะย้อนกลับไปหาผลลัพธ์ในเซลล์ก่อนหน้าคือ B3 และจะได้ผลลัพธ์เป็น 5% เช่นเดียวกับการใชฟ้ ังก์ช่นั IF แตม่ ีรปู แบบการเขยี นทีส่ นั้ กวา่ คือ =VLOOKUP(E1,$B$2:$C$5,2,TRUE) สตู รที่ 9-3 ภาพที่ 8.6 การคน้ หาด้วย VLOOKUP แบบคา่ ใกล้เคียง ซง่ึ แตล่ ะอาร์กิวเมนต์ท่แี ทนคา่ ในฟังกช์ นั VLOOKUP จะอธบิ ายโดยละเอียดในหัวข้อถัดไป 8.2 ฟงั ก์ชนั คน้ หา VLOOKUP ในการทาใบเสร็จรบั เงิน จากสูตรท่ี 9-1 ในภาพท่ี 8.4และ สูตรที่ 9-3 ในภาพที่ 8.6 จะเห็นได้ว่าฟังก์ชัน VLOOKUP ประกอบดว้ ย 4 อารก์ ิวเมนต์ ซงึ่ 3 อาร์กิวเมนต์แรกนนั้ ต้องใส่ค่า ส่วนอาร์กวิ เมนต์สดุ ท้ายไม่จาเป็นต้องใส่ กไ็ ด้ แตท่ ั้งนีจ้ ะทางานตามค่าที่ Excel ตั้งให้เป็นเร่ิมต้น โดยมรี ายละเอียดดงั นี้ VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) การทางาน ใช้คน้ หาคา่ ในคอลมั นแ์ รกของตาราง แล้วส่งกลับค่าในแถวเดียวกนั จากคอลมั น์อนื่ ในตาราง

ดร.สาวติ รี บญุ มี สว่ นท่ี 3 การประยกุ ต์ใช้ฟังกช์ ัน Microsoft Excel ในงานธุรกิจ 165 ภาพท่ี 8.7 หน้าตา่ งฟังก์ชนั VLOOKUP และอารก์ วิ เมนต์  lookup_value (ต้องระบุ) ค่าที่จะค้นหาในคอลัมน์แรกของตาราง ท้ังนี้ Lookup_value สามารถเป็นได้ท้ังค่าหรือการอ้างอิงเซลล์ ถ้าค่า lookup_value ที่เป็นตัวเลขมีค่าน้อยกว่าค่าท่ี นอ้ ยที่สดุ ในแถวแรกของ table_array จะทาให้ VLOOKUP คนื คา่ ความผิดพลาด #N/A หรอื อีก นยั หนงึ่ คือหาข้อมลู ไมพ่ บ ค่าท่ีใส่ในอาร์กิวเมนต์น้ี คือ ค่าหน่ึงค่า หรือ เซลล์หนึ่งเซลล์ ที่มีค่าหน่ึงค่าอยู่ในน้ัน สามารถ ใช้ได้ทั้งตัวเลขและตัวอักษรขึ้นอยู่กับวิธีการค้นหา ถ้าเป็นการค้นหาแบบตรงการทุกประการสามารถใชไ้ ด้ ทั้งตัวเลขและตัวอักษร แต่ถ้าเป็นการค้นหาแบบใกล้เคียงจะใช้ได้เฉพาะตัวเลขเท่านั้น แต่ท้ังนี้ควร หลกี เล่ียงการพิมพ์คา่ ลงไปโดยตรงในสูตร แตใ่ ช้วธิ ีการอา้ งอิงเซลล์ในสูตรเพอื่ ให้สามารถคดั ลอกสตู ร หรือ เปลย่ี นคา่ ที่จะค้นหาได้ จากตัวอย่างในสูตรที่ 9-1 และ 9-3 ค่า lookup_value ท่ีแทนค่าลงไปคือ E1 ซ่ึงในสูตรท่ี 9-1 เซลล์ E1 จะเก็บค่ารหัสสินค้า คือ T1005 ท่ีต้องการนาไปค้นหา ดังภาพที่ 8.8 และในสูตรที่ 9-3 เซลล์ E1 จะเกบ็ ค่ายอดขายให้กบั ลกู คา้ คอื 25,000

166 บทที่ 8 การใช้ฟังก์ชนั ในการคน้ หาเพื่อจัดทาใบเสรจ็ รับเงิน ภาพที่ 8.8 ตวั อยา่ งอารก์ วิ เมนต์ lookup_value ใน VLOOKUP  table_array (ต้องระบุ) คอลัมน์ข้อมูลสองคอลัมน์หรือ มากกว่า โดยค่าในคอลัมน์แรกของ table_array คือค่าท่ีค้นหาด้วย lookup_value ค่าเหล่านี้อาจเป็นข้อความ ตัวเลข หรือค่าทาง ตรรกะ ตกั อกั ษรขอ้ ความทใ่ี ช้ตวั พมิ พใ์ หญ่และตวั พิมพเ์ ล็กจะไม่แตกตา่ งกันไมม่ ผี ลตอ่ การค้นหา ค่าท่ีใส่ในอาร์กิวเมนต์น้ี คือ ช่วงของเซลล์ที่เป็นตารางท่ีมีข้อมูลทั้งหมด และไม่จาเป็นต้อง เลอื กหวั คอลัมน์ เชน่ จากตัวอยา่ งในภาพที่ 8.9 คือ $A$2:$C$9 ภาพที่ 8.9 ตัวอยา่ งอาร์กิวเมนต์ table_array ใน VLOOKUP สิง่ สาคัญท่ีควรร้ใู นการเลือก table_array o คอลัมน์ท่ีมีค่าท่ีจะค้นหา หรือ lookup_value ในคอลัมน์ จะต้องอยู่ คอลมั น์แรกจากทางซ้ายของช่วงเซลลท์ ี่เลอื กเปน็ ตารางเสมอ o ถ้าเป็นการค้นหาแบบตรงกันทุกประการ คอลัมน์แรกน้ันไม่จาเป็นต้อง เรยี งลาดับ o ถ้าเป็นการค้นหาแบบใกล้เคียง คอลัมน์แรกน้ันต้องเรียงลาดับจากน้อยไป มาก ไมเ่ ชน่ น้นั ผลลัพธอ์ าจจะผดิ พลาดได้ o การเลือกชว่ งเซลล์นัน้ ตอ้ งครอบคลุมตั้งแตค่ อลัมนท์ ี่มีคา่ ทจ่ี ะค้นหาไปจนถึง คอลมั นท์ ม่ี ีผลลัพธท์ ีต่ อ้ งการ และถึงขอ้ มูลแถวสุดทา้ ย

ดร.สาวิตรี บญุ มี สว่ นท่ี 3 การประยกุ ตใ์ ช้ฟงั ก์ชนั Microsoft Excel ในงานธุรกจิ 167 จากตัวอย่างในสูตรท่ี 9-1 ค่า table_array ที่แทนลงไปในสูตรคือ $A$2:$C$10 จาก ภาพที่ 8.2 รวมเป็น 3 คอลมั น์ ซง่ึ ในคอลัมน์ A มขี ้อมลู รหัสสินค้าท่ตี อ้ งการใช้คน้ หา และ คอลมั น์ C มีผลลัพธ์คือราคาขายต่อหน่วยท่ีต้องการทราบ จากตัวอย่างในสูตรที่ 9-3 ค่า table_array ท่ี แทนลงไปในสูตรคือ $B$2:$C$5 รวมเป็น 2 คอลัมน์ จากภาพท่ี 8.3 ซึ่งในคอลัมน์ B มีข้อมูล เกณฑ์ยอดขายท่ีใช้เป็นเง่ือนไขการให้ส่วนลด และใน คอลัมน์ C มีผลลัพธ์คือเปอร์เซ็นต์ส่วนลด ของแต่ละระดับการขาย ให้ลองสังเกตว่าในสูตรท่ี 3 จะไม่ใช้คอลัมน์ A เน่ืองจาก ข้อมูลท่ีใช้ ค้นหาเป็นตัวเลข จึงต้องแปลงช่วงเกณฑ์ในคอลัมน์ A ให้เป็นตัวเลขโดยใช้ค่าขั้นต่าของเกณฑ์นั้น ในคอลมั น์ B และใชค้ อลัมนน์ เี้ ปน็ คอลมั นแ์ รกทางซา้ ยใน table_array  col_index_num (ต้องระบุ) หมายเลขคอลัมน์ใน table_array ซ่ึงค่าผลลัพธ์ต้องการทราบ จะถูกส่งกลับมา ถ้า col_index_num เป็น 1 จะส่งกลับค่าในคอลัมน์แรกใน table_array ถ้า col_index_num เป็น 2 จะสง่ กลับคา่ คอลัมน์ที่สองใน table_array เป็นต้น ค่าท่ีใส่ในอาร์กิวเมนต์นี้จะต้องเป็นตัวเลขเท่านั้น ซ่ึงได้มาจากการนับคอลัมน์จากช่วง ของ table_array ท่ีเลอื กไว้ โดยนบั จากคอลมั น์แรกทางซา้ ยเป็น 1 เสมอและนบั ไปจนถงึ คอลัมน์ ทตี่ ้องการทราบผลลพั ธ์ จากตัวอย่างในสูตรที่ 9-1 ตามภาพท่ี 8.9 ค่า col_index_num ท่ีแทนค่าในสูตรคือ 3 ซึ่งได้มาจาก table_array ช่วง $A$2:$C$10 โดยคอลัมน์ C มีผลลัพธ์คือราคาขายต่อหน่วยท่ี ต้องการทราบ ดังนน้ั จะเรม่ิ นบั จาก คอลัมน์ A  B  C จะได้ col_index_num เปน็ คอลมั น์ ที่ 3 ในขณะเดยี วกันถา้ ต้องการผลลพั ธ์เปน็ รายละเอยี ดสินค้า จะได้ col_index_num เป็น 2   ภาพที่ 8.10 ตวั อย่างอาร์กิวเมนต์ col_index_num ใน VLOOKUP

168 บทท่ี 8 การใชฟ้ ังกช์ นั ในการคน้ หาเพือ่ จดั ทาใบเสร็จรับเงนิ จากตัวอย่างในสูตรท่ี 9-3 ในภาพที่ 8.11 ค่า col_index_num ที่แทนค่าในสูตรคือ 2 ซึ่งได้มาจาก table_array ช่วง $B$2:$C$5 โดยคอลัมน์ C มีผลลัพธ์คือราคาขายต่อหน่วยท่ี ตอ้ งการทราบ ดังน้ันจะเริ่มนบั จาก คอลัมน์ B  C จะได้ col_index_num เป็นคอลมั น์ท่ี 2  ภาพที่ 8.11 ตัวอย่างอาร์กิวเมนต์ col_index_num ใน VLOOKUP  range_lookup ค่าตรรกะที่ระบุว่าต้องการให้ VLOOKUP ค้นหาการจับคู่ที่ตรงกันหรือการ จบั คูท่ ใ่ี กล้เคยี ง: o ถ้าต้องการค้นหาแบบค่าใกล้เคียง ให้ใส่เป็น TRUE หรือไม่ใส่ค่าอะไรไว้ โดย VLOOKUP จะทาการส่งการจับคู่ค่าท่ีค้นหา lookup_value กับ ขอ้ มูลในคอลมั นแ์ รกทต่ี รงกนั หรือถ้าไม่พบการจบั คู่ทตี่ รงกัน คา่ ทใ่ี กลเ้ คียง ที่สุดท่ีน้อยกว่าค่า lookup_value จะถูกส่งกลับมา คอลัมน์แรกน้ันต้อง เรียงลำดับจำกนอ้ ยไปมำก o ถ้าต้องการค้นหาแบบตรงกันทุกประการ ให้ใส่เป็น FALSE จะทาให้ VLOOKUP คน้ หาเฉพาะการจับคู่ที่ตรงกัน ในกรณีนี้ ค่าในคอลัมนแ์ รกของ table_array ไม่จาเป็นต้องเรียงลาดับ หากมีค่าสองค่าหรือมากกว่าใน คอลัมน์แรกของ table_array ท่ีตรงกับ lookup_value ค่าแรกท่ีพบจะ ถูกนามาใช้ หากไม่พบการจับคู่ที่ตรงกัน ค่าความผิดพลาด #N/A จะถูก ส่งคืน ค่าท่ีใส่ในอาร์กิวเมนต์นี้จะต้องเป็นคาวา่ TRUE หรือ FALSE หรือเว้นไว้โดยไม่ใส่ซึ่งก็จะ เป็นการทางานเปน็ ค่า TRUE ตวั อยา่ งเช่น จากสูตรท่ี 3 สามารถเขยี นอกี รปู แบบได้ ดงั นี้ =VLOOKUP(E1,$B$2:$C$5,2,) สตู รที่ 9-4 8.2.1 ตัวอย่างการใช้ VLOOKUP 1) จากไฟลช์ อ่ื ch8Invoice.xlsx เปิดแผน่ งาน คะแนน 2) ในแผ่นงานคะแนนมเี กณฑก์ ารประเมนิ ผลการเรียนตามภาพท่ี 8.12

ดร.สาวติ รี บญุ มี สว่ นท่ี 3 การประยกุ ตใ์ ชฟ้ งั กช์ นั Microsoft Excel ในงานธรุ กจิ 169 ภาพท่ี 8.12 เกณฑ์การประเมินผลการเรยี น 3) จากตารางคะแนนในภาพท่ี 8.12 ควรใชว้ ิธกี ารค้นหาแบบ ค่าใกล้เคียง เน่ืองจากค่าท่ีค้นหาไม่ ตรงกนั ทุกประการกับคา่ ที่มใี นตารางทมี่ ขี ้อมลู ภาพท่ี 8.13 การใข้ VLOOKUP คานวณผลการเรียน 4) ให้นาเกณฑ์มาสร้างสูตรโดยใช้ฟังก์ชัน VLOOKUP ในเซลล์ G3 เพ่ือคานวณหาเกรดของนักศึกษาแต่ ละคน โดยตอ้ งสามารถคดั ลอกไปยังเซลล์อนื่ ๆได้ สตู ร G3 =VLOOKUP(E3,$J$5:$K$7,2) ภาพที่ 8.14 ตวั อยา่ งการใช้ VLOOKUP ค้นหาเกรด

170 บทท่ี 8 การใชฟ้ ังกช์ ันในการคน้ หาเพือ่ จัดทาใบเสรจ็ รับเงิน 5) ใตต้ ารางเกณฑ์มตี ารางที่ใช้คน้ หาคะแนนของนักเรยี นโดยใช้รหสั นกั เรียนจากเซลล์ K12 ในการค้นหา ตามภาพท่ี 8.15 ภาพที่ 8.15 ตารางคน้ หาคะแนนตามรหัสนกั เรยี น 6) จากตารางคะแนนในภาพท่ี 8.15 ควรใช้วธิ กี ารค้นหาแบบใด ตรงกันทุกประการ เพราะ เปน็ การค้นหาข้อมลู จากตารางท่เี ฉพาะตรงกับค่าที่นาไปคน้ หาเท่าน้ัน 7) ฟังก์ชัน VLOOKUP ในเซลล์ K13, K14 เพื่อค้นหาคะแนน(%) และผลการเรียนของนักศึกษาแต่ละ คน ตามรหัสนกั ศึกษาในเซลล์ K12 สูตร K13 =VLOOKUP(K12,A3:G163,5,FALSE) สตู ร K14 =VLOOKUP(K12,A3:G163,7,FALSE) ภาพที่ 8.16 การใช้ VLOOKUP คน้ หาข้อมูลคะแนน จากภาพท่ี 8.16 พบวา่ ในสูตรไมม่ กี ารยดึ เซลลเ์ นอื่ งจากไมม่ ีการคดั ลอกสตู รไปยงั เซลล์อ่ืน 8.3 ฟังก์ชั่น IFERROR สาหรับใช้คูก่ ับ VLOOKUP จากทก่ี ล่าวมาถึงการใช้ฟังก์ชัน VLOOKUP ขา้ งตน้ จะพบวา่ ถ้าไม่พบข้อมลู ทีต่ ้องการหา VLOOKUP อาจจะสง่ คา่ กลับมาเป็นค่าผดิ พลาดหรือ รหสั Error เรยี กว่า #N/A ในเซลลท์ ่ีสรา้ งสูตร ดงั ภาพที่ 8.17 ซง่ึ หากผู้ใช้ที่ไม่มีความรู้เก่ียวกับ Excel อาจจะคิดว่าเกิดความผิดพลาดในการคานวณ ดังน้ันตั้งแต่ Excel 2007 เป็นตน้ มาจึงมกี ารเพมิ่ ฟังกช์ นั IFERROR มาเพ่อื ใช้ในการควบคมุ การแสดงคา่ ความผิดพลาด

ดร.สาวิตรี บญุ มี ส่วนที่ 3 การประยกุ ตใ์ ชฟ้ ังกช์ นั Microsoft Excel ในงานธุรกจิ 171 ภาพที่ 8.17 Error เมื่อ VLOOKUP หาขอ้ มูลไม่พบ IFERROR(value, value_if_error) ฟงั ก์ชัน IFERROR มีอารก์ ิวเมนต์ 2 อารก์ ิวเมนต์ ดังน้ี ภาพที่ 8.18 หน้าตา่ งฟังก์ชัน IFERROR และอาร์กิวเมนต์  Value (ต้องระบุ) อาร์กิวเมนต์ที่ถูกตรวจสอบหาข้อผิดพลาด ซ่ึงเป็นส่วนท่ีใช้ใส่สูตรคานวณ ต่างๆ ของ Excel ค่าท่ีใส่ในอาร์กิวเม้นต์น้ีจะต้องเป็นสูตรคานวณ เช่น สูตรจากฟังก์ชั่น VLOOKUP ท้ังน้ี ไม่จากัดท่ีเฉพาะ VLOOKUP อาจจะเป็นสูตรคานวณอื่น ๆ ท่อี าจจะมีข้อผิดพลาดทแี่ สดงออกมา เปน็ รหัส Error ได้  Value_if_error (ต้องระบุ) ค่าที่ส่งกลับถ้าสูตรประเมินได้ข้อผิดพลาด ซึ่งประกอบด้วย ข้อผิดพลาดชนิดต่อไปน้ี คือ #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? หรือ #NULL! ค่าที่ใส่ในอาร์กิวเมนต์นี้สามารถเป็นได้ตั้งแต่ ข้อความ เช่น “ไม่พบข้อมูล” หรือ ตัวเลข หรอื สูตรคานวณอืน่ จากสูตรที่ 1 เมอื่ ใช้รว่ มกบั ฟังก์ชนั IFERROR จะมีลกั ษณะได้หลายแบบ ดังตวั อย่างตอ่ ไปน้ี =IFERROR(VLOOKUP(E1,$A$2:$C$10,3,FALSE), “”)

172 บทท่ี 8 การใชฟ้ ังก์ชันในการคน้ หาเพ่ือจัดทาใบเสรจ็ รับเงนิ หมายถึง ถ้าสูตรนม้ี ีผลลัพธเ์ ป็นขอ้ ผิดพลาด ให้ส่งคา่ กลับเป็นคา่ ว่าง (แทนทจ่ี ะแสดง #N/A) =IFERROR(VLOOKUP(E1,$A$2:$C$10,3,FALSE), “ไมพ่ บขอ้ มูล”) หมายถงึ ถ้าสูตรน้มี ผี ลลัพธ์เปน็ ขอ้ ผดิ พลาด ให้ส่งค่ากลบั เปน็ ข้อความว่า “ไมพ่ บข้อมลู ” =IFERROR(VLOOKUP(E1,$A$2:$C$10,3,FALSE), 0) หมายถึง ถ้าสูตรนมี้ ผี ลลพั ธ์เป็นขอ้ ผิดพลาด ใหส้ ง่ ค่ากลับเปน็ คา่ ศูนย์ =IFERROR(VLOOKUP(E1,$A$2:$C$10,3,FALSE), “ไม่พบ ” & E1) หมายถึง ถ้าสตู รนม้ี ีผลลัพธเ์ ป็นขอ้ ผิดพลาด ใหส้ ง่ ค่ากลบั เป็นขอ้ ความ “ไม่พบ T1005” =IFERROR(VLOOKUP(E1,$A$2:$C$10,3,FALSE), F1*F2) หมายถงึ ถ้าสูตรนม้ี ผี ลลัพธ์เป็นข้อผิดพลาด ให้สง่ คา่ กลับเปน็ ผลลัพธ์จากการคานวณการคูณของ ค่าทอี่ ยู่ในเซลล์ F1 และ F2 8.3.1 ตัวอย่างการใชฟ้ งั ก์ชัน IFERROR รว่ มกบั VLOOKUP 1) ในแผน่ งาน คะแนน ท่ีสร้างสคู รคานวณหาเกรดด้วย VLOOKUP ไว้ 2) ให้สร้างสูตรในเซลล์ K13 โดยใช้ IFERROR ร่วมกับ VLOOKUP ท่ีสร้างไว้ ให้แสดงข้อความ “---” เมอ่ื มคี วามผดิ พลาด สูตร K13 =IFERROR(VLOOKUP(K12,A3:G163,5,FALSE),\"---\") 3) ให้สรา้ งสูตรในเซลล์ K14 โดยใช้ IFERROR รว่ มกบั VLOOKUP ทีส่ ร้างไว้แลว้ ให้แสดงคา่ ว่างในเซลล์ เมื่อมคี วามผดิ พลาด สูตร K14 =IFERROR(VLOOKUP(K12,A3:G163,7,FALSE),\"\") ภาพที่ 8.19 ฟังก์ชัน IFERROR ร่วมกับ VLOOKUP

ดร.สาวติ รี บญุ มี สว่ นที่ 3 การประยกุ ต์ใชฟ้ งั ก์ชัน Microsoft Excel ในงานธรุ กจิ 173 ภาพที่ 8.20 ตัวอยา่ งก่อนและหลงั การใช้ IFERROR 8.4 ฟงั กช์ นั อนื่ ๆ ท่ีเก่ยี วข้องในการทาใบเสร็จรบั เงนิ โดยปกติแล้วใบเสร็จรับเงินสาเร็จรูปท่ีซ้ือได้จากร้านค้านั้นจะมีข้อมูลอย่างน้อย คือ เลขท่ีใบเสร็จ ช่ือ-ที่อยู่ของลูกค้า วันที่ออกใบเสร็จ จานวนสินค้า ช่ือสินค้า ราคาต่อหน่วย จานวนเงินรวม และจานวน เงินเป็นตัวอักษร ตามภาพท่ี 8.21 โดยแต่ละบริษัทท่ีจัดพิมพ์ใบเสร็จเองอาจจะมีรายละเอียดอื่น ๆ เพมิ่ เตมิ ตามความตอ้ งการของบริษทั นัน้ ภาพที่ 8.21 ตวั อย่างใบเสรจ็ รับเงินสาเรจ็ รูป ในแตล่ ะสว่ นสามารถใช้ Excel ช่วยในการคานวณและนาเสนอข้อมูลดังนี้ รายละเอียดลูกค้า ใช้ VLOOKUP ค้นหาจากตารางขอ้ มูลลูกค้า รายละเอยี ดตามหวั ขอ้ กอ่ นหน้าน้ี

174 บทที่ 8 การใช้ฟังกช์ ันในการค้นหาเพ่อื จดั ทาใบเสร็จรับเงนิ วนั ทป่ี จั จุบัน ใช้ TODAY() แสดงผล”วันท่ี”ปัจจบุ นั ฟงั ก์ชันน้ีไมม่ อี าร์กวิ เมนต์ หรือใช้ NOW() แสดงผล”วันที่และเวลา”ปัจจุบัน ฟังก์ชนั นไ้ี มม่ อี าร์กิวเมนต์ รายละเอยี ดสินคา้ ใช้ VLOOKUP คน้ หาจากตารางขอ้ มูลสนิ คา้ รายละเอียดตามหวั ข้อกอ่ นหน้านี้ จานวนเงินรวม ใช้ SUMPRODUCT หาผลรวมจากการคูณระหวา่ งจานวนสนิ ค้าและราคาสินค้า ตอ่ หนว่ ย จานวนเงนิ อกั ษรภาษาไทย ใช้ BAHTTEXT แปลงตัวเลขจากตัวเลขเปน้ ขอ้ ความตัวอักษรภาษาไทยลงท้าย ด้วยหน่วยเงินบาท และสตางค์ หรือ ถ้วน โดยฟังกช์ ัน TODAY NOW BAHTTEXT และ SUMPRODUCT มีรายละเอียดการใช้งานดงั น้ี TODAY() ส่งคา่ กลบั เป็นวนั ท่ีปจั จุบัน โดยไมต่ ้องใสอ่ ารก์ วิ เมนต์ แต่ตอ้ งมีวงเลบ็ ภาพท่ี 8.22 ตวั อยา่ งการใช้ฟังก์ชนั TODAY NOW() ส่งค่ากลับเปน็ วนั ทีแ่ ละเวลาปจั จุบนั โดยไมต่ ้องใส่อารก์ ิวเมนต์ แตต่ อ้ งมีวงเล็บ ภาพที่ 8.23 ตวั อยา่ งการใช้ฟังกช์ ัน NOW

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยกุ ตใ์ ช้ฟงั ก์ชัน Microsoft Excel ในงานธรุ กจิ 175 BAHTTEXT(number) ใช้แสดงผลด้วยการแปลงค่าตัวเลขเป็นตัวอักษรจานวนเงินภาษาไทย (มีคาว่าถ้วนลงท้าย) โดยมี เพียงอารก์ ิวเมนตเ์ ดียวคอื ตวั เลข  Number (ตอ้ งระบุ) คือตวั เลขท่ีตอ้ งการแปลงเปน็ ภาษาไทย ค่าทีใ่ สใ่ นอารก์ ิวเม้นต์น้ีจะต้องเป็นตวั เลขเท่าน้นั อาจจะมาจากการใส่ค่าตวั เลขโดยตรงหรือการ อ้างอิงเซลลท์ เ่ี ก็บคา่ ตัวเลขกไ็ ด้ เชน่ = BAHTTEXT(20510.50) จะไดผ้ ลลัพธ์เปน็ “สองหม่ืนห้ารอ้ ยสิบบาทห้าสบิ สตางค์” = BAHTTEXT(5690) จะไดผ้ ลลพั ธเ์ ป็น “ห้าพันหกร้อยเก้าสิบบาทถว้ น” = BAHTTEXT(E1) จะได้ผลลพั ธต์ ามค่าทอ่ี ย่ใู นเซลล์ E1 ภาพท่ี 8.24 ตัวอย่างการใช้ฟังกช์ นั BAHTTEXT SUMPRODUCT(array1,array2,array3, ...) ใช้แสดงผลรวมของผลคูณระหว่าง 2 อาร์เรย์ โดยจะนาค่าแรกของแต่ละอาร์เรย์มาคูรกันและบวก ด้วยผลคูณของค่าในลาดับต่อไป เช่น {1, 2, 3} x {4,5,10} จะได้ผลลัพธ์เป็น (1x4) + (2x5) +(3x10) = 44  array1, array2, array3 ... คืออาร์เรย์ 2 ถึง 30 อาร์เรย์ ท่ีต้องการให้คูณกัน แล้วบวกกัน ทัง้ หมดทุกคอมโพเนนต์ โดยแตล่ ะอารเ์ รย์ตอ้ งมขี ้อมูลทอี่ ยใู่ นอารเ์ รย์เทา่ กนั ค่าที่ใส่ในอาร์กิวเม้นต์นี้จะต้องเป็นช่วงของเซลล์ท่ีเป็นตัวเลข นั่นคือ แต่ละอาร์เรย์จะต้อง ประกอบด้วยเซลล์หลาย ๆ เซลล์ และแต่ละอาร์เรย์จะต้องมีจานวนเซลล์เท่ากัน เช่น จากภาพที่ 8.25 ถ้า ดูตามแนวตั้งหรือคอลัมน์จะประกอบด้วย 2 อาร์เรย์ ในคอลัมน์ A และ คอลัมน์ B แต่ละอาร์เรย์มีข้อมูล 3 แถวเทา่ กัน เมือ่ สร้างเป็นสูตรจะได้ดงั นี้ =SUMPRODUCT(A1:A3,B1:B3) =(1x4) + (2x5) +(3x10) = 44 ตามภาพที่ 8.26

176 บทท่ี 8 การใชฟ้ ังกช์ ันในการคน้ หาเพื่อจดั ทาใบเสรจ็ รับเงิน ภาพท่ี 8.25 ตวั อย่างอาร์เรย์ท่ใี ชใ้ น SUMPRODUCT ภาพท่ี 8.26 ตัวอยา่ งการใช้ฟังก์ชัน SUMPRODUCT สรา้ งตามอาร์เรย์แนวตงั้ ถ้าดูตามแนวนอนหรอื แถวจะประกอบดว้ ย 3 อาร์เรย์ จากแถวที่ 1 แถวท่ี 2 และแถวที่ 3 ตามลาดับ แต่ละอารเ์ รยม์ ีข้อมลู 2 คอลมั น์เท่านนั้ เมอื่ สรา้ งเป็นสตู รจะไดด้ ังนี้ =SUMPRODUCT(A1:B1,A2:B2,A3:B3) =(1x2x3) +(4x5x10) = 206 ตามภาพที่ 8.27 ภาพท่ี 8.27 ตัวอยา่ งการใช้ฟังกช์ ัน SUMPRODUCT สร้างตามอาร์เรย์แนวนอน

ดร.สาวติ รี บญุ มี สว่ นท่ี 3 การประยกุ ต์ใชฟ้ ังกช์ นั Microsoft Excel ในงานธุรกจิ 177 8.4.1 ตัวอย่างการใชฟ้ งั กช์ น่ั SUMPRODUCT 1) จากไฟลช์ ่ือ ch8Invoice.xlsx เปิดแผ่นงาน Bonus ซง่ึ แสดงเงินเดือนและจานวนเดือนท่ีพนักงานแต่ ละคนไดโ้ บนัส ตามภาพที่ 8.28 ภาพท่ี 8.28 ตารางเงินเดอื นและโบนสั 2) จานวนเงินรวมโบนัสทั้งหมดท่ีต้องจ่ายพนักงาน คานวณมาจากผลรวมของผลคูณระหว่าง เงินเดือน และโบนสั ของพนักงานแต่ละคน 3) สรา้ งสูตรโดยใช้ฟงั ก์ชัน SUMPRODUCT ในเซลล์ C11 เพื่อคานวณหาจานวนเงนิ รวมโบนสั ท้งั หมด สูตร C11 =SUMPRODUCT(B2:B10,C2:C10) ภาพท่ี 8.29 ตัวอย่างการใช้ฟังก์ชนั SUMPRODUCT คานวณโบนัส

178 บทที่ 8 การใชฟ้ ังกช์ นั ในการค้นหาเพือ่ จดั ทาใบเสร็จรบั เงิน 8.5 การใช้ฟงั กช์ นั Excel สรา้ งใบเสรจ็ รับเงนิ ในการสร้างใบเสร็จรับเงิน ใช้ฟังก์ชัน VLOOKUP TODAY BAHTTEXT และ SUMPRODUCT มา ประยุกต์ใช้ รวมถึงการอ้างอิงเซลล์ และการเช่ือมสูตรกับข้อความโดยใช้เครื่องหมาย “&” โดยมี รายละเอียดตามภาพที่ 8.30 ภาพท่ี 8.30 ตวั อย่างแผ่นงาน invoice-แบบฝกี หัดที่ใส่สตู รแลว้ จากไฟล์ชอื่ ch8Invoice.xlsx เปิดแผน่ งาน invoice-แบบฝกึ หัด สร้างสตู รเพ่อื ทาเป็นใบเสรจ็ รบั เงิน โดย สูตรท่สี ร้างในใบเสร็จรบั เงิน มีรายละเอยี ดตามคาอธบิ ายดงั นี้  สว่ นท่เี ตมิ สขี าว ใชส้ าหรับกรอกข้อมูล ประกอบด้วย รหสั ลูกค้า รหัสสินค้า และจานวน  ส่วนท่ีเติมสีเทา ใช้สาหรับกรอกสตู ร ซงึ่ จะปอ้ งกันเซลลไ์ ม่ใหส้ ามารถแก้ไขได้ในภายหลงั  ทกุ เซลลท์ ่สี ร้างสตู ร ใช้ IFERROR รว่ มกับสูตรเพ่ือแสดงค่าวา่ งเมื่อมขี อ้ ผิดพลาดจากการหาข้อมูลไม่พบ เทคนิคการจดั การ ERROR โดยใช้ IF สามารถให้เซลล์ที่มสี ูตรให้แสดงคา่ เฉพาะเม่ือมีการกรอกข้อมูลแทนการแสดง error ได้ โดยใช้ฟังก์ชัน IF ได้เช่นกัน เช่น = IF(B10<>””,VLOOKUP(B10,$A$2:$C$10,3,FALSE,””) หมายถึง ถ้าเซลล์ รหสั สนิ คา้ ไม่ว่างให้ดาเนินการคานวณ ไม่อย่างนั้นใหแ้ สดงผลเปน็ “” (ค่าว่าง)

ดร.สาวิตรี บญุ มี สว่ นที่ 3 การประยกุ ตใ์ ชฟ้ ังกช์ นั Microsoft Excel ในงานธรุ กิจ 179  รายละเอยี ดลกู ค้า ประกอบดว้ ย ชือ่ ทีอ่ ยู่ เบอรโ์ ทรศพั ท์ และเลขประจาตวั ผู้เสยี ภาษี ใช้ VLOOKUP ค้นหาจากตารางข้อมูลลูกค้าในแผ่นงาน “ลูกค้า” เซลล์ A2:E9 โดยใช้รหสั ลกู ค้าในเซลล์ B6 ในการค้นหาจากแผ่นงานลกู คา้ ชอ่ื ลกู ค้า สตู ร D6 =IFERROR(VLOOKUP(B6,ลูกค้า!$A$2:$E$9,2,FALSE),\"\") หรอื =IF(B6<>\"\",VLOOKUP(B6,ลูกค้า!$A$2:$E$9,2,FALSE),\"\") ท่อี ยู่ สูตร B7 =IFERROR(VLOOKUP(B6,ลูกค้า!$A$2:$E$9,4,FALSE),\"\") เบอร์โทรศัพท์ สูตร I7 =IFERROR(VLOOKUP(B6,ลูกคา้ !$A$2:$E$9,5,FALSE),\"\") เลขประจาตัวผู้เสียภาษี สตู ร C8 =IFERROR(VLOOKUP(B6,ลูกคา้ !$A$2:$E$9,3,FALSE),\"\")  วนั ทป่ี จั จุบัน ใช้ TODAY() แสดงผล”วันท่ี”ปัจจบุ นั สูตร I6 = TODAY()  ลาดับที่ ใหแ้ สดงลาดับทีเ่ มื่อเซลล์รหสั สนิ คา้ มีการกรอกข้อมูล โดยบวกคา่ เพม่ิ จากลาดับ ก่อนหนา้ เพื่อใหส้ ูตรสามารถคดั ลอกได้ สตู ร A11 =IF(B11<>\"\",A10+1,\"\") แลว้ คัดลอกสตู รไปยงั เซลล์ A12:A19  รายละเอียดสนิ คา้ ประกอบด้วย รายการสนิ ค้า และราคาต่อหน่วย ใช้ VLOOKUP คน้ หาจากตารางข้อมูลสนิ ค้า ในแผน่ งาน “สินค้า” ช่วง A2:C10 โดยใชร้ หัสสินคา้ ในเซลล์ B10 ในการคน้ หาจากแผ่นงานสนิ ค้า โดยสตู รต้องสามารถคัดลอกได้ รายการสนิ คา้ สูตร C10 =IFERROR(VLOOKUP(B10,สินค้า!$A$2:$C$10,2,FALSE),\"\") หรอื =IF(B10<>\"\",VLOOKUP(B10,สินคา้ !$A$2:$C$10,2,FALSE),\"\") แลว้ คัดลอกสตู รไปยงั เซลล์ C11:C19 ราคาตอ่ หน่วย สูตร I10 =IFERROR(VLOOKUP(B10,สนิ คา้ !$A$2:$C$10,3,FALSE),\"\") แล้วคดั ลอกสตู รไปยงั เซลล์ I11:I19  จานวนเงิน ผลคูณระหว่าง จานวน และราคาต่อหน่วย โดยใช้ฟังก์ชัน IF ตรวจสอบว่า ถ้า เซลล์รหัสสินค้า มีการกรอกข้อมูลหรือไม่ว่าง ให้แสดงค่าผลลัพธ์จากการคูณ ถ้ายังไม่กรอกแสดงผล เป็นค่าวา่ ง สตู ร J10 =IF(B10<>\"\",H10*I10,\"\") แล้วคดั ลอกสตู รไปยังเซลล์ J11:J19  จานวนเงนิ รวม ใช้ SUMPRODUCT ระหวา่ งอาร์เรย์ จานวน และ อารเ์ รย์ ราคาตอ่ หน่วย สูตร J20 =SUMPRODUCT(H10:H19,I10:I19)  ส่วนลด ใช้ VLOOKUP ค้นหาจากตารางส่วนลด ในแผ่นงาน “ส่วนลด” ช่วง B2:C5 โดยใช้จานวนเงนิ รวมในเซลล์ J20 ในการคน้ หาจากแผ่นงานสว่ นลด

180 บทท่ี 8 การใชฟ้ ังกช์ ันในการค้นหาเพือ่ จัดทาใบเสรจ็ รับเงนิ %สว่ นลด สูตร I21 =VLOOKUP(J20,สวนลด!B2:C5,2)  จานวนเงนิ ส่วนลด ผลคณู ระหว่าง%ส่วนลดกับจานวนเงินรวม สูตร J21 =I21*J20  รวมท้งั สน้ิ ผลตา่ งระหว่างจานวนเงินรวมและส่วนลด สตู ร J22 =J20-J21  จานวนเงินอักษรภาษาไทย ใช้ BAHTTEXT แปลงตัวเลขจากตัวเลขเป็นข้อความตัวอักษรภาษาไทย และใช้สัญลักษณ์ “&” เช่ือมสูตรท่ีสร้างจากฟังก์ช่ัน BAHTTEXT กับเครื่องหมายวงเล็บ “( )” ดัง ตัวอยา่ ง “( สามหม่ืนหา้ พนั หน่ึงรอ้ ยแปดสิบเอด็ บาทถ้วน )” สตู ร B22 =\"( \"&BAHTTEXT(J22)&\" )\" เทคนคิ การเชอ่ื มข้อความและสตู ร นอกจากใช้สัญลกั ษณ์ & ในการเช่อื มข้อความและสูตรแลว้ สามารถใชฟ้ ังก์ชนั CONCATENATE() ได้อกี ดว้ ย จากตัวอยา่ งสามารถเขยี นไดด้ ังนี้ =CONCATENATE(\"( \",BAHTTEXT(J22),\" )\") ภาพที่ 8.31 สรุปสูตรทใ่ี ชใ้ นการสรา้ งใบเสร็จรบั เงนิ 8.6 วิธกี ารป้องกนั สูตรในแผน่ งาน สูตรที่สร้างใน Excel น้ัน ส่วนใหญ่มีความซับซ้อน และมีความสาคัญเน่ืองจากผลลัพธ์จากการ คานวณต้องมีความถูกต้อง ในบางครั้งผู้ที่ใช้ไฟล์ Excel กับผู้สร้างไฟล์เป็นบุคคลที่ไม่ใชค่ นเดียวกัน ดังนั้น ผู้ใช้ท่ีไม่มีความรู้ใน Excel อาจจะพยายามแก้ไขสูตรที่สร้างขึ้นด้วยความรู้เท่าไม่ถึงการณ์ หรือ ลบสูตรที่ สร้างทิ้งเนื่องจากแค่พิมพ์ข้อความใด ๆ ลงไปในเซลล์ก็สามารถแทนท่ีสูตรท่ีสร้างขึ้นได้ในทันที ดังนั้น

ดร.สาวติ รี บญุ มี ส่วนที่ 3 การประยุกตใ์ ชฟ้ งั กช์ ัน Microsoft Excel ในงานธรุ กจิ 181 Excel จึงมีเคร่ืองมือท่ีช่วยในการป้องกันเซลล์ในแผ่นงาน ซ่ึงสามารถเลือกป้องกันทุกเซลล์ หรือป้องกัน เพยี งบางเซลลก์ ไ็ ด้ ทุกเซลล์ในทุกแผ่นงานของ Excel จะได้รับการตั้งค่าให้ถูกป้องกันเป็นค่าเร่ิมต้นอยู่แล้ว ดังน้ัน ก่อน การป้องกันแผ่นงานต้องพิจารณาก่อนว่ามีเซลล์ใดบ้างที่ใช้กรอกข้อมูลนาเข้า หรือ เป็นส่วนนาเข้าข้อมูล และปลดลอ็ คกอ่ นล็อคแผน่ งาน จากตัวอย่างการสร้างใบเสร็จรบั เงิน เซลล์ที่ต้องใช้กรอกข้อมูลและไม่ต้องการป้องกันเซลล์ คือ ส่วน ที่เติมสีขาวในแผ่นงาน ประกอบด้วย รหัสลูกค้าในเซลล์ B6 รหัสสินค้าในเซลล์ B10:B19 และจานวนใน เซลล์ H10:H19 8.6.1 วิธกี ารปอ้ งกันแผ่นงาน วิธกี ารปอ้ งกนั เซลล์ในแผ่นงาน มีขัน้ ตอนดังต่อไปนี้ 1. จากแผ่นงาน invoice-แบบฝึกหัด เลือกเซลล์ต่อไปนี้ B6, B10:B19 และ H10:H19 โดยกดปุ่ม Ctrl ค้างไว้ระหวา่ งเลือกเซลล์เพือ่ เลอื กเซลลท์ ไ่ี ม่ติดกัน 2. คลิกเมา้ ส์ป่มุ ขวา แล้วเลือก จดั รูปแบบเซลล์ (Format cells…) 3. ในหนา้ ต่างจัดรปู แบบ ให้เลือกแท็บ การปอ้ งกัน (Protection) 4. เอาเครือ่ งหมาย  ออกจากกลอ่ ง Lock ให้มลี กั ษณะตามภาพที่ 8.32 ภาพที่ 8.32 การตั้งคา่ รูปแบบการล็อคเซลล์ 5. คลกิ ปมุ่ ตกลงเพ่อื ปดิ หนา้ ต่าง 6. ไปทร่ี บิ บอนเมนู รวี วิ (Review) ในกลุ่มเครอ่ื งมอื การเปลยี่ นแปลง (Changes) 7. ใหค้ ลกิ ปอ้ งกันแผน่ งาน (Protect Sheet) ตามภาพท่ี 8.33

182 บทท่ี 8 การใช้ฟังกช์ ันในการค้นหาเพ่อื จัดทาใบเสร็จรับเงิน ภาพที่ 8.33 กลุ่มเคร่ืองมือ การเปลยี่ นแปลง 8. ในหน้าตา่ งใหเ้ ลอื กการป้องกันตามคา่ เร่ิมต้นทต่ี ้ังมาให้ ตามภาพที่ 8.34 ภาพที่ 8.34 หนา้ ต่างป้องกันแผ่นงาน 9. ถา้ ไม่ต้องการต้งั รหัสผ่าน สามารถคลกิ ป่มุ ตกลง เพอื่ เริ่มการป้องกันแผ่นงานไดท้ นั ที 10. ถ้าต้องการตั้งรหัสผ่าน เพ่ือป้องกันไม่ให้ผู้ใช้ยกเลิกการป้องกันแผ่นงานได้เอง ให้ระบุรหัสผ่านท่ี ตอ้ งการ ยกตวั อยา่ งเช่น 12345 ในกลอ่ งรหัสผ่าน เมอ่ื คลกิ ปมุ่ ตกลงแลว้ Excel จะแสดงหน้าต่าง ใหก้ รอกรหสั ผา่ นยนื ยันอกี คร้งั ตามภาพที่ 8.35 ให้ระวงั รหสั ผ่านเป็น Case-sensitive ภาพท่ี 8.35 การตั้งรหสั ผ่านปอ้ งกนั แผน่ งาน 11. คลปิ ปุ่มตกลง 12. ทดลองแก้ไขแผ่นงาน จะพบว่าเซลล์ท่ีไม่ได้ปลดล็อคจะไม่สามารถทาการแก้ไขได้ และมีหน้าต่าง แจง้ เตือน ตามภาพท่ี 8.36

ดร.สาวติ รี บญุ มี สว่ นที่ 3 การประยุกต์ใชฟ้ ังก์ชนั Microsoft Excel ในงานธุรกิจ 183 ภาพท่ี 8.36 หน้าต่างแจง้ เตือนเซลล์ทมี่ กี ารป้องกนั 8.6.2 วธิ ีการยกเลกิ การป้องกนั แผ่นงาน วธิ ีการยกเลิกการปอ้ งกันเซลลใ์ นแผ่นงาน มีขัน้ ตอนดงั ต่อไปนี้ 1. ไปที่ริบบอนเมนู รีวิว (Review) ในกล่มุ เคร่ืองมอื การเปล่ยี นแปลง (Changes) 2. ให้คลิก ยกเลิกปอ้ งกนั แผ่นงาน (Unprotect Sheet) ตาม ภาพที่ 8.37 เครื่องมือยกเลิกการป้องกนั แผน่ งาน 3. ถา้ มรี หสั ผา่ นให้กรอกรหัสผา่ น 8.7 สรปุ ในบทน้ีได้อธิบายถึงการใช้ฟังก์ชันเพ่ือใข้ในการจัดทาใบเสร็จรับเงิน โดยฟังก์ชันที่สาคัญคือ VLOOKUP ท่ีเป็นฟังก์ชันท่ีใช้ในการค้นหาข้อมูลจากตารางโดยทาการค้นหาจากบนลงล่าง ถ้าต้องการ ค้นหาจากซ้ายไปขวาสามารถใช้ฟังก์ชัน HLOOKUP ซึ่งมีรูปแบบการใช้งานเช่นเดียวกัน VLOOKUP เป็น ฟังก์ชันการค้นหาที่ใช้งานบ่อย โดยใช้สาหรับค้นหาข้อมูลที่อยู่แถวเดียวกันกับค่าท่ีใช้ในการค้นหาแต่คน ละคอลัมน์ เช่น การใช้รหัสสนิ ค้าค้นหาราคาสินค้าท่ีอยู่ในคอลัมน์ที่สาม โดยสามารถค้นหาได้ 2 แบบคือ แบบใกล้เคยี งและแบบตรงกนั ทุกประการ ถ้า VLOOKUP คน้ หาขอ้ มลู ไม่พบจะส่งคา่ ผิดพลาดกลับมาเป็น #N/A ท้ังท่ีสร้างฟังก์ชันถูกต้อง ดังนั้นสามารถนาฟังก์ชัน IFERROR มาใช้คู่กับ VLOOKUP เพื่อควบคุม การแสดงผลเมื่อเกดิ ข้อผิดพลาด นอกจากนใ้ี นการจัดทาใบเสรจ็ รบั เงินยังสามารถนาฟงั กช์ ันอ่นื ๆ มาใช้ให้ เกิดความสมบูรณ์ได้อีก คือ TODAY() สาหรับแสดงวันที่ปัจจุบันท่ีออกใบเสร็จ BAHTTEXT สาหรับแปลง ตัวเลขเป็นตัวอักษรจานวนเงินภาษาไทย และ SUMPRODUCT สาหรับหาผลคูณของจานวนเงินรวม ฟังก์ชันเหล่าน้ีสามารถนาไปประยุกต์ใช้กับงานได้อีกหลากหลาย โดยเฉพาะ VLOOKUP ท่ีสามารถ นาไปใช้ในการค้นหาข้อมูลจากตารางท่ีเก็บข้อมูลไว้จานวนมากได้อย่างรวดเร็ว เช่น ค้นหาผลการเรียน นักศกึ ษา คน้ หาเปอร์เซ็นต์การจ่ายคอมมิชชนั่ ใหพ้ นักงานตามเกณฑ์ท่ีกาหนด

184 บทท่ี 8 การใช้ฟังกช์ นั ในการคน้ หาเพ่อื จดั ทาใบเสร็จรบั เงิน แบบฝึกหดั ทา้ ยบทที่ 8 ตอนที่ 1 การใช้ VLOOKUP ประเมินดรรชนมี วลกาย 1. เปิดแผน่ งาน BMI 2. สรา้ งสตู รคานวณหา BMI= กก./เมตร^2 ในเซลล์ D8 สตู ร D8 = 3. ใช้ฟังก์ชัน VLOOKUP เพ่ือแสดงสถานะในเซลล์ D10 โดยค้นหามาจากช่วง D13:E17 โดยอ้างอิงจาก BMI ทีค่ านวณไดใ้ น D8 สตู ร D10 = ตอนท่ี 2 การใช้ SUMPRODUCT คานวณเกรดเฉลีย่ 1. เปิดแผน่ งาน GPA 2. จงใสส่ ตู รโดยใช้ ฟงั กช์ นั SUMPRODUCT ในเซลล์ E14