Помощь по Excel

[Lockheed]

Прежде, чем задать вопрос в этой теме, попробуйте почитать справку Microsoft Excel, часто помогает...

[СантехникСан]

Пожалуйста...

[404]

СантехникСан
Проследите за правильностью формул.
1-я формула, как я понял из вашего файла, а*(х +7)^2 - b, т. е. "(x + 7) в квадрате". Но в вашем файле потерялось "- b".
Во 2-й формуле, если я опять же правильно понял, нужны скобки после дроби: (x-c*b)/(a*x). И кстати тут "c*b", или "с*d", как вы выше написали?
Словом, проверьте соответствие формул тем, что у вас в условиях задачи.

[СантехникСан]

Да, действительно, -b потерял. А d, я полагаю, просто опечатка.Спасибо большое, что уделили время.

[gef68]

Доброго времени суток. Проблема такая: есть таблица объема воды по двум аргументам Trim & Sound. А как можно наоборот, по объему воды узнать Trim & Sound или хотя бы, по объему и триму узнать sound. Верчусь вокруг ВПР, ПОИСКПОЗ, СМЕЩ уже неделю, но что то ничего не выходит. Большая просьба, подскажите что и как. Заранее благодарен

[404]

Цитата (gef68) »

Проблема такая: есть таблица объема воды по двум аргументам Trim & Sound.

Поясните подробнее задачу. Что такое "таблица объема воды по двум аргументам Trim & Sound"? Дана формула этой зависимости, или что?

[gef68]

Это таблица для балластных танков на судне, она идет с судовыми документами в бумажном виде. При балластировке судна (прием или сброс забортной воды) в эту таблицу входят с двумя параметрами: по горизонтали - дифферент судна (trim), по вертикали - замер уровня воды в танке (sound), на пересечении получают объем воды в танке (куб.метры). В отосланном мною примере я (с подсказками) написал формулу для рассчета этого объема по двум параметрам trim и sound (зеленая дополнительная таблица, последний столбец), а вот как найти обратную задачу, т.е по объему воды определить trim & sound (если это возможно). Или по trim (первая верхняя строка основной таблицы) и объему определить sound (крайний левый столбец основной таблицы) . Если возможно, то без макросов и UDF, этого мне не понять пока ))). Спасибо за отклик.

[Smirnoff]

Цитата (gef68) »

по объему воды определить trim & sound (если это возможно).

А самостоятельно подумать? Тут ведь даже формул не надо, чтобы понять - невозможно это...

[404]

Я что-то в упор не пойму.

Для решения такой задачи нужны сведения о форме танка, положении в нём измерителя уровня воды, тогда можно написать формулу зависимости.

Если известно расположение баков на судне, и как-то задано распределение массы судна (которое, вообще-то, зависит от его загрузки), то может быть можно написать и формулу зависимости дифферента только от объёма воды в каждом из танков.

А тут вместо формул и параметров — какие-то таблицы

[Smirnoff]

Цитата (404) »

вместо формул и параметров — какие-то таблицы

Так он их просто перенёс с бумажной документации; а та была может и рассчитана (только формулы на бумагу не попали ), а может и снята экспериментально...

[404]

А-а, понял. Тогда чтобы

Цитата (gef68) »

по trim (первая верхняя строка основной таблицы) и объему определить sound

надо, например:
1) при помощи ПОИСКПОЗ найти соседние табличные значения "Trim" t1 и t2, между которыми попадает заданный дифферент t;

2) в каждом из столбцов, соответствующих t1 и t2, найти соседние табличные значения объёма (V11, V12) и (V21, V22), между которыми попадает заданный объём V; им будут соответствовать в левом столбце значения "Sound" (S11, S12) и (S21, S22).

3) и дальше линейная аппроксимация:

S1= (S11*(V12 - V) + S12*(V - V11))/(V12 - V11)
S2= (S21*(V22 - V) + S22*(V - V21))/(V22 - V21)
S = (S1 *(t2 - t) + S2 *(t - t1))/(t2 - t1)

P.S. Также можно искать и V(t,S) линейной аппроксимацией по соседним табличным точкам.
У вас там ПРЕДСКАЗ, ТЕНДЕНЦИЯ и т. п, вроде слишком наворочено, не разбирался.

[gef68]

3) и дальше линейная аппроксимация:

S1= (S11*(V12 - V) + S12*(V - V11))/(V12 - V11)
S2= (S21*(V22 - V) + S22*(V - V21))/(V22 - V21)
S = (S1 *(t2 - t) + S2 *(t - t1))/(t2 - t1)

Cпасибо за ответ. Т.е получается решение только через промежуточные разультаты, я правильно понял ? Для определения объема по двум аргументам (trim & sound) была написана такая формула
'ПРЕДСКАЗ(M14;ВЫБОР({1:2};ПРЕДСКАЗ(L14;СМЕЩ($B$6;ПОИСКПОЗ(M14;$A$6:$A$150)-1;ПОИСКПОЗ(L14;$B$5:$I$5)-1;;2);СМЕЩ($B$5;;ПОИСКПОЗ(L14;$B$5:$I$5)-1;;2));ПРЕДСКАЗ(L14;СМЕЩ($B$6;ПОИСКПОЗ(M14;$A$6:$A$150);ПОИСКПОЗ(L14;$B$5:$I$5)-1;;2);СМЕЩ($B$5;;ПОИСКПОЗ(L14;$B$5:$I$5)-1;;2)));СМЕЩ($A$6;ПОИСКПОЗ(M14;$A$6:$A$150)-1;;2)) (зеленая таблица, последний столбец)
неужели ничего нельзя сделать для решения обратной задачи, без промежуточных рассчетов ?!

[gef68]

Вот например, известен объем 3.898 (выделен желтым в главной таблице) - ему соответсвует trim = 1.0 и sound = 30. Неужели c помощью Exel никак это нельзя впихнуть в одну формулу ? Жалко если это так , печалька.
а функция СМЕЩ ничем здесь не поможет?

[gef68]

Прошу прощения, не тот файлик прикрепил. И еще раз уточню, то есть по известному объему никак нельзя с помощью Exel выбрать trim и sound. Я правильно понял ?!

[Smirnoff]

Цитата (gef68) »

по известному объему никак нельзя с помощью Exel выбрать trim и sound

Что-то выбрать можно, только с реальностью "выбранное" не будет иметь никакой связи.
Вот тебе совсем примитивный пример: двое партнёров могут тебе дать каждый по нескольку яблок, ты себе составил такую же табличку со значениями по горизонтали и вертикали - типа, сколько каждый из них дал яблок и сколько их у тебя оказалось. И теперь ставишь обратную задачу: вот у тебя 4 яблока - сколько дал каждый из партнёров?
Уже понятно, что однозначного ответа нет, или будешь на пальцах перебирать варианты?..

[404]

Цитата (gef68) »

Неужели c помощью Exel никак это нельзя впихнуть в одну формулу?

Можно, но зачем?
Формулу в моём п. 3 можно записать в одну, просто подставьте выражения для S1 и S2 в формулу для S.
Но всякие ПОИСКПОЗ и СМЕЩ из п. 1 и 2 лучше в промежуточные ячейки записать, иначе получится монструозная формула.

И кстати:

Цитата (gef68) »

Для определения объема по двум аргументам (trim & sound) была написана такая формула

А как эта формула у вас будет работать, если значение одного из аргументов выйдет за границы диапазона табличных значений? Ошибку выдаст. Чтобы этого избежать, надо использовать всякие ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(…));…). Это вы уже в одну формулу не уместите.

И кстати ПРЕДСКАЗ у меня для вашей таблицы для Sound=0 выдаёт отрицательные значения объёма По-видимому, ПРЕДСКАЗ здесь не годится и в вашей формуле.

[gef68]

Спасибо за участие, буду пробовать. По результату отпишусь. Таблица имеет строго заданный диапазон, она фиксирована. У меня вроде считает нормально, без ошибок. Таблицы сканировал из бумажных, так что может где-то цифры непривильно отсканировались вот и выдает ошабку. Еще раз спасибо.

Добавлено через 4 минуты

Да вроде без ошибок, ставлю sound = 0, trim = 0 считает 1,32 куб.метра

Добавлено через 29 минут

Еще дело в том, что внутри таблицы все числа уникальные, нет повторяющихся, и trim стандартно ограничен восемью столбцами, т.е если взять, скажем, столбец Е (ограничим таблицу до 20-й строки и будем искать только по точному значению объема) и объем 3,898, то простая формула ИНДЕКС(A5:I20;ПОИСКПОЗ(R5;E520;0);1) отлично возвращает sound равный 30 (табличка над красным текстом). Я думаю, что так путем перебора в восьми столбцах можно найти sound. В принципе это то, что подсказал ув. 404, только у него еще с интерполяцией. Это так, мысли в слух. )))

[404]

Цитата (gef68) »

Таблица имеет строго заданный диапазон, она фиксирована.

Таблица то фиксирована, но в реальности у вас значение trim = -0.5001 может быть? Или вот прям гарантировано ни на на волос меньше, чем -0.5?
По хорошему надо ументь и trim = -0.5001 или 3.01, и sound = 381.5 тоже уметь обрабатывать

[gef68]

[QUOTE=Smirnoff;2314425]Что-то выбрать можно, только с реальностью "выбранное" не будет иметь никакой связи.


если находить по точным значениям объема, то для:
SOUND: "ИНДЕКС(A6:A16;МойПОИСКПОЗ(R5;B6:I16;1))"
TRIM: "ИНДЕКС(B5:I5;1;МойПОИСКПОЗ(R5;B6:I15;2))"
Подсказали на другом форуме.

[Smirnoff]

Цитата (gef68) »

если находить по точным значениям объема

Ещё раз: я не про твою убогую табличку, я про реальность.
В реальности не было, нет и не будет ни у кого "точного значения объёма" - такого, чтобы совпал с табличкой до последнего знака.
Далее - не только судостроителю либо флотоводцу, но даже и ежу должно быть понятно: при одинаковом объёме воды в балластных цистернах крен судна и замеренный уровень воды могут отличаться (так, навскидку: расположение груза на это влияет, ветер; да та же течь вскрылась, например ).
Если же рассуждать с точки зрения математики: твоя табличка интерпретируется как некая поверхность в трёхмерном пространстве; по одной-единственной координате в принципе невозможно получить одну-единственную точку на такой поверхности, только некую линию на этой поверхности.
Вот если есть две любые координаты - тогда (поскольку это поверхность, а не объёмная фигура) третья координата уже определяется однозначно...

[gef68]

"Спасибо", я все понял, немного не туда обратился за помощью.
Уважаемого 404 благодарю за дельный совет,буду пытаться делать, как вы подсказали. Думаю, тема исчерпана