FÓRMULAS VARIAS

 

Página inicial

 

 

Notas:

 

 

Poner en una celda el nombre del libro:   [Ejemplo]

=EXTRAE(CELDA("nombrearchivo");ENCONTRAR("[";CELDA("nombrearchivo"))+1;ENCONTRAR("]";CELDA("nombrearchivo"))-ENCONTRAR("[";CELDA("nombrearchivo"))-1)

Nota: para que esto funcione es necesario haber guardado el libro al menos una vez.

 

Poner en una celda el nombre de la hoja:   [Ejemplo]

=EXTRAE(CELDA("nombrearchivo";A1);ENCONTRAR("]";CELDA("nombrearchivo";A1))+1;32)

 

Si el libro tiene una sola hoja y además libro y hoja se llaman igual, es necesario complicar bastante la fórmula:  [Ejemplo]

 

=SI(ESERROR(EXTRAE(CELDA("nombrearchivo";A1);ENCONTRAR("]";CELDA("nombrearchivo";A1))+1;32));EXTRAE(SUSTITUIR(CELDA("nombrearchivo";A1);"\";"*";LARGO(CELDA("nombrearchivo";A1))-LARGO(SUSTITUIR(CELDA("nombrearchivo";A1);"\";"")));ENCONTRAR("*";SUSTITUIR(CELDA("nombrearchivo";A1);"\";"*";LARGO(CELDA("nombrearchivo";A1))-LARGO(SUSTITUIR(CELDA("nombrearchivo";A1);"\";""))))+1;32);EXTRAE(CELDA("nombrearchivo";A1);ENCONTRAR("]";CELDA("nombrearchivo";A1))+1;32))

 

Saber la edad exacta (o cualquier otro período de tiempo) expresada en años, meses y días:   [Ejemplo]

=SIFECHA(A1;HOY();"y") & " años " & SIFECHA(A1;HOY();"ym") & " meses " & SIFECHA(A1;HOY();"md") & " días"

(suponiendo que la fecha de nacimiento estuviera en A1 y que se quisiera saber la edad a fecha de hoy).

 

Si no se desea que aparezcan incoherencias como, por ejemplo, "0 meses" o "1 días", se puede usar una fórmula más compleja:

=SI(SIFECHA(A1;HOY();"y")>0;SIFECHA(A1;HOY();"y")&" "&ELEGIR(MIN(SIFECHA(A1;HOY();"y")+1;3);"";"año ";"años ");"")&SI(SIFECHA(A1;HOY();"ym")>0;SIFECHA(A1;HOY();"ym")&" "&ELEGIR(MIN(SIFECHA(A1;HOY();"ym")+1;3);"";"mes ";"meses ");"")&SI(SIFECHA(A1;HOY();"md")>0;SIFECHA(A1;HOY();"md")&" "&ELEGIR(MIN(SIFECHA(A1;HOY();"md")+1;3);"";"día ";"días ");"")

 

 

Sumar las celdas situadas en las filas impares de una columna:   [Ejemplo]

{=SUMA((RESIDUO(FILA(A1:A100);2)=1)*A1:A100)}

ó

=SUMAPRODUCTO((RESIDUO(FILA(A1:A100);2)=1)*A1:A100)

 

Sumar las celdas situadas en las filas pares de una columna:   [Ejemplo]

{=SUMA((RESIDUO(FILA(A1:A100);2)=0)*A1:A100)}

ó

=SUMAPRODUCTO((RESIDUO(FILA(A1:A100);2)=0)*A1:A100)

 

Sumar las celdas situadas en las columnas “impares” de una fila (A,C,E, etc.):  [Ejemplo]

{=SUMA((RESIDUO(COLUMNA(A1:D1);2)=1)*(A1:D1))}

ó

=SUMAPRODUCTO((RESIDUO(COLUMNA(A1:G1);2)=1)*A1:G1)

 

Sumar las celdas situadas en las columnas “pares” de una fila (B,D,F, etc.):   [Ejemplo]

{=SUMA((RESIDUO(COLUMNA(A1:D1);2)=0)*(A1:D1))}

ó

=SUMAPRODUCTO((RESIDUO(COLUMNA(A1:G1);2)=0)*A1:G1)

 

Saber el número de elementos distintos que hay en un rango:  [Ejemplo]

Si en el rango no hay celdas vacías, se puede usar:

{=SUMA(1/CONTAR.SI(A1:A10;A1:A10))}

ó

=SUMAPRODUCTO(1/CONTAR.SI(A1:A10;A1:A10))

 

Si hubiera o pudiera haber celdas vacías:

{=SUMA((A1:A10<>"")/CONTAR.SI(A1:A10;A1:A10&""))}

ó

=SUMAPRODUCTO((A1:A10<>"")/CONTAR.SI(A1:A10;A1:A10&""))

(lógicamente, estas dos funciones se pueden usar también si no hay celdas vacías)

 

y si sólo hubiera números en las celdas se podría usar:

=SUMA(SIGNO(FRECUENCIA(A1:A10;A1:A10)))

que sirve tanto si hay celdas vacías como si no las hay.

 

Hallar la suma de los valores únicos en un rango:  [Ejemplo]

Si se necesita saber la suma de los valores únicos de un rango con nombre llamado Lista (es decir, sumando una sola vez cada valor aunque esté más de una vez en el rango), se puede usar la fórmula:

{=SUMA(Lista/CONTAR.SI(Lista;Lista))}

Lo mismo hace;

=SUMAPRODUCTO(Lista/CONTAR.SI(Lista;Lista))

 

Nota: el rango no puede tener ni celdas vacías, ni textos, ni valores lógicos. Si hubiera fechas, la fórmula las consideraría números.

 

 

Mostrar los datos de una lista omitiendo los duplicados  [Ejemplo]

Suponiendo que en un rango con nombre llamado 'Lista' (de una sola columna y sin celdas vacías) hay un conjunto de datos uno o más de los cuales pueden estar repetidos, y que se necesita una lista en la que se hayan eliminado los duplicados, se podría usar:

{=INDICE(Lista;K.ESIMO.MENOR(SI(COINCIDIR(Lista;Lista;0)=FILA(INDIRECTO("1:"&CONTARA(Lista)));COINCIDIR(Lista;Lista;0);"");FILA()))}

 

La fórmula anterior habría que ponerla en la fila 1 de una columna cualquiera, y copiarla hacia abajo. Cuando no quedaran más datos que mostrar, comenzaría a aparecer el error #¡NUM!

 

Si la fórmula matricial hubiera de estar situada en una fila distinta a la 1, habría que restar el número de dicha fila menos uno en la segunda vez que aparece la función FILA(). Por ejemplo, para empezar en la fila 5:

{=INDICE(Lista;K.ESIMO.MENOR(SI(COINCIDIR(Lista;Lista;0)=FILA(INDIRECTO("1:"&CONTARA(Lista)));COINCIDIR(Lista;Lista;0);"");FILA()-4))}

 

Si el rango con nombre 'Lista' estuviera dispuesto en una fila en lugar de en una columna, las fórmulas serían:

{=INDICE(Lista;K.ESIMO.MENOR(SI(TRANSPONER(COINCIDIR(Lista;Lista;0))=FILA(INDIRECTO("1:"&CONTARA(Lista)));TRANSPONER(COINCIDIR(Lista;Lista;0));"");FILA()))}

 

e

 

{=INDICE(Lista;K.ESIMO.MENOR(SI(TRANSPONER(COINCIDIR(Lista;Lista;0))=FILA(INDIRECTO("1:"&CONTARA(Lista)));TRANSPONER(COINCIDIR(Lista;Lista;0));"");FILA()-4))}

 

 

Obtener una lista ordenada de los valores únicos de un rango.  [Ejemplo]

Suponiendo que en un rango con nombre llamado "Lista" (de una sola columna y sin celdas vacías) hay un conjunto de valores que pueden estar repetidos, y que se necesita una lista ordenada de mayor a menor de los valores únicos (sin repeticiones), la fórmula sería:

{=SI(K.ESIMO.MAYOR(SI(CONTAR.SI(DESREF(Lista;;;FILA(INDIRECTO("1:"&CONTAR(Lista))));Lista)=1;Lista;MIN(Lista)-1);FILA())>=MIN(Lista);K.ESIMO.MAYOR(SI(CONTAR.SI(DESREF(Lista;;;FILA(INDIRECTO("1:"&CONTAR(Lista))));Lista)=1;Lista;MIN(Lista)-1);FILA());"Valor repetido")}

 

Si se deseara que la lista estuviera ordenada de menor a mayor, la fórmula sería:

{=SI(K.ESIMO.MENOR(SI(CONTAR.SI(DESREF(Lista;;;FILA(INDIRECTO("1:"&CONTAR(Lista))));Lista)=1;Lista;MAX(Lista)+1);FILA())<=MAX(Lista);K.ESIMO.MENOR(SI(CONTAR.SI(DESREF(Lista;;;FILA(INDIRECTO("1:"&CONTAR(Lista))));Lista)=1;Lista;MAX(Lista)+1);FILA());"Valor repetido")}

 

Nota: las listas generadas por ambas fórmulas también pueden servir si lo que se necesita es una jerarquización de los elementos únicos del rango.

 

 

Números de puesto únicos.   [Ejemplo]

Si, por ejemplo, se tiene un rango con nombre llamado Notas, en el que las notas están repetidas (p.ej., 2 dieces, 2 nueves, etc.) y se desea obtener el puesto único de cada una de ellas (los dos dieces el primer puesto, los dos nueves el segundo, etc.), la fórmula sería:

 

{=COINCIDIR(A2;K.ESIMO.MAYOR(SI(FRECUENCIA(Notas;Notas)<>0;Notas);FILA(INDIRECTO("1:"&SUMA(1/CONTAR.SI(Notas;Notas)))));0)}

 

Sustituyendo K.ESIMO.MAYOR por K.ESIMO.MENOR es posible obtener los puestos únicos, pero otorgándole a la nota más baja el puesto más alto y así con todas las demás.

 

 

 

Averiguar los valores que faltan en una serie de números enteros  [Ejemplo]

Si se quiere un listado de los números que faltan en una serie de números enteros positivos llamada Lista, se podría usar la fórmula:

 

{=K.ESIMO.MAYOR(SI(CONTAR.SI(Lista;FILA(INDIRECTO(MIN(Lista)&":"&MAX(Lista))))=0;FILA(INDIRECTO(MIN(Lista)&":"&MAX(Lista)));0);FILA())}

 

La fórmula habría que ponerla en la fila 1 de una columna vacía y copiarla hacia abajo. Los números faltantes irían apareciendo de mayor a menor, y cuando no quedaran más números faltantes, comenzarían a aparecer ceros.

 

Si se necesitara que los números faltantes fueran apareciendo ordenados de menor a mayor, se podría usar la fórmula:

 

{=K.ESIMO.MENOR(SI(CONTAR.SI(Lista;FILA(INDIRECTO(MIN(Lista)&":"&MAX(Lista))))=0;FILA(INDIRECTO(MIN(Lista)&":"&MAX(Lista)));FALSO);FILA())}

 

En este caso, cuando no queden más números faltantes comenzará a mostrarse el error #!NUM¡

 

La serie de números no tiene que estar necesariamente ordenada ni de mayor a menor ni a la inversa, si bien el ejemplo lo está para mayor claridad. Tampoco sucede nada si uno o más números de la serie están repetidos.

 

He recibido una consulta que plantea una situación interesante que me ha hecho escribir una nueva fórmula: se trata de averiguar qué números faltan en una serie cuyo número inicial es muy grande, por ejemplo 10010003456.

 

En este caso, las formulas anteriores fallarían porque no pueden procesar números superiores al máximo de filas de Excel (65536 en la versión 2003), así que en este caso y similares habría que usar:

 

{=MIN(Lista)-1+K.ESIMO.MAYOR(SI(ESERROR(COINCIDIR(FILA(INDIRECTO(MIN(Lista)-MIN(Lista)+1&":"&MAX(Lista)-MIN(Lista)+1));Lista-MIN(Lista)+1;0));FILA(INDIRECTO(MIN(Lista)-MIN(Lista)+1&":"&MAX(Lista)-MIN(Lista)+1));"");FILA())}

 

para que los números faltantes aparecieran ordenados de menor a mayor, y sustituir .MENOR por .MAYOR si el orden deseado fuera el inverso.

 

Lógicamente, esta última fórmula puede usarse también si la serie comienza con cualquier número más bajo. Otra ventaja de esta última fórmula es que puede procesar números enteros negativos y positivos. El inconveniente es que es más compleja que la primera.

 

 

 

Obtener un valor aleatorio de entre las filas con datos de una columna:  [Ejemplo]

=INDICE(A:A;REDONDEAR(ALEATORIO()*(CONTARA(A:A)-1)+1;0))

En este caso, los datos estarían en la columna A.

Los datos deben empezar en la fila 1 y no tener celdas vacías entre medias.

 

Contar y/o sumar las celdas cuyo valor se encuentra entre dos valores dados:  [Ejemplo]

=SUMAPRODUCTO((A1:A25>=5)*(A1:A25<=15))

o, usando la función CONTAR.SI:

=CONTAR.SI(A1:A25;">=5")-CONTAR.SI(A1:A25;">15")

Ambas fórmulas devolverían el número de celdas cuyo valor estuviera entre 5 y 15 en el rango A1:A25.

 

=SUMAPRODUCTO((A1:A25>=5)*(A1:A25<=15)*A1:A25)

o, usando la función CONTAR.SI:

=SUMAR.SI(A1:A25;">=5")-SUMAR.SI(A1:A25;">15")

Ambas fórmulas devolverían la suma de los números cuyo valor estuvieran entre 5 y 15 en el rango A1:A25

 

 

Contar las celdas de un determinado mes y año:

=SUMAPRODUCTO((MES(A1:A15)=1)*(AÑO(A1:A15)=2009))

En este caso, se contarían las celdas en el rango A1:A15 cuya fecha fuera del mes de enero del año 2009.

o, usando las funciones CONTAR.SI y FECHA:

=CONTAR.SI(A1:A15;">="&FECHA(2009;1;1))-CONTAR.SI(A1:A15;">"&FECHA(2009;1;31))

 

 

Sumar los X valores mayores o menores en una columna:  [Ejemplo]

{=SUMA(K.ESIMO.MAYOR(A1:A50;FILA(INDIRECTO("1:3"))))}

o, usando SUMAPRODUCTO:

=SUMAPRODUCTO(K.ESIMO.MAYOR(A1:A50;FILA(INDIRECTO("1:3"))))

En este caso, se sumarían los 3 valores mayores del rango A1:A50

Para sumar los 3 valores menores, habría que sustituir K.ESIMO.MAYOR por K.ESIMO.MENOR

 

 

 

Nota: Las cuatro fórmulas siguientes son ejemplos que trabajan con el rango A1:A100.

Saber el número de la primera fila con datos en una columna:

{=MIN(SI(A1:A100<>"";FILA(A1:A100)))}

 

Saber el valor de la primera fila con datos en una columna:

{=INDICE(A1:A100;MIN(SI(A1:A100<>"";FILA(A1:A100))))}

 

Saber el número de la última fila con datos en una columna:

{=MAX((A1:A100<>"")*FILA(A1:A100))}

 

Saber el valor de la última fila con datos en una columna:

=BUSCAR(2;1/(A1:A100<>"");A1:A100)

{=INDICE(A1:A100;MAX(SI(A1:A100<>"";FILA(A1:A100))))}

 

 

 

 

Nota: Las cuatro fórmulas siguientes son ejemplos que trabajan con el rango A1:Z1

Averiguar el número de la primera columna con datos en una fila:

{=MIN(SI(A1:Z1<>"";COLUMNA(A1:Z1)))}

 

Averiguar el valor de la primera columna con datos en una fila:

{=INDICE(A1:Z1;;MIN(SI(A1:Z1<>"";COLUMNA(A1:Z1))))}

 

Averiguar el número de la última columna con datos en una fila:

{=MAX((A1:Z1<>"")*COLUMNA(A1:Z1))}

 

Averiguar el valor de la última columna con datos en una fila:

=BUSCAR(2;1/(A1:Z1<>"");A1:Z1)

{=INDICE(A1:Z1;;MAX(SI(A1:Z1<>"";COLUMNA(A1:Z1))))}

 

 

 

Fórmulas para conocer el número de la primera o última fila cuyo valor es distinto de algo (o su valor):

Imagen (13 kb)

Partiendo del ejemplo anterior, si se necesita:

{=MIN(SI(A1:A10<>"a";FILA(A1:A10)))}

 

{=DESREF(A1;MIN(SI(A1:A10<>"a";FILA(A1:A10)))-1;0)}

 

{=MAX(SI(A1:A10<>"a";FILA(A1:A10)))}

 

{=DESREF(A1;MAX(SI(A1:A10<>"a";FILA(A1:A10)))-1;0)}

 

Si hubiera o pudiera haber celdas vacías en el rango a evaluar, habría que sustituir las dos primeras fórmulas por:

{=MIN(SI(A1:A10<>"a";SI(A1:A10<>"";FILA(A1:A10))))}

 

{=DESREF(A1;MIN(SI(A1:A10<>"a";SI(A1:A10<>"";FILA(A1:A10))))-1;0)}

 

 

Averiguar el número de veces en que coinciden los valores de dos columnas

Imagen (19 kb)

En este ejemplo se necesita saber cuantas veces tienen el mismo valor ambas celdas de la misma fila. Dos posibilidades para lograrlo:

{=SUMA((A1:A10=B1:B10)*1)}

=SUMAPRODUCTO((A1:A10=B1:B10)*1)

 

Estas mismas fórmulas servirían si la comparación hubiera de hacerse entre dos filas:

{=SUMA((A1:J1=A2:J2)*1)}

=SUMAPRODUCTO((A1:J1=A2:J2)*1)

 

 

Saber el valor mínimo o el valor máximo que hay en un rango a partir de un valor dado

La función MIN devuelve el valor mínimo de un rango, pero si se necesita hallar el mínimo a partir de un número determinado (por ejemplo, excluyendo los ceros y números negativos), se puede usar:

{=MIN(SI(A1:A10>0;A1:A10))}

 

De igual forma, si se necesita saber el valor máximo a partir de un valor determinado se puede usar:

{=MAX(SI(A1:A10<10;A1:A10))}

En este caso, la fórmula devolvería el valor máximo en A1:A10 excluyendo los valores superiores a 10.

 

 

Fórmulas para averiguar el título de la columna o de la fila en que se encuentra el valor máximo o mínimo de un rango

Imagen (28 kb)

A partir del ejemplo, si se necesita:

{=INDICE(B1:D1;MIN(SI(MAX(B2:D4)=B2:D4;COLUMNA(B2:D4)))-1)}

 

{=INDICE(B1:D1;MAX((B2:D4=MAX(B2:D4))*COLUMNA(B2:D4))-1)}

 

{=INDICE(B1:D1;MIN(SI(MIN(B2:D4)=B2:D4;COLUMNA(B2:D4)))-1)}

 

{=INDICE(B1:D1;MAX((B2:D4=MIN(B2:D4))*COLUMNA(B2:D4))-1)}

 

{=INDICE(A2:A4;MIN(SI(MAX(B2:D4)=B2:D4;FILA(B2:D4)))-1)}

 

{=INDICE(A2:A4;MAX((B2:D4=MAX(B2:D4))*FILA(B2:D4))-1)}

 

{=INDICE(A2:A4;MIN(SI(MIN(B2:D4)=B2:D4;FILA(B2:D4)))-1)}

 

{=INDICE(A2:A4;MAX((B2:D4=MIN(B2:D4))*FILA(B2:D4))-1)}

 

 

Sumar las cantidades correspondientes a los últimos doce meses

Suponiendo que en el rango A1:A50 hay una serie de fechas, en B1:B50 unos importes correspondientes a cada una de dichas fechas, y que se necesitara saber la suma de los importes correspondientes a los últimos doce meses, se podría usar:

=SUMAPRODUCTO((A1:A50>=FECHA(AÑO(HOY())-1;MES(HOY());1))*(A1:A50<FECHA(AÑO(HOY());MES(HOY());1));B1:B50)

Y si se necesitara excluir de la suma los importes correspondientes al mes en curso, la fórmula sería:

=SUMAPRODUCTO((A1:A50>=FECHA(AÑO(HOY())-1;MES(HOY())+1;1))*(A1:A50<FECHA(AÑO(HOY());MES(HOY());1));B1:B50)

 

 

Totalizar por semanas los importes de una tabla de meses y días

ver libro con un ejemplo (34 kb)

Nota sobre este ejemplo: la gran cantidad de fórmulas matriciales -cincuenta y tres- que tiene la única hoja de este libro, junto con el gran número de cálculos necesarios, hace que el recálculo tenga un retardo perceptible.

 

 

Calcular un descuento en función de un baremo y de la cantidad comprada o vendida

Imagen (79 kb)

Si se necesita aplicar un descuento dependiendo del número de unidades compradas o vendidas, es posible hacerlo anidando SIes, siempre y cuando los tramos del baremo sean siete o menos. Usando los datos del ejemplo:

=SI(D2<=10;0,05;SI(D2<=20;0,08;SI(D2<=30;0,11;SI(D2<=40;0,14;SI(D2<=50;0,17;SI(D2<=60;0,2;SI(D2<=70;0,23)))))))

Pero si los tramos son más de siete (que es el caso del ejemplo), se puede recurrir a una fórmula matricial:

{=SI(D2>0;SI(D2>0;INDICE(B$2:B$11;MIN(SI((A$2:A$11>=D2);FILA(A$2:A$11)-1));1);0);0)}

 

 

Averiguar la fecha del domingo de Pascua de un año (entre 1900 y 2078)

La siguiente fórmula devuelve la fecha del domingo de Pascua del año especificado en A1. Su autor es Thomas Jansen. Fue presentada a un concurso para encontrar la fórmula más corta para determinar el domingo de Pascua, que se desarrolló en el sitio web de Hans W. Herber (http://www.herber.de). La fórmula en inglés es:

=DOLLAR((DAY(MINUTE(A1/38)/2+55)&".4."&A1)/7,)*7-6

Yo lo único que he hecho ha sido traducirla al Excel en español:

=MONEDA((DIA(MINUTO(A1/38)/2+55)&"-4-" & A1)/7;)*7-6

Lógicamente, también es posible sustituir la referencia a la celda A1 por un año. Por ejemplo, para el año 2008:

=MONEDA((DIA(MINUTO(2008/38)/2+55)&"-4-" & 2008)/7;)*7-6

 

Esta otra, más larga, funciona desde el año 1900 hasta 2368:

=MULTIPLO.INFERIOR(DIA(MINUTO(A1/38)/2+56)&"-5-"&A1;7)-34

 

La celda en la que se ponga cualquiera de las dos fórmulas ha de tener formato de fecha, dado que devuelven un número de serie.

 

Averiguar cual es el siguiente año Jacobeo

Para averiguar cual es el próximo año Jacobeo (el día 25 de julio es domingo), se puede usar la fórmula:

{=AÑO(HOY())+COINCIDIR(VERDADERO;DIASEM(FECHA(AÑO(HOY())+FILA(INDIRECTO("1:11"))-1;7;25))=1;0)-1}

 

 

Averiguar el valor máximo de la suma de las celdas de varias columnas (o filas)

Por ejemplo, en el rango A1:C10 hay una serie de valores, y lo que se necesita es el valor máximo de la suma de cada conjunto de tres celdas de cada fila. La fórmula sería:

{=MAX(A1:A10+B1:B10+C1:C10)}

 

Para saber en qué fila se encuentra el máximo anterior, la fórmula sería:

{=SUMA((MAX(A1:A10+B1:B10+C1:C10)=(A1:A10+B1:B10+C1:C10))*FILA(A1:A10))}

si no hay duplicados, y:

{=MAX((MAX(A1:A10+B1:B10+C1:C10)=(A1:A10+B1:B10+C1:C10))*FILA(A1:A10))}

si pudiera haberlos. Ésta fórmula devuelve el número de la última fila cuyas celdas suman el valor máximo.

 

Las fórmulas para averiguar el valor mínimo son muy parecidas a las anteriores:

{=MIN(A1:A10+B1:B10+C1:C10)}

{=SUMA((MIN(A1:A10+B1:B10+C1:C10)=(A1:A10+B1:B10+C1:C10))*FILA(A1:A10))}

{=MAX((MIN(A1:A10+B1:B10+C1:C10)=(A1:A10+B1:B10+C1:C10))*FILA(A1:A10))}

 

Las fórmulas para el caso de que los datos estuvieran situados de forma horizontal (p.ej., A1:J3) serían:

{=MAX(A1:J1+A2:J2+A3:J3)}

{=SUMA((MAX(A1:J1+A2:J2+A3:J3)=(A1:J1+A2:J2+A3:J3))*COLUMNA((A1:J3)))}

{=MAX((MAX(A1:J1+A2:J2+A3:J3)=(A1:J1+A2:J2+A3:J3))*COLUMNA(A1:J1))}

para el valor máximo, y:

{=MIN(A1:J1+A2:J2+A3:J3)}

{=SUMA((MIN(A1:J1+A2:J2+A3:J3)=(A1:J1+A2:J2+A3:J3))*COLUMNA((A1:J3)))}

{=MAX((MIN(A1:J1+A2:J2+A3:J3)=(A1:J1+A2:J2+A3:J3))*COLUMNA(A1:J1))}

para el valor mínimo.

 

 

Contar y resumir datos filtrados  [Ejemplo]

Normalmente, las funciones de Excel para resumir o contar datos en base a criterios trabajarían con la lista completa (a excepción de la función SUBTOTALES, que por defecto trabaja con los datos visibles), pero Laurent Longre descubrió hace años una forma de usar la función DESREF que permite trabajar tan sólo con las filas visibles en cada momento en el rango filtrado.

 

Partiendo del ejemplo anterior, si por ejemplo se necesitara (siempre sobre las filas visibles):

          {=SUMA((B2:B15>50)*(SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1)))*B2:B15)}

          {=SUMA((B2:B15>50)*(SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1))))}

          {=SUMA((SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1)))*B2:B15)}

          (Nota: =SUBTOTALES(9;B2:B15) hace lo mismo, la fórmula anterior es sólo otro ejemplo de esta forma de usar DESREF)

          {=SUMA((SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1))))}

          (Nota: =SUBTOTALES(2;B2:B15) hace lo mismo, la fórmula anterior es sólo otro ejemplo de esta forma de usar DESREF)

           {=COINCIDIR(1;SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1));0)+1}

           {=MAX(SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1))*FILA(A2:A15))}

 

 

Nota sobre esta última fórmula: hasta Excel 2003 tan sólo podía haber una condición activa en el filtro, pero en la versiones siguientes es posible tener activa más de una condición al mismo tiempo. La fórmula para devolver todas las condiciones activas es:

 

{=SI(SUMA((SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1))))=FILAS(A2:A15);"No hay filtro activo.";INDICE(A2:A15;INDICE(K.ESIMO.MENOR(SI(FILA(A2:A15)-1=SI(SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1));COINCIDIR(A2:A15;A2:A15;0));FILA(A2:A15));FILA(INDIRECTO("1:"&FILAS(A2:A15))));1)))}

 

que devolverá la primera condición activa. Para devolver las siguientes, habría que sustituir el último 1 de la fórmula por el número que se desee.

 

Esta fórmula trabaja con el supuesto de que hay tan sólo una fila de títulos. Si hubiese más, habría que sustituir el -1 por dicho número.

 

Hay disponible otro libro de ejemplo con esta fórmula, que sólo funcionará a partir de la versión 2007 puesto que supera el límite de niveles de anidamiento permitidos hasta Excel 2003.

 

 

 

Fórmula que simula la función BUSCARV, pero buscando en dos columnas

Imagen (19 kb)

En el ejemplo se necesita averiguar el valor de la celda de la columna C en cuya fila la columna A tiene el valor 20 y la columna B tiene el valor 2. La fórmula sería:

{=INDIRECTO("C"&COINCIDIR("202";A1:A9&B1:B9;0))}

 

o, usando la función INDICE:

 

{=INDICE(C1:C9;COINCIDIR("202";A1:A9&B1:B9;0))}

 

 

Averiguar la posición de la última aparición de un carácter en un texto

La función HALLAR devuelve la posición en que aparece por primera vez un carácter dentro de una cadena de texto, pero si se necesita saber la posición en que aparece por última vez se puede usar la fórmula:

=ENCONTRAR(CARACTER(255);SUSTITUIR(A1;"b";CARACTER(255);LARGO(A1)-LARGO(SUSTITUIR(A1;"b";""))))

 

En este caso la fórmula devolvería la posición de la última b dentro del texto que está en A1.

 

CARACTER(255) puede sustituirse por cualquier otro caracter que se sepa que en ningún caso va a estar en el texto. Por ejemplo, si se supiera con seguridad que no va a haber ningún cero se podría usar:

=ENCONTRAR("0";SUSTITUIR(A1;"b";"0";LARGO(A1)-LARGO(SUSTITUIR(A1;"b";""))))

 

 

Extraer un número de una cadena de texto  [Ejemplo]

Si se tiene que extraer un número de una cadena de texto situada en A1 pero la posición del número no es fija, se puede usar la siguiente fórmula:

{=EXTRAE(A1;MIN(SI(ESNUMERO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*1);FILA(INDIRECTO("1:"&LARGO(A1)))));MAX(SI(ESNUMERO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*1);FILA(INDIRECTO("1:"&LARGO(A1)))))-MIN(SI(ESNUMERO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*1);FILA(INDIRECTO("1:"&LARGO(A1)))))+1)}

 

La fórmula devolverá el número como cadena de texto. Si se necesitase convertirlo en su valor para poder operar numéricamente con él, lo único que habría que hacer es multiplicar por 1 el resultado:

{=EXTRAE(A1;MIN(SI(ESNUMERO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*1);FILA(INDIRECTO("1:"&LARGO(A1)))));MAX(SI(ESNUMERO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*1);FILA(INDIRECTO("1:"&LARGO(A1)))))-MIN(SI(ESNUMERO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*1);FILA(INDIRECTO("1:"&LARGO(A1)))))+1)*1}

 

Hay que tener en cuenta que tan sólo puede haber un número en la cadena de texto. Es decir, por ejemplo la fórmula funcionará con la cadena "extraer 125 de esta cadena" (devolverá 125), pero no con "extraer 125 y 250 de esta cadena" (devolvería "125 y 250").

 

Una curiosidad: esta es probablemente la fórmula matricial que más matrices crea durante su cálculo de todas las que hay en esta página: concretamente 6 matrices, cada una de las cuales tendrá tantos elementos como caracteres tenga la cadena de texto que ha de procesar. Por lo tanto, aconsejo prudencia si ha de ser usada muchas veces en el mismo libro, especialmente si las cadenas de texto son largas.

 

 

Saber el número de veces que aparece un caracter o una cadena en un texto

Suponiendo que A1 contiene el texto ABACDCABAEFE, para averiguar el número de aes que aparecen en el texto:

=LARGO(A1)-LARGO(SUSTITUIR(A1;"A";""))

y para averiguar el número de veces que aparece ABA:

=(LARGO(A1)-LARGO(SUSTITUIR(A1;"ABA";"")))/3

 

Si se quisiera saber el número de aes que aparecen en el rango A1:A10, la fórmula sería:

{=SUMA(LARGO(A1:A10)-LARGO(SUSTITUIR(A1:A10;"A";"")))}

y para averiguar el número de veces que aparece ABA en el mismo rango:

{=SUMA((LARGO(A1:A10)-LARGO(SUSTITUIR(A1:A10;"ABA";"")))/3)}

 

 

Averiguar el texto más repetido en un rango

La función MODA devuelve el valor que más se repite en un rango, pero tan sólo se puede aplicar a valores numéricos. Si se necesitara emular esta función en un rango compuesto de celdas con texto, y suponiendo que dicho rango fuera A1:A10, se podría usar la fórmula:

=INDICE(A1:A10;MODA(COINCIDIR(A1:A10;A1:A10;0)))

 

 

Averiguar el elemento menos repetido en un rango

Si se necesita saber cual es el elemento menos repetido de, por ejemplo, el rango A1:A10 (en el que no puede haber celdas vacías), la fórmula sería:

{=INDICE(A1:A10;COINCIDIR(MIN(CONTAR.SI(A1:A10;A1:A10));CONTAR.SI(A1:A10;A1:A10);0))}

teniendo en cuenta que si hubiese más de un elemento con el menor número de repeticiones la fórmula devolvería el situado más arriba.

 

Esta fórmula sirve para cualquier tipo de datos.

 

 

Averiguar el número de palabras de un texto y/o el promedio de sus longitudes

Suponiendo que la celda A1 tiene un texto cuyas palabras están separadas por espacios, la fórmula para averiguar el número de palabras sería:

=LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))+1

 

Para saber el promedio de las longitudes de las palabras del texto situado en la celda A1 la fórmula sería:

=LARGO(SUSTITUIR(A1;" ";""))/(LARGO(A1)-LARGO(SUSTITUIR(A1;" ";""))+1)

 

Notas:

 

 

 

Averiguar cual es el texto más largo de un rango  [Ejemplo]
Si se deseara saber la fila con el texto más largo en el rango A1:A10, la fórmula sería:
{=MAX(LARGO(A1:A10))}
Para obtener dicho texto más largo:
{=INDICE(A1:A10;COINCIDIR(1;SIGNO(LARGO(A1:A10)=MAX(LARGO(A1:A10)));0))}

Si hubiera dos o más celdas con el texto de la misma longitud, esta fórmula devolverá la primera de ellas.

Sería posible usar un formato condicional para resaltar todas las celdas cuyos textos fueran los más largos del rango. La fórmula para dicho formato condicional sería:
=LARGO(A1)=MAX(LARGO(A$1:A$10))

Esta fórmula habría que aplicarla a todo el rango A1:A10

 

 

Averiguar el carácter o el dígito más repetido en una celda y/o el número de veces que aparece  [Ejemplo]

Suponiendo que se quiera saber cual es el caracter o el número más repetido en la celda A1, se podría usar:

{=EXTRAE(A1;MODA(COINCIDIR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);0));1)}

teniendo en cuenta que si dos o más caracteres y/o dígitos se repiten el mismo número de veces, la fórmula devolverá el situado más a la izquierda.

 

Es posible también averiguar el número de veces que aparece el carácter o dígito que más lo hace, usando la fórmula:

{=MAX(LARGO(A1)-LARGO(SUSTITUIR(A1;EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"")))}

 

 

Averiguar la posición del primer carácter o número en una celda

Si se desea obtener la posición del primer carácter no numérico en la celda A1, se puede usar:

{=COINCIDIR(VERDADERO;ESERROR(SIGNO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)));0)}

Y para obtener el carácter que es:

{=EXTRAE(A1;COINCIDIR(VERDADERO;ESERROR(SIGNO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)));0);1)}

 

Para obtener la posición del primer número o el número en sí, bastaría con sustituir VERDADERO por FALSO.

 

Averiguar la posición del último carácter o número en una celda

Si se desea obtener la posición del último carácter no numérico en la celda A1, se puede usar:

{=MAX(ESERROR(SIGNO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))*FILA(INDIRECTO("1:"&LARGO(A1))))}

Y para obtener el carácter que es:

{=EXTRAE(A1;MAX(ESERROR(SIGNO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))*FILA(INDIRECTO("1:"&LARGO(A1))));1)}

 

Si se desea el último número, las fórmulas serían:

{=MAX(ESNUMERO(SIGNO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))*FILA(INDIRECTO("1:"&LARGO(A1))))}

y:

{=EXTRAE(A1;MAX(ESNUMERO(SIGNO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))*FILA(INDIRECTO("1:"&LARGO(A1))));1)}

 

 

Saber si todos los caracteres del texto de una celda son letras  [Ejemplo]

Excel dispone de la función ESTEXTO para saber si el contenido de una celda es un texto, pero dicha función no evalúa cada caracter, por lo que, por ejemplo, AB1C lo consideraría como texto a pesar de tener un número.

 

Si, por ejemplo, se necesitara saber si todos y cada uno de los caracteres de una celda son letras mayúsculas, excluyendo la Ñ, se podría usar:

 

=SUMAPRODUCTO((CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))>64)*(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))<91))=LARGO(A1)

 

e incluyendo la Ñ:

 

=SUMAPRODUCTO(((CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))>64)*(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))<91))+(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))=209))=LARGO(A1)

 

Si, además, se necesitara saber si el texto tiene una longitud determinada (por ejemplo, 6 caracteres), la fórmula sería (sin la Ñ):

 

=Y(SUMAPRODUCTO((CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))>64)*(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))<91))=6;LARGO(A1)=6)

 

y con la Ñ:

 

=Y((SUMAPRODUCTO((CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))>64)*(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))<91)+(CODIGO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))=209)))=6;LARGO(A1)=6)

 

Si se quisiera verificar que todas las letras fueran minúsculas, habría que hacer las siguientes sustituciones en las fórmulas:

 

96 en vez de 64

123 en vez de 91

241 en vez de 209

 

Nota: todas estas fórmulas pueden ser utilizadas como 'Fórmula personalizada' en las validaciones.

 

 

Fórmulas para averiguar los dígitos de control de un Código de Cuenta de Cliente (CCC) del sistema bancario español  [Ejemplo]

Suponiendo que en A1 estuviera el código de la entidad bancaria (4 dígitos), en B1 el código de la sucursal (4 dígitos) y en C1 el código de la cuenta (10 dígitos), la fórmula para averiguar el dígito de control de la entidad + la sucursal (8 dígitos en total) sería:

{=EXTRAE("12345678910";11-RESIDUO(SUMA(EXTRAE(A1&B1;FILA(INDIRECTO("1:8"));1)*(EXTRAE("37498625";FILA(INDIRECTO("1:8"));1)+1));11);1)}

 

La fórmula para averiguar el dígito de control del número de cuenta sería:

{=EXTRAE("12345678910";11-RESIDUO(SUMA(EXTRAE(C1;FILA(INDIRECTO("1:10"));1)*(EXTRAE("0137498625";FILA(INDIRECTO("1:10"));1)+1));11);1)}

 

Si se necesitara averiguar ambos dígitos de control en la misma celda, la fórmula sería:

{=EXTRAE("12345678910";11-RESIDUO(SUMA(EXTRAE(A1&B1;FILA(INDIRECTO("1:8"));1)*(EXTRAE("37498625";FILA(INDIRECTO("1:8"));1)+1));11);1)&EXTRAE("12345678910";11-RESIDUO(SUMA(EXTRAE(C1;FILA(INDIRECTO("1:10"));1)*(EXTRAE("0137498625";FILA(INDIRECTO("1:10"));1)+1));11);1)}

 

Y para obtener el CCC completo (entidad + sucursal + dígitos de control + número de cuenta) separados por un espacio, que es como se suele presentar el CCC en España, la fórmula sería:

{=A1&" "&B1&" "&EXTRAE("12345678910";11-RESIDUO(SUMA(EXTRAE(A1&B1;FILA(INDIRECTO("1:8"));1)*(EXTRAE("37498625";FILA(INDIRECTO("1:8"));1)+1));11);1)&EXTRAE("12345678910";11-RESIDUO(SUMA(EXTRAE(C1;FILA(INDIRECTO("1:10"));1)*(EXTRAE("0137498625";FILA(INDIRECTO("1:10"));1)+1));11);1)&" "&C1}

 

Nota: conviene que tanto las celdas con los códigos bancarios como las que tengan las fórmulas para averiguar sus dígitos de control tengan formato de texto para evitar que se pierdan los ceros que pudiera haber a la izquierda. Otra posibilidad sería anteponer un apóstrofo ' a los códigos, por ejemplo '0123

 

 

Fórmula para averiguar los dígitos de control IBAN (International Bank Account Number)

Suponiendo que en A1 estuviera el CCC (Código de Cuenta del Cliente del sistema bancario español) completo (es decir, los cuatro dígitos del banco + los cuatro de la sucursal + los dos dígitos de control + los diez dígitos de la cuenta), sin espacios ni otros signos (como p.ej. guiones), la fórmula para averiguar los dígitos de control IBAN sería:

=DERECHA("0"&98-RESIDUO(RESIDUO(RESIDUO(RESIDUO(EXTRAE(A1;1;8);97)&EXTRAE(A1;9;8);97)&EXTRAE(A1&"142800";17;8);97)&EXTRAE(A1&"142800";25;2);97);2)

 

y la fórmula para devolver el código IBAN completo sería:

="ES"&DERECHA("0"&98-RESIDUO(RESIDUO(RESIDUO(RESIDUO(EXTRAE(A1;1;8);97)&EXTRAE(A1;9;8);97)&EXTRAE(A1&"142800";17;8);97)&EXTRAE(A1&"142800";25;2);97);2)&A1

 

Si se deseara desglosar el IBAN en 6 grupos de 4 caracteres separados por un espacio (que es como se suele presentar el IBAN en España), la fórmula sería:

="ES"&DERECHA("0"&98-RESIDUO(RESIDUO(RESIDUO(RESIDUO(EXTRAE(A1;1;8);97)&EXTRAE(A1;9;8);97)&EXTRAE(A1&"142800";17;8);97)&EXTRAE(A1&"142800";25;2);97);2)&" "&IZQUIERDA(A1;4)&" "&EXTRAE(A1;5;4)&" "&EXTRAE(A1;9;4)&" "&EXTRAE(A1;13;4)&" "&EXTRAE(A1;17;4)

 

Nota: en el libro de ejemplo para la fórmula anterior (la que averigua los dígitos de control del CCC) hay una hoja con ejemplos de estas funciones que calculan los códigos de control IBAN.

 

 

Averiguar la letra de control de un NIF (Número de Identificación Fiscal en España)  [Ejemplo]

Suponiendo que en A1 se encuentra el DNI cuya letra se quiera averiguar, la fórmula sería:

=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";RESIDUO(A1;23)+1;1)

 

Averiguar el dígito verificador de un RUT (Rol Único Tributario) chileno

Suponiendo que en A1 se encuentra el RUT, la fórmula sería:

{=EXTRAE("123456789K0";11-RESIDUO(SUMA(EXTRAE(DERECHA(REPETIR("0";12)&$A$1;12);13-FILA(INDIRECTO("1:12"));1)*(FILA(INDIRECTO("1:12"))+1-(FILA(INDIRECTO("1:12"))>6)*6));11);1)}

 

Usando como separador la coma, la fórmula quedaría así:

{=EXTRAE("123456789K0",11-RESIDUO(SUMA(EXTRAE(DERECHA(REPETIR("0",12)&$A$1,12),13-FILA(INDIRECTO("1:12")),1)*(FILA(INDIRECTO("1:12"))+1-(FILA(INDIRECTO("1:12"))>6)*6)),11),1)}

 

Nota: no dispongo de muchos RUTs auténticos para probar de forma más intensiva si la fórmula funciona. En cualquier caso, sí lo calcula bien con el ejemplo que aparece en la wikipedia sobre el tema. Si alguien encuentra un error en la fórmula, que por favor me lo comunique a excel ARROBA jrgc.es --> GRACIAS.

 

Averiguar el dígito verificador de un DNIC (Dirección Nacional de Identificación Civil) uruguayo

Suponiendo que en A1 se encuentra el DNIC, la fórmula sería:

=DERECHA(10-RESIDUO(SUMAPRODUCTO(VALOR(DERECHA(EXTRAE(A1;FILA(INDIRECTO("1:7"));1)*EXTRAE("2987634";FILA(INDIRECTO("1:7"));1);1)));10);1)

 

Usando como separador la coma, la fórmula sería:

=DERECHA(10-RESIDUO(SUMAPRODUCTO(VALOR(DERECHA(EXTRAE(A1,FILA(INDIRECTO("1:7")),1)*EXTRAE("2987634",FILA(INDIRECTO("1:7")),1),1))),10),1)

 

Averiguar el dígito verificador de una CUIT (Clave Única de Identificación Tributaria) argentina

Suponiendo que en A1 se encuentra la CUIT (en el formato 99-99999999), la fórmula para averiguar su dígito verificador sería:

{=EXTRAE("12345678990";11-RESIDUO(SUMA(EXTRAE(IZQUIERDA($A$1;2)&EXTRAE($A$1;4;8);10-FILA(INDIRECTO("1:10"))+1;1)*(FILA(INDIRECTO("1:10"))+1-(FILA(INDIRECTO("1:10"))>6)*6));11);1)}

 

Usando como separador la coma, la fórmula sería:

{=EXTRAE("12345678990",11-RESIDUO(SUMA(EXTRAE(IZQUIERDA($A$1,2)&EXTRAE($A$1,4,8),10-FILA(INDIRECTO("1:10"))+1,1)*(FILA(INDIRECTO("1:10"))+1-(FILA(INDIRECTO("1:10"))>6)*6)),11),1)}

 

 

Fórmula para averiguar el dígito de control de un código de barras EAN-13

Suponiendo que en A1 se encuentre el código EAN-13 (12 dígitos) cuyo dígito de control se desee averiguar, la fórmula sería:

 

=10-RESIDUO(SUMAPRODUCTO(EXTRAE(A1;FILA(INDIRECTO("1:12"));1)*(EXTRAE("131313131313";FILA(INDIRECTO("1:12"));1)));10)

 

Una variante, un poco más corta:

 

=10-RESIDUO(SUMAPRODUCTO(EXTRAE(A1;FILA(INDIRECTO("1:12"));1) *(1+((RESIDUO(FILA(INDIRECTO("1:12"));2)=0)*2)));10)

 

 

Fórmula para averiguar el dígito de control de un código de barras EAN-8

Suponiendo que en A1 se encuentre el código EAN-8 (7 dígitos) cuyo dígito de control se desee averiguar, la fórmula sería:

=10-RESIDUO(SUMAPRODUCTO(EXTRAE(A1;FILA(INDIRECTO("1:7"));1)*(EXTRAE("3131313";FILA(INDIRECTO("1:7"));1)));10)

 

 

Nota: las fórmulas que usan la función SUMAPRODUCTO se pueden hacer un poco más cortas si se convierten en matriciales, para lo cual lo único que hay que hacer es sustituir SUMAPRODUCTO por SUMA e introducirlas como fórmulas matriciales.

 

 

Reitero la importancia de que la celdas que contienen los códigos cuyos dígitos de control se quieran averiguar estén formateadas como texto, para evitar que se puedan perder ceros por la izquierda, si los hay.

 

 

 

Detectar si existe duplicidad de un conjunto de celdas

imagen (11 kb)

En este ejemplo se necesita averiguar qué filas están duplicadas (es decir, tienen las tres celdas iguales en el mismo orden).

 

Una posibilidad es poner la siguiente fórmula en la fila 1 de una columna vacía

 

=SUMAPRODUCTO(($A$1:$A$6&$B$1:$B$6&$C$1:$C$6=$A1&$B1&$C1)*1)

 

y copiarla y pegarla hasta la última fila. La fórmula devolverá el número de veces en que se produce la duplicidad.

 

También sería posible usar la siguiente fórmula, pero hay que tener en cuenta que hay que introducirla como matricial habiendo seleccionado antes todo el rango donde debe ir.

 

{=COINCIDIR(A1:A6&B1:B6&C1:C6;A1:A6&B1:B6&C1:C6;0)}

 

Esta fórmula asignará a cada grupo de celdas un número, y los grupos duplicados aparecerán con el mismo número. En esta imagen (37 kb) se puede ver la fórmula, aplicada en el rango D1:D6

 

 

Fijar en una celda el lunes de la semana actual (o cualquier otro día de la semana)

Por ejemplo, para mostrar siempre en una celda el lunes de la semana actual, la fórmula sería:

 

=SI(DIASEM(HOY())=2;HOY();HOY()-DIASEM(HOY()-2))

 

Para los demás días de la semana, habría que sustituir los doses por:

    1 para el domingo

    3 para el martes

    4 para el miércoles

    5 para el jueves

    6 para el viernes

    7 para el sábado

 

 

Fórmula para averiguar el sábado, domingo, etc. anterior o posterior a una fecha
Si en A1 hay una fecha y se desea saber el sábado anterior a la misma se puede usar:
=A1-DIASEM(A1)
Para el sábado posterior la fórmula sería:
=A1-DIASEM(A1)+7

Para el domingo anterior o siguiente, las fórmulas serían:
=A1-DIASEM(A1-1)
=A1-DIASEM(A1-1)+7

Para el lunes, martes...viernes, habría que sustituir -1 por -2,-3... -6

Lógicamente, se puede trabajar con HOY() o AHORA() en vez de la referencia a A1, en cuyo caso los resultados serían volátiles.
 

 

 

Fijar en una celda el primer o último día del mes actual, anterior o siguiente

Primer día del mes actual:      =FECHA(AÑO(HOY());MES(HOY());1)

Último día del mes actual:      =FECHA(AÑO(HOY());MES(HOY())+1;1)-1

Primer día del mes anterior:    =FECHA(AÑO(HOY());MES(HOY())-1;1)

Último día del mes anterior:    =FECHA(AÑO(HOY());MES(HOY());1)-1

Primer día del mes siguiente:  =FECHA(AÑO(HOY());MES(HOY())+1;1)

Último día del mes siguiente:  =FECHA(AÑO(HOY());MES(HOY())+2;1)-1

 

 

Subtotalizar datos por tramos de edad

Suponiendo que en rango A1:A50 hay una serie de fechas de nacimiento, y que se necesita subtotalizar por tramos de edad de 10 años desde dichas fechas de nacimiento hasta la fecha actual, la fórmula sería:

=SUMAPRODUCTO((ENTERO(SIFECHA($A$1:$A$50;HOY();"y")/10)=0)*1)

para las fechas de nacimiento de los 10 años anteriores. Para los 10, 20, ... XX años, habría que sustituir =0 por =1, =2 ... =XX/10

Si se necesitara tomar como referencia una fecha en concreto en lugar de la actual, habría que sustituir HOY() por alguna de las funciones de conversión a fechas de Excel. Por ejemplo, usando la función FECHA para el 31/12/2007, la fórmula anterior quedaría:

=SUMAPRODUCTO((ENTERO(SIFECHA($A$1:$A$50;FECHA(2007;12;31);"y")/10)=0)*1)

Si se modifica un poco las fórmulas es posible subtotalizar por otros períodos distintos al decenio, como por ejemplo quinquenios:

=SUMAPRODUCTO((ENTERO(SIFECHA($A$1:$A$50;HOY();"y")/5)=0)*1)

o bienios:

=SUMAPRODUCTO((ENTERO(SIFECHA($A$1:$A$50;HOY();"y")/2)=0)*1)

Lógicamente, también es posible obtener sumas de cantidades que estén relacionadas con esas fechas. Por ejemplo, suponiendo que en el rango B1:B50 de la misma hoja hubiera una serie de cantidades y se deseara obtener la suma que corresponde a quienes han nacido en los 10 años anteriores a la fecha actual, la fórmula sería:

=SUMAPRODUCTO((ENTERO(SIFECHA($A$1:$A$50;HOY();"y")/10)=0)*$B1:$B$50)

 

 

Obtener un promedio despreciando el/los valor/es mayor/es o menor/es  [Ejemplo]

Suponiendo que se tiene un rango con nombre (de una sola columna de ancho) llamado Notas, y se desea obtener su promedio despreciando su valor más alto, la fórmula sería:

{=PROMEDIO(K.ESIMO.MENOR(Notas;FILA(INDIRECTO("1:"&FILAS(Notas)-1))))}

Para despreciar los dos valores más altos tan sólo habría que sustituir el -1 por -2, etc.

Para obtener el promedio despreciando la nota más baja, habría que usar la función K.ESIMO.MAYOR, o sea:

{=PROMEDIO(K.ESIMO.MAYOR(Notas;FILA(INDIRECTO("1:"&FILAS(Notas)-1))))}

 

Si se deseara el promedio sin tener en cuenta TODAS las apariciones de la nota más baja, la fórmula sería:

 

{=PROMEDIO(K.ESIMO.MAYOR(Notas;FILA(INDIRECTO("1:"&FILAS(Notas)-CONTAR.SI(Notas;MIN(Notas))))))}

 

Para obtener el promedio sin tener en cuentas TODAS las apariciones de la nota más alta:

 

{=PROMEDIO(K.ESIMO.MENOR(Notas;FILA(INDIRECTO("1:"&FILAS(Notas)-CONTAR.SI(Notas;MAX(Notas))))))}

 


Si el rango con nombre fuera una fila en lugar de una columna, habría que sustituir en la fórmula FILAS por COLUMNAS.

 

Para averiguar el promedio del rango con nombre Notas despreciando tanto todos los valores menores como todos los mayores, la fórmula sería:

 

{=PROMEDIO(SI(NO((Notas=MIN(Notas))+(Notas=MAX(Notas)));Notas;""))}

 

 

Averiguar la/s letra/s de una columna  [Ejemplo]
Esta fórmula devuelve la/s letra/s de la columna en que se encuentra:

=SI(COLUMNA()>26;CARACTER((ENTERO(COLUMNA()-1)/26)+64)&CARACTER(RESIDUO((COLUMNA()-1);26)+65);CARACTER(COLUMNA()+64))

Lógicamente, es posible sustituir COLUMNA() por un número de columna cualquiera.

 

 

Averiguar a qué bimestre, trimestre, cuatrimestre o semestre pertenece una fecha  [Ejemplo]

Suponiendo que la fecha estuviera en A1, la fórmula para el bimestre sería:

=REDONDEAR.MAS(MES(A1)/2;0)

 - para el trimestre, habría que sustituir /2 por /3

 - para el cuatrimestre, /2 por /4

 - y para el semestre, /2 por /6.

 

 

Obtener la cantidad de domingos (o cualquier otro día de la semana) que hay entre dos fechas  [Ejemplo]
Suponiendo que en A1 hay una fecha y en A2 otra fecha, mayor que la de A1, la siguiente fórmula devolverá el número de domingos que hay entre ambas:
=ENTERO((B1-DIASEM(B1)-A1+8)/7)
para saber la cantidad de lunes, la fórmula sería:
=ENTERO((B1-DIASEM(B1-1)-A1+8)/7)

Sustituyendo -1 por -2, -3... -6 se obtendría el número de martes, miércoles... sábados.

 

 

Fórmula para saber si un número es o no primo  [Ejemplo]

Suponiendo que el número estuviera en A1:

{=A1&ELEGIR(N((SUMA(--(MULTIPLO.INFERIOR(A1;FILA(INDIRECTO("1:"&ENTERO(RAIZ(A1+1)))))=A1))>1))+1;" sí";" no") & " es un número primo."}

 

Se podría usar la siguiente fórmula en un Formato Concidional para que las celdas que contengan un número primo se destaquen como se haya establecido en el propio Formato Condicional:

=SUMA(--(MULTIPLO.INFERIOR(A1;FILA(INDIRECTO("1:"&ENTERO(RAIZ(A1)))))=A1))=1

 

 

Notas:

 

Fórmula para obtener el reverso de un número entero  [Ejemplo]

Si, por ejemplo, en la celda A1 está el número 12345, la siguiente fórmula devolverá su reverso (54321):

{=SUMA(EXTRAE(ABS(A1);FILA(INDIRECTO("1:"&LARGO(ABS(A1))));1)*10^(FILA(INDIRECTO("1:"&LARGO(ABS(A1))))-1))*SIGNO(A1)}

Lo mismo hace:

=SUMAPRODUCTO(EXTRAE(ABS(A1);FILA(INDIRECTO("1:"&LARGO(ABS(A1))));1)*10^(FILA(INDIRECTO("1:"&LARGO(ABS(A1))))-1))*SIGNO(A1)

 

Usando esta fórmula también es posible saber si el número situado en A1 es o no capicúa:

{=A1&ELEGIR(--(SUMA(EXTRAE(ABS(A1);FILA(INDIRECTO("1:"&LARGO(ABS(A1))));1)*10^(FILA(INDIRECTO("1:"&LARGO(ABS(A1))))-1))*SIGNO(A1)=A1)+1;" no";" sí")&" es capicúa."}

 

Nota: El máximo de dígitos que puede tener el número es 15, es decir los mismos que la precisión numérica de Excel.

 

 

Averiguar si una palabra o frase es un palíndromo  [Ejemplo]

La siguiente fórmula devuelve VERDADERO si la palabra en A2 es un palíndromo (se lee igual en ambos sentidos) y FALSO si no lo es:

{=Y(EXTRAE(A2;FILA(INDIRECTO("1:"&ENTERO(LARGO(A2)/2)));1)=EXTRAE(A2;LARGO(A2)-FILA(INDIRECTO("1:"&ENTERO(LARGO(A2)/2)))+1;1))}

 

Esta otra sirve para procesar una frase en lugar de una palabra:

{=Y(EXTRAE(SUSTITUIR(A2;" ";"");FILA(INDIRECTO("1:"&ENTERO(LARGO(SUSTITUIR(A2;" ";""))/2)));1)=EXTRAE(SUSTITUIR(A2;" ";"");LARGO(SUSTITUIR(A2;" ";""))-FILA(INDIRECTO("1:"&ENTERO(LARGO(SUSTITUIR(A2;" ";""))/2)))+1;1))}

 

Se podrían acortar un poco las fórmulas, pero en este caso tendrían que evaluar todas las letras de la palabra o de la frase, mientras que las dos fórmulas anteriores necesitan evaluar tan sólo la mitad:

 

{=Y(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)=EXTRAE(A1;LARGO(A1)+1-FILA(INDIRECTO("1:"&LARGO(A1)));1))}

 

{=Y(EXTRAE(SUSTITUIR(A1;" ";"");FILA(INDIRECTO("1:"&LARGO(SUSTITUIR(A1;" ";""))));1)=EXTRAE(SUSTITUIR(A1;" ";"");LARGO(SUSTITUIR(A1;" ";""))+1-FILA(INDIRECTO("1:"&LARGO(SUSTITUIR(A1;" ";""))));1)))

 

Nota: en principio da igual si las letras están en mayúsculas o minúsculas, pero lo que no puede haber son signos de puntuación ni acentos.

 

 

Averiguar el valor decimal de un número binario  [Ejemplo]

Excel dispone del complemento Herramientas para Análisis, que incluye una función para realizar este cálculo, pero en cualquier caso se puede usar la siguiente fórmula para hallar el valor decimal del número binario situado en A1:

 

{=SUMA(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*2^(LARGO(A1)-FILA(INDIRECTO("1:"&LARGO(A1)))))}

Lo mismo hace:

=SUMAPRODUCTO(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)*2^(LARGO(A1)-FILA(INDIRECTO("1:"&LARGO(A1)))))

 

Debido a la precisión numérica de Excel (15 dígitos), si la celda donde está el número binario tiene formato numérico, éste no podrá ser mayor de 15 unos (32.767 en decimal), mientras que si la celda está formateada como texto el número binario no podrá ser mayor de 49 unos (562.949.953.421.311 en decimal)

 

Nota: Esta misma fórmula sirve para averiguar el valor decimal de un numero en las bases 3 a 9. Lo único que hay que hacer es sustituir el 2 de la fórmula (en la expresión *2^) por la base en la que se encuentre el número a convertir.

 

 

Averiguar el valor binario de un número decimal  [Ejemplo]

 

NOTA: se aconseja consultar este artículo sobre la conversión desde base 10 (decimal) a cualquiera de las bases 2 a 9.

 

Excel dispone del complemento Herramientas para Análisis, que incluye una función para realizar este cálculo, pero en cualquier caso se puede usar la siguiente fórmula para hallar el valor binario de un número decimal situado en A1:

{=SUMA(RESIDUO(ENTERO(A1/2^((FILA(INDIRECTO("1:"&ENTERO(LOG(A1;2))+1)))-1));2)*10^(FILA(INDIRECTO("1:"&ENTERO(LOG(A1;2))+1))-1))}

Lo mismo hace:

=SUMAPRODUCTO(RESIDUO(ENTERO(A1/2^((FILA(INDIRECTO("1:"&ENTERO(LOG(A1;2))+1)))-1));2)*10^(FILA(INDIRECTO("1:"&ENTERO(LOG(A1;2))+1))-1))

 

Notas:

 

 

Averiguar el valor decimal de un número hexadecimal  [Ejemplo]

Excel dispone del complemento Herramientas para Análisis, que incluye una función para realizar este cálculo, pero en cualquier caso se puede usar la siguiente fórmula para hallar el valor decimal de un número hexadecimal situado en A1:

{=SUMA((HALLAR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"0123456789abcdef")-1)*16^(LARGO(A1)-FILA(INDIRECTO("1:"&LARGO(A1)))))}

Lo mismo hace:

=SUMAPRODUCTO((HALLAR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"0123456789abcdef")-1)*16^(LARGO(A1)-FILA(INDIRECTO("1:"&LARGO(A1)))))

 

Debido a la precisión numérica de Excel (15 dígitos), el hexadecimal más alto que puede devolver esta fórmula con garantías es 38D7EA4C68000 (un uno seguido de 15 ceros en decimal).

 

Nota: esta misma fórmula se puede usar (con las necesarias modificaciones) para averiguar el valor decimal de un número en otras bases que requieran más de los 10 símbolos numéricos del sistema decimal. Por ejemplo, para hallar el valor decimal de un número en base 15, la fórmula sería:

{=SUMA((HALLAR(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1);"0123456789abcde")-1)*15^(LARGO(A1)-FILA(INDIRECTO("1:"&LARGO(A1)))))}

 

Las modificaciones con respecto a la fórmula hexadecimal -> decimal han sido tan sólo dos:

1) Se ha eliminado la f (en "0123456789abcdef")

2) Se ha sustituido el 16 (en *16^) por 15

 

 

Ley de Benford

Si se desea conocer el cumplimiento (o incumplimiento) de una serie de datos llamada Datos de la Ley de Benford, la fórmula para el 1 sería:

{=(SUMA(--(IZQUIERDA(ABS(Datos);1)="1"))/FILAS(Datos))-LOG(1+1/1)}

para el 2:

{=(SUMA(--(IZQUIERDA(ABS(Datos);1)="2"))/FILAS(Datos))-LOG(1+1/2)}

Para el resto de números, tan sólo habría que sustituir los dos doses de esta ultima fórmula por el número que se deseara.

 

Con unas pequeñas modificaciones es posible conseguir que la fórmula funcione para los n primeros dígitos, donde n lo determinará el número de fila donde se encuentre la fórmula:

 

{=(SUMA(--(IZQUIERDA(ABS(Datos);LARGO(FILA()))*1=FILA()))/FILAS(Datos))-LOG(1+1/FILA())}

 

Notas:

 

Saber la cantidad de números pares y/o impares que hay en un rango

Suponiendo que en el rango A1:A10 hay una serie de números enteros y que se desea averiguar cuantos de ellos son pares y cuantos impares, las fórmulas serían:

=SUMAPRODUCTO(--NO(RESIDUO(A1:A10;2))) para los pares

y:

=SUMAPRODUCTO(RESIDUO(A1:A10;2)) para los impares

 

Nota: se entiende que en el rango A1:A10 hay sólo números enteros. Si hubiera celdas vacías, fechas, números con decimales, cadenas de texto, etc., las fórmulas podrían dar como resultado un error o no devolver el resultado correcto.

 

Sumar los números pares o impares de un rango

Suponiendo que en el rango A1:A10 hay una serie de números enteros y que se desa averiguar la suma de los pares y/o la suma de los impares, las fórmulas serían:

=SUMAPRODUCTO(NO(RESIDUO(A1:A10;2))*A1:A10) para los pares

y:

=SUMAPRODUCTO(RESIDUO(A1:A10;2)*A1:A10) para los impares

 

Nota: se entiende que en el rango A1:A10 hay sólo números enteros. Si hubiera celdas vacías, fechas, números con decimales, cadenas de texto, etc., las fórmulas podrían dar como resultado un error o no devolver el resultado correcto.

 

 

Averiguar un número de la serie Fibonacci

La siguiente fórmula devuelve el número 50 de la serie Fibonacci:

 

=((((1+RAIZ(5))/2)^50)-(-((1+RAIZ(5))/2)^-50))/RAIZ(5)

 

Lógicamente, es posible averiguar cualquier otro sustituyendo los dos 50 de la fórmula por el número que se desee; pero hay que tener en cuenta que, debido a la precisión numérica de Excel (15 dígitos), el máximo número que la fórmula devolverá correctamente es el 73º de la serie.

 

Usando una UDF que utilice el tipo de datos Decimal para hacer los cálculos es posible averiguar hasta el Fibonacci 139 (29 dígitos). En este libro de ejemplo hay un ejemplo de la función, junto con algunas otras que hacen uso de este tipo de datos (artículo sobre el tema).

 

En esta página hay varias funciones para trabajar en Excel con enteros grandes, sirviendo una de ellas para hayar números de la serie Fibonacci más allá del 139 (en las pruebas he llegado hasta el 9999 de la serie).

 

La siguiente fórmula devuelve VERDADERO si el número situado en A1 pertenece a la serie Fibonacci y FALSO en caso contrario. Hay que tener en cuenta que, debido a la precisión numérica de Excel, sólo funciona hasta el número 73º de la serie (el 806.515.533.049.393):

 

{=NO(ESERROR(COINCIDIR(TEXTO($A$1;"0");TEXTO(((((1+RAIZ(5))/2)^FILA(INDIRECTO("2:73")))-(-((1+RAIZ(5))/2)^-FILA(INDIRECTO("2:73"))))/RAIZ(5);0);"0")))}

 

 

[Ejemplo para las tres fórmulas que vienen a continuación]

Nota: en esta página hay funciones VBA que pueden hacer los tres cálculos que van a continuación hasta el máximo del tipo de datos Currency de VBA (922.337.203.685.477)

Averiguar la cantidad de divisores de un número entero positivo y obtener una lista de los mismos

La siguiente fórmula devuelve la cantidad de divisores del número entero positivo situado en A1:

{=SUMA(--((MULTIPLO.INFERIOR(A1;FILA(INDIRECTO("1:"&ENTERO(A1/2))))=A1)))}

 

Es posible obtener un listado de dichos divisores poniendo la siguiente fórmula en la fila 1 de cualquier columna:

{=K.ESIMO.MAYOR(((MULTIPLO.INFERIOR(A1;FILA(INDIRECTO("1:"&ENTERO(A1/2))))=A1)*FILA(INDIRECTO("1:"&ENTERO(A1/2))));FILA())}

y copiándola hacia abajo hasta igualar el número de divisores del número (el resultado de la primera fórmula).

 

El número más alto que pueden procesar ambas fórmulas (en Excel 2003) es el 131.073

La fórmula no considera el número que procesa como divisor de sí mismo, aunque lógicamente lo es.

 

 

Averiguar la suma de los divisores de un número entero positivo

La siguiente fórmula devuelve la suma de los divisores del número entero positivo situado en A1:

{=SUMA((MULTIPLO.INFERIOR(A1;FILA(INDIRECTO("1:"&ENTERO(A1/2))))=A1)*FILA(INDIRECTO("1:"&ENTERO(A1/2))))}

 

El número más alto que puede procesar esta fórmula (en Excel 2003) es el 131.073

La fórmula no considera el número que procesa como divisor de sí mismo, aunque lógicamente lo es.

 

Averiguar si un número entero positivo es Defectivo, Perfecto o Abundante

La siguiente fórmula dirá qué tipo de número es el situado en A1:

{=ELEGIR(SIGNO(SUMA((MULTIPLO.INFERIOR(A1;FILA(INDIRECTO("1:"&ENTERO(A1/2))))=A1)*FILA(INDIRECTO("1:"&ENTERO(A1/2))))-A1)+2;"Defectivo";"Perfecto";"Abundante")}

 

El número más alto que puede procesar esta fórmula (en Excel 2003) es el 131.073

 

 

 

 

 

 

Página inicial