lunes, 2 de julio de 2012

Sumar alfanuméricos en Excel.

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:

Sumar alfanuméricos en Excel.


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:

Sumar alfanuméricos en Excel.

15 comentarios:

  1. Hola,

    Cuando 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!

    ResponderEliminar
    Respuestas
    1. Hola José Manuel,
      si, 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

      Eliminar
    2. Ok, al final te lo he mandado por email. Como te comento, tengo excel 2010 en español.
      Muchas gracias!

      Eliminar
    3. prueba a poner comillas si introduces algun nombre

      Eliminar
  2. hola como hago para colocar en esta formula mas condiciones
    =CONTAR.SI.CONJUNTO(AD2:BN2;"=pronto 1" que sea tambien pronto 2 etc

    ResponderEliminar
    Respuestas
    1. Hola,
      incluir 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

      Eliminar
  3. 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
    prueba1303@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Bellas Nicas,
      lo siento pero no guardo los archivos tan viejos.
      Saludos

      Eliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. Hola, 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?

    ResponderEliminar
    Respuestas
    1. Hola,
      es una función matricial.. ?te has asegurado de validarla presionando Ctrl+Mayusc+Enter en lugar de solo enter?
      Slds

      Eliminar
    2. Muchas gracias por responder. Ese era el problema, funciona perfecto!!

      Eliminar
  6. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  7. Hola Ismael,

    Tengo 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.

    ResponderEliminar
    Respuestas
    1. Hola Johana,
      al 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

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.