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

Зміст

• Алгоритми видалення
  • Спосіб 1: виділення груп клітин
  • Спосіб 2: умовне форматування та фільтрація
  • Спосіб 3: застосування складної формули
• Питання та відповіді

При виконанні завдань в додатку 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. Варіант з виділенням груп осередків найбільш простий і швидкий. Але ситуації бувають різні. Тому, як додаткові способи, можна використовувати варіанти з фільтруванням і застосуванням комплексної формули.