Excel Forum

รบกวนหน่อยครับ มีไฟล์ *.xls เยอะมาก อยากสร้าง FileMain.xlsx มาเช็ค

ที่ไฟล์ *.xls จะไม่ทำอะไร (แต่ใส่สูตรตารางเหลืองให้ได้ผลลัพท์เป็น referent), ชื่อไฟล์ และ sheet จะเป็นคำเดียวกัน

ความต้องการคือ ที่ไฟล์ FileMain.xlsx, ที่ B4 กรอกชื่อไฟล์ แล้วต้องการให้ H2 to H14 คำนวนค่าจากไฟล์ xls นั้นๆ มาให้ ซึ่งต้องจับ location จาก sheet Stock ที่ไฟล์ xlsx นี้ครับ

กรุณาระบุ Version ของ Excel ที่ใช้ใน Profile ตาม Link นี้ด้วยครับ https://snasui.com/viewtopic.php?p=103177#p103177

การทำตามที่ถามมานั้นต้องอาศัย VBA ทั้งหมดหรือบางส่วน

หากใช้ VBA บางส่วนเช่นช่วยเปิดและปิดไฟล์ที่ระบุในเซลล์ B4 ส่วนที่เหลือสามารถใช้สูตรเข้ามาช่วยได้ ทั้งนี้เนื่องจากฟังก์ชัน Indirect ที่ใช้ Reference ชื่อไฟล์ ชื่อชีตในสูตรใด ๆ จะต้องทำงานกับไฟล์ที่เปิดอยู่ ไม่สามารถทำงานกับไฟล์ที่ปิดอยู่ได้ ไม่มีฟังก์ชันอื่นใดที่ใช้ทดแทนหรือทำงานแบบ Indirect ได้

สำหรับ Code VBA ที่ใช้ในการเปิดและปิดไฟล์ลองค้นหาและเขียนมาเองก่อน ติดตรงไหนค่อยถามกันต่อ ในการเปิดต้องระบุ Path (ตำแหน่งที่เก็บไฟล์ในเครื่อง) ให้กับโปรแกรมด้วยจึงจะเปิดไฟล์นั้น ๆ ได้

สิ่งที่ต้องดำเนินการ

1. สร้าง Code เปิดไฟล์ตาม B4 > สร้างปุ่มเพื่อคลิกให้ทำงานตาม Code นี้
2. สร้าง Code ปิดไฟล์ตาม B4 > สร้างปุ่มเพื่อคลิกให้ทำงานตาม Code นี้
3. เขียนสูตรสำหรับตรวจสอบการทำงาน ดูตัวอย่างตามด้านล่าง

ลำดับการทำงาน

1. คลิกปุ่มเพื่อเปิดไฟล์
2. ตรวจสอบผลลัพธ์
3. คลิกปุ่มเพื่อปิดไฟล์

ตัวอย่างสูตร

1. ที่ C5 คีย์สูตรแสดงช่วงข้อมูลในคอลัมน์ G ของไฟล์ต้นทางที่ต้องการใช้เป็นเงื่อนไข
   ="'["&B4&"]"&C4&"'!G2:G60"
   Enter
2. ที่ C6 คีย์สูตรแสดงช่วงข้อมูลในคอลัมน์ K ของไฟล์ต้นทางที่ต้องการใช้เป็นเงื่อนไข
   ="'["&B4&"]"&C4&"'!K2:K60"
   Enter
3. ที่ H3 คีย์สูตรแสดงผลลัพธ์
   =SUMPRODUCT(--(INDIRECT($C$5)<>""),--(MONTH(INDIRECT($C$5))=MONTH(G3)),--(ISNA(MATCH(INDIRECT($C$6),Stock!$A$2:$A$11,0))))
   Enter > Copy ลงด้านล่าง

Update แล้วครับ

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

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

ทำได้ตามโจทย์ต้องการแล้วครับ ขอบคุณมากครับอาจารย์