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: 63648 Skopiuj do schowka Dziwna formuła
Autor Wiadomość
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1273 razy
Posty: 4362
Wysłany: 02-01-2019, 11:29   Dziwna formuła

W skoroszycie Excela wpisałem pewną formułę i nadałem jej nazwę "formuła".
Formuła zachowuje się trochę dziwnie - patrz obrazek (tym razem wyjątkowo bez pliku). Kolumny A i D zawierają wyniki, kolumny B i E podgląd formuł. Kolumna E dodana w celu ilustracji wyników w bardziej zrozumiałej formie.
Pytanie konkursowe - co zawiera formuła?
Zapewniam, że nie zawiera żadnych "ukrytych" odwołań i że w każdej komórce arkusza wynik byłby taki sam.
W Nowym Roku życzę Wszystkim jasności umysłu i oczekuję odpowiedzi (może być na Forum - kto pierwszy, ten lepszy).

Formuła.png
Plik ściągnięto 55 raz(y) 19.75 KB

ID posta: 359453 Skopiuj do schowka
 
 
Wormsek 



Zaproszone osoby: 2
Wersja: Win Office 2016
Pomógł: 912 razy
Posty: 5269
Wysłany: 03-01-2019, 18:42   

Patrząc po wzorach, to ciąg geometryczny z innym wzorem dla parzystych i nie parzystych w ciele matematycznym, gdzie działanie matematyczne + jest zdefiniowane raz jako plus, ale może być mnożeniem ;-)
_________________
Pozdro
Worm

FAQ - Najczęściej zadawane pytania.
JAK KORZYSTAĆ Z SZUKAJKI
Słownik funkcji

Znajdź nas na Facebook'u

A może fajny dodatek do excela?
ID posta: 359589 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1273 razy
Posty: 4362
Wysłany: 04-01-2019, 11:14   

Wormsek, dziękuję za zainteresowanie, ale coś chyba przefilozofowałeś ;-) . To nie jest ciąg arytmetyczny ani geometryczny. Zwróć uwagę, że jest to jedna i ta sama formuła i jedyna różnica, to to, co jest do niej dodane (lub odjęte) w komórce. Wszystkie inne komórki w arkuszu (poza widocznymi) są puste, nie ma też żadnych innych formuł.
Trzeba sobie najpierw odpowiedzieć na pytanie, czym musi charakteryzować się taka formuła, żeby dawała różne wyniki w zależności od tego, co jeszcze jest w komórce. Wszystkie wyniki są powtarzalne, nie ma tu żadnej funkcji losowej.
ID posta: 359657 Skopiuj do schowka
 
 
Wormsek 



Zaproszone osoby: 2
Wersja: Win Office 2016
Pomógł: 912 razy
Posty: 5269
Wysłany: 04-01-2019, 22:47   

Maciej Gonet napisał/a:
rzeba sobie najpierw odpowiedzieć na pytanie, czym musi charakteryzować się taka formuła, żeby dawała różne wyniki w zależności od tego, co jeszcze jest w komórce. Wszystkie wyniki są powtarzalne, nie ma tu żadnej funkcji losowej.

Hehe rozumiem o co chodzi, ale źle to ująłeś ;-) . Do większości formuł jak dodasz coś innego to masz inne wyniki ;-)
_________________
Pozdro
Worm

FAQ - Najczęściej zadawane pytania.
JAK KORZYSTAĆ Z SZUKAJKI
Słownik funkcji

Znajdź nas na Facebook'u

A może fajny dodatek do excela?
ID posta: 359707 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1234 razy
Posty: 4296
Wysłany: 05-01-2019, 13:26   

Mi się wydaje, że włączona jest opcja "Enable iterative calculation", ale nie potrafię wyprowadzić formuły.
ID posta: 359748 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1273 razy
Posty: 4362
Wysłany: 07-01-2019, 10:28   

Apollo, masz rację, to jest formuła iteracyjna, inaczej nie mogłaby tak działać.
Ponieważ są dwie osoby zainteresowane (a może więcej, tylko się nie ujawniły), to spróbuję naprowadzić. Najpierw przeanalizujmy wyniki, jakie otrzymujemy w komórkach. Jeśli w komórce jest
Kod:
=formuła + r
gdzie r jest jakąś liczbą, to wynik ma postać:
Kod:
r + PIERWIASTEK(czegoś)
To "coś" zależy od r: dla r=0;1;2;3 mamy coś = 4;5;8;13. Łatwo przypisać do tego formułę:
Kod:
coś = 4 + r^2
Do tego schematu nie pasuje wynik z r=-1, ale to potraktujemy jak wyjątek i zostawimy na później. Zatem na ogół wynik zwracany w komórce ma postać:
Kod:
r + PIERWIASTEK(r^2 + 4)
Kto pamięta jeszcze coś ze szkolnej matematyki, powinno mu się skojarzyć z równaniem kwadratowym.
A teraz drugi trop, na który zwrócił uwagę apollo. Formuła musi być iteracyjna, to znaczy odwoływać się do wartości komórki, w której jest zapisana, inaczej takie wyniki nie byłyby możliwe. Jeśli formuła jest iteracyjna, to musi spełniać równanie:
Kod:
x = formuła(x) + r
x oznacza wartość komórki. Sama formuła musi zależeć od wartości komórki, w której jest zapisana, równocześnie nie może zawierać w sobie r (bo formuła jest jedna i ta sama dla różnych r i "nie wie", co do niej zostanie dopisane w komórce.
Może teraz będzie łatwiej dojść do sedna! :->
ID posta: 359886 Skopiuj do schowka
 
 
DwaNiedźwiedzie 
Excel Expert



Pomógł: 215 razy
Posty: 551
Wysłany: 07-01-2019, 13:16   

Wprawdzie wyniki zgadzają się tylko dla 1, 2 i 3, ale reszta to już pewnie odrobina matematyki - za to działa bez włączania iteracji :)
Kod:
=PIERWIASTEK(PODSTAW(O.KOMÓRCE(6;Arkusz1!A1);"=formula";"")^2+4)
ID posta: 359904 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1273 razy
Posty: 4362
Wysłany: 07-01-2019, 13:40   

Brawo :clap , DwaNiedźwiedzie, dla zera też działa, pod warunkiem, że wpisze się je jawnie
Kod:
=formula+0
Dla -1 wynik jest inny niż u mnie, bo u mnie -1 było wyjątkiem.
Generalnie moja koncepcja rozwiązania była zupełnie inna, bez odwoływania się do tekstu formuły, więc konkursu nie zamykamy, może pojawią się jeszcze interesujące pomysły.
Rozwiązanie jest bardzo interesujące, bo faktycznie omija iteracje, a wynik daje taki jakby były użyte.
ID posta: 359906 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1273 razy
Posty: 4362
Wysłany: 11-01-2019, 11:36   

No, widzę że nikt nie chce podążać moim tokiem myślenia, ani nie proponuje własnych pomysłów (poza Misiami), więc kontynuuję moją podpowiedź.
Wyrażenie, które podałem poprzednio łatwo powiązać z równaniem kwadratowym
Kod:
x^2 - 2*r*x - 4 = 0
Przepiszmy to równanie w takiej postaci, aby było zgodne z zapisem formuły iteracyjnej (dodajemy do obu stron x^2):
Kod:
2*x*(x - r) = x^2 + 4
x - r = (x^2 + 4)/(2*x) = formuła(x)
I już mamy naszą formułę iteracyjną, niezależną od r. Teraz już tylko jeden krok, żeby to zadziałało w Excelu (oczywiście po włączeniu obliczeń iteracyjnych).
ID posta: 360232 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 2016
Pomógł: 1273 razy
Posty: 4362
Wysłany: 24-01-2019, 14:03   

Zapał w narodzie osłabł, więc pora wreszcie, abym podał swoją koncepcję rozwiązania. Po włączeniu zgody na obliczenia iteracyjne, należałoby zdefiniować formułę iteracyjną dla komórki aktywnej np. A4 w postaci:
Kod:
=(A4^2+4)/(2*A4)
albo
Kod:
=A4/2+2/A4

Ale taka formuła nie może w tej formie działać, bo początkową wartością komórki jest 0, więc tu byłoby dzielenie przez 0. Trzeba zatem obudować naszą formułę funkcją JEŻELI albo JEŻELI.BŁĄD, eliminującą tę niedogodność. Trzeba przy tym określić przybliżenie początkowe iteracji:
Kod:
=JEŻELI.BŁĄD(A4/2+2/A4; p_pocz)
Równanie kwadratowe ma dwa rozwiązania, więc jeśli przyjmiemy ujemne przybliżenie początkowe, to z równania
Kod:
= formuła

dostaniemy wynik ujemny, a gdy dodatnie - dodatni. Od wyboru przybliżenia początkowego zależy jednak wynik formuły w przypadkach szczególnych. Gdy przyjmiemy określoną wartość p_pocz, a później użyjemy w komórce formuły:
Kod:
= formuła - p_pocz
otrzymamy w wyniku 0, bo komórka zwraca 0, formuła zamienia to na p_pocz, komórka zwraca 0 itd. po 2 iteracjach z tym samym wynikiem obliczenia się kończą.
Jeśli wpis w komórce ma postać:
Kod:
= formuła + r

gdzie
Kod:
r > -p_pocz
otrzymamy wynik ze wzoru:
Kod:
r + (r^2 + 4)^0,5
Jeśli użyjemy w komórce
Kod:
r < -p_pocz
otrzymamy wynik ze wzoru:
Kod:
r - (r^2 + 4)^0,5

Zatem w naszej zagadce przyjąłem, że
Kod:
p_pocz = 1

Obliczenia iteracyjne zorganizowane w podobny sposób mogą być czasem wygodniejsze od użycia procedury Szukaj wyniku, jeśli mamy rozwiązać kilka podobnych równań, bo tę formułę można kopiować, a Szukaj wyniku trzeba konfigurować za każdym razem, albo pisać makro.

Zagadka_formuła.xlsx
Pobierz Plik ściągnięto 24 raz(y) 9.49 KB

ID posta: 361220 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