Todo año nuevo es una oportunidad para avanzar y cumplir metas. Y si tu meta es avanzar y mejorar en Excel, esta es tu oportunidad: los cursos de Excel Avanzado y Macros de Excelforo.
Estos cursos están diseñados para ayudarte a convertirte en un profesional de Excel, contribuyendo a tu desarrollo profesional y laboral.
Mediante el uso de plataformas educativas punteras, los cursos de Excel y cursos de programación en Visual Basic para Excel (VBA-Macros) se desarrollan en la modalidad e-learning (online).Con las posibilidades que brinda una plataforma educativa de e-learning, se establece una relación entre alumnos y profesor, generándose un ambiente de enseñanza-aprendizaje, que contribuye a combatir algunos de los inconvenientes de la enseñanza a distancia tradicional.
La inscripción a estos cursos se cierra el próximo 10 de Enero!
Visita esta página para más detalles e inscripción.
martes, enero 07, 2014
lunes, enero 06, 2014
Extraer una muestra aleatoria de datos de una lista en Excel
En una nota anterior mostré una técnica para extraer muestras aleatorias de listas en Excel. Esta técnica incluía el uso de las funciones ALEATORIO.ENTRE, INDICE, CONTARA y una columna auxiliar con la función CONTAR.SI.
Deambulando por la Internet me topé con esta técnica sugerida por Bob Umlas que usa Filtro Avanzado y la función ALEATORIO.
Como ya hemos visto Filtro Avanzado acepta el uso de fórmulas en el área de criterios, lo que le da una gran flexibilidad.
La idea en la técnica de Umlas es usar la fórmula "=ALEATORIO()<x" como criterio, donde "x" es un número mayor que 0 y menor que 1.
Por ejemplo, si queremos extraer una muestra que represente el 10% de la lista usamos "=ALEATORIO()<0.1"
En este ejemplo tenemos una lista de 100 nombres y queremos extraer una muestra aleatoria de 10
Esta técnica tiene un inconveniente: no siempre el resultado será el número esperado. En el ejemplo arriba se puede apreciar que hay 12 nombres en la muestra. Si aplicamos varias veces el filtro veremos que el tamaño de la muestra puede ser mayor o menor que 10.
Para solucionar este problema podemos repetir la acción hasta que la muestra extraída tenga el tamaño deseado. Para automatizar el proceso usamos una macro simple. Grabamos con el grabador de macros las acciones y la macro resultante la asociamos a un botón
Para ahorrarnos el trabajo de contar cuantos nombres hay en la muestra podemos agregar una celda de control con la función CONTARA()
Deambulando por la Internet me topé con esta técnica sugerida por Bob Umlas que usa Filtro Avanzado y la función ALEATORIO.
Como ya hemos visto Filtro Avanzado acepta el uso de fórmulas en el área de criterios, lo que le da una gran flexibilidad.
La idea en la técnica de Umlas es usar la fórmula "=ALEATORIO()<x" como criterio, donde "x" es un número mayor que 0 y menor que 1.
Por ejemplo, si queremos extraer una muestra que represente el 10% de la lista usamos "=ALEATORIO()<0.1"
En este ejemplo tenemos una lista de 100 nombres y queremos extraer una muestra aleatoria de 10
Esta técnica tiene un inconveniente: no siempre el resultado será el número esperado. En el ejemplo arriba se puede apreciar que hay 12 nombres en la muestra. Si aplicamos varias veces el filtro veremos que el tamaño de la muestra puede ser mayor o menor que 10.
Para solucionar este problema podemos repetir la acción hasta que la muestra extraída tenga el tamaño deseado. Para automatizar el proceso usamos una macro simple. Grabamos con el grabador de macros las acciones y la macro resultante la asociamos a un botón
Para ahorrarnos el trabajo de contar cuantos nombres hay en la muestra podemos agregar una celda de control con la función CONTARA()
miércoles, enero 01, 2014
Comparar listas con Excel y Access
Una tarea frecuente es comparar listas con Excel, ya sea para detectar valores duplicados o valores faltantes en una u otra lista.
Hay varias técnicas posibles al usar Excel para este tipo de tareas. Muchas de ellas las he mostrado en distintas notas en este blog (pueden leerse haciendo un clic a la etiqueta "Comparar Listas" en la nube de etiquetas).
Las técnicas más usuales incluyen usar Formato Condicional, fórmulas (con COINCIDIR, CONTAR.SI, etc.), e inclusive la poco valorada funcionalidad Datos-Consolidar). Existen también complementos (Add-ins)
El problema con estos métodos es que son poco prácticos, en particular si tenemos que comparar listas con centenas o miles de registros.
Podemos comparar listas de miles de registros con facilidad, seguridad e inclusive obtener los resultados en hojas de Excel con unos pocos clics. Todo lo que necesitamos es una herramienta presente en casi todo computador (o red) que tenga instalado el paquete de Office: Access.
Si, sin saber Access! Vamos a demostrarlo con un ejemplo. Supongamos dos listas, Lista A y Lista B, que contienen nombres de países y su PIB. Cada lista se encuentra en una hoja de un cuaderno Excel.
Nos piden crear los siguientes reportes:
1 – países en la Lista A que no se encuentran en Lista B;
2 – países en Lista B que no se encuentran en Lista A.
Para dificultar las cosas las listas no están ordenadas.
Primer paso: preparamos una base de datos en Access (si, no hace falta saber Access!, tengan confianza)
Abrimos Access y creamos la base de datos
cambiamos el nombre por defecto y elegimos la carpeta
Al terminar el proceso, veremos esto en la pantalla:
Access a creado una base de datos con una tabla vacía. Esta tabla no nos hace falta; al cerrarla Access la borrará.
Segundo paso: insertar las tablas de datos en la base de datos.
Para esta tarea accionamos la pestaña "Datos Externos" en la cinta de opciones (como ven, muy parecido a Excel) y en Importar y Vincular elegimos Excel
Al hacerlo se abre un diálogo que nos guiará en el proceso de importar los datos de las tablas de Excel a la base de datos de Access
Al apretar Aceptar se abre el asistente para importación de hojas de cálculo
Elegimos la hoja Lista A (luego repetiremos el proceso para Lista B) y apretamos "Siguiente"
Si no está marcado, señalamos la opción "Primera fila contiene encabezados...".
En el próximo paso podemos definir el tipo de dato que debe contener cada campo (columna). Para nuestra tarea no nos detendremos en esto y apretamos Siguiente.
En el paso siguiente podemos definir una clave principal para la tabla; tampoco esto es importante a los efectos de nuestra tarea y podemos sencillamente señalar la opción "Sin clave principal"
En el último paso podemos dar nombre a la tabla (por defecto Access usará el nombre de la hoja)
y al apretar Finalizar, Access creará la tabla.
El icono de la tabla aparece en la ventana de objetos (a la derecha). Un doble clic al icono abre la tabla.
Repetimos el proceso para la segunda tabla; al final de proceso tendremos una base de datos con ambas tablas
Tercer paso: crear las consultas.
Access tiene un asistente de para consultas que nos permite crear una consulta para encontrar los elementos faltantes en cada tabla en relación a la otra. Y si, nuevamente no necesitamos saber Access para hacerlo, sólo seguir las instrucciones del asistente.
En la cinta de opciones seleccionamos "Crear" y "Consultas—Asistente para consultas"
En el asistente elegimos la opción "Búsqueda de no coincidentes"
y apretamos Continuar
En los siguientes pasos seleccionamos qué tabla vamos a comparar con cual
En el próximo paso debemos señalar el campo común a ambas tablas (en nuestro caso País). Por lo general Access "adivina" cuál es el campo y todo lo que tenemos que hacer es apretar el botón con el icono <=>
El último paso es definir que campos queremos ver en la consulta, lo que hacemos señalando el campo y apretando el icono ">" (el icono ">>" selecciona todos los campos de una vez).
Apretamos Finalizar y "abracadabra"!!
Repetimos el proceso para comparas Lista B con A
Cuarto paso (opcional): exportar las consultas a hojas de Excel.
En este caso podemos bastarnos con ver los resultados en la ventana de la consulta. Pero en ciertos casos podemos querer exportar los resultados a hojas de Excel para posterior análisis o presentación. También aquí Access nos facilita la vida con el asistente de exportación de datos en la pestaña Datos Externos
Activamos la ventana de la consulta a exportar y definimos en el asistente la carpeta de destino, el formato de Excel, si queremos exportar la consulta con su formato y diseño (recomendado) y si abrir el archivo Excel al terminar el proceso (recomendado).
Todo lo que queda por hacer es apretar Aceptar.
Hay varias técnicas posibles al usar Excel para este tipo de tareas. Muchas de ellas las he mostrado en distintas notas en este blog (pueden leerse haciendo un clic a la etiqueta "Comparar Listas" en la nube de etiquetas).
Las técnicas más usuales incluyen usar Formato Condicional, fórmulas (con COINCIDIR, CONTAR.SI, etc.), e inclusive la poco valorada funcionalidad Datos-Consolidar). Existen también complementos (Add-ins)
El problema con estos métodos es que son poco prácticos, en particular si tenemos que comparar listas con centenas o miles de registros.
Podemos comparar listas de miles de registros con facilidad, seguridad e inclusive obtener los resultados en hojas de Excel con unos pocos clics. Todo lo que necesitamos es una herramienta presente en casi todo computador (o red) que tenga instalado el paquete de Office: Access.
Si, sin saber Access! Vamos a demostrarlo con un ejemplo. Supongamos dos listas, Lista A y Lista B, que contienen nombres de países y su PIB. Cada lista se encuentra en una hoja de un cuaderno Excel.
Nos piden crear los siguientes reportes:
1 – países en la Lista A que no se encuentran en Lista B;
2 – países en Lista B que no se encuentran en Lista A.
Para dificultar las cosas las listas no están ordenadas.
Primer paso: preparamos una base de datos en Access (si, no hace falta saber Access!, tengan confianza)
Abrimos Access y creamos la base de datos
cambiamos el nombre por defecto y elegimos la carpeta
Al terminar el proceso, veremos esto en la pantalla:
Access a creado una base de datos con una tabla vacía. Esta tabla no nos hace falta; al cerrarla Access la borrará.
Segundo paso: insertar las tablas de datos en la base de datos.
Para esta tarea accionamos la pestaña "Datos Externos" en la cinta de opciones (como ven, muy parecido a Excel) y en Importar y Vincular elegimos Excel
Al hacerlo se abre un diálogo que nos guiará en el proceso de importar los datos de las tablas de Excel a la base de datos de Access
Al apretar Aceptar se abre el asistente para importación de hojas de cálculo
Elegimos la hoja Lista A (luego repetiremos el proceso para Lista B) y apretamos "Siguiente"
Si no está marcado, señalamos la opción "Primera fila contiene encabezados...".
En el próximo paso podemos definir el tipo de dato que debe contener cada campo (columna). Para nuestra tarea no nos detendremos en esto y apretamos Siguiente.
En el paso siguiente podemos definir una clave principal para la tabla; tampoco esto es importante a los efectos de nuestra tarea y podemos sencillamente señalar la opción "Sin clave principal"
En el último paso podemos dar nombre a la tabla (por defecto Access usará el nombre de la hoja)
y al apretar Finalizar, Access creará la tabla.
El icono de la tabla aparece en la ventana de objetos (a la derecha). Un doble clic al icono abre la tabla.
Repetimos el proceso para la segunda tabla; al final de proceso tendremos una base de datos con ambas tablas
Tercer paso: crear las consultas.
Access tiene un asistente de para consultas que nos permite crear una consulta para encontrar los elementos faltantes en cada tabla en relación a la otra. Y si, nuevamente no necesitamos saber Access para hacerlo, sólo seguir las instrucciones del asistente.
En la cinta de opciones seleccionamos "Crear" y "Consultas—Asistente para consultas"
En el asistente elegimos la opción "Búsqueda de no coincidentes"
y apretamos Continuar
En los siguientes pasos seleccionamos qué tabla vamos a comparar con cual
En el próximo paso debemos señalar el campo común a ambas tablas (en nuestro caso País). Por lo general Access "adivina" cuál es el campo y todo lo que tenemos que hacer es apretar el botón con el icono <=>
El último paso es definir que campos queremos ver en la consulta, lo que hacemos señalando el campo y apretando el icono ">" (el icono ">>" selecciona todos los campos de una vez).
Apretamos Finalizar y "abracadabra"!!
Repetimos el proceso para comparas Lista B con A
Cuarto paso (opcional): exportar las consultas a hojas de Excel.
En este caso podemos bastarnos con ver los resultados en la ventana de la consulta. Pero en ciertos casos podemos querer exportar los resultados a hojas de Excel para posterior análisis o presentación. También aquí Access nos facilita la vida con el asistente de exportación de datos en la pestaña Datos Externos
Activamos la ventana de la consulta a exportar y definimos en el asistente la carpeta de destino, el formato de Excel, si queremos exportar la consulta con su formato y diseño (recomendado) y si abrir el archivo Excel al terminar el proceso (recomendado).
Todo lo que queda por hacer es apretar Aceptar.
Suscribirse a:
Entradas (Atom)