สูตรerror เนื่องจากข้อมูลจำนวนมาก

[eyepop99]

เนื่องด้วย งานที่ผมทำอยู่นั้นมีข้อมูลอยู่จำนวนมาก และใช้สูตรหาค่า โดย Array
ปล.เป็นสูตรที่ อาจารท่านนึงให้มาครับ user admin(snasui)
**ปัญหาอยู่ที่ชีต Summary data ช่อง D ครับ

แล้วปัญหาที่พบตอนนี้ มีการดึงข้อมูลที่ผิดคอลัมป์ เนื่องจากสูตรมีการตรวจเช็ควันที่ที่ผิดพลาดครับ
ยกตัวอย่างเช่น
วันที่ 1-feb-2017 ตัวสูตรนั้น ตรวจสอบว่า ข้อมูลที่นำไปตรวจสอบไม่ตรงกัน แต่ในความความเป็นจริงข้อมูลตรงกันคือ (1-feb-2017)
ลองตรวจเช็คด้วยวิธีการดั่งนี้
- ลองพิมพ์วันที่ใหม่ แล้วก๊อปวางทั้งข้อมูล ต้นทาง และ ข้อมูลปลายทาง ( ก็ยัง error )
- ลองเปลี่ยนวันที่ ที่ละ 1 วัน (ก็ยัง error)
- ลองเปลี่ยนวันที่โดยที่แบบ สุ่ม ( 3-jan-1900) ทั้งต้นทางและปลายทาง ผลลัพที่ได้ ถูกต้อง

ยกตัวอย่างวันที่ ที่error ครับ
วันที่ 1-4 feb 2017 ไม่สามารถเทียบกันได้ // แต่สามารถใช้วันที่ 5 feb-2017 แล้ว ไม่error

มีอีก 1 case คือ สูตรจะขึ้น #VALUE ถ้าใช้วันที่ 29-apr-2017 พอเปลี่ยนวันที่เป็นวันที่ 28-apr-2017 (ทั้งต้นทางและปลายทาง)
สูตรสามารถคำนวนข้อมูลได้ออกมาถูกต้องครับ

ไฟล์ใหญ่ ไม่สามารถแน๊บไฟล์ได้ครับ
จึงupload อีกที่หนี่ง https://ufile.io/58ak7
**Filter ที่ช่อง Remark ใน sheet Summary data ครับ จะเป็นเฉพาะส่วนที่ Error ครับ
** Error มี 2 แบบ ครับ
1 - ที่ขึ้นว่า #VALUE (แก้วันที่ก็หายเช่นกัน)
2- จะดึงข้อมูลมาผิดครับ

[snasui]

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

[eyepop99]

เนื่องจาก การย้ายชีตตัดเฉพาะข้อมูลที่ error ในไฟล์หลัก มาไฟล์ใหม่
- จะมีข้อมูลที่errorบ้างส่วนที่มาอยู่ไฟล์ใหม่แล้วไม่ error
- จะมีข้อมูลที่ยัง error เหมือนเดิม
ปัญหา ไฟล์ที่จะแน๊บ ผมตัดบางตัว ให้เหลือน้อยที่สุด ไม่มี Record ไหน error เลยครับ
พอทำให้เยอะ พอที่จะ พบทั้ง error และ ไม่error ไฟล์ก็ประมาน 5000 kb ไฟล์ ไม่สามารถ อัพโหลดได้ครับ

ผมจึงcapture มาเปรียบเทียบสำหรับ ไฟล์ ที่ error และ ไม่error
ในขณะที่ ข้อมูลตั้งต้นเหมือนกันครับ

not error 1.PNG

Error 1.PNG

[snasui]

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

1. F1 คีย์หัวคอลัมน์เช่น ColNum
2. F2 คีย์สูตร
   =MIN(IF('Raw data'!$A$2:$A$4000=A2,IF('Raw data'!$B$2:$B$4000=B2,IF('Raw data'!$E$2:$Z$4000=C2,COLUMN('Raw data'!$E:$Z)-COLUMN('Raw data'!$E:$E)))))
   Ctrl+Shift+Enter > Copy ลงด้นาล่าง
3. D2 คีย์สูตร
   =SUMIFS(OFFSET('Raw data'!$D$2:$D$4000,0,F2),'Raw data'!$A$2:$A$4000,A2,'Raw data'!$B$2:$B$4000,B2,OFFSET('Raw data'!$D$2:$D$4000,0,F2+1),C2)
   Enter > Copy ลงด้านล่าง

[eyepop99]

ขอบคุณมากครับอาจารปัญญหาได้ถูกแก้แล้ว และอยากทราบปัญหาที่เกิดจาก Error นี้ครับ
เพื่อที่จะได้ศึกษาครับ
ตอนนี้ผมพอเข้าใจการทำงาน แบบArray ของ Excel แล้วผมจึงอยากทราบปัญญา และวิธีแก้ไขคับ

ขอบคุณครับ สำหรับความช่วยเหลือและวิทยาทาน

[snasui]

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

[eyepop99]

ขอบคุณครับ