Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Перебор ячеек и обработка\замена их значений, Через with Range("A1", Cells(Rows.Count, "A").End(xlUp)) End With
 
#1
28.09.2017 16:39:57
Доброго дня, уважаемые!
Не могу осознать принцип работы следующего макроса и прошу разъяснить на пальцах:
Цитата
16 Май 2013 10:05:26 Казанский написал:
Код
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("INDEX(TRIM(" & .Address & "),)")
End With
По отдельности прочитал\посмотрел значения, но логика всего этого в одну строку не уловил. А также прошу подсказать, как указать текущую ячейку. Как я понял, из-за оператора "Evaluate" ссылка на ячейку происходит с помощью фразы " & .Address & ".
Конкретно мне требуется перебрать все ячейки (их значение - текст) удалив "лишние" пробелы, включая невидимые.
Нашёл несколько вариаций, включая цитируемый способ, и решил его доработать, другим вариантом:
Код
TRIM(CLEAN(SUBSTITUTE(("A1",CHAR(160),"" "")))
который, мне кажется, более предпочтительным, но может я ошибаюсь в правильности выбора, так как не очень понял их описание, трактовка порой разная. Одни пишут, что удаляют всё, но зачем-то отдельно 160-ый символ обрабатывают, а другие перечисляют 120-ые вручную помимо операторов... В примере применяется 2 оператора TRIM(сжпробелы) и Clean(печсимв), но я не уверен, что они так же стирают эти 120-ые символы; и для 160 символа отдельно используется 3-ий оператор SUBSTITUTE(подставить).
В любом случае, я не понимаю, как мне указать текущую ячейку, которую требуется обработать, т. е. в другом коде:
Код
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value =TRIM(CLEAN(SUBSTITUTE((XXX,CHAR(160),"" "")))
End With
Вместо XXX должно быть что-то вроде того же ".Value" или " & .Address & ", который мне и нужно обработать каждым шагом перебора или "Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value" - но это всё не работает.
Циклом For это выглядело бы нагляднее (пример)
Код
For i=1 to 10
    If Range("A" & i).Value = " " Then Range("A" & i).Value = ""
End If: End For
Т. е. тут в ячейку i-тую присваиваем результат работы с нею же, И-той ячейкой - я явно указал с какой ячейкой работал, а в коде выше я не знаю, как указать ссылку на И-тую ячейку, потому что не вижу упоминания "И-тости".
В первом коде, в качестве ссылки используется " & Address & " - тоже не понятная мне конструкция, и ничего умнее этого не придумал:
Код
With Range("I1", Cells(Rows.Count, "I").End(xlUp))
    .Value = Evaluate("INDEX(TRIM(" & .Address & "),)")
    .Value = TRIM(CLEAN(SUBSTITUTE((" & .Address & ",CHAR(160),"" "")))
    'или
    '.Value = TRIM(CLEAN(SUBSTITUTE((.Value,CHAR(160),"" "")))
End With
где вместо XXX указал "Аддресс", но так не сработало =(

Да, в пользу найденного мною варианта с 3 операторами:
Цитата
Функция СЖПРОБЕЛЫ (TRIM) предназначена для удаления из текста знаков пробела 7-разрядного кода ASCII (значение 32). В наборе знаков Юникода существует дополнительный знак пробела, который называется знаком неразрывного пробела и имеет десятичное значение 160. Этот знак обычно используется на веб-страницах как сущность HTML  . Сама по себе функция СЖПРОБЕЛЫ не удаляет этот знак неразрывного пробела. Пример синтаксиса, позволяющего удалить из текста оба знака пробела, см. в разделе Удаление пробелов и непечатаемых знаков из текста.
ПЕЧСИМВ (CLEAN) Удаляет из текста первые 32 непечатаемых знака в 7-битном коде ASCII (значения с 0 по 31).
СЖПРОБЕЛЫ (TRIM) Удаляет из текста знак пробела в 7-битной кодировке ASCII (значение 32).
ПОДСТАВИТЬ (SUBSTITUTE) Можно использовать для замены символов Юникода с более высокими значениями (127, 129, 141, 143, 144, 157 и 160) знаками 7-битной кодировки ASCII, для которых предназначены функции СЖПРОБЕЛЫ и ПЕЧСИМВ.
*Если я пойму принцип работы такого "цикла", то я многое смогу переделать с цикла for на этот.

*Косвенный вопрос: какой цикл\способ перебора быстрее работает? В итоге требуется перебирать много ячеек и либо редактировать их, например, удалять пробелы, а затем почищенные диапазоны сравнивать между собой (~80+ тысяч строк сравнить с ~60+ тысячами строк). Писать всё по детски\не профессионально циклом For мне, конечно, проще, нагляднее, но есть "пинок" сделать это лучше\быстрее, удобнее и короче в разы.

Благодарю за внимание и терпение!
Изменено: Zelik - 03.10.2017 22:50:03 (Добавил файл-пример)
 
 
 
#2
28.09.2017 16:54:08
Цитата
Zelik написал: *Если я пойму принцип работы такого "цикла", то я многое смогу переделать с цикла for на этот
Если Вы про конструкцию With...End With, то это не цикл. Эта конструкция позволяет не указывать каждый раз, к какому объекту применяется метод/свойство/и проч. В Вашем случае, это диапазон Range("I1", Cells(Rows.Count, "I").End(xlUp)). Без нее нужно было бы указывать этот диапазон везде
Как-то так

Код
Range("I1", Cells(Rows.Count, "I").End(xlUp)).Value = Evaluate("INDEX(TRIM(" & Range("I1", Cells(Rows.Count, "I").End(xlUp)).Address & "),)")
Range("I1", Cells(Rows.Count, "I").End(xlUp)) .Value = TRIM(CLEAN(SUBSTITUTE((" & Range("I1", Cells(Rows.Count, "I").End(xlUp)).Address & ",CHAR(160),"" "")))
    'или
Range("I1", Cells(Rows.Count, "I").End(xlUp)).Value = TRIM(CLEAN(SUBSTITUTE((Range("I1", Cells(Rows.Count, "I").End(xlUp)).Value,CHAR(160),"" "")))
Изменено: Sanja - 28.09.2017 16:57:26
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
28.09.2017 16:54:48
Код
.Value = Evaluate("TRIM(CLEAN(SUBSTITUTE(" & .Address & ",CHAR(160),"" "")))")

Evaluate - это пересчет формулы записанной как тектст.
Изменено: V - 28.09.2017 16:59:52
 
 
 
#4
28.09.2017 16:56:32
Цитата
Zelik написал: какой цикл\способ перебора быстрее работает?
Перебор ячеек это в принципе медленный способ работы. Забирайте все данные из нужного диапазона в массив, при необходимости заносите эти данные в словари/коллекции, обрабатывайте как Вам нужно и выгружайте готовый результат на лист
Согласие есть продукт при полном непротивлении сторон
 
 
 
#5
28.09.2017 18:15:56
Цитата
Sanja написал:
With...End With, .. Без нее нужно было бы указывать этот диапазон везде
Угу, понял. Но не понял, как в данной конструкции мне сослаться на этот Value. Благодаря "Cells(Rows.Count, "A").End(xlUp)", как я понял происходит "пробежка" по всем строкам и Value меняется, т. к. относиться с каждым шагом к другой строке-ячейке. И вот думаю, как мне к этому перебору подсунуть обработку ячеек, но в обработчике нужно указать какую ячейку править, а я не могу показать. Если укажу "A1", так только значение этой ячейки будет браться для анализа\изменения, а нужно динамически... не знаю, вторые сутки не знаю как это объяснить, но всё-таки решил написать что в голове - может кто поймёт. Сам - с усами :(
Цитата
V написал:
Evaluate - это пересчет формулы записанной как тектст
Зачем пересчитывать? Указаны операторы, они что-то делают с текстом... Или вы имели ввиду, что формула из "обычного интерфейса" не работает\не воспринимается как VBA код? Пользуясь записью макросов, я наткнулся на подобное и в коде макроса было замечено, что формула записывается в кавычки, т. е. в значение параметра\свойства .Value передаётся уже "текст", а не результат выполнения операторов.
Код
.Value = "=TRIM(CLEAN(SUBSTITUTE(ActiveCell,CHAR(160),"" "")))"
И, я так понимаю, чтобы это сработало, этот код нужно было бы обработать Evaluat`ом? Грубо говоря, как написал ниже?
Код
With Range("I1", Cells(Rows.Count, "I").End(xlUp))
    .Value = Evaluate("=TRIM(CLEAN(SUBSTITUTE(ActiveCell,CHAR(160),"" "")))")

End With
Цитата
Sanja написал:
Забирайте все данные из нужного диапазона в массив, при необходимости заносите эти данные в словари/коллекции, обрабатывайте как Вам нужно и выгружайте готовый результат на лист
Я думал это лишнее телодвижения, мол зачем брать и куда-то перемещать, там обрабатывать, когда можно сразу и на месте? Насчёт словарей и коллекций не понял. В моём понимании коллекции это "иерархия "эксель-книга-лист-диапозон\ячейка", - но, наверное путаю снова что-то. Можно попросить пример по словарям\коллекциям для наглядности?

Да, в отладчике разбирал
Код
Evaluate("INDEX(TRIM(" & .Address & "),)")
и получил, что ".Address" - содержит в себе "$A$1:$A$5", т. е. в моём случае это 5 строк с данными. Зачем TRIM`у текст "$A$1:$A$5", понять не могу. Если бы это воспринималось не как строка\текст, а как адрес\ссылка на содержимое, и уже содержимое TRIM`ировалось - тогда да. Потом INDEX, в VBA я его не нашёл, плюс там не один параметр в скобках, и что он возьмёт с TRIM`а? опять "$A$1:$A$5", и зачем это INDEX`у? Я так понял, что Эволют нужен чтоб понять функцию индекса, т.к. индекса в ВБА нет. Тогда мой код в визе должен быть такой:
Код
.Value = TRIM(CLEAN(SUBSTITUTE(.Address,CHAR(160),"" "")))
Но не работает, вся строка красная и при выполнении ругается на синтаксис этой строки.
Да, заметил, что где-то неоходимо указывать не запятую, как разделитель, а точку с запятой.
Изменено: Zelik - 29.09.2017 15:27:11 (Удалил "справку")
 
 
 
#6
28.09.2017 20:27:43
Привет!
Для себя разложил
Код
Sub With_Evaluate()
  '  With Range("I1", Cells(Rows.Count, "I").End(xlUp))
  '    .Value = Evaluate("INDEX(TRIM(" & .Address & "),)")
  '  End With
  Debug.Print Now
  Application.SendKeys "^g ^a {DEL}"
  
  With Range("A1:A2")
    .Cells(1, 1) = "Два  пробела"
    .Cells(2, 1) = "Три   пробела"
    'я = Evaluate("INDEX(TRIM(" & .Address & "),)")
    Формула = "INDEX(TRIM(" & .Address & "),)"
    Debug.Print Формула
    Массив = Evaluate(Формула)
    For i = LBound(Массив) To UBound(Массив)
      Debug.Print Массив(i, 1)
    Next
    .Value = Массив
  End With
End Sub
PHP Битрикс24 REST API, Google Apps Script, Excel VBA Windows Mac
 
 
 
#7
29.09.2017 17:59:23
Доброго времени суток! Благодарю, кажется разобрался, прошу проверить по комментариям:
Код
Sub With_Evaluate()
Dim формула, массив, i 'объявляем переменные\массив, для примера не принципиально - тип Variant
  With Range("A1:A2") 'Range - Диапазон ссылающийся на A1-A2
     формула = "INDEX(TRIM(" & .Address & "),)" 'формула - переменная, в которую сохраняется текст, в данном случае
'       текст - это "формула" для "визуальной части" экселя, т. е. буд-то я сам в ячейку набираю формулу, по обработке ячеек, и
'       указанные там операторы срабатывают как формула, а не как код VBA.
'       Т. е. что-бы сработал этот набор операторов-функций (формула), его следует выполнить как обычную функцию в ячейке, и для этого используется оператор "Evaluate".
     массив = Evaluate(формула) 'передаём в переменную-массив результат выполнения оператором Evaluate формулы и 
'       в результате получаем отредактированное содержимое диапазона A1-A2.
     For i = LBound(массив) To UBound(массив) 'цикл с начальной позиции массива до последней
        Debug.Print массив(i, 1) 'отображаем в дебаг-окне И-тый элемент массива
     Next 'для цикла For (i++)
     .Value = массив 'вывести в диапазон "a1-a2" массив \ заменяем сразу весь диапазон, таким же блоком данных - массивом.
  End With
End Sub
А массив используем, как предложил Sanja, для более быстрой обработки?
Изменено: Zelik - 29.09.2017 18:27:18
 
 
 
#8
29.09.2017 18:12:22
Zelik, Вы бы файл-пример набросали, а то на пальцах оно утомительно объяснять.
Согласие есть продукт при полном непротивлении сторон
 
 
 
#9
03.10.2017 22:53:51
Цитата
Sanja написал:
Вы бы файл-пример набросали
Прошу прощения за ожидание. "Накидал" примитивно на основе выше мною понятого, вроде работает.
Ссылка на файл (он же добавлен в шапку темы)
 
 
 
#10
15.10.2017 18:50:52
Доброго дня!
Просьба помочь с добавлением замены\удаления символа по коду 160 в массиве.
Снова встрял по теме замены, не получается с имеющимся кодом совместить функции замены 160-го символа пробела. Сделал пример:
Столбец А содержит описание (коды символов) для столбца В.
Столбец D служит для хранения исходных примеров и копирования в "В"
Макрос проходит по столбцу В, убирая разного рода знаки, но остаётся 160-ый.
Пытался приспособить к
Код
formula = "INDEX(CLEAN(TRIM(" & .Address & ")),)"
функции replace, substitute, но код "красный". Причём где-то пишут Char\Символ(160), где-то Chr(160) - что так, что так не получалось.
Сделал цикл перебора массива, и если находит символ, то у меня не хватает ума его заменить\удалить :(
Код
For i = LBound(arr) To UBound(arr)
If InStr(arr(i, 1), Chr(160)) > 0 Then
'MsgBox "dsad " & InStr(arr(i, 1), Chr(160))
End If
Next
Изменено: Zelik - 15.10.2017 18:51:38
 
 
 
#11
15.10.2017 20:30:35
Код
Sub AAAA()
Dim arr, i
arr = Range("B1:B42")
For i = 1 To UBound(arr)
    If arr(i, 1) Like "*" & Chr(160) & "*" Then
        If MsgBox("Символ '160' найден в ячейке 'B" & i & "'. Удалить?", vbYesNoCancel + vbInformation) = vbYes Then
            Cells(i, 2).Value = Replace(Cells(i, 2).Value, Chr(160), "")
        End If
    End If
Next
End Sub
Согласие есть продукт при полном непротивлении сторон
 
 
 
#12
15.10.2017 21:14:40
Сканируются элементы массива, а изменения вносятся в ячейку? Я думал в массиве изменить..., а затем из массива в рейндж.
Совместить с "formula = "INDEX(CLEAN(TRIM(" & .Address & ")),)"" не получиться?
 
 
 
#13
15.10.2017 21:20:29
Цитата
Zelik написал: а затем из массива в рейндж
Можно и так
Код
Sub AAAA()
Dim arr, i
arr = Range("B1:B42")
For i = 1 To UBound(arr)
    If arr(i, 1) Like "*" & Chr(160) & "*" Then arr(i, 1) = Replace(arr(i, 1), Chr(160), Empty)
Next
Range("B1").Resize(UBound(arr), 1) = arr
End Sub
Согласие есть продукт при полном непротивлении сторон
 
 
 
#14
17.10.2017 12:35:14
Sanja, кажется, мне имеет смысл предложить какую-то материальную благодарность?
- Не понимаю, почему у меня Replace не сработал... Но что писал\пробовал уже не вспомню.
- Запись "Range("B1").Resize(UBound(arr), 1) = arr" вместо "Range("B1:B42").Value = arr" - какой смысл ресайза в данном случае? Вдруг станет больше\меньше строк на листе? Вроде мы ранее явно указываем B1:B42.
 
 
 
#15
17.10.2017 13:02:30
Цитата
Zelik написал: какой смысл ресайза в данном случае?
Что в данном случае, что в любых других, Resize расширяет диапазон до размера выгружаемого в него массива. В отличии от Вашего примера (Range("B1:B42").Value = arr) нижняя граница задана не жестко, и не зависит от
Цитата
Вдруг станет больше\меньше строк на листе?
Цитата
Zelik написал: имеет смысл предложить какую-то материальную благодарность?
Спасибо, не откажусь
Согласие есть продукт при полном непротивлении сторон
 
 
 
#16
17.10.2017 13:26:44
Цитата
Sanja написал:
нижняя граница задана не жестко
Да, но размер массива в данном случае мы не меняем. Но в другом случае эта конструкция, кажется, мне пригодиться, там я скорее всего буду менять размеры массивов, и ломал бы голову...
Да, мне кажется или CLEAN в моём случае не отрабатывает чистку текста от символов по кодам 127, 129, 141, 144, 157, когда как функция Clean должна удалить их...
Цитата
Функция CLEAN (ПЕЧСИМВ) удаляет только некоторые непечатаемые символы из текста – символы с кодом от 0 до 31, 129, 141, 143, 144 и 157.
Или я ошибаюсь с описанием функции? Даже в ячейке прописал "=ПЕЧСИМВ(СИМВОЛ(127))" и ничего не удалилось :( А можно, тогда, с помощью Replace сделать, добавить помимо 160 кода ещё и эти? Нельзя ли в Replace указать перечень\диапазон\массив символов, которые требуется заменить?
Изменено: Zelik - 17.10.2017 13:29:56
 
 
 
#17
17.10.2017 13:36:10
Цитата
Zelik написал:
Нельзя ли в Replace указать перечень\диапазон\массив символов, которые требуется заменить?
Можно вложить несколько Replace'ов друг в друга, но при большом кол-ве удаляемых знаком лучше (ИМХО) использовать RegExp
Согласие есть продукт при полном непротивлении сторон
 
 
 
#18
17.10.2017 16:55:00
RegExp глянул... нашёл подходящие комбинации. Поправьте меня, пожалуйста, если не прав где-то как-то... 1. Взял "(?:шаблон)"
Код
Option Explicit
Dim objRegExp, Str, objMatches, i
Sub asdasd()
Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.Global = True
Str = "господин гОспожа господа"
objRegExp.Pattern = "(?:" & Chr(238) & "|" & Chr(206) & ")"
Set objMatches = objRegExp.Execute(Str)
MsgBox objMatches.Count
For i = 0 To objMatches.Count - 1
    MsgBox objMatches.Item(i).Value
Next
End Sub
Изменил значение параметра Pattern, на коды символов букв "о" кириллицы обоих регистров (т. к. функция чувствительна к регистру). Результат кол-во совпадений. Можно попробовать примастерить код к моему случаю, разве что...
2. (?=шаблон) почти тот же код, за исключением замены двоеточия на равно и в конце отображение содержимого Mathes. Здесь у меня отображались после количества пустые мсдж-боксы. Поменял со знака равно на двоеточие и мсдж-боксы отобразили буквы по шаблону (о и О).
3. x|y тоже самое что п. 1. Отличий в применении знака вопроса не нашёл в моём случае.
4. [a-z] тоже самое... objRegExp.Pattern = "[" & Chr(238) & Chr(206) & "]"
Sanja, Правильно мыслю? Как теперь это прикрутить?
Код
For i = 0 To objMatches.Count - 1
    objMatches.Item(i).Value = "" 'РУГАЕТСЯ на эту строку
    MsgBox objMatches.Item(i).Value 'просмотреть, заменён ли символ на пустоту
Next
Попытался прилепить, но опять не до конца, не знаю как из объекта внести информацию в массив.
Код
Option Explicit
Sub xxx()
Dim arr, formula, i, objRegExp, objMatches, x
Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.Global = True
objRegExp.Pattern = "[" & Chr(160) & "]"
x = 0
With Range("B1:B42")
    formula = "INDEX(TRIM(" & .Address & "),)"
    arr = Evaluate(formula)
    For i = 1 To UBound(arr)
        'If arr(i, 1) Like "*" & Chr(160) & "*" Then arr(i, 1) = Replace(arr(i, 1), Chr(160), Empty)
        Set objMatches = objRegExp.Execute(arr(i, 1))
            'If objMatches.Count > x Then: ... : x=x+1
    Next
    .Value = arr
End With
End Sub
 
 
 
#19
17.10.2017 17:20:26
Цитата
Zelik написал: Sanja , Правильно мыслю?
Обращаясь персонально ко мне, Вы резко сужаете круг потенциальных помощников  :) . В регулярных выражениях я не очень силен.
Думаю, что вопрос заявленный в стартовом сообщении, решен. Создайте новую тему, с более реальным файлом-примером (показать в нем желаемый результат), и вопросом по регуляркам. На форуме есть супер асы в составлении шаблонов. Да и на форуме поищите - была недавно тема про удаление символов при помощи RegExp  
Согласие есть продукт при полном непротивлении сторон
 
 
 
#20
18.10.2017 13:48:15
Sanja, V, Inexsu, Благодарю за помощь! Будем считать тему закрытой.
В итоге слепил так:
Код
Sub Замена_Невидимых_Символов()
'http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=96356
Dim objRegExp, formula As String, arr, i As Long
    Set objRegExp = CreateObject("VBScript.RegExp") 'http://www.script-coding.com/WSH/RegExp.html
    With objRegExp
        .Global = True 'False - проверять до первого соответствия, True - проверять по всему тексту. По умолчанию - False.
        .IgnoreCase = True 'False - учитывать регистр символов, True - игнорировать регистр символов. По умолчанию - False.
        .Pattern = "[" & Chr(127) & Chr(129) & Chr(141) & Chr(144) & Chr(157) & Chr(160) & "]" 'строка, используемая как шаблон."
    End With
    With Range("B1:B42")
        formula = "INDEX(CLEAN(TRIM(" & .Address & ")),)"
        arr = Evaluate(formula)
        For i = 1 To UBound(arr)
            arr(i, 1) = objRegExp.Replace(arr(i, 1), "") 'замена соответствующих шаблону вхождений в строке-оригинале на указанную подстроку. Возвращает (возможно) изменённую строку.
        Next
        .Value = arr
    End With
End Sub
---
Поправка. Вместо
Код
.Pattern = "[" & Chr(127) & Chr(129) & Chr(141) & Chr(144) & Chr(157) & Chr(160) & "]"
достаточно было использовать
Код
.Pattern = "[^A-Za-zа-яА-Я0-9\_\-\/\s\.\,\:\!\@]
Изменено: Zelik - 20.10.2017 15:04:35 (Добавил в .Pattern а-яА-Я)
 
 
 
Страницы: 1
Читают тему
Наверх