Как преобразовать номер столбца 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”, “”)
Как работает эта формула:
Во-первых, вы создаете адрес ячейки с номером интересующего столбца. Для этого предоставьте следующие аргументы функции 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 простых шага.
- Преобразование номера столбца в букву
С номером столбца в H3 используйте уже знакомую формулу, чтобы изменить его на буквенный символ:
=ПОДСТАВИТЬ(АДРЕС(1, H3, 4), “1”, “”)
Кончик. Если число в вашем наборе данных не соответствует номеру столбца, обязательно внесите необходимые исправления. Например, если бы у нас были данные за неделю 1 в столбце B, данные за неделю 2 в столбце C и т. д., мы бы использовали H3+1, чтобы получить правильный номер столбца.
- Создайте строку, представляющую ссылку на диапазон
Чтобы создать ссылку на диапазон в виде строки, вы объединяете букву столбца, возвращаемую приведенной выше формулой, с номерами первой и последней строки. В нашем случае ячейки данных находятся в строках с 3 по 8, поэтому мы используем эту формулу:
= ПОДСТАВИТЬ (АДРЕС (1, H3, 4), “1”, “”) & “3:” & ПОДСТАВИТЬ (АДРЕС (1, H3, 4), “1”, “”) & “8”
Учитывая, что H3 содержит «3», которая преобразуется в «C», наша формула претерпевает следующее преобразование:
=”С”&”3:”&”С”&”8″
И выдает строку C3:C8.
- Сделать ссылку на динамический диапазон
Чтобы преобразовать текстовую строку в допустимую ссылку, которую может понять Excel, вложите приведенную выше формулу в функцию ДВССЫЛ, а затем передайте ее в 3-й аргумент XLOOKUP:
=XLOOKUP(H2, E3:E8, ДВССЫЛ(H4), “Не найдено”)
Чтобы избавиться от дополнительной ячейки, содержащей строку возвращаемого диапазона, вы можете поместить формулу ПОДСТАВИТЬ АДРЕС в саму функцию ДВССЫЛ:
=XLOOKUP(H2, E3:E8, НЕПРЯМОЙ(ЗАМЕНИТЬ(АДРЕС(1, H3, 4), “1”, “”) & “3:” & ПОДСТАВИТЬ(АДРЕС(1, H3, 4), “1”, “”) & “8”), “Не найдено”)
С нашим заказом Функция ColumnLetterможно получить более компактное и изящное решение:
=XLOOKUP(H2, E3:E8, INDIRECT(Буква столбца(H3) & “3:” & Буква столбца(H3) & “8”), “Не найдено”)
Вот как найти букву столбца из числа в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Номер столбца Excel в букву — примеры (файл .xlsm)
