Удалить текст в excel макрос
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:
- Выделяем таблицу с данными, включая заголовки. Если их нет - то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
- устанавливаем фильтр:
- для Excel 2003 : Данные-Фильтр-Автофильтр
- для Excel 2007-2010 : вкладка Данные (Data) -Фильтр (Filter)(или вкладка Главная (Home) -Сортировка и фильтр (Sort&Filter) -Фильтр (Filter) )
Теперь выбираем условие для фильтра:
- в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
- Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме
После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк - все?! Как избежать.
Способ второй:
применить код VBA, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.
УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.Sub Del_Array_SubStr() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем Dim rr As Range For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = 1 To lLastRow 'цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li DoEvents Next lr If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" - надо удалить, т.к. есть слово "привет"), то надо строку:
If CStr(arr(li, 1)) = sSubStr Then
заменить на такую:
If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then
УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.'процедура оставляет в листе только те значения, которые перечислены в списке Sub LeaveOnlyFoundInArray() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr Dim IsFind As Boolean lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем Dim rr As Range For li = 1 To lLastRow 'цикл с первой строки таблицы до конца IsFind = False For lr = 1 To UBound(avArr, 1) 'цикл по списку значений на удаление sSubStr = avArr(lr, 1) If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then IsFind = True End If DoEvents Next lr 'если значение таблицы не найдено в списке - удаляем строку If Not IsFind Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше сравнивал значения таблицы со значениями списка по точному совпадению слов, а не по частичному, то надо строку:
макрос удалит на листе все строки, в которых содержится искомый текст:
Чтобы вместо удаления просто скрыть такие строки, замените строку
Расширенная версия этого макроса - с использованием UserForm для ввода искомого значения
Ещё один вариант кода, позволяющего выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:
Комментарии
Относительно вопроса от 9 Сен 2011 - 20:12. (пишет тот же гость)
М.б. это происходит потому что мы назначаем массив с данными которые описываем функцией array, а соотв другой массив, который и нужно удалить он не видит.Пробую использовать простую функцию циклическую, работает только при одном условии!! При нескольких уже нет, почему так происходит, подскажите, пжта, как можно решить эту проблему.
Добрый день!
Вы знаете, пробовал и в одном месте убрать NOT, и во всех где оно стоит, но ничего не получается. Подскажите, пжта, что делаю неправильно? Цель - удалить строки, в которых НЕ содержится текст. СпасибО!Это на 26 комментарий замечание.
Почему-то если значений для исключения больше одного, то удаляет все строки.
а если условия в виде выделенного диапазона или в форме ввести возможность добавления диапазона как условия?
Много условий можно записать в таком виде:
Проблема в том, что я не знаю, как в данном макросе сделать удобным ввод нескольких условий (а то давно бы реализовал)
Вариантов тут несколько, но они все недостаточно удобны:
1) писать все условия в коде - в принципе неправильно
2) вводить условия через форму ввода - можно, но там надо либо делать много полей (а это куча кода), либо одно большое поле, но тогда не будет видно, где заканчивается предыдущее условие, и начинается следующее (если текст условий не влазит в форму по длине в одну строку)
Да ещё и специально сохранять условия требуется - чтобы при следующем запуске макроса их снова не вводить.3) загружать список условий с листа - но придётся где-то в макросе прописывать код, с какого листа скаких ячеек брать данные
4) текстовый файл со списком условий - тоже не совсем удобно может быть
Одной строчкой все условия у меня не влезают, excel начинает ругаться. Я их разбил но незнаю как их объединит, чтобы макрос по очереди все условия отрабатывал?
СпасибоSub УдалениеСтрокПоНесколькимУсловиям()
Dim ra As Range, delra As Range
Application.ScreenUpdating = False ' отключаем обновление экрана' ищем и удаляем строки, содержащие заданный текст
' (можно указать сколько угодно значений, и использовать подстановочные знаки)
УдалятьСтрокиСТекстом = Array("Наименование *", "Количество", _
"текст?", "цен*сти", "*78*")
УдалятьСтрокиСТекстом = Array("Наименование *", "Количество", _
"текст?", "цен*сти", "*78*")
УдалятьСтрокиСТекстом = Array("Наименование *", "Количество", _
"текст?", "цен*сти", "*78*")' если подходящие строки найдены, то: (оставьте одну из 2 следующих строк)
If Not delra Is Nothing Then delra.EntireRow.Hidden = True ' скрываем их
If Not delra Is Nothing Then delra.EntireRow.Delete ' удаляем их
End Subмакрос удалит на листе все строки, в которых содержится искомый текст:
Чтобы вместо удаления просто скрыть такие строки, замените строку
Расширенная версия этого макроса - с использованием UserForm для ввода искомого значения
Ещё один вариант кода, позволяющего выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:
Комментарии
это просто - добавьте вложенное условие:
If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then
if ra.Find("Северо-западный федеральный округ", , xlValues, xlPart) is Nothing then
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
end if
End IfЕще вопрос по удалению. Мне нужно удалить строчки, допустим, "Центральный федеральный округ", "Приволжский федеральный округ", "Уральский федеральный округ", "Сибирский федеральный округ". И я для этого благополучно пользуюсь данным макросом, прописав строки, которые необходимо удалить. Затем замечаю что можно не прописывать эти Array, а сократить процедуру, прописав "*федеральный округ*", то есть, проставив звездочки. Прекрасно. Оптимизировали процедуру, подсократили текст. Но есть одна загвоздка. Мне нужно чтобы строчка "Северо-Западный федеральный округ" осталась. Как в данном макросе учитывать исключения? Ведь при данной процедуре эта строка тоже будет удалена.
Хорошо, спасибо. Кстати, идея с константой xlWhole лучше чем с константой xlPart. Даже если мне понадобится неточное соответствие для какой-то отдельной строчки, я могу просто приписать звездочку * для этой отдельной строчки и её этот макрос тоже удалит как надо.
Да, всё можно сделать. Оформляйте заказ (кнопка выше), прикладывайте примеры, опишите, что хотите получить, либо полистайте комментарии к данной статье, там есть ответы, которые помогут найти правильный путь. Кстати, ответ на Ваш предыдущий вопрос в комментариях тоже был.
Да, работает, благодарю. А подскажите, можно ли на основе данного макроса сделать немного другую процедуру - не удаление строк с заданным условием, а копирование строки с заданным условием. Например, чтобы строка "Северо-Западный округ" макросом вырезалась и помещалась повыше под строку "Российская Федерация". Вроде тоже простейшая операция, не пойму что заменить в процедуре.
Замените константу xlPart на xlWhole. В этом случае макрос будет искать точное соответствие Вашей фразе.
Но обратите внимание, что после работы макроса в стандартной форме Эксель "Найти" (Ctrl+f) будет стоять галочка "Ячейка целиком", если для повседневной работы она не нужна, то снимите ее.
Подскажите, пожалуйста. Удаление строк по условию в моем случае оказывает медвежью услугу. Есть следующие строки:
-Архангельская область:
-в том числе:
-Ненецкий авт. округ
-Архангельская область без автономных округовМне нужно удалить строку "Архангельская область". Но когда я её прописываю для поиска, то удаляет и строку с надписью "Архангельская область без автономных округов". Как задать точность в данном макросе, чтобы предотвратить удаление всех строк где встречается данная фраза?
Спасибо за ценные макросы и за помощь!)
Всё правильно у вас, - просто вы скопировали, но не указали, куда вставить
попробуйте написать последнюю строку макроса в таком виде:
Добрый день. Подскажите пожалуйста как скопировать, а не удалить строки в Вашем коде "поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям". При этом что бы поиск значений велся по определенному столбцу. У меня получилось так! Подскажите что неправильно?
Sub qwe()
Dim ra As Range, delra As Range
Application.ScreenUpdating = FalseText = Array("qwe", "krt", _
"ghj")For Each ra In Range("G2", [G15].End(xlDown))
For Each word In Text
If Not ra.Find(word, , xlValues, xlPart) Is Nothing Then
If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
End If
Next word
NextIf Not delra Is Nothing Then delra.EntireRow.Copy
End SubПодскажите пожалуйста, как сделать, чтобы в макросе:
1) вылезла табличка с выбором нескольких данных (например четыре города)
2) выбираем (например) г. Самара и все строки где содержится этот текст остаются, остальные удаляются
Спасибо!Добрый день. Подскажите пожалуйста, возможно сделать скрытие или удаление не строк, а столбцов.
Неправильно считает количество удаленных строк.
Большое СПАСИБО прямь ВЫ Выручили меня.
чтобы по точному совпадению искалось, - замените в макросе xlPart на xlWhole
очень большая просьба, вот какая штука мне нужно скрыть строки в которых сумма по 13 столбцу равна 0. я смог сделать выборку по 13 столбцу,но вот скрытие строк происходит не правильно. скрываются строки которые содержат цифру 0 (например 308), в то время как мне нужно сто бы только скрывались строки с конкретным числом - 0. СПАСИБО.
Готовый код не выложу, - но могу сделать под заказ.
Самый простой вариант (как вручную сделать):
в первом дополнительном столбце написать формулу, которая округляет время до ближайшей четной секунды,
во втором столбце - сцепить значение с округленным временем,
а в третьем доп столбце написать формулу =СЧЁТЕСЛИ()
А потом удалить все строки (применив автофильтр), где в третьем доп столбце значение больше 1Здравствуйте,
У меня на одном колонке есть повторяющие значения, а на втором колонке есть время. Удаляю я повторящуе значки, только с условием если время не одинаковые, нужно искать и где есть +1 секунд разница и тоже удалит. Как можно это сделать ? Если можно готовый код выложите, Очень нужно, заранее спасибо.Проблема не решена, - так этой проблемы не существует в данном макросе.
Если ничего не найдено, - то ничего и не удаляется.Не понятно как решена проблема с удалением, если искомое значение не найдено. Как убрать ошибку? мозг кипит.
Здравствуйте. Не могу разобраться, что прописать в приведенном коде, чтоб строки перед удалением копировались не на новый лист, а определённый. К примеру на лист "Архив". Подскажите пожалуйста, если не трудно.
Подскажите пожалуйста как можно удалить во всём столбце числа, которые содержат более 3-х знаков
Здравствуйте, Дмитрий
Тут можно не макрос исправлять, а в дополнительном чтолбце написать формулу типа =СЧЁТЕСЛИ(), которая вычислит количество повторений значения в столбце А
И потом по этому доп столбцу включит автольфильтр, оставив только значения = 1 (макрос вообще не нужен)
С учетом правила по столбцу V - формула просто чуть сложнее получитсяДобрый день!
Прочитал все коментарии в надежде найти подходящий вариант, но не нашел.
Ситуация следующая в столбце А несколько тысяч значений, которые хаотично повторяются т.е.
и т.д. в столбце V напротив этих значений бывают пустые ячейки.A V
13 yes
13 yes
50 yes
11 yes
11
11 yes
42
41 yesМожно ли переделать макрос так, что бы скрывались строки с одинаковым значением в столбце А, если в этих строках хоть одна ячейка в столбце V пустая?
Т.е. в моем примере скрыть все строки с 11 и 42, а 13, 50, и 41 оставить?Здравствуйте, Антон.
Да, можно сделать, - но это будет совсем другой макрос
Можем сделать под заказ.Здрасте, у меня проблема с удалением строк одинаковой высоты на листе. Можно ли вообще так сделать что бы макрос удалял строки одинаковые по высоте?
Антон, вместо ActiveSheet.UsedRange
можно указать конкретный диапазон, - например, Range("a1:f300")Добрый день!
Все прочитал, но никак не понимаю как ввести диапазон поиска в Ваш макрос.на листе имеется 3 таблицы по 300 строк с объединенными ячейками. нужно скрывать по значению.
Sub УдалениеСтрокСОбъединённымиЯчейками()
Application.ScreenUpdating = False ' отключаем обновление экрана
On Error Resume Next ' отключаем остановку при ошибкеТекстДляПоиска = "0" ' удаляем строки с таким текстом
Замените строку ТекстДляПоиска = "Наименование ценности"
на строку ТекстДляПоиска = [B4]Подскажите пожалуйста. Скрываю строки на листе1, нужно скрыть те же строки на соседнем листе2 автоматически.
Дмитрий, а вы пролистайте комментарии к статье, — там найдете решение (уже отвечал на этот вопрос)
Подскажите, а что изменить в коде, если надо искать в определённом столбце только? А удалять по прежнему строки.
Здравствуйте, Олег
Если готовы оплатить помощь, - оформляйте заказ на сайте, сделаем.
Если хотите бесплатно, - обратитесь на форумы по ExcelУдаление (скрытие) строк по условию. Кто поможет с таблицой консигнация скачал образец, дальше я чайник могу выслать фаил
Да - неудобное и неправильное решение. Про надстройку почитаю спасибо, и еще я не знаю как обращаться из макроса к данным на листах :( Нет ли на примете чего нибудь почитать на эту тему? Можно в ЛС.
И кроме того у меня, к сожалению, не дошли руки автоматизировать последние несколько % рутинной бредо-работы с этими документами (из того что можно автоматизировать:)), но надеюсь в ближайшее время сделаю.
Спасибо в любом случае.
Всех благ.Илья, копировать каждый раз макрос в новый файл, - вам не кажется, что это неудобное / неправильное решение?
Для этого делают надстройку (файл Excel с макросами, скрытый лист с данными, кнопка на панели инструментов)
Запустили надстройку, - на панели инструментов появилась кнопка, - нажали её, макрос запустился и сделал что надоДа, есть ограничение, похоже что 1024 символа.
Да, в большинстве случаев хранить какие-либо данные в коде бредово, однако в моем случае мне просто необходимо хранить массив именно в коде. С помощью одного макроса я обрабатываю много документов регулярно: мне удобно, что все что мне нужно содержится в коде и я просто копирую его в Excel и исполняю на листе. Если выносить значения на отдельный лист и работать с ними таким образом, то это добавит немало хлопот.
Так что придется в случае чего как-то использовать несколько массивов, или может быть вы мне подскажите как указывать элементы массива в несколько строк.В любом случае спасибо большое!
Добрый день! Помогите, пожалуйста, написать код, если нужно удалить строки, содержащие отрицательные значения или 0 в диапазоне A9:F22.
Вероника, тут совсем другой код нужен.
Если только ручная окраска ячеек, - то несложно
Если с учетом условного форматирования, - в десятки раз сложнее будет
Обратитесь на форумы по Excel - там помогут с кодом.Доброго времени суток.
Подскажите, как сделать проверку для скрытия строк не по значению, а по цвету ячейки в определенном столбце?
Ячейка окрашивается в черный цвет по условному форматированию или вручную.
Спасибо.Илья, в VBA есть ограничение на длину строки кода
Сколько конкретно - не помню.
Код - явно не лучшее место для хранения массива значений. Правильнее их хранить на листе Excel (возможно, скрытом)Спасибо огромнейшее! Обе ваши подсказки помогли решить проблему!
А вы не знаете почему у меня при вставке в Microsoft VB For Applications кода, большой массив искомых элементов подсвечивается красным и переноситься на 2ю строку?
Есть какое-то ограничение на максимальное кол-во элементов массива? Или длину строки?
Как в таком случае поместить в массив искомых элементов все которые нужны?Спасибо еще раз!
С уважением, Ильяу нас ведь есть проверка If Not range Is Nothing, которая должна бы не пускать в это условие если диапазон пуст
Ошибка возникает из-за того, что условие If Not hrange Is Nothing Then
распространяется только на первую строку (раскраска), а вторая строка выполняется в любом случае:Правильно будет так:
насчёт ошибки "Данная команда неприменима для несвязных диапазонов" — она возникает при копировании строк: range1.EntireRow.Copy
я бы поискал объединённые ячейки на проблемном листе
(обычно ошибка такая из-за объединённых ячеек)если объединённых ячеек нет, - попробуйте такой вариант:
вместоЯ продолжаю использовать и кастомизировать ваш супер-макрос, сейчас у меня уже вариации на 200 строк, поэтому еще раз выношу глубочайшую благодарность и признательность за его публикацию в паблике и поддержку пользователей.
Ну и естественно, как у человека далекого от VBA и от понимания объектной, и вообще какой-либо, модели excel документа, у меня возникли очередные трудности на этот раз почти мистического характера, когда кажется что 1000% должно работать, но нет.
В общем, самая критичная проблема такая - ваш исходный макрос я привел к следующему виду:
Тут я привел его условно - заменив для большей наглядности содержимое и названия массивов и объектов Range.
Как видите из кода, идея такова: из относительно не маленьких таблиц (1000+ строк на 13 столбцов) классифицировать данные в зависимости от наличия в строках тех или иных значений и скопировать на новый лист (либо с удалением с исходного либо нет).Второй вопрос менее критичный, но не менее абсурдный как по мне: на той же самой команде с копированием найденного Range я получаю ошибку "Object required" или "Object variable or With block variable not set", в случае если на листе не было найдено значения необходимого для записи в этот Range. И тут я не понимаю: у нас ведь есть проверка If Not range Is Nothing, которая должна бы не пускать в это условие если диапазон пуст, но почему то этого не происходит.
И еще одно уточнение-вопрос: в предыдущем ответе на мой вопрос в последнем блоке условий, вы указали что надо дописывать End If, но если я его использую то получаю ошибку компиляции - "End If without block If", хотя в отдельном случае без использования End If я получил ошибку "Block If without End If" и добавление End If'а к каждому блоку If Not решает проблему. Короче, тоже мистика.
Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:
- лишние пробелы перед, после или между словами (для красоты!)
- ненужные символы ("г." перед названием города)
- невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или "кривой" выгрузки из 1С, переносы строк, табуляция)
- апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)
Давайте рассмотрим способы избавления от такого "мусора".
Замена
"Старый, но не устаревший" трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу "найди Маша – замени на Петя", но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим "г." (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы "г." перед названиями городов:
Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов
Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).
Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:
Удаление непечатаемых символов
В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их "зачистить".
Вариантов решения два:
- Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
- Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.
Функция ПОДСТАВИТЬ
Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:
- Текст в котором производим замену
- Старый текст – тот, который заменяем
- Новый текст – тот, на который заменяем
С ее помощью можно легко избавиться от ошибок (замена "а" на "о"), лишних пробелов (замена их на пустую строку ""), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):
Удаление апострофов в начале ячеек
Апостроф (') в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские ("це" вместо русской "эс", "игрек" вместо русской "у" и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert - Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу :)
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех "оптом", используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.
Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная - Найти и выделить - Выделить группу ячеек (Home - Find&Select - Go to special) .
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить - Удалить строки с листа (Delete - Delete rows) или жмём сочетание клавиш Ctrl + минус - и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу - как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик - Visual Basic (Developer - Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .
В открывшемся окне редактора Visual Basic выберите команду меню Insert - Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) - все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий - это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую "умную" сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные - Получить данные - Из таблицы/диапазона (Data - Get Data - From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная - Сократить строки - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована - преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
Читайте также: