ลิงก์ใน Excel – แบบสัมบูรณ์ แบบสัมพัทธ์ และแบบผสม ข้อผิดพลาดเมื่อทำงานกับลิงก์ที่เกี่ยวข้องใน Excel

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

1. ลิงค์ที่เกี่ยวข้อง ใช้สำหรับการคำนวณอย่างง่าย การคัดลอกสูตรทำให้เกิดการเปลี่ยนแปลงพิกัด
2. ลิงก์แบบสัมบูรณ์ หากคุณต้องการสร้างการคำนวณที่ซับซ้อนมากขึ้น ตัวเลือกนี้เหมาะสม สำหรับการแก้ไขให้ใช้สัญลักษณ์ “$” ตัวอย่าง: $A$1.
3. ลิงค์ผสม การกำหนดแอดเดรสประเภทนี้ใช้ในการคำนวณเมื่อจำเป็นต้องแก้ไขคอลัมน์หรือบรรทัดแยกกัน ตัวอย่าง: $A1 หรือ A$1

หากจำเป็นต้องคัดลอกข้อมูลของสูตรที่ป้อน การอ้างอิงที่มีการกำหนดแอดเดรสแบบสัมบูรณ์และแบบผสมจะถูกนำมาใช้ บทความจะเปิดเผยพร้อมตัวอย่างวิธีการคำนวณโดยใช้ลิงก์ประเภทต่างๆ

การอ้างอิงเซลล์สัมพัทธ์ใน Excel

นี่คือชุดของอักขระที่กำหนดตำแหน่งของเซลล์ ลิงค์ในโปรแกรมจะถูกเขียนโดยอัตโนมัติด้วยการกำหนดแอดเดรสที่สัมพันธ์กัน ตัวอย่างเช่น: A1, A2, B1, B2 การย้ายไปยังแถวหรือคอลัมน์อื่นจะเปลี่ยนอักขระในสูตร ตัวอย่างเช่น ตำแหน่งเริ่มต้น A1 การย้ายในแนวนอนจะเปลี่ยนตัวอักษรเป็น B1, C1, D1 ฯลฯ ในทำนองเดียวกันการเปลี่ยนแปลงเกิดขึ้นเมื่อเคลื่อนที่ไปตามเส้นแนวตั้งเท่านั้นในกรณีนี้ตัวเลขจะเปลี่ยน - A2, A3, A4 เป็นต้น หากจำเป็นต้องทำซ้ำ การคำนวณชนิดเดียวกันในเซลล์ที่อยู่ติดกัน การคำนวณจะดำเนินการโดยใช้การอ้างอิงแบบสัมพัทธ์ หากต้องการใช้คุณลักษณะนี้ ให้ทำตามขั้นตอนต่อไปนี้:

1. ทันทีที่ป้อนข้อมูลลงในเซลล์ ให้เลื่อนเคอร์เซอร์และคลิกด้วยเมาส์ การเน้นด้วยสี่เหลี่ยมสีเขียวหมายถึงการเปิดใช้งานเซลล์และความพร้อมสำหรับการทำงานต่อไป
2. โดยการกดคีย์ผสม Ctrl + C เราจะคัดลอกเนื้อหาไปยังคลิปบอร์ด
3. เราเปิดใช้งานเซลล์ที่คุณต้องการถ่ายโอนข้อมูลหรือสูตรที่เขียนไว้ก่อนหน้านี้
4. โดยการกดรวมกัน Ctrl + V เราจะโอนข้อมูลที่บันทึกไว้ในคลิปบอร์ดของระบบ

คำแนะนำจากผู้เชี่ยวชาญ! ในการคำนวณแบบเดียวกันในตาราง ให้ใช้แฮ็คชีวิต เลือกเซลล์ที่มีสูตรที่ป้อนก่อนหน้านี้ วางเคอร์เซอร์เหนือสี่เหลี่ยมเล็ก ๆ ที่ปรากฏที่มุมล่างขวาและกดปุ่มซ้ายของเมาส์ค้างไว้ ลากไปที่แถวล่างสุดหรือคอลัมน์สุดขั้ว ขึ้นอยู่กับการกระทำที่ทำ เมื่อปล่อยปุ่มเมาส์ การคำนวณจะดำเนินการโดยอัตโนมัติ เครื่องมือนี้เรียกว่าเครื่องหมายเติมอัตโนมัติ

ตัวอย่างลิงค์สัมพัทธ์

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

ลำดับของการกระทำ:

1. ตัวอย่างแสดงให้เห็นว่าคอลัมน์ B และ C ถูกใช้เพื่อเติมปริมาณสินค้าที่ขายและราคา ดังนั้น หากต้องการเขียนสูตรและรับคำตอบ ให้เลือกคอลัมน์ D สูตรจะมีลักษณะดังนี้: = บี2 *C

เอาใจใส่! เพื่ออำนวยความสะดวกในกระบวนการเขียนสูตรให้ใช้เคล็ดลับเล็กน้อย ใส่เครื่องหมาย “=” คลิกจำนวนสินค้าที่ขาย ตั้งเครื่องหมาย “*” แล้วคลิกราคาสินค้า สูตรหลังเครื่องหมายเท่ากับจะถูกเขียนโดยอัตโนมัติ

2. สำหรับคำตอบสุดท้าย ให้กด "Enter" ถัดไป คุณต้องคำนวณจำนวนกำไรทั้งหมดที่ได้รับจากผลิตภัณฑ์ประเภทอื่น ถ้าจำนวนบรรทัดไม่ใหญ่ การจัดการทั้งหมดสามารถทำได้ด้วยตนเอง ในการเติมแถวจำนวนมากพร้อมกันใน Excel มีฟังก์ชันที่มีประโยชน์อย่างหนึ่งที่ทำให้สามารถถ่ายโอนสูตรไปยังเซลล์อื่นได้
3. เลื่อนเคอร์เซอร์ไปที่มุมล่างขวาของสี่เหลี่ยมผืนผ้าด้วยสูตรหรือผลลัพธ์ที่เสร็จสิ้น การปรากฏตัวของกากบาทสีดำทำหน้าที่เป็นสัญญาณว่าสามารถลากเคอร์เซอร์ลงได้ ดังนั้นจึงทำการคำนวณกำไรที่ได้รับโดยอัตโนมัติสำหรับแต่ละผลิตภัณฑ์แยกกัน
4. โดยการปล่อยปุ่มเมาส์ที่กดไว้ เราจะได้ผลลัพธ์ที่ถูกต้องในทุกบรรทัด

เมื่อคลิกที่เซลล์ D3 คุณจะเห็นว่าพิกัดของเซลล์มีการเปลี่ยนแปลงโดยอัตโนมัติ และตอนนี้มีลักษณะดังนี้: =บี3 *C3 มันตามมาว่าลิงก์นั้นสัมพันธ์กัน

ข้อผิดพลาดที่เป็นไปได้เมื่อทำงานกับลิงก์ที่เกี่ยวข้อง

ไม่ต้องสงสัย ฟังก์ชัน Excel นี้ทำให้การคำนวณง่ายขึ้นอย่างมาก แต่ในบางกรณีอาจเกิดปัญหาขึ้นได้ ลองพิจารณาตัวอย่างง่ายๆ ในการคำนวณค่าสัมประสิทธิ์กำไรของสินค้าแต่ละรายการ:

1. สร้างตารางและกรอก: A – ชื่อผลิตภัณฑ์; B – ปริมาณขาย; C – ต้นทุน; D คือจำนวนเงินที่ได้รับ สมมติว่ามีเพียง 11 รายการในการเลือกสรร ดังนั้นโดยคำนึงถึงคำอธิบายของคอลัมน์จึงเติม 12 บรรทัดและจำนวนกำไรทั้งหมดคือD
2. คลิกที่เซลล์ E2 และป้อน =D2 /D13.
3. หลังจากกดปุ่ม "Enter" ค่าสัมประสิทธิ์ของส่วนแบ่งการขายที่เกี่ยวข้องของรายการแรกจะปรากฏขึ้น
4. ยืดคอลัมน์ลงและรอผล อย่างไรก็ตาม ระบบแสดงข้อผิดพลาด #DIV/0!”

สาเหตุของข้อผิดพลาดคือการใช้การอ้างอิงแบบสัมพัทธ์สำหรับการคำนวณ จากการคัดลอกสูตร พิกัดจึงเปลี่ยนไป นั่นคือสำหรับ E3 สูตรจะเป็นแบบนี้ =D3 /D13. เนื่องจากเซลล์ D13 ไม่ได้เติมและในทางทฤษฎีมีค่าเป็นศูนย์ โปรแกรมจะให้ข้อผิดพลาดกับข้อมูลที่หารด้วยศูนย์เป็นไปไม่ได้

สำคัญ! เพื่อแก้ไขข้อผิดพลาด จำเป็นต้องเขียนสูตรในลักษณะที่พิกัด D13 ได้รับการแก้ไข การระบุที่อยู่สัมพัทธ์ไม่มีฟังก์ชันดังกล่าว ในการทำเช่นนี้ มีลิงก์อีกประเภทหนึ่ง – แบบสัมบูรณ์ 

คุณจะสร้างลิงก์แบบสัมบูรณ์ใน Excel ได้อย่างไร

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

1. คลิกที่ E2 และป้อนพิกัดของลิงค์ =D2 /D13. เนื่องจากลิงก์เป็นแบบสัมพัทธ์จึงต้องตั้งค่าสัญลักษณ์เพื่อแก้ไขข้อมูล
2. แก้ไขพิกัดของเซลล์ D ในการดำเนินการนี้ นำหน้าตัวอักษรที่ระบุคอลัมน์และหมายเลขแถวโดยวางเครื่องหมาย "$"

คำแนะนำจากผู้เชี่ยวชาญ! เพื่อความสะดวกในการป้อนก็เพียงพอที่จะเปิดใช้งานเซลล์เพื่อแก้ไขสูตรและกดปุ่ม F4 หลาย ๆ ครั้ง จนกว่าคุณจะได้ค่าที่น่าพอใจ สูตรที่ถูกต้องมีดังนี้: =D2/$D$13.

3. กดปุ่ม "เข้าสู่" จากการกระทำที่ดำเนินการแล้วผลลัพธ์ที่ถูกต้องควรปรากฏขึ้น
4. ลากเครื่องหมายไปที่บรรทัดล่างสุดเพื่อคัดลอกสูตร

เนื่องจากการใช้การระบุที่อยู่แบบสัมบูรณ์ในการคำนวณ ผลลัพธ์สุดท้ายในแถวที่เหลือจะถูกต้อง

วิธีใส่ลิงค์ผสมใน Excel

สำหรับการคำนวณสูตร ไม่เพียงแต่ใช้การอ้างอิงแบบสัมพัทธ์และแบบสัมบูรณ์เท่านั้น แต่ยังใช้การอ้างอิงแบบผสมด้วย คุณสมบัติที่โดดเด่นของพวกเขาคือแก้ไขพิกัดตัวใดตัวหนึ่ง

• ตัวอย่างเช่น ในการเปลี่ยนตำแหน่งของบรรทัด คุณต้องเขียนเครื่องหมาย $ หน้าการกำหนดตัวอักษร
• ในทางตรงกันข้าม หากเครื่องหมายดอลลาร์เขียนขึ้นหลังการกำหนดตัวอักษร ตัวบ่งชี้ในบรรทัดจะยังคงไม่เปลี่ยนแปลง

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

1. สำหรับการคำนวณที่แม่นยำ ให้ป้อน =D1/$D$3 และกด “เข้าสู่” โปรแกรมให้คำตอบที่แน่นอน
2. หากต้องการย้ายสูตรไปยังเซลล์ถัดไปในคอลัมน์และได้ผลลัพธ์ที่ถูกต้อง ให้ลากจุดจับไปที่เซลล์ด้านล่าง
3. เป็นผลให้โปรแกรมจะให้การคำนวณที่ถูกต้อง

โปรดทราบ! หากคุณตั้งค่าเครื่องหมาย $ ไว้หน้าตัวอักษร Excel จะแสดงข้อผิดพลาด “#DIV/0!” ซึ่งหมายความว่าการดำเนินการนี้ไม่สามารถทำได้

“SuperAbsolute” ที่อยู่

ในท้ายที่สุด มาดูตัวอย่างอื่นของลิงก์แบบสัมบูรณ์ – การระบุที่อยู่ “SuperAbsolute” คุณสมบัติและความแตกต่างของมันคืออะไร นำตัวเลขประมาณ 30 มาใส่ในเซลล์ B2 ตัวเลขนี้จะเป็นตัวเลขหลักซึ่งจำเป็นต้องดำเนินการหลายอย่างกับมันเช่นเพื่อเพิ่มพลัง

1. เพื่อให้การดำเนินการทั้งหมดถูกต้อง ให้ป้อนสูตรต่อไปนี้ในคอลัมน์ C: =$B$2^$D2. ในคอลัมน์ D เราป้อนค่าขององศา

2. หลังจากกดปุ่ม "Enter" และเปิดใช้งานสูตร เราจะขยายเครื่องหมายลงไปตามคอลัมน์
3. เราจะได้ผลลัพธ์ที่ถูกต้อง

บรรทัดล่างคือการดำเนินการทั้งหมดที่ดำเนินการอ้างถึงเซลล์คงที่หนึ่งเซลล์ B2 ดังนั้น:

• การคัดลอกสูตรจากเซลล์ C3 ไปยังเซลล์ E3, F3 หรือ H3 จะไม่เปลี่ยนแปลงผลลัพธ์ จะยังคงไม่เปลี่ยนแปลง – 900
• หากคุณต้องการแทรกคอลัมน์ใหม่ พิกัดของเซลล์ที่มีสูตรจะเปลี่ยนไป แต่ผลลัพธ์จะไม่เปลี่ยนแปลง

นี่คือลักษณะเฉพาะของลิงก์ "SuperAbsolute": หากคุณต้องการย้ายผลลัพธ์จะไม่เปลี่ยนแปลง อย่างไรก็ตาม มีบางสถานการณ์ที่ข้อมูลถูกแทรกจากแหล่งบุคคลที่สาม ดังนั้น คอลัมน์จะถูกเลื่อนไปด้านข้าง และข้อมูลจะถูกตั้งค่าแบบเก่าในคอลัมน์ B2 จะเกิดอะไรขึ้นในกรณีนี้? เมื่อผสมกัน สูตรจะเปลี่ยนไปตามการกระทำที่ทำ กล่าวคือจะไม่ชี้ไปที่ B2 อีกต่อไป แต่จะชี้ไปที่ C2 แต่เนื่องจากการแทรกใน B2 ผลลัพธ์สุดท้ายจะผิด

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

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

1. แทนที่จะใส่พิกัดของเซลล์ B2 ให้ป้อนตัวบ่งชี้ต่อไปนี้: =ทางอ้อม("B2") เป็นผลให้หลังจากย้ายองค์ประกอบการกำหนดจะมีลักษณะดังนี้: =ทางอ้อม(“B2”)^$E2.
2. ด้วยฟังก์ชันนี้ ลิงก์จะชี้ไปที่สี่เหลี่ยมจัตุรัสที่มีพิกัด B2 เสมอ ไม่ว่าจะเพิ่มหรือลบคอลัมน์ในตารางหรือไม่

ต้องเข้าใจว่าเซลล์ที่ไม่มีข้อมูลจะแสดงค่า "0" เสมอ

สรุป

ด้วยการใช้ลิงก์ที่อธิบายไว้สามแบบ มีโอกาสมากมายที่ช่วยให้ทำงานกับการคำนวณใน Excel ได้ง่ายขึ้น ดังนั้น ก่อนที่คุณจะเริ่มทำงานกับสูตร ให้อ่านลิงก์และกฎสำหรับการติดตั้งก่อน