Microsoft Excel ລະດັບສູງ

ເຊື່ອມຕໍ່ຫຼາຍແຫຼ່ງຂໍ້ມູນ ແລ້ວລວມ ລ້າງ ແລະ ແປງໃຫ້ພ້ອມໂຫຼດເຂົ້າ data model — ໂດຍມີ Copilot ຮ່າງ M code ໃຫ້ ສ່ວນເຈົ້າເປັນຄົນວາງໃສ່ ແລະ ກວດຄວາມຖືກຕ້ອງ

• ພາບລວມໂຄງສ້າງລະບົບ
  • –Power Query → Data Model → PivotTable → Dashboard
  • –ຊັ້ນ Staging, Transform ແລະ Load
  • –ມາດຕະຖານການຕັ້ງຊື່ query ແລະ ຊື່ຂັ້ນຕອນ ພ້ອມແນວທາງການຂຽນເອກະສານປະກອບ
• ເຊື່ອມຕໍ່ ແລະ ລວມຂໍ້ມູນ
  • –From Folder — ລວມໄຟລ໌ລາຍເດືອນໃຫ້ອັດຕະໂນມັດ
  • –ດຶງຈາກ Excel, CSV, Text, Web ແລະ SharePoint
  • –Combine Files ແລະ helper function ທີ່ມັນສ້າງໃຫ້ — helper ຮັບເນື້ອຫາແບບ binary ຂອງແຕ່ລະໄຟລ໌ ສ່ວນຊື່ໄຟລ໌ຈະມາເປັນຖັນ Source.Name ດັ່ງນັ້ນໃຫ້ເກັບຖັນນີ້ໄວ້ ທຸກແຖວຈຶ່ງຮູ້ວ່າຕົນເອງມາຈາກໄຟລ໌ໃດ
  • –Data source settings, Credentials (ບັນຊີ ແລະ ລະຫັດຜ່ານທີ່ໃຊ້ເຂົ້າເຖິງແຫຼ່ງຂໍ້ມູນ) ແລະ Privacy Levels — ສາທິດໃຫ້ເບິ່ງ ເພື່ອໃຫ້ຮູ້ວ່າຕ້ອງເຮັດແນວໃດ ໃນມື້ທີ່ການ Refresh ເລີ່ມຖາມຫາສິດເຂົ້າເຖິງ
  • –Parameters ແລະ custom function ທີ່ໃຊ້ຄືນໄດ້ 1 ອັນ ເອີ້ນໃຊ້ດ້ວຍ Invoke Custom Function — ສ້າງນຳກັນໃນບົດຝຶກ ເພື່ອໃຫ້ query ດຽວຮັບໃຊ້ໄດ້ຫຼາຍໄຟລ໌
• ລ້າງ ແລະ ແປງຂໍ້ມູນ
  • –ປ່ຽນຊະນິດຂໍ້ມູນ; Trim, Clean, Split, Extract, Format ແລະ Merge ຖັນ
  • –ລຶບແຖວ, ຖັນ, ຂໍ້ມູນຊ້ຳ ແລະ ແຖວທີ່ເປັນ error
  • –Group By (ລວມທັງ All Rows), Fill Down/Up, Replace Values ແລະ Index Column
  • –Pivot ແລະ Unpivot ເພື່ອຈັດຮູບຮ່າງໃຫ້ເປັນຕາຕະລາງ Fact; ສ່ວນ Transpose ສາທິດໃຫ້ເບິ່ງ
  • –Conditional Column ແລະ Custom Column — ກົດເກນທຸລະກິດແບບ if/then/else
  • –Column from Examples — ດຶງຂໍ້ມູນຕາມຮູບແບບທີ່ຍົກຕົວຢ່າງໃຫ້ ໂດຍບໍ່ຕ້ອງຂຽນສູດ
  • –ການຈັດການ error: Replace/Remove Errors, try ... otherwise ແລະ ການເກັບແຖວທີ່ຜິດໄວ້ກວດຍ້ອນຫຼັງ
  • –ການແປງວັນທີ: Date.StartOfMonth/EndOfMonth, Date.AddMonths, key ປີ-ເດືອນແບບ ISO ແລະ ການປ່ຽນຊະນິດຕາມ locale ດ້ວຍ Change Type > Using Locale. ສ່ວນຟັງຊັນ Duration ແລະ Time ຢູ່ໃນເອກະສານສະຫຼຸບ
• Merge ແລະ Append
  • –Append Queries — ຕໍ່ຕາຕະລາງລາຍເດືອນເຂົ້າກັນເປັນອັນດຽວ
  • –Merge Queries: join ແບບ Left, Right, Inner, Full ແລະ Anti
  • –Fuzzy Matching ສຳລັບ key ທີ່ບໍ່ຕົງກັນທຸກຕົວອັກສອນ
  • –ໃຊ້ Anti join ເປັນລາຍງານຂໍ້ຍົກເວັ້ນ — ຫາລາຍການທີ່ຈັບຄູ່ບໍ່ໄດ້ ຫຼື ບໍ່ມີຂໍ້ມູນຫຼັກຮອງຮັບ
  • –ເລືອກ join ໃຫ້ຖືກແບບ: ນັບຈຳນວນແຖວກ່ອນ ແລະ ຫຼັງ ເພື່ອຢືນຢັນຄວາມຖືກຕ້ອງ
• ປະສິດທິພາບ ແລະ ມາດຕະຖານ
  • –ຕັດຂັ້ນຕອນທີ່ຊ້ຳຊ້ອນອອກ ແລະ ໃຊ້ Table.Buffer ບັງຄັບໃຫ້ຕາຕະລາງຢູ່ໃນໜ່ວຍຄວາມຈຳ — ແຕ່ຕ້ອງຮູ້ວ່າມັນຕັດ Query Folding ຖິ້ມ ຈຶ່ງໃຊ້ໄດ້ສະເພາະເທົ່າທີ່ຈຳເປັນ
  • –Query Folding — ມັນແມ່ນຫຍັງ ແລະ ເປັນຫຍັງຈຶ່ງເກີດສະເພາະເມື່ອແຫຼ່ງຂໍ້ມູນຮັບ query ໄດ້ (SQL Server, ODBC, OData, SharePoint list). ກົດຂວາທີ່ຂັ້ນຕອນ > View Native Query ເພື່ອເບິ່ງ SQL. ແຫຼ່ງແບບ Folder, Excel ແລະ CSV ບໍ່ຮອງຮັບ folding ຫົວຂໍ້ນີ້ຈຶ່ງເປັນການສາທິດ ບໍ່ແມ່ນບົດຝຶກເທິງໄຟລ໌ຂອງ workshop
  • –Load To: ໂຫຼດເປັນ Table, Only Create Connection ຫຼື Only Create Connection ພ້ອມຕິກ Add this data to the Data Model — ເຮົາຕັ້ງໃຈໃຫ້ມີ query ໜຶ່ງອັນໂຫຼດລົງເປັນ Table ໄວ້ ເພື່ອໃຫ້ບົດຝຶກ Copilot ມີຂໍ້ມູນເທິງ worksheet ໃຫ້ເຮັດວຽກນຳ
  • –ໂຄງສ້າງໂຟນເດີ, ການຈັດການເວີຊັນໄຟລ໌ ແລະ ຄູ່ມືການ Refresh
  • –Reference ກັບ Duplicate ຕ່າງກັນແນວໃດ — query ຊັ້ນ staging ທີ່ປ້ອນຜົນໃຫ້ຫຼາຍປາຍທາງ
  • –Query Dependencies view ສາທິດໃຫ້ເບິ່ງ; ສ່ວນ Advanced Editor — ອ່ານ ແລະ ແກ້ M code ແມ່ນລົງມືເຮັດເອງ
• Copilot ແລະ ການຕຽມຂໍ້ມູນດ້ວຍ AI
  • –Copilot ໃນ Excel ເຮັດວຽກເທິງ worksheet ເທົ່ານັ້ນ ບໍ່ໄດ້ເຂົ້າໄປໃນ Power Query Editor. ໃຫ້ໂຫຼດ query ຊັ້ນ staging ໜຶ່ງອັນລົງ worksheet ເປັນ Table ກ່ອນ Copilot ຈຶ່ງມີຂໍ້ມູນໃຫ້ອ່ານ ແລ້ວໃຊ້ມັນເຮັດໃນສິ່ງທີ່ມັນເກັ່ງ — ຂຽນສູດຈາກຄຳສັ່ງພາສາທຳມະດາ, ຈັດລຽງ, ກັ່ນຕອງ, ເນັ້ນສີ, ແນະນຳ PivotTable ຫຼື ກຣາຟ ແລະ ເບິ່ງພາບລວມຂໍ້ມູນກ່ອນລົງມືສ້າງ model
  • –ຮູບແບບການຂຽນ prompt: Context → Task → Format. ບອກແຫຼ່ງຂໍ້ມູນ, ຮູບຮ່າງທີ່ຢາກໄດ້ ແລະ ກົດເກນ ແລ້ວປັບຊ້ຳໄປເລື້ອຍໆ ດ້ວຍຕົວຢ່າງແບບ few-shot ແລະ role prompt
  • –Copilot ຮ່າງ M code ໃຫ້: ອະທິບາຍການແປງຂໍ້ມູນເປັນພາສາທຳມະດາໃນ chat ຮັບ M ກັບມາເປັນຂໍ້ຄວາມ ແລ້ວວາງໃສ່ Advanced Editor ແລະ ປັບແຕ່ງຢູ່ບ່ອນນັ້ນ. Copilot Chat ມາພ້ອມບັນຊີ Microsoft 365 ແບບອົງກອນ ຫຼື ສະຖາບັນການສຶກສາ ທຸກຄົນໃນຫ້ອງຈຶ່ງເຮັດສ່ວນນີ້ໄດ້
  • –ກວດຜົນທີ່ AI ອອກໃຫ້ — ເບິ່ງແຖວຕົວຢ່າງ, ນັບຈຳນວນແຖວ, ຢ່າເຊື່ອໂດຍບໍ່ກວດ

ສ້າງ data model ແບບ Star Schema ແລະ ຂຽນ DAX measures ສຳລັບ KPI ແລະ Time Intelligence — ໃຫ້ Copilot ຮ່າງໃນ chat ແລ້ວເຈົ້າວາງເຂົ້າ Power Pivot ແລະ ຢືນຢັນຄວາມຖືກຕ້ອງເອງ

• ການສ້າງ data model (Star Schema)
  • –Fact ກັບ Dimension; Primary Key ແລະ Foreign Key; cardinality — ລະດັບຄວາມລະອຽດຂອງຂໍ້ມູນ (grain) ແລະ ຄວາມບໍ່ຊ້ຳຂອງ key ທີ່ເຮັດໃຫ້ relationship ໃຊ້ໄດ້ຈິງ
  • –relationship ແບບ Active ກັບ Inactive ແລະ USERELATIONSHIP
  • –Date Dimension ແລະ ຄຳສັ່ງ Mark as Date Table
  • –Diagram View; Manage Relationships; relationship ແບບ one-to-many ໃນ Excel ແລະ ຮູບແບບ bridge table ສຳລັບ many-to-many — Excel ບໍ່ມີ cardinality ແບບ many-to-many ແລະ ບໍ່ມີການກັ່ນຕອງສອງທາງ ຮູບແບບ bridge ຈຶ່ງເປັນການສາທິດ ບໍ່ແມ່ນສ້າງໃໝ່ຕັ້ງແຕ່ຕົ້ນ
  • –Hierarchy (ປີ → ເດືອນ, ໝວດ → ສິນຄ້າ); Hide from Client Tools ສຳລັບຖັນ key; ການຈັດລະບຽບ measure ແລະ ພື້ນຖານເລື່ອງຂະໜາດ model — cardinality ເປັນຕົວກຳນົດຂະໜາດໄຟລ໌
• ພື້ນຖານ DAX
  • –Calculated Column ກັບ Measure ຕ່າງກັນແນວໃດ
  • –Row Context ກັບ Filter Context — ພື້ນຖານຂອງ DAX ທັງໝົດ
  • –SUM, COUNTROWS, DISTINCTCOUNT ແລະ DIVIDE
  • –RELATED ແລະ RELATEDTABLE
  • –ກຸ່ມ iterator: SUMX, AVERAGEX, COUNTX — ຄິດເທື່ອລະແຖວເທິງຕາຕະລາງ
  • –ຕົວແປ (VAR / RETURN) ເພື່ອໃຫ້ measure ອ່ານງ່າຍ ແລະ ຫາຈຸດຜິດໄດ້
  • –Implicit ກັບ explicit measure — ເປັນຫຍັງ explicit ຈຶ່ງດີກວ່າສະເໝີ
• CALCULATE ແລະ ການກັ່ນຕອງ
  • –CALCULATE ຮ່ວມກັບ ALL, ALLEXCEPT ແລະ KEEPFILTERS — ກຸ່ມຕົວປັບການກັ່ນຕອງທີ່ໃຫ້ຜົນຄືກັນທຸກເວີຊັນຂອງ Excel. ສ່ວນ REMOVEFILTERS ເປັນຊື່ໃໝ່ຂອງ ALL — ເຮົາຈະກວດໃຫ້ກ່ອນເປີດຫຼັກສູດວ່າ Excel ເວີຊັນຂອງເຈົ້າມີຟັງຊັນນີ້ບໍ່
  • –measure ຫຼັກ: Total Sales, Cost, Gross Profit ແລະ %Margin — ລົງມືເຮັດເອງ
  • –Sales LY (DATEADD) ແລະ YoY% — ລົງມືເຮັດເອງ
  • –FILTER ໃນຖານະ table argument ແລະ ALLSELECTED ສຳລັບຍອດລວມຕາມສິ່ງທີ່ຜູ້ໃຊ້ເລືອກ — ສາທິດເປັນຕົວຢ່າງໃຫ້ເບິ່ງ ແລະ ເຈົ້າເປີດຈາກໄຟລ໌ແບບຝຶກຫັດມາທວນຄືນໄດ້
  • –Context Transition — CALCULATE ເຮັດຫຍັງແທ້ — ສາທິດໃຫ້ເບິ່ງ ແລ້ວຝຶກກັບ measure ໜຶ່ງໂຕ
  • –% of Total, % of Parent ແລະ measure branching (ເອົາ measure ໄປໃຊ້ຊ້ອນໃນ measure ອື່ນ)
• Time Intelligence
  • –PREVIOUSDAY/MONTH/YEAR, DATEADD, DATESYTD ແລະ TOTALYTD
  • –ຍອດສະສົມແບບ Running Total; Moving Average 3, 6 ແລະ 12 ເດືອນ ດ້ວຍ DATESINPERIOD
  • –SAMEPERIODLASTYEAR ແລະ PARALLELPERIOD; ວາງ MTD, QTD ແລະ YTD ຄຽງກັນ — ຂຽນດ້ວຍ TOTALMTD, TOTALQTD ແລະ TOTALYTD ຫຼື ໃຊ້ CALCULATE ຫຸ້ມ DATESMTD, DATESQTD ແລະ DATESYTD ເພາະຟັງຊັນກຸ່ມ DATES… ຄືນຄ່າເປັນຕາຕະລາງວັນທີ ບໍ່ແມ່ນຕົວເລກ
  • –ປີງົບປະມານ: ໃຊ້ argument ວັນສິ້ນປີໃນ TOTALYTD ແລະ DATESYTD ພ້ອມເພີ່ມຖັນປີງົບປະມານໃນຕາຕະລາງວັນທີຂອງເຈົ້າເອງ — Excel ບໍ່ມີບ່ອນຕັ້ງຄ່າປີງົບປະມານລວມໃຫ້ທັງ model
• KPI ແລະ ການຈັດອັນດັບ
  • –ຕັ້ງຮູບແບບຕົວເລກໃນລະດັບ measure; KPI ຂອງ Power Pivot — measure ຫຼັກ, ເປົ້າໝາຍ ແລະ ເກນສະຖານະ. ສ່ວນທ່າອ່ຽງໃຫ້ໃຊ້ measure YoY ຫຼື MoM ຂອງເຈົ້າເອງ ສະແດງເປັນ icon set ຫຼື sparkline ເພາະກ່ອງ KPI ບໍ່ມີສ່ວນທ່າອ່ຽງໃຫ້
  • –Sort by Column, RANKX, Actual vs Target, Variance ແລະ % Achievement — ຜົນຈິງທຽບກັບເປົ້າໝາຍ, ຜົນຕ່າງ ແລະ % ການບັນລຸ
  • –ສ້າງ PivotTable ຈາກ measure: ການຈັດວາງ field, Show Values As ແລະ ການເຈາະລົງລາຍລະອຽດ
  • –TopN ດ້ວຍ RANKX; Pareto 80/20 ດ້ວຍ measure % ສະສົມ ແລະ ການຈັດກຸ່ມແບບ ABC
• Copilot ແລະ ການຂຽນ DAX ດ້ວຍ AI
  • –ຮ່າງ, ອະທິບາຍ ແລະ ຫາຈຸດຜິດຂອງ DAX measure ດ້ວຍ prompt ໃນ Copilot Chat
  • –ຂຽນ prompt ໃຫ້ມີບໍລິບົດ: ຕາຕະລາງ, relationship, grain ແລະ ຜົນທີ່ຄາດວ່າຈະໄດ້ — Copilot ເບິ່ງ model ຂອງເຈົ້າບໍ່ເຫັນ ບໍລິບົດຈຶ່ງຕ້ອງມາຈາກເຈົ້າ
  • –Copilot ຂຽນຮ່າງທຳອິດຂອງທຸກ DAX measure ຈາກນັ້ນເຈົ້າວາງເຂົ້າ Power Pivot ແລ້ວປັບແຕ່ງ ທົດສອບ ແລະ ຢືນຢັນຄວາມຖືກຕ້ອງ
  • –ກວດ measure ທຸກໂຕທີ່ AI ຂຽນ ດ້ວຍການທຽບກັບຍອດລວມທີ່ຮູ້ຄຳຕອບຢູ່ແລ້ວ

ສ້າງ Dashboard ໂຕ້ຕອບໄດ້ລະດັບໃຊ້ງານຈິງ ແລະ ເຮັດໂຄງການ Capstone ຄົບວົງຈອນໃຫ້ສຳເລັດ

• ລາຍງານດ້ວຍ PivotTable
  • –ລາຍງານແບບ Matrix: Sales ແລະ Profit ແຍກຕາມວັນທີ, ສິນຄ້າ, ລູກຄ້າ ແລະ ເຂດ — ການຈັດວາງ field, Show Values As ແລະ ການຕັ້ງຮູບແບບຕົວເລກທີ່ຕົວ measure
  • –Conditional Formatting: Data Bars, Color Scales ແລະ Icon Sets
  • –PivotChart: ກຣາຟແທ່ງ, ເສັ້ນ ແລະ Combo — ສ່ວນ Pareto ສ້າງເປັນ combo chart ບວກເສັ້ນ % ສະສົມເທິງແກນທີສອງ ເພາະຊະນິດກຣາຟ Pareto ໃນຕົວຂອງ Excel ໃຊ້ກັບ PivotChart ບໍ່ໄດ້
• ການອອກແບບ Dashboard
  • –ພື້ນຖານການຈັດວາງ: Grid, Alignment, Spacing ແລະ Theme — ວາງລະບົບການອອກແບບສຳລັບສີ ຟອນ ແລະ ເສັ້ນ ພ້ອມບລັອກ Title, Subtitle ແລະ Notes ແບບມາດຕະຖານ
  • –Slicer ແລະ Timeline: ການຈັດຮູບແບບ ແລະ ໃຫ້ Slicer ອັນດຽວຄວບຄຸມທຸກ PivotTable ແລະ PivotChart ຜ່ານ Report Connections
  • –KPI card ແລະ sparkline ທີ່ຂັບດ້ວຍ CUBEVALUE ແລະ CUBEMEMBER — PivotTable Analyze > OLAP Tools > Convert to Formulas ຈະແຍກ PivotTable ຂອງ model ອອກເປັນສູດ cube ທີ່ວາງໄວ້ບ່ອນໃດກໍໄດ້ເທິງໜ້າ ແລະ CUBEVALUE ຮັບ Slicer ເປັນ argument ໄດ້ KPI card ທຸກອັນຈຶ່ງຍັງຕອບຮັບຕົວກັ່ນຕອງຢູ່
  • –Camera Tool ສຳລັບເອົາພາບສົດຂອງບລັອກລາຍງານ — ບໍ່ມີຢູ່ເທິງ ribbon ຈຶ່ງຕ້ອງເພີ່ມຈາກ All Commands ໃສ່ Quick Access Toolbar ຫຼື ໃຊ້ Paste Special > Linked Picture ແທນ
• ການນຳໄປໃຊ້ຈິງ ແລະ ການດູແລຮັກສາ
  • –ແຜນການ Refresh: Refresh All, background refresh ແລະ ຂໍ້ຈຳກັດຂອງມັນ, ຕົວເລືອກ Refresh data when opening the file ແລະ ການປັກ Refresh All ໄວ້ເທິງ Quick Access Toolbar ໃຫ້ເປັນປຸ່ມກົດຄັ້ງດຽວ — ບໍ່ຕ້ອງໃຊ້ macro ໄຟລ໌ຈຶ່ງຍັງເປັນ .xlsx ທຳມະດາ
  • –ວິທີຮັບມືເມື່ອໂຄງສ້າງຂໍ້ມູນປ່ຽນ ມີຖັນໃໝ່ເພີ່ມເຂົ້າມາ ຫຼື ຊະນິດຂໍ້ມູນປ່ຽນ
• ໂຄງການ Capstone
  • –Build → Model → Measure → Pivot → Dashboard → Present
  • –ໃຫ້ຄະແນນຈາກຄວາມຖືກຕ້ອງ, ປະສິດທິພາບ, ການເລົ່າເລື່ອງດ້ວຍຂໍ້ມູນ ແລະ ການອອກແບບ — ນຳສະເໜີໃນຕອນບ່າຍຂອງມື້ທີ 3 ແລ້ວແຕ່ລະຄົນຈະໄດ້ຮັບຄຳຕິຊົມເປັນລາຍລັກອັກສອນຫຼັງຈົບຫຼັກສູດ
• ກ້າວຕໍ່ໄປ
  • –ລາຍການກວດກ່ອນນຳໄປໃຊ້ຈິງ ແລະ ການດູແລໄຟລ໌ໃນໄລຍະຍາວ
  • –ເສັ້ນທາງຕໍ່ໄປ: Dataflows, SharePoint ແລະ Power BI
• Copilot ສຳລັບການຫາຂໍ້ຄົ້ນພົບ ແລະ ການເລົ່າເລື່ອງດ້ວຍຂໍ້ມູນ
  • –ໃຫ້ Copilot ສະຫຼຸບພາບລວມ ທ່າອ່ຽງ ແລະ ຄ່າຜິດປົກກະຕິ — ແຕ່ຕ້ອງດຶງຜົນຈາກ model ລົງເປັນ Table ເທິງ worksheet ກ່ອນ ເພາະ Copilot ອ່ານໄດ້ແຕ່ຂໍ້ມູນເທິງ worksheet ບໍ່ແມ່ນ data model
  • –ໃຊ້ prompt ຂໍຄຳແນະນຳເລື່ອງກຣາຟ ແລະ PivotTable ພ້ອມຄຳບັນຍາຍທີ່ພ້ອມສົ່ງໃຫ້ຜູ້ບໍລິຫານ
  • –ກົດເກນຂອງນັກວິເຄາະ: ກວດໃຫ້ແນ່ກ່ອນເຜີຍແຜ່ — ພື້ນຖານເລື່ອງຄວາມເປັນສ່ວນຕົວຂອງຂໍ້ມູນ ແລະ ຈັງຫວະທີ່ບໍ່ຄວນໃຊ້ AI