ผู้ใช้สร้าง PivotTable เพื่อวิเคราะห์ สรุป และนำเสนอข้อมูลจำนวนมาก เครื่องมือ Excel ดังกล่าวช่วยให้คุณสามารถกรองและจัดกลุ่มข้อมูลแสดงในส่วนต่าง ๆ (เตรียมรายงาน)
แหล่งข้อมูลคือตารางที่มีหลายสิบหลายร้อยบรรทัด หลายตารางในหนังสือเล่มเดียว ไฟล์หลายไฟล์ จำลำดับของการสร้าง: "แทรก" - "ตาราง" - "ตารางสาระสำคัญ"
และในบทความนี้เราจะดูวิธีการทำงานกับตารางเดือยใน Excel
วิธีสร้าง pivot table จากหลายๆ ไฟล์
ขั้นตอนแรกคือการอัปโหลดข้อมูลไปที่ โปรแกรมเอ็กเซลและนำมาปรับใช้กับสเปรดชีตของ Excel หากข้อมูลของเราอยู่ใน Worde เราจะโอนข้อมูลนั้นไปยัง Excel และสร้างตารางตามกฎทั้งหมดของ Excel (เราให้ส่วนหัวของคอลัมน์ ลบบรรทัดว่าง ฯลฯ)
การทำงานเพิ่มเติมในการสร้างตารางเดือยจากหลาย ๆ ไฟล์จะขึ้นอยู่กับประเภทของข้อมูล หากข้อมูลเป็นประเภทเดียวกัน (มีหลายตาราง แต่ส่วนหัวเหมือนกัน) ตัวช่วยสร้าง PivotTable จะช่วยได้
เราเพียงสร้างรายงานสรุปตามข้อมูลในช่วงการรวมบัญชีหลายรายการ
ยากมากที่จะทำ ตารางเดือยขึ้นอยู่กับตารางแหล่งที่มาของโครงสร้างที่แตกต่างกัน ตัวอย่างเช่น:
ตารางแรกคือการมาถึงของสินค้า ประการที่สองคือจำนวนหน่วยที่ขายในร้านค้าต่างๆ เราจำเป็นต้องรวมตารางทั้งสองนี้เป็นรายงานเดียวเพื่อแสดงยอดคงเหลือ ยอดขายในร้าน รายได้ และอื่นๆ
ตัวช่วยสร้าง PivotTable จะสร้างข้อผิดพลาดกับพารามิเตอร์เริ่มต้นดังกล่าว เนื่องจากหนึ่งในเงื่อนไขหลักสำหรับการรวมบัญชีถูกละเมิด - ชื่อคอลัมน์เดียวกัน
แต่ทั้งสองหัวข้อในตารางเหล่านี้เหมือนกัน ดังนั้นเราจึงสามารถรวมข้อมูลแล้วสร้างรายงานสรุปได้
รายงานสรุปฉบับร่างจะเปิดขึ้นพร้อมกับรายการฟิลด์ที่สามารถแสดงได้
ตัวอย่างเช่นแสดงปริมาณสินค้าที่ขาย
คุณสามารถแสดงพารามิเตอร์ต่างๆ สำหรับการวิเคราะห์ ย้ายฟิลด์ แต่การทำงานกับตารางเดือยใน Excel ไม่ได้จบลงเพียงแค่นั้น ความเป็นไปได้ของเครื่องมือนั้นมีความหลากหลาย
รายละเอียดข้อมูลใน PivotTable
จากรายงาน (ดูด้านบน) เราพบว่ามีการขายการ์ดวิดีโอเพียง 30 ใบเท่านั้น หากต้องการทราบข้อมูลที่ใช้เพื่อรับค่านี้ ให้ดับเบิลคลิกที่หมายเลข "30" เราได้รับรายงานโดยละเอียด:
วิธีรีเฟรชข้อมูลใน Excel PivotTable
หากเราเปลี่ยนพารามิเตอร์ใด ๆ ในตารางเดิมหรือเพิ่ม สถิติใหม่ข้อมูลนี้จะไม่แสดงในรายงานสรุป สถานการณ์นี้ไม่เหมาะกับเรา
อัพเดทข้อมูล:
เคอร์เซอร์ควรอยู่ในเซลล์ใดก็ได้ของรายงานสรุป
หรือ:
ปุ่มเมาส์ขวา - รีเฟรช
เพื่อปรับแต่ง อัพเดทอัตโนมัติ pivot table เมื่อเปลี่ยนข้อมูล เราทำตามคำแนะนำ:
การเปลี่ยนโครงสร้างของรายงาน
เพิ่มฟิลด์ใหม่ลงในตารางเดือย:
หลังจากเปลี่ยนช่วง ฟิลด์ "การขาย" ปรากฏในข้อมูลสรุป
จะเพิ่มเขตข้อมูลจากการคำนวณไปยังตารางเดือยได้อย่างไร?
บางครั้งผู้ใช้มีข้อมูลไม่เพียงพอใน PivotTable ไม่มีประเด็นใดในการเปลี่ยนแปลงข้อมูลเดิม ในสถานการณ์เช่นนี้ ควรเพิ่มฟิลด์จากการคำนวณ (กำหนดเอง)
นี่คือคอลัมน์เสมือนที่สร้างขึ้นจากการคำนวณ มันสามารถแสดงค่าเฉลี่ย เปอร์เซ็นต์ ความคลาดเคลื่อน นั่นคือผลลัพธ์ สูตรต่างๆ. ข้อมูลเขตข้อมูลจากการคำนวณโต้ตอบกับข้อมูล PivotTable
คำแนะนำในการเพิ่มฟิลด์ที่กำหนดเอง:
การจัดกลุ่มข้อมูลในรายงาน Pivot
ตัวอย่างเช่น พิจารณาต้นทุนสินค้าในปีต่างๆ จำนวนเงินที่ใช้ไปในปี 2555 2556 2557 และ 2558 การจัดกลุ่มตามวันที่ในตารางเดือยของ Excel ทำได้ดังนี้ ตัวอย่างเช่น ลองสรุปง่ายๆ ตามวันที่จัดส่งและจำนวนเงิน
การคลิก คลิกขวาหนูสำหรับวันที่ใดก็ได้ เลือกคำสั่ง "กลุ่ม"
ในกล่องโต้ตอบที่เปิดขึ้น ให้ตั้งค่าพารามิเตอร์การจัดกลุ่ม วันที่เริ่มต้นและวันที่สิ้นสุดของช่วงจะแสดงโดยอัตโนมัติ เลือกขั้นตอน - "ปี"
เราได้จำนวนการสั่งซื้อตามปี
ในทำนองเดียวกัน คุณสามารถจัดกลุ่มข้อมูลในตาราง Pivot ตามพารามิเตอร์อื่นๆ
ตาราง Pivot จำเป็นสำหรับการสรุป วิเคราะห์ และนำเสนอข้อมูลที่อยู่ในตารางต้นฉบับ "ขนาดใหญ่" ในการตัดต่างๆ. พิจารณาขั้นตอนการสร้าง Pivot Table อย่างง่าย
ตารางเดือย (แทรก/ ตาราง/ Pivot ตาราง) จะมีประโยชน์หากตรงตามเงื่อนไขต่อไปนี้พร้อมกัน:
- มีตารางเริ่มต้นที่มีหลายแถว (บันทึก) เรากำลังพูดถึงแถวหลายสิบและหลายร้อยแถว
- จำเป็นต้องดำเนินการวิเคราะห์ข้อมูลซึ่งต้องมีการสุ่มตัวอย่างข้อมูล (การกรอง) การจัดกลุ่ม (การสรุป การนับ) และการนำเสนอข้อมูลในส่วนต่าง ๆ (การจัดทำรายงาน)
- การวิเคราะห์นี้ยากที่จะดำเนินการบนพื้นฐานของตารางเดิมโดยใช้เครื่องมืออื่นๆ: ( CTRL+SHIFT+L), ;
- ตารางแหล่งที่มาเป็นไปตามข้อกำหนดบางประการ (ดูด้านล่าง)
ผู้ใช้มักหลีกเลี่ยงการใช้ ตารางเดือย, เพราะ แน่นอนว่ามันซับซ้อนเกินไป แน่นอน การเรียนรู้เครื่องมือหรือวิธีการใหม่ ๆ ต้องใช้เวลาและความพยายาม แต่ด้วยเหตุนี้ ผลของการเรียนรู้สิ่งใหม่ควรเกินความพยายามที่ลงทุนไป ในบทความนี้ เราจะเข้าใจวิธีสร้างและนำไปใช้ ตารางเดือย.
การเตรียมตารางเริ่มต้น
เริ่มจากข้อกำหนดสำหรับตารางต้นฉบับกันก่อน
- แต่ละคอลัมน์ต้องมีหัวข้อ
- แต่ละคอลัมน์ควรมีค่าในรูปแบบเดียวเท่านั้น (เช่น คอลัมน์วันที่จัดส่งควรมีค่าทั้งหมดในรูปแบบ วันที่; คอลัมน์ "ซัพพลายเออร์" - ชื่อบริษัทเท่านั้นใน รูปแบบข้อความหรือคุณสามารถป้อนรหัสซัพพลายเออร์ในรูปแบบตัวเลข)
- ตารางไม่ควรมีแถวและคอลัมน์ที่ว่างเปล่าทั้งหมด
- ต้องป้อนค่า "อะตอม" ลงในเซลล์เช่น เฉพาะที่ไม่สามารถแบ่งออกเป็นคอลัมน์ต่างๆ ตัวอย่างเช่น คุณไม่สามารถป้อนที่อยู่ในเซลล์เดียวในรูปแบบ: "เมือง ชื่อถนน หมายเลขอาคาร" คุณต้องสร้าง 3 คอลัมน์ที่มีชื่อเดียวกัน มิฉะนั้น ตารางเดือยจะทำงานอย่างไม่มีประสิทธิภาพ (หากคุณต้องการข้อมูล เช่น ในบริบทของเมือง)
- หลีกเลี่ยงตารางที่มีโครงสร้าง "ผิด" (ดูรูปด้านล่าง)
แทนที่จะสร้างคอลัมน์ซ้ำ ( ภูมิภาค 1, ภูมิภาค 2, …) ที่จะมีเซลล์ว่างมากมาย คิดใหม่ โครงสร้างตารางดังแสดงในรูปด้านบน (มูลค่ายอดขายทั้งหมดควรอยู่ในคอลัมน์เดียว ไม่กระจายไปหลายคอลัมน์ ในการดำเนินการนี้ คุณอาจต้องรักษามากกว่า บันทึกรายละเอียด(ดูรูปด้านบน) แทนที่จะแสดงยอดขายรวมสำหรับแต่ละภูมิภาค)
เคล็ดลับโดยละเอียดเพิ่มเติมเกี่ยวกับการสร้างตารางมีอยู่ในบทความชื่อเดียวกัน
ทำให้กระบวนการสร้างง่ายขึ้นเล็กน้อย ตารางเดือยความจริงที่ว่าถ้าต้นฉบับ ( แทรก/ ตาราง/ ตาราง). ในการทำเช่นนี้ ก่อนอื่นให้นำตารางต้นฉบับตามข้อกำหนดข้างต้น จากนั้นเลือกเซลล์ใดก็ได้ในตารางและเรียกหน้าต่างเมนู แทรก/ ตาราง/ ตาราง. ทุกช่องในหน้าต่างจะถูกกรอกโดยอัตโนมัติ คลิกตกลง
ตอนนี้ทำเครื่องหมายที่ช่องถัดจากฟิลด์การขายในรายการฟิลด์
เพราะ หากเซลล์ในคอลัมน์การขายอยู่ในรูปแบบตัวเลข เซลล์เหล่านั้นจะถูกวางไว้ในส่วนรายการฟิลด์ค่าโดยอัตโนมัติ
ด้วยการคลิกเมาส์ไม่กี่ครั้ง (หรือหกครั้ง) เราสร้างรายงานการขายสำหรับแต่ละผลิตภัณฑ์ ผลลัพธ์เดียวกันสามารถทำได้โดยใช้สูตร (ดูบทความ)
ตัวอย่างเช่น หากจำเป็นต้องกำหนดปริมาณการขายสำหรับซัพพลายเออร์แต่ละราย ให้ยกเลิกการทำเครื่องหมายที่ช่องถัดจากฟิลด์สินค้าในฟิลด์รายการ และทำเครื่องหมายที่ช่องถัดจากฟิลด์ซัพพลายเออร์
เจาะลึกข้อมูล PivotTable
หากคุณมีคำถามว่าข้อมูลใดจากตารางเดิมที่ใช้ในการคำนวณค่าบางอย่าง ตารางเดือยจากนั้นเพียงดับเบิลคลิกที่ค่าเฉพาะใน ตารางเดือยเพื่อสร้างแผ่นงานที่แยกจากแถวที่เลือกจากตารางต้นฉบับ ตัวอย่างเช่น มาดูกันว่าระเบียนใดใช้เพื่อสรุปยอดขายของผลิตภัณฑ์ "Oranges" ในการทำเช่นนี้ ให้ดับเบิลคลิกที่ค่า 646720 แผ่นงานแยกต่างหากจะถูกสร้างขึ้นโดยมีเฉพาะแถวของตารางต้นฉบับที่เกี่ยวข้องกับผลิตภัณฑ์ “Oranges”
อัปเดต PivotTable
ถ้าหลังสร้าง ตารางเดือยระเบียนใหม่ (แถว) ถูกเพิ่มลงในตารางเดิม ข้อมูลเหล่านี้จะไม่ถูกนำมาพิจารณาโดยอัตโนมัติ ตารางเดือย. เพื่ออัพเดท ตารางเดือยเลือกเซลล์ใดก็ได้แล้วเลือกรายการเมนู: เมนู การทำงานกับ PivotTable/ ตัวเลือก/ ข้อมูล/ รีเฟรช. ผลลัพธ์เดียวกันสามารถทำได้ผ่านเมนูบริบท: เลือกเซลล์ใดก็ได้ ตารางเดือย รีเฟรช.
การลบ PivotTable
ลบ ตารางเดือยเป็นไปได้หลายวิธี อย่างแรกคือเอาแผ่นงานออก ตารางเดือย(เว้นแต่จะมีข้อมูลที่เป็นประโยชน์อื่นๆ เช่น ตารางต้นฉบับ) วิธีที่สองคือการลบเท่านั้น ตารางเดือย: เลือกเซลล์ใดก็ได้ ตารางเดือยคลิก CTRL+ ก(ทั้งหมดจะถูกเลือก) ตารางเดือย) กดปุ่ม ลบ.
การเปลี่ยนฟังก์ชันผลรวม
ในขณะที่กำลังสร้าง ตารางเดือยค่าที่จัดกลุ่มจะรวมกันตามค่าเริ่มต้น แท้จริงแล้ว เมื่อแก้ปัญหาในการค้นหาปริมาณการขายสำหรับแต่ละผลิตภัณฑ์ เราไม่ได้สนใจเกี่ยวกับฟังก์ชันผลรวม - ยอดขายทั้งหมดที่เกี่ยวข้องกับผลิตภัณฑ์เดียวจะถูกสรุปรวมเข้าด้วยกัน
ตัวอย่างเช่น หากจำเป็นต้องนับจำนวนแบทช์ที่ขายของแต่ละผลิตภัณฑ์ ฟังก์ชันผลรวมจะต้องเปลี่ยน สำหรับสิ่งนี้ใน ตารางเดือย รวมต่อ / ปริมาณ.
การเปลี่ยนลำดับการจัดเรียง
ตอนนี้มาแก้ไขของเรา รายงานรวม. ขั้นแรก เรามาเปลี่ยนลำดับการจัดเรียงของชื่อสินค้า: เรียงลำดับย้อนกลับจาก Z เป็น A ในการทำเช่นนี้ ผ่านรายการดรอปดาวน์ที่ส่วนหัวของคอลัมน์ที่มีชื่อของสินค้า ให้เข้าสู่เมนู และเลือก เรียงจาก Z ถึง A.
สมมติว่ารายการ Baranka เป็นรายการที่สำคัญที่สุด ดังนั้นควรแสดงในบรรทัดแรก ในการทำเช่นนี้ให้เลือกเซลล์ที่มีค่า Baganka และวางเคอร์เซอร์ที่ขอบของเซลล์ (เคอร์เซอร์ควรอยู่ในรูปแบบกากบาทพร้อมลูกศร)
จากนั้น กดปุ่มซ้ายของเมาส์ ลากเซลล์ไปยังตำแหน่งบนสุดในรายการ ด้านล่างส่วนหัวของคอลัมน์
หลังจากปล่อยปุ่มเมาส์ ค่าเบเกิลจะถูกย้ายไปยังตำแหน่งบนสุดในรายการ
เปลี่ยนรูปแบบของค่าตัวเลข
ตอนนี้ให้เพิ่มตัวคั่นกลุ่มหลักเป็นค่าตัวเลข (ฟิลด์การขาย) ในการดำเนินการนี้ ให้เลือกค่าใดๆ ในฟิลด์การขาย เรียกเมนูบริบทด้วยปุ่มเมาส์ขวา และเลือกรายการเมนู รูปแบบตัวเลข…
ในหน้าต่างที่ปรากฏขึ้น ให้เลือกรูปแบบตัวเลขและทำเครื่องหมายในช่อง ตัวคั่นหลักพัน.
การเพิ่มเขตข้อมูลใหม่
สมมติว่าคุณต้องการจัดทำรายงานเกี่ยวกับการขายสินค้า แต่แบ่งตามภูมิภาคที่ขาย ในการดำเนินการนี้ ให้เพิ่มฟิลด์ภูมิภาคการขายโดยเลือกช่องทำเครื่องหมายที่เหมาะสมในรายการฟิลด์ ฟิลด์พื้นที่การขายจะถูกเพิ่มลงในพื้นที่ชื่อแถวของรายการฟิลด์ (ไปยังฟิลด์ผลิตภัณฑ์) โดยการเปลี่ยนแปลงในพื้นที่ ชื่อแถวในรายการฟิลด์ ลำดับของฟิลด์ Product และ Region of sale เราจะได้ผลลัพธ์ดังต่อไปนี้
โดยเน้นชื่อผลิตภัณฑ์ใด ๆ แล้วคลิกรายการเมนู เครื่องมือ PivotTable/ ตัวเลือก/ เขตข้อมูลที่ใช้งานอยู่/ ยุบเขตข้อมูลทั้งหมด,สามารถรีดได้ ตารางเดือยเพื่อแสดงเฉพาะยอดขายตามภูมิภาค
การเพิ่มคอลัมน์
การเพิ่มฟิลด์ภูมิภาคการขายลงในพื้นที่แถวส่งผลให้ ตารางเดือยขยายเป็น 144 บรรทัด ไม่สะดวกเสมอไป เพราะ การขายมีขึ้นใน 6 ภูมิภาคเท่านั้น จึงควรวางฟิลด์ภูมิภาคการขายไว้ในพื้นที่คอลัมน์
ตารางเดือยจะอยู่ในรูปแบบต่อไปนี้
สลับคอลัมน์
หากต้องการเปลี่ยนลำดับของคอลัมน์ คุณต้องคว้าส่วนหัวของคอลัมน์เข้ามา ตารางเดือยลากไปยังตำแหน่งที่ถูกต้อง
การลบฟิลด์
สามารถลบฟิลด์ใดก็ได้ออกจาก PivotTable ในการดำเนินการนี้ ให้เลื่อนเคอร์เซอร์ของเมาส์ไปที่รายการฟิลด์ (ในตัวกรองรายงาน ชื่อรายงาน ชื่อคอลัมน์ พื้นที่ค่า) กดปุ่มซ้ายของเมาส์แล้วลากฟิลด์ที่จะลบออกจากรายการฟิลด์
อีกวิธีหนึ่งคือการยกเลิกการทำเครื่องหมายที่ช่องถัดจากฟิลด์ที่จะลบที่ด้านบนของรายการฟิลด์ แต่ในกรณีนี้ ฟิลด์จะถูกลบออกจากพื้นที่ทั้งหมดของรายการฟิลด์พร้อมกัน (หากมีการใช้ในหลายพื้นที่)
การเพิ่มตัวกรอง
สมมติว่าจำเป็นต้องจัดทำรายงานเกี่ยวกับการขายกลุ่มสินค้า และจะต้องทำใน 2 เวอร์ชัน: เวอร์ชันหนึ่งสำหรับชุดของสินค้าที่ก่อให้เกิดผลกำไร และอีกเวอร์ชันหนึ่งสำหรับกลุ่มที่ไม่ทำกำไร สำหรับสิ่งนี้:
- PivotTable คลิกรายการเมนู ;
- ใส่เครื่องหมายถูกในรายการฟิลด์ใกล้กับฟิลด์ กลุ่ม การขายและกำไร
- ย้ายฟิลด์กำไรจากพื้นที่ชื่อแถวของรายการฟิลด์ไปยังพื้นที่ตัวกรองรายงาน
ประเภทของผลลัพธ์ ตารางเดือยควรเป็นดังนี้:
ตอนนี้เอาเปรียบ รายการแบบเลื่อนลง (แบบเลื่อนลง)ในเซลล์ บี1 (ฟิลด์กำไร) ตัวอย่างเช่น คุณสามารถสร้างรายงานเกี่ยวกับการขายกลุ่มสินค้าที่ทำกำไรได้
หลังจากกดปุ่ม OK ค่าการขายเฉพาะล็อตที่ทำกำไรจะแสดงขึ้น
โปรดทราบว่าในรายการฟิลด์ ตารางเดือยไอคอนตัวกรองปรากฏขึ้นตรงข้ามฟิลด์กำไร คุณสามารถลบตัวกรองได้โดยยกเลิกการเลือกช่องทำเครื่องหมายในรายการฟิลด์
คุณสามารถล้างตัวกรองผ่านเมนู เครื่องมือ PivotTable/ ตัวเลือก/ การดำเนินการ/ ล้าง/ ล้างตัวกรอง.
นอกจากนี้ ข้อมูลยังมีอยู่ในรายการแบบเลื่อนลงในส่วนหัวของแถวและคอลัมน์ ตารางเดือย.
ผลรวมหลายรายการสำหรับหนึ่งฟิลด์
- ล้างรายงานที่สร้างไว้ก่อนหน้านี้: เลือกค่าใดก็ได้ ตารางเดือยให้คลิกรายการเมนู เครื่องมือ PivotTable/ ตัวเลือก/ การดำเนินการ/ ล้าง/ ล้างทั้งหมด;
- ทำเครื่องหมายที่ช่องถัดจากฟิลด์ผลิตภัณฑ์และการขายที่ด้านบนของรายการฟิลด์ ฟิลด์การขายจะถูกวางไว้ในพื้นที่ค่าโดยอัตโนมัติ
- ลากและวางสำเนาของฟิลด์การขายลงในพื้นที่ค่าเดียวกัน ใน ตารางเดือยจะมี 2 คอลัมน์นับยอดขาย
- วี ตารางเดือยเลือกค่าใดก็ได้ในฟิลด์การขาย คลิกขวาที่เมนูบริบทแล้วเลือก รวมต่อ / ปริมาณ. แก้ไขปัญหา.
ปิดใช้งานแถวทั้งหมด
แถบผลรวมสามารถปิดใช้งานได้ผ่านเมนู: การทำงานกับ PivotTables/ ตัวสร้าง/ เค้าโครง/ ผลรวมทั้งหมด. อย่าลืมเลือกเซลล์ล่วงหน้า ตารางเดือย.
การจัดกลุ่มตัวเลขและวันที่
สมมติว่าคุณต้องการจัดทำรายงานเวลาขาย ด้วยเหตุนี้ คุณต้องได้รับข้อมูลต่อไปนี้: จำนวนสินค้าที่ฝากขายในช่วง 1 ถึง 10 วัน ในช่วง 11-20 วัน เป็นต้น สำหรับสิ่งนี้:
- ล้างรายงานที่สร้างไว้ก่อนหน้านี้: เลือกค่าใดก็ได้ ตารางเดือยให้คลิกรายการเมนู เครื่องมือ PivotTable/ ตัวเลือก/ การดำเนินการ/ ล้าง/ ล้างทั้งหมด;
- ทำเครื่องหมายที่ช่องถัดจากฟิลด์การขาย (วันที่ขายสินค้าจริง) ที่ด้านบนของรายการฟิลด์ ฟิลด์การขายจะถูกวางไว้ในพื้นที่ค่าโดยอัตโนมัติ
- เลือกค่าเดียวของฟิลด์การขายใน ตารางเดือยเรียกเมนูบริบทด้วยปุ่มเมาส์ขวาแล้วเลือกรายการ รวมต่อ / ปริมาณ.
- ลากสำเนาอื่นของฟิลด์การขายด้วยเมาส์ไปยังพื้นที่ชื่อแถว
ตอนนี้ ตารางเดือยแสดงจำนวนสินค้าที่ฝากขายใน 5, 6, 7, ... วัน มีทั้งหมด 66 เส้น มาจัดกลุ่มค่าด้วยขั้นตอนที่ 10 โดยทำดังนี้
- เน้นหนึ่งค่า ตารางเดือยในคอลัมน์ชื่อแถว
- เลือกรายการจากเมนู จัดกลุ่มตามเขตข้อมูล;
- กรอกหน้าต่างที่ปรากฏดังรูปด้านล่าง
- คลิกตกลง
ตอนนี้ ตารางเดือยแสดงจำนวนสินค้าที่ฝากขายในช่วง 1 ถึง 10 วันในช่วง 11-20 วัน เป็นต้น
หากต้องการยกเลิกการจัดกลุ่มค่า ให้เลือก ยกเลิกการจัดกลุ่มในเมนู การทำงานกับ PivotTable/ ตัวเลือก/ กลุ่ม.
การจัดกลุ่มที่คล้ายกันสามารถทำได้โดยฟิลด์วันที่จัดส่ง ในกรณีนี้คือหน้าต่าง จัดกลุ่มตามเขตข้อมูลจะมีลักษณะดังนี้:
ตอนนี้ ตารางเดือยแสดงจำนวนสินค้าที่ส่งมอบในแต่ละเดือน
การจัดรูปแบบเซลล์ PivotTable ตามเงื่อนไข
ให้กับเซลล์ ตารางเดือยคุณสามารถใช้กฎได้เช่นเดียวกับที่คุณใช้กับเซลล์ในช่วงปกติ
ยกตัวอย่างเช่น เซลล์ที่มียอดขายสูงสุด 10 อันดับแรก สำหรับสิ่งนี้:
- เลือกเซลล์ทั้งหมดที่มีค่าการขาย
- เลือกรายการเมนู หน้าแรก/ สไตล์/ การจัดรูปแบบตามเงื่อนไข/ กฎสำหรับการเลือกค่าแรกและค่าสุดท้าย/ องค์ประกอบ 10 รายการแรก;
- คลิกตกลง
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สมบูรณ์.
ฉันขอ เคารพ ผลงานของผู้เขียน ติดตาม สำหรับประกาศบทความ ในหน้าต่างที่อยู่ท้ายบทความหรือในหน้าต่างด้านบนของหน้า!
เรียนผู้อ่าน ฝากคำถาม คำติชม และความคิดเห็นไว้ในความคิดเห็นที่ด้านล่างของหน้า
Excel PivotTable เป็นหนึ่งในเครื่องมือที่ช่วยให้คุณวิเคราะห์ข้อมูลจำนวนมากได้อย่างรวดเร็วและง่ายดาย เราจะแสดงตัวอย่างวิธีสร้างตารางเดือยใน Excel
ในบทความนี้คุณจะได้เรียนรู้:
PivotTable ใน Excel คืออะไร
เมื่อทำงานกับข้อมูลใน Excel จำเป็นต้องวิเคราะห์เป็นระยะ สิ่งนี้มีความสำคัญ ตัวอย่างเช่น ในการขาย - ใครในช่วงเวลาใด ยอดขายเท่าใด เมื่อเกิดภาวะเศรษฐกิจถดถอย และเมื่อมีคลื่นสูง สินค้าใดเป็นที่ต้องการมากที่สุด ซึ่งน้อยที่สุด ยิ่งข้อมูลมาก การวิเคราะห์ก็ยิ่งยากขึ้น ยิ่งมีโอกาสเกิดข้อผิดพลาดสูงเนื่องจากปัจจัยมนุษย์ ดังนั้นจึงจำเป็นต้องใช้เครื่องมือที่ Excel จัดเตรียมไว้ให้
Pivot table MS Excel เป็นเครื่องมือสำหรับการวิเคราะห์และการนำเสนอข้อมูลในรูปแบบที่สะดวกและเข้าใจได้ง่าย ใช้ในกรณีต่อไปนี้:
- ข้อมูลเริ่มต้นจำนวนมาก ข้อมูลจำนวนเล็กน้อยสามารถคำนวณและวิเคราะห์ได้ด้วยตนเอง อย่างไรก็ตาม หากคุณมีเรกคอร์ดเป็นร้อยเป็นพันรายการ ข้อผิดพลาดเป็นสิ่งที่หลีกเลี่ยงไม่ได้และจะมีจำนวนมาก เวลาที่ใช้ในการประมวลผลจะเพิ่มขึ้นอย่างมากเช่นกัน
- จำเป็นต้องระบุแนวโน้มและการเปลี่ยนแปลงในข้อมูล ในตาราง Pivot คุณสามารถแปลงผลลัพธ์ของข้อมูลได้อย่างง่ายดาย คุณไม่จำเป็นต้องจัดเรียงด้วยตนเอง
- เพื่อสร้างแผนภูมิหรือกราฟตามข้อมูลที่จัดรูปแบบ
- มีความจำเป็นต้องทำการ "ตัด" ขั้นกลางของผลลัพธ์ซึ่งมักจะเปลี่ยนแปลงสำหรับการเปรียบเทียบในภายหลัง ในกรณีนี้ แค่อัปเดตข้อมูลก็เพียงพอแล้ว Excel จะคำนวณทุกอย่างใหม่โดยอัตโนมัติ
- สำหรับการวิเคราะห์และค้นหาค่าสูงสุด - data minima และ maxima
- เพื่อรวบรวมข้อมูลที่แตกต่างกันจากแหล่งต่างๆ หากจำเป็น คุณสามารถเพิ่มแถวหรือคอลัมน์ที่ต้องการลงในแหล่งข้อมูลและอัปเดตการแสดงผลขั้นสุดท้ายของรายงานได้
นอกจากนี้ Pivot Table ใน Excel ยังใช้งานได้สะดวกกว่าเมื่อดูข้อมูล .
ตารางเดือยมีลักษณะอย่างไร
ก่อนที่เราจะพูดถึงวิธีสร้าง pivot table ใน Excel เรามาแสดงหน้าตาของมันกันก่อน ข้อมูลสำหรับตารางนำมาจากตารางที่ใช้ร่วมกันที่ระบุโดยผู้ใช้ ตามหน้าที่ ตารางจะแบ่งออกเป็นสี่ส่วน:
- ค่า;
- เส้น;
- คอลัมน์;
- ตัวกรองรายงาน
PivotTable ของไฟล์ .xlsx มีข้อจำกัดต่อไปนี้:
- จำนวนฟิลด์แถวสูงสุดคือ 1,048,576 หรือจำกัดด้วยขนาดของ RAM
- จำนวนคอลัมน์สูงสุดคือ 16,384;
- จำนวนฟิลด์เพจสูงสุดคือ 16,384;
- จำนวนฟิลด์ข้อมูลสูงสุดคือ 16,384
ลองใช้ข้อมูลต่อไปนี้เป็นตัวอย่าง
ตารางเดือยสำหรับข้อมูลนี้จะมีลักษณะดังนี้:
ในส่วนด้านขวาของตารางเดือย - นามสกุล, ที่ด้านบน - วันที่, ตรงกลาง - ผลงาน เห็นได้ชัดว่าการนำเสนอข้อมูลนี้สะดวกและเข้าใจได้มากขึ้น .
ในภาพนี้ ส่วนค่าจะถูกเน้นด้วยสีแดง นี่คือส่วนที่ดำเนินการคำนวณ คุณสามารถดูค่าใช้จ่ายของดีลที่สรุปตามวันและตามนามสกุลได้ที่นี่
นี่คือส่วนสตริง อาจมีหนึ่งค่าหรือมากกว่าที่นี่ ในกรณีของเรา นี่คือชื่อของผู้จัดการ ตรงข้ามชื่อในบรรทัดที่เกี่ยวข้อง - บทสรุปของงาน
นี่คือส่วนคอลัมน์ โดยปกติจะเป็นมาตราส่วนเวลา การใช้งานจะช่วยอำนวยความสะดวกในการวิเคราะห์และรับรู้ข้อมูล
ในตัวช่วยสร้างตารางเดือยพิเศษ คุณสามารถเปลี่ยนตำแหน่งของข้อมูลได้ ในกรณีของเรา สามารถใส่นามสกุลลงในส่วนของคอลัมน์ ส่วนวันที่ - ในส่วนของแถว จากนั้นตารางเดือยจะเปลี่ยนโฉมใหม่
PivotTable Builder ใช้งานง่าย ในนั้น คุณสามารถลากค่าลงในช่อง คุณสามารถเลือกค่าจากรายการแบบหล่นลง นอกจากนี้ในบทความ บทช่วยสอนเกี่ยวกับการสร้างตารางเดือยใน Excel พร้อมตัวอย่าง .
วิธีสร้าง PivotTable ใน Excel
เราจะบอกและแสดงตัวอย่างวิธีสร้างตารางเดือยใน Excel เพื่อเป็นข้อมูลเบื้องต้น มาดูตารางของเราพร้อมข้อมูลเกี่ยวกับงานของผู้จัดการในเดือนพฤษภาคม หากต้องการสร้าง Pivot Table ให้เลือก "Pivot Table" จากเมนูด้านบนสุดในแท็บ "Insert" พวกเขาจะถูกเน้นในภาพ
หน้าต่างใหม่จะปรากฏขึ้น:
จะต้องระบุแหล่งที่มาของข้อมูลที่จะใช้ในการสร้างตารางเดือย ในการทำเช่นนี้ คุณต้องเลือกพื้นที่ที่ต้องการหรือทั้งตาราง แล้ว Excel จะแทนที่ข้อมูลนี้ในแถว
สีแสดงว่าอาร์เรย์ทั้งหมดถูกเลือก ข้อมูลปรากฏในบรรทัด จากนั้นคุณต้องระบุตำแหน่งที่ Excel ควรสร้างตารางเดือย คุณสามารถเลือกแผ่นงานปัจจุบันหรือเลือกแผ่นงานใหม่
หากคุณเลือกแผ่นงานใหม่ ในกรณีนี้แผ่นงานจะถูกสร้างขึ้น ตัวออกแบบตารางเดือยจะปรากฏขึ้น เราขอแนะนำให้ทำเช่นนั้น หลังจากระบุข้อมูลทั้งหมดแล้ว - แหล่งข้อมูลและสถานที่สำหรับรายงาน คุณต้องคลิกปุ่ม "ตกลง" และข้อความแจ้งจะปรากฏขึ้นเพื่อแจ้งให้คุณเลือกฟิลด์สำหรับสร้างรายงานใหม่
ทางด้านขวาจะมีส่วนเปิดขึ้นซึ่งคุณสามารถระบุฟิลด์เพื่อเพิ่มลงในตารางเดือยได้
ภายใต้นั้นจะมีฟิลด์งานอื่นที่คุณสามารถลากฟิลด์ไปยังพื้นที่ต่างๆ
คุณต้องทำเครื่องหมายในช่องสำหรับฟิลด์ที่จำเป็น ตำแหน่งที่ระบุจะปรากฏในส่วนด้านล่างโดยอัตโนมัติ สามารถปรับแต่งได้ตามใจชอบ
เมื่อสร้างข้อมูลต้นทางใหม่ จอแสดงผลจะถูกสร้างขึ้นใหม่โดยอัตโนมัติ หากมีค่ามากมายสิ่งนี้อาจรบกวนได้ ดำเนินการตามปกติ. ดังนั้น คุณสามารถทำเครื่องหมายที่ช่องทำเครื่องหมาย "เลื่อนการอัปเดตเลย์เอาต์" การสร้างใหม่โดยอัตโนมัติจะถูกปิดใช้งาน หากต้องการรีเฟรชตาราง Pivot เพียงคลิกปุ่มรีเฟรช
ดังนั้นเราจะสร้างตารางเดือยและจะใช้รูปแบบต่อไปนี้
จะเห็นได้ว่าลำดับของบรรทัดมีความสำคัญ บรรทัดแรกในตัวสร้างมาก่อน บรรทัดที่สองในตัวสร้างกลายเป็นย่อหน้าย่อยของบรรทัดแรก ในกรณีของเรา ผลงานของผู้จัดการแต่ละคนจะแสดงโดยแยกตามรายการสินค้า - บทความโดยอ้างอิงวันที่
คุณสามารถเปลี่ยนลำดับของเส้นได้ โดยเพียงแค่ลากเส้นที่ต้องการขึ้นหรือลงด้วยเมาส์ หากคุณสลับนามสกุลกับบทความ ตารางเดือยจะใช้แบบฟอร์มต่อไปนี้
เพื่อความสะดวกในการรับรู้ข้อมูล สามารถยุบรายการย่อยได้ ตามค่าเริ่มต้น จะแสดงแบบขยาย หากต้องการยุบตำแหน่งที่ไม่จำเป็น คุณต้องคลิกเครื่องหมายลบถัดจากบทความ จากนั้นรายการย่อยจะถูกซ่อน หากต้องการขยาย ให้คลิกเครื่องหมายบวก พวกเขาจะถูกเน้นในภาพ
ส่วนค่าส่วนกลางจะแสดงข้อมูลทั้งหมดที่โปรแกรมสรุปและคำนวณ ยอดรวมจะแสดงตามวันที่ ตามบทความ และถ้าบทความไม่ถูกยุบ ก็จะแสดงตามชื่อผู้จัดการ
หากคุณเปลี่ยนแปลงตารางเดิม จะต้องอัปเดตตาราง Pivot ในการทำเช่นนี้ให้คลิกขวาที่ใดก็ได้ในตำแหน่งนั้นแล้วเลือกที่ปรากฏ เมนูบริบท"อัปเดต". ข้อมูลจะถูกสร้างขึ้นใหม่
หากมีการเพิ่มแถวหรือคอลัมน์ใหม่และการแสดงรายงานแบบเก่าไม่ครอบคลุมชุดข้อมูลที่จำเป็นทั้งหมด คุณต้องรวมไว้ในตาราง Pivot ตัวอย่างเช่น มีการเพิ่มข้อมูลใหม่ลงในอาร์เรย์หลักของข้อมูล (เน้นด้วยสี) และตาราง Pivot ยังไม่ได้พิจารณาข้อมูลเหล่านี้
หากต้องการเพิ่มลงในรายงาน คุณต้องคลิกที่ตาราง Pivot ไปที่แท็บ "พารามิเตอร์" แล้วเลือก "เปลี่ยนแหล่งข้อมูล" - ตัวเลือกนี้จะถูกเน้นในรูปภาพ
ในหน้าต่างที่ปรากฏขึ้น ให้ระบุแหล่งข้อมูลพร้อมกับแถวที่อัปเดต แล้วคลิก ตกลง
ตารางเดือยจะสร้างใหม่โดยอัตโนมัติ ในภาพ ข้อมูลของวันที่ 29/05/2018 ซึ่งไม่มีให้ก่อนหน้านี้ จะถูกเน้นด้วยสี
วิดีโอ: วิธีการทำงานกับ pivot table
เพื่อนของฉันซึ่งเป็นนักเศรษฐศาสตร์ในองค์กรเป็นเจ้าของ Excel บางครั้งดูที่ฟอรัม แต่เป็นเวลา 5 ปีที่เธอไม่สามารถเข้าใกล้เครื่องมือ Excel ที่มีประโยชน์ได้มากที่สุด - ตารางเดือยในความคิดของฉัน มันคืออะไร? ตาราง Pivot เป็นผลมาจากการเลือกข้อมูลที่มีรูปแบบเหมาะสม เครื่องมือนี้มีข้อดีมากมาย ฉันจะไม่อธิบาย ฉันจะบอกว่าหากไม่มีเครื่องมือนี้ หลายคนจะต้องเชี่ยวชาญไม่เพียงแต่ Excel เท่านั้น แต่ยังรวมถึง Microsoft Access ด้วย
ความคิดเห็นของฉันคือควรศึกษาเครื่องมือนี้ด้วยตัวเองและในบทความนี้ฉันจะบอกคุณถึงความแตกต่างที่ผู้เริ่มต้นที่ต้องการเชี่ยวชาญเครื่องมือนี้สะดุด
ST ต้องการข้อมูลที่มีรูปแบบที่ดี มุมมองในอุดมคติ - หนึ่งการดำเนินการต่อบรรทัดสอดคล้องกับฟิลด์ต่างๆ ของคุณสมบัติเรกคอร์ด
ฉันสร้างตารางอย่างง่ายที่ปรากฏในคลังสินค้าทุกแห่ง คลังสินค้าจัดส่งวัสดุก่อสร้างไปยังร้านค้า 4 แห่งในระหว่างปี และการดำเนินการขนส่งสินค้าแต่ละครั้งจะมีบันทึกของตนเอง
อย่างที่คุณเห็น ที่นี่ไม่มีการจัดกลุ่มหรือสรุปข้อมูล ฉันเรียกว่าชีต เพราะแม้ว่าอาจมีหลายคอลัมน์ แต่ก็ยังมีแถวอีกมากมาย ไม่มีช่องว่างที่นี่ซึ่งเป็นสิ่งสำคัญเช่นกัน
Excel จะแนะนำช่วง ดังนั้นให้คลิกตกลง
มี 4 ตัวเลือกที่นี่: แถว คอลัมน์ ค่า และตัวกรองรายงาน
เรามาหยุดกันในรายละเอียดเพิ่มเติม สตริงและ คอลัมน์โดยพื้นฐานแล้วเป็นหมวดหมู่เดียวกัน คุณสามารถใส่ฟิลด์เหล่านี้ที่บ่งบอกได้ เช่น ชื่อ เดือน หมวดหมู่ คุณสามารถใส่สองแถวในหนึ่งแถวและไม่ใช่ในคอลัมน์ ซึ่งในกรณีนี้ตารางของคุณจะมีการแบ่งกลุ่มเป็นแถว
ค่า- ที่นี่เราใส่ค่าตัวเลขเท่านั้น
ตัวกรองรายงาน. ตามที่คุณเข้าใจ คุณจะสร้างเฟรมขึ้นมาเองและคุณสามารถละเว้นบางฟิลด์ วางในแนวนอน บางฟิลด์ในแนวตั้ง ทั้งแถวและคอลัมน์จะมีตัวกรองของตัวเอง แต่ตัวกรองรายงานช่วยให้คุณเลือกข้อมูลที่คุณต้องการแสดงในตารางนี้ได้สะดวกยิ่งขึ้น
ตอนนี้ในตัวอย่างข้อมูลของฉัน
โต๊ะแบบไหนที่รับได้:
อย่างที่คุณเห็นมีหลายรูปแบบ
1) แม้ว่าตารางเดือยจะดึงส่วนข้อมูลที่มีอยู่ทั้งหมดออกมาอย่างชัดเจน การทำงานในตารางนั้นไม่ใช่เรื่องง่ายเสมอไป แต่ไม่สามารถแก้ไขได้ ทำได้เฉพาะแบบอ่านอย่างเดียว หากคุณมีข้อมูลหลายส่วน การสร้างตารางและกรอกข้อมูลลงในตารางจะง่ายกว่าโดยใช้ฟังก์ชัน GET.DATA.PIVOTAL.TABLE และกฎสำหรับการทำงานกับลิงก์ที่เกี่ยวข้อง
รูปแบบของฟังก์ชันนั้นเรียบง่าย ("ค่า"; "PivotTable"; "Field1"; "Item1"; "Field2"; "Item2" เป็นต้น)
นั่นคือคุณสามารถแทนที่ค่าขององค์ประกอบด้วยตนเองด้วยลิงก์สัมพัทธ์ของตารางของคุณ
2) เพื่ออัปเดตข้อมูลอย่างต่อเนื่องและไม่แตะเค้าโครง ให้เพิ่มข้อมูลที่ด้านล่างของแผ่นงาน จากนั้นจะไม่มีอะไรรวบรวมข้อมูลให้คุณ และการอัปเดตจะเกิดขึ้นด้วยปุ่มเดียวเพื่ออัปเดตบน ST เอง นอกจากนี้ในแหล่งข้อมูลสำหรับ ST ให้ระบุคอลัมน์ทั้งหมดของแผ่นงาน (ตัวเลือกสันหลังยาว) หรือชื่อของช่วง ซึ่งขนาดจะถูกเปลี่ยนโดยพลการโดยไม่ต้องเข้าสู่การตั้งค่า ST
"ตากลัว แต่มือทำ"
กำลังโหลด...