电子表格财务实用技巧

本文文章目录
一、将竖排的数据快速复制到横排的由2个单元格合并的单元格里
二、对指定区域中符合指定条件的单元格计数-COUNTIF函数
三、一个表的数据对应到另一个表-VLOOKUP、HLOOKUP函数
四、快速跳转起始单元格和快速填充相同内容
五、不合并单元格的情况下标题栏显示为合并居中

之后的空了来看整不整目录吧,不晓得怎么快捷的搞目录,用HTML整的锚点,有点麻烦^^感觉越来越懒了,反正后面的越来越简短,自己能看懂就行了


一、将竖排的数据快速复制到横排的由2个单元格合并的单元格里



如图1564382912(1).jpg

我需要把上面这个表名字栏的名字复制到下面这个表中,下面这个表是横排的,而且是由2个单元格合并的单元格,直接复制粘贴肯定是不行的。

1564383094(1).jpg

第一步,在第一个表名字的旁边重新编个序号,然后再重复一次,如图

1564383324(1).jpg

第二步,升序排序,扩展选定区域

1564383542(1).jpg

第三步,复制选定部分

1564383721(1).jpg

第四步,在第二个表需要复制进数据的第一个单元格空白处右键,选择性粘贴,选择数值,转置处打勾(不是横排的不需要转置)

1564383879(1).jpg

最后,数据就复制到里面了。

>1564385410(1).jpg


二、对指定区域中符合指定条件的单元格计数COUNTIF函数


如图,我想统计座位数在20座及20座以下,21座至30座,31座及31座以上的个数。

1564715665(1).jpg

在汇总表格里,用COUNTIF函数选定范围,然后分别按条件输入,就可以自动得到个数了。

1564715721(1).jpg

1564715740(1).jpg

1564715761(1).jpg


三、一个表的数据对应到另一个表VLOOKUP(纵向查找)、HLOOKUP函数(横向查找)

=VLOOKUP(查找值,查找范围,查找范围内的第几列,FALSE)

如:下面“姓名”在A1栏,“吉祥物组”在C3栏


姓名金额部门
张三1000空气污染组
李四2000吉祥物组

此时,在另个表格里,要自动查找李四(李四在E1单元格里)的部门,就在李四后的表格里输入=VLOOKUP(E1,$A$1:$C$3,3,FALSE),就自动对应出李四的部门了,单元格范围字母前后加$是为了绝对引用,锁定范围(可以点进单元格里按F4快捷切换),大量数据拖动公式的时候不移动查找范围。


HLOOKUP同理,只是查找的方向不同。


扩展一下,对应不到的数据,显示的不是空白而是N/A,影响数据计算,可以公式外再套公式,如上面那个公式可以改为=IFERROR(VLOOKUP(E1,$A$1:$C$3,3,FALSE),"0"),则对应不到的数据为0,也可以改为横杠空白。


四、快速跳转起始单元格和快速填充相同内容

快速跳转起始单元格:Ctrl+方向键

快速填充相同内容:在要填充的竖排单元格的最后一个单元格按Ctrl+Shift+↑,然后Ctrl+D


五、不合并单元格的情况下标题栏显示为合并居中

不合并单元格的情况下标题栏显示为合并居中,主要是有怕标题栏是个合并单元格造成一些列的数据不好处理。

在选择好标题栏的范围,右键,单元格格式,对齐,水平对齐里选跨列居中。


六、单元格里的日期距离本月有多少个月

=DATEDIF(日期或有日期的单元格位置,TODAY(),"m")


七、范围内空白栏填充数字0

选中需要填写数字0的范围,Ctrl+G调出定位对话框,定位条件选空值,然后输入数字0,再按住Ctrl不放,回车,该范围内的所有空白栏都填充了数字0。


八、两个单元格的内容合并到一个单元格里

如一个在A1,一个在A2,需要将A1和A2的内容都显示到A3里,则在A3输入=A1&A2


九、一个单元格的内容(中间有相同的间隔符号)分列到几个单元格里

数据,分列,输入间隔的符号。

步骤中有一步可以选数据类型,有时候不分列,只是批量单元格转文本格式也可以用这个,选中需要转文本的列,分列,一直下一步,数据类型选文本完成。


十、按标注颜色数据求和

有时候颜色不在一列里,在一列就可以用筛选工具按颜色筛选了,要在一个表很多列里按颜色合计数据的话,

CTRL+F 查找 选项 格式 选好后用吸管工具需要的颜色上吸一下,查找全部,CTRL+A全选,

然后在名称框里输入一个名称,回车,

12.png

然后在要显示合计的单元格里输入=SUM(名称)。


十一、比较两个文本字符串,如果它们完全相同,则返回 TRUE,否则返回 FALSE

=EXACT(第一个单元格位置,第二个单元格位置)


十二、永不看错行

很多行很多列的时候,肉眼对行费事费力还容易出错,WPS个人版会员有个好用的功能,永不看错行,但企业版我没找到这个功能(已经找到了,在视图-阅读模式),这个代码可以实现这个功能,我用的WPS企业版,OFFICE方法类似,开发工具→VB编辑器Project展开,WPS表格对象展开,右键点击需要的表格,查看代码,然后输入以下代码

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    On Error Resume Next

    Cells.FormatConditions.Delete

    With Target.EntireRow.FormatConditions

        .Delete

        .Add xlExpression, , "TRUE"

        .Item(1).Interior.ColorIndex = 24

    End With

    With Target.EntireColumn.FormatConditions

        .Delete

        .Add xlExpression, , "TRUE"

        .Item(1).Interior.ColorIndex = 24

    End With

    Selection.FormatConditions.Delete

End Sub



效果如下:

blob.png


Ps.对颜色有强迫症将代码里的两个24换成1~56里的数字,用自己喜欢的颜色


十三、按条件求和SUMIF和SUMIFS


SUMIF

这个有时候可以代替数据透视

比如


姓名金额
2011张三100
2012李四200
2013小小300
2011张三400
2012小小500
2013李四600
2013张三100


要求张三的金额和,=SUMIF(姓名单元格范围区域,张三的单元格,金额单元格范围区域)


或者点fx选择


SUMIFS

每一年设置为下拉菜单,选哪一年,求和哪一年张三的金额和

在表格输入

2011
2012
2013

数据,有效性,允许选为序列,来源选择上面这个范围,得到一个年的下拉菜单格。

=SUMIFS(金额单元格范围区域,年的单元格范围区域,下拉菜单格,姓名单元格范围区域,张三的单元格)

或者点fx选择

好了,再来一个,用SUMIFS来做一个实用的东西,比如求一段日期之间的金额合计。

源数据:

image.png

效果:

image.png

这里的起和止的日期用了数据有效性-序列,可以在生成下拉菜单选择起止日期,

金额和的公式为=SUMIFS(数据!$B$3:$B$12,数据!$A$3:$A$12,">="&B2,数据!$A$3:$A$12,"<="&D2)


十四、排名RANK

=RANK(数值,数值范围并按F4锁定,排位方式0降序非0升序)

让排名单元格前面显示红旗

选择排序范围,条件格式,图表集,选红旗,再选条件格式,管理规则,双击输入显示红旗的规则


十五、Python快速处理电子表格代码

需要Python编程软件,安装pandas、openpyxl、NumPy、xlrd第三方库

安装库的方法为CMD下pip install XXX,安装了用pip list查看是否安装上

第三方库安装太慢可以使用国内的源,在安装命令后加个 -i 然后接国内源地址

pip install pandas  -i https://pypi.douban.com/simple/

另外,升级库,运行程序时如提示xlrd版本太低的错误,则

pip install --upgrade xlrd

Python库国内镜像地址:

1.阿里云:https://mirrors.aliyun.com/pypi/simple/

2.豆瓣:https://pypi.douban.com/simple/

3.清华大学:https://pypi.tuna.tsinghua.edu.cn/simple/

4.中国科学技术大学 http://pypi.mirrors.ustc.edu.cn/simple/

5.华中理工大学:http://pypi.hustunique.com/

6.山东理工大学:http://pypi.sdutlinux.org/

————————————————

1、批量透视汇总

多个电子表格的汇总透视,文件路径和汇总值、透视列名按自己的修改,维度可以增加

import os
import numpy as np
import pandas as pd
 
files = []
dfs=[]
for file in os.listdir('C:\\Users\\YANGYIVIP\\Desktop\\分析'): 
    if '.xlsx' in file:
        files.append(file)
        dfs.append(pd.read_excel('C:\\Users\\YANGYIVIP\\Desktop\\分析\\'+file))
print(files)
    
concat_df = pd.concat(dfs,sort=True)
df_toushi = pd.pivot_table(concat_df,values=['汇总值1','汇总值2'],index=['透视列名1','透视列名1'],aggfunc=np.sum)
df_toushi.to_excel('C:\\Users\\YANGYIVIP\\Desktop\\分析\\汇总透视.xlsx')

2、多个电子表格的合并

import pandas as pd    #1.记录文件夹路径 2.将原表格构建到新表格中  3.找到所有需要合并的表,写进新表格
import os
file_dir = 'C:\\Users\\YANGYIYIVIP\\Desktop\\YY'  #文件路径
new_filename = file_dir + '\\new_file.xlsx'  #构建新的表格名称
file_list = os.listdir(file_dir)   #找到文件路径下的所有表格名称,返回列表
new_list = []

for file in file_list:
  
    file_path = os.path.join(file_dir,file)     #重构文件路径
    print(file_path)
    
    dataframe = pd.read_excel(file_path)   #将excel转换成DataFrame
    
    new_list.append(dataframe)   #保存到新列表中
  
df = pd.concat(new_list)   #多个DataFrame合并为一个
df.to_excel(new_filename,index=False)    #写入到一个新excel表中

3、多sheet合并

import pandas as pd   #合并多个sheet
data = pd.read_excel('C:\\Users\\YANGYIVIP\\Desktop\\财务费用.xlsx',None)
cols = list(data.keys())
newdata =pd.DataFrame()
for i in cols:
    df= data[i]
    newdata=pd.concat([newdata,df])
newdata.to_excel('C:\\Users\\YANGYIVIP\\Desktop\\财务合并数据.xlsx',index=False)

4、按照某一列的条件拆分为多个sheet,比如下面代码为把合并.xlsx按E列拆分

import openpyxl  #拆分多个sheet
workbook = openpyxl.load_workbook('C:\\Users\\YANGYIVIP\\Desktop\\合并.xlsx') 

s = workbook['Sheet1']

departs = []
for i in range(1,s.max_row):
    departs.append(s['E'][i].value)
    
departs = list(set(departs))
departs_linenum = {}
for depart in departs:
    departs_linenum[depart]=[1]
    
for i in range(1,s.max_row):
    depart = s['E'][i].value
    departs_linenum[depart].append(i+1)
    
for depart in list(departs_linenum.keys()):
    new_sheet = workbook.create_sheet(depart)
    i = 1
    for linenum in departs_linenum[depart]:
        for j in range(s.max_column):
            new_sheet.cell(i,j+1).value = s[str(linenum)][j].value
        i+=1

workbook.save('C:\\Users\\YANGYIVIP\\Desktop\\拆分.xlsx')

5、从Excel表中查找指定数据填入新表

模板

image.png

要抽取原文件的黄色部分到模板里生成新表

image.png

#读取xls文件中的数据
import xlrd

file = "原表.xls"
wb = xlrd.open_workbook(file) #读取工作簿
ws = wb.sheets()[0] #选第一个工作表
data = []

for row in range(7, ws.nrows):
    name = ws.cell(row, 1).value.strip() #科室名称
    total1 = ws.cell(row, 2).value #总计
    total2 = ws.cell(row, 3).value #计
    avg = ws.cell(row, 20).value #平均每日人次
    
    info_list=[name,total1,total2,avg]

    if info_list[0] != "": #去除空数据
        data.append(info_list)

from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment #设置单元格格式
thin = Side(border_style="thin", color="000000")#定义边框粗细及颜色

file = "统计表模板.xlsx"
wb = load_workbook(file)
ws = wb.active

#写入数据
for i in data:
    ws.append(i)
    
#设置字号,对齐,缩小字体填充,加边框
#Font(bold=True)可加粗字体
for row_number in range(3, ws.max_row+1):    
    ws.row_dimensions[row_number].height = 25 #设置行高
    for col_number in range(1,5):
        c = ws.cell(row=row_number,column=col_number)
        c.font = Font(size=11,bold=True)
        c.border = Border(top=thin, left=thin, right=thin, bottom=thin)
        c.alignment = Alignment(horizontal="center", vertical="center")
    
wb.save("统计表.xlsx")


记录在这里为了方便不至于久了不用搞忘了,空了再补充🙈


最后,来个彩蛋,Excel函数词典(483个函数说明)点此下载 &



常用快捷键组合速记表
office 通用类表格编辑类
Windows版Mac版功能Windows版Mac版功能
F1F1打开帮助文档Ctrl+1Command+1或Control+1打开数字格式窗口
F4F4重复上一次操作、撤销后恢复F2F2进入编辑状态
Ctrl+BCommand+B
或Control+B
字体加粗EscEsc推出编辑;取消正处理的进程
Ctrl+FControl+F查找Ctrl+EnterCommand+Return
或Control+Return
批量输入相同内容
Ctrl+G/F5Shift+F5定位Ctrl+Shift+;Command+分号(;)插入系统时间
Ctrl+HCommand+Shift+H
或Control+H
替换Ctrl+;Control+分号(;)插入系统日期
Ctrl+ZCommand+Z
或Control+Z
撤销上一步操作Ctrl+DCommand+D或Control+D向下填充(可先预选区域)
Ctrl+CCommand+C
或Control+C
复制Ctrl+RCommand+R或Control+R向右填充(可先预选区域)
Ctrl+XCommand+X
或Control+X
剪切Alt+拖拽对象边缘-对齐网格边缘调整图表尺寸
Ctrl+VCommand+V
或Control+V
粘贴Alt+EnterCommand+Option+Return或Control+Option+Return单元格内强制换行
Ctrl+Alt+VCommand+Control+V
或Control+Option+V
或Command+Option+V
选择性粘贴Ctrl+拖曳填充柄Ctrl+拖曳填充柄自动填充(复制或序列填充)
Ctrl+NCommand+N文件工作簿双击填充柄双击填充柄填充到最底行
Ctrl+OCommand+O
或Control+O
打开文件表格选择和移动类
Ctrl+PCommand+P
或Control+P
打印Ctrl+ACommand+A全选数据区域,对象
Ctrl+SCommand+S
或Control+S
保存Ctrl+拖选、单击Command+拖选、单击选中多个不连续区域、工作表
Ctrl+WCommand+W
或Control+W
关闭文件Ctrl+两次单击Command+两次单击选中两次单击间的连续区域
Ctrl+拖拽对象Command+拖拽对象复制对象、选区Ctrl+方向键Command+方向键移动到当前数据区域各个方向的边界
公式编辑类Ctrl+Shift+方向键Command+Shift+箭头键从当前位置选到数据区域边缘
F4Command+T或F4公式引用方式切换($)Ctrl+Home-返回A1
EscEsc取消输入Ctrl+鼠标滚轮Command+鼠标滚轮缩放表格比例
Alt+=-快速求和EnterEnter完成输入并向下移动;确定
Ctrl+'Control+重音符(`)显示/隐藏公式TabTab完成输入并向右移动;自动补全函数



常用函数速查表
逻辑判断查找匹配普通数学计算和统计
IF判断条件满足与否返回不同的值VLOOKUP按照垂直方向搜索区域SUM求和计算
AND检测所有的条件是否为真HLOOKUP按照水平方向搜索区域AVERAGE求平均值
OR检测任意一项条件是否为真LOOKUP在向量或数组中查找并返回匹配值COUNTIF计算满足条件的单元格人个数(单条件)
TRUE真,作为参数时代表成立、模糊,值等于1INDEX按行、列索引,返回交叉位置的值MAX计算区域内最大值
FALSE假,作为参数时代表不成立、精确,值等于0MATCH查找对象并返回匹配值的行、列位置MIN计算区域内最小值
NOT对表示条件的参数的逻辑值求反日期和时间LARGE求第几大的值
IFERROR判断结果是否为错误值,是则返回指定的值DATE返回指定年月日对应的日期。SMALL求第几小的值
IFNA判断结果是否为错误NA,是则返回指定的值WORKDAY返回自开始日期算起相隔指定天数之前或之后(不包括周末和专门指定的假日)的日期的序列号.RANK求区域内指定数值的排名
文本处理NETWORKDAYS计算除了周六、日和休息日之外的工作天数MOD指定除数求余
CLEAN删除非打印字符TIME从时、分、秒来计算出时间的序列号值SUMPRPDUCT计算两列数据的乘积
TRIM删除多余的空格字符TODAY计算当前的日期RAND生成连续型随机无限步循环小数
&链接多个文本NOW计算当前的日期和时间RANDBEWTEEN生成指定区间内的离散型随机整数
LEN统计文本字符串中字符数目(计算文本的长度)YEAR从日期中提取出"年"COUNT计算包含数值的单元格数量
LEFT从一个文本字符串的第一个字符开始,截取指定数目的字符MONTH从日期中提取出"月"COUNTA计算所有非空单元格数量,包括文本
RIGHT从一个文本字符串的最后一个字符开始,截取指定数目的字符DAY从日期中提取出"日"按条件统计计算
MID从一个文本字符串的指定位置开始,截取指定数目的字符WEEKDAY计算出与日期相对应的星期SUMIF对满足条件的单元格的数值求和(单条件)
FIND检索字符位置(区分大小写)WEEKNUM计算从1月1日算起的第几个星期SUMIFS对满足条件的单元格的数值求和(多条件)
TEXT将数值转换成自由的显示格式文本DATEDIF计算期间内的年数、月数、天数COUNTIFS计算满足条件的单元格人个数(多条件)
VALUE将表示数值的文本转换成数值HOUR从时间中提取出"时"DMAX单条件求最大值
UPPER将所有英文字母转换成大写字母MINUTE从时间中提取出"分"DMIN单条件求最小值
SUBSTITUTE替换检索的文本SECOND从时间中计算出"秒"MAXIFS多条件求最大值
SEARCH检索字符位置(不区分大小写)TIMEVALUE从表示时间的文本来计算序列号值MINIFS多条件求最小值



扫码访问