กฎสำหรับการสร้างตารางเดือย วิธีทำงานกับข้อมูล

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

เคล็ดลับที่ 1: อัปเดตตารางสรุปโดยอัตโนมัติ

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

1. คลิกขวาที่ตารางสรุปและเลือกจากเมนูบริบท ตัวเลือก PivotTable.
2. ในกล่องโต้ตอบที่ปรากฏขึ้น ตัวเลือก PivotTableเลือกแท็บ ข้อมูล.
3. ทำเครื่องหมายในช่อง รีเฟรชเมื่อเปิดไฟล์.

ข้าว. 1. เปิดใช้งานตัวเลือก รีเฟรชเมื่อเปิดไฟล์

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

ดาวน์โหลดบันทึกในรูปแบบหรือรูปแบบตัวอย่างในรูปแบบ (ไฟล์มีรหัส VBA)

เคล็ดลับ 2: อัปเดตตารางเดือยสมุดงานทั้งหมดพร้อมกัน

หากสมุดงานประกอบด้วยตาราง Pivot หลายตาราง การอัปเดตพร้อมกันอาจเป็นปัญหาได้ มีหลายวิธีในการเอาชนะปัญหาเหล่านี้:

วิธีที่ 1: คุณสามารถตั้งค่าตารางสรุปแต่ละตารางที่เป็นส่วนหนึ่งของสมุดงานให้อัปเดตโดยอัตโนมัติเมื่อคุณเปิดสมุดงาน (ดูรายละเอียดในเคล็ดลับที่ 1)

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

ย่อย Refresh_All()

สมุดงานนี้RefreshAll

เคล็ดลับ 3: เรียงลำดับรายการข้อมูลตามลำดับแบบสุ่ม

ในรูป รูปที่ 2 แสดงลำดับเริ่มต้นของขอบเขตที่แสดงใน PivotTable ภูมิภาคจะเรียงลำดับตามตัวอักษร: ตะวันตก เหนือ มิดเวสต์ และใต้ หากนโยบายบริษัทของคุณกำหนดให้แสดงภูมิภาคตะวันตกก่อน ตามด้วยภูมิภาคมิดเวสต์ เหนือ และใต้ ให้ดำเนินการจัดเรียงด้วยตนเอง เพียงป้อนมิดเวสต์ในเซลล์ C4 แล้วกดปุ่ม เข้า- ลำดับการจัดเรียงของภูมิภาคจะเปลี่ยนไป

เคล็ดลับ 4: แปลง PivotTable เป็นค่าฮาร์ดโค้ด

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

เมื่อต้องการแปลงส่วนของ PivotTable ให้ทำตามขั้นตอนเหล่านี้:

1. เลือกข้อมูล PivotTable ที่จะคัดลอก คลิกขวาและเลือกจากเมนูบริบท สำเนา(หรือพิมพ์ Ctrl+C บนคีย์บอร์ดของคุณ)
2. คลิกขวาที่ใดก็ได้บนแผ่นงานและเลือกคำสั่งจากเมนูบริบท แทรก(หรือพิมพ์ Ctrl+V)

หากคุณต้องการแปลง PivotTable ทั้งหมด ให้ทำตามขั้นตอนเหล่านี้:

1. เลือกตารางเดือยทั้งหมด คลิกขวาและเลือกจากเมนูบริบท สำเนา- หากตารางสรุปสาระสำคัญไม่มีพื้นที่ตัวกรอง คุณสามารถใช้แป้นพิมพ์ลัด Ctrl+Shift+* เพื่อเลือกพื้นที่ตารางสรุปได้
2. คลิกขวาที่ใดก็ได้บนแผ่นงานแล้วเลือกตัวเลือกจากเมนูบริบท เม็ดมีดพิเศษ.
3. เลือกตัวเลือก ค่านิยมและคลิก ตกลง.

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

เคล็ดลับ 5: การเติมเซลล์ว่างในฟิลด์ ROW

หลังจากแปลง PivotTable แล้ว แผ่นงานจะแสดงไม่เพียงแต่ค่าเท่านั้น แต่ยังแสดงโครงสร้างข้อมูลทั้งหมดของ PivotTable อีกด้วย ตัวอย่างเช่น ข้อมูลที่แสดงในรูปที่ 1 3 ได้มาจากพื้นฐานของตารางเดือยที่มีเค้าโครงในรูปแบบตาราง

ข้าว. 3. การใช้ตารางเดือยที่แปลงแล้วโดยไม่กรอกเซลล์ว่างทางด้านซ้ายถือเป็นปัญหา

โปรดทราบว่าสนาม ภูมิภาคและ ตลาดขายคงโครงสร้างแถวเดิมไว้เมื่อข้อมูลอยู่ในพื้นที่ ROWS ของตารางเดือย ใน Excel 2013 มีวิธีที่รวดเร็วในการเติมค่าลงในเซลล์ในพื้นที่ ROW คลิกในพื้นที่ตารางสรุปข้อมูล จากนั้นเลื่อนไปตามเมนู ตัวสร้าง -> เค้าโครงรายงาน -> (รูปที่ 4) จากนั้นคุณแปลงตาราง Pivot ให้เป็นค่าได้ ซึ่งจะทำให้ได้ตารางข้อมูลที่ไม่มีช่องว่าง

ข้าว. 4. หลังจากใช้งานคำสั่งแล้ว ทำซ้ำป้ายกำกับองค์ประกอบทั้งหมดเติมเซลล์ว่างทั้งหมดแล้ว

เคล็ดลับ 6: การจัดอันดับเขตข้อมูลตัวเลขใน PivotTable

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

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

เคล็ดลับ 7: ลดขนาดของรายงาน PivotTable

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

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

เคล็ดลับ 8: สร้างช่วงข้อมูลที่ขยายโดยอัตโนมัติ

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

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

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

เคล็ดลับ 9: เปรียบเทียบตารางปกติโดยใช้ PivotTable

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

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

เคล็ดลับ 10: กรอง PivotTable โดยอัตโนมัติ

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

เคล็ดลับ 11: แปลงชุดข้อมูลที่แสดงใน PivotTable

เค้าโครงที่ดีที่สุดสำหรับแหล่งข้อมูลที่แปลงเป็น PivotTable คือเค้าโครงแบบตาราง เค้าโครงประเภทนี้มีลักษณะดังต่อไปนี้: ไม่มีแถวหรือคอลัมน์ว่าง แต่ละคอลัมน์มีส่วนหัว แต่ละฟิลด์มีค่าที่สอดคล้องกันในแต่ละแถว และคอลัมน์ไม่มีกลุ่มข้อมูลที่ซ้ำกัน ในทางปฏิบัติ เรามักจะพบชุดข้อมูลที่คล้ายคลึงกับสิ่งที่แสดงไว้ในรูปที่ 1 13. อย่างที่คุณเห็น ชื่อเดือนจะปรากฏเป็นแถวตามขอบด้านบนของตาราง โดยทำหน้าที่เป็นป้ายกำกับคอลัมน์และข้อมูลจริง ในตารางสรุปที่สร้างจากตารางลักษณะนี้ จะส่งผลให้ต้องจัดการ 12 ช่อง โดยแต่ละช่องแสดงถึงเดือนที่แตกต่างกัน

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

ขั้นตอนที่ 1: รวมฟิลด์ที่ไม่ใช่คอลัมน์ทั้งหมดไว้ในคอลัมน์เดียวหากต้องการสร้างตารางสรุปข้อมูลที่มีช่วงรวมหลายช่วง คุณต้องสร้างคอลัมน์มิติเดียว ในตัวอย่างนี้ ทุกอย่างที่ไม่ได้อยู่ในฟิลด์เดือนจะถือเป็นมิติ ดังนั้นทุ่งนา ตลาดขายและ คำอธิบายของบริการควรรวมเป็นคอลัมน์เดียว หากต้องการรวมช่องต่างๆ ให้เป็นคอลัมน์เดียว เพียงป้อนสูตรที่เชื่อมช่องทั้งสองเข้าด้วยกันโดยใช้เครื่องหมายอัฒภาคเป็นตัวคั่น ตั้งชื่อคอลัมน์ใหม่ สูตรที่ป้อนจะแสดงในแถบสูตร (รูปที่ 14)

ข้าว. 14. ผลลัพธ์ของการต่อคอลัมน์ ตลาดขายและ คำอธิบายของบริการ

หลังจากสร้างคอลัมน์ที่ต่อกันแล้ว ให้แปลงสูตรเป็นค่า เมื่อต้องการทำเช่นนี้ ให้เลือกคอลัมน์ที่สร้างขึ้นใหม่ กด Ctrl+C จากนั้นรันคำสั่ง แทรก -> เม็ดมีดพิเศษ -> ค่านิยม- ตอนนี้คุณสามารถลบคอลัมน์ออกได้ ตลาดขายและ คำอธิบายของบริการ(รูปที่ 15)

ข้าว. 15. ลบคอลัมน์แล้ว ตลาดขายและ คำอธิบายของบริการ

ขั้นตอนที่ 2: สร้างตารางสรุปที่มีช่วงการรวมหลายช่วงตอนนี้คุณต้องเรียก Pivot Table และ Chart Wizard ซึ่งเป็นที่คุ้นเคยสำหรับผู้ใช้หลายคนจาก Excel เวอร์ชันก่อนหน้า หากต้องการเรียกวิซาร์ดนี้ ให้กดคีย์ผสม Alt+D+P ขออภัย การกดคีย์ผสมนี้มีไว้สำหรับ Excel เวอร์ชันภาษาอังกฤษ 2013 ในเวอร์ชันรัสเซีย จะสอดคล้องกับคีย์ผสม Alt+D+N แต่ด้วยเหตุผลที่ฉันไม่ทราบ มันไม่ทำงาน อย่างไรก็ตาม คุณสามารถแสดง Pivot Table Wizard แบบเก่าที่ดีบนแถบเครื่องมือด่วนได้ โปรดดู หลังจากเริ่มตัวช่วยสร้าง ให้เลือกสวิตช์ ในช่วงการรวมหลายช่วง- คลิก ไกลออกไป- ตั้งสวิตช์ สร้างฟิลด์หน้าและคลิก ไกลออกไป- กำหนดระยะการทำงานแล้วคลิก พร้อม(ดูรายละเอียด). คุณจะสร้างตารางเดือย (รูปที่ 16)

ขั้นตอนที่ 3: ดับเบิลคลิกที่จุดตัดของแถวและคอลัมน์ในแถวผลรวมทั้งหมดในขั้นตอนนี้ คุณจะมีตารางสรุป (รูปที่ 16) ซึ่งรวมถึงช่วงการรวมหลายช่วงซึ่งไม่มีประโยชน์ในทางปฏิบัติ เลือกเซลล์ที่จุดตัดของแถวและคอลัมน์ผลรวมทั้งหมด แล้วดับเบิลคลิก (ในตัวอย่างของเรา เซลล์ N88) คุณจะได้รับแผ่นงานใหม่ซึ่งมีโครงสร้างคล้ายกับโครงสร้างที่แสดงในรูปที่ 1 17. เอกสารนี้จริงๆ แล้วเป็นเวอร์ชันที่ย้ายข้อมูลต้นฉบับ

ขั้นตอนที่ 4 การแยกคอลัมน์แถวออกเป็นช่องแยกกันมันยังคงแยกคอลัมน์ เส้นลงในช่องแยก (กลับสู่โครงสร้างเดิม) เพิ่มคอลัมน์ว่างหนึ่งคอลัมน์ต่อจากคอลัมน์นั้น เส้น- ไฮไลต์คอลัมน์ A จากนั้นไปที่แท็บ Ribbon ข้อมูลและคลิกที่ปุ่ม ข้อความคอลัมน์- กล่องโต้ตอบจะปรากฏขึ้นบนหน้าจอ ตัวช่วยสำหรับการกระจายข้อความลงในคอลัมน์- ในขั้นตอนแรก ให้เลือกปุ่มตัวเลือก มีตัวคั่นและคลิกที่ปุ่มถัดไป ในขั้นตอนถัดไป ให้เลือกปุ่มตัวเลือก อัฒภาคและคลิก พร้อม- จัดรูปแบบข้อความ เพิ่มชื่อเรื่อง และเปลี่ยนข้อมูลต้นฉบับเป็นตารางโดยกด Ctrl+T (รูปที่ 18)

ข้าว. 18. ชุดข้อมูลนี้เหมาะอย่างยิ่งสำหรับการสร้างตารางเดือย (เทียบกับรูปที่ 13)

เคล็ดลับ 12: รวมรูปแบบตัวเลขสองรูปแบบใน PivotTable

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

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

เพื่อแก้ไขปัญหานี้ มีการใช้รูปแบบตัวเลขแบบกำหนดเองเพื่อจัดรูปแบบค่าใดๆ ที่มากกว่า 1.5 เป็นตัวเลข ถ้าค่าน้อยกว่า 1.5 จะถูกจัดรูปแบบเป็นเปอร์เซ็นต์ ในกล่องโต้ตอบ รูปแบบเซลล์เลือกแท็บ (ทุกรูปแบบ)และในสนาม พิมพ์ป้อนสตริงรูปแบบต่อไปนี้ (รูปที่ 21): [>=1.5]$# ##0; -<1,5]0,0%

ข้าว. 21. ใช้รูปแบบตัวเลขแบบกำหนดเองซึ่งมีการจัดรูปแบบตัวเลขใดๆ ที่น้อยกว่า 1.5 เป็นเปอร์เซ็นต์

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

เคล็ดลับ 13: สร้างการกระจายความถี่สำหรับตารางสาระสำคัญ

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

คลิกขวาที่ค่าใดๆ ในพื้นที่แถวแล้วเลือกตัวเลือกจากเมนูบริบท กลุ่ม- ในกล่องโต้ตอบ การจัดกลุ่ม(รูปที่ 24) กำหนดค่าของพารามิเตอร์ที่กำหนดจุดเริ่มต้นจุดสิ้นสุดและขั้นตอนของการแจกแจงความถี่ คลิกตกลง

ข้าว. 24. ในกล่องโต้ตอบ การจัดกลุ่มกำหนดค่าพารามิเตอร์การกระจายความถี่

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

ข้าว. 25. ตอนนี้คุณมีการกระจายธุรกรรมของลูกค้าตามขนาดการสั่งซื้อ (เป็นดอลลาร์)

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

เคล็ดลับ 14: ใช้ตารางสรุปเพื่อกระจายชุดข้อมูลไปยังแผ่นงานในสมุดงาน

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

ข้าว. 28. คลิกที่ปุ่ม แสดงหน้าตัวกรองรายงาน

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

ข้าว. 29. กล่องโต้ตอบ การแสดงหน้าตัวกรองรายงาน

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

เคล็ดลับ 15: ใช้ PivotTable เพื่อกระจายชุดข้อมูลไปยังสมุดงานแต่ละเล่ม

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

รหัสวีบีเอ

ตารางระเบิดย่อย()

Dim PvtItem เป็น PivotItem

Dim PvtTable เป็น PivotTable

Dim strfield เป็น PivotField

'การเปลี่ยนแปลงตัวแปรตามสคริปต์

ConststrFieldName = "ตลาดการขาย" ‘<—Изменение имени поля

Const strTriggerRange = "A4" ‘<—Изменение диапазона триггера

'เปลี่ยนชื่อตารางเดือย (ถ้าจำเป็น)

SetPvtTable = ActiveSheet.PivotTables(" PivotTable1 ") '<—Изменение названия сводной

'วนซ้ำแต่ละองค์ประกอบของฟิลด์ที่เลือก

สำหรับ PvtItem แต่ละรายการใน PvtTable.PivotFields(strFieldName).PivotItems

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

ช่วง(strTriggerRange).ShowDetail = True

'การตั้งชื่อแผ่นงานชั่วคราว

ActiveSheet.Name = "TempSheet"

'การคัดลอกข้อมูลไปยังสมุดงานใหม่และการลบแผ่นงานชั่วคราว

ActiveSheet.Cells.คัดลอก

ActiveSheet วาง

Cells.EntireColumn.ปรับพอดีอัตโนมัติ

Application.DisplayAlerts = เท็จ

ActiveWorkbook.SaveAs_

ชื่อไฟล์:=ThisWorkbook.Path & " \ " & PvtItem.Name & " .xlsx "

ActiveWorkbook ปิด

ชีต (" Tempsheet "). ลบ

Application.DisplayAlerts = จริง

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

• Const strFieldName. ชื่อของฟิลด์ที่ใช้แยกข้อมูล กล่าวอีกนัยหนึ่ง คือฟิลด์ที่วางไว้ในพื้นที่หน้าตารางตัวกรอง/ตาราง Pivot
• Const strTriggerRange. เซลล์ทริกเกอร์ที่เก็บตัวเลขตัวเดียวจากพื้นที่ข้อมูล PivotTable ในกรณีของเรา เซลล์ทริกเกอร์คือ A4 (ดูรูปที่ 31)

ผลจากการรันโค้ด VBA ข้อมูลสำหรับตลาดการขายแต่ละแห่งจะถูกบันทึกไว้ในสมุดงานแยกต่างหาก

บันทึกนี้เขียนขึ้นจากหนังสือของ Jelen, Alexander - บทที่ 14

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

การสร้างตาราง Pivot

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

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

1. ช่วง (อาจอยู่ในหนังสือเล่มอื่น);
2. ตารางข้อมูล (ระบุชื่อ)
3. ข้อมูลจากแหล่งภายนอกที่ได้รับโดยการสืบค้น SQL จากฐานข้อมูล ฯลฯ

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

การจัดการรายการเขตข้อมูลตาราง

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

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

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

หากต้องการกำหนดพื้นที่ให้กับฟิลด์เฉพาะ เพียงลากฟิลด์ด้วยเมาส์
ตัวอย่างเช่น เราจะใช้ตารางที่แสดงการเปลี่ยนแปลงของอัตราแลกเปลี่ยนเงินดอลลาร์ต่อรูเบิลเป็นเวลา 9 เดือน:

เดือน	วันที่	จำนวน	ดี	เปลี่ยน
มกราคม	10.01.2013	1	30,4215	0,0488
มกราคม	11.01.2013	1	30,3650	-0,0565
มกราคม	12.01.2013	1	30,2537	-0,1113
...	...	...	...	...
กันยายน	28.09.2013	1	32,3451	0,1715

จำเป็นต้องสร้างตารางเดือยใน Excel ที่คำนวณอัตราแลกเปลี่ยนเฉลี่ยในแต่ละเดือน (สร้างตารางว่างไว้แล้วที่จุดเริ่มต้นของส่วนนี้)

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

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

เขตข้อมูล PivotTable จากการคำนวณ

หากการดำเนินการและการคำนวณที่ให้ไว้ไม่เพียงพอ Excel จะอนุญาตให้คุณสร้างเขตข้อมูลจากการคำนวณของคุณเองในตารางเดือย ในการดำเนินการนี้ ให้เลือกเซลล์จากพื้นที่ตาราง ไปที่แท็บ "ตัวเลือก" ("การวิเคราะห์" สำหรับ Excel 2013) ของ Ribbon ที่ปรากฏขึ้น ถัดไปในส่วน "เครื่องมือ" คลิกที่ไอคอน "สูตร" จากเมนูแบบเลื่อนลง (ในเวอร์ชัน 2010 และสูงกว่าเส้นทางจะแตกต่างออกไป: ส่วน "การคำนวณ" -> รายการแบบเลื่อนลง "ฟิลด์องค์ประกอบและชุด" ”) เลือก “ฟิลด์จากการคำนวณ...” หน้าต่างควรปรากฏขึ้น:

ตั้งชื่อที่จำง่าย และเขียนสูตรโดยใช้ฟังก์ชันใดๆ (โปรดทราบว่าเขตข้อมูลจากการคำนวณใช้ไม่ได้กับข้อความ) ตามตัวอย่าง ลองคูณอัตราแลกเปลี่ยนด้วย 1,000 แล้วลบ 13 เปอร์เซ็นต์ (=Rate*1000*0.87) ตั้งชื่อฟิลด์ว่า "ZP" เพิ่มลงในพื้นที่ค่าและใช้ค่าสูงสุดเป็นการดำเนินการ ตรวจสอบมุมมองรายงานใหม่:

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

ตัวเลือกตาราง Pivot ใน Excel

เพื่อศึกษาหัวข้อเพิ่มเติมเราจะสร้างตารางที่ซับซ้อนมากขึ้น (หลักการก่อสร้างไม่แตกต่างจากที่กล่าวไว้ก่อนหน้านี้)
แหล่งข้อมูลแสดงรายการ 100 บรรทัด โดยแต่ละรายการสะท้อนถึงเงินเดือนของพนักงานในอุตสาหกรรมต่างๆ ในบางภูมิภาค:

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

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

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

ขณะนี้ PivotTable ที่เสร็จสมบูรณ์จะมีลักษณะเช่นนี้ในแผ่นงาน Excel:

นอกเหนือจากคุณสมบัติที่กล่าวถึงข้างต้น คุณสามารถตั้งค่าต่อไปนี้ผ่านพารามิเตอร์ตาราง:

1. ชื่อตารางสรุป
2. การรวมและจัดแนวลายเซ็น
3. การแสดงค่าสำหรับเซลล์ว่าง
4. เปลี่ยนความกว้างของคอลัมน์โดยอัตโนมัติ

1. เรามีฐานข้อมูลพนักงานของบริษัททรานส์ มีข้อมูลธุรกรรมทางการเงินจำนวนมากซึ่งมีความยาวมากกว่า 150 บรรทัด หน้าที่ของเราคือสร้างรายงานต้นทุนสำหรับเงินเดือนและเบี้ยประกันสำหรับแต่ละแผนกเท่านั้น การดำเนินการด้วยตนเองจะต้องใช้ความพยายามและเวลาอย่างมาก ดังนั้นเราจะดำเนินการให้แตกต่างออกไป เมื่อยืนอยู่บนเซลล์ของฐานแล้ว มาเริ่มสร้าง Pivot Table กัน: แทรก - ตารางสาระสำคัญ.

2. ในหน้าต่าง "การสร้างตาราง Pivot" ที่ปรากฏขึ้นโดยไม่ทำการเปลี่ยนแปลงใด ๆ ให้คลิกตกลง

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

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

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

คำแนะนำ:
เมื่อสร้างตารางสรุปข้อมูล ให้ใช้ตารางอัจฉริยะ ในกรณีนี้ คุณสามารถสร้างตารางสรุปข้อมูลได้โดยใช้คำสั่ง Design - Pivot Table

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

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

ตารางสาระสำคัญใน Excel

ตัวอย่างเช่น เราใช้ตารางยอดขายผลิตภัณฑ์ในสาขาการขายต่างๆ

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

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

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

รายละเอียดที่สำคัญ:

• ต้องเติมบรรทัดแรกของช่วงที่ระบุสำหรับการลดข้อมูล
• ในตารางพื้นฐาน แต่ละคอลัมน์ควรมีส่วนหัวของตัวเอง - การตั้งค่ารายงานสรุปทำได้ง่ายกว่า
• ใน Excel คุณสามารถใช้ตาราง Access, SQL Server ฯลฯ เป็นแหล่งข้อมูลได้



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

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

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

มาสร้างรายงานโดยใช้ Pivot Table Wizard:

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

วิธีทำงานกับตารางเดือยใน Excel

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

ทางด้านขวาของตารางสรุป เรามีบานหน้าต่างงานที่เราเลือกคอลัมน์จากรายการฟิลด์ ถ้ามันหายไปเพียงคลิกที่เครื่องหมาย

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

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

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

แต่จะเกิดอะไรขึ้นถ้าเราลบ "วันที่" และเพิ่ม "แผนก":

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

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

เราวางช่อง "แผนก" ไว้หน้าชื่อผลิตภัณฑ์ การใช้ส่วน "ย้ายไปที่จุดเริ่มต้น" ของเมนู

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

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

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

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

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

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

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

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

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

อัตราค่าไฟฟ้า * จำนวนคน / อ่านมิเตอร์ / พื้นที่

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

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

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

แหล่งข้อมูลคือตารางที่มีหลายสิบหลายร้อยแถว หลายตารางในหนังสือเล่มเดียว หลายไฟล์ ให้เราจำลำดับการสร้าง: "Insert" - "Tables" - "Pivot Table"

และในบทความนี้เราจะดูวิธีทำงานกับตารางเดือยใน Excel

วิธีสร้างตารางเดือยจากหลายไฟล์

ขั้นตอนแรกคือการอัปโหลดข้อมูลลงใน Excel และจัดแนวให้ตรงกับตาราง Excel หากข้อมูลของเราอยู่ใน Worde เราจะถ่ายโอนข้อมูลไปยัง Excel และสร้างตารางตามกฎของ Excel ทั้งหมด (กำหนดส่วนหัวให้กับคอลัมน์ ลบแถวว่าง ฯลฯ)

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

เราเพียงแค่สร้างรายงานสรุปตามข้อมูลในช่วงการรวมหลายช่วง

การสร้างตารางสรุปโดยอิงจากตารางต้นฉบับที่มีโครงสร้างแตกต่างกันนั้นยากกว่ามาก ตัวอย่างเช่น:

ตารางแรกคือการมาถึงของสินค้า ประการที่สองคือจำนวนหน่วยที่ขายในร้านค้าต่างๆ เราจำเป็นต้องรวมสองตารางนี้เป็นรายงานเดียวเพื่อแสดงยอดคงเหลือ ยอดขายในร้าน รายได้ ฯลฯ

ตัวช่วยสร้างตาราง Pivot จะสร้างข้อผิดพลาดกับพารามิเตอร์เริ่มต้นเหล่านี้ เนื่องจากมีการละเมิดเงื่อนไขหลักประการหนึ่งของการรวมบัญชี - ชื่อคอลัมน์ที่เหมือนกัน

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

เทมเพลตรายงานสรุปจะเปิดขึ้นพร้อมกับรายการฟิลด์ที่สามารถแสดงได้

เรามาแสดงปริมาณสินค้าที่ขายกันดีกว่า

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



ข้อมูลรายละเอียดในตารางสาระสำคัญ

จากรายงาน (ดูด้านบน) เราพบว่ามีการขายการ์ดแสดงผลเพียง 30 ใบเท่านั้น หากต้องการทราบว่าข้อมูลใดที่ใช้เพื่อให้ได้ค่านี้ ให้ดับเบิลคลิกที่หมายเลข “30” เราได้รับรายงานโดยละเอียด:

จะอัปเดตข้อมูลในตารางสาระสำคัญของ Excel ได้อย่างไร

หากเราเปลี่ยนพารามิเตอร์ใดๆ ในตารางต้นฉบับหรือเพิ่มบันทึกใหม่ ข้อมูลนี้จะไม่ปรากฏในรายงานสรุป สถานการณ์นี้ไม่เหมาะกับเรา

การอัปเดตข้อมูล:

เคอร์เซอร์ต้องอยู่ในเซลล์ใดก็ได้ของรายงานสรุป

หรือ:

ปุ่มเมาส์ขวา - รีเฟรช

หากต้องการตั้งค่าการอัปเดตตารางสรุปอัตโนมัติเมื่อข้อมูลมีการเปลี่ยนแปลง ให้ทำตามคำแนะนำ:

การเปลี่ยนโครงสร้างรายงาน

มาเพิ่มฟิลด์ใหม่ให้กับตารางสรุป:

หลังจากเปลี่ยนช่วงแล้ว ช่อง "การขาย" จะปรากฏในข้อมูลสรุป

จะเพิ่มเขตข้อมูลจากการคำนวณลงในตารางสาระสำคัญได้อย่างไร

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

นี่คือคอลัมน์เสมือนที่สร้างขึ้นจากการคำนวณ สามารถแสดงค่าเฉลี่ย เปอร์เซ็นต์ และความคลาดเคลื่อนได้ นั่นก็คือผลลัพธ์ของสูตรต่างๆ ข้อมูลเขตข้อมูลจากการคำนวณจะโต้ตอบกับข้อมูล PivotTable

คำแนะนำในการเพิ่มฟิลด์ที่กำหนดเอง:

การจัดกลุ่มข้อมูลในรายงานสรุป

เช่น ลองคำนวณต้นทุนสินค้าในปีต่างๆ จำนวนเงินที่ใช้ไปในปี 2012, 2013, 2014 และ 2015 การจัดกลุ่มตามวันที่ในตารางเดือย Excel ทำได้ดังนี้ เช่น มาสรุปง่ายๆ ตามวันที่และจำนวนที่จัดส่ง

คลิกขวาที่วันที่ใดก็ได้ เลือกคำสั่ง "กลุ่ม"

ในกล่องโต้ตอบที่เปิดขึ้น ให้ตั้งค่าพารามิเตอร์การจัดกลุ่ม วันที่เริ่มต้นและสิ้นสุดของช่วงจะแสดงโดยอัตโนมัติ เลือกขั้นตอน – “ปี”

เราได้รับจำนวนการสั่งซื้อตามปี

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