使用R或者Python编程语言完成Excel的基础操作

Posted on by

职场白领和学生通常都会对Excel有一定的熟悉度，原因如下：

  1. 教育背景：在许多教育课程中，特别是与商业、经济、工程、生物统计、社会科学等相关的领域，Excel作为数据处理和分析的基本工具被广泛教授。

  2. 职场需求：在多种职业领域，如金融、会计、市场营销、人力资源等，Excel经常用于数据整理、预算编制、数据分析和报告制作。

  3. 普及性：Excel是Microsoft Office套件的一部分，这是世界上最流行的办公软件套件之一，因此很多人在工作或学习中都会接触到它。

  4. 功能性：Excel不仅支持基本的表格制作和数据计算，还提供了高级功能，如数据透视表、宏编程、条件格式、图表绘制等，这些功能使其成为处理和展示数据的理想选择。

  5. 用户友好：Excel具有直观的用户界面和丰富的帮助文档，使得用户即使没有编程背景也能相对容易地学习如何使用它。

  6. 标准化：Excel文件（如.xls和.xlsx）是一种广泛接受的文件格式，便于数据共享和协作。

  7. 数据分析：Excel在数据分析领域的应用非常广泛，它支持使用公式、函数和数据分析工具进行复杂的数据处理。

  8. 学术研究：学生在撰写毕业论文或进行学术研究时，经常需要处理和分析数据，Excel是完成这类任务的常用工具。

  9. 灵活性：Excel允许用户自定义工作流程，自动化重复性任务，提高工作效率。

  10. 跨平台：Excel有适用于Windows、macOS等操作系统的版本，并且还有在线版本，增加了其可访问性。

尽管Excel在职场和学术界非常流行，但对于一些高级的统计分析、数据可视化、大规模数据处理等任务，可能需要更专业的软件或编程语言，如R、Python、SAS或Stata。此外，对于特定的行业或研究领域，可能会有其他更适合的工具和平台。

因为大家都或多或少熟悉了Excel操作，所以可能会误以为Excel是非常容易掌握的， 这个是“知识的诅咒”，我们会忘记了当初是如何从零开始掌握一个技术的。其实从零开始学习Excel确实可能会感觉有些挑战，尤其是考虑到Excel具有广泛的功能和深入的定制选项。但是，通过分阶段学习，逐步掌握基础知识和更高级的技能，学习过程可以变得更加容易和有成效。以下是一些建议，可以帮助你从零开始学习Excel：

  1. 理解基本概念：首先了解Excel的基本组成部分，如工作簿、工作表、单元格、行、列等。

  2. 熟悉界面：打开Excel并熟悉其界面，包括菜单栏、工具栏、功能区等。

  3. 掌握基本操作：学习如何插入、删除行/列，重命名工作表，以及基本的数据输入。

  4. 使用公式：学习使用Excel的基本公式，如SUM、AVERAGE、VLOOKUP等，并理解相对引用和绝对引用的概念。

  5. 数据格式设置：了解如何设置数据格式，包括数字、货币、日期、百分比等。

  6. 条件格式：学习如何使用条件格式来突出显示满足特定条件的单元格。

  7. 图表：学习如何根据数据创建图表，如柱状图、折线图、饼图等。

  8. 数据排序和筛选：掌握如何对数据进行排序和筛选，以查找和组织信息。

  9. 数据透视表：学习如何创建和使用数据透视表对数据进行多维度分析。

  10. 宏和VBA：对于更高级的用户，可以学习如何录制宏和编写VBA代码来自动化重复性任务。

  11. 函数学习：逐渐学习更多的内置函数，如逻辑函数、文本函数、统计函数等。

  12. 实际练习：通过解决实际问题来练习你的技能，可以是工作中的项目，也可以是自己感兴趣的数据集。

  13. 在线资源：利用在线教程、视频课程、社区论坛和官方文档来学习。

  14. 逐步提高：不要试图一次性学习所有内容，而是逐步提高，从基础到高级功能。

  15. 求助和分享：加入Excel用户社区，如论坛或社交媒体群组，与其他用户交流心得和技巧。

  16. 定期复习：定期复习你已经学过的内容，以防忘记。

  17. 项目实践：通过完成一些小项目，如家庭预算、工作报表、学校作业等，将所学知识应用到实践中。

  18. 设置目标：为自己设定学习目标和里程碑，这有助于保持动力并衡量进度。

  19. 耐心和毅力：学习任何新技能都需要时间和努力，不要灰心，保持耐心和毅力。

  20. 享受过程：尝试找到学习Excel的乐趣，随着技能的提高，你将能够更有效地完成工作和项目。

记住，Excel是一个非常强大的工具，即使你只掌握了其一小部分功能，也能在工作和学习中获得巨大的回报。

同理，我们能掌握Excel操作， 那就未必不可以掌握编程语言，比如常见的R或者Python编程语言就几乎是可以代替大家在Excel里面的需求的实现啦。

Excel的基础表格操作

在Excel中，对表格数据进行增删改查（即增加、删除、修改、查询）以及排序和筛选等操作是常见的数据处理任务。以下是一些基本的操作方法：

1. 增加数据

  • 插入行或列：右键点击行号或列标，选择“插入”。
  • 输入数据：直接在单元格中输入数据。

2. 删除数据

  • 删除行或列：右键点击行号或列标，选择“删除”。
  • 清除内容：选中单元格，按Delete键或右键选择“清除内容”。

3. 修改数据

  • 直接修改：选中单元格，直接输入新数据。
  • 使用查找和替换：按Ctrl+F或Ctrl+H，进行查找和替换操作。

4. 查询数据

  • 使用公式：在单元格中输入公式进行计算。
  • 查找特定数据：按Ctrl+F打开查找窗口，输入要查找的内容。

5. 排序

  • 简单排序：选中数据区域，点击“数据”选项卡中的“升序”或“降序”按钮。
  • 自定义排序：点击“排序和筛选”中的“自定义排序”，设置排序规则。

6. 筛选

  • 应用筛选器：选中数据区域，点击“数据”选项卡中的“筛选”按钮。
  • 筛选特定数据：在列头上的筛选下拉菜单中选择要显示的数据。

7. 高级查询

  • 使用高级筛选：在“数据”选项卡中选择“高级”，根据条件进行数据筛选。
  • 使用查询：在“数据”选项卡中使用“从表/区域获取数据”进行更复杂的查询。

8. 数据验证

  • 限制输入：选中单元格，点击“数据”选项卡中的“数据验证”，设置输入限制。

9. 数据分析

  • 使用PivotTable：在“插入”选项卡中选择“透视表”，对数据进行多维度分析。

10. 格式化

  • 设置单元格格式：右键点击单元格，选择“格式化单元格”，设置字体、颜色、边框等。
  • 应用样式：使用“开始”选项卡中的“样式”快速应用预设的单元格样式。

11. 数据导入与导出

  • 导入外部数据：使用“数据”选项卡中的“从文本/CSV”或“从其他源”导入数据。
  • 导出数据：可以将表格导出为CSV、Excel文件或其他格式。

12. 条件格式

  • 高亮显示特定数据：在“开始”选项卡中使用“条件格式”根据条件自动设置单元格格式。

13. 合并与拆分单元格

  • 合并单元格：选中多个单元格，点击“合并与居中”。
  • 拆分单元格：选中合并的单元格，点击“合并与居中”旁边的小箭头选择拆分选项。

14. 使用函数

  • 使用逻辑、统计、文本、日期等函数：在单元格中输入如=SUM(A1:A10)、=VLOOKUP(value, range, column, [exact])等函数进行计算。

这些是Excel中一些常见的数据操作技巧，掌握这些技巧可以大大提高处理表格数据的效率。

Excel的中级表格操作

在Excel中除了前面提到的增删改查、排序、筛选等基本操作，Excel还提供了许多其他高级的表格处理功能，可以帮助用户更高效地分析和呈现数据。以下是一些其他的操作：

数据分析工具

  • 数据透视表：对大量数据进行快速汇总和分析。
  • 数据透视图：将数据透视表的数据以图表形式展示。

条件格式

  • 数据条：根据单元格的值显示条形图。
  • 色阶：根据单元格的值变化显示颜色的深浅。
  • 图标集：在单元格中显示图标，以直观地表示数据的大小。

公式和函数

  • 数组公式：对一系列数据进行复杂的计算。
  • 查找和引用函数：如VLOOKUP、HLOOKUP、INDEX和MATCH等。
  • 统计函数：如AVERAGE、MEDIAN、STDEV等。
  • 逻辑函数：如IF、AND、OR等。

图表

  • 插入图表：根据数据快速创建各种类型的图表，如柱状图、折线图、饼图等。
  • 自定义图表：调整图表样式、布局、图例等。

文本处理

  • 文本分列：将一列数据根据分隔符分成多列。
  • 合并文本：使用CONCATENATE函数或“&”运算符将多个单元格的文本合并为一个。

宏和VBA编程

  • 录制宏：自动记录一系列操作，以便重复执行。
  • VBA编程：编写VBA代码实现自动化和定制化功能。

数据导入和处理

  • 从外部数据源导入：如从数据库、网站或文本文件导入数据。
  • Power Query：用于数据清洗、转换和加载的强大工具。

安全性和协作

  • 保护工作表/工作簿：设置密码保护，限制对数据的访问和修改。
  • 共享工作簿：允许多人同时编辑同一份Excel文档。

打印设置

  • 页面布局：调整边距、方向、大小等。
  • 打印区域：设置哪些单元格或区域需要打印。
  • 打印预览：查看打印效果并进行调整。

模板

  • 使用模板：快速创建具有预定义格式和功能的表格。

高级筛选

  • 自定义筛选条件：设置复杂的筛选条件，如“大于”、“小于”、“包含”等。

错误检查

  • 追踪错误：找出公式中的错误来源。
  • 错误检查：使用Excel的错误检查功能识别和修复常见错误。

函数库

  • 使用Excel函数库：利用Excel提供的大量预定义函数进行复杂的数据处理。

自定义快捷键

  • 设置快捷键：为常用操作设置快捷键，提高工作效率。

自定义视图

  • 创建视图：保存当前的视图设置，如行高、列宽、排序状态等。

这些高级功能可以帮助用户进行更深入的数据分析，实现更复杂的数据处理需求，以及提高工作效率。掌握这些技能可以显著提升使用Excel的能力。

在R编程语言中

处理表格数据通常依赖于dplyr和tidyr这样的包，它们提供了强大的数据操作功能。以下是一些基础操作在R中的实现方式，以及一个实战案例。

安装和加载必要的包

install.packages("dplyr")
install.packages("tidyr")
library(dplyr)
library(tidyr)

基础操作

  1. 读取数据：使用read.csv()或read.table()等函数读取CSV或文本文件。
data <- read.csv("path_to_file.csv")
  1. 增加列：使用mutate()添加新列。
data <- data %>%
 mutate(new_column = existing_column * 2)
  1. 删除列：使用select()去除不需要的列。
data <- data %>%
 select(-column_to_remove)
  1. 修改数据：直接对数据框的列进行赋值操作。
data$existing_column[which(data$existing_column > 10)] <- 10
  1. 查询数据：使用filter()根据条件筛选数据。
filtered_data <- data %>%
 filter(some_column > 5)
  1. 排序：使用arrange()对数据进行排序。
sorted_data <- data %>%
 arrange(desc(some_column))
  1. 分组求和：使用group_by()和summarise()进行分组汇总。
grouped_data <- data %>%
 group_by(group_column) %>%
 summarise(sum = sum(numeric_column))
  1. 合并数据：使用left_join(), right_join(), inner_join(), full_join()进行数据合并。
data1 <- read.csv("data1.csv")
data2 <- read.csv("data2.csv")
merged_data <- left_join(data1, data2, by = "common_column")
  1. 重塑数据：使用pivot_longer()或pivot_wider()在长格式和宽格式之间转换数据。
long_data <- data %>%
 pivot_longer(cols = starts_with("variable_"), names_to = "variable", values_to = "value")

实战案例

假设我们有一个名为sales_data.csv的文件，包含商店的销售数据，有以下列：Date, Store, Product, Sales, Customers。

目标

找出每个商店每月的总销售额，并按商店和日期排序。

R代码

# 读取数据
sales <- read.csv("sales_data.csv")

# 将日期列转换为日期类型
sales$Date <- as.Date(sales$Date)

# 转换为每月总销售额
sales_monthly <- sales %>%
 mutate(Month = format(Date, "%Y-%m")) %>%
 group_by(Store, Month) %>%
 summarise(Total_Sales = sum(Sales))

# 按商店和日期排序
sales_monthly <- sales_monthly %>%
 arrange(Store, Month)

# 查看结果
print(sales_monthly)

这个实战案例展示了如何使用R语言进行数据的读取、转换、汇总和排序。通过dplyr和tidyr包，我们可以轻松地对数据进行复杂的操作。

在R语言中，即使不使用dplyr和tidyr这样的现代包，也可以使用基础包中的函数来完成数据操作。以下是使用R的基础函数完成类似操作的例子：

读取数据

data <- read.csv("path_to_file.csv", header = TRUE)

增加列

data$new_column <- data$existing_column * 2

删除列

data <- data[ , !(names(data) %in% c("column_to_remove"))]

修改数据

data$existing_column[data$existing_column > 10] <- 10

查询数据

filtered_data <- data[data$some_column > 5, ]

排序

sorted_data <- data[order(data$some_column, decreasing = TRUE), ]

分组求和

grouped_data <- aggregate(numeric_column ~ group_column, data = data, FUN = sum)

合并数据

merged_data <- merge(data1, data2, by = "common_column")

重塑数据

对于长格式到宽格式的转换，基础R没有直接的函数像pivot_wider()那样工作，但可以使用reshape()函数：

library(reshape)
long_data <- acast(data, date + id_variable ~ variable_name, value.var = "value_to_pivot")

实战案例

继续使用之前商店销售数据的实战案例：

# 读取数据
sales <- read.csv("sales_data.csv", header = TRUE)

# 将日期列转换为日期类型
sales$Date <- as.Date(sales$Date)

# 创建月份列
sales$Month <- format(sales$Date, "%Y-%m")

# 转换为每月总销售额
sales_monthly <- aggregate(Sales ~ Store + Month, data = sales, FUN = sum)

# 按商店和日期排序
sales_monthly <- sales_monthly[order(sales_monthly$Store, sales_monthly$Month), ]

# 查看结果
print(sales_monthly)

使用R的基础包进行数据处理可能需要编写更多的代码，并且不如dplyr和tidyr这样的专用包那样直观和方便。然而，基础包的函数非常强大，对于简单的数据处理任务来说，它们是完全足够的。此外，对于复杂的数据处理任务，或者当需要编写自定义函数时，基础包的函数也非常重要。

在Python编程语言中

处理表格数据通常使用Pandas库，它提供了非常强大的数据结构和数据分析工具。以下是如何在Python中使用Pandas完成类似于R语言中的操作，以及一个实战案例。

安装Pandas

如果尚未安装Pandas，可以通过pip安装：

pip install pandas

基础操作

  1. 读取数据：使用pandas.read_csv()或pandas.read_table()读取CSV或文本文件。
import pandas as pd

data = pd.read_csv('path_to_file.csv')
  1. 增加列：通过直接赋值增加新列。
data['new_column'] = data['existing_column'] * 2
  1. 删除列：使用drop()方法。
data.drop('column_to_remove', axis=1, inplace=True)
  1. 修改数据：直接对DataFrame的列进行修改。
data.loc[data['existing_column'] > 10, 'existing_column'] = 10
  1. 查询数据：使用布尔索引查询数据。
filtered_data = data[data['some_column'] > 5]
  1. 排序：使用sort_values()方法。
sorted_data = data.sort_values(by='some_column', ascending=False)
  1. 分组求和：使用groupby()和sum()方法。
grouped_data = data.groupby('group_column')['numeric_column'].sum().reset_index()
  1. 合并数据：使用merge()方法。
data1 = pd.read_csv('data1.csv')
data2 = pd.read_csv('data2.csv')
merged_data = pd.merge(data1, data2, on='common_column', how='left')
  1. 重塑数据：使用melt()或pivot_table()方法。
long_data = pd.melt(data, id_vars=['id_var'], value_vars=['variable_1', 'variable_2'])

实战案例

假设我们有一个名为sales_data.csv的文件，包含商店的销售数据，有以下列：Date, Store, Product, Sales, Customers。

目标

找出每个商店每月的总销售额，并按商店和日期排序。

Python代码

import pandas as pd

# 读取数据
sales = pd.read_csv('sales_data.csv')

# 将日期列转换为日期类型
sales['Date'] = pd.to_datetime(sales['Date'])

# 创建月份列
sales['Month'] = sales['Date'].dt.to_period('M')

# 转换为每月总销售额
sales_monthly = sales.groupby(['Store', 'Month'])['Sales'].sum().reset_index()

# 按商店和日期排序
sales_monthly.sort_values(by=['Store', 'Month'], inplace=True)

# 查看结果
print(sales_monthly)

这个实战案例展示了如何在Python中使用Pandas库进行数据的读取、类型转换、增加列、分组求和、排序和查看结果。Pandas提供了类似于R语言中的数据操作功能，使得数据处理变得非常直观和方便。

在Python中，处理表格数据的基础包是Pandas，但它本身已经是一个非常强大的库，提供了许多高级功能。然而，如果你想要使用Python的更基础的内置数据结构和功能来处理数据，你可以使用列表（List）、字典（Dictionary）和内置的函数来完成一些简单的操作。以下是一些使用Python基础数据结构进行数据处理的例子：

读取数据

假设数据已经以列表形式加载到Python中：

data = [
 ['Date', 'Store', 'Product', 'Sales', 'Customers'],
 ['2021-01-01', 'Store A', 'Product 1', 100, 10],
 # ... 更多数据行
]

增加列

# 假设我们要基于已有的列增加一个新列 'Total'，为 'Sales' 和 'Customers' 之和
for row in data[1:]: # 跳过标题行
 row.append(row[-2] + row[-1]) # 假设 'Sales' 在倒数第二列，'Customers' 在最后一列

删除列

# 删除 'Customers' 列
data[1:] = [[col for col in row if col != 'Customers'] for row in data[1:]]

修改数据

# 假设我们要将所有 'Sales' 大于10的值改为10
for row in data[1:]:
 if row[-2] > 10: # 假设 'Sales' 在倒数第二列
 row[-2] = 10

查询数据

# 查询 'Sales' 大于5 的所有行
filtered_data = [row for row in data[1:] if int(row[-2]) > 5]

排序

# 按 'Sales' 列降序排序
data[1:] = sorted(data[1:], key=lambda x: int(x[-2]), reverse=True)

分组求和

分组求和在不使用Pandas的情况下会相对复杂，需要手动实现分组逻辑：

# 假设我们要按 'Store' 分组求 'Sales' 的和
grouped_sum = {}
for row in data[1:]:
 store = row[1] # 假设 'Store' 在第二列
 sales = int(row[-2]) # 假设 'Sales' 在倒数第二列
 if store not in grouped_sum:
 grouped_sum[store] = 0
 grouped_sum[store] += sales

print(grouped_sum)

合并数据

在不使用Pandas的情况下，合并数据需要手动实现连接逻辑：

# 假设 data1 和 data2 是两个已经加载的列表，我们要按 'common_column' 合并
data1_common = [row[common_index] for row in data1] # common_index 是共同列的索引
data2_common = {row[common_index]: row for row in data2[1:]} # 假设标题行已被跳过

merged_data = []
for row in data1[1:]:
 common_value = row[common_index]
 if common_value in data2_common:
 merged_data.append(row + data2_common[common_value])

使用Python基础函数处理数据通常适用于数据量较小、结构简单的场景。对于更复杂的数据处理任务，使用Pandas等专门的数据分析库会更加高效和方便。在实际工作中，直接使用Pandas进行数据处理是非常常见的做法，因为Pandas提供了对大型数据集进行高效操作的能力，以及丰富的数据分析功能。

​

Comments are closed.