在excel中数据公式怎么设置副本Word格式.docx
- 文档编号:6065493
- 上传时间:2023-05-06
- 格式:DOCX
- 页数:17
- 大小:25.62KB
在excel中数据公式怎么设置副本Word格式.docx
《在excel中数据公式怎么设置副本Word格式.docx》由会员分享,可在线阅读,更多相关《在excel中数据公式怎么设置副本Word格式.docx(17页珍藏版)》请在冰点文库上搜索。
确定"
即可。
四、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色
比如:
A1〉1时,C1显示红色
0<
A1<
1时,C1显示绿色
0时,C1显示黄色
方法如下:
1、单元击C1单元格,点“格式”>
“条件格式”,条件1设为:
公式=A1=1
2、点“格式”->
“字体”->
“颜色”,点击红色后点“确定”。
条件2设为:
公式=AND(A1>
0,A1<
1)
3、点“格式”->
“颜色”,点击绿色后点“确定”。
条件3设为:
公式=A1<
0
点“格式”->
“颜色”,点击黄色后点“确定”。
4、三个条件设定好后,点“确定”即出。
五、EXCEL中如何控制每列数据的长度并避免重复录入
1、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围,点"
数据"
有效性"
设置"
,"
有效性条件"
设成"
允许"
文本长度"
等于"
5"
(具体条件可根据你的需要改变)。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"
2、用条件格式避免重复。
选定A列,点"
条件格式"
,将条件设成“公式=COUNTIF($A:
$A,$A1)>
1”,点"
字体"
颜色"
,选定红色后点两次"
这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。
六、在EXCEL中如何把B列与A列不同之处标识出来?
(一)、如果是要求A、B两列的同一行数据相比较:
假定第一行为表头,单击A2单元格,点“格式”->
“条件格式”,将条件设为:
“单元格数值”“不等于”=B2
“颜色”,选中红色,点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
(二)、如果是A列与B列整体比较(即相同数据不在同一行):
“公式”=COUNTIF($B:
$B,$A2)=0
按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。
七、在EXCEL中建立下拉列表按钮
选定你要设置下拉列表的单元格,点“数据”->
“有效性”->
“设置”,在“允许”下面选择“序列”,在“来源”框中输入你的下拉列表内容,各项之间用半角逗号隔开,如:
A,B,C,D
选中“提供下拉前头”,点“确定”。
十、巧用函数组合进行多条件的计数统计
例:
第一行为表头,A列是“姓名”,B列是“班级”,C列是“语文成绩”,D列是“录取结果”,现在要统计“班级”为“二”,“语文成绩”大于等于104,“录取结果”为“重本”的人数。
统计结果存放在本工作表的其他列。
公式如下:
=SUM(IF((B2:
B9999="
二"
)*(C2:
C9999>
=104)*(D2:
D9999="
重本"
),1,0))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"
{}"
十二、如何在EXCEL中引用当前工作表名
如果你的工作薄已经保存,下面公式可以得到单元格所在工作表名:
=RIGHT(CELL("
filename"
),LEN(CELL("
))-FIND("
]"
CELL("
)))
十三、相同格式多工作表汇总求和方法
假定同一工作薄有SHEET1至SHEET100共100个相同格式的工作表需要汇总求和,结果放在SHEET101工作表中,请在SHEET101的A1单元格输入:
=SUM(
单击SHEET1标签,按住Shift键并单击SHEET100标签,单击A1单元格,再输入:
)
此时公式看上去内容如下:
=SUM('
SHEET1:
SHEET100'
!
A1)
按回车后公式变为
=SUM(SHEET1:
SHEET100!
所以,最简单快捷的方法就是在SHEET101的A1单元格直接输入公式:
然后按回车。
十九、EXCEL中如何动态地引用某列的最后一个单元格?
在SHEET2中的A1单元格中引用表SHEET1中的A列的最后一个单元格中的数值(SHEET1中A列的最后一个单元格的数值不确定,随时会增加行数):
=OFFSET(Sheet1!
A1,COUNTA(Sheet1!
A:
A)-1,0,1,1)
或者:
=INDIRECT("
sheet1!
A"
&
COUNTA(Sheet1!
A))
注:
要确保你SHEET1的A列中间没有空格。
二十、如何在一个工作薄中建立几千个工作表
右击某个工作表标签,点"
插入"
选择"
工作表"
点"
然后按住Alt+Enter键不放,你要多少个你就按住多久不放,你会看到工作表数量在不断增加,几千个都没有问题。
二十一、如何知道一个工作薄中有多少个工作表
方法一:
点"
宏"
VB编辑器"
模块"
输入如下内容:
Subsheetcount()
DimnumAsInteger
num=ThisWorkbook.Sheets.Count
Sheets
(1).Select
Cells(1,1)=num
EndSub
运行该宏,在第一个(排在最左边的)工作表的A1单元格中的数字就是sheet的个数。
方法二:
按Ctrl+F3(或者点"
名称"
定义"
),打开"
定义名称"
对话框
定义一个X
引用位置"
输入:
=get.workbook(4)
然后你在任意单元格输入=X
出来的结果就是sheet的个数。
3、快速显示单元格中的公式
如果工作表中的数据多数是公式生成的,如果想要快速知道每个单元格中的公式形式,可以这样做:
用鼠标左键单击“工具”菜单,选取“选项”命令,出现“选项”对话框,单击“视图”选项卡,接着设置“窗口选项”栏下的“公式”项有效,单击“确定”按扭。
这是每个单元格中的公式就显示出来了,再设置“窗口选项”栏下的“公式”项失效即可。
9、用下拉列表快速输入数据
如果你希望减少手工录入的工作量,可以用下拉列表来实现。
创建下拉列表的方法为:
首先,选中需要显示下拉列表的单元格或单元格区域;
接着,选择“数据”菜单中的的“有效性”命令,从有效数据对话框中选择“序列”,单击“来源”栏右侧的小图标,将打开一个新的“有效数据”小对话框;
接着,在该对话框中输入下拉列表中所需要的数据,项目和项目之间用逗号隔开。
注意在对话框中选择“提供下拉箭头”复选框;
最后单击“确定”即可。
表格做好后,如何固定行和列的宽度
首先选择全部工作表格,按右键,选择-->
设置单元格格式-->
保护-->
把锁定前面的勾去掉-->
按确定。
选择菜单-->
工具-->
保护工作表-就可以看到已经自动打上最上面的三个勾了,直接按确定就可以了。
--------------------------------------
如何设置方格中的数据是不能改动的
首先要先把楼上的这条设置好后,接下来再设置这个。
选择一个或多个不能改动的方格里的数据,按右键,选择-->
保护--->
在锁定前面打上勾-->
按确定-->
OK。
如何在行和列中自动显示录入相同的数据
设你的数据列在A列
第一种方法:
A列全选,菜单-->
数据-->
有效性-->
设置-->
允许-->
在公式栏输入:
=COUNTIF(a:
a,a1)=1
此方法当输入重复值时会出现错误提示窗口。
第二种方法:
A列全选,菜单-->
格式-->
条件格式-->
最左选"
公式"
,输入:
a,a1)=2
然后设置格式(建议只要设置格式里面的颜色)。
此方法当输入重复值时,重复的两个值的单元格会显示你设置的格式颜色,不会出现错误提示。
提示:
以上二种方法公式里的字母a都是代表列,如在不同的行和列中,此公式的三个字母都需要改成相应的字母和数字。
我个人喜欢用第一种方式。
---以上方法也可以跨行跨列的数据比较---
五、让EXCEL也“自动编号” 我们都知道,EXCEL的自动填充是它的一大特色功能。
利用它来进行序号的“自动编制”,简直就像是量体裁衣一样方便。
一般自动填充的方法都是用鼠标左键指向填充柄,按住鼠标向下拖动完成的(填充柄是位于选定区域角上的小黑块,将鼠标指向填充柄时,鼠标的形状变为黑十字。
拖动填充柄可以将内容复制到相邻单元格中,或填充日期系列)。
如果表格有太多的行,这种方法不便掌握拖动的距离。
这里介绍一种
本人摸索的更简单的操作方法:
用鼠标左键双击填充柄。
一切OK!
注意:
这样做有时候不能达到预期的填充效果。
还必须满足下列条件:
1、只能纵向填充不能横向填充。
2、欲填充的列的左(右)相邻列非空,且中间无间断,否则以上填充将在间断处停止。
有3、该方法对文本形式的序列也起作用。
六、自动定位小数位
因为有一大批诸如123.5之类的学生成绩需要录入,如果录入前先进行下面的设置,将会使你的输入速度成倍提高(可以不需要输入小数点)。
单击“工具”→“选项”→“编辑”选项卡,选中“自动设置小数点”复选框,在“位数”微调编辑框中键入需要显示在小数点右面的位数。
在此,我们键入“1”(如图3)。
单击“确定”按钮。
现在,在工作表的某单元格中键入“1235”,则在你按了回车键之后,该单元格的数字自动变为“123.5”。
方便多了吧!
此时如果你在单元格中键入的是“89”,则在你结束输入之后,该单元格中的数字自动变为“89.0”。
另外,如果你在“位数”框中输入一个负数,比如“-1”,则Excel将在输入的数字后自动添上两个“0”。
如果要暂时取消在“自动设置小数点”功能中设置的选项,可以在输入的数据中自带小数点。
利用Excel的“自动设置小数点”的功能,对于大量带有固定小数位的数字,或带有固定位数的以“0”字符串结尾的数字的输入,将大大提高编辑速度。
七、让表格个性化 为了提高工作效率,越干越起劲,同时好为了避免录入时间过长后出现差错。
选中整个工作表,单击格式菜单下的“自动套用格式……”,出现“自动套用格式”对话框,选择一种比较你所喜欢的方案,单击[选项]按钮,展开对话框下部的“应用
格式种类”选项框,为了保持前面的诸多设置,必须去掉“数字”、“对齐”、“边框”等复选项前的选择,只复选“字体”和“图案”两项,[确定]后完成设置。
[提示:
在表格中,给单元格加上不同的颜色和底纹可以增强数据输入时的直观感觉,但在黑白打印时如果连颜色和底纹都打出来,表格的可视性就大打折扣了,
因此用户希望在数据处理时色彩缤纷,打印时仍旧是普通的白纸黑字。
可以在“文件”菜单中选择“页面设置→工作表→打印→单元格单色打印”,这样打印的表格
就是普通的黑白表格了
21.批量修改数据
在EXCEL表格数据都已被填好的情况下,如何方便地对任一列(行)的数据进行修改呢?
比如我们做好一个EXCEL表格,填好了数据,现在想修改其中的一列(行),例如:
想在A列原来的数据的基础上加8,有没有这样的公式?
是不是非得手工的一个一个数据地住上加?
对于这个问题我们自然想到了利用公式,当你利用工式输入A1=A1+8时,你会得到EXCEL的一个警告:
“MICROSOFT EXCEL不能计算该公式……”只有我们自己想办法了,这里介绍一种简单的方法:
第一步:
在想要修改的列(假设为A列)的旁边,插入一个临时的新列(为B列),并在B列的第一个单元格(B1)里输入8。
第二步:
把鼠标放在B1的或下角,待其变成十字形后住下拉直到所需的数据长度,此时B列所有的数据都为8。
第三步:
在B列上单击鼠标右键,“复制”B列。
第四步:
在A列单击鼠标的右键,在弹出的对话框中单击“选择性粘贴”,在弹出的对话框中选择“运算”中的你所需要的运算符,在此我们选择“加”,这是本方法的关键所在。
第五步:
将B列删除。
怎么样?
A列中的每个数据是不是都加上了8呢?
同样的办法可以实现对一列(行)的乘,除,减等其它的运算操作。
原表格的格式也没有改变。
此时整个工作结束,使用熟练后,将花费不到十秒钟
22.将Excel数据导入Access
如果想将Excel中的数据转换到Access中,可以采取下面的直接导入法和建立链接法来完成。
一、直接导入法
1.启动Access,新建一数据库文件。
2.在“表”选项中,执行“文件→获取外部数据→导入”命令,打开“导入”对话框。
3.按“文件类型”右侧的下拉按钮,选中“MicrosoftExcel(.xls)”选项,再定位到需要转换的工作簿文件所在的文件夹,选中相应的工作簿,按下“导入”按钮,进入“导入数据表向导”对话框(图1)。
4.选中需要导入的工作表(如“工程数据”),多次按“下一步”按钮作进一步的设置后,按“完成”按钮。
注意:
如果没有特别要求,在上一步的操作中直接按“完成”按钮就行了。
5.此时系统会弹出一个导入完成的对话框(图1的中部),按“确定”按钮。
至此,数据就从Excel中导入到Access中。
二、建立链接法
2.在“表”选项中,执行“文件→获取外部数据→链接表”命令,打开“链接”对话框。
3.以下操作基本与上述“直接导入法”相似,在此不再赘述,请大家自行操练。
“直接导入法”和“建立链接法”均可以将Excel数据转换到Access中,两者除了在Access中显示的图标不同(图2)外,最大的不同是:
前者转换过来的数据与数据源脱离了联系,而后者转换过来的数据会随数据源的变化而自动随时更新。
23.办公技巧:
Excel定时提醒不误事
如果您从事设备管理工作,有近千台机械设备需要定期进行精度检测,那么,就得每天翻阅“设备鉴定台账”来寻找“到期”的设备——实在是太麻烦了!
用Excel建立一本“设备鉴定台账”是不是方便得多?
方法是:
用Excel的IF函数嵌套TODAY函数来实现设备“到期”自动提醒。
首先,运行Excel,将“工作簿”的名称命名为“设备鉴定台账”,输入各设备的详细信息、上次鉴定日期及到期日期(日期的输入格式应为“年-月-日”,如:
2003-10-21,如图1)。
然后,选中图1所示“提示栏”下的F2单元格,点击插入菜单下的函数命令,在“插入函数”对话框中选择“逻辑”函数类中的IF函数,点击[确定]按钮,就会弹出“函数参数”对话框,分别在Logical_test行中输入E2=TODAY()、value_if_true行中输入“到期”、Value_if_false行中输入“"
"
”(如图2),并点击[确定]按钮。
这里需要说明的是:
输入的"
是英文输入状态下的双引号,是Excel定义显示值为字符串时的标识符号,即IF函数在执行完真假判断后显示此双引号中的内容。
为了醒目,可在“单元格属性”中将F2单元格的字体颜色设置为红色。
最后,拖动“填充柄”,填充F列以下单元格即可。
我们知道Excel的IF函数是一个“条件函数”,它的语法是“IF(logical_test,value_if_true,value_if_false)”,具体地说就是:
如果第一个参数logical_test返回的结果为真,则执行第二个参数Value_if_true的结果,否则执行第三个参数Value_if_false的结果;
Excel的TODAY函数[语法是TODAY()]是返回当前系统日期的函数。
实际上,本文所应用的IF函数语句为IF(E2=TODAY(),"
到期"
"
),解释为:
如果E2单元格中的日期正好是TODAY函数返回的日期,则在F2单元格中显示“到期”,否则就不显示,TODAY函数返回的日期则正好是系统当天的日期。
24.办公小绝招构造Excel动态图表
(1)
Excel中的窗体控件功能非常强大,但有关它们的资料却很少见,甚至Excel帮助文件也是语焉不详。
本文通过一个实例说明怎样用窗体控件快速构造出动态图表。
假设有一家公司要统计两种产品(产品X,产品Y)的销售情况,这两种产品的销售区域相同,不同的只是它们的销售量。
按照常规的思路,我们可以为两种产品分别设计一个图表,但更专业的办法是只用一个图表,由用户选择要显示哪一批数据——即,通过单元按钮来选择图表要显示的数据。
为便于说明,我们需要一些示例数据。
首先在A列输入地理区域,如图一,在B2和C2分别输入“产品X”和“产品Y”,在B3:
C8区域输入销售数据。
一、提取数据
接下来的步骤是把某种产品的数据提取到工作表的另一个区域,以便创建图表。
由于图表是基于提取出来的数据创建,而不是基于原始数据创建,我们将能够方便地切换提取哪一种产品的数据,也就是切换用来绘制图表的数据。
在A14单元输入=A3,把它复制到A15:
A19。
我们将用A11单元的值来控制要提取的是哪一种产品的数据(也就是控制图表要描述的是哪一批数据)。
现在,在A11单元输入1。
在B13单元输入公式=OFFSET(A2,0,$A$11),再把它复制到B14:
B19。
OFFSET函数的作用是提取数据,它以指定的单元为参照,偏移指定的行、列数,返回新的单元引用。
例如在本例中,参照单元是A2(OFFSET的第一个参数),第二个参数0表示行偏移量,即OFFSET返回的将是与参照单元同一行的值,第三个参数($A$11)表示列偏移量,在本例中OFFSET函数将检查A11单元的值(现在是1)并将它作为偏移量。
因此,OFFSET(A2,0,$A$11)函数的意义就是:
找到同一行且从A2(B2)偏移一列的单元,返回该单元的值。
25.办公小绝招构造Excel动态图表
(2)
现在以A13:
B19的数据为基础创建一个标准的柱形图:
先选中A13:
B19区域,选择菜单“插入”→“图表”,接受默认的图表类型“柱形图”,点击“完成”。
检查一下:
A13:
B19和图表是否确实显示了产品X的数据;
如果没有,检查你是否严格按照前面的操作步骤执行。
把A11单元的内容改成2,检查A13:
B19和图表都显示出了产品B的数据。
1.Excel表中用身份证号码中取其中的号码用:
MID(文本,开始字符,所取字符数);
2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。
MID()——从指定位置开始提取指定个数的字符(从左向右)。
对一个身份证号码是15位或是18位进行判断,用逻辑判断函数IF()和字符个数计算函数LEN()辅助使用可以完成。
综合上述分析,可以通过下述操作,完成形如1978-12-24样式的出生年月日自动提取:
假如身份证号数据在A1单元格,在B1单元格中编辑公式
=IF(LEN(A1)=15,MID(A1,7,2)&
-"
MID(A1,9,2)&
MID(A1,11,2),MID(A1,7,4)&
MID(A1,11,2)&
MID(A1,13,2))
回车确认即可。
如果只要“年-月”格式,公式可以修改为
MID(A1,9,2),MID(A1,7,4)&
MID(A1,11,2))
3.这是根据身份证号码(15位和18位通用)自动提取性别的自编公式,供需要的朋友参考:
说明:
公式中的B2是身份证号
根据身份证号码求性别:
=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"
女"
男"
),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,1)),2)=0,"
),"
身份证错"
))
根据身份证号码求年龄:
=IF(LEN(B2)=15,2007-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2007-VALUE(MID(B2,7,4)),"
4.Excel表中用Year\Month\Day函数取相应的年月日数据;
另一方法:
这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:
1、根据身份证号码求性别:
),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"
2、根据身份证号码求出生年月:
=IF(LEN(B2)=15,CONCATENATE("
19"
MID(B2,7,2),"
."
MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),"
MID(B2,11,2)),"
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 在excel中数据公式怎么设置 副本 excel 数据 公式 怎么 设置