Wednesday, January 27, 2010

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.

Monday, January 25, 2010

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.

Sunday, January 24, 2010

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

Saturday, January 23, 2010

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.

    Tuesday, January 19, 2010

    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.