Excel数据有效性应用提取生日的办法

1、Excel中的数据有效性在Excel中，我们可以约束某个栏位只能输入某些值，这些值可以是固定的序列，也可以是某些单元格。下面我们来看看这两种方式如何设置(以下截图是在Excle2007中，Excel2003类似)：1、固定的序列【步骤1】通过菜单【数据】->【数据有效性】->【数据有效性.】进入【数据有效性】面板：【步骤2】【允许】选择【序列】，然后在来源中输入固定值“New,Update,Delete”，以英文逗号隔开：【步骤3】效果如下：2、来源为某些单元格：【步骤1】同方式1【步骤2】将鼠标点中来源，然后圈选A1A3：【步骤3】效果同方式。注意：第一种方式不灵活，但是可以拷贝

2、到其他的Excel中直接使用；第二种方式由于引用了Excel中的单元格，不能拷贝到其他Excel中，也不难拷贝到同一个Excel文档的其他Sheet中，只能在同一个 Sheet中使用.在excel2003中定义有效性标准要定义允许输人到单元格或者范围中数据的类型，步骤如下：选择单元格或者范围，选择菜单栏的“数据”“有效性”，Excel显示“数据有效性”对话框。单击“数据有效性”对话框中的“设置”选项卡，从“允许”下拉框中选择个选项，要定义公式，选择“自定义”。从“数据”下拉框中选择定义条件。所做的选择决定可以访问的其他控制，如下图所示：单击“输入消息”选项卡，并且定义当用户选择了该单元格，要显

3、示哪个信息。可以使用这个选项逐步告诉用户需要什么样的数据类型，如图所示单击“出错警告”选项卡，并且定义当用户进行了无效的输入，要显示哪个错误信息。风格的选择确定当输入了无效项时用户选择什么。要防止无效输入，选择停止。这一步是可选的。单击“确定”按钮关闭“数据有效性”对话框。执行了这些步骤之后，单元格或者范围就包吉了所定义的有效性标准。EXCEL关于数据有效性的应用使单元格区域内记录不能重复输入控制=COUNTIF(A:A,A2)=1 (直接复制此公式进去即可)禁止单元格输入数字控制=ISNUMBER(A1)<>TRUE允许单元格只能输入数字控制=ISNUMBER(A1)=TRUE禁

4、止单元格输入字母和数字=LENB(A1)=2禁止输入周末日期=AND(WEEKDAY(A1)<>1,WEEKDAY(A1)<>7)特定前缀输入:应该含某个字开头=OR(LEFT(A1)="张",LEFT(A1)="李")禁止单元格前后输入多余空格=A1=TRIM(A1)禁止输入数字大于某某值=A1<=100禁止输入限定的值>=MAX(A:A) 同 <>"" 同=""限定区域输入的和的最大值=SUM(A1:A10)<100有效性应用实例一：我的ID你别用教师经常要

5、用Excel制作表格，录入学生信息，Excel强大的制表功能，给教师工作带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。其实，只要合理设置“数据有效性”规则，就可以避免错误。为了便于管理学生信息，每个学生都有属于自己独有的ID（学号），在信息录入时，学生ID不允许重复，如果在Excel录入重复的ID，就会给信息管理带来不便，我们可以对Excel“数据有效性”进行设置，拒绝录入重复数据。有效性应用实例二：快速揪出无效数据有些数据是有范围限制的，比如以百分制记分的考试成绩必须是0100之间的某个数据，录入此范围之外的数据就

6、是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel 的“数据有效性”，快速揪出表格中的无效数据。现在学生的身份证号已经全部都是18位的新一代身份证了，里面的数字都是有规律的。前6位数字是户籍所在地的代码，714位就是出生日期。第17位“2”代表的是性别，偶数为女性，奇数为男性。我们要做的就是把其中的部分数字想法“提取出来”。STEp1，转换身份证号码格式我们先将学生的身份证号完整地输入到Excel2003表格中，这时默认为“数字”格式（单元格内显示的是科学记数法的格式），需要更改一下数字格式。选中该列中的所有身份证号后，右击鼠标，选择“设置单元格格

7、式”。在弹出对话框中“数字”标签内的“分类”设为“文本”，然后点击确定。STEP2，“提取出”出生日期将光标指针放到“出生日期”列的单元格内，这里以C2单元格为例。然后输入“=MID(B2,7,4)&"年"&MID(B2,11,2)&"月"&MID(B2,13,2)&"日"”（注意：外侧的双引号不用输入，函数式中的引号和逗号等符号应在英文状态下输入）。回车后，你会发现在C2单元格内已经出现了该学生的出生日期。然后，选中该单元格后拖动填充柄，其它单元格内就会出现相应的出生日期。如图1 。图1 通过

8、上述方法，系统自动获取了出生年月日信息小提示：MID函数是EXCEL提供的一个“从字符串中提取部分字符”的函数命令，具体使用格式在EXCEL中输入MID后会出现提示。STEP3，判断性别“男女”选中“性别”列的单元格，如D2。输入“=IF(MID(B2,17,1)/2=TRUNC(MID(B2,17,1)/2),"女","男")”（注意如上）后回车，该生“是男还是女”已经乖乖地判断出来了。拖动填充柄让其他学生的性别也自动输入。如图2。图2 性别被自动填入指定位置这样，通过两个简单的函数，我们就可以让EXCEL从身份证号中自动提取出生日期和性别并填充到单元

9、格内，极大地减轻了我们的输入工作量。最后效果如图3。图3 用EXECL可以自动识别并填入学生信息如何不启用宏就不能使用Excel,不启用宏就不能用，应该怎么达到这种效果？用VBA做了个表个给其他部门用，但头一次他们使用时都要将安全级别设置成低时才能正常使用，有的干脆不启用宏，填完数据就交上来，那些辛苦做的VBA功能根本没用上。有什么办法，让他们不启用宏就不能使用EXCEL  简单的办法就是： Private Sub Workbook_BeforeClose(Cancel As Boolean)Sheet1.Visible = xlSheetHiddenEnd SubPrivate S

10、ub Workbook_Open()Sheet1.Visible = xlSheetVisibleEnd Sub意思就是sheet1表默认是隐藏的，只要启用宏，那么sheet1表才会自动取消隐藏。复杂一点的办法就是用老的宏表函数，也可以达到禁用宏就退出，具体的搜索一下本坛，很多的，就搜索 禁用宏就退出。不过这个方法在未来的版本可能会不兼容了吧。还有个办法，就是干脆在工作表设置一个按钮，只有启用宏按钮才有效果，点击按钮，需要的表格才能出来。-如何在Excel 2010 中快速录入小数使用以上两种方法虽然可以实现同样的功能，但仍存在一定的区别：使用方法一更改的设置将对数据表中的所有单元格有效，方法

11、二则只对选中单元格有效，使用方法二可以针对不同单元格的数据类型设置不同的数据格式。      在工作中笔者要经常录入大批保留三位小数的数据表，数据范围为0.001100.000，由于大部分数据集中0.0010.010之间，这样输入一个数据就需要击键5次，录入速度比较慢。能不能提高输入速度呢？经过研究，笔者发现通过对数据格式进行重新定义可大大提高录入效率，如输入“0.001”只需输入“1”即可，下面是具体的实现方法和步骤：方法一：自动设置小数点1、选定需要输入数据的单元格;2、在“工具”菜单上，单击“选项”，再单击“编辑”选项卡;3、选中“自动设置小数点”复选框;4、在“位数”框中，输入小数位数，本例中输入“3”;5、单击“确定”按钮，开始输入数据。编辑提示：“位数”框中可输入正数，也可以输入负数。例如，如果在“位数”框中输入“3”，然后在单元格中键入“1”，则其值为“0.001”。如果在“位数”框中输入“-3”，然后在单元格中键入“1”，则其值为 “1000”;在选择“自动设置小数点”选项之前输入的数字不受小数位数的影响。方法二：自定义数据格式1、选定需要输入数据的单元格;2、在“格式”菜单上，单击“单元格”，选中“数字”选项卡; 3、在“分类”下拉框中选中“自定义