Manejo de datos en Excel: Ejemplo BetExplorer (2)

En la anterior entrada ya vimos como podíamos aprovechar los datos de una web como betexplorer y organizarlos para que puedan ser tratados más fácilmente. Al final obtuvimos una tabla de partidos, equipos y resultados similar a esta:


A la izquierda de la tabla pegabamos los datos de Betexplorer y a la derecha los teníamos ordenados. Pero para completar nuestra base de datos nos falta una parte muy importante: las variables de entrada. Estas variables son las que representan al conjunto de datos disponibles ANTES de que el partido se haya jugado, es decir, necesitamos algo así:


Esto lo podíamos haber hecho tomando los datos jornada tras jornada antes de los partidos. Pero también lo podemos hacer partiendo de los datos de betexplorer. No es demasiado complicado, pero debemos ser cuidadosos con las fórmulas. Vamos con ello. Empezaremos por lo más sencillo que es calcular la cantidad de partidos que se han jugado y para ello usaremos la función =contrar.si().


Dos cosas importantes que tengo que destacar, la primera es que en la fórmula tenemos un rango en el que la celda inicial es una referencia absoluta (los valores están entre $) y la final es una referencia relativa. Esto es así para que cuando 'arrastremos' esta fórmula a toda la tabla, el rango de la fórmula SIEMPRE empiece en la primera fila. La segunda es que las fechas o las jornadas deben ir de menor a mayor, es decir, las primeras filas de la tabla serán las primeras jornadas y la tabla se irá rellenando hacia abajo con nuevos partidos y nuevas jornadas.

Una vez dicho esto lo siguiente que debemos hacer es calcular la cantidad de goles anotados y encajados. En este caso vamos a usar la función =sumar.si(), que tiene tres parámetros, el primero es el rango inicial donde se buscan los datos, el segundo es el criterio de búsqueda, y el tercero es el rango que queremos sumar.


Por último vamos a calcular los partidos ganados, perdidos y empatados. Para este cálculo necesitamos hacer un paso intermedio, y crear tres columnas una para cada resultado que llenaremos de unos y ceros en función del resultado del partido. Esto lo haremos con la función =si(condicion; valor si verdadero; valor si falso) de la siguiente forma.


Una vez tenemos estas columnas creadas, utilizaremos otra vez la función =sumar.si() para calcular los tres datos que nos faltan, de la misma manera que hemos hecho con los goles a favor y en contra.

Con esto habremos terminado la tabla para el equipo de casa, para el equipo de fuera se opera de forma similar teniendo en cuenta que los goles a favor son los que mete el equipo de fuera y que los partidos ganados son los que aparecen en la columna con un '2' de encabezado.

Estas funciones de excel son muy potentes y nos pueden ser de gran ayuda, pero tienen una limitación muy importante, y es que son MUY EXTRICTAS. Para estas funciones no es lo mismo Real Madrid que R. Madrid, o incluso peor todavía, diferencian entre cosas como Almería y Almeria (sin acento), incluso un espacio de más entre dos palabras o al principio/final del nombre del equipo hace que para Excel esos datos sean diferentes también. Para evitar, en lo posible, estos problemas aconsejo dos cosas, la primera es tomar los datos SIEMPRE DE LA MISMA PAGINA y segundo usar la función =blancos(), que nos elimina estos fastidiosos espacios innecesarios.

No me quiero extender más en este post, así que si alguien tiene alguna pregunta o necesita alguna explicación más no tiene más que añadir un comentario al post.

3 Comentarios:

francisco dijo...

Off topic.

Hola. Te felicito por tu blog. Acabo de descubrirlo. Soy novato en apuestas y descubrí hace un tiempo por casualidad el de Anja Ander en el cual colaboraste, que me gusta mucho ya que va de matemáticas y que estoy "destripando". Lo dos blogs son muy interesantes por cuanto tratan el tema de las apuestas desde una perspectiva científica, aunque creo que el tuyo me va a gustar todavía más porque me encanta la estadística.

Aunque no se si debo, me voy a atrever a preguntarte una duda de carácter matemático que encontré en una de las entradas del blog de Anja y no logro descifrar. Tal vez tú lo sepas, o tal vez fue un error en cuyo caso no merece la pena perder más el tiempo en ello, aunque supongo que algo se me escapa. La entrada es la siguiente: http://anjaander.blogspot.com/2007/09/bono-de-bienvenida-de-paddy-power.html , y la duda es la gráfica del final del post en la que se representa el beneficio esperado en función de la cuota justa, que representa como una hipérbola, así como una afirmación que hace en la que dice que el valor esperado mínimo de una bonificación de 50€ es 20,75€. No se cómo saca ni una cosa ni la otra. Pensaba que tendría que ver con la esperanza matemática, pero no encuentro la relación. ¿Has tratado en alguna entrada de tu blog algo en relación a este asunto? Yo he buscado, pero de momento no he encontrado nada.

Saludos, y gracias por el blog, que está muy bien.

Buzjss dijo...

Hola Francisco, he estado mirando el post que dices de Anja, y la verdad es que no se de donde saca esa gráfica. Así que lo mejor será que se lo preguntes a él.

Un saludo

francisco dijo...

Gracias Buzjss, por echarle un vistazo a eso. Ya lo pregunté en el blog, pero parece estar abandonado. Es igual, no tiene mayor importancia.

Voy a disfrutar ahora también mucho con el tuyo repasando mis oxidados conocimientos estadísticos, lo que siempre es agradable :)

Saludos.