ReSQL.ru
     
powered by simpleCommunicator - 2.0.58     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / ВПР с логическим условием в параметре
11 сообщений из 11, страница 1 из 1
ВПР с логическим условием в параметре
    #38115792
madg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
madg
Участник
Добрый день.
Кто нибудь делал формулу ВПР с логическим условием в параметре?

Пример в файле вложения.

Заранее всем спасибо!

Ok.MadG
...
Рейтинг: 0 / 0
18.01.2013, 17:46
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38115933
Фотография The_Prist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
The_Prist
Участник
madg,

и что же за логическое условие? Из примера, если честно, ни фига не ясно. Вы бы хоть на слова не скупились - вроде не чернилами, не должны обеднеть. Гадая на кофейной гуще:
=ПРОСМОТР(A4;E10:E12;H10:H12)
...
Рейтинг: 0 / 0
18.01.2013, 19:34
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38116166
madg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
madg
Участник
Добрый день.
Прошу извинить за краткость - пришлось уходить от компа, дописывал уже на ходу.

Описание проблемы:
У путевки автомобиля есть дата+время открытия и закрытия(абсолютно непредсказуемые величины), во время движения а\м водитель получает накладные и, наверное, товар.
Каждая накладная имеет дату+время создания.
Массив данных о накладных ежедневно сливается на почту одним файлом.
Задача - проставить для каждой накладной номер путевого листа, выбрав данные по двум условиям:

Номер а\м в ТТН=Номер а\м в Путевом листе
Дата ТТН входит в период действия путевого листа.
С первым никаких проблем нет, а вот второе никак не могу реализовать.
В голове крутится что то вроде
SELECT *
FROM ХХХ
WHERE УУУ BETWEEN AND

Но как это организовать для ячейки...

ВПР по двум условиям, по конкатенции двух полей в каждой из таблиц не получится -
дата+время в каждом из документов величины абсолютно непредсказуемые.

Добавления по возможным вариантам:
Менять столбцы местами - без проблем, в любой из таблиц
Использовать VBA так же не проблема.

:( вот только сообразить не могу как использовать...

Во вложении файл - постарался как мог понятнее изобразить что нужно в итоге.

Заранее всем спасибо.

Ok.MadG
...
Рейтинг: 0 / 0
19.01.2013, 02:31
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38116176
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
Вы, кстати, зря про "абсолютно непредсказуемые". В любом случае, все даты/время проставлены (что в накладных, что в ПЛ), за исключением, может быть, незакрытых "текущих" ПЛ для машин (или такого нет?). При этом в ПЛ ДатаВремяЗакрытия>=ДатаВремяОткрытия, при этом ДатаВремяНакладной в любом случае попадает в какой-либо ПЛ (или нет?).

У вас обе таблицы (ПЛ и НАКЛ) - накопительные? То есть данные сначала дописываются к уже имеющимся, а затем мы рассчитываем недостающее? Таблицы, надеюсь, хранятся на отдельных листах (и как они назваются) или в именованных диапазонах?
Можно ли сортировать исходные таблицы (например, ПЛ)? - хотя это для решения не имеет принципиального значения.

И да, дайте пример с бОльшим количеством данных (или хотя бы опишите порядок цифр - кол-во машин, сколько "дозаливается" ПЛ и Накладных), и с реальными форматами ваших данных. Чтобы было на чём посмотреть построение выборок (или подбора), скорости обработки...
...
Рейтинг: 0 / 0
19.01.2013, 03:39
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38116437
madg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
madg
Участник
AndreTMВы, кстати, зря про "абсолютно непредсказуемые". В любом случае, все даты/время проставлены (что в накладных, что в ПЛ), за исключением, может быть, незакрытых "текущих" ПЛ для машин (или такого нет?). При этом в ПЛ ДатаВремяЗакрытия>=ДатаВремяОткрытия, при этом ДатаВремяНакладной в любом случае попадает в какой-либо ПЛ (или нет?).

У вас обе таблицы (ПЛ и НАКЛ) - накопительные? То есть данные сначала дописываются к уже имеющимся, а затем мы рассчитываем недостающее? Таблицы, надеюсь, хранятся на отдельных листах (и как они назваются) или в именованных диапазонах?
Можно ли сортировать исходные таблицы (например, ПЛ)? - хотя это для решения не имеет принципиального значения.

И да, дайте пример с бОльшим количеством данных (или хотя бы опишите порядок цифр - кол-во машин, сколько "дозаливается" ПЛ и Накладных), и с реальными форматами ваших данных. Чтобы было на чём посмотреть построение выборок (или подбора), скорости обработки...

Добрый...
Данные хранятся на сервере, идут накопительно, можно выгрузить за произвольный период.
Сначала появляются(исключая случаи возможных тех.накладок) данные по путевым листам, потом данные о накладных.
Данные хранятся на разных листах - куда положу выгрузку там и будут.

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

Прошу дать хотя бы принципиальную идею, для этого файла в предпоследнем посте достаточно, ибо данные будут примерно такой структуры.
Да и я вроде бы не совсем глуп - доработать идею под себя наверняка смогу.
Все дело в том, что сколько я не ищу в нете что нибудь похожее - все или конкатенцию мучат, или другие способы, позволяющие найти конкретную строку, используя для поиска сопоставление двух и более значений, и ища равное им значение в другой таблице.
У меня задача чуть другая - выбрать среди строк, относящихся к определенной а\м, запись с периодом, в который входит дата накладной, в которой указана эта самая определенная а\м.

Пробовал в VBA сделать, но пока бросил, т.к. дальше тупого перебора у меня дело не пошло.
А перебор как то мне не нравится... :) некрасиво как то, что ли...

Заранее всем спасибо за идеи!!!
Ok.MadG
...
Рейтинг: 0 / 0
19.01.2013, 17:47
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38116556
скукотища
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
скукотища
Гость
madg,

одним SQL запросом :
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
/* 
TTN - название листа с ТТН-ками
        заголовки столбцов (без кавычек):
        'Дата', 'Номер накладной', 'Номер авто', 'Номер путевого листа'

PL  - название листа с путевыми листами
        заголовки столбцов (без кавычек):
        'Дата открытия ПЛ', 'Дата закрытия ПЛ', 'Номер авто', 'Номер ПЛ'
*/
update 
  [TTN$] as T1
    inner join 
    (select * from [PL$] where [Номер авто]='{номер_интересующего_авто}' ) as T2
    on T1.[Дата] between T2.[Дата открытия ПЛ] and T2.[Дата закрытия ПЛ]
set 
  T1.[Номер авто]=T2.[Номер авто]
  , T1.[Номер путевого листа]=T2.[Номер ПЛ];
...
Рейтинг: 0 / 0
19.01.2013, 22:08
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38116694
скукотища
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
скукотища
Гость
> madg
> Задача - проставить для каждой накладной номер путевого листа, выбрав данные по двум условиям:
>
> Номер а\м в ТТН=Номер а\м в Путевом листе
> Дата ТТН входит в период действия путевого листа.

точнее так
Код: sql
1.
2.
3.
4.
5.
6.
7.
update [TTN$] as T1 
  inner join 
  [PL$] as T2
  on T1.[Номер авто] = T2.[Номер авто]
    and T1.[Дата] > T2.[Дата открытия ПЛ] 
    and T1.[Дата] < T2.[Дата закрытия ПЛ]
set T1.[Номер путевого листа]=T2.[Номер ПЛ];
...
Рейтинг: 0 / 0
20.01.2013, 01:40
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38116707
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
По идее, действительно, такие вещи надо делать, используя SQL. А вообще, эту инфу уже можно было включить при вытягивании с сервера

С другой стороны, можно и собственную обработку прикрутить. На самом деле, я у вас не так просто интересовался про непротиворечивость данных и прочее. Ваша задача как раз и решается так, как говорят - созданием ключей и подбором по ним. Хоть формулой, хоть через VBA. И вы зря про то, что получится "тупой перебор" - при отсортированной таблице поиска всё очень быстро.

ПЛ.ДатаЗакрытия вам нужен только для проверки. Ключ создаем по НомерАвто+ДатаОткрытия/Накладной. Действительно, если ПЛ был открыт в какой-то момент, то все накладные с датами после этого момента и до даты открытия следующего ПЛ - принадлежат этому ПЛ. А для создания таких условий мы отсортируем список ПЛ по НомерАвто, затем по ДатаОткрытия. И тогда можно использовать функцию ПРОСМОТР() (в векторной форме), или ВПР(;;;ИСТИНА), или ИНДЕКС(ПОИСКПОЗ(. Хитрость там, конечно, имеется (Ключевое поле в ПЛ нельзя расширять вне списка ПЛ) - но результаты, тем не менее, получаем нужные.
Кроме того, разделив ИНДЕКС() и ПОИСКПОЗ(), можно проделать дополнительные проверки.

Таким же методом можно решить и с помощью VBA, просто поиск в списке ПЛ (отсортированном!) надо выполнять, например, половинным делением интервала. Естественно, при этом никакие дополнительные столбцы не понадобятся.

Посмотрите пример.
...
Рейтинг: 0 / 0
20.01.2013, 02:15
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38118055
madg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
madg
Участник
Добрый день.
Спасибо всем участникам.

Решение, удовлетворившее суть моей задачи выглядит вот так:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
Sub Запрос_обновление()

'=============================
Dim Connection As ADODB.Connection
Dim DBFullName As Variant
Dim Cnct As Variant

    DBFullName = ThisWorkbook.FullName
'   Открытие соединения
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Cnct = Cnct & "Extended Properties= Excel 8.0;"
    Connection.Open ConnectionString:=Cnct

Dim SQLString As String
Dim SQLAdditionalString As String

SQLString = "UPDATE [Данные$]" & _
" inner join" & _
" [ПЛ$]" & _
" on [Данные$].[Гос номер] = [ПЛ$].[Гос номер]" & _
" and [Данные$].[Время начала события] >= [ПЛ$].[Время вызда факт]" & _
" and [Данные$].[Время начала события] <= [ПЛ$].[Время возврат факт]" & _
" SET [Данные$].[НАК] = [ПЛ$].[НАК]," & _
" [Данные$].[ВЭ] = [ПЛ$].[ВЭ]" & _
" where [Данные$].[Гос номер] = [ПЛ$].[Гос номер];"
Connection.Execute SQLString

'=============================
Connection.Close
End Sub



В референс VBA нужно поднять галку на
MicrosoftActiveX Data Objects 2.xxx Libray

Дополнительно к этому нужно дописать обработчик ошибок.

PS.
У кого есть замечания\улучшения кода - прошу отписаться!

Еще раз всем спасибо!

Ok.MadG
...
Рейтинг: 0 / 0
21.01.2013, 14:22
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38118687
скукотища
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
скукотища
Гость
madg,
 
Здесь "мелочные придирки" :)Для создания объекта соединения можно использовать позднее связывание
Код: vbnet
1.
2.
3.
4.
Dim Connection As Object
...
Set Connection = CreateObject("ADODB.Connection")
...

Тогда не нужно будет "В референс VBA поднимать галку наMicrosoftActiveX Data Objects 2.xxx Libray"
Минусы такого способа
- невозможность использовать именованные константы, предоставляемые подключаемой библиотекой
- отсутствие "поддержки" IDE на этапе написания кода

Неплохо было бы добавить в 'Extended Properties' параметр, явно указывающий наличие подписей полей в диапазонах данных.
Чтобы не закладывыаться на дефолтное поведение Jet
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
'Достаточный тип данных для строки соединения и полного имени "базы" - String
Dim DBFullName As String
Dim Cnct As String
...
' REM если поле "Extended Properties" содержит более одного параметра - 
'     необходимо заключить значение поля в кавычки
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=YES';"
Cnct = Cnct & "Data Source=" & DBFullName
...


В запросе условие
Код: sql
1.
/* where */ [Данные$].[Гос номер] = [ПЛ$].[Гос номер]

- лишнее, так как по этому условию данные также фильтруются при соединении таблиц.
...
Рейтинг: 0 / 0
21.01.2013, 20:04
| Ответить | Цитировать | Написать  
ВПР с логическим условием в параметре
    #38118872
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AndreTM
Участник
madg,

Еще немножко "исправлений". Если использовать в коннекте строку подключения не через провайдер, а через драйвер
Код: vbnet
1.
2.
3.
"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=0;DBQ='FullPath to XL-file'"
' или
"Driver={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ='FullPath to XL-file'"

то можно справиться и с проблемами одновременного использования кода на всех версиях Excel.
...
Рейтинг: 0 / 0
22.01.2013, 00:20
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / ВПР с логическим условием в параметре
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/search_topic.php?author=FLY_18&author_mode=last_posts&do_search=1]:
 0ms
get settings:
 10ms
get forum list:
 14ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 31ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 819ms
total:  961ms
созд. / загр.: 961ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]