Advanced Analytics with Excel 2019

Name: Advanced Analytics with Excel 2019 | Perform Data Analysis Using Excel's Most Popular Features
Brand: De Gruyter
Price: 14.49 EUR
Availability: OnlineOnly

Perform Data Analysis Using Excel's Most Popular Features

Manisha Nigam(Autor*in)

De Gruyter (Verlag)

1. Auflage

Erschienen am 4. Juli 2020

414 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-93-89845-81-5 (ISBN)

14,49 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Cover Page
Title Page
Copyright Page
About the Author
Preface
Who should read this book?
How is the book organized?
Version of Excel referred in this book
References
Table of Contents
1. Getting Started with Excel
1.1 Workbook and Worksheets
1.2 Navigation with Keyboard
1.3 Tabs and Ribbons
1.3.1 Contextual Tabs
1.3.2 Tab and Ribbon Visibility
1.4 File Menu
1.5 Quick Access Toolbar (QAT)
1.6 Excel Options
1.7 Create a New Workbook, Print and Save
1.7.1 Create New File
1.7.2 Print File
1.7.3 Save and Save As File
1.8 Understanding Worksheet Basics
1.8.1 Commands for entering/editing data
1.8.2 Insert/Delete Cells
1.8.3 Formatting Cells
1.8.4 Data Tools
1.8.5 Working with Excel tables
1.8.6 Controlling View of Worksheet
1.8.7 Accessibility Features
1.9 Protecting Excel Workbook and Worksheet
1.9.1 Protect Workbook
1.9.2 Protect Sheet & Allow Edit Ranges
1.10 Importing data into Excel
1.10.1 Importing from Text
1.10.2 Importing from Web
1.10.3 Import from Database
1.11 Exporting Data from Excel
1.11.1 Export to file
1.11.2 Export table to SharePoint List
1.12 Sharing in Excel
1.12.1 Co-authoring
1.12.2 View and restore changes in workbooks that are shared
1.12.3 Shared Workbooks (legacy)
2. Perform Functions with Shortcut Keys
2.1 Keys for Menus
2.2 Move on a Worksheet or Workbook
2.3 Select Cells, Columns, Rows or Objects
2.4 Select Cells with Special Characteristics
2.5 Format Data
2.6 Filling data in cells
2.7 Working on Tables
3. Formulas and Functions
3.1 Understanding Formulas
3.2 Operators in Formula
3.2.1 Arithmetic Operators
3.2.2 Logical / Comparison Operators
3.2.3 Text Concatenation & Reference Operators
3.2.4 Operators Precedence
3.2.5 Double Minus / Unary Operator
3.3 Defined Names
3.3.1 Create New, Delete, Edit Named Range
3.3.2 Using Named Range
3.4 Calculations
3.5 Functions in Formula
3.6 Relative and Absolute addressing
3.7 Referencing Cells Outside the Worksheet
3.8 Referencing Cells Outside the Workbook
3.9 Logical Functions
3.9.1 Using IF
3.9.2 Using nested IF
3.9.3 Writing conditional expressions: IF combined with AND / OR
3.9.4 Using IFS
3.9.5 Using SWITCH
3.10 Summarizing Functions
3.10.1 COUNT, COUNTA, COUNTBLANK
3.10.2 COUNTIF, COUNTIFS
3.10.3 SUM, AVERAGE, PRODUCT
3.10.4 SUMIF, SUMIFS
3.10.5 SUMPRODUCT
3.10.6 Calculations using SUM with OFFSET function
3.10.7 SUBTOTAL
3.11 Text functions
3.11.1 TEXT
3.11.2 FIND
3.11.3 LEN and TRIM
3.11.4 CONCATENATE
3.11.5 CONCAT
3.11.6 TEXTJOIN
3.11.7 LEFT, MID and RIGHT
3.12 Lookup and Reference functions
3.12.1 VLOOKUP, HLOOKUP
3.12.2 MATCH, INDEX
3.12.3 OFFSET
3.12.4 INDIRECT
3.13 Date and Time functions
3.13.1 TIME
3.13.2 DATE
3.13.3 TODAY
3.13.4 EOMONTH
3.13.5 NETWORKDAYS
3.13.6 YEARFRAC
3.14 Math Functions
3.14.1 INT
3.14.2 ROUND, ROUNDDOWN, ROUNDUP, MROUND
3.14.3 CEILING, FLOOR
3.14.4 ABS, SQRT, SUMSQ
3.14.5 EXP, LN
3.14.6 RAND, RANDBETWEEN
3.15 Statistical Functions
3.15.1 MIN, MAX
3.15.2 MINIFS, MAXIFS
3.15.3 RANK.EQ, RANK.AVG
3.15.4 FREQUENCY
3.15.5 MEDIAN, MODE.SNGL, MODE.MUTL
3.15.6 QUARTILE.INC(/.EXC), PERCENTILE.INC(/.EXC)
3.15.7 PERCENTRANK.INC, PERCENTRANK.EXC
3.15.8 STDEV.P(/.S), VAR.P(./S)
3.15.9 CORREL, COVARIANCE.P(/.S)
3.16 Financial Functions
3.16.1 PV and FV
3.16.2 PMT, IPMT and PPMT
3.16.3 NPV and XNPV
3.16.4 IRR and XIRR
3.17 Error Handling
3.17.1 Error Types
3.17.2 Error Handling Functions
3.18 Formula Auditing
3.18.1 Show Formulas
3.18.2 Trace Precedents, Trace Dependents, and Remove Trace Arrows
3.18.3 Trace Error
3.18.4 Error Checking, Evaluate Formula
3.18.5 Watch Window
4. Data Visualization with New Chart types
4.1 Chart types and when to use them
4.2 Waterfall Chart
4.3 Histogram
4.4 Pareto Chart
4.5 Box & Whisker Chart
4.6 Treemap Chart
4.7 Sunburst Chart
4.8 Sparkline Chart
4.9 3D Map
4.9.1 Create a Tour
4.9.2 Create a New Scene
4.9.3 Play Tour/Scene, Create Video
4.10 Funnel Chart
4.11 Map Chart
5. Gantt and Milestone Chart
5.1 Creating a Gantt Chart in Excel
5.2 Creating a Milestone Chart in Excel
6. SmartArt & Organization Chart
6.1 SmartArt graphics
6.2 Organization Chart
6.2.1 Insert Graphic
6.2.2 Change Layout, Color & Text
6.2.3 Add/Delete boxes
6.2.4 Move boxes
7. Get creative with Icons, 3D models, Digital Inking
7.1 Insert Icons
7.2 Insert / Edit Scalable Vector Graphics
7.3 Insert 3D Models
7.4 Digital Inking
7.4.1 Define new pen - Add Pen
7.4.2 Modify, Move or Delete Existing Pens
7.4.3 Rewind your Ink - Ink Replay
7.4.4 Write your equation - Ink to Math
7.4.5 Convert drawing to shape - Ink to Shape
7.4.6 Use Lasso Select for selecting Ink
8. Putting Data in perspective with Pivots
8.1 Understanding the Terminology
8.2 Verify the Source
8.3 Format data for sync
8.4 Recommended PivotTables
8.5 Setting PivotTable default layout
8.6 Adding Slicers & Timelines
8.7 Adding Calculated Fields in Pivot
8.8 Deleting Calculated Fields from Pivot
8.9 Adding Calculated Items in Pivot
8.10 Deleting Calculated Items from Pivot
8.11 Group Field in Pivot
8.11.1 Automatic time grouping
8.12 Interactive Dashboard with PivotTables, Charts and Slicers
8.13 Consolidate data from different sources in Pivot
8.13.1 Method #1: Data with identical column structure using Pivot Wizard
8.13.2 Method #2: Data with identical column structure using Microsoft Query
8.13.3 Method #3: Data with different column structure using Pivot
8.14 Using Power Pivot Add-in
8.14.1 Add to Data Model
8.14.2 Create a Relationship between datasets
8.14.3 Save Relationship Diagram View as Picture
8.14.4 Edit / Delete Relationship between data sets
8.14.5 Adding Calculated Column
8.14.6 Creating Perspectives in Power Pivot
8.14.7 Adding Measures / Calculated Fields
8.15 Get and Transform Data / Power Query tool
8.15.1 Import data from Text/CSV
8.15.2 Import data from Web
8.15.3 Import data from Table / Range
8.15.4 Import data from Other sources
8.15.5 Refine data using Power Query Editor
8.15.6 Different Merging options
8.15.7 Recent Sources and Existing Connections
9. Complex Data Analysis using ToolPak
9.1 Enabling Analysis ToolPak in Excel
9.2 Descriptive Statistics in Excel
9.3 ANOVA in Excel
9.3.1 ANOVA: Single Factor
9.3.2 t-Test following ANOVA
9.3.3 ANOVA: Two Factor with Replication
9.3.4 ANOVA: Two Factor without Replication
10. Forecasting in Excel
10.1 Forecast Sheet - One-click forecasting
10.1.1 Create Forecast Worksheet
10.1.2 Customize Forecast using Options
10.2 FORECAST Functions
10.2.1 FORECAST.ETS
10.2.2 FORECAST.ETS.CONFINT
10.2.3 FORECAST.ETS.STAT
10.3 What-if Analysis Tools
10.3.1 Scenario Manager
10.3.2 Goal Seek
10.3.3 Data Table
10.3.4 Solver Add-In
11. Mail Merge using Excel
11.1 Mail Merge from Word using Excel
11.1.1 Preparing the database
11.1.2 Creating the template
11.1.3 Generating the Merged document
11.1.4 Merged Field Codes
11.2 Mail Merge from Excel (without Word)
11.2.1 Prepare database in Excel
11.2.2 Prepare Mail Merge template in Excel
11.2.3 Print Preview the merged documents
12. Create Custom Excel Template
13. Macros in Excel
13.1 VBA Quick overview
13.1.1 Basic language constructs
13.2 Enabling Developer tab
13.3 Create Macro
13.4 Record Macro
14. Get help for your problem

Systemvoraussetzungen

Als PDF speichern Als Link merken