Foro Excel

HOLA FOREROS DE EXCEL:
Tengo un problema con un libro de excel que no se como solucionar y que a continuación os expongo:

1.- Tengo un libro de excel que se compone de tres hojas, en la hoja 1 estan los datos de los empleados de una empresa que cada mes que pasa aumentan los datos tal y como aparece en el ejemplo; en esta hoja estan los nombres de los empleados de una empresa, con su base, cuota y seguridad social. Aquí­ he puesto a la columna B=hoja1!$B:$B NOMBRE, a la C=hoja1!$C:$C BASE, a la D=hoja1!$D:$D CUOTAy a la E=hoja1!$E:$E SS.
2.- Luego en la hoja 2 tomo todos los nombres de los empleados, los ordeno y con la sentencia =si(a2=a1;0;a2) elimino los nombres repetidos.
3.- Una vez que tengo los nombres que no estan repetidos los pongo en la hoja 3 y en la columna B pongo =SUMAR.SI(NOMBRE;A2;BASE) y así­ con el resto.

Mi problema es que cada vez que transcurre el tiempo debo de reperir los pasos 2 y 3, por ello me gustarí­a saber si hay alguna solución algo más automática, ya que suelo trabajar con cerca de 5.000 registros.

En definitiva necesito una formula que sume las bases, cuotas y ss de cada empleado totalizando por empleado.

Un saludo y gracias de antemano.

Hola franziskaner1789

Revisa el adjunto. Quizás algo así­ te sirva, deberás tener las funciones en tantas filas como personas preveas.

Verás que he modificado los rangos nombrados.

Un saludo desde Vitoria

Hola ioyama como siempre muchas gracias por tu ayuda.
Salu2

Hola de nuevo Ioyama, he revisado el ejemplo que has realizado y la verdad es que es muy avanzado para mí­, me podrí­as indicar como funciona.
La verdad es que no me entero.

Salu2 desde Castro.

Hola franziskaner1789

Me imagino que tu problema estará en la función matricial

{=SI(O(ESERROR(INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0)));INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0))=0);"";INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0)))}

Por partes (y olvidando inicialmente que es matricial):
La función empieza con un condiciional (SI) la condición es para evitar que te aparezcan errores (por ejemplo #N/A) cuando ho hay datos O(ESERROR(INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0)));INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0))=0)
pongo dos condiciones, que la función principal de errore (ESERROR) o que sea =0.
Si se produce el error te devuelve "", es decir te deja la celda en blanco (ten en cuenta que he puesto la función hasta la fila 28 y sólo hay datos en 4, el resto te aparecerí­an con 0 o errores).

Si no hay error es cuando se aplica la función que nos interesa
=INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0))
INDICE nos devuelve el valor de la intersección de una fila y una columna en un rango
El rango va de B2 a B10000 he considerado que con 10000 filas serí­a suficiente (podí­amos haber "automatizado la toma del último valor a base de complicar la función), como puedes ver el rango será una columna
La fila que buscamos dentro de ese rango viene dada por COINCIDIR
COINCIDIR nos devuelve la posición relativa de un elemento de una matriz que coincide con un valor
El valor que buscamos es 0 y la matriz de búsqueda viene dada por
--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0))
dejamos de momento --ESNUMERO y veamos
COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0)
Nuevamente usamos la función coincidir como en el caso anterior lo que buscamos ahora es un valor dado por el rango Hoja1!$B$2:$B$10000 (matriz)
Este valor lo buscamos en el rango Hoja2!$A$1:A1, verás que la primera referencia es absoluta y la segunda relativa, por lo que al copiar la función en filas sucesivas el rango se va a ir incrementando Hoja2!$A$1:A2, Hoja2!$A$1:A3,..., el tipo de coincidencia es 0 o lo que es lo mismo el primer valor que es exactamente igual al valor buscado.
Si encuentra el valor nos devolverá su posición, y si no lo encuentra un error
Ahora viene la parte en la que no sé si me explicaré bien,
con --ESNUMERO hacemos lo siguiente, ESNUMERO comprueba si el valor encontrado es un número con lo que devuelve VERDADERO o no lo es, con lo que devuelve FALSO. Con el doble guión lo que hacemos es pasar el verdadero a 1 (unos) y el falso a 0 (ceros).
Si recuerdas tras este rollo (nada fácil de entender la primera vez) en el primer coincidir el valor que buscábamos era un 0 y lo buscábamos en el rango dado por --ESNUMERO(.......), rango que ahora hemos transformado en un rango de ceros y unos.
Así­ pues llegamos a la funció INDICE con unos valores que le permitiran devolvernos cada primer nombre distinto del listado de nombres.

La función debe ser matricial, ya que trabajaremos con conjuntos de valores y se introduce con Mayúsculas+Control+Intro

Luego

En la primera fila (la 2, ya que en la primera está el tí­tulo de columna) la función busca el listado de nombres en el rango $A1:A1 de la Hoja 2 , obviamente no lo encuentra ya que sólo está el tí­tulo, luego el segundo coincidir nos devolverá 0 (error realmente, pero con --ESNUMERO lo pasa a 0) como primer valor de la matriz de vuelta; el segundo coincidir nos va a decir Que la primera coincidencia con el valor 0 es el primer elemento 1, así­ INDICE nos devolvera el elemento 1 del listado.
En la segunda fila (la 3) la función es casi igual pero el rango de la Hoja2 pasa a ser el rango $A1:A2, es decir el tí­tulo y el primer nombre del listado, ahora la función se encontrará que el segundo coincidir nos devolverá como primer término de la matriz encontrada 1 ya que el primer nombre del listado ya existe en el rango de la hoja anterior (en la fila 2), y como segundo valor 0 (si el segundo nombre del listado fuera igual que el primero nos devolverí­a 1), luego el segundo coincidir nos dará como valor de vuelta 2 e INDICE nos devolverá el segundo nombre.
Si seguimos copiando la función en la fila 4 tendremos Hoja2!$A$1:A4 y si revisamos los valores devueltos por cada parte de la función veremos que la matriz de ceros y unos queda como {1\1\1\1\1\1\1\1\1\0\......} con lo que el segundo coincidir nos devuelve 10 e INDICE nos dará el décimo valor de la lista para la fila 4. etc.

No sé si he conseguido explicarme, lo he repasado y parece que no he bailado nada de sitio.

Si siguel sin entender algo lo dices Y si alguien puede dar una explicación más clara que no se corte.

Un saludo desde Vitoria

P.S. Es más que probable que este sábado me pase por tu bonita localidad, esperemos que el tiempo medio acompañe.

Hola Ioyama, sigo teniendo unas dudas:

1.- Como copio mas a alla de la fila 28 tu funcion.

2.- He introducido en la hoja 1 un nuevo empleado con las cantidades : 1000 de base, 100 de cuotas y 10 SS ; pero me acumula como 2135,85, 251,25 y 95,14 respectivamente.

3.- Como acumulas, te lo pregunto porque no veo el rangos nombrados para realizar el sumar.si.

Salu2 desde Castro.

Hola franziskaner1789

Respecto a tus dudas
1º "Como copio mas a alla de la fila 28 tu función"
Selecciona el rango A28:D28, copia (control + C por ejemplo, o botón derecho copiar))
Selecciona el rango donde quieras copiar (por ejemplo A29:A200)
Pega

2º "He introducido en la hoja 1 un nuevo emplea...."
El rango nombre estaba mal definido, deberí­a quedar como
=DESREF(Hoja1!$B$2;;;CONTARA(Hoja1!$B:$B)-1)

3º "Como acumulas, te lo pregunto porque no veo el rangos nombrados para ..."
No sé a qué te refieres, en las tres columnas que aparece SUMAR.SI se emplea siempre el rango nombre (el que estaba mal definido) en las tres y en cada una de ellas el correspondiente rango llamados base, cuota y ss respectivamente.
Todos los nombres están definidos con DESREF para que los rangos sea dinámicos (o como dirí­a Gali, con gran razón por cierto), las referencias sean dinámicas).
Si vas al menú Insertar > Nombre > Definir, seleccionas uno de ellos y sitúas el cursor en la casilla Se refiere a.... verás que te recuadra en discontí­nuo el rango correspondiente. Sal, añade un nuevo dato a esa columna y vuelve a repetir el proceso de Insertar > Nombre , etc. , verás que ahora el recuadro te engloba también el nuevo dato sin que hayas tenido que modificar nada en la definición del nombre. (obviamente esto sólo es para comprobar que al añadir datos se agregan al rango definido con DESREF, no es preciso ir al menú Insertar > nombre etc, etc para que funcione).

Si siguel con alguna pega lo dices.

Un saludo desde Vitoria

P.S. Te adjunto el archivo con el nombre corregido.

Hola Ioyama !

Con respecto a tu explicación me surgieron algunas dudas que quizá me puedas aclarar.

Cual es la diferencia entre una función matricial y una función anidada ? La función SUMAPRODUCTO que serí­a ? Seguramente sea algo básico pero bueno, no querí­a dejar pasar la oportunidad de preguntar.

En cuanto a tu explicación de la función que le pasaste a Franziskaner, la intentaré de descifrar aunque supongo que en mi nivel de excel no la voy a comprender completamente todaví­a.

Saludos desde Buenos Aires y muchas gracias.

Tomás

Para entender las funciones matriciales, si estuviera en tu lugar, esperarí­a un poco mas...no mucho...pero un poco mas.....
Es mas te conviene ver un articulo de F KABEL que tradujo xpelos sobre SUMAPRODUCTO, que es excelente, una vez entiendas el manejo de SUMPRODUCTO, que aunque no lo sea, es en sí­ una matricial.
Si alguna vez viste en matematicas: el uso de { [ ( el concepto de anidacion de funciones es asimilable a las Muñecas Rusas, que ddentro contienen otra Muñeca, bueno tambien el concepto de anidacion es paangonable al de Composicion de Funciones, Es decir Una funcion devuelve un Valor (para facilitar dejemops esta afirmacion sin retoques), si esta anidada, este valor pasa a ser argumento para la Funcion contenedora.....

Trataré de ir leyendo sobre el tema e ir entendiendo cada vez más.

Voy a buscar esa traducción que mencionás.

Gracias !!

Saludos,

Tomás

Hola Ioyama, ya me funciona, pero quisiera que me explicases unas dudas que tengo al respecto:

1.- La sentencia =DESREF (Hoja1!$B$2;;;CONTARA(Hoja1!$B:$B)-1) ¿Cómo funciona? ¿Qué hace?

2.- Que diferencia hay entre la sentencia =SI(A2="";"";SUMAR.SI(NOMBRE;A2;BASE) y SUMAR.SI(NOMBRE;A2;BASE)

3.- A la sentencia siguiente: {=SI(O(ESERROR(INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0)));INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0))=0);"";INDICE(Hoja1!$B$2:$B$10000;COINCIDIR(0;--ESNUMERO(COINCIDIR(Hoja1!$B$2:$B$10000;Hoja2!$A$1:A1;0));0)))}
¿Se le puede añadir algo para que aparezcan los nombres ordenados alfabeticamente?
Muchas gracias por todo.
Salu2 desde Castro

Te mando una version con tablas dinamicas....

Hola dranziskaner 1789

1.- DESREF
textualmente de la ayuda de excel
Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver.
Sintaxis
DESREF(ref;filas;columnas;alto;ancho)
Ref es la referencia en la que desea basar la desviación. Ref debe referirse a una celda o rango de celdas adyacentes; en caso contrario, DESREF devuelve el valor de error #¡VALOR!
Filas es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior izquierda de la referencia pasa a estar cinco filas más abajo de la referencia. Filas puede ser positivo (lo que significa por debajo de la referencia de inicio) o negativo (por encima).
Columnas es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda superior izquierda del resultado. Si el argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).
Alto es el alto, en número de filas, que desea que tenga la referencia devuelta. El alto debe ser un número positivo.
Ancho es el ancho, en número de columnas, que desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo.
ej. =DESREF(C3;2;3;1;1) Muestra el valor en la celda F5 (0)
En tu caso
=DESREF (Hoja1!$B$2;;;CONTARA(Hoja1!$B:$B)-1)
referencia: Hoja1!$B$2
filas: 0
columnas: 0
alto: CONTARA(Hoja1!$B:$B)-1, tantas celdas copmo elementos haya en la columna B menos uno correspondiente al tí­tulo de la columna en este caso.
ancho: 0

2.- Que diferencia hay entre la sentencia =SI(A2="";"";SUMAR.SI(NOMBRE;A2;BASE) y SUMAR.SI(NOMBRE;A2;BASE)
En la primera lo que hacemos es usar la función condicional para en el caso de que la celda A2 esté vací­a nos devuelva vací­o. DE lo contrario en tu caso te devolverí­a 0 en cada celda cuya correspondiente de la columna A no haya ningún nombre.

3.- "....A la sentencia .... ¿Se le puede añadir algo para que aparezcan los nombres ordenados alfabeticamente? .."
Tendrí­a que pensar que se puede hacer (complicando la función final que obtengamos).
No obstante la opción que te proporciona Gali es muy buena (una vez más ni me acordé de la posibilidad de usar tablas dinámicas). Incluso te ha puesto una refencia dinámica para que el rango afectado se adecúe al añadir o eliminar datos y una macro de evento que al activar la página de la tabla la recalcula.

Por otra parte, esta opción seguramente será bastante más rápida (el uso de funciones matriciales ralentiza mucho la velocidad de cálculo)

Un saludo desde Vitoria

Gali, muchas gracias me ha servido de mucho.

Por cierto me podrí­as indicar ¿donde puedo conseguir la traducción de Xpelos de Sumaproducto de F Kabel?

Esto muy interesado, ya que me parece un mundo las matrices.

Salu2 desde Castro.

Ioyama muchas gracias por todo ha sido de mucha utilidad tus explicaciones, ya que desconocia la funcion DESREF y todo lo que lleva aparejado.

Salu2 desde Castro y buen finde.