ตารางเดือยจากแผ่นงานต่างๆ การรวมข้อมูลโดยไม่ใช้ระยะขอบหน้า

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

แม้ว่าจะมีระบบ ERP และ BI แต่ CFO ไม่สามารถทำได้หากไม่มี Excel การคำนวณทุกชนิด ตารางเดือยกราฟที่สะดวก - ใน Excel คุณสามารถทำเกือบทุกอย่าง แต่คุณต้องรู้วิธีการทำ

ตาราง Pivot ใน Excel โดยใช้ Power Query

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

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

ในกรณีนี้ คุณต้องสร้างเวิร์กชีตใหม่ที่ว่างเปล่าใน Excel

ในแผ่นงานนี้ ไปที่แท็บ "ข้อมูล"

จากนั้นคลิก "สร้างคำขอ" ในรายการแบบเลื่อนลง เลือก "จากไฟล์" จากนั้น - "จากหนังสือ" มาสร้างตารางเดือยของหลาย ๆ แผ่นโดยใช้ตัวอย่าง - ร้านค้าสองแห่งส่งรายงานเกี่ยวกับการมีกล่องที่มีสีต่างกันบนชั้นวางของพวกเขา ข้อมูลของแต่ละร้านค้าถูกจัดเก็บไว้ในแผ่นเดียว จากแผ่นงานเหล่านี้ "เล่ม 1" ถูกสร้างขึ้นซึ่งเราทำงาน

ในหน้าต่างที่ปรากฏขึ้นคุณต้องระบุหนังสือที่โปรแกรมควรรับข้อมูลและคลิกปุ่ม "นำเข้า"

หน้าต่างชื่อ "Navigator" จะปรากฏขึ้น ในนั้นคุณต้องเลือกแผ่นงานที่จะใช้ข้อมูล คุณสามารถระบุแผ่นใดก็ได้

โปรแกรมจะแสดงข้อมูลในหน้าต่างแสดงตัวอย่างซึ่งจะต้องนำมาจากแผ่นงานที่กำหนด

ในตัวอย่างของเรา จะเห็นได้ว่าชีตที่ระบุมีหลายเซลล์ที่มีข้อมูล "null" สิ่งนี้ไม่เป็นความจริง เนื่องจากโปรแกรมจะประมวลผลเซลล์เหล่านี้เช่นกัน หากต้องการลดพื้นที่ของค่าที่ประมวลผลและลบเซลล์ที่เป็นศูนย์ออกคุณต้องแก้ไข ไฟล์ต้นฉบับ. โดยไปที่ตารางเดิมแล้วกด "Ctrl + End" เซลล์ตารางที่ใช้งานล่าสุดจะถูกเน้น จำเป็นต้องลบเซลล์ทั้งหมดทางด้านขวาและด้านล่างตาราง ตรวจสอบให้แน่ใจว่าเมื่อคุณกด “Ctrl + End” เซลล์ด้านล่างขวาของตารางจะทำงาน

หลังจากนั้นแหล่งข้อมูลจะไม่มีข้อมูลที่ไม่จำเป็น

คุณสามารถลบบรรทัด "Navigation" และ "Changed Type" และเริ่มแก้ไขข้อมูลในส่วน "แหล่งที่มา" หน้าต่างตัวแก้ไขหลักจะแสดงรายการชีตทั้งหมดของหนังสือที่ระบุ ในกรณีของเรา "Sheet1" และ "Sheet2"

จากนั้นในบรรทัด "ข้อมูล" ให้คลิกไอคอนที่มีลูกศร 2 อันดังแสดงในรูป

ในหน้าต่างที่ปรากฏขึ้น ให้ยกเลิกการเลือกช่อง "ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า" และคลิก "ตกลง" ตารางที่มีข้อมูลทั้งหมดจะปรากฏขึ้น

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

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

ในหน้าต่างที่ปรากฏขึ้น ให้ตั้งสวิตช์ไปที่ตำแหน่ง "ใช้แหล่งข้อมูลภายนอก" แล้วคลิกปุ่ม "เลือกการเชื่อมต่อ"

ในหน้าต่างที่ปรากฏขึ้น เลือกชื่อคำขอที่สร้างขึ้น ในกรณีของเรา - "สรุป" แล้วคลิกปุ่ม "เปิด"

ตัวออกแบบ PivotTable จะปรากฏขึ้น สามารถเพิ่มและย้ายข้อมูลได้เหมือนใน pivot table ทั่วไป

PivotTable ใน Excel เวอร์ชันก่อนปี 2016

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

ในเวอร์ชันตั้งแต่ปี 2550 จำเป็นต้องเพิ่ม "ตัวช่วยสร้างตาราง Pivot" แบบพิเศษ โดยระบุช่วงข้อมูลใหม่ทีละรายการผ่านกล่องโต้ตอบ ตารางเดือยที่เป็นผลลัพธ์ยังมีฟังก์ชันค่อนข้างจำกัด

Excel Pivot Table คืออะไร? อาจเป็นไปได้ว่าคนที่ไม่ได้ฝึกหัดจะถือว่าชัดเจนที่สุด - นี่คือตารางที่รวบรวมข้อมูลจากแหล่งข้อมูลที่แตกต่างกันหลายแห่ง (สรุป) ที่ตรงกับข้อความค้นหา ในความเป็นจริง ข้อสรุปนี้ไม่ถูกต้องทั้งหมด

Excel PivotTables เป็นเครื่องมือที่รวบรวมข้อมูลส่วนใหญ่จากอัลกอริทึมตามอัลกอริทึมที่กำหนด ตารางฐานข้อมูลหนึ่งตาราง! (มีความเป็นไปได้ของการใช้ช่วงการรวมหลายช่วงเป็นแหล่งข้อมูล แต่จะไม่กล่าวถึงในบทความนี้)

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

หากคุณทำงานอย่างต่อเนื่องใน MS Excel ด้วยข้อมูลจำนวนมากและไม่รู้อะไรเลยเกี่ยวกับ pivot table ให้พิจารณาว่าคุณกำลัง "ตอกตะปู" กับเครื่องคิดเลขโดยไม่รู้จุดประสงค์ที่แท้จริงของมัน!

ควรใช้ pivot table เมื่อใด

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

ประการที่สอง เมื่อจำเป็นต้องทำซ้ำ (คำนวณใหม่) รายงานในช่วงเวลาหนึ่งเนื่องจากการเปลี่ยนแปลงในแหล่งที่มา - ตารางฐานข้อมูล

ประการที่สาม เมื่อวิเคราะห์การเปลี่ยนแปลงของข้อมูลฐานข้อมูลในบริบทของช่วงเวลาต่างๆ หรือเกณฑ์อื่นๆ

นี่คือสถานการณ์หลักที่เปิดเผยพลังเวทย์มนตร์ที่แท้จริงของเครื่องมือ Excel PivotTable

การสร้างตารางเดือยและทำงานกับตารางเหล่านี้ไม่มีผลกับเนื้อหาของแหล่งที่มา - ฐานข้อมูล! ตารางเดือย เก่งให้คุณดูข้อมูลนี้จากมุมต่าง ๆ โดยไม่ต้องเปลี่ยนแปลงอะไรในฐานข้อมูล!!!

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

ในบทความก่อนหน้าของวัฏจักรนี้ เราได้สร้าง เรียนรู้ นำไปใช้ และ เข้าสู่

สร้างเทมเพลตตารางเดือย

เราจะสร้างตารางเดือยในสมุดงานเดียวกันเก่งซึ่งเป็นที่ตั้งของแหล่งข้อมูล - ตารางฐานข้อมูล แต่อยู่ในแผ่นงานอื่น

เรายังคงทำงานร่วมกับ DB2 ฐานขนาดเล็กเพื่อการศึกษา "การผลิตโครงสร้างโลหะตามหัวข้อหมายเลข 2" ซึ่งเราได้ทำงานร่วมกันในบทความทั้งหมดของวัฏจักรนี้

1. เปิดไฟล์ใน MS Excel

2. เราเปิดใช้งาน ("คลิกด้วยเมาส์") เซลล์ใด ๆ ภายในตารางฐาน

3. เราดำเนินการคำสั่งของเมนูหลักของโปรแกรม "ข้อมูล" - "ตาราง Pivot ... " คำสั่งนี้เรียกใช้บริการขนาดเล็กของตัวช่วยสร้าง PivotTable

4. โดยไม่ต้องคิดนานเกี่ยวกับตัวเลือกสำหรับการเลือกตำแหน่งของสวิตช์ในหน้าต่างแบบเลื่อนลงของ "ตัวช่วยสร้าง ... " เรากำหนดค่าพวกมัน (อย่าแตะต้องพวกมันอย่างแม่นยำยิ่งขึ้น) ดังที่แสดงในภาพหน้าจอด้านล่าง ย้ายไปมาระหว่างหน้าต่างโดยใช้ปุ่ม "ถัดไป"

ในขั้นตอนที่สอง "ตัวช่วยสร้าง ... " จะเลือกช่วงเองหากคุณเตรียมฐานข้อมูลอย่างถูกต้องและทำตามขั้นตอนที่ 2 ของส่วนนี้ของบทความนี้

5. ในขั้นตอนที่สามของ "ตัวช่วยสร้าง ... " ให้คลิกปุ่ม "เสร็จสิ้น" เทมเพลตตารางเดือยถูกสร้างขึ้นและวางบนชีตใหม่ของไฟล์เดียวกันซึ่งเป็นที่ตั้งของฐานข้อมูล!

ยกเว้นตอนนี้ แม่แบบที่ว่างเปล่าบนแผ่นงานที่สร้างขึ้นใหม่ ไฟล์เอ็กเซลคุณจะเห็นแผง "Pivot Tables" พร้อมเครื่องมือต่างๆ และหน้าต่าง "List of Pivot Table Fields" ที่ปรากฏขึ้นเมื่อเปิดใช้งานเซลล์ใดๆ ภายในตาราง Pivot สิ่งเหล่านี้ปรากฏขึ้นโดยอัตโนมัติ ฉันเพียงแค่วางไว้ข้างแม่แบบและปรับขนาด หากคุณไม่เห็นแผง "Pivot Tables" ให้เรียกผ่านเมนูหลักด้วยคำสั่ง "View" - "Toolbars" - "Pivot Table"

ใน MS Excel 2007 และเวอร์ชันที่ใหม่กว่า "Wizard ..." ถูกยกเลิกเนื่องจากผู้ใช้ 99% ไม่เคยเปลี่ยนการตั้งค่าสวิตช์ที่เสนอและทำตามขั้นตอนทั้งสามนี้ เพียงแค่ยอมรับตัวเลือกที่เสนอ (เราก็ทำเช่นเดียวกัน)

ใน MS Excel 2007 เมื่อใช้คำสั่ง "Pivot Table" กล่องโต้ตอบ "Create Pivot Table" จะปรากฏขึ้น ซึ่งเพียงพอที่จะระบุแหล่งข้อมูลสำหรับการวิเคราะห์และตำแหน่งของตาราง Pivot ที่จะสร้าง

การสร้างเดือยตาราง Excel ที่ใช้งานได้

มีสี่โซนในเทมเพลตที่สร้างขึ้นซึ่งในการสร้างตารางเดือยคุณควรวางชื่อของฟิลด์ - คอลัมน์ของตารางฐานข้อมูลโดยการลากด้วยปุ่มซ้ายของเมาส์จากหน้าต่าง "รายการฟิลด์ตารางเดือย" ฉันเตือนคุณว่าในฐานข้อมูลฟิลด์เป็นคอลัมน์ที่มีชื่อ

ความสนใจ!!!

องค์ประกอบของหน้าต่าง "รายการเขตข้อมูล PivotTable" คือชื่อเรื่องของเขตข้อมูลฐานข้อมูล!

1. หากคุณวางหนึ่งในองค์ประกอบของ "รายการฟิลด์ Pivot Table" ไว้มากที่สุด โซนบนแม่แบบที่มันจะกลายเป็น "ช่องหน้า"แล้วเราจะได้รับความสะดวก กรองระเบียนของฟิลด์นี้.

2. หากคุณลากองค์ประกอบหนึ่งรายการ (หรือสองรายการ แต่มักจะน้อยกว่าสามรายการขึ้นไป) ของรายการเขตข้อมูล PivotTable ไปยังโซนด้านซ้ายของเทมเพลต ซึ่งองค์ประกอบนั้นจะกลายเป็น "เขตของสตริง" ส่วนหัวของแถวตารางเดือย

3. หากคุณลากองค์ประกอบหนึ่งรายการ (หรือสองรายการ น้อยกว่า - สามรายการขึ้นไป) ของ "รายการ ... " ไปยังโซนด้านบนของเทมเพลต ซึ่งองค์ประกอบนั้นจะกลายเป็น "ช่องคอลัมน์"จากนั้นเราจะได้รับจากบันทึกของฟิลด์นี้ของตารางฐานข้อมูล ส่วนหัวของคอลัมน์ตารางเดือย

4. หากคุณลากองค์ประกอบหนึ่งรายการ (หรือสองรายการน้อยกว่า - สามรายการขึ้นไป) ของ "รายการ ... " ลงในพื้นที่ส่วนกลางของเทมเพลตซึ่งจะกลายเป็น "รายการข้อมูล"จากนั้นเราจะได้จากบันทึกของฟิลด์ฐานข้อมูลนี้ ค่าตารางเดือย

ค่า - องค์ประกอบข้อมูล - จะอยู่อย่างเคร่งครัดตามกฎของตารางสองมิตินั่นคือที่จุดตัดของส่วนหัวของแถวและส่วนหัวของคอลัมน์ที่สอดคล้องกัน!

ไม่ค่อยชัดเจน? มาดูตัวอย่างที่ใช้งานได้จริง - ทุกอย่างจะชัดเจน!

งานหมายเลข 9:

สั่งผลิตโครงสร้างโลหะกี่ตันต่อออเดอร์?

มาทำสองสิ่งเพื่อตอบคำถามนี้กันเถอะ! รูปแบบของการกระทำเหล่านี้แสดงในรูปก่อนหน้าพร้อมลูกศรสีน้ำเงิน

1. ลากองค์ประกอบ "Order" จากหน้าต่าง "List of Pivot Table Fields" ไปยังพื้นที่ "Row Fields" ของเทมเพลตว่าง

2. ลากองค์ประกอบ "มวลรวม, t" จากหน้าต่าง "รายการฟิลด์ของตารางเดือย" ไปยังโซน "องค์ประกอบข้อมูล" ของเทมเพลต ผลลัพธ์อยู่ในภาพหน้าจอด้านซ้าย ฉันคิดว่าไม่จำเป็นต้องอธิบาย

ส่วนหัวของ "Data Elements" "Sum over the field Total mass, t" ตั้งอยู่ สูงขึ้นหัวข้อ "เขตข้อมูลแถว" และทางด้านซ้ายของตำแหน่งที่สามารถระบุหัวข้อ "เขตข้อมูลคอลัมน์" ให้ความสนใจกับสิ่งนี้!

งานหมายเลข 10:

โครงสร้างเหล็กผลิตได้กี่ตันต่อออเดอร์ตามวันที่?

เรายังคงทำงานต่อไป

3. ในการตอบคำถามของงานหมายเลข 10 ก็เพียงพอแล้วที่จะเพิ่มองค์ประกอบ "วันที่" ลงในตารางเดือยของเราจากหน้าต่าง "รายการฟิลด์ตารางเดือย" ไปยังโซน "ฟิลด์คอลัมน์" ของเทมเพลต

4. สามารถจัดกลุ่มระเบียนได้ เช่น ตามเดือน ในการทำเช่นนี้ในแผง "Pivot Tables" ให้คลิกแท็บ "Pivot Table" แล้วเลือก "Group and Structure" - "Group ..." ในหน้าต่างการจัดกลุ่ม ให้ตั้งค่าตามภาพหน้าจอด้านซ้าย

เนื่องจากบันทึกทั้งหมดในฐานข้อมูลของเราสร้างขึ้นในเดือนเมษายน หลังจากการจัดกลุ่ม เราจึงเห็นเพียงสองคอลัมน์ - "เม.ย." และ "ผลรวมทั้งหมด" เมื่อบันทึกของเดือนพฤษภาคมและมิถุนายนปรากฏในฐานข้อมูลต้นทาง คอลัมน์สองคอลัมน์ที่เกี่ยวข้องจะถูกเพิ่มลงในตาราง Pivot

งานหมายเลข 11:

แสตมป์ลำดับที่ 3 ผลิตทั้งหมดเมื่อใดและจำนวนกี่ตัน

5. ในการเริ่มต้น ให้ลากฟิลด์ "Order" จากหน้าต่าง "List of Pivot Table Fields" หรือลากจาก Pivot Table โดยตรงไปยังด้านบนสุดของแผ่นงานไปยังพื้นที่ "Page Fields" ของเทมเพลต

7. เพิ่มช่อง "ปริมาณ ชิ้น" ลงในพื้นที่ "องค์ประกอบข้อมูล"

การกำหนดค่าของตารางเดือย Excel ที่จำเป็นในการตอบคำถามของงานได้ถูกสร้างขึ้น

8. คลิกที่ปุ่มลูกศรลงในเซลล์ B1 และในรายการแบบเลื่อนลง แทนที่จะเป็นรายการ “(ทั้งหมด)” ให้เลือก “3” ซึ่งตรงกับลำดับที่ 3 ที่เราสนใจ และปิดรายการโดยคลิกที่ “ ปุ่มตกลง”

คำตอบสำหรับคำถามงานอยู่ในภาพหน้าจอด้านล่างข้อความนี้

การจัดรูปแบบตาราง Pivot

หากตารางเดือยจำเป็นต้องพิมพ์และแสดงให้ใครบางคนเห็นหรือเพียงเพื่อทำให้ตัวเองพอใจเล็กน้อย ก็จำเป็นต้องจัดรูปแบบ - ให้มีลักษณะที่เหมาะสมและจัดรูปแบบใหม่ของส่วนหัวที่ "ไม่เพียงพอ" บางส่วน

หัวข้อ "ผลรวมสำหรับฟิลด์ น้ำหนักรวม, t" และ "ผลรวมสำหรับฟิลด์ ปริมาณ, ชิ้น" ฟังดูเข้าใจได้ แต่ก็ไม่เป็นภาษารัสเซีย มาเปลี่ยนชื่อเป็น "Total mass of products, t" และ "Number of products, pcs" ที่สละสลวยมากขึ้น

1. มาต่อกับตารางที่สร้างขึ้น เปิดใช้งานเซลล์ B5 ด้วยเมาส์

2. บนแถบเครื่องมือ PivotTable ให้คลิกปุ่มตัวเลือกเขตข้อมูล (เน้นที่ด้านขวาในรูปภาพด้านล่าง)

3. ในหน้าต่างแบบเลื่อนลง "การคำนวณฟิลด์ของตารางเดือย" ให้เปลี่ยนชื่อแล้วคลิกปุ่ม "ตกลง"

4. ตามอัลกอริทึมที่คล้ายกัน เราเปลี่ยนชื่อส่วนหัวของฟิลด์ที่สอง หลังจาก "แทรก" เมาส์ในเซลล์ B6 เป็นครั้งแรก

5. ในแผง "Pivot Table" คลิกปุ่ม "รูปแบบรายงาน" (ไฮไลต์ทางด้านซ้ายในรูปภาพด้านบน - เหนือรายการที่ 3)

6. ในหน้าต่าง "รูปแบบอัตโนมัติ" ที่ปรากฏขึ้น ให้เลือกจากตัวเลือกการจัดรูปแบบที่เสนอ เช่น "รายงาน 6" แล้วคลิก "ตกลง"

รูปร่างตารางเดือยของเรามีการเปลี่ยนแปลงอย่างมาก

บทสรุป.

ฉันดึงความสนใจของคุณไปยังประเด็นสำคัญหลายประการ!

เมื่อเปลี่ยนแหล่งที่มา (เช่น เพิ่มระเบียนอื่นในฐานข้อมูล) การเปลี่ยนแปลงจะไม่เกิดขึ้นโดยอัตโนมัติในตารางเดือยเอง !!!

เพื่อให้การเปลี่ยนแปลงในฐานข้อมูลแสดงในตาราง Pivot จำเป็นต้องกดปุ่ม "อัปเดตข้อมูลด้วยตนเอง" ทุกครั้ง (ไอคอนของปุ่มคือ " ! ) ในแผง PivotTable!!!

วางในช่องพื้นที่ "Page Margins" ที่มี ข้อมูลข้อความซึ่งอาจกลายเป็น หัวเรื่อง สำหรับตารางสาระสำคัญที่สร้างขึ้น.

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

ในโซน "องค์ประกอบข้อมูล" ให้วางเป็นส่วนใหญ่ ข้อมูลตัวเลข

ดำเนินการต่อโดยวางฟิลด์ในแบบที่คุณทำบนกระดาษ! อย่ากลัวที่จะทำผิดพลาด ทุกอย่างแก้ไขได้ง่าย

ตาราง Pivot ที่สร้างขึ้นด้วยการเลื่อนเมาส์สองหรือสามครั้งในส่วนก่อนหน้าของบทความนี้ตอบคำถามที่ยากมากอย่างรวดเร็ว! หากมีการสั่งซื้อภายในหลายเดือน และจำนวนของแบรนด์เนมเกินหลายพัน คุณจะใช้เวลานานเท่าใดในการแก้ปัญหาที่กล่าวถึงข้างต้น ชั่วโมง? วัน? Excel PivotTable ทำได้ทันที ซ้ำๆ ไม่มีข้อผิดพลาด!!!

มีหนังสือหนาและบางที่ดีหลายเล่มเขียนเกี่ยวกับ Excel PivotTable ก่อนอื่น นี่คือหนังสือของ Bill Jelen (“Mr. Excel”) และ Michael Alexander สำหรับคนที่สนใจเรื่องนี้ ผมแนะนำให้อ่านครับ

แน่นอนว่าเป็นเรื่องยากมากแทบจะเป็นไปไม่ได้เลยที่จะพูดถึงรายละเอียดเกี่ยวกับเครื่องมือที่ยอดเยี่ยมนี้ในบทความสั้นๆ ในแง่หนึ่ง ฉันต้องการสัมผัสแง่มุมที่สำคัญจริงมากกว่า แต่ในอีกแง่หนึ่ง ในบทความแนะนำ ฉันไม่ต้องการ "เข้าป่า" ทำให้ผู้ใช้ตกใจกับปริมาณและความซับซ้อนของข้อมูล หาก "เครื่องมือมหัศจรรย์" ของ MS Excel นี้กระตุ้นความสนใจของผู้อ่านบล็อก ในอนาคตหัวข้อนี้จะดำเนินต่อไปด้วยการเปิดตัวโบรชัวร์ขนาดเล็กพร้อมคำแนะนำเชิงปฏิบัติที่สำคัญและมีค่า

อย่าลืมใช้ Excel PivotTable! มูลค่าของคุณในฐานะผู้เชี่ยวชาญจะเพิ่มขึ้นหลายเท่า!

ในบทความหกชุดนี้ซึ่งเป็นภาพรวมทั่วไปของหัวข้อ ประมวลผลข้อมูลจำนวนมากใน Excelสมบูรณ์.

ฉันขอ เคารพ ผลงานของผู้เขียน ติดตาม สำหรับประกาศบทความ ในหน้าต่างที่อยู่ท้ายบทความหรือในหน้าต่างด้านบนของหน้า!

เรียนผู้อ่าน ฝากคำถาม คำติชม และความคิดเห็นไว้ในความคิดเห็นที่ด้านล่างของหน้า

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

รูปแสดงหลักการที่ผมอธิบาย เหล่านั้น. ในช่วง A2:E5 คือตาราง Pivot ดั้งเดิม ซึ่งแปลงเป็นรายการข้อมูล (ช่วง H2:J14) ตารางที่สองแสดงข้อมูลชุดเดียวกัน จากมุมที่ต่างกัน แต่ละค่าของตาราง Pivot ดั้งเดิมมีลักษณะเหมือนสตริง ประกอบด้วยรายการช่องแถว ช่องคอลัมน์ และค่าที่สอดคล้องกัน การแสดงข้อมูลนี้มีประโยชน์เมื่อคุณต้องการจัดเรียงและจัดการข้อมูลด้วยวิธีอื่นๆ

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

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

ขณะนี้คุณมีไอคอนใหม่บนแถบเครื่องมือด่วน

คลิกที่แท็บนี้เพื่อเปิดใช้งาน ตัวช่วยสร้าง PivotTable

ขั้นตอนแรกของวิซาร์ดคือการเลือกประเภทแหล่งข้อมูลของ PivotTable การติดตั้งสวิตช์ ในช่วงการรวมหลายช่วงแล้วคลิก ไกลออกไป.

ในขั้นตอนที่ 2a ระบุวิธีสร้างฟิลด์เพจ วางสวิตช์ สร้างฟิลด์หน้า -> ถัดไป

ที่ขั้นตอน 2b ในฟิลด์ พิสัยเลือกช่วงที่มีข้อมูลแล้วคลิก เพิ่ม.ในกรณีของเรา นี่จะเป็นตำแหน่งของตาราง Pivot ดั้งเดิม A1:E4

ในขั้นตอนที่สาม คุณต้องตัดสินใจว่าคุณต้องการวาง pivot table ไว้ที่ใด และคลิกปุ่ม พร้อม.

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

คุณจะจบลงด้วยตาราง Pivot ขนาดเล็กที่ประกอบด้วยหนึ่งเซลล์ที่มีผลรวมของค่าทั้งหมดในตารางต้นฉบับ

ดับเบิลคลิกที่เซลล์นี้ Excel จะสร้างแผ่นงานใหม่ที่มีตารางพร้อมรายการค่าต่างๆ

ส่วนหัวของตารางนี้แสดงถึง ข้อมูลทั่วไปคุณอาจต้องการให้ข้อมูลเพิ่มเติม

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

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

Pivot Table ใน Excel

ตัวอย่างเช่นเราใช้ตารางการขายสินค้าในสาขาการค้าต่างๆ

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

วิธีแก้ปัญหาที่สมเหตุสมผลที่สุดคือการสร้างตารางเดือยใน Excel:

ง่าย รวดเร็ว และมีคุณภาพสูง

ความแตกต่างที่สำคัญ:

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



วิธีสร้าง pivot table จากหลายๆ ตาราง

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

ขั้นตอนการสร้าง pivot table จากหลายแผ่นจะเหมือนกัน

มาสร้างรายงานโดยใช้ตัวช่วยสร้าง PivotTable:

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

วิธีการทำงานกับ pivot table ใน Excel

เริ่มจากวิธีที่ง่ายที่สุด: การเพิ่มและลบคอลัมน์ ตัวอย่างเช่น พิจารณาตารางสรุปการขายสำหรับแผนกต่างๆ (ดูด้านบน)

ทางด้านขวาของตาราง Pivot เรามีพื้นที่งานที่เราเลือกคอลัมน์ในรายการเขตข้อมูล หากหายไปให้คลิกที่แท็บเล็ต

เพิ่มฟิลด์อื่นในตารางเดือยสำหรับรายงานในการทำเช่นนี้ ให้ทำเครื่องหมายที่ช่องตรงข้าม “วันที่” (หรือตรงข้ามกับ “ผลิตภัณฑ์”) รายงานจะเปลี่ยนแปลงทันที - พลวัตของการขายตามวันในแต่ละแผนกจะปรากฏขึ้น

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

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

และนี่คือสิ่งที่เกิดขึ้นหากเราลบ "วันที่" และเพิ่ม "แผนก":

แต่สามารถจัดทำรายงานดังกล่าวได้หาก ลากช่องระหว่างพื้นที่ต่างๆ:

หากต้องการเปลี่ยนชื่อแถวเป็นชื่อคอลัมน์ เลือกชื่อนี้ คลิกที่เมนูป๊อปอัป คลิก "ย้ายไปที่ส่วนหัวของคอลัมน์" ด้วยวิธีนี้ เราได้ย้ายวันที่ไปยังคอลัมน์

เราใส่ฟิลด์ "แผนก" ไว้หน้าชื่อสินค้า ใช้ส่วนเมนู "ย้ายไปที่จุดเริ่มต้น"

แสดงรายละเอียดของผลิตภัณฑ์เฉพาะ. ในตัวอย่างตารางเดือยที่สอง ซึ่งแสดงยอดคงเหลือในคลังสินค้า เลือกเซลล์ คลิกปุ่มเมาส์ขวา - "ขยาย"

ในเมนูที่เปิดขึ้น ให้เลือกฟิลด์ที่มีข้อมูลที่คุณต้องการแสดง

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

การตรวจสอบความถูกต้องของบิลค่าสาธารณูปโภคที่ออกให้

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

ในการเริ่มต้น เราเสนอให้รวบรวมตารางสรุปภาษีสำหรับค่าสาธารณูปโภคทั้งหมด สำหรับเมืองต่างๆ ข้อมูลจะแตกต่างกัน

ตัวอย่างเช่น เราจัดทำตารางสรุปภาษีสำหรับมอสโก:

เพื่อการศึกษา ลองมาเป็นครอบครัว 4 คนที่อาศัยอยู่ในอพาร์ตเมนต์ขนาด 60 ตร.ม. m. ในการควบคุมค่าสาธารณูปโภค คุณต้องสร้างตารางสำหรับการคำนวณในแต่ละเดือน

คอลัมน์แรก = คอลัมน์แรกจากตารางเดือย ประการที่สองคือสูตรสำหรับการคำนวณมุมมอง:

อัตราค่าบริการ * จำนวนคน/หน่วยเมตร/พื้นที่

สูตรของเราอ้างถึงแผ่นงานที่ตารางสรุปพร้อมอัตราอยู่

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

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

ในการสร้าง คุณต้องเลือกส่วนหัวสำหรับทั้งแถวและคอลัมน์จากรายงานที่สร้างขึ้น วิธีที่สะดวกที่สุดในการทำรายงานคือใน Excel คุณสามารถใช้ตัวกรองกับรายงานทั้งหมดหรือบางส่วนก็ได้

วิธีสร้าง pivot table ใน excel

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

1. เริ่มต้น Excel 2007 เลือกแท็บ "แทรก" ที่แถบด้านบน
2. เลือก "สร้าง PivotTable ที่แนะนำ"
3. หน้าต่างจะปรากฏขึ้นเพื่อแสดงตัวเลือกเฟรม คุณสามารถดูตัวอย่างว่ารายงานของคุณจะมีลักษณะอย่างไรโดยคลิกที่เค้าโครง หลังจากนั้นคุณสามารถเลือกรายการหลักได้ คลิกที่ตกลงและรายงานจะปรากฏบนแผ่นงาน นอกจากนี้ Excel เองจะกำหนดลำดับของแถว
4. คุณสามารถกรองและเรียงลำดับค่าในโปรแกรมได้ มีไอคอนพิเศษบนแผงควบคุมสำหรับสิ่งนี้

นี่เป็นตัวอย่างที่ง่ายที่สุดในการสร้างตารางเดือย เวอร์ชั่นเก่าเก่ง

Pivot table ใน excel 2010 และ 2013 - ตัวอย่างการทำงานใน excel

การสร้างรายงานใน Excel 2010 และ 2013 เกือบจะเหมือนกัน กระบวนการในเวอร์ชันเหล่านี้แตกต่างจากการสร้างในเวอร์ชัน 2007 เล็กน้อย แต่, บางช่วงเวลาคล้ายกัน: ก่อนที่คุณจะเริ่ม คุณต้องสร้างตารางอย่างง่ายซึ่งคุณระบุข้อมูลทั้งหมดที่คุณต้องการ บรรทัดบนสุดควรมีชื่อคอลัมน์

ตอนนี้คุณสามารถสร้างรายงานใน Excel:

แผ่นงานสามารถทำอะไรได้บ้าง:

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

วิธีสร้าง PivotTable โดยใช้ตัวช่วยสร้าง PivotTable

เครื่องมือตัวช่วยสร้าง PivotTable ไม่ปรากฏในแผงหลัก นั่นคือเหตุผลที่ต้องเปิดใช้งานก่อน ในการทำเช่นนี้ไปที่ "ไฟล์" "ตัวเลือก" "แถบเครื่องมือด่วน" ตรงหน้าคุณ รายการจะเปิดขึ้น"เลือกคำสั่งจาก" เลือก "คำสั่งบน Ribbon" ตอนนี้เลือกตัวช่วยสร้าง PivotTable และเพิ่ม หลังจากดำเนินการทั้งหมดแล้ว ไอคอนของฟังก์ชันที่เลือกจะปรากฏบนแผงหลัก

ตอนนี้คุณสามารถดำเนินการตามขั้นตอนการสร้างรายงานสรุปโดยใช้วิซาร์ด:

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

หากคุณเลือกแหล่งข้อมูลที่ต้องการรวมข้อมูลจากแหล่งอื่น จะต้องถูกติ๊กตรงข้ามกับรายการนี้ ตอนนี้คุณต้องระบุวิธีสร้างฟิลด์เพจ คุณจะต้องระบุช่วงด้วย ในการทำเช่นนี้ ก่อนอื่นให้เลือกช่วงหนึ่งแล้วคลิกที่ปุ่ม "เพิ่ม" ค่าที่ตามมาจะถูกเพิ่มด้วย

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

excel 2010 pivot table ที่มีหลายชีต

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

ตอนนี้เราต้องสร้างรายงานสรุป:

• แทรก.
• รายงานรวม
• ป้อนช่วงและที่ตั้ง

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

ดังนั้นเราจึงหาวิธี สร้างเดือยตารางใน excel คุณต้องแสดงความอุตสาหะและความเอาใจใส่เล็กน้อยจากนั้นผลลัพธ์จะไม่ทำให้คุณต้องรอ