Para nombrar celdas en Excel simplemente hay que indicar a que columna y fila pertenece cada una de ellas.
Una celda no es más que la intersección de una columna y una fila de modo que cada una de ellas tiene un nombre (Columna) y apellidos (Fila). En el ejemplo
De esta manera todas las celdas de Excel tiene su referencia unívoca y podremos utilizarlas en cálculos independientemente de su contenido. Esto es lo que confiere a Excel su gran potencial. Si nos paramos a analizarlo en detalle entonces nos damos cuenta de como hemos trabajado durante años sin pararnos a pensar en algo tan obvio y es que trabajamos con el contenido de las celdas sin embargo nos referimos a ellas por su nombre y apellidos.
Modelamos funciones muy complejas que se refieren siempre a los nombres y apellidos de las celdas. De esta manera, cuando cambiamos el contenido de la celda, nuestra función sigue teniendo validez. Es entonces fundamental intentar siembre que nuestras funciones trabajen con «nombres de celda» y evitar en la medida de lo posible introducir valores en las funciones. Pongamos un ejemplo:
En la celda K3 hemos diseñado una función que nos calculará la suma de H3 e I3. Haciendo uso de una función y dos referencias podremos usar ese calculo a pesar de que los datos en H3 e I3 cambiase. ¡¡¡Podemos reutilizar el cálculo!!! Esta forma de trabajar se considera una buena práctica en Excel. Puedes consultar otras buenas prácticas aquí.
Sin embargo, si hubiéramos escrito una fórmula, como la que se muestra a continuación no podríamos reutilizar el cálculo. Cada vez que cambiase el contenido de las celdas H3 e I3 tendríamos que actualizar nuestra fórmula.
Nombrar celdas en Excel formato R1C1
Por defecto para nombrar celdas en Excel utilizamos el formato (columna,fila) como si se tratase de unas coordenadas. Por ejemplo para referenciar a la celda intersección de la columna B con la fila 2 diremos B2.
Pues bien, existe otro forma, mucho menos común de referenciar a las celdas. Y es mediante el modelo Fila 1, Columna 1. Este modelo establece que las columnas se identifican de forma numérica en vez de utilizar letras. Si nos fijamos en el gráfico la celda que normalmente nombraríamos como D6 ahora es la R6C4 dónde la R (rows) se refiere a las filas y la C (columns) se refiere a las columnas.
Cuando trabajamos en entornos de oficina donde una hoja de Excel es compartida por varias personas y cada una de ellas realiza labores diferentes sobre la hoja entonces es fundamental facilitar la entrada de datos de manera que se eviten los fallos de mecanografía es lo que se conoce como validación de datos en Excel.
Además podemos preestablecer de partida mediante la validación de datos una nomenclatura común de cara a convertir los ficheros en estructuras de información homogénea. Por ejemplo si estamos trabajando con una columna en la que varias personas actualizan “fechas” estaremos entonces de acuerdo en que el preestablecer de partida como se rellenan esas fechas es un factor importante para evitar errores.
Se puede ajustar como se rellenan los datos en cada celda y presentar un mensaje por pantalla a la persona que rellena la celda cuando intenta introducir un valor fuera del estándar predefinido. De esta manera todos los que trabajaran con la hoja de Excel compartida utilizarán un criterio común en el rellenado de datos gracias a la validación de datos en Excel.
Por ejemplo mediante la validación de datos en Excel se pueden definir reglas para introducir números dentro de unos umbrales (números mayores que 0 y menores que 10), listas de valores estáticas, fechas entre unos rangos determinados (fechas entre el 01-01-2017 y 31-12-2017), horas concretas (09:00 y 19:00), cadenas de texto con un máximo y mínimos de caracteres e incluso podremos predefinir una función que evalúe el dato introducido y en base a algún criterio permita que se escriba o no. Para acceder a la validación de datos en Excel tendremos que hacer clic sobre el menú “Datos” y después sobre la opción “Validación de datos”.
Ajustes de la validación de datos en Excel
Dentro del menú de validación de datos entonces encontraremos tres pestañas. Nos detenemos ahora en la primera de ellas. “Configuración”. Y veremos un ejemplo de validación de datos en Excel para cada una de las opciones del menú desplegable.
Cualquier valor: No existe validación de datos. Es el estado por defecto definido para todas las celdas de Excel.
Número entero: Utilizaremos ésta validación de datos cuando necesitamos que se rellene una celda con un valor entero y no decimal. Por ejemplo el campo “Edad”. Podemos definir que se introduzcan valores comprendidos entre 1 y 110 años.
Número decimal: Con los números decimales se sigue el mismo procedimiento que con los números enteros. Sin embargo Excel nos mostrará un error cuando el dato introducido sea diferente a un número decimal.Un ejemplo puede ser el formatear toda una columna para que nos rellenen los porcentajes de descuento de un determinado producto o servicio.
Lista: El ejemplo de validación de datos en Excel con uso de listas es el más extendido y además el más vistoso. Nos permite dentro de una celda mostrar un desplegable que muestre diferentes valores y la persona que rellena la celda tendrá que seleccionar uno de esos valores. Evitando así cualquier tipo de error en la mecanografía. Además se utiliza para preparar listas enlazadas en Excel combinado con el uso de la función indirecto en Excel. El efecto que se conseguido tras aplicar esta validación de datos en un listado desplegable que como hemos mencionado evita que la persona que rellena la hoja de datos introduzca textos y así ayudaremos a que no se cometan errores de escritura.
Fechas: El caso de fechas al igual que ejemplos anteriores obligará a que los datos que se rellenen se encuentren entre dos fechas predefinidas. Si por ejemplo solo se pueden rellenar fechas de un trimestre o un año concreto esta validación de datos en Excel cubrirá nuestras necesidades.
Horas: En el caso de las fechas podremos definir una horquilla de tiempo entre las 9:00 am y las 19:00 de la tarde si por ejemplo nos referimos a un horario de atención o un horario en el que nuestros comerciales se desplazan a realizar visitas a los clientes.
Texto de un número determinado de caracteres: Para esta validación y como su nombre indica lo que se controla es que no se introduzcan cadenas de caracteres superiores a un determinado número de letras. Pero también podemos usarlo para obligar a que al menos se escriba una palabra o un texto mínimo.
Personalizado: Es el último y más complejo de todos los sistemas de validación de datos. En este tipo de validación se comprueba el valor introducido en la celda mediante una función de Excel (Ojo! Solamente aquellas funciones o fórmulas cuya salida o resultado sea valor verdadero o falso) y si se cumple la condición definida en la fórmula o función entonces se permitirá el dato introducido. Por ejemplo si queremos validar que solo se introduzcan valores enteros mayores o iguales a 0 podremos utilizar la siguiente validación de datos en Excel personalizada.
Redondear con un círculo datos no válidos
Gracias a la herramienta redondear con un círculo podremos averiguar dentro de nuestra hoja de cálculo que celdas no cumplen las reglas de validación de datos.
Hemos visto que cuando una celda esta vacía y previamente se ha aplicado sobre ella una regla de validación de datos entonces a la hora de rellenar esa celda la regla deberá ser cumplida. Sin embargo si aplicamos reglas de validación de datos en hojas de Excel que ya tenían datos en las celdas Excel permite que esos datos permanezcan en su celda original y no se estará aplicando la regla de validación de datos. Entonces, ¿Cómo podemos averiguar que datos de los que ya estaban cargados en la hoja incumplen las reglas de validación que hemos creado?
Gracias a redondear con un círculo los datos no válidos podremos resaltar aquellos datos que no cumplen con la validación que hubiéramos aplicado. Seleccionaremos todo el rango sobre el que hemos creado las reglas de validación de datos en Excel y seguidamente seleccionamos la opción “Marcar con un circulo los datos inválidos”
Como resultado veremos en nuestro fichero de Excel todos aquellos datos que estaban precargados en la hoja de Excel y que no cumplen con las normas que hemos establecido en validación de datos.
Podemos hacer desaparecer los círculos en la opción “Eliminar círculos de validación.”
Dentro de una hoja de cálculo podemos trabajar con fórmulas y podemos trabajar con funciones. Es imprescindible entonces conocer qué operadores de Excel están a nuestra disposición y cómo podemos utilizarlos. Los operadores de Excel permiten realizar operaciones entre números, rangos, funciones y tablas. Existen operadores aritméticos, operadores de comparación, operadores de texto y operadores de referencia.
Antes de entrar de presentar los operadores de Excel vamos a diferenciar lo que son las fórmulas y las funciones. Una fórmula por ejemplo es cualquier cálculo que podemos realizar con una calculadora normal y corriente, «2 x 2=4», donde han intervenido varios números y varios operadores. Una función es algo más complejo que una fórmula. Una función puede realizar en su interior cálculos muy elaborados. Trataremos las funciones como cajas negras a la que suministramos unos «inputs» o «argumentos» y nos devuelven unos resultados. En excel las funciones se agrupan por familias.
Dentro de una celda de excel se puede utilizar indistintamente fórmulas y funciones por ejemplo =SUM(A2;A3;A4) * 1,21
Las fórmulas más comunes agrupadas en familias son:
Aritméticas.
Comparación.
Texto.
Referencia.
Operadores de Excel
Y los operadores que habitualmente se utilizan en esas fórmulas agrupados también por familias son los siguientes:
Operadores aritméticos. (+, -, *, /, %, ^)
Operadores de comparación. (=,<,>, >=,<=, <>)
Operadores de texto. (&)
Operadores de referencia. (:, ; ,blanco)
Se puede consultar más información sobre operadores de Excel en la web de Microsoft haciendo clic en este enlace.
Cuál es nuestro representante de ventas que ha conseguido más ventas y el que menos en cada servicio.
Comenzamos como en casos anteriores visualizando mentalmente las etiquetas de columna que formarán parte en nuestro informe de tabla dinámica. En esta ocasión serán los “Vendedores”, los “Servicios” y finalmente las cifras de venta que como en casos anteriores obtendremos de las ventas.
Una vez analizados los datos que queremos mostrar representamos la información con el informe de tabla dinámica.
Vemos que los datos no están ordenados. Queremos visualizar en primer lugar aquel vendedor que ha realizado el mayor número de ventas de consultoría. Igualmente ocurre con las cifras de formación. Para resolverlo situamos el ratón sobre la primera cifra y de nuevo como cuando agrupamos fechas hacemos clic con el botón derecho. Sin embargo esta vez seleccionaremos la opción “Ordenar” y después “De mayor a menor”.
Ahora daremos a las cifras el formato de moneda y observamos lo que ocurre si tratamos de ordenar los datos de ventas de “Formación” también de mayor a menor.Vemos que nos ha desordenado “Consultoría”
Para resolver el problema enviaremos el campo “Servicio” a “Filtros” y obtendremos un informe para Consultoría y otro informe distinto para Formación. Finalmente eliminaremos el campo “Servicio” de la zona de columnas y así tendremos un informe con el resultado de ambos tipo transacciones para cada representante de ventas. Una vez arrastrado el campo “Servicio” a la zona de filtros debemos desplegar en la zona de presentación de informe y marcar el filtro que deseamos aplicar como se muestra en la imagen. Realizaremos la misma operación para “Formación” y en ambos casos ordenaremos los datos de mayor a menor.
Para averiguar el mejor y peor vendedor tendremos en cuenta el total acumulado tanto de formación como de consultoría. Para ello eliminaremos el campo servicio de nuestro informe de tabla dinámica.
Recuerda que puedes descargar el fichero con los datos originales para que pruebes tu mismo a hacer las tablas dinámicas desde este acceso directo:
Finalmente habíamos comenzado a elaborar distintos informes de tablas dinámicas que contestaran a preguntas como:
¿Qué cifra de ventas se ha obtenido en cada zona para cada servicio? Puedes consultar como contestamos a esta pregunta en el artículo anterior.
Evolución de las ventas mensual y trimestral de cada servicio
Cuál es nuestro representante de ventas que ha conseguido más ventas y el que menos en cada servicio.
Evolución de las ventas mensual y trimestral de cada servicio
De nuevo comenzamos pensando que campos utilizaremos en nuestro informe. El precio ira en la zona de “Valores” y parece lógico que además sea agrupado con la suma. Los “Servicios” como en el anterior caso pueden ir colocados en “Columnas”. Y finalmente el campo “Fecha” lo llevaremos a zona de filas.
Si presentamos la información tal y como hemos diseñado en nuestro esquema anterior obtendremos un resultado como el que presentamos a continuación:
Sin embargo, en la pregunta que nos formulan, hablan de meses y trimestres. Para resolver esta problemática nos situaremos en nuestro informe de tabla dinámica y haremos clic con el botón derecho sobre cualquiera de los valores de fecha. En el menú contextual elegiremos la opción “Agrupar” y haremos clic sobre «Meses y Trimestres» para que queden sombreados en azul como en la imagen. Seguidamente haremos clic en “Aceptar”.
Como resultado veremos que la información queda segmentada tal y como andábamos buscando. Además en la zona de campos de tabla dinámica aparece un nuevo campo “Trimestres”
Para finalizar nuestro informe convertiremos cada una de las cifras a formato moneda € y además centraremos los datos para que queden alineados visualmente.
Recuerda que puedes descargar el fichero con los datos originales para que pruebes tu mismo a hacer las tablas dinámicas desde este acceso directo:
Para ofrecer las mejores experiencias, utilizamos tecnologías como las cookies para almacenar y/o acceder a la información del dispositivo. El consentimiento de estas tecnologías nos permitirá procesar datos como el comportamiento de navegación o las identificaciones únicas en este sitio. No consentir o retirar el consentimiento, puede afectar negativamente a ciertas características y funciones.
Funcional
Siempre activo
El almacenamiento o acceso técnico es estrictamente necesario para el propósito legítimo de permitir el uso de un servicio específico explícitamente solicitado por el abonado o usuario, o con el único propósito de llevar a cabo la transmisión de una comunicación a través de una red de comunicaciones electrónicas.
Preferencias
El almacenamiento o acceso técnico es necesario para la finalidad legítima de almacenar preferencias no solicitadas por el abonado o usuario.
Estadísticas
El almacenamiento o acceso técnico que es utilizado exclusivamente con fines estadísticos.El almacenamiento o acceso técnico que se utiliza exclusivamente con fines estadísticos anónimos. Sin un requerimiento, el cumplimiento voluntario por parte de tu proveedor de servicios de Internet, o los registros adicionales de un tercero, la información almacenada o recuperada sólo para este propósito no se puede utilizar para identificarte.
Marketing
El almacenamiento o acceso técnico es necesario para crear perfiles de usuario para enviar publicidad, o para rastrear al usuario en una web o en varias web con fines de marketing similares.
