Как преобразовать номер столбца Excel в букву

В этом уроке мы рассмотрим, как изменить номера столбцов Excel на соответствующие буквенные символы.

При построении сложных формул в Excel иногда может потребоваться получить букву столбца определенной ячейки или из заданного числа. Это можно сделать двумя способами: используя встроенные функции или пользовательскую.

Как преобразовать номер столбца в алфавит (однобуквенные столбцы)

Если имя столбца состоит из одной буквы от A до Z, вы можете получить его, используя эту простую формулу:

СИМВОЛ(64 + col_number)

Например, чтобы преобразовать число 10 в букву столбца, используйте следующую формулу:

=СИМВОЛ(64 + 10)

Также можно ввести число в какую-либо ячейку и ссылаться на эту ячейку в формуле:

=СИМВОЛ(64 + A2)
Простая формула для изменения номера столбца на алфавит

Как работает эта формула:

Функция CHAR возвращает символ на основе кода символа в ASCII установлен. Значения ASCII заглавных букв английского алфавита — от 65 (A) до 90 (Z). Итак, чтобы получить код заглавной буквы A, вы добавляете 1 к 64; чтобы получить код символа верхнего регистра B, вы добавляете 2 к 64 и так далее.

Как преобразовать номер столбца Excel в букву (любой столбец)

Если вы ищете универсальную формулу, которая работает для любого столбца в Excel (1 буква, 2 буквы и 3 буквы), вам нужно использовать немного более сложный синтаксис:

ПОДСТАВИТЬ(АДРЕС(1, col_number4), «1», «»)

С буквой столбца в A2 формула принимает следующий вид:

=ПОДСТАВИТЬ(АДРЕС(1, A2, 4), «1», «»)
Преобразование номера столбца Excel в букву.

Как работает эта формула:

Во-первых, вы создаете адрес ячейки с номером интересующего столбца. Для этого предоставьте следующие аргументы функции ADDRESS:

  • 1 для row_num (номер строки особого значения не имеет, поэтому можно использовать любой).
  • A2 (ячейка, содержащая номер столбца) для номер_столбца.
  • 4 для abs_num аргумент для возврата относительной ссылки.

С указанными выше параметрами функция АДРЕС возвращает в качестве результата текстовую строку «A1».

Поскольку нам нужна только буква столбца, мы удаляем номер строки с помощью функции ПОДСТАВИТЬ, которая ищет «1» (или любой другой номер строки, который вы жестко запрограммировали внутри функции АДРЕС) в тексте «А1» и заменяет его на пустой строки («»).

Получить букву столбца из номера столбца с помощью пользовательской функции Пользовательская функция

Если вам нужно регулярно преобразовывать номера столбцов в алфавитные символы, пользовательская пользовательская функция (UDF) может значительно сэкономить ваше время.

Код функции довольно прост и понятен:

Публичная функция ColumnLetter(col_num) ColumnLetter = Split(Cells(1, col_num).Address, «$»)(1) End Function

Здесь мы используем Клетки свойство для ссылки на ячейку в строке 1 и указанный номер столбца и Адрес для возврата строки, содержащей абсолютную ссылку на эту ячейку (например, $A$1). Затем функция Split разбивает возвращаемую строку на отдельные элементы, используя знак $ в качестве разделителя, и мы возвращаем элемент (1), который является буквой столбца.

Вставьте код в редактор VBA, и ваш новый КолонкаПисьмо функция готова к использованию. Подробное руководство см. в разделе Как вставить код VBA в Excel.

С точки зрения конечного пользователя синтаксис функции так же прост:

Буква столбца (номер_столбца)

Где номер_столбца это номер столбца, который вы хотите преобразовать в букву.

Ваша реальная формула может выглядеть следующим образом:

= Буква столбца (A2)

И он вернет точно такие же результаты, как собственные функции Excel, рассмотренные в предыдущем примере:
Пользовательская функция для изменения номера столбца на букву

Как получить букву столбца определенной ячейки

Чтобы определить букву столбца конкретной ячейки, используйте функцию СТОЛБЦ, чтобы получить номер столбца, и передайте этот номер функции АДРЕС. Полная формула примет следующий вид:

ПОДСТАВИТЬ(АДРЕС(1, СТОЛБЦ(сотовый_адрес), 4), «1», «»)

В качестве примера найдем букву столбца ячейки C5:

=ПОДСТАВИТЬ(АДРЕС(1, СТОЛБЦ(C5), 4), «1», «»)

Очевидно, результат «С» 🙂
Получение буквы столбца определенной ячейки

Как получить букву столбца текущей ячейки

Для отработки буквы текущей ячейки формула почти такая же, как в приведенном выше примере. Единственное отличие состоит в том, что функция COLUMN() используется с пустым аргументом для ссылки на ячейку, в которой находится формула:

=ПОДСТАВИТЬ(АДРЕС(1, СТОЛБЦ(), 4), «1», «»)
Получение буквы столбца текущей ячейки

Как создать ссылку динамического диапазона из номера столбца

Надеюсь, предыдущие примеры дали вам новые темы для размышлений, но вас могут интересовать практические применения.

В этом примере мы покажем вам, как использовать формулу «цифра столбца в букву» для решения реальных задач. В частности, мы создадим динамическую формулу XLOOKUP, которая будет извлекать значения из определенного столбца на основе его номера.

Предположим, что вы хотите получить показатель прибыли для данного проекта (H2) и недели (H3).
Источник данных

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

Для удобства разобьем весь процесс на 3 простых шага.

  1. Преобразование номера столбца в букву

    С номером столбца в H3 используйте уже знакомую формулу, чтобы изменить его на буквенный символ:

    =ПОДСТАВИТЬ(АДРЕС(1, H3, 4), «1», «»)
    Преобразование номера столбца в букву

    Кончик. Если число в вашем наборе данных не соответствует номеру столбца, обязательно внесите необходимые исправления. Например, если бы у нас были данные за неделю 1 в столбце B, данные за неделю 2 в столбце C и т. д., мы бы использовали H3+1, чтобы получить правильный номер столбца.

  2. Создайте строку, представляющую ссылку на диапазон

    Чтобы создать ссылку на диапазон в виде строки, вы объединяете букву столбца, возвращаемую приведенной выше формулой, с номерами первой и последней строки. В нашем случае ячейки данных находятся в строках с 3 по 8, поэтому мы используем эту формулу:

    = ПОДСТАВИТЬ (АДРЕС (1, H3, 4), «1», «») & «3:» & ПОДСТАВИТЬ (АДРЕС (1, H3, 4), «1», «») & «8»

    Учитывая, что H3 содержит «3», которая преобразуется в «C», наша формула претерпевает следующее преобразование:

    =»С»&»3:»&»С»&»8″

    И выдает строку C3:C8.
    Получение ссылки на диапазон в виде строки

  3. Сделать ссылку на динамический диапазон

    Чтобы преобразовать текстовую строку в допустимую ссылку, которую может понять Excel, вложите приведенную выше формулу в функцию ДВССЫЛ, а затем передайте ее в 3-й аргумент XLOOKUP:

    =XLOOKUP(H2, E3:E8, ДВССЫЛ(H4), «Не найдено»)
    Создание эталона динамического диапазона

    Чтобы избавиться от дополнительной ячейки, содержащей строку возвращаемого диапазона, вы можете поместить формулу ПОДСТАВИТЬ АДРЕС в саму функцию ДВССЫЛ:

    =XLOOKUP(H2, E3:E8, НЕПРЯМОЙ(ЗАМЕНИТЬ(АДРЕС(1, H3, 4), «1», «») & «3:» & ПОДСТАВИТЬ(АДРЕС(1, H3, 4), «1», «») & «8»), «Не найдено»)
    Динамическая формула XLOOKUP для извлечения значений из столбца, указанного числом

С нашим заказом Функция ColumnLetterможно получить более компактное и изящное решение:

=XLOOKUP(H2, E3:E8, INDIRECT(Буква столбца(H3) & «3:» & Буква столбца(H3) & «8»), «Не найдено»)

Вот как найти букву столбца из числа в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Номер столбца Excel в букву — примеры (файл .xlsm)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *