Как убрать пустое пространство в excel график

Обновлено: 03.01.2025

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

Как НЕ НАДО удалять пустые строки в Excel

Удаление пустых строк не рекомендуется выполнять через раздел «Найти и выделить». Данная команда удаляет сразу всю строку, в которой есть пустоты. В таком случае ячейки с нужной информацией также пропадут. Инструмент «Найти и выделить» актуален, когда пустой получилась целая строчка в таблице, и ее необходимо удалить. В такой ситуации необходимо проделать следующие шаги:

Выделить пустую строчку с помощью мыши.
Перейти в раздел «Найти и выделить».
Кликнуть по пункту «Перейти к специальному».
Нажать ЛКМ по слову «Пробел». После этого пустота должна исчезнуть, и таблица преобразится.

В Эксель пустые строки могут удаляться посредством сортировки таблицы либо установления фильтра. Однако такой способ также может привести к полному удалению строки с заполненными ячейками. Причем пострадать могут и соседние строчки или столбцы.

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

Формула для удаления пустых строк в Excel

Как удалить пустые строки в Excel с помощью Power Query

Power Query — это специальный инструмент, который присутствует в версиях Excel, начиная с 2013 года.

Важно! Такой инструмент изменяет форматирование таблицы, заливку ячеек, их размер, ориентацию.

Чтобы удалить пустые ячейки в таблицы, необходимо выполнить несколько простых шагов по алгоритму:

Выделить манипулятором строчку или столбик, в котором есть пустоты, требующие деинсталляции.
В интерфейсе Excel сверху перейти в раздел «Данные».
В графе открывшихся параметров кликнуть по кнопке «Получить данные».
В меню контекстного типа нажать по строке «Из других источников» и далее выбрать вариант «Из таблицы/диапазона». Теперь откроется небольшое меню, где надо поставить галочку рядом с параметром «Таблица с заголовком» и щелкнуть по «ОК».

Изучить интерфейс открывшегося редактора Power Query.
Кликнуть ЛКМ по кнопке «Удалить строки» в верхней части раздела.
В контекстном меню нажать по «Удалить пустые строки».

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

Дополнительная информация! После реализации рассмотренного способа формат отображения дат и чисел в таблице изменится.

Как удалить строки, если ячейка в определенном столбце пуста

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

Нажать ПКМ по названию столбика, в котором есть пустоты.
В контекстном меню нажать на «Выделить» и в следующем окне указать «Пустые ячейки». После закрытия окошка в выбранном столбце выделятся только пустоты, а ячейки с информацией останутся незатронутыми.
Щелкнуть ПКМ по любой выделенной ячейки и нажать по строчке «Удалить». Откроется небольшое окно, в котором надо поставить тумблер напротив поля «Строки», а затем кликнуть «ОК».
Проверить результат. Строчки, в которых присутствовали пустые ячейки, должны полностью деинсталлироваться из таблицы. Как и говорилось выше, минус метода в том, что ячейки с информацией также удалятся.

Как удалить лишние строки под данными или скрыть их

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

Выделить пустую ячейку ЛКМ и зажать одновременно кнопки «Ctrl+Shift+End». Данная команда выделит оставшиеся ячейки, в которых на самом деле что-то написано.
Кликнуть ПКМ по ячейке и в окошке контекстного типа выбрать вариант «Удалить».
Проверить удалились ли лишние пустоты. Выбранные ячейки со скрытыми данными должны деинсталлироваться, как и в предыдущем методе.

Иногда пользователю не хочется удалять информацию из скрытых ячеек таблицы. В таком случае их можно скрыть. Скрытие строчек в Microsoft Office Excel осуществляется по следующей инструкции:

Выделить ячейку под последней строкой с данными и зажать «Ctrl+Shift+End» для выделения оставшихся лишних пустот.
После выделения всех пустот по любой из них надо кликнуть ПКМ и щелкнуть по варианту «Скрыть».
Можно зажать кнопки «Ctrl+9» для скрытия лишних ячеек.
Зажать «Ctrl+Shift+9», если пустоты нужно будет опять сделать видимыми.

Как быстро удалить пустые строки в Excel с помощью VBA

VBA — это специальный язык программирования, предназначенный для автоматизации процессов в редакторах Microsoft Office. С его помощью можно будет в кратчайшие сроки удалить пустые ячейки в Excel, скопировав нужный код программы. Для реализации такого способа деинсталляции не обязательно быть программистом. Далее будет рассмотрено несколько команд, реализуемых на VBA в Эксель.

Макрос 1. Удалить пустые строки в выбранном диапазоне

Чтобы ввести макрос в Excel на языке программирования VBA и запустить его, необходимо:

Переключиться в раздел «Разработчик», находящийся в списке параметров сверху главного меню.
Кликнуть по кнопке «Запись макроса».
Скопировать код с изображения выше и ввести его в соответствующее поле.
Сохранить изменения нажатием на «ОК».
Для запуска программы необходимо перейти во вкладку «Макросы», выбрать нужный код по названию созданных, выделить диапазон, в котором предстоит удалить пустоты, и кликнуть «ОК».

Важно! Созданный макрос сохранится в Excel, и его не нужно будет вводить каждый раз для выполнения конкретного действия.

Макрос 2. Удалить все пустые строки в Excel

С помощью написанного кода можно деинсталлировать сразу все пустоты, которые находятся на активном листе Эксель. Для этого используется следующий код:

Макрос для удаления всех пустот на рабочем поле листа Excel

Макрос 3. Удалить строку, если ячейка пуста

Это метод пробелов, который был описан выше, реализуемый с помощью кода. Данный способ позволяет удалить всю строку, в которой присутствует одна или несколько пустых ячеек, а также избавиться от лишних строк. Представленный код будет выглядеть так:

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

Как добавить макрос в свою книгу

Для записи кодов в Excel создается своя книга. Выше был рассмотрен альтернативный метод создания и запуска макросов. Есть еще один способ, который заключается в выполнении следующих шагов:

Открыть лист, в котором нужно удалить какие-либо ячейки.
Зажать клавиши «Alt+F11», чтобы запустить встроенный редактор «Visual Basic».
В графе параметров слева открывшегося окошка нажать по слову «Insert», а затем перейти во вкладку «Module».
Вставить нужный код программы в отобразившееся окошко.
Щелкнуть по F5, чтобы проверить код на работоспособность.
Кликнуть по кнопке «Сохранить» для добавления макроса в свою книгу.

Дополнительная информация! Чтобы активировать созданный макрос в следующий раз, необходимо зайти в книгу, выбрать соответствующую таблицу, зажать «Alt+F8» с клавиатуры, выбрать нужный код и щелкнуть по кнопке «Выполнить».

Как удалить пустые строки в Excel с помощью сортировки

Сортировка данных в таблице помогает удалить пустоты. Данная операция выполняется по алгоритму:

Как удалить пустые строки в Excel с помощью фильтрации

Процесс выполнения поставленной задачи подразделяется на следующие этапы:

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

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

Как убрать пустые строки в Excel с помощью выделения группы ячеек

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

Как удалить пустые строки в Экселе с помощью вспомогательного столбца

Для деинсталляции пустот рядом с таблицей можно создать столбец, который будет выступать в роли вспомогательного элемента. Это один из самых быстрых и надежных способов удаления. Чтобы реализовать данный метод, требуется выполнить несколько простых манипуляций по пошаговой инструкции:

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

Важно! Данная формула выглядит следующим образом: «=СЧИТАТЬПУСТОТЫ(А6:D6)». Вместо букв в скобках указывается диапазон ячеек.

Заключение

Таким образом, существует несколько способов удаления пустых строк в Microsoft Excel, каждый из них актуален для конкретного случая. Чтобы разбираться в теме, необходимо внимательно ознакомиться с вышеизложенной информацией.

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

Shambala

Автор - Shambala
Дата добавления - 13.07.2019 в 09:51

Стройте диаграмму на основе "Умной таблицы", при необходимости добавляйте или удаляйте лишние строки. Еще вариант создать сводную таблицу с исключением пустых строк. Будет пример в файле Excel - будут варианты решения. Стройте диаграмму на основе "Умной таблицы", при необходимости добавляйте или удаляйте лишние строки. Еще вариант создать сводную таблицу с исключением пустых строк. Будет пример в файле Excel - будут варианты решения. gling gling, файл прикреплён уже в первом посте. Сразу забыла. По одной выборке имён будет сформировано более 40 таблиц с с подтягиванием различных данных. И сформировано более сорока диаграмм для наглядного анализа.
Что-то постоянно добавлять и убирать возможности при таком объёме не будет. Выборка имён будет очень часто меняться и будет меняться все 40 таблиц и диаграмм. Поэтому и вопрос можно ли решить это автоматически? gling, файл прикреплён уже в первом посте. Сразу забыла. По одной выборке имён будет сформировано более 40 таблиц с с подтягиванием различных данных. И сформировано более сорока диаграмм для наглядного анализа.
Что-то постоянно добавлять и убирать возможности при таком объёме не будет. Выборка имён будет очень часто меняться и будет меняться все 40 таблиц и диаграмм. Поэтому и вопрос можно ли решить это автоматически? Shambala _Boroda_, Изменила выборку, диаграмма ведет себя идеально! Вопрос решен. Буду сейчас через контрл F3 пытаться делать тоже самое, как это у получилось у вас! Огромное спасибо! _Boroda_, Изменила выборку, диаграмма ведет себя идеально! Вопрос решен. Буду сейчас через контрл F3 пытаться делать тоже самое, как это у получилось у вас! Огромное спасибо! Shambala

ЮMoney(Яндекс Деньги) 41001841029809

Вариант сводными таблицами. Не всё автоматом, но лучше чем каждый раз в диаграмме менять диапазон, достаточно выбрать нужное в столбце "Наименование". Всё равно же в столбце А прописываете нужный фильтр.
Второй вариант с макросом. Макрос меняет диапазон умной таблицы. Вариант сводными таблицами. Не всё автоматом, но лучше чем каждый раз в диаграмме менять диапазон, достаточно выбрать нужное в столбце "Наименование". Всё равно же в столбце А прописываете нужный фильтр.
Второй вариант с макросом. Макрос меняет диапазон умной таблицы. gling Вариант сводными таблицами. Не всё автоматом, но лучше чем каждый раз в диаграмме менять диапазон, достаточно выбрать нужное в столбце "Наименование". Всё равно же в столбце А прописываете нужный фильтр.
Второй вариант с макросом. Макрос меняет диапазон умной таблицы. Автор - gling
Дата добавления - 13.07.2019 в 11:56 _Boroda_, Очень понравился ваш способ решения, через контрл F3 - было сделано тоже самое, что и в вашем примере. Только у Вас все работает идеально, а у меня вообще не реагирует. Формулы прописаны внимательно и более-менее все понятно. В чем может быть ошибка? _Boroda_, Очень понравился ваш способ решения, через контрл F3 - было сделано тоже самое, что и в вашем примере. Только у Вас все работает идеально, а у меня вообще не реагирует. Формулы прописаны внимательно и более-менее все понятно. В чем может быть ошибка? Shambala Shambala, кроме F3 еще нужно в каждой диаграмме прописать имена этих диапазонов. Посмотрите ПКМ по диаграмме -- Выбрать данные -- активируйте строку Ряд. -- Изменить. Увидите имя файла в сцепке с именем диапазона, так же нужно прописать и в вашем файле.
Но как понимаю для 40 диаграмм придется создать 160 имен диапазонов и каждый прописать в нужную диаграмму. Возможно я ошибаюсь и есть способ проще. Shambala, кроме F3 еще нужно в каждой диаграмме прописать имена этих диапазонов. Посмотрите ПКМ по диаграмме -- Выбрать данные -- активируйте строку Ряд. -- Изменить. Увидите имя файла в сцепке с именем диапазона, так же нужно прописать и в вашем файле.
Но как понимаю для 40 диаграмм придется создать 160 имен диапазонов и каждый прописать в нужную диаграмму. Возможно я ошибаюсь и есть способ проще. gling

gling, спасибо за совет! На счёт 40 диаграмм и 160 имен диапазонов - совершенно не страшно, с удовольствием погружусь в работу. Главное результат!

Создала файл пример, сделала всё что вы описали и всё получилось и заработало.
Но когда перехожу к своему рабочему документу то картина следующая см. фото.
Я так понимаю, что ошибка возникает из-за того, что не правильно прописываю имя документа Excel или имя листа? Хотя делаю всё идентично. Стыдно задавать вопрос по такой мелочи, но в быстро созданном документе все работает, а в моём рабочем такая вот картина.

В картинке не видно что прописано, а при увеличении всё смазывается. Скопируйте текстом формулы в именованом диапазоне, имя диапазона и формулу (ссылку на диапазон) из диаграммы. Или сделайте скрин качественней, или удалите в реальном файле все данные и пришлите его. Диаграмму и формулы в ДиспИмен не удаляйте. В картинке не видно что прописано, а при увеличении всё смазывается. Скопируйте текстом формулы в именованом диапазоне, имя диапазона и формулу (ссылку на диапазон) из диаграммы. Или сделайте скрин качественней, или удалите в реальном файле все данные и пришлите его. Диаграмму и формулы в ДиспИмен не удаляйте. gling

gling, думаю качество должно быть хорошим.

Автор - Shambala
Дата добавления - 14.07.2019 в 09:43

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

Отображать пробелы в таблице как ноль

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

1. После создания диаграммы по значениям щелкните диаграмму правой кнопкой мыши и выберите Выберите данные сформировать всплывающее контекстное меню. Смотрите скриншот:

2. Затем в Выберите источник данных диалоговое окно, нажмите Скрытые и пустые ячейкиИ в Настройки скрытых и пустых ячеек диалог, проверьте 0 вариант. Смотрите скриншот:

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

Пропускать пробелы в таблице

Чтобы пропустить пробелы непосредственно в диаграмме, вам понадобится формула перед созданием диаграммы.

2. Затем, не снимая выделения с ячеек формулы, щелкните Вставить вкладку и вставьте нужную диаграмму в Графики группа. Смотрите скриншот:

Тогда пустые ячейки на диаграмме игнорировались.

Наконечник: Если вы обычно используете сложные диаграммы в Excel, что будет затруднительно, поскольку вы создадите их очень быстро, здесь с Авто текст инструмент Kutools for Excel, вам просто нужно сначала создать диаграммы, затем добавить диаграммы в панель автотекста, а затем вы можете повторно использовать их в любом месте в любое время, вам нужно только изменить ссылки в соответствии с вашими реальными потребностями. Нажмите, чтобы бесплатно скачать сейчас.

Автор книги «Мастерство визуализации данных» и создатель блога data.vis Ольга Базалева поделилась приемами создания профессиональных диаграмм в Excel.

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

Прием 1: задать перекрытие столбцов на диаграмме

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

Как сделать:

Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы;
Формат ряда данных;
Перекрытие рядов.

Чтобы одни столбцы полностью перекрывали другие, выбирайте перекрытие 100%. Можно настроить другое соотношение перекрытия, например, 70%.

Прием 2: уменьшить расстояние между столбцами

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

Одна и та же диаграмма со стандартным и уменьшенным расстоянием между столбцами

Как сделать:

ПКМ по ряду данных;
Формат ряда данных;
Выбрать боковой зазор в процентах.

Чтобы сохранить визуальную целостность и сделать акцент на данных, выбирайте зазор до 25-50%.

ольга базалева

Курс

EXCEL

Узнать больше

Учитесь анализировать любые данные и приводить таблицы в порядок
Работайте с любыми отчетами и формулами Excel

Прием 3: отобразить среднее скользящее

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

Пример скользящей средней — красной линии на диаграмме

Как сделать:

ПКМ по линии графика;
Добавить линию тренда;
Выбрать скользящее среднее;
Установить период.

Инструмент сглаживает колебания и позволяет четче увидеть тренд. Можно построить скользящее среднее за 21 день, 11, 5 или выбрать любое другое окно. Все зависит от уровня сглаживания, который вы хотите увидеть. Чем шире окно, тем более сглаженная линия.

Прием 4: оставить одну подпись на несколько диаграмм

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

Две диаграммы с двумя подписями, лист перегружен информацией Одна подпись для двух диаграмм проще воспринимается

Как сделать:

Построить первую диаграмму;
Расположить следующие поверх первой основной;
Убрать с листа все лишние элементы: подписи рядов, линии сетки, белую заливку области диаграмм.

Подробнее процесс показан в видеоуроке.

Прием 5: добавить вспомогательную ось

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

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