电子表格财务实用技巧

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

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

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

如图

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

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

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

第三步，复制选定部分

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

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

>

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

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

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

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

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

如：下面“姓名”在A1栏，“吉祥物组”在C3栏

此时，在另个表格里，要自动查找李四(李四在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全选,

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

然后在要显示合计的单元格里输入=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

效果如下：

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

十三、按条件求和SUMIF和SUMIFS

SUMIF

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

比如

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

或者点fx选择

SUMIFS

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

在表格输入

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

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

或者点fx选择

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

源数据：

效果：

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

金额和的公式为=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表中查找指定数据填入新表

模板

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

#读取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个函数说明）点此下载 &