วันนี้ขอนำเสนอการใช้ excel เกี่ยวกับการกำหนดข้อมูลแบบพลวัตร ซึ่งจะอำนวยความสะดวกและอัพเดตข้อมูลให้กับผู้ใช้งานได้ตลอดเวลา และเป็นการแก้จุดด้อยของการกำหนดข้อมูลแบบคงที่ ดังที่นำเสนอในบทความที่ผ่านมา ก่อนอื่นผมขอนำเสนอการนำวิธีการกำหนดข้อมูลแบบพลวัตรไปใช้งานก่อน ซึ่งจะพบว่าการสร้างรายการสินค้า รายชื่อลูกค้า รวมถึงรายละเอียดอื่นๆของสินค้าหรือลูกค้า เพื่อให้ผู้ใช้เลือกข้อมูลดังกล่าวนั้น รายการสินค้าหรือรายชื่อสิ้นค้าดังกล่าวมักจะถูกเพิ่มเติมได้ตลอดเวลา ซึ่งเมื่อมีการเปลี่ยนแปลงข้อมุลดังกล่าว รายการที่สร้างขึ้นต้องถูกปรับปรุงให้เป็นข้อมูลปัจจุบันอยู่เสมอ ซึ่งหากการสร้างรายการ หรือสร้างตารางเพื่อใช้สำหรับค้นหาอ้างอิงข้อมูลแบบคงที่ ผู้ใช้งานจะต้องกลับไปปรับสูตร excel ที่ใช้อ้างอิงในรายการหรือตารางดังกล่าวให้ครอบคลุมกับข้อมูล แต่หากใช้การกำหนดข้อมูลแบบพลวัตรจะแก้ปัญหาดังกล่าวได้ โดยมิต้องกลับไปแก้ไขสูตร excel แต่อย่างใด เรามาดูวิธีการกำหนดข้อมูลแบบพลวัตรกันเลยครับ
สูตร excel ที่ใช้คือ สูตร excel : OFFSET ซึ่งเป็นสูตรที่จะคืนค่าตำแหน่งของช่วงข้อมูลกลับมาให้กับผู้ใช้ โดยมีรูปแบบดังนี้
OFFSET(REF CELL, ROW,COL,HEIGHT,WIDTH)โดย REF CELL คือ เซลล์ที่ใช้สำหรับอ้างอิงการกำหนดช่วงข้อมูล
ROW คือค่าตำแหน่งแถวเริ่มต้นของช่วงข้อมูล โดยอ้างอิงจาก REF CELL
COL คือ ค่าตำแหน่งหลักเริ่มต้นของช่วงข้อมูล โดยอ้างอิงจาก REF CELL
HEIGHT คือจำนวนแถวของช่วงข้อมูล
WIDTH คือจำนวนหลักของช่วงข้อมูล
ตัวอย่างเช่น
OFFSET($A$1,0,0,3,2) จะคืนค่า A1:B3
OFFSET($A$1,1,0,3,2) จะคืนค่า A2:B4
ลักษณะของข้อมูลแบบพลวัตรจำนวนแถวหรือจำนวนหลักของช่วงข้อมูลมักจะมีการเปลี่ยนแปลง อาจจะเพิ่มขึ้นหรือลดลง ดังนั้นสูตร excel ของเราต้องมีการคำนวณหาจำนวนแถวและจำนวนหลักอยู่ตลอดเวลาเพื่ออัพเดตข้อมูล การนับจำนวนแถวหรือจำนวนหลักจะใช้สูตร excel : COUNTA เพื่อหาจำนวนแถวหรือหลักที่มีข้อมูล มาดูตัวอย่างสูตร excel ที่ใช้หาตำแหน่งของช่วงข้อมูลกันเลยครับ สมมุติเรามีข้อมูลรายชื่อลูกค้าอยู่ในหลัก B ดังภาพที่ 1
ภาพที่ 1 ตัวอย่างข้อมูล
เราสามารถกำหนดช่วงข้อมูลรายชื่อลูกค้าได้ดังนี้ครับ
OFFSET($B$3,1,0,COUNTA($B:$B)-1,1)
ข้อสังเกต จำนวนหลักเป็นค่าคงที่ 1 เนื่องจากข้อมูลรายชื่อลูกค้าเป็นแบบเวกเตอร์ (ต้องการข้อมูลเพียงหลักเดียว)
และเราสามารถกำหนดตารางข้อมูลลูกค้าได้ดังนี้
OFFSET($B$3,1,0,COUNTA($B:$B)-1,3)
ข้อสังเกต จำนวนหลักเป็นค่าคงที่ 3 เนื่องจากตารางข้อมูลลูกค้ามีจำนวนหลักคงที่
หากจำนวนหลักของข้อมูลมีโอกาสเพิ่มหรือลดลงได้ท่านผู้อ่านต้องใช้สูตร excel : COUNTA นับจำนวนหลักด้วย
ข้อสังเกต จำนวนแถวของช่วงข้อมูลที่ได้จากสูตร excel : COUNTA จะต้องถูกลบด้วย 1 เนื่องด้วยแถวที่ 3 เป็นชื่อของข้อมูล
ท่านผู้อ่านสามารถกำหนดชื่อของช่วงข้อมูลตามขั้นตอนการกำหนดชื่อดังแสงในบทความการกำหนดชื่อข้อมูลแบบคงที่ โดยในขั้นตอน อ้างอิงไปยังให้พิมพ์สูตร excel : OFFSET โดยสมมุติให้ชื่อข้อมูลรายชื่อลูกค้าเป็น CUSTOMER ขณะที่ชื่อตารางข้อมูลลูกค้าเป็น tb_cus ดังแสดงในภาพที่ 2
ภาพที่ 2 การกำหนดชื่อข้อมูลใน excel
ตัวอย่างการใช้งาน
นำไปใช้ในการค้นหาข้อมูลรายละเอียดลูกค้าได้ดังแสดงในภาพที่ 3 โดยในช่องรายชื่อ จะใช้การแสดงรายการโดยกำหนดข้อมูลเป็นแบบ Data validation และเลือกชนิดเป็นแบบ List และแหล่งข้อมูล(Source) จะเป็นข้อมูลชื่อ CUSTOMER ดังแสดงในภาพที่ 5 ส่วนของ ที่อยู่ และ เครดิต เราจะใช้สูตร Excel : vlookup เพื่อช่วยค้นหารายละเอียดลูกค้าโดยอ้างอิงจากรายชื่อลูกค้า โดยสูตร Excel : vlookup มีดังนี้
vlookup(G9,tb_cus,2,false) สำหรับ ที่อยู่vlookup(G9,tb_cus,3,false) สำหรับ เครดิต
ภาพที่ 3 การใช้ excel ทำระบบการค้นหา
หากข้อมูลลูกค้าดังภาพที่ 1 มีการเปลี่ยนแปลง List รายการในการค้นหาก็จะมีการอัพเดตตามดังแสดงในภาพที่ 4
ภาพที่ 4 ตัวอย่างเมื่อข้อมูลมีการอัพเดต
จากคำอธิบายและตัวอย่างการกำหนดข้อมูลใน excel ที่ได้นำเสนอมาทั้งสองบทความ ก็หวังว่าท่านผู้อ่านจะนำไปประยุกต์ใช้กับการใช้ excel ของท่านได้อย่างมีประสิทธิภาพครับ เพราะการกำหนดข้อมูลถือว่าเป็นพื้นฐานการใช้งาน Excel ขั้นสูงต่อไป
ภาพที่ 5 การกำหนดรายการลูกค้า
ไม่มีความคิดเห็น:
แสดงความคิดเห็น