microsoft excel使用技巧和问题解决

谁借莪１个温暖的怀抱￠ 2022-08-01 00:24 199阅读 0赞

http://[blog.csdn.net/pipisorry/article/details/46506195][blog.csdn.net_pipisorry_article_details_46506195]

Excel使用技巧

## 编码 ##

Office for Mac 打开 Excel 文件全是乱码

解决方案\[[mac：文件编码问题][mac]\]

## 引用 ##

excel绝对引用、混合引用和相对引用

=A1这种表达是行和列均是“相对引用”，也就是均没有锁定。比如说将B1单元格下拉到B2就会变成=A2，行数会变化。如果向右拉到C1，则会变成=B1，列会变动。

如果将公式改成=$A1，也就是在列标前面加上一个$符号，这就表示列绝对引用，行相对引用。这时下拉单元格时行号会变动变成=$A2，但是横着拉列号不会变动，仍然是=$A1。

类似，如果把公式改成=A$1，则表明行是绝对引用，怎么拉都不会变，而列是相对引用，随着公式在行方向上的变化而变化。

如果公式变成=$A$1则行列均锁定，就是所谓的“行列均绝对引用”，怎么拉公式都不会变化，都是引用A1单元格。

双击进入单元格编辑模式，用鼠标选中单元格引用区域，按键盘上的F4就可以实现在相对引用和绝对引用之间的快速切换，这在编辑公式时非常有用。

## 复制和粘贴 ##

批量复制相同内容到不同行

A列添加新列，按需要填入 ， ， ，1（代表每4行插入一新内容行），选择此4个单元格，下拉

格式刷刷需要复制的内容，刷需要复制到的一行，撤销

选择所有需要插入复制内容的行：查找和选择-定位条件-常量，Enter

行列转置

1>复制选中数据，到其他单元格，右键“选择性粘贴”，选中“转置”

2>区域的转置。函数 TRANSPOSE 必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与 array 的列数和行数相同。

某些函数，如函数 LINEST 返回水平数组。函数 LINEST 返回斜率和 Y 轴截距的水平数组。

下面的公式返回函数 LINEST 的斜率和 Y 轴截距的垂直数组：TRANSPOSE(LINEST(Yvalues,Xvalues))

使用TRANSPOSE函数将A1:B8区域的内容变为D1：K2区域的内容。

![Center][]

操作：因A1:B8区域为8行2列，因此应该选择一个2行8列的区域作为存放数据的区域，因此先选择D1：K2区域，然后输入公式：=TRANSPOSE(A1:B8)，最后按Shift+Ctrl+Enter键进行确认输入，结果如上表右边。

Excel粘贴如何不覆盖原有内容

选中要复制或移动的单元格，“复制”或“剪切”，选中要粘贴的起始单元格，按“Ctrl”+“Shift”+“+”，在弹出的“插入粘贴”对话框中选择活动单元格移动方向，单击“确定”。

## 查找、筛选和替换 ##

excel单元格中多余的文字怎么设置不显示

选中单元格 > 开始 > 自动换行

![Center 1][] >>![Center 2][]

\[[50个逆天功能，看完变Excel绝顶高手！][50_Excel]\]

\[[Excel VBA提取目录][Excel VBA]\]

**取单元格中\_分割的最后一个字符串**

一种方法是先将所有分割符替换成n长的空格，取右边n长数据，再去除空格，最后取其值

VALUE(TRIM(RIGHT(SUBSTITUTE(A1,"\_",REPT(" ",50)),50)))

comb\_pred\_life\_\*\*\*\_catl1\_Y\_120\*\*\*1就变成了120\*\*\*1

VLOOKUP函数  
　　在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处数值  
　　格式：VLOOKUP(lookup\_value,table\_array,col\_index\_num,range\_lookup)  
Lookup\_value要查找的数值；

Table\_array要在其中查找数据的单元格区域；

Col\_index\_num为在table\_array区域中待返回的匹配值的列序号（如当Col\_index\_num为2时,则返回table\_array第2列中的数值…）；Range\_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup\_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值\#N/A。  
　　应用举例：参见图7，我们在D65单元格中输入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，确认后，只要在B65单元格中输入一个学生的姓名（如丁48），D65单元格中即刻显示出该学生的语言成绩。  
　　特别提醒：Lookup\_value参见必须在Table\_array区域的首列中；如果忽略Range\_lookup参数，则Table\_array的首列必须进行排序；在此函数的向导中，有关Range\_lookup参数的用法是错误的。

在sheet2中查找对应名字的对应科目成绩

在sheet1中筛选对应科目（大学体育I），ctrl+a复制到sheet2中，对应名字的对应科目成绩单元格中输入

=IF(ISERROR(VLOOKUP($C10,Sheet2!$D:$E,2,FALSE)),0,VLOOKUP($C10,Sheet2!$D:$E,2,FALSE))

（在sheet2 D列查找C10，并将查找到的对应行中相对第2列中的值写入单元格）

横拉再下拉布满单元格，选大学体育I，复制-粘贴值

以此类推，在sheet1中筛选对应科目（中国近现代史）。。。。

筛选两次六级数据，取最高分

两次成绩放在一起，先对分数排序，再选择姓名项，数据-删除重复项

筛选大二上、大二下都有成绩的学生姓名、学号、加权等

将大二上和大二下成绩复制到同一sheet内，并分别按姓名（大二上列B，大二下列E）排序

在临时列G1单元格填入=COUNTIF($B:$B,E1),下拉到E1最后一行，1表示Ei在B列中存在

在临时列H1单元格填入=COUNTIF($E:$E,B1),下拉到B1最后一行，1表示Bi在E列中存在

筛选出G列中的0，即只在E中存在的，复制(剪切会出错)与E筛选列有关的内容到新sheet中，并删除与E筛选列有关的内容

取消筛选，再筛选出H列中的0，即只在B中存在的，复制与B筛选列有关的内容到新sheet中，并删除与B筛选列有关的内容

剩余的分别重新按B，E列排序，新的sheet中的名单即只有一个学期的成绩的学生

筛选出B列有，但是A列没有的数据

第三列单元格输入=COUNTIF($A:$A,B1)，下拉，如果显示0，就说明在B列有，A列没有

即B1是否在A列中存在

筛选出六级2013.6才通过而2012.12未通过的

先将两次六级成绩如图复制到同一sheet上，增加一列D，在D1单元格输入=COUNTIF($F:$F,A1), 下拉，则0表示在A列有而在F列没有的（0代表Ai在F列不存在）

![Center 3][]

筛选出D 列的0，筛选结果复制（ABC列）到临时的sheet中，取消筛选，将临时sheet中的筛选结果复制回原sheet中，即可

将EXCEL中的所有\#N/A替换成0

1.全选工作表-复制-选择性黏贴-值-确定；然后查找\#N/A，替换成0；

2.公式变成=if(iserror(原公式),0,原公式)

## 删除 ##

批量删除某一列单元格内的数字中的最后一个数字

1>右边新建一列，输入=mid(左列，1，9），下拉

（B1中输入 =LEFT(A1,LEN(A1)-1)下拉）

复制右列，再在左列粘贴为只有值，再删除右列

2>右边新建一列，选中列，分列-其他-~-完成

3>选择区域，输入前面的数，按CTRL+回车，区域内的数全变为输入的数

批量删除某一列单元格内的数字中的某一个数字

=MID(D1,1,9)&MID(D1,11,14)

批量删除前后空格

相邻单元格 C1中输入公式=TRIM (A1) ，然后按**回车键**。

[皮皮blog][blog]

## 汇总和统计 ##

统计各班各科目平均分

**![Center 4][]**

班级列排序-分类汇总（分类字段：班级，汇总方式：平均值，选择学年度之后所有），选取all,数值粘贴到原表中，选择学年度列（\#div/0!）,选取all粘贴到新表中，选取学号单元格，替换（平均值-null）,删除多余的单元格

将标题名添加到所有不同姓名行中

选择姓名行，菜单栏-数据-分类汇总-确定

复制行（学号姓名等）

选择含有空值的一列，查找-空值-粘贴

统计某个特定值对应的数量

如要统计如下ETC币种的买入总数量

![20180523173530788][]

使用=SUMIF(G$2:G$36,O2,D$2:D$36)就是说如果G$2:G$36中单元格内容是O2（即etc），则对D$2:D$36相应的行进行加和。

COUNTIF用于统计满足某个条件的单元格的数量

如统计b1中的数据（任意类型）在C1:E1中出现的次数，或者说看b1:e1中数据是不是都是重复的，即判断多个单元格是否都一样。

=COUNTIF(C1:E1,B1)

## 合并 ##

excel合并单元格如何避免只保留左上角数据

1.C1=A1&A2&A3

然后等合并A1A2A3之后再把C1的数据拷到A1去

2.合并单元格是用户在制作表格时常用的命令，它可以把多个单元格显示成一个单元格，起到美化的作用。通常情况下，如果把几个含有数据的单元格进行合并，Excel会提示“在合并单元格时，如果选择的单元格中不止一个含有数据，Excel将保留左上角单元格中的数据，并删除其余的所有数据。

这在很多时候会让用户觉得为难，合并会丢失数据，影响数据的计算，而不合并则无法兼顾到美观性。下面的方法可以突破Excel的这种局限，在合并单元格的同时保留所有数值。假设有如图72‑2所示的表格，现在需要分别把A1:A4和A5:A8进行单元格合并。

![Center 5][]

图72‑2需要合并单元格的表格

选择C1:C4，单击工具栏中的“合并与居中”按钮。同样的方法将C5:C8也合并单元格，如图72‑3所示。

![Center 6][]

图72‑3在空白的单元格区域按照目标区域的大小合并单元格

选择C1:C8，单击工具栏中的“格式刷”按钮，然后单击A1进行格式复制。结果如图72‑4所示。在这个过程里面，Excel并不会出现任何警告。

![Center 7][]

图72‑4把空白区域的格式复制到目标区域

为了验证一下被合并的单元格是否还保留了原来的数据，可以在D列中使用公式进行引用计算，在D1中输入公式：       =A1

然后拖曳到D8进行公式复制。结果如图72‑5所示。

![Center 8][]

图72‑5合并单元格后所有数值都得以保留

如果直接选择合并A1:A4,那么在其他地方引用时只有A1还保留原来的值，A2,A3,A4的值都没有了。但是如果是拷贝格式的方法合并，之后再引用A1，A2，A3，A4的时候，他们的值都还是保留着的

初步猜测Excel合并单元格操作实际上是执行了几个步骤，猜测如下：

1.将合并区域左上角单元格的数据暂存

2.清空合并区域所有单元格中的数据

3.将选中区域进行合并

4.在合并区域中填上之前暂存的左上角单元格的数据

Excel的特性？

为什么通过格式刷刷出来的“合并单元格”，被合并单元格的所有数据都会被保留呢？大家都知道格式刷本身是用来“刷”格式的，只能用来“复制”格式，而无法执行数据的删除操作。而使用格式刷刷合并单元格的格式时，只“复制了格式”，而其中的删除数据的工作没有被执行。因为删除数据本身并不属于“格式”范畴，所以导致上面提到的问题。

如果是Excel本身的特性，则我们可以利用这一特性进行一些数据的隐藏，想要查看真实的数据也很简单，使用单元格引用或是取消区域的合并即可。

假想：既然通过格式刷来实现单元格合并时可以实现数据的保留，则说明Excel本身是能够实现合并单元格并保留数据的，希望后续Excel可以提供是否保留所有数据的选项。出处http://windyli.blog.51cto.com/1300305/302006

合并当前目录下多个excel文件到一个文件中

. 当前目录下新建一个excel文件（lz后来采用的是python的pandas库代码实现更方便简洁）

. 右键shttle - 查看代码 - 输入：

Sub 合并当前目录下所有工作簿的全部工作表()

Dim MyPath, MyName, AWbName

Dim Wb As workbook, WbN As String

Dim G As Long

Dim Num As Long

Dim BOX As String

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & "\\" & "\*.xls")

AWbName = ActiveWorkbook.Name

Num = 0

Do While MyName <> ""

If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & "\\" & MyName)

Num = Num + 1

With Workbooks(1).ActiveSheet

.Cells(.Range("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)

For G = 1 To Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)

WbN = WbN & Chr(13) & Wb.Name

Wb.Close False

End With

End If

MyName = Dir

Loop

Range("B1").Select

Application.ScreenUpdating = True

MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"

End Sub

. 点击菜单栏上的运行 - 运行子程序就OK了

**Note**:合并后多余的特定行可以用筛选选出来再复制粘贴

[皮皮blog][blog]

## 其它技巧 ##

[win7下EXCEL多窗口打开的实现][win7_EXCEL]

打开注册表（文件/导出，备份出注册表信息），regedit  
2. 在左侧中展开HKEY\_CLASSES\_ROOT/Excel.Sheet.8/shell/Open/commend，双击右侧中的“默认”，在最后添加"%1"，最终为 "C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE" /e"%1"  
右键点击右侧的“Command”，选择重命名，改一个其它名字  
3. 找到HKEY\_CLASSES\_ROOT/Excel.Sheet.8/shell/Open/ddeexec，把 ddeexec文件夹改个其它名字  
4. 找到HKEY\_CLASSES\_ROOT/Excel.Sheet.12/shell/Open/commend，重复2、3步骤。  
其中Excel.Sheet.8下面改的是针对.xls后缀的文件，Excel.Sheet.12是针对.xlsx后缀的文件

IF you are Excel 2003, tested and work!  
Please follow below step to fix that problem  
  
1. Go to My Computer  
2. Tools > Folder Options > File Types  
3. Choose XLS file type then Press Advanced  
4. Uncheck "browse in same window" in advanced window.  
5. Then highlight Open, press Edit  
  
in the Action box:  
&Open  
  
in the application used to perform action:  
"C:\\Program Files\\[Microsoft Office][]\\OFFICE11\\EXCEL.EXE" "%1"  
  
Check the box next to use DDE  
Remove anything that is in DDE Message box and DDE Application Not Running box.  
application box says:  
EXCEL  
  
And in the Topic box it says:  
System  
  
Press ok and you can open multi Excel file in totally separtate windows now.

填充柄的使用

输入1-20时，有两种输入方法：

①  输入1，再把指针放在填充柄上，按住Ctrl键拖动填充柄，即可完成填充1-20功能。

②  先输入1和2，选中1和2单元格，指针拖动填充柄，即可完成1-20的填充功能。

可以在智能标签中选择想要的操作－序列填充或者复制

插入或删除单元格功能：按住Shift键的同时，把指针放在填充柄上，指针形状变为“等号上下都有箭头”时，如果向上拖动填充柄，则删除单元格；如果向下拖动填充柄，则插入单元格（拖动一个单元格时删除，拖动两个以上则是添加一个或多个单元格了）

自动列出排序序号

![Center 9][]

k2输入=RANK($J2,J:J)，下拉就可以自动排出排序序号

计算加权平均值，加权不变，并四舍五入保留2位小数

**![Center 10][]**=ROUND(SUMPRODUCT(E6:AK6,E$3:AK$3)/102,2)

输入制表符

word 表格内输入不了制表符，按tab输入不了，直接跳到下个单元格去了

用了Office tab会覆盖此快捷键，需要 关闭/修改 Office tab的快捷键

使用快捷键Ctrl+Tab   字数补丁

其实在不知道快捷方式的情况下，活用替换也可以实现，设想在每个单元格正文输入时，先输入表格中不会有的字符，！或@或00随便，即想输入“正文A”，直接输入“00正文A”，全选表格，如图使用替换，重点是替换为中选择高级下的特殊字符，除了制表位外，还有其他个性需要。

from:[http://blog.csdn.net/pipisorry/article/details/46506195][blog.csdn.net_pipisorry_article_details_46506195]

ref:

[blog.csdn.net_pipisorry_article_details_46506195]: http://blog.csdn.net/pipisorry/article/details/46506195
[mac]: https://youzipi.blog.csdn.net/article/details/89494657
[Center]: /images/20220801/3e0a5c7c34414127a9cc85900d2f3ed3.png
[Center 1]: https://img-blog.csdn.net/20150625000334389?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcGlwaXNvcnJ5/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: /images/20220801/05033044d5c54eaa8315477a6a72138c.png
[50_Excel]: http://www.jianshu.com/p/99f3d6359ded
[Excel VBA]: http://www.itongji.cn/cms/article/articledetails?articleid=3257
[Center 3]: /images/20220801/cd520acf7d304e53a16699c9883678d1.png
[blog]: http://blog.csdn.net/pipisorry
[Center 4]: /images/20220801/7bb3f774cd5549cbb740670c38d83749.png
[20180523173530788]: /images/20220801/0ff666c53d6e40a6a0415baf703621ca.png
[Center 5]: /images/20220801/74bd42cd5e314d41ba2b380a6043a711.png
[Center 6]: /images/20220801/6a0783a975ca406ab7f1ed0ea6f5b3c8.png
[Center 7]: /images/20220801/0b688bc792724c7289871d63f839463f.png
[Center 8]: /images/20220801/6d8e481c141b480182f0673359670b7d.png
[win7_EXCEL]: http://tieba.baidu.com/p/1084339746#frs_nav
[Microsoft Office]: http://zhidao.baidu.com/search?word=Microsoft%20Office&fr=qb_search_exp&ie=gbk
[Center 9]: /images/20220801/43b32918e9c34682a324484317fdf1f1.png
[Center 10]: /images/20220801/bd60835123ee496695a916d648c9a136.png