Listas personalizadas en Excel: Aprende a usarlas y crearlas y trabajaras más eficazmente

Probablemente ya sabes que si una celda contiene “lunes” o cualquier otro día de la semana, y tiras con el cursor de la esquina inferior derecha de la celda, obtendrás el otro días de la semana, en la celda donde sueltes el cursor. A este cursor con forma de marco se le conoce como "controlador de relleno". 

Si continúa más allá de siete días, la lista se reinicializa continuamente. Lo mismo pasa con los meses, y con todas las listas personalizadas que incluyamos en Excel. Esta función que le permitirá ahorrar tiempo completa automáticamente las secuencias de números y palabras más utilizadas para que no tenga que hacerlo usted.

1. Listas integradas

Office Excel proporciona las siguientes listas integradas día de la semana y mes del año.

LISTAS INTEGRADAS
Do, Lun, Mar, Mié, Jue, Vie, Sáb
Domingo, Lunes, Martes, Miércoles, Jueves, Viernes, Sábado
Ene, Feb, Mar, Abr, May, Jun, Jul, Ago, Sep, Oct, Nov, Dic
Enero, Febrero, Marzo, Abril, Mayo, Junio, Julio, Agosto, Septiembre, Octubre, Noviembre, Diciembre

 NOTA   No es posible modificar ni eliminar una lista integrada.

2. Varias listas a la vez

Una peculiaridad de esta funcionalidad es que podemos trabajar y extender dos o mas listas a la vez. Así, en el ejemplo de abajo, si tomamos C3: F3, y tiramos hacia abajo el cursor, obtendremos cuatro listas rellenas. También puedes tirar del cursor hacia arriba, a la derecha, a la izquierda.
Excel, ve cual es el valor de la/s celda/s activa , el patrón que siguen, el formato y se encarga de replicarlo.

3. Listas personalizadas

Pero lo mas práctico es que el 80% del tiempo que dedicamos en Excel a crear tablas y cuadrantes, se repiten los mismos nombres, conceptos, artículos, etc. ¿Cómo puedo hacer una lista con esos nombres? Como lo puesto en las celdas B15:F19.

Para ello, sólo tienes que seleccionar el bloque H3:H7, y a continuación, ejecute en la línea de comandos "Excel", la opción de "Preferencias".  En la linea de "Fórmulas y listas" encontraras un icono con "Listas personalizadas", haz clic en el botón.

Sólo tienes que hacer clic en el botón "Importar" y confirme con "OK". Y voilá! Acaba de agregar a su Excel una nueva lista personalizada.

 4. Combinar listas y formatos para hacer tablas y cuadrantes

Y por último combina las listas personalizadas con celdas de valores estáticos, es decir puedes confeccionar una lista personalizada y al lado una columna con los datos que sólo se copiaran y por lo tanto se repiten. Esto te hace ganar en rapidez, aumentando la productividad de tu tiempo, evitando errores y tareas repetitivas. 

Por ejemplo en la celda J3 pon el nombre de "Pablo", en las celda K3 pon "Bruto". Teniendo activa la celda K3, arrastra lel cursos para crear una lista de "Bruto, IRPF, y Neto". añade bordes a las celdas y señala con un color la celda de Pablo.

Ahora marca el área J3:L5, que es el grupo de celdas que confecciona el patrón, y arrastra el cursor,. YA!!! .

En un momento hemos creamos un cuadrante de personas y datos. Son muchas las combinaciones que puedes realizar, coge el habito de usarlas y veras la cantidad de listas personalizadas, listas de nombres que se repiten continuamente en tu trajo, personas, oficinas, productos, materias primas, elementos, lo que tu veas.

5. El controlador de rellenado: sus opciones.

Y por último, cuando has rellenado una lista aparece un icono con las opciones del "controlador de rellenado". Prueba y ejercita con ellas para entender lo que hacen.

Como otras veces puedes el fichero Excel con el modelo que hemos utilizado en la explicación, puedes descargartelo en Flasforward.

¿Cómo utilizar las funciones de fechas en Excel? ¿Cual es el primer mes del año que comienza en jueves? ¡Excel lo sabe¡

¿Cual es el primer mes del año que comienza en jueves? ¡Excel lo sabe¡

Utilización de las funciones de fechas en Excel

De bote pronto.  Rápido, contesta: ¿cual es el primer mes de un año que comienza en un día determinado de la semana?, es decir dime ¿cual es el primer mes del próximo año 2014 que comienza en jueves?

Si nunca te has hecho esta pregunta, posiblemente podrás seguir el resto de tu vida. Pero si bien te pica la curiosidad, o estas preparando un proyecto o un evento, preguntas de esta guisa puede que te hagas.

He preparado un pequeño modelo en Excel, y que en base a dicha pregunta trabajaremos, las fórmulas matriciales, los formatos condicionales, las listas personalizadas, funciones como elegir, coincidir, índice, y el uso de formas en una Excel. ¿Quién da mas? Animo, descárgate aquí el e modelo en Flashforward, y repasa algunos temas de Excel que puedas tener oxidados. 

Explorando la pregunta.

Para saber en que día empieza un mes podemos utilizar la función DIASEM(fecha, tipo), que devuelve el número de la semana que representa una fecha concreta, y este dependerá de en que día consideras que empieza la semana, en Domingo, lunes, etc.

Así que podemos construir una tabla de donde pongamos en el eje vertical los años y en el horizontal los meses. Calculamos para cada combinación de mes y año, el número de día de la semana de del primer día, y para aclararnos, convertimos ese numero en texto, mediante la función ELEGIR (;), y para los argumentos utilizaremos las listas personalizadas de los días de la semana. Sitúate en B7 y;

• Crear la fecha con FECHA(año;mes;dia):   “=Fecha($A7;columnas()-1;1)”. El truko de esta función es utilizar columnas(), que devuelve el número de la columna de la celda en la que esta la formula, y como hemos empezado en la columna B, que es la columna #2, pues tendríamos el número del mes que deseamos, sin tener que poner en una fila con los números de los meses.
• Calcular el día de la semana, para ello utilizamos DIASEM(núm_de_serie,tipo) con la opción #2 que corresponde a la semana que empieza por lunes como día #1 y domingo como #7. “DIASEM(FECHA(…);2)” nos devolverá un número del 1 al 7.
• Poner en texto el numero de día de la semana mediante ELEGIR(núm_índice,valor1,valor2,...) : “ELEGIR(DIASEM(FECHA($A8;COLUMNA()-1;1);2);"Lun";"Mar";"Mié";"Jue";"Vie";"Sáb";"Dom")”
•  Ya lo tienes, ya esta. 

De esta forma hemos creado una tabla donde se recogen el día de la semana en que comienzan los meses de diferentes años.

¿Y esto no lo puedes poner en una formula y en una sola celda?

Pues a eso vamos, en eso esta el reto.

Pongamos en la celda G5 Año, y en la celda G6 el número de año que deseas. En la celda c4, pon el número de la semana que te interesa, empezando el lunes como 1.

En H4 escribiremos esta función:

“{=ELEGIR(+COINCIDIR(C4;DIASEM(FECHA(G4;FILA(1:12);1);2);0);"Enero";"Febrero";"Marzo";"Abril";"Mayo";"Junio";"Julio";"Agosto";"Septiembre";"Octubre";"Noviembre";"Diciembre")}” ojo es matricial y tienes que introducir la fórmula pulsando Shiht+Ctrl+Enter” .

Las “tripas” de la fórmula

·      El “core” de la fórmula FILA(1:12), generamos una variable matricial, de 12 filas por 1 columna que contiene los 12 números de mes que tiene el año, y sobre esta variable se construye toda la formula.

·      Hemos generado una matriz de 12x1, y que servirá de contenedor para las doce fechas de inicio de mes de ese año, cosa que logramos mediante FECHA(año;mes;dia), y estos resultados son transformados en el número de la semana mediante DIASEM(fecha;tipo). Así logramos tener una matriz de 12X1 elementos que contiene los números de semana correspondientes al primer día de mes de cada uno de los meses de ese año.

·      Pues si tienes una lista, una matriz, aplicamos la función COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia) y nos devolverá el número del orden que ocupa el primer elemento de la matriz en que dicho valor (día de la semana) coincida con el día que tu has elegido.

·      Y todo esto lo integramos en el operador del valor de la función ELEGIR, que al coincidir el número del mes con el numero de orden, nos devolverá el nombre del mes en formato texto.

Así de fácil, y si quieres automatizarlo, puedes explorar el modelo y encontrarás mas cosas como insertar controles de formularios y vincular ese valor a una celda, de forma que si cambia el valor de la celda, cambia el valor del texto de la forma insertada o los formatos condicionales, etc..

Reconversión Excel. MAC.

·      Me he vuelto un poco mas loco buscando las listas personalizadas de Excel. En Windows están aquí.

En MAC podremos encontrarlas en Excel > Preferencias, o también con “⌘ + ,”.

·      Para fijar referencias absolutas pulsar la tecla de “⌘ +T”, es el equivalente al F3 de Excel para Windows.