ฟังก์ชันรับข้อมูลของตารางเดือยทำงานอย่างไร

ก่อนหน้านี้ฉันได้พูดถึงข้อเท็จจริงที่ว่าเมื่อพูดถึงเซลล์ ตารางเดือยแทนที่จะเป็นการอ้างอิงปกติ ฟังก์ชัน GET.PIVOTO.TABLE.DATA จะถูกส่งคืน (ดู ) ถ้าคุณสนใจ ยังไงเพื่อหลีกเลี่ยงความไม่สะดวกนี้ ฉันขอแนะนำให้อ้างอิงถึงบันทึกที่กล่าวถึง ถ้าคุณสนใจ, ทำไมสิ่งนี้เกิดขึ้น และอะไรคือด้านบวกของฟังก์ชัน GET.DATA.PIVOTO.TABLE จากนั้นฉันขอเสนอส่วนหนึ่งของหนังสือโดย Jelen, Alexander (บทที่ 15) เทคนิคที่ได้รับการพิจารณาจะช่วยให้คุณสามารถรับมือกับปัญหามากมายที่ทำให้ผู้ใช้ตารางเดือยปวดหัวโดยเฉพาะอย่างยิ่ง:

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

อันที่จริง ทุกสิ่งที่อธิบายในที่นี้ไม่ใช่เรื่องใหม่ นอกจากนี้ มีการใช้เทคนิคที่คล้ายกันตั้งแต่ Excel 2002 อย่างไรก็ตาม การสื่อสารกับผู้ใช้ของฉันแสดงว่ามีน้อยกว่า 1% ที่คุ้นเคยกับพวกเขา คำถามเดียวที่ผู้ใช้มีคือจะปิดฟีเจอร์ GET.PIVOTO.TABLE.DATA แปลกๆ ได้อย่างไร น่าเสียดาย…

ดาวน์โหลดบันทึกในรูปแบบหรือตัวอย่างในรูปแบบ

มาเริ่มกันเลยดีกว่า

วิธีละทิ้งฟังก์ชันที่มีปัญหา GET.DATA.PIVOTO.TABLE

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

สมมติว่าในตารางเดือยที่แสดงในรูป 1 คุณต้องเปรียบเทียบข้อมูลสำหรับปี 2558 และ 2557

ข้าว. 1. ตารางเดือยเริ่มต้น

1. เพิ่มหัวข้อ "% การเจริญเติบโต" ในเซลล์ D3
2. คัดลอกรูปแบบจากเซลล์ C3 ไปยังเซลล์ D3
3. ในเซลล์ D4 ให้ใส่เครื่องหมายเท่ากับ
4. คลิกที่เซลล์ C4
5. ใส่เครื่องหมาย / (สแลช) เพื่อระบุการดำเนินการหาร
6. คลิกที่เซลล์ B4
7. พิมพ์ -1 แล้วกดคีย์ผสม ให้อยู่ในเซลล์เดียวกัน จัดรูปแบบผลลัพธ์เป็นเปอร์เซ็นต์ คุณจะเห็นว่าภูมิภาคตะวันตกมีรายได้ลดลง 43.8% (รูปที่ 2) ผลลัพธ์ไม่ค่อยดีนัก
8. หลังจากป้อนสูตรแรกเสร็จแล้ว ให้เลือกเซลล์ D4
9. ดับเบิลคลิกที่สี่เหลี่ยมเล็ก ๆ ที่มุมล่างขวาของเซลล์ กล่องนี้แสดงถึงจุดจับเติมที่สามารถใช้เพื่อคัดลอกสูตรเพื่อเติมคอลัมน์รายงานทั้งหมด

หลังจากคัดลอกสูตรเสร็จแล้ว เมื่อดูที่หน้าจอ คุณจะเข้าใจว่ามีบางอย่างผิดปกติ - แต่ละภูมิภาคมีการลดลง 43.8% ต่อปี (รูปที่ 3)

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

มันแทบจะไม่เกิดขึ้นใน ชีวิตจริง. ผู้ใช้ทุกคนจะบอกคุณว่าหลังจากดำเนินการข้างต้น การกระทำของ Excelจะสร้างสูตร =C4/B4–1 กลับไปที่เซลล์ D4 และให้ความสนใจกับแถบสูตร (รูปที่ 4) มันเป็นเพียงสิ่งที่น่ารังเกียจ! ไม่มีสูตรง่ายๆ =C4/B4–1 อีกต่อไป โปรแกรมแทนที่โครงสร้างที่ซับซ้อนด้วยฟังก์ชัน GET.PIVOTAL.TABLE.DATA เหตุใดสูตรนี้จึงให้ผลลัพธ์ที่ถูกต้องในเซลล์ D4 แต่เมื่อคัดลอกไปยังเซลล์ด้านล่างกลับใช้งานไม่ได้

ปฏิกิริยาแรกของผู้ใช้ต่อสิ่งที่เกิดขึ้นจะเป็นดังนี้: "อะไรคือโครงสร้าง GET.DATA.PIVOTO.TABLE แปลกๆ ที่ทำให้รายงานของฉันยุ่งเหยิง" ผู้ใช้ส่วนใหญ่จะต้องการกำจัดคุณลักษณะนี้ทันที บางคนจะถามคำถาม: "เหตุใด Microsoft จึงให้คุณลักษณะนี้แก่เรา"

ไม่มีอะไรแบบนั้นเลย เก่งครั้ง 2000 เมื่อฉันเริ่มเห็น GET.DATA.PIVOTOMATIC.TABLE เป็นประจำ ฉันแค่เกลียดมัน เมื่อมีคนถามฉันในการสัมมนาครั้งหนึ่งว่าสามารถใช้ให้เกิดประโยชน์ได้อย่างไร ฉันรู้สึกทึ่ง ฉันไม่เคยถามคำถามแบบนี้กับตัวเองเลย! ในความคิดของฉันและตามความเห็นของผู้ใช้ Excel ส่วนใหญ่ ฟังก์ชัน GET.DATA.PIVOTAL.TABLE เป็นผลผลิตจากความชั่วร้ายที่ไม่มีส่วนเกี่ยวข้องกับพลังแห่งความดี โชคดีที่มีสองวิธีในการปิดใช้งานคุณสมบัตินี้

การบล็อกฟังก์ชัน GET.PIVOTO.TABLE.DATA โดยการป้อนสูตรมีวิธีง่ายๆ ในการป้องกันไม่ให้ฟังก์ชัน GET.DATA.PIVOTO.TABLE ปรากฏขึ้น ในการทำเช่นนี้ คุณต้องสร้างสูตรโดยไม่ต้องใช้เมาส์หรือปุ่มเคอร์เซอร์ เพียงทำตามขั้นตอนเหล่านี้

1. ไปที่เซลล์ D4 แล้วพิมพ์ = (เครื่องหมายเท่ากับ)
2. ใส่ C4
3. พิมพ์ / (เครื่องหมายทับเพื่อแบ่ง)
4. ป้อน B4
5. ป้อน -1
6. คลิก เข้า.

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

ข้าว. 5. เพียงพิมพ์ =С4/В4–1 จากแป้นพิมพ์และสูตรจะทำงานตามที่ควร

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

ปิดใช้งานฟังก์ชัน GET.PIVOTO.TABLE.DATAคุณสามารถปิดใช้งานฟังก์ชัน GET.PIVOTO.TABLE.DATA อย่างถาวรได้ คลิกที่แถบเมนู ไฟล์ → ตัวเลือก. ในหน้าต่างที่เปิดอยู่ ตัวเลือกเก่งไปที่แท็บ สูตรและยกเลิกการเลือกตัวเลือก ใช้ฟังก์ชั่นGetPivotData สำหรับลิงก์ PivotTable. คลิก ตกลง.

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

เหตุใด Microsoft จึงเสนอฟังก์ชัน GET.PIVOTO.TABLE.DATA ให้เราหากฟีเจอร์นี้แย่มาก ทำไมนักพัฒนาซอฟต์แวร์ของ Microsoft จึงเปิดใช้งานเป็นค่าเริ่มต้น เหตุใดพวกเขาจึงสนใจที่จะรักษาการสนับสนุนฟีเจอร์นี้ใน Excel เวอร์ชันใหม่กว่า พวกเขาตระหนักถึงความรู้สึกของผู้ใช้หรือไม่? และเราไปสู่สิ่งที่น่าสนใจที่สุด ...

การใช้ฟังก์ชัน GET.PIVOTO.TABLE.DATA เพื่อปรับปรุงตารางสาระสำคัญ

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

เทคนิค PivotTable ใหม่ที่แนะนำโดย Rob Colley (นักพัฒนาของ Microsoft) และกล่าวถึงด้านล่างเป็นการปรับปรุงกระบวนการที่อธิบายไว้ข้างต้น ในกรณีนี้ จะมีการสร้าง pivot table ดั้งเดิมขึ้นมาก่อน ตารางนี้ไม่จำเป็นต้องจัดรูปแบบ จากนั้น ทำตามขั้นตอนหนึ่งขั้นตอนที่ค่อนข้างใช้เวลานานเพื่อสร้างเชลล์ที่จัดรูปแบบอย่างสวยงามซึ่งจะมีรายงานขั้นสุดท้าย หลังจากนั้น ฟังก์ชัน GET.DATA.PIVOTOMATIC.TABLE จะใช้เพื่อเติมข้อมูลลงในรายงานอย่างรวดเร็ว หลังจากได้รับข้อมูลใหม่แล้ว คุณสามารถวางไว้บนแผ่นงาน อัปเดตตารางเดือยดั้งเดิม และพิมพ์รายงานที่อยู่ในเปลือก (รูปที่ 9) เทคนิคนี้มีข้อดีมากมายที่ไม่อาจปฏิเสธได้ ตัวอย่างเช่น คุณไม่ต้องกังวลกับการจัดรูปแบบรายงานทันทีหลังจากที่สร้างเสร็จ กระบวนการสร้าง pivot table กลายเป็นระบบอัตโนมัติเกือบทั้งหมด

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

การสร้างตารางเดือยดั้งเดิมข้อมูลเริ่มต้น (รูปที่ 10) แสดงในรูปแบบของธุรกรรมที่มีข้อมูลเกี่ยวกับตัวบ่งชี้ที่วางแผนไว้และตามจริงสำหรับแต่ละภูมิภาคที่มีสาขาของบริษัท ตัวบ่งชี้ที่วางแผนไว้มีรายละเอียดในระดับเดือนและตามจริง - ในระดับของแต่ละวัน ตัวบ่งชี้ที่วางแผนไว้ถูกสร้างขึ้นสำหรับปีข้างหน้า และตามจริง - สำหรับเดือนที่ผ่านมา เนื่องจากรายงานจะได้รับการอัปเดตทุกเดือน กระบวนการนี้จึงง่ายขึ้นอย่างมากหากแหล่งข้อมูล PivotTable มีขนาดใหญ่ขึ้นเมื่อมีการเพิ่มข้อมูลใหม่ที่ด้านล่าง ในเวอร์ชันเก่า การสร้างเอ็กเซลแหล่งข้อมูลดังกล่าวดำเนินการโดยใช้ช่วงไดนามิกที่มีชื่อโดยใช้ฟังก์ชัน OFFSET (สำหรับรายละเอียดเพิ่มเติม โปรดดูที่) เมื่อทำงานใน Excel 2013 เพียงเลือกเซลล์ข้อมูลเซลล์ใดเซลล์หนึ่งแล้วกด Ctrl+T (สร้างตาราง) ชุดข้อมูลที่ตั้งชื่อจะปรากฏขึ้น ขยายโดยอัตโนมัติเมื่อมีการเพิ่มแถวและคอลัมน์ใหม่

ตอนนี้เรามาสร้างเดือยตารางกัน ฟังก์ชัน GET.PIVOT.TABLE. มีประสิทธิภาพมาก แต่สามารถคืนค่าที่ปรากฏในตาราง Pivot จริงเท่านั้น ฟังก์ชันนี้ไม่สามารถตระเวนแคชเพื่อคำนวณรายการที่ไม่อยู่ใน PivotTable

สร้างตารางเดือย:

1. เลือกทีม แทรก → ตารางเดือยจากนั้นในกล่องโต้ตอบ สร้าง PivotTableคลิก ตกลง.
2. ในรายการ เขตข้อมูล PivotTable ให้เลือกเขตข้อมูล วันที่. รายการวันที่จะปรากฏทางด้านซ้ายของตารางเดือย (รูปที่ 11)
3. เลือกเซลล์วันที่ใดก็ได้ เช่น A4 บนแท็บบริบท การวิเคราะห์ซึ่งอยู่ในชุดของแท็บบริบท การทำงานกับ pivot tableให้คลิกปุ่ม จัดกลุ่มตามเขตข้อมูล(สำหรับรายละเอียดดู). ในกล่องโต้ตอบ การจัดกลุ่มเลือกตัวเลือก เดือน(รูปที่ 12) คลิก ตกลง. ชื่อของเดือนจะปรากฏทางด้านซ้ายของตารางเดือย (รูปที่ 13)
4. กล่องลาก วันที่ในพื้นที่คอลัมน์ PivotTable
5. กล่องลาก ดัชนีไปยังพื้นที่คอลัมน์ของรายการเขตข้อมูล PivotTable
6. เลือกฟิลด์ ภูมิภาคซึ่งจะแสดงในคอลัมน์ด้านซ้ายของตารางเดือย
7. เลือกฟิลด์ รายได้ซึ่งจะปรากฏในพื้นที่ PivotTable Values

ข้าว. 11. เริ่มด้วยการจัดกลุ่มตามฟิลด์ วันที่

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

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

ทำตามขั้นตอนต่อไปนี้ (รูปที่ 15)

1. ในเซลล์ A1 ให้ป้อนชื่อรายงาน - ตัวบ่งชี้ที่วางแผนไว้และตามจริงตามภูมิภาค.
2. ไปที่แท็บ บ้านคลิกที่ปุ่ม รูปแบบของเซลล์เลือกรูปแบบ หัวข้อ 1.
3. ในเซลล์ A2 ให้ป้อนสูตร = MONTH (TODAY (); 0) ฟังก์ชันนี้ส่งคืนวันสุดท้าย เดือนนี้. ตัวอย่างเช่น หากคุณกำลังอ่านข้อมูลนี้ในวันที่ 14 สิงหาคม 2014 เซลล์ A2 จะเป็นวันที่ 31 สิงหาคม 2014
4. เลือกเซลล์ A2 กดคีย์ผสม Ctrl+1 เพื่อแสดงกล่องโต้ตอบ รูปแบบเซลล์. บนแท็บ ตัวเลขคลิกที่รายการ ทุกรูปแบบ. ป้อนรูปแบบตัวเลขที่กำหนดเองในแบบฟอร์ม "ตั้งแต่เดือน" MMMM "ตัวบ่งชี้ที่วางแผนไว้"(รูปที่ 16) ด้วยเหตุนี้ วันที่ที่คำนวณได้จะมีลักษณะเป็นข้อความ
5. ในเซลล์ A5 ให้ป้อนหัวเรื่อง ภูมิภาค.
6. ป้อนชื่อภูมิภาคในเซลล์ที่เหลือในคอลัมน์ A ชื่อภูมิภาคต้องตรงกับชื่อภูมิภาคใน PivotTable
7. หากจำเป็น ให้เพิ่มป้ายชื่อในคอลัมน์สำหรับผลรวมของแผนก
8. เพิ่มบรรทัดที่ด้านล่างของรายงาน รวมสำหรับบริษัท.
9. ในเซลล์ B4 ให้ป้อนสูตร =DATE(YEAR($A$2),COLUMN(A1),1) สูตรนี้ส่งคืนวันที่ 01/01/2014, 02/01/2104 เป็นต้น ซึ่งเป็นวันแรกของทั้ง 12 เดือนของปีปัจจุบัน
10. เลือกเซลล์ B4 กดคีย์ผสม Ctrl+1 เพื่อเปิดหน้าต่าง รูปแบบเซลล์. บนแท็บ ตัวเลขในบท ทุกรูปแบบป้อนรูปแบบตัวเลขที่กำหนดเอง มม. รูปแบบนี้แสดงชื่อตัวอักษรสามตัวของเดือน จัดข้อความชิดขอบเซลล์ด้านขวา
11. คัดลอกเนื้อหาของเซลล์ B4 ไปยังช่วง C4:M4 แถวที่มีชื่อเดือนจะแสดงที่ด้านบนของตารางเดือย
12. ในเซลล์ B5 ให้ป้อนสูตร =IF(เดือน(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово ข้อเท็จจริงและสำหรับปัจจุบันและอนาคต - วางแผน.
13. เพิ่มหัวเรื่องไปที่เซลล์ N5 ผล. ในเซลล์ O4 - ผล, O5 - วางแผน, Р5 - % ส่วนเบี่ยงเบน.
14. ป้อนสูตร Excel ปกติที่ใช้ในการคำนวณผลรวมของแผนก แถวผลรวมของบริษัท คอลัมน์ผลรวมทั้งหมด และคอลัมน์ % ผลต่าง:
    1. ในเซลล์ B8 ให้ป้อนสูตร = SUM (B6: B7) แล้วคัดลอกไปยังเซลล์อื่นในแถว
    2. ในเซลล์ N6 ให้ป้อนสูตร = SUM (B6: M6) แล้วคัดลอกไปยังเซลล์อื่นของคอลัมน์
    3. ในเซลล์ P6 ให้ป้อนสูตร =IFERROR((N6/O10)-1;0) แล้วคัดลอกไปยังเซลล์อื่นของคอลัมน์
    4. ในเซลล์ B13 ให้ป้อนสูตร = SUM (B10: B12) แล้วคัดลอกไปยังเซลล์อื่นในแถว
    5. ในเซลล์ B17 ให้ป้อนสูตร = SUM (B15: B16) แล้วคัดลอกไปยังเซลล์อื่นในแถว
    6. ในเซลล์ B19 ให้ป้อนสูตร =SUM(B6:B18)/2 แล้วคัดลอกไปยังเซลล์อื่นๆ ในแถว
15. นำสไตล์หัวเรื่อง 4 ไปใช้กับคำบรรยายในคอลัมน์ A และกับหัวเรื่องในแถวที่ 4 และ 5
16. สำหรับช่วงเซลล์ B6:O19 ให้เลือกรูปแบบตัวเลข ###0
17. สำหรับเซลล์ในคอลัมน์ P ให้เลือกรูปแบบตัวเลข 0.0%

ดังนั้นเราจึงได้สร้างตัวห่อรายงานที่แสดงในรูป 15. รายงานนี้มีรูปแบบที่จำเป็นทั้งหมด ส่วนต่อไปนี้จะสาธิตวิธีใช้ฟังก์ชัน GET.PIVOTAL.TABLE DATA เพื่อทำรายงานให้เสร็จสมบูรณ์

ข้าว. 15. ห่อรายงานก่อนเพิ่มสูตร

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

เลือกเซลล์ B6 ของเชลล์รายงาน เซลล์นี้สอดคล้องกับภาคตะวันออกเฉียงเหนือและตัวเลขจริงสำหรับเดือนมกราคม

1. พิมพ์ = (เครื่องหมายเท่ากับ) เพื่อเริ่มป้อนสูตร
2. ไปที่แผ่นงานจากตารางเดือยแล้วคลิกที่เซลล์ที่ตรงกับภาคตะวันออกเฉียงเหนือและตัวเลขจริงสำหรับเดือนมกราคม - C12 (รูปที่ 17)
3. กดปุ่ม เข้าเพื่อสิ้นสุดการป้อนสูตรและกลับไปที่เชลล์รายงาน ด้วยเหตุนี้ Excel จะเพิ่มฟังก์ชัน GET.PICTABLE.TABLE DATA ลงในเซลล์ B6 เซลล์จะแสดง $277,435

จำหมายเลขนี้ไว้เนื่องจากคุณจะต้องใช้เมื่อเปรียบเทียบกับผลลัพธ์ของสูตรที่คุณจะแก้ไขในภายหลัง สูตรที่โปรแกรมสร้างมีรูปแบบดังนี้: =GET.DATA.PIVOTAL.TABLE(" Income " ;'Fig. 11-14′!$A$3; "Region" ; "North-East" ; "Date" ; 1; "ตัวบ่งชี้"; "ข้อเท็จจริง"). หากคุณเพิกเฉยต่อฟังก์ชัน GET.PIVOTAL.TABLE จนถึงตอนนี้ ได้เวลาทำความรู้จักกับฟังก์ชันนี้ให้ดีขึ้นแล้ว บนมะเดื่อ 18 สูตรนี้จะแสดงในโหมดแก้ไขพร้อมกับคำใบ้

อาร์กิวเมนต์ของฟังก์ชัน:

• Data_field. เขตข้อมูลจากพื้นที่ค่า PivotTable โปรดทราบ: ในกรณีนี้ จะใช้ฟิลด์นี้ รายได้, แต่ไม่ จำนวนเงินในช่อง รายได้.
• PivotTable ด้วยตัวเลือกนี้ Microsoft จะถามคุณว่า "PivotTable ใดที่คุณต้องการใช้" การระบุหนึ่งในเซลล์ของตาราง Pivot ก็เพียงพอแล้ว รายการ 'รูปที่ 11-14'!$A$3 หมายถึงเซลล์แรกใน PivotTable ที่ป้อนข้อมูล เนื่องจากในกรณีของเรา คุณสามารถตั้งค่าเซลล์ใดๆ ที่เกี่ยวข้องกับ pivot table ได้ ไม่ต้องเปลี่ยนอาร์กิวเมนต์ ที่อยู่เซลล์ $A$3 เหมาะกับทุกวิถีทาง
• สนาม 1; องค์ประกอบ 1 ในสูตรที่สร้างขึ้นโดยอัตโนมัติ ชื่อจะถูกเลือกเป็นชื่อฟิลด์ ภูมิภาคและเป็นค่าฟิลด์ - ภาคตะวันออกเฉียงเหนือ. นี่คือสาเหตุของปัญหาที่เกิดขึ้นเมื่อทำงานกับฟังก์ชัน GET.PIVOT.TABLE DATA ไม่สามารถคัดลอกค่าที่เลือกอัตโนมัติได้เนื่องจากเป็นค่าฮาร์ดโค้ด ดังนั้น หากคุณคัดลอกสูตรในพื้นที่รายงานทั้งหมด คุณจะต้องเปลี่ยนด้วยตนเอง สำหรับภาคตะวันออกเฉียงเหนือ ให้แทนที่การอ้างอิงเซลล์เป็น $A6 การระบุเครื่องหมายดอลลาร์หน้าชื่อคอลัมน์ A แสดงว่าคุณกำหนดว่าส่วนแถวของข้อมูลอ้างอิงสามารถเปลี่ยนแปลงได้เมื่อคัดลอกสูตรลงในเซลล์ของคอลัมน์
• สนาม 2; องค์ประกอบ 2 อาร์กิวเมนต์คู่นี้ระบุฟิลด์ วันที่โดยมีค่าเป็น 1 หากตาราง Pivot เดิมจัดกลุ่มตามเดือน ฟิลด์เดือนจะคงชื่อฟิลด์เดิมไว้ วันที่. ค่าตัวเลขของเดือนคือ 1 ซึ่งตรงกับเดือนมกราคม ไม่แนะนำให้ใช้ค่าดังกล่าวเมื่อสร้างสูตรขนาดใหญ่ที่ตั้งค่าเป็นสิบหรือหลายร้อยเซลล์รายงาน ควรใช้สูตรที่คำนวณค่าฟิลด์ วันที่เช่นเดียวกับสูตรในเซลล์ B4 แทน 1 ในกรณีนี้ คุณสามารถใช้สูตร MONTH (B $ 4) เครื่องหมายดอลลาร์ก่อนหน้า 4 ระบุว่าสูตรสามารถกำหนดค่าให้กับฟิลด์ได้ วันที่ตามเดือนอื่นๆ เนื่องจากสูตรถูกคัดลอกลงในเซลล์แถว
• สนาม 3; องค์ประกอบ 3 ในกรณีนี้ ชื่อฟิลด์จะถูกกำหนดโดยอัตโนมัติ ดัชนีและค่าฟิลด์ ข้อเท็จจริง. ค่าเหล่านี้ถูกต้องสำหรับเดือนมกราคม แต่ในเดือนต่อๆ ไป ค่าของฟิลด์จะต้องเปลี่ยนเป็นแผน เปลี่ยนค่าฟิลด์ฮาร์ดโค้ด ข้อเท็จจริงเพื่อเชื่อมโยง B$5
• สนาม 4; องค์ประกอบ 4 อาร์กิวเมนต์เหล่านี้ไม่ได้ใช้เพราะ สนามหมดแล้ว

สูตรใหม่แสดงในรูป 19. ในหนึ่งนาที แทนที่จะเป็นสูตรฮาร์ดโค้ดที่ออกแบบมาเพื่อทำงานกับค่าเดียว สูตรที่ยืดหยุ่นได้ถูกสร้างขึ้นซึ่งสามารถคัดลอกไปยังเซลล์ทั้งหมดในชุดข้อมูลได้ กดปุ่ม เข้าและคุณจะได้ผลลัพธ์เหมือนกับก่อนที่จะแก้ไขสูตร สูตรที่แก้ไขจะกลายเป็น: =GET.PIVOTOMAT.TABLE DATA("Revenue" ;'Fig. 11-14′!$A$3; "Region" ;$A6; "Date" ;MONTH(B$4); " Indicator " ; 5 บาท)

ข้าว. 19. เมื่อแก้ไขเสร็จแล้ว สูตร GET.DATA.PIVOTO.TABLE เหมาะสำหรับการคัดลอกไปยังทุกเซลล์ของช่วง

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

ในขั้นตอนถัดไป เราจะตั้งค่าสูตร GET.PIVOTO.TABLE.DATA เพื่อคำนวณตัวบ่งชี้ที่วางแผนไว้ทั้งหมด เพียงคัดลอกสูตรลงในเซลล์ O6 จะแสดง #REF! สาเหตุของข้อผิดพลาดนี้คือคำว่า ผลในเซลล์ O4 ไม่ใช่ชื่อเดือน เพื่อให้แน่ใจว่าการทำงานของฟังก์ชัน GET.PIVOTO.TABLE.DATA ถูกต้อง ค่าที่ต้องการจะต้องอยู่ในตาราง Pivot แต่เนื่องจากในตารางเดือยเดิมฟิลด์ ดัชนีเป็นฟิลด์ที่สองในพื้นที่คอลัมน์ คอลัมน์ข้อมูล ผลลัพธ์ของแผนขาดอยู่จริง ย้ายกล่อง ดัชนีเพื่อให้กลายเป็นอันแรกในพื้นที่คอลัมน์ (รูปที่ 20)

ข้าว. 20. ปรับเค้าโครงของฟิลด์ในพื้นที่คอลัมน์เพื่อให้คอลัมน์ปรากฏขึ้น แผนผลลัพธ์

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

กลับไปที่เชลล์ชีตของรายงาน ยืนในเซลล์ O6 พิมพ์ = (=) และอ้างถึงเซลล์ N12 บนชีตของตาราง Pivot ที่สอดคล้องกับผลรวมที่วางแผนไว้สำหรับภูมิภาคตะวันออกเฉียงเหนือ คลิก เข้า. ผลลัพธ์คือสูตร = GET. DATA. OF. A. PIVOT. TABLE("Income"; 'Fig. 11-14′! $A$3; "Region"; "North-East"; "Indicator"; " วางแผน"). แก้ไข: =GET SUMMARY.TABLE.DATA("รายได้";'รูปที่ 11-14′!$A$3;"ภูมิภาค";$A6;"ตัวบ่งชี้";O$5) คัดลอกสูตรนี้ไปยังเซลล์อื่นในคอลัมน์ O (รูปที่ 21) โปรดทราบว่าแม้ในขณะที่ย้ายพื้นที่ต่างๆ ของรายงาน PivotTable ตัวตัดคำจะทำงานได้อย่างถูกต้อง แน่นอน หากคุณทำให้บางฟิลด์ของข้อมูลสรุปไม่ทำงาน เชลล์จะไม่สามารถรับมือกับสิ่งนี้ได้ ...

ข้าว. 21. รายงานขั้นสุดท้ายที่สามารถนำเสนอต่อผู้จัดการ

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

รายงานการปรับปรุงหากต้องการอัปเดตรายงานด้วยข้อมูลสำหรับเดือนต่อๆ ไป ให้ทำตามขั้นตอนเหล่านี้:

1. แทรกค่าจริงใต้ชุดข้อมูลต้นฉบับ เนื่องจากมีการเลือกรูปแบบตารางสำหรับแหล่งข้อมูล การจัดรูปแบบตารางจึงกระจายไปยังแถวใหม่ของข้อมูลโดยอัตโนมัติ นอกจากนี้ยังขยายคำจำกัดความของ PivotTable ดั้งเดิม (ฉันได้เพิ่มค่าจริงสำหรับทั้งปีในไฟล์ Excel แล้ว)
2. ไปที่ตารางเดือย คลิกขวาและเลือก รีเฟรช. รูปลักษณ์ของตารางเดือยจะเปลี่ยนไป แต่ก็ไม่น่ากลัว
3. ไปที่กระดาษห่อรายงาน โดยหลักการแล้วทุกอย่างได้ดำเนินการไปแล้วเพื่ออัปเดตรายงาน แต่จะไม่รบกวนการทดสอบผลลัพธ์ เปลี่ยนสูตรในเซลล์ A2 เช่น =OMONTH(TODAY() +31 ;0) และดูว่าเกิดอะไรขึ้น

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

ฉันไม่คิดว่าฉันจะพูดอะไรต่อไปนี้: "GET.PIVOTO.TABLE.Function เป็นประโยชน์มากที่สุด เราอยู่ได้อย่างไรหากไม่มีมันมาก่อน?

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

ก่อนหน้านี้ ฉันได้พูดถึงความจริงที่ว่าเมื่ออ้างถึงเซลล์ตารางเดือย แทนที่จะเป็นการอ้างอิงปกติ ฟังก์ชัน GET.PIVOTO.TABLE.DATA จะถูกส่งกลับ (ดู) ถ้าคุณสนใจ ยังไงเพื่อหลีกเลี่ยงความไม่สะดวกนี้ ฉันขอแนะนำให้อ้างอิงถึงบันทึกที่กล่าวถึง ถ้าคุณสนใจ, ทำไมสิ่งนี้เกิดขึ้น และอะไรคือด้านบวกของฟังก์ชัน GET.DATA.PIVOTO.TABLE จากนั้นฉันขอเสนอส่วนหนึ่งของหนังสือโดย Jelen, Alexander (บทที่ 15) เทคนิคที่ได้รับการพิจารณาจะช่วยให้คุณสามารถรับมือกับปัญหามากมายที่ทำให้ผู้ใช้ตารางเดือยปวดหัวโดยเฉพาะอย่างยิ่ง:

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

อันที่จริง ทุกสิ่งที่อธิบายในที่นี้ไม่ใช่เรื่องใหม่ นอกจากนี้ มีการใช้เทคนิคที่คล้ายกันตั้งแต่ Excel 2002 อย่างไรก็ตาม การสื่อสารกับผู้ใช้ของฉันแสดงว่ามีน้อยกว่า 1% ที่คุ้นเคยกับพวกเขา คำถามเดียวที่ผู้ใช้มีคือจะปิดฟีเจอร์ GET.PIVOTO.TABLE.DATA แปลกๆ ได้อย่างไร น่าเสียดาย…

ดาวน์โหลดบันทึกในรูปแบบหรือตัวอย่างในรูปแบบ

มาเริ่มกันเลยดีกว่า

วิธีละทิ้งฟังก์ชันที่มีปัญหา GET.DATA.PIVOTO.TABLE

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

สมมติว่าในตารางเดือยที่แสดงในรูป 1 คุณต้องเปรียบเทียบข้อมูลสำหรับปี 2558 และ 2557

ข้าว. 1. ตารางเดือยเริ่มต้น

1. เพิ่มหัวข้อ "% การเจริญเติบโต" ในเซลล์ D3
2. คัดลอกรูปแบบจากเซลล์ C3 ไปยังเซลล์ D3
3. ในเซลล์ D4 ให้ใส่เครื่องหมายเท่ากับ
4. คลิกที่เซลล์ C4
5. ใส่เครื่องหมาย / (สแลช) เพื่อระบุการดำเนินการหาร
6. คลิกที่เซลล์ B4
7. พิมพ์ -1 แล้วกดคีย์ผสม ให้อยู่ในเซลล์เดียวกัน จัดรูปแบบผลลัพธ์เป็นเปอร์เซ็นต์ คุณจะเห็นว่าภูมิภาคตะวันตกมีรายได้ลดลง 43.8% (รูปที่ 2) ผลลัพธ์ไม่ค่อยดีนัก
8. หลังจากป้อนสูตรแรกเสร็จแล้ว ให้เลือกเซลล์ D4
9. ดับเบิลคลิกที่สี่เหลี่ยมเล็ก ๆ ที่มุมล่างขวาของเซลล์ กล่องนี้แสดงถึงจุดจับเติมที่สามารถใช้เพื่อคัดลอกสูตรเพื่อเติมคอลัมน์รายงานทั้งหมด

หลังจากคัดลอกสูตรเสร็จแล้ว เมื่อดูที่หน้าจอ คุณจะเข้าใจว่ามีบางอย่างผิดปกติ - แต่ละภูมิภาคมีการลดลง 43.8% ต่อปี (รูปที่ 3)

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

มันแทบจะไม่เกิดขึ้นในชีวิตจริง ผู้ใช้ทุกคนจะบอกคุณว่าหลังจากทำตามขั้นตอนข้างต้นแล้ว Excel จะสร้างสูตร =C4/B4-1 กลับไปที่เซลล์ D4 และให้ความสนใจกับแถบสูตร (รูปที่ 4) มันเป็นเพียงสิ่งที่น่ารังเกียจ! ไม่มีสูตรง่ายๆ =C4/B4–1 อีกต่อไป โปรแกรมแทนที่โครงสร้างที่ซับซ้อนด้วยฟังก์ชัน GET.PIVOTAL.TABLE.DATA เหตุใดสูตรนี้จึงให้ผลลัพธ์ที่ถูกต้องในเซลล์ D4 แต่เมื่อคัดลอกไปยังเซลล์ด้านล่างกลับใช้งานไม่ได้

ปฏิกิริยาแรกของผู้ใช้ต่อสิ่งที่เกิดขึ้นจะเป็นดังนี้: "อะไรคือโครงสร้าง GET.DATA.PIVOTO.TABLE แปลกๆ ที่ทำให้รายงานของฉันยุ่งเหยิง" ผู้ใช้ส่วนใหญ่จะต้องการกำจัดคุณลักษณะนี้ทันที บางคนจะถามคำถาม: "เหตุใด Microsoft จึงให้คุณลักษณะนี้แก่เรา"

ไม่มีอะไรแบบนี้ในสมัยของ Excel 2000 เมื่อฉันเริ่มเห็นฟังก์ชัน GET.DATA.PIVOTE.TABLE เป็นประจำ ฉันก็เกลียดมัน เมื่อมีคนถามฉันในการสัมมนาครั้งหนึ่งว่าสามารถใช้ให้เกิดประโยชน์ได้อย่างไร ฉันรู้สึกทึ่ง ฉันไม่เคยถามคำถามแบบนี้กับตัวเองเลย! ในความคิดของฉันและตามความเห็นของผู้ใช้ Excel ส่วนใหญ่ ฟังก์ชัน GET.DATA.PIVOTAL.TABLE เป็นผลผลิตจากความชั่วร้ายที่ไม่มีส่วนเกี่ยวข้องกับพลังแห่งความดี โชคดีที่มีสองวิธีในการปิดใช้งานคุณสมบัตินี้

การบล็อกฟังก์ชัน GET.PIVOTO.TABLE.DATA โดยการป้อนสูตรมีวิธีง่ายๆ ในการป้องกันไม่ให้ฟังก์ชัน GET.DATA.PIVOTO.TABLE ปรากฏขึ้น ในการทำเช่นนี้ คุณต้องสร้างสูตรโดยไม่ต้องใช้เมาส์หรือปุ่มเคอร์เซอร์ เพียงทำตามขั้นตอนเหล่านี้

1. ไปที่เซลล์ D4 แล้วพิมพ์ = (เครื่องหมายเท่ากับ)
2. ใส่ C4
3. พิมพ์ / (เครื่องหมายทับเพื่อแบ่ง)
4. ป้อน B4
5. ป้อน -1
6. คลิก เข้า.

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

ข้าว. 5. เพียงพิมพ์ =С4/В4–1 จากแป้นพิมพ์และสูตรจะทำงานตามที่ควร

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

ปิดใช้งานฟังก์ชัน GET.PIVOTO.TABLE.DATAคุณสามารถปิดใช้งานฟังก์ชัน GET.PIVOTO.TABLE.DATA อย่างถาวรได้ คลิกที่แถบเมนู ไฟล์ → ตัวเลือก. ในหน้าต่างที่เปิดอยู่ ตัวเลือกเก่งไปที่แท็บ สูตรและยกเลิกการเลือกตัวเลือก ใช้ฟังก์ชั่นGetPivotData สำหรับลิงก์ PivotTable. คลิก ตกลง.

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

เหตุใด Microsoft จึงเสนอฟังก์ชัน GET.PIVOTO.TABLE.DATA ให้เราหากฟีเจอร์นี้แย่มาก ทำไมนักพัฒนาซอฟต์แวร์ของ Microsoft จึงเปิดใช้งานเป็นค่าเริ่มต้น เหตุใดพวกเขาจึงสนใจที่จะรักษาการสนับสนุนฟีเจอร์นี้ใน Excel เวอร์ชันใหม่กว่า พวกเขาตระหนักถึงความรู้สึกของผู้ใช้หรือไม่? และเราไปสู่สิ่งที่น่าสนใจที่สุด ...

การใช้ฟังก์ชัน GET.PIVOTO.TABLE.DATA เพื่อปรับปรุงตารางสาระสำคัญ

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

เทคนิค PivotTable ใหม่ที่แนะนำโดย Rob Colley (นักพัฒนาของ Microsoft) และกล่าวถึงด้านล่างเป็นการปรับปรุงกระบวนการที่อธิบายไว้ข้างต้น ในกรณีนี้ จะมีการสร้าง pivot table ดั้งเดิมขึ้นมาก่อน ตารางนี้ไม่จำเป็นต้องจัดรูปแบบ จากนั้น ทำตามขั้นตอนหนึ่งขั้นตอนที่ค่อนข้างใช้เวลานานเพื่อสร้างเชลล์ที่จัดรูปแบบอย่างสวยงามซึ่งจะมีรายงานขั้นสุดท้าย หลังจากนั้น ฟังก์ชัน GET.DATA.PIVOTOMATIC.TABLE จะใช้เพื่อเติมข้อมูลลงในรายงานอย่างรวดเร็ว หลังจากได้รับข้อมูลใหม่แล้ว คุณสามารถวางไว้บนแผ่นงาน อัปเดตตารางเดือยดั้งเดิม และพิมพ์รายงานที่อยู่ในเปลือก (รูปที่ 9) เทคนิคนี้มีข้อดีมากมายที่ไม่อาจปฏิเสธได้ ตัวอย่างเช่น คุณไม่ต้องกังวลกับการจัดรูปแบบรายงานทันทีหลังจากที่สร้างเสร็จ กระบวนการสร้าง pivot table กลายเป็นระบบอัตโนมัติเกือบทั้งหมด

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

การสร้างตารางเดือยดั้งเดิมข้อมูลเริ่มต้น (รูปที่ 10) แสดงในรูปแบบของธุรกรรมที่มีข้อมูลเกี่ยวกับตัวบ่งชี้ที่วางแผนไว้และตามจริงสำหรับแต่ละภูมิภาคที่มีสาขาของบริษัท ตัวบ่งชี้ที่วางแผนไว้มีรายละเอียดในระดับเดือนและตามจริง - ในระดับของแต่ละวัน ตัวบ่งชี้ที่วางแผนไว้ถูกสร้างขึ้นสำหรับปีข้างหน้า และตามจริง - สำหรับเดือนที่ผ่านมา เนื่องจากรายงานจะได้รับการอัปเดตทุกเดือน กระบวนการนี้จึงง่ายขึ้นอย่างมากหากแหล่งข้อมูล PivotTable มีขนาดใหญ่ขึ้นเมื่อมีการเพิ่มข้อมูลใหม่ที่ด้านล่าง ใน Excel เวอร์ชันเก่า การสร้างแหล่งข้อมูลดังกล่าวดำเนินการโดยใช้ช่วงไดนามิกที่มีชื่อโดยใช้ฟังก์ชัน OFFSET (สำหรับรายละเอียดเพิ่มเติม โปรดดูที่) เมื่อทำงานใน Excel 2013 เพียงเลือกเซลล์ข้อมูลเซลล์ใดเซลล์หนึ่งแล้วกด Ctrl+T (สร้างตาราง) ชุดข้อมูลที่ตั้งชื่อจะปรากฏขึ้น ขยายโดยอัตโนมัติเมื่อมีการเพิ่มแถวและคอลัมน์ใหม่

ตอนนี้เรามาสร้างเดือยตารางกัน ฟังก์ชัน GET.PIVOT.TABLE. มีประสิทธิภาพมาก แต่สามารถคืนค่าที่ปรากฏในตาราง Pivot จริงเท่านั้น ฟังก์ชันนี้ไม่สามารถตระเวนแคชเพื่อคำนวณรายการที่ไม่อยู่ใน PivotTable

สร้างตารางเดือย:

1. เลือกทีม แทรก → ตารางเดือยจากนั้นในกล่องโต้ตอบ สร้าง PivotTableคลิก ตกลง.
2. ในรายการ เขตข้อมูล PivotTable ให้เลือกเขตข้อมูล วันที่. รายการวันที่จะปรากฏทางด้านซ้ายของตารางเดือย (รูปที่ 11)
3. เลือกเซลล์วันที่ใดก็ได้ เช่น A4 บนแท็บบริบท การวิเคราะห์ซึ่งอยู่ในชุดของแท็บบริบท การทำงานกับ pivot tableให้คลิกปุ่ม จัดกลุ่มตามเขตข้อมูล(สำหรับรายละเอียดดู). ในกล่องโต้ตอบ การจัดกลุ่มเลือกตัวเลือก เดือน(รูปที่ 12) คลิก ตกลง. ชื่อของเดือนจะปรากฏทางด้านซ้ายของตารางเดือย (รูปที่ 13)
4. กล่องลาก วันที่ในพื้นที่คอลัมน์ PivotTable
5. กล่องลาก ดัชนีไปยังพื้นที่คอลัมน์ของรายการเขตข้อมูล PivotTable
6. เลือกฟิลด์ ภูมิภาคซึ่งจะแสดงในคอลัมน์ด้านซ้ายของตารางเดือย
7. เลือกฟิลด์ รายได้ซึ่งจะปรากฏในพื้นที่ PivotTable Values

ข้าว. 11. เริ่มด้วยการจัดกลุ่มตามฟิลด์ วันที่

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

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

ทำตามขั้นตอนต่อไปนี้ (รูปที่ 15)

1. ในเซลล์ A1 ให้ป้อนชื่อรายงาน - ตัวบ่งชี้ที่วางแผนไว้และตามจริงตามภูมิภาค.
2. ไปที่แท็บ บ้านคลิกที่ปุ่ม รูปแบบของเซลล์เลือกรูปแบบ หัวข้อ 1.
3. ในเซลล์ A2 ให้ป้อนสูตร = MONTH (TODAY (); 0) ฟังก์ชันนี้ส่งคืนวันสุดท้ายของเดือนปัจจุบัน ตัวอย่างเช่น หากคุณกำลังอ่านข้อมูลนี้ในวันที่ 14 สิงหาคม 2014 เซลล์ A2 จะเป็นวันที่ 31 สิงหาคม 2014
4. เลือกเซลล์ A2 กดคีย์ผสม Ctrl+1 เพื่อแสดงกล่องโต้ตอบ รูปแบบเซลล์. บนแท็บ ตัวเลขคลิกที่รายการ ทุกรูปแบบ. ป้อนรูปแบบตัวเลขที่กำหนดเองในแบบฟอร์ม "ตั้งแต่เดือน" MMMM "ตัวบ่งชี้ที่วางแผนไว้"(รูปที่ 16) ด้วยเหตุนี้ วันที่ที่คำนวณได้จะมีลักษณะเป็นข้อความ
5. ในเซลล์ A5 ให้ป้อนหัวเรื่อง ภูมิภาค.
6. ป้อนชื่อภูมิภาคในเซลล์ที่เหลือในคอลัมน์ A ชื่อภูมิภาคต้องตรงกับชื่อภูมิภาคใน PivotTable
7. หากจำเป็น ให้เพิ่มป้ายชื่อในคอลัมน์สำหรับผลรวมของแผนก
8. เพิ่มบรรทัดที่ด้านล่างของรายงาน รวมสำหรับบริษัท.
9. ในเซลล์ B4 ให้ป้อนสูตร =DATE(YEAR($A$2),COLUMN(A1),1) สูตรนี้ส่งคืนวันที่ 01/01/2014, 02/01/2104 เป็นต้น ซึ่งเป็นวันแรกของทั้ง 12 เดือนของปีปัจจุบัน
10. เลือกเซลล์ B4 กดคีย์ผสม Ctrl+1 เพื่อเปิดหน้าต่าง รูปแบบเซลล์. บนแท็บ ตัวเลขในบท ทุกรูปแบบป้อนรูปแบบตัวเลขที่กำหนดเอง มม. รูปแบบนี้แสดงชื่อตัวอักษรสามตัวของเดือน จัดข้อความชิดขอบเซลล์ด้านขวา
11. คัดลอกเนื้อหาของเซลล์ B4 ไปยังช่วง C4:M4 แถวที่มีชื่อเดือนจะแสดงที่ด้านบนของตารางเดือย
12. ในเซลล์ B5 ให้ป้อนสูตร =IF(เดือน(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово ข้อเท็จจริงและสำหรับปัจจุบันและอนาคต - วางแผน.
13. เพิ่มหัวเรื่องไปที่เซลล์ N5 ผล. ในเซลล์ O4 - ผล, O5 - วางแผน, Р5 - % ส่วนเบี่ยงเบน.
14. ป้อนสูตร Excel ปกติที่ใช้ในการคำนวณผลรวมของแผนก แถวผลรวมของบริษัท คอลัมน์ผลรวมทั้งหมด และคอลัมน์ % ผลต่าง:
    1. ในเซลล์ B8 ให้ป้อนสูตร = SUM (B6: B7) แล้วคัดลอกไปยังเซลล์อื่นในแถว
    2. ในเซลล์ N6 ให้ป้อนสูตร = SUM (B6: M6) แล้วคัดลอกไปยังเซลล์อื่นของคอลัมน์
    3. ในเซลล์ P6 ให้ป้อนสูตร =IFERROR((N6/O10)-1;0) แล้วคัดลอกไปยังเซลล์อื่นของคอลัมน์
    4. ในเซลล์ B13 ให้ป้อนสูตร = SUM (B10: B12) แล้วคัดลอกไปยังเซลล์อื่นในแถว
    5. ในเซลล์ B17 ให้ป้อนสูตร = SUM (B15: B16) แล้วคัดลอกไปยังเซลล์อื่นในแถว
    6. ในเซลล์ B19 ให้ป้อนสูตร =SUM(B6:B18)/2 แล้วคัดลอกไปยังเซลล์อื่นๆ ในแถว
15. นำสไตล์หัวเรื่อง 4 ไปใช้กับคำบรรยายในคอลัมน์ A และกับหัวเรื่องในแถวที่ 4 และ 5
16. สำหรับช่วงเซลล์ B6:O19 ให้เลือกรูปแบบตัวเลข ###0
17. สำหรับเซลล์ในคอลัมน์ P ให้เลือกรูปแบบตัวเลข 0.0%

ดังนั้นเราจึงได้สร้างตัวห่อรายงานที่แสดงในรูป 15. รายงานนี้มีรูปแบบที่จำเป็นทั้งหมด ส่วนต่อไปนี้จะสาธิตวิธีใช้ฟังก์ชัน GET.PIVOTAL.TABLE DATA เพื่อทำรายงานให้เสร็จสมบูรณ์

ข้าว. 15. ห่อรายงานก่อนเพิ่มสูตร

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

เลือกเซลล์ B6 ของเชลล์รายงาน เซลล์นี้สอดคล้องกับภาคตะวันออกเฉียงเหนือและตัวเลขจริงสำหรับเดือนมกราคม

1. พิมพ์ = (เครื่องหมายเท่ากับ) เพื่อเริ่มป้อนสูตร
2. ไปที่แผ่นงานจากตารางเดือยแล้วคลิกที่เซลล์ที่ตรงกับภาคตะวันออกเฉียงเหนือและตัวเลขจริงสำหรับเดือนมกราคม - C12 (รูปที่ 17)
3. กดปุ่ม เข้าเพื่อสิ้นสุดการป้อนสูตรและกลับไปที่เชลล์รายงาน ด้วยเหตุนี้ Excel จะเพิ่มฟังก์ชัน GET.PICTABLE.TABLE DATA ลงในเซลล์ B6 เซลล์จะแสดง $277,435

จำหมายเลขนี้ไว้เนื่องจากคุณจะต้องใช้เมื่อเปรียบเทียบกับผลลัพธ์ของสูตรที่คุณจะแก้ไขในภายหลัง สูตรที่โปรแกรมสร้างมีรูปแบบดังนี้: =GET.DATA.PIVOTAL.TABLE(" Income " ;'Fig. 11-14′!$A$3; "Region" ; "North-East" ; "Date" ; 1; "ตัวบ่งชี้"; "ข้อเท็จจริง"). หากคุณเพิกเฉยต่อฟังก์ชัน GET.PIVOTAL.TABLE จนถึงตอนนี้ ได้เวลาทำความรู้จักกับฟังก์ชันนี้ให้ดีขึ้นแล้ว บนมะเดื่อ 18 สูตรนี้จะแสดงในโหมดแก้ไขพร้อมกับคำใบ้

อาร์กิวเมนต์ของฟังก์ชัน:

• Data_field. เขตข้อมูลจากพื้นที่ค่า PivotTable โปรดทราบ: ในกรณีนี้ จะใช้ฟิลด์นี้ รายได้, แต่ไม่ จำนวนเงินในช่อง รายได้.
• PivotTable ด้วยตัวเลือกนี้ Microsoft จะถามคุณว่า "PivotTable ใดที่คุณต้องการใช้" การระบุหนึ่งในเซลล์ของตาราง Pivot ก็เพียงพอแล้ว รายการ 'รูปที่ 11-14'!$A$3 หมายถึงเซลล์แรกใน PivotTable ที่ป้อนข้อมูล เนื่องจากในกรณีของเรา คุณสามารถตั้งค่าเซลล์ใดๆ ที่เกี่ยวข้องกับ pivot table ได้ ไม่ต้องเปลี่ยนอาร์กิวเมนต์ ที่อยู่เซลล์ $A$3 เหมาะกับทุกวิถีทาง
• สนาม 1; องค์ประกอบ 1 ในสูตรที่สร้างขึ้นโดยอัตโนมัติ ชื่อจะถูกเลือกเป็นชื่อฟิลด์ ภูมิภาคและเป็นค่าฟิลด์ - ภาคตะวันออกเฉียงเหนือ. นี่คือสาเหตุของปัญหาที่เกิดขึ้นเมื่อทำงานกับฟังก์ชัน GET.PIVOT.TABLE DATA ไม่สามารถคัดลอกค่าที่เลือกอัตโนมัติได้เนื่องจากเป็นค่าฮาร์ดโค้ด ดังนั้น หากคุณคัดลอกสูตรในพื้นที่รายงานทั้งหมด คุณจะต้องเปลี่ยนด้วยตนเอง สำหรับภาคตะวันออกเฉียงเหนือ ให้แทนที่การอ้างอิงเซลล์เป็น $A6 การระบุเครื่องหมายดอลลาร์หน้าชื่อคอลัมน์ A แสดงว่าคุณกำหนดว่าส่วนแถวของข้อมูลอ้างอิงสามารถเปลี่ยนแปลงได้เมื่อคัดลอกสูตรลงในเซลล์ของคอลัมน์
• สนาม 2; องค์ประกอบ 2 อาร์กิวเมนต์คู่นี้ระบุฟิลด์ วันที่โดยมีค่าเป็น 1 หากตาราง Pivot เดิมจัดกลุ่มตามเดือน ฟิลด์เดือนจะคงชื่อฟิลด์เดิมไว้ วันที่. ค่าตัวเลขของเดือนคือ 1 ซึ่งตรงกับเดือนมกราคม ไม่แนะนำให้ใช้ค่าดังกล่าวเมื่อสร้างสูตรขนาดใหญ่ที่ตั้งค่าเป็นสิบหรือหลายร้อยเซลล์รายงาน ควรใช้สูตรที่คำนวณค่าฟิลด์ วันที่เช่นเดียวกับสูตรในเซลล์ B4 แทน 1 ในกรณีนี้ คุณสามารถใช้สูตร MONTH (B $ 4) เครื่องหมายดอลลาร์ก่อนหน้า 4 ระบุว่าสูตรสามารถกำหนดค่าให้กับฟิลด์ได้ วันที่ตามเดือนอื่นๆ เนื่องจากสูตรถูกคัดลอกลงในเซลล์แถว
• สนาม 3; องค์ประกอบ 3 ในกรณีนี้ ชื่อฟิลด์จะถูกกำหนดโดยอัตโนมัติ ดัชนีและค่าฟิลด์ ข้อเท็จจริง. ค่าเหล่านี้ถูกต้องสำหรับเดือนมกราคม แต่ในเดือนต่อๆ ไป ค่าของฟิลด์จะต้องเปลี่ยนเป็นแผน เปลี่ยนค่าฟิลด์ฮาร์ดโค้ด ข้อเท็จจริงเพื่อเชื่อมโยง B$5
• สนาม 4; องค์ประกอบ 4 อาร์กิวเมนต์เหล่านี้ไม่ได้ใช้เพราะ สนามหมดแล้ว

สูตรใหม่แสดงในรูป 19. ในหนึ่งนาที แทนที่จะเป็นสูตรฮาร์ดโค้ดที่ออกแบบมาเพื่อทำงานกับค่าเดียว สูตรที่ยืดหยุ่นได้ถูกสร้างขึ้นซึ่งสามารถคัดลอกไปยังเซลล์ทั้งหมดในชุดข้อมูลได้ กดปุ่ม เข้าและคุณจะได้ผลลัพธ์เหมือนกับก่อนที่จะแก้ไขสูตร สูตรที่แก้ไขจะกลายเป็น: =GET.PIVOTOMAT.TABLE DATA("Revenue" ;'Fig. 11-14′!$A$3; "Region" ;$A6; "Date" ;MONTH(B$4); " Indicator " ; 5 บาท)

ข้าว. 19. เมื่อแก้ไขเสร็จแล้ว สูตร GET.DATA.PIVOTO.TABLE เหมาะสำหรับการคัดลอกไปยังทุกเซลล์ของช่วง

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

ในขั้นตอนถัดไป เราจะตั้งค่าสูตร GET.PIVOTO.TABLE.DATA เพื่อคำนวณตัวบ่งชี้ที่วางแผนไว้ทั้งหมด เพียงคัดลอกสูตรลงในเซลล์ O6 จะแสดง #REF! สาเหตุของข้อผิดพลาดนี้คือคำว่า ผลในเซลล์ O4 ไม่ใช่ชื่อเดือน เพื่อให้แน่ใจว่าการทำงานของฟังก์ชัน GET.PIVOTO.TABLE.DATA ถูกต้อง ค่าที่ต้องการจะต้องอยู่ในตาราง Pivot แต่เนื่องจากในตารางเดือยเดิมฟิลด์ ดัชนีเป็นฟิลด์ที่สองในพื้นที่คอลัมน์ คอลัมน์ข้อมูล ผลลัพธ์ของแผนขาดอยู่จริง ย้ายกล่อง ดัชนีเพื่อให้กลายเป็นอันแรกในพื้นที่คอลัมน์ (รูปที่ 20)

ข้าว. 20. ปรับเค้าโครงของฟิลด์ในพื้นที่คอลัมน์เพื่อให้คอลัมน์ปรากฏขึ้น แผนผลลัพธ์

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

กลับไปที่เชลล์ชีตของรายงาน ยืนในเซลล์ O6 พิมพ์ = (=) และอ้างถึงเซลล์ N12 บนชีตของตาราง Pivot ที่สอดคล้องกับผลรวมที่วางแผนไว้สำหรับภูมิภาคตะวันออกเฉียงเหนือ คลิก เข้า. ผลลัพธ์คือสูตร = GET. DATA. OF. A. PIVOT. TABLE("Income"; 'Fig. 11-14′! $A$3; "Region"; "North-East"; "Indicator"; " วางแผน"). แก้ไข: =GET SUMMARY.TABLE.DATA("รายได้";'รูปที่ 11-14′!$A$3;"ภูมิภาค";$A6;"ตัวบ่งชี้";O$5) คัดลอกสูตรนี้ไปยังเซลล์อื่นในคอลัมน์ O (รูปที่ 21) โปรดทราบว่าแม้ในขณะที่ย้ายพื้นที่ต่างๆ ของรายงาน PivotTable ตัวตัดคำจะทำงานได้อย่างถูกต้อง แน่นอน หากคุณทำให้บางฟิลด์ของข้อมูลสรุปไม่ทำงาน เชลล์จะไม่สามารถรับมือกับสิ่งนี้ได้ ...

ข้าว. 21. รายงานขั้นสุดท้ายที่สามารถนำเสนอต่อผู้จัดการ

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

รายงานการปรับปรุงหากต้องการอัปเดตรายงานด้วยข้อมูลสำหรับเดือนต่อๆ ไป ให้ทำตามขั้นตอนเหล่านี้:

1. แทรกค่าจริงใต้ชุดข้อมูลต้นฉบับ เนื่องจากมีการเลือกรูปแบบตารางสำหรับแหล่งข้อมูล การจัดรูปแบบตารางจึงกระจายไปยังแถวใหม่ของข้อมูลโดยอัตโนมัติ นอกจากนี้ยังขยายคำจำกัดความของ PivotTable ดั้งเดิม (ฉันได้เพิ่มค่าจริงสำหรับทั้งปีในไฟล์ Excel แล้ว)
2. ไปที่ตารางเดือย คลิกขวาและเลือก รีเฟรช. รูปลักษณ์ของตารางเดือยจะเปลี่ยนไป แต่ก็ไม่น่ากลัว
3. ไปที่กระดาษห่อรายงาน โดยหลักการแล้วทุกอย่างได้ดำเนินการไปแล้วเพื่ออัปเดตรายงาน แต่จะไม่รบกวนการทดสอบผลลัพธ์ เปลี่ยนสูตรในเซลล์ A2 เช่น =OMONTH(TODAY() +31 ;0) และดูว่าเกิดอะไรขึ้น

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

ฉันไม่คิดว่าฉันจะพูดอะไรต่อไปนี้: "GET.PIVOTO.TABLE.Function เป็นประโยชน์มากที่สุด เราอยู่ได้อย่างไรหากไม่มีมันมาก่อน?

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

สำหรับ PivotTable ฟังก์ชัน GET.PIVOTO.TABLE DATA จะส่งคืนข้อมูลที่จัดเก็บไว้ในรายงาน PivotTable

หากต้องการเข้าถึงฟังก์ชันอย่างรวดเร็ว คุณต้องป้อนเครื่องหมายเท่ากับในเซลล์ (=) และเลือกเซลล์ที่ต้องการในตาราง Pivot Excel จะสร้างฟังก์ชัน GET.PIVOTOMATIC.DATA โดยอัตโนมัติ

ปิดใช้งานการสร้าง GetPivotData

หากต้องการปิดใช้งานการสร้างฟังก์ชัน GET.PIVOTO.TABLE.DATA โดยอัตโนมัติ ให้เลือกเซลล์ใดก็ได้ในตาราง Pivot ไปที่แท็บ การทำงานกับตารางเดือย -> ตัวเลือกให้กับกลุ่ม ตารางเดือย.คลิกลูกศรลงที่อยู่ถัดจากแท็บ ตัวเลือก.ในเมนูแบบเลื่อนลง ให้ยกเลิกการเลือกช่อง สร้างรับ PivotData

การใช้การอ้างอิงเซลล์ในฟังก์ชัน GET.PIVOTO.TABLE DATA

แทนที่จะระบุชื่อรายการหรือฟิลด์ในฟังก์ชัน GET.DATA.PIVOTO.TABLE คุณสามารถอ้างถึงเซลล์ที่อยู่ในแผ่นงานได้ ในตัวอย่างด้านล่าง เซลล์ E3 มีชื่อผลิตภัณฑ์ และสูตรในเซลล์ E4 อ้างถึงชื่อนั้น เป็นผลให้ปริมาณเค้กทั้งหมดจะถูกส่งกลับ

การใช้การอ้างอิงเขตข้อมูล PivotTable

ไม่มีคำถามเกี่ยวกับวิธีการทำงานของลิงก์ไปยังรายการตารางสาระสำคัญ ปัญหาจะเกิดขึ้นหากเราต้องการอ้างถึงเขตข้อมูล

ในตัวอย่าง เซลล์ E3 มีชื่อช่องข้อมูล "Count" และจะเป็นการดีที่จะอ้างถึงเซลล์นี้ในฟังก์ชัน แทนที่จะมีชื่อช่องในสูตร GET.DATA.PIVOTO.TABLE

อย่างไรก็ตาม หากเราเปลี่ยนอาร์กิวเมนต์แรก data_fieldในการอ้างอิงถึงเซลล์ E3 Excel จะส่งกลับข้อผิดพลาด #LINK!

GET.PIVOTO.TABLE.DATA(E3,$A$3)

การเพิ่มสตริงว่าง (“”) อย่างง่าย ๆ ที่จุดเริ่มต้นหรือจุดสิ้นสุดของการอ้างอิงเซลล์จะช่วยแก้ปัญหาได้

GET.PIVOTO.TABLE.DATA(E3&"";$A$3)

การแก้ไขสูตรอย่างง่ายจะส่งกลับค่าที่ถูกต้อง

ใช้วันที่ใน GET DATA

ถ้าคุณใช้วันที่ในฟังก์ชัน GET.PIVOTO.TABLE คุณอาจประสบปัญหาแม้ว่าวันที่จะปรากฏใน PivotTable ก็ตาม ตัวอย่างเช่น อาร์กิวเมนต์สูตรด้านล่างคือวันที่ “04/21/2013” ​​และ PivotTable ประกอบด้วยเขตข้อมูลที่มีวันที่ขาย อย่างไรก็ตาม สูตรในเซลล์ E4 ส่งกลับข้อผิดพลาด

GET.PIVOTO.TABLE.DATA("เล่ม",$A$3,"วันที่","04/21/2013")

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

• เปรียบเทียบรูปแบบวันที่ในสูตรและตารางเดือย
• ใช้ฟังก์ชัน DATEVALUE
• ใช้ฟังก์ชันวันที่
• อ้างถึงเซลล์ที่มีวันที่ที่ถูกต้อง

เปรียบเทียบรูปแบบวันที่ในสูตรและตารางเดือย

เพื่อให้ได้ผลลัพธ์ที่ถูกต้อง เมื่อใช้ฟังก์ชัน GET.PIVOTOMAT.DATA ตรวจสอบให้แน่ใจว่ารูปแบบวันที่ของอาร์กิวเมนต์สูตรและตาราง Pivot เหมือนกัน

ในเซลล์ E4 สูตรใช้รูปแบบวันที่ “DD.MM.YYYY” และส่งคืนข้อมูลที่ถูกต้อง

การใช้ฟังก์ชัน DATEVALUE

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

ในเซลล์ E4 วันที่จะถูกป้อนโดยใช้ฟังก์ชัน DATEVALUE และ Excel จะส่งคืนข้อมูลที่จำเป็น

GET.PIVOTO.TABLE.DATA("ปริมาณ",$A$3,"วันที่",DATEVALUE("04/21/2013"))

การใช้ฟังก์ชัน DATE

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

GET.PIVOTO.TABLE.DATA("ปริมาณ",$A$3,"วันที่",DATE(2013,4,21))

เชื่อมโยงไปยังเซลล์ที่มีวันที่

แทนที่จะป้อนวันที่ในสูตรด้วยตนเอง คุณสามารถอ้างถึงเซลล์ที่มีวันที่ (ในรูปแบบใดก็ตามที่ Excel ถือว่าข้อมูลเป็นวันที่) ในตัวอย่างในเซลล์ E4 สูตรอ้างถึงเซลล์ E3 และ Excel จะส่งกลับข้อมูลที่ถูกต้อง

GET.PIVOTO.TABLE.DATA("ปริมาณ",$A$3,"วันที่",E3)