เชี่ยวชาญ Excel: นับตัวอักษรหรือคำในสตริงและช่วงข้อมูลได้อย่างรวดเร็ว

ในบทช่วยสอนนี้ เราจะอภิปรายเกี่ยวกับการนับจำนวนครั้งที่ตัวอักษรหรือคำปรากฏในสตริงเฉพาะและช่วงของสตริงใน Excel บ่อยครั้งที่เราต้องคำนวณความถี่ของตัวอักษรในเซลล์หรือช่วงข้อมูลที่มีปริมาณมาก ดังนั้น เพื่อให้การนับจำนวนนี้ง่ายขึ้น เราจะอภิปรายสูตรที่ใช้งานง่ายบ้างในบทความนี้

ดาวน์โหลดสมุดงานฝึกฝน

คุณสามารถดาวน์โหลดสมุดงานฝึกฝนที่เราใช้เตรียมบทความนี้ได้

5 วิธีในการนับจำนวนครั้งที่ตัวอักษรปรากฏในสตริงใน Excel

1. หาจำนวนตัวอักษรทั้งหมดในสตริงใน Excel ด้วย SUMPRODUCT และ LEN

หากคุณต้องการทราบจำนวนตัวอักษรทั้งหมดในเซลล์ ฟังก์ชัน LEN ทำหน้าที่ได้ แต่เมื่อคุณต้องการทราบจำนวนตัวอักษรทั้งหมดในช่วง คุณสามารถรวม SUMPRODUCT และ LEN functions ได้ ตัวอย่างเช่น เรามีชุดข้อมูลชื่อหนังสือ และต้องการทราบจำนวนตัวอักษรทั้งหมดในช่วง เราจะใช้ขั้นตอนต่อไปนี้

ขั้นตอน:

• แรก พิมพ์สูตรด้านล่าง:

=SUMPRODUCT(LEN(B5:B9))

ที่นี่ ฟังก์ชัน LEN ส่งคืนจำนวนตัวอักษรในสตริงข้อความ และ ฟังก์ชัน SUMPRODUCT ส่งคืนผลรวมของผลคูณของช่วงที่สอดคล้องกัน

• และ นี่คือจำนวนตัวอักษรทั้งหมดในช่วงที่ระบุ

หมายเหตุ:

Excel LEN function นับตัวอักษรทั้งหมดในแต่ละเซลล์ รวมถึงตัวอักษร ตัวเลข ช่องว่าง สัญลักษณ์ และเครื่องหมายวรรคตอน ทุกอย่าง

2. รวม SUBSTITUTE และ LEN เพื่อรวมจำนวนครั้งของตัวอักษรเฉพาะในสตริงใน Excel (Case Sensitive)

บางครั้ง เราต้องการทราบจำนวนความถี่ของตัวอักษรในเซลล์ Excel ในสถานการณ์แบบนี้ เราสามารถใช้ฟังก์ชัน LEN และ SUBSTITUTE พร้อมกันได้ สิ่งสำคัญคือ ฟังก์ชัน SUBSTITUTE เป็น Case Sensitive ดังนั้น หากคุณค้นหาตัวอักษรตัวเล็ก คุณจะได้จำนวนเฉพาะตัวอักษรเหล่านั้น ตัวอย่างเช่น ในชุดข้อมูลชื่อหนังสือของเรา เราจะค้นหาจำนวนของตัวอักษร 'a' เท่านั้น ขั้นตอนที่เกี่ยวข้องคือ:

ขั้นตอน:

• แรก พิมพ์สูตรต่อไปนี้:

=LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))

การแยกวิเคราะห์สูตร:

➤ LEN(B5)

ที่นี่ ฟังก์ชัน LEN นับตัวอักษรใน เซลล์ B5

➤ SUBSTITUTE(B5,"a","")

ฟังก์ชัน SUBSTITUTE แทนที่ตัวอักษร 'a' ทั้งหมดด้วยช่องว่าง ("")

➤ LEN(SUBSTITUTE(B5,"a",""))

ตอนนี้ สูตร SUBSTITUTE ถูกครอบด้วยฟังก์ชัน LEN ที่ให้จำนวนตัวอักษรที่เหลือของสตริง (ไม่นับ 'a' ทั้งหมด)

➤ LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))

สุดท้าย สูตรนี้ลบความยาวสองครั้งที่คำนวณไว้ก่อนหน้านี้ และให้จำนวนรวมของตัวอักษรที่เราระบุ 'a'

• ในที่สุด นี่คือจำนวนของตัวอักษรที่คาดหวัง:

3. ใช้การรวมของ SUBSTITUTE และ LEN คำนวณจำนวนครั้งของตัวอักษรเฉพาะในสตริงใน Excel (Case Insensitive)

ตรงข้ามกับวิธีก่อนหน้า คุณอาจต้องนับตัวอักษรโดยไม่คำนึงถึง Case Sensitivity ในกรณีนั้น คุณสามารถเพิ่มฟังก์ชัน UPPER หรือ LOWER เข้าไปในสูตรก่อนหน้า ตอนนี้ เราจะค้นหาทั้ง 'A' และ 'a' ในชุดข้อมูลชื่อหนังสือ ขั้นตอนมีดังนี้:

ขั้นตอน:

• พิมพ์สูตรด้านล่างก่อน

=LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),"A",""))

ที่นี่ ฟังก์ชัน UPPER แปลงสตริงข้อความเป็นตัวพิมพ์ใหญ่ทั้งหมด ที่นี่ 'a' ทั้งหมดจะกลายเป็น 'A' และคุณจะได้จำนวนตามนั้น ส่วนที่เหลือของสูตรทำงานคล้ายกับตัวอย่างก่อนหน้า

• สุดท้าย คุณจะได้ผลลัพธ์ดังนี้ หากใส่สูตรถูกต้อง

4. คำนวณจำนวนครั้งที่ตัวอักษรเดี่ยวปรากฏในช่วงใน Excel

บางครั้ง คุณอาจต้องคำนวณจำนวนครั้งทั้งหมดของตัวอักษรเฉพาะในช่วงสตริง เช่น เราต้องการทราบความถี่ของตัวอักษร 'A' หรือ 'a' ในช่วงข้อมูล

ขั้นตอน:

• พิมพ์สูตรต่อไปนี้ก่อน

=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"a","")))

ที่นี่ ฟังก์ชัน SUM บวกตัวเลขทั้งหมดในช่วงของเซลล์ ส่วนที่เหลือของสูตรทำงานเหมือนเดิม และสุดท้ายส่งคืนจำนวนรวมของตัวอักษร 'a' ในทั้งช่วง

• ผลลัพธ์ คุณจะได้จำนวนรวมดังนี้:

5. นับจำนวนครั้งที่ตัวอักษร (ข้อความหรือ Substring) ปรากฏในสตริงในช่วง

คล้ายกับวิธีก่อนหน้า คุณอาจต้องการทราบความถี่ของข้อความเฉพาะในช่วงข้อมูล ในสถานการณ์เช่นนั้น คุณสามารถใช้การรวมของฟังก์ชัน ในวิธีนี้ เรารวม SUM, LEN, และ SUBSTITUTE functions ตัวอย่างเช่น เรามีช่วงข้อมูลที่มีชื่อสี และเราต้องการทราบความถี่ของสี 'Green' จากช่วง

ขั้นตอน:

• พิมพ์สูตรด้านล่างก่อน

=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"Green","")))/LEN("Green")

ในสูตรข้างต้น คุณต้องหารจำนวนตัวอักษรด้วยข้อความ/Substring ที่ระบุ มิฉะนั้น ตัวอักษรแต่ละตัวในข้อความจะถูกนับแยกกัน

• สุดท้าย นี่คือจำนวนรวมของ 'Green' ในช่วงที่ระบุ

หมายเหตุ:

สูตรที่กล่าวมาข้างต้นต้องป้อนเป็น Array Formula (สูตรอาเรย์) เพื่อป้อนสูตรเป็นอาเรย์ใน Excel สำหรับ Windows ให้กด CTRL+SHIFT+ENTER

สรุป

ในการอภิปรายข้างต้น ผมพยายามอธิบายวิธีที่ง่ายและเหมาะสมในการหาจำนวนตัวอักษรในสตริงหรือช่วงของสตริงใน Excel หวังว่าคุณจะพบว่าวิธีทั้งหมดมีประโยชน์ หากคุณมีข้อสงสัยเกี่ยวกับวิธีเหล่านี้ แจ้งให้ผมทราบได้

บทความที่เกี่ยวข้อง

• วิธีค้นหาตัวอักษรในสตริงใน Excel
• First Occurrence of a Value in a Range in Excel (3 Ways)
• วิธีใช้ FIND Function ใน Excel (7 ตัวอย่างที่เหมาะสม)
• Use LEN Function in Excel (Formula and VBA Code)