Excel word ppt office使用技巧大全(DOC格式)-第64部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！



…………………………………………………………Page　378……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　只要你工作表中输入了公式（以=开头），含有公式的单元格将自动变为浅绿色，提醒你和　

　　　　别人此处有公式，小心编辑，十分醒目。编辑完公式后再次运行该命令就可以此保护工作　

　　　　表并锁定公式，禁止改动。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何让　EXCEL　自动从头统计到当前单元格　　



情况如下：　　C　列要根据　A　列的内容来统计　B　列的数据，范围从　A1　：An　，即当A　列中　An　有　

数据时，Cn　　自动根据An　的值，统计B1　：Bn　的数据。　　



解答：=SUM（INDIRECT（〃B1：B〃　　&　　LARGE（（A1：A65535〃〃）*（ROW（A1：A65535））；1）））　，按　Ct　

rl＋Shift＋Enter　。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　请问想要取当前单元格的列号，用什么函数　　



1、=CHAR（64＋COLUMN（））　　



2　、Private　　Sub　　Worksheet_SelectionChange（ByVal　　Target　　As　　Excel。Range）　　



If　　Selection。Columns。Column　　》　　26　　Then　　



tt　　=　　Mid（ActiveCell。Address；　　2；　　2）　　



Else　　



tt　　=　　Mid（ActiveCell。Address；　　2；　　1）　　



End　　If　　



MsgBox　　（tt）　　



End　　Sub　　



3、1　的公式就变化一下：=IF（COLUMN（）》26；CHAR（64＋INT（COLUMN（）/26））　　&　　CHAR（64＋　

MOD（COLUMN（）；26））；CHAR（64＋COLUMN（）））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　SUMIF　函数　　



a1　至　A4　是　10；24；30；12。B1　至　B4　是　8；15；25；35。D1　至　D4　是　25；4；3；5。用　SUMIF　　第一参数选取　A　

1：B4；第二参数是》20；第三参数选取　D1：D4。它得出是　7　。它判断的是A1：A4　　　。我的要求是　B1：　

B4　　也跟着判断　　；　　也就要得出来得是　12　　



解答：｛=SUM（（A1：A4》20）*（B1：B4》20）*C1：C4）｝　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　378　　


…………………………………………………………Page　379……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　怎么能快速的将两个单元格的内容互换　　



1、　　

Sub　ChangVal（）　　

my1value　=　ActiveCell。Value　　

For　Each　a　In　Selection　　

If　a。Address　　ActiveCell。Address　Then　　

my2value　=　a。Value　　

a。Value　=　my1value　　

ActiveCell。Value　=　my2value　　

End　If　　

Next　a　　

End　Sub　　

2　、用鼠标先选定单元格，点住单元格边框，并按住　SHIFT　键，然后托拽到隔壁单元格的后面　

一条边框处。你能看到被托拽部分会变成灰色的“工”字形，然后放手后，单元格就互换了。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　如何能到两个时间段的　17：00…8：00　小时数？　　　



1。已知〃E4〃为　08011500（为文本格式﹐开始时间﹐意思为　8　　　　　　　　　　　　　　　　　　　　　　　　　月　1　　　日　　下午　15　﹕00）　　﹐〃G4〃为　

08100900（为文本格式﹐结束时间﹐意思为　8　月　10　　日　　上午　9　点）　　　问﹕如何能得到开始时间到　

结束时间（17：00…08：00　）的总小时数？　　　　

2。　　如何得到开始时间到结束时间的节假日时数？（如　5。1　　﹐10。1　﹐星期六﹐星期天）　　

　　　　　解答：方法　1：　

=（DATE（YEAR（NOW（））；MIDB（AF4；1；2）；MIDB（AF4；3；2））…DATE（YEAR（NOW（））；MIDB（E4；1；2）；MI　

DB（E4；3；2））…1）*15＋IF（MIDB（E4；5；2）＋MIDB（E4；7；2）/60=17；24（MIDB（E4；5；2）＋MIDB（E4；7；2）/60）；7））　

＋IF（MIDB（AF4；5；2）＋MIDB（AF4；7；2）/60=17；MIDB（AF4；5；2）＋MIDB（AF4；7；2）/60…9；8））　　　

方法　2　：networkdays　需要你提供一个节假日列表作为参数。我猜你不会喜欢手工输入这样一　

个表。以下公式功能更为强劲，它可以算出两个单元格（A2　和　C2）所储存的日期之间的周末　

天数，乘以小时数即可计算出你所说的节假日时数。但是，它无法计算国庆节等公众假期。　　　

=IF（C2》A2；SUM（IF（WEEKDAY（C2…ROW（INDIRECT（〃1：〃&　　

C2…A2））；2）》5；1；0））；SUM（IF（WEEKDAY（A2…ROW（INDIRECT（〃1：〃&　A2…C2））；2）》5；1；0）））。这是　

一个数组公式，输入完成后按　CTRL＋SHIFT＋ENTER　结束。　　　

方法　3　：用格式定义　E4；G4　为日期时间格式：****…**…**　　**：**。然后：（G4…E4）*24　　即可得两　

时间内的小时数。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何在单元格返回工作表名称　　



答：=RIGHT（CELL（〃filename〃）；LEN（CELL（〃filename〃））…FIND（〃＇〃；CELL（〃filename〃）））　　



　　　　　　　　　　　　　如何在输入数字的加减乘除按　ENTE　后能在另一单元格自动出现计算数值　　



1、编了个宏：　　　　

Sub　aa（）　　　　

Cells（1；　1）　=　Mid（ActiveCell。Formula；　2；　13）　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　379　　


…………………………………………………………Page　380……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



End　Sub　　　　

先在单元格里输入公式得出结果；选定得出结果的单元格运行宏就可以在A1　出文本。　　

　2　、宏（测试通过）　　

Sub　aa（）　　　　

A　=　2　'行'　　　　

B　=　4　'列　　　　

LINE1：　　　　

If　Cells（A；　B）　=　〃〃　Then　　　　

Exit　Sub　　　　

Else　　　　

Cells（A；　B　＋　1）。Formula　=　〃=〃　&　Cells（A；　B）　　　　

A　=　A　＋　1　　　　

GoTo　LINE1：　　　　

End　If　　　　

End　Sub　　



　　　　　　　　　　　　　　　　　　有　A1，B1，C1，D1　四个单元格D1　的值要随着Ａ１的变化而变化　　　



Ａ１有三种变化，一、二、三；当Ａ１＝‘一’时，Ｄ１＝“　　”；　　Ａ１＝‘二’时，Ｄ１　

＝（Ｂ１＋Ｃ１）／３　　；　　Ａ１＝‘二’时，Ｄ１＝（Ｂ１＋Ｃ１）／６　　　。以上可以通过Ｉ　

　Ｆ来完成　　　。可我希望的是：当Ｂ１或Ｃ１为空时，不论Ａ１为什么Ｄ１都为空，这样做得到　

么？　　

解　答　：　1　　　　　　　、　=IF（OR（B1=〃〃；C1=〃〃）；〃〃；IF（A1=〃　　　　　　一　〃；〃〃；IF（A1=〃　　　　二　〃；（B1＋C1）/3；IF（A1=〃　　三　

〃；（B1＋C1）/6；〃〃））））　　

2　、=IF（OR（B1=〃〃；　C1=〃〃；　A1=〃一〃）；　〃〃；　IF（A1　=　〃二〃；　（B1＋C1）/3；　（B1＋C1）/6））　　

18、　　　　　　如果考虑　　A1　　没有数据的话：=IF（OR（B1=〃〃；　C1=〃〃；　A1=〃一〃；A1=〃〃）；　〃〃；　IF（A1　=　〃二〃；　　

　　　　（B1＋C1）/3；　（B1＋C1）/6））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　对　A　列不重复的数值计数　　



我只能做到新建一列，B　列，然后第一个单元格　countif　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（A1：A100；A1），然后拖动到全部　

新列。最后在新列下面用　sumif（B1：B100；1）　　谁有更好地方法。　　



　　　　　解答：1、｛=SUM（IF（A1：A100=〃〃；〃〃；1/（COUNTIF（A1：A100；A1：A100））））｝（又　

问：公式中的“1/（COUNTIF（A1：A100；A1：A100））”像是一个倒数，怎么理解？答：　

用倒数是这个意思：如果只出现一次，数组中的相应项统计为　1，其倒数为　1，Sum　统计计　1。　

如果出现　N　次，其倒数为　1/N，出现了N　次，求和就是　Nx1/N，最后　Sum　统计就只计　1。）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　永恒的求和　　



　　　　　1、=SUM（OFFSET（A1；；；ROW（）…ROW（A1）））可以对　A　列数值自动求和。　　



　　　　　2、=SUM（INDIRECT（〃R2C：R＇…1＇C〃；FALSE））　　　



　　　　　3、=SUM（INDIRECT（〃A2：A〃&ROW（）…1））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　380　　


…………………………………………………………Page　381……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



19、　　　　　坚持不用　R1C1　栏名列号表示法还有一个方法，不过又复杂了些。首先定义一个公式：　

　　　COL=IF（COLUMN（）》26；CHAR（INT（（COLUMN（）…1）/26）＋64）&CHAR（IF（MOD（COLUMN（）；　

　　　26）=0；26；MOD（COLUMN（）；26））＋64）；CHAR（COLUMN（）＋64））　。后于欲求加总之储存格输入：　

　　　=SUM（INDIRECT（COL&〃1：〃&COL&ROW（）…1））。则此公式复制到任何一任皆可用，又不怕　

　　　产生错误值。（注：COL=IF（COLUMN（）》26；CHAR（MOD（COLUMN（）；26）＋64）&CHAR（INT（C　

　　　OLUMN（）/26）＋64）；CHAR（COLUMN（）＋64））　。暴露了一个为人不知的缺点，如果列数到了A　

　　　A　列以后就不行了，虽然可用　ADDRESS（）解决，比用　CHAR（）好多了，但公式还是太长，　

　　　用在一个加法中实在不值）　　

20　、　　　　其实用　ADDRESS　更好，=SUM（INDIRECT（ADDRESS（1；COLUMN（））&〃：〃&ADDRES　

　　　S（ROW（）…1；COLUMN（）））），还是一句老话，为做一个加法不是太值，这只能是技术上的讨　

　　　论。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何使用　EXCEL　进行动态跨表取数　　



有两个文件，第一个文件有　31　张日报表，每天一个表单；第二个文件仅一个表单；如何在第　

二个文件中，输入　1　时由函数动态取出第一张表单数（如　SHEET1），输入2　时取（SHEET2）　

数，依次类推〃〃。，如何设公式。在同一文件中可以用　INDIRECT　和　ADDRESS　组合，可是　

跨表好象不行，请各位指教一二！　　

解答：前提是两个工作表都要打开：=INDIRECT（〃＇Book2＇Sheet〃&A1&〃！B1〃）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何使用對照表　　



设一对照表如下：如何使用函数当输入。当a1=34　　则　a2　显示〃乙〃　　b1=68　　则　b2　显示〃丁〃/　甲　　2　

2　　33　　44　　66。。。。/　乙　　26　　34　　43　　62。。。。　　/丙　　28　　39　　41　　67。。。。　　/丁　　27　　31　　49　　68。。。。　　/戊　　23　　32　　46　　64。。。。　

　　/　己　　29　　38　　47　　61。。。。　　/更　　21　　37　　48　　69。。。。　　/辛　　24　　36　　42　　63。。。。　　　　

解答：如　A　列输入的数字在表中都存在，把你的数据放在　D　到　G　列，甲乙丙丁戊己辛放在最　

后一列，即　H　列。在　B　列输入公式“=IF（A1=〃〃；〃〃；VLOOKUP（A1；INDIRECT（ADDRESS（1；IN　

T（0。1*A1）＋IF（A1》60；1；2）））：INDIRECT（ADDRESS（8；8））；7　…INT（0。1*A1）＋IF（A1》60；1；0）；FALS　

E））　”。如甲乙丙丁戊己辛放在第一列，即列用公式“=IF（A1=〃〃；〃〃；INDEX（D：D；MATCH（A1；IN　

DIRECT（ADDRESS（1；INT（0。1*A1）＋IF（A1》60；2；3）））：INDIRECT（ADDRESS（8；INT（0。1*A1）＋IF　

（A1》60；2；3）））；0）））　”　　

解答　2　：如果数据无规律，可用以下方法：如你的数据表在D　到　H　列，在　I　列输入公式“=IF　

（ISERROR（MATCH（A1；E1：H1；0））=TRUE；0；row（））〃并向下填充，在　B1　单元格输入公式“=IF（A　

1=〃〃；〃〃；INDEX（D：D；LARGE（I：I；1）））”　　

解答　3　：我将解答2　公式做了小修改（I　列公式删除）　，B1　单元格输入公式：=IF（A1=〃〃；〃〃；INDE　

X（D：D；　　IF（ISERROR（MATCH（A1；E1：H1；0））=TRUE；0；ROW（E1：H1））＋　　IF（ISERROR　

（MATCH（A1；E2：H2；0））=TRUE；0；ROW（E2：H2））＋　　IF（ISERROR（MATCH（A1；E3：H　

3；0））=TRUE；0；ROW（E3：H3））＋　　IF（ISERROR（MATCH（A1；E4：H4；0））=TRUE；0；ROW　

（E4：H4））＋　　IF（ISERROR（MATCH（A1；E5：H5；0））=TRUE；0；ROW（E5：H5））＋　　IF（ISER　

ROR（MATCH（A1；E6：H6；0））=TRUE；0；ROW（E6：H6））＋　　IF（ISERROR（MATCH（A1；E7：　

H7；0））=TRUE；0；ROW（E7：H7））＋　　IF（ISERROR（MATCH（A1；E8：H8；0））=TRUE；0；ROW　

（E8：H8））））　。　　

解答　4　：在B1　单元格输入公式：｛=IF（COUNTIF（E1：G8；A1）=0；〃查无资料〃；INDEX（D1：D　

8；SUM（IF（E1：H8=A1；ROW（E1：H8）））））｝　　

又问：当数据区域有重复数据时，就得不到正确结果。因为，在这里你的　SUM（）返回的只是　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　381　　


…………………………………………………………Page　382……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



对一个数据求和。如果有重复数据，怎样才能得到正确结果呢？　　

答：在　B1　单元格输入公式：=IF（（COUNTIF（E1：H8；A1）=0）　　

＋（COUNTIF（E1：H8；A1）　　

《　ROW（　））；〃〃　；　　

INDEX（D1：D8；SMALL（IF（E1：H8=A1；ROW（E1：H8））；ROW（））））　　

再往下拖曳，就可依序顯示了。真是快瘋了改了十幾次，有些莫名其妙，進來編輯看公式是完　

整的可是發表後又老是缺枺�缺西的，只好多加些空白或強迫分段處理，請使用者自行修改。　　

或：如有重复数据；則顯示〃数据重复〃表示；代表要修改数据。｛=IF（COUNTIF（E1：H8；A1）=0；　

〃查無資料〃；IF（COUNTIF（E1：H8；A1）》1；〃資料重眩�〃；INDEX（D1：D8；SUM（IF（E1：H　

8=A1；ROW（E1：H8））））））｝　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何在单元格返回工作表名称　　



解答：=RIGHT（CELL（〃filename〃）；LEN（CELL（〃filename〃））…FIND（〃＇〃；CELL（〃filename〃）））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　何在一列数据中统计限定范围的数据　　



请教各位：现　A　列有数字（包括绝对值为　0　的数字）　、文本、空格；要统计数值为　14　到　35（包括　1　

4　与　35）的个数；还要统计数值》35　并13　and　35　　or