Excel показать диапазон всего столбца

Обновлено: 04.07.2024

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

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

Так кто же прав?

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

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

СУММ(А:А)
ПОИСКПОЗ(99,A:A,0)
СУММПРОИЗВ(А:А)
СЧЁТЕСЛИ(A:A;99)
(массив введен в каждую ячейку)
(один массив формул введен в D1:D100)
IF(A:A=1,1,0) (массив не введен)

Все тестовые примеры имеют ячейку A1 со значением 1, а формула вводится в D1:D1000.

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

Тестовый пример 1. Базовый вариант: на листе нет других данных или форматирования.

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

Форматирование тестовых случаев:

Тестовый пример 2 – весь столбец A окрашен в желтый цвет.
Тестовый пример 3. Ячейки A2000:A1048576 окрашены в желтый цвет.
Тестовый пример 4. Ячейка XFD1048576 пуста, но окрашена в желтый цвет.
Тестовый пример 5: каждая вторая строка в столбце A окрашена в желтый цвет.

Ячейки с тестовыми примерами данных:

Тестовый пример 6. Ячейка A1048576 содержит 98
Тестовый пример 7. Ячейки A2000:A1048576 содержат 1.
Тестовый пример 8 – каждая вторая строка в столбце A содержит 1

Время расчета указано в миллисекундах для медианы трех однопоточных значений Range. Вычисляется по формулам 1000 в формате D1:D1000. Время обновления экрана исключено.

Результаты тестирования

Интерпретация результатов теста

1. Базовый вариант: без дополнительного форматирования или данных

Самая эффективная формула – =ЕСЛИ(A:A=0,1,0).
Эта формула использует неявный метод пересечения Excel, поэтому каждая ячейка, содержащая формулу в D1:D1000, рассматривает только одну ячейку в D1:D1000. та же строка в столбце A.

Формулы СУММ, ПОИСКПОЗ и СЧЁТЕСЛИ эффективны, поскольку каждая формула рассматривает только 1000 ячеек в столбце А, ограниченных используемым диапазоном (строка 1000).

Формула СУММПРОИЗВ и формула массива с одной ячейкой =ЕСЛИ(А:А=0,1,0) заставляют Excel просматривать каждую из миллионов ячеек в столбце А, поэтому просматривается 1000*10000000 ячеек. в. Формула ЕСЛИ работает медленнее, чем СУММПРОИЗВ, потому что она должна создавать дополнительные виртуальные столбцы.

Версия IF с массивом из нескольких ячеек представляет собой единый массив формул, введенный в 1000 ячеек в столбце D. Эта единственная формула рассматривает 1 000 000 ячеек и затем возвращает 1000 результатов. Поскольку он просматривает в 1000 раз меньше ячеек, он работает намного быстрее.

2. Столбец A: форматированный желтый

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

3. Отформатировать более миллиона ячеек из A2000:A1048576 Yellow

Форматирование большого, но непрерывного блока ячеек увеличивает используемый диапазон, если адрес начальной ячейки выходит за пределы предыдущей последней использованной ячейки. Но (на удивление) он не расширяет используемый диапазон до последней отформатированной строки!
Результаты синхронизации показывают небольшое увеличение для тех формул, которые зависят от используемого диапазона, и размер файла немного увеличивается.

4. Отформатируйте последнюю ячейку на листе (XFD1048576) в желтый цвет.

Это значительно расширяет используемый диапазон: последняя использованная строка теперь является последней строкой на листе.
Результаты синхронизации, которые зависят от размера используемого диапазона, значительно увеличиваются, но размер файла не меняется.

Результаты будут такими же, если вы закрасите всю строку 1048576 или только одну ячейку A1048576.

5. Отформатируйте каждую вторую строку в столбце A желтым цветом.

Это создает гораздо более сложный используемый диапазон, даже несмотря на то, что последняя использованная строка фактически на 1 меньше, чем в тестовом примере 4. Excel должен хранить информацию о форматировании для каждой из полумиллиона строк!
Все тайминги значительно увеличиваются (не только формулы, которые зависят от используемого диапазона) из-за дополнительной работы, необходимой для обхода гораздо большей таблицы ячеек, и размер файла резко увеличивается.

6. Установите для последней ячейки в столбце A значение 98.

Это расширяет используемый диапазон так же, как форматирование последней ячейки, а время и размер файла практически идентичны тестовому примеру 4.

7. Задайте для ячеек A2000:A1048576 значение 1.

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

Но время, затрачиваемое формулами, зависящими от используемого диапазона, увеличивается примерно в 1000 раз, и даже время для СУММПРОИЗВ и формул массива примерно удваивается. Размер файла также значительно увеличивается.

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

8. Установите для каждой второй ячейки в столбце A значение 1.

В результате количество ячеек, содержащих данные, примерно в два раза меньше, чем в тестовом примере 7, но в последней использованной строке всего на 1 меньше.
Время для формул, зависящих от используемого диапазона, увеличивается примерно в 2 раза по сравнению с тестовым набором 5, в котором форматируются только те же ячейки, но уменьшается примерно в 2 раза по сравнению с тестовым набором 7 (в котором было примерно удвоить количество ячеек, содержащих данные). Размер файла не намного больше, чем тестовый пример 5.

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

Выводы

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

Работа с Excel означает работу с ячейками и диапазонами в строках и столбцах в нем.

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

Как и в большинстве случаев в Excel, в Excel существует несколько способов выбора столбца или строки.

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

Я также покажу вам, как это сделать, когда вы работаете с таблицей Excel или сводной таблицей.

Итак, приступим!

Это руководство охватывает:

Выбрать весь столбец/строку с помощью сочетания клавиш

Предположим, у вас есть набор данных, как показано ниже, и вы хотите выбрать весь столбец (скажем, столбец C).

Первое, что нужно сделать, это выбрать любую ячейку в столбце C.

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

Удерживайте клавишу Control, а затем нажмите клавишу пробела на клавиатуре

Если вы используете Excel на Mac, используйте COMMAND + ПРОБЕЛ

Приведенный выше ярлык мгновенно выделяет весь столбец (как вы увидите, он будет выделен серым цветом, что указывает на то, что он выбран)

Тот же ярлык можно использовать и для выбора нескольких смежных столбцов. Например, предположим, что вы хотите выбрать оба столбца C и D.

Для этого выберите две соседние ячейки (одну в столбце C и одну в столбце D), а затем используйте ту же комбинацию клавиш.

Выбор всей строки

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

Удерживайте клавишу Shift, а затем нажмите клавишу пробела.

Вы снова увидите, что он выбран и выделен серым цветом.

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

Выделить весь столбец (или несколько столбцов) с помощью мыши

У меня такое чувство, что вы, возможно, уже знаете этот метод, но позвольте мне все же рассказать о нем (он будет коротким).

Выберите один столбец (или строку)

Если вы хотите выделить весь столбец (скажем, столбец D), наведите курсор на заголовки столбцов (где написано D). Вы заметите, что курсор изменится на черную стрелку, указывающую вниз.

Теперь нажмите левую клавишу мыши.

При этом будет выделен весь столбец D.

Аналогичным образом, если вы хотите выделить всю строку, нажмите на номер строки (в заголовке строки слева)

Выбрать несколько смежных столбцов (или строк)

Предположим, вы хотите выбрать несколько столбцов, расположенных рядом друг с другом (скажем, столбцы D, E и F)

Для этого выполните следующие действия:

Поместите курсор в самый левый заголовок столбца D.
Нажмите и удерживайте левую клавишу мыши
Удерживая нажатой левую клавишу, перетащите мышь, чтобы также охватить столбцы E и F.

Вышеуказанные шаги автоматически выберут все столбцы между первым и последним выбранным столбцом.

Точно так же вы можете выбрать несколько смежных строк.

Выбрать несколько несмежных столбцов (или строк)

Это наиболее распространенный сценарий, когда вам нужно выбрать несколько столбцов, которые не находятся рядом друг с другом (скажем, столбцы D и F).

Ниже приведены шаги для этого:

Поместите курсор на заголовок одного из столбцов (в данном случае — на столбец D)
Нажмите левую клавишу мыши, чтобы выбрать столбец.
Нажмите и удерживайте клавишу Control
Удерживая нажатой клавишу Control, выберите все остальные столбцы, которые хотите выделить.

То же самое можно сделать и со строками.

Выберите весь столбец (или несколько столбцов) с помощью поля имени

Используйте этот метод, если хотите:

Выберите несколько смежных или несмежных строк/столбцов

Поле имени — это небольшое поле слева от строки формул.

Хотя основная цель поля имени – быстро назвать ячейку или диапазон ячеек, вы также можете использовать его для быстрого выбора любого столбца (или строки).

Например, если вы хотите выделить весь столбец D, введите в поле имени следующее и нажмите Enter:

Аналогичным образом, если вы хотите выбрать несколько столбцов (скажем, D, E и F), введите в поле имени следующее:

И это не так!

Если вы хотите выбрать несколько несмежных столбцов, например D, H и I, введите следующее:

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

Трюк с именованным диапазоном

Позвольте мне также показать вам еще один замечательный трюк.

Предположим, вы работаете с рабочей книгой, и вам часто приходится выбирать удаленные столбцы (скажем, столбцы B, D и G).

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

После создания вы можете просто ввести имя именованного диапазона в поле "Имя" (или выбрать его из раскрывающегося списка)

Ниже приведены шаги по созданию именованного диапазона для определенных столбцов:

Выберите столбцы, для которых вы хотите создать именованный диапазон (удерживая клавишу Control, выберите столбцы один за другим)
Введите имя, которое вы хотите дать выбору, в поле имени (в имени не допускаются пробелы). В этом примере я буду использовать имя SalesData

После этого вы создали именованный диапазон в Excel, который теперь ссылается на выбранные вами столбцы (B, D и G в моем примере).

А теперь пришло время волшебства.

Если вы хотите быстро выбрать столбцы B, D и G, просто введите имя в поле «Имя» и нажмите «Ввод» (или нажмите на небольшой раскрывающийся значок в конце поля имени и выберите имя из списка).

Вуаля, все столбцы будут выбраны.

Этот метод удобен, если вам нужно несколько раз выбрать одни и те же столбцы на одном листе.

Эту технику можно использовать для выбора как строк, так и разных диапазонов. Например, если вы хотите выбрать два отдельных диапазона в Excel, просто выполните те же действия (вместо выбранных столбцов выберите диапазоны и дайте им имя).

Выбрать столбец в таблице Excel

При работе с таблицами Excel иногда может потребоваться выделить всю строку или столбец в таблице.

Это означает, что вы хотите выбрать не весь столбец на листе, а весь столбец таблицы.

Вот как это сделать:

Поместите курсор на заголовок таблицы Excel (обратите внимание, что это заголовок столбца в таблице Excel, а не тот, который отображает букву столбца)
Вы заметите, что курсор изменится на черную стрелку, указывающую вниз.

Вышеуказанные шаги позволят выбрать весь столбец в таблице Excel (а не весь столбец).

Если вы хотите выбрать несколько столбцов, удерживайте клавишу Control и повторите процесс для всех столбцов, которые вы хотите выбрать.

Выбрать столбец в сводной таблице

Как и в таблице Excel, в сводной таблице можно быстро выделить всю строку или столбец.

Предположим, у вас есть сводная таблица, как показано ниже, и вы хотите выбрать столбцы «Продажи»,

Ниже приведены шаги для этого:

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

Эти шаги позволят выбрать столбец "Продажи". Точно так же, если вы хотите выбрать несколько столбцов, удерживайте клавишу Control, а затем сделайте выбор.

Итак, вот некоторые из распространенных способов выделения всего столбца или всей строки в Excel.

Excel поддерживает ссылки как на полные столбцы, так и на полные строки. Полная ссылка на столбец — это ссылка на весь столбец. Например, чтобы просуммировать все значения в столбце А, вы можете использовать функцию СУММ следующим образом:

Обратите внимание, что полная ссылка на столбец вводится так же, как и другие диапазоны, с двоеточием (:), отделяющим начальную точку от конечной точки. Поскольку в полной ссылке на столбец нет номеров строк, буквальный перевод диапазона A: A - это «каждая ячейка в столбце A». Как и другие диапазоны, полная ссылка на столбец может включать несколько столбцов. Например, чтобы сослаться на столбцы от A до D:

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

Здесь B:B — это диапазон, а C:C — это sum_range.

За и против

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

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

Это сложная тема, поскольку она зависит также от того, что Excel считает «используемым диапазоном». Кроме того, Excel всегда развивается с упором на повышение производительности, поэтому разные версии Excel могут вести себя по-разному. Чарльз Уильямс, эксперт по производительности Excel, опубликовал здесь некоторые результаты тестирования.

Лично я избегаю полных ссылок на столбцы по старой привычке, но вы будете сталкиваться с ними во многих бизнес-средах. Если ваша цель — создать динамический диапазон, хорошим выбором будет таблица Excel. Если вам нужна дополнительная гибкость, вы можете создать динамический именованный диапазон.

Чтобы вернуть весь столбец, вам нужны функции возврата массива, такие как INDEX или OFFSET. Обе эти функции могут возвращать массивы, а также отдельные значения, которые можно использовать в других функциях, таких как СУММ, СРЗНАЧ или даже в другом ИНДЕКСЕ или СМЕЩЕНИИ. В статье Как вернуть столбец целиком объясняется, как это можно сделать.

Синтаксис

=ИНДЕКС(данные без заголовков, 0, ПОИСКПОЗ(значение поиска, заголовки данных, 0))

=OFFSET(точка привязки перед заголовками, 1, MATCH(значение поиска, заголовки данных, 0), ROWS(любой столбец данных или даже сами данные), 1)

Шаги

Начните с =ИНДЕКС(, который возвращает диапазон
Введите или выберите диапазон, включающий данные C3:E7,
Продолжайте с 0, чтобы указать, что вам нужен весь столбец.
Используйте ПОИСКПОЗ(, чтобы найти расположение нужного столбца
Выберите диапазон, который включает значение, определяющее столбец H3,
Выберите диапазон, включающий значение, включающее заголовки C2:E2,
Введите 0)) для выбора метода точного соответствия и закрытия формул ИНДЕКС и ПОИСКПОЗ.

Как упоминалось выше, чтобы вернуть весь столбец или строку, вам нужно использовать функции возврата массива. Альтернативным способом может быть использование функций массива. Однако в большинстве случаев они довольно сложны, не нужны (из-за существующих формул) и работают медленнее.

Индекс и смещение — это мощные функции, которые возвращают значение или диапазон в сочетании с функцией ПОИСКПОЗ, которая может обрабатывать процесс поиска. Давайте посмотрим, как они работают:

ИНДЕКС

Хитрость заключается в использовании "0" в качестве аргумента строки, который указывает, что вы хотите использовать весь столбец в данных, а не одну ячейку. Остальное, как это делается с обычным INDEX-MATCH. Поэтому используйте функцию ПОИСКПОЗ, чтобы найти расположение столбца:

Функция ИНДЕКС вернет массив, содержащий значения в этом столбце.

Что отображает Excel: 71

Обратите внимание, что Excel покажет только первое значение массива, если вы не введете их в другую формулу. Мы использовали функцию СРЗНАЧ, чтобы продемонстрировать, как можно использовать массив.

СМЕЩЕНИЕ

Функция OFFSET также использует аналогичный подход. Вместо ИНДЕКС, который вам нужен для выбора всего диапазона данных, функция СМЕЩ требует ячейку за пределами диапазона данных, которую можно использовать в качестве точки привязки.

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

Кратко; вам нужно указать, где начинается столбец и какова его длина. Функция ПОИСКПОЗ работает так же, как и раньше, для поиска нужного столбца, а функция СТРОКИ возвращает длину столбца. Конечно, мы использовали «1» в качестве номера строки, потому что нам нужен только один столбец.

Читайте также: