algorithm - rango - formulas de excel

¿Cómo encuentro el nombre de columna de Excel que corresponde a un número entero dado?

Question

Esta pregunta ya tiene una respuesta aquí:

• Cómo convertir un número de columna (por ejemplo, 127) en una columna de Excel (por ejemplo, AA) 45 respuestas

¿Cómo determinaría el nombre de la columna (por ejemplo, "AQ" o "BH") de la enésima columna en Excel?

Editar: Un algoritmo independiente del idioma para determinar este es el objetivo principal aquí.

Answer 1

= CHAR (64 + COLUMN ())

Answer 2

Actualmente uso esto, pero tengo la sensación de que se puede optimizar.

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;
}

Answer 3

Aquí está la solución Gary Waters

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

a través de http://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to-letters/

Answer 4

DESDE wcm:

Si no quiere usar VBA, puede usar este reemplazar colnr con el número que desee

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

Tenga en cuenta que esta fórmula es volátil debido al uso de la función DIRECCIÓN. Las funciones volátiles son funciones que son recalculadas por excel después de CADA cambio. Normalmente Excel recalcula fórmulas solo cuando cambian sus referencias dependientes.

Podría ser un asesino de rendimiento, para usar esta fórmula.

Answer 5

El código de Joseph es bueno pero, si no quiere o no necesita usar una función de VBA, intente esto.

Suponiendo que el valor de n está en la celda A2 use esta función:

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

Answer 6

En VBA, suponiendo que lCol es el número de columna:

function ColNum2Letter(lCol as long) as string
    ColNum2Letter = Split(Cells(1, lCol).Address, "$")(0)
end function

Answer 7

Esta es la solución correcta más limpia que pude encontrar (en Java, pero puede usar su idioma favorito):

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

Pero por favor avíseme si encuentra un error en este código, gracias.

Answer 8

Esto funciona bien en MS Excel 2003-2010. Debería funcionar para las versiones anteriores que admiten las Celdas (...). Función de Address :

1. Para la columna 28 - tomando columnNumber=28 ; Cells(1, columnNumber).Address devuelve "$AB$1" .
2. Hacer una división en el signo $ devuelve la matriz: ["","AB","1"]
3. Así que Split(Cells(1, columnNumber).Address, "$")(1) le da el nombre de columna "AB" .

ACTUALIZAR:

Tomado de Cómo convertir números de columnas de Excel en caracteres alfabéticos

'' 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

SE APLICA A: Microsoft Office Excel 2007 SE / 2002 SE / 2000 SE / 97 SE

Answer 9

Esto hace lo que quieras en 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

Answer 10

Esto parece funcionar en 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

Cogí el de Joseph y lo probé en BH, luego lo alimenté con 980-1000 y se veía bien.

Answer 11

FYI T-SQL para dar el nombre de la columna Excel dado un ordinal (basado en cero), como una sola declaración.

Cualquier valor inferior a 0 o superior a 16.383 (columnas máximas en Excel2010) devuelve 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

Answer 12

Gracias, Joseph Sturtevant! Tu código funciona perfectamente, lo necesitaba en vbscript, así que pensé que compartiría mi versión:

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

Answer 13

Ruby one-liner:

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

Convierte el entero en base26, luego lo divide y hace algunas operaciones matemáticas para convertir cada carácter de ascii. Finalmente los une a todos nuevamente. Sin división, módulo o recursión.

Divertido.

Answer 14

Supongo que necesitas el código VBA:

Public Function GetColumnAddress(nCol As Integer) As String

Dim r As Range

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

End Function

Answer 15

Teniendo en cuenta el comentario de wcm (valor superior = xfd), puedes calcularlo así;

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;

Hay 26 caracteres en el alfabeto y tenemos un sistema numérico como hex o binario, solo con un conjunto de caracteres inusual (A..Z), que representa posicionalmente los poderes de 26: (26 ^ 2) (26 ^ 1) ( 26 ^ 0).

Answer 16

Todos estos ejemplos de código que estas buenas personas han publicado se ven bien.

Hay una cosa a tener en cuenta A partir de Office 2007, Excel en realidad tiene hasta 16,384 columnas. Eso se traduce a XFD (el antiguo máximo de 256 columnas era IV). Tendrá que modificar estos métodos un tanto para que funcionen para tres caracteres.

No debería ser tan difícil ...

Answer 17

Un algoritmo de agnóstico del lenguaje sería el siguiente:

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

Este algoritmo también tiene en cuenta si Excel se actualiza nuevamente para manejar más de 16k columnas. Si realmente quería ir por la borda, podría pasar un valor adicional y reemplazar las instancias de 26 con otro número para acomodar alfabetos alternativos

Answer 18

Una vez escribí esta función para realizar esa tarea exacta:

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());
}

Answer 19

Y aquí hay una conversión de la versión de VBScript a 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

Answer 20

IF(COLUMN()>=26,CHAR(ROUND(COLUMN()/26,1)+64)&CHAR(MOD(COLUMN(),26)+64),CHAR(COLUMN()+64))

Esto funciona con columnas de 2 letras (hasta la columna ZZ ). Tendría que anidar otra instrucción if para columnas de 3 letras.

La fórmula anterior falla en las columnas AY , AZ y en cada una de las siguientes columnas nY y nZ . La fórmula corregida es:

=IF(COLUMN()>26,CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64)&CHAR(MOD((COLUMN()-1),26)+65),CHAR(COLUMN()+64)