Operadores de Excel – Interseccion (Espacio)

Operadores son las herramientas que le permiten a Excel saber que tipo de operación queremos realizar. Por ejemplo, el operador "+" indica la operación suma.

Excel define cuatro tipos de operadores: aritméticos, de comparación, texto y referencia.

Los dos primeros son los más conocidos, como el ya mencionado suma (+) o multiplicación (*) entre los aritméticos y mayor que (>) o menor que (<) entre los de comparación. Existe un único operador de texto, el "&", que nos permite unir cadenas de texto. Los operadores de referencia son los menos conocidos. Incluyen - los dos puntos (":"), el operador de rango que crea una referencia a todas las celdas incluidas entre dos referencias (por ejemplo SUMA(A1:A10) suma todos los valores entre las celdas de referencia, ellas incluidas). - La coma (","), o punto y coma (";") dependiendo de las definiciones del sistema, que es el operador de unión. Este operador combina referencias múltiples en una referencia (por ejemplo SUMA(A1,C1,D1). - El espacio que es el operador de intersección y genera una referencia a celdas comunes a dos rangos. Este último es el menos conocido, y utilizado, de los operadores de Excel. Supongamos esta tabla

La fórmula en la celda A5 es "=B1:B3 A2:C2". La intersección entre ambos rangos, es decir la celda común a ambos, es B2 cuyo valor aparece en la celda A5.

Veamos ahora una aplicación más o menos práctica. Supongamos esta tabla de cotizaciones del peso argentino



Con las funciones INDICE y COINCIDIR podemos construir una tabla de cotizaciones cruzadas



En la celda B9 ponemos la fórmula

=INDICE(cambio,COINCIDIR($A9,monedas,0))/INDICE(cambio,COINCIDIR(B$8,monedas,0))

Que luego copiamos al resto de la tabla. Como pueden ver hemos definidos los distintos rangos como nombres:

cambio =Hoja1!$B$2:$B$5
monedas =Hoja1!$A$2:$A$5

Como se puede ver en la tabla, la relación entre el euro y la libra esterlina aparece en la intersección de los rangos C9:C12 y B11:E11. Es decir, la fórmula

=(C9:C12 B11:E11)

da como resultado 1.4759, la cotización del euro respecto a la libra. El operador de esta fórmula es el espacio entre los dos rangos.

Ahora podemos crear un modelo dinámico que nos ayude a calcular las tasas de cambio entre las distintas monedas. Para ello definimos nombres que contengan los rangos "verticales" y los rangos "horizontales" de cada moneda

Dolar_H =Hoja1!$B$9:$E$9
Dolar_V =Hoja1!$B$9:$B$12
Euro_H =Hoja1!$B$10:$E$10
Euro_V =Hoja1!$C$9:$C$12
Libra_Esterlina_H =Hoja1!$B$11:$E$11
Libra_Esterlina_V =Hoja1!$D$9:$D$12
Real_H =Hoja1!$B$12:$E$12
Real_V =Hoja1!$E$9:$E$12

Creamos esta tabla, donde en las celdas B16 y B17 ponemos una lista desplegable usando Validación de Datos—Lista—monedas






En la celda B18 escribimos esta fórmula:

=INDIRECTO(B16&"_V") INDIRECTO(B17&"_H")

INDIRECTO toma la cadena de texto formada por la moneda escogida a la que le agregamos _V y _H para generar el nombre del rango. Ambas funciones INDIRECTO están separadas por un espacio que funciona como operador de intersección. El resultado es la celda común a los dos rangos definidos por las funciones INDIRECTO.

Ahora podemos agregar una funcionalidad más a nuestro modelo, usando macros de tipo eventos, tal como hicimos en la nota de ayer sobre como ingresar fechas con separadores

.
En este caso queremos que cuando seleccionamos una tasa de cambio en la tabla, esta reciba un fondo de color celeste y los nombres de las monedas (los encabezamientos de la columna y la fila) un fondo de color gris.



Para esto usamos un evento Worksheet_SelectionChange. Abrimos el editor de Vba (Alt+F11). Seleccionamos la Hoja1 (Sheet1) y en el área de código escribimos esta rutina:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngTabCurr As Range


Set rngTabCurr = Range("A8:E12")

If Union(Target, rngTabCurr).Address = rngTabCurr.Address Then
Application.EnableEvents = False

rngTabCurr.Interior.ColorIndex = xlNone

Range(Target.Address).Interior.ColorIndex = 20
Cells(Target.Row, 1).Interior.ColorIndex = 15
Cells(8, Target.Column).Interior.ColorIndex = 15

Application.EnableEvents = True
End If


End Sub


Esta técnica puede ser muy útil en tablas de cierto tamaño.





Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Excel

Importar datos de la Web a Excel

Estoy preparando una nota para mi blog sobre gráficos y presentación de datos. La idea es mostrar un gráfico que refleje el ranking de los jugadores de tenis, tal como lo muestran en las transmisiones de torneo de Masters de Shanghai (espantosa derrota de Nalbandian a manos de Blake ).

Estaba por empezar a tipear manualmente los nombres de los primeros diez clasificados del ranking, cuando me acordé que Excel permite importar datos de tablas de la Web fácilmente.

Supongo que muchos de mis lectores conocen esta funcionalidad, pero para aquellos que no aquí un pequeño ejemplo.

Encontré el ranking de la ATP en la página del torneo de Masters.




Para importar los datos de esta tabla (o cualquier otra que Excel reconozca en el sitio), hacemos lo siguiente:

1 – Abrimos la página donde se encuentra la tabla y copiamos el URL al clipboard.

2 – En una hoja de Excel abrimos el menú Datos—Obtener Datos Externos—Nueva Consulta Web



3 – En la ventanilla dirección pegar el URL de la página que contiene la tabla de datos



4 – Navegar en la ventana hasta encontrar la tabla. Excel marca las tablas que reconoce con una flecha sobre un fondo amarillo



5 – Hacer clic sobre la flecha para marcar la tabla. Es posible marcar más de una tabla. Después de marcar la tabla, apretar Importar



Señalamos dónde queremos ubicar los datos y apretamos Aceptar

Excel crea un vínculo con la tabla de origen, de manera que podemos renovar los datos sin necesidad de abrir la página.




A veces conviene crear referencias a los datos en otra hoja, para poder controlar mejor el formato. En nuestro ejemplo, Excel ha dejado dos columnas vacías entre cada columna importada. Si eliminamos las columnas vacías, al renovar los datos volverán a aparecer.



Categorías: Manejo de Datos_

Technorati Tags: Excel

Ingresar fechas en Excel sin separadores

A veces tenemos que entrar manualmente una larga lista de fechas en Excel. Para que Excel reconozca las entradas como fechas, tenemos que usar los separadores de fechas aceptados por Excel ("/", "-" o "." dependiendo de las definiciones del sistema).
Cómo hacemos para escribir fechas sin usar separadores, lo que nos permite agilizar el trabajo?

La idea es que entramos el número 18112006 en una celda y que Excel lo convierta en la fecha 18/11/2006 (día/mes/año).

Veamos las distintas posibilidades.

1 – Usando fórmulas

En este tipo de solución entramos el número en una celda y lo transformamos en fecha en una celda contigua.




En esta tabla, entramos los números en la columna A. En la columna B escribimos la fórmula
=SI(LARGO(A2)=8,FECHA(DERECHA(A2,4),EXTRAE(A2,3,2),IZQUIERDA(A2,2)),FECHA(DERECHA(A2,4),EXTRAE(A2,2,2),IZQUIERDA(A2,1)))

La fórmula básica es FECHA(DERECHA(A2,4),EXTRAE(A2,2,2),IZQUIERDA(A2,1))

Pero dado que el número del cual partimos puede tener 7 u 8 cifras, usamos la función SI.

La ventaja de este método es que los resultados de la fórmula son interpretados por Excel como fechas. Esto nos permite ordenar la lista en orden descendiente o ascendiente de acuerdo a las fechas.
La desventaja es que debemos agregar una columna a nuestro modelo.
También conviene notar que luego de las primeras cuatro entradas, Excel copia la fórmula en la columna B automáticamente.
Otra posibilidad es utilizar la funcionalidad Formulario del menú Datos, después de haber escrito la primer línea



Al usar Formulario Excel no sólo copiará las fórmulas, sino también los formatos.

2 – Usando formato personalizado

En el menú de formato de celdas elegimos la opción Personalizada. En la ventanilla Tipo anotamos lo siguiente:

##\/##\/####




El símbolo "\" lo usamos para forzar a Excel a exhibir "/" como separador en el formato.




Como pueden ver, en la barra de fórmulas aparece el número tal como lo entramos, 21102006, pero Excel lo exhibe en la celda de acuerdo al formato personalizado.

La ventaja de este método es que no hay necesidad de agregar una columna auxiliar a nuestro modelo.
La desventaja es que no podemos ordenar nuestra lista en orden creciente (o decreciente) de acuerdo a la fecha, ya que Excel no convierte nuestras entradas en fechas, como en el método anterior.

3 – Usando Eventos (macros).

Hasta ahora he tocado sólo tangencialmente el tema de las macros en este blog. Pero si queremos un método que reúna las ventajas de los dos anteriores, tenemos que echar mano a las macros (Vba).
Eventos son un tipo especial de macros que, como su nombre lo indica, entran en acción cuando sucede algún evento en el objeto al que se refieren.
En nuestro caso usaremos el evento Worksheet_Change ligado a la hoja de cálculos específica (que hemos llamado Evento).
Para acceder a este módulo apuntamos a la pestaña con el nombre de la hoja y con un clic del botón derecho del mouse abrimos el menú, donde seleccionamos Ver Código.
Otra posibilidad es abrir el editor de Vba (Alt+F11) y activar la hoja correspondiente en el panel Vba Project:



En el módulo de VBa copiamos esta rutina


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range

Set rngData = Range("A2:A65536")

On Error Resume Next

If Union(Target, rngData).Address = rngData.Address Then

Application.EnableEvents = False

Target.ClearFormats

Select Case Len(Target)
Case 8
Target = DateSerial(Right(Target, 4), Mid(Target, 3, 2), Left(Target, 2))
Case 7
Target = DateSerial(Right(Target, 4), Mid(Target, 2, 2), Left(Target, 1))
Case Else
MsgBox "Entrada Incorrecta"
End Select

Application.EnableEvents = True

End If


On Error GoTo 0

End Sub


Este rutina hace que cada vez que escribimos algo en una celda del rango A2:A65536 (dejamos la celda A1 disponible para el encabezamiento de la columna), Excel evalúe si es un número de 7 u 8 cifras. De serlo, lo convierte en fecha tomando los últimas cuatro cifras par el año, las dos del medio para el mes y la primera o las dos primeras para el día.
La ventaja de este método es que usamos sólo una columna y que tenemos un control cuando ingresamos un número incorrecto.
La desventaja es que se requiere ciertos conocimientos de macros (Vba) para implementarlo.




Categorías: Varios_

Technorati Tags: Excel