未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
一些Excel公式的实用运用例子

一些Excel公式的实用运用例子

 

=COUNTIF(D2:D10,">400")
统计D2:D10的值大于400的个数
=COUNTIF(B2:B10,"东北部")
统计B2:B10的内容为"东北部"的个数
=TODAY()
显示当前系统日期
=NOW()
显示当前系统日期和具体时间
=YEAR(B2)
获得B2单元格内(当前系统日期和具体时间)的年
=MONTH(B2)
获得B2单元格内(当前系统日期和具体时间)的月
=DAY(B2)
获得B2单元格内(当前系统日期和具体时间)的日
=HOUR(B2)
获得B2单元格内(当前系统日期和具体时间)的时

=RANK(D2,$D$2:$D$10)
取D2的值在D2-D10范围内的排名是多少
=MATCH(99,C2:C10,0)
统计出C2-C10范围内值为99的个数
=EXACT(A4,B4)
比较A4,B4两个单元格内的字符串内容是否相等,返回布尔值TRUE/FALSE
=IF(C2>=60,IF(C2>=90,"优秀","及格"),"不及格")
如果C2>=60 (如果C2>=90则显示"优秀"否则显示"及格") 否则显示"不及格"
=IF(AND(B2>=60,C2>=60),IF(OR(B2>=90,C2>=90),"优秀","及格"),"不及格")
与上例相似,只不过是2个单元格都要进行条件判断

=VLOOKUP(B3,D2:G14,4,0)
VLOOKUP(需在第一列中查找的数值,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False)
经常用Excel建立一些表格,有时我们需要给一些表格建立很多个副表,那么如何使这些复制表格中的数据随原表的修改而修改呢?VLOOKUP函数可以帮我们做到这一点
=HLOOKUP(B7,B1:F3,2,0)
HLOOKUP与VLOOKUP
HLOOKUP用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值。
VLOOKUP用于在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。
当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用函数 HLOOKUP。
当比较值位于要进行数据查找的左边一列时,请使用函数 VLOOKUP。
语法形式为:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
其中,Lookup_value表示要查找的值,它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。
Table_array查找的区域,用于查找数据的区域,上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。
Row_index_num为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时,返回 table_array 第一行的数值,row_index_num 为 2 时,返回 table_array 第二行的数值,以此类推。
Col_index_num为相对列号。最左列为1,其右边一列为2,依此类推.
Range_lookup为一逻辑值,指明函数 HLOOKUP 查找时是精确匹配,还是近似匹配。
检查单元格 A2 是否为空白 (FALSE) =ISBLANK(A2)
检查 #REF! 是否为错误值 (TRUE)  =ISERROR(A4)
检查 #REF! 是否为错误值 #N/A (FALSE) =ISNA(A4)
检查 #N/A 是否为错误值 #N/A (TRUE) =ISNA(A6)
检查 #N/A 是否为错误值 (FALSE)  =ISERR(A6)
检查 10.72 是否为数值 (TRUE)  =ISNUMBER(A5)
检查 COUNTRY 是否为文本 (TRUE)  =ISTEXT(A3)
检查 5 是否为偶数    ISEVEN(5)  FALSE
检查 -1 是否为奇数    ISODD(-1)  TRUE
 
1.excel两张相同内容合并
execel中sheet1、 sheet2两张表,有部分相同的内容,比如sheet1中a,b,c与sheet2中d,b,e的内容相同,现在要通过这些相同项关联把sheet2中的e,f,h项copy到sheet1中对应的行中。如何用vba语句实现?十分着急!
使用VBA?那是非常简单的语句啊,使用循环即可。
For I = 1 To 100
   For J = 1 To 100 
       If Sheet1.Cells(I,1)=Sheet2.Cells(J,4) And .... Then
           Sheet1.Cells(I,n)=Sheet2.Cells(J,m)
           ...
           Exit For
       End If
   Next J
Next I
以上仅为示例,您稍加改动即可使用。
不用vba用公式就可.
在sheet1中.把a.b.c列的值串起来,放在单独一列,如H 列,做为一个主键值.
在sheet2中把d,e.f...........................如M列............
然后在sheet中求e 列的值
=vlookup(H,sheet2的范围(如:Sheet2!A$1:M$100),3(e列的位置),false);
f列
=vlookup(H,sheet2的范围(如:Sheet2!A$1:M$100),4(f列的位置),false);
g列
=vlookup(H,sheet2的范围(如:Sheet2!A$1:M$100),5(g列的位置),false);

////
2.如何去掉execl单元格中文字前面的数字?
自己写个函数放在模块里,然后在单元格调用函数  =delnum(A1)
Public Function delnum(zifu As String) As String
Dim l As Integer, m As Integer, n As String, a As String
l = Len(zifu)
For m = 1 To l
n = Mid(zifu, m, 1)
If Asc(n) < 48 Or Asc(n) > 57 Then
a = a & n
End If
Next m
delnum = a
End Function
///
3.excel中,列很多,行很少,怎么能让打印在一页上?
使用公式先进行一下转换就是了。
以下为示例:
源数据为数据区域A1:O2,即一个2行15列的数据,如下:
A   B   C   D   E   F   G   H   I   J    K    L    M    N    O
1   2   3   4   5   6   7   8   9   10   11   12   13   14   15
先使用公式转变为6行5列的数据,公式如下:
[假设我们在A6单元格开始输入公式,转变后的数据区域为A6:E11]
在单元格A6输入以下公式:
=INDIRECT(ADDRESS(IF(MOD(ROW(),2)=0,1,2),IF(MOD(COLUMN(),5)=0,5,MOD(COLUMN(),5))+INT((ROW()-6)/2)*5))
并将该公式复制到数据区域A6:E11,我们可以看到,现在数据已经进行了转换。
结果为:
A    B    C    D    E
1    2    3    4    5
F    G    H    I    J
6    7    8    9    10
K    L    M    N     O
11   12   13   14   15

公式说明:
1.由于假定从单元格A6开始,因此IF(MOD(ROW(),2)=0,1,2)的结果为若为偶数行则指向第一行,否则指向第二行。
2.MOD(COLUMN(),5)由于示例中指定了为5列。
3.INT((ROW()-6)/2)*5),示例中是从A6单元格开始的,因此减6行,5为列数。
附加:如果不是正好满列数,那么应该进行一次判断,如下:
=If(Indirect(...)="","",Indirect(...))
[Indirect(...)即上面示例中的公式]
 
4.关于两个excel表格内容进行比较 删除的问题
我现在有两个excel表格,内容均在1000条以上。我现在要在两个表格中找出“非共有”的纪录。  
  例如:表1                                                                   表二  
   
  账号 户名 金额                                   账号 户名 金额  
  12345 ***** *****                           12345 ***** *****  
  23456 ***** *****                           23456 ***** *****    
  4567     ***** *****                           34567 ***** *****  
  56789 ***** *****                           56789 ***** *****  
   
  要在这两个表格中找到表一的   4567       和     表二的   34567
因为数据量较大,手动查找太费事。
假设你的账号数据在sheet1和sheet2的A列,1000行数据
定义2个名称
x=Sheet2!$A2:$A1000
y=Sheet1!$A2:$A1000
在sheet1的A1:A1000使用条件格式-公式,=ISERROR(MATCH(A1,x,0)),设置颜色.
在sheet2的A1:A1000使用条件格式-公式,=ISERROR(MATCH(A1,y,0)),设置颜色.
“非共有”的数据就可以找出来了.
将(你的文件1\2).xls另存为(你的文件1\2).dbf,然后(你的文件1).dbf和(你的文件2).dbf用VFP命令连接:
sele 1
use (你的文件1).dbf
sele 2
use (你的文件2).dbf
join with a for b->帐号=a->帐号 to c
(两个文件帐号必须对应一致,且都不重复,而且两个文件中的:"账号 户名 金额"要在另存为(你的文件1\2).dbf时分别标成:"账号 1户名 1金额";"账号 2户名 2金额")
如果想得到*..xls:只要导出*.xls就行了。
use
copy to 你的文件.xls type xl5 (office版本 xls 5)
 
5.excel里A列为身份证号码,要求在B列得出其出身日期?
A列为个人的身份证号或企业代码,身份证包括2类:15位的身份证,18位身份证。15位(440105610202453)的身份证的生日为1956-10-20;18位&nbsp;(440306197403150053)的身份证生日为1974-03-15。企业代码不满足15位或18位。
现在要求在B列得到A列身份证号人的出生日期;若是企业代码的不需要。
=if(len(A1)=15,"19" & mid(A1,7,2) & "-" & mid(A1,9,2) & "-" & mid(A1,11,2),mid(A1,7,4) & "-" & mid(A1,11,2) & "-" & mid(A1,13,2))
为15位时,应该没2000年后出生的吧所以,以上应该行得通,试试看
当A列是企业代码时,公式有问题.
如:A1=21341231233210,得到的是3123-32-10
公式上做了点修改.
=IF(OR(LEN(A1)={15,18}),IF(LEN(A1)=15,"19" & MID(A1,7,2) & "-" & MID(A1,9,2) & "-" & MID(A1,11,2),MID(A1,7,4) & "-" & MID(A1,11,2) & "-" & MID(A1,13,2)),"")

=IF(LEN(A1)=15,"19" & MID(A1,7,2) & "-" & MID(A1,9,2) & "-" & MID(A1,11,2),IF(LEN(A1)=18,MID(A1,7,4) & "-" & MID(A1,11,2) & "-" & MID(A1,13,2),A1))
当A列是企业代码时,返回原企业代码
本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:虚无—飘渺  > 我的图书馆
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
EXCEL问题大全(2)
Excel的一些使用技巧(2)
EXCEL中如何使用VLOOKUP函数提取单元格字符串中的数值
EXCEL揭秘身份证号内含的秘密,实在是太全了
Excel函数应用实例
【Excel函数篇】你会提取单元格中的手机号吗?
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!