Удаление нулевых значений в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Удаление нулевых значений в Microsoft Excel

Как убрать нули в ячейках в Excel?

Разберем несколько вариантов как можно убрать нули в ячейках в Excel заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

Обработка таблиц с числовыми данными практически неотъемлемая часть работы в Excel для многих пользователей программы.

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

Обычно в таких случаях нули в таблицах заменяют либо на пустое поле, либо на аналогичные по смыслу символы (к примеру, в виде прочерка).

В зависимости от требуемых задач можно выделить несколько различных начальных условий для подобной замены нулей:

  • На всем листе (в каждой ячейке);
  • В конкретных (выделенных) ячейках;
  • В формулах;
  • В сводных таблицах.

Как скрыть нулевые значения в Excel?

Предположим, что у нас имеется таблица с числами (большого размера, чтобы наглядно оценить различие во внешнем виде), где в частности есть достаточно много нулей, и на ее примере попытаемся удалить ненулевые значения:

На всем листе

Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):

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

Нажимаем OK и в результате исходная таблица приобретает следующий вид:

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

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

Важная деталь.

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

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

Частично эту проблему может решить следующий вариант.

В выделенных ячейках

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

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

В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:

  • A — запись когда число положительное;
  • B — запись когда число отрицательное;
  • C — запись когда число равно нулю;
  • D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

Так как наша задача в удалении именно нуля из записи, то нам нужно в маске прописать как будет выглядеть третий параметр (часть C из маски), а заодно и первый (часть A, положительное число) со вторым (часть B, отрицательное число).

Поэтому, чтобы убрать нули из выделенных ячеек, щелкаем по ним правой кнопкой мыши и в контекстном меню выбираем Формат ячеек -> Число, а далее среди форматов переходим во Все форматы:

Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;”-“, чтобы в ячейке стоял прочерк), и нажимаем OK.

В результате получаем:

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

В формулах

Проблему с нулями в ячейках также можно решить и формульным путем.
Если в ячейке значение вычисляется по формуле, то с помощью функции ЕСЛИ мы можем задать различные сценарии отображения (например, если значение не равно 0, то оставляем значение, а если равно 0, то возвращаем пустое поле или любой другой альтернативный вариант).

Давайте для исходной таблицы пропишем отклонение между периодами с помощью функции ЕСЛИ.
Вместо стандартной формулы =A1-B1 пропишем =ЕСЛИ(A1-B1=0;”-“;A1-B1):

Как мы видим теперь в столбце с отклонениями вместо 0 стоят прочерки, чего мы как раз и хотели добиться.

В сводных таблицах

Для начала пусть у нас имеется простая и небольшая таблица с данными (при этом в таблице не все ячейки будут заполнены), на основе которой мы построим сводную таблицу:

Задача удаления нулей из сводной таблицы можно условно разделить на 2 направления:

  • Работа с данными как с ячейками листа;
    В этом случае мы не делаем разницы между ячейками сводной таблицы и ячейками листа, т.е. можем воспользоваться вышеописанными способами, чтобы скрыть 0.
  • Работа с данными в сводной таблице.
    В этом случае мы работаем не с ячейками листа, а непосредственно со сводной таблицей.

Так как первый вариант мы уже детально разбирали выше, то давайте поподробнее остановимся на втором, в котором также выделим 2 способа, чтобы убрать нули из таблицы:

  • С помощью создания фильтров;
  • С помощью настройки параметров сводной таблицы.

Рассмотрим оба варианта.

Создание и применение фильтра

Давайте в сводной таблице добавим в качестве фильтра поля, по которым мы хотим скрыть нули (в данном примере это Количество, но в принципе тут может стоят все что угодно).

Теперь щелкаем по значку фильтра для поля Количество и в выпадающем списке снимаем галочку напротив значений или (пусто), тем самым исключив эти значения из отображения:

Как мы видим строки, где находились нулевые значения, скрылись, что нам и требовалось.

Перейдем к следующему варианту.

Настройка параметров сводной таблицы

С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:

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

В этом случае получается, что пустые ячейки из исходной таблицы подтянулись в сводную, а уже затем мы задали конкретный формат (пустое поле) для их отображения. В итоге получаем:

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.

Удаление нулевых значений в Microsoft Excel

Здравствуйте.
Столкнулась со следующей проблемой:
Имеется массив из почти 500 столбцов и более 1000 строк.
Часть ячеек заполнена числами. В остальных нули.
Мне необходимо удалить нулевые значения, но при этом не удалить нули из чисел типа 10, 100, 101 и тп.

Есть ли несложные пути? Ибо Не владею VBA.
Помогите пожалуйста

Здравствуйте.
Столкнулась со следующей проблемой:
Имеется массив из почти 500 столбцов и более 1000 строк.
Часть ячеек заполнена числами. В остальных нули.
Мне необходимо удалить нулевые значения, но при этом не удалить нули из чисел типа 10, 100, 101 и тп.

Читайте также:  Создание базы данных в Microsoft Excel

Есть ли несложные пути? Ибо Не владею VBA.
Помогите пожалуйста Элли

Сообщение Здравствуйте.
Столкнулась со следующей проблемой:
Имеется массив из почти 500 столбцов и более 1000 строк.
Часть ячеек заполнена числами. В остальных нули.
Мне необходимо удалить нулевые значения, но при этом не удалить нули из чисел типа 10, 100, 101 и тп.

Есть ли несложные пути? Ибо Не владею VBA.
Помогите пожалуйста Автор – Элли
Дата добавления – 25.12.2013 в 16:53

PelenaДата: Среда, 25.12.2013, 16:58 | Сообщение № 2

Здравствуйте. Вариантов много, но так как файла с примером нет, то ответ общий:
1) В параметрах снять флажок Показывать нули в ячейках, которые содержат нулевые значения
2) Использовать Условное форматирование
3) Использовать пользовательский формат ячейки

(это три разных способа)

Здравствуйте. Вариантов много, но так как файла с примером нет, то ответ общий:
1) В параметрах снять флажок Показывать нули в ячейках, которые содержат нулевые значения
2) Использовать Условное форматирование
3) Использовать пользовательский формат ячейки

(это три разных способа)

“Черт возьми, Холмс! Но как. ”
ЯД 41001765434816

Ответить

Сообщение Здравствуйте. Вариантов много, но так как файла с примером нет, то ответ общий:
1) В параметрах снять флажок Показывать нули в ячейках, которые содержат нулевые значения
2) Использовать Условное форматирование
3) Использовать пользовательский формат ячейки

(это три разных способа)

Кстати, вот похожая тема Автор – Pelena
Дата добавления – 25.12.2013 в 16:58

ЭллиДата: Среда, 25.12.2013, 17:04 | Сообщение № 3
PelenaДата: Среда, 25.12.2013, 17:09 | Сообщение № 4

Через найти/заменить: найти 0 — заменить пусто — в параметрах поставить Ячейка целиком — Заменить все

Если не поможет, прикладывайте файл с примером

Через найти/заменить: найти 0 — заменить пусто — в параметрах поставить Ячейка целиком — Заменить все

Если не поможет, прикладывайте файл с примером Pelena

“Черт возьми, Холмс! Но как. ”
ЯД 41001765434816

Ответить

Сообщение Через найти/заменить: найти 0 — заменить пусто — в параметрах поставить Ячейка целиком — Заменить все

Если не поможет, прикладывайте файл с примером Автор – Pelena
Дата добавления – 25.12.2013 в 17:09

Удаление нулевых значений в Microsoft Excel

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

Алгоритмы удаления нулей

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

Способ 1: настройки Excel

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

    Находясь во вкладке «Файл», переходим в раздел «Параметры».

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

    Способ 2: применение форматирования

    Скрыть значения пустых ячеек можно при помощи изменения их формата.

      Выделяем диапазон, в котором нужно скрыть ячейки с нулевыми значениями. Кликаем по выделяемому фрагменту правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек…».

    Производится запуск окна форматирования. Перемещаемся во вкладку «Число». Переключатель числовых форматов должен быть установлен в позицию «Все форматы». В правой части окна в поле «Тип» вписываем следующее выражение:

    Для сохранения введенных изменений жмем на кнопку «OK».

    Теперь во всех областях, которые содержат нулевые значения, будет пусто.

    Способ 3: условное форматирование

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

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

    Открывается окошко форматирования. В поле «Форматировать ячейки, которые РАВНЫ» вписываем значение «0». В правом поле в раскрывающемся списке кликаем по пункту «Пользовательский формат…».

    Открывается ещё одно окно. Переходим в нем во вкладку «Шрифт». Кликаем по выпадающему списку «Цвет», в котором выбираем белый цвет, и жмем на кнопку «OK».

  • Вернувшись в предыдущее окно форматирования, тоже жмем на кнопку «OK».
  • Теперь при условии, если значение в ячейке будет нулевым, то оно будет невидимым для пользователя, так как цвет его шрифта сольется с цветом фона.

    Способ 4: применение функции ЕСЛИ

    Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ.

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

    Запускается Мастер функций. Производим поиск в списке представленных функций оператора «ЕСЛИ». После того, как он выделен, жмем на кнопку «OK».

    Активируется окно аргументов оператора. В поле «Логическое выражение» вписываем ту формулу, которая высчитывает в целевой ячейке. Именно результат расчета этой формулы в конечном итоге и может дать ноль. Для каждого конкретного случая это выражение будет разным. Сразу после этой формулы в том же поле дописываем выражение «=0» без кавычек. В поле «Значение если истина» ставим пробел – « ». В поле «Значение если ложь» опять повторяем формулу, но уже без выражения «=0». После того, как данные введены, жмем на кнопку «OK».

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

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

    Способ 5: применение функции ЕЧИСЛО

    Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО.

      Как и в предыдущем примере, открываем окно аргументов функции ЕСЛИ в первой ячейке обрабатываемого диапазона. В поле «Логическое выражение» записываем функцию ЕЧИСЛО. Эта функция показывает, заполнен ли элемент данными или нет. Затем в том же поле открываем скобки и вписываем адрес той ячейки, которая в случае, если она пустая, может сделать нулевой целевую ячейку. Закрываем скобки. То есть, по сути, оператор ЕЧИСЛО проверит, содержатся ли какие-то данные в указанной области. Если они есть, то функция выдаст значение «ИСТИНА», если его нет, то — «ЛОЖЬ».

    А вот значения следующих двух аргументов оператора ЕСЛИ мы переставляем местами. То есть, в поле «Значение если истина» указываем формулу расчета, а в поле «Значение если ложь» ставим пробел – « ».

    После того, как данные введены, жмем на кнопку «OK».

  • Так же, как и в предыдущем способе, копируем с помощью маркера заполнения формулу на остальную часть диапазона. После этого нулевые значения исчезнут из указанной области.
  • Существует целый ряд способов удалить цифру «0» в ячейке, если она имеет нулевое значение. Проще всего, отключить отображения нулей в настройках Excel. Но тогда следует учесть, что они исчезнут по всему листу. Если же нужно применить отключение исключительно к какой-то конкретной области, то в этом случае на помощь придет форматирование диапазонов, условное форматирование и применение функций. Какой из данных способов выбрать зависит уже от конкретной ситуации, а также от личных умений и предпочтений пользователя.

    Как удалить только 0 (ноль) значений из столбца в excel 2010

    Я хочу удалить значения из всего столбца, где значение ячеек равно 0.

    результирующие ячейки должны быть пустыми.

    Как я могу написать формулу для этого? Есть предложения?

    13 ответов

    пресс управления + H , выберите Options и Match entire cell contents и Match case . В Find what тип поля 0 и оставить Replace with поле пустым. Тогда Замените Все. Это удалит все нули, которые стоят отдельно.

    Я выбрал столбцы, которые хочу удалить 0 значений, затем щелкнул Данные > Фильтр. В заголовке столбца появляется значок фильтра. Я нажал на этот значок и выбрал только 0 значений и нажал OK. Выбирается только 0 значений. Наконец, очистить содержимое или использовать кнопку Удалить. Проблема Решена!

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

    =IF (желаемая ячейка = 0, “”, желаемая ячейка)

    Это должно вернуть исправленный столбец, который затем вы можете скопировать и вставить обратно поверх исходного столбца в виде текста-если вы просто скопируете вставку, происходит круговая ссылка, и все данные “стираются” из обоих столбцов. Не волнуйтесь, если вы не читали это внимательно Ctrl+Z-ваш лучший друг.

    это не отличный ответ, но он должен привести вас к правильному. Я не написал VBA за целую минуту, поэтому я не могу вспомнить точный синтаксис, но вот вам “код psudeo” – я знаю, что вы можете легко реализовать это в макро VBA

    в основном, VBA проходит через каждую строку. Если ячейка в этой строке является целым числом и равна нулю, просто замените ее на ” “. Она не будет пустой, но будет казаться пустой. Я думаю, что есть также свойство cell.value is empty что может очистить содержимое ячейки. Используйте библиотеку в VBA, я уверен, что там есть что-то, что вы можете использовать.

    как вариант, если это одноразовая работа, вы можете использовать специальный фильтр. Просто выберите фильтр из ленты и замените все 0 s по строке с пробелом.

    Я надеюсь, что это поможет вам начать.

    вы не должны использовать пробел “”вместо ” 0″, потому что excel имеет дело с пространством в качестве значения.

    Итак, ответ с опцией by (Ctrl + F). Затем нажмите Параметры и поместите в поиск с “0”. Затем щелкните (сопоставить содержимое всей ячейки. Наконец, заменить или заменить все зависит от вас.

    Это решение также может дать больше. Вы можете использовать (*) до или после значений для удаления любых деталей.

    решение (Ctrl+F)действительно близко – просто последний шаг в процессе не был сформулирован. Хотя автор прав относительно пробела = “0”, это не будет иметь никакого значения с помощью этого метода. Данные, которые вы ищете (все, что вы хотите удалить) может быть что угодно.

    поиск данных, которые вы хотите удалить (в поле “Поиск”). В поле” заменить ” оставьте его пустым. Затем замените или замените все. Ячейки с этими конкретными данными будут опустошены.

    Спасибо, что вывел меня на правильный путь.

    существует проблема с решением Command + F. Он заменит все 0, если вы нажмете “заменить все”. Это означает, что если вы не просматриваете каждый ноль, ноль, содержащийся в важных ячейках, также будет удален. Например, если у вас есть номера телефонов с кодами (420), все они будут изменены на (40).

    Я выбрал столбцы, которые хочу удалить 0 значений, затем щелкнул Данные > Фильтр. В заголовке столбца появляется значок фильтра. Я нажал на этот значок и выбрал только 0 значений и нажал OK. Выбирается только 0 значений. Наконец, очистить содержимое или использовать кнопку Удалить.

    затем, чтобы удалить пустые строки из удаленных значений 0 удалены. Я нажимаю Данные > Фильтр я нажимаю на этот значок фильтра и невыбранные пробелы копируют и вставляют оставшиеся данные в новый лист.

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

    измените тип правила на ” форматировать только ячейки, содержащие” Значение ячейки > равно > 0.

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

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

    Как удалить лишние строки в Excel

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

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

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

    Удаление пустых строк в Excel через сортировку

    Алгоритм действия данного метода весьма прост:

    1. Выделяем всю таблицу.
    2. Перемещаемся на вкладку «Данные».
    3. На панели «Сортировка и фильтр» выбираем функцию «Сортировка». (данная функция может быть запущена и из всплывающего меню, вызываемого нажатием правой кнопки мыши на участке выделенной таблицы).
    4. Указываем столбец сортировки.
    5. Нажимаем «OK».

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

    Как удалить пустые строки в Excel через фильтр

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

    1. Выделяем «шапку» таблицы.
    2. Отправляемся на вкладку «Данные».
    3. На панели «Сортировка и фильтр» выбираем функцию «Фильтр».
    4. Нажимаем на направленную вниз стрелочку, появившуюся в правом нижнем углу заголовка каждого столбца.
    5. В открывшимся окне отфильтровываем содержимое по имени «Пустые».
    6. Нажимаем «OK».

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

    Как удалить лишние строки в Excel через выделение группы ячеек

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

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

    1. Выделяем всю таблицу.
    2. Находим в главном меню вкладку «Редактирование».
    3. Нажимаем на кнопку «Найти и выделить».
    4. Выбираем функцию «Выделение группы ячеек».
    5. В появившемся окне ставим метку рядом с «Пустые ячейки».
    6. Нажимаем «ОК».
    7. Кликаем правой кнопкой мыши в выделенный диапазон.
    8. Выбираем функцию «Удалить».

    Результат – таблица без пустых ячеек.

    Как удалить пустые строки в Excel через вспомогательный столбец

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

    1. Создать вспомогательный столбец в конце таблицы.
    2. Поставить в каждой его ячейки обозначение соответствующей строки (например, заполненная срока – «+», а пустая – «-»).
    3. Отфильтровать последний столбец по значению «-».
    4. Удалить все строки, оставшиеся после фильтрации.
    5. Отменить фильтр.

    В результате остаются только те строки, которые были помечены знаком «+». Вспомогательный столбец по завершению может быть удалён.

    Про удаление пустых строк вне диапазона таблицы

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

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

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

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

    Стандартная функция Excel для удаления повторяющихся значений

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

    1. Выделить всю таблицу.
    2. Проследовать на вкладку «Данные».
    3. Выбрать функцию «Удалить дубликаты».
    4. В появившемся окне отметить столбцы, которые должны содержать повторяющиеся значения.
    5. Нажать «OK».

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

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

    Прекрасный метод, дающий размах для дальнейшего форматирования.

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

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

    Как удалить повторяющиеся строки в Excel через пометки Ложь и Истина

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

    Если интересующее нас значение находится, допустим, в ячейке C10, то в ячейке правее необходимо прописать формулу:

    =C10=C11

    После того, как будет нажата клавиша «Enter», в ячейке появится одно из следующих значений:

    • ИСТИНА (если значение следующей ячейки совпадает с выбранной);
    • ЛОЖЬ (если значения разнятся).

    Этот вариант имеет весьма ограниченное применение.

    Горячие клавиши для удаления строк в MS Excel

    Разбирая вопрос «как удалить лишние строки в Excel» будет абсолютно разумно затронуть тему горячих клавиш, способствующих данному процессу. Среди них:

    • Ctrl + «-» (удаление выделенной строки);
    • Shift + пробел (выделение необходимой строки)
    • Ctrl + пробел (выделение необходимого столбца);
    • Ctrl + A (выделение зоны с данными);
    • Ctrl + Shift + Home, а после Ctrl + Shift + End (выделить весь рабочий диапазон таблицы от первой до последней строки).

    Эти комбинации позволяют проводить форматирование таблицы быстрее и комфортнее.

    Exceltip

    Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

    Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

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

    1. Удаление повторяющихся значений в Excel (2007+)

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

    Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.

    Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

    2. Использование расширенного фильтра для удаления дубликатов

    Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

    3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

    4. Использование сводных таблиц для определения повторяющихся значений

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

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

    Ссылка на основную публикацию
    ×
    ×
    Adblock
    detector