KuklP, спасибо большое! несколько заходов сделал для поиска решения и вот наконец-то ваш совет помог)
Суть как и выше описано, но повторю конкретно свой пример.
Есть таблица, в которой много ячеек, значение которых является результатом вычисления формулы =»»,
так вот эта таблица скопирована и вставлена на новый лист по условию «только значения»,
в итоге визуально много пустых ячеек , не содержащих значения и формулы, которые необходимо удалить со сдвигом вверх,
но функция поиска пустых ячеек их не замечает, для того чтобы эти ячейки корректно воспринимались как пустые, необходимо:
1) Функцией меню «найти и заменить», в которой поле «найти» оставляем пустым, а в поле «заменить на» любое уникальное значение, например «fordelete», и заменяем все ячейки.
2) Снова повторяем функция меню «найти и заменить», но теперь с обратными значениями, т.е., в поле найти «fordelete», а в поле «заменить на» теперь пусто, и снова заменяем все ячейки.
В результате этой процедуры пустые ячейки, теперь по-настоящему пустые.
Например, теперь можно воспользоваться способом 1. отсюда
ссылка
, который до этого на мою таблицу не действовал.
vba для выделенного диапазона:
Selection.Replace What:=»», Replacement:=»del», LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=»del», Replacement:=»», LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
// Повторюсь, что в сообщение KuklP написано абсолютно тоже самое, просто повторил подробнее, для новичков вроде меня)
I’ve created a tool in VBA for Excel that analyses .xlsx files we receive based on a number of criteria. One of those is the number of empty cells in the dataset. Unfortunately, though, I have noticed that a number of the files we receive contain cells with zero-length-strings in them which are being «incorrectly» counted as non-empty cells.
It’s actually highly beneficial to the whole process if I am just able to remove these from the file.
I have googled this problem a lot, but the only solution I have been able to find so far is to loop through all cells in the sheet (I’ve also tried just constants, and also using Find to find all zls). This isn’t very efficient as the worksheets have large amounts of data in them. EDIT: I have also tried the UsedRange.values = UsedRange.values method, but this removes leading zeroes, which I require.
I have also discovered that this works (-@- is a random string that is highly unlikely to be a single cell in my data, and if it’s there, is fine to be removed):
ws.UsedRange.Replace what:=vbNullString, replacement:="-@-", _
lookat:=xlWhole, MatchCase:=False
ws.UsedRange.Replace what:="-@-", replacement:="", _
lookat:=xlWhole, MatchCase:=False
but if I only do it using the single replace it doesn’t:
ws.UsedRange.Replace what:=vbNullString, replacement:="", _
lookat:=xlWhole, MatchCase:=False
The first is ok, but has a number of problems:
- It takes twice as long as a single replace
- It’s not always clear if it has crashed, or continued to work
- If it does crash, I am left with a number of cells containing -@-, which isn’t always obvious, and the tool should be ok to be used by someone who isn’t able to understand VBA.
So my questions:
- Is there a way of doing this using just a single replace? Why does replace work with the double replace, but not the single?
- If the first isn’t possible, then is there a way of ‘rolling back’ the replacement if the code crashes?
- Is there a way of updating the status bar to show how far through the replace is to prove the code is running (much like when running replace in Excel itself)?
- Or is there just a better way of doing it all?
Thanks in advance!
EDIT: Unfortunately, due to the data being processed, I need to retain formatting, including leading zeroes
EDIT: Here is an example of the sort of data I am looking at. I want to replace the zero-length-strings (which are non-blank cells that have no value in them) so they are a truly blank cell.
Я создал инструмент в VBA для Excel, который анализирует файлы .xlsx, которые мы получаем, на основе ряда критериев. Одним из них является количество пустых ячеек в наборе данных. Однако, к сожалению, я заметил, что ряд файлов, которые мы получаем, содержат ячейки со строками нулевой длины, которые «неправильно» считаются непустыми ячейками.
На самом деле это очень полезно для всего процесса, если я просто могу удалить их из файла.
Я много гуглил эту проблему, но единственное решение, которое я смог найти до сих пор, — это перебрать все ячейки на листе (я также пробовал только константы, а также использовал Find, чтобы найти все zls). Это не очень эффективно, так как рабочие листы содержат большие объемы данных. Обновлено: я также пробовал метод UsedRange.values = UsedRange.values, но он удаляет начальные нули, которые мне нужны.
Я также обнаружил, что это работает (-@- это случайная строка, которая вряд ли будет отдельной ячейкой в моих данных, и если она есть, ее можно удалить):
ws.UsedRange.Replace what:=vbNullString, replacement: = "-@-", _
lookat:=xlWhole, MatchCase:=False
ws.UsedRange.Replace what: = "-@-", replacement: = "", _
lookat:=xlWhole, MatchCase:=False
Но если я сделаю это только с помощью единственной замены, это не так:
ws.UsedRange.Replace what:=vbNullString, replacement: = "", _
lookat:=xlWhole, MatchCase:=False
Первый неплох, но имеет ряд проблем:
- Это занимает в два раза больше времени, чем одна замена
- Не всегда ясно, произошел ли сбой или он продолжал работать.
- Если это произойдет, у меня останется несколько ячеек, содержащих -@-, что не всегда очевидно, и инструмент должен быть в порядке, чтобы его мог использовать кто-то, кто не может понять VBA.
Итак, мои вопросы:
- Есть ли способ сделать это, используя только одну замену? Почему replace работает с двойной заменой, но не с одинарной?
- Если первое невозможно, то есть ли способ «откатить» замену в случае сбоя кода?
- Есть ли способ обновить строку состояния, чтобы показать, как далеко должна пройти замена, чтобы доказать, что код работает (так же, как при запуске замены в самом Excel)?
- Или есть просто лучший способ сделать все это?
Заранее спасибо!
Обновлено: К сожалению, из-за обрабатываемых данных мне нужно сохранить форматирование, включая начальные нули
Обновлено: Вот пример данных, на которые я смотрю. Я хочу заменить строки нулевой длины (которые представляют собой непустые ячейки, в которых нет значения), чтобы они были действительно пустой ячейкой.
|
Удалить строки в которых одни ноли. |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Я создал инструмент в VBA для Excel, который анализирует файлы.xlsx, которые мы получаем на основе ряда критериев. Одним из них является количество пустых ячеек в наборе данных. Однако, к сожалению, я заметил, что ряд файлов, которые мы получаем, содержат ячейки со строками нулевой длины, которые «неправильно» считаются непустыми ячейками.
Это на самом деле очень полезно для всего процесса, если я просто могу удалить их из файла.
Я много гуглил эту проблему, но единственное решение, которое мне удалось найти, — это пройти по всем ячейкам листа (я также пробовал только константы, а также использовать Find, чтобы найти все zls). Это не очень эффективно, поскольку рабочие листы содержат большое количество данных. РЕДАКТИРОВАТЬ: я также попытался метод UsedRange.values = UsedRange.values, но это удаляет начальные нули, которые мне требуются.
Я также обнаружил, что это работает ([email protected] это случайная строка, которая вряд ли будет единственной ячейкой в моих данных, и, если она есть, ее можно удалить):
ws.UsedRange.Replace what:=vbNullString, replacement:="[email protected]", _
lookat:=xlWhole, MatchCase:=False
ws.UsedRange.Replace what:="[email protected]", replacement:="", _
lookat:=xlWhole, MatchCase:=False
но если я делаю это только с помощью одной замены, это не так:
ws.UsedRange.Replace what:=vbNullString, replacement:="", _
lookat:=xlWhole, MatchCase:=False
Первый в порядке, но имеет ряд проблем:
- Это занимает вдвое больше времени, чем одна замена
- Не всегда понятно, разбился ли он или продолжал работать
- Если это произойдет сбой, у меня останется несколько ячеек, содержащих [email protected], что не всегда очевидно, и инструмент должен быть в порядке, чтобы его мог использовать тот, кто не может понять VBA.
Итак, мои вопросы:
- Есть ли способ сделать это, используя только одну замену? Почему замена работает с двойной заменой, а не единственной?
- Если первое невозможно, то есть ли способ «откатить» замену в случае сбоя кода?
- Есть ли способ обновления строки состояния, чтобы показать, как далеко заходит замена, чтобы доказать, что код работает (так же, как при запуске замены в самом Excel)?
- Или есть только лучший способ сделать все это?
Заранее спасибо!
РЕДАКТИРОВАТЬ: К сожалению, из-за обрабатываемых данных мне нужно сохранить форматирование, включая ведущие нули
РЕДАКТИРОВАТЬ: Вот пример данных, которые я смотрю. Я хочу заменить строки нулевой длины (которые являются непустыми ячейками, в которых нет значения), чтобы они были действительно пустыми ячейками.
2019-05-07 11:12
3
ответа
Решение
Я знаю, что это тоже цикл, но, возможно, более быстрый путь:
До:
Запустите этот код на примере данных:
Dim X As Double
Option Explicit
Sub Test()
Application.ScreenUpdating = False
Application.Calculation = xlManual
With ActiveWorkbook.Sheets(1).Range("A1:C7")
For X = 1 To 3
.AutoFilter Field:=X, Criteria1:=""
.Columns(X).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
Next X
.AutoFilter
End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
После:
2019-05-07 13:15
Вы можете иметь ячейки с формулами, которые возвращают пустую строку или ячейки с пустыми строковыми константами. Чтобы очистить ячейки второго типа:
Sub KillNullConstants()
Dim cell As Range, Konstants As Range, rng As Range
Set Konstants = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants)
Set rng = Nothing
For Each cell In Konstants
If Len(cell) = 0 Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
If Not rng Is Nothing Then
rng.ClearContents
End If
End Sub
Чтобы очистить ячейки формулы, которые возвращают нулевые строки, просто измените SpecialCells строка кода.
2019-05-07 12:11
Чтение листа в набор записей ADO, а затем копирование набора записей на новый лист, кажется, решает эту проблему. Попробуйте этот код VBA:
Sub copy_data()
Dim cn As Object
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 Macro;IMEX=1;HDR=YES"";"
.Open
End With
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM [Sheet1$];", cn
Dim i As Integer
Dim fld As Object
With ThisWorkbook.Worksheets("Sheet2")
.UsedRange.ClearContents
i = 0
For Each fld In rs.Fields
i = i + 1
.Cells(1, i).Value = fld.Name
Next fld
.Cells(2, 1).CopyFromRecordset rs
.UsedRange.Columns.AutoFit
End With
rs.Close
cn.Close
End Sub
Заметки:
- измените имена листов в коде, чтобы они соответствовали именам листов, которые вы используете. Вы должны положить
$подпишите после имени листа, который вы используете вrs.Open, Если бы ваш лист назывался «Данные», то вы бы поставилиrs.Open "SELECT * FROM [Data$];", cn - в строке подключения расширенные свойства,
HDR=YESбыл использован для указания того, что данные имеют заголовки. ADO может изменить некоторые имена столбцов, если они содержат определенные символы — главным образом.символ, который обычно будет заменен#персонаж
Потенциальные проблемы:
- Ваши данные должны быть примерно в формате таблицы — т.е. имена столбцов в строке 1 со значениями данных под каждым именем столбца
- любые формулы в данных будут преобразованы в значения
- форматирование ячеек не будет скопировано, но числа, отформатированные как текст, останутся как текст, так что начальные нули будут сохранены
- любой текст значения ячейки, длина которого превышает 255 символов, вероятно, будет усечен до 255 символов (при необходимости можно обойти это)
В зависимости от ваших данных использование ADO может создать больше проблем, чем решить
2019-05-08 01:18
Skip to content
Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации.
Пустые строки в таблице — это проблема, с которой мы все время от времени сталкиваемся, особенно при объединении данных из разных источников или импорте информации из каких-то отчетов. Они могут мешать вашим расчетам, портить внешний вид таблицы, а их удаление вручную может быть длительным и подверженным ошибкам процессом.
В этой статье вы узнаете несколько простых и надежных методов удаления “белых пятен” на листах Excel.
- Как НЕ НАДО удалять пустые строки в Excel
- Как удалить пустые строки при помощи формул
- Использование Power Query
- Удаляем строки с определенными пустыми ячейками
- Как убрать или скрыть лишние строки под данными
- Как быстро удалить пустые строки при помощи VBA
- Макрос 1. Удаление в выбранном диапазоне
- Макрос 2. Удаляем все пустые строки
- Макрос 3. Критерий удаления — пустая ячейка
- Самый быстрый способ удалить пустые строки в Excel
Как НЕ НАДО удалять пустые строки в Excel
Есть несколько различных способов удаления пустых строк в Microsoft Excel. Но на удивление многие онлайн-ресурсы придерживаются в своих рекомендациях наиболее опасных из них.
Пожалуй, самый популярный совет – использовать инструмент «Найти и выделить» > «Перейти к специальному» > «Пробелы» .
Что плохого в этой технике? Она выбирает все пустоты в диапазоне данных. Но ведь в строке может быть, к примеру, только одна пустая ячейка, а в остальных будет записаны какие-то данные. И, следовательно, вы в итоге удалите множество строчек, которые содержат хотя бы одну пустую ячейку. Некоторые нужные данные, естественно, будут утеряны.
Специально не буду подробно описывать последовательность действий в этом методе, чтобы вы случайно его не повторили.
И ключевым в данном случае является вопрос: «Вы уверены, что все ячейки в вашей строке пустые? У вас точно нет таких, в которых по какой-то причине не заполнены одна или несколько ячеек, а остальные содержат данные?»
Просто запомните: «Не следует использовать метод поиска пустых ячеек, чтобы выделить и удалить пустые строки».
В качестве иллюстрации на изображении ниже слева показана исходная таблица, а справа — итоговая таблица. И в результирующей таблице все неполные строки пропали, даже 4,6 и 10, которые были частично заполнены:
Встречаются также предложения удалять незаполненные строки при помощи сортировки таблицы или установления фильтра.
Но здесь мы сталкиваемся с той же опасностью. Если вы упорядочите данные по какому-то столбцу, к примеру, от меньшего к большему, то строчки с пустыми ячейками в этом столбце окажутся в самом низу. Но где гарантия того, что в других столбцах правее или левее также отсутствует информация? А если таблица достаточно большая, то и проверить это не так просто.
В итоге вы опять же рискуете удалить данные, в которых случайно оказался пропуск именно в этом столбце.
С фильтрацией примерно та же история. Вы можете установить фильтр по пустым ячейкам в каком-то конкретном столбце. Далее все может произойти так же, как в истории с сортировкой. Какие-то из ячеек могут оказаться заполнены нужной информацией. Чтобы обезопасить себя от случайной её потери, такой фильтр по пустым ячейкам нужно установить в каждом столбце. Хорошо, если их в вашей таблице штук 5. А если 20? А 30? Вряд ли у вас есть желание устанавливать, а затем снимать даже 10 фильтров.
Поэтому эти методы – это также не наш выбор.
Итог: если вы не хотите испортить или потерять данные, никогда не удаляйте пустые строки, выбирая каким-то способом отдельные пустые ячейки в вашей таблице. Вместо этого используйте один из наиболее продуманных подходов, обсуждаемых ниже.
Формула для удаления пустых строк в Excel
Формулу мы будем использовать в дополнительном столбце, который лучше всего расположить справа от вашей таблицы. Поясним на примере.
У нас имеется таблица с заказами от различных покупателей. В ней имеются пропуски. Часть ячеек не заполнена. Давайте избавимся от пустых строк, но при этом сохраним все прочие данные.
Если вы хотите точно знать, что удаляете, то используйте следующую формулу:
=ЕСЛИ(СЧЁТЗ(A2:E2)=0;»Пусто»;»»)
Где A2 — первая, а E2 — последняя использованная ячейка первой строки данных.
Введите эту формулу в F2 или любой другой свободный столбец в строке 2 и перетащите маркер заполнения, чтобы скопировать формулу вниз.
В результате у вас будет записано «Пусто» в незаполненных строках и ничего, если есть хотя бы одна ячейка с данными:
Логика формулы очевидна: вы подсчитываете заполненные ячейки с помощью функции СЧЁТЗ и используете оператор ЕСЛИ для возврата «Пусто» для нулевого результата. Если же найдена хоть какая-то информация в нужном диапазоне, то не возвращаем ничего.
Фактически, вы можете обойтись без ЕСЛИ:
=СЧЁТЗ(A2:Е2)=0
В этом случае формула вернет ИСТИНА для пустых и ЛОЖЬ для заполненных строк.
Встречаются также аналогичные решения с использованием функции СЧИТАТЬПУСТОТЫ. В ней указываем диапазон ячеек и затем сравниваем с количеством столбцов.
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(A2:E2)=ЧИСЛСТОЛБ(A2:E2);»Пусто»;»»)
Если достигнуто равенство, значит, число незаполненных клеток равно числу столбцов и данных в строке нет. Поэтому её можно пометить на удаление.
Как мы это удаление сделаем?
Разберем пошагово действия, как быстро удалить пустые строки:
- Выберите любую ячейку в заголовке и нажмите «Фильтр» на вкладке «Данные». Это добавит стрелки раскрывающегося списка фильтрации ко всем ячейкам заголовка.
- Щелкните стрелку в заголовке столбца формулы, снимите флажок (Выбрать все), выберите «Пусто» и нажмите ОК:
- Выделите всё отфильтрованное. Для этого щелкните самую верхнюю ячейку и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки отфильтрованной области.
Или же щелкните по номеру первой незаполненной строки на вертикальной шкале координат. При этом вся она должна подсветиться. Затем наберите на клавиатуре комбинацию Ctrl + Shift +
(стрелка вниз). При этом будет выделена вся область до конца таблицы. Поле этого кликните по выделению правой кнопкой мыши и выберите из контекстного меню пункт «Удалить». Все выделенные строки будут удалены.
Можно не вызывать контекстное меню, а просто набрать на клавиатуре сочетание клавиш Ctrl и - .
- Снимите фильтр, нажав
Ctrl+Shift+L. Или же поставьте курсор в самую верхнюю позицию и вновь нажмите на иконку фильтра в меню «Данные». - Удалите столбец с формулой проверки, поскольку он вам больше не нужен.
В результате мы имеем чистую таблицу без пробелов, но с сохранением всей информации. Частично заполненные строки остались в таблице, мы их не потеряли.
Совет. Вместо удаления, вы можете скопировать непустые строки в другое место. Для этого отфильтруйте всё, кроме «Пусто», выделите и нажмите Ctrl + C. Затем переключитесь на другой лист, выберите верхнюю левую ячейку целевого диапазона и нажмите Ctrl + V.
Как удалить пустые строки в Excel с помощью Power Query
В Excel 2016 и Excel 2019 есть еще один способ удалить незаполненные строки — с помощью инструмента Power Query. В Excel 2010 и 2013 его можно загрузить как надстройку.
Важное примечание! Этот метод работает со следующим предостережением: Power Query преобразует ваши данные в таблицу Excel и изменяет форматирование, такое как цвет заливки, границы и некоторые числовые форматы. Если для вас важно форматирование исходных данных, то лучше выбрать другой способ удаления.
Итак, давайте по порядку.
- Выберите диапазон, в котором вы хотите заняться удалением.
- Перейдите на вкладку «Данные» и нажмите Получить данные — Из других источников — Из таблицы/диапазона . Это загрузит вашу таблицу в редактор Power Query.
- На вкладке Главная в редакторе Power Query щелкните Удалить строки > Удалить пустые …. (как на рисунке).
- Нажмите кнопку «Закрыть и загрузить». Полученная таблица загрузится на новый лист и закроется редактор запросов.
В результате этих манипуляций я получил следующую таблицу без прежних белых полос посреди данных, но с парой неприятных изменений — формат чисел утерян и даты отображаются в формате по умолчанию вместо пользовательского.
Как удалить строки, если ячейка в определенном столбце пуста
В начале этого урока мы предостерегали вас от удаления пустых строк путем выбора пробелов. Однако этот метод пригодится, если вы хотите удалить строки на основе пробелов в определенном столбце.
В качестве примера удалим все строки, в которых ячейка в столбце A не содержит никакой информации:
- Выберите ключевой столбец, в нашем случае столбец A.
- На вкладке «Главная» нажмите Найти и выделить > Перейти… > Выделить. Или просто нажмите
F5и затем — Выделить… .
- В диалоговом окне выберите «Пробелы» и затем — «ОК». Это выделит все пустоты в столбце A.
- Щелкните правой кнопкой мыши любую выделенную ячейку и выберите «Удалить…» из контекстного меню.
- В диалоговом окне «Удалить» кликните «Строку» и нажмите «ОК».
Готово! Строки, которые не имеют данных в столбце A, больше не существуют.
Такого же результата можно добиться, отфильтровав пробелы в ключевом столбце.
Как удалить лишние строки под данными или скрыть их.
Иногда строки, которые выглядят совершенно пустыми, на самом деле могут содержать пробелы, перевод строки или непечатаемые символы. Чтобы проверить, действительно ли последняя ячейка с данными является последней использованной ячейкой на листе, нажмите Ctrl + End. Если это привело вас к визуально свободной от данных строке под вашими данными, то с точки зрения Excel, в ней на самом деле что-то записано. Чтобы удалить эти лишние строки под таблицей, сделайте следующее:
- Щелкните заголовок первой свободной строки под данными, чтобы выбрать ее целиком.
- Нажмите
Ctrl+Shift+End. Это выберет все строчки ниже, которые содержат что-либо, включая пробелы и непечатаемые символы. - Щелкните выделение правой кнопкой мыши, как это мы уже делали ранее, и выберите «Удалить…».
Однако, удаление — это достаточно радикальный шаг. Поэтому, думаю, стоит поискать более «мягкие» варианты. К примеру, нет ничего, что могло бы помешать вам незаполненные строки либо столбцы просто скрыть. Вот простая инструкция:
- Выберите строку под последней строкой с данными, щелкнув ее заголовок.
- Нажмите
Ctrl+Shift+
, чтобы расширить выделение до конца вниз. - Нажмите
Ctrl+9чтобы скрыть выбранные строки. Или щелкните выделение правой кнопкой мыши и выберите «Скрыть».
Чтобы вновь показать ранее скрытое, нажмите Ctrl + A , чтобы выделить весь лист, а затем нажмите Ctrl + Shift + 9, чтобы снова сделать все видимым.
Аналогичным образом вы можете скрыть неиспользуемые столбцы справа от ваших данных.
Как быстро удалить пустые строки в Excel с помощью VBA
Excel VBA может исправить множество вещей, включая и несколько пустых строк. Лучшее в предлагаемом подходе — то, что он не требует никаких навыков программирования. Просто возьмите один из приведенных ниже кодов и запустите его в своем Excel.
Макрос 1. Удалить пустые строки в выбранном диапазоне.
Этот код VBA автоматически удаляет все пустые строки в выбранном диапазоне, не показывая пользователю никаких сообщений или диалоговых окон.
Макрос удаляет строку, только если вся она пуста . Он определяет количество ячеек с данными в каждой строке, а затем удаляет строки с нулевым результатом.
Public Sub DeleteBlankRows()
Dim SourceRange As Range
Dim EntireRow As Range
Set SourceRange = Application.Selection
If Not (SourceRange Is Nothing) Then
Application.ScreenUpdating = False
For I = SourceRange.Rows.Count To 1 Step -1
Set EntireRow = SourceRange.Cells(I, 1).EntireRow
If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End If
End Sub
Чтобы дать пользователю возможность выбрать целевой диапазон после запуска макроса, используйте этот код:
Public Sub RemoveBlankLines()
Dim SourceRange As Range
Dim EntireRow As Range
On Error Resume Next
Set SourceRange = Application.InputBox( _
"Выберите диапазон:", "Удалить пустые строки", _
Application.Selection.Address, Type:=8)
If Not (SourceRange Is Nothing) Then
Application.ScreenUpdating = False
For I = SourceRange.Rows.Count To 1 Step -1
Set EntireRow = SourceRange.Cells(I, 1).EntireRow
If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End If
End Sub
После запуска макрос показывает вам поле ввода, вы указываете целевой диапазон и нажимаете OK:
Через мгновение все лишние строки в выбранном диапазоне будут удалены, а оставшиеся сдвинутся вверх.
Макрос 2. Удалить все пустые строки в Excel
Чтобы массово удалить все пустые строки на активном листе, макрос находит последнюю строку используемого диапазона (т.е. содержащую последнюю ячейку с данными), а затем переходит вверх, удаляя строчки, в которых нет никакой информации:
Sub DeleteAllEmptyRows() Dim LastRowIndex As Integer Dim RowIndex As Integer Dim UsedRng As Range Set UsedRng = ActiveSheet.UsedRange LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count Application.ScreenUpdating = False For RowIndex = LastRowIndex To 1 Step -1 If Application.CountA(Rows(RowIndex)) = 0 Then Rows(RowIndex).Delete End If Next RowIndex Application.ScreenUpdating = True End Sub
Макрос 3. Удалить строку, если ячейка пуста.
С помощью этого макроса вы можете удалить всю строку, если ячейка в указанном столбце не содержит данных.
Этот код проверяет столбец A на наличие пробелов. Чтобы удалить строки на основе другого столбца, замените «A» другой соответствующей буквой.
Sub DeleteRowIfCellBlank()
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Важно! Макрос удаляет неиспользуемые строки со всего листа , поэтому будьте очень осторожны при его использовании. В качестве меры предосторожности может быть целесообразно создать резервную копию рабочего листа перед запуском этого макроса.
Чтобы удалить незаполненные строки в Excel с помощью макроса, вам нужно вставить код VBA в свою книгу.
Как добавить макрос в свою книгу?
Чтобы вставить макрос в книгу, выполните следующие действия:
- Откройте лист, на котором вы хотите произвести удаление.
- Нажмите
Alt+F11, чтобы открыть редактор Visual Basic. - На левой панели щелкните правой кнопкой мыши и выберите Insert > Module. Те же пункты можно выбрать вверху через главное меню.
- Вставьте код в появившееся окно кода.
- Нажмите F5 для запуска макроса (но вы можете сделать это и позже прямо из вашей книги).
- Нажмите кнопку «Сохранить». При этом вам будет предложено изменить формат вашей книги. Впрочем, запустить макрос на выполнение можно и без сохранения. Просто закройте окно Visual Basic и вернитесь к вашей таблице. Переходите сразу к пункту 3 ниже.
Чтобы выполнить макрос в рабочей книге Excel, сделайте следующее:
- Откройте свою собственную книгу и включите макросы, если будет предложено.
- Перейдите к нужной таблице.
- На листе нажмите
Alt+F8, выберите макрос и нажмите «Выполнить».
Вы можете запустить один из следующих макросов:
DeleteBlankRows — удаляет строки без данных в текущем выбранном диапазоне.
RemoveBlankLines — удаляет чистые строки и перемещается вверх в диапазоне, выбранном вами после запуска макроса.
DeleteAllEmptyRows — удаляет все незаполненные строки на активном листе.
DeleteRowIfCellBlank — удаляет строку, если ячейка в определенном столбце пуста.
Имейте в виду, что отменить удаление при помощи меню или же комбинации клавиш Ctrl+Z в данном случае будет невозможно. Поэтому рекомендую перед использованием макросов позаботиться о сохранении резервной копии ваших данных.
Самый быстрый способ удалить пустые строки в Excel
Читая предыдущие примеры, разве не казалось, что мы используем кувалду, чтобы расколоть орех? Существует способ буквально в два клика мышки удалить пустые строки в Excel.
При помощи надстройки Ultimate Suite вы можете удалить все пустые строки на листе:
- На вкладке Ablebits Tools в группе Преобразование (Transform) нажмите Удалить пробелы (Delete Blanks) > Пустые строки (Empty Rows) :
- Надстройка сообщит вам, что все пустые строки будут удалены из активного листа, и попросит подтвердить. Нажмите ОК, и через мгновение все строки без данных будут удалены.
Как показано на скриншоте ниже, мы удалили только абсолютно пустые строки , в которых нет ни одной ячейки с данными:
Сравните это с первым рисунком, и вы без труда заметите те 3 строки, которые были удалены одним нажатием мышки. Согласитесь: это самый простой и быстрый способ удаления пустых строк среди всех прочих, рассмотренных в этой статье. Подробнее об инструменте удаления пустот в таблице вы можете прочитать здесь.
Чтобы узнать о других полезных функциях, включенных в надстройку Ultimate Suite, вы можете загрузить пробную версию.
Благодарю вас за чтение.
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью… 
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых… 
Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…
Иногда вы хотите удалить всю строку, если ячейка содержит ноль в Excel, и вы можете удалить их по одной, если их несколько. Но как насчет того, чтобы удалить сотни строк, содержащих ноль? Вы можете выбрать один из сложных способов, указанных ниже.
Удалить строку, если ячейка содержит ноль, с помощью функции фильтра в Excel
Удалить строку, если ячейка содержит ноль, с помощью VBA в Excel
Удалить строку, если ячейка содержит ноль с Kutools for Excel
Удалить строку, если ячейка содержит ноль, с помощью функции фильтра в Excel
Вы можете использовать функцию фильтра, чтобы отфильтровать все строки на основе нулевых значений в определенном столбце, а затем удалить все видимые строки позже. Пожалуйста, сделайте следующее.
1. Выберите ячейки столбца, содержащие нулевые значения, на основе которых вы хотите удалить все строки, затем щелкните Данные > Фильтр. Смотрите скриншот:
2. Затем в первой ячейке выбранного столбца отобразится стрелка раскрывающегося списка, щелкните стрелку и выберите Числовые фильтры > Равно из раскрывающегося списка.
3. в Пользовательский автофильтр диалоговое окно введите номер 0 в текстовое поле, как показано на скриншоте ниже, а затем щелкните OK кнопку.
4. Затем все ячейки с нулевым значением в этом столбце отфильтровываются. Выберите все видимые ячейки в диапазоне фильтра и щелкните их правой кнопкой мыши, выберите Удалить строки из контекстного меню. И во всплывающем окне подсказки щелкните значок OK кнопка. Смотрите скриншот:
5. Теперь все видимые строки удалены. Вы можете нажать Данные > Фильтр снова, чтобы показать все данные без ячеек с нулевым значением. Смотрите скриншот:
Удалить строку, если ячейка содержит ноль, с помощью VBA в Excel
В этом разделе будет показан метод VBA для удаления всех строк, если в определенном столбце Excel существуют нулевые значения.
1. Нажмите Alt + F11 одновременно кнопки для отображения Microsoft Visual Basic для приложений окно.
2. В окне Microsoft Visual Basic для приложений щелкните Вставить > Модули, затем скопируйте и вставьте следующий код VBA в окно модуля.
VBA: удалить целые строки, если в определенном диапазоне столбцов на листе существует нулевое значение
Sub DeleteZeroRow () 'Обновить ) Application.ScreenUpdating = False Do Set Rng = WorkRng.Find ("20140616", LookIn: = xlValues) Если не Rng Is Nothing, то Rng.EntireRow.Delete End If Loop While Not Rng Is Nothing Application.ScreenUpdating = True End Sub
3. нажмите F5 ключ для запуска кода, во всплывающем окне KutoolsforExcel в диалоговом окне выберите диапазон столбцов, в котором вы хотите удалить целые строки на основе нулевых значений внутри, затем щелкните OK кнопка. Смотрите скриншот:
Затем все строки, основанные на нулевых значениях в указанном диапазоне столбцов, немедленно удаляются.
Удалить строку, если ячейка содержит ноль с Kutools for Excel
Для многих пользователей Excel использование кода VBA опасно для удаления данных в Excel. Если вы не совсем доверяете коду VBA, здесь мы рекомендуем вам попробовать Выбрать определенные ячейки полезности Kutools for Excel.
1. Выберите диапазон столбцов, в котором вы удалите целые строки на основе нулевых значений внутри, а затем щелкните Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки диалог, вам необходимо:
(1) Выберите Весь ряд вариант в Тип выбора .
(2) Выберите Равно в первом Конкретный тип раскрывающийся список, затем введите номер 0 в текстовое поле.
(3) Щелкните значок OK кнопку.
3. Появится диалоговое окно, в котором указано, сколько строк было выбрано, щелкните значок OK кнопка. Теперь выбраны все строки с нулевыми значениями в указанном диапазоне столбцов. Щелкните правой кнопкой мыши любую выбранную строку, затем щелкните Удалить в контекстном меню. Смотрите скриншот:
Теперь все строки с нулевыми значениями, существующие в указанном столбце, немедленно удаляются. Смотрите скриншот:
Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Удалить строку, если ячейка содержит ноль с Kutools for Excel
Относительные статьи:
- Удалить строки, не содержащие определенного текста в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Содержание
- Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?
- Как удалить строки нулевой длины в Excel, используя VBA?
- Excel не пусто
- Excel говорит мне, что мои пустые ячейки не пустые
- 15 ответов
- Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?
- Функция ЕСЛИ: производим действия в зависимости от условия
- Как работает эта формула?
- Найти первое непустое значение в строке
- 12 наиболее распространённых проблем с Excel и способы их решения
- Вы не даёте заголовки столбцам таблиц
- Пустые столбцы и строки внутри ваших таблиц
- На одном листе располагается несколько таблиц
- Данные одного типа искусственно располагаются в разных столбцах
- Разнесение информации по разным листам книги «для удобства»
- Информация в комментариях
- Бардак с форматированием
- Объединение ячеек
- Объединение текста и чисел в одной ячейке
- Числа в виде текста в ячейке
- Если ваша таблица будет презентоваться через LCD проектор
- Страничный режим листа в Excel
Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?
Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.
И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование —Очистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:
- Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
- в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( A1 =1;10;»»)
=IF(A1=1,10,»»)
в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения
А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:
Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value If Not IsArray(avR) Then Redim avR(1 to 1, 1 to 1) avR(1,1) = rR.Value end if For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub
Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
- создаем в книге новый стандартный модуль: Alt+F11 —Insert —Module()
- копируем в созданный модуль приведенный выше код
- выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
- нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString
Короткая видеоинструкция: 
Статья помогла? Поделись ссылкой с друзьями!
Источник
Как удалить строки нулевой длины в Excel, используя VBA?
Я создал инструмент в VBA для Excel, который анализирует файлы .xlsx, которые мы получаем на основе ряда критериев. Одним из них является количество пустых ячеек в наборе данных. Однако, к сожалению, я заметил, что ряд файлов, которые мы получаем, содержат ячейки со строками нулевой длины, которые «неправильно» считаются непустыми ячейками.
Это на самом деле очень полезно для всего процесса, если я просто могу удалить их из файла.
Я много гуглил эту проблему, но единственное решение, которое мне удалось найти, — это пройти по всем ячейкам листа (я также пробовал только константы, а также использовать Find, чтобы найти все zls). Это не очень эффективно, поскольку рабочие листы содержат большое количество данных. РЕДАКТИРОВАТЬ. Я также пытался использовать метод UsedRange.values = UsedRange.values, но при этом удаляются начальные нули, которые мне требуются.
Я также обнаружил, что это работает (- @ — это случайная строка, которая вряд ли будет единственной ячейкой в моих данных, и, если она есть, ее можно удалить):
Но если я делаю это только с помощью одной замены, это не так:
Первый в порядке, но имеет ряд проблем:
- Это занимает вдвое больше времени, чем одна замена
- Не всегда понятно, разбился ли он или продолжал работать
- Если это произойдет сбой, у меня останется несколько ячеек, содержащих — @ -, что не всегда очевидно, и инструмент должен быть в порядке, чтобы его мог использовать тот, кто не может понять VBA.
Итак, мои вопросы:
- Есть ли способ сделать это, используя только одну замену? Почему замена работает с двойной заменой, а не единственной?
- Если первое невозможно, то есть ли способ «откатить» замену в случае сбоя кода?
- Есть ли способ обновления строки состояния, чтобы показать, как далеко заходит замена, чтобы доказать, что код работает (так же, как при запуске замены в самом Excel)?
- Или есть просто лучший способ сделать все это?
РЕДАКТИРОВАТЬ. К сожалению, из-за обрабатываемых данных мне необходимо сохранить форматирование, включая начальные нули .
РЕДАКТИРОВАТЬ: Вот пример данных, на которые я смотрю. Я хочу заменить строки нулевой длины (которые представляют собой непустые ячейки, в которых нет значения), чтобы они были действительно пустой ячейкой.
Источник
Excel не пусто
Excel говорит мне, что мои пустые ячейки не пустые
поэтому в excel я пытаюсь избавиться от пустых ячеек между моими ячейками, в которых есть информация, используя F5 для поиска пустых ячеек, затем Ctrl + — для их удаления и сдвига ячеек вверх. Но когда я пытаюсь это сделать, он говорит мне, что «клеток не найдено».
Я заметил, что если я выбираю свои «пустые» ячейки, Excel все еще считает их: 
что странно. Но если я нажму Delete на эти выбранные ячейки, счетчик исчезнет, а затем я могу перейти F5, пробелы, Ctrl + — и Сдвиньте клетки вверх, и это сработает.
Итак, мой вопрос в том, как я могу это сделать, но с этими пустыми ячейками, которые Excel считает не пустыми? Я попытался пройти и просто нажать delete над пустыми ячейками, но у меня много данных и понял, что это займет слишком много времени. мне нужно найти способ выбрать эти «пустые» ячейки в пределах выбора данных.
заранее спасибо за вашу помощь! 🙂
15 ответов
откровение: некоторые пустые ячейки на самом деле не пустой! Как я покажу, ячейки могут иметь пробелы, новые строки и true empty:
чтобы найти эти ячейки быстро вы можете сделать несколько вещей.
- на =CODE(A1) формула вернет значение#! если клетка действительно пуста, в противном случае вернется. Это число -номер ASCII в =CHAR(32) .
- если вы выделите ячейку и щелкните в строке формул и используйте курсор, чтобы выбрать все.
удаление этих:
если у вас есть только пробел в клетках они могут быть легко удалены с помощью:
- пресс ctrl + h чтобы открыть find и replace.
- введите одно место в найти, оставить заменить на пустой и убедитесь, что у вас есть матч всей ячейки содержание галочкой в опциях.
- пресс заменить все.
если у вас newlines это сложнее и требует VBA:
- щелкните правой кнопкой мыши на вкладке листа > Просмотр кода.
затем введите следующий код. Помните Chr(10) новая строка заменяет это только по мере необходимости, например » » & Char(10) это пробел и новая строка:
Теперь запустите ваш код нажатие Ф5 .
после файл поставляется: выберите диапазон интересов для повышения производительности, затем выполните следующие действия:
простой способ выбрать и очистить эти пустые ячейки, чтобы сделать их пустыми:
- пресс ctrl + a или предварительно выберите свой диапазон
- пресс ctrl + f
- оставить найти пустой и выберите матч всего содержимого ячейки.
- нажмите найти все
- пресс ctrl + a выбрать все пустые ячейки нашли
- закрыть поиск диалоговое окно
- пресс backspace или удалить
Это сработало для меня:
- CTR-H для поиска и замены
- оставьте «найти что» пустой
- изменить «заменить на» на уникальный текст, то, что вы
положительный не будет найден в другой ячейке (я использовал ‘xx’) - клик «Заменить Все»
- скопируйте уникальный текст на Шаге 3, чтобы найти то, что’
- удалить уникальный текст в «заменить на»
- нажмите «заменить все»
У меня была аналогичная проблема, когда разбросанные пустые ячейки из экспорта из другого приложения все еще отображались в подсчетах ячеек.
мне удалось очистить их
- выбор столбцов / строк, которые я хотел очистить, а затем делать
- «найти» [без текста] и» заменить » [слово выбора].
- затем я сделал «найти» [слово выбора] и» заменить » на [нет текста].
он избавился от всех скрытых / фантомных персонажей в тех ячейки. Может, это сработает?
все, это довольно просто. Я пытался сделать то же самое, и это то, что сработало для меня в VBA
С уважением, Ананд Ланка
Если у вас нет форматирования или формул, которые вы хотите сохранить, вы можете попробовать сохранить файл в виде текстового файла с разделителями табуляции, закрыть его и снова открыть с помощью excel. Это сработало для меня.
нашел другой способ. Установите автофильтр для все столбцы (важно или вы будете смещать данные), выбрав строку заголовка > вкладка «данные» > сортировка и фильтр — «фильтр». Используйте раскрывающийся список в первом столбце данных, снимите флажок «выбрать все» и выберите только «(пробелы) » > [OK]. Выделите строки (Теперь все вместе) > щелкните правой кнопкой мыши > «удалить строку». Вернитесь к раскрывающемуся меню > ‘Select all’. Престо:)
Не уверен, что это уже было сказано, но у меня была аналогичная проблема с ячейками, ничего не показывающими в них, но не пустыми при запуске формулы IsBlank ().
Я выбрал весь столбец, выбрал Find & Replace, нашел ячейки ни с чем и заменил на 0, затем снова запустил find и replace, найдя ячейки с 0 и заменив их на «».
Это решило мою проблему и позволило мне искать пустые ячейки (F5, специальные, пробелы) и удалять строки, которые были пусты. БУМ.
может не работать для каждого приложения, но это решило мою проблему.
иногда в ячейках есть пробелы, которые кажутся пустыми, но если вы нажмете F2 на ячейке, вы увидите пробелы. Вы также можете выполнить поиск таким образом, если знаете точное количество пробелов в ячейке
Это работает с цифрами.
Если ваш диапазон O8:O20, то в соседнем пустом диапазоне (например, T8: T20) введите =O8/1 и заполните. Это даст вам результат #VALUE для «пустых» ячеек, и ваш исходный номер останется прежним.
затем с выбранным диапазоном T8: 20 (CTL -*, если это еще не так) нажмите F5 и выберите специальный. В специальном диалоге выберите ошибки и нажмите кнопку ОК. Это отменит выбор фактических номеров, оставив только выбранные ячейки #VALUE. Удалите их, и у вас будут фактические пустые ячейки. Скопируйте T8:T20 и вставьте обратно через O8: O20.
по существу, поскольку пустые ячейки не работают, вам нужно преобразовать «пустые» ячейки во что-то, что может зацепиться за специальное. Любое действие, которое будет преобразовано в #VALUE, будет работать, и другие типы «ошибок» также должны поддерживаться.
мой метод похож на предложение Курта выше о сохранении его как файла с разделителями табуляции и повторном импорте. Предполагается, что данные имеют только значения без формул. Это, вероятно, хорошее предположение, потому что проблема «плохих» пробелов вызвана путаницей между пробелами и нулями-обычно в данных, импортированных из какого-то другого места, — поэтому не должно быть никаких формул. Мой метод —разобрать на месте — очень похоже на сохранение в виде текстового файла и повторный импорт, но вы можете сделать это без закрытия и повторного открытия файла. Он находится в разделе Данные > текст в Столбцы > разделители > удалить все символы синтаксического анализа (также можно выбрать текст, если хотите) > готово. Это должно заставить Excel повторно распознавать ваши данные с нуля или из текста и распознавать пробелы как действительно пустые. Вы можете автоматизировать это в подпрограмме:
вы также можете включить эту прокомментированную строку, чтобы эта подпрограмма выполнялась «в фоновом режиме». Для этой подпрограммы улучшается производительность только немного (для других это действительно может помочь). Имя-F2Enter, потому что исходный ручной метод для исправления этой проблемы «пробелов» — заставить Excel распознать формулу, нажав F2 и Enter.
вот как я исправил эту проблему без какого-либо кодирования.
- выберите весь столбец, из которого я хотел удалить «пустые» ячейки.
- щелкните вкладку Условное форматирование вверху.
- Выберите «Новое Правило».
- нажать «форматировать только ячейки, которые содержат».
- изменить «между» На «равно».
- нажмите на поле рядом с полем» равно».
- нажмите одну из проблемных» пустых » ячеек.
- клик кнопка формат.
- выберите случайный цвет для заполнения коробки.
- нажмите «OK».
- Это должно изменить все проблемные» пустые » ячейки на цвет, который вы выбрали. Теперь щелкните правой кнопкой мыши одну из цветных ячеек и перейдите в раздел «сортировать» и «поместить выбранный цвет ячейки сверху».
- это поставит все проблемные ячейки в верхней части столбца, и теперь все ваши другие ячейки останутся в исходном порядке, в котором вы их поместите. Теперь вы можете выбрать все проблемные ячейки в одной группе и нажмите кнопку Удалить ячейку сверху, чтобы избавиться от них.
самым простым решением для меня было:
1) Выберите диапазон и скопируйте его (ctrl+c)
2)Создайте новый текстовый файл (в любом месте, он будет удален в ближайшее время), откройте текстовый файл, а затем вставьте в excel информацию (ctrl+v)
3) Теперь, когда информация в Excel находится в текстовом файле, выполните select all в текстовом файле (ctrl+a), а затем скопируйте (ctrl+c)
4) перейдите к началу исходного диапазона на шаге 1 и вставьте его старая информация из копии на Шаге 3.
готово! Больше никаких фальшивых заготовок! (теперь вы можете удалить временный текстовый файл)
Goto — > Special — >blanks не любит объединенные ячейки. Попробовать unmerging клеток выше диапазона, в котором вы хотите выбрать то, болванками попробовать снова.
У меня была аналогичная проблема с получением формулы COUNTA для подсчета непустых ячеек, она считала все из них (даже пустые как непустые), я попытался =CODE (), но у них не было пробелов или новых строк.
Я обнаружил, что когда я щелкнул в ячейке, а затем щелкнул из нее, формула будет считать ячейку. У меня были тысячи ячеек, поэтому я не мог сделать это вручную. Я написал этот оператор VBA, чтобы буквально проверить все ячейки, и если они были пустыми, то сделать их пустыми. Игнорируйте бессмысленность этого макроса и поверьте мне, что он действительно работал, заставляя Excel распознавать пустые ячейки как пустые.
Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?
Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.
И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование —Очистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:
- Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
- в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( A1 =1;10;»»)
=IF(A1=1,10,»»)
в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения
А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:
Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub
Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
- создаем в книге новый стандартный модуль: Alt+F11 —Insert —Module()
- копируем в созданный модуль приведенный выше код
- выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
- нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString
Короткая видеоинструкция:
Статья помогла? Поделись ссылкой с друзьями!
Функция ЕСЛИ: производим действия в зависимости от условия
Чтобы выполнить действие только тогда, когда ячейка не пуста (содержит какие-то значения), вы можете использовать формулу, основанную на функции ЕСЛИ.
В примере ниже столбец F содержит даты завершения закупок шоколада.
Поскольку даты для Excel — это числа, то наша задача состоит в том, чтобы проверить в ячейке наличие числа.
Формула в ячейке F3:
Как работает эта формула?
Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек, значит, пустых ячеек нет и можно производить вычисление. Если равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.
Согласитесь, что нельзя назвать этот способ определения наличия пустых ячеек удобным. Ведь число строк в таблице может измениться, и нужно будет менять формулу: вместо цифры 7 ставить другое число.
Давайте рассмотрим и другие варианты. В ячейке F6 записана большая формула —
Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.
Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. В противном случае — производим вычисления.
Все достаточно просто, но перечислять кучу ссылок на ячейки не слишком удобно. К тому же, здесь, как и в предыдущем случае, формула не масштабируема: при изменении таблицы она нуждается в корректировке. Это не слишком удобно, да и забыть можно сделать это.
Рассмотрим теперь более универсальные решения.
В качестве условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.
И, наконец, еще одна формула Excel, которая позволит производить расчет только при наличии непустых ячеек.
Функция ЕЧИСЛО ( или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.
А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.
Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.
А теперь рассмотрим как проверить, что ячейки не пустые, если в них могут быть записаны не только числа, но и текст.
Итак, перед нами уже знакомая формула
Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.
То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.
А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.
А теперь вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.
Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями и функцией ЕСЛИ в Excel.
Найти первое непустое значение в строке
Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).
Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве
Скачать заметку в формате Word или pdf, примеры в формате Excel
Решение: формула в А2: =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0). Хотя эта формула имеет дело с массивом ячеек, она в конечном счете возвращает одно значение, так что использовать при вводе нажатие Ctrl+Shift+Enter не требуется (о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).
Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: .
Обратите внимание, что далее этот массив вычитается из 1. При попытке использовать значения ИСТИНА и ЛОЖЬ в математической формуле, значение ИСТИНА интерпретируется как 1, а значение ЛОЖЬ – как 0. Задавая 1-ЕПУСТО(С2:К2), вы преобразуете массив логических значений ИСТИНА/ЛОЖЬ в числовую последовательность нулей и единиц: .
Итак, фрагмент формулы 1-ЕПУСТО(С2:К2) возвращает массив . Это немного странно, так как от такого фрагмента Excel ожидает, что вернется одно значение. Странно, но не смертельно. Функция ИНДЕКС также обычно возвращает одно значение. Но вот, что написано в Справке Excel: Если указать в качестве аргумента номер_строки или номер_столбца значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца.
Если функция ИНДЕКС возвращает массив, ее можно использовать внутри других функций, ожидающих, что аргумент является массивом.
Итак, указав в качестве третьего аргумента функции ИНДЕКС(1-ЕПУСТО(C2:K2);1; ) значение ноль, мы получим массив .
Функция ПОИСКПОЗ выполняет поиск искомого значения в одномерном массиве и возвращает относительную позицию первого найденного совпадения. Формула =ПОИСКПОЗ(1,МАССИВ,0) просит Excel найти номер ячейки в МАССИВЕ, которая содержит первую встретившуюся единицу. Функция ПОИСКПОЗ определяет в каком столбце содержится первая непустая ячейка. Когда вы просите ПОИСКПОЗ найти первую 1 в массиве , она возвращает 3.
Итак =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) превращается в =ПОИСКПОЗ(1; ;0) и возвращает результат 3.
В этот момент, вы знаете, что третий столбец строки С2:К2 содержит первое непустое значение. Отсюда довольно просто, используя функцию ИНДЕКС, узнать само это первое непустое значение: =ИНДЕКС(МАССИВ;1;3) или =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0)).
Рис. 2. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки
Дополнительные сведения: если все ячейки пустые, то формула возвращает ошибку #Н/Д.
Альтернативные стратегии: когда вы из единицы вычитаете значение ЕПУСТО, вы преобразуете логические значения ИСТИНА/ЛОЖЬ в числовые 1/0. Вы могли бы пропустить этот шаг, но тогда вам придется искать ЛОЖЬ в качестве первого аргумента функция ПОИСКПОЗ: =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(ЛОЖЬ;ИНДЕКС(ЕПУСТО(C2:K2);1;0);0)).
12 наиболее распространённых проблем с Excel и способы их решения
Представляем вам гостевой пост, из которого вы узнаете, как избежать самых распространённых проблем с Excel, которые мы создаём себе сами.
Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.
Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».
Вы не даёте заголовки столбцам таблиц
Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.
Пустые столбцы и строки внутри ваших таблиц
Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.
На одном листе располагается несколько таблиц
Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.
Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?
Данные одного типа искусственно располагаются в разных столбцах
Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:
Казалось бы, перед нами безобидный формат для накопления информации по продажам агентов и их штрафах. Подобная компоновка таблицы хорошо воспринимается человеком визуально, так как она компактна. Однако, поверьте, что это сущий кошмар — пытаться извлекать из таких таблиц данные и получать промежуточные итоги (агрегировать информацию).
Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.
Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.
Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.
Рекомендуемый формат таблицы выглядит так:
Разнесение информации по разным листам книги «для удобства»
Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.
Информация в комментариях
Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.
Бардак с форматированием
Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:
- Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
- Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
- Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.
Объединение ячеек
Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.
Объединение текста и чисел в одной ячейке
Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.
Числа в виде текста в ячейке
Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.
Если ваша таблица будет презентоваться через LCD проектор
Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.
Страничный режим листа в Excel
Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.
Ещё больше полезной информации про Excel можно узнать на сайте Дениса.
Источник
Скрыть значения равные 0 во всех ячейках на листе можно через Параметры MS EXCEL (далее нажмите Дополнительно/ Раздел Показать параметры для следующего листа/ , затем снять галочку Показывать нули в ячейках, которые содержат нулевые значения ).
Скрыть значения равные 0 в отдельных ячейках можно используя пользовательский формат, Условное форматирование или значение Пустой текст («») .
Пользовательский формат
Пользовательский формат вводим через диалоговое окно Формат ячеек .
- для вызова окна Формат ячеек нажмите CTRL+1 ;
- выберите (все форматы).
- в поле Тип введите формат # ##0,00;-# ##0,00;
Применение вышеуказанного формата не влияет на вычисления. В Строке формул , по-прежнему, будет отображаться 0, хотя в ячейке ничего не будет отображаться.
Условное форматирование
Используя Условное форматирование , также можно добиться практически такого же результата.
- выделите интересующий диапазон;
- в меню выберите Главная/ Стили/ Условное форматирование/ Правила выделения ячеек/ Равно );
- в левом поле введите 0;
- в правом поле выберите пользовательский формат (белый цвет шрифта);
Теперь нулевые значения отображаются белым шрифтом и не видны на стандартном белом фоне.
Если фон поменять, то нули станут снова видны, в отличие от варианта с пользовательским форматом, который не зависит от фона ячейки. Даже если просто выделить диапазон, то нулевые ячейки будут слегка видны.
Значение Пустой текст («»)
Замечательным свойством значения Пустой текст является, то что оно не отображается в ячейке (например, введите формулу =»» ). Записав, например, в ячейке B1 формулу =ЕСЛИ(A1;A1;»») получим пустую ячейку В1 в случае, если в А1 находится значение =0. Столбец А можно скрыть.
Как не показывать 0 в Excel, если это не нужно
Как не показывать 0 в Эксель? В версиях 2007 и 2010 жмите на CTRL+1, в списке «Категория» выберите «Пользовательский», а в графе «Тип» — 0;-0;;@. Для более новых версий Excel жмите CTRL+1, а далее «Число» и «Все форматы». Здесь в разделе «Тип» введите 0;-0;;@ и жмите на «Ок». Ниже приведем основные способы, как не отображать нули в Excel в ячейках для разных версий программы — 2007, 2010 и более новых версий.
Как скрыть нули
Существует много способов, как не показывать 0 в Excel, если в них нет необходимости. Здесь алгоритм действий немного отличается в зависимости от версии программы.
Версия 2007 и 2010
При наличии под рукой версии 2007 или 2010 можно внести изменения следующими методами.
Числовой формат
Наиболее простой способ, как не показывать нулевые значения в Excel — скрыть данные с помощью форматирования. Сделайте следующие шаги:
- Выделите ячейки с цифрами «0», которые нужно не показывать.
- Кликните на CTRL+1 или зайдите в раздел «Главная», а далее «Ячейки» и «Формат».
- В разделе «Категория» формата ячеек кликните на «Пользовательский»/«Все форматы».
- В секции «Тип» укажите 0;-0;;@.
Скрытые параметры показываются только в fx или в секции, если вы редактируете данные, и не набираются.
Условное форматирование
Следующий метод, как в Экселе не показывать нулевые значения — воспользоваться опцией условного форматирования. Сделайте следующие шаги:
- Выделите секцию, в которой имеется «0».
- Перейдите в раздел «Главная», а далее «Стили».
- Жмите на стрелку возле кнопки «Условное форматирование».
- Кликните «Правила выделения …».
- Выберите «Равно».
- Слева в поле введите «0».
- Справа укажите «Пользовательский формат».
- В окне «Формат …» войдите в раздел «Шрифт».
- В поле «Цвет» выберите белый.
Указание в виде пробелов / тире
Один из способов, как в Excel не показывать 0 в ячейке — заменить эту цифру на пробелы или тире. Для решения задачи воспользуйтесь опцией «ЕСЛИ». К примеру, если в А2 и А3 находится цифра 10, а формула имеет вид =А2-А3, нужно использовать другой вариант:
- =ЕСЛИ(A2-A3=0;»»;A2-A3). При таком варианте устанавливается пустая строка, если параметр равен «0».
- =ЕСЛИ(A2-A3=0;»-«;A2-A3). Ставит дефис при 0-ом показателе.
Сокрытие данных в нулевом отчете Excel
Как вариант, можно не отображать нулевые значения в Excel в отчете сводной таблицы. Сделайте следующие шаги:
- Войдите в «Параметры», а в разделе «Параметры сводной таблицы» жмите на стрелку возле пункта с таким же названием и выделите нужный раздел.
- Кликните на пункт «Разметка и формат».
- В секции изменения способа отображения ошибок в поле «Формат» поставьте «Для ошибок отображать», а после введите в поле значения. Чтобы показывать ошибки в виде пустых ячеек удалите текст из поля.
- Еще один вариант — поставьте флажок «Для пустых ячеек отображать» и в пустом поле введите интересующий параметр. Если нужно, чтобы поле оставалось пустым, удалите весь текст.
Для более новых версий
Ситуация обстоит иным образом, если нужно не показывать нули в Excel более новой версии. В таком случае воспользуйтесь одним из предложенных ниже решений.
Как не показывать 0 в выделенных ячейках
Это действие позволяет не отображать «0» в Excel с помощью числовых форматов. Спрятанные параметры показываются только в панели формул и не распечатываются. Если параметр в одной из секций меняется на нулевой число, параметр отобразится в ячейке, а формат будет правильным.
- Выделите место таблицы, в котором имеется «0» в Excel.
- Жмите CTRL+1.
- Кликните «Число» и «Все форматы».
- В поле «Тип» укажите 0;-0;;@.
- Кликните на кнопку «ОК».
Скрытие параметров, которые возращены формулой
Следующий способ, как не отображать 0 в Excel — сделать следующие шаги:
- Войдите в раздел «Главная».
- Жмите на стрелку возле «Условное форматирование».
- Выберите «Правила выделения ячеек» больше «Равно».
- Слева введите «0».
- Справа укажите «Пользовательский формат».
- В разделе «Формат ячейки» введите «Шрифт».
- В категории «Цвет» введите белый и жмите «ОК».
Отражение в виде пробелов / тире
Как и в более старых версиях, в Экселе можно не показывать ноль, а ставить вместо него пробелы / тире. В таком случае используйте формулу =ЕСЛИ(A2-A3=0;»»;A2-A3). В этом случае, если результат равен нулю, в таблице ничего не показывается. В иных ситуациях отображается А2-А3. Если же нужно подставить какой-то другой знак, нужно между кавычками вставить интересующий знак.
Скрытие 0-х параметров в отчете
Как вариант, можно не показывать 0 в Excel в отчете таблицы. Для этого в разделе «Анализ» в группе «Сводная таблица» жмите «Параметры» дважды, а потом войдите в «Разметка и формат». В пункте «Разметка и формат» сделайте следующие шаги:
- В блоке «Изменение отображения пустой ячейки» поставьте отметку «Для пустых ячеек отображать». Далее введите в поле значение, которое нужно показывать в таблице Excel или удалите текст, чтобы они были пустыми.
- Для секции «Изменение отображения ошибки» в разделе «Формат» поставьте отметку «Для ошибок отображать» и укажите значение, которое нужно показывать в Excel вместо ошибок.
Как показывать нулевые значения
Иногда возникает обратная ситуация, когда нужно показать 0 в Excel.
Для Эксель 2007 и 2010
Для Excel 2007 и 2010 сделайте следующие шаги:
- Выберите «Файл» и «Параметры».
- Сделайте «Дополнительно».
- В группе «Показать параметры для следующего листа». Чтобы показать «0», нужно установить пункт «Показывать нули в ячейках, которые содержат 0-ые значения».
Еще один вариант:
- Жмите CTRL+1.
- Кликните на раздел «Категория».
- Выберите «Общий».
- Для отображения даты / времени выберите нужный вариант форматирования.
Для более новых версий
Для Эксель более новых версий, чтобы показывать 0, сделайте следующее:
- Выделите секции таблицы со спрятанными нулями.
- Жмите на CTRL+1.
- Выберите «Число», а далее «Общий» и «ОК».
Это основные способы, как не показывать 0 в Excel, и как обратно вернуть правильные настройки. В комментариях расскажите, каким способом вы пользуетесь, и какие еще имеются варианты.
Удаление нулевых значений в Microsoft Excel
При использовании формул в Экселе, в случае, если ячейки, на которые ссылается оператор, пусты, в области расчетов по умолчанию будут стоять нули. Эстетически это не очень красиво выглядит, особенно, если в таблице очень много подобных диапазонов с нулевыми значениями. Да и пользователю труднее ориентироваться в данных по сравнению с ситуацией, если бы подобные области были бы вообще пустыми. Давайте выясним, какими способами можно удалить отображение нулевых данных в Excel.
Алгоритмы удаления нулей
Эксель предоставляет возможность произвести удаление нулей в ячейках несколькими способами. Это можно сделать, как использовав специальные функции, так и применив форматирование. Также существует возможность отключение отображение подобных данных в целом по листу.
Способ 1: настройки Excel
Глобально данный вопрос можно решить, изменив настройки Эксель для текущего листа. Это позволяет сделать абсолютно все ячейки, содержащие нули, пустыми.
- Находясь во вкладке «Файл», переходим в раздел «Параметры».
После этих действий все ячейки текущего листа, которые содержат нулевые значения, будут отображаться, как пустые.
Способ 2: применение форматирования
Скрыть значения пустых ячеек можно при помощи изменения их формата.
- Выделяем диапазон, в котором нужно скрыть ячейки с нулевыми значениями. Кликаем по выделяемому фрагменту правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек…».
Теперь во всех областях, которые содержат нулевые значения, будет пусто.
Способ 3: условное форматирование
Для удаления лишних нулей можно также применить такой мощный инструмент, как условное форматирование.
- Выделяем диапазон, в котором могут содержаться нулевые значения. Находясь во вкладке «Главная», кликаем по кнопке на ленте «Условное форматирование», которая размещена в блоке настроек «Стили». В открывшемся меню последовательно переходим по пунктам «Правила выделения ячеек» и «Равно».
Теперь при условии, если значение в ячейке будет нулевым, то оно будет невидимым для пользователя, так как цвет его шрифта сольется с цветом фона.
Способ 4: применение функции ЕСЛИ
Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ.
- Выделяем первую ячейку из того диапазона, в который выводятся результаты вычислений, и где возможно будут присутствовать нули. Кликаем по пиктограмме «Вставить функцию».
Кстати, если в окне аргументов в поле «Значение если истина» установить тире, то при выводе результата в ячейках с нулевым значением будет не пробел, а прочерк.
Способ 5: применение функции ЕЧИСЛО
Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО.
- Как и в предыдущем примере, открываем окно аргументов функции ЕСЛИ в первой ячейке обрабатываемого диапазона. В поле «Логическое выражение» записываем функцию ЕЧИСЛО. Эта функция показывает, заполнен ли элемент данными или нет. Затем в том же поле открываем скобки и вписываем адрес той ячейки, которая в случае, если она пустая, может сделать нулевой целевую ячейку. Закрываем скобки. То есть, по сути, оператор ЕЧИСЛО проверит, содержатся ли какие-то данные в указанной области. Если они есть, то функция выдаст значение «ИСТИНА», если его нет, то — «ЛОЖЬ».
А вот значения следующих двух аргументов оператора ЕСЛИ мы переставляем местами. То есть, в поле «Значение если истина» указываем формулу расчета, а в поле «Значение если ложь» ставим пробел – « ».
Существует целый ряд способов удалить цифру «0» в ячейке, если она имеет нулевое значение. Проще всего, отключить отображения нулей в настройках Excel. Но тогда следует учесть, что они исчезнут по всему листу. Если же нужно применить отключение исключительно к какой-то конкретной области, то в этом случае на помощь придет форматирование диапазонов, условное форматирование и применение функций. Какой из данных способов выбрать зависит уже от конкретной ситуации, а также от личных умений и предпочтений пользователя.
Мы рады, что смогли помочь Вам в решении проблемы.