ExcelExpertTip2010OK

สดุ ยอดเคล็ดลบั และลดั ของ Excel โดย สมเกยี รติ ฟ้ งุ เกยี รติ 7/1 รามคําแหง ซอย 35 หวั หมาก บางกะปิ กทม 10240 โทร 02 718 9331, โทร/แฟกซ์ 02 318 7021 www.ExcelExpertTraining.com www.XLSiam.com [email protected]

Page 2 of 147  6  7 9 สารบญั 9 10 1. คาํ นํา 11 2. วธิ ปี รบั Excel ใหพ้ รอ้ มตอ่ การใชง้ านอยา่ งมอื อาชพี 13 15 3. การกาํ หนดตาํ แหนง่ อา้ งองิ และขอบเขตพนื้ ทต่ี าราง Excel 17 4. ตงั้ หลกั กนั กอ่ นวา่ จะใช้ Excel อยา่ งคอมพวิ เตอร์ 18 5. สตู รฉลาด (Smart Formula) 19 20 ตวั อยา่ ง Smart Formula แบบใชต้ ําแหน่งอา้ งองิ เซลลเ์ ดยี ว 21 ตวั อยา่ ง Smart Formula แบบใชต้ ําแหน่งอา้ งองิ ตามขอบเขตพน้ื ทห่ี วั ตาราง ตวั อยา่ ง Smart Formula แบบใชต้ ําแหน่งอา้ งองิ แบบ Array ตามขอบเขตพน้ื ทหี่ วั ตาราง 22 24 6. วธิ ปี รบั ขนาดขอบเขตพนื้ ทตี่ าราง 25 26 ขอ้ ควรระวงั ในการ Move เซลลข์ อบเขตพนื้ ทตี่ าราง เคล็ดการเลอื กพนื้ ทต่ี าราง 27 ป่ มุ บนแป้นพมิ พท์ ใี่ ชใ้ นการเลอื กพนื้ ทตี่ าราง 27 วธิ ใี ช ้ Mouse เลอื กพน้ื ทตี่ าราง 27 28 7. การแบง่ ขอบเขตพนื้ ทตี่ าราง 29 8. การจดั เตรยี มพน้ื ท่ี ICO เพอ่ื รบั ขอ้ มลู และป้ องกนั การแกไ้ ข 9. เมอ่ื ใดจะแยกชที เมอ่ื ใดจะแยกแฟ้ ม 10. การกาํ หนดตาํ แหนง่ อา้ งองิ ขา้ มชที หรอื ขา้ มแฟ้ ม วธิ ยี อ้ นปลายกลบั มาหาตน้ วธิ ี Copy จากตน้ แลว้ มา Paste Special แบบ Paste Link ทป่ี ลาย วธิ ี Cut จากตน้ แลว้ ไป Paste ทป่ี ลาย ขอ้ ควรระวงั ในการรักษาสตู ร link ขา้ มแฟ้มใหใ้ ชง้ านไดถ้ กู ตอ้ งตลอดไป วธิ ใี ช ้ Excel ไลเ่ ปิดแฟ้มทัง้ หมดทต่ี อ้ งการใหเ้ อง สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 3 of 147   30 แนวทางการสรา้ งงานเบอ้ื งตน้ 32 เคล็ดการจัดการกบั ระบบ link ขา้ มแฟ้ม 37การนําตําแหน่งอา้ งองิ ไปใชซ้ ํ้า 3911. หลกั การตงั้ ชอ่ื และใชช้ อ่ื ใน Excel 41 42การตงั้ ชอ่ื Constant Name 45การตงั้ ชอื่ Range Name 47วธิ ตี งั้ ชอื่ ตําแหน่งอา้ งองิ แบบไรข้ อบเขตจํากดั 47การตงั้ ชอื่ Formula Name 48การเปลย่ี นตําแหน่งอา้ งองิ ในสตู รทม่ี อี ยกู่ อ่ นใหก้ ลายเป็ นชอื่ ทตี่ งั้ ขน้ึ ในภายหลงัเคล็ดลบั อนื่ ๆทคี่ วรทราบเกยี่ วกบั ชอ่ื ทบี่ อกตําแหน่ง 4912. การใชส้ ตู รปรบั ตาํ แหนง่ อา้ งองิ 49 50วธิ ที ่ี 1 : ใชส้ ตู ร Indirect(“Text บอกตําแหน่ง”) แบบเซลลเ์ ดยี ว 51วธิ ที ี่ 2 : ใชส้ ตู ร Indirect(“Text บอกตําแหน่ง”) แบบใชต้ ําแหน่งขอบเขตตาราง 51วธิ ที ่ี 3 : ใชส้ ตู ร Indirect(“Text บอกตําแหน่ง”) แบบใชช้ อื่ Range Name 52ขอ้ ควรระวงั ในการใชส้ ตู ร Indirectวธิ ที ่ี 4 : ใชส้ ตู รเพอื่ ยา้ ยตําแหน่งอา้ งองิ โดยอตั โนมตั ิ 5213. การกาํ หนดตาํ แหนง่ อา้ งองิ แบบวงกลม (Circular Reference) 54ขนั้ ตอนการสรา้ งสตู รคํานวณแบบ Circular Reference 5514. เคล็ดลบั อนื่ ๆเกย่ี วกบั การใชต้ าํ แหนง่ อา้ งองิ 5815. แป้ นพมิ พล์ ดั ทเ่ี กยี่ วขอ้ งกบั ตาํ แหนง่ อา้ งองิ และขอบเขตพนื้ ทตี่ าราง 5916. วธิ ใี ช้ Excel วเิ คราะหท์ างออกทเ่ี ป็ นไปไดท้ างธรุ กจิ 59 61เรม่ิ จากแยกตวั แปรคอื ประตสู ทู่ างออก 64วธิ ใี ช ้ Goal Seekขอ้ จํากดั ของ Goal Seek และวธิ แี กไ้ ข สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 4 of 147  65  66 68 วธิ ใี ช ้ Data Table 69 Data Table แบบ 1 ตวั แปร 71 Data Table แบบ 2 ตวั แปร 75 Data Table แบบไมจ่ ํากดั ตวั แปร วธิ ใี ช ้ Solver 76 เครอื่ งมอื อน่ื ในการวเิ คราะหท์ างออกทเี่ ป็ นไปได ้ 76 17. เรอื่ งทไี่ มค่ อ่ ยรกู้ นั เกย่ี วกบั สตู ร 78 78 ทําอยา่ งไรใหเ้ กง่ สตู ร Excel 79 Formula vs Function 80 Text vs Number 80 ขนาดแฟ้ม vs ประเภทขอ้ มลู 82 ตําแหน่งเซลลท์ เ่ี ลอื กสําหรับสรา้ งสตู ร 83 สตู รกม็ จี ังหวะหายใจดว้ ยนะ 84 ธรรมชาตขิ องสตู ร 86 จะสรา้ งสตู รใหว้ นุ่ กนั ไปไดแ้ คไ่ หน 87 สตู รทห่ี าคา่ ได ้ ก็ใชบ้ อกตําแหน่งได ้ 88 จํานวนเซลลส์ ตู รเดยี ว วธิ แี ก ้ error #VALUE! ดว้ ย Lotus 1-2-3 89 ตวั อกั ษรมไิ ดม้ คี า่ เทา่ กบั 0 91 18. หลากหลายแนวทางการใชส้ ตู ร IF 93 93 การใชส้ ตู ร IF ในการเปลย่ี นเสน้ ทางการรับคา่ 95 การใชส้ ตู ร IF ในการตดั สนิ ใจ 97 การใช ้ IF แบบกําหนดเงอ่ื นไขแตกตอ่ กรณที ไ่ี มใ่ ช่ 98 การใช ้ IF แบบกําหนดเงอ่ื นไขแตกตอ่ กรณที ใ่ี ช่ การใชส้ ตู ร IF ในการเปลย่ี นคา่ หรอื แสดงคา่ สตู รกลมุ่ Is ทใี่ ชช้ ว่ ยในการตรวจสอบ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 5 of 147  99  101 การใชส้ ตู ร IF ในการเปลยี่ นเสน้ ทางการสง่ คา่ 103 19. หลบรอ้ นหนสี ตู ร IF ไปหาทางออกอนื่ 105 107 หนสี ตู ร IF ไปใชส้ ตู ร Choose หรอื Index 109 หนสี ตู ร IF ไปใชส้ ตู ร VLookup หนสี ตู ร IF ไปใชส้ ตู ร Max Min หรอื แคใ่ ชว้ งเล็บชว่ ย 110 หนสี ตู ร IF ไปใชค้ ําสง่ั บนเมนู 111 20. วธิ ใี ช้ Data Validation 21. วธิ ใี ช้ Conditional Formatting 112 22. การควบคมุ ตาํ แหนง่ ของเซลลท์ จี่ ะเปิ ดใหพ้ มิ พค์ า่ 23. สตู ร Array : สตู รซง่ึ คนสว่ นใหญใ่ ชไ้ มเ่ ป็ น 113 สตู ร Array IF เพอื่ ใชห้ ายอดรวมแยกประเภท 123 วธิ พี ลกิ แพลงสตู ร SumProduct 132 24. ของจรงิ ของปลอม ของใชไ้ มไ่ ดใ้ น Excel 134 ของจรงิ ทท่ี ําใหส้ ตู ร IF กลายเป็ นของปลอม (Round, Trunc, Int, Mod) 134 วนั ทจ่ี อมปลอม 136 วธิ ใี ชว้ นั ทแ่ี ละเวลา 137 แฟ้มของฉันทํางานไดจ้ รงิ แตถ่ า้ คนอนื่ ใชจ้ ะเป็ นของปลอม 140 Excel สวยแตร่ ปู จบู ไมห่ อมกไ็ ด ้ จบู หอมก็ได ้ (Format) 142 25. วธิ ใี ช้ Add-In 147 คําอทุ ศิ แดแ่ มป่ ้าทร่ี ักยง่ิ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 6 of 147   คาํ นําสดุ ยอดเคล็ดลบั และลดั ของ Excel เลอื กสรรความรเู ้ กยี่ วกบั การใช ้ Excel เทา่ ทจ่ี ําเป็ นตอ่ การใช ้งานมานําเสนอ ขอใหร้ จู ้ ักเลอื กใชเ้ ครอ่ื งมอื สตู ร หรอื คําสงั่ ทจี่ ําเป็ นของ Excel ใหไ้ ดเ้ พยี ง15% แลว้ หาทางนําไปใชส้ รา้ งงานใหไ้ ดเ้ ต็มทใี่ นทนั ที ยอ่ มดกี วา่ การเสยี เงนิ เสยี เวลาไปซอ้ืหนังสอื เลม่ โตแลว้ ไลอ่ า่ นไปทลี ะหนา้ แตไ่ มท่ ราบวา่ จะนําความรไู ้ ปใชก้ อ่ นหลงั อยา่ งไรสดุ ยอดเคล็ดลบั และลดั ของ Excel มใิ ชเ่ ป็ นเพยี งหนังสอื เลม่ หนง่ึ แตย่ งั เป็ นหลกั สตู รอบรมExcel ทจ่ี ดั ขน้ึ ทสี่ มาคมสง่ เสรมิ เทคโนโลยี (ไทย-ญป่ี ่ นุ ) มากวา่ สบิ ปี จนกลายเป็ นหลักสตู รยอดนยิ มหลกั สตู รหนง่ึ ซงึ่ มผี สู ้ นใจตดิ ตอ่ จองอบรมกนั ลว่ งหนา้ กนั เป็ นเดอื น โดยเฉพาะในชว่ งเศรษฐกจิ ซบเซากย็ งั มผี สู ้ นใจเขา้ อบรมกนั มใิ ชน่ อ้ ย จงึ นับวา่ เป็ นหลกั สตู รอบรมทกี่ อ่ ประโยชน์ชว่ ยเหลอื การทําธรุ กจิ ไดท้ งั้ ในยามเฟ่ืองฟแู ละในยามยากจนขดั สนเงนิ ทองผทู ้ เี่ หมาะสําหรับสดุ ยอดเคล็ดลบั และลดั ของ Excel ควรมพี นื้ ฐานความรู ้ Excel มาบา้ ง อยา่ งนอ้ ยทสี่ ดุ ขอใหค้ นุ ้ เคยกบั การใชส้ ตู รและคําสงั่ ของ Excel มากอ่ น ถา้ ใหด้ ที สี่ ดุ ควรเคยใช ้ Excelทําผดิ พลาดมากอ่ นมากเทา่ ใดยง่ิ ดี เพราะจะไดเ้ รยี นรสู ้ ง่ิ ใหมๆ่ และมองเปรยี บเทยี บออกวา่ วธิ ีเกา่ ของคณุ กบั วธิ ที ส่ี ดุ ยอดเคล็ดลบั และลดั ของ Excel นําเสนอนัน้ วธิ ไี หนดกี วา่ กนั วธิ ใี ดทง่ี า่ ยชว่ ยลดความผดิ พลาดในการใช ้ Excel และทําใหค้ ณุ มคี วามสขุ ในการใช ้ Excel มากกวา่ กนัเนอื้ หาในหนังสอื เลม่ นใี้ ชก้ บั Excel 2010 เป็ นตน้ มาและเรยี งลําดบั ไวใ้ หใ้ กลเ้ คยี งกบั ลําดบั ในการอบรมใหม้ ากทสี่ ดุ จงึ ขอแนะนําใหเ้ ปิดอา่ นตงั้ แตห่ นา้ แรกไลไ่ ปทลี ะบท และขอใหพ้ ยายามคดิ พจิ ารณาตามไปดว้ ยวา่ ทําไมเครอื่ งมอื นัน้ ตอ้ งใชก้ อ่ นเครอื่ งมอื นี้ นกึ ตงั้ คําถามตอ่ ตวั เองเสมอวา่ เพราะอะไรและเพอ่ื อะไรจงึ ตอ้ งใชเ้ ครอ่ื งมอื สตู ร หรอื คําสง่ั อยา่ งนัน้ อยา่ งน้ี ลําดบัเนอ้ื หาของสดุ ยอดเคล็ดลบั และลดั ของ Excel มไิ ดย้ กขนึ้ มาเพยี งเพราะคดิ วา่ เป็ นเรอื่ งทส่ี ําคญัหรอื น่าสนใจ หากยงั มคี วามสมั พันธก์ นั ระหวา่ งเนอื้ หาแตล่ ะลําดบั กอ่ นหลังอกี ดว้ ยนอกจากความรทู ้ ไ่ี ดร้ ับจากหนังสอื นแ้ี ละการอบรมทส่ี มาคมสง่ เสรมิ เทคโนโลยี (ไทย-ญป่ี ่ นุ )แลว้ ขอใหด้ บู ทความและสอบถามปัญหาไดฟ้ รจี ากเว็บ www.ExcelExpertTraining.com หรอืwww.XLSiam.com สมเกยี รติ ฟ้งุ เกยี รติ 7/1 รามคําแหง ซอย 35 หวั หมาก บางกะปิ กทม 10240 โทร 02 718 9331, โทร/แฟกซ์ 02 318 7021 [email protected] 30 สงิ หาคม 2553 สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 7 of 147  วธิ ปี รบั Excel ใหพ้ รอ้ มตอ่ การใชง้ านอยา่ งมอื อาชพี1. ตอ้ งการปรบั ใหก้ ดป่ มุ Enter เพอื่ บนั ทกึ คา่ แลว้ อยทู่ เี่ ซลลเ์ ดมิ สง่ั File > Options > Advanced > ตดั กาชอ่ ง After pressing Enter, Move selection ทงั้ นใี้ น Excel 2007 สง่ั Office > Excel Options แทน File > Options > (Excel 2003 : Tools > Options > Edit > ตดั กาชอ่ ง Move selection after Enter)2. ตอ้ งการปรบั ใหก้ ดป่ มุ F2 เพอ่ื Edit แกไ้ ขบน Formula Bar แทนในเซลล์ สงั่ File > Options > Advanced > ตดั กา ชอ่ ง Allow editing directly in cells (Excel 2003 : Tools > Options > Edit > ตดั กาชอ่ ง Edit directly in cell) คําสงั่ นย้ี งั มผี ลทําใหเ้ มอื่ ดับเบลิ คลกิ ทเ่ี ซลลส์ ตู รแลว้ Excel จะยา้ ย ตําแหน่งไปยงั ตําแหน่งเซลลต์ น้ ทางทอี่ า้ งองิ ในสตู รนัน้ และเมอื่ กด ป่ มุ F5 > Enter จะยา้ ยกลบั มาทเี่ ซลลเ์ ดมิ ทเ่ี คยดบั เบลิ คลกิ3. ตอ้ งการเปลย่ี นระบบการคาํ นวณเป็ น manual สงั่ File > Options > Formulas > กา ชอ่ ง Manual จากนัน้ ตอ้ งกดป่ มุ F9 เพอื่ สงั่ คํานวณทกุ แฟ้มหรอื กด Shift+F9 เพอื่ สง่ั คํานวณเฉพาะ แฟ้มทเ่ี ลอื กอยเู่ ทา่ นัน้ (Excel 2003 : Tools > Options > Calculation > กาชอ่ ง Manual) หมายเหตุ ระบบการคํานวณจะ เปลย่ี นตามระบบของแฟ้มแรกที่ เปิดขนึ้ กอ่ นเสมอ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 8 of 147  4. ตอ้ งการเปิ ดใหใ้ ช้ Macro และ Update Links ไดอ้ ตั โนมตั ทิ กุ แฟ้ มในโฟลเดอรท์ ่ี กาํ หนดวา่ ปลอดภยั สง่ั File > Options > Trust Center > กดป่ มุ Trust Center Settings... (Excel 2003 : Tools > Macro > Security > Security Level > กาชอ่ ง Medium) เลอื กเมนู Trusted Locations จากเมนูดา้ นซา้ ย จากนัน้ กดป่ มุ Add new location... เพอื่ เลอื กชอ่ื โฟลเดอร์ (Trusted Folder) ตามตอ้ งการ นอกจากนสี้ ําหรับ Excel 2010 เป็ นตน้ มายงั มเี มนู Trusted Documents เพอ่ื ใชค้ วบคมุ การ เปิดแฟ้มเพอื่ ทําใหแ้ ฟ้มใดทเี่ คยเปิดโดยให ้ Macro ทํางานหรอื Update Links มากอ่ นก็จะ เปิดแบบนัน้ ใหเ้ องในคราวตอ่ ไป สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 9 of 147  การกาํ หนดตาํ แหนง่ อา้ งองิ และขอบเขตพน้ื ทต่ี าราง Excelบทความนม้ี จี ดุ ประสงคใ์ หผ้ ทู ้ เี่ คยใช ้ Excel มากอ่ นและมพี นื้ ฐานมาบา้ งจนพอทราบวา่ ตําแหน่งอา้ งองิ (Cell Reference) ในตาราง Excel คอื อะไรและนําไปใชก้ นั อยา่ งไร จะไดเ้ ปิดหเู ปิดตาเรยี นรเู ้ คล็ดลบั ทคี่ นทวั่ ไปอาจมองขา้ มหรอื ไมน่ กึ ฝันวา่ จะเป็ นไปได ้ แลว้ สามารถนําตําแหน่งอา้ งองิ ไปใชก้ บั สตู รและคําสงั่ ไดอ้ ยา่ งพลกิ แพลงมากยงิ่ ขน้ึ โดยเฉพาะการเลอื กใสเ่ ครอ่ื งหมาย$ ใหก้ บั ตําแหน่งอา้ งองิ นัน้ ไมว่ า่ จะเป็ น $ ในเซลลส์ ตู ร, $ ใน Conditional Format, $ ในData Validation, หรอื $ ในในเครอ่ื งมอื อน่ื ๆของ Excel ก็ตาม ผมถอื วา่ $ เป็ นหวั ใจของการใช ้Excel ทเี ดยี วตงั้ หลกั กนั กอ่ นวา่ จะใช้ Excel อยา่ งคอมพวิ เตอร์คณุ ลองนกึ ดซู วิ า่ มนุษยเ์ ราไดร้ ับประโยชนจ์ ากการใชเ้ ครอ่ื งคอมพวิ เตอรต์ า่ งจากสมยั ทย่ี งั มแี ต่เครอ่ื งคดิ เลขใหใ้ ชก้ นั อยา่ งไรบา้ ง หลายคนน่าจะตอบวา่ เครอ่ื งคอมพวิ เตอรต์ อ้ งชว่ ยใหค้ ําตอบทเี่ ร็วกวา่ เครอ่ื งคดิ เลขใชไ่ หม แลว้ ความเร็วทว่ี า่ นถ้ี กู วดั มาจากเวลาทใี่ ชไ้ ปของเครอ่ื งคอมพวิ เตอรห์ รอื เวลาของคณุ กนั ละ่แน่นอนทส่ี ดุ วา่ เครอ่ื งคอมพวิ เตอรย์ อ่ มทํางานเร็วกวา่ เครอื่ งคดิ เลข และเครอ่ื งคอมพวิ เตอรร์ นุ่ใหมย่ อ่ มทํางานไดเ้ ร็วกวา่ เครอ่ื งคอมพวิ เตอรร์ นุ่ เกา่ ยงิ่ ใช ้ Excel ทต่ี ดิ ตงั้ บนเครอื่ งคอมพวิ เตอร์รนุ่ ลา่ สดุ กย็ ง่ิ ไดค้ ําตอบผลลพั ธจ์ ากการคํานวณเร็วขน้ึ ซง่ึ ถา้ ใครหลงเขา้ ใจวา่ ตวั เองใช ้ Excelแบบคอมพวิ เตอรเ์ ป็ นอยแู่ ลว้ เพยี งเพราะดจู ากความเร็วของเครอ่ื งคอมพวิ เตอรท์ ใ่ี ชอ้ ยู่ กเ็ ป็ นความเขา้ ใจทถี่ กู ตอ้ งเพยี งบางสว่ น (และเป็ นสว่ นทไี่ มส่ ําคญั เทา่ ใดเสยี ดว้ ย) เพราะถา้ คณุ ใช ้Excel อยา่ งคอมพวิ เตอรเ์ ป็ น ตอ่ ใหย้ กเครอื่ งคอมพวิ เตอรร์ นุ่ โบราณทท่ี ํางานชา้ เป็ นเตา่ ก็จะพบวา่ คณุ ไมไ่ ดเ้ สยี เวลาตา่ งกนั มากนักลกั ษณะของการใช ้ Excel อยา่ งคอมพวิ เตอรม์ ดี งั นี้ 1. คณุ จะตอ้ งเสยี เวลาสรา้ งงานนานหน่อย เนอ่ื งจากตอ้ งหาทางคดิ สรา้ งงานใหเ้ หมาะสม กบั แตล่ ะสถานการณ์ ซง่ึ คณุ ควรตงั้ หลกั ไวว้ า่ แฟ้มงานทสี่ รา้ งขน้ึ มานัน้ จะตอ้ งสามารถ นํากลบั มาใชซ้ าํ้ แลว้ ซํา้ อกี ไดโ้ ดยไมต่ อ้ งยอ้ นกลบั ไปแกไ้ ขใหมอ่ กี แมแ้ ตน่ อ้ ย 2. หากจําเป็ นตอ้ งแกไ้ ข ตอ้ งสามารถแกไ้ ขปรับปรงุ แฟ้มไดง้ า่ ย ใชเ้ วลาไมม่ าก และไม่ สง่ ผลกระทบตอ่ การคํานวณเดมิ ทถ่ี กู ตอ้ งอยแู่ ลว้ ทงั้ นไ้ี มว่ า่ จะกําหนดใหผ้ ทู ้ ท่ี ําหนา้ ท่ี แกไ้ ขนัน้ คอื คณุ ซงึ่ เป็ นผสู ้ รา้ งแฟ้มนัน้ เอง หรอื แมแ้ ตจ่ ะเป็ นผอู ้ น่ื ซง่ึ ไมเ่ คยใชแ้ ฟ้มนัน้ มา กอ่ นกต็ าม 3. สามารถลดจํานวนคนและไมต่ อ้ งเพม่ิ อตั รากําลงั คนตามปรมิ าณงานทเี่ พม่ิ ขน้ึ 4. ประหยดั เงนิ ลงทนุ เพราะไมต่ อ้ งจา่ ยเพอื่ ซอ้ื เครอื่ งหรอื โปรแกรมคอมพวิ เตอรร์ นุ่ ใหม่ 5. คณุ ตอ้ งมคี วามสขุ และมเี วลาวา่ งใหก้ บั ชวี ติ มากขนึ้ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 10 of 147  สตู รฉลาด (Smart Formula)Smart Formula แปลวา่ สตู รฉลาด ซงึ่ สตู รฉลาดทวี่ า่ นจี้ ะฉลาดไดก้ ต็ อ่ เมอื่ ใชค้ วามฉลาดของคณุ ในการสรา้ งสตู รขน้ึ มา เพอื่ ใหม้ ลี กั ษณะการใชง้ านไดต้ ามนี้ ในชที หนงึ่ ๆหรอื ตารางหนงึ่ ๆ ตอ้ งมสี ตู รตน้ ฉบบั เพยี งเซลลเ์ ดยี ว ซงึ่ สามารถนําไปใชไ้ ด ้ กบั เซลลอ์ นื่ ๆไดท้ งั้ ตาราง แลว้ เมอ่ื ใดทจี่ ําเป็ นตอ้ งแกไ้ ขสตู รใหม่ กจ็ ัดการแกไ้ ขสตู รนี้ เพยี งเซลลเ์ ดยี วแลว้ Copy นําไป Paste เพอื่ ใชไ้ ดก้ บั ทกุ เซลลใ์ นตารางในโครงสรา้ งของตวั สตู รฉลาดอาจใชว้ ธิ กี ําหนดตําแหน่งอา้ งองิ แบบเซลลเ์ ดยี วหรอื แบบกําหนดขอบเขตตารางก็ได ้ โดยไมว่ า่ จะใชว้ ธิ ใี ดกห็ ลกี หนกี ารกําหนดตําแน่งอา้ งองิ ทม่ี เี ครอ่ื งหมาย $กํากบั ตําแหน่ง 3 แบบตอ่ ไปนไี้ ปไมพ่ น้  ตาํ แหนง่ อา้ งองิ แบบ Absolute Reference มเี ครอื่ งหมาย $ วางไวด้ า้ นหนา้ ของทงั้ ตําแหน่ง Row และ Column เชน่ =$A$1 โดยใหใ้ ชต้ ําแหน่งอา้ งองิ แบบนก้ี บั กรณีที่ ตอ้ งการนําคา่ คงทไี่ ปใช ้  ตาํ แหนง่ อา้ งองิ แบบ Relative Reference เป็ นตําแหน่งอา้ งองิ ซง่ึ ไมม่ เี ครอ่ื งหมาย $ เชน่ =A1 ซงึ่ สตู รทอี่ า้ งองิ แบบนจ้ี ะตอ้ งใชท้ เ่ี ซลลเ์ ดมิ เซลลเ์ ดยี วไปตลอด ไมส่ ามารถ Copy นําไป Paste เพอ่ื ทําใหไ้ ดค้ า่ เดมิ ซา้ํ ทเี่ ซลลอ์ น่ื ไดอ้ กี เพราะตําแหน่งอา้ งองิ จะ เขยอื้ นตา่ งจากตําแหน่งเดมิ เสมอ ดงั นัน้ จงึ เป็ นการอา้ งองิ แบบทใ่ี ชใ้ นเซลลส์ ตู รกนั นอ้ ย ทสี่ ดุ แตก่ ลบั ถกู นํามาใชก้ นั มากใน Conditional Formatting หรอื Data Validation  ตาํ แหนง่ อา้ งองิ แบบ Mixed Reference เป็ นตําแหน่งอา้ งองิ ทนี่ ยิ มใชก้ นั มากทส่ี ดุ โดยผสู ้ รา้ งสตู รตอ้ งเลอื กใสเ่ ครอ่ื งหมาย $ ดา้ นหนา้ ตําแหน่ง Row หรอื Column เพยี ง อยา่ งใดอยา่ งหนงึ่ เชน่ =A$1 เพอ่ื คงตําแหน่ง Row 1 ไวต้ ามเดมิ ตลอด หรอื =$A1 เพอื่ คงตําแหน่ง Column A ไวต้ ามเดมิ ตลอด ไมว่ า่ จะ Copy นําสตู รไป Paste ท่ี ตําแหน่งอน่ื ก็ตามวธิ สี รา้ งสตู รใหม้ ตี ําแหน่งอา้ งองิ ทมี่ เี ครอ่ื งหมาย $ นําหนา้ ตําแหน่งของ Row และหรอื Columnน้ี ใหใ้ ชว้ ธิ ี Pointing แลว้ กดป่ มุ F4 ซาํ้ ไปเรอื่ ยๆจนกวา่ จะไดต้ ําแหน่ง $ ตามตอ้ งการ 1. ใหเ้ รม่ิ จากพมิ พเ์ ครอื่ งหมายเทา่ กบั = ลงไปในเซลลท์ ต่ี อ้ งการสรา้ งสตู ร (เพอื่ แสดงการ ยดึ ครองเซลลน์ ัน้ ไวก้ อ่ นวา่ เป็ นเซลลท์ จี่ ะรับสตู รลงไปเมอื่ เรากดป่ มุ Enter) 2. จากนัน้ ใชว้ ธิ ชี ล้ี งไปในเซลลท์ ต่ี อ้ งการ (Pointing แปลวา่ ช)้ี โดยใช ้ Mouse คลกิ เลอื ก เซลลท์ ตี่ อ้ งการ ไมว่ า่ จะอยตู่ า่ งชที หรอื ตา่ งแฟ้มกนั ก็ตาม เชน่ คลกิ ทเี่ ซลล์ A1 จะได ้ สตู ร =A1 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 11 of 147   3. จากนัน้ ใหก้ ดป่ มุ F4 ไปเรอื่ ยๆ จะพบวา่ มเี ครอ่ื งหมาย $ แทรกลงในสตู รใหเ้ องตามลําดบั ทม่ี กี ารกดป่ มุ F4 ดงั นี้ A1  $A$1  A$1  $A1  A1  แลว้ จะมเี ครอื่ งหมาย $ วนซาํ้ ไปอกี 4. เมอื่ ไดต้ ําแหน่ง $ ควบคมุ ตําแหน่ง Row และหรอื Column ตามตอ้ งการแลว้ ใหก้ ดป่ มุ Enter เพอ่ื รับสตู รลงไปในเซลล์ แลว้ จะพบวา่ Excel จะพากลบั มาทเี่ ซลลซ์ ง่ึ พมิ พ์ เครอ่ื งหมายเทา่ กบั นําหนา้ สตู รเอาไวต้ งั้ แตแ่ รกนั่นเองการใสเ่ ครอ่ื งหมาย $ เพอ่ื เป็ นการควบคมุ ตําแหน่งอา้ งองิ ไวไ้ มใ่ หเ้ ขยอ้ื นนี้ นอกเหนอื จากการทําใหค้ ณุ สามารถ Copy สตู รไปใชใ้ นเซลลอ์ น่ื ไดท้ นั ทแี ลว้ ยงั เป็ นเรอ่ื งน่าสงั เกตวา่ ชว่ ยทําใหแ้ ฟ้มมขี นาดเล็กลงกวา่ แฟ้มทมี่ สี ตู รซงึ่ ไมม่ ี $ กําหนดไวเ้ ลย (ขอใหท้ ดลองลบเครอื่ งหมาย $ ทงิ้จากสตู รในตารางขนาดใหญเ่ พอ่ื เปรยี บเทยี บดขู นาดแฟ้ม โดยใชค้ ําสง่ั Replace เครอื่ งหมาย $แตไ่ มไ่ ด ้ Replace with ดว้ ยเครอ่ื งหมายใด)นอกจากตําแหน่งอา้ งองิ ทตี่ อ้ งมเี ครอ่ื งหมาย $ เป็ นองคป์ ระกอบสําคญั แลว้ ในโครงสรา้ งของสตู ร Smart Formula ยงั อาจจําเป็ นตอ้ งพงึ่ สตู ร IF, And, Or หรอื สตู รอนื่ ๆซงึ่ ทําหนา้ ทต่ี ดั สนิ ใจเพอื่ ชว่ ยทําใหส้ ตู รมเี งอ่ื นไขในการคํานวณตา่ งไปจากเดมิ ไดเ้ องทนั ที เมอ่ื มกี ารนําสตู รเดยี วกนัไปใชซ้ ้าํ ทตี่ ําแหน่งเซลลอ์ นื่ตวั อยา่ ง Smart Formula แบบใชต้ าํ แหนง่ อา้ งองิ เซลลเ์ ดยี วขอใหส้ รา้ งตารางสตู รคณู ซงึ่ นําตวั เลขบนหวั ตารางมาคณู กบั ตวั เลขดา้ นขา้ งซา้ ยของตารางขนั้ ตอนการสรา้ ง 1. เรมิ่ จากเลอื กเซลล์ C3 แลว้ พมิ พเ์ ครอื่ งหมายเทา่ กบั = ลงไป 2. จากนัน้ คลกิ เซลล์ C2 จะไดส้ ตู ร =C2 3. กดป่ มุ F4 เพอื่ เปลยี่ นสตู รใหเ้ ป็ น =C$2 4. พมิ พเ์ ครอ่ื งหมายคณู * 5. จากนัน้ คลกิ เลอื กเซลล์ B3 จะไดส้ ตู ร =C$2*B3 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 12 of 147   6. กดป่ มุ F4 เพอื่ เปลย่ี นสตู รเป็ น =C$2*$B3 7. กดป่ มุ Enter เพอื่ รับสตู รลงไปในเซลล์ C3 8. Copy ไปทบั ตาราง C3:F5คาํ อธบิ าย  เซลล์ C3 เป็ นเซลลท์ มี่ สี ตู ร Smart Formula ซง่ึ โดยทว่ั ไปจะเลอื กใหเ้ ซลลห์ วั มมุ ดา้ นซา้ ยบนของตารางเป็ นเซลลท์ ม่ี สี ตู ร Smart Formula  คณุ อาจเลอื กสรา้ งสตู รคณู โดยใชเ้ ลข 2 นําหนา้ แลว้ นําไปคณู กบั เลข 10 เพอ่ื ทําใหไ้ ด ้ สตู ร =C$2*$B3 หรอื จะเลอื กใชเ้ ลข 10 นําหนา้ แลว้ นําไปคณู กบั เลข 2 ซง่ึ จะทําใหเ้ กดิ สตู ร =$B3*C$2 ก็ไดแ้ ละทําใหเ้ กดิ ผลลพั ธถ์ กู ตอ้ งเทา่ กนั ทงั้ สองแบบ แตข่ อแนะนําให ้ สรา้ งพฤตกิ รรมในการสรา้ งสตู รของตนวา่ “ทกุ สตู รทสี่ รา้ งขนึ้ จะนําตาํ แหนง่ อา้ งองิ ตามแนวนอนขน้ึ กอ่ นตาํ แหนง่ อา้ งองิ ตามแนวตงั้ เสมอ” ซงึ่ หลกั นจี้ ะชว่ ยใหค้ ณุ แกะ สตู รของตวั เองไดง้ า่ ยขน้ึ และหากสงั เกตสตู รอนื่ ๆของ Excel เชน่ Index หรอื Offset จะใชต้ ําแหน่งอา้ งองิ จากแนวนอน Row ขน้ึ กอ่ นตําแหน่งอา้ งองิ จากแนวตงั้ Column เชน่ กนั  C$2 ตอ้ งมเี ครอื่ งหมาย $ วางไวห้ นา้ เลข 2 เนอื่ งจากตารางสตู รคณู นม้ี ี Row 2 เป็ น ตําแหน่งอา้ งองิ ทตี่ อ้ งควบคมุ ไวไ้ มใ่ หเ้ ปลย่ี น สว่ นตวั C ไมต่ อ้ งมเี ครอ่ื งหมาย $ นําหนา้ เพอ่ื ปลอ่ ยให ้ C เปลยี่ นตําแหน่งเป็ น Column D E F หรอื Column อน่ื ๆตามแตว่ า่ จะใช ้ สตู รนห้ี รอื ไม่  $B3 ตอ้ งมเี ครอื่ งหมาย $ วางไวห้ นา้ Column B เนอื่ งจากตารางสตู รคณู นม้ี ี Column B เป็ นตําแหน่งอา้ งองิ ทต่ี อ้ งควบคมุ ไวไ้ มใ่ หเ้ ปลยี่ น สว่ นเลข 3 ไมต่ อ้ งมเี ครอ่ื งหมาย $ นําหนา้ เพอื่ ปลอ่ ยใหเ้ ลข 3 เปลยี่ นตําแหน่งเป็ น Row 4 5 6 หรอื Row อนื่ ๆตามแตว่ า่ จะใชส้ ตู รนห้ี รอื ไม่  หลกั การใสเ่ ครอื่ งหมาย $ นี้ ใหจ้ ําไวว้ า่ “ถา้ ตาํ แหนง่ อา้ งองิ ในสตู รมาจากคา่ ตาม แนวนอน ใหใ้ ส่ $ ไวห้ นา้ Row (เพราะ แนวนอน = Row) แตถ่ า้ ตาํ แหนง่ อา้ งองิ ในสตู รมาจากคา่ ตามแนวตง้ั ใหใ้ ส่ $ ไวห้ นา้ Column (เพราะ แนวตง้ั = Column)” สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 13 of 147  ตวั อยา่ ง Smart Formula แบบใชต้ ําแหนง่ อา้ งองิ ตามขอบเขตพน้ื ทห่ี วัตารางขอใหส้ รา้ งตารางสตู รคณู ซง่ึ นําตวั เลขบนหวั ตารางมาคณู กบั ตวั เลขดา้ นขา้ งซา้ ยของตารางวธิ กี ารสรา้ งคลา้ ยกบั วธิ แี รก แทนทจี่ ะคลกิ เลอื กเซลล์ C2 หรอื เซลล์ B3 วธิ นี ใ้ี หค้ ลกิ เลอื กเซลล์C2:F2 และ B3:B5 ซงึ่ เป็ นหวั ตารางดา้ นบนและดา้ นซา้ ยทงั้ หมด แลว้ กดป่ มุ F4 เพอื่ ทําใหเ้ กดิสตู ร =$C$2:$F$2*$B$3:$B$5 1. เรม่ิ จากเลอื กเซลล์ C3 แลว้ พมิ พเ์ ครอ่ื งหมายเทา่ กบั = ลงไป 2. จากนัน้ คลกิ เลอื กเซลลบ์ นหวั ตารางดา้ นบนจากเซลลเ์ ลข 2 - 5 จะไดส้ ตู ร =C2:F2 3. กดป่ มุ F4 เพอ่ื เปลย่ี นสตู รใหเ้ ป็ น =$C$2:$F$2 4. พมิ พเ์ ครอ่ื งหมายคณู * 5. จากนัน้ คลกิ เลอื กเซลลบ์ นหวั ตารางดา้ นซา้ ยจากเซลลเ์ ลข 10 - 30 จะไดส้ ตู ร =$C$2:$F$2*B3:B5 6. กดป่ มุ F4 เพอ่ื เปลยี่ นสตู รเป็ น =$C$2:$F$2*$B$3:$B$5 7. กดป่ มุ Enter เพอ่ื รับสตู รลงไปในเซลล์ C3 8. Copy ไปทบั ตาราง C3:F5คาํ อธบิ าย  สตู รแบบทอ่ี า้ งองิ กบั ตําแหน่งทงั้ หมดบนหวั ตารางหรอื ขา้ งตาราง แลว้ ใสเ่ ครอ่ื งหมาย $ ใหก้ บั ตําแหน่งทงั้ Row และ Column นี้ ผมตงั้ ชอ่ื เรยี กวา่ สตู รแบบลอ้ มรวั้ โดยทสี่ ตู ร =$C$2:$F$2*$B$3:$B$5 จะทําหนา้ ทน่ี ําตําแหน่งอา้ งองิ บนรัว้ ตามแนวตงั้ ฉากทตี่ ดั กนั มาคณู กนั ทลี ะตวั ใหเ้ อง ทําใหไ้ มต่ อ้ งคอยพะวงวา่ จะตอ้ งเลอื กใสเ่ ครอ่ื งหมาย $ เพอ่ื ควบคมุ ตําแหน่งอา้ งองิ ตามแนวใดเป็ นหลกั  ถา้ เปรยี บเทยี บกบั สตู รคณู วธิ แี รกซงึ่ ใชต้ ําแหน่งอา้ งองิ แบบเซลลเ์ ดยี ว จะพบวา่ การใช ้ สตู รอา้ งองิ แบบเซลลเ์ ดยี วมคี วามยดื หยนุ่ มากกวา่ เชน่ เมอื่ หวั ตารางมขี นาดใหญม่ ากขนึ้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 14 of 147   ก็สามารถ Copy สตู รไปใชต้ อ่ ไดท้ นั ที แตถ่ า้ สรา้ งสตู รแบบลอ้ มรัว้ จะตอ้ งสรา้ งสตู รใหม่ เพอื่ กําหนดตําแหน่งอา้ งองิ ในสตู รใหม้ ขี นาดขยายตาม (เวน้ แตว่ า่ คณุ ทราบวธิ ขี ยาย ขอบเขตตําแหน่งอา้ งองิ ซงึ่ จะนํามาอธบิ ายตอ่ ไปในภายหลงั )  จดุ ออ่ นของสตู รแบบลอ้ มรัว้ ก็คอื สตู รแบบนใี้ สเ่ ครอื่ งหมาย $ ควบคมุ ตําแหน่งแบบ Absolute จงึ คํานวณหาคําตอบใหไ้ ดเ้ ฉพาะตารางจาก C3:F5 ซงึ่ อยภู่ ายในขอบเขต ของรัว้ เป็ นแนวของตําแหน่งคงที่ หาก Copy สตู รไปใชน้ อกรัว้ จะเกดิ Error ขน้ึ ตามรปู สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 15 of 147  ตวั อยา่ ง Smart Formula แบบใชต้ ําแหนง่ อา้ งองิ แบบ Array ตามขอบเขตพน้ื ทห่ี วั ตารางขอใหส้ รา้ งตารางสตู รคณู ซง่ึ นําตวั เลขบนหวั ตารางมาคณู กบั ตวั เลขดา้ นขา้ งซา้ ยของตารางขน้ั ตอนการสรา้ ง 1. แทนทจี่ ะเลอื กเซลล์ C3 เพยี งเซลลเ์ ดยี ว คราวนใี้ หเ้ ลอื กพน้ื ทต่ี ารางจากเซลล์ C3:F5 ทงั้ หมดพรอ้ มกนั ไวก้ อ่ น 2. จากนัน้ พมิ พเ์ ครอื่ งหมายเทา่ กบั = 3. คลกิ เลอื กเซลล์ C2:F2 บนหวั ตาราง จะไดส้ ตู ร =C2:F2 4. พมิ พเ์ ครอ่ื งหมายคณู * 5. คลกิ เลอื กเซลล์ B3:B5 จากขา้ งตาราง จะไดส้ ตู ร =C2:F2*B3:B5 6. จากนัน้ แทนทจ่ี ะกดป่ มุ Enter ใหก้ ดป่ มุ Ctrl+Shift+Enter พรอ้ มกนั ทเี ดยี ว 3 ป่ มุ จะ ทําใหเ้ กดิ เครอื่ งหมายวงเล็บปี กกาปิดหวั ทา้ ยสตู รใหเ้ องกลายเป็ น {=C2:F2*B3:B5}คาํ อธบิ าย  สาเหตทุ ตี่ อ้ งเลอื กพนื้ ทต่ี าราง C3:F5 ไวก้ อ่ นแทนการเลอื กเซลล์ C3 เพยี งเซลลเ์ ดยี ว เพอ่ื ทําใหผ้ ลลพั ธท์ ไ่ี ดจ้ ากสตู ร Array คนื คา่ กระจายคําตอบลงไปในตารางใหค้ รบทกุ ตําแหน่ง  ลองใช ้ Mouse คลกิ ลงไปในชอ่ ง Formula Bar เลอื กเฉพาะสว่ นของสตู ร C2:F2 แลว้ กดป่ มุ F9 จะพบวา่ ตําแหน่งอา้ งองิ สว่ นนเ้ี ปลยี่ นเป็ น {2,3,4,5} จากนัน้ ใหก้ ดป่ มุ Esc เพอ่ื คนื สภาพสตู รตามเดมิ  ลองใช ้ Mouse คลกิ ลงไปในชอ่ ง Formula Bar เลอื กเฉพาะสว่ นของสตู ร B3:B5 แลว้ กดป่ มุ F9 จะพบวา่ ตําแหน่งอา้ งองิ สว่ นนเี้ ปลยี่ นเป็ น {10;20;30} จากนัน้ ใหก้ ดป่ มุ Esc เพอ่ื คนื สภาพสตู รตามเดมิ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 16 of 147    หากกดป่ มุ F2 ตามดว้ ย F9 จะพบวา่ สตู ร Array เดมิ บน Formula Bar {= C2:F2*B3:B5} จะเปลยี่ นเป็ น ={20,30,40,50;40,60,80,100;60,90,120,150} แลว้ ขอใหส้ งั เกตวา่ มเี ครอ่ื งหมาย Comma และ Semi-Colon ขนั้ อยรู่ ะหวา่ งตวั เลข (ซง่ึ เครอ่ื งหมาย Comma , นแี้ สดงถงึ วา่ คา่ ถดั ไปยงั คงอยใู่ น Row เดมิ สว่ นเครอื่ งหมาย Semi-Colon ; แสดงวา่ คา่ ถดั ไปตอ้ งขน้ึ Row ใหม)่  ดงั นัน้ ={20,30,40,50;40,60,80,100;60,90,120,150} จงึ แสดงใหเ้ ห็นวา่ สตู ร Array นคี้ นื คา่ เป็ นคําตอบซง่ึ มขี นาดความกวา้ ง 4 Column และมคี วามสงู 3 Row ตามขนาด พนื้ ทตี่ งั้ แต่ C3:F5 ทต่ี อ้ งเลอื กไวก้ อ่ น เพอ่ื ใหก้ ระจายคา่ ลงไปใหค้ รบน่ันเอง  หากหวั ตารางมขี นาดเปลย่ี นไป ตอ้ งลบสตู ร Array ทส่ี รา้ งไวเ้ ดมิ นที้ ง้ิ ไปกอ่ นแลว้ จงึ สรา้ งสตู ร Array ใหมล่ งไป ซงึ่ ในการลบสตู ร Array ทงิ้ นี้ หากตอนทส่ี รา้ งสตู รเกดิ จาก การสรา้ งพรอ้ มกนั ทเี ดยี วหลายเซลล์ จะเลอื กลบสตู รทง้ิ บางเซลลไ์ มไ่ ด ้ ตอ้ งเลอื กเซลล์ C3:F5 ทงั้ หมดแลว้ จงึ สง่ั ลบสตู รทงิ้ พรอ้ มกนั ทเี ดยี วจงึ จะลบได ้  เนอื่ งจากสตู ร Array ในตัวอยา่ งนเ้ี กดิ จากการสรา้ งพรอ้ มกนั ทงั้ ตารางดว้ ยสตู รเดยี ว จงึ ทําให ้ Excel คํานวณสตู รทงั้ ตารางสตู รคณู เพยี งครัง้ เดยี ว ถอื เป็ นเคล็ดลบั และลดั ที่ น่าสนใจเป็ นอยา่ งยงิ่ เพราะถา้ สรา้ งไดจ้ ะทําใหแ้ ฟ้มคํานวณเร็วขน้ึ อยา่ งผดิ หผู ดิ ตาทเี ดยี ว (เร็วกวา่ ตวั อยา่ งสตู รคณู ซงึ่ สรา้ งสตู รลงไปในเซลลเ์ ดยี วแลว้ ตอ้ ง Copy ไป Paste เพอื่ ใหค้ ํานวณซาํ้ อกี ในเซลลอ์ น่ื )  หากตอ้ งการคน้ หาวา่ สตู ร Array ทเี่ ห็นนัน้ สรา้ งขนึ้ พรอ้ มกนั จากเซลลใ์ ดถงึ เซลลใ์ ด ให ้ เรมิ่ จากคลกิ เลอื กเซลลส์ ตู ร Array นัน้ ๆกอ่ นเพยี งเซลลเ์ ดยี ว แลว้ กดป่ มุ F5 > Special > กาชอ่ ง Current array จะพบวา่ Excel เลอื กพน้ื ทตี่ ารางสว่ นทเี่ กดิ จาก การสรา้ งดว้ ยสตู ร Array พรอ้ มกนั นัน้ ใหท้ นั ที จากนัน้ หากตอ้ งการลบหรอื สรา้ งสตู ร Array ใหมล่ งไปก็ใหก้ ระทําตอ่ พน้ื ทตี่ ารางทเ่ี ลอื กใหแ้ ลว้ นัน้ รว่ มกนั เสมอ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 17 of 147  วธิ ปี รบั ขนาดขอบเขตพนื้ ทต่ี ารางเพอื่ ทําใหต้ ําแหน่งอา้ งองิ ทก่ี ําหนดไวใ้ นสตู รปรับตําแหน่งตามอยา่ งถกู ตอ้ งเสมอ เมอื่ ใดที่ตอ้ งการปรับขนาดขอบเขตพน้ื ทตี่ าราง ใหใ้ ชว้ ธิ ี Insert หรอื Delete แนว Row หรอื Columnเพอ่ื แทรกเพมิ่ หรอื ลดพนื้ ทภ่ี ายในทง้ิ โดยกําหนดใหม้ ผี ลเฉพาะทงั้ แถวหรอื เฉพาะพน้ื ทใ่ี นตารางนัน้ กไ็ ด ้ตวั อยา่ งการขยายขนาดตารางสมมตวิ า่ ตอ้ งการขยายขนาดตารางใหเ้ พม่ิ อกี 2 Row ใหเ้ รมิ่ จากเลอื กพนื้ ทต่ี ารางตงั้ แต่ Rowสดุ ทา้ ยออกไปตามจํานวน Row ทต่ี อ้ งการ จากนัน้ คลกิ ขวาสงั่ Insert > Shift cells downตวั อยา่ งการลดขนาดตารางสมมตวิ า่ ตอ้ งการลดขนาดตารางใหม้ จี ํานวน Row ลดลง 2 Row ใหเ้ รม่ิ จากเลอื ก Row ภายในพนื้ ทตี่ ารางตามจํานวน Row ทต่ี อ้ งการ จากนัน้ คลกิ ขวาสงั่ Delete > Shift cells upหมายเหตุ 1. หากในชที มตี ารางขอ้ มลู เพยี งตารางเดยี ว ใหใ้ ชว้ ธิ เี ลอื กทงั้ แนว Row หรอื Column โดยไมจ่ ําเป็ นตอ้ งเลอื กเฉพาะภายในพน้ื ทตี่ าราง จากนัน้ ใหส้ ง่ั Insert หรอื Delete ทงั้ แนว Row หรอื Column ไดเ้ ลย 2. การลดขนาดตาราง สามารถเลอื กลบพนื้ ทรี่ วมทงั้ แนว Row สดุ ทา้ ยหรอื Column ขวา สดุ ของขอบเขตตารางก็ได ้ (แตไ่ มแ่ นะนําเพราะเป็ นวธิ ตี า่ งจากการขยายตาราง) สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 18 of 147  ขอ้ ควรระวงั ในการ Move เซลลข์ อบเขตพนื้ ทต่ี าราง 1. หาก move เซลลข์ อบตาราง (ไมว่ า่ จะเป็ นดา้ นขวาสดุ หรอื ดา้ นลา่ งสดุ ) โดยใชค้ ําสงั่ Cut-Paste หรอื ใช ้ Mouse ลากเซลลอ์ อกไปในแนวเดมิ จะทําใหต้ ําแหน่งเซลลท์ อ่ี า้ งไว ้ ในสตู รปรับตําแหน่งอา้ งองิ ตาม แตถ่ า้ move เซลลก์ ลบั มาทเี่ ดมิ ตําแหน่งอา้ งองิ ในสตู ร จะไมย่ า้ ยกลบั มาทเี่ ดมิ แตจ่ ะมตี ําแหน่งเกนิ ไป 1 เซลล์ 2. หาก move เซลลข์ อบตารางออกไปนอกแนวเดมิ จะไมท่ ําใหต้ ําแหน่งเซลลท์ อ่ี า้ งไวใ้ น สตู รยา้ ยตามตวั อยา่ งสมมตวิ า่ จากตารางคํานวณสตู รคณู ซงึ่ อยใู่ นเซลล์ B2:F5 นัน้ เราไดส้ รา้ งสตู รหายอดรวมตวั เลขบนหวั ตารางดา้ นบนไวท้ เ่ี ซลล์ D9 โดยใชส้ ตู ร =SUM(C2:F2) และหายอดรวมตวั เลขจากหวัตารางดา้ นซา้ ยไวท้ เ่ี ซลล์ D10 โดยใชส้ ตู ร =SUM(B3:B5) สว่ นเซลล์ D11 หายอดรวมของผลคณู ไวโ้ ดยใชส้ ตู ร =SUM(C3:F5) (โปรดสงั เกตเฉพาะตําแหน่งอา้ งองิ ในสตู ร โดยไมต่ อ้ งสนใจตวั เลขผลลพั ธ)์เมอ่ื ยา้ ยเซลล์ B5:F5 ซง่ึ เป็ นขอบตารางดา้ นลา่ งออกไปวางไวท้ ี่ Row 7 ในแนว Column เดมิสว่ นขอบตารางดา้ นขวาจากเซลล์ F2:F4 ใหย้ า้ ยไปวางไวท้ เ่ี ซลล์ H3:H5 นอกแนวเดมิ จะพบวา่ การยา้ ยขอบตารางดา้ นลา่ งตามแนวเดมิ ทําใหต้ ําแหน่งอา้ งองิ ในสตู รทอ่ี ยใู่ นเซลล์ D10และ D11 เปลย่ี นตําแหน่งตามไดอ้ ยา่ งถกู ตอ้ ง สว่ นการยา้ ยขอบตารางดา้ นขวาออกไปนอกแนวไมไ่ ดส้ ง่ ผลทําใหส้ ตู รหายอดรวมตวั เลขจากหวั ตารางดา้ นบนในเซลล์ D9 เปลย่ี นไปแตอ่ ยา่ งใด สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 19 of 147  แตเ่ มอ่ื ยา้ ยเซลลก์ ลบั มาตําแหน่งเดมิ พบวา่ ตําแหน่งอา้ งองิ ในเซลล์ D10 และ D11 ไมย่ อมยา้ ยกลบั มาทเี่ ดมิ แตจ่ ะเกนิ ไป 1 เซลล์ (ตารางเดมิ มขี อบเขตดา้ นลา่ งถงึ Row 5 แตต่ ําแหน่งอา้ งองิ เมอ่ื ยา้ ยกลบั จะมขี อบเขตถงึ Row 6) สว่ นสตู รในเซลล์ D9 แสดงถกู ตอ้ งตามเดมิดงั นัน้ แทนทจี่ ะใชว้ ธิ ี Move เซลลข์ อบตารางไปทอี่ นื่ เพอ่ื ปรับขนาดตารางพนื้ ทเี่ ดมิ ทก่ี ําหนดไว ้ในสตู ร ขอแนะนําใหใ้ ชว้ ธิ ี Insert หรอื Delete กบั Row หรอื Column ซง่ึ จะชว่ ยใหต้ ําแหน่งอา้ งองิ ในสตู รปรับตามการขยายหรอื ลดขนาดไดถ้ กู ตอ้ งตามตอ้ งการเสมอเคล็ดการเลอื กพน้ื ทต่ี ารางตารางขอ้ มลู ขนาดเล็กทใี่ ชก้ นั อยใู่ นวันน้ี จะเรมิ่ มขี นาดใหญโ่ ตขน้ึ ไปเรอ่ื ยๆในวนั ถดั ไป จากเดมิทใ่ี ช ้ Mouse คลกิ เลอื กพนื้ ทต่ี ารางไดง้ า่ ยๆ พอตารางใหญข่ น้ึ เพยี งเล็กนอ้ ยจนขอบตารางยน่ืพน้ ขอบจอภาพ คณุ จะพบวา่ กวา่ จะใช ้ Mouse คลกิ เลอื กพนื้ ทต่ี ารางไดค้ รบทกุ เซลลก์ ต็ อ้ งเสยี เวลาลากไปลากมา หรอื ถา้ ใช ้ Mouse ลากขา้ มจอเร็วเกนิ ไปกจ็ ะหลดุ ออกนอกตารางไปอยู่ตําแหน่งไหนก็ไมร่ ู ้ ยง่ิ คอมพวิ เตอรข์ องคณุ เป็ นเครอื่ ง Notebook ทไี่ มม่ ี Mouse กย็ งิ่ ตอ้ งงอนว้ิเกร็งมอื ปลํ้ากบั พนื้ ทบี่ นหนา้ จอหลายพักทเี ดยี วกวา่ จะเลอื กพนื้ ทไี่ ดต้ ามตอ้ งการไมว่ า่ คณุ จะใชเ้ ครอ่ื งคอมพวิ เตอรแ์ บบใด จะมี Mouse หรอื ไมก่ ็ตาม ขอใหพ้ ยายามฝึกใชม้ อืของคณุ ใหค้ ลอ่ ง หากมอื กําลงั แตะแป้นพมิ พอ์ ยเู่ พราะเพง่ิ พมิ พข์ อ้ มลู เสร็จ ก็ขอใหฝ้ ึกใช ้แป้นพมิ พใ์ นการเลอื กพน้ื ทตี่ ารางตอ่ ไปเลย แตถ่ า้ มอื กําลงั จับ Mouse อยกู่ ็ขอใหใ้ ช ้ Mouseตอ่ ไปไดท้ นั ที หรอื ถา้ มอื ซา้ ยกําลงั แตะแป้นพมิ พอ์ ยแู่ ลว้ มอื ขวากําลงั จับ Mouse อยู่ กข็ อใหใ้ ช ้2 มอื รว่ มกนั ไดใ้ นทันที ไมใ่ ชว่ า่ ตอ้ งเสยี เวลายา้ ยมอื จากแป้นพมิ พไ์ ปจับ Mouse เพราะใชเ้ ป็ นแต่ Mouseสําหรับคนทช่ี อบฝึกสมาธิ การใชค้ อมพวิ เตอรน์ แี่ หละจะชว่ ยในการฝึกสตไิ ดเ้ ป็ นอยา่ งดี เพราะเราตอ้ งคอยตามรทู ้ งั้ กายและใจและใชก้ ายใจประสานกนั ใหด้ ใี นการเลอื กใชแ้ ป้นพมิ พห์ รอืMouse เพอ่ื นํามาใชง้ านใหเ้ หมาะสม สว่ นคนทค่ี ดิ วา่ ตวั เองมสี มาธมิ สี ตดิ แี ลว้ ขอใหค้ อยดมู อืของตวั เองใหด้ วี า่ พอใชท้ งั้ 2 มอื ชว่ ยพมิ พอ์ ะไรเสร็จลงไปแลว้ มอื ขวาของคณุ ยา้ ยไปแบบไมม่ ี สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 20 of 147  สตไิ ปควา้ Mouse อยเู่ รอื่ ยเลยหรอื ไม่ ถา้ มอื ของคณุ ดเู หมอื นวา่ มชี วี ติ จติ ใจของมนั เอง ชอบเลอ่ื นไปจับ Mouse เองทกุ ครัง้ จนเหมอื นวา่ มอื จะขาด Mouse ไปไมไ่ ด ้ แสดงวา่ ยงั ขาดสติ ไม่สามารถตามรอู ้ าการของกายใจไดท้ นัป่ มุ บนแป้ นพมิ พท์ ใ่ี ชใ้ นการเลอื กพน้ื ทตี่ ารางการใชแ้ ป้นพมิ พล์ ดั มมี ากมายหลายวธิ หี ลายป่ มุ บางทตี อ้ งกด 2 ป่ มุ หรอื 3 ป่ มุ พรอ้ มกนั บางทีตอ้ งกดไลก่ นั ไปทลี ะป่ มุ ทําใหต้ อ้ งจดจํากนั วนุ่ วาย โดยหารไู ้ มว่ า่ หากคณุ เขา้ ใจหนา้ ทขี่ องแตล่ ะป่ มุ ดพี อแลว้ เมอ่ื นําไปใชร้ ว่ มกนั ก็จะทํางานอยา่ งทมี่ นั ถกู สรา้ งมาใหท้ ําน่ันเอง กลา่ วคอื  ป่ มุ Ctrl ใหจ้ ําวา่ ป่ มุ นท้ี ําหนา้ ที่ ทําซํา้ หรอื จําวา่ Ctrl แปลวา่ ซาํ้  ป่ มุ Shift แปลวา่ ตรงึ หมายถงึ ทําหนา้ ทยี่ ดึ ตําแหน่งปัจจบุ ันเอาไวไ้ มใ่ หเ้ ลอื่ นตาม  ป่ มุ End แปลวา่ สดุ หมายถงึ ทําหนา้ ทย่ี า้ ยตําแหน่งเซลลไ์ ปจนสดุ ทาง  ป่ มุ ลกู ศรชไี้ ปทางไหน ก็ยา้ ยไปทางนัน้  ป่ มุ * หรอื เครอื่ งหมายคณู แปลวา่ อะไรกไ็ ด ้  ป่ มุ . หรอื เครอ่ื งหมายจดุ ทศนยิ ม แปลวา่ ถงึจากนัน้ เรามาทดลองกบั ตวั อยา่ งตารางทม่ี ตี วั เลข 1 – 50 บนั ทกึ อยใู่ นเซลล์ B2:F11 โดยเรมิ่จากสมมตวิ า่ คณุ กําลงั เลอื กเซลลใ์ ดก็ไดใ้ นตารางตวั เลขน้ี แลว้ กดป่ มุ ตามลําดบั ตอ่ ไปน้ี1. กดป่ มุ Ctrl+* (เครอื่ งหมายบวก หมายถงึ ใหก้ ดป่ มุ Ctrl พรอ้ มกบั ป่ มุ *) จะทําหนา้ ทเ่ี ลอื ก พนื้ ทต่ี งั้ แตเ่ ซลล์ B2:B11 เนอื่ งจากป่ มุ Ctrl แปลวา่ ซ้ํา และป่ มุ * แปลวา่ อะไรกไ็ ด ้ ดงั นัน้ เมอื่ กด 2 ป่ มุ นพี้ รอ้ มกนั จงึ ทําหนา้ ทเี่ ลอื กเซลลอ์ ะไรกไ็ ดท้ ตี่ ดิ ตอ่ กนั ซํา้ ไปตลอด ซงึ่ พน้ื ที่ แบบนเ้ี รยี กวา่ Current Region (ทําไดอ้ กี วธิ หี นงึ่ โดยการกดป่ มุ F5 > Special > กาชอ่ ง Current Region) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 21 of 147  2. ตอ่ จากขนั้ แรกเมอ่ื เลอื กพนื้ ท่ี Current Region ไดแ้ ลว้ ใหส้ งั เกตวา่ เซลล์ B2 ทม่ี เี ลข 1 จะ เป็ นเซลลซ์ งึ่ มสี พี น้ื สวา่ งทสี่ ดุ แสดงตําแหน่งของ Active Cell (ซง่ึ ความหมายของ Active นจ้ี ะมเี พยี งหนง่ึ เดยี วในกลมุ่ ของมนั เชน่ ในชที หนงึ่ ซง่ึ มหี ลายเซลลก์ จ็ ะมี Active Cell เพยี งเซลลเ์ ดยี ว หรอื ในแฟ้มหนง่ึ ซง่ึ มหี ลายชที ก็มเี พยี งชที เดยี วทเ่ี ป็ น Active Sheet)3. กดป่ มุ Ctrl+. แปลตามตวั ป่ มุ วา่ ซํา้ ถงึ หรอื ทําหนา้ ทห่ี าขอบเขตหวั มมุ ตารางวา่ เรมิ่ จาก ไหนถงึ ไหน ซง่ึ เมอ่ื กด 2 ป่ มุ นซี้ ํา้ ตอ่ ไปเรอื่ ยๆจะพบวา่ Excel จะยา้ ยตําแหน่ง Active Cell จากเซลลเ์ ลข 1  5  50  46  1 วนกลบั ทเี่ ดมิ ซ้าํ ตอ่ ไปเรอ่ื ยๆ4. ใหท้ ดลองกดป่ มุ Ctrl+. เพอ่ื เลอื ก Active Cell ใหก้ ลบั มาเป็ นเซลลเ์ ลข 1 ซง่ึ อยตู่ รงหวั มมุ ซา้ ยบนสดุ ของตาราง5. จากนัน้ กดป่ มุ Shift คา้ งไวเ้ พอ่ื ตรงึ เซลลเ์ ลข 1 ไวไ้ มใ่ หข้ ยบั แลว้ กดป่ มุ ลกู ศรซา้ ยขวาบน ลา่ งดจู ะพบวา่ ขอบเซลลด์ า้ นขวาและดา้ นลา่ งเทา่ นัน้ ทจ่ี ะขยบั ตามทศิ ของการกดป่ มุ ลกู ศร ซง่ึ เป็ นวธิ ขี ยายหรอื ลดขนาดพน้ื ทตี่ ารางทต่ี อ้ งการเลอื กนั่นเอง (จําไวว้ า่ หากตอ้ งการขยาย หรอื ลดขอบดา้ นใด ใหย้ า้ ย Active Cell ไปไวฝ้ ่ังตรงขา้ มกอ่ น จากนัน้ จงึ กดป่ มุ Shift+ป่ มุ ลกู ศร ซง่ึ จะขยบั ขอบตารางอกี ฝั่งหนงึ่ ของ Active Cell)จากนัน้ มาทดลองเลอื กเฉพาะแนวใดแนวหนง่ึ ของตารางกนั บา้ ง โดยใหเ้ รม่ิ จากคลกิ เซลลเ์ ลข 1ไวเ้ ซลลเ์ ดยี วกอ่ น แลว้ กดป่ มุ Ctrl+Shift+ลกู ศร จะเลอื กพนื้ ทจี่ ากเซลลเ์ ดมิ ทเี่ ลอื กไวไ้ ปจนสดุ ตารางตามทศิ ทางของลกู ศร เชน่ เดมิ อยทู่ เี่ ซลลเ์ ลข 1 ถา้ กดป่ มุ Ctrl+Shift+ลกู ศรขวา จะเลอื กเซลลเ์ ลข 1 – 5 (อาจใชก้ ารกดป่ มุ อกี วธิ หี นงึ่ โดยเรม่ิ จากกดป่ มุ Shift คา้ งไวแ้ ลว้ กดป่ มุEnd ครัง้ หนงึ่ แลว้ กดป่ มุ ลกู ศรขวาตามอกี ครัง้ กไ็ ด ้ ขอใหเ้ ลอื กใชต้ ามถนัด)วธิ ใี ช้ Mouse เลอื กพน้ื ทต่ี ารางโดยทว่ั ไปเรามกั ทราบกนั แตว่ า่ ใหค้ ลกิ แลว้ ลากเพอื่ เลอื กพนื้ ทตี่ าราง ซง่ึ กวา่ จะเลอื กไดต้ รงตําแหน่งครบทงั้ ตารางก็ตอ้ งลากแลว้ ลากอกี ดงั นัน้ หากมอื ขวาของคณุ อยทู่ ่ี Mouse อยแู่ ลว้ ให ้ใชม้ อื ซา้ ยชว่ ยโดยการกดป่ มุ Shift เพอ่ื ตรงึ เซลลท์ เี่ ลอื กนัน้ ไวก้ อ่ นจากนัน้ จงึ ใช ้ Mouse ขยบัจอแลว้ คลกิ เลอื กเซลลห์ วั มมุ ปลายทาง เชน่ เดมิ เลอื กเซลลเ์ ลข 1 เอาไว ้ กใ็ หก้ ดป่ มุ Shift แลว้คลกิ เซลลเ์ ลข 50 ไดเ้ ลยเพอ่ื เลอื กตารางตงั้ แตเ่ ลข 1 – 50 พรอ้ มกนัสว่ นการเลอื กพนื้ ทตี่ ารางตามแนวนอนหรอื แนวตงั้ ใหใ้ ชว้ ธิ ี Double คลกิ ทขี่ อบเซลลร์ ว่ มกบัการกดป่ มุ Shift เชน่ ถา้ ตอ้ งการเลอื กเซลลเ์ ลข 1 – 5 ใหเ้ รม่ิ จากเลอื กเซลลเ์ ลข 1 แลว้ กดป่ มุ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 22 of 147  Shift คา้ งไว ้ จากนัน้ ใหใ้ ช ้ Mouse ชที้ ขี่ อบเซลลด์ า้ นขวา (จะพบวา่ Cursor เปลยี่ นเป็ นเครอ่ื งหมายลกู ศร ) แลว้ ให ้ Double คลกิ ทดี่ า้ นขวาขอบเซลล์ เพอื่ บอก Excel วา่ตอ้ งการกระโดดไปตามทศิ ของขอบเซลลน์ ั่นเองอกี วธิ หี นงึ่ แทนทจี่ ะตอ้ งเสยี เวลากดป่ มุ Shift คา้ งไวต้ ลอด ใหเ้ รม่ิ จากคลกิ เซลลเ์ ลข 1 ไวก้ อ่ นจากนัน้ ใหก้ ดป่ มุ F8 ซง่ึ จะพบวา่ ตรงขอบหนา้ จอ Excel ดา้ นขวาลา่ งสดุ จะมคี ําวา่ Ext (ยอ่ จากExtend แปลวา่ ยดื ) แสดงขนึ้ มา แลว้ จงึ คลกิ เลอื กเซลลป์ ลายทาง จากนัน้ ใหก้ ดป่ มุ F8 อกี ครัง้หนง่ึ เพอ่ื ลา้ งคําวา่ Ext ออกไปกอ่ นแลว้ จงึ ทํางานตอ่ ไปไดต้ ามเดมิการแบง่ ขอบเขตพน้ื ทต่ี ารางในชที ของ Excel 2007 เป็ นตน้ มามพี น้ื ทต่ี ารางใหญก่ วา่ รนุ่ กอ่ นอยา่ งมาก จากเดมิ มเี พยี ง65,536 row และ 256 column ไดป้ รับเพมิ่ เป็ น 1,048,576 row และ 16,384 column ซงึ่ ถา้คณุ พมิ พค์ า่ ลงไปแลว้ พยายาม paste ใหค้ รบทกุ เซลลใ์ นชที จะพบวา่ Excel ตอ้ งใชเ้ วลานานแสนนานทเี ดยี วกวา่ จะไดข้ อ้ มลู เต็มทงั้ ชที และเมอื่ สง่ั save จะไมม่ ี hard disk เครอื่ งไหนที่สามารถเก็บแฟ้มขอ้ มลู นัน้ ได ้ เพราะแฟ้มจะมขี นาดใหญเ่ กนิ กวา่ จะรับได ้ (ขออยา่ พยายามทดลองทําตามนเี้ ด็ดขาด เพราะคณุ จะรอไมไ่ หวแลว้ ตอ้ งหนั ไปปิดเครอื่ งคอมพวิ เตอรไ์ ปกอ่ นที่จะ save เสร็จ)สาเหตทุ ่ี Excel ขยายขนาดตารางใหใ้ หญข่ นึ้ จนดเู หมอื นใหญก่ วา่ ความจําเป็ น มไิ ดม้ งุ่ ใหค้ ณุจําเป็ นตอ้ งใชใ้ หค้ รบทกุ เซลล์ เพยี งแตจ่ ะชว่ ยใหส้ ามารถรับขอ้ มลู จากโปรแกรมอน่ื เขา้ มาเกบ็ ไว ้ในชที ไดส้ ะดวกขน้ึ และเมอื่ คณุ หนั มาใช ้ Excel 2007 แลว้ ก็มไิ ดห้ มายความวา่ จากนไี้ ปคณุ ไม่จําเป็ นตอ้ งใชโ้ ปรแกรมฐานขอ้ มลู อนื่ อกี ตอ่ ไป เพราะในทส่ี ดุ แลว้ เมอ่ื ขอ้ มลู มปี รมิ าณมากขน้ึ และมคี วามสมั พันธใ์ นตวั ฐานขอ้ มลู ซบั ซอ้ นตามขนึ้ ไปแลว้ โปรแกรมทสี่ รา้ งขนึ้ มาเพอ่ื จดั การฐานขอ้ มลู โดยเฉพาะยอ่ มเหมาะกวา่ อยดู่ ีอยา่ วา่ แตข่ นาดของตารางทมี่ ขี นาดใหญส่ ามารถรับขอ้ มลู ไดม้ ากขน้ึ นเี้ ลย ตอ่ ใหใ้ ช ้ Excel 2003ทมี่ ขี นาดตารางเล็กกวา่ หากผใู ้ ช ้ Excel ไมเ่ ป็ น พอนําขอ้ มลู มาเก็บไวใ้ นชที อยา่ งไมม่ หี ลกั การแลว้ เมอ่ื ถงึ เวลาจะตอ้ งแยกขอ้ มลู ออกจากกนั เป็ นสว่ นๆกจ็ ะทําไดย้ าก จงึ ขอเสนอใหใ้ ชห้ ลกัICO แบง่ ขอบเขตพนื้ ทตี่ าราง กลา่ วคอื1. ใหแ้ บง่ แยกตารางขอ้ มลู ออกเป็ น 3 ประเภท คอื ตาราง Input, ตาราง Calculate, และ ตาราง Output (หรอื ใชต้ วั ยอ่ วา่ ICO)2. ตาราง Input คอื ตารางทใี่ ชเ้ กบ็ ขอ้ มลู ทพ่ี มิ พล์ งไปหรอื เป็ นขอ้ มลู ทรี่ ับมาจากโปรแกรม อนื่ หรอื อกี นัยหนง่ึ เป็ นตารางฐานขอ้ มลู น่ันเอง ตาราง Input นต้ี อ้ งออกแบบใหโ้ ครงสรา้ ง ของตารางมลี กั ษณะดงั นี้ 2.1. หวั ตารางดา้ นบน ตอ้ งเป็ น row เดยี ว ซง่ึ อาจแตง่ ขอ้ ความในเซลลใ์ หแ้ สดงเป็ นหลาย บรรทดั ได ้ โดยกดป่ มุ Alt+Enter เพอ่ื ขนึ้ ขอ้ ความในบรรทดั ใหมใ่ นเซลลเ์ ดมิ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 23 of 147   2.2. ตอ้ งบนั ทกึ ขอ้ มลู ใน column ใด column หนงึ่ ตดิ ตอ่ กนั ไปตลอด หา้ มเวน้ ชอ่ งวา่ ง ซง่ึ โดยทวั่ ไป column ซา้ ยสดุ มกั จะมขี อ้ มลู ตดิ ตอ่ กนั ไปอยแู่ ลว้ เนอื่ งจากเป็ นขอ้ มลู ของ เลขทรี่ ายการ หรอื ขอ้ มลู รหสั หรอื ขอ้ มลู วนั ทซี่ งึ่ เรยี งไปตามลําดบั รายการทเ่ี กดิ ขนึ้ 2.3. ตอ้ งเวน้ ขอบตารางไมว่ า่ จะเป็ นขอบดา้ นบนลา่ งซา้ ยขวาอยา่ งนอ้ ยขา้ งละ 1 เซลล์ หา้ ม นําตาราง Input ไปตดิ กบั เซลลข์ อ้ มลู อนื่ ซงึ่ ไมใ่ ชข่ อ้ มลู เรอื่ งเดยี วกบั ตารางขอ้ มลู นัน้3. ตาราง Calculate คอื ตารางทใ่ี ชส้ รา้ งสตู รคํานวณ ไมจ่ ําเป็ นตอ้ งมโี ครงสรา้ งตามแบบ ของตาราง Input แตใ่ นตาราง Calculate นคี้ ณุ ตอ้ งหาทางสรา้ งสตู รทเี่ ซลลห์ วั มมุ เป็ นสตู ร ฉลาดทสี่ ามารถปรับตําแหน่งอา้ งองิ ไดเ้ อง และในขณะทส่ี รา้ งสตู รตอ้ งใชป้ ่ มุ F4 เพอื่ ใส่ เครอ่ื งหมาย $ เพอื่ ควบคมุ ตําแหน่งอา้ งองิ ในสตู รไปยงั เซลลท์ บี่ นั ทกึ คา่ ตัวแปรทใ่ี ชค้ ํานวณ ไว ้ ซงึ่ โดยทวั่ ไปเซลลค์ า่ ตวั แปรจะวางไวต้ ามแนวคขู่ นานกบั แนวของตาราง Calculate เพอ่ื ชว่ ยใหเ้ ครอื่ งหมาย $ ทใี่ สไ่ วใ้ นสตู ร ทําหนา้ ทค่ี วบคมุ ตําแหน่งของแนวของเซลลต์ วั แปรไว ้ เพอื่ นําคา่ มาใชค้ ํานวณน่ันเอง4. ตาราง Output คอื ตารางแสดงผลลพั ธท์ ตี่ อ้ งการ โดยตารางนจ้ี ะออกแบบใหม้ หี นา้ ตา อยา่ งไรกไ็ ดต้ ามแตว่ า่ จะนําไปแสดงผลบนจอภาพหรอื ในหนา้ กระดาษทส่ี งั่ พมิ พ์สาเหตทุ ต่ี อ้ งใชห้ ลกั ICO แยกขอ้ มลู แตล่ ะประเภทออกจากกนั เพอ่ื ชว่ ยทําใหเ้ ราสามารถใช ้Excel รองรับกบั ขอ้ มลู ทม่ี ปี รมิ าณมากขนึ้ ไปเรอ่ื ยๆไดต้ ลอด ซงึ่ ในชว่ งแรกทยี่ งั มขี อ้ มลู ไมม่ ากนัก อาจใชช้ ที เดยี วเกบ็ ขอ้ มลู ทงั้ I C และ O ไวด้ ว้ ยกนั จากนัน้ เมอื่ มปี รมิ าณขอ้ มลู มากขน้ึ หรอืขอ้ มลู มคี วามสลบั ซบั ซอ้ นมากขน้ึ จงึ คอ่ ยยา้ ยตารางแยกไปเก็บในชที อนื่ หรอื แฟ้มอน่ื ไดท้ นั ทีโดยไมก่ ระทบกบั สตู รคํานวณทส่ี รา้ งไว ้ขอ้ ผดิ พลาดทพ่ี บเห็นบอ่ ยครัง้ เกดิ จากการออกแบบตารางตามแบบรายงานทต่ี อ้ งพมิ พ์ เชน่หากมรี ายงานยาว 100 หนา้ ก็มกั ออกแบบตารางใน Excel ใหย้ าวเหยยี ดตอ่ ๆกนั ลงมาทงั้ 100หนา้ แลว้ จงึ สง่ั พมิ พอ์ อกไปทลี ะหนา้ ซง่ึ วธิ นี ท้ี ําใหแ้ ฟ้มมขี นาดใหญแ่ ละยากตอ่ การแกไ้ ขเพราะจําเป็ นตอ้ งยอ้ นกลบั ไปไลแ่ กไ้ ขในตารางแตล่ ะหนา้แตห่ ากออกแบบตารางแบบ ICO เป็ น ใหเ้ รม่ิ จากแยกตารางขอ้ มลู ทเ่ี ป็ น Input ออกไป แลว้ ใช ้สตู รดงึ คา่ จากตาราง Input มาคํานวณในตาราง Calculate จากนัน้ จงึ ดงึ ผลลพั ธท์ ค่ี ํานวณเสร็จแลว้ ไปสรา้ งตาราง Output ซง่ึ จดุ สําคญั อยทู่ ตี่ าราง Output นแี่ หละ โดยเราตอ้ งหาทางสรา้ งตาราง Output ไวเ้ พยี งหนา้ เดยี ว จากนัน้ ตอ้ งสรา้ งสตู รดงึ ขอ้ มลู แตล่ ะหนา้ มาแสดงในตารางOutput หนา้ เดยี วนใี้ หไ้ ด ้ ชว่ ยใหแ้ ฟ้มมขี นาดเล็กและแกไ้ ขไดง้ า่ ยกวา่ มากสมมตวิ า่ หนา้ กระดาษทพ่ี มิ พม์ ี 30 บรรทดั ตอ่ หนา้ ดงั นัน้ พอพมิ พเ์ ลขหนา้ ที่ 1 ลงไป ตอ้ งสรา้ งสตู รดงึ ผลลพั ธบ์ รรทดั ท่ี 1 - 30 มาแสดงในหนา้ น้ี จากนัน้ พอเปลย่ี นเลขหนา้ เป็ นเลข 2 กจ็ ะได ้บรรทดั ที่ 31 – 60 มาแสดง พอเป็ นหนา้ 3 ก็ไดบ้ รรทดั ท่ี 61 - 90 มาแสดง ซง่ึ สตู รทด่ี งึ ขอ้ มลูเชน่ นไี้ ดก้ ็คอื สตู ร VLookup, Match, Index เป็ นตน้ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 24 of 147  การจดั เตรยี มพน้ื ท่ี ICO เพอ่ื รบั ขอ้ มลู และป้ องกนั การแกไ้ ขแฟ้มทสี่ รา้ งเสร็จพรอ้ มใชง้ าน ตอ้ งมรี ะบบแยกแยะตําแหน่งตารางทเ่ี ปิดใหร้ ับคา่ ใหมไ่ ดพ้ รอ้ มทงั้หาทางป้องกนั ตารางทไี่ มต่ อ้ งการใหใ้ ครเขา้ ไปแกไ้ ข โดยตอ้ งพจิ ารณาจากพน้ื ทต่ี ารางแตล่ ะสว่ น กลา่ วคอื1. ตาราง Input เป็ นพน้ื ทส่ี ว่ นทเ่ี ปิดใหผ้ ใู ้ ชง้ านสามารถพมิ พค์ า่ ใหมล่ งไปได ้ โดยมขี นั้ ตอน ในการจัดเตรยี มพนื้ ทดี่ งั นี้ 1.1. คลกิ เลอื กพน้ื ทต่ี ารางสว่ นทเี่ ป็ น Input (หากมหี ลายพน้ื ทไี่ มต่ ดิ ตอ่ กนั ใหก้ ดป่ มุ Ctrl คา้ งไวก้ อ่ นแลว้ คลกิ เลอื กพนื้ ทแ่ี ตล่ ะสว่ น แลว้ อาจกําหนดสี Font ใหแ้ ตกตา่ งจากสว่ น อนื่ ซง่ึ ผมเองชอบใชส้ ชี มพเู พอื่ บอกผใู ้ ชว้ า่ เป็ นเซลล์ Input) 1.2. คลกิ ขวา สงั่ Format Cells > Protection > แลว้ ตดั กาชอ่ ง Locked ทงิ้ ไป 1.3. สง่ั Review > Protect Sheet (Excel 2003 ใชค้ ําสงั่ Tools > Protection > Protect Sheet) 1.4. หลงั จากท่ี Protect Sheet เสร็จ เมอ่ื พมิ พข์ อ้ มลู ใหมล่ งไปในเซลลท์ เ่ี ป็ น Input แลว้ ใหก้ ดป่ มุ Tab ซง่ึ จะพบวา่ Excel จะกระโดดไปเลอื กเซลล์ Input ตอ่ ไปใหเ้ อง2. ตาราง Calculate และตาราง Output เป็ นพนื้ ทซี่ ง่ึ มเี ซลลส์ ว่ นใหญเ่ ป็ นสตู รซง่ึ เมอื่ สรา้ ง เสร็จแลว้ ควรป้องกนั ไมใ่ หใ้ ครเขา้ ไปดสู ตู ร แกไ้ ขสตู ร หรอื ลบสตู รทง้ิ ไดอ้ กี โดยมขี นั้ ตอน ในการจดั เตรยี มพนื้ ทด่ี งั นี้ (ถา้ ชที ถกู Protect อยตู่ อ้ ง Unprotect Sheet กอ่ น) 2.1. คลกิ เลอื กพน้ื ทตี่ ารางสว่ นทเ่ี ป็ นตาราง Calculate และตาราง Output 2.2. คลกิ ขวา สง่ั Format Cells > Protection > แลว้ กาชอ่ ง Hidden เพอ่ื ซอ่ นการ แสดงสตู รทจี่ ะเห็นบน Formula Bar และยงั ป้องกนั ไมใ่ ห ้ Copy สตู รออกไปไดด้ ว้ ย โดยจะ Copy เฉพาะคา่ ผลลพั ธอ์ อกไปไดเ้ ทา่ นัน้ (หา้ มตดั กาชอ่ ง Locked ทงิ้ อยา่ ง เด็ดขาด) และตอ้ งทําขนั้ ตอนตอ่ ไปกอ่ นดว้ ยจงึ จะเห็นผล 2.3. สง่ั Review > Protect Sheet (Excel 2003 ใชค้ ําสง่ั Tools > Protection > Protect Sheet) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 25 of 147  เมอื่ ใดจะแยกชที เมอื่ ใดจะแยกแฟ้ มสมมตวิ า่ คณุ เกบ็ ขอ้ มลู ไวจ้ นเต็มชที ถา้ เกบ็ ขอ้ มลู ไวจ้ นถงึ row สดุ ทา้ ยในชที แลว้ ชว่ ยคดิ กนัหน่อยวา่ ขอ้ มลู รายการถดั ไปนัน้ จะนําไปเกบ็ ไวท้ ไ่ี หน จะ Insert ชที ใหม่ หรอื จะเปิดแฟ้มใหม่เพอ่ื บนั ทกึ รายการตอ่ ไปถา้ คณุ ตอบวา่ ตอ้ งขน้ึ ชที ใหมเ่ พอื่ บนั ทกึ รายการถดั ไปขอถามกลบั วา่ แลว้ ชที ใหมน่ ัน้ ยงั คงอยใู่ นแฟ้มเดมิ ใชไ่ หม ถา้ ทําแบบนย้ี อ่ มทําใหแ้ ฟ้มของคณุ มีขนาดแฟ้มใหญข่ นึ้ ไปเรอื่ ยๆ แลว้ คณุ เคยจําเป็ นตอ้ งใชข้ อ้ มลู ทอ่ี ตุ สา่ หเ์ กบ็ ไวน้ ัน้ ทกุ รายการพรอ้ มกนั บา้ งไหมผมเชอ่ื วา่ นอ้ ยคนนักทมี่ โี อกาสใชข้ อ้ มลู พรอ้ มกนั ทกุ รายการ ทงั้ 65,536 row ในชที ของ Excel2003 หรอื ทกุ row ในชที ทม่ี จี ํานวน row มากกวา่ นัน้ ใน Excel 2007 แสดงวา่ ขอ้ มลู ทเี่ ก็บไว ้กลายเป็ นภาระทตี่ อ้ งแบกเอาไวเ้ รอื่ ยไป พอแฟ้มใหญข่ น้ึ จะสง่ ผลให ้ Excel ทํางานชา้ ลง แลว้พอเครอื่ งคอมพวิ เตอรท์ ํางานชา้ ลงจนเป็ นเตา่ คณุ ตอ้ งวงิ่ ไปหาหวั หนา้ เพอ่ื ของบซอื้ เครอ่ื งคอมพวิ เตอรใ์ หมห่ รอื เปลา่ ละ่ ถา้ เป็ นเชน่ นี้ ผมรับรองวา่ คณุ ตอ้ งหาซอื้ คอมพวิ เตอรใ์ หมอ่ ยบู่ อ่ ยๆทเี ดยี ว ซง่ึ ถา้ คณุ ใช ้ Excel เป็ น ขอใหย้ ดึ หลกั ในการแยกชที หรอื แยกแฟ้มดงั ตอ่ ไปนแ้ี ลว้ รับรองวา่ คณุ จะสามารถใชเ้ ครอื่ งคอมพวิ เตอรข์ องคณุ ตอ่ ไปไดอ้ กี นาน ไมว่ า่ ปรมิ าณขอ้ มลู จากมากขน้ึเพยี งใด ขอ้ มลู ใดทต่ี อ้ งคํานวณพรอ้ มกนั ใหเ้ ก็บไวใ้ นแฟ้มเดยี วกนั โดยจะแยกชที แตล่ ะตาราง หรอื ไมก่ ไ็ ด ้ สว่ นขอ้ มลู ใดทไ่ี มต่ อ้ งคํานวณพรอ้ มกนั หรอื เลอื กคํานวณได ้ ใหแ้ ยกเก็บไว ้ ในแฟ้มอนื่ แลว้ สรา้ งสตู ร Link เพอ่ื นําขอ้ มลู มาใชเ้ มอ่ื ตอ้ งการยกตวั อยา่ งเชน่ ในงบกําไรขาดทนุ อาจแบง่ ตารางคา่ ใชจ้ า่ ยออกมาเป็ นคา่ ใชจ้ า่ ยคงทกี่ บัคา่ ใชจ้ า่ ยผันแปร ซงึ่ หากคณุ นําตารางคํานวณคา่ ใชจ้ า่ ยคงทม่ี ารวมไวใ้ นแฟ้มเดยี วกบั ตารางคา่ ใชจ้ า่ ยผันแปร ยอ่ มทําใหแ้ ฟ้มนัน้ มขี นาดใหญแ่ ละตอ้ งเสยี เวลาคํานวณทไี่ มจ่ ําเป็ นทกุ ครัง้ ทําใหค้ ํานวณชา้ ลง แตถ่ า้ แยกตารางคํานวณคา่ ใชจ้ า่ ยคงทอี่ อกไปเป็ นแฟ้มตา่ งหาก เพอื่ ทําให ้แฟ้มนค้ี ํานวณแยกจากสตู รอน่ื แลว้ พอไดผ้ ลลพั ธเ์ ป็ นยอดคา่ ใชจ้ า่ ยคงทแี่ ลว้ จงึ Link ผลลพั ธท์ ี่ไดม้ าแสดงในแฟ้มงบกําไรขาดทนุ โดยอาจรวมตารางคํานวณคา่ ใชจ้ า่ ยผันแปรไวใ้ นแฟ้มงบหรอื แยกเป็ นแฟ้มตา่ งหาก แลว้ ดงึ ผลลพั ธม์ าแสดงในงบเชน่ เดยี วกบั คา่ ใชจ้ า่ ยคงทก่ี ไ็ ด ้การแยกแฟ้มเพอื่ ใหค้ ํานวณแตล่ ะเรอ่ื งแยกจากกนั นี้ จะทําใหแ้ ฟ้มมขี นาดเล็กลงอยา่ งมาก แต่เราตอ้ งเสยี เวลาเปิดแตล่ ะแฟ้มเพอ่ื ไลค่ ํานวณแตล่ ะขนั้ ใหเ้ สร็จกอ่ น ซงึ่ ถา้ ใช ้ Macro Recorderบนั ทกึ การไลเ่ ปิดปิดแฟ้มตามลําดบั คํานวณ จะชว่ ยทําใหข้ นั้ ตอนนท้ี ํางานตอ่ เนอื่ งกนั ไปโดยอตั โนมตั แิ ละใชเ้ วลาทัง้ หมดเร็วกวา่ การรวมตารางทกุ อยา่ งไวใ้ นแฟ้มเดยี วกนั สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 26 of 147  สําหรับผทู ้ ไี่ มค่ นุ ้ เคยกบั การใชส้ ตู ร link ขา้ มแฟ้มมากอ่ น อาจสงสยั วา่ ทําไมจงึ ตอ้ งไลเ่ ปิดแฟ้มท่ี link กนั ทลี ะคดู่ ว้ ย ทงั้ ๆทตี่ อนทเ่ี ปิดแคแ่ ฟ้มปลายทางขนึ้ มากจ็ ะพบวา่ Excel จดั การ Updatelink ใหท้ นั ทอี ยแู่ ลว้ จงึ ขอเรยี นใหท้ ราบวา่ การเปิดแฟ้มปลายทางทรี่ ับคา่ จากแฟ้มอน่ื ขน้ึ มานัน้ไมไ่ ดท้ ําใหเ้ กดิ การคํานวณในแฟ้มตน้ ทาง แตเ่ ป็ นการดงึ ผลจากการคํานวณทเ่ี กดิ ขนึ้ คราวกอ่ นนัน้ มาใชใ้ นแฟ้มปลายทางเทา่ นัน้ หากตอ้ งการใหแ้ ฟ้มใดคํานวณ เราตอ้ งเปิดแฟ้มคทู่ ี่ link กนันัน้ ขนึ้ มากอ่ นเสมอจงึ จะกระตนุ ้ ใหก้ ารคํานวณเกดิ ขน้ึ มาได ้การกาํ หนดตาํ แหนง่ อา้ งองิ ขา้ มชที หรอื ขา้ มแฟ้ มผมเรมิ่ ตน้ ใชโ้ ปรแกรม Excel ตงั้ แตย่ คุ ทใี่ นแฟ้มหนง่ึ ๆยงั มชี ที ใหใ้ ชง้ านไดเ้ พยี งชที เดยี วและยงัไมส่ ามารถใชส้ ตู รเชอื่ มขอ้ มลู จากแฟ้มอน่ื มาใชง้ านรว่ มกนั ในยคุ นัน้ ตอ้ งออกแบบตารางทงั้ หมดลงไปในชที เดยี ว ตอ่ มา Excel ถกู พัฒนาใหส้ ามารถสง่ ขอ้ มลู จากแฟ้มหนงึ่ ออกไปยงั แฟ้มอน่ื ได ้แตก่ ็ยงั ไมส่ ามารถทําหนา้ ทที่ งั้ รับทงั้ สง่ ขอ้ มลู กลบั ไปกลบั มาระหวา่ งแฟ้มเชน่ ท่ี Excel ในรนุ่ปัจจบุ นั ทําได ้ ซง่ึ ถา้ ดใู หด้ จี ะพบวา่ สงิ่ ที่ Excel สามารถสง่ ขอ้ มลู กลบั ไปกลบั มาไดน้ ัน้ เป็ นเพราะเราถกู Excel หลอกใหด้ เู หมอื นวา่ มหี ลายชที หรอื มหี ลายแฟ้ม...แคน่ ัน้ เอง ขอใหจ้ ําไวว้ า่ ไมว่ า่ จะเป็ นตา่ งชที หรอื ตา่ งแฟ้ม พอเปิดแฟ้มขนึ้ มาใชง้ านพรอ้ มกนั มนั ก็ คอื ชที เดยี วกนั น่ันแหละการทเ่ี ราเห็นวา่ ในแฟ้มหนง่ึ ๆมหี ลายชที นัน้ เป็ นภาพลวงตาทช่ี ว่ ยใหผ้ ใู ้ ชง้ านสามารถแยกเก็บขอ้ มลู ตา่ งเรอื่ งไวใ้ นชที ตา่ งกนั ไป สว่ นขอ้ มลู ท่ี link ขา้ มแฟ้มไดน้ ัน้ ถา้ คณุ ใช ้ Excel รนุ่ แรกๆมากอ่ นอยา่ งผม พอสงั่ Unhide Sheet จะพบวา่ ในแฟ้มปลายทางมชี ที หนง่ึ ถกู ซอ่ นไว ้ โดยชที นใี้ ช ้เกบ็ ขอ้ มลู จากชที ตน้ ทางไวท้ งั้ หมด ดงั นัน้ ขอ้ มลู ทไี่ ดม้ าจากสตู ร link ขา้ มแฟ้ม ถา้ วา่ กนั ให ้ถกู ตอ้ ง ไมไ่ ดเ้ ป็ นผลจากสตู รที่ link มาจากแฟ้มตน้ ทางโดยตรง แตเ่ ป็ นขอ้ มลู ทไ่ี ดม้ าจากชที ท่ีซอ่ นไวภ้ ายในชที ปลายทางในแฟ้มเดยี วกนั กบั แฟ้มทม่ี สี ตู ร link นั่นเอง โดยเรยี กขอ้ มลู ทเี่ กบ็ในชที นว้ี า่ Cache Data ซง่ึ สง่ ผลใหแ้ ฟ้มปลายทางมขี นาดแฟ้มใหญก่ วา่ ปกตติ ามไปดว้ ย(ตอ่ มา Microsoft ไดป้ รับปรงุ ให ้ Excel ซอ่ นชที นไ้ี วโ้ ดยไมส่ ามารถคน้ หาพบเชน่ แตก่ อ่ น)ถา้ คณุ สามารถสรา้ งสตู ร link ขอ้ มลู ภายในชที เดยี วกนั ก็ขอใหใ้ ชว้ ธิ เี ดยี วกนั ในการ link ขอ้ มลูขา้ มชที หรอื แมแ้ ตข่ า้ มแฟ้ม ขอเพยี งอยา่ กลวั ภาพลวงตาทท่ี ําใหเ้ ห็นวา่ เป็ นตา่ งชที หรอื ตา่ งแฟ้มกนั เพราะความจรงิ มนั กค็ อื ชที เดยี วกนั นั่นเอง ซง่ึ กอ่ นทจี่ ะใชว้ ธิ สี รา้ งสตู ร link ทน่ี ยิ มใชก้ นัตอ่ ไปนี้ ขอใหเ้ ปิดแฟ้มทกุ แฟ้มทต่ี อ้ งการสรา้ งสตู ร link ขอ้ มลู ระหวา่ งกนั ขน้ึ มากอ่ นทงั้ แฟ้มตน้ทางและแฟ้มปลายทางแลว้ จะชว่ ยใหส้ รา้ งสตู ร link ไดง้ า่ ยมาก สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 27 of 147  1. วธิ ยี อ้ นปลายกลบั มาหาตน้ 1.1. ใหเ้ รม่ิ จากพมิ พเ์ ครอ่ื งหมายเทา่ กบั = ทเ่ี ซลลป์ ลายทาง 1.2. (หากไมต่ อ้ งการ link ขา้ มชที หรอื ขา้ มแฟ้มใหข้ า้ มขนั้ ตอนน)้ี คลกิ เลอื กชอื่ ชที ตน้ ทาง หรอื คลกิ ชอื่ แฟ้มตน้ ทางทเ่ี ปิดไวก้ อ่ นแลว้ จากคําสง่ั View > Switch Windows (Excel 2003 ใชค้ ําสงั่ Windows เพอื่ ไปทแี่ ฟ้มตน้ ทาง) 1.3. แลว้ คลกิ เลอื กเซลลต์ น้ ทาง 1.4. จากนัน้ กดป่ มุ Enter เพอ่ื รับสตู ร link ตําแหน่งเซลลต์ น้ ทางกลบั ไปบนั ทกึ ไวท้ เ่ี ซลล์ ปลายทาง (วธิ นี โ้ี ดยทวั่ ไปจะทําไดท้ ลี ะเซลล์ เวน้ แตจ่ ะสรา้ งสตู รแบบ Array จงึ จะสรา้ ง พรอ้ มกนั หลายเซลลท์ งั้ ตาราง และขอใหส้ งั เกตวา่ ถา้ เป็ นสตู ร link ขา้ มแฟ้มจะได ้ ตําแหน่งอา้ งองิ แบบ Absolute เสมอ)2. วธิ ี Copy จากตน้ แลว้ มา Paste Special แบบ Paste Link ทป่ี ลาย 2.1. ใหเ้ รมิ่ จากเลอื กพนื้ ทตี่ ารางตน้ ทางทงั้ ตารางพรอ้ มกนั 2.2. สงั่ Copy 2.3. (หากไมต่ อ้ งการ link ขา้ มชที หรอื ขา้ มแฟ้มใหข้ า้ มขนั้ ตอนน)ี้ คลกิ เลอื กชอื่ ชที ปลายทาง หรอื คลกิ ชอ่ื แฟ้มปลายทางทเี่ ปิดไวก้ อ่ นแลว้ จากคําสง่ั View > Switch Windows (Excel 2003 ใชค้ ําสงั่ Windows เพอ่ื ไปทแ่ี ฟ้มปลายทาง) 2.4. จากนัน้ ใหเ้ ลอื กเซลลห์ วั มมุ ตารางปลายทางเพยี งเซลลเ์ ดยี วแลว้ คลกิ ขวา สง่ั Paste Special > กดป่ มุ Paste Link จะไดส้ ตู ร link พรอ้ มกนั ทเี ดยี วทงั้ ตาราง3. วธิ ี Cut จากตน้ แลว้ ไป Paste ทปี่ ลาย โดยวธิ นี ว้ี า่ ไปแลว้ ไมไ่ ดเ้ ป็ นการสรา้ งสตู ร link แตใ่ หเ้ รมิ่ จากสรา้ งสตู ร link ไวใ้ นชที เดยี วกนั ตามวธิ ขี า้ งตน้ ใหเ้ สร็จกอ่ น จากนัน้ จงึ สงั่ Cut ตารางสตู รจากชที ตน้ ทางไป Paste ทต่ี ําแหน่งปลายทางในชที อนื่ หรอื แฟ้มอน่ื ซง่ึ Excel จะปรับโครงสรา้ งตําแหน่งอา้ งองิ ในสตู รใหก้ ลายเป็ นตําแหน่งอา้ งองิ ขา้ มชที หรอื ขา้ มแฟ้มให ้ เองทนั ที สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 28 of 147   วธิ ที ี่ 3 นเี้ ป็ นเคล็ดลบั สําคญั ในการสรา้ งสตู ร link ขา้ มแฟ้ม โดยเรม่ิ จากสรา้ งสตู ร link ทงั้ หมดในชที เดยี วกนั ใหเ้ สร็จกอ่ น จากนัน้ เมอ่ื Cut เซลลส์ ตู รไปทช่ี ที อนื่ จะไดส้ ตู ร link ขา้ มชที หรอื ถา้ Cut เซลลส์ ตู รไปทแี่ ฟ้มอน่ื จะไดส้ ตู ร link ขา้ มแฟ้ม ชว่ ยใหไ้ มต่ อ้ งเสยี แรง เสยี เวลาสรา้ งสตู ร link ขา้ มชที หรอื ขา้ มแฟ้มตงั้ แตแ่ รกแมแ้ ตน่ อ้ ย เชน่  จากเดมิ สรา้ งสตู ร link ในชที เดยี วกนั เป็ น =A1  พอ Cut ไปทช่ี ที อนื่ สตู รนจี้ ะปรับตวั ใหมเ่ ป็ น =Sheet1!A1  พอ Cut ตอ่ ไปทแี่ ฟ้มอนื่ สตู รนจี้ ะปรับตวั ใหมเ่ ป็ น =[Source.xlsx]Sheet1!A1 โดย คําวา่ Source ก็คอื ชอ่ื แฟ้มตน้ ทาง และคําวา่ Sheet1 คอื ชอื่ ชที ตน้ ทาง  ตอ่ มาถา้ ปิดแฟ้มตน้ ทางทช่ี อื่ Source ทง้ิ สตู รนจี้ ะปรับตวั ใหมเ่ ป็ น ='D:\Dummy\[Source.xlsx]Sheet1'!A1 ซง่ึ การทม่ี ชี อ่ื Drive D และชอื่ โฟลเดอรว์ า่ Dummy แสดงไวด้ า้ นหนา้ สตู ร แสดงวา่ แฟ้มตน้ ทางทชี่ อ่ื Source นัน้ ในขณะนัน้ ถกู จัดเก็บไวท้ ใี่ ดแตย่ งั ไมไ่ ดถ้ กู เปิดขนึ้ มาขอ้ ควรระวงั ในการรกั ษาสตู ร link ขา้ มแฟ้ มใหใ้ ชง้ านไดถ้ กู ตอ้ งตลอดไป1. หากตอ้ งการทาํ การแกไ้ ขใดๆในแฟ้ มท่ี link กนั ตอ้ งเปิดแฟ้มท่ี link กนั ทกุ แฟ้มขน้ึ มา ทงั้ แฟ้มตน้ ทางและแฟ้มปลายทางกอ่ นทจี่ ะเรมิ่ ทําการแกไ้ ขเสมอ เพอื่ ทําใหก้ ารแกไ้ ขที่ เกดิ ขนึ้ เชน่ การยา้ ยตําแหน่งเซลล์ การเปลยี่ นชอื่ ชที หรอื แมแ้ ตช่ อ่ื แฟ้ม สง่ ผลให ้ Excel ไล่ แกต้ ําแหน่งอา้ งองิ ในสตู ร link ขา้ มแฟ้มใหเ้ อง (ถา้ ตอ้ งการให ้ Excel ชว่ ยจําวา่ ตอ้ งเปิด แฟ้มใดบา้ งพรอ้ มกนั ใหใ้ ชก้ ารเปิดแฟ้ม Workspace ชว่ ย ซงึ่ จะอธบิ ายในภายหลงั )2. หากตอ้ งการ Save as เพอื่ เปลยี่ นชอื่ แฟ้ มหรอื ยา้ ยโฟลเดอรท์ เี่ ก็บ ใหท้ ําการ Save แฟ้มตน้ ทางกอ่ นแลว้ จงึ Save แฟ้มปลายทางตามทหี ลงั โดยการ Save ทว่ี า่ นใ้ี หใ้ ช ้ โปรแกรม Excel เปิดแฟ้มแลว้ Save จากโปรแกรม Excel เทา่ นัน้ เพอ่ื ทําให ้ Excel ปรับ ตําแหน่งอา้ งองิ ในสตู รตามตําแหน่งของโฟลเดอรแ์ ละชอื่ แฟ้มทเ่ี ปลยี่ นไป (ตา่ งจากการใช ้ Windows Explorer ในการยา้ ยโฟลเดอรห์ รอื เปลยี่ นชอ่ื แฟ้ม ซง่ึ จะไมช่ ว่ ยใหส้ ตู รมกี าร แกไ้ ขตําแหน่งอา้ งองิ ตามแมแ้ ตน่ อ้ ย)3. หากตอ้ งการแกไ้ ขตาํ แหนง่ อา้ งองิ เนอื่ งจาก Excel เตอื นวา่ คน้ หา link ไมพ่ บ อาจ เป็ นเพราะคณุ พลงั้ เผลอไมไ่ ดป้ ฏบิ ตั ติ ามขอ้ ควรระวงั 2 ขอ้ ขา้ งตน้ กย็ งั สามารถแกไ้ ขให ้ link ทํางานตามเดมิ ไดต้ ามขนั้ ตอนตอ่ ไปน้ี โดย Excel จะไลแ่ กไ้ ขชอื่ แฟ้มตน้ ทางในเซลลส์ ตู ร link ทกุ เซลลใ์ หเ้ องไมว่ า่ อยใู่ นชที ใด 3.1. ใหเ้ ปิดแฟ้มปลายทางขนึ้ มากอ่ นโดยไมต่ อ้ งเปิดแฟ้มตน้ ทาง (แฟ้มปลายทางนกี้ ค็ อื แฟ้มทเี่ ปิดขน้ึ มาแลว้ พบวา่ Excel มคี ําเตอื นวา่ หา link ไมพ่ บ) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 29 of 147   3.2. ตรวจสอบวา่ ไมม่ ชี ที ใดถกู สง่ั Protect Sheet เอาไว ้ (เพราะถา้ ชที ใดถกู Protect จะไม่ สามารถแกไ้ ขสตู ร link ทม่ี อี ยใู่ นชที นัน้ ไดเ้ ลย แมจ้ ะสง่ั ผา่ น Excel ก็ตาม) 3.3. จากนัน้ ใหใ้ ชค้ ําสง่ั Data > Edit Links (Excel 2003 ใชค้ ําสง่ั Edit > Links) 3.4. คลกิ เลอื กชอื่ แฟ้มตน้ ทางทแ่ี สดงในชอ่ งรายชอื่ Source 3.5. กดป่ มุ Change Source แลว้ คลกิ ไลห่ าแฟ้มตน้ ทางทเ่ี กบ็ ไวใ้ นโฟลเดอรท์ ต่ี อ้ งการ4. เคล็ดอน่ื ๆซงึ่ สามารถนํามาใชร้ ว่ มกบั คําสง่ั Edit Links จากรปู ขา้ งตน้ 4.1. ป่ มุ Update Values ทําหนา้ ที่ update ขอ้ มลู จากแฟ้มตน้ ทางทเ่ี ลอื กชอ่ื ไวจ้ ากชอ่ ง Source ชว่ ยทําใหส้ ามารถเปิดแฟ้มขน้ึ มาโดยไมต่ อ้ ง update ในตอนเปิดแฟ้มกไ็ ด ้ (เพราะหากสงั่ update ตอนเปิดแฟ้ม จะทําให ้ Excel เสยี เวลาอยา่ งมากในการไล่ update แฟ้มตน้ ทางทมี่ ที กุ แฟ้ม) แลว้ ตอ่ เมอื่ ตอ้ งการดงึ ขอ้ มลู ใหมจ่ ากแฟ้มตน้ ทาง เมอ่ื ใด จงึ คอ่ ยคลกิ ป่ มุ Update Values นท้ี ลี ะแฟ้มทต่ี อ้ งการ 4.2. ป่ มุ Open Source ทําหนา้ ทเี่ ปิดแฟ้มตน้ ทาง ตามชอ่ื แฟ้มทเี่ ลอื กไวจ้ ากชอ่ ง Source 4.3. ป่ มุ Break Link ทําหนา้ ทที่ ําลายสตู รทงั้ หมดในเซลลท์ กุ เซลลท์ ม่ี สี ตู ร link ขา้ มแฟ้ม ใหก้ ลายเป็ นคา่ value หรอื ผลลพั ธ์ ซงึ่ ไมแ่ นะนําใหใ้ ชว้ ธิ นี เ้ี พราะ Excel จะทําลายสตู ร link ในแฟ้มนัน้ ทงั้ หมดโดยเราไมม่ ที างทราบไดเ้ ลยวา่ เป็ นเซลลใ์ ดบา้ งและยงั ไม่ สามารถ Undo ดงั นัน้ หากตอ้ งการทําลายสตู ร link ในเซลลใ์ ด ใหค้ น้ หาตําแหน่งเซลล์ ทมี่ สี ตู ร link โดยใชช้ อื่ แฟ้มทแี่ สดงในชอ่ ง Source สําหรับคน้ หาตําแหน่งเซลลน์ ัน้ เพอื่ Copy แลว้ Paste Special แบบ Value ทบั เฉพาะเซลลน์ ัน้ ๆจะเหมาะกวา่วธิ ใี ช้ Excel ไลเ่ ปิ ดแฟ้ มทง้ั หมดทตี่ อ้ งการใหเ้ อง1. ใหเ้ ปิดแฟ้มทกุ แฟ้มทตี่ อ้ งการเปิดขน้ึ มาพรอ้ มกนั ไวก้ อ่ น2. สงั่ View > Save Workspace (Excel 2003 สงั่ File > Save Workspace) แลว้ ให ้ ตงั้ ชอ่ื แฟ้มใหมท่ จี่ ะมนี ามสกลุ .xlw และมขี นาดแฟ้มเล็กมาก3. จากนัน้ เมอื่ ตอ้ งการเปิดแฟ้มทงั้ หมด ใหเ้ ปิดแฟ้ม .xlw นแ้ี ฟ้มเดยี ว จะพบวา่ Excel ไลเ่ ปิด แฟ้มทงั้ หมดใหเ้ องทนั ที โดยแฟ้ม .xlw นัน้ จะไมแ่ สดงขนึ้ บนจอตามแฟ้มอน่ื ๆทเี่ ปิดขนึ้ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 30 of 147  แนวทางการสรา้ งงานเบอื้ งตน้ลองนกึ ดซู วิ า่ หากคณุ มคี วามรแู ้ ละสามารถใช ้ Excel เพยี งแคร่ จู ้ ักการกําหนดตําแหน่งอา้ งองิดว้ ยเครอ่ื งหมาย $ และการ link พนื้ ทต่ี ารางดว้ ยสตู รขา้ มชที หรอื ขา้ มแฟ้มไดแ้ ลว้ พอคณุ ไดร้ ับมอบหมายงานจากหวั หนา้ จะตอ้ งวางแผนสรา้ งงานกนั ทลี ะขนั้ อยา่ งไรบา้ ง เพอื่ ชว่ ยใหพ้ อสรา้ งงานไปแลว้ ก็ตอ้ งไมต่ อ้ งเสยี เวลายอ้ นกลบั มาแกไ้ ขกนั อกี ใหว้ นุ่ วายเมอื่ ไดร้ ับมอบงานใหใ้ ช ้ Excel มขี นั้ ตอนทค่ี วรคดิ พจิ ารณาตามลําดบั ตอ่ ไปน้ี1. ขอใหค้ ดิ แบง่ ขอ้ มลู ออกเป็ นสว่ นของตาราง Input, Calculate, และ Output2. ใหอ้ อกแบบตาราง Output ซง่ึ เป็ นสว่ นทอี่ อกแบบไดง้ า่ ยทส่ี ดุ กอ่ น เพราะสว่ นใหญม่ แี บบ ตามหนา้ ตารายงานทเี่ คยใชก้ นั มากอ่ นอยแู่ ลว้3. จากนัน้ ใหค้ ดิ ยอ้ นมาออกแบบตาราง Input โดยพจิ ารณาวา่ ขอ้ มลู ใดบา้ งซงึ่ เป็ นทม่ี าหรอื ตน้ ทางของขอ้ มลู ทใ่ี ชค้ ํานวณแลว้ จะทําใหไ้ ด ้ Output ตามตอ้ งการ และยดึ หลกั วา่ ขอ้ มลู Input ทม่ี อี ยตู่ อ้ งไมม่ ขี อ้ มลู ใดทบี่ นั ทกึ ซ้ําไวโ้ ดยไมจ่ ําเป็ นอยา่ งเด็ดขาด เพอ่ื ลดความ ซา้ํ ซอ้ นและทําใหแ้ ฟ้มขอ้ มลู มขี นาดเล็กทส่ี ดุ4. ใหแ้ บง่ ขอ้ มลู Input ออกเป็ น 2 สว่ นเพอ่ื แยกตารางออกจากกนั 4.1. สว่ นแรกเป็ นขอ้ มลู ทม่ี ลี กั ษณะไมเ่ ปลย่ี นแปลงตามระยะเวลา เชน่ ขอ้ มลู ของสนิ คา้ หรอื ลกู คา้ ในสว่ นทเี่ ป็ นรหสั และชอ่ื ซง่ึ กําหนดไวแ้ น่นอนตายตวั วา่ รหสั นัน้ ตอ้ งเป็ นชอื่ นัน้ หรอื ขอ้ มลู คา่ คงทต่ี า่ งๆซง่ึ เป็ นขอ้ มลู สว่ นกลางทตี่ อ้ งนําไปใชร้ ว่ มกบั สว่ นอนื่ อยเู่ สมอ 4.2. อกี สว่ นหนง่ึ เป็ นขอ้ มลู ทม่ี ลี กั ษณะเปลยี่ นแปลงอยเู่ สมอ โดยทว่ั ไปมกั เป็ นขอ้ มลู ท่ี เกยี่ วขอ้ งกบั การดําเนนิ ธรุ กจิ ประจําวนั เชน่ การขาย การผลติ5. ในตาราง Calculate ใหแ้ ยกเซลลค์ ํานวณคา่ ทต่ี อ้ งถกู นําผลลพั ธไ์ ปใชเ้ ป็ นประจําออกไวเ้ ป็ น ตารางเฉพาะ จะไดไ้ มต่ อ้ งเสยี เวลาสรา้ งสตู รคํานวณคา่ เดมิ ซา้ํ อกี โดยตารางคํานวณแตล่ ะ ตารางตอ้ งหาทางออกแบบตารางใหใ้ ชส้ ตู ร Smart Formula ทกี่ ําหนดตําแหน่งควบคมุ ดว้ ย เครอื่ งหมาย $ ใหถ้ กู ตอ้ งเพอื่ ทําใหต้ ารางคํานวณหนงึ่ ๆมเี ซลลส์ ตู ร Smart Formula เพยี ง เซลลเ์ ดยี ววางไวท้ เ่ี ซลลห์ วั มมุ ซา้ ยสดุ ของตารางคํานวณ6. ถา้ ยงั มขี อ้ มลู ทงั้ หมดไมม่ ากนัก ใหอ้ อกแบบรวมทกุ ตารางไวใ้ นชที เดยี วกนั กอ่ น แตต่ อ้ งแยก พน้ื ทข่ี องตาราง Input, ตาราง Calculate, และ ตาราง Output ออกจากกนั โดยอาจใช ้ แนว Column เป็ นแนวในการแบง่ ตารางแตล่ ะสว่ นออกจากกนั และใหจ้ ัดลําดบั ขอ้ มลู และ สตู รตามลําดบั กอ่ นหลงั โดยพยายามสรา้ งสตู ร link ใหม้ ที ศิ ทางการ link เซลลจ์ ากบนมา สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 31 of 147   ลา่ งหรอื จากซา้ ยไปขวา เพอื่ ชว่ ยใหไ้ ลท่ ไ่ี ปทม่ี าของขอ้ มลู ไดง้ า่ ย7. หากขอ้ มลู มปี รมิ าณมากขน้ึ หรอื มหี ลายตาราง ใหย้ า้ ยขอ้ มลู แตล่ ะตารางไปเก็บแยกชที ไวใ้ ห ้ เป็ นระเบยี บ และตอ้ งวางตําแหน่งตารางใหท้ กุ ชที ทเี่ กบ็ ขอ้ มลู เรอื่ งเดยี วกนั ใชต้ ําแหน่งเซลล์ เดยี วกนั ตรงกนั ทกุ ชที เชน่ สมมตวิ า่ คณุ แยกรายงานรายเดอื นออกเป็ นชที ละเดอื น โดยท่ี รายงานแตล่ ะเดอื นมโี ครงสรา้ งตารางเหมอื นกนั ทงั้ หมด หากชที แรกวางตารางไวท้ เี่ ซลล์ B2:C6 กใ็ หใ้ ชเ้ ซลล์ B2:C6 ของทกุ ชที เป็ นตําแหน่งตารางรายงานตรงกนั ดว้ ย8. ประเด็นการวางตําแหน่งตารางใหต้ รงกนั ทกุ ชที ถอื เป็ นเรอื่ งสําคญั มาก แมบ้ างชที จะมขี อ้ มลู แคบ่ างเซลลก์ ต็ าม ก็ขอใหว้ างคา่ เรอื่ งเดยี วกนั ไวใ้ นเซลลเ์ ดยี วกนั ไปตลอดทกุ ชที เพราะเรา สามารถใชส้ ตู รหรอื คําสงั่ ของ Excel ในการดงึ คา่ จากตําแหน่งเซลลเ์ ดยี วกนั ไดโ้ ดยงา่ ยแม ้ จะตา่ งชที กนั ก็ตาม ทงั้ นอ้ี ยา่ พยายามลบ Row หรอื Column ทไ่ี มม่ ขี อ้ มลู ทงิ้ หรอื เลอื่ น เซลลท์ ม่ี คี า่ มาตดิ กนั อยา่ งเด็ดขาด9. ตราบใดทแ่ี ฟ้มนัน้ ยงั มขี นาดไมใ่ หญน่ ักและ Excel ไมเ่ สยี เวลาคํานวณนานจนรอไมไ่ หว ขอใหเ้ ก็บตารางทงั้ Input Calculate และ Output ไวใ้ นแฟ้มเดยี วกนั ไปเรอ่ื ยๆแมจ้ ะมหี ลาย ชที กต็ าม หากไมจ่ ําเป็ นแลว้ อยา่ พยายามแยกแฟ้มอยา่ งเด็ดขาด10. ใหพ้ จิ ารณายา้ ยชที ออกมาเป็ นแฟ้มใหมต่ า่ งหาก ตอ่ เมอื่ แฟ้มเดมิ มขี นาดใหญม่ ากและตอ้ ง เสยี เวลาในการเปิดแฟ้มและเสยี เวลาในการคํานวณนานจนรอไมไ่ หว เมอ่ื ตอ้ งการยา้ ยชที ทงั้ ชที เป็ นแฟ้มใหมใ่ หใ้ ชว้ ธิ คี ลกิ ขวาทช่ี อ่ื ชที แลว้ เลอื กคําสงั่ Move or Copy จากนัน้ คลกิ ใน ชอ่ ง To book เพอื่ เปลยี่ นเป็ น (new book) (ทงั้ นหี้ า้ มกาชอ่ ง Create a copy) แลว้ กด OK สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 32 of 147   จะพบวา่ ชที นัน้ ถกู ยา้ ยไปเปิดเป็ นแฟ้มใหม่ แลว้ ใหจ้ ดั เก็บแฟ้มนัน้ ในชอ่ื แฟ้มใหมท่ ต่ี อ้ งการ11. แทนทจี่ ะ Move ชที ไปเป็ นแฟ้มใหมซ่ ง่ึ มเี พยี งชที เดยี ว ใหพ้ จิ ารณาวา่ ชที ใดทตี่ อ้ งคํานวณ พรอ้ มกนั หรอื คํานวณตอ่ เนอ่ื งกนั ไปทกุ ครัง้ ทเี่ กดิ การคํานวณ กข็ อใหย้ า้ ยชที เหลา่ นัน้ ไปเก็บ ไวใ้ นแฟ้มเดยี วกนั12. หากแฟ้มใหมท่ เี่ กดิ ขน้ึ จากการยา้ ยชที ในขนั้ ตอนขา้ งตน้ มสี ตู ร link กบั ชที อน่ื จะพบวา่ Excel ปรับตําแหน่งอา้ งองิ ในสตู ร link ตามชอ่ื ชที และชอื่ แฟ้มใหมท่ เี่ กดิ ขน้ึ ใหเ้ อง ขอให ้ สงั เกตวา่ ถา้ อยใู่ นแฟ้มปลายทางจะพบวา่ คําสง่ั Data > Edit Links (Excel 2003 ใหด้ ทู ่ี คําสง่ั Edit > Links) จะปรากฏขนึ้ เพอ่ื พรอ้ มตอ่ การใชง้ านและยงั มคี วามหมายอกี อยา่ ง หนงึ่ วา่ แฟ้มนัน้ เป็ นแฟ้มปลายทาง13. หากตอ้ งการเปิดแฟ้มท่ี link กนั ขนึ้ มาใชง้ าน ใหเ้ ปิดแฟ้มปลายทางขนึ้ มา โดยไมต่ อ้ งเปิด แฟ้มตน้ ทาง แตถ่ า้ ตอ้ งการเปิดแฟ้มเพอ่ื ตอ้ งการแกไ้ ขใดๆ ใหเ้ ปิดแฟ้มทกุ แฟ้มที่ link กนั ขนึ้ มาพรอ้ มกนั ทงั้ หมดโดยใชว้ ธิ เี ปิดแฟ้ม Workspace ตามทอ่ี ธบิ ายไปกอ่ นแลว้14. หากพบวา่ แฟ้มทตี่ อ้ งเปิดพรอ้ มกนั เพอื่ ทําใหค้ ํานวณพรอ้ มกนั นัน้ มขี นาดแฟ้มรวมกนั แลว้ ใหญม่ ากและเสยี เวลาเปิดแฟ้มตลอดจนเสยี เวลาคํานวณนานมาก ใหท้ ยอยเปิดแฟ้มท่ี link กนั ทลี ะคตู่ ามลําดบั การ link เพอ่ื ทําใหส้ ตู รทเ่ี กยี่ วขอ้ งในแฟ้มท่ี link กนั นัน้ คํานวณแลว้ สง่ คา่ ไปเกบ็ ไวใ้ นแฟ้มปลายทางแตล่ ะคู่ แลว้ จงึ ไลเ่ ปิดแฟ้มคทู่ ี่ link กนั ในลําดบั การคํานวณ ขนั้ ตอ่ ไปเรอ่ื ยไป ซงึ่ วธิ นี จี้ ะชว่ ยใหไ้ มต่ อ้ งเปิดแฟ้มขนาดใหญม่ ากและสามารถใชเ้ ครอื่ ง คอมพวิ เตอรร์ นุ่ เกา่ ๆทํางานชา้ และมหี น่วยความจําไมม่ ากนักไดเ้ ร็วโดยไมต่ อ้ งซอื้ เครอื่ งใหม่เคล็ดการจดั การกบั ระบบ link ขา้ มแฟ้ มใน Excel 2003 ระบบการ link ไมค่ อ่ ยยงุ่ ยากเทา่ ใดนัก พอคณุ เปิดแฟ้มปลายทางขนึ้ มาแฟ้มเดยี ว จะพบวา่ Excel เปิดหนา้ จอขน้ึ มาถามวา่ ตอ้ งการ Update Links ไหม  ถา้ เลอื ก Update จะพบวา่ ไฟแสดงการทํางานของ Hard disk กระพรบิ อยพู่ ักหนงึ่ แสดงวา่ Excel กําลงั แอบไปเปิดแฟ้มตน้ ทางขน้ึ มาเพอ่ื ดงึ ขอ้ มลู ในแฟ้มตน้ ทางไปเก็บ ไวท้ ่ี cache ในแฟ้มปลายทาง พอเก็บขอ้ มลู เสร็จแลว้ Excel ก็จะปิดแฟ้มตน้ ทางโดยที่ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 33 of 147   เราไมม่ สี ว่ นรเู ้ ห็นในการไปเปิดปิดแฟ้มตน้ ทางแมแ้ ตน่ อ้ ย จากนัน้ แฟ้มปลายทางกจ็ ะมี ขอ้ มลู ใหมต่ ามขอ้ มลู ในแฟ้มตน้ ทาง  ถา้ เลอื ก Don’t Update จะพบวา่ แฟ้มปลายทางเปิดขน้ึ มาใหใ้ ชง้ านไดท้ นั ที เพยี งแต่ วา่ ขอ้ มลู ในแฟ้มปลายทางยงั คงเป็ นขอ้ มลู เกา่ ไมไ่ ดข้ อ้ มลู ใหมต่ ามแฟ้มตน้ ทาง จากนัน้ หากคณุ ตอ้ งการ update หลงั จากทเ่ี ปิดแฟ้มมาแลว้ ใหส้ งั่ Edit > Links > คลกิ ชอื่ แฟ้ มตน้ ทาง > กดป่ มุ Update Valuesนอกจากนัน้ ยงั สามารถสงั่ ให ้ Excel 2003 เลกิ ถามวา่ ตอ้ งการ Update Links อยา่ งถาวรเลยก็ได ้ โดยใชค้ ําสง่ั Tools > Options > Edit > ตดั กาชอ่ ง Ask to update automaticlinks ซงึ่ จะสง่ ผลให ้ Excel 2003 เลกิ ถามการ update และจะจดั การ update ใหเ้ องทันทเี มอ่ืคณุ เปิดแฟ้มปลายทางใดๆขน้ึ มาถา้ พบวา่ แฟ้มปลายทางเก็บ cache ซง่ึ เป็ นขอ้ มลู จากแฟ้มตน้ ทางไวจ้ นแฟ้มปลายทางมขี นาดใหญม่ าก ก็สามารถตดั cache ทง้ิ ไดโ้ ดยเปิดแฟ้มปลายทางทต่ี อ้ งการตดั cache ขน้ึ มากอ่ นแลว้ สง่ั Tools > Options > Calculation > ตดั กาชอ่ ง Save external links valuesทงิ้ ไป ซง่ึ จะพบวา่ แฟ้มปลายทางมขี นาดแฟ้มลดลงอยา่ งมาก แตเ่ มอื่ ตอ้ งการใชง้ านก็จําเป็ นตอ้ งเปิดทกุ แฟ้มที่ link กนั ขน้ึ มาพรอ้ มกนั ใหค้ รบกอ่ น หรอื ถา้ อยากเปิดแฟ้มปลายทางเพยี งแฟ้มเดยี ว ตอ้ งสงั่ Update Links เสมอเพอื่ ทําใหเ้ กดิ cache ชวั่ คราวขน้ึ มาใชง้ านสว่ นระบบการ link ใน Excel 2010 แมจ้ ะมคี ําสง่ั และลกั ษณะบนจอทตี่ า่ งจากเดมิ ไปบา้ ง แตก่ ็ทําหนา้ ทไ่ี มไ่ ดแ้ ตกตา่ งจาก Excel 2003 เทา่ ใดนักกลา่ วคอื  เมอื่ เปิดแฟ้มปลายทางขนึ้ มาแฟ้มเดยี วโดยไมต่ อ้ งเปิดแฟ้มตน้ ทาง จะมี Security Warning Tab เปิดขน้ึ เหนอื ขอบพน้ื ทตี่ ารางเพอื่ บอกวา่ Excel ไมไ่ ด ้ update link จนกวา่ จะกดป่ มุ Enable Content สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 34 of 147    หาก Security Warning Tab ถกู ปิดหายไป คณุ สามารถสงั่ update link ไดเ้ องจาก คําสง่ั Data > Edit Links แลว้ คลกิ ชอ่ื แฟ้มตน้ ทางในชอ่ ง Source แลว้ กดป่ มุ Update Values  ใน Edit Links ตามรปู ขา้ งตน้ หากตอ้ งการปรับระบบการเตอื น update ของแฟ้ม ปลายทางทก่ี ําลงั เปิดอยู่ ใหก้ ดป่ มุ Startup Prompt ดา้ นลา่ งสดุ จะสามารถกําหนดให ้ เมอื่ เปิดแฟ้มนัน้ มกี ารเตอื น update link และ/หรอื ให ้ update link ทนั ทที เี่ ปิดแฟ้มดว้ ย ไดต้ ามตอ้ งการ (ไมค่ วรเลอื กชอ่ ง Don’t display the alert and don’t update automatic links เพราะในขณะเปิดแฟ้ม คณุ จะไมม่ ที างทราบเลยวา่ ขอ้ มลู ในแฟ้มทเี่ ปิด นัน้ เป็ นขอ้ มลู ที่ update แลว้ หรอื ยงั ) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 35 of 147    แทนทจ่ี ะปรับระบบการเตอื น update link ใหก้ บั แตล่ ะแฟ้ม คณุ สามารถปรับระบบของ Excel ใหเ้ ลกิ เตอื นการ update link ในขณะเปิดแฟ้มปลายทางทกุ แฟ้ม เพอ่ื ใหม้ กี าร update link เองทนั ทที เ่ี ปิดแฟ้ม โดยสงั่ File > Options > Advanced > General > ตดั กาชอ่ ง Ask to update automatic links ซงึ่ คําสง่ั นจ้ี ะมผี ล ตอ่ เนอื่ งไปตลอดไมว่ า่ จะปิดเปิดตวั โปรแกรม Excel มาใชใ้ หมก่ ็ตาม  หากแฟ้มปลายทางมขี นาดใหญม่ าก คณุ สามารถตดั cache ทเ่ี กบ็ ไวใ้ นแฟ้มปลายทาง ทงิ้ ไดเ้ พอื่ ลดขนาดแฟ้มลง โดยเปิดแฟ้มปลายทางนัน้ แลว้ สง่ั File > Options > Advanced > When calculating this workbook > ตดั กาชอ่ ง Save external link values ตามภาพขา้ งตน้ (และเมอ่ื จะใชข้ อ้ มลู ในแฟ้มปลายทางทไี่ มม่ ี cache ก็ตอ้ งเปิดแฟ้มแบบ update link เสมอ หรอื ตอ้ งใชว้ ธิ เี ปิดแฟ้มทงั้ ตน้ ทางและ ปลายทางขนึ้ มาใชง้ านพรอ้ มกนั )  ใน Excel 2007 เป็ นตน้ มา มรี ะบบ update link เมอื่ มกี ารคํานวณ ซงึ่ เป็ นระบบใหมท่ ไี่ ม่ เคยมใี น Excel 2003 จากการสงั่ File > Options > Advanced > When calculating this workbook ตามภาพขา้ งตน้ จากนัน้ ใหเ้ ลอื กกาในชอ่ ง Update links to other documents ซง่ึ ปกตชิ อ่ งนจี้ ะถกู เลอื กไวอ้ ยกู่ อ่ นแลว้  ใน Excel 2007 เป็ นตน้ มา มรี ะบบ Trust Folder โดยคณุ สามารถกําหนดให ้ Excel ยอมรับขอ้ มลู จากแฟ้มทกุ แฟ้มทเ่ี กบ็ ไวใ้ นโฟลเดอรท์ คี่ ณุ ไวใ้ จโดย Excel จะไมต่ อ้ ง เสยี เวลาถาม update link หรอื enable macro เมอื่ คณุ เปิดแฟ้ม เรมิ่ จากการสงั่ File > Options > Trust Center > กดป่ มุ Trust Center Settings สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 36 of 147   จากนัน้ ในหนา้ จอถดั ไปใหค้ ลกิ เลอื ก Trusted Locations > กดป่ มุ Add new location เพอื่ เลอื กโฟลเดอรท์ ไี่ วใ้ จได ้ จากนัน้ เมอ่ื เปิดแฟ้มทเ่ี ก็บไวใ้ นโฟลเดอรท์ ่ี กําหนด Excel ก็จะเปิดแฟ้มใหท้ นั ทโี ดยไมม่ กี ารเตอื นใดๆทงั้ สนิ้  Excel 2010 มรี ะบบ Trusted Documents ชว่ ยจําวา่ แฟ้มใดทเี่ คยเปิดแบบ Enable Macro หรอื Update Links มากอ่ น เมอ่ื เปิดแฟ้มนัน้ อกี จะเปิดระบบใหท้ ํางานเองทันที สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 37 of 147  การนําตาํ แหนง่ อา้ งองิ ไปใชซ้ ํา้การนําตําแหน่งอา้ งองิ ไปใชซ้ ํา้ เป็ นขนั้ ตอนสําคญั ทจ่ี ะทําใหค้ ณุ ใช ้ Excel ไดอ้ ยา่ งคอมพวิ เตอร์เพราะไมเ่ พยี งชว่ ยลดเวลาในการสรา้ งงานทําใหไ้ มต่ อ้ งสรา้ งสตู รใหมท่ กุ ครัง้ ทอี่ ยากไดแ้ ลว้หากทราบเคล็ดลบั ทนี่ ํามาอธบิ ายนยี้ งั ชว่ ยใหค้ ณุ เกดิ ความรสู ้ กึ วา่ Excel เป็ นโปรแกรมทส่ี ามารถใชง้ านไดง้ า่ ยอกี ดว้ ยเพอื่ ทําใหเ้ ขา้ ใจประเด็นของการนําตําแหน่งอา้ งองิ ไปใชซ้ ้าํ วา่ คอื อะไร ขอใหล้ องทําตวั อยา่ งงา่ ยๆตอ่ ไปนด้ี สู กั หน่อยวา่ จะหาทางนําสตู รในเซลล์ D2 ทนี่ ําตําแหน่งอา้ งองิ จากเซลล์ B2 และC2 มาคณู กนั ดว้ ยสตู ร =B2*C2 นไี้ ปใชซ้ ํา้ ทเี่ ซลลอ์ นื่ ไดอ้ ยา่ งไรกนัโปรดสงั เกตวา่ ในตวั สตู ร =B2*C2 มไิ ดใ้ สเ่ ครอ่ื งหมาย $ เพอ่ื ควบคมุ ตําแหน่งใดไว ้ ดงั นัน้ หากใชว้ ธิ ี Copy เซลล์ D2 ไปใชท้ เี่ ซลลอ์ นื่ จะทําใหต้ ําแหน่งอา้ งองิ ในตวั สตู รเขยอ้ื นจากตําแหน่งเดมิ เชน่ ถา้ Copy ไปใชท้ เี่ ซลล์ D3 จะไดส้ ตู ร =B3*C3 ซง่ึ ไดค้ ําตอบผดิ พลาดตา่ งไปจากเดมิดงั นัน้ หากตอ้ งการนําสตู ร =B2*C2 ไปใชซ้ า้ํ ทเ่ี ซลลอ์ น่ื ไดท้ นั ทโี ดยไมต่ อ้ งกงั วลเรอ่ื งการใส่เครอื่ งหมาย $ จะทําไดอ้ ยา่ งไรวธิ ที ่ี 1 : Copy สตู รทเี่ ปลย่ี นเป็ น Text ไวก้ อ่ นใหแ้ กไ้ ขสตู รจากเดมิ =B2*C2 ใหก้ ลายเป็ น Text โดยใสต่ วั อกั ษรใดๆก็ไดล้ งไปไวห้ นา้เครอื่ งหมายเทา่ กบั เชน่ c=B2*C2 จากนัน้ จงึ Copy เซลล์ D2 ไป Paste ทเ่ี ซลลอ์ นื่ แลว้ยอ้ นกลบั มาแกไ้ ขสตู รในเซลลท์ งั้ เซลลเ์ ดมิ และเซลลใ์ หมใ่ หก้ ลบั คนื เป็ น =B2*C2 ตามเดมิในการแกไ้ ขสตู รน้ี หากมเี ซลลส์ ตู รเป็ นจํานวนมาก ใหใ้ ชค้ ําสง่ั Replace ชว่ ยแกไ้ ขในการเปลย่ี นเครอื่ งหมายเทา่ กบั = เป็ น c= แลว้ Replace กลบั มาสสู่ ตู รทมี่ เี พยี งเครอื่ งหมายเทา่ กบันําหนา้ ตามเดมิ (หา้ มใสเ่ ครอ่ื งหมายฝนทอง ‘ นําหนา้ เครอื่ งหมายเทา่ กบั เนอื่ งจากคําสง่ัReplace จะคน้ หาเครอ่ื งหมายฝนทองไมพ่ บ) สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 38 of 147  วธิ ที ่ี 2 : Copy  Move  Copy เซลลส์ ตู ร 1. ใหเ้ รม่ิ จาก Copy เซลล์ D2 ซง่ึ มสี ตู ร =B2*C2 ไปเกบ็ ไวท้ เ่ี ซลลใ์ ดกไ็ ด ้ จากรปู สมมตวิ า่ นําไปเก็บไวท้ เ่ี ซลล์ D6 โดยไมต่ อ้ งสนใจวา่ จะทําใหส้ ตู รมตี ําแหน่งอา้ งองิ ตา่ งไปจาก เดมิ กลายเป็ น =B6*C6 ซงึ่ ไมใ่ ชต่ ําแหน่งเซลลเ์ ดมิ B2 และ C2 ก็ตาม 2. จากนัน้ ให ้ Cut เซลล์ D2 ไปยงั ตําแหน่งเซลลใ์ หมต่ ามตอ้ งการ เชน่ ยา้ ยเซลลไ์ ปไวท้ ี่ เซลล์ F2 ทําใหไ้ ดส้ ตู ร =B2*C2 ตามเดมิ (วธิ ี Cut นยี้ งั ชว่ ยใหส้ ามารถนําสตู รไปใชซ้ า้ํ ในชที อนื่ ไดด้ ว้ ย) 3. ขนั้ สดุ ทา้ ยให ้ Copy เซลล์ D6 กลบั ไปทเ่ี ซลล์ D2 จะทําใหไ้ ดส้ ตู ร =B2*C2 กลบั มา ตามเดมิเนอื่ งจากมกี าร Cut เซลล์ D2 ไปทตี่ ําแหน่งเซลลใ์ หม่ จงึ ตอ้ งทําความเขา้ ใจดว้ ยวา่ หากเดมิ มีสตู รในเซลลอ์ นื่ ทเ่ี คยกําหนดตําแหน่งอา้ งองิ กบั เซลล์ D2 เอาไว ้ จะทําใหต้ ําแหน่งอา้ งองิ ในสตู รถกู แกไ้ ขตามการยา้ ยไปยงั ตําแหน่งเซลลใ์ หมด่ ว้ ยโดยอตั โนมตั ิวธิ ที ี่ 3 : Copy สตู รในเซลลไ์ ปใชซ้ ํา้ ทเ่ี ซลลอ์ นื่ 1. ใหเ้ รม่ิ จากใช ้ Mouse คลกิ ลากทบั สว่ นของสตู รทแ่ี สดงใน Formula Bar 2. คลกิ ขวาสงั่ Copy 3. กดป่ มุ Esc 4. จากนัน้ ใหค้ ลกิ เลอื กเซลลท์ ต่ี อ้ งการแลว้ สงั่ Pasteนอกจาก 3 วธิ ขี า้ งตน้ นแ้ี ลว้ ยงั มวี ธิ อี น่ื อยอู่ กี เพยี งแตจ่ ะตอ้ งรจู ้ ักการตงั้ ชอ่ื และใชช้ อื่ RangeName กอ่ น ซงึ่ จะนํามาอธบิ ายในลําดบั ตอ่ ไป สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 39 of 147  หลกั การตงั้ ชอื่ และใชช้ อื่ ใน Excelการตงั้ ชอ่ื และใชช้ อื่ เป็ นหวั ใจสําคญั ของการนําตําแหน่งอา้ งองิ ไปใชซ้ ํ้า เนอื่ งจากหากปราศจากการตงั้ ชอ่ื ทด่ี ขี น้ึ มาใชแ้ ทนตําแหน่งอา้ งองิ ของเซลลแ์ ลว้ สตู รสนั้ ๆทดี่ ไู มม่ พี ษิ ภยั อะไร จะกลบักลายเป็ นปัญหาอยา่ งมาก ยง่ิ ใน Excel 2007 มพี นื้ ทตี่ ารางกวา่ หมนื่ Column และลา้ นกวา่Row จะทําให ้ สตู ร =JAN2009 ซง่ึ เป็ นสตู รทน่ี ําคา่ มาจากเซลลใ์ น Column JAN ตดั กบั Row2009 สามารถดผู าดๆและถกู ตคี วามวา่ เป็ นคา่ ของเดอื น Jan ปีค.ศ.2009 แทนก็เป็ นได ้หากสงั เกตขนาดของตวั อกั ษรแตล่ ะตวั ในคําแตล่ ะคําทป่ี รากฏในตาราง Excel จะพบวา่ ชอ่ื ใดๆก็ตามทเ่ี ป็ นชอ่ื มาตรฐานของโปรแกรม Excel ซง่ึ เราไมส่ ามารถเขา้ ไปแกไ้ ขเปลย่ี นแปลงใหเ้ ป็ นชอื่ อนื่ ไดถ้ กู จดั ใหใ้ ชต้ วั อกั ษรตวั ใหญท่ งั้ หมด (เวน้ ชอื่ คาํ สง่ั บนRibbon หรอื เมน)ู เชน่ ชอ่ื ตําแหน่ง Column A B C D จนถงึ XFD (Excel 2003 มี Columnสดุ ทา้ ยชอื่ IV) และสตู รสําเร็จรปู ของ Excel ทกุ สตู ร เชน่ สตู ร SUM, MAX, MIN, VLOOKUPลว้ นใชต้ วั อกั ษรตวั ใหญท่ ัง้ หมด ดงั นัน้ แทนทจี่ ะพรอ้ มใจกนั ตงั้ ชอ่ื แลว้ ใชช้ อื่ ทเ่ี ป็ นตวั ใหญ่ทงั้ หมดตามแบบของชอื่ ของ Excel เราควรกําหนดหลกั การตงั้ ชอื่ และใชช้ อื่ ทสี่ อื่ ความหมายได ้ดขี นึ้ กวา่ เดมิ ดงั น้ี1. หากแฟ้มใดจะถกู ใชซ้ ํ้า ขอใหต้ งั้ ใจตงั้ ชอ่ื ใหด้ ี เพอ่ื ทําใหไ้ มว่ า่ ผทู ้ นี่ ําแฟ้มงานไปใชซ้ า้ํ นัน้ จะ เป็ นตวั คณุ ซงึ่ เป็ นผสู ้ รา้ งแฟ้มนัน้ เองหรอื จะเป็ นเพอ่ื นทเี่ พง่ิ เปิดใชแ้ ฟ้มนัน้ เป็ นครัง้ แรกก็ตาม จะไดส้ ามารถเปิดแฟ้มดแู ลว้ สามารถไลท่ ไี่ ปทมี่ าของสตู รในเซลลไ์ ดท้ นั ที แตถ่ า้ แฟ้มใดถกู สรา้ งขนึ้ มาเพอ่ื ใชง้ านงา่ ยๆเพยี งครัง้ เดยี วแลว้ ทง้ิ ไมต่ อ้ งนํากลบั มาใชอ้ กี กไ็ มจ่ ําเป็ นตอ้ งตงั้ ชอ่ื ใหด้ กี ไ็ ด ้2. ชอื่ ใดทคี่ ณุ ตงั้ ขนึ้ มาไมว่ า่ จะเป็ นชอื่ ชที ชอื่ แฟ้ม ชอื่ โฟลเดอร์ ชอื่ ตําแหน่งเซลล์ (Range Name) ชอื่ สตู ร (Formula Name) หรอื ชอ่ื ทต่ี งั้ ใหก้ บั คา่ คงท่ี (Constant Name) ใหต้ งั้ ชอ่ื โดยใชต้ วั อกั ษรตวั ใหญผ่ สมกบั ตวั เล็กเสมอ3. เมอื่ ใดทต่ี อ้ งการสรา้ งสตู รหรอื อา้ งถงึ ชอ่ื ใดๆก็ตาม ใหใ้ ชต้ วั อกั ษรตวั เล็กทงั้ หมดเทา่ นัน้ เพราะเมอ่ื พมิ พล์ งไปในเซลลแ์ ลว้ กดป่ มุ Enter จะเกดิ ขอ้ สงั เกตในตวั อกั ษรของแตล่ ะชอื่ ขน้ึ มาทนั ทกี ลา่ วคอื 3.1. ตวั อกั ษรทก่ี ลายเป็ นตวั ใหญท่ งั้ หมด แสดงวา่ เป็ นชอ่ื เฉพาะของ Excel 3.2. ตวั อกั ษรทก่ี ลายเป็ นตวั ใหญผ่ สมตวั เล็ก แสดงวา่ เป็ นชอื่ ทค่ี ณุ ตงั้ ไว ้ 3.3. ตวั อกั ษรทย่ี งั คงเป็ นตวั เล็กทงั้ หมด แสดงวา่ สะกดชอื่ ผดิ ดงั นัน้ Excel จงึ ไมย่ อมรับชอื่ นัน้ ทําใหข้ นาดตวั อกั ษรไมเ่ ปลย่ี นแปลงตาม4. ชอื่ ทต่ี งั้ ขน้ึ ควรใชต้ วั อกั ษรตงั้ แต่ 4 ตวั ขน้ึ ไปเพอ่ื มใิ หซ้ ํา้ กบั ชอื่ ของ Column แตถ่ า้ จําเป็ นตอ้ งตัง้ ชอ่ื ทม่ี ตี วั อกั ษร 2-3 ตวั กท็ ําได ้ เพราะการตงั้ ชอ่ื ทใ่ี ชต้ วั อกั ษรตวั ใหญผ่ สมตวั เล็กจะชว่ ยในการแยกแยะใหเ้ ห็นไดช้ ดั อยแู่ ลว้ วา่ Jan2009 มไิ ดม้ คี วามหมายเดยี วกบั JAN2009 ทเ่ี ป็ นเซลลใ์ น Column JAN ตดั กบั Row 2009 (ถา้ ตอ้ งการตงั้ ชอื่ ทเ่ี ป็ นตวั อกั ษร ตวั เดยี ว ขอใหห้ ลกี เลยี่ งตวั C และ R เพราะซา้ํ กบั ตวั ยอ่ ของ Row และ Column) สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 40 of 147  5. Excel จะไมย่ อมรับชอื่ Range Name ทมี่ กี ารเวน้ วรรค โดยหากสงั่ ให ้ Excel ใชข้ อ้ ความใน เซลลต์ ามขอบตารางมาเป็ นชอ่ื โดยอตั โนมตั ิ จะถกู แกไ้ ขเครอ่ื งหมายวรรคเป็ นเครอื่ งหมาย ขดี ลา่ ง _ (Underscore) ใหเ้ อง และ Excel จะไมย่ อมรับชอื่ Range Name ทเ่ี ป็ นตวั เลข ลว้ นหรอื เป็ นตวั เลขนําหนา้ ตวั อกั ษร6. แมจ้ ะไมม่ ขี อ้ หา้ มในการตงั้ ชอ่ื เป็ นภาษาไทย แตก่ ็แนะนําใหต้ งั้ ชอ่ื เป็ นภาษาองั กฤษจะ เหมาะสมกวา่ เพราะหากใช ้ Excel version ภาษาองั กฤษ เปิดแฟ้มทม่ี กี ารใชช้ อ่ื ภาษาไทย จะอา่ นชอ่ื ภาษาไทยไมอ่ อก7. ในการตงั้ ชอื่ ชที หา้ มตงั้ ชอ่ื วา่ History เนอ่ื งจากเป็ นชอื่ ท่ี Excel สํารองไวใ้ ชภ้ ายใน8. ในการตงั้ ชอ่ื Range Name หา้ มตงั้ ชอื่ วา่ Criteria หรอื Extract เพราะชอื่ Criteria นจ้ี ะถกู ลบทงิ้ หรอื เปลย่ี นแปลงตําแหน่ง Extract จากเดมิ ไดเ้ องทนั ทที ใ่ี ชค้ ําสงั่ Advanced Filter9. หากตอ้ งการไดร้ ับความสะดวกจากคําสง่ั Data Form ใหต้ งั้ ชอ่ื ตารางฐานขอ้ มลู วา่ Database เพอื่ ทําให ้ Excel ดงึ ขอ้ มลู มาใชใ้ น Data Form ไดเ้ องโดยทคี่ ณุ ไมต่ อ้ งเลอื ก ตารางฐานขอ้ มลู นัน้ ไวก้ อ่ น10. โปรดเขา้ ใจเพมิ่ เตมิ วา่ ในการเขยี นชอื่ สตู รและชอื่ ตา่ งๆทใ่ี ชใ้ นการอบรมตลอดจนขอ้ มลู บน เว็บ www.ExcelExpertTraining.com นัน้ ใชว้ ธิ เี ขยี นชอื่ โดยใชอ้ กั ษรตวั ใหญผ่ สมตวั เล็ก เสมอ เพอื่ ชว่ ยในการอา่ นใหส้ ามารถแยกแตล่ ะสว่ นของคําไดง้ า่ ยขน้ึ เชน่ VLookup, SumIF, SumProduct เป็ นตน้ ซง่ึ เมอ่ื ใดทต่ี อ้ งการนําชอื่ ไปใช ้ ใหพ้ มิ พต์ วั เล็กตลอดตาม หลกั การขา้ งตน้ใน Excel 2003 มรี ะบบการตงั้ ชอื่ Range Name, Formula Name, และ Constant Name ทไี่ ม่ซบั ซอ้ นนัก เพยี งสง่ั Insert > Name > Define กส็ ามารถ Add ตงั้ ชอ่ื หลายๆชอ่ื ไดโ้ ดยใช ้คําสงั่ นเ้ี พยี งครัง้ เดยี ว สว่ นใน Excel 2007 มคี ําสง่ั ในการตงั้ ชอ่ื ใหเ้ ลอื กได ้ 2 วธิ ี คอื1. หากตอ้ งการตงั้ ชอื่ เดยี ว ใหส้ งั่ Formulas > Define Name2. หากตอ้ งการตงั้ ชอื่ หลายชอ่ื และสามารถยอ้ นกลบั มาแกไ้ ขหรอื ตงั้ ชอ่ื อน่ื ใหมอ่ กี ไดใ้ นทนั ที ใหส้ ง่ั ผา่ น Formulas > Name Manager สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 41 of 147   2.1. กดป่ มุ New เพอื่ ตงั้ ชอ่ื ใหม่ หรอื 2.2. กดป่ มุ Edit เพอื่ แกไ้ ขตําแหน่งอา้ งองิ ในชอื่ หรอื 2.3. กดป่ มุ Delete เพอ่ื ลบชอ่ื ทงิ้นอกจากนัน้ Excel 2007 เป็ นตน้ มา ไดป้ รับปรงุ ระบบการตงั้ ชอื่ ใหส้ นองตอบตอ่ การใชง้ านในระดบั File Level หรอื Sheet Level ไดส้ ะดวกกวา่ แตก่ อ่ นมาก กลา่ วคอื  การตง้ั ชอื่ เพอ่ื ใชใ้ นระดบั File Level เป็ นระบบมาตรฐานท่ี Excel เลอื ก Scope เป็ น Workbook ไวใ้ หต้ งั้ แตต่ น้ เพอื่ ทําใหช้ อ่ื ทต่ี งั้ ขน้ึ ในระดบั นสี้ ามารถนําไปใชร้ ว่ มกนั ไดท้ กุ ชที  การตง้ั ชอ่ื เพอื่ ใชใ้ นระดบั Sheet Level โดยคลกิ เปลยี่ น Scope เป็ นชอ่ื ชที ที่ ตอ้ งการ ทําใหช้ อื่ ทต่ี งั้ ขน้ึ ในระดบั ชที เป็ นชอื่ ทใี่ ชใ้ นชที นัน้ และเป็ นอสิ ระจากชที อนื่ สง่ ผลใหส้ ามารถตงั้ ชอื่ ซ้ํากนั ไดใ้ นแฟ้มเดยี วกนั (น่าสงั เกตวา่ เราสามารถกาํ หนด Scope ใหอ้ ยตู่ า่ งชที กบั ชที ที่ Refers to ก็ยงั ได)้การตง้ั ชอ่ื Constant NameConstant Name เป็ นชอ่ื ทตี่ งั้ ใหก้ บั คา่ คงท่ี ซงึ่ ตามปกตใิ นการดําเนนิ ธรุ กจิ ตอ้ งเกย่ี วขอ้ งกบัคา่ คงทมี่ ากมาย นับตงั้ แต่ ชอ่ื บรษิ ัท ทอี่ ยู่ ชอ่ื ผบู ้ รหิ าร รหสั ทเี่ ป็ นความลบั หมายเลขบญั ชีตวั เลขทางการเงนิ ตา่ งๆ เชน่ อตั ราดอกเบย้ี คา่ เสอื่ มราคา ตวั เลขเงนิ เดอื น ดงั นัน้ แทนทจ่ี ะตอ้ งบนั ทกึ คา่ เหลา่ นซี้ าํ้ ลงไปในเซลลโ์ ดยตรง นอกจากจะเสยี เวลาพมิ พเ์ องใหมท่ กุ ครัง้ แลว้ ยงั อาจพลงั้ เผลอบันทกึ ขอ้ มลู ตา่ งไปจากเดมิ จงึ ขอแนะนําใหต้ งั้ ชอ่ื ใหก้ บั คา่ คงทที่ งั้ หมดทมี่ ี แทนที่จะตอ้ งพมิ พช์ อ่ื ยากๆยาวๆ ก็เพยี งพมิ พช์ อื่ สนั้ ๆทจี่ ํางา่ ยกวา่ และภายหลงั หากมกี ารเปลย่ี นแปลงคา่ ทเ่ี คยคดิ วา่ คงท่ี ก็ใหท้ ําการแกไ้ ขคา่ ทกี่ ําหนดใหก้ บั ชอ่ื ทเี่ ป็ น Constant Name นเี้ พยี งครัง้เดยี ว ก็จะสง่ ผลใหท้ กุ เซลลท์ น่ี ํา Constant Name ไปใชม้ คี า่ ใหมต่ ามการแกไ้ ขนัน้ ไดท้ ันทีสมมตวิ า่ ตอ้ งการตงั้ ชอ่ื Constant Name วา่ “MyKey1” ใหม้ คี า่ เทา่ กบั ชอื่ “Excel ExpertTraining” ใหเ้ รม่ิ จากพมิ พช์ อื่ ทตี่ อ้ งการตงั้ ลงไปในชอ่ ง Name (โดยไมต่ อ้ งใสเ่ ครอื่ งหมายคําพดู ปิดหวั ทา้ ยชอื่ ) จากนัน้ พมิ พค์ า่ ทต่ี อ้ งการลงไปในชอ่ ง Refers to โดยตอ้ งมเี ครอ่ื งหมาย สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 42 of 147  เทา่ กบั นําหนา้ เสมอและถา้ มคี า่ เป็ นตวั อกั ษรก็ตอ้ งใสข่ อ้ ความไวร้ ะหวา่ งเครอื่ งหมายคําพดู ดว้ ยตามนี้ Name: MyKey1 Refers to: =”Excel Expert Training”แตถ่ า้ ตอ้ งการตงั้ ชอ่ื Constant Name ใหม้ คี า่ เป็ นตวั เลข ก็ไมต่ อ้ งใสเ่ ครอ่ื งหมายคําพดู แตต่ อ้ งใสเ่ ครอื่ งหมายเทา่ กบั นําหนา้ เชน่ กนั ตามน้ี Name: MyKey2 Refers to: =23187021 จากนัน้ เมอื่ ตอ้ งการนําชอื่ Constant Name นไ้ี ปใชใ้ น เซลล์ ใหพ้ มิ พ์ =mykey1 (สงั เกตวา่ ถา้ พมิ พเ์ องให ้ พมิ พด์ ว้ ยตวั เล็กตลอด) หรอื กดป่ มุ F3 เพอื่ เปิดรายชอื่ ทต่ี งั้ ไวใ้ นแฟ้มนัน้ ขนึ้ มาแสดงบนจอ แลว้ ใหด้ บั เบลิ้ คลกิ ทช่ี อ่ื ทต่ี อ้ งการ (หรอื คลกิ ป่ มุ OK) แลว้ กดป่ มุ Enter เพอื่ รับสตู ร =MyKey1 ลงไปในเซลล์ จะไดค้ ําวา่ Excel Expert Training แสดงในเซลลน์ ัน้หมายเหตุ ชอื่ ทเ่ี ป็ น Range Name หรอื Formula Name มขี นั้ ตอนการใชช้ อ่ื เหมอื นกบัConstant Name เชน่ กนัการตง้ั ชอื่ Range NameRange Name เป็ นชอื่ ทต่ี งั้ ใหก้ บั เซลลเ์ ดยี วหรอื พนื้ ทต่ี าราง (ทม่ี พี น้ื ทต่ี ดิ กนั หรอื ไมต่ ดิ กนั ก็ได)้ซงึ่ มวี ธิ ตี งั้ ชอ่ื ไดห้ ลายวธิ ี ดงั นี้1. วธิ ใี ช้ Name Box เป็ นชอ่ งดา้ นซา้ ยมอื ของ Formula Bar วธิ นี เ้ี ป็ นวธิ ตี ัง้ ชอื่ ทงี่ า่ ยทส่ี ดุ แต่ ใชก้ บั การตงั้ ชอื่ ไดอ้ ยา่ งเดยี ว ไมส่ ามารถใช ้ Name Box เพอ่ื แกไ้ ขหรอื ลบชอื่ ทง้ิ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 43 of 147   1.1. เรม่ิ จากคลกิ เลอื กเซลลห์ รอื ตารางทตี่ อ้ งการ (หากตอ้ งการเลอื กเซลลท์ ไี่ มต่ ดิ ตอ่ กนั ให ้ กดป่ มุ Ctrl แลว้ คลกิ เลอื กพนื้ ทต่ี ารางทต่ี อ้ งการไปเรอ่ื ยๆ) 1.2. คลกิ ลกู ศรดา้ นขวาของชอ่ ง Name Box แลว้ พมิ พช์ อ่ื Range Name ทต่ี อ้ งการลงไป ในชอ่ ง Name Box ขอใหส้ งั เกตวา่ พอพมิ พต์ วั อกั ษรทลี ะตวั ของชอื่ ลงไป ชอ่ ง Name Box จะเลอื่ นไปหาชอื่ เกา่ ทตี่ งั้ ไวก้ อ่ นแลว้ เพอื่ คณุ จะไดต้ งั้ ชอ่ื ใหมท่ ไี่ มซ่ ํ้ากบั ชอื่ เดมิ ทมี่ ี อยแู่ ลว้ 1.3. กดป่ มุ Enter เพอ่ื รับชอื่ ลงไปในชอ่ ง Name Box2. วธิ ใี ชค้ าํ สง่ั Formulas > Define Name หรอื Formulas > Name Manager (Excel 2003 ใชค้ าํ สง่ั Insert > Name > Define) โดยกรอกชอ่ื ทต่ี อ้ งการตงั้ ลงไป ในชอ่ ง Name: แลว้ คลกิ ลงไปในชอ่ ง Refers to (โดยใหค้ ลกิ ตอ่ ทา้ ยตําแหน่งเดมิ ทแ่ี สดง ไวใ้ นชอ่ ง Refers to: น)้ี จากนัน้ ใหเ้ ลอื กพนื้ ทต่ี ารางทต่ี อ้ งการแลว้ กดป่ มุ OK เพอ่ื รับชอื่3. วธิ ใี ชค้ าํ สง่ั Formulas > Create from selection (Excel 2003 ใชค้ าํ สง่ั Insert > Name > Create) วธิ นี ช้ี ว่ ยนําขอ้ ความทอี่ ยขู่ อบตารางมาตงั้ เป็ นชอื่ ใหก้ บั พนื้ ทต่ี าราง ในฝั่งตรงขา้ มกบั ขอ้ ความนัน้ เรม่ิ จากคลกิ เลอื กพนื้ ทตี่ ารางทตี่ อ้ งการตงั้ ชอื่ โดยใหเ้ ลอื ก รวมทงั้ พน้ื ทห่ี วั ตารางไวด้ ว้ ย จากนัน้ ใชค้ ําสง่ั Formulas > Create from Selection จะพบ หนา้ จอตอ่ ไปนเี้ ปิดขนึ้ มา ใหด้ วู า่ ตรงตามตอ้ งการแลว้ กดป่ มุ OK เพอื่ รับชอื่ Excel เลอื กชอ่ ง Left column ไวใ้ ห ้ เนอื่ งจาก Column ซา้ ยสดุ ของพนื้ ทต่ี ารางทเี่ ลอื กนัน้ มตี วั อกั ษรบนั ทกึ ไว ้ โดย Excel จะตงั้ ชอื่ พน้ื ทต่ี าราง C2:E2 วา่ Income และตงั้ ชอ่ื พน้ื ที่ ตาราง C3:E3 วา่ Cost ตามขอ้ ความใน Column ซา้ ยสดุ ใหท้ นั ที ทงั้ นขี้ อใหเ้ ขา้ ใจดว้ ยวา่ สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 44 of 147   ชอื่ Income และ Cost เป็ นชอ่ื ท่ี Excel ชว่ ยตงั้ ใหเ้ พยี งครัง้ เดยี วตอนทใ่ี ชค้ ําสงั่ Create from selection นเ้ี ทา่ นัน้ และจะยงั คงใชช้ อื่ นอี้ ยโู่ ดยไมเ่ ปลย่ี นแปลงตามขอ้ ความใน Column ซา้ ยทอ่ี าจมขี อ้ ความตา่ งไปจากเดมิ แตอ่ ยา่ งใดหากตอ้ งการขยายขอบเขตของ Range Name ใหใ้ ชว้ ธิ กี าร Insert Row หรอื Insert Columnหรอื ใชว้ ธิ ที ี่ 2 หรอื วธิ ที ี่ 3 ขา้ งตน้ เพอ่ื เขา้ ไปกําหนดขอบเขตพน้ื ทตี่ ารางใหมใ่ หม้ ขี นาดใหญข่ น้ึตามตอ้ งการRange Name ทต่ี งั้ ขน้ึ สามารถนําไปใชอ้ า้ งองิ ผา่ นสตู รสําเร็จรปู ของ Excel ไดท้ นั ที เชน่ หากตอ้ งการหายอดรวมของตวั เลขใน Range Name ชอื่ Income ใหเ้ รมิ่ สรา้ งสตู รตามลําดบัตอ่ ไปนี้1. พมิ พ์ =sum(2. กดป่ มุ F3 เพอื่ แสดงรายชอื่ ทต่ี งั้ ไวใ้ นแฟ้ม (ในกรณีทตี่ อ้ งการนําชอื่ มาจากแฟ้มอน่ื ใหย้ า้ ย ไปอยใู่ นแฟ้มทม่ี ชี อื่ Range Name ทตี่ อ้ งการนัน้ กอ่ นแลว้ จงึ กดป่ มุ F3)3. ดบั เบล้ิ คลกิ ทชี่ อื่ Income จะไดส้ ตู ร =sum(Income4. กดป่ มุ Enter จะไดส้ ตู ร =SUM(Income)แตถ่ า้ นํา Range Name ไปใชอ้ า้ งองิ โดยตรงโดยไมไ่ ดใ้ ชอ้ า้ งองิ ผา่ นสตู รสําเร็จรปู ของ Excelจะพบวา่ หากตงั้ ชอ่ื ใหก้ บั ตารางทม่ี เี ซลลต์ งั้ แต่ 2 เซลลข์ นึ้ ไป ชอื่ นัน้ จะนํามาใชไ้ ดเ้ ฉพาะในแนวคขู่ นานกบั แนวพน้ื ทตี่ ารางทตี่ งั้ ชอ่ื ไวเ้ ทา่ นัน้ เชน่ หากตงั้ ชอื่ ตัวเลขหวั ตารางในตําแหน่งจากC2:F2 วา่ Top และตงั้ ชอื่ ตวั เลขขา้ งตารางตําแหน่งจาก B3:B5 วา่ Left จะสง่ ผลใหใ้ ชส้ ตู ร=Top*Left คํานวณหาคําตอบไดเ้ ฉพาะในขอบเขตตารางทแ่ี นวคขู่ นานของ Top ตดั กบั Leftคอื พน้ื ทต่ี ารางตงั้ แต่ C3:F5 โดยสตู รนอกแนวคขู่ นานจะไมค่ ํานวณแตค่ นื คา่ เป็ น Error#VALUE! สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 45 of 147  วธิ ตี งั้ ชอื่ ตาํ แหนง่ อา้ งองิ แบบไรข้ อบเขตจาํ กดัขอยอ้ นนําตวั อยา่ งการคํานวณทตี่ งั้ ชอ่ื หวั ตารางดา้ นบนวา่ Top มาคณู กบั หวั ตารางดา้ นซา้ ยทตี่ งั้ชอ่ื วา่ Left โดยใชส้ ตู ร =Top*Left มาวเิ คราะหอ์ ยา่ งละเอยี ดวา่ ดว้ ยเหตใุ ดสตู ร =Top*Leftจงึ สามารถคํานวณหาคําตอบไดเ้ ฉพาะพนื้ ทต่ี ารางในสว่ นทต่ี ดั กนั ของแนวคขู่ นานของ Top ตดักบั Left เทา่ นัน้ สว่ นพนื้ ทตี่ ารางนอกขอบเขตแนวทต่ี ดั กนั จะใหค้ ําตอบเป็ น Errorเมอ่ื ใชค้ ําสง่ั Formulas > Name Manager ยอ้ นไปตรวจสอบตําแหน่งอา้ งองิ ท่ี Excelกําหนดใหก้ บั ตําแหน่งอา้ งองิ ทม่ี ชี อ่ื วา่ Top กบั Left จะพบวา่ เป็ นตําแหน่งอา้ งองิ แบบAbsolute โดยมเี ครอ่ื งหมาย $ ควบคมุ ตําแหน่งทงั้ แนว Row และ Column ตามรปู ตอ่ ไปน้ี  ตําแหน่งอา้ งองิ ชอ่ื Top มตี ําแหน่งอา้ งองิ Refers to: =Sheet1!$C$2:$F$2  ตําแหน่งอา้ งองิ ชอ่ื Left มตี ําแหน่งอา้ งองิ Refers to: =Sheet1!$B$3:$B$5ดว้ ยสาเหตทุ ตี่ ําแหน่งอา้ งองิ ถกู ควบคมุ ดว้ ยเครอื่ งหมาย $ ทงั้ แนว Row และ Column นเี่ องท่ีทําใหเ้ กดิ การจํากดั ขอบเขตใหใ้ ชส้ ตู รอา้ งองิ ถงึ ชอื่ Top กบั Left ไดเ้ ฉพาะในแนวคขู่ นานดงั นัน้ หากคณุ เขา้ ใจถงึ เหตแุ ละผลตามทไ่ี ปทม่ี าดงั กลา่ วขา้ งตน้ จะสามารถเลอื กใชเ้ ครอ่ื งหมาย$ ในการกําหนดขอบเขตแบบไมจ่ ํากดั ได ้ โดยปรับตําแหน่งเครอื่ งหมาย $ ตามหลกั ตอ่ ไปนี้  หากตอ้ งการใหก้ ําหนดตําแหน่งอา้ งองิ ใหห้ มายถงึ ทกุ เซลลใ์ นแนวนอน ใหใ้ ช ้ ตําแหน่งอา้ งองิ จากเซลลเ์ ดยี วตามแนวคขู่ นานกบั เซลลท์ จี่ ะใชช้ อื่ นัน้ และใส่ เครอ่ื งหมาย $ ควบคมุ ตาํ แหนง่ เฉพาะ Row เชน่ C$2  หากตอ้ งการใหก้ ําหนดตําแหน่งอา้ งองิ ใหห้ มายถงึ ทกุ เซลลใ์ นแนวตง้ั ใหใ้ ชต้ ําแหน่ง อา้ งองิ จากเซลลเ์ ดยี วตามแนวคขู่ นานกบั เซลลท์ จี่ ะใชช้ อ่ื นัน้ และใสเ่ ครอื่ งหมาย $ ควบคมุ ตาํ แหนง่ เฉพาะ Column เชน่ $B3 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 46 of 147    หากตอ้ งการใหก้ ําหนดตําแหน่งอา้ งองิ ใหห้ มายถงึ ทกุ แนว ใหใ้ ชต้ ําแหน่งอา้ งองิ จาก เซลลเ์ ดยี วตามแนวคขู่ นานกบั เซลลท์ จี่ ะใชช้ อ่ื นัน้ และไมใ่ สเ่ ครอื่ งหมาย $  หากตอ้ งการใหต้ ําแหน่งอา้ งองิ ใชไ้ ดก้ บั ทกุ ชที กไ็ มต่ อ้ งระบชุ อ่ื ชที นําหนา้ ตําแหน่ง อา้ งองิ แตต่ อ้ งทง้ิ เครอื่ งหมาย ! ไวด้ ว้ ยเพอ่ื หมายถงึ ทกุ ชที เชน่ !I5เนอ่ื งจากตําแหน่งอา้ งองิ แบบทไี่ มไ่ ดใ้ ส่ $ แบบ Absolute นี้ จะยา้ ยตําแหน่งไปตามแนวคขู่ นานกบั ตําแหน่งเซลลส์ ตู รทใ่ี ช ้ ดงั นัน้ กอ่ นทจ่ี ะใชค้ ําสงั่ Formulas > Name Manager เขา้ ไปกําหนดตําแหน่ง คณุ จะตอ้ งเลอื กเซลลใ์ นแนวเดยี วกบั เซลลท์ อี่ า้ งถงึ ในชอ่ ง Refers to: ไว ้ลว่ งหนา้ เสมอ ตามรปู ตวั อยา่ งตอ่ ไปน้ี1. หากตอ้ งการตงั้ ชอื่ TopRow และ LeftColumn (หรอื ชอื่ ใดก็ไดต้ ามใจชอบ) ใหม้ ี ตําแหน่งอา้ งองิ ทหี่ มายถงึ ทกุ เซลลใ์ น Row 2 และทกุ เซลลใ์ น Column B ตามลําดบั 1.1. ใหค้ ลกิ เลอื กเซลล์ C3 ไวก้ อ่ นแลว้ จงึ สง่ั Formulas > Name Manager 1.2. ตงั้ ชอ่ื TopRow ใหม้ ตี ําแหน่งอา้ งองิ Refers to: =Sheet1!C$2 1.3. ตงั้ ชอื่ LeftColumn ใหม้ ตี ําแหน่งอา้ งองิ Refers to: =Sheet1!$B32. หากตอ้ งการตงั้ ชอ่ื Sum3Above (หรอื ชอื่ ใดกไ็ ดต้ ามใจชอบ) ใหท้ ําหนา้ ทห่ี ายอดรวม ของเซลล์ 3 เซลลท์ อี่ ยเู่ หนอื ขนึ้ ไปจากเซลลท์ ใี่ ชช้ อื่ นอ้ี ยโู่ ดยเวน้ เซลลท์ ตี่ ดิ กบั เซลลส์ ตู ร 2.1. ใหค้ ลกิ เลอื กเซลล์ C13 ไวก้ อ่ นแลว้ จงึ สง่ั Formulas > Name Manager 2.2. ตงั้ ชอ่ื Sum3Above ใหม้ ตี ําแหน่งอา้ งองิ Refers to: =SUM(Sheet1!C9:C11)3. หากตอ้ งการตงั้ ชอื่ Left2 (หรอื ชอ่ื ใดกไ็ ดต้ ามใจชอบ) ใหท้ ําหนา้ ทหี่ าคา่ จากเซลลท์ อี่ ยู่ ดา้ นซา้ ยถดั ไป 2 เซลลจ์ ากเซลลท์ ใี่ ชช้ อ่ื นอี้ ยแู่ ละใหใ้ ชก้ บั ทกุ ชที ไดด้ ว้ ย 3.1. ใหค้ ลกิ เลอื กเซลล์ G10 ไวก้ อ่ นแลว้ จงึ สง่ั Formulas > Name Manager 3.2. ตงั้ ชอ่ื Left2 ใหม้ ตี ําแหน่งอา้ งองิ Refers to: =!E10 สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 47 of 147  การตงั้ ชอื่ Formula Nameในความเป็ นจรงิ นัน้ ทัง้ Constant Name หรอื Range Name ตา่ งกค็ อื Formula Name น่ันเองเพราะในชอ่ ง Refers to: ตอ้ งใสเ่ ครอื่ งหมายเทา่ กบั ไวด้ า้ นหนา้ คา่ คงทห่ี รอื ตําแหน่งอา้ งองิ ไว ้เสมอ จงึ เทา่ กบั วา่ เป็ นการตงั้ ชอื่ ใหก้ บั สตู ร เพยี งแตว่ า่ สตู รทเี่ ป็ น Constant Name กําหนดใหม้ ีคา่ เป็ นคา่ คงที่ สว่ น Range Name ไดก้ ําหนดใหม้ คี า่ เทา่ กบั คา่ ทเี่ ก็บไวใ้ นตารางตามทรี่ ะบไุ วใ้ นชอ่ ง Refers to: ซงึ่ นอกจากจะอา้ งถงึ คา่ คงทห่ี รอื ตําแหน่งอา้ งองิ ตามทอี่ ธบิ ายผา่ นไปแลว้ เรายงั สามารถใชช้ อ่ ง Refers to: ไดอ้ กี หลายแบบ กลา่ วคอื  ใชผ้ สมกนั ระหวา่ งคา่ คงทแ่ี ละตําแหน่งอา้ งองิ เชน่ =5*Top*Left โดยเลข 5 ถอื เป็ น คา่ คงท่ี สว่ น Top กบั Left เป็ นตําแหน่งอา้ งองิ  ใชร้ ว่ มกบั สตู รสําเร็จรปู ของ Excel เชน่ =SUM(Income)  ใชย้ อ่ สตู รยากๆยาวๆใหเ้ ป็ นชอ่ื สนั้ ๆเพยี งชอ่ื เดยี ว โดยทยอยนําชอื่ ทต่ี งั้ ไวม้ าคํานวณใน สตู รยาวๆทคี่ ณุ สรา้ งขนึ้ เชน่ แทนทจี่ ะตงั้ ชอ่ื เดยี วใหก้ บั สตู ร =(5*Top*Left)+Sum(Income) ก็สามารถตงั้ ชอ่ื ใหก้ บั สตู รแตล่ ะสว่ นกอ่ นแลว้ จงึ นําชอ่ื มาใชต้ อ่ เป็ น =MyVar1+MyVar2 โดยกําหนดใหต้ งั้ ชอ่ื MyVar1 มคี า่ =5*Top*Left และตงั้ ชอื่ MyVar2 มคี า่ =SUM(Income) ไวก้ อ่ น  ใชก้ ําหนดตําแหน่งอา้ งองิ แบบ Dynamic Range โดยใชส้ ตู ร Offset ชว่ ยกําหนดขนาด ตารางใหย้ อ่ หรอื ขยายไดโ้ ดยอตั โนมัตติ ามขนาดของขอ้ มลูการเปลยี่ นตาํ แหนง่ อา้ งองิ ในสตู รทม่ี อี ยกู่ อ่ นใหก้ ลายเป็ นชอ่ื ทต่ี งั้ ขนึ้ ในภายหลงัสมมตวิ า่ สรา้ งสตู ร =C2-C3 เอาไวใ้ นเซลล์ C4 เป็ นตวั เลขกําไรขนั้ ตน้ แลว้ Copy สตู รนไี้ ปใช ้ในเซลลต์ งั้ แต่ C4:E4 จากนัน้ ตอ่ มาไดต้ งั้ ชอ่ื Income และ Cost เป็ น Range Name ใหก้ บัพน้ื ทตี่ ารางตงั้ แต่ C2:E2 และ C3:E3 ตามลําดบั หากตอ้ งการใหส้ ตู รในเซลล์ C4:E4 เปลย่ี นไปเป็ นสตู ร =Income-Cost ตามชอื่ ทต่ี ัง้ ขนึ้ ในภายหลงั ใหเ้ ลอื กพน้ื ท่ี C4:E4 แลว้ สง่ั Formulas> Define Name > Apply Names (Excel 2003 สง่ั Insert > Name > Apply) แลว้เลอื กชอ่ื Income และ Cost (ซง่ึ Excel มกั จะเลอื กชอ่ื ทเี่ กยี่ วขอ้ งกบั สตู รในพนื้ ที่ C4:E4 ให ้เอง) จากนัน้ กดป่ มุ OK สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 48 of 147  เคล็ดลบั อน่ื ๆทค่ี วรทราบเกยี่ วกบั ชอื่ ทบ่ี อกตาํ แหนง่1. ชอ่ ง Name Box แสดงไดเ้ ฉพาะชอ่ื Range Name ทอี่ ยใู่ นแฟ้มนัน้ เทา่ นัน้2. การคน้ หาตําแหน่งเซลลห์ รอื ตารางทต่ี งั้ ชอื่ ไว ้ ใหใ้ ช ้ Name Box หรอื กดป่ มุ F5 เพอื่ เลอื ก ชอื่ ทแ่ี สดงไวห้ รอื พมิ พช์ อื่ ทท่ี ราบลงไปเองก็ได ้3. หากตอ้ งการ Copy ชอื่ ตําแหน่งอา้ งองิ ไปใชท้ แ่ี ฟ้มอนื่ ใหม้ ชี อื่ แบบเดยี วกนั ใหเ้ ลอื ก Copy พนื้ ทต่ี ารางทงั้ หมดทเี่ กย่ี วขอ้ งและสตู รไปดว้ ยกนั (ซง่ึ หมายถงึ พน้ื ทต่ี ารางทร่ี ะบไุ วใ้ น ตําแหน่งอา้ งองิ และเซลลส์ ตู รทอี่ า้ งถงึ ชอื่ ตําแหน่งอา้ งองิ ซง่ึ อยใู่ นพน้ื ทที่ เี่ ลอื กนัน้ ดว้ ย) จากนัน้ ให ้ Paste ลงไปในแฟ้มใหมต่ ามตาํ แหนง่ เดมิ ของพน้ื ทต่ี ารางท่ี Copy มา แลว้ จงึ สง่ั ยา้ ยไปทอี่ น่ื ในภายหลงั ตามตอ้ งการ4. เซลลเ์ ดยี วมชี อื่ ไดห้ ลายชอื่ แตช่ อ่ ง Name Box แสดงชอื่ แรกทตี่ งั้ ตามลําดบั ตวั อกั ษร เชน่ เซลล์ A1 ตงั้ ชอื่ Range Name ไวห้ ลายชอื่ ทงั้ Income, Sales, และ Revenue จะพบวา่ เมอื่ คลกิ เขา้ ไปทเ่ี ซลล์ A1 ในชอ่ ง Name Box จะแสดงวา่ มชี อ่ื วา่ Income เพราะตวั อกั ษร I มากอ่ นตวั อกั ษร R หรอื S แตถ่ า้ ไลด่ รู ายชอ่ื ใน Name Box จะพบชอื่ อนื่ แสดงไวด้ ว้ ย5. ตวั ชอื่ Range Name กบั ตําแหน่งอา้ งองิ ในชอ่ ง Refers to: ไมจ่ ําเป็ นตอ้ งอยใู่ นแฟ้ม เดยี วกนั กไ็ ด ้ โดยเราสามารถตงั้ ชอื่ ไวใ้ นแฟ้มหนง่ึ แตก่ ําหนดใหต้ ําแหน่ง Refers to: ไปอยู่ ในแฟ้มอนื่ ได ้ โดยใช ้ Mouse คลกิ เลอื กตําแหน่งตารางในแฟ้มอน่ื ก็จะไดต้ ําแหน่งทต่ี อ้ งการ มาใสใ่ นชอ่ ง Refers to: ใหเ้ อง6. หาก Cut ตารางทตี่ งั้ ชอ่ื Range Name เอาไวไ้ ปวางไวใ้ นแฟ้มอนื่ จะทําใหต้ วั ชอื่ Range Name ยงั อยใู่ นแฟ้มเดมิ แตต่ ําแหน่งอา้ งองิ ในชอ่ ง Refers to: จะยา้ ยตามไปทแ่ี ฟ้มอน่ื และยงั สง่ ผลใหช้ อ่ ง Name Box ไมแ่ สดงชอื่ Range Name นัน้ อกี ตอ่ ไป (โดยยงั คงดชู อื่ ทกุ ประเภทไดจ้ ากคําสง่ั Formulas > Name Manager)7. หากตอ้ งการหาคา่ ณ ตําแหน่งเซลลท์ ตี่ ดั กนั ของตารางทตี่ งั้ ชอื่ ไว ้ (Intersection) ใหใ้ ช ้ สตู รทม่ี เี ครอื่ งหมายวรรคคนั่ ระหวา่ งชอื่ เชน่ =Top Left จะไดค้ า่ จากเซลลท์ ที่ ต่ี ําแหน่งตดั กนั ของ Top ตดั กบั Left8. หากตอ้ งการสรา้ งสตู รทอ่ี า้ งถงึ ตําแหน่งอา้ งองิ ในตารางโดยตรง โดยไมต่ อ้ งการใหส้ ตู รที่ เกดิ ขนึ้ ใชช้ อื่ Range Name ใหเ้ รมิ่ จากเลอื กเซลลต์ งั้ แต่ 2 เซลลข์ น้ึ ไปไวก้ อ่ นแลว้ จงึ พมิ พ์ = แลว้ คลกิ เลอื กเซลล์ จะไดส้ ตู รทมี่ ตี ําแหน่งอา้ งองิ โดยไมอ่ า้ งถงึ ชอื่ Range Name9. Formula Name ทใ่ี ชต้ ําแหน่งอา้ งองิ ทไ่ี มใ่ ช่ Absolute Reference ในบางกรณีจะไม่ คํานวณตามคา่ ตวั แปรทเี่ ปลยี่ นไป ซงึ่ แกไ้ ขไดโ้ ดยปรับสตู รใหค้ ํานวณรว่ มกบั สตู ร Now() หรอื Today() เชน่ Sum3Above ใช ้ Refers to: =SUM(Sheet1!C9:C11)*Now()/Now()10. ชอื่ ทตี่ งั้ ไวจ้ ะถกู เก็บไวใ้ นแฟ้มทใี่ ชต้ งั้ ชอื่ เทา่ นัน้ หากตอ้ งการทําใหแ้ ฟ้มใหมม่ ชี อื่ ทต่ี งั้ ไว ้ แบบเดยี วกบั แฟ้มเดมิ ให ้ Copy เซลลส์ ตู รทใี่ ชช้ อื่ ในการอา้ งองิ ถงึ ตําแหน่งเซลลไ์ ปพรอ้ ม กบั เซลลท์ ถี่ กู ตงั้ ชอ่ื ไวไ้ ปดว้ ยกนั และในการสงั่ Paste ในแฟ้มใหม่ ตอ้ ง Paste ลงไปใน ตําแหน่งเดมิ ดว้ ย จงึ จะทําใหแ้ ฟ้มใหมเ่ กดิ ชอื่ และมตี ําแหน่งอา้ งองิ ถกู ตอ้ งตามแฟ้มเดมิ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 49 of 147  การใชส้ ตู รปรบั ตาํ แหนง่ อา้ งองิคําอธบิ ายจากนไ้ี ปจะมปี ระโยชนต์ อ่ เมอื่ คณุ มพี นื้ ฐานมากอ่ นแลว้ วา่ ตําแหน่งอา้ งองิ มปี ระโยชน์อยา่ งไรและจะนําตําแหน่งอา้ งองิ ไปใชซ้ าํ้ โดยการ Copy หรอื โดยการตงั้ ชอ่ื ตําแหน่งอา้ งองิ ได ้อยา่ งไร ซงึ่ การนําตําแหน่งอา้ งองิ ไปใชซ้ าํ้ ตามวธิ ที ใี่ ชก้ นั โดยทวั่ ไป คณุ ตอ้ งเสยี แรงเสยี เวลาCopy หรอื ตอ้ งสรา้ งสตู รใหมท่ กุ ครัง้ จงึ เป็ นเรอื่ งน่าคดิ วา่ จะมวี ธิ ใี ดบา้ งทจี่ ะทําใหต้ ําแหน่งอา้ งองิ เดมิ ทม่ี อี ยปู่ รับตําแหน่งไดเ้ องโดยคณุ ไมต่ อ้ งเขา้ ไปยงุ่ เกย่ี วแกไ้ ขสตู รใหมอ่ กี เลยวธิ ที ่ี 1 : ใชส้ ตู ร Indirect(“Text บอกตาํ แหนง่ ”) แบบเซลลเ์ ดยี วลองนกึ ดซู วิ า่ ถา้ เดมิ คณุ สรา้ งสตู รเพอ่ื ดงึ คา่ จากเซลล์ A1 มาใชด้ ว้ ยสตู ร =A1 จากนัน้ หากตอ้ งการทําใหส้ ตู รในเซลลเ์ ดมิ น่ันแหละ ดงึ คา่ จากเซลล์ A2 มาแทน จะทําไดอ้ ยา่ งไรโดยไม่ตอ้ งยอ้ นกลบั ไปแกไ้ ขสตู รใหเ้ ปลยี่ นเป็ น =A2 แตอ่ ยา่ งใดแทนทจี่ ะสรา้ งสตู รตรงๆลงไปวา่ =A1 คราวนใ้ี หย้ อมเปลอื งเซลลห์ น่อย เรม่ิ จากใชเ้ ซลล์ C4และ C5 เป็ นเซลลร์ ับตวั อกั ษร a และเลข 1 ลงไปตามลําดบั จากนัน้ นําตวั อกั ษรกบั ตวั เลขมาตอ่กนั ในเซลล์ C6 โดยใชส้ ตู ร =C4&C5 จะไดค้ ําวา่ a1 ซงึ่ เป็ น Text ทบี่ อกตําแหน่งตามแบบมาตรฐานของตําแหน่งอา้ งองิ ใน Excel (ถา้ อยากลดั ขนั้ ตอน จะใชเ้ ซลล์ C6 สําหรับพมิ พ์ตําแหน่งเซลล์ a1 ลงไปเลยก็ได ้ โดยไมต่ อ้ งเสยี เซลล์ C4 และ C5 เพอ่ื แยกตําแหน่งแตล่ ะสว่ นไวก้ อ่ น)จากนัน้ เซลล์ C7 ใชส้ ตู ร =Indirect(C6) จะไดค้ า่ เป็ นตวั เลข 11 ซงึ่ เป็ นคา่ จากเซลล์ A1 นั่นเองทนี หี้ ากตอ้ งการคา่ จากเซลลใ์ ด ใหใ้ ชต้ ําแหน่งอา้ งองิ ตามคา่ ในเซลล์ C6 ซง่ึ อาจเกดิ จากการพมิ พแ์ ยกสว่ นของตวั อกั ษรและตวั เลขทบี่ อกตําแหน่ง Column และตําแหน่ง Row ลงไปในเซลล์ C4 และ C5 หรอื จะใชว้ ธิ พี มิ พต์ ําแหน่งเซลลท์ ตี่ อ้ งการลงไปเองโดยตรงในเซลล์ C6 เลยกไ็ ด ้ เชน่  ถา้ พมิ พค์ ําวา่ a2 ลงไปในเซลล์ C6 กจ็ ะไดค้ ําตอบในเซลล์ C7 เทา่ กบั คา่ จากเซลล์ a2  ถา้ พมิ พค์ ําวา่ a3 ลงไปในเซลล์ C6 ก็จะไดค้ ําตอบในเซลล์ C7 เทา่ กบั คา่ จากเซลล์ a3  ถา้ พมิ พค์ ําวา่ sheet3!a2 ลงไปในเซลล์ C6 ก็จะไดค้ ําตอบในเซลล์ C7 เทา่ กบั คา่ จาก เซลล์ a2 แตค่ ราวนมี้ าจากชที ชอ่ื Sheet3 เป็ นสตู รดงึ คา่ ขา้ มชที ตามชอื่ ชที ทต่ี อ้ งการ สดุ ยอดเคล็ดลบั และลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com

Page 50 of 147  วธิ ที ่ี 2 : ใชส้ ตู ร Indirect(“Text บอกตาํ แหนง่ ”) แบบใชต้ าํ แหนง่ ขอบเขตตารางวธิ นี ม้ี หี ลกั การคลา้ ยกบั วธิ แี รกทใ่ี ชต้ ําแหน่งอา้ งองิ จากเซลลเ์ ดยี ว เพยี งแตต่ ําแหน่งอา้ งองิ แบบขอบเขตตารางตอ้ งอยใู่ นโครงสรา้ งของ ตาํ แหนง่ เซลลต์ น้ ทาง:ตาํ แหนง่ เซลลป์ ลายทางเชน่ A1:A3 จากนัน้ จงึ นําตําแหน่งอา้ งองิ ทไ่ี ดไ้ ปใชก้ บั สตู ร Indirectสมมตวิ า่ คณุ ตอ้ งการหาผลรวมของตวั เลขในเซลล์ A1:A3 ซง่ึ ปกตติ อ้ งใชส้ ตู ร =Sum(A1:A3)ซง่ึ สตู รนยี้ งั ไงๆก็จะหาผลรวมจากเซลล์ A1:A3 ไปตลอด แตถ่ า้ ตอ้ งการใหห้ ายอดรวมจากเซลล์ใดก็ไดถ้ งึ เซลลใ์ ดก็ไดล้ ะ่ จะสรา้ งสตู รอยา่ งไรครัง้ เดยี วแลว้ ไมต่ อ้ งแกไ้ ขสตู รอกี เลยในภายหลงัจากภาพขา้ งตน้ มี Text ทบี่ อกตําแหน่งเซลลต์ น้ ทาง a1 ในเซลล์ C6 ตรงกบั ตวั อยา่ ง Indirectแบบเซลลเ์ ดยี ว เพยี งแตค่ ราวนใ้ี หใ้ ชเ้ ซลล์ E4, E5, และ E6 สําหรับสรา้ ง Text ทบี่ อกตําแหน่งของเซลลป์ ลายทาง a3จากนัน้ ใชเ้ ซลล์ D8 นําตําแหน่งเซลลต์ น้ ทางมาเชอื่ มตอ่ กบั เครอื่ งหมาย : แลว้ เชอ่ื มตอ่ กบัตําแหน่งเซลลป์ ลายทางดว้ ยสตู ร =C6&”:”&E6 จะไดค้ า่ a1:a3หากตอ้ งการหายอดรวมก็สามารถนํา Text ทบ่ี อกตําแหน่งในเซลล์ D8 ไปใชร้ ว่ มกบั สตู ร Sumและ Indirect เชน่ เซลล์ D9 ใชส้ ตู ร =Sum(Indirect(D8)) จะหายอดรวมตวั เลขจากตารางในขอบเขตทตี่ อ้ งการนอกจากนแ้ี ทนทจ่ี ะนําคา่ ในเซลล์ D8 ซงึ่ มสี ตู รเป็ น =C6&”:”&E6 ไปใชต้ อ่ ในสตู ร Sum เพอ่ืหาผลรวม เรายงั สามารถนําขอบเขตตําแหน่งอา้ งองิ ทไี่ ดน้ ไ้ี ปใชใ้ นสตู รอนื่ ไดเ้ ชน่ เดยี วกนัเพยี งแตต่ อ้ งใชส้ ตู ร Indirect นําหนา้ ตําแหน่งอา้ งองิ ไวเ้ สมอ เชน่ =Max(Indirect(D8)),=Min(Indirect(D8)), =Average(Indirect(D8)), =Index(Indirect(D8),2,1) เป็ นตน้ยง่ิ กวา่ นัน้ หากนําสตู ร =$C$6&”:”&$E$6 หรอื =Indirect($C$6&”:”&$E$6) ไปตงั้ ชอื่ เป็ นFormula Name วา่ MyRange1 และ MyRange2 ตามลําดบั (หรอื ชอ่ื อน่ื ใดก็ได ้ โดยตําแหน่งอา้ งองิ ทใ่ี ชใ้ น Formula Name ตอ้ งกําหนดตําแหน่งแบบ Absolute จงึ จะหาคา่ ไดถ้ กู ตอ้ ง) จะชว่ ยใหก้ ารนําตําแหน่งอา้ งองิ ไปใชต้ อ่ ไดส้ ะดวกขน้ึ เชน่ จากเดมิ ตอ้ งใชส้ ตู ร=Sum(Indirect(D8)) กจ็ ะเทยี บเทา่ กบั สตู ร =Sum(Indirect(MyRange1)) หรอื=Sum(MyRange2) ซง่ึ นอกเหนอื จากจะเป็ นสตู รทด่ี ดู กี วา่ มากแลว้ ยงั ไมข่ น้ึ กบั คา่ ในเซลล์ D8ทอ่ี าจถกู แกไ้ ขอยา่ งพลงั้ เผลอใหผ้ ดิ เพย้ี นไปในภายหลงั ไดอ้ กี ดว้ ย สดุ ยอดเคล็ดลับและลดั ของ Excel © พ.ศ. 2553 โดย สมเกยี รติ ฟ้งุ เกยี รติ www.ExcelExpertTraining.com