[原创]免费 实用的Excel工具箱推荐

qj.chen

免费 实用的Excel工具箱推荐

（qjchen @ scut）

初来乍到，先发帖子

Excel工具箱中最出名的应该是ASAP工具箱了
http://www.asap-utilities.com/
不过该工具是收费的，有许多限制。

宏是很好，可惜不见的人人会编，因而由高手编写的XLA工具箱是许多朋友喜爱的。绿色而实用。

个人装了三个，下面简单介绍一下。也希望各位大侠多推荐一些其他好的XLA工具箱，谢谢。
很感谢这些作者，能写出这些工具箱的无疑是高手，而他们还将其免费提供，非常感谢。

当今社会，多一个工具箱，意味着拥有更强的解决问题能力。

一个是“百宝箱4.0”，由andysky版主编写。此工具箱取于本版，自无需介绍，确实好用。

一个是Extools，由Vincent Yong编写，有不少实用内容，介绍如下
介绍网址：http://www.excel-extools.com/index.php
下载地址：http://www.excel-extools.com/download.php?file=extools2.0.zip

一个是 JMT Excel Utilities，由Masaru Kaji (aka Colo)和Andrew Engwirda两位微软MVP编写
JMT Excel Utilities is a great new utility with dozens of great features! This great new add-in was developed by Masaru Kaji (aka Colo) and Andrew Engwirda, both Microsoft Excel MVPs. They rigorously tested the utility and for a small fee you can even get the source code (with or without the add-in)! This is the best add-in since ASAP-Utilities! This utility is designed to work with Excel versions 97-2003, and a 2007 version is in the works! (We'll post a link here when it's available.)
介绍地址：http://www.vbaexpress.com/cooltools.php
注，Vbaexpress是国外一个著名的VBA论坛，有不少高手
上述地址尚有其他不少高手写的工具箱
下载地址：http://jmt.puremis.net/jmtutils/JMT_Excel_Utilities.zip

分别介绍如下：
1）Extools
图文可见此处
http://qjchen.yo2.cn/go/474651.html

下载地址：http://www.excel-extools.com/download.php?file=extools2.0.zip

安装，解压到一个目录，在excel的工具->加载宏->浏览，选xla文件，就会新增一个菜单项的。

安装后界面：

基本功能介绍：其实这就是一个加了菜单功能之后的宏包，不过内容可是加密的的。

部分基本功能介绍：

部分基本功能介绍：

Favorite file：添加常用的excel文件到列表，便于打开
Favorite text：添加常用的输入文字，方便录入

Range：选区（这些功能我都很喜欢，每个函数都很好！，本都想编的，不过得花许多时间，有些还不会编）
Delete empty cells in column：删除选区内所有列中的空格项，后续项向上移动
Delete empty rows in selections： 选择一个区域，将其内的所有空行删除（注意，是整行删除）
Transpose：转置，其实这个excel本来就有，它的做法是：在执行菜单命令之前，要先有选区，然后点击需要转置到的左上角位置；
Reverse Order（Flip cells）：逆向，选择一行或者一列选区，会得到逆序结果；
Check for duplicate：检查重复数据，结果可以是删除或者高亮；
Multi selection copy：多个选区的拷贝。excel中，多个选区的选择是按ctrl来完成的，但是非常奇怪的是无法粘贴，怪异，这个宏可以完成。
Save selection as files：选择一个区域，存为excel,csv,txt文件。虽说excel本身也可做到，但是对有大量此需要的朋友确非常实用。我就是在donation software的网站上看到朋友提问和回答这个问题才发现这个软件的。
Save selection as html： 选择区域，写出html的表格代码，好功能，也能写出格式，且代码是比较精简的。相比excel本身的输出html的大量冗余代码要好的多。
Save selection as picture：选择区域，输出图像（png,gif,jpeg），好。

看了半天才只是1/10的菜单，哇，看来得几次才介绍的完了，都是我很梦想得到的功能（虽然我一般用tecplot,origin或者sigmaplot来绘制图表，但是数据整理方面还是excel做的最方便，包括选择，删除，计算等），看来开发者确实是个对excel不足之处很有认识的高手啊，想到和许多没有想到的功能它都有。

其他的功能介绍待添加，包括工作表对比，删除所有空表，sheet排序，多sheets打印，自动尾数字变上下标，公式的绝对引用和相对引用转换等等好功能。

继续，
Worksheet目录：
View：视图控制，包括下列项的显示与否，网格线，行列的标题（就是A，B，C，1，2，3等），水平垂直滚动条，sheet标签，打印页显示，显示为公式，状态栏，工具栏等。
Compare WorkSheets：依次列出两个sheet中所有内容不同的格子的值的不同和公式的不同，很好
Create WorkSheet Map：给出一个表格中每个格子的内容情况，标明某个格子是文本，数字，公式，错误或者逻辑
Sort Sheets：将标签排序
Delete Empty Sheet：删除空sheet
Hide Blank Row / Column At end of Sheet：选右下角，隐藏掉A1到此右下角外的所有行列部分，（注意，和Hide不同，是真的隐藏，可以吓人一跳的哦）
UnHide —：这两个是反隐藏——显示所有的sheets或者某个sheet
Remove HyperLinks in Worksheet——去掉所有页面内的超链接，好功能。MS自作主张加上的超链接，许多时候个人并不喜欢，要去掉还不是一般的麻烦，这个功能我很喜欢。
Reset Last Cell——似乎是回到左上角？

Workbook（这些功能我倒比较少用）
Save and Backup：文件存盘的同时做备份
close all saved workbook：关闭掉所有保存了的文件
close and delete current workbook： 关闭并删除本文件（可能在临时文件时有用）
close all workbooks without saving：关闭所有文件，不做保存（对于只是读取许多文件的时候有用）

Application：
List All Font：列出所有字体
Clear the Recently files list：清除最近的使用文件列表

Select：选择，也是一个很好的工具箱，有点类似AutoCAD里面的quickselect
Conditional Select cells（rows） by Value： 按数值过滤器选择单元格或者行，包括：介乎多少之间，大于，小于，等于，正则表达式等等。方便选取数值
Conditional Select cells by Format： 按格式过滤器选择单元格，包括，正体斜体，颜色，字体大小。可以选择一个单元格作为参照。
Select Cells With Max（Min） Value： 选择最大（最小）的数值所在的格，相同时选出第一个。Excel本身的Max只给出最大值而没有位置。
Select Cells With error：选中出错的单元格
Select last cell：选中最右列和最下行的相交单元格
Select used area：选中图表中最左上角和最右下角形成的区域
Select to right（bottom）：将选区扩展到有内容的最右边或者最下边，似乎有些bug。
Select all sheets：选中所有的sheet
Select all empty sheets：选中所有的空Sheet

Text：不错的文本处理
包含
Case：改变大小写
Insert：在所有字符串前面，后面，或者从左偏移地 加入一个新的字符串
Trim：去掉左，或右，或全部 等空格，或者前后各剪切掉一定长度的字符
Remove： 去掉指定字符
Combine：横向进行字符串的合并，可以加空格

Formula and Numbers：
copy cells formula as is (extra copy)： 精确拷贝公式内容。有时候，我们想拷贝单元格内的公式，但是一移位粘贴，excel非常聪明地就帮我们按照相对坐标对公式进行修改，现在有这个功能就有更多的选择了。
Convert Formula to Constant Value：将公式内容改成数值，这个不错，以前经常得拷贝，选择性粘贴-数值几步
Convert Between Absolute and Relative Reference：在绝对引用和相对引用中转换，很好，虽然$很让人喜欢，不过大量输入的时候可挺让人烦躁的。
Perform Math Calculation on Selection：对选择内容进行批量加减乘除，也挺好。
Unit Converter：将选择内容在两种单位制间转换，要是能帮忙加上一些强度、容重之间的转换就好了
round number： 真正的四舍五入
convert percent to number：百分制变数字
covert number to ordinal：把数字变成1st,2nd之类
Convert Number to text ：把数字变成英文的one two ten hundred
Convert Between Arabic and Roman： 在阿拉伯数字和罗马数字间转换
Insert random number to selection： 在选区内插入随机数

Format：
Set x2：set subscript/superscript ：将最后的一位文字或者数字变成下标或者上标（好功能）
Word Wrap On/off：自动换行开关
Center Verticall： 直接垂直居中

Print
Print multiple sheets：打印多个sheets
Set filepath as header /footer：把文件的路径打印在上方或者下方

Date：（似乎作用不大）
Number of days between two days： 计算两天之间的天数
add days to date： 把某天加上天数得到新一天

2）JMT Excel Utilities（待翻译）
Sheets Utilities 
 Show Worksheet Tools
 Save Sheet As Workbook
 Show Selection Tools
 Highlight Blank Cells Conditionally
 Highlight Range Without Formatting
 Deselect Cells Within Range
 Show Selection Dimensions
 Show Row And Column Tools
 Insert Rows And Columns
 Show Comment Tools
 Copy and Paste Exact Formulas
 Copy Merged Area Top Left Cell
 Hide Error Messages In Formulas
 Cond. Formatting Find And Replace
 Show Deletion Tools
 Clear The Active Sheet
 Delete External Links
 Show Drawing Objects Tools
 Copy Selection As HTML Table
 Export Picture Of Selection
 Copy Picture Of Selection
 Export Range As Text File
 Show Checkbox Tools

Data Utilities 
 Show Text And Format Tools
 Insert Sequential List In Selection
 Find Maximum And Minimum Values
 Insert Bar Graph In Selection
 Convert Numbers To Text (Add ' Mark)
 Revert Text Numbers To Real Numbers
 Move Minus Sign To Front
 Positive And Negative Numbers
 Perform Quick Math On Selection
 Round Numbers In Selection
 Copy Sum Of Selection To Clipboard
 Delete Duplicates In Selection
 Show Conversion Tools
 Show Excel Calculator
 Show Special Characters
 Join Cell Text
 Insert Multi Line Cell Text
 Show Quick Write
 Show Time Picker
 Insert Calendar Sheet
 Show Day and Date Calculator
 Select Days And Months
 Show Calendar Date Picker
 
Other Utilities 
 Change Excel Application Settings
 Show Full Path And Book Name in Titlebar
 Copy Pathname To Clipboard
 Open Dialog Box With Current File's Folder
 Open Explorer With Current File's Folder
 Show Folder Details in Worksheet
 Add Workbook to Recent Files
 Clear Recent Files List
 Rename Active Workbook While Open
 Delete Active Workbook While Open
 Save Workbook With Backup File
 Reopen Workbook Without Saving
 Close Saved Workbooks
 Insert Headers And Footers
 Delete Active Workbook Macros
 Show Excel Menu Details
 Show Face IDs Toolbar
 Show Excel Color Details
 Show Color Picker
 Web Search From Excel
 Add JMT Functions To Workbook
 JMT Excel Utilities Settings
 Show JMT Help Files

Custom Functions 
 Custom Functions, How to use them
 Custom Function Details

Other 
 JMT Excel Utilities

[此贴子已经被作者于2008-2-2 12:46:45编辑过]

andysky

看了一下。

Extools，由Vincent Yong编写的工具代码加密方式不错。

不知是否有高手可以破解。

---

JMT Excel Utilities，由Masaru Kaji (aka Colo)和Andrew Engwirda两位微软MVP编写。
这个工具中关于日期、日历等等的工具太多了（居然包括23个窗体）。个人认为通过窗体（包括任何工具）来输入时间和日期都不会为用户提升工作效率。

例如输入2007-10-1或者今天日期，手工输入或者用函数输入都很快，不会慢于从菜单打开窗体，再从窗体中分别选择年月日等等。所以这个工具实例性极小。

另外，Excel显示设置等工具也差不多，完全是Excel自带功能的重复，在Excel选项和工具栏都有现成的选项。个人认为既然是二次开发，就应该开发一些Excel的菜单栏、工具栏、选择设置中没有的工具。

原工具见下图：

他的工具中“选择工具”（Selection Utilities）很不错，实用性很高，值得大家学习。

我的工具箱中也有一个“定位工具箱”，第一版（百宝箱3.0和4.0）中不支持双条件（如介于50-100之间），这一点差于他的工具。但支持选择作用范围（如选择区域、已用区域、可见区域），也支持文本对象的模糊查询，这两点较实用。第二版（百宝箱5.0和6.0已加入双条件选择）已加入双条件选择。但基于单元格格式的选择还是没有。有必要考虑是否强化本工具。

最后补充一点，国外人士的工具不会考虑中国国情的。例如中国人关心的简繁转换、大小写转换、分页小计、制作工资条等等还是要靠中国人自己。

[此贴子已经被作者于2008-2-4 15:45:56编辑过]

qj.chen

Vincent Yong人挺好的，解答问题很热心的，可以跟他发个邮件啊：）

andysky

QUOTE:

以下是引用qj.chen在2008-2-2 13:36:46的发言：
Vincent Yong人挺好的，解答问题很热心的，可以跟他发个邮件啊：）

嘿嘿，他听不懂我说话，我也听不懂他说话

---

<...> 删除原帖留言 - taller

[此贴子已经被taller于2008-2-6 0:55:22编辑过]

luo159

Masaru Kaji (aka Colo)和Andrew Engwirda两位微软MVP编写的程序也不一定就多好。

看了几段代码，有很多有是争议的或者是某些代码可要可不要，或者稍微修改一点就可以将代码缩短，且功能不变。

例如提取颜色一段代码。原代码如下：

Sub ShowColorIndex()
    Const PROC_NAME As String = "ShowColorIndex"
    On Error Resume Next
    Dim myColors As Long, COLORNAME As String
    Dim HexString As String, HTMLcolor As String, RGBColor As String
    Dim ColName As Variant, iNewBookSheets As Integer

    If Not HasActiveWkb Then Exit Sub

    UserFormSheetOrBook.Show

    If bCancel = True Then
        bCancel = False
        On Error GoTo 0
        Exit Sub
    End If

    If ShowDetails = "New Sheet" Then
        If BookIsProtected Then Exit Sub
    End If

    Call SetCalcSetting

    If ShowDetails = "New Sheet" Then
        ActiveWorkbook.Sheets.Add.Name = "Colors"
    Else
        With Application
            iNewBookSheets = .SheetsInNewWorkbook
            .SheetsInNewWorkbook = 1
        End With
        Workbooks.Add
    End If

    For myColors = 1 To 56
        Cells(myColors, 1).Interior.ColorIndex = myColors
        Cells(myColors, 2) = myColors
        HexString = Right("000000" & Hex(Cells(myColors, 1).Interior.Color), 6)
        HTMLcolor = "#" & Right(HexString, 2) & Mid(HexString, 3, 2) & Left(HexString, 2)
        Cells(myColors, 3) = HTMLcolor
        RGBColor = Cells(myColors, 1).Interior.Color Mod 256
        RGBColor = RGBColor & " " & Int(Cells(myColors, 1).Interior.Color / 256) Mod 256
        RGBColor = RGBColor & " " & Int(Cells(myColors, 1).Interior.Color / 256 / 256)
        Cells(myColors, 4) = RGBColor
        ColName = Array("Black", "White", "Red", "Bright Green", "Blue", "Yellow", "Pink", "Turquoise", _
                        "Dark Red", "Green", "Dark Blue", "Dark Yellow", "Violet", "Teal", "Gray-25%", "Gray-50%", "Periwinkle", _
                        "Plum", "Ivory", "Light Turquoise", "Dark Purple", "Coral", "Ocean Blue", "Ice Blue", "Dark Blue", "Pink", _
                        "Yellow", "Turquoise", "Violet", "Dark Red", "Teal", "Blue", "Sky Blue", "Light Turquoise", "Light Green", _
                        "Light Yellow", "Pale Blue", "Rose", "Lavender", "Tan", "Light Blue", "Aqua", "Lime", "Gold", "Light Orange", _
                        "Orange", "Blue-Gray", "Gray-40%", "Dark Teal", "Sea Green", "Dark Green", "Olive Green", "Brown", _
                        "Plum", "Indigo", "Gray-80%")
        COLORNAME = ColName(myColors)
        Cells(myColors, 5) = COLORNAME
    Next
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("B1").Value = "Color Index"
    Range("C1").Value = "HTML Colors"
    Range("D1").Value = "RGB Index"
    Range("E1").Value = "Color Name"
    Range("B1:E1").Font.Bold = True
    Columns("B:B").HorizontalAlignment = xlCenter
    Columns("B:E").Columns.AutoFit
    Range("A1").Select

    If ShowDetails = "New Book" Then
        Application.DisplayAlerts = False
        ActiveWorkbook.Sheets(2).Delete
        Application.DisplayAlerts = True
        ActiveSheet.Name = "Colors"
    End If

    Application.SheetsInNewWorkbook = iNewBookSheets
    Call SetCalcSetting("Restore")
    On Error GoTo 0
End Sub

[此贴子已经被作者于2008-2-2 15:30:40编辑过]

luo159

首先：变量声明不准确Dim myColors As Long这一句并不好，程序中myColors在1-56之间，声明为long将使程序速度降低。
其次：代码不紧凑

以下代码
    If bCancel = True Then
        bCancel = False
        On Error GoTo 0
        Exit Sub
    End If
可以修改为       If bCancel = True Then  bCancel = False： On Error GoTo 0： Exit Sub
这样显得结构紧凑一些
再次：多余的循环

将下变这一句加在循环语句中间无疑使程序变慢，而事实上根本不需要。无端使程序效率降低...........
ColName = Array("Black", "White", "Red", "Bright Green", "Blue", "Yellow", "Pink", "Turquoise", _
                        "Dark Red", "Green", "Dark Blue", "Dark Yellow", "Violet", "Teal", "Gray-25%", "Gray-50%", "Periwinkle", _
                        "Plum", "Ivory", "Light Turquoise", "Dark Purple", "Coral", "Ocean Blue", "Ice Blue", "Dark Blue", "Pink", _
                        "Yellow", "Turquoise", "Violet", "Dark Red", "Teal", "Blue", "Sky Blue", "Light Turquoise", "Light Green", _
                        "Light Yellow", "Pale Blue", "Rose", "Lavender", "Tan", "Light Blue", "Aqua", "Lime", "Gold", "Light Orange", _
                        "Orange", "Blue-Gray", "Gray-40%", "Dark Teal", "Sea Green", "Dark Green", "Olive Green", "Brown", _
                        "Plum", "Indigo", "Gray-80%")
还有：多余的变量

以下两句可以合并。原代码无端端多使用一个变量，也会导致程序效率低下
COLORNAME = ColName(myColors)
        Cells(myColors, 5) = COLORNAME
可以缩为一句：  Cells(myColors, 5) = ColName(myColors)
还有：这两句也是不必要的。在前面的代码加1就可避免这两个操作（先选择然后插入）。况且选择行再插入本就是多余，可以不用选择而直接插入，这是VBA基础课上需要讲的。
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
最后：多余的赋值操作

下面的代码赋值四次，完全可以一次完成，提升效率。
    Range("B1").Value = "Color Index"
    Range("C1").Value = "HTML Colors"
    Range("D1").Value = "RGB Index"
    Range("E1").Value = "Color Name"
以上改为：    Range("B1:E1").Value = Array("Color Index", "HTML Colors", "RGB Index", "Color Name")
等等，等等...............

---

可以精简码为：
Sub ShowColorIndex()
    On Error Resume Next
    Dim myColors As Byte, COLORNAME As String, HexString As String, HTMLcolor As String, RGBColor As String, ColName As Variant
    Sheets.Add.Name = "Colors"
    ColName = Array("Black", "White", "Red", "Bright Green", "Blue", "Yellow", "Pink", "Turquoise", "Dark Red", "Green", _
                        "Dark Blue", "Dark Yellow", "Violet", "Teal", "Gray-25%", "Gray-50%", "Periwinkle", "Plum", "Ivory", "Light Turquoise", _
                        "Dark Purple", "Coral", "Ocean Blue", "Ice Blue", "Dark Blue", "Pink", "Yellow", "Turquoise", "Violet", "Dark Red", _
                        "Teal", "Blue", "Sky Blue", "Light Turquoise", "Light Green", "Light Yellow", "Pale Blue", "Rose", "Lavender", "Tan", _
                        "Light Blue", "Aqua", "Lime", "Gold", "Light Orange", "Orange", "Blue-Gray", "Gray-40%", "Dark Teal", "Sea Green", _
                        "Dark Green", "Olive Green", "Brown", "Plum", "Indigo", "Gray-80%")
    For myColors = 1 To 56
        Cells(myColors + 1, 1).Interior.ColorIndex = myColors
        Cells(myColors + 1, 2) = myColors
        HexString = Right("000000" & Hex(Cells(myColors, 1).Interior.Color), 6)
        HTMLcolor = "#" & Right(HexString, 2) & Mid(HexString, 3, 2) & Left(HexString, 2)
        Cells(myColors + 1, 3) = HTMLcolor
        RGBColor = Cells(myColors + 1, 1).Interior.Color Mod 256
        RGBColor = RGBColor & " " & Int(Cells(myColors + 1, 1).Interior.Color / 256) Mod 256
        RGBColor = RGBColor & " " & Int(Cells(myColors + 1, 1).Interior.Color / 256 / 256)
        Cells(myColors + 1, 4) = RGBColor
        Cells(myColors + 1, 5) = ColName(myColors - 1)
    Next
    Range("B1:E1").Value = Array("Color Index", "HTML Colors", "RGB Index", "Color Name")
    Columns("B:E").Columns.AutoFit
    Range("A1").Select
    On Error GoTo 0
End Sub

[此贴子已经被作者于2008-2-2 15:49:51编辑过]

luo159

还有，原工具箱中以下代码是打开当前工作簿的文件夹。

Private Sub OpenExplorerDirectory()
    Const PROC_NAME As String = "OpenExplorerDirectory"
    On Error Resume Next

    If Not HasActiveWkb Then Exit Sub
    Shell "explorer.exe /n,/e," & Application.Substitute(ActiveWorkbook.FULLNAME, ActiveWorkbook.Name, ""), vbMaximizedFocus

    On Error GoTo 0
End Sub

然而可以修改一下：

Private Sub OpenExplorerDirectory()
    Shell "explorer.exe /n,/e," & ActiveWorkbook.Path, vbMaximizedFocus
End Sub

各位试试效果两段代码运行后的效果。

可见微软MVP认证有时可能也马马虎虎。

luo159

还有这段代码(提取Excel内存菜单与ID号)同样问题多多：
Private Sub MenuDetails()
........
end sub
不是说程序错误，而是可以大大精简，简化一些不必要的操作
例如以下语句可以用一句搞定：
Range("A1").Value = "Menu Name"
    Range("B1").Value = "Caption"
    Range("C1").Value = "ID No."
    Range("D1").Value = "Visible"
    Range("E1").Value = "Menu Type"
    Range("F1").Value = "(Local Name)"
.............................
另外，一个极度简单的选择也用提供一个窗体，这将使程序体积变大，降低启动速度，完全可以用一个inputbox来代替
..............

paipaitemp

上面的几位都很厉害！

海孩

留个记号，方便查找。