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

A partir de un comentario de Anahí, me encontré con un problema muy frecuente en Excel: No funciona como se espera la función SI cuando la función lógica contiene una fecha.

Ejemplo, si escribimos:

=SI(I4<=20/06/2010;”fecha”;”vencido”), esperamos que compare el valor de I4 con la fecha 20/06/2010, pero no funciona.

Esto pasa porque nosotros queremos comparar el resultado de I4 con la fecha 20/06/2010, pero al expresar la fecha de esta forma, Excel interpreta que estamos haciendo un cálculo.

Entonces compara I4 con el resultado de dividir 20/6 y después dividirlo nuevamente entre 2010.

Por lo tanto, la función SI nos devuelve cualquier cosa.

Esta situación me sirve para presentar la función FECHANUMERO.

Esta función, “Convierte una fecha en forma de texto en un número que representa la fecha en código fecha y hora de Microsoft Office Excel” (fuente: Ayuda de Microsoft Excel).

La sintaxis es simplemente:

=FECHANUMERO(texto_de_fecha)

Ejemplo:

FECHANUMERO(“20/06/2010″) devolverá 40349 que para nosotros no significa nada, pero para Excel significa 20 de junio de 2010 que es exactamente lo que necesitamos.

Por lo tanto, para resolver el problema mencionado al inicio de este tutorial, solamente debemos reemplazar la fórmula por:

=SI(I4<=FECHANUMERO(“20/06/2010″);”fecha”;”vencido”).

Y ya obtendremos el resultado que necesitamos.

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

¿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

Este truco va en respuesta a una consulta que me hizo Richard.

Es muy útil para quienes arman formularios o recibos en Excel.

Necesitamos que cada vez que escribamos un texto, el espacio dejado en blanco se complete con guiones (o cualquier otro caracter).

Afortunadamente la solución es muy sencilla.

La solución NO viene por el lado de utilizar funciones de texto, sino por formatos.

Debemos hacer los siguiente:

1. Vamos a Formato de celdas (ctrl 1 es el atajo de teclado)
2. En la pestaña Número, seleccionamos la categoría Personalizada y en Tipo ingresamos: @*-

Así no más: arroba asterisco guión (o el caracter que necesitemos).

Las tres partes del formato indican a Excel:

• @: indica que es el formato para “texto”.
• *: para el resto del espacio visible
• -: complete con guiones.

Y listo!! Sin importar el ancho de la columna, siempre la completará con guiones, pero el contenido de la celda seguirá siendo el valor entrado (en este caso, simplemente la palabra “texto”).

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

¿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

BUSCARV caso coincidencia aproximada.

Descargar archivo de ejemplo.

He recibido muchísimas consultas sobre cómo asignar un valor o una “etiqueta” para distintos valores según el rango en que se encuentren.

Puede ser tanto el caso de asignar un porcentaje según el nivel de ventas alcanzado o poner una calificación según el rango de nota obtenida.

En este tutorial de Excel veremos este caso.

Tenemos la tabla con rangos que se muestra a la derecha y necesitamos que para cada nota nos asigne una calificación según el rango en que se encuentre.

Por ejemplo, si una nota es 6.8, la calificación será “Regular” o si es 8.7 será “Muy buena”.

Para esto utilizaremos BUSCARV.  Ya vimos cómo funciona cuando necesitamos hacer una búsqueda exacta.  Esto es, hay una asociación única entre dos valores.  Si no recuerdas bien cómo utilizar la función, te invito a que repases antes ese tutorial.

Leer el resto de esta entrada »

Ir a.. Especial .. Diferencias entre filas

En el tutorial anterior les mencionaba dónde encontrar “Ir a …” en Excel 2007.

Ahora veremos un par de ejemplos.  En el primero, simplemente buscaremos los números que no coinciden.

En el segundo, la utilizaremos para encontrar una fórmula mal ingresada.

Por ejemplo, asumamos que tenemos los datos como en las columnas del gráfico de la derecha.

Ejemplo 1: Encontrar datos diferentes

Las columnas son prácticamente idénticas, excepto por algunas filas donde los datos no coinciden.

¿Cómo podemos identificarlos fácilmente?

1. Seleccionamos el rango con los datos A3:B11.

2. Abrimos el menú “Ir a …” (Ctrl I es la forma más fácil)

3. Hacemos click en “Especial …“  y seleccionamos “Diferencia entre filas”.  Con esto indicamos a Excel que queremos encontrar aquellos datos en cada fila que son diferentes.

Ir a Especial -> Diferencia entre filas

4. Al oprimir “Aceptar”, Excel seleccionará todas las celdas de la segunda columna que no coinciden con la primera.

Ejemplo 2: Encontrar fórmulas que no coinciden.

Supongamos que tenemos el siguiente caso, donde en la primera fila tenemos unos datos que se ingresan y el la segunda unos resultados a partir de una fórmula.

Diferencias en fórmulas

En principio, no vemos nada raro.  Los resultados de la fila 2 son el doble de la fila 1.

Por lo menos en B2, la fórmula es así, pero ¿Es así en las celdas C2, D2, E2 y F2?

• Seleccionamos el rango B2:F2
• Ctrl I (abre el menú Ir a …)
• Especial …
• Diferencia entre filas

Me quedó marcada la celda D2, ¿por qué?

Si utilizamos “Mostrar fórmulas” veremos el porqué:

A pesar que los resultados se veían correctos, por alguna razón tenía pegado el valor 60 en lugar de la fórmula.

A través de “Diferencias entre filas”, logré identificar un error de fórmulas en esta fila.

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

¿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!

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