excel 基础小技巧

 

一、数据输入技巧

1．  输入分数：0空格3/4

2．  输入区号： ‘010

3．  输入多行数据： （1）Alt+Enter （2） 【编辑】|【填充】|【内容重排】(3)插入“文本框”，按住Alt拖动文本框使其与单元格编辑重合。

4．  自动输入“当天”日期： “Ctrl+；”

5．  添加拼音： 【格式】|【拼音指南】|【显示或隐藏】

6．  快速输入小数： 【工具】|【选项】|【编辑】|【自动设置小数位数】，“2“自动在前面添0，如0.01；“-2”中的在后面添0，如1.00

7．  自动填充员工姓名： 【工具】|【选项】|【导入】

8．  添加项目编号： =COUNT(INDIRECT(“R2C:R[-1]C”,FALSE))+1

9．  快速填充相同内容的文字： 【编辑】|【填充】|【向下填充】

10．              连续单元格中自动输入等差数据序列： 【编辑】|【填充】|【序列】

11．              在多个工作表的同一位置输入相同的数据： 【选定全部工作表】，在指定位置输入数据。

12．              记录单： 【数据】|【记录单】

13．              快速填充每月最后一天的日期： 先输入第一个月的日期，鼠标右键双击拖动柄，……

14．              报销单的小写金额分撒填写： 千万位的单元格I5中的公式是：“IF(H5>=10000000,MID(RIGHTB(H5*100,10),1,1),IF(H5>=1000000,”￥”,””))”,剩下的一直到分，请仿照千万位的单元格写。

15．              直接获取网上的数据： 【数据】|【导入外部数据】|【新建Web查询】|【导入】|【属性】|【打开工作薄是自动刷新】

16．              语音协助校对数据： 【工具】|【语音】|【显示“从文本到语音”工具栏】

17．              下拉列表的制作：  【数据】|【有效性】|“序列”，“来源”；【复制】拖动要填写相关信息的单元格，【粘贴】

18．              数据输入的有效性：【数据】|【有效性】|“整数”，“介于”，“1500”，“4000”，【出错警告】填写相关信息。【视图】|【工具栏】|【公式审核】|【圈释无效数据】

19．              保证一列中输入不重复的数据： 【数据】|【有效性】|“自定义”，“=COUNTIF($D$4:$D$18,D6)<2

20．              特定编号的正确输入： 【数据】|【有效性】|“自定义”，“=OR(LEFT(B2,2)=”BY”)

 

二、工作表数据的格式化处理技巧

1．不在工作表中显示0： 【工具】|【选项】|将窗口选项中的“零值”取消

2．不显示负数： 在【单元格格式】|【数字】|【自定义】在类型中输入公式

“G/通用格式；；G/通用格式；”

3．不显示正数： 在【单元格格式】|【数字】|【自定义】在类型中输入公式

“；G/通用格式；G/通用格式；”  即正数；负数；零；文本

4．同一单元格输入文本和数字： 公式“="合计:"&TEXT(SUM(P7:P33))”

5．格式化数据的快捷键： 常规：【Ctrl+Shift+ ~】

货币格式：【Ctrl+Shift+ $】

百分数格式：【Ctrl+Shift+ %】

科学计数法：【Ctrl+Shift+ ^】

日期格式：【Ctrl+Shift+ #】

时间格式：【Ctrl+Shift+ @】

带有两个小数位、千位分隔符，负值前面有一连字符：【Ctrl+Shift+ ！】

6．小数点对齐： 【格式】|【单元格】类型中输入“????.????”

7．时间格式：“a” 、“ddd”、“dddd”表示星期，“y”表示年，“m”表示月，“d”表示日

8．锁定工作表的标题栏： 点击要锁定的行、列的下一行、列，【窗口】|【冻结窗格】

9．单元格数据斜向排版： 【工具】|【自定义】“命令”“格式”

10．特殊单元格中的文本分列： 【数据】|【分列】|……

11．选中偶数行： 在第一个偶数行P2中输入“=1/0”，选中P2:P3，将光标移至所选中区域的右下角，点击鼠标左键拖至…【编辑】|【定位】|【定位条件】公式中只选中“错误”【确定】

12．使活动行变宽变色： 【工具】|【宏】|【Visual Basic编辑器】在“工程资源管理器”中选择表输入代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

Application.ScreenUpdating = False

If Target.Row > 1 And [Height].Row <> Target.Row Then

[Height].RowHeight = 15

[Height].Interior.ColorIndex = xlNone

[Height].Font.ColorIndex = 1

[Height].Font.Bold = False

 

Target.EntireRow.Name = "height"

[Height].RowHeight = 30

[Height].Interior.ColorIndex = 3

[Height].Font.ColorIndex = 2

[Height].Font.Bold = True

End If

Application.ScreenUpdating = True

End Sub

13．单元格在选中时高亮度显示： 【工具】|【宏】|【Visual Basic编辑器】在“工程资源管理器”中选择“workbook”输入代码：

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Row > 0 Then

On Error Resume Next

[ChangeColor_With].FormatConditions.Delete

Target.Name = "ChangeColor_With"

With [ChangColor_With].FormatConditions

.Delete

.Add xlExpression, , "TRUE"

.Item(1).Interior.ColorIndex = 3

End With

End If

 

End Sub

三、数据计算与分析技巧

1．核对两列数据是否相同，并找出不同的数据： 选中任意单元格，如F8，【格式】|【条件格式】“单元格数值”、“不等于”，单击右侧的【选择】选择被比较的单元格D8，去掉$，【格式】……

2．自动实现数据分类： 公式：“=IF(C3<80,”及格”,IF(C3<90,”良”,IF(C3<=100,”优”)))”或“LOOKUP(C3,{0,80,90,100},{“及格”,”良”,”优”,”优”})”

3．打印工资条：visual basic代码：

 

Sub InsertTitle()

Selection.CurrentRegion.Select

Cells(Selection.Row, Selection.Column).Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

ActiveCell.Offset(2, 0).Range("A1").Select

 

Do Until ActiveCell = ""

    Selection.Insert Shift:=xlDown

    Range(Selection, Selection.End(xlToRight)).Select

    Selection.Copy

    ActiveCell.Offset(2, 0).Range("A1").Select

 

Loop

4．回归统计： 【工具】|【加载宏】|选中“分析工具库”，“规划求解”，“条件求和向导”|【确定】|【工具】|【数据分析】|【回归】|输入……“标志”，“线性拟合图”，“正太概率图”

5．提醒尚未发出的货物： 【格式】|【条件格式】|“公式”

“=AND(TODAY()-$G1>=3,$H1=”否”)”“格式”……

6．前三甲用红色标志：选中B3：B16，【格式】|【条件格式】|“公式”|“=B3>LARGE(B:B,4)”|          “格式”【确定】

7．销售数据的季度报表： 【数据】|【数据透视图和数据透视表】|“数据透视表”|【下一步】|“选定区域”|【下一步】|“新建工作表”|【布局】|【确定】|【完成】

8．数据透视图： 【视图】|【工具栏】|【数据透视表】|【图表向导】

9．将excel中的数据发布到internet： 【文件】|【另存为Web页】|“工作表”|“添加交互”|【发布】|在发布内容位“数据透视表”，在添加交互对象中选择“数据透视表功能”|【发布】

 

在Excel中使用查找、替换等功能

查找单元格中的部分内容： ~内容，？内容（内容前面有字）