Porque lo "VERDADERO" no es VERDADERO

En Excel, hay dos palabras que aunque las escribamos directamente no se interpetan como texto... verdadero y falso (Excel en español). A pesar de que, a menudo, no seamos consciente de ello, al escribir estas dos palabras, se convierten a mayúsculas y quedan centradas en la celda. Son valores lógicos.

De hecho podemos escribir verdadero como texto, por ejemplo escribiendo ="VERDADERO" o 'VERDADERO. Pero entonces será un valor distinto. Y puede que no funcione como lo esperemos. La confusión entre el "VERDADERO" (texto) y el VERDADERO (lógico) genera dificultades y alguna que otra sorpresa. Por ello, este comentario...

En una celda, el valor lógico se escribe directamente (VERDADERO o  FALSO) o mediante la función correspondiente: =VERDADERO() o =FALSO(). El texto, con un apóstrofe delante o entre comillas, ="VERDADERO". En una fórmula, el texto se escribe entre comillas "VERDADERO" y el valor lógico sin ellas, VERDADERO.

Antes de ver algunos ejemplos y curiosidades, citar el efecto que estas diferencias tienen en entornos internacionales. El valor lógico se traduce de forma automática al idioma de la versión de Excel en uso. El texto, no; con lo cual el valor lógico permite construir interfaces más usables.

En las filas 2 y 14, se han introducido los valores lógicos (columna A) y de texto (columna B) y, en las siguientes filas, se han hecho los cálculos que se indican en la columna C, observándose que:

• Los valores lógicos y de texto no son comparables. Son distintos.
• La función CONTAR.SI solo cuenta los valores lógicos aunque usemos construcciones que parecerían referirse a la entrada como texto. ¡La entrada como texto no se cuenta ni cuando contamos valores iguales a sí mismo!
• Los valores lógicos se cuentan igual que los de texto; se incluyen en un CONTARA y no en un CONTAR.
• Las funciones lógicas Y y O ignoran los valores introducidos como texto.

Pero esto es en Excel, os añado la misma hoja abierta en OpenOffice Calc...

En Calc, el CONTAR.SI cuenta los valores de texto y el valor lógico se cuenta con CONTAR (de hecho internamente es un número). ¡Cuidado al crear hojas de cálculo compatibles con ambos sistemas!

Si ya lo dicen los filósofos que el concepto de verdad es problemático...

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