การใช้การอ้างอิงที่มีแบบแผนด้วยตาราง Excel

จะเกิดอะไรขึ้นเมื่อฉันใช้การอ้างอิงเซลล์แบบตายตัว

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

1. ในเวิร์กชีตตัวอย่างของคุณ ให้คลิกเซลล์ E2

2. ในแถบสูตร ให้ใส่ =C2*D2 แล้วกด Enter

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

ฉันจะเปลี่ยนชื่อตารางได้อย่างไร

เมื่อคุณสร้างตาราง Excel ขึ้นมา Excel ก็จะสร้างชื่อตารางเริ่มต้น (ตาราง1 ตาราง2 ต่อไปเรื่อยๆ) แต่คุณสามารถเปลี่ยนชื่อตารางให้สื่อความหมายมากขึ้นได้

1. เลือกเซลล์ในตารางเพื่อแสดง เครื่องมือตาราง > แท็บ ออกแบบ บน Ribbon

2. พิมพ์ชื่อที่คุณต้องการในกล่อง ชื่อตาราง แล้วกด Enter

ในข้อมูลตัวอย่างของเรา เราใช้ชื่อว่า DeptSales

ใช้กฎต่อไปนี้สำหรับชื่อตาราง

• ใช้อักขระที่ถูกต้อง  เริ่มต้นชื่อด้วยตัวอักษร อักขระขีดล่าง (_) หรือเครื่องหมายทับขวา (\) เสมอ ใช้ตัวอักษร ตัวเลข จุด และอักขระขีดล่างสําหรับส่วนที่เหลือของชื่อ คุณไม่สามารถใช้ "C", "c", "R" หรือ "r" สำหรับชื่อได้ เนื่องจากตัวอักษรเหล่านี้ได้ถูกกำหนดเป็นทางลัดในการเลือกคอลัมน์หรือแถวสำหรับเซลล์ที่ใช้งานอยู่เมื่อคุณใส่ตัวอักษรเหล่านี้ลงในกล่อง ชื่อ หรือ ไปที่

• อย่าใช้การอ้างอิงเซลล์  ชื่อต้องไม่เหมือนกับการอ้างอิงเซลล์ เช่น Z$100 หรือ R1C1

• อย่าใช้ช่องว่างเพื่อแยกคํา  ไม่สามารถใช้ช่องว่างในชื่อได้ คุณสามารถใช้อักขระขีดล่าง (_) และมหัพภาค (.) เป็นตัวคั่นคํา ตัวอย่างเช่น DeptSales, Sales_Tax หรือ First.Quarter

• ใช้อักขระไม่เกิน 255 ตัว ชื่อตารางสามารถมีอักขระได้สูงสุด 255 ตัว

• ใช้ชื่อตารางที่ไม่ซ้ํากัน ไม่อนุญาตให้ใช้ชื่อที่ซ้ํากัน Excel จะไม่แยกความแตกต่างระหว่างอักขระตัวพิมพ์ใหญ่และตัวพิมพ์เล็กในชื่อ ดังนั้นถ้าคุณใส่ "Sales" แต่มีชื่ออื่นที่เรียกว่า "SALES" ในเวิร์กบุ๊กเดียวกัน คุณจะได้รับพร้อมท์ให้เลือกชื่อที่ไม่ซ้ํากัน

• ใช้ตัวระบุวัตถุ  ถ้าคุณวางแผนที่จะผสมตาราง PivotTable และแผนภูมิ เป็นความคิดที่ดีที่จะนําหน้าชื่อของคุณด้วยชนิดของวัตถุ ตัวอย่างเช่น tbl_Sales สําหรับตารางยอดขาย pt_Sales สําหรับ PivotTable การขาย และ chrt_Sales สําหรับแผนภูมิยอดขาย หรือ ptchrt_Sales สําหรับ PivotChart การขาย วิธีนี้จะเก็บชื่อทั้งหมดของคุณไว้ในรายการลําดับในตัวจัดการชื่อ

กฎไวยากรณ์ของการอ้างอิงที่มีแบบแผน

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

=SUM(DeptSales[[#ผลรวม],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

สูตรนี้มีคอมโพเนนต์ของการอ้างอิงที่มีแบบแผน ดังนี้

• ชื่อตาราง:    DeptSales เป็นชื่อตารางแบบกําหนดเอง โดยจะอ้างอิงข้อมูลในตาราง โดยไม่มีส่วนหัวหรือแถวผลรวมใดๆ คุณสามารถใช้ชื่อตารางเริ่มต้น เช่น ตาราง 1 หรือเปลี่ยนชื่อเพื่อใช้ชื่อที่กําหนดเองได้

• ตัวระบุคอลัมน์:    [Sales Amount] และ [Commission Amount] คือตัวระบุคอลัมน์ที่ใช้ชื่อของคอลัมน์ที่แสดง โดยจะอ้างอิงข้อมูลคอลัมน์ โดยไม่มีส่วนหัวของคอลัมน์หรือแถวผลรวมใดๆ ใส่ตัวระบุในวงเล็บเหลี่ยมตามที่แสดงเสมอ

• ตัวระบุรายการ:    [#Totals] และ [#Data] เป็นตัวระบุรายการพิเศษที่อ้างถึงส่วนที่ระบุของตาราง เช่น แถวผลรวม

• ตัวระบุตาราง:    [[#Totals],[Sales Amount]] และ [[#Data],[Commission Amount]] เป็นตัวระบุตารางที่แสดงส่วนภายนอกของการอ้างอิงที่มีแบบแผน การอ้างอิงภายนอกจะตามหลังชื่อตาราง และคุณใส่ไว้ในวงเล็บเหลี่ยม

• การอ้างอิงที่มีแบบแผน:    (DeptSales[[#Totals],[Sales Amount]] และ DeptSales[[#Data],[Commission Amount]] เป็นการอ้างอิงที่มีแบบแผน ซึ่งแสดงด้วยสตริงที่เริ่มต้นด้วยชื่อตารางและลงท้ายด้วยตัวระบุคอลัมน์

เมื่อต้องการสร้างหรือแก้ไขการอ้างอิงที่มีแบบแผนด้วยตนเอง ให้ใช้กฎไวยากรณ์เหล่านี้

• ใช้วงเล็บเหลี่ยมล้อมรอบตัวระบุ    ตัวระบุตาราง คอลัมน์ และรายการพิเศษทั้งหมดต้องอยู่ในวงเล็บเหลี่ยมที่ตรงกัน ([ ]) ตัวระบุที่มีตัวระบุอื่นต้องการวงเล็บเหลี่ยมที่ตรงกันด้านนอกเพื่อล้อมรอบวงเล็บเหลี่ยมที่ตรงกันด้านในของตัวระบุอื่น ตัวอย่างเช่น: =DeptSales[[Sales Person]:[Region]]

• ส่วนหัวของคอลัมน์ทั้งหมดเป็นสตริงข้อความ    แต่ไม่จําเป็นต้องมีเครื่องหมายอัญประกาศเมื่อใช้ในการอ้างอิงที่มีแบบแผน ตัวเลขหรือวันที่ เช่น 2557 หรือ 1/1/2557 จะถือว่าเป็นสตริงข้อความด้วย คุณไม่สามารถใช้นิพจน์กับส่วนหัวของคอลัมน์ได้ ตัวอย่างเช่น นิพจน์ DeptSalesFYSummary[[2014]:[2012]] จะไม่ทํางาน

ใช้วงเล็บเหลี่ยมล้อมรอบส่วนหัวของคอลัมน์ที่มีอักขระพิเศษ    ถ้ามีอักขระพิเศษ ส่วนหัวของคอลัมน์ทั้งหมดจะต้องอยู่ในวงเล็บเหลี่ยม ซึ่งหมายความว่าจําเป็นต้องมีวงเล็บเหลี่ยมคู่ในตัวระบุคอลัมน์ ตัวอย่างเช่น: =DeptSalesFYSummary[[Total $ Amount]]

ต่อไปนี้เป็นรายการของอักขระพิเศษที่จำเป็นต้องมีวงเล็บเหลี่ยมเพิ่มเติมในสูตร

• Tab 

• ฟีดบรรทัด

• อักขระขึ้นบรรทัดใหม่

• เครื่องหมายจุลภาค (,)

• เครื่องหมายจุดคู่ (:)

• จุด (.)

• วงเล็บเหลี่ยมเปิด ([)

• วงเล็บเหลี่ยมขวา (])

• เครื่องหมายปอนด์ (#)

• เครื่องหมายอัญภาคเดี่ยว (')

• เครื่องหมายอัญภาคคู่ (")

• วงเล็บปีกกาซ้าย ({)

• วงเล็บปีกกาขวา (})

• เครื่องหมายดอลลาร์ ($)

• Caret (^)

• เครื่องหมายและ (&)

• เครื่องหมายดอกจัน (*)

• เครื่องหมายบวก (+)

• เครื่องหมายเท่ากับ (=)

• เครื่องหมายลบ (-)

• สัญลักษณ์มากกว่า (>)

• สัญลักษณ์น้อยกว่า (<)

• เครื่องหมายหาร (/)

• เครื่องหมาย At (@)

• เครื่องหมายทับขวา (\)

• เครื่องหมายอัศเจรีย์ (!)

• วงเล็บเปิด (()

• วงเล็บขวา ())

• เครื่องหมายเปอร์เซ็นต์ (%)

• เครื่องหมายคำถาม (?)

• Backtick (')

• เครื่องหมายอัฒภาค (;))

• เครื่องหมายตัวหนอน (~)

• ขีดล่าง (_)

• ใช้อักขระหลีกสําหรับอักขระพิเศษบางตัวในส่วนหัวของคอลัมน์    อักขระบางตัวมีความหมายพิเศษและต้องใช้เครื่องหมายอัญพจ์เดี่ยว (') เป็นอักขระหลีก ตัวอย่างเช่น: =DeptSalesFYSummary['#OfItems]

ต่อไปนี้คือรายการของอักขระพิเศษที่จําเป็นต้องมีอักขระหลีก (') ในสูตร:

• วงเล็บเหลี่ยมเปิด ([)

• วงเล็บเหลี่ยมขวา (])

• เครื่องหมายปอนด์(#)

• เครื่องหมายอัญภาคเดี่ยว (')

• เครื่องหมาย At (@)

ใช้อักขระช่องว่างเพื่อปรับปรุงความยากง่ายในการอ่านในการอ้างอิงที่มีแบบแผน    คุณสามารถใช้อักขระช่องว่างเพื่อปรับปรุงความยากง่ายในการอ่านการอ้างอิงที่มีแบบแผน ตัวอย่างเช่น: =DeptSales[ [Sales Person]:[Region] ] หรือ =DeptSales[[#Headers], [#Data], [% Commission]]

ขอแนะนำให้ใช้ช่องว่างหนึ่งช่อง

• หลังวงเล็บเหลี่ยมซ้าย ([) แรก

• ก่อนวงเล็บเหลี่ยมขวา (]) สุดท้าย

• หลังเครื่องหมายจุลภาค

ตัวดำเนินการอ้างอิง

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

ตัวระบุรายการพิเศษ

เมื่อต้องการอ้างถึงส่วนที่ระบุของตาราง เช่น เฉพาะแถวผลรวม คุณสามารถใช้ตัวระบุรายการพิเศษต่อไปนี้ในการอ้างอิงที่มีแบบแผนของคุณได้

การระบุการอ้างอิงที่มีแบบแผนที่ถูกต้องในคอลัมน์จากการคำนวณ

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

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

ตัวอย่างของการใช้การอ้างอิงที่มีแบบแผน

ต่อไปนี้คือวิธีบางอย่างในการใช้การอ้างอิงที่มีแบบแผน

กลยุทธ์สำหรับการทำงานกับการอ้างอิงที่มีแบบแผน

พิจารณาสิ่งต่อไปนี้เมื่อคุณทํางานกับการอ้างอิงที่มีแบบแผน

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

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

• ใช้เวิร์กบุ๊กที่มีลิงก์ภายนอกไปยังตาราง Excel ในเวิร์กบุ๊กอื่น    ถ้าเวิร์กบุ๊กมีลิงก์ภายนอกไปยังตาราง Excel ในเวิร์กบุ๊กอื่น เวิร์กบุ๊กต้นฉบับที่ลิงก์นั้นจะต้องเปิดใน Excel เพื่อหลีกเลี่ยง #REF! ข้อผิดพลาดในเวิร์กบุ๊กปลายทางที่มีลิงก์ ถ้าคุณเปิดเวิร์กบุ๊กปลายทางก่อน แล้ว #REF! ข้อผิดพลาดจะปรากฏขึ้น ข้อผิดพลาดเหล่านั้นจะถูกแก้ไขถ้าคุณเปิดเวิร์กบุ๊กต้นฉบับ ถ้าคุณเปิดเวิร์กบุ๊กต้นฉบับก่อน คุณควรจะไม่เห็นรหัสข้อผิดพลาด

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

• ปิดส่วนหัวของคอลัมน์    คุณสามารถสลับส่วนหัวของคอลัมน์ตารางเป็นเปิดและปิดได้จากแท็บ ออกแบบ ของตาราง > แถวส่วนหัว ถ้าคุณปิดส่วนหัวของคอลัมน์ตาราง การอ้างอิงที่มีแบบแผนที่ใช้ชื่อคอลัมน์จะไม่ได้รับผลกระทบ และคุณยังคงสามารถใช้การอ้างอิงเหล่านั้นในสูตรได้ การอ้างอิงที่มีแบบแผนที่อ้างอิงโดยตรงไปยังส่วนหัวของตาราง (เช่น =DeptSales[[#Headers],[%Commission]]) จะส่งผลให้เกิด #REF

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

• เปลี่ยนชื่อตารางหรือคอลัมน์    ถ้าคุณเปลี่ยนชื่อคอลัมน์หรือตาราง Excel จะเปลี่ยนการใช้ตารางและส่วนหัวของคอลัมน์นั้นในการอ้างอิงที่มีแบบแผนทั้งหมดที่ใช้ในเวิร์กบุ๊กโดยอัตโนมัติ

• ย้าย คัดลอก และเติมการอ้างอิงที่มีแบบแผน    การอ้างอิงที่มีแบบแผนทั้งหมดจะยังคงเหมือนเดิมเมื่อคุณคัดลอกหรือย้ายสูตรที่ใช้การอ้างอิงที่มีแบบแผน

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

ต้องการความช่วยเหลือเพิ่มเติมไหม

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community หรือรับการสนับสนุนใน ชุมชน

หัวข้อที่เกี่ยวข้อง

ภาพรวมของตาราง
Excelวิดีโอ: สร้างและจัดรูปแบบตาราง
Excelผลรวมข้อมูลในตาราง
Excelจัดรูปแบบตาราง
Excelปรับขนาดตารางด้วยการเพิ่มหรือเอาแถวและคอลัมน์
ออกกรองข้อมูลในช่วงหรือตาราง
แปลงตารางเป็นช่วง
ปัญหาความเข้ากันได้ของ
ตาราง Excelส่งออกตาราง Excel ไปยัง SharePoint
ภาพรวมของสูตรใน Excel