Data Analysis Using Excel and Python from Scratch

Name: Data Analysis Using Excel and Python from Scratch
ISBN: 9787301332146

Lan YijieAug 2025

3.2

Word count: 95K words
Chapters: 294
ISBN: 9787301332146

Report an error Find this book

Summary

This book introduces the methods and steps of data analysis and implements and compares them through Excel and Python respectively. Through this book, on the one hand, you can broaden your understanding of Excel's functions; on the other hand, you can learn and master the basic operations of Python.

The book is divided into 11 chapters, covering the positioning and core function comparison of Excel and Python in the field of data analysis, an introduction to statistical measures, setting up practice environments for Excel and Python, basic methods of data processing and analysis, ETL methods, data modeling theory, the foundations of data mining, basic methods of data visualization, and how to produce analytical reports.

The content progresses from浅 to深 and emphasizes practical functionality, making it suitable for data analysis workers, students in related fields, Python beginners, and those studying Excel in greater depth.

Editor's review

The book's distinctive feature is that it places Excel and Python side by side: for the same data analysis task, it first does it in Excel and then implements it in Python, so readers can see clearly the respective positioning and boundaries of the two tools. The content extends from statistical measures, environment setup, and basic methods of data processing and analysis to ETL, data modeling, the foundations of data mining, visualization, and the production of analytical reports, covering the complete workflow of data analysis. For office workers accustomed to Excel, this is a path to broadening their toolset; for Python beginners, the Excel comparison lowers the difficulty of understanding abstract code. The book emphasizes progressing from浅 to深 and practical functionality, and is suitable for reading while operating on a computer. One caveat: data analysis skills ultimately come from repeated hands-on practice; reading without practicing yields limited results.

Who it's for

A good fit for

Data analysis workers who use Excel daily and wish to expand their Python skills
Python beginners who want to be introduced to programming through familiar data analysis tasks
Students in economics, management, statistics, and related fields who need to master a practical analysis workflow
Self-learners who want a systematic understanding of the complete chain from data processing to analytical reporting

Not a good fit for

Readers already proficient in using Python for data analysis and seeking advanced algorithm content
Readers with no basic computer operation skills who do not intend to practice hands-on
Readers who only want to learn advanced Excel techniques and have no interest in programming

Reading tips

Read in chapter order; for each chapter, first look at the Excel implementation, then run the Python code yourself for comparison.
Prepare a set of your own real data and complete the full exercise from cleaning to visualization as the chapters progress.
When you reach the ETL and modeling sections, go back and compare the differences between the earlier Excel and Python approaches and take notes.
After finishing, independently produce an analytical report to test your mastery of the book's workflow.

版权信息
前言
第1章 Excel与Python的定位与功能对比
1.1 数据分析简介
1.1.1 发展趋势
1.1.2 赋能增值
1.2 Excel与Python的特征对比
1.2.1 定位对比
1.2.2 操作方式对比
1.2.3 适用场景和对象
1.3 Excel与Python的功能范围
1.3.1 Excel的功能区
1.3.2 Python数据处理包和工具
1.3.3 Excel与Python的数据处理功能对比
1.4 Excel与Python的选择和协作
1.4.1 如何选择
1.4.2 如何协作
第2章统计量
2.1 常用统计量介绍
2.1.1 集中趋势
2.1.2 离散程度
2.1.3 可能性评估
2.1.4 条件概率
2.2 随机变量及其分布
2.2.1 随机变量
2.2.2 离散型随机变量分布
2.2.3 连续型随机变量分布
第3章实践环境的搭建
3.1 Excel数据分析环境
3.1.1 配置和扩展功能区
3.1.2 Excel的操作方法
3.2 Python开发环境
3.2.1 安装Python
3.2.2 安装Anaconda开发环境
3.3 Python基础语法
3.3.1 认识Python的工作方式
3.3.2 基础语法——Python的独特“口音”
3.3.3 变量与类型——角色扮演
3.3.4 运算符——算术演示
3.3.5 字符串操作——以语文实例演示
3.3.6 条件控制——以物理实例演示
3.3.7 循环控制——以数学实例演示
3.3.8 GUI编程——以美术实例演示
3.3.9 复合数据类型——以地理实例演示
3.3.10 函数——以化学实例演示
3.3.11 类——时间对象的演示
3.3.12 包和模块
3.3.13 主要的数据科学包
3.3.14 数据库操作
3.4 Excel与Python的整合环境
3.4.1 安装openpyxl包
3.4.2 构建测试数据
3.4.3 Excel文件读写
3.4.4 自动化处理
第4章数据处理与分析
4.1 各种统计量的计算
4.1.1 平均数的计算
4.1.2 中位数的计算
4.1.3 众数的计算
4.1.4 最大值、最小值的计算
4.1.5 四分位数的计算
4.1.6 标准差的计算
4.1.7 频率计算
4.1.8 Excel与Python中统计量函数对比
4.2 数据分析与概率统计
4.2.1 描述统计
4.2.2 排列组合
4.2.3 二项分布计算
4.2.4 几何分布计算
4.2.5 正态分布计算
4.2.6 抽样工具
4.2.7 相关系数
4.2.8 方差分析
4.2.9 Excel和Python数据分析和概率计算功能对比
4.3 逻辑运算
4.3.1 逻辑“与”的运算
4.3.2 逻辑假的设置
4.3.3 逻辑条件判断
4.3.4 逻辑错误处理
4.3.5 异常值处理
4.3.6 逻辑“非”的运算
4.3.7 逻辑“异或”的运算
4.3.8 Excel与Python逻辑运算对比
4.4 文本处理
4.4.1 拼接字符串
4.4.2 对比字符串
4.4.3 查找字符串
4.4.4 计算字符串长度
4.4.5 大小写转换
4.4.6 替换字符串
4.4.7 去除空格
4.4.8 Excel和Python的文本处理函数对比
4.5 日期与时间
4.5.1 构建日期和时间数据
4.5.2 拆分日期和时间数据
4.5.3 日期的加减
4.5.4 星期制度数据
4.5.5 转换日期和时间格式
4.5.6 Excel和Python的日期和时间函数对比
4.6 查找与引用
4.6.1 定义引用关系
4.6.2 LOOKUP函数
4.6.3 VLOOKUP函数
4.6.4 TRANSPOSE函数
4.6.5 MATCH函数
4.6.6 Excel和Python的查找引用功能对比
4.7 数学与三角函数
4.7.1 数值处理
4.7.2 转换计算
4.7.3 三角函数
4.7.4 指数与对数
4.8 数据的排序、查重、汇总
4.8.1 数据的排序
4.8.2 数据的查重
4.8.3 数据的汇总
4.8.4 Excel和Python的数据排序、查重、汇总功能对比
第5章数据抽取——ETL中的E
5.1 连接数据库的配置
5.1.1 Excel中的配置方式
5.1.2 Python中的配置方式
5.2 使用Power Query
5.2.1 打开Power Query
5.2.2 使用Power Query
5.3 从数据库抽取数据
5.3.1 连接SQLite数据库
5.3.2 连接SQL Server数据库
5.3.3 连接Oracle数据库
5.3.4 连接MySQL数据库
5.3.5 连接PostgreSQL数据库
5.3.6 连接MongoDB数据库
5.3.7 Excel和Python连接数据库的方法对比
5.4 从数据文件中读取数据
5.4.1 从CSV文件中读取数据
5.4.2 从XML文件中读取数据
5.4.3 从JSON文件中读取数据
5.4.4 从TXT文件中读取数据
5.4.5 Excel和Python读取各类文件数据的方法对比
5.5 从互联网获取数据
5.5.1 构建测试用网站数据
5.5.2 抓取网页数据
5.5.3 调用Web API服务
5.5.4 Excel和Python抓取互联网数据方法对比
5.6 验证抓取的数据
5.6.1 数据统计
5.6.2 确定数据类型
5.6.3 检查数据边界
第6章数据清洗——ETL中的T
6.1 问题数据类型与数据清洗方法
6.1.1 问题数据类型
6.1.2 数据清洗方法
6.2 使用Python抓取演示用金融数据
6.3 数据清洗方法说明
6.3.1 Power Query清洗方法
6.3.2 DataFrame类型数据清洗方法
6.4 数据清洗实践
6.4.1 重复数据的处理
6.4.2 缺失数据的处理
6.4.3 添加自定义列
6.4.4 错误、异常数据的处理
6.4.5 数据关联
6.4.6 数据追加
6.4.7 分类汇总
6.4.8 步骤编排和复用
6.4.9 总结
第7章数据装载——ETL中的L
7.1 数据仓库ETL技术
7.1.1 ETL
7.1.2 ELT
7.2 通过Excel装载数据
7.2.1 保存为CSV文件
7.2.2 保存为HTML文件
7.2.3 保存为XML文件
7.2.4 发布到SharePoint
7.2.5 发布到Power BI
7.2.6 发布到OneDriver
7.3 通过Python装载数据
7.3.1 写入HDF5文件
7.3.2 写入关系数据库
7.3.3 生成HTML文件
7.3.4 保存到JSON文件
7.3.5 保存为LaTeX数据
7.3.6 保存为Parquet格式数据
7.3.7 转换为NumPy数据
7.3.8 生成Markdown格式数据
7.3.9 总结
7.4 数据装载策略
7.4.1 装载的方式
7.4.2 装载的频率
7.4.3 操作的方式
第8章数据建模
8.1 数据模型相关概念
8.1.1 关系模型
8.1.2 多维度建模
8.1.3 测试数据说明
8.2 使用Power Pivot构建数据模型
8.2.1 启用Power Pivot
8.2.2 认识Power Pivot
8.2.3 构建Power Pivot关系模型
8.2.4 应用Power Pivot数据模型
8.3 使用SQLAlchemy构建模型
8.3.1 启用SQLAlchemy
8.3.2 认识SQLAlchemy
8.3.3 通过SQLAlchemy构建数据模型
8.3.4 应用SQLAlchemy数据模型
8.4 Excel和Python构建关系数据模型对比
第9章数据挖掘
9.1 认识数据挖掘
9.1.1 数据分析和数据挖掘
9.1.2 数据挖掘算法的分类
9.1.3 数据挖掘步骤
9.2 Excel数据挖掘方案
9.2.1 安装数据挖掘插件
9.2.2 认识数据挖掘插件
9.2.3 使用数据挖掘插件
9.2.4 创建数据挖掘模型
9.2.5 管理数据挖掘模型
9.2.6 使用Excel函数进行数据挖掘
9.3 Python数据挖掘方案
9.3.1 安装scikit-learn
9.3.2 认识scikit-learn
9.3.3 scikit-learn API接口
9.4 scikit-learn操作
9.4.1 特征抽取
9.4.2 算法选择
9.4.3 验证操作
9.5 具体挖掘算法
9.5.1 线性回归
9.5.2 逻辑回归
9.5.3 聚类分析
9.5.4 贝叶斯算法
9.5.5 决策树算法
第10章数据可视化
10.1 数据可视化基础
10.1.1 数据可视化图的构成
10.1.2 数据可视化陷阱
10.2 可视化方案
10.2.1 Excel数据可视化方案
10.2.2 Python数据可视化方案
10.2.3 辨认数据类型
10.2.4 选择可视化图
10.3 散点图
10.3.1 散点图说明
10.3.2 在Excel中绘制散点图
10.3.3 在Python中绘制散点图
10.4 饼图
10.4.1 饼图说明
10.4.2 在Excel中绘制饼图
10.4.3 在Python中绘制饼图
10.5 条形图
10.5.1 分段条形图说明
10.5.2 堆积条形图说明
10.5.3 在Excel中绘制条形图
10.5.4 在Python中绘制条形图
10.6 面积图
10.6.1 面积图说明
10.6.2 在Excel中绘制面积图
10.6.3 在Python中绘制面积图
10.7 折线图
10.7.1 折线图说明
10.7.2 在Excel中绘制折线图
10.7.3 在Python中绘制折线图
10.8 柱形图
10.8.1 柱形图说明
10.8.2 在Excel中绘制柱形图
10.8.3 在Python中绘制柱形图
10.9 特殊可视化图
10.9.1 绘制股票K线图
10.9.2 数据挖掘可视化
10.9.3 其他学科数据可视化
10.10 Excel与Python可视化处理方式对比
第11章分析报告
11.1 分析报告基础
11.1.1 仪表板和报表的差异
11.1.2 设计的原则
11.1.3 内容的确定
11.1.4 Excel分析报告制作方案
11.1.5 Python分析报告制作方案
11.2 Excel数据透视
11.3 Excel数据仪表板
11.3.1 确定内容
11.3.2 布局设计
11.3.3 表格样式设置
11.3.4 添加目标值和计算百分比
11.3.5 使用迷你图
11.3.6 增强表格可视化效果
11.3.7 添加切片器
11.4 安装JupyterLab插件
11.4.1 安装插件
11.4.2 验证使用插件
11.5 JupyterLab交互式设计
11.5.1 使用Markdown添加说明
11.5.2 读取处理数据
11.5.3 添加交互功能
11.5.4 添加可视化控件

FAQ

Can I read this book directly if I know nothing about Python?

Yes. The book is aimed at Python beginners, progresses from浅 to深, and provides Excel–Python comparisons that use familiar tasks to help you understand code. However, you still need to set up the practice environment and run the examples yourself according to the book's steps; it is hard to master the material by reading alone. If you have basic experience with computer file operations and Excel, getting started will be smoother.

Are Excel and Python taught in comparison or separately in the book?

According to its content description, the book implements and compares the methods and steps of data analysis through Excel and Python respectively, so it is a comparative presentation. Readers can see both Excel's positioning and core functions in data analysis and learn Python's basic operations, thereby understanding which task scenarios each tool suits, rather than treating them as two unrelated sets of content.

Which stages of data analysis does this book cover?

According to the table of contents, it covers an introduction to statistical measures, setting up practice environments, basic methods of data processing and analysis, ETL methods, data modeling theory, the foundations of data mining, basic methods of data visualization, and how to produce analytical reports—essentially the complete workflow from data preparation to presenting results, suitable for building an overall understanding.

After finishing, can I directly take on data analysis work?

The book provides a methodological framework and an introduction to the tools; it helps readers understand the steps of data analysis and implement them in Excel and Python. Actual work capability also depends on familiarity with the business and the amount of project practice. It is advisable to apply the book's methods to your own real data, complete several full analyses, and write reports, then gradually take on actual analysis tasks.

Comments

Find this book

This site is an index: you start “Search all sources” in your browser, and the entries it lists all point to third-party sites; please judge their availability yourself.

About the author

Lan Yijie Author · Chinese

Lan Yijie is a senior software engineer and project manager who has long focused on and practiced the integrated use of data analysis and deep learning technologies. Publicly available information is limited, and details of his professional career and personal background have not been disclosed in depth, but his published works show that his writing is oriented toward practical application. He has spent years in development work involving Python-based data operations, master data projects, and big data analysis projects, with technical experience spanning both small data and big data. His representative works include Starting from Scratch: Data Analysis with Excel and Python, Python Big Data Analysis from Beginner to Mastery, and AI Prompt Engineering in Practice: Learning to Apply Large Language Models with Prompt Engineering from Scratch, most of which fall under programming design and general computing. These works follow a path from introductory to hands-on material, emphasizing the combination of tools and methods with real-world scenarios, and are suitable for readers who wish to systematically master data processing and prompt engineering skills.