viernes, 20 de abril de 2012

Condiciones de Excel

Aunque Excel incluye una batería de más de trescientas funciones, pocas veces las usamos. La función condicional ‘SI’ permite resolver situaciones muy comunes. Y es aún más poderosa combinada con otras funciones.

La hoja de la Figura 1 es una lista de vendedores con el importe de sus ventas. Queremos dar un premio de 50 € a quienes hayan superado la meta de 5.000 € vendidos.
Considerando los importes de las ventas en la columna C, a partir de la segunda fila (reservamos la primera para títulos) el premio se obtiene escribiendo en D2 la función condicional =SI(C2>5000;50;0).

La función condicional SI tiene tres argumentos entre los paréntesis:
  • El primero es la condición de la que depende el valor que finalmente devolverá la fórmula. En nuestro caso, que las ventas superen la meta de 5.000 €.
  • El segundo es el valor que devolverá la fórmula si la condición se cumple. En nuestro caso, el premio de 50 €.
  • El tercero es el valor que devolverá la fórmula si la condición no se cumple. En nuestro caso, cero, porque para ellos no hay premio.
La función escrita en D2 debe extenderse luego a toda la lista.

Funciones condicionales en Excel

Suma con condiciones. La función SUMAR.SI
La función SI no es la única función condicional de Excel. Por ejemplo, la hoja de la Figura 1 indica, en la columna B, la región a la que pertenece cada vendedor. Queremos calcular el total de ventas de la región Sur. Para esto podemos usar la función SUMAR.SI: =SUMAR.SI(B2:B11; “Sur”;C2:C11), tal como se ve en la hoja de la Figura 2. Esta función también tiene tres argumentos entre los paréntesis:
  • El primero es el rango donde se evalúa la condición. En este caso, el rango donde se indican las regiones.
  • El segundo es el valor que debe tener el rango anterior para que sea tenido en cuenta en la suma. En este caso, la palabra Sur.
  • El tercero es el rango que se suma. En este caso, el de las ventas.
La función SUMAR.SI compara cada celda del rango indicado como primer argumento con el valor del segundo. Cuando se satisface la igualdad, suma el valor correspondiente del tercer argumento.

Condicionales combinadas
El cálculo anterior puede hacerse también con una variante poco conocida de la función condicional, combinándola con la sumatoria: =SUMA(SI(B2:B11= “Sur”;C2:C11)). Pero, atención, a esta expresión hay que darle entrada con la combinación de teclas Control + Mayús + Intro.

Como se ve en la Figura 3, la fórmula queda encerrada entre llaves. Estas llaves no se escriben, sino que aparecen como consecuencia de haber usado la combinación de teclas Control + Mayús + Intro.

En esta expresión se evalúa la condición de tener el valor Sur en el rango B2:B11. Si se satisface la condición, se suma el respectivo valor de C2:C11. El resultado es el mismo que el devuelto por la función SUMAR.SI.

Cálculos con más de una condición
La fórmula del ejemplo anterior admite variantes más complejas. Por ejemplo, en la hoja de la Figura 4 combinamos la suma y la condicional para sumar las ventas de la región Sur, que superan los 3.000 €: =SUMA(SI(B2:B11=”Sur”;SI(C2:C11 >3000;C2:C11))). A esta expresión también se le da entrada con la combinación de teclas Control + Mayús + Intro.
Esta fórmula evalúa primero la condición de pertenecer a la región Sur. Si esta condición se cumple, evalúa si las ventas son mayores que 3.000. Si esta segunda condición también se cumple, se suma el correspondiente valor de ventas.

Conteo condicional
En principio, cualquier función estadística o matemática puede sujetarse a una condición. Por ejemplo, la hoja de la Figura 5 muestra una lista de alumnos con la calificación obtenida en la columna C. ¿Cuántos alumnos del turno Tarde obtuvieron una nota mayor que 5? En este caso tenemos que vincular las funciones SI y CONTAR: =CONTAR(SI(B2:B11=”Tarde”;SI(C2:C11>5;C2:C11))).

Como en las casos anteriores, a esta expresión se le debe dar entrada con la combinación Control + Mayús + Intro.

La expresión evalúa las condiciones de pertenecer al turno Tarde y tener una nota mayor que 5. Si ambas condiciones se cumplen, se cuentan las notas.

Funciones condicionales en Excel

Máximo condicional
Ahora supongamos el caso de una hoja de dos columnas: en la primera tenemos fechas y en la segunda tenemos los nombres de personas que visitamos en cada fecha. Una persona puede repetirse porque la visitamos más de una vez. Queremos saber cuándo fue la última vez visitamos a Pedro.

Este problema equivale a encontrar la mayor fecha (es decir, la última) para la cual en la segunda columna está el valor Pedro: =MAX(SI(B2:B11=”Pedro”;A2:A11)).

Esta fórmula se interpreta de forma similar a las anteriores: se evalúa si en el rango B2:B11 está el valor Pedro. De las filas que cumplen esta condición, busca la que tiene el máximo valor en la columna de fechas.

Se pueden hacer cálculos similares combinando la función SI con MIN, PROMEDIO, MODA, etc; siempre oprimiendo la combinación Control + Mayús + Intro para dar por ingresada la fórmula.

Funciones condicionales en Excel

A1<=>A
B1<=>B
C1<=>C
D1<=>D
Celda donde va la fórmula <=> X

=SI(Y(A1="si";B1="si";C1="si");10;SI(Y(A1="si";B1="si";C1="no");9;SI(Y(A1="si";B1="no";C1="si");9;SI(Y(A1="si";B1="no";C1="no");8;SI(Y(A1="no";B1="no";C1="si");7;SI(Y(A1="no";B1="no";C1="no");6;"")))))) + SI(D1="si";0,5;0)

Macro en Excel

  • Lo primero a realizar es hacer visible la lengüeta de “Programador”. Por defecto esta lengüeta viene oculta en Excel 2007.



  • Iremos a Inicio y daremos click en “Opciones de Excel”.



  • La primera opción es “Más Frecuente” y dentro de ella debemos marcar la opción de “Mostrar Ficha Programador en la cinta de Opciones”.




  • Acá podemos observar como la hemos marcado, que en este caso es la tercera opción.




  • Con ello la lengüeta “Programador” está visible y podemos comenzar a utilizar Macros en nuestro libro de Excel.
Lo que cubriremos en este instructivo es la grabación de Macros. Esto es muy parecido a la grabación de acciones que alguna vez cubrimos en un tutorial de Photoshop.

Grabando una Macro

  • Seleccionamos una celda; cualquier celda servirá.
  • En la lengüeta de programador damos click en “grabar Macro”.


  • Con ello nos aparece la ventana  “Grabar Macro”.



  • En “Nombre de la macro” pondremos un nombre representativo (Ojo no puede llevar espacios por lo que si desean pueden usar guión bajo “_” para poner espacios entre palabras), en “método abreviado” pondremos una combinación  de teclas como acceso directo (por defecto es CTRL +”algo ”, en mi caso utilizá la tecla Shift o Mayúscula más la tecla N), en “Guardar macro en: ” dejamos igual como está, y en “Descripción” anotamos justamente una descripción de lo que hará la macro.

  • Damos click en “Aceptar ”. Con ello la grabación de la Macro comienza, por lo tanto todos los movimientos que realicemos dentro del libro de Excel serán grabados.

  • Anotaremos nuestro nombre en una celda
  • Hacemos click en la celda bajo el nombre y anotaremos la siguiente fórmula: =AHORA()
Esta fórmula muestra la fecha y hora actual.
  • Seleccionamos la celda que contiene la fórmula y la copiaremos (Con CTRL+C o en la lengüeta Inicio -> Copiar)


  • En la lengüeta Inicio está el botón Pegar. Si damos Click en la flecha bajo el ícono verán que aparecen más opciones. Elegimos la opción “Pegar Valores”.

Esto convierte la fórmula a su valor correspondiente.
  • Seleccionamos ambas celdas, osea la celda con la fecha y la celda con el nombre.
  • Aplicamos a ambas celdas un formato, por ejemplo “Negrita ” y en el tamaño de la letra ponemos  “16”.

  • Vamos a la lengüeta Programador y detenemos la grabación de la Macro con el botón respectivo.


¡Felicitaciones! Acabas de grabar tu primera Macro. Esta es una buena oportunidad para llamar a tu madre y contarle las buenas noticias.
Ahora solo nos falta correr la Macro y ver los resultados. Para ello iremos a una nueva hoja de cálculo. Seleccionamos la celda A1.



  • En la lengüeta de Programador hacemos click en Macros con lo cual se nos abre la ventana que administra las Macros.



  • Elegimos nuestra Macro, que en este caso es única y le damos click al botón ejecutar. También si  prefieren pueden seleccionar la celda A1 y simplemente utilizar la combinación de teclas que configuramos, en este caso Ctrl+Shift+N.


Como observarán se ha pegado la información y se le ha dado el formato de letra en negrita y tamaño 16. Con un simple click o combinación de teclas sin necesidad de anotar el nombre, aplicar una fórmula, copiarla, pegarla, seleccionar ambas celdas y darles formato.
Ahora analicemos el código o lenguaje que se ha grabado.
Para ello podemos dar click en el botón “Visual Basic” de la lengüeta Programador o utilizar la combinación de teclas ALT+F11.



Con ello se nos abre el editor de VBA.


Damos click en el símbolo “+” de “Módulos” de manera de ampliar el árbol de módulos y con ello nos aparecerá “módulo 1” tal como se observa en la fotografía.



Con ello se abre el módulo y podemos obsevar el código que se ha generado.
Los comentarios se pueden anotar anteponiendo el símbolo de apóstrofe (’).
En este caso para explicar el código dejaré comentarios en cada línea.