excel行变列引用,excel引用一列到一行函数
在工作中处理Excel表格经常会遇到问题。编辑Excel表格时,发现表格中列数过多,行数过少。为了便于打印,您可能需要转换表格的行和列。也许是为了进一步的统计分析,目前的格式不太方便,这个时候也会用到行列转换。
下面这个十字Excel表是很常见的格式,方便填写和查看:
但是如果要做进一步的统计分析,这种格式不方便,需要将行和列转换成如下格式的详细列表:
显然人工操作会很麻烦。如果数据量小,也是可以的。如果数据量很大,就要花很多时间,这是灾难。
我们以此为例来说明几种常见的解决方案。
解决方案一:Excel透视表Excel可以通过透视表支持行列转换功能,效果如下:
显然,这不是我们想要的格式。Excel数据透视表可以满足简单格式的行列转换,但如果格式稍复杂,转换效果往往不尽如人意。
2.编程语言是通过写程序来解决的,思路很简单:
加载excel文件,并加载所需的工作表。
读取“账套名称”这一行,转换成字符串数组。
读取“帐户代码”列,并将其转换为字符串数组。
按“账户代码”分组,用“账套名称”数组构造一个表。
根据“账套名称”对应的数据,遍历所有明细值,填入对应的表格中。
这样,就可以构建相应的时间表。
如果用Java实现,估计代码量不会少于200行。如果需要将结果输出到excel文件中,开发工作量会更多。虽然Excel本身提供了VBA,谁知道麻烦程度,别提了。其他语言呢?传说python有处理行列转换的功能(熊猫包有pivot功能),代码量会比java少很多。让我们来试试:
import pandas as PD import numpy as NP df=PD . read _ excel( d:excel pandas . xlsx ,0,3)cols=df . columns . Values . to list()# Get header information #删除前两列,只保留cols . Remove( account code )cols . Remove( account details )#构造一个列表. frames=[]for cols:df1=df . pivot _ table(index=[ account code , account details],Values=[col]) df1.renameframes.append(df1) # concat将同一字段的表首尾相连Result=PD . concat(frames)Result . rename(columns={ 3:帐套名称 },In=true) Result。To _ Excel (D: Excel pandas _ n.xlsx ,sheet _ name= account details )很好用,真的很简洁!这是Python生成的一个excel文件:
但是,有一个小问题。这个excel格式有点特殊。如果你想使用Python的pivot,我们必须将“帐户代码”和“帐户详细信息”移动到与转换列标题相同的行,它将看起来像下面这样。否则,您必须特别注意代码。反正只有一句台词。手工做就行了,比写代码方便多了。
无论如何,python处理这个细节的小“瑕疵”并不影响它的便利性。Python真的名不虚传。虽然它使用了循环,但整个代码看起来只有10行。
能简单点吗?
嘿,嘿,太好了!
3.计算器的编程。让我们看看计算器的代码:
代码非常简单。让我们列出每一步的中间结果:
A1:加载excel文件工作表1,提取指定范围(3行到40行)的数据,其中选项@ t表示第一行的标题。加载数据并生成如下表格:
A2:删除非数据行
A3:更改列名
A4:将第三列的列名连接成一个字符串,并用“,”分隔
A5: Pivot函数转换行列数据,将A4中对应的列数据放入“值”列。
A6:将排序后的数据保存为xlsx文件。
编译器脚本只有六行,所以不存在循环和判断这种东西,也不像Python那样需要先手动转身,让这个看似“乱七八糟”的数据表得到处理。相比之下,Python使用“N”字的列优先转换进行多次循环,而计算器使用行优先的一次性处理。在数据处理上,计算器在细节处理和使用习惯上更专业。而且计算器的开发环境很容易调试,可以看到每个操作步骤的中间结果,方便挑出错误,让开发更方便。在这个常规的数据处理任务中,计算器优于Python。
优点:关于这个问题,关于python和计算器的区别,我来说说我自己的经验:
1.多列转换。对于需要多列行列转换并聚合成“长”列的场景,python需要将每个数据列构造成一个数组,添加一列记录当前列名,然后追加到一个大列表中。最后合并,去掉标题;在合并的非第一个数组中;
计算器更容易。它只是将需要转换的列聚集在一起。与笨重的python相比,计算器至少可以节省几个脑细胞。
2.名称更改python无法更改需要转换的列的名称,比如cols[0]=天津。这时候python找不到修改前的关键词“哪个朋友挖的坑,别以为我找不到”。欺负大叔的视力,能不能给我破例?
但是对于相应的计算器就很方便了,比如A1.rename(_1:账户代码,_2:账户明细,4%:成都)。
3.Title Null问题Python读取excel中转换行的标题时,前两列为空(对应原excel中的“账户编码,账户明细”),此时标题列中的Null值已经没有了。这个“坑”有点隐蔽。真的没找到,还丢了两个,有点尴尬;
但如果计算器能识别出来,就会自动加上相应的标记_1和_2,这样在处理数据时,就能找到对应的两列。
4.网格格式编程计算器使用网格A1格式,自动关联所在位置的对象,非常方便,感觉很有特色;Python只能望洋兴叹。