ใช้ multiple match จับคู่ข้อมูลหลายเงื่อนไข

[pori]

มีข้อมูลอยู่ 2 ชีท ต้องการหาว่า รายการที่อยู่ในชีท 2 นั้น มีอยู่ในชีท 1 หรือไม่ ถ้ามี ให้แสดงวันที่ในคอลัมน์ Postg Date ในชีทที่ 1 ออกมา

ตอนแรกใช้สูตร index+match หลายเงื่อนไขช่วย โดยมีเงื่อนไขที่ใช้เป็น keyword ได้แค่ 2 อย่าง คือ จำนวนเงินที่เท่ากัน(ไม่สนใจ +/-) และข้อความเลข 206 แต่พบปัญหาว่า จำนวนเงินที่เท่ากันนั้นมีหลายรายการและเป็นคนละวันที่ พอใช้สูตรด้วยเงื่อนไขแค่ 2 อย่างเท่านั้น ข้อมูลจึงแสดงออกมาแต่รายการแรกที่เจอ ซึ่งไม่ถูกต้อง


ตัวอย่าง
ประเภท ว/ทเอกสาร Postg Date PK จำนวนเงินในสกุลในปท.
BP 05.09.2013 05.09.2013 40 1,100,000.00
BP 26.11.2012 26.11.2012 40 1,100,000.00
BP 29.09.2011 29.09.2011 40 1,100,000.00


ต้องทำอย่างไร ใช้สูตรแบบไหน จึงจะหาคำตอบให้ตรงรายการได้บ้างคะ แนบไฟล์มาด้วยค่ะ

ขอบคุณที่ชี้แนะค่ะ

[tupthai]

ลองดูนะครับว่าตรงตามที่ต้องการหรือเปล่า

ที่ V2 พิมพ์สูตร
=IFERROR(INDEX(sheet1!$N$1:$N$24,SMALL(IF(ABS($P$2:$P$24)=ABS(sheet1!$P$2:$P$24),IF($T$2:$T$24=sheet1!$T$2:$T$24,ROW(sheet2!$T$2:$T$24))),ROW()-1)),"")

กด ctrl+shift+enter > copy ลงด้านล่าง

[pori]

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

ขอรบกวนอีกนิดนึง สูตรตรงช่วงอื่นพอเข้าใจหมด ยกเว้นตรง Row คือทำไมต้อง -1 แล้ว row นี้มันทำอย่างไรกับสูตรบ้างคะ พอดีลองหาคำตอบแล้วยังงงอยู่ค่ะ

ขอบคุณที่ชี้แนะค่ะ



จากรูปตรงสีแดงเหมือนสลับกันค่ะ

[tupthai]

=SMALL(array, k)
ค่า k ที่ต้องการหา คือลำดับที่ 1.2.3.....เป็นต้นไป
สูตรที่นำไปว่างแถวแรกคือ แถวที่ 2
ถ้าใช้แค่ =row() จะได้ค่าเท่ากับ 2 แต่เราต้องการเริ่มที่ 1 จึงลบออกออก 1
จึงใช้สูตร =SMALL(array, row()-1)

=IFERROR(INDEX(sheet1!$N$1:$N$24,SMALL(IF(ABS($P$2:$P$24)=ABS(sheet1!$P$2:$P$24),IF($T$2:$T$24=sheet1!$T$2:$T$24,ROW(sheet2!$T$2:$T$24))),ROW()-1)),"")

ต้องการ Postg Date จากชีทไหน 1 หรือ 2 ครับเพราะสูตรนี้ดึงมาจากชีท 1
แต่ถ้าดึงจากชีท 2 จะได้ค่าตรงกันให้เปลี่ยนตรงที่ระบายสี

[pori]

ขอโทษค่ะ อาจอธิบายไม่ละเอียดพอ sheet1 คือ ข้อมูลเงินโอนจากสำนักงานใหญ่ ส่วน sheet2 คือ ข้อมูลเงินโอนจากสาขา

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

จึงเอา sheet 2 เป็นหลัก แล้วไปหาจาก sheet 1 ว่าแต่ละรายการในชีท 2 มันมีอยู่ในชีท 1 หรือไม่ ถ้ามีให้แสดงวันที่ในชีท 1 ออกมา เพื่อเปรียบเทียบวันที่ที่อยุ่ในชีท 2 (ต้องการเปรียบเทียบวันที่ กรณีที่มันโอนคนละวันกันค่ะ)

จากกรณียอดเงิน 1,100,000.00 นั้น ในชีท2 มีทั้งหมด 3 รายการ ซึ่งเมื่อดูในชีท 1 ก็ มี 3 รายการนี้เช่นกัน


ชีท 1 เรียงลำดับวันตามนี้ คือ 29.09.2011, 05.09.2013, 26.11.2012

ชีท 2 เรียงลำดับตามนี้ คือ 05.09.2013, 26.11.2012, 29.09.2011

กรณีนี้คือโอนวันเดียวกันทั้งหมด ดังนั้น คำตอบที่ต้องการในชีท 2 ก็คือจะต้องเรียงตามนี้ด้วยค่ะ 05.09.2013, 26.11.2012, 29.09.2011



ส่วนกรณีที่เจอกันคนละวัน สมมติยอด 13,466,000.00 เหมือนกัน 2 ยอด
ชีท 2 พบว่า โอนวันที่ 17.01.2013,09.12.2012
ชีท 1 พบว่ารับโอนวันที่ 18.01.2013, 11.12.2012

คำตอบที่ต้องการให้แสดงออกมา คือ รายการในชีท 2 ที่โอนวันที่ 17.01.2013 ก็ควรจับคู่กับ ชีท 1 ที่รับโอนมาวันที่ 18.01.2013 (ไม่ใช่ 11.12.2012)

ส่วนรายการถัดมา ที่ชีท 2 รับโอนมาวันที่ 09.12.2012 ก็ควรจะจับคู่กับวันที่ 11.12.2012 ในชีท 1 (ไม่ใช่ 18.01.2013)

ประมาณนี้ค่ะ รบกวนแนะนำด้วย คิดไม่ออกจริงๆ หรือวิธีที่คิดมานี่มันไม่ work คะ (ความรู้เท่าหางอึ่งเองค่ะ)


สูตรที่คุณtupthai แนะนำมาเหมือนใช้ได้ แต่จะดึงตามลำดับบรรทัดที่อยู่ในชีทที่1 ซึ่งไม่ใช่คำตอบที่ต้องการน่ะค่ะ

ชี้แนะด้วยนะคะ ขอบคุณมากๆ


ข้อมูลที่โหลดออกมาอาจไม่เรียงตามวันก่อนหลังก็ได้ค่ะ บางครั้งมันก็กระโดดไปกระโดดมา และไม่ได้เรียงบรรทัดเหมือนกันทั้ง 2 ชีทด้วยค่ะ

[tupthai]

ลองทดสอบดูครับ

1.ที่ชีท 2 เนื่องจากคอลัมน์ N วันที่เป็นข้อความนำไปคำนวณไม่ได้จึงใช้คอลัมน์ W ใส่วันที่แทน
ที่ W2=DATE(RIGHT(N2,4),MID(N2,4,2),LEFT(N2,2))
enter >copy ลงด้านล่าง

ที่ชีท 1 ก็ทำเหมือนกันได้นำวันทีไปไว้ที่ คอลัมน์ U ใช้สูตรเดียวกัน

2.ที่ V2 =IF(SUMPRODUCT(--IF(P2=ABS(sheet1!$P$2:$P$20),IF(T2=sheet1!$T$2:$T$20,ROW(sheet1!$T$2:$T$20)))),INDEX(sheet1!$U$1:$U$20,SMALL(IF(SMALL(ABS(W2-IF(P2=ABS(sheet1!$P$2:$P$20),IF(T2=sheet1!$T$2:$T$20,sheet1!$U$2:$U$20))),1)=ABS(W2-IF(P2=ABS(sheet1!$P$2:$P$20),IF(T2=sheet1!$T$2:$T$20,sheet1!$U$2:$U$20))),ROW(sheet1!$U$2:$U$20)),1)),"")

กด ctrl+shift+enter > copy ลงด้านล่าง

[pori]

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

=IF(SUMPRODUCT(--IF(P2=ABS(sheet1!$P$2:$P$20),IF(T2=sheet1!$T$2:$T$20,ROW(sheet1!$T$2:$T$20)))),INDEX(sheet1!$U$1:$U$20,SMALL(IF(SMALL(ABS(W2-IF(P2=ABS(sheet1!$P$2:$P$20),IF(T2=sheet1!$T$2:$T$20,sheet1!$U$2:$U$20))),1)=ABS(W2-IF(P2=ABS(sheet1!$P$2:$P$20),IF(T2=sheet1!$T$2:$T$20,sheet1!$U$2:$U$20))),ROW(sheet1!$U$2:$U$20)),1)),"")

sumproductเอาไว้นับจำนวนรายการที่ตรงกับเงื่อนไข ถ้ามีรายการก็ให้ไป index หาช่วงวันที่ใน sheet1 แต่ตรงช่วง small หลายๆๆครั้ง ยังมึนอยู่เลยค่ะ ตรงที่ใช้ ABS=ABS นี่คือจะ test ว่า true หรือ False เหมือนกันหรือไม่ ถ้า true มันจะโชว์ ROW(sheet1!$U$2:$U$20) ส่วน K = 1 คือหาวันที่น้อยที่สุดหรือเปล่า


โอย คือ ขอโทษที่เขียนวกไปวนมา แบบว่าพยามยามหาเหตุผล และทำความเข้าใจสุดๆๆแล้วค่ะ อยากทำความเข้าใจสูตร จะได้ไปประยุกต์ต่อได้ ไม่ใช่ copy -plate อย่างเดียวค่ะ มันเป็นประมาณแบบนี้หรือเปล่าคะ

แบบว่าถ้าไม่รบกวนเกินไปนัก ช่วยอธิบายความให้กระจ่างกว่าที่ตัวเองเขียนข้างบนหน่อยได้ไหมคะ คืออ่านเองแล้วยังมึนเลยค่ะ จะพยายามต่อไปค่ะ

ขอบคุณที่แนะนำ เว็บนี้มีแต่คนเก่งๆๆทั้งนั้นเลยค่ะ หวังสักวันเราคงจะเก่งอย่างนี้ แล้วตอบคำถามคนอื่นได้บ้างค่ะ

[pori]

จากสูตรที่ให้มาในตัวอย่างใช้ได้ดีค่ะ แต่พอเอามาใช้กับข้อมูลจริง ที่โหลดออกมาดิบๆๆจาก sap ปรากฏว่า พบปัญหาดังนี้ค่ะ

1. จากสูตรเดิม ถ้าเจอกรณีที่ เข้าเงื่อนไขข้อเดียวคือ จำนวนเงินเท่ากันในชีท1 และ 2 แต่เงื่อนไขส่วนข้อความ ไม่ตรงกัน สูตรกลับแสดงค่าวันที่ให้ได้ (เอามาจากไหนไม่ทราบค่ะ งง) ทำไมถึงไม่แสดงค่าว่างให้แทนละคะ

2. ถ้าอยาก copy สูตรลงมาเผื่อไว้หลายๆๆบรรทัด กรณี เผื่อถ้ามีข้อมูลยาวกว่าเดิม แต่ปรากฏขึ้น error ค่ะ อยากให้เป็นช่องว่างๆ แทนจะได้ไหมคะ


3. ข้อมูลดิบจะมีบรรทัดว่าง และบรรทัดหัวแถว แทรกอยู่เป็นระยะ ทำให้สูตรที่ใช้นั้นเกิด error ขึ้นค่ะ (คิดว่าเพราะมีเซลล์ text ปะปนอยู่ ทำให้ สูตร ABS เกิด error แล้วไม่สามารถใช้ต่อใน sumproduct ได้ - ไม่แน่ใจนะคะ อันนี้คิดเองจากการ test สูตรค่ะ)


รบกวนขอคำชี้แนะอีกครั้งด้วยค่ะ อยากหาวิธีแก้ไขเอง ลองอ่านนู่นนี่แล้วเผื่อจะแก้ไหว แต่ปัญญาอันน้อยนิด คิดไม่ออกเลยค่ะ ขอบคุณมากๆๆค่ะ

[snasui]

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

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

ผมได้แทรกลำดับของข้อมูลใน Sheet1 และ Sheet2 อยู่ที่ คอลัมน์ V และ W ตามลำดับ

สูตรที่คุณ tubthai เขียนมาผมได้เปลี่ยนให้เป็น Sum แทน Smproduct และเพิ่ม Iserror เพื่อเปลี่ยนค่า Error ให้เป็นค่าว่างเท่านั้น ส่วนอื่น ๆ เหมือนเดิมทั้งหมดและให้ผลลัพธ์เช่นเดิมยกเว้นเปลี่ยนค่าผิดพลาดให้เป็นค่าว่าง ที่ต้องเปลี่ยนเพื่อลดการเกิด Error หากเจอ Text ซึ่งจะได้สูตรเป็น

Code: Select all

=IFERROR(IF(SUM(IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,ROW(sheet1!$T$2:$T$23)))),INDEX(sheet1!$U$1:$U$23,SMALL(IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23)),1)),""),"")

หมายถึง หาก

Code: Select all

SUM(IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,ROW(sheet1!$T$2:$T$23))))

เป็นจริงแล้ว ให้นำผลลัพธ์ของ

Code: Select all

INDEX(sheet1!$U$1:$U$23,SMALL(IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23)),1))

มาแสดง หากไม่เป็นจริงให้แสดงค่าว่าง

จากสูตร

Code: Select all

INDEX(sheet1!$U$1:$U$23,SMALL(IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23)),1))

หมายถึง จากช่วงเซลล์ sheet1!$U$1:$U$23 ให้นำลำดับที่เป็นผลลัพธ์ของ

Code: Select all

SMALL(IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23)),1)

มาแสดง

จากสูตร

Code: Select all

SMALL(IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23)),1)

หมายถึง ให้นำค่าที่น้อยที่สุดเป็นลำดับที่ 1 ของ

Code: Select all

IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23))

มาแสดง

จากสูตร

Code: Select all

IF(SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),ROW(sheet1!$U$2:$U$23))

หมายถึง ถ้า

Code: Select all

SMALL(ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))),1)=ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23)))

เป็นจริง ให้แสดงผลลัพธ์ของ

Code: Select all

ROW(sheet1!$U$2:$U$23)

ซึ่งก็คือให้แสดงลำดับบรรทัด

จากสูตร

Code: Select all

ABS(U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23)))

หมายถึงค่าสัมบูรณ์ของ

Code: Select all

U2-IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))

จากสูตร

Code: Select all

IF(P2=ABS(sheet1!$P$2:$P$23),IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23))

หมายถึง หาก

Code: Select all

P2=ABS(sheet1!$P$2:$P$23)

เป็นจริง ให้แสดงผลลัพธ์ของ

Code: Select all

IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23)

จากสูตร

Code: Select all

IF(T2=sheet1!$T$2:$T$23,sheet1!$U$2:$U$23)

หมายถึง หาก

Code: Select all

T2=sheet1!$T$2:$T$23

เป็นจริง ให้แสดงผลลัพธ์ของ

Code: Select all

sheet1!$U$2:$U$23

จากตัวอย่างการแกะสูตรด้านบน การแทนค่าควรจะแทนค่าจากด้านล่างขึ้นด้านบน จะทำให้เข้าใจได้สะดวก

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

[pori]

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

ขอบคุณมากค่ะสำหรับคำแนะนำ

[pori]

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

[pori]

ทำไฟล์ตัวอย่างใหม่แล้วค่ะ จำลองข้อมูลดิบที่โหลดออกมา ซึ่งจะมีแถวว่าง และหัวคอลัมน์แทรกอยู่เป็นระยะ คิดว่าอาจจะทำให้สูตร abs มีปัญหา (ไม่แน่ใจนะคะ )

รบกวนแนะนำด้วยค่ะ

ป.ล. ไฟล์ใหญ่เกินไป แนบไม่ได้ ส่งทางเมล์แทนได้ไหมคะ

[pori]

ขออภัยค่ะ ได้ทำไฟล์มาใหม่ให้เล็กลงแล้วค่ะ รบกวนชี้แนะด้วยค่ะ

[snasui]

2. จากข้อมูลดิบพบว่า คอลัมน์ข้อความในชีทสาขาไม่ได้กรอกครบทุกบรรทัด
จึงมาระบุรหัสที่ต้องการไว้ที่ z1 แทนค่ะ

จะต้องเติมให้เต็ม เว้นว่างไม่ได้ ไปเติมไว้ที่เซลล์อื่นเช่นนั้นไม่ได้ครับ

[pori]

2. จากข้อมูลดิบพบว่า คอลัมน์ข้อความในชีทสาขาไม่ได้กรอกครบทุกบรรทัด
จึงมาระบุรหัสที่ต้องการไว้ที่ z1 แทนค่ะ

จะต้องเติมให้เต็ม เว้นว่างไม่ได้ ไปเติมไว้ที่เซลล์อื่นเช่นนั้นไม่ได้ครับ

ทดลองเติมข้อความให้เต็มแล้ว แต่ก็ #value อยู่ดีค่ะ หรือว่าดิฉันกรอกสูตรอะไรผิดไปหรือเปล่าคะ

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

ขอรบกวนอีกครั้ง ขอบคุณมากๆเลยค่ะ จะพยายามต่อไปค่ะ

[snasui]

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

จาก

3. คีย์ที่ใช้จับคู่ คือ จำนวนเงิน ในชีท สาขา ที่ตรงกับจำนวนเงินในชีท สนญ และต้องมีรหัสข้อความในชีท สนญ. = 206
4. ถ้าจับคู่กันได้ (จำนวนเงินเท่ากัน และรหัส ตรงกัน ) ให้แสดงวันที่ Postg Date ในชีท สนญ ออกมา
5. กรณีที่มีจำนวนเงินเท่ากันหลายรายการ จะต้องจับคู่วันที่ให้ถูกรายการด้วยค่ะ

โจทย์แค่นี้ไม่สามารถขจัดรายการที่มีจำนวนเงินตรงกัน ข้อความตรงกัน แต่ต่างวันที่กันได้ ความต่างของวันที่จะต้องระบุด้วยว่า ต่างกันกี่วันจึงจะไม่เอามาแสดง ดังเช่นรายการในบรรทัด 31, 32 ในชีท สาขา ตามไฟล์แนบ จะยังให้คำตอบได้ จนกว่าจะระบุได้ว่า วันที่ต่างกันเท่าไรจึงจะถือว่าไม่ตรงกันและต้องไม่นำมาแสดง

สูตรตัวอย่างในเซลล์ W3 ชีท สาขาตามด้านล่าง

Code: Select all

=IF(T3="","",IFERROR(INDEX(สนญ!$U$4:$U$85,MATCH(MIN(IF(ISNUMBER(สนญ!$P$4:$P$85),IF(P3=ABS(สนญ!$P$4:$P$85),IF(T3=สนญ!$T$4:$T$85,ABS(สนญ!$U$4:$U$85-U3))))),IF(ISNUMBER(สนญ!$P$4:$P$85),IF(P3=ABS(สนญ!$P$4:$P$85),IF(T3=สนญ!$T$4:$T$85,ABS(สนญ!$U$4:$U$85-U3)))),0)),""))

[pori]

ขอบคุณอาจารย์มากค่ะที่ชี้แนะ ขออธิบายเพิ่มเติม ดังนี้

จุดประสงค์ คือ เจ้านายให้โจทย์มาว่า อยากรู้ว่า สาขาโอนเงิน และ สนญ.รับเงินนั้น เป็นวันเดียวกันหรือไม่ เพราะจริงๆแล้วควรจะต้องเป็นวันเดียวกัน และวันที่สนญ. รับโอน ควรต้องเป็นวันที่หลังจากสาขาโอนค่ะ เช่น สาขาโอน 02.05.13 สนญ ควรรับ 02.05.13 (ถ้าตรงวัน) หรือถ้าไม่ตรง อาจเป็น 03.05.13 ก็ได้ แต่ไม่ควรเป็น 01.05.13 (ก่อนวันที่สาขาโอนไม่ได้ค่ะ)


แต่ถ้ามีเหลื่อมวันกันบ้าง ก็สันนิษฐานเอาว่ามันไม่น่าจะนานนัก อาจเป็น 1-3 วัน แต่ก็ระบุไม่ได้จริงๆค่ะ ว่าต่างกันกี่วันจึงไม่นำมาสดงค่ะ รู้แต่ว่า ถ้าต่างกันเป็น สิบๆๆวัน ร้อยวัน ไม่น่าใช่น่ะค่ะ

สำหรับตัวอย่างจากบรรทัด ที่ 31-32 เป็นกรณีที่สมมติขึ้นมาจากข้อสันนิษฐานว่า สนญ รับโอนมาจากหลายสาขา อาจเป็นไปได้ว่า จำนวนเงินที่โอนเข้ามาจากหลายสาขา อาจเท่ากันก็ได้ ซึ่งดูจากข้อความที่คอลัมน์ T ว่า มาจากสาขารหัสไหนค่ะ

เช่น ยอดเงิน 2600000 นั้น ในชีท สนญ มี 7 รายการ (ตัวหนังสือสีม่วง) มาจากสาขา 206 จำนวน 5 รายการ และสาขา 206e อีก 2 รายการ

ในชีท สาขา พบยอดเงิน 2600000 อยู่ 7 รายการเหมือนกัน แต่จับคู่ได้ว่า 5 รายการนั้น โอนไปที่ สนญ. (เพราะพบในชีท สนญ.) แต่อีก 2 รายการที่เหลือ ทดลองดูว่าถ้าข้อความระบุเป็นอย่างอื่นที่ไม่ใช่ 206 (ในชีทสาขา) สูตรที่อาจารย์ให้มาจะทำงานได้อย่างดีเลยค่ะ

แต่ถ้าสมมติว่า ข้อความที่ระบุในชีทสาขาเป็น 206 เช่นกัน แต่เป็นการถอนไปใช้อย่างอื่นที่ไม่ใช่การโอนไปยัง สนญ (ที่จริงควรระบุรหัสอื่น แต่ user อาจกรอกผิด) ก็ไม่ควรจะแสดงวันที่ให้เห็นน่ะค่ะ

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

ประมาณนี้ค่ะอาจารย์ ขออภัยถ้าเขียนอธิบายไม่กระจ่างค่ะ ได้ความรู้เพิ่มขึ้นเยอะมากๆๆเลยค่ะ

แก้ไข ลืมแนบไฟล์ค่ะ

[snasui]

แต่ถ้ามีเหลื่อมวันกันบ้าง ก็สันนิษฐานเอาว่ามันไม่น่าจะนานนัก อาจเป็น 1-3 วัน แต่ก็ระบุไม่ได้จริงๆค่ะ ว่าต่างกันกี่วันจึงไม่นำมาสดงค่ะ รู้แต่ว่า ถ้าต่างกันเป็น สิบๆๆวัน ร้อยวัน ไม่น่าใช่น่ะค่ะ

ต้องระบุให้ได้ น้อยกว่ากี่วันก็ว่าไปครับ ถ้าระบุไม่ได้ก็หาคำตอบมาให้ไม่ได้

แต่ถ้าสมมติว่า ข้อความที่ระบุในชีทสาขาเป็น 206 เช่นกัน แต่เป็นการถอนไปใช้อย่างอื่นที่ไม่ใช่การโอนไปยัง สนญ (ที่จริงควรระบุรหัสอื่น แต่ user อาจกรอกผิด) ก็ไม่ควรจะแสดงวันที่ให้เห็นน่ะค่ะ

การกรอกผิดโปรแกรมจะทราบได้อย่างไรว่ากรอกผิด ถ้าไม่สามารถแยกได้ว่าอันไหนกรอกผิด อันไหนกรอกถูก โปรแกรมก็ย่อมหาคำตอบไม่ได้เช่นกันครับ

[pori]

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

[pori]

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

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


อันนี้มาจากบันทึก macro ไว้ ผิดถูกอย่างไรช่วยชี้แนะด้วยค่ะ ตรงสูตร array ถ้าบันทึกแบบนี้ไม่ได้ ต้องเขียนใหม่เองหรือเปล่าคะ คือ ความรู้ vba น้อยมาก ให้เขียนเองขึ้นมาไม่สามารถจริงๆค่ะ นอกจากอาศัย macro ก็พอไปได้หน่อยค่ะ)

Code: Select all

Sub test()
'
' test แมโคร
'

'
    Sheets("สนญ").Select
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "DATE"
    Range("U4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-6]),DATE(RIGHT(RC14,4),MID(RC14,4,2),LEFT(RC14,2)),0)"
    Range("U4").Select
    Selection.Copy
    Range("U5:U85").Select
    ActiveSheet.Paste
    Sheets("สาขา").Select
    Range("U1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "DUMMY"
    Range("U3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-1]),--(RIGHT(RC[-9],4)&MID(RC[-9],4,2)&LEFT(RC[-9],2)),"""")"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "แปลง date"
    Range("V3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(RC[-2]),DATE(RIGHT(RC14,4),MID(RC14,4,2),LEFT(RC14,2)),0)"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "วันที่ผ่านรายการ สนญ"
    Range("W3").Select
    Selection.FormulaArray = _
        "=IF(RC[-3]="""","""",INDEX(สนญ!R[1]C[-2]:R[82]C[-2],MATCH(MIN(IF(ISNUMBER(สนญ!R[1]C[-7]:R[82]C[-7]),IF(สาขา!RC[-7]=ABS(สนญ!R[1]C[-7]:R[82]C[-7]),IF(สาขา!RC[-3]=สนญ!R[1]C[-3]:R[82]C[-3],IF(สาขา!RC[-2]=สนญ!R[1]C[-19]:R[82]C[-19],ABS(สนญ!R[1]C[-2]:R[82]C[-2]-สาขา!RC[-1])))))),IF(ISNUMBER(สนญ!R[1]C[-7]:R[82]C[-7]),IF(สาขา!RC[-7]=ABS(สนญ!R[1]C[-7]:R[82]C[-7]),IF(สาขา!RC[" & _
        "R[1]C[-3]:R[82]C[-3],IF(สาขา!RC[-2]=สนญ!R[1]C[-19]:R[82]C[-19],ABS(สนญ!R[1]C[-2]:R[82]C[-2]-สาขา!RC[-1]))))),0)))"
    Range("U3:W3").Select
    Selection.Copy
    Range("U4:W61").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

ขอบคุณมากๆๆนะคะ