excel技巧大全集（EXCEL经验分享十）

EXCEL函数对于单元格的引用我们在之前说过，一般来说都是直接用鼠标来进行选择的，不过EXCEL还提供了一种将单元格范围定义为一个名称，此后所有函数涉及到单元格范围的都可以通过这个名称来表示，这就减少了工作量不过名称如果直接指定单元格范围，那么还是死的，一旦你需要变化单元格范围了，那么就要再次进行修改，所以配合着函数来指定单元格范围让他成为动态的，就很简便了另外，所有EXCEL功能凡是涉及到单元格范围的，都可以通过函数来解决动态的问题，比如之前说过的数据透视表的数据源可以动态的，我来为大家科普一下关于excel技巧大全集?以下内容希望对你有帮助!

excel技巧大全集

EXCEL函数对于单元格的引用我们在之前说过，一般来说都是直接用鼠标来进行选择的，不过EXCEL还提供了一种将单元格范围定义为一个名称，此后所有函数涉及到单元格范围的都可以通过这个名称来表示，这就减少了工作量。不过名称如果直接指定单元格范围，那么还是死的，一旦你需要变化单元格范围了，那么就要再次进行修改，所以配合着函数来指定单元格范围让他成为动态的，就很简便了。另外，所有EXCEL功能凡是涉及到单元格范围的，都可以通过函数来解决动态的问题，比如之前说过的数据透视表的数据源可以动态的。

所以说函数不仅仅就应用于单元格内，他可以应用到几乎所有的场景，甚至在VBA编程中也可以使用。

函数应用在名称范围以及有效性规则中

新建名称，在EXCEL菜单的公式的定义名称中。点击之后出现对话框，先取个名字，当然最好是英文来表示，然后就是选定范围了。比如这里有A1到C3一共9个单元格，我定义为sumArea，然后我使用SUM函数求和的话，我就可以使用定义的名称了，公式即为SUM(sumArea)，公式自然就会对A1到C3单元格进行求和了。

接下来，就是对于名称中所涉及的单元格如何让他成为动态的了。此时就可以利用INDIRECT把字符串转化成单元格范围了，你只要把公式填写到定义名称的引用位置即可。至于说怎么写这个公式，我在第九篇内已经说过了，大家可以看一看。

EXCEL本身就是处理数据的，不过处理数据之前那肯定是要录入数据的，而录入数据是手工录入的就不可避免的要产生错误，所以EXCEL就有一个有效性原则的功能。所谓有效性原则，就比如你录入手机号码，那一定要是11位的（就当前情况而定），身份证号码那必须十18位的等等。

当你把单元格设置了有效性原则后，一旦出现录入数据错误，EXCEL就会有错误提示，而这个错误提示也是可以自定义的。

有效性原则在EXCEL菜单的数据中的数据验证中的数据验证。点击之后出现的了对话框，在设置选项卡这里允许下拉框内，EXCEL已经设置了很多常用的规则，比如整数、小数等等数字的录入范围。而文本长度就是我刚才提到的手机号码或者身份证号码必须是11位和18位，你只要把规则定为等于11或者18就可以了。而在下拉框中最后一个自定义，就是通过函数来定义规则了，因为还有很多规则是根据自己实际情况来定义的。下面我就演示一下一个自定义规则，就是录入的数据前两个字必须是中国。同时，我把自定义错误信息也演示一下。

因为前两个字必须是中国，所以很自然的就想到函数LEFT，从左边取两位，然后必须等于中国即可。我设置的单元格是F1单元格，同时我要把这个规则都应用到整个F列，所以先选到F1单元格，然后选中F列，然后在开始定义规则，这样我们设置F1单元格规则的时候，他会把这个规则同时应用到整列中去。

怎么样，数据验证还是很有用处的吧，如果一个表格内的数据是多人完成的，那么数据验证还是很有必要的。在这里我要提一下数据验证其实有一个漏洞，当我把其他数据粘贴到有数据验证的单元格的时候，EXCEL就不会报错了。我的理解就是粘贴并不是编辑单元格，所以EXCEL没有感知到，只有在编辑栏中输入内容，并且按下了回车之后，EXCEL就会感知到了。

非常有意思的随机函数

EXCEL中有两个随机函数，一个是RAND函数，他没有参数，作用是随机生成0-1之间的小数，另一个则是RANDBETWEEN，从字面意思理解就是在指定的两个整数之间随机生成一个整数，所以他有两个参数，一个下限，一个上限。早期的EXCEL版本只有RAND函数，而在EXCEL2007以后就加入了RANDBETWEEN，虽然RAND函数也可以通过变化的方式达到RANDBETWEEN的作用，不过EXCEL也是在进步，逐步的增加了很多函数。

其实，刚开始我接触到随机函数的时候也觉得他会有什么用呢，只是EXCEL丰富自己的函数库而已。不过，之后有一个老朋友来求助于我，原来他老丈人要买车，当时的车牌是可以自己的定义的，只要没出现重复的就可以。我这个朋友的老丈人要他想几个车牌号出来，而我朋友就问我能不能随机生成100个，他打印出来让老丈人自己去挑，省的自己去想了。然而还没完，他接着提出了要求，首先车牌号不能出现数字4，其次，车牌的第二位或者第三位必须是英文字母，而英文字母也并不是A-Z，有些字母也不能出现。我听了他的要求之后，第一反应就是想到了随机函数，然后我就开始考虑起他提出的要求了。

随机从0-9里面产生一个数字，直接就用RANDBETWEEN函数就可以了，可如何保证没有4这个数字呢。我当时先想到，用一个IF函数产生一个分支，不是随机从0-3产生一个数，就是从5-9之间产生一个数，而IF函数产生分支也是用随机函数随机从0-1之间产生数字那就是说也只有两种可能，其实这也是一个办法，但是我总觉得概率不够平均，虽然我数学也不是很好，但这种方法我觉得50%的可能产生数字0-3，50%的可能产生数字5-9，这样概率分布似乎不太平均。所以，我又想了想，后来我想到了INDEX函数。

此前我介绍过INDEX函数，这个函数的第一个参数就是列或者行，第二个参数则是列号或者行号，然后就返回了指定单元格的值。比如INDEX(A:A,2)，他的意思就是返回A列第二个单元格的值。有了INDEX函数，我就想到事先先把0-9但不包含4的9个数字分别写在A列中比如A2到A10，然后随机2-10任意一个数字，那么INDEX函数就自然返回A2到A9的值了，然而重要的是肯定不会有4。

解决了这个问题，我着实地兴奋了一把，因为这个问题解决了关于英文字母那一块也就搞定了。

我们先假定是上海市的车牌，所以车牌的第一个字一定是“沪”，然后就要跟着英文字母，我假设只在A-E中产生。然后的车牌号一共有5位，第一位自然是数字，第二位或者第三位一定要有一个字母，同时第二位或者第三位不是字母的就一定是数字，之后的第四位和第五位也都是数字。我再次假设第二位或者第三位的字母是从F-M中随机取一个，那么是在第二位还是在第三位中产生一个字母就可以用IF函数来做一个分支了。将IF函数的条件设置成随机函数2-3，只随机产生两个数字，如果是2，则第二位就是字母，如果是3，则第三位就是字母。

每一位产生的字符无论数字还是字母统统用&符号进行拼接，最后形成了车牌号。好了，我把不含4的数字写在A列，把车牌前缀的字母就是比如沪A，沪C所涉及的字母写在了B列，然后把第二位或者第三位涉及的字母写在C列，最后要生成的车牌号我放在了E1单元格。

经过一番折腾，公式最终就是：

="沪"&INDEX($B:$B,RANDBETWEEN(2,6))&" - "&INDEX($A:$A,RANDBETWEEN(2,10))&IF(RANDBETWEEN(2,3)=2,INDEX($B:$B,RANDBETWEEN(2,6))&INDEX($A:$A,RANDBETWEEN(2,10))&INDEX($A:$A,RANDBETWEEN(2,10))&INDEX($A:$A,RANDBETWEEN(2,10)),INDEX($A:$A,RANDBETWEEN(2,10))&INDEX($B:$B,RANDBETWEEN(2,6))&INDEX($A:$A,RANDBETWEEN(2,10))&INDEX($A:$A,RANDBETWEEN(2,10)))

公式够长的了吧，我自己看着都有点头晕，不过公式虽然长，原理其实不复杂，主要是因为涉及到好几个字符串的拼接，同时中间还要产生一个分支，所以公式就很长。不过这个公式里面单元格的引用一定要绝对引用，换句话说如果你要把这个公式不同的拉动，那些基础数据的单元格是不能变化的，比如不含4的单元格，无论你公式怎么拉动，他所在的位置永远都是A2-A10，绝对引用我在之前也说过可以用F4快捷键快速的添加$绝对引用符号。

这个公式任意拉，结果就是我告诉我朋友，你甭说是100个了，10万一次生成也可以。另外我发现，点到空白的单元格，然后不停的按键盘的DEL键，每按一次，所有的随机数据都更新一次，看着这些数据在跳动，我觉得既开心又有成就感。

好了，公式虽然长，但我的目的是让大家知道第一，EXCEL的函数往往是多个函数一起配合的，而这其中自己分析问题的思维是最为重要的，哪怕你不知道函数的用法，思维永远是第一位的；第二，EXCEL不仅仅只在办公领域起作用，他可以应用到现实生活中去。