Validacion de celdas

Lista para validación de datos en Excel

Validar la información que introduces en Excel es de suma importancia para prevenir cualquier interpretación equivocada al momento de analizar los datos. Afortunadamente Excel tiene una funcionalidad que nos permite validar los datos al momento de ingresarlos.

Lista desplegable en Excel

Excel nos permite crear listas desplegables que dan la posibilidad al usuario de elegir un valor dentro de una lista que ha sido previamente definida y validada:

Para hacer uso de esta funcionalidad primero debes seleccionar la celda donde deseas que aparezca la lista desplegable e ir a la ficha Datos y hacer clic sobre el comando Validación de datos:

Se mostrará el cuadro de diálogo Validación de datos en donde deberás seleccionar la opciónLista:

En el recuadro Origen puedes colocar directamente los valores de tu lista desplegable separados por coma:

Al hacer clic en el botón Aceptar se verán reflejados los cambios en la celda:

Lista desplegable con valores en un rango

Si tu lista tiene muchos elementos puedes colocarlos dentro de una hoja de Excel y especificar el rango en el cuadro de diálogo Validación de datos y de esta manera tener una lista desplegablecon los valores extraídos de dicho rango:

No es mala idea invertir un  poco de tiempo para agregar esta funcionaldiad a tus hojas de Excel, recuerda que una lista desplegable te evitará muchos problemas al permitir el ingreso de valores previamente validados.

Texto en colunmas

Dividir texto en columnas

En esta ocasión te mostraré cómo separar el texto de una celda en diferentes columnas, es decir, colocar cada palabra del texto en una columna diferente. En primer lugar debes seleccionar la celda:

Posteriormente en la ficha Datos dentro del grupo Herramientas de datos debes hacer clic sobre el comando Texto en columnas:

Convertir texto en columnas en Excel

Se mostrará el asistente para convertir texto en columnas:

De manera predeterminada aparecerá seleccionada la opción De ancho fijo,  ahora debes hacer clic en Siguiente para ir al paso 2:

En el paso 2 del proceso podemos observar cómo Excel ha detectado los espacios en nuestro texto. Haz clic en Siguiente para ir al último paso:

Lo más importante de este últmo paso es colocar la celda Destino, que es la celda a partir de la cual Excel pondrá la primer palabra. En este ejemplo seleccioné la celda $B$1. Al hacer clic en el botón Finalizar notarás de inmediato la cadena de texto dividida:

Separar palabras de varias celdas

Como segundo ejemplo, si tienes una lista de datos que necesitas dividir será suficiente con seleccionar todos los datos:

De igual manera harás clic sobre el comando Texto en columnas, solo que ahora en la primera pantalla del asistente Excel sugerirá la opción Delimitados, pero eso no cambiará en mucho las cosas así que solo haz clic en el botón Siguiente para ir al paso 2 en donde deberás asegurarte que la opción Espacio está seleccionada como Separador:

Al dar clic en Siguiente se presentará la última pantalla del proceso y solamente deberás seleccionar la celda Destino a partir de la cual deberán colcarse los datos. Al hacer clic en el botón Finalizar obtuve los resultados esperados:

Nombres de rangos

Asignar nombres a celdas o rangos

Hasta ahora he utilizado el estilo de referencia A1 para referirme tanto a una celda como a un rango pero también existe la posibilidad de crear un nombre descriptivo que los represente adecuadamente.

Asignar un nombre a un rango de celdas

Estos nombres se pueden utilizar dentro de una fórmula para ayudar en la compresión de la misma posteriormente. Para asignar  un nombre a una celda sigue los siguientes pasos. Selecciona la celda o rango a la que asignarás un nombre y haz clic en el cuadro Nombre que se encuentra en el extremo izquierdo de la barra de fórmulas:

Escribe el nombre que deseas y presiona Entrar.

Otra manera de crear un nombre para un rango es desde la ficha Fórmulas y el botón Asignar nombre.

Una vez que hayas seleccionado el rango de celdas oprime este botón y se mostrará el cuadro de diálogo Nombre nuevo:

En la caja de texto Nombre coloca el nombre que asignarás a la celda o rango y oprime el botón Aceptar.

Utilizar un nombre en una fórmula

Como ejemplo final utilizaré el nombre que acabamos de crear dentro de una fórmula para que observes cómo Excel interpreta correctamente el nuevo nombre del rango, observa la barra de fórmulas:

Ahora ya sabes que puedes nombrar tanto celdas como rangos y utilizar ese nombre dentro de tus fórmulas para facilitar tu trabajo.

Concatenar

La función CONCATENAR en Excel

La función CONCATENAR en Excel nos permite unir dos o más cadenas de texto en una misma celda lo cual es muy útil cuando nos encontramos manipulando bases de datos y necesitamos hacer una concatenación.

Sintaxis de la función CONCATENAR

La función CONCATENAR tiene una sintaxis muy sencilla donde cada argumento será un texto que se irá uniendo al resultado final. El máximo de argumentos que podemos especificar en la función es de 255 y el único obligatorio es el primer argumento.

• Texto1 (obligatorio): El primer texto que se unirá a la cadena de texto final.
• Texto2 (opcional): El segundo texto a unir al resultado final. Todos los argumentos son opcionales a partir del segundo argumento.

Ejemplo de la función CONCATENAR

La función CONCATENAR nos puede ayudar en casos en los que la información está distribuida en varias columnas y deseamos integrar la información en una sola. Por ejemplo, tengo información de los nombres de los empleados en 3 columnas diferentes:

Para unir el nombre y los apellidos de todos los empleados en la columna D podemos seguir los siguientes pasos.

1. En la celda D2 comenzaré a escribir la función CONCATENAR especificando el primer argumento:
   =CONCATENAR(A2,
2. Como segundo argumento debo especificar un espacio vacío de manera que el Nombre y el Apellido paternos permanezcan separados en la nueva cadena de texto:
   =CONCATENAR(A2," ",
3. Ahora puedo especificar el tercer y cuarto argumento que será el Apellido paterno con su respectivo espacio:
   =CONCATENAR(A2," ",B2," ",
4. Y como último paso en el quinto argumento irá el Apellido materno:
   =CONCATENAR(A2," ",B2," ",C2)

La función CONCATENAR nos da el resultado esperado en la celda D2:

Sólo resta copiar la fórmula hacia abajo para que Excel realice la concatenación de los demás nombres.

Promedio.si.conjunto

La función PROMEDIO.SI.CONJUNTO 

La función PROMEDIO.SI.CONJUNTO nos devuelve el promedio de un rango de celdas que cumplan con las condiciones definidas. Esta función nos permitirá incluir hasta 127 criterios distintos para las celdas que deseamos considerar.

Sintaxis de la función PROMEDIO.SI.CONJUNTO

La función PROMEDIO.SI.CONJUNTO tienen 3 argumentos obligatorios y a partir de ellos los demás serán opcionales.

• Rango_promedio (obligatorio): Las celdas que contienen los valores de los que obtendremos el promedio.
• Rango_criterios1 (obligatorio): El rango de celdas que contiene los valores que serán evaluados con la condición determinada.
• Criterio1 (obligatorio): El criterio con el que serán evaluadas las celdas del Rango_criterios1.
• Rango_criterios2 (opcional): El segundo rango de celdas con los valores que serán evaluados por el Criterio2.
• Criterio2 (opcional): El criterio con el que se evaluará el Rango_criterios2.

A partir de Rango_criterios2 y Criterio2 los argumentos serán opcionales y podremos hasta 127 parejas de rangos y criterios para decidir las celdas que serán consideradas en el cálculo de promedio.

Ejemplo de la función PROMEDIO.SI.CONJUNTO

A continuación mostraré un ejemplo muy sencillo de la función PROMEDIO.SI.CONUNTO. Tengo un rango de celdas en la columna B que contienen el número de Artículos que han entrado al almacén en fechas específicas.

Adicionalmente en la columna C se encuentra el color de dichos artículos. Ahora quiero obtener el promedio de artículos que son de color rojo y para ello puedo utilizar la siguiente fórmula:

=PROMEDIO.SI.CONJUNTO(B2:B11, C2:C11, "Rojo")

El resultado lo puedes observar en la celda F1:

Puedes ver resaltadas en color las celdas que cumplen con el criterio y que por lo tanto son consideradas en el cálculo del promedio. Ahora bien, si deseo adicionar otra condición a este cálculo para obtener el promedio de los artículos que son rojos y además medianos, entonces tendré que agregar dos argumentos a la función PROMEDIO.SI.CONUNTO de la siguiente manera:

=PROMEDIO.SI.CONJUNTO(B2:B11, C2:C11, "Rojo", D2:D11, "Mediano")

Observa cómo las celdas que cumplen con la condición se reducen a solamente dos celdas:

La función PROMEDIO.SI.CONJUNTO es de gran utilidad cuando deseamos obtener el promedio de un grupo de celdas que cumplan con más de una condición.

Contar.si.conjunto

CONTAR.SI.CONJUNTO

Sintaxis

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

La sintaxis de la función CONTAR.SI.CONJUNTO tiene los siguientes argumentos:

• criteria_range1    Obligatorio. El primer rango en el que se evalúan los criterios asociados.
• criteria1    Obligatorio. Los criterios en forma de número, expresión, referencia de celda o texto que determinan las celdas que se van a contar. Por ejemplo, los criterios se pueden expresar como 32, ">32", B4, "manzanas" o "32".
• criteria_range2, criteria2, ...    Opcional. Rangos adicionales y criterios asociados. Se permiten hasta 127 pares de rango/criterio.

 IMPORTANTE   Cada rango adicional debe tener la misma cantidad de filas y columnas que el argumentocriteria_range1. No es necesario que los rangos sean adyacentes.

Ejemplo 1

El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

¿Cómo copio un ejemplo?

1 2 3 4 5 6 7 8 9

A B C D Comercial Cuota de widgets superada Cuota de gadgets superada Cuota de doodads superada García Sí No No Navarro Sí Sí No Gil Sí Sí Sí López No Sí Sí Fórmula Descripción Resultado =CONTAR.SI.CONJUNTO(B2:D2;"=Sí") Cuenta cuántas veces García ha superado una cuota de ventas para widgets, gadgets y doodads. 1 =CONTAR.SI.CONJUNTO(B2:B5;"=Sí";C2:C5;"=Sí") Cuenta cuántos comerciales han superado su cuota de widgets y gadgets. 2 =CONTAR.SI.CONJUNTO(B5:D5;"=Sí";B3:D3;"=Sí") Cuenta cuántas veces López y Navarro han superado la misma cuota para widgets, gadgets y doodads. 1

Ejemplo 2

El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

¿Cómo copio un ejemplo?

1 2 3 4 5 6 7 8 9 10 11

A B C Datos Datos 1 1/5/2008 2 2/5/2008 3 3/5/2008 4 4/5/2008 5 5/5/2008 6 6/5/2008 Fórmula Descripción Resultado =CONTAR.SI.CONJUNTO(A2:A7,"<6",A2:A7,">1") Cuenta cuántos números entre 1 y 6 (sin incluir el 1 y el 6) contienen las celdas de la A2 a la A7. 4 =CONTAR.SI.CONJUNTO(A2:A7, "<5",B2:B7,"<3/5/2008") Cuenta cuántas filas tienen números menores que 5 en las celdas de la A2 a la A7 y, además, cuántas filas tienen fechas anteriores al 3/5/2008 en las celdas de la B2 a la B7. 2 =CONTAR.SI.CONJUNTO(A2:A7, "<" & A6,B2:B7,"<" & B4) La descripción es la misma del ejemplo anterior, pero se usan referencias de celda en lugar de constantes en los criterios. 2

Funcion sumr.si.conjunto

Función SUMAR.SI.CONJUNTO

Mostrar todo

Descripción

Agrega las celdas de un rango que cumpla con varios criterios. Por ejemplo, si desea sumar los números del rango A1:A20 sólo si los números de B1:B20 correspondientes son mayores que cero (0) y los números de C1:C20 correspondientes son menores que 10, puede usar la siguiente fórmula:

=SUMAR.SI.CONJUNTO(A1:A20; B1:B20; ">0"; C1:C20; "<10")

 IMPORTANTE   El orden de los argumentos es diferente entre las funciones SUMAR.SI.CONJUNTO y SUMAR.SI. Concretamente, el argumento rango_suma es el primer argumento de SUMAR.SI.CONJUNTO, pero es el tercero de SUMAR.SI. Si está copiando o editando estas funciones similares, asegúrese de poner los argumentos en el orden correcto.

Sintaxis

SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterios1, [rango_criterios2;
         criterios2]; …)

La sintaxis de la función SUMAR.SI.CONJUNTO tiene los siguientes argumentos:

• rango_suma    Obligatorio. Una o más celdas para sumar, incluidos números o nombres, rangos o referencias de celda que contienen números. Se omiten los valores en blanco o de texto.
• rango_criterios1    Obligatorio. El primer rango en el que se evalúan los criterios asociados.
• criterios1    Obligatorio. Los criterios en forma de número, expresión, referencia de celda o texto que define qué celdas del argumento rango_criterios1 se agregarán. Por ejemplo, los criterios se pueden expresar como 32, ">32", B4, "manzanas" o "32".
• rango_criterios2, criterios2, …    Opcional. Rangos adicionales y sus criterios asociados. Se permiten hasta 127 pares de rangos/criterios.

Ejemplo 1

El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

¿Cómo copio un ejemplo?

1 2 3 4 5 6 7 8 9 10 11 12

A B C Cantidad vendida Producto Vendedor 5 Manzanas 1 4 Manzanas 2 15 Alcachofas 1 3 Alcachofas 2 22 Bananas 1 12 Bananas 2 10 Zanahorias 1 33 Zanahorias 2 Fórmula Descripción Resultado =SUMAR.SI.CONJUNTO(A2:A9; B2:B9; "=A*"; C2:C9; 1) Agrega la cantidad total de productos vendidos que comienzan con "A" y fueron vendidos por el vendedor 1. 20 =SUMAR.SI.CONJUNTO(A2:A9; B2:B9; "<>Bananas"; C2:C9; 1) Agrega la cantidad total de productos (sin incluir bananas) vendidos por el vendedor 1. 30

Ejemplo 2

Agregar importes de cuentas bancarias sobre la base del interés pagado

El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

¿Cómo copio un ejemplo?

1 2 3 4 5 6 7 8

A B C D E Totales Cuenta 1 Cuenta 2 Cuenta 3 Cuenta 4 Cantidad en dólares 100 390 8321 500 Interés pagado (2000) 1% 0,5% 3% 4% Interés pagado (2001) 1% 1,3% 2,1% 2% Interés pagado (2002) 0,5% 3% 1% 4% Fórmula Descripción Resultado =SUMAR.SI.CONJUNTO(B2:E2; B3:E3; ">3%"; B4:E4; ">=2%") Importes totales de cada una de las cuentas bancarias en las que el interés era mayor que 3% para el año 2000 y mayor o igual que 2% para el año 2001. 500 =SUMAR.SI.CONJUNTO(B2:E2; B5:E5; ">=1%"; B5:E5; "<=3%"; B4:E4; ">1%") Importes totales de cada una de las cuentas bancarias en las que el interés era entre 1% y 3% para el año 2002 y mayor que 1% para el año 2001. 8711