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) ,然后按回车键。
复制和粘贴
批量复制相同内容到不同行
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单元格中多余的文字怎么设置不显示
选中单元格 > 开始 > 自动换行
>>
取单元格中_分割的最后一个字符串
一种方法是先将所有分割符替换成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 的向量形式在单行区域或单列区域(称为“向量”)中查找值,然后返回第二个单行区域或单列区域中相同位置的值。
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中未成功
[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成功)
示例:统计某个特定值对应的数量。如要统计如下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, "卢宁")
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: