ตาราง Pivot นั้นดีสำหรับทุกคน โดยจะคำนวณได้อย่างรวดเร็วและกำหนดค่าได้อย่างยืดหยุ่น และสามารถรวมการออกแบบเข้ากับมันได้อย่างสวยงาม หากจำเป็น แต่ครีมบางชนิดมีแมลงวันโดยเฉพาะอย่างยิ่งไม่สามารถสร้างบทสรุปได้ซึ่งพื้นที่ค่าไม่ควรมีตัวเลข แต่เป็นข้อความ
ให้พยายามหลีกเลี่ยงข้อจำกัดนี้และคิดหา "ไม้ค้ำยัน" ในสถานการณ์เดียวกัน
สมมติว่าบริษัทของเราขนส่งผลิตภัณฑ์ในคอนเทนเนอร์ไปยังหลายเมืองในประเทศของเราและคาซัคสถาน คอนเทนเนอร์จะถูกส่งไม่เกินเดือนละครั้ง แต่ละคอนเทนเนอร์มีตัวเลขและตัวอักษร เป็นข้อมูลเบื้องต้น มีตารางมาตรฐานแสดงรายการการจัดส่ง ซึ่งคุณต้องทำการสรุปบางอย่างเพื่อให้เห็นจำนวนตู้คอนเทนเนอร์ที่ส่งไปยังแต่ละเมืองและแต่ละเดือนอย่างชัดเจน:
เพื่อความสะดวก ให้สร้างตารางที่มีข้อมูลเริ่มต้น "ฉลาด" ไว้ล่วงหน้าโดยใช้คำสั่ง หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง) และตั้งชื่อให้เธอว่า การส่งมอบ แถบ นวกรรมิก (ออกแบบ). ในอนาคตสิ่งนี้จะทำให้ชีวิตง่ายขึ้นเพราะ จะสามารถใช้ชื่อของตารางและคอลัมน์ในสูตรได้โดยตรง
วิธีที่ 1 ง่ายที่สุด – ใช้ Power Query
Power Query เป็นเครื่องมือที่ทรงพลังสำหรับการโหลดและแปลงข้อมูลใน Excel Add-in นี้สร้างขึ้นใน Excel โดยค่าเริ่มต้นตั้งแต่ปี 2016 หากคุณมี Excel 2010 หรือ 2013 คุณสามารถดาวน์โหลดและติดตั้งแยกต่างหาก (ฟรีทั้งหมด)
เพื่อความชัดเจนของกระบวนการทั้งหมด ฉันวิเคราะห์ทีละขั้นตอนในวิดีโอต่อไปนี้:
ถ้าไม่สามารถใช้ Power Query ได้ คุณสามารถใช้วิธีอื่นๆ ผ่านตาราง Pivot หรือสูตรได้
วิธีที่ 2 สรุปเสริม
มาเพิ่มอีกหนึ่งคอลัมน์ในตารางเดิมของเรา โดยใช้สูตรง่ายๆ เราคำนวณจำนวนแต่ละแถวในตาราง:
เห็นได้ชัดว่าต้องการ -1 เพราะเรามีส่วนหัวแบบบรรทัดเดียวในตารางของเรา หากตารางของคุณไม่ได้อยู่ที่จุดเริ่มต้นของแผ่นงาน คุณสามารถใช้สูตรที่ซับซ้อนกว่าเล็กน้อย แต่เป็นสูตรสากลที่คำนวณความแตกต่างในตัวเลขของแถวปัจจุบันและส่วนหัวของตาราง:
ด้วยวิธีมาตรฐาน เราจะสร้างตารางเดือยประเภทที่ต้องการตามข้อมูลของเรา แต่ในฟิลด์ค่า เราจะปล่อยฟิลด์ หมายเลขบรรทัด แทนสิ่งที่เราต้องการ ภาชนะ:
เนื่องจากเราไม่มีตู้คอนเทนเนอร์หลายตู้ในเมืองเดียวกันในเดือนเดียวกัน ที่จริงแล้ว ข้อมูลสรุปของเราจะแจกให้ไม่ใช่จำนวน แต่เป็นหมายเลขบรรทัดของคอนเทนเนอร์ที่เราต้องการ
นอกจากนี้ คุณสามารถปิดยอดรวมและยอดย่อยบนแท็บ ตัวสร้าง – ผลรวมทั่วไป и ผลรวมย่อย (การออกแบบ — ผลรวมทั้งหมด, ผลรวมย่อย) และในที่เดียวกันให้สลับสรุปเป็นรูปแบบตารางที่สะดวกยิ่งขึ้นด้วยปุ่ม รายงานจำลอง (เค้าโครงรายงาน).
ดังนั้นเราจึงมาถึงครึ่งทางของผลลัพธ์แล้ว: เรามีตารางที่จุดตัดของเมืองและเดือนมีหมายเลขแถวในตารางต้นทางซึ่งรหัสคอนเทนเนอร์ที่เราต้องการอยู่
ตอนนี้ ให้คัดลอกสรุป (ไปยังแผ่นงานเดียวกันหรืออย่างอื่น) แล้ววางเป็นค่า จากนั้นป้อนสูตรของเราลงในพื้นที่ค่า ซึ่งจะแยกรหัสคอนเทนเนอร์ตามหมายเลขบรรทัดที่พบในสรุป:
ฟังก์ชัน IF (ถ้า)ในกรณีนี้ ให้ตรวจสอบว่าเซลล์ถัดไปในการสรุปไม่ว่างเปล่า หากว่างเปล่า ให้ส่งออกสตริงข้อความว่าง “” กล่าวคือปล่อยเซลล์ว่างไว้ ถ้าไม่ว่างให้แยกจากคอลัมน์ ภาชนะ ตารางแหล่งที่มา การส่งมอบ เนื้อหาเซลล์ตามหมายเลขแถวโดยใช้ฟังก์ชัน ดัชนี (ดัชนี).
บางทีจุดเดียวที่ไม่ชัดเจนมากที่นี่คือคำสองคำ ภาชนะ ในสูตร รูปแบบการเขียนที่แปลกประหลาดเช่นนี้:
เสบียง[[คอนเทนเนอร์]:[คอนเทนเนอร์]]
… จำเป็นสำหรับการอ้างอิงคอลัมน์เท่านั้น ภาชนะ เป็นแบบสัมบูรณ์ (เช่นการอ้างอิงที่มีเครื่องหมาย $ สำหรับตาราง "ไม่ฉลาด" ทั่วไป) และไม่ลื่นไถลไปยังคอลัมน์ที่อยู่ใกล้เคียงเมื่อคัดลอกสูตรของเราไปทางขวา
ในอนาคตเมื่อมีการเปลี่ยนแปลงข้อมูลในตารางต้นทาง การส่งมอบเราต้องอย่าลืมอัปเดตข้อมูลสรุปเสริมด้วยหมายเลขบรรทัดโดยคลิกขวาและเลือกคำสั่ง อัปเดตและบันทึก (รีเฟรช).
วิธีที่ 3 สูตร
วิธีนี้ไม่จำเป็นต้องสร้างตารางเดือยกลางและอัปเดตด้วยตนเอง แต่ใช้ "อาวุธหนัก" ของ Excel ซึ่งเป็นฟังก์ชัน ซัมเมสลิม (ซูมิฟส์). แทนที่จะค้นหาหมายเลขแถวในการสรุป คุณสามารถคำนวณได้โดยใช้สูตรนี้:
ด้วยความเทอะทะภายนอก อันที่จริง นี่เป็นกรณีการใช้งานมาตรฐานสำหรับฟังก์ชันการรวมแบบคัดเลือก ซัมเมสลิมA ที่รวมหมายเลขแถวสำหรับเมืองและเดือนที่ระบุ อีกครั้ง เนื่องจากเราไม่มีตู้คอนเทนเนอร์หลายตู้ในเมืองเดียวกันในเดือนเดียวกัน อันที่จริง ฟังก์ชันของเราจะไม่แจกแจงจำนวน แต่จะแจกหมายเลขบรรทัดเอง แล้วฟังก์ชันที่คุ้นเคยจากวิธีก่อนหน้านี้แล้ว ดัชนี คุณยังสามารถแยกรหัสคอนเทนเนอร์:
แน่นอน ในกรณีนี้ คุณไม่จำเป็นต้องคิดถึงการอัปเดตข้อมูลสรุปอีกต่อไป แต่สำหรับตารางขนาดใหญ่ ฟังก์ชัน ซัมเมสลี อาจช้าอย่างเห็นได้ชัด จากนั้นคุณจะต้องปิดการอัพเดทสูตรอัตโนมัติ หรือใช้วิธีการแรก – ตาราง Pivot
หากลักษณะสรุปไม่เหมาะกับรายงานของคุณมากนัก คุณสามารถแยกหมายเลขแถวออกจากตารางสุดท้ายได้ ไม่ใช่โดยตรงอย่างที่เราทำ แต่ใช้ฟังก์ชัน GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). วิธีการทำเช่นนี้สามารถพบได้ที่นี่
- วิธีสร้างรายงานโดยใช้ตารางสาระสำคัญ
- วิธีตั้งค่าการคำนวณในตารางเดือย
- การนับแบบเลือกด้วย SUMIFS, COUNTIFS เป็นต้น