วิธี copy สูตร excel ข้ามไฟล์

ฟอรัมถาม-ตอบปัญหาการใช้งาน MS Excel and VBA

Forum rules

1. ไม่อนุญาตให้ใช้ภาษาแชทในการถามและตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
2. ห้ามถามโดยระบุชื่อผู้ตอบและต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
3. กรุณาอธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
4. กรุณาแนบไฟล์ตัวอย่างพร้อมแสดงคำตอบที่ถูกต้องมาในไฟล์ด้วยเพื่อให้ง่ายต่อการทำความเข้าใจและสะดวกต่อการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่นที่อาจจะถูกลบทิ้งไปโดยต้นทางในภายหลัง นอกจากนี้ไม่ควรแนบไฟล์ที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
5. กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน กรุณาโพสต์ Code ให้แสดงเป็น Code คือเปิดด้วย [code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
6. กรุณาแจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่

patsacholMember

ต้องการ Copy สูตร ข้าม ไฟล์ แบบ สัมพัทธ์ ค่ะ

Post by patsachol » Thu Oct 07, 2010 4:46 pm

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

You do not have the required permissions to view the files attached to this post.

Re: ต้องการ Copy สูตร ข้าม ไฟล์ แบบ สัมพัทธ์ ค่ะ

#2

Post by snasui » Thu Oct 07, 2010 6:28 pm

You do not have the required permissions to view the files attached to this post.

ใน Excel คุณ copy สูตร (formula) ไปใส่ทีเดียวทั้งแถวหรือคอลัมน์ได้เลย แต่บางทีก็ไม่ได้ผลอย่างที่คาด ถ้าผลลัพธ์ผิด หรือเจอ error อย่าง #REF และ /DIV0 ก็อย่าเพิ่งหงุดหงิดไป เพราะไม่ต้องมานั่งแก้ spreadsheet ขนาด 5,000 บรรทัดทีละเซลล์อย่างที่คิด บทความวิกิฮาวนี้จะแนะนำวิธีการ copy สูตรไปไว้เซลล์อื่นแบบง่ายๆ ให้คุณเอง

1. 

   1

   เปิด workbook ใน Excel. บางทีคุณก็มี spreadsheet ขนาดใหญ่ มีสูตรต่างๆ เต็มไปหมด แล้วยังอยาก copy สูตรทั้งหมด แบบเป๊ะๆ ถ้าต้องมานั่งเปลี่ยนทุกอย่างโดยอ้างอิงแบบสัมบูรณ์ (absolute cell references) คงเหนื่อยน่าดู โดยเฉพาะถ้าเดียวต้องเปลี่ยนกลับทีหลัง แค่ทำตามวิธีการที่บทความวิกิฮาวนี้แนะนำ ก็ย้ายสูตรโดยอ้างอิงแบบสัมพันธ์ (relative cell references) ไปตรงไหนก็ได้ ไม่ต้องเปลี่ยนการอ้างอิงเซลล์[1] ตัวอย่าง spreadsheet ในบทความวิกิฮาวนี้ จะเป็นการ copy สูตรจากคอลัมน์ C ไปใส่คอลัมน์ D โดยไม่เปลี่ยนแปลงอะไรเลย

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C	คอลัมน์ D
   แถว 1	944	กบ	=A1/2
   แถว 2	636	คางคก	=A2/2
   แถว 3	712	จิ้งเหลน	=A3/2
   แถว 4	690	งู	=A4/2

   • ถ้าแค่จะ copy สูตรไปใส่เซลล์เดียว ก็ข้ามไปขั้นตอนสุดท้าย ("ลองใช้วิธีทางเลือก") ของวิธีการนี้ได้เลย

2. 

   2

   กด Ctrl+H เพื่อเปิดหน้าต่าง Find. shortcut นี้ใช้ได้ทั้งใน Windows และ macOS

3. 

   3

   ค้นหาแล้วเปลี่ยน "=" เป็นอักขระอื่น. พิมพ์ "=" ในช่อง "Find what" แล้วพิมพ์อักขระอื่นในช่อง "Replace with" คลิก Replace All เพื่อเปลี่ยนสูตรทั้งหมด (ที่ปกติจะขึ้นต้นด้วยเครื่องหมายเท่ากับ) ไปเป็นสตริงข้อความ ที่เริ่มต้นด้วยอักขระอื่น ให้ใช้อักขระอื่นที่ไม่ได้ใช้ใน spreadsheet เช่น เปลี่ยนเป็น # หรือ & หรือสตริงข้อความที่ยาวขึ้น เช่น ##&

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C	คอลัมน์ D
   แถว 1	944	กบ	##&A1/2
   แถว 2	636	คางคก	##&A2/2
   แถว 3	712	จิ้งเหลน	##&A3/2
   แถว 4	690	งู	##&A4/2

   • ห้ามใช้อักขระอย่าง * หรือ ? เพราะจะทำให้ยุ่งยากในขั้นตอนหลังๆ

4. 

   4

   copy แล้ว paste เซลล์. เลือกเซลล์ที่จะ copy จากนั้นกด Ctrl + C (PC) หรือ Cmd + C (Mac) เพื่อ copy จากนั้นเลือกเซลล์ที่จะ paste ไป แล้วกด Ctrl + V (PC) หรือ Cmd + V (Mac) เพื่อ paste พอไม่ถูกมองเป็นสูตรแล้ว ก็จะ copy ออกมาได้เป๊ะๆ

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C	คอลัมน์ D
   แถว 1	944	กบ	##&A1/2	##&A1/2
   แถว 2	636	คางคก	##&A2/2	##&A2/2
   แถว 3	712	จิ้งเหลน	##&A3/2	##&A3/2
   แถว 4	690	งู	##&A4/2	##&A4/2

5. 

   5

   ใช้ Find & Replace อีกรอบ เพื่อเปลี่ยนทุกอย่างกลับเป็นเหมือนเดิม. พอได้ทุกสูตรที่ต้องการแล้ว ให้ใช้ "Replace All" อีกรอบ เพื่อคืนค่าเดิม ในตัวอย่างนี้คือให้ค้นหาสตริงอักขระ "##&" แล้วเปลี่ยนเป็น "=" ตามเดิม เพื่อให้เซลล์พวกนี้กลับมาเป็นสูตรอีกครั้ง เท่านี้ก็แก้ไข spreadsheet ได้ตามปกติ

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C	คอลัมน์ D
   แถว 1	944	กบ	=A1/2	=A1/2
   แถว 2	636	คางคก	=A2/2	=A2/2
   แถว 3	712	จิ้งเหลน	=A3/2	=A3/2
   แถว 4	690	งู	=A4/2	=A4/2

6. 

   6

   ลองใช้วิธีทางเลือก. ถ้าวิธีการที่อธิบายไปด้านบนใช้ไม่ได้ผล หรือกลัวเผลอเปลี่ยนเนื้อหาของเซลล์อื่นด้วยตัวเลือก "Replace all" ก็ยังมีอีก 1 - 2 วิธีให้ได้ลอง

   • คุณ copy สูตรจากเซลล์เดียวได้แบบไม่ต้องเปลี่ยนการอ้างอิง ให้เลือกเซลล์ แล้ว copy สูตรที่ขึ้นในแถบสูตรทางด้านบนของหน้าต่าง (ไม่ใช่ในเซลล์) กด Esc เพื่อปิดแถบสูตร แล้ว paste สูตรตรงที่ต้องการได้เลย
   • กด Ctrl และ ` (อยู่คีย์เดียวกับ ~) เพื่อเปิดโหมดดูสูตรใน spreadsheet จากนั้น copy สูตรไป paste ใน text editor อย่าง Notepad หรือ TextEdit เสร็จแล้ว copy อีกรอบ ไป paste ใน spreadsheet ตรงตำแหน่งที่ต้องการ สุดท้ายกด Ctrl และ ` อีกรอบ เพื่อสลับกลับไปเป็นโหมดปกติ

   โฆษณา

1. 

   1

   พิมพ์สูตรในเซลล์ว่าง. Excel ให้คุณกระจายสูตรไปตามคอลัมน์ หรือตามแถวได้โดย "filling" หรือเติมข้อมูลในเซลล์ ก็เหมือนทุกสูตรที่ขึ้นต้นด้วยเครื่องหมาย = แล้วใช้ฟังก์ชั่นหรือเลขคณิตไหนก็ได้ตามต้องการ ต่อไปนี้เป็นตัวอย่าง spreadsheet แบบง่ายๆ โดยจะเพิ่มคอลัมน์ A กับ B เข้าด้วยกัน ให้กด Enter หรือ Return เพื่อคำนวณสูตร

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
   แถว 1	10	9	19
   แถว 2	20	8
   แถว 3	30	7
   แถว 4	40	6

2. 

   2

   คลิกมุมขวาล่างของเซลล์ที่มีสูตรที่จะ copy. เคอร์เซอร์จะกลายเป็นเครื่องหมาย + ตัวหนา

3. 

   3

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

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
   แถว 1	10	9	=A1+B1
   แถว 2	20	8	=A2+B2
   แถว 3	30	7	=A3+B3
   แถว 4	40	6	=A4+B4

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
   แถว 1	10	9	19
   แถว 2	20	8	28
   แถว 3	30	7	37
   แถว 4	40	6	46

   • จะดับเบิลคลิกเครื่องหมายบวกก็ได้ เพื่อใส่สูตรในทั้งคอลัมน์ แทนการลากคลุม โดย Excel จะหยุดใส่สูตรในคอลัมน์ ถ้าเจอเซลล์ว่าง ถ้าข้อมูลอ้างอิงมีช่องว่าง ต้องทำขั้นตอนนี้ซ้ำ เพื่อใส่สูตรในคอลัมน์ล่างช่องว่าง
   • อีกวิธีใส่สูตรเดียวทั้งคอลัมน์ คือเลือกเซลล์ด้านล่าง ที่ติดกับเซลล์ที่มีสูตร แล้วกด Ctrl + D[2]

   โฆษณา

1. 

   1

   พิมพ์สูตรในเซลล์เดียว. ก็เหมือนทุกสูตร คือเริ่มด้วยเครื่องหมาย = แล้วใช้ฟังก์ชั่นหรือเลขคณิตไหนก็ได้ตามต้องการ ต่อไปนี้เป็นตัวอย่าง spreadsheet แบบง่ายๆ โดยจะเพิ่มคอลัมน์ A กับ B เข้าด้วยกัน ให้กด Enter หรือ Return เพื่อคำนวณสูตร

   ตัวอย่าง Spreadsheet

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
   แถว 1	10	9	19
   แถว 2	20	8
   แถว 3	30	7
   แถว 4	40	6

2. 

   2

   เลือกเซลล์แล้วกด Ctrl+C (PC) หรือ ⌘ Command+C (Mac). เพื่อ copy สูตรลงใน clipboard

3. 

   3

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

4. 

   4

   กด Ctrl+V (PC) หรือ ⌘ Command+V (Mac) เพื่อ paste. สูตรจะโผล่มาในเซลล์ที่เลือก

   โฆษณา

1. 

   1

   ใช้การอ้างอิงเซลล์แบบสัมพันธ์ในสูตร. ในสูตร Excel ตัว "cell reference" หรือการอ้างอิงเซลล์ ก็คือที่อยู่ของเซลล์นั้น จะพิมพ์เองก็ได้ หรือคลิกเซลล์ที่จะใช้ตอนพิมพ์สูตร เช่น spreadsheet ต่อไปนี้ มีสูตรที่อ้างอิงถึงเซลล์ A2

   อ้างอิงแบบสัมพันธ์

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
   แถว 2	50	7	=A2*2
   แถว 3	100
   แถว 4	200
   แถว 5	400

2. 

   2

   ทำไมถึงเรียก relative references. ในสูตร Excel การอ้างอิงแบบสัมพันธ์ จะใช้ตำแหน่งที่เกี่ยวข้องของที่อยู่เซลล์ ในตัวอย่างนี้ C2 มีสูตร “=A2” ที่เป็นการอ้างอิงแบบสัมพันธ์กับค่าที่ห่างไปทางซ้าย 2 เซลล์ ถ้า copy สูตรไปใส่ใน C4 ก็จะยังอ้างถึง 2 เซลล์ทางซ้าย ที่ตอนนี้ขึ้นว่า “=A4”

   การอ้างอิงสัมพันธ์

   	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
   แถว 2	50	7	=A2*2
   แถว 3	100
   แถว 4	200		=A4*2
   แถว 5	400

   • ใช้กับเซลล์นอกแถวหรือคอลัมน์เดียวกันได้ด้วย ถ้า copy สูตรเดียวกันจากเซลล์ C1 ไปใส่ในเซลล์ D6 (ไม่แสดง) Excel จะเปลี่ยนการอ้างอิง "A2" ไปยังเซลล์ 1 แถวทางขวา (C→D) และ 5 แถวด้านล่าง (2→7) หรือ "B7"

3. 

   3

   ใช้การอ้างอิงแบบสัมบูรณ์แทน. อย่างถ้าคุณ ไม่อยาก ให้ Excel เปลี่ยนสูตรอัตโนมัติ แทนที่จะใช้การอ้างอิงเซลล์แบบสัมพันธ์ ก็ให้อ้างอิงแบบ สัมบูรณ์ โดยใส่สัญลักษณ์ $ หน้าคอลัมน์หรือแถวที่จะเก็บไว้เหมือนเดิม ไม่ว่าจะ copy สูตรไปไว้ที่ไหน[3] ต่อไปนี้เป็นตัวอย่าง 2 - 3 spreadsheet ที่แสดงสูตรเดิมเป็นข้อความขนาดใหญ่ ตัวหนา รวมถึงแสดงผลลัพธ์หลัง copy-paste สูตรใส่เซลล์อื่นแล้ว

   • คอลัมน์สัมพันธ์ แถวสัมบูรณ์ (B$1): สูตรจะอ้างอิงแบบสัมบูรณ์กับแถว 1 เพราะฉะนั้นจะอ้างอิงถึงแถว 1 เสมอ

     	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
     แถว 1	50	7	=B$3
     แถว 2	100	=A$3	=B$3
     แถว 3	200	=A$3	=B$3
     แถว 4	400	=A$3	=B$3

     
     
   • คอลัมน์สัมบูรณ์ แถวสัมพันธ์ ($B1): สูตรจะอ้างอิงแบบสัมบูรณ์กับคอลัมน์ B เพราะฉะนั้นจะอ้างอิงถึงคอลัมน์ B

     	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
     แถว1	50	7	=$B1
     แถว 2	100	=$B2	=$B2
     แถว 3	200	=$B3	=$B3
     แถว 4	400	=$B4	=$B4

     
     
   • คอลัมน์และแถวสัมบูรณ์ ($B$1): สูตรจะอ้างอิงแบบสัมพันธ์กับคอลัมน์ B ของแถว 1 เพราะฉะนั้นจะอ้างอิงถึงคอลัมน์ B ของแถว 1 เสมอ

     	คอลัมน์ A	คอลัมน์ B	คอลัมน์ C
     แถว 1	50	7	=$B$1
     แถว 2	100	$B$1	$B$1
     แถว 3	200	$B$1	$B$1
     แถว 4	400	$B$1	$B$1

     
     

4. 

   4

   กดปุ่ม F4 เพื่อสลับไปมาระหว่างสัมบูรณ์กับสัมพันธ์. เลือกการอ้างอิงเซลล์ในสูตรโดยคลิก จากนั้นกด F4 เพื่อเพิ่มหรือลบเครื่องหมาย $ โดยอัตโนมัติ ให้กด F4 ไปเรื่อยๆ จนเลือกการอ้างอิงสัมบูรณ์หรือสัมพันธ์ตามต้องการ จากนั้นกด Enter หรือ Return

   โฆษณา

เคล็ดลับ

• ถ้า copy สูตรลงเซลล์ใหม่ แล้วมีสามเหลี่ยมสีเขียว แสดงว่า Excel สแกนเจอ error ให้เช็คสูตรดีๆ ว่ามีอะไรผิดไปหรือเปล่า[4]
• ถ้าเผลอใช้ ? หรือ * แทน = ในวิธีการ "copy สูตรแบบเป๊ะๆ" แล้วค้นหา "?" หรือ "*" จะไม่ได้ผลลัพธ์ที่ต้องการ ให้แก้โดยค้นหา "~?" หรือ "~*" แทน[5]
• เลือกเซลล์ แล้วกด Ctrl' (apostrophe) เพื่อใส่สูตรด้านบน ติดกับเซลล์นั้น

โฆษณา

คำเตือน

• Excel ต่างเวอร์ชั่นกัน ก็อาจมีขั้นตอนแตกต่างไปจากใน screenshot ของบทความวิกิฮาวนี้

โฆษณา

เกี่ยวกับวิกิฮาวนี้

มีการเข้าถึงหน้านี้ 62,584 ครั้ง

บทความนี้เป็นประโยชน์กับคุณไหม