Как найти имя столбца Excel, которое соответствует заданному целому числу? [Дубликат]

Как бы вы определили имя столбца (например, «AQ» или «BH») n-го столбца в Excel?

Редактировать: не зависящий от языка алгоритм, чтобы определить это является главной целью здесь.

22.08.2008 15:49:53
Не удаляйте это. А) Это необходимо в качестве заглушки для увеличения шансов поиска канонического. Б) У него хорошие ответы.
Lance Roberts 23.07.2014 13:49:09
Общей проблемой, решаемой здесь, является преобразование в / из биективной нумерации, в частности, биективной базы-26 .
ecatmur 27.05.2015 11:53:13
20 ОТВЕТОВ
РЕШЕНИЕ

Однажды я написал эту функцию для выполнения именно этой задачи:

public static string Column(int column)
{
    column--;
    if (column >= 0 && column < 26)
        return ((char)('A' + column)).ToString();
    else if (column > 25)
        return Column(column / 26) + Column(column % 26 + 1);
    else
        throw new Exception("Invalid Column #" + (column + 1).ToString());
}
40
22.08.2008 15:53:47
Я что-то упускаю? Не пересекаются ли условия "(column> = 0 && column <26)" и "(column> 25)"? Является ли тест «если еще» опечаткой?
Onorio Catenacci 8.09.2008 19:30:45
@Onorio Catenacci: «столбец> 25» - это еще один способ сказать «столбец> = 26». Я нахожу последнее более понятным, но оба они верны.
technomalogical 7.05.2009 18:53:56
Изумительное решение. Здорово, я хотел бы дать +10
Elbek 20.05.2012 05:07:21
Excel 2007 позволяет иметь 3-символьные столбцы. Таким образом, вам нужен другой случай (или заменить на шаблон).
Michael 17.01.2013 05:30:27
@Michael - этот алгоритм является рекурсивным и работает с столбцом любого размера. Я проверил это в Excel 2007 3-символьные столбцы, и они отлично работают (например, Column(703) = "AAA"и Column(704) = "AAB")
Joseph Sturtevant 17.01.2013 15:29:47

Я в настоящее время использую это, но у меня есть чувство, что это может быть оптимизировано.

private String GetNthExcelColName(int n)
{
    String firstLetter = "";  
    //if number is under 26, it has a single letter name
    // otherwise, it is 'A' for 27-52, 'B' for 53-78, etc
    if(n > 26)
    {
        //the Converts to double and back to int are just so Floor() can be used
        Double value = Convert.ToDouble((n-1) / 26);
        int firstLetterVal = Convert.ToInt32(Math.Floor(value))-1;
        firstLetter = Convert.ToChar(firstLetterValue + 65).ToString();
    }    

    //second letter repeats
    int secondLetterValue = (n-1) % 26;
    String secondLetter = Convert.ToChar(secondLetterValue+65).ToString();

    return firstLetter + secondLetter;
}
-1
22.08.2008 15:58:03

Код Джозефа хорош, но, если вы не хотите или должны использовать функцию VBA, попробуйте это.

Предполагая, что значение n находится в ячейке, A2 используйте эту функцию:

=MID(ADDRESS(1,A2),2,LEN(ADDRESS(1,A2))-3)
5
8.01.2012 10:49:01

Я полагаю, вам нужен код VBA:

Public Function GetColumnAddress(nCol As Integer) As String

Dim r As Range

Set r = Range("A1").Columns(nCol)
GetColumnAddress = r.Address

End Function
1
22.08.2008 16:11:02

Все эти примеры кода, которые выложили эти хорошие люди, выглядят хорошо.

Есть одна вещь, о которой нужно знать. Начиная с Office 2007, Excel на самом деле имеет до 16 384 столбцов. Это переводится как XFD (старый максимум в 256 столбцов был IV). Вам нужно будет несколько изменить эти методы, чтобы они работали для трех символов.

Не должно быть так сложно ...

0
22.08.2008 17:30:09

Алгоритм независимости от языка будет выглядеть следующим образом:

function getNthColumnName(int n) {
   let curPower = 1
   while curPower < n {
      set curPower = curPower * 26
   }
   let result = ""
   while n > 0 {
      let temp = n / curPower
      let result = result + char(temp)
      set n = n - (curPower * temp)
      set curPower = curPower / 26
   }
   return result

Этот алгоритм также учитывает, обновляется ли Excel снова для обработки более 16 тыс. Столбцов. Если вы действительно хотите пойти за борт, вы можете передать дополнительное значение и заменить экземпляры 26 другим числом для размещения альтернативных алфавитов.

11
23.08.2008 00:15:11
Приятно. Я предполагаю, tempчто это целое число, как let temp = n / curPowerокругляется деление, а также / 26?
SantiBailors 8.01.2017 13:05:21

Это делает то, что вы хотите в VBA

Function GetNthExcelColName(n As Integer) As String
    Dim s As String
    s = Cells(1, n).Address
    GetNthExcelColName = Mid(s, 2, InStr(2, s, "$") - 2)
End Function
1
31.08.2008 18:12:13

Вот решение Гэри Уотерса

Function ConvertNumberToColumnLetter2(ByVal colNum As Long) As String
    Dim i As Long, x As Long
    For i = 6 To 0 Step -1
        x = (1 - 26 ^ (i + 1)) / (-25) - 1 ‘ Geometric Series formula
        If colNum > x Then
            ConvertNumberToColumnLetter2 = ConvertNumberToColumnLetter2 & Chr(((colNum - x - 1)\ 26 ^ i) Mod 26 + 65)
        End If
    Next i
End Function

через http://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to-letters/

0
2.09.2008 16:26:56

Учитывая комментарий wcm (top value = xfd), вы можете рассчитать его следующим образом;

function IntToExcel(n: Integer); string;
begin
   Result := '';
   for i := 2 down to 0 do 
   begin
      if ((n div 26^i)) > 0) or (i = 0) then
         Result := Result + Char(Ord('A')+(n div (26^i)) - IIF(i>0;1;0));
      n := n mod (26^i);
   end;
end;

В алфавите 26 символов, и у нас есть система счисления, похожая на шестнадцатеричную или двоичную, с необычным набором символов (A..Z), позиционно представляющим степени 26: (26 ^ 2) (26 ^ 1) ( 26 ^ 0).

0
8.09.2008 19:30:07
IIF здесь не определяется, поэтому эта реализация больше, чем кажется.
hubalazs 6.12.2013 10:16:57

Это похоже на работу в vb.net

Public Function Column(ByVal pColumn As Integer) As String
    pColumn -= 1
    If pColumn >= 0 AndAlso pColumn < 26 Then
        Return ChrW(Asc("A"c) + pColumn).ToString
    ElseIf (pColumn > 25) Then
        Return Column(CInt(math.Floor(pColumn / 26))) + Column((pColumn Mod 26) + 1)
    Else
    stop
        Throw New ArgumentException("Invalid column #" + (pColumn + 1).ToString)
    End If
End Function

Я взял Джозефа и проверил его в BH, затем накормил его 980-1000, и это выглядело хорошо.

1
1.12.2009 20:39:29

= ЗНАК (64 + COLUMN ())

-2
11.08.2009 04:45:03
это работает только для столбцов от А до Я. А как насчет 220 других столбцов?
Patrick Honorez 18.02.2010 23:15:25

В VBA предполагается, что lCol является номером столбца:

function ColNum2Letter(lCol as long) as string
    ColNum2Letter = Split(Cells(1, lCol).Address, "$")(0)
end function
1
25.02.2013 15:25:52
IF(COLUMN()>=26,CHAR(ROUND(COLUMN()/26,1)+64)&CHAR(MOD(COLUMN(),26)+64),CHAR(COLUMN()+64))

Это работает 2 буквенных столбца (вплоть до столбца ZZ). Вы должны были бы вложить другой оператор if для трехбуквенных столбцов.

Приведенная выше формула не работает по столбцам AY, AZа по каждому из следующих nYи по nZстолбцам. Исправленная формула:

=IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64)
3
8.01.2012 10:46:35

Вот самое чистое правильное решение, которое я мог придумать (на Java, но не стесняйтесь использовать ваш любимый язык):

String getNthColumnName(int n) {
    String name = "";
    while (n > 0) {
        n--;
        name = (char)('A' + n%26) + name;
        n /= 26;
    }
    return name;
}

Но, пожалуйста, дайте мне знать, если вы обнаружите ошибку в этом коде, спасибо.

26
17.01.2013 00:59:34
Отличный кусок кода. Довольно лаконично. Не могли бы вы объяснить обоснование этого n--?
seeker 13.10.2013 18:44:37
@KodeSeeker Давайте посмотрим, смогу ли я объяснить это просто ... Каждая цифра имеет 26 символов, но это не основание 26. Это похоже на основание 27, но без 0. n--это способ сдвинуть 0 от основания 27 и у нас осталось отображение диапазона [1, 26] на [A, Z]. Имеет смысл?
Samuel Audet 14.10.2013 11:36:30
Просто примечание для всех, кто пытается использовать этот пример - без активации его мозга (как я): n - это столбец NUMBER (начинается с 1), а не INDEX (начинается с 0)!
eventhorizon 2.12.2016 08:58:57
@ eventhorizon точно!
Jinxer Albatross 3.08.2017 07:19:53

Спасибо, Джозеф Стуртевант! Ваш код работает отлично - он мне нужен в vbscript, поэтому решил поделиться своей версией:

Function ColumnLetter(ByVal intColumnNumber)
    Dim sResult
    intColumnNumber = intColumnNumber - 1
    If (intColumnNumber >= 0 And intColumnNumber < 26) Then
        sResult = Chr(65 + intColumnNumber)
    ElseIf (intColumnNumber >= 26) Then
        sResult = ColumnLetter(CLng(intColumnNumber \ 26)) _
                & ColumnLetter(CLng(intColumnNumber Mod 26 + 1))
    Else
        err.Raise 8, "Column()", "Invalid Column #" & CStr(intColumnNumber + 1)
    End If
    ColumnLetter = sResult
End Function
7
14.01.2011 20:57:53

А вот и преобразование версии VBScript в SQL Server 2000+.

CREATE FUNCTION [dbo].[GetExcelColRef] 
(
    @col_seq_no int
)
RETURNS varchar(5)
AS
BEGIN

declare @Result varchar(5)
set @Result = ''
set @col_seq_no = @col_seq_no - 1
If (@col_seq_no >= 0 And @col_seq_no < 26) 
BEGIN
    set @Result = char(65 + @col_seq_no)
END
ELSE
BEGIN
    set @Result = [dbo].[GetExcelColRef] (@col_seq_no / 26) + '' + [dbo].[GetExcelColRef]  ((@col_seq_no % 26) + 1)
END
Return @Result

END
GO
2
29.10.2012 01:14:21

FYI T-SQL, чтобы дать имя столбца Excel с порядковым номером (начиная с нуля), как один оператор.

Если значение меньше 0 или больше 16 383 (максимальное количество столбцов в Excel2010), возвращается значение NULL.

; WITH TestData AS ( -- Major change points
    SELECT -1 AS FieldOrdinal
    UNION ALL
    SELECT 0
    UNION ALL
    SELECT 25
    UNION ALL
    SELECT 26
    UNION ALL
    SELECT 701
    UNION ALL
    SELECT 702
    UNION ALL
    SELECT 703
    UNION ALL
    SELECT 16383
    UNION ALL
    SELECT 16384
)
SELECT
      FieldOrdinal
    , CASE
       WHEN FieldOrdinal < 0     THEN NULL
       WHEN FieldOrdinal < 26    THEN ''
       WHEN FieldOrdinal < 702   THEN CHAR (65 + FieldOrdinal / 26 - 1)
       WHEN FieldOrdinal < 16384 THEN CHAR (65 + FieldOrdinal / 676 - 1)
                                    + CHAR (65 + (FieldOrdinal / 26) - (FieldOrdinal / 676) * 26 - 1)
       ELSE NULL
      END
      + CHAR (65 + FieldOrdinal % 26)
 FROM TestData
 ORDER BY FieldOrdinal
0
27.09.2012 08:53:31

ОТ wcm:

Если вы не хотите использовать VBA, вы можете использовать этот заменить colnr на номер, который вы хотите

=MID(ADDRESS(1,colnr),2,LEN(ADDRESS(1,colnr))-3)

Обратите внимание на тот факт, что эта формула является изменчивой из-за использования функции ADDRESS. Изменчивые функции - это функции, которые пересчитываются в Excel после КАЖДОГО изменения. Обычно Excel пересчитывает формулы только при изменении их зависимых ссылок.

Это может быть убийца производительности, если использовать эту формулу.

3
14.05.2014 21:06:50

Рубиновый однострочник:

def column_name_for(some_int)
    some_int.to_s(26).split('').map {|c| (c.to_i(26) + 64).chr }.join # 703 => "AAA"
end

Он преобразует целое число в base26, затем разбивает его и выполняет математические вычисления для преобразования каждого символа из ascii. Наконец-то объединяет их. Нет деления, модуля или рекурсии.

Весело.

2
14.12.2012 16:09:37
И неправильно: column_name_for (26) => 'A @'
Ringding 17.09.2013 13:59:23

Это прекрасно работает в MS Excel 2003-2010. Должно работать для предыдущих версий, поддерживающих Cells (...). Адресная функция:

  1. Для 28-й колонны - взятие columnNumber=28; Cells(1, columnNumber).Addressвозвращается "$AB$1".
  2. Деление $знака возвращает массив:["","AB","1"]
  3. Так что Split(Cells(1, columnNumber).Address, "$")(1)дает вам имя столбца "AB".

ОБНОВИТЬ:

Взято из Как преобразовать номера столбцов Excel в алфавитные символы

' The following VBA function is just one way to convert column number 
' values into their equivalent alphabetical characters:

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

ПРИМЕНИМО к: Microsoft Office Excel 2007 SE / 2002 SE / 2000 SE / 97 SE

2
6.06.2013 10:46:13