Excel基本应用及公式计算.doc

Excel基本应用及公式计算目 录一、表格的建立方式二、共享表格的应用三、多个部门同时编辑一张表格四、超链接的应用五、文件的插入六、工作组的应用七、重排窗口的应用三、如何在不同的单元格内加上、或乘以、除以同一数值四、不连续的单元格内输入同一内容五、筛选的应用六、下拉菜单的应用七、工作表的隐藏六、求和函数七、AVERAGE函数八、CONCATENATE函数九、TEXT函数十、VALUE函数十一、ROUND函数十一、Excel常用快捷方式 一 Excel表格如何设定部分单元格可编辑，部分单元格锁定加密，且整表不可复制粘贴一、 表格的建立方式方法一：Microsoft office 直接建立 1、 单击START，进入程序（p）、Microsoft office，打开Microsoft office Excel即得到一张新表格。2、表格特点：全新表格。2、 适用于全新建立表格方法二、Ctrl+C方式1、找到拟复印的工作表，按下Ctrl+C，打开需建立工作表的存放地址，按下Ctrl+V，重新命名表格即可。2、表格特点：保留所有原表格数据、计算公式及格式。3、适用于同类表格复制。方法三、1、找到拟复制的工作表，单击鼠标右键，按下复制。打开需建立工作表的存放地址，单击鼠标右键，按下粘贴，重新命名表格即可。2、表格特点：保留所有原表格数据、计算公式及格式。3、适用于同类表格复制。方法四：ctrl直接拖动方式1、找到拟复制的工作表，ctrl+鼠标左键拖动至存放地址，重新命名表格即可。2、表格特点：保留所有原表格数据、计算公式及格式。3、适用于同类表格复制。方法五：1、 打开工作表，选中拟复制的表格，按ctrl可选择多张表格，单击鼠标左键，选择移动或复制表格，建立副本打，选择存放地址即可。2、表格特点：保留所有原表格数据、计算公式及格式。3、适用于同类表格复制，引用单独表格使用。方法六：1、打开工作表，全部选中表格内容，复制、粘贴2、表格特点：保留所有原表格数据、计算公式，但不保留格式。3、适用于原表格存在查找不易的错误，导致无法进行工作时采用，建议一般不采用此种方式建立表格。 返回目录二、共享表格的应用1、同一局域网下，可将文件设为共享，按照不同的分权可察看、修改、保存等。单击鼠标右键，打开属性，选择安全-添加,输入共享人员名称，设立权限。2、适用：由不同部门或员工协同完成同一工作时，最终成果需汇总、分析使用，特别适用于总包及重大工程评标工作。3、注意事项：权限的划分，不能多人同时对同一张表格编辑。 返回目录三、多部门同时编辑同一张表格1、首先打开一个Excel工作簿，选择菜单栏中的“工具”“共享工作簿”命令；接着在出现的“共享工作簿”对话框中，选择“编辑”选项卡，然后选中“允许多用户同时编辑.”选项并单击“确定”按钮。2、最后选择菜单中的“文件”“另存为”命令，将文件共享于局域网中即可。经过上述处理后，这个Excel工作簿可以同时被256个不同人员进行浏览或编辑。选项前需“” 返回目录四、超链接的应用1、建立方式：A、插入-超链接B、Ctrl+K2、注意事项：选定书签的位置3、批量取消超链接方法方法一、宏代码去除法打开该Excel文件，切换到“视图”选项卡，点击“宏”“录制宏”，出现“录制新宏”窗口，在“宏名”定义一个名称为：RemoveHyperlinks(图2)，点击“确定”退出;再点击“宏”“查看宏”，选择“宏名”下的“RemoveHyperlinks”并点击“编辑”，打开“Microsoft Visual Basic”编辑器，用如下内容替换右侧窗口中的所有代码(图3)，然后保存关闭VBA编辑器：Sub RemoveHyperlinks()Remove all hyperlinks from the active sheetActiveSheet.Hyperlinks.DeleteEnd Sub再点击“宏”“查看宏”，选择“宏名”下的“RemoveHyperlinks”并点击“执行”即可去除该工作表的链接。用以下代码也可以达到相同的目的：Sub ZapHyperlinks()Cells.Hyperlinks.DeleteEnd Sub方法二、选择性粘贴法右击含有超链接的列并选择“复制”，接着在该列(左)右边插入一空白列，然后右击该空白列，选择“选择性粘贴”(图4)，在随后出现的“选择性粘贴”窗口中，点选“数值”选项(细心的人会发现，当选择“数值”等选项时，“粘贴链接”按钮就变成灰色不可用状态，图5，自然也就不会进行超链接的粘贴)，最后保留该列，再删除原先含有超链接的列即可。五、文件的插入1、建立：插入-对象，根据插入文件来源选择“新建”、“由文件创建”，根据需要选择“链接到文件”或“显示为图标”，若选择“显示为图标”，可重新命名，按确定即可。六、工作组的操作1、全部选定工作表：单击鼠标右键，选定“选定全部工作表”即可；2、选定部分工作表：Ctrl+拟选用表格即可3、用途：同一操作适用于所有选定工作表。 返回目录七、重排窗口1、建立：窗口-并排比较或重排窗口2、应用：多表格比较时用八、如何在不同的单元格内加上或乘以、除以同一个数值方法一：利用公式一一加上；方法二：录入需增加的数值，复制此数值。选择需录入的单元格，单击鼠标右键，选择性粘贴，选择运算符号，确定即可。九、不连续的单元格内输入同一内容首先Ctrl键逐一选取准备输入的单元格，然后在最后一个单元格键入要输入的数字或者其他字符，确定的时候用Ctrl + Enter的组合键即可。十、筛选的应用1、建立：数据-筛选-行动筛选或高级筛选2、不能出现空格行3、快速查找数据十一、下拉菜单的应用1、 建立：选中要做下拉列表的单元格。在菜单中选择：数据有效性设置（允许）：序列；（来源）：输入内容，用,（逗号）分隔。 2、注意事项：来源的录入必须是英文状态3、应用：自定义选择输入内容十二、工作表的隐藏1、 建立：选中要做下拉列表的单元格。在菜单中选择：格式工作表隐藏 取消：格式工作表取消隐藏十三、求和函数1：SUM函数 函数名称：SUM 主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Number2） 参数说明：Number1、Number2代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。特别提醒：如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式修改为：=SUM(LARGE(D2:D63,1,2,3,4,5)，则可以求出前5名成绩的和。 2、SUMIF函数 函数名称：SUMIF 主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。 应用举例：特别提醒： “Criteria”如果是文本型的，需要放在英文状态下的双引号（）中。十四、AVERAGE函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,) 参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域），参数不超过30个。 应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。 十五、CONCATENATE函数 函数名称：CONCATENATE 主要功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。 使用格式：CONCATENATE(Text1，Text) 参数说明：Text1、Text2为需要连接的字符文本或引用的单元格。 应用举例：在C14单元格中输入公式：=CONCATENATE(A14,B14,.com)，确认后，即可将A14单元格中字符、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。 特别提醒：如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：=A14B14.com，也能达到相同的目的。十六、VALUE函数 函数名称：VALUE 主要功能：将一个代表数值的文本型字符串转换为数值型。 使用格式：VALUE(text) 参数说明：text代表需要转换文本型字符串数值。 应用举例：广联达倒出数据转换时应用。如果B74单元格中是通过LEFT等函数截取的文本型字符串，我们在C74单元格中输入公式：=VALUE(B74)，确认后，即可将其转换为数值型。 特别提醒：如果文本型数值不经过上述转换，在用函数处理这些数值时，常常返回错误。 十七、TEXT函数 函数名称：TEXT 主要功能：根据指定的数值格式将相应的数字转换为文本形式。 使用格式：TEXT(value,format_text) 参数说明：value代表需要转换的数值或引用的单元格；format_text为指定文字形式的数字格式。 应用举例：如果B68单元格中保存有数值1280.45，我们在C68单元格中输入公式：=TEXT(B68, $0.00)，确认后显示为“$1280.45”。 特别提醒：format_text参数可以根据“单元格格式”对话框“数字”标签中的类型进行确定。十八、ROUND函数函数名称：ROUND 主要功能：返回按指定位数进行四舍五入的数值。 使用格式：Round(expression,numdecimalplaces)参数说明：参数 Expression 必选项。数值表达式被四舍五入。 Numdecimalplaces 可选项。数字表明小数点右边有多少位进行四舍五入。如果省略，则Round函数返回整数。应用举例：DimMyVar,pipi=3.14159MyVar=Round(pi,2)MyVarcontains3.14。十九、常用快捷键“常规”数字格式：Ctrl+Shift+带两位小数位的“货币”格式：Ctrl+Shift+$不带小数位的“货币”格式：Ctrl+Shift+%带两位小数位的“科学记数”数字格式：Ctrl+Shift+年月日“日期”格式：Ctrl+Shift+#小时和分钟“时间”格式，并标明上午或下午：Ctrl+Shift+千位分隔符且负数用负号（-）：Ctrl+Shift+！应用外边框：Ctrl+Shift+删除外边框：Ctrl+Shift+-应用或取消字体加粗格式：Ctrl+B应用或取消字体倾斜格式：Ctrl+I应用或取消字体下划线格式：Ctrl+U应用或取消字体下划线格式：Ctrl+5隐藏行：Ctrl+9取消隐藏行：Ctrl+ Shift+（左括号隐藏列：Ctrl+0取消隐藏行：Ctrl+ Shift+）右括号二十、用于编辑数据的快捷键编辑活动单元格并将插入点放置到线条末尾：F2取消单元格或编辑栏中的输入项：Esc编辑活动单元格并清除其中原有的内容：Backspace将定义的名称粘贴到公式中：F3完成单元格的输入：Enter将公式作为数组公式输入：Ctrl+Shift+Enter常用数组的一些函数：1.if函数。形式是：if(A1:H1=A3:H3,A5:H5,A7:H7)，其运算原理是，A1:H1=A3:H3返回逻辑数组。TRUE时，选A5:H5中对应的数，FALSE时，选A7:H7中对应的数。2.match函数。形式是：match(1,2,3,1,5,6,2,3,8,0)，返回结果：1,4,5，即数组1,2,3在1,5,6,2,3,8中的对应位置。3.sum函数。一般和其他函数如IF结合，将其返回的数组结果加总。如sum(match(1,2,3,1,5,6,2,3,8,0)，返回结果10，即1+4+5。4.countif函数。形式是：countif(A5:A9, A1:A2)，返回A1:A2在A5:A9中出现的次数的数组结果在公式中键入函数名之后，显示公式选项板：Ctrl+A在公式中键入函数名后为该函数插入量名和括号：Ctrl+Shift+A显示“拼写检查”对话框：F7二十一、插入、删除和复制选中区域的快捷键复制选定区域：Ctrl+C剪切选定区域：Ctrl+X粘贴选定区域：Ctrl+V清除选定区域的内容：Delete撤消最后一次操作：Ctrl+Z插入空白单元格：Ctrl+Shift+加号二十二、插入、删除和复制选中区域的快捷键在选中区域内移动在选定区域内由上往下移动： Enter在选定区域内由下往上移动：Shift+Enter在选定区域内由左往右移动：Tab在选定区域内由右往左移动：Shift+Tab按顺时针方向移动到选定区域的下一个角：Ctrl+PERIOD右移到非相邻的选定区域：Ctrl+Alt+右头键箭左移到非相邻的选定区域：Ctrl+Alt+左头键箭二十三、选择单元格、列或行的快捷键选定当前单元格周围的区域：Ctrl+Shift+*将选定区域扩展一个单元格宽度：Shift+箭头选定区域扩展到单元格同行同列的最后非空单元格：Ctrl+Shift+箭头将选定区域扩展到行首：Shift+Home将选定区域扩展到工作表的开始：Ctrl+Shift+Home将选定区域扩展到工作表的最后一个使用的单元格：Ctrl+Shift+End选定整列：Ctrl+空格键选定整行：Shift+空格键选定整个工作表：Ctrl +A如果选定了多个单元格则只选定其中的单元格：Shift+Backspace将选定区域向下扩展一屏：Shift+Pack Down将选定的区域向上扩展一屏：Shift+Pack Up选定工作表上的所有对象：Ctrl+Shift+空格键在隐藏对象、显示对象与对象占位符之间切换：Ctrl+6显示对象或隐藏“常用”工具栏：Ctrl+7使用剪头键启动扩展选中区域的功能：F8将其它区域中单元格添加到选中区域中：Shift+F8二十四、用于工作簿和工作表的按键1、预览和打印Ctrl+P或Ctrl+Shift+F12：显示“打印”对话框在打印预览中使用下列按键（若要定位至打印预览，按“Alt+F”，再按V）：箭头键：放大显示时，在页面中移动Page Up 或Page Down：缩小显示时，每次滚动一页Ctrl+向上键或Ctrl+向左键：缩小显示时，滚动第一页Ctrl+向上键或Ctrl+向右键：缩小显示时，滚动最后一页2、处理工作表Shift+F11或Alt+Shift+F1：插入新工作表Ctrl+Page Down：移动到工作簿中的下一张工作表Ctrl+Page Up：移动到工作簿中的上一张工作表Shift+Ctrl+Page Down：选定当前工作表和下一张工作表。若要取消选定多张工作表，按“Ctrl+Page Down”，若要选定其他工作表，按“Ctrl+Page Down”。Shift+Ctrl+Page Up ：选定当前工作表和上一张工作表Alt +O H R ：对当前工作表重命名Alt+E M：移动或复制当前工作表Alt+E L：删除当前工作表3、在工作表内移动各滚动箭头键：向上、下、左或右移动一下单元格；Ctrl+箭头键：移动到当前数据区域的边缘。Home：移动到行首。Ctrl+Home：移动到工作表的开头Ctrl+End：移动到工作表的最后一个单元格，该单元格位于数据后占用的最右列的最下行中。Page Down：向下移动一屏Page Up：向上移动一屏Alt+Page Down：向右移动一屏Alt +Page Up：向左移动一屏F6：切换到被拆分的工作表中的下一个窗格Shift+F6：切换到被拆分的工作表中的上一个窗格Ctrl+Backspace：滚动以显示活动单元格。Shift+F5：显示“查找”对话框Shift+F4：重复上一次“查找”对话框。Tab：在受保护的工作表上的非锁定单元格之间移动。二十五、用于输入、编辑、设置格式和计算数据的按键1、输入数据Enter：在完成单元格输入并选取下一个单元格。Alt+Enter：在单元格中换行（？）Ctrl+Enter：用于当前输入项填充选定的单元格区域shift+Enter：完成单元格输入并向上选取上一个单元格。Tab：完成单元格输入并向右选取下一个单元格。Shift+Tab：完成单元格输入并向左选取上一个单元格。Esc：取消单元格输入。箭头键：向上、下、左或右移动一下单元格Home：移到行首F4或Ctrl+Y：重复上一次操作。Ctrl+shift+F3：由行列标志创建名称。Ctrl+D：向下填充。Ctrl+R：向右填充。Ctrl+F3：定义名称。Ctrl+K：插入超链接。Ente（在具有超链接的单元格中 ）：激活超链接。Ctrl+；(分号)：输入日期Ctrl+：(冒号)：输入时间Alt+向下键：显示清单的当前列中的数值下拉列表。Ctrl+Z：撤消上一次操作。2、输入特殊字符按F2可编辑单元格，打开Num Lock，再用数字小键盘按下列键：Alt+0162：输入分币字符：Alt+0163：输入英镑字符：Alt+0165：输入日圆符号：Alt+0128：输入欧元符号：3、输入并计算公式=（等号）：键入公式。F2：关闭了单元格的编辑状态后，将插入点移动到编辑栏内。Backspace：在编辑栏内，向左删除一个字符。Enter：在单元格或编辑栏中完成单元格输入。Ctrl+Shift+ Enter：将公式作为数组公式输入。Esc：取消单元格或编辑栏中的输入。Shift+F3：在公式中，显示“插入函数”对话框Ctrl+A：当插入点位于公式中公式名称的右侧时，显示“函数参数”对话框Ctrl+Shift+A：当插入点位于公式中函数名称的右侧时，插入参数名和括号。F3：将定义的名称粘贴到公式中Alt+=（等号）：用SUM函数插入“自动求和”公式。Ctrl+Shift+”（双引号）：将活动单元格上方单元格中的数值复制到当前单元格或编辑栏。Ctrl+（撇号）：将活动单元格上方单元格中的公式复制到当前单元格或编辑栏。Ctrl+（左单引号）：在显示单元格值和显示公式之间切换。F9：计算所有打开的工作簿中的所有工作表。如果选定了一部分公式，则计算选定部分。然后按Enter可用计算出的值替换选定部分。4、插入、删除和复制单元格Ctrl+C：复制选定的单元格Ctrl+X：剪切选定的单元格Ctrl+V粘贴复制的单元格Delete：清除单元格的内容Ctrl+连字符：删除选定的单元格Ctrl+Shift+加号：插入空白单元格 5、设置数据的格式Alt+ （撇号）：显示“样式”对话框。