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

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



1　设置生日为　11…28…1998　的形式，并根据生日计算出学生的年龄。　/2　对平均分》=90　分，80=60；N7；〃〃）））　（平均分在　J　列）　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　隔行求和问题　　



若有　20　列（只有一行），需没间隔　3　列求和，该公式如何做？前面行跳跃求和的公式不管用。　　

　　解　答　：　假　设　　a1　　至　　t1　　为　数　据　（　共有　　20　　列　），　在　任意　单　元　格　中　输　入　公式　：　

=SUM（IF（MOD（TRANSPOSE（ROW（1：20））；3）=0；（a1：t1））　/按　ctrl＋shift＋enter　结束即可求出　

每隔三行之和。　　



　　　　　　　　　　　　　　　　　　　　　　　　　在　EXCEL　里取数值的一部分用什么函数　　



打比方说有一个数字　123456，我只想取它的后三位，也就是说只需要　456，应该用什么函数。　　

解答：假设　A1　的值为　123456；　用=RIGHT（a1；3）可以取其后三位。/　你如果想取其中的几位，不　

妨试试　mid　函数。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　用什么函数可以插入字符　　



00020304T0239　　0400020304T0239　　/00020404T0211　　0400020404T0211　　/00020604T0199　　

0400020604T0199　　/00020704T0216　　0400020704T0216　　/00021304T0241　　0400021304T0241　　

/00021404T0222　　0400021404T0222　　/00021504T0222　　0400021504T0222　　/00021704T0139　　

0400021704T0139　/就像上表一样，怎样用第一列的数据形成第二列的数据，即在特定位置加　

上几个相同字符串？　　

解答：　b1=〃04〃&　a1　　

问：哪如果倒过来呢？　　

答：用公式　A1=MID（B1；3；13）或　A1=SUBSTITUTE（B1；〃04〃；〃〃；1）　　



　　　　　　　　　　　　　　　　　将文件保存为以某一单元格中的值为文件名的宏怎么写　　　　



解答：假设你要以　Sheet1　的A1　单元格中的值为文件名保存，则应用命令：　　　

ActiveWorkbook。SaveCopyAs　Str（Range（〃Sheet1！A1〃））　＋　〃。xls〃　　　

A1　是文本串的话，使用这条命令：　ActiveWorkbook。SaveAs　Range（〃A1〃）　&　〃。xls〃　。当然，　

如果你的　A1　是路径，F1　是文件名的话，可以这样写：　ActiveWorkbook。SaveAs　Range（〃A1〃）　　

&　Range（〃F1〃）　&　〃。xls〃　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　求余数的函数　　



比如：A1=28，A2=　（A1÷6）的余数=4，请问这个公式怎么写？　解答：=MOD（28；6）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　评委评分　　



解　答　：　用　两　个　函　数　可　解　决　：　rank（　排　高　低　）average（　求　平　均　）　。　也　可　以　用　：　

｛=（SUM（B1：B10）…SUM（LARGE（B1：B10；ROW（A1：A2）））…SUM（SMALL（B1：B　

10；ROW（A1：A2））））/（COUNT（B1：B10）…4）｝（可以解决有多个最高和最低分的问题）　。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　318　　


…………………………………………………………Page　319……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　数据校对　　



已知：A2=SUM（A3：A20）；B2=SUN（B3：B20）；C2=SUM（C3：C20）；D2=SUM（d3：d20）；E2=SUM（E3：E20）、　

又知　A2=B2＋C2=D2＋E2　。需要解决的问题：当　B2＋C2　或者　D2＋E2　不等于　A2　时；系统自动提示错　

误；请各位高手给予指点；是否一定要通过宏才可以解决；有没有更简单的办法？　　

解答：=IF（AND（B2＋C2=SUM（A3：A20）；D2＋E2=SUM（A3：A20））；SUM（A3：A20）；〃　：　（〃）　　



　　　　　　　　　　　　　　　如何在一个单元格中自动输入在另外一个单元格中输入的文字　　



　解答：a2　中输入a1　的文字；则　a2=indirect（〃a1〃）　　



　　　　　　　　　　　在表格中查找我需要的东西并把该行所有的数据反映到另外的表格中去　　



解答：比如找表　A　中B　列数值等于　5　的数。在表B　中的A1　单元输入：=VLOOKUP（5；A！B1：B4；1）　　



　　　　　　　　　　　在　EXCEL　表里建立唯一索引在该列输入重复的数值后自动提示输入错误　　



解答：1、选定你要限制输入的列（假设是A2：A20），选菜单的“数据”－“有效性”；　　　

2　、在许可条件中选定“自定义”，在公式一拦中，输入你要求的限制，　　例如：　

　“=COUNTIF（A2：A20；A2）=1　”。　　

3、你还可以在“输入信息”和“出错信息”输入一些提示信息。　　

不过，你一定要注意！这个功能只能在你从键盘上键入数据时有效！拷贝和粘贴数据的操作是　

无效的。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　发票小写金额填充　　



我输入　123456。52　它自动给拆成￥1　2　3　4　5　6　5　2　的形式并且随我输入的长度改变而改变？　　

　解答：我所知函数不多，我是这样做的，如有更方便的方法，请指点　　　

例如：　　　

在　A1　输入小写金额，则：　　　

千万：B1=IF（A1》=10000000；MID（RIGHTB（A1*100；10）；1；1）；IF（A1》=1000000；〃￥〃；0））　　　

百万：C1=IF（A1》=1000000；MID（RIGHTB（A1*100；9）；1；1）；IF（A1》=100000；〃￥〃；0））　　　

十万：D1=IF（A1》=100000；MID（RIGHTB（A1*100；8）；1；1）；IF（A1》=10000；〃￥〃；0））　　　

万：E1=IF（A1》=10000；MID（RIGHTB（A1*100；7）；1；1）；IF（A1》=1000；〃￥〃；0））　　　

千：F1=IF（A1》=1000；MID（RIGHTB（A1*100；6）；1；1）；IF（A1》=100；〃￥〃；0））　　　

百：G1=IF（A1》=100；MID（RIGHTB（A1*100；5）；1；1）；IF（A1》=10；〃￥〃；0））　　　

十：H1=IF（A1》=10；MID（RIGHTB（A1*100；4）；1；1）；IF（A1》=1；〃￥〃；0））　　　

元：I1=IF（A1》=1；MID（RIGHTB（A1*100；3）；1；1）；IF（A1》=0。1；〃￥〃；0））　　　

角：J1=IF（A1》=0。1；MID（RIGHTB（A1*100；2）；1；1）；IF（A1》=0。01；〃￥〃；0））　　　

分：K1=IF（A1》=0。01；RIGHTB（A1*100；1）；0）　　　

（编者注：公式中最后一个　0　应改为〃〃）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　排列问题　　



已知　1，2，3，4　共　4　个数字符号构成的　4　位数的全排列有　256　个，如：1234，2341，3245　等　

等，现在我需要将这　256　个数字全部列出，如果用手写笔算的话既麻烦又容易出错，不知可否　

用　Excel　来解决这个问题？　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　319　　


…………………………………………………………Page　320……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



解　　　　　　　答　　　　　　　：　　　　　　　　在　　　　　　　单　　　　　　　元　　　　　　　格　　　　　　　输　　　　　　　入　　　　　　　　公　　　　　　　式　

　“=1111＋1000*INT（（ROW（）…1）/64）＋100*MOD（INT（（ROW（）…1）/16）；4）＋10*MOD（INT（（ROW（）…1）/　

4）；4）＋MOD（ROW（）…1；4）”拖放到A256。　　



　　　　　　　　　　　　　　　　　　　在条件语句中如何实现符合某个时期的条件的记录进行统计　　



比如有　1…12　月份的记录单；需要实现对每个月里些数据的统计汇总/（格式如何？）　　

　解答：以下公式；A　列为日期列；B　列为数据；要求计算　1　月份的累计：　　　

=SUM（IF（MONTH（A：A）=1；B：B；0））　　　

此为数组公式；在输完公式后；不要　ENTER；而要　CTRL＋SHIFT＋ENTER。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　行改列（不是转置问题）　　



1。　原内容为行（每行　5　列内容共几千行）：　　　

列　1　列　2　列　3　列　4　列　5　　　

行　1　1　2　3　4　5　/行　2　1　2　3　4　5　/行　3　1　2　3　4　5　/以下类同〃　/〃　　　

2。　改为行排列（每　12　行　5　列内容共　12　组，排为一行）　　　

列　1　列　2　列　3　列　4　列　5　列　6　列　7　列　8　列　9　列　10　〃〃（60列）　　　

原　1…12　行　1　2　3　4　5　1　2　3　4　5　〃〃（60列）　/原　13…24　行　1　2　3　4　5　1　2　3　4　5　〃〃（60　

列）　/原　25…36　行　1　2　3　4　5　1　2　3　4　5　〃〃（60列）　/以下类推　〃　　　

本人不会编写宏，让您见笑曾费劲手工移动录制了一个取　2400　行（每行　5　列内容）改列的宏。　

因原有工作表行不断增加，经常需要行改列作其它用途，录制的宏不能满足需要。烦请大家帮　

助写一自动循环取　12　行　5　列内容排列为一行的宏。　　　

规律：　　　

1。　1…12　行　1…5　列内容排在第　1　行（第一行　1…5　列、第二行　1…5　列、类推，完成为　60　列）　　　

2。　13…24　行　1…5　列内容排在第　2　行（第　13　行　1…5　列、第　14　行　1…5　列、类推）　　　

3。　25…36　行　1…5　列内容排在第　3　行（第　25　行　1…5　列、第　26　行　1…5　列、类推）　　　

4。　以下类推　〃　　

解答：Sub　转换　（）　　　

Dim　numcol　As　Integer　　　

Dim　numrow　As　Long　　　

Dim　i　As　Long　　　

Dim　x　As　Integer　　　

Dim　numperrow　As　Integer　　　

numperrow　=　InputBox（〃请输入每行要填的数据行的数目：〃）　　　

Range（〃数据〃）。Select　　　

numrow　=　Selection。Rows。Count　'数据区的行数　　　

numcol　=　Selection。Columns。Count　'数据区的列数　　　

x　=　numperrow　*　numcol　　　

Range（〃a1〃）。Select　　　

For　i　=　1　To　numrow　'以数据的每一行为单位进行剪切　　　

Range（〃数据〃）。Rows（i）。Cut　　　

ActiveSheet。Paste　　　

Selection。Offset（；　numcol）。Select　　　

If　（i　Mod　numperrow）　Then　'判断是否要换行　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　320　　


…………………………………………………………Page　321……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



Else：　Selection。Offset（1；　…x）。Select　　　

End　If　　　

Next　i　　　

End　Sub　　　

'本程序需要把要变换的数据命名为〃数据〃（数据区的列数可以任意；不一定是　5）方法：选中该　

区域；在名称框内输入〃数据〃即可；然后按程序要求输入每行要填的数据行的数目　（如本例中的　

12）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何给自动筛选中的自定义指定快捷键　　　　



解答：Sub　自动筛选（）　　　

On　Error　GoTo　xx　　　

Selection。AutoFilter　=　True　　　

xx：　　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　汇总问题　　



本人有以下一个问题需要解决，请诸位大虾指点迷津。　　　

　　　　如下表：　　　

　　　　　　A　　　　　　B　　　　　　　　　　C　　　

　　1　　50　　　　采购　　　　　　　　　（采购汇总）　/　　2　　60　　　　工程　　　　　　　　　　　　　　　（工程汇总）　/　　3　　80　　　　工资　　　　

　（工资汇总）　/　　4　　100　　　　税　　　　　　　（税汇总）　/　　5　　70　　　　采购　　　　　/　　6　　50　　　　工资　　　　　　

/　　7　　60　　　　工程　/。　。　。　/请问：C1、C2、C3、C4　中的公式如何设置？　　

　解答：方法　1：如下表：　　　

　　　　　　A　　　　　　B　　　　　　　　　　C　　　

　　1　　50　　　　采购　　　　=countif（a1：a7；〃采购〃）　/　　2　　60　　　　工程　　　　=countif（a1：a7；〃工　

程　〃）　　/　　　　3　　　　80　　　　　　　工　资　　　　　　　=countif（a1：a7；〃　工　资　〃）　　/　　　　4　　　　100　　　　　　　税　　　　　

=countif（a1：a7；〃税〃）　/　　5　　70　　　　采购　　　　　/　　6　　50　　　　工资　　　　　/　　7　　60　　　　工程　　

/　　。　。　。　　　

方法　2：用如下方法才对：　　　　　

　　　　A　　　　　　B　　　　　　　　　　C　　　

　　1　　50　　　　采购　　　　=SUMIF（B1：B7；B1；A1：A7）　　　

　　　　2　　　　　60　　　　　　　　工　程　　　　　　　　　　　=SUMIF（B1：B7；B2；A1：A7）　　/　　　　　　　3　　　　　80　　　　　　　　　工　资　　　　

=SUMIF（B1：B7；B3；A1：A7）　/　　4　　100　　　　税　　　　　=SUMIF（B1：B7；B4；A1：A7）　/　　5　　70　　　　采购　　　　　　

/　　6　　50　　　　工资　　　　　/　　7　　60　　　　工程　　　

　　方法　3：由于Ｂ栏属于数据表的一部分，可能会随时改变，故此最好把采购等项目名称抄到　

另外一个地方（Ｃ栏）。　　　

　　　　A　　　　　　B　　　　　　　　　　C　　　　　　　　D　　　

　　1　　50　　　　采购　　　　采购　　　　=SUMIF（B1：B7；C1；A1：A7）　/　　2　　60　　　　工程　　　　工程　　　　

=SUMIF（B1：B7；C2；A1：A7）　/　　3　　80　　　　工资　　　　工资　　　　=SUMIF（B1：B7；C3；A1：A7）　　

/　　4　　100　　　　税　　　　　税　　　　　　=SUMIF（B1：B7；C4；A1：A7）　/　　5　　70　　　　采购　　　　　/　　6　　

50　　　　工资　　　　　/　　7　　60　　　　工程　/还有，记着要适当地使用绝对参照地址　　（Absolute　　

referencing）。　　

方法　4：　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　321　　


…………………………………………………………Page　322……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



A　　　　　　B　　　　　　　　　　C　　　　　　　　D　　　

　　　1　　　50　　　　采购　　　　　采购　　　　　=SUMIF（B：B；C1；A：A）　　/　　　2　　60　　　　　工程　　　　　工程　　　　

=SUMIF（B：B；C2；A：A）　/　　3　　80　　　　工资　　　　工资　　　　=SUMIF（B：B；C3；A：A）　/　　4　　100　　　　税　　　　　　

税　　　　　　=SUMIF（B：B；C4；A1：A）　/　　5　　70　　　　采购　　　　　/　　6　　50　　　　工资　　　　　/　　7　　60　　　　

工　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　统计包含某个字符的个数　　



我想编的公式是：　a/＇84　　（b×4）＇　。其中a　是一个数值，小于或等于　84；b　是包含字符　

C　的单元格的个数；C　是一个符号。这个公式的关键是要统计出包含字符　C　的单元格的个数。　　

　解答：方法　1：=a/（84…countif（b；〃=c〃）*4）　　

问题：我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c”　

在　D2—D30　中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式如何　

做？　　

解答：　=a/（84…COUNTIF（D3：D30；〃c〃）*4）　　　



　　　　　　　　如何用　if　函数判断；如果　a1　单元格大于　0；b1　单元格为　0　是错误；为非　0　是正确　　



　解答：1：if（and（a0；if（b1=0；〃错误〃；〃正确〃）；〃条件一不满足〃）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　我改进以下一个验证先进先出法公式　　



=IF（B3