Выбор целого столбца, кроме первых X (заголовочных) ячеек в Excel

Я знаю, что могу выбрать все ячейки в определённом столбце, щёлкнув по заголовку столбца (например, A или AB). Но можно ли исключить несколько ячеек, например, заголовки таблицы данных?

Пример

Я хотел бы выбрать ячейки данных в определённом столбце, чтобы установить проверку данных (которая в конечном итоге отобразит выпадающий список значений, определённых в именованном диапазоне). Но я не хочу, чтобы в этот выбор включались заголовки данных (так что в них не будут отображаться эти выпадающие списки и они не будут проверяться). Что если я позже решу изменить настройки проверки для этих ячеек?

Как же тогда выбрать свой столбец?

Замечание

Я знаю, что могу установить проверку данных на весь столбец, а затем выбрать только те ячейки, которые хочу исключить, и очистить их проверку данных. Что мне бы хотелось знать, так это возможно ли сделать правильный выбор на первом шаге, чтобы избежать второго.

Я пытался щёлкнуть по заголовку столбца, чтобы выбрать весь столбец, а затем удерживать CTRL и щёлкать по тем ячейкам, которые не хочу включать в свой выбор, но это не сработало, как ожидалось.

Щёлкните по первой ячейке, которую хотите выбрать, а затем нажмите Ctrl + Shift + ↓, чтобы выбрать блок непустых ячеек или блок пустых ячеек (включая первую непустую ячейку ниже), вниз. 
Нажмите ещё раз для расширения выделения на дополнительные блоки.

Это может привести к тому, что верхняя часть рабочего листа прокрутится с экрана. 
Нажмите Ctrl + Backspace, чтобы быстро прокрутиться обратно вверх.

Универсальный

=SUM(A2:INDEX(A:A, ROWS(A:A)))

или

=SUM(A:A) – A1

Оба варианта вернут сумму для “столбца A”, за исключением заголовка в A1

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

Вы можете ввести F5, чтобы вызвать поле имени. Затем введите B3:B65536 и нажмите ОК. 65536 для Excel 2003 и более ранних версий, 1048576 для 2007 и 2010.

Если вы хотите исключить заголовок, который я предполагаю находится в первой строке, вы можете сделать следующее:

A2:A

Например:

=SUM(A2:A)

что будет включать A2 и всё остальное после этого в столбце A.

Надеюсь, этот ответ поможет вам.

Правка: я сделал это в Google таблицах, и это не работает в Excel.

Вы можете использовать функцию Ctrl Shift, и вместо того, чтобы использовать ↓ для прокрутки до конца, просто нажмите End, а затем ↓ один раз, и вы окажетесь внизу листа, вместо того чтобы удерживать ↓ навсегда.

Вы также можете использовать функцию F5 следующим образом: нажмите Ctrl + G, затем введите номер ячейки, с которой начинается ваш диапазон, например: B5. Это приведёт вас к этой ячейке. Как только она будет выделена, снова нажмите Ctrl + G и введите любой номер ячейки в диапазоне, который вы хотите выделить, например: B7024, и ПЕРЕД тем, как нажать Enter, удерживайте Shift, и этот точный диапазон будет выделен.

Или вы можете просто исключить заголовки при выполнении проверки данных. Также, чтобы выбрать все ячейки, если вы перейдёте в A1 и нажмёте Ctrl A один раз. Это выберет только ячейки с данными, что хорошо, чтобы избежать пустых ячеек.

Ответ Марии выше (который был выбран) работает, но я чувствую, что он недостаточен, так как вам приходится идти до самого низа листа – затем вам нужно использовать другой ярлык, чтобы снова Центрировать экран вверху листа. Вот моё решение (тестировалось только в Excel 2013):

Я пытался щёлкнуть по заголовку столбца, чтобы выделить весь столбец, а затем удерживать CTRL и щёлкать по тем ячейкам, которые не хочу включать в свой выбор, но это не работало, как ожидалось.

После щелчка по заголовку столбца и выделения всего столбца удерживайте Shift, а затем нажмите Tab, ↓, ↓

Время между нажатием Tab и первым нажатием ↓ должно быть коротким, иначе вы начнете перемещаться от низа экрана вверх, но не одновременно, иначе вам придётся сделать больше действий, чтобы это сработало.

Если вы затем продолжите удерживать кнопку Shift, то сейчас сможете начать удалять ячейки из вашего списка выделенных ячеек сверху вниз, используя ↓.

Некоторые из ответов здесь хорошие, а некоторые неверные… Если вам нужен более сложный, но более гибкий ответ, вот моё решение.

Например, чтобы начать с третьей строки столбца C, вы можете использовать это

=SUM(OFFSET(C3,0,0,LOOKUP(2,1/(NOT(ISBLANK(C:C))),ROW(C:C))))

Где C3 – это первая ячейка, которая будет учитываться, а оба C:C из последней части ссылаются на столбец, который вы хотите указать. Первое 0 – это если вы хотите сдвинуть первую (начальную) ячейку на другую строку, а второе 0 – это сдвинуть начальную ячейку в другой столбец. Например, если вы всё ещё хотите использовать C3, но хотите более общий пример, вы можете использовать это (начальная ячейка A1 + 2 строки + 2 столбца = C3):

=SUM(OFFSET(A1,2,2,LOOKUP(2,1/(NOT(ISBLANK(C:C))),ROW(C:C))))

Успех!…

Альтернативный метод выбора всего столбца с исключением верхней ячейки – использовать OFFSET для сдвига диапазона вниз на одну ячейку. Однако важно предотвратить переполнение диапазона формулы за пределы листа. К счастью, OFFSET включает возможность задать не только сдвиг, но и длину диапазона:

используйте OFFSET(A:A,1,0,COUNTA(A:A)-1)

например, SUM(OFFSET(A:A,1,0,COUNTA(A:A)-1))

Конечно, этот подход также будет работать для пропуска более чем одной строки сверху:

например, SUM(OFFSET(A:A,3,0,COUNTA(A:A)-3))

Напишите скрипт VBA, который можно (де)активировать с помощью ярлыка.

Подробности о скрипте VBA:

• Скрипт VBA понимает, когда выбран полный столбец, и добавляет последний маленький шаг по удалению первых x ячеек.

• Или скрипт VBA назначает имена необходимым адресам всех заполненных столбцов, так что простая буква столбца в поле имени выберет то, что вам нужно, см. @Peachy, или вы просто создаете адрес автоматически, когда щелкаете по столбцу, а затем нажимаете клавишу.

• Вы также можете настроить скрипт VBA, чтобы отметить всё ниже линии замороженного окна.

• Скорее всего, есть много других вариантов, используя ответы от сюда в скриптах VBA.

В настоящее время, если вы пытаетесь выбрать столбец для формулы, это так же просто, как использовать термин A2:A или что-то подобное.

В общем, это можно использовать для выбора одного или нескольких столбцов, исключая какое-то пространство заголовка вверху, включая одну из верхних ячеек угла, за которой следует двоеточие, затем столбец на другом конце диапазона (это будет один и тот же столбец, если вы пытаетесь выбрать только один). Например, если я хочу среднее значение столбца C, но не хочу включать две ячейки заголовка вверху, я бы использовал =AVERAGE(C3:C), потому что C3 – это верхняя ячейка, которую я хочу выбрать. Если я хотел бы сумму столбцов B-E с третьей строки вниз, я бы использовал =SUM(B3:E).

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

• Выберите заголовочное поле, которое вы хотите исключить из правила проверки данных
• Из меню выберите Данные -> Проверка данных -> Проверка данных
• Нажмите кнопку “Очистить все”
• Нажмите кнопку ОК

Теперь ваше заголовочное поле не будет иметь проверки данных, которая применима к остальным полям столбца

Щелкните правой кнопкой мыши и выберите Скрыть на строках, которые вы не хотите выделять, а затем выберите целые столбцы.