|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/automate-data-processing-transformations-with-excel-vba/
课程评论：没有评论
课程名称：利用Excel VBA自动化数据处理与转换 课程概述：本课程使用Microsoft 365中的Excel开发，但所学内容可应用于任何版本的Excel。课程将专注于许多人忽视的领域——直接在VBA编辑器中处理和转换数据。我们将像使用Python或R那样使用Excel VBA，而不仅仅是作为一个格式化工作表的附加功能。课程将首先回顾数组和对象的基础知识，然后快速进入从工作表和其他工作簿中获取数据。在商业环境中，您经常会接收到Excel工作簿中的大量数据，因此能够以编程方式获取这些数据至关重要。 在数据转换部分，我们将构建14个函数，以帮助您处理和转换数据，这些函数将全部从头开始编写。这一部分旨在提供可以立即在您日常工作中应用的函数，更重要的是，它将帮助您开始思考如何构建其他函数以丰富您的工具箱。接下来，我们将通过学习使用标题名称而非索引来改进这些函数。 最后，我们将进行ERP项目，这里您将运用所学知识来解决商业中常见的真实场景，即数据迁移到新的ERP系统。 您将学习到： - 如何在编辑器中开发函数以直接处理数据。 - 如何以编程方式获取工作表和工作簿中的数据。 - 如何导出数据以便在开发数据处理工作流程时进行审阅。 我们将编写的函数包括： - 获取数据 - 导出绝对值 - 列求和 - 添加列 - 数学比较 - 复制列 - 填充列（条件填充） - 交换列 - 数据堆叠转置 本课程为中级Excel VBA课程，学生应对以下概念感到熟悉： - 模块 - 变量（包括常量和枚举） - 函数和子程序 - If语句 - For和For Each循环 - Select Case **本课程非常适合与许多优秀的初学者课程搭配，能够帮助您快速上手。还提供了一本带注释的VBA快速入门代码书，以便回顾。** **如果您有编程经验，可以在课程中逐步掌握这些概念。** 适合对象：商业分析师、财务分析师、数据分析师，以及希望提升VBA技能的任何人。
This course was developed with Excel in Microsoft 365, but what you learn here can be applied to any version of excel.This course will cover an area that most leave out, working directly in the VBA editor to process and transform your data. We are going to work with Excel VBA like any other programming language such as Python or R, and not just like an add on to excel that just formatts your worksheets.We will first start with a refresher on arrays and objects. Then we just jump into getting data from worksheets and other workbooks. In any business setting a lot of data you will receive will be in an excel workbook, so it is important that you can gather this data programmatically.In our transformation section we will be building 14 functions to help process and transform your data. This will all be written from scratch. This section is meant to give you functions that you can immediately apply in your day job, but more importantly it will help you start thinking about how you can start building other functions to add to your tool box. We will then improve those functions by learning to work with header names instead of working with indexes.Finally, we have your ERP project. This is where you will use what you learned to help solve a real life scenario that comes up a lot in business, data migration to a new ERP system.You will learn:How to develop function to work with data directly in the editor.How to programmatically get data from worksheets and workbooks.Export your data for you to review as you develop your data processing workflow.Functions we will code:Get DataExport Absolute ValueSum ColumnsAdd ColumnsMathCompareCopy ColumnFill Column IfFill ColumnFill Column IfSwap ColumnsStack Data TransposeThis is an intermediate Excel VBA course and students should be comfortable with the following concenpts:ModulesVariables (including constants & enums)Functions and SubroutinesIf StatementsFor & For Each LoopsSelect Case** There are a lot of great beginners courses available that will get you up to speed realatively quickly and this would be a great companion course to any of them** ** A VBA Quickstart code book with commentary is provided, but this is meant to be a refresher**** If you have programming experience then you can probably pickup these concepts as you go through the course **Who is this course for:Business AnalystFinancial AnalystData AnalystAnyone wanting to advance their VBA skills
