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