|
Option Explicit
'1 使用循环查找 (在单元格中查找效率太低)
'2 调用工作表函数
Sub c1() '判断是否存在,并查找所在行数
Dim hao As Integer
Dim icount As Integer
icount = Application.WorksheetFunction.CountIf(Sheets('库存明细表').[b:b], [g3]) '调用 Excel 程序的工作表函数 CountIf 统计 B 列中 与G3单元格值相同的单元格的数量
If icount > 0 Then '如果数量大于0
MsgBox '该入库单号码已经存在，请不要重复录入' '弹窗提示
MsgBox Application.WorksheetFunction.Match([g3], Sheets('库存明细表').[b:b], 0) '调用 工作表函数 Match 函数，要匹配的值是 g3 单元格的值，查找的范围是 B 列，匹配方式是 0，即找到第一个匹配的值
End If
End Sub
***********************************************
'3 使用Find方法（上一节有 Find 语法的说明资料）
Sub c2()
Dim r As Integer, r1 As Integer
Dim icount As Integer
icount = Application.WorksheetFunction.CountIf(Sheets('库存明细表').[b:b], [g3])
If icount > 0 Then
r = Sheets('库存明细表').[b:b].Find(Range('G3'), Lookat:=xlWhole).Row
'查找号码第一次出现的位置，Lookat 参数指的是匹配方式，xlWhole是完全匹配，xlPart 是部分匹配，这里显示说明了参数 Lookat 及参数值，与下面的方式不同，下面的方式是通过位置去匹配的。
r1 = Sheets('库存明细表').[b:b].Find([g3], , , , , xlPrevious).Row
'可以参考前一节 find 函数的语法，find 共有9个参数，除了第1个，后面的都是可选参数，但如此例要使用第6个参数时，前面不指定的参数要用逗号表示以匹配参数的位置，后面的则可以省略。
MsgBox r & ':' & r1
End If
End Sub
**********************************************
Sub c3() '返回最下一行非空行的行数
MsgBox Sheets('库存明细表').Cells.Find('*', , , , , xlPrevious).Row
'弹窗信息，在 “库存明细表”里的单元格集合中搜索最一个非空单元格，第1参数是 星号，表示有任意内容的单元格，xlPrevious 是搜索方向向前找，即向下，xlNext向后，即向上
End Sub
==============================
入库单实例图及代码
图1
图2
Option Explicit
Sub 输入() '此过程实现入库单的录入操作
Dim c As Integer '号码在库存表中的个数
Dim r As Integer '入库单的数据行数
Dim cr As Integer '库存明细表中第一个空行的行数
With Sheets('库存明细表') '注意：当前操作的表是 入库单 这个表，即 上图1 ，这里有个 With 的使用，即使用 库存表的属性
c = Application.CountIf(.[b:b], Range('g3')) '在 图2 这个库存表中的 b 列查找 与 图1 入库单 的 G3 单元格 即单号 相匹配的 行数有多少，也就是查找当前单号下有几个商品
If c > 0 Then '如果商品数量大于0
MsgBox '该单据号码已经存在！,请不要重复录入'
Exit Sub
Else '否则，即如果不存在这个单号的话
r = Application.CountIf(Range('b6:b10'), '<>') '获取 B6:B10区域中，非真空即单元格内容不为空的单元格个数，或着当前单据里有商品多少个
cr = .[b65536].End(xlUp).Row 1 '只要用到库存表，因为前已经使用了 with，这里可以省略为 点，表示库存表中 B 列最后一个有效单元格的行号再加 1，即定位到 这个单元格的下方单元格
'如上图2示，即定位到 B8 单元格的行号
图3 图4
参考上图看下面代码
.Cells(cr, 1).Resize(r, 1) = Range('e3') '将入库单（图3）里的日期 写入 到库存表（图4）里
.Cells(cr, 2).Resize(r, 1) = Range('g3') '将入库单的单号写入到库存表
.Cells(cr, 3).Resize(r, 1) = Range('c3') '将入库单的供货公司名写入到库存表
.Cells(cr, 4).Resize(r, 6) = Cells(6, 2).Resize(r, 6).Value '最后将入库单里的 3 个商品 写入到库存表里去
MsgBox '输入已完成'
End If
End With
End Sub
*******************************************************
Sub 查找() '实现入库单查寻功能
Dim c As Integer '号码在库存表中的个数
Dim r As Integer '入库单的数据行数
With Sheets('库存明细表')
c = Application.CountIf(.[b:b], Range('g3')) '同样是先到库存表里 根据单号去查寻此入库单里有几个商品
If c = 0 Then '如果结果是0，表示不存在这个入库单
MsgBox '该单据号码不存在！'
Exit Sub
Else '否则就把获取到的单据商品信息给调到入库单里去
r = .[b:b].Find(Range('g3'), , , , , xlNext).Row '使用 Find 函数，在库存表里向上查找最顶端的与当前单号匹配的行的行号
Range('c3') = .Cells(r, 3) '从库存表中提取公司名到入库对应 单头的 单位位置，即入库单的 C3 单元格
Range('e3') = .Cells(r, 1) '提取日期
Cells(6, 2).Resize(c, 5) = .Cells(r, 4).Resize(c, 5).Value '然后就是此单号下的所有商品
MsgBox '查询已完成'
End If
End With
End Sub
************************************************
Sub 删除() '删除 指定 入库单的操作
Dim c As Integer '号码在库存表中的个数
Dim r As Integer '入库单的数据行数
With Sheets('库存明细表')
c = Application.CountIf(.[b:b], Range('g3')) '在库存表里的 B 列查寻当前单号下商品的数量
If c = 0 Then
MsgBox '该单据号码不存在！'
Exit Sub
Else
r = .[b:b].Find(Range('g3'), , , , , xlNext).Row '库存表中 找到第 1 个商品，并定位它的行号
.Range(r & ':' & c r - 1).Delete '根据定位的行号和商品数量，将它从库存表中删除。
MsgBox '删除已完成'
End If
End With
*********************************************
'由于这不同于 真正的 ERP 系统，在 EXCEL 表中，要修改一个单据信息，只需要先查寻，再删除，然后修改后重新录入一下就好，
'因为这里的删除，只在库存表清除了数据，但入库单里的信息还在，作个修改再录入即可实现修改。
End Sub
Sub 修改()
Call 删除
Call 输入
End Sub
=========================
Excel中MATCH函数是一个很强大的辅助函数，
MATCH函数语法为：MATCH（lookup_value,lookuparray,match-type）
lookup_value：表示查询的指定内容；
lookuparray：表示查询的指定区域；
match-type：表示查询的指定方式，用数字-1、0或者1表示，具体如图：
=============================
关于CountIF 的使用
许多EXCEL问题都涉及数据计数，对于一些简单的计数，通常使用COUNT函数或COUNTA函数就可以解决。然而，在实际的业务处理当中，往往存在诸多条件的限制，仅仅使用简单的统计函数是无法满足人们的实际需求的，例如多条件计数、统计不重复个数等。
例如，在一个EXCEL表格中，D列是考生的数学考试成绩，我们想统计数学成绩及格的人数。可以使用“=COUNTIF(D,'>=60')”来实现。
本例中，COUNTIF函数可以对区域中满足单个条件的单元格区域进行计数。语法如下：
COUNTIF(参数一，参数二)，其中
参数一为需要计算其中满足条件的单元格数目的单元格区域，
参数二是用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。
COUNTIF函数的常见用法如下：（假如数据所在单元格区域命名为“ Data ”）
公 式 说 明
=COUNTIF(Data,'=') 返回真空单元格个数（真空单元格是指什么都没有的单元格，空的单元格）
=COUNTIF(Data,'') 返回真空 假真空单元格个数（假真空是指0字符的空文本）
=COUNTIF(Data,'<>') 返回非真空单元格个数
=COUNTIF(Data,'*') 返回文本型单元格个数
=COUNTIF(Data,'<9.99E 307') 返回数值型单元格个数
=COUNTIF(Data,'<>''') 返回区域内所有单元格个数
=COUNTIF(Data,'<0') 返回偶包含负值的单元格个数
=COUNTIF(Data,'<>0') 返回真不等于零的单元格个个数
=COUNTIF(Data,60) 返回值等于60的单元格个数
=COUNTIF(Data,'>60') 返回值大于60的单元格个数
=COUNTIF(Data,'<60') 返回值小于60的单元格个数
=COUNTIF(Data,'>=60') 返回值大于等于60的单元格个数
=COUNTIF(Data,'<=60') 返回值小于等于60的单元格个数
=COUNTIF(Data,A1) 返回值与A1单元格内容相同的单元格个数
=COUNTIF(Data,'>'&A1) 返回值大于A1单元格内容的单元格个数
=COUNTIF(Data,'<'&A1) 返回值小于A1单元格内容的单元格个数
=COUNTIF(Data,'???') 返回字符等于3的单元格个数
=COUNTIF(Data,'YDL') 返回值等于YDL的单元格个数
=COUNTIF(Data,'YDL?') 返回以字母YDL开头且字符数等于4的单元格个数
=COUNTIF(Data,'YDL*') 返回以字母YDL开头的文本单元格的个数
=COUNTIF(Data,'?YDL*') 返回第2，3，4字符为YDL的单元格个数
=COUNTIF(Data,'*YDL*') 返回含的YDL字符的单元格个数
=COUNTIF(Data,'*'&A1&'*') 返回包含A1单元格内容的文本单元格个数
=COUNTIF(Data,TODAY()) 返回值等于当前日期的单元格个数
=COUNTIF(Data,'>'&AVERAGE(Data)) 返回大于均值的单元格个数
=SUM(COUNTIF(Data,'>'&{10,15})*{1,-1}) 返回大于10小于等于15的单元格个数
=SUM(COUNTIF(Data,{TRUE,FALSE})) 返回包含逻辑值的单元格个数
特别指出的是，在EXCEL2010中，新增了一个多条件计数函数，那就是“COUNTIFS” ，假如在一个EXCEL表格中，D350单元格的内容是职工的年龄，E3:E50单元格的内容是是否有房，F3:F50单元格的内容是是否有车，那么统计职工中35岁以上有房有车的人数应该用如下公式：
COUNTIFS(D350,'>35',E3:E50,'是',F3:F50,'是')
|
