财会

Excel数据分列两法

@凌波踏月

    在处理Excel数据时，经常遇到一长串有规律的长数据，为了符合标准需要对长数据进行分列操作，例如四组张三、三组李四、三组王五，需要将人名和小组序列分开显示。Excel分列有两种情况，一种是固定宽度的分列，就是“四组张三、三组李四、三组王五”这种，另外一种是有特征符号的分列，例如“123,1234,12345,123456”。具体操作如下所示：

    先将数据单独整理出来，放入一张新建的工作表的A列中，然后选中A列，点击菜单栏中的“数据”，再选择“分列”，在弹出的出口中可以看到两个选项，一个是“固定宽度”，另外一个是“分隔符号”。

    如果选择的是“固定宽度”，点击“下一步”，在新窗口中的“数据预览”处移动标尺，通过标尺自定义一个宽带，最后点击“完成”即可；如果选择的是“分隔符号”， 点击“下一步”，在新窗口中的勾选特定的符号，如果不是预设的符号，可以点击“其他”，在后面的空白处输入特殊的符号，最后点击“完成”即可。需要注意的是，分列后的数值如果是0开头的，这个0就无法显示，需要提前将单元格数值为“文本”格式。

董师傅点评：分列之后又想合并怎么办？可以借助公式=CONCATENATE(A1,B1)搞定哟！

自动提取工作表名和指定内容

@悦心自足

    如果一个Excel工作簿里面有多张表格，翻开起来就比较麻烦，可以设计一个目录表格，目录中包含每张表格的名称，以及制表人的姓名，这个目的如何实现呢？可以借助VBA代码实现，代码如下：    

Sub 汇总()

Application.ScreenUpdating = False

Dim wb As Excel.Workbook

Dim sh, sht As Worksheet

Set sht = ThisWorkbook.Worksheets("汇总")

sht.Range("a2:c60000") = ""

f = Dir(ThisWorkbook.Path & "\*.xls*") '生成查找EXCEL的目录

Do While f > "" '在目录中循环

If f <> ThisWorkbook.Name Then  '如果不是打开的工作簿

Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & f)

p = wb.Worksheets.Count

  For Each sh In wb.Worksheets

   pp = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1

  sht.Cells(Rows.Count, 1).End(xlUp).Offset(1) = sh.Name

   sht.Cells(pp, 3) = sh.[P100]

   Next sh

     sht.Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(p, 1) = Split(wb.Name, ".")(0)

     ActiveWorkbook.Close False

    End If

    f = Dir

    Loop

    MsgBox "ok！全部搞定了！"

    Application.ScreenUpdating = True

End Sub

    在这里，把第一张工作表的名称改为“汇总”，制表人名称所在的单元格都假设为P100,具体可以根据实际情况修改。最后就是为新增加的宏命令指定一个按钮，今后点击按钮就可以完成自动目录提取和制表人名称提取了。

    董师傅点评：要使用宏功能，需要在“工具”→“宏” →“安全性”中降低安全级别。

提取WPS表格中身份证信息

@古月曦

    笔者今年加入一个初创公司，公司之前只是一个工作室，如今又二三十人了，人多了各种杂事也多，比如笔者就要整理一份职工信息。之前的职工信息表格只有姓名、身份证号码、毕业院校和手机，现在要增加生日和性别，未来员工生日要送一个蛋糕、三八妇女节要送每个女员工一朵玫瑰花。人不多可以手动增加，不过公司后续招人怎么办？经过研究，可以通过公式实现目的，操作如下：

    在WPS表格中，A列是序号、B列是员工姓名、C列为身份证号码、D列为生日、E列为性别、F列为毕业院校、G列为手机号码，在D2单元格中输入公式“=IF(LEN(C2)=15,TEXT(MID(C2,7,6),"1900年00月00日"),TEXT(MID(C2,7,8),"00年00月00日"))”， 拖动填充柄向下复制公式，这样就完成了出生日期的提取，知道出生日期自然就知道生日是那天了；在E2中输入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")”，拖动填充柄向下复制公式，这样就完成了性别信息的提取。公式中的LEN是长度函数，MID是提取字符函数，TEXT是转换格式函数，MOD是整除函数，IF是判断函数。

    董师傅点评：如果想把出生日期转换为日期类型，需要在公式中的TEXT前加两个减号，变成减负运算。

请关注董师傅微博

        怎么办公最有效率？怎么办公最轻松？来看看董师傅的珍藏，相信你一定会有意外的收获。顺带也可以听听董师傅的理财心得，倾听财富的声音！同时有问题也可以微博私信董师傅。