Excel VBA ในการดึงข้อมูลแต่ละเดือนไปยังอีกไฟล์ที่มีเดือนตรงกัน

[mistertenshi]

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

ปล.ผมแทบไม่มีความรู้ทางด้าน VBA Macro เลยครับ

[parakorn]

แนบไฟล์ตัวอย่างมาด้วย ทุกคนจะได้เข้าถึงปัญหาและทดลองทำได้ครับ

[mistertenshi]

ที่ผมเขียนมาทั้งหมดคือเป็นการเลือกไฟล์ที่ต้องการแล้ว Copy Data ออกมาคำนวณใหม่แล้วค่อยนำค่าที่คำนวณแล้วนั้นไปวางไว้ในไฟล์ปลายทางครับ

[snasui]

กรุณานำ Code ที่เขียนเองแล้วยังติดปัญหามาถามกัน กรณีไม่มีความรู้ยังไม่ควรใช้ VBA จำเป็นต้องศึกษามาเองก่อนตามบำดับ หากเขียนมาแล้วให้ปฏิบัติตามกฎการใช้บอร์ดข้อ 5 ด้านบนครับ

[mistertenshi]

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

[snasui]

ไฟล์ปลายทางต้องการนำข้อมูลใดจากไฟล์ต้นทางมาบ้าง ช่วยแสดงตัวอย่างคำตอบที่ถูกต้องมาด้วย จะได้คำนวณเปรียบเทียบได้

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

[mistertenshi]

สิ่งที่ต้องการคือ การดึงข้อมูลเงิน EST ที่แยก OGC Rank เป็นกลุ่มๆของแต่ละเดือน และ PL จากไฟล์ต้นทางหรือไฟล์ Data_Test.xlsx ไปยังไฟล์ปลายทางหรือไฟล์ Sum_Data.xlsm ที่มีหมวดหมู่ Rank , OGC / IH ในแต่ละเดือนให้ตรงกัน ครับ โดยมีข้อแม้คือข้อมูล EST ที่ถูกดึงมาจากไฟล์ต้นคือ Data_Test.xlsx จำนวนเงินที่ถูกดึงออกมาจำเป็นต้อง คูณ 1,000,000 ก่อนครับแล้วค่อยนำไปใส่ยังไฟล์ปลายทางหรือไฟล์ Sum_Data.xlsm ตาม หมวดหมู่และเดือน

[mistertenshi]

นี่ตัวอย่างไฟล์ครับ

[snasui]

ค่อย ๆ ถามตอบกันไปครับ

เซลล์ E6 ในชีต EST ของไฟล์ Sum_Data.xlsm มีค่า 1785130 นำมาจากเซลล์ไหนของไฟล์ต้นทางบ้าง พิจารณาอย่างไรว่าให้นำค่านั้นมาครับ

[mistertenshi]

เซลล์ E6 ใน sheet EST ในไฟล์ Sum_Data.xlsm มาจาก การใช้ Filter ของ OGC ให้โชว์แค่ตัว g และ Filter Rank ให้โชว์แค่ A และ A B/S แล้วใช้สูตร Subtotal ในเดือน เม.ย.-18 ของช่อง EST โดยสูตร Subtotal นี้คลุมเซลล์ทั้งหมดในช่อง EST ของเดือน เม.ย.-18 ครับ และนำค่านั้นมาคูณด้วย 1,000,000 ครับ จึงได้ค่า 1,785,130 จากไฟล์ที่ชื่อว่า Data_Test.xlsx

[snasui]

ตัวอย่างสูตรที่เซลล์ E6 ครับ

=SUMPRODUCT(--([Data_Test.xlsx]Sheet1!$BT$3:$BT$396="g"),--(ISNUMBER(SEARCH("A",[Data_Test.xlsx]Sheet1!$BY$3:$BY$396))),INDEX([Data_Test.xlsx]Sheet1!$N$3:$BI$396,0,MATCH(LOOKUP(9.99999999999999E+307,$E$4:E$4),[Data_Test.xlsx]Sheet1!$N$1:$BI$1,0)+1))*1000000

Enter > คัดลอกไปด้านขวาเฉพาะเซลล์ที่เกี่ยวข้อง

กรณีเป็น Rank อื่นจะต้องอธิบายมาอย่างละเอียดเช่นเดิมว่ามีวิธีคิดอย่างไรจะได้เขียนสูตรเปรียบเทียบได้ครับ

[mistertenshi]

ได้ผลตามที่ต้องการครับ ขอบคุณอาจารย์ คนควน มากครับ

จะสอบถามเพิ่มเติ่มเกี่ยวกับกรณีที่ ต้องใช้ในการดึงข้อมูลจากไฟล์อื่นนอกจากไฟล์นี้ครับ
เพราะจำเป็นต้องดึงข้อมูลออกมาโดยวิธีเดิมๆแต่ไฟล์ต้นทางที่ต้องดึงออกมา จำเป็นต้องเปลี่ยนไปเรื่อยๆครับ
รบกวนอาจารย์ ช่วยอธิบายขั้นตอนคิดของสูตร
=SUMPRODUCT(--([Data_Test.xlsx]Sheet1!$BT$3:$BT$396="g"),--(ISNUMBER(SEARCH("A",[Data_Test.xlsx]Sheet1!$BY$3:$BY$396))),INDEX([Data_Test.xlsx]Sheet1!$N$3:$BI$396,0,MATCH(LOOKUP(9.99999999999999E+307,$E$4:E$4),[Data_Test.xlsx]Sheet1!$N$1:$BI$1,0)+1))*1000000

ตั้งแต่ --(ISNUMBER(SEARCH("A",[Data_Test.xlsx]Sheet1!$BY$3:$BY$396))),INDEX([Data_Test.xlsx]Sheet1!$N$3:$BI$396,0,MATCH(LOOKUP(9.99999999999999E+307,$E$4:E$4),[Data_Test.xlsx]Sheet1!$N$1:$BI$1,0)+1))*1000000
ทำไมถึงต้องเป็น -- แล้วMATCH(LOOKUP9.99999999999999E+307 หมายความว่าอะไรครับ

[parakorn]

--(ISNUMBER(SEARCH("A",[Data_Test.xlsx]Sheet1!$BY$3:$BY$396))) คือการค้นหาว่า บรรทัดไหนมีตัว A บ้าง แล้วเปลี่ยนเป็นค่า TRUE FALSE ด้วยสูตร ISNUMBER ครับ แล้วใช้ -- เปลี่ยน TRUE เป็น 1 เปลี่ยน FALSE เป็น 0

INDEX([Data_Test.xlsx]Sheet1!$N$3:$BI$396,0,MATCH(LOOKUP(9.99999999999999E+307,$E$4:E$4),[Data_Test.xlsx]Sheet1!$N$1:$BI$1,0)+1) คือการเลือกข้อมูลในช่วง [Data_Test.xlsx]Sheet1!$N$3:$BI$396 ที่มีหัว Column เป็นเดือนที่ตรงกับ หัวตารางในชีต EST ครับ การที่อาจารย์ใช้ MATCH ตามด้วย LOOKUP เพราะการอ้างอิง Cell ที่ Merge ไม่สามารถอ้างอิงได้ทุก Cell เช่นว่า Merge Cell E4 ถึง G4 แต่ถ้าเราต้องการอ้างอิงข้อมูลจะสามารถอ้างอิงได้แค่ E4 สูตร LOOKUP(9.99999999999999E+307,$E$4:E$4) จึงเป็นการขยายการอ้างอิงช่วงข้อมูลเรื่อยๆ โดยเริ่มตั้งแต่ E4 เป็นต้นไป ถ้าพบข้อความล่าสุดคือข้อความใด จะนำข้อความนั้นมาอ้างอิง ดังนั้นจึงตอบโจทย์ เมื่ออ้างอิง F4 G4 สูตร LOOKUP จะนำ E4 มาอ้างอิง แต่เมื่อ Copy ไปทางขวาต่อ ไปพบข้อความที่ H4 ดังนั้น H4 ถึง I4 จะนำข้อมูลจาก H4 มาอ้างอิง

หลังจากได้ผลลัพท์ทั้ง 3 Array แล้ว สูตร Sumproduct จะนำมาคูณกัน

Array ที่ 1 คือ --([Data_Test.xlsx]Sheet1!$BT$3:$BT$396="g") จะคืนค่าเป็น 0 หรือ 1 กรณีบรรทัดนั้นตรงเงื่อนไข
Array ที่ 2 คือ --(ISNUMBER(SEARCH("A",[Data_Test.xlsx]Sheet1!$BY$3:$BY$396))) จะคืนค่าเป็น 0 หรือ 1 กรณีบรรทัดนั้นตรงเงื่อนไขเช่นกัน
Array ที่ 3 คือ INDEX([Data_Test.xlsx]Sheet1!$N$3:$BI$396,0,MATCH(LOOKUP(9.99999999999999E+307,$E$4:E$4),[Data_Test.xlsx]Sheet1!$N$1:$BI$1,0)+1) จะได้เป็นยอดที่มีหัว Column ตรงกับเงื่อนไข

นำทั้ง 3 Array มาคูณกับ Array อื่นๆ (ถ้าผลลัพท์ใดๆคูณด้วย 0 จะได้0) แล้วนำมารวมกันจะได้เป็นผลลัพท์ครับ

[mistertenshi]

ขอขอบคุณ อาจารย์ parakorn มากเลยครับ ที่มาอธิบายให้ผมเข้าใจมากขึ้นในส่วนนี้