Snasui Excel Forum

ปรึกษาอาจารย์ทุกท่านครับ
ผมต้องการรวบรวมข้อมูลของลูกค้าที่ส่งมาให้ โดยมีไฟล์ 2 ประเภทคือ
ไฟล์แรกชื่อ Tracking file.xlsx เป็นไฟล์ที่ต้องการรวบรวมข้อมูล โดยมีเนื้อที่ต้องการหาดังนี้
1.) มีไฟล์ชื่อนี้อยู่ใน floder(สมมติชื่อ DATA) หรือไม่ ถ้า มี ตอบ "มี" ถ้าไม่มีตอบ "ไม่มี"
2.) ถ้ามีไฟล์ในfloderแล้ว ให้แสดงว่าในไฟล์นั้นมีระบุว่ารายการของใบอนุญาตดังนี้
2.1) มีใบอนุญาต A หรือไม่ ถ้ามีให้แสดงว่า "มี" และแสดง "วันหมดอายุ"
2.2) มีใบอนุญาต B หรือไม่ ถ้ามีให้แสดงว่า "มี" และแสดง "วันหมดอายุ"
2.3) มีใบอนุญาต C หรือไม่ ถ้ามีให้แสดงว่า "มี" และแสดง "วันหมดอายุ"

กลุ่มไฟล์ที่2 เป็นไฟล์ xxxx.xlsx ซึ่งเป็นไฟล์ที่ลูกค้าส่งกลับมาให้

รบกวนช่วยแนะนำด้วยครับ
ขอบคุณครับ

ผมลองที่cell c3 ของไฟล์ Tracking file

=INDIRECT("["&TEXT( $A3,"0000")&"]"&"Data"&"'!"&C3) มันขึ้น #REF!

แก้ยังไงดีครับ หรือต้องใช้วิธีอี่น

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

หากจะทำลักษณะนั้นพอจะมีวิธีการทำได้โดยให้เปิดไฟล์ Tracking file.xlsx ไว้พร้อมกับไฟล์ต้นทาง หากไฟล์ต้นทางยังไม่มีให้สร้างเอาไว้ก่อนแล้วค่อยลบทิ้งเมื่อมีการเขียนสูตรเสร็จแล้วก็ทำได้ ที่ต้องเปิดไว้พร้อมกันก่อนเพียงเพื่อสะดวกในการเขียนสูตรเท่านั้น

สมมุติไฟล์ต้นทางคือ 0123.xlsm ที่ไฟล์ Tracking file.xlsx เขียนสูตรดังนี้

1. ที่ B3 คีย์
   =IF(ISERR([0123.xlsx]Data!$A$1),"ไม่มี","มี")
   Enter > Copy ลงด้านล่าง
2. ที่ C3 คีย์
   =IF($B3="ไม่มี","",IF(SUMPRODUCT(--([0123.xlsx]Data!$B$3:$B$5=C$2),--([0123.xlsx]Data!$C$3:$C$5="มี")),"มี","ไม่มี"))
   Enter > Copy ลงด้านล่าง
3. ที่ D3 คีย์
   =IF($B3="ไม่มี","",SUMPRODUCT(--([0123.xlsx]Data!$B$3:$B$5=C$2),[0123.xlsx]Data!$D$3:$D$5))
   Enter > Copy ลงด้านล่าง

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

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

อาจารย์ snasui ครับ
ขอบคุณมากครับ แต่ขออนุญาตถามเพิ่มเติมครับ
1. จากข้อที่1 พอ copy ลงมาข้างล่าง มันฟ้องเป็นมีหมดเลยครับ
1. ถ้าไฟล์ต้นทาง เป็นเลขเช่น 1234,1235,1236 ผมต้องเปลี่ยนสูตร [0123.xlsx] เป็นเลขตามชื่อไฟล์ใช่ไหมครับ ถ้าเป็นแบบน้ันผมต้องเปิดไฟล์และแก้สูตรประมาณ 400คร้ัง(มีประมาณ 400ไฟล์ครับ) มีวิธีแก้ไขไหมครับ
2. พอเราทำสูตรเสร็จหมดแล้ว สามารถปิดและลบไฟล์ต้นทาง(ที่สร้างเอาไว้ก่อน)ได้ใช่ไหมครับ
ขอบคุณครับ

sommonr99 wrote: Sun Jun 16, 2019 8:08 pm 1. จากข้อที่1 พอ copy ลงมาข้างล่าง มันฟ้องเป็นมีหมดเลยครับ
1. ถ้าไฟล์ต้นทาง เป็นเลขเช่น 1234,1235,1236 ผมต้องเปลี่ยนสูตร [0123.xlsx] เป็นเลขตามชื่อไฟล์ใช่ไหมครับ ถ้าเป็นแบบน้ันผมต้องเปิดไฟล์และแก้สูตรประมาณ 400คร้ัง(มีประมาณ 400ไฟล์ครับ) มีวิธีแก้ไขไหมครับ

ถูกต้องครับ

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

ขอบคุณอาจารย์ที่แนะนำ ได้แนวทางมากขึ้นเยอะเลยครับ
เดี๋ยวผมลองดูเรื่อง VBA ก่อนครับ
ขอบคุณอีกคร้ังครับ