функция ВПР

Программирование на Visual Basic for Applications
oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 21.08.2007 (Вт) 17:47

KL

спасибо.
сейчас попробую.

oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 21.08.2007 (Вт) 17:57

KL

что-то не идет-(
Сообщает о синтаксической ошибке.
а можете ли дать пример более полного скрипта для даного случая?

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 21.08.2007 (Вт) 18:30

oleg11 писал(а):KL

что-то не идет-(
Сообщает о синтаксической ошибке.
а можете ли дать пример более полного скрипта для даного случая?


Все-таки наверное будет лучше если ТЫ дашь пример скрипта, который сообщает о синтаксической ошибке. Тебе не кажется, что мы тогда быстрее узнаем в чем твоя проблема?
Привет,
KL

oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 22.08.2007 (Ср) 0:27

KL

я только начал изучене VBA, поэтому в моем скрипте может быть полная чушь. быть может легче, написать мне пример поиска-вставки даных для одной позиции а я продолжу по аналогии. если конечно это возможно.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 22.08.2007 (Ср) 3:36

oleg11 писал(а):KL

я только начал изучене VBA, поэтому в моем скрипте может быть полная чушь. быть может легче, написать мне пример поиска-вставки даных для одной позиции а я продолжу по аналогии. если конечно это возможно.


Конечно возможно! Вешай файл с детальным описанием задачи, примером данных и с указанием правильного ответа, а я тебе - скрипт. Или пример выдумывать и вбивать тоже мне?

P.S. А вот если бы ты вместо препираний выполнил то, что я тебе рекомендовал в предыдущем сообщении, вероятно ответ уже сейчас был бы у тебя в кармане ;-)
Привет,
KL

oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 22.08.2007 (Ср) 19:07

KL
Вложения
A.xls
(16.5 Кб) Скачиваний: 289

oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 22.08.2007 (Ср) 19:09

KL
Вложения
B.xls
(16 Кб) Скачиваний: 271

oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 22.08.2007 (Ср) 19:10

KL

имеем файл А - куда должны попасть даные из файлов В и С. Причем строки не всегда точно совпадают по названию и по количеству (как в файле С есть лишняя строка "литература" которую нужно сложить с другой (верхней) строкой и сумму поместить в строку в А, отмеченную желтым цветом для понимания что там сумма двух строк. дать определение строкам - что чему соответствует, лучше в файле А где-то в спрятанном столбце. Важно чтобы были вставлены только значения без формул а итоговые (последняя строка снизу и последний столбец справа) остались с формулами. В реальности таблица больше и шире раза в два чем прведеный пример. Желательнов А где-то снизу иметь строчку для самопроверки. Где будут итоговые суммы по столбцам из В и С. Все расчеты в английской верси Excel. спасибо.
Вложения
C.xls
(16 Кб) Скачиваний: 302

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 22.08.2007 (Ср) 21:15

Вот мой субъективный анализ:

1) Из всех наименований строк, только следующие в точности присутствуют во всех 3-х файлах:

Automobile
Buyers Expense
Cash over/short
Depreciation and amortisation
Marketing Allowance
Meals and Entertainment
Repaire and Maintenance
Royalties
Shipping and delivery
Shipping salaries
Travelling


2) При этом, наименования строк в файлах B и C совпадают, но есть одна лишня строка Literatura в С, которая на словах "мапится" в строку Repaire and Maintenance

3) Соответственно, неясно в какие строки в файле А "мапится" следующие строки файлов B и C

Advertising and promotion
Commissions
General
Overhead allocation
Salesmen Salaries
Selling Salaries
Showroom general
Showroom rents
Travelling-Overhead


4) Из вышесказанного следует, что файлы сами по себе неготовы к автоматизации, т.к. не просматриваются объективные критерии поиска соответствий (например общие коды для строк - ID)

5) Использование функций (в т.ч. VLookup и CountIf) из-под VBA для извлечения данных из закрытых файлов [практически] невозможно.

6) Возможные направления поиска решения

a. После того, как структура данных будет модифицирована:

- переход на Access
- Excel: Сводные Таблицы
http://peltiertech.com/Excel/Pivots/pivotstart.htm
- Excel VBA: ADO
http://erlandsendata.no/english/index.php?t=envbadac
http://www.rondebruin.nl/ado.htm
- Excel VBA: OLE Automation
http://www.rondebruin.nl/tips.htm
http://peltiertech.com/Excel/XL_PPT.html
http://erlandsendata.no/english/index.php?t=envbaole
- Excel VBA: MSQuery
- Excel: Формулы прямо в ячейках файла А

b. Если структура данных НЕ будет модифицирована, то остается:

- Excel: Сводные Таблицы
- Excel: Ручной "копи-пейст" данных из файлов B и С в А
Привет,
KL

oleg11
Начинающий
Начинающий
 
Сообщения: 8
Зарегистрирован: 21.08.2007 (Вт) 5:32

Сообщение oleg11 » 22.08.2007 (Ср) 22:42

KL

Я специально привел такой пример, т.к. он близок к реальности. В данном случае я полагал, что можно дать определение строкам - что чему соответствует, в файле А где-то в спрятанном столбце (я привожу пример в новой редакци А. Где нзванию Marketing Allowance соответствует Salesmen Salaries которое имеется в В и С).

В случае, если это все проблематично реализовать, давайте упростмм пример - просто скопируйте названия из В в А. т.о. мы будем иметь 100% совпадение по строкам, за исключением в файла С, где есть лишняя строка "литература". но и это можно убрать. Также можно назвать одинаково столбцы. Если даже я получу просто поиск-копироваие-вставка с помощью VBA, то несоответсвие наименований строк и столбцов не проблема - найду другое решение. спасибо.
Вложения
A.xls
(16.5 Кб) Скачиваний: 268

Слобожанин
Начинающий
Начинающий
 
Сообщения: 10
Зарегистрирован: 14.02.2006 (Вт) 16:00

Сообщение Слобожанин » 12.09.2007 (Ср) 14:27

Уважаемый oleg11,
Вообще-то в приведенном вами примере можно воспользоваться функцией ИНДЕКС(массив;номер_строки;номер столбца)
В свою очередь "номер_строки" и "номер_столбца" можно найти с помощью функции ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)
Высылаю вам ваш пример в одном файле (то что было в разных файлах, я разместил на отдельных листах)
Если есть желание, конечно, можно всё это уместить в более короткую функцию в VBA, но...
... зачем? Разве что - в качестве разминки.
Опять же, можно написать макрос, когда будут переноситься только значения, без формул, но тогда будет иметь смысл делать это через отсортированный массив.

С уважением,
Слобожанин.
P.S. Приведенный вами итог (файл "A"), некорректен, т.к. данные не соответствуют заглавной колонке исходных файлов :)
Вложения
Итог АВС.xls
(55 Кб) Скачиваний: 291

ice_sar
Начинающий
Начинающий
 
Сообщения: 3
Зарегистрирован: 16.06.2012 (Сб) 22:04

Re: функция ВПР

Сообщение ice_sar » 16.06.2012 (Сб) 22:16

Приветствую
нужна помощь по данной теме
Требуется создать макрос выполняющий поиск значения в одном файле и перенос этого значения в другой
1й файл имеет формат

стол 100
стул 200
шкаф 300

т.е. в первой колонке наименование, во второй цена. в первой колонке наименования не повторяются. количество строк неизвестно и может меняться.

2й файл имеет формат
стол
стул
шкаф
стол
стол
шкаф

т.е есть только колонка с наименованиями, которые могут повторяться по несколько раз.количество строк неизвестно и может меняться

Задача проставить цены во втором файле получив следующий результат
стол 100
стул 200
шкаф 300
стол 100
стол 100
шкаф 300

Спасибо за помощь

Viper
Артефакт VBStreets
Артефакт VBStreets
Аватара пользователя
 
Сообщения: 4394
Зарегистрирован: 12.04.2005 (Вт) 17:50
Откуда: Н.Новгород

Re: функция ВПР

Сообщение Viper » 17.06.2012 (Вс) 8:58

Где вопрос? Да еще и некрофилия.
Весь мир матрица, а мы в нем потоки байтов!

ice_sar
Начинающий
Начинающий
 
Сообщения: 3
Зарегистрирован: 16.06.2012 (Сб) 22:04

Re: функция ВПР

Сообщение ice_sar » 17.06.2012 (Вс) 12:44

Вопрос в помощи - я не программист. Может кто-то помочь в написании подобного макроса?

сама формула ВПР выглядит так
=ЕСЛИ(ЕНД(ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ));"";ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ))
пытался засунуть ее в VBA - не работает

делал так:
Код: Выделить всё
Sub test()

Dim A
A = "=ЕСЛИ(ЕНД(ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ));"";ВПР(A:A;'E:\test\[test2.xls]Лист1'!$A:$C;3;ЛОЖЬ))"
[G] = A   
End Sub

ice_sar
Начинающий
Начинающий
 
Сообщения: 3
Зарегистрирован: 16.06.2012 (Сб) 22:04

Re: функция ВПР

Сообщение ice_sar » 17.06.2012 (Вс) 15:25

Ну вроде получилось что-то, правда медленно работает - 1000 строк около 1минуты
Буду благодарен, если возможно ускорить


Код: Выделить всё
Sub test2()

Dim A As Long, B, sCr As String

    Application.ScreenUpdating = False
 
For i = 2 To 1000
    sCr = Cells(i, 1)
   
    Workbooks.Open "E:\test\test2.xls"
       
    B = Application.VLookup(sCr, Range("A2:C10"), 2, False)
   
    ActiveWorkbook.Close False
 
  Cells(i, 8 ) = B
 
  Next i
   
Application.ScreenUpdating = True
End Sub

[Viper] :: Пользуйся тэгом CODE!

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Re: функция ВПР

Сообщение alibek » 18.06.2012 (Пн) 8:18

Вынеси открытие и закрытие книги за пределы цикла.
Lasciate ogni speranza, voi ch'entrate.

Michael_A
Начинающий
Начинающий
 
Сообщения: 2
Зарегистрирован: 04.06.2013 (Вт) 7:12

Re: функция ВПР

Сообщение Michael_A » 04.06.2013 (Вт) 7:47

Добрый день, как реализовать это, есть 2 документа в первом там структура такая:
Справочник.xls

Наименование 1 | | | | | | | |
Наименование 2 | | | | | | | |
Наименование 3 | | | | | | | |
Наименование 4 | | | | | | | |
Наименование 5 | | | | | | | |

один столбик с наименованием и последующее для данных.
второй документ:
Данные.xls

Наименование 1 | 123456789
Наименование 5 | 987654123
Наименование 2 | 147852369
Наименование 4 | 741258963
Наименование 3 | 321456987
Наименование 5 | 258741963
Наименование 5 | 654789123

тут наименования расположены в разброс и могут повторятся и тогда нада сохранить если несколько значений их все сохранить в первой таблице в строку напротив соответствующего наименования.
как бы так должно получится:
Справочник.xls

Наименование 1 |123456789| | |
Наименование 2 |147852369| | |
Наименование 3 |321456987| | |
Наименование 4 |741258963| | |
Наименование 5 |987654123|258741963|654789123| |

Буду очень благодарен если поможете! :salut:

Michael_A
Начинающий
Начинающий
 
Сообщения: 2
Зарегистрирован: 04.06.2013 (Вт) 7:12

Re: функция ВПР

Сообщение Michael_A » 04.06.2013 (Вт) 7:52

пробовал при помощью опычной команды:
Код: Выделить всё
=ВПР(G18;[2.xls]Лист2!$A:$B;2;0)

но тут выдает тока одно значение тобиш не подойдет :(

Пред.

Вернуться в VBA

Кто сейчас на конференции

Сейчас этот форум просматривают: AhrefsBot и гости: 11

    TopList