< návrat zpět

MS Excel


Téma: Výpočet bodu v grafe rss

Zaslal/a 6.2.2024 18:25

Ahojte potreboval by som pomoc s výpočtom neviem či by sa to dalo previesť do grafu alebo cez vzorce tu je príklad https://www.rajce.idnes.cz/y64m/album/graf/1624339427

Hodnoty bodov ABC.... sú vypočítané iným postupom a menia sa z nich je zostrojený graf hodnoty jednotlivých grafov sú určené stredný vždy 0 krajné sa sa menia napríklad- 500 0 -500 ... zadám si bod ako v príklade 14200 vytiahol som si ho na osi X a hodnota -250 ktorá je tiež určená mi určí polohu výsledného bodu keďže -250 je stredná hodnota medzi 0 a -500 tak bod bude presne v strede medzi grafom s určenou hodnotou 0 a -500. Potom ho vytiahnem na os Y a to je hodnota ktorú potrebujem zistiť a keďže je v strede medzi osami ktoré majú ešte hodnoty tam kde sa vynesená priamka od 14200 pretne s grafmi medzi ktorými sa bod nachádza tomto prípade +1 až -1 tam je výsledok 100 na osi Y a 0

Zaslat odpověď >

Strana:  1 2 3   další »
#056135
Lader
Zdravím,
pokud si kreslil ten graf, tak si myslím že se jedná o Problém XY.
Znáš několik bodů a potřebuješ zjistit mezilehlý bod.
A myslíš si, že nejlepší cesta je přes graf.
Pokud tomu tak je, pak graf nepotřebuješ a bod jde snadno vypočítat pomocí Lagrangeovy interpolace.citovat
#056136
Lader
Napsal jsem makro, které používá lineární interpolaci:
Function Najdi(Xa1#,Ya1#,Za1#,Xa2#,Ya2#,Za2#,Xb1#,Yb1#,Zb1#,Xb2#,Yb2#,Zb2#, X#, Z#) As Variant
Dim Y#
On Local Error GoTo Chyba
Ya=-((Xa1-X)*Ya2+(X-Xa2)*Ya1)/(Xa2-Xa1)
Yb=-((Xb1-X)*Yb2+(X-Xb2)*Yb1)/(Xb2-Xb1)
Za=-((Xa1-X)*Za2+(X-Xa2)*Za1)/(Xa2-Xa1)
Zb=-((Xb1-X)*Zb2+(X-Xb2)*Zb1)/(Xb2-Xb1)
Y=(Ya*Zb-Yb*Za+(Yb-Ya)*Z)/(Zb-Za)
Najdi = Y
Exit Function
Chyba:
Najdi = "?"
End Function

Zadáš do něj čtyři body nejblíž k hledanému bodu.
Příklad - hledáš hodnotu Y pro souřadnici X=14200 a hodnotu Z=0.
Najdeš si nejbližší čtyři body, dva na červené lince (budou mít index A) a dva na zelené lince (budou mít index B) a vyplníš parametry takto:
Tedy Xa1=13500 (nejbližší menší jak 14200), tomu odpovídá Ya1=75 a Za1=0.
Dále Xa2=15000 (nejbližší větší jak 14200), tomu odpovídá Ya2=200 a Za2=3.
Na druhé lince Xb1=12500 (nejbližší menší jak 14200), tomu odpovídá Yb1=-75 a Zb1=-7.
Dále Xb2=14750 (nejbližší větší jak 14200), tomu odpovídá Yb2=125 a Zb2=2.
Zapíšeš hledanou hodnotu X=14200 a Z=0.
Funkce ti vrátí vypočtenou hodnotu Y=83.26389.

Neodpovídá tomu co si nalezl (Y=100), protože ta tvoje kresba není úplně dokonalá.citovat
#056138
Lader
Ještě jsem makro upravil pro zadání všech devíti bodů (Lagrangeova interpolace). Výpočet je znatelně přesnější, není to lineární interpolace, ale kvadratická, takže je proložená křivkami a navíc ve dvou osách. Popis je uvnitř makra. Dal bych sem hotový program, ale nemám Excel, pouze Calc a nevím zda Excel to umí načíst. V Calcu to funguje a z mé zkušenosti předpokládám, že po nakopírování makra do Excelu to bude fungovat také. (V případě potřeby zde můžu dát funkční program v Calcu)
Function NajdiZ9Bodu(R, X#, Z#) As Variant
' R zadání pole dat 9 řádků a tři sloupce
' - první sloupec hodnoty X
' - druhý sloupec odpovídající hodnoty Y
' - třetí sloupec odpovídající hodnoty Z
' přičemž první 3 řádky je červená linka, další 3 řádky zelená linka a poslední 3 řádky červená linka
' X a Z jsou zadané hodnoty pro které hledáme hodnotu Y
'
' Lagrange interpolace 3 body (dvourozměrná a kvadratická, proloženo křivkou ve dvou osách)
Dim Yc#, Zc#, Yz#, Zz#, Ym#, Zm#, Y#
On Local Error GoTo Chyba

' Červená linka (Výpočet souřadnice Yc a odpovídající hodnota Zc):
Yc=(R(1,2)*(X-R(2,1))*(X-R(3,1)))/((R(1,1)-R(2,1))*(R(1,1)-R(3,1))) _
+(R(2,2)*(X-R(1,1))*(X-R(3,1)))/((R(2,1)-R(1,1))*(R(2,1)-R(3,1))) _
+(R(3,2)*(X-R(1,1))*(X-R(2,1)))/((R(3,1)-R(1,1))*(R(3,1)-R(2,1)))
Zc=(R(1,3)*(X-R(2,1))*(X-R(3,1)))/((R(1,1)-R(2,1))*(R(1,1)-R(3,1))) _
+(R(2,3)*(X-R(1,1))*(X-R(3,1)))/((R(2,1)-R(1,1))*(R(2,1)-R(3,1))) _
+(R(3,3)*(X-R(1,1))*(X-R(2,1)))/((R(3,1)-R(1,1))*(R(3,1)-R(2,1)))

' Zelená linka (Výpočet souřadnice Yz a odpovídající hodnota Zz):
Yz=(R(4,2)*(X-R(5,1))*(X-R(6,1)))/((R(4,1)-R(5,1))*(R(4,1)-R(6,1))) _
+(R(5,2)*(X-R(4,1))*(X-R(6,1)))/((R(5,1)-R(4,1))*(R(5,1)-R(6,1))) _
+(R(6,2)*(X-R(4,1))*(X-R(5,1)))/((R(6,1)-R(4,1))*(R(6,1)-R(5,1)))
Zz=(R(4,3)*(X-R(5,1))*(X-R(6,1)))/((R(4,1)-R(5,1))*(R(4,1)-R(6,1))) _
+(R(5,3)*(X-R(4,1))*(X-R(6,1)))/((R(5,1)-R(4,1))*(R(5,1)-R(6,1))) _
+(R(6,3)*(X-R(4,1))*(X-R(5,1)))/((R(6,1)-R(4,1))*(R(6,1)-R(5,1)))

' Modrá linka (Výpočet souřadnice Ym a odpovídající hodnota Zm):
Ym=(R(7,2)*(X-R(8,1))*(X-R(9,1)))/((R(7,1)-R(8,1))*(R(7,1)-R(9,1))) _
+(R(8,2)*(X-R(7,1))*(X-R(9,1)))/((R(8,1)-R(7,1))*(R(8,1)-R(9,1))) _
+(R(9,2)*(X-R(7,1))*(X-R(8,1)))/((R(9,1)-R(7,1))*(R(9,1)-R(8,1)))
Zm=(R(7,3)*(X-R(8,1))*(X-R(9,1)))/((R(7,1)-R(8,1))*(R(7,1)-R(9,1))) _
+(R(8,3)*(X-R(7,1))*(X-R(9,1)))/((R(8,1)-R(7,1))*(R(8,1)-R(9,1))) _
+(R(9,3)*(X-R(7,1))*(X-R(8,1)))/((R(9,1)-R(7,1))*(R(9,1)-R(8,1)))

' Nyní zjistíme hledaný bod Y z předcházejících výsledků:
Y=(Yz*(Z-Zc)*(Z-Zm))/((Zz-Zc)*(Zz-Zm)) _
+(Ym*(Z-Zc)*(Z-Zz))/((Zm-Zc)*(Zm-Zz)) _
+(Yc*(Z-Zm)*(Z-Zz))/((Zc-Zm)*(Zc-Zz))

' Výsledek:
NajdiZ9Bodu = Y
Exit Function
Chyba:
NajdiZ9Bodu = "?"
End Function
citovat
#056143
Lader
Aby interpolace správně fungovala musí být interpolované hodnoty uvnitř bodů. V nejhorším případě aspoň v modrém regionu. Mimo tuto plochu se jedná o extrapolaci a je to víceméně hádání.
V obrázku XY-Z.png je vidět mírný nesoulad dat. Po hodnotě dvě následuje čtyřka a pak trojka a ještě dál pětka, což vypadá na nekvalitní data. Přičemž trojka je vlevo od pětky a čtyřky. Dole zase naopak hodnoty směrem v pravo klesají.
Příloha: png56143_xy-z.png (56kB, staženo 11x)
56143_xy-z.png
citovat
#056144
Lader
V dalším obrázku XZ-Y.png je to vidět ještě více. Hodnota od 125 stoupá k 200 pak klesá na 150 a zase stoupá na 225. Což evidentně vypadá na nekvalitní zdroj, pokud to ovšem není záměr. Pak i interpolovaná data půjdou do extrémních vln ...
Vzhledem k tomu že se interpolují data jak v souřadnicích XY tak i YZ tak se obě podivnosti zkombinují a výsledek nemusí vypadat podle očekávání.
Příloha: png56144_xz-y.png (49kB, staženo 13x)
56144_xz-y.png
citovat
#056145
Lader
Takhle asi vypadají možné hodnoty. Vyloučil jsem data které extrémně vybočovaly.
Příloha: png56145_mozne-hodnoty.png (206kB, staženo 13x)
56145_mozne-hodnoty.png
citovat
#056146
Lader
Pokud je křivost prostoru, který chceme popsat vyšší než nám umožňuje interpolační funkce, pak výsledek nebude dle očekávání.
Lze to vyřešit dvěma způsoby, buď použijeme podstatně více vstupních bodů než 9, pak funkce bude mnohem složitější, například pro 16 bodů je to kubická křivka ve dvou osách, ale ani to nemusí stačit. Nebo body budeme volit mnohem blíže k sobě, kdy křivost prostoru je mnohem menší a výsledky budou přijatelnější.citovat
#056147
avatar
@lader
Robíš si tu diplomovku prácu? Zadávateľa to očividne nezaujíma.citovat
#056158
avatar
Voľbu umiestnenia
bodov a ani ich počet neviem ovplyvniť bodov musí byť 9 a vzdialenosti sú dané pomoholo by keby niekto pomohol aspoň zostrojiť graf podľa nákresu tri grafy a pospájané body ktoré by sa menili podľa hodnôt iného výpočtu v exceli 9 bodov tri grafy rozdelené podľa hodnoty bodu ktorá je tiež daná tým vypoctom v exceli ďakujemcitovat
#056161
Lader
Graf není problém nakreslit. Jako ukázku posílám v příloze. Je tam vidět devět zadaných bodů, každé tři proložené interpolační křivkou. A interaktivně se mění černá příčka podle zadání hodnot X a Y. Je tam také znázorněn bod vypočítané hodnoty Z. Myslím, že máš trochu jiné pojmenování (Y a Z prohozeno), ale už jsem to tak nechal.
Je to vlastně to samé co jsem již poslal, jenom je to nakreslené místo výpočtu a hodnoty jsem si nějak zvolil.
Příloha: png56161_graf.png (37kB, staženo 16x)
56161_graf.png
citovat

Strana:  1 2 3   další »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Helios iNuvio

Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.

On-line nástroje