第一篇章 数据整理与保护

1

CTRL+E,截取填充部分文本

如何截取身份证号中的出生年月,一个快捷操作,截取、填充同时搞定。

操作步骤:在数据源当中的第一行,输入:出生年月日,然后选中整列区域,按键盘CTRL+E,完成快速填充。也可以在第一行右下角单元格处,双击十字句柄,点击右下角的填充选项,选择【快速填充】。

excel技巧

2

一键分列,规范日期格式

日期格式千奇百怪,数据类型却完全不一样,分析起来就频频出现错误。分列一下,马上规范起来。

操作步骤:选中数据源,在【数据】选项卡下找到【分列】,按照分割符号,下一步到设置数据类型为日期,点击完成。
麻瓜程序员excel技巧

3

数据管理的小闹钟:条件格式

对数据进行格式标识,以期更加直观地显示数据,比如员工工资的数额高低、培训成绩的分布、工作进度的控制、异常数据的监测等。

操作步骤:选中数据单元格,点击【开始】选项卡–【条件格式】–【数据条】,让单元格直接嵌上了类似于“柱状图”的效果。

excel技巧

除了数据条,这个功能还有很多种显示方式,见下图

excel技巧

第二篇章 常见函数应用

4

VLOOKUP函数,查找匹配,随心随遇

根据姓名匹配职位、身份证号等信息,总不能一个一个查找复制,耗时耗力,还经常容易出错。因此要用VLOOKUP函数查找引用数据,这也是Excel中使用最频繁的操作。

VLOOKUP函数结构

excel技巧

VLOOKUP精确匹配

VLOOKUP模糊匹配:不再为等级匹配发愁,取代if多层嵌套

excel技巧

5

INDEX+MATCH函数,查找界的王者

从适用性上讲,INDEX+MATCH的组合函数更具威力!

Index+Match中,Match用以确定数据所在的行值和列值(查找姓名所在的行,查找身份证号所在的列,行列交汇的数据就是要匹配出来的数据),Index负责调出由Match确定的行值和列值交叉位置确定的唯一数据,于是查找匹配就自然实现了。

Match:查找到你的位置

Index:提取出相应位置的数据

Index+match+数据有效性,一个小型的查询系统成型了。

第三篇章 数据汇总与分析

6

ALT+=,快速求和

不用再写那么多的SUM了,一个快捷操作统统搞定

7

多表汇总,就用合并计算

对于表结构一致的多表汇总,不用再想着要用到VBA才能解决,合并计算功能就足够了。

8

数据透视表:Excel分析汇总神器

数据透视表,允许用户根据需要对各类数据维度进行划分,进行不同的重组,助你轻松发现隐藏在数据背后的本质。

超强统计:根据你想要呈现的数据报表,透视一下,让你轻松“拖”出来。

多数值计算:求和?计数?平均值?最大值/最小值?标准差?方差?你想要的数值计算方式,应有尽有。

创建组:按季度?分年龄段?数据标签由你定义。

操作方式:选中字段下的数据—右键单击选择【创建组】-创建“起始于”和“终止于”对应的数值,也可采用自带的年、季度、月等组合方式。

切片器:一枚切片器,轻松控制多个数据透视表,数据展现随心而动。

操作方式:选中数据透视表中任一数据——【分析】选项卡—插入切片器—右键单击切片器—报表连接—勾选需要控制的多个表格。

excel技巧

综合运用上面的功能,一张人员基本情况分析的看板就实现了。

excel技巧

第四篇章 数据呈现与可视化

9

图表呈现

相比较枯燥乏味的文字和数据信息,人们更愿意也更容易接受各种图形信息,也就是可视化呈现。通常我们遵循的原则为:能用数据显示的,绝不用文字说明;能用图形显示的,绝不用数据说明。

比如要做这张图:

excel技巧

1、选中数据源,插入一张柱形图,并修改图表类型为组合图。设置:产值:图表类型为-带数据标记的折线图

环比增长:图表类型为-簇状柱形图,勾选次坐标

2.设置柱形图的填充颜色为蓝色,并添加数据标签。

通过调整分类间距的大小,改变柱形图两柱形之间的间距距离。

excel技巧

3.设置折线图的标记点显示方式

①设置折线图,线条填充样式为:无线条

②设置标记点:数据标记选项为原型,大小为35

填充颜色为:白色

标记表框为蓝色:5磅,线条类型为粗细结合式

excel技巧

4、设置数据标签及轴坐标。

点击选中次坐标,在设置坐标轴格式中,更改坐标轴的最大值为1,即100%;点击环形图的数据点以后,单击鼠标右键,选:添加数据标签;选中数据标签后,在设置数据标签格式中,更改标签位置为:居中。

5、设置图表标题,删除冗余刻度线条

如果觉得坐标轴的数值比较多的话,可以通过设置坐标轴格式,更改主单位的大小进行调整。

excel技巧

更高阶的图表比如仪表图、动态图、看板等。

excel技巧

excel技巧

excel技巧

 

excel快速添加超链接的方法一

按住ALT依次按F11,I,M
粘贴以下代码后按F5

Sub test()
For Each K In Columns(3).Hyperlinks
ActiveSheet.Hyperlinks.Add K.Parent.Offset(0, 1), K.Name
Next
End Sub

这时候使用以上方法就可以完成上图的操作了。

 

原来的宏

Sub 生成中山一院预约记录表()

 

If MsgBox(“1、最多只处理500条订单,如超出请联系技术部。2、订单数量大时请耐心等待。”, vbOKCancel) <> vbOK Then Exit Sub

 

ThisWorkbook.Worksheets(“sheet2″).Unprotect “85666190″

Sheet2.Select

Range(“a7:h10000″).Select ‘先清除历史数据

Selection.Delete

 

i = 3 ‘订单起始行数为3

j = 2 ‘打印页的 数据行 起始行数

a = i + 1 ‘订单第二行

b = i + 2 ‘订单第三行

c = i + 3 ‘第四行,如果没有流水号数据将停止复制

k = j + 1 ‘打印页的第二行数据

l = j + 2 ‘第三行

m = j + 3 ‘第四行

n = j + 4 ‘第五行

p = j + 6 ‘复制粘贴的起始行

x = p + 1 ‘粘贴的第二行

y = j – 1 ‘复制区域的起始行

e = i – 3 ‘确定处理的订单数量

f = i – 2

g = i – 1

h = j + 5 ‘需要调整行高的行

 

Do While i < 1000 ‘最大订单数为999

If Sheet1.Cells(i, 1) = “” Then GoTo end1 ‘没有数据则中止

‘If Sheet1.Cells(i, 1) > 0 Then Sheet2.Cells(j, 2) = Sheet1.Cells(i, 1)

If Sheet1.Cells(i, 11) > 0 Then Sheet2.Cells(k, 2) = Sheet1.Cells(i, 11)

If Sheet1.Cells(i, 6) > 0 Then Sheet2.Cells(l, 2) = Left(Sheet1.Cells(i, 6), 7)

If Sheet1.Cells(i, 5) > 0 Then Sheet2.Cells(m, 2) = Left(Sheet1.Cells(i, 5), 7)

If Sheet1.Cells(i, 7) > 0 Then Sheet2.Cells(n, 2) = Sheet1.Cells(i, 8   )

 

If Sheet1.Cells(a, 1) = “” Then GoTo end2

‘If Sheet1.Cells(a, 1) > 0 Then Sheet2.Cells(j, 5) = Sheet1.Cells(a, 1)

If Sheet1.Cells(a, 11) > 0 Then Sheet2.Cells(k, 5) = Sheet1.Cells(a, 11)

If Sheet1.Cells(a, 6) > 0 Then Sheet2.Cells(l, 5) = Left(Sheet1.Cells(a, 6), 7)

If Sheet1.Cells(a, 5) > 0 Then Sheet2.Cells(m, 5) = Left(Sheet1.Cells(a, 5), 7)

If Sheet1.Cells(a, 7) > 0 Then Sheet2.Cells(n, 5) = Sheet1.Cells(a, 8   )

 

If Sheet1.Cells(b, 1) = “” Then GoTo end3

‘If Sheet1.Cells(b, 1) > 0 Then Sheet2.Cells(j, 8  ) = Sheet1.Cells(b, 1)

If Sheet1.Cells(b, 11) > 0 Then Sheet2.Cells(k, 8  ) = Sheet1.Cells(b, 11)

If Sheet1.Cells(b, 6) > 0 Then Sheet2.Cells(l, 8  ) = Left(Sheet1.Cells(b, 6), 7)

If Sheet1.Cells(b, 5) > 0 Then Sheet2.Cells(m, 8  ) = Left(Sheet1.Cells(b, 5), 7)

If Sheet1.Cells(b, 7) > 0 Then Sheet2.Cells(n, 8  ) = Sheet1.Cells(b, 8   )

 

If i = 504 Then MsgBox “需要打印 28 张纸,纸张消耗过大,很不环保,请马上向公司汇报,点击确定继续”

 

Rows(h).Select

With ActiveWindow.RangeSelection

.RowHeight = 16

End With

If Sheet1.Cells(c, 1) = “” Then GoTo end4

‘Range(“a1:h6″).Select ‘选择最上面的6行进行复制

Cells(y, 1).Select ‘选择上面6行进行复制

Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count + 7).Select

Selection.Copy

Sheet2.Cells(p, 1).Select

ActiveSheet.Paste

Sheet2.Cells(x, 2).Select ‘粘贴后先进行原有数据的清除

Selection.Resize(Selection.Rows.Count + 4, Selection.Columns.Count).Select

Selection.ClearContents

Sheet2.Cells(x, 5).Select

Selection.Resize(Selection.Rows.Count + 4, Selection.Columns.Count).Select

Selection.ClearContents

Sheet2.Cells(x, 8  ).Select

Selection.Resize(Selection.Rows.Count + 4, Selection.Columns.Count).Select

Selection.ClearContents

 

i = i + 3 ‘订单每隔三行循环

j = j + 7 ‘打印页每隔7行循环

a = i + 1

b = i + 2

c = i + 3

k = j + 1

l = j + 2

m = j + 3

n = j + 4

p = j + 6

x = p + 1

y = j – 1

e = i – 3

f = i – 2

g = i – 1

h = j + 5

 

Loop

 

end1:

MsgBox “已处理 ” & e & ” 条订单,请核对数据是否完整”

GoTo end5

 

end2:

MsgBox “已处理 ” & f & ” 条订单,请核对数据是否完整”

GoTo end5

 

end3:

MsgBox “已处理 ” & g & ” 条订单,请核对数据是否完整”

GoTo end5

 

end4:

MsgBox “已处理 ” & i & ” 条订单,请核对数据是否完整”

GoTo end5

 

end5:

 

ThisWorkbook.Worksheets(“sheet2″).Protect “85666190″

Range(“a2″).Select

End Sub

/———*****************——–/

要添加的一行数据索引 加多一行病人名称

 

Sub 生成中山一院预约记录表()

 

If MsgBox(“1、最多只处理500条订单,如超出请联系技术部。2、订单数量大时请耐心等待。”, vbOKCancel) <> vbOK Then Exit Sub

 

ThisWorkbook.Worksheets(“sheet2″).Unprotect “85666190″

Sheet2.Select

Range(“a8:h10000″).Select ‘先清除历史数据

Selection.Delete

 

i = 3 ‘订单起始行数为3

j = 2 ‘打印页的 数据行 起始行数

a = i + 1 ‘订单第二行

b = i + 2 ‘订单第三行

c = i + 3 ‘第四行,如果没有流水号数据将停止复制

k = j + 1 ‘打印页的第二行数据

l = j + 2 ‘第三行

m = j + 3 ‘第四行

n = j + 4 ‘第五行

v = j + 5

p = j + 7 ‘复制粘贴的起始行

x = p + 1 ‘粘贴的第二行

y = j – 1 ‘复制区域的起始行

e = i – 3 ‘确定处理的订单数量

f = i – 2

g = i – 1

h = j + 5 ‘需要调整行高的行

 

Do While i < 1000 ‘最大订单数为999

If Sheet1.Cells(i, 1) = “” Then GoTo end1 ‘没有数据则中止

‘If Sheet1.Cells(i, 1) > 0 Then Sheet2.Cells(j, 2) = Sheet1.Cells(i, 1)

If Sheet1.Cells(i, 11) > 0 Then Sheet2.Cells(l, 2) = Sheet1.Cells(i, 11)

If Sheet1.Cells(i, 2) > 0 Then Sheet2.Cells(k, 2) = Left(Sheet1.Cells(i, 2), 7)

If Sheet1.Cells(i, 6) > 0 Then Sheet2.Cells(m, 2) = Left(Sheet1.Cells(i, 6), 7)

If Sheet1.Cells(i, 5) > 0 Then Sheet2.Cells(n, 2) = Left(Sheet1.Cells(i, 5), 7)

If Sheet1.Cells(i, 7) > 0 Then Sheet2.Cells(v, 2) = Sheet1.Cells(i, 8   )

 

If Sheet1.Cells(a, 1) = “” Then GoTo end2

‘If Sheet1.Cells(a, 1) > 0 Then Sheet2.Cells(j, 5) = Sheet1.Cells(a, 1)

If Sheet1.Cells(a, 11) > 0 Then Sheet2.Cells(l, 5) = Sheet1.Cells(a, 11)

If Sheet1.Cells(a, 2) > 0 Then Sheet2.Cells(k, 5) = Left(Sheet1.Cells(a, 2), 7)

If Sheet1.Cells(a, 6) > 0 Then Sheet2.Cells(m, 5) = Left(Sheet1.Cells(a, 6), 7)

If Sheet1.Cells(a, 5) > 0 Then Sheet2.Cells(n, 5) = Left(Sheet1.Cells(a, 5), 7)

If Sheet1.Cells(a, 7) > 0 Then Sheet2.Cells(v, 5) = Sheet1.Cells(a, 8   )

 

If Sheet1.Cells(b, 1) = “” Then GoTo end3

‘If Sheet1.Cells(b, 1) > 0 Then Sheet2.Cells(j, 8  ) = Sheet1.Cells(b, 1)

If Sheet1.Cells(b, 11) > 0 Then Sheet2.Cells(l, 8  ) = Sheet1.Cells(b, 11)

If Sheet1.Cells(b, 2) > 0 Then Sheet2.Cells(k, 8  ) = Left(Sheet1.Cells(b, 2), 7)

If Sheet1.Cells(b, 6) > 0 Then Sheet2.Cells(m, 8  ) = Left(Sheet1.Cells(b, 6), 7)

If Sheet1.Cells(b, 5) > 0 Then Sheet2.Cells(n, 8  ) = Left(Sheet1.Cells(b, 5), 7)

If Sheet1.Cells(b, 7) > 0 Then Sheet2.Cells(v, 8  ) = Sheet1.Cells(b, 8  )

 

If i = 504 Then MsgBox “需要打印 28 张纸,纸张消耗过大,很不环保,请马上向公司汇报,点击确定继续”

 

Rows(h).Select

With ActiveWindow.RangeSelection

.RowHeight = 16

End With

If Sheet1.Cells(c, 1) = “” Then GoTo end4

‘Range(“a1:h6″).Select ‘选择最上面的6行进行复制

Cells(y, 1).Select ‘选择上面6行进行复制

Selection.Resize(Selection.Rows.Count + 6, Selection.Columns.Count + 8  ).Select

Selection.Copy

Sheet2.Cells(p, 1).Select

ActiveSheet.Paste

Sheet2.Cells(x, 2).Select ‘粘贴后先进行原有数据的清除

Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count).Select

Selection.ClearContents

Sheet2.Cells(x, 5).Select

Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count).Select

Selection.ClearContents

Sheet2.Cells(x, 8  ).Select

Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count).Select

Selection.ClearContents

 

i = i + 3 ‘订单每隔三行循环

j = j + 8 ‘打印页每隔7行循环

a = i + 1

b = i + 2

c = i + 3

k = j + 1

l = j + 2

m = j + 3

n = j + 4

v = j + 5

p = j + 7

x = p + 1

y = j – 1

e = i – 3

f = i – 2

g = i – 1

h = j + 5

 

Loop

 

end1:

MsgBox “已处理 ” & e & ” 条订单,请核对数据是否完整”

GoTo end5

 

end2:

MsgBox “已处理 ” & f & ” 条订单,请核对数据是否完整”

GoTo end5

 

end3:

MsgBox “已处理 ” & g & ” 条订单,请核对数据是否完整”

GoTo end5

 

end4:

MsgBox “已处理 ” & i & ” 条订单,请核对数据是否完整”

GoTo end5

 

end5:

 

ThisWorkbook.Worksheets(“sheet2″).Protect “85666190″

Range(“a2″).Select

End Sub

 

 

复件 广州市番禺中心医院(模板)    (原件)

 

广州市番禺中心医院(模板)    (新件)

 

 

 

 

© 2012 Ai-WEB的博客 Suffusion theme by Sayontan Sinha