คัดลอกสูตรโดยไม่ต้องเปลี่ยนลิงค์

ปัญหา

สมมติว่าเรามีตารางง่ายๆ แบบนี้ ซึ่งมีการคำนวณจำนวนเงินสำหรับแต่ละเดือนในสองเมือง จากนั้นยอดรวมจะถูกแปลงเป็นยูโรในอัตราจากเซลล์สีเหลือง J2

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

ภารกิจ: คัดลอกช่วงด้วยสูตรเพื่อไม่ให้สูตรเปลี่ยนแปลงและยังคงเหมือนเดิม โดยคงผลการคำนวณไว้

วิธีที่ 1 ลิงก์แอบโซลูท

วิธีที่ 2: ปิดใช้งานสูตรชั่วคราว

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

1. เลือกช่วงที่มีสูตร (ในตัวอย่างของเรา D2:D8)
2. คลิก Ctrl + H บนแป้นพิมพ์หรือบนแท็บ หน้าแรก – ค้นหาและเลือก – แทนที่ (หน้าแรก — ค้นหา&เลือก — แทนที่)

   

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

   

5. แทนที่ # on = กลับมาใช้หน้าต่างเดิม คืนค่าฟังก์ชันการทำงานเป็นสูตร

วิธีที่ 3: คัดลอกผ่าน Notepad

วิธีนี้เร็วกว่าและง่ายกว่ามาก

กดแป้นพิมพ์ลัด Ctrl+Ё หรือปุ่ม แสดงสูตร แถบ สูตร (สูตร — แสดงสูตร), ในการเปิดโหมดตรวจสอบสูตร – แทนที่จะแสดงผลลัพธ์ เซลล์จะแสดงสูตรที่ใช้คำนวณ:

คัดลอกช่วง D2:D8 ของเราแล้ววางลงในมาตรฐาน สมุดบันทึก:

ตอนนี้เลือกทุกอย่างที่วาง (Ctrl + A) คัดลอกไปยังคลิปบอร์ดอีกครั้ง (Ctrl + C) แล้ววางลงบนแผ่นงานในตำแหน่งที่คุณต้องการ:

เหลือเพียงการกดปุ่ม แสดงสูตร (แสดงสูตร)เพื่อให้ Excel กลับสู่โหมดปกติ

หมายเหตุ: บางครั้งวิธีนี้ใช้ไม่ได้ผลในตารางที่ซับซ้อนที่มีเซลล์ที่ผสาน แต่ในกรณีส่วนใหญ่ วิธีนี้ใช้ได้ผลดี

วิธีที่ 4 มาโคร

ถ้าคุณต้องทำการคัดลอกสูตรบ่อยๆ โดยไม่ต้องเปลี่ยนข้อมูลอ้างอิง ก็ควรใช้มาโครสำหรับสิ่งนี้ กดแป้นพิมพ์ลัด Alt + F11 หรือปุ่ม ของ Visual Basic แถบ ผู้พัฒนา (ผู้พัฒนา), แทรกโมดูลใหม่ผ่านเมนู แทรก – โมดูล  และคัดลอกข้อความของมาโครนี้ที่นั่น:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Select cells with formulas to copy.", _ "Copy สูตรทั้งหมด", Default:=Selection.Address, Type := 8) ถ้า copyRange ไม่มีอะไร จากนั้นออกจาก Sub Set pasteRange = Application.InputBox("ตอนนี้เลือกช่วงการวาง" & vbCrLf & vbCrLf & _ "ช่วงต้องมีขนาดเท่ากับช่วงของเซลล์ต้นฉบับ " & vbCrLf & _ " เพื่อคัดลอก" , "คัดลอกสูตรทุกประการ", _ Default:=Selection.Address, Type:=8) ถ้า pasteRange.Cells.Count <> copyRange.Cells.Count จากนั้น MsgBox "คัดลอกและวางช่วงที่มีขนาดแตกต่างกัน!", vb อัศเจรีย์ "คัดลอกข้อผิดพลาด" ออกจาก Sub End ถ้า PasteRange ไม่มีอะไร จากนั้นออกจาก Sub อื่น ๆ pasteRange.Formula = copyRange.Formula End ถ้า End Sub

คุณสามารถใช้ปุ่มเพื่อเรียกใช้แมโคร แมโคร แถบ ผู้พัฒนา (ผู้พัฒนา — มาโคร) หรือแป้นพิมพ์ลัด Alt + F8. หลังจากเรียกใช้มาโคร ระบบจะขอให้คุณเลือกช่วงที่มีสูตรดั้งเดิมและช่วงการแทรก และจะคัดลอกสูตรโดยอัตโนมัติ:

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