เมื่อคุณรู้วิธีใช้ฟังก์ชันทางอ้อมใน Excel แล้ว คุณสามารถดึงข้อมูลจากชีตอื่น อ้างอิงช่วงที่มีชื่อ และรวมเข้ากับฟังก์ชันอื่นๆ เพื่อสร้างเครื่องมืออเนกประสงค์ได้อย่างแท้จริง อาจต้องใช้การฝึกฝนเล็กน้อยเพื่อรับมือ แต่ด้วยฟังก์ชันทางอ้อม คุณสามารถทำอะไรได้มากกว่าที่คุณคิด
คำแนะนำในบทความนี้ใช้กับ Excel สำหรับ Microsoft 365, Excel 2019 และ Excel 2016
ฟังก์ชันทางอ้อมคืออะไร
ฟังก์ชันทางอ้อมเป็นวิธีแปลงสตริงข้อความเป็นข้อมูลอ้างอิง นั่นคือดึงข้อมูลจากการอ้างอิงไปยังเซลล์หรือช่วงอื่น จะสร้างการอ้างอิงจากข้อความ และจะไม่เปลี่ยนแปลงเมื่อมีการแก้ไข เพิ่ม หรือลบเซลล์ แถว หรือคอลัมน์ออกจากช่วงที่อ้างถึงข้อมูลอ้างอิงที่สร้างจะได้รับการประเมินแบบเรียลไทม์ ดังนั้นข้อมูลอ้างอิงจึงแม่นยำเสมอกับข้อมูลที่ดึงออกมา
ถ้ารู้สึกสับสนนิดหน่อยก็ไม่ต้องหงุดหงิด สูตรทางอ้อมสามารถเข้าใจได้ง่ายขึ้นด้วยตัวอย่างที่ถูกต้องและในทางปฏิบัติ หากมีข้อสงสัย ให้ทำตามขั้นตอนด้านล่าง แล้วคุณจะเข้าใจในทันที
การใช้ฟังก์ชันทางอ้อมกับช่วงที่มีชื่อ
ช่วงที่มีชื่อใน Excel เป็นวิธีที่ยอดเยี่ยมในการรวบรวมข้อมูลภายใต้การอ้างอิงเดียว และฟังก์ชันทางอ้อมทำให้การรับข้อมูลจากพวกเขานั้นง่ายขึ้นเล็กน้อย นี่คือวิธีการ:
-
เปิดเอกสาร Excel ที่ใช้ช่วงที่มีชื่อแล้ว ในตัวอย่างของเรา เรามีข้อมูลการขายจากอาหารและเครื่องดื่มต่างๆ โดยเงินที่ได้รับในแต่ละวันของสัปดาห์จะรวบรวมภายใต้ช่วงที่ตั้งชื่อตามผลิตภัณฑ์
Image -
เลือกเซลล์สำหรับช่วงที่คุณตั้งชื่อไว้ และป้อนหนึ่งในเซลล์นั้นลงไป ในตัวอย่างของเรา เราใช้ Burgers เพิ่มชื่อผู้ออกแบบและระบายสีอื่นๆ หากต้องการ
Image -
เลือกเซลล์อื่นที่คุณต้องการให้เอาต์พุตทางอ้อมไป เนื่องจากเรากำลังมองหาการเพิ่มยอดขายทั้งหมดจากสัปดาห์สำหรับอาหารชนิดใดชนิดหนึ่ง ในกรณีนี้ เบอร์เกอร์ เราจะพิมพ์ข้อความต่อไปนี้ลงในเซลล์:
=SUM(ทางอ้อม(G5)
-
สิ่งนี้กำหนดฟังก์ชัน SUM ซึ่งจะใช้ฟังก์ชันทางอ้อมเพื่อดึงข้อมูลจากช่วงที่มีชื่อในเซลล์ G5 ในกรณีนี้ Burgers ผลผลิตคือ 3781 ยอดขายรวมของสัปดาห์สำหรับเบอร์เกอร์
ในตัวอย่างของเรา เราสามารถแทนที่เบอร์เกอร์ในเซลล์ G5 ด้วยน้ำมะนาวหรือของหวาน ส่วนอีกสองช่วงที่มีชื่อ และผลลัพธ์จะเปลี่ยนเป็นผลรวม SUM แทน
Image
การใช้ฟังก์ชันทางอ้อมบนหลายแผ่น
สูตรทางอ้อมจะมีประสิทธิภาพมากขึ้นเมื่อคุณใช้เพื่อดึงข้อมูลจากแผ่นงานอื่นๆ คุณไม่จำเป็นต้องใช้ช่วงที่มีชื่อเพื่อทำเช่นกัน
- เปิดเอกสาร Excel ของคุณที่มีหลายแผ่น หรือสร้างด้วยข้อมูลที่จำเป็นทั้งหมด
- ในชีตที่คุณต้องการให้เอาต์พุตทางอ้อม ให้สร้างเซลล์ที่มีชื่อชีตที่คุณต้องการดึงข้อมูล ในตัวอย่างของเราคือ FoodSales.
-
เนื่องจากเราต้องการดึงข้อมูลจากแผ่นงาน FoodSales รวมจำนวน Burgers ขาย เราจึงพิมพ์ข้อความต่อไปนี้ (แทนที่ ช่วงเซลล์และชื่อชีตด้วยของคุณเอง):
=SUM(ทางอ้อม(B4&"!B4:B10"))
Image - นี่กำหนดว่าเป็นฟังก์ชัน SUM เนื่องจากเรากำลังพยายามหาผลรวม จากนั้นกำหนดเซลล์ B4 เป็นข้อความอ้างอิงสำหรับฟังก์ชันทางอ้อม & นำองค์ประกอบของฟังก์ชันนี้มารวมกัน ตามด้วยเครื่องหมายคำพูดและเครื่องหมายอัศเจรีย์ ตามด้วยช่วงของเซลล์ที่เราต้องการดึงข้อมูลออกมา B4 ถึง B10
-
ผลลัพธ์คือยอดรวมของยอดขายเบอร์เกอร์ในสัปดาห์นั้น เมื่อเราสร้างแผ่นงาน FoodSales2 ใหม่สำหรับสัปดาห์ใหม่ที่มีตัวเลขต่างกัน เราเพียงแค่ปรับเซลล์ B4 เพื่อบอกว่า FoodSales2 เพื่อรับข้อมูลยอดขายเบอร์เกอร์สำหรับสัปดาห์นั้น
การใช้ฟังก์ชันทางอ้อมกับการอ้างอิงรูปแบบ R1C1
สำหรับชีตที่มีการขยายอย่างต่อเนื่อง โดยที่การอ้างอิงที่คุณต้องการใช้จะไม่อยู่ในเซลล์เดียวกันเสมอไป การอ้างอิงสไตล์ R1C1 สามารถใช้กับสูตรทางอ้อมเพื่อให้ข้อมูลที่คุณต้องการได้เราจะยังคงใช้ตัวอย่างการขายอาหารของเราที่นี่ แต่ลองนึกภาพว่าสำหรับเวิร์กชีตในระดับที่สูงขึ้นซึ่งพิจารณาถึงยอดรวมของยอดขายรายสัปดาห์
- เปิดเอกสาร Excel ที่มีข้อมูลทั้งหมดที่คุณต้องการดึงออกมา แล้วเลือกเซลล์สำหรับเอาต์พุตฟังก์ชันทางอ้อมของคุณ ในตัวอย่างของเรา เรากำลังดูยอดรวมการขายอาหารรายเดือน และเราต้องการทราบยอดขายรวมล่าสุดในเดือนนั้น
-
ในตัวอย่างของเรา สูตรจะมีลักษณะดังนี้:
=ทางอ้อม("R12C"&COUNTA(12:12), FALSE)
Image -
ฟังก์ชันทางอ้อมใช้ R12 (แถวที่ 12) ตามด้วย C เพื่อระบุคอลัมน์ที่อยู่ภายในเครื่องหมายคำพูด & รวมสองส่วนของฟังก์ชันเข้าด้วยกัน เรากำลังใช้ฟังก์ชัน COUNTA เพื่อนับเซลล์ที่ไม่ว่างทั้งหมดในแถวที่ 12 (เลือกแถวหรือพิมพ์ 12:12) ตามด้วยเครื่องหมายจุลภาคFALSE กำหนดให้สิ่งนี้เป็นข้อมูลอ้างอิง R1C1
-
ผลลัพธ์คือรายการสุดท้ายในตารางของเรา ในกรณีนี้คือ 8102 หรือ $8, 102 เมื่อเราเพิ่มข้อมูลการขายของเดือนเมษายนในที่สุด หมายเลขยอดขายล่าสุดจะอัปเดตโดยอัตโนมัติในแบบเรียลไทม์
Image
