microsoft excel使用技巧和问题解决

http://blog.csdn.net/pipisorry/article/details/46506195

Excel使用技巧

编码乱码问题

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

解决方案：

原因：Mac默认的编码是utf-8，utf-8编码的文件，用utf-8解码才会正确显示。而Mac下的excel并不支持utf-8，一般支持GBK编码的文件，用GBK解码utf-8编码的文件，所以会出错。
解决方案1： 新建excel空白文档，文件>导入>csv文件，文件格式选择 文件正确的编码，如：unicode(utf-8)或中文（GB 18030) ，即可显示正常。

解决方案2：

1）搜索并打开Mac下自带的“Automator”。

2）点击“新建文稿”。

3）选取文稿类型：快速操作

4）创建Shell脚本
在左上角的输入框中输入“Shell”，看到“运行Shell脚本”这一项 ，双击
“工作流程收到当前” 选择“文稿”（excel或者csv文件或者txt文本文件都可以选择文稿）
“位于” 这一项选择“Finder.app”/访达
“Shell” 这一项选择“/bin/bash”
传递输入，这一项选择“作为自变量”
文本框中输入如下代码：
for f in "$@"; do
if [ -f "$f" ]; then
iconv -s -c -f UTF8 -t GBK "$f" > /tmp/iconv.utf8.gbk.tmp
mv /tmp/iconv.utf8.gbk.tmp "$f"
fi
done

5）点击菜单“文件”-“保存”，为服务取一个名字，例如“文件编码转换utf8->gbk”

6）关闭“Automator”。

在Finder中找到之前的csv文件，右击鼠标，就能看到右击菜单中多出了一项“文件编码转换utf8->gbk”。

7）点击这一项，等待数秒（右上角会出现一个小齿轮，并快速消失），再用Excel重新打开，就会看到转换好的文件。

[Office for Mac 打开 Excel 文件全是乱码，原因和解决办法是什么？]

Mac中打开txt文本文件会报错：未能打开文稿“***.txt”。文本编码“Unicode (UTF-8)”不适用？如何让mac能够读取txt中文？

解决方案：

同上面的解决方案2，只是修改代码中的编码顺序：iconv -s -c -f GBK -t UTF8 "$f" > /tmp/iconv.utf8.gbk.tmp。

windows excel打开csv文件乱码

解决方案：打开 Excel，数据 > 从文本/CSV 文件 > 导入 > 文件原始格式选择utf-8或者空，分隔符选择制表符 > 加载。加载后，所有汉字显示正常，乱码问题解决。

输入

引用

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

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

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

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

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

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

填充柄的使用

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

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

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

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

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

输入制表符

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

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

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

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

excel表格斜杠右上角和左下角分别打字

选中单元格右键 > 下框线 > 其它边框 > 选择左右下角里的斜杠和反斜杠
然后单元格中输入文字，通过空格和alt+enter来控制排版即可。

[怎么在excel表格斜杠右上角和左下角分别打字？_百度知道]

删除

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

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

复制和粘贴

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

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

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

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

行列转置

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

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

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

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

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

​

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

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

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

查找、筛选和替换

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

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

​ >>​

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

[Excel VBA提取目录]

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

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

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

comb_pred_life_***_catl1_Y_120***1就变成了120***1

判断函数if

IF([@label]=[@prediction],1,"")

VLOOKUP查找函数

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

在数据表table_array的首列查找指定的数值lookup_value，并由此返回数据表table_array当前行中指定列col_index_num处的数值。

Lookup_value：要查找的数值；Lookup_value必须在Table_array区域的首列中（现在这个函数可能改进不需要了）。

Table_array：要在其中查找数据的单元格区域；注意，一般区域要用绝对引用或者整列。

Col_index_num：为在table_array区域中待返回的匹配值的列序号（如当Col_index_num为2时,则返回table_array第2列（从列1开始）中的数值）；

Range_lookup：为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE或者0，则返回精确匹配值，如果找不到，则返回错误值#N/A。“在用中文匹配的时候，一定要开启精确匹配，也就是最后一个参数需要指定为FALSE或者0，如果缺少该参数，excel会默认用模糊匹配，即选择相似性最大的那一个作为返回的的数值。”

示例

我们在D65单元格中输入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，确认后，只要在B65单元格中输入一个学生的姓名（如丁48），D65单元格中即刻显示出该学生的语言成绩。

示例：在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中筛选对应科目（中国近现代史）。。。。

LOOKUP简单查找

很sb，不建议使用这个函数！用VLOOKUP代替。

[LOOKUP 函数 - Microsoft 支持]

向量形式
LOOKUP 的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。

LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP 函数向量形式语法具有以下参数：
lookup_value    必需。 LOOKUP 在第一个向量中搜索的值。 Lookup_value 可以是数字、文本、逻辑值、名称或对值的引用。
lookup_vector    必需。 只包含一行或一列的区域。 lookup_vector 中的值可以是文本、数字或逻辑值。
    重要:  lookup_vector 中的值必须按升序排列：..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE；否则，LOOKUP 可能无法返回正确的值。 文本不区分大小写。
result_vector    可选。 只包含一行或一列的区域。 result_vector 参数必须与 lookup_vector 参数大小相同。 其大小必须相同。

示例

LOOKUP(搜索目标, 搜索位置, 结果值)
LOOKUP(C166, $D$2:$D$42, $J$2:$J$42)

numbers中不要使用全列（如A这种），要写成A1:A1000这样，否则可能出现莫名其妙的错误。建议使用sumifs替代。

数组形式
官方强烈建议使用 VLOOKUP 或 HLOOKUP，不要使用数组形式。

lookup函数多条件查找

=LOOKUP(1,0/((A:A="base")*(B:B=B92)*(C:C=C92)*(D:D=D92)),Q:Q)  //numbers中未成功

// 不行就用SUMIFS
=VLOOKUP("base"&B92&C92,IF({1,0},A:A&B:B&C:C,D:D),2,0) //excel未成功
LOOKUP(1,0÷((A="base")×(org=B92)×(ad_type=C92)×(level=D92)),ctcvr_diff) //numbers中未成功

[LOOKUP函数在多条件查找中的应用 - 简书]

[excel多条件查找三种方法：lookup、vlookup、indexmatch多条件查找案例_Excel技巧-蓝山办公]

[Excel函数公式：Lookup函数，查找引用之王！！！ - 知乎]

示例

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

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

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

将大二上和大二下成绩复制到同一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列不存在）

​

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

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

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

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

排序

自动列出排序序号

​

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

汇总和统计

统计各班各科目平均分

​

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

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

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

复制行（学号姓名等）

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

SUMIF 函数

SUMIF(range, criteria, [sum_range]) //多条件加和（excel和numbers成功）

[SUMIF 函数 - Microsoft 支持]

示例：统计某个特定值对应的数量。如要统计如下ETC币种的买入总数量

​

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

SUMIFS 函数

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

示例：计算以“香”开头并由“卢宁”售出的产品的总量。

=SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "卢宁")

[SUMIFS 函数 - Microsoft 支持]

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

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

=COUNTIF(C1:E1,B1)

加权平均值SUMPRODUCT

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

​=ROUND(SUMPRODUCT(E6:AK6,E$3:AK$3)/102,2)

合并

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

1.C1=A1&A2&A3

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

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

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

​

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

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

​

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

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

​

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

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

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

​

图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)

Next

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:合并后多余的特定行可以用筛选选出来再复制粘贴

from:microsoft excel使用技巧和问题解决_-柚子皮-的博客-CSDN博客_下拉云89b1e6

ref: