FUNCIONES PERSONALIZADAS EN EXCEL

Excel dispone de más de 350 funciones en 13 categorías, sin embargo muchas veces deseamos funciones más específicas para nuestro trabajo y dependiendo del área laboral al que nos desempeñemos. Excel permite mediante codificación en Visual Basic crear nuestras propias funciones, las cuales por defecto son almacenadas en una 14° categoría llamada “Definida por el usuario”.

Para crear la función personalizada es necesario abrir el editor de Visual Basic desde la Ficha Desarrollador o presionando las teclas ALT + F11, se inserta un Módulo y luego un procedimiento pero con la opción de Función, se coloca el nombre de la función y finalmente el código.

image

Una función tiene la siguiente sintaxis:

image

Veremos los códigos de algunas funciones personalizadas:

  •  Función Factorial

image

Ejemplo: Factorial(4) = 24

  • Función para invertir el orden de un valor numérico o texto

image

Ejemplo: Invertir(24578) = 87542 , Invertir(ATR-2) = 2-ATR

  • Función para limpiar un valor alfanumérico

image

Ejemplo: Limpia(ABC-124, 1) = 124 , Limpia(ABC-124 , 2) = ABC- , Limpia(ABC-124 , 3) = ABC

  • Función para Concatenar un rango de valores

image

Ejemplo: 

Función Concat

  • Función para obtener las iniciales de nombres propios

image

Ejemplo: 

Iniciales

Y así podemos crear infinidad de funciones según nuestra necesidad, la próxima entrada veremos más funciones personalizadas.

FITXURE BRASIL 2014 CON EXCEL

Pelota de fútbol  Aprovechando estas fechas en las que se vive la fiebre del Mundial de Futbol, en esta entrada tenemos un fitxure del mundial hecho 100% en Excel. Consta de un Menú inicial de donde se puede acceder a las diferentes Tablas pos cada Grupo, además de una Tabla General de la 1ra Ronda y de un Cuadro donde se colocan los resultados de Octavos, Cuartos, Semifinal y Final.

Fitxure

Para cada grupo se observan datos de la sede, los partidos y el estado, con celdas para colocar los resultados de cada partido.

Grupos

Los cálculos de partidos ganados, partidos empatados, partidos perdidos, goles a favor, goles en contra, diferencia de goles, puntos totales y partidos jugados salen automáticamente y de forma fácil de acuerdo a los resultados de cada partido.

Final

Se trata realmente de una aplicación hecha a base de fórmulas y funciones para los cálculos, formas o dibujos para los vínculos entre hoja y hoja, imágenes para los estadios, mascota y logo del mundial  y formatos de celdas para el diseño general como son colores, bordes, tipo y color de fuente, etc.

Debajo ´se encuentra el vínculo para descargar ésta útil aplicación que les servirá para hacer un buen seguimiento al mundial, espero les sirva, sobre todo a los fanáticos del futbol.

Pelota de fútbol  DESCARGAR FITXURE BRASIL 2014  Pelota de fútbol

LAS TABLAS DINÁMICAS

Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las herramientas que más usuarios de Excel no usan o no lo usan correctamente. .

Las tablas dinámicas permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar los diferentes campos que formarán parte del reporte. Si todavía no lo usas, posiblemente estas perdiendo mucho tiempo en hacer reportes tediosos, cuando fácilmente se podrían hacer usando las tan prácticas Tablas Dinámicas.

Reportes flexibles

Es verdad que puedes formar muy buenos reportes con lo que ya sabes de Excel, pero imagina la siguiente situación. Ya has creado un gran reporte que compara el total de ventas en las diferentes regiones del país, pero ahora tus superiores han pedido que hagas otro reporte que compare las ventas de cada región con los tipos de productos vendidos. Y por si fuera poco, después de terminar el segundo reporte, te piden un tercero para comparar las ventas de los productos pero ahora por cada ciudad del país. Es muy probable que tengas que empezar desde cero para crear los nuevos reportes.

Afortunadamente Excel tiene la funcionalidad de tablas dinámicas que ayuda a resolver este problema. Al utilizar una tabla dinámica podrás crear los reportes sin escribir una sola fórmula, pero lo más notable será que podrás arreglar el reporte de una manera dinámica de acuerdo a tus necesidades.

El verdadero problema de las tablas dinámicas

Muchos usuarios de Excel evitan el uso de las tablas dinámicas porque parecieran muy complicadas a primera vista. Aunque las tablas dinámicas pueden parecer desafiantes, la realidad es que el problema radica en que muy pocas veces se explican adecuadamente. Realmente si nos ponemos a comparar, las funciones son más complicadas de entender que una Tablas dinámica, con lo que no quiero decir que las Tablas dinámicas reemplazan en su totalidad a las funciones, si no que dichas Tablas permiten con mucha más rapidez la presentación de un reporte o informe.

Como crear una Tabla Dinámica

Para la creación de una Tablas dinámica necesitamos una información en forma de Tabla o Base de datos es decir con un encabezado de títulos en la fila superior y con datos en celdas únicas, no se permiten celdas combinadas por ningún motivo. Un ejemplo puede ser como en la imagen:

image

Las Tablas dinámicas para armar un informe reconoce dos conceptos importantes dentro de la información: Campos (columnas del rango, tabla o base de datos), Elementos de campo ( valores sin repetición de cada fila o registro de un campo) .

En la imagen un Campo esPaís Destinatario”, y sus Elementos de campo son Argentina, Brasil, España y Reino Unido. Así mismo sucede para los demás campos.

Por ejemplo si queremos hacer un Informe del importe total vendido a cada cliente por país destinatario sólo de las categorías de productos bebidas de la base de datos mostrada, procedemos de la siguiente manera:

1. Colocándonos en la primera celda de la Base de datos, vamos a la ficha Insertar, y presionamos el icono de Tabla dinámica y saldrá un cuadro de dialogo donde aparecerá por defecto el rango de nuestra información, presionamos aceptar y veremos en una hoja nueva el área donde colocaremos nuestros campos para formar el informe.TD2

2.. Para colocar los campos dentro del informe tenemos  una lista de Campos que se encontrará en la parte derecha de la hoja de cálculo, el acomodo de los campos será de acuerdo a la siguiente imagen animada.

TD3

3. Finalmente para una mejor presentación debemos usar las herramientas de Tabla dinámica que sale inmediatamente en la banda de opciones cuando se inserta la Tabla dinámica, dichas herramientas están formadas por dos fichas: Opciones y Diseño. Vea la imagen animada siguiente.

TD4

4. Siempre que cambiemos alguna información en el Origen de datos, para poder ver los resultados en la Tabla dinámica, debemos ir a la ficha Opciones, y presionar el botón Actualizar.

TD5

En una próxima entrada veremos más propiedades y herramientas de Tablas dinámicas e inclusive con sus propios gráficos, que por que cuyos valores de series provienen de una Tabla dinámica, se denominan Gráficos dinámicos.

GRAFICOS ESPECIALES PARA MOSTRAR INDICADORES

En el mundo empresarial son fundamentales los indicadores, también conocidos como KPI’s para decidir el rumbo de la empresa. Los gráficos son una inestimable ayuda para ello, pues facilitan al usuario la transición entre el “conocimiento” y la “toma de decisiones”.

Los gráficos que tiene Excel son: de columnas, barras, líneas, circulares, de dispersión, área, etc. Pero también podemos combinar éstos gráficos con formas y con ciertos cálculos con fórmulas matemáticas y funciones de Excel para obtener gráficos especiales que nos interpretarán mejor los indicadores. Realmente Excel nos sorprende con la cantidad de variantes en gráficos que se puede crear con él, entre tantos podemos nombrar los siguientes:

GRAFICO TIPO VELOCÍMETRO: Un ejemplo de tipo de gráfico que ayuda a interpretar correctamente y de un vistazo el conocimiento que se infiere a la información, es el gráfico de tipo velocímetro. Se trata de un gráfico que compara dos magnitudes, una de las cuales representa un objetivo a alcanzar y la otra el valor realmente conseguido hasta el momento. Existen varios diseños como el que tiene un dial lineal y el que tiene un dial circular.

image

GRAFICO TIPO TERMÓMETRO:  Este tipo de gráficos nos permiten visualizar fácilmente indicadores de cumplimiento, medir el alcance que se obtuvo en los presupuestos diseñados en el área de ventas, costos, contabilidad compras etc., ofreciéndonos una percepción gráfica muy rápida de la situación que se tiene.

image

GRAFICO TIPO ECUALIZADOR: Este tipo de gráficos nos permiten comparar valores actuales y las metas u objetivos trazadas, como por ejemplo volúmenes de ventas mensuales, semanales, etc. 

image

GRAFICO TIPO SEMAFORO: Estos gráficos nos dan una alarma visual y rápida sobre el estado actual de la empresa en base a un objetivo trazado. Nos indican con tres colores verde, amarillo y rojo si el indicador analizado esta por encima de la meta, esta dentro de la meta o por debajo de ella.

image

La aplicación más importante de estos gráficos es para el diseño de Tableros de mandos, Dashboards y Balanced Scorecard como parte del Plan Estratégico de la empresa.

LA FUNCIÓN BUSCARV EN EXCEL

La función BUSCARV es una de las funciones más utilizadas en Excel y pertenece a la categoría de funciones de búsqueda y referencia. Esta función nos permite hacer una búsqueda de un valor dentro de la primera columna de un rango de datos.

Su sintaxis es la siguiente:

=BUSCARV(valor_buscado,matriz_buscar_en,indicador_columna,[ordenado])

A continuación haremos una revisión detallada de cada uno de sus argumentos.

Argumentos de la función BUSCARV

La función BUSCARV tiene cuatro argumentos, los primeros tres son obligatorios y el último es opcional. Para poder hacer un buen uso de la función debemos tener bien claro sobre el significado de cada argumento.

Valor_buscado: Como primer argumento de la función BUSCARV debemos indicar el valor que estamos buscando. Ya sea que proporcionamos el valor directamente o colocamos una referencia de celda en donde se encuentra el valor.

image

Debemos tener en cuenta que el valor del primer argumento de la función será buscado siempre en la primera columna de la tabla de datos. No es posible buscar en una columna diferente que no sea la primera columna.

Matriz_buscar_en: El segundo argumento de la función indica la totalidad del rango que contiene los datos. En este rango es importante asegurase de incluir la columna que vamos a necesitar como resultado.

image

Si la tabla tiene encabezados, lo más recomendable es excluirlos del rango para evitar que la función considere a los títulos dentro de la búsqueda.

Indicador_columna: El tercer argumento indica la columna que deseamos obtener como resultado:

image

[Ordenado]: El último argumento de la función es opcional, pero si no proporcionamos un valor, la función BUSCARV hará una búsqueda aproximada. Para que la función realice una búsqueda exacta debemos colocar el valor FALSO o cero (0) y obtendremos como resultado el valor de la columna que hayamos indicado.

image

En esta imagen se puede ver que la función BUSCARV encuentra el valor “BR” en la primera columna pero nos devolverá el valor de la tercera columna tal como lo indicamos en los argumentos de la función. Si la función BUSCARV no encuentra el valor en la columna uno, devolverá el error #N/A.

LAS FUNCIONES SUMA , SUMAR.SI Y SUMAR.SI.CONJUNTO EN EXCEL

En la hoja de cálculo Excel la función más usada es la SUMA, que permite sumar uno o varios rangos de celdas. Pero además de la SUMA, Excel cuenta con otras dos funciones más que permiten sumar celdas que cumplen uno o varios criterios que nos servirán para construir tablas resumen.

1. SUMA

La función SUMA permite sumar todas las celdas de un rango, por defecto Excel selecciona las celdas que se encuentran por encima o a la izquierda de la celda activa. Se puede ejecutar directamente con el símbolo Σ llamado autosuma.

Si los rangos a sumar son más de uno, y se encuentran en distintas áreas de la hoja, se seleccionan con la tecla CTRL sin dejar de apretar la tecla cada vez que se selecciona un rango nuevo.

Si las celdas que se desean sumar están en otra hoja, una vez seleccionadas pulsar INTRO, de esta forma Excel devuelve el resultado a la celda de la que partíamos.

2. SUMAR.SI

La función SUMAR.SI suma los valores de un rango que cumplen un determinado criterio o condición. La sintaxis es la siguiente:

=SUMAR.SI( rango, criterio, rango_suma )

Rango: es el rango de celdas que desea evaluar.

Criterio: es el criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar. Siempre va entre comillas,

Rango_suma: son las celdas que se van a sumar. Si el rango de la suma coincide con el rango de evaluación se puede omitir.

Veamos algunos ejemplos:

1. En la siguiente hoja se suman las cantidades correspondientes a los productos A y B. Los resultados son 34 y 24 respectivamente.

clip_image003

  • El rango de celdas que se evalúa es A1:A5.
  • El criterio o condición es el texto “Producto A” y “Producto B”.
  • Rango_suma son las celdas que se van a sumar, en este caso es el rango B1:B5.

2. La siguiente hoja muestra el número de cajas que han entrado en un almacén en marzo. Se necesita averiguar a cuanto asciende la suma de entradas superiores a 150 unidades.

clip_image005

  • El rango de celdas que se evalúa es B2:B14.
  • El criterio o condición es que la cantidad sea superior a 150: “>150″.
  • Rango_suma son las celdas que se van a sumar, en este caso el rango suma coincide con el rango de evaluación, por eso motivo se omite.

3. SUMAR.SI.CONJUNTO

La función SUMAR.SI.CONJUNTO permite sumar los valores de un rango que cumplen varias condiciones o criterios. La sintaxis correcta es la siguiente:

=SUMAR.SI.CONJUNTO( rango_suma, rango criterios 1, criterio1, rango criterios 2, criterio2, …… )

Rango_suma: son las celdas que se van a sumar. Es obligatorio ponerlo, aunque coincida con el rango de criterios.

Rango criterios 1: es el primer rango de celdas que desea evaluar.

Criterio 1: es el primer criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar. Siempre va entre comillas.

Se pueden especificar hasta 127 rangos de criterios con sus correspondientes condiciones.

El siguiente ejemplo muestra la suma total de entradas en el almacén comprendidas entre 100 y 350 unidades.

clip_image008

COMO USAR LAS TABLAS EN EXCEL

Las tablas no son más que un conjunto de filas y columnas que contienen datos relacionados y que son manejados de manera independiente por Excel. Las tablas son una herramienta muy poderosa que debes aprender a utilizar adecuadamente.

¿Qué es una tabla en Excel?

Las tablas son la manera en que Excel identifica un rango de celdas y de esa manera sabe que la información contenida en ellas está relacionada. La primera de las filas de una tabla siempre contendrá los encabezados de columnas y el resto de las filas contendrán los datos.

El utilizar tablas es muy conveniente porque Excel se hará cargo de los detalles del manejo de las tablas, como facilitar el agregar nuevas filas o eliminarlas, lo cual te permitirá enfocarte más en el análisis de los datos.

Creación de una tabla

Para crear una tabla en Excel todo lo que se debe hacer es seleccionar el rango de celdas que contiene los datos y encabezados de columna y oprimir el botón Tabla que se encuentra dentro de la ficha Insertar.

image

 

 

 

A continuación mencionaré algunos beneficios de utilizar las tablas de Excel.

Cambiar el estilo de una tabla

Excel 2010 contiene algunos estilos de tabla predefinidos de los cuales podrás elegir alguno para aplicarlo a tu tabla. Solamente selecciona cualquier celda que se encuentra dentro de la tabla y cambia su estilo seleccionado la opción adecuada dentro del grupo Estilos de tabla que se encuentra dentro de la ficha Diseño de las Herramientas de tabla.

Si no te agrada ninguno de los estilos mostrados puedes hacer clic en la opción Nuevo estilo de tabla para crear un nuevo estilo.

image

Resaltar filas alternas de una tabla

Cuando creas una nueva tabla Excel aplica un fondo especial a las filas impares. Si por alguna razón no deseas que se aplique este formato puedes deshabilitarlo con la opción Filas con bandas dentro de la ficha Herramientas de tabla.

image

Filtros y opciones de ordenamiento

Cada tabla que creas en Excel ya trae consigo filtros para cada columna así como opciones de ordenamiento para cada columna.

image

Referencias estructuradas de una tabla

Una de las ventajas más importantes de una tabla de Excel es que, en lugar de utilizar las referencias de celdas como las conocemos, podemos utilizar referencias estructuradas las cuales hacen uso del nombre de la tabla así como de los nombres de las columnas para hacer referencia a los datos. image

 

Al igual que con las referencias tradicionales, las referencias estructuradas se actualizan automáticamente al insertar nuevos datos en la tabla o removerlos.

Crear columnas calculadas fácilmente

Una vez que sabes utilizar las referencias estructuras será muy sencillo crear columnas calculadas utilizando los nombres de las columnas. Por ejemplo:

image

Fila de totales de una tabla

Se puede agregar totales a una tabla con tan solo marcar la caja de selección Fila de totales:

image

 

 

 

 

Y por si fuera poco, podemos modificar la Fila de totales especificando la operación que deseamos aplicar.

image

TIPS PARA MAYOR EFICIENCIA EN EL MANEJO DE EXCEL

Imagen

Hola a todos, empezamos con el primer contenido sobre ésta maravillosa herramienta: “EXCEL”.

Creo que todo el mundo está de acuerdo con que el tiempo es dinero, lo que quiere decir que mientras en menos tiempo hagamos una tarea, nos dará más tiempo en hacer otra y otra, y si éstas generan dinero, nuestros ingresos serán mejores. Esto se cumple en todo tipo de trabajo, negocio o empresa, y se resume en Eficiencia. Excel tiene todos las herramientas y procedimientos necesarios para lograr una mayor eficiencia en el trabajo, en ésta parte les diré, que con la experiencia que tengo con el Excel, que se debe dominar para obtener mayor rapidez en el manejo del programa. Los puntos más importantes y que nos pueden ahorrar, minutos, horas, días y hasta semanas para hacer un reporte:

  1. Los Métodos Abreviados: Hay que dominar los principales atajos por      teclado como son CTRL + C para copiar, CTRL + V para pegar, CTRL + X para cortar, CTRL + (-) , CTRL + (+) para eliminar e insertar celdas, filas o columnas respectivamente, SHIFT + CRTL + Flechas direccionales para seleccionar un rango de datos, etc. Las combinaciones con la tecla ALT para usar las combinaciones que aparecen en la banda de opciones también es importante. Todo esto si se llega a dominar nos ahorrará a partir del 50% del tiempo que nos llevará comúnmente en hacer alguna tarea. Haz click aquí para ver los principales métodos abreviados.
  2. El ingreso de datos mediante Auto llenado: En Excel se puede aprovechar el auto llenado para datos que tienen una sucesión como son las      fechas, los días, los meses, los trimestres y los números. Mediante procedimientos sencillos podemos colocar los meses del año escribiendo sólo el mes de Enero en una celda y jalando con el mouse hacia cualquier dirección se auto llena con los demás meses. Lo mismo pasa cuando queremos enumerar datos del 1 al 100 por ejemplo. Ahorraremos mucho tiempo haciendo este procedimiento que estar llenando los datos uno por uno.
  3. Dar Formato como Tabla: Este formato se le da a rangos de datos con encabezados completos, a base de datos o tablas con formato simple, éste formato es mucho más automático, ya que permite dar bordes y colores predefinidos por Excel, además de forma automática se añaden filas o columnas tan sólo escribiendo un nuevo nombre de campo o un nuevo registro. Ahorra tiempo en la elaboración de tablas de forma manual.
  4. Las referencias Relativas, mixtas y absolutas: La diferencia entre saber y no saber estos conceptos es simplemente Tiempo, podemos ahorrarnos mucho tiempo si usamos las refrencias en sus distintas formas dentro de una fórmula o función. Las referencias relativas cambian al ser copiadas o arrastradas tanto en filas como en columnas, las mixtas sólo en fila o columna y las absolutas no cambian. Entiendase por referencia el nombre de una celda como por ejemplo A1, que por defecto siempre será relativa, si manejamos la tecla de función F4, intercalamos el simbolo de dolar($) para poder fijar, filas y columnas obteniendo una refrencia absoluta: $A$1, o fijar una de ellas, dependiendo de eso podemos obtener referncias mixtas: $A1 donde solo se fija la columna o A$1 donde solo se fija la fila. El objetivo de las referencias es poder llenar un cuadro, tabla o plantilla con la menor cantidad de fórmulas posibles, las cuales solo al arrastrarlas podamos obtener millones de cálculos en el menor tiempo.
  5. Sintaxis de una función: Saber la sintaxis de una función nos ayuda a entender que todas las funciones desde la más simple a la más compleja tienen una misma estructura: =nombre_función(argumento1, argumento2, ….argumentoN) . Es mejor ingresar la función por medio de su sintaxis de forma directa en la celda a hacerlo por medio del asistente de fórmulas. Al ingresar la función o combinación de funciones en una celda, Excel nos muestra una lista de funciones y nombres para escoger y también aparece una etiqueta debajo que nos guía para sabe ren que parte del a sintaxis estamos.
  6. Usar Nombres de Celdas y Rangos: Este punto es importante en el cálculo, es mejor usar un nombre de rango dentro de una fórmula y función que estar seleccionandola en todo momento cuando lo necesite en el cálculo. Los nombres de rangos se dan a través del Cuadro de nombres y si se tratasé de dar nombres a una tabla con muchos campos, es mejor usar la herramienta Crear desde la selección del grupo Nombres definidos de la ficha Fórmulas. Esta funcionalidad permite que los nombres de los rangos sean igual a los titulos del encabezado o fila superior de la tabla.