Operaciones con rangos discontinuos en Excel.

miércoles, marzo 19, 2008

No todas las funciones de Excel permiten realizar cálculos con rangos discontinuos. Por ejemplo, supongamos estos dos rangos, A1:A3 y A5:A8




Supongamos que por algún motivo no podemos operar con el rango A1:A8. Si queremos sumar todos los valores en los dos rango podemos usar la fórmula

=SUMA(A1:A3,A5:A8)

Lo mismo si queremos contar cuántos elementos hay en ambos rangos

=CONTAR(A1:A3,A5:A8)

Pero si queremos usar CONTAR.SI para averiguar cuantos números mayores a 200 hay en los dos rangos, la fórmula

=CONTAR.SI((A1:A3,A5:A8),">200")

da como resultado #¡VALOR! Lo mismo sucede si queremos usar SUMAR.SI

La solución, por lo general, será combinar dos funciones CONTAR.SI. En nuestro caso sería

=CONTAR.SI(A1:A3,">200")+CONTAR.SI(A5:A8,">200")

El problema con esta solución es que en muchos casos tenemos que combinar muchas funciones CONTAR.SI, lo que convierte en engorrosa la tarea de construir nuestra fórmula.
Veamos cuáles son nuestras posibilidades:

Una posibilidad más compacta es ésta (propuesta por Juan Pablo González en el foro de MrExcel):

=SUMA(CONTAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))

o utilizar la función ARRAY.JOIN del complemento Morefunc que tantas veces he recomendado

={SUMA((ARRAY.JOIN(A1:A3,A5:A8)>200)*1)}

Esta última fórmula es matricial y debe introducirse en la celda apretando simultáneamente Ctrl+Mayúsculas+Enter.

Los "puristas" pueden utilizar también

=SUMA(--(ARRAY.JOIN(A1:A3,A5:A8)>200))

donde usamos el doble signo menos (--) para forzar la conversión de valores lógicos a 1 (VERDADERO) o 0 (FALSO).

Ambas fórmulas dan como resultado 6.

Para sumar condicional podemos usar estas posibilidades:

=SUMA(SUMAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))

una variante de la fórmula con INDIRECTO, adaptada a suma condicional, o

=SUMAPRODUCTO((ARRAY.JOIN(A1:A3,A5:A8)>200)*ARRAY.JOIN(A1:A3,A5:A8))

donde usamos SUMAPRODUCTO junto con ARRAY.JOIN

Podemos, y es recomendable, usar rangos nominados. Definimos dos nombres, cada uno refiriéndose a cada uno de los rangos

rango1 =Hoja1!$A$1:$A$3
rango2 =Hoja1!$A$5:$A$8

y utilizarlos en nuestras fórmulas. Por ejemplo:

=SUMA(SUMAR.SI(INDIRECTO({"rango1";"rango2"}),">200"))

Habrán notado que en las fórmulas con INDIRECTO usamos la expresión {"A1:A3";"A5:A8"}. Esta expresión crea un matriz en base a los datos de los rangos expresados como texto (que aparecen entre comillas y por ese motivo usamos INDIRECTO). Pueden consultar esta nota sobre funciones y constantes matriciales.



Technorati Tags:

10 comments:

diego 21 marzo, 2008 02:52  

Hola maestro!!, buenisimo tu blog, felicitaciones. es un excelente lugar para satisfacer dudas a las cuales microsoft no nos da una respuesta fácil.
Te escribo, por que tengo una duda desde hace ya varios meses. quizás esté en este blog, o bien es muy básica como para ello. Aquí va; quiero hacer una pestaña desplegable con opciones determinadas (como por ejemplo nombres de personas) y que cuando seleccione uno, en otra casilla se disponga un valor relacionado con ese nombre. esto, sin la utilización de macros.

muchas gracias, y nuevamente felicitaciones por tu blog

diego

Jorge L. Dunkelman 21 marzo, 2008 20:10  

Hola Diego

la lista desplegable la haces con Validación de datos. El valor relacionado con la selección de la lista desplegable lo obtienes con alguna función de búsqueda, por lo general BUSCARV.
Puedes buscar información sobre ambos temas en mi blog.

Anónimo,  20 octubre, 2008 13:13  

Hola jorge, necesito ayuda, como se anidada buscarv con la funcion si y O la funcion si o; cual es el razonamiento que tengo que hacer, como indicarle que si es a ( pero lo tengo que buscar en otra lista, reciba tal sueldo? no entiendo como seguir los pasos, muchas gracias por la ayuda
saludos

Jorge L. Dunkelman 29 octubre, 2008 11:50  

Creo que la técnica que muestro en esta nota te será útil

alexxxxxo 17 enero, 2011 12:11  

Hola Jorge:
Tengo una columna A2:A50 con las fechas de nacimiento de un grupo de personas, en la columna C2:C50 estan las edades de cada una de las personas (las calcule a partir de la formula =SIFECHA(A2,HOY(),"Y") ) en la columna AB2:AB50 esta el sexo de dichas personas;
a partir de la fórmula =SUMAPRODUCTO(--(SIFECHA(A2:A50,HOY(),"y")<30),--(SIFECHA(A2:A50,HOY(),"y")>=0)), logré determinar los grupos de edad,
el PROBLEMA ahora es que tengo que saber en cda grupo de edad cuantas mujeres y cuantos hombres hay, por ejemplo menores de 30 años cuantos hombre y cuantas mujeres, espero me puedan ayudar porque ya lo intenté de mil formas y no logro obtener el dato.
Gracias de Antemano,
Saludos
Alexx
Los Mochis, Sinaloa, México

Jorge L. Dunkelman 17 enero, 2011 20:22  

alexxxo,
tu comentario no está relacionado con el tema de la nota (que para eso están los comentarios).
Estás envíado a hacerme la consulta por mail privado, tal como pongo en el enlace "Ayuda".

Anónimo,  03 julio, 2011 12:08  

muy agradecido por tu blog, paso a detallarte mi pregunta, mediante una macro voy llenando una tabla que tiene 14 filas, hasta aquí todo bien mi problema viene cuando se acaban esas 14 filas, que solución sería mejor, modificar la macro para cada hoja? se puede crear tablas con rango discontinuo en la misma hoja?no puede ser continuo pues la tabla tiene a parte del encabezado un logo y algun dato mas y al final de las catorce filas otros datos que quiero q salgan al imprimir, un saludo y reitero mi agradecimiento

Jorge L. Dunkelman 03 julio, 2011 21:12  

La solución es construir el modelo siguiendo las normas de buenas prácticas, entre ellas la separación entre datos y reportes. Tienes que poner los datos en una hoja (sin logo y nada al final de la tabla, solo datos) y el reporte, con logo y todo lo que haga falta en otra hoja.
Al poner los datos en una hoja separda se eliminan los rangos discontinuos. El código de la macro hay que escribirlo con una variable que se ajuste a la cantidad de filas de la tabla.
En el blog hay una serie de notas sobre buenas prácticas en Excel (puedes hacer una búsqueda en el blog con la palabra "prácticas").

harold duvan yate oyola 20 marzo, 2014 06:14  

UN BUEN BLOG, LO FELICITO, SOY UN JOVEN QUE TENGO 16 AÑOS ESTOY EN EL GRADO ONCE DE BACHILLERATO, DESDE NATAGAIMA TOLIMA... SI ME HA SERVIDO TODAS SUS VENTANAS ME SIRVEN UN BUEN APOYO DE TAREAS Y SIGUEN LUCHANDO POR SER MEJORES.. YA TODAS LAS TAREAS QUE HAGO EN SU BLOG ME HE SACADO BUEN PUNTUAJE... Y LOS FELICITO... GRACIAS POR AYUDARME MUCHO...

Anónimo,  19 agosto, 2016 01:25  

excelentes articulos

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP