การอ้างอิงที่มีโครงสร้างใน Excel | คำแนะนำทีละขั้นตอนพร้อมตัวอย่าง

วิธีสร้างการอ้างอิงที่มีโครงสร้างใน Excel

การอ้างอิงที่มีโครงสร้างเริ่มต้นด้วยตาราง excel ทันทีที่สร้างตารางใน excel ระบบจะสร้างการอ้างอิงที่มีโครงสร้างให้คุณโดยอัตโนมัติ

ตอนนี้ดูภาพด้านล่าง

• ขั้นตอนที่ 1:ฉันให้ลิงก์ไปยังเซลล์ B3 แทนที่จะแสดงลิงก์เนื่องจาก B2 แสดงเป็นTable1 [@Sales] นี่Table1เป็นชื่อของตารางและ@Salesเป็นคอลัมน์ที่เรากำลังหมายถึง เซลล์ทั้งหมดในคอลัมน์นี้อ้างอิงด้วยชื่อตารางและตามด้วยชื่อส่วนหัวของคอลัมน์
• ขั้นตอนที่ 2:ตอนนี้ผมจะเปลี่ยนชื่อตารางเพื่อData_Tableและเปลี่ยนส่วนหัวของคอลัมน์เพื่อจํานวนเงิน
• ขั้นตอนที่ 3:ในการเปลี่ยนชื่อตารางให้วางเคอร์เซอร์ไว้ในตาราง> ไปที่ออกแบบ> ชื่อตาราง

• ขั้นตอนที่ 4:พูดถึงชื่อตารางเป็นData_Table

• ขั้นตอนที่ 5:ตอนนี้การเปลี่ยนแปลงให้อ้างอิงไปยังเซลล์ B3

ดังนั้นเราจึงเข้าใจว่าการอ้างอิงแบบมีโครงสร้างมีสองส่วนชื่อตารางและชื่อคอลัมน์

ตัวอย่าง

คุณสามารถดาวน์โหลดเทมเพลต Excel การอ้างอิงที่มีโครงสร้างได้ที่นี่ - เทมเพลต Excel การอ้างอิงที่มีโครงสร้าง

ตัวอย่าง # 1

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

ขอฉันใช้สูตร SUM สำหรับทั้งช่วงปกติและตาราง excel

สูตร SUM สำหรับช่วงปกติ

สูตร SUM สำหรับตาราง Excel

ขอฉันเพิ่มสองสามบรรทัดในข้อมูลของทั้งตารางปกติและ excel ฉันได้เพิ่มรายการโฆษณา 2 รายการลงในข้อมูลตอนนี้เห็นความแตกต่าง

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

ตัวอย่าง # 2

ตอนนี้ดูอีกหนึ่งตัวอย่าง ฉันมีข้อมูลชื่อผลิตภัณฑ์ปริมาณและราคา การใช้ข้อมูลนี้ฉันต้องการเพื่อให้ได้มูลค่าการขาย

เพื่อที่จะได้รับค่าขายสูตรคือจำนวน * ราคา ลองใช้สูตรนี้ในตาราง

สูตรระบุว่า[@QTY] * [@PRICE] สิ่งนี้เข้าใจได้มากกว่าการอ้างอิงปกติของB2 * C2 เราจะไม่ได้รับชื่อตารางหากเราใส่สูตรไว้ในตาราง

ปัญหาเกี่ยวกับการอ้างอิงที่มีโครงสร้างของ Excel

ในขณะที่ใช้การอ้างอิงที่มีโครงสร้างเราประสบปัญหาบางประการซึ่งถูกเกณฑ์ไว้ด้านล่าง

ปัญหา # 1

การอ้างอิงที่มีโครงสร้างมีปัญหาในตัวเองเช่นกัน เราทุกคนคุ้นเคยกับการใช้สูตร excel และคัดลอกหรือลากไปยังเซลล์อื่น ๆ ที่เหลือ นี่ไม่ใช่กระบวนการเดียวกันในการอ้างอิงแบบมีโครงสร้างซึ่งทำงานแตกต่างกันเล็กน้อย

ตอนนี้ดูตัวอย่างด้านล่าง ฉันใช้สูตร SUM ใน excel สำหรับช่วงปกติแล้ว

ถ้าฉันต้องการรวมราคาและมูลค่าการขายฉันเพียงแค่คัดลอกและวางหรือลากสูตรปัจจุบันไปยังอีกสองเซลล์และจะให้ค่า SUM ของราคาและมูลค่าการขาย

ตอนนี้ใช้สูตรเดียวกันสำหรับตาราง excel สำหรับคอลัมน์ Qty

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

โอ้พระเจ้า!!! มันไม่ได้แสดงคอลัมน์ราคาทั้งหมด แต่ยังคงแสดงคอลัมน์ Qty ทั้งหมดเท่านั้น ดังนั้นเราจึงไม่สามารถคัดลอกและวางสูตรนี้ลงในเซลล์ที่อยู่ติดกันหรือเซลล์อื่น ๆ เพื่ออ้างถึงคอลัมน์หรือแถวที่สัมพันธ์กันได้

ลากสูตรเพื่อเปลี่ยนข้อมูลอ้างอิง

ตอนนี้เราทราบข้อ จำกัด แล้วเราไม่สามารถทำงานคัดลอกวางได้อีกต่อไปด้วยการอ้างอิงที่มีโครงสร้าง แล้วเราจะเอาชนะข้อ จำกัด นี้ได้อย่างไร?

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

ตอนนี้เราได้อัปเดตสูตรเพื่อรับผลรวมตามลำดับ

ปัญหา # 2

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

ลองดูตัวอย่างด้านล่างตอนนี้ ฉันมีตารางการขายที่มีหลายรายการและฉันต้องการรวมข้อมูลโดยใช้ฟังก์ชัน SUMIF ใน excel

ตอนนี้ฉันจะใช้ฟังก์ชัน SUMIF เพื่อรับมูลค่าการขายรวมสำหรับแต่ละผลิตภัณฑ์

ฉันใช้สูตรสำหรับเดือนมกราคมแล้วเนื่องจากเป็นการอ้างอิงที่มีโครงสร้างเราไม่สามารถคัดลอกและวางสูตรลงในสองคอลัมน์ที่เหลือได้มันจะไม่เปลี่ยนการอ้างอิงเป็น Feb & Mar ดังนั้นฉันจะลากสูตร

โอ้ย !! ฉันไม่ได้รับค่าใด ๆ ในคอลัมน์ ก.พ. และ มี.ค. จะมีปัญหาอะไร ??? ดูสูตรอย่างใกล้ชิด

เราลากสูตรมาตั้งแต่เดือนม. ค. ในอาร์กิวเมนต์แรกของฟังก์ชัน SUMIF คือ Criteria Range Sales_Table [Product]  เนื่องจากเราลากสูตรจึงมีการเปลี่ยนแปลงเป็น Sales _Table [Jan]

แล้วเราจะจัดการกับมันอย่างไร ?? เราจำเป็นต้องทำให้อาร์กิวเมนต์แรกคือคอลัมน์ผลิตภัณฑ์เป็นคอลัมน์สัมบูรณ์และคอลัมน์อื่น ๆ เป็นการอ้างอิงแบบสัมพัทธ์ ซึ่งแตกต่างจากการอ้างอิงปกติเราไม่มีความหรูหราในการใช้คีย์ F4 เพื่อเปลี่ยนประเภทการอ้างอิง

วิธีแก้ปัญหาคือเราต้องทำซ้ำคอลัมน์อ้างอิงดังที่แสดงในภาพด้านล่าง

ตอนนี้เราสามารถลากสูตรไปยังการคว้านสองคอลัมน์อื่น ๆ ได้ ช่วงเกณฑ์จะคงที่และการอ้างอิงคอลัมน์อื่น ๆ จะเปลี่ยนไปตามนั้น

เคล็ดลับสำหรับมือโปร:ในการทำให้ ROW เป็นข้อมูลอ้างอิงแบบสัมบูรณ์เราจำเป็นต้องสร้างรายการ ROW สองครั้ง แต่เราต้องใส่สัญลักษณ์ @ ก่อนชื่อ ROW

= Sales_Table [@ [Product]: [Product]]

วิธีปิดการอ้างอิงที่มีโครงสร้างใน Excel

หากคุณไม่ใช่แฟนของการอ้างอิงที่มีโครงสร้างคุณสามารถปิดได้โดยทำตามขั้นตอนด้านล่างนี้

• ขั้นตอนที่ 1:ไปที่ FILE> Options
• ขั้นตอนที่ 2:สูตร> ยกเลิกการเลือกใช้ชื่อตารางในสูตร

สิ่งที่ต้องจำ

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