เรียนรู้วิธีคัดลอกสูตรไปยังหลาย ๆ เซลล์ ลงคอลัมน์หนึ่งไปยังเซลล์ที่ไม่อยู่ติดกัน คัดลอกสูตรด้วยการอ้างอิงเซลล์แบบสัมบูรณ์หรือแบบผสม เป็นต้น
การคัดลอกสูตรเป็นงานทั่วไปและง่ายที่สุดงานหนึ่งที่คุณทำในสเปรดชีตทั่วไปที่อาศัยสูตรเป็นหลัก แทนที่จะพิมพ์สูตรเดียวกันซ้ำแล้วซ้ำอีกใน Excel คุณสามารถคัดลอกและวางสูตรจากเซลล์หนึ่งไปยังหลายเซลล์ได้อย่างง่ายดาย
หลังจากเขียนสูตรใน Excel แล้ว คุณสามารถใช้คำสั่งคัดลอกและวางลงในเซลล์หลายเซลล์ เซลล์ที่ไม่อยู่ติดกันหลายเซลล์ หรือทั้งคอลัมน์ได้ หากคุณทำไม่ถูกต้อง คุณจะจบลงด้วยข้อผิดพลาด # REF และ /DIV0 ที่น่ากลัว ในบทความนี้ เราจะแสดงวิธีการต่างๆ ที่คุณสามารถใช้เพื่อคัดลอกสูตรใน Excel
วิธีคัดลอกและวางสูตรใน Excel
Microsoft Excel มีวิธีต่างๆ ในการคัดลอกสูตรด้วยการอ้างอิงเซลล์แบบสัมพัทธ์ การอ้างอิงเซลล์แบบสัมบูรณ์ หรือการอ้างอิงแบบผสม
- คัดลอกสูตรจากเซลล์หนึ่งไปยังอีกเซลล์หนึ่ง
- คัดลอกสูตรหนึ่งเซลล์ไปยังหลาย ๆ เซลล์
- กำลังคัดลอกสูตรไปทั้งคอลัมน์
- คัดลอกสูตรโดยไม่ต้องจัดรูปแบบ
- คัดลอกสูตรไปยังเซลล์ที่ไม่อยู่ติดกัน
- คัดลอกสูตรโดยไม่เปลี่ยนการอ้างอิงเซลล์
วิธีการคัดลอกสูตรจากเซลล์หนึ่งไปยังอีกเซลล์หนึ่งใน Excel
ในบางครั้ง คุณอาจต้องการคัดลอกสูตรจากเซลล์หนึ่งไปยังอีกเซลล์หนึ่งใน excel เพื่อหลีกเลี่ยงการพิมพ์สูตรทั้งหมดซ้ำอีกครั้งและประหยัดเวลาขณะทำเช่นนั้น
สมมติว่าเรามีตารางนี้:
มีวิธีการสองสามวิธีในการคัดลอกสูตรจากเซลล์หนึ่งไปยังอีกเซลล์หนึ่ง
ขั้นแรก เลือกเซลล์ที่มีสูตรและคลิกขวา และในเมนูบริบท ให้เลือก 'คัดลอก' เพื่อคัดลอกสูตร หรือคุณสามารถใช้ตัวเลือก "คัดลอก" ในส่วน "คลิปบอร์ด" ของแท็บ "หน้าแรก"
แต่คุณยังคัดลอกสูตรด้วยการกดแป้นพิมพ์ลัด Ctrl + C. นี่เป็นวิธีการที่มีประสิทธิภาพและประหยัดเวลามากขึ้น
จากนั้นเราไปที่เซลล์ที่เราต้องการจะวาง กดทางลัด Ctrl + V เพื่อวางสูตร หรือคลิกขวาที่เซลล์ที่คุณต้องการวางแล้วคลิกตัวเลือกภายใต้ 'วางตัวเลือก': ตัวเลือก 'วาง (P)' แบบธรรมดาหรือวางเป็นตัวเลือก 'สูตร (F)'
หรือคุณสามารถคลิกที่ 'วางแบบพิเศษ' ใต้ไอคอนวางหกไอคอนเพื่อเปิดกล่องโต้ตอบ 'วางแบบพิเศษ' ที่นี่คุณมีหลายตัวเลือกรวมถึงหกตัวเลือกการวางจากเมนูบริบท เลือก 'ทั้งหมด' หรือ 'สูตร' ใต้ส่วน วาง แล้วคลิก 'ตกลง'
ตอนนี้เซลล์ที่มีสูตรที่วางควรมีสูตรเดียวกัน (เช่นเดียวกับในเซลล์ที่คัดลอก) แต่มีการอ้างอิงเซลล์ต่างกัน ที่อยู่ของเซลล์จะปรับตัวเองโดย excel เพื่อให้ตรงกับหมายเลขแถวของเซลล์ที่วาง
คัดลอกสูตรจากเซลล์หนึ่งไปยังหลายเซลล์
การวางแบบเดียวกันทำงานเหมือนกันถ้าเราเลือกหลายเซลล์หรือช่วงของเซลล์
เลือกเซลล์ที่มีสูตรแล้วกด Ctrl + C เพื่อคัดลอกสูตร จากนั้นเลือกเซลล์ทั้งหมดที่คุณต้องการวางสูตรแล้วกด Ctrl + V เพื่อวางสูตรหรือใช้วิธีการวางด้านบนเพื่อวางสูตร (เช่นเดียวกับที่เราทำในเซลล์เดียว)
คัดลอกสูตรไปยังทั้งคอลัมน์หรือแถว
ใน Excel คุณสามารถคัดลอกสูตรไปยังทั้งคอลัมน์หรือแถวได้อย่างรวดเร็ว
ในการคัดลอกสูตรไปยังคอลัมน์หรือแถว ก่อนอื่น ให้ป้อนสูตรในเซลล์ จากนั้นเลือกเซลล์สูตร (D1) แล้ววางเคอร์เซอร์ไว้เหนือสี่เหลี่ยมสีเขียวเล็กๆ ที่มุมล่างขวาของเซลล์ เมื่อคุณวางเคอร์เซอร์ไว้ เคอร์เซอร์จะเปลี่ยนเป็นเครื่องหมายบวกสีดำ (+) ซึ่งเรียกว่า Fill Handle คลิกที่จับเติมนั้นค้างไว้ แล้วลากไปในทิศทางใดก็ได้ที่คุณต้องการ (คอลัมน์หรือแถว) เหนือเซลล์เพื่อคัดลอกสูตร
เมื่อคุณคัดลอกสูตรไปยังช่วงของเซลล์ การอ้างอิงเซลล์ของสูตรจะปรับโดยอัตโนมัติตามตำแหน่งสัมพัทธ์ของแถวและคอลัมน์ และสูตรจะทำการคำนวณตามค่าในการอ้างอิงเซลล์เหล่านั้น (ดูด้านล่าง)
ในตัวอย่างข้างต้น เมื่อคัดลอกสูตรใน D1 (=A1*B1)/2) ไปยังเซลล์ D2 การอ้างอิงแบบสัมพัทธ์จะเปลี่ยนตามตำแหน่งของสูตร (=A2*B2)/2) เป็นต้น
ในทำนองเดียวกัน คุณสามารถลากสูตรไปยังเซลล์ที่อยู่ติดกันไปทางซ้าย ไปทางขวา หรือขึ้นด้านบนได้
อีกวิธีหนึ่งในการคัดลอกสูตรไปยังทั้งคอลัมน์คือการดับเบิลคลิกที่จุดจับเติมแทนที่จะลาก เมื่อคุณคลิกสองครั้งที่จุดจับเติม จะใช้สูตรทันทีที่มีข้อมูลในเซลล์ที่อยู่ติดกัน
คัดลอกสูตรไปยังช่วงโดยไม่ต้องคัดลอกการจัดรูปแบบ
เมื่อคุณคัดลอกสูตรไปยังช่วงของเซลล์ที่มีจุดจับเติม มันจะคัดลอกการจัดรูปแบบของเซลล์ต้นทางด้วย เช่น สีฟอนต์หรือสีพื้นหลัง สกุลเงิน เปอร์เซ็นต์ เวลา ฯลฯ (ดังที่แสดงด้านล่าง)
เพื่อป้องกันการคัดลอกการจัดรูปแบบเซลล์ ให้ลากที่จับเติมแล้วคลิก 'ตัวเลือกการเติมอัตโนมัติ' ที่มุมขวาล่างของเซลล์สุดท้าย จากนั้นในเมนูแบบเลื่อนลง ให้เลือก 'เติมโดยไม่ต้องจัดรูปแบบ'
ผลลัพธ์:
คัดลอกสูตร Excel ด้วยการจัดรูปแบบตัวเลขเท่านั้น
หากคุณต้องการคัดลอกสูตรที่มีเฉพาะสูตรและการจัดรูปแบบ เช่น รูปแบบเปอร์เซ็นต์ จุดทศนิยม เป็นต้น
คัดลอกสูตรและเลือกเซลล์ทั้งหมดที่คุณต้องการคัดลอกสูตร บนแท็บ "หน้าแรก" ให้คลิกลูกศรใต้ปุ่ม "วาง" บนริบบิ้น จากนั้นคลิกไอคอน 'สูตรและการจัดรูปแบบตัวเลข' (ไอคอนที่มี % fx) จากเมนูแบบเลื่อนลงเพื่อวางเฉพาะสูตรและการจัดรูปแบบตัวเลข
ตัวเลือกนี้จะคัดลอกเฉพาะการจัดรูปแบบสูตรและตัวเลข แต่จะละเว้นการจัดรูปแบบเซลล์อื่นๆ ทั้งหมด เช่น สีพื้นหลัง สีแบบอักษร ฯลฯ
คัดลอกสูตรไปยังเซลล์ที่ไม่อยู่ติดกัน/ไม่ต่อเนื่องกัน
หากคุณต้องการคัดลอกสูตรไปยังเซลล์ที่ไม่อยู่ติดกันหรือช่วงที่ไม่อยู่ติดกัน คุณสามารถทำได้โดยใช้ Ctrl กุญแจ.
เลือกเซลล์ที่มีสูตรแล้วกด Ctrl + C เพื่อคัดลอก จากนั้นเลือกเซลล์/ช่วงที่ไม่อยู่ติดกันขณะกด . ค้างไว้ Ctrl กุญแจ. จากนั้นกด Ctrl + V เพื่อวางสูตรแล้วกด เข้า ทำให้สมบูรณ์.
การคัดลอกสูตรโดยไม่เปลี่ยนการอ้างอิงเซลล์ใน Excel
เมื่อคัดลอกสูตรไปยังเซลล์อื่น Excel จะเปลี่ยนการอ้างอิงเซลล์โดยอัตโนมัติเพื่อให้ตรงกับตำแหน่งใหม่ การอ้างอิงเซลล์เหล่านี้ใช้ตำแหน่งสัมพัทธ์ของที่อยู่เซลล์ ดังนั้นจึงเรียกว่าการอ้างอิงเซลล์แบบสัมพัทธ์ (ไม่มี $) ตัวอย่างเช่น หากคุณมีสูตร '=A1*B1' ในเซลล์ C1 และคุณคัดลอกสูตรนี้ไปยังเซลล์ C2 สูตรจะเปลี่ยนเป็น '=A2*B2' วิธีการทั้งหมดที่เรากล่าวถึงข้างต้นใช้การอ้างอิงแบบสัมพัทธ์
เมื่อคุณคัดลอกสูตรที่มีการอ้างอิงเซลล์แบบสัมพัทธ์ สูตรจะเปลี่ยนการอ้างอิงโดยอัตโนมัติเพื่อให้สูตรอ้างอิงถึงแถวและคอลัมน์ที่เกี่ยวข้อง หากคุณใช้การอ้างอิงแบบสัมบูรณ์ในสูตร ระบบจะคัดลอกสูตรเดียวกันโดยไม่เปลี่ยนแปลงการอ้างอิงเซลล์
เมื่อคุณใส่เครื่องหมายดอลลาร์ ($) ไว้หน้าตัวอักษรของคอลัมน์และหมายเลขแถวของเซลล์ (เช่น $A$1) เซลล์จะเปลี่ยนเป็นเซลล์แบบสัมบูรณ์ ตอนนี้ไม่ว่าคุณจะคัดลอกสูตรที่มีการอ้างอิงเซลล์แบบสัมบูรณ์จากที่ใด สูตรจะไม่มีวันคัดลอก แต่ถ้าคุณมีการอ้างอิงเซลล์แบบสัมพัทธ์หรือแบบผสมในสูตร ให้ใช้วิธีใดวิธีหนึ่งต่อไปนี้เพื่อคัดลอกโดยไม่เปลี่ยนแปลงการอ้างอิงเซลล์
คัดลอกสูตรด้วยการอ้างอิงเซลล์แบบสัมบูรณ์โดยใช้วิธีการคัดลอกและวาง
ในบางครั้ง คุณอาจต้องคัดลอก/ใช้สูตรที่ถูกต้องลงในคอลัมน์ โดยไม่เปลี่ยนแปลงการอ้างอิงเซลล์ หากคุณต้องการคัดลอกหรือย้ายสูตรที่แน่นอนด้วยการอ้างอิงแบบสัมบูรณ์ ให้ทำดังนี้
ขั้นแรก ให้เลือกเซลล์ที่มีสูตรที่คุณต้องการคัดลอก จากนั้น คลิกที่แถบสูตร เลือกสูตรโดยใช้เมาส์ แล้วกด Ctrl + C เพื่อคัดลอก หากต้องการย้ายสูตร ให้กด Ctrl + X เพื่อตัดมัน ต่อไปกด เอสค คีย์เพื่อออกจากแถบสูตร
หรือเลือกเซลล์ที่มีสูตรแล้วกด F2 คีย์หรือดับเบิลคลิกที่เซลล์ ซึ่งจะทำให้เซลล์ที่เลือกเข้าสู่โหมดแก้ไข จากนั้นเลือกสูตรในเซลล์แล้วกด Ctrl + C เพื่อคัดลอกสูตรในเซลล์เป็นข้อความ
จากนั้นเลือกเซลล์ปลายทางแล้วกด Ctrl + V เพื่อวางสูตร
ตอนนี้สูตรที่แน่นอนจะถูกคัดลอกไปยังเซลล์ปลายทางโดยไม่มีการเปลี่ยนแปลงการอ้างอิงเซลล์
คัดลอกสูตรด้วยการอ้างอิงเซลล์แบบสัมบูรณ์หรือแบบผสม
ถ้าคุณต้องการย้ายหรือคัดลอกสูตรที่แน่นอนโดยไม่ต้องเปลี่ยนการอ้างอิงเซลล์ คุณควรเปลี่ยนการอ้างอิงแบบสัมพันธ์ของเซลล์เป็นการอ้างอิงแบบสัมบูรณ์ ตัวอย่างเช่น การเพิ่มเครื่องหมาย ($) ในการอ้างอิงเซลล์แบบสัมพัทธ์ (B1) ทำให้เป็นการอ้างอิงแบบสัมบูรณ์ ($B$1) ดังนั้นจึงยังคงเป็นแบบคงที่ไม่ว่าจะคัดลอกหรือย้ายสูตรไปที่ใด
แต่บางครั้ง คุณอาจต้องใช้การอ้างอิงเซลล์แบบผสม ($B1 หรือ B$1) โดยเพิ่มเครื่องหมายดอลลาร์ ($) ที่ด้านหน้าตัวอักษรของคอลัมน์หรือหมายเลขแถวเพื่อล็อคแถวหรือคอลัมน์ให้เข้าที่
ให้เราอธิบายด้วยตัวอย่าง สมมติว่าคุณมีตารางนี้ที่คำนวณเงินออมรายเดือนโดยลบค่าเช่า (B9) จากรายได้ (ในคอลัมน์ B) ทุกเดือน
ในตัวอย่างด้านล่าง สูตรนี้ใช้การอ้างอิงเซลล์แบบสัมบูรณ์ ($B$9) เพื่อล็อคจำนวนเงินที่เช่าไปยังเซลล์ B9 และการอ้างอิงเซลล์ที่สัมพันธ์กับเซลล์ B2 เนื่องจากจำเป็นต้องปรับสำหรับแต่ละแถวเพื่อให้ตรงกันในแต่ละเดือน B9 เป็นการอ้างอิงเซลล์แบบสัมบูรณ์ ($B$9) เนื่องจากคุณต้องการลบจำนวนค่าเช่าเดียวกันออกจากรายได้ของแต่ละเดือน
สมมติว่าคุณต้องการย้ายยอดดุลจากคอลัมน์ C ไปยังคอลัมน์ E หากคุณคัดลอกสูตร (โดยวิธีการคัดลอก/วางปกติ) จากเซลล์ C2 (=B2-$B$9) จะเปลี่ยนเป็น =D2-$B$9 เมื่อวาง ในเซลล์ E2 ทำให้การคำนวณของคุณผิดทั้งหมด!
ในกรณีนั้น ให้เปลี่ยนการอ้างอิงเซลล์แบบสัมพัทธ์ (B2) เป็นการอ้างอิงเซลล์แบบผสม ($B2) โดยเพิ่มเครื่องหมาย '$' ที่ด้านหน้าตัวอักษรประจำคอลัมน์ของสูตรที่ป้อนในเซลล์ C2
และตอนนี้ ถ้าคุณคัดลอกหรือย้ายสูตรจากเซลล์ C2 ไปยัง E2 หรือเซลล์อื่นๆ และนำสูตรไปใช้กับคอลัมน์ การอ้างอิงคอลัมน์จะยังคงเหมือนเดิมในขณะที่หมายเลขแถวจะถูกปรับสำหรับแต่ละเซลล์
คัดลอกวางสูตร Excel โดยไม่ต้องเปลี่ยนข้อมูลอ้างอิงโดยใช้ Notepad
คุณสามารถดูทุกสูตรในสเปรดชีต Excel ของคุณได้โดยใช้ตัวเลือกแสดงสูตร โดยไปที่แท็บ "สูตร" และเลือก "แสดงสูตร"
หรือคุณสามารถเข้าสู่โหมดดูสูตรโดยกด Ctrl + ` ทางลัด ซึ่งแสดงทุกสูตรในเวิร์กชีตของคุณ คุณจะพบปุ่มเน้นเสียง (`) ที่มุมซ้ายบนของแป้นพิมพ์ในแถวที่มีปุ่มตัวเลข (ด้านล่าง ESC คีย์และก่อนคีย์หมายเลข 1)
เลือกเซลล์ทั้งหมดที่มีสูตรที่คุณต้องการคัดลอกแล้วกด Ctrl + C เพื่อคัดลอกหรือ Ctrl + X เพื่อตัดพวกเขา จากนั้นเปิด Notepad แล้วกด Ctrl + V เพื่อวางสูตรในแผ่นจดบันทึก
จากนั้นเลือกสูตรและคัดลอก (Ctrl + C) จากแผ่นจดบันทึก และวาง (Ctrl + V) ในเซลล์ที่คุณต้องการคัดลอกสูตรที่แน่นอน คุณสามารถคัดลอกและวางทีละรายการหรือทั้งหมดในครั้งเดียว
หลังจากวางสูตรแล้ว ให้ปิดโหมดดูสูตรโดยกด Ctrl + ` หรือไปที่ 'สูตร' -> 'แสดงสูตร' อีกครั้ง
คัดลอกสูตรที่แน่นอนโดยใช้การค้นหาและแทนที่ของ Excel
ถ้าคุณต้องการคัดลอกช่วงของสูตรที่แน่นอน คุณสามารถใช้เครื่องมือค้นหาและแทนที่ของ Excel เพื่อทำเช่นนั้นได้
เลือกเซลล์ทั้งหมดที่มีสูตรที่คุณต้องการคัดลอก จากนั้นไปที่แท็บ 'หน้าแรก' คลิก 'ค้นหาและเลือก' ในกลุ่มการแก้ไข แล้วเลือกตัวเลือก 'แทนที่' หรือเพียงแค่กด Ctrl + H เพื่อเปิดกล่องโต้ตอบ ค้นหาและแทนที่
ในกล่องโต้ตอบ ค้นหาและแทนที่ ให้ป้อนเครื่องหมายเท่ากับ (=) ในช่อง "ค้นหาอะไร" ในฟิลด์ 'แทนที่ด้วย' ให้ป้อนสัญลักษณ์หรืออักขระที่ยังไม่ได้เป็นส่วนหนึ่งของสูตรของคุณ เช่น # หรือ ฯลฯ จากนั้นคลิกปุ่ม 'แทนที่ทั้งหมด'
คุณจะได้รับกล่องข้อความแจ้งว่า 'เราทำการเปลี่ยน 6 ครั้ง' (เพราะเราเลือก 6 เซลล์ที่มีสูตร) จากนั้นคลิก 'ตกลง' และ 'ปิด' เพื่อปิดกล่องโต้ตอบทั้งสอง การทำเช่นนี้จะแทนที่เครื่องหมายเท่ากับ (=) ทั้งหมดด้วยเครื่องหมายแฮช (#) และเปลี่ยนสูตรเป็นสตริงข้อความ ตอนนี้การอ้างอิงเซลล์ของสูตรจะไม่เปลี่ยนแปลงเมื่อคัดลอก
ตอนนี้คุณสามารถเลือกเซลล์เหล่านี้ได้ กด Ctrl + C เพื่อคัดลอกและวางลงในเซลล์ปลายทางด้วย Ctrl + V.
สุดท้าย คุณต้องเปลี่ยนเครื่องหมาย (#) กลับเป็น (=) เครื่องหมาย โดยเลือกทั้งสองช่วง (ช่วงเดิมและช่วงที่คัดลอก) แล้วกด Ctrl + H เพื่อเปิดกล่องโต้ตอบ ค้นหาและแทนที่ คราวนี้ พิมพ์เครื่องหมายแฮช (#) ในช่อง "ค้นหาอะไร" และเท่ากับ (=) ลงชื่อเข้าใช้ในช่อง "แทนที่ด้วย" แล้วคลิกปุ่ม "แทนที่ทั้งหมด" คลิก 'ปิด' เพื่อปิดกล่องโต้ตอบ
ตอนนี้ สตริงข้อความจะถูกแปลงกลับไปเป็นสูตร และคุณจะได้ผลลัพธ์นี้:
เสร็จแล้ว!