Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Списки с поиском по более, чем одному параметру (множественный ПОИСКПОЗ)
 
#1
09.03.2021 14:00:29
Добрый день всем!

Столкнулся с вопросом, на попытки разобраться с которым потратил уже не одну неделю, без преувеличений. Используется Способ №2 для горизонтальных баз из статьи https://www.planetaexcel.ru/techniques/1/38/, с методом СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Для списка используется формула:
Код
=СМЕЩ(Данные!$A$2:$A$8;ПОИСКПОЗ(Регион;Данные!$A$2:$A$8;0)-1;2;СЧЁТЕСЛИ(Данные!$A$2:$A$8;Регион);1)

Но как быть, если при формировании списка надо учитывать не только Регион, но и другие столбцы находящиеся в таблице? Например "тип отправления" и нахождения даты между заданными для данного конкретного сотрудника? Другими словами, сделать множественный ПОИСКПОЗ.

Пример во вложении.  
 
 
 
#2
09.03.2021 14:03:26
Jake,
Первое, что приходит в голову - это создать вспомогательный столбец, где "склеить" все интересные параметры в каждой строке. А потом искать соответствие уже по нему...
========================================
Интереса ради рекомендую Вам ознакомится со следующими статьями на сайте
Суммирование по множеству условий функцией БДСУММ (DSUM)
Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Возможно какие-нибудь идеи оттуда покажутся Вам небезынтересными
Изменено: IKor - 09.03.2021 14:07:20
 
 
 
#3
09.03.2021 14:07:46
IKor, к сожалению такой выход из положения не поможет решить вопрос с датами
 
 
 
#4
09.03.2021 14:22:21
Jake, а какой должен быть список сейчас для заданных параметров
г. Казань
конверт
08.03.2020
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#5
09.03.2021 14:26:08
Если кроме Региона учитывать "конверт" и дату, то никакого, т.к. для сотрудника с "конверт" дата не входит между указанных сроков. При смене даты в таблице на любую между 15.06.2020 и 15.06.2021, должен подбираться Сотрудник 3-1 т.к. остальным параметрам удовлетворяет
 
 
 
#6
09.03.2021 14:36:08
Jake, поэкспериментируйте с функцией ТЕКСТ
===========UPDATE==========
Хотя формально для вспомогательного столбца важна не читабельность представления дат, а однозначность соответствия данных :)
Изменено: IKor - 09.03.2021 14:39:13
 
 
 
#7
09.03.2021 14:40:39
Jake, ну в лист ДАННЫЕ в ячейку G1 (протянуть с запасом)
Код
 =ЕСЛИОШИБКА(ИНДЕКС(Данные!$C$2:$C$10;АГРЕГАТ(15;6;СТРОКА(Данные!$C$1:$C$10)/(Данные!$A$2:$A$10=Лист1!$B$1)/(Данные!$F$2:$F$10=Лист1!$B$2)/(Данные!$D$2:$D$10<=Лист1!$B$3)/(Данные!$E$2:$E$10>=Лист1!$B$3);СТРОКА()));"/")

в ячейку с выпадающим списком

Код
=СМЕЩ(Данные!$G$1;0;0;СЧЁТЕСЛИМН(Данные!$G:$G;"<>"&"/";Данные!$G:$G;"<>"&"");1)
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#8
09.03.2021 14:51:30
Mershik, Ого. Я еще не понял как, но это работает. Огромная благодарность!
 
 
 
#9
09.03.2021 14:54:45
Jake, а что ? просто обычный выбор по условию данных (многоразовый ВПР)...можно макросом еще что бы он очищал данные и в список попадали данные уже сразу при изменении ячеек (B1 B2 B3)
в файле 2 примера
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B1, B2, B3")) Is Nothing Then

Dim arr, arr2, i As Long, lr As Long, sh As Worksheet, sh2 As Worksheet
Set sh = Worksheets("Ëèñò1"): Set sh2 = Worksheets("Äàííûå")
R = sh.Range("B1"): O = sh.Range("B2"): D = sh.Range("B3")
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
arr = sh2.Range("A2:F" & lr)
ReDim arr2(1 To 1)
For i = LBound(arr) To UBound(arr)
    If arr(i, 1) = R Then
    If arr(i, 6) = O Then
    If arr(i, 4) <= D And arr(i, 5) >= D Then
        arr2(UBound(arr2)) = arr(i, 3)
        ReDim Preserve arr2(1 To UBound(arr2) + 1)
    End If
    End If
    End If
Next i
On Error Resume Next
sh2.Range("H:H").Clear
sh2.Range("H1").Resize(UBound(arr2), 1) = Application.WorksheetFunction.Transpose(arr2)
sh.Range("C5").ClearContents
End If
End Sub
Изменено: Mershik - 09.03.2021 14:58:17
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#10
09.03.2021 15:04:52
Mershik, Изучу обязательно. Насчет макроса, это великолепная идея, с решением которой я также безуспешно бьюсь.

Например 1,
Цитата
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address(False, False) = "B2" Then Range("B1").ClearContents
End Sub
Только очищает, но не выбирает первый из возможных вариантов.

Например 2,
Цитата
Private Sub Worksheet_Change(ByVal Target As Range)

 Dim sh As Worksheet
 Set sh = ActiveWorkbook.Sheets("Данные")
 
 If (Target.Address = sh.Range("Сотрудник").Address) Then refreshLists
End Sub
и функция
Цитата
Sub refreshLists()
 Dim shH, shD As Worksheet
 Set shH = ActiveWorkbook.Sheets("Лист 1")
 Set shD = ActiveWorkbook.Sheets("Данные")
 
 shH.Range("Сотрудник") = shD.Range("....")
End Sub

Второй вариант не работает как надо, т.к. игнорирует изменения в ячейке пока не обновишь её руками  :oops:  
 
 
 
#11
09.03.2021 15:08:36
Jake, я Вам макрос выше в файле прикрепил и проверьте его работу,  про выбор первый из возможных не сложно так как просто сделать ссылку в коцне моего
заменить
Код
sh.Range("C5").ClearContents
на
Код
sh.Range("C5")=sh2.range("H1")
Изменено: Mershik - 09.03.2021 15:09:53
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#12
09.03.2021 15:20:07
Mershik, Спасибо вам большое!
 
 
 
#13
09.03.2021 15:46:29
как вариант
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#14
09.03.2021 19:03:31
Mershik, В присланном вами файле все работает отлично.. но макрос совершенно отказывается заполнять конец таблицы в листе Данные в рабочем файле!
Перенес корректно, имена листов те же, номера ячеек и ссылок на столбцы исправил, переменные передаются верно (проверил в MsgBox), дошло уже до того - что полностью переделал в вашем примере архитектуру "как у рабочего", и переключая между макросами - разницы в примере и рабочем никакого - они идентичны, но заполнения все равно нет!

Что это за мистика?  
Изменено: Jake - 09.03.2021 19:05:54
 
 
 
#15
09.03.2021 21:12:23
Jake, значит есть ) без файла ничего не смогу сказать  
Не бойтесь совершенства. Вам его не достичь.
 
 
 
Страницы: 1
Наверх