课程详情

Microsoft Excel is a powerful tool essential for managing and presenting datain today's working environment. In this one day excel course,you will gain the knowledge and skills required to create and edit worksheets, use formulas and functions, sort and filter detail data visually, and present summary information in a consumable and professional format.A macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer Errors. Macros are written in VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft.The Power Query and Power Pivot features in Microsoft Excel can make a powerful combination. Power Query enables you to discover, connect to, and import data, and Power Pivot lets you quickly model that data. You will learn how to use the DAX formula language to provide lookup abilities.I will walk you through step-by-step how to use Power Query to select data, prepare a query, cleanse data, and prepare data for Power Pivot. Also i will walks you through the Power Pivot workflow, showing how to create a data model, import additional data if needed, build relationships between data, and create calculations and measures. You will learn hands on real-world scenarios for working together with Power Query and Power Pivot.What You Will Learn Include: What is Office 365 Setting up Office 365 Excel 2016 user interface Exploring the Excel Ribbon Customizing the Quick Access Toolbar Keeping Tabs on the Ribbon Entering Data Opening a new workbook Entering Data in Excel Basic Data entry Entering Data with Autofill Entering Date Entering Time Undo and Redo Changes Adding Comments Giving your worksheet a title Saving your work Creating Formulas and Functions Using formulas for arithmetic tasks Re-using Formulas Calculating YTD Profits Calculating Percentage Change Using Relative and Absolute Reference Using RANK Function STD Function Small and Large Functions Median Function Count and counta functions Formatting Exploring Fonts Adjusting Column Width and Row Height Using Alignment Designing Borders Formatting Numbers Conditional FormattingCreating Tables Inserting Shapes What VBA is and why it is so incredibly usefulWhat are MacrosCreating Macros using macro recorderCreating macro manually from scratchEditing MacrosDebugging MacrosHow to write VBA code and execute it in ExcelHow to make your macros work with workbooks, worksheets and the data on themHow to find and eliminate errors in your programs and make your macros runAutomation using macros.Analyse the macro from the Developer tab or the View tab.Examine the VBA window and VBA components.Distinguish between absolute and relative references.How to save workbook with macrosCreate a moduleCreate procedureCreate a subUnderstand the difference between a module and subRun the code in a subPreparing queriesCleansing data with Power QueryEnhancing queriesCreating a data model in Power PivotBuilding relationshipsCreating Pivot Tables and Pivot ChartsCreating LookupsUsing DAX to link dataCreating data modelCreating relationship between data sourcesAnalyzing data with PivotTables and PivotChartsUsing Power Query with PowerPivotConnecting to a variety of data sources with Power BICreate and publish reports to Power BI ServiceRefreshing Data Source Updating QueriesUsing Conditional statementsUsing quick and dynamic measuresTransforming data on connected datasetsAfter completing this course you would have gained useful and practical skills to enable you to confidently create spreadsheets that you can apply formulas and functions to as well as create tables from the data within the spreadsheet and much more.

课程标签

0人关注该课程

主题相关的课程