{"id":1321,"date":"2021-06-25T22:30:28","date_gmt":"2021-06-25T22:30:28","guid":{"rendered":"https:\/\/garikoitz.info\/blog\/?p=1321"},"modified":"2021-07-08T17:30:28","modified_gmt":"2021-07-08T17:30:28","slug":"regresion-lineal-y-no-lineal-excel-vs-net","status":"publish","type":"post","link":"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/","title":{"rendered":"Regresi\u00f3n Lineal y No Lineal &#8211; Excel VS .Net"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">\u00cdndice<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Alternar tabla de contenidos\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Introduccion\" >Introducci\u00f3n<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Datos\" >Datos<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Regresion_Lineal\" >Regresi\u00f3n Lineal<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Excel\" >Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#NET\" >NET<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Regresion_No_Lineal\" >Regresi\u00f3n No Lineal<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Excel-2\" >Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#NET-2\" >NET<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Resultados_y_Conclusiones\" >Resultados y Conclusiones<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Descargas\" >Descargas<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Referencias\" >Referencias<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Enlaces\" >Enlaces<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/#Libros\" >Libros<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Introduccion\"><\/span>Introducci\u00f3n<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">La <a rel=\"noreferrer noopener\" href=\"https:\/\/es.wikipedia.org\/wiki\/Ecuaci%C3%B3n_de_Antoine\" target=\"_blank\">ecuaci\u00f3n de Antoine<\/a> es utilizada para obtener el valor de la presi\u00f3n de vapor (P) en funci\u00f3n de la temperatura (T) para componentes puros y se basa en una correlaci\u00f3n de tres par\u00e1metros A, B y C. Dicho esto, el objetivo de esta entrada es obtener los coeficientes de la <a rel=\"noreferrer noopener\" href=\"https:\/\/es.wikipedia.org\/wiki\/Ecuaci%C3%B3n_de_Antoine\" target=\"_blank\">ecuaci\u00f3n de Antoine<\/a> para un rango de temperaturas y presiones concretos dada la ecuaci\u00f3n 1.<\/p>\n\n\n\n<div class=\"wp-block-katex-display-block katex-eq\" data-katex-display=\"true\"><pre>\\begin{align}\n&amp; log(P) = A-B \/ (T + C)\n\\end{align}<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Para ello utilizaremos <a rel=\"noreferrer noopener\" href=\"https:\/\/www.microsoft.com\/es-es\/microsoft-365\/excel\" target=\"_blank\">Excel <\/a>como herramienta b\u00e1sica que casi todos conocer\u00e9is y tendr\u00e9is instalada en vuestro equipo y adem\u00e1s veremos como afrontar este tipo de problemas en .NET con la ayuda de una librer\u00eda externa bastante interesante como es <a href=\"http:\/\/accord-framework.net\/\" target=\"_blank\" rel=\"noreferrer noopener\">Accord<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">No es la intenci\u00f3n de esta entrada profundizar en conceptos matem\u00e1ticos complejos ni entrar en detalle en el concepto de regresi\u00f3n, m\u00e1s bien se pretende dar unas pinceladas para poder ser capaces de plantear y resolver problemas similares.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Datos\"><\/span>Datos<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>Temperatura (\u00baF)<\/strong><\/td><td><strong>Presi\u00f3n (psia)<\/strong><\/td><\/tr><tr><td>65,44<\/td><td>41,17<\/td><\/tr><tr><td>73,78<\/td><td>47,32<\/td><\/tr><tr><td>82,11<\/td><td>54,15<\/td><\/tr><tr><td>90,45<\/td><td>61,70<\/td><\/tr><tr><td>98,78<\/td><td>70,02<\/td><\/tr><tr><td>107,12<\/td><td>79,15<\/td><\/tr><tr><td>115,46<\/td><td>89,14<\/td><\/tr><tr><td>123,79<\/td><td>100,03<\/td><\/tr><tr><td>132,13<\/td><td>111,87<\/td><\/tr><tr><td>140,46<\/td><td>124,70<\/td><\/tr><tr><td>148,80<\/td><td>138,58<\/td><\/tr><tr><td>157,13<\/td><td>153,54<\/td><\/tr><tr><td>165,47<\/td><td>169,64<\/td><\/tr><tr><td>173,80<\/td><td>186,90<\/td><\/tr><tr><td>182,14<\/td><td>205,39<\/td><\/tr><\/tbody><\/table><figcaption>Tabla de datos<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Regresion_Lineal\"><\/span>Regresi\u00f3n Lineal<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">La ecuaci\u00f3n de Antoine no es lineal, por lo que para plantear la soluci\u00f3n lineal lo primero es \u00ablinealizar\u00bb dicha ecuaci\u00f3n.<\/p>\n\n\n\n<div class=\"wp-block-katex-display-block katex-eq\" data-katex-display=\"true\"><pre>\\begin{align}\n&amp; log(P) = A-B \/ (T + C)\\\\ &amp; T * log(P) + C * log(P) = A * T + A * C - B\\\\ &amp; log(P) = A + (A * C - B) \/ T - C * log(P) \/ T\n\\end{align}<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Planteamos la ecuaci\u00f3n 4 como polin\u00f3mica.<\/p>\n\n\n\n<div class=\"wp-block-katex-display-block katex-eq\" data-katex-display=\"true\"><pre>\\begin{align}\n&amp; y = a0 + a1 * x1 + a2 * x2\\\\\n&amp; y = log(P)\\\\\n&amp; x1 = 1 \/ T \\\\\n&amp; x2 = log(P) \/ T\n\\end{align}<\/pre><\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Excel\"><\/span>Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Obtener los coeficientes en Excel es tan sencillo como plantear los datos y usar la funci\u00f3n <strong>ESTIMACION.LINEAL<\/strong>. Con esto obtenemos los valores de a0, a1 y a2. Relacionando las ecuaciones 4 y 5 calculamos los coeficientes A, B y C de la siguiente manera.<\/p>\n\n\n\n<div class=\"wp-block-katex-display-block katex-eq\" data-katex-display=\"true\"><pre>\\begin{align}\n&amp; A = a0\\\\\n&amp; B = a0*-a2-a1\\\\\n&amp; C = a2 \\\\\n\\end{align}<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Para comprobar que los par\u00e1metros obtenidos son correctos podemos calcular la presi\u00f3n en funci\u00f3n de ellos mediante la ecuaci\u00f3n 12 y comparar gr\u00e1ficamente.<\/p>\n\n\n\n<div class=\"wp-block-katex-display-block katex-eq\" data-katex-display=\"true\"><pre>\\begin{align}\n&amp; Pcalc = 10^{A - B \/ (T + C)}\n\\end{align}<\/pre><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><a href=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"311\" src=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02-1024x311.png\" alt=\"\" class=\"wp-image-1384\" srcset=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02-1024x311.png 1024w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02-300x91.png 300w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02-768x233.png 768w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02-604x183.png 604w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Excel_est_lineal02.avif 1426w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Regresi\u00f3n lineal con Excel<\/figcaption><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">El par\u00e1metro R\u00b2, tambi\u00e9n llamado coeficiente de determinaci\u00f3n, que nos devuelve la estimaci\u00f3n lineal,  es la relaci\u00f3n entre el valor real y el calculado, siendo 1 la perfecci\u00f3n, por lo que podemos concluir que los coeficientes obtenidos son buenos.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"NET\"><\/span>NET<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">La implementaci\u00f3n en .NET de la soluci\u00f3n lineal con la librer\u00eda de Accord no supone mayor problema. Como se puede ver a continuaci\u00f3n en el fragmento de c\u00f3digo, el planteamiento es sencillo y apenas supone diez l\u00edneas de c\u00f3digo.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"true\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Dim Tx, Px As Double\n'inputs(x1)(x2)  -> x1 = 1 \/ T  |  x2 = log10(P) \/ T\nDim inputs As Double()() = New Double(14)() {}\nFor i = 0 To 14\n\tTx = DGV_API.Rows(i).Cells(0).Value\n\tPx = DGV_API.Rows(i).Cells(1).Value\n\tinputs(i) = New Double() {1 \/ Tx, Math.Log10(Px) \/ Tx}\nNext\n'outputs = y -> y = log10(P)\nDim outputs As Double() = {1.615316424, 1.675711408, 1.734153592, 1.790736182, 1.845546545, 1.898666658, 1.950173521, 2.000139525, 2.048632796, 2.0957175, 2.141454129, 2.185899763, 2.229108299, 2.271130681, 2.312015089}\n'\nDim ols As New OrdinaryLeastSquares()\nols.UseIntercept = True\nDim regression As MultipleLinearRegression = ols.Learn(inputs, outputs)\n'\nDim a1 As Double = regression.Weights(0)\nDim a2 As Double = regression.Weights(1)\nDim a0 As Double = regression.Intercept\n' y = a0 + a1*x1 + a2*x2\nDim predicted As Double() = regression.Transform(inputs)\nDim Serror As Double = New SquareLoss(outputs).Loss(predicted)\nDim r2 As Double = New RSquaredLoss(2, outputs).Loss(predicted)\n'Resultados\nA = a0\nB = (a0 * Math.Abs(a2) - a1)\nC = Math.Abs(a2)<\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><a href=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"319\" src=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal-1024x319.png\" alt=\"\" class=\"wp-image-1364\" srcset=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal-1024x319.png 1024w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal-300x93.png 300w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal-768x239.png 768w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal-604x188.png 604w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_lineal.avif 1461w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Regresi\u00f3n Lineal con .NET<\/figcaption><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Tanto los coeficientes de Antoine como el par\u00e1metro R\u00b2 son similares a los ofrecidos por Excel.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Regresion_No_Lineal\"><\/span>Regresi\u00f3n No Lineal<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Excel-2\"><\/span>Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">La \u00fanica dificultad para resolver un problema de regresi\u00f3n no lineal con Excel radica en plantear el problema correctamente y tener activado el <a href=\"https:\/\/www.google.com\/search?q=activar+solver+en+excel&amp;oq=activar+solver+en+excel&amp;aqs=chrome..69i57j0l3j0i20i263j0l2.6111j0j7&amp;sourceid=chrome&amp;ie=UTF-8\" target=\"_blank\" rel=\"noreferrer noopener\">complemento Solver<\/a>. Nuestro planteamiento parte del c\u00e1lculo de una presi\u00f3n (Pcalc) que debe ser igual o lo m\u00e1s parecido posible a la presi\u00f3n de nuestra tabla (P). Simplificando, lo que buscamos es <strong>minimizar el error<\/strong> de la diferencia entre P y Pcalc alterando los coeficientes de Antoine A, B y C. Pcalc se calcula como anteriormente mediante la ecuaci\u00f3n 12 y para el error, en realidad se utiliza el cuadrado del error, y he obtenido mejores resultados con la ecuaci\u00f3n 14 que con la 13.<\/p>\n\n\n\n<div class=\"wp-block-katex-display-block katex-eq\" data-katex-display=\"true\"><pre>\\begin{align}\n&amp; (P-Pcalc)^2\\\\\n&amp; (A-B \/ (T + C))-log(P))^2\n\\end{align}<\/pre><\/div>\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"lyte-wrapper fourthree\" style=\"width:420px;max-width:100%;margin:5px;\"><div class=\"lyMe\" id=\"WYL_t-vXfZ1xUvU\"><div id=\"lyte_t-vXfZ1xUvU\" data-src=\"\/\/i.ytimg.com\/vi\/t-vXfZ1xUvU\/hqdefault.jpg\" class=\"pL\"><div class=\"tC\"><div class=\"tT\"><\/div><\/div><div class=\"play\"><\/div><div class=\"ctrl\"><div class=\"Lctrl\"><\/div><div class=\"Rctrl\"><\/div><\/div><\/div><noscript><a href=\"https:\/\/youtu.be\/t-vXfZ1xUvU\" rel=\"nofollow\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i.ytimg.com\/vi\/t-vXfZ1xUvU\/0.jpg\" alt=\"YouTube video thumbnail\" width=\"420\" height=\"295\" \/><br \/>Ver este v\u00eddeo en YouTube<\/a><\/noscript><\/div><\/div><div class=\"lL\" style=\"max-width:100%;width:420px;margin:5px;\"><\/div><figcaption>Regresi\u00f3n No Lineal con Excel usando Solver<\/figcaption><\/figure>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"NET-2\"><\/span>NET<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">La implementaci\u00f3n de la regresi\u00f3n no lineal mediante la librer\u00eda de Accord no es muy compleja pero con respecto a Excel tiene una peque\u00f1a diferencia, y es que nos obliga a plantear la <strong>funci\u00f3n <\/strong>y el <strong><a href=\"https:\/\/es.khanacademy.org\/math\/multivariable-calculus\/multivariable-derivatives\/partial-derivative-and-gradient-articles\/a\/the-gradient\" target=\"_blank\" rel=\"noreferrer noopener\">gradiente <\/a><\/strong>de la funci\u00f3n. El gradiente es un vector que se construye mediante las <strong>derivadas parciales de cada par\u00e1metro<\/strong>. Que no cunda el p\u00e1nico, si no te apetece coger l\u00e1piz y papel, se pueden obtener las <a rel=\"noreferrer noopener\" href=\"https:\/\/www.wolframalpha.com\/input\/?i=d%2FdA+%2810%5E%28A-B+%2F+%28T%2BC%29%29%29+%3B+d%2FdB+%2810%5E%28A-B+%2F+%28T%2BC%29%29%29+%3B+d%2FdC+%2810%5E%28A-B+%2F+%28T%2BC%29%29%29\" target=\"_blank\">derivadas online con WolframAlpha<\/a> de forma muy r\u00e1pida e intuitiva.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><a href=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/derivadas.png\"><img decoding=\"async\" src=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/derivadas.png\" alt=\"\" class=\"wp-image-1370\"\/><\/a><figcaption>Derivadas parciales<\/figcaption><\/figure><\/div>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"true\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">Dim Tx, Px As Double\n'inputs(T)(-)\nDim inputs As Double()() = New Double(14)() {}\nDim output As Double() = New Double(14) {}\nFor i = 0 To 14\n\tTx = DGV_API_NL.Rows(i).Cells(0).Value\n\tPx = DGV_API_NL.Rows(i).Cells(1).Value\n\tinputs(i) = New Double() {Tx, 0}\n\toutput(i) = Px\nNext\n' Create a Nonlinear regression using LevenbergMarquardt algorithm\nDim nls As NonlinearLeastSquares = New NonlinearLeastSquares\nWith nls\n\t.NumberOfParameters = 3 'A, B, C\n\t' Initialize to some random values\n\t.StartValues = {CDbl(Txt_A_ini.Text), CDbl(Txt_B_ini.Text), CDbl(Txt_C_ini.Text)}\n\t'\n\t' y = 10^(A-B \/ (T+C))\n\t.Function = Function(par, inp) 10 ^ (par(0) - par(1) \/ (inp(0) + par(2)))\n\t'\n\t'Derivative in respect to the weights | Derivadas parciales\n\t.Gradient = Sub(par, inp, result)\n\t\t\t\t\tresult(0) = Math.Log(10) * 10 ^ (par(0) - par(1) \/ (inp(0) + par(2)))                                       ' d\/dA\n\t\t\t\t\tresult(1) = -Math.Log(10) * 10 ^ (par(0) - par(1) \/ (par(2) + inp(0)))                                      ' d\/dB\n\t\t\t\t\tresult(2) = (par(1) * Math.Log(10) * 10 ^ (par(0) - par(1) \/ (par(2) + inp(0)))) \/ (par(2) + inp(0)) ^ 2    ' d\/dC\n\t\tEnd Sub\nEnd With\n'\nDim algorithm As LevenbergMarquardt = New LevenbergMarquardt\nWith algorithm\n\t.MaxIterations = CInt(Txt_iter.Text)\n\t.Tolerance = 0\nEnd With\n'\nnls.Algorithm = algorithm\n'\nDim regression As NonlinearRegression = nls.Learn(inputs, output)\n' Use the function to compute the input values\nDim predict As Double() = regression.Transform(inputs)\n' The solution will be at\nA = regression.Coefficients(0) &amp; vbNewLine\nB = regression.Coefficients(1) &amp; vbNewLine\nC = regression.Coefficients(2) &amp; vbNewLine<\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"317\" src=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal-1024x317.png\" alt=\"\" class=\"wp-image-1377\" srcset=\"https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal-1024x317.png 1024w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal-300x93.png 300w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal-768x238.png 768w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal-604x187.png 604w, https:\/\/garikoitz.info\/blog\/wp-content\/uploads\/2021\/06\/Net_est_no_lineal.avif 1457w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Regresi\u00f3n no lineal con NET<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Resultados_y_Conclusiones\"><\/span>Resultados y Conclusiones<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-table aligncenter is-style-regular\"><table><tbody><tr><td class=\"has-text-align-right\" data-align=\"right\"><\/td><td class=\"has-text-align-center\" data-align=\"center\"><strong>Excel lineal<\/strong><\/td><td class=\"has-text-align-center\" data-align=\"center\"><strong>Excel no lineal<\/strong> (GRG)<\/td><td class=\"has-text-align-center\" data-align=\"center\"><strong>.NET lineal<\/strong><\/td><td class=\"has-text-align-center\" data-align=\"center\"><strong>.NET no lineal<\/strong> (LM)<\/td><\/tr><tr><td class=\"has-text-align-right\" data-align=\"right\">A<\/td><td class=\"has-text-align-center\" data-align=\"center\">5,2984<\/td><td class=\"has-text-align-center\" data-align=\"center\">4,8213<\/td><td class=\"has-text-align-center\" data-align=\"center\">5,2921<\/td><td class=\"has-text-align-center\" data-align=\"center\">4,9822<\/td><\/tr><tr><td class=\"has-text-align-right\" data-align=\"right\">B<\/td><td class=\"has-text-align-center\" data-align=\"center\">1838,8791<\/td><td class=\"has-text-align-center\" data-align=\"center\">1348,0797<\/td><td class=\"has-text-align-center\" data-align=\"center\">1832,6783<\/td><td class=\"has-text-align-center\" data-align=\"center\">1497,0177<\/td><\/tr><tr><td class=\"has-text-align-right\" data-align=\"right\">C<\/td><td class=\"has-text-align-center\" data-align=\"center\">433,7311<\/td><td class=\"has-text-align-center\" data-align=\"center\">354,4275<\/td><td class=\"has-text-align-center\" data-align=\"center\">432,9067<\/td><td class=\"has-text-align-center\" data-align=\"center\">378,3875<\/td><\/tr><tr><td class=\"has-text-align-right\" data-align=\"right\">SumaErrores\u00b2<\/td><td class=\"has-text-align-center\" data-align=\"center\">5,98\u00d710<sup>-9<\/sup><\/td><td class=\"has-text-align-center\" data-align=\"center\">6,44\u00d710<sup>-5<\/sup><\/td><td class=\"has-text-align-center\" data-align=\"center\">1,14\u00d710<sup>-7<\/sup><\/td><td class=\"has-text-align-center\" data-align=\"center\">5,39\u00d710<sup>-5<\/sup><\/td><\/tr><tr><td class=\"has-text-align-right\" data-align=\"right\">R\u00b2<\/td><td class=\"has-text-align-center\" data-align=\"center\">0,99999968<\/td><td class=\"has-text-align-center\" data-align=\"center\">&#8211;<\/td><td class=\"has-text-align-center\" data-align=\"center\">0,99999972<\/td><td class=\"has-text-align-center\" data-align=\"center\">&#8211;<\/td><\/tr><\/tbody><\/table><figcaption>Comparativa de resultados<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">La parte de la regresi\u00f3n lineal no tiene mayor complicaci\u00f3n y tanto en Excel como en NET son f\u00e1ciles y r\u00e1pidos de implementar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">La parte de regresi\u00f3n no lineal no es que sea muy compleja ya que tanto Excel como Accord nos lo ponen bastante f\u00e1cil, pero si es cierto que requiere que pongamos algo m\u00e1s de nuestra parte para obtener buenos resultados. Los algoritmos de optimizaci\u00f3n no lineal usados no han sido los mismos, <strong>Solver <\/strong>de Excel utiliza el algoritmo <strong>GRG <\/strong>(Gradiente Reducido Generalizado) y <strong>Accord <\/strong>dispone de los algoritmos Gauss-Newton y <strong>Levenberg-Marquardt<\/strong>, siendo \u00e9ste \u00faltimo el implementado. Estos algoritmos son similares y <strong>ambos nos devuelven un m\u00ednimo local<\/strong>, es decir, no te dan la mejor soluci\u00f3n pero s\u00ed una muy pr\u00f3xima. Estos algoritmos necesitan que les indiquemos unos valores iniciales de los par\u00e1metros o coeficientes que va a alterar para buscar una soluci\u00f3n, y la soluci\u00f3n obtenida depende de esos valores iniciales, por lo que tanto en Excel como en NET se ha partido de los valores 2, 1500, 273 para A, B y C respectivamente.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">En general Excel es una gran herramienta que facilita el trabajo enormemente y de la que podemos sacar chispas en nuestro d\u00eda a d\u00eda, pero me apetec\u00eda darle una vuelta a la posibilidad de resolver problemas de optimizaci\u00f3n con un lenguaje de programaci\u00f3n y palpar cuan f\u00e1cil es obtener \u00abeso\u00bb que con Excel se obtiene tan <em>r\u00e1pido<\/em>. Espero que os sirvan los ejemplos y que los disfrut\u00e9is tanto como yo.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Descargas\"><\/span>Descargas<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/garikoitz.info\/blog\/descargas\/Antoine_blog.xlsx\">Archivo Excel<\/a><\/li><li><a href=\"https:\/\/garikoitz.info\/blog\/descargas\/Antoine_NET.zip\">Proyecto .Net<\/a><\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Referencias\"><\/span>Referencias<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Enlaces\"><\/span>Enlaces<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li><a rel=\"noreferrer noopener\" href=\"https:\/\/es.wikipedia.org\/wiki\/Ecuaci%C3%B3n_de_Antoine\" target=\"_blank\">Ecuaci\u00f3n de Antoine<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"http:\/\/accord-framework.net\/\" target=\"_blank\">Accord.NET Machine Learning Framework<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"http:\/\/accord-framework.net\/docs\/html\/T_Accord_Statistics_Models_Regression_Linear_OrdinaryLeastSquares.htm\" target=\"_blank\">Accord.NET Ordinary Least Squares Class<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"http:\/\/accord-framework.net\/docs\/html\/T_Accord_Statistics_Models_Regression_NonlinearRegression.htm\" target=\"_blank\">Accord.NET Non Linear Regrassion Class<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/cheguide.com\/antoine_eqn.html\" target=\"_blank\">Curve fitting data to an Antoine Equation<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/statisticsbyjim.com\/regression\/r-squared-invalid-nonlinear-regression\/\" target=\"_blank\">R-squared Is Not Valid for Nonlinear Regression<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/es.wikipedia.org\/wiki\/Algoritmo_de_Levenberg-Marquardt\" target=\"_blank\">Algoritmo Levenberg-Marquardt<\/a><\/li><li><a href=\"https:\/\/es.khanacademy.org\/math\/multivariable-calculus\/multivariable-derivatives\/partial-derivative-and-gradient-articles\/a\/the-gradient\" target=\"_blank\" rel=\"noreferrer noopener\">El gradiente<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/findanyanswer.com\/what-is-grg-nonlinear-solver\" target=\"_blank\">GRG nonlinear<\/a><\/li><li><a rel=\"noreferrer noopener\" href=\"https:\/\/www.wolframalpha.com\/\" target=\"_blank\">WolframAlpha<\/a><\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Libros\"><\/span>Libros<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">[1] Mathias Brandewinder. Machine Learning Projects for .NET Developers, Apress. e-ISBN: 978-1-4302-6766-9<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">[2] David G. Luenberger, Yinyu Ye. Linear and Nonlinear Programming, Springer, e-ISBN: 978-0-387-74503-9<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introducci&oacute;n La ecuaci&oacute;n de Antoine es utilizada para obtener el valor de la presi&oacute;n de vapor (P) en funci&oacute;n de la temperatura (T) para componentes puros y se basa en una correlaci&oacute;n de tres par&aacute;metros A, B y C. Dicho esto, el objetivo de esta entrada es obtener los coeficientes de la ecuaci&oacute;n de Antoine para un rango de temperaturas y presiones concretos dada la ecuaci&oacute;n 1. Para ello utilizaremos Excel como herramienta b&aacute;sica que casi todos conocer&eacute;is y tendr&eacute;is&#8230;<\/p>\n<p class=\"read-more\"><a class=\"btn btn-default\" href=\"https:\/\/garikoitz.info\/blog\/2021\/06\/regresion-lineal-y-no-lineal-excel-vs-net\/\"> Leer m\u00e1s<span class=\"screen-reader-text\">  Leer m\u00e1s<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":1322,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","wpupg_custom_link":[],"wpupg_custom_link_behaviour":[],"wpupg_custom_link_nofollow":[],"wpupg_custom_image":[],"wpupg_custom_image_id":[],"footnotes":""},"categories":[37,45],"tags":[40,108,110,115,83,111,112,113,114,109],"class_list":["post-1321","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-net","category-programacion-matematica","tag-net","tag-accord-framework","tag-accord-net","tag-estimacion-lineal-excel","tag-excel","tag-regresion","tag-regresion-lineal","tag-regresion-no-lineal","tag-solver","tag-visual-studio"],"_links":{"self":[{"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/posts\/1321","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/comments?post=1321"}],"version-history":[{"count":58,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/posts\/1321\/revisions"}],"predecessor-version":[{"id":1395,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/posts\/1321\/revisions\/1395"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/media\/1322"}],"wp:attachment":[{"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/media?parent=1321"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/categories?post=1321"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/garikoitz.info\/blog\/wp-json\/wp\/v2\/tags?post=1321"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}