วิธีทำให้กราฟยืดได้หดได้แบบ Moveable Dynamic Range ด้วยสูตรใหม่ใน Excel 365

Dynamic Range เป็นพื้นที่ตารางที่ยืดได้หดได้ ทำให้สูตรคำนวณได้เร็วขึ้นเพราะไม่จำเป็นต้องใช้ข้อมูลทั้งหมดที่มี หากทำให้ดีขึ้นต้องหาทางทำให้ Moveable คือ สามารถกำหนดรายการแรกที่เริ่มได้ด้วย ไม่จำเป็นต้องใช้ข้อมูลตั้งแต่รายการแรกเสมอไป ซึ่งแต่เดิมนั้นต้องใช้สูตร Offset มาช่วย

พอเปลี่ยนมาใช้ Excel 365 ที่เซลล์ F7 ให้ใช้สูตรตามนี้แทน

=CHOOSEROWS( ChartData, SEQUENCE(5,1,Start) )

โดย SEQUENCE(5,1,Start) ทำหน้าที่สร้างเลขลำดับทีละ 5 ตัว อยากให้เลขเริ่มจากเลขอะไรก็กำหนดได้ตาม Start

ถ้า Start = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5
ถ้า Start = 2 จะได้เลขลำดับ 2; 3; 4; 5; 6
ถ้า Start = 3 จะได้เลขลำดับ 3; 4; 5; 6; 7

พอนำสูตร Sequence ไปซ้อนในสูตร ChooseRows จะช่วยเลือกรายการจากตารางเก็บข้อมูล ChartData ด้านซ้ายสุดได้รายการตามเลขลำดับที่ต้องการออกมาให้ แสดงไว้ในพื้นที่ตารางตรงกลางที่เตรียมไว้เป็น Chart Template สำหรับนำไปสร้างกราฟด้านขวาต่ออีกที

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

แทนที่จะดึงข้อมูลออกมาพร้อมกันทุก Column ใน Chart Data ถ้าอยากจะดึงข้อมูลออกมาทีละ Column จะใช้สูตร ChooseCols ซ้อนเข้าไปอีกชั้นก็ได้ แต่เพื่อทำให้เข้าใจได้ง่ายขึ้นก็เพียงเปลี่ยนสูตรมาเป็น

=CHOOSEROWS( Year, SEQUENCE(Interval,1,Begin) ) เพื่อดึงช่วงปีที่ต้องการ
=CHOOSEROWS( Income, SEQUENCE(Interval,1,Begin) ) เพื่อดึงรายการ Income ช่วงเดียวกันกับปีที่ต้องการ

ตามภาพที่ 2 สูตรนี้ทำหน้าที่เหนือกว่าสูตรข้างต้น โดยใช้เลข Interval ช่วยในการกำหนดขนาดจำนวนรายการได้ด้วย 

ถ้า Interval = 6 และ Begin = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5; 6
ถ้า Interval = 7 และ Begin = 1 จะได้เลขลำดับ 1; 2; 3; 4; 5; 6; 7
ถ้า Interval = 7 และ Begin = 2 จะได้เลขลำดับ 2; 3; 4; 5; 6; 7; 8

นำสูตรไปตั้งเป็น Formula Name ในชื่อ XAxis กับ YAxis แล้วนำชื่อไปเปลี่ยนสูตร Series บนเส้นกราฟ

จากสูตรเดิมที่ใช้ตำแหน่งอ้างอิง $B$5:$B$16 กับ $C$5:$C$16 ตามสูตรนี้

=SERIES( Dynamic!$C$4, Dynamic!$B$5:$B$16, Dynamic!$C$5:$C$16, 1)

ให้พิมพ์ชื่อที่ตั้งไว้ XAxis ทับ $B$5:$B$16 และพิมพ์ YAxis ทับ $C$5:$C$16 แล้วกดปุ่ม Enter สูตรจะนำชื่อแฟ้มนำหน้าชื่อสูตรมาใส่ให้เอง สูตร Series จะเปลี่ยนเป็น

=SERIES( Dynamic!$C$4, DynamicChart365.xlsb!XAxis, DynamicChart365.xlsb!YAxis, 1)

พอคลิกเปลี่ยนเลขในช่อง Interval กับ Begin เส้นกราฟก็จะยืดได้หดได้และเลื่อนปีที่เริ่มได้ตามสบาย โดยไม่ต้องพึ่งตาราง Template อีกต่อไป

Download ตัวอย่างได้จาก

https://drive.google.com/file/d/1kw00MUGDh75rNTcE2H6P8Hyfw_Z0xp_z/view?usp=sharing

+++++++++++

สำหรับ Excel รุ่นเก่าก่อน 365 เชิญดูวิธีการได้ที่หลักสูตรเคล็ดการสร้างกราฟให้เห็นปุ๊บ เข้าใจปั้บ บทเรียนที่ 26 ใช้สูตร Offset

เชิญสมัครเรียนออนไลน์ ฟรี 1 ปีได้ที่เว็บ XLSiam.com