หาค่าที่ต้องการจากข้อมูลที่ซ้ำกัน

[pori]

ข้อมูลจาก database จะมีเลขสมาชิกซ้ำกันหลายแถว ดังนี้

เลขที่สมาชิก วันหมดรับประกัน
1001 10/1/2013
1001 25/8/2013
1002 14/2/2013
1003 9/9/2011
1003 31/12/9999
1003 16/5/2012
1004 19/7/2015
1004 31/12/9999


ถ้าต้องการค้นหาวันที่ที่นานที่สุดของแต่ละเลขสมาชิก ผลลัพธ์ดังนี้

เลขที่สมาชิก วันหมดรับประกัน
1001 25/8/2013
1002 14/2/2013
1003 31/12/9999
1004 31/12/9999

วันที่ 31/12/9999หมายถึง ไม่มีวันหมดอายุรับประกัน

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

หาค่าที่ต้องการจากข้อมูลที่ซ้ำๆกัน.xlsx

[bank9597]

ลองตามนี้ครับ

ที่ D4 คีย์ =COUNTIF($B$4:B4,B4) คัดลอกลงมา

ที่ F4 คีย์ =IF(ROWS($F$4:$F4)>COUNTIF($D$4:$D$11,1),"",INDEX($B$4:$B$11,SMALL(IF($D$4:$D$11=1,ROW($D$4:$D$11)-ROW($D$4)+1),ROWS($F$4:$F4)))) กด Ctrl+Shift+Enter หากกดถูกต้อง จะมีเครื่องหมาย {} คร่อมสูตรไว้ครับ จากนั้นคัดลอกสูตรลงมา

ที่ G4 คีย์ =IF(F4="","",MAX(IF($B$4:$B$11=$F4,$C$4:$C$11))) กด Ctrl+Shift+Enter คัดลอกสูตรลงมาครับ

[bank9597]

แถมอีกสูตรครับ

ที่ F4 คีย์ =IFERROR(INDEX($B$4:$B$11,MATCH(0,INDEX(COUNTIF($F$3:$F3,$B$4:$B$11),0,0),0)),"") คัดลอกสูตรลงมา

ที่ G4 คีย์ =IF(F4="","",MAX(IF($B$4:$B$11=$F4,$C$4:$C$11)))
กด Ctrl+Shift+Enter คัดลอกสูตรลงมา

[snasui]

=IFERROR(INDEX($B$4:$B$11,MATCH(0,INDEX(COUNTIF($F$3:$F3,$B$4:$B$11),0,0),0)),"")

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

สูตรหาค่า Unique แบบนี้สามารถทำงานได้ดีในเรื่องความกระชับ เนื่องจากสูตรไม่ยาวมากสามารถทำความเข้าใจได้ง่าย แต่ข้อเสียคือหากใช้กับอักขระพิเศษอื่น เช่น เมื่อเซลล์ใด ๆ ในช่วง B4:B11 มีเครื่องหมาย > หรือ < จะทำให้สูตรแสดงผลผิดพลาด แต่สำหรับข้อมูลที่ถามมาตามกระทู้นี้สามารถนำไปใช้ได้ ไม่เกิดข้อผิดพลาดใด

สูตรที่รัดกุมที่สุดสูตรหนึ่งในการหาค่า Unique คือสูตรด้านล่างครับ

=IFERROR(INDEX($B$4:$B$11,SMALL(IF(FREQUENCY(MATCH("~"&$B$4:$B$11,$B$4:$B$11&"",0),ROW($B$4:$B$11)-ROW($B$4)+1),ROW($B$4:$B$11)-ROW($B$4)+1),ROWS(H$4:H4))),"")

Ctrl+Shift+Enter

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

[bank9597]

หาจุดบกพร่องของสูตรผมจนได้น่ะครับ อิอิ

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

[snasui]

หาจุดบกพร่องของสูตรผมจนได้น่ะครับ

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

[bank9597]

หาจุดบกพร่องของสูตรผมจนได้น่ะครับ

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

แล้วกัน ดันรู้ก่อนหน้าเสียอีก อาจารย์เก่งเกินไปแล้วครับ

[pori]

สูตรที่รัดกุมที่สุดสูตรหนึ่งในการหาค่า Unique คือสูตรด้านล่างครับ

=IFERROR(INDEX($B$4:$B$11,SMALL(IF(FREQUENCY(MATCH("~"&$B$4:$B$11,$B$4:$B$11&"",0),ROW($B$4:$B$11)-ROW($B$4)+1),ROW($B$4:$B$11)-ROW($B$4)+1),ROWS(H$4:H4))),"")

สูตรยาวมากมายค่ะ ช่วยอธิบายการทำงานของแต่ละสูตรหน่อยได้ไหมคะ index พอเข้าใจ แต่ สงสัยว่า ทำไมต้องใช้ small, frequency แล้วที่ต้องมี row ด้วยนี่หมายความว่ายังไงเหรอคะ
พอดีเคยเห็นสูตรยาวๆแบบนี้มาบ้างแต่งงก็เลยไม่เคยเอามาใช้เลยค่ะ

ขอบคุณคุณ bank และอาจารย์มากๆค่ะ เก่งมากๆเลย สูตรอลังการงานสร้าง ช่วยประโยชน์ได้เยอะมากจริงๆค่ะ

ป.ล.ลองเอาสูตรคุณ bank ไปทดลองได้ผลที่ต้องการค่ะ แต่สูตรของอาจารย์มันคืนค่าผลลัพธ์ออกมาเป็นเลขที่สมาชิก ไม่ใช่วันที่หมดอายุที่ต้องการค่ะ (หรือดิฉันวางสูตรผิดเองค่ะ งงเล็กน้อย )

[snasui]

ลองศึกษาแต่ละฟังก์ชั่นให้เข้าใจก่อนตาม Link นี้ครับ http://office.microsoft.com/th-th/excel ... aspx?CTT=1 จากนั้นลองแปลผลดู ติดตรงไหนค่อยมาถามกันต่อครับ

สำหรับ Frequency เป็นฟังก์ชั่นที่เข้าใจยาก เป็นการหาความถี่ของข้อมูล โดยมีการกำหนดให้ว่าหาค่าความถี่จากชุดข้อมูลใด ส่วนฟังก์ชั่นที่เหลือคิดว่าไม่ยากนัก

สูตรที่ผมเขียนนั้นเป็นสูตร Array นอกจากจะเข้าใจแต่ละฟังก์ชั่นแล้วต้องเข้าใจความเป็น Array ด้วย สามารถทำความเข้าใจคร่าว ๆ ได้จากความเห็นที่ 3 ใน Link นี้ครับ http://www.atriumtech.com/cgi-bin/hilig ... 07988.html

[snasui]

ป.ล.ลองเอาสูตรคุณ bank ไปทดลองได้ผลที่ต้องการค่ะ แต่สูตรของอาจารย์มันคืนค่าผลลัพธ์ออกมาเป็นเลขที่สมาชิก ไม่ใช่วันที่หมดอายุที่ต้องการค่ะ (หรือดิฉันวางสูตรผิดเองค่ะ งงเล็กน้อย )

ลองลำดับความเข้าใจใหม่ครับว่าผมมาเสริมเฉพาะสูตรใด

[pori]

อ้าว อ่านเร็วเกิน อายจังเลยค่ะ ขออภัยด้วย ก่อนอื่นขอไปศึกษาสูตร Array เพื่อเพิ่มความเข้าใจหน่อย ขอบคุณสำหรับคำตอบมากๆๆนะคะ ดีใจที่มีเว็บนี้ให้ปรึกษาได้ ไม่งั้น สำหรับคนรู้ excel งูๆปลาๆอย่างดิฉันคงมืดแปดด้านไปหลายวันเลย ขอบคุณอีกครั้งค่ะ