วิธีจัดกลุ่มคอลัมน์ในตาราง Pivot หากข้อมูลตัวเลขไม่ถูกจัดกลุ่ม? การจัดกลุ่มฟิลด์ข้อความ

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

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

วิธีจัดกลุ่มข้อมูลในตาราง Pivot ใน Excel

ในการวิเคราะห์ยอดขายในแง่มุมต่างๆและการวิเคราะห์ให้จัดกลุ่มข้อมูลตามวันที่ตัวเลขหรือข้อความ

จัดกลุ่มวันที่ ดูผลรวมตามเดือนไตรมาสหรือปี ตัวอย่างเช่นในงบกำไรขาดทุนคุณต้องหาว่า บริษัท ทำเงินได้เท่าไหร่ต่อเดือน ในตาราง Pivot ให้วางฟิลด์ Item ในพื้นที่ Row Labels ฟิลด์ Profit ในพื้นที่ Values \u200b\u200bและวันที่ในพื้นที่ Column Labels เลือกเซลล์วันที่ใดก็ได้คลิกขวา ในเมนูบริบทคลิกกลุ่ม ในช่อง "With step" ให้เลือก "Month" (ดูรูปที่ 1 การจัดกลุ่มข้อมูลในรายงานตามเดือนและปี) โปรดทราบว่าโปรแกรมจะแจกจำนวนเงินทั้งหมดโดยไม่ระบุปี หากต้องการแยกตัวเลขตามเดือนและปีให้กดปุ่ม "Ctrl" ค้างไว้เลือก "เดือน" และ "ปี" คลิกตกลง

รูปที่ 1. การจัดกลุ่มข้อมูลในตาราง Pivot ตามเดือนและปี

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

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

รูปที่ 2. การจัดกลุ่มฟิลด์ตัวเลข

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

จะเกิดอะไรขึ้นถ้า Excel ไม่จัดกลุ่มข้อมูลในรายงาน Pivot และให้ข้อผิดพลาด

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

ในการแปลงรูปแบบของเซลล์ในตารางต้นฉบับ:

• ดูรายงาน Excel จะเน้นเซลล์ข้อความด้วยสามเหลี่ยมสีเขียวที่ดูเหมือนตัวเลข หากคุณเห็นเซลล์ดังกล่าวให้เลือกแล้วกด Ctrl + Shift + ลูกศรลง กลับไปที่เซลล์ที่ไฮไลต์แล้วคลิกไอคอนด้วย เครื่องหมายตกใจ... ในหน้าต่างที่เปิดขึ้นให้เลือก "แปลงเป็นตัวเลข"
• คัดลอกเซลล์ว่างบนแผ่นงาน เน้นวันที่หรือตัวเลข คลิกขวาเลือก " วางแบบพิเศษ". ในกล่องโต้ตอบตั้งค่า "ค่า" และ "เพิ่ม" (ดู. รูปที่ 3). คลิกตกลง

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

Helen Bradley อธิบายวิธีจัดกลุ่มข้อมูลใน Excel PivotTable เพื่อสรุปและวิเคราะห์ข้อมูลเพิ่มเติม

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

จัดกลุ่มตามค่า

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

ในตาราง Pivot แต่ละ id OrderID นำเสนอในบรรทัดแยกต่างหาก ข้อมูลดังกล่าวถือได้ว่าเป็นข้อมูลทั่วไป แต่ยังไม่เพียงพอสำหรับเรา

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

ตามค่าเริ่มต้น Excel แนะนำ เริ่มต้นที่ (เริ่มต้นที่) องค์ประกอบแรก OrderID: 10248 อย่างไรก็ตามคุณสามารถปรับแต่งหรือสร้างการจัดกลุ่มที่แตกต่างกันได้โดยตั้งค่าของคุณเองสำหรับพารามิเตอร์ เริ่มต้นที่ (เริ่มต้นด้วย). เริ่มต้นด้วยองค์ประกอบ 10240 ป้อนค่านี้ในฟิลด์ เริ่มต้นที่ (เริ่มต้นด้วย) - คุณสามารถตั้งค่าเริ่มต้นใดก็ได้แม้กระทั่งค่าที่ไม่อยู่ในข้อมูล

เรากำหนดค่าพารามิเตอร์สำหรับการจัดกลุ่มข้อมูล - จะถูกจัดกลุ่มตามฟิลด์ OrderID คำสั่งซื้อที่มีหมายเลขติดต่อกันสิบรายการ

ดังนั้นเราจึงกำหนดค่า เริ่มต้นที่ (เริ่มต้นด้วย) ออกจากค่า สิ้นสุดที่ (โดย) เช่น Excel แนะนำและทิ้งค่าไว้ โดย (มีขั้นตอน) เท่ากัน 10 เพราะเหมาะกับเรา คุณสามารถทำให้กลุ่มเล็กลงหรือใหญ่ขึ้นได้โดยการเปลี่ยนค่านี้เช่น 5, 20, 50 หรือ 100 ตอนนี้คลิก ตกลง... ข้อมูลจะถูกจัดกลุ่มตามค่า OrderID เป็นกลุ่มที่สะดวกในการประมวลผล

นี่คือลักษณะของข้อมูลโดยจัดกลุ่มตาม OrderID... อย่างที่คุณเห็นพวกเขามีลักษณะทั่วไปมากกว่าเมื่อก่อนมาก

จัดกลุ่มตามวันที่

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

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

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

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

เมื่อเลือกหลายช่องคุณสามารถรวมเป็นกลุ่มได้ตามสะดวก

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

หลังจากสร้างกลุ่มแล้วคุณสามารถเปลี่ยนชื่อได้ตามต้องการ

คุณสามารถขยายหรือยุบกลุ่มได้โดยคลิกที่สัญลักษณ์ เป็นบวก หรือ ลบ (+/-) ทางด้านซ้ายของชื่อกลุ่ม

การแก้ไขข้อผิดพลาด

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

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

กลุ่มรายละเอียด

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

การดับเบิลคลิกที่ค่าใด ๆ ในตาราง Pivot จะสร้างแผ่นงานใหม่ที่มีข้อมูลทั้งหมดที่เกี่ยวข้องกับค่านั้น

การจัดกลุ่มและช่วงการรวมหลายรายการ

คุณสามารถสร้างกลุ่มในตาราง Pivot ที่รวบรวมจากช่วงที่รวมไว้หลายช่วงตัวอย่างเช่นจากข้อมูลบน แผ่นงานที่แตกต่างกัน สมุดงาน ในการดำเนินการนี้ใน Excel 2007 และ 2010 ให้เพิ่มคำสั่ง (PivotTable และ Chart Wizard) ลงในแถบเครื่องมือด่วน

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

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

คำสั่งนี้ไม่รวมอยู่ในเมนู Excel 2010 แต่คุณสามารถเพิ่มลงในแถบเครื่องมือด่วนได้ด้วยตัวเอง

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

PivotTable และตัวช่วยสร้างแผนภูมิ ช่วยให้คุณสามารถรวมหลายช่วงพร้อมกันในตาราง Pivot เดียว

ขั้นตอนแรกคือการเลือกช่วงข้อมูลที่จะใช้

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

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

ใน PivotTable และ Chart Wizards คุณสามารถเชื่อมโยงช่วงกับเพจดังที่แสดงในภาพด้านบน - ไตรมาส (Quarters) และ ฤดูกาล (ฤดูกาล).

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

ภาพด้านบนแสดงให้เห็น: เราได้ย้ายเพจ หน้า 2 (ฤดูกาล) จากพื้นที่ ตัวกรองรายงาน (ฟิลเตอร์) ไปยังพื้นที่ ป้ายกำกับแถว (สตริง)

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