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

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

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

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

Чтобы преобразовать букву столбца в номер столбца в Excel, вы можете использовать эту общую формулу:

СТОЛБЦ(КОСВЕННЫЙ(письмо&”1″))

Например, чтобы получить номер столбца F, формула выглядит так:

=СТОЛБЦ(ДВССЫЛ(“F”&”1”))

А вот как вы можете идентифицировать номера столбцов по буквам, введенным в предопределенные ячейки (в нашем случае от A2 до A7):

=СТОЛБЦ(ДВССЫЛ(A2&”1″))

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

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

Сначала вы создаете текстовую строку, представляющую ссылку на ячейку. Для этого вы объединяете букву и цифру 1. Затем вы передаете строку функции ДВССЫЛ, чтобы преобразовать ее в реальную ссылку Excel. Наконец, вы передаете ссылку функции COLUMN, чтобы получить номер столбца.

Как преобразовать букву столбца в число (энергонезависимая формула)

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

СООТВЕТСТВИЕ(письмо&”1″, АДРЕС(1, СТОЛБЦ($1:$1), 4), 0)

Это отлично работает в динамическом массиве Excel (365 и 2021). В более старой версии вам нужно ввести его как формулу массива (Ctrl + Shift + Enter), чтобы заставить его работать.

Например:

=ПОИСКПОЗ(A2&”1″, АДРЕС(1, СТОЛБЦ($1:$1), 4), 0)

Или вы можете использовать эту формулу без массива во всех версиях Excel:

=ПОИСКПОЗ(A2&”1″, ИНДЕКС(АДРЕС(1, ИНДЕКС(СТОЛБЦ($1:$1), ), 4), ), 0)

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

Во-первых, вы объединяете букву в A2 и номер строки «1», чтобы создать стандартную ссылку на стиль «A1». В этом примере у нас есть буква «A» в A2, поэтому результирующая строка — «A1».

Затем вы получаете массив строк, представляющих все адреса ячеек в первой строке, от «A1» до «XFD1». Для этого вы используете функцию COLUMN($1:$1), которая генерирует последовательность номеров столбцов и передает этот массив в номер_столбца аргумент функции АДРЕС:

АДРЕС(1, {1,2,3,4,5,…, 16384), 4)

При условии row_num (1-й аргумент) установлен в 1 и abs_num (3-й аргумент) имеет значение 4 (это означает, что вам нужна относительная ссылка), функция ADDRESS предоставляет этот массив:

{“A1″,”B1″,”C1″,”D1″,…,”XFD1”}

Наконец, вы создаете формулу ПОИСКПОЗ, которая ищет конкатенированную строку в приведенном выше массиве и возвращает позицию найденного значения, которая соответствует номеру столбца, который вы ищете:

ПОИСКПОЗ(“A1”, {“A1″,”B1″,”C1″,”D1″,…,”XFD1”}, 0)

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

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

В полном соответствии с изложенным выше принципом код функции настолько прост, насколько это возможно:

Публичная функция ColumnNumber(col_letter As String) As Long ColumnNumber = Columns(col_letter).Конечная функция столбца

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

Функция требует только один аргумент, col_letterкоторая представляет собой букву столбца, которую нужно преобразовать в число:

ColumnLetter (col_letter)

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

=Номерстолбца(A2)

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

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

Чтобы получить номер столбца конкретной ячейки, просто используйте функцию COLUMN:

СТОЛБЕЦ(сотовый_адрес)

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

=СТОЛБЦ(B3)

Очевидно, что результат равен 2.

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

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

=СТОЛБЦА()

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

По умолчанию Excel использует стиль ссылок A1 и помечает заголовки столбцов буквами, а строки — цифрами. Чтобы столбцы были помечены числами, измените стиль ссылок по умолчанию с A1 на R1C1. Вот как:

В Excel нажмите Файл > Опции.
в Параметры Excel диалоговое окно, выберите Формулы на левой панели.
Под Работа с формуламиустановите флажок Стиль ссылок R1C1 и щелкните ХОРОШО.

Метки столбцов сразу изменятся с букв на цифры:

Обратите внимание, что выбор этой опции изменит не только метки столбцов — адреса ячеек также изменятся со ссылками A1 на R1C1, где R означает «строка», а C означает «столбец». Например, R1C1 относится к ячейке в столбце 1 строки 1, что соответствует ссылке A1. R2C3 относится к ячейке в столбце 3 строки 2, что соответствует ссылке C2.

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

Кончик. Чтобы вернуться к стилю A1, снимите флажок Эталонный стиль R1C1 флажок в Параметры Excel.

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

Если вы не привыкли к стилю ссылок R1C1 и хотите сохранить ссылки A1 в своих формулах, вы можете вставить числа в первую строку нашего рабочего листа, чтобы у вас были как буквы столбца, так и числа. Это легко сделать с помощью функции автозаполнения.

Вот подробные шаги:

В A1 введите номер 1.
В B1 введите номер 2.
Выберите ячейки A1 и B1.
Наведите курсор на маленький квадратик в правом нижнем углу ячейки B1, который называется Ручка заполнения. При этом курсор изменит свой вид на толстый черный крест.
Перетащите маркер заполнения вправо до нужного столбца.