ExcelExpertTip2010OK

Page 51 of 147  วธิ ที ่ี 3 : ใชส้ ตู ร Indirect(“Text บอกตาํ แหนง่ ”) แบบใชช้ อ่ื Range Nameตวั อยา่ งนตี้ า่ งจากตวั อยา่ งทผ่ี า่ นมาเพยี งแคน่ ําชอื่ Range Name มาใชแ้ ทนตําแหน่งอา้ งองิโดยตรง เชน่ หากตงั้ ชอ่ื พน้ื ทตี่ าราง D3:D6 วา่ Chonburi และตงั้ ชอื่ พน้ื ทตี่ าราง C4:E4 วา่Year2 (ใหใ้ ชค้ ําสง่ั Formulas > Create from selection เพอื่ ตงั้ ชอื่ พนื้ ทต่ี ามคําทอี่ ยบู่ นของตารางรวดเดยี ว)จากนัน้ ใชเ้ ซลล์ C9 และ C10 สําหรับพมิ พช์ อื่ Range Name ลงไป เชน่ พมิ พค์ ําวา่ Chonburiและ Year2 ลงไปตามลําดบั  เซลล์ D9 หายอดรวมของ Chonburi ไดโ้ ดยใชส้ ตู ร =Sum(Indirect(C9))  เซลล์ D10 หายอดรวมของ Year2 ไดโ้ ดยใชส้ ตู ร =Sum(Indirect(C10))  เซลล์ E9 หาคา่ ของ Chonburi ในปี Year2 ไดโ้ ดยใชส้ ตู รหาคา่ แบบ Intersection =Indirect(C9) Indirect(C10)ขอ้ ควรระวงั ในการใชส้ ตู ร Indirectสตู รทกุ สตู รของ Excel มที งั้ ขอ้ ดแี ละขอ้ ดอ้ ย สตู รบางสตู รทพ่ี วกเราชอบใชเ้ พราะเป็ นสตู รที่เขยี นสนั้ ๆ แตก่ ลบั ทําให ้ Excel ทํางานชา้ ลงกม็ ี (เชน่ สตู ร VLookup เป็ นตน้ ) ซงึ่ สตู ร Indirectกม็ ขี อ้ ควรระวงั อยดู่ ว้ ยกลา่ วคอื สตู ร Indirect สามารถ link ขอ้ มลู ขา้ มแฟ้มได ้ แตแ่ ฟ้มตน้ ทางตอ้ งเปิดขน้ึ พรอ้ มกบั แฟ้มปลายทางทใี่ ชส้ ตู รนอี้ ยู่ และเป็ นสตู รหนง่ึ ทถี่ อื เป็ น Volatile FunctionVolatile Function เป็ นชอื่ เรยี กประเภทของสตู ร ทจี่ ะทําการคํานวณใหมท่ กุ ครัง้ ตามการคํานวณทเ่ี กดิ ขน้ึ ในแฟ้ม ทงั้ ๆทเ่ี ซลลส์ ตู รนัน้ ไมไ่ ดม้ ตี ําแหน่งอา้ งองิ ทสี่ มั พันธก์ บั เซลลอ์ น่ื มกี ารคํานวณแมแ้ ตน่ อ้ ย (ตามปกตสิ ตู รจะคํานวณใหมต่ ามตอ่ เมอ่ื มเี ซลลอ์ น่ื ทมี่ ตี ําแหน่งอา้ งองิ เกย่ี วขอ้ งเกดิการคํานวณเทา่ นัน้ ) ดงั นัน้ หากใชส้ ตู รทท่ี ําหนา้ ทแี่ บบ Volatile นม้ี ากไปจะทําใหแ้ ฟ้มคํานวณชา้ ลง แตถ่ า้ มกี ารใชส้ ตู รเพยี งไมก่ เ่ี ซลลก์ ็ไมส่ ง่ ผลเทา่ ใดนักสตู รทจ่ี ัดเป็ นประเภท Volatile Function ไดแ้ ก่ สตู ร Areas, Cell, Column, Columns,Indirect, Info, Now, Offset, Rand, Row, Rows, Today สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 52 of 147  วธิ ที ่ี 4 : ใชส้ ตู รเพอื่ ยา้ ยตาํ แหนง่ อา้ งองิ โดยอตั โนมตั ิแมส้ ตู ร Indirect ชว่ ยทําใหส้ ามารถเปลย่ี นตําแหน่งอา้ งองิ ไดก้ ต็ าม แตค่ ณุ ยงั คงตอ้ งเป็ นผู ้กําหนดตําแหน่งใหมล่ งไปเองทกุ ครัง้ ซงึ่ Excel ยงั มสี ตู รอน่ื อกี มากมายทที่ ําหนา้ ทเี่ ปลย่ี นยา้ ยตําแหน่งอา้ งองิ ไดเ้ ชน่ ทส่ี ตู ร Indirect ทําได ้ แถมยงั มคี วามสามารถเหนอื กวา่ เพราะสามารถเปลยี่ นยา้ ยตําแหน่งอา้ งองิ ตามเงอื่ นไขทก่ี ําหนดหรอื จะกําหนดใหเ้ ปลย่ี นยา้ ยตําแหน่งโดยอตั โนมตั ไิ ดอ้ กี ดว้ ย  สตู รทส่ี ามารถเปลยี่ นยา้ ยตําแหน่งอา้ งองิ ตามเงอื่ นไขทกี่ ําหนด เชน่ IF, Choose, Index, Offset  สตู รทสี่ ามารถเปลย่ี นยา้ ยตําแหน่งอา้ งองิ โดยอตั โนมตั ิ เชน่ VLookup, Match ที่ นํามาใชร้ ว่ มกบั สตู ร Index หรอื Offset, Address ทน่ี ํามาใชร้ ว่ มกบั สตู ร Indirectรายละเอยี ดการใชส้ ตู รเหลา่ น้ี จะนํามาอธบิ ายตอ่ ไปในภายหลงั ในบททเี่ กยี่ วขอ้ งกบั แตล่ ะสตู รโดยเฉพาะการกาํ หนดตาํ แหนง่ อา้ งองิ แบบวงกลม (Circular Reference)เมอ่ื แรกเห็นชอ่ื การกําหนดตําแหน่งอา้ งองิ แบบวงกลมน่าจะทําใหค้ ดิ ไปวา่ เป็ นชอื่ เรยี กทนี่ ่ารักดีแตเ่ มอื่ ใดทเ่ี กดิ การกําหนดตําแหน่งอา้ งองิ แบบวงกลมขน้ึ เมอื่ นัน้ แฟ้มงานของคณุ จะเขา้ ขา่ ยเสยี่ งทเี ดยี ววา่ จะใชแ้ ฟ้มนัน้ ตอ่ ไปไดห้ รอื ไม่ เพราะตําแหน่งอา้ งองิ แบบวงกลมอาจเกดิ ขน้ึ โดยไมต่ งั้ ใจโดยไมร่ ตู ้ วั หรอื เกดิ ขน้ึ อยา่ งเจตนาใหม้ นั เกดิ ขนึ้ ก็ได ้ ซง่ึ ถา้ ใครทใ่ี ช ้ Excel เป็ นประจําทกุ วนั น่าจะเคยเจอคําเตอื นตอ่ ไปนแี้ สดงขน้ึ บนหนา้ จอมาแลว้ บา้ ง“การกําหนดตําแหน่งอา้ งองิ แบบวงกลมอาจจะทําให ้ Excel คํานวณผดิ พลาด” นเี่ ป็ นประเด็นสําคญั ของคําเตอื นทแ่ี สดงขนึ้ บนจอทนี่ ่ากลวั ทส่ี ดุ ก็คอื คนบางคนทใี่ ช ้ Excel มานาน แตอ่ าจไมเ่ คยพบคําเตอื นตามหนา้ จอขา้ งบนน้ีเปิดขนึ้ มาแสดงสกั ครัง้ เดยี ว ซงึ่ เป็ นไปไดน้ อ้ ยมากทผี่ ใู ้ ช ้ Excel ไมเ่ คยพลัง้ เผลอสรา้ งสตู รผดิแบบ Circular มากอ่ น แตอ่ าจเป็ นไปไดว้ า่ ตวั เองไดส้ รา้ งสตู รแบบ Circular Reference นม้ี ากอ่ นแลว้ เพยี งแตว่ า่ เขาเหลา่ นัน้ สงั่ ให ้ Excel เลกิ เตอื น โดยเฉพาะแฟ้มงานทเ่ี ป็ นมรดกตกทอดใชต้ อ่ กนั มาหลายตอ่ หลายรนุ่ พอเปิดแฟ้มเขา้ ไปแกไ้ ขสตู รหรอื โยกยา้ ยเซลลท์ รี่ นุ่ พเี่ ขาสรา้ งไว ้โดยไมร่ จู ้ ักทไี่ ปทม่ี าของสตู รในเซลลเ์ หลา่ นัน้ กอ็ าจทําใหเ้ กดิ Circular Reference ไดท้ นั ที สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 53 of 147  ตวั อยา่ งการสรา้ งสตู รทมี่ ตี ําแหน่งอา้ งองิ แบบวงกลมทงี่ า่ ยทส่ี ดุ เชน่ ในเซลล์ A1 สรา้ งสตู ร=A1 ลงไป หรอื ในเซลล์ B3 สรา้ งสตู ร = B1+B2+B3 ลงไป หรอื สงั เกตงา่ ยๆวา่ สตู รในเซลล์ใดมตี ําแหน่งอา้ งองิ ทร่ี ะบถุ งึ เซลลน์ ัน้ เองอยดู่ ว้ ย ซง่ึ นอกจากนห้ี ากมสี ตู รท่ี link ตอ่ ๆกนั ไปหลายๆเซลลแ์ ลว้ กลบั สง่ คา่ กลบั มาในเซลลใ์ ดเซลลห์ นงึ่ ในวงจรของเซลลท์ ่ี link กนั เชน่เซลล์ A1 มสี ตู ร =A3 และเซลล์ A2 มสี ตู ร =A1 แลว้ เซลล์ A3 มสี ตู ร =A2 ยอ่ มทําใหเ้ กดิตําแหน่งอา้ งองิ แบบวงกลมไดเ้ ชน่ กนั ซงึ่ เรยี กอกี แบบหนง่ึ วา่ เกดิ การคํานวณแบบวงกลม(Circular Calculation)ในการดําเนนิ งานทางธรุ กจิ มงี านหลายประเภทซงึ่ อาจมเี งอ่ื นไขทจี่ ําเป็ นตอ้ งสรา้ งสตู รทม่ี กี ารกําหนดตําแหน่งอา้ งองิ แบบวงกลม อาทเิ ชน่  ในเงอื่ นไขการคํานวณตน้ ทนุ สนิ คา้ ซง่ึ นําคา่ ใชจ้ า่ ยทงั้ ทางตรงและทางออ้ มมา allocate เป็ นตน้ ทนุ ของสนิ คา้ จากนัน้ หากสนิ คา้ นัน้ กลบั ไปใชเ้ ป็ นวตั ถดุ บิ ใหก้ บั สนิ คา้ อน่ื แลว้ สดุ ทา้ ยกน็ ําสนิ คา้ อน่ื นัน้ มาใชเ้ ป็ นองคป์ ระกอบรว่ มกบั สนิ คา้ เดมิ อกี  หากบรษิ ัทรับจา่ ยภาษีเงนิ ไดบ้ คุ คลธรรมดาแทนพนักงาน ซงึ่ ตามกฎเกณฑท์ างภาษี อากรกําหนดใหน้ ําภาษีทจี่ า่ ยแทนนัน้ นํามาบวกเป็ นเงนิ ไดเ้ พม่ิ แลว้ เงนิ ไดร้ วมทเี่ พม่ิ ขน้ึ น้ี ก็ตอ้ งนํากลบั ไปยอ้ นคดิ ภาษีเงนิ ไดอ้ กี  คา่ ใชจ้ า่ ยบางรายการ อาจมขี อ้ กําหนดทางภาษีวา่ หา้ มนํามาหกั ภาษีซา้ํ อกี แตค่ า่ ใชจ้ า่ ย นัน้ กลบั มฐี านการคํานวณทเี่ กดิ ขน้ึ จากกําไรสทุ ธิ  ในกรณีของการกเู ้ งนิ ถา้ บรษิ ัทนําดอกเบย้ี จา่ ยเงนิ กไู ้ ปเพม่ิ ยอดเงนิ ทตี่ อ้ งกเู ้ พมิ่ ยอ่ มทํา ใหต้ อ้ งจา่ ยดอกเบย้ี เพม่ิ และทําใหต้ อ้ งกเู ้ พม่ิ  การของบประมาณคา่ ใชจ้ า่ ยทกี่ ําหนดวา่ จะใหต้ ามอตั รารอ้ ยละของกําไร ซง่ึ ถา้ กําไรเพมิ่ กจ็ ะทําใหไ้ ดง้ บประมาณคา่ ใชจ้ า่ ยเพม่ิ แตค่ า่ ใชจ้ า่ ยทเ่ี พมิ่ ขนึ้ ยอ่ มสง่ ผลใหก้ ําไรลด และทําใหไ้ ดง้ บประมาณคา่ ใชจ้ า่ ยลดลงตาม ซงึ่ กลบั ทําใหก้ ําไรเพมิ่ ขนึ้  งานคํานวณอนื่ ๆ เชน่ การควบคมุ ให ้ Excel เพมิ่ คา่ ตวั เลขครัง้ ละเทา่ ๆกนั โดยสรา้ งสตู ร =Number+A1 ลงไปในเซลล์ A1 หรอื การบนั ทกึ ประวตั ติ วั เลขสงู สดุ โดยสรา้ งสตู ร =Max(A1,NumberRange) ลงไปในเซลล์ A1หมายเหตุ แทนทจ่ี ะใชส้ ตู รทม่ี ตี ําแหน่งอา้ งองิ แบบวงกลม ซงึ่ ทําใหแ้ ฟ้มนัน้ มรี ะบบการคํานวณแบบพเิ ศษทตี่ อ้ งสงั่ ให ้ Excel ยอมรับกอ่ นจงึ จะคํานวณไดค้ ําตอบถกู ตอ้ ง เราสามารถใชค้ ําสง่ัอนื่ ทที่ ําหนา้ ทสี่ งั่ ให ้ Excel คํานวณซํา้ ได ้ เชน่ Goal Seek หรอื ใช ้ Solver Add-in หรอื ใชค้ ําสง่ัจาก Visual Basic ควบคมุ การคํานวณแทน สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 54 of 147  ขนั้ ตอนการสรา้ งสตู รคาํ นวณแบบ Circular Reference1. ใหเ้ ลอื กสรา้ งสตู รอนื่ ๆทคี่ ํานวณตามปกตทิ กุ สตู ร (ทไ่ี มต่ อ้ งใชก้ ารคํานวณแบบ Circular Reference) ใหเ้ สร็จกอ่ น แลว้ จงึ สรา้ งสตู รทต่ี อ้ งคํานวณแบบ Circular Reference เป็ นสตู ร สดุ ทา้ ย เนอื่ งจากหากตอ้ งการให ้ Excel คํานวณแบบ Circular เมอื่ ใด คณุ จะตอ้ งสงั่ ให ้ Excel เลกิ เปิดคําเตอื น ซง่ึ หากมกี ารสรา้ งสตู รผดิ พลาดเผลอไปสรา้ งสตู ร Circular Reference ขนึ้ โดยไมเ่ จตนา ก็ยอ่ มไมม่ กี ารเตอื นใหท้ ราบ2. เมอ่ื สรา้ งสตู รคํานวณแบบ Circular Reference แลว้ กดป่ มุ Cancel เพอื่ ปิดคําเตอื นบน หนา้ จอตามรปู ขา้ งตน้ แลว้ จะพบคําวา่ Circular References: ตอ่ ดว้ ยตําแหน่งเซลลแ์ สดง ขนึ้ ตรงขอบดา้ นลา่ งซา้ ยของจอ พรอ้ มทงั้ ในพนื้ ทตี่ ารางก็จะมเี สน้ ลกู ศรสนี ํ้าเงนิ ชตี้ ําแหน่ง เซลลท์ ท่ี ําใหเ้ กดิ การอา้ งองิ แบบวงกลม ซงึ่ หากตอ้ งการให ้ Excel แสดงชอื่ ตําแหน่งเซลลท์ ่ี เกยี่ วขอ้ งกบั การอา้ งองิ แบบวงกลมดว้ ย ใหส้ ง่ั Formulas > Error Checking > Circular References แลว้ จะพบตําแหน่งเซลลท์ เ่ี กยี่ วขอ้ งปรากฏตอ่ ทา้ ยคําสงั่ นี้3. เมอ่ื ตอ้ งการให ้ Excel ยอมรับการคํานวณแบบ Circular Reference ใหส้ ง่ั File > Options > Formulas > กาชอ่ ง Enable iterative calculation (Excel 2003 สง่ั Tools > Options > Calculation > กาชอ่ ง Iteration)4. ชอ่ ง Maximum Iterations และชอ่ ง Maximum Change นัน้ ไมจ่ ําเป็ นตอ้ งเขา้ ไปแกไ้ ข ตวั เลขใดๆ ซง่ึ 2 ชอ่ งนที้ ําหนา้ ทสี่ งั่ ให ้ Excel คํานวณสงู สดุ ถงึ 100 ครัง้ หรอื อาจไมถ่ งึ 100 ครัง้ ก็ไดโ้ ดยใหค้ ํานวณจนกวา่ จะพบวา่ คําตอบทคี่ ํานวณไดใ้ นแตล่ ะรอบใหค้ า่ แตกตา่ งกนั สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 55 of 147   นอ้ ยกวา่ .001 ก็จะหยดุ คํานวณ ซงึ่ การคํานวณเป็ นรอบดงั กลา่ วนจ้ี ะเกดิ ขน้ึ ทันทที มี่ กี าร สรา้ งสตู รนัน้ เสร็จแลว้ กดป่ มุ Enter รับสตู รลงไปหรอื เมอื่ มกี ารกดป่ มุ F9 เพอ่ื สงั่ ให ้ Excel คํานวณ 4.1. ใหเ้ พม่ิ ตวั เลขจํานวนรอบสงู สดุ ในชอ่ ง Maximum Iterations เมอ่ื พบวา่ ทกุ ครัง้ ทกี่ ดป่ มุ F9 เพอื่ สงั่ คํานวณ จะไดต้ วั เลขคําตอบตา่ งจากเดมิ ไปเสมอ ซง่ึ หากเพมิ่ ตวั เลขในชอ่ งน้ี แลว้ กดป่ มุ F9 พบวา่ ตวั เลขผลจากการคํานวณไมเ่ ปลยี่ นแปลงตอ่ ไปอกี ยอ่ มแสดงวา่ ไดค้ ําตอบสดุ ทา้ ยแลว้ 4.2. ใหป้ รับตวั เลขคา่ ความละเอยี ดในชอ่ ง Maximum Change ตอ่ เมอื่ ตอ้ งการให ้ Excel คํานวณละเอยี ดขน้ึ ซง่ึ ยอ่ มทําใหต้ อ้ งใชจ้ ํานวนครัง้ ในชอ่ ง Maximum Iterations เพมิ่ ขน้ึ ตามไปดว้ ย5. หลงั จากสงั่ คําสงั่ ตามขอ้ 3 แลว้ จะไมพ่ บขอ้ ความหรอื ตําแหน่งเซลลท์ แี่ สดงบนหนา้ จอตาม ตําแหน่งตา่ งๆทเ่ี คยแสดงไวต้ ามขอ้ 2 อกี ตอ่ ไป แตจ่ ะพบคําวา่ Calculate แสดงคา้ งอยตู่ รง ขอบจอดา้ นลา่ งซา้ ยของโปรแกรม Excel แทน และขอใหส้ งั เกตวา่ ทกุ ครัง้ ทกี่ ดป่ มุ F9 จะ ทําใหค้ ําวา่ Calculate นหี้ ายไปชวั่ ขณะหนงึ่ แลว้ กลบั มาแสดงใหม่ (โดย ไมเ่ กยี่ วขอ้ งกบั การสงั่ ใหค้ ํานวณแบบ Manual ทจี่ ะมคี ําวา่ Calculate แสดงขน้ึ มาเชน่ กนั แตเ่ มอื่ กดป่ มุ F9 เพอ่ื สงั่ คํานวณแบบ Manual จะทํา ใหค้ ําวา่ Calculate หายไปโดยไมก่ ลบั มาใหมอ่ กี )6. ในกรณีทตี่ อ้ งการตรวจสอบทไ่ี ปทม่ี าของสตู รทม่ี กี ารใชต้ ําแหน่งอา้ งองิ แบบวงกลมวา่ มี เซลลใ์ ดทเี่ กย่ี วขอ้ งบา้ ง ใหย้ อ้ นกลบั ไปตดั กาชอ่ ง Enable iterative calculation ทงิ้ ไป จากนัน้ Excel จะแสดงตําแหน่งเซลลท์ เี่ กยี่ วขอ้ งไวท้ คี่ ําสงั่ Formulas > Error Checking > Circular References ซงึ่ เซลลเ์ หลา่ นัน้ อาจจะเป็ นเซลลส์ ตู รทสี่ รา้ งไว ้ อยา่ งตงั้ ใจหรอื เผลอแกไ้ ขสตู รใหต้ า่ งจากเดมิ ไปจนทําใหเ้ กดิ Circular reference ขนึ้ ก็ได ้ จากนัน้ ใหท้ ยอยลบสตู รในเซลลด์ งั กลา่ วทงิ้ ไปทลี ะเซลล์ จนกวา่ จะพบวา่ เมอื่ ลบสตู รใน เซลลใ์ ดเสร็จแลว้ ทําใหไ้ มเ่ กดิ คําเตอื น Circular ตอ่ ไปอกี หลงั จากนัน้ จงึ ไลส่ รา้ งสตู รใหม่ ขน้ึ มาแทน ซงึ่ หลายๆครัง้ จะพบวา่ ตอ้ งไลล่ บสตู รในเซลลท์ ง้ิ แลว้ ตอ้ งสรา้ งสตู รใหมน่ ับรอ้ ย นับพันเซลลท์ เี ดยี ว โดยเฉพาะตารางคํานวณทต่ี อ้ งคํานวณรายการรายวนั หรอื รายเดอื นตอ่ กนั ไปเป็ นตารางขนาดใหญ่ ดงั นัน้ ผใู ้ ช ้ Excel ทกุ คนตอ้ งรตู ้ วั ทนั ทเี มอื่ ตวั เองเผลอสรา้ งสตู ร ทม่ี ตี ําแหน่งอา้ งองิ แบบวงกลมขนึ้ มาเป็ นครัง้ แรก หากปลอ่ ยไวก้ ็จะตอ้ งเสยี แรงเสยี เวลาไล่ ยอ้ นมาแกไ้ ขกนั ใหมท่ ลี ะเซลล์7. เซลลท์ ท่ี ําใหเ้ กดิ Circular น้ี ควรใสส่ ใี หต้ า่ งจากเซลลอ์ น่ื หรอื ทําเครอื่ งหมายพเิ ศษ หรอื เขยี นอธบิ ายกํากบั ไวด้ ว้ ยเพอ่ื เตอื นผใู ้ ชง้ านใหแ้ กไ้ ขสตู รอยา่ งระมดั ระวงั8. ควรตงั้ ชอ่ื แฟ้มทม่ี สี ตู รทม่ี ตี ําแหน่งอา้ งองิ แบบวงกลมใหม้ ชี อ่ื ทสี่ ะดดุ ตา จะไดไ้ มเ่ ผลอเปิด แฟ้มทต่ี อ้ งคํานวณแบบ Iterations นขี้ น้ึ มาใชง้ านพรอ้ มกบั แฟ้มอนื่ เพราะแฟ้มอนื่ ๆจะรับ คําสงั่ Iterations นต้ี ามไปดว้ ยโดยไมจ่ ําเป็ น9. หากปิดโปรแกรม Excel แลว้ เปิด Excel ขนึ้ มาใหม่ จะพบวา่ Excel ตดั กาชอ่ ง Enable iterative calculation ทง้ิ ไปใหเ้ สมอ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 56 of 147  เคล็ดลบั อน่ื ๆเกย่ี วกบั การใชต้ าํ แหนง่ อา้ งองิ1. ในกรณีตอ้ งการหายอดรวมทกุ ชที จากตําแหน่งเซลลเ์ ดยี วกนั ใหส้ รา้ งสตู รหายอดรวมแบบ 3 มติ ิ ซงึ่ จะอยใู่ นโครงสรา้ งสตู ร =SUM(ชอื่ ชที แรก:ชอ่ื ชที สดุ ทา้ ย!ตําแหน่งเซลล)์ เชน่ =SUM(Sheet1:Sheet3!A1) ทําหนา้ ทห่ี ายอดรวมของเซลล์ A1 ทอี่ ยใู่ นชที Sheet1 ถงึ Sheet3 ดงั นัน้ หากตอ้ งการใหร้ วมขอ้ มลู จากชที ใด ใหย้ า้ ยชที นัน้ ไปวางไวร้ ะหวา่ ง Sheet1 ถงึ Sheet3 1.1. ใหเ้ ลอื กเซลลค์ ําตอบ พมิ พ์ =sum( 1.2. คลกิ เซลลใ์ นชที แรกทต่ี อ้ งการ 1.3. กดป่ มุ Shift คา้ งไวแ้ ลว้ คลกิ ชที สดุ ทา้ ยทตี่ อ้ งการ 1.4. กดป่ มุ Enter2. หากตอ้ งการหายอดรวมจากทกุ ชที เวน้ ชที ทส่ี รา้ งสตู รนล้ี งไป ใหพ้ มิ พส์ ตู ร =SUM('*'! ตําแหน่งเซลล)์ เชน่ เมอื่ สรา้ งสตู ร =SUM('*'!A1) ลงไปในชที Sheet1 จะไดส้ ตู ร =SUM(Sheet2:Sheet3!A1)3. ในกรณีทตี่ อ้ งการปรับโครงสรา้ งของตารางในชที หนง่ึ ๆใหเ้ ป็ นหลายแบบ โดยไมจ่ ําเป็ นตอ้ ง สรา้ งชที ใหมส่ ําหรับโครงสรา้ งตารางแตล่ ะแบบ แตใ่ หป้ รับโครงสรา้ งในชที เดมิ ชที เดยี วนัน้ แลว้ สงั่ ให ้ Excel จดจําโครงสรา้ งหนา้ ตาตารางโดยสง่ั View > Custom Views > Add จากนัน้ ใหต้ ัง้ ชอื่ หนา้ ตาโครงสรา้ งตารางนัน้ ลงไป แลว้ เมอ่ื ใดทตี่ อ้ งการแสดงหนา้ ตาตารางชอ่ื ใดใหส้ ง่ั View > Custom Views แลว้ เลอื กชอื่ View ทเ่ี คยตงั้ ชอ่ื ไว ้ (วธิ นี สี้ ามารถตงั้ ชอ่ื View ขา้ มชที ไดด้ ว้ ย ทําใหไ้ มต่ อ้ ง พง่ึ ชอ่ื ชที แถมยงั ชว่ ยปรับ Print settings และ Filter settings ตามแบบทก่ี ําหนดไวใ้ นขณะทต่ี งั้ ชอ่ื ใหท้ นั ท)ี4. ขอใหอ้ อกแบบชที ทกุ ชที ทเี่ ก็บขอ้ มลู ประเภทเดยี วกนั ไวใ้ หใ้ ชต้ ําแหน่งเซลลเ์ ดยี วกนั ของ ทกุ ชที โดยไมต่ อ้ งคํานงึ วา่ บางชที จะมขี อ้ มลู ครบหรอื ไม่ จากนัน้ ใหส้ งั่ Hide รายการทไี่ มม่ ี ขอ้ มลู นัน้ ออกไป และใหใ้ ช ้ Custom View ชว่ ยบนั ทกึ แบบโครงสรา้ งตารางทงั้ กอ่ นและ หลงั การ Hide ไวด้ ว้ ย5. พยายามหลกี เลยี่ งการใชเ้ ซลลใ์ ดๆใน Column A และ Row 1 ทงั้ นเ้ี พอ่ื ทําใหภ้ าพตารางที่ แสดงบนจอดไู มอ่ ดึ อดั และยงั สามารถปรับขนาดความสงู ความกวา้ งของ Column A และ Row 1 เพอื่ ชว่ ยปรับตําแหน่งตารางในหนา้ กระดาษพมิ พไ์ ดด้ กี วา่ การกําหนด Margin แต่ เพยี งอยา่ งเดยี ว6. หา้ ม Merge เซลลท์ เี่ กยี่ วขอ้ งกบั การคํานวณ เพราะตําแหน่งอา้ งองิ ในสตู รทอ่ี า้ งถงึ เซลลท์ ่ี Merge กนั ไวน้ ัน้ อาจใหต้ ําแหน่งอา้ งองิ ทคี่ ลาดเคลอื่ นจากความเป็ นจรงิ เมอื่ มกี าร Copy สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 57 of 147   สตู รทอี่ า้ งองิ ถงึ ตําแหน่งเซลลท์ ี่ Merge ไปใชท้ เี่ ซลลอ์ น่ื ดงั นัน้ หากตอ้ งการปรับการแสดง ขอ้ มลู ใหเ้ ห็นวา่ อยกู่ ลางชว่ งเซลลเ์ ชน่ เดยี วกบั Merge แตว่ า่ เซลลแ์ ตล่ ะเซลลย์ งั คงเป็ น เซลลข์ องมนั เองโดยมไิ ดร้ วมเป็ นเซลลเ์ ดยี วอยา่ ง Merge แตอ่ ยา่ งใด ใหเ้ ลอื กชว่ งเซลลน์ ัน้ แลว้ คลกิ ขวา สง่ั Format Cells > Alignment > Center Across Selection7. ในกรณีทต่ี อ้ งการ Cut ตารางจากชที หนงึ่ ไปยงั ชที อน่ื หรอื แฟ้มอนื่ ใหเ้ รมิ่ จากเลอื กพน้ื ท่ี ตารางทต่ี อ้ งการจะ Cut ไปทงั้ หมด (โดยตอ้ งตรวจสอบวา่ เป็ นพน้ื ทต่ี ารางทงั้ หมดทมี่ สี ตู รที่ เกย่ี วขอ้ งกนั มฉิ ะนัน้ จะมเี ซลลส์ ตู รบางเซลลท์ กี่ ลายเป็ นสตู ร link ขา้ มแฟ้ม) จากนัน้ เมอ่ื ตอ้ งการ Paste สามารถเลอื ก Paste ทเี่ ซลลใ์ ดในชที ใหมก่ ไ็ ด ้ โดย Excel จะปรับตําแหน่ง อา้ งองิ ตามตําแหน่งในชที ใหมใ่ หเ้ อง8. ในกรณีทตี่ อ้ งการ Copy ตารางจากชที หนงึ่ ไปยงั ชที อน่ื หรอื แฟ้มอนื่ ใหเ้ รม่ิ จากเลอื กพนื้ ท่ี ตารางทตี่ อ้ งการจะ Copy จากนัน้ เมอ่ื ตอ้ งการ Paste ใหเ้ ลอื กเซลลใ์ นชที ใหมท่ เ่ี ป็ นเซลล์ เดยี วกบั เซลลแ์ รกของตารางท่ี Copy ไวก้ อ่ น แลว้ จงึ สงั่ Paste9. หากตอ้ งการป้องกนั ไมใ่ หผ้ อู ้ น่ื นําแฟ้มไปแกไ้ ขสตู ร ไมจ่ ําเป็ นตอ้ ง Protect Sheet กไ็ ด ้ แต่ ใหส้ งั่ ตดั เครอ่ื งหมาย $ ออกจากเซลลส์ ตู รทกุ เซลลโ์ ดยใชค้ ําสงั่ Replace เพอ่ื ทําใหส้ ตู ร ทงั้ หมดถกู แกไ้ ขหรอื สรา้ งใหมไ่ ดย้ ากมาก (น่าสงั เกตวา่ พอตดั เครอื่ งหมาย $ ทงิ้ ไป แฟ้มจะ มขี นาดใหญข่ นึ้ )10. หากตอ้ งการป้องกนั การ Insert Row หรอื Insert Column ในพน้ื ทชี่ ว่ งใด ใหส้ รา้ งสตู ร Array ลงไปพรอ้ มกนั เป็ นรัว้ ขอบเขตนัน้ เชน่ เลอื กเซลล์ A1:A10 แลว้ พมิ พส์ ตู ร =0 หรอื =”” (=”” น้ี จะกลายเป็ นรัว้ ไฟฟ้าทมี่ องไมเ่ ห็นขอ้ มลู ในเซลลร์ ัว้ นัน้ ดว้ ย)แลว้ กดป่ มุ Ctrl+Shift+Enter ลงไป จะไดร้ ัว้ ป้องกนั การ Insert Row ในชว่ ง Row 1 ถงึ Row 1011. หากตอ้ งการใช ้ Windows Explorer ในการ copy แฟ้ม Excel ท่ี link กนั หลายๆแฟ้มไปเกบ็ ที่ drive อนื่ หรอื โฟลเดอรอ์ น่ื ให ้ Copy ทงั้ โฟลเดอรท์ เี่ กบ็ แฟ้มท่ี link กนั นัน้ ไปพรอ้ มกนั ทงั้ หมด เชน่ ใน ถา้ เกบ็ แฟ้มท่ี link กนั ไวใ้ นโฟลเดอรช์ อ่ื MyExcelFile ก็ให ้ Copy ตวั โฟลเดอร์ MyExcelFile ซง่ึ มแี ฟ้มท่ี link กนั อยภู่ ายในนัน้ ไปทัง้ ชดุ โดยในโฟลเดอรช์ อ่ื MyExcelFile อาจมโี ฟลเดอรย์ อ่ ยสรา้ งไวด้ ว้ ยก็ได ้ เมอ่ื Copy ไปไวท้ ี่ drive อนื่ แลว้ คณุ จะ เปลย่ี นชอ่ื โฟลเดอร์ MyExcelFile เป็ นชอื่ อนื่ กไ็ ด ้ แตห่ า้ มเปลย่ี นชอ่ื โฟลเดอรย์ อ่ ยเพราะสตู ร link ในแฟ้ม Excel จะจําชอื่ โฟลเดอรย์ อ่ ยไวใ้ นสตู ร (แตไ่ มจ่ ําชอื่ โฟลเดอร์ MyExcelFile) สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 58 of 147  12. แทนทจ่ี ะจดั เก็บแฟ้มไวใ้ นชอื่ เดมิ ขอใหจ้ ดั เกบ็ แฟ้มในชอื่ อน่ื ทกุ ครัง้ ทมี่ กี ารแกไ้ ขใดๆใน แฟ้มนัน้ ทงั้ นเ้ี พอื่ ทําใหม้ แี ฟ้มเกา่ เกบ็ ไวห้ ลายๆรนุ่ ดกี วา่ การมแี ฟ้มรนุ่ เดยี วแฟ้มเดยี ว ซง่ึ เสยี่ งหากแฟ้มนัน้ อาจเสยี หายจนเปิดไมไ่ ด ้แป้ นพมิ พล์ ดั ทเ่ี กย่ี วขอ้ งกบั ตําแหนง่ อา้ งองิ และขอบเขตพนื้ ทตี่ ารางF2 ใชแ้ กไ้ ขขอ้ มลู หรอื สตู รในเซลล์F3 ใชใ้ สช่ อื่ ตําแหน่งเซลลห์ รอื ชอ่ื สตู รทตี่ งั้ ไวใ้ นแฟ้มนัน้ ๆลงไปในสตู ร หรอื สงั่ Paste List ตอ่ จะสรปุ ชอ่ื ทตี่ งั้ ไวท้ งั้ หมดลงไปในตารางF4 ใชใ้ สเ่ ครอ่ื งหมาย $ กํากบั ตําแหน่งอา้ งองิ หรอื สงั่ ทวนคําสงั่ สดุ ทา้ ยซํ้าF5 ใชห้ าเซลล์ ตามตําแหน่งหรอื ชอ่ื ตําแหน่งเซลลท์ ท่ี ราบ หรอื สง่ั Special ตอ่ จะคน้ หาตําแหน่งเซลลไ์ ดอ้ กี หลายแบบF8 ใชค้ วบคมุ ตําแหน่งเซลลแ์ รกไวไ้ มใ่ หข้ ยบั เพอ่ื ขยายตาราง (Extend)F9 ใชส้ ง่ั คํานวณทกุ ชที ทกุ แฟ้มทเ่ี ปิดขน้ึShift + F9 ใชส้ ง่ั คํานวณเฉพาะชที ทเี่ ลอื กอยชู่ ที เดยี วเทา่ นัน้F2 ตามดว้ ย F9 ใชแ้ กะสตู รในเซลลเ์ พอื่ ดวู า่ มคี า่ ทแี่ ทจ้ รงิ เป็ นอยา่ งไร แลว้ กด EscCtrl + * หรอื Ctrl+a ใชเ้ ลอื กพน้ื ทตี่ ารางซงึ่ มขี อ้ มลู ตดิ ตอ่ กนั ไป (Current Region)Ctrl + . ใชย้ า้ ย Active Cell ไปตามเซลลห์ วั มมุ ของตารางทเี่ ลอื กไว ้Ctrl + Shift +  ใชเ้ ลอื กพนื้ ทต่ี ารางจากเซลลท์ เี่ ลอื กไวไ้ ปจนสดุ ทางดา้ นขวาShift + End  หรอื ตามทศิ ทางของลกู ศรทก่ี ดCtrl + PgUp ใชเ้ ลอื กชที ถดั ไปCtrl + PgDn ใชเ้ ลอื กชที กอ่ นหนา้Ctrl + Tab ใชเ้ ลอื กแฟ้มถดั ไปทเ่ี ปิดอยู่Ctrl + Shift + Tab ใชเ้ ลอื กแฟ้มกอ่ นหนา้ ทเ่ี ปิดอยู่Ctrl + Shift + Enter ใชส้ รา้ งสตู ร Array ซงึ่ จะมเี ครอ่ื งหมาย { } ปิดหวั ทา้ ยสตู ร สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 59 of 147   วธิ ใี ช้ Excel วเิ คราะหท์ างออกทเ่ี ป็ นไปไดท้ างธรุ กจิการประกอบธรุ กจิ ยคุ นท้ี ําไดย้ ากกวา่ ยคุ กอ่ นมาก แมจ้ ะสามารถระดมสมองรวมพลงั ผมู ้ คี วามรู ้ความสามารถมาชว่ ยคดิ ชว่ ยทําไดก้ ็ตาม แตค่ แู่ ขง่ ทางการคา้ ก็สามารถคดิ แบบเดยี วกนั ได ้เชน่ กนั นาย Bill Gates ผกู ้ อ่ ตงั้ บรษิ ัท Microsoft ถงึ กบั กลา่ ววา่ ในยคุ กอ่ นผทู ้ มี่ ขี อ้ มลู คอื ผทู ้ ม่ี ีอํานาจ แตเ่ ดย๋ี วนเ้ี รามขี อ้ มลู มากเสยี จนไมส่ ามารถนําขอ้ มลู มาใชป้ ระโยชนไ์ ดเ้ ต็มท่ี ผทู ้ ส่ี ามารถใชข้ อ้ มลู เหลา่ นัน้ ไดเ้ ร็วทสี่ ดุ ตา่ งหากคอื ผทู ้ จ่ี ะมอี ํานาจในปัจจบุ นั ความเร็วทวี่ า่ นกี้ ต็ อ้ งรวดเร็วแบบฉับพลนั ทนั ที ใครก็ตามทส่ี ามารถใชข้ อ้ มลู เพอ่ื ตดั สนิ ใจแลว้ ฉวยโอกาสหาทางออกไดเ้ ร็วกวา่ ผนู ้ ัน้ จงึ จะเป็ นผชู ้ นะโปรแกรม Microsoft Excel มเี ครอื่ งมอื หลายอยา่ งทเี่ ราสามารถนํามาชว่ ยในการตดั สนิ ใจหาทางออกใหก้ บั ธรุ กจิ ไดแ้ ก่ Goal Seek, Data Table, Scenarios, Solver, และ Pivot Table  เครอื่ งมอื ทใ่ี ชว้ เิ คราะหห์ าทางออกในอนาคต ไดแ้ ก่ Goal Seek, Data Table, Scenarios, และ Solver เรยี กวา่ เป็ นเครอื่ งมอื ทใ่ี ชใ้ นงาน Sensitivity Study (หรอื Sensitivity Analysis หรอื ใน Excel 2007 เรยี กเครอื่ งมอื นว้ี า่ What-If Analysis) ซงึ่ เป็ นเนอื้ หาทจ่ี ะนํามาเลา่ สกู่ นั ฟังในบทความนี้  เครอ่ื งมอื ทใ่ี ชว้ เิ คราะหข์ อ้ มลู ในอดตี มาดผู ลในปัจจบุ นั ไดแ้ ก่ Pivot Tableเรมิ่ จากแยกตวั แปรคอื ประตสู ทู่ างออกหากในเซลลห์ นง่ึ มสี ตู รทสี่ รา้ งไวเ้ ป็ น =2*3 ชว่ ยกนั พจิ ารณาดวู า่ การสรา้ งสตู รแบบนไี้ มด่ ีอยา่ งไรสตู รทพี่ มิ พค์ า่ คงทเ่ี ป็ นตวั เลข 2 ตวั เลข 3 แลว้ นํามาคณู กนั แบบนมี้ ชี อื่ เรยี กวา่ Hard Code แปลตามตวั หนังสอื ก็มคี วามหมายวา่ เป็ นรหสั ทย่ี ากตอ่ การแกไ้ ข เพราะถา้ วนั หนงึ่ คณุ ตอ้ งการแกไ้ ขสตู รจากเดมิ =2*3 เป็ น =5*3 วนั นัน้ คณุ ก็ตอ้ งเปิดแฟ้มขน้ึ มาแลว้ คน้ หาใหพ้ บกอ่ นวา่ เซลลท์ มี่ ีสตู ร =2*3 ทต่ี อ้ งการแกไ้ ขนัน้ อยทู่ เี่ ซลลใ์ ดชที ใด แลว้ ก็ตอ้ งคลกิ เขา้ ไปแกส้ ตู รใหมเ่ ป็ น =5*3ถา้ ในแฟ้มนัน้ มเี ซลลส์ ตู รคํานวณอยนู่ ับพันนับหมน่ื เซลล์ กวา่ จะคน้ หาจนพบเซลลท์ ต่ี อ้ งการ ก็ตอ้ งเสยี เวลาไมใ่ ชน่ อ้ ย ยงิ่ มหี ลายเซลลซ์ ง่ึ มสี ตู ร =2*3 เหมอื นกนั ยง่ิ เกดิ ความเสยี่ งหากเผลอไปแกไ้ ขผดิ เซลล์ และความผดิ พลาดเล็กนอ้ ยแบบนแี้ หละทจี่ ะปิดประตสู ทู่ างออกของคณุคอมพวิ เตอรจ์ ะเป็ นคอมพวิ เตอรไ์ ดอ้ ยา่ งสมบรู ณ์และคณุ สามารถนํา Goal Seek, Data Table,Scenarios, และ Solver มาใชไ้ ดต้ อ้ งเรม่ิ จากการแยกตวั แปรออกนอกสตู รไวก้ อ่ น ดงั นัน้ แทนที่จะสรา้ งสตู ร =2*3 ลงไป ใหเ้ ลอื กเซลลร์ ับตวั เลข 2 และอกี เซลลห์ นง่ึ รับตวั เลข 3 จากนัน้ จงึ นําเซลลท์ งั้ สองมาคณู กนั ไดผ้ ลลพั ธเ์ ป็ นเลข 6 สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 60 of 147    เซลล์ B2 เป็ นเซลลร์ ับตวั แปร มคี า่ เป็ นเลข 2  เซลล์ C2 เป็ นเซลลร์ ับตวั แปร มคี า่ เป็ นเลข 3  เซลล์ D2 เป็ นเซลลส์ ตู รคํานวณหาผลลพั ธ์ มสี ตู ร =B2*C2หลกั การแยกตวั แปร 1. ตวั แปร คอื คา่ ทมี่ นุษยส์ ามารถกําหนดใหม้ คี า่ เปลยี่ นแปลงตา่ งไปจากเดมิ ตวั แปรอาจ เป็ นไดท้ ัง้ คา่ ทเ่ี ป็ นตวั เลข ตวั อกั ษร หรอื แมแ้ ตส่ ตู ร 2. เซลลร์ ับตวั แปรอาจใชเ้ ซลลเ์ ดยี วหรอื หลายเซลล์ ขนึ้ กบั ประเภทของตวั แปรนัน้ ๆวา่ มกี าร เปลย่ี นแปลงไปตามระยะเวลาหรอื เงอ่ื นไขอน่ื ใดหรอื ไม่ ถา้ เป็ นคา่ คงทซี่ ง่ึ ใชค้ า่ เดยี วไป ตลอด กส็ ามารถใชเ้ ซลลร์ ับคา่ เพยี งคา่ เดยี วเซลลเ์ ดยี วในแฟ้มหนง่ึ แตถ่ า้ เป็ นคา่ ทมี่ ี ความผันผวนเปลยี่ นแปลงไปตามระยะเวลาก็จําเป็ นตอ้ งใชห้ ลายเซลล์ เชน่ ตวั แปร ประเภทราคาสนิ คา้ ควรกําหนดใหแ้ ตล่ ะชว่ งเวลามรี าคาสนิ คา้ แตกตา่ งกนั ไปเพอ่ื ใหเ้ กดิ ความยดื หยนุ่ ในการคํานวณ ก็จําเป็ นตอ้ งใชห้ ลายเซลลเ์ พอื่ รับตวั แปรประเภทราคา สนิ คา้ ทมี่ ยี อดตา่ งกนั ไปในแตล่ ะชว่ งเวลา 3. ควรคดิ เผอื่ ไวต้ งั้ แตต่ น้ เสมอวา่ คา่ คงทท่ี กุ คา่ เป็ นตวั แปรทคี่ า่ อาจเปลยี่ นแปลง เพราะ คา่ คงทใ่ี ดๆทคี่ ดิ วา่ คงทใี่ นวนั นี้ อาจเป็ นไปไดว้ า่ ไมใ่ ชค่ า่ ทจ่ี ะคงทไ่ี ปตลอด ดงั นัน้ หาก ไมม่ น่ั ใจวา่ จะคงทไี่ ปตลอดหรอื ไม่ ควรออกแบบตารางตงั้ แตแ่ รกเผอื่ ไวใ้ หส้ ามารถ เปลยี่ นแปลงตวั แปรตามระยะเวลาไดด้ ว้ ย เชน่ อตั ราภาษีธรุ กจิ เฉพาะ ซงึ่ อาจถกู ปรับเปลยี่ นใหต้ า่ งไปจากเดมิ ในอนาคต 4. ลกู เลน่ ทางธรุ กจิ เป็ นตวั แปรไดเ้ หมอื นกนั อยา่ คดิ วา่ วธิ ดี ําเนนิ การทางธรุ กจิ ตอ้ งเป็ นแบบ ปัจจบุ นั ไปตลอด คณุ ตอ้ งเผอ่ื เซลลร์ ับตวั แปรทอี่ าจเกดิ ขน้ึ หรอื ไมเ่ กดิ ขน้ึ ไวก้ อ่ นตงั้ แต่ แรก ดกี วา่ ทจ่ี ะยอ้ นกลบั ไปแกไ้ ขตารางแทรกตวั แปรเพม่ิ ในภายหลงั ขอใหอ้ อกแบบ โครงสรา้ งตารางเผอ่ื ไวก้ อ่ นดกี วา่ ขาด 5. ในกรณีทตี่ วั แปรมเี งอื่ นไขซบั ซอ้ น ควรแยกตวั แปรประเภทนัน้ ไปเกบ็ เป็ นตารางเฉพาะ หรอื เป็ นชที เฉพาะหรอื แมแ้ ตเ่ ป็ นแฟ้มเฉพาะสําหรับตวั แปรนัน้ ๆ โดยใหอ้ อกแบบตาราง ตามแบบโครงสรา้ งฐานขอ้ มลู ทด่ี ดี ว้ ย 6. ควรใสส่ สี รรคใ์ หเ้ ห็นเซลลร์ ับตวั แปรแยกชดั เจนแตกตา่ งจากเซลลส์ ตู รคํานวณและ กําหนดให ้ Excel เปิดรับคา่ ใหมไ่ ด ้ โดยคลกิ ขวาลงไปในเซลลต์ วั แปรนัน้ สง่ั Format สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 61 of 147   Cells > Protection แลว้ ตดั กาชอ่ ง Locked ทง้ิ ไป จากนัน้ สง่ั Review > Protect Sheet (Excel 2003 สงั่ Tools > Protection > Protect Sheet) แลว้ จะพบวา่ เมอ่ื กด ป่ มุ Tab จะยา้ ยตําแหน่งเซลลไ์ ปตามเซลลท์ เ่ี ป็ นตวั แปรใหเ้ องวธิ ใี ช้ Goal Seekเลขอะไรเอย่ นําไปคณู กบั เลข 3 แลว้ ไดผ้ ลลพั ธเ์ ป็ นเลข 30 คณุ ก็ตอ้ งตอบวา่ เลข 10 เพราะคณุคดิ สตู รคณู ในใจออกมาไดท้ นั ทเี ลยใชไ่ หมวา่ 10*3 = 30 แตถ่ า้ เปลย่ี นโจทยใ์ หมว่ า่ เลขอะไรเอย่ นําไปคณู กบั เลข 3 แลว้ ไดผ้ ลลัพธเ์ ป็ นเลข 77.77 คราวนสี้ มองของคณุ กจ็ ะคดิ ในใจอยา่ งรวดเร็ววา่ “ไมไ่ หว คดิ ไมไ่ หว” ถา้ ตอ้ งเสยี เวลากดเครอ่ื งคดิ เลขหรอื สรา้ งสตู รเอาเลข 77.77 ตงั้แลว้ หารดว้ ย 3 เพอื่ หาเลขอะไรเอย่ ตวั นัน้ กไ็ ด ้ ยอ่ มแสดงวา่ คณุ ใช ้ Goal Seek ไมเ่ ป็ นGoal Seek เป็ นเครอ่ื งมอื ทชี่ ว่ ยคดิ ยอ้ นโดยคณุ ไมต่ อ้ งเสยี เวลาสรา้ งสตู รใหมเ่ พอ่ื คดิ ยอ้ นเองแมแ้ ตน่ อ้ ย เพยี งแตว่ า่ Goal Seek จะทํางานไดต้ อ่ เมอ่ื มเี ซลลร์ ับตวั แปรทส่ี ามารถถกู Excel ทําหนา้ ทเ่ี ปลยี่ นแปลงเป็ นเลขใหมต่ ามตอ้ งการ แตห่ ากเป็ นเซลลส์ ตู รจะไมส่ ามารถใช ้ Goal Seekเขา้ ไปแกไ้ ขสตู รใดๆในเซลลใ์ หเ้ ป็ นคา่ ใหมไ่ ด ้จากโจทยเ์ ดมิ เลขอะไรเอย่ นําไปคณู กบั เลข 3 แลว้ ไดผ้ ลลพั ธเ์ ป็ นเลข 77.77 1. สรา้ งตวั อยา่ ง 2*3 ได ้ 6 ตามตวั อยา่ งทผ่ี า่ นมา 2. สง่ั Data > What-If Analysis > Goal Seek (Excel 2003 สง่ั Tools > Goal Seek) 3. คลกิ ในชอ่ ง Set cell: แลว้ ใหค้ ลกิ ตอ่ ไปทเ่ี ซลล์ D2 โดยเซลลน์ ต้ี อ้ งเป็ นเซลลส์ ตู ร คํานวณ สว่ นชอ่ ง To value: ใหพ้ มิ พเ์ ลข 77.77 ลงไป แลว้ ใหค้ ลกิ ในชอ่ ง By changing cell: แลว้ คลกิ ตอ่ ไปทเ่ี ซลล์ B2 โดยเซลล์ B2 นต้ี อ้ งเป็ นเซลลร์ ับตวั แปรท่ี กําลงั ตอ้ งการหา ถอื เป็ นตวั เลขคําตอบใหมท่ ตี่ อ้ งการ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 62 of 147   4. กดป่ มุ OK จะพบวา่ Excel เปลย่ี นตวั เลข 2 ในเซลล์ B2 เป็ นตวั เลข 25.92333 ซงึ่ ทํา ใหเ้ กดิ ผลลพั ธท์ เ่ี ซลล์ C2 เป็ นเลข 77.77 ตามตอ้ งการ แลว้ ใหก้ ดป่ มุ OK เพอ่ื ยอมรับ คําตอบท่ี Goal Seek หาให ้ 5. หากตอ้ งการหาตวั เลขคําตอบทต่ี า่ งจากเดมิ ตอ้ งสง่ั Data > What-If Analysis > Goal Seek ซํา้ เองใหม่คราวนมี้ าลองใช ้ Goal Seek กบั ปัญหาทยี่ ากขนึ้ โดยใหห้ าวา่ ตวั เลขคา่ โฆษณาในเซลล์ G18ตอ้ งเป็ นเทา่ ใด จงึ จะคดิ เป็ น 15% ของกําไรขนั้ ตน้จากภาพตารางขา้ งบนน้ี เซลล์ G16 และเซลล์ H17 เป็ นเซลลร์ ับตวั แปร ซง่ึ มคี า่ เป็ นตวั เลขยอดขาย 1,000 บาทและตวั เลขเปอรเ์ ซน็ ตต์ น้ ทนุ ขายเทา่ กบั 20% ตามลําดบั สว่ นเซลลส์ ตู รคํานวณมใี นเซลล์ G17 มสี ตู ร =H17*G16 เพอ่ื หายอดตน้ ทนุ ขายเทา่ กบั 200 บาท (คดิ เป็ น20% ของยอดขาย) สว่ นเซลล์ G19 เป็ นเซลลส์ ตู รคํานวณหากําไรขนั้ ตน้ มสี ตู ร =G16-G17-G18 มาจากยอดขายหกั ตน้ ทนุ ขายและหกั คา่ โฆษณา เซลลท์ เี่ วน้ วา่ งไวค้ อื เซลล์ G18 ใหใ้ ชห้ าคา่ โฆษณา ซง่ึ ตอนเรมิ่ ตน้ นเ้ี วน้ วา่ งไว ้ จงึ ทําใหเ้ ซลล์ H18 ซง่ึ มสี ตู รคํานวณหาสดั สว่ นรอ้ ยละของคา่ โฆษณาตอ่ กําไรขนั้ ตน้ =G18/G19 ซง่ึ ขณะนค้ี นื คา่ เทา่ กบั 0ตวั อยา่ งนไี้ มส่ ามารถใชว้ ธิ คี ดิ งา่ ยๆวา่ ในเมอื่ ตอ้ งการคา่ โฆษณาเป็ น 15% ของกําไรขนั้ ตน้ ก็ให ้สรา้ งสตู ร =.15*G19 ลงในในเซลล์ G18 กส็ นิ้ เรอื่ ง เพราะหากสรา้ งสตู ร =.15*G19 ลงไปในเซลล์ G18 จะพบวา่ เกดิ การอา้ งองิ แบบวงกลม (ตอ้ งหนั ไปใชก้ ารคํานวณแบบ Iteration เขา้ มาชว่ ย ซง่ึ เป็ นอกี วธิ หี นงึ่ ในการแกป้ ัญหาน)ี้ เพราะหากคา่ โฆษณาเพม่ิ จะทําใหก้ ําไรลด แลว้ พอกําไรลด ยอ่ มสง่ ผลใหค้ า่ โฆษณาลดตาม พอคา่ โฆษณาลดลง กําไรก็ตอ้ งเพมิ่ กลายเป็ นการคํานวณแบบวงกลมทไี่ มส่ ามารถหาคําตอบสดุ ทา้ ยไดโ้ ดยวธิ ธี รรมดาทว่ั ไปคณุ สามารถแกป้ ัญหานโี้ ดยใช ้ Goal Seek โดยเรมิ่ ตน้ จากสง่ั สง่ั Data > What-If Analysis >Goal Seek สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 63 of 147   1. คลกิ ในชอ่ ง Set cell: แลว้ ใหค้ ลกิ ตอ่ ไปทเี่ ซลล์ H18 โดยเซลลน์ เี้ ป็ นเซลลส์ ตู ร คํานวณหาวา่ คา่ โฆษณาเป็ นรอ้ ยละเทา่ ใดของกําไรขนั้ ตน้ =G18/G19 2. ชอ่ ง To value: ใหพ้ มิ พเ์ ลข .15 ซง่ึ เป็ นตวั เลขคําตอบทต่ี อ้ งการ 3. คลกิ ในชอ่ ง By changing cell: แลว้ คลกิ ตอ่ ไปทเ่ี ซลล์ G18 เพอื่ ใหห้ าตวั เลขคา่ โฆษณา 4. กดป่ มุ OK > OK จะไดต้ วั เลขคําตอบคา่ โฆษณาเทา่ กบั 104.50377964375 ท่ี Goal Seek คดิ ให ้Goal Seek เป็ นเครอ่ื งมอื พสิ ดารทเ่ี หมาะในการวางแผนงบประมาณ ชว่ ยตงั้ ราคาขายสนิ คา้ เพอื่ทําใหไ้ ดก้ ําไรตามตอ้ งการ หรอื ชว่ ยในการคํานวณยอ้ นกลบั ในงานทค่ี นเราคดิ เองไมไ่ หวได ้อยา่ งไมน่ ่าเชอ่ื ซง่ึ ในงานวางแผนตัดสนิ ใจนัน้ แทบเป็ นไปไมไ่ ดเ้ ลยทเ่ี ราจะมคี วามเชอ่ื มน่ั100% ตอ่ คําตอบทม่ี อี ยเู่ สมอไป ดงั นัน้ แทนทจี่ ะเสยี่ งกบั การใชต้ วั เลขเพยี งตวั เดยี ว ขอแนะนําใหใ้ ช ้ Goal Seek คดิ ยอ้ นหาชว่ งคา่ ทเ่ี ป็ นไปไดว้ า่ อยใู่ นชว่ งตวั เลขระหวา่ งคา่ ใด เพอื่ ทําให ้สามารถวางแผนไดอ้ ยา่ งยดื หยนุ่ มากขนึ้ เชน่ เดมิ เคยถามวา่ ตวั เลขคา่ โฆษณาในเซลล์ G18ตอ้ งเป็ นเทา่ ใด จงึ จะคดิ เป็ น 15% ของกําไรขนั้ ตน้ ก็ใหใ้ ช ้ Goal Seek คน้ หาคําตอบทยี่ ดื หยนุ่กวา่ เดมิ วา่ ตวั เลขคา่ โฆษณาในเซลล์ G18 ตอ้ งเป็ นคา่ ระหวา่ งเลขใด จงึ จะคดิ เป็ น 15% -20% ของกําไรขนั้ ตน้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 64 of 147  ขอ้ จาํ กดั ของ Goal Seek และวธิ แี กไ้ ขขอ้ จํากดั ทสี่ ําคญั ของ Goal Seek เกดิ จากทกุ ครัง้ ทต่ี อ้ งการคดิ ยอ้ นครัง้ ใหม่ คณุ ตอ้ งเป็ นผสู ้ งั่ ให ้Goal Seek ทํางานซา้ํ ดว้ ยตนเอง และ Goal Seek จะยอมใหเ้ ปลยี่ นแปลงตวั เลขตวั แปรไดเ้ พยี งเซลลเ์ ดยี ว นอกจากนสี้ ว่ นทไี่ มย่ ดื หยนุ่ ทสี่ ดุ ก็คอื คณุ ตอ้ งกรอกตวั เลขทตี่ อ้ งการลงไปในชอ่ งTo value เองเสมอ โดยไมส่ ามารถ link คา่ ตวั เลขเป้าหมายทต่ี อ้ งการจากเซลลเ์ ขา้ ไปใชใ้ นGoal Seek จงึ เป็ นเครอ่ื งมอื ทไี่ มเ่ หมาะสําหรับผทู ้ ไี่ มเ่ คยใช ้ Goal Seek มากอ่ นเราสามารถแกไ้ ขจดุ ออ่ นของ Goal Seek ใหส้ ามารถรับคา่ จากเซลลไ์ ดโ้ ดยตรง และหาทางปรับปรงุ ให ้ Goal Seek ทํางานหาคําตอบใหเ้ องทันทอี ยา่ งอตั โนมตั ิ โดยเรมิ่ จากใช ้ MacroRecorder บนั ทกึ การสงั่ Data > What-If Analysis > Goal Seek แลว้ ใชง้ านตามตวั อยา่ งการคํานวณหาคา่ โฆษณาในตวั อยา่ งทผ่ี า่ นมา จะไดร้ หสั VBA ตามน้ี Sub SetGoal() Range(\"H18\").GoalSeek Goal:=0.15, ChangingCell:=Range(\"G18\") End Subจากนัน้ ใหแ้ กไ้ ขตําแหน่งอา้ งองิ และคา่ คงทใ่ี นตวั รหสั ใหอ้ า้ งถงึ ชอ่ื Range Name ทต่ี งั้ ไวใ้ นตารางกลายเป็ นรหสั ใหมด่ งั นี้ Sub SetGoal() [MyTarget].GoalSeek Goal:=[TargetValue], ChangingCell:=[ChangeCell] End Subโดยกําหนดใหต้ งั้ ชอื่ Range Name ชอื่ วา่ MyTarget ใหก้ บั เซลล์ H18 และชอ่ื ChangeCellใหก้ บั เซลล์ G18 และเมอ่ื ใดทต่ี อ้ งการตวั เลขตวั ใหม่ ใหก้ รอกตวั เลขลงไปในเซลลท์ ตี่ งั้ ชอื่ วา่TargetValueหากตอ้ งการความสะดวกมากขน้ึ ควรสง่ั ให ้ Goal Seek ทํางานเองโดยใช ้ Event กจ็ ะได ้ผลลพั ธท์ นั ทโี ดยไมต่ อ้ งเสยี เวลามาเปิด Goal Seek ขนึ้ เพอื่ กรอกคา่ เองอกี ตอ่ ไป เชน่ หากใช ้Calculation Event กจ็ ะไดผ้ ลลพั ธต์ ามการคํานวณใดๆทเี่ กดิ ขน้ึ ในชที นัน้ Private Sub Worksheet_Calculate() [MyTarget].GoalSeek Goal:=[TargetValue], ChangingCell:=[ChangeCell] End Sub สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 65 of 147  วธิ ใี ช้ Data Tableถา้ ถามวา่ ตอนเรม่ิ ตน้ ใชส้ ตู รคํานวณใน Excel แรกเรม่ิ นัน้ คณุ ตอ้ งรจู ้ กั ใชอ้ ะไรใหเ้ ป็ น กต็ อบวา่ตอ้ งรจู ้ กั การกําหนดตําแหน่งอา้ งองิ โดยการใชเ้ ครอื่ งหมาย $ ควบคมุ แนวของคา่ ทใี่ ชใ้ นการคํานวณ แลว้ ถา้ ถามตอ่ ไปวา่ ตอนสรา้ งแฟ้มงานเสร็จแลว้ ละ่ ตอ้ งใชอ้ ะไรใหเ้ ป็ น คําตอบกค็ อืตอ้ งใช ้ Data Table สรปุ คําตอบทเ่ี ป็ นไปไดเ้ พอื่ ใชต้ ดั สนิ ใจData Table เป็ นคําสงั่ ทม่ี มี านานแลว้ แตม่ กั ถกู ละเลยไมส่ นใจใชก้ นั นัก คงเป็ นเพราะคําสง่ั นถี้ กูออกแบบมาใหอ้ ยกู่ นั เมนู Data จงึ ทําใหน้ กึ ไปวา่ เป็ นคําสง่ั ทไี่ มเ่ กยี่ วขอ้ งกบั การคํานวณ อกีทงั้ ตวั อยา่ งและคําอธบิ ายใน Help กม็ ไิ ดใ้ หร้ ายละเอยี ดชดั เจนนัก ผทู ้ จี่ ะเขา้ ใจ Data Table ได ้ก็ตอ้ งลองใชค้ ําสงั่ นดี้ แู ลว้ จงึ จะซงึ้ ถงึ ประโยชนข์ องมนัData Table อาศยั การออกแบบตารางแบบพเิ ศษทจี่ ะชว่ ยให ้ Excel นําคา่ ทวี่ างไวบ้ นหวั ตารางหรอื ขา้ งตารางดา้ นใดดา้ นหนงึ่ หรอื ทัง้ สองดา้ น สง่ ตอ่ ไปแทนทคี่ า่ หรอื สตู รทบี่ นั ทกึ ไวใ้ นเซลล์เมอื่ คา่ ในเซลลเ์ หลา่ นัน้ เปลยี่ นไปตามคําสง่ั Data Table ก็จะทําใหเ้ ซลลส์ ตู รทเี่ กยี่ วขอ้ งคํานวณหาคําตอบใหม่ เพยี งแตก่ ารเปลย่ี นแปลงคา่ ในเซลลแ์ ละคําตอบใหมท่ เ่ี กดิ ขน้ึ นัน้ เราจะไมเ่ ห็นมกี ารเปลยี่ นแปลงใดๆเกดิ ขน้ึ ในตาราง แตจ่ ะพบวา่ Excel นําคําตอบมาสรปุ ไวใ้ นตารางData Table ใหเ้ องสําหรับผทู ้ ค่ี นุ ้ เคยกบั การเขยี นโปรแกรมสําเร็จรปู การทํางานของ Data Table เปรยี บเทยี บได ้กบั การใชร้ หสั โปรแกรมสง่ั ให ้ Excel ทํางานซ้าํ หลายครัง้ เป็ น loop โดย Excel จะสง่ คา่ ไปท่ีเซลลท์ เ่ี รากําหนดใหใ้ นแตล่ ะ loop ทงั้ นี้ Data Table จะทํางานเป็ น loop ใหอ้ ตั โนมตั โิ ดยเราไมต่ อ้ งเสยี เวลาเขยี นโปรแกรมแมแ้ ตน่ อ้ ยData Table มี 3 แบบตามจํานวนตวั แปรทเี่ กยี่ วขอ้ ง ไดแ้ ก่  Data Table แบบ 1 ตวั แปร  Data Table แบบ 2 ตวั แปร  Data Table แบบไมจ่ ํากดั ตวั แปร โดยใชร้ ว่ มกบั สตู ร Index (กลายเป็ นวธิ ที ฉี่ ลาดกวา่ การใช ้ Scenarios)โครงสรา้ งตาราง Data Table แบง่ เป็ น 4 สว่ น ตามรปู สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 66 of 147  Data Table แบบ 1 ตวั แปรจากตวั อยา่ งการหาคา่ โฆษณา หากกาํ หนดใหค้ า่ โฆษณาเป็ นตวั เลขในชว่ ง 100 ถงึ 400 บาทใหห้ าวา่ จะสง่ ผลตอ่ กําไรขนั้ ตน้ อยา่ งไรทางออกในการแกป้ ัญหาน้ี หากคณุ ไมร่ จู ้ กั Data Table มากอ่ น ก็คงตอ้ งสรา้ งตารางคํานวณซํา้กนั หลายๆตารางเพอ่ื ทดลองใสต่ วั เลขคา่ โฆษณาลงไปเพอื่ หาผลลพั ธท์ ต่ี อ้ งการแทนทจี่ ะเสยี แรงเสยี เวลาสรา้ งตารางคํานวณซาํ้ ใหอ้ อกแบบโครงสรา้ งตาราง Data Tableขน้ึ มากอ่ น โดยในปัญหานสี้ งั เกตวา่ ตอ้ งการเปลย่ี นคา่ โฆษณาเพยี งคา่ เดยี ว ดงั นัน้ จงึ เขา้ ขา่ ยData Table แบบ 1 ตวั แปร ซง่ึ มโี ครงสรา้ งแยกแยะออกไปอกี 2 แบบยอ่ ยตามแตว่ า่ คณุตอ้ งการดตู วั แปรตามแนวใด ไดแ้ ก่  Data Table แบบวางตวั แปรไวใ้ นหวั ตารางดา้ นขา้ งตามแนวตงั้ สว่ นหวั ตารางดา้ นบน เป็ นสตู รคําตอบที่ link มา และเมอื่ สง่ั Data Table ใหเ้ ลอื กใชช้ อ่ ง Column Input Cell  Data Table แบบวางตวั แปรไวใ้ นหวั ตารางดา้ นบนตามแนวนอน สว่ นหวั ตารางดา้ นขา้ ง เป็ นสตู รคําตอบที่ link มา และเมอ่ื สง่ั Data Table ใหเ้ ลอื กใชช้ อ่ ง Row Input Cellตวั อยา่ งการใช ้ Data Table แบบวางตวั แปรไวใ้ นหวั ตารางดา้ นขา้ งตามแนวตงั้ 1. นําตวั เลขคา่ โฆษณามาพมิ พล์ งในเซลล์ E23:E27 โดยจะกําหนดใหเ้ ป็ นตวั เลขใด กอ่ นหลงั ก็ได ้ ไมจ่ ําเป็ นตอ้ งเรยี งลําดบั มากนอ้ ย 2. เซลล์ F22 และ G22 สรา้ งสตู ร link ผลลพั ธข์ องสตู รคํานวณมาจากเซลล์ G19 และ H18 ตามลําดบั 3. ใหเ้ ลอื กพน้ื ที่ E22:G27 แลว้ สงั่ Data > What-If Analysis > Data Table (Excel 2003 สงั่ Data > Table) 4. คลกิ ลงในชอ่ ง Column input cell แลว้ คลกิ ตอ่ ไปทเ่ี ซลล์ G18 (เพอื่ กําหนดให ้ Data Table ยงิ ตวั เลขคา่ โฆษณาทวี่ างไวต้ ามแนวตงั้ ไปทเี่ ซลล์ G18) แลว้ กดป่ มุ OK 5. จะพบคําตอบปรากฏในตาราง F23:G27 และเมอ่ื คลกิ ดจู ะพบสตู ร {=TABLE(,G18)} ซง่ึ สตู รนเ้ี ราไมส่ ามารถสรา้ งเอง ตอ้ งใชค้ ําสงั่ Data Table เทา่ นัน้ จงึ จะเกดิ สตู รนขี้ น้ึ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 67 of 147   และเมอื่ ดใู นสตู รจะพบ (,G18) แสดงถงึ การไมใ่ ช ้ Row Input แตใ่ ช ้ Column Input มี เซลล์ G18 เป็ นเซลลร์ ับคา่หากใชต้ าราง Data Table แบบวางตวั แปรไวใ้ นหวั ตารางดา้ นบนตามแนวนอน ตอ้ งออกแบบตารางตามรปู ตอ่ ไปนี้ขอ้ สงั เกต 1. Data Table แบบ 1 ตวั แปร สามารถหาคําตอบมาแสดงในตารางไดห้ ลายเรอื่ ง ตามแต่ วา่ จะ link สตู รคําตอบเรอ่ื งใดมาแสดงในหวั ตารางอกี ขา้ งหนงึ่ จงึ ใหผ้ ลลพั ธไ์ ดม้ ากกวา่ Data Table แบบ 2 ตวั แปร ซง่ึ แมจ้ ะใชต้ วั แปรได ้ 2 ตวั แตจ่ ะแสดงคําตอบในตารางได ้ เพยี งเรอ่ื งเดยี วเพราะมเี ซลลห์ วั มมุ เพยี งเซลลเ์ ดยี ว 2. ถา้ ตอ้ งการใชต้ วั แปรมากกวา่ 2 ตวั หรอื ตอ้ งการคําตอบหลายเรอื่ ง สามารถใชส้ ตู ร If, Choose หรอื Index ชว่ ยทําให ้ Data Table ทํางานไดไ้ มจ่ ํากดั 3. สตู รที่ Excel สรา้ งใหเ้ มอื่ สรา้ ง Data table เป็ นสตู รทถ่ี กู สรา้ งขน้ึ แบบ Array พรอ้ มกนั ทกุ เซลล์ โดยมโี ครงสรา้ งสตู รดงั น้ี =Table(Row Input Cell, Column Input Cell) Data Table แบบ 1 ตวั แปร จะเกดิ สตู ร =Table(Row Input Cell,) เมอื่ เป็ นตารางทใี่ ช ้ Row Input และจะเกดิ สตู ร =Table(, Column Input Cell) เมอ่ื เป็ นตารางทใี่ ช ้ Column Input สว่ น Data Table แบบ 2 ตวั แปรจะเกดิ สตู รทใ่ี ชโ้ ครงสรา้ งแบบเต็ม =Table(Row Input Cell, Column Input Cell) 4. การอา่ นผลลพั ธจ์ ากตาราง Data Table ตอ้ งอธบิ ายใหช้ ดั เจนวา่ ในขณะทค่ี า่ โฆษณา เปลย่ี นแปลงไปนัน้ ตวั เลขตวั แปรอนื่ ไดแ้ ก่ ยอดขาย และ %ตน้ ทนุ ขาย ถอื วา่ คงท่ี 5. คา่ โฆษณาในเซลล์ G18 จะเวน้ วา่ งไวห้ รอื จะใสต่ วั เลขใดๆแมจ้ ะสรา้ งสตู รคํานวณไวก้ อ่ น หรอื หลงั การสง่ั Data Table กไ็ ด ้ เพราะไมม่ ผี ลตอ่ การคํานวณใน Data Table 6. ตาราง Data Table ไมส่ ามารถถกู Copy ออกไปใชท้ อ่ี นื่ เพราะเมอื่ Paste จะพบวา่ สตู ร Table ในตารางถกู เปลย่ี นเป็ นตวั เลขคา่ คําตอบแทนทกุ เซลล์ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 68 of 147   7. เราไมส่ ามารถ Insert แทรกเซลลส์ ตู ร Table และหากตอ้ งการลบสตู ร Table ทง้ิ ตอ้ ง เลอื กพนื้ ทตี่ ารางทงั้ หมดทม่ี สี ตู ร Table แลว้ จงึ จะสามารถลบสตู รทงิ้ พรอ้ มกนั ทกุ เซลล์Data Table แบบ 2 ตวั แปรในตวั อยา่ งเดมิ หากตอ้ งการวเิ คราะหว์ า่ ถา้ เราเปลยี่ นแปลงตน้ ทนุ ขายและคา่ โฆษณาจะมผี ลอยา่ งไรบา้ งตอ่ ยอดกําไรขนั้ ตน้ 1. พมิ พต์ วั แปรตน้ ทนุ ขายไวใ้ นเซลล์ F22:H22 ซง่ึ ถอื เป็ น Row Input 2. พมิ พต์ วั แปรตน้ คา่ โฆษณาไวใ้ นเซลล์ E23:E27 ซง่ึ ถอื เป็ น Column Input 3. ในเซลลห์ วั มมุ ตาราง E22 สรา้ งสตู ร =G19 เพอื่ link สตู รคํานวณหากําไรขนั้ ตน้ มาใช ้ 4. จากนัน้ ใหเ้ ลอื กพนื้ ทต่ี งั้ แต่ E22:H27 แลว้ สง่ั Data > What-If Analysis > Data Table (Excel 2003 สง่ั Data > Table) 5. คลกิ ลงไปในชอ่ ง Row Input Cell แลว้ คลกิ ตอ่ ไปยงั เซลล์ G17 ซง่ึ เป็ นเซลลต์ น้ ทนุ ขาย 6. คลกิ ลงไปในชอ่ ง Column Input Cell แลว้ คลกิ ตอ่ ไปยงั เซลล์ G18 ซงึ่ เป็ นเซลลค์ า่ โฆษณา แลว้ กดป่ มุ OK จะพบคําตอบของกําไรขนั้ ตน้ แสดงในตาราง F23:H27เมอ่ื นํา Data Table มาเปรยี บเทยี บกบั Goal Seek มขี อ้ สงั เกตดงั น้ี1. Data Table สามารถสง่ คา่ ตวั แปรใหมค่ รัง้ ละหลายตวั ไปแทนทเ่ี ซลลท์ ม่ี สี ตู รสรา้ งไว ้ ซงึ่ Excel จะคํานวณใหต้ ามคา่ ตวั แปรใหมน่ ัน้ โดยถอื วา่ ไมไ่ ดใ้ ชส้ ตู รทสี่ รา้ งไวก้ อ่ นแลว้ ในเซลล์ นัน้ มาใชค้ ํานวณรว่ มดว้ ยแตอ่ ยา่ งใด สว่ น Goal Seek สามารถสง่ ตวั แปรใหมไ่ ปไดค้ รัง้ ละ 1 ตวั และไมส่ ามารถสง่ ตวั แปรไปแทนเซลลท์ เ่ี ป็ นสตู ร สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 69 of 147  2. Data Table เหมาะกบั การใชว้ างแผนระยะยาว โดยกําหนดคา่ ตวั แปรบนหวั ตารางให ้ ครอบคลมุ ชว่ งคา่ ทเี่ ป็ นไปไดเ้ พอ่ื ดผู ลลพั ธท์ เ่ี กดิ ขนึ้ ในชว่ งคา่ นัน้ ๆซงึ่ อาจไมไ่ ดค้ ําตอบตรง กบั คา่ เป้าหมายทต่ี อ้ งการ สว่ น Goal Seek เหมาะสําหรับการวเิ คราะหห์ าตวั เลขเฉพาะกจิ ซงึ่ จะคดิ ยอ้ นกลบั จนไดต้ วั เลขตามตอ้ งการ3. เราสามารถสง่ั Formulas > Calculation Options > Automatic Except for Data Tables (Excel 2003 สงั่ Tools > Options > Calculation > Automatic except tables) เพอ่ื กําหนดใหใ้ ชก้ ารกดป่ มุ F9 เพอ่ื สง่ั ให ้ Data Table คํานวณเฉพาะเมอื่ ตอ้ งการเทา่ นัน้ สว่ น Goal Seek แมว้ า่ จะพบวา่ ใชก้ บั การคํานวณแบบ Automatic หรอื Manual กไ็ ด ้ แตเ่ ราควร ใช ้ Goal Seek รว่ มกบั การคํานวณแบบ Automatic ไวก้ อ่ น เพอ่ื ชว่ ยทําใหม้ น่ั ใจวา่ เซลล์ สตู รทเ่ี กยี่ วขอ้ งถกู คํานวณตามกนั ไปครบทงั้ หมดดว้ ย4. เนอื่ งจากเราเป็ นผกู ้ ําหนดคา่ ตวั แปรใหใ้ ชก้ บั Data Table ดงั นัน้ Excel จะถกู สงั่ ใหค้ ํานวณ ตามคา่ ตวั แปรเหลา่ นัน้ จนครบทกุ ตวั สว่ น Goal Seek จะคํานวณยอ้ นกลบั ใหเ้ องแตอ่ าจได ้ คําตอบเฉพาะในชว่ งคา่ ท่ี Excel คน้ หาใหเ้ ทา่ นัน้5. หาก Data Table ไมส่ ามารถหาคําตอบทตี่ อ้ งการ ก็สามารถใช ้ Goal Seek ทํางานตอ่ บน ตาราง Data Table โดยใหใ้ ช ้ Goal Seek คดิ ยอ้ นหาคา่ ตวั แปรบนหวั ตาราง Data Table จนกวา่ จะคํานวณไดค้ ําตอบมาปรากฏขนึ้ ในพน้ื ทส่ี ว่ นของคําตอบในตาราง Data tableData Table แบบไมจ่ าํ กดั ตวั แปรวธิ ดี ดั แปลง Data Table ใหท้ ํางานไดไ้ มจ่ ํากดั ตวั แปร เป็ นเคล็ดลบั ทผี่ มคน้ พบตงั้ แตส่ มยัทํางานวเิ คราะหโ์ ครงการลงทนุ ใหก้ บั บรษิ ัทซพี ี ตอนนัน้ ยงั ไมย่ อมเปิดเผยเคล็ดลบั นก้ี บั ใครเพราะถอื วา่ เป็ นวธิ ที ท่ี ําใหส้ ามารถใช ้ Excel วเิ คราะหว์ างแผนไดไ้ มจ่ ํากดั และยงั ไมเ่ คยพบวา่ มีฝร่ังชาตอิ น่ื คนใดทราบวธิ ที ผ่ี มใช ้ แตพ่ อผมเปลย่ี นอาชพี มาเป็ นวทิ ยากรก็ตงั้ ใจวา่ จะเปิดเผยให ้เรยี นรอู ้ ยา่ งไมต่ อ้ งปิดบงั กนั แตอ่ ยา่ งใดสมยั ทที่ ํางานอยบู่ รษิ ัทซพี ี ผมคยุ ใหห้ วั หนา้ ทเ่ี ป็ นคนจนี เชอื้ สายฝร่ังฟังวา่ ผมสามารถใช ้โปรแกรม Lotus 1-2-3 วเิ คราะหโ์ ครงการไดอ้ ยา่ งไมจ่ ํากดั ตวั แปร ชว่ งแรกเขายงั สงสยั วา่ ทผี่ มวา่ ไมจ่ ํากดั ตวั แปรนัน้ เป็ นอยา่ งไร หวั หนา้ คนนข้ี อใหผ้ มสาธติ วธิ กี ารใหเ้ ขาดู พอเขาเห็นวธิ ที ผ่ี มใชว้ า่ สามารถชว่ ยใหว้ เิ คราะหโ์ ครงการไดย้ ดื หยนุ่ เพยี งใด เขากส็ งั่ งานผมเพมิ่ ขนึ้ ถงึ 6 เทา่ ตวัทเี ดยี ว (แมง้ านจะหนักขน้ึ แตผ่ มมคี วามภมู ใิ จเป็ นอยา่ งมากเพราะหวั หนา้ ใหเ้ กยี รตลิ งมาน่ังท่ีโตะ๊ กบั ผมเพอื่ ใหผ้ มสาธติ ใหด้ ู ซงึ่ หวั หนา้ คนนมี้ ตี ําแหน่งใหญเ่ ป็ นอนั ดบั สองของซพี ที เี ดยี วและในชวั่ ชวี ติ ของผมกม็ หี วั หนา้ คนนเี้ พยี งคนเดยี วทย่ี อมมาน่ังขา้ งๆเพอ่ื ดผู มทําคอมพวิ เตอร)์หลกั การทชี่ ว่ ยทําให ้ Data Table คํานวณไดไ้ มจ่ ํากดั ตวั แปร เกดิ จากการนําสตู ร Index ดงึ ตวัแปรแตล่ ะชดุ ไปแทนทตี่ วั แปรทใ่ี ชต้ ามปกติ (โดยตวั แปรแตล่ ะชดุ มจี ํานวนตวั แปรนับรอ้ ยนับพันตวั ไดไ้ มจ่ ํากดั ) แทนทจี่ ะใช ้ Data table สง่ คา่ ตวั แปรทใ่ี ชค้ ํานวณไปยงั Row Input Cell หรอืColumn Input Cell โดยตรง ใหเ้ ปลยี่ นไปใชเ้ ลขท่ี Row หรอื เลขท่ี Column ของตารางฐานขอ้ มลู เป็ นตวั แปรแทน สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 70 of 147  พอ Data Table เรม่ิ สง่ ตวั เลข Row Input Cell หรอื Column Input Cell กจ็ ะทําใหส้ ตู ร Indexดงึ ตวั แปรทเี่ กย่ี วขอ้ งสง่ ตอ่ ไปยงั ตารางคํานวณและเกดิ ผลลพั ธส์ ง่ กลบั มาแสดงในตาราง DataTable (ซง่ึ การทํางานแบบนย้ี งั ทําใหเ้ ครอ่ื งมอื ทเี่ รยี กวา่ Scenarios กลายเป็ นเครอื่ งมอื ท่ีลา้ สมยั เราไมค่ วรเสยี เวลาหรอื ใสใ่ จทจี่ ะนํา Scenarios มาใชอ้ กี ตอ่ ไป)1. วงจรการคํานวณในภาพขา้ งตน้ นเ้ี รม่ิ จากสรา้ งตารางฐานขอ้ มลู ชอ่ื MyData จากเซลล์ N13:P16 เพอ่ื บนั ทกึ ตวั แปรยอดขายและตน้ ทนุ ของแตล่ ะ Case ทเี่ ป็ นไปได ้ เชน่ Case1 กําหนดใหย้ อดขายเทา่ กบั 100 และมตี น้ ทนุ แตล่ ะตวั เทา่ กบั 10, 20, 30 และบนั ทกึ Case2, Case3 ใน column ถดั ไป2. เซลล์ J12 ถอื เป็ นหวั ใจของการคํานวณ โดยกําหนดใหพ้ มิ พเ์ ลข 1, 2, หรอื 3 ลงไปเพอ่ื แสดงถงึ เลขท่ี Case ทต่ี อ้ งการนํามาคํานวณ เชน่ ตามภาพนเ้ี ซลล์ J12 มคี า่ เป็ นเลข 1 ซงึ่ แสดงวา่ กําลงั ดงึ ตวั แปรของ Case1 มาใชค้ ํานวณ (หรอื หมายถงึ Column ท่ี 1 จากตาราง ชอ่ื MyData)3. เซลล์ J13:J16 ใชส้ รา้ งสตู ร =INDEX(MyData,0,J12) แบบ Array ลงไปพรอ้ มกนั เพอ่ื ดงึ ขอ้ มลู จากตารางชอ่ื MyData ตามเลข Column ทเี่ ลอื กจากเซลล์ J124. เซลล์ H13:H16 ใชส้ รา้ งสตู รรับคา่ แตล่ ะเซลลต์ อ่ มาจากขอ้ 3 เพอื่ นํามาคํานวณเป็ นงบ การเงนิ เพอื่ หายอด Margin, Tax, และ Profit5. ตาราง Data Table คอื พน้ื ทตี่ าราง M20:P27 เป็ น Data Table แบบ 1 ตวั แปร 5.1. กําหนดใหว้ างตวั แปรเป็ นเลขท่ี Case ไวบ้ นหวั ตารางดา้ นบนในชว่ ง N20:P20 5.2. หวั ตารางดา้ นขา้ ง M21:M27 ใชส้ รา้ งสตู ร link ผลลพั ธจ์ ากงบการเงนิ ทัง้ งบจากชว่ ง เซลล์ H13:H19 มาแสดงเซลลต์ อ่ เซลลต์ ามลําดับรายการของงบการเงนิ 5.3. ในการสงั่ Data Table ใหเ้ ลอื กพน้ื ทตี่ าราง M20:P27 แลว้ ใช ้ Row Input Cell เป็ น เซลล์ J12 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 71 of 147  6. วงจรของการคํานวณของ Data Table เรม่ิ จาก Data Table ยงิ ตวั เลขที่ Case ไปทเ่ี ซลล์ J12 จากนัน้ สตู ร Index ในชว่ ง J13:J16 จะดงึ คา่ จากตาราง MyData สง่ คา่ ตอ่ ไปยงั งบ การเงนิ ในชว่ ง H13:H16 แลว้ Data Table จะดงึ ผลลพั ธก์ ลบั มาแสดงแตล่ ะ Case ตามแนว ของเลขตวั แปรซงึ่ เป็ นเลขทแ่ี ตล่ ะ Case ทอ่ี ยใู่ นหวั ตารางดา้ นบน ถอื เป็ นการทํางานครบ วงจรของ Data Table ทม่ี กี ารสง่ คา่ ไปแลว้ รับผลลพั ธก์ ลบั มา (หากเขา้ ใจประเด็นนี้ จะ สามารถใช ้ Data Table สง่ คา่ ตวั แปรขา้ มชที หรอื ขา้ มแฟ้มไดด้ ว้ ย)วธิ ใี ช้ Solverตอนทเี่ ห็นชอ่ื คําวา่ Solver เป็ นครัง้ แรก ไมเ่ ขา้ ใจวา่ มนั คอื อะไร แตพ่ อลองใช ้ Solver ไปสกั พักจงึ เขา้ ใจวา่ ทแี่ ท ้ Solver กค็ อื เครอ่ื งมอื ทชี่ ว่ ยในการคํานวณประเภท Linear Programmingน่ันเอง แตพ่ อเอย่ ถงึ คําวา่ Linear Programming ก็อาจทําใหห้ ลายคนทไี่ มเ่ คยเรยี นเรอ่ื งนใ้ี นสมยั มหาวทิ ยาลยั งงมากขนึ้ ก็ได ้ จงึ ขออธบิ ายแบบภาษาชาวบา้ นกนั ดกี วา่ขอใหน้ กึ ถงึ Solver ตอ่ เมอื่ ปัญหาหนง่ึ สามารถมคี ําตอบไดห้ ลายคําตอบ เชน่ ถา้ คณุ มเี งนิ อยู่หนง่ึ แสนบาท จะขอแลกธนบตั รใบละ 1,000 บาท 500 บาท 100 บาท 50 บาท หรอื 20 บาทไดอ้ ยา่ งละกฉ่ี บบั หรอื ถา้ ตอ้ งการสว่ นผสมทเี่ ป็ นนํ้ารวม 100 ซซี ี จะนําของเหลว 3 อยา่ งมาผสมกนั ในอตั ราสว่ นอยา่ งละเทา่ ใดเพอื่ ใหร้ วมกนั แลว้ เป็ นนํ้า 100 ซซี ี ซงึ่ จะเห็นวา่ ในสองปัญหาน้ี เราสามารถแลกธนบตั รไดห้ ลายแบบ และมอี ตั ราสว่ นของเหลวไดห้ ลายแบบการคดิ แกป้ ัญหาการคํานวณใน Excel มหี ลกั อยวู่ า่ ถา้ ปัญหาหนงึ่ ๆมคี ําตอบเพยี งคําตอบเดยี วขอรับรองวา่ Excel มสี ตู รทสี่ ามารถนํามาใชค้ ํานวณหาคําตอบทต่ี อ้ งการนัน้ ไดแ้ น่ๆ ซงึ่ ถงึ จะไม่สามารถใชส้ ตู รสําเร็จรปู ท่ี Excel เตรยี มไวใ้ หใ้ ชไ้ ดโ้ ดยตรง เรากส็ ามารถนําสตู รมาซอ้ นตอ่ กนัหรอื ใชห้ ลายสตู รมาคํานวณรว่ มกนั เพอื่ แกป้ ัญหานัน้ ขออยา่ ใจรอ้ นรบี ตอบวา่ ไมม่ สี ตู รใน Excelทที่ ําได ้ ขอใหใ้ จเย็นๆคอ่ ยๆคดิ เพราะบางปัญหากวา่ จะคดิ สตู รออกมาไดต้ อ้ งคดิ กนั เป็ นสบิ ปีก็เป็ นได ้แตถ่ า้ ปัญหาหนงึ่ ๆอาจจะมหี ลายคําตอบ กจ็ ําเป็ นตอ้ งใชส้ ตู รคํานวณของ Excel นํามาใชร้ ว่ มกบัคําสง่ั Solver โดยตอ้ งหาทางสรา้ งสตู รคํานวณทเ่ี กยี่ วขอ้ งไวต้ ารางใหเ้ รยี บรอ้ ยกอ่ น จากนัน้ จงึใช ้ Solver นําผลลพั ธจ์ ากสตู รทคี่ ํานวณไดไ้ ปใชต้ อ่ ทงั้ นใี้ นตัวคําสงั่ Solver เองจะไมส่ ามารถรับสตู รคํานวณ ดงั นัน้ จงึ เป็ นหนา้ ทขี่ องเราทต่ี อ้ งสรา้ งสตู รไวใ้ นเซลลต์ า่ งๆเพอ่ื Solver จะทําหนา้ ทค่ี น้ หาตวั เลขคําตอบทเ่ี ป็ นไปได ้ แลว้ นําผลลพั ธท์ คี่ ํานวณไดไ้ ปเปรยี บเทยี บระหวา่ งเซลล์สตู รคํานวณทเี่ กยี่ วขอ้ งวา่ ไดค้ ําตอบตามขอบเขตเงอื่ นไขทก่ี ําหนดทงั้ หมดแลว้ หรอื ไม่Solver กบั Goal Seek มลี กั ษณะการทํางานคลา้ ยกนั และตา่ งกนั ดงั นี้  เครอ่ื งมอื ทงั้ คทู่ ําหนา้ ทท่ี ดลองคา่ แทนลงไปในเซลลร์ ับตวั แปร (Changing Cells) ซงึ่ หา้ มเป็ นเซลลส์ ตู ร โดย Solver สามารถใชต้ วั แปรไดม้ ากถงึ 200 ตวั (หรอื มากกวา่ นัน้ หากใช ้ Solver แบบพเิ ศษของ www.Solver.com) สว่ น Goal Seek ใชต้ วั แปรไดเ้ พยี ง ตวั เดยี ว สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 72 of 147    เซลลผ์ ลลพั ธเ์ ป้าหมาย (Target Cell) ของ Solver สามารถเลอื กใหเ้ ป็ นคา่ Maximize, Minimize, หรอื Optimize ใหไ้ ดค้ า่ ใดคา่ หนงึ่ ในขณะท่ี Goal Seek หาคา่ แบบ Optimize เทา่ นัน้  Solver รับเงอื่ นไข (Constraints) ไดโ้ ดยตรง เพอื่ ใชค้ วบคมุ ใหค้ ําตอบทไ่ี ดน้ ัน้ ตอ้ ง บรรลเุ งอ่ื นไขทก่ี ําหนดดว้ ย สว่ น Goal Seek ใช ้ Constraint ของ Calculation Options  การสงั่ Solver ตอ้ งเรยี กใชผ้ า่ น Add-ins ซง่ึ Microsoft จดั เตรยี มไวใ้ หใ้ ชโ้ ดยไมต่ อ้ ง หาซอื้ เพม่ิ แตอ่ ยา่ งใด สว่ น Goal Seek เป็ นคําสง่ั มาตรฐานทเี่ รยี กใชไ้ ดท้ ันทีเนอื่ งจาก Solver มขี นั้ ตอนการใชง้ านทคี่ อ่ นขา้ งซบั ซอ้ น ดงั นัน้ จงึ ขอยกตวั อยา่ งทไ่ี มย่ ากนักเพอื่ สรา้ งความคนุ ้ เคยกบั พน้ื ฐานการใชง้ าน หากตอ้ งการศกึ ษาการใชง้ านโดยละเอยี ดขอใหด้ ูไดจ้ าก www.Solver.comในตวั อยา่ งนกี้ ําหนดใหม้ ตี วั เลข 3 ตวั อยใู่ นเซลล์ B2:B4 มคี า่ เทา่ กบั 15, 25, 35 ตามลําดบั ให ้ตอบคําถามวา่ มเี ซลลใ์ ดบา้ งทสี่ ามารถนํามารวมกนั แลว้ ไดค้ ําตอบเป็ นเลข 40ถา้ คดิ ในใจก็ตอบวา่ ตอ้ งใชต้ วั เลขในเซลล์ B2+B3 = 15+25 ทําใหไ้ ดผ้ ลลพั ธเ์ ทา่ กบั 40 แต่เราตอ้ งการใช ้ Solver ในการบอกตําแหน่ง B2 และ B3 จงึ ตอ้ งหาทางสรา้ งสตู รทเี่ กยี่ วขอ้ งลงไปในตารางใหเ้ สร็จกอ่ นดงั น้ี  เซลล์ C2:C4 เวน้ ไวเ้ พอ่ื ให ้ Solver หาตวั เลขเฉพาะเลข 1 หรอื 0 ใสล่ งไป เพอ่ื ใชเ้ ป็ น การชต้ี ําแหน่งทจ่ี ะนํามาใช ้ เชน่ ถา้ C2:C4 ไดต้ วั เลข 1, 1, 0 ตามลําดบั ก็แสดงวา่ ใช ้ เซลล์ C2 และ C3 สว่ นเซลล์ C4 ไมต่ อ้ งนํามาใช ้  เซลล์ D2:D4 ตอ้ งสรา้ งสตู รนําตวั เลขใน B2:B4 มาคณู กบั ตวั เลข 1 หรอื 0 ท่ี Solver จะหาใหจ้ ากเซลล์ C2:C4 เป็ นผลลพั ธท์ คี่ ณู กนั ทลี ะเซลล์  เซลล์ D5 สรา้ งสตู ร =SUM(D2:D4) เพอื่ หายอดรวมทงั้ หมดของผลคณู ซง่ึ เราตอ้ งการ คําตอบในเซลลน์ เ้ี ทา่ กบั 40หลงั จากทอ่ี อกแบบตารางและสรา้ งสตู รคํานวณขา้ งตน้ เรยี บรอ้ ยแลว้ ใหเ้ รมิ่ ใช ้ Solver ตามขนั้ ตอนตอ่ ไปนี้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 73 of 147   1. สง่ั File > Options > Add-ins เลอื ก Manage : Excel Add-ins แลว้ กดป่ มุ Go (Excel 2003 สงั่ Tools > Options > Add-ins) 2. กาชอ่ ง Solver Add-in แลว้ กดป่ มุ OK 3. สงั่ Data > Solver (Excel 2003 สงั่ Tools > Solver) สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 74 of 147   4. ในชอ่ ง Set Objective: ใหค้ ลกิ เลอื กเซลล์ D5 ซงึ่ เป็ นสตู รหายอดรวมของผลคณู แลว้ คลกิ เลอื กชอ่ ง Value of: พมิ พเ์ ลขคําตอบ 40 ลงไป 5. ในชอ่ ง By Changing Variable Cells: ใหค้ ลกิ เลอื กเซลล์ C2:C4 ซงึ่ เป็ นเซลลร์ ับตวั แปรทต่ี อ้ งการหาคา่ ใหม่ 6. เพม่ิ Constraints โดยกดป่ มุ Add แลว้ กําหนดเซลล์ C2:C4 = binary 7. กดป่ มุ Solve จะเห็นตวั เลข 1 หรอื 0 แสดงในเซลล์ C2:C4 แลว้ ทําใหไ้ ดต้ วั เลขยอด รวมของผลคณู ในเซลล์ D5 = 40 ตามตอ้ งการแลว้ กดป่ มุ OK เพอ่ื ยอมรับขอ้ ควรระวงั ในการใช้ Solver 1. หากตอ้ งการสงั่ ให ้ Solver พมิ พร์ ายงาน ใหค้ ลกิ เลอื กชอื่ รายงานในชอ่ ง Reports (เป็ น ชอ่ งดา้ นขวาของ Solver Results ตามภาพขา้ งบนน)ี้ แตม่ ขี อ้ แมว้ า่ ตอ้ งไมส่ งั่ Protect Workbook ไวก้ อ่ น 2. เงอื่ นไขในสว่ นของ Constraints เป็ นสง่ิ สําคญั ทที่ ําให ้ Solver สามารถหาคา่ ทต่ี อ้ งการ หากกําหนด Constraints ไวไ้ มค่ รบถว้ นก็จะสง่ ผลใหไ้ มส่ ามารถหาคําตอบทต่ี อ้ งการ หรอื อาจเกดิ คําตอบทไ่ี มน่ ่าจะเป็ นไปได ้ เชน่ ในปัญหาการผลติ ถา้ ลมื กําหนด Constraints ใหเ้ ป็ นจํานวนเต็ม ก็จะไดค้ ําตอบจํานวนผลติ ทม่ี เี ศษทศนยิ ม หรอื ถา้ ไมไ่ ด ้ กําหนดใหเ้ ป็ นคา่ ทม่ี ากกวา่ หรอื เทา่ กบั 0 กจ็ ะไดค้ ําตอบทเ่ี ป็ นเลขตดิ ลบ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 75 of 147  เครอื่ งมอื อนื่ ในการวเิ คราะหท์ างออกทเี่ ป็ นไปได้Goal Seek, Data Table, และ Solver จัดเป็ นเครอื่ งมอื ทใี่ ชว้ เิ คราะหห์ าทางออกในอนาคตเนอ่ื งจากผลลพั ธท์ ไี่ ด ้ ตอ้ งผา่ นสตู รทเี่ ราสรา้ งเพอ่ื คํานวณตามคา่ ตวั แปรทส่ี มมตขิ น้ึ ทงั้ นี้ Excelยงั มเี ครอ่ื งมอื ทางสถติ อิ กี หลายอยา่ งทเ่ี ราสามารถนํามาชว่ ยวดั ระดบั ความเชอ่ื มนั่ เพยี งแค่เรยี กใช ้ Analysis ToolPak Add-in ทตี่ ดิ ตงั้ มาพรอ้ มกบั โปรแกรม Excel กจ็ ะไดค้ ําสงั่ ดา้ นสถติ ิอกี มากมายหลายอยา่ งมาใชง้ านในดา้ น Data Analysis เชน่ Anova, Correlation,Covariance, F-Test, Regression, t-test, และ z-test เป็ นตน้ โดยบรษิ ัท Microsoft ได ้พัฒนาเครอื่ งมอื ทางสถติ เิ หลา่ นใี้ หม้ ขี นั้ ตอนการใชง้ านงา่ ยๆ เครอื่ งมอื หลายอยา่ งสามารถทํางานอยา่ งอตั โนมตั ิ ชว่ ยทําใหค้ นทคี่ ดิ วา่ วชิ าสถติ เิ ป็ นฝันรา้ ยสามารถนอนหลบั ฝันดี เพราะสถติ ใิ น Excel ไมไ่ ดย้ ากอยา่ งทเ่ี คยคดิสว่ น Pivot Table จัดเป็ นเครอ่ื งมอื ทใี่ ชว้ เิ คราะหข์ อ้ มลู ในอดตี มาดผู ลในปัจจบุ นั เพราะขอ้ มลูสว่ นใหญท่ เ่ี หมาะสําหรับนํามาใชก้ บั Pivot Table เป็ นผลมาจากการดําเนนิ การทางธรุ กจิ โดยจะนํารายละเอยี ดการใช ้ Pivot Table มาอธบิ ายในภายหลงั สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 76 of 147   เรอื่ งทไี่ มค่ อ่ ยรกู้ นั เกยี่ วกบั สตู รโปรแกรม Microsoft Excel รนุ่ ใหม่ มไิ ดม้ เี สน่หอ์ ยทู่ รี่ ปู รา่ งหนา้ ตาบนหนา้ จอทปี่ ระกอบไปดว้ ยแถบรบิ บอนแบบใหม(่ ทยี่ งั ไมค่ อ่ ยคนุ ้ เคย) และมพี น้ื ทต่ี ารางซง่ึ มขี นาดใหญม่ ากขนึ้ กวา่ เดมิหลายเทา่ เพยี งเทา่ นัน้ แตส่ าเหตทุ ที่ ําให ้ Excel มเี สน่ห์ กลายเป็ นโปรแกรมยอดนยิ มมานานแสนนาน ก็เพราะมอี งคป์ ระกอบสําคญั น่ันคอื Excel ตงั้ แตร่ นุ่ แรกๆไดจ้ ัดเตรยี มสตู รสําเร็จรปู ไว ้มากมายใหน้ ํามาใชก้ นั หากสตู รทมี่ อี ยยู่ งั ไมส่ ามารถใชค้ ํานวณหาคําตอบทตี่ อ้ งการได ้ เราก็สามารถนําสตู รทมี่ อี ยนู่ ัน้ มาใชร้ ว่ มกนั ซอ้ นกนั หรอื ผกู สตู รเขา้ ดว้ ยกนั จนกลา่ วไดว้ า่ เราสามารถใช ้ Excel คํานวณหาคําตอบไดก้ บั โจทยก์ ารคํานวณทกุ เรอื่ ง ปัญหาใดทค่ี ดิ วา่ ไมส่ ามารถใช ้Excel หาคําตอบ นั่นเป็ นเพราะคนเราตา่ งหากทยี่ งั คดิ ไมอ่ อก ไมใ่ ชว่ า่ ใช ้ Excel หาคําตอบไมไ่ ด ้ บางปัญหาอาจตอ้ งใชเ้ วลาคดิ นับสบิ ปีกวา่ จะทราบวา่ ตอ้ งเอาสตู รนัน้ มาซอ้ นกบั สตู รนแ้ี ลว้กจ็ ะไดค้ ําตอบทต่ี อ้ งการออกมานั่นเองเคล็ดลบั ทชี่ ว่ ยใหส้ ามารถใชส้ ตู รจนชาํ่ ชอง มใิ ชว่ า่ ตอ้ งรจู ้ ักสตู รทกุ สตู รที่ Excel มอี ยหู่ รอกเพราะสตู รทใ่ี ชง้ านกนั ประจํานัน้ มไี มม่ ากนัก แคร่ จู ้ กั การใชส้ ตู ร Sum, If, And, Or, Choose,VLookup, Match, Index, CountIF, SumIF, Offset, Indirect, และ Array Formula ใหเ้ ป็ นและสามารถนําสตู รแคน่ แี้ หละมาใชร้ ว่ มกนั ใหเ้ ป็ นกเ็ พยี งพอแลว้ แตก่ วา่ จะใชส้ ตู รใหเ้ ป็ นยงั มีหลายเรอื่ งทต่ี อ้ งทําความเขา้ ใจกนั หากคดิ เพยี งวา่ ขอใชส้ ตู รเหลา่ นเ้ี ป็ นกพ็ อแลว้ คณุ ก็จะถงึทางตนั ในไมช่ า้ โดยบทความนจ้ี ะขอนําเรอ่ื งทไี่ มค่ อ่ ยรกู ้ นั เกยี่ วกบั สตู รมาอธบิ าย หลายเรอื่ งเป็ นเรอื่ งสําคญั ทคี่ ดิ กนั วา่ ไมส่ ําคญั บางเรอื่ งก็งา่ ยแตค่ ดิ ไมถ่ งึ วา่ เรอ่ื งงา่ ยน่ันแหละยงั มอี ะไรหลายอยา่ งซอ่ นอยอู่ กีทําอยา่ งไรใหเ้ กง่ สตู ร Excel 1. ไมจ่ ําเป็ นตอ้ งซอื้ หนังสอื เกยี่ วกบั สตู ร เพยี งแคเ่ ปิดโปรแกรม Excel แลว้ กดป่ มุ F1 กจ็ ะ เปิด Excel Help ขน้ึ มาคน้ หาสตู รที่ Excel มอี ยไู่ ดแ้ ลว้ ถา้ คณุ ตอ้ งการซอ้ื หนังสอื เกย่ี วกบั สตู ร ขอใหห้ าซอื้ หนังสอื ทอ่ี ธบิ ายเรอ่ื งสตู รไดล้ ะเอยี ดกวา่ ทม่ี ใี น Help (ซง่ึ หา ไดย้ ากมาก เพราะคนแตง่ หนังสอื มกั เขยี นอธบิ ายตา่ งจาก Help ไมม่ าก) 2. ถา้ ซอ้ื หนังสอื Excel มาแลว้ ขอใหเ้ ปิดอา่ นตงั้ แตห่ นา้ แรกจนถงึ หนา้ สดุ ทา้ ยโดยไมต่ อ้ ง เปิดคอมพวิ เตอร์ เพราะถา้ มวั เปิดอา่ นไปทําตวั อยา่ งไปบนหนา้ จอพรอ้ มกนั กวา่ จะอา่ น หมดเลม่ กจ็ ะใชเ้ วลาเกนิ ปีครง่ึ (ซงึ่ Excel จะมรี นุ่ ใหมใ่ หพ้ วกเราไดใ้ ชง้ านกนั ประมาณ ทกุ ปี ครงึ่ แลว้ คณุ จะตอ้ งซอื้ หนังสอื Excel รนุ่ ใหมม่ าตงั้ ตน้ อา่ นกนั ตงั้ แตห่ นา้ แรกกนั ใหม่ อกี สดุ ทา้ ยกไ็ มส่ ามารถรจู ้ กั Excel ครบทงั้ หมดสกั ท)ี 3. ถา้ ยงั ไมไ่ ดซ้ อื้ หนังสอื ขอใหไ้ ปที่ www.xlfdic.com ซง่ึ จะมแี ฟ้มตวั อยา่ งการใชส้ ตู ร Excel กวา่ 150 สตู รให ้ download มาศกึ ษากนั ไดฟ้ รี สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 77 of 147   4. ขอใหพ้ ยายามรอบรกู ้ อ่ นรลู ้ กึ คณุ ควรรอบรไู ้ วก้ อ่ นวา่ Excel มสี ตู รอะไรบา้ ง จากนัน้ เมอ่ื ถงึ เวลาทํางานจงึ คอ่ ยใชเ้ วลารลู ้ กึ เรยี นรสู ้ ตู รแตล่ ะสตู รใหค้ ลอ่ งในภายหลงั 5. ตงั้ หลกั ไวเ้ ลยวา่ Excel มสี ตู รทคี่ ณุ สามารถนํามาใชก้ บั งานทกุ อยา่ งของคณุ ไดอ้ ยแู่ ลว้ ถา้ ไมม่ สี ตู รสาํ เร็จรปู ทค่ี ํานวณลดั หาคําตอบไดโ้ ดยตรง ใหห้ าทางนําสตู รหลายสตู รมาใช ้ งานรว่ มกนั 6. เรมิ่ แรกทที่ ดลองใชส้ ตู ร ควรใชก้ บั โจทยง์ า่ ยๆทท่ี ราบคําตอบทถ่ี กู ตอ้ งอยแู่ ลว้ จะได ้ เขา้ ใจหลกั การคํานวณที่ Excel ใชว้ า่ มขี นั้ ตอนการคดิ คํานวณอยา่ งไร และเมอ่ื ได ้ คําตอบตรงกบั ทที่ ราบ จะชว่ ยทําใหค้ ณุ เกดิ ความมน่ั ใจ และเกดิ ความกลา้ ทจ่ี ะนําสตู รไป ใชใ้ นปัญหาอนื่ ตอ่ ไป ลองคดิ ดวู า่ =2*3 กบั =123*4.56 สตู รใดทงี่ า่ ยกวา่ กนั 7. ถา้ สตู รคํานวณไดค้ ําตอบอนื่ ตา่ งจากทต่ี อ้ งการ อยา่ คดิ วา่ ตวั เองสรา้ งสตู รผดิ แตค่ วร พยายามคดิ คน้ หาใหไ้ ดว้ า่ คําตอบทไ่ี ดน้ ัน้ หมายถงึ การคํานวณของอะไร หลายๆครัง้ ทเี ดยี วทคี่ ณุ จะไดส้ ตู รลดั และลบั แปลกๆทคี่ นอน่ื ไมร่ จู ้ กั กนั 8. อยา่ รรี อทจี่ ะสรา้ งสตู รใหเ้ ห็นผลลพั ธก์ บั ตาวา่ ถกู หรอื ผดิ ถา้ คณุ เอาแตก่ ลวั ๆกลา้ ๆไมย่ อม สรา้ งสตู รสกั ทกี จ็ ะไมม่ วี นั เกง่ สตู รไดห้ รอก ขอใหส้ รา้ งสตู รบอ่ ยๆ สตู รทผี่ ดิ หรอื ถกู เหลา่ นัน้ จะเป็ นบทเรยี นสอนคณุ ใหเ้ กง่ กวา่ คนอน่ื ทไ่ี มเ่ คยยอมทําผดิ 9. ถา้ ปัญหาการคํานวณซบั ซอ้ นยากมาก ควรแยกเซลลใ์ หใ้ ชเ้ ซลลห์ ลายๆเซลลแ์ ยก คํานวณสตู รแตล่ ะขนั้ จากนัน้ จงึ คอ่ ยหาวธิ นี ําสตู รมาตอ่ กนั เป็ นสตู รยาวๆสตู รเดยี วใน เซลลเ์ ดยี ว 10. พยายามหาทางแกป้ ัญหาสรา้ งสตู รดว้ ยตวั เองใหไ้ ด ้ ถา้ ทําไดเ้ องแลว้ คณุ จะเกดิ ความ ภาคภมู ใิ จ เกดิ ความกลา้ และอยากลอง ทําใหเ้ กง่ ขน้ึ ไปไดเ้ อง อยา่ ลอกสตู รคนอนื่ มาใช ้ ถา้ พยายามอยา่ งไรแลว้ คดิ สตู รไมอ่ อก ขอใหค้ น้ หาคําตอบจาก Google กอ่ นทจี่ ะเอย่ ปากถามใครหวงั วา่ คําแนะนํา 10 ขอ้ นจ้ี ะเป็ นแนวทางชว่ ยใหค้ ณุ กลายเป็ นคนเกง่ สตู ร Excel และขอฝากคนทช่ี อบใชส้ ตู รรนุ่ ใหมท่ เี่ พงิ่ เกดิ ขน้ึ ใน Excel 2007 ขน้ึ ไปวา่ อยา่ เพงิ่ รบี รอ้ นนําสตู รใหมๆ่ เหลา่ นัน้มาใช ้ เพราะตราบใดทเี่ พอ่ื นของคณุ ยงั คงใช ้ Excel รนุ่ เกา่ กวา่ อยู่ เขาจะไมส่ ามารถนําแฟ้มของคณุ ทส่ี รา้ งไวโ้ ดยใชส้ ตู รรนุ่ ใหมม่ าคํานวณไดเ้ ลย คณุ น่ันแหละทจ่ี ะตอ้ งรับภาระยอ้ นกลบั ไปแกไ้ ขสตู รรนุ่ ใหมใ่ หเ้ ป็ นรนุ่ เกา่ เพอ่ื เปิดแฟ้มใน Excel รนุ่ เกา่ ทคี่ นอนื่ เขายงั ไมเ่ ลกิ ใชก้ นั สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 78 of 147  Formula vs Functionทงั้ สองคํานถ้ี า้ แปลเป็ นไทยกแ็ ปลวา่ “สตู ร” เหมอื นกนั โดยคําวา่ Formula เป็ นคําทม่ี ีความหมายกวา้ งกวา่ Function กลา่ วคอื  Formula หมายถงึ สตู รทกุ อยา่ งทมี่ เี ครอื่ งหมายเทา่ กบั = นําหนา้ เชน่ =A1 หรอื =1+2 หรอื =Now()  Function หมายถงึ สตู รสําเร็จรปู ทต่ี ดิ มากบั โปรแกรม Excel เชน่ สตู ร Sum, Max, Min, If, VLookup หรอื Now เป็ นตน้ดงั นัน้ เพอื่ ทําความเขา้ ใจใหต้ รงกนั ในบทความนจ้ี ะใชค้ ําวา่ สตู ร ซงึ่ หมายถงึ ทงั้ Formula และFunction หรอื อกี นัยหนงึ่ สตู รกค็ อื อะไรกต็ ามทม่ี เี ครอื่ งหมาย = นําหนา้ สว่ นตวั ผมเองจะเขยี นอธบิ ายเรอื่ งสตู รในกระดาษโดยใชต้ วั ใหญป่ นตวั เล็ก เชน่ Sum หรอื VLookup เพอ่ื ชว่ ยใหอ้ า่ นแตล่ ะคําของสตู รไดง้ า่ ย แตเ่ มอื่ ถงึ คราวนําไปใชใ้ น Excel โปรดพมิ พส์ ตู รดว้ ยตวั เล็กทงั้ หมดเสมอText vs Numberคา่ ทพ่ี มิ พบ์ นั ทกึ ลงไปในเซลลห์ รอื ผลจากการคํานวณมอี ยเู่ พยี ง 3 อยา่ งคอื คา่ ทถ่ี อื เป็ นTextคา่ ทถ่ี อื เป็ น Number และคา่ ที่ error ไมส่ ามารถนําไปใชค้ ํานวณตอ่ ได ้  Text คอื คา่ ทพี่ มิ พล์ งไปในเซลลห์ รอื เกดิ จากสตู รคํานวณแลว้ จะชดิ ซา้ ยของเซลล์ ทนั ที (โดยไมต่ อ้ งกําหนด Format) เชน่ พมิ พค์ ําวา่ abc หรอื กขค หรอื แมแ้ ตต่ วั เลขท่ี พมิ พต์ อ่ ทา้ ยเครอ่ื งหมายฝนทอง เชน่ ‘123 หรอื ผา่ นการกําหนด Format Number ให ้ ถอื เป็ นแบบ Text ซง่ึ โดยทวั่ ไปหากเซลลด์ า้ นขวายงั วา่ งอยู่ จะพบวา่ ถา้ เซลลม์ ี Text ท่ี ยาวกวา่ ความกวา้ งของเซลล์ จะยอมให ้ Text นัน้ แสดงตอ่ ไปในเซลลด์ า้ นขวา  Number คอื คา่ ทพี่ มิ พล์ งไปในเซลลห์ รอื เกดิ จากสตู รคํานวณแลว้ จะชดิ ขวาของเซลล์ ทนั ที (โดยไมต่ อ้ งกําหนด Format) เชน่ พมิ พต์ วั เลข 123 หรอื พมิ พว์ นั ท่ี 14/2/2009 ซง่ึ โดยทว่ั ไปถา้ เซลลม์ คี วามกวา้ งไมพ่ อทจ่ี ะแสดงตวั เลขทงั้ หมด Excel จะแสดงดว้ ย เครอื่ งหมาย ####### ซา้ํ จนเต็มเซลล์ (แกไ้ ดโ้ ดยคลกิ ขวาลงไปในเซลลแ์ ลว้ สง่ั Format Cells > Alignment > กาชอ่ ง Shrink to fit เพอ่ื สง่ั ให ้ Excel ยอ่ ขนาด ของ Font ใหม้ ขี นาดพอดที จี่ ะแสดงไดเ้ ต็มความกวา้ งของเซลล)์  Error เป็ นผลทเี่ กดิ จากการคํานวณทไี่ มส่ ามารถนําไปคํานวณตอ่ ได ้ เชน่ o #N/A เมอ่ื สตู รหาขอ้ มลู ทต่ี อ้ งการไมพ่ บ (Not Available) o #NAME? เมอ่ื สตู รหาชอื่ ทต่ี อ้ งการไมพ่ บ เชน่ =answer โดยทใ่ี นแฟ้มนัน้ ไมไ่ ดต้ งั้ ชอ่ื Range Name วา่ answer ไวก้ อ่ น o #NULL! เมอ่ื สตู รหาตําแหน่งทตี่ ดั กนั ไมไ่ ด ้ เชน่ =A:A B:B สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 79 of 147   o #NUM! เมอื่ สตู รใชต้ วั เลขทเี่ ป็ นไปไมไ่ ดใ้ นการคํานวณ เชน่ =Sqrt(-1) o #REF! เมอื่ ตําแหน่งเซลลท์ เี่ คยอา้ งไวใ้ นสตู รถกู delete ทง้ิ ไป o #VALUE! เมอื่ สตู รไมส่ ามารถคํานวณได ้ เชน่ นําตวั เลขคณู ตวั อกั ษร =123*a หลกั การใช ้ Excel ทดี่ ี คอื คา่ ใดๆทเ่ี กยี่ วขอ้ งกบั ตวั เลข กข็ อใหเ้ ป็ นตวั เลขทชี่ ดิ ขวาของ เซลลเ์ สมอ ไมค่ วรใชต้ วั เลขทช่ี ดิ ซา้ ยของเซลล์ขนาดแฟ้ ม vs ประเภทขอ้ มลูขนาดของแฟ้มขน้ึ กบั ประเภทของขอ้ มลู โดยเรยี งลําดบั ขนาดแฟ้มจากเล็กไปใหญไ่ ดต้ ามน้ี 1. แฟ้มทเ่ี กบ็ ขอ้ มลู เป็ น Number เชน่ 1, 111, 123 2. แฟ้มทเ่ี ก็บขอ้ มลู เป็ น Text เชน่ a, aaa, abc 3. แฟ้มทเี่ กบ็ ขอ้ มลู เป็ นสตู รทค่ี นื คา่ เป็ น Number เชน่ =1, =111, =123 4. แฟ้มทเ่ี กบ็ ขอ้ มลู เป็ นสตู รทค่ี นื คา่ เป็ น Text เชน่ =”a”, =”aaa”, =”abc”ทแ่ี ปลกกค็ อื ขนาดของแฟ้ม ไมข่ นึ้ กบั ความยาวของคา่ ในเซลล์ เชน่ ถา้ เซลลม์ คี า่ ทบี่ นั ทกึ ไว ้เป็ น 1 หรอื เพม่ิ ความยาวตวั เลขเป็ น 111 หรอื 123 ก็จะมขี นาดแฟ้มเทา่ กนั กบั 1 ตวั เดยี ว หรอืถา้ สรา้ งเป็ นสตู ร =1 หรอื เพมิ่ ความยาวตวั เลขเป็ น =111 หรอื =123 จะมขี นาดแฟ้มเทา่ กนั กบั=1ดงั นัน้ ถา้ ตอ้ งการลดขนาดแฟ้มใหเ้ ล็กทสี่ ดุ แทนทจี่ ะพมิ พเ์ ลข 1 แลว้ สรา้ งสตู รบวกเลขตอ่ กนั ไปทลี ะเซลลใ์ หก้ ลายเป็ นลําดบั ตวั เลข 1, 2, 3, 4, 5 กค็ วรพมิ พเ์ ลขลงไปในเซลลโ์ ดยตรงแทนการสรา้ งสตู ร และสตู รใดๆทค่ี ํานวณเสร็จเรยี บรอ้ ยไปแลว้ และไมม่ กี ารเปลยี่ นแปลงคําตอบตา่ งจากเดมิ อกี แลว้ เรากค็ วร Copy แลว้ Paste Special ใหก้ ลายเป็ น Value จะทําใหแ้ ฟ้มเล็กลงไปมากทเี ดยี วสว่ นเซลลส์ ตู รทเ่ี กดิ Error ขน้ึ กค็ วรใชส้ ตู ร IF ปรับ Error ใหเ้ ปลยี่ นเป็ นคา่ อนื่ แทน ดงั น้ี  ในกรณีท่ี A1 คนื คา่ เป็ น Number ใหใ้ ช ้ =IF( IsError(A1), 0, A1)  ในกรณีท่ี A1 คนื คา่ เป็ น Text ใหใ้ ช ้ =IF( IsError(A1), “คําเตอื นใดๆกไ็ ด”้ , A1)ซงึ่ ตวั ผมเอง นยิ มใชส้ ตู ร =IF( IsError(A1), 0, A1) ทงั้ ในกรณีทเ่ี ดมิ คนื คา่ เป็ น Number หรอืText กต็ าม เพราะสามารถใช ้ Format ปรับการแสดงเลข 0 ใหแ้ สดงเป็ นคา่ อน่ื หรอื แมแ้ ตท่ ําให ้ดเู หมอื นกลายเป็ นเซลลว์ า่ งไดต้ อ่ ไป สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 80 of 147  ตาํ แหนง่ เซลลท์ เี่ ลอื กสําหรบั สรา้ งสตู รเห็นหวั ขอ้ นแ้ี ลว้ คงสงสยั กนั วา่ ตําแหน่งเซลลท์ เี่ ลอื กสรา้ งสตู รลงไปนัน้ เป็ นประเด็นทตี่ อ้ งใสใ่ จกนั ดว้ ยหรอื ถา้ คดิ กนั งา่ ยๆวา่ “ในเมอื่ ฉันตอ้ งการจะสรา้ งสตู รลงไปตรงนกี้ ็ตอ้ งตามใจฉัน ฉันจะสรา้ งสตู รลงไปตรงนเี้ สยี อยา่ ง ใครจะทําไม” ก็แสดงวา่ คณุ มองขา้ มอะไรทเี่ ป็ นเรอื่ งสําคญั อยา่ งหนง่ึ ไปแลว้ตําแหน่งเซลลท์ ค่ี ณุ สรา้ งสตู รหาคําตอบลงไป สามารถชเี้ ป็ นชต้ี ายใหก้ บั งานของคณุ ทเี ดยี วเพราะถา้ อยมู่ าวนั หนง่ึ เมอื่ ยา้ ยเซลลส์ ตู รไปทตี่ ําแหน่งอนื่ สตู รทเี่ คยคํานวณหาคําตอบไดต้ ลอดมา อาจคนื คา่ เป็ นคําตอบทผ่ี ดิ หรอื แสดงผลลพั ธแ์ ตกตา่ งไปจากเดมิ ก็ได ้วธิ ที ดสอบสตู รทค่ี ณุ หรอื ใครสรา้ งขนึ้ วา่ เป็ นสตู รทใ่ี ชไ้ ดห้ รอื ไม่ ใหท้ ดลองยา้ ยเซลลส์ ตู รไปทอี่ น่ืยา้ ยไปเรอื่ ยๆหลายๆตําแหน่ง พรอ้ มกนั นัน้ ทดลองยา้ ยตารางขอ้ มลู ทใ่ี ชอ้ า้ งองิ ไปทอ่ี นื่ ดว้ ย ลองInsert Row หรอื Column แทรกตารางขอ้ มลู เขา้ ไปอกี ถา้ สตู รนัน้ ยงั คงหาคําตอบได ้เหมอื นเดมิ แสดงวา่ สตู รนัน้ ใชง้ านได ้ทราบไหมวา่ ถา้ ตารางขอ้ มลู ทเ่ี ก็บคา่ อยใู่ นตารางชว่ ง B2:D5 คณุ ควรสรา้ งสตู รคํานวณซงึ่เกย่ี วขอ้ งกบั การใชข้ อ้ มลู ในตารางนไ้ี วท้ เ่ี ซลลใ์ ด ซง่ึ จะรับประกนั วา่ สตู รทสี่ รา้ งขน้ึ จะสามารถนําไปใชง้ านตอ่ ไดท้ กุ ท่ีคําตอบก็คอื หา้ มสรา้ งสตู รในแนวเดยี วกบั ตารางขอ้ มลู น่ันคอื อยา่ สรา้ งสตู รในชว่ ง Column Bถงึ D และชว่ ง Row 2 ถงึ 5สาเหตทุ หี่ า้ ม เพราะการสรา้ งสตู รใดๆทม่ี ตี ําแหน่งอา้ งองิ ไปยงั คา่ ในแนวเดยี วกบั ตารางขอ้ มลูอาจคนื คา่ เป็ นคําตอบเป็ นคา่ ในตารางตามแนวเดยี วกนั กไ็ ด ้หากสรา้ งสตู รในเซลลซ์ ง่ึ ไมไ่ ดอ้ ยใู่ นแนวเดยี วกบั ชว่ ง Column B ถงึ D และชว่ ง Row 2 ถงึ 5แลว้ คณุ หาทางปรับสตู รนัน้ ใหค้ ํานวณหาคําตอบทตี่ อ้ งการ ยอ่ มรับประกนั ไดว้ า่ สตู รนัน้ เป็ นสตู รทใ่ี ชง้ านไดจ้ รงิ และจะยงั คงคนื คา่ เป็ นคําตอบเดมิ ไปตลอด ไมว่ า่ จะยา้ ยเซลลส์ ตู รไปทต่ี ําแหน่งอนื่ กต็ ามพอพบวา่ สตู รทํางานไดจ้ รงิ แลว้ จากนัน้ จะยา้ ยเซลลส์ ตู รไปวางไวใ้ นแนวคขู่ นานกบั ตารางขอ้ มลูทใ่ี ชใ้ นการคํานวณก็ไมว่ า่ กนัสตู รก็มจี งั หวะหายใจดว้ ยนะผทู ้ ใ่ี ช ้ Excel เป็ นประจําตอ้ งรจู ้ กั สตู รตอ่ ไปนเ้ี ป็ นอยา่ งดี =IF(C2=0, 0, B2/C2)สตู รขา้ งตน้ นค้ี อื สตู รทใี่ ชส้ ําหรับปรับสตู รทนี่ ําตวั เลขมาหารกนั ไมใ่ หแ้ สดงคําเตอื น Error วา่#DIV/0! ขน้ึ มา สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 81 of 147  เมอื่ เซลล์ C2 ซง่ึ เป็ นเลขตวั หารมคี า่ เป็ น 0 ใหค้ นื คา่ 0 เป็ นตวั เลขคําตอบแทน แตถ่ า้ เซลล์ C2ไมไ่ ดเ้ ป็ นเลข 0 กย็ อ่ มนํา C2 มาหาร B2 ไดต้ ามปกติ ผทู ้ สี่ รา้ งสตู รหารกนั จะหลกี เลยี่ งสตู รดงั กลา่ วนไี้ มไ่ ด ้ขอถามวา่ เวลาทคี่ ณุ สรา้ งสตู ร คณุ มพี ฤตกิ รรมในการสรา้ งสตู รกนั อยา่ งไร คณุ จะพมิ พล์ งไปในเซลลว์ า่ =if(c2=0,0,b2/c2) รวดเดยี วใหค้ รบทงั้ หมดแลว้ จงึ กดป่ มุ Enter หรอื ไม่ หรอื จะคอ่ ยๆพมิ พ์ =if แลว้ หยดุ หายใจพักหนง่ึ แลว้ จงึ พมิ พ์ c2=0 ตอ่ แลว้ ก็หยดุ พักหายใจอกี เฮอื กหนง่ึ จงึคอ่ ยๆพมิ พส์ ว่ นทเ่ี หลอื ตอ่ขอ้ ผดิ พลาดทพ่ี บบอ่ ยในการสรา้ งสตู รของคนทเี่ พงิ่ เรยี นรกู ้ ารใช ้ Excel ก็คอื มกั หลงลมืเครอื่ งหมายวงเล็บ พมิ พเ์ ครอ่ื งหมาย Comma ขาดไป บางครัง้ พมิ พส์ ลบั ท่ี บางครัง้ พมิ พข์ าดบางครัง้ พมิ พเ์ กนิ ทําใหเ้ มอื่ กดป่ มุ Enter ลงไปแลว้ Excel ตอ้ งเตอื นขนึ้ มาบนจอใหค้ ณุ ทราบวา่สตู รทสี่ รา้ งนัน้ ผดิผสู ้ รา้ งสตู รตอ้ งมสี มาธพิ อตวั สมาธทิ วี่ า่ นเ้ี กดิ จากการรจู ้ กั วา่ เมอื่ ใดจะหยดุ เมอื่ ใดจะสรา้ งตอ่ ถา้ รู ้หลกั จังหวะหายใจจะชว่ ยใหค้ ณุ สรา้ งสตู รไดง้ า่ ยขน้ึ ไมว่ า่ สตู รสนั้ ๆหรอื สตู รยากๆยาวๆกใ็ ชห้ ลกัจงั หวะหายใจนกี้ นั ทัง้ นัน้จังหวะหายใจทวี่ า่ นี้ ไมใ่ ชจ่ ังหวะหายใจของ Excel หรอกนะ Excel มนั ไมไ่ ดม้ กี ารหายใจเหมอื นกบั มนุษย์ แตเ่ ป็ นจังหวะหายใจของคณุ นั่นแหละ ตอ้ งรวู ้ า่ เมอื่ ใดทค่ี วรจะหยดุ แลว้ หายใจสกั เฮอื กหนง่ึ แลว้ จงึ สรา้ งสตู รตอ่ ซงึ่ เรอื่ งการหายใจนไ้ี มต่ อ้ งลอกจงั หวะการหายใจใหเ้ หมอื นกบัผมก็ได ้ ตวั ใครตวั มนั ขอใหเ้ ลอื กจังหวะหายใจทต่ี วั เองคดิ วา่ ถนัดก็แลว้ กนัขอยกสตู รทงั้ หมดมาดกู นั อกี ครัง้=IF(C2=0, 0, B2/C2)มาดกู นั วา่ ถา้ เป็ นวธิ ขี องผม จะสรา้ งสตู รนโ้ี ดยหยดุ พักเพอื่ หายใจกนั ตรงไหนบา้ ง เอา้ หายใจเขา้แลว้ นะ จากนัน้ เรม่ิ สรา้ งสตู รโดยใชว้ ธิ ตี ามลําดบั ตอ่ ไปน้ี 1. พมิ พเ์ ครอ่ื งหมายเทา่ กบั 2. พมิ พค์ ําวา่ if( 3. หยดุ เพอ่ื หายใจ จากนัน้ สรา้ งสตู รตอ่ ดว้ ยขนั้ ตอ่ ไป 4. หยบิ เมาสค์ ลกิ เซลล์ C2 5. แลว้ พมิ พ์ =0, ตอ่ 6. หยดุ เพอ่ื หายใจ 7. ในใจอา่ นสตู รทไี่ ดต้ ามน้ี =if(C2=0, วา่ ถา้ เซลลต์ วั หารมคี า่ เทา่ กบั 0 ละ่ กอ้ 8. พมิ พ์ 0, ตอ่ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 82 of 147   9. หยดุ เพอ่ื หายใจ 10. ในใจอา่ นสตู รทพี่ มิ พ์ 0, วา่ ใหค้ ําตอบทไ่ี ดเ้ ป็ น 0 แทนนะ จากนัน้ 11. หยบิ เมาสค์ ลกิ เซลล์ B2 12. พมิ พเ์ ครอื่ งหมายหาร / 13. แลว้ หยบิ เมาสค์ ลกิ เซลล์ C2 14. หยดุ เพอ่ื หายใจ 15. ในใจอา่ นสตู ร B2/C2 ทไ่ี ดม้ าวา่ แตถ่ า้ ตวั หารไมใ่ ชเ่ ลข 0 ใหค้ ําตอบทไี่ ดเ้ ป็ นสตู รหาร กนั ตามปกติ 16. จากนัน้ จงึ กดป่ มุ Enter รับสตู รลงไปในเซลล์ โดยไมต่ อ้ งพมิ พเ์ ครอื่ งหมายวงเล็บปิด เพราะถา้ มวี งเล็บเปิดตวั เดยี ว Excel จะพมิ พเ์ ครอื่ งหมายวงเล็บปิดใหเ้ องจบั หลกั ไดไ้ หมเอย่ ขอใหส้ งั เกตวา่ ใหห้ ยดุ เพอ่ื หายใจไดห้ ลงั จากพมิ พจ์ บตรงเครอื่ งหมายนัน้ ๆไง ถา้ ใชเ้ ครอ่ื งหมายจดุ จดุ แทนชว่ งหยดุ เพอ่ื หายใจ จะไดส้ ตู รตามแบบน้ี=if(.........C2=0,.........0,......... B2/C2ฝึกหยดุ เพอื่ พักหายใจใหไ้ ดจ้ ังหวะ วธิ นี จี้ ะชว่ ยลดขอ้ ผดิ พลาดหลงลมื การใสเ่ ครอ่ื งหมายวงเล็บหรอื เครอ่ื งหมาย Comma ในการสรา้ งสตู รไปไดเ้ ยอะทเี ดยี วธรรมชาตขิ องสตู รถา้ เขา้ ใจธรรมชาตติ อ่ ไปนข้ี องสตู ร จะทําใหเ้ ราใชส้ ตู ร Excel ไดง้ า่ ยโดยไมต่ อ้ งทอ่ งจําและลดขอ้ ผดิ พลาดเมอ่ื นําสตู รมาใช ้1. ชอ่ื สตู รเป็ นตัวอกั ษรภาษาองั กฤษตวั ใหญ่ เชน่ IF, MAX, MIN ดงั นัน้ เวลาพมิ พส์ ตู รใหพ้ มิ พ์ ตวั เล็ก พอกดป่ มุ Enter แลว้ จะพบวา่ ถา้ เราสะกดชอ่ื สตู รถกู ตอ้ ง Excel จะเปลย่ี นตวั อกั ษร จากตวั เล็กไปเป็ นตวั ใหญท่ งั้ หมดใหท้ นั ที2. เราสามารถพมิ พส์ ตู รโดยถา้ ในสตู รมเี ครอ่ื งหมายวงเล็บเปิดเพยี งวงเล็บเดยี ว พอกดป่ มุ Enter จะพบวา่ Excel พมิ พเ์ ครอ่ื งหมายวงเล็บปิดใหเ้ อง แตถ่ า้ มวี งเล็บเปิดหลายตวั เราตอ้ ง ใสเ่ ครอื่ งหมายปิดวงเล็บใหค้ รบทกุ คู่ เพราะถา้ กดป่ มุ Enter ลงไป Excel จะพยายามคดิ ใส่ เครอื่ งหมายวงเล็บปิดใหค้ รบแตอ่ าจใสว่ งเล็บปิดผดิ คใู่ หก้ ็ได ้3. วงเล็บในสตู รตอ้ งใสใ่ หค้ รบคแู่ ละถกู ตําแหน่ง มวี งเล็บเกนิ ดกี วา่ ขาดวงเล็บ4. ถา้ ไมใ่ สว่ งเล็บ Excel จะไลค่ ํานวณตามลําดบั ดงั นี้ ยกกําลงั กอ่ นแลว้ ตามดว้ ยคณู หรอื หาร แลว้ ตามดว้ ยบวกหรอื ลบ (คณู หาร หรอื บวกลบ มศี กั ดศ์ิ รเี ทา่ กนั จะไลค่ ํานวณจากซา้ ยไป ขวาแลว้ แตว่ า่ เครอ่ื งหมายใดมกี อ่ น) ดตู วั อยา่ งทา้ ยขอ้ 7 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 83 of 147  5. โครงสรา้ งสตู ร Excel ถกู สรา้ งขนึ้ มาเลยี นแบบลําดบั ความคดิ ของมนุษย์ ดงั นัน้ ถา้ เราเขา้ ใจ ตวั เองวา่ เรามองเราคดิ ทลี ะขนั้ อยา่ งไร เราก็จะสรา้ งสตู รไดง้ า่ ยโดยไมต่ อ้ งทอ่ ง เชน่ เวลา เราคดิ เรอ่ื งเงอ่ื นไขวา่ ถา้ ทําดี ตอ้ งไดด้ ี แตถ่ า้ ไมท่ ําดี ตอ้ งไดเ้ รอ่ื งอนื่ ๆ ตวั สตู ร IF ของ Excel ก็จะมลี ําดบั ในโครงสรา้ งสตู รตามแบบทเ่ี ราคดิ ออกมาเป็ น =IF(ทําดใี ชไ่ หม, ไดด้ ,ี ไดอ้ น่ื ๆ) ขอใหอ้ า่ นรายละเอยี ดประเด็นนจ้ี ากบทความเรอ่ื ง เมอ่ื สตู ร Excel มชี วี ติ จติ ใจ หรอื จากลงิ ค์ www.excelexperttraining.com/blogs/archives/z300-ExcelCore000706.php6. ในโครงสรา้ งสตู ร ถา้ มกี ารอา้ งองิ กบั ตําแหน่งเซลลต์ ามแนวนอนและแนวตงั้ จะตอ้ งใส่ ตําแหน่งอา้ งองิ ตามแนวนอนไวก้ อ่ นแนวตงั้ เสมอ ดไู ดจ้ ากสตู ร Index หรอื สตู ร Offset (ดงั นัน้ เราควรสรา้ งสตู รคํานวณบวกลบคณู หารอน่ื ๆใหใ้ ชต้ ําแหน่งอา้ งองิ ตามแนวนอนกอ่ น แนวตงั้ ดว้ ยเชน่ กนั เพอ่ื เป็ นแบบแผนชว่ ยใหเ้ ขา้ ใจทไี่ ปทมี่ าและแกะสตู รไดง้ า่ ย)7. ถา้ ในโครงสรา้ งสตู รประเภทฐานขอ้ มลู มตี วั แปร (Option) ทอ่ี าจใสห่ รอื ไมใ่ สก่ ไ็ ด ้ เชน่ สตู ร VLookup หรอื สตู ร Match ขอใหย้ ดึ หลกั วา่ 7.1. ถา้ ไมใ่ ส่ Option หรอื ละไว ้ หรอื ใสต่ วั เลข 1 นัน้ สตู รนจ้ี ะใชก้ บั ตารางขอ้ มลู ทเ่ี รยี งคา่ จากนอ้ ยไปมาก (จําไวว้ า่ เรยี งตามธรรมชาตติ อ้ งเรยี งจากนอ้ ยไปมากเสมอ) 7.2. ถา้ ใส่ Option เป็ น False หรอื ใสต่ วั เลข 0 สตู รนจ้ี ะใชก้ บั ตารางขอ้ มลู ทไ่ี มเ่ รยี งลําดบั (จําไวว้ า่ 0 แปลวา่ ไม่ หมายถงึ ไมต่ อ้ งเรยี งลําดบั ) 7.3. ถา้ ใส่ Option เป็ นตวั เลข -1 นัน้ สตู รนจี้ ะใชก้ บั ตารางขอ้ มลู ทเี่ รยี งคา่ จากมากไปนอ้ ย (จําไวว้ า่ เรยี งผดิ ธรรมชาตติ อ้ งเรยี งจากมากไปนอ้ ย)ตวั อยา่ งลําดบั การคาํ นวณตามเครอ่ื งหมายบวกลบคณู หารถา้ สรา้ งสตู ร =1+2-3*4/5^6 ลงไปในเซลล์ Excel จะคํานวณตามหลกั การดงั น้ี 1. คํานวณเครอื่ งหมายยกกําลงั ^ กอ่ น นั่นคอื จะคํานวณ 5^6 2. ตามดว้ ยการคํานวณคณู หรอื หาร แลว้ แตว่ า่ เครอ่ื งหมายใดมากอ่ นจากซา้ ยไปขวา นั่นคอื จะคํานวณ 3*4 แลว้ จงึ นําผลคณู ทไ่ี ดไ้ ปหารดว้ ยคําตอบทไ่ี ดจ้ าก 5^6 3. ตามดว้ ยการคํานวณบวกหรอื ลบ แลว้ แตว่ า่ เครอ่ื งหมายใดมากอ่ นจากซา้ ยไปขวา นั่นคอื จะคํานวณ 1+2 แลว้ จงึ นําผลบวกทไ่ี ดไ้ ปลบกบั ผลลพั ธท์ ไ่ี ดจ้ าก 3*4/5^6จะสรา้ งสตู รใหว้ นุ่ กนั ไปไดแ้ คไ่ หนสตู รสําเร็จรปู ของ Excel 2003 ทเี่ ราใชก้ นั จนชนิ (จนไมอ่ ยากเปลยี่ นมาใช ้ Excel 2007 หรอืExcel รนุ่ ทใ่ี หมก่ วา่ ) มขี อ้ กําหนดในตวั สตู รวา่ ในวงเล็บของสตู รสําเร็จรปู หนงึ่ ๆนัน้ สามารถซอ้ นสตู รเขา้ ไปไดอ้ กี 7 สตู ร และในวงเล็บของบางสตู รจะถกู แบง่ Argument หรอื ตวั แปรทคี่ น่ั ดว้ ยเครอ่ื งหมาย comma แบง่ ไดส้ งู สดุ 30 Arguments (หรอื อกี นัยหนงึ่ ใส่ Comma ไดส้ งู สดุ 29 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 84 of 147  ตวั เพอ่ื แบง่ สว่ นในวงเล็บออกเป็ น 30 สว่ น) และสตู รทย่ี าวทสี่ ดุ ทส่ี ามารถพมิ พล์ งไปในเซลล์หนง่ึ ๆไดน้ ัน้ ยาวไดส้ งู สดุ 1,024 ตวั อกั ษรขอ้ กําหนดเหลา่ นน้ี ับวา่ เพยี งพอกบั การคํานวณทว่ั ไป นานๆทจี งึ มโี จทยท์ ตี่ อ้ งใชจ้ นเกนิ กวา่ขดี จํากดั ซงึ่ แกไ้ ขไดง้ า่ ยๆโดยแยกการคํานวณเป็ นสว่ นๆ แลว้ แบง่ แตล่ ะเซลลใ์ หแ้ ยกคํานวณแตล่ ะสว่ นของสตู ร จากนัน้ จงึ นําผลการคํานวณทไี่ ดม้ าคํานวณรว่ มกนั ตอ่ เป็ นขนั้ ๆ หรอื ไมก่ ต็ อ้ งฉลาดเลอื กใชส้ ตู รอน่ื ทคี่ ํานวณไดค้ ําตอบแบบเดยี วกนั แทนสําหรับสตู รสาํ เร็จรปู ใน Excel รนุ่ 2007 เป็ นตน้ มา ปรับขอ้ กําหนดดงั กลา่ วเป็ นดงั นี้1. ในวงเล็บของสตู รสําเร็จรปู สามารถซอ้ นสตู รเขา้ ไปไดอ้ กี 64 สตู ร (Nested levels of functions)2. ในวงเล็บของสตู รสําเร็จรปู สามารถแบง่ ออกเป็ น 255 arguments (Arguments in Functions)3. ในเซลลห์ นงึ่ ๆรับสตู รไดย้ าวทสี่ ดุ 8,192 characters (Length of formula contents)ขอ้ กําหนดเหลา่ นเ้ี ปิดกวา้ งใหเ้ ราสรา้ งสตู รไดส้ บายขนึ้ ซงึ่ ขอแนะนําวา่ อยา่ ใชค้ วามสบายจนเกนิตวั เพราะตวั คณุ นั่นแหละทจี่ ะถกู ลงโทษหากสรา้ งสตู รแบบสบายเกนิ ไป เพราะหากตอ้ งยอ้ นกลบั มาแกะสตู รเพอื่ แกไ้ ขอกี ในภายหลงั มนั ไมใ่ ชง่ านทง่ี า่ ยเลยทจ่ี ะแกะสตู รซอ้ นกนั หลายสบิ ชนั้พงึ ระลกึ ไวเ้ สมอวา่ สตู รทคี่ ณุ สรา้ งตงั้ แตแ่ รกนัน้ ตอ้ งเป็ นสตู รทใี่ ชง้ านไดต้ ลอดไปโดยไมต่ อ้ งยอ้ นกลบั มาแกไ้ ขสตู รอกี แตถ่ า้ จําเป็ นตอ้ งแกไ้ ขสตู ร กต็ อ้ งแกไ้ ขไดง้ า่ ย ไมว่ า่ คณุ หรอื เพอ่ื นของคณุ ก็ตอ้ งแกะสตู รไดง้ า่ ยดงั นัน้ การท่ี Microsoft ปรับขอ้ จํากดั ของสตู รใหย้ ดื หยนุ่ มากขนึ้ นี้ ถา้ สตู รใดทคี่ ณุ คดิ วา่ มเี งอ่ื นไขของการคํานวณไมย่ ากนัก\"ตอ่ การจําของคณุ \"และงา่ ยทจ่ี ะยอ้ นกลบั มาแกไ้ ข กเ็ ชญิ ซอ้ นสตู รเขา้ไปหลายๆชนั้ ไดต้ ามใจ แตถ่ า้ มที างเลอื กอนื่ ทด่ี กี วา่ เชน่ มสี ตู รอน่ื หรอื ตอ้ งปรับโครงสรา้ งตารางเสยี ใหมเ่ พอ่ื ใหเ้ ขา้ กบั สตู รนัน้ ก็ขอแนะนําใหต้ ดั สนิ ใจเลอื กใหด้ ีสตู รทห่ี าคา่ ได้ ก็ใชบ้ อกตาํ แหนง่ ได้เชอ่ื ไหมวา่ เรอ่ื งทจ่ี ะอธบิ ายนแ้ี ทบทกุ คนทใี่ ช ้ Excel ทราบกนั ดอี ยแู่ ลว้ ใชก้ นั อยทู่ กุ วนั แตน่ กึ ไม่ถงึ วา่ สตู รทห่ี าคา่ ได ้ จะใชบ้ อกตําแหน่งทแี่ ปลกแตกตา่ งจากทเี่ ขา้ ใจกนั อยา่ งไรพอสรา้ งสตู รลงไปในเซลลใ์ ดๆกต็ ามวา่ =A1 สตู รนกี้ ็จะดงึ คา่ จากเซลล์ A1 มาแสดงใหเ้ ห็นและพรอ้ มกนั นัน้ เราก็จะทราบดว้ ยวา่ คา่ ทไี่ ดน้ ม้ี าจากเซลล์ A1 เพราะดจู ากตวั สตู รทใ่ี ชต้ ําแหน่งอา้ งองิ =A1 น่ันเอง สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 85 of 147  ทพี่ เิ ศษไปกวา่ นี้ หากคณุ ตดั กาในชอ่ ง Allow edit directly in cells ทงิ้ ไป (ใน Excel Options> Advanced หรอื ใน Excel 2003 ใชเ้ มนู Tools > Options > Edit) จะพบวา่ เมอ่ื ดบั เบลิ คลกิทเ่ี ซลลส์ ตู ร =A1 จะยา้ ยตําแหน่ง cursor ไปทเี่ ซลลต์ น้ ทาง A1 ใหท้ นั ทีสง่ิ ทอี่ ธบิ ายขา้ งตน้ นเี้ คยอธบิ ายมาหลายตอ่ หลายครัง้ ในการอบรมและในเว็บExcelExpertTraining.com ซงึ่ พฤตกิ รรมแบบนแ้ี หละทน่ี ําไปสเู่ รอื่ งแปลกทคี่ าดไมถ่ งึ จากเดมิทเ่ี ขยี นวา่ \"สตู รทห่ี าคา่ ได ้ ก็ใชบ้ อกตําแหน่งได\"้ ขอเขยี นใหมใ่ หล้ ะเอยี ดชดั เจนกวา่ เดมิ วา่ สตู รใดๆทสี่ ามารถหาคา่ ทเ่ี กบ็ ไวจ้ ากตารางไดโ้ ดยตรง สตู รนัน้ ยอ่ มเป็ นสตู รทบี่ อก ตําแหน่งได ้ โดยอยา่ สรา้ งสตู รนัน้ ลงไปในเซลล์ แตต่ อ้ งนําสตู รนัน้ ไปซอ้ นในสตู รอน่ื ก็จะ กลายเป็ นสตู รทบี่ อกตําแหน่งยกตวั อยา่ งเชน่ สตู ร =INDEX(A1:B10,5,2) เป็ นสตู รหาคา่ จากตารางขอ้ มลู ชว่ ง A1:B10 ในตําแหน่ง Row 5 ตดั กบั Column 2 จะไดค้ า่ จากเซลล์ B5ถา้ อยากจะตามไปดวู า่ เซลล์ B5 อยทู่ ไ่ี หนโดยไมต่ อ้ งนั่งแกะสตู รวา่ Row 5 ตดั กบั Column 2คอื เซลลใ์ ด ใหห้ ยบิ เมาสค์ ลกิ ลากทบั สตู ร =INDEX(A1:B10,5,2) ทแ่ี สดงบน Formula Barจากนัน้ กดป่ มุ F5 แลว้ กดป่ มุ Enter จะพบวา่ สตู ร =INDEX(A1:B10,5,2) เปลยี่ นไปเป็ น =B5ใหท้ นั ที พอเห็นดงั นแี้ ลว้ ใหก้ ดป่ มุ Esc เพอ่ื ทําใหส้ ตู รคนื สสู่ ภาพเดมิยง่ิ กวา่ นัน้ ถา้ Copy สตู ร =INDEX($A$1:$B$10,5,2) ไวก้ อ่ นแลว้ กดป่ มุ F5 แลว้ กดป่ มุ Ctrl+vเพอื่ Paste สตู รนล้ี งไปในชอ่ ง Reference แลว้ กดป่ มุ Enter จะพบวา่ Excel พาคณุ ยา้ ยตําแหน่งไปทเ่ี ซลล์ B5 ใหเ้ ลย (สงั เกตวา่ วธิ นี ต้ี อ้ งกําหนดตําแหน่งอา้ งองิ เป็ น Absolute คอื$A$1:$B$10 กอ่ นดว้ ย)ยงิ่ กวา่ นข้ี องยง่ิ กวา่ นัน้ อกี ถา้ นําสตู ร INDEX(A1:B10,5,2) ไปซอ้ นในสตู รอนื่ เชน่ สตู ร Offsetจะทําใหส้ ตู ร INDEX(A1:B10,5,2) ทําหนา้ ทช่ี ตี้ ําแหน่งตอ่ ใหก้ บั Offsetขอใหส้ รา้ งสตู ร =OFFSET(INDEX(A1:B10,5,2),2,3) แลว้ ลองใชว้ ธิ ขี า้ งตน้ คน้ หาวา่ หมายถงึเซลลใ์ ด จะพบวา่ เป็ นเซลล์ E7 ใชไ่ หม เพราะเซลล์ E7 เป็ นเซลลท์ อ่ี ยถู่ ดั ลงมาจากเซลล์ B5จํานวน 2 row และถดั ไปดา้ นขวาจํานวน 3 column เทยี บเทา่ กบั การใชส้ ตู ร=OFFSET(B5,2,3) นั่นเองแตถ่ า้ สรา้ งสตู ร =INDEX(A1:B10,5,2) ไวใ้ นเซลล์ D12 แลว้ ใชส้ ตู ร Offset อา้ งถงึ D12 ในแบบ =OFFSET(D12,2,3) จะพบวา่ D12 ไมไ่ ดช้ ว่ ยบอกตําแหน่งตอ่ ใหก้ บั สตู ร Offset เพราะสตู ร Index ในเซลล์ D12 ทําหนา้ ทหี่ าคา่ จากเซลล์ B5 มาแสดงเสร็จก็จบหนา้ ทไี่ ปแลว้สตู รทส่ี ามารถหาคา่ ทเี่ ก็บไวจ้ ากตารางไดโ้ ดยตรง ซง่ึ ทํางานไดแ้ บบทอ่ี ธบิ ายนี้ ไดแ้ ก่ If,Choose, Index, Offset, Indirect (แตไ่ มร่ วมสตู ร VLookup เพราะมนั ไมไ่ ดห้ าคา่ ไดโ้ ดยตรง)ซงึ่ ไมจ่ ําเป็ นตอ้ งคนื คา่ เพยี งคา่ เดยี วเป็ นตําแหน่งเซลลเ์ ดยี ว โดยสามารถคนื คา่ เป็ นตารางเพอื่ใชบ้ อกตําแหน่งตารางทงั้ ตารางก็ยงั ได ้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 86 of 147  ดตู วั อยา่ งประกอบบทความนไี้ ดจ้ าก\"www.excelexperttraining.com/forums/content.php?r=191-สตู ร-ตําแหน่งอา้ งองิ \"จาํ นวนเซลลส์ ตู รเดยี วผสู ้ รา้ งสตู ร Excel ทวั่ ไป คนุ ้ เคยกบั การสรา้ งสตู รหนง่ึ ทค่ี นื คา่ เพยี งหนงึ่ คา่ หรอื อกี นัยหนงึ่เรยี กวา่ สตู รเดยี วคนื คา่ เดยี ว ซงึ่ สรา้ งลงไปในเซลลเ์ ดยี ว แตน่ อ้ ยคนนักทจ่ี ะทราบวา่ สตู ร Excelสตู รหนง่ึ ๆสามารถคนื คา่ เป็ นคําตอบไดห้ ลายคา่ ก็เป็ นไปไดเ้ หมอื นกนั สตู รพวกหลงั นเ้ี รยี กวา่สตู ร Arrayการทเี่ ราเลอื กเซลลเ์ ดยี ว แลว้ พมิ พส์ ตู รลงไปในเซลลเ์ ดยี วนัน้ มสี าเหตเุ นอ่ื งจากสตู รนัน้ คนื คา่ไดค้ ําตอบเพยี งคา่ เดยี วถา้ สตู รหนงึ่ ๆคนื คา่ เป็ นคําตอบไดห้ ลายคา่ หากสรา้ งสตู รนัน้ ลงไปในเซลลเ์ ดยี ว ยอ่ มไมถ่ กู ตอ้ งแมจ้ ะเห็นคําตอบแสดงขน้ึ มากต็ าม เพราะคําตอบทแี่ สดงขนึ้ มาใหเ้ ห็นนัน้ เป็ นเพยี งคําตอบแรกคําตอบเดยี ว เราหมดโอกาสทราบคําตอบคา่ อนื่ ๆพออา่ นถงึ ตรงนี้ คนทไี่ มร่ จู ้ กั กบั สตู ร Array คงเรมิ่ สงสยั วา่ สตู รแบบไหนกนั ทม่ี คี ําตอบไดห้ ลายคา่ ในสตู รเดยี ว มาทดลองโดยเรม่ิ จากพมิ พค์ า่ ใดๆกไ็ ดล้ งไปในเซลล์ A1:A3 สมมตวิ า่ พมิ พ์ 11,22, 33 ตามลําดบัแลว้ สรา้ งสตู ร =A1:A3 ลงไปในเซลล์ B1 จะเห็นคําตอบเป็ นเลข 11 คา่ เดยี วใชไ่ หมจากนัน้ ใหแ้ กะดคู า่ ทแ่ี ทจ้ รงิ ในสตู ร โดยกดป่ มุ F2 ตามดว้ ยป่ มุ F9 จะพบวา่ สตู ร =A1:A3เปลยี่ นเป็ น ={11;22;33}ขอใหส้ งั เกตวา่ เครอ่ื งหมายวงเล็บ { ทเ่ี กดิ ขนึ้ นัน้ อยหู่ ลงั จากเครอื่ งหมายเทา่ กบั ซง่ึ แสดงคา่แบบ Array และระหวา่ งตวั เลขจะมเี ครอ่ื งหมาย semi-colon ; คน่ั ซง่ึ แสดงวา่ คา่ ถดั ไปอยใู่ นRow ถดั ไป (หรอื ขน้ึ Row ใหม)่พอเห็นคา่ แลว้ วา่ สตู รนมี้ ี 3 คา่ และแตล่ ะคา่ อยตู่ ามแนวตงั้ กใ็ หก้ ดป่ มุ Esc เพอื่ ยอ้ นกลับไปเป็ นสตู ร =A1:A3 ตามเดมิ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 87 of 147  คราวนถี้ งึ ขนั้ ตอนสําคญั หากตอ้ งการกระจายคา่ แตล่ ะคา่ ในสตู ร Array ลงไปในเซลล์ ใหเ้ รมิ่จากเลอื กเซลล์ 3 เซลลต์ ามแนวตงั้ (เพราะเราทราบจากการแกะสตู รมากอ่ นแลว้ วา่ มคี า่ 3 คา่ตามแนวตงั้ ) โดยจะเลอื กทต่ี ําแหน่งใดกไ็ ดใ้ นชที จากนัน้ สรา้ งสตู ร =A1:A3 ลงไปแลว้ กดป่ มุCtrl+Shift+Enter พรอ้ มกนั จะพบวา่ Excel กระจายคา่ ในสตู รลงไปในเซลลแ์ ตล่ ะเซลลใ์ หเ้ ห็นจนครบ และตวั สตู รเปลยี่ นเป็ น {=A1:A3} โดยสงั เกตวา่ คราวนเ้ี ครอื่ งหมาย { ตวั แรกวางไว ้กอ่ นเครอ่ื งหมายเทา่ กบั แสดงวา่ เป็ นวงเล็บปีกกาทเ่ี กดิ จากการกดป่ มุ Ctrl+Shift+Enter(ไมใ่ ชว่ งเล็บปี กกาทใี่ ชก้ ํากบั ลําดบั ของคา่ ทตี่ อ้ งอยหู่ ลงั เครอื่ งหมายเทา่ กบั )ประโยชนข์ องการเลอื กจํานวนเซลลใ์ หค้ รบเหมาะกบั จํานวนและแนวของคา่ ทตี่ อ้ งการนี้ จะชว่ ยใหเ้ ราเห็นคา่ ทกุ คา่ กระจายตวั ลงไปในเซลลแ์ ตล่ ะเซลล์ ทําใหป้ ระหยดั เวลาไมต่ อ้ งคอยกดป่ มุF2 ตามดว้ ย F9 เพอ่ื แกะสตู รเป็ นครัง้ ๆไป อกี ทงั้ สตู รทส่ี รา้ งขน้ึ พรอ้ มกนั ทเี ดยี วหลายเซลล์ จะทําให ้ Excel เสยี เวลาในการคํานวณเพยี งครัง้ เดยี ววธิ แี ก้ error #VALUE! ดว้ ย Lotus 1-2-3เมอ่ื นําเซลลท์ ม่ี คี า่ เป็ นตวั เลขมาบวกดว้ ยเซลลท์ มี่ คี า่ เป็ นตวั อกั ษรโดยการสรา้ งสตู รบวกทน่ี ําเซลลม์ าบวกกนั เชน่ =A1+A2 จะพบวา่ ไดค้ ําตอบเป็ น #VALUE! นัน้ เราสามารถสง่ั ให ้ Excelคํานวณหาคําตอบเป็ นยอดรวมเฉพาะตวั เลขไดโ้ ดยใชส้ ง่ั ให ้ Excel คํานวณแบบ Lotus 1-2-3ใน Excel 2010 เรม่ิ จากสงั่ File > Options > Advanced > แลว้ เลอ่ื นจอไปดา้ นลา่ งสดุ จะพบLotus compatibility Settings for: ชอื่ ชที ทต่ี อ้ งการใหค้ ํานวณแบบ Lotus 1-2-3 โดยใหก้ าชอ่ ง Transition formula evaluation (Excel 2007 ใหค้ ลกิ ป่ มุ Office แทน File หรอื Excel2003 สงั่ Tools > Options > Transition > กาชอ่ ง Transition formula evaluation) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 88 of 147  คําสง่ั Transition formula evaluation นเี้ กดิ ขน้ึ ในสมยั ทคี่ นทงั้ โลกยงั นยิ มใช ้ Lotus 1-2-3 กนัดงั นัน้ เพอ่ื ชว่ ยทําใหค้ นเปลยี่ นใจมาใช ้ Excel ไดง้ า่ ยขน้ึ ทางไมโครซอฟทจ์ งึ สรา้ งคําสงั่ นขี้ น้ึเพอ่ื ใหพ้ วกเราสามารถนําแฟ้มทส่ี รา้ งจาก Lotus 1-2-3 มาเปิดใชใ้ น Excel ตอ่ ไดท้ นั ทโี ดยExcel จะเปลย่ี นระบบการคํานวณในชที ทเ่ี ลอื กไวใ้ หค้ ํานวณตามแบบท่ี Lotus 1-2-3 ใชน้ ่ันคอืใน Lotus 1-2-3 ถอื วา่ ตวั อกั ษรมคี า่ เทา่ กบั 0 (สว่ น Excel ถอื วา่ ตวั อกั ษรมคี า่ มากกวา่ เลขทงั้ปวง)นอกจากนัน้ หากกาชอ่ ง Transition formula entry จะชว่ ยทําใหส้ ตู รทส่ี รา้ งจาก Lotus 1-2-3release 2.2 ถกู เปลยี่ นเป็ นสตู รของ Excel ใหท้ นั ทเี มอ่ื เปิดแฟ้มทใ่ี ชส้ ตู รนัน้ ดว้ ย Excelสว่ นชอ่ ง Transition navigation keys จะเปลย่ี นพฤตกิ รรมของป่ มุ ลกู ศร ป่ มุ Tab หรอื ป่ มุ ใดๆที่ทําหนา้ ทเ่ี คลอื่ นยา้ ยตําแหน่งเซลลท์ เ่ี ลอื ก ใหท้ ําตามที่ Lotus 1-2-3 ใช ้ ซง่ึ ชว่ งแรกกวา่ ผมจะหนั มาใช ้ Excel อยา่ งเต็มตวั ตอ้ งอาศยั กาชอ่ งนอ้ี ยนู่ านเพราะมอื ยงั ไมค่ นุ ้ กบั ป่ มุ ทเ่ี คยเลอ่ื นไปเลอื่ นมาตามแบบทตี่ วั เองเคยชนิตวั อกั ษรมไิ ดม้ คี า่ เทา่ กบั 0กอ่ นจะจบบทความน้ี ทราบไหมวา่ ตวั อกั ษร a หรอื ตวั อกั ษรใดๆกต็ ามทบี่ นั ทกึ ลงไปในเซลล์มไิ ดถ้ อื วา่ มคี า่ เทา่ กบั 0 หรอกนะ ถา้ ไมเ่ ชอ่ื สมมตวิ า่ เซลล์ A1 มคี า่ เป็ น a ขอใหล้ องสรา้ งสตู ร=A1=0 ดกู ไ็ ดจ้ ะพบวา่ ไดค้ ําตอบเป็ น FALSE สว่ นสตู ร =A1>999999999 จะไดค้ ําตอบเป็ นTRUEExcel ถอื วา่ ตวั อกั ษรใดๆมคี า่ มากกวา่ เลขทงั้ ปวง อกี นัยหนงึ่ มคี า่ เป็ น Infinity หากสรา้ งสตู ร=Sum(A1,123) แลว้ ไดค้ ําตอบเทา่ กบั 123 เป็ นเพราะ Excel จะละเลยไมน่ ําตวั อกั ษรมาคํานวณในสตู ร Sum นแ่ี หละเป็ นเรอ่ื งทห่ี ลายๆคนชอบเขา้ ใจผดิ กนั วา่ ตวั อกั ษรมคี า่ เป็ น 0ถา้ สรา้ งสตู ร =A1+123 จะไดค้ ําตอบเป็ น #VALUE! เพราะ Excel จะไมย่ อมรับการนําตวั อกั ษรมาบวกกบั ตวั เลข แตเ่ ราสามารถเปลยี่ นพฤตกิ รรมของ Excel ใหค้ ดิ แบบ Lotus 1-2-3 ไดต้ ามคําอธบิ ายขา้ งตน้ถา้ ถามวา่ ระหวา่ งสตู รบวกกบั สตู ร Sum เราจะเลอื กใชก้ ารบวกเลขวธิ ใี ดกบั งานสําคญัคําตอบกค็ อื ตอ้ งเลอื กใชส้ ตู รบวก เพราะหากมกี ารบนั ทกึ ตวั เลขผดิ กลายเป็ นบนั ทกึ ตวั อกั ษรลงไปแทน จะพบวา่ Excel ไมย่ อมคํานวณให ้ โดยไดค้ ําตอบเป็ น #VALUE! ซง่ึ เทา่ กบั ชว่ ยเตอื นเราใหท้ ราบวา่ เกดิ ขอ้ ผดิ พลาดขนึ้ มาในระหวา่ งการบนั ทกึ ขอ้ มลู น่ันเอง สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 89 of 147   หลากหลายแนวทางการใชส้ ตู ร IFในบรรดาสตู รสําเร็จรปู ของ Excel นัน้ สตู รทใ่ี ชม้ ากทส่ี ดุ และมปี ระโยชนม์ ากทส่ี ดุ รองจากสตู รSUM เห็นจะหนไี มพ่ น้ สตู ร IF ซง่ึ แปลวา่ \"ถา้ \" นแี้ หละ โดยเฉพาะคนทมี่ ตี ําแหน่งขน้ึ ชอ่ื วา่ เป็ นหวั หนา้ ถา้ ใชส้ ตู ร IF ไมเ่ ป็ น ก็ไมส่ มควรเป็ นหวั หนา้ หรอกนะ เปลยี่ นตําแหน่งใหไ้ ดช้ อ่ื ตรงขา้ มกบั คําวา่ หวั กบั หนา้ ใหก้ ลายเป็ นตําแหน่งเทา้ หลงั แทนจะเหมาะสมกวา่ถา้ นําเงอ่ื นไขขา้ งตน้ เกย่ี วกบั หวั หนา้ มาเขยี นดว้ ยสตู ร IF จะไดส้ ตู รตามนี้ =IF ( หวั หนา้ ใชส้ ตู ร IF เป็ นหรอื ไม่ , ถา้ ใชเ้ ป็ น ใหเ้ ป็ นหวั หนา้ ตอ่ ไป , ถา้ ใชไ้ ม่ เป็ น ใหเ้ ป็ นเทา้ หลงั แทน )หรอื ถา้ คดิ เขยี นสตู ร IF ตามหลกั กฎแหง่ กรรมของการทําดตี อ้ งไดด้ ี ทําชว่ั ไดช้ วั่ จะไดส้ ตู รตามนี้ =IF ( คณุ ทาํ ดใี ชไ่ หม , ถา้ ทาํ ดี ตอ้ งไดด้ ี , ถา้ ไมท่ าํ ดี ตอ้ งไดร้ บั ผลอน่ื ๆ )สงั เกตวา่ ในสตู ร IF ของการตรวจสอบวา่ ทําดหี รอื ไมน่ ัน้ ถา้ ไมไ่ ดท้ ําดี ไมใ่ ชว่ า่ จะไดช้ วั่ เพราะเงอื่ นไขทใี่ ชเ้ ป็ นเพยี งตรวจสอบวา่ ทําดอี ยา่ งเดยี วเทา่ นัน้ คนทไ่ี มไ่ ดท้ ําดี ไมใ่ ชว่ า่ ตอ้ งทําชวั่ ก็ได ้ อาจอยเู่ ฉยๆไมไ่ ดท้ ําดไี มไ่ ดท้ ําชว่ั ดงั นัน้ กรณีทไ่ี มไ่ ดท้ ําดี จงึ ตอ้ งไดร้ ับผลอยา่ งอน่ื ซงึ่ ไมใ่ ช่ผลจากการทําดีถา้ คณุ เขา้ ใจวธิ คี ดิ เงอื่ นไขในใจของตวั คณุ เอง กจ็ ะพบวา่ บรษิ ัทไมโครซอฟทไ์ ดส้ รา้ ง Excelขนึ้ มาโดยใชส้ ตู ร IF ทม่ี โี ครงสรา้ งของสตู รตามหลกั การเดยี วกนั กบั ทค่ี ณุ คดิ ไดน้ ่ันเองโครงสรา้ งสตู ร IF =IF(การตรวจสอบเงอื่ นไข, ผลลพั ธก์ รณีทตี่ รวจสอบเงอ่ื นไขวา่ ใช,่ ผลลพั ธ์ กรณีทต่ี รวจสอบเงอ่ื นไขวา่ ไมใ่ ช)่ในสว่ นของการตรวจสอบเงอ่ื นไข ใหใ้ สส่ มการทตี่ อ้ งการลงไป โดยใชเ้ ครอื่ งหมาย =, >, <,>=, <=, หรอื ไมเ่ ทา่ กบั <> หรอื ในสว่ นของการตรวจสอบเงอื่ นไขน้ี อาจใชส้ ตู รอน่ื ซง่ึ คนื คา่เป็ น TRUE หรอื FALSE อยา่ งใดอยา่ งหนงึ่ เชน่ AND, OR, TRUE, FALSE, ISNUMBER เป็ นตน้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 90 of 147  ตวั อยา่ งการใช้ IF แบบงา่ ยๆ =IF(SaleChoice=\"n\", 1000, 2000)สตู รนจี้ ะคนื คา่ เป็ นตวั เลข 1000 ตอ่ เมอ่ื เซลลท์ ตี่ งั้ ชอื่ วา่ SaleChoice มคี า่ เป็ นตวั อกั ษร nเทา่ นัน้ แตถ่ า้ SaleChoice มคี า่ อน่ื จะคนื คา่ เป็ นตวั เลข 2000 แทนเนอื่ งจากตวั อกั ษร n เป็ นตวั อกั ษรที่ Excel ไมร่ จู ้ ัก จงึ จําเป็ นตอ้ งใส่ \"n\" ไวร้ ะหวา่ งเครอื่ งหมายคําพดู สว่ นคําวา่ IF และ SaleChoice นัน้ แมว้ า่ เป็ นตวั อกั ษรแตไ่ มต่ อ้ งใสร่ ะหวา่ งเครอื่ งหมายคําพดู เพราะเป็ นตวั อกั ษรที่ Excel รจู ้ ักแลว้ วา่ คําวา่ IF คอื สตู รชอ่ื IF และคําวา่ SaleChoice เป็ นRange Name ทต่ี งั้ ชอื่ ไว ้ สว่ นตวั เลข 1000 และ 2000 เป็ นตวั เลขท่ี Excel รับทราบอยแู่ ลว้ วา่เป็ นตวั เลขจงึ ไมต่ อ้ งใสร่ ะหวา่ งเครอ่ื งหมายคําพดู แตอ่ ยา่ งใด =IF(MyMargin>0, MyMargin*0.3, 0)สตู รนใ้ี ชก้ บั การคํานวณหายอดภาษี ถา้ MyMargin หรอื เซลลก์ ําไรขนั้ ตน้ มคี า่ มากกวา่ 0 ยอ่ มแสดงวา่ มกี ําไรเกดิ ขน้ึ จงึ นํายอดกําไรขนั้ ตน้ ไปคณู ดว้ ยอตั ราภาษี 30% แตถ่ า้ ไมม่ กี ําไรกไ็ ม่ตอ้ งจา่ ยภาษีแนวทางการใชส้ ตู ร IF 1.ใชเ้ ปลยี่ นเสน้ ทางการรับคา่ 2.ใชใ้ นการตดั สนิ ใจ 3.ใชใ้ นการเปลย่ี นคา่ หรอื แสดงคา่ 4.ใชเ้ ปลย่ี นเสน้ ทางการสง่ คา่ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 91 of 147  การใชส้ ตู ร IF ในการเปลย่ี นเสน้ ทางการรบั คา่การสรา้ งสตู รลงิ คเ์ พอ่ื เชอ่ื มโยงคา่ โดยทว่ั ไปเป็ นการเชอื่ มโยงกนั แบบ 1 ตอ่ 1 เชน่ เมอ่ื ตอ้ งการทําใหเ้ ซลล์ A2 มคี า่ เทา่ กบั เซลล์ A1 กจ็ ะสรา้ งสตู ร =A1 ลงไปในเซลล์ A2 ทําใหเ้ ซลล์ A2แสดงคา่ เปลย่ี นแปลงตามคา่ ในเซลล์ A1 ซงึ่ การลงิ คแ์ บบนเ้ี ซลล์ A1 เรยี กวา่ เป็ นเซลลต์ น้ ทางและเซลล์ A2 เรยี กวา่ เป็ นเซลลป์ ลายทางแตถ่ า้ เซลลต์ น้ ทางมมี ากกวา่ 1 เซลล์ เชน่ กําหนดใหเ้ ซลล์ C2 และ C3 สามารถเป็ นเซลลต์ น้ทางไดท้ งั้ คู่ โดยกําหนดใหเ้ ซลล์ E3 เป็ นเซลลป์ ลายทางเพยี งเซลลเ์ ดยี ว โดยเลอื กทจ่ี ะรับคา่จากเซลล์ C2 หรอื C3 ก็ไดแ้ ลว้ แตเ่ งอื่ นไขทเี่ รากําหนดวา่ จะใหเ้ ลอื กรับคา่ มาจากเซลลใ์ ดจากภาพกําหนดใหเ้ ซลล์ E2 เป็ นเซลลส์ ําหรับใสค่ า่ เงอ่ื นไขลงไปวา่ จะเป็ นตวั อกั ษร A หรอื B(หรอื คา่ อน่ื ใดกไ็ ด)้ สว่ นเซลล์ E3 ซงึ่ เป็ นเซลลป์ ลายทาง ใหส้ รา้ งสตู รตอ่ ไปน้ี =IF( E2=\"A\", C2, C3)ดงั นัน้ เมอื่ เซลล์ E2 มคี า่ เป็ น A จงึ ทําใหเ้ ซลล์ E3 รับคา่ 100 มาจากเซลล์ C2 แตถ่ า้ เซลล์ E2ไมไ่ ดม้ คี า่ เป็ น A กจ็ ะทําใหเ้ ซลล์ E3 เปลย่ี นเสน้ ทางการรับคา่ 200 มาจากเซลล์ C3 แทนแตถ่ า้ กําหนดใหม้ จี ํานวนเซลลต์ น้ ทางมากมายหลายเซลล์ เชน่ ตามรปู ตอ่ ไปนใ้ี ชเ้ ซลลต์ น้ ทางถงึ 14 เซลล์สตู รลงิ คเ์ พอื่ เลอื กรับคา่ ในเซลล์ F9 จะกลายเป็ นสตู รซบั ซอ้ นมากขนึ้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 92 of 147   =IF(E9=\"A\",C2,IF(E9=\"B\",C5,IF(E9=\"C\",C8,IF(E9=\"D\",C11,IF(E9=\"E\",C14,IF( E9=\"F\",C17,IF(E9=\"G\",F2,IF(E9=\"H\",F17,IF(E9=\"I\",I2,IF(E9=\"J\",I5,IF(E9=\" K\",I8,IF(E9=\"L\",I11,IF(E9=\"M\",I14,I17)))))))))))))ซง่ึ เซลล์ E9 มคี า่ เป็ น K จงึ ทําใหส้ ตู ร IF ในเซลล์ F9 เลอื กรับคา่ มาจากเซลล์ I8แตถ่ า้ โครงสรา้ งตารางไมไ่ ดจ้ ดั ใหเ้ ป็ นระเบยี บ แมส้ ตู ร IF จะยงั คงทํางานไดต้ ามเดมิ ก็ตาม แต่จะพบวา่ การแกะสตู รเพอื่ ตรวจสอบความถกู ตอ้ งทําไดย้ ากขนึ้ มากสตู รในเซลล์ F9 =IF(E9=\"A\",C2,IF(E9=\"B\",I3,IF(E9=\"C\",J11,IF(E9=\"D\",F2,IF(E9=\"E\",I9,IF(E9 =\"F\",J16,IF(E9=\"G\",H14,IF(E9=\"H\",E14,IF(E9=\"I\",G17,IF(E9=\"J\",H7,IF(E9= \"K\",C8,IF(E9=\"L\",D6,IF(E9=\"M\",D12,C17)))))))))))))แตช่ ว่ ยตรวจสอบวา่ แมว้ า่ E9 มคี า่ เทา่ กบั K ทําใหเ้ ซลล์ F9 รับคา่ 1100 มาจากเซลล์ C8 ได ้ถกู ตอ้ งตามตอ้ งการกต็ าม แตค่ า่ 1100 ทไี่ ดร้ ับมานัน้ ไมใ่ ชต่ วั เลขของคา่ K แตเ่ ป็ นของคา่ Xมใิ ชห่ รอืตงั้ แต่ Excel 2007 เป็ นตน้ มา ในวงเล็บของแตล่ ะสตู ร เราสามารถซอ้ นสตู รเขา้ ไปไดถ้ งึ 64 สตู ร(Excel 2003 และรนุ่ กอ่ นนัน้ จะซอ้ นไดเ้ พยี ง 7 สตู ร) ซงึ่ จากตวั อยา่ งขา้ งตน้ น่าจะทําใหเ้ ราได ้เรยี นรวู ้ า่ ถงึ แมว้ า่ เราสามารถใชส้ ตู ร IF ซอ้ น IF เพอื่ หาคา่ ทกี่ ระจายกนั อยตู่ า่ งทตี่ า่ งชที ตา่ งแฟ้มไดก้ ต็ าม แตก่ ารกระจายกนั ของคา่ นเ่ี อง หากกระจายอยา่ งขาดระเบยี บ ยอ่ มเพม่ิ ความเสยี่ งทจ่ี ะหาคําตอบผดิ พลาดไดโ้ ดยไมร่ ตู ้ ัว และเมอื่ จะตรวจสอบแกไ้ ขก็จะทําใหท้ ําไดย้ าก ดงั นัน้ โปรดจําไวว้ า่ ขอใหเ้ ลอื กใชส้ ตู ร IF กบั ปัญหางา่ ยๆ แตเ่ มอื่ ปัญหาซบั ซอ้ นมากขน้ึ เราควรปรับเปลย่ี นโครงสรา้ งตารางหรอื เลอื กใชส้ ตู รอน่ื ซงึ่ ทํางานไดเ้ ชน่ เดยี วกนั กบั สตู ร IF จะเหมาะสมกวา่ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 93 of 147  การใชส้ ตู ร IF ในการตดั สนิ ใจโครงสรา้ งสตู ร IF แบบงา่ ยๆ =IF(เงอื่ นไข,ผลกรณีใช,่ ผลกรณีไมใ่ ช)่ มพี น้ื ฐานของตวั สตู รทําหนา้ ทตี่ ดั สนิ ใจตามเงอื่ นไขทเ่ี รากําหนดลงไปในสตู รอยแู่ ลว้ ไมว่ า่ จะใชส้ ตู ร IF ในการเปลยี่ นเสน้ ทางการรับคา่ หรอื ใชใ้ นการปรับเปลยี่ นคา่ ก็ตาม ยอ่ มถอื วา่ เป็ นการใชส้ ตู ร IF ในการตดั สนิ ใจนั่นเอง เพยี งแตห่ วั ขอ้ นจ้ี ะแยกแยะประเภทของการตดั สนิ ใจใหเ้ ห็นชดั ขน้ึการใชส้ ตู ร IF ในการตดั สนิ ใจมี 2 แบบ ขนึ้ กบั วา่ เงอ่ื นไขถดั ไปกําหนดวา่ ตอ้ งผา่ นเงอื่ นไขกอ่ นหนา้ อยา่ งไร 1. การใช ้ IF แบบกําหนดเงอ่ื นไขแตกตอ่ กรณีทไี่ มใ่ ช่ 2. การใช ้ IF แบบกําหนดเงอ่ื นไขแตกตอ่ กรณีทใี่ ช่การใช้ IF แบบกาํ หนดเงอ่ื นไขแตกตอ่ กรณีทไ่ี มใ่ ช่เป็ นเงอื่ นไขทพ่ี บเห็นกนั ทว่ั ไป เชน่ สตู ร IF ในตัวอยา่ งทใี่ ชส้ ําหรับเปลยี่ นเสน้ ทางในการรับคา่น่ันเอง เมอื่ เงอ่ื นไขการรับคา่ แรกผา่ นไปแลว้ ไดค้ ําตอบแรกตามเงอ่ื นไขแรกเมอ่ื ตดั สนิ วา่ ใชไ่ ปแลว้ กจ็ ะไดค้ ําตอบกรณีทใี่ ชเ่ รยี บรอ้ ย แตห่ ากเงอ่ื นไขแรกไมผ่ า่ น ก็จะตอ้ งพจิ ารณาเงอ่ื นไขในลําดบั ถดั ไป ซงึ่ แสดงเป็ นโครงสรา้ งสตู ร IF จากแบบงา่ ยๆกลายเป็ นแบบซอ้ นกนั ดงั นี้สําหรับการตดั สนิ ใจในเงอื่ นไขแรก =IF(เงอื่ นไขที่ 1, ผลกรณีใชข่ องเงอื่ นไขที่ 1, ผลกรณีไมใ่ ชข่ องเงอื่ นไขท่ี 1)ถา้ เงอ่ื นไขที่ 1 ยงั ไมผ่ า่ น ใหแ้ ตกผลกรณีไมใ่ ชข่ องเงอื่ นไขท่ี 1 ตอ่ เป็ น IF สตู รทส่ี องเพอ่ื คดิตามเงอ่ื นไขที่ 2 =IF(เงอื่ นไขที่ 2, ผลกรณีใชข่ องเงอ่ื นไขที่ 2, ผลกรณีไมใ่ ชข่ องเงอ่ื นไขท่ี 2)เมอื่ นําสตู รทงั้ สองเงอ่ื นไขมาซอ้ นเป็ นสตู รในเซลลเ์ ดยี ว จะไดส้ ตู รตามน้ี =IF(เงอื่ นไขที่ 1, ผลกรณีใชข่ องเงอ่ื นไขท่ี 1, IF(เงอ่ื นไขที่ 2, ผลกรณีใชข่ อง เงอ่ื นไขท่ี 2, ผลกรณีไมใ่ ชข่ องเงอ่ื นไขท่ี 2) ) สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 94 of 147  ตวั อยา่ งการใช้ IF แบบกาํ หนดเงอ่ื นไขแตกตอ่ กรณีทไี่ มใ่ ชใ่ นการตดั เกรดกําหนดใหเ้ ซลล์ B3:C5 เป็ นตารางคะแนน-เกรด  ถา้ สอบไดต้ งั้ แต่ 90 คะแนนขน้ึ ไปใหไ้ ดเ้ กรด A  ถา้ สอบไดต้ งั้ แต่ 70 คะแนนขนึ้ ไปใหไ้ ดเ้ กรด B  ถา้ สอบไดต้ งั้ แต่ 0 คะแนนขน้ึ ไปใหไ้ ดเ้ กรด Cสมมตวิ า่ เซลล์ E3 มคี า่ เป็ น 75 เป็ นคะแนนทสี่ อบได ้ เซลล์ F3 หาคําตอบเป็ นเกรด B โดยใช ้สตู ร IF ซอ้ นกนั ดงั นี้ =IF( E3>=B3, C3, IF(E3>=B4,C4,C5) )ซง่ึ แปลสตู รออกมาตามเงอื่ นไขทก่ี ําหนดไดเ้ ป็ น =IF( คะแนนทสี่ อบได้ >=90, \"A\", IF(คะแนนทส่ี อบได้ >=70,\"B\",\"C\") )ขอ้ สงั เกต 1. การเขยี นสตู ร IF ซอ้ น IF มหี ลกั การสําคญั คอื ตอ้ งจดั ลาํ ดบั ของเงอ่ื นไขทเี่ ปิ ด โอกาสให้ IF ทกุ ตวั ในสตู รมโี อกาสไดท้ าํ งาน ดงั นัน้ จงึ ตอ้ งใชเ้ งอื่ นไข คะแนนที่ สอบได ้ >=90 ขนึ้ กอ่ นเป็ นเงอื่ นไขแรก หากเงอ่ื นไขนไี้ มผ่ า่ นจงึ ปลอ่ ยใหเ้ งอ่ื นไขถดั ไป ทํางาน (แตถ่ า้ สรา้ งผดิ โดยใชเ้ งอื่ นไข >=70 ขนึ้ กอ่ น จะปิดโอกาสของเงอ่ื นไขเกรด A เพราะ >=70 ยอ่ มหมายถงึ >=90 อยแู่ ลว้ ) 2. แมต้ อ้ งการเกรด A, B, C แตเ่ ราใช ้ IF ซอ้ นกนั เพยี ง 2 IF เทา่ นัน้ เพราะถา้ ไมใ่ ชท่ งั้ สอง เงอื่ นไข กต็ อ้ งเป็ นเกรด C นั่นเอง สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 95 of 147  การใช้ IF แบบกาํ หนดเงอ่ื นไขแตกตอ่ กรณีทใี่ ช่เป็ นเงอ่ื นไขทก่ี ําหนดซอ้ นเขา้ ไปเพอื่ ตดั สนิ ใจในทกุ เงอื่ นไขพรอ้ มกนั ไป เกดิ เป็ นโครงสรา้ งสตู รตามน้ี =IF(เงอ่ื นไขท่ี 1, IF(เงอ่ื นไขที่ 2, ผลกรณีใชข่ องเงอื่ นไขท่ี 1 และ 2, ผลกรณี ไมใ่ ชข่ องเงอื่ นไขท่ี 2), ผลกรณีไมใ่ ชข่ องเงอ่ื นไขที่ 1)หากผลกรณีทใี่ ชข่ องทกุ เงอื่ นไขเป็ นคา่ เดยี วกนั สว่ นผลกรณีทไ่ี มใ่ ชข่ องเงอื่ นไขที่ 1 และผลกรณีทไ่ี มใ่ ชข่ องเงอื่ นไขท่ี 2 เป็ นคา่ เดยี วกนั แทนทจี่ ะใชส้ ตู ร IF ซอ้ นกนั หลายสตู ร ใหใ้ ชส้ ตู รAnd หรอื Or มาชว่ ยในสตู ร IF เพยี งสตู รเดยี วไดด้ งั น้ี =IF( And( เงอ่ื นไขที่ 1,เงอื่ นไขท่ี 2), ผลกรณีใช,่ ผลกรณีไมใ่ ช)่ =IF( OR( เงอ่ื นไขที่ 1,เงอ่ื นไขท่ี 2), ผลกรณีใช,่ ผลกรณีไมใ่ ช)่หมายเหตุ 1. สตู ร And จะคนื คา่ เป็ นจรงิ ตอ่ เมอื่ ทกุ เงอื่ นไขเป็ นจรงิ พรอ้ มกนั หากเงอ่ื นไขใดเงอื่ นไข หนง่ึ เป็ นเท็จ จะคนื คา่ เป็ นเท็จ (จําวา่ False เป็ นลักษณะเดน่ ) 2. สตู ร OR จะคนื คา่ เป็ นเท็จตอ่ เมอ่ื ทกุ เงอ่ื นไขเป็ นเท็จพรอ้ มกนั หากเงอ่ื นไขใดเงอื่ นไข หนง่ึ เป็ นจรงิ จะคนื คา่ เป็ นจรงิ (จําวา่ True เป็ นลักษณะเดน่ )ตวั อยา่ งการใช้ IF แบบกาํ หนดเงอื่ นไขแตกตอ่ กรณีทใ่ี ชใ่ นการป้ องกนั สตู รลบัสมมตวิ า่ เราตอ้ งการบวกเลข 1 กบั เลข 2 ในเซลล์ B2 และ C2 เขา้ ดว้ ยกนั แตก่ อ่ นจะยอมให ้สตู ร B2+C2 ทํางานไดต้ ามปกติ ตอ้ งผา่ นการกรอกรหสั ลงไปในเซลล์ B4 และ B5 เป็ นเลข123 และ 999 ตามลําดบั ใหถ้ กู ตอ้ งเรยี บรอ้ ยกอ่ นถา้ สรา้ งสตู ร IF ซอ้ น IF จะไดส้ ตู รตามเซลล์ D2 =IF( B4=123, IF(B5=999, B2+C2, 0), 0)หรอื ใชส้ ตู ร And มาใชง้ านรว่ มดว้ ย กลายเป็ นสตู ร IF เพยี งสตู รเดยี วดงั น้ี =IF( AND(B4=123,B5=999), B2+C2, 0) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 96 of 147  ตวั อยา่ งการใช้ IF แบบกาํ หนดเงอื่ นไขแตกตอ่ กรณีทใี่ ชใ่ นการรบั เงนิ ในชว่ งวนั ที่กาํ หนดหากตอ้ งการรับเงนิ 100 บาท ระหวา่ งวนั ท่ี 2-5 กําหนดใหใ้ ช ้ Range Name ในตารางตอ่ ไปนี้  B3:B8 ชอ่ื From  C3:C8 ชอ่ื To  D3:D8 ชอื่ Amount  E2:K2 ชอื่ Dateเซลล์ E3 เป็ นสตู รรับเงนิ ในชว่ งวนั ทตี่ อ้ งการตามน้ี =IF( Date>=From, IF(Date<=To,Amount,0), 0) หรอื =IF( AND(Date>=From,Date<=To), Amount, 0)ขอ้ สงั เกตตวั อยา่ งนใ้ี ชส้ ตู ร And มาชว่ ยเพราะตอ้ งการใหค้ นื คา่ เป็ น True เฉพาะในชว่ งวนั ท่ี 2-5 เทา่ นัน้(ดเู ซลล์ E8:K8) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 97 of 147  การใชส้ ตู ร IF ในการเปลย่ี นคา่ หรอื แสดงคา่ในกรณีทผี่ ลลพั ธจ์ ากการคํานวณหรอื คา่ ทไ่ี ดจ้ ากการลงิ คต์ อ่ มาจากเซลลอ์ น่ื อาจไมใ่ ชค่ า่ ที่ตอ้ งการแสดงใหผ้ ใู ้ ชเ้ ห็นเสมอไป เราสามารถนําสตู ร IF มาชว่ ยเปลย่ี นคา่ เดมิ นัน้ ใหก้ ลายเป็ นคา่ ใหมห่ รอื เปลยี่ นจากตัวเลขหรอื คําเตอื น Error ใหก้ ลายเป็ นตวั อกั ษรทสี่ อื่ ความหมายได ้ชดั เจนกวา่ แทนโปรดดตู วั อยา่ งจาก Row 2 ขา้ งตน้ ในการนําเลขมาหารกนั นัน้ หากเป็ นตวั เลขทน่ี ํามาหารกนัได ้ กย็ อ่ มคนื คา่ ทต่ี อ้ งการออกมาไดท้ นั ที เชน่ เมอื่ นําเลข 100 จากเซลล์ B2 มาหารดว้ ยเลข 5จากเซลล์ C2 โดยสรา้ งสตู ร = B2/C2 ลงไปในเซลล์ D2 จะไดค้ ําตอบเป็ นตวั เลข 20ใน Row 5 ถา้ เปลย่ี นเลข 5 ทเ่ี ป็ นตวั หาร ใหเ้ ป็ นเลข 0 แทน จะพบวา่ สตู ร =B5/C5 ไมส่ ามารถคํานวณได ้ โดยแสดง Error #DIV/0! ออกมาแทน (#DIV/0! ยอ่ มาจากคําวา่ Error fromDivided by Zero หรอื Error เนอ่ื งจากการถกู หารดว้ ยเลข 0)เราสามารถเปลย่ี นการแสดง Error ใหเ้ ป็ นเลข 0 แทน โดยใชส้ ตู รตามตวั อยา่ งใน Row 8 ดงั น้ี =IF(C8=0, 0, B8/C8)หรอื อา่ นสตู รเป็ นคําแปลวา่ ถา้ เซลลต์ วั หารมคี า่ เทา่ กบั 0 ใหผ้ ลลพั ธแ์ สดงเลข 0 แทนการแสดงError แตถ่ า้ ตวั หารไมใ่ ชเ่ ลข 0 ก็ใหค้ ํานวณหารกนั ตอ่ ไปตามปกติถา้ ตวั หารไมใ่ ชเ่ ลข 0 แตก่ ลบั กลายเป็ นตวั อกั ษร o จะพบวา่ สตู ร IF ขา้ งตน้ นค้ี อื คา่ ออกมาเป็ น#VALUE! แทน เพราะ Excel ไมย่ อมรับการนําตวั อกั ษรไปหารตวั เลขใน Row 11 กรณีทตี่ วั หารเป็ นตวั อกั ษร o (หรอื ตวั อกั ษรอน่ื ใด) เราสามารถป้องกนั การเกดิError จากการหารโดยใชส้ ตู ร IsError มาชว่ ยตรวจสอบการคํานวณวา่ จะเกดิ Error หรอื ไม่ =IF( ISERROR(B11/C11), 0, B11/C11)หรอื ตงั้ แต่ Excel 2007 เป็ นตน้ มามี IfError เป็ นสตู รใหมท่ ส่ี นั้ ลง =IFERROR(B11/C11,0) สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 98 of 147  หมายเหตุ 1. กอ่ นทจ่ี ะใชส้ ตู ร IsError ควรหาทางตรวจสอบสตู รคํานวณทส่ี รา้ งกอ่ นวา่ สามารถ คํานวณไดค้ ําตอบทต่ี อ้ งการหรอื ไม่ เพราะหากใชส้ ตู ร IsError เขา้ มาชว่ ยรว่ มกบั สตู ร IF แลว้ เราจะไมเ่ ห็น Error เกดิ ขนึ้ อกี เลย 2. ควรเลอื กนําเฉพาะบางสว่ นของการคํานวณจากสตู รทจ่ี ะนํามาตรวจสอบดว้ ยสตู ร IsError โดยไมจ่ ําเป็ นตอ้ งนําสตู รยาวๆทงั้ หมดมาใสล่ งไปในวงเล็บของสตู ร IsError 3. ในกรณีทเี่ กดิ Error ขนึ้ ในกรณีทผ่ี ลลพั ธเ์ ดมิ เป็ น Text ควรเปลยี่ น Error เป็ นคําเตอื น หรอื กรณีผลลพั ธเ์ ดมิ เป็ นเลข ควรเปลยี่ น Error เป็ นเลข 0 แทน จากนัน้ ใหใ้ ช ้ Format 0;-0; ซอ่ นเลข 0 ใหด้ เู หมอื นเป็ นชอ่ งวา่ ง 4. ในการเปลยี่ นการแสดง Error ใหแ้ สดงเป็ นชอ่ งวา่ ง พยายามหลกี เลยี่ งการใช ้ Null Text หรอื \"\" (แทนการใชเ้ ลข 0) เพราะ \"\" มสี ภาพเป็ น Text ซงึ่ หากนําคา่ นไี้ ปบวกลบคณู หารตอ่ จะเกดิ Error ตอ่ ไปอกี กลายเป็ นภาระใหเ้ ราตอ้ งสรา้ งสตู ร =IF(Cell=\"\", \"\", Cell) ตอ่ ไปอกี ซง่ึ ทําใหแ้ ฟ้มคํานวณชา้ ลงและมขี นาดใหญข่ น้ึ โดยไมจ่ ําเป็ น 5. ในกรณีทตี่ อ้ งการแสดง #N/A ใหใ้ ชส้ ตู ร NA() 6. นอกเหนอื จากการใชส้ ตู ร IF ชว่ ยในการเปลยี่ น Error ใหเ้ ป็ นคา่ อน่ื แลว้ ในหนา้ กระดาษ ทถ่ี กู พมิ พ์ สามารถใชค้ ําสงั่ Page Setup > Sheet > Print > Cells error as เพอื่ เปลยี่ นเซลลท์ มี่ คี า่ เป็ น Error ใหแ้ สดงเป็ นชอ่ งวา่ ง, --, #N/A แทนไดอ้ กี ดว้ ยสตู รกลมุ่ Is ทใี่ ชช้ ว่ ยในการตรวจสอบ  IsNA ตรวจสอบ Error #N/A \"Not Available\" ซงึ่ เกดิ จากสตู รกลมุ่ Lookup ในกรณีที่ หาคา่ ทตี่ อ้ งการไมพ่ บ  IsError ตรวจสอบ Error ทกุ ประเภท  IsErr ตรวจสอบ Error เกอื บทกุ ประเภท เวน้ NA  IsBlank ตรวจสอบเซลลว์ า่ ง  IsText ตรวจสอบเซลลม์ คี า่ เป็ น Text หรอื คา่ ทจ่ี ะชดิ ซา้ ยใหเ้ องเมอ่ื พมิ พล์ งไป เชน่ '123 ถอื วา่ เป็ น Text  IsNumber ตรวจสอบเซลลม์ คี า่ เป็ น Number หรอื คา่ ทจี่ ะชดิ ขวาใหเ้ องเมอ่ื พมิ พล์ งไป เชน่ 10/4/2010 จะชดิ ขวาเพราะถอื วา่ เป็ น Number สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 99 of 147  การใชส้ ตู ร IF ในการเปลยี่ นเสน้ ทางการสง่ คา่ตามปกตสิ ตู รทส่ี รา้ งลงไปในเซลลจ์ ะทําหนา้ ทค่ี ํานวณหรอื รับคา่ ทล่ี งิ คม์ าจากเซลลต์ น้ ทาง สว่ นการสง่ คา่ จากเซลลต์ น้ ทางไปยงั เซลลป์ ลายทางโดยไมต่ อ้ งสรา้ งสตู รลงไปในเซลลน์ ัน้ ไม่สามารถทําไดโ้ ดยวธิ ปี กตทิ ว่ั ไป แตต่ อ้ งเขยี นรหสั VBA เขา้ มาชว่ ยตวั อยา่ ง กําหนดใหส้ ง่ คา่ จากเซลล์ Source ไปยงั เซลล์ Target1 หรอื Target2 ตามเงอ่ื นไขตอ่ ไปน้ี  หาก Source มคี า่ นอ้ ยกวา่ 0 เชน่ มคี า่ เป็ นเลข -111 ใหส้ ง่ คา่ -111 ไปยงั เซลล์ Target1  หาก Source มคี า่ มากกวา่ หรอื เทา่ กบั 0 เชน่ มคี า่ เป็ นเลข 222 ใหส้ ง่ คา่ 222 ไปยงั เซลล์ Target2เรม่ิ ตน้ จากใหต้ งั้ ชอ่ื Source, Target1, Target2 ใหก้ บั เซลล์ C2, C5 ,C6 ตามลําดบั และตงั้ ชอื่Target เป็ น Formula Name มคี า่ เป็ นสตู ร =IF( Source<0, Target1, Target2)จากนัน้ ใหส้ รา้ งชดุ คําสงั่ นลี้ งไปใน Visual Basic Editor Sub SendData() MyVar = [Source] [Target] = MyVar End Sub สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 100 of 147   1. ชดุ คําสง่ั นจ้ี ะรับคา่ ทเี่ กบ็ ไวใ้ นเซลลท์ ตี่ งั้ ชอ่ื ไวว้ า่ Source ไปเก็บไวท้ ต่ี วั แปรทม่ี ชี อ่ื วา่ MyVar 2. จากนัน้ คา่ ทเ่ี ก็บไวใ้ น MyVar จะถกู สง่ ตอ่ ไปยงั Formula Name ทต่ี งั้ ชอ่ื ไวว้ า่ Target 3. เนอ่ื งจาก Target เป็ นสตู ร =IF( Source<0, Target1, Target2) จงึ ทําหนา้ ท่ี ตรวจสอบคา่ ทร่ี ับมาจาก Source กอ่ นแลว้ จงึ เลอื กสง่ คา่ ไปยงั เซลลท์ ต่ี งั้ ชอื่ วา่ Target1 หรอื Target2 ตามเงอ่ื นไขทก่ี ําหนดไวข้ า้ งตน้ ตอ่ ไป สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com