office 小技巧

1. 数据匹配 VLOOKUP

　　例一：如图，将左表（sheet1）的e列关联到右表（sheet2），条件为: sheet2.字段名 = sheet1.D

　　公式：=VLOOKUP(sheet2!D2:D653, sheet1!D$1:E$528, 2, 0)     #最好固定检索区间，否则可能出现部分没关联上

     

　　例二：在例一的条件下，增加条件：sheet2.表名 = sheet1.C 

　　公式：=VLOOKUP(sheet2!C2:C653&sheet2!D2:D653, IF({1,0}, sheet1!C$1:C$528&sheet1!D$1:D$528, sheet1!E$1:E$528), 2, 0)

　　执行：ctrl + shift + enter

　　例三：横向匹配

　　公式：=VLOOKUP($H$3,$B$3:$F$12,COLUMN(B1),0)

　　例四：纵向匹配

　　公式：=IFERROR(VLOOKUP(ROW(A1),$A$3:$C$12,3,0),"")    #增加iferror判断

2. excel目录

　　插入【超链接】

      

  3. word中插入横向页面

　　1）定位至第1页末尾，插入【布局】【分隔符】【分节符】【下一页】

　　2）定位至第2页末尾，插入【布局】【分隔符】【分节符】【下一页】

　　3）定位至第2页，执行【布局】【纸张方向】【横向】

 4. 批量合并单元格

　　1）排序：要合并的列排序；

　　2）分类汇总：选中要批量合并的区域，数据》分类汇总》确定；

　　3）定位控制：ctrl+g（或开始》查找和选择》定位条件）调出【定位条件】窗口，定位条件》空值》确定；

　　4）开始》合并并居中；

　　5）点击分类汇总结果的其中一个值，分类汇总》选中需要删除的项》全部删除；

　　6）开始》格式刷》点要批量合并的第一个值。

5. 相同值凸显标记相同颜色VBA代码

Sub ColorCompanyDuplicates()
'Updateby Extendoffice 20160704
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xChar As String
    Dim xCellPre As Range
    Dim xCIndex As Long
    Dim xCol As Collection
    Dim I As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xCIndex = 2
    Set xCol = New Collection
    For Each xCell In xRg
      On Error Resume Next
      xCol.Add xCell, xCell.Text
      If Err.Number = 457 Then
        xCIndex = xCIndex + 1
        Set xCellPre = xCol(xCell.Text)
        If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex
        xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex
      ElseIf Err.Number = 9 Then
        MsgBox "Too many duplicate companies!", vbCritical, "Kutools for Excel"
        Exit Sub
      End If
      On Error GoTo 0
    Next
End Sub

　　执行步骤：

　　1） alt + f11, 调出 VBA 环境窗口；

　　2）插入 > 模块，f5执行，或运行 > 运行子模块， 选中代码执行区域

6.提取字符串中数字

#如果数据中数字的起始位置和长度是一致的
=--mid(a1,2,4) 
#如果数字都位于某个特定字符串的后面，需按组合键ctrl+shift+enter来结束
=-lookup(0,-mid(a1,find("ABC",a1)+LEN("num"),row(1:99)))
#如果数字在字符串的前面
=left(a1,2*len(a1)-lenb(a1))
#如果数字在字符串的后面
=right(a1,2*len(a1)-lenb(a1))
#如果没有给出任何条件，需按组合键ctrl+shift+enter来结束
#SEARCHB("?",A1) 找到A1中第一个单字节字符，公式》公式求值，可查看计算过程
=-lookup(0,-mid(a1,min(find(row(1:10)-1,a1&1/17)),row(1:99)))
=--MIDB(A1,SEARCHB("?",A1),2*LEN(A1)-LENB(A1))

7. excel中回车换行符替换

　　word中 ‘^p’ 表示回车换行符

　　excel中可用 'Alt + 小键盘1 + 小键盘 0' 组合键，或 ‘Ctrl + j’ 组合键打出回车换行符