Страницы: 1
RSS
Подбор компонента исходя из ряда совпадений
 
Помогите пожалуйста с формулой. Смысл таков:
Есть таблица с наборами. Для каждого набора свои данные. Нужно чтобы вводя данные на первом листе, формула искала совпадения по всем критериям  в интервале ОТ/ДО на другом листе. Находила вариант с полным совпадением и показывала какой это набор.
Пример:
Набор 1 - Железо от 1 до 5, Марганец от 2 до 8, Соль от 0,1 до 3
Набор 2 - Железо от 5 до 10, Марганец от 4 до 12, Соль от 5 до 30
Набор 3 - Железо от 10 до 15, Марганец от 3 до 9, Соль от 7 до 9
Набор 4 - Железо от 15 до 25, Марганец от 10 до 20, Соль от 0,5 до 4

Вводим данные для поиска: Железо 7, Марганец 8, Соль 15
И формула говорит что в данном случае нам подходит набор 2.

Примерный файл приложил. На первом листе ввожу данные в B2:B10. Результат получаю в B13.
 
В примере под критерии подпадает схемы 1 и 6. Как быть в таком случае?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Хороший вопрос. Тогда чтобы выдавал все варианты подходящие под критерии.
 
Решение в файле.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Огромное спасибо!
 
Вариант массивной формулы. Протягиваем вправо. Нули скрываем форматом ячейки.
=НАИБОЛЬШИЙ((ЧАСТОТА((ИНДЕКС(Схемы!$E5:$E64;Ч(ИНДЕКС(СТРОКА(1:60);)))<=ИНДЕКС($B2:$B11;Ч(ИНДЕКС(ОСТАТ(СТРОКА(10:69);10)+1;))))*(ИНДЕКС(Схемы!$F5:$F64;Ч(ИНДЕКС(СТРОКА(1:60);)))>=ИНДЕКС($B2:$B11;Ч(ИНДЕКС(ОСТАТ(СТРОКА(10:69);10)+1;))))*ОТБР(СТРОКА(10:69)/10);СТРОКА(1:9)-1)=10)*СТРОКА(1:10);СТОЛБЕЦ(A13))
*Ещё вариант:
=НАИБОЛЬШИЙ((ЧАСТОТА(ЕСЛИОШИБКА((ВПР(ИНДЕКС(Схемы!$D5:$D64;Ч(ИНДЕКС(СТРОКА(1:60);)));$A2:$B10;2;)<=Схемы!$F5:$F64)*(ВПР(ИНДЕКС(Схемы!$D5:$D64;Ч(ИНДЕКС(СТРОКА(1:60);)));$A2:$B10;2;)>=Схемы!$E5:$E64)*ОТБР(СТРОКА(10:69)/10);99);СТРОКА(1:9)-1)=9)*СТРОКА(1:10);СТОЛБЕЦ(A13))
Изменено: Светлый - 02.04.2021 13:55:02
 
Предыдущие формулы могут дать ошибку в 1 наборе.
Это рабочая формула:
=НАИБОЛЬШИЙ((ЧАСТОТА(ЕСЛИ(МУМНОЖ(Ч(Ч(СМЕЩ(Схемы!$E4;СТРОКА(1:60);{0;1}))*{1;-1}>Ч(СМЕЩ($B2;ОСТАТ(СТРОКА(10:69);10);))*{1;-1});{1:1})=0;--ЛЕВБ(СТРОКА(10:69)));СТРОКА(1:8)-1)=10)*СТРОКА(1:9);СТОЛБЕЦ(A1))
 
добрый день!
настоятельно рекомендую Power Query. Там подобное делается просто и не нужно будет строить довольно жуткие формулы

у Вас, кстати, название пятой схемы и шестой одинаковые
Изменено: ArgentumTiger_7 - 04.04.2021 07:44:48
 
Андрей Атанов, еще вариант
Код
Sub mrshkei()
Dim arr, i As Long, arr2, k As Long
arr = Worksheets("Схемы").Range("A5:G64")
ReDim arr2(1 To 6, 1 To 2): k = 1
For i = LBound(arr) To UBound(arr) Step 10
    With Worksheets("Калькулятор")
        If _
        .Cells(2, 2) >= arr(i, 5) And .Cells(2, 2) <= arr(i, 6) And _
        .Cells(3, 2) >= arr(i + 1, 5) And .Cells(2, 3) <= arr(i + 1, 6) And _
        .Cells(4, 2) >= arr(i + 2, 5) And .Cells(2, 3) <= arr(i + 2, 6) And _
        .Cells(5, 2) >= arr(i + 3, 5) And .Cells(2, 3) <= arr(i + 3, 6) And _
        .Cells(6, 2) >= arr(i + 4, 5) And .Cells(2, 3) <= arr(i + 4, 6) And _
        .Cells(7, 2) >= arr(i + 5, 5) And .Cells(2, 3) <= arr(i + 5, 6) And _
        .Cells(8, 2) >= arr(i + 6, 5) And .Cells(2, 3) <= arr(i + 6, 6) And _
        .Cells(9, 2) >= arr(i + 7, 5) And .Cells(2, 3) <= arr(i + 7, 6) And _
        .Cells(10, 2) >= arr(i + 8, 5) And .Cells(2, 3) <= arr(i + 8, 6) Then
            arr2(k, 1) = arr(i, 1)
            arr2(k, 2) = arr(i, 2)
            k = k + 1
        End If
    End With
Next i
Worksheets("Калькулятор").Range("B13").Resize(6, 2) = arr2
End Sub
Не бойтесь совершенства. Вам его не достичь.
Страницы: 1
Наверх