ต้องการดึงรายการที่ซ้ำและไม่ซ้ำออกจากรายการเดิมครับ

[yodpao.b]

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

11.JPG

ดังรูปด้านบน
ต้องการดึงรายการที่ซ้ำและไม่ซ้ำออกจากรายการเดิมมาไว้ในคอลัม D
รายการเดิมอยู่คอลัม C
คำตอบที่ถูกต้องอยู่ คอลัม G

จะใช้สูตรฟังชั่นในExcel อะไรดีครับ

[DhitiBank]

ลองแบบนี้ครับ

ที่ F1 คีย์
=SUMPRODUCT(1/COUNTIF($C$4:$C$65,$C$4:$C$65))
Enter

ที่ G4 คีย์
=IF(ROWS(G$4:G4)>$F$1,"",INDEX($C$4:$C$65,SMALL(IF(MATCH($C$4:$C$65,$C$4:$C$65,0)=ROW($C$4:$C$65)-ROW($C$4)+1,ROW($C$4:$C$65)-ROW($C$4)+1),ROWS(G$4:G4))))

กด Ctrl+Shift ค้างไว้ แล้วกด Enter --> คัดลอกลงล่างครับ

เวลาเอาไปปรับใช้ก็ปรับตรงช่วงสีแดงให้เข้ากับช่วงข้อมูลจริงครับ

[yodpao.b]

ขอบคุณมากครับใช้ได้แล้วครับ
ขอกลับบ้านก่อนครับ
ไว้พรุ่งนี้ขอถามต่อนะครับ

[yodpao.b]

12.JPG

จากภาพด้านบน
ช้อมูลในกล่องช้อความได้ ลิงค์กับคอลัม G
นั้นเป็นตามต้องการ

13.JPG

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

[DhitiBank]

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

การสร้างรายการไม่ซ้ำด้วย VBA สามารถทำได้หลายวิธี อาจลองดูตัวอย่างเพิ่มเติมเช่นจากด้านล่างครับ

Some way to create unique list

[yodpao.b]

VBA เคยทำมาแล้วครับ
ครั้งนี้ทางหัวหน้าต้องการให้คนทั่วไปใช้ได้ครับ ตอนไปออกงานคงมี่คนขอไฟล์ไว้ใช้
จึงไม่อยากให้ใช้ VBA ครับเพราะต้องไปปลดความปลอดภัย คนทั่วไปคงไม่เข้าใจ
แต่ไม่เป็นไรครับเดียวผมใช้วิธีง่าย ๆไปก่อน

[snasui]

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

ไม่มีสูตรที่สามารถเขียนเข้าไปโดยตรงใน ComboBox แล้วให้แสดง Unique List ครับ

[yodpao.b]

ขอบคุณครับ

[yodpao.b]

ลองแบบนี้ครับ

ที่ F1 คีย์
=SUMPRODUCT(1/COUNTIF($C$4:$C$65,$C$4:$C$65))
Enter

ที่ G4 คีย์
=IF(ROWS(G$4:G4)>$F$1,"",INDEX($C$4:$C$65,SMALL(IF(MATCH($C$4:$C$65,$C$4:$C$65,0)=ROW($C$4:$C$65)-ROW($C$4)+1,ROW($C$4:$C$65)-ROW($C$4)+1),ROWS(G$4:G4))))

กด Ctrl+Shift ค้างไว้ แล้วกด Enter --> คัดลอกลงล่างครับ

เวลาเอาไปปรับใช้ก็ปรับตรงช่วงสีแดงให้เข้ากับช่วงข้อมูลจริงครับ

จากสูตรด้านบน ทำดูแล้วมีปัญหาแปลกๆครับ
คือ พอจัดการกับข้อมูลทางซ้าย ข้อมูลไม่ถูกต้อง แต่พอทำทางซ้ายมือกลับถูก
ทางขวานับได้ 22 แต่แสดง 21 ชื่อ (ไม่ถูกต้อง)
ทางซ้ายนับได้ 23 แสดง 23 ชื่อ (ถูกต้อง)
ผมจึงไม่เข้าจจครับ

แก้ไขข้อมูลทางขวาได้โดยสั่งให้เรียงลำดับ ค่าออกมาถูกต้องคือ ทางขวานับได้ 22 แสดง 22 ชื่อ

[snasui]

ปรับสูตร

=SUMPRODUCT(1/COUNTIF($C$4:$C$65,$C$4:$C$65))

เป็นด้านล่างแล้วทดสอบดูใหม่ครับ

=Round(SUMPRODUCT(1/COUNTIF($C$4:$C$65,$C$4:$C$65)),0)

[yodpao.b]

ผมได้แก้ไขทั้งสองด้านตามอาจารย์

ผลถูกต้องครับ
ขอบคุณครับ

[DhitiBank]

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

[OonjunG]

ขอความรู้ครับดังนี้ครับ

จากสูตร =SUMPRODUCT(1/COUNTIF($C$4:$C$65,$C$4:$C$65)) จะมีค่าเท่ากับ 6 อย่างไรก็ตาม ผมได้ลองใช้ excel คำนวนทีละขั้นตอนดังนี้
step 1 : =COUNTIF($C$4:$C$65,$C$4:$C$65) จะมีค่าเท่ากับ 0
step 2 : =1/COUNTIF($C$4:$C$65,$C$4:$C$65) = 1/0 = #DIV/0!
step 3 : =SUMPRODUCT(1/COUNTIF($C$4:$C$65,$C$4:$C$65))
=SUMPRODUCT(#DIV/0!)
= 6
ผลเลยสงสัยว่า =SUMPRODUCT(#DIV/0!) ได้ผลเป็น 6 ได้ยังไงครับ ขอบคุณครับ

[DhitiBank]

เราสามารถแกะสูตรแบบที่ละเอียดขึ้นได้ครับ โดยอาจจะ

1. ใช้วิธี Evaluate Formula
เลือกเซลล์ที่มีสูตร --> เมนู Formula --> Evaluate Formula --> กด Evaluate แล้วสังเกตในช่องว่างๆ ครับ

1.png

2.png

2. ใช้ปุ่ม F9 และ ESC
เลือกเซลล์ที่มีสูตร --> ไปที่ formula bar --> เอาเม้าส์ลากคลุมช่วงสูตรที่ต้องการดูผลการคำนวณ (คลุมให้ครบ ทั้งชื่อสูตร วงเล็บเปิด/ปิด) --> F9 --> หากดูเสร็จแล้วกด ESC

[DhitiBank]

9.jpg

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

[OonjunG]

ขอบคุณครับ