将excel列名转换为数字

我想编写一个函数将excel列名转换为相应的数字.到目前为止我想出的只是部分工作.也就是说,较低的字母首先出现的输入("AB",AC"等)工作正常.但它不起作用("BA","CA"等).我已经追踪到错误到行,y <- which(base::LETTERS==x)但我真的不明白这些布尔运算符如何在向量上工作.任何建议？

#so to pass excel column-names directly, this function should do the trick
LettersToNumbers <- function(input){
    x <- toupper(substring(input, c(1:nchar(input)), c(1:nchar(input)))) #parse input-string
    y <- which(base::LETTERS==x) #letters to numbers
    y <- rev(y) #reverse
    #base26 conversion:
    result <- 0
    for (i in 1:length(y)){
        result <- result + ( y[i]*26^(i-1) )
    }
    return(result)
}

事实上,事实证明还有一些例子不起作用.这里有一些,我真的不明白发生了什么.

> which(LETTERS==c("A", "B"))
[1] 1 2
> which(LETTERS==c("A", "C"))
[1] 1
> which(LETTERS==c("A", "D"))
[1] 1 4
> which(LETTERS==c("D", "A"))
integer(0)
> 

Derek Damron.. 9

这很快又脏,但我认为它可以让你得到你想要的东西.它应该适用于任意字符串长度.

# Input: A string of letters s
# Output: Corresponding column number
LettersToNumbers <- function(s){
  # Uppercase
  s_upper <- toupper(s)
  # Convert string to a vector of single letters
  s_split <- unlist(strsplit(s_upper, split=""))
  # Convert each letter to the corresponding number
  s_number <- sapply(s_split, function(x) {which(LETTERS == x)})
  # Derive the numeric value associated with each letter
  numbers <- 26^((length(s_number)-1):0)
  # Calculate the column number
  column_number <- sum(s_number * numbers)
  column_number
}
# Vectorize in case you want to pass more than one column name in a single call
LettersToNumbers <- Vectorize(LettersToNumbers)

# Quick tests
LettersToNumbers("A")
LettersToNumbers("Z")
LettersToNumbers("AA")
LettersToNumbers("BA")
LettersToNumbers("AAA")
LettersToNumbers(LETTERS)

如上面的评论中所述,您的代码的主要问题是矢量回收,此功能通过使用避免了sapply.

这很快又脏,但我认为它可以让你得到你想要的东西.它应该适用于任意字符串长度.

# Input: A string of letters s
# Output: Corresponding column number
LettersToNumbers <- function(s){
  # Uppercase
  s_upper <- toupper(s)
  # Convert string to a vector of single letters
  s_split <- unlist(strsplit(s_upper, split=""))
  # Convert each letter to the corresponding number
  s_number <- sapply(s_split, function(x) {which(LETTERS == x)})
  # Derive the numeric value associated with each letter
  numbers <- 26^((length(s_number)-1):0)
  # Calculate the column number
  column_number <- sum(s_number * numbers)
  column_number
}
# Vectorize in case you want to pass more than one column name in a single call
LettersToNumbers <- Vectorize(LettersToNumbers)

# Quick tests
LettersToNumbers("A")
LettersToNumbers("Z")
LettersToNumbers("AA")
LettersToNumbers("BA")
LettersToNumbers("AAA")
LettersToNumbers(LETTERS)

如上面的评论中所述,您的代码的主要问题是矢量回收,此功能通过使用避免了sapply.