Lab: Clean Real Dataset

เป้าหมาย

นำเข้าไฟล์ CSV ที่มีปัญหา ทำความสะอาดข้อมูล ตั้ง Validation แล้วใช้ QUERY + VLOOKUP ตอบ 5 คำถามทางธุรกิจ

ข้อมูลเริ่มต้น

สร้าง Google Sheet ใหม่ชื่อ “GS-4 Lab: Clean Real Dataset” แล้วสร้างข้อมูลตามนี้:

สร้าง Sheet “raw_orders” — ข้อมูลคำสั่งซื้อ (จำลอง CSV Import)

พิมพ์ข้อมูลนี้ (จงใจใส่ปัญหาไว้):

order_id	date	customer_id	product_id	qty	status
001	2025-03-15	C001	P101	5	shipped
002	15/03/2025	C002	P203		shipped
003	2025-03-16	C001	P101	3	Shipped
004	2025-03-17	C003	P999	2	pending
005	2025-03-18	C002	P305	-1	completed
006	2025/03/19	C004	P101	10	shipped
007	2025-03-20	C001	P203	abc	pending
008	2025-03-21	C005	P305	7	Completed

ปัญหาที่ซ่อนอยู่: วันที่หลายรูปแบบ, qty เว้นว่าง/ติดลบ/ไม่ใช่ตัวเลข, status ตัวพิมพ์ไม่สม่ำเสมอ

สร้าง Sheet “products” — ตารางสินค้าอ้างอิง

product_id name price category
P101 Widget A 500 Electronics
P203 Gadget B 1200 Electronics
P305 Notebook C 150 Stationery
P410 Pen D 35 Stationery
สร้าง Sheet “clean_orders” — ทำความสะอาดข้อมูล

คัดลอก raw_orders มาแล้วแก้ไข:
- แก้ format วันที่ให้เหมือนกัน
- แก้ status ให้เป็น lowercase ทั้งหมด
- จัดการ qty ที่ผิดปกติ
- เพิ่มคอลัมน์ product_name และ total ด้วย VLOOKUP/XLOOKUP
สร้าง Sheet “validation” — ตั้ง Data Validation

สร้างแบบฟอร์มกรอกคำสั่งซื้อใหม่พร้อม Validation:
- order_id: ห้ามว่าง
- date: ต้องเป็นวันที่ในปี 2025
- product_id: Dropdown จาก products sheet
- qty: ตัวเลข 1-1000
- status: Dropdown (pending, shipped, completed)
สร้าง Sheet “analysis” — ตอบ 5 คำถามธุรกิจ

ใช้สูตร QUERY, FILTER, VLOOKUP บน clean_orders เพื่อตอบคำถาม

product_id	name	price	category
P101	Widget A	500	Electronics
P203	Gadget B	1200	Electronics
P305	Notebook C	150	Stationery
P410	Pen D	35	Stationery

5 คำถามธุรกิจ

ใน Sheet “analysis” ตอบคำถามเหล่านี้ (แต่ละข้อใช้สูตรเดียว):

ยอดขายรวมทั้งหมด (sum of total)
สินค้าที่ขายดีที่สุด (มี qty สูงสุดรวมทุก order)
ลูกค้าที่สั่งบ่อยที่สุด (จำนวน orders มากสุด)
ยอดขายแยกตาม Category (Electronics vs Stationery)
รายการที่ status = “pending” (แสดงรายละเอียดทั้งหมด)

เฉลย

Show Solution

สูตรสำหรับ clean_orders:

status (ทำให้ lowercase):
=LOWER(F2)

product_name (ดึงชื่อสินค้า):
=XLOOKUP(D2, products!A:A, products!B:B, "ไม่พบ")

price (ดึงราคา):
=XLOOKUP(D2, products!A:A, products!C:C, 0)

total (คำนวณยอดรวม):
=IF(ISNUMBER(E2), E2 * XLOOKUP(D2, products!A:A, products!C:C, 0), 0)

สำหรับ qty ที่มีปัญหา:
- Row 2 (ว่าง): ใส่ 0 หรือ flag ว่า missing
- Row 5 (ติดลบ): ใช้ =ABS(E5) หรือ flag ว่าผิดปกติ
- Row 7 ("abc"): ไม่สามารถคำนวณ → ใส่ 0 หรือ flag

ตรวจสอบ qty:
=IF(AND(ISNUMBER(E2), E2>0), E2, 0)

Show Solution

Data Validation สำหรับแบบฟอร์มกรอกข้อมูล:

order_id:  Custom formula → =A2<>""
date:      Date is between → 1/1/2025 and 31/12/2025
product_id: Dropdown from range → products!A2:A5
qty:       Number is between → 1 and 1000
status:    Dropdown → pending, shipped, completed

ทุกข้อตั้งเป็น "Reject input" เพื่อป้องกันข้อมูลผิด

Show Solution

=SUM(clean_orders!H2:H100)

หรือถ้าคำนวณ total ในแต่ละแถว:
=SUMPRODUCT(
  IF(ISNUMBER(clean_orders!E2:E100),clean_orders!E2:E100,0) *
  XLOOKUP(clean_orders!D2:D100, products!A:A, products!C:C, 0)
)

Show Solution

=QUERY(clean_orders!A1:H100,
  "SELECT D, SUM(E)
   WHERE E IS NOT NULL
   GROUP BY D
   ORDER BY SUM(E) DESC
   LIMIT 1")

Show Solution

=QUERY(clean_orders!A1:H100,
  "SELECT C, COUNT(A)
   GROUP BY C
   ORDER BY COUNT(A) DESC
   LIMIT 1")

Show Solution

=QUERY({clean_orders!D2:D100, clean_orders!H2:H100,
        XLOOKUP(clean_orders!D2:D100, products!A:A, products!D:D)},
  "SELECT Col3, SUM(Col2)
   GROUP BY Col3
   LABEL Col3 'Category', SUM(Col2) 'Total Sales'")

Show Solution

=FILTER(clean_orders!A2:H100, clean_orders!G2:G100 = "pending")

ตรวจสอบตัวเอง

ข้อมูลใน clean_orders ไม่มี format วันที่ปนกัน
status เป็น lowercase ทั้งหมด
qty ที่ผิดปกติถูกจัดการแล้ว (0 หรือ flag)
XLOOKUP ดึง product_name ได้ถูกต้อง (P999 แสดง “ไม่พบ”)
Validation ป้องกันข้อมูลผิดในแบบฟอร์มใหม่
ตอบ 5 คำถามธุรกิจได้ด้วยสูตรเดียวต่อข้อ