Excel Level 3: สร้างไฟล์ให้คนอื่นใช้ได้จริง

ไฟล์ที่ดีต้องใช้ต่อ ดูแลต่อ และตรวจต่อได้

Level 3 ไม่ได้วัดว่าสูตรยาวแค่ไหน แต่วัดว่าคนอื่นกรอกข้อมูล เพิ่มรายการ ดูรายงาน และเจอข้อผิดพลาดได้โดยไม่ต้องแก้สูตรหรือทำโครงสร้างพัง

01 · 1 hData Structure และ Grain

หนึ่งแถวแทนหนึ่ง record และหนึ่งคอลัมน์มีหนึ่งความหมาย

02 · 1.25 hTable และ Data Validation

สร้างช่วงข้อมูลที่ขยายได้และกำหนดสัญญาของค่าที่กรอก

03 · 1.25 hRule Engine

ใช้ IF/IFS กับ Threshold Table และ XLOOKUP โดยแยกกติกาออกจากสูตร

04 · .75 hWorkbook Architecture

แยก Input, Reference, Calculation และ Report ให้ data flow เดินทางเดียว

05 · 1 hProtection Scope

ปลดล็อกช่องกรอก ป้องกันสูตร และเข้าใจว่า Worksheet Protection ไม่ใช่ Security

06 · .75 hMini Case และ UAT

สร้าง Training Evaluation System แล้วทดสอบทั้งกรอกถูก กรอกผิด เพิ่มแถว และแก้สูตร

ขอบเขตระดับนี้ยังไม่รวม Macro หรือ VBA เป้าหมายคือสร้างระบบที่แข็งแรงด้วยโครงสร้าง ตาราง สูตร การควบคุมข้อมูล และกระบวนการทดสอบที่ตรวจย้อนกลับได้

กำหนด Grain ก่อนสร้างสูตร

ถามให้ชัดว่าหนึ่งแถวคืออะไร เช่น หนึ่งครั้งที่พนักงานเข้าอบรม ไม่ใช่แค่ “ข้อมูลพนักงาน” จากนั้นกำหนด stable key, ใช้หนึ่งคอลัมน์ต่อหนึ่งความหมาย และแยก Transaction ออกจาก Reference

TRANSACTIONรายการที่เกิดขึ้นซ้ำ

RecordID · EmployeeID · CourseID · Date · Score

REFERENCEข้อมูลแม่บทที่ใช้ร่วมกัน

Employees · Courses · Grade Criteria

Excel Table ทำให้ช่วงมีตัวตน; Validation ทำให้ข้อมูลมีสัญญา

Ctrl+T เปลี่ยน Range ให้เป็น Table ที่มีชื่อคอลัมน์และขยายตามแถวใหม่ Structured Reference อ่านเป็นภาษางานได้ ส่วน Data Validation ต้องกำหนด list source, input message และ error alert แล้วทดสอบค่าที่ผิดจริง

STRUCTURED REFERENCE=SUM(tblInput[Score])ชื่อ Table และคอลัมน์บอกความหมาย

ช่วงขยายตามข้อมูลใหม่

DATA CONTRACTStatus → Completed, In Progressค่าที่อนุญาตต้องมีแหล่งเดียว

Dropdown ช่วยลดความต่างของการสะกด ไม่ได้แทนการทดสอบ

แยกกติกาออกจากสูตรเพื่อให้ดูแลได้

เขียน Business rule, Boundary cases และ Test cases ก่อนเลือกสูตร IF เหมาะกับเงื่อนไขไม่กี่ทาง IFS คืนค่าจาก TRUE แรก ส่วนเกณฑ์ที่เปลี่ยนได้ควรอยู่ใน Threshold Table แล้วให้ XLOOKUP อ่านเกณฑ์นั้น

IFS=IFS(Score>=80,"A",Score>=70,"B",TRUE,"Review")ลำดับเงื่อนไขมีผล

วางเงื่อนไขเฉพาะก่อน default

THRESHOLD TABLE=XLOOKUP(Score,tblGrade[MinScore],tblGrade[Grade],,-1)แก้เกณฑ์ที่ตาราง ไม่กระจายทุกสูตร

ทำให้ audit และปรับเกณฑ์ง่ายกว่า Nested IF

Data Flow ต้องเดินทางเดียว

แยกหน้าที่ชีตเป็น Input → Reference → Calculation → Report → Decision ผู้ใช้กรอกเฉพาะ Input, สูตรอ่าน Master จาก Reference, Calculation ทำงานกลาง และ Report แสดงผลโดยไม่ให้คนแก้สูตรเพื่อเพิ่ม record

01 Input→02 Reference→03 Calculation→04 Report→05 Decision

Input UI ที่ดีบอกได้เองว่าช่องใดกรอก ช่องใดห้ามแก้ และค่าใดผิด โดยไม่ต้องให้ผู้สร้างไฟล์ยืนอธิบายทุกครั้ง

Protection ลดการแก้โดยไม่ตั้งใจ แต่ไม่ใช่ Security

สถานะ Locked จะมีผลเมื่อ Protect Sheet เท่านั้น จึงต้อง Unlock ช่องกรอกก่อน Protect สูตร แยก scope ของ Cell, Sheet, Workbook และ File ให้ถูก และอย่าใช้รหัสผ่าน Worksheet เป็นคำอ้างว่าข้อมูลลับปลอดภัย

Scope	ควบคุมอะไร	ข้อจำกัด
Cell	Locked / Unlocked	มีผลเมื่อ Protect Sheet
Sheet	การแก้เซลล์และคำสั่งบางส่วน	ไม่ใช่การเข้ารหัส
Workbook	เพิ่ม ลบ ย้าย หรือเปลี่ยนชื่อชีต	ไม่ป้องกันข้อมูลลับ
File	สิทธิ์เปิดหรือแก้ไฟล์	ต้องพิจารณา encryption และระบบสิทธิ์จริง

Mini Case: Training Evaluation System

ระบบปลายทางรับข้อมูลการอบรม ดึงพนักงานและหลักสูตรจาก Master ตัดเกรดจาก Threshold Table ตรวจ PassStatus และ IssueNote สรุป Report แล้วใช้ UAT Checklist ยืนยันทั้งเส้นทางปกติและกรณีผิดพลาด

END STATEเพิ่ม record แล้วระบบขยายเอง

Input เป็น Table, สูตรและ Validation เดินตามแถวใหม่

UATทดสอบทั้งดีและพัง

กรอกถูก กรอกผิด เพิ่มแถว แก้สูตร ตรวจ Report และตรวจ exception

Input Table→Master Lookup→Grade Engine→Pass / Review→Report

ดาวน์โหลด

เรียน 6 บท เปิด 6 ไฟล์ แล้วใช้ UAT ตรวจระบบ

ฝึกใน Excel

ชุดไฟล์ฝึก (.zip)

ใช้ทั้ง 6 หัวข้อเพื่อออกแบบ Structure, Validation, Rules, Architecture, Protection และ UAT

ดาวน์โหลดชุดไฟล์ฝึก↓

จดและทบทวน

Handout PDF

เอกสาร A4 ขาวดำ 12 หน้า ใช้ดูสารบัญ ทำกิจกรรม และจดบันทึกระหว่างเรียน พิมพ์ใช้งานได้ทันที

ดาวน์โหลด Handout PDF↓

หรือเลือกดาวน์โหลดเฉพาะไฟล์ที่ต้องใช้

ชุดไฟล์ฝึกครบ (.zip)

6 module workbooks, HRcade และ README พร้อมลำดับการใช้

STUDY PACK · VERSION 2.0 01 · Structure และ Grain

จัดหนึ่งแถวต่อหนึ่ง record กำหนด key และซ่อมโครงสร้างข้อมูลที่เสี่ยง

XLSX · MODULE 01 02 · Table และ Validation

สร้าง Table, Structured Reference และรายการค่าที่อนุญาต

XLSX · MODULE 02 03 · Rule Engine

IF · IFS · AND · OR · XLOOKUP · IFERROR · Boundary Test

XLSX · MODULE 03 04 · Workbook Architecture

Input → Reference → Calculation → Report

XLSX · MODULE 04 05 · Protection

ฝึก Locked, Protect Sheet, Workbook Structure และข้อจำกัดของการป้องกัน

XLSX · MODULE 05 06 · Evaluation System และ UAT

ประกอบระบบปลายทางและบันทึก Expected, Actual, Pass/Fail

XLSX · MODULE 06 ฝึกสร้าง Training Evaluation System

ใช้ทั้ง 6 หัวข้อเพื่อออกแบบ Structure, Validation, Rules, Architecture, Protection และ UAT

XLSX · PRACTICE เฉลยและสูตรตัวอย่าง

เปิดหลังทำแบบฝึกเพื่อเทียบสูตร วิธีคิด และผลลัพธ์

XLSX · ANSWER ชุดคำถาม HRcade

Pre-test 10 ข้อและ Post-test 18 ข้อพร้อมคำอธิบาย

XLSX · HRCADE README TH/EN

เปิดก่อนใช้ชุดไฟล์ เพื่อดูว่าแต่ละไฟล์ใช้ช่วงไหน

TXT · START HERE

ชุดไฟล์ฝึกใช้ข้อมูลจำลองทั้งหมด จึงทดลองแก้ข้อมูลและสูตรได้โดยไม่กระทบข้อมูลจริง

ทบทวน

เกณฑ์ผ่านคือระบบส่งต่อได้ ไม่ใช่สูตรยาวที่สุด

จบด้วย Post-test 18 ข้อและ UAT Checklist ระบบควรรับค่าที่อนุญาต ปฏิเสธค่าผิด เพิ่มแถวโดยสูตรไม่หลุด แสดง Report ถูก และกันการแก้สูตรโดยไม่ทำให้ช่องกรอกใช้ไม่ได้

เปิด HRcade↗

Excel Level 3สร้างไฟล์ให้คนอื่นใช้ได้จริง