|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/excelmapvlookup/
课程评论：没有评论
**Coursera 课程总结：MS Excel 2019 高级 VLOOKUP 函数与数据透视表** 本课程专为希望精通 Excel 中最常用查询函数（LOOKUPs）和数据透视表（Pivot Tables）的学习者设计，旨在帮助您以前所未有的方式处理、提取和分析信息。课程使用最新版 Excel 2019 录制，并由行业专家倾囊相授，结合大量实例和可下载的练习文件，确保您在学习过程中能够动手实践，巩固技能。 **课程亮点：** * **数据透视表精通：** 尽管只有 10% 的 Excel 用户充分利用数据透视表，但 Mastering 这项技能可以为您节省大量时间，并成为您在企业中的宝贵财富。您将快速有效地学习数据透视表和数据透视图的创建，并能熟练进行数据分析、钻取，甚至帮助他人解决数据分析问题。 * **查询函数（LOOKUPs）专家：** Excel 提供了强大的查询功能，轻松搜索行和列中的数据。本课程将带您深入了解 VLOOKUP 和 HLOOKUP 这两个简单而强大的函数，并涵盖 VLOOKUP 的各种高级应用，包括： * 左侧查询 * 替代嵌套 IF 公式 * 多表查询 * 多条件查询 * 不同工作表间的公式应用 * 自动更新列号 * 通配符的应用 * 嵌套 VLOOKUP 技巧 * VLOOKUP 的 3D 和 2D 查询 * **拓展函数掌握：** 除了 VLOOKUP，您还将学习 HLOOKUP、INDEX、MATCH、TRANSPOSE 和 INDIRECT 等函数的强大功能，并能： * 使用 INDIRECT 函数整合来自多个工作表的数据。 * 利用 OFFSET 函数创建自动扩展的引用。 * 通过下拉列表创建灵活的 Excel 仪表板查询。 * 利用“近似匹配”创建基于范围的查询。 * 结合 VLOOKUP 和 MATCH 创建二维查询。 * 使用“精确匹配”查找特定项目。 * 通过 INDEX 和 MATCH 克服 VLOOKUP 的局限性。 * 理解查询和引用函数对您工作流程的价值。 * 运用命名范围和 Excel 表格作为引用列表。 * 使用 TRANSPOSE 函数实现数据 90 度翻转，且表格间保持链接。 完成本课程后，您将成为 Excel 高级查询函数和数据透视表方面的专家，能够显著提升您的 Excel 工作效率和数据分析能力。
Mastering the use of most popular LOOKUP'S and Pivot Tables will allow you to manipulate, extract and Analyze information like never before! The learners becomes experts after following this Video Course. This Complete course is About LOOKUP and References from Formulas and Complete Pivot Tables in Excel. Explained by Industry experts with all their experience here.This course is recorded with the latest version of Excel 2019. All the lectures are explained with the examples and you can download all these excel files and practice the formulas.This Course improves your Excel workflow faster with the true power of Excel lookup-and-reference functions. Course OverviewPivot Tables90% of Excel users do not use Pivot Tables but those that do save hours of time and become valuable assets in their companies.You learn Excel Pivot Tables & Pivot Charts quickly and effectively, download the companion exercise files so you can follow along with the instructor by performing the same actions he is showing you on the videos.By the end, you will be an Expert user of Pivot Tables, able to create reliable analyses which are able to be drilled-down quickly, and you'll be able to help others with their data analysis.Look up FunctionExcel makes it easy to search for values, both in rows and columns. While there are advanced functions also that enable Excel users to search for data "database-style", the HLookup and VLookup functions are their simpler-yet-powerful Functions.VLOOKUP was launched in 1985. It has been with Excel from the beginning; it was included in Excel 1 for Macintosh released in 1985. For 34 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE).Topics CoveredVLOOKUP to the LeftVLOOKUP to Replace Nested IF FormulaVLOOKUP Multiple TablesVLOOKUP Multiple CriteriaVLOOKUP Different Sheets with Same FormulaVLOOKUP Auto Update Column NumberUse of wildcard in VLOOKUPNested VLOOKUP Trick3D LOOKUP with VLOOKUP2D LOOKUP with VLOOKUPHLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECTLearning OutcomesBe an Expert using Powerful Excel functions: VLOOKUP, HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECTConsolidate data from multiple sheets with the INDIRECT functionCreate automatically-expanding references with the OFFSET functionCreate flexible lookups for Excel dashboards with drop-down listsCreate range-based lookups with the "approximate match"Create two-dimensional lookups by combining VLOOKUP and MATCHFind specific items using the "exact match"Overcome the limitations of VLOOKUP by using INDEX and MATCHRecognize the value of lookups and references to your specific workflowUse named ranges and Excel tables as reference listsUse TRANSPOSE function to flip your data 90 degrees with links to the original cells
