Excel-数据导入、自定义格式、收集与清洗

快速输入数据的方法

1、自定义列表：文件-选项-高级-编辑自定义列表；

2、填充柄：ctrl +；

3、填充柄+快捷菜单;

4、Ctrl+回车：在多个不连续的单元格中输入内容;

5、文本记忆式输入法：右键-从下拉列表中选择;

6、数据验证方式：数据-数据验证-序列-直接将文本输入然后用，隔开，如果新增加了行，那就直接将设置的数据验证复制即可;

Excel自定义格式

7、方式一：代码结构组成代码码分为四个部分，中间用；号分隔，具体如下：

正数格式；负数格式；零格式；文本格式

两个代码部分，则第一部分用于正数和零，第二部分用于负数

一个代码部分，则该部分将用于所有数字

要跳过某一代码部分，然后在其后面包含一个代码部分，则必须为要跳过的部分包含结束分号。

例如：正数-默认色；负数-红色+括号；零-蓝色：设置数字自定义-0.0%;[红色](0.0%);[蓝色]0.0。

方式二：特定条件

大于条件值；小于条件值；等于条件值；文本

例如: [红色][<=100];[蓝色][>100]

方式三：特定条件

条件值1；条件值2；同时不满足条件值1，2；文本

例如：[=1]"男";[=0]"女"

各个参数的涵义

G/通用格式用excel做crm：以常规的数字显示，相当于分类列表中的常规选项。

例：代码：G/通用格式。10显示为10；10.1显示为10.1。

0：显示数字，如果数字位数少于格式中的零的个数，则显示无意义的零。

例：代码：00000。1234567显示为1234567；123显示为00123

代码：00.000。100.14显示为100.140；1.1显示为01.100

：只显示有效数字而不显示无意义的零。

例：代码：.,12.1显示为12.10;12.1263显示为：12.13

？：为无意义的零在小数点两边添加空格，以便使小数点对齐。

例：分别设置单元格格式为??.??和???.???,对齐结果如下：

例：设置单元格自定义格式 ??/???。

1.25显示为1 1/4 ，

：显示千位分隔符或者将数字以千倍显示。

例：代码：，。10000显示为10，000

代码：，。10000显示为10。

代码：,,。1000000显示为1。

.：小数点。如果外加双引号则为字符。

例：代码0.。11.23显示为11.2

%：百分比。

例：代码%。0.1显示为10%

/：用excel做crm显示下一个字符。和用途相同都是显示输入的文本，且输入后会自动转变为双引号表达。不同的/是显后面的文本，双引号是显示双引中间的文本。

例：代码/ABC或ABC均显示为ABC 。

*：重复下一次字符，直到充满列宽。

_（下划线）：留下一个和下一个字符同等宽度的空格

例：代码：$,0.00_);($,0.00)

文本：显示双引号中的文本。

例：代码：中国@显示为中国河南

[颜色]：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、蓝色、青色和洋红。

例：代码：[青色];[红色];[黄色];[蓝色]。显示结果为正数为青色，负数显示红色，零显示黄色，文本则显示为蓝色

[颜色N]：是调用调色板中颜色，N是0~56之间的整数。

例：代码：[颜色3]。单元格显示的颜色为调色板上第3种颜色。

[条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另一个是所有的其他。条件要放到方括号中。必须进行简单的比较。

例：代码：[>0]"正数";[=0]"零";"负数"。显示结果是单元格数值大于零显示正数，等于0显示零，小于零显示负数。

！用excel做crm：显示"。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来"。要想显示出来，须在前加入！；例：代码：!"。10显示10"；代码：!"!"。10显示10"" 。

！：显示： 例如122040-（=--TEXT(原始值,"00!:00!:00")）-12：20：40

时间和日期代码：

m ：将月显示为不带前导零的数字。

mm ：根据需要将月显示为带前导零的数字。

mmm：将月显示为缩写形式（Jan 到 Dec）。

mmmm：将月显示为完整名称（January 到 December）。

mmmmm ：将月显示为单个字母（J 到 D）。

d：将日显示为不带前导零的数字。

dd ：根据需要将日显示为带前导零的数字。

ddd：将日显示为缩写形式（Sun 到 Sat）。

dddd：将日显示为完整名称（Sunday 到 Saturday）。

yy：将年显示为两位数字。

yyyy：将年显示为四位数字。

例：代码：YYYY-MM-DD。2005年1月10日显示为：2005-01-10

代码：YY-M-D。2005年10月10日显示为：05-1-10

AAAA：日期显示为星期。

H或HH：以一位（0~23）或两位（01~23）显示小时

M或MM：以一位（0~59）或两位（01~59）显示分钟

S或SS：以一位（0~59）或两位（01~59）显示秒用excel做crm

例：代码：HH:MM:SS。23:1:15显示为23:01:15

[H]或[M]或[SS]：显示大于24小时的小时或显示大于60的分或秒。

下面介绍几个常遇到的实例

把12345显示为12

代码：0.,

设置千元单位显示且四舍五入保留两位小数要求：把12345显示为：12.35

代码：.00,

在数字中任意插入字符要求：把20050512设置为：2005-05-12

代码：0000-00-00

在文本或数字前添加字符要求：郑州市显示为河南省郑州市

代码："河南省"@

要求：1035010121显示为手机号码1034010121

代码："手机号码"@或"手机号码"

另外日期格式的显示方法

"bbbb":显示四位佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。

"mmm":显示英文月份的简称。

"mmmm":显示英文月份的全称。

"ddd":显示英文星期几的简称。

"dddd":显示英文星期几的全称。

关于特殊数字的显示:

[DBNum1]:中文小写数字

例：代码：[DBNum1]。1232显示为一千二百三十二

[DBNum2]: 中文小写数字

代码： 13显示为壹拾叁

[DBNum3]: 中文小写数字用excel做crm

例：123显示为1百2十3

特殊说明

因为参数的特殊性，所以自定义的参数也是有关键字的。如函数=TEXT(A1,"b0000")就会显示错误。因为b就是保留的关键字，在自定义格式输入b系统就会自动填入bb。bb就是佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。bbbb就是四位佛历年份。要解决=TEXT(A1,"b0000")的错误问题，需要这样定义函数=TEXT(A1,"""b""0000")。在自定义格式中定义就是"b"0000。其它的关键字自己体会如：d、e............

8、生成随机数：rand()生成0~到1左闭右开的随机数，randbetween(10,99)生成10~99的左闭右开的随机数；

9、填充√或x：首先变成Wingdings2的字体格式-数据验证-序列-R,S-确定;

10、抓取网页数据的函数：=FILTERXML(WEBSERVICE(在线翻译_有道")

11、同时填充多个工作表：打开一个新的工作表-文件-选项-可以设置一个工作簿中包含几个工作表；同时选中几个工作表-摁住shift-选中工作表-在任意一个工作表中填充内容，可以看到选中的几个表中都包含了相同的内容；用excel做crm

数据收集

12、导入txt文本数据：数据-自文本-当每列字段长度都相同时，就可以用固定宽度，否则用分隔符号；导入access数据：数据-自access;

13、快速填充：输入内容-右键向下拉-快速填充；汉字中提取数字：先输入一地个单元格中的数字-右键向下拉-快速填充；在A列生成1至10000的连续序号：在第一个单元格中输入初始值-选中所需要的单元格数（在名称框中输入A1:A10000,回车）-开始-填充-序列-等差序列-步长1-终止值10000；

格式转换与规范

14、文本数字转换数值数字：文本格式转化成数字格式的方法有6种分别是乘1、除1、加0、减0、=--单元格、=value(单元格)；身份证中年月日的提取：例如：131127198712193238 =mid( ,7,8)得到19871219、=text( ,"0000-00-00")得到1987-12-19、设置格式为日期格式、=--text( ,"0000-00-00")得到一九九二年五月一日；

16、规范日期：不规范的数据是用点进行分割；例如2018.12.09-substitute函数得到2018-12-09或者2018/12/09，此时的结果显示的是文本格式时间，然后用六种转换方式中的一种转换成数字格式时间；另一种方法就是查找替换； 例如20181209-text函数()转换成2018-12-09的文本式-（--text()转换成数字型的格式）； 如果格式或者长度一致可以用数据选项卡下的分列来转换；

17、规范时间：！：显示： 例如122040-（=--TEXT(原始值,"00!:00!:00")）-12：20：40；合并日期和时间：自定义-(yyyy-m-d h-mm-ss) 拆分日期：用int函数将合并的日期和时间取整，或者直接对合并的结果转换成拆分时间：合并的减去拆分出的日期；用excel做crm

数据清洗

18、分列源数据：数据选项卡-分列-如果长度一致用固定宽度，如果不一致就用分隔符号-分隔符号一般都是利用一个字符将其分开，如果出现两个字符的情况，先将两个字符的替换为一个字符的情况，再分列；

20、合并多个工作表的内容：比如有上半年的数据和下半年的数据，并且顺序一致-只选择数字部分-数据选项卡-合并计算-选择函数-选择工作表； 如果行的数据不一致，需要选择标签位置为最左列； 如果列的数据不一致，需要选择标签位置为首行； 如果都不一致就需要都选中； 以上数据在选择表中的数据时需要注意选上列或者行或者行列都选上；

批量导入照片用excel做crm

21、批量复制照片：（1）在空单元格输入公式：=""，并填充到所有单元格；（2）然后选中此列需填充照片的列，复制，粘贴到记事本中，然后再复制所有数据到原来的位置；（3）选中首列，调整行高，放到最后一个单元格下方变成十字拖动到与照片对齐(与照片的分辨率数据不是一个单位）；

最后，教大家一个简单拖拽创建表单和生成报表的方法。使用简道云表单在线创建“云表格”，通过简单拖拽，即可制作出一个个柱形图、折线图、饼图等图表，1分钟实现数据可视化分析！非常方便~