frequency histogram

สร้างตารางแจกแจงความถี่ด้วย FREQUENCY ฟังก์ชันสุดล้ำใน Excel

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

เวลาที่เรามีข้อมูลแล้วอยากจะทำตารางแจกแจงความถี่ จริงๆ แล้วก็มีวิธีที่ทำได้ก็มีหลายแบบ เช่น ใช้ Pivot Table Group เอา แต่ก็มีข้อเสียคือแต่ละช่วง ( bin )ต้องมีขนาดเท่ากัน หากจะทำแบบ Bin ไม่เท่ากัน ก็อาจจะใช้ VLOOKUP Approximate Match มาช่วยจัดกลุ่มก่อนที่จะนับ ซึ่งก็ยุ่งยากอีก ดังนั้น Excel จึงมีฟังก์ชันที่เอาไว้จัดการเรื่องนี้โดยเฉพาะ นั่นก็คือ FREQUENCY นั่นเอง

ข้อมูลประกอบ

สมมติว่าเรามีข้อมูลความสูงตัวละครใน Dragon Ball ตามไฟล์นี้

เราสามารถใช้ฟังก์ชัน FREQUENCY มาช่วยได้ ซึ่งมีวิธีใช้ดังนี้

=FREQUENCY(data_array,bins_array)
  • data_array คือ ช่วงของข้อมูลที่เราจะเอามานับแจกแจงความถี่ (ไม่จำเป็นต้องเรียง)
  • bin_array คือ ช่วงการจัดความถี่ เป็นค่ามากสุด (ขอบบน) ของแต่ละช่วง โดยจะไม่นับ item ซ้ำเด็ดขาด (การเว้นระยะของ bin ไม่จำเป็นต้องเท่ากัน)

หากใช้ Excel 365 ที่รองรับ Dynamic Array เราสามารถใช้ฟังก์ชัน FREQUENCY นี้ได้ง่ายๆ ตามการเขียนสูตรปกติเลย มันจะงอกผลลัพธ์ออกมาให้มีขนาดสูงกว่า bin_array 1 ช่องโดยอัตโนมัติ (ผลลัพธ์มันล้ำไป 1 ช่อง นี่แหละถึงบอกว่าเป็นฟังก์ชันล้ำที่สุด)

สร้างตารางแจกแจงความถี่ด้วย FREQUENCY ฟังก์ชันสุดล้ำใน Excel 1

แต่ถ้าใช้ Excel versionเก่ากว่านั้น เราจะต้องลากคลุมพื้นที่ผลลัพธ์ให้สูงกว่า bin_array 1 ช่อง แล้วคอ่นเขียนสูตร FREQUENCY แล้วพอเขียนสูตรเสร็จต้องกดปุ่ม Ctrl+Shift+Enter ด้วย เพื่อบอก Excel ว่าจะเขียนสูตรแบบ Array Formula

ซึ่งพอมีตารางแจกแจงความถี่แล้ว เราก็สามารถสร้าง Histogram ได้ง่ายๆ ได้ด้วย Column Chart ธรรมดาๆ (แต่ปรับ Gap Width เป็น 0% ) แล้วล่ะครับ (แม้ว่าใน Excel version ใหม่ๆ จะสร้าง Histogram ได้จาก Data ดิบทันทีเลยก็เถอะ… )

สร้างตารางแจกแจงความถี่ด้วย FREQUENCY ฟังก์ชันสุดล้ำใน Excel 2

ความพิเศษของ FREQUENCY

ความสามารถของ FREQUENCY ไม่ใช่แค่นั้น เพราะมันมีพฤติกรรมพิเศษหลายอันที่ควรรู้ คือ

bin_array จะเรียงยังไงก็ได้ มันจะจัดการให้ได้ผลลัพธ์ที่ถูกต้องเอง โดยคิดเหมือนกับการเรียงจากน้อยไปมากเสมอ

สร้างตารางแจกแจงความถี่ด้วย FREQUENCY ฟังก์ชันสุดล้ำใน Excel 3

หาก bin_array ซ้ำกัน มันจะไม่นับแล้ว ซึ่งจะได้ 0 ทันที (เพราะมันจะไม่นับซ้ำเด็ดขาด)

สังเกตว่า การที่เลข 150 หายไป ส่งผลให้ bin ช่วงถัดไปก็อาจจะมีขนาดเปลี่ยนไปได้นะครับ (เดิม >150 – 160 กลายเป็น >140 – 160) แต่เป็นคนละประเด็นกับเลขซ้ำนะ

สร้างตารางแจกแจงความถี่ด้วย FREQUENCY ฟังก์ชันสุดล้ำใน Excel 4

ด้วยเหตุนี้เอง จึงมีการนำ FREQUENCY ไปประยุกต์ใช้อะไรพิสดารหลายอย่างเลย เช่น

นับจำนวนข้อมูลที่ไม่ซ้ำกัน

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

=FREQUENCY(B2:B51,B2:B51)
สร้างตารางแจกแจงความถี่ด้วย FREQUENCY ฟังก์ชันสุดล้ำใน Excel 5

ดังนั้นถ้าเราใส่เงื่อนไขให้สนใจเฉพาะตัวที่มากกว่า 0 ให้เป็น TRUE แล้วก็แปลงเป็นเลข 1 ซะด้วยการคูณ1 แล้วทำการ SUM ด้วย SUMPRODUCT หรือ SUM เราก็จะได้จำนวนข้อมูลที่ไม่ซ้ำกันนั่นเอง

=SUMPRODUCT((FREQUENCY(B2:B51,B2:B51)>0)*1)

ซึ่งใน Data จะนับได้ 41 ตัวนั่นเองครับ

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

Power BI Workshop 2024 ตุลาคมนี้
อบรม In-House Training

Feedback การใช้งาน AI Chatbot