ฟอรัมถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
- ไม่อนุญาตให้ใช้ภาษาแชทในการถามและตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
- ห้ามถามโดยระบุชื่อผู้ตอบและต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
- กรุณาอธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
- กรุณาแนบไฟล์ตัวอย่างพร้อมแสดงคำตอบที่ถูกต้องมาในไฟล์ด้วยเพื่อให้ง่ายต่อการทำความเข้าใจและสะดวกต่อการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่นที่อาจจะถูกลบทิ้งไปโดยต้นทางในภายหลัง นอกจากนี้ไม่ควรแนบไฟล์ที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
- กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน กรุณาโพสต์ Code ให้แสดงเป็น Code คือเปิดด้วย [code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
- กรุณาแจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
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
เปิด 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
กด Ctrl+H เพื่อเปิดหน้าต่าง Find. shortcut นี้ใช้ได้ทั้งใน Windows และ macOS
-
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
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
ใช้ 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
ลองใช้วิธีทางเลือก. ถ้าวิธีการที่อธิบายไปด้านบนใช้ไม่ได้ผล หรือกลัวเผลอเปลี่ยนเนื้อหาของเซลล์อื่นด้วยตัวเลือก "Replace all" ก็ยังมีอีก 1 - 2 วิธีให้ได้ลอง
- คุณ copy สูตรจากเซลล์เดียวได้แบบไม่ต้องเปลี่ยนการอ้างอิง ให้เลือกเซลล์ แล้ว copy สูตรที่ขึ้นในแถบสูตรทางด้านบนของหน้าต่าง (ไม่ใช่ในเซลล์) กด Esc เพื่อปิดแถบสูตร แล้ว paste สูตรตรงที่ต้องการได้เลย
- กด Ctrl และ ` (อยู่คีย์เดียวกับ ~) เพื่อเปิดโหมดดูสูตรใน spreadsheet จากนั้น copy สูตรไป paste ใน text editor อย่าง Notepad หรือ TextEdit เสร็จแล้ว copy อีกรอบ ไป paste ใน spreadsheet ตรงตำแหน่งที่ต้องการ สุดท้ายกด Ctrl และ ` อีกรอบ เพื่อสลับกลับไปเป็นโหมดปกติ
โฆษณา
-
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
คลิกมุมขวาล่างของเซลล์ที่มีสูตรที่จะ copy. เคอร์เซอร์จะกลายเป็นเครื่องหมาย + ตัวหนา
-
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 คอลัมน์ A คอลัมน์ B คอลัมน์ C แถว 1 10 9 19 แถว 2 20 8 28 แถว 3 30 7 37 แถว 4 40 6 46 - จะดับเบิลคลิกเครื่องหมายบวกก็ได้ เพื่อใส่สูตรในทั้งคอลัมน์ แทนการลากคลุม โดย Excel จะหยุดใส่สูตรในคอลัมน์ ถ้าเจอเซลล์ว่าง ถ้าข้อมูลอ้างอิงมีช่องว่าง ต้องทำขั้นตอนนี้ซ้ำ เพื่อใส่สูตรในคอลัมน์ล่างช่องว่าง
- อีกวิธีใส่สูตรเดียวทั้งคอลัมน์ คือเลือกเซลล์ด้านล่าง ที่ติดกับเซลล์ที่มีสูตร แล้วกด Ctrl + D[2]
โฆษณา
-
1
พิมพ์สูตรในเซลล์เดียว. ก็เหมือนทุกสูตร คือเริ่มด้วยเครื่องหมาย = แล้วใช้ฟังก์ชั่นหรือเลขคณิตไหนก็ได้ตามต้องการ ต่อไปนี้เป็นตัวอย่าง spreadsheet แบบง่ายๆ โดยจะเพิ่มคอลัมน์ A กับ B เข้าด้วยกัน ให้กด Enter หรือ Return เพื่อคำนวณสูตร
ตัวอย่าง Spreadsheetคอลัมน์ A คอลัมน์ B คอลัมน์ C แถว 1 10 9 19 แถว 2 20 8 แถว 3 30 7 แถว 4 40 6 -
2
เลือกเซลล์แล้วกด Ctrl+C (PC) หรือ ⌘ Command+C (Mac). เพื่อ copy สูตรลงใน clipboard
-
3
เลือกเซลล์ที่จะ copy สูตรไปใส่. คลิกเลือกเซลล์หนึ่ง แล้วลากขึ้นหรือลงโดยใช้เมาส์หรือปุ่มลูกศรก็ได้ อันนี้จะต่างจากวิธีใส่สูตรในคอลัมน์หรือแถว คือเซลล์ที่จะ copy สูตรไปใส่ ไม่ต้องอยู่ติดกับเซลล์ที่ copy ข้อมูลมา ให้กด Control แล้วเลือก copy เซลล์และ range อื่นได้เลย
-
4
กด Ctrl+V (PC) หรือ ⌘ Command+V (Mac) เพื่อ paste. สูตรจะโผล่มาในเซลล์ที่เลือก
โฆษณา
-
1
ใช้การอ้างอิงเซลล์แบบสัมพันธ์ในสูตร. ในสูตร Excel ตัว "cell reference" หรือการอ้างอิงเซลล์ ก็คือที่อยู่ของเซลล์นั้น จะพิมพ์เองก็ได้ หรือคลิกเซลล์ที่จะใช้ตอนพิมพ์สูตร เช่น spreadsheet ต่อไปนี้ มีสูตรที่อ้างอิงถึงเซลล์ A2
อ้างอิงแบบสัมพันธ์คอลัมน์ A คอลัมน์ B คอลัมน์ C แถว 2 50 7 =A2*2 แถว 3 100 แถว 4 200 แถว 5 400 -
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
ใช้การอ้างอิงแบบสัมบูรณ์แทน. อย่างถ้าคุณ ไม่อยาก ให้ 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
- คอลัมน์สัมพันธ์ แถวสัมบูรณ์ (B$1): สูตรจะอ้างอิงแบบสัมบูรณ์กับแถว 1 เพราะฉะนั้นจะอ้างอิงถึงแถว 1 เสมอ
-
4
กดปุ่ม F4 เพื่อสลับไปมาระหว่างสัมบูรณ์กับสัมพันธ์. เลือกการอ้างอิงเซลล์ในสูตรโดยคลิก จากนั้นกด F4 เพื่อเพิ่มหรือลบเครื่องหมาย $ โดยอัตโนมัติ ให้กด F4 ไปเรื่อยๆ จนเลือกการอ้างอิงสัมบูรณ์หรือสัมพันธ์ตามต้องการ จากนั้นกด Enter หรือ Return
โฆษณา
เคล็ดลับ
- ถ้า copy สูตรลงเซลล์ใหม่ แล้วมีสามเหลี่ยมสีเขียว แสดงว่า Excel สแกนเจอ error ให้เช็คสูตรดีๆ ว่ามีอะไรผิดไปหรือเปล่า[4]
- ถ้าเผลอใช้ ? หรือ * แทน = ในวิธีการ "copy สูตรแบบเป๊ะๆ" แล้วค้นหา "?" หรือ "*" จะไม่ได้ผลลัพธ์ที่ต้องการ ให้แก้โดยค้นหา "~?" หรือ "~*" แทน[5]
- เลือกเซลล์ แล้วกด Ctrl' (apostrophe) เพื่อใส่สูตรด้านบน ติดกับเซลล์นั้น
โฆษณา
คำเตือน
- Excel ต่างเวอร์ชั่นกัน ก็อาจมีขั้นตอนแตกต่างไปจากใน screenshot ของบทความวิกิฮาวนี้
โฆษณา
เกี่ยวกับวิกิฮาวนี้
มีการเข้าถึงหน้านี้ 62,584 ครั้ง