Microsoft Excel Advanced

Day 1: สูตรคำนวณขั้นสูงและสูตรใหม่ใน Microsoft 365

Section 1: Microsoft Excel สำหรับการใช้งานระดับสูง

• ความสามารถและขอบเขตของ Excel ที่เหมาะกับงานแต่ละประเภท และส่วนประกอบที่ผู้ใช้ระดับสูงควรรู้จัก
• ประเภทไฟล์ Excel (.xlsx, .xlsm, .xlsb, .csv) การตรวจสอบ Excel Version และผลกระทบต่อฟังก์ชันที่ใช้ได้
• แนวคิดการออกแบบไฟล์งานให้ดูแลรักษาง่าย (Data Layer, Calculation Layer, Report Layer) พร้อม Workshop สำรวจโครงสร้างไฟล์ตัวอย่าง

Section 2: คีย์ลัด (Shortcut Key) ที่จำเป็นต่อการทำงาน

• การเคลื่อนที่และการเลือกช่วงข้อมูลขนาดใหญ่อย่างแม่นยำโดยไม่ใช้เมาส์
• คีย์ลัดสำหรับการจัดรูปแบบ การทำงานกับสูตร การคัดลอก และ Paste Special
• Workshop: แบบฝึกหัดจับเวลา ทำงานชุดเดิมด้วยเมาส์เทียบกับการใช้คีย์ลัด

Section 3: การทำงานกับ Name ใน Excel

• Name คืออะไร ช่วยให้สูตรอ่านง่ายขึ้นอย่างไร พร้อมกฎการตั้งชื่อและการแก้ไข
• การอ้างอิง Name กับ Cell, Range และ Formula การใช้ Name Manager และการสร้าง Dynamic Name
• Workshop: แปลงสูตรอ้างอิงแบบ Cell Reference ในรายงานตัวอย่างให้เป็นการอ้างอิงด้วย Name

Section 4: ฟังก์ชันในการกำหนดเงื่อนไข

• ทบทวน IF และข้อจำกัดของการซ้อน IF หลายชั้น สู่การใช้ IFS และ SWITCH
• การเลือกใช้ระหว่าง IF, IFS และ SWITCH ให้เหมาะกับโจทย์ และการใช้ AND, OR, NOT ร่วมกับเงื่อนไขที่ซับซ้อน
• Workshop: คำนวณเกรดผลการประเมินพนักงาน และจัดกลุ่มลูกค้าตามยอดซื้อด้วย IFS และ SWITCH

Section 5: สูตรคำนวณเพื่อการสรุปผลข้อมูลแบบมีเงื่อนไข

• SUMIFS, COUNTIFS และ AVERAGEIFS สำหรับรวมยอด นับจำนวน และหาค่าเฉลี่ยตามหลายเงื่อนไข
• การใช้ Wildcard ตัวดำเนินการเปรียบเทียบ และการกำหนดเงื่อนไขช่วงวันที่ เช่น ยอดขายภายในเดือนหรือไตรมาส
• Workshop: สร้างตารางสรุปยอดขายรายพนักงานแยกตามเดือนและกลุ่มสินค้าด้วย SUMIFS และ COUNTIFS

Section 6: สูตรการคำนวณเพื่อการสืบค้นข้อมูล (Lookup & Reference)

• OFFSET สำหรับการอ้างอิงแบบเลื่อนตำแหน่ง และ INDIRECT สำหรับการอ้างอิงจากข้อความ
• XLOOKUP และความสามารถที่เหนือกว่ารุ่นก่อน เปรียบเทียบกับ VLOOKUP และ HLOOKUP พร้อมการค้นหาแบบ Approximate Match และ Search Mode
• Workshop: การค้นหาข้อมูลชื่อลูกค้า ราคาสินค้า และชื่อพนักงาน จากตารางอ้างอิงหลายชุด

Section 7: สูตรใหม่ใน Microsoft 365 สำหรับทำงานกับข้อมูล

• แนวคิด Dynamic Array และ Spill Range พร้อม FILTER, SORT, SORTBY และ UNIQUE
• TOCOL, TOROW, VSTACK, HSTACK, TAKE, DROP และการสรุปผลด้วย GROUPBY และ PIVOTBY เพียงบรรทัดเดียว
• Workshop: การดึงข้อมูล Top 10 และ Bottom 10 จากข้อมูลยอดขายตามเงื่อนไขที่ต้องการ

Section 8: สูตรทำงานกับข้อความ รูปภาพ และการสร้างสูตรขึ้นเอง

• TEXTBEFORE, TEXTAFTER, TEXTJOIN และ TEXTSPLIT สำหรับตัด รวม และแยกข้อความ และ IMAGE สำหรับแสดงรูปภาพในเซลล์
• LAMBDA สำหรับสร้างฟังก์ชันขึ้นใช้เอง การตั้งชื่อผ่าน Name Manager และแนวทางนำไปใช้ซ้ำในองค์กร
• Workshop: แยกชื่อและนามสกุล สร้าง QR Code ด้วย IMAGE และสร้างสูตร TOP3 ด้วย LAMBDA

Day 2: การจัดการข้อมูล Power Query PivotTable และการรักษาความปลอดภัย

Section 9: การจัดการกับ ERROR ในสูตรคำนวณ

• ประเภทของ Error (#N/A, #REF!, #VALUE!, #DIV/0!, #NAME?) และฟังก์ชัน ISNA กับ IFERROR
• ข้อควรระวังในการใช้ IFERROR ที่อาจซ่อนปัญหาที่แท้จริง และเครื่องมือ Formula Auditing (Trace Precedents/Dependents)
• Workshop: ตรวจสอบและแก้ไขไฟล์รายงานตัวอย่างที่มี Error หลายจุด พร้อมระบุต้นตอ

Section 10: การจัดรูปแบบแบบมีเงื่อนไข (Conditional Formatting)

• หลักการทำงานและลำดับความสำคัญของกฎ การใช้ Data Bar, Color Scale และ Icon Set
• การเขียนกฎด้วยสูตร และการจัดการกฎด้วย Manage Rules พร้อมแก้ปัญหากฎซ้อนทับ
• Workshop: Data Bar แสดงกำไรขาดทุน ไอคอนสำหรับสินค้าสต็อกต่ำกว่า Reorder Level และไฮไลต์รายการส่งล่าช้า

Section 11: การป้องกันความผิดพลาดด้วย Data Validation

• การป้องกันการป้อนข้อมูลไม่ถูกต้อง การกำหนดข้อความแจ้งเตือน และเทคนิค Custom Validation ด้วยสูตร
• การสร้าง Dropdown List จากรายการและช่วงข้อมูล และการตรวจสอบด้วย Circle Invalid Data
• Workshop: ป้องกันการกรอกข้อมูลซ้ำ ป้องกันการกรอกผิด และทำ Dynamic Dropdown List เช่น เลือกยี่ห้อแล้วรายการรุ่นเปลี่ยนตาม

Section 12: การทำงานกับข้อมูลด้วย Excel และการใช้ Table

• ภาพรวมเครื่องมือจัดการข้อมูล (Table, Power Query, PivotTable, PivotChart) และการเลือกใช้ให้เหมาะกับงาน
• การสร้าง Table การคำนวณด้วย Structured Reference การลบข้อมูลซ้ำ (Remove Duplicates) และการทำ Slicer
• Workshop: แปลงข้อมูลสินค้าและลูกค้าเป็น Table เพื่อนำไปวิเคราะห์และสรุปผลต่อ

Section 13: Power Query เพื่อการเตรียมข้อมูล (Data Preparation)

• Power Query คืออะไร แนวคิด ETL เบื้องต้น หน้าจอ Power Query Editor และ Applied Steps
• การนำเข้าข้อมูลจากไฟล์ Excel หลายไฟล์ CSV และ Folder การทำความสะอาดข้อมูล การ Unpivot การรวมด้วย Append/Merge และการ Refresh
• Workshop: นำเข้าไฟล์ Excel ที่มีโครงสร้างเดียวกันจากทั้งโฟลเดอร์ แล้วรวมกันอัตโนมัติพร้อม Refresh ได้ในคลิกเดียว

Section 14: การวิเคราะห์และสรุปผลด้วย PivotTable และ PivotChart

• การเตรียมข้อมูลและการสร้าง PivotTable การจัดวางฟิลด์ การเปลี่ยน Summarize Values By และ Show Values As (สัดส่วนร้อยละ การเติบโต)
• การจัดกลุ่มข้อมูลตามวันที่ เดือน ไตรมาส และช่วงตัวเลข การสร้าง Calculated Field และการทำ PivotChart กับ Slicer และ Timeline
• Workshop: สร้างรายงานสรุปยอดขายแบบ Interactive จากข้อมูลที่เตรียมด้วย Power Query พร้อม Slicer และ Timeline

Section 15: การนำเสนอและปรับแต่งกราฟ (Chart Customization)

• การเลือกประเภทกราฟให้เหมาะกับสารที่ต้องการสื่อ และการปรับแต่ง Axis, Label, Legend, Gridline
• การจัดรูปแบบตัวเลขด้วย Format Code (แสดงหลักล้านเป็น M หลักพันเป็น K) การสร้าง Combo Chart และ Sparkline
• Workshop: ปรับแต่งกราฟยอดขายรายเดือนให้พร้อมนำเสนอผู้บริหาร พร้อมใส่ Sparkline ในตารางสรุป

Section 16: การกำหนดค่าการรักษาความปลอดภัย (Security)

• การกำหนดรหัสผ่านให้ไฟล์ (Password to Open และ Password to Modify) และการป้องกัน Worksheet และ Workbook
• การป้องกันไม่ให้เห็นสูตรและไม่ให้แก้ไขเซลล์ที่ต้องการ (Lock and Hidden) และการอนุญาตให้แก้ไขเฉพาะบางช่วง (Allow Edit Ranges)
• Workshop: จัดทำไฟล์ฟอร์มกรอกข้อมูลที่ผู้ใช้กรอกได้เฉพาะช่องที่กำหนด มองไม่เห็นสูตร และมีรหัสผ่านป้องกันการแก้ไขโครงสร้าง

Section 17: Workshop ปิดท้าย (Capstone Workshop)

• โจทย์: จัดทำรายงานสรุปยอดขายประจำเดือนแบบครบวงจรจากไฟล์ข้อมูลดิบหลายไฟล์ เตรียมและรวมข้อมูลด้วย Power Query ให้ Refresh อัตโนมัติ
• สร้างชั้นการคำนวณด้วยสูตรที่เรียนมา (SUMIFS, XLOOKUP, FILTER, GROUPBY) สรุปผลด้วย PivotTable พร้อม Slicer ปรับแต่งกราฟ และกำหนด Conditional Formatting กับ Data Validation
• ป้องกันไฟล์และซ่อนสูตรก่อนส่งมอบ พร้อมสรุปแนวทางต่อยอดไปยัง Power Query ขั้นสูง, PivotTable ขั้นสูง, Macro/VBA และ Power BI