Страницы: 1
RSS
Возвращать значение в столбец "Цена" в соответствии с "Кодом" и "Периодом"
 
Здравствуйте,
Помогите пожалуйста решить проблему с написанием формулы.
Необходимо, чтобы выполнялось условие: возвращать значение в столбец "Цена" в соответствии с "Кодом" и "Периодом" (нужен апр.20).
 
Формула при заполнении всех строк
Код
=INDEX(данные!H$2:H$1000;AGGREGATE(15;6;ROW($1:$1000)/(A3=данные!C$2:C$1000)/(B3=данные!B$2:B$1000)/(D3=данные!E$2:E$1000)/(I3=данные!G$2:G$1000);1))
 
Другой вариант - также нужно, чтобы столбец с кодом был заполнен для всех строк (нужно вводить с ctrl+shift+enter):
Код
=ИНДЕКС(данные!$H$2:$H$136;
    ПОИСКПОЗ(A4&I4;данные!$C$2:$C$136&данные!$G$2:$G$136;0))
 
Еще вариант без изменения исходных данных. Формула массива:
=ИНДЕКС(ИНДЕКС(данные!H$2:H$136;ПОИСКПОЗ(A3;данные!C$2:C$136;)):данные!H136;ПОИСКПОЗ(I3;ИНДЕКС(данные!G$2:G$136;ПОИСКПОЗ(A3;данные!C$2:C$136;)):ИНДЕКС(данные!G$2:G$136;МИН(ЕСЛИ(ПОИСКПОЗ(A$3:A$8;данные!C$2:C$136;)>ПОИСКПОЗ(A3;данные!C$2:C$136;);ПОИСКПОЗ(A$3:A$8;данные!C$2:C$136;)))-1);))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
Максим В., заметил ошибку - в начале "данные!H136" без абсолютной ссылки по строкам. При этом ещё этот вариант не сработает для последней строки в списке - будет выдавать ЗНАЧ, т.к. конструкция "МИН(ЕСЛИ...))" будет выдавать "-1". Попробовал заменить ИНДЕКС(данные!G$2:G$136;МИН(ЕСЛИ(...))) на ЕСЛИОШИБКА(ИНДЕКС(данные!G$2:G$136;МИН(ЕСЛИ()));данные!G$136), что должно было бы помочь, но судя по всему ЕСЛИОШИБКА не способна возвращать ссылку на ячейку, а только значение, поэтому формула тоже не работает.

В итоге немного доработал, чтобы формула работала для всех случаев (наверное, можно придумать какое-нибудь более элегантное решение):

Код
=ИНДЕКС(
    ИНДЕКС(данные!H$2:H$136;ПОИСКПОЗ(A3;данные!C$2:C$136;)):данные!H$136;
    ПОИСКПОЗ(I3;
        ИНДЕКС(данные!G$2:G$136;ПОИСКПОЗ(A3;данные!C$2:C$136;)):ИНДЕКС(
            данные!G$2:G$136;
            1/ЕСЛИОШИБКА(
                1/(1/МИН(ЕСЛИ(
                    ПОИСКПОЗ(A$3:A$8;данные!C$2:C$136;)>ПОИСКПОЗ(A3;данные!C$2:C$136;);
                    ПОИСКПОЗ(A$3:A$8;данные!C$2:C$136;))
                ));
                СТРОКА(данные!G$136) - СТРОКА(данные!G$2) + 1)
        );
    )
)

Изменено: Andrej730 - 29.06.2020 13:12:27
 
Спасибо!!! Работает. Только массив на 6000 строк, поэтому придется посидеть немного с заполнением.
Изменено: Ольга Иванова - 30.06.2020 14:28:23
 
Ольга Иванова, вернитесь, приедите в порядок сообщение. Цитата - не бездумная копия
 
Спасибо. Формула агрегат крутая, но у меня Эксель 2007 и там ее нет. (забыла указать версию)
Изменено: Ольга Иванова - 30.06.2020 14:29:49
 
Ольга Иванова, почему не реагируетек на замечание? не нужно тупо-глупо копировать! Приведите в порядок сообщения
 
Цитата
возвращать значение в столбец "Цена" в соответствии с "Кодом" и "Периодом" (нужен апр.20).
Код
'запускать при активном листе расчет
Sub Tsena()
Dim i As Long
Dim iLastRow As Long
Dim FoundCell As Range
Dim FoundData As Range
   iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
   Range("G3:G" & iLastRow).ClearContents
 With Worksheets("данные")
  For i = 3 To iLastRow
    Set FoundCell = .Columns(3).Find(Cells(i, "A"), , xlValues, xlWhole)
     If Not FoundCell Is Nothing Then
       Set FoundData = .Columns(7).Find(What:=Cells(i, "I"), After:=FoundCell.Offset(, 4), _
           LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
       Cells(i, "G") = FoundData.Offset(, 1)
     End If
  Next
 End With
End Sub
 
vikttur, для вашего спокойствия скорректировала комментарии!
Страницы: 1
Наверх