| includer-test |
19-06-2013 15:10 2170682 |
Сравнение списка VBA
Всем доброго времени суток.
Стоит задача для сравнения большого списка в Excel. Алгоритм понимаю, как это выразить в VBA не представляю понятия.
Так вот. Имеется столбец с фамилиями А и три столбца с фамилиями B, С, D.
Необходимо все фамилии из столбца А сравнить со столбцами фамилий B C D, если фамилия из А не встречается ни в одном из столбцов B C D, то записать эту фамилию в столбец E.
Формулами я знаю как это сделать, но хочется сделать это именно через VBA, т.к. список огромный.
|
Не вполне понятен Ваш алгоритм. Приложите образец рабочей книги.
|
| includer-test |
19-06-2013 16:06 2170699 |
Грубо говоря, есть таблица
Иванов
Петров
Сидоров
Берем Иванова, сравниваем с первым столбцом других фамилий, если там его нет, то начинаем сравнивать с следующим столбцом, если и там его нет, то сравниваем с третьим столбцом, если там его нет, то записываем Иванов в четвертый столбец. В противном случае начинаем перебирать следующую фамилию - Петров. И так проверяем все фамилии столбца:
Иванов
Петров
Сидоров
Формулой в Excel я это делаю так. Считаю количество вхождений ячейки А2 в первый диапазон.(лист Апрель от A2:A42734)
=(СЧЁТЕСЛИ(Апрель!A2:A42734;Январь!A2))
Затем
=(СЧЁТЕСЛИ(Март!A2:A42734;Январь!A2))
Затем
=(СЧЁТЕСЛИ(Февраль!A2:A42734;Январь!A2))
Ну а потом просто, фильтрую по 0 все три столбца, вот они мои люди, которые не входят ни в один диапазон.
Но мне надо это делать например нажатием кнопки.
|
includer-test, Вам сложно приложить образец рабочей книги? Мне гораздо больше времени потребуется, чтобы воссоздать её вид вручную. И это не гарантирует её точного вида, такого же, как у Вас.
|
| includer-test |
19-06-2013 22:39 2170954 |
Вложений: 1
Приношу извинения.
Не стал грузить весь файл, т.к. там личная информация, да и строк там более 48 т. Поэтому подготовил свой пример, в котором все понятно.
Файл 100513 |
includer-test,
А если у Вас в столбце А - Иванов Иван Иваныч, а в столбце B - Иванов Иван Ильич, а в столбце C - Иванов Иван Кузьмич. Фамилии одинаковые, а Имена и Отчества могут быть разные...
и как сравнивать: подряд, перебирая все 3 столбца по строке, или поочереди - сравнивая со вторым столбцом, потом с третьим и потом с четвертым столбцами.
ps: пример сохранили бы в более раннем формате - не открывается он у меня.
|
| includer-test |
20-06-2013 07:49 2171050 |
Вложений: 1
Если имя и/или отчество не совпадает со всеми тремя столбцами, то записать в столбец с результатом, это мы как раз и ищем.
Сравнивать думаю лучше перебирая ФИО из первого столбца поочереди с каждым столбцом. Если будет четкое совпадение, то выходить из перебора и брать следующую ФИО.
Пересохранил... Файл 100535 |
Вложений: 1
includer-test, для формата *.xls (97/2003) работает в лучшем виде такой код (запрос исключения из запроса на объединение):
Код:
Option Explicit
Sub SelectFIOFromListNotInTables()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = 1
Dim objConnection As Object
Dim objRecordset As Object
Dim intRow As Integer
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 8.0;HDR=Yes;"";"
objRecordset.Open _
"SELECT FIO FROM [Эталон$] " & _
"WHERE FIO NOT IN (" & _
"SELECT FIO FROM (" & _
"SELECT FIO FROM [Февраль$] UNION SELECT FIO FROM [Март$] UNION SELECT FIO FROM [Апрель$]" & _
")" & _
")" & _
";", objConnection, adOpenStatic, adLockOptimistic, adCmdText
intRow = 1
Do Until objRecordset.EOF
intRow = intRow + 1
ThisWorkbook.Worksheets.Item("Эталон").Cells.Item(intRow, 7).Value = objRecordset.Fields.Item("FIO").Value
objRecordset.MoveNext
Loop
objRecordset.Close
Set objRecordset = Nothing
objConnection.Close
Set objConnection = Nothing
End Sub
(файл рабочей книги прилагается: Файл 100558; обратите внимание, что для корректной работы я убрал лишние строки [с объяснениями] из листа «Эталон»).
К сожалению, мне никак не удалось из-под Microsoft Excel 2003 добиться корректной работы непосредственно с форматом *.xlsx/*.xlsm (2007/2010) — аналогичный запрос там попросту не срабатывал. Если хотите попробовать сами поиграться — вот Вам ссылки на строку подключения для нового провайдера и на самого провайдера (если у Вас новый Microsoft Office — то сей провайдер должен был устанавливаться в комплекте):
Excel 2007 Connection String Samples - ConnectionStrings.com
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center
|
Цитата:
Цитата includer-test
Формулами я знаю как это сделать, но хочется сделать это именно через VBA, т.к. список огромный. »
|
не вижу связи. И запись макросов еще никто не отменял.
Включаем запись, вбиваем формулу в нужный столбец и дважды жмем на правый нижний угол ячейки с формулой. Сделается автозаполнение по всем данным, пусть их хоть сотни тысяч. Жмем остановить запись макроса и получаем готовый код. |
| includer-test |
24-06-2013 11:43 2173234 |
Цитата:
Цитата Delirium
не вижу связи. И запись макросов еще никто не отменял.
Включаем запись, вбиваем формулу в нужный столбец и дважды жмем на правый нижний угол ячейки с формулой. Сделается автозаполнение по всем данным, пусть их хоть сотни тысяч. Жмем остановить запись макроса и получаем готовый код. »
|
Автоматическое заполнение делается, но сдвигается диапазон сравнения на один шаг. Как зафиксировать диапазон я не разобрался. Если Вы знаете как обойти это, то буду признателен за подсказку.
Цитата:
Цитата Iska
К сожалению, мне никак не удалось из-под Microsoft Excel 2003 добиться корректной работы непосредственно с форматом *.xlsx/*.xlsm (2007/2010) — аналогичный запрос там попросту не срабатывал. Если хотите попробовать сами поиграться — вот Вам ссылки на строку подключения для нового провайдера и на самого провайдера (если у Вас новый Microsoft Office — то сей провайдер должен был устанавливаться в комплекте):
Excel 2007 Connection String Samples - ConnectionStrings.com
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center »
|
Спасибо за помощь. Но что-то дома у меня этот макрос на Office 2010 никак не запустился. Сейчас еще на работе попробую, руки еще не дошли просто. |
| includer-test |
24-06-2013 21:47 2173608 |
Цитата:
Цитата Iska
(файл рабочей книги прилагается: Sample.7z; обратите внимание, что для корректной работы я убрал лишние строки [с объяснениями] из листа «Эталон»).
К сожалению, мне никак не удалось из-под Microsoft Excel 2003 добиться корректной работы непосредственно с форматом *.xlsx/*.xlsm (2007/2010) — аналогичный запрос там попросту не срабатывал. Если хотите попробовать сами поиграться — вот Вам ссылки на строку подключения для нового провайдера и на самого провайдера (если у Вас новый Microsoft Office — то сей провайдер должен был устанавливаться в комплекте): »
|
На работе все заработало, наверно пакеты обновлений нужные автоматом ставились. Большое спасибо. Решено. |
Цитата:
Цитата includer-test
Но что-то дома у меня этот макрос на Office 2010 никак не запустился. »
|
Я же писал, что для свежих Microsoft Office нужен новый провайдер (который, по идее, должен устанавливаться вместе с самим Office; я же его загружал и устанавливал отдельно) и другая строка подключения (ссылку: Excel 2007 Connection String Samples - ConnectionStrings.com я приводил выше). |
Цитата:
Цитата includer-test
но сдвигается диапазон сравнения на один шаг »
|
Значит надо зафиксировать либо столбец либо строку. Для этого встаем в строку формул и жмем F4 на имени ячейки. ПОявятся знаки $. Это признак абсолютной ссылки. |
Время: 19:59.
© OSzone.net 2001-
