Excel里怎么自动填充内容.docx

Excel里怎么自动填充内容.docx

《Excel里怎么自动填充内容.docx》由会员分享，可在线阅读，更多相关《Excel里怎么自动填充内容.docx（58页珍藏版）》请在冰豆网上搜索。

Excel里怎么自动填充内容

第一部分：

SUMPRODUCT函数用法介绍

　　excel中SUMPRODUCT函数是一个数组类型的函数。

SUMPRODUCT函数能够计算多个区域的数值相乘后之和。

　　SUMPRODUCT函数的语法为：

SUMPRODUCT（数组1,数组2,数组3,……）

　　使用SUMPRODUCT函数有两点需要注意：

　　第一，在SUMPRODUCT函数的参数中，数组的大小必须相等，否则将返回#NUM!

错误。

　　第二，SUMPRODUCT函数将数组中不是数字的数组元素作为0对待。

第二部分：

SUMPRODUCT函数实例

　　实例：

计算6月份总销售额

　　实例详见下图：

　　选中G6单元格，输入公式：

=SUMPRODUCT（C3:

C9,D3:

D9），即可求出所有产品的总销售额。

们在本文通过统计多种产品的总销售金额来巩固所学的sumproduct函数。

　　实例：

统计多种产品的总销售金额

　　本例中按类别统计了销售记录表，此时需要统计出女式连衣裙和女式职业装两类的销售金额，我们可以直接使用sumproduct函数来实现。

如下图所示。

　　选中E8单元格，输入公式：

=SUMPRODUCT（（（B2:

B19="女式连衣裙"）+（B2:

B19="女式职业装"））,$C$2:

$C$19）

　　按下回车键，确定，即可统计出女式连衣裙和女式职业装两类的销售金额。

　vlookup函数应用实例一：

　　问题：

如下图，已知表sheet1中的数据如下，如何在数据表二sheet2中如下引用：

当学号随机出现的时候，如何在B列显示其对应的物理成绩？

　　根据问题的需求，这个公式应该是：

=vlookup（a2,sheet1!

$a$2:

$f$100,6,true）

详细说明一下在此vlookup函数例子中各个参数的使用说明：

　　第一，vlookup是垂直方向的判断，如果是水平方向的判断可使用Hlookup函数

　　第二，a2是判断的条件，也就是说如果sheet2表中a列对应的数据和sheet1表中的数据相同方能引用；

　　第三，sheet1!

$a$2:

$f$100是数据跟踪的区域，因为需要引用的数据在f列，所以跟踪的区域至少在f列，$是绝对引用。

　　第四，6这是返回什么数的列数，如上图的物理是第6列，所以应该是6，如果要求英语的数值，那么此处应该是5。

　　第五，是否绝对引用，如果是就输入true如果是近似即可满足条件，那么输入false（近似值主要用于带小数点的财务、运算等）。

　　结果如下图：

　　vlookup函数实例讲解二：

说明函数=VLOOKUP（f1,A1:

E100,2,FALSE）的意思。

　　意思是：

在A1:

E100区域查找f1的值，找到后，返回从a开始算的第2列值（即b列），false为精确查找的必备参数。

　　vlookup函数实例讲解三：

　　上图，A2：

B5为参照数组范围，E2为欲搜寻的值，传回数组表的欲对照的栏为第2栏（姓名），在F2输入=VLOOKUP（E2,A2:

B5,2,FALSE）将会找到155003是王小华，然后显示出来。

　　vlookup函数实例讲解四：

　　A　　B　　　C　　D

1编号　姓名　工资　科室

22005001 张三2870办公室

32005002 李四2750人事科

42005006郑化2680供应科

52005010屠刚红2980销售科

62005019 王五2530财务科

72005036孟小庭2200工会

A列已排序（第四个参数缺省或用TRUE）

VLOOKUP（2005001,A1:

D7,2,TRUE）等于“张三”

VLOOKUP（2005001,A1:

D7,3,TRUE）等于“2870”

VLOOKUP（2005001,A1:

D7,4,TRUE）等于“办公室”

VLOOKUP（2005019,A1:

D7,2,TRUE）等于“王五”

VLOOKUP（2005036,A1:

D7,3,TRUE）等于“2200”

VLOOKUP（2005036,A1:

D7,4,TRUE）等于“工会”

VLOOKUP（2005036,A1:

D7,4）等于“工会”

若A列没有排序，要得出正确的结果，第四个参数必须用FALAE。

　　解释：

关于TRUE和FALSE的应用

　　先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。

用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。

当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。

个人觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。

　　vlookup函数实例讲解五：

　　本示例搜索员工表的ID列并查找其他列中的匹配值，计算并测试错误条件。

A　　B　C　　D　　E

ID姓氏名字职务出生日期

1李小明销售代表12/8/1968

2林彩瑜销售部副总2/19/1952

3王志东销售代表8/30/1963

4潘金销售代表9/19/1958

5林丹销售经理3/4/1955

6苏术平销售代表7/2/1963

公式说明（结果）

　　=IF（ISNA（VLOOKUP（5,A2:

E7,2,FALSE））=TRUE,"未找到员工",VLOOKUP（5,A2:

E7,2,FALSE））如果有ID为5的员工，则显示该员工的姓氏；否则，显示消息“未找到员工”。

（林）

　　当VLOOKUP函数返回错误值#NA时，ISNA函数返回值TRUE。

　　=IF（ISNA（VLOOKUP（15,A3:

E8,2,FALSE））=TRUE,"未找到员工",VLOOKUP（15,A3:

E8,2,FALSE））如果有ID为15的员工，则显示该员工的姓氏；否则，显示消息“未找到员工”。

（未找到员工）

　　当VLOOKUP函数返回错误值#NA时，ISNA函数返回值TRUE。

　　=VLOOKUP（4,A2:

E7,2,FALSE）&""&VLOOKUP（4,A2:

E7,3,FALSE）&"是"&VLOOKUP（4,A2:

E7,4,FALSE）&"。

"对于ID为4的员工，将三个单元格的值连接为一个完整的句子。

（潘金是销售代表。

）

　　第一步：

A列是日期，B列是流量数据。

我们在C3和C5单元格中分别输入提示文字“输入日期：

”和“流量IP是：

”。

　　第二步：

按Ctrl键不放并单击A列和B列以全部选中这两列，然后执行“插入→名称→定义”命令调出定义名称对话框，为选中的区域设置好名称（如：

“流量信息”），单击“添加”后再单击“确定”返回。

　　第三步：

点选D5单元格，输入公式：

=IF（ISNA（VLOOKUP（D3,流量信息,2,FALSE））,"没有找到",VLOOKUP（D3,流量信息,2,FALSE））。

　　解释说明：

如果在D5单元格，输入公式：

=VLOOKUP（D3,流量信息,2,FALSE）。

一样可以实现查询，但当D3单元格为空或者输入了不匹配的内容时，D5单元格将显示为“#N/A”，虽不影响使用，但感觉还是不太好。

所有我们结合IF和ISNA函数来使用，这样更合适一些。

　　关于ISNA函数的用法请参考：

excelisna函数的用法和实例，网址是：

　　到此，一个效率奇快的数据查询系统就建立完成了，我们现在只需要在D3单元格输入任意一天的日期，并回车执行,在D5单元格就会显示出这一天的流量数据了。

Excel里怎么自动填充内容？

A1：

A20是编号，B1：

B20是姓名，C1：

C20是性别，当我在A21单元格输入A1：

A20范围内的任意一个编号时，B21出现对应的姓名，C21出现对应的性别。

该如何做，请帮忙。

解答：

B21单元格公式“=IF（A21=0,"",VLOOKUP（A21,A1:

C20,2,FALSE））”；C21单元格公式“=IF（A21=0,"",VLOOKUP（A21,A1:

C20,3,FALSE））”这个公式也适用于A列编号不排序的情况，如果升序的话会更简单一点。

问：

以上公式中的'false'有什么用？

能否省略？

答：

false参数主要是用它以后在A列中的数据可以不是升序排列。

不然如果A列不是升序排列，公式会出错的。

选择要插入“√”的单元格，在字体下拉列表打开工作簿。

在“文件”菜单上，单击“另存为”。

在“工具”菜单上，单击“常规选项”。

在“打开权限密码”或“修改权限密码”框中（或二者都有），双击星号，再按Delete。

单击“确定”，再单击“保存”。

单击“是”以替换原有的工作簿。

单击要更改其数据有效性设置的单元格。

在“数据”菜单中，单击“有效性”。

在每个选项卡上，选定或修改所需的选项。

如果对工作表上其他单元格进行相同的更改，请选中“设置”选项卡上的“对有同样设置的所有其他单元格应用这些更改”复选框。

中选择“Marlett”字体，输入a或b，

取消查看或编辑工作簿文件的密码（Excel）

隐藏工作簿的一部分或全部（Excel）

如果您隐藏了工作簿的一部分，则数据将从视图中移走，但并不从工作簿中删除。

如果保存并关闭了工作簿，下次打开它时隐藏的数据仍然会是隐藏的。

打印工作簿时，MicrosoftExcel不会打印隐藏部分。

为避免其他用户显示隐藏的工作表、行或列，可以使用密码保护工作簿或工作表。

隐藏工作簿和工作表为避免屏幕上的窗口和工作表数量太多，并防止不必要的更改，您可以隐藏工作簿和工作表。

例如，您可以隐藏包含敏感数据的工作表，或隐藏包含宏的工作簿，而隐藏宏将使得该宏可以在不出现宏工作簿窗口的情况下运行。

隐藏的工作簿或工作表是可以访问的，其他文档可以使用其信息。

隐藏行和列您可以隐藏未被使用或不希望其他用户看到的行和列。

隐藏窗口元素为了尽可能多地利用屏幕显示工作表数据，您可以隐藏大多数窗口元素。

这些窗口元素包括MicrosoftWindows任务栏和Excel的标题栏、工具栏、公式栏和状态栏。

只要工作簿是打开的，这些元素就会保持隐藏状态。

当您关闭该工作簿并再次打开它后，这些元素又会重新显示

快速快速计算一个人的年龄（Excel）

Excel中的DATEDIF（）函数可以计算两单元格之间的年、月或日数。

因此，这个函数使得计算一个人的年龄变得容易了。

在一个空白工作表中的A1单元里输入生日，用斜线分隔年、月和日，在A2单元中输入type=DATEDIF（A1,TODAY（）,"y"），然后按Enter，这个人的年龄（以年表示）将被显示在A2单元中

怎样根据条件选择单元格？

单击“编辑→定位”命令，在打开的“定位”对话框中单击“定位条件”按钮，根据你要选中区域的类型，在“定位条件”对话框中选择需要选中的单元格类型（），例如“常量”、“公式”等，此时还可以复选“数字”、“文本”等项目，单击“确定”按钮后符合条件的所有单元格将被选中。

在图表中增加文本框（Excel）

只要你愿意，你可以在图表中的任何地方增加能够移动的文本内容（不限于标题）。

方法为:

选定图表除标题或数据系列外的任何部分，然后在编辑栏中键入文本内容，接着按回车键，这样，图表中就自动生成包含键入内容的文本框，你可以把它移到任何地方并根据自己的喜好进行格式化。

自定义序列填充的操作

如果您输入的序列比较特殊，可以事先加以定义，就能像内置序列那样使用了。

自定义序列的方法是：

单击“工具”菜单中的“选项”命令，打开“选项”对话框中的“自定义序列”选项卡。

在“输入序列”框中输入自定义序列的全部内容，每输入一条就要按一下回车键，完成后单击“添加”按钮。

整个序列输入完毕后，单击对话框中的“确定”按钮。

此后，您只要输入自定义序列中的前两三项，就可以按前面介绍的方法将其填入单元格。

怎么将自定义数据作为排序的次序？

在单元格区域中，按照一定的次序输入主要关键字。

例如，

数据

高

中

低

请选定相应的区域。

在“工具”菜单上，单击“选项”，再单击“自定义序列”选项卡。

单击“导入”，再单击“确定”。

在需要排序的区域中，选择任一单元格。

在“数据”菜单上，单击“排序”。

在“主要关键字”框中，单击需要排序的列。

单击“选项”按钮。

在“自定义排序次序”之下，单击创建的自定义列表。

例如，单击“高、中、低”。

单击“确定”。

选中所需的其他排序选项，再单击“确定”。

注释在“次要关键字”框中无法使用自定义排序次序。

自定义排序只应用于“主要关键字”框中的特定列。

若要用自定义排序次序对多个数据列进行排序，则可以逐列进行排序。

例如，若要根据列A和列B进行排序，请先根据列B排序，然后使用“排序选项”对话框指定自定义排序次序。

接着，根据列A对区域进行排序。

复制单元格的格式的帮助（Excel）

以下操作只复制单元格的格式，例如字体或填充颜色，而不复制单元格的内容。

请选定需要复制的单元格。

单击“常用”工具栏上的“复制”。

选定粘贴区域的左上角单元格。

单击“粘贴”右侧的箭头，再单击“选择性粘贴”。

单击“格式”。

允许直接编辑单元格或限制对编辑栏进行编辑（Excel）

在“工具”菜单上，单击“选项”，再单击“编辑”选项卡。

若要直接在单元格中进行编辑或键入内容，请选中“单元格内部直接编辑”复选框。

若只在编辑栏上进行编辑或键入数据，请清除该复选框。

字体颜色和单元格阴影等格式自动更改的相关问题

如果一个单元格设置了条件格式，那么，MicrosoftExcel是否应用该格式取决于单元格数值或其他被计算的数据是否满足指定的条件。

如果设置的条件格式是基于选定单元格中的数值的，那么包含文本的单元格在运算中被看作ASCII码字符串。

在这种情况下，如果对单元格中的文本进行了修改，那么像粗体或字体颜色这些格式可能会随之改变。

条件格式与通过“单元格”命令（“格式”菜单）或“格式”工具栏按钮设置的格式相比，有更高的优先级。

如果设定的条件为真时，该条件对应的格式会覆盖手动设置的格式。

Excel查询函数中，Lookup和Vlookup有哪些区别？

它们在应用中应该如何把握？

请看本文讲解。

　　★Lookup——数与行列比

　　Lookup的工作职责是什么呢?

用一个数与一行或一列数据依次进行比较，发现匹配的数值后，将另一组数据中对应的数值提取出来。

　　·工资税率表：

用数值比较

　　根据不同的工资进行不同的税率计算是一个常见的应用。

我们来看这张“工资税率查询”表（见图1）。

现在要在右侧根据“收入”（F列），直接得到对应的“税率”（G列）。

在计算第1个“税率”时，输入函数公式“=LOOKUP（F4,$B$3:

$B$8,$D$3:

$D$8）”，回车，便可得到“36.00%”。

　　这个结果是怎么来的?

用F4中的第1个收入数“$123,409”，与左侧表的“收入最低”各档数据（“$B$3:

$B$8”）进行对比，虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是会与其中小于它的最大数“$58,501”相匹配。

这样，同一行对应的“36.00%”就提取出来了。

　　·图书销售表：

用文本比较

　　Lookup函数的对比数还可以是文本。

在这张图书销售查询表中（见图2），用下表输入的“编号”（A15单元格）文本当作查询数，与上表的“编号”一列（$A$3:

$A$11）进行对比，查询到了匹配的文本后，将“教材名称”一列（$B$3:

$B$11）对应的数据提取出来。

公式是“=LOOKUP（A15,$A$3:

$A$11,$B$3:

$B$11）”。

　　★Vlookup——数与表格比

　　Lookup有一个大哥——Vlookup函数。

两兄弟有很多相似之处，但大哥本领更大。

Vlookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行，并且Vlookup可以选择采用精确查询或是模糊查询方式，而Lookup只有模糊查询。

　　·模糊匹配

　　用Vlookup函数进行模糊查询时，几乎与Lookup的作用完全一致。

我们用Vlookup函数来提取第1个例子中的工资税率结果。

函数公式为“=VLOOKUP（F4,$B$3:

$D$8,3,TRUE）”。

　　在这个函数中，用第1个收入“$123,409”（F4单元格）当作对比数，用它与左侧表（“$B$3:

$D$8”）的第1列数进行对比，虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是函数的最后一个参数是“TURE”（“TURE”就是模糊查询），所以它会与其中小于它的最大数“$58,501”相匹配。

并将表中第3列（函数的第3个参数为“3”）对应的数据提取出来，所以结果同样是“36.00%”。

　　·订单明细表：

精确匹配

　　有时候，我们需要精益求精。

在下面这个“订单明细表”（见图3）中，最后一列“货运费用”中的数据要通过“交货方式”从左侧“配送公司收费表”中进行匹配查询。

这是一个典型的精确查询的例子，计算第1个数据的函数公式是“=VLOOKUP（H3,$B$2:

$D$6,3,FALSE）”。

　　小提示：

　　把最后一个参数从“TRUE”变更成“FLASE”，就是精确匹配。

而精确查询，就是查询数要与查询表第1列中的数据完全一致才能匹配提取，否则结果返回错误值“#N/A”。

　　点评：

　　Excel为我们提供了近20个有关“查找和引用”的函数，除了最常用的Lookup、Vlookup，还有Choos、Row、Colum、Index和Match等，大家可以通过函数的帮助查看具体的功能。

这些函数往往不是单独使用，可以与其他函数和Excel中的一些功能进行配合。

　Excel函数可以帮助各位老师用字母A、B、C给学生打成绩。

我们定的标准是80分以上为A，60分至79分为B，60分以下为C。

下面我们看如何实现这一效果。

　　一、利用公式计算等级：

　　在Excel中把成绩录入完成，然后在表头行的最后边再添加上一个“等级”列。

在F2单元格中输入：

=CONCATENATE（IF（C2>=80,"A",IF（C2>=60,"B","C"））,IF（D2>=80,"A",IF（D2>=60,"B","C"））,IF（E2>=80,"A",IF（E2>=60,"B","C"））），然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。

（如图1）

　　也可以在F2单元格中输入：

=IF（C2>=80,"A",IF（C2>=60,"B","C"））&IF（D2>=80,"A",IF（D2>=60,"B","C"））&IF（E2>=80,"A",IF（E2>=60,"B","C"）），然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。

（如图2和图3）

　　二、函数和公式说明：

　　上面的公式中我们主要是利用IF条件函数的嵌套。

IF函数是根据对指定条件的逻辑判断的真假结果，返回相对应的内容的条件函数。

利用此函数逻辑计算出真假值，会返回不同的结果，从而可达到筛选数据的目的。

其语法格式是：

IF（logical_test,value_if_true,value_if_false），logical_test：

表示计算结果为TRUE或者FALSE的任意值或表达式，该参数可使用任何比较运算符;value_if_true：

表示logical_test为TRUE（也就是真或正确）时返回的值;value_if_false：

表示logical_test为FALSE（也就是假或错误）时返回的值。

　　而本例中的公式IF（C2>=80,"A",IF（C2>=60,"B","C"））意思是如果C2单元格中的语文分数大于或等于80分，则在F2单元格中显示字母A，而如果小于80分则再重新判断是否大于等于60分，如果大于或等于60分则在F2单元格中显示字母B，而如果小于60分则显示字母C。

CONCATENATE是将若干个文字项合并至一个文字项中的函数。

其语法格式是：

CONCATENATE（text1,text2……）。

而&是将多个单元格字符串连接成一个字符串的函数。

其语法格式是：

numb1&numb2&……。

这里text1和numb1是指IF（C2>=80,"A",IF（C2>=60,"B","C"）），即语文这列成绩的等级;text2和numb2是指IF（D2>=80,"A",IF（D2>=60,"B","C"）），即数学这列成绩的等级;text3和numb3是指IF（E2>=80,"A",IF（E2>=60,"B","C"）），即英语这列成绩的等级。

用Excel函数核对录入成绩

　　很多老师都有一个头疼的问题，当遇到大型考试时，成绩录入的核对是一项烦琐的工作。

在今年的中招考试中我就遇到了这样的事情。

我把自己的方法与大家共享，希望对你有所帮助。

　　领导为了尽量减少录入错误，要求每一科必须由不同的人录入三次，如果录入的三次成绩有一次不相同就需要重新找出试卷，核查成绩。

一万多个考生，六个科目，怎么核对呢？

正当我愁眉不展之时，Excel中的if函数在脑海中浮现了出来，如果A=B,B=C,A=C不就三次成绩都一样了吗？

我在A列B列C列随意输入了10个数，在D列输入函数“=IF（A8=B8,IF（A8=C8,IF（B8=C8,）））”，结果就出现了两个值“0和FLASE”。

三个数都相同的出现的值是“0”，三个数中有一个不相同的出现的值就是“FLASE”。

对D列数据进行“自动筛选”不就可以把不一样的数值选出来了吗？

　　我在成绩录入完后，用这种方法在不到两个小时的时间内就对一万多名考生、六个科目的试卷录入不合格的考生名单全部打印出来了，而且考号还是按原来顺序排列，查找起来也很方便。

用了不到四个小时的时间就全部核对完毕，又快又准确！

不信你试一试，简单实用。

　这是一位网友提给我的问题，说给大家看看；

　　工作簿内一共有两个工作表Sheet1和Sheet2，先说Sheei1，如下

　　Ａ　　　　　　Ｂ　　　　　　Ｃ

　　货号　序号　　名称

　　101　　1　　　车背带

　　101　　2　　　合前片

　　101　　3　　　合后片

　　101　　4　　　车手带

　　101　　5　　　车边片

　　101　　6　　　合前袋

　　101　　7　　　车后手带

　　202　　1　　　车前片链

　　202　　2　　　车前袋

　　202　　3　　　合包

　　202　　4　　　车后片

　　202　　5　　　车手垫

　　202　　6　　　合前袋

　　表Sheet2如下：

　　Ａ　　　　　　Ｂ　　　　　　Ｃ

　　货号　序号　　名称

　　101　　5　　　[此单元格空]

　　101　　2　　　[此单元格空]

　　101　　3　　　[此单元格空]

　　101　　7　　　[此单元格空]

　　202　　3　　　[此单元格空]

　　202