提取A1中8个连续数字:
=IFERROR(0+MID(A1,MATCH(10,MMULT(ABS(ISNUMBER(0+MID(MID(“ζ”&A1&”ζ”,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-7)),10),{1,2,3,4,5,6,7,8,9,10},1))-{1,0,0,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1;1;1}),0),8),””)
分类: Office软件
PPT 批量更改字体、颜色
Sub OED01()
Dim oShape As Shape
Dim oSlide As Slide
Dim oTxtRange As TextRange
On Error Resume Next
For Each oSlide In ActivePresentation.Slides
For Each oShape In oSlide.Shapes
Set oTxtRange = oShape.TextFrame.TextRange
If Not IsNull(oTxtRange) Then
With oTxtRange.Font
.Name = “微软雅黑” ‘更改为需要的字体
.Color.RGB = RGB(Red:=0, Green:=0, Blue:=0) ‘改成想要的文字颜色,用RGB参数表示,黑色全为0,白色全为255
End With
End If
Next
Next
End Sub
每次打开excel,所有数据都变成日期格式
EXCEL提取单元格中第一个数字
=MID(G2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},G2&"0123456789",1)),1)
提取G2单元格中第一个数字。
解决Excel自动化(Automation)错误发生意外遇到问题(批处理)
今天遇到的问题,打开任意Excel文档,均出现“Microsoft Office Excel 遇到问题需要关闭。我们对此引起的不便表示抱歉。您正在处理的信息有可能丢失。Microsoft Office Excel 可以尝试为您恢复”。
然后出现“Microsoft Visual Basic 自动化(Automation)错误 发生意外。”的对话框,如下图所示:
关于Microsoft Office Excel遇到问题需要关闭的信息倒有点类似于之前遇到的由于Normal.dot损坏导致Word打开出现的对话框,于是顺着上次的思路进行手动的修复。
建议大家先阅读我的《解决Word遇到问题需要关闭和“安全模式”启动问题》那篇文章,部分准备步骤与那篇文章描述一致,我就不累述了,一是关闭所有Excel文档,打开“任务管理器”,结束残余的Excel.exe进程;二是打开隐藏文件开关;三是开始运行输入
以便于打开配置文件夹,分别删除
和
下所有文件,这一步我找到了问题的所在,在XLSTART目录下存在一个带毒的k4.xls文档,这个目录下的Excel文档所包含的程序将会在打开Excel时自动加载,这样k4.xls中所包含的恶意代码就可以执行并感染正常Excel了,所以我们立即删除它;四是找到Office的安装路径,比如我的Office 2003装在D盘Program Files,那么路径就是
,这里Office11对应着Office 2003,同样的Office12对应Office 2007,Office 14对应着Office 2010,好了,找到
文件夹,删除这个文件夹内的所有文件。
通过以上步骤后,及时升级更新你的杀毒软件,全盘查杀病毒,完成后Excel就可以正常打开了。
当然你也可以尝试我编写的批处理来快速解决这个问题,将下面代码另存为批处理文件(*.bat),双击运行即可修复Excel,如果是Vista、Win7或者更高版本请右击以管理员身份运行,运行前请保存你的所有Excel工作簿并且关闭Excel程序,因为本批处理会强制结束未关闭的Excel运行以保证清理效果:
<span class="pln">REM
REM COPYRIGHT </span><span class="pun">:</span> <span class="typ">Copyright</span> <span class="pun">(</span><span class="pln">c</span><span class="pun">)</span> <span class="lit">2013</span> <span class="typ">WangYe</span><span class="pun">.</span> <span class="typ">All</span><span class="pln"> rights reserved</span><span class="pun">.</span><span class="pln">
REM
REM DESCRIPTION </span><span class="pun">:</span> <span class="typ">Clear</span><span class="pln"> ALL FILES </span><span class="kwd">in</span> <span class="typ">Excel</span><span class="pln"> XLSTART directory</span><span class="pun">.</span><span class="pln">
REM AUTHOR </span><span class="pun">:</span><span class="pln"> WANGYE
REM WEBSITE </span><span class="pun">:</span><span class="pln"> http</span><span class="pun">:</span><span class="com">//wangye.org</span><span class="pln">
REM
REM </span><span class="typ">Compatible</span> <span class="kwd">with</span> <span class="typ">Office</span> <span class="lit">2003</span><span class="pun">/</span><span class="lit">2007</span><span class="pun">/</span><span class="lit">2010</span><span class="pln">
REM
REM </span><span class="typ">Last</span> <span class="typ">Update</span> <span class="pun">:</span> <span class="lit">2013</span><span class="pun">/</span><span class="lit">1</span><span class="pun">/</span><span class="lit">10</span><span class="pln">
REM
</span><span class="lit">@ECHO</span><span class="pln"> OFF
</span><span class="pun">:</span><span class="kwd">BEGIN</span><span class="pln">
CLS
ECHO </span><span class="typ">Copyright</span> <span class="pun">(</span><span class="pln">c</span><span class="pun">)</span> <span class="lit">2013</span> <span class="typ">WangYe</span><span class="pun">.</span> <span class="typ">All</span><span class="pln"> rights reserved</span><span class="pun">.</span><span class="pln">
ECHO </span><span class="typ">For</span><span class="pln"> more information please visit http</span><span class="pun">:</span><span class="com">//wangye.org</span><span class="pln">
ECHO</span><span class="pun">.</span><span class="pln">
ECHO </span><span class="typ">Please</span><span class="pln"> wait</span><span class="pun">...</span><span class="pln">
TASKKILL </span><span class="pun">/</span><span class="pln">F </span><span class="pun">/</span><span class="pln">IM EXCEL</span><span class="pun">.</span><span class="pln">EXE</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">></span><span class="pln">NUL
SETLOCAL </span><span class="typ">EnableDelayedExpansion</span><span class="pln">
FOR </span><span class="pun">/</span><span class="pln">L </span><span class="pun">%%</span><span class="pln">i IN </span><span class="pun">(</span><span class="lit">14</span><span class="pun">,-</span><span class="lit">1</span><span class="pun">,</span><span class="lit">11</span><span class="pun">)</span><span class="pln"> DO </span><span class="pun">(</span>
<span class="kwd">if</span> <span class="pun">%%</span><span class="pln">i NEQ </span><span class="lit">13</span> <span class="pun">(</span><span class="pln">
SET KEY_NAME</span><span class="pun">=</span><span class="str">"HKLM\SOFTWARE\Microsoft\office\%%i.0\Excel\InstallRoot"</span><span class="pln">
FOR </span><span class="pun">/</span><span class="pln">F </span><span class="str">"tokens=2*"</span> <span class="pun">%%</span><span class="pln">A IN </span><span class="pun">(</span><span class="str">'REG QUERY !KEY_NAME! /v "Path" 2^>NUL ^| FINDSTR "REG_SZ"'</span><span class="pun">)</span><span class="pln"> DO </span><span class="pun">(</span><span class="pln">
SET </span><span class="typ">ExcelInstallDir</span><span class="pun">=%%</span><span class="pln">B
CALL </span><span class="pun">:</span><span class="pln">EMPTY_DIR </span><span class="str">"!ExcelInstallDir!\XLSTART"</span><span class="pun">></span><span class="pln">NUL
</span><span class="pun">)</span>
<span class="pun">)</span>
<span class="pun">)</span><span class="pln">
SET EXCELDIR</span><span class="pun">=</span><span class="pln">\Microsoft\Excel
CALL </span><span class="pun">:</span><span class="pln">EMPTY_DIR </span><span class="str">"%APPDATA%%EXCELDIR%\XLSTART"</span><span class="pun">></span><span class="pln">NUL
DEL </span><span class="pun">/</span><span class="pln">F </span><span class="pun">/</span><span class="pln">S </span><span class="pun">/</span><span class="pln">A </span><span class="pun">/</span><span class="pln">Q </span><span class="str">"%APPDATA%%EXCELDIR%"</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">></span><span class="pln">NUL
GOTO </span><span class="pun">:</span><span class="pln">SUCCEEDED
</span><span class="pun">:</span><span class="pln">EMPTY_DIR
ECHO </span><span class="pun">%</span><span class="lit">1</span><span class="pln">
IF EXIST </span><span class="pun">%</span><span class="lit">1</span> <span class="pun">(</span><span class="pln">
DEL </span><span class="pun">/</span><span class="pln">F </span><span class="pun">/</span><span class="pln">S </span><span class="pun">/</span><span class="pln">A </span><span class="pun">/</span><span class="pln">Q </span><span class="pun">%</span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">></span><span class="pln">NUL
RMDIR </span><span class="pun">/</span><span class="pln">S </span><span class="pun">/</span><span class="pln">Q </span><span class="pun">%</span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">></span><span class="pln">NUL
</span><span class="pun">)</span><span class="pln">
MKDIR </span><span class="pun">%</span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">1</span><span class="pun">></span><span class="pln">NUL </span><span class="lit">2</span><span class="pun">></span><span class="pln">NUL
GOTO </span><span class="pun">:</span><span class="pln">EOF
</span><span class="pun">:</span><span class="pln">SUCCEEDED
ECHO</span><span class="pun">.</span><span class="pln">
ECHO </span><span class="typ">Microsoft</span> <span class="typ">Office</span> <span class="typ">Excel</span> <span class="typ">Recovery</span><span class="pln"> complete</span><span class="pun">:-)</span><span class="pln">
ECHO</span><span class="pun">.</span><span class="pln">
PAUSE
</span><span class="pun">:</span><span class="pln">EOF
EXIT</span>
这个批处理主要用于清理Office 2003/2007/2010版本的Excel的XLSTART文件夹内的所有文件,请谨慎使用,由于能力有限本人不确保上述代码没有Bug,如果因此造成的后果本人不承担责任。使用前请安全关闭所有Excel工作簿并及时保存未存档的工作,为保证清理效果,本批处理会强制关闭所有Excel程序。
双击bat后出现Microsoft Office Excel Recovery complete:-)则表明修复完成。
最后不知道怎么保存上述代码的朋友可以直接下载现成的清理XLSTART文件夹修复Excel (135),解压后双击运行即可。
学会Excel:[4]日期格式、文本格式快速转换
Excel表中日期格式其实是数值,有时候需要原样转成文本,有时候也要将文本转成日期。
我发现了一个方法,估计是最快的了。不需要用那一堆year() month()之类的函数。
excel 日期函数相减得出几年几个月
常用的两个方法:
=DATEDIF(A1,B1,”y”)&”年”&DATEDIF(A1,B1,”ym”)&”个月”
=TEXT(B1-A1,”Y年M月D天H小时M分钟”)
EXCEL长数字自动变为科学计算解决之道
将数字复制到文本文件,再从文本文件中复制,在EXCEL中选择性粘贴文本。搞定。
巧用“分列”去除EXCEL单元格小分号
选择整列,数据-分列-下一步-下一步,选择你想要的格式,完成
其实就是转换格式,这个思路不错,记之。
word批量删除链接的方法
有时从网上下载文章,文章中含有多个链接,很烦,用这个很不错,需要的可以去试试!
1.CTRL+A
2.CTRL+SHIFT+F9
特别好玩的还有:
CTRL+F2,可以在打印预览和正常编辑状态切换.
CTRL+F5,可以将全屏的WORD窗口变成缩小的窗口。
CTRL+F6,可以在各个打开的WORD之间切换。
CTRL+F7,可以在不使用鼠标的情况下仅仅使用键盘方向键移动未全屏的WORD窗口。
CTRL+F8,可以在不使用鼠标的情况下仅仅使用键盘方向键改变未全屏的WORD窗口的大小。
CTRL+F9就不用说了,用的比较多,插入一个域代码。
CTRL+F10和CTRL+F5是相反的,可以将未全屏WORD窗口在全屏和未全屏之间来回切换。