|
所在平台: Coursera |
课程主页: https://www.coursera.org/learn/excel-vba-for-creative-problem-solving-part-2
课程评论：没有评论
课程名称：Excel/VBA 创意思维解决问题第二部分 概述： “Excel/VBA 创意思维解决问题第二部分”建立在第一部分所获得的知识和技能基础上，旨在帮助学习者增强、扩展、优化和提高Excel电子表格的效率，通过利用VBA（Visual Basic for Applications）的强大编程、自动化和自定义功能。本课程的第二部分，学习者将学习：1）如何使用VBA代码处理数组，并从Excel导入/导出数组；2）如何处理文本字符串以及将数据写入.txt文件和从.txt文件导入信息；3）自动化从多个工作表到中心工作表的信息导入、修改和合并，以及从多个工作簿到中心工作簿的信息导入；4）创建专业的用户表单以与用户交互，执行高级计算并操作电子表格数据的经验。 具有VBA代码和编程结构基础的学习者可以直接进入课程第二部分，并使用第一部分的录像作为参考。每个模块将介绍与现实生活中可能遇到的情况相关的基础和广泛问题。要通过每个模块，学习者需要完成一个掌握测验和一个问题解决作业。本课程的独特之处在于每周的作业是在应用程序中完成的（即在自己的电脑上使用Excel），为学习者提供宝贵的实践训练。 课程大纲： 1. 数组和数组函数 - 在第一周，学习数组和数组函数，掌握如何使用嵌套的For...Next循环和For Each...Next语句迭代数组；学习如何从Excel导入数据到VBA以及从VBA导出数据到Excel；创建用户定义的数组函数；了解可调整大小数组的ReDim Preserve语句。第一周以测验结束，解锁作业1。 2. 字符串和.txt文件的处理 - 第二周将重点讨论文本字符串和文本（.txt）文件，学习Excel和VBA中的字符串函数；通过创建利用字符串函数的VBA函数加深实践；提取邮件地址等字符串，并将信息从Excel写入.txt文件及从.txt文件导入Excel。该模块以测验和作业2结束。 3. 遍历工作表和工作簿 - 第三周专注于遍历（自动化）工作表和工作簿，学习如何将多个工作表中的信息合并到单个工作表中，并自动合并多个工作簿中的信息到单个工作簿的单个工作表中。该模块的自动化原则非常宝贵，以测验和一个极具挑战性的作业结束。 4. 用户表单和高级用户输入/输出 - 在第四周，将学习高级输入和消息框及事件处理程序，与用户进行专业交互，创建验证输入操作的用户表单，防止“调试”窗口和VBE的出现；向用户表单上的组合框填充电子表格信息。最后，学习如何将这一切组合起来并实施解决技术（如二分法）来解决现实问题。该周以测验结束，解锁作业4。
Name:Arrays and Array Functions
Description:In Week 1, you will learn all about arrays and array functions. You will learn how to iterate through arrays using nested For...Next loops and For Each...Next statements; you will learn how to import data to VBA from Excel and export data to Excel from VBA; you will learn how to create user-defined array functions; and you will learn about the ReDim Preserve statement for resizable arrays. Week 1 is concluded with a quiz, which unlocks Assignment 1.
Name:Working with strings and .txt files
Description:Week 2 deals with text strings and text (.txt) files. You will learn about the string functions in Excel and VBA. You will gain practice with string functions as you learn how to create functions in VBA that utilize string functions. You will learn how to extract strings, such as email addresses, from a spreadsheet of mixed string formats. You will also learn how to write information from Excel to .txt files and also to import information in .txt files to Excel. The module is concluded with a quiz and Assignment 2.
Name:Iterating through worksheets and workbooks
Description:Week 3 is all about iterating through (automating) worksheets and workbooks. You will learn how to consolidate information from multiple worksheets into a single worksheet and you will learn how to automatically consolidate information found within multiple workbooks into a single worksheet of a single workbook. The automation principles you learn in this module are priceless. Week 3 is concluded with a quiz and a very challenging assignment.
Name:User forms and advanced user input/output
Description:In Week 4, you will learn about advanced input and message boxes and event handlers. You will learn how to interface with the user in a professional manner using user forms that validate input and prevent the "debug" window and VBE from appearing. You will also learn how to place combo boxes on user forms and populate those combo boxes with information on the spreadsheet. Finally, you will learn how to put it all together and implement a solving technique (bisection method) to solve a real world problem in a user form. Week 4 is concluded with a quiz, which unlocks Assignment 4.
"Excel/VBA for Creative Problem Solving, Part 2" builds off of knowledge and skills obtained in "Excel/VBA for Creative Problem Solving, Part 1" and is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). In Part 2 of the course, learners will: 1) learn how to work with arrays and import/export arrays from/to Excel using VBA code; 2) learn how to work with text strings and write data to .txt files and import information from .txt files; 3) automate the import, modification, and consolidation of information from multiple worksheets into a central worksheet as well as the import of information from multiple workbooks to a central workbook; and 4) gain experience with creating professional user forms to interface with the user, perform advanced calculations, and manipulate data on the spreadsheet. Learners who have a foundational understanding of VBA code and programming structures can jump right into Part 2 of the course without taking Part 1 and use the screencasts in Part 1 as reference. Each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.
