ตั้งค่าช่วงเงื่อนไข

Excel มีฟังก์ชันฐานข้อมูลอยู่กลุ่มหนึ่งที่ชื่อทั้งหมดขึ้นต้นด้วยตัวอักษร D ได้แก่ DSUM, DCOUNT, DAVERAGE, DGET และฟังก์ชันอื่น ๆ

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

บทเรียนนี้มุ่งเน้นการสร้างช่วงเกณฑ์ให้ถูกต้อง เพราะฟังก์ชัน D ทุกฟังก์ชันต้องอาศัยช่วงนี้

ฟังก์ชัน D ทุกฟังก์ชันใช้อาร์กิวเมนต์สามรายการเหมือนกัน:

• ฐานข้อมูล - ตารางทั้งหมด รวมถึงแถวหัวตาราง
• เขตข้อมูล - คอลัมน์ที่จะดำเนินการด้วย โดยระบุชื่อหัวคอลัมน์ในเครื่องหมายคำพูด หรือระบุหมายเลขคอลัมน์
• เกณฑ์ - ช่วงที่เก็บกฎสำหรับการจับคู่

ดังนั้นรูปแบบจึงเป็น DSUM(database, field, criteria) เสมอ อาร์กิวเมนต์เกณฑ์เป็นส่วนที่ผู้เริ่มต้นมักเขียนผิด เราจึงจะเริ่มจากส่วนนี้ก่อน

ช่วงเกณฑ์เป็นเพียงกลุ่มเซลล์ขนาดเล็กที่มีอย่างน้อยสองแถว:

• แถวบนสุดเก็บหัวคอลัมน์ที่ตรงกับหัวคอลัมน์ของฐานข้อมูลทุกประการ
• แถวด้านล่างเก็บเงื่อนไข

สมมติว่าข้อมูลยอดขายมีหัวคอลัมน์เป็น Region, Rep, Amount หากต้องการจับคู่เฉพาะภูมิภาคตะวันออก ช่วงเกณฑ์จะเป็นเซลล์สองเซลล์ที่เรียงซ้อนกัน โดยมี Region อยู่ด้านบนและ East อยู่ด้านล่าง

หัวตารางในช่วงเกณฑ์ต้องสะกดเหมือนกับหัวตารางของฐานข้อมูลทุกประการ หากคอลัมน์ข้อมูลของคุณชื่อ Amount แต่เกณฑ์ระบุเป็น Amounts หรือ amt ฟังก์ชัน D จะไม่พบคอลัมน์นั้น และอาจส่งคืนข้อผิดพลาดหรือค่าเป็นศูนย์

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

สมมติว่า A1:C13 มีตารางของคุณ โดยมีหัวตารางเป็น Region, Rep, Amount ให้พิมพ์ Region ในเซลล์ E1 และพิมพ์ East ในเซลล์ E2 กลุ่มเซลล์สองเซลล์ E1:E2 นี้คือช่วงเกณฑ์ของคุณ

จากนั้น DSUM จะหาผลรวมเฉพาะแถว East ฟังก์ชันจะอ่านหัวตารางใน E1 เห็นว่าตรงกับคอลัมน์ Region แล้วเก็บไว้เฉพาะแถวที่ Region เท่ากับ East

โดยค่าเริ่มต้น เงื่อนไขข้อความอย่าง East จะจับคู่กับค่าที่ขึ้นต้นด้วยข้อความนั้น ดังนั้น East จึงจับคู่กับ Eastern ได้ด้วย

หากต้องการบังคับให้จับคู่แบบตรงกันทุกประการ ให้ครอบข้อความด้วยการเปรียบเทียบในรูปแบบสูตร: พิมพ์ ="=East" ลงในเซลล์เกณฑ์ คุณยังสามารถใช้อักขระตัวแทนได้ด้วย โดย E* จะจับคู่กับทุกสิ่งที่ขึ้นต้นด้วย E และ ?at จะจับคู่กับ Cat, Bat หรือ Hat

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

• >1000 จับคู่กับยอดที่มากกว่า 1000
• <=50 จับคู่กับค่าที่น้อยกว่าหรือเท่ากับ 50
• <>0 จับคู่กับทุกสิ่งที่ไม่ใช่ศูนย์

วางหัวตาราง เช่น Amount ไว้ด้านบน แล้ววางข้อความเปรียบเทียบไว้ด้านล่าง ฟังก์ชัน D จะประเมินค่าของแต่ละระเบียนเทียบกับกฎนั้น

เงื่อนไขที่วางเคียงข้างกันในแถวเดียวกันจะเชื่อมด้วย AND ซึ่งหมายความว่าทุกเงื่อนไขต้องเป็นจริง

หากต้องการจับคู่ภูมิภาคตะวันออกกับยอดที่มากกว่า 1000 ให้สร้างช่วงเกณฑ์สองคอลัมน์ โดยวางหัวตาราง Region และ Amount ในแถวบนสุด แล้ววาง East และ >1000 ในแถวด้านล่าง ระเบียนจะผ่านเกณฑ์ก็ต่อเมื่ออยู่ใน East และมียอดมากกว่า 1000

เงื่อนไขที่วางในแยกคนละแถวจะเชื่อมด้วย OR ซึ่งหมายความว่าแถวใดแถวหนึ่งที่ตรงตามเงื่อนไขก็เพียงพอ

หากต้องการจับคู่ East หรือ West ให้วางหัวตาราง Region ไว้ด้านบน จากนั้นวาง East ในแถวถัดไป และ West ในแถวถัดจากนั้น ช่วงเกณฑ์จะครอบคลุมสามแถว และระเบียนจะผ่านเกณฑ์หากตรงกับค่าใดค่าหนึ่ง

อย่าลืมรวมแถวทั้งหมดเหล่านี้ไว้ในอาร์กิวเมนต์เกณฑ์

คุณสามารถใช้การจัดวางทั้งสองแบบร่วมกันได้ สมมติว่าคุณต้องการ (East AND >1000) OR (West AND >500)

ใช้สองคอลัมน์คือ Region และ Amount วาง East คู่กับ >1000 ในแถวหนึ่ง แล้ววาง West คู่กับ >500 ในแถวถัดไป แต่ละแถวเป็นกลุ่ม AND ส่วนแถวที่แยกกันทำหน้าที่เป็น OR การจัดวางแบบตารางนี้ช่วยให้ฟังก์ชัน D แสดงตรรกะที่ซับซ้อนได้โดยไม่ต้องซ้อนฟังก์ชัน

ระวังข้อผิดพลาดเหล่านี้:

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

เก็บช่วงเกณฑ์ไว้ในพื้นที่แยกต่างหากที่เห็นได้ชัดเจนบนแผ่นงาน

ในช่วงเกณฑ์ของฟังก์ชัน D เงื่อนไขสองรายการที่เขียนอยู่ในแถวเดียวกันจะถูกรวมกันอย่างไร

ช่วงเกณฑ์คือหัวใจของฟังก์ชัน D ทุกฟังก์ชัน ประเด็นสำคัญมีดังนี้:

• ต้องมีแถวหัวตารางที่มีชื่อตรงกับตารางทุกประการ
• วางเงื่อนไขไว้ในแถวด้านล่าง ซึ่งอาจเป็นข้อความ อักขระตัวแทน หรือการเปรียบเทียบอย่าง >1000
• แถวเดียวกัน = AND และแยกคนละแถว = OR
• หลีกเลี่ยงแถวเงื่อนไขว่าง เพราะจะจับคู่กับทุกสิ่ง

เมื่อเตรียมช่วงเกณฑ์ที่เรียบร้อยแล้ว คุณก็พร้อมส่งช่วงดังกล่าวให้กับ DSUM, DCOUNT, DAVERAGE และ DGET ในบทเรียนถัดไป