Расчет стоимости печати в Excel

[Spirit412]

Ответ: Расчет стоимости печати в Excel

ГПР($I$22;Цены!$B$2:$Z$9;$H$18+2;ИСТИНА)
$I$22 - тираж
Цены!$B$2:$Z$9 - таблица с расценками (в столбцах тиражи в строках цена по цветности)
$H$18+2 - цветность, чтобы понять из какой строки взять значение цены
результат будет значение из ячейки соотв. нижней границе диапазона
получить следующую ячейку тоже легко
ну а матем. формулы - это совсем просто

Всё замечательно! Но он ищет ближайшее минимальное значение в заголовках. А как сделать ближайшее максимальное?
Пробовал вставить ГПР в СМЩ (что бы сделать смещение на 1 столбец влево) - не получилось т.к. ГПР возвращает значение а не ссылку.
Как вариант "костыля" - найти функцию на подобии ГПР только возвращяющую не значение а ссылку. Пока не нашел.

 

[splxgf]

Ответ: Расчет стоимости печати в Excel

Чуть модифицируем таблицу, добавляем еще одну в которой указана цена 1 экз сверх номинального тиража

	4000	5000	5500	6000
	4000	5000	5500	6000
1+0	50	100	150	200
2+0	60	120	160	210
4+0	70	130	170	220


	4000	5000	5500	6000
1+0	0,05	0,1	0,1	0,03
2+0	0,06	0,08	0,1	0,04
4+0	0,06	0,08	0,1	0,04

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

к первому прибавляем второе помноженное на третье.

 

[Spirit412]

Ответ: Расчет стоимости печати в Excel

Чуть модифицируем таблицу, добавляем еще одну в которой указана цена 1 экз сверх номинального тиража

Отличная идея... Я как-то даже и не подумал о таком варианте... Цена за 1 экз. сверх номинального тиража... в педелах от одного номинального до другого остается неизменной.

 

[1998]

Ответ: Расчет стоимости печати в Excel


Не по теме:
Почему бы не погуглить на тему excel linear interpolation? Были и формулы с использованием встроенных функций, и скачиваемые "надстройки"

 

[Spirit412]

Ответ: Расчет стоимости печати в Excel

Нашел решение. Код для написания модуля.

Public Function linterp(rMasX As Range, rMasY As Range, x As Double) As Double
    
    Dim iMasX, iMasY
    iMasX = rMasX.Value
    iMasY = rMasY.Value
    LastX = UBound(iMasX, 2)
    LastY = UBound(iMasY, 2)
    ReDim MasX(LastX) As Double
    ReDim MasY(LastY) As Double
    For i = 1 To LastX
        MasX(i) = CDbl(iMasX(1, i))
    Next i
    For i = 1 To LastY
        MasY(i) = CDbl(iMasY(1, i))
    Next i

'------- Тело расчета ----------
            
    If x < MasX(1) Then
        linterp = MasY(1) + (MasY(2) - MasY(1)) * ((x - MasX(1)) / (MasX(2) - MasX(1)))
        GoTo exit_function
    End If
            
    If x > MasX(LastX) Then
        linterp = MasY(LastY - 1) + (MasY(LastY) - MasY(LastY - 1)) * ((x - MasX(LastX - 1)) / (MasX(LastX) - MasX(LastX - 1)))
        GoTo exit_function
    End If
            
    For i = 1 To LastX - 1
        If x > MasX(i) And x < MasX(i + 1) Then
            linterp = MasY(i) + (MasY(i + 1) - MasY(i)) * ((x - MasX(i)) / (MasX(i + 1) - MasX(i)))
            GoTo exit_function
        End If
    Next i
            
    For i = 1 To LastX
        If x = MasX(i) Then
            linterp = MasY(i)
            GoTo exit_function
        End If
    Next i
            
            
exit_function:
End Function

5000 5500
100 150
найти стоимость для 5200
=linterp(5000:5500;100:150;5200)
ответ 120