Trucos de Excel

Archivo de la categoría ‘AVANZADO’

Descargar el archivo Excel de este ejemplo.

No he estado muy creativo en mis últimos trucos: por lo menos los últimos tres los he escrito con base en preguntas que he recibido por parte de ustedes.

Éste no será la excepción.  Esther me comenta que necesita armar un cuadro de control con listas dependientes (como en el tutorial de INDIRECTO – Ejemplo de listas dependientes), pero no a través de validación de datos, sino con Listas desplegables (o Cuadros Combinados).

No habría mucho problema si no fuera por un detalle: Al definir el Rango de Entrada de una Lista Desplegable, solo podemos incluir eso: un rango (o nombre de un rango) y no nos permite utilizar la función INDIRECTO como hicimos en dicho tutorial.

Si no entiendes de lo que hablo, te invito a leer los tutoriales de Indirecto y Listas desplegables.

Para solucionarlo, vamos a utilizar un Rango Variable.

En el archivo que vamos a trabajar, ya tenemos los países en el rango llamado Paises, cada una de las ciudades correspondientes nombradas como en el tutorial de Indirecto.

Tenemos además dos cuadros combinados: uno al lado de países (asociado a la celda E2 que necesitaremos más adelante).

Para definir un nombre, necesitamos abrir el Administrador de Nombres.

Este se encuentra en la pestaña Fórmulas -> Administrador de Nombres.  O simplemente usando el atajo de teclado: CTRL + F3

Esto nos despliega todos los nombres que tenemos definidos y nos da la posibilidad de crear un nombre Nuevo, Editar uno Existente o Eliminar.

Seleccionamos Nuevo…

Ahora viene lo más complicado!! (¿?): Necesitamos definir un rango para Ciudades que varíe según el país que se elija en la Lista Desplegable de país.

Como vimos en el tutorial de INDIRECTO, tenemos cada conjunto de ciudades en rangos nombrados según al país al que pertenecen (Ejemplo, el nombre “Brasil” está asociado al rango $F$3:$F$7), tal como vemos marcado en la imagen superior.

Como los países están en el rango Paises, a través de la función INDICE podemos obtener el país seleccionado que nos indicará el nombre de las ciudades asociadas (como vimos en el tutorial de Indirecto).

En el menú de Nombre Nuevo, ingresamos un nombre (por ejemplo MiRango).

En “Hace referencia a” es en el lugar donde definimos el rango variable. La fórmula es:

=INDIRECTO(INDICE(Paises;$E$2))

Donde:

• $E$2: es la celda vinculada con el cuadro combinado de países.  Es decir, tendremos un índice según la selección (Brasil es 1, Argentina 2, Colombia 3 y México 4).
• Paises: Es el nombre del rango que tiene a los países.  Como puede verse en la imagen del Administrador de nombres, es el rango K3 a K6.
• INDICE(Paises:$E$2) Nos devuelve el nombre del país seleccionado (es decir, reconvierte un 1 en “Brasil” por ejemplo).
• INDIRECTO(INDICE(Paises:$E$2)), convierte el texto en una dirección.  Es decir, ahora “Brasil” no es un texto sino un nombre de Excel asociado a su rango.

Ahora solo nos resta vincular la segunda lista combinada con “MiRango” como se muestra en la primera imagen de este tutorial (botón derecho sobre el control, Formto de Control, Pestaña “Control”).

Prueba con el archivo de trabajo de este post e intenta hacerlo.  Es mucho más fácil de lo que parece!

———————————————————————————————————————————————-

¿Te gustó este truco o tenés alguna recomendación u otra forma de hacerlo?  No te olvides de dejar tu comentario y compartir esta nota!

Aunque a veces demore en contestar, leo ABSOLUTAMENTE todos los comentarios y mails que me envían, y estoy muy agradecido por el tiempo que se toman con sus consultas, halagos y críticas y procuro contestar a todos.

Buscá más trucos en: trucosdeexcel.com
Seguime por Twitter, usuario “trucosdeexcel”.
Mandá tus sugerencias o preguntas a: excelman@trucosdeexcel.com

Descargar archivo de ejemplo.

Barra de desplazamiento

Pasaron varios vaaaarios días desde mi último truco de Excel, pero acá estoy de vuelta!!

Ya hemos visto los controles de formulario Listas Desplegables (Cuadros combinados) y Botón de opción.

Hoy veremos uno más: Barra de desplazamiento.  Y veremos su uso a través de un ejemplo donde crearemos un gráfico interactivo.

La barra de desplazamiento nos es muy familiar.  Podemos verla en el gráfico de la derecha.

Vamos a ver su funcionamiento con un ejemplo donde el gráfico cambiará la proporción de “Aprobados” y “Desaprobados” según movamos la barra hacia la izquierda o derecha.

Leer el resto de esta entrada »

Descargar archivo de ejemplo.

Botones de Opción

Ya vimos uno de los controles de formularios en el post de Listas desplegables.  Si no lo viste, te recomiendo que lo hagas ya que el funcionamiento es bastante similar.

Hoy veremos otro: El botón de opciones.

Es muy común en distintos tipos de formularios donde hay un número de alternativas fijas, pero solo se debe optar por una única opción.  El ejemplo más claro: las respuestas de una encuesta o examen.

En este tutorial veremos un ejemplo muy sencillo para entender el funcionamiento.

Tendremos dos botones: uno que diga SI, otro NO y en otra celda nos dirá “Usted eligió ” y la opción que hayamos elegido.

Leer el resto de esta entrada »

Descargar archivo de ejemplo

Luis me envió la siguiente consulta:

Tiene un detalle de compras de un año (factura, fecha, categoría, descripción, importe) y necesita tener – en hojas individuales por cada mes – el resumen de las compras agrupadas por categoría.

Esto es, necesitamos las fechas individuales de cada transacción, agrupadas por mes y que cada uno de ellos esté en una hoja diferente.

Por supuesto, sin tener que armar manualmente una a una la hoja de cada mes.

Más o menos como se muestra en la siguiente imagen:

Para hacer esto vamos a aprovechar propiedades poco conocidas de las tablas dinámicas: Agrupar y Mostrar páginas de filtros.

Leer el resto de esta entrada »

NIVEL: AVANZADO

Descargar archivo de ejemplo.  Para que funcione hay que habilitar las macros.

En este tutorial veremos como programar una función en Excel que permitar concatenar fácilmente varios elementos al ingresar un rango de datos y no las celdas uno a uno como nos pide la función CONCATENAR en Excel.

No serán frecuentes los trucos publicados que involucren programación en Visual Basic (VBA) por dos motivos:

• El objetivo de este blog es aprender explotar las herramientas que ya cuenta el Excel y que en varios casos facilita el no tener que recurrir a programación.
• Fundamentalmente porque NO es mi fuerte la programación.  La enorme mayoría de situaciones que he tenido que resolver, he podido solicionarlas sin necesidad de recurrir a macros.

Pero siempre hay excepciones.  Y este caso es uno de ellos.

La función CONCATENAR es MUY limitada.  Debemos incluir una a una las celdas que se quieren combinar y no permite utilizar rangos.

Supongamos tenemos las siguientes listas:

Y queremos tener en una única celda, todas las ciudades de cada país combinadas.

Ejemplo, que para Brasil nos quede: “San Pablo, Belo Horizonte, Fortaleza, Salvador”.

La función que deberíamos aplicar sería:

• =CONCATENAR(B3;”, “;B4;”, “;B5;”, “;B6;”, “;B7;”.”)

Bastante engorroso especialmente si tuviéramos mucho más que cinco celdas para combinar.

Para complicar más la situación, no todos los países de este ejemplo tienen la misma cantidad de ciudades.

Por ejemplo, para México solo listé tres.  Si copiamos la función de arriba en la columna E, el resultado sería: “México, Monterrey, Guadalajara, , .”

No es lo que estamos buscando, pero para que quede mejor, habría que corregir la función a mano y escribir:

• =CONCATENAR(E3;”, “;E4;”, “;E5;”.”)

No es práctico.

En este tutorial veremos cómo solucionar esta situación al crear una función que solo con escribir la siguiente función, nos de la respuesta que queremos:

• =MULTCONCAT(B3:B7)

Leer el resto de esta entrada »