Excel - problém s řešením
Napsal: 30 bře 2012 14:17
od kippp
Zdravím,
Potřeboval bych poradit s jedním problémem v Excelu. Řeším tabulku o nějakých 8000 řádcích a na každém řádku mám jednu buňku, která má vzorec závislí na jedné proměnné (vzorec vypadá asi nějak takto =a.x4+b.x3+c.x2+d.x+e), hodnoty a, b, c, d, e jsou konstanty, které si tam sám vkládám a tu proměnou x mám v další buňce a právě stým bych potřeboval poradit, jak mám udělat, aby se mi automaticky vypočítala ta buňka s tou proměnou, když vím, že ta rovnice má být rovna nule (0). Vím, že se to dá řešit řešitelem, ale počítá to jen pro jeden řádek, a když změním konstanty a, b, c, d, e, tak se mi to nezmění a to je to co bych potřeboval vyřešit, aby se ten výpočet dal zkopírovat na vše 8000 řádků a aby se ta proměnná x přepočítávala, když změním konstanty a, b, c, d, e… Zatím děkuji všem za každou radu…
Re: Excel - problém s řešením
Napsal: 30 bře 2012 17:19
od cmuch
Vítej na PC-Help
Tady v tomto případě by se hodila nějaká ukázka s pár řádkama.
Re: Excel - problém s řešením
Napsal: 30 bře 2012 19:17
od kippp
Zdravím,
Přiložil jsem pár řádků daného souboru. Žádaný problém je na listu výpočty a ten vzorec je ve sloupci AD a proměnná x je ve sloupci AE. Zatím děkuji… PS: můj mail:
kippp@seznam.cz
Re: Excel - problém s řešením
Napsal: 30 bře 2012 19:35
od cmuch
Nějak to pořád nechápu.
Vzorec v AD počítá tak jak má a nevím podle čeho by se měla změnit ta proměná v AE.
Re: Excel - problém s řešením
Napsal: 30 bře 2012 20:36
od Azuzula
Možná už vím jak to myslí. V buňce AE je potřeba něco co dopočte tu proměnnou tak, aby se výsledek v AD rovnal nule.
Tj. aby se například konstanta v AE11 rovnala přibližně 3,229692 (což jsem "dopočítala" pokusným zapisováním desetinných čísel aby se výsledek co možná nejvíce zmenšil) takže výsledek v AD11 je -0,0000263146993830787 což je po zaokrouhlení nula.
Mám pravdu?
Re: Excel - problém s řešením
Napsal: 31 bře 2012 12:00
od kippp
Přesně jak říkáš, tak to potřebuji...
Re: Excel - problém s řešením
Napsal: 31 bře 2012 14:28
od Azuzula
Tak jsem nakonec našla jak to udělat s pomocí řešitele ve VBA tady:
http://peltiertech.com/Excel/SolverVBA.htmlNa celý sloupek to jde snadno udělat pomocí cyklu. Po delších testech to funguje dobře jen je to kapku pomalé, ale i sama funkce řešitele je pomalá při výpočtu, takže tisíce řádků může trvat i několik minut.
--- Doplnění předchozího příspěvku (31 Bře 2012 17:35) ---
Nakonec řešení nebylo tak snadné jak jsem myslela
Ale nakonec se podařilo. Ale mám excel 2010 a i když to je uloženo v kompatibilním formátu, nemusí to chodit na 100% tak uvidíme.
Na kompletní přepočítání celého listu zkopírujte následující makro do modulu pro makra:
Kód: Vybrat vše
Option Explicit
Sub vyresit()
Dim a, i As Double
Application.ScreenUpdating = False
a = Cells(Rows.Count, 30).End(xlUp).Row
Range("AE4").Select
For i = 4 To a
' SolverReset 's vypnutým resetem jede makro rychleji, na první pohled to vypadá že to nemá příliš důležitou funkci
SolverAdd CellRef:="$$AE$4:$AE$" & a, Relation:=1 ', FormulaText:="4" 'FormulaText občas zapíše do řešitele omezení a pak přestává fungovat a nevím proč
SolverOk SetCell:="$AD$" & i, MaxMinVal:=3, ValueOf:="0", ByChange:="$AE$4:$AE$" & a
SolverSolve True
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
A pro jednotlivé přepočty po změně konstanty zkopírujte následující makro do modulu listu "výpočty":
Kód: Vybrat vše
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aRange As Range
Dim aCell As Range
Dim tRow, tCell As Double
On Error Resume Next
'proces řešitele a kontroly změn v listu se asi příliš nesnesou a tak to po každém výpočtu hodí chybu: 1004 Method 'Range' of object '_Worksheet' failed
Set aRange = Intersect(Target, Range("E:E,V:V,AA:AA,AC:AC")) 'tohle zařídí kontrolu ve sloupcích
tRow = Target.Row
If Not aRange Is Nothing Then
If Target = 0 Then Cells(tRow, 31).Value = 0: End
For Each aCell In aRange
' SolverReset 's vypnutým resetem jede makro rychleji, na první pohled to vypadá že to nemá příliš důležitou funkci
SolverAdd CellRef:="$AE$" & tRow, Relation:=1 ', FormulaText:="100" 'FormulaText občas zapíše do řešitele omezení a pak přestává fungovat a nevím proč
SolverOk SetCell:="$AD$" & tRow, MaxMinVal:=3, ValueOf:="0", ByChange:="$AE$" & tRow
SolverSolve True
Next
End If
End Sub
PS: Musím se přiznat že tohle řešení mě docela proškolilo, zase jsem se naučila pár nových věcí :-)
Re: Excel - problém s řešením
Napsal: 02 dub 2012 14:55
od kippp
Mockrát díky za radu funguje to dobře... Zatí dík