Страницы: 1
RSS
Макрос вместо ВПР
 
Добрый день, Знатоки Excel!  
 
Столкнулся с одной проблемой, четкого ответа на которую на форуме не нашел. Буду признателен если поможете решить вопрос или сбросите ссылку на решение.  
Суть проблемы: В таблице 20 000 строк и 2 десятка столбцов,  3 из которых содержат формулу ВПР. Из-за этого таблица пересчитывается при открытии иногда мин по 5 (на i5). Случайно наткнулся на msexcel.ru на решение, как заменить ВПР макросом.(см. вложенный файл макрос.doc). Очень красивое и, по-видимому, быстрое решение. Но не смог применить к своей таблице, т.к. макрос, судя по всему, работает только в том случае, если в первом столбце числа. У меня же - текст и, притом, из нескольких слов.    
ПРосьба: 1. Можно ли адаптировать макрос, чтобы искало и по текстовым данным в первом столбце?    
2. Возможно ли сделать так, чтобы выбирать не только столбец в который вставляются данные из классификатора (ст. A), но и столбец, по которому эти данные искать в классификаторе? (например, в столбец В я вставил данные из классификатора при поиске по столбцу А; а в столбец С - данные при поиске по столбцу В).  
 
PS. прошу прощения если оффтоп.  
 
Заранее благодарю за помощь!
 
Это немного не то - тут для сортированных чисел, если правильно понял.  
Нужно этот код скрестить с моим - тогда получится что надо :)
 
Спасибо, Hugo!    
Ваш макрос действительно работает с текстом и прекрасно, а главное БЫСТРО(!), сопоставляет ячейки. (Во вложении - пример, как выгружает сопоставленные данные на Лист1!). Но, к сожалению, нельзя работать в основной таблице и не получилось подтянуть Цена. Буду пробовать "слепить" его с другим макросом (не силен, правда,  в этом :) ).
 
Всё можно.  
Совсем немного подправил - и всё работает.
 
Уважаемый Hugo, cпасибо большое!  
Очень четко все работает.
 
Hugo, как Вам можно написать в личку?
 
Так подпись ведь есть, только собаку и точку добавьте (всё равно спам лезет, но меньше...)
 
Точно. :-) Не заметил.
 
Hugo, а как быть , если нужно больше столбцов подтянуть к первому столбцу(точнее 8) - не подскажите? - голову сломал себе - впр ну очень долго:(
c
 
смотрите в файле на 3 и 4 столбика из примера Hugo
 
Ну в общем ответ уже есть.  
Немного проясню алгоритм -    
1. в словаре запоминаем уникальные значения "источника" и их позицию (т.е. номер строки, где находятся нужные данные) - это один проход по данным, для ускорения сперва их берём в массивы.  
2. создаём пустой массив нужного размера (в высоту по "получателю", в ширину как нужно по задаче).  
3. циклом по "получателю" берём из словаря позицию нужных данных в "источнике" и копируем их в пустой созданный массив - это второй проход по данным.  
4. выгружаем результат на лист.  
Всего 2 прохода по данным, по одному по каждому массиву.  
Остальное - как определять данные, в каком порядке копировать, нужна ли промежуточная обрабаботка или доп.проверки - это уже вторично, зависит от задачи.  
 
Возможны варианты - если например нужно составить список только найденных пар ("выкидываем" данные без пары), то в массив c() заносим данные по отдельному счётчику, и не только что нашли, но и критерий, по которому искали.  
И выгружать тогда есть смысл не весь массив, а только заполненную верхушку, и не рядом с данными, а полностью самостоятельным списком.  
Т.е. примерно так:  
 
      For i = 1 To UBound(a)  
           If .exists(a(i, 1)) Then  
               ii=ii+1  
               c(ii, 1) = a(i, 1)  
               c(ii, 2) = b(.Item(a(i, 1)), 2)  
               ...  
 
                .[A2].Resize(ii, 4) = c
 
Спасибо,вроде разобрался  
Работает намного шустрее впра :)  
Немного доработать и можно бросать в личную книгу макросов - очень хорошая вещь.
c
 
{quote}{login=Hugo}{date=15.11.2011 08:48}{thema=}{post}Всё можно.  
Совсем немного подправил - и всё работает.{/post}{/quote}  
 
Уважаемый, Hugo!  
 
Подскажите, пожалуста, как можно попробовать реализовать некий фильтр ячеек. Попробую пояснить на Вашем примере (post_278322.zip). Если, например, в колонке "А", в какой-то из строк не указан Продукт - просто пусто, а в колонке "B" есть забитые данные, которые нужно оставить, то при запуске макроса данные в колонке "B" перезаписываются на пустые.  
 
Как поступить в данном случаи, чтобы данные колонки "B" не перезаписывались?  
 
Заранее спасибо.
 
Кажется это нужно?  
 
Option Explicit  
 
 
Sub compare()  
   Dim a, b, c, iLastrow As Long, i As Long, ii As Long  
 
   '1. данные в два массива  
   With Sheet1    'используется кодовое имя  
       iLastrow = .Cells(Rows.Count, 1).End(xlUp).Row  
       a = Range(.[a3], .Range("A" & iLastrow)).Value
       '2.НЕпустой массив для результата  
       c = Range(.[a3], .Range("A" & iLastrow)).Offset(, 1).Resize(, 2).Value
   End With  
 
   With Sheet2    'используется кодовое имя  
       iLastrow = .Cells(Rows.Count, 1).End(xlUp).Row  
       b = Range(.[c2], .Range("A" & iLastrow)).Value
   End With  
 
   '2.пустой массив для результата  
   'ReDim c(1 To UBound(a), 1 To 2)  
 
   With CreateObject("Scripting.Dictionary")  
     
       '3.в словарь уникальные и номер строки из массива  
       For i = 1 To UBound(b)  
           .Item(b(i, 1)) = i  
       Next  
 
       '4.по словарю из массива b в массив c  
       For i = 1 To UBound(a)  
           If .exists(a(i, 1)) Then  
               c(i, 1) = b(.Item(a(i, 1)), 2)  
               c(i, 2) = b(.Item(a(i, 1)), 3)  
           End If  
       Next  
   End With  
 
   '5. выгрузка всего массива  
   With Sheet1    'используется кодовое имя  
       .[B3].Resize(UBound©, 2) = c
       .Activate  
   End With  
 
End Sub
 
{quote}{login=Hugo}{date=26.09.2012 03:41}{thema=}{post}Кажется это нужно?  
{/post}{/quote}  
 
Hugo, громадное СПАСИБО! Именно то, что нужно.
Страницы: 1
Наверх