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

สถานการณ์ของเราคือ…

สมมติว่าเรามีข้อมูลอยู่ตารางหนึ่ง เป็นตารางการขายของในช่วงเดือนที่ผ่านมา…
ลักษณะข้อมูลคือ 1 บรรทัดเป็น 1 order ซึ่งแต่ละ order ก็จะมี Sales นักขายกำกับอยู่ว่าชื่ออะไร

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

สมมติข้อมูลเป็นแบบนี้ (มีแค่ 10 บรรทัดพอ) => distinct-count

ถ้าเราเจอปัญหาแบบนี้ จะแก้ไขได้ยังไงมาดูกันครับ!

วิธีที่ 1 : ใช้ PivotTable

วิธีนี้ง่ายสุดๆ นั่นคือให้สร้าง PivotTable ลากสิ่งที่เราต้องการจะนับแบบไม่ซ้ำไปที่ Row Label แค่นี้มันก็จะ List item แบบไม่ซ้ำกันมาให้แล้วครับ ซึ่งถ้าจะนับ จะลากเม้าส์แล้วดูที่ Status Bar หรืออาจใช้ COUNTA มาช่วยอีกทีก็ได้ครับ ซึ่งจะได้ 4 คนครับ

แต่วิธีนี้ก็มีข้อเสียคือ ต้องคอย Refresh Pivot หากข้อมูล Source Data เปลี่ยนนั่นเองครับ

จะลาก item มานับจำนวนว่าแต่ละ sales มี order กี่ครั้งด้วยก็ได้

วิธีที่ 2 : ใช้ Remove duplicates

วิธีนี้ค่อนข้างลูกทุ่งครับ ให้ Copy คอลัมน์ที่ต้องการออกมา แล้วไปที่เครื่องมือ [Data] –> Data Tools –> Remove Duplicates จากนั้น ok เป็นอันจบ

วิธีที่ 3 : ใช้ Advanced Filter ก็ได้

วิธีนี้ง่ายเหมือนกัน คือ ไปที่ [Data] –> Sort & Filter –> Advanced แล้ว ต้องติ๊ก Unique records only แล้วเลือก copy to โดยใส่ชื่อ Field ที่ต้องการจะ list แบบไม่ซ้ำ

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

วิธีที่ 4 : ถ้ายอมให้ Sort ได้ใช้ IF ก็ได้นะ

วิธีนี้ค่อนข้างง่ายครับ แต่มีข้อจำกัดคือ คุณต้องเรียงคอลัมน์ที่คุณจะนับซะก่อนครับ

พอเรียงแล้ว เราก็แค่เช็คว่า “ถ้าช่องที่อยู่ติดกันข้างบนมันซ้ำกันกับบรรทัดตัวเอง ก็ไม่ต้องนับ”
พอนึกถึงคำว่า ถ้า เราก็ใช้ IF มาเช็ค เช่นดังนี้ครับ

วิธีที่ 5 : ถ้าไม่ให้ Sort ก็ใช้ COUNTIF ซิ

ถ้าข้อมูลของเราไม่สามารถ Sort ได้ เช่น ถ้า Sort ปุ๊ป VLOOKUP พัง หรือ Sort แล้วทำให้กรอกข้อมูลลำบาก เราก็ต้องใช้วิธีอื่น เช่น COUNTIF ครับ

ซึ่ง COUNTIF จะเป็นการนับว่าใน range มีคำที่เราสนใจ (ตั้งว่าเป็น criteria) อยู่กี่ตัว?

หากเราลองเขียนดูจะได้ดังนี้

มันก็นับออกมาจริงๆ นั่นแหละว่าแต่ละบรรทัดมีตัวซ้ำกันกี่ตัว…
เช่น
สมเทพ แต่ละบรรทัดจะนับได้ 4 ตัว ซึ่งเลข 4 นี้ก็จะโผล่ขึ้นมา 4 รอบ ทั้งๆ ที่จริงๆ ต้องนับแค่ 1 เอง
สมหญิง แต่ละบรรทัดจะนับได้ 2 ตัว ซึ่งเลข 2 นี้ก็จะโผล่ขึ้นมา 2 รอบ ทั้งๆ ที่จริงๆ ต้องนับแค่ 1 เอง

แล้วเราจะกำจัดตัวที่เกินยังไงดี?

ถ้า 4 + 4 + 4 + 4 เราต้องทำให้เป็น แค่ 1… แปลว่าแต่ละอันเราต้องหารด้วย 4 ถึง 4 ครั้งนั่นเอง ( 4×4 หรือ 4^2)

= E2/(E2^2) = 1/E2 หรือ = 1/COUNTIF($C$2:$C$11,C2) นั่นเองครับ

ซึ่งพอ SUM รวมกันก็จะได้ 4 อยู่ดี!

แต่จะเห็นว่าวิธีนี้เอาไว้หาจำนวนนับว่ามีกี่ตัวที่ไม่ซ้ำ แต่ไม่สามารถคัดเลือกมาโชว์มาอย่างละบรรทัดได้ (ไม่ได้ flag 1, 0 แบบการใช้ IF ข้างบน)

วิธีที่ 6 : ใช้ COUNTIF แบบ Array Formula

วิธีนี้คล้ายๆ กับวิธีข้างบนแหละครับ ถ้าสังเกตุคือ วิธีข้างบน = 1/COUNTIF(xxx) แล้วเอาผลลัพธ์มาบวกกัน

แต่แทนที่เราจะให้ผลลัพธ์มันแสดงแยกช่องกัน แล้วค่อยเอามาบวกทีหลัง คราวนี้เราจะใช้การเขียนสูตรแบบ Array เพื่อให้ทุกอย่างอยู่ในช่องเดียว เช่น =1/COUNTIF(data_range,data_range)

=1/COUNTIF(C2:C11,C2:C11)
ไอ้ตรงสีแดงเนี่ย ปกติต้องการแค่ค่าเดียว แต่นี่เราใส่เป็น Range เลยเพื่อให้ได้ค่าแบบ Array

ถ้าลองลากแล้วกด F9 จะเห็นแบบนี้ครับ
=1/{4;4;2;2;4;2;4;2;2;2}
={0.25;0.25;0.5;0.5;0.25;0.5;0.25;0.5;0.5;0.5}

นั่นแสดงว่าถ้าเรารวบตอนจบด้วย SUM ก็จะได้ 4 เช่นกัน นั่นคือ =SUM(1/COUNTIF(C2:C11,C2:C11)) แต่เราต้องกด Ctrl+Shift+Enter ด้วยเพราะเป็นการเขียนสูตรแบบ Array Formula

แต่ถ้าเราเปลี่ยน SUM เป็น SUMPRODUCT เราจะกด Enter ได้ตามปกติ เพราะ SUMPRODUCT รองรับการเขียนแบบ Array ในตัวอยู่แล้ว

ถ้างั้นผมก็สามารถเขียนสูตรแบบ Array ได้ว่า  =SUMPRODUCT(1/COUNTIF(data_range,data_range)) หรือ =SUMPRODUCT(1/COUNTIF(C2:C11,C2:C11)) ก็จะได้ 4 เช่นกันครับ

แต่จะเห็นว่าวิธีนี้เอาไว้หาจำนวนนับว่ามีกี่ตัวที่ไม่ซ้ำ แต่ไม่สามารถคัดเลือกมาโชว์มาอย่างละบรรทัดได้ (ไม่ได้ flag 1, 0 แบบการใช้ IF ข้างบน)

วิธีที่ 7 : ใช้ COUNTIF แต่ Flag 1, 0 ทำไง?

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

ทีนี้ก็ใส่เงื่อนไขเพิ่ม ว่าถ้านับแล้วมากกว่า 1 ก็ให้เป็น 0 ไปซะ เพราะแสดงว่าซ้้ำแล้ว

วิธีที่ 8 : ใช้ฟังก์ชั่น FREQUENCY

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

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

=FREQUENCY(data_array,bins_array)
=FREQUENCY(ช่วงข้อมูล,ช่วงค่าขอบบน)

• ช่วงข้อมูลผมคือ A2:A9 อันนี้ไม่มีอะไรแปลก
• ส่วนค่าขอบบนของกลุ่มเป็น C2:C5 ซึ่งมีค่า 1, 3, 5, 10 มีความหมายดังนี้
  • กลุ่มแรก <=1
  • 1<กลุ่มสอง<=3
  • 3<กลุ่มสาม<=5
  • 5<กลุ่มสี่<=10
  • กลุ่มสุดท้าย >10 (ถ้าต้องการกลุ่มนี้ต้องลากเผื่อไปอีก 1 ช่อง)

เนื่องจาก FREQUENCY เป็นฟังก์ชั่นประเภท Array ดังนั้นวิธีเขียนต้องลากช่วง Range ที่จะแสดงผลลัพธ์ก่อน แล้วค่อยเขียนสูตร จากนั้นค่อยกด Ctrl+Shift+Enter

ซึ่งถ้าเราใส่ช่วงค่าขอบบนซ้ำกัน มันจะแสดงแค่อันแรกอันเดียว อันอื่นจะเป็น 0 หมด

ทีนี้ถ้าเราลองให้ bin_array เป็นค่า data_array ไปเลยล่ะ จะเกิดอะไรขึ้นมาดูกัน?

ทีนี้แปลว่าเราเอาความสามารถนี้มาประยุกต์ใช้ได้ หากใช้ IF เช็คว่า FREQUENCY มันนับ >0 แปลว่าเป็นตัวที่ไม่ซ้ำนั่นเอง เราจะนับเป็น 1 แต่ถ้านับได้ 0 เราก็แสดงเป็น 0 ไป

ซึ่งเราจะเขียน IF หรือ จะใส่ให้เป็น TRUE/FALSE แล้วแปลงเป็น 1,0 ด้วยการใส่ — ครอบไปก็ได้

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

แต่ถ้าข้อมูลที่ต้องการนับเป็น Text มันจะต้องพลิกแพลงมากหน่อยครับ ( ค่อนข้าง Advanced มาก)

Concept ก็คือ เราต้องแปลง Text ให้กลายเป็นเลขซะก่อน โดยใช้ MATCH แบบ Exact Match มาช่วย
ซึ่งการหาตำแหน่งแบบ Exact match จะได้ตำแหน่งผลลัพธ์แรกสุดมาเท่านั้น เช่น

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

วิธีที่ 9 : ใช้ Measure ในเครื่องมือ PowerPivot

วิธีนี้เป็นการใช้เครื่องมือ PowerPivot ที่สามารถใช้ได้ตั้งแต่ Excel 2010 ขึ้นไป แต่หลายคนอาจยังไม่เคยลองใช้ แต่จริงๆ แล้วมันทรงพลังมากๆ ครับ เพราะเราสามารถใส่สูตรคำนวณซับซ้อนลงไป (เรียกว่า Measure) แล้วหมุนด้วยเครื่องมือคล้ายๆ PivotTable ได้เลย พูดง่ายๆ คือ แทนที่จะสรุปข้อมูลด้วยฟังก์ชั่นพื้นฐานอย่าง SUM COUNT AVERAGE ที่ทำใน PivotTable ถ้าคุณเปลี่ยนไปใช้ PowerPivot คุณจะสร้างสูตรซับซ้อนแค่ไหนมารองรับก็ได้!! (แต่อาจต้องเรียนรู้ฟังก์ชั่นของ PowerPivot เพิ่มนิดหน่อย)

แต่ในเคสนี้ ไม่ต้องใช้สูตรอะไรยากมากมาย

ขั้นแรก ให้สร้าง Measure โดยใส่สูตรที่ต้องการก่อน

ถ้าเป็น PowerPivot version ใหม่ๆ หน่อย ก็จะมีฟังก์ชั่น DISTINCTCOUNT ให้เลย สามารถเขียนว่า 
=DISTINCTCOUNT(Table1[Sales]) เท่านี้ถือว่าจบเลยครับ!!

แล้วมันก็จะกลายเป็น Field List ตัวใหม่ ให้ลากไปอยู่ใน Value ของ PowerPivot ได้เลย ซึ่งมันนับไม่ซ้ำจริงๆ เอาไปประกอบกับ FieldList อื่นๆ อย่างวันที่ได้อีก เจ๋งสุดๆ

วิธีที่ 10 : คุณล่ะว่าไง?

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