assistindo agora
82
Alunos Descrição
Os alunos aprenderão todos os tópicos do Excel em um curso.
Espero que depois de concluir este curso, você passe as Perguntas da entrevista de emprego sobre o Excel. Este curso é atualizado a cada semana. Novos tópicos do Excel são adicionados no curso após cada semana.
Além disso, este curso é gravado no Excel 2019. Todos os novos recursos do Excel 2019 são adicionados neste curso.
O ponto mais importante sobre este curso, este curso tem cinco questões em cada questionário no final de cada seção. Você pode testar seus conhecimentos sobre o Excel resolvendo perguntas do questionário.
O que você vai aprender?
- Aprenda as fórmulas mais usadas no escritório. Aprenda excel de iniciante a especialista. Aprenda todos os conceitos básicos do excel. Aprenda todos os conceitos avançados do Excel 2019. Aprenda como criar o pivot e o pivotchart no excel.
Requisitos
- Este curso está registrado no excel 2019, mas todas as versões anteriores de 2007, 2010, 2013, 2016 e o office 365 funcionarão neste curso.
Grade Curricular do Curso
Course consist of total 23h 7m of content, in total.
Seção 1: Data Analysis in PivotTables (Creating Revenue Dashboard)
33:33
Download Resource Files
Adding and Formatting Bar Chart and Creating Customer Revenue PivotTable
04:15
Creating Dashboard Part 1
03:57
Creating Dashboard (Adding Text Box) Part 2
03:48
Creating Dashboard (Adding Text Box) Part 3
03:17
Creating Dashboard (Adding Text Box) Part 4
03:18
Creating Connections between PivotTables and Slicers
03:24
Seção 2: Sparklines (Line,Column,Win or Loss) and Data Bars
20:59
Sparklines (Line,Column,Win or Loss)
13:27
Data Bars
07:32
Seção 3: Waterfall Chart,Funnel Chart and Map Chart
09:56
Waterfall Chart,Funnel Chart and Map Chart
09:56
Seção 4: People Graph,Sunburst Chart,Treemap and Bubble Charts
14:37
People Graph,Sunburst Chart,Treemap and Bubble Charts
14:37
Seção 5: Stocks and Geography,Progress Chart
24:11
Stocks and Geography,Progress Chart Part 1
11:05
Stocks and Geography,Progress Chart Part 2
13:06
Seção 6: Analyzing Sales Data in Pivot Tables and Pivot Charts
34:38
Adding Data in Excel Table
02:50
Adding and Formatting First Chart in Pivot Table
04:02
Adding and Formatting Second Chart in Pivot Table
03:27
Adding and Formatting Third Chart in Pivot Table
04:09
Adding and Formatting Fourth Chart in Pivot Table
03:17
Adding and Formatting Fifth Chart in Pivot Table
03:29
Adding and Formatting Sixth Chart in Pivot Table
03:04
Adding Chart in Excel Dashboard
03:03
Designing Dashboard Layout and Extracting Pivot Tables Data in Text Box
03:24
Inserting Slicers in the Dashboard for Analyzing Sales Data
03:53
Seção 7: Creating Excel Tables, Pivot Tables and Pivot Charts
46:07
Freezing Header or Row
01:31
Converting Data in an Excel Table
02:01
Adding Records in an Excel Table
04:09
Renaming and Finding Records in an Excel Table
02:47
Editing and Deleting Records in an Excel Table
02:26
Remove Duplicates
02:26
Sorting Date Column in an Excel Table
01:43
Sorting Multiple Columns
02:05
Filtering Data in an Excel Worksheet and Deleting an Excel Table
03:15
Filtering Data by Creating Criteria
02:57
Filtering Data in an Excel Table by Using Slicer
02:16
Splitting the Worksheet into Panes
02:34
Calculating Records in an Excel Table
02:54
Inserting Subtotals
03:07
Creating and Adding Data in a Pivot Table
03:51
Filtering Data in a Pivot Table
03:13
Adding Pivot Chart in a Pivot Table
02:14
Home Work
00:38
Creating Excel Tables, Pivot Tables and Pivot Charts Quiz
Seção 8: Data Validation and Inserting Comments
17:53
Specifying a Validation Rule by Using List
04:34
Show an Input Message on an Active Cell
04:10
Displaying Error Alert Message
04:01
Circle Invalid Data
01:08
Inserting Comments in a Worksheet
03:18
Home Work
00:42
Data Validation and Inserting Comments Quiz
Seção 9: Analyzing Data in the Table
24:21
Introducing and Converting a Range to a Table
03:03
Sorting a Table using Different Methods
03:40
Filtering a Table using Different Methods
05:48
Analyzing Basic Table Operations
05:33
Analyzing Table Data using COUNTIF,SUMIF and AVERAGEIF Functions
06:17
Seção 10: Analyzing Data with PivotTables
16:20
Introducing PivotTables
02:00
Creating PivotTable
04:49
Inserting Subtotals In the PivotTable
03:39
Summarizes Value Field by Changing Functions
02:11
Adding PivotChart and Slicer in the PivotTable
03:41
Seção 11: Analyzing Data with Business Intelligence (Power Queries and Trendlines)
20:46
Introducing Power Query and Upload CSV File
03:48
Retrieving Power Query Data into an Excel Table and Editing a Query Data
05:03
Adding a New Column and Grouping Values in a Power Query Data
06:40
Analyzing Revenue Data by Adding Trendlines in the Chart
05:15
Seção 12: Analyzing Data with Business Intelligence (Forecast Sheet)
06:04
Analyzing Revenue Data by Creating a Forecast Sheet
06:04
Seção 13: Analyzing Data with Business Intelligence (Power Pivot,Data Model & Power Map)
37:21
Introducing Database and Upload Access Database File
04:27
Creating the Data Model
04:26
Transferring Data in the Power Pivot
03:36
Exploring the Data Model in the Diagram View
02:01
Managing Table Relationships in the Power Pivot
04:11
Creating a PivotTable from the Data Model
05:38
Adding a Timeline Slicer in the PivotTable
02:48
Drilling down into the Data in the PivotTable
03:46
Visualizing Stores Sale Data with Power Map
06:28
Seção 14: Extracting Data from the Website(Power Query,Power Pivot,Pivot Table)
17:26
Importing Population and Area Data from the Website and Creating Queries
05:11
Extracting Population and Area Data into PivotTable Edit Delete Expand Move
06:20
Merging Two Queries into One Query
05:55
Seção 15: Loading Website Data into Power Map(Power Query,Power Map)
06:51
Loading Website Data into Power Map
06:51
Seção 16: Extracting Data from the Website(Power Query,Pivot Table)
16:36
Extracting and Transforming User Rating Data from the Website
07:52
Combining Queries
08:44
Seção 17: Extracting Football Team Data from the Website(Power Query,Pivot Table)
06:51
Extracting and Transforming Data from the Website
06:51
Seção 18: Analyzing Business Data by using What-If Analysis Tools(Goal Seek,Data Table)
31:21
Cost-Volume-Profit(CVP) Chart Analysis
02:54
Find the Break-Even Point by Using Goal Seek
04:09
Generate $30000 Net Income of Company by using Goal Seek Tool
01:13
Creating One-Variable Data Table to Examine the Impact of Changing Sales Volume
04:41
Completing the One-Variable Data Table
02:41
Creating the CVP Chart of the Data Table
02:59
Viewing the Impact of Changing the Sales Price in the Data Table
02:45
Creating the Two-Variable Data Table
05:35
Create a Chart of the Two Variable Data Table
04:24
Seção 19: Analyzing Business Data by using What-If Analysis Tools(Scenario Manager)
23:02
Define Names for the Income Statement Values
02:34
Adding the Scenarios in the Scenario Manager
07:18
View the Impact of the Reduced Operations Scenario
04:12
Creating the Scenario Summary Report
02:37
Creating the Scenario PivotTable Report
06:21
Seção 20: Analyzing Business Data with Solver
21:36
Understand Solver with Simple Example
05:52
Introducing Product Mix and Find an Optimal Solution with Solver Part 1
04:53
Introducing Product Mix and Find an Optimal Solution with Solver Part 2
06:10
Creating a Solver Answer Report
02:23
Saving and Loading Solver Model
02:18
Seção 21: Introduction to Excel
23:29
Course Files
00:41
Download Practices Files
Starting With Excel
01:43
Excel Tabs, Groups and Commands
02:26
Excel Rows and Columns
03:23
Entering Text and Numbers in a Worksheet
04:06
Changing the Size of Columns and Rows
01:31
Wrap Text Inside the Cell
00:44
Edit Text Within a Cell
00:34
Merging and Formatting Cells in a Worksheet
01:53
Formatting Header in a Worksheet
01:09
Adding Image in a Worksheet
01:55
Formatting Date in Excel Worksheet
01:24
Working of Undo & Redo Commands in an Excel Worksheet
01:07
Home Work
00:41
Introduction to Excel Quiz
Seção 22: Calculations With Basic Formulas
23:13
Introduction to Arithmetic Operators
01:45
Calculations with Arithmetic Operators
07:12
Copy and Paste the Formula
01:56
Auto Complete Formula
01:40
Inserting and Deleting Columns and Rows
02:03
Hide and Unhide Columns and Rows
01:26
Moving a Cell or Range by Drag and Drop Method
01:28
Moving a Cell or Range by Cut and Paste Method
00:41
Relative and Absolute Cell References
04:07
Home Work
00:55
Calculations With Basic Formulas Quiz
Seção 23: Calculations with Simple Excel Functions
22:05
Introduction to Function Syntax
02:17
Calculating Total Sales by Using SUM Function
02:46
Calculating Total Sales Quantities by Using SUM Function
01:12
Count Price Items by Using COUNT Function
01:28
Count Food Items by Using COUNTA Function
01:38
Calculating Average Sales by Using AVERAGE Function
02:14
Calculating Maximum Sales Value of Customer by Using MAX Function
01:31
Calculating Minimum Sales Value of Customer by Using MIN Function
01:28
Calculating Total Price of Products by Using PRODUCT Function
02:02
Calculating Sales Data by Using Auto FUNCTION Button
01:42
Calculating a Tax Rate on Total Sales
03:03
Home Work
00:44
Calculations with Simple Excel Functions Quiz
Seção 24: Formatting a Worksheet
32:49
Merging Two or More Cells into One Cell
02:05
Alignment of Cell Text
02:09
Splitting Text into Multiple Lines
02:08
Rotating a Cell Text or Numbers
01:31
Formatting Background Color of Text and Numbers
03:11
Formatting Font Size and Font Color
02:18
Adding Border into Cells
01:40
Formatting Cell Styles
02:11
Find and Replace Text in a Worksheet
03:07
Calculating Students Enrollment and Difference Between two Columns Values
04:22
Calculating Percentage Difference Between two Columns Values
02:45
Conditionally Formatting Cells in a Worksheet
02:54
Conditionally Formatting Cells with a Top and Bottom Rules
01:51
Home Work
00:37
Formatting a Worksheet Quiz
Seção 25: Saving and Printing a Workbook
15:50
Saving a Workbook
01:08
Worksheet Views Page Orientation and Scaling
03:37
Setting Margins in a Print Page
01:24
Adding Header and Footer in a Print Page
03:42
Print Preview and Selecting Print Area
03:05
Setting Scaling and Margins in a Print Page(Document 1)
02:54
Saving and Printing a Workbook Quiz
Seção 26: Project Calculations with Operators and Functions
25:05
Calculating the Area of Field and Plots
03:32
Calculating the Grown and Empty Area of Vegetables Field
01:58
Calculating the Percentage of Grown and Empty area in Vegetables Field
02:18
Calculating Cost of Sales and Weightages of Vegetables
04:08
Calculating Profit and Percentage of Profit from Vegetables
03:41
Calculating Revenue by Using Sum, Max, Min, Average and Median Functions
04:53
Introduction to NESTED Function
01:38
Nesting the SUM and MEDIAN Functions within ROUND Function
01:53
Home Work
01:04
Project Calculations with Operators and Functions Quiz
Seção 27: IF and VLOOKUP Functions
23:29
Introduction to IF Function and Apply Weather Condition on IF Function
04:10
Apply Different Conditions on IF Function
08:07
Introduction to VLOOKUP Function
03:30
Apply VLOOKUP Function on Data Table
02:02
Retrieving Values from Data Table by Using VLOOKUP Function
02:27
Errors in VLOOKUP Function
02:28
Home Work
00:45
IF and VLOOKUP Functions Quiz
Seção 28: Adding Chart in an Excel worksheet
15:20
Embed Column Chart in a Worksheet
02:42
Analyzing Data with a Column Chart
05:21
Analyzing Data with a Pie Chart
03:47
Recommended Charts
02:53
Home Work
00:37
Analyzing Data with a Chart Quiz
Seção 29: Date and Time Functions
42:36
NOW and TIME Functions
03:40
HOUR, MINUTE and SECOND Functions
03:27
TODAY, DATE AND DAYS Functions
03:57
YEAR, MONTH and DAY Functions
04:23
WEEKDAY Function
02:38
WEEKNUM Function
01:47
EDATE Function
02:32
EOMONTH Function
02:58
Determining Person's Birthday
02:03
Calculating Age of Person
02:13
Formatting Date in a Cell
01:24
DATEVALUE Function
02:13
DAYS360 Function
02:02
ISOWEEKNUM Function
01:38
NETWORKDAYS Function
01:22
TIMEVALUE Function
01:58
YEARFRAC Function
02:21
Date and Time Functions Quiz
Seção 30: Logical Functions in an Excel
1:01:02
Calculating the Age of Employees and Length of Service
02:45
Analyzing Data by Using IF Function
04:16
Determining Birthday of Employees
02:24
Specifying Day of Birthdate by Nesting DAY Function Within IF Function
03:06
Determining Employee Eligibility for Promotion by Using AND Function
04:14
Nesting the AND Function Within IF Function
04:24
Determining Employee Eligibility for Promotion by Using OR Function
03:49
Nesting the OR Function Within IF Function
04:23
IFERROR Function
03:00
IFNA Function
05:32
FALSE Function
02:48
IFS Function
04:42
NOT Function
03:24
SWITCH Function
04:20
TRUE Function
03:00
XOR Function
02:37
Home Work
00:32
Logical Functions in an Excel Quiz
COLUMN Function
01:46
Seção 31: Lookup and Reference Functions in an Excel
49:19
Extracting Value from Data Table by Using VLOOKUP Function
06:13
Extracting Value from Data Table by Using HLOOKUP Function
05:05
Calculating Discount and Total Price
02:02
Calculating Tax Rate on Total Price Value
01:09
Extracting Value by Using CHOOSE Function
03:59
Nesting the CHOOSE Function Within Sum Function
04:17
MATCH Function
02:58
Extracting Value by Using INDEX Function
03:20
Nesting the MATCH Function Within an INDEX Function
03:24
Nesting the VLOOKUP Function Within an IFERROR Function
04:29
COLUMN Function
01:46
LOOKUP Function
06:33
TRANSPOSE Function
03:41
Home Work
00:23
Seção 32: Adding Line and Combination Charts in an Excel
07:46
Adding and Formatting Line Chart
03:39
Adding and Formatting Combination Chart
04:07
Seção 33: Text Functions in an Excel
1:15:10
BHATTEXT Function
01:51
CHAR Function
02:18
CLEAN Function
01:30
CODE Function
01:17
CONCAT Function
02:25
DOLLAR Function
02:19
EXACT Function
02:29
FIND Function
03:40
FIXED Function
02:46
LEFT Function
02:44
LEN Function
01:55
LOWER Function
02:01
MID Function
04:05
NUMBERVALUE Function
02:50
PROPER Function
01:19
REPLACE Function
04:26
REPT Function
03:59
RIGHT Function
02:54
SEARCH Function
03:52
SUBSTITUTE Function
03:55
T Function
01:41
TEXT Function
04:09
TEXTJOIN Function
03:34
TRIM Function
01:48
UPPER Function
02:14
UNICHAR Function
01:17
UNICODE Function
01:42
UPPER Function
02:14
VALUE Function
01:56
Seção 34: Math and Trigonometry Functions in an Excel
1:02:12
SUM Function
05:44
MOD Function
01:39
POWER Function
02:57
SUBTOTAL Function
06:18
SUMIF Function
07:19
SUMIFS Function
08:09
SUMPRODUCT Function
04:20
ROUND Function
05:38
ROUNDDOWN Function
02:18
ROUNDUP Function
02:07
CEILING.MATH Function
03:21
FLOOR.MATH Function
02:59
EVEN Function
01:50
ODD Function
01:45
INT Function
02:25
TRUNC Function
03:23
Seção 35: Statistical Functions in an Excel
1:04:08
AVERAGE Function
01:50
AVERAGEIF Function
08:32
AVERAGEIFS Function
09:46
COUNT and COUNTA Function
04:46
COUNTBLANK Function
02:52
COUNTIF Function
07:16
COUNTIFS Function
08:09
LARGE Function
04:28
SMALL Function
03:51
MAX and MIN Function
02:56
MEDIAN Function
03:02
MODE.MULT Function
03:40
MODE.SNGL Function
03:00
Seção 36: Engineering Functions in an Excel
24:51
BIN2DEC Function
02:54
BIN2HEX Function
03:20
BIN2OCT Function
03:06
COMPLEX Function
03:46
CONVERT Function
05:08
DEC2BIN Function
02:49
DEC2HEX Function
01:56
DEC2OCT Function
01:52
Seção 37: Information Functions in an Excel
1:04:14
CELL Function
06:57
ERROR.TYPE Function
01:45
INFO Function
03:07
ISBLANK Function
04:34
ISERR Function
01:51
ISERROR Function
03:56
ISEVEN Function
02:54
ISFORMULA Function
02:48
ISLOGICAL Function
03:03
ISNA Function
02:55
ISNONTEXT Function
02:55
ISNUMBER Function
02:35
ISODD Function
03:00
ISREF Function
03:01
ISTEXT Function
03:01
N Function
03:18
NA Function
03:20
SHEET Function
03:00
SHEETS Function
02:23
TYPE Function
03:51
Seção 38: Database Functions in an Excel
14:42
DAVERAGE Function
04:17
DSUM Function
02:33
DMAX and DMIN Functions
03:54
DCOUNTA Function
03:58
Seção 39: Financial Functions:Borrow Money to Bank
1:22:09
Calculating a Quarterly Payment of Loan by using the PMT Function
07:15
Calculating a Future Value of Loan by using the FV Function
05:46
Calculating a Payment Period of Loan by using the NPER Function
04:11
Calculating a Present Value of Loan by using the PV Function
04:57
Calculating an Interest Rate of Loan by using the RATE Function
04:23
Positive Cash Flow and Negative Cash Flow
03:19
Calculating the Value of Inflation by Using FV Function
03:20
Calculating a Monthly Car Payment
04:20
Calculating the Interest Payment by using IPMT Function
05:39
Calculating the Principal Payment by using PPMT Function
03:26
Calculating Remaining Principal Amount and Total Payment
04:09
Completing the Amortization Schedule of Loan
06:25
Plotting Interest and Principal Payments
03:23
Calculating Cumulative Interest Payments by Using CUMIPMT Function
05:12
Calculating Cumulative Principal Payments by Using CUMPRINC Function
05:31
Calculating Remaining Principal,Total Interest and Total Principal Payments
06:04
Calculating Total Loan Amount
04:49
Seção 40: Financial Functions:Invest Money in the Bank Saving Account
57:15
Calculating the Compound Interest
05:27
Calculating Nominal and Effective Interest Rates Using NOMINAL&EFFECT Functions
06:11
Calculating the Future Value of an Investment by Monthly and Initial Deposits
06:36
Calculating the Interest Rate of an Investment
06:25
Calculating the Total Number of Deposits of an Investment
06:51
Calculating the Initial Deposit or Present Value of an investment
05:18
Calculating the Value of Deposit Per Month
04:38
Calculating the Variable Interest Rates by using FVSCHEDULE Function
05:09
Amortization Schedule of an Investment 1
05:28
Amortization Schedule of an Investment 2
05:12
Seção 41: Financial Functioms:Build Future Income Statement of Company
59:35
Introduction to Income Statement Worksheet
02:15
Linear and Growth Trend Values
02:09
Projecting Future Value of Sales or Revenue by using Linear Trend Option
05:02
Projecting Future Value of Sales or Revenue by Inserting a Growth Trend
04:59
Calculating the Cost of Research and Development
03:32
Calculating the Cost of Sales and Marketing
03:36
Calculating the Gross Profit
03:27
Calculating the Expenses of Company Part 1
03:21
Calculating the Expenses of Company Part 2
04:19
Calculating the Expenses of Company Part 3
04:29
Calculating the Total General Expenses and Initial Earnings
03:52
Adding Depreciation of an Asset in the Income Statement
03:09
Calculating Operating Profit in the Income Statement
02:32
Adding Interest Expenses in the Income Statement
04:00
Calculating Profit Without Adding Tax
01:55
Adding Taxes in the Income Statement
03:58
Calculating Final Profit of the Company in the Income Statement
03:00
Seção 42: Financial Functions:Calculate Depreciation of an Asset of Company
39:10
Straight Line Depreciation of an Asset Part 1
03:22
Straight Line Depreciation of an Asset Part 2
04:04
Straight Line Depreciation of an Asset Part 3
03:07
Declining Balance Depreciation of an Asset Part 1
03:58
Declining Balance Depreciation of an Asset Part 2
05:15
Calculating Depreciation of an asset by Using SYD Function
04:57
Calculating Depreciation of an asset by Using DDB Function
04:01
Calculating Depreciation of an asset by Using VDB Function
05:28
Calculating Depreciation Value of a Car
02:37
Selecting Method of Depreciation to Calculate Depreciation of an Asset
02:21
Seção 43: Financial Functions:Invest Money in Company
32:40
Calculating Yearly Interest Rate of an Investment by Using Rate Function Part 1
02:40
Calculating Yearly Interest Rate of an Investment by Using Rate Function Part 2
03:32
Calculating Payback Period of an Investment Part 1
04:30
Calculating Payback Period of an Investment Part 2
02:49
Calculating Payback Period of an Investment Part 3
02:44
Calculate Present Value of an Invetment by Using NPV Function Part 1
03:14
Calculate Present Value of an Invetment by Using NPV Function Part 2
02:26
Calculate Net Present Value of an Invetment
02:09
Calculate Internal Rate of Return of an Invetment by Using IRR Function
02:04
Calculate Net Present Value & Internal Rate of Return using XNPV & XIRR Functons
03:34
Calculate Equivalent Value of Interest Rate by Using RRI Function
02:58
Seção 44: Basic Financial Examples
39:07
Calculating the Remaining Amount with 30% Rate
03:30
Calculating the Remaining Amount with 15% and 40% Rate
04:25
Calculating the Percentage of Remaining Amount
04:30
Calculating the Percentage of Savings
04:10
Finding the Value of Actual Amount
04:57
Calculating the Value of Expense,Save and Total Amount
05:23
Calculating the Remaining Profit
04:04
Calculating the Values of Profit and Percentage of Profit
03:25
Calculating the Values of Loss and Percentage of Loss
04:43
Avaliações da plataforma BitDegree
Days
Hours
Minutes
Seconds