การวิเคราะห์ฟังก์ชันทางอ้อมตามตัวอย่าง

ได้อย่างรวดเร็วก่อน (โดยเฉพาะเมื่ออ่านความช่วยเหลือ) ฟังก์ชัน ทางอ้อม (ทางอ้อม) ดูเรียบง่ายและไม่จำเป็น สาระสำคัญของมันคือการเปลี่ยนข้อความที่ดูเหมือนลิงค์เป็นลิงค์ที่สมบูรณ์ เหล่านั้น. ถ้าเราต้องการอ้างถึงเซลล์ A1 เราก็สามารถสร้างลิงก์โดยตรงได้ตามปกติ (ป้อนเครื่องหมายเท่ากับใน D1 คลิกที่ A1 แล้วกด Enter) หรือเราจะใช้ ทางอ้อม เพื่อจุดประสงค์เดียวกัน:

โปรดทราบว่าอาร์กิวเมนต์ของฟังก์ชัน – การอ้างอิงถึง A1 – ถูกป้อนในเครื่องหมายคำพูด ดังนั้น อันที่จริงแล้ว เป็นข้อความที่นี่

“อืม ก็ได้” คุณพูด “แล้วมีประโยชน์อะไร” 

แต่อย่าตัดสินจากความประทับใจแรกพบ เพราะเป็นการหลอกลวง คุณลักษณะนี้สามารถช่วยคุณได้ในหลายสถานการณ์

ตัวอย่างที่ 1 ทรานสโพส

คลาสสิกของประเภท: คุณต้องเปิดdia .แนวตั้ง

ร่องเป็นแนวนอน (transpose) แน่นอน คุณสามารถใช้เม็ดมีดหรือฟังก์ชันพิเศษได้ ทรานส์ (โอน) ในสูตรอาร์เรย์ แต่คุณสามารถทำได้ด้วย ทางอ้อม:

ตรรกะง่าย ๆ : เพื่อให้ได้ที่อยู่ของเซลล์ถัดไปเราติดตัวอักษร "A" ด้วยอักขระพิเศษ "&" และหมายเลขคอลัมน์ของเซลล์ปัจจุบันซึ่งฟังก์ชันให้เรา คอลัมน์ (คอลัมน์).

ขั้นตอนย้อนกลับทำได้ดีกว่าแตกต่างกันเล็กน้อย ตั้งแต่เวลานี้เราจำเป็นต้องสร้างลิงก์ไปยังเซลล์ B2, C2, D2 ฯลฯ จึงสะดวกกว่าที่จะใช้โหมดลิงก์ R1C1 แทน "การต่อสู้ทางทะเล" แบบคลาสสิก ในโหมดนี้ เซลล์ของเราจะแตกต่างกันเฉพาะในคอลัมน์หมายเลข: B2=R1C2, C2=R1C3, D2=R1C4 เป็นต้น

นี่คือที่มาของอาร์กิวเมนต์ฟังก์ชันทางเลือกที่สอง ทางอ้อม. ถ้าเท่ากัน โกหก (เท็จ)จากนั้นคุณสามารถตั้งค่าที่อยู่ลิงก์ในโหมด R1C1 ดังนั้นเราจึงสามารถย้ายช่วงแนวนอนกลับเป็นแนวตั้งได้อย่างง่ายดาย:

ตัวอย่างที่ 2 ผลรวมตามช่วงเวลา

เราได้วิเคราะห์วิธีหนึ่งในการรวมหน้าต่าง (ช่วง) ของขนาดที่กำหนดบนแผ่นงานโดยใช้ฟังก์ชัน การกำจัด (ออฟเซ็ต). ปัญหาที่คล้ายกันสามารถแก้ไขได้โดยใช้ ทางอ้อม. หากเราต้องการสรุปข้อมูลจากช่วงระยะเวลาหนึ่งเท่านั้น เราก็สามารถติดมันจากชิ้นส่วนแล้วแปลงเป็นลิงก์ที่สมบูรณ์ ซึ่งเราสามารถแทรกเข้าไปภายในฟังก์ชันได้ SUM (ผลรวม):

ตัวอย่างที่ 3 รายการดรอปดาวน์ตารางอัจฉริยะ

บางครั้ง Microsoft Excel ไม่ถือว่าชื่อและคอลัมน์ของตารางอัจฉริยะเป็นลิงก์แบบเต็ม ตัวอย่างเช่น เมื่อพยายามสร้างรายการดรอปดาวน์ (tab ข้อมูล – การตรวจสอบข้อมูล) ตามคอลัมน์ พนักงาน จากสมาร์ทเทเบิล คน เราจะได้รับข้อผิดพลาด:

หากเรา "ตัด" ลิงก์ด้วยฟังก์ชันของเรา ทางอ้อมจากนั้น Excel จะยอมรับอย่างง่ายดาย และรายการดรอปดาวน์ของเราจะได้รับการอัปเดตแบบไดนามิกเมื่อเพิ่มพนักงานใหม่ต่อท้ายตารางอัจฉริยะ:

ตัวอย่างที่ 4 ลิงค์ที่แตกไม่ได้

ดังที่คุณทราบ Excel จะแก้ไขที่อยู่อ้างอิงในสูตรโดยอัตโนมัติเมื่อแทรกหรือลบแถว-คอลัมน์บนแผ่นงาน ในกรณีส่วนใหญ่ วิธีนี้ถูกต้องและสะดวก แต่ไม่เสมอไป สมมติว่าเราต้องโอนชื่อจากไดเร็กทอรีพนักงานไปยังรายงาน:

หากคุณใส่ลิงก์ปกติ (ป้อน =B2 ในเซลล์สีเขียวเซลล์แรกแล้วคัดลอกลง) จากนั้นเมื่อคุณลบ เช่น Dasha เราจะได้รับ #LINK! ข้อผิดพลาดในเซลล์สีเขียวที่สอดคล้องกับเธอ (#REF!). กรณีใช้ฟังก์ชั่นสร้างลิงค์ ทางอ้อม จะไม่มีปัญหาดังกล่าว

ตัวอย่างที่ 5: การรวบรวมข้อมูลจากหลายแผ่น

สมมติว่าเรามีแผ่นงาน 5 แผ่นพร้อมรายงานประเภทเดียวกันจากพนักงานที่แตกต่างกัน (Mikhail, Elena, Ivan, Sergey, Dmitry):

สมมติว่ารูปร่าง ขนาด ตำแหน่งและลำดับของสินค้าและเดือนในตารางทั้งหมดเหมือนกัน – มีเพียงตัวเลขที่แตกต่างกันเท่านั้น

คุณสามารถรวบรวมข้อมูลจากชีตทั้งหมด (อย่าสรุป แต่วางไว้ใต้กันใน "กอง") ด้วยสูตรเดียว:

อย่างที่คุณเห็น แนวคิดก็เหมือนกัน: เราติดลิงก์ไปยังเซลล์ที่ต้องการของชีตที่กำหนดและ ทางอ้อม เปลี่ยนมันเป็น "สด" เพื่อความสะดวก เหนือตาราง ฉันเพิ่มตัวอักษรของคอลัมน์ (B,C,D) และด้านขวา – หมายเลขบรรทัดที่ต้องนำมาจากแต่ละแผ่นงาน

ข้อผิดพลาด

หากคุณกำลังใช้ ทางอ้อม (ทางอ้อม) คุณต้องจำเกี่ยวกับจุดอ่อนของมัน:

• หากคุณเชื่อมโยงไปยังไฟล์อื่น (โดยการติดชื่อไฟล์ในวงเล็บเหลี่ยม ชื่อแผ่นงาน และที่อยู่ของเซลล์) ไฟล์จะทำงานเฉพาะในขณะที่ไฟล์ต้นฉบับเปิดอยู่เท่านั้น ถ้าเราปิด เราได้รับข้อผิดพลาด #LINK!
• ทางอ้อมไม่สามารถอ้างถึงช่วงที่มีชื่อแบบไดนามิก แบบคงที่ - ไม่มีปัญหา
• ทางอ้อมเป็นฟังก์ชันผันผวนหรือ "ผันผวน" กล่าวคือ จะถูกคำนวณใหม่สำหรับการเปลี่ยนแปลงในเซลล์ใดๆ ของชีต ไม่ใช่แค่ส่งผลต่อเซลล์ เช่นเดียวกับในฟังก์ชันปกติ สิ่งนี้ส่งผลเสียต่อประสิทธิภาพการทำงาน และเป็นการดีกว่าที่จะไม่ยุ่งกับโต๊ะทางอ้อมขนาดใหญ่

• วิธีสร้างช่วงไดนามิกด้วยการปรับขนาดอัตโนมัติ
• รวมช่วงหน้าต่างบนแผ่นงานด้วยฟังก์ชัน OFFSET