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 ");"")

 

Esta misma fórmula, con unas pequeñas modificaciones, sirve también para convertir en años, meses y días un número de días determinados (valgan como ejemplo los números de días que figuran en los informes de vida laboral de la Seguridad Social española). Si el numero de días a convertir está en A1 la fórmula sería:

 

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

 

Como curiosidad, en esta fórmula todos los +2 deberían ser +1. Esa unidad adicional es necesaria porque Excel, para no perder la compatibilidad, "heredó" un error procedente de Lotus 1-2-3, que erróneamente consideraba que el año 1900 fue bisiesto cuando en realidad no lo fue.

 

 

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)

 

Nota 1: estas fórmulas sirven también, lógicamente, para sumar cada n filas o columnas. Por ejemplo, para sumar cada 5 filas de una columna:

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

 

 

 

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(ESERROR(K.ESIMO.MAYOR(SI(FRECUENCIA(Lista;Lista)>0;Lista);FILA()));"Valor repetido";K.ESIMO.MAYOR(SI(FRECUENCIA(Lista;Lista)>0;Lista);FILA()))}

 

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

{=SI(ESERROR(K.ESIMO.MENOR(SI(FRECUENCIA(Lista;Lista)>0;Lista);FILA()));"Valor repetido";K.ESIMO.MAYOR(SI(FRECUENCIA(Lista;Lista)>0;Lista);FILA()))}

 

En Excel 2007-2010 es posible utilizar la función SI.ERROR en lugar de la pareja SI y ESERROR, con lo que las fórmulas resultan bastante más cortas y eficientes:

 

{=SI.ERROR(K.ESIMO.MAYOR(SI(FRECUENCIA(Lista;Lista)>0;Lista);FILA());"Valor repetido")}

{=SI.ERROR(K.ESIMO.MENOR(SI(FRECUENCIA(Lista;Lista)>0;Lista);FILA());"Valor repetido")}

 

La función FILA() que utilizan todas las fórmulas puede ser sustituida por un entero concreto, en cuyo caso la fórmula devolverá el elemento situado en esa posición en la lista de elementos únicos.

 

Si las fórmulas hubiesen de estar situadas a partir de una fila distinta a la 1 habría que sustituir FILA() por FILA()-n, siendo n dicho número de fila+1 (por ejemplo, si se tratase de la fila 3 sería FILA()-2)

 

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 (65.536 en la versión 2003, 1.048.576 a partir de la versión 2007), 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.

 

Si se sabe que tan sólo falta un número en la serie de enteros positivos y que no hay repeticiones, es posible utilizar una fórmula más sencilla (aunque más interesante desde un punto de vista matemático) para averiguar cual es el que falta. Por ejemplo, si en la columna A se encuentra la serie de números (y sólo la serie) sin repeticiones, la fórmula sería:

 

=(MIN(A:A)+MAX(A:A))*(MAX(A:A)-MIN(A:A)+1)/2-SUMA(A:A)

 

 

 

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.

 

 

Encontrar un valor en un rango de varias filas y columnas  [Ejemplo]

Si, por ejemplo, se desea averiguar la dirección de la celda en la que se encuentra el número 111111 dentro del rango con nombre Datos, la fórmula sería:

{=DIRECCION(COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(Datos;FILA(Datos)-CELDA("fila";Datos);;1;);111111));0);COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(Datos;;COLUMNA(Datos)-CELDA("columna";Datos);;1);111111));0))}

 

A partir de Excel 2007 se puede utilizar esta fórmula junto con ESERROR() y NO() como fórmula para una regla de validación. En Excel 2003 y versiones anteriores esto no es posible porque las fórmulas necesarias superarían el límite de siete niveles de anidamiento permitidos.

 

Conviene recordar que la función DIRECCION() admite otros dos argumentos adicionales (tres en realidad, pero el último no nos interesa en lo que concierne a esta fórmula):

 - dirección absoluta, relativa o una mezcla de ambas

 - estilo de referencia A1 (por defecto) o R1C1 (si se pone un 0 como último argumento)

 

Nota: El uso de la función CELDA() resultaría innecesario si se supiese con total seguridad que el rango con nombre Datos empieza en A1. En este caso la fórmula podría acortarse poco:

 

{=DIRECCION(COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(Datos;FILA(Datos)-1;;1;);111111));0);COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(Datos;;COLUMNA(Datos)-1;;1);111111));0))}

 

y además se habría conseguido ahorrar un nivel de anidamiento con respecto a la primera fórmula, con lo que esta segunda fórmula sí podría ser utilizada [junto con NO() y ESERROR(), lógicamente] como fórmula para una validación de datos en Excel 2003. Por ejemplo, si la celda con la validación fuese F1, la fórmula sería:

 

=NO(ESERROR(DIRECCION(COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(Datos;FILA(Datos)-1;;1;);F1));0);COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(Datos;;COLUMNA(Datos)-1;;1);F1));0))))

 

 

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 (hasta Excel 2003). 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 2203)

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

 

Otra, un poco más larga:

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

 

Nota: Pedro Wave me ha corregido amablemente el rango de fechas de estas fórmulas y me señala que fallan para el año 2079. Sugiero visitar su blog en el que se trata el tema del cálculo del domingo de Pascua en Excel de forma minuciosa.

 

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)

          {=SUMA(SIGNO(FRECUENCIA(SI(SUBTOTALES(3;DESREF(B2:B15;FILA(B2:B15)-2;;1))>0;B2:B15);SI(SUBTOTALES(3;DESREF(B2:B15;FILA(B2:B15)-2;;1))>0;B2:B15)))}

           {=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 que devuelve una por una 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 carácter 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 los números situados a la derecha o a la izquierda en una cadena de texto.  [Ejemplo]

Si se desea extraer el número situado a la derecha en una cadena situada en A1 (por ejemplo, extraer 9854 de la cadena abcd9854), pero la longitud del número no es fija, se puede usar:

 

{=BUSCAR(9,99999999999999E+307;1*(DERECHA(A1;FILA(INDIRECTO("1:"&LARGO(A1))))))}

 

Para extraer el número situado a la izquierda (por ejemplo, extraer 9854 de la cadena 9854abcd), lo único que habría que hacer es sustituir DERECHA por IZQUIERDA.

 

Estas fórmulas funcionarán con números con decimales, siempre que el separador decimal en la cadena de texto sea el mismo que el establecido en la configuración regional del equipo.

 

 

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;COINCIDIR(1;1*ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1));0);LARGO(A1)-COINCIDIR(1;1*ESNUMERO(1*EXTRAE(A1;LARGO(A1)-FILA(INDIRECTO("1:"&LARGO(A1)))+1;1));0)+1-COINCIDIR(1;1*ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1));0)+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 (es decir, sustituir =EXTRAE por =1*EXTRAE)

 

La fórmula sirve también para extraer una fecha o una hora de una cadena de texto. En este caso sería posible convertir a hora la cadena de texto devuelta por la fórmula usando la función HORANUMERO, o la función FECHANUMERO para convertirla a fecha.

 

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,50 de esta cadena" (devolverá 125,50), pero no con "extraer 125 y 250 de esta cadena" (devolverá "125 y 250").

 

Si se sabe con seguridad que los números a extraer de las cadenas de texto son enteros (no tienen decimales), es posible utilizar una fórmula más sencilla:

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

 

 

 

Extraer todos los caracteres numéricos de una cadena de texto   [Ejemplo] <--Este ejemplo sólo funciona en Excel 2007 y 2010

Una complicación de la fórmula anterior sirve para extraer todos los caracteres numéricos de una cadena de texto, con la limitación de que tan sólo puede haber 14 caracteres numéricos, como máximo, a la derecha del primer número que no sea cero. Es decir, la fórmula funcionará con, por ejemplo, la cadena "xxx0000000000000abc123de45678fg901hi2345jk" (devolverá 0000000000000123456789012345), pero no lo haría con "ab1234cd5678efg9012hi345jk6789" (devolvería 123456789012346000). Esto es debido a la precisión númerica de Excel, que es de 15 dígitos.

 

Esta fórmula sólo funcionará a partir de la versión 2007 ya que supera los siete niveles de anidamiento permitidos como máximo hasta la versión 2003, y es posiblemente una de las más "desmesuradas" de las que hay en esta página, lo que quizás pueda resultar un poco sorprendente porque, en principio, la tarea no parece muy complicada, pero en realidad para una sola fórmula sí lo es:

 

{=REPETIR("0";COINCIDIR(1;ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))*(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)<>"0");0)-LARGO(SUSTITUIR(IZQUIERDA(A1;COINCIDIR(1;ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))*(EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)<>"0");0));"0";"")))&SUMA(EXTRAE(A1;K.ESIMO.MAYOR(ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))*FILA(INDIRECTO("1:"&LARGO(A1)));FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))));1)*10^(FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))))-1))}

 

En la fórmula propuesta, a la expresión necesaria para extraer los números (a partir de SUMA), ya de por sí bastante larga y complicada (282 caracteres), hay que anteponerle una aún más larga expresión adicional (299 caracteres) para contar los posibles ceros a la izquierda y anteponerlos al resto de dígitos para evitar que se pierdan. Por lo tanto, si lo que se necesita es el valor numérico de los caracteres numéricos de la cadena (lo que, lógicamente, implica despreciar los ceros que pudiera haber a la izquierda), es posible prescindir de esa parte de la fórmula (hasta el & de "&SUMA" incluido), con lo que adquiere un tamaño más manejable, aunque sigue sin poder ser utilizada en las versiones anteriores a Excel 2007 dado que mantiene intacta su estructura de anidamientos:

 

{=SUMA(EXTRAE(A1;K.ESIMO.MAYOR(ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))*FILA(INDIRECTO("1:"&LARGO(A1)));FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))));1)*10^(FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))))-1))}

 

Si se tratara de un número con decimales (por ejemplo extraer el número 987,0654 de la cadena "a9b8c7,0d6e5f4"), se podría usar la fórmula:

{=SUMA(EXTRAE(A1;K.ESIMO.MAYOR(ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))*FILA(INDIRECTO("1:"&LARGO(A1)));FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))));1)*10^(FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))))-1))/10^CONTAR(1*EXTRAE(DERECHA(A1;LARGO(A1)-ENCONTRAR(",";A1));FILA(INDIRECTO("1:"&LARGO(DERECHA(A1;LARGO(A1)-ENCONTRAR(",";A1)))));1))}

en la que si el separador decimal fuese el punto en vez de la coma habría que sustituir los "," por ".".

 

Si no se sabe con seguridad cual podrá ser el separador decimal en el equipo en el que se vaya a usar esta última fórmula, es posible complicarla todavía un poco más para que ella misma lo averigüe:

{=SUMA(EXTRAE(A1;K.ESIMO.MAYOR(ESNUMERO(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))*FILA(INDIRECTO("1:"&LARGO(A1)));FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1)))));1)*10^(FILA(INDIRECTO("1:"&CONTAR(1*EXTRAE(A1;FILA(INDIRECTO("1:"&LARGO(A1)));1))))-1))/10^CONTAR(1*EXTRAE(DERECHA(A1;LARGO(A1)-ENCONTRAR(EXTRAE(1/2;2;1);A1));FILA(INDIRECTO("1:"&LARGO(DERECHA(A1;LARGO(A1)-ENCONTRAR(EXTRAE(1/2;2;1);A1)))));1))}
 

Estas fórmulas procesan la cadena de texto situada en A1.

 

 

Extraer una dirección de correo electrónico de una cadena de texto  [Ejemplo]

Sospecho que esta fórmula podría ser utilizada para fines maliciosos, pero como también podría ser útil para alguien con buenas intenciones he decidido ponerla.

Se trata de extraer la dirección de correo electrónico de una celda en la que se encuentran datos personales (nombre, dirección, etc.) además de la dirección de correo electrónico. La fórmula está escrita para el supuesto de que los datos estén separados por comas, pero funcionaría igual si el separador fuese otro, aunque lógicamente habría que sustituir las comas por el separador que fuese:

=ESPACIOS(EXTRAE(SUSTITUIR(","&A1&",";",";REPETIR(" ";500));HALLAR("@";SUSTITUIR(","&A1&",";",";REPETIR(" ";500)))-250;500))
 

 

Saber el número de veces que aparece un carácter 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 2º, 3er., 4º etc. valor más repetido en un rango  [Ejemplo]
Sabemos que la función MODA devuelve el elemento más repetido de un rango. Si se necesita averiguar cuál es el 2º, 3º, etc. valor más repetido de un rango con nombre llamado Datos (que ha de empezar en la fila 1 y estar constituido por valores), se puede usar la fórmula:
 
{=INDICE(Datos;COINCIDIR(K.ESIMO.MAYOR(SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/10^MAX(LARGO(Datos-ENTERO(Datos))));FILA());SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/10^MAX(LARGO(Datos-ENTERO(Datos))));0))}

Para que esta fórmula —tal como está aquí— devolviese el 2º valor más repetido, tendría que estar situada en la fila 2. Situada en la fila 1, la fórmula devolvería el elemento más repetido, lo que carece de sentido porque eso mismo se puede conseguir mucho más fácilmente utilizando =MODA(Datos), si bien hay que precisar que en las versiones de Excel que no disponen de la función MODA.VARIOS, la fórmula propuesta representa un modo de conseguir que vayan aparecido todos los elementos "empatados" en la primera posición de la clasificacion por su cantidad de apariciones, si es que hay más de uno.

La parte de la fórmula que determina el elemento a devolver es la función FILA(), por lo que si se sustituye esta función por un número entero específico la fórmula devolverá el elemento que ocupe esa posición en la jerarquía de repeticiones.

Esta fórmula necesita jerarquizar los elementos únicos del rango que procesa al objeto de deshacer los posibles "empates" en su cantidad de apariciones, para lo que recurre a la técnica de restarle a cada uno de ellos el resultado de la división de su número de fila entre 10^número de decimales del elemento qué más decimales tenga+2. Esto podría tener como consecuencia que la fórmula no trabajase adecuadamente si uno o varios de los números que debe procesar tuviesen muchos decimales y/o si uno o varios de los números fueran muy grandes.

 

Si los elementos en el rango Datos fuesen cadenas de texto, se podría usar la siguiente variante de la fórmula:

{=INDICE(Datos;COINCIDIR(K.ESIMO.MAYOR(SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/100);FILA());SI(COINCIDIR(Datos;Datos;0)=FILA(Datos);CONTAR.SI(Datos;Datos)-FILA(Datos)/100);0))}

 

Esta fórmula trabajará (en principio) también si los elementos son una mezcla de valores y texto o incluso si tan sólo hay números, pero si los números tuviesen decimales podrían producirse resultados incorrectos.

En caso de empate en el número de repeticiones, estas fórmulas devolverán primero el situado más arriba en el rango, luego el siguiente hacia abajo, etc. Cuando no queden más elementos únicos en el rango Datos, comenzarán a devolver el error #¡NUM!

 

 

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 carácter 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. En Excel 2010 es posible utilizar la nueva función MODA.VARIOS para devolver la lista de elementos "empatados" (si los hay) en el primer puesto por el número de apariciones. La fórmula sería:

{=INDICE(MODA.VARIOS(COINCIDIR(EXTRAE($A$1;FILA(INDIRECTO("1:"&LARGO($A$1)));1);EXTRAE($A$1;FILA(INDIRECTO("1:"&LARGO($A$1)));1);0));FILA())}

 

que habría que colocar en la fila 1 de cualquier columna vacía y copiarla y pegarla hacia abajo. Cuando no haya más elementos "empatados" en el primer puesto por el número de apariciones, comenzará a aparecer el error #¡REF!

 

Complicando un poco la primera fórmula propuesta es posible, en caso de empate, devolver el carácter más a la derecha (útil para las versiones anteriores a la 2010, que no disponen de la función MODA.VARIOS):

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

 

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 carácter, 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(0&137498625;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(0&137498625;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(0&137498625;FILA(INDIRECTO("1:10"));1)+1));11);1)&" "&C1}

 

Notas:

 

 

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

Suponiendo que en A1 está 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)&DERECHA(A;4)&1428;97)&"00";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)&DERECHA(A1;4)&1428;97)&"00";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)&DERECHA(A1;4)&1428;97)&"00";97);2)&" "&IZQUIERDA(A1;4)&" "&EXTRAE(A1;5;4)&" "&EXTRAE(A1;9;4)&" "&EXTRAE(A1;13;4)&" "&EXTRAE(A1;17;4)

 

Notas:

 

 

Averiguar la letra de control de un NIF (Número de Identificación Fiscal en España) o un NIE (Número de Identificación de Extranjeros)  [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)

 

Respecto a los NIE, hay que tener en cuenta que deben comenzar siempre por X ó Y (estando previsto que en el futuro puedan empezar también por Z), y tener a continuación siete dígitos. La fórmula para averiguar el dígito de control del NIE situado en A1 sería:

 

=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";RESIDUO(ABS(CODIGO(IZQUIERDA(A1;1)))-88&DERECHA(A1;7);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: vaya mi agradecimiento a quien sea que haya preparado el generador de RUTs válidos (http://joaquinnunez.cl/jQueryRutPlugin/generador-de-ruts-chilenos-validos.html), el cual me ha servido para poder verificar que la fórmula funciona correctamente.

 

 

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)

 

Una amable persona de Uruguay, RJM, me informa de que esta fórmula falla si ha de calcular un número < 1.000.000 y me sugiere una modificación que permite calcular dichos números:

 

=DERECHA(10-RESIDUO(SUMAPRODUCTO(VALOR(DERECHA(EXTRAE(TEXTO(A1;"0000000");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)}

 

 

Calcular el dígito verificador de un Número de Cédula ecuatoriano

Suponiendo que en A1 se encuentra el número de cédula cuyo dígito verificador se desea calcular, la fórmula sería:

{=10-RESIDUO(SUMA(SI(EXTRAE(A1;FILA(INDIRECTO("1:9"));1)*RESIDUO(212121212;FILA(INDIRECTO("1:9"));1)>9;RESIDUO(A1;FILA(INDIRECTO("1:9"));1)*RESIDUO(212121212;FILA(INDIRECTO("1:9"));1)-9;RESIDUO(A1;FILA(INDIRECTO("1:9"));1)*RESIDUO(212121212;FILA(INDIRECTO("1:9"));1)));10)}

 

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

{=10-RESIDUO(SUMA(SI(EXTRAE(A1,FILA(INDIRECTO("1:9")),1)*RESIDUO(212121212,FILA(INDIRECTO("1:9")),1)>9,RESIDUO(A1,FILA(INDIRECTO("1:9")),1)*RESIDUO(212121212,FILA(INDIRECTO("1:9")),1)-9,RESIDUO(A1,FILA(INDIRECTO("1:9")),1)*RESIDUO(212121212,FILA(INDIRECTO("1:9")),1))),10)}

 

Nota: no dispongo de ejemplos reales de Cédulas del Ecuador para comprobar el correcto funcionamiento de la fórmula, pero parece que sí funciona puesto que calcula bien el ejemplo planteado aquí.

 

 

Calcular el dígito verificador de un IMSS mexicano

Si el número (10 dígitos) está en A1, la fórmula será:

 

=DERECHA(10-RESIDUO(SUMAPRODUCTO(DERECHA(EXTRAE(A1;FILA(INDIRECTO("1:10"));1)*(EXTRAE(1212121212;FILA(INDIRECTO("1:10"));1));1)+(EXTRAE(A1;FILA(INDIRECTO("1:10"));1)*(EXTRAE(1212121212;FILA(INDIRECTO("1:10"));1))>9)*1);10);1)

 

Utilizando como separador de listas la coma, la fórmula sería:

 

=DERECHA(10-RESIDUO(SUMAPRODUCTO(DERECHA(EXTRAE(A1,FILA(INDIRECTO("1:10")),1)*(EXTRAE(1212121212,FILA(INDIRECTO("1:10")),1)),1)+(EXTRAE(A1,FILA(INDIRECTO("1:10")),1)*(EXTRAE(1212121212,FILA(INDIRECTO("1:10")),1))>9)*1),10),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

 

 

Fijar en una celda la fecha correspondiente al 1º, 2º, 3º, etc. lunes, martes, miércoles, etc. del mes actual.
Por ejemplo, si se necesita dejar fija en una celda la fecha del tercer lunes del mes actual la fórmula podría ser:

{=K.ESIMO.MENOR(SI(DIASEM(FILA(INDIRECTO(FECHA(AÑO(HOY());MES(HOY());1)&":"&FECHA(AÑO(HOY());MES(HOY())+1;1)-1)))=2;FILA(INDIRECTO(FECHA(AÑO(HOY());MES(HOY());1)&":"&FECHA(AÑO(HOY());MES(HOY())+1;1)-1)));3)}

Sustituyendo =2 por=1 se averiguaría el 3er. domingo, =2 por =3 el 3er. martes, etc.; y sustituyendo el 3 que hay al final por 2 se averiguaría la fecha del 2º lunes en vez de la del 3º.

Habrá que aplicarle a la celda donde esté la fórmula algún formato de fecha, ya que por defecto Excel le pondrá formato numérico.

 

 

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 la fórmula 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;""))}

 

 

Promediar cada n filas o columnas  [Ejemplo] 

A partir de la fila 1 de una columna se tiene un rango con nombre llamado Datos y se desea calcular el promedio cada, por ejemplo, 10 filas. La fórmula sería:

 

{=PROMEDIO(SI(RESIDUO(Datos;10)=0;Datos))}

 

Lógicamente, para calcular el promedio cada, por ejemplo, 5 filas, lo único que habría que hacer es sustituir el 10 por un 5.

 

Esta fórmula funcionaría exactamente igual si el rango con nombre Datos estuviese dispuesto en una fila a partir de su columna A.

 

 

Averiguar la/s letra/s de una columna  [Ejemplo]
Esta fórmula devuelve la letra de la columna de la celda A1:

=SUSTITUIR(IZQUIERDA(CELDA("direccion";A1);ENCONTRAR("$";CELDA("direccion";A1);2)-1);"$";"")

Lógicamente, para devolver la letra (o letras) correspondiente/s a otra columna lo único que hay que hacer es sustituir los dos A1 por la dirección de cualquier celda de la columna deseada.

 

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 B1 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órmulas para saber si un número es o no primo  [Ejemplo]

 

NOTA: En este enlace hay una pequeña monografía sobre fórmulas capaces de procesar números más grandes que la expuesta aquí.

 

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))}

 

Es posible acortar un poco las fórmulas, pero en este caso tendrán 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: aconsejo la consulta de éste 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 grado de 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 de la celda en la que se encuentre la fórmula:

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

 

Notas:

 

Si se desease verificar el 2º, 3er o 4º dígito (a partir del 4º no tiene mucho sentido hacerlo, salvo que se trate de muestras enormes de números, puesto que las frecuencias esperadas son prácticamente 10%), las fórmulas serían:

Para el 2º dígito:

{=(SUMA(--(SI(LARGO(ABS(Lista))>1;1*EXTRAE(ABS(Lista);2;1))=FILA()-1))/FILAS(Lista))-SUMA(LOG((FILA(INDIRECTO("1:9"))*10+FILA())/(FILA(INDIRECTO("1:9"))*10+FILA()-1)))}

 

Para el 3er dígito:

{=(SUMA(--(SI(LARGO(ABS(Lista))>2;1*EXTRAE(ABS(Lista);3;1))=FILA()-1))/FILAS(Lista))-SUMA(LOG((FILA(INDIRECTO("10:99"))*10+FILA())/(FILA(INDIRECTO("10:99"))*10+FILA()-1)))}

 

Y para el 4º dígito:

 

{=(SUMA(--(SI(LARGO(ABS(Lista))>3;1*EXTRAE(ABS(Lista);4;1))=FILA()-1))/FILAS(Lista))-SUMA(LOG((FILA(INDIRECTO("100:999"))*10+FILA())/(FILA(INDIRECTO("100:999"))*10+FILA()-1)))}

 

Estas fórmulas habría que pegarlas en la fila 1, y servirían para averiguar las divergencias entre lo esperado y lo real para el cero. Copiando y pegando las fórmulas hasta la fila 10 se mostrarían los valores para el 1, el 2, ... y el 9.

 

Dado que en muchas ocasiones la lista de números a procesar tendrá como origen datos contables, y puesto que casi siempre estos datos tendrán los dos decimales correspondientes a los céntimos, habrá que tener la precaución de eliminar los separadores decimales, para lo que habrá que sustituir (Lista) por (Lista*100), de forma que las fórmulas serían en este caso:

 

Para el 2º dígito:

{=(SUMA(--(SI(LARGO(ABS(Lista)*100)>1;1*EXTRAE(ABS(Lista)*100;2;1))=FILA()-1))/FILAS(Lista))-SUMA(LOG((FILA(INDIRECTO("1:9"))*10+FILA())/(FILA(INDIRECTO("1:9"))*10+FILA()-1)))}

 

Para el 3er dígito:

{=(SUMA(--(SI(LARGO(ABS(Lista)*100)>2;1*EXTRAE(ABS(Lista)*100;3;1))=FILA()-1))/FILAS(Lista))-SUMA(LOG((FILA(INDIRECTO("10:99"))*10+FILA())/(FILA(INDIRECTO("10:99"))*10+FILA()-1)))}

 

y para el 4º dígito:

{=(SUMA(--(SI(LARGO(ABS(Lista)*100)>3;1*EXTRAE(ABS(Lista)*100;4;1))=FILA()-1))/FILAS(Lista))-SUMA(LOG((FILA(INDIRECTO("100:999"))*10+FILA())/(FILA(INDIRECTO("100:999"))*10+FILA()-1)))}

 

De nuevo es posible sustituir ABS(Lista) por Lista si se sabe con seguridad que no hay valores negativos en el rango con nombre Lista, con lo que se ganará algo de rendimiento.

 

A tener en cuenta también que estas fórmulas son incluso más costosas de calcular que las que vimos para el primer dígito, puesto que han de construir matrices adicionales para el cálculo de la suma de las diferencias entre los logaritmos. Así, la fórmula para el 2º dígito debe crear 2 matrices de 9 elementos, la fórmula para el 3er dígito 2 matrices de 90 elementos, y la fórmula para el 4º dígito 2 matrices de 900 elementos. 

 

 

Generar números de un dígito que satisfagan la ley de Benford  [Ejemplo]

Si se utiliza la siguiente fórmula el suficiente número de veces, los porcentajes de apariciones de cada uno de los números del 1 al 9 deberían tender a satisfacer la ley de Benford según más grande vaya siendo la muestra de números:

 

{=COINCIDIR(ALEATORIO();LOG(FILA(INDIRECTO("1:10"))))}

 

La fórmula sólo es aplicable al primer dígito. No obstante, hay que tener en cuenta que la ley de Benford predice también los porcentajes de apariciones de los dígitos siguientes, pero que para dígitos más allá del primero esta fórmula no serviría. En este enlace muestro la forma de generar números de 5 dígitos de longitud, todos ellos satisfaciendo la ley de Benford.

 

 

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 hallar 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")))}

 

 

Rachas  [Ejemplo] <--Este ejemplo sólo funciona en Excel 2007 y 2010

Por ejemplo, si en el rango A1:A30 hay un rango con nombre llamado Serie, con las letras ABAACABBBAAACCAAAABCBBAAAAAACA (una letra en cada celda), y se necesita contar la cantidad de rachas de A que tengan al menos dos aes, la fórmula sería:

 

{=SUMA(SIGNO(SI(FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A")))>=2;FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"))))))}

 

Para averiguar el promedio de elementos que tienen esas rachas, la fórmula sería:

 

{=PROMEDIO(SI(FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A")))>=2;FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A")))))}

 

En esta última fórmula sería posible sustituir PROMEDIO por otra función estadística, como por ejemplo MAX ó MIN.

 

Lógicamente, sustituyendo >=2 por >=número_que_se_desee la fórmula devolverá los datos correspondientes a dicho número.

 

Sería posible obtener un listado con el número de repeticiones que tiene cada una de las rachas. Por ejemplo, para listar el número de repeticiones correspondientes a cada racha con al menos dos aes la fórmula sería:

 

{=INDICE(SI(FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A")))>=2;FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"))));K.ESIMO.MENOR(SI(SI(FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A")))>=2;FRECUENCIA(SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"));SI(Serie="A";FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"A"))));FILA(INDIRECTO("1:"&SUMA(SIGNO(SI(Serie="A";FILA(Serie)))))));FILA()))}

 

Esta fórmula habría que situarla en la fila 1 de cualquier columna vacía, y luego copiarla y pegarla hacia abajo. Cuando no queden rachas por mostrar comenzará a aparecer el error #¡NUM!

 

Como curiosidad, esta última fórmula, con sus más de 950 caracteres, es la más larga de las que aparecen en esta página, y a mi juicio es también una de las más complicadas, si no la que más.

 

Ninguna de las fórmulas anteriores funciona con versiones de Excel anteriores a la 2007 puesto que superan (la última, ampliamente) el límite de siete niveles de anidamiento que admiten dichas versiones.

 

 

Una simplificación de estas fórmulas puede ser utilizada para averiguar la racha que tiene más números positivos y más negativos en el rango con nombre Serie. La fórmula para averiguar la racha con más números positivos es:

 

{=MAX(FRECUENCIA(SI(Serie>0;FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));">0"));SI(Serie>0;FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));">0"))))}

 

y para averiguar la que tiene más números negativos:

 

{=MAX(FRECUENCIA(SI(Serie<0;FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"<0"));SI(Serie<0;FILA(Serie)-CONTAR.SI(DESREF(INDICE(Serie;1);;;FILA(INDIRECTO("1:"&FILAS(Serie))));"<0"))))}

 

Resulta evidente que estas mismas fórmulas sirven también para averiguar las rachas de números mayores o menores a uno dado. Lo único que habría que hacer es sustituir ">0" y "<0" por el número que se necesitase.

 

Estas dos últimas fórmulas sí pueden ser utilizadas en versiones de Excel anteriores a la 2007. En este enlace hay un libro (versión Excel 2003) con ambas fórmulas trabajando.

 

 

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

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($A$1;FILA(INDIRECTO("1:"&ENTERO($A$1/2))))=$A$1)*FILA(INDIRECTO("1:"&ENTERO($A$1/2))));FILA())}

y copiándola hacia abajo hasta igualar el número de divisores del número (el resultado de la primera fórmula). Si se sustituye FILA() por un número entero, la fórmula devolverá el divisor que ocupe ese número en la lista de divisores (ordenada de forma descendente). Si el número excediese la cantidad de divisores, la fórmula devolverá 0.

 

La fórmula no considera el entero 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))))}

 

La fórmula no considera el entero 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 pueden procesar estas fórmulas en Excel 2003 es el 131.073. En Excel 2007 y 2010 es el 2.097.153, pero los cálculos necesarios para números tan grandes pueden llevar bastante tiempo.

 

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

 

 

Complicando estas fórmulas es posible llegar a procesar hasta el 13.107.200 en Excel 2003 y hasta el 209.715.200 a partir de Excel 2007, pero hay que tener en cuenta que los cálculos para números tan grandes pueden requerir muchísimo tiempo y, además, que es posible que aparezca el error "Excel se ha quedado sin recursos" dependiendo del número que se esté intentando procesar y de la memoria de que disponga la computadora.

 

La fórmula para calcular la suma de los divisores del número entero positivo situado en A1 es:

 

{=SUMA((MULTIPLO.INFERIOR(A1;COLUMNA(INDIRECTO("a:cv"))+(FILA(INDIRECTO("1:"&REDONDEAR.MAS((A1/100)/2;0)))-1)*100)=A1)*(COLUMNA(INDIRECTO("a:cv"))+(FILA(INDIRECTO("1:"&REDONDEAR.MAS((A1/100)/2;0)))-1)*100))}

 

La fórmula para obtener el listado de los divisores es:

{=K.ESIMO.MAYOR((MULTIPLO.INFERIOR($A$1;COLUMNA(INDIRECTO("a:cv"))+(FILA(INDIRECTO("1:"&REDONDEAR.MAS(($A$1/100)/2;0)))-1)*100)=$A$1)*(COLUMNA(INDIRECTO("a:cv"))+(FILA(INDIRECTO("1:"&REDONDEAR.MAS(($A$1/100)/2;0)))-1)*100);FILA())}

Si se sustituye FILA() por un número entero, la fórmula devolverá el divisor que ocupe esa posición en la lista de divisores ordenada de mayor a menor. Si el número excediese la cantidad de divisores, la fórmula devolverá 0. Si se desease la lista de divisores ordenada de menor a mayor lo único que habría que hacer es sustituir K.ESIMO.MAYOR por K.ESIMO.MENOR.

 

Y la fórmula para averiguar si el número es Defectivo, Perfecto o Abundante:

 

{=ELEGIR(SIGNO(SUMA((MULTIPLO.INFERIOR(A1;COLUMNA(INDIRECTO("a:cv"))+(FILA(INDIRECTO("1:"&REDONDEAR.MAS((A1/100)/2;0)))-1)*100)=A1)*(COLUMNA(INDIRECTO("a:cv"))+(FILA(INDIRECTO("1:"&REDONDEAR.MAS((A1/100)/2;0)))-1)*100))-A1)+2;"Defectivo";"Perfecto";"Abundante")}

 

Hay que tener en cuenta que las dos últimas fórmulas podrían no devolver el resultado correcto si el número a procesar fuese inferior al 101.

 

 

Página inicial