Как очистить столбец в excel
Рассмотрим наиболее распространенные вопросы, касающиеся столбцов Excel, на примере Excel 2007, а также познакомимся с надстройкой (макросом), автоматизирующей удаление и скрытие столбцов Excel по заданному условию.
С чем имеем дело?
Рабочая книга (Workbook) – это файл Excel, содержащий рабочие листы. Рабочий лист (Worksheet) - это электронная таблица, которая используется приложением Excel для хранения и обработки данных. Рабочий лист состоит из ячеек. Ячейка – это основной элемент электронной таблицы. Именно ячейки являются носителями информации и содержат текст, значения, примечания, формулы и т.д. Каждая ячейка имеет свой адрес – номер строки и номер столбца, на пересечении которых эта ячейка находится. Ячейки организованы в столбцы и строки и являются частью рабочей книги.
Сколько столбцов в рабочем листе?
Количество столбцов в Excel варьируется в зависимости от версии, так в версии Excel 2003 столбцов 256, а в версиях 2007 и 2010 уже 16 384. Сумму значений столбца можно узнать, выделив столбец и посмотрев параметр «Сумма выделенных ячеек» в строке состояния. Строка состояния находится внизу окна, под горизонтальной полосой прокрутки.
Состав строки состояния может быть настроен на усмотрение пользователя, для этого необходимо кликнуть правой кнопкой мыши на свободном месте строки состояния. Ниже приведен набор параметров, выводить которые в строке состояния можно путем установки флажка перед соответствующим параметром.
Как выделить столбец? Как выделить несколько столбцов подряд? Как выделить несколько столбцов выборочно?
Выделить столбец можно однократным кликом левой кнопкой мыши по имени столбца либо нажатием сочетания клавиш Ctrl+Spase (клавиша пробел), либо поместить курсор (маркер выделения) в самую первую ячейку нужного столбца и нажать сочетание клавиш Ctrl+Shift+Стрелка вниз. Несколько столбцов подряд можно выделить, проведя курсором по названиям столбцов с нажатой левой кнопкой мыши, либо выделить первый столбец нужного диапазона столбцов, а затем при нажатой клавише Shift выделить последний столбец диапазона столбцов. Выделить несколько столбцов выборочно, можно щелкая курсором по именам нужных столбцов при нажатой клавише Ctrl.
Как изменить стиль заголовков столбцов?
В Excel столбцы буквами обозначаются по умолчанию, но есть еще способ обозначения столбцов цифрами. При этом способе адрес выделенной ячейки выглядит не как А1, а как R1C1, где R1 (сокращение слова Row – строка) обозначает номер строки, а C1 (сокращение слова Column – колонка) обозначает номер столбца. Изменить способ отображения заголовков столбцов можно в меню «Параметры Excel» после нажатия кнопки «Office». В разделе «Формулы» в группе «Работа с формулами» необходимо поставить флажок перед полем «Стиль ссылок R1C1».
Как закрепить столбцы на листе? Как снять закрепление столбцов на листе?
При работе с таблицами, которые в ширину выходят за видимые пределы экрана для удобства ввода и просмотра данных можно закрепить нужные столбцы. Для этого необходимо выделить столбец, следующий за нужным, перейти во вкладку «Вид», в группу «Окно», нажать кнопку «Закрепить области» и в выпадающем списке команд выбрать пункт «Закрепить области». Для закрепления первого столбца можно не выделяя столбца выбрать пункт «Закрепить первый столбец» в меню кнопки «Закрепить области».
Для закрепления следующих столбцов нужно снять предыдущее закрепление, если оно имеется. Для этого надо зайти на все ту же вкладку «Вид» и в группе «Окно» раскрыть меню кнопки «Закрепить области» после чего выбрать пункт «Снять закрепление областей».
Как изменить ширину столбца? Как задать автоподбор ширины столбца? Как задать фиксированную ширину столбцов? Как задать ширину столбцов по умолчанию?
Ширину столбцов в Excel можно изменять как в сторону увеличения, так и в сторону уменьшения. Для изменения ширины столбца подводим курсор мыши к границе двух соседних столбцов и в тот момент, когда курсор станет двухсторонней горизонтальной стрелкой, нажимаем левую кнопку мыши и перемещаем границу между столбцами в нужном направлении. При этом если было выделено несколько столбцов, то ширина изменилась бы у каждого из них. Автоподбор ширины столбца можно задать на вкладке «Главная», в группе «Ячейки», меню кнопки «Формат», выбрав пункт «Автоподбор ширины столбца».
Автоподбор ширины столбца можно также задать двойным кликом левой кнопки мыши на правой границе столбца возле его имени. При этом если выделены несколько столбцов, то автоподбор применится ко всем столбцам выделенной группы. Ширина столбца по умолчанию зафиксирована в 8,43 символа при размере букв 10 пунктов, но фиксированную ширину можно задать своим собственным значением, для этого выделяем нужные столбцы, на выделенном диапазоне кликаем правой кнопкой мыши и в контекстном меню выбираем пункт «Ширина столбца…». В появившемся окне «Ширина столбца» устанавливаем нужную нам ширину. Для того чтобы задать произвольную ширину столбца по умолчанию, заходим на вкладку «Главная», выбираем группу «Ячейки», раскрываем меню кнопки «Формат» и выбираем пункт «Ширина по умолчанию», где задаем свое значение, после чего нажимаем кнопку «ОК».
Как транспонировать значения столбцов в строки?
Без особого труда можно преобразовать Excel столбец в строку и наоборот. Такое преобразование называется транспонированием. Для преобразования столбца в строку необходимо скопировать данные столбца, после чего нажать правую кнопку мыши и из контекстного меню выбрать пункт «Специальная вставка…». В появившемся диалоговом окне «Специальная вставка» поставить галочку в поле «транспонировать» и нажать кнопку «ОК». Еще один способ - зайти на вкладку "Главная", в группу "Буфер обмена", открыть меню кнопки "Вставить" и выбрать из него пункт "Транспонировать".
Как добавить новый столбец в таблицу? Как вставить скопированный столбец?
Вставить новый столбец в таблицу можно, кликнув правой кнопкой мыши на имени столбца, левее которого надо поместить новый столбец и выбрать в контекстном меню пункт «Вставить». Аналогичное действие можно осуществить через главное меню Excel. В окне открытого листа выделите столбец, левее которого надо поместить новый столбец, после этого зайдите на вкладку «Главная», в группу «Ячейки», раскройте меню кнопки «Вставить» и выберите в списке пункт «Вставить столбцы на лист». При вставке новых столбцов, общее количество столбцов на листе остается неизменным.
При вставке скопированных столбцов предусмотрены разные режимы вставки, так например, при осуществлении вставки в контекстном меню есть три пункта, относящиеся к вставке – «Вставить», «Специальная вставка» и «Вставить скопированные ячейки». При выборе пункта «Вставить», данные вставляются вместо или поверх тех данных, которые находились в столбце, куда производится вставка. При выборе же пункта «Вставить скопированные ячейки» данные вставляются рядом, старые значение столбца сохраняются, но смещаются при этом в сторону. Описанные выше способы вставки, сделают полные копии ячеек в столбцах, включая текст, форматы, формулы и примечания. При выборе пункта «Специальная вставка» можно произвести частичное копирование, выбрав из предложенного списка только нужный параметр.
Как очистить столбцы от содержимого? Как скрыть и отобразить столбцы? Как удалить столбцы?
Для очистки столбцов от содержимого нужно выделить нужный диапазон и нажать клавишу Delete на клавиатуре либо кликнуть правой кнопкой мыши на выделенном диапазоне и выбрать из контекстного меню пункт «Очистить содержимое». Очистить столбцы от содержимого можно также на вкладке «Главная» в группе «Редактирование», меню кнопки «Очистить».
Через меню кнопки «Очистить» можно на выбор произвести очистку выделенных столбцов от содержимого (текста, цифровых значений, формул), от форматов, от примечаний, а можно произвести полную очистку, выбрав пункт «Очистить все». Информацию, расположенную в столбцах или строках, можно скрывать. Скрытые данные не удаляются, а временно не выводятся на экран. Для скрытия столбца, его необходимо выделить, затем щелкнуть правой кнопкой мыши в любом месте выделенного диапазона либо на названии столбца и выбрать из контекстного меню пункт «Скрыть». Для отображения скрытых столбцов нужно выделить два столбца, между которых находятся скрытые столбцы и, щелкнув правой кнопкой мыши, выбрать из контекстного меню пункт «Отобразить». Скрытие и отображение столбцов можно также производить на вкладке «Главная», в группе «Ячейки», в меню кнопки «Формат».
Удаление столбцов производится абсолютно аналогично их скрытию, только в контекстном меню вместо пункта «Скрыть» выбирается пункт «Удалить», а на вкладке «Главная», в группе «Ячейки», выбирается кнопка «Удалить» вместо кнопки «Формат».
Как удалять либо скрывать столбцы макросом при соблюдении условия?
При работе с объемными таблицами данных, зачастую приходится заниматься скрытием или удалением лишних столбцов с данными, это могут быть, например, пустые столбцы, либо столбцы с промежуточными расчетами, либо столбцы, содержащие определенный текст. Процесс, мягко говоря, утомительный. Как раз в таких случаях существенно облегчит жизнь надстройка для быстрой и безошибочной обработки столбцов. Посмотрим на возможности, предоставляемые этой надстройкой.
На выбор пользователя надстройка позволяет удалять либо скрывать пустые столбцы, а может на выбор пользователя скрывать либо удалять столбцы, удовлетворяющие заданным условиям, например, таким как содержание заданного текста, либо отсутствием такового, может учитывать регистр при поиске текста в столбцах, а может не учитывать. Для того, чтобы при удалении/скрытии в таблицах не пострадали столбцы с какими-либо промежуточными результатами, введено дополнительное ограничение по номерам столбцов, при помощи которого можно задавать диапазон для действия макроса. Если же диапазон не задан пользователем, то макрос работает со столбцами, так называемого используемого диапазона, то есть со столбца, в котором содержится первая заполненная информацией ячейка, до столбца с последней заполненной ячейкой. Пользователю также предлагается ограничить обработку данных либо активным листом, либо всеми листами активной рабочей книги.
Как удалять либо скрывать столбцы макросом по нескольким условиям?
Специально для случаев, когда необходимо удалять либо скрывать столбцы, содержащие разные слова, либо сочетания слов, надстройка доработана таким образом, чтобы можно было ввести сразу все искомые слова либо их сочетания в поле для ввода текста. При этом в качестве разделителя используется знак точка с запятой (;). Это позволяет сократить количество запусков макроса и сделать необходимые манипуляции со столбцами за один раз.
1) Скрывать либо удалять столбцы;
2) Выбирать одно из нескольких условий для искомых значений (совпадает, не совпадает, содержит, не содержит, начинается, не начинается, заканчивается, не заканчивается);
3) Находить пустые столбцы и столбцы, содержащие заданный текст;
4) Позволяет осуществлять одновременный поиск различных текстовых выражений, внесенных в поле для ввода текста с использованием знака-разделителя. В качестве знака-разделителя используется (;) точка с запятой ;
5) Выбирать различные диапазоны для обработки данных;
6) Ограничивать выбранные диапазоны номерами столбцов слева и/или справа для избежания повреждения боковых шапок таблиц и столбцов с разного рода промежуточными итогами;
7) Осуществлять поиск текста в столбцах как с учетом регистра, так и без.
При нажатии кнопки "Пуск" макрос скроет либо удалит пустые столбцы, либо столбцы, содержащие заданный пользователем текст на активном листе либо на всех листах рабочей книги.
Вставляйте и удаляйте строки и столбцы для оптимального размещения данных на листе.
Примечание: В Microsoft Excel установлены следующие ограничения на количество строк и столбцов: 16 384 столбца в ширину и 1 048 576 строк в высоту.
Вставка и удаление столбца
Выделите любую ячейку в столбце, а затем выберите Главная > Вставить > Вставить столбцы на лист или Удалить столбцы с листа.
Можно также щелкнуть правой кнопкой мыши в верхней части столбца и выбрать Вставить или Удалить.
Вставка и удаление строки
Выделите любую ячейку в строке, а затем выберите Главная > Вставить > Вставить строки листа или Удалить строки листа.
Можно также щелкнуть правой кнопкой мыши номер строки и выбрать Вставить или Удалить.
Параметры форматирования
При выделении строки или столбца, к которым применено форматирование, это форматирование переносится в новую вставляемую строку или столбец. Если вы не хотите применять форматирование, можно нажать кнопку Параметры вставки после выполнения вставки, а затем выбрать один из следующих параметров.
Если кнопка "Параметры вставки" не отображается, выберите Файл > Параметры > Дополнительно и в группе Вырезание, копирование и вставка установите флажок Отображать кнопку параметров вставки.
Вставка строк
Чтобы вставить одну строку: щелкните правой кнопкой мыши всю строку, над которой требуется вставить новую, и выберите Вставить строки.
Чтобы вставить несколько строк:выделите столько же строк, сколько хотите добавить сверху. Щелкните выделенные строки правой кнопкой мыши и выберите Вставить строки.
Вставка столбцов
Чтобы вставить один столбец: щелкните правой кнопкой мыши весь столбец, справа от которого хотите добавить новый, и выберите Вставить столбцы.
Чтобы вставить несколько столбцов:выделите столько же столбцов, сколько хотите добавить справа. Щелкните выделенные столбцы правой кнопкой мыши и выберите Вставить столбцы.
Удаление ячеек, строк и столбцов
Если вам больше не нужны какие-либо ячейки, строки или столбцы, вот как удалить их:
Выделите ячейки, строки или столбцы, которые вы хотите удалить.
Щелкните правой кнопкой мыши и выберите нужный вариант удаления, например Удалить ячейки (со сдвигом вверх), Удалить ячейки (со сдвигом влево), Удалить строки, или Удалить столбцы.
При удалении строк или столбцов следующие за ними строки и столбцы автоматически сдвигаются вверх или влево.
Совет: Если вы передумаете сразу после того, как удалите ячейку, строку или столбец, просто нажмите клавиши CTRL+Z, чтобы восстановить их.
Вставка ячеек
Чтобы вставить одну ячейку
Щелкните правой кнопкой мыши ячейку, над которой нужно вставить новую.
Нажмите Вставить и выберите Ячейки со сдвигом вниз.
Чтобы вставить несколько ячеек
Выделите столько же ячеек, сколько хотите добавить сверху.
Щелкните выделенные ячейки правой кнопкой мыши и выберите Вставить > Ячейки со сдвигом вниз.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Кроме того, что в ячейках может содержаться информация, к ним также могут быть применены специальные форматы, различные рамки, заливки, примечания, могут быть изменены шрифты, их цвета и размеры. Как очистить ячейки от всего этого, оставив информацию в ячейках нетронутой?
Ранее рассматривались вопросы о том, как полностью удалить ячейки вместе со всей информацией, содержащейся в них и о том, как удалить значения из ячеек с определенными параметрами. В этой статье решим задачу очистки ячеек как от значений, так и от специальных форматов, шрифтов, заливок, рамок, примечаний, гиперссылок и объединения ячеек.
Как очистить ячейки стандартными средствами Excel?
Для очистки ячеек от информации в Excel предусмотрено несколько режимов. Для того, чтобы их увидеть, выделяем диапазон, который будет подвергнут очистке и на вкладке "Главная" в группе кнопок "Редактирование" заходим в меню кнопки "Очистить".
Рассмотрим пункты меню кнопки "Очистить" более детально.
Как очистить все в выделенном диапазоне ячеек?
При выборе первого пункта "Очистить все", из ячеек выделенного диапазона будет удалена абсолютно вся информация.
Как очистить только форматы в выбранном диапазоне ячеек?
При выборе второго пункта "Очистить форматы" из выделенного даипазона будет удалена информация, связанная с форматом, то есть информация о шрифтах, их размерах, цветах, способах начертания и так далее, а точнее говоря, все эти параметры будут приведены к параметрам, используемым по умолчанию.
Как очистить ячейки от содержимого?
При выборе третьего пункта "Очистить содержимое" из выделенного диапазона будет удалена только та часть информации, которую вы можете видеть в строке формул, то есть удалены будут только значения ячеек.
Как очистить ячейки от примечаний?
Выбрав пункт четвертый "Очистить примечания", из выделенного диапазона будут удалены только примечания, вся остальная информация сохранится.
Как очистить ячейки средствами VBA?
Для более гибкой очистки ячеек создана надстройка, позволяющая очистить диапазон по выбранным параметрам. Диалоговое окно этой надстройки можно увидеть на скриншоте ниже. С ее помощью пользователи могут выбрать диапазон для очистки, а также выбрать параметр, либо сочетание параметров, от которых будет очищен выбранный диапазон.
Использование надстройки позволяет:
1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;
2. на выбор пользователя удалять значения ячеек, примечания, гиперссылки, форматы, шрифты, заливки, рамки и объединения ячеек;
3. выбирать по своему усмотрению один из четырех режимов обработки данных (смотри скриншот).
Отличия от стандартной очистки состоят в том, что дополнительно можно производить очистку ячеек от гиперссылок и от объединения ячеек, кроме того, предусмотрена более тонкая очистка по параметрам, связанным с форматированием ячеек. Еще одним отличием является возможность очищать ячейки на всех листах.
Надстройка легко устанавливается и запускается одним нажатием кнопки, выведенной прямо в главное меню Excel.
Многим пользователям, в том числе и мне, приходится ежедневно, еженедельно, ежемесячно либо ежегодно использовать одни и те же таблицы, сделанные в Excel. Для повторного и последующего использования этих таблиц, как правило приходится удалять значения некоторых ячеек для того, чтобы освободить место для внесения в них новых данных. Удалить устаревшие значения в таблице тем дольше и сложнее, чем старее версия Excel, чем больше таблица и чем больше промежуточных расчетов и результатов в этой таблице. Как сделать это быстро?
Чтобы было понятно о чем пойдет речь, вспомним, что ячейка - это часть табличного пространства, образованная пересечением строки и столбца. Ячейка может содержать какие-то значения, например числа, текст, формулы и так далее, а также может содержать информацию о шрифтах, их цветах и размерах, заливках, рамках, защите и так далее. Таким образом, удалять можно как сами ячейки со всем их содержимым (при этом место удаленной ячейки занимает новая, а всё табличное пространство смещается либо вверх, либо влево) так и часть информации, например только значения или только форматы. В этой публикации речь пойдет об удалении значений, когда сами ячейки остаются на своих местах и информация об их форматах остается неизменной.
Стандартные средства для удаления значений ячеек
Представим (либо посмотрим на видео ниже), что на рабочем листе Excel расположена таблица. В этой таблице шапка и некоторые столбцы, содержат различные текстовые значения, основное пространство таблицы содержит ячейки с числовыми значениями, которые в свою очередь участвуют в расчетах, определенных формулами. К примеру, необходимо удалить из этой таблицы только числа, не удаляя при этом формулы, или только текст, не удаляя при этом числа и всё остальное.
Для того чтобы очистить место в таблице для внесения новых значений можно выделять поочередно нужные диапазоны ячеек и нажимать кнопку "Delete" на клавиатуре, как говорится, медленно, но верно. Проделав такую операцию полтора десятка раз невольно появляется вопрос "а можно ли как-нибудь быстрее?". Конечно можно! Даже нужно. В Microsoft Excel 2007 и выше для этих целей подойдет такой инструмент как "Выделение группы ячеек". Для этого выбираем на ленте Excel вкладку "Главная", переходим в группу "Редактирование", нажимаем кнопку "Найти и выделить" и выбираем команду "Выделение группы ячеек". Появляется одноименное диалоговое окно, при помощи которого можно выделить определенные ячейки таблицы и после этого удалить их значения.
Выделение группы ячеек производится последовательно, нельзя например одновременно выделить и константы и формулы. Так уже гораздо быстрее, не правда ли? Но и это не предел. Все-таки удаление происходит в два этапа, сначала выделяем, затем удаляем. Если таблицы на разных листах и их много, то и с таким способом можно провозиться достаточно долго. В Excel 2003 выделение группы ячеек вообще не предусмотрено и в этой версии без дополнительных инструментов не обойтись.
Дополнительные инструменты для выборочного удаления значений
В некоторых случаях ускорить выборочное удаление значений ячеек поможет надстройка, которая легко устанавливается и запускается одним нажатием кнопки, выведенной прямо в главное меню. Надстройка позволяет удалять значения из ячеек с числами, с текстом, с формулами, с примечаниями, с заливкой, с рамками, как по раздельности, так и в любых сочетаниях. Кроме того предусмотрен выбор диапазона для удаления значений. Возможен используемый диапазон (с первой до последней заполненной ячейки), используемые диапазоны (используемый диапазон на каждом листе активной рабочей книги), выделенный пользователем диапазон, а также выделенный диапазон, но на каждом листе рабочей книги.
Использование надстройки позволяет:
1. удалить значения из ячеек с числовыми значениями;
2. удалить значения из ячеек с текстовыми значениями;
3. удалить значения из ячеек с формулами;
4. удалить значения из ячеек, содержащих примечания;
5. удалить значения из всех ячеек с цветной заливкой;
6. удалить значения из ячеек только с определенным цветом заливки;
Читайте также: