Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 67705 Skopiuj do schowka Wyliczenie jednej niewiadomej
Autor Wiadomość
chrmat5
świeżak


Wersja: Win Office 2019
Posty: 4
Wysłany: 01-04-2020, 14:31   Wyliczenie jednej niewiadomej

Cześć!

Mam pewien problem z którym nie mogę sobie poradzić, a wydaje mi się, że jest możliwe uporanie się z nim w Excelu za pomocą odpowiednich form. Nie wiem czy jest potrzebny do tego dodatek Solver, czy też jakiś inny czy po prostu da się to zrobić na wersji "bazowej".

Równanie wygląda w następujący sposób



Niewiadomą dla mnie w tym równaniu jest p, które występuje zarówno na dole jak i u góry cosinusa hiperbolicznego. Jest to ta sama wartość. Po prostu jest ona i u góry i na dole równania. Więcej, wiem co stoi przed znakiem =. Tutaj znam wynik tego równania, kwestia tylko tego, żeby Excel powiedział mi ile musi się równać p aby faktycznie wynik się zgadzał.

Nie wiem niestety jak tego dokonać. Znam formuły cosh() w Excelu i do tej pory liczyły mi świetnie. Teraz tylko mam tą jedną niewiadomą w dwóch miejscach. Czy można sobie z tym jakoś poradzić? Jest do tego odpowiednia formuła/funkcja?

Z góry dziękuje za jakąkolwiek pomoc. Przepraszam jeśli to nie jest odpowiedni dział na takie pytanie, ale nie jestem pewien czy powinienem umieścić to tutaj (bo jest taka formuła, która to określi) czy może po prostu w zdaniu albo jakiś innych pytaniach.

W załączniku poza imgurem dołączam jak wygląda równanie.

2020-04-01_14h24_52.png
Plik ściągnięto 275 raz(y) 2.33 KB

ID posta: 384671 Skopiuj do schowka
 
 
Waldek 
Excel Expert


Wersja: Win Office 2019
Pomógł: 238 razy
Posty: 1051
Wysłany: 01-04-2020, 15:20   Re: Wyliczenie jednej niewiadomej

chrmat5 napisał/a:
...Więcej, wiem co stoi przed znakiem =...
To ma być jakaś zgaduj-zgadula? Mamy zgadnąć co stoi przed znakiem "="? Gdyby było wiadomo co jest przed znakiem "=" to najprościej zastosować "Szukaj wyniku". Można też przekształcić odpowiednio wzór, ale to ręczna robota poza Excelem.
ID posta: 384677 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1597 razy
Posty: 5257
Wysłany: 01-04-2020, 15:36   

Tak jak pisze Waldek zadanie jest do rozwiązania Solverem lub za pomocą Szukaj wyniku, jeśli załączysz plik z danymi (w ostateczności możesz je napisać w poście, ale tu łatwiej o pomyłkę). Czyli potrzebne są:
wartość wyrażenia (to co przed =), y i λ
czyli 3 wartości.
ID posta: 384680 Skopiuj do schowka
 
 
chrmat5
świeżak


Wersja: Win Office 2019
Posty: 4
Wysłany: 01-04-2020, 19:42   

chrmat5 w dniu: 01-04-2020, 19:29 napisał/a:
Właśnie czytałem o opcji szukania wyników i sprawdza się to całkiem nieźle ale zastanawiam się czy da się to uprościć.

Wartości mam kilka bo muszę wyliczyć kilka parametrów p.
Ale zakładając ten jeden

Całe równanie ma wynik 0.88
y zmienia się od 0.1 do 0.9 z krokiem 0.1 - ale dla przykładu weźmy tylko jeden. (gdy w równaniu za y podstawimy 0 to wychodzi wtedy całość równania równa 0 i to jest ok)
λ alfa = 3 * 10^7

Dopiero teraz odkryłem funkcję szukaj wyników i jest to nawet zadowalające o ile nie spróbuję wstawiać do formuły λ z potęgą 10^7. Choć biorąc pod uwagę, że to jest i u góry i na dole równania to nie wiem czy czasem to się nie skraca ale niestety nie wiem jak to wygląda przy cosh.

Odnośnie rady z przekształceniem wzoru, nie wiem czy jest możliwe tak aby przedstawić to wszystko jako p = ... Próbowałem np. Wolframu Alpha ale jakoś mi głupieje po wpisaniu takie formuły i nie pokazuje równania w postaci niewiadoma = ...


Przepraszam za post pod postem ale udostępniam plik jak to wygląda u mnie. Czy wygląda to poprawnie?
Jeśli tak to w zasadzie pozostaje kwestia czy mogę "pomijać" potęgę 10^7 przy lambdzie. Niestety z nią wyrzuca mi błąd #LICZBA

edycja Zbiniek:
Proszę korzystać z opcji edytowania wypowiedzi – przycisk „edytuj”. Opcja jest dostępna bodajże przez 30 minut od momentu wysłania wypowiedzi.


Procedura liczenia p.xlsx
Obliczenia równania
Pobierz Plik ściągnięto 7 raz(y) 12.52 KB

ID posta: 384711 Skopiuj do schowka
 
 
Waldek 
Excel Expert


Wersja: Win Office 2019
Pomógł: 238 razy
Posty: 1051
Wysłany: 01-04-2020, 20:31   

Wykres cosh to krzywa łańcuchowa, czyli ze wzrostem odległości x od zera y bardzo szybko rośnie. Zobacz wykres. U Ciebie najmniejsze x to ok. 3 mln, to y na wykresie daje liczbę poza zakresem Excela i pewnie nie tylko Excela.

cosh.png
Plik ściągnięto 0 raz(y) 14.06 KB

Ostatnio zmieniony przez Waldek 01-04-2020, 20:40, w całości zmieniany 1 raz  
ID posta: 384717 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1597 razy
Posty: 5257
Wysłany: 01-04-2020, 20:36   

p w Twoim wzorze jest zawsze mnożone przez lambda. Zatem wystarczy, że znajdziesz iloczyn. Innymi słowy jeśli przyjmiesz w obliczeniach lambda = 3 zamiast lambda = 3E7, to obliczoną wartość p będziesz musiał na końcu podzielić przez 10^7. Poza tym wyniki są generalnie poprawne. Dokładność procedury Szukaj wyniku zależy od ustawienia opcji maksymalna zmiana w Opcje/Formuły/Opcje obliczania. Domyślnie ma ona wartość 0,001, do takich obliczeń powinno się ją zmniejszyć np. do 0,00000001 (1E-8).
Procedura Szukaj wyniku jest dobra do jednorazowego użycia, tu jak masz obliczyć kilka wartości, musisz ją wykonać kilkakrotnie (albo napisać makro).
Inne narzędzie Solver pozwala to zrobić hurtem. Tylko jeśli nie używałeś wcześniej Solvera, to trzeba go sobie zainstalować (w Opcjach/Dodatki/Przejdź do/zaznaczyć haczyk przy Solverze). Ikona Solvera jest na karcie Dane. W tym przypadku jak chcesz od razu mieć wszystkie wyniki nie należy zaznaczać komórki celu, tylko warunki, w tym przypadku (mój załącznik) I9:I19=0,88, a komórki zmieniane podać od razu wszystkie H9:H19. Dokładność Solvera jest na ogół wystarczająca do tego typu obliczeń (on ma własne parametry dokładności, nie korzysta z Maksymalnej zmiany).

Procedura liczenia p.xlsx
Pobierz Plik ściągnięto 11 raz(y) 12.59 KB

ID posta: 384719 Skopiuj do schowka
 
 
Waldek 
Excel Expert


Wersja: Win Office 2019
Pomógł: 238 razy
Posty: 1051
Wysłany: 01-04-2020, 21:15   

Maciej Gonet napisał/a:
... to obliczoną wartość p będziesz musiał na końcu podzielić przez 10^7...
Tak. Mogę tylko dodać, że z uwagi, że funkcja jest symetryczna, funkcja odwrotna ma dwie gałęzie to wyników będzie dwa razy więcej, dodatnie i ujemne. Raz musisz podzielić przez 10^7 i drugi raz przez 10^7 i pomnożyć przez -1.
ID posta: 384721 Skopiuj do schowka
 
 
chrmat5
świeżak


Wersja: Win Office 2019
Posty: 4
Wysłany: 02-04-2020, 17:16   

Bardzo dziękuję z pomocą w liczeniu parametru p! Wszystko wyszło idealnie i mam też potwierdzenie, że na pewno wartości są w porządku.

Teraz tylko ukazał się inny problem. Ponownie próbuję wykorzystać procedurę "Szukaj wyniku" gdyż w nowym równaniu mam ponownie na dobrą sprawę jedną niewiadomą. Wiem ile równa się lewa strona, ale gdy wpiszę równanie na prawą i potem szukam wyniku parametru "z" tym razem to nie potrafi mi znaleźć poprawnego wyniku. Według informacji literaturowych wynik ten powinien oscylować w granicy 10^6, 10^7...

Nie wiem gdzie leży kłopot. Możliwe, że gdzieś w jednostkach. Próbowałem np. nie liczyć w procentach, ale za każdym razem Excel nie potrafi znaleźć wyniku. Raz myślałem, że już mi się udało ale zapomniałem zamienić z procentów wartości (16 na 0.16). Wtedy policzyło...

Próbowałem zwiększyć ilość iteracji w opcjach, dokładność też ale niestety nie pomaga. Wrzucam plik i równanie. Może tutaj już trzeba tego Solvera użyć? Albo może w ogóle Excel odpada?

2020-04-02_17h15_31.png
Plik ściągnięto 164 raz(y) 5.73 KB

Liczenie z.xlsx
Pobierz Plik ściągnięto 8 raz(y) 15.09 KB

ID posta: 384786 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1597 razy
Posty: 5257
Wysłany: 02-04-2020, 18:33   

chrmat5 napisał/a:
Albo może w ogóle Excel odpada?
Skąd taki pesymizm? Matematyka jest jedna i jakiego by narzędzia nie użyć wynik będzie taki sam. Warunkiem jest poprawny zapis równania i poprawność danych.
Nadmiar nawiasów nie sprzyja przejrzystości równania i coś tam z tymi nawiasami było nie tak!
Poza tym proszę upewnić się, czy dane są poprawne. Dla mniejszych wartości x* (<≈0,27) rozwiązanie nie istnieje - gdy z maleje, prawa strona rośnie do pewnej granicy, ale nie osiąga poziomu lewej strony. Dopiero przy odpowiednio dużym x* istnieje rozwiązanie.
Z punktu widzenia sprawności działania narzędzi do rozwiązywania równań lepiej tak formułować problem, żeby funkcja zmieniała się w okolicy 1, a nie jak w tym przypadku 10^-8. Już tego nie poprawiałem, bo równanie mogłoby stać się niejasne, ale wskazane byłoby pomnożyć obie strony przez z, obliczyć różnicę i porównywać z zerem, zamiast porównywania lewej strony z prawą, tak jak jest w tej chwili.

Liczenie z1.xlsx
Pobierz Plik ściągnięto 8 raz(y) 14.19 KB

Ostatnio zmieniony przez Maciej Gonet 02-04-2020, 18:39, w całości zmieniany 1 raz  
ID posta: 384794 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 2
Wersja: Win Office 365
Pomógł: 4178 razy
Posty: 7785
Wysłany: 02-04-2020, 18:39   

Brrr, zacząłem pisać długą odpowiedź, tak długą, że mnie wylogowało, i … zwykłe cofnij nie przywróciło mi tego co już wpisałem.

No nic - streszczając:
Widzę problem. Miksując małe i duże wartości zbliżasz się lub przekraczasz możliwości obliczeniowe zmiennych podwójnej precyzji (IEEE 754)
Spójrz choćby na drugi tanh - jest równy 1. Dla excela każdy tanh x dla x > 17( z kawałkiem ) to dokładnie 1. bo u Ciebie x to ok. 110.

Być może też Ty lub ja zrobiliśmy błąd przy wpisywaniu równania (taki urok tasiemców). Ja dla poprawienia czytelności zamiast odwołań C17 itp. użyłem nazwanych zakresów i formuła dla prawej strony to:
Kod:
=2/z_*((x_gwiazdka-x_zero-z_^2*(x_zero-x_i)/(p_^2-z_^2))*TANH(z_*lambda_alfa/2)+p_*z_*(x_zero-x_i)*TANH(p_*lambda_alfa/2)/(p_^2-z_^2))


Spróbowałem (to w podobnych przypadkach zwykle pomaga skorzystać z Wolfram Alfa wpisując

Kod:
solve 2 / z ((0.16-0.135-z^2 (0.135-0.0411) / (567000000^2-z^2) ) tanh(z 0.000000389/2) + 567000000 z (0.135-0.0411) tanh(567000000  0.000000067/2)/(567000000^2-z^2)) -0.000000067 0.68 = 0 for z

No ale tak jakby nie było rozwiązania (patrz screenshot). Gotowe równanie w Wolframie (bo może jest tam jednak jakiś błąd) to: https://www.wolframalpha.....68+%3D+0+for+z

Skuteczności życzę i ... podziel się (mam nadzieję - sukcesem) wynikiem dalszych prac.

Przechwytywanie.JPG
Plik ściągnięto 3 raz(y) 65.46 KB

Zeszyt4.xlsx
Pobierz Plik ściągnięto 7 raz(y) 16.48 KB

_________________
Kaper Jej Królewskiej Mości :boss

Jeśli dane będą torturowane dość długo, przyznają się do wszystkiego
ID posta: 384795 Skopiuj do schowka
 
 
chrmat5
świeżak


Wersja: Win Office 2019
Posty: 4
Wysłany: 03-04-2020, 19:25   

Bardzo dziękuję za wyjaśnienia powyżej.

Otóż nie poddawałem się i ostatecznie policzyłem to dla wszystkich przykładów które mam. Wydaje mi się, że od strony formuły i nawiasów jest "ok".

Mam trochę wątpliwości co do dokładności wyliczania parametru z. W załączonym pliku na zielono zaznaczyłem te wartości, które zrównują obie strony równania. Ale dokonując tego procesu (szkoda, że trzeba pojedynczo każdą wartość sprawdzać, może da się to pokonać jakimś makrem ale jeszcze nie jestem na tym etapie wtajemniczenia :P) zauważyłem pewną (dziwną?) rzecz.

Otóż początkowo aby nie zostawiać pustego pola wpisałem sobie losową wartość za parametr z. Idąc od góry, czyli tam gdzie miałem lewą stronę 3,16e-6 udało się znaleźć wartość z tylko dla X* równego 40. W późniejszych przykładach było już trochę lepiej ale trochę przypadkiem odkryłem pewną dziwną zależność.

Otóż "literaturowo" wartość z jest na ogół o rząd wielkości większa od p. Czasem może być mniejsza, ale jakoś nie odbiegają od siebie za bardzo. Wpisałem więc zamiast losowej wartości, wartość bliską p. Nagle okazało się, że praktycznie wszędzie poza najmniejszymi x* udało się znaleźć wartości! Kiedy wcześniej nie było nawet odrobinę bliskiej wartości.

Co więcej, kilka razy zdarzyło mi się, że za pierwszym razem wartości nie udało się znaleźć ale widziałem, że lewa strona, praktycznie równa się prawej (zamiast 3,16e-6, było 3,15e-6). Kazałem wyszukać jeszcze raz i znalazło wartość. Dziwne. :) Tak się zdarzyło też dla innych wartości lewej strony po drodze jak liczyłem.

Generalnie jest możliwość, że faktycznie dla tych już najmniejszych x* nie istnieje wartość z, ale z uwagi na to co mi się przytrafiło opisane wyżej to mam drobne wątpliwości.

Dla niejako potwierdzenia tych wartości wrzuciłem wyliczony z 2,24e-7 dla x* 40 z samej góry tabeli i sprawdziłem wynik w MathCad. Wyrzucił wartość 3,08e-6. Czyli różnica na poziomie 0,08e-6. Jak najbardziej akceptowalna. Ale ciekawa jest ta "tolerancja" wyniku.

Zwracając uwagę, że te otrzymane wyniki p i z posłużą mi dalej do kolejnych równań ciekaw jestem dalszych rezultatów. W Excelu pracuje mi się przyjemnie i nie chciałbym zmieniać środowiska. Ta uwaga o tanh = 1 bardzo trafna. Zakładam, że od tak pewnie nie jestem wstanie zwiększyć czułości na wartości? Bardzo Wam dziękuje za dotychczasową pomoc. Naprawdę nieoceniona przy mojej pracy.

Wyliczenie parametru Z - DD - 03.04.20.xlsx
Pobierz Plik ściągnięto 8 raz(y) 24.33 KB

ID posta: 384892 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1597 razy
Posty: 5257
Wysłany: 03-04-2020, 22:43   

Gdyby to równanie zapisać w postaci f(z)=0 tak jak sugerowałem w poprzednim poście, można byłoby uzyskać dokładniejsze wyniki, jakkolwiek nie wiem jaka dokładność jest tu potrzebna. Na pewno gdy zbliżamy się do obszaru niestabilnego, dokładność będzie mniejsza. Poza tym przy takim zapisie jak zastosowałem równanie ma zawsze dodatkowy pierwiastek z=0, który nas nie interesuje. Jeśli jest to jedyny pierwiastek, to znaczy że oryginalne równanie nie ma rozwiązania.
W tym przypadku oprócz Szukaj wyniku i Solvera można jeszcze zastosować obliczenia iteracyjne. Do tego rodzaju obliczeń równanie trzeba zapisać w postaci z = f(z).
Ten sposób jest dość prosty w zastosowaniu, ale nie jest uniwersalny. Nie zawsze łatwo określić sposób przekształcenia równania do wymaganej postaci, aby uzyskać zbieżny ciąg przybliżeń. W tym przypadku szczęśliwie to przekształcenie jest proste, choć samo równanie dość złożone. Żeby to wykorzystać w praktyce, trzeba w opcjach Excela wyrazić zgodę na obliczenia iteracyjne (cykliczne), inaczej Excel potraktuje to jak błąd.
Zaletą tej formuły cyklicznej w tym przypadku jest możliwość jej kopiowania. Jednak obliczenia trwają trochę, więc arkusz będzie ociężały. Zatem po uzyskaniu wyników można zamienić formuły na wartości, żeby obliczenia nie były wciąż powtarzane.

Kopia Wyliczenie parametru Z - DD - 03.04.20_MG.xlsx
Pobierz Plik ściągnięto 6 raz(y) 35.26 KB

ID posta: 384910 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe