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

Обновлено: 30.06.2024

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

Скачать книгу

Вы можете скачать бесплатную практическую рабочую тетрадь Excel отсюда.

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

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

1. Формула для преобразования номера столбца в букву в Excel

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

Шаги:

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

B5 = номер ссылки на ячейку, содержащий номер столбца для преобразования в букву

Вы получите соответствующий буквенный адрес (A) номера столбца (1) в вашем наборе данных.

Теперь перетащите строку вниз с помощью маркера заполнения, чтобы применить формулу к остальным ячейкам и преобразовать их в буквы.

Разбивка формулы:

АДРЕС(1,B5,4)
- Вывод: A1
- Объяснение:Функция АДРЕС возвращает адрес ячейки на основе заданной строки и столбца. Мы предоставили строку номер 1 и номер столбца из B5 для построения адреса, а чтобы получить относительную ссылку, мы установили 4 для аргумента abs_num.
  - abs_num = 4 — постоянное значение. Вы должны установить значение 4, иначе адрес ячейки будет отображаться со знаком $.
  - ЗАМЕНИТЬ(АДРЕС(1,B5,4)",1″,"") -> становится
    - ЗАМЕНИТЬ(A1, "1", "")
    - Вывод: А
    - Объяснение. Функция ПОДСТАВИТЬ заменяет 1 ничем ("") из A1, поэтому возвращает A.
    2. VBA для преобразования номера столбца в букву в Excel
    
    Шаги по преобразованию номера столбца в букву в Excel с помощью VBA приведены ниже.
    
    Мы будем использовать определяемую пользователем функцию (UDF) для преобразования числа.
    
    Шаги:
    - Нажмите Alt + F11 на клавиатуре или перейдите на вкладку "Разработчик" -> Visual Basic, чтобы открыть редактор Visual Basic.
    - Во всплывающем окне кода в строке меню выберите Вставить -> Модуль.
    - Скопируйте следующий код и вставьте его в окно кода.
    Это не подпроцедура для запуска программы VBA, это создание определяемой пользователем функции (UDF). Таким образом, после написания кода вместо нажатия кнопки «Выполнить» в строке меню нажмите «Сохранить».
    - Теперь вернитесь к интересующему вас рабочему листу и напишите функцию, которую вы только что создали с помощью кода VBA (функция NumToLetter в первой строке кода), а в скобках функции NumToLetter передайте номер ссылки на ячейку, которую вы хотите преобразовать в букву (в нашем случае мы передаем ячейку B5 в круглых скобках).
    Итак, наша окончательная формула относится,
    
    =NumToLetter(B5)
    
    Вы получите соответствующий буквенный адрес (A) номера столбца (1) в вашем наборе данных.
    - Теперь перетащите строку вниз с помощью указателя заполнения, чтобы применить определяемую пользователем функцию к остальным ячейкам и преобразовать их в буквы.
    3. Встроенная функция Excel для изменения номера столбца на букву
    
    Excel имеет встроенную функцию изменения номера столбца (показанного ниже на рисунке) на букву.
    
    Шаги:
    - Во всплывающем окне Excel выберите «Формулы» -> снимите флажок «Стиль ссылки R1C1» -> «ОК».
    Ваши столбцы теперь будут иметь буквенные адреса вместо цифр.
    
    Заключение
    
    В этой статье показано, как преобразовать номер столбца в букву в Excel тремя различными способами. Я надеюсь, что эта статья была очень полезна для вас. Не стесняйтесь спрашивать, если у вас есть какие-либо вопросы по теме.
    
    При построении сложных формул в Excel иногда может потребоваться получить букву столбца определенной ячейки или заданного числа. Это можно сделать двумя способами: с помощью встроенных функций или пользовательских.
    
    Как преобразовать номер столбца в алфавит (однобуквенные столбцы)
    
    Если имя столбца состоит из одной буквы от A до Z, его можно получить с помощью этой простой формулы:
    
    Например, чтобы преобразовать число 10 в букву столбца, используйте следующую формулу:
    
    Также можно ввести число в какую-либо ячейку и ссылаться на эту ячейку в формуле:
    
    =СИМВОЛ(64 + A2)
    
    Как работает эта формула:
    
    Функция CHAR возвращает символ на основе кода символа в наборе ASCII. Значения ASCII заглавных букв английского алфавита — от 65 (A) до 90 (Z). Итак, чтобы получить код заглавной буквы A, вы добавляете 1 к 64; чтобы получить код символа в верхнем регистре B, вы добавляете 2 к 64 и т. д.
    
    Как преобразовать номер столбца Excel в букву (любой столбец)
    
    Если вы ищете универсальную формулу, которая работает для любого столбца в Excel (1 буква, 2 буквы и 3 буквы), вам нужно использовать немного более сложный синтаксис:
    
    С буквой столбца в A2 формула принимает следующий вид:
    
    =ПОДСТАВИТЬ(АДРЕС(1, A2, 4), "1", "")
    
    Как работает эта формула:
    
    Во-первых, вы создаете адрес ячейки с номером интересующего столбца. Для этого предоставьте функции АДРЕС следующие аргументы:
    - 1 для номер_строки (номер строки не имеет особого значения, поэтому вы можете использовать любой).
    - A2 (ячейка, содержащая номер столбца) для номер_столбца.
    - 4 для аргумента abs_num, чтобы вернуть относительную ссылку.
    С указанными выше параметрами функция АДРЕС возвращает в качестве результата текстовую строку "A1".
    
    Поскольку нам нужна только буква столбца, мы удаляем номер строки с помощью функции ПОДСТАВИТЬ, которая ищет «1» (или любой другой номер строки, который вы жестко запрограммировали в функции АДРЕС) в тексте «А1» и заменяет это с пустой строкой ("").
    
    Получить букву столбца из номера столбца с помощью пользовательской функции Пользовательская функция
    
    Если вам нужно регулярно преобразовывать номера столбцов в алфавитные символы, пользовательская определяемая пользователем функция (UDF) может значительно сэкономить ваше время.
    
    Код функции довольно прост и понятен:
    
    Здесь мы используем свойство Cells для ссылки на ячейку в строке 1 и указанный номер столбца, а также свойство Address для возврата строки, содержащей абсолютную ссылку на эту ячейку (например, $A$1). Затем функция Split разбивает возвращаемую строку на отдельные элементы, используя знак $ в качестве разделителя, и мы возвращаем элемент (1), который является буквой столбца.
    
    Вставьте код в редактор VBA, и ваша новая функция ColumnLetter готова к использованию. Подробные инструкции см. в разделе Как вставить код VBA в Excel.
    
    С точки зрения конечного пользователя синтаксис функции очень прост:
    
    Где col_num – это номер столбца, который вы хотите преобразовать в букву.
    
    Ваша настоящая формула может выглядеть следующим образом:
    
    И он вернет те же результаты, что и встроенные функции Excel, описанные в предыдущем примере:
    
    Как получить букву столбца определенной ячейки
    
    Чтобы определить букву столбца конкретной ячейки, используйте функцию СТОЛБЦ, чтобы получить номер столбца, и передайте этот номер функции АДРЕС. Полная формула примет следующий вид:
    
    В качестве примера найдем букву столбца ячейки C5:
    
    =ПОДСТАВИТЬ(АДРЕС(1, СТОЛБЦ(C5), 4), "1", "")
    
    Очевидно, что результат "C" :)
    
    Как получить букву столбца текущей ячейки
    
    Для определения буквы текущей ячейки используется формула почти такая же, как в приведенном выше примере. Единственное отличие состоит в том, что функция 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", наша формула подвергается следующему преобразованию:
    
    И создает строку C3:C8.
    
    Чтобы преобразовать текстовую строку в действительную ссылку, которую может понять Excel, вложите приведенную выше формулу в функцию ДВССЫЛ, а затем передайте ее третьему аргументу функции 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. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
    
    Чтобы преобразовать номер столбца в букву, мы будем использовать функции ADDRESS и SUBSTITUTE.
    
    Приведенная выше формула возвращает букву столбца на позицию числа в указанной ячейке.
    
    Давайте рассмотрим приведенную выше формулу.
    
    Функция АДРЕС
    
    Во-первых, мы будем использовать функцию ADDRESS для возврата адреса ячейки на основе заданной строки и столбца.
    
    Важно! Вы должны установить для ввода [abs_num] значение 4, иначе адрес ячейки будет отображаться со знаком $.
    
    Функция ПОДСТАВИТЬ
    
    Затем мы используем функцию ПОДСТАВИТЬ, чтобы заменить номер строки «3» пустой строкой текста («»), оставив только букву столбца.
    
    Объединение функций приводит к исходной формуле, которая преобразует номера столбцов в буквы.
    
    Обратите внимание, что в качестве номера строки в функции АДРЕС можно использовать любое число, если вы введете тот же номер строки в функцию ПОДСТАВИТЬ.
    
    Преобразование номера столбца в букву в Google Таблицах
    
    Эти формулы работают точно так же в Google Таблицах, как и в Excel.
    
    Практический лист Excel
    
    Вы также можете написать эту формулу следующим образом:
    
    Вам просто нужно ввести номер столбца непосредственно в формулу.
    
    Функция CHAR() получает букву на основе ее номера, определенного набором символов вашего компьютера; однако вам не нужно знать это, чтобы использовать эту функцию.
    
    Использование COLUMN()
    
    Если вы создаете динамическую формулу, вам может понадобиться автоматически определить букву определенного столбца для использования в другой функции, например в функции ДВССЫЛ().
    
    В этом случае вы можете использовать функцию COLUMN(), чтобы получить номер определенного столбца или текущего столбца:
    
    Это позволит получить букву столбца для ячейки A1, то есть A.
    
    Удалите ссылку на ячейку из функции COLUMN(), и вы получите букву текущего столбца.
    
    Помните, что это будет работать только для столбцов от A до Z, а в некоторых случаях это может не работать в зависимости от локализованных настроек компьютера. Следующий пример не имеет этих ограничений.
    
    Более сложная формула — работает для всех столбцов
    
    Этот пример более универсальный, поскольку работает со всеми столбцами в Excel, но немного сложнее.
    
    A3 — это пример ячейки, содержащей номер столбца, для которого мы хотим получить букву (буквы).
    
    Эта формула использует функции ПОДСТАВИТЬ() и АДРЕС() для получения букв столбца. Это изящный маленький трюк, который позволяет вам получить буквы для любого столбца, используя только число в качестве ссылки для него.
    
    Функция АДРЕС() дает нам адрес ячейки на основе номера столбца и строки, а функция ПОДСТАВИТЬ() заменяет числовую часть ссылки на ячейку ничем, что удаляет номер. Если вы хотите увидеть это в Excel, просто разделите эти две функции на отдельные ячейки и посмотрите на результаты:
    
    Использование COLUMN()
    
    (это почти то же самое, что и раздел COLUMN() выше, только с новой формулой)
    
    Если вы создаете динамическую формулу, вам может понадобиться автоматически определить букву определенного столбца для использования в другой функции, например в функции ДВССЫЛ().
    
    В этом случае вы можете использовать функцию COLUMN(), чтобы получить номер определенного столбца или текущего столбца:
    
    Это позволит получить букву столбца для ссылки на ячейку A1, то есть A.
    
    Вы также можете удалить ссылку на ячейку из функции COLUMN(), чтобы получить букву (буквы) для столбца, в котором в данный момент находится формула.
    
    Это вернет D, поскольку формула находится в столбце D.
    
    Эти последние два примера могут показаться тривиальными, но они очень полезны, когда вы копируете формулу в ячейку и столбец, которые неизвестны, так как вы не знаете, в каком столбце будет формула, когда она используется или скопировано.
    
    Примечания
    
    Это мощная вещь, которую можно сделать в Excel, получить букву (буквы) столбца из их номера. Вы не будете использовать это каждый день, и это может не помочь базовым пользователям Excel, но для всех вас, кто хочет или должен поднять Excel на следующий уровень, сохраните это руководство, особенно для второй формулы, той, которая использует ПОДСТАВИТЬ () и АДРЕС().
    
    Обязательно загрузите прилагаемую книгу, чтобы работать с этими примерами в Excel.
    
    Вопрос? Спросите об этом на нашем форуме Excel
    
    Курс Excel VBA — от новичка до эксперта
    
    200+ видеоуроков 50+ часов обучения 200+ руководств Excel
    
    С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)
    
    Курс VBA — от новичка до эксперта
    
    Преобразование названия месяца в число и наоборот в Excel
    Учебное пособие: как преобразовать название месяца, например, январь, в число с помощью формулы в Excel; др.
    
    Функции ROMAN() и ARABIC() в Excel
    Учебное пособие: преобразование римских цифр в обычные числа (арабские), а также как преобразовать обычные числа.
    
    5 советов по оценке сложных формул в Excel
    Учебник : 5 простых советов по оценке любой сложной формулы в Excel! Сочетание этих советов поможет вам в этом.
    
    Активация рабочего листа или переход к нему с помощью макросов VBA в Excel.
    Учебное пособие. Сделайте определенный рабочий лист видимым с помощью макроса в Excel. Это называется активацией wo.
    
    Возврат минимального или максимального значения с помощью поиска в Excel — ПОИСКПОЗ ИНДЕКС
    Учебное пособие. Найдите минимальное или максимальное значение в диапазоне и на его основе верните значение из другого диапазона.
    
    Получить день недели (от 1 до 7) для даты в Excel — ДЕНЬ НЕД
    Учебник: Используйте функцию в Excel, чтобы получить номер дня в неделе от 1 до 7. Это позволяет .
    
    Подпишитесь на еженедельные уроки
    
    БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!
    
    Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!
    
    (Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)
    
    Курс Excel VBA — от новичка до эксперта
    
    200+ видеоуроков
    50+ часов видео
    200+ руководств по Excel
    
    С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel.(Опыт работы с VBA не требуется.)
    
    Читайте также: