jueves, 22 de noviembre de 2012

Árbol de Navidad

Descargue el archivo: christmastree.xlsm

Al descargar este fichero verá la forma que tenemos desde la página EXCELAVANZADO.COM de desear feliz Navidad a todos nuestros amigos. El código está sin proteger por lo que puede modificarlo y adaptarlo a su felicitación de Navidad personalizada.



Al tratarse de un fichero con macros para que funcione debe tener un nivel de seguridad que permita su ejecución. Si le preguntan si desea habilitar las macros diga que si.



domingo, 14 de octubre de 2012

Sumar horas en Excel

Descargar el fichero: sumar_horas.xlsx

Para sumar horas y minutos en #Excel cuando la suma supera las 24 horas no debemos emplear el formato clásico de hh:mm sino este otro [h]:mm


En el ejemplo que estamos manejando en el fichero sumar_horas.xlsx deseamos calcular las horas semanales trabajadas por un empleado en jornadas de mañana y tarde.

Para introducir las horas de inicio y final de jornada lo haremos en Excel escribiendo por ejemplo 8:00 para indicar las ocho de la mañana, y 17:15 para indicar las cinco y cuarto de la tarde.

La fórmula de la celda H4 es la siguiente:

=(E4-D4)+(G4-F4)

Los paréntesis no son necesarios. Los hemos puesto para separar la jornada de mañana y la jornada de tarde.


Las celdas H9, H10 y H11 contienen todas ellas la misma fórmula que es la suma de las horas trabajadas durante la semana. La fórmula es la siguiente:

=SUMA(H4:H8)

La diferencia entre las tres fórmulas está en el formato que hemos empleado. El formato correcto es el de la celda amarilla H9. Esto es así, ya que cuando la suma de horas supera las 24 horas, se añade un día y si usamos un formato donde no se ve ese día, únicamente vemos la fracción de horas.

En este caso 35 horas y media es lo mismo que 1 día y 13 horas y media.

sábado, 8 de septiembre de 2012

Distribución Uniforme

Puede descargar el archivo: uniforme.xlsm

En estadística disponemos de la distribución Uniforme. Es una distribución de carácter continuo que asigna la misma probabilidad en cualquier parte del intervalo en el que está definida.

ALEATORIO

En Excel se puede conseguir con la función:
=ALEATORIO()
que nos proporciona un número aleatorio entre 0 y 1, sin llegar al valor 1. Esto se expresa diciendo que la función genera números aleatorios en el intervalo [0;1).

En realidad, lo que se genera es un número pseudoaleatorio ya que se obtiene con ciertos algoritmos internos, y no sacando por ejemplo, bolas de un bombo de la lotería, o tirando monedas al aire a cara o cruz, lo cual generaría valores realmente aleatorios.

Podemos escribir la función ALEATORIO() en Excel y al pulsar luego la tecla de función F9, de recálculo manual, observaremos que el valor aleatorio que se obtiene cambia. Es un número entre 0 y 1 con muchos decimales. En la siguiente imagen se muestran diez números aleatorios obtenidos con esta función.


Los valores aleatorios también cambian siempre que se recalculen los valores de la hoja, cosa que sucede cada vez que se introduce un nuevo valor o se edita uno existente.

Ejemplo

Escriba en una celda un número aleatorio con la función ALEATORIO() y en otra celda escriba la palabra Hola. Al pulsar Enter observe como cambia el valor aleatorio ya que se recalcula toda la hoja.


Aleatorios en un intervalo


Vamos a ver las transformación que podemos realizar partiendo de un aleatorio creado con la función ALEATORIO(), al multiplicar por un número y al sumar cierta cantidad. También veremos cómo podemos quitar la parte fraccionaria y quedarnos únicamente con la parte entera.

Factor de escala


Si a la función ALEATORIO() la multiplicamos por un número la estamos afectando de un factor de escala. Por ejemplo, al multiplicar por 200, el número aleatorio que se obtiene ya no se mueve en el intervalo [0;1) sino en el intervalo [0;200).
=ALEATORIO()*200

Esto supone amplificar 200 veces el resultado obtenido con el número aleatorio.



Traslación


Si al número aleatorio anterior se le suma una cierta cifra lo que estamos haciendo es un cambio de escala. Por ejemplo, después de haber multiplicado por 200, lo que haremos es sumar 5.000 con lo que el número obtenido ya no se moverá en el intervalo [0;200) sino en el intervalo [5000;5200).
=5000+ALEATORIO()*200

Será un número con decimales como el del siguiente ejemplo.



Parte entera

Si deseamos quitar los decimales, podemos utilizar la función =ENTERO. Es una función que no redondea, lo que hace es truncar la parte fraccionaria y quedarse únicamente con la parte entera.

=ENTERO(5000+ALEATORIO()*200)




En este ejemplo, lo que conseguimos es un número entero en el intervalo [5000;5199]. No se llega a alcanzar nunca el valor 5200, ya que nunca se llegaba a alcanzar el valor 1 con la función ALEATORIO, y la función ENTERO lo que hace es quitar la parte decimal.

Si queremos llegar hasta 5.200 hemos de sumar 1.

=ENTERO(5000+ALEATORIO()*200)+1


En este caso el aleatorio obtenido será un número entero que se mueve en el intervalo [5001;5200]


ALEATORIO.ENTRE


Existe una función en Excel que pertenece a las complementarias y que se denomina

=ALEATORIO.ENTRE(mínimo;máximo)


Esta función genera números aleatorios enteros entre un valor mínimo y un valor máximo que proporcionemos.

Para conseguir el mismo intervalo que el que hemos creado en el apartado anterior escribiríamos la siguiente expresión.

=ALEATORIO.ENTRE(5001;5200)
>

En VBA


En programación tambíen podemos crear números aleatorios. En realidad son número pseudoaleatorios ya que se generan con algoritmos que imitan a los verdaderos números aleatorios.


La función que genera los números aleatorios es

Rnd


Genera un número aleatorio entre 0 y 1, excluido el 1. Es similar a la función ALEATORIO.

Barajar

Los números aleatorios que genera VBA en realidad no son aleatorios y se pueden repetir según una cierta secuencia. Para evitar que se repitan lo que hacemos es utilizar el comando

RANDOMIZE


que equivale a barajar las cartas de una baraja. Es lo que se denomina cambiar la semilla de generación de los números aleatorios.

RANDOMIZE lo utilizaremos en nuestro programa, normalmente una sola vez, al principio. Por ejemplo, después de los DIM. En caso de usar bucles se debe procurar no introducirlo dentro, ya que sino estaría barajando continuamente a cada iteración del bucle.


Método 1

El siguiente programa genera 10 números aleatorios y los escribe en la columna A.


Aleatorios en un intervalo

Si deseamos generar aleatorios en un cierto intervalo entre un valor mínimo (min) y un cierto valor máximo (max), sin decimales, utilizaremos la siguiente expresión:

Int((max - min + 1) * Rnd + min)
Por ejemplo, podemos utilizar la 'ventana Inmediato' para generar números aleatorios enteros entre 80 y 100.

Método 2

Para generar números aleatorios entre 5.000 y 5.200 utilizaremos la siguiente expresión.
Int((5200 - 5000 + 1) * Rnd + 5000)
En el siguiente programa generamos 10.000 números aleatorios entre 5.000 y 5.200.


Método 3

Otro sistema que me gusta emplear para conseguir lo anterior es el siguiente.


Se multiplica por 201 ya que realmente entre 5.000 y 5.200, incluidos ambos hay 201 números.


Consulte el siguiente post para ver cómo se trabaja con matrices y cómo se ahorra tiempo en los cálculos.


Listado de valores con Macro

Cuando se trabajan con muchos valores es aconsejable utilizar matrices.

Método 4

También podemos llamar a una función de Excel desde el código de VBA. Las funciones a las que llamemos  han de estar en inglés, y el separador de argumentos es la coma, ya que todo va en inglés.

Para ver un listado de funciones en inglés y en español consulte el siguiente post:

Glosario: Traducción de funciones


La función ALEATORIO.ENTRE en inglés es RandBetween.

También se pude utilizar así:



Método 5

Si lo que deseamos es dejar la fórmula en las celdas de la hoja en lugar de depositar el valor podemos utilizar la siguiente macro.



Método 6

Si lo que deseamos es utilizar nuestro idioma local depositando la fórmula en las celdas la macro será la siguiente.


Así podremos utilizar las fórmulas en español.

viernes, 10 de agosto de 2012

Pilas

Descargar el fichero pilas.xlsx

Este es un ejemplo de nivel inicial para introducirse en el mundo de Excel. Aprendera los conceptos básicos de Excel:
  • Introducción y edición de textos
  • Formato de celdas
  • Introducción de fórmulas
  • Fórmulas relativas y absolutas (uso de los dólares $)
  • Creación de gráficos
Existen diferencias apreciables entre la versión de Excel 2003 o anteriores y la nueva versión 2007. Las diferencias básicamente se refieren al entorno de usuario, esto es, a los menús que puede ver el usuario. Es conveniente irnos acostumbrando a la vesión nueva, aunque en la mayoría de las empresas aún se trabaja con versiones anteriores.


Iremos estableciendo los pasos necesarios para llegar a elaborar nuestro fichero.

  1. Inicie Excel. Puede hacerlo mediante: Inicio, Programas, Microsoft Office, Excel. Puesto que lo utilizaremos con asiduidad es aconsejable crearnos un acceso directo en nuestro escritorio.
  2. Excel se abre con un libro nuevo, que inicalmente se llama Libro1.xls, o en la versión 2007 se denomina Libro1.xlsx. Observar que la extensión en las versiones 2003 y anteriores es xls y en la vesión 2007 y posteriores la extensión es xlsx. Excel 2007 abre fiecheros de versiones anteriores, y al grabar se puede pedir que lo grabe en modo compatibilidad con vesiones anteriores, esto es, con extensión xls. Por tanto, en general, grabaremos los ficheros con la extensión xls para que sean compatibles con cualquier versión de Excel que utilicemos. Grabe el fichero con el nombre pilas.xlsx.
  3. Estrechamos la columna A, para dejarla como margen y no utilizarla. Esta es una peculiaridad que utilizaremos en nuestros ficheros y tiene una finalidad estética. Para estrechar una columna ponemos el cursos entre el indicador de columna A y B y en ese momento arrastramos hacia la izquierda. Esto hace que la columan A se estreche.
  4. Crear la tabla que se muestra en la siguiente imagen. Escriba la columna 'TIPO PILA' y la columna 'FACTURACIÓN'.



  5. La celda C9 totaliza las ventas. Esto se consigue incluyendo la función =SUMA(C5:C8). Toda función en Excel comienza con el signo igual (=). Tambíen puede comenzarle con el signo más (+). La función suma se puede escribir manualmente, o bien utilizando el icono del sumatorio (Autosuma). Para ello nos situamos en la celda C9 y pulsamos el icono ∑.
  6. Podemos dar formato a las celdas. En este caso seleccionamos los importes de la facturación y pulsamos el botón derecho del ratón, y elegimos 'Formato de celdas'. Para poner los importes con separador de miles y cero decimales elegimos las opciones que se muestran en la siguiente imagen.


  7. Vamos a calcular el porcentaje que supone la facturación de cada tipo de pila. La fórmula que debemos utilizar para la celda D5 es: =C5/C9. Esto nos proporcionará el valor 0,4 que expresado en tanto por uno es un 40%. Para poder ver este valor en porcentaje le damos formato de porcentaje. Estando en D5, esto se puede conseguir pulsando el icono del porcentaje (%).
  8. Si la fórmula anterior la copiamos hacia abajo obtendremos un hermoso error. Si copiamos la fórmula de la celda D5 una hacia abajo hasta D6, la fórmula tambien baja en una celda todas sus referencias. Esto hace que la fórmula obtenida sea =C6/C10. Esto nos da el error #!DIV/0¡, que indica que se ha producido una división por cero, ya que la celda del denominador C10 esta vacía. Al copiar una celda hacia abajo nos interesa que el numerador cambie pero queremos que el demoninador constinúe siendo la facturación total de la empresa (celda C9). Para fijar la celda C9, debemos editar la fórmula (hacindo clic sobre ella) y situado el cursor sobre C9 pulsar la tecla de función F4. Esta tecla, permite poner dólares a esta referencia de celda en una fórmula. Si ponemos =+C5/$C$9 conseguimos que la celda C9 quede fija al copiarse la fórmula. Por tanto, si en una celda una referencia a celda va sin dólares Excel efectúa una copia relativa, y si va con dólares

martes, 26 de junio de 2012

Comparar Tablas dinámicas entre Excel 2010 y 2003

Descargar el fichero: TablaDinamica2010.xlsx

Podemos comparar las Tablas Dinámicas en la versión 2010 con la versión 2003, adaptando la nueva versión al estilo clásico.

 

domingo, 29 de abril de 2012

Elementos distintos y repetidos en una lista

Descargar el fichero: aeropuertos.xlsx

Deseamos determinar cuantos elementos se encuentran repetidos en un listado, y deseamos saber quienes son los repetidos.

Hoja 1

Disponemos de una serie de ciudades y deseamos conocer cuantos elementos hay sin repetición. Para ello utilizamos la fórmula matricial que se encuentra en la celda D14 y cuya explicación se encuentra paso a paso en las columna C, y D.

La fórmula matricial de la celda D14 es la siguiente:

=SUMA(1/CONTAR.SI(B4:B11;B4:B11))



Hoja 2

En este caso vamos a entender que un elemento está o no repetido según dos diferentes criterios. El estudio se puede entender de dos formas. La primera considera el conjunto total de datos y establece quienes están repetidos una o más veces. El segundo criterio analiza los datos por orden y únicamente considera que el dato está repetido si ya apareció anteriormente.

Partimos de un listado de AENA con los aeropuertos españoles y sus códigos de tres caracteres. Por ejemplo, el código de Barcelona es BCN.

En la columna K establecemos una serie de números aleatorios entre 0 y 1, que nos servirán para luego elegir los aeropuertos que deseamos analizar en otra tabla.

La fórmula de la celda K4 es:

=1/42+K3



En la columna C generamos de forma aleatoria los códigos de los aeropuertos. Generamos 24 códigos a elegir entre los 42 disponibles. Estos códigos al ser aleatorios podrán salir repetidos. Esto se consigue con la fórmula siguiente:

=BUSCARV(ALEATORIO();valores;2)

Donde 'valores' es el rango K3:L44

Método 1

En la columna D determinamos si el valor de la columna C que estamos analizando se encuentra repetido o no respecto a todos los valores de la propia columna C. Esto se hace en la celda D12 con la siguiente expresión.

=SI(CONTAR.SI(C:C;C12)>1;"repe";"")

Método 2

En la columna E vamos a determinar si un valor se encuentra repetido únicamente respecto a los que han aparecido antes que él, dentro de la propia columna C.

Este es el motivo de que en E12 siempre ponga 'nuevo' por definición, ya que es el primero.

La fórmula de la celda E13 es la siguiente:

=SI(C13="";"";SI(ESERROR(BUSCARV(C13;C$12:C12;1;FALSO));"nuevo";"repetido"))

Es importante observar que el rango que se está analizando es el rango C$12:C12 que se corresponde con los valores previos, y se ha de marcar con un dolar la primera referencia para dejarla fija y sin dólares la segunda referencia para dejarla libre.


Cata de datos en Excel

Descargar el fichero: cata.xlsm

Una cata de vinos nos permite probar y elegir el vino que sea de nuestro agrado. Lo mismo sucede con una cata de quesos, o bien si realizamos una cata de un melón. En este caso nos hemos permitido utilizar el término 'cata' para aplicarla a unos datos que tenemos y elegir de entre ellos un número dado, que deseamos seleccionar de forma aleatoria.

Disponemos de una serie de 20 datos, en este caso códigos de identificación de productos, y de entre ellos deseamos elegir 5 de forma aleatoria, al azar.

Hoja 1

En la columna B generamos números aleatorios con la función =ALEATORIO().
En la columna C generamos códigos inventados con la función:

=ELEGIR(ALEATORIO.ENTRE(1;23);"A";"B";"C";"D";"E";"F";"G";"H";"J";"K";"L";"M";"N";"P";"Q";"R";"S";"T";"V";"W";"X";"Y";"Z")&ALEATORIO.ENTRE(1000;9999)

Dotamos los elementos de la columna C con un Formato Condicional que tiene la siguiente expresión para la celda C4:

=B4<=K.ESIMO.MENOR($B$4:$B$23;5)

La columna B se ha utilizado como columna auxiliar para dar carácter aleatorio a las selecciones que luego hacemos. Se utiliza la función K.ESIMO.MENOR para elegir los 5 que tienen un número aleatorio menor. También se podría haber utilizado la función K.ESIMO.MAYOR y funcionaría exactamente igual.




Hoja 2


En la Hoja2 tenemos una variante de la anterior. La columna C contiene códigos fijos que no varían y hemos preferido elegir los códigos y mostrarlos en la columna F. La columna E es necesaria para poner los números hasta el máximo que deseamos extraer, que en este caso son 5.

La fórmula de F4 es:

=INDICE($B$4:$C$23;COINCIDIR(K.ESIMO.MENOR($B$4:$B$23;E4);$B$4:$B$23;0);2)

El color que se da a los valores elegidos de la columna C en esta ocasión se realiza con Formato Condicional pero con otra expresión que se basa en determinar si ya existen en la columna F.

La fórmula del Formato Condicional de la celda C4 es el siguiente:

=CONTAR.SI($F$4:$F$8;C4)>0



viernes, 20 de abril de 2012

Incorpora datos

Descargar el fichero: incorpora.xlsm

Incorpore datos en Excel a una columna, indicando la fecha y hora de incorporación del dato. Esto se puede conseguir con una pequeña macro. La idea es disponer de un botón que esté asociado a una macro que cuando lo pulsemos incorpore una celda calculada y la fecha y hora del sistema a una tabla, de forma que se vaya guardando un historial.

Supongamos que en un proceso industrial usted debe incorporar el promedio de 5 pesadas (celda verde) y la fecha y hora actuales (celda amarilla) a una tabla (columnas E y F) donde desea guardar un historial. Cada vez que pulse el botón denonimado "Incorpora Dato" ambas celdas pasaran a formar parte del historial.



La macro que consigue esto es la siguiente.


sábado, 31 de marzo de 2012

Incorporar nuevas cuentas contables

Descargar el fichero: TomaCuentas.xlsm

Normalmente importamos de nuestra base de datos las cuentas contables con sus saldos, y luego manipulamos esa información hasta llegar a los informes que deseamos. Esa manipulación se realiza en Excel, e incluso la presentación del informe final, ya que Excel es una herramienta muy flexible e intuitiva. La base de datos varía con la empresa en la que nos encontremos, puede ser Oracle, u otra base de datos profesional, o bien un sistema integrado de gestión tipo SAP.

Supongamos que importamos de nuestra base de datos y obtenemos una relación de cuentas contables del grupo 6, a 8 dígitos, y con ellas también importamos su saldo actual. Estas cuentas pueden venir sin ordenar de menor a mayor, o bien pudieran venir ordenadas.


En otra tabla tenemos esas mismas cuentas con los saldos. Un sistema para llevarnos las cuentas a esta nueva tabla de Saldos sería copiar y pegar la tabla anterior, pero no lo vamos a hacer así, ya que en base a esta tabla de Saldos tenemos nuestros informes que toman las cuentas contables vinculando con la tabla de Saldos, por lo que si copiamos y pegamos destruimos los vínculos.


Posteriormente importaremos nuevamente nuestras cuentas de SAP o del sistema de que se trate y pueden venir nuevas cuentas que se hayan creado. Lo que necesitamos es que las nuevas cuentas se incorporen a la tabla de Saldos, pero recordemos que no podemos simplemente copiar y pegar ya que los vínculos de los otros informes se destruirían.

Para resolver este caso hemos creado una macro que copia en la tabla de Saldos únicamente las cuentas nuevas. También hemos creado otra macro que copia tanto el color de fondo como el color de la tinta y lo replica en la tabla de Saldos.

Pero veamos todo esto por partes, y con detalle.

BuscarV y otros métodos

La tabla de Saldos dispone de tres columnas denominadas Saldo1, Saldo2 y Saldo3. Son tres formas de obtener el saldo de cada cuenta consultando la primera tabla, la que hemos denominado 'Base de datos importada'.

Veamos Saldo1. La celda F7 es:

=BUSCARV(E7;$B$7:$C$206;2;0)

Veamos Saldo2. La celda G7 es:

=DESREF($A$7;COINCIDIR(E7;$B$7:$B$206;0)-1;2)

Veamos Saldo 3. La celda H7 es:

=INDICE($B$7:$C$206;COINCIDIR(E7;$B$7:$B$206;0);2)

El sistema clásico de búsqueda en una tabla es BuscarV, pero en algunos casos no es el sistema más conveniente, por lo que hemos dado dos alternativas. Para ello, utilizamos las funciones DESREF, o INDICE que son muy similares, y dentro de ellas para determinar la fila utilizamos COINCIDIR.

El rango en el que buscamos es $B$7:$B$206. Si la base de datos fuera mayor tendríamos que extender el rango por exceso hasta la fila que fuera necesaria para cubrir toda la base de datos. Otro método pudiera ser establecer un rango dinámico tal y como se hace en este post: Rango dinámico.


Añadimos nuevas cuentas

Supongamos que una nueva importación de SAP, o el sistema de que se trate, nos proporciona nuevas cuentas contables con su saldo asociado.



Las nuevas cuentas las hemos coloreado dando diferentes colores de fondo y de tinta.

Estamos suponiendo que el proceso de importación de cuentas deja las cuentas nuevas al final de la tabla.


Macro que incorpora cuentas nuevas

Antes de lanzar la macro la situación previa es la siguiente.


Después de lanzar la macro habremos conseguido incorporar las cuentas nuevas.


La macro ha traído también el saldo de las cuentas, pero este es un valor numérico y no una fórmula. Lo mejor será copiar las fórmulas de los métodos 1, 2 y 3 hacia abajo. Por tanto, seleccionamos el rango F26:H26 y lo copiamos hacia abajo, obteniendo la siguiente imagen.



La macro que nos ha permitido hacer la incorporación de las cuentas nuevas es la siguiente.


El 6 que aparece en las expresiones filas2+i+6 obedece a que antes de los datos numéricos de las tablas hay 6 filas, que son las 6 primeras filas de la hoja.

La macro Incorpora1 requiere que las nuevas cuentas se añadan al final de la columna B. Si las nuevas cuentas aparecen intercaladas en la columna B, por ejemplo, por venir ordenadas, la macro Incorpora1 no es válida, y tendremos que utilizar la macro Incorpora2, que es válido vengan en el orden que vengan las nuevas cuentas.



Macro que copia color

Ahora vamos a copiar el color de fondo y el color de la tinta de todas las celdas de la columna B y los vamos a trasladar a la columna E.


La macro utilizada es la siguiente.



Incorporación aunque estén intercaladas

Si las nuevas cuentas que llegan a la columna B no se agrupan al final sino que aparecen intercaladas tendríamos el caso que se recoge en la Hoja2.


En este caso aplicamos la macro Incorpora2 que es válida para cualquier caso, bien vengas las nuevas cuentas intercaladas, o bien vengan todas ellas al final de la columna B.


Finalmente, nos faltaría copiar las fórmulas de BuscarV y similares y dar color con la macro que copia color. El resultado sería el siguiente.


Ahora, si lo deseamos, podemos ordenar la tabla de Saldos. En todo caso, hemos conseguido nuestro objetivo, esto es, al incorporar nuevas cuentas no se estropean los vínculos que teníamos de nuestros informes previos.