excel inventory management

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 2 แยกตารางซื้อขาย

จากที่เราทำไฟล์ Excel บริหาร Stock แบบง่ายสุดๆ (version 1) ไปแล้วในบทความก่อน คราวนี้เราจะมาทำให้ไฟล์มันเจ๋งขึ้นกว่าเดิม โดยการแยกตารางซื้อขายออกมาให้ชัดเจน จะได้บันทึกข้อมูลง่ายขึ้นครับ ก่อนอื่นเราเปลี่ยนชื่อ sheet เดิมเป็น summary แล้วสร้าง Sheet เพิ่ม 2 อัน คือ ซื้อ กับ ขาย Sheet ซื้อ : เตรียมคอลัมน์ดังนี้ วันที่ซื้อ, Product, จำนวนซื้อ จากนั้นแปลงเป็น Table ซะ โดยกด Ctrl+T แล้วเลือกเรื่องหัวตารางด้วยว่ามีหรือไม่ Data Validation เดี๋ยวเราจะใส่ Data Validation ลงไปในแต่ละช่อง จะได้กรอกข้อมูลไม่ผิด วันที่ เลือกข้อมูลช่องวันที่ตามรูป แล้วไปที่ Data -> Data…
Excel Stock Inventory Simple

สอนทำไฟล์ Excel บริหาร Stock สินค้าคงคลัง : Version 1 ง่ายสุดๆ

เรื่องการบริหาร Stock เป็นปัญหายอดฮิตอันนึงที่หลายคนต้องเจอ เพราะธุรกิจส่วนใหญ่จะมีการซื้อขายสินค้า ดังนั้นการบริหาร Stock จึงเป็นเรื่องที่หลีกเลี่ยงไม่ได้เลย โดยเฉพาะกับธุรกิจ SME ที่ยังไม่มีระบบบริหารจัดการที่ดี ก็อาจยังต้องใช้ Excel มาช่วยอยู่มากพอสมควร ความฮิตของเรื่องนี้สะท้อนมาให้เห็นในผลโหวตในเพจ ที่ผมถามไปว่า "ระหว่าง บริหาร Stock สินค้า กับ การเทียบยอดขายกับ Target อยากให้ทำเรื่องไหน?"ตัวที่ได้ผลโหวตมากกว่า คือเรื่องการบริหาร Stock สินค้านั่นเอง แสดงว่ามีคนมีปัญหานี้เยอะจริงๆ ก่อนหน้านี้ (นานมาแล้ว...) ผมเคยเขียนเรื่องการบริหาร stock ไว้นิดหน่อยแล้วล่ะ แต่คราวนี้จะเขียนให้ครบทุกมิติมากขึ้น และละเอียดขึ้น ยังไงลองติดตาม Series นี้ได้เลยครับ ความสัมพันธ์ของ Stock สินค้า Stock ตั้งต้น + การเปลี่ยนแปลง = Stock คงเหลือ หรือจะเขียนให้ละเอียดอีกนิดได้ว่า Stock ตั้งต้น…
สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA) 1

สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)

คุณเคยมีปัญหามีรายการที่จะต้องแสดงใน Drop Down List จำนวนมากจนแสดงไม่ไหวหรือไม่? วิธีแก้อันนึงที่ผมนำเสนอไปแล้วคือ การทำ Drop Down หลายชั้น แต่หากเราสามารถที่จะทำให้คนใช้งานพิมพ์ข้อความบางส่วนแล้วแสดงรายการที่มีคำนั้นๆ ได้เลยจะดีขนาดไหน? หลายคนคิดว่าเรื่องแบบนี้ต้องใช้ VBA ทำเท่านั้น แต่ในความเป็นจริง เราก็สามารถเขียนสูตรเพื่อสร้าง "Drop down List แบบค้นหาได้" โดยจะใช้ Excel Version ไหนก็สามารถทำได้ครับ สุดยอดไปเลยมั้ยล่ะ!! คลิปวีดีโอ ในคลิปนี้ผมมีการอธิบายโดยละเอียดถึงวิธีทำตั้งแต่ต้นจนจบ ลองดูได้เลยครับ https://youtu.be/KeKfVf8AHZY ไฟล์ประกอบการทำตาม ไฟล์เริ่มต้น : https://drive.google.com/open?id=1qiWOJjoB9dGyPDt9HjByaCLUwky8_jqX ไฟล์จบ : https://drive.google.com/open?id=17QXXR5xVtHqxPgU1ay5biyg4Vcd1ypod หลักการทำงานของ Drop Down List แบบค้นหาได้ ใช้ ISNUMBER + SEARCH เพื่อเช็คว่ารายการไหน มีคำค้นหาอยู่บ้างพยายามเอารายการที่เจอ มากองรวมกันข้างบนด้วย INDEXในนี้ผมมีใช้ฟังก์ชัน…
work time calculation

วิธีคำนวณระยะเวลาทำงาน แบบมีช่วงเวลาหยุดพัก

ปัญหาหนึ่งที่มีคนถามมาบ่อยๆ เกี่ยวกับเรื่องของการคำนวณเวลา นั่นก็คือ การคำนวณระยะเวลาทำงาน เมื่อเรารู้เวลาเริ่มงาน เวลาเลิกงาน และมีการกำหนดช่วงเวลาพักไว้ เช่น Break อาจมีหลายช่วงด้วย เช่น พัก1 ตอน 12:00-13:00 และ พัก2 ตอน 17:00-18:00 เป็นต้น ในบทความนี้ผมจะขอเขียนอธิบายแนวทางในการคิด ซึ่งหากเข้าใจแล้วจะมีช่วงพักกี่ช่วงเราก็ไม่กลัวครับ ในบทความนี้ผมเตรียมข้อมูลอยู่ในรูปแบบ Table เพื่อให้เห็นชื่อ Field ชัดๆ ตอนเขียนสูตรดังนี้ (โหลดไฟล์ได้ที่นี่) แนวคิด เนื่องจากมีการพักสองช่วงเวลา ดังนั้นผมจะขอแบ่งการทำงานออกเป็น 3 Part ดังนี้ เพื่อคำนวณเวลาการทำงานในแต่ละ Part จากนั้นค่อยเอามารวมกันอีกที การคำนวณระยะเวลาใน Part 1 เวลาจบ : MIN([@เริ่มพัก1],[@เลิกงาน]) เวลาเริ่ม : MAX([@เริ่มงาน],0) ที่เทียบกับเลข 0 เพราะว่าเวลา 0:00…
extract-text-excel

สารพัดวิธีดึงข้อความหลังตัวคั่นตัวที่สอง

หลายวันก่อนผมมีการ Post คำถามนี้ ไปใน เพจเทพเอ็กเซล ว่า จะดึงข้อความหลังเครื่องหมาย - ตัวที่สองได้อย่างไร? ปรากฏว่ามีคนสนใจมาตอบเป็นจำนวนมากกกกก กว่าที่ผมคิดไว้เยอะเลย และมีหลายท่านได้นำเสนอวิธีที่น่าสนใจและควรค่าต่อการนำมาอธิบายต่อมาก ดังนั้นผมจะขออนุญาตนำวิธีของแต่ละท่านที่ตอบมาอธิบายให้ละเอียดมากขึ้น เผื่อที่คนมาอ่านจะได้นำไปต่อยอดความรู้ของตัวเองได้ครับ (ลองไปดูใน Post ได้นะครับว่าใครตอบแบบไหนมา หลายคนก็ตอบวิธีเดียวกันครับ) ขอเริ่มจากวิธีที่ผมคิดว่าง่ายที่สุดก่อนละกันนะครับ วิธีที่ 1 : ใช้ Flash Fill หลักการ : Excel จะพยายามหา Pattern ของสิ่งที่เราใส่เป็นตัวอย่าง แล้วเลียนแบบสิ่งนั้นให้โดยอัตโนมัติ (แต่ไม่ใช่สูตร หากข้อมูลต้นทางเปลี่ยนต้องกดคำสั่ง Flash Fill ใหม่) ข้อจำกัด : ใช้ได้ตั้งแต่ Excel 2013 ขึ้นไป แนะนำมาโดย : Bob Pytnst, Noppadol Rattanawisadrat, Taekuza…
ใช้ Excel คำนวณมูลค่าเงินในอนาคตจากการลงทุน 2

ใช้ Excel คำนวณมูลค่าเงินในอนาคตจากการลงทุน

"ถ้าเราลงทุน x บาท ด้วยผลตอบแทน z% เป็นเวลา y ปี สุดท้ายจะมีเงินเท่าไหร่ครับ?" นี่คือคำถามที่ผมได้รับ หลังจากที่ผม Post เรื่องการคำนวณยอดผ่อนเงินกู้ไป ซึ่งได้รับผลตอบรับที่ดีมากๆ (มีคนสนใจเรื่องกู้เงินเยอะขนาดนี้เลยเหรอเนี่ย!! น่ากลัวนิดๆ นะ 555 ) เลยคิดว่า เมื่อมีคนสนใจเรื่องการลงทุนด้วย ยิ่งเป็นสิ่งที่ควรสนับสนุนเลย เพราะยิ่งลงทุนเร็วยิ่งได้เปรียบมากๆ นะครับ (อ่านจบแล้วจะเห็นว่าระยะเวลาการลงทุน สำคัญขนาดไหน) ดังนั้นผมจะสอนวิธีใช้ Excel คำนวณมูลค่าเงินในอนาคตแบบง่ายๆ ได้ด้วยฟังก์ชัน FV ใน Excel เองครับ ใช้ Excel คำนวณการลงทุน FV เราสามารถคำนวณมูลค่าเงินในอนาคตได้ด้วยฟังก์ชัน FV (Future Value) ได้ครับ สูตรเดียวจบ แถมรองรับการใส่เงินเพิ่มในแต่ละเดือนได้ด้วยนะ ซึ่งมีสูตรดังนี้ =FV(rate,nper,pmt,[pv],[type]) จะเห็นว่าสูตรขึ้นอยู่กับ Input หลายๆ…
สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ 3

สอนใช้ Excel คำนวณยอดผ่อนเงินกู้แบบง่ายๆ

ในฐานะที่ทำงานเกี่ยวกับสินเชื่อในธนาคารมาก่อน วันนี้ผมจะมาสอนใช้ Excel คำนวณเรื่องเงินกู้แบบง่ายๆ ใครๆ ก็ทำได้ครับ ทำแป๊ปเดียว ไม่ต้องสร้างตารางผ่อนอะไรให้ยุ่งยากด้วย การผ่อนเงินกู้โดยทั่วไปสำหรับคนทั่วไป จะแบ่งเป็น 2 ลักษณะ คือ แบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate เช่น การผ่อนรถยนต์) แบบลดต้นลดดอก (ดอกเบี้ย Effective Rate แบบผ่อนบ้าน หรือ สินเชื่อธุรกิจ) ซึ่งผมจะสอนวิธีคิดทั้งคู่เลยครับ เอาล่ะ สมมติเราจะกู้เงิน 1 ล้านบาท ดอกเบี้ย 6% ต่อปี แล้วอยากรู้ว่าจะต้องผ่อนเดือนละกี่บาท (เดือนละเท่าๆ กัน) จึงจะผ่อนหมดพอดี ในระยะเวลา 5 ปี เราก็กรอกข้อมูลดังรูปเลย ยอดผ่อนแบบเงินต้นคงที่ (ดอกเบี้ย Flat Rate) ทีนี้จะเริ่มจากตัวที่เข้าใจง่ายก่อน ก็คือการผ่อนรถ ซึ่งโดยทั่วไปจะคิดดอกเบี้ยแบบ Flat…
วิธีจัดการข้อมูลซ้ำใน Excel (แบบลึกซึ้ง) 4

วิธีจัดการข้อมูลซ้ำใน Excel (แบบลึกซึ้ง)

หากเราไม่แน่ใจว่ามีข้อมูลซ้ำอยู่ในรายการข้อมูล Excel หรือไม่? แล้วเราอยากจะรู้หรืออยากจะทำให้ข้อมูลมันไม่ซ้ำกัน เราจะทำยังไงดี? มาดูกันครับ สถานการณ์ คือ เรามีข้อมูล เป็น Customer ID และวันที่ของการบันทึกข้อมูลลูกค้า สมมติว่า เราอยากจะได้เฉพาะข้อมูลครั้งล่าสุดเท่านั้น เราจะทำยังไงดี? แนวทาง #1 : ลบข้อมูลซ้ำด้วย Remove Duplicates เครื่องมือ Remove Duplicates มันจะเก็บข้อมูลไว้เฉพาะบรรทัดบนสุดเท่านั้น ดังนั้นก่อนจะใช้เครื่องมือนี้ ผมแนะนำให้เรียงข้อมูลให้อยู่ในลักษณะที่ หากข้อมูลซ้ำกัน ให้เอาแถวที่เราอยากได้ไว้บนสุดซะก่อนครับ โดยใช้ Data --> Sort ก่อน ในที่นี้เราจะเรียงตาม CustomerID ก่อน แล้วเรียงตามวันที่ โดยเอาวันที่ใหม่สุดไว้บน จากนั้นเราจะได้ข้อมูลที่เรียงกันในแบบที่อยากได้แล้ว ให้เราเลือกข้อมูลช่องใดช่องหนึ่ง แล้วไปที่ Data -> Remove Duplicates แล้วให้ติ๊กเฉพาะ CustomerID เท่านั้น…
เปรียบเทียบข้อมูล

วิธีเปรียบเทียบข้อมูล 2 List ว่ามี item ไหนที่ต่างกัน?

"ไม่ทราบว่ามีวิธีเปรียบเทียบข้อมูลใน Excel จากข้อมูล 2 รายการหรือไม่ ว่ามี item ไหนต่างกันบ้างครับ?" คำถามนี้มีคนถามบ่อยพอสมควร เลยเขียนเป็นบทความให้อ่านกัน คนอื่นๆ จะได้รับประโยชน์ไปด้วยเนอะ เอาล่ะ! สมมติผมมีข้อมูลอยู่ 2 List ซึ่งอยู่คนละ Sheet ดังนี้ SheetA SheetB จะเห็นว่ามีทั้งรายการที่มีใน A แต่ไม่มีใน B รวมถึง มีใน B แต่ไม่มีใน A ด้วย ผมจะเสนอวิธี Compare List นี้ 2 วิธีด้วยกัน คือ1. วิธีใช้ VLOOKUP2. วิธีใช้ Power Query ซึ่งความพิเศษของบทความนี้ คือ ผมจะแสดง Step การแก้ปัญหาเหมือนกันเป๊ะๆ เลย แต่ใช้แค่เครื่องมือต่างกันเท่านั้นเอง…
สอน 2 เทคนิคการทำ VLOOKUP หลายค่า จากคำค้นหาเดียว 5

สอน 2 เทคนิคการทำ VLOOKUP หลายค่า จากคำค้นหาเดียว

จะทำยังไงถึงจะทำ VLOOKUP หลายบรรทัด จาก Lookup_Value เดียวได้นะ?? นี่คือคำถามที่เกิดขึ้นบ่อย เพราะปกติแล้ว เวลาเราใช้ VLOOKUP หรือจะ INDEX MATCH ก็แล้วแต่ หาก Lookup_Value ซ้ำกัน มันจะได้ผลลัพธ์เป็นตัวบนสุดเสมอ บทความนี้ผมจะมาบอกวิธีทำ VLOOKUP เพื่อแสดงผลลัพธ์หลายบรรทัดให้เอง และจะทำให้ดูหลายแบบด้วยครับ!! โจทย์คือแบบนี้ครับ เราต้องการเลือกสินค้า แล้วให้แสดง Order ID ทั้งหมดของสินค้านั้นออกมา (ถ้าเราเอา Order ID ออกมาได้แล้ว อย่างอื่นก็คงไม่ยากแล้วจริงมั้ยครับ?) วิธีที่ 1 : ใช้สูตรเพื่อทำ vlookup หลายบรรทัด ใช้ VLOOKUP + Helper Column ในเมื่อ ปัญหาคือมี lookup_value ซ้ำกันหลายตัว ใน table_array…