วิธีตรึงเซลล์ในสูตร Excel

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

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

ลิงค์ Excel คืออะไร

แผ่นงานประกอบด้วยเซลล์ แต่ละรายการมีข้อมูลเฉพาะ เซลล์อื่นสามารถใช้ในการคำนวณได้ แต่พวกเขาเข้าใจได้อย่างไรว่าข้อมูลมาจากไหน? ช่วยให้พวกเขาสร้างลิงก์

แต่ละลิงก์กำหนดเซลล์ด้วยตัวอักษรหนึ่งตัวและตัวเลขหนึ่งตัว ตัวอักษรแสดงถึงคอลัมน์และตัวเลขแสดงถึงแถว 

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

วิธีที่ 1

หากต้องการบันทึกทั้งที่อยู่ของคอลัมน์และแถว ให้ทำตามขั้นตอนเหล่านี้:

1. คลิกที่เซลล์ที่มีสูตร
2. คลิกที่แถบสูตรสำหรับเซลล์ที่เราต้องการ
3. กด F4

ด้วยเหตุนี้ การอ้างอิงเซลล์จะเปลี่ยนเป็นแบบสัมบูรณ์ สามารถรับรู้ได้จากสัญลักษณ์ดอลลาร์ ตัวอย่างเช่น หากคุณคลิกที่เซลล์ B2 แล้วคลิกที่ F4 ลิงก์จะมีลักษณะดังนี้: $B$2

เครื่องหมายดอลลาร์หมายถึงอะไรก่อนส่วนของที่อยู่ต่อเซลล์

1. หากวางไว้หน้าตัวอักษร แสดงว่าการอ้างอิงคอลัมน์ยังคงเหมือนเดิม ไม่ว่าสูตรจะย้ายไปอยู่ที่ใด
2. หากเครื่องหมายดอลลาร์อยู่ข้างหน้าตัวเลข แสดงว่าสายนั้นถูกตรึงไว้ 

วิธีที่ 2

วิธีนี้เกือบจะเหมือนกับวิธีก่อนหน้านี้ เพียงคุณกด F4 สองครั้ง ตัวอย่างเช่น ถ้าเรามีเซลล์ B2 หลังจากนั้นก็จะกลายเป็น B$2 พูดง่ายๆ ด้วยวิธีนี้ เราจัดการแก้ไขบรรทัดได้ ในกรณีนี้ ตัวอักษรของคอลัมน์จะเปลี่ยนไป

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

วิธีที่ 3

นี่เป็นวิธีเดียวกับวิธีก่อนหน้าทั้งหมด เพียงคุณกดแป้น F4 สามครั้ง เฉพาะการอ้างอิงคอลัมน์เท่านั้นที่จะเป็นค่าสัมบูรณ์ และแถวจะคงที่

วิธีที่ 4

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

การตรึงเซลล์สำหรับช่วงขนาดใหญ่

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

น่าเสียดายที่วิธีการมาตรฐานของ Excel จะไม่บรรลุเป้าหมายนี้ ในการทำเช่นนี้ คุณต้องใช้ส่วนเสริมพิเศษที่เรียกว่า VBA-Excel มันมีฟีเจอร์เพิ่มเติมมากมายที่ช่วยให้คุณทำงานทั่วไปกับ Excel ได้เร็วกว่ามาก

ประกอบด้วยฟังก์ชันที่ผู้ใช้กำหนดเองมากกว่าร้อยรายการและมาโครต่างๆ กว่า 25 รายการ และยังมีการอัปเดตเป็นประจำอีกด้วย ช่วยให้คุณสามารถปรับปรุงการทำงานได้เกือบทุกด้าน:

1. เซลล์.
2. มาโคร
3. หน้าที่ของประเภทต่างๆ
4. ลิงค์และอาร์เรย์

โดยเฉพาะอย่างยิ่ง Add-in นี้ช่วยให้คุณสามารถแก้ไขลิงก์ในสูตรจำนวนมากได้ในคราวเดียว คุณต้องทำตามขั้นตอนต่อไปนี้:

1. เลือกช่วง
2. เปิดแท็บ VBA-Excel ที่จะปรากฏขึ้นหลังการติดตั้ง 
3. เปิดเมนู "ฟังก์ชัน" ซึ่งมีตัวเลือก "ล็อกสูตร"
   

   

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

ตัวอย่าง

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

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

แน่นอน ในตัวอย่างนี้ คุณสามารถลองลากสูตรลงโดยใช้เครื่องหมายป้อนอัตโนมัติ แต่ในกรณีนี้ เซลล์จะเปลี่ยนไปโดยอัตโนมัติ ดังนั้นในเซลล์ D3 จะมีสูตรอื่น โดยที่ตัวเลขจะถูกแทนที่ตามลำดับด้วย 3 นอกจากนี้ ตามรูปแบบ – D4 – สูตรจะใช้รูปแบบ = B4 * C4, D5 – ในทำนองเดียวกัน แต่ด้วย หมายเลข 5 และอื่นๆ

หากจำเป็น (โดยส่วนใหญ่จะปรากฎ) ก็ไม่มีปัญหา แต่ถ้าคุณต้องการแก้ไขสูตรในเซลล์เดียวเพื่อไม่ให้เปลี่ยนแปลงเมื่อลาก การดำเนินการนี้จะค่อนข้างยากขึ้น 

สมมติว่าเราจำเป็นต้องกำหนดรายได้ดอลลาร์ ลองใส่ไว้ในเซลล์ B7 มาดูความคิดถึงกันเล็กน้อยและระบุราคา 35 รูเบิลต่อดอลลาร์ ดังนั้น เพื่อกำหนดรายได้เป็นดอลลาร์ จึงจำเป็นต้องแบ่งจำนวนเงินเป็นรูเบิลด้วยอัตราแลกเปลี่ยนเงินดอลลาร์

นี่คือสิ่งที่ดูเหมือนในตัวอย่างของเรา

หากเราพยายามกำหนดสูตรเช่นเดียวกับรุ่นก่อนหน้าเราจะล้มเหลว ในทำนองเดียวกันสูตรจะเปลี่ยนเป็นสูตรที่เหมาะสม ในตัวอย่างของเรา มันจะเป็นดังนี้: =E3*B8. จากที่นี่เราจะเห็น ว่าส่วนแรกของสูตรกลายเป็น E3 และเราตั้งค่างานนี้เอง แต่เราไม่จำเป็นต้องเปลี่ยนส่วนที่สองของสูตรเป็น B8 ดังนั้น เราจำเป็นต้องเปลี่ยนการอ้างอิงให้เป็นแบบสัมบูรณ์ คุณสามารถทำได้โดยไม่ต้องกดปุ่ม F4 เพียงแค่ใส่เครื่องหมายดอลลาร์

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

สูตรจะมีลักษณะดังนี้:

=D2/$B$7

โปรดทราบ! เราได้ระบุสองสัญญาณดอลลาร์ ด้วยวิธีนี้ เราจะแสดงโปรแกรมที่จำเป็นต้องแก้ไขทั้งคอลัมน์และแถว

การอ้างอิงเซลล์ในมาโคร

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

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

ดังนั้น เซลล์จึงเป็นอ็อบเจ็กต์ Cells ประกอบด้วยเซลล์ทั้งหมดของชีตหนึ่งๆ

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

ตัวอย่างเช่น บรรทัดโค้ดที่มีการอ้างอิงไปยังเซลล์ C5 จะมีลักษณะดังนี้:

สมุดงาน("Book2.xlsm").แผ่นงาน("List2").เซลล์(5, 3)

สมุดงาน("Book2.xlsm").แผ่นงาน("List2").เซลล์(5, "C")

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

ในกรณีนี้ เส้นจะมีลักษณะดังนี้

สมุดงาน("Book2.xlsm").ชีต("List2").ช่วง("C5")

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

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

สรุป

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