-
2019-05-10 21:01:52
Excel 下拉列表数据有效性智能匹配筛选
来源公式如下(适用于2010以上版本)
=OFFSET(数据!$A$1,MATCH(INDIRECT("R"&ROW()&"C"&COLUMN(),)&"*",数据!$A:$A,0)-1,,COUNTIF(数据!$A:$A,INDIRECT("R"&ROW()&"C"&COLUMN(),)&"*"))
对于2010版本以下的excel,不能跨工作表引用sheet ,可如下设置
点击【公式】下的【对应名称】,设置范围名称
"a"对应上面的 "数据!$A$1"
"aa"对应上面的 "数据!$A:$A"
=OFFSET(a,MATCH(INDIRECT("R"&ROW()&"C"&COLUMN(),)&"*",aa,0)-1,,COUNTIF(aa,INDIRECT("R"&ROW()&"C"&COLUMN(),)&"*"))
点击保存时会提示错误,忽略!
更多相关内容 -
数据有效性 动态选择
2018-06-12 08:17:26EXCEL有两列数据栏,A列和B列,都是通过下来框来选择,要求A列选择完成后,B列动态调整可选择的内容。例如:第一例选择“AA”,第二列可供选择的范围是“a-d”;第一例选择“BB”,第二列可供选择的范围是“e-h”;...EXCEL有两列数据栏,A列和B列,都是通过下来框来选择,要求A列选择完成后,B列动态调整可选择的内容。例如:第一例选择“AA”,第二列可供选择的范围是“a-d”;第一例选择“BB”,第二列可供选择的范围是“e-h”;第一例选择“CC”,第二列可供选择的范围是“i-w”;第一例选择“DD”,第二列可供选择的范围是“w-z”;
AA
a
b
c
d
BB
e
f
g
h
CC
i
j
k
w
DD
w
x
y
z
首先设置第一列的数据有效性:
图一
接着设置第二列的数据有效性
图二
公式比较复杂:
=OFFSET($F$13:$I$16,VLOOKUP(INDIRECT("A"&ROW()),$D$14:$E$17,2,0),0,1,4)
解读如下:
先看一下基础数据区
基础数据区的信息,范围为:D14:I17
AA
1
a
b
C
d
BB
2
e
f
G
h
CC
3
i
j
K
w
DD
4
w
x
Y
z
公式中用了两层嵌套,在OFFSET函数中嵌套了VLOOKUP函数,在VLOOKUP函数中嵌套了INDIRECT函数。INDIRECT函数用来读取同行第一列选择的数值,VLOOKUP用来返回偏移量,
OFFSET实现偏移。
新增一列作为辅助列,填入序号“1-4”。基础数据区域的第一行作为起始行。
运行结果:
-
设置excel中数据有效性不被复制黏贴破坏
2019-03-18 15:43:18Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim c As String For Each rng In Target If Not rng.Validation.Value Then Application.Undo Dim msg As String msg = "粘贴的数据不符合...加入vbs脚本编程,同时开启宏。但是如果用户电脑不开启宏,意味着生成的excel中vbs脚本失效。操作步骤如下:
点击开发工具,启动宏,如果wps的话开启需要安装插件
点击根据步骤1,2,3将vbs代码复制黏贴进去,vbs代码在文章底部。
重点!重点!重点!最后记得要加密整个工作簿,可以编辑的单元格要设置取消锁定(选中单元格,鼠标右键后点击单元格格式)。
vbs代码如下:Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim c As String For Each rng In Target If Not rng.Validation.Value Then Application.Undo Dim msg As String msg = "粘贴的数据不符合校验规则:位置在第" & rng.Row & "行,第" & getColumnName(rng.column) & "列,请仔细检查" MsgBox prompt:=msg, Title:="输入提示" Exit For End If Next End Sub Private Function getColumnName(column As Integer) As String Dim alphabet(26) As String alphabet(0) = "A" alphabet(1) = "B" alphabet(2) = "C" alphabet(3) = "D" alphabet(4) = "E" alphabet(5) = "F" alphabet(6) = "G" alphabet(7) = "H" alphabet(8) = "I" alphabet(9) = "J" alphabet(10) = "K" alphabet(11) = "L" alphabet(12) = "M" alphabet(13) = "N" alphabet(14) = "O" alphabet(15) = "P" alphabet(16) = "Q" alphabet(17) = "R" alphabet(18) = "S" alphabet(19) = "T" alphabet(20) = "U" alphabet(21) = "V" alphabet(22) = "W" alphabet(23) = "X" alphabet(24) = "Y" alphabet(25) = "Z" If (column < 27) Then getColumnName = alphabet(column - 1) Else Dim i, j As Integer i = column \ 26 j = column Mod 26 If (i < 26) Then getColumnName = alphabet(i - 1) & alphabet(j - 1) Else getColumnName = column End If End If End Function
-
EXCEL数据有效性的多级联动
2020-09-25 11:56:02今天被人请教了怎么设置excel数据有效性的多级联动,长期不用excel手生的很,于是乎今天又温故知新了一把。 需求如下: 解决方案: 第一步,定义名称 1.CTRL+G 选择“定位条件” 2.选择“常量”并确定 3选择...今天被人请教了怎么设置excel数据有效性的多级联动,长期不用excel手生的很,于是乎今天又温故知新了一把。
需求如下:
解决方案:
第一步,定义名称
1.CTRL+G 选择“定位条件”
2.选择“常量”并确定
3选择“公式”、“根据所选内容创建”、“首行”
4.打开名称管理器可以看到定义的名称
第二步,在需要的地方引用名称
1.一级有效性,选择“数据”“数据验证”“序列”,填写公式 =老师(定义的名称)
2.二级有效性,选择“数据”“数据验证”“序列”,填写公式 =INDIRECT($A2)
3.其他级别可参考2级有效性
-
Excel 用函数自定义验证数据的有效性
2018-12-29 09:09:59Excel 数据验证也叫数据有效性,它可以控制单元格内输入的内容,让数据的输入更加准确、规范,的确为表单制作的一个神器。本文除了介绍常用功能外,还列举了一堆自定义的函数供你验证数据。 一、常用功能 验证... -
Excel 多级下拉菜单设置,数据有效性
2020-05-23 10:01:331、首先选中二级数据,进入「公式」-「定义的名称」-「根据所选内容创建」,在对话框中将「最左列」前面方框里的勾取消掉,只保留「首行」前面的勾,点击确定; 2、首先选中三级数据,进入「公式」-「定义的名称... -
VBA设置数据有效性(取列数据)
2019-04-11 16:57:58On Error Resume Next 'site With Sheets("site").Columns(4).Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=tmpl!$A:$A" End Wit... -
Excel学习笔记(1)——数据类型,自动填充,数据有效性,美化
2016-10-06 13:52:41《EXCEL 2010从入门到精通》一、数据类型1、文本 输入数值型的文本数据,在前面先加一个单引号’ 例如:’1234567890 2、数字(数值) 输入分数:0 1/3(先输入0 + 空格,再输入分数) 3、日期、时间 / 或 - ... -
【Excel】设置数据有效性以及COUNTIF用法
2017-12-03 12:07:32下面我想介绍的是如何设置数据有效性,以及对某个数据进行个数统计,具体我们通过以下两个例子详细说明一下。 设置数据有效性 我们经常用Excel设计测试用例,为了跟踪测试用例的状态,我们会对用例执行情况进行... -
【Excel】某列数据有效性根据另外一列数据有效性进行动态更改
2013-09-03 10:06:163)选中C2,【数据】→【数据工具】→【数据有效性】→【设置】→【序列】,然后输入公式:“=IF(B2="一类",一类的二级分类,IF(B2="二类",二类的二级分类,IF(B2="三类",三类的二级分类)))” 4)选中C2,ctrl+c,... -
使用POI为Excel添加数据有效性验证
2015-06-26 10:24:38根据客户要求需要添加数据有效性验证。但是在查找多方资料后发现,POI无法读取到文件中的有效性验证,只能添加。无奈之下只能通过配置文件的方式去添加了。 给Excel添加数据有效性的验证,xls格式和xlsx格式不同,... -
数据结构完整性是指数据的正确性、有效性、相容性!
2018-09-26 21:08:06数据的完整性是指数据的正确性、有效性和______。 A.可维护性 B.独立性 C.安全性 D.相容性 答案:D [评析] 数据模型的完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则,用以限定符合数据模型... -
excel单元格数据有效性自定义
2014-11-02 10:30:50菜单 “数据” -> “有效性” -> “设置” -> “允许” -> “序列” -> “来源” -> “无,有” -> “确定” (2)在J1单元格加入有效性检查 菜单 “数据” -> “有效性” -> “设置” -> “允许” -> “自定义” -> ... -
数据库数据连接有效性检测
2018-09-20 13:44:49想要增加对连接池中连接的测试/验证,防止数据库认为连接已死而Web应用服务器认为连接还有效的问题,到底是使用testQuery还是validationQuery取决于连接池的的实现: 连接池类型 该功能属性名 The Tomcat JDBC ... -
Excel实现数据项校验的功能---VBA的编写以及数据有效性的设置
2017-03-24 13:56:492.使用数据有效性检查输入的是否大写 公式: =NOT(ISERROR(SUM(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))(A1))) 解决方案来自: Excel 有什么办法设置单元格只能输入... -
Excel设置数据有效性实现单元格下拉菜单的3种方法
2014-02-10 17:45:40Excel设置数据有效性实现单元格下拉菜单的3种方法 一、直接输入: 1.选择要设置的单元格,譬如A1单元格; 2.选择菜单栏的“数据”→“有效性”→出现“数据有效性”弹出窗口; 3.在“设置”选项中→“有效性条件”... -
如何利用Excel数据有效性制作多级联动下拉列表
2013-12-19 16:13:51相信很多朋友都想通过Excel数据有效性实现多级联动下拉列表的制作,多级联动下拉列表就是第一列选择了某项,第二列则只会提供相对应的选项供我们选择,这个在网页中可以经常看到。 现在EXCEL也可以实现这样的效果... -
EXCEL数据有效性-允许序列-数据来源如何设置为其它工作表的一大连续区
2014-11-07 13:49:00数据有效性中写入公式就行 =indirect("sheet2!A1:A100") jurgzi : 个人认为,使用=indirect的方式方便一点,不需要去定义区域 因为如果要做很多不同的序列,定义区域的话就很麻烦了 而且=indirect("sheet2!... -
execl2010数据有效性验证,保存后丢失问题
2013-07-09 09:20:50在excel2010中,对某些列做了数据有效性验证,保存成2013后,验证丢失; 解决方案: 1、给引用的区域命名(该区域是另一个sheet中的) 2、在数据有效性中引用该命名区域 注意一定要用等号,否则不能... -
探索性数据分析
2019-01-05 21:15:22探索性数据分析(Exploratory Data Analysis,EDA)是指对已有数据在尽量少的先验假设下通过作图、制表、方程拟合、计算特征量等手段探索数据的结构和规律的一种数据分析方法,该方法在上世纪70年代由美国统计学家J.... -
防止excel单元格有效性验证因被粘贴而失效
2017-06-09 17:04:03msg = "粘贴的数据不符合校验规则:位置在第" & rng.Row & "行,第" & getColumnName(rng.column) & "列,请仔细检查" MsgBox prompt:=msg, Title:="输入提示" Exit For End If Next End Sub Private Function... -
Excel2007中数据有效性的序列来源怎样引用其他工作表单元格内的数据?
2011-08-29 17:01:06因为数据有效性中不能直接引用别的工作表的内容,故而首先必须将sheet2的A列内容(假设在A1:A10中)定义为一个名称,然后设置sheet1 A2单元格的数据有效性,具体操作如下: 1.按ctrl+F3打开定义名称对话框,在... -
数据库中的数据完整性(实体完整性、域完整性、引用完整性)
2019-05-06 17:56:51确保数据的完整性 = 在创建表时给表中添加约束 完整性的分类: 实体完整性: 域完整性: 引用完整性: 4.1 实体完整性 实体:即表中的一行(一条记录)代表一个实体(entity) 实体完整性的作用:标识... -
EXCEL二级数据有效性的设置问题
2008-12-08 20:48:00一、在A1:A2中输入高中、初中,并设置成“类别”名称”(选中A1:A2,在名称框〔就是平时用来显示单元地址的框,通常位置在“打开”、“保存”的图标的下方...二、选择D1单元格,菜单“数据”/“有效性”/“允许”中选 -
access 有效性规则和有效性文本
2013-07-12 10:49:48有效性规则是对输入数据的约束 比如在有效性规则中输入"男" or"女"那么输入的数据只能为男或女,而有效性文本是指在 输入文本不为男或女时出现的错误指示 -
2007Excel中引用其它工作表数据作为有效性
2011-12-08 10:40:071.把Sheet2中的数据,定义为名称 作法~ 1)把数据全选起来 ...4.按[数据]>[有效性]>选[设置]页 5.在[有效性条件 允许]选[序列] 6.鼠标定位在[来源]中,按[公式]>[用于公式]>下拉,选择[数据]>按[确定] -
无偏估计、有效性、相合性
2019-07-03 15:28:54首先,前面的参数估计方法是采用采样得到的数据进行估计的,如x1,x2,x3...xn是采样得到的数据,而 g(x1,x2,x3,...,xn)是样本数据的函数,基本上参数估计方法都是类似函数g,而数据中xi每个都是一个随机变量,在... -
量化交易 第十五课 单因子有效性分析之 Alphalens
2021-03-21 02:20:17第十五课 单因子有效性分析之 Alphalens. -
apache POI3.2 java操作excel 设置数据有效性,实现excel单元格列表和提示
2012-02-03 15:29:45excel菜单栏上--数据--有效性--允许--序列, excel菜单栏上--数据--有效性--输入信息. 程序现实: 首先,http://poi.apache.org/官方下载POI3.2 jar包. import org.apache.poi.hssf.usermodel.HSSFWorkbook; ...