ตารางอัจฉริยะใน Excel

วีดีโอ

การกำหนดปัญหา

เรามีตารางที่เราต้องทำงานอย่างต่อเนื่อง (เรียงลำดับ กรอง นับบางอย่างในตาราง) และเนื้อหาที่เปลี่ยนแปลงเป็นระยะ (เพิ่ม ลบ แก้ไข) อย่างน้อยก็เป็นตัวอย่าง - นี่คือสิ่งนี้:

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

Solution

เลือกเซลล์ใดก็ได้ในตารางและบนแท็บ หน้าแรก (บ้าน) ขยายรายการ จัดรูปแบบเป็นตาราง (รูปแบบเป็นตาราง):

ในรายการแบบเลื่อนลงของสไตล์ ให้เลือกตัวเลือกการเติมตามรสนิยมและสีของเรา และในหน้าต่างยืนยันสำหรับช่วงที่เลือก ให้คลิก OK และเราได้ผลลัพธ์ดังต่อไปนี้:

เป็นผลให้หลังจากการเปลี่ยนแปลงช่วงดังกล่าวเป็น "สมาร์ท" ตาราง (ด้วยตัวพิมพ์ใหญ่!) เรามีความสุขดังต่อไปนี้ (ยกเว้นการออกแบบที่ดี):

1. วันเริ่มใช้งาน ตาราง ได้รับชื่อ 1,2,3 ตาราง ฯลฯ ซึ่งสามารถเปลี่ยนเป็นแบบที่พอใช้ได้บนแท็บ นวกรรมิก (ออกแบบ). ชื่อนี้สามารถใช้ได้ในสูตร รายการดรอปดาวน์ และฟังก์ชันต่างๆ เช่น แหล่งข้อมูลสำหรับตารางสาระสำคัญหรืออาร์เรย์การค้นหาสำหรับฟังก์ชัน VLOOKUP
2. สร้างครั้งเดียว ตาราง ปรับขนาดอัตโนมัติ เมื่อเพิ่มหรือลบข้อมูล หากคุณเพิ่มเข้าไปเช่น ตาราง บรรทัดใหม่ – มันจะยืดด้านล่าง ถ้าคุณเพิ่มคอลัมน์ใหม่ – มันจะขยายในความกว้าง ที่มุมขวาล่าง ตาราง คุณสามารถดูเครื่องหมายเส้นขอบที่เคลื่อนที่โดยอัตโนมัติ และหากจำเป็น ให้ปรับตำแหน่งด้วยเมาส์:

    

3. ในหมวก ตาราง อัตโนมัติ เปิดตัวกรองอัตโนมัติ (สามารถบังคับให้ปิดการใช้งานบนแท็บ ข้อมูล (วันที่)).
4. เมื่อเพิ่มบรรทัดใหม่โดยอัตโนมัติ คัดลอกทุกสูตร.
5. เมื่อสร้างคอลัมน์ใหม่ด้วยสูตร – จะถูกคัดลอกไปยังทั้งคอลัมน์โดยอัตโนมัติ – ไม่ต้องลากสูตรด้วยเครื่องหมายกากบาทอัตโนมัติสีดำ.
6. เมื่อเลื่อนดู ตาราง ลง ส่วนหัวของคอลัมน์ (A, B, C…) จะถูกเปลี่ยนเป็นชื่อฟิลด์นั่นคือคุณไม่สามารถแก้ไขส่วนหัวของช่วงได้อีกต่อไป (ใน Excel 2010 มีตัวกรองอัตโนมัติด้วย):
7. โดยเปิดใช้งานช่องทำเครื่องหมาย แสดงเส้นทั้งหมด (แถวทั้งหมด) แถบ นวกรรมิก (ออกแบบ) เราจะได้แถวผลรวมอัตโนมัติในตอนท้าย ตาราง ด้วยความสามารถในการเลือกฟังก์ชัน (ผลรวม ค่าเฉลี่ย จำนวนนับ ฯลฯ) สำหรับแต่ละคอลัมน์:
8. ไปยังข้อมูลใน ตาราง สามารถแก้ไขได้ โดยใช้ชื่อขององค์ประกอบแต่ละอย่าง. ตัวอย่างเช่น ในการรวมตัวเลขทั้งหมดในคอลัมน์ VAT คุณสามารถใช้สูตร =SUM(ตารางที่1[VAT]) แทน = SUM (F2: F200) และไม่ต้องคำนึงถึงขนาดของตาราง จำนวนแถว และความถูกต้องของช่วงการเลือก นอกจากนี้ยังสามารถใช้คำสั่งต่อไปนี้ได้ (สมมติว่าตารางมีชื่อมาตรฐาน 1 ตาราง):

• =ตารางที่1[#ทั้งหมด] – ลิงก์ไปยังทั้งตาราง รวมถึงส่วนหัวของคอลัมน์ ข้อมูล และแถวทั้งหมด
• =ตารางที่1[#Data] – ลิงค์สำหรับข้อมูลเท่านั้น (ไม่มีแถบชื่อเรื่อง)
• =ตารางที่1[#ส่วนหัว] – เชื่อมโยงเฉพาะกับแถวแรกของตารางที่มีส่วนหัวของคอลัมน์
• =ตารางที่1[#Totals] – เชื่อมโยงไปยังแถวทั้งหมด (ถ้ามี)
• =ตารางที่1[#แถวนี้] — อ้างอิงถึงแถวปัจจุบัน เช่น สูตร =Table1[[#This row];[VAT]] จะอ้างอิงถึงค่า VAT จากแถวตารางปัจจุบัน

  (ในเวอร์ชันภาษาอังกฤษ โอเปอเรเตอร์เหล่านี้จะส่งเสียงตามลำดับ #All, #Data, #Headers, #Totals และ #This row)

PS

ใน Excel 2003 มีบางอย่างที่คล้ายกับตาราง "อัจฉริยะ" จากระยะไกล - มันถูกเรียกว่ารายการและถูกสร้างขึ้นผ่านเมนู ข้อมูล – รายการ – สร้างรายการ (ข้อมูล — รายการ — สร้างรายการ). แต่ฟังก์ชั่นปัจจุบันครึ่งหนึ่งไม่มีเลย Excel เวอร์ชันเก่าก็ไม่มีเช่นกัน