楼主 水星钓鱼 |
Q:如何用公式生成Excel列标题的字母的序列? A:下列公式可获取A1引用样式下的Excel的列标题。
谢谢4楼的yfan17的指正,更正为如下的公式,可以右拖
|
2楼 wise |
=CHAR(ROW(INDIRECT("1:26"))+64) 这个也可以 |
3楼 水星钓鱼 |
有点不同,你的只能到字母"Z"吧. |
4楼 yfan17 |
水星钓鱼版主笔误,公式不能右拖。COLUMN(1:1)修改为COLUMN():
|
5楼 水星钓鱼 |
谢谢yfan17。已经更改了。 |
6楼 yaoxuejian |
学习了······ |
7楼 breezy |
=CHOOSE(TRUNC(ROW(A1)/27)+1,"","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")&CHOOSE(MOD(ROW(A1)-1,26)+1,"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z") 这个可以返回到ZZ列 |
8楼 悟空师弟 |
Q:请问有没有哪个公式可以返回单元格所在列的列标? A:公式如下: 公式1:=SUBSTITUTE(SUBSTITUTE(CELL("address",A1),"$",),1,) 公式2:=MID(ADDRESS(1,COLUMN(A1),4,1),1,LEN(ADDRESS(1,COLUMN(A1),4,1))-1) 详见附件: 如何返回所在列的列标?.rar 效果如图: |
9楼 zlxtl |
用VBA完成: MsgBox Split(ActiveCell.Address, "$")(1) |
10楼 杏花雨V翟 |
=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),) 参与一下 |
11楼 wjc2090742 |
|
12楼 zlxtl |
不具备通用性 |
13楼 wjc2090742 |
http://www.exceltip.net/thread-527-1-1.html |
14楼 99253415 |
Q:如何获取当前单元格所在列标,即在A列则返回值"A",依此类推。 A:
如何获取当前单元格所在列标.rar |
15楼 zzmxy |
来个简单一点的~~
|
16楼 larkzh |
这个不错,适用在任何单元格。 |
17楼 yaoxuhuijsz |
学习 |
18楼 caoxi09 |
学习了,麻烦点! |
19楼 cango |
=SUBSTITUTE(ADDRESS(1,COLUMN(1:1),4),1,) 要Ctrl+Shift+Enter |
20楼 paul001 |
好多方法可以选择啊 |
21楼 大海/微笑 |
公式是否没有写全?好像是:SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"") |