¡SORPRESA: Cambios en los nombres de las funciones en Excel 2010!

Ni idea porqué, pero algunas funciones de hoja de cálculo han cambiado de nombre entre las versiones 2007 y 2010 de Microsoft Excel. ¿Se habrá jubilado el traductor anterior?

Aquí va una tabla de las que he detectado y localizado... (No busquéis en la ayuda, la referencia no está e incluso os encontraréis listados con funciones que ya no existen...)

ERA... (Excel 2007)

AHORA ES... (Excel 2010)

LETRA.DE.TEST.EQV.A.BONO SI.ERROR CARACTER ESPACIOS EXTRAE - - DIAS360 FECHANUMERO HORANUMERO NSHORA BUSCARH BUSCARV - - - RAIZ RESIDUO BINOM.CRIT COVAR - CUARTIL - DESVEST DESVESTP DIST.WEIBULL DISTR.BETA DISTR.BETA.INV DISTR.BINOM DISTR.CHI - DISTR.EXP DISTR.F - DISTR.F.INV - DISTR.GAMMA DISTR.GAMMA.INV DISTR.HIPERGEOM DISTR.LOG.INV DISTR.LOG.NORM DISTR.NORM.ESTAND DISTR.NORM.ESTAND.INV DISTR.NORM DISTR.NORM.INV DISTR.T DISTR.T.INV - - - - INTERVALO.CONFIANZA - JERARQUIA - MODA - NEGBINOMDIST PERCENTIL - POISSON PRUEBA.CHI PRUEBA.CHI.INV PRUEBA.F PRUEBA.T PRUEBA.Z RANGO.PERCENTIL - VAR VARP CONVERTIR - - NOD

LETRA.DE.TES.EQV.A.BONO SIERROR CAR RECORTAR MED DIA.LAB.INTL DIAS.LAB.INTL DÍAS360 VALFECHA VALHORA TIEMPO CONSULTAH CONSULTAV AGREGAR MULTIPLO.INFERIOR.EXACTO MULTIPLO.SUPERIOR.EXACTO RCUAD RESTO BINOM.CRIT*, INV.BINOM COVAR*, COVARIANZA.M COVARIANCE.P CUARTIL*, CUARTIL.INC CUARTIL.EXC DESVEST*, DESVEST.M DESVESTP*, DESVEST.P DIST.WEIBULL*, DISTR.WEIBULL DISTR.BETA*, DISTR.BETA.N DISTR.BETA.INV*, INV.BETA.N DISTR.BINOM*, DISTR.BINOM.N DISTR.CHI*, DISTR.CHICUAD DISTR.CHICUAD.CD DISTR.EXP*, DISTR.EXP.N DISTR.F*, DISTR.F.N DISTR.F.CD DISTR.F.INV*, INV.F INV.F.CD DISTR.GAMMA*, DISTR.GAMMA.N DISTR.GAMMA.INV*, INV.GAMMA DISTR.HIPERGEOM*, DISTR.HIPERGEOM.N DISTR.LOG.INV*, INV.LOGNORM DISTR.LOG.NORM*, DISTR.LOGNORM DISTR.NORM.ESTAND*, DISTR.NORM.ESTAND.N DISTR.NORM.ESTAND.INV*, INV.NORM.ESTAND DISTR.NORM*, DISTR.NORM.N DISTR.NORM.INV*, INV.NORM DISTR.T*, DISTR.T.N DISTR.T.INV*, INV.T DISTR.T.2C INV.T.2C DISTR.T.CD GAMMA.LN.EXACTO INTERVALO.CONFIANZA*,    INTERVALO.CONFIANZA.NORM INTERVALO.CONFIANZA.T JERARQUIA*, JERARQUIA.EQV JERARQUIA.MEDIA MODO*, MODA.UNO MODA.VARIOS NEGBINOMDIST*, NEGBINOM.DIST PERCENTIL*, PERCENTIL.INC PERCENTIL.EXC POISSON*, POISSON.DIST PRUEBA.CHI*, PRUEBA.CHICUAD PRUEBA.CHI.INV*, INV.CHICUAD PRUEBA.F*, PRUEBA.F.N PRUEBA.T*, PRUEBA.T.N PRUEBA.Z*, PRUEBA.Z.N RANGO.PERCENTIL*, RANGO.PERCENTIL.INC RANGO.PERCENTIL.EXC VAR*, VAR.S VARP*,VAR.P CONVERT FUN.ERROR.COMPL.EXACTO FUN.ERROR.EXACTO ND

* Función mantenida por compatibilidad; puede deaparecer en futuras versiones. ¿Por qué no se han mantenido los nombres anteriores en todos los casos?

Afortunadamente, no es necesario preocuparse por la compatibilidad de los libros que usan estas funciones ya que la codificación interna se mantiene en la mayoría de los casos y el funcionamiento no debería verse afectado. Aunque nunca está de más revisarlo...

ACTUALIZACIÓN (octubre-2011):  El SP1 de Microsoft Office 2010 publicado en junio de 2011 (http://support.microsoft.com/kb/2460049) ha revertido los nombres de las funciones a los de Office 2007. ¡Suerte que nos han dejado un año para aprendernos lo que ahora deberemos desaprender!

Funciones de Excel en VBA

Cuando programamos en VBA para Excel, a menudo resulta cómodo usar las funciones y programadas en Excel que no existen en VBA (PI, ACOS, ASENOH...). ¿Cómo lo hacemos? Al menos hay tres formas distintas de acceder a ellas... pero en todos los casos las funciones se llaman por su nombre en la versión inglesa de la hoja de cálculo.

Opción 1
Usar las funciones como métodos del objeto Application.WorksheetFunction, por ejemplo:

Opción 2
Escribir la función en una celda de Excel, por ejemplo creando un libro nuevo para hacer el cálculo:

Opción 3
Usar el método Evaluate...

Usando las funciones de respuesta matricial desde VBA

Excel incorpora entre las funciones que reconoce algunas funciones que no devuelven un único valor sino matriz de datos. Son las denominadas funciones de respuesta matricial.

¿Podemos usar estas funciones desde VBA para Excel? ¿Cómo?
Sí, las funciones de respuesta matricial como MINVERSA, MMULT o TRANSPONER pueden ser llamadas desde VBA como cualquier otra función de Excel a partir del objeto Application.WorksheetFunction siendo los funciones (en inglés) miembros de este objeto [MInverse, MMult, Transpose].

Sin embargo al usar estas funciones deben tenerse en cuenta algunas consideraciones:

• Las dimensiones de la matrix deben coincidir los índices usados. Por ejemplo si  la matriz se declara a partir de 0, deben situarse los elementos de la misma a partir de dicho índice; una matriz declarada como A(3,3) y con valores en los elementos.
• El argumento matricial de estas funciones puede ser una matriz de tipos simples, un dato de tipo Variant con contenido matricial o un rango de celdas de cálculo (objeto Range).
• La respuesta es un tipo Variant con contenido matricial.

Un ejemplo de estos usos se muestra en la imagen adjunta.

Gráficos condicionales

A veces, queremos desarrollar una plantilla de cálculo que tiene respuesta gráfica. Y a veces, esta respuesta debe variar en función de unas u otras condiciones. Pero un gráfico no acepta condiciones... ¿Cómo cambiamos condicionalmente un gráfico? Por ejemplo, ¿cómo conseguimos hacer un gráfico de medias móviles tal que su contenido dependa de datos introducidos por el usuario (datos de partida, tipo de media móvil, número de datos promediados...)?

Esta pregunta implica dos cuestiones distintas:

• ¿Cómo cambiar los datos representados?
• ¿Cómo cambiar el número de datos representados? o ¿cómo hacer que algunos datos sólo se muestren cuando sea conveniente?

¿Cómo cambiar los datos representados?
La solución pasa por representar siempre el mismo rango de datos y que sean estos datos los que varíen condicionalmente. Las celdas de cálculo representadas contendrán fórmulas tales que daran los resultados esperados en función de los datos introducidos por el usuario.

¿Cómo cambiar el número de datos representados? o ¿cómo hacer que algunos datos sólo se muestren cuando sea conveniente?
Para conseguir que algunas de las celdas seleccionadas en la serie de datos no figuren en el gráfico en ellas deberá aparecer el mensaje de error #N/A (no disponible). Este mensaje de error puede obtenerse llamando a la función NOD(). Por ejemplo para evitar que se represente cualquier un valor que puede resultar no numérico podría usarse la fórmula
=SI(ESNUMERO(VALOR(A1));A1;NOD())
y representar el resultado de esta última celda en lugar de la inicial.

Usando el objeto Range para programar funciones matriciales

A veces nos encontramos con la necesidad de programar alguna función definida por el usuario (en VBA) de forma que acepte una matriz como argumento. Sin embargo, si usamos como argumento una matriz, la función no funcionará cuando queramos usar un conjunto de celdas de Excel como argumento... La respuesta está en el objeto Range.

El objeto Range se refiere a un rango de celdas de la hoja de cálculo y puede ser pasado como argumento a una función de VBA. Por ejemplo, la función

Public Function miFuncion (miRango as Range) As Integer

    Dim numeroCeldas As Integer

    numeroCeldas=miRango.Rows.Count*miRango.Columns.Count

    miFuncion=numeroCeldas

End Function

cuenta el número de celdas que hay en un rango.

Las propiedades más importantes de este objeto son:

• .Cells(i,j).Value: Permite acceder al valor de una celda del rango. Los valores i y j corresponden a fila y columna respectivamente siendo el primer valor, esquina superior izquierda, el (1, 1).
• .Columns.Count: Indica el número de columnas que tiene el rango.
• .Rows.Count: Indica el número de filas que tiene el rango.
  

Con estas tres propiedades se puede desarrollar cualquier función que requiera la transferencia de una matriz como argumento.

¿Qué va antes? Sobre prioridades de cálculo....

Algunos de los errores más frecuentes (y que peor sientan) que aparecen (a veces incluso misteriosamente) en los trabajos de los alumnos, son errores de cálculo debido a la consideración incorrecta de las propiedades de cálculo.

Recordemoslas. Las operaciones en Excel se ejecutan (con los criterios matemáticos habituales) de acuerdo con el siguiente orden:

1. Paréntesis (())
   
2. Funciones (SENO, EXP, RESIDUO, Y, NO...)
   
3. Negación (-, operación unaria)
4. Porcentaje (%)
   
5. Potencia (^)
6. Producto (*), división (/)
   
7. Suma (+), resta (-)
8. Concatenación (&)
   
9. Comparación (=, <, >, <=, >=, <>)

Las operaciones de misma prioridad se evalúan de izquierda a derecha.

 Algunos de los errores más habituales son:

• escribir "=A1/A2+A3" en lugar de "A1/(A2+A3)"
• escribir "=A1/A2*A3" en lugar de "=A1/(A2*A3)" o "=A1/A2/A3"
• escribir "=A1^1/3" en lugar de "=A1^(1/3)"
• escribir "=-A1^2" en lugar de "=-(A1^2)" o "=-1*A1^2"

CONSEJOS

• Comprueba tus cálculos. Puedes usar para ello valores simples de los datos tales que sepas que deben dar.
• Fragmenta tus cálculos. Desarrolla tus cálculos en más de una celda. Si necesitas que el cálculo esté en una única celda, házlo fragmentado en paralelo; corrige tus fórmulas hasta que ambos cálculos te den resultados consistentes. Entonces puedes eliminar el cálculo fragmentado.
  

Concatenar rangos ¿o no?

Cuando realizamos cálculos en Excel no es infrecuente que llegado a un punto nos sea necesario concatenar un conjunto relativamente largo, posiblemente indeterminado, de celdas en un rango fila o columna. ¿Cómo lo hacemos?

No, la función CONCATENAR no funcionar para rangos; tampoco el operador &.

Dos son las respuestas que suelen darse a este problema:

Opción 1
Crear una función definida por el usuario (en VBA) que permita concatenar rangos; veáse, por ejemplo, http://excel.tips.net/Pages/T003062_Concatenating_Ranges_of_Cells.html.

Opción 2
Si no puede hacerse, no se hace. Busquemos un procedimiento alternativo, en lugar de concatenar todas las celdas de golpe, las añadimos secuencialmente a la cadena construida hasta el momento; en la última celda tendremos la concatenación deseada.
Por ejemplo, si queremos concatenar el rango A5:BA5 en la fila 6 (asumiendo que está libre) escribiremos:

• en A6, escribiremos =A5,
• en B6 y siguientes, =A6+B5
• Se extiende hasta el final del área de trabajo. En BA6 está el rango concatenado.
  

Espero que te sea útil.

Funciones de respuesta matricial

Distintas funciones en Excel devuelven más de un valor como respuesta, ejemplos son las funciones TRANSPONER, MMULT, MINVERSA, FRECUENCIA, ESTIMACION.LINEAL...

Sin embargo la ejecución estándar (con Enter) sólo devuelve un valor y no el conjunto de valores esperados...
¿Cómo deben ejecutarse?

Su ejecución implica la selección previa del rango de respuesta y su ejecución matricial (con Ctrl+Shift+Enter). El proceso completo es el siguiente:

1. Escribir la fórmula en la celda superior izquierda del rango previsto de respuesta. Pulsar Enter.
   
2. Seleccionar el rango de respuesta a partir de la celda anterior.
3. Editar la fórmula manteniendo la selección (por ejemplo pulsando F2).
4. Ejecutar la fórmula de forma matricial pulsando Ctrl+Shift+Enter de forma simultánea.

NOTAS

• Si el rango de respuesta seleccionado es mayor que el conjunto de datos devuelto por la función, el rango se rellena con valores de error #N/A (valor no disponible).
• Si el rango de respuesta seleccionado es menor al conjunto de datos devuelto por la función, el resultado bien será parte del resultado total bien un conjunto de mensajes de error.
• Una vez ejecutada la función matricial, el rango de respuesta sólo puede editarse o borrarse de forma conjunta. Si aparece el mensaje de alerta "No puede cambiarse parte de una matriz." puede cancelarse la edición con la tecla Esc.

Parecen números...¿lo son?

A veces en Excel, un número queda alineado a la izquierda. Un primer error es ignorarlo; el segundo es alinearlo a la derecha porque queda más bonito (o porque es donde debería estar).
Cuando esto pasa es porque eso que parece un número es internamente texto, sea porque el formato de número de la celda es de texto, porque se ha puesto un apóstrofe como primer carácter (ello fuerza el comportamiento como texto), porque se ha creado como cadena de texto o porque es el resultado de cálculo de una función de texto.

En cualquier caso, operar con texto no siempre funciona (aunque a veces, sí) lo cual genera errores difíciles de detectar y de interpretar. Para muestra un botón....

CONSEJO
Tén presente el tipo de dato que contiene tu celda de cálculo y no hagas cálculos con texto cuando esperas un número. Tres expresiones pueden ser útiles:

• ESNUMERO(ref): indica si el contenido de una celda está considerado como número.
• ESNUMERO(VALOR(ref)): indica si el contenido de una celda puede ser reconocido como número.
• VALOR(ref): convierte a número.