Cómo hacer un Dashboard económico-financiero con Excel (1)

Quizá el lector ha llegado a este artículo ávido de formación e información sobre un tema que ya conocía de su existencia como es el de los Dashboards o Cuadros de Mando económico - financieros. Quizá el lector ha llegado hasta aquí por la curiosidad que le genera un tema que no conocía, pero que cree que puede ayudarle en su desempeño profesional en el área eco-fin de la empresa donde trabaja... o de su propia empresa.

Parte de un Dashboard creado en Excel

En cualquier caso, vamos a intentar construir esta serie como corresponde... desde el principio.

¿Que es un "Dashboard"?

En el ámbito empresarial, un "Dashboard" (traducido de forma literal sería "Tablero de a bordo" por su parecido a un salpicadero con velocímetros y otros indicadores gráficos) es un informe que proporciona indicadores de ciertos procesos de negocio y que cumple las siguientes condiciones:

1. Muestra gráficamente los datos, ayudando al destinatario del informe a enfocar su atención en posibles desviaciones, tendencias, comparaciones y otros comportamientos de algunas métricas de carácter económico y financiero (por ejemplo).
2. Muestra sólo los datos relevantes con respecto a un objetivo general establecido en un Plan de Empresa o en un Plan Estratégico.
3. Muestra conclusiones objetivas para inferir en el lector la construcción de su propio análisis ponderado por matices y factores que van más allá de la frialdad de los números.

Dashboard creado en Excel

Si bien lo que acabamos de ver es la cara visible, tenemos que señalar que detrás de esta página o "tablero de a bordo" debe haber toda una organización o estructura de hojas con distintas funciones. Aprenderemos la forma de desarrollar todo ello más adelante, pero un adelanto de esa estructura se puede ver en el artículo de este mismo Blog: "Buenas prácticas: Estructura y tipos de hojas en Excel"

Así pues, en adelante, cuando hagamos referencia a "Dashboard", se supone que estamos teniendo en cuenta también la estructura que lo soporta y no sólo la página que contiene los KPI's.

¿Cómo empezar?

Se dice que lo ideal cuando nos disponemos a desarrollar una aplicación en Excel es intentar aplicar el principio de Pareto de la siguiente forma:

• 80% del tiempo planificando bolígrafo en mano
• 20% del tiempo desarrollando en Excel

A mi me parece algo exagerado, pero quizá sí sería ajustado considerar una relación de 60% - 40%  o incluso de 70%-30%.

Vamos a ocuparnos de esa planificación que debería ocuparnos entre el 60% y el 80%. Para ello y, de forma contraria a lo que mucho usuarios hacen, DEBEMOS EMPEZAR POR EL FINAL. Es decir, lo primero que debemos decidir al abordar el desarrollo de un cuadro de mando, son todas aquellas cosas que tienen que ver con el final. Estas cosas son:

Planificación

1. Definir el objetivo general que a final queremos conseguir.
2. Definir quienes serán los destinatarios finales de nuestro trabajo, cómo lo utilizarán, el nivel de detalle requerido, etc.
3. Definir los indicadores que al final son los que nos guiarán en la toma de decisiones.
4. Definir las fuentes de datos que "alimentarán" a nuestro Dashboard cuando ya esté terminado.
5. Definir las dimensiones (criterios por los que la información se mostrará) y los filtros (formas de segmentar o acotar los datos y la información.

Es absolutamente necesario tener TODOS estos puntos bien definidos y expresados por escrito, puesto que en el proceso de desarrollo del Dashboard vamos a necesitar una visión de conjunto.

Como puede ver el lector, no hemos decidido nada respecto de nuestro libro de Excel, ni tan siquiera hemos hablado de Excel. Para ello, aun es pronto. En el artículo siguiente desarrollaremos con más detalle cada uno de los 5 puntos enumerados anteriormente, poniendo también algunos ejemplos.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Detectar valores duplicados en una columna de Excel

De forma distinta al artículo anterior, en el que se trataba de "Detectar diferencias entre 2 columnas en Excel", vamos en esta ocasión a aprender un "truco" para detectar valores repetidos más de una vez, dentro de una misma columna.

Insistimos en que vamos a "detectar", no a eliminar duplicados. Para esto último, Excel tiene una herramienta que podemos encontrar en la ficha Datos / grupo Herramientas de Datos / Quitar duplicados

Además, y de la misma forma que lo hacíamos en el artículo anterior, lo vamos a hacer con una utilización avanzada de "Formato condicional". Es decir, sin necesidad que crear una columna auxiliar con fórmulas, sino que incluiremos una función "dentro" del formato condicional.

Detectar un valor repetido más de una vez en una columna (forma sencilla)

Si sólo queremos saber los valores que están repetidos (independientemente del nº de veces), se puede conseguir directamente utilizando una de las opciones de Formato Condicional (después de seleccionar el rango donde suponemos que habrá valores repetidos):

Ficha Inicio / Formato condicional / Resaltar reglas de celdas / duplicar valores

Detectar un valor repetido x veces en una columna (forma avanzada)

Vamos a aprender a conseguir lo mismo que antes, pero de una forma algo más avanzada, lo que nos permitirá tener más control en algunos casos. Este mayor control consiste en que, no sólo podremos saber  los valores que están repetidos más de una vez, sino que podemos discriminar aquellos que están repetidos más de x veces sólamente.... o bien aquellos que están repetidos exactamente x veces.

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Elegimos, por ejemplo, la columna 2 (el rango C3:C9) porque queremos saber si en esa columna ha hay algún valor repetido más de una vez. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!

Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula: 

El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:

El motivo de utilizar la función =CONTAR.SI ($C$3:$C$9;C3)>1 es que conseguimos que el formato condicional reconozca en cada celda si el valor contenido en ella está repetido más de una vez (por ello se escribe > 1 al final) en todo el rango C3:C9.

Vemos que el rango C3:C9 se escribe con forma de referencia absoluta: $C$3:$C$9;C3 , sin embargo, la referencia C3 NO se debe escribir entre "signos dólar". El motivo de que la celda C3 deba ser una referencia relativa es posibilitar al formato condicional que se aplique a todo el rango (el formato condicional, aunque no lo hayamos puesto, va leyendo celda a celda aumentando un nº de fila: C3, C4, C5, C6...etc.).

Es muy fácil intuir que si queremos detectar sólo los valores que están repetidos más de dos veces, deberíamos cambiar >1  por  >2 ..... y las demás posibilidades son fáciles de imaginar. Por ejemplo, podríamos marcar aquellos valores que estén repetidos exactamente 3 veces, tan sólo cambiando > por =3.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Detectar diferencias entre 2 columnas en Excel

Es muy frecuente que los usuarios de Excel necesiten detectar diferencias entre dos columnas o tablas de Excel. Esto se puede hacer de varias formas, pero en este caso, vamos a hacer que la detección se haga mediante "formato condicional" y que las diferencias encontradas en una columna B respecto de una columna A se marquen con un color de fondo distinto.

Nos podemos encontrar con varios casos o varias necesidades distintas:

Detectar diferencias entre pares de celdas de una misma fila

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Es decir, seleccionamos el rango C3:C9 porque queremos saber si en esa columna ha cambiado algún valor comparado con su pareja en la columna 1. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!

Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula:  

El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:

...viendo que han quedado resaltadas las celdas de la columna 2 que suponen una diferencia respecto de sus parejas en la columna 1.

Detectar diferencias en cualquier parte de las columnas

En al caso anterior, sólo se detectaban diferencias entre parejas de celdas. Aunque en la columna 2 hubiesen los mismos valores que en la columna 1, tan sólo con cambiarlos de orden, ya se detectaban diferencias.

Ahora necesitamos detectar si en la columna 2 aparece algún valor que no esta en la columna 1, no importándonos la coincidencia de orden de los demas valores. Dicho sobre un ejemplo, en la siguiente tabla, necesitamos que aparezcan resaltados los valores "S" y "P" de la columna  2, puesto que no existían en la columna 1. Los demás valores de la columna 2, aunque en distinto orden, sí existían en la columna 1:

Para conseguir que los valores "S" y "P" de la columna  2 queden resaltados, debemos seleccionar los datos de la columna 2 de la misma forma que lo hicimos en el otro ejercicio y vamos al mismo cuadro de diálogo. Posteriormente, la fórmula a introducir sería:

...y el resultado...

Explicación para la función BUSCARV:

El primer argumento de la función BUSCARV es la primera celda de la columna 2 donde se van a resaltar las diferencias (¡¡NO debe ser una referencia absoluta. Es decir, no debe tener los sígnos de dólar!!). El segundo argumento es el rango de celdas donde se buscarán las diferencias en la columna 1. El tercer argumento es un 1 porque necesitamos que se busque en la única columna del rango (la B). El cuarto argumento indica que se buscará una coincidencia exacta aunque no esté la columna B ordenada.

Explicación para la función SI.ERROR:

El primer argumento de la función SI:ERROR es la propia función BUSCARV. Esto es, SI:ERROR evaluará lo que se obtenga con BUSCARV. El segundo argumento de la función SI:ERROR es un valor lógico (VERDADERO = 1) que obtendremos si BUSCARV da un error (si NO encuentra lo que busca y por lo tanto hay una diferencia).

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Extraer una parte del texto de una celda, con VBA

Hace unos días recibí un e-mail de una lectora del Blog, preguntando cómo podría conseguir mediante código VBA (una macro) extraer una parte del texto de un rango de celdas en una columna. En su mensaje, me proponía un código que ya estaba utilizando, pero quería perfeccionarlo para tener una mayor utilidad.

El código aportado por mi lectora era:

Sub Extraer()

Final = Range("A65536").End(xlUp).Row

For x = 2 To Final

Cells(x, 2) = Mid(Cells(x, 1), 5, 4)

Next

Lo que hacía cada una de las líneas era:

1. Determinar a cuantas celdas se le debe aplicar la "Extracción"  (2ª línea del código)
2. Iniciar un bucle con un número de repeticiones igual al número de celdas hallado en el punto anterior, asignando en la primera vuelta del bucle, el valor x = 2
3. Insertar en la celda B2 un texto que se extraerá del texto de la celda A2 (Cells (x,2), empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
4. Comienza otra vuelta de bucle en el que inserta en la celda B3 un texto que se extraerá del texto de la celda A3, empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
5. ...y así sucesivamente...

En definitiva, que del texto  FRANCISCO-01   extraería la cadena de texto   CISC

Pero mi lectora quería algo diferente. Lo que pretendía era tener la posibilidad de "extraer" un cierto número de caracteres contando desde la derecha. Ese "cierto número de caracteres" debería ser elegido previamente por el usuario, mediante un cuadro de diálogo que se obtendría en pantalla al presionar un botón (por ejemplo). 

Pues bien, le ofrecí la siguiente solución:

Sub RecortarDerecha()
NCaracteres = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduce el número de caracteres a recortar", "Nº caracteres a recortar", "Nº caracteres a recortar", 1000, 1000)
Final = Range("A65536").End(xlUp).Row
For x = 2 To Final
Cells(x, 2) = Right(Cells(x, 1), NCaracteres)
Next
End Sub

Respetando parte del código que teníamos en la propuesta inicial, una de las soluciones posibles se trata de:

1. Introducir la variable NCaracteres, que representaría a lo introducido por el usuario en un "Inputbox" (2ª línea del código). Podemos recordar cómo se introduce un cuadro de introducción de datos o "Inputbox" en el siguiente artículo: "Introducción de datos en Excel con un cuadro de diálogo"
2. Cambiar la función MID por RIGHT  (recordemos que ahora se pretende extraer desde el primer caracter de la derecha siempre).
3. Cambiar los dos últimos argumentos que tenía la función MID, por un sólo argumento: el número de caracteres que queremos recortar desde el primer caracter de la derecha (NCaracteres).

Podemos comprobar el funcionamiento de todo ello, descargando el siguiente archivo Excel:

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Buenas prácticas: Estructura y tipos de hojas en Excel

Nos disponemos a diseñar desde el principio una hoja de cálculo. Hacemos doble click en el icono de Microsoft Excel. Aparecen en pantalla las tres hojas vacías que por defecto proporciona la aplicación. Tenemos todo un mundo por delante. Todo por hacer. Hay que poner los cimientos.

Es imprescindible que no vayamos improvisando la forma de estructurar nuestro libro de Excel (la cantidad, orden y función de cada una de las hojas), sino que debemos ceñirnos en alguna medida, a una estructura óptima que está probada y que nos beneficiará a largo plazo en nuestro trabajo con Excel.

Es decir, con una buena estructura de hojas en nuestro libro, conseguiremos...

• Que nuestro libro de Excel sea escalable sin necesidad de copiar y pegar hojas para añadir datos del próximo mes, año, etc.
• Que no sea necesario editar y modificar fórmulas durante la utilización de nuestro libro de Excel. Una vez terminado el diseño de nuestro libro nunca se debe editar o modificar una fórmula.
• Que otros usuarios puedan utilizar nuestro libro de Excel de forma intuitiva y rápida. Es decir, no perdiéndose en una maraña de hojas sin orden ni objetivo claro.
• Que nosotros mismos, como desarrolladores del libro de Excel, podamos mantenerlo o ampliarlo  en un futuro en caso de ser necesario. Si no hay una estructura clara, no seremos capaces de entender nuestro propio trabajo.
• Que nuestro libro de Excel sea seguro y exento de errores y sorpresas
• Que nuestro libro de Excel aporte el máximo valor con los mínimos recursos

Basándonos en los consejos del apartado BPMS 1-3 Sheet content, del libro Best Practice Spreadsheet Modelling Standards & Conventions (Version 6.1) de la Spreadsheet Standards Review Board (SSRB), pasamos a orientar al lector de cuál sería la estructura y los tipos de hojas que deben estar presentes en cualquier libro de Excel que pretenda gestionar datos, información y conocimiento de forma eficiente.

Tipos de hojas en una aplicación basada en Excel

1. HOJA DE CONFIGURACIONES, TABLAS AUXILIARES Y TABLAS DE BÚSQUEDA

Llamadas "Lookup Sheets" por la SSBR, pueden ser hojas que contienen:

• Datos y supuestos que se asumen de partida y no son modificables por el usuario ("Non-Assumptions"). Por ejemplo, una serie de meses que será utilizada como títulos para la realización de gráficos posteriormente o como origen de datos para otras hojas.
• Datos y supuestos que pueden ser modificados ("Assumptions") . Por ejemplo, un dato que debe ser posible modificar por el usuario sería el beneficio exigido en un Plan de Viabilidad Económico-Financiero.

2. HOJA DE PORTADA

Como su nombre indica, debemos dotar al libro de una hoja al principio en la que se exprese:

• El nombre de la aplicación
• Notas sobre la aplicación:
• Descripción del contenido de la aplicación
• Instrucciones generales de uso: ¿por dónde empezar?
• Advertencias y peligros de uso

3. MENÚ GENERAL, TABLA DE CONTENIDOS O ÍNDICE

Debe haber una hoja que facilite al usuario ir adonde lo necesita. La hoja debe contener botones o bien enlaces mediante hipervínculos y estos deben contener iconos o texto descriptivo en cada opción.

4. PORTADA DE SECCIÓN O SUBMENÚ

Si bien decíamos que es conveniente la existencia de un menú general, éste no debe contener demasiados items, sino que es conveniente estén presentes los más generales. Cada uno de ellos, dirigirá al usuario a otros SUBMENÚS o  PORTADAS DE SECCIÓN.

5. HOJA CON UN ESQUEMA VISUAL DEL MODELO

Siendo fieles a las recomendaciones de la SSBR, es conveniente tener una hoja donde de forma esquemática y visual podamos, desde un principio, ver cuál es el flujo de datos e información a lo largo de las distintas hojas. Esto ayudará al diseñador de la aplicación, pero también se puede poner a disposición del usuario mediante algún botón o enlace.

6. HOJAS EN BLANCO

La SSBR, concibe que los libros de Excel pueden imprimirse de forma íntegra posteriormente (algo que, sinceramente, pienso que es muy dudoso) y que cada hoja tendrá una salida por impresora en determinado orden. Por ello, y al modo de algunos libros que dejan hojas en blanco entre secciones o capítulos, tiene justificación esta recomendación. El lector decidirá si esto es conveniente para su libro de Excel.

7. HOJAS DE INTRODUCCIÓN O IMPORTACIÓN DE DATOS

Llamadas "Time Series Assumptions sheets" por la SSBR, son las hojas donde recibimos los datos de un ERP u otro programa (en el caso de que los datos ya estén generados y sean importados), o bien las hojas donde vamos introduciendo los datos (en al caso de que se generen día a día)

8. HOJAS DE ANÁLISIS E INFORMES

Llamadas "Time Series Output Sheets" por la SSBR, son las hojas que combinan los datos de las "Time Series Assumptions sheets" para ofrecerlos de forma comprensible al usuario como informes para ver o imprimir, pero NO para introducir o importar ningún dato.

9. HOJAS DE GRÁFICOS O CUADROS DE MANDO

Llamadas "Chart Sheets" por la SSBR, son hojas que transmiten conocimiento e información de alto valor mediante gráficos y cuadros de mando intuitivos. Están orientadas a la toma de decisiones y a personal que no necesariamente tiene conocimientos especializados, por lo tanto deben tener la cualidad de transmitir bien lo que se quiere expresar. En este tipo de hojas, el usuario nunca debe tener la posibilidad de introducir datos, aunque SI deberían ser hojas interactivas mediante controles especiales (botones de opción, listas desplegables, cuadros de lista, etc.)

Si ordenamos todo esto en una pirámide que exprese el flujo de información de forma correcta, podría resultar una imagen como la mostrada al principio de este artículo.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Ordenar una tabla de Excel por varios campos a la vez

Muy pocos conocen este truco o "rincón" de Excel y sin embargo estamos hablando de una herramienta que es utilizable en uno de los ámbitos más populares dentro de la utilización de Excel:  el manejo de grandes cantidades de datos en forma de tabla.

Se trata de la posibilidad de ordenar una tabla aplicando criterios a varios campos simultáneamente. Casi todos los usuarios de Excel conocen los autofiltros que se pueden aplicar a una serie de datos dispuestos en forma de tabla (y también a lo que se llama formalmente una "Tabla de Excel")...

pero sólo es posible aplicar un filtro a la vez y sobre un campo. Al intentar aplicar un segundo filtro, el primero desaparece.

No es muy conocido que es posible aplicar varios filtros simultáneamente y que esto se puede conseguir seleccionando la opción Ordenar por color / Orden personalizado... que hay en los autofiltros. 

Esta ópción de los menús de autofiltros nos llevan a obtener el siguiente cuadro:

...en el que podemos hacer uso de distintos niveles de filtros de forma simultánea seleccionando  las columnas por las que queremos ordenar, el objeto que será ordenado (valores, colores, etc.) y el criterio  de ordenación. Cada vez que queramos agregar un nivel de ordenación, lo hacemos desde el botón de arriba a la izquierda "Agregar nivel".

Por ejemplo, quedaría así el cuadro para ordenar 2 campos distintos simultáneamente con criterios distintos:

... y así quedaría la tabla ordenada:

Es entendible que no sea muy conocida esta opción, puesto que no es muy intuitiva la forma de acceder a ella. Recordemos que el menú del autofiltro era... Ordenar por color / Orden personalizado...  y lo de "Ordenar por color" despista bastante.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

La función INDICE en Excel

La función INDICE proporciona un valor elegido de una matriz. Dicho valor será el que ocupe un nº de fila especificado y se encuentre en un número de columna también especificado. Podemos decir que la sintaxis más corriente de la función INDICE es:

=INDICE (matriz ; núm_fila ; [núm_columna])

Por ejemplo, en la siguiente imagen, vemos a la función INDICE eligiendo el país que ocupa la 4ª fila de la matriz (ver barra de fórmulas):

...como se puede observar, se puede omitir el 3er argumento cuando la matriz sólo tiene una columna.

Así pues, siempre que sepamos el número de fila que ocupa, podemos obtener el valor de una celda de una matriz.

Otra de las aplicaciones de la función INDICE, es trabajar en combinación con la función COINCIDIR. Con ello, conseguimos una utilidad similar e incluso superior a la que nos ofrecen las funciones BUSCARV Y BUSCARH, puesto que el valor que obtenemos (anidando las funciones INDICE y COINCIDIR) puede estar a la izquierda o por encima del valor buscado (a diferencia de lo que ocurre con las funciones BUSCARV y BUSCARH ). Podemos ver todo esto en artículo ("Las funciones INDICE y COINCIDIR combinadas para búsquedas en Excel") de este mismo Blog.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Las funciones INDICE y COINCIDIR combinadas para busquedas en Excel

Los que conocen la función BUSCARV (o su homóloga BUSCARH) saben que tiene una limitación (a pesar de ser de las más utilizadas en Excel). Ésta estriba en la incapacidad de obtener valores en una columna que esté más a la izquierda que la columna donde está el valor buscado. Es decir, no podemos poner un numero negativo en su tercer argumento núm_columna.

Esta contrariedad es salvable a costa de tener que anidar la función COINCIDIR dentro de la función INDICE, pero nos puede sacar de muchos apuros.

Tengamos esta tabla como referencia:

Supongamos que necesitamos buscar el nombre de un trabajador a partir de su fecha de nacimiento. Esto es, introducir la fecha en una celda y que en otra, nos apareciera el nombre de forma automática. Si bien con BUSCARV no sería posible hacerlo al estar la columna de búsqueda (Fecha nacimiento) más a la derecha de la columna objetivo (ID Nombre trabajador), si sería posible combinando INDICE y COINCIDIR de la forma que vemos en la imagen de la derecha.

En la barra de formulas hemos escrito las funciones anidadas, donde el resultado lo proporciona la función INDICE, eligiendo entre la columna "ID Nombre trabajador", aquel que está situado en la 6ª fila.

Para no tener que editar constantemente la fórmula para modificar el segundo argumento de la función INDICE con un nuevo nº de fila cada vez que queramos buscar otro trabajador con otra fecha de nacimiento, en su lugar hemos introducido la función COINCIDIR... que nos proporciona ese nº de fila que ocupa la fecha de nacimiento que introducimos de forma más cómoda y sostenible en la celda C13.

La función COINCIDIR tiene tres argumentos:

=COINCIDIR (valor_buscado;matriz_buscada;[tipo_de_coincidencia])

Valor_buscado = es la celda C13 porque allí escribiremos la fecha que debe buscar la función COINCIDIR.

Matriz_buscada = es la matriz donde se buscará la fecha introducida en C13

[Tipo_de_coincidencia] = Es opcional (por eso no lo vemos en la imagen anterior). Si se pone el número 1 o se omite, al buscar la fecha, si no encontrara una coincidencia exacta, tomaría la más cercana por abajo. Si se pone un 0, obligamos a que haya una coincidencia exacta. Si se pone el número -1 tomaría la fecha más cercana por arriba.

Por todo ello, si pusiéramos de forma aislada la función COINCIDIR:  =COINCIDIR (C13;C3:C10) , nos devolvería el número 6 en el ejemplo anterior (que es el número que utiliza INDICE en su segundo argumento para buscar el nombre del trabajador).

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Mostrar la edad, teniendo la fecha de nacimiento en Excel

En cualquier libro de Excel que se gestionen datos de personas, se utilizan fechas de nacimiento. Pero, ¿que ocurre si lo que nos hace falta es mostrar la edad? A veces, es más útil y rápido tener a la vista la edad ya calculada y no tener que hacerlo mentalmente.

Esto tiene una fácil solución: utilizar la función TEXTO.

La función TEXTO convierte un valor numérico en un texto, permitiendo especificar el formato de presentación mediante unos caracteres especiales.

Tenemos que recordar que en Excel, las fechas son número representados en forma de fecha. Por ejemplo, la fecha 1 de Enero de 1900 corresponde al número 1, la fecha 2 de Enero de 1900 corresponde al número 2 y así sucesivamente. Por ejemplo, la fecha 1 de Enero de 2012 corresponde al número 40909. Sólo tenemos que introducir la fecha en una celda y cambiar el formato a "Número" para darnos cuenta.

Por ejemplo, si la celda B8 tiene como valor la fecha de nacimiento 01/01/1970, escribiremos en B9 lo siguiente:

= TEXTO (HOY ( ) - B8;"aa""&" años")

y obtenemos la edad, seguida de la palabra "años".

Vemos que la función TEXTO tiene 2 argumentos:

=TEXTO(valor;formato)

Para el primer argumento  HOY ( ) - B8  , el valor que se obtiene es la diferencia entre el día actual y la fecha de nacimiento. Con ello, obtenemos un número que corresponderá a la cantidad de días que ha vivido la persona.

El segundo argumento   "aa" , tiene la función de convertir el número de días vivido en forma de años (caracteres de formato"aa" entrecomillados). Después se añade la palabra "años".  

Si queremos "rizar el rizo" como se suele decir, podemos añadir

=TEXTO(HOY()-B8;"aa")&" años"&" + "& TEXTO(HOY()-B8;"m")&" meses"

y obtenemos la edad, seguida de la palabra "años" y los meses (caracter de formato "m" entrecomillado), seguido del signo + y la palabra "meses".

Si queremos saber todos los caracteres de formato que existen en Excel, podemos acudir a esta página de ayuda de Microsoft

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Precio Medio Ponderado con la función SUMAPRODUCTO en Excel

El PMP (Precio Medio Ponderado) es el método de valoración de existencias recomendado en el Nuevo Plan General Contable de 2008 (aunque acepta el ya conocido - en el ámbito de la contabilidad - método FIFO que es el acrónimo de "First Input, First Output". Es decir, que para la valoración de las existencias, se considera que los elementos más antiguos de un almacén, son los primeros en causar baja cuando hay una salida de almacén).

Ni mucho menos todas las pymes controlan sus existencias mediante un ERP o cualquier otro programa que consiga una gestión de inventario que permita saber el valor de las existencias almacenadas en un momento determinado y, con ello, saber el resultado de la empresa (normalmente al final de cada período que se cierra, ya sea mensualmente o al final del ejercicio económico de la empresa). Así pues, la realidad es que muchas empresas deben hacer esta valoración en Excel.

Llegados aquí, se descarta la posibilidad de utilizar el método FIFO (para este método es conveniente un control de inventario permanente mediante programas informáticos que gestionen las entradas y salidas en tiempo real) y no hay más remedio que utilizar el método del PMP o Precio Medio Ponderado.

El cálculo del PMP resulta del sumatorio de las multiplicaciones de las unidades vendidas de cada producto, por su precio / unidad. En Excel, se podría hacer así:

...aunque podríamos mejorar esta forma de hacerlo, a la vez que dar más utilidad a nuestra tabla.

La utilización de la función SUMAPRODUCTO lo hará posible y permitirá:

• Evitar la necesidad de añadir la columna "Venta producto"
• Evitar la suma de las unidades, que nunca suele ser necesaria
• Poder introducir filtros. Por ejemplo, podríamos saber el Precio Medio Ponderado y la valoración del almacén por cada línea de negocio o por cada tipo de producto (por ejemplo).

La función SUMAPRODUCTO debe tener 1 o varios argumentos, los cuales son los rangos que se van a multiplicar entre si, registro a registro (por eso, los rangos deben tener el mismo número de celdas).

Es decir, si queremos multiplicar [unidades x el precio/Unidad] de cada uno de los registros y obtener el sumatorio de todas las multiplicaciones, los rangos que deben aparecer como argumentos de la función SUMAPRODUCTO son D3:D12  y  E3:E12.

Valoración total del inventario =SUMAPRODUCTO (D3:D12 ; E3:E12)

Hasta aquí, tenemos la valoración de todo el inventario Después, si deseamos obtener el PMP, tendremos que dividir entre SUMA(C2:C12) quedando la fórmula como sigue:

PMP =SUMAPRODUCTO (D3:D12 ; E3:E12) / SUMA(D3:D12)

... como podemos ver en la barra de fórmulas de la siguiente imagen:

OTRA FORMA DE UTILIZAR LA FUNCIÓN SUMAPRODUCTO

Como decíamos anteriormente, podemos seguir mejorando la utilidad de nuestro inventario en Excel si introducimos una variación a nuestra función SUMAPRODUCTO. Esto es, un filtro (o condición) para que el resultado esté referido al tipo de producto (Materia prima, producto en curso o producto terminado) que seleccionemos en una lista desplegable.

Para ello, debemos utilizar la función de otra forma. Si bien en los ejemplos anteriores hemos utilizado SUMAPRODUCTO con varios argumentos (separados por punto y coma), para nuestro nuevo objetivo sólo utilizaremos un argumento, dentro del cual multiplicamos los rangos C3:C12 * D3:D12 * E3:E12 ... aunque introduciendo una condición en el primero de ellos. Por lo tanto, las fórmulas quedarían así:

Valoración total del inventario =SUMAPRODUCTO ((C3:D12=E14)*D3:D12*E3:E12)

PMP =SUMAPRODUCTO ((C3:D12=E14)*D3:D12*E3:E12) / SUMA(D3:D12)

Con esto conseguimos que cuando Excel evalúe la condición (E14 es una lista desplegable con los tipos de producto), devuelva un uno o un cero en cada celda del rango C3:C12 y de esta forma sólo habrá un resultado en aquellos registros que en la columna C haya un 1, es decir, en aquellos que el tipo de producto coincida con lo seleccionado en la lista desplegable que hay en E14. En las demás, Excel devuelve un cero (FALSO en la condición) y este cero, multiplicado por las celdas de las columnas D y E, dará como resultado... cero y no sumará para el resultado final.

Podemos descargar un archivo Excel de ejemplo con las fórmulas y la lista desplegable funcionando:

... también podemos verlo aquí:

¿Cómo hacer una lista desplegable?

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Con Excel 2013: El nuevo "Control de escala de tiempo" interactivo

 (Artículo actualizado el 26/08/2016)

Otra de las "joyas" que nos trae el nuevo Excel 2013 es el "Control de escala de tiempo" interactivo. Es una herramienta extremadamente útil y fácil de implementar y utilizar. Es aplicable a Tablas Dinámicas.

Como podemos ver arriba, se trata de un cuadro flotante en el que obtenemos una escala de tiempo sobre la que podemos interactuar formando una selección. Dicha selección, no sólo puede variar en amplitud (nº de meses seleccionados), sino también en su posición en la escala.

Es una mejora importante respecto a otras posibilidades de selección de fechas como son los filtros desplegables de siempre ....

o la segmentación de datos (introducida en Excel 2010)...

...el motivo es evidente. La agilidad es infinitamente mayor. Podemos ver en el detalle siguiente, que la amplitud de período se puede elegir fácilmente haciendo click en cualquiera de los períodos y arrastrando el ratón hacia un lado, como si fueran celdas. 

... o extendiendo la zona sombreada de rojo mediante la manija señalada por las fechas negras:

INSERTANDO UNA ESCALA DE TIEMPO

Los controles de escala de tiempo están disponibles solo para las tablas dinámicas y podemos acceder a ellos situando el cursor sobre cualquier celda de una tabla dinámica y después activando la ficha contextual "Analizar". Una vez hecho eso, si miramos en el grupo de botones "Filtrar" podemos ver que hay un botón llamado "Insertar escala de tiempo":

Cuando hacemos uso de este comando, obtenemos un cuadro de diálogo donde podremos seleccionar el campo de fecha (que debe existir, evidentemente) sobre el que queremos el control.




Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company: