Data Analysis and Business Modeling with Excel 2013电子书

Data Analysis and Business Modeling with Excel 2013

Table of Contents

Data Analysis and Business Modeling with Excel 2013

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Downloading the color images of this book

Errata

Piracy

Questions

1. Getting Data into Excel

Getting started with data

Gathering data

Preparing data

Analyzing data

Presenting data

Manually creating data

Importing data from various sources

Importing data from a text file

Importing a CSV file

Importing other Excel files

Importing data from the Web

Summary

2. Connecting to Databases

Reading a table from MSSQL – the Microsoft SQL Server database

Reading multiple tables from MSSQL

Reading from MSSQL using SQL

Summary

3. How to Clean Texts, Numbers, and Dates

Leading/trailing/in-between spaces

Capitalization

Duplicates

Text to Columns

Combine data from multiple columns into one column

Fixing similar words

Text to dates

Text to numbers

Summary

4. Using Formulas to Prepare Your Data for Analysis

How to create formulas

Combining strings and numbers

Using built-in functions

If/else/then statements

Comparing columns

Summary

5. Analyzing Your Data Using Descriptive Statistics and Charts

Gathering data

Preparing the data for analysis

Analyzing our data

Question – how many applications were processed in the year 2014?

Summary

6. Link Your Data Using Data Models

Gathering data

Preparing data

Analyzing data

Data models

Summary

7. A Primer on Using the Excel Solver

Activating the Excel Solver

Modeling our linear programming problem

Using the Excel Solver

Summary

8. Learning VBA – Excel's Scripting Language

What is VBA?

What is a macro?

Opening the VBA Editor

Your very first "Hello World" VBA script

Declaring variables

Conditional statements

Loops

Creating macros without any code

Saving macro-powered spreadsheets

Summary

9. How to Build and Style Your Charts

Quick analysis charts

Charting options

Chart elements

Chart styles

Chart filters

Additional design options

Summary

10. Creating Interactive Spreadsheets Using Tables and Slicers

What are slicers?

How to create slicers

Styling slicers

Adding multiple slicers

Clearing filters

Final tweaks

Summary

A. Tips, Tricks, and Shortcuts

Shortcuts

Tips and tricks

Index