Ideas de Excel Avanzadas

Invitación: Curso virtual 12 horas de Excel avanzado

noreply@blogger.com (Diego Alzate) — Sat, 30 Jan 2010 16:04:00 +0000

Si por algún motivo no pudo participar de nuestro curso anterior, lo invitamos a hacer parte de este Curso virtual 12 horas de Excel avanzado, inicio del 2010

Le ofrecemos horarios flexibles que se adaptan a su calendario, donde los temas que trataremos irán desde validación de datos, nombres de rango, formato condicional, hasta tablas dinámincas, macros básicas y cómo registrar complementos.

Un curso orientado a aquellos usuarios que quieren sacarle más provecho a esta fabulosa herramienta que es Excel.

Inscríbase ya!!

Más información | Curso virtual Excel avanzado

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

¿Cómo crear una macro automáticamente?

noreply@blogger.com (Diego Alzate) — Mon, 25 Jan 2010 14:51:00 +0000

La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone Excel.

Este grabador de macros le permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA (Visual Basic para Aplicaciones), las cuales podemos modificar posteriormente si tenemos conocimientos de programación.

Por el momento solo será automáticamente y no necesitaremos de la programación en VBA.

Recordemos:

¿Qué es una macro? Es una serie de pasos que se almacenan y se pueden activar con alguna tecla de control y una letra. Por ejemplo, todos los días empleo frecuentemente en mis celdas los mismos pasos: Tamaño de Fuente (Tamaño de la letra), Negrita, Fuente (Tipo de letra) y Color de Fuente (Color de Letra), para no estar repitiendo estos pasos los puedo almacenar en una macro y cuando ejecute la macro los pasos antes mencionados se ejecutaran las veces que yo lo desee.

Nota: Recuerde que debe de habilitar las macros para su funcionamiento.

1. Haga clic en la ficha Vista.

Despliegue el menú de la opción Macros, y seleccione la opción "Grabar macro..."

2. Aparecerá una nueva ventana con las siguientes opciones:

Nombre de la macro (obligatorio): Es el nombre que usted le colocará para identificarla de las demás.

Método abreviado (opcional): Personalizamos la forma de llamar la macro mediante la combinación de las tecla CTRL + "una tecla del teclado". Tenga en cuenta en utilizar una combinación que no exista en Excel.

Guardar Macro en (por defecto el libro actual): Seleccione guardar la macro en el libro activo, en el libro de macros personal o en otro libro.

Descripción (opcional): Haga una breve descripción de la utilización de esta macro.

Termine haciendo clic en Aceptar

3. Automáticamente comenzará a grabar los pasos que usted haga en ese momento en la hoja de calculo de Excel, se dará cuenta ya que en parte inferior izquierda aparece lo siguiente:

4. Ahora, para este ejercicio haremos lo siguiente: Seleccionaremos cualquier celda de la hoja de cálculo y le cambiaremos de color de relleno, colocaremos negrita y aumentaremos el tamaño de la fuente.

Nota: Debemos tener en cuenta que si llegamos a escribir algo en la celda, la macro se grabará de tal forma que al ejecutarla aparecerá tantas veces sea posible lo que hayamos escrito, y este ejercicio no tiene esa función.

Para darle formato a las celdas, usaremos "Color de relleno", "Negrita" y "Tamaño de letra" en la opción "Fuente" de la ficha "Inicio"

5. Luego de hacer este cambio, detenga la grabación de la macro, desde la barra inferior derecha

6. Ahora, ejecutaremos la macro que hemos creado, pero antes, debemos seleccionar las celdas en las que se ejecutará la macro.

En nuestro ejemplo tenemos la siguiente tabla de ejemplos, lo cual cambiaremos lo que esta seleccionado.

7. En la ficha Vista, haga clic en la opción Macros y seleccione "Ver macros"

Seleccione la macro deseada y pulse sobre el botón Ejecutar. Se cerrará el cuadro y se ejecutará la macro.

Cancelar: Cierra el cuadro de diálogo sin realizar ninguna acción.

Paso a paso: Ejecuta la macro, instrucción por instrucción abriendo el editor de programación de Visual Basic.(para usuarios avanzados)

Modificar: Abre el editor de programación de Visual Basic para modificar el código de la macro. (para usuarios avanzados)

Eliminar: Borra la macro.

Opciones: Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (combinación de teclas que provoca la ejecución de la macro sin necesidad de utilizar el menú) y la descripción de la macro.

8. Asi se vería la macro ejecutada

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Función SUBTOTALES

noreply@blogger.com (Diego Alzate) — Wed, 09 Dec 2009 16:44:00 +0000

La Función “SUBTOTALES” permite visualizar subtotales en una lista o base de datos, con el fin de evitar perder la sumatoria de un rango específico. Esta función resulta ser muy útil al momento de trabajar con filtros o celdas ocultas.

EJEMPLO:

1. Abra un Libro de Excel en blanco y llene la siguiente información:

2. Aplique SUMA en la columna Valor

Si aplica un filtro y selecciona solo la línea Bebidas, notará que la sumatoria permanece sin actualizar; o si necesitara ocultar algunas líneas, la sumatoria del valor total quedaría estática, haciendo de éste un valor inexacto. Veamos ….

Para evitar que esto pase entonces utilizaremos la Función SUBTOTALES.

3. Muestre nuevamente todas las celdas y elimine el total agregado manualmente y quite los filtros si los tiene.

4. Ubíquese en la celda C15.

5. Inserte la Función SUBTOTALES:

6. El campo Núm_función corresponde a un número dependiendo de una lista de acuerdo a la función, donde:

7. Para nuestro ejemplo utilizaremos la Opción 109 correspondiente a SUMA: pasa por alto valores ocultos.

De esta forma se podrán ocultar celdas y Excel automáticamente actualizará el valor de la suma.

Tablas Dinámicas | Capitulo 5

noreply@blogger.com (Diego Alzate) — Wed, 02 Dec 2009 21:49:00 +0000

GRÁFICOS DINÁMICOS

Después de haber finalizado y estructurado una tabla dinámica, el siguiente paso para presentar la información es en una gráfica. A continuación verá como crear un gráfico dinámico partiendo de una tabla dinámica realizada.

NOTA: Para realizar el mismo ejemplo, descargue el archivo en Excel con datos de prueba del capítulo 1.

Abra el archivo con los datos ejemplo.
Realice una tabla dinámica con los siguientes datos:
Campo de página: Arrastre el campo mes y seleccione el mes 5.
Campo de Fila: Arrastre el campo Nombre del Vendedor.
Campo Valor: Arrastre el campo Precio

Agregue formato "estilo millar" al campo valor.
Ubíquese en una celda en la tabla dinámica.
Haga clic a la pestaña herramientas de tabla dinámica. Y posteriormente haga clic en la pestaña "Opciones"

Haga clic a la opción "Gráfico dinámico"

Seleccione el tipo de gráfico deseado

De esta manera el gráfico quedará dinámico, usted podrá agregar, eliminar, seleccionar campos de acuerdo a sus necesidades utilizando el panel de filtros de Gráfico dinámico que aparece al lado derecho de su pantalla.

TIPS 2007

Para cambiar la ubicación del gráfico haga clic en el botón en la pestaña "Diseño", y posteriormente en el botón Ubicación.

Para insertar un logo o imagen dentro del gráfico dinámico, haga clic en la pestaña "Presentación" y posteriormente en el botón "Insertar", seleccione la ubicación de la imagen.

Office 2007 agrega la pestaña Analizar, dentro del grupo de Herramientas del gráfico Dinámico. Este es un grupo de opciones que le permiten activar o desactivar el filtro para el gráfico dinámico, actualizar, insertar y eliminar filtros.

Video | Construir informes con Tablas dinámicas | Capitulo 4

noreply@blogger.com (Diego Alzate) — Mon, 23 Nov 2009 19:53:00 +0000

Con Excel, es posible crear varios informes en varias hojas con un sólo clic utililizando tablas dinámicas.

En muchos casos de informes de ventas, es complicado cuando nos piden 1 informe por cada vendedor. En este caso, si tenemos 10 vendedores serían 10 informes.

Otro caso sería 1 informe por cada línea. Imagine si tiene 10 líneas, serían 10 informes por cada línea.

Con la tabla dinámica es mucho más fácil resolver este problema.

El siguiente video le mostrará en 4 minutos cómo hacerlo

Si no puede ver el video, haga clic aqui

Cómo habilitar la pestaña Programador en Excel 2007?

noreply@blogger.com (Diego Alzate) — Wed, 11 Nov 2009 19:10:00 +0000

Con las opciones de esta pestaña, es posible programar Macros y añadir controles de formularios

Para habilitarla, haga lo siguiente:

1. Haga clic en el botón de Office ubicado en el extremo superior izquierdo de Excel

2. Haga clic en el botón "Opciones de Excel"

3. En la opción "Más frecuentes", active la opción "Mostrar ficha Programador en la cinta de opciones"

4. Termine haciendo clic en Aceptar

Curso virtual | 12 horas de Excel avanzado

noreply@blogger.com (Diego Alzate) — Fri, 06 Nov 2009 17:11:00 +0000

Tenemos una invitación muy especial para ustedes!

Cuando alguien le pregunta: Qué maneja usted de Excel? Usted responde: "Lo básico", cierto? Pero, qué es lo básico? Listados, funciones, formatos de celda, filtros, gráficos y otras cosas más.

Pero igual siempre se tiene la sensación que nos falta mucho por conocer, incluso muchas personas creen que solo manejan el 5% o 10% de lo que es Excel®.

Hemos diseñado un Curso de Excel® de 12 horas con herramientas realmente productivas, que no inicie con cosas tan básicas que ya sabemos manejar como por ejemplo abrir Excel®, crear un nuevo libro, imprimir, cerrar, etc. Que no dedique 40 o 60 horas de un curso tradicional en el cual en realidad la mayoría de tiempo se va en práctica, cosa que podemos hacer en nuestro tiempo, pero que en cambio si enseñe muchas de las herramientas que no usamos de Excel® simplemente porque no las conocemos o porque creemos que son Excel® Avanzado, por ejemplo:

Formato Condicional
Validación de datos
Listas personalizadas
Funciones de Texto, Derecha, Izquierda, Extrae, Largo e.t.c
Función Elegir y Función Ir especial
Haremos mucho énfasis en tablas dinámicas.

Ingrese al sitio, entérese e inscríbase ya!! | Curso virtual Excel®

Tablas Dinámicas | Capitulo 3

noreply@blogger.com (Diego Alzate) — Wed, 04 Nov 2009 21:01:00 +0000

Recuerden que esta es una serie de tutoriales sobre tablas dinámicas y al final de este post podrán encontrar los enlaces a los capítulos 1 y 2. Anexaremos también el archivo en Excel que estamos usando en los ejercicios.

Hoy veremos "Formato y Diseño".

Excel proporciona unos estilos predeterminados para asignar un formato a una tabla dinámica, esto con el fin de mejorar su presentación y dar un toque más agradable a la presentación de la información

Después de agregar campos y determinar el nivel de detalles que han de mostrarse, como lo vimos en el capitulo 1, es posible que desee mejorar el diseño y el formato

Ubíquese dentro de la Tabla Dinámica.

Haga clic a la pestaña Diseño.

En esta pestaña veremos cada uno de los botones que lo componen:

ETIQUETA 1

“SUBTOTALES”

Esta característica permite agregar un subtotal a la tabla dinámica

1.

Esta opción elimina los subtotales de la tabla dinámica

Esta opción permite mostrar los subtotales en la parte inferior de cada grupo como lo indica el botón

Esta opción permite mostrar los subtotales en la parte superior de cada grupo como lo indica el botón

Esta opción solo es posible sólo si se tiene aplicado el formato en diseño de informe tipo esquema o compacto. Ver etiqueta Diseño de Informe.

Etiqueta 2

“Diseño de informes”

Esta opción permite visualizar el informe de 3 formas diferentes: Compacta, Esquema y Tabular

Etiqueta 3

“Filas en blanco”

Es esta etiqueta podemos agregar o eliminar una celda en blanco antes del siguiente elemento.

Etiqueta 4

“Opciones de estilo de la tabla dinámica”

En esta lista de opciones, Excel permite agregar un toque más personalizado y agradable

Si activa la casilla de verificación de encabezados de filas, el título de fila tendrá formato negrita

Si activa la casilla de verificación de encabezados de columnas, éste agregará un color predeterminado a sus encabezados

Si activa la casilla de verificación filas con bandas, éste agregará unos rellenos predeterminados a cada una de las líneas de forma intercalada.

Si activa la casilla de verificación columnas con bandas, éste agregará un relleno predeterminado a la columna de valores.

Etiqueta 5

“Estilos de tabla dinámica”

Para finalizar estas opciones tenemos los estilos de tablas dinámicas: Permiten personalizar y agregar un estilo más presentable a su tabla. Basta con seleccionar el diseño.

En el siguiente enlace encontrará el Capítulo 1 y el Capítulo 2

Descargue el archivo de Excel de muestra desde el siguiente botón:

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Tablas Dinámicas | Capitulo 2

noreply@blogger.com (Diego Alzate) — Wed, 28 Oct 2009 14:44:00 +0000

Para esta ocasión, veremos una utilidad de la tabla dinámica: Buscar el porcentaje que representa un valor sobre un valor total.

Seguiremos trabajando con nuestro archivo del Capitulo 1. Descárguelo aquí:

Es decir, para nuestro informe dinámico donde tenemos en el campo de fila la lista de las líneas y en campo de valor la cantidad vendida, vamos a hallar el porcentaje que representa esta venta frente al valor total.

CARACTERISTICAS DEL CAMPO DATO DE LA TABLA DINAMICA

Etiqueta "Campo Activo"

Con el archivo que acaba de descargar, realice una tabla dinámica llevando a los campos de fila, el campo "Nombre línea" y el campo "precio" al área Valor dos veces

Ubíquese en cualquier celda del campo de Valor, ejemplo "B6"

Haga clic a la pestaña de la pestaña "Opciones"

Haga clic en la etiqueta “Campo Activo”

Haga clic a la opción "Configuración de Campo"

Haga clic a la pestaña “Mostrar valores como”

Seleccione la opción mostrar valores como: “% del total”

Haga clic en Aceptar

Podrá verificar que el campo Precio cambiara por el % que representa ese valor sobre las ventas totales.

Para una mejor presentación ordene por valor descendente.

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Tablas Dinámicas | Capitulo 1

noreply@blogger.com (Diego Alzate) — Tue, 20 Oct 2009 20:58:00 +0000

A partir de hoy, estaremos publicando una serie continua de posts referentes a las tablas dinámicas. Éste será nuestro primer capítulo. Si tiene dudas, déjenos un comentario.

DEFINICION: Las tablas dinámicas permiten realizar un resumen de datos agrupados, que puntualizan en forma concreta datos de una información. Es decir con una Tabla Dinámica se pueden hacer resúmenes de una base de datos, utilizándose para promediar, o totalizar datos.

Invoque el asistente de Tablas Dinámicas.

1. Haga clic en la pestaña Insertar

2. Ubique la etiqueta Tabla

3. Haga clic en la opción Tablas Dinámicas

DEFINICION | Base de datos: Una base de datos es un conjunto de información estructurada en registros y almacenada en un soporte electrónico legible desde un ordenador.
Cada registro constituye una unidad autónoma de información que puede estar a su vez estructurada en diferentes campos o tipos de datos que se recogen en dicha base de datos.

COMPONENTES DE UNA TABLA DINÁMICA

Una tabla dinámica contiene DIMENSIONES, que corresponden a los campos pertenecientes a la BD, cada uno de los cuales resume múltiples filas de información de los datos originales. A su vez, resume los datos utilizando las funciones matemáticas que se especifiquen como SUMA, CUENTA, PROMEDIO, etc. Se podrían incluir automáticamente subtotales y totales generales o utilizar sus propias fórmulas agregando campos y elementos calculados.

PASOS PARA CREAR UNA TABLA DINÁMICA

Para crear una tabla dinámica debemos partir de una base de datos bien sea en un programa de almacenamiento de Información como Access, SQL entre otros; o bien sea de un listado en Excel, para nuestro ejemplo utilizaremos un listado con datos ficticio de un sistema de Facturación (este listado guárdelo en su equipo, nos servirá para los siguientes capítulos.)

Descárguelo desde el siguiente botón

Ubíquese en un lugar de la base de datos.

Invoque el asistente de Tablas Dinámicas

1. Haga clic en la pestaña Insertar

2. Ubique la etiqueta Tablas.

3. Haga clic en la opción Tablas Dinámicas

4. Automáticamente Excel selecciona el rango de datos

5. Clic en Aceptar

Aparecerá en una hoja diferente, la tabla dinámica lista para que usted inicie el proceso de consulta dinámica. Para ello arrastre cada uno de los datos que desea analizar y suelte el dato solo en el campo deseado. Para nuestro ejemplo analicemos las líneas de productos que más se vendieron por cantidad.
Para ello haga lo siguiente.

1. Ubique en la lista de campos de la tabla dinámica, el campo "Nombre Línea", arrástrelo hasta el área rótulos de fila.

2. Ubique en la lista de campos de la tabla dinámica, el campo cantidad y arrástrelo hasta el área rótulos de valores

Verá como ya tiene un informe rápido y dinámico.

Adicionalmente Excel agrega un pestaña nueva “Herramientas de Tabla dinámica” y ésta a su vez agrega dos pestañas más: “Opciones” y “Diseño” que permiten la administración, configuración y personalización de las tablas dinámicas

NOTA

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Gráficos intercambiables en una misma hoja

noreply@blogger.com (Diego Alzate) — Wed, 14 Oct 2009 14:20:00 +0000

Para esta ocasión, veremos varias funciones avanzadas.

Digitaremos en una hoja unos datos para generar un gráfico pero que a su vez este tenga la posibilidad de seleccionar el tipo de gráfico.

1. Abra un libro nuevo en Excel.

2. Cree una lista con los siguientes datos:

3. Cree el primer gráfico de tipo Columnas Agrupadas.

4. Seleccione el gráfico y córtelo (CTRL + X)

5. Péguelo en la Hoja2 (CTRL + V). Asegúrese que el área del gráfico coincida exactamente con un rango de celdas, para este caso A1:F16.

6. Copie el gráfico y péguelo en otro rango de la Hoja2, por ejemplo A18:F33.

7. Repita el mismo procedimiento

NOTA: En la Hoja2 debe tener 3 gráficos, Por favor cambie el estilo de gráficos: El primero en columnas, el segundo en líneas y el tercero en áreas. Para cambiar el tipo de gráfico: Haga clic en el gráfico y haga clic en cambiar tipo de gráfico

8. Cree un rango para cada una de las áreas de los gráficos así:

Columnas = Hoja2!$A$1:$F$16
Líneas = Hoja2!$A$18:$F$33
Area = Hoja2!$A$35:$F$50

9. En la Hoja2 cree una lista en el rango I1:I3 escribiendo en cada celda: Columnas, Lineas, Areas. Esta lista nos servirá más adelante para crear la lista desplegable.

10. Haga clic en la Hoja1, ubíquese en la celda H1 y cópiela (Ctrl+C). Ahora ubíquese en la celda D1 y presione la tecla MAYUSCULA, haga clic en la Ficha "Inicio", despliegue la opción "Pegar" - "Pegar como Imagen" y posteriormente seleccione la opción "Pegar vínculos de imagen"

11. Aparecerá una imagen vacía y en la barra de fórmulas verá la referencia a la celda que hemos copiado ($H$1)

12. Reemplace $H$1 en la barra de las fórmulas por el nombre Columnas que había creado sobre el rango que contiene ese tipo de gráfico en la Hoja2.

Note que el gráfico de columnas aparece en la Hoja1. Si cambia la referencia Columnas a Líneas, verá aparecer el gráfico tipo Líneas.

Ahora vamos a incluir un método para elegir el tipo de gráfico de una lista desplegable.

13. Cree una lista desplegable usando el control "Cuadro combinado" de la Ficha "Programador" (no controles ActiveX).

14. Dibuje el control y ubíquese sobre la celda A12.

El rango de entrada es de la Hoja2 I1:I3

Vincule con la celda A12.

15. Cree el nombre "Tipo3" que contenga la siguiente fórmula:
=ELEGIR(Hoja1!$A$12;Columnas;Líneas;Areas)

16. Seleccione la celda D1 y en la barra de Fórmulas escriba =Tipo3

A partir de este momento el tipo de gráfico cambiará de acuerdo a la selección que usted haga en la lista desplegable

Puede descargar el archivo del ejercicio en este link (archivo comprimido)

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Validación de datos

noreply@blogger.com (Diego Alzate) — Wed, 07 Oct 2009 20:08:00 +0000

La validación de datos en Excel permite definir el dato que desea registrar en una celda y limitar en algunos casos a algún usuario a registrar un valor NO valido, esto permite que nuestros listados o informes sean más
genéricos y con menos errores.

Veamos un ejemplo claro de Validación.

Para nuestro ejemplo, tenemos un cuadro en Excel que es necesario sea manipulado por varios usuarios y que cada uno de ellos digite la información requerida para analizar las cantidades vendidas de acuerdo al nombre del vendedor y al día de la semana.

Este es el libro que será diligenciado por varios usuarios:

1. Ubíquese en la celda E1 e iniciamos el registro de los nombres de los vendedores tipo listado y los días de la semana.

2. Ubíquese en la celda A3.

3. Haga clic en la etiqueta "Datos" y posteriormente a la opción "Validación de datos".

4. En la pestaña "Configuración" y en la opción "Permitir valor", haga clic en "Lista". Inmediatamente se activará un campo ORIGEN, donde seleccionaremos nuestra lista de nombre vendedores.

5. Seleccione la lista de nombres

6. Para retornar al cuadro de dialogo de Validación de datos haga clic en el botón

7. Haga clic a la pestaña "Mensaje de error"

8. Active la casilla de verificación:

9. Seleccione el estilo de mensaje que usted desee.

10. Escriba un título al mensaje ejemplo:

11. Si lo desea puede escribir aún más detallado el mensaje de error en el espacio habilitado “Mensaje de error"

12. Haga clic en aceptar.

13. En nuestro libro aparecerá una pestaña para seleccionar.

NOTA: Se recomienda copiar hacia abajo la Validación de datos

14. Para validar los mensajes de error escriba un nombre de vendedor que no se encuentre en la lista ejemplo: Natalia Arias

Note que inmediatamente aparecerá un mensaje de error, este fue el mensaje que se configuro anteriormente, asi el usuario se verá obligado a escoger de la lista su nombre sin necesidad de dañar nuestro formato.

Repita el mismo procedimiento para el Día.

Si desea conocer más acerca de validación de datos visite Microsoft Office

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Función BUSCARV

noreply@blogger.com (Diego Alzate) — Fri, 02 Oct 2009 16:44:00 +0000

Definición La función BUSCARV permite hallar un valor en una primera columna de una matriz (o rango de datos).

La V significa vertical, asi que entonces la función BUSCARV significa Buscar valor Vertical.

SINTAXIS: =BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

valor_buscado es el valor que se va a buscar en la primera columna de la matriz de tabla. Puede ser un valor o una referencia.

matriz_buscar_en Dos o más columnas de datos. Use una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca .

indicador_columnas: Este campo hace referencia al número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si indicador_columnas es:

Ordenado: Especifica si BUSCARV va a buscar una coincidencia exacta o aproximada:

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este caso, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o más valores en la primera columna de matriz_buscar_en, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

VEAMOS UN EJEMPLO PRÁCTICO

Abra una hoja de Excel en blanco donde según el nombre de vendedor nos devolverá la ciudad del vendedor dependiendo de una lista (matriz o rango de datos)

Lo que pretende la función BUSCARV es que al momento de un usuario digitar en la HOJA2 en la celda A1 el nombre del vendedor, automáticamente en la celda B2 aparezca la Ciudad que depende del nombre.

1. Haga clic a la etiqueta HOJA2.

2. Ubíquese Celda B1

3. Haga clic sobre el botón insertar función de Excel, en categorías de funciones, seleccione "búsqueda y referencia". En nombre de la función, selecciona BUSCARV

En el argumento valor_buscado, selecciona la celda A1.

En el argumento matriz_buscar_en, selecciona el rango Hoja1!A1:B10

En el argumento indicador_columnas, escribe 2 (es decir, la segunda columna de la matriz)

En el argumento ordenado, introduzca FALSO

Pulse Enter

Escriba en la HOJA2 el nombre del vendedor que desea buscar e inmediatamente devolverá el valor de la ciudad

Usted puede añadir complejidad a la fórmula de acuerdo al tamaño de matriz: agregar selección por listas, etc

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Formato condicional usando conjunto de íconos

noreply@blogger.com (Diego Alzate) — Wed, 23 Sep 2009 15:33:00 +0000

El formato condicional ofrece una visualización de sus datos bien sea numéricos o de texto información gráfica adicional, resalta celdas interesantes, enfatiza valores poco usuales y visualiza datos usando barras de datos, escalas de color y conjuntos de íconos basados en criterios.

En el siguiente ejemplo, veremos cómo mostrar por medio de 3 íconos cómo se comportan las ventas de determinados empleados.

1. Escriba en una hoja de cálculo nueva, la siguiente información

2. Seleccione el rango de celdas desde B2 hasta E5

3. En la cinta de opciones Inicio, haga clic en el botón Formato condicional, seleccione "Conjunto de íconos" y ubique la opción "3 símbolos (en círculo)"

4. Automáticamente, Excel le dará un formato a ese conjunto de celdas seleccionadas de acuerdo a un criterio estándar

5. De aquí en adelante, modificaremos la condición del formato que cumpla las características nuestras. Para ello, con este rango de celdas seleccionadas, haga clic nuevamente en "Formato condicional"; y seleccione la opción "Administrar reglas"

6. En la ventana Administrador de reglas de formato condicional,
haga clic en el botón "Editar regla" y seleccione la primera opción "Aplicar formato a todas las celdas según sus valores"

7. En la lista desplegable de la opción "Estilo de formato" seleccione el ítem "Conjunto de íconos"

8. Para nuestro ejemplo de empleados y sus ventas, le diremos que coloque el ícono cuando las ventas superen los 900.000. En este caso, digite el número 900000 en el campo "Valor" que se encuentra al frente del botón respectivo. En la opción "Tipo", seleccione Número.

9. Para el caso de evaluaremos como alerta los vendedores que deberán reforzar su plan de ventas, para lo cual, tendremos las ventas superiores a 500.000 y menores que 900.000. En este caso, digite el número 500000 en el campo "Valor", y en la opción "Tipo", seleccione Número.

10. Y para el caso de se tomarán en cuenta los valores inferiores al valor digitado en la opción de alerta . Lo cual indica que no se está cumpliendo el plan de ventas programado.

11. Termine haciendo clic en Aceptar y verifique si este formato cumple con sus necesidades

Si te gustó, suscríbete al Feed RSS de Ideas de Excel y recibe nuestras actualizaciones

Video | Validación dinámica de listas

noreply@blogger.com (Diego Alzate) — Fri, 11 Sep 2009 18:51:00 +0000

El video presentado a continuación le mostrará cómo crear una validación dinámica a partir de una lista extensa y catalogada. Sin usar macros, sólo funciones de Excel.

El ejercicio consiste en crear una validación de los municipios colombianos partiendo del listado DIVIPOL.

Se usaron las funciones BUSCARV(), ESERROR(), SI(), COINCIDIR(), IZQUIERDA(), DESREF(), así como también la validación de listas y el formato condicional mediante funciones.

Descargue desde el siguiente botón el libro usado para el ejercicio.