---
title: วิธีหาจำนวน และตำแหน่งที่เกิดค่าที่เราต้องการแบบต่อเนื่องกันมากที่สุด (Consecutive Value)
url: https://www.thepexcel.com/consecutive-value/
type: post
date: 2021-02-27
updated: 2025-12-22
author: Sira Ekabut
categories: [Excel Advanced Formula]
tags: [CONCAT, IF, SEARCH, ROW, max, column, textjoin, array, FREQUENCY, rept]
---

# วิธีหาจำนวน และตำแหน่งที่เกิดค่าที่เราต้องการแบบต่อเนื่องกันมากที่สุด (Consecutive Value)

หลายๆ ครั้งเรามักมีความต้องการอยากจะหาว่าค่าที่เราสนใจนั้น เกิดขึ้นแบบต่อเนื่องกัน (Consecutive Value) มากที่สุดกี่ครั้ง? เกิดขึ้นตรงไหน?   
  
เช่น มีวันหยุดต่อเนื่องกันมากสุดกี่วัน หรือ เกิดยอดขายเกินกว่า xxx ต่อเนื่องกันกี่เดือน หรือทำงานสำเร็จต่อเนื่องกันสูงสุดกี่ครั้ง เป็นต้น ซึ่งการหาจำนวน และตำแหน่ง ที่มี **“ค่าตามที่เราต้องการเกิดขึ้นต่อเนื่องมากสุด”** นั้นไม่ใช่เรื่องง่ายๆ ที่จะคิดเองได้

 

ตัวผมเองก็เลยขอนำเสนอไอเดียที่ไปเรียนรู้มา[จากเว็บต่างประเทศ](https://www.extendoffice.com/documents/excel/4627-excel-count-consecutive-negative-number.html) แล้วนำมาคิดสูตรเพิ่มเติม เรียบเรียงใหม่ให้เข้าใจง่ายขึ้นเพื่อมาแชร์ให้กับเพื่อนๆ ได้นำไปใช้กันง่ายๆ ครับ

 

สมมติเรามีข้อมูลอยู่แบบนี้ (ซึ่งจะเป็น Range ธรรมดา หรือจะเป็น Table ก็ได้) แล้วเราอยากจะหาว่า x เกิดขึ้นต่อเนื่องกันมากที่สุดกี่ครั้ง และเกิดขึ้นตรงไหน? เราจะหาคำตอบได้ยังไง มาดูกันครับ

 ![1](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-002-1.png) 

## หาจำนวนที่เกิดต่อเนื่องมากที่สุด

 

### วิธีใช้สูตรปกติ

 

เราก็สามารถสร้างข้อมูลในคอัมน์ข้างๆ ให้นับ x ไปเรื่อยๆ โดยเงื่อนไข IF ว่าถ้าค่าในช่องด้านซ้ายมือตรงกับเงื่อนไขที่เราต้องการ (เช่น ตรงกับค่า x)ก็ให้นับเพิ่ม +1 ไป ถ้าไม่ใช่ให้ reset เป็น 0 ดังนี้

 

```
=IF(A3="x",B2+1,0)
```

 ![2](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-012.png) 

จากนั้นก็หาค่า Max จบเลย

 ![3](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-013.png) 

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

 

### วิธีที่ใช้ Array Formula

 

ก่อนอื่น ให้เราเขียนเงื่อนไข IF ว่าถ้าค่าใน Range ตรงกับเงื่อนไขที่เราต้องการ (เช่น ตรงกับค่า x) ให้แสดง Row ออกมา นอกนั้นปล่อยเป็น FALSE ไป (ถ้าข้อมูลเป็นแนวนอน ก็ให้แสดง Column ออกมาแทนที่จะใช้ Row นะ)

 

```
=IF(A3:A13="x",ROW(A3:A13))
```

 

Excel version เก่าอย่าลืมกดปุ่ม Ctrl+Shift+Enter ด้วยนะ เพราะเป็นสูตรแบบ Array

 ![4](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-003.png) 

จากนั้นเราทำแบบเดียวกันอีกชุดนึง แต่คราวนี้เช็คว่าถ้าไม่ตรงกับเงื่อนไขที่เราต้องการ (เช่น ไม่ใช่ x) ให้แสดง Row ออกมา นอกนั้นปล่อยเป็น FALSE ไป เช่นเดิม

 ![5](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-004-1024x683.png) 

จากนั้นใช้ฟังก์ชัน FREQUENCY ครอบทั้งสอง Range ที่สร้างขึ้นมาดังนี้

 

จะได้ผลลัพธ์ว่า มีตัว <=5 อยู่ 2 อัน, <=6 อยู่ 0 อัน, <=10 อยู่ 3 อัน, <=11 อยู่ 1 อัน และ >11 อยู่ 2 อัน

 

ใครไม่เข้าใจ[การทำงานของ FREQUENNCY](https://www.thepexcel.com/frequency-function-histogram/)ไปอ่านได้ที่นี่

 ![6](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-005-1024x625.png) 

ที่นี้เราก็จะรู้แล้วว่ามันต่อเนื่องกันมากสุดเท่าไหร่ โดยใช้ MAX มาครอบผลลัพธ์จาก FREQUENCY อีกที ดังนี้

 ![7](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-006-1024x607.png) 

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

 

```
=MAX(FREQUENCY(IF(A3:A13="x",ROW(A3:A13)),IF(A3:A13<>"x",ROW(A3:A13))))
```

 ![8](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-007.png) 

## หาตำแหน่งที่เกิดต่อเนื่องมากที่สุด

 

เพื่อให้สะดวกต่อการระบุตำแหน่ง เราจะแปลงค่าให้เป็น 1,0 ให้หมดก่อน **จะได้มั่นใจว่า 1 ช่องคือ 1 ตัวอักษร** ดังนี้

 ![9](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-008.png) 

### ถ้ามี CONCAT / TEXTJOIN

 

จากนั้นเราแค่ใช้ CONCAT / TEXTJOIN เพื่อรวม 1,0 เข้าด้วยกันเป็นข้อความเดียว แบบนี้

 

```
=CONCAT((A3:A12="x")*1)
```

 ![10](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-011.png) 

จากนั้นใช้ SEARCH หรือ FIND เพื่อหาตำแหน่ง “111” ในข้อความนั้นก็จะได้คำตอบแล้วครับ แบบนี้

 

```
=SEARCH(REPT("1",C3),CONCAT((A3:A12="x")*1))
```

 ![11](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-009-1024x676.png) 

### ถ้าไม่มี CONCAT / TEXTJOIN

 

ถ้าเอาแบบเข้าใจง่ายสุด ก็อาจจะใช้ & เชื่อมต่อข้อความเข้าด้วยกันก่อน แล้วค่อยใช้ SEARCH ค้นหา “111” ในนั้นอีกทีก็ได้ครับ

 ![12](https://www.thepexcel.com/wp-content/uploads/2021/02/consecutive-010-1024x642.png) 

## สรุป

 

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

---

_Source: [https://www.thepexcel.com/consecutive-value/](https://www.thepexcel.com/consecutive-value/)_