อยากรู้ Excel สูตร ข้อความซ้ำ ช่วยหน่อยค่ะ

[ซำเหมา]

ลองแนบไฟล์มาดูหน่อยครับ

ขอพิมพ์แทนนะครับ เพราะในห้างไม่มีเอ๊กเซล ขอบคุณครับ
สมมุติว่า รายชื่อต่อไปนี้หรือข้อความต่อไปนี้อยู่ในคอลัมน์ A นะครับเช่น อาจเริ่มจาก คอลัมน์ A1
A............................................ B ........................................C
นาย แดง สกุลแดง
นายเขียว สกุลเขียว
นายขาว สกุลขาว
นายแดง สกุลแดง
นายขาว สกุลขาว
นายดำ สกุลดำ
คือรายชื่อหรือข้อความเรียงกันไป ตามคอลัมน์ลงไปสมมุติว่าหลายร้อยชื่อหรือข้อความ
ผมอยากจะรู้ว่า ชื่อใครที่มีซ้ำกันบ้างครับ เช่น ในตัวอย่างมีชื่อนายขาว สกุลขาวซ้ำกัน
ก็ให้รายงานออกมาในอีกคอลัมน์เช่น ในคอลัมน์ B เริ่มจาก B1 ก็ได้ครับ หวังว่าคงเข้าใจคำถามนะครับ ขออภัยจริงๆ แต่กลับบ้านจะทำไฟล์แนบมาให้ครับ

[snasui]

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

ที่เซลล์ B2 คีย์

=Countif(A$2:A2,A2)>1

Enter > Copy ลงด้านล่าง แล้วกรองเอาเฉพาะค่าที่เป็น True

การเขียนให้ List ค่าออกมาเลยก็ทำได้แต่สูตรจะซับซ้อนและเข้าใจยาก

[bank9597]

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

[ซำเหมา]

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

ที่เซลล์ B2 คีย์

=Countif(A$2:A2,A2)>1

Enter > Copy ลงด้านล่าง แล้วกรองเอาเฉพาะค่าที่เป็น True

การเขียนให้ List ค่าออกมาเลยก็ทำได้แต่สูตรจะซับซ้อนและเข้าใจยาก

ลองทำแล้วมันได้อย่างที่เห็นครับ ผมพิมพ์ผิดหรือเปล่าครับ รบกวนอาจารย์อีกรอบครับ

[ซำเหมา]

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

ตัวอย่างครับพอดี เมื่อวานตอนเย็นกลับถึงบ้านเครื่องไม่ว่างเลยส่งให้ช้านิด ขออภัยด้วยครับ

[snasui]

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

ที่เซลล์ B2 คีย์

=Countif(A$2:A2,A2)>1

Enter > Copy ลงด้านล่าง แล้วกรองเอาเฉพาะค่าที่เป็น True

การเขียนให้ List ค่าออกมาเลยก็ทำได้แต่สูตรจะซับซ้อนและเข้าใจยาก

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

[bank9597]

ผมลองแยกข้อความออกมา แล้วพิสูจน์เงื่อนไข ลองดูตามไฟล์แนบครับ

[ซำเหมา]

ผมลองแยกข้อความออกมา แล้วพิสูจน์เงื่อนไข ลองดูตามไฟล์แนบครับ

อ่านแล้วยังงง แต่เดี๋ยวไปดูอีกที ทำไมมันแยกเป็นหลาย คอลัมน์ แล้วเราต้องใส่สูตรทั้งหมด
5คอลัมฯเลยใช่ไม๊ครับ

[bank9597]

ผมแยกออกมาให้เห็นว่า ชื่อ กับนามสกุลนั้น ซ้ำกันหรือไม่ โดยที่คอลัมน์ G เป็นตัวพิสูจน์ครับ หากได้ค่าเป็น True คือซ้ำ หากเป็น False คือไม่ซ้ำ

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

[ซำเหมา]

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

ที่เซลล์ B2 คีย์

=Countif(A$2:A2,A2)>1

Enter > Copy ลงด้านล่าง แล้วกรองเอาเฉพาะค่าที่เป็น True

การเขียนให้ List ค่าออกมาเลยก็ทำได้แต่สูตรจะซับซ้อนและเข้าใจยาก

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

อาจารย์ครับ ผมลองป้อนสูตรที่ไฟล์ ลองทำตามB1.xlsx และ ลองทำตามB2.xlsx ผลคือนายแดง สกุลแดง ยังมีซ้ำ ผมผิดตรงจุดไหนครับ แบบว่าผมอาจจะเข้าใจยากสักนิดนะครับ ตอนเรียน ภาษาเบสิค เกือบตกนะครับ นานแล้ว

[ซำเหมา]

ผมแยกออกมาให้เห็นว่า ชื่อ กับนามสกุลนั้น ซ้ำกันหรือไม่ โดยที่คอลัมน์ G เป็นตัวพิสูจน์ครับ หากได้ค่าเป็น True คือซ้ำ หากเป็น False คือไม่ซ้ำ

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

ขอบคุณครับ ได้อธิบายสิ่งที่อยากได้เพิ่มเข้ามาแล้วครับ แนบไฟล์ใหม่ครับ

[apirak2455]

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

B1=IF(COUNTIF(A$1:A1,A1)>1,A1&" (ซ้ำ)","")

[bank9597]

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

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

หาชื่อซ้ำ แสดงค่า True เฉพาะคือที่ซ้ำค่าแรก และ ค่าที่ไม่ซ้ำด้วย ที่เซลล์ B1 คีย์

=IF(COUNTIF($A$1:$A4,A4)=1,TRUE,"") คัดลอกลงมา

แสดงค่า True เฉพาะชื่อที่ซ้ำ ที่เซลล์ B1 คีย์ =IF(COUNTIF($A$1:$A$13,A8)>1,TRUE,"") คัดลอกลงมา

[ซำเหมา]

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

B1=IF(COUNTIF(A$1:A1,A1)>1,A1&" (ซ้ำ)","")

ได้ผลครับ ขอบคุณอาจารย์มากครับ ผมจะนำไปเป็นเครื่องมือทำงานครับ แต่จะศึกษาเพิ่มเติมไปเรื่อยๆครับ

[ซำเหมา]

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

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

หาชื่อซ้ำ แสดงค่า True เฉพาะคือที่ซ้ำค่าแรก และ ค่าที่ไม่ซ้ำด้วย ที่เซลล์ B1 คีย์

=IF(COUNTIF($A$1:$A4,A4)=1,TRUE,"") คัดลอกลงมา

แสดงค่า True เฉพาะชื่อที่ซ้ำ ที่เซลล์ B1 คีย์ =IF(COUNTIF($A$1:$A$13,A8)>1,TRUE,"") คัดลอกลงมา

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

[ซำเหมา]

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

ที่เซลล์ B2 คีย์

=Countif(A$2:A2,A2)>1

Enter > Copy ลงด้านล่าง แล้วกรองเอาเฉพาะค่าที่เป็น True

การเขียนให้ List ค่าออกมาเลยก็ทำได้แต่สูตรจะซับซ้อนและเข้าใจยาก

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

ขอโทษ ครับ ผมไม่เข้าใจเองครับ เพิ่งมาทบทวนใหม่ อาจารย์ให้กรองเอาค่า true แต่ผมดันทำตรงกันข้าม ตอนนี้ใช้งานได้แล้วครับ ดันโง่เอง อิอิ ขอบคุณอีกครั้งครับ

[ซำเหมา]

สูตร1
=Countif(A$2:A2,A2)>1
สูตร2
=IF(COUNTIF($A$1:$A4,A4)=1,TRUE,"")

คืองี้ครับ อยากให้ช่วยอธิบายสูตรหน่อย คืองงๆ

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

[bank9597]

ในหนังจะอธิบายพื้นฐานการใช้สูตรแต่ละสูตรเท่านั้นครับ ส่วนการพลิกแพลงนำไปใช้นั้น เป็นหน้าที่ของผู้ใช้เอง เพราะหากหนังสือสอนเรื่องการพลิกแพลงเข้าไปด้วยแล้ว เชื่อว่า 1 เล่มได้แค่เสี้ยวเดียวของความสามารถเอ็กเซลล์ครับ ดังนั้นผู้เขียนหนังสือย่อมรู้สูตรลึกกว่าที่เขียนอย่างแน่นอน เพียงแต่ต้องคัดกรองเนื้อหาที่จะเขียนลงไปให้ครบ และให้ตรงกับกลุ่มเป้าหมายที่จะอ่านเป็นสำคัญ สมมุติเอาความรู้ของอาจารย์คนควนที่รู้เกี่ยวกับโปรแกรมเอ็กเซลล์ คงจะตีออกมาเป็นเล่มแล้ว น่าจะเกิน 30 เล่ม เล่มล่ะ 1000 หน้า หรืออาจจะมากกว่านั้น เป็นห้องสมุดเลยก้ได้ครับ

=Countif(A$2:A2,A2)>1

สูตรนี้หมายความว่า ให้นับค่าใน A2 ที่ตรงกับค่าในช่วงเซลล์ A$2:A2 แต่มันมี >1 เพิ่มเข้ามา ผลที่ได้ก็จะเป็น True หรือ False
สรุป : ค่าใน A2 ที่ตรงกับค่าในช่วงเซลล์ A$2:A2 มีค่ามากกว่า 1 ค่า จริงหรือไม่ (A$2 <== ลักษณะเซลล์ั้แบบนี้คือมีการตรึงไว้ ไม่ให้เคลื่อนที่ใรกรณีที่มีการลากสูลงลงมา)

=IF(COUNTIF($A$1:$A4,A4)=1,TRUE,"")

สูตรนี้ มี IF เข้ามากำหนดเงื่อนไข หมายความว่า ให้นับค่าใน A4 ที่ตรงกับค่าในช่วงเซลล์ A$1:A4 ถ้า นับได้ 1 ค่า ให้แสดงเป็นค่า True ถ้าไม่อย่างนั้นก็ให้แสดงเป็นค่าว่าง ("")
สรุป : ถ้านับค่าใน A4 เทียบกับในช่วงเซลล์ A$1:A$4 แล้วเท่ากับ 1 ก็ให้แสดงค่า "True" ถ้าไม่อย่างนั้นก็แสดง "" (ค่าว่าง)

แสดงค่า True เฉพาะชื่อที่ซ้ำ ที่เซลล์ B1 คีย์ =IF(COUNTIF($A$1:$A$13,A8)>1,TRUE,"") คัดลอกลงมา

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




หวังว่าคงเข้าใจน่ะครับ

[ซำเหมา]

ในหนังจะอธิบายพื้นฐานการใช้สูตรแต่ละสูตรเท่านั้นครับ ส่วนการพลิกแพลงนำไปใช้นั้น เป็นหน้าที่ของผู้ใช้เอง เพราะหากหนังสือสอนเรื่องการพลิกแพลงเข้าไปด้วยแล้ว เชื่อว่า 1 เล่มได้แค่เสี้ยวเดียวของความสามารถเอ็กเซลล์ครับ ดังนั้นผู้เขียนหนังสือย่อมรู้สูตรลึกกว่าที่เขียนอย่างแน่นอน เพียงแต่ต้องคัดกรองเนื้อหาที่จะเขียนลงไปให้ครบ และให้ตรงกับกลุ่มเป้าหมายที่จะอ่านเป็นสำคัญ สมมุติเอาความรู้ของอาจารย์คนควนที่รู้เกี่ยวกับโปรแกรมเอ็กเซลล์ คงจะตีออกมาเป็นเล่มแล้ว น่าจะเกิน 30 เล่ม เล่มล่ะ 1000 หน้า หรืออาจจะมากกว่านั้น เป็นห้องสมุดเลยก้ได้ครับ

=Countif(A$2:A2,A2)>1

สูตรนี้หมายความว่า ให้นับค่าใน A2 ที่ตรงกับค่าในช่วงเซลล์ A$2:A2 แต่มันมี >1 เพิ่มเข้ามา ผลที่ได้ก็จะเป็น True หรือ False
สรุป : ค่าใน A2 ที่ตรงกับค่าในช่วงเซลล์ A$2:A2 มีค่ามากกว่า 1 ค่า จริงหรือไม่ (A$2 <== ลักษณะเซลล์ั้แบบนี้คือมีการตรึงไว้ ไม่ให้เคลื่อนที่ใรกรณีที่มีการลากสูลงลงมา)

=IF(COUNTIF($A$1:$A4,A4)=1,TRUE,"")

สูตรนี้ มี IF เข้ามากำหนดเงื่อนไข หมายความว่า ให้นับค่าใน A4 ที่ตรงกับค่าในช่วงเซลล์ A$1:A4 ถ้า นับได้ 1 ค่า ให้แสดงเป็นค่า True ถ้าไม่อย่างนั้นก็ให้แสดงเป็นค่าว่าง ("")
สรุป : ถ้านับค่าใน A4 เทียบกับในช่วงเซลล์ A$1:A$4 แล้วเท่ากับ 1 ก็ให้แสดงค่า "True" ถ้าไม่อย่างนั้นก็แสดง "" (ค่าว่าง)

แสดงค่า True เฉพาะชื่อที่ซ้ำ ที่เซลล์ B1 คีย์ =IF(COUNTIF($A$1:$A$13,A8)>1,TRUE,"") คัดลอกลงมา

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




หวังว่าคงเข้าใจน่ะครับ

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

[ซำเหมา]

จากสูตร
=Countif(A$2:A2,A2)>1 และสูตร =IF(COUNTIF($A$1:$A4,A4)=1,TRUE,"")

สูตรแรก ให้ตรวจที่เซลล์ A2:A2 พอเข้าใจ
แต่ทำไม
สูตรที่สองกลับ อ้างอิง เซลล์ A1:A4 ถ้าเราอ้างอิง เซลล์ A2:A2 เหมือนกันจะได้ไม๊ครับ ลองทำเล่นมันไม่ได้
เลยอยากทราบว่าหลักการคิดคิดได้ยังไงครับ

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