Как преобразовать букву столбца в число в 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, который называется Ручка заполнения. При этом курсор изменит свой вид на толстый черный крест.
- Перетащите маркер заполнения вправо до нужного столбца.
В результате вы сохраните метки столбцов в виде букв, а под буквами у вас будут номера столбцов.
Кончик. Чтобы номера столбцов оставались в поле зрения при прокрутке к нижним областям рабочего листа, вы можете зафиксировать верхнюю строку.
Вот как вернуть номера столбцов в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Номер столбца Excel — примеры (файл .xlsm)
