Listas personalizadas en Excel. Añadir nuestras propias listas.

Listas personalizadas en Excel. Crear, ordenar, modificar listas personalizadas Excel.

Escrito por Iván Antona en . Publicado en Excel Básico, Niveles Excel.

Qué son las listas personalizadas en Excel

Las listas personalizadas en excel son conjuntos de datos que siguen algún tipo de orden. Así por ejemplo las listas personalizadas más comúnmente utilizadas son los meses del año, los días de la semana, los puntos cardinales, calificaciones de estudiantes, etc…

Excel trae algunas listas personalizadas por defecto incluidas. En la imagen se muestran algunas de ellas.

Listas personalizadas en Excel. Añadir nuestras propias listas.

Crear listas personalizadas en Excel

Podemos crear nuestras listas personalizadas para trabajar con ellas en Excel. Desde el menú “File” o “Archivo” en la versión de Excel 2010 y haciendo clic sobre “Options” u “Opciones” y a continuación en “Advanced” o “Avanzado”. Debemos localizar “Edit Custom Lists…” o “Editar listas personalizadas”.

Una vez dentro del asistente y haciendo clic sobre “Add” o “Añadir” se puede introducir los elementos de una lista personaliza. Cada vez que incluimos un elemento de la lista pulsaremos la tecla entrar para que nos coloque el elemento en una nueva línea. Así por ejemplo añadiremos los puntos cardinales para mostrar el procedimiento.

Listas personalizadas en Excel. Añadir nuestras propias listas.

Ordenar mediante listas personalizadas en Excel

Las listas personalizadas pueden ser de utilidad para ordenar información que queremos representar en un gráfico.

Listas personalizadas en Excel. Añadir nuestras propias listas.

Si nos fijamos en el gráfico de la imagen podemos observar que los datos aparecen representados pero los meses están ordenados alfabéticamente. Este tipo de ordenación no nos da una visión sencilla de la realidad. Para nuestro ejemplo habría sido mucho más visual representar en primer lugar Enero, después Febrero, y así sucesivamente. De esta manera cuando observáramos el gráfico tendríamos una visión más sencilla sobre la evolución de las ventas realizadas por Alfredo a lo largo del año.

Para poder ordenar los datos “mensualmente” vamos a hacer uso de una lista personalizada que ya existe en nuestra hoja de cálculo. Si no existiera tendríamos que crearla como se indica en los apartados anteriores.

El primer paso es seleccionar la lista que deseamos ordenar. Y después tendremos que utilizar la herramienta de ordenación que se encuentra en la cinta de opciones.

Listas personalizadas en Excel. Añadir nuestras propias listas.

Una vez que accedemos al asistente de ordenación debemos elegir una ordenación personalizada. Una vez que hemos elegido el campo (etiqueta de columna o encabezado) por el que deseamos ordenar la lista elegiremos “Lista personalizada” o “Custom List”

Listas personalizadas en Excel. Añadir nuestras propias listas.

Y una vez dentro de las listas personalizadas podremos elegir nuestra lista de meses. También podríamos crear desde este punto la lista si es que no existiese.

Listas personalizadas en Excel. Añadir nuestras propias listas.

Elegiremos la lista que deseamos utilizar y haciendo clic en OK podremos comprobar como nuestros datos se han ordenado siguiendo la estructura de la lista personalizada. El resultado final nos ofrece una visión sobre las ventas de nuestro vendedor “Alfredo” mucho más fácil de interpretar ya que los datos del gráfico se muestran ordenados cronológicamente.

Listas personalizadas en Excel. Añadir nuestras propias listas.

La herramienta rellenar de Excel permite que con un simple clic las celdas adyacentes se rellenen con los datos correlativos.

Rellenar datos en Excel

Escrito por Iván Antona en . Publicado en Excel Básico, Niveles Excel.

Cómo Rellenar datos en Excel

Excel presenta algunas funcionalidades que conviene conocer para hacer nuestro trabajo diario mucho mas productivo. La herramienta «Rellenar datos en Excel» o herramienta de relleno automático permite que con un simple clic o bien con un clic y arrastrando el ratón las celdas adyacentes  se rellenen con los datos correlativos. La mejor forma de entender este concepto es mediante un ejemplo.

Rellenar datos automáticamente de tipo número

Partiremos de dos celdas que contienen valores numéricos como se muestra en la imagen.

La herramienta rellenar de Excel permite que con un simple clic las celdas adyacentes se rellenen con los datos correlativos.
Seguidamente nos situamos en el vértice inferior derecho de la selección hasta conseguir que aparezca una cruz negra. En ese momento haremos clic izquierdo del ratón y sin soltar arrastramos tantas casillas como necesitemos. En este ejemplo llegaremos a 10.Finalmente soltamos el botón del ratón y las celdas se auto rellenan con los datos esperados.

La herramienta rellenar de Excel permite que con un simple clic las celdas adyacentes se rellenen con los datos correlativos.

Rellenar datos automáticamente de tipo Fecha

Lo mismo ocurre con las fechas. Si tenemos en una celda cualquiera dos fechas por ejemplo el 03-05-2016 y el 04-05-2016 y seleccionamos ambas, al poner el puntero del ratón encima del borde inferior derecho se activa la opción de relleno. Arrastrando podremos de forma automatizada cargar los datos de las fechas siguientes en las celdas contiguas.

La herramienta rellenar de Excel permite que con un simple clic las celdas adyacentes se rellenen con los datos correlativos.

 

Rellenar datos automáticamente de tipo texto

Igualmente se puede utilizar esta herramienta para rellenar datos que siguen una secuencia lógica. La secuencia «lunes, martes» se rellenara automáticamente con el resto de días de la semana. Lo mismo ocurre con meses como «mayo, junio…»

Mas adelante veremos como crear y gestionar listas personalizadas para poder añadir nuevas secuencias, por ejemplo, los puntos cardinales (Norte, Este, Sur, Oeste) o quizás las tareas repetitivas de un proyecto que gestionamos.

Las listas personalizadas también resultarán muy útiles para aplicar un orden determinado en gráficos.

«Los que emplean mal el tiempo son los primeros en quejarse de su brevedad.»  Jean de la Bruyere

En cualquier hoja de Excel con la que trabajemos haremos referencia constantemente a tres tipos de datos, constante, variable y campo calculado

Constante, variable, campo calculado en excel.

Escrito por Iván Antona en . Publicado en Excel Básico, Niveles Excel.

Qué es una constante variable en Excel

En cualquier hoja de Excel con la que trabajemos haremos referencia constantemente a tres tipos de datos: constante, variable y campo calculado. Estos datos normalmente estarán contenidos en celdas. Para acceder a esos contenidos haremos uso de las referencias relativa, referencia absoluta o referencia mixta.

Constante, variable, campo calculado en excel.

– Constantes en Excel: Aquellos valores que no cambian a lo largo de la vida de nuestra hoja de calculo. Por ejemplo el IVA

– Variables en Excel: Son valores que a diferencia de las constantes si que cambian. Por ejemplo el valor de las ventas que realiza un comercial mensualmente.

– Campos calculados en Excel: Decimos que un campo es calculado cuando es resultado de una operación previa. Por ejemplo el salario anual de una persona que cobra 1200€ al mes en 14 pagas. Diremos que el campo calculado será igual a 1200€ x 14. Un campo calculado también se define como la celda o rango resultado o salida de una función de Excel.

Ejemplos Constante, variable, campo calculado en Excel.

En primer lugar el ejemplo del IVA. Encontramos en una hoja de cálculo una serie de productos sin IVA y queremos actualizar su precio final como precio más el precio con el IVA. Las constantes están muy fuertemente relacionadas con las referencias de tipo absoluto. Es decir con el formato $A$1 que hemos obtenido presionando F4. Y es así porque el valor no cambia a lo largo de la vida útil de la hoja. Es por ello que son constantes.

En cualquier hoja de Excel con la que trabajemos haremos referencia constantemente a tres tipos de datos, constante, variable y campo calculado

Podemos entender como variable el número de unidades vendidas. Por ejemplo en la primera fila (fila 2) podemos observar que el número de unidades vendidas es 5. Este dato es un valor variable. Cambia en cada fila. Dependerá de factores no controlables. A diferencia de las constantes que no cambian este dato puede variar y no podemos fijarlo. No debemos usar referencias absolutas con este tipo de datos.

En último lugar tenemos el Total como «Campo calculado» resultado de sumar al Subtotal el importe del IVA. Diremos que un campo es calculado cuando su resultado se obtiene de realizar alguna operación con otros valores de la hoja de cálculo.

Referencia absoluta en Excel, explicación y ejemplo de uso

Referencia absoluta en excel, explicación y ejemplo de uso

Escrito por Iván Antona en . Publicado en Excel Básico, Niveles Excel.

Referencia absoluta en excel

La referencia absoluta en excel es también muy utilizada. Su principal cometido es fijar un valor constante. Por ejemplo, si hablamos de facturación el IVA en el momento de este ejemplo es un valor fijo del 21%. Si nos referimos a cálculos matemáticos por ejemplo el valor de Pi 3,14 es siempre fijo o constante, lo mismo ocurre con el número e 2,71 etc…

Mediante la referencia absoluta en excel evitamos que Excel utilice la referencia por defecto o referencia relativa. Veremos a continuación el siguiente ejemplo:

Tenemos valores de facturas sin IVA en nuestra hoja:

A2= 100€

A3= 200€

A4= 100€

Añadimos en B1 el término IVA que además consideramos para este ejemplo el 21% y así lo reflejamos en C2. El cálculo es sencillo:

=A2*C2

Referencia absoluta en excel, explicación y ejemplo de uso

Para el primer cálculo obtenemos el valor buscado, 21€, sin embargo  si copiamos el resultado y lo pegamos en las celdas B3 y B4 como hicimos en el caso de la referencia relativa veremos que no obtenemos el resultado esperado.

Referencia absoluta en Excel, explicación y ejemplo de uso

¿Qué ha ocurrido? La respuesta la conocemos, se han recalculado las operaciones con referencias relativas Es decir, en B3 tenemos el resultado de multiplicar A3 por C3 en consecuencia “cero” y en B4 tenemos el resultado de multiplicar A4 por C4, de nuevo “cero”.

Para solucionarlo tendríamos que haber realizado estos cálculos:

=A2*C2

=A3*C2

=A4*C2

 

O lo que es lo mismo fijar la celda C2 para que cuando copiamos nuestro cálculo a otra celda se mantenga de forma permanente el C2 y no avance.

Para lograr este objetivo cuando escribamos nuestro cálculo, una vez que hemos hecho clic en la celda C2, tendremos que pulsar la tecla F4 de nuestro teclado. De esta manera veremos cómo se coloca un símbolo de $ delante de cada una de las coordenadas de nuestra celda.

Referencia absoluta en Excel, explicación y ejemplo de uso

 

=A2*$C$2

De esta manera le decimos a Excel que cuando copiemos nuestros cálculos en el cálculo. el valor que se encuentra precedido en sus coordenadas por el símbolo “$” permanecerá constante

Volviendo al ejemplo del IVA, modificamos el cálculo en la celda B2 como se muestra en la siguiente imagen:

Ahora sí podemos copiar el contenido de B2 y al pegarlo sobre B3 y B4 el resultado será el deseado. Hemos fijado el valor del IVA o celda C2 como constante. Si el valor de C2 lo modificamos automáticamente los datos se actualizarán en toda la hoja. Esta es la forma correcta o buena práctica para trabajar con valores constantes.

 

 

La referencia relativa es la más utilizada en Excel. Además si no hacemos ningún cambio en el tipo de referencia será la que se utilice por defecto.

Referencia relativa en Excel. Ejemplo sencillo de uso.

Escrito por Iván Antona en . Publicado en Excel Básico, Niveles Excel.

La referencia relativa es la más utilizada en Excel. Además si no hacemos ningún cambio en el tipo de referencia será la que se utilice por defecto. Este tipo de referencia nos vuelve a calcular de forma automática los orígenes de datos de los cálculos siempre que interprete una sucesión.

La referencia relativa

Vamos a explicarlo de una forma más sencilla. Por ejemplo en la celda A1 tenemos el número 1 y en la celda A2 tenemos el número 2 y así hasta la celda A5 donde tendremos el valor 5. Por otro lado en las celdas que van desde B1 y hasta B5 pondremos el valor fijo 2. Finalmente en la columna C en la primera celda C1 escribiremos un cálculo sencillo. La multiplicación del valor contenido en la celda A1 multiplicado por el valor contenido en la celda B1 que en Excel se escribe de la siguiente manera:

= A1*B1

La referencia relativa es la más utilizada en Excel. Además si no hacemos ningún cambio en el tipo de referencia será la que se utilice por defecto.

A priori conocemos el resultado  que para este ejemplo sería 2. Pero, ¿qué ocurre si copiamos el contenido de la celda C1 y lo pegamos en C2? ¿El resultado seguirá siendo 2? La respuesta es NO. El resultado se actualiza por arte de magia. Si consultamos el cálculo existente en la celda C2 (haciendo clic en la celda y presionando F2) veremos que el resultado responde al cálculo:

=A2*B2

Referencia relativa en excel.

Lo que ha ocurrido es que Excel ha actualizado el cálculo de forma relativa. La hoja de cálculo ha analizado los datos que se encuentran a continuación, ha comprobado el contenido de las celdas adyacentes y ha detectado que hay información. Al existir datos de forma automática avanza para evitarnos trabajo. Se ha utilizado la referencia relativa. Si arrastramos hacia abajo o copiamos el contenido de la celda C1 en el resto de celdas desde C2 a C5 automáticamente se actualizará el cálculo.

C1= A1*B1

C2= A2*B2

…

C5= A5*B5

Este tipo de referencia es muy útil y nos ahora mucho tiempo y trabajo. Pero hay que tener cuidado por que en algunas ocasiones no necesitamos que los datos de nuestros cálculos avancen. Es entonces cuando toman sentido otros tipos de referencia utilizados en Excel, como puede ser la referencia absoluta o la referencia relativa.