Contar filas
Aprovecho la oportunidad que me ofrece este magnífico sitio web para exponer un problema que se me plantea al trabajar con excel.
Tengo confeccionada una hoja de cálculo con gran cantidad de datos que importo desde "access". Necesito realizar multitud de cálculos, entre ellos los de tipo "contar filas teniendo en cuen-ta varias condiciones", para lo cual aplico la fórmula matricial correspondiente: {=SUMA(SI(rango1=condición1;SI(rango2=condición2;1;0)))}. Esta expresión me resulta ideal siempre y cuando la "condición1", como las demás, coincidan con el contenido completo de las celdas del rango. El problema surge en los casos en que haya celdas que posean no solo la "condición1", sino además otros caracteres, pues la fórmula sólo es valida si la condición establecida corresponde con el valor exacto de la celda, omitiéndose en el recuento todas las celdas que contengan otros valores añadidos a parte del especificado, no resultando por tanto todo lo efectiva que necesito. He tratado de solucionar este inconveniente buscando un sustituto para el operador "=" por uno del tipo "contiene", pero parece ser que no existe. También e introducido la condición entre comodines (con el símbolo * ), con la idea de que ésta interprete que "es igual" o "contenga" la condición, sin embargo, la fórmula considera los comodines como un carácter más y sólo contribuyo a empeorar el resultado deseado. Por otro lado, estos comodines sí son perfectamente válidos para otro tipo de fórmulas, como por ejemplo su "hermana pequeña", "contar filas teniendo en cuenta una condición": =CONTAR. SI(rango1;condición1), cuenta todas las filas del rango que coinciden con la "condición1" , o =CONTAR. SI(rango1;*condición1*), cuenta todas las filas del rango que coinciden o contengan "condición1".
A estas alturas del planteamiento sólo me queda realizar la esperada pregunta:
¿Existe una modificación a la fórmula "contar filas teniendo en cuenta varias condiciones" para que no tenga en cuenta si la celda posee más datos a parte de los solicitados?, ¿Hay una fórmula diferente para este cálculo?, ¿Se soluciona el problema con la última versión de excel?
Gracias anticipadas y un saludo.
Tengo confeccionada una hoja de cálculo con gran cantidad de datos que importo desde "access". Necesito realizar multitud de cálculos, entre ellos los de tipo "contar filas teniendo en cuen-ta varias condiciones", para lo cual aplico la fórmula matricial correspondiente: {=SUMA(SI(rango1=condición1;SI(rango2=condición2;1;0)))}. Esta expresión me resulta ideal siempre y cuando la "condición1", como las demás, coincidan con el contenido completo de las celdas del rango. El problema surge en los casos en que haya celdas que posean no solo la "condición1", sino además otros caracteres, pues la fórmula sólo es valida si la condición establecida corresponde con el valor exacto de la celda, omitiéndose en el recuento todas las celdas que contengan otros valores añadidos a parte del especificado, no resultando por tanto todo lo efectiva que necesito. He tratado de solucionar este inconveniente buscando un sustituto para el operador "=" por uno del tipo "contiene", pero parece ser que no existe. También e introducido la condición entre comodines (con el símbolo * ), con la idea de que ésta interprete que "es igual" o "contenga" la condición, sin embargo, la fórmula considera los comodines como un carácter más y sólo contribuyo a empeorar el resultado deseado. Por otro lado, estos comodines sí son perfectamente válidos para otro tipo de fórmulas, como por ejemplo su "hermana pequeña", "contar filas teniendo en cuenta una condición": =CONTAR. SI(rango1;condición1), cuenta todas las filas del rango que coinciden con la "condición1" , o =CONTAR. SI(rango1;*condición1*), cuenta todas las filas del rango que coinciden o contengan "condición1".
A estas alturas del planteamiento sólo me queda realizar la esperada pregunta:
¿Existe una modificación a la fórmula "contar filas teniendo en cuenta varias condiciones" para que no tenga en cuenta si la celda posee más datos a parte de los solicitados?, ¿Hay una fórmula diferente para este cálculo?, ¿Se soluciona el problema con la última versión de excel?
Gracias anticipadas y un saludo.
3 Respuestas
Respuesta de pduran24
1
La solución pasaría por crear un módulo en visual basic, pero para ello se requiere un cierto dominio de programación. Existe una solución sin necesidad de recurrir a la programación, es utilizar una columna vacía para llevar a cabo el recuento. Supongamos que tu rango abarca desde la A1 hasta la Z3000, usaría la celda AA1 para escribir la cadena de texto que deseo contar. En AA2 escribiria la siguiente función: =SI(NO(ESERROR(ENCONTRAR($AA$1;celda_a_contar;1)));1;0), despues solo queda copiar dicha celda hasta la fila 3000 y sumar los 1... Es un metodo sencillo pero efectivo. Tiene la ventaja que para contar otra cadena de texto basta con cambiar el contenido de la celda AA1... En fin, espero haberte ayudado. Un saludo. No olvides valorar mi respuesta para que pueda seguir mejorando.
Agradezco tu respuesta. He probado la fórmula, aunque claro está que no ofrece la posibilidad de introducir varias condiciones, que sería precisamente lo que busco. Lo que necesito es algo más complejo pues no sólo debo realizar recuentos de celdas teniendo en cuenta una condición. Tampoco me sirve sumar el resultado de dos fórmulas que tenga en cuenta una condición cada una de ellas. El proceso sería el siguiente: primero debe contemplar varias condiciones (supongamos dos), comprobar que ambas condiciones (una por cada columna de datos) se cumpla en cada fila, y sólo entonces arrojar un resultado positivo, es decir, que cuente la fila. Te lo explico con un ejemplo:
Tengo dos columnas A y B. En la A, cinco filas con colores: Azul, rojo, amarillo, azul y azul. En la columna B, cinco filas con números: 1, 1, 2, 1, 3; respectivamente. Si introduzco la fórmula para contar filas teniendo en cuenta dos condiciones (la que expongo en mi pregunta) como por ejemplo: azul y 1, el resultado sólo puede ser: 2, ya que la fórmula estudia ambas condiciones a la vez por cada fila. Con la solución que me propones sólo podría establecer una condición, un color o un número, y en el mejor de los casos, sumando el resultado de dos fórmulas (una por cada condición) obtendría 6, nada más lejos de la realidad.
El problema se plantea cuando en una misma celda hay más colores del solicitado o más números de los deseados. Por ejemplo si A1 fuese Azul-rojo en lugar de Azul, el resultado sería 1 y nunca 2. Ese es mi quebradero de cabeza.
Mucho me temo que, como bien dices en tu primera exposición, haya que recurrir al lenguaje de programación, pues no tengo los conocimientos necesarios para ello. Gracias.
Tengo dos columnas A y B. En la A, cinco filas con colores: Azul, rojo, amarillo, azul y azul. En la columna B, cinco filas con números: 1, 1, 2, 1, 3; respectivamente. Si introduzco la fórmula para contar filas teniendo en cuenta dos condiciones (la que expongo en mi pregunta) como por ejemplo: azul y 1, el resultado sólo puede ser: 2, ya que la fórmula estudia ambas condiciones a la vez por cada fila. Con la solución que me propones sólo podría establecer una condición, un color o un número, y en el mejor de los casos, sumando el resultado de dos fórmulas (una por cada condición) obtendría 6, nada más lejos de la realidad.
El problema se plantea cuando en una misma celda hay más colores del solicitado o más números de los deseados. Por ejemplo si A1 fuese Azul-rojo en lugar de Azul, el resultado sería 1 y nunca 2. Ese es mi quebradero de cabeza.
Mucho me temo que, como bien dices en tu primera exposición, haya que recurrir al lenguaje de programación, pues no tengo los conocimientos necesarios para ello. Gracias.
Je je je, espero poder ayudarte tras tu aclaración, por cierto, solo tengo una duda cuando me dices " Por ejemplo si A1 fuese Azul-rojo en lugar de Azul, ¿el resultado sería 1 y nunca 2. Ese es mi quebradero de cabeza." Te refieres a que no debe contar esta celda o SÍ debe contarla? Me explico, la fórmula tal y como te la dí, es cierto que sólo sirve para una condición, pero nada te impide utilizar una nueva columna y establecer una nueva condición usando la misma fórmula pero adaptando los argumentos, al final solo debes contar las filas en las que todas las condiciones dan como resultado 1 y existen varias formas de lograrlo, incluso teniendo en cuenta las excepciones (necesitaría saber si cuando pones "Azul-Rojo" y el segundo valor es "1" ¿También te interesa contarlo o no? En cualquier caso tengo claro que se puede solucionar. Incluso podrías llegar a tener un numero variable de condiciones y que solo las tuviera en cuenta cuando las utilizases... Veamos. Segguiendo tu ejemplo:
Columna A (A1, A2, A3, A4, A5 respectivamente): Azul, Rojo, Amarillo, Azul, Azul.
Columna B (idem): 1, 1, 2, 1, 3
Columna C (suponiendo que no quieras más datos y ya digo que no habria problema si los hubiera): escribe en C1 Azul (por ejemplo). En C2 la fórmula =SI(NO(ESERROR(ENCONTRAR($C$1;A2;1)));1;0) y arrastrala (resultado 1, 0, 0, 1, 1).
Columna D: escribe en D1 1 (por ejemplo). En D2 la fórmula =SI(NO(ESERROR(ENCONTRAR($D$1;B2;1)));1;0) y arrastrala (resultado 1, 1, 0, 1, 0). Si hubiera mas datos, utiliza más columnas con las condiciones (este ejmplo esta pensado hasta para 5 condiciones diferentes)...
Columna H: Escribe en H1 la fórmula: =CONTARA(C1:G1), para saber cuantas condiciones valoramos.
En H2 la fórmula =SI(SUMA(C2:G2)=$H$1;1;0), comprueba si el número de condiciones que se cumplen son las mismas que queremos usar y devuelve un 1 o por el contrario un 0, finalmente suma los 1 de la columna H y verás cuantas veces se cumplen todas las condiciones establecidas. ¡Ojo! Si una celda de la columna B contiene este valor 212 y la condición de búsqueda es 1 la contara como valida (de aquí la pregunta que te hacia antes). Ya me dirás que te parece la nueva solución.
Columna A (A1, A2, A3, A4, A5 respectivamente): Azul, Rojo, Amarillo, Azul, Azul.
Columna B (idem): 1, 1, 2, 1, 3
Columna C (suponiendo que no quieras más datos y ya digo que no habria problema si los hubiera): escribe en C1 Azul (por ejemplo). En C2 la fórmula =SI(NO(ESERROR(ENCONTRAR($C$1;A2;1)));1;0) y arrastrala (resultado 1, 0, 0, 1, 1).
Columna D: escribe en D1 1 (por ejemplo). En D2 la fórmula =SI(NO(ESERROR(ENCONTRAR($D$1;B2;1)));1;0) y arrastrala (resultado 1, 1, 0, 1, 0). Si hubiera mas datos, utiliza más columnas con las condiciones (este ejmplo esta pensado hasta para 5 condiciones diferentes)...
Columna H: Escribe en H1 la fórmula: =CONTARA(C1:G1), para saber cuantas condiciones valoramos.
En H2 la fórmula =SI(SUMA(C2:G2)=$H$1;1;0), comprueba si el número de condiciones que se cumplen son las mismas que queremos usar y devuelve un 1 o por el contrario un 0, finalmente suma los 1 de la columna H y verás cuantas veces se cumplen todas las condiciones establecidas. ¡Ojo! Si una celda de la columna B contiene este valor 212 y la condición de búsqueda es 1 la contara como valida (de aquí la pregunta que te hacia antes). Ya me dirás que te parece la nueva solución.
Realmente tu respuesta da solución al problema que planteo, sin embargo, origina un grave inconveniente. Para obtener el resultado deseado es necesario la utilización de muchas celdas y fórmulas complementarias. Si sólo necesitara obtener un resultado, el utilizar todas esas fórmulas no sería un inconveniente excesivo, pero, como es mi caso, que necesito más de mil cálculos mensuales... ¡Imagínate el tinglado que habría que formar!. He conseguido resolverlo por otro procedimiento: una función de base de datos para excel junto con una tabla de criterios previamente establecida.
Respuesta de nilda
1
Entendí lo que necesitas y creo que la mejor forma de resolverlo es utilizando funciones de Base de datos que te permiten colocar más de un criterio, si me pasa un ejemplo más concreto de los datos y una dirección de correo te pongo un ejemplo de como sería.
De las respuestas que he recibido creo que la tuya es la que más me acerca a la solución. Coincido contigo en que una alternativa sería utilizar una fórmula de base de datos que reconozca excel, pues me consta que con access si puedes elaborar consultas con múltiples condiciones y cadenas de texto. A continuación te expongo un ejemplo práctico:
Tengo dos columnas A y B. En la A, cinco filas con colores: Azul, rojo, amarillo, azul y azul. En la columna B, cinco filas con números: 1, 1, 2, 1, 3; respectivamente. Si introduzco la fórmula para contar filas teniendo en cuenta dos condiciones (la que expongo en mi pregunta) y establezco los valores: azul y 1, el resultado sólo puede ser: 2, ya que la fórmula estudia ambas condiciones a la vez por cada fila.
El problema se plantea cuando en una misma celda hay más colores del solicitado o más números de los deseados. Por ejemplo si A1 fuese Azul-rojo en lugar de Azul, el resultado sería 1 y nunca 2. ¿Qué puedo hacer para que siga siendo 2?. Ese es mi quebradero de cabeza.
Tengo dos columnas A y B. En la A, cinco filas con colores: Azul, rojo, amarillo, azul y azul. En la columna B, cinco filas con números: 1, 1, 2, 1, 3; respectivamente. Si introduzco la fórmula para contar filas teniendo en cuenta dos condiciones (la que expongo en mi pregunta) y establezco los valores: azul y 1, el resultado sólo puede ser: 2, ya que la fórmula estudia ambas condiciones a la vez por cada fila.
El problema se plantea cuando en una misma celda hay más colores del solicitado o más números de los deseados. Por ejemplo si A1 fuese Azul-rojo en lugar de Azul, el resultado sería 1 y nunca 2. ¿Qué puedo hacer para que siga siendo 2?. Ese es mi quebradero de cabeza.
¿Cuándo comentas lo de los colores te estás refiriendo a que escribes la palabra por ejemplo Azul, y en el caso que planteas dos colores es que colocas Azul y Celeste (el texto) dentro de la celda?
Quiero decir:
El color "Azul" está dentro de la fórmula, como una de las condiciones. Y, utilizando el ejemplo que me mandas, en una celda tengo el texto: "Azul celeste". Con la función "contar filas teniendo en cuenta varias condiciones" no hay manera que cuente esa celda. Yo quiero que la cuente porque tiene los caracteres "Azul.." aunque haya texto añadido.
Te paso mi correo: [email protected]
El color "Azul" está dentro de la fórmula, como una de las condiciones. Y, utilizando el ejemplo que me mandas, en una celda tengo el texto: "Azul celeste". Con la función "contar filas teniendo en cuenta varias condiciones" no hay manera que cuente esa celda. Yo quiero que la cuente porque tiene los caracteres "Azul.." aunque haya texto añadido.
Te paso mi correo: [email protected]
Sale a tu correo un ejemplo.
Respuesta de fejoal
1
La tuya no es una pregunta sencilla, más aún si hay que leerla -como aparece aquí- reeplazando los no pocos esotéricos códigos “ o &8221, por comillas...
Pero trataré de simplificarlo diciendo que la solución pasará por cambiar tu pregunta.
En lugar de considerar que "no tenga en cuenta si la celda posee más datos a parte de los solicitados", diría que "tenga en cuenta si la Condición1 *está incluida* en el texto revisado".
Así la siguiente fórmula (también matricial) devuelve el estado:
=SUMA(SI(ESERROR(ENCONTRAR(MAYUSC(A23);MAYUSC($D$10)));0;1))
Claro que esta considera una sola condición, pero es sólo para que entiendas la idea. Luego, podrás anidar más condiciones de este tipo.
El uso de mayúsculas es para evitar exclusiones sólo por haber usado distinta tipografía.
---
Una recomendación final:
Al evaluar el uso de una fórmula matricial, es saludable considerar si es reemplazable por alguna fórmula del tipo "BD" (x ej: =BDCONTAR) donde el manejo de los criterios es más gráfico que en las matriciales...
Aclarando qué entendí mal o qué faltó. Gracias...
*====================================*
Pd: Disculpa la demora, pero actualmente estoy con mucho trabajo.
Pero trataré de simplificarlo diciendo que la solución pasará por cambiar tu pregunta.
En lugar de considerar que "no tenga en cuenta si la celda posee más datos a parte de los solicitados", diría que "tenga en cuenta si la Condición1 *está incluida* en el texto revisado".
Así la siguiente fórmula (también matricial) devuelve el estado:
=SUMA(SI(ESERROR(ENCONTRAR(MAYUSC(A23);MAYUSC($D$10)));0;1))
Claro que esta considera una sola condición, pero es sólo para que entiendas la idea. Luego, podrás anidar más condiciones de este tipo.
El uso de mayúsculas es para evitar exclusiones sólo por haber usado distinta tipografía.
---
Una recomendación final:
Al evaluar el uso de una fórmula matricial, es saludable considerar si es reemplazable por alguna fórmula del tipo "BD" (x ej: =BDCONTAR) donde el manejo de los criterios es más gráfico que en las matriciales...
Aclarando qué entendí mal o qué faltó. Gracias...
*====================================*
Pd: Disculpa la demora, pero actualmente estoy con mucho trabajo.
Realmente me interesa saber si esta solución te sirvió.
Por favor, dedicale un minuto a comentarla, ¿OK?
Un gran abrazo!
Fernando
Por favor, dedicale un minuto a comentarla, ¿OK?
Un gran abrazo!
Fernando
Je-je. La culpa de las esotéricas comillas la tuvo el portapapeles, cuando me percaté de ello ya era demasiado tarde, aunque confiaba en tu intuición para descubrir el enigma.
Lo siento, pero hallé la solución días antes de que me enviaras tu respuesta. Precisamente, como bien dices en tu planteamiento, me aconsejaron que utilizara una función de base de datos. Fue mediante la expresión BDCONTARA e introduciendo en la fórmula un rango perteneciente a una tabla de criterios que previamente tuve que establecer (muchísimas filas y columnas, por cierto). Aun así, y aunque mi problema ha quedado resuelto, hubiese preferido otro tipo de fórmula que prescindiera de una tabla complementaria como la que describo, aunque éste es un mínimo inconveniente.
Al pedirme que valore tu respuesta me creas un dilema razonable, pues si tuviera que hacerlo considerando que aún desconociera la solución a mi problema, tu planteamiento me hubiera resultado algo parco, al carecer de un ejemplo adecuado que desarrollara la función de acorde a mis necesidades. No obstante, y suponiendo que de haber necesitado esa nota aclaratoria, a buen seguro la hubiese recibido, ahí va mi puntuación.
Lo siento, pero hallé la solución días antes de que me enviaras tu respuesta. Precisamente, como bien dices en tu planteamiento, me aconsejaron que utilizara una función de base de datos. Fue mediante la expresión BDCONTARA e introduciendo en la fórmula un rango perteneciente a una tabla de criterios que previamente tuve que establecer (muchísimas filas y columnas, por cierto). Aun así, y aunque mi problema ha quedado resuelto, hubiese preferido otro tipo de fórmula que prescindiera de una tabla complementaria como la que describo, aunque éste es un mínimo inconveniente.
Al pedirme que valore tu respuesta me creas un dilema razonable, pues si tuviera que hacerlo considerando que aún desconociera la solución a mi problema, tu planteamiento me hubiera resultado algo parco, al carecer de un ejemplo adecuado que desarrollara la función de acorde a mis necesidades. No obstante, y suponiendo que de haber necesitado esa nota aclaratoria, a buen seguro la hubiese recibido, ahí va mi puntuación.