ทางออกที่ดีกว่าการใช้ VBA

ข้อผิดพลาดที่ร้ายแรงที่สุดในการใช้ VBA เกิดจากการตัดสินใจใช้ VBA ทั้งๆที่มีทางออกอื่นที่ดีกว่า ง่ายกว่า สะดวกกว่าอยู่แล้ว ทำให้ต้องเสียแรงเสียเวลาสร้าง รหัส VBA ขึ้นมา พอสร้างเสร็จ ยังต้องเสียเวลาย้อนกลับมาแก้ไขอย่างยากเย็นเสียอีก

ในตัวโปรแกรม Excel มีเครื่องมือที่เป็นเมนูคำสั่งและสูตรสำเร็จรูป ซึ่งสามารถนำมาช่วยควบคุมการทำงานได้เช่นเดียวกันกับการใช้ VBA หรือนำมาใช้ร่วมกับ VBA

เครื่องมือช่วยในการเปิดแฟ้มที่ต้องเปิดพร้อมกัน

เริ่มจากจัดเปิดแฟ้มที่ต้องการทั้งหมดขึ้นมาบนจอ จากนั้นสั่ง View > Save Workspace ซึ่งจะเกิดแฟ้มใหม่เพิ่มอีกแฟ้มหนึ่งมีนามสกุล .xlw ต่อมาหากต้องการเปิดแฟ้มชุดเดิมทั้งหมดพร้อมกันอีก ให้สั่งเปิดเฉพาะแฟ้ม .xlw นั้นแฟ้มเดียว Excel จะไล่เปิดแฟ้มทุกแฟ้มที่ต้องการต่อให้เอง

แฟ้ม .xlw เป็นแฟ้มขนาดเล็กมาก ทำหน้าที่เก็บแค่ชื่อแฟ้มที่เคยเปิดขึ้นมาทำงานพร้อมกัน แฟ้มนี้ไม่ได้เก็บตัวแฟ้มอื่นไว้แบบ zip ดังนั้นถ้าต้องการส่งแฟ้มให้ผู้อื่นนำไปใช้งานต่อ ต้องส่งแฟ้ม .xlw นี้ไปพร้อมกับแฟ้มอื่นๆที่ต้องการเปิดพร้อมกันด้วย เพียงระบุว่า เมื่อต้องการเปิดแฟ้ม ให้เปิดแฟ้ม .xlw เพียงแฟ้มเดียว แล้วจะพบว่าแฟ้มอื่นๆจะถูกเปิดต่อให้เอง (แต่น่าเสียดายที่ Excel 2013 ยกเลิกการสร้าง xlw ไปแล้ว)

ถ้าแฟ้มมีหลายชีท อยากจะคลิกง่ายๆให้ไปที่ชีทที่ต้องการ

คุณสามารถใช้ Hyperlink สร้างรูปภาพสวยๆไว้ในตารางเพื่อคลิก link ไปยังชีทที่ต้องการ โดยเริ่มจากวาดรูปใส่ลงไปในตารางก่อน แล้วคลิกขวาที่รูป สั่ง Hyperlink แล้วเลือก Place in this document หรือ Existing File or Web Page เพื่อกำหนดตำแหน่งปลายทางในแฟ้มเดิมหรือในแฟ้มอื่นตามลำดับ

นอกจากการใช้ Hyperlink ที่สร้างจากเมนู ยังมี Hyperlink ที่สร้างจากสูตรได้อีก เช่น หากต้องการสร้าง link ในคำว่า Click here ไปที่ชีทชื่อ June เซลล์ E56 ใช้สูตรต่อไปนี้

=HYPERLINK("[Book1.xls]June!E56", "Click here")

อยากไปที่ชีทที่ต้องการ แล้วให้ปรับโครงสร้างตารางในชีทนั้นด้วย

เป็นเรื่องหน้าเบื่ออย่างมาก หากแฟ้มที่ใช้งานมีมากมายหลายชีท ชื่อชีทด้านล่างจอจะเขียนต่อกันเป็นแถบยาวออกหน้าจอ ต้องเสียเวลาขยับไล่หาชีท จึงจะพบชีทที่ต้องการ และยังอาจพบปัญหาต่อเนื่องไปอีกว่า ชีทที่อยากจะดูนั้น ต้องคอยซ่อนแถว และปรับขนาดแถวใหม่จนกว่าจะได้โครงสร้างตารางตรงใจผู้ใช้ ซึ่งบางคนหาทางออกโดยสร้างชีทที่มีข้อมูลเหมือนกันหลายๆชีทขึ้นมา แล้วจัดโครงสร้างให้ต่างกันไปแล้วแต่ว่า ตารางนั้นชีทนั้นจัดไว้ให้ใครดู ถ้าทำอย่างนี้แฟ้มจะใหญ่กว่าเดิมขึ้นเยอะทีเดียว

ปัญหาดังกล่าวจะหมดไปโดยเริ่มจากจัดโครงสร้างตารางให้ตรงความต้องการก่อน แล้วใช้คำสั่ง View > Custom Views ตั้งชื่อแบบชีทที่ต้องการตามที่เห็นบนจอ ทำเช่นนี้ซ้ำไปเรื่อยๆ โดยไม่จำเป็นต้องสร้างชีทซ้ำกันขึ้นอีก

เมื่อใดที่ต้องการไปที่ชีทใด ให้คลิกที่ View > Custom Views แล้วคลิกชื่อแบบชีทที่ตั้งชื่อไว้ จะพบว่า Excel พาคุณไปที่ชีทนั้นแล้วปรับโครงสร้างตารางให้ด้วย และเมื่อคุณสั่งพิมพ์ยังสามารถปรับหัวตาราง ท้ายตารางในหน้ากระดาษให้แตกต่างกันไปได้อีกด้วย เนื่องจาก Custom Views บันทึก Page Setup ไปพร้อมกัน

นอกจากนี้ควรใช้คำสั่ง Data > Group ร่วมกับ Custom View เพื่อทำให้ชีทหนึ่งๆสามารถจัดโครงสร้างตารางให้เหมาะกับงานได้หลายๆแบบ และ Group ที่ใช้จะสร้างความยืดหยุ่น ช่วยให้ผู้ใช้สามารถเลือกปรับโครงสร้างตารางได้ตามใจต่อไปได้อีก ดีกว่าการใช้ VBA จัดโครงสร้างตารางแล้วไม่ยืดหยุ่น เนื่องจากจัดได้แบบเดียวตามตัวรหัสที่เขียนไว้แล้วเท่านั้น

ต้องการ Insert Row เพื่อให้ข้อมูลเดิมที่ติดกัน กลายเป็นเว้นบรรทัด

สมมติว่าเซลล์ A1:A5 มีข้อมูลเป็น A, B, C, D, E ตามลำดับ ลองคิดหาทางทำให้ข้อมูลเดิมที่อยู่ใน row ติดกันกลายเป็นห่างกัน row เว้น row จะใช้วิธีใดดี ซึ่งมีข้อแม้ว่า วิธีที่ใช้นั้นต้องสามารถนำมาใช้กับตารางที่มีข้อมูลนับหมื่นๆ row ได้ทันทีด้วย

ปัญหานี้ไม่จำเป็นต้องใช้ VBA แม้แต่น้อย เพียงแค่ใช้คำสั่ง Data > Sort ก็เสร็จแล้ว ทำไมคำสั่งจัดเรียงจะช่วย Insert row ให้ได้ ลองทำตามนี้

ในเซลล์ด้านขวาของตาราง A1:A5 เดิม คือในเซลล์ B1:B10 ให้ใส่ตัวเลข 1, 2, 3, 4, 5, 1, 2, 3, 4, 5 ตามลำดับ จากนั้นเลือกพื้นที่ A1:B10 แล้วสั่ง Data > Sort โดยจัดเรียงลำดับตามตัวเลข จะพบว่า ตารางเดิม A1:A5 ถูกแทรก row ระหว่างค่าเดิมให้ทันที เนื่องจากเซลล์ตัวเลขเมื่อจัดเรียงจากน้อยไปมากเป็น 1, 1, 2, 2, 3, 3, 4, 4, 5, 5 จะพาเซลล์ช่องว่างใน Column A ติดไปด้วย จึงดูเหมือน ตารางเดิมถูก Insert Row

อยากควบคุมการกรอกค่าลงไปในตาราง ให้บันทึกได้เฉพาะเซลล์ที่ต้องการ

พอตารางข้อมูลใหญ่ขึ้น ซับซ้อนมากขึ้น คุณคงอยากหาทางทำให้ผู้ใช้แฟ้ม เกิดความสะดวกในการกรอกค่าลงไปในตาราง ซึ่งบางคนอาจคิดไกลไปใช้ VBA สร้างจอแสดงแบบฟอร์มขึ้นมาให้ผู้ใช้งานกรอกข้อมูล แล้วจึงส่งค่าที่กรอกในแบบฟอร์มไปยังเซลล์ที่ต้องการต่ออีกที

ปัญหานี้นอกจากจะใช้ Custom Views ช่วยปรับโครงสร้างตารางให้ง่ายขึ้นแล้ว แนะนำให้เลือกเซลล์ที่ยอมให้บันทึกข้อมูลทับได้ แล้วสั่ง Format > Cells > Protection > ตัดเครื่องหมายกาช่อง Locked ทิ้งไปแล้วตามด้วยคำสั่ง Tools > Protection > Protect Sheet จะพบว่าเมื่อกดปุ่ม Tab บนแป้นพิมพ์ Excel จะย้ายตำแหน่งเซลล์กระโดดไปตามเซลล์ที่เลือกตัดกาช่อง Lock ทิ้งให้ไปเรื่อยๆ

เมื่อต้องการบันทึกข้อมูล ให้กดปุ่ม Tab แล้วพิมพ์ข้อมูลบันทึกลงไป แล้วกดปุ่ม Tab แล้วพิมพ์ ทำเช่นนี้ซ้ำต่อไปจนบันทึกข้อมูลครบทุกเซลล์

อยากสั่งให้ Excel ทำงานซ้ำ

เพียงแค่กดปุ่ม F4 จะพบว่า Excel ทำงานทวนซ้ำคำสั่งล่าสุดให้เองทุกครั้งที่กดปุ่ม F4

ตัวอย่างข้างต้นนี้เป็นเพียงบางส่วนที่แสดงให้เห็นว่า เราสามารถใช้เพียงคำสั่งบนเมนู หรือใช้สูตรคำนวณ หรือใช้ทั้งคำสั่งบนเมนูมาใช้ร่วมกับสูตร เพื่อทำให้ Excel ทำงานได้เกินกว่าที่คิดกัน จึงขอแนะนำว่า ก่อนที่จะคิดใช้ VBA ควรเรียนรู้สักนิดว่า บนเมนูและสูตร Excel ยังมีอะไรที่ซ่อนไว้อีกบ้าง

คำสั่งบนเมนู

• Data > Consolidate เพื่อสร้างตารางยอดรวมจากหลายตาราง
• Data > Filter เพื่อแสดงรายการข้อมูลที่ต้องการ
• Data > SubTotal เพื่อสร้างตารางแบ่งยอดรวมเป็นยอดย่อย
• Data > Validation > List เพื่อสร้างปุ่มให้คลิกเลือกข้อมูล
• Data > What-If Analysis > Data Table เพื่อสรุปการคำนวณเมื่อตัวแปรมีการเปลี่ยนแปลง
• Data > Text to Columns เพื่อแยกคำออกจากประโยค
• Insert > PivotTable เพื่อสรุปข้อมูลนำมาสร้างตารางเปรียบเทียบ
• Home > Find & Select เพื่อค้นหาหรือแก้ไขข้อมูล
• Data > Edit Links เพื่อจัดการกับสูตร link ข้ามแฟ้ม
• F2 F3 F4 F5 F9 ปุ่มลัด
• Format > Cells เพื่อกำหนดรูปแบบ
• Home > Conditional Formatting เพื่อกำหนดรูปแบบตามเงื่อนไข
• Home > Style เพื่อกำหนดรูปแบบหลักให้กับแฟ้ม
• Formulas > Define Name เพื่อตั้งชื่อให้กับตารางหรือให้กับสูตร
• Data > What-If Analysis > Goal Seek เพื่อคำนวณย้อนกลับไปหาตัวแปร หรือ Scenarios เพื่อเลือกใช้ตัวแปรคำนวณตามเหตุการณ์

ปุ่มบนแป้นพิมพ์

• Ctrl+* เพื่อเลือกพื้นที่ตารางที่ติดต่อกัน (Current Region)
• Ctrl+จุด เพื่อตรวจสอบหัวมุมตารางที่เลือก
• Ctrl+Shift+ลูกศร เพื่อเลือกพื้นที่ตารางตามแนวที่ต้องการ
• Ctrl+PageUp เพื่อไปยังชีทก่อน
• Ctrl+PageDown เพื่อไปยังชีทถัดไป
• Ctrl+c = Copy
• Ctrl+x = Cut
• Ctrl+v = Paste
• Ctrl+z = Undo
• Shift+End,ลูกศร เพื่อเลือกพื้นที่ตารางตามแนวที่ต้องการ

สูตรสำเร็จรูป

If And Or Choose Vlookup Match Index CountIF SumIf SumArray Offset

ต่อเมื่อคุณรู้จักคำสั่งบนเมนู วิธีใช้แป้นพิมพ์ลัด และสูตรพอควรแล้ว ควรทดลองใช้ร่วมกับ Macro Recorder จะช่วยประหยัดเวลาไม่ต้องเขียนรหัส VBA ได้มากทีเดียว