Continuamos con la serie de artículos dedicados a las novedades de la versiones 2019 y 365 de la suite ofimática de Microsoft. Aunque este es un blog sobre Excel, remitimos al usuario a nuestro blog de presentaciones para ver el dossier completo de novedades. Podemos afirmar que casi cualquier usuario o usuaria que utiliza Excel, alguna vez (o quizá muchas), utiliza también PowerPoint.
Al igual que en el caso de Excel, lo primero que debemos decir es que no todas las novedades están disponibles para usuarios de PowerPoint 2019 (la versión de pago único). Sin embargo, los suscriptores de Office 365 si podrán disfrutar de un PowerPoint con todas las nuevas funcionalidades.
Aquí os dejo el enlace para que veáis todas las novedades de PowerPoint:
Si queréis ver más sobre novedades de Office, podéis hacer clic en el siguiente enlace y tenéis todo un curso gratuito grabado por mí para LinkedIn Learning:
La función BUSCARV se utiliza en Excel para obtener como resultado, el valor de una celda que está en la misma fila de una tabla, pero una o varias columnas más a la derecha que otro valor que conocemos y buscamos.
Para escribir la función BUSCARV, necesitamos 4 argumentos separados por punto y coma y encerrados entre paréntesis:
=BUSCARV (valor buscado ; rango de toda la tabla ; nº columna valor devuelto ; [Ordenado])
Valor buscado: debe ser una referencia a la celda donde esté el valor que queremos buscar en la otra tabla.
Rango de toda la tabla: debe ser un rango que abarque todos los datos de la tabla donde hay que buscar, debiendo ser la columna donde se encuentra el valor buscado,la primera a la izquierda.
Nº columna valor devuelto: es un número (entero positivo o negativo) que es el número de columna (contando hacia la derecha desde la columna de la izquierda) donde está al dato que queremos obtener. Si el número es negativo, se cuenta hacia la izquierda.
[Ordenado]: es un valor opcional. Sólo admite una de las opciones: VERDADERO o FALSO. Si se omite, se considera VERDADERO, lo que obliga a que la columna de la izquierda (donde se encuentra el valor buscado) esté ordenada de forma ascendente. Si por el contrario ponemos FALSO (es la opción que frecuentemente se utiliza), no es necesario que la columna esté ordenada.
Para una mayor comprensión, veamos el siguiente video-tutorial:
BUSCARH
La V (vertical) de BUSCARV significa que el valor buscado se debe encontrar a lo largo de una columna). Esta función tiene como homóloga BUSCARH, la cual tiene el mismo funcionamiento que BUSCARV, excepto que el valor buscado se intenta encontrar a lo largo de una fila (en horizontal) y que el valor que se solicita está a x filas hacia abajo (si se pone un nº positivo), contando desde la primera fila de la tabla.
Si el lector ha revisado los artículos de este blog referidos a Modelización de datos en Excel, y los 4 artículos relacionados siguientes, encontrará la necesidad de saber como se pueden relacionar los datos de dos tablas distintas.
Pues bien, en primer lugar definamos qué es eso de "relacionar" los datos de dos tablas.
Relacionar tablas
Dicho de forma coloquial, relacionar dos tablas, es conseguir que, estando en una tabla nº 2, se pueda elegir un dato de un registro de la tabla nº 1 y automáticamente aparezcan en la tabla nº 2 los datos asociados a ese registro.
Por ejemplo: estando en una tabla que registra las nóminas mensuales, cuando seleccionamos el trabajador a quien corresponde los datos de la nómina que estamos registrando, aparezca en el campo de al lado el área a la que pertenece dicho trabajador.
¿Cómo se hace?
Debemos hacerlo en 3 pasos:
PASO 1) Definir un "Nombre al rango" donde están los datos a enlazar
En la Tabla nº 1 debemos seleccionar la columna de datos que queremos tener en la lista desplegable de la Tabla nº2. En el ejemplo que vamos a utilizar, es la columna donde tenemos los nombres de trabajadores:
Seguidamente, hacemos click para obtener el cuadro de "Nombre nuevo, en la ficha Fórmulas / grupo Nombres definidos / Asignar nombre / Definir nombre...
... y sustituimos el Nombre que se propone en el cuadro de diálogo, por el de IDTrabajadores
después de aceptar ya estará definido el Nombre para el rango, aunque no apreciemos nada. Si queremos ver los nombres de rangos que tiene nuestro libro de Excel, podemos revisarlos en el botón "Administrador de Nombres" que había a la izquierda de "Asignar nombre...".
PASO 2) Crear las listas desplegables para que queden los datos enlazados
Ya en la Tabla nº 2, que es donde tenemos que seleccionar el nombre del trabajador en una lista desplegable, debemos crear una lista desplegable mediante "Validación de datos" y arrastrarla al resto de celdas. Cuando estemos creando la lista desplegable, debemos introducir en el campo "Origen:" el nombre que creamos en el paso 1) precedido del signo igual:
Podemos comprobar la lista desplegable (y también comprobar que si añadimos trabajadores en Tabla nº 1, también aparecerán en la lista desplegable).
PASO 3) Obtener en la celda de la derecha el IDÁrea que corresponde al trabajador que se seleccione en la lista desplegable
Es decir, si selecciono el trabajador Páez Tur, Gabriel ... que me aparezca automáticamente en la celda de la derecha el área de la empresa donde trabaja.
Esto se hace introduciendo una función BUSCARV que busque el valor de la lista desplegable en la columna correspondiente de Tabla nº1. Una vez encontrado el valor (estará en una determinada fila), que nos devuelva el valor que hay en la misma fila, pero en la 5ª columna (podemos ver como en la columna nº 5 de la Tabla nº 1 que hay en el PASO 1), tenemos el IDÁrea del trabajador).
Hemos conseguido que, seleccionando un trabajador en el campo ID Nombre Trabajador, obtengamos automáticamente un dato relacionado con él y que está en otra tabla.
Las dos tablas han quedado relacionadas mediante los campos clave "ID Nombre 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:
En este post, vamos a ofrecer varias posibilidades de personalización del diseño y lo vamos a hacer mediante videotutoriales:
Aspecto de la aguja:
Longitud:
Para cambiar la longitud de la aguja, basta con cabiar el valor de la celda que a tal efecto hay en el cuadro del velocímetro:
Ancho y forma:
Seleccionando la aguja y desde el comando "Contorno de forma" de la "Ficha Formato", podemos alegir el aspecto que tendrá la aguja de nuestro velocímetro:
Aspecto de la banda de graduación:
Podemos hacer más ancha o estrecha esta banda y dar un aspecto 3D, además de adecuar los colores a nuestro gusto:
Etiquetas de la banda de graduación:
Si deseamos acompañar la banda de graduación con etiquetas, es tan sencillo como introducir los porcentajes deseados en determinadas celdas y después colocar el velocímetro en el lugar adecuado:
Mostrar el porcentaje cumplido:
Si queremos presentar en pantalla el porcentaje cumplido respecto del objetivo, tan sólo debemos hacer referencia a la celda que a tal efecto hay en el cuadro de datos del velocímetro.
Cuando tenemos que referirnos al contenido que hay en una determinada celda o en un grupo de celdas, lo podemos hacer escribiendo su referencia. Por ejemplo, =Hoja!B1 (en el caso de la celda B1 que está en la Hoja1) o por ejemplo =B1:B5 (en el caso de un rango de celdas que está en la hoja actual).
A veces, hacer estas referencias de la forma que todos conocemos (haciendo click en la celda o seleccionando el rango deseado mientras escribimos una fórmula en una celda) es rápido y eficiente, pero en otras ocasiones, es posible que aparezcan algunos inconvenientes:
Hacer referencia a una celda o rango seleccionándola directamente allí donde se encuentre, pudiera ser más molesto o lento de lo deseado.
Si tenemos que revisar una fórmula que tiene referencias a una celda o a un rango, probablemente no vamos a reconocer a primera vista que tipo de datos contiene esa referencia. Por ejemplo, la fórmula =B4 + C4 no nos dice que contienen las celdas B4 y C4.
Para solucionar esto, existe la posibilidad de asignar un nombre descriptivo a las celdas o rangos.
Siguiendo el ejemplo del último punto anterior y suponiendo que B4 contiene una base imponible y que C4 contiene el IVA, podemos asignar nombres a B4 y a C4, de forma que podamos relacionar esas celdas con sus conceptos cuando tengamos que revisar la fórmula. De esta forma y una vez asignados los nombres "Base" e "IVA", para sumarlos en una fórmula sólo tenemos que poner: =Base+IVA.
De la misma forma, si tenemos un rango donde aparecen las ventas realizadas por una serie de vendedores, por ejemplo, F5:F24, podemos asignarle el nombre Ventas y en adelante cuando tengamos que operar con el rango sólo tenemos que poner directamente (por ejemplo) =SUMA(Ventas) o PROMEDIO(Ventas), etc.
Además de la opción de generar listas desplegables vista en el post "Listas desplegables en Excel (2)" , tenemos la posibilidad de generarlas mediante el cuadro de diálogo "Validación de datos" que está en la ficha "Datos" de la cinta de opciones y seleccionando en el campo "Permitir:" la opción "Lista":
Con esta opción, tenemos las siguientes ventajas:
Los items de la lista desplegable pueden ser:
Elegidos arbitrariamente por nosotros e introducidos a mano, separados por el signo punto y coma, en el campo "Origen:" del cuadro de dialogo de "Validación de datos".
Elegidos de un rango de celdas de la hoja activa o de otra hoja (en Excel 2003 sólo de la hoja activa).
Elegidos de un rango variable (con más o menos items) mediante la función DESREF. Es decir, podemos tener una lista desplegable con los nombres de nuestros clientes, pero si añadimos un cliente, automáticamente aparecerá después en nuestra lista desplegable.
Elegidos de un rango al que le hemos asignado un nombre (Ver: Nombres de celdas o de rangos en Excel). Si utilizamos esta opción para el origen de los items, podemos permitir que se introduzcan valores que no están incluidos en la lista siempre y cuando:
Se active la casilla "Omitir blancos".
En el rango exista una celda en blanco al menos.
Las listas desplegables realizadas con "Validación de datos" se crean en una celda y el valor elegido queda introducido en ella directamente.
Podemos utilizar mensajes informativos para que el usuario tenga información, por ejemplo, de que información debe introducir en la celda. Esto se consigue en la pestaña "Mensaje de entrada" del cuadro de diálogo que ya conocemos de "Validación de datos":
Con el resultado de...
Podemos utilizar mensajes de error para el caso de que el usuario no seleccione correctamente un item o bien intente introducir un valor no adecuado. Esto se consigue de forma muy similar al mensaje de entrada, pero en la pestaña "Mensaje de error" del cuadro de diálogo que ya conocemos de "Validación de datos".
Si deseamos, por ejemplo, que una tabla contenga listas desplegables en todos las celdas de una columna, podemos replicar las listas desplegables tan sólo con utilizar "Autorrellenar".
Desarrollamos con más detalle y otra metodología (video-tutorial) la primera de las clases de listas desplegables que podemos utilizar en Excel.
Nos referimos a las listas desplegables generadas mediante el menú contextual que obtenemos con el botón derecho del ratón:
Cuando tenemos una serie de items introducidos en un rango de celdas a lo largo de una columna (de forma similar a la imagen anterior), Excel reconoce la posibilidad de que el usuario pueda necesitar introducir algunas veces más alguno de los items ya utilizados. De esta forma, cuando hacemos click con el botón derecho en la celda inmediatamente inferior a la última de la lista, obtenemos un menú contextual como el siguiente:
Podemos ver que la antepenúltima opción es "Elegir de la lista desplegable...". Una vez que la seleccionamos, obtenemos una lista de los items que hay en las celdas inmediatamente superiores a la celda donde hemos obtenido el menú desplegable.
Tenemos que tener en cuenta que:
Si la lista que ya tenemos en Excel tiene algún item repetido, sólo lo muestra una vez en la lista desplegable.
La lista desplegable que generamos, sólo contiene items que ya hayan sido introducidos en la lista que tenemos ya en Excel.
Si la lista que ya tenemos en Excel está fraccionada (no tiene todos los items seguidos), sólo aparecerán en la lista desplegable los items contiguos del último tramo de más abajo.
Si hacemos click en otra celda que no sea la inmediatamente inferior a la lista que ya tenemos en Excel, no se generará ninguna lista desplegable.
Si la lista que ya tenemos en Excel contiene items de fechas o números, estos no se incluirán en la lista desplegable. Sólo se inlcuyen en las listas desplegables generadas mediante el menú contextual, los items que son cadenas de texto (o texto mezclado con números).
Cuando necesitamos introducir una serie de datos (números, textos, palabras, etc.) que tienen una relación entre si (en Excel se llaman "listas") podemos hacer más rápido el proceso utilizando la herramienta "Autorrellenar" de Excel.
Veamos una demostración con un viedo-tutorial:
Por defecto y con la propia instalación del programa ya hay configuradas unas cuantas listas, preparadas para cuando utilicemos "Autorrellenar". Concretamente, son las cuatro que hemos visto en el video-tutorial:
Días de la semana con formato corto (lun)
Días de la semana con formato largo (lunes)
Meses del año con formato corto (ene)
Meses del año con formato largo (enero)
Es posible añadir y modificar listas personalizadas para optimizar tiempo en la introducción repetitiva de listas de datos. Para ello, remitimos al artículo que trata de ello:
Iniciamos este artículo con la intención de repasar lo más importante de las posibilidades avanzadas que ofrece Excel para aplicar "Formato condicional".
En anteriores artículos: Formato condicional en Excel y Formato condicional en Excel (2) habíamos visto principalmente las 5 opciones que se ofrecen con formatos preestablecidos, pero para un uso avanzado de Excel pudiera ser necesaria la personalización de algunas opciones.
Para ello, al hacer click en "Formato condicional" dentro de la ficha "Inicio", podemos encontrar 3 opciones que dan paso a una amplia gama de posibilidades de personalización:
Nueva regla...
Borrar reglas
Administrar reglas...
Nuevas reglas...
La primera de las opciones es añadir un nuevo formato condicional aplicando reglas personalizadas. Haciendo click en "Nuevas reglas...", obtenemos la imagen de abajo:
En el primer panel: podemos seleccionar el tipo de regla, siendo las 5 primeras, similares a las ya vistas, aunque con la posibilidad de personalizarlas en el segundo panel.
En el segundo panel ("Editar una descripción de regla:"), se pueden aplicar las personalizaciones deseadas.
Veamos un video-tutorial de las posibilidades que ofrece la sexta opción del primer panel: "Utilice una fórmula que determine las celdas para aplicar formato":
Borrar reglas
La segunda opción nos permite eliminar los formatos condicionales que hayamos podido aplicar en un rango de celdas. Hay que insistir que si tenemos un formato condicional aplicado a determinadas celdas y volvemos a aplicar un nuevo formato condicional sin borrar las reglas del primero, el último formato se acumula y tendríamos varias reglas de forma simultánea.
Podemos elegir entre borrar las reglas de las celdas que tengamos seleccionadas o borrar todas las reglas de formato condicional que haya en la hoja.
Administrar reglas...
Esta opción es la más polivalente y desde la que aconsejamos operar a los usuarios avanzados, puesto que desde el cuadro de diálogo de "Administrar reglas..." tenemos la posibilidad de acceder a todas las opciones: crear nuevas reglas, borrarlas y administrarlas, como vamos a poder ver en el video-tutorial siguiente:
El formato condicional es una utilidad que posee Excel para poder presentar los datos de una o varias celdas, con las características visuales (formato) que nosotros deseemos, siempre y cuando se cumplan una o varias condiciones que podemos poner.
El valor de esta herramienta reside en potenciar y facilitar al usuario la comprensión, comparación e identificación de los datos mediante la apariencia, símbolos o iconos. Por ejemplo, ¿podíamos saber sin temor a equivocarnos y en no más de 5 sg. qué trabajador ha vendido más, de todos los de la tabla de abajo?
Y en esta otra tabla: ¿podríamos dar respuesta a la pregunta anterior, también en 5 sg.?
Pues esta es sólo una (y de las más sencillas) de las funcionalidades que ofrece "formato condicional".
Para acceder a los formatos condicionales, tenemos que tener activada la ficha "Inicio", de esta forma, podemos encontrar el grupo "Estilos" que es donde se encuentra el comando de formato condicional:
Al hacer click, se despliega un menú en el que podemos apreciar dos partes bien diferenciadas:
En primer lugar vemos 5 opciones con imágenes a la izquierda:
- Resaltar reglas de cledas
- Reglas superiores e inferiores
- Barras de datos
- Escalas de color
- Conjuntos de iconos
Estas 5 opciones son de aplicación inmediata y simplificada y, aunque se podrían personalizar posteriormente, son ideales si no tenemos un nivel alto de exigencia y concreción.
En segundo lugar, vemos 3 comandos que dan paso a otras tantas pantallas que nos permitirán personalizar el formato a aplicar y también las reglas y condiciones que ponemos para que se aplique. Estas 3 últimas opciones las trataremos otros articulos posteriormente.
De momento y en este artículo, para explicar el funcionamiento de 2 de las 5 opciones más básicas, ofrecemos unos videos que mostrarán las posibilidades que ofrece "formato condicional" aplicado de forma inmediata y simplificada:
