Nueva Hoja Excel para control, seguimiento y análisis estadístico de apuestas

Cita de: hedelsi en 29 de Octubre de 2013,  09:20

Hola Mick, buenos días! Lo tengo bastante controlado ya, algunas pequeñas dudas nada mas.

Ahora tengo una grande con el balance mensual, he introducido apuestas ficticias para meses de diciembre, enero 14, febrero 14... etc, y no me las recoge, aun copiando y modificando las formulas de excel.

Me puedes ayudar? Porque estoy encantado con la tabla, créeme.

Cómo añadir más líneas al balance mensual

Hola hedelsi buenos días,

Sí, es correcto. El balance mensual lo tenía sólo configurado hasta Noviembre 2013 y pensaba ir añadiendo las líneas a medida que las necesitara. Tendrás que crear nuevas líneas para los siguientes meses.

Te recomiendo este procedimiento:

1.- Creas las líneas, insertándolas después de la de Noviembre y antes de la de los totales. De este modo las fórmulas de los totales no se verán afectadas.
2.- Configuras las fórmulas teniendo en cuenta lo siguiente:

- Las celdas "apuestas", "%acierto", "beneficio", "Yield", "bank ant." y "bank" son las más simples pues sus cálculos se realizan entre otra celdas de la misma tabla. Simplemente sitúate en la línea de Septiembre para cada una de esas celdas. Haces "copiar" (el contenido de septiembre) y lo "pegas" arrastrando en todas las líneas siguientes de la columna. De este modo se te trasladará la fórmula y no los valores absolutos.

- Las otras celdas son un pelín más complicadas pero nada grave. Esas celdas buscan los datos de la fórmula en otras hojas de la misma excel. El procedimiento más sencillo es el siguiente:

- Vamos a hacer por ejemplo la celda "aciertos". Sitúate en la celda "aciertos" de Septiembre, copia la fórmula (no el contenido de la celda) en el portapapeles y pégalo en la celda "aciertos" del mes que estás haciendo, por ejemplo Diciembre. La fórmula que has copiado tiene este aspecto:

=CONTAR.SI.CONJUNTO('Bets del mes'!Q12:Q4247;"A";'Bets del mes'!V12:V4247;Configuración!$C$12;'Bets del mes'!W12:W4247;Configuración!$D$4)

Al copiarla en la celda de Diciembre, debería aparecerte la misma cantidad que en Septiembre. Si es así, vamos bien.

Ahora modificaremos la fórmula para que en lugar de que nos traiga los datos de Septiembre nos traiga los de Diciembre. La parte de la fórmula que he subrayado arriba es donde Excel busca los meses y los años ... en la hoja "configuración", las celdas C12 y D4, que corresponden a "Septiembre" y "2013". Simplemente tenemos que modificar $C$12 por $C$15 (para diciembre) y ya estára. El año no cambia.

Cuando tengamos que cambiar también el año (para 2014), modificamos la $D$4 de la fórmula por $D$5, que corresponde a la celda D5 de la hoja configuración, donde tenemos el año 2014.

Hecho ésto, puedes repetir la misma operación para todas las otras celdas calculadas... simplemente modificas la referencia al mes y al año que corresponda y listos. Ojo que algunas celdas tiene la fórmula un poco más larga y puede haber más de una referencia al mes y al año.

Espero que te sirva de ayuda.

Gracias y un saludo,

Cita de: Etr15 en 12 de Diciembre de 2013,  23:21

Hola de nuevo, tengo una duda respecto a esto, quiero añadir ya el mes de Enero, y como me dijiste una vez ya no solo hay que modificar el numero de los meses, sino tambien el año, quiero ir por partes.

Primero en la pestaña "balance Mensual" tengo que añadir la nueva linea, la de enero, ¿La puedo poner justo debajo de la de Diciembre? ¿O tengo que añadir alguna mas? es que tengo la B9, B10,B11 y B12 vacias, lo digo por si en esas, que creo que no hay formulas, las puedo utilizar para ponerlas yo y para saber como se ponen las formulas en esas lineas vacias.

Lo segundo, ¿Que numeros de la formula son los que tengo que cambiar?

Muchas gracias.

Hola Etr15 buenos días,

Efectivamente, puedes utilizar las filas vacías que tienes debajo de la de Diciembre para añadir las del año 2014. Mejor que dejes siempre dos ó tres filas en blanco a continuación. Si quieres hacer una "frivolité" tal vez te interese hacer un subtotal para el año 2013 (?) y luego añadir las líneas de 2014, de manera que el total general sea eso, el general de siempre, y tengas un subtotal para cada año.  En realidad el total anual ya te lo da también la pestaña filtros ó estadísticas. Eso ya verás tú lo que más te apetezca.

Para el mes de Enero tendrás que modificar las referencias de las fórmulas tanto al mes como al año. Simplemente tenemos que modificar $C$15 (si copias la fórmula de Diciembre) por $C$4 (para enero). Para el año modificamos la $D$4 de la fórmula (que corresponde a 2013) por $D$5, que corresponde a la celda D5 de la hoja configuración, donde tenemos el año 2014. Las referencias a los meses y a los años recuerda que los sacamos de la pestaña "configuración". Allí tenemos la lista de meses y de años.

Esto es sólo de aplicación para las celdas con fórmula complicada, del tipo "SUMAR SI" ó "CONTAR SI". Hay otras celdas como las de "apuestas" "% acierto" "beneficio" "yield" que son fórmulas simples en las que copiando la celda del mes anterior y pegándola en la del mes siguiente ya te hará el cálculo correctamente.

Si tienes alguna otra duda por aqui andaré.

Gracias y un saludo,

Cita de: Etr15 en 13 de Diciembre de 2013,  14:10

Nueva duda que no tiene que ver con lo que he preguntado o eso creo, ¿Que significa la columna Bank ant? creo que se lo que es pero no estoy seguro, la cantidad que aparece en esa columna tiene que ser la misma en todos los meses no? y la columna Bank se refiere al bank que tengo yo actualmente ¿no? es que me he dado cuenta que la cantidad que aparece ahi no es la que corresponde con la que tengo, ni tampoco es la misma que la que me aparece en la principal de Bets del Mes (la cual si es la que tengo realmente), pense que era porque como he recibido bonus no los habia metido en el apartado de depositos o bonus que hay en la pestaña de balance mensual, pero lo he metido y aun hay una diferencia de 46 euros respecto al Bank que tengo realmente, ¿a que se debe?

Hola Etr15,

Esa celda recoge el saldo final de bank del mes anterior. Por lo tanto, varía cada mes (en función del saldo de bank con el que finalizaras el mes anterior).

La idea es que, en cada fila de mes,  si sumas el bank anterior + beneficio del mes + depósitos/bonus - retiradas = saldo del bank.

Probablemente no te cuadra porque hayas introducido una cantidad en lugar de dejar la fórmula. Puedes verificar manualmente si el bank ant. de cada fila = bank final de la fila anterior.

Gracias y un saludo,

Cita de: Etr15 en 13 de Noviembre de 2013,  20:22

Una duda que me ha surgido, me he metido en la pestaña configuracion y he visto que en mercados ya hay puesto algunos, como resultado final etc, yo en mi hoja de excel cuando estoy rellenando los datos, en uno de ellos he puesto un mercado que no esta en esa pestaña de configuracion ¿Pasa algo con eso? es que he visto que en esa pestaña pone que si se precisan mas datos hay que modificar en consecuencia del criterio de validacion.

No se si eso sera importante o no, muchas gracias.

Hola Etr15 buenos días,

En pura teoría Excel no tendría que haberte dejado introducir en la casilla "Mercado" un dato distinto a los que figuran en la lista del desplegable, sino que debería haberte mostrado un mensaje de error. Si lo has podido introducir es porque de alguna forma se te ha desconfigurado la validación de entrada de datos.

¿Por qué restringimos en la casilla "mercados" la entrada de datos a los de una lista? Lo hacemos también en la casilla "Deportes" y es básicamente por dos motivos:

- Facilitar al usuario una guía de qué es lo que debe introducir ahí.
- Permitir la confección de estadísticas homogéneas sin datos introducidos con errores mecanográficos o con incosistencias.

Por ejemplo, en el caso actual de "Mercados", restringimos a la lista para que el usuario no pueda introducir múltiples variantes del mismo término (Asiático, Hándicap Asiático, HA, Handicap Asiatico sin acentos, etc.) que luego nos aparecerían como mercados "distintos" en las estadísticas, haciéndolas inútiles.

La ventaja de este sistema es que es totalmente configurable por el usuario. Es decir, es el propio usuario quien determina qué datos permite introducir en la casilla. Para eso tenemos la lista en la pestaña "configuración" y es desde ahí desde donde administramos la lista. En la versión de descarga ya vienen algunos mercados creados pero obviamente cada uno los puede modificar a su gusto simplemente modificando la lista de la pestaña "configuración".

Cómo modificar los elementos de una lista desplegable

1.- Vamos a la pestaña "configuración" y buscamos la lista en cuestión. En nuestro caso es la lista "mercados" y está en la columna E.
2.- Hasta donde están los ********* podemos añadir más elementos sin necesidad de modificar el criterio de validación. Simplemente los añadimos en los espacios en blanco. Si queremos ordenarlos alfabéticamente, seleccionamos todas las entradas de la lista y con botón dercho del ratón  -----> Ordenar -----> A/Z. Importante: No ordenar alfabéticamente la lista de las casas de apuestas porque afectaría al cálculo del saldo de cada una de ellas al estar referenciado dicho cálculo a la posición absoluta que actualmente ocupan en la lista.
3.- Una vez la lista de elementos está a nuestro gusto, si no nos hemos pasado del punto marcado con los ****** , podemos ir a la hoja principal de apuestas y comprobaremos que efectivamente al pulsar el desplegable de cualquier casilla "Mercados" nos aparecerán los mercados que hemos añadido.

El motivo de marcar la frontera con ***** y no dejar mucho más margen de casillas en blanco es evitar que el menú desplegable nos aparezca con un montón de espacio en blanco al final.

4.- Si necesitamos añadir más elementos de los que permite la "frontera" de los asteriscos lo hacemos tal como se explica en los puntos anteriores, pero luego hay que realizar un siguiente paso.
5.- Excel sabe que tiene que mostrarnos en el desplegable todos los elementos de la lista hasta los asteriscos. Si añadimos elementos más allá tendremos que decirle a Excel que el límite ha cambiado y que nos tiene que mostrar más elementos. ¿Cómo lo hacemos? Cambiando el criterio de validación de datos en la columna de "Mercados" de la hoja principal de apuestas.

Para ello nos vamos a la hoja principal de apuesta, nos situamos en la columna "Mercados" y seleccionamos de la primera celda de apuestas hasta la última que tengamos formateada en la rejilla.

En el menú de Excel "Datos" buscamos la opción "Validación de Datos" y pinchamos. Se nos abrirá este configurador:

Aquí vemos que el criterio de validación para estas celdas es una lista cuyo origen está en la pestaña "configuración" y que va de la celda E4 hasta la E20 (con los signos $ porque son referencias absolutas). Si pulsamos en el botón con la flechita roja situado a la derecha del origen, Excel nos llevará a la pestaña configuración y nos mostrará de dónde está tomando los datos, para que modifiquemos lo que nos interesa:

Simplemente modificamos la información relativa a la última celda, para que en lugar de la E20 sea la E25 o la que nos intereses. Siempre manteniendo los símbolos $. Es decir, cambiamos el valor 20 por el valor 25 (o el que sea).

Guardamos los cambios y ya tenemos modificado el criterio de validación para las celdas de la columna "Mercados" de la hoja principal de apuestas. A partir de ahora, al pulsar en el desplegable, Excel nos propondrá todos los elementos de la lista de la pestaña configuración, de la celda E4 hasta la E25 (o la que hayamos elegido como última a mostrar).

Espero que te sirva de ayuda.

Gracias y un saludo,