有了 Power 插件，EXCEL 开启自动化时代面对海量数据，如何快速提炼、合并表单？如何整理成规范智能的工作表？如何迅速建立灵活的分析环境，找到问题和关键原因，找到破解问题的方法？如何讲操作过程自动化？如何轻松完成复杂解题过程？单凭 EXCEL 常规用法显得力不从心，高版本的 EXCEL 提供 Power 智能插件，让我们走进数据自动化办公时代，工作变得轻松又高效。Excel Query 插件，提高 10 倍效率的整理法数据整理虽然不能带来多高的价值，但会用 PQ 智能工具能节省出 80%的整理时间。如果可以将繁冗的数据整理工作搭建成一套智能的数据模型，数据的增删改都可以在其中自动化运转，这不次于亲手组装一条自动化的生产线。这就是我们一直寻找的提升 10 倍效率的工作法。Excel AI 插件，提升函数的解题能力在 365 版本中新增加的 AI 函数是个亮点，自动化支持数据增删改，减少重复工作，智能识别数据范围，加速计算运行，在课上我们可以一起感受这些函数的强大之处。
Faced with massive amounts of data, how can you quickly extract and merge sheets? How can you organize them into standardized, intelligent worksheets? How can you swiftly establish a flexible analysis environment to identify issues and key causes, and find solutions? How can you automate the operational process and easily complete complex problem-solving tasks? Conventional Excel usage falls short in these areas. However, with the Power intelligent add-ins provided by the latest versions of Excel, we enter an era of automated data management, making work easier and more efficient.Although data organization itself may not add significant value, using the Power Query (PQ) intelligent tool can save up to 80% of the time spent on data organization. By creating an intelligent data model, where data additions, deletions, and modifications can be automated, it’s akin to assembling an automated production line. This is the 10x efficiency improvement method we’ve been looking for.A highlight of the 365 version is the newly added AI functions. These functions support automated data additions, deletions, and modifications, reducing repetitive tasks, intelligently recognizing data ranges, and accelerating computational processes. During the course, we will experience the powerful features of these functions together.
【课程大纲】 Part1：巧用 PQ 实现数据加工的办公自动化 (2 小时)
多张表单的收集合并 -用 Power Query 实现多张 Sheet 表单的合并 -用 Power Query 实现多个文件的合并
多张表单的关联引用 -用 Power Query 建立合并查询 -引用列、比对记录
去粗取精整理表单的技巧 -清洗、转换、提取 -公式计算、统计工具、表单重构
Part2：增效计算，必知必会的智能函数用法(2.5 小时)
Lookup 智能查找用法 -正向查找 -逆向查找 -多条件查找
Xlookup 智能查找用法 -正向查找 -横向查找 -逆向查找 -多条件查找 -屏蔽错误值查找 -关键字模糊匹配查找 -一次性查找匹配多列数据
FILTER 筛选函数用法 -等同于高级筛选的用法（单条件、多条件、模糊筛选） -自动屏蔽筛选错误值-一次性筛选多个值
SORT BY 分组排序用法 --单列排序 -多列排序 -自定义排序
AI 函数用法 -GPT 函数 -GPT_List 函数 -GPT_Split 函数 -GPT_Match 函数 -GPT_Table 函数 -GPT_Fill 函数
Part3：高效整理和分析数据的利器（1.5 小时）
筛选的多种应用技巧 -图片的筛选 -多复杂逻辑条件的筛选 -多表比对重复值
数据透视表的实时分析技巧 -创建数据透视表 -透视表分析器面板的分析方法 -创建透视图 -分组汇总的技巧：对文本类型、日期类型、数值类型的分类汇总
-鲜为人知的排序技巧：自动排序、修改排序、拖拽排序 -多维分析技巧：表的二次计算
Main Content:
Course Outline
Part 1: Efficient Use of Power Query for Office Automation in Data Processing (2 Hours)
- Collecting and Merging Multiple Sheets
- Merging multiple sheets with Power Query
- Merging multiple files with Power Query
- Referencing and Linking Multiple Sheets
- Creating merged queries with Power Query
- Referencing columns and comparing records
- Techniques for Cleaning and Organizing Sheets
- Cleaning, transforming, and extracting data
- Using formulas, statistical tools, and sheet reconstruction
Part 2: Enhancing Calculations with Essential Smart Functions (2.5 Hours)
- Lookup Intelligent Search Usage
- Forward lookup
- Reverse lookup
- Multi-condition lookup
- Xlookup Intelligent Search Usage
- Forward lookup
- Horizontal lookup
- Reverse lookup
- Multi-condition lookup
- Error value shielding lookup
- Fuzzy keyword matching lookup
- One-time lookup matching multiple columns
- FILTER Function Usage
- Equivalent to advanced filtering (single condition, multiple conditions, fuzzy filtering)
- Automatically shielding filtering errors
- One-time filtering of multiple values
- SORT BY Function Usage
- Single column sorting
- Multiple column sorting
- Custom sorting
- AI Function Usage
- GPT function
- GPT_List function
- GPT_Split function
- GPT_Match function
- GPT_Table function
- GPT_Fill function
Part 3: Tools for Efficient Data Organization and Analysis (1.5 Hours)
- Various Application Techniques for Filtering
- Filtering images
- Filtering with complex logical conditions
- Comparing duplicate values across multiple sheets
- Real-Time Analysis Techniques with Pivot Tables
- Creating pivot tables
- Analysis methods with the pivot table analyzer panel
- Creating pivot charts
- Grouping and summarizing techniques: summarizing text, dates, and numerical types
- Little-known sorting techniques: automatic sorting, modifying sorting, drag-and-drop sorting
- Multidimensional analysis techniques: secondary calculations in tables
付款方式
报名后请将培训费汇款至以下账户，并在银行转账单上附注SY20240619 或加活动负责人微信进行扫码支付
A/C Name: 中国欧盟商会
A/C No.: 7110210182500027543
China CITIC Bank 中信银行京城大厦支行
北京市朝阳区新源南路6号京城大厦
取消注册
如果您不能参加已注册活动，请在活动开始前不少于一个工作日的时间内取消您的注册。若您未能及时地通知我们，我们将收取您相应的活动费用。
如取消注册您可以： 1) 发送邮件至 lliu@europeanchamber.com.cn； 或 2) 网站注册者可在网上取消；或3) 联系活动负责人进行取消。