По значению в первом столбце найти значение во втором столбце и перенести его на лист "Перечень" в столбец 11

@Анна Гурами · Регистрация: 10.06.2015

Author24 — интернет-сервис помощи студентам

Добрый день!
Только начинаю изучение VBA, прошу помочь оптимизировать пример кода для случая:
надо по значению в ячейке на листе "Перечень" в столбце 9 (рубрика) (сделано выпадающим списком) по словарю с листа "справочник рубрика": по значению в первом столбце найти значение во втором столбце и перенести его на лист "Перечень" в столбец 11 (название рубрики).
Помогите, пож-та!

Добавлено через 43 секунды
https://www.cyberforum.ru/post3266448.html

@Hugo121 · 11.06.2015, 10:58

Т.е. нужно оптимизировать код ikki?

@Анна Гурами · 11.06.2015, 13:20 **[ТС]**

Да, или подскажите как написать заново (если это требуется) ?
Если по коду ikki дадите подробные комментарии - типа куда идем, где ищем, что куда записываем, с пояснением ссылка, строка, столбец - попробую доработать самостоятельно. Пока "вслепую" не заработала так как надо... ((

@Hugo121 · 11.06.2015, 13:25

Вообще судя по описанию задачи (если учитывать, что как всегда ничего ПЕРЕНОСИТЬ не нужно (посмотрите значение этого слова в любом словаре)) - достаточно формулы ВПР().
Если всёж нужен макрос - запишите рекордером процесс использования формулы и замену формулы спецвставкой на значение.
Это если просто нужен результат, а не навороченный оптимизированный код для суперогромных массивов данных.

@Sasha_Smirnov · 11.06.2015, 13:49

Анна, прошу прощения за любопытство. А на кого вы учитесь?

Добавлено через 11 минут
Напужал!

@Анна Гурами · 11.06.2015, 15:34 **[ТС]**

Sasha_Smirnov, добрый день!
Дело в том, что я работаю в фирме и моя должность звучит как специалист по статистике и информации, собственно основная часть работы проходит в Excel - большие объемы информации. Процесс автоматизации по фирме - планировался еще с 2011 года, мы писали ТЗ нашим ИТистам, но воз и ныне там...
Сейчас все свелось к тому, чтобы помочь коллегам с других отделов автоматизировать заполнение Перечней ассортимента - в частности с помощью баз информации с других файлов Excel и банально справочников.
Вот - макросы нам в помощь. В институте с программированием знакомилась, в частности крепких знаний пока конкретно в VBA нет, изучаю литературу. Поэтому приму любую помощь.
Данная задача поставлена в строго отведенные сроки. Дальше - больше ))
И спасибо з авнимание к моей проблеме!

Добавлено через 4 минуты
Hugo121, реализация функцией ВПР или другими комбинациями (например, используя функции СМЕЩ, ПОИСК) первое, что было предложено коллегам, но были выдвинуты пожелания понаписанию общего макроса для решения всех "нюансов" по задаче...
Код в найденном примере от ikki достаточно не сложный, пробел в необх. времени, чтоб подтянуть синтаксис языка... Если дадите комменты по операндам - буду чрезмерно благодарна!!! Или же на источник русскоязычной лит-ры с кратким и ясным описанием нужного ))

Добавлено через 1 час 10 минут

Сообщение от Hugo121

навороченный оптимизированный код для суперогромных массивов данных.

файл предполагает порядка 15 и более тысяч строк

@Hugo121 · 11.06.2015, 15:37

У ikki всё построено на использовании стандартного поиска - что небыстро (как он написал), для больших объёмов не годится (хотя работать будет). Хотя если в миллионе строк нужно найти всего десяток значений - может это даже самый быстрый способ

С ВПР() минус в том, что каждое значение будет искаться как первое - снова перебор всего объёма.
Если работаете под Виндовс, то рекомендую сразу освоить применение массивов и scripting.dictionary (словарь) и коллекций - будет оптимально. Ну и SQL/запросы, хотя я прекрасно обхожусь без них.
А по поводу "всех "нюансов" по задаче" - мы и задачу то ещё не видели. Так, что-то слышали только...

@Анна Гурами · 11.06.2015, 16:27 **[ТС]**

Hugo121, да, под Виндовс, спасибо за советы.
Файлик вот
Цифры вверху - номера столбцов в исходнике

@сороконожка · 11.06.2015, 17:24

Когда-то давно делал для себя. Немного подредактировал под вас.
Сравнение по 9 колонке, подстановка в 11.
С листа "списки" забираются значения из первых двух колонок

Кликните здесь для просмотра всего текста

Visual Basic

Sub up()
 
Set dic = CreateObject("Scripting.Dictionary")
 
Dim i As Long
Dim n As Long
 
With Sheets("списки")
n = .Cells(Rows.Count, "a").End(xlUp).Row
 
ReDim k(1 To n) As Variant
ReDim v(1 To n) As Variant
 
For i = 2 To n
    k(i) = .Cells(i, "a").Value
Next i
 
For i = 2 To n
    v(i) = .Cells(i, "b").Value
Next i
End With
 
For i = 1 To n
    dic.Add k(i), v(i)
Next i
 
Dim rubrika As Long
Dim res As Variant
 
For i = 5 To Cells(Rows.Count, "a").End(xlUp).Row
    rubrika = Cells(i, 9)
    If dic.exists(rubrika) Then
        res = dic.Item(rubrika)
    Cells(i, 11) = res
    End If
Next i
 
End Sub

@Hugo121 · 11.06.2015, 17:55

сороконожка, зачем массив в цикле заполняете? Очень долго, и код большой.

@Vovchikvsb · 12.06.2015, 03:34

Могу предложить вариант с SQL запросом. Книгу нужно предварительно сохранить.

Visual Basic

Sub test()
    Const FirstRPer As Long = 5 'Строка начала данных на листе Перечень
    Dim LastRSp As Long, LastRPer As Long, con As Object, rst As Object
    Set con = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    'Ищем последнюю строку на листе списки
    LastRSp = Sheets("списки").Cells(Sheets("списки").Rows.Count, 1).End(xlUp).Row
    'Ищем последнюю строку на листе Перечень
    LastRPer = Sheets("Перечень").Cells(Sheets("Перечень").Rows.Count, 9).End(xlUp).Row
    'Подключаемя к файлу книги
    con.Open ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 12.0;HDR=No;IMEX=1""")
    'SQL запрос
    rst.Open ("SELECT L2.F2 FROM [списки$A2:B" & LastRSp & "] AS L2 RIGHT JOIN [Перечень$I" & FirstRPer & ":I" & LastRPer & "] AS L1 ON L1.F1 = L2.F1"), con
    'Вывод результата в 11 колонку
    Sheets("Перечень").Cells(FirstRPer, 11).CopyFromRecordset rst
    rst.Close
    Set rst = Nothing
    con.Close
    Set con = Nothing
End Sub

Добавлено через 1 минуту
Кстати, если кто-то может сказать что-то относительно скорости (относительно других вариантов), буду очень рад.

@Анна Гурами · 12.06.2015, 09:51 **[ТС]**

Vovchikvsb, спасибо огромнейшее за ПОМОЩЬ!
Опробовала два способа, все работает ))

@Sasha_Smirnov · 12.06.2015, 10:45

ИТистам слава!!!

@сороконожка · 12.06.2015, 11:19

Сообщение от Hugo121

сороконожка, зачем массив в цикле заполняете? Очень долго, и код большой.

в массив по шаблону проверка стояла.

@Hugo121 · 12.06.2015, 11:22

Сообщение от сороконожка

в массив по шаблону проверка стояла

Ну а здесь то зачем?

@сороконожка 15 / 15 / 5 Регистрация: 01.12.2014 Сообщений: 117
	12.06.2015, 11:19	14
	Сообщение от Hugo121 сороконожка, зачем массив в цикле заполняете? Очень долго, и код большой. в массив по шаблону проверка стояла. 0

По значению в первом столбце найти значение во втором столбце и перенести его на лист "Перечень" в столбец 11

Решение

Решение

Решение

@Анна Гурами 0 / 0 / 1 Регистрация: 10.06.2015 Сообщений: 5
		1
	По значению в первом столбце найти значение во втором столбце и перенести его на лист "Перечень" в столбец 11 11.06.2015, 09:50. Показов 1854. Ответов 14 Метки нет (Все метки) Добрый день! Только начинаю изучение VBA, прошу помочь оптимизировать пример кода для случая: надо по значению в ячейке на листе "Перечень" в столбце 9 (рубрика) (сделано выпадающим списком) по словарю с листа "справочник рубрика": по значению в первом столбце найти значение во втором столбце и перенести его на лист "Перечень" в столбец 11 (название рубрики). Помогите, пож-та! Добавлено через 43 секунды https://www.cyberforum.ru/post3266448.html 0

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	11.06.2015, 10:58	2
	Т.е. нужно оптимизировать код ikki? 0

@Анна Гурами 0 / 0 / 1 Регистрация: 10.06.2015 Сообщений: 5
	11.06.2015, 13:20 [ТС]	3
	Да, или подскажите как написать заново (если это требуется) ? Если по коду ikki дадите подробные комментарии - типа куда идем, где ищем, что куда записываем, с пояснением ссылка, строка, столбец - попробую доработать самостоятельно. Пока "вслепую" не заработала так как надо... (( 0

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	11.06.2015, 13:25	4
	Вообще судя по описанию задачи (если учитывать, что как всегда ничего ПЕРЕНОСИТЬ не нужно (посмотрите значение этого слова в любом словаре)) - достаточно формулы ВПР(). Если всёж нужен макрос - запишите рекордером процесс использования формулы и замену формулы спецвставкой на значение. Это если просто нужен результат, а не навороченный оптимизированный код для суперогромных массивов данных. 0

@Sasha_Smirnov 5562 / 1368 / 150 Регистрация: 08.02.2009 Сообщений: 4,109 Записей в блоге: 30
	11.06.2015, 13:49	5
	Анна, прошу прощения за любопытство. А на кого вы учитесь? Добавлено через 11 минут Напужал! 0

@Анна Гурами 0 / 0 / 1 Регистрация: 10.06.2015 Сообщений: 5
	11.06.2015, 15:34 [ТС]	6
	Сообщение было отмечено Sasha_Smirnov как решение Решение Sasha_Smirnov, добрый день! Дело в том, что я работаю в фирме и моя должность звучит как специалист по статистике и информации, собственно основная часть работы проходит в Excel - большие объемы информации. Процесс автоматизации по фирме - планировался еще с 2011 года, мы писали ТЗ нашим ИТистам, но воз и ныне там... Сейчас все свелось к тому, чтобы помочь коллегам с других отделов автоматизировать заполнение Перечней ассортимента - в частности с помощью баз информации с других файлов Excel и банально справочников. Вот - макросы нам в помощь. В институте с программированием знакомилась, в частности крепких знаний пока конкретно в VBA нет, изучаю литературу. Поэтому приму любую помощь. Данная задача поставлена в строго отведенные сроки. Дальше - больше )) И спасибо з авнимание к моей проблеме! Добавлено через 4 минуты Hugo121, реализация функцией ВПР или другими комбинациями (например, используя функции СМЕЩ, ПОИСК) первое, что было предложено коллегам, но были выдвинуты пожелания понаписанию общего макроса для решения всех "нюансов" по задаче... Код в найденном примере от ikki достаточно не сложный, пробел в необх. времени, чтоб подтянуть синтаксис языка... Если дадите комменты по операндам - буду чрезмерно благодарна!!! Или же на источник русскоязычной лит-ры с кратким и ясным описанием нужного )) Добавлено через 1 час 10 минут Сообщение от Hugo121 навороченный оптимизированный код для суперогромных массивов данных. файл предполагает порядка 15 и более тысяч строк 0

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	11.06.2015, 15:37	7
	У ikki всё построено на использовании стандартного поиска - что небыстро (как он написал), для больших объёмов не годится (хотя работать будет). Хотя если в миллионе строк нужно найти всего десяток значений - может это даже самый быстрый способ С ВПР() минус в том, что каждое значение будет искаться как первое - снова перебор всего объёма. Если работаете под Виндовс, то рекомендую сразу освоить применение массивов и scripting.dictionary (словарь) и коллекций - будет оптимально. Ну и SQL/запросы, хотя я прекрасно обхожусь без них. А по поводу "всех "нюансов" по задаче" - мы и задачу то ещё не видели. Так, что-то слышали только... 1

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	11.06.2015, 17:55	10
	сороконожка, зачем массив в цикле заполняете? Очень долго, и код большой. 0

@Анна Гурами 0 / 0 / 1 Регистрация: 10.06.2015 Сообщений: 5
	12.06.2015, 09:51 [ТС]	12
	Vovchikvsb, спасибо огромнейшее за ПОМОЩЬ! Опробовала два способа, все работает )) 0

@Sasha_Smirnov 5562 / 1368 / 150 Регистрация: 08.02.2009 Сообщений: 4,109 Записей в блоге: 30
	12.06.2015, 10:45	13
	ИТистам слава!!! 0

	12.06.2015, 11:22

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	12.06.2015, 11:22	15
	Сообщение от сороконожка в массив по шаблону проверка стояла Ну а здесь то зачем? 0