FIFO Costing with Excel Data Table (Part II) (th-TH)

วิธีใช้ Data Table กับงานสินค้าคงคลังโดยทั่วไป

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

Data Table ช่วยทำให้ไม่ต้องสร้างสูตรคำนวณซ้ำแล้วซ้ำอีก ขอเพียงมั่นใจว่า สูตรคำนวณที่สร้างไว้แล้วนั้นให้ผลลัพธ์ที่ถูกต้อง ย่อมส่งผลให้ Data Table ให้คำตอบที่ถูกต้องเช่นกัน
Data Table มีโครงสร้างตารางที่ผู้ใช้งานสามารถกำหนดได้เอง จึงเป็นตารางคำตอบที่ยืดหยุ่นกว่าตารางที่สร้างด้วย Pivot Table
Data Table ให้คำตอบได้ทั้งที่เป็นตัวเลขและตัวอักษร ต่างกับ Pivot Table ซึ่งหาคำตอบเฉพาะตัวเลขเท่านั้น
เมื่อใช้ Data Table ร่วมกับสูตร Index, IF, Choose หรือสูตรใดก็ตามที่เกี่ยวข้องกับการใช้ตัดสินใจ จะทำให้สามารถใช้ Data Table กับตัวแปรไม่จำกัดจำนวนและไม่จำกัดประเภทของคำตอบที่ต้องการ โดยไม่ได้จำกัดว่าจะรับตัวแปรได้เพียง 1 หรือ 2 ตัวตามที่อธิบายไว้ในตำราทั่วไป

ก่อนจะทำให้ Data Table ทำงานได้ ต้องออกแบบโครงสร้างตารางให้เหมาะสมก่อน โดยเลือกวางค่าตัวแปรที่ต้องการไว้บนหัวตารางข้างบน หรือด้านข้างซ้ายมือของตาราง หรือทั้งสองข้าง แล้วสร้างสูตรลิงค์เซลล์ผลลัพธ์กลับมาที่ตาราง จากนั้นให้เลือกพื้นที่ตารางทั้งหมดแล้วสั่ง Data > What-if Analysis > Data Table ให้เลือกเซลล์ที่จะรับค่าตัวแปรลงไปในช่อง Row Input Cell หรือ Column Input Cell ช่องใดช่องหนึ่งหรือทั้งสองช่องก็ได้ตามแต่ลักษณะโครงสร้างตารางที่ออกแบบไว้

ดูรายละเอียดวิธีออกแบบตารางและพื้นฐานขั้นต้นของการใช้ได้จาก http://www.excelexperttraining.com/forums/content.php?r=672

จากนี้ขอนำเสนอ 2 ตัวอย่าง เป็นการใช้ Data Table กับการจัดการข้อมูลสินค้าคงคลัง โดยใช้ Data Table แบบ 1 ตัวแปรและ 2 ตัวแปรตามลำดับ

ตัวอย่างการใช้ Data Table แบบ 1 ตัวแปร

ตัวอย่างนี้ตาราง B2:F7 ด้านซ้ายมือเป็นตารางฐานข้อมูลเก็บข้อมูลสินค้า ตาราง H2:J3 ด้านขวาบนเป็นตารางสูตรที่ใช้ค้นหารายละเอียดของรหัสที่กรอกไว้ในเซลล์ H3 ส่วนตาราง H8:J16 เป็นตาราง Data Table แบบ 1 ตัวแปรโดยใช้รหัส NewID เป็นตัวแปร

สังเกตว่าในตาราง B3:F7 ด้านซ้ายมือสุดบันทึกรหัสซ้ำเอาไว้ ซึ่งหากใช้สูตร VLookup จะใช้แสดงข้อมูลของรหัสตัวแรกได้เท่านั้น ไม่สามารถแสดงข้อมูลของรหัสที่ซ้ำรายการถัดไป จึงต้องหาทางสร้างรหัสใหม่ที่ไม่ซ้ำขึ้นมาใช้แทน โดยสร้างสูตร =COUNTIF($B$3:B3,B3) ลงไปในเซลล์ C3 แล้ว copy ลงมาให้ครบทุกรายการจะได้ตัวเลขแสดงจำนวนครั้งที่ซ้ำไว้ในเซลล์ C3:C7 จากนั้นจึงนำรหัสเดิมมาต่อท้ายด้วยเลขจำนวนครั้งที่ซ้ำด้วยสูตร =B3&C3 เกิดเป็นรหัสใหม่ NewID ที่ไม่ซ้ำแสดงไว้ในเซล��์ D3:D7 แล้วตั้งชื่อ MyData ให้กับพื้นที่ตาราง D3:F7

เซลล์ H3 ใช้กรอกรหัสที่ต้องการค้นหา จากนั้นในเซลล์ I3 และ J3 ใช้สูตรต่อไปนี้ตามลำดับ เพื่อแสดงรายละเอียดชื่อและจำนวนของรหัส

I3 ใช้สูตร =VLOOKUP($H$3,MyData,MATCH(E2,$D$2:$F$2,0),0)

J3 ใช้สูตร =VLOOKUP($H$3,MyData,MATCH(F2,$D$2:$F$2,0),0)

เมื่อต้องการแสดงรายละเอียดของรหัสอื่น แทนที่จะใช้วิธีกรอกรหัสใหม่ที่ต้องการลงไปในเซลล์ H3 เพื่อดูผลในเซลล์ I3 กับ J3 ซึ่งสร้างสูตร VLookup เอาไว้ หรือบางคนอาจถึงขั้นสร้างสูตรแบบเดียวกับเซลล์ I3 กับ J3 ซ้ำอีกหลายครั้งตามจำนวนรหัสที่ต้องการแสดงรายละเอียด ให้ใช้ Data Table เพื่อนำผลจากสูตร I3 กับ J3 ที่มีอยู่แล้วมาใช้จะเหมาะสมกว่า โดยมีขั้นตอนการสร้าง Data Table ดังนี้

สร้างสูตรลิงค์ผลลัพธ์จากเซลล์ I3 กับ J3 มาไว้ที่เซลล์ I8 กับ J8 ตามลำดับ
ในเซลล์ H9:H16 ให้พิมพ์รหัสที่ต้องการใช้แสดงรายละเอียดลงไป โดยจะเลือกใช้รหัสบางตัวหรือทุกตัวและจะเรียงลำดับรหัสอย่างไรก็ได้ ซึ่งข้อมูลรหัสเหล่านี้เรียกว่า Column Input เนื่องจากเป็นตัวแปรที่เก็บไว้ในตารางตามแนวตั้ง
เลือกพื้นที่ตาราง H8:J16 แล้วสั่ง Data > What-if Analysis > Data Table
คลิกลงไปในช่อง Column Input Cell แล้วคลิกต่อไปที่เซลล์ H3
กดปุ่ม OK

จะพบว่าเซลล์ I9:J16 เกิดสูตร {=TABLE(,H3)} ขึ้นเอง และแสดงรายละเอียดชื่อและจำนวนของรหัสแต่ละตัวให้ทันที จากนั้นเมื่อต้องการค้นหาข้อมูลของรหัสตัวใด ให้พิมพ์รหัสที่ต้องการลงไปในเซลล์ H9:H16

หากต้องการขยายหรือลดขนาดตาราง Data Table ให้เลือกพื้นที่เซลล์ I9:J16 เพื่อลบสูตร {=TABLE(,H3)} ทิ้งก่อน ซึ่งสูตรนี้ต้องลบพร้อมกันทีเดียวจะเลือกลบบางเซลล์ไม่ได้ จากนั้นให้ทำตามขั้นตอนที่ 3 โดยเลือกพื้นที่ตามที่ต้องการใหม่

หากต้องการใช้ตาราง Data Table ให้แสดงรายละเอียดอื่น เพียงเปลี่ยนสูตร VLookup ในเซลล์ I3 กับ J3 เป็นสูตรอื่น จะส่งผลให้ตาราง Data Table แสดงคำตอบตามให้ทันที

ในกรณีที่ต้องการสร้างตาราง Data Table กลับข้างกับตัวอย่างนี้ ให้วางตัวแปรที่เป็นรหัสไว้ตามแนวนอน แล้วย้ายสูตรลิงค์จากเซลล์คำนวณมาไว้ด้านข้างซ้ายของตาราง จากนั้นเมื่อสั่ง Data Table ให้คลิกเลือกช่อง Row Input Cell แล้วคลิกต่อไปยังเซลล์ H3 เช่นเดิม

ตัวอย่างการใช้ Data Table แบบ 2 ตัวแปร

ตัวอย่างนี้ตาราง B2:E7 ด้านซ้ายมือเป็นตารางฐานข้อมูล ตาราง G2:I3 ด้านขวาบนเป็นตารางสูตรที่ใช้ค้นหารายละเอียดของชื่อหน่วยงานที่กรอกไว้ในเซลล์ G3 และ H3 ส่วนตาราง G9:K13 เป็นตาราง Data Table แบบ 2 ตัวแปรโดยใช้ชื่อหน่วยงานทั้ง Division และ Department เป็นตัวแปร

ยอด Actual ที่ต้องการต้องเป็นของ Division และ Department ที่ตรงกับค่าที่กรอกไว้ในเซลล์ G3 และ H3 ทั้งคู่พร้อมกัน ซึ่งหากใช้สูตร Match จะต้องใช้กับข้อมูลที่เก็บไว้ตามแนวตั้ง column เดียวเท่านั้น ไม่สามารถใช้กับ column ของ Division และ Department พร้อมกันได้ จึงแก้ไขโดยสร้างสูตรนำชื่อ Division และ Department มาต่อกันในเซลล์ D3 =B3&C3 แล้ว copy ลงไปในช่วงเซลล์ D3:D7 จึงสามารถใช้สูตรต่อไปนี้หายอด Actual

I3 ใช้สูตร =INDEX(Actual,MATCH(G3&H3,D3:D7,0))

เมื่อต้องการหายอด Actual ของหน่วยงานอื่น แทนที่จะใช้วิธีกรอกชื่อหน่วยงานใหม่ที่ต้องการลงไปในเซลล์ G3 และ H3 เพื่อดูผลในเซลล์ I3 ซึ่งสร้างสูตร Index เอาไว้ หรือบางคนอาจถึงขั้นสร้างสูตรแบบเดียวกับเซลล์ I3 ซ้ำอีกหลายครั้งตามจำนวนหน่วยงานที่ต้องการแสดงรายละเอียด ใ��้ใช้ Data Table แบบ 2 ตัวแปร เพื่อนำผลจากสูตร I3 ที่มีอยู่แล้วมาใช้จะเหมาะสมกว่า โดยมีขั้นตอนการสร้าง Data Table ดังนี้

ตามปกติจะสร้างสูตรลิงค์ผลลัพธ์จากเซลล์ I3 มาไว้ที่เซลล์ G9 ก็เพียงพอแล้ว แต่เพื่อทำให้สามารถเปลี่ยนการแสดง error กรณีที่หาค่าไม่พบคืนค่าเป็นเลข 0 แทน จึงใช้สูตรปรับ error ในเซลล์ G9 ด้วยสูตร =IF(ISERROR(I3),0,I3) นอกจากนี้ยังอาจใช้สูตร Choose หรือสูตรอื่นเพื่อเลือกนำผลลัพธ์จากเซลล์ที่ต้องการมาใช้แทนค่าจากเซลล์ I3 ก็ได้
ในเซลล์ H9:K9 ให้พิมพ์ชื่อ Department ที่ต้องการลงไป โดยจะเลือกใช้ชื่อหน่วยงานบางหน่วยงานหรือทุกหน่วยงานตัวและจะเรียงลำดับชื่ออย่างไรก็ได้ ซึ่งข้อมูลชื่อหน่วยงานเหล่านี้เรียกว่า Row Input เนื่องจากเป็นตัวแปรที่เก็บไว้ในตารางตามแนวนอน
ในเซลล์ G10:G13 ให้พิมพ์ชื่อ Division ที่ต้องการลงไป โดยจะเลือกใช้ชื่อหน่วยงานบางหน่วยงานหรือทุกหน่วยงานตัวและจะเรียงลำดับชื่ออย่างไรก็ได้ ซึ่งข้อมูลชื่อหน่วยงานเหล่านี้เรียกว่า Column Input เนื่องจากเป็นตัวแปรที่เก็บไว้ในตารางตามแนวตั้ง
เลือกพื้นที่ตาราง G9:K13 กำหนดรูปแบบ 0;-0; เพื่อซ่อนเลข 0 แล้วสั่ง Data > What-if Analysis > Data Table
ในช่อง Row Input Cell ให้คลิกต่อไปที่เซลล์ H3
คลิกลงไปในช่อง Column Input Cell แล้วคลิกต่อไปที่เซลล์ G3
กดปุ่ม OK

จะพบว่าเซลล์ H10:K13 เกิดสูตร {= TABLE(H3,G3)} ขึ้นเอง และแสดงยอด Actual ของหน่วยงานที่ต้องการให้ทันที

หากต้องการขยายหรือลดขนาดตาราง Data Table ให้เลือกพื้นที่เซลล์ H10:K13 เพื่อลบสูตร {= TABLE(H3,G3)} ทิ้งก่อน ซึ่งสูตรนี้ต้องลบพร้อมกันทีเดียวจะเลือกลบบางเซลล์ไม่ได้ จากนั้นให้ทำตามตั้งแต่ขั้นตอนที่ 4 โดยเลือกพื้นที่ตามที่ต้องการใหม่

ถ้าต้องการใช้ Data Table ให้ทำงานได้ไม่จำกัดตัวแปร ให้ใช้สูตร Index ช่วยส่งค่ามาคำนวณ ศึกษาวิธีการได้จาก http://www.excelexperttraining.com/forums/content.php?r=672

Download ตัวอย่างนี้ได้จาก
http://www.excelexperttraining.com/forums/showthread.php?t=3345

Links for FIFO Costing with Excel Data Table

Part I :
http://social.technet.microsoft.com/wiki/contents/articles/14006.fifo-costing-with-excel-data-table-part-i-th-th.aspx

Part II :
http://social.technet.microsoft.com/wiki/contents/articles/14020.fifo-costing-with-excel-data-table-part-ii-th-th.aspx

Part III :
http://social.technet.microsoft.com/wiki/contents/articles/14039.fifo-costing-with-excel-data-table-part-iii-th-th.aspx

Part IV :
http://social.technet.microsoft.com/wiki/contents/articles/14040.fifo-costing-with-excel-data-table-part-iv-th-th.aspx