excel系列问题汇总.doc

系列问题汇总（一）2008-10-30目录1、 Excel中找出重复记录/不重复的记录1高级筛选方法1函数方法：用IF函数对数据进行条件检测2使用数据有效性：防止输入重复数据32、 小数取整的方法7法一：INT取整7法二：ROUND四舍五入取整7法三：ROUNDDOWN向绝对值减小的方向舍入取整7法四：ROUNDUP远离零值，向上舍入数字7法五：公式提取小数点左面的数字73、 假数作图法的应用8修改时间间隔对应坐标轴作图8利用辅助数据将图表按指定区间进行区域分割11利用添加空白列加入堆积柱形图的柱空隙134、 辅助列/行的加入方法及应用16法一：利用工具栏的快捷按钮16法二：自动填充法16如何使Excel可以随时恢复原来顺序17如何翻转Excel表格列的顺序17如何每隔一行加一空白行18怎么在Excel里加入项目符号195、 分列中英文混杂内容21法一：手动添加分隔符后再数据分列21法二：公式提取字符法221、 Excel中找出重复记录/不重复的记录高级筛选方法步骤：（1） 选择要排序的行或列标题（或者直接就选择行列） 此处要保证首行不是要筛选的数据，如果是此数据会被记为列标题，操作会有误差，例子见图1-3（2） 筛选-高级筛选，如果收到如下提示，单击确定 （3） 打开高级筛选对话框，单击”将筛选结果复制到其他位置”单击”选择不重复的记录”复选框（4） 在”列表区域”框中，键入或选择一个记录范围（如A1：A10）（5） 在”条件区域”框中，键入或选择相同的记录范围（即A1：A10）（6） 在”复制到”框中，键入用于存放信息的第一个单元格的地址，或直接选择该单元注意：请确保该区域有足够的空间，以免覆盖已存在的数据，以上步骤（3）到（6）如图1-1所示（7） 点击”确定”后，原数据A列的重复数据就在”复制到”的新区域里显示出来了，如图1-2（8） 另外一种情况，当忽略了首行的数据（确实为重复数据之一时）为列标题时就会出现重复数据并没有被除去，而是作为列标题被复制到新区域里了，所以在选择数据的时候要注意这一点（如图1-3）图1-1图1-2图1-3函数方法：用IF函数对数据进行条件检测（假设重复内容在A列，数据操作都在B列进行）步骤：（1） 对A列排序，选中数据-数据-排序，只要按一定顺序排序即可，目的是为了是相同数据相邻（如图1-4）（2） 在B列单元格输入公式”=if(A2=A1,”0”,”1”)”，并向下复制此公式，显示”1”的为不重复数据，或重复数据中的一个（如图1-5）（3） 要将重复数据删除，对B进行筛选：选中区域-数据-筛选-自动筛选-选择值为”1”，即可筛选出不重复数据（如图1-6）图1-4图1-5图1-6使用数据有效性：防止输入重复数据（1） 选定需要检查的数据，如”A1:B10”，数据-有效性（如图1-7）（2） 打开”数据有效性”对话框-选择”设置”标签-有效性条件-允许-自定义-在公式栏里输入”=COUNTIF($A$1:$B$10,A1)=1”注 此公式代表在此区域内满足给定条件的单元格的个数=1（如图1-8）（3） 选择”出错警告”标签，勾选”输入无效数据时显示出错警告”，”样式”列表处选择”停止”，在”标题”处输入”数据重复”，在”错误信息”处输入”数据重复，请重新输入”（如图1-9）（4） 通过以上设置当在A1:B10区域中的B1单元格中输入9后，因为与同在该区域的A9单元格内容重复，所以立刻会弹出一个警告对话框，阻止重复输入的数据（如图1-10）（5） 只有取消或更换输入的内容后，输入操作才得到允许（6） 如果不操作步骤（3），在输入重复数据时也会有所提示（如图1-11），步骤（3）只是为了使用者快速了解出错原因（7） 需要注意的一点是：当输入一个数值时，如果使用向下填充单元格，Excel还是允许的（如图1-12） 图1-7图1-8图1-9图1-10图1-11图1-122、 小数取整的方法说明：当运算是在修改”单元格数字格式”的小数位数后进行的，其实并不能以显示的结果为计算值，它的计算还是按照全数来算的，因此要用函数方法真正的四舍五入或取整法一：INT取整语法：INT(number)Number需要进行向下舍入取整的实数说明：取接近原数的整数的最小值法二：ROUND四舍五入取整语法：ROUND(number,num_digits)Number需要进行四舍五入的数字Num_digits指定的位数，按此位数进行四舍五入法三：ROUNDDOWN向绝对值减小的方向舍入取整语法：ROUNDDOWN(number,num_digits)Number需要向下舍入的任意实数Num_digits四舍五入后的数字的位数说明：以绝对值的最小值作为取值结果法四：ROUNDUP远离零值，向上舍入数字语法：ROUNDUP(number,num_digits)Number需要向上舍入的任意实数Num_digits四舍五入后的数字的位数说明：四舍五入后取绝对值的最大值比较：函数ROUNDDOWN和函数ROUNDUP功能相似，不同之处在于函数ROUNDDOWN是以最接近原点的整数作为取值（绝对值最小），而ROUNDUP总是向上舍入数字，即坐标轴原点的两端作为取值UPUP- +DOWN0法五：公式提取小数点左面的数字公式：=LEFT(A1,FIND(“.”,A1)-1) （以单元格A1为例）查找小数点起始位置说明：从左提取小数点起始位置-1的字符，没有四舍五入，提取结果和ROUNDDOWN函数一样，比较以下这组数的取整结果：3、 假数作图法的应用修改时间间隔对应坐标轴作图需求：如图3-1所示，已有数据是从07年1月到08年07月，基本单位都是天。现需要将未来年份预测值（通过计算公式的出的预测值）延续添加到现有折线图。未来年份的预测值主要时间点是：2008Q3、2008Q4、2009Q1、2009Q2、2009Q3、2009Q4、2010、2011、2012图3-1步骤：（1） 延长时间点，并使延长的时间间隔由原来的3天变为一个月，对应未来预测值主要时间点，如图3-2所示（2） 做两条线，分类X轴标志相同，没有数据的部分都为空，调整坐标轴刻度如图3-3（1）和3-3（2）（3） 延长原数据的取值，也即第一条线，效果如图3-4（4） 预测数据做成散点图，也即第二条线，效果如图3-5（5） 在相应预测值部分添加可覆盖的文本框，标注相应的时间点，最终效果如图3-6这里的时间间隔由原来的3天变为一个月，因此数据点出现的位置也就明显的被分开图3-2图3-3（1）图3-3（2）图3-4图3-5图3-6利用辅助数据将图表按指定区间进行区域分割需求：如图3-7所示，为了清楚显示数据系列与分割区间，可以将图表背景填充颜色用以分割，但是填充图对折线图会有一点颜色的影响，为了清晰显示折线图如何利用添加辅助数据实现，而不是画图实现？ 如此复杂的方法并不适用快速工作的需求，此方法适于有相同多组数据都有相同数据区间时复制使用图3-7步骤：（1） 在原数据的基础上添加作为分割区间的辅助数据，如图3-8（2） 选择图表类型为”堆积柱形图”，选择原数据区域为”折线图”，效果如图3-9（3） 双击堆积柱形图-数据系列格式-选项，改分类间距为0（默认为150），如图3-10；图案-边框-无，此操作对柱图的每个系列都要修改，本图修改四次（4） 清除多余的图例项标示格式，选中要清除的图例项-右键-清除，如图3-11（1），此处要注意一点，需先选中图例的方框在框里删除图例项标示，如图3-11（2）（5） 对填充颜色修改，效果如图3-12添加的辅助数据区域图3-8图3-9图3-10图3-11（1）图3-11（2）图3-12利用添加空白列加入堆积柱形图的柱空隙需求：如图3-13，想要按照原有和新增分组显示堆积柱形图，如何实现？图3-13步骤：（1） 原数据如图3-14（1），为了分组则需要把原数据按照原有和新增分两列显示，空白区域如蓝色框内显示，同时要把数据分月份显示，则要在月份之间加入空行，如图3-14（2）（2） 更新数据区域的范围，效果如图3-15，但是没有按月份分组数据（3） 双击柱图-选择”数据系列格式”-选项-将分类间距改为”0”，可将数据按月份分组，如图3-16，而空白部分由图3-14（2）中红色空白区域值组成（4） 通过加入空白列可将数据分组，修改分类间距值可将柱图无间距显示，最终效果如图3-17图3-14（1）图3-14（2）图3-15图3-16图3-174、 辅助列/行的加入方法及应用法一：利用工具栏的快捷按钮（1） 在工具栏空白处点击右键-自定义-命令-编辑-序列，把”序列”按钮拖到工具栏（如图4-1）（2） 新插入一列，在第一个单元格里输入”1”（或是任意起始的数字），选中这个单元格点击工具栏”序列”或是依次点击编辑-填充-序列，弹出”序列”对话框，在步长值里输入公差的数字，终止值里输入列值的最后数字-确定，就会自动添加公差为”1”的等差序列（如图4-2） 图4-1图4-2法二：自动填充法输入有效数据列的前两项，如1、2 ，1、3 框选这一列当鼠标变成小加号时向下拖动，选择以序列方式填充即可（如图4-3）图4-3如何使Excel可以随时恢复原来顺序说明：操作数据时经常要用排序，而当排序错误，进行了保存，原数据又无规律可循时，往往难以恢复数据原貌，如果在做数据操作之前进行依照辅助列排序（有规律），就可以不论何种操作情况下随时还原原数据原顺序。方法：排序前插入一列自然序的辅助列（如图4-4），需要恢复的时候按照辅助列排序，表格就可随时可还原为原顺序。图4-4如何翻转Excel表格列的顺序步骤：（例子如图4-5所示，如何使列项逆序排列）（1） 当首行（列标题）不可直接用作排列时，须在首行加一辅助行，单元格依序列输入1、2、3 （2） 选择这一部分数据-数据-排序-选项-方向-按行排序主要关键字-行1-降序-确定（如图4-6）（3） 调整行宽列高，并删除首行的辅助行即可（如图4-7）图4-5图4-6图4-7如何每隔一行加一空白行步骤：（例子如图4-8，如何在每个时间后面加以空行）（1） 在A列从A2开始添加序列数1、3、5 到A6，可以看到最后一个数字是9，从A7起添加2、4、6 到10（如图4-9）（2） 选定这一区域A1:D11，数据-排序-主要关键字”按列A”排序，如图所示原数据显示每隔一行加一空行的效果（如图4-10）（3） 删除A列，同样步骤也可以每隔一列加一空列，公差不同也可以隔多行加入空列/行图4-8图4-9图4-10怎么在Excel里加入项目符号说明：由于Excel里没有像Word一样的项目符号和编号的功能，只能用加入特殊符号这种方法来完成，因此特殊符号是被当作字符来处理的，不能实现悬挂缩进的效果。（如图4-11）所以加一辅助列作为特殊符号对齐内容图4-11步骤：（1） 在A列插一空列，插入特殊符号n ，打开单元格格式设置-对齐-垂直对齐-靠上（2） 修改A列的右边框为无（如图4-13），即可做出Excel里的项目符号样式（如图4-14）图4-12图4-135、 分列中英文混杂内容法一：手动添加分隔符后再数据分列步骤：（表5-1为原数据）（1） 先手动添加分隔符号，如表5-2（2） 数据-分列-分隔符号-选择添加的分隔符（注意分隔符号必须是英文状态下的，中文符号是不能识别的），如图5-1、图5-2所示（3） 在目标区域里可以选择分列后的存放位置，默认为原有单元格及其右侧一列，如图5-3（4） 或者选择已有的可作为字段分隔的符号，根据提示操作-完成，即可分列数据表5-1表5-2注 表5-1到5-3都为嵌套Excel表，可以双击进Excel表使用图5-1图5-2图5-3注意：此方法可用于数据量小的分列，如果数据量大，又没有分隔符号就不太适合了，法二用于大量数据的分列则较容易法二：公式提取字符法步骤：以单元格A2 Andorra安道尔为例，在B2中输入：=LEFT(A2,LEN(A2)-(LENB(A2)-LE