Як видалити / приховати стовпці в Excel за умовою? Робота зі стовпцями

1. З чим маємо справу?
2. Скільки стовпців в робочому аркуші?
3. Як виділити стовпець? Як виділити декілька стовпців підряд? Як виділити декілька стовпців вибірково?
4. Як змінити стиль заголовків стовпців?
5. Як закріпити стовпці на аркуші? Як зняти закріплення стовпців на аркуші?
6. Як змінити ширину стовпця? Як задати автоматичний підбір ширини стовпця? Як задати фіксовану ширину...
7. Як транспонувати значення стовпців в рядки?
8. Як додати новий стовпець в таблицю? Як вставити скопійований стовпець?
9. Як очистити стовпці від вмісту? Як приховати і відобразити стовпці? Як видалити стовпці?
10. Як видаляти або приховувати стовпці макросом при дотриманні умови?
11. Як видаляти або приховувати стовпці макросом по декількох умовах?

Розглянемо найбільш поширені питання, що стосуються стовпців 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) Здійснювати пошук тексту в стовпцях як з урахуванням регістра, так і без.

відео по роботі надбудови

макрос (надбудова) для видалення / приховування порожніх стовпців і стовпців за умовою

При натисканні кнопки "Пуск" макрос приховає або видалить порожні стовпці, або стовпці, що містять заданий користувачем текст на активному аркуші або на всіх аркушах робочої книги.

Інші матеріали по темі: