Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Применение встроенной в VBA функции Left к диапазону
 
#1
23.07.2010 16:24:45
Добрый день знатокам и любителям VBA.  
Вопрос такой. Есть таблица с указанием товара, артикула, мужское или женское и количеством штук. Как в стоблце D (мужское или женское) "обрезать" значение ячеек до 3 знаков, чтобы осталось муж ил жен. Но сделать это не через объявление переменной типа String и перебора каждой ячейки, а применить ко всему диапазону сразу. Была похожая проблема с функцией Trim, но как раз на этом сайте нашел ответ. За что большой спасибо форумчанам. Прикрепляю таблицу-образец и там в модуле два кода как пробовал и как не работает. Вообще пробовал больше, оставил только эти. Спасибо за внимание.  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#2
23.07.2010 16:39:37
через цикл прогнать ПОСТРОЧНО все строки, и брать только 3 левых символа.
 
 
 
#3
23.07.2010 17:03:45

Спасибо. С циклом все понятно и сделать это несложно. Вопрос как раз в том, чтобы обойтись без него. Хочется узнать более изысканный способ решения такой задачи, про который в обычной книжке не прочтешь. http://www.planetaexcel.ru/forum.php/plex.php?thread_id=14948 вот, к примеру, как нашли способ обойти цикл при применении фунции Trim
 
 
 
#4
23.07.2010 17:25:47
Со вставкой формулы на лист(сразу на весь диапазон), и затем преобразовывания в значения - ясно как работает и как сделать. А вот с массивами я не работал. Знаю их слабовато. Хотя конечно мысль, что наверняка это все можно сделать через массив, у меня была и есть. Только вот хочется понять, есть ли у такого способа (через массив) какие то преимущества. Стоит им "заморачиваться". Может у кого есть опыт и свое мнение на этот счет.
 
 
 
#5
23.07.2010 17:37:21
Вот так например. Это на Вашем примере, можете замерить время например на 65000 записей:  
 
Dim n()  
    n = Range("D3:D20").Value  
    x = UBound(n)  
For i = 1 To x  
n(i, 1) = Left(n(i, 1), 3)  
Next  
 
Range("D3:D20") = n
 
 
 
#6
23.07.2010 17:42:46
Я замерил (в секундах) - первая цифра после обработки массива, вторая после вставки массива на лист, 65535-3 значений:  
 
0.140625    
0.234375
 
 
 
#7
23.07.2010 18:00:57
Спасибо, Hugo. Я тоже вставил Ваш код и попробовал расширить диапазон до 7000 строк. Результат выполнения - мгновенно. И еще главное для меня - это более изысканный способ, чем построчный перебор. И спасибо, что прислали именно строки кода - по ним все наглядно видно. И как объявить массив, когда не знаешь сколько там в итоге будет элементов, и как определить индекс последнего элемента. В общем, с Вашей помощью я стал чуточку грамотней.) Примите мой поклон.
 
 
 
#8
23.07.2010 18:06:05
Чуть поясню - границу массива в строку x = UBound(n) определяю отдельно, чтоб потом в цикле не определять на каждом шаге. Если массив небольшой, обычно пишут  
 
For i = LBound(n) To UBound(n)
 
 
 
#9
23.07.2010 18:14:42
А в каком случае надо применять именно LBound(n)? Ведь в вашем коде первый элемент просто обозначен i=1. Что вполне логично. Есть случаи когда это не работает?
 
 
 
#10
23.07.2010 18:29:27
Обычно массив начинается с 0. Но если объявлено Option Base 1, то с 1. И массив из диапазона с 1. Поэтому универсальнее LBound/  
А в данном случае и UBound лишнее, ведь нам известна верхняя граница тоже. Можно было просто 17 (20-3) написать, или 65532 (65535-3). Но тут можно и ошибиться на единичку, так что надёжнее UBound  :)
 
 
 
#11
23.07.2010 18:31:49
Да, про Split я и не знал...  
В любом случае, что содержится в массиве, легко посмотреть в окне Locals редактора. С его помощью эти массивы становятся намного понятнее :)
 
 
 
#12
23.07.2010 18:34:59
Если я правильно понял задачу, то такие варианты:  
 
hard-coded, т.е. подстановка переменных невозможна:  
[D3:D20] = [INDEX(LEFT(D3:D20,3),)]
 
или гибче, с текстовой строкой:  
[D3:D20] = Evaluate("INDEX(LEFT(D3:D20,3),0)")
KL
 
 
 
#13
23.07.2010 19:25:27
The_Prist и Hugo спасибо за разъяснения. KL (Кирилл как я понял), именно Вы тогда дали подсказку как побороть Trim без перебора строк. Я оба Ваши кода - попробовал, они работают. Спасибо ). Но Вы видимо не зря сделали оговорку "hard-coded, т.е. подстановка переменных невозможна". Конечно в реальной таблице я не знаю последней строки. Задаю переменную, программа находит последнюю строку и присваивает переменной (str_end) номер строки. Я попробовал в ваших кодах применить переменную. Range("D3:D" & str_end) = [INDEX(LEFT(D3:D20,3),)] - такой код проходит, но он конечно только для эскперимента нужен. А вот в правую часть кода переменную str_end вместо 20 строки вставить не удается. Выходит только через массив надо делать?
Или у Вас есть подсказка, какой код применить, если последняя строка определяется через переменную?
 
 
 
#14
23.07.2010 19:55:32
{quote}{login=Kevin}{date=23.07.2010 07:25}{thema=}{post}The_Prist и Hugo спасибо за разъяснения. KL (Кирилл как я понял), именно Вы тогда дали подсказку как побороть Trim без перебора строк. Я оба Ваши кода - попробовал, они работают. Спасибо ). Но Вы видимо не зря сделали оговорку "hard-coded, т.е. подстановка переменных невозможна". Конечно в реальной таблице я не знаю последней строки. Задаю переменную, программа находит последнюю строку и присваивает переменной (str_end) номер строки. Я попробовал в ваших кодах применить переменную. Range("D3:D" & str_end) = [INDEX(LEFT(D3:D20,3),)] - такой код проходит, но он конечно только для эскперимента нужен. А вот в правую часть кода переменную str_end вместо 20 строки вставить не удается. Выходит только через массив надо делать?
Или у Вас есть подсказка, какой код применить, если последняя строка определяется через переменную?{/post}{/quote}  
 
Range("D3:D" & str_end) = Evaluate("INDEX(LEFT(D3:D" &  str_end & ",3),0)")
KL
 
 
 
#15
23.07.2010 20:26:41
Спасибо! Так работает. Пару строк напишите еще, что делает Index и что делает Evaluate в данном случае. А то по встроенному help я никак не пойму.
 
 
 
#16
23.07.2010 20:42:55
{quote}{login=Kevin}{date=23.07.2010 08:26}{thema=}{post}Спасибо! Так работает. Пару строк напишите еще, что делает Index и что делает Evaluate в данном случае. А то по встроенному help я никак не пойму.{/post}{/quote}  
 
1) Про INDEX(). Дело в том, что некоторые функции листа, без проблем возвращающие массивы в формулах на листе, при вызове из-под VBA приведенными способами не могут возвращать массивы если их "об этом специально не попросить". К ним относится функция LEFT(). А функция INDEX() с нулевым вторым параметром или функция TRANSPOSE() примененная два раза - это разновидности "специально попросить" :-)  
 
2) Evaluate() - макрофункция из языка XLM (Excel4) предшественника VBA. Суть ее в том, что она расчитывает как будто на листе формулу переданную ей в виде текстовой строки.
KL
 
 
 
#17
23.07.2010 20:54:16
Большое спасибо! Надо будет еще немного поэкспериментировать, чтобы лучше понять и запомнить. Главное, что теперь направление мысли верное.  
Удачных всем выходных!
 
 
 
#18
23.07.2010 20:55:57
Проверил - перебор массива то выигрывает у Evaluate, то проигрывает пару сотых секунды. Это на 2000.  
На 2003 офисе всё происходит в два раза медленне, и тут уже массивы всё время лидируют, где-то на 14-16 сотых секунды :)
 
 
 
#19
23.07.2010 21:05:02
На мой взгляд оба метода - и через массив и через Evaluate достойны внимания и применения.  Мне очень не хотелось применять именно перебор циклом всех ячеек диапазона, потому что считал такой подход малоквалифицированным. А у нас ведь модернизация теперь в почете. Так что надо подтягиваться.))
 
 
 
#20
24.07.2010 09:59:08
ради истины пробурчу - если вы не видите цикла, это еще не значит, что его нет..  
 
 
иными словами: цель не в решении без цикла, а в наиболее эффективном способе(организации цикла..обычно)
Живи и дай жить..
 
 
 
Страницы: 1
Читают тему
Наверх