เนื้อหา
ยินดีด้วย! คุณมาถึงวันสุดท้ายของการวิ่งมาราธอน 30 ฟังก์ชัน Excel ใน 30 วัน. เป็นการเดินทางที่ยาวนานและน่าสนใจในระหว่างที่คุณได้เรียนรู้สิ่งที่มีประโยชน์มากมายเกี่ยวกับฟังก์ชัน Excel
วันที่ 30 ของการวิ่งมาราธอน เราจะทุ่มเทศึกษาหน้าที่ ทางอ้อม (ทางอ้อม) ซึ่งส่งคืนลิงก์ที่ระบุโดยสตริงข้อความ ด้วยฟังก์ชันนี้ คุณสามารถสร้างรายการดรอปดาวน์ที่ขึ้นต่อกัน ตัวอย่างเช่น เมื่อเลือกประเทศจากรายการดรอปดาวน์ จะเป็นตัวกำหนดว่าตัวเลือกใดจะปรากฏในรายการดรอปดาวน์ของเมือง
มาดูส่วนทฤษฎีของฟังก์ชันกันดีกว่า ทางอ้อม (ทางอ้อม) และสำรวจตัวอย่างการใช้งานจริง หากคุณมีข้อมูลหรือตัวอย่างเพิ่มเติม โปรดแบ่งปันในความคิดเห็น
ฟังก์ชัน 30: ทางอ้อม
ฟังก์ชัน ทางอ้อม (ทางอ้อม) ส่งคืนลิงก์ที่ระบุโดยสตริงข้อความ
คุณจะใช้ฟังก์ชัน INDIRECT ได้อย่างไร?
ตั้งแต่หน้าที่ ทางอ้อม (ทางอ้อม) ส่งคืนลิงก์ที่กำหนดโดยสตริงข้อความ คุณสามารถใช้เพื่อ:
- สร้างลิงค์เริ่มต้นที่ไม่เปลี่ยน
- สร้างการอ้างอิงไปยังช่วงที่มีชื่อคงที่
- สร้างลิงก์โดยใช้ข้อมูลแผ่นงาน แถว และคอลัมน์
- สร้างอาร์เรย์ตัวเลขที่ไม่ขยับ
ไวยากรณ์ทางอ้อม (ทางอ้อม)
ฟังก์ชัน ทางอ้อม (ทางอ้อม) มีรูปแบบดังนี้:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) คือข้อความของลิงก์
- a1 – ถ้าเท่ากับ TRUE (TRUE) หรือไม่ระบุ รูปแบบของลิงค์จะถูกใช้ A1; และถ้า FALSE (FALSE) แสดงว่า style R1C1.
กับดักทางอ้อม (ทางอ้อม)
- ฟังก์ชัน ทางอ้อม (ทางอ้อม) จะถูกคำนวณใหม่ทุกครั้งที่ค่าในแผ่นงาน Excel เปลี่ยนไป การทำเช่นนี้อาจทำให้เวิร์กบุ๊กของคุณช้าลงได้อย่างมาก หากมีการใช้ฟังก์ชันนี้ในหลายสูตร
- ถ้าฟังก์ชัน ทางอ้อม (ทางอ้อม) สร้างลิงก์ไปยังเวิร์กบุ๊ก Excel อื่น เวิร์กบุ๊กนั้นจะต้องเปิดอยู่ ไม่เช่นนั้นสูตรจะรายงานข้อผิดพลาด #REF! (#ลิงก์!).
- ถ้าฟังก์ชัน ทางอ้อม (ทางอ้อม) อ้างอิงช่วงที่เกินขีดจำกัดแถวและคอลัมน์ สูตรจะรายงานข้อผิดพลาด #REF! (#ลิงก์!).
- ฟังก์ชัน ทางอ้อม (ทางอ้อม) ไม่สามารถอ้างอิงช่วงที่มีชื่อไดนามิก
ตัวอย่างที่ 1: สร้างลิงก์เริ่มต้นที่ไม่เปลี่ยนแปลง
ในตัวอย่างแรก คอลัมน์ C และ E มีตัวเลขเหมือนกัน โดยจะคำนวณผลรวมโดยใช้ฟังก์ชัน SUM (SUM) ก็เช่นเดียวกัน อย่างไรก็ตาม สูตรจะแตกต่างกันเล็กน้อย ในเซลล์ C8 สูตรคือ:
=SUM(C2:C7)
=СУММ(C2:C7)
ในเซลล์ E8 ฟังก์ชัน ทางอ้อม (ทางอ้อม) สร้างลิงก์ไปยังเซลล์เริ่มต้น E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
หากคุณแทรกแถวที่ด้านบนของแผ่นงานและเพิ่มค่าสำหรับเดือนมกราคม (ม.ค.) จำนวนในคอลัมน์ C จะไม่เปลี่ยนแปลง สูตรจะเปลี่ยนโดยตอบสนองต่อการเพิ่มบรรทัด:
=SUM(C3:C8)
=СУММ(C3:C8)
อย่างไรก็ตาม ฟังก์ชัน ทางอ้อม (ทางอ้อม) แก้ไข E2 เป็นเซลล์เริ่มต้น ดังนั้นมกราคมจะรวมอยู่ในการคำนวณผลรวมของคอลัมน์ E โดยอัตโนมัติ เซลล์สิ้นสุดมีการเปลี่ยนแปลง แต่เซลล์เริ่มต้นไม่ได้รับผลกระทบ
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
ตัวอย่างที่ 2: ลิงก์ไปยัง range ที่มีชื่อคงที่
ฟังก์ชัน ทางอ้อม (ทางอ้อม) สามารถสร้างการอ้างอิงไปยังช่วงที่มีชื่อได้ ในตัวอย่างนี้ เซลล์สีน้ำเงินประกอบกันเป็นช่วง NumList. นอกจากนี้ ช่วงไดนามิกยังสร้างจากค่าในคอลัมน์ B NumListDynขึ้นอยู่กับจำนวนตัวเลขในคอลัมน์นี้
ผลรวมของทั้งสองช่วงสามารถคำนวณได้โดยเพียงแค่ตั้งชื่อเป็นอาร์กิวเมนต์ของฟังก์ชัน SUM (SUM) ดังที่คุณเห็นในเซลล์ E3 และ E4
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
แทนที่จะพิมพ์ชื่อช่วงลงในฟังก์ชัน SUM (SUM) คุณสามารถอ้างถึงชื่อที่เขียนในเซลล์ใดเซลล์หนึ่งของเวิร์กชีต ตัวอย่างเช่น ถ้าชื่อ NumList เขียนในเซลล์ D7 จากนั้นสูตรในเซลล์ E7 จะเป็นดังนี้:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
น่าเสียดายที่ฟังก์ชัน ทางอ้อม (ทางอ้อม) ไม่สามารถสร้างการอ้างอิงช่วงไดนามิก ดังนั้นเมื่อคุณคัดลอกสูตรนี้ลงในเซลล์ E8 คุณจะได้รับข้อผิดพลาด #REF! (#ลิงก์!).
ตัวอย่างที่ 3: สร้างลิงก์โดยใช้ข้อมูลแผ่นงาน แถว และคอลัมน์
คุณสามารถสร้างลิงค์โดยยึดตามหมายเลขแถวและคอลัมน์ได้อย่างง่ายดาย เช่นเดียวกับการใช้ค่า FALSE (FALSE) สำหรับอาร์กิวเมนต์ของฟังก์ชันที่สอง ทางอ้อม (ทางอ้อม). นี่คือวิธีการสร้างลิงค์สไตล์ R1C1. ในตัวอย่างนี้ เราได้เพิ่มชื่อแผ่นงานลงในลิงก์ – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
ตัวอย่างที่ 4: สร้างอาร์เรย์ตัวเลขที่ไม่ขยับ
บางครั้ง คุณจำเป็นต้องใช้อาร์เรย์ของตัวเลขในสูตร Excel ในตัวอย่างต่อไปนี้ เราต้องการหาค่าเฉลี่ยตัวเลขที่ใหญ่ที่สุด 3 ตัวในคอลัมน์ B ตัวเลขสามารถป้อนลงในสูตรได้ เช่นเดียวกับที่ทำในเซลล์ D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
หากคุณต้องการอาร์เรย์ที่ใหญ่กว่า คุณก็ไม่น่าจะต้องการป้อนตัวเลขทั้งหมดในสูตร ตัวเลือกที่สองคือการใช้ฟังก์ชัน แถว (ROW) ตามที่ทำในสูตรอาร์เรย์ที่ป้อนในเซลล์ D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
ตัวเลือกที่สามคือการใช้ฟังก์ชัน แถว (STRING) พร้อมด้วย ทางอ้อม (ทางอ้อม) เหมือนกับที่ทำกับสูตรอาร์เรย์ในเซลล์ D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
ผลลัพธ์สำหรับทั้ง 3 สูตรจะเหมือนกัน:
อย่างไรก็ตาม หากมีการแทรกแถวที่ด้านบนของแผ่นงาน สูตรที่สองจะแสดงผลลัพธ์ที่ไม่ถูกต้อง เนื่องจากการอ้างอิงในสูตรจะเปลี่ยนไปพร้อมกับการเลื่อนแถว ตอนนี้ แทนที่จะใช้ค่าเฉลี่ยของตัวเลขที่ใหญ่ที่สุดสามตัว สูตรจะส่งกลับค่าเฉลี่ยของตัวเลขที่มากที่สุดที่ 3, 4 และ 5
การใช้ฟังก์ชัน ทางอ้อม (ทางอ้อม) สูตรที่สามเก็บการอ้างอิงแถวที่ถูกต้องและยังคงแสดงผลที่ถูกต้องต่อไป