Como usar Formato condicional avanzado.
Veremos una forma de aplicar el formato condicional avanzado de manera sencilla con unas pequeñas formulas, nos puede ayudar para cambiar el formato dependiendo el texto que tenemos dentro de unas celdas, o cuando cumplen una condición.
Se puede aplicar con muchas formulas, y estas las irán adaptando dependiendo el usuario y sus necesidades, así como su ingenio recordemos que en Excel existe siempre mas de una forma de obtener un resultado.
En esta ocasión les mostrare 2 ejemplos prácticos del formato condicional espero les sea útil.
Primero designamos nuestro semáforo de los estatus para poder definir de esta manera el resto de personas que vean o trabajen con esta base puedan identificar por que los colores.
Una vez ya designada cuales serán los colores procedemos con el formato en este caso va aplicar en toda la fila, y dependerá de una celda que se encuentra dentro de la fila, y abrimos la ventana de formato condicional.
Dentro de la ventana vamos a usar unas formulas, hay que seleccionar la parte donde dice "utilice una formula que determine las celdas para aplicar formato", y usaremos esto =$J7="cerrada" y seleccione el color que va quedar.
Podemos meter varias reglas dentro de un rango, para que podamos identificar varios datos importantes de la base, cabe mencionar que Excel prioriza de arriba hacia abajo las reglas del formato condicional.
también podemos meter funciones para que sea mas dinámica
como por ejemplo =$Q7-HOY()<5, en el cual nos va identificar la fecha que le quedan menos de 5 días para que la fecha de vencimiento.
ya para finalizar copiamos y pegamos con pegado especial (alt gr + V) solo formatos al resto de la base, esta es la parte mas sencilla del ejercicio
ya para finalizar copiamos y pegamos con pegado especial (alt gr + V) solo formatos al resto de la base.
Al final quedaría de esta forma.
Checa el vídeo a continuación, donde te explico mas a detalle como realizar el formato condicional avanzado.
Ocultar y mostrar filas y columnas
Al trabajar con una base de datos en Excel, se tiene información no necesaria para un ejercicio, para eso se usa ocultar y mostrar filas y columnas
En Excel, las hojas de calculo están formadas por filas y columnas; así se pueden identificar cada una de las celdas que se encuentran dentro de la hoja. Una fila es una linea horizontal de celdas enumerada de lado derecho de la hoja de calculo, mientras que una columna es una linea vertical de celdas, representada por una letra del alfabeto ubicada en la parte superior de la hoja de calculo.
A continuación, te enseñamos como borrar, agregar, ocultar y/o mostrar filas y columnas de una forma fácil y rápida con los siguientes atajos del teclado:
Primero se deben seleccionar las filas o columnas que se quieran ocultar, usando los atajos:
Una vez seleccionada las filas o columnas que contienen la información que no se necesita o no se desea tener visible, se van a utilizar los siguientes atajos:
Para las filas:
Para las columnas:
Cuando la información contenida se desea eliminar o por el contrario es necesario agregar más filas o columnas, el primer paso también es seleccionarlas con los atajos mencionados primero y una vez hecho esto, se usa el atajo:
Los atajos de teclado en Excel son una tecla o grupos de teclas que deben oprimirse al mismo tiempo, sirven para ejecutar una acción específica más rápido. Para conocer cuales son los atajos del teclado mas utilizados te recomendamos visitar nuestro canal de YouTube y el video Perfect Exam 2.
Power BI Fórmulas de Texto
Funciones de Base de Datos (BD)
Funciones de Base de Datos (BD)
En este ejercicio analizaremos una base se datos SIN tablas dinámicas solo con funciones BD
Las Funciones de Base de Datos (BD) permiten la creación, modificación y depuración de los datos dentro de una datebasa en Excel.
Estas funciones son similares al resto de funciones ofrecidas por Excel, pero en este caso llevan el prefijo BD en su sintaxis, lo cual hace referencia a su utilidad solo en las bases de datos
![]("https://res.cloudinary.com/nivelproexcel/image/upload/v1609178974/Entradas/Funciones%20de%20Base%20de%20Datos%20%28BD%29/Captura-de-pantalla-2020-11-12-a-las-11.42.36_ldfjml.png)
Tenemos una base de datos y una serie de preguntas, para resolver estas preguntas utilizaremos
BDSUMA, BDCONTAR, BDMAX, BDMIN y BDPROMEDIO
Lo primero será nombrar nuestra base de datos, seleccionandola y con F3, la nombraremos "ventas"
La primer pregunta nos pide el total de venta del 22 de febrero del 2018, utilizaremos la función BDSUMA.
Los argumentos son base de datos y será el rango ya nombrado de toda la base de datos, nos pide nombre del campo será Ventas o I4 y en los criterios seleccionaremos H4:H5 encabezado y la fecha.
Posteriormente nos pide las piezas vendidas de marcadores en el 2020, como base de datos colocamos "ventas" ya que así fue nombrado el rango, el nombre del rango es J8 "piezas" y los criterios son la fecha y los productos todo el rango "H8:I9"
La tercer pregunta nos pide saber en cuantas fechas del 2018 se vendieron reglas utilizaremos BDCONTAR
Los argumentos son los mismos que en los ejercicios anteriores
A continuación te dejamos el enlace del tutorial completo
☝Calendario en Google Sheets☝
Calendario en Google Sheets
Tener un calendario a la mano es útil para organizar nuestras actividades, esto puede ser mas fácil teniendo un Calendario en Google Sheets.
Google Sheets es el Excel gratuito de Google, por lo que podrás acceder a el desde cualquier dispositivo que cuente con esta aplicación. Además, puedes compartir tu calendario con otras personas y permitirles que lo editen si es necesario. Esto puede ser muy útil con tu equipo de trabajo, por ejemplo.
Recuerda que anteriormente te enseñamos como crear un calendario en Excel y ahora te mostramos como hacerlo en la hoja de calculo de Google.
Las fórmulas que se utilizan en Google Sheets para crear el calendario son muy parecidas a las usadas en Excel.
FECHA( año, mes, día): Convierte el año, el mes y el día proporcionados en una fecha.
DIASEM( fecha, tipo): Devuelve un número que representa el día de la semana de la fecha especificada.
PARA APLICAR FORMATO CONDICIONAL Y RESALTAR DÍAS ESPECÍFICOS:
FIN.MES( fecha_inicio, meses): Devuelve la fecha del último día del mes que corresponde a un número específico de meses antes o después de otra fecha.
COINCIDIR( valor_búsqueda, intervalo, tipo_búsqueda): Devuelve la posición relativa de un elemento en un intervalo que coincide con un valor especificado.
Listas Desplegables e Indirecto
Listas Desplegables e Indirecto
En esta pequeña entrada veremos Listas Desplegables e Indirecto
En el primer ejercicio tenemos un pequeño catalogo con precios y simularemos crear una cuenta de lo consumido, en una hoja tenemos el catalogo con precios y en la otra donde generaremos la cuenta con Listas Desplegables.
En la parte de producto generaremos una Lista Desplegable desde el menú de datos y comando validación de datos
Posteriormente en la parte del precio utilizaremos un BUSCARV para traerlo del catalogo
Para finalizar el primer ejemplo de Listas Desplegables vamos a multiplicar la cantidad por el precio y así obtendremos el total
Bien a continuación veremos Listas Desplegables e Indirecto en un ejemplo mas complejo. Tenemos una base de datos con los estados de la República Mexicana nombraremos los rangos de los municipios con el nombre de su Estado
Formato condicional en Google Sheets
Formato Condicional
El formato condicional es una herramienta que se utiliza para aplicar características de formato a determinadas celdas de la hoja de cálculo.
A continuación un ejemplo: (Crédito y cobranza)
Tenemos una base de datos de empresas y queremos resaltar ciertas celdas , vamos a Aplicar un semáforo de 3 colores al estatus en la hoja de calculo de Google Sheets.
Paso 1.- Seleccionar el rango de estatus, ir al menú de formato y seleccionar formato condicional
Paso 2.- Reglas del formato, " el texto contiene" , ingresamos el texto (solicitar pago) y aplicamos un color rojo a las celdas.
3.- Agregar norma, En las reglas del formato, ingresamos el texto (Programar pago) y aplicamos un color amarillo a las celdas correspondientes agregamos la ultima norma donde " el texto contiene" (con crédito) y damos un color Verde a las celdas.
Anexamos otro formato personalizado, en esta ocasión sera mediante una formula es importante mencionar que se aplica el formato únicamente al rango que tenemos seleccionado.
1.- En la columna de contacto, menú formato, formato condicional y en las reglas de formato seleccionamos formula personalizada
2.- Ingresamos la formula de =Contar.si(rango,criterio) >1 , en el rango seleccionamos la columna de contacto y el criterio sera, si la celda $B2 se repite mas de una ocasión coloree la celdas.
La ultima regla que vamos a aplicar en este ejercicio sera mediante una formula personalizada
1.- Menú formato, formato personalizado,reglas de formato, formula personalizada.
2.- la formula que vamos a aplicar es la siguiente =Residuo(Fila(),2)<>0
En esta ocasión la formula de residuo va a dividir la fila en la que estamos posicionados, entre 2 y el resultado sera 1, Entonces si la fila es diferente de cero le vamos a aplicar un formato condicional para resaltar esas filas, de lo contrario agregaremos otro formato para las que sean igual a 0.
De este modo, se pueden destacar rápidamente determinados valores, facilitando su lectura e interpretación dentro de la planilla de cálculo.
Crear un calendario en Excel
Crear un calendario en Excel puede ser muy útil para registrar, organizar y planear diferentes actividades durante el año.
Un calendario es un sistema que nos permite dividir el tiempo en años, meses, semanas y días.
En Excel, podemos encontrar diferentes planillas de calendarios que se deben adaptar según el uso que se le quieran dar. Estas planillas contienen formulas que pueden llegar a ser complicadas para quienes no están familiarizados con el programa.
Es por esto que aquí te enseñaremos algunas fórmulas sencillas que puedes usar para crear un calendario en Excel. Además, podrás personalizarlo y marcar fechas importantes como días festivos, cumpleaños, eventos importantes, etc.
Las fórmulas que puedes utilizar son:
- FECHA(año, mes, día)
Esta función devuelve el número que representa la fecha en código de fecha y hora de Microsoft Excel. - DIASEM(núm_de_serie, [tipo])
Esta función devuelve un número del 1 al 7 que identifica el día de la semana.
núm_de_serie= la fecha que obtuvimos con la fórmula anterior.
tipo= nos va a indicar en que número de día va a iniciar la semana.
APLICAR FORMATO CONDICIONAL EN UN CALENDARIO
Normalmente cada mes tiene entre cuatro, cinco y hasta seis semanas, dependiendo de el día de la semana en que inicie. Para diferenciar los días que no corresponden al mes en curso, se debe aplicar un formato condicional a estos días con la fórmula FIN.MES(fecha_inicial, meses).
Para marcar días importantes en el calendario, como días festivos, cumpleaños, eventos, etc. Se debe hacer una lista con estas fechas y aplicar un formato condicional con la fórmula COINCIDIR(valor_buscado, matriz_buscada, tipo_de_coincidencia).
En donde, el valor buscado van a ser las celdas dentro del calendario. La matriz buscada va a ser el rango en donde se encuentre la lista con las fechas importantes, fijando este rango. Y el tipo de coincidencia debe ser Exacta (0).
En el siguiente vídeo te mostramos como puedes crear un calendario personalizable en Excel.
Graficos en Excel: Barras, Lineas y Circular
Graficos en Excel: Barras, Lineas y Circular
Existen diferentes tipos de gráficos en Excel entre los que podemos elegir de acuerdo al tipo de información que se desea mostrar.
Un gráfico es la representación del comportamiento de un conjunto de datos a través de formas o figuras ordenadas y esta formado por un eje X (vertical) y un eje Y (horizontal).
Entre los gráficos más usados en Excel se encuentran:
Gráficos de barras: Pueden ser verticales u horizontales. Muestra los datos por medio de barras del mismo ancho, cada una representa una categoría de la información y su altura depende de la cantidad o tamaño de los datos. Normalmente se usan para representar variables cualitativas o discretas, por ejemplo, el precio de un conjunto de productos de una tienda.
Gráficos de líneas: Muestra el comportamiento de los datos a través de líneas, que tocan puntos que representan la ubicación de la información. Este tipo de gráfico es útil para mostrar la tendencia de los datos en un periodo de tiempo determinado, por ejemplo, la evolución del precio de las acciones de alguna empresa.
Gráficos circulares: No tiene ejes. Muestra la estructura o composición de un conjunto total de datos por medio de un círculo y se divide en partes que representan fragmentos de la información. Son utilizados para mostrar las proporciones de los componentes de un total, por ejemplo, las partes que componen la deuda total de una empresa.
Para insertar un gráfico en Excel:
- Posicionarse dentro de la tabla de datos y oprimir el atajo de teclado Alt + F1, o bien,
- Seleccionar los datos que se van a graficar.
- Después ir al menú “Insertar”.
- En la herramienta “Gráficos” seleccionar el comando del tipo de gráfico que se prefiera.
En el siguiente vídeo podemos encontrar como insertar los diferentes tipos de gráficos en Excel.
Fecha y Hora
Empezaremos por una breve explicación de fecha y hora, que te servirán a tus base de datos, darle estructura a las mismas, para administrar tus vacaciones.
Atajos de Teclado
Si quieres escribir rápidamente (atajos de teclado) la fecha y la hora ponemos 'ctrl + ,' te da la fecha del día de hoy y 'ctrl + shift + :' te da la hora.
Formulas
Usando la formula a de ahora, nos la el día de hoy y la hora actual y si copiamos fecha y hora en la parte de abajo como valores vemos que cambia el formato a un numero entero y decimales;
Separamos con la formula entero y con una resta, el valor de numero entero, podemos visualizar lo siguiente: la fecha en los enteros y la hora en los decimales. podemos escribir la formula de hoy, y después relacionarla para ver el numero entero que corresponde al día en que estés haciendo el ejercicio o al revés escribir un numero aleatorio entero (...o inclusive con decimales para ver la hora) y podrás ver la fecha en que corresponde ese numero.
(Por tanto seria fácil sumarle números enteros a una fecha y te dará esa fecha. En realidad puedes sumar fechas a otras fechas, el resultado seria el numero de días que hay entre esas fechas.)
Tenemos una referencia para las horas del día:
0.00 es media noche
0.50 es medio día
0.88 son las 9 pm (por ejemplo)
Primer Fecha
El numero 1 es a primero de enero de 1990. Si quisiéramos escribir una fecha con formato dd/mm/aa pero el año es antes de 1990, por ejemplo 1989 el formato que tendrá esa celda es general.
vemos como tiene un formato 00/01/1900... y debería ser 12/31/1989? esto es porque ta cambio el formato, al ya no reconocerlo como fecha; y la cambia a formato general
Ahora bien, escribimos la fecha con el formato dd/mm/aa; hasta antes del año, y cuando tenemos que escribir el año pasa algo interesante... si escribimos del 0 al 29 se completa con un año del 2000 hasta 2029 y si escribimos de 30 al 99, lo completa como 1930 al 1999.
Opciones de personalización:
Podemos entrar con 'ctrl + 1' podemos separa la fecha de la hora con un tipo de separador, yendo a formato de celda 'ctrl + 1' en el menú numero, categoría, personalizado y ahí viene varios tipos de separadores o tu teclear algún separador.
Desde la cinta menú inicio a la herramienta formato y después fecha Larga, fecha corta, y para fecha y hora podemos usar la formula de ahora.
Análisis de datos en Excel
Análisis de datos en Excel
La función "Análisis de datos" es una herramienta de Excel usada para obtener los valores de medidas estadísticas como la media, varianza, desviación estándar, realizar el histograma de los datos, etc.
El análisis de datos se refiere a examinar un grupo de datos con la finalidad de obtener información sobre el comportamiento de estos. Consiste en realizar operaciones estadísticas a los datos que se desean analizar.
Este comando se encuentra dentro del menú "Datos" en la herramienta "Análisis de datos", si no es así, para habilitarla es necesario seguir los siguientes pasos:
- Dentro del menú "Archivo" se debe seleccionar el ultimo apartado "Opciones". Una vez abierta esta ventana, se selecciona en la parte de "Complementos" y después "Complementos de Excel". Es importante que en el primer recuadro de opciones este seleccionada la opción "Herramientas para análisis":
- Una vez que se despliega la ventana de complementos, se debe marcar la opción "Herramientas para análisis" y seleccionar Aceptar.
Otra opción para habilitar esta función, es ir directamente al menú "Programador" y en la herramienta "Complementos", seleccionar el comando "Complementos de Excel":
A continuación, se va a realizar un ejemplo para mostrar como funciona esta herramienta.
A partir de una base de datos de una hoja de calculo que contiene información sobre las calificaciones de un grupo de estudiantes:
- Se debe seleccionar la información que se va a analizar.
- Seleccionar la opción "Análisis de datos" que ya debe estar dentro del menú "Datos".
- Buscar y seleccionar las acciones que se quieren aplicar a la información para realizar su análisis.