|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/effective-use-of-templates-in-excel/
课程评论：没有评论
课程名称：在Excel中有效利用模板 课程概述：有效利用模板可以高效且合理地构建Excel表格，特别适用于使用数学公式的Excel开发者。如果您经常重复使用相同的表格，并手动重新格式化或重新应用公式，那么本课程将帮助您提高效率。这里提到的Excel表格指的是格式化在一个组内的数据，而不是结构化的Excel表（之前称为列表）。 课程背景：本课程内容源自一门较旧的Excel格式化课程。根据反馈意见，决定将模板这一主题扩展成独立课程。课程中使用的一些Excel文件也来自于旧课程。 课程内容： 第一部分：介绍 我们介绍模板的概念并概述课程内容。 讲座1：模板概念 本讲解说明了模板的概念。您将学习： - 使用模板的重要性 - 模板的适当布局 - 原始模板与克隆模板的区别 - 设置易于遵循的模板的注意事项 第二部分：使用克隆模板进行简单数据集 您将创建一个原始模板和一个克隆模板，以便复制生成衍生表格。 讲座2：创建原始模板 设置表格以形成克隆，从而有效地重新设计表格。 讲座3：设置克隆模板 演示如何在Excel工作簿中设置克隆模板。 讲座4：克隆模板的使用 演示如何使用克隆模板以更高效的方式创建相同风格的其他表格。 第三部分：创建可全局应用的公式 学习如何创建无需修改的可复制公式，以减少错误并提高效率。 讲座5：状态的模板公式 使用嵌套IF语句应用于文本。 讲座6：可用百分比的模板 讨论原开发者的目标并演示轻微的重新设计。 讲座7：损失百分比的模板 演示如何简化复杂的公式，以提高模板的效率和清晰度。 第四部分：使用克隆模板处理更复杂的数据集 学习如何创建链接到外部数据的模板，以便轻松修改衍生表格。 讲座8：模板在工程系统中的应用 探讨在矿物加工中使用的一个案例。 第五部分：结语 课程重点在于创建可复制的模板。有效使用模板可以显著提高工作簿开发的效率。 讲座9：模板总结 回顾使用模板的好处，并提供有效使用模板的步骤总结。 讲座10：额外内容 通过学习本课程，您将掌握在Excel中有效利用模板的技能，提升工作效率。
PurposeEffective use of templates allows Excel tables to be constructed efficiently, and logically. It is particularly useful for the Excel developers who use mathematical formulae.In short if you are repeatedly using the same tables, and then reformatting the tables or reapplying the formulae manually then this course is essential to help you be more efficient.Note that here when I refer to Excel tables I simply mean data that is formatted within a group. I do not mean a structured Excel Table (previously called a list).BackgroundThis course includes content from an older course on formatting in Excel. Based on feedback it was decided to expand the subject of Templates to become a separate course.Some of the Excel files used in this course are also provided in the older course and therefore the file names also refer to the previous file names as follows:Template1 (Unformatted7)Template2 (Unformatted8)Template3(Unformattted9)Section 1 IntroductionWe introduce the concepts for templates and outline what will be covered in this course.Lecture 1 Template ConceptsThis lecture explains template concepts in preparation for following lectures.You will learn:• The importance of using templates• Appropriate layouts for templates• The difference between an original template and a clone template• Considerations for setting up of easy to follow templatesThe corresponding sections are:2. Using a clone template for a simple set of data.3. Creating formulae that can be globally applied4. Using a clone template for more complex data5. CloseThe user is not required to know VBA , named ranges or arrays. These will be contained in extension courses.Section 2 Using a clone template for a simple set of dataThere is an original template, a clone template and progeny tables. You will create an original template and a clone template which can be copied to create progeny tables.Lecture 2 Creating an original TemplateHere a table is set up (using Template1Unformatted7 Excel workbook) that is to be copied to form clones. Here we set up the original template. This allows for efficient redesigning of tables.You will therefore learn skills in setting up an original template.Lecture 3 Setting up Clone TemplateThis lecture demonstrates setting up a clone template in Template 2(Unformatted8) Excel workbook. Here a clone is similar to the original template but has some modifications.You will therefore learn skills in setting up a clone template.Lecture 4 Clone Template UseThis lecture demonstrates the use of the clone template to create other tables of the same style in the Template2(Unformatted8) Excel workbook. By doing this Excel is used more efficiently.You will learn skills in:• Copying a clone template• Adjusting a clone template• Adding data to a clone templateSection 3 Creating formulae that can be globally appliedOften people create formulas that can be applied locally and when copied the formulas have to be modified. This type of strategy is manual and prone to error. You will learn to create formulae so that when they are copied they do not need to be modified. You will find applying this approach is more automated and less prone to error. You will develop a deeper understanding of how to use relative referencing.Lecture 5 Templates Formula for StatusThis lecture demonstrates the use of Templates Formula for status in the Template3(Unformatted9) Excel workbook. You will learn to use nested IF statements applied to text.Lecture 6 Templates Percent AvailableIn this lecture we consider what the original developer is trying to achieve. Here a minor redesign is demonstrated in Unformatted9.xlsx workbook by adding a calculation.Lecture 7 Templates Percent LostThis lecture demonstrates appropriate redesigning in the Template3(Unformatted9) Excel workbook by making equations much simpler. An equation with some 20 terms is converted to one with about 5. A key part of formatting Excel is to use equations in such a manner that they are as simple as possible. This allows templates to be both efficient and clear.Section 4 Using a clone template for a more complex datasetThus far templates have been based on self-contained data. Here we talk about templates which are linked to external data.You will learn the concepts of how to create a template so that the progeny tables can be easily modified with respect to the external data sources.Lecture 8 Application of Templates to an engineering systemThe use of templates where there are linkages is the basis of flowsheet modelling, or visual modelling.Here we explore an example which is used in mineral processing using Complex Template Excel file and Complex Simulation Excel file.You will learn how to modify progeny tables so as to be able to link to external data sources.Section 5 CloseIn this course we have focused on creating copyable templates. The efficient use of templates allows development of workbooks to be completed much more efficiently than with manual methods.Lecture 9 Template CloseThis lecture reviews the benefits of using templates. A summary of the steps in using templates effectively in Excel is provided.Lecture 10 Bonus
