Excel笔记

Ryan_wander
352 阅读7分钟

第一章 execl常用功能

一、导入外部数据

  1. 方法：数据—获取外部数据
  2. 导入文本, access, word, excel, 网页数据
  3. 在导入时创建参数查询
  4. 根据网页内容更新工工作表数据

二、数据输入

  1. 不同类型的数据的输入规则
  2. 快速录入重复项：①记忆式键入 ②从下拉列表中选择
  3. 自动填充
  4. 自定义序列

三、数据验证

  1. 输入条件的限制和现有数据的校验
  2. 数据验证
  3. 下拉式菜单

四、数据处理与表格编辑

  1. 高效操作单元格区域

    ①快速定位首末单元格：ctrl+Home/End

    ②水平/垂直方向定位：ctrl+↑↓←→

  2. 定位选择特殊区域

  3. 改变行列次序：shift+拖动

  4. 单元格文本分行, 分列

  5. 多行数据合并成一行, 一列

  6. 删除重复行

  7. 选择性粘贴

  8. 照相机

五、设置表格格式

  1. 区域转化为表格
  2. 表格转化为区域
  3. 单元格样式

六、优化excel工作环境

  1. 保护工作表
  2. 工作簿瘦身
  3. 工作表可用范围
  4. 隐藏工作表
  5. 标题始终可见
  6. 多窗口协作工作
  7. 使用超链接制作报表导航目录
  8. 分级显示

七、数据排序

八、数据筛选

  1. 自动筛选
  2. 高级筛选
  3. 自定义视图
  4. 高级筛选中的通配符
条件设置 筛选效果
= 记录为空
<> 记录非空
? 单个任意字符
* 多个任意字符
~ 引用*或?本身时，需要在前面加~

九、使用条件格式标识数据

十、合并计算

第二章 图形与图表

一、数据透视表

二、切片器

第三章 函数和公式

一、文本处理函数

  1. len(text)

    主要功能：len返回文本串的字符数

  2. left(text，[num_chars]), right(text，[num_chars]), mid(text，start_num，num_chars)

    主要功能：一个字符串中截取出指定数量字符

  3. proper(text), lower(text), upper(text)

    主要功能：首字母大写, 全部小写, 全部大写

  4. search(find_text，within_text，[start_num])

    主要功能：在第二个文本字符串中查找第一个文本字符串，并返回第一个文本字符串的起始位置的编号，该编号从第二个文本字符串的第一个字符算起

  5. find(find_text，within_text，[start_num])

    主要功能：在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起

  6. replace(old_text，start_num，num_chars，new_text)

    主要功能：在某一文本字符串中替换特定位置处的任意文本

  7. substitute(text，old_text，new_text，[instance_num])

    主要功能：在某一文本字符串中替换指定的文本

  8. rept(text，number_times)

    主要功能：将文本重复一定次数

  9. concatenate(text1，[text2]，...)

    主要功能：文本字符串合并为一个文本字符串

  10. text(value， format_text)

    主要功能：通过格式代码向数字应用格式，进而更改数字的显示方式

二、查找引用函数

  1. column([reference])

    主要功能：返回指定单元格引用的列号

  2. columns(array)

    主要功能：返回数组或引用的列数

  3. row([reference])

    主要功能：返回一引用的行号

  4. rows(array)

    主要功能：返回引用或数组的行数

  5. counta(value1，[value2]，...)

    主要功能：返回参数列表中非空的单元格个数

  6. dcount(database，field，criteria)

    主要功能：统计满足某个条件的单元格个数

  7. indirect(ref_text，[a1])

    主要功能：此函数立即对引用进行计算，并显示其内容

 8. lookup(lookup_value，lookup_vector，[result_vector])或LOOKUP(lookup_value, array)

  主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值

 9. vlookup(lookup_value，table_array，col_index_num，[range_lookup])

  主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值

 10. hlookup(lookup_value，table_array，row_index_num，[range_lookup])

  主要功能：在数据表的首行查找指定的数值，并由此返回数据表当前列中指定行处的数值

 11. index(array，row_num，[column_num])

  主要功能：返回表或区域中的值或对值的引用

 12. match(lookup_value，lookup_array，[match_type])

  主要功能：返回指定数值在指定数组区域中的位置

  1. offset(reference，rows，cols，[height]，[width])

    主要功能：返回对单元格或单元格区域中指定行数和列数的区域的引用

三、逻辑函数

 1. if(logical_test，value_if_true，[value_if_false])

  主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容

2. iferror(value，value_if_error)

主要功能：如果公式的计算结果为错误，则返回指定的值；否则将返回公式的结果

  1. is类函数(value)

    主要功能：可以检验数值的类型并根据参数取值返回 true 或 false

函数 如果符合以下条件，则返回 true
isblank 值为空白单元格
iserr 值为任意错误值(除去 #n/a)
iserror 值为任意错误值
islogical 值为逻辑值
isna 值为错误值 #n/a
isnontext 值为不是文本的任意项(值为空单元格时返回 true)
isnumber 值为数字
isref 值为引用
istext 值为文本

  1. and(logical1，[logical2]，...)

    主要功能：返回逻辑值：如果所有参数值均为真，则返回true；只要有一个参数的逻辑值为假，即返回 false

  2. or(logical1，[logical2]，...)

    主要功能：返回逻辑值：在其参数组中，任何一个参数逻辑值为 true，即返回 true；所有参数的逻辑值为 false，才返回 false

  3. not(Logical)

    主要功能：如果逻辑值为 false，函数 not 返回 true；如果逻辑值为 true，函数 not 返回 false

四、日期函数

  1. workday(start_date，days，[holidays])

    主要功能：返回在某日期(起始日期)之前或之后, 与该日期相隔指定工作日的某一日期的日期值

  2. networkdays(start_date，end_date，[holidays])

    主要功能：返回两个日期之间完整的工作日数值，使用参数指示有多少天是假期

  3. networkdays.intl(start_date，end_date，[weekend]，[holidays])

    主要功能：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是假期

  4. weekday(serial_number，[return_type])

    主要功能：返回代表一周中第几天的数值

  5. weeknum(serial_number，[return_type])

    主要功能：返回一个数字，该数字代表该日期在一年中的第几周

  6. datedif(start_date，end_date，y/m/d/yd/md/ym)

    主要功能：计算返回两个日期参数的差值

  7. date(year，month，day)

    主要功能：返回表示特定日期的连续序列号

  8. datevalue(date_text)

    主要功能：将由文本表示的日期转换为序列数

  9. year(), month(), day(), hour(), minute(), second(), today(), now()

五、数学函数

  1. int(number)

    主要功能：向下取整为最接近的整数

  2. max, min, rank

  3. large(array,k)

    主要功能：返回数据集中的第k个最大值

  4. small(array,k)

    主要功能：返回数据集中的第k个最小值

  5. rand()

    主要功能：返回了一个大于等于 0 且小于 1 的平均分布的随机实数

  6. randbetween(bottom，top)

    主要功能：返回位于两个指定数之间的一个随机整数

  7. round(number，num_digits), roundup, rounddown

    主要功能：返回按指定位数进行四舍五入(上进, 下舍)数值

  8. mod(number，divisor)

    主要功能：两个数值表达式作除法运算后的余数

  9. sumproduct(array1，[array2]，[array3]，...)

    主要功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和

 10. sumif(range，criteria，[sum_range])

  主要功能：计算符合指定条件的单元格区域内的数值和

  1. sumifs(sum_range，criteria_range1，criteria1，[criteria_range2，criteria2]，...)

    主要功能：计算符合指定条件的多个单元格区域内的数值和

  2. averageif(range，criteria，[average_range])

    主要功能：计算符合指定条件的单元格区域内的数值平均数

  3. averageifs(average_range，criteria_range1，criteria1，[criteria_range2, criteria2]，...)

    主要功能：计算符合指定条件的多个单元格区域内的数值平均数

 14. countif(range，riteria)

  主要功能：统计某个单元格区域中符合指定条件的单元格数目

15. countifs(criteria_range1，criteria1，[criteria_range2，criteria2]，…)

主要功能：统计多个单元格区域中符合指定条件的单元格数目

第四章 宏与vba

附录

一、返回错误值

错误 原因
##### 列宽不够
#VALUE! 在公式中使用了错误类型的数据
#DIV/O! 在公式中使用了0或空值作除数
#NAME? 在公式中遇到了未知字符
#N/A 提供的数据对函数或公式不可用
#NUM! 设置了无效的数值型函数参数
#REF! 公式中使用了无效的引用
#NULL! 公式引用的多个区域没有共同区域
avatar
文章
阅读
粉丝