หลายๆ ครั้งเรามักมีความต้องการอยากจะหาว่าค่าที่เราสนใจนั้น เกิดขึ้นแบบต่อเนื่องกัน (Consecutive Value) มากที่สุดกี่ครั้ง? เกิดขึ้นตรงไหน?
เช่น มีวันหยุดต่อเนื่องกันมากสุดกี่วัน หรือ เกิดยอดขายเกินกว่า xxx ต่อเนื่องกันกี่เดือน หรือทำงานสำเร็จต่อเนื่องกันสูงสุดกี่ครั้ง เป็นต้น ซึ่งการหาจำนวน และตำแหน่ง ที่มี “ค่าตามที่เราต้องการเกิดขึ้นต่อเนื่องมากสุด” นั้นไม่ใช่เรื่องง่ายๆ ที่จะคิดเองได้
ตัวผมเองก็เลยขอนำเสนอไอเดียที่ไปเรียนรู้มาจากเว็บต่างประเทศ แล้วนำมาคิดสูตรเพิ่มเติม เรียบเรียงใหม่ให้เข้าใจง่ายขึ้นเพื่อมาแชร์ให้กับเพื่อนๆ ได้นำไปใช้กันง่ายๆ ครับ
สมมติเรามีข้อมูลอยู่แบบนี้ (ซึ่งจะเป็น Range ธรรมดา หรือจะเป็น Table ก็ได้) แล้วเราอยากจะหาว่า x เกิดขึ้นต่อเนื่องกันมากที่สุดกี่ครั้ง และเกิดขึ้นตรงไหน? เราจะหาคำตอบได้ยังไง มาดูกันครับ
สารบัญ
หาจำนวนที่เกิดต่อเนื่องมากที่สุด
วิธีใช้สูตรปกติ
เราก็สามารถสร้างข้อมูลในคอัมน์ข้างๆ ให้นับ x ไปเรื่อยๆ โดยเงื่อนไข IF ว่าถ้าค่าในช่องด้านซ้ายมือตรงกับเงื่อนไขที่เราต้องการ (เช่น ตรงกับค่า x)ก็ให้นับเพิ่ม +1 ไป ถ้าไม่ใช่ให้ reset เป็น 0 ดังนี้
=IF(A3="x",B2+1,0)
จากนั้นก็หาค่า Max จบเลย
วิธีนี้เข้าใจง่ายมากๆ แต่มีข้อเสียคือเปลืองพื้นที่ อย่างไรก็ตาม ถ้าเราต้องการจะรวบทุกอย่างให้อยู่ในช่องเดียว อาจจะต้องใช้ Array Formula ดังนี้
วิธีที่ใช้ Array Formula
ก่อนอื่น ให้เราเขียนเงื่อนไข IF ว่าถ้าค่าใน Range ตรงกับเงื่อนไขที่เราต้องการ (เช่น ตรงกับค่า x) ให้แสดง Row ออกมา นอกนั้นปล่อยเป็น FALSE ไป (ถ้าข้อมูลเป็นแนวนอน ก็ให้แสดง Column ออกมาแทนที่จะใช้ Row นะ)
=IF(A3:A13="x",ROW(A3:A13))Excel version เก่าอย่าลืมกดปุ่ม Ctrl+Shift+Enter ด้วยนะ เพราะเป็นสูตรแบบ Array
จากนั้นเราทำแบบเดียวกันอีกชุดนึง แต่คราวนี้เช็คว่าถ้าไม่ตรงกับเงื่อนไขที่เราต้องการ (เช่น ไม่ใช่ x) ให้แสดง Row ออกมา นอกนั้นปล่อยเป็น FALSE ไป เช่นเดิม
จากนั้นใช้ฟังก์ชัน FREQUENCY ครอบทั้งสอง Range ที่สร้างขึ้นมาดังนี้
จะได้ผลลัพธ์ว่า มีตัว <=5 อยู่ 2 อัน, <=6 อยู่ 0 อัน, <=10 อยู่ 3 อัน, <=11 อยู่ 1 อัน และ >11 อยู่ 2 อัน
ใครไม่เข้าใจการทำงานของ FREQUENNCY ไปอ่านได้ที่นี่
ที่นี้เราก็จะรู้แล้วว่ามันต่อเนื่องกันมากสุดเท่าไหร่ โดยใช้ MAX มาครอบผลลัพธ์จาก FREQUENCY อีกที ดังนี้
ซึ่งถ้ารวบทุกสูตรเข้าด้วยกันเหลือช่องเดียวจะได้ดังนี้
=MAX(FREQUENCY(IF(A3:A13="x",ROW(A3:A13)),IF(A3:A13<>"x",ROW(A3:A13))))
หาตำแหน่งที่เกิดต่อเนื่องมากที่สุด
เพื่อให้สะดวกต่อการระบุตำแหน่ง เราจะแปลงค่าให้เป็น 1,0 ให้หมดก่อน จะได้มั่นใจว่า 1 ช่องคือ 1 ตัวอักษร ดังนี้
ถ้ามี CONCAT / TEXTJOIN
จากนั้นเราแค่ใช้ CONCAT / TEXTJOIN เพื่อรวม 1,0 เข้าด้วยกันเป็นข้อความเดียว แบบนี้
=CONCAT((A3:A12="x")*1)
จากนั้นใช้ SEARCH หรือ FIND เพื่อหาตำแหน่ง “111” ในข้อความนั้นก็จะได้คำตอบแล้วครับ แบบนี้
=SEARCH(REPT("1",C3),CONCAT((A3:A12="x")*1))
ถ้าไม่มี CONCAT / TEXTJOIN
ถ้าเอาแบบเข้าใจง่ายสุด ก็อาจจะใช้ & เชื่อมต่อข้อความเข้าด้วยกันก่อน แล้วค่อยใช้ SEARCH ค้นหา “111” ในนั้นอีกทีก็ได้ครับ
สรุป
หวังว่าบทความนี้จะช่วยให้เพื่อนๆ สามารถเขียนสูตรเพื่อคำนวณสิ่งที่ต้องการได้ ไม่ว่าจะเป็น หาว่ามีวันหยุดต่อเนื่องกันมากสุดกี่วัน หรือ เกิดยอดขายเกินกว่า xxx ต่อเนื่องกันกี่เดือน เป็นต้น
