En una entrada anterior expliqué una forma de sumar letras en Excel asociados a ciertos valores (ver). En esta ocasión explicaremos la forma de segregar información de celdas, en la que tenemos datos alfanuméricos juntos.
Veámoslo mejor en una imagen:
Como vemos tenemos una plantilla para ciertos trabajadores, en los que informamos de las horas trabajadas según una tipología:
Hora tipo A = Faltas
Hora tipo B = Bono
Hora tipo C = Horas al 50%
Hora tipo D = Horas al 100%
En esta plantilla nos encontramos, entonces, información alfanumérica tipo 1A, 3B, 10C, etc reflejando el número de horas empleadas en el mes junto al tipo de horas.
Se trata entonces de resumir la información de todo el año, según dicha tipología, es decir, cuantas horas en todo el año ha empleado tal trabajador de tipo A, B, C ó D.
Para ello aplicaremos una función matricial (validada presionando Ctrl+Mayusc+Enter):
=SUMA(SI.ERROR(VALOR(SUSTITUIR($B3:$M3;O$2;""));0))
Pero ¿cómo funciona esta fórmula?...en primer lugar, empleamos la función SUSTITUIR, mediante la cual, eliminamos, letra a letra, para cada mes del rango B:M y cada empleado, dejando, por tanto sólo los valores numéricos; para asegurarnos que luego podremos trabajar con este valor numérico, es decir, que no habrá problemas en que Excel lo trate como texto (a pesar de ser un número, que por otro lado es lo más normal), anidamos esta función dentro de otra VALOR.
Ya tenemos en el rango $B3:$M3 una serie de valores numéricos junto a otros que siguen siendo alfanuméricos, que al haberles aplicado VALOR, se habrán convertido en un error. Si a este rango de errores y valores numéricos, le aplicamos el condicional SI.ERROR, se queda un rango final de valores con horas, para aquellos alfanuméricos que correspondían a la letra sustituida, y un montón de ceros para los demás; asi que sólo nos queda sumar ese rango con la función SUMA.
Podemos ver las tres etapas de la fórmula en la imagen siguiente:
Finalmente podemos comprobar como efectivamente, columna a columna, tipo de hora a tipo de hora, sumamos para cada empleado el parcial acumulado de todo el año:
Veámoslo mejor en una imagen:
Como vemos tenemos una plantilla para ciertos trabajadores, en los que informamos de las horas trabajadas según una tipología:
Hora tipo A = Faltas
Hora tipo B = Bono
Hora tipo C = Horas al 50%
Hora tipo D = Horas al 100%
En esta plantilla nos encontramos, entonces, información alfanumérica tipo 1A, 3B, 10C, etc reflejando el número de horas empleadas en el mes junto al tipo de horas.
Se trata entonces de resumir la información de todo el año, según dicha tipología, es decir, cuantas horas en todo el año ha empleado tal trabajador de tipo A, B, C ó D.
Para ello aplicaremos una función matricial (validada presionando Ctrl+Mayusc+Enter):
=SUMA(SI.ERROR(VALOR(SUSTITUIR($B3:$M3;O$2;""));0))
Pero ¿cómo funciona esta fórmula?...en primer lugar, empleamos la función SUSTITUIR, mediante la cual, eliminamos, letra a letra, para cada mes del rango B:M y cada empleado, dejando, por tanto sólo los valores numéricos; para asegurarnos que luego podremos trabajar con este valor numérico, es decir, que no habrá problemas en que Excel lo trate como texto (a pesar de ser un número, que por otro lado es lo más normal), anidamos esta función dentro de otra VALOR.
Ya tenemos en el rango $B3:$M3 una serie de valores numéricos junto a otros que siguen siendo alfanuméricos, que al haberles aplicado VALOR, se habrán convertido en un error. Si a este rango de errores y valores numéricos, le aplicamos el condicional SI.ERROR, se queda un rango final de valores con horas, para aquellos alfanuméricos que correspondían a la letra sustituida, y un montón de ceros para los demás; asi que sólo nos queda sumar ese rango con la función SUMA.
Podemos ver las tres etapas de la fórmula en la imagen siguiente:
Finalmente podemos comprobar como efectivamente, columna a columna, tipo de hora a tipo de hora, sumamos para cada empleado el parcial acumulado de todo el año:
Hola,
ResponderEliminarCuando lo intento hacer recreando tus pasos, me sale el error nombre. ¿Qué puede ser? ¿Me puedes mandar el excel para ver si lo puedo averiguar?
Muchas gracias!
Hola José Manuel,
Eliminarsi, claro, dime tu correo o envíame la petición a
excelforo@gmail.com
Es posible estés trabajando con Excel 2003 y no te reconozca la función SI.ERROR; si es este tu caso, podrías probar con esta otra fórmula matricial:
=SUMA(SI(ESERROR(VALOR(SUSTITUIR($B3:$M3;O$2;""));0;VALOR(SUSTITUIR($B3:$M3;O$2;""))))
Slds
Ok, al final te lo he mandado por email. Como te comento, tengo excel 2010 en español.
EliminarMuchas gracias!
prueba a poner comillas si introduces algun nombre
Eliminarhola como hago para colocar en esta formula mas condiciones
ResponderEliminar=CONTAR.SI.CONJUNTO(AD2:BN2;"=pronto 1" que sea tambien pronto 2 etc
Hola,
Eliminarincluir más condiciones en la función tipo CONTAR.SI.CONJUNTO es sencillo, basta ir añadiendo rango de criterio y el criterio:
=CONTAR.SI.CONJUNTO(AD2:BN2;"pronto 1";AD2:BN2;"pronto 2";AD2:BN2;"pronto 3")
pero OJO!! cada nueva condición hace la función más restrictiva, esto es, lo que haces al incluir una segunda, tercera, etc, condición, estamos añadiendo una condición Y, estamos indicando que cuente los casos en que a la vez/simultáneamente se verifica que es 'pronto1', 'pronto2' y 'pronto3'... lo que parece poco probable.
Para contar cuando sea 'pronto1' O 'pronto2' O 'pronto3' tendrás que sumar:
=CONTAR.SI(AD2:BN2;"pronto 1")+CONTAR.SI(AD2:BN2;"pronto 2")+CONTAR.SI(AD2:BN2;"pronto 3")
que tiene más sentido...
Saludos
me da cero, fijate en 2010, te agradecria si me das el archivo de excel con la formula, pues la veo fantastica para dar unas clases que estoy empezando gracias
ResponderEliminarprueba1303@hotmail.com
Hola Bellas Nicas,
Eliminarlo siento pero no guardo los archivos tan viejos.
Saludos
Este comentario ha sido eliminado por el autor.
ResponderEliminarHola, tu tutorial es justo lo que necesito para resolver un problema que tengo. Pero al seguir los pasos que pones, me da como resultado "0"....que puede ser?
ResponderEliminarHola,
Eliminares una función matricial.. ?te has asegurado de validarla presionando Ctrl+Mayusc+Enter en lugar de solo enter?
Slds
Muchas gracias por responder. Ese era el problema, funciona perfecto!!
EliminarEste comentario ha sido eliminado por el autor.
ResponderEliminarHola Ismael,
ResponderEliminarTengo una tabla con datos numéricos y alfanuméricos y necesito hacer sumatorias de estos valores por columna, para que me sume los valores alfanuméricos tengo la formula =SUMAPRODUCTO(--(IZQUIERDA(F1:F30))) pero los valores que tienen decimales me los suma como enteros, el resultado me da un numero entero. Mi pregunta es como puedo hacer para que también sume los decimales. Agradezco mucho tu ayuda.
Hola Johana,
Eliminaral emplear IZQUIERDA solo, sin especificar el segundo argumento, estará cogiendo solo el primer caracter por la izquierda... lo que entiendo será siempre la parte entera (un solo dígito), y obviamente su suma será también un número entero.
Si tienes mezclados alfanuméricos con parte entera y decimal, tendrás que buscar un patrón que se cumpla en todos los casos (números enteros, incluso de varios dígitos, y números con decimales).
Echa un vistazo a
https://excelforo.blogspot.com/2015/12/vba-una-funcion-para-extraer-caracteres.html
Te puede dar pistas de cómo trabajar
Saludos