EXECEL ~ INGENIEROS, TÉCNICOS

PUBLIQUE SE AQUÍ

   

BUSCA EL TEMA QUE REQUIERAS AQUI

lunes, 30 de marzo de 2020

EXECEL


Manual Práctico de Excel

1.    Historia de Excel y sus componentes
1.1.  Que es Excel
1.2.  Componentes de las Hojas de cálculos
1.3.  Un poco de historia
1.4.  Tipos de datos
1.4.1.    Valor Numérico
1.4.2.    Texto
1.4.3.    Formulas
2.    Celdas y Rangos
2.1.  Las filas
2.2.  Las columnas
2.3.  Las celdas
2.4.  Los rangos
3.    Tipos de Pegados
3.1.  Pegar
3.2.  Fórmulas
3.3.  Formato de fórmulas y números
3.4.  Mantener formato de origen
3.5.  Mantener ancho de columnas de origen
3.6.  Transponer
3.7.  Valores
3.8.  Formato de valores y números
3.9.  Formato de valores y origen
3.10.             Formato
3.11.             Pegar vínculo
3.12.             Imagen
3.13.             Tipos de Operadores
3.14.             Fórmulas aritméticas
3.15.             Fórmulas de comparación
3.16.             Fórmulas de texto
3.17.             Fórmulas de referencia
3.17.1. Tipos de Errores Excel
3.18.             Error #¿NOMBRE?
3.19.             Error #¡REF!
3.20.             Error #¡DIV/0!
3.21.             Error #¡VALOR!
3.22.             Error #¡NUM!
3.23.             Error #¡NULO!
3.24.             Error #N/A
3.25.             Manejo de Errores
3.26.             ESNOD
3.27.             ESERR
3.28.             SI.ERROR
3.29.             ESERROR
4.    Gráficos y presentación de datos
4.10.             Esquinas redondeadas en gráficos
4.11.             Alinear gráficos
4.12.             Cómo imprimir un gráfico
4.13.             Plantillas de gráficos
4.14.             Gráfico de máximos y mínimos
4.15.             Gráficos combinados en Excel
5.    Mini Gráficos
5.1.  Tipos de minigráficos en Excel
5.2.  Línea
5.3.  Columna
5.4.  Ganancia o pérdida
5.5.  Cómo crear un minigráficos
5.6.  Cambiar el tipo de minigráficos
5.7.  Editar un minigráficos
5.8.  Borrar un minigráficos
6.    Filtros
6.1.  Cómo crear filtros en Excel
6.2.  Cómo usar los filtros en Excel
6.3.  Filtrar por varias columnas
6.4.  Cómo quitar un filtro en Excel
6.5.  Filtrar en Excel buscando valores
6.6.  Filtros de texto en Excel
6.7.  Filtros de número en Excel
6.8.  Filtros de fecha en Excel
6.9.  Filtrar por color en Excel
7.    Filtros Avanzados
7.1.  Aplicar Filtros Avanzados en Excel
8.    Tablas dinámicas
8.11.             Crear un gráfico dinámico
8.12.             Mover un gráfico dinámico
8.13.             Cambiar origen de datos de una tabla dinámica


¿Qué es Excel?
Excel es un programa informático desarrollado por Microsoft y forma parte de Office que es una suite ofimática la cual incluye otros programas como Word y PowerPoint. Excel se distingue de los demás programas porque nos permite trabajar con datos numéricos, es decir, podemos realizar cálculos, crear tablas o gráficos y también podemos analizar los datos con herramientas tan avanzadas como las tablas dinámicas.

Excel es una hoja de cálculo
Excel es un programa computacional que es conocido como hoja de cálculo. Las hojas de cálculo fueron desarrolladas desde la década de 1960 para simular las hojas de trabajo contables de papel y de esa manera ayudar en la automatización del trabajo contable.



Un poco de historia de Excel

En el año 1982, Microsoft incursionó en el ámbito de las hojas de cálculo electrónicas con el lanzamiento de su producto Multiplan. En ese tiempo ya existían dos competidores que estaban fuertemente posicionados en el mercado: SuperCalc de la empresa Sorcim y VisiCalc de la empresa VisiCorp, así que no era una tarea sencilla para Microsoft ganar un poco de mercado.
Por si fuera poco, en el año 1983, fue lanzada la hoja de cálculo Lotus 1-2-3 que tomó a todos por sorpresa y se posicionó rápidamente como la hoja de cálculo más utilizada. Microsoft solamente observó cómo la hoja de cálculo de Lotus se posicionaba en el primer lugar, así que hizo a un lado su producto Multiplan y comenzó a trabajar en la primera versión de Excel la cual fue lanzada en el año 1985.
Esa primera versión de Excel fue hecha exclusivamente para la plataforma Macintosh, pero dos años después, en 1987, Microsoft lanzó Excel 2.0 que ya consideraba una versión para la plataforma Windows. A partir de ese momento, la aplicación de Microsoft comenzó su crecimiento hasta convertirse en la hoja de cálculo más utilizada de todos los tiempos.

La pantalla principal de Excel

La característica principal de Excel, tal como lo conocemos hoy en día, es que la pantalla principal muestra una matriz de dos dimensiones, es decir, está formada por filas y por columnas. De esta manera se forman pequeños recuadros que conocemos como celdas donde cada una de ellas tendrá una dirección única que estará conformada por la columna y la fila a la que pertenece, es decir, la dirección será una letra (columna) y un número (fila). Por ejemplo, la celda superior izquierda de la matriz tiene la dirección A1.

En cada una de las celdas podemos ingresar datos numéricos y alfanuméricos. Una manera de saber si Excel ha reconocido un dato como un número, o como un texto, es que si introducimos un dato de tipo texto se alineará a la izquierda de la celda mientras que los datos numéricos se alinearán a la derecha.

De esta manera sabemos que Excel reconoce entre un dato numérico y un dato alfanumérico en cada celda.
Componentes De Excel


 
 Para que sirve Microsoft Excel?
§  Microsoft Excel sirve para:
§  La elaboración de tablas
§  La creación de graficas
§  Operaciones Aritméticas
§  Hacer calendarios especializados
§  Hacer facturas
§  Realizar horarios, de trabajo o escolares
§  Crear informes detallados (por ejemplo informes contables).
§  Sirve para elaborar presupuestos
Permite inserta vínculos a textos u hojas de cálculo relacionadas
Insertar imágenes (por ejemplo gráficas), en las hojas de cálculo
Editar hojas de cálculo de otros programas y plataformas similares compatibles, como Open office, KOffice y Star Office, entre outros.
Otras funciones de Microsoft Excel:
Microsoft Excel sirve para realizar gráficas.- Con el Excel, podemos utilizar gráficas que dan una idea visual de las estadísticas de los datos que estamos manejando.
Crear vínculos.- Este programa permite que se puedan insertar vínculos, ya sea a direcciones de páginas web, a otras hojas de cálculo, a secciones especiales de una misma hoja de cálculo, a gráficas, o imágenes, para que estas se habrán de manera automática al realizar clic en dicho enlaces.
Microsoft Excel sirve para hacer plantillas.- Con este programa, el usuario puede hacer o modificar sus propias plantillas, especialmente adaptadas a sus necesidades, agregando por ejemplo funciones automáticas idóneas para la labor que realice cotidianamente, sistematizando y haciendo más eficiente el trabajo.



Tipos de datos en Excel 2013
Todos los libros de Excel están formados por hojas, cada una de las hojas contiene más de 17 mil millones de celdas y en cada una de esas celdas podremos almacenar nuestros datos. Por esta razón es importante conocer los tipos de datos que podemos ingresar en cada una de las  celdas de Excel.
Aunque pareciera sorprendente, cada celda de Excel puede contener solamente tres tipos de datos:
1.   Valores numéricos
2.   Texto
3.   Fórmulas
En esta lección hablaremos de los primeros dos tipos de datos y dejaremos el detalle de las fórmulas para una lección posterior.
Valores numéricos en Excel
El primer tipo de dato que analizaremos serán los valores numéricos los cuales representan cantidades, montos, porcentajes, etc.  Cuando ingresamos un valor numérico en una celda el contenido será alineado a la derecha:

Un valor numérico puede tener un formato especial, es decir, Excel puede desplegar el valor numérico de diferentes maneras. Por ejemplo, en la siguiente imagen puedes observar que las columnas B y C tienen los mismos valores numéricos que la columna A pero son desplegados de una manera diferente:

Es importante distinguir entre el valor de una celda y el valor desplegado por la celda. En el ejemplo anterior las celdas de una misma fila tienen el mismo valor pero son desplegados de manera diferente debido al formato aplicado a la celda el cual modifica la apariencia pero no su valor. Para este ejemplo el formato de las columnas B y C fue aplicado utilizando la lista de formatos que se encuentra en la pestaña Inicio, dentro del grupo Número.

Las fechas en Excel son números

Uno de los datos que vemos frecuentemente en Excel son las fechas y que pueden desplegarse de diferentes maneras. En la siguiente imagen puedes observar diferentes formatos que Excel puede aplicar a una misma fecha:


Aunque cada celda muestra un formato de fecha diferente, en realidad todas las celdas tienen el mismo valor. Una manera de conocer el valor numérico de una fecha es cambiando el formato de la celda al formato general. En la siguiente imagen he copiado los mismos valores de la columna A en la columna B y he aplicado el formato General.


 De esta manera podemos observar que la fecha 01/01/2013 es en realidad el valor numérico 41275. La manera en que Excel calcula dicho número es obteniendo la cantidad de días transcurridos a partir del 1 de enero de 1900. Una manera de comprobar que la numeración comienza con esa fecha es ingresando el número 1 en una celda y posteriormente cambiar su formato a Fecha y Excel desplegará el valor 01/01/1900 dentro de la celda.
La ventaja de manejar las fechas como valores numéricos es que los cálculos entre fechas se facilitan mucho. De esta manera, al hacer la resta de dos fechas Excel obtendrá  fácilmente la diferencia en días porque en realidad estará haciendo una simple resta de valores numéricos. Es por esta razón que las fechas en sí mismas no son un tipo de datos en Excel, si no que pertenecen al grupo de datos numéricos de la herramienta.

Precisión de los valores numéricos

En cuanto a los valores numéricos en Excel 2013 tenemos una precisión de hasta 15 dígitos, es decir, como máximo podemos tener un número de 15 dígitos. Por ejemplo, si ingresamos el números 123456789012345 en una celda, Excel lo guardará con la precisión indicada ya que el número consta exactamente de 15 dígitos pero si agregamos un 6 al final del número anterior para tener el valor numérico 1234567890123456 entonces Excel guardará dicho número con el valor 1234567890123450.
Aunque este tema podría parecer preocupante, la realidad es que muy pocos usuarios de Excel tienen problemas con esta limitante porque nunca llegan a manejar valores numéricos que requieran más de 15 dígitos.

Texto en Excel

El otro tipo de dato en Excel es el texto, que inevitablemente encontraremos en nuestras hojas de Excel ya que es utilizado para indicar diversos tipos de datos como los nombres de empleados o códigos de clientes que necesitamos incluir para dar significado a otros valores numéricos de nuestra hoja.
Por texto entendemos aquellas cadenas que están formadas por caracteres alfanuméricos y/o símbolos especiales. Una manera de identificar fácilmente una celda con un valor de texto es porque dicho valor se alineará de manera automática a la izquierda. Inclusive, un valor que comience por un número seguido de algunas letras es considerado texto y por lo tanto estará alineado a la izquierda de cualquier celda. A continuación puedes observar algunos ejemplos de celdas que contienen texto:


Formulas en Excel

A parte de los valores numéricos y el texto, una celda de Excel puede contener una fórmula que no es más que un conjunto de “instrucciones” que manipularán los valores numéricos o texto y nos devolverán un resultado.
Ya que las fórmulas son la base de Excel trataremos su uso en varias lecciones posteriores. Por ahora solo debes recordar que una fórmula hace uso de los valores numéricos y el texto para hacer algún cálculo u operación y devolvernos un resultado que a su vez será un valor numérico o un texto.
Las fórmulas en Excel son el medio por el cual podemos indicar a la herramienta la manera de realizar un cálculo. Todas la fórmulas en Excel deben iniciar con el símbolo igual (=) o de lo contrario Excel interpretará la fórmula como un simple texto.
Las fórmulas en Excel siempre están formadas por una combinación de los siguientes cinco elementos:
1.   Operadores: Los operadores son los símbolos que representan las operaciones como la suma (+), la resta (-), la multiplicación o la división (/).
2.   Valores: Las fórmulas trabajan con valores los cuales pueden ser numéricos o de texto. Los valores de texto deben estar siempre encerrados por dobles comillas.
3.   Paréntesis: Los paréntesis nos ayudan a controlar el orden en que se evalúan las expresiones dentro de una fórmula.
4.   Referencias de celda: En lugar de especificar los valores en una fórmula podemos indicar la referencia de la celda que contiene el valor que deseamos incluir en el cálculo.
5.   Funciones: Una función es un procedimiento que ya está incorporado en Excel y que realiza un tipo de cálculo específico. Algunos ejemplos de funciones son: SUMA, PRODUCTO, PROMEDIO, HORA, etc.
Cómo ingresar fórmulas en Excel
Para ingresar una fórmula será suficiente con introducir el símbolo igual (=) en la barra de fórmulas y Excel sabrá que hemos comenzado a introducir una fórmula. Para aquellas fórmulas en Excel que contienen referencias de celda existen tres maneras de indicar dichas referencias: de forma manual, con el teclado o con el ratón.
  • Manual: Si decidimos ingresar las referencias en forma manual seremos responsables de colocar la dirección correcta de cada celda o de lo contrario obtendremos un mensaje de error.
  • Con flechas del teclado: La otra alternativa que tenemos para ingresar referencias de celda dentro de las fórmulas es utilizando las flechas del teclado. Cuando llega el momento de indicar una referencia dentro de una fórmula, en lugar de escribir la referencia, nos movemos con la flechas del teclado sobre la celda que deseamos incluir en la fórmula y Excel insertará la referencia en la fórmula.
  • Con el ratón: También podemos introducir una referencia utilizando el ratón con solo hacer clic sobre cada celda que necesitemos incluir en la fórmula.
Sin importar el método que utilices para ingresar una fórmula será necesario pulsar la tecla Entrar al finalizar o hacer clic sobre el botón Introducir de la barra de fórmulas. Si por el contrario quieres cancelar la introducción de la fórmula puedes hacer clic en el botón Cancelar de la barra de fórmulas o simplemente pulsar la tecla Esc.
Cómo editar las fórmulas en Excel
Si por alguna razón debes hacer cambios a tu hoja de Excel será probable que necesites modificar tus fórmulas. También es común que después de realizar algún cambio en la hoja, nuestra fórmula envíe un error por lo que se hará necesario editar la fórmula para corregir el problema. Para editar fórmulas en Excel tenemos dos métodos:
  • Edición sobre la celda: Podemos modificar una fórmula directamente en la celda si entramos en el modo de edición de Excel ya sea haciendo doble clic sobre la celda o pulsando la tecla F2.
  • Edición en la barra de fórmulas: Podemos editar una fórmula haciendo clic sobre ella para convertirla en la celda activa e ir directamente a la barra de fórmulas para hacer las modificaciones necesarias.
Una vez que termines de hacer la edición de una fórmula debes pulsar la tecla Entrar, o pulsar el botón Introducir de la barra de fórmulas, para que Excel vuelva a realizar el cálculo tomando en consideración las modificaciones.
Las fórmulas en Excel son el corazón de la herramienta. Es importante que te familiarices pronto con su uso ya que son el ingrediente principal para obtener el máximo provecho de Excel.
Celdas y rangos en Excel 2013
Las celdas son el elemento más básico dentro de una hoja de Excel y es donde colocamos nuestros datos. Cada celda está identificada por su propia dirección la cual se determina por la letra de la columna y el número de la fila a la cual pertenece.
Rangos en Excel
Un rango es un grupo de celdas contiguas y su dirección está compuesta por la dirección de la celda superior izquierda seguida de dos puntos y por la dirección de la celda inferior derecha. Por ejemplo, el rango A1:C2 se refiere a las celdas A1, B1, C1, A2, B1 y C2. Para seleccionar un rango tenemos varias alternativas:
  • Pulsa el botón izquierdo del ratón y arrástralo en la dirección deseada hasta que todas las celdas hayan sido seleccionadas y suelta el botón.
  • Pulsa la tecla Mayus y utiliza las flechas del teclado para seleccionar el rango de celdas deseado.
  • Ingresa la dirección del rango dentro del cuadro de nombres y pulsa Entrar. La dirección introducida desaparecerá del cuadro de nombres pero el rango quedará seleccionado.
·         

·         Seleccionar varios rangos

·         En ocasiones necesitas aplicar algún formato especial a rangos que no están contiguos y para poder seleccionarlos utilizaremos una técnica especial. Realiza la selección del primer rango e inmediatamente después pulsa la tecla Ctrl y mantenla pulsada mientras haces la selección de otros rangos con el ratón. Al terminar de hacer la selección podrás soltar la tecla Ctrl.
·         

·         En la imagen anterior se han seleccionado los rangos A1:B2, A5:B6 y D3:D4. Aunque este tipo de  selección se facilita con el ratón también es posible seleccionar varios rangos utilizando solo el teclado y lo único que debemos hacer es pulsar la combinación de teclas Mayús + F8 después de haber seleccionado un rango lo cual nos permitirá agregar un nuevo rango a la selección. Te darás cuenta de que has pulsado correctamente la combinación de teclas porque la barra de estado mostrará el mensaje “Agregar a la selección”.

·         Seleccionar un rango en varias hojas

·         También es posible seleccionar un rango de celdas en varias hojas, la única peculiaridad es que solo se puede hacer la selección del mismo rango dentro de las hojas indicadas. Para iniciar debemos hacer la selección del rango en la primera hoja y enseguida pulsar la tecla Ctrl y hacer clic en la etiqueta de las hojas que deseamos incluir en la selección.
·         

·         Una vez hecha la selección, cualquier comando que ejecutemos se aplicará sobre todos los rangos de las diferentes hojas incluidas. La tecla Ctrl nos permite elegir hoja por hoja pero si deseamos incluir un grupo continuo de hojas podemos utilizar en su lugar la tecla Mayús y hacer clic en la etiqueta de la última hoja y automáticamente las hojas intermedias serán incluidas.
Copiar o mover rangos
La diferencia entre copiar y mover rangos es que al copiar un rango los datos originales quedan intactos, pero cuando movemos un rango los datos originales son removidos y colocados en la nueva ubicación. Para copiar un rango solo debemos pulsar la combinación de teclas Ctrl + C (Copiar) y posteriormente seleccionar la celda destino y pulsar las teclas Ctrl + V (Pegar). La celda que hayamos elegido como destino es donde se copiará la celda superior izquierda del rango. La copia de un rango puede ser dentro de la misma hoja o en otra hoja del libro.
Para mover un rango seguimos los mismos pasos anteriores con la diferencia de que en lugar de pulsar Ctrl + C utilizaremos la combinación Ctrl + X (Cortar) y posteriormente pegaremos el rango en la celda deseada. Todos los comandos que he mencionado hasta ahora los puedes encontrar también en la ficha Inicio dentro del grupo Portapapeles.

Es posible mover un rango dentro de la misma hoja utilizando solo el ratón. Primero selecciona el rango y después posiciona el puntero del ratón en alguno de los bordes y arrastra el rango hacia la nueva ubicación. Para copiar el rango en lugar de moverlo realiza la misma acción pero mantén pulsada la tecla Ctrl mientras arrastras las celdas. Observarás que se muestra un pequeño símbolo (+) junto al puntero del ratón indicando que se realizará una copia.
Pegado especial de celdas
Cuando decidimos copiar un rango a otra ubicación podemos decidir la manera como serán pegados los datos y para ello utilizamos las opciones de pegado especial que nos ofrece Excel. Para ver las opciones disponibles debemos abrir el menú desplegable del botón Pegar:

  • Pegar: Pega el contenido de las celdas, formatos y validación de datos.
  • Fórmulas: Pega las fórmulas sin el formato de las celdas.
  • Formato de fórmulas y números: Pega las fórmulas y el formato de números.
  • Mantener formato de origen: Pega las fórmulas y todo el formato de la celda.
  • Sin bordes: Pega todo excepto los bordes de la celda.
  • Mantener ancho de columnas de origen: Pega las fórmulas y el ancho de las columnas de origen.
  • Transponer: Cambia la orientación del rango cambiando las filas por columnas o viceversa.
  • Valores: Pega solamente el resultado de las fórmulas.
  • Formato de valores y números: Pega el resultado de las fórmulas y el formato de números.
  • Formato de valores y origen: Pega el resultado de las fórmulas y todo el formato de la celda.
  • Formato: Pega solamente el formato de las celdas.
  • Pegar vínculo: Crea una fórmula en la celda destino haciendo referencia a la celda origen.
  • Imagen: Pega las celdas copiadas como una imagen.
  • Imagen vinculada: Pega la información como una imagen que es actualizada automáticamente su cambian los datos originales.
Las celdas de una hoja contienen los datos que utilizaremos en nuestras fórmulas y reportes y es posible copiarlas o moverlas a otra ubicación de acuerdo a nuestras necesidades. Excel nos ofrece diferentes opciones de pegado que es importante conocer porque serán de mucha utilidad en nuestro trabajo cotidiano con la herramienta.

Tipos de operadores
Los operadores son un elemento básico de las fórmulas en Excel. Un operador es un símbolo que representa una determinada operación. En esta ocasión haremos una revisión detallada de todos los tipos de operadores en Excel así como su precedencia, es decir, el orden en que se realizan las operaciones.

Podemos clasificar las fórmulas de Excel en cuatro grupos principales:
1.   Fórmulas aritméticas
2.   Fórmulas de comparación
3.   Fórmulas de texto
4.   Fórmulas de referencia
Cada uno de estos grupos tiene su propio conjunto de operadores que permiten realizar los cálculos y operaciones específicas de cada grupo. A continuación enlistaré los operadores de cada grupo.

1. Operadores aritméticos

Las fórmulas aritméticas son las más comunes y combinan números, referencias de celda, funciones y operadores aritméticos para realizar cálculos matemáticos. La siguiente tabla muestra los operadores aritméticos de Excel:

Aunque el porcentaje no es un operador aritmético, Excel le da un tratamiento como operador ya que al ingresar un símbolo de porcentaje después de un número provocará que Excel realice una división entre 100 de manera automática.

2. Operadores de comparación

Los operadores de comparación nos permiten comparar dos o más números o cadenas de texto. Si el resultado de la comparación es positivo, obtendremos como resultado en valor lógico VERDADERO. De lo contrario obtendremos como resultado el valor FALSO. A continuación tenemos la lista de operadores de comparación:

Los operadores de comparación son muy utilizados con las que nos permiten ejecutar una  acción al cumplirse la condición establecida.

3. Operadores de texto

Las fórmulas de Excel también pueden manipular texto y pueden hacer uso del operador de concatenación para unir el valor de dos cadenas de texto.

4. Operadores de referencia

Los operadores de referencia nos ayudan a combinar dos o más referencias de celda para crear una sola referencia.

Recuerda que si tu sistema tiene la configuración regional de España, entonces el operador de unión será el punto y coma (;) y no la coma como aparece en la imagen.

Precedencia de operadores en Excel

Cuando creamos fórmulas que contienen más de un operador, será necesario conocer el orden en que dichas operaciones serán calculadas por Excel. Por esta razón existe un orden de precedencia que determina la manera en que Excel hace los cálculos:

Si tenemos una fórmula con varios operadores con la misma precedencia, entonces Excel realizará los cálculos de izquierda a derecha.

Cómo controlar el orden de precedencia

La única manera en que podemos influir en el orden de precedencia de operadores en Excel es utilizando paréntesis. Al colocar paréntesis alrededor de un cálculo estaremos diciendo a Excel que deseamos otorgar una alta prioridad a dicho cálculo y por lo tanto se realizará primero. En la siguiente imagen puedes observar cómo se modifica el resultado de una fórmula al incluir paréntesis.

Cuando existen paréntesis dentro de una fórmula, Excel comenzará los cálculos con el paréntesis que tenga el mayor nivel de anidación. Y si dentro de un mismo paréntesis existen varios operadores, entonces se aplicarán las reglas de precedencia antes vistas. De esta manera los paréntesis nos permiten controlar el orden de precedencia de los cálculos es una fórmula de Excel.

El operador de intersección de Excel

El operador de intersección de Excel es poco conocido pero es de gran utilidad para encontrar una celda que sea común entre dos rangos. El operador de intersección es un espacio en blanco que se coloca entre los dos rangos que deseamos evaluar.

Encontrar la intersección de dos rangos

Considera la siguiente tabla donde he colocado números aleatorios. De color verde podrás identificar el rango A1:B3 y de color azul el rango B3:C5. La celda B3 tiene un fondo de color gris que significa que dicha celda pertenece a ambos rangos.

Si quisiera encontrar la intersección de dos rangos utilizando una fórmula puedo utilizar el operador de intersección de la siguiente manera:
=A1:B3 B3:C5
Debes notar el espacio en blanco que se encuentra entre ambos rangos. Al colocar esta fórmula en Excel obtendré el valor de la intersección:

Con esta fórmula podemos entender fácilmente el comportamiento del operador intersección de Excel.

Sumar la intersección de dos rangos

Ahora mostraré cómo podemos utilizar el operador de intersección dentro de la función SUMA para sumar el valor de todas las celdas que se encuentra en la intersección de dos rangos. La fórmula que utilizaré es la siguiente:

=SUMA(A1:B3 B2:C5)


En la siguiente imagen puedes observar esta fórmula y comprobar el resultado es efectivamente la suma de las celdas B2 y B3 que son la intersección entre ambos rangos:

Encontrar la intersección entre una fila y una columna

Considera la siguiente tabla de datos que contiene la información de ventas de cada uno de los vendedores a lo largo de un año:

Si quisiera encontrar las ventas para Hugo en el mes Junio podría utilizar algunas funciones de Excel para obtener el resultado correcto:

=INDICE(A1:D13, COINCIDIR("Junio", A1:A13,0), COINCIDIR("Hugo", A1:D1, 0))

Observa cómo la fórmula nos devuelve el resultado esperado:

Sin embargo, este mismo resultado lo podemos obtener de una manera mucho más simple utilizando el operador de intersección de Excel con la siguiente fórmula:

=B1:B13 A7:D7


Observa el resultado de dicha fórmula en la siguiente imagen:

De esta manera puedes encontrar fácilmente la intersección entre una fila y una columna en Excel utilizando el operador de intersección.
Tipos de errores de Excel 2013

Error #¿NOMBRE?

El tipo de error #¿NOMBRE? se genera cuando una celda hace referencia a una función que no existe. Por ejemplo, si introducimos la fórmula =FORMATOFINAL() obtendremos este tipo de error porque es una función inexistente.
Cuando veas desplegado el error #¿NOMBRE? debes asegurarte de que has escrito correctamente el nombre de la función. Y si estás acostumbrado a utilizar el nombre de las funciones en inglés, pero te encuentras utilizando una versión de Excel en español, debes utilizar su equivalente en español o de lo contrario obtendrás este tipo de error.

Error #¡REF!

Cuando una celda intenta hacer referencia a otra celda que no puede ser localizada porque tal vez fue borrada o sobrescrita, entonces obtendremos un error del tipo #¡REF!.
Si obtienes este tipo de error  debes revisar que la función no esté haciendo referencia a alguna  celda que fue eliminada. Este tipo de error es muy común cuando eliminamos filas o columnas que contienen datos que estaban relacionados a una fórmula y al desaparecer se ocasiona que dichas fórmulas muestren el error #¡REF!

Error #¡DIV/0!

Cuando Excel detecta que se ha hecho una división entre cero muestra el error #¡DIV/0! Para resolver este error copia el denominador de la división a otra celda e investiga lo que está causando que sea cero.

Error #¡VALOR!

El error #¡VALOR! sucede cuando proporcionamos un tipo de dato diferente al que espera una función. Por ejemplo, si introducimos la siguiente función =SUMA(1, “a”) obtendremos el error #¡VALOR! porque la función SUMA espera argumentos del tipo número pero hemos proporcionado un carácter.
Para resolver este error debes verificar que has proporcionado los argumentos del tipo adecuado tal como los espera la función ya sean del tipo texto o número. Tal vez tengas que consultar la definición de la función para asegurarte de que estás utilizando el tipo de datos adecuado.

Error #¡NUM!

El error #¡NUM! es el resultado de una operación en Excel que ha sobrepasado sus límites y por lo tanto no puede ser desplegado. Por ejemplo, la fórmula =POTENCIA(1000, 1000) resulta en un número tan grande que Excel muestra el error #¡NUM!

Error #¡NULO!

El error #¡NULO! se genera al especificar incorrectamente un rango en una función. Por ejemplo, si tratamos de hacer una suma =A1 + B1 B5 Excel mostrará este tipo de error. Observa que en lugar de especificar el rango B1:B5 he omitido los dos puntos entre ambas celdas.
Este error se corrige revisando que has especificado correctamente los rangos dentro de la fórmula.

Error #N/A

Este tipo de error indica que el valor que estamos intentando encontrar no existe. Por esta razón el error #N/A es muy común cuando utilizamos funciones de búsqueda como BUSCARV o BUSCARH. Cuando la función BUSCARV no encuentra el valor que estamos buscando, regresa el error de tipo #N/A
Cuando te familiarizas con los errores en Excel podrás resolver fácilmente las causas que ocasionan cada uno de ellos.

Manejo de errores en Excel

Los errores en Excel son comunes cuando trabajamos con fórmulas, pero existen diferentes métodos para tratar con cada uno de ellos de manera que podemos tomar una decisión adecuada al momento de detectar alguno de ellos.
Una razón por la que generalmente deseamos tratar con los errores de Excel es porque deseamos desplegar un mensaje personalizado en lugar de permitir que Excel muestre sus códigos de error. En cualquier caso, el primer paso que debemos dar es conocer si un valor es efectivamente un error.

Funciones que informan sobre errores

Existen algunas funciones de información que nos permiten saber si un valor es efectivamente un error. Las funciones que nos ayuda en esta tarea son: la función ESNOD, la función ESERR y la función ESERROR. Cada una de estas funciones es diferente y a continuación explicaré las diferencias entre ellas.
La función ESNOD solamente detecta los errores de tipo #N/A. La función ESERR detecta todos los errores de Excel excepto el error de tipo #N/A. Finalmente la función ESERROR detecta todos los errores de Excel. Observa a continuación una tabla comparativa de cada una de estas funciones.



Este ejemplo comprueba que la única diferencia entre la función ESERR y ESERROR es la evaluación que cada una hace sobre el tipo de error #N/A.

Manejo de errores en Excel

La mancuerna perfecta para las funciones de información de errores es la función SI a través de la cual podremos decidir la acción a tomar en caso de que exista un error. Considera la siguiente fórmula:

=SI(ESERROR(A1), "Error", A1)


Esta fórmula evalúa el valor de la celda A1 y en caso de que contenga un error desplegará el mensaje “Error” de lo contrario mostrará el valor de la celda A1.

De esta manera podemos saber cuando una celda contiene un error y en base a ello tomar una decisión.

La función SI.ERROR

La combinación de fórmulas que acabo de mostrar para el manejo de errores en Excel ha sido muy común entre los usuarios y es por eso que a partir de la versión 2007 se incluyó la función SI.ERROR la cual es un excelente remplazo para la combinación de la función SI y la función ESERROR. Observa cómo puedo remplazar la fórmula del ejemplo anterior:

Opción 1:

SI(ESERROR(A1), "Error", A1)

Opción 2:

SI.ERROR(A1, "Error")

El resultado de ambas funciones será el mismo, pero será más rápido introducir la segunda fórmula la cual utilizar la función SI.ERROR.

Es una buena práctica implementar un estrategia de manejo de errores en Excel de manera que las personas que consulten la información no sean distraídos ni confundidos por los códigos de error que devuelve la aplicación al encontrar un error.

Gráficos y presentación de datos

Gráficos combinados en Excel

Cómo crear un gráfico en Excel

Lo primero que debes hacer para crear un gráfico en Excel es organizar los datos que deseas trazar. Es decir, acomodar los datos en columnas y filas de manera que Excel pueda “entender” la información y crear el gráfico.
Observa el siguiente ejemplo de datos:

Una vez organizada la información debes seleccionar el rango completo para indicar a Excel que trazará el gráfico utilizando como base dicha información:

Antes de continuar debo decir que es importante conocer los tipos de gráficos en Excel de manera que puedas tomar la decisión adecuada sobre el gráfico a crear.

Tipos de gráficos en Excel

Elegir entre los diferentes tipos de gráficos en Excel para mostrar la información adecuadamente es de suma importancia. Cada tipo de gráfico desplegará la información de una manera diferente así que utilizar el gráfico adecuado ayudará a dar la interpretación correcta a los datos.

Tipos de gráficos más utilizados

Estos son los tipos de gráficos más utilizados en Excel:
Gráficos de columna. Este tipo de gráfico hace un énfasis especial en las variaciones de los datos a través del tiempo. Las categorías de datos aparecerán en el eje horizontal y los valores en el eje vertical. Frecuentemente se compara este tipo de gráfico con los gráficos de barra, donde la diferencia principal es que en los gráficos de barra las categorías aparecen en el eje vertical.

Gráficos de línea. Un gráfico de línea muestra las relaciones de los cambios en los datos en un período de tiempo. Este gráfico es comparado con los gráficos de área, pero los gráficos de línea hacen un énfasis especial en las tendencias de los datos más que en las cantidades de cambio como lo hacen los gráficos de área.

Gráficos circulares. También conocidos como gráficos de pie (en inglés) o gráficos de pastel. Estos gráficos pueden contener una sola  ya que muestran los porcentajes de cada una de las partes respecto al total.


Gráficos de Barra. Un gráfico de barra hace un énfasis en la comparación entre elementos en un período de tiempo específico. Este tipo de gráfico incluye cilindros, conos y pirámides.

Gráficos de Área. Los gráficos de área muestran la importancia de los valores a través del tiempo. Un gráfico de área es similiar a un gráfico de línea, pero ya que el área entre las líneas está relleno, el gráfico de área le da una mayor importancia a la magnitud de los valores que lo que puede hacer un gráfico de línea.

Gráficos XY (Dispersión). Los gráficos de dispersión son útiles para mostrar la relación entre diferentes puntos de datos. Este tipo de gráfico utiliza valores numéricos para ambos ejes en lugar de utilizar categorías en alguno de los ejes como en los gráficos anteriores.

Existen otros tipos de gráficos en Excel como los de superficie, anillos, burbuja, pero los analizaremos en una publicación posterior. Los gráficos presentados el día de hoy son los tipos de gráficos más utilizados en Excel y pronto aprenderás a obtener el mayor provecho de cada uno de ellos.

Partes de un gráfico de Excel

Un gráfico de Excel está formado por diferentes partes que incluyen el área del gráfico, las series de datos, ejes, leyendas,  rótulos del eje, entre otros. El siguiente gráfico muestra las partes de un gráfico de Excel que necesitas conocer:

  • Área del gráfico. Esta es el área que se encuentra definida por el marco del gráfico y que incluye todas sus partes.
  • Título del gráfico. Texto descriptivo del gráfico que se coloca en la parte superior.
  • Puntos de datos. Es un símbolo dentro del gráfico (barra, área, punto, línea) que representa un solo valor dentro de la hoja de Excel, es decir que su valor viene de una celda.
  • Series de datos. Son los puntos de datos relacionados entre sí trazados en un gráfico. Cada serie de datos tiene un color exclusivo. Un gráfico puede tener una o más series de datos a excepción de los gráficos circulares que solamente pueden tener una serie de datos.
  • Ejes. Un eje es la línea que sirve como referencia de medida. El eje Y es conocido como el eje vertical y generalmente contiene datos. El eje X es conocido también como el eje horizontal y suele contener las categorías del gráfico.
  • Área de trazado. Es el área delimitada por los ejes e incluye todas las series de datos.
  • Líneas de división. Son líneas opcionales que extienden los valores de los ejes de manera que faciliten su lectura e interpretación.
  • Título de eje: Texto descriptivo que se alinea automáticamente al eje correspondiente.
  • Leyenda. Un cuadro que ayuda a identificar los colores asignados a las series de datos.
Minigráficos en Excel 2013
Los minigráficos de Excel son gráficos muy pequeños que caben dentro de una celda y nos permiten mostrar el comportamiento de los datos a través del tiempo o representar las variaciones que existen en la información.
Es importante saber que, a diferencia de los gráficos de Excel, los minigráficos solo pueden representar una serie de datos a la vez. Los comandos que nos permiten crear los minigráficos se encuentran en la ficha Insertar y dentro del grupo Minigráficos.


Tipos de minigráficos en Excel
Existen tres tipos diferentes de minigráficos en Excel 2013 y son los siguientes:
  • Línea: Similar a un gráfico de línea
  • Columna: Similar al gráfico de columnas.
  • Ganancia o pérdida: Este tipo de minigráfico no tiene parecido con ningún gráfico de Excel ya que solo representa dos tipos de valores: ganancias o pérdidas. Un cambio positivo en los datos será representado como ganancia mientras que un cambio negativo como pérdida.
En la siguiente imagen puedes observar cada uno de los tipos de minigráficos en el mismo orden en que fueron mencionados:

Cómo crear un minigráfico
Para crear un minigráfico debemos seleccionar primero los datos que serán graficados. Es importante seleccionar solamente los datos, sin títulos de columna ni etiquetas. Posteriormente ir a la ficha Insertar y dentro del grupo Minigráficos pulsar el botón correspondiente al tipo de minigráfico que deseamos crear lo cual mostrará el cuadro de diálogo Crear grupo de Minigráfico.

Dentro del cuadro de diálogo debemos seleccionar el rango de celdas donde serán colocados los minigráficos y pulsar el botón Aceptar. Cada minigráfico quedará vinculado a los datos y si existe alguna modificación, el minigráfico será actualizado automáticamente.
Si al momento de crear los minigráficos has seleccionado varias filas (o columnas) de datos, Excel creará un grupo de minigráficos. Una manera rápida de saber si los minigráficos están agrupados es seleccionando uno de ellos y Excel colocará un borde y un color de fondo especial sobre todos los minigráficos del mismo grupo.

Una ventaja de tener los minigráficos agrupados es al momento de editarlos ya que cualquier cambio en el estilo será aplicado de manera automática a todo el grupo. Sin embargo, si quieres desagrupar los minigráficos para tratarlos de manera individual, puedes hacerlo con el comando Herramientas para minigráfico > Diseño > Agrupar > Desagrupar.



Cambiar el tipo de minigráfico
Si después de crear los minigráficos decides que no son del tipo adecuado, no es necesario eliminarlos y volver a crearlos, sino simplemente cambiar su tipo. Este cambio lo podemos hacer seleccionando el grupo de minigráficos y en Herramientas para minigráfico, dentro del grupo Tipo, elegir el nuevo tipo.

Editar un minigráfico
Una de las modificaciones que podemos hacer a un grupo de minigráficos es cambiar su estilo, es decir, los colores utilizados en el minigráfico. Puedes encontrar y seleccionar un estilo diferente desde Herramientas para minigráfico > Estilo.

Además de poder elegir un nuevo estilo, también podemos mostrar puntos específicos dentro del minigráfico como los siguientes:
  • Punto alto: Aplica un color diferente al dato de mayor magnitud.
  • Punto bajo: Aplica un color diferente al dato de menor magnitud.
  • Puntos negativos: Aplica un color diferente a todos los valores negativos.
  • Primer punto: Resalta el primer punto de la serie.
  • Último punto: Resalta el último punto de la serie.
  • Marcadores: Sólo disponible para los minigráficos de línea que coloca marcadores para todos los puntos de la serie.
Cada uno de estos puntos puede mostrase u ocultarse a través de las cajas de selección ubicadas en la ficha Herramientas para minigráfico.
Un tema importante de mencionar al hablar de la edición de un minigráfico es la modificación de su tamaño. La manera de aumentar o disminuir el tamaño de un minigráfico es modificando el ancho o el alto de la celda a la que pertenece ya que el minigráfico se ajustará para ocupar la totalidad de la celda. Por otro lado, ya que es posible colocar un minigráfico dentro de celdas combinadas, podemos aumentar el tamaño de un minigráfico combinando varias celdas evitando así la modificación del ancho o alto de celdas individuales.
Borrar un minigráfico
Un minigráfico no puede ser eliminado con solo seleccionarlo y pulsando la tecla Suprimir. Es necesario seleccionarlo e ir a la ficha Herramientas de minigráfico y pulsar el botón Borrar.

El botón Borrar nos permite eliminar solo el minigráfico seleccionado, que es la opción predeterminada, o también podemos borrar todo el grupo de minigráficos. Estas mismas opciones de borrado las encontramos al hacer clic derecho sobre un minigráfico y seleccionando la opción de menú Minigráficos.
Cinta de opciones en Excel 2013
La cinta de opciones agrupa los comandos más utilizados en Excel y las pestañas son el primer nivel de agrupamiento que encontramos dentro de la cinta de opciones. Al mismo tiempo, cada una de las pestañas organiza los comandos en grupos.
Nota importante: Lo que en Excel 2007 y 2010 conocíamos como una ficha de la cinta de opciones, a partir de Excel 2013 será conocida como una pestaña. Microsoft ha cambiado este nombre en toda su documentación en español para Excel 2013.
Pestañas en Excel 2013
La primera vez que abres Excel 2013, la cinta de opciones mostrará siete pestañas:
1.   Inicio: Contiene los comandos necesarios para aplicar formato y editar las hojas de Excel. Esta pestaña organiza los comandos en siete grupos: Portapapeles, Fuente, Alineación, Número, Estilos, Celdas y Modificar.
2.   Insertar: En esta pestaña encontramos los comandos para insertar elementos a nuestra hoja como tablas y gráficos. Los grupos de comandos de esta pestaña son los siguientes: Tablas, Ilustraciones, Aplicaciones, Gráficos, Informes, Minigráficos, Filtro, Filtros, Vínculos, Texto y Símbolos.
3.   Diseño de página: Aquí se encuentran los comandos utilizados para preparar nuestra hoja para imprimir y organizar los elementos dentro de la hoja. Los grupos de esta pestaña son: Temas, Configurar página, Ajustar área de impresión, Opciones de la hoja y Organizar.
4.   Fórmulas: La pestaña fórmulas contiene los comandos para agregar funciones y fórmulas a nuestras hojas de Excel así como buscar cualquier error en las fórmulas. Los grupos de esta pestaña son: Biblioteca de funciones, Nombres definidos, Auditoría de fórmulas y Cálculo.
5.   Datos: Contiene los comandos para importar datos provenientes de otras fuentes y trabajar posteriormente con ellos aplicando algún filtro o quitando duplicados. Los grupos de la pestaña Datos son: Obtener datos externos, Conexiones, Ordenar y filtrar, Herramientas de datos y Esquema.
6.   Revisar: En esta pestaña están localizados los comandos para revisar, proteger e insertar comentarios en las celdas de la hoja. Los grupos en los que están organizados los comandos son: Revisión, Idioma, Comentarios, Cambios.
7.   Vista: Comandos utilizados para cambiar la vista de la hoja y ocultar algunos de sus elementos. Esta pestaña contiene los siguientes grupos: Vistas de libro, Mostrar, Zoom, Ventana y Macros.

Como ya lo he mencionado, cada pestaña está formada por grupos y es precisamente dentro de cada grupo donde encontramos los botones de comando. También puedes ver que junto al nombre de algunos grupos, en la esquina inferior derecha, se muestra una pequeña flecha que es conocida como un indicador de cuadro de diálogo y al hacer clic sobre él se mostrará un cuadro de diálogo con más comandos relacionados con dicho grupo.

Pestañas contextuales
Las siete pestañas mencionadas previamente no son las únicas que se muestran en la cinta de opciones, también existen otro tipo de pestañas conocidas como pestañas contextuales y que se muestran solamente cuando estamos trabajando con algún objeto en particular. Por ejemplo, cuando insertamos un nuevo gráfico en una hoja, Excel mostrará las pestañas Diseño y Formato las cuales tienen comandos especiales para trabajar con el gráfico:

Como puedes observar en la imagen anterior, las pestañas contextuales están agrupadas e identificadas con el nombre Herramientas de gráficos. Si quitamos la selección del gráfico entonces las pestañas contextuales desaparecerán y volverán a mostrarse cundo volvamos a seleccionar de nuevo el gráfico.
Ocultar la cinta de opciones
En ocasiones necesitamos incrementar el espacio para trabajar con nuestros datos dentro de una hoja de Excel. Para ganar un poco de espacio en pantalla podemos ocultar la cinta de opciones haciendo clic en el botón Opciones de presentación de la cinta de opciones que se encuentra en la esquina superior derecha de la ventana. Al pulsar el botón se mostrarán tres opciones:

  • Ocultar automáticamente la cinta de opciones: Oculta por completo la cinta de opciones y es necesario hacer clic en la parte superior de la pantalla para poder mostrarla de nuevo y seleccionar cualquier comando. En      el momento en que regresamos al área de trabajo, la cinta de opciones se vuelve a ocultar automáticamente.
  • Mostrar pestañas: A diferencia del método anterior, esta opción deja visibles en todo momento los nombres de las pestañas. Al hacer clic sobre cualquiera de ellas se mostrarán los comandos asociados para dicha pestaña. Al regresar al área de trabajo se ocultan de nuevo todos los comandos y permanecen visibles solamente las pestañas.
  • Mostrar pestañas y comandos: Esta opción restablece el comportamiento predeterminado de la cinta de opciones dejándola visible en todo momento.
Personalizar la cinta de opciones
En Excel 2013 es posible personalizar la cinta de opciones agregando o removiendo comandos o agrupándolos de acuerdo a nuestras necesidades. Para hacer estas modificaciones debemos ir a Archivo > Opciones > Personalizar cinta de opciones:

También es posible mostrar este cuadro de diálogo si hacemos clic derecho sobre cualquier área vacía de la cinta de opciones y seleccionamos la opción Personalizar la cinta de opciones. A continuación describiré cada una de las secciones de este cuadro de diálogo:
  • A: Lista desplegable a través de la cual podemos restringir la lista de comandos mostrados en el cuadro inferior. Algunas de las opciones de la lista desplegable son: Comandos más utilizados, Comandos que no están en la cinta de opciones y Todos los comandos.
  • B: Lista de los comandos que cumplen la opción elegida de la lista desplegable superior. Podemos seleccionar cualquier comando de la lista y pulsar el botón Agregar para incluirlo en la pestaña seleccionada de la lista de la derecha.
  • C: Lista desplegable para restringir el tipo de pestañas que deseamos visualizar en la lista inferior.
  • D: Lista de pestañas que cumplen con la opción elegida en la lista desplegable superior. Para mostrar una pestaña en la cinta de opciones será suficiente con hacer clic en el cuadro de selección mostrado junto a su nombre. Para remover algún comando debemos seleccionarlo y hacer clic en el botón Quitar.
  • E: Los botones Agregar y Quitar son utilizados para incluir nuevos comandos dentro de una pestaña o para removerlos de la cinta de opciones.
  • F: El botón Nueva pestaña crea una nueva pestaña en la cinta de opciones.
  • G: Para crear un nuevo grupo de comandos dentro de una pestaña utilizamos el botón Nuevo grupo.
  • H: El botón Cambiar nombre es útil para cambiar el nombre de una pestaña o de un grupo.
  • I: Para eliminar cualquier personalización que hayamos realizados a la cinta de opciones y regresar a su configuración predeterminada podemos utilizar el botón Restablecer.
  • J: Si deseamos aplicar las configuraciones que hemos hecho a la cinta de opciones en otro equipo podemos crear un archivo de exportación con el botón Importar o exportar.

Formato de números en Excel 2013

El formato números en Excel es el proceso a través del cual podemos cambiar la apariencia de un número contenido en una celda. Es importante remarcar que dicho formato no modifica el valor numérico de la celda sino solo su apariencia.
Cuando ingresamos valores numéricos en nuestra hoja de Excel, dichos valores no tienen formato alguno, pero podemos modificar la cantidad de decimales mostrados, agregar el símbolo de moneda o el separador de miles. Todos estos cambios en la apariencia de un número los podemos lograr aplicando un formato especial a la celda.

Formato de números automático

Excel puede identificar ciertos formatos de número al momento de ingresar los datos y los despliega de manera adecuada mientras asigna el valor numérico correcto a la celda. Por ejemplo, si ingresamos el valor 12.50% en una celda, Excel mantendrá la apariencia de porcentaje pero el valor de la celda no contendrá el símbolo de porcentaje sino solamente el valor numérico 0.125 y lo podremos saber si hacemos una operación con dicha celda:

Ya que la celda D2 no contiene formato alguno se muestra el valor de la celda D1 multiplicado por dos. De esta manera podemos saber que al incluir a la celda D1 en cualquier operación, Excel considerará su valor numérico. Así como Excel aplica un formato automático a valores de porcentaje, también lo hace si utilizamos el separador de miles o si utilizamos el símbolo de moneda. En realidad el valor de la celda no contendrá el separador de miles ni el símbolo de moneda sino solamente el valor numérico.

Formato de números desde la Cinta de opciones

Hay ocasiones en las que importamos datos a Excel o simplemente queremos modificar la manera en que se despliegan los números de una celda después de haberlos ingresado y para ello podemos utilizar los comandos de la Cinta de opciones que se encuentran dentro del grupo Número en la ficha Inicio. Cualquiera de estos comandos aplicará el formato seleccionado a la celda activa o a una selección de celdas.

1.   Podemos elegir rápidamente un formato de la lista de formatos  más utilizados entre los cuales encontramos los formatos Número, Moneda, Contabilidad, Fecha corta, Fecha larga, Hora, Porcentaje, Fracción, Científica y Texto.
2.   El menú desplegable del botón Formatos de contabilidad nos permite elegir entre varias opciones de formatos específicos de contabilidad y especificar si deseamos agregar el símbolo de dólares americanos o el símbolo de euros.
3.   El botón Estilo porcentual aplicará un estilo de porcentaje al valor de la celda.
4.   El comando Estilo millares agregará el separador de miles al valor numérico de la celda.
5.   Podemos Aumentar decimales mostrados dentro de la celda siempre y cuando el valor numérico contenga dicha cantidad de decimales.
6.   También podemos mostrar menos decimales utilizando el botón Disminuir decimales.
7.   Si queremos tener más opciones de formato podemos pulsar el indicador de cuadro de diálogo del grupo Número el cual abrirá una nueva ventana que nos dará más opciones de formato.

El cuadro de diálogo Formato de celdas

Si ninguno de los comandos de formato de la Cinta de opciones atiende nuestros requerimientos de formato, entonces podemos pulsar el indicador correspondiente que se encuentra justo a la derecha de la etiqueta del grupo Número y se mostrará el cuadro de diálogo Formato de celdas:

También podemos abrir este cuadro de diálogo si seleccionamos la opción Más formatos de número dentro de la lista de formatos del grupo Número en la ficha Inicio. De igual manera tendremos acceso a él si hacemos clic derecho sobre cualquier celda y seleccionamos la opción Formato de celdas.
A través de este cuadro de diálogo tendremos un mayor control sobre el formato aplicado a una celda de Excel. La sección Número nos muestra 12 categorías de formato de las cuales podemos elegir y además configurar ciertas propiedades particulares. A continuación describo brevemente cada una de las 12 categorías:
1.   General: El formato General es el formato predeterminado de todas las celdas de una hoja y despliega los números tal como son ingresados excepto cuando el valor es muy grande para caber en la celda y lo convierte a notación científica.
2.   Número: Este formato nos permite especificar la cantidad de posiciones decimales que deseamos mostrar y además indicar si deseamos incluir el separador de miles. Otra opción de configuración es la de indicar el formato de los números negativos ya sea solamente con el símbolo negativo (-), o de color rojo, o ambas opciones.
3.   Moneda: El formato de Moneda nos permite elegir el símbolo a utilizar así como la cantidad de decimales a utilizar. Al igual que el formato anterior, podemos indicar la apariencia de los números negativos.
4.   Contabilidad: El formato de Contabilidad es diferente del de Moneda en que los símbolos de moneda siempre están alineados de manera vertical.
5.   Fecha: Esta opción nos permite elegir entre diferentes formatos para una fecha.
6.   Hora: Nos permite elegir un formato para las horas ingresadas en una celda.
7.   Porcentaje: Nos permite desplegar el símbolo de porcentaje e indicar la cantidad de decimales.
8.   Fracción: Para las fracciones mostradas en la hoja de cálculo podemos elegir entre nueve diferentes formatos.
9.   Científica: Para números expresados en notación científica podemos especificar la cantidad de decimales a mostrar.
10.               Texto: Cuando este formato es aplicado a una celda, Excel tratará dicho valor como un texto, inclusive si es un número.
11.               Especial: Esta opción contiene formatos especiales y dependen de la Configuración regional seleccionada. Algunas opciones de formatos especiales son los códigos postales, los números de teléfono o los números de seguridad social.
12.               Personalizada: La opción de formato personalizado nos da la posibilidad de crear nuestros propios formatos de números. Esta es una opción que requiere una explicación más amplia por lo cual encontrarás un vínculo a otro artículo en la sección de material adicional.
Es interesante observar que dentro de la lista de formatos se encuentran la Fecha y la Hora. Esto se debe a que Excel almacena este tipo de datos como un número aunque en realidad los vemos desplegados como fechas y horas. Ya hemos hablado de este tema cuando hicimos una revisión de los tipos de datos en Excel.
Recuerda que al aplicar un formato a un número en Excel no modificamos realmente su valor sino solamente cambiamos su apariencia dentro de la celda. Existen diferentes tipos de formatos de acuerdo al tipo de dato que hemos ingresado, ya sean porcentajes, fracciones, montos, fechas, horas, etc. Además, a través de los comandos de la Cinta de opciones y del cuadro de diálogo Formato de celdas podremos indicar el formato que deseamos aplicar a cualquier celda de Excel.

Aplicar formato de porcentaje

Muchas hojas de Excel utilizan números con formato de porcentaje para expresar tasas de interés, tasas de inflación o de crecimiento. Debes tener en cuenta la manera en como Excel aplica el formato de porcentaje a un valor.

Formato de porcentaje a un valor en celda

El formato de porcentaje se puede aplicar a un número previamente ingresado a una celda utilizando el botón Estilo porcentual que se encuentra dentro del grupo Número en la ficha Inicio.

Cuando aplicas el formato de porcentaje en Excel, se multiplica el valor de la celda por 100 y se agrega el símbolo de porcentaje.

Formato de porcentaje al capturar

Otra manera de ingresar un porcentaje es al momento de estar capturando la información en la celda. Para aplicar el formato de porcentaje solamente debes ingresar el símbolo de porcentaje después del número. Por ejemplo, para indicar un porcentaje de 50% sigue los siguientes pasos:

Observa cómo el valor ingresado se muestra como 50% aunque, si utilizas esta celda para efectuar algún cálculo numérico, Excel tomará el valor de la celda como 0.50 lo cual implica que Excel hace una división del valor ingresado entre 100 al momento de la captura.

Decimales de un porcentaje

En ocasiones deseas que Excel muestre algún número específico de decimales a un porcentaje y para ello puedes utilizar los botones Aumentar decimales y Disminuir decimales que se encuentran en el grupo Número de la ficha Inicio.


Formato personalizado de celdas en Excel

Con el formato personalizado de celdas en Excel podemos dar una apariencia particular al valor de dicha celda. Un formato personalizado puede contener hasta 4 códigos diferentes dentro de la misma cadena de texto.

Formato personalizado de una celda

Para asignar un formato personalizado a una celda en Excel solamente debes hacer clic derecho sobre la celda deseada y seleccionar la opción Formato de celdas. Al mostrarse el cuadro de diálogo debes asegurarte de estar en la sección Número y elegir la opción Personalizada:

El formato personalizado de la celda se debe especificar dentro del cuadro de texto Tipo. Un formato personalizado es una cadena de texto que indicará a Excel la manera en cómo debe mostrarse el valor contenido en una celda.

El formato personalizado en Excel

En términos de formato personalizado de celdas en Excel, cada celda puede tener 4 posibles valores: positivos, negativos, cero y texto. De esta manera un formato personalizado nos permite especificar un código para cada uno de ellos dentro de la misma cadena de texto con la condición de separarlos por un punto y coma (;) con el siguiente orden:




Cada uno de los recuadros de la imagen representa un código, de manera que podemos indicar a Excel que trate de manera diferente el valor de una celda de acuerdo al tipo de valor que tenga.

Ejemplo de formato personalizado de una celda

Para tener claro la manera en que Excel aplica un formato personalizado a una celda crearemos el siguiente formato:

[Azul]Estándar;[Rojo]-Estándar;[Negro]Estándar;[Verde]Estándar

La palabra Estándar significa que Excel dará un trato “normal” al valor de la celda, es decir, no modificará su apariencia. Sin embargo, entre cada corchete [] he especificado un color diferente de acuerdo al valor de la celda. Si el valor de la celda es positivo debe mostrarse de color azul, si es negativo de color rojo y además he colocado el símbolo “-“ de manera que Excel coloque el símbolo negativo precediendo el valor de la celda.
Si el valor es cero entonces se mostrará de color negro y si es de tipo texto será de color verde. En la siguiente animación podrás observar cómo se comporta una celda que tiene este formato personalizado:

Al usar formato personalizado de celdas en Excel solamente tendremos disponibles 8 nombres de colores para modificar la apariencia de una celda: Negro, Verde, Blanco, Azul, Magenta, Amarillo, Aguamarina, Rojo.
Existe otra opción que nos permite elegir un color por su código y tendremos disponibles hasta 56 colores diferentes. Este código lo podemos especificar de la siguiente manera:

[Color5]Estándar;[Color20]-Estándar;[Color40]Estándar;[Color50]Estándar

A diferencia del ejemplo anterior donde colocamos directamente el nombre del color, en este ejemplo debemos indicar el número del color que deseamos aplicar.

Códigos de formato personalizado

Ya hemos visto cómo modificar el color de una celda de acuerdo a su valor pero también podemos modificar otros elementos como la cantidad de decimales que deseamos mostrar.  En el siguiente ejemplo he especificado un formato personalizado que hará que siempre se muestren 3 decimales en todas las celdas. Observa el resultado:

El código # nos ayuda a representar la posición de un número y hemos indicado que deseamos tener 3 posiciones después del punto decimal. Para conocer más sobre el código # así como otros códigos te sugiero consultar el articulo Códigos de formato personalizado.

Formato personalizado a texto

Una pregunta muy frecuente de los usuarios de Excel es saber si se puede aplicar un formato personalizado a un texto. En realidad los formatos personalizados fueron creados para dar formato a números y por consecuencia también a las fechas ya que las fechas en Excel son también números.
Los formatos personalizados solo nos permiten efectuar algunas acciones con texto como hacer que se despliegue un carácter en cierta posición cuando acompaña a un número. En el siguiente ejemplo he utilizado el formato personalizado #! que colocará el signo de exclamación al final del valor de la celda.

Observa cómo la barra de fórmulas muestra que el valor de la celda es 365 pero el formato personalizado indica a Excel que se agregue el símbolo “!” al final del valor. Así como agregamos un solo carácter también podemos agregar una palabra completa al final de un número. El formato personalizado que utilizaré será el siguiente: #.## “pesos”

De nueva cuenta observa la barra de fórmulas y observa que el valor de la celda no considera la palabra “pesos” sino que es agregada por el formato personalizado. Tanto los caracteres como las palabras se pueden colocar en cualquier posición que queramos, solamente debemos indicarlo correctamente dentro del formato personalizado.
Recuerda que el formato personalizado de celdas en Excel no es una manera de manipular cadenas de texto sino que fue creado primordialmente para ayudarnos a dar formato a datos numéricos. Puedes leer un artículo complementario que escribí sobre el tema de formatos personalizados de números.

Códigos de formato personalizado en Excel

Los códigos de formato nos ayudan a definir los formatos personalizados. En el artículo anterior hablé sobre la manera en cómo se construye un formato personalizado en Excel y a continuación te mostraré una tabla con los códigos que podemos utilizar en dichos formatos.
Código
Descripción
#
Representa un número sin considerar ceros a la izquierda.
?
Deja el espacio para los caracteres especificados
0
Despliega ceros a la izquierda para rellenar el formato
.
Despliega un punto decimal
%
Despliega el símbolo de porcentaje
,
Despliega el separador de miles
E+ e+ E- e-
Despliega la notación científica
+ – / () : $
Se muestra este carácter
carácter
Despliega el carácter especificado
*
Repite el siguiente carácter hasta llenar el ancho de la columna
_
Deja un espacio del mismo ancho que el siguiente carácter
“texto”
Despliega el texto dentro de las dobles comillas
@
Representa un texto
[color]
Especifica el color de la fuente que puede ser: Negro, Azul, Cian, Verde, Magenta, Rojo, Blanco, Amarillo.
[COLOR n]
Muestra el color correspondiente de la paleta de colores donde n es un número entre 0 y 56.

El código #

Cuando utilizamos el código # estamos asegurando que ese espacio será ocupado por un número. Observa el resultado de aplicar diferentes formatos personalizados utilizando el código #:

Debemos notar que para cada aparición del código # después del punto decimal significará un solo decimal mientras que con una sola vez que coloquemos el código # antes del punto decimal será interpretado como un número de cualquier cantidad de dígitos.

El código?

Este código tiene el mismo objetivo que el anterior con la única diferencia de que muestra un espacio en blanco en caso de que el número de dígitos sea menor. Observa el siguiente ejemplo:

En este caso las celdas C4 y C5 han sido alineadas a la izquierda de manera que puedas observar cómo son conservados los espacios en blanco al utilizar el código ?

El código 0

El código 0 (cero) se comporta de manera similar que el código ? con la diferencia de que en lugar de espacios en blanco se mostrará el número cero. Observa el ejemplo:

Fechas en Excel

Excel almacena una fecha como si fuera un número entero. Al utilizar un sistema como éste, Excel puede sumar, restar y comparar fácilmente las fechas sin necesidad de hacer alguna conversión ni cálculo especial.
Por ejemplo, la fecha 1/1/2000 está representada por el número de serie 36526 y la fecha 05/10/2000 tiene asignado el valor 36804. Como puedes imaginar, al realizar una operación con fechas se utiliza su número de serie para facilitar las operaciones.

El primero y último día de Excel

Excel reconoce todas las fechas posteriores a 1/1/1900. Es por eso que el primer día del año 1900 tiene asignado el número 1. Por el contrario, el último día que Excel reconoce es el 31/12/9999 el cual tiene asignado el número 2958465.

Valor numérico de una fecha

Es realmente sencillo conocer el valor numérico de una fecha, solamente cambia el formato de la celda que contiene una fecha a un formato General y Excel desplegará su valor numérico.


La función FECHA

Finalmente mencionaré la función FECHA la cual nos ayuda a obtener el número de serie que le corresponde a una fecha dados el día, el mes y el año.

Esta función es de gran utilidad para convertir datos a un formato fecha en caso de que tengas el día, mes y año en columnas separadas. Una vez convertidos al tipo de dato adecuado se podrán hacer operaciones con las fechas sin dificultad alguna.

Formatos personalizados de números en Excel

Los formatos personalizados de números en Excel nos permiten crear diferentes tipos de formato que se adapten a nuestras necesidades. Cuando ninguno de los formatos prestablecidos es adecuado la mejor opción es crear un formato personalizado.

Crear formatos personalizados en Excel

Muchos usuarios de Excel, incluso algunos que llevan años de utilizar la herramienta, evitan crear formatos personalizados porque creen que es una tarea muy complicada. En realidad los formatos personalizados de números se ven más complejos de lo que en realidad son.
La manera más fácil de crear un formato personalizado es pulsando la combinación de teclas Ctrl + 1 para desplegar el cuadro de diálogo Formato de celdas y asegurarnos de que estamos en la sección Número. Es entonces cuando seleccionamos la categoría Personalizada.

Un formato personalizado se compone de una serie de códigos los cuales se especifican en la caja de texto Tipo de manera que el valor de una celda adopte el nuevo formato.

Partes de un formato personalizado

Un formato personalizado nos permite especificar 4 códigos dentro de la misma cadena de texto: valores positivos, valores negativos, valores cero y texto. Cada código debe ser separado por un punto y coma. Considera el siguiente ejemplo de un formato personalizado:

[Verde]Estándar;[Rojo]Estándar;[Negro]Estándar;[Azul]Estándar

En primer lugar observa que los 4 códigos están separados por un punto y coma. El primero código indica que los valores positivos serán de color verde. El segundo código nos dice que los valores negativos serán de color rojo. El tercer código hará que los valores que sean cero tengan un color negro y el último código hace que todas las celdas que sean de tipo texto deberán ser de color azul. Observa cómo se comporta este formato personalizado al aplicarlo a celdas con diferentes valores:

La palabra Estándar que aparece dentro del formato personalizado que acabamos de crear significa que el número se mostrará en un formato estándar y solamente le aplicaremos un color. Es importante notar que el ejemplo mostrado hace uso de los nombres de colores los cuales podemos utilizar en nuestros formatos personalizados.

Omitir un código en el formato personalizado

Aunque en el ejemplo mostrado especifiqué los 4 códigos, no siempre es necesario hacerlo. Podemos omitir algunos de los códigos del formato personalizado tomando en consideración lo siguiente:
  • Si solamente especificamos un solo código entonces se aplicará para todos los valores (positivo, negativo, cero y texto)
  • Si especificamos dos códigos, el primero aplicará para los valores positivos, los ceros y el texto, mientras que el segundo código será para los valores negativos.
  • Si especificamos tres códigos, el primero será para los valores positivos y el texto, el segundo código para los valores negativos y el tercer código para los ceros.
Observa el resultado de aplicar un formato personalizado con tres secciones solamente:

[Verde]Estándar;[Rojo]Estándar;[Negro]Estándar



Por último debo decirte que cuando creas un formato personalizado en Excel frecuentemente hacemos varios intentos hasta llegar al formato que deseamos. Cada vez que editamos la cadena de texto del formato personalizado, Excel la agrega a la lista como un nuevo formato disponible. Así que cuando hayas logrado el formato personalizado que necesitas  asegúrate de eliminar aquellos formatos que solamente fueron pruebas y que no necesitarás de nuevo.
En el siguiente artículo mostraré el detalle de los códigos que podemos utilizar en nuestros formatos personalizados.

Sumar valores entre dos fechas

La función SUMAR.SI nos permite sumar valores que cumplen con cierta condición. Si necesitas sumar valores entre dos fechas la condición especificada en la función será de gran relevancia para obtener el resultado deseado.
Supongamos la siguiente tabla de datos:

Quiero sumar los valores que están entre las fechas 4 de enero de 2011 y 10 de enero de 2011. Antes de escribir la fórmula explicaré el razonamiento que hay detrás de ella. El resultado final lo obtendré al realizar una resta de dos sumas, es decir, sumaré todos los valores que sean menores o iguales al 10 de enero de 2011 y a ese resultado le restaré la suma de los valores menores al 4 de enero de 2011. ¿Te confundió mi explicación? Observa la siguiente imagen:

La columna A en azul ilustra las filas que se sumarán con la primera fórmula, es decir, aquellos valores que sean menores o iguales al 10 de enero de 2011. La columna B en rojo son las filas que se sumarán en la segunda operación y que se restarán del primer resultado para obtener el total  deseado el cual está ilustrado por las filas marcadas en verde en la columna B.

La función SUMAR.SI y la función FECHA

La primera fórmula es la siguiente:

=SUMAR.SI(A1:A14,"<="&FECHA(2011,1,10),B1:B14)

La segunda fórmula queda como sigue:

=SUMAR.SI(A1:A14,"<"&FECHA(2011,1,4),B1:B14)

Al hacer la resta de ambas obtengo el resultado deseado:

La función FECHA juega un papel importante en la condición especificada porque a través de ella podemos obtener el valor adecuado para realizar la comparación. Este ejemplo lo dividí en varias celdas (y fórmulas) para poder explicar adecuadamente el procedimiento, sin embargo puedes hacer todo el cálculo en una sola celda.

Último día del mes en Excel

Frecuentemente necesitamos calcular el último día del mes en Excel, ya sea para calcular los días faltantes o para tomar esa fecha como base para algún cálculo. En esta ocasión mostraré dos métodos para obtener dicha fecha.

La función FECHA

El primer método es utilizando la función FECHA la cual permite obtener el número de serie de una fecha determinada. Supongamos que tenemos la fecha 19 de Julio de 2011 y queremos saber el último día del mes. Podemos utilizar la función de la siguiente manera:

=FECHA(2011, 7 + 1, 0)

Pon especial atención al segundo parámetro de la función que indica el mes. Julio es el mes 7 y es por eso que observas el número 7, pero estoy sumando 1 mes adicional lo cual daría el mes 8 (Agosto). La razón de sumar 1 es porque en el tercer parámetro colocaré el valor 0 (cero) y lo explico a continuación.
Al especificar el día cero del mes ocho estaré pidiendo a Excel la fecha 0 de Agosto del 2011, lo cual identificará de inmediato como una fecha inválida, pero en lugar de indicar un error Excel interpreta el día cero como el día anterior al 1 de Agosto del 2011 y por lo tanto me dará como resultado el último día del mes de Julio que es el mes anterior.




La función FIN.MES

El segundo método es mucho más directo que el anterior ya que la función FIN.MES ha sido diseñada específicamente para este fin. Para obtener el último día del mes de una fecha especificada podemos utilizar la función FIN.MES de la siguiente manera:

=FIN.MES(FECHA(2011,7,19), 0)

El primer parámetro de la función FIN.MES es una fecha por lo que vuelvo a utilizar la función FECHA para obtener el valor adecuado a partir del número de año, mes y día. El segundo parámetro es el número de meses que deseas añadir a la fecha especificada. El resultado es el siguiente.

Recuerda que la función devuelve el número de serie de la fecha y Excel no da el formato adecuado de manera predeterminada por lo que necesitarás dar explícitamente el formato de fecha a la celda.

Determinar fechas específicas en Excel

En esta ocasión revisaremos diferentes métodos para determinar ciertas fechas específicas en Excel como determinar el número de día del año, conocer el último día el mes, así como otras fechas que pueden ser de utilidad al trabajar con Excel.

Calcular los días transcurridos en el año

Si la celda B1 contiene la fecha 12 de junio del 2012 y necesito saber los días del año que han transcurrido hasta ese día lo puedo hacer con la siguiente fórmula:

=B1 - FECHA(AÑO(B1),1,0)

Observa como la fórmula devuelve el número 164 que es justamente el número de días transcurridos:


Si deseas calcular el número de días que faltan para el final de año podemos utilizar la siguiente fórmula:

=FECHA(AÑO(B1),12,31) - B1

La fórmula nos devolverá los días que existen entre el fin de año y nuestra fecha:

Por supuesto que si la fecha actual la deseas obtener con la función HOY, entonces la fórmula será la siguiente:

=FECHA(AÑO(HOY()), 12, 31) - HOY()

Determinar la fecha del domingo pasado

Para conocer la fecha del domingo anterior a la fecha proporcionada en la celda B1 podemos utilizar la siguiente fórmula:

=B1 - RESIDUO(B1 - 1, 7)

Observa el resultado de la fórmula para una fecha determinada:


Es importante mencionar que el resultado de la fecha será un valor numérico y deberás aplicar el formato de fecha correspondiente. Esta misma fórmula funciona para encontrar otros días de la semana con tan solo cambiar el número que es restado en el primer argumento de la función RESIDUO.
Para este ejemplo se hizo la resta del número 1 para obtener la fecha del domingo anterior, pero si deseo obtener la fecha del lunes anterior debo utilizar el número 2:

=B1 - RESIDUO(B1 - 2, 7)

Para obtener la fecha del martes anterior debo restar el número 3 y así sucesivamente hasta llegar al número 7 que obtendrá la fecha del sábado anterior.

Determinar el último día del mes

Para obtener el último día del mes podemos utilizar la función FECHA pero con un pequeño truco que será incrementar el número del mes en 1 y utilizar el día 0 (cero). El día “cero” del mes siguiente será el último día del mes actual.

=FECHA(AÑO(B1), MES(B1) + 1, 0)


Observa cómo se calcula el último día del mes en base a una fecha especificada en la celda B1.





Haciendo una pequeña variación a esta fórmula podemos obtener el número total de días del mes en cuestión ya que hemos obtenido el último día del mes podemos saber el número de días de un mes con la ayuda de la función DIA:

=DIA(FECHA(AÑO(B1), MES(B1) + 1, 0))

Para mostrar el resultado adecuadamente debemos dar un formato de celda de número:


Determinar una fecha de vencimiento en Excel

En esta ocasión hablaremos sobre las fechas de vencimiento en Excel y para eso utilizaremos como ejemplo un rango de datos donde la primera columna contiene la fecha de inicio de  un contrato y la segunda columna contiene su duración. Ahora necesito una tercera columna que me permita calcular la fecha de vencimientodel contrato.

Duración en días


Reacciones:

0 comentarios:

Publicar un comentario

suscribe te a los boletines de la web

Enter your email address:

Delivered by FeedBurner