สอบถามการทำ Professional Macro Recording

[sakajohn]

ผมลองทำในหัวข้อการใช้ Macro เรียกข้อมูลเก่าขึ้นมา แล้วทำการแก้ไขแล้วใส่ข้อมูลกลับเข้าไปที่เดิม ถ้าเป็นข้อมูลที่ Sheet อยู่ใน ไฟล์งานเดียวกันไม่ติดปัญหาครับ แต่พอจะเรียกข้อมูลที่อยู่คนละเครื่อง เวลาRunมาโครมันติดปัญหาครับ ผมขอสอบถามดังนี้ครับ
1. ผมมีไฟล์งานชื่อ Plannew.XLSM อยู่ที่เครื่อง A ต้องการดึงข้อมูลจากวงแลน ในเครื่อง \\ACCOUNT\DATA (D)\SALE\SaleData.xlsx มาแสดงที่ไฟล์งาน Plannew.xlsm เมื่อแก้ไขเสร็จ จากนั้นก็ส่งข้อมูลกลับไปไว้ที่ตำแหน่งเดิมในเครื่อง \\ACCOUNT\DATA (D)\SALE\SaleData.xlsx
2. ผมใช้คำสั่ง F5 เรียกหน้าต่าง GOTO แล้วพิมพ์ offset([SaleData.xlsx]Total!a1,L1,0,1,21) โดยเปิดไฟล์งาน SaleData ไว้ก่อน มันสามารถไปหาข้อมูลได้ตรงครับ
3. ทำการบันทึกมาโคร code ที่ได้

Code: Select all

Sub Macro33()
'
' Macro33 Macro
'

'
    Workbooks.Open Filename:="\\ACCOUNT\Data (D)\SALE\SaleData.xlsx"
    ThisWorkbook.Activate
    Application.Goto Reference:="OFFSET(TOTAL!R[-8]C,R1C12,0,1,21)"
End Sub

จะ error ที่ บรรทัด

Code: Select all

Application.Goto Reference:="OFFSET(TOTAL!R[-8]C,R1C12,0,1,21)"

ผมลองแก้ไขเป็น

Code: Select all

Application.Goto Reference:="OFFSET([SaleData.xlsx]TOTAL!R[-8]C,R1C12,0,1,21)"

ปรากฎว่าไม่ error ครับ แต่ไม่ตรงตำแหน่งที่ต้องการครับ
ผมต้องอยากทราบ CODE สำหรับการไปหาข้อมูลที่อยู่ต่างเครื่องครับ

[parakorn]

ผมคิดว่าตอนบันทึก น่าจะอ้างอิงไม่ถูกต้องนะครับ
R[-8]C คือ คอลัมภ์ปัจจุบันถอยบรรทัดขึ้นไป 8 บรรทัด ซึ่งหมายถึงต้องมี Cell Ref ที่อ้างอิง
ลองเปลี่ยนเป็น R1C1 ดูครับ

[sakajohn]

ลองแล้วครับ ค่าที่ได้เวลารันมาโคร จะไม่ตรงตำแหน่งครับ

[snasui]

ตัวอย่างการปรับ Code ครับ

offset([SaleData.xlsx]DATA!a1,[plannew.xlsm]Sheet1!E1,0,1,5)

[sakajohn]

ขอบคุณครับอาจารย์

[sakajohn]

อาจารย์ครับรบกวนสอบถามเพิ่มเติมครับ ที่ไฟล์ SaleData.xlsx มี sheet Sale1, Sale2, DATA ที่ Sheet DATA ทำหน้าที่รวมข้อมูลใน Sale1และSale2 มาต่อเรียงลำดับกันครับ ถ้าจำนวนไม่มากทำงานเร็วครับ แต่ถ้า copy สูตรไว้ประมาณ 5,000 บรรทัด โปรแกรมจะทำงานช้าครับ มีวิธีอื่นแนะนำบ้างมั้ยครับ

[snasui]

ลองปรับสูตรที่ A2 เป็นด้านล่างครับ

=IF($E2<>"",OFFSET(INDIRECT("'"&$E2&"'!A2"),ROWS($A2:A2)-1,COLUMNS($A2:A2)-1),"")

Enter > Copy ไปด้านขวาและลงด้านล่าง สูตรลักษณะนี้หากใช้มากจะทำให้ไฟล์คำนวณช้าครับ

[sakajohn]

อาจารย์ครับ copy ไปแล้วครับ ค่าที่ A3 มันเหมือนกับค่า A2 เลยครับ มันไม่ได้เปลี่ยนตามครับ

[snasui]

แนบไฟล์นั้นมาด้วยแล้วชี้ให้เห็นว่าผิดพลาดตรงไหน อย่างไร จะได้ตอบต่อไปจากนั้นครับ

[sakajohn]

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

[snasui]

ปรับสูตรที่ A2 ใหม่ตามด้านล่างครับ

=IF($E2<>"",OFFSET(INDIRECT("'"&$E2&"'!A2"),COUNTIF($E$2:$E2,$E2)-1,COLUMNS($A2:A2)-1),"")

Enter > Copy ไปด้านขวาและลงด้านล่าง

[sakajohn]

วิธีนี้ถ้าเราcopyสูตรไว้เพื่อรอข้อมูลประมาณ 10000 บรรทัด มันจะช้าหรือเปล่าครับ ถ้าช้าเราจะมีวิธีอื่นมั้ยครับ

[snasui]

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

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

อีกวิธีก็เป็นการใช้ VBA ครับ wordpress/search-multiple-sheets/

[sakajohn]

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

อาจารย์ครับวิธีนี้ต้องเขียนเป็น VBA มั้ยครับ ถ้าเป็น VBA มันก็จะเป็น MACRO ใช่มั้ยครับ ผมติดตรงที่ว่า ไฟล์นี้จะต้องแชร์ให้หลายคนใช้ครับ

[snasui]

สำหรับการคัดลอกแล้ววางเป็นค่าทำด้วย Manual ได้ครับ

ไฟล์ที่ Shared ไม่สามารถใช้ VBA ได้ครับ

[sakajohn]

รบกวนอาจารย์ช่วยแนะนำได้มั้ยครับ ผมติดตรงนี้ครับ พอเขียนเป็น VBA ก็แชร์ไม่ได้ครับ

[snasui]

ผมตอบไปอย่างละเอียดแล้วในโพสต์ที่ #13 ไม่เข้าใจประเด็นใดครับ

[sakajohn]

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

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

[snasui]

ผมตอบไปในโพสต์ที่ #15 เรียบร้อยแล้วครับ

คำว่าทำด้วย Manual คือทำเองด้วยมือ

[sakajohn]

"OFFSET(R9C30,1,0,COUNTIF(C34,"">0""),COUNTA(R9))"

codeนี้ผมได้มาจากการบันทึกมาโคร ใช้ F5 ครับ ถ้าผมต้องการให้ไปทางขวา 30 คอลัมภ์ ต้องเปลี่ยนcode อย่างไรครับ