Búsqueda de ayuda de Excel

jueves, 22 de enero de 2009

Tipos de Datos en Excel. Parte 1

Tipos de datos en Excel

Sigamos con la serie de artículos sobre los fundamentos de Excel. Ya vimos algunos artículos para uno de los principales usos de Excel: realizar cálculos. Ahora revisemos otra aplicación esencial de Excel, que es almacenar, organizar y manipular datos. En el artículo no cubriremos todos estos aspectos, solamente lo fundamental: ¿Qué son los Datos en Excel?

En principio pareciera un tema sencillo, pero no lo es tanto, he visto usuarios que llevan años utilizando Excel, pero que por desconocer estos principios, piensan que Excel se "comporta" raro o "hace" cosas raras.

En una celda de Excel puede haber 4 tipos de datos
  • Texto
  • Número
  • Valor Lógico
  • Fórmula
Si las celdas donde se introducen datos no tienen aplicado ningún formato de alineación, Excel aplica la siguiente alineación predeterminada:

  • Texto: Izquierda
  • Número: Derecha
  • Valor Lógico: Centro
  • Fórmula: Depende del tipo de dato del resultado.
Figura 1
Nota: Las fechas y horas son datos numéricos y dedicaremos un artículo posteriormente al tema.

Es buena práctica que en listas de datos, no se apliquen formatos de alineación; aún cuando no los tienen, podemos a simple vista saber el tipo de dato del que se trata

Al introducir algún dato en la hoja de cálculo, Excel trata de interpretar qué tipo de dato es. Algunas veces no considera lo que deseamos.

Al introducir en una celda
001
Excel lo interpreta como un dato numérico y lo alinea a la derecha y muestra:
1

Figura 2  Figura 3


Al introducir en una celda
1/65
Excel lo interpreta como una fecha (dato numérico) y lo alinea a la derecha y muestra:
Ene-65

Figura 4  Figura 5

Excel también cambió el formato, así que si borramos el contenido (Ene-65) de la celda e introducimos 5
Excel muestra:
Ene-00
Es necesario borrar el formato con Edición > Borrar > Formatos (Edit > Clear >Formats) para que la celda muestre
5

Al introducir en una celda
falso (minúsculas)
Excel lo interpreta como un dato lógico y lo alinea al centro y muestra:
FALSO (mayúsculas)

Figura 6  Figura 7

Al introducir en una celda
- Texto
Excel lo interpreta como una fórmula y muestra
#¿NOMBRE?

Figura 8  Figura 9

Este error se debe a que cualquier texto en una celda cuyo primer carácter contenga los signos -, +, =, Excel lo interpreta como fórmula, y al no reconocer la palabra "Texto", se presenta el error.

Una manera de evitar que Excel interprete el texto como otro tipo de dato, es utilizar como primer carácter el apóstrofe (')
Al introducir en una celda
'001
Excel lo interpreta como texto y muestra
001

Figura 10  Figura 11

En la celda no se muestra el apostrofe (').

No es práctico introducir el apóstrofe en cada una de las celdas de texto cuando se introduce mucha información en una hoja de trabajo.

Una forma rápida de evitar capturar el apóstrofe es dando a las celdas el formato de texto.

Seleccione el rango de celdas en las cuales piensa introducir texto y con el menú
Formato > Celdas (Format > Cells) aparece una ventana de diálogo, en la pestaña Número seleccionar de la lista la opción Texto

Figura 12

Utilizando este método, todo lo que se introduzca en ese rango se interpretará como dato de texto y lo alineará a la izquierda.

Al introducir en una celda
=A1
Excel lo interpreta como texto y muestra
=A1

Figura 13  Figura 14

Es necesario cambiar el formato de Texto por General, y volver a introducir la fórmula para que Excel lo interprete como fórmula.

Figura 15


Este tema aún no está completo, así que espera próximamente la continuación.

No olvides dejar comentarios sobre los artículos que aquí se presentan, ya que es retroalimentación necesaria para mejorar este sitio.

martes, 13 de enero de 2009

Referencias en Excel. Parte 3

No pensé que el tema de las Referencias llevaría tanto tiempo en cubrir, pero si no se comprenden bien lo que las referencias son, difícilmente te avanzará en el conocimiento de Excel. Si no has leído las dos primeras partes de este artículo, te recomiendo que lo hagas, en la Parte 1 vimos en qué consiste una Referencia y en la Parte 2 cómo las referencias se ven afectadas por varios comandos.

Algo muy importante en relación con las fórmulas, es la capacidad de copiar esas fórmulas a otras celdas, de esa manera, sólo nos ocupamos de editar una sola fórmula y al pegarla en otras celdas, tendremos tantas fórmulas como necesitemos sin necesidad de escribirlas una por una: tarea titánica cuando hablamos de miles de fórmulas! Pero, ¿Qué pasa con las referencias cuando copiamos y pegamos las fórmulas a otras celdas?

En este ejemplo tenemos en la celda B2 la fórmula:
=A2
Figura 1
 
Al copiar la fórmula de B2 y pegarla en el rango B3:B11 podemos ver que las referencias cambian.
En la celda B3 tenemos la fórmula
=A3.
Figura 2


Referencias Relativas

Las referencias cambian de esta manera debido a que son Referencias Relativas.

Las referencias relativas cambian de acuerdo a la posición relativa de la celda que contiene la fórmula

La fórmula en B2
=A2
Le "dice" a Excel: hacer la celda B2 igual al valor de la celda que se encuentra en una columna a la izquierda y en la misma fila, así que al pegarla en la celda B3, Excel hará referencia a la celda que está una columna a la izquierda en la misma fila.

En este ejemplo tenemos en la celda B3 la fórmula:
=A3
Figura 3
 
Al copiar la celda B3 y pegarla en la celda AD103
La fórmula ahora es:
=AC103
Una columna a la izquierda y dos filas arriba.
Figura 4


Un ejercicio que podemos hacer es una tabla de equivalencia entre Pesos Mexicanos y U.S. dólares.
Verificando el tipo de cambio el día de hoy, encontré que es de 13.7595 pesos por cada dólar:

Una opción es introducir en la celda B2 la fórmula:
=A3*13.7595
y copiarla al todo el rango.
Figura 5
 
Introducir el valor 13.7595 en la fórmula no es conveniente, ya que éste cambia constantemente, así que en la celda B1 introducimos este valor y en la celda B2 la fórmula:
=A2*B1
Figura 6
 
Al copiar la celda B2 y pegarla en B3, la fórmula es:
=A3*B2
La fórmula tiene como referencia B2 en lugar de B1, ya que es una referencia relativa.
Figura 7


¿Qué hacer para que al copiar una fórmula, la referencia no cambie?.

En estos casos se utilizan Referencias Absolutas.


Referencias Absolutas

Es una referencia a una celda o rango que no cambia, no importa a dónde es copiada.

En el ejemplo anterior, modificamos la fórmula a:
=A2*$B$1
El signo $ le dice a Excel que la referencia es absoluta.
Figura 8
 
Al copiar la celda B2 y pegarla en todo el rango
La fórmula en B3 ahora es:
=A3*$B$1
La referencia $B$1 no cambia al copiar las fórmulas.
Figura 9


Ahora bien, quiero hacer lo mismo para Euros. Verifico el tipo de cambio y encuentro que 1 Euro equivale a 18.3340 pesos mexicanos.

Se introduce este valor en la celda C1 y al copiar la fórmula de la celda B2 en la celda C2 obtenemos lo siguiente:

La fórmula es:
=B2*$B$1
Cuando en realidad necesitamos
=A2*C1
Figura 10

En la referencia $B$1, el signo $ frente a la B ($B) le "dice" a Excel: hacer la referencia de la columna absoluta; mientras que el signo $ frente al número 1 ($1) le "dice" a Excel: hacer la referencia de la fila absoluta. Para resolver esto, es necesario utilizar referencias mixtas.


Referencias Mixtas

Existen dos tipos de Referencias Mixtas:
  • Columna absoluta y fila relativa.
  • Columna relativa y fila absoluta.
En el ejemplo anterior, para la fórmula en B2
=A2*B1
Se requiere que la primera referencia de columna absoluta y fila relativa $A1.
La referencia B1 debe de ser de columna relativa y de fila fija B$1.

De esta forma, la fórmula en B2 debe de ser:
=$A1*B$1
Figura 11
 
Al copiar la celda B2 a todo el rango las fórmulas muestran las referencias correctas.
Figura 12

Para editar más fácilmente las referencias, en la barra de fórmulas, seleccione la referencia que requiere cambiar y presione F4 para alternar las combinaciones.

No olvides dejar comentarios sobre los artículos que aquí se presentan, ya que es retroalimentación necesaria para mejorar este sitio.

lunes, 5 de enero de 2009

Referencias en Excel. Parte 2


En el artículo anterior se trató sobre las referencia en Excel y como pueden referirse a cualquier celda o rango en cualquier hoja de Excel, pero ¿Qué pasa cuando se copian, cortan, borran o eliminan las celdas a las que se hacen referencia? ¿Qué pasa cuando se insertan celdas, columnas o renglones? ¿Que pasa con las referencias cuando las celdas con las fórmulas cuando estas celdas se copian o se cortan?

Veamos lo que pasa cuando las celdas o rangos de los que se hace referencia son modificados.
Estas son las modificaciones que pueden realizarse:

Borrar Celda o Rango (Clear)

Presionar la tecla Supr (Del) sólo borra el contenido de la celda o rango, tanto el formato como los comentarios permanecen intactos.

Figura 1 Para borrar, no sólo el contenido de una celda o rango, se selecciona y con el menú Edición > Borrar (Edit > Clear).

Se puede borrar:
-Todo
-Formatos
-Contenido
-Comentarios



El borrar Celdas o Rangos NO altera las fórmulas que utilizan estas celdas como referencias, solamente el resultado es recalculado.


Eliminar Celdas o Rangos (Delete)

Se pueden eliminar celdas, rangos, filas completas, columnas completas y hojas completas. Al eliminar las celdas, éstas desaparecen y son remplazadas por otras celdas. Hay que ser más cautelosos que al borrar, ya que las referencias SI se alteran.

Figura 2 Para eliminar una celda o rango se selecciona el comando Edición > Eliminar (Edit > Delete) o se utiliza el menú contextual presionando el botón derecho del ratón.


En este ejemplo, la celda B1 se eliminará mientras que la fórmula en C1 hace referencia a B1 Figura 3
 
Al aplicar el comando Eliminar mientras la celda B1 es la celda activa aparece el siguiente diálogo.
Al Eliminar celdas hay un desplazamiento de celdas para ocupar el lugar de la celdas que han sido eliminadas. Mediante este diálogo se elige qué tipo de desplazamiento se desea, o si se desea eliminar toda una fila o toda una columna.
Figura 4
 
Después de presionar el botón de Aceptar, así se ve la hoja de cálculo:
La fórmula en C1 cambió de:
=B1    a    =#¡REF!
La fórmula en C2 cambió de:
=B2    a    =B1
Figura 5


Esto es debido a que la celda B1 ha sido eliminada y la referencia en la fórmula en C1 ya no es válida y es remplazada por el error #REF
La celda B2 se desplazó hacia arriba para ocupar el lugar de la celda eliminada B1, por lo que la fórmula en la celda C2 cambió para mostrar la nueva referencia.


Insertar Celdas o Rangos (Insert)

Se pueden insertar celdas, rangos, filas completas, columnas completas y hojas completas. Al insertar las celdas, nuevas celdas aparecen y las otras celdas son desplazadas. Igual que Eliminar, hay que ser cautelosos al Insertar ya que las referencias SI se alteran.

Figura 6 Para insertar una celda o rango se selecciona el comando Insertar > Celdas (Insert > Cells) o se utiliza el menú contextual presionando el botón derecho del ratón.


En este ejemplo, se insertará una celda en la celda B1. Figura 3
 
Al aplicar el comando Insertar mientras la celda B1 es la celda activa aparece el siguiente diálogo.
Al insertar celdas hay un desplazamiento de las celdas que ocupan el rango donde se insertarán nuevas celdas. Mediante este diálogo se elige hacia dónde se desea el desplazamiento, o si se desea insertar toda una fila o toda una columna.
Figura 7
 
Después de presionar el botón de Aceptar, así se ve la hoja de cálculo:
La fórmula en C1 cambió de:
=B1    a    =B2
La fórmula en C2 cambió de:
=B2    a    =B3
Figura 8


Esto es debido a que la celda B1 ha sido insertada y la celdas B1 y B2 han sido desplazadas hacia abajo y las fórmulas en las celdas C1 y C2 muestran ahora las nuevas referencias.


Cortar y Pegar (Cut & Paste)

Cortar y pegar una celda o un rango significa que esa celda o rango va a moverse de posición, desplazando a las celdas que originalmente estaban en esa nueva posición. La operación de cortar y pegar SI altera las referencias cuando existen fórmulas que utilicen como referencia a las celdas involucradas.

Figura 9 Este es un comando de dos pasos: Cortar y después Pegar. Para cortar una celda o rango se selecciona el comando usando el menú Edición > Cortar ( Edit > Cut) o se utiliza el menú contextual presionando el botón derecho del ratón. Una vez que se aplicó el comando Cortar, se ejecuta el comando Pegar usando el menú Edición > Pegar (Edit > Paste)


En este ejemplo, cortará la celda B1 y se pegará en la celda B2. Figura 3
 
Después de Cortar y Pegar, así se ve la hoja de cálculo:
La fórmula en C1 cambió de:
=B1    a    =B2
La fórmula en C2 cambió de:
=B2    a    =#¡REF!
Figura 10


Esto es debido a que la celda B1 se movió hacia la celda B2 y la celda C1 muestra ese movimiento.
La celda B2 fue eliminada y por eso la celda C2 muestra error al desaparecer la celda de la que se hacía referencia.


Copiar y Pegar (Copy & Paste)

Se aplica de la misma manera que Cortar y Pegar, pero la celda origen no sufre alteraciones y la celda destino no es desplazada.

Con los comandos Copiar y Pegar ninguna celda se mueve de lugar o es desplazada y por lo tanto NO altera las fórmulas que utilizan estas celdas como referencias, solamente el resultado es recalculado.


Conclusión

Es necesario tomar precauciones para no modificar accidentalmente las referencias en las fórmulas. Frecuentemente me piden ayuda con archivos en los que aparece el error #REF en muchas de las celdas donde antes se realizaban los cálculos.

En términos generales, es mejor utilizar los comandos de copiar y pegar y borrar en lugar de cortar, insertar y eliminar, sobre todo si no se conoce a fondo el archivo con el que se está trabajando.

Aún resta la tercera parte de esta serie, donde se analizarán las referencias absolutas, relativas y mixtas.

No olvides dejar comentarios sobre los artículos que aquí se presentan, ya que es retroalimentación necesaria para mejorar este sitio.

sábado, 27 de diciembre de 2008

Referencias en Excel. Parte 1

En el artículo anterior se mencionó a las referencias como uno del los elementos de las fórmulas y las funciones. Si se requiere hacer cálculos complejos en Excel, es necesario dominar lo relacionado a las referencias.

Antes de entrar en materia, definamos brevemente algunos términos, para quien no esté familiarizado con ellos.
 

Hoja de Cálculo (Spreadsheets):

Es un programa computacional que permite manipular datos; estos datos se presentan en forma de tablas. La hoja de cálculo permite principalmente realizar cálculos complejos, hacer gráficas y manejar listas de datos. Existen varias hojas de cálculo en el mercado, las más utilizadas son:

  • Excel: Integrada en Microsoft Office. Es el objeto principal de este blog.
  • Calc: Integrada en OpenOffice. Es una aplicación gratuita. Personalmente no la he utilizado; es similar a Excel y mucho de lo expuesto aquí, aplica también en Calc.

Libro de trabajo (Workbook):

Es un archivo creado por Excel. Los libros de trabajo de Excel tienen la extensión .xls

Figura 1

El nombre del libro activo se puede observar en la barra de título.
 

Hojas de trabajo (Worksheet):

Cada libro de trabajo está formado por varias hojas de trabajo. Cada hoja de trabajo tiene su nombre, que podemos ver en la parte inferior en forma de pestañas. Podemos cambiar de hoja, sólo dando click con el ratón a las pestañas.

Figura 2
 

Fila, Columna, Celda y Rango (Row, Column, Cell y Range):

Las hojas de trabajo están formadas por tablas formadas por filas y columnas. A la intersección de una fila con una columna se le llama Celda. Rango es un grupo de Celdas.

  • Las columnas se nombra por medio de letras: A, B, C…
  • Las filas se nombran por medio de números 1, 2, 3.…
  • Las celdas se nombran por el nombre de la columna seguida por el número de la fila A1, A2, B1, B2.….
  • Los rangos se nombran por el nombre de la celda que está en la esquina superior izquierda y la celda inferior derecha, ambas separadas por dos puntos ":". El rango A1:B3 incluye las celdas A1, A2, A3, B1, B2 y B3.

Figura 3
 

Referencias:

Una referencia es una celda o un rango de celdas. Es lo que le "dice" a Excel dónde se localizan los datos que debe utilizar para los cálculos o para cualquier otra acción.

Figura 4 Si en la celda B1 se introduce la fórmula:
=A1*2
Excel tomará el valor (constante o resultado de una fórmula) que se encuentra en la intersección de la columna A y el fila 1 (referencia A1) y lo multiplicará por 2 y mostrará el resultado en la celda B1.

Al cambiar el valor en la celda A1, Excel realiza nuevamente el cálculo de la fórmula en B1.

 Si en la celda B5 se introduce la fórmula:
=SUMA(A1:A4) SUM(A1:A4)
Excel calculará la suma del valor en las celdas A1, A2, A3 y A4.
En la referencia A1:B3, se incluyen las celdas A1, A2, A3, B1,B2 y B3

Figura 4

La referencia puede pertenecer a la misma hoja de trabajo

 
Figura 5

La referencia puede pertenecer a otra hoja de trabajo en el mismo libro de trabajo

La referencia puede pertenecer a otro libro de trabajo.
A esto se le conoce como referencias externas o vínculos externos (external links).
Al libro que contiene los vínculos externos se le llama libro destino y al libro que contiene los datos originales se le llama libro origen.
No es requisito que el libro origen esté abierto para realizar estos enlaces.

Figura 6

Si el libro externo está abierto, la referencia sólo muestra el nombre del libro.

 
Figura 7

Si el libro externo está cerrado, la referencia muestra la ruta completa.

 Cuando ambos libros están abiertos, Excel actualiza los vínculos de forma automática.
Al cambiar los valores en el libro origen, Excel ejecuta los cálculos para actualizar el libro destino.

Si ambos libros están cerrados y se abre el libro destino, Excel pregunta si se desean actualizar los vínculos mediante el siguiente diálogo:

Figura 8

Al seleccionar No Actualizar, Excel conservará los últimos datos y no tratará de buscar el libro origen para actualizar datos.

Al seleccionar Actualizar, Excel tratará de localizar el libro origen para actualizar los datos. Si el libro origen todavía existe y la referencia es válida, cualquier cambio de éste se reflejará en el libro destino.

Si el libro origen no se encuentra o las referencias no son válidas, Excel mostrará el siguiente diálogo:

Figura 9

Al seleccionar Continuar, Excel abrirá el libro de trabajo sin actualizar los vínculos externos.

Al seleccionar Modificar Vínculos, Excel mostrará el siguiente diálogo, que también puede ser invocado con el menú Editar > Vínculos (Edit > Links)

Figura 10

  • Actualizar Valores: Excel tratará de localizar el libro origen para actualizar datos.
  • Cambiar Origen: Permite seleccionar otro archivo como Libro origen. Esta opción es útil cuando el libro ha sido movido a otro directorio o cuando se requiere usar un libro diferente.
  • Abrir Origen: Abrirá el libro origen.
  • Romper vínculo: Remplaza todas las referencias con vínculos externos por sus valores actuales:
    =‘C:\[Libro1.xls]Hoja1’!$A$1*2
    Será remplazado por
    =5*2 (donde 5 era el último valor de la celda A1 en la hoja Hoja1 del libro Libro1.xls)
  • Comprobar estado: Verifica si los libros origen existen y si las referencias son válidas.

Este diálogo también permite configurar los vínculos externos al seleccionar, si se desea, que la actualización de los vínculos sea de forma automática, es decir, cada vez que se abra el libro destino o de forma manual, utilizando el actual diálogo mediante el menú Editar > Vínculos (Edit > Links).

En la próxima entrega trataré sobre las referencias absolutas, las relativas y las mixtas, completando así Referencias en Excel.

Deja tus comentarios acerca del artículo. Hazme saber si la explicación ha sido clara o tus sugerencias para enriquecerlo.