Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Найти коэффициент умножения двух чисел
 
#1
27.10.2015 23:46:36
Всем добра!
Задачка такая образовалась, не знаю с какой стороны подойти

Есть пары чисел, надо найти "коэффициенты", чтобы при умножении на первое число первого коэффициента получалось произведение примерно равное второму произведению (с погрешностью до 5-7%), которое получается умножением второго числа на второй коэффициент..
В файлике небольшой пример, там коэффициенты подобраны руками.

Если кто подскажет в каком направлении тут рыть, буду благодарен весьма
 
 
 
#2
27.10.2015 23:52:59
Берите оба коэффициента 0 - не ошибетесь. А если серьезно, то как определить какое число должно получиться на выходе? Или, если это подбирается случайным образом, то первый коэффициент берете через СЛЧИС, а второй =первый коэффициент * первое число / второе число
 
 
 
#3
28.10.2015 01:11:55
С погрешность 0%:
=НОК(A3;B3)/A3 (7)
=НОК(A3;B3)/B3 (17)
произведение - 1428
 
 
 
#4
28.10.2015 07:32:10
MBT и Михаил С. - спасибо за ответы..
Постановка задачи именно такая.
Как определить какое число должно быть на выходе, я не знаю алгоритма. Но если делать так сказать "руками и глазами", то как бы интуитивно понятно... Может алгоритм мог бы быть таким - берем большее число - умножаем на два, смотрим сколько раз целиком в это произведение впишется второе число - соответственно это второй коэффициент, если отношение первого и второго из получившихся произведений имеет погрешность меньше 5%, то остановка, если нет - то умножаем большее число на три... и тд... как то так, наверное

с погрешностью 0% - не устраивает, ибо НОК (наименьшее общее кратное) это в теории правильный подход, но он дает слишком "завышенные" произведения. Сами видите - для строки 3 коэффициенты получились 7 и 17 а произведение - 1428, в то время, как при подборе вручную - произведение колеблется с 408 по 420 (и погрешность 2,8% - вполне допустимая)
 
 
 
#5
28.10.2015 08:19:29
Может быть так?
 
 
 
#6
28.10.2015 08:45:25
MCH - я в шоке! ну и формула! еще и погрешность можно задавать!
А как она (принцип) устроена и как она работает? хотя бы в двух словах можете написать комментарии к формуле? думаю, что не только мне будет интересно и не только интересно, а будет чему поучиться...
:-)
 
 
 
#7
28.10.2015 09:11:52
Красивое решение.
использование "памяти" формул массива и формула ОКРУГЛТ (а я все думал для чего она вообще может понадобиться)
Буду пользоваться.

Спасибо.
Никто не знает столько - сколько не знаю я
 
 
 
#8
28.10.2015 19:54:42
Цитата
Zhukov_K написал:
А как она (принцип) устроена и как она работает?
Алгоритм работы формулы достаточно прост.
На примере формулы из D3:
Код
=ПОИСКПОЗ(ИСТИНА;ABS(1-A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3))/ОКРУГЛТ(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3));B3))<=D$1;)

НОК(A3:B3) - определяем наименьшее общее кратное 204 и 84 - 1428

НОК(A3:B3)/A3 - определяем максимальный множитель для числа из A3, при котором погрешность равна нулю - 7

СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3)) - формирует вертикальный массив от 1 до максимального множителя - {1:2:3:4:5:6:7}

A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3)) умножаем все это на 204, получая массив {204:408:612:816:1020:1224:1428}

ОКРУГЛТ(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3));B3) - округляет данный массив до чисел кратных 84, получая массив {168:420:588:840:1008:1260:1428}

затем делим один массив на другой получая {204:408:612:816:1020:1224:1428}/{168:420:588:840:1008:1260:1428} = {1,21428571428571:0,971428571428571:1,04081632653061:0,971428571428571:1,01190476190476:0,971428571428571:1}

ABS(1-...) - вычисляем погрешность отношения чисел - {0,214285714285714:0,0285714285714286:0,0408163265306123:0,0285714285714286:0,0119047619047619:0,0285714285714286:0}

ABS(1-...)<=D$1 - сравниваем с необходимой погрешностью, получая массив {ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА:ИСТИНА}

ПОИСКПОЗ(ИСТИНА;...;) - находит первую позицию ИСТИНА в массиве с условиями - 2

Думаю, что формула в E3
Код
=ОКРУГЛ(A3*D3/B3;)

не нуждается в комментариях
Изменено: MCH - 28.10.2015 19:55:55
 
 
 
#9
28.10.2015 21:51:30
спасибо огромное за "расшифровку" формулы! (только сейчас добрался до компа с интернетом и прочитал Ваше объяснение!)    :)
 
 
 
#10
28.10.2015 23:21:10
Цитата
MCH написал: ABS(1-...) - вычисляем погрешность отношения чисел -
тут есть косячок
у автора коэффициент вычисляется =1-МИН(G5:H5)/МАКС(G5:H5)
У Вас деление первое на второе
на третий строке это видно на округление не знаю на скок это важно автору но все же
1,078% и 1,0667%
Изменено: Александр Сергеевич - 28.10.2015 23:23:01
не нужно оскорблять.
 
 
 
#11
28.10.2015 23:38:42
Цитата
Александр Сергеевич написал: тут есть косячок
Это не "косячок", а умышленное допущение, о котором я знаю.

Для оптимизации размера формулы, я пренебрег методом вычисления погрешности
погрешность 1-МИН/МАКС меньше чем МАКС/МИН-1, но т.к. может быть первое число больше второго или наоборот, то применил метод ABS(1-a/b)
В зоне погрешности от 0% до 5% этим можно пренебречь, т.к AbS(1-105/100) Примерно равно Abs(1-100/105)
Да и ТС погрешность нужна в диапазоне 5-7%.
При точном вычисления 1-МИН/МАКС необходимо увеличить формулу, нужно ли это ТС, большой вопрос, т.к. даже по формуле чуть больше 100 символов возникло непонимание, как она работает.

Если уж очень хочется вычислять 1-МИН/МАКС, то формула увеличится почти в два раза:
=ПОИСКПОЗ(ИСТИНА;1-(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3))/ОКРУГЛТ(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3));B3))^ЕСЛИ(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3))>ОКРУГЛТ(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3));B3);-1;1)<=D$1;)
Почти без увеличения размера:
=ПОИСКПОЗ(ИСТИНА;1-10^-ABS(LOG(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3))/ОКРУГЛТ(A3*СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3));B3)))<=D$1;)
Изменено: MCH - 29.10.2015 00:44:43
 
 
 
#12
29.10.2015 00:15:51
Не знаю насколько влияет на производительность при наличие данных в 1:х если будет тормозить можно оптимизировать формулу на производительность
Цитата
MCH написал:
СТРОКА(ДВССЫЛ("1:"&НОК(A3:B3)/A3))
заменить на СТРОКА(ДВССЫЛ("A1:A"&НОК(A3;B3)/A3)

P.S. Хотя  и сам люблю писать в формулах А:А...

не нужно оскорблять.
 
 
 
#13
29.10.2015 00:30:42
Цитата
Александр Сергеевич написал:
Не знаю насколько влияет на производительность при наличие данных в 1:х если будет тормозить можно оптимизировать формулу на производительность
Можете померить самостоятельно, но думаю что на скорость это не повлияет
Функция ДВССЫЛ, также, как и СМЕЩ является волатильной и пересчитывается при любом событии (вызывающим пересчет)
Разница в расчетах (в пересчете) может возникнуть, если указывать диапазоны для СТРОКА() в явном виде, поэтому СТРОКА(A1:A10) предпочтительнее, чем СТРОКА(1:10), т.к. в первом случае пересчет формулы возникнет при изменении данных в ячейках A1:A10, во втором случае, при изменении данных в любой ячейке с первой по 10 строку
Для уменьшения пересчета динамические диапазоны лучше прописывать в виде СТРОКА(A1:ИНДЕКС(A:A;10)) или СТРОКА(ИНДЕКС(A:A;1):ИНДЕКС(A:A;10))
Но в этом случае есть уязвимость формулы, при удалении/вставки ячеек, поэтому ДВССЫЛ - является более устойчивой конструкцией по сравнению с СМЕЩ и ИНДЕКС для определения динамических диапазонов.
Изменено: MCH - 29.10.2015 00:32:26
 
 
 
#14
29.10.2015 01:17:07
MCH, вот Вы меня озадачила, бьюсь не могу понять почему с логарифмой это работает, проверка показала что действительно работает
=1-МИН(G3:H3)/МАКС(G3:H3)
=1-10^-ABS(LOG(G3/H3))
равнозначные формулы, но почему... придется искать школьный учебник про логарифмы...
не нужно оскорблять.
 
 
 
Страницы: 1
Читают тему
Наверх