ใช้ไวลด์การ์ดในเกณฑ์

จนถึงตอนนี้ เกณฑ์ของคุณจับคู่ค่าที่ตรงกันทุกประการ แต่ข้อมูลจริงมักไม่เป็นระเบียบ คุณอาจต้องการค้นหารหัสผลิตภัณฑ์ทุกตัวที่ขึ้นต้นด้วย AB หรือชื่อตำแหน่งงานทุกชื่อที่มีคำว่า ผู้จัดการ

อักขระตัวแทนช่วยให้จับคู่ข้อความบางส่วนภายใน SUMIF, COUNTIF และ AVERAGEIF ได้ อักขระพิเศษสองตัวนี้ทำงานทั้งหมด: เครื่องหมายดอกจันและเครื่องหมายคำถาม

มีอยู่สองแบบพอดี:

• * เครื่องหมายดอกจันจับคู่อักขระจำนวนเท่าใดก็ได้ รวมถึงไม่มีอักขระเลย
• ? เครื่องหมายคำถามจับคู่อักขระหนึ่งตัวพอดี

ให้นำอักขระเหล่านี้ไปใส่ไว้ภายในข้อความเกณฑ์ที่อยู่ในเครื่องหมายอัญประกาศ ทุกสิ่งที่เรียนรู้เกี่ยวกับเครื่องหมายอัญประกาศและเครื่องหมายแอมเพอร์แซนด์ยังคงใช้ได้เช่นเดิม

ใส่เครื่องหมายดอกจันไว้ท้ายข้อความเพื่อหมายถึงส่วนลงท้ายใด ๆ หากต้องการนับรหัสทุกตัวใน A2:A10 ที่ขึ้นต้นด้วย AB ให้ใส่เครื่องหมายดอกจันต่อท้าย AB

วิธีนี้จะจับคู่ AB, AB1, ABXYZ และทุกสิ่งที่ขึ้นต้นด้วยตัวอักษรสองตัวนี้ เครื่องหมายดอกจันทำหน้าที่แทนสิ่งใดก็ตามที่อยู่ถัดจากนั้น

ใส่เครื่องหมายดอกจันไว้หน้าข้อความเพื่อจับคู่ส่วนเริ่มต้นใด ๆ หากต้องการหาผลรวมจำนวนเงินใน B2:B10 เมื่อป้ายกำกับในคอลัมน์ A ลงท้ายด้วยคำว่า เหนือ ให้ใส่เครื่องหมายดอกจันไว้ด้านหน้า

วิธีนี้จะจับคู่คำว่า เหนือไกล ขึ้นเหนือ และ เหนือ ได้ทั้งหมด เครื่องหมายดอกจันจะครอบคลุมทุกสิ่งที่อยู่หน้าคำดังกล่าว

ครอบข้อความด้วยเครื่องหมายดอกจันที่ทั้งสองด้าน เพื่อจับคู่ข้อความที่อยู่ส่วนใดก็ได้ภายในเซลล์ หากต้องการนับตำแหน่งงานใน A2:A10 ที่มีคำว่า ผู้จัดการ:

วิธีนี้จะค้นพบทั้ง ผู้จัดการฝ่ายขาย ผู้จัดการฝ่าย IT และผู้ช่วยผู้จัดการ เครื่องหมายดอกจันทั้งสองตัวเปิดให้มีข้อความใด ๆ อยู่ก่อนและหลังคำที่ต้องการ

เครื่องหมายคำถามจะจับคู่อักขระหนึ่งตัวพอดี ไม่มากหรือน้อยกว่านั้น หากรหัสประกอบด้วยตัวอักษรหนึ่งตัวตามด้วยตัวเลขสองหลักเสมอ ให้ใช้เครื่องหมายคำถามหนึ่งตัวต่ออักขระที่ไม่ทราบค่า เพื่อค้นหารหัสที่ขึ้นต้นด้วย A

"A??" จะจับคู่ A12 และ A99 แต่ไม่จับคู่ A1 หรือ A123 เพราะความยาวต้องเท่ากับสามอักขระพอดี

หากต้องการสร้างเงื่อนไขการขึ้นต้นด้วยข้อความจากเซลล์ ให้เชื่อมเซลล์กับเครื่องหมายดอกจันด้วยเครื่องหมายแอมเพอร์แซนด์ หาก D1 มีค่า AB อยู่ ให้จับคู่ทุกสิ่งที่ขึ้นต้นด้วยข้อความดังกล่าว

& จะต่อเครื่องหมายดอกจันเข้ากับค่าใน D1 ทำให้เกิด "AB*" ตอนนี้การจับคู่ข้อความบางส่วนจะอ้างอิงจากเซลล์แทนการระบุข้อความตายตัว

ใช้เทคนิคเดียวกันเพื่อสร้างการค้นหาข้อความที่มีคำอยู่ภายใน โดยครอบเซลล์ด้วยเครื่องหมายดอกจันทั้งสองด้านผ่านเครื่องหมายแอมเพอร์แซนด์สองตัว

ในที่นี้ ค่าใน D1 จะถูกครอบเป็น "*value*" ดังนั้นเมื่อเปลี่ยน D1 สิ่งที่สูตรค้นหาก็จะเปลี่ยนทันที นี่คือหัวใจของสรุปข้อมูลแบบค้นหาขณะพิมพ์อย่างง่าย

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

ดังนั้น "~?" จะค้นหาเครื่องหมายคำถามตามตัวอักษร และ "~*" จะค้นหาเครื่องหมายดอกจันตามตัวอักษร เครื่องหมายทิลเดอจะบอกให้สเปรดชีตถือว่าอักขระถัดไปเป็นข้อความทั่วไป

ข้อจำกัดสำคัญประการหนึ่งคือ อักขระตัวแทนใช้กับข้อความ ไม่ใช่ตัวเลข คุณไม่สามารถใช้ * เพื่อจับคู่บางส่วนของตัวเลข เช่น ราคาได้

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

ลองนึกภาพว่าคอลัมน์ A มีรหัสอย่าง SALES-01, SALES-02 และ HR-01 ส่วนคอลัมน์ B มีงบประมาณ หากต้องการหาผลรวมของฝ่ายขายทั้งหมด ให้จับคู่รหัสใด ๆ ที่ขึ้นต้นด้วย SALES แล้วตามด้วยข้อความใดก็ได้

ใช้ SUMIF หนึ่งสูตรกับ "SALES*" เพื่อสรุปรวมรหัสย่อยทั้งหมดได้ในครั้งเดียว โดยไม่ต้องระบุทีละรายการ เพียงเปลี่ยนคำนำหน้าก็สามารถสรุปยอดของแผนกอื่นได้

ทดสอบความเข้าใจของคุณเกี่ยวกับการจับคู่ด้วยอักขระแทน

ตอนนี้คุณสามารถจับคู่ข้อความบางส่วนในฟังก์ชันทางคณิตศาสตร์ IF ได้แล้ว โปรดจำไว้ว่า:

• * จับคู่อักขระจำนวนเท่าใดก็ได้ ส่วน ? จับคู่อักขระได้พอดีหนึ่งตัว
• ใช้ "AB*" สำหรับข้อความที่ขึ้นต้นด้วย ใช้ "*North" สำหรับข้อความที่ลงท้ายด้วย และใช้ "*Manager*" สำหรับข้อความที่มีคำดังกล่าวอยู่ภายใน
• เชื่อมเซลล์กับเครื่องหมายแอมเพอร์แซนด์ ดังใน D1&"*"
• ใช้เครื่องหมายทิลด์เพื่อหลีกอักขระ * หรือ ? ที่ต้องการให้เป็นอักขระตามตัวอักษร และโปรดจำไว้ว่าอักขระแทนใช้ได้เฉพาะกับข้อความเท่านั้น

เท่านี้ก็จบเรื่องการคำนวณแบบมีเงื่อนไขด้วย SUMIF, COUNTIF และ AVERAGEIF