1 EXCEL基本操作
1 基本概念：工作簿，工作表，行，列，单元格
2 数据录入
• 批量录入相同数据（ctrl+enter）
• 身份证号码输入（设置单元格属性为“文本”或者前面加 ’ ）
• 分数输入（直接输1/3EXCEL默认为1月3日，应该写成0空格 1/3）
• 单元格里换行（alt+enter）
3 查找 ：模糊查找、值查找
4 冻结窗口
5 筛选：一般筛选，空行筛选
6 行列转置
7 插入对象：对象格式的设置
8 数据剔重和分列
9 下拉菜单制作
10 格式刷使用
2 EXCEL通用运算符
分类 |
运算符号 |
运算符含义 |
示例 |
算术运算 |
+(加号) |
加 |
2+3=5 |
-(减号) |
减 |
3-1=2 |
|
*(星号 |
乘 |
3*2=6 |
|
/(斜杠) |
除 |
6/2=3 |
|
%(百分号) |
百分号 |
50% |
|
^(脱字号) |
乘方 |
4^3=43=64 |
|
文本 |
& |
可以将文本连接起来 |
"zhang"&"YL" |
比较 |
=(等号) |
相等 |
B1=C1,若B1中单元格内的值确实与C1中的值相等,则产生逻辑真值TRUE,若不相等,则产生逻辑假值FALSE |
<(小于号) |
小于 |
B1<C1 |
|
>(大于号) |
大于 |
B1>C1,若B1中数值为6,C1中数值为4,则条件成立产生逻辑真值TRUE,否则产生逻辑假值FALSE |
|
>=(大于等于号) |
大于等于 |
B1>=C1 |
|
<>(不等号) |
不等于 |
B1<>C1 |
|
<=(小于等于号) |
小于等于 |
B1<=C1 |
|
引用运算符 |
: |
区域运算符,产生对包括在丙相引用之间的所有单元格的引用 |
(A5:A15) |
, |
联合运算符,将多个引用合并为一个引用 |
SUM(A5:A15,C5:C15) |
|
(空格) |
交叉运算符,产生对两个引用构有的单元格的引用 |
(B7:D7 C6:C8) |
3 EXCEL函数
• 单元格引用方式
相对应用 A1、B1
绝对引用 $A$5、$C$3
混合引用 $A5、C$3
4 函数的常见错误
错误值类型 |
含义 |
##### |
当使用了负的日期或负的时间时，出现错误 |
#VALUE! |
当使用错误的参数或运算对象类型时，出现错误 |
#DIV/O! |
当公式被零（0）除时，出现错误 |
#NAME? |
在公式中使用了Excel不能识别的文本时，出现错误 |
#N/A |
当在函数或公式中没有可用数值时，出现错误 |
#REF! |
当单元格引用无效时，出现错误 |
#NUM! |
当公式或函数中某个数字有问题时，出现错误 |
#NULL! |
两个并不相交的区域指定交叉点时，出现错误 |
5 文本类函数和公式
• &，concatenate：将文本连接起来
• FIND(find_text, within_text, start_num) Search()
查找其他文本字符串 (within_text)内的文本字符串 (find_text)，并从within_text 的首字符开始返回find_text 的起始位置编号
• MID(text, start_num, num_chars)
返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。
• TRIM(text)
除了单词之间的单个空格外，清除文本中所有的空格
• LEN(text)
返回文本字符串中的字符个数。
• TEXT(value, format_text)
将数值转换为以指定数字格式表示的文本。
• LEFT(text, num_chars)
根据所指定的字符数，返回文本字符串中第一个字符或前几个字符。
• RIGHT(text, num_chars)
根据指定的字符数，返回文本字符串的最后一些字符。
• Upper,Lower，Proper
6 统计类函数和公式-count
• COUNT(value1,value2,...)
计算包含数字的单元格以及参数列表中数字的个数。使用 COUNT 可以获取区域或数字数组中数字字段的输入项的个数。
• COUNTA(value1,value2,...)
计算参数列表中非空值的单元格数量。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格数量。
• COUNTBLANK(range)
计算指定单元格区域中空白单元格的个数。
• COUNTIF(range,criteria)
计算区域中满足给定条件的单元格的个数。
• COUNTIFS(range1,criteria1, range2,criteria2,...)
– 计算区域中满足多个条件的单元格个数。
7 统计类函数和公式-avg
• AVERAGE(number1,number2,...)
返回参数的平均值（算术平均值）。
• AVERAGEA(value1,value2,...) --注意不要使用
计算参数列表中值的平均值（算术平均值）。 除了数字，计算中还包括文本和逻辑值，如 TRUE 和FALSE。
• AVERAGEIF(range, criteria, average_range)
返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。
• AVERAGEIFS(average_range, criteria_range1,criteria1, criteria_range2,criteria2…)
返回满足多重条件的所有单元格的平均值（算术平均值）。
8 统计类函数和公式-sum
• SUM(number1,number2,...)
计算单元格区域中所有数值的和。
• SUMIF(range, criteria, sum_range)
根据指定条件对若干单元格求和。
• SUMIFS(sum_range, criteria_range1,criteria1,criteria_range2,criteria2…)
对区域中满足多个条件的单元格求和。
• SUMPRODUCT(array1,array2,array3,...) -注意：不要使用
将给定数组中的对应元素相乘，并返回这些乘积之和。
例子：=SUMPRODUCT(A1:B3,C1:D3) 将两个数组中的所有元素相乘，然后将乘积相加 — 即 3*2 + 4*7 + 8*6 +6*7 + 1*5 + 9*3。(156)
9 搜索类函数和公式
• VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
搜索表格第一列中的值，然后返回表格指定列中相同行上的值。当比较值位于所需查找的数据的左边一列时
• HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
返回符合特定值特定顺序的项在数组中的相对位置。如果需要获取某个项在区域中的位置而不是项本身时，请使用MATCH 函数，而不是某个LOOKUP 函数。
• MATCH(lookup_value,lookup_array,match_type)
返回符合特定值特定顺序的项在数组中的相对位置。如果需要获取某个项在区域中的位置而不是项本身时，请使用MATCH 函数，而不是某个LOOKUP 函数。
• INDEX(reference,row_num,column_num,area_num)
返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。
10 判断类函数和公式
• IF(logical_test, value_if_true, value_if_false)
如果您指定的条件计算为 TRUE 返回一个值，并且如果计算为 FALSE 返回为另外一个值。
• IFERROR()
• ISERROR(value)
引用任何错误值（ #N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!），则返回逻辑值 TRUE；否则，返回 FALSE。
• ISERR(value)
引用错误值（#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）
• ISNA(value)
引用错误值（ #N/A），则返回逻辑值 TRUE；否则返回 FALSE。
• ISBLANK(value) ，如果是空单元格，则返回逻辑值TRUE；否则返回FALSE。
• ISTEXT(value) ，如果是字符，则返回逻辑值TRUE；否则返回FALSE。
• ISNUMBER(value) ，如果是数值，则返回逻辑值TRUE；否则返回FALSE。
11 通配符
以下通配符可作为筛选以及查找和替换内容时的比较条件
请使用 |
若要查找 |
?（问号） |
任何单个字符 |
例如，sm?th 查找“smith”和“smyth” |
|
*（星号） |
任何字符数 |
例如，*east 查找“Northeast”和“Southeast” |
12 排序类函数和公式
• RANK(number,ref,order)
返回一个数字在数字列表中的排位。其大小与数字列表中的他值相关。
• RANK.EQ(number,ref,order)
返回一个数字在数字列表中的排位。其大小与列表中的其他值相关。如果多个值具有相同的排位，则返回该组数值的最高排位。（如果要对列表进行排序，则数字排位可作为其位置。）
• RANK.AVG(number,ref,order)
返回一个数字在数字列表中的排位。其大小相对于列表中的其他值；如果有一个以上的值排位相同，则返回平均排位。
• SMALL(array,k)
返回数据集中第k 个最小值。使用此函数可以返回数据集中特定位置上的数值。
• LARGE(array,k)
返回数据集中第k 个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用LARGE 返回第一名、第二名或第三名的得分。
13 数值处理类函数和公式
• MAX(number1,number2,...)
返回一组值中的最大值。
• MIN(number1,number2,...)
返回一组值中的最小值。
• MAXA(value1,value2,...)
返回参数列表中的最大值。除了数字，计算中还包括文本和逻辑值，如TRUE 和FALSE。
• MINA(value1,value2,...)
返回参数列表中的最小值。 除了数字，计算中还包括文本和逻辑值，如 TRUE 和FALSE。
• ROUND(number,num_digits)
可将某个数字四舍五入为指定的位数。
• ROUNDDOWN(number,num_digits)
靠近零值，向下舍入数字。
• ROUNDUP(number,num_digits)
远离 0（零）值，向上舍入数字。
• INT(number)
将数字向下舍入到最接近的整数。
• TRUNC(number,num_digits)
将数字的小数部分截去，返回整数。