Excel助力科研-入门篇202211.pdf
Excel助力科研-入门篇202211.pdf
Excel 助力学习科研（入门篇） 中国科学技术大学图书馆 2022.11 前言/PREFACE EXCEL：强大的数据处理与分析展示功能 Excel在科研方面的应用 图1 图2 图3 图4 图5 图6 Microsoft Office 4 …… Excel 是Office系列软件 之一，是世界上使用最广 泛、最流行的电子表格。 WPS是金山软件股份有限公司开发的一款办公软件套件。 5 6 在学校 在家里 在公司 触类旁通、举一反三，锻炼解决实际问题的能力。 以具体应用为根 精选出包括Excel 本，从基础入手, 高级筛选等特色 讲解Excel的基本 功能的典型案 概念和基本操作 例…… 7 录屏的方式完成 帮助同学们快速 案例的详细操作 的掌握利用Excel 过程 来处理分析数据 子曰：工欲善其事，必先利其器。 要想做好学术研究，首先要善于使用科研工具。 9 1、Excel的基本知识 •1、工作簿/工作表/单元格；2、Excel的工作环境；3、Excel选项卡：自动保存/设置密码；4、窗口操作技巧：冻结窗 格；5、工作表页面布局设置与打印 2、数据录入技巧 •1、数据的录入技巧；一般录入/编号、序列（自定义序列）录入/快速填充/单元格内换行 •2、数据验证：数值限制/下拉列表填充 3、数据处理与分析 •1、查找与替换；2、选择性粘贴；3、数据拆分 分列；4、筛选：筛选特定内容/颜色筛选/自定义筛选条件；5、数 据合并、分类汇总 4、运用函数处理数据 •text（文本）；len（文本字符串中的字符数）；vlookup（查找和引用）；proper（首字母转换成大写） 5、图表的使用技巧 •1、图表；2、迷你图 6、数据透视表的应用 •1、概述 •2、数据透视表与数据透视图的创建与操作 01 认识Excel—Excel的基本知识 Excel简介： 是Office组件之一，是一套功能强大的电子表格处理软件，广泛应用于 我们工作和生活的各个领域（如人事管理、行政管理、市场与营销管理、 生产管理、投资分析等）。 是机关单位、公司、学校、医院、工厂甚至家庭必不可少的工具，用于 管理账务、制作报表，对数据进行排序和分析，或者将数据转换为直观 的图表等。 主要功能：表格的制作和打印，数据记录与整理、数据加工与计算、数 据统计与分析、图形报表的制作等。 认识Excel Excel的 基本知识 12 • 1、工作簿/工作表/单元格； • 2、Excel的工作环境 • 3、Excel选项卡：自动保存/设置密码； • 4、窗口操作技巧：窗口查看/窗口拆分/冻结窗格； • 5、工作表页面布局设置与打印:添加表头/添加页 码/纯色打印/打印内容在一页 1.1工作簿、工作表、单元格 工作簿 在excel中，一个工作簿就是一个 用来存储并处理工作数据的excel 文件，它是工作表的集合体，工 作簿就像日常工作的日记本。一 个工作簿中可以放多张工作表， 最多可以放255张。 13 1.1工作簿、工作表、单元格 14 工作表 每张工作表都以标签的形式排列 在工作簿的底部。Excel工作表是 由行和列组成的一张表格，行用 数字1、2、3、4等来表示行号， 列用英文字母A、B、C、D表示列 号。工作表是数据存储的主要场 所，一个工作表可以有1048576 行和16384列构成。 1.1工作簿、工作表、单元格 15 单元格 行和列交叉的区域成为单元格。 它是excel工作表中的最小单位。 单元格按所在的行列交叉位置来 命名，命名时，列号在前，行号 在后。单元格的名称又成为单元 格的地址。 红色框单元格名称？ 1.2Excel的工作环境 快速访问工具栏 标签(选项卡)， 功能区命令按钮。 名称栏，编辑栏。 工作表编辑区。 滚动条。 工作表标签， 状态栏，视图和显示 比例工具条。 16 工作簿标题栏 功能区设置按钮，窗口按钮 登陆账户 1.2Excel的工作环境 工作编辑区一演示操作 认识名称框：左上角。记录当前选择的单元格的位置； 播入函数： fx。 放置一些常用的函数。体现excel中函数的重要性； 编辑栏：记录显示当前选择的单元格中的值。 工作区范围：行(Ctrl+左/右箭头)，列(CtrI+上/下箭头)； 获取列数：函数(column) /选项-公式R1C1引用样式； 单元格：表格中行与列的交叉部分，它是组成表格的最小单位，可拆分或者合并。 工作表有多少个单元格？ (思考…… ) 状态栏：常见数据汇总，视图切换，大小缩放 17 18 工 作 表 环 境 19 自 定 义 功 能 区 按 钮 16384 列 1048576行 工 作 区 范 围 大 小 行(Ctrl+左/右箭头)，列(CtrI+上/下箭头) 20 1.2Excel的工作环境----单元格设置 21 单元格：行，列，单元格引用样式切换，同一单元格里换行: alt+enter； 单元格的选择: 单元格区域;行/列全选(Ctrl+a; 三角标志)，多选(Ctrl+左键移 动鼠标 单元格的设置: 基础设置(插入,删除，移动，复制，行(列)高一-大致，精确) 录入技巧(正常录入，长数字录入，分数录入) 特殊符号的录入(硬键盘,软键盘插入，从功能区(插入- (特殊、字体)符号)……) 字体，边框，底纹，定位 1.3Excel选项卡：自动保存/设置密码 在制作Excel文档时如遇到意外错误，突然重新启动 或计算机死机等状况，会导致数据全部丢失? 文件选项卡下： 22 1.3Excel选项卡：自动保存/设置密码 23 1.4 窗口操作技巧：窗口共同查看 24 肝脏NK细胞 两个或多个工作簿的数据内容，如何同时查看？ 并排查看/全部重排： 视图→窗口→并排查看/全部重排 LIVER NK CELL 1.4 窗口操作技巧：窗口拆分 选中单元格→视图→拆分 一个工作表中（sheet）大量的数据，同时查看？ 窗口拆分： 视图→窗口→拆分 双击拆分条线去除该单条拆分条线 拖拽拆分条线移动该拆分条线位置 25 1.4 窗口操作技巧：窗口拆分 窗 口 拆 分 26 1.4 窗口操作技巧：冻结窗格 一个工作表中（sheet）大量的数据，往下滚动滑块 后看不到标题行？ 窗口冻结：冻结行、冻结列、行列冻结（鼠标选中需 要冻结的行列的下一行列交叉的单元格） 视图→窗口→冻结窗格 27 大家练习一下冻结窗格的使用 3min 1.5 工作表页面布局设置与打印 一个工作表中（sheet）大量的数据，如何使打印的每一页都 有表头标题？ 添加打印标题： 页面布局→页面设置→打印标题→顶端标题行→选择打印标题 30 1.5 工作表页面布局设置与打印 打印好多页面后，打乱后如何快速整理好文件？ 添加页眉页脚： 页面布局→页面设置→页眉页脚 32 1.5 工作表页面布局设置与打印 如何设置，不打印单元格底纹颜色？ 工作表设置： 页面布局→工作表选项→工作表→单色打印 34 35 1.5 工作表页面布局设置与打印 打印Excel表格时，列太多，一页放不下，两页又不方便阅读， 如何设置？ 分页预览： 视图→工作簿视图→分页预览 调整虚线使得整张表格在一页。 原理：表格打印比例缩小，即文字缩小了。 36 Excel的 基本知识 • 1、工作簿/工作表/单元格； • 2、Excel的工作环境 • 3、Excel选项卡：自动保存/设置密码； • 4、窗口操作技巧：窗口查看/窗口拆分/冻结窗格； • 5、工作表页面布局设置与打印:添加表头/添加页 码/纯色打印/打印内容在一页 02 数据录入技巧 数据 录入技巧 • 1、数据的录入技巧： 一般录入 编号、序列（自定义序列）录入 快速填充 单元格内换行 • 2、数据验证： 数值限制 / 下拉列表填充 2.1 Excel数据的录入——一般录入 41 1、数据输入的一般过程： 选定要输入数据的单元格，从键盘上输入数据，按Enter键 2、输入数值： 正数的输入: 567, 1.20E+03（Excel中默认数字在超过11位后，科学计数法显示; Excel单元格中默认的数值有效位数是15位 ,超过15位的部分会自动变成0） 负数的输入: -666或 (666) 分数的输入:零+空格+分数，例如：0 2/3 ；也可以设置单元格格式为“分数” 货币数据的输入: ¥12，$21 ；“设置单元格格式”—“货币” 3、输入文本 字符文本应逐字输入 文本格式数字(邮编、电话、身份证等) :单引号+录入内容；或 用=“数字”方式 输入，如：‘100875 ，=“100875”；或“设置单元格格式”一“ 文本” 2.1 Excel数据的录入——一般录入 42 4、输入日期和时间. 日期:年、月、日之间要用“/”号或“-"号隔开， 如2002-8-16， 2002/8/16 时间:时、分、秒之间要用冒号隔开，如10:20:40；同时输入日期和时间， 日期和时间之间应该用空格隔开 5、输入公式 公式要以等号 = 开始 如=1+2*3 6、灵活使用TAB、ENTER键输入数据 Tab: 输入数据单元格间平移 Enter: 输入数据单元格移至起始输入单元格下方 43 练习: 输入18位身份证号 单引号+录入内容， ‘100875 ； 用=“数字”方式输入，="100875” “设置单元格格式”一“ 文本” 2.1 Excel数据的录入——编号序列（自定义序列）录入 44 2 编号的输入 1、连续编号的输入 连续编号 (或等差、等比性质的数据) 应该采用复制或序列 填充的方式进行输入。如:学生学号、日期等。 1) 在A1中输入2009001 在A2中输入2009002 2) 选中A1:A2 3) 等差：左键向下拖动填充柄 等比：右键拖动-选择等比 + + 45 等 差 等 比 序 列 录 入 自 定 义 序 列 的 录 入 2.1 Excel数据的录入——快速填充（智能填充） “快速填充”是Excel2013中的新增功能，弥补了之前版本中单元格填充 时只能复制数据和按照一定的序列规律自动扩展数据的不足，实现了以 前需要借助公式才能实现的字符串处理功能： • 将某列数据中的数据拆分成几个数据块组成部分，并分别填入到指定 的单元格中； • 将某几列数据按设定的方式合并后分别填入指定的区域中。 注意：“快速填充”必须是在连续的数据区域中才能使用，而且不适用 于横向填充。 46 2.1 Excel数据的录入——快速填充（智能填充） • 快速填充的方式： 先输入样式 1，鼠标左键下拉，然后选择快速填充， 2，快捷键：CTRL+E； 3，功能区按钮，开始-编辑-填充 • 快速填充的作用： 按固定长度拆分； 按符号拆分； 按日期拆分； 按固定长度合并，按符号合并，按日期合并 47 48 快 速 填 充 ： 拆 分 与 合 并 2.1 Excel数据的录入——单元格内换行 3 单元格内数据多行显示 自动换行(随列宽调整) Alt+Enter →强制换行 1、2行直接输入 3设置为自动换行 4强制换行 49 2.2 数据验证——数值限制 50 ② 对 录 入 的 数 据 范 围 有 限 制 ③ ① ④ ⑤ 2.2 数据验证——下拉列表填充 51 ② 以 下 拉 列 表 的 方 式 录 入 数 据 ③ ① ④ ⑤ 52 ②练习: 从下拉列表中选择输入“职称”：教授、副 教授、讲师 数据 录入技巧 • 1、数据的录入技巧： 一般录入 编号、序列（自定义序列）录入 快速填充 单元格内换行 • 2、数据验证： 数值限制 / 下拉列表填充 03 数据处理与分析 数据 处理与分析 • 1、查找与替换； • 2、选择性粘贴； • 3、数据拆分-分列； • 4、筛选：筛选特定内容/颜色筛 选/自定义筛选条件； • 5、数据合并、分类汇总 数据处理与分析——查找与替换 利用“选项”按钮 （巧用单元格匹配功能） 56 57 查 找 与 替 换 3 种 情 况 演 示 数据处理与分析——选择性粘贴 选择性粘贴 复制后，选中要粘贴的单元格， 右键，选择性粘贴 58 59 选 择 性 粘 贴 ： 数据处理与分析——数据分列 60 61 分 列 功 能 演 示 数据处理与分析——筛选 如何筛选出张老师上课的信息？ 62 63 筛 选 功 能 演 示 数据处理与分析——合并与分类汇总 合并 合并计算：以某一字段为基准， 将其对应的其他字段进行函 数计算(求和、平均、最大、最小） 分类汇总 数据整理；分类汇总操作； 64 65 66 04 Excel常用的函数公式 68 Excel函数与公式 公式是对工作表中的数据进行计算的等式，它以等号（=）开头，可以对数据进行加 减乘除和比较运算。 函数是Excel中内置好的一些公式，只要往公式中填写参数即可。能处理、分析、汇 总数据。 语法结构 等号 参数 函数名称 =SUM（A1:B3）,B4:D5,4,23） 括号 69 Excel函数与公式 序号 函数类型 为了满足各种数据处理的要求， Excel提供了大量函数供用户使用，函 数是系统预先编制好的用于数值计算和 数据处理的公式，使用函数可以简化或 缩短工作表中的公式，使数据处理简单 方便。 1 财务函数 2 日期和时间函数 3 数学和三角函数 4 统计函数 5 查找和引用函数 6 7 8 9 10 数据库函数 文本函数 逻辑函数 信息函数 工程函数 11 多维数据集函数 12 13 兼容性函数 Web函数 Excel函数与公式 70 常用的函数： sum（求和）；average（平均值）；count（计数）；max/min（最大/小值）； text（文本）；len（文本字符串中的字符数）；date（日期）；left/right（最左/ 右边的字符）;mid（中间的字符）； if（逻辑检测）；vlookup（查找和引用）； proper（首字母转换成大写） Excel文本函数 71 72 TEXT 函数 Len 函数 Excel文本函数 73 74 PROPER 函数 Excel查找和引用函数 75 76 VLOOKU P函数 绝对引 用（查 找范围 不变化） $B$48:$ H$56 77 VLOOKU P函数 查找身 份证号 码 78 IF函数 MOD函数 MID函数 IF(MOD(MID(D60,17,1),2)=0,"女","男") 79 05 Excel图表 图表的使 用技巧 • 1、图表 • 2、迷你图 Excel绘图（表）与数据处理——创建图表 图表：将数据以图的形式展现出来 82 Excel绘图（表）与数据处理——图表编辑 1.修改图表：数据源修改;图表类型修改； 2.图表格式设置：布局，颜色，样式等等； 案例 83 84 Excel绘图（表）与数据处理——迷你图 85 86 06 数据透视表的应用 数据透视 表的应用 •1、概述 •2、数据透视表与数据 透视图的创建与操作 89 6.1 概述 1.数据透视表： 集排序、筛选、组合、汇总等多个功能于一体，是一一种交互式 的报表，可以多角度灵活汇总分析数据；熟练掌握该功能可显著提高 工作效率； 2.数据透视图： 数据透视表的图形显示效果。 两种方法：直接通过数据表中的数据创建数据透视图 通过已有数据透视表创建数据透视图 6.2 数据透视表的创建与操作 准备数据源: Excel数据清单 格式要求： 规范的行列构成的二维表 原始数据表必须有列标题设置 不存在合并单元格 不存在空格、空行、空列 90 6.2 数据透视表的创建与操作 操作步骤: 单击数据中的任意位置; 在“插入”选项卡上的“表格”组中，单击“数据透视 表”，选择“数据透视表”; 在对话框中: 选择数据“表或区域”及放置数据透视表的位置; 单击“确定”。Excel将空的数据透视表添加至指定位置并 显示数据透视表字段列表 指定数据透视表框架中四个区域内的数据内容 91 6.2 数据透视表的创建与操作 案例一：统计下 表中不同文献类 型在不同年份的 发文量 92 6.2 数据透视表的创建与操作 案例一： 值区域 93 6.2 数据透视表的创建与操作 94 6.2 数据透视表的创建与操作 ---调出数据透视表字段 95 96 ③ 练习: 统计每个班数学、语文、英语、政治的平均分数 6.2 数据透视表的创建与操作---练习 视 频 97 6.2 数据透视表的创建与操作 案例二：按照 学院统计不同 职称发表的论 文平均值 98 6.2 数据透视表的创建与操作 视 频 99 6.2 数据透视表的创建与操作—筛选器 视 频 100 6.3 数据透视图的创建与操作 视 频 101 102 1、Excel的基本知识 •1、工作簿/工作表/单元格；2、Excel选项卡：自动保存/设置密码；3、窗口操作技巧：冻结窗格；4、工作表页面布 局设置与打印 2、数据录入技巧 •1、数据的录入技巧；一般录入/编号、序列（自定义序列）录入/快速填充/单元格内换行 •2、数据验证：数值限制/下拉列表填充 3、数据处理与分析 •1、查找与替换；2、选择性粘贴；3、数据拆分 分列；4、筛选：筛选特定内容/颜色筛选/自定义筛选条件；5、数 据合并、分类汇总 4、运用函数处理数据 •text（文本）；len（文本字符串中的字符数）；vlookup（查找和引用）；proper（首字母转换成大写） 5、图表的使用技巧 •1、图表；2、迷你图 6、数据透视表的应用 •1、概述 •2、数据透视表与数据透视图的创建与操作 THANKS