ต้องรู้
- ฟังก์ชัน INDIRECT จะเปลี่ยนช่วงของการอ้างอิงเซลล์ในสูตรโดยไม่ต้องแก้ไขสูตร
- ใช้ทางอ้อมเป็นอาร์กิวเมนต์สำหรับ COUNTIF เพื่อสร้างช่วงไดนามิกของเซลล์ที่ตรงตามเกณฑ์ที่ระบุ
- เกณฑ์กำหนดโดยฟังก์ชัน INDIRECT และจะนับเฉพาะเซลล์ที่ตรงตามเกณฑ์เท่านั้น
บทความนี้อธิบายวิธีใช้ฟังก์ชันทางอ้อมในสูตร Excel เพื่อเปลี่ยนช่วงของการอ้างอิงเซลล์ที่ใช้ในสูตรโดยไม่ต้องแก้ไขสูตรเอง เพื่อให้แน่ใจว่ามีการใช้เซลล์เดียวกัน แม้ว่าสเปรดชีตของคุณจะเปลี่ยนไปข้อมูลใช้กับ Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac และ Excel Online
ใช้ช่วงไดนามิกกับ COUNTIF - สูตรทางอ้อม
ฟังก์ชัน INDIRECT สามารถใช้กับฟังก์ชันจำนวนหนึ่งที่ยอมรับการอ้างอิงเซลล์เป็นอาร์กิวเมนต์ เช่น ฟังก์ชัน SUM และ COUNTIF
การใช้ทางอ้อมเป็นอาร์กิวเมนต์สำหรับ COUNTIF จะสร้างช่วงไดนามิกของการอ้างอิงเซลล์ที่ฟังก์ชันสามารถนับได้ ถ้าค่าของเซลล์ตรงตามเกณฑ์ โดยเปลี่ยนข้อมูลข้อความ ซึ่งบางครั้งเรียกว่าสตริงข้อความ เป็นการอ้างอิงเซลล์
ตัวอย่างนี้อ้างอิงจากข้อมูลที่แสดงในภาพด้านบน สูตร COUNTIF - ทางอ้อมที่สร้างในบทช่วยสอนคือ:
=COUNTIF(ทางอ้อม(E1&":"&E2), ">10")
ในสูตรนี้ อาร์กิวเมนต์สำหรับฟังก์ชัน INDIRECT ประกอบด้วย:
- เซลล์อ้างอิง E1 และ E2 ซึ่งมีข้อมูลข้อความ D1 และ D6
- ตัวดำเนินการช่วง ทวิภาค (:) ล้อมรอบด้วยเครื่องหมายคำพูดคู่ (" ") ที่จะเปลี่ยนเครื่องหมายทวิภาคให้เป็นข้อความ สตริง
- เครื่องหมายสองอัน (&) ที่ใช้ต่อหรือรวมเครื่องหมายทวิภาคที่มีการอ้างอิงเซลล์ E1 และ E2
ผลลัพธ์คือ INDIRECT แปลงสตริงข้อความ D1:D6 เป็นการอ้างอิงเซลล์และส่งผ่านไปยังฟังก์ชัน COUNTIF ที่จะนับหากเซลล์ที่อ้างอิงมีขนาดใหญ่กว่า 10
ฟังก์ชัน INDIRECT ยอมรับการป้อนข้อความใดๆ เซลล์เหล่านี้อาจเป็นเซลล์ในเวิร์กชีตที่มีข้อความหรือการอ้างอิงเซลล์ข้อความที่ป้อนลงในฟังก์ชันโดยตรง
เปลี่ยนช่วงของสูตรแบบไดนามิก
จำไว้ว่า เป้าหมายคือการสร้างสูตรที่มีช่วงไดนามิก ช่วงไดนามิกสามารถเปลี่ยนแปลงได้โดยไม่ต้องแก้ไขสูตรเอง
โดยการเปลี่ยนข้อมูลข้อความที่อยู่ในเซลล์ E1 และ E2 จาก D1 และ D6 เป็น D3 และ D7 ช่วงที่ฟังก์ชันทั้งหมดสามารถเปลี่ยนแปลงได้อย่างง่ายดายจาก D1:D6 เป็น D3:D7 ทำให้ไม่จำเป็นต้องแก้ไขสูตรในเซลล์ G1 โดยตรง
ฟังก์ชัน COUNTIF ในตัวอย่างนี้จะนับเฉพาะเซลล์ที่มีตัวเลขหากจำนวนนั้นมากกว่า 10 แม้ว่าสี่ในห้าเซลล์ในช่วง D1:D6 จะมีข้อมูล แต่มีเพียงสามเซลล์เท่านั้นที่มีตัวเลข เซลล์ที่ว่างหรือมีข้อมูลข้อความจะถูกละเว้นโดยฟังก์ชัน
นับข้อความด้วย COUNTIF
ฟังก์ชัน COUNTIF ไม่จำกัดเฉพาะการนับข้อมูลตัวเลข นอกจากนี้ยังนับเซลล์ที่มีข้อความโดยตรวจสอบว่าตรงกับข้อความหรือไม่
ในการทำเช่นนี้ ให้ป้อนสูตรต่อไปนี้ในเซลล์ G2:
=COUNTIF(ทางอ้อม(E1&":"&E2), "สอง")
ในสูตรนี้ ฟังก์ชัน INDIRECT จะอ้างอิงเซลล์ B1 ถึง B6 ฟังก์ชัน COUNTIF จะรวมจำนวนเซลล์ที่มีค่าข้อความเป็น 2 ในเซลล์
ในกรณีนี้ ผลลัพธ์คือ 1.
COUNTA, COUNTBLANK และทางอ้อม
ฟังก์ชันการนับ Excel อีกสองฟังก์ชันคือ COUNTA ซึ่งนับเซลล์ที่มีข้อมูลประเภทใดก็ได้โดยไม่สนใจเฉพาะเซลล์ว่างหรือเซลล์ว่าง และ COUNTBLANK ซึ่งนับเฉพาะเซลล์ว่างหรือเซลล์ว่างในช่วง
เนื่องจากฟังก์ชันทั้งสองนี้มีไวยากรณ์คล้ายกับฟังก์ชัน COUNTIF จึงสามารถแทนที่ฟังก์ชันเหล่านี้ลงในตัวอย่างด้านบนด้วย INDIRECT เพื่อสร้างสูตรต่อไปนี้:
=COUNTA(ทางอ้อม(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
สำหรับช่วง D1:D6 COUNTA จะส่งกลับคำตอบที่ 4 เนื่องจากสี่ในห้าเซลล์มีข้อมูล COUNTBLANK ส่งคืนคำตอบ 1 เนื่องจากมีเซลล์ว่างเพียงเซลล์เดียวในช่วงนี้
ทำไมต้องใช้ฟังก์ชันทางอ้อม
ประโยชน์ของการใช้ฟังก์ชัน INDIRECT ในสูตรเหล่านี้ทั้งหมดคือสามารถแทรกเซลล์ใหม่ได้ทุกที่ในช่วง
ช่วงจะเปลี่ยนแปลงแบบไดนามิกภายในฟังก์ชันต่างๆ และผลลัพธ์จะอัปเดตตามนั้น
Image หากไม่มีฟังก์ชัน INDIRECT ทุกฟังก์ชันจะต้องได้รับการแก้ไขเพื่อรวมทั้ง 7 เซลล์ รวมถึงเซลล์ใหม่
ประโยชน์ของฟังก์ชันทางอ้อมคือสามารถแทรกค่าข้อความเป็นการอ้างอิงเซลล์ และอัปเดตช่วงแบบไดนามิกทุกครั้งที่มีการเปลี่ยนแปลงในสเปรดชีตของคุณ
ทำให้การบำรุงรักษาสเปรดชีตโดยรวมง่ายขึ้นมาก โดยเฉพาะสเปรดชีตขนาดใหญ่มาก
