Видалення порожніх клітинок в Microsoft Excel

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

алгоритми видалення

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

• Якщо рядок (стовпець) повністю є порожній (в таблицях);
• Якщо осередки в рядку і стовпці логічно не пов'язані один з одним (в масивах).

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

Спосіб 1: виділення груп осередків

Найбільш простий спосіб видалити порожні елементи - це скористатися інструментом виділення груп осередків.

1. Виділяємо діапазон на аркуші, над яким будемо проводити операцію пошуку і видалення порожніх елементів. Тиснемо на функціональну клавішу на клавіатурі F5.
2. Запускається невелике віконце, яке називається «Перехід». Тиснемо в ньому кнопку «Виділити ...».
3. Відкривається наступне вікно - «Виділення груп осередків». Встановлюємо в ньому перемикач в позицію «Порожні клітинки». Виконуємо клацання по кнопці «OK».
4. Як бачимо, все порожні елементи зазначеного діапазону були виділені. Кількома за допомогою одного з них правою кнопкою миші. У запустити контекстному меню клацаємо за пунктом «Видалити ...».
5. Відкривається маленьке віконце, в якому потрібно вибрати, що саме слід видалити. Ми залишаємо налаштування за замовчуванням - «Осередки, із зсувом вгору». Тиснемо на кнопку «OK».

Після цих маніпуляцій все порожні елементи всередині зазначеного діапазону будуть видалені.

Спосіб 2: умовне форматування і фільтрація

Видалити порожні клітинки можна також шляхом застосування умовного форматування і подальшої фільтрації даних. Цей метод складніше попереднього, але, тим не менш, деякі користувачі віддають перевагу саме йому. Крім того, потрібно відразу обмовитися, що цей спосіб підходить тільки в тому випадку, якщо значення перебувають в одному стовпці і не містять формули.

1. Виділяємо діапазон, який збираємося обробляти. Перебуваючи у вкладці «Головна», тиснемо на піктограму «Умовне форматування», яка, в свою чергу, розташовується в блоці інструментів «Стилі». Переходимо до пункту списку, «Правила виділення осередків». У списку дій вибираємо позицію «Більше ...».
2. Відкривається віконце умовного форматування. У ліве поле вписуємо цифру «0». У правому полі вибираємо будь-який колір, але можна залишити настройки за замовчуванням. Клацаємо по кнопці «OK».
3. Як бачимо, всі осередки зазначеного діапазону, в яких знаходяться значення, були виділені в обраний колір, а порожні залишилися білими. Знову виділяємо наш діапазон. У цій же вкладці «Головна» клацаємо по кнопці «Сортування і фільтр», розташованої в групі «Редагування». У меню, тиснемо на кнопку «Фільтр».
4. Після цих дій, як бачимо, в верхньому елементі стовпчика з'явилася піктограма символізує фільтр. Тиснемо на неї. У списку, переходимо до пункту «Сортування за кольором». Далі в групі «Сортування за кольором осередку» вибираємо той колір, яким відбулося виділення в результаті умовного форматування.

   Можна також зробити трохи по-іншому. Кількома по значку фільтрації. У меню знімаємо галочку з позиції «Порожні». Після цього клацаємо по кнопці «OK».

5. У будь-якому із зазначених в попередньому пункті варіантів порожні елементи будуть приховані. Виділяємо діапазон решти осередків. На вкладці «Головна» в блоці налаштувань «Буфер обміну» виконуємо клацання по кнопці «Копіювати».
6. Потім виділяємо будь-яку порожню область на тому ж або на іншому аркуші. Виконуємо клацання правою кнопкою миші. У контекстному списку дій в параметрах вставки вибираємо пункт «Значення».
7. Як бачимо, сталася вставка даних без збереження форматування. Тепер можна видалити первинний діапазон, а на його місце вставити той, який ми отримали в ході зазначеної процедури, а можна продовжувати роботу з даними на новому місці. Тут все вже залежить від конкретних завдань і особистих пріоритетів користувача.

урок: Умовне форматування в Excel

урок: Сортування і фільтрація даних в Excel

Спосіб 3: застосування складної формули

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

1. Перш за все, нам потрібно буде дати ім'я діапазону, який піддається трансформації. Виділяємо область, робимо клацання правою кнопкою мишки. В активувати меню вибираємо пункт «Присвоїти ім'я ...».
2. Відкривається вікно присвоєння найменування. В поле «Ім'я» даємо будь-який зручний назву. Головна умова - в ньому не повинно бути пробілів. Для прикладу ми присвоїли діапазону найменування «С_пустимі». Більше ніяких змін в тому вікні вносити не потрібно. Тиснемо на кнопку «OK».
3. Виділяємо в будь-якому місці на аркуші точно такий же за розмірами діапазон порожніх клітинок. Аналогічно натискаємо правою кнопкою миші і, викликавши контекстне меню, переходимо по пункту «Присвоїти ім'я ...».
4. У вікні, як і в попередній раз, присвоюємо будь-яке найменування даної області. Ми вирішили дати їй назву «Без_пустих».
5. Виділяємо подвійним клацанням лівої кнопки мишки перший осередок умовного діапазону «Без_пустих» (у вас він може назватися і по-іншому). Вставляємо в неї формулу наступного типу:

   =ЕСЛИ(СТРОКА()-СТРОКА(Без_пустых)+1>ЧСТРОК(С_пустыми)-СЧИТАТЬПУСТОТЫ(С_пустыми);"";ДВССЫЛ(АДРЕС(НАИМЕНЬШИЙ((ЕСЛИ(С_пустыми "";СТРОКА(С_пустыми);СТРОКА()+ЧСТРОК(С_пустыми)));СТРОКА()-СТРОКА(Без_пустых)+1);СТОЛБЕЦ(С_пустыми);4)))

   Так як це формула масиву, то для виведення розрахунку на екран потрібно натиснути комбінацію клавіш Ctrl + Shift + Enter, замість звичайного натискання кнопки Enter.

6. Але, як бачимо, заповнилася тільки одна комірка. Для того, щоб заповнилися і інші, потрібно скопіювати формулу на решту діапазону. Це можна зробити за допомогою маркера заповнення. Встановлюємо курсор в нижній правий кут комірки, що містить комплексну функцію. Курсор повинен перетворитися в хрестик. Затискаємо ліву кнопку миші і тягнемо його вниз до самого кінця діапазону «Без_пустих».
7. Як бачимо, після цього дії ми маємо діапазон, в якому поспіль розташовані заповнені осередки. Але виконувати різні дії з цими даними ми не зможемо, тому що вони пов'язані формулою масиву. Виділяємо весь діапазон «Без_пустих». Тиснемо на кнопку «Копіювати», яка розміщена у вкладці «Головна» в блоці інструментів «Буфер обміну».
8. Після цього виділяємо початковий масив даних. Клацаємо правою кнопкою миші. У списку, в групі «Параметри вставки» тиснемо на піктограму «Значення».
9. Після цих дій дані будуть вставлені в початкову область свого розташування цільним діапазоном без порожніх клітинок. При бажанні масив, який містить формулу, тепер можна видалити.

урок: Як присвоїти ім'я клітинці в Excel

Існує кілька способів видалення порожніх елементів в Microsoft Excel. Варіант з виділенням груп осередків найбільш простий і швидкий. Але ситуації бувають різні. Тому, як додаткові способи, можна використовувати варіанти з фільтруванням і застосуванням комплексної формули.

• ← Функція EXP (експонента) в Microsoft Excel
• Розрахунок коефіцієнта варіації в Microsoft Excel →