Snasui Excel Forum

สวัสดีอาจารย์และเพื่อนๆ ทุกคนครับ

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

ตัวอย่างที่ต้องการผมลองคีย์ไว้ในไฟล์แนบแล้วครับ

ขอบคุณมากครับ

การบันทึกรายการในชีท GoodDat ควรปรับปรุงให้เป็น Database คือบันทึกไปด้านล่างเรื่อย ๆ ในแต่ละเลขที่เอกสารไม่ว่าจะเกิดจากการซื้อหรือขายหรืออื่น ๆ ไม่ใช่บันทึกไปด้านขวาครับ

เมื่อบันทึกเป็น Database แล้วจะได้ง่ายต่อการนำไปใช้งานต่อไม่ว่าจะสรุปด้วย PivotTable หรือ ด้วยสูตร จากที่ถามมานั้นสามารถทำด้วยสูตรได้เช่นกันแต่ไม่แนะนำให้ทำเช่นนั้น เพราะจะเป็นปัญหาในวันหน้าได้หากข้อมูลมีจำนวนมากครับ

ขอบคุณอาจารย์มากครับ ที่จริงตอนนี้รายการสินค้าก็มีมากพอสมควรครับ ผมตัดมาแค่บางรายการ ถ้าอย่างนั้นผมควรเอารายการสินค้าไปใส่ในแนวนอนแทนที่จะเป็นแนวตั้ง แล้วก็ไล่รายการบิลแต่ละใบลงมาใช่ไหมครับ

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

ชื่อสินค้าจะยาวจะสั้นไม่ใช่ปัญหา จะไม่ปรับความกว้างคอลัมน์ ปรับความสูงแถว ให้พอดีกับข้อมูลในเซลล์ก็ได้ เพราะเราสามารถดูที่บรรทัด Formula Bar ของโปรแกรมได้ว่าเซลล์นั้น ๆ คือค่าใด

การบันทึกเป็น Database จากข้อมูลตามที่แนบมานั้นให้นำ วันที่ ประเภท ผู้ขาย เลขที่เอกสาร ลงมาในแนวตั้งให้หมดครับ

กรณีต้องการจะดูข้อมูลต่าง ๆ เราสามารถใช้ Filter เข้ามาช่วยได้ แต่หากบันทึกข้อมูลไปทางขวาแล้วจะใช้การ Filter ดูข้อมูลที่ต้องการไม่ได้ ตรงนี้จะลำบากกว่ากันมากชนิดเทียบกันไม่ได้ การค้นหาข้อมูลเราไม่ควรหาด้วยการกวาดสายตาแต่ใช้เครื่องมือที่โปรแกรมมีมาให้เป็นหลัก ควรฝึกฝนให้ชินครับ

ลักษณะงานที่ทำมานั้นมีลักษณะเป็นรายงานอยู่ด้วย การทำ Database ก็ควรให้เป็น Database ไม่ใช่ให้เป็นรายงาน ยอดซื้อรวม, ขายรวม, คงเหลือ ไม่จำเป็นต้องมี ค่อยทำตารางสรุปมาอีกต่างหาก มีเพียงยอดยกมากับรายการที่เกิดในงวดก็เพียงพอที่จะทำสรุปข้อมูลตามต้องการ เนื่องจากยอดคงเหลือคือ ยอดยกมา บวก ยอดรับ หัก ยอดจ่าย

ครับอาจารย์ เดี๋ยวจะลองปรับตารางดูครับ

(สาเหตุที่ผมทำเป็นแบบรายงานเนื่องจากผมจะปริ้นท์ปริมาณสินค้าและยอดคงเหลือมาเก็บไว้เป็นระยะๆ ตอนแรกเคยทำรายงานแยกต่างหาก แต่พอมีการแทรกสินค้าใหม่เข้าไปตามหมวดหมู่ ก็พบว่าตารางที่เคยทำสูตรอ้างอิงไว้ขึ้นเป็นค่า #REF หมดเลย ผมไม่รู้จะทำอย่างไร ตอนนั้นก็ยังไม่รู้จักบอร์ดแห่งนี้ด้วย ก็เลยปรับตารางเป็นแบบพร้อมปริ้นท์เป็นรายงานเก็บไว้เลยครับ

กรณีที่มีการแทรกแถวหรือคอลัมน์ แล้วไม่ให้ตารางที่มีการอ้างอิงข้อมูลมาถึงขึ้นค่าผิดพลาดเป็น #REF นี่ทำได้ใช่ไหมครับ?)

การบันทึกเป็น Database เราไม่จำเป็นต้องแทรกบรรทัดและแม้จะมีการแทรกบรรทัด สูตรที่สรุปข้อมูลเราก็สามารถเขียนให้ดึงข้อมูลไปใช้อย่างถูกต้องอยู่เสมอครับ

อ๋อ คือก่อนหน้านี้ลักษณะตารางก็คล้ายกับที่แนบไฟล์ไปครับ พอมีสินค้าตัวใหม่มาก็จะเอาไปแทรกตามหมวดหมู่ เช่น ผลิตภัณฑ์เกี่ยวกับเส้นผม ผลิตภัณฑ์ทำความสะอาด ฯลฯ ตารางรายงานที่ทำไว้ชีทอื่นซึ่งเขียนเป็นสูตรอ้างอิงก็เลยขึ้นค่าผิดพลาด REF หลังจากที่มีการแทรกแถวไปน่ะครับ
สูตรอ้างอิงผมก็คีย์ง่ายๆ ตรงๆ ไปเลย อย่างเช่น Sheet2 ที่ A1 คีย์ว่า
=Sheet1!A1
ทำนองนี้ล่ะครับ

ปล. อาจารย์ยังไม่นอนหรือครับ เห็นสถานะ offline นึกว่าหลับแล้ว แต่เห็นตอบมาไวมากเลยตกใจเลยครับ

เรียนอาจารย์คนควน และสวัสดีเพื่อนๆ ครับ
ผมต้องการเรียงลำดับข้อความซึ่งเป็นรหัสเอกสาร บางครั้งมี 8 หลัก บางครั้งมี 9 หลัก ซึ่งจะคีย์ตามวันที่ที่ทำ แต่อยากจะเรียงใหม่โดยให้เรียงตามตัวอักษร ควรจะเขียนสูตรอย่างไรดีครับ
ผมใส่ลักษณะการเรียงที่ต้องการไว้ในไฟล์แนบแล้วครับ

ขอบคุณล่วงหน้าครับ

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

การเรียงแบบนั้นไม่จำเป็นต้องเรียงด้วยสูตร สามารถเข้าเมนู Data > Sort จากน้อยไปหามากได้เลยครับ

คือผมจะเอาไปลิสต์เป็นรายการใหม่เพื่อจะทำ data validation เป็นรายการให้ลูกน้องเลือก ตอนออกบิลครับ เพราะลองทำแล้ว drop down list ที่ได้มันไม่เรียงลำดับให้ มันจะเรียงตามที่คีย์ไปจริงๆ ผมจำได้ว่าเคยเห็นคนในบอร์ดโพสต์ถามครั้งหนึ่ง แล้วอาจารย์ก็ตอบไปแล้ว แต่ผมหาไม่เจอครับ

ลองดูที่นี่ครับ http://www.snasui.com/viewtopic.php?f=1 ... w=previous

snasui wrote: ลองดูที่นี่ครับ http://www.snasui.com/viewtopic.php?f=1 ... w=previous

ขอบคุณมากครับ

สูตรนี้ไม่ใช่ยากธรรมดา แต่ยาก...มาก...เลยนะครับ จะพยายามศึกษาดูครับ

ดู Link นี้ดีกว่าครับ http://snasui.blogspot.com/2011/04/blog-post_18.html Link เดิมยังไม่ได้ปรับบางอักขระในสูตรให้ถูกต้อง

snasui wrote:Link เดิมยังไม่ได้ปรับบางอักขระในสูตรให้ถูกต้อง

เครื่องหมาย < และ & สินะครับ ตอนแรก excel ไม่ยอมรับสูตร ผมเลยดูเทียบกับภาพอธิบายสูตรแล้วก็แก้เรียบร้อยแล้วครับ สูตรใช้ได้เยี่ยมไปเลย ขอบคุณมากครับ

ตอนนี้กำลังนั่งแกะสูตรอยู่ รู้สึกว่าความยากมากจะเริ่มตั้งแต่ที่คำสั่ง ISERR แล้วก็ไปหนักอยู่ตรง CODE กับ COUNTIF ถ้าผมเข้าใจไม่ผิด อาจารย์ใช้ Countif แบบไม่ธรรมดาเพื่อดัดแปลงตัวเลขที่ได้จากคำสั่ง Code ให้ไม่ซ้ำกัน จะได้สามารถใช้คำสั่ง SMALL เรียงลำดับได้ใช่ไหมครับ

เข้าใจถูกแล้วครับ

อาจารย์คนควนครับ พอดีนึกขึ้นได้ว่าจะถามตั้งแต่แรกแล้วเกี่ยวกับ
=sumproduct(1/countif(Array,Array))
เป็นสูตรแรกที่ได้จากบอร์ดนี้ ทำไมสูตรนี้ไม่ใช่สูตรอาร์เรย์ครับ แล้วมีวิธีดูคร่าวๆ ไหมว่าสูตรไหนเป็นสูตรอาร์เรย์ครับ

สูตรนี้คือสูตร Array เช่นกันเพียงแต่เราไม่ต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจาก Sumproduct มีความเป็น Array โดยธรรมชาติอยู่แล้วครับ

การสังเกตสูตร Array อย่างง่าย ๆ คือสังเกตว่าช่วงประกอบของสูตรมีการเปรียบเทียบ ไม่ว่าจะเป็น

1. การเปรียบเทียบด้วย Function เช่น
   =Isnumber(A1:A10)
2. นำเซลล์เดี่ยวหรือค่าคงที่เดี่ยวเปรียบเทียบกับช่วงเซลล์ เช่น
   =Sum(If(A1:A10<10,1))
3. นำช่วงเซลล์หรือชุดค่าคงที่เปรียบเทียวกับช่วงเซลล์ เช่น
   =Sumproduct(1/Countif(A1:A10,A1:A10))

ขอบคุณมากครับ

มารายงานผลครับ ผมลองปรับ database ดูแล้วทำให้จัดการข้อมูลได้ง่ายขึ้น ขอบคุณสำหรับคำแนะนำของอาจารย์มากครับ

แต่ตอนนี้มีปัญหาใหม่ คือหลังจากที่ลองมั่วเขียนสูตรดึงข้อมูลในชีท "บิลเงินสด" จนคิดว่าได้ผลตามต้องการ เวลาเอาไปใช้งานจริง โปรแกรมจะเสียเวลาคำนวณแต่ละครั้งที่คีย์แล้วกด enter ประมาณ 3-4 วินาที รบกวนอาจารย์ดูให้ผมหน่อยได้ไหมครับ ผมเขียนสูตรฟุ่มเฟือยไปไหม หรือผมควรปรับอะไรเพื่อให้ทำงานได้ไวขึ้น

ขออภัยที่แนบมาเป็น zip file ผมพยายามตัดแล้วแต่ก็เกิน 300k อยู่ครับ

ขอบคุณอาจารย์มากครับ

เนื่องจากมีการคำนวณหลายเงื่อนไข ในชีท GoodDat มีการใช้ Sumproduct จำนวนมาก ในชีทฐานข้อมูลก็มีสูตรซึ่งถ้าเป็นไปได้ ฐานข้อมูลควรมีแต่ Data เท่านั้นครับ

ในชีท Allist ก็ใช้สูตรในการเรียงที่ซับซ้อน การกด Enter เพื่อรับค่าที่คีย์โปรแกรมจะคำนวณสูตรเสมอหากกำหนดการคำนวณเป็น Automatic เมื่อเป็นเช่นนี้โปรแกรมคำนวณช้าและไฟล์ขนาดใหญ่แน่นอนครับ

กรณีที่เป็นการใช้ Sumproduct จำนวนมากลองเปลี่ยนเป็น Sumifs หรือ Countifs แล้วแต่ว่าเป็นการรวมหรือการนับ น่าจะช่วยให้คำนวณเร็วขึ้น