Font s :
Background :

ว่าด้วยเรื่อง ซ้ำ ซ้ำ

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

F4 ซ้ำล่าสุด

ปุ่ม F4 มีประโยชน์มาก นอกจากช่วยทำหน้าที่ใส่เครื่องหมาย $ ในขณะสร้างสูตรให้แล้ว ยังช่วยสั่งให้ Excel ทำงานซ้ำเฉพาะคำสั่งสุดท้ายเท่านั้นให้อีก เช่น พอสั่ง Paste Special เสร็จ เมื่อกดปุ่ม F4 ที่เซลล์อื่น จะพบว่า Excel ทวนคำสั่ง Paste Special ให้อีกทุกครั้งที่กดปุ่ม F4

F4 ทวนคำสั่งสุดท้ายให้เพียงคำสั่งเดียว ไม่ว่าจะทำดีร้ายอย่างไรก็จะทวนซ้ำให้เสมอ จึงโปรดระวังอย่ากดปุ่ม F4 อย่างไม่ตั้งใจ เพราะหากเพิ่งสั่งลบชีททิ้งไป จะทวนการลบชีททิ้งให้อีก

ชื่อ Range Name ซ้ำ

ในการใช้งานตามปกติ Excel จะไม่ยอมให้ตั้งชื่อ Range Name ซ้ำในชีทเดียวกัน ซึ่งจะพบว่า เมื่อพยายามใช้ Name Box ตั้งชื่อซ้ำกับชื่อที่มีอยู่แล้ว Excel จะกระโดดกลับไปหาพื้นที่เซลล์ที่ตั้งชื่อนั้นไว้ก่อน

ในชีทเดียวกัน แม้จะใช้คำสั่ง Insert > Name > Create ตั้งชื่อตามหัวตาราง จะพบคำเตือนเปิดขึ้นมาถามก่อนว่า Replace definition of 'ชื่อ'? เพื่อป้องกันไม่ให้เกิดชื่อซ้ำกันในชีทเดียวกันเป็นอันขาด

หากต้องการตั้งชื่อ Range Name ซ้ำในชีทอื่น โดยใช้ชื่อซ้ำกับชื่อที่เคยตั้งไว้ในชีทอื่น จะไม่สามารถตั้งชื่อซ้ำโดยใช้ Name Box แต่สามารถใช้คำสั่ง Insert > Name > Define หรือ Create ตั้งชื่อซ้ำได้ตามต้องการ

ทั้งนี้เมื่อใช้ Insert > Name > Define จะต้องพิมพ์ชื่อชีทนำชื่อ Range Name ไว้ก่อน เช่น Sheet1!Id ส่วนคำสั่ง Insert > Name > Create จะช่วยตั้งชื่อให้เองทันทีโดยไม่ต้องเสียเวลาพิมพ์ชื่อชีทนำหน้า

นอกจากนี้ชื่อ Range Name ซ้ำจะเกิดขึ้นต่อเมื่อสั่ง Copy ทั้งชีท โดยกดปุ่ม Ctrl แช่ไว้แล้วคลิกลากชื่อชีทไปปล่อยข้างนอก

ชื่อ Range Name ซึ่งเกิดขึ้นก่อน ถือว่าเป็น File Level Name ซึ่งสามารถอ้างถึงชื่อนี้ในสูตรคำนวณได้โดยตรง เช่น =Id ส่วนชื่อซ้ำซึ่งเกิดขึ้นภายหลัง ถือว่าเป็น Sheet Level Name ทำให้เมื่อต้องการใช้ในชีทอื่น จำต้องพิมพ์ชื่อชีทที่เป็นเจ้าของ นำหน้าชื่อ Range Name ที่เป็น Sheet Level Name ก่อนเสมอ เช่น =Sheet1!Id

หากต้องการสร้างสูตรอ้างถึงชื่อระดับ File Level ในชีทซึ่งมีชื่อ Range Name ระดับ Sheet Level ซ้ำกัน ต้องพิมพ์ชื่อแฟ้มนำหน้าชื่อ Range Name ก่อน เช่น =File1.xls!Id

การตั้งชื่อ Range Name ซ้ำข้างต้นนี้จะช่วยให้สูตรที่ใช้แต่ละชีทดูเหมือนกัน แม้เซลล์ต้นตอที่มาของค่าจะอยู่ต่างที่กันก็ตาม ช่วยให้นำเสนอผลงานได้สะดวกขึ้น แต่ผู้ใช้ต้องรู้ที่ไปที่มาและข้อควรระวังในการใช้ชื่อ

วิธีใช้ข้อมูลจากชีทที่มีโครงสร้างซ้ำกัน

วิธีนี้จะทำได้ต่อเมื่อทุกชีทที่ใช้งานมีโครงสร้างของตารางเหมือนกัน เช่น สร้างแฟ้มงานเพื่อเก็บข้อมูลรายเดือน โดยแบ่งชีทตามรายเดือน สมมติว่าตั้งชื่อชีทเป็น Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec และข้อมูลในแต่ละเดือนถูกบันทึกเรื่องเดียวกันไว้ในเซลล์เดียวกัน

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

รูปแบบสูตร Indirect

=Indirect( "Text บอกตำแหน่ง" )

ชื่อชีท ก็คือ Text ที่บอกตำแหน่ง ซึ่งจะว่าไปแล้ว ชื่อทั้งหมดที่ถูกตั้งขึ้น ไม่ว่าเป็นชื่อแฟ้ม ชื่อชีท ชื่อเซลล์ ชื่อตาราง ชื่อสูตร จะนำมาใช้กับสูตร Indirect ได้เสมอ

=Indirect("Jan!A1") จะได้ข้อมูลจากชีท Jan เซลล์ A1
=Indirect("Feb!A1") จะได้ข้อมูลจากชีท Feb เซลล์ A1
=Indirect("Mar!A1") จะได้ข้อมูลจากชีท Mar เซลล์ A1

ดังนั้นแทนที่จะต้องย้อนกลับมาแก้ไขสูตรเพื่อเปลี่ยนชื่อเดือน สมมติว่าเซลล์ B1 เป็นเซลล์ที่บันทึกชื่อเดือน Jan, Feb, Mar, .... ซึ่งเราสามารถพิมพ์เปลี่ยนไปได้เอง

=Indirect(ฺB1&"!A1") จะได้ข้อมูลจากชีท Jan เซลล์ A1
=Indirect(B1&"!A1") จะได้ข้อมูลจากชีท Feb เซลล์ A1
=Indirect(B1&"!A1") จะได้ข้อมูลจากชีท Mar เซลล์ A1

สูตร Indirect สามารถ link ข้อมูลจากแฟ้มอื่นได้ แต่แฟ้มต้นทางนั้นต้องเปิดขึ้นมาใช้พร้อมกับแฟ้มปลายทางซึ่งมีสูตร Indirect

จะเห็นว่า เมื่อโครงสร้างตารางของทุกชีทมีตำแหน่งเซลล์ตรงกัน ช่วยทำให้ใช้ตารางที่เก็บข้อมูลไว้ให้เกิดประโยชน์มากขึ้น ซึ่งนอกจากการใช้สูตร Indirect เพื่อหาข้อมูลจากแต่ละชีทแล้ว หากต้องการหายอดรวมของเซลล์ A1 ของทุกชีท ให้ใช้สูตร
=Sum(Jan:Dec!A1)

ตัวเลขในเซลล์ A1 ของทุกชีทตั้งแต่ชีท Jan ถึงชีท Dec ไม่ว่าจะมีกี่ชีทระหว่างชีท Jan กับ Dec จะถูกบวกรวมกันให้เอง โดยไม่ต้องใช้สูตรบวกกันทีละชีททีละเซลล์แต่อย่างใด

วิธีใช้แฟ้มซ้ำ

แทนที่จะแยกข้อมูลรายเดือนเก็บไว้ในชีทต่างกัน สมมติว่าคราวนี้แยกเก็บไว้ต่างแฟ้มกัน เช่น Jan.xls, Feb.xls, Mar.xls ซึ่งโครงสร้างภายในแฟ้มเป็นแบบเดียวกันทั้งชีท เซลล์ และตารางภายในอยู่ในตำแหน่งเดียวกัน

สูตร Link ข้ามแฟ้มที่ทำไว้ เช่น =[Jan.xls]Sheet1!$A$1 เพื่อดึงข้อมูลจากแฟ้มชื่อ Jan จะถูกเปลี่ยนเป็น =[Feb.xls]Sheet1!$A$1 ให้อัตโนมัติ โดยมีขั้นตอนง่ายๆดังนี้

  1. เปิดแฟ้มปลายทางซึ่งมีสูตร link ข้ามแฟ้ม จากนั้นให้สั่ง Tools > Protection > Unprotect Sheet ทุกชีทซึ่งมีสูตร link ข้ามแฟ้ม
  2. สั่ง Edit > Links แล้วคลิกชื่อแฟ้มต้นทางเดิม เช่น Jan.xls ซึ่งปรากฏอยู่ในรายชื่อของ Source
  3. กดปุ่ม Change Source ด้านขวาบน แล้วคลิกหาแฟ้มต้นทางตัวใหม่ที่ต้องการ เช่น Feb.xls ซึ่งอาจเก็บไว้ใน folder ใดก็ได้ตามต้องการ

หากต้องการรวมยอดทุกแฟ้มซึ่งมีโครงสร้างตารางเหมือนกัน ให้ใช้คำสั่ง Data > Consolidate

หากต้องการเปิดแฟ้มที่เคยใช้งานร่วมกันซ้ำ โดยเราไม่ต้องเสียเวลาไล่เปิดเองทีละแฟ้ม ให้เปิดแฟ้มทั้งหมดที่ต้องการก่อนครั้งแรก จากนั้นสั่ง Save > Workspace จะได้แฟ้มนามสกุล .xlw ต่อมาหากต้องการเปิดแฟ้มทั้งหมด ให้เปิดแฟ้ม .xlw นี้แฟ้มเดียว Excel จะช่วยเปิดแฟ้มอื่นๆต่อให้เอง

วิธีลดสูตรซ้ำ

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

หากต้องการ link ข้อมูลทั้งตารางมาใช้ซ้ำ แทนที่จะต้องสร้างสูตร link กันเซลล์ต่อเซลล์ ให้สั่ง Copy ตารางต้นทาง จากนั้นไปที่เซลล์หัวมุมของตารางปลายทาง กดปุ่ม Shift แช่ไว้แล้วคลิกเมนู Edit > Paste Picture Link

วิธี Paste แบบพิสดารนี้สามารถใช้สร้างตารางที่ link ข้ามชีทหรือข้ามแฟ้มก็ได้ ช่วยประหยัดเวลาและทำให้ตารางที่ link มาเกิดจากสูตรง่ายๆเพียงสูตรเดียว แต่วิธีนี้ไม่สามารถใช้กับตารางที่เป็น Chart Sheet เนื่องจากบนเมนูของ Chart Sheet ไม่มีคำสั่ง Paste Picture Link

ยังมีวิธีอีกวิธีหนึ่งสามารถสร้าง link picture ได้ทุกที่ ดังนี้

  1. ให้เริ่มจากเลือกตารางต้นทางแล้วกดปุ่ม Shift แช่ไว้ จากนั้นคลิกเมนู Edit > Copy Picture แล้วกด Enter
  2. เลือกเซลล์หัวมุมของตารางปลายทาง หรือคลิกเลือก Chart Sheet จากนั้นสั่ง Paste ตามปกติ จะปรากฏรูปภาพตารางต้นทางแต่ยังขาดสูตร link
  3. คลิกต่อที่ Formula Bar แล้วสร้างสูตร link ย้อนกลับมาที่ตารางต้นตอด้วยตนเอง

นอกจากนี้ยังมีวิธียืมสูตรมาใช้ซ้ำ โดยใช้ Data Table ซึ่งจะนำมาอธิบายเฉพาะเรื่องนี้เป็นพิเศษในภายหลัง

Categories

About this Entry

This page contains a single entry by สมเกียรติ ฟุ้งเกียรติ published on April 24, 2005 2:03 PM.

โหราศาสตร์เชื่อถือได้หรือไม่ was the previous entry in this blog.

Excel Expert Extreme is the next entry in this blog.

Find recent content on the main index.

Font s :
Background :