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

Power BI Fórmulas de Texto

En Power BI hay una serie de funciones de texto que nos pueden ser muy útiles para procesar información.

Vamos a ir poniendo descripciones y usando las funciones para manipular texto.

Queremos convertir todo el nombre a mayúsculas, para ello usamos la función UPPER

=UPPER() devuelve una cadena de texto en MAYUSCULAS

=LEN() Cuenta o devuelve el número de caracteres de una cadena de texto.

=LEFT() Devuelve el número de caracteres especificados desde el principio de una cadena de texto. La cadena de texto que contiene los caracteres que se quieren extraer de lado izquierdo, o bien una referencia a una columna que contiene texto.

=LOWER() Convierte todas las letras de una cadena de texto a minúsculas.

=REPT() Repite el texto un número determinado de veces. REPT se usa para llenar una celda con un número de instancias de una cadena de texto.

=TRIM() Quita todos los espacios del texto, excepto los espacios individuales entre palabras.

Ejemplo: Si tu texto tiene varios espacios, con esta fórmula los remueves.

=RIGHT() Devuelve el último carácter o caracteres de una cadena de texto a la derecha de la celda, en función del número de caracteres que se especifique.

=MID() Devuelve una cadena de caracteres de la mitad de una cadena de texto a partir de una posición inicial y una longitud.

=REPLACE() reemplaza la parte de una cadena de texto, según el número de caracteres que se especifique, por otra cadena de texto.

=SUBSTITUTE() Reemplaza texto existente por otro nuevo en una cadena 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

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.

Calendario con Sheets

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.

 

Te invitamos a ver nuestro vídeo, Como crear un calendario en Google Sheets

 

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

 

Si quieres ver el tutorial completo de Listas Desplegables e Indirecto. Entra al video tutorial de Youtube

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.

Ejemplo calendario

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:

  1. 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.
  2. 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.

Ejemplo de un gráfico de barras
Ejemplo de gráfico de barras

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.

Ejemplo de un gráfico de lineas.
Ejemplo de gráfico de líneas

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. 

Ejemplo de un gráfico circular
Ejemplo de gráfico circular

Para insertar un gráfico en Excel:

  1. Posicionarse dentro de la tabla de datos y oprimir el atajo de teclado Alt + F1, o bien,
  2. Seleccionar los datos que se van a graficar.
  3. Después ir al menú “Insertar”.
  4. En la herramienta “Gráficos” seleccionar el comando del tipo de gráfico que se prefiera. 

Atajo de teclado insertar grafico

En el siguiente vídeo podemos encontrar como insertar los diferentes tipos de gráficos en Excel.

Aquí puedes consultar la plantilla utilizada para este ejercicio.

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:

  1. 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":
    Complementos de Excel, herramientas para análisis de datos
  2. Una vez que se despliega la ventana de complementos, se debe marcar la opción "Herramientas para análisis" y seleccionar Aceptar.
    Activar herramientas para análisis

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:

  1. Se debe seleccionar la información que se va a analizar.
  2. Seleccionar la opción "Análisis de datos" que ya debe estar dentro del menú "Datos".
  3. Buscar y seleccionar las acciones que se quieren aplicar a la información para realizar su análisis.

 

 

 

 

 

Formato condicional Avanzado

Deja una respuesta