1 Copyright of www.ExcelExpertTraining.com ฉลาดใชสารพ ้ ัดสตร ู Excel อยางม ่ ออาช ื พ ี โดย สมเกยรตี ิฟ้งเกุยรตี ิ www.ExcelExpertTraining.com www.XLSiam.com [email protected] โทร 02 718 9331 Fax 02 318 7021 สงวนลขสิทธิตามกฎหมายิ์ 17 เมษายน 2555

สารบัญ Show

ไฟล์งาน Microsoft Excel 2016 ที่บันทึกแล้วจะมีนามสกุลใด
การคัดลอกข้อมูล Excel มีกี่วิธี
Excel มีหน้าที่และประโยชน์อย่างไร
ไฟล์งาน Microsoft Excel ที่มีเวอร์ชั่นต่ำกว่า 2007 ที่บันทึกแล้วจะมีนามสกุลใด

2 Copyright of www.ExcelExpertTraining.com สารบญ ั 1. วธิปร ี ับ Excel ใหพร้อมต้ อการใช ่งานอย้างม่ ออาช ืพี 6 2. การกําหนดตําแหน่งอางอ้งิ 9 3. วธิคี นหาส ้ตรู 17 4. วธิแกะส ีตรู 21 5. วธิสร ี างส ้ ตรซูอนส้ตรู 23 6. วธิสร ี างช ้ อซื่อนช้อื่ 25 7. เรองทื่ไมี่ค่อยร่กู้นเกัยวกี่บสัตรู 26 8. การแบงขอบเขตพ่นทื้ตารางี่ 41 9. การกําหนดตําแหน่งอางอ้งขิ ามช ้ทหรีอขื ามแฟ ้ ้ม 44 10. หลกการตั งชั้อและใช ื่ช้ อใน ื่ Excel 54 11. การใชส้ตรปรูับตําแหน่งอางอ้งิ 66 12. การกําหนดตําแหน่งอางอ้งแบบวงกลมิ 71 13. แป้นพมพิล์ดทัเกี่ยวขี่องก้บตั ําแหน่งอางอ้งิ 78 14. หลกการแยกตั วแปร ั 79 15. วธิใชี Goal Seek 80 ้

3 Copyright of www.ExcelExpertTraining.com 16. วธิใชี Data Table 85 ้ 17. หลากหลายแนวทางการใชส้ตรู IF 90 18. การใชส้ตรู IF ในการตดสั นใจ ิ 95 19. หลบรอนหน้ สีตรู IF ไปหาทางออกอนื่ 104 20. หนสีตรู IF ไปใชค้ําสงั่ Format 114 21. วธิใชี Data Validation 115 ้ 22. วธิใชี Conditional Formatting 116 ้ 23. สตรู Array 118 24. สตรู Array IF เพอใช ื่หายอดรวมแยกประเภท ้ 130 25. ของจรงิ ของปลอม ของใชไม้ ได่ ใน้ Excel 143 26. หลกการใช ัว้นทัและเวลาี่ 147 27. หลกการกั ําหนด Format Cells > Number 151 28. วธิใชี Add-In 155 ้ 29. Function VBA 157 30. General Functions 202 31. Day and Time Formulas 224

4 Copyright of www.ExcelExpertTraining.com 32. List & Database Formulas 246 33. Conditional Formatting & Data Validation Formulas 248 34. Array Formulas 253 35. การกําหนดขอบเขตตารางแบบยดหยืนุ่ 281 36. การจัดการคาซ่ ํ้ า 297 37. การคํานวณยอดรวมเพอจื่ัดทํารายงาน 304 38. วธิสร ี างส ้ ตรบนกราฟู 312 39. วธิจี ัดการกบความอัวนอ้ยอุ้าย้ ...ของแฟ้ม 315 40. การออกแบบโครงสรางตารางค้ ํานวณ 325

5 Copyright of www.ExcelExpertTraining.com คาน ํ ํ า ในระยะเวลาหลายสบปิ ีทผี่านมา่แม Excel ้ถกพูัฒนาใหเป้ ็นโปรแกรมทเปี่ ็นประโยชนมากข์นึ้มี คําสงและมั่เครีองมื่อใหม ืๆเตร่ ยมไว ี ให้เล้ อกใช ืมากข้นกวึ้าแต่ก่อนก่ ็ตาม ทงๆทั้ Excel ี่จะเป็น Excel ไดก้็เพราะการมสีตรเพูอใช ื่ในการค ้ ํานวณ แตต่ วสัตรทู Excel ี่มอยีกลู่บมั การเปล ี ยนแปลง ี่ นอยมาก้มสี ตรใหมูท่จี่ําเป็นตอการใช ่งานเก้ดขินเพึ้ยงเลี ็กนอยเท้าน่ ัน้สวนท่ เปล ี่ยนแปลงส ี่ตรู ใน Excel 2010 ก็มงปรุ่ ับปรงใหุม้ความละเอี ยดในการค ี ํานวณยงขิ่นึ้ หลกสั ตรฉลาดใชูสารพ้ ัดสตรู Excel อยางม่ ออาช ืพกี ็ผานร่อนผ้ านหนาวมาหลายป ่ ีเชนก่นั หลกสัตรนูมี้งใหุ่ผ้ ใชู้ Excel ้รจู้ ักคดิฉลาดเลอกใช ื ส้ตรู Excel ใหถ้กกูบสถานการณ ั ์โดยเลอกื เฉพาะสตรทูมี่ความจี ําเป็นตองน้ ํามาใชงานข้นมาอบรมเพึ้อทราบขื่อด้ ีขอเส้ยีและขอควรระว้งั แนะนําวธิคี ดออกแบบตารางให ิ เหมาะสมก ้บการคั ํานวณ และหาทางกระตนใหุ้ผ้เขู้าอบรมเร้ยนรีู้ วธิแกี ป้ ัญหาไดด้วยต้วเองั อยางไรก ่ ็ตามเนองจากระยะเวลาอบรมมื่เพียงี 3 วนั ไมสามารถแนะน ่ ําเรองราวและถื่ายทอด่ ประสบการณ์การใชส้ตรไดูครบถ้วน้จงขอแนะนึ ําใหอ้านค่มู่อเลืมน่ ซี้งมึ่เนีอหาทื้อาจไม ี่ม่ โอกาส ี หยบยกขินมาอบรมอยึ้อู่กมากี คมู่ อประกอบการอบรมเล ืมน่ ไดี้รวบรวมเน้ อหาส ื้ํ าคญของหลายหลั กสัตรูทงหลั้กสั ตรสูดยอดุ เคล็ดลบและลัดของั Excel หลกสัตรหูนมาใช ั Excel ้จัดการฐานขอม้ลแทนู Access กนดักวีา่ และหลกสั ตรประยูกตุใช์ Excel ้เพอวางแผนความตื่องการว้ สดัและจุดตารางการผลัติตลอดจน การใชส้ตรในู VBA อกที ังได้น้ําตวอยัางค่ ําถามคําตอบและสตรแบบแปลกๆจากเว ู็บหลายแหง่ มารวมไว ้จงหวึงวัาผ่เขู้ าอบรมจะได ้ เป้ ิดหเปูิดตาและไดแนวทางน้ ํากลบไปสร ัางงานของตน้ ประเด็นสํ าคญของการสร ั างส ้ตรูคอืตองร้จู้ ักคดและสร ิ างส ้ ตรไดูด้วยต้วเองัมใชิ ลอกส่ตรของู คนอนมาใช ื่แล้วแกะหร้อแกื ไขต ้ อให ่ด้ขีนกวึ้าเด่ มไม ิ เป่ ็น สมเกยรตี ิฟ้งเกุยรตี ิ 17เมษายน 2555

6 Copyright of www.ExcelExpertTraining.com วธิปรี บ ั 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 จะยายกล้บมาทั เซลล ี่เด์มทิ ี่ เคยดบเบัลคลิกิ

7 Copyright of www.ExcelExpertTraining.com 3. ตองการเปล ้ยนระบบการคี่านวณเป ํ ็น manual สงั่ File > Options > Formulas > กา ชอง่ Manual จากนันต้องกดป ้ ่มุ F9 เพอสื่งคั่ํานวณทกแฟุ้มหรอกดื Shift+F9 เพอสื่งั่ คํานวณเฉพาะแฟ้มทเลี่อกอยืเทู่าน่ ัน้ (Excel 2003 : Tools > Options > Calculation > กาชอง่ Manual) หมายเหตุระบบการคํานวณจะเปลยนตามระบบของแฟ ี่้มแรกทเปี่ิดขนกึ้อนเสมอ ่ 4. ตองการเป ้ ิดใหใช้ Macro ้และ Update Links ไดอ้ ตโนม ัตัทิ กแฟุ้มในโฟลเดอรท์ ี่ กาหนดวํ าปลอดภ ่ยั สงั่ File > Options > Trust Center > กดป่มุ Trust Center Settings... (Excel 2003 : Tools > Macro > Security > Security Level > กาชอง่ Medium) เลอกเมนืู Trusted Locations จากเมนูดานซ ้าย้จากนันกดป้่มุ Add new location... เพอื่

8 Copyright of www.ExcelExpertTraining.com เลอกช ื อโฟลเดอร ื่ (Trusted Folder) ์ตามตองการ้ นอกจากนสี้ํ าหรับ Excel 2010 เป็นตนมาย้งมัเมนีู Trusted Documents เพอใช ื่ควบค้มการุ เปิดแฟ้มเพอทื่ําใหแฟ้ ้มใดทเคยเป ี่ิดโดยให Macro ้ทํางานหรอื Update Links มากอนก่ ็จะ เปิดแบบนันให้เองในคราวต ้ อไป ่

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

10 Copyright of www.ExcelExpertTraining.com 2. หากจําเป็นตองแก้ ไข้ตองสามารถแก ้ ไขปร ้ ับปรงแฟุ้มไดง้าย่ ใชเวลาไม ้มาก่ และไม่ สงผลกระทบต่อการค่ ํานวณเดมทิถี่กตูองอย้แลู่ว้ทงนั้ไมี้ว่าจะก่ ําหนดใหผ้ทู้ที่ําหนาท้ ี่ แกไขน ้ ันค้อคื ณซุงเปึ่็นผสรู้ างแฟ ้ ้มนันเอง้หรอแมืแต้ จะเป ่ ็นผอู้ นซื่งไม ึ่เคยใช ่ แฟ้ ้มนันมา้ กอนก่ ็ตาม 3. สามารถลดจํานวนคนและไมต่องเพ้มอิ่ตรากั ําลงคนตามปร ัมาณงานทิเพี่มขิ่นึ้ 4. ประหยดเงันลงทินุเพราะไมต่องจ้ายเพ่ อซื่อเครื้องหรื่อโปรแกรมคอมพ ืวเตอริร์นใหมุ่ ่ 5. คณตุองม้ ความส ีขและมุเวลาวี างให ่ก้บชัวีตมากขินึ้ สตรฉลาดู (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

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

12 Copyright of www.ExcelExpertTraining.com ตวอยัาง่ Smart Formula แบบใชต้ ําแหนงอ่างอ้ งเซลล ิเด์ยวี ขอใหสร้ างตารางส ้ตรคูณูซงนึ่ําตวเลขบนหัวตารางมาคัณกูบตัวเลขดัานข้ างซ้ายของตาราง้ ขนตอนการสร ั้าง้ 1. เรมจากเลิ่อกเซลล ื C3 ์แลวพ้มพิเคร์องหมายเทื่าก่บั = ลงไป 2. จากนันคล้กเซลล ิ C2 ์ จะไดส้ตรู =C2 3. กดป่มุ F4 เพอเปล ื่ยนส ี่ตรใหูเป้ ็น =C$2 4. พมพิเคร์องหมายคื่ณู * 5. จากนันคล้กเลิ อกเซลล ื B3 ์ จะไดส้ตรู =C$2*B3 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

13 Copyright of www.ExcelExpertTraining.com จะใชต้ําแหน่งอางอ้งจากแนวนอนิ 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)” ตวอยัาง่ Smart Formula แบบใชต้ ําแหนงอ่างอ้งตามขอบเขตพินทื้หี่วั ตาราง ขอใหสร้ างตารางส ้ตรคูณูซงนึ่ําตวเลขบนหัวตารางมาคัณกูบตัวเลขดัานข้ างซ้ายของตาราง้ วธิการสร ีางคล้ายก้บวัธิแรกีแทนทจะคลี่กเลิ อกเซลล ื C2 ์หรอเซลล ื B3 ์วธินี ใหี้คล้กเลิ อกเซลล ื ์ C2:F2 และ B3:B5 ซงเปึ่็นหวตารางดัานบนและด้ านซ ้ายท้งหมดั้แลวกดป ้ ่มุ F4 เพอทื่ําใหเก้ดิ สตรู =$C$2:$F$2*$B$3:$B$5 1. เรมจากเลิ่อกเซลล ื C3 ์แลวพ้มพิเคร์องหมายเทื่าก่บั = ลงไป 2. จากนันคล้กเลิ อกเซลล ืบนห์วตารางดั านบนจากเซลล ้เลข์ 2 - 5 จะไดส้ตรู =C2:F2

14 Copyright of www.ExcelExpertTraining.com 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 จะทําหนาท้นี่ําตําแหน่งอางอ้งบนริ ัวตามแนวต้งฉากทั้ตี่ดกันั มาคณกูนทัละตี วให ัเอง้ทําใหไม้ต่องคอยพะวงว้าจะต่องเล้ อกใส ืเคร่องหมายื่ $ เพอื่ ควบคมตุําแหน่งอางอ้ งตามแนวใดเป ิ ็นหลกั • ถาเปร ้ยบเทียบกี บสัตรคูณวูธิแรกซ ี งใช ึ่ต้ําแหน่งอางอ้ งแบบเซลล ิเด์ยวีจะพบวาการใช ่ ้ สตรอูางอ้ งแบบเซลล ิเด์ยวมีความยีดหยืนมากกวุ่า่ เชนเม่อหื่วตารางมั ขนาดใหญ ีมากข่นึ้ ก็สามารถ Copy สตรไปใชูต้ อได ่ท้ ันทีแตถ่ าสร ้ างส ้ตรแบบลูอมร้ ัว้จะตองสร ้ างส ้ ตรใหมู่ เพอกื่ําหนดตําแหน่งอางอ้ งในส ิ ตรใหูม้ขนาดขยายตามี (เวนแต้ว่าค่ณทราบวุธิขยายี ขอบเขตตําแหน่งอางอ้งิ ซงจะนึ่ํามาอธบายติ อไปในภายหล ่งั) • จดอุอนของส ่ตรแบบลูอมร้ ัวก้ ็คอื สตรแบบนูใสี้เคร่องหมายื่ $ ควบคมตุําแหน่งแบบ Absolute จงคึ ํานวณหาคําตอบใหได้เฉพาะตารางจาก้ C3:F5 ซงอยึ่ภายในขอบเขตู่ ของรัวเป้ ็นแนวของตําแหน่งคงที่หาก Copy สตรไปใชูนอกร้ ัวจะเก้ดิ Error ขนตามรึ้ปู

15 Copyright of www.ExcelExpertTraining.com ตวอยัาง่ 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 เพอคื่นสภาพส ืตรตามเดูมิ

16 Copyright of www.ExcelExpertTraining.com • ลองใช Mouse ้คลกลงไปในช ิอง่ Formula Bar เลอกเฉพาะส ื วนของส่ตรู B3:B5 แลว้ กดป่มุ F9 จะพบวาต่ ําแหน่งอางอ้ งสิวนน่ เปล ี้ยนเป ี่ ็น {10;20;30} จากนันให้กดป ้ ่มุ Esc เพอคื่นสภาพส ืตรตามเดูมิ • หากกดป่มุ 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 ใหมลงไปก ่ ็ใหกระท้ ําตอพ่นทื้ี่ ตารางทเลี่อกให ืแล้วน้ ันร้วมก่ นเสมอ ั

17 Copyright of www.ExcelExpertTraining.com วธิค ี นหาส ้ ตรู เมอเจอป ื่ัญหาในงาน ใหค้อยๆว่เคราะหิ ป์ ัญหาทละขีนตอนั้แบงป่ ัญหาแยกออกเป็นขนๆั้ ตามลําดบการคั ํานวณ ตามลําดบการตั ดสั นใจ ิควรลองสรางแนวทางแต้ละข่นลงบนกระดาษั้ กอน่เพอจื่ัดลําดบการส ังค่ าไปท ่ละขี นให ั้ถ้กตูอง้แลวลองใช ้ม้อื ใชเคร้องคื่ดเลขิคดคิ ํานวณ อยางคร่าวๆก่อนว่า่ จะใชส้ตรคูํานวณอยางไร ่ และไดผลล้พธั ออกมาเป ์ ็นเทาใด ่ ปัญหาทวี่ายาก่อาจยากเพราะเราทําใหม้นยากขันเองึ้เชน่คํานวณวา่ถาซ้ อสื้นคิาต้นท้นุ 127 บาทตอหน่ ่วย มาปรับกําไรขนอึ้กี 13.45% จะตองก้ ําหนดราคาขายเทาใด ่ ปัญหานที้ําใหง้ายลง่ โดยใชเลขต้วกลมแทนตัวเลขจรั งไปก ิอน่ลองคํานวณตนท้นุ 100 บาท แทน 127 บาท ลองกําหนดกําไรเป็น 10% แทน 13.45% จะคํานวณไดผลล้พธัง์ายข่นจรึ้งิ ไหม สาเหตทุเราปร ี่ับปัญหาใหง้ายลงน่ ี้เพอให ื่มองท้ลี่ําดบขันการคั้ํานวณแทนทจะมี่ววันอยุ่กู่บั ตวเลขัเมอได ื่ข้นการคั้ํานวณทตี่องการแล้ว้จงหึ นมาสร ัางมาค้ นหาส ้ตรู Excel ทจะใช ี่้ภายหลงั จากสรางส ้ ตรเสรู็จแลว้ เราจะเปลยนตี่วเลขให ัยากข้นเทึ้าใดก ่ ็ไดท้งนั้ัน้ Excel จะคํานวณให้ เสร็จในพรบตาิ ขอใหย้ดหลึกวัา่ Excel มสีตรคูํานวณสํ าหรับปัญหาของเราทกอยุาง่ บางปัญหาตองใช ้ ส้ตรู รวมก่ บสัตรอูนตื่อๆก่ นไป ั บางปัญหาตองใช ้ ส้ตรซูอนส้ตรูแตบางป ่ ัญหาใชส้ตรๆเด ูยวกี ็ได ้ ผลลพธัแล์ว้ขนตอนการแกั้ป้ ัญหาจงเปึ ็นภาระของมนุษย์ตองหาส ้ตรทูตี่องการใช ้ ให้เจอ้ แทนทจะเส ี่ยเงีนซิอหนื้ังสอเรืองส ื่ตรูใหเป้ ิดดจากู Excel Help ซงตึ่ดมากิ บโปรแกรม ั Excel อยู่ แลว้ (หนังสอเรืองส ื่ตรทูขายกี่นั สวนมากม่กลอกคั ําอธบายจากิ Excel Help ไปใช ้มนีอยเล้ม่ มากทเขี่ยนอธีบายดิวยต้วเองั )

วธิที ี่1 เลอกเื ซ บน For ถาไม ้ร่วู้่ เลอกื A คลกลงิ ไ สตรใดูก็ นันๆลง้ไ ตวอักัษ จอจะแส Help on เปิดดรูา วธิที ี่2 เมอเป ื่ิด เกยวขี่อ้ ควรรอบ ปัญหาที 1 คนหาต้ ซลลว์างๆท่ยี่ั rmula Bar วาส่ตรนูันๆ้อ All ในชอง่ se ไปในชอง่ S ก็ได ้แลวพ้มิ ไป Excel จ ษรทตี่องการ้ ใ สดงรปแบบูข n this func ายละเอยดขี 2 คนหาส ้ตู ดสตรทูตี่อง้ก องกนั บรสู้ ตรซูงมึ่คี ทสี่ดุ Cop ามชอสื่ตู ยงไม ัม่ สี ตรใู อยในกลู่ มไุ่ห elect a cat Select a fun มพต์วอักษรั ะเลอนไปห ื่า ใหท้นทั ีสว่ ของสตรูหรื ction ดานล้า่ องสตรู ตรทใชี่ง้า การ แลวเล้อื่ ความเกยวขี่อ้ pyright of ww ร ดๆ แลวใช้ค้ หน ใหคล้กิ egory แลว้ nction ตรง นําของสตรู าชอสื่ตรตาูม นดานล้ างใ ่ต รอคลืกทิคี่ําว างซายส้ดเุพื านทดแทน อนจอไปดา้น องกนทังหั้ม 18 ww.ExcelExp คําสงั่ Form ม ต้ วา่ พอื่ นกนั นลาส่ดุใหค้ มดกอน่แลว้ pertTraining.c ulas > Ins คลกคิ ําวา่ S วจงตึ ดสั นใิจ com ert Functio ee Also เพื่ จเลอกใช ื ส้ตู on หรอคลืกิ พอเปิดรายชอื่ ตรซงเหมาะึ่ก กทปี่่มุ fx อสตรทูี่ กบั

วธิที ี่3 ใหเร้มิ่พิ ชวยเล่อื ไมแนะ่น ทําใหม้ > Optio AutoCo 3 คนหาต้ ั พมพิ = ์ตาม อกตวแปรใ ัน นําใหใช้ Fu้ องไมว่ าเป ่ ็น ons > Adv omplete ทงิ้ Cop วแปรท ั ใี่ช มดวยช ้ อสื่ตูร นสตรใหูเอง้ nction Scre น column ใ anced (แล งไปดวยเพ้ร pyright of ww ชในส ้ตรู รทตี่องการ้ ใ een Tips เพ ด โดยตดกัา ะควรสงั่ File ราะ Excel จ 19 ww.ExcelExp ใชงานก้อน่ พราะคําอธบิ าชอง่ Show e > Option ะพยายามเด pertTraining.c แลวกดป ้ ่มุ C บายทปราก ี่ฏ w Function ns > Formu ดาชอสื่ตรทูพี่ com Ctrl+a เพอื่ ฏขนจะทึ้บแัน ScreenTip ulas > ตดัก พมพิ ลงไป ์ ใ อเปิด Help ส นวของหวั c ps จากการสั กาชอง่ Form ให) ้ สํ าหรับ column สง่ั File mula

วธิที ี่4 เมอเป ื่ิด อธบายิข 4 คนหาข้ ้ ดดรายละเอูยี ขอแม้หร้อืข้ Cop อจ้ ํากดขั ยดของสตรูใ ขอจ้ ํากดใน ัก pyright of ww องสตรู ใน Excel He การใชงานข้อ 20 ww.ExcelExp elp แลวให้ ้ องสตรเอาไู pertTraining.c ด้ขูอจ้ ํากดัข ว้ com ของสตรไดูจ้าก Remarks ซงึ่

21 Copyright of www.ExcelExpertTraining.com วธิแกะส ี ตรู ลองพจารณาส ิตรคูํานวณหาเวลาสนสิ้ดในงานุ Material Requirements Planning ตอไปน ่ ี้ =INDEX(Begin,COUNTIF(Cum_Hour,"<"&(I10+((INDEX(Cum_Hour,MATCH(VLOOKU P(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH(VLOOKUP(J10,Begin,1) ,Begin,1),1))+((J10-VLOOKUP(J10,Begin,1))*24))))+1,1)+(((I10+((INDEX( Cum_Hour,MATCH(VLOOKUP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH (VLOOKUP(J10,Begin,1),Begin,1),1))+((J10 - VLOOKUP(J10, Begin, 1))*24)))- (INDEX(Cum_Hour,(COUNTIF(Cum_Hour,"<"&(I10+((INDEX (Cum_Hour,MATCH( VLOOKUP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH( VLOOKUP(J10, Begin,1),Begin,1),1))+((J10-VLOOKUP(J10,Begin,1))*24))))+1),1)-INDEX( Working_Hour,(COUNTIF(Cum_Hour,"<"&(I10+((INDEX(Cum_Hour,MATCH(VLOOK UP(J10,Begin,1),Begin,1),1)-INDEX(Working_Hour,MATCH(VLOOKUP( J10,Begin,1),Begin,1),1))+((J10-VLOOKUP(J10,Begin,1))*24))))+1),1)))/24) • เรมจากส ิ่งเกตเครัองหมายเทื่าก่บัเครองหมายวงเลื่ ็บ และเครองหมายลื่กนูํ้า ซงเปึ่็น องคประกอบแยกโครงสร ์ างของส ้ตรู • สงเกตตัวอักษรตั วใหญ ั ่ เป็นชอสื่ตรและตูําแหน่งอางอ้งิ • เมอกดป ื่่มุ F2 หรอคลื กลงไปในส ิตรบนู formula bar จะพบวา่ Excel เปลยนส ี่แตีละ่ สวนของส่ตรูพรอมก้บมั กรอบส ีตรงกีนลั อมรอบเซลล ้ ์เพอชื่ใหี้เห้ ็นตําแหน่งของ เซลลซ์งถึ่กนูําไปอางอ้ งในส ิตรู • ใหคล้ กลงบนส ิตรูแลวใช้ แป้ ้นพมพิเล์อนลื่กศรผูานเคร่องหมายวงเลื่ ็บ จะพบวา่ เครองหมายวงเลื่ ็บคกู่นทั ังด้านหน้าและด้านหล้งั จะเปลยนเป ี่ ็นสดีําเขมประมาณ ้ 2 วนาทิ ีเพอบอกวื่าเป ่ ็นวงเล็บคเดู่ยวกีนั • เมอพบวงส ื่ตรทูเปี่ ็นวงเล็บชดเดุยวกีนแลัว้ ใหคล้กเลิ อกส ืตรนูันในช้อง่ Formula Bar แลวกดป ้ ่มุ F9 จะเห็นผลลพธั เฉพาะส ์วนท่เลี่อกไว ื ้แลวให้ กดป ้ ่มุ Esc เพอื่ ยอนกล้ บไปแสดงส ัตรตามเดูมิ • ให double ้คลกลงไปในเซลล ิ ส์ตรูจะพบวา่ Excel ยายต้ ําแหน่งตวชั ไปเล ี้อกเซลล ื ์ ตนทางท้ ใชี่ในส ้ตรูทงนั้ตี้องต้ ดกาช ัอง่ Allow editing directly in Cells ในคําสงั่ File > Options > Advanced ไวก้อน่และเมอไปย ื่งเซลล ัต์นทางแล้วหากต้องการ้ ยายกล้บมายั งเซลล ั ส์ตรเดูมิ ใหกดป ้ ่มุ F5 แลวกดป ้ ่มุ Enter • ควรใสวรรคระหว่างเคร่องหมายทื่ใชี่ในส ้ตรูเพอแยกให ื่ห้างออกจากก่นั จะได ้ สะดวกในการแกะสตรูหากตองการแยกส ้ตรขูนบรรทึ้ดใหม ั ในส ่ตรเดูมิ ใหกด้ Alt+Enter • หากอยระหวู่างการแก่ ไขส ้ตรูแตย่งแกั ไขไม ้ เสร ่ ็จ ใหพ้มพิเคร์องหมายคื่ําพดใสู่ ดานหน้าเคร้องหมายเทื่าก่บัเพอทื่ําใหส้ ตรกลายเปู็นตวอั กษรไปก ัอน่ หากไมใช่ว้ธินีี้ ตองคอยระว้งอยัากด่ Enter เพราะ Excel จะรับสตรลงไปและเตูอนขือผ้ดพลาดหริอื แกไขส ้ตรตูางไปจากท ่ตี่องการ้

• • • • • • ควรพมิพ เมนูยอ่ย สตรซูงึ่อ นอกเขา้ ออกมา สตรบาูง Name ใ Manag ถาต้อง้ก เชอมโ ื่ย F5 > S Column Depend ถดไป ัแ เรอยๆเื่พ เรอยๆจื่ ถาต้อง้ก Formul แสดงสู ใหใช้เ้ม Show F Option referen อางอ้งิว สภาพเดิ Cop พ Comme ์ n ย Insert Co อยในวงเลู่ ็บ าใน เพอเขื่า้ อกครี ังเพ้อื่ไ งสตรไมูได่อ้ ใหด้ ไดูจาก้ er เพอเขื่า้ไ การคนหาเ้ ซ ยงกนดั วยส ้ตู Special > ก n Differenc dents และเ แลวกดป ้ ่มุ O พอสื่งให ั่ E้ x นกวาจะหม่ การลากเสน้ las > Form ตรในเซลลใ์ มนู Formula Formulas แ s > Formu nce style เพื วาเป ่ ็นแบบเ ดมเมิอตรวื่จ pyright of ww nt กํากบเซ ัล omment เพื บในสดุจะสง่ าใจกบควาัม ไลล่ ําดับการ อยในเซลลู่ ์ คําสงั่ Form ไปดและแกูไ้ ซลลต์นทาง้ห ตร ใหเล้อกืเ าชอง่ Row ces หรอเลือื เลอกื Direc OK จากนัน้ใ xcel ทวนคํา ดการเชอมื่โ นลกศรแสดูง mula Auditin ใหเห้ ็นแทน as > Formu และใชค้กู่บัค ulas > กาชอ่ พอแสดงร่ื ปูแ ดยวกีนหรัอื จสอบสตรเรูยี 22 ww.ExcelExp ลล์โดยคลกิ พออธ่ื บายส ิตู งคาต่ อมาใ ่ห มหมายของส รคํานวณสง่ แตเป่ ็น For mulas > Na ไขสตรู หรอปลาย ืท เซลลส์ตรแู Difference อก Precede ct only เพอื่ ใหกดป ้ ่มุ F4 าสงคั่นหาน้ ซี้ โยง งความเชอมื่ ng > Trace การแสดงผ ula Auditin คําสงั่ File > อง R1C1 แบบตําแหน อไม่และแกค้ ยบรอยแล้ว้ pertTraining.c กขวาลงไปใ ตรทใชี่งาน้ว หส้ ตรสูวนน่ สตรทูงหมดั้ คาในส ่ตรเูซ rmula ame ทางที่ ลวกดป ้ ่มุ es หรอื ents / หาเซลล์ 4 ตอไป ่ ซํ้ าไป โยงสงค่าร่ะ e Preceden ลลพธั ์ g > > น่ง คนสืู่ com ในเซลลท์ตี่อ้ วาม่ ความเป ี ็น อก ดงนั ันจ้งึ กอน่แลวแ้ก ซลลหน์งๆึ่ ะหวางส ่ตรูใ nts/Depend องการ้แลว้ นมาอยางไ ่ร งควรแกะสตู กะสตรจากูใ ใหใช้เมนู้ dents เลอกื ร ตรจาก ใน

23 Copyright of www.ExcelExpertTraining.com วธิสรี างส ้ ตรซูอนส ้ ตรู จดประสงคุของการน์ ําสตรมาซูอนส้ตรูเพอทื่ําใหส้ตรๆเด ูยวสามารถค ี ํานวณใหผลล้พธั ได์เลย้ ทนทั ีโดยไมต่องพ้ งพาส ึ่ตรในเซลลูอ์นๆื่ทําใหเก้ ดประโยชน ิด์งนั ี้ • ชวยจ่ ํากดเซลล ัท์ตี่องใช ้เพ้อสร ื่างส ้ตรู • ชวยให ่ค้ นหาเซลล ้ ส์ ตรไดูง้ายเน่ องจากส ื่ตรถูกนูํามารวมกนไว ัเพ้ยงทีเดี่ยวี • หากมจีํานวนเซลลท์มี่สีตรยาวๆจ ูํานวนไมมากน่ ัก จะทําให file ้มขนาดเลี ็ก ลง เนองจากไม ื่ต่ องเปล ้ องเซลล ืหร์อื sheet เพอแยกเกื่ ็บสตรูและชวยให ่ ้ คํานวณรวดเร็วขนึ้ สวนข่อเส้ ยของการสรีางส ้ ตรซูอนส้ตรกู็คอื สรางยาก้และทําใหแก้ ไขส ้ ตรในภายหลูงยากขันึ้ จงตึ องเปร ้ยบเทียบขีอด้ขี อเส้ยดีงกลัาวน่ เสมอ ี้หากสรางเองใช ้เองแก้ ไขเองคงไม ้ต่องห้วงน่ ัก แตถ่ าสร ้ างส ้ตรแลูวท้ งไว ิ้ให้คนอ้นแกื่ไข้คงตองล้มกื นไปเลยว ัา่จะเรมติ่นแก้ ไขก ้นยั งไงด ั ี สวนมากม่กสร ั างส ้ตรขูนใหม ึ้เลยด่กวีา่ หลกการสร ั างส ้ ตรซูอนส้ตรู 1. ควรรวมกลมของสุ่ ตรซูงคึ่ํานวณเป็นลําดบตัอเน่องกื่นไว ั เป้ ็นสตรเดูยวีและแยก ขนตอนซ ั้งไม ึ่ต่อเน่ องออกเป ื่ ็นอกสีตรหนูงึ่ 2. ควรแยกสตรออกใหูท้ ํางานคนคื าซ่งมึ่ความหมายสมบ ีรณู์ในขนนั้ันๆ้และงายต่อการ่ ตรวจสอบวา่ลําดบการคั ํานวณขนใดผ ั้ดพลาดิ จะไดสะดวกต ้อการแก่ ไขให ้ถ้กตูอง้ 3. อยารวมท่กขุนตอนของการคั้ํานวณไวในส ้ตรๆเด ูยวจนยาวเกี นไป ิ วธิสรี างส ้ ตรซูอนส้ตรู ใหสร้ างส ้ ตรซูงควรเป ึ่็นสตรวงเลู็บในสดกุอนส ่ตรอูนื่สตรนูมี้กใช ัค้ํานวณหาคาพ่ นฐานแรกส ื้ดุ ซงจะถึ่กนูําไปใชเป้ ็นตวแปรในส ัตรอูนถื่ดไป ั เชน่สรางส ้ตรคูํานวณหาเลขทบรรที่ดัดวยส ้ตรู =MATCH( MinimumCost, TotalCost, 0) จากนันจ้งสร ึ างส ้ตรอูนครื่อมข่างนอกเพ้อนื่ําผลลพธั จากส ์ตรู Match ไปใช ้เชน่ =INDEX( LotSize, MATCH( MinimumCost, TotalCost, 0), 1) ถาเป้ ็นสตรชูวยการต่ดสั นใจ ิเชน่สตรู IF ใหพ้ จารณาหาส ิ ตรซูงใช ึ่ต้ดสั นใจอ ินๆื่มาซอนเข้ าไป ้ ในสตรเดูมเมิอถื่งตึ วแปรซ ังตึ่องการใช ้ ส้ตรอูนคื่ํานวณคาให ่ ้เชน่ตองการต้ ดสันคะแนนิ 0 - 50 ใหเกรด้ C ถาคะแนน้ 51 - 80 ใหเกรด้ B แตถ่าเก้นกวิา่ 80 ใหเกรด้ A ใหสร้ างส ้ตรหาเกรดู A ใหได้ก้อน่

24 Copyright of www.ExcelExpertTraining.com =IF(คะแนน>80,"A","BC") แลวค้อยพ่จารณาติอว่า่จะตดสันเกรดิ B C ตอไปด ่ วยส ้ตรู IF อยางไร ่ =IF(คะแนน>80,"A",IF(คะแนน>50,"B","C")) ถาไม ้ถน่ ัดสรางส ้ตรตูอก่ นซัอนก้ นไปเลยท ัเดียวี ใหสร้ างส ้ตรแยกแตูละข่ นตอนออกไปลงใน ั้ เซลลแยกจากก์นกัอน่แลวจ้งึ copy ตวสัตรูไป paste ทบในต ั ําแหน่งอางอ้งิ เชน่ A1: =NOW() A2: =DAY(A1) ให copy ้ สตรู NOW() จากเซลล A1 ์ ไปทบคั ําวา่ A1 ในเซลล A2 ์ จะไดส้ ตรซูอนก้ นเป ั ็น =DAY(NOW()) วธิ copy ี สตรูใหคล้กเลิ อกส ื วนของส่ตรทูตี่องการจากส ้ตรบนู Formula Bar เชน่เลอกคื ําวา่ NOW() แลวคล้กขวาเลิอกื copy (หรอกดื Ctrl+c) แลวกด้ Esc หนงครึ่ังเพ้อออกจากการเลื่อกื แลวจ้ งไปคล ึกเลิอกคื ําวา่ A1 บน Formula Bar ของสตรในเซลลู A2 ์แลวคล้ กขวาส ิงั่ paste (หรอกดื Ctrl+v)

25 Copyright of www.ExcelExpertTraining.com วธิสรี างช ้ อซ ื่อนช ้ อ ื่ ใชค้ําสงั่ Formulas > Define Name เพอตื่งชั้อให ื่ก้บสัตรูหลงจากนั ันจ้งนึ ําชอนื่ันไปซ้อนใน ้ ชออื่นตื่อไปอ ่กี เชน่สตรในงานวางแผนการผลูติ ซงใช ึ่เวลาเร้มงานิ่และเวลาทใชี่ท้ ํางาน คํานวณหาเวลาสนสิ้ดงานุตอไปน ่ ี้ Calc1 =VLOOKUP(RelativeStart, Begin, 1) Calc2 =INDEX(Working_Hour, MATCH( Calc1, Begin, 1 ), 1 ) Calc3 =INDEX(Cum_Hour, MATCH( Calc1, Begin, 1 ), 1 ) Calc4 =Calc3 - Calc2 Calc5 =RelativeStart - Calc1 Calc6 =Calc4 + ( Calc5*24 ) Calc7 =RelativeHour + Calc6 Calc8 =COUNTIF(Cum_Hour, "<" & Calc7 ) + 1 Calc9 =INDEX(Working_Hour, Calc8, 1) Calc10 =INDEX(Cum_Hour, Calc8, 1) Calc11 =Calc10 - Calc9 Calc12 =(Calc7 - Calc11) / 24 Calc13 =INDEX(Begin, Calc8, 1) Calc14 =Calc13 + Calc12 วธินีําชอทื่ตี่ังไว้มาใส ้ ในส ่ตรูใหกดป ้ ่มุ F3 เพอเลื่อกช ือทื่ตี่งไว ั้้เพอสร ื่างส ้ ตรลงไปในเซลลูก์อน่ แลวจ้งึ Copy สตรไปใสูต่ อในช ่ องส่ตรของู Define Name เวลาใชงานก้ ็นําสตรชูอื่ Calc14 ไปใชเท้าน่ ัน้ก็จะคํานวณสตรทูงหมดตั้งแตั้ Calc1 – Calc13 ่ ใหอ้ตโนม ัตั ิ

26 Copyright of www.ExcelExpertTraining.com เรองท ื่ไมี่ค ่ อยร ่ กู้ นเก ั ยวก ี่บสั ตรู โปรแกรม 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 จะมรีนใหมุ่ ให่ พวกเราได ้ ใช้งานก้ นประมาณ ั

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

28 Copyright of www.ExcelExpertTraining.com 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 เป็นผลทเกี่ดจากการคิ ํานวณทไมี่สามารถน ่ ําไปคํานวณตอได ่ ้เชน่

29 Copyright of www.ExcelExpertTraining.com o

N/A เมอสื่ตรหาขูอม้ลทูตี่องการไม ้พบ่ (Not Available) o

NAME? เมอสื่ตรหาชูอทื่ตี่องการไม ้พบ่ เชน่ =answer โดยทในแฟ ี่้มนัน้ ไมได่ต้ งชั้อื่ Range Name วา่ answer ไวก้อน่ o

NULL! เมอสื่ตรหาตูําแหน่งทตี่ดกั นไม ั ได่ ้เชน่ =A:A B:B 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 ใหเปล ้ ยนเป ี่ ็นคาอ่นแทนื่ดงนั ี้

30 Copyright of www.ExcelExpertTraining.com • ในกรณีท A1 ี่คนคื าเป ่ ็น Number ใหใช้ =IF( IsError(A1), 0, A1) ้ • ในกรณีท A1 ี่คนคื าเป ่ ็น Text ใหใช้ =IF( IsError(A1), “ ้คําเตอนใดๆก ื ็ได”, A1) ้ ซงตึ่วผมเองันยมใช ิ ส้ตรู =IF( IsError(A1), 0, A1) ทงในกรณ ั้ีทเดี่มคินคื าเป ่ ็น Number หรอื Text ก็ตาม เพราะสามารถใช Format ้ ปรับการแสดงเลข 0 ใหแสดงเป ้ ็นคาอ่นหรื่อแมืแต้ท่ ําให้ ดเหมูอนกลายเป ื ็นเซลลว์ างได ่ต้ อไป ่ ตาแหนํ งเซลล ่ท์เลี่อกส ื ํ าหรบสร ั างส ้ตรู เห็นหวขัอน้แลี้วคงสงส ้ยกันวัาต่ ําแหน่งเซลลท์เลี่อกสร ื างส ้ ตรลงไปนูันเป้ ็นประเด็นทตี่องใส ้ ใจ่ กนดัวยหร้อืถาค้ดกินงัายๆว่า่ “ในเมอฉื่ันตองการจะสร ้ างส ้ ตรลงไปตรงนูกี้ ็ตองตามใจฉ ้ ัน ฉันจะ สรางส ้ ตรลงไปตรงนูเสี้ยอยีาง่ ใครจะทําไม” ก็แสดงวาค่ณมองขุามอะไรท ้ เปี่ ็นเรองส ื่ํ าคญอยัาง่ หนงไปแล ึ่ว้ ตําแหน่งเซลลท์คี่ณสรุางส ้ตรหาคูําตอบลงไป สามารถชเปี้ ็นชตายให ี้ก้บงานของคัณทุเดียวี เพราะถาอย้มาวู่นหนังึ่เมอยื่ายเซลล ้ ส์ ตรไปทูตี่ําแหน่งอนื่สตรทูเคยคี่ํานวณหาคําตอบไดตลอด้ มา อาจคนคื าเป ่ ็นคําตอบทผี่ดหริ อแสดงผลล ืพธัแตกต์ างไปจากเด ่มกิ ็ได ้ วธิทดสอบส ีตรทูคี่ณหรุอใครสร ืางข้นวึ้าเป ่ ็นสตรทูใชี่ได้หร้ อไม ื ่ ใหทดลองย้ ายเซลล ้ ส์ ตรไปทูอี่นื่ ยายไปเร ้อยๆหลายๆตื่ําแหน่ง พรอมก้นนั ันทดลองย้ายตารางข้อม้ลทูใชี่อ้างอ้ งไปท ิอี่นดื่วย้ลอง Insert Row หรอื Column แทรกตารางขอม้ลเขูาไปอ ้กีถาส้ตรนูันย้งคงหาคั ําตอบได ้ เหมอนเดืมิ แสดงวาส่ตรนูันใช้งานได ้ ้ ทราบไหมวาถ่าตารางข้อม้ลทูเกี่ ็บคาอย่ ในตารางชู่วง่ B2:D5 คณควรสรุางส ้ตรคูํานวณซงึ่ เกยวขี่องก้ บการใช ัข้อม้ ลในตารางนูไวี้ท้ เซลล ี่ใด์ ซงจะรึ่ับประกนวั าส่ตรทูสร ี่างข้ นจะสามารถ ึ้ นําไปใชงานต้ อได ่ท้กทุี่ คําตอบก็คอืหามสร ้ างส ้ ตรในแนวเดูยวกีบตารางขัอม้ลูนั่นคอือยาสร ่ างส ้ ตรในชูวง่ Column B ถงึ D และชวง่ Row 2 ถงึ 5 สาเหตทุหี่าม้ เพราะการสรางส ้ ตรใดๆท ูมี่ตีําแหน่งอางอ้ งไปย ิงคั าในแนวเด ่ยวกีบตารางขัอม้ลู อาจคนคื าเป ่ ็นคําตอบเป็นคาในตารางตามแนวเด ่ยวกีนกั ็ได ้

31 Copyright of www.ExcelExpertTraining.com หากสรางส ้ ตรในเซลลูซ์ งไม ึ่ได่อย้ ในแนวเดู่ยวกี บชัวง่ Column B ถงึ D และชวง่ Row 2 ถงึ 5 แลวค้ ณหาทางปรุับสตรนูันให้ค้ ํานวณหาคําตอบทตี่องการ้ยอมร่ ับประกนได ัว้า่ สตรนูันเป้ ็นสตรู ทใชี่งานได ้จร้งและจะยิงคงคันคื าเป ่ ็นคําตอบเดมไปตลอด ิ ไมว่าจะย่ ายเซลล ้ ส์ ตรไปทูตี่ําแหน่ง อนกื่ ็ตาม พอพบวาส่ตรทูํางานไดจร้งแลิว้จากนันจะย้ายเซลล ้ ส์ ตรไปวางไวูในแนวค ้ขนานกู่บตารางขัอม้ลู ทใชี่ในการค ้ ํานวณก็ไมว่าก่นั สตรกู็มจีงหวะหายใจด ัวยนะ้ ผทู้ ใชี่ Excel ้ เป็นประจําตองร้จู้ ักสตรตูอไปน ่ เปี้ ็นอยางด่ =IF(C2=0, 0, B2/C2) ี สตรขูางต้นน้คี้อสืตรทูใชี่ส้ํ าหรับปรับสตรทูนี่ําตวเลขมาหารกั นไม ั ให่ แสดงค ้ ําเตอนื Error วา่

DIV/0! ขนมาึ้ เมอเซลล ื่ C2 ์ ซงเปึ่็นเลขตวหารมัคี าเป ่ ็น 0 ใหค้นคืา่ 0 เป็นตวเลขคั ําตอบแทน แตถ่ าเซลล ้ C2 ์ ไมได่ เป้ ็นเลข 0 ก็ยอมน่ ํา C2 มาหาร B2 ไดตามปกต ้ ิผทู้ สร ี่างส ้ตรหารกูนจะหลักเลี ยงส ี่ตรู ดงกลัาวน่ ไมี้ได่ ้ ขอถามวา่เวลาทคี่ณสรุางส ้ตรูคณมุ พฤตี กรรมในการสร ิ างส ้ตรกูนอยั างไร ่คณจะพุมพิ ลงไปใน ์ เซลลว์า่ =if(c2=0,0,b2/c2) รวดเดยวให ีครบท้งหมดแลั้วจ้ งกดป ึ ่มุ Enter หรอไม ื ่หรอจะคือยๆ่ พมพิ =if ์แลวหย้ ดหายใจพุักหนงแลึ่วจ้งพึมพิ c2=0 ์ตอ่แลวก้ ็หยดพุักหายใจอกเฮ ีอกหนืงจึ่งึ คอยๆพ่มพิ ส์วนท่เหลี่อตือ่ ขอผ้ดพลาดทิพบบี่อยในการสร ่ างส ้ตรของคนทูเพี่งเริ่ยนรี การใชู้ Excel ้ก็คอืมกหลงลัมื เครองหมายวงเลื่ ็บ พมพิเคร์องหมายื่ Comma ขาดไป บางครังพ้มพิ สล์บทั ี่บางครังพ้มพิขาด์ บางครังพ้มพิเก์นิทําใหเม้ อกดป ื่่มุ Enter ลงไปแลว้ Excel ตองเต้อนขื นมาบนจอให ึ้ค้ณทราบวุา่ สตรทูสร ี่างน้ ันผ้ดิ ผสรู้ างส ้ตรตูองม้ สมาธ ีพอติวั สมาธทิวี่าน่เกี้ดจากการริจู้ ักวาเม่ อใดจะหย ื่ดเมุอใดจะสร ื่างต้อ่ถารู้้ หลกจั ังหวะหายใจจะชวยให ่ค้ ณสรุางส ้ ตรไดูง้ายข่นึ้ไมว่ าส่ ตรสู นๆหรั้อสืตรยากๆยาวๆก ู็ใชหล้กั จังหวะหายใจนกี้นทั ังน้ัน้

32 Copyright of www.ExcelExpertTraining.com จังหวะหายใจทวี่าน่ ี้ไมใช่จ่ ังหวะหายใจของ Excel หรอกนะ Excel มนไม ั ได่ม้ การหายใจ ี เหมอนกืบมนัุษย์แตเป่ ็นจังหวะหายใจของคณนุั่นแหละ ตองร้วู้าเม่ อใดท ื่ควรจะหยี่ดแลุวหายใจ ้ สกเฮัอกหนืงแลึ่วจ้ งสร ึ างส ้ตรตูอ่ ซงเรึ่องการหายใจน ื่ไมี้ต่องลอกจ้ ังหวะการหายใจใหเหม้อนกืบั ผมก็ได ้ตวใครต ัวมันั ขอใหเล้อกจื ังหวะหายใจทตี่วเองคัดวิาถน่ ัดก็แลวก้นั ขอยกสตรทูงหมดมาดั้กูนอักครี ัง้ =IF(C2=0, 0, B2/C2) มาดกูนวัาถ่ าเป้ ็นวธิของผมี จะสรางส ้ตรนูโดยหย ี้ดพุักเพอหายใจก ื่นตรงไหนบ ัาง้เอาหายใจเข ้า้ แลวนะ้จากนันเร้มสร ิ่างส ้ ตรโดยใชูว้ธิตามลี ําดบตั อไปน ่ ี้ 1. พมพิเคร์องหมายเทื่าก่บั 2. พมพิค์ ําวา่ if( 3. หยดเพุอหายใจ ื่จากนันสร้างส ้ตรตูอด่วยข้นตั้อไป ่ 4. หยบเมาส ิคล์กเซลล ิ C2 ์ 5. แลวพ้มพิ =0, ์ตอ่ 6. หยดเพุอหายใจ ื่ 7. ในใจอานส ่ตรทูไดี่ตามน้ =if(C2=0, ี้วาถ่ าเซลล ้ต์วหารมัคีาเท่าก่บั 0 ละก่อ้ 8. พมพิ 0, ์ตอ่ 9. หยดเพุอหายใจ ื่ 10.ในใจอานส ่ตรทูพี่มพิ 0, ์วา่ ใหค้ ําตอบทไดี่เป้ ็น 0 แทนนะ จากนัน้ 11.หยบเมาส ิคล์กเซลล ิ B2 ์ 12.พมพิเคร์องหมายหารื่ / 13.แลวหย้ บเมาส ิคล์กเซลล ิ C2 ์ 14.หยดเพุอหายใจ ื่ 15.ในใจอานส ่ตรู B2/C2 ทไดี่มาว้า่แตถ่าต้ วหารไม ั ใช่เลข่ 0 ใหค้ ําตอบทไดี่เป้ ็นสตรหารู กนตามปกต ั ิ 16.จากนันจ้งกดป ึ ่มุ Enter รับสตรลงไปในเซลลู์โดยไมต่องพ้มพิเคร์องหมายวงเลื่ ็บปิด เพราะถาม้วงเลี ็บเปิดตวเดัยวี Excel จะพมพิเคร์องหมายวงเลื่ ็บปิดใหเอง้ จับหลกได ั ไหมเอ ้ย่ ขอใหส้งเกตวั าให ่หย้ดเพุอหายใจได ื่หล้งจากพัมพิจบตรงเคร์องหมายนื่ันๆ้ ไง ถาใช้เคร้องหมายจื่ดจุดแทนชุวงหย่ดเพุอหายใจ ื่จะไดส้ตรตามแบบนู ี้

33 Copyright of www.ExcelExpertTraining.com =if(.........C2=0,.........0,......... B2/C2 ฝึกหยดเพุอพื่ักหายใจใหได้จ้ ังหวะ วธินี จะช ี้วยลดข่อผ้ดพลาดหลงลิ มการใส ืเคร่องหมายวงเลื่ ็บ หรอเครืองหมายื่ Comma ในการสรางส ้ ตรไปไดูเยอะท้เดียวี ธรรมชาตของส ิตรู ถาเข้ าใจธรรมชาต ้ติ อไปน ่ ของส ี้ตรูจะทําใหเราใช ้ ส้ตรู Excel ไดง้ายโดยไม ่ต่องท้องจ่ ําและลด ขอผ้ดพลาดเมิอนื่ําสตรมาใชู้ 1. ชอสื่ตรเปู็นตัวอกษรภาษาอังกฤษตั วใหญ ั ่ เชน่ IF, MAX, MIN ดงนั ันเวลาพ้มพิ ส์ ตรใหูพ้มพิ ์ ตวเลั ็ก พอกดป่มุ Enter แลวจะพบว้า่ถาเราสะกดช ้ อสื่ตรถูกตูอง้ Excel จะเปลยนตี่วอักษรั จากตวเลั ็กไปเป็นตวใหญ ัท่ งหมดให ั้ท้นทั ี 2. เราสามารถพมพิ ส์ ตรโดยถูาในส ้ตรมูเครีองหมายวงเลื่ ็บเปิดเพยงวงเลี ็บเดยวี พอกดป่มุ Enter จะพบวา่ Excel พมพิเคร์องหมายวงเลื่ ็บปิดใหเอง้แตถ่าม้วงเลี ็บเปิดหลายตวัเราตอง้ ใสเคร่ องหมายป ื่ิดวงเล็บใหครบท้กคุ ู่เพราะถากดป ้ ่มุ Enter ลงไป Excel จะพยายามคดใส ิ ่ เครองหมายวงเลื่ ็บปิดใหครบแต้ อาจใส ่วงเล่ ็บปิดผดคิ ใหู่ก้็ได ้ 3. วงเล็บในสตรตูองใส ้ ให่ครบค้และถู่กตูําแหน่ง มวงเลี ็บเกนดิกวีาขาดวงเล่ ็บ 4. ถาไม ้ ใส่วงเล่ ็บ Excel จะไลค่ ํานวณตามลําดบดังนั ี้ยกกําลงกัอนแล่วตามด้วยค้ณหรูอหารื แลวตามด้วยบวกหร้อลบื (คณหารูหรอบวกลบืมศีกดัศร ิ์เทีาก่นั จะไลค่ ํานวณจากซายไป ้ ขวาแลวแต้ว่าเคร่ องหมายใดม ื่กีอน่ ) ดตูวอยัางท่ายข้อ้ 7 5. โครงสรางส ้ตรู Excel ถกสรูางข้นมาเลึ้ยนแบบลี ําดบความคัดของมนิุษย์ดงนั ันถ้าเราเข้ าใจ ้ ตวเองวัาเรามองเราค่ดทิละขีนอยั้างไร ่เราก็จะสรางส ้ ตรไดูง้ายโดยไม ่ต่องท้อง่ เชน่เวลา เราคดเริองเงื่อนไขว ื่า่ถาท้ ําดีตองได ้ด้ ีแตถ่ าไม ้ท่ ําดีตองได ้เร้องอื่นๆื่ตวสัตรู IF ของ Excel ก็จะมลีําดบในโครงสร ั างส ้ตรตามแบบทูเราคี่ดออกมาเป ิ ็น =IF(ทําดใชี ไหม ่ , ไดด้ , ี ไดอ้นๆื่ ) ขอใหอ้านรายละเอ่ ยดประเด ี ็นนจากบทความเรี้องื่เมอสื่ตรู Excel มชีวีตจิ ตใจ ิหรอื จากลงคิ www.excelexperttraining.com/blo ์ gs/archives/z300-ExcelCore000706.php 6. ในโครงสรางส ้ตรูถาม้การอีางอ้งกิบตั ําแหน่งเซลลตามแนวนอนและแนวต์ ัง้จะตองใส ้ ่ ตําแหน่งอางอ้ งตามแนวนอนไว ิก้อนแนวต่ งเสมอ ั้ดไดูจากส ้ตรู Index หรอสืตรู Offset (ดงนั ันเราควรสร้างส ้ตรคูํานวณบวกลบคณหารอู นๆให ื่ใช้ต้ําแหน่งอางอ้งตามแนวนอนกิอน่ แนวตงดั้วยเช ้นก่นัเพอเป ื่ ็นแบบแผนชวยให ่เข้ าใจท ้ ไปท ี่มาและแกะส ี่ตรไดูง้าย่ )

34 Copyright of www.ExcelExpertTraining.com 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 ตวเพัอแบื่งส่ วนในวงเล ่ ็บออกเป็น 30 สวน่ ) และสตรทูยาวที่สี่ดทุสามารถพ ี่มพิ ลงไปในเซลล ์ ์ หนงๆได ึ่น้ ัน้ยาวไดส้ งสูดุ 1,024 ตวอักษรั ขอก้ ําหนดเหลาน่นี้ับวาเพ่ยงพอกีบการคั ํานวณทวไป ั่นานๆทจีงมึ โจทย ีท์ตี่องใช ้จนเก้นกวิา่ ขดจี ํากดั ซงแกึ่ไขได ้ง้ายๆโดยแยกการค ่ ํานวณเป็นสวนๆ่แลวแบ้งแต่ ละเซลล ่ ให์แยกค้ ํานวณ แตละส ่ วนของส่ตรูจากนันจ้งนึ ําผลการคํานวณทไดี่มาค้ ํานวณรวมก่นตั อเป ่ ็นขนๆั้หรอไม ืก่ ็ตอง้ ฉลาดเลอกใช ื ส้ตรอูนทื่คี่ํานวณไดค้ ําตอบแบบเดยวกีนแทนั

35 Copyright of www.ExcelExpertTraining.com สํ าหรับสตรสูํ าเร็จรปในู 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 นั่นเอง ทพี่เศษไปกว ิาน่ ี้หากคณตุดกาในช ัอง่ Allow edit directly in cells ทงไป ิ้ (ใน Excel Options > Advanced หรอใน ื Excel 2003 ใชเมนู้ Tools > Options > Edit) จะพบวาเม่อดื่บเบัลคลิกิ ทเซลล ี่ส์ตรู =A1 จะยายต้ ําแหน่ง cursor ไปทเซลล ี่ต์นทาง้ A1 ใหท้นทั ี

36 Copyright of www.ExcelExpertTraining.com สงทิ่อธี่บายขิางต้นน้เคยอธี้บายมาหลายติอหลายคร่ ังในการอบรมและในเว้ ็บ 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 ์ มาแสดงเสร็จก็จบหนาท้ ไปแล ี่ว้

37 Copyright of www.ExcelExpertTraining.com สตรทูสามารถหาค ี่าท่เกี่ ็บไวจากตารางได ้ โดยตรง ้ ซงทึ่ํางานไดแบบท้อธี่บายนิ ี้ไดแก้ If, ่ Choose, Index, Offset, Indirect (แตไม่ รวมส ่ตรู VLookup เพราะมนไม ั ได่หาค้ าได ่ โดยตรง ้ ) ซงไม ึ่จ่ ําเป็นตองค้นคืาเพ่ยงคีาเด่ ยวเป ี ็นตําแหน่งเซลลเด์ยวี โดยสามารถคนคื าเป ่ ็นตารางเพอื่ ใชบอกต้ ําแหน่งตารางทังตารางก้ ็ยงได ั ้ ดตูวอยั างประกอบบทความน ่ ไดี้จาก้ "www.excelexperttraining.com/forums/content.php?r=191-สตรู -ตําแหน่งอางอ้งิ" จํานวนเซลลส์ตรเดูยวี ผสรู้ างส ้ตรู Excel ทวไป ั่คนเคยกุ้ บการสร ั างส ้ตรหนูงทึ่คี่นคืาเพ่ยงหนีงคึ่า่หรออืกนี ัยหนงึ่ เรยกวีา่ สตรเดูยวคีนคืาเด่ยวี ซงสรึ่างลงไปในเซลล ้เด์ยวีแตน่อยคนน้ ักทจะทราบวี่า่ สตรู Excel สตรหนู งๆสามารถค ึ่นคื าเป ่ ็นคําตอบไดหลายค้าก่ ็เป็นไปไดเหม้อนกืนั สตรพวกหลูงนัเรี้ยกวีา่ สตรู Array การทเราเลี่อกเซลล ืเด์ยวีแลวพ้มพิ ส์ ตรลงไปในเซลลูเด์ยวนี ัน้มสาเหต ีเนุองจากส ื่ตรนูันค้นคืา่ ไดค้ ําตอบเพยงคีาเด่ยวี ถาส้ตรหนู งๆคึ่นคื าเป ่ ็นคําตอบไดหลายค้า่ หากสรางส ้ตรนูันลงไปในเซลล้เด์ยวียอมไม ่ถ่กตูอง้ แมจะเห้ ็นคําตอบแสดงขนมากึ้ ็ตาม เพราะคําตอบทแสดงข ี่นมาให ึ้เห้ ็นนันเป้ ็นเพยงคี ําตอบแรก คําตอบเดยวี เราหมดโอกาสทราบคําตอบคาอ่นๆื่ พออานถ่งตรงนึ ี้คนทไมี่ร่จู้ ักกบสัตรู Array คงเรมสงส ิ่ยวัา่ สตรแบบไหนกูนทัมี่คีําตอบไดหลาย้ คาในส ่ตรเดูยวี มาทดลองโดยเรมจากพิ่มพิค์ าใดๆก ่ ็ไดลงไปในเซลล ้ A1:A3 ์ สมมตวิาพ่มพิ 11, ์ 22, 33 ตามลําดบั

38 Copyright of www.ExcelExpertTraining.com แลวสร ้ างส ้ตรู =A1:A3 ลงไปในเซลล B1 ์จะเห็นคําตอบเป็นเลข 11 คาเด่ ยวใช ี ไหม ่ จากนันให้แกะด้คูาท่แที่จร้ งในส ิตรูโดยกดป่มุ F2 ตามดวยป ้ ่มุ F9 จะพบวาส่ตรู =A1:A3 เปลยนเป ี่ ็น ={11;22;33} ขอใหส้งเกตวัาเคร่องหมายวงเลื่ ็บ { ทเกี่ดขินนึ้ันอย้หลู่งจากเครัองหมายเทื่าก่บั ซงแสดงคึ่า่ แบบ Array และระหวางต่วเลขจะมัเครีองหมายื่ semi-colon ; คนั่ซงแสดงวึ่าค่าถ่ ดไปอย ั ในู่ Row ถดไป ั (หรอขืนึ้ Row ใหม) ่ พอเห็นคาแล่วว้ าส่ตรนูมี้ 3 ีคาและแต่ละค่าอย่ตามแนวตู่งั้ก็ใหกดป ้ ่มุ Esc เพอยื่อนกล้ ับไปเป็น สตรู =A1:A3 ตามเดมิ คราวนถี้งขึ นตอนส ั้ํ าคญัหากตองการกระจายค้าแต่ละค่ าในส ่ตรู 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 โดยใหกา้

39 Copyright of www.ExcelExpertTraining.com ชอง่ Transition formula evaluation (Excel 2007 ใหคล้ กปิ ่มุ Office แทน File หรอื Excel 2003 สงั่ Tools > Options > Transition > กาชอง่ Transition formula evaluation) คําสงั่ 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-3 release 2.2 ถกเปลูยนเป ี่ ็นสตรของู Excel ใหท้นทัเมี อเป ื่ิดแฟ้มทใชี่ส้ตรนูันด้วย้ Excel สวนช่อง่ Transition navigation keys จะเปลยนพฤตี่กรรมของป ิ ่มลุกศรูป่มุ Tab หรอปื ่มใดๆท ุี่ ทําหนาท้เคลี่อนยื่ายต้ ําแหน่งเซลลท์เลี่อกื ใหท้ ําตามท Lotus 1-2-3 ี่ใช ้ซงชึ่วงแรกกว่าผมจะ่ หนมาใช ั Excel ้อยางเต่ ็มตวัตองอาศ ้ ยกาชัองน่อยี้นานเพราะมู่อยื งไม ัค่นกุ้ บปั ่มทุเคยเลี่อนไป ื่ เลอนมาตามแบบทื่ตี่วเองเคยช ันิ

40 Copyright of www.ExcelExpertTraining.com ตวอักษรมั ไดิม้คีาเท่าก่บั 0 กอนจะจบบทความน่ ี้ทราบไหมวา่ตวอักษรั a หรอตืวอั กษรใดๆก ั ็ตามทบี่นทั กลงไปในเซลล ึ ์ มไดิถ้อวืาม่คีาเท่าก่บั 0 หรอกนะ ถาไม ้ เช่อื่สมมตวิาเซลล ่ A1 ์มคี าเป ่ ็น a ขอใหลองสร ้ างส ้ตรู =A1=0 ดกู็ไดจะพบว้ าได ่ค้ ําตอบเป็น FALSE สวนส่ตรู =A1>999999999 จะไดค้ ําตอบเป็น TRUE Excel ถอวืาต่วอั กษรใดๆม ัคีามากกว่าเลขท่ งปวง ั้อกนี ัยหนงมึ่คี าเป ่ ็น Infinity หากสรางส ้ตรู =Sum(A1,123) แลวได ้ค้ ําตอบเทาก่บั 123 เป็นเพราะ Excel จะละเลยไมน่ ําตวอักษรมาั คํานวณในสตรู Sum นแหละเป ี่ ็นเรองทื่หลายๆคนชอบเข ี่าใจผ ้ดกินวัา่ตวอักษรมัคี าเป ่ ็น 0 ถาสร ้ างส ้ตรู =A1+123 จะไดค้ ําตอบเป็น

VALUE! เพราะ Excel จะไมยอมร่ ับการนําตวอักษรั มาบวกกบตัวเลขัแตเราสามารถเปล ่ยนพฤตี่กรรมของิ Excel ใหค้ดแบบิ Lotus 1-2-3 ไดตาม้ คําอธบายขิางต้น้ ถาถามว้าระหว่ างส ่ตรบวกกูบสัตรู Sum เราจะเลอกใช ืการบวกเลขว้ธิใดก ี บงานส ั ํ าคญั คําตอบก็คอืตองเล้ อกใช ื ส้ตรบวกูเพราะหากมการบีนทักตึวเลขผัดิ กลายเป็นบนทักตึวอักษรั ลงไปแทน จะพบวา่ Excel ไมยอมค่ ํานวณให้โดยไดค้ ําตอบเป็น

VALUE! ซงเทึ่าก่ บชัวย่ เตอนเราให ืทราบว้าเก่ดขิอผ้ดพลาดขิ นมาในระหว ึ้างการบ่นทักขึอม้ลนูั่นเอง

41 Copyright of www.ExcelExpertTraining.com การแบงขอบ ่ เขตพนท ื ้ ตาราง ี่ ในชทของี 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 เพอขื่นขึ้อความในบรรท ้ ดใหม ั ในเซลล ่เด์มิ 2.2.ตองบ้นทักขึอม้ ลในู column ใด column หนงตึ่ดติอก่ นไปตลอด ัหามเว้ นช้องว่าง่ ซงึ่ โดยทวไป ั่ column ซายส้ดมุกจะมัขีอม้ลตูดติอก่ นไปอย ัแลู่วเน้ องจากเป ื่ ็นขอม้ลของู เลขทรายการี่หรอขือม้ลรหูสัหรอขือม้ลวูนทั ซี่งเรึ่ยงไปตามล ี ําดบรายการทัเกี่ดขินึ้

42 Copyright of www.ExcelExpertTraining.com 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 เป็นตน้

43 Copyright of www.ExcelExpertTraining.com การจดเตรัยมพีนทื้ 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)

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

45 Copyright of www.ExcelExpertTraining.com 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 จะปรับโครงสรางต้ ําแหน่งอางอ้ งในส ิ ตรใหูกลายเป ้ ็นตําแหน่งอางอ้งขิ ามช ้ทหรีอขื ามแฟ ้ ้มให้ เองทนทั ี วธิที 3 ี่นเปี้ ็นเคล็ดลบสั ํ าคญในการสร ั างส ้ตรู link ขามแฟ ้ ้ม โดยเรมจากสร ิ่างส ้ตรู link ทงหมดในช ั้ทเดียวกี นให ั เสร ้ ็จกอน่จากนันเม้อื่ Cut เซลลส์ ตรไปทูชี่ทอี นจะได ื่ส้ตรู link

46 Copyright of www.ExcelExpertTraining.com ขามช ้ทีหรอถืา้ 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 ไมพบ่ )

47 Copyright of www.ExcelExpertTraining.com 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 ทบเฉพาะเซลล ัน์ ันๆจะเหมาะกว้า่

48 Copyright of www.ExcelExpertTraining.com ระบบการรกษาความปลอดภ ัยั ในการ Link File ของ Excel 2010 แมว้าขนาดตารางในช ่ทของี Excel 2010 มจีํานวน 1,048,576 rows และ 16,384 columns ชวยรองร่ ับกบขัอม้ลจูํานวนมหาศาลมากขนกวึ้า่ Excel รนกุ่อนก่ ็ตาม แตม่ ไดิท้ ําใหว้ธิบี ันทกึ ขอม้ลทูถี่กตูองต้ างไปจากท ่ควรี่กลาวค่อื ในแฟ้มหนงๆควรเลึ่อกบืนทักเฉพาะขึอม้ลทูี่ จาเป ํ ็นตองใช ้งานเร้องทื่เกี่ยวขี่องก้ นไว ัด้วยก้ นในช ัทเดียวกีนเทัาน่นั้เชน่ แยกแฟ้มเก็บ ขอม้ ลการขายในชูวงแต่ ละไตรมาสไว ่ด้วยก้นั แฟ้มแรกเก็บขอม้ลเดูอนื 1-3 แฟ้มทสองเก ี ็บ ขอม้ลเดูอนื 4-6 แฟ้มทสามเก ี่ ็บขอม้ลเดูอนื 7-9 และแฟ้มทสี่เกี่็บขอม้ลเดูอนื 10-12 จากนัน้ เมอตื่องการด้งขึอม้ ลของไตรมาสใดมาใชู้ใหสร้ างส ้ตรู link ขามแฟ ้ ้มนําขอม้ลมาคูํานวณ และ ใชค้ําสงั่ Change Sources เพอเปล ื่ยนการี่ link จากแฟ้มเดมไปย ิ งแฟ ั ้มอนเพื่อนื่ําขอม้ ลชูวง่ ไตรมาสทตี่องการมาค้ ํานวณโดยไมต่ องเส ้ ยเวลาสรีางส ้ตรู link ใหมแต่อย่ างใด ่ ใน Excel 2010 ไมได่ม้วีธิการสร ี างส ้ตรู link หรอมืระบบการี Change Sources ตางไปจาก ่ Excel รนกุ่อน่แตใน่ Excel 2010 มระบบการรี ักษาความปลอดภยในการใช ัข้อม้ลทูเครี่งคร่ ัดกวา่ รนกุ่ อนและส ่งผลท่ ําให Excel 2010 ้ แสดงอาการและมคีําเตอนตื างไปจากเด ่มเมิอมื่การเป ี ิด แฟ้มท link ี่กนับทความนจะทดสอบระบบการ ี้ link file ของ Excel 2010 โดยจําลอง สถานการณ์ในแตละข่ นตอนมาให ั้ศ้กษาตึงแตั้เร่มติ่นย้ งไม ัม่ขีอม้ลูจวบจนสรางส ้ตรู link และ ทดลองปรับระบบการรักษาความปลอดภยวั าจะส ่งผลต่อค่ ําเตอนเรืองการื่ link อยางใดบ ่าง้ ระบบความปลอดภยใน ั Excel 2010 ทจะนี่ํามาทดสอบกบระบบการั link เชน่ Trusted Location และ Trusted Document (เชญิ Download แฟ้มทใชี่ในการทดสอบน ้ ไดี้จาก้ www.ExcelExpertTraining.com/download/testlink.zip) 1. เรมติ่นจากทดสอบขนาดแฟ ้ ้มทเปี่ิดขนมาโดยย ึ้งไม ัม่ขีอม้ ลอะไรเลยูสงั่ Save แฟ้มตงั้ ชอวื่า่ Target.xlsx พบวาม่ ขนาดแฟ ี ้ม 8.18 KB 2. ในแฟ้มชอื่ Target นี้ทดลองพมพิค์ ําวา่ Operations ลงไปในเซลล B2 ์พบวา่ จากแฟ้ม วางๆซ ่งมึ่ขนาดี 8.18 KBแฟ้มมขนาดใหญ ีข่นเลึ้ ็กนอยเป ้ ็น 8.55 KB พอพมพิต์วเลขั 20496 ลงไปในเซลล C2 ์ แฟ้มมขนาดใหญ ีเพ่มขิ่นเป ึ้ ็น 8.57 KB (แสดงวาข่อม้ลตูวเลขั ทําใหแฟ้ ้มมขนาดใหญ ีข่ นชึ้ากว้าข่อม้ลทูเปี่ ็นตวอักษรั )

3. 4. 5. สรางแฟ ้ ้มฐ ในแฟ้ม Tar รายการ De ใชส้ตรู Arra ทําใหแฟ้ ้ม ใหแฟ้ ้มมขีน แฟ้มทําใหเ้ เป็นพเศษ ิจ เมอปื่ิดแฟ้ม เพยงแฟ ี ้มเด คําวา่ Oper หายอดรวม Target เปลี Cop านขอม้ลจูํา rget เซลล์ epartment ท ay {=SUM Target มขี นาดเพมขิ่นึ้ไ เกดิ cache จากการเก็บ ม Source ซึ่ ดยวีพบวา่แ rations เป็น ไดตามต้อง้ ลยนแปลงแ่ี pyright of ww านวน 200 ร C2 เมอสร ื่า้ ทมี่ชีอื่ Ope ((Source.x นาดเพมขิ่นึ้ ไดง้ายกว่า่ข data ขนใน ึ้ cache data ซงเปึ ็นแฟ้มต้ แฟ้ม Target น Security ห การ โดยไม ตอย่ างใด ่ ( 49 ww.ExcelExp รายการ ชอื่ างสตรู link erations วา่ม xlsx!DEPAR นจาก 8.57 K ขอม้ลทูเปี่ ็นต นแฟ้มปลายท a เอาไว) ้ ตนทาง้ ใหค้ t ยงคงมัขีน หรอื R&D เพ มต่ องเป ้ ิดแฟ้ แสดงวาใช ่ข้ pertTraining.c Source.xlsx เพอหายอดื่ มยอดของี A RTMENT=B KB เป็น 12. ตวเลขหรัอืต ทาง ทําใหแ้ งเหลอแฟ ื ้ม นาดเทาเด่มิเ พอหายอดื่ร ฟ้ม Source ขอม้ลจากู c com x มขนาดี 1 รวมจากแฟ้ม Actual รวมเ B2)*Source 5KB (แสดง ตวอักษรัแล แฟ้มปลายท มปลายทาง T เป็น 12.5 K รวมของรายก และไมท่ ําให cache data 19.1 KB ม Source ข เทาก่บั 204 e.xlsx!ACTU งวาส่ตรคูําน ละการ link ข างมขนาดใ ีห Target เปิด KB และเมอื่เ การ พบวา่ ส หขนาดแฟ ้ ้ม a ทเกี่ ็บไวใ้น ของ 496 โดย UAL)} นวณทํา ขาม้ หญข่นึ้ ดอยู่ เปลยนี่ สามารถ ม นแฟ้ม

6. 7. Target) ตรวจสอบคํ update au จากนันทด้ล แถบคําเตอื links has b ขนมาอธึ้บิา Cop คําสงั่ File > tomatic lin ลองปิดแฟ้ม น Security been disab ายวา่ The fo pyright of ww Options > nks ไว ้ ม Target แล Warning ต led และเมอื่ ollowing co 50 ww.ExcelExp > Advanced ลวเป้ ิดแฟ้ม ตรงดานบน้ข อคลกลงไป ิท ontent has pertTraining.c d > พบวา่ร Target ขนึ้ ของจอเตอืน ทขี่อความ้คํ been disa com ะบบของ Ex มาใหมเพ่ยีง นวา่ Autom คําเตอนนื ี้จะ bled: Work xcel กาชอ่ง งแฟ้มเดยวี atic update ะเปิดหนา้ In kbook links ง Ask to ปรากฏ e of nfo s