Excel2010高级应用教程.ppt

Excel高级应用数据输入 一 数据显示显示方式与调整单元格宽度 Excel会自动判断使用者输入的资料型态 来决定资料的预设显示方式 例如数字资料将会靠右对齐 文字资料则会考左对齐 若输入的资料超过单元格宽度时 Excel将会改变资料的显示方式 当单元格宽度不足显示内容时 数字资料会显示成 而文字资料则会由右边相邻的储存格决定如何显示 当右邻单元格有内容时 文字资料会被截断 当右邻单元格是空白时 文字资料会跨越到右边 单元格太窄 数字会显示成 字 当右邻单元格有内容时 文字资料会被截断 当右邻单元格是空白时 文字资料会跨越到右边 当右邻单元格有内容时 文字资料会被截断 当右邻单元格是空白时 文字资料会跨越到右边 当右邻单元格有内容时 文字资料会被截断 当右邻单元格是空白时 文字资料会跨越到右边 这时候只要调整单元格的宽度或在标题栏的右框线上双击鼠标左键就可以调整宽度 调整高度的方法和调整宽度的方法一样 只要拉曳行下方的框线就可以了 二 选取不连续的多个范围如果要选取多个不连续的单元格范围 如B2 D2 A3 A5 先选取B2 D2范围 然后按住Ctrl键 再选取第2个范围A3 A5 选好后再放开Ctrl键 就可以同时选取多个单元格范围了 先选取B2 D2范围 按住Ctrl键后 再接着选取A3 A5 三 选取整行或整列要选取整行或整列 在行编号或列编号上按一下 在此按一下 选取整个B列 在此按一下 选取整个第4列 四 选取整张工作表若要选取整张工作表 按下左上角的 全选按钮 即可一次选取所有的单元格 按下 全选按钮 可选取整张工作表 五 快速输入出现过的资料 1 利用 自动完成 输入相同的资料在输入同一列的资料时 若内容有重复 就可以透过 自动完成 功能快速输入 例如在工作表3的B2到B4单元格输入内容 然后再B5单元格中输入 成 字 此时 成 之后自动填入与B3单元格相同的文字 并以反白额方式显示 若自动填入的资料正好是想输入的文字 按下Enter键就可以将资料存入单元格中 若不是想要的内容 可以不予理会 继续完成输入文字的工作 自动完成 功能 只适用于文字资料 此外 当同栏中出现两个以上单元格数据雷同时 Excel在无从判定内容的情况下 将暂时无法使用自动完成功能 当你继续输入至Excel可判断的内容 才会显示自动完成的文字内容 一直要输入到 风 字 才会发挥 自动完成 的功能 2 从下拉式清单挑选输入过的资料当同一栏单元格的数据太雷同 总是得输入到最后自动完成才能判断出正确的内容 这时你可以改用从下拉式列表挑选功能来输入 接续上例 我们要继续在C6单元格输入内容 请先如图输入C2 C5的内容 然后在C6储存格按右钮执行 从下拉式清单挑选 命令 从下拉式列表挑选功能与自动完成功能一样 都只适用于文字数据 执行此命令 C6单元格下方会出现一张列表 记录同一栏 即C栏 中出现过的数据 如 品项 超静音风扇 超静音立扇 只要由清单中选取就可输入数据了 哪些资料会列入下拉式清单中Excel会从选取的单元格往上 往下寻找 只要找到的单元格内有资料 就把它放到清单中 直到遇到空白单元格为止 以下图的例子而言 当选区A6单元格时 清单中会列出 财务部 人事部 行销部 与 业务部 这4个项目 而不会出现 生产部 资讯部 空白单元格 空白单元格 不再往上找 不再往下找 选取的单元格 3 快速填满相同的资料 当你要将相同的内容连续填入数个单元格 除了可复制 贴上单元格内容外 更有效率的方法是利用自勤填满功能来达成 此功能亦可建立日期 数列等具规则性的内容 这部份我们将在下一节说明 假设我们想建立以下的数据内容 先来看看哪些可利用自动填满功能来快速输入 可建立具规律变化的资料 详细请参考下一节 可为连续的储存格填入相同的数据 请开敔一份新的工作簿 首先练习操作步骤较单纯的填满功能 看看如何在B2 B6范围内填满 业务部 先如图输入内容 然后在B2单元格输入 业务部 3个字 并维持单元格的选取状态 然后将指标移至粗框线的右下角 此时指标会呈 状 选取某个单元格或范围时 其周围会被粗框线围住 右下角的小方块稻为填满控点 将指标移到填满控点上 按住左钮不放向下拉至单元格B6 业务部 2个字就会填满B2 B6范围了 指标旁出现工具提示 显示单元格将填入的资料 自动填满选项钮 在刚才的操作中 数据自动填满B2 B6单元格后 B6单元格旁出现了一个自动填满选项钮 按下此钮可显示下拉选单 让你变更自动填满的方式 预设的填满方式 即填入单元格的内容与格式 如字型 颜色设定 若改选此项 只会填入单元格格式 选此项表示只填入单元格内容 但不要填入格式 自动填满选项钮的作用 可让我们选择是否要一并套用单元格的格式设定 不过 我们尚未设定格式 所以不用变更设定 当你选取了其他单元格 并进行编辑动作后 自动填满选项钮就会自动消失 学会单元格的格式设定后 您就会感受到此钮的便利之处了 4 自动建立等差 日期及等比数列 1 建立等差数列 首先要说明数列的类型 以便清楚知道哪些数列可由Excel的功能来自动建立 Exccl可建立的数列类型有4种 等差级数 数列中相邻两数字的差相等 例如 1 3 5 7 等比级数 数列中相邻两数字的比值相等 例如 2 4 8 16 日期 例如 2012 12 5 2012 12 6 2012 12 7 自动填入 自动填入数列是属于不可计算的文字数据 例如 一月 二月 三月 星期一 星期二 星期三 等都是 Excel已将这类型文字数据建立成数据库 让我们使用自动填入数列时 就像使用一般数列一样 以下就分别说明如何建立以上这4种数列 建立等差数列在下图的A2 A6单元格中 建立1 2 3 4 5的等差数列 操作如下 在A2 A3单元格分别输入1 2 并选取A2 A3范围做为来源单元格 也就是要有两个初始值 如1 2 这样Excel才能判断等差数列的间距值是多少 来源储存格 将指针移到填满控点上 按住鼠标左钮不放 向下拉曳至A6单元格 指针旁的数字表示目前到达的储存格将填入的值 参考指针旁的数字 当建立到你要的数字后请放开滑鼠 等差数列就建立好了 建立数列后 自动填满选项钮又再度出现了 而且下拉选单中的第1个项目略有不同 以复制数据的方式来填满 就上例而言 A2 A6范围会变成1 2 1 2 1 此外 利用填满控点还可以建立 项目1 专案3 专案5 专案7 的文数字组合数列 只要在来源单元格输入 项目1 专案3 再拉曳填满控点即可完成 你可以自行试试看 2 建立日期数列 再来试试建立日期的功能 请开启一份新工作簿 假设我们要在Al El建立一个日期数列 就可以在来源Al单元格中输入起始日期 然后拉曳填满控点至El单元格 1 输入起始日期 2 拉曳填满控点至El儲存格即可建立 由于我们建立的是日期数列 所以当你按下自动填满选项钮熙时 会看到多出几个与日期有关的选项 若改选此项 建立的日期数列会跳过星期六 日 此为预设选项 选此项 A1 El会填入 2012 1 1 2012 2 1 2012 5 1 3 建立文字数列 Excel还内建许多常用的自动填人数列 例如 甲 乙 丙 丁 一月 二月 三月 等 方便我们使用填满控点快速完成输入 如右图操作 我们来练习在A3 Al0建立 甲 乙 辛 拉曳至A10 建立不同的自动填入数列 自动填满选项钮下拉选单中的项目也会随数列的特性而有所不同 在A3输入 甲 4 建立等比数列 不用按计算器 就能轻松建立等比数列 等比数列无法像等差数列以拉曳填满控点的方式来建立 我们实际来操作看看 例如要在B3 Bl0建立5 25 125 的等比数列 在B3单元格中输入5 按下圈图键 接着选取B3 Bl0的范围 切换到开始页次 按下编辑区的填满钮 由下拉选单中选择 数列 命令开启数列交谈窗 1 选择此项 因为根据步骤1选取的范围 数列会产生在列 2 选择要建立的数列类型 此栏可设定数列最后结束的数字 若未设定将延伸到选取范围为止 此例为Bl0单元格 3 输入等比的数值 按下确定钮 等比数列就建立完成了 Excel高级应用公式与函数 3 1在单元格建立公式 当我们需要将工作表中的数字数据做加 减 乘 除 等运算时 可以把计算的动作交给Excel的公式去做 省去自行运算的工夫 而且当数据有变动时 公式计算的结果还会立即更新 3 1 1公式的表示法 Excel的公式和一般数学公式差不多 数学公式的表示法为 若将这个公式改用Excel表示 则变成要在A3单元格中输入 意思是Excel会将Al单元格的值十A2单元格的值 然后把结果显示在A3单元格中 3 1 2输入公式 输入公式必须以等号 起首 例如 Al A2 这样Excel才知道我们输入的是公式 而不是一般的文字数据 现在我们就来练习建立公式 请开殷范例文件Ch03 01 我们已在其中输入了两个学生的成绩 我们打算在E2单元格存放 王书桓的各科总分 也就是要将 王书桓 的英文 生物 理化分数加总起来 放到E2单元格中 因此将E2单元格的公式设计为 B2十C2 D2 请选定要输入公式的E2单元格 并将指针移到数据编辑列中输入等号 函数方块 在此输入 接着输入 之后的公式 请在单元格B2上单击 Excel便会将B2输入到数据编辑列中 此时B2被虚线框包围住 B2自动输入到公式中 再输入 然后选取C2单元格 继续输入 选取D2单元格 如此公式的内容便输入完成了 最后按下数据编辑列上的翰入钮或按下键 公式计算的结果马上显示在E2单元格中 数据编辑列会显示公式 单元格显示公式计算的结果 Tip 若想直接在单元格中查看公式 可按下 键键在键的上方 在公式和计算结果间做切换 你也可以直接在E2储存格中 以键盘直接输入 82十C2十D2 再按下Enter键来输入公式 3 1 3自动更新结果 公式的计算结果会随着单元格内容的变动而自动更新 以上例来说 假设当公式建好以后 才发现 王书桓 的英文成绩打错了 应该是 90 分才对 当我们将单元格B2的值改成 90 E2单元格中的计算结果立即从220更新为225 自动更新计算结果了 3 2相对参照位址与绝对参照位址 3 2 1相对与绝对参照的差异 公式中会运用到的地址有相对引用地址与绝对参照地址2种类型 相对引用地址的表示法例如 Bl C4 而绝对参照地址的表示法 则须在单元格地址前面加上 符号 例如 B l C 4 假设你要前往某地 但不知道该怎么走 于是就向路人打听 结果得知你现在的位置往前走 碰到第一个红绿灯后右转 再直走约100公尺就到了 这就是相对引用地址的概念 另外有人干脆将实际地址告诉你 假设为 中正路二段60号 这就是绝对参照地址的概念 由于地址具有唯一性 所以不论你在什么地方 根据这个绝对参照地址 所找到的永远是同一个地点 将这两者的特性套用在公式上 代表相对引用地址会随着公式的位置而改变 而绝对参照位址则不管公式在什么地方 它永远指向同一个单元格 3 2 2实例说明 底下我们以实例为你说明相对引用地址与绝对参照地址的使用方式 先选取D2单元格 在其中输入公式 B2十C2 并计算出结果 根据前面的说明 这是相对引用地址 以下我们要在D3单元格输入绝对参照地址的公式 B 3 C 2 选取D3单元格 然后在数据编辑列中输入 B3 按下键 B3会切换成 B 3的绝对参照地址 你也可以直接在资料编辑列中输入 B 3 切换相对引用与绝对参照地址的快速键 键可循序切换单元格地址的参照类型 每按一次键 参照地址的类型就会改变 其切换结果如右 接着输入 C3 再按下F4键将C3变成 C 3 最后按下键 公式就建立完成了 D3的公式內容 D2及D3的公式分别是由相对地址与绝对地址组成 但两者的计算结果却一样 到底它们差别在哪里呢 选定D2 D3单元格 拉曳填满控点到下一栏 将公式复制到E2 E3储存格 计算结果不同了 相对地址公式 D2的公式 B2 C2 使用了相对位址 表示要计算D2往左找两个储存格 B2 C2 的总和 因此当公式复制到E2单元格后 便改成从E2往左找两个储存格相加 结果就变成C2和D2相加的结果 绝对地址公式 D3的公式 B 3十 C 3 使用了绝对位址 因此不管公式复制到哪里 Excel都是找出B3和C3的值来相加 所以D3和E3的结果都是一样的 3 2 3混合参照 如果在公式中同时使用相对引用与绝对参照 这种情形称为混合参照 例如 这种公式在复制后 绝对参照的部份 如 Bl的 B 不会变动 而相对引用的部份则会随情况做调整 绝对参照 相对参照 绝对参照 相对参照 我们继续沿用范例文件Ch03 02做练习 请依照下列步骤将E3单元格中的公式改成混合参照公式 B3 C3 双按E3单元格 将插入点移至 之后 接着按两次F4键 让 B 3变成 B3 将插入点移至 之后 按3次F4键将 C 3变成C3 最后按下enter键 公式便输入完成 接着选定F3 分别拉曳填满控点至F3及E4 E4单元格 F3单元格 B4单元格为0 C4单元格也为0 B3维持1215 D3为2180 3 3函数的使用方法 3 3 1函数的格式 函数是Excel根据各种需要 预先设计好的运算公式 可让你节省自行设计公式的时间 底下我们就来看看如何运用Excel的函数 每个函数都包含三个部份 函数名称 自变量和小括号 我们以加总函数SUM来说明 SUM即是函数名称 从函数名称可大略得知函数的功能 用途 小括号用来括住自变量 有些函数虽没有自变量 但小括号还是不可以省略 引敷是函数计算时所必须使用的数据 例如SUM 1 3 5 即表示要计算1 3 5三个数字的总和 其中的1 3 5就是自变量 自变量的数据类型 函数的自变量不仅只有数字类型而已 也可以是文字或以下3项类别 位址 如SUM B1 C3 即是要计算B1单元格的值十C3单元格的值 范围 如SUM A1 A4 即是要加总Al A4范围的值 函数 如SQRT SUM B1 B4 即是先求出B1 B4的总和后 再开平方根的结果 3 3 2使用函数方块输入函数函数也是公式的一种 所以输入函数时 也必须以等号 起首 假设我们要在B8单元格运用SUM函数来计算班费的总支出 首先选取存放计算结果的B8储存格 并在数据编辑列中数入等号 接着按下函数方块右侧的下拉钮 在列示窗中选取SUM 此时会开启函数引数交谈窗来协助我们输入函数 选取SUM函数 若按下此处 可取得函数的进阶说明 TIP 函数方块列示窗只会显示最近用过的10个函数 若在函数方块列示窗中找不到想要的函数 可选取其他函数项目开启插入函数交谈窗来寻找欲使用的函数 稍后说明 这里会描述此函数的功能 再来就是要设定函数的自变量 先按下第一个自变量栏Number1右侧的折迭钮 将函数自变量交谈窗收起来 再从工作表中选取B4 B6当作自变量 按 下自变量栏右侧的展开钮 再度将函数自变量交谈窗展开 选取B4 B6当作自变量 TIP 除了从工作表中选取单元格来设定自变量 你也可以直接在自变量栏中输入自变量 省下折迭 展开函数引 数交谈窗的痲烦 选取的范围会被虚线框围住 函数自变量交谈窗目前被折迭起来 这里会显示计算的结果 按下确定钮 函数的计算结果就会显示在B8单元格内 刚才输入的函数及公式 计算结果 3 3 3用自动显示的函数列表输入函数若已经知道要使用哪一个函数 或是函数的名称很长 我们还有更方便的输入方法 请直接在单元格内输入 再输入函数的第1个字母 例如 S 单元格下方就会列出S开头的函数 如果还没出现要用的函数 再继续输入第2个字母 例如 U 出现要用的函数后 用鼠标双按函数就会自动输入单元格了 移动箭头键到函数上 会显示函数的说明 双按函数可自动输入单元格 TIP 若一直没有出现函数列表 请切换到文件页次再按下选项钮 然后切换到公式页次 确认已勾选公式自动完成选项 3 3 3利用 自动求和 钮快速输入函数 在开始页次编辑区有一个自动加按钮 可让我们快速输入函数 例如当我们选取B8单元格 并按下钮时 便会自动插入SUM函数 且连自变量都自动帮我们设定好了 只要按下enter键 就可以算出班费的总支出了 会自勤选取好函数引数 你也可以自行重新选取其他范围 这里会有函数的输入格式提示 除了加总功能之外 还提供数种常用的函数供我们选择使用 只要按下钮旁边的下拉钮 即可选择要进行的计算 可用来做这些运算 若选此项 会开启插入函敷交谈窗 3 3 4开启 插入函数 交谈窗输入需要的函数 插入函数交谈窗是Excel函数的大本营 当你在函数方块列示窗中找不到需要的函数时 就可从这里来输入函数 现在我们要练习透过插入函数交谈窗来输入函数 列出面包店各家门市的营业额排名 请选取C4单元格 然后按下数据编辑列上的插入函数钮 你会发现数据编辑列自动输入等号 并且开殷插入函数交谈窗 可从这里选择函数的类别 如财务 统计 文字 日期及时间 等 列出Excel所提供的函数 按下此处可显示目前所选取函数的使用说明 函数的功能叙述 接着从插入函数交谈窗中选取RANK EQ函数 进行门市营业额的排名 1 选择统计类别 若不知道Excel是否提供你所要的函数 也可在此栏输入中 英文关键词 再按下右侧的开始钮进行搜寻 2 选取此类别下的RANK EO函数 3按下确定钮 开启函数引数交谈窗 开启函数自变量交谈窗后 如下图输入各自变量的内容 在此输入B4 2输入要进行排名的单元格范围 在此我们要排名的范围是固定在B4 813之间 所以使用绝对参照地址 按下确定钮即可得到计算结果 计算出 惠城精城门市的营业额是所有门市的第3名 请选取C4单元格 并拉曳其填满控点到C13储存格 计算出所有门市的营业额排名 若想变更自变量设定 请选取函数所在的单元格 然后按下插入函数钮展开函数自变量交谈窗来重新设定 RANK EQ与RANK AVG函数的差异 用来算排名的函数有RANK EO和RANK AVG两个 而在Excel2007 或之前 版本则只有RANK函数 这3个函数都可用来排序 RANK AVG和RANK EQ的差异是在遇到相同数值时的处理方法不同 RANK AVG会传回等级的平均值 RANK EQ则会传回最高等级 RANK则是Excel2007之前版本的函数 在Excel2010仍可使用 其作用与RANK EQ相同 传回3 4的平均等级3 5 传回最高等级 所以会有2个3 没有4 结果与RANK EQ相同 3 4函数综合练习 奖金发放作业 学会插入函数之后 我们来做个综合练习 让你对函数有更进一步的了解 假设我们要依年资来计算中秋节发放的奖金 年资未满1年的员工没有奖金 满1年以上未满3年则发放5 000元奖金 满3年以上则发给8 000元奖金 如下图所示 首先要进行年资的计算 请先选取D2储存格 在此要使用DATEDIF这倜函数来计算两个日期之间的年数 月数或天数 其格式如下 DATEIF 开始日期 结束日期 差距参数单位 到职日 奖金基数基准日 我们以这个日期为基准 所以使用绝对参照位址 求算两日期差距的整年数要使用 Y 参数 在D2单元格中输入 DATEDIF B2 C 2 Y 然后按Enter DATEIF的差距单位参数 计算出 蒋文文 的年资后 请选取D2储存格 然后拉曳填满控黠至Dll储存格 即可算出所有员工的年资 年资为0表示未满一年 计算出 蒋文文 的年资后 请选取D2储存格 然后拉曳填满控黠至Dll储存格 即可算出所有员工的年资 计算出年资後 就可以依年资来计算奖金 我们要用IF函数来判断应发放的奖金 IF函数可判断条件是否成立 如果所传回的值为TRUE时 就执行条件成立时的作业 反之则执行条件不成立时的作业 IF 判读式 条件成立时的作业 条件不成立时的作业 当年资小于1年的条件成立 没有奖金 当年资小于3年的条件成立 奖金为5000 若条件不成立 就是满3年以上 奖金为8000 在E2单元格中输入 IF D2 1 0 IF D2 3 5000 8000 就是好蒋文文的奖金后 请将E2单元格的填满控点拉曳到E11单元格 即可算出所有人的奖金 3 5不需输入公式也能得知计算结果 这一节要介绍一个超实用的自动计算功能 让你不需输入任何公式或函数的情况下 也能快速得到运算结果 其可计算的项目包括求和 平均值 最大值 最小值 等常用的类别 如右图所示 只要选取E2 E11单元格 马上就会在状态栏看到计算结果 选取E2 E11范围 状态列会自动算出此次奖金发放的总金额 平均值等计算结果 自动计算功能不仅会计算总和 平均值 项目个敷 还可以计算最大值 最小值 数字计数等 若要燮更目前计算的项目 可如下操作 在状态栏按右键 选择所要的计算功能 功能前显示打勾符虢 表示含列在状态列 此例请加选最大值 最小值2项 可看出此次奖金发放最少是0元 最都是8000元 4工作表的编辑作业 复制单元格数据搬移单元格资料复制与搬移对公式的影响选择性贴上一复制单元格属性储存格的新增与删除储存格的文字格式设定数字数据的格式化设定单元格的样式日期和时间的格式设定设定数据对齐方式 方向与自动换列功能冻结拆分编辑窗口数据有效性 4 1复制单元格资料 当工作表中要重复使用相同的数据时 可将单元格的内容复制到要使用的目的位置 节省一一输入的时间 这一节我们要分别介绍利用工具钮及鼠标拉曳复制资料的操作步骤 4 1 1利用工具钮复制资料先来说明用工具钮复制单元格数据的操作 如下所示范例 在这个范例中 一月份的总计已在G5单元格建立好了 公式为 SUM C5 F5 由于各月份总计栏的计算方法都是一样的 因此我们要用复制 贴上的方法 将G5的公式复制到G9 Gll单元格 计算出四到六月的总计 接着选取要贴上的区域G9 GII 然后按下开始页次剪贴簿区的贴上钮将源数据贴上 来源资料被虚线框包围 按下此钮 计算出四到六月份的总计 粘贴选项钮 后面会介绍 由于G5单元格的公式使用相对引用地址 因此复制到G9 Gll后 公式参照的位址会随着位置调整 所以G9 Gl0及Gll的公式分别如下 数据复制到贴上区域后 源数据的虚线框仍然存在 所以可再选取其它要贴上的区域继续复制 若复制工作已完成 请按下键或执行其它命令来取消虚线框 4 1 2利用鼠标拉曳复制资料我们也可以改用鼠标拉曳的方式来复制单元格数据 在执行移动单元格的过程中 按住Ctrl键 在箭头的右上角会出现一个 号 这就可以完成对单元格的复制操作 4 1 3插入复制的资料如果贴上区域已有数据存在 我们直接将复制的数据贴上去 会盖掉贴上区域中原有的资料 想保留原有数据的话 可将单元格改以插入的方式复制 假设我们想将A4单元格复制到B4单元格 但又要保留B4原有的内容 可如下操作 选取源数据单元格A4 然后按下Ctrl C键复制来源资料 选定目的单元格B4 切换到开始页次再如图操作 选择此钮 选择此项 原有的资料富益投信会往下移一格 而A4单元格的数据永胜科技会复制到B4单元格 按Esc键即可取消虚线框 A4单元格的资料复制到B4 原来B4资料往下移 4 2搬移单元格资料搬移单元格数据 就是将数据移到另一个单元格放置 当数据放错单元格 或要调整位置时 皆可用搬移的方式来修正 这一节我们同样要介绍以工具钮 鼠标拉曳 以及插入搬移3种方法 4 2 1利用工具钮搬移资料首先选取欲搬移的源数据A3 D3 然后切换到开始页次 按下剪贴簿区的剪下钮 剪下来源资料 被剪下的资料会出现虚线框 选取贴上区域A5 然后按下剪贴簿区的贴上钮 将源数据搬到选取的区域 资料被搬下来了 4 2 2利用鼠标拉曳搬移资料用鼠标拉曳的方式同样也可以搬移数据 接续上例 我们要将单元格A2 D2的资料搬到第6列 选取单元格A2 D2 并将指标移到粗框上 不要放在填满控点上 拉曳至A6单元格后放开鼠标 4 2 3插入搬移资料若粘贴区域已存有数据 为了避免原有的数据被覆盖掉 可改用插入的方式来贴上 请接续上例 假设 梁秀云 和 沈心慈 这两组数据的位置要对调 我们就用插入搬移数据的方式来修正 选取欲搬移的范围A6 D6 然后按下Ctrl Shift键剪下资料 接着选取贴上区域A5 然后由储存格区的插入钮选择贴上的方式 资料已经插入A5 D5 而原来的资料 往下移 4 3复制与搬移对公式的影响 前两节我们谈了许多复制与搬移单元格数据的技巧 当复制或搬移单纯的文字 数字数据时还算简单 但若是要搬移 复制含有公式的单元格 可就要格外当心了 这一节我们就来解析复制与搬移对公式的影响 4 3 1复制公式的注意事项将公式复制到贴上区域后 Excel会自动将贴上区域的公式调整为该区域相关的相对地址 因此如果复制的公式仍然要参照到原来的单元格地址 该公式应该使用绝对地址 以下以范例进行说明 B5单元格含有公式 用来计算上面3个单元格的总和 将85复制到B6时 公式变成从B6往上找3个储存格来做加总 4 3 2搬移公式的注意事项若搬移的单元格资料跟公式有关 请特别注意下列几点 假如搬移的是公式 则公式中的位址不会随着粘贴区域的位址调整 所以公式仍然会参照到原来的单元格位址 搬移前公式 搬移后公式 按下Ctrl X键剪下资料 再到B6单元格 若公式参照到单元格范围 则仅搬移其中一倔储存格的话 其公式参照的储存格范围不变 参照到B2 B4 参照的范围不变 而值改变了 将B3单元格搬到E3单元格 若将参照的范围整个搬移到别处 公式的参照会跟着调整到新地址 公式变了 但值没变 将B2 B4移到E2 E4 如果公式参照到的单元格数据被移到其它单元格中 则公式会自动跟着调整到新地址 搬移前公式内容 搬移后公式变了 将C3单元格搬移到E3单元格 储存格袒含有多种资料 例如用来建立公式的单元格 其内就含有公式和计算的结果 而单元格也可以只包含单纯的文字或数字资料 逭些我们通称为储存格的属性 4 4选择性粘贴上一复制单元格属性 4 4 1复制单元格属性下面我们用实例来进行练习 假设要将D2单元格的公式及值属性 分别复制到E2及E3单元格 先选取来源资料储存格D2 并切换到开始页次 按下剪贴簿区的复制按钮 这个动作会复制D2的所有属性 这个单元格具有公式与值两个属性 选取E2储存格 然后按下粘贴钮 即可看到数种储存格属性供你选择 这些都是粘贴钮可以选择的单元格属性 从粘贴的下拉选单中选取公式 表示要粘贴公式属性 公式复制过来了 值会重新计算 接着选取E3储存格 再次按下粘贴钮 从选单中改选值 这次要贴上的是值这个属性 只将值复制过来 4 4 2由粘贴选项设定要帖上的单元格属性将储存格的属性贴到目的储存格之后 目的储存格旁边舍出现粘贴选项钮 若按下此钮 可显示选单来改边所要粘贴的储存格属性 点击此按钮 粘贴属性 4 5单元格的新增与删除有时候舍在输入资料的遇程中爱发现资料漏打 需要在现有的资料周穿插放入这些资料时 就需要先在工作表中插入空白列 行或储存格 再到其中输入资料 若有空白的储存格 或用不到的内容 则可将之删除 4 5 1新增与删除整行 整列新增列单击要插入位置后面的列标 选择一列单元格 单击右键弹出快捷菜单 选择插入命令 或者选择 开始 菜单里的插入按钮 新增行单击要插入位置后面的行标 选择一行单元格 单击右键弹出快捷菜单 选择插入命令 或者选择 开始 菜单里的插入按钮 删除行 列在要删除的行或列上单击右键 在弹出的快捷菜单中选择 删除 命令即可 或者选择 开始 菜单里的删除按钮 4 5 1新增与删除空白单元格新增空白单元格假设要在B2 D2插入3个空白单元格 则先选取要插入空白单元格的B2 D2 切换至 开始 菜单的单元格区域下的插入按钮 执行插入单元格命令 删除空白单元格选取要删除的单元格 比如B2 D2 切换至 开始 菜单的单元格区域下的删除按钮 执行删除单元格命令 4 6单元格的文字格式设定如果设定的封象是整个储存格的文字 只要选取储存格 或是选取储存格范围 整行 整列 再切换到开始页次 按下字体区的工具钮进行设定 文字就会套用设定的格式了 此区域用来设定文字格式 4 7数字资料的格式化工作表的敷字资料 也许是一笔金额 一倜数量或是银行利率 等等 若能为数字加上货币符号 百分比符号 想必更能表达出它们的特性 4 7 1直接输入数字格式储存格预设的数字格式精通用格式 表示用哪一种格式输入敷字 敷字就含以该种格式显示 如在Al储存格中输入 123 以后就算在Al储存格中重新输入其他敷字 Excel也会以货币格式来显示 再次选取Al储存格 然后输入 4567 不要输入 符虢 4 7 2以数值格式列示窗设定格式例如想把 4567 改成 4567 00 选择 开始 菜单里数字区域 点击数据格式 选择适合的数值格式 选择此项 数据格式提供了多种设定数值格式的选项 具体可以自选择练习 4 8设定单元格的样式如果觉得预设的一般样式太单调 可先选取要变换样式的单元格或范围 再切换到开始页次 在样式去中按下单元格样式钮 从中选挥喜欢的样式 节省自己设定各种格式的时间 选择此按钮 也可以在此设定数字格式 选择喜欢的样式 4 9日期和时间的格式设定日期和时间也是属于数字资料 不过因为它们的格式比较特殊 可显示的格式也有多种变化 所以我们特别独立一节来详细说明 4 8 1输入日期与时间当你在储存格中输入日期或时间资料时 必须以Excel能接受的格式输入 才会被当成是日期或时间 否则会被当成文字资料 以下列举Excel所能接受的日期与时间格式 4 8 2更改日期与时间的显示方式选中要更改日期格式的单元格 单击鼠标右键 选择 设置单元格格式 进入数字选项 如下图所示 选择要设定的日期格式 更改时间的显示方式和更改日期的方法类似 日期格式 4 8 3计算两个日期间的天数如果想知道两日期间的间隔天敷 或是两时间所间隔的时数 可以建立公式来计算 间隔天数 4 8 3计算数天后的日期 45天后的日期 4 10设定资料对齐方式 方向与自动换列功能 4 10 1设定单元格资料的水平及垂直对齐方式要调整储存格资料的对齐方式 最快的方法是选取储存格 然後切换到开始页次 按下对齐方式区的工具钮来设定 设定垂直的对齐方式 设定水平的对齐方式 4 10 2设定文字的方向储存格内的资料预设是横式走向 若字敷较多 储存格宽度较窄 还可以设为直式文字 更特别的是可以将文字旋转角度 将文字斜着放 要改变字体的方向 先选中单元格 切换到开始菜单 在对齐方式区下选择钮 也可以通过设置单元格格式里的对齐方式进行设定 设定文字方向 4 10 3让单元格的文字能自动换列先选中单元格 切换到开始菜单 在对齐方式区下选择自动换列钮 选择此按钮 4 11冻结拆分窗口 当冻结窗格时 可以选择在工作表中滚动时仍可见的特定行或列 其方法如下 要锁定行 请选择其下方要出现拆分的行 要锁定列 请选择其右侧要出现拆分的列 要同时锁定行和列 请单击其下方和右侧要出现拆分的单元格 点击菜单栏 视图 冻结拆分窗口 当冻结窗格时 冻结窗格 选项更改为 取消冻结窗格 可以取消对行或列的锁定 拆分窗格以锁定单独工作表区域中的行或列 指向垂直滚动条顶端或水平滚动条右端的拆分框 当指针变为拆分指针或时 将拆分框向下或向左拖至所需的位置 要取消拆分 请双击分割窗格的拆分条的任何部分 垂直滚动条拆分框 水平滚动条拆分框 4 12数据有效性 利用数据菜单中的有效性功能可以控制一个范围内的数据类型 范围等 还可以快速 准确地输入一些数据 比如录入身份证号码 手机号这些数据长 数量多的数据 操作过程中容易出错 数据有效性可以帮助防止 避免错误的发生 数据有效性的设置方法如下 选定要输入数据的区域 如F列 选择菜单栏里的 数据 有效性 打开数据有效性对话框 设置有效性条件为 文本长度 选择 数据 为 等于 长度栏根据需要填写 如身份证为 18 手机号码就填 11 这样在单元格填写的号码会以科学记数法的形式出现 要点击 设置单元格式 选择 数据 中的文本 通过数据有效性还可以设置下拉菜单选定要设定下拉菜单的列 选择菜单栏里的 数据 有效性 打开数据有效性对话框 设置有效性条件为 序列 在 来源 里输入要限制输入的值 逗号输入时要切换到英文输入模式 数据有效性还可以应用于时间 日期 整数 小数等的检查控制 数据有效性的出错信息数据有效性的出错信息包括 停止 警告 信息 5建立图表 图表类型介绍建立图表物件调整图表物件的位置及大小认识图表的组成项目变更图表类型变更资料范围 5 1图表类型介绍 工作表中的数据若用图表来表达 可让数据更具体 更易于了解 Excel内建了多达70余种的图表样式 你只要选择适合的样式 马上就能制作出一张具专业水平的图表 请切换到插入页次 在图表区中即可看到内建的图表类型 按下各个图表类型钮 可开启Excel内建的图表类型 进一步选择副图表类型 你可以参考5 5节的说明来更换 以下我们就简单为你说明几种常见的图表类型 当你要建立图表前 可以依自己的需求来选择适当的图表 直条图 柱形图是最普遍使用的图表类型 它很适合用来表现一段期间内数量上的变化 或是比较不同项目之间的差异 各种项目放置于水平坐标轴上 而其值则以垂直的长条显示 例如各项产品在第一季每个月的销售量 折线图 显示一段时间内的连续数据 适合用来显示相等间隔 每月 每季 每年 的资料趋势 例如某公司想查看各分公司每一季的销售状况 就可以利用折线图来显示 圆形图 饼图只能有一组数列数据 每个数据项都有唯一的色彩或是图样 饼图适合用来表现各个项目在全体数据中所占的比率 例如底下我们要查看流行时尚杂志中卖得最好的是哪一本 就可以使用饼图来表示 横条图 可以显示每个项目之间的比较情形 Y轴表示类别项目 X轴表示值 条形图主要是强调各项目之间的比较 不强调时间 例如你可以查看各地区的销售额 或是像底下列出各项商品的人气指数 区域图 强调一段时间的变动程度 可由值看出不同时间或类别的趋势 例如可用分区图强调某个时间的利润数据 或是某个地区的销售成长状况 底下以惠州近年来各县市新生儿人口数为例来绘制分区图 散布图 显示2组或是多组资料数值之间的关联 散布图若包含2组坐标轴 会在水平轴显示一组数字数据 在垂直轴显示另一组数据 图表会将这些值合并成单一的数据点 并以不均匀间隔显示这些值 散布图通常用于科学 统计及工程数据 你也可以拿来做产品的比较 例如底下的冰热两种饮料会随着气温变化而影响销售量 气温愈高 冷饮的销量愈好 股票图 股票图顾名思义就是用在说明股价的波动 例如你可以依序输入成交量 开盘价 最高价 最低价 收盘价的数据 来当做投资的趋势分析图 环圈图 与饼图类似 不过环圈图可以包含多个资料数列 而饼图只能包含一组数列 例如右图的例子 可以看出电器产品近三年的销售状况 泡泡图 泡泡图和散布图类似 不过泡泡图是比较3组数值 其数据在工作表中是以栏进行排列 水平轴的数值 X轴 在第一栏中 而对应的垂直轴数值 Y轴 及泡泡大小值则列在相邻的栏中 例如底下的例子 X轴代表产品的销售量 Y轴代表产品的销售额 而泡泡的大小则是广告费 雷达图 可以用来做多个资料数列的比较 例如右图的例子 我们可以雷达图来了解每位学生最擅长及最不擅长的科目 5 2建立图表对象 认识Excel的图表类型后 现在我们就以实际的例子来学学如何在工作表中建立图表对象 将令人眼花的数据数据转变成美观 易于辨别高低趋势变化的图表 看起来也更为专业 有说服力 5 2 1建立销售图下面以房产销售量为例子说明建立销售图 如下图 选取A3 E7范围 切换到插入页次 在图表区中如下操作 按下柱形图按钮 选择三维柱形图下的三维簇状柱形图 随即在工作表中建立好图表物件 若是修改了图表的来源资料 图表会自动重新修正内容 不需重新绘制图表 TIP 你也可以在选取源数据后 直接按下ALT十F1键 快速在工作表中建立图表 不过所建立的图表类型则是预设的柱形图 如果你有自行修改过默认的图表类型 那么将以你设定的为主 5 2 2认识 图表工具 页次 在建立图表物件后 图表会呈选取状态 功能区还会自动出现一个图表工具页次 你可以在此页次中进行图表的各项美化 编辑工作 图表工具页次 图表的周围出现框线 表示图表目前为选取状态 5 2 3将图表建立在新工作表中 刚才建立的图表对象和数据源放在同一个工作表中 最大的好处是可以对照数据源中的数据 但若是图表太大 反而容易遮住数据源 此时你可以将图表单独放在一份新的工作表中 请在选取图表对象后 在图表上单击即可选取 切换到图表工具页次下的设计页次 并按下移动图表钮 选择此钮 接着会开启移动图表交谈窗 让你选择图表要建立在新工作表或是移动到其他工作表中 选择新工作表 若拉下此列示窗 可选择将图表搬移到其他工作表中 设定完成后 即可在Chart1工作表中看到图表物件 TIP 要将图表建立在独立的工作表中 除了刚才所述的方法外 还有一个更简单的方法 那就是在选取数据来源后 直接按下F11键 即可自动将图表建立在Chartl工作表中 5 3调整图表对象的位置及大小 5 3 1移动图表的位置建立在工作表中的图表对象 也许位置和大小都不是很理想 没关系 只要稍加调整即可 直接拉曳图表对象的外框 即可移动图表 黑色框线代表图表物件移动后的新位置 5 3 2调整图表的大小如果图表的内容没办法完整显示 或是觉得图表太小看不清楚 你可以拉曳图表对象周围的控点来调整 拉曳图表外框的控点可调整图表的宽度或高度 拉曳对角控点可同步调整宽 高 5 3 3调整字型的大小如果调整过图表的大小后 图表中的文字变得太小或太大 那么你可以先选取要调整的文字 并切换到开始页次 在字型区拉下字号列示窗来调整 在文字上按一下即可选定文字 拉下字号列示窗 调整文字大小 文字变大了 TIP 在字型区中除了可调整图表中的文字大小 你还可以利用此区的工具钮来修改文字的格式 例如加粗 斜体 更改字型颜色 等 调整文字大小 图表也会自动缩放大小 如果调整文字后 图表反而变的不清楚 你可以利用刚才所教的方法 拉曳图表对象的边框 来调整图表对象的大小 5 4认识图表的组成项目从上面所建立的图表中 我们可以发现图表是由许多项目所组成 这一节我们就来认识这些图表的组成项目 不同的图表类型其组成项目多少会有些差异 但大部份是相同的 底下我们以图表来说明图表的组成项目 图表标题 绘图区 格线 图例 图表区 坐标轴 图表区 指整个图表及所涵盖的所有项目 绘图区 指图表显示的区域 包含图形本身 类别名称 坐标轴等区域 绘图区 图例 辨识图表中各组数据系列的说明 图例内还包括图例项标示 图例项目 图例项目是指与图例符号对应的资料数列名称 图例项标示代表数据系列的图样 座标轴与网格线 平面图表通常有两个坐标轴 X轴和Y轴 立体图表上则有2个坐标轴 X Y和Z轴 但并不是每种图表都有坐标轴 例如饼图就没有坐标轴 而由坐标轴的刻度记号向上或向右延伸到整个绘图区的直线便是所谓的网格线 显示网格线比较容易察看图表上数据点的实际数值 格线 X轴通常为水平轴 包含类别 Y轴通常是垂直轴 包含数值资料 刻度符号 图表墙和图表底板 图表墙和图表底板则是立体图表所特有的 图表底板 为了方便辨谶 我们替图表底板填上色彩 图表墙 为了方便辨识 我们替图表墙填上色彩 5 5变更图表类型 本章一开始我们就提过 不同的图表类型 所表达的意义也不同 若是你觉得当初建立图表时所选择的图表类型不适合 该怎么办呢 这一节我们先带你了解一下选择数据源范围与图表类型的关系 再告诉你如何替建立好的图表更换图表类型 每一种图表类型都有自己的特色 而且绘制的方法也不尽相同 千万别以为只要任意选取了一个数据范围 就可以画出各种图表 例如 圆形图只能表达一组数据系列 所以数据范围应该只包含一组数列 5 5 1选取数据源范围与图表类型的关系 若要画散布图 由于此种图表类型的X Y轴都要是数值坐标轴 没有类别坐标轴 所以选取的范围至少包含两组有互相影响关系的数值坐标轴 画雷达图时 每个类别都会有自己的数值坐标轴 所以数据范围最好要包含多组类别 而且至少要超过3个 因此在选取数据范围时 你应该先考虑到这个数据范围要以什么样的图表类型来表达 或是先决定好要绘制哪一种类型的图表 再来考虑数据的选取范围 5 5 2更换成其他图表类型 当图表建立好以后 若觉得原先设定的图表类型不恰当 可在选取图表后 切换到图表工具 设计页次 按下类型区的变更图表类型钮来更换 选取图表 切换到圈表工具 设计页次 按下变更图表类型钮 由此区选择要更换的图表类型 由此区选择图表的样式 按下确定钮 5 6变更资料范围 在建立好图表之后 才发现当初选取的数据范围错了 想改变图表的数据源范围 你可以如下操作 不必重新建立图表 房产销售量为范例而言 我们只需要一到三月的销售量 而不需将第一季的总销售量也绘制成图表 所以要重新选取数据范围 多选了第一季总销量的数据 请选取图表对象后 切换到图表工具 设计页次 然后按下数据区的选取数据钮 开启选取数据数据来源对话框来操作 选取第一季总量 按下删除钮 按下此钮 可将交谈窗缩小 到工作表中重新选取资料 按下确定钮后 图表即会自动依选取范围重新绘图 只绘制一到三月的销售量 改变图表栏列方向 数据系列取得的方向有循栏及循列两种 接下来我们要告诉你如何改变数据系列的取得方向 接续刚才的范例 图表的数据系列是来自列 如果我们想将数据系列改成从行取得 请选取图表对象 然后切换到图表工具 设计页次 按下切换列 栏钮即可 数据系列来自列 数据系列来自行 按下此钮 6图形化数据分析 利用 格式化条件 分析数据数据帮助快速了解数据的走势图 6 1利用 格式化条件 分析数据 当工作表中的数据量一多 实在很难一眼辨识出数据高低或找出所需的项目 若我们想强调工作表中的某些资料 如 会计成绩达到90分以上者 销售额未达标准者 便可利用设定格式化的条件功能 自动将数据套上特别的格式以利辨识 在设定格式化的条件功能中 具有许多种数据设定规则与视觉效果 请在开始页次中按下定格式化的条件钮 设定格式化的条件功能的各种资料规则 每种规则中尚可选择更细的设定条件 设定格式化的条件应用之一 分析全班考试成绩 标示为黄色的是90分以上 标示为红色的则是不及格的成绩 一眼就可以分辨出来 以下我们将一一利用不同的范例 来练习套用设定格式化的条件功能的各种规则 实例1 分析考试成绩 用 大于 规则标示90分以上的考试成绩假设我们要找出90分以上的成绩 看看考到高分的学生大约有多少 便可利用大于规则帮我们标示出90分以上的成绩 请选取B3 G15 然后切换到开始页次 在样式区按下设定格式化的条件钮 执行 突出提示单元格规则 大于 命令 执行此命令 输入89 按下确定钮 选择符合规则的储存格要标示的格式 设定时工作表会实时预览标示的结果 将90分以上的成绩都标示出来 当您在数据表中加入格式化条件后 若在选取的数据范围内 又修改了数值 标示的结果也会立即变更 原本的成续 88 未符合标示条件 修改成绩为 90 之后按下Enter键 一符合条件便立即显示标示 接着 可以利用相同的方法 执行 突出提示单元格规则 小于 命令 练习将分数小于60分的标示为红色 执行 突出提示单元格规则 介于 命令 将介于75 89分的标示为绿色 就可在工作表中一眼看出各成绩的区间了 标示为黄色的是90分以上 红色是不及格 绿色则是介于75 8