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

Question

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

Как я могу определить, что я хочу, чтобы диапазон начинался с, скажем, B2 и заканчивался в последней ячейке с текстом в столбце B?

Answer 1

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

Поэтому я могу только посоветовать вам, как создавать автоматические (динамические) диапазоны:

1

Используйте таблицу - таблицы Excel невероятно мощны, и автоматически считаются диапазонами, если вы хотите обратиться к каждой ячейке в столбце, просто введите [ columnname ], а не традиционную ссылку на ячейку. Таблицы Excel автоматически расширяются при добавлении новых строк, что очень удобно.

Недостаток таблиц возвращает нас к вопросу о том, что представляет собой этот шаблон и что он должен делать: если предполагается, что доступ к шаблону будет осуществляться пользователями Excel 2003 или более ранних таблиц, не будет работать.

2

Что подводит меня к варианту 2: динамические именованные диапазоны.

• Выберите столбец, который вы хотите установить с автоматическим диапазоном, нажав в первой ячейке данных (обычно строка 2) и перейдите в Формулы> Определить имя> Определить имя


• Дайте имени ясное имя, например, ColumnNameRange


• оставьте Scope как Workbook и добавьте комментарий, если хотите


• В Относится к: введите эту формулу (при необходимости заменяя ссылки на ячейки и листы)

=OFFSET(SheetName!$A$2,0,0,COUNTA(SheetName!$A$2:$A$500),)

Этот именованный диапазон Dyanmic, при обращении к нему в формуле, будет возвращать все из диапазона, указанного в смещении, во вторую половину диапазона, указанного в COUNTA. Формула просматривает весь диапазон в СЧЕТЕ, чтобы получить количество непустых ячеек, а затем создает выбор диапазона смещения от первой ячейки до количества найденных ячеек.

Больше информации о динамических именованных диапазонах здесь: http://www.ozgrid.com/Excel/DynamicRanges.htm
Более подробная информация о таблицах здесь: http://www.ozgrid.com/Excel/data-tables.htm

Несколько вещей, о которых следует помнить: динамические именованные диапазоны RE:

• Если вы установите для второй части COUNTA значение 500, как указано выше, а затем получите более 500 строк данных, это не будет учитывать дополнительные данные, так что имейте это в виду


• Если в данных, которые просматривает COUNTA, есть пробелы, то отсчет будет только до первого пробела. Чтобы избежать этого, всегда устанавливайте COUNTA в первый столбец (который должен быть вашим столбцом id, поэтому не должно иметь пробелов), а затем установите ячейку, на которую ссылается OFFSET, на нужный вам столбец (поэтому для столбца D установите COUNTA в $ A $ 2: $ 500 $ и СМЕЩЕНИЕ до $ 2 $


• Всегда исправляйте все диапазоны ячеек с помощью $ (F4), иначе это не будет работать должным образом. Все диапазоны ячеек должны выглядеть следующим образом: $ column $ row


• Вы можете создать динамический именованный диапазон, который охватывает коллекцию столбцов, добавив еще одну COUNTA после первой: = OFFSET ($ A $ 1,0,0, COUNTA ($ A1: $ A500), COUNTA ($ B1: $ B500)) , Вы можете сделать это с таблицами, указав некоторые столбцы: = Table1 [[Column1]: [Column2]] или указав всю таблицу: = Table1

Answer 2

Вот макрос, который будет делать выбор

Public Sub SelectRange()

Cells(2, 2).Select
Range(Selection, Selection.End(xlDown)).Select

*do stuff*

End Sub

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

Если вы сообщите нам формулу, мы можем добавить ее в VBA. Если вам нужен ввод пользователя до того, как формула будет запущена, вы можете добавить ее к кнопке или какому-либо другому действию.