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

Функция СТОЛБЕЦ в Excel и полезные примеры ее использования

Функция СТОЛБЕЦ в Excel возвращает номер столбца на листе по заданным условиям. Синтаксис элементарный: всего один аргумент. Но с ее помощью можно эффективно решать разнообразные задачи.

Описание и синтаксис функции

Функция с параметром: = СТОЛБЕЦ (С3) возвращает значение 3, т.к. (C) является третьим по счету.

Аргумент «ссылка» необязательный. Это может быть ячейка или диапазон, для которого нужно получить номер столбца.

Аргумент – ссылка на ячейку:

Функция выдала номер колонки для этой ячейки.

Функция вернула номер столбца, в котором находится.

Аргумент – вертикальный диапазон ячеек:

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

Аргумент – горизонтальный диапазон ячеек:

Функция СТОЛБЕЦ вернула номер крайнего левого столбца (А) в указанном диапазоне. Если выделить формулу в строке формул и нажать кнопку F9, то программа выдаст все номера столбцов заданного диапазона.

Но при нажатии кнопки Enter в ячейке с формулой отобразится только номер крайнего левого столбца.

Чтобы на листе появились номера всех столбцов диапазона, который является аргументом функции СТОЛБЕЦ, нужно использовать формулу массива. Выделяем такое количество ячеек, сколько элементов входит в горизонтальный диапазон. Вводим формулу и нажимаем сочетание кнопок Ctrl + Shift + Enter.

Аргумент – ссылка на горизонтальный массив:

Формула вернула номера столбцов в виде горизонтального массива.

В качестве аргумента нельзя применять ссылки на несколько областей.

Полезные примеры функции СТОЛБЕЦ в Excel

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

Например, рассчитаем значение выражения 1 + ½ + 1/3. Используем формулу: =СУММПРОИЗВ(1/СТОЛБЕЦ(A2:C2)).

Выполним более сложные манипуляции с числовым рядом: найдем сумму значений от 1 до 1/n^3, где n = 6. Формула расчета: =СУММПРОИЗВ(1/СТОЛБЕЦ(A9:F9)^3).

Чаще всего данную функцию используют совместно с функцией ВПР. Задача первой функции – указать номер столбца возвращаемых значений. Такое совмещение удобно при работе с огромными таблицами. Например, пользователь помещает возвращаемые данные в табличку с такой же, как в исходной таблице, последовательностью столбцов. Причем обе таблицы достаточно широкие.

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

Аргументы функции ВПР: искомое значение, массив данных для анализа, номер столбца, интервальный просмотр (точный или приблизительный поиск). Сам номер можно задать с помощью такой формулы: =ВПР(8;A1:C10;СТОЛБЕЦ(C1);ИСТИНА).

При работе с широкими таблицами можно просто копировать функцию ВПР по горизонтали. В этом случае номера столбцов автоматически пересчитываются – табличка заполняется.

Нужна корректировка номера– прибавляем или отнимаем определенную цифру или рассчитанное с помощью какой-либо функции значение. Например,

Функция СТОЛБЕЦ должна вычесть 1 из номера колонки C. Поэтому функция ВПР возвращает значение не из третьего, а из второго столбца девятой строки.

Теперь проиллюстрируем, как работает многоразовое копирование без необходимости в ручной правке. Сначала в формуле закрепим ссылки на таблицу (кнопка F4). Скопируем формулу ВПР поперек столбцов – номер меняется автоматически (функция СТОЛБЕЦ сдвигается вместе с другими ссылками).

Достаточно элегантное решение, позволяющее править формулы в автоматическом режиме.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Excel works!

Номер строки и столбца в Excel может пригодиться в разных ситуациях. Функции могут использоваться для создания нумерации строк списка или таблицы, их можно использовать и в формулах, и в условном форматировании. Так же разберем как быстро посчитать количество столбцов без формул.

Функция СТРОКА() и СТОЛБЕЦ(). Как использовать?

Интересная особенность этих формул, что они могут работать как с реквизитами, так и без них. Т.е. формула =СТРОКА() будет возвращать номер именно этой строки, а =СТРОКА(A3) будет возвращать номер строки ячейки A3, соответственно 3.

Подробнее на примере

Номер строки и столбца для списка или таблицы

Довольно часто функция СТРОКА используется для нумерации данных в списке, достаточно только сопоставить номер строки и номер позиции списка, в данном случае делаем -1

Очень удобно, если вы часто изменяете порядок строки, удаляете или добавляете их. Поскольку формула остается, то порядок всегда будет правильным.

Тоже самое можно провернуть для нумерации столбцов.

Как посчитать количество столбцов в выделенном диапазоне столбцов?

Если вы выделяете не диапазон ячеек, а диапазон, к примеру столбцов, необязательно пользоваться какой-то формулой, чтобы посчитать сколько столбцов в диапазоне. При выделение диапазона, количество столбцов (еще раз обращу внимание, если вы выделяете именно диапазон столбцов) автоматически считается и показывается. Буква С означает, что это считаются колонки -Colomn

Это очень удобно, когда вы создаете формулу с функцией ВПР .

Соответственно, если вы выделяете диапазон строк, то будет отображаться число строк.

Функция СТРОКА() в условном форматировании. Как сделать зебру в таблице — чередование цветов заливки?

Условным форматировании можно раскрасить заливку ячеек и даже целых таблиц. Удобно для чтения, когда каждая строка таблица выделяется чередующимся цветом — т.н. зеброй. Как раз функция СТРОКА() нам и поможет.

Задайте в окне формул условного форматирования:

=НЕЧЁТ(СТРОКА())=СТРОКА()

И получилось удобное оформление

Я довольно часто пользуюсь этим, т.к. не очень люблю форматирование в виде Таблицы.

Похожие статьи

• 03.06.2017Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифрыPosted in Формулы, Оформление
• 16.10.2017Как правильно объединить ячейки в Excel?Posted in Прочее, Формулы, Оформление
• 15.10.2015Циклические ссылки в ExcelPosted in Формулы
• 26.01.2017Как добавить фигуру в Excel? Применение автофигурPosted in Оформление
• 15.07.2016Представления Excel. Сохранить вид анализаPosted in Оформление
• 26.09.2017Как убрать надпись страница в Excel? Страничный режимPosted in Оформление, Работа с файлом
• 20.02.2018Как правильно и быстро очистить ячейки в Excel?Posted in Работа с текстом, Оформление, Работа с файлом
• 01.02.2019Формула ЗНАЧЕН в ExcelPosted in Формулы

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

Добрый день. Подскажите пожалуйста. Есть таблица с колонками. В первой ячейке название колонки. На другом листе выпадающий список с названиями. Как определить номер колонки таблицы по значению выпадающего списка макросом?

Пользователь
Сообщений: 2735 Регистрация: 25.12.2012
07.10.2014 15:53:35

такое чувство, что можно и без макроса обойтись, но вопрос задан так, что без поллитры не обойтись. Приложите пример чтоли

Учимся сами и помогаем другим.
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 15:56:48
ber$erk, прав насчёт примера. Николай Малыгин, без файла на пальцах объяснять затруднительно.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
07.10.2014 16:07:47

Цитата
Николай Малыгин пишет: Как определить номер колонки таблицы по значению выпадающего списка макросом?

если вып.список содержит названия «колонок», то определить номер по порядку можно без макроса, при помощи функции =ПОИСКПОЗ(выпавшая_колонка;список_колонок;0).
Наверное, список_колонок — это диапазон первой строки таблицы на первом листе.
а выпавшая_колонка — это значение ячейки с выпадающим списком
а в макросе это можно точно так же сделать при помощи Application.WorksheetFunction.Match

F1 творит чудеса
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 16:45:56

Пример прилагаю. Может есть и другое решение. Я хотел по значению из списка определить номер колонки в базе и использовать в функции VLOOKUP. Но при попытке в формуле использовать вместо номера колонки значение переменной ничего не получилось.

Прикрепленные файлы

• Пример.xlsm (17.47 КБ)

Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 16:51:30
=ВПР($A2;База!$1:$1048576;ПОИСКПОЗ($C$1;База!$1:$1;0);0)
в яч. C2 и протянуть вниз.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 16:55:31
А макросом возможно?
Пользователь
Сообщений: 5088 Регистрация: 22.12.2012
07.10.2014 17:00:00

как вариант вместо вашей формулы прописать новую. и судя по коду придется его (код) повесить на событие изменения ячейки С1 чтобы автоматом отрабатывал.

Изменено: V — 07.10.2014 17:01:42
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 17:00:19

Цитата
Николай Малыгин пишет: при попытке в формуле использовать вместо номера колонки значение переменной ничего не получилось

Не понимаю зачем макрос, если используете формулу.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 17:03:56
На листе «Счет» нет формул и не надо их там. База скрыта и закрыта.
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 17:12:28
Попробуйте воспользоваться Find.

MsgBox Sheets("База").Rows(1).Find(what:=Range("Счет!C1")).Column

Изменено: JayBhagavan — 07.10.2014 17:15:07

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 17:53:32

Уже хорошо, спасибо. Но как ввести это значение переменной в формулу в макросе в качестве номера столбца?. Что то ни фига не выходит

Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
07.10.2014 17:58:52

.Value = "=VLOOKUP(A2," & iAddress$ & ", " & Sheets("База").Rows(1).Find(what:=Range("Счет!C1")).Column & " ,0)"

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 148 Регистрация: 05.03.2013
07.10.2014 18:14:22
Отлично. Про кавычки я забыл. Всем спасибо
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
07.10.2014 18:21:54
Только лучше эту процедуру повесить на Worksheet_Change по изменению ячейки C1

Sub ВПР() With Sheets("База") iAddress$ = .Range(.Range("A2"), .Range("A65536") _ .End(xlUp)).Resize(, 6).Address(External:=True) On Error Resume Next n = Application.WorksheetFunction.Match(Sheets("Счет").Range("C1").Value, .Rows(1), 0) If Err <> 0 Then Exit Sub On Error GoTo 0 End With With Sheets("Счет") With .Range(.Range("A2"), .Range("A65536").End(xlUp)).Offset(, 2) .Value = "=VLOOKUP(A2," & iAddress$ & "," & n & ",0)" .Value = .Value .Replace What:="#N/A", Replacement:=0 End With End With End Sub

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

Столбцы в таблице нумеруются буквыми так: A, B, C, . Y, Z, AA, AB, AC, . и т.д.

Задача оптимальным способом по номеру столбца получить его букву. Например:

На любом языке программирования.

целое от деления на 25, потом остаток от деления
через кейс определить буквы
Это называется перевод в другую систему счисления

Ну. Кто первый догадается?

а.. то есть тут призы?
(0)
системы счисления на информатике в школе проходят.
Вам на подростковый форум.
Система счисления с переменной базой — младший разряд 26, а остальные — 27.
В цикле заполнить соответствие.
(6) ТС просто скосячил, в екселе нумерация с 1 а не с 0, так что никакой переменной базы

http://catalog.mista.ru/public/544232/
Можно получить индекс зная смещение символа 64 относительно 1 и 26 разрядную систему

Или можно заполнить колонки

Функция ЗаписатьКолонки(Колонки,НачСтр,Разряд,КоличествоРазрядов,Сравнивать,ПоследняяКолонка)
// Процедура создает колонки которые меньше или равны имени последней колоки
// A,B. AA..ABC
Для сч=КодСимвола(«A») по КодСимвола(«Z») Цикл
НовСтр=НачСтр+Символ(сч);

Если Разряд рез= ЗаписатьКолонки(Колонки,НовСтр,Разряд+1,КоличествоРазрядов,Сравнивать,ПоследняяКолонка);
Если Сравнивать и Рез Тогда
возврат истина
КонецЕсли;
Иначе
Колонки.Добавить(НовСтр,ОписаниеСтроки());

Если Сравнивать и НовСтр=ПоследняяКолонка Тогда
возврат истина
КонецЕсли
КонецЕсли;
КонецЦикла;

возврат ложь;
КонецФункции

// Создадим колонки учитывая разряды
// Например если имя последней колоки ABC то колонки идут по разрядно
//A..Z
//AA..ZZ
//AAA..ABC
КоличествоРазрядов=СтрДлина(ПоследняяКолонка);

Для сч=1 По КоличествоРазрядов Цикл
Сравнивать=сч=КоличествоРазрядов;
рез= ЗаписатьКолонки(Колонки,»»,1,сч,Сравнивать,ПоследняяКолонка);
Если Сравнивать и рез Тогда
возврат;
КонецЕсли;
КонецЦикла;
КонецПроцедуры

Пока правильного ответа нет. Если не считать (10), так как он громоздкий.
лТЗн.Колонки.Получить(Индекс).Имя?

private string GetExcelColumnName(int columnNumber)
<
int dividend = columnNumber;
string columnName = String.Empty;
int modulo;

while (dividend > 0)
<
modulo = (dividend — 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend — modulo) / 26);
>

(13) Нагуглил. И все равно громоздко, хотя и правильно. Но у меня короче получилось
А переключит нумерацию столбов в экселе почему не хочешь?
Зная что есть ограничение на кол-во столбцов в екселе можно в одну строчку записать, но науя?
(0)А зачем? есксель и цифры прекрасно понимает.Да они наглядне
(16) Ограничение столбцов в последних версиях Excel — 16384. Точно одной строкой получится?
(18) угу всего то максимум 3 буковки

Возвращает номер столбца по заданной ссылке.

Ссылка — это ячейка или интервал ячеек, для которых определяется номер столбца.

Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ.

Если ссылка является интервалом ячеек, и если функция СТОЛБЕЦ введена как горизонтальный массив, то функция СТОЛБЕЦ возвращает номера столбцов в ссылке в виде горизонтального массива.

Ссылка не может ссылаться на несколько областей.

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Создайте пустую книгу или лист.
Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца.
Выделение примера в справке.

Выделение примера в справке.

Нажмите сочетание клавиш CTRL+C
На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.

1
2
3
A B
Формула Описание (результат)
=СТОЛБЕЦ() Столбец в котором отображается формула (1)
=СТОЛБЕЦ(A10) Столбец ссылки (1)