สร้างฐานข้อมูลสินค้าด้วยตาราง Google Sheet

เพื่อศึกษาความรู้ด้าน สร้างฐานข้อมูลสินค้าด้วยตาราง Google Sheet สามารถนำไปปรับ หรือประยุกต์ใช้งานตามความต้องการส่วนบุคคลต่อได้ 

สิ่งที่ต้องมี

บัญชี Google Drive

สร้างตารางบันทึกข้อมูลสินค้า 

นำเข้าข้อมูลสินค้าตัวอย่าง

ความรู้เพิ่มเติมของ

SKU ย่อมาจาก store stocking unit สำหรับโรงงานผู้ผลิต เป็นกล่อง หรือหีบห่อ สินค้าแต่ละชนิด เช่น รองเท้ากีฬา รุ่น ยี่ห้อ สี ขนาด บรรจุในแพค กล่อง ลัง จำนวน 12 หรือ 24 คู่เป็นต้น 

ส่วน Serial Number เป็นรหัสที่แตกต่างกันของสินค้าแต่ละรายการ เป็นหน่วยย่อย 

สร้าง Table เก็บข้อมูลสินค้น ประกอบด้วย 

SKU

Description คำอธิบายสินค้า

Pack จำนวนบรรจุต้อกล่องหรือต่อลัง 

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

สร้างแผ่นงานที่ 2 เพิ่ม เพื่อแยกบันทึกข้อมูล 

SKU

Description คำอธิบายสินค้า

Pack จำนวนบรรจุต้อกล่องหรือต่อลัง 

QTY จำนวนสินค้า 

QTY Expanded แยกกล่อง

Cost piece ราคาต่อหน่วยแยก

Total cost ราคารวมกล่อง 

*** เริ่มจาก การทำให้ SKU ของแผ่นงานที่ 2 เป็นเมนูตัวเลือกแบบ รายการ หรือ Drop Down List 

โดย คลิกเลือก คอลัมน์ SKU แล้ว ไปที่แท็บ DAta แล้วเลือก Data Validation 

ระบุ Souce ที่จะใช้เป็น รายการตัวเลือก เลือกจาก แผ่นงานที่ 1 คอลัมน์ SKU แนะนำให้เลือกเผื่ออนาคต ที่มีการเพิ่มรายการสินค้าเข้ามา ลองเลือกดูสัก 500 หรือ 900 รายการ ก็ได้ 

** เลือก รูปแบบการป้อนข้อมูล เป็น Reject คือห้ามป้อนข้อมูล ต้องนำเข้าจากรายการตัวเลือกเท่านั้น 

เลือกแล้ว Autofill คำอธิบายรายการสินค้า  และจำนวนบรรจุผลิตภัณฑ์ ทำอย่างไร 

จะใช้ฟังก์ชั่น Vlookup มาช่วยทำงาน 

การปรับแต่งฟอร์มบันทึกข้อมูล Google Sheet ตอน 2

 เราจะมาปรับปรุงหน้าจอฟอร์มบันทึกข้อมูล จากเดิม มีช่องบันทึกชื่อเท่านั้น เราจะมาปรับเพิ่มช่องบันทึกข้อมูล และ การสร้าง ตัวเลือก Drop Down List กัน 

รวมทั้งความรู้ในเรื่องของการปรับแต่ง หน้าตาของฟอร์มด้วยการจัดการรูปแบบ HTML CSS กัน

ความรู้ในเรื่องของ ภาษาHTML  JAva Script 

การแยกไฟล์ CSS และ ไฟล์ JS ออกจากหน้าแบบฟอร์ม HTML 

สำหรับไฟล์ประกอบ สามารถดูและคัดลอกได้ ท้ายคลิปในช่อง ยูทูป ครับ 

ชมคลิปตัวอย่าง

ถ้ามีเวลา และต้องการศึกษาวิธีการจริงๆ จังๆ แนะนำ ให้ ฝึกเขียน ที่ละบรรทัดด้วยตนเอง ครับ

การเขียนโค้ดเพื่อบันทึกข้อมูลลงในตาราง Google Sheet

 เดิมแอดมินเองก็เคยศึกษาการเขียน Code ในการบันทึกข้อมูล เข้าสู่ตารางข้อมูลในรูปแบบของ ASP และ HTML โดยอาศัย Script การทำงาน ต้นแบบจากเว็บไซต์ต่างๆ เป็นแนวทางและ ปรับปรุงใช้งาน ตามต้องการ 

สำหรับยุค 2020 ตอนนี้ ตอนที่ระบบฐานข้อมูลแบบเปิด Cloud มีบทบาทและความสำคัญมากขึ้น และผู้ใช้งานอินเตอร์เน็ต ก็เข้าถึงได้มากขึ้น รวดเร็วขึ้น สะดวกขึ้น ข้อมูลภาพ ข้อความ วีดีโอ สามารถเข้าถึงประมวลผลได้รวดเร็ว บนอุปกรณ์พกพา โทรศัพท์มือถือ แท็บเล็บ โน้ตบุ้ค เป็นต้น 

สำหรับความรู้ที่อยากนำมาแชร์ ในวันนี้ คือ การ ใช้พื้นที่ cloud ส่วนตัวของทุกคน Google Drive ในการสร้างตารางสำหรับจัดเก็บข้อมูล จาก User หรือผู้ใช้งาน ผ่านการเขียน Code หรือ Script ง่ายๆ สำหรับ นักเรียน นักศึกษา หรือผู้ใช้งานคอมพิวเตอร์ทั่วไป ที่สนใจเริ่มต้นการเขียน Code เพื่อจัดการข้อมูล โดยไม่มีค่าใช้จ่าย 

ขั้นแรกไปที่ Google drive : GD สร้าง scipt ขึ้นมาตั้งชื่อว่า MY FOM

เพื่อใช้ลิงค์ไปที่ตาราง Google sheet และรับข้อมูลจากหน้าแบบฟอร์ม HTML 

เริ่มต้นด้วย 

Function doget() {

การส่งกลับค่า เป็นตัวเลข หรืออักษร

retun HtmlSevice.createHtmlOutputFromFile("ชื่อไฟล์")

โดยไปสร้างไฟล์ที่เมนู File ตั้งชื่อไฟล์เป็น Form HTML

แล้วเขียน code เพื่อแสดงข้อมูลพื้นฐาน 

แสดงส่วนหัว

<h1>Heading</h1>

เพิ่มส่วน input

<label>Name:</label><input type="text" id="username">

<button id="btn">ส่งข้อมูล</button>

บันทึกแล้วลองทดสอบ Script 

จะแสดงข้อมูล Heading 

เพิ่มการติดต่อกับผู้ใช้งาน โดยพื้นฐานคือการส่งผ่านค่าข้อมูล passing parameter 

เพิ่มปุ่มกด 

และระบุรหัส ID ให้ปุ่มกด เพื่อโยงกับภาษา JAVA SCIPT อีกที เริ่มด้วย

เมื่อมีคนกดปุ่ม จะให้ script ทำงานอะไร 

<script>

document.getElementById("btn").addEventListener("click",doStuff);

function doStuff(){

var uname=document.getElementById("username").value; ค่าที่ผู้ใช้งานป้อนชื่อเข้ามา 

google.script.run.userClicked(uname); 

document.getElementById("username").value="";

}

</script>

กลับไปเขียนโค้ดรองรับการทำงานของ script นี้ ในส่วนของ code ใน MY FORM

Function doget(e) {

Logger.log(e.parameter);

retun HtmlSevice.createHtmlOutputFromFile("Form HTML")

}

function userClicked(name){

คัดลอก url ของตาราง google sheet มาใส่

var url ="ใส่ค่า url ไฟล์ตาราง google sheet ของเรา";

var ss = SpreadsheetApp.openByUrl(url);

var ws =ss.getSheetByName("ชื่อชีทไฟล์ของเรา");

ws.appenRow([name]);

ws.appenRow([name,new Date]);

Logger.log(name+"Someone Clicked the Button");

}

กลับไปเพิ่ม script ที่หน้าฟอร์ม HTML 

ทดสอบรัน script จะพบว่า ดูใน Log ไฟล์ จะมีข้อมูลเก็บ Log ไว้ 

เราจะใช้พื้นฐานการเก็บค่า Log ในการเพิ่มช่องนำเข้าข้อมูล input ในส่วนของฟอร์มผู้ใช้งาน 

ขั้นตอนสุดท้าย คือการส่งค่าที่ได้รับจากการเปิดดูใน Log ไฟล์ ไปบันทึกลงในตาราง Google sheet 

การตรวจสอบค่าซ้ำแล้วลบออกตาราง Google Sheet

 การใช้สูตรสำหรับการค้นหาค่าซ้ำ แล้วลบออก ในตารางข้อมูล Google Sheet 

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

การป้องกัน คือทางแนะนำที่ดีที่สุด โดยใช้เงื่อนไข การป้องกันใส่ค่าซ้ำ 

กรณีมีค่าซ้ำเข้ามา เราก็สามารถ ตรวจสอบ และค้นหา และลบออก ได้ เพื่อ ให้การทำงาน เป็นไปตามต้องการ 

สูตรที่แนะนำคือ =ArrayFormula(IF(LEN(A2:A),if(SORT(ROW(A2:A)-ROW(A2)+2,SORT(ROW(A2:A)-ROW(A2)+2,B2:B,1),1)-MATCH(B2:B,SORT(B2:B),0)=1,,0),))

การนำสูตรไปใช้งาน ในช่อง TEST =ArrayFormula(IF(LEN(A2:A),if(SORT(ROW(A2:A)-ROW(A2)+2,SORT(ROW(A2:A)-ROW(A2)+2,B2:B,1),1)-MATCH(B2:B,SORT(B2:B),0)=1,,0),))

ค่าซ้ำ คือ ในคอลัมน์ B เป็นการบันทึก ชื่อ - สกุล เข้ามา ซ้ำกัน

ค่าที่กรองออกมาแล้วซ้ำกัน คอลัมน์ F จะแสดงขึ้นมาด้วยเลข 0

การลบข้อมูลที่ซ้ำกันออก

ให้ใช้ตัวกรองข้อมูลในคอลัมนื F โดยเลือกแสดงค่าเลข 0

จากนั้น ให้เลือกแถวทั้งหมด แล้ว ลบออกได้

ยกเลิกการกรองข้อมูล

จะเหลือข้อมูลที่ไม่มีค่าซ้ำ

** บันทึก

แนะนำให้ลองใช้งานกับฐานข้อมูลจำลอง เพื่อดูผลการทำงาน ที่ถูกต้อง ไม่มีข้อผิดพลาดของสูตรการทำงาน จากนั้นจึงนำไปใช้งานกับ ฐานข้อมูลจริง ต่อไป

การใช้งานฟังก์ชั่น V-LOOKUP ของ MS EXCEL

 ขั้นตอนแรก 

เตรียมตารางข้อมูลเพื่อใช้งานในการค้นหา

ฟังก์ชั่น V-LOOKUP หมายึงแปลตามตัว การค้นหาข้อมูลในแท่งแนวดิ่งของตาราง คอลัมน์ 

เช่น เรามีข้อมูลในตารางจำนวนมากๆ Big data บางครั้ง การค้นหาข้อมูล อาจจะช้าไป การใช้ vlookup ก็สามารถเป็นทางเลือก 

อธิบายตามตัวอย่าง 

เราเก็บข้อมูลนักเรียน ข้อมูลสินค้น และรายลเอียดประกอบอื่นๆ จำนวนมาก ไว้

วันดีคืนดี เราต้องการเรียนกดูข้อมูลของ นักเรียนสักคน โดยใช้ vlookup กัน 

ขั้นแรก คลิกช่องที่ต้องการใส่สูตร =VLOOKUP กดแท็บ จะได้วงเล็บ ระบุค่า lookup_value ให้ระบุเป็นค่า ชื่อ-สกุลนักเรียน B2 ลงไป 

ในส่วนของ table_array เราไม่ต้องระบุ ข้ามไปเลย

ใส่ ,

หลังคอมม่า ให้เลือกแหล่งข้อมูลที่ต้องการค้นหา แดรกเม้าส์คลุม ทั้งหมด จะขึ้น ค่าแถวแรก ถึงสุดท้าย 

และใก้กดแป้น F4 หนึ่งครั้งจะเป็นการแทรก $ ลงในสูตร เพื่อช่วยในการทำงานของสูตร 

เวลามีการคัดลอกสูตรไปใช้งาน 

ใส่ , 

หลังคอมม่า จะเป็นการระบุ คอลัมน์ที่ต้องการแสดงข้อมูล ในตัวอย่างคือ 3,0

ค่าสูตร คือ =VLOOKUP(B2,$G$2:$J$42,3,0)