01 unidad i - competencia aplicada

COMPUTACIÓN APLICADA

Unidad I: Introduccio n

La asignatura Computación Aplicada permite al alumno desarrollar las habilidades fundamentales para la operación manual de una planilla electrónica.

Apunte recopilado por:

Aníbal Garay Salcedo

Ingeniero en Gestión Informática,

en base a los textos señalados en la

Bibliografía.

APUNTES DE COMPUTACION APLICADA 2015

2


3

Descripción de la Asignatura:

La asignatura Computación Aplicada I permite al alumno desarrollar las habilidades fundamentales para la operación manual de una planilla electrónica.

Objetivos Generales:

Al aprobar la asignatura, el alumno estará en condiciones de: realizar operaciones de cálculo en una planilla electrónica, además de interrelacionar, ordenar y presentar en forma de gráfico los resultados obtenidos.

Unidades de Aprendizaje:

UNIDAD

HORAS

1 INTRODUCCIÓN A LAS PLANILLAS DE CÁLCULO 8

2 MANEJO DE FÓRMULAS Y FUNCIONES 36

3 VOLUMENES DE DATOS 20

EVALUACIÓN 8


4

Unidad 1: Introducción a la Planilla de Cálculo

En esta Unidad, se examinará el programa de hoja electrónica de cálculo Microsoft Excel 2010. Esta aplicación le permite organizar datos en filas y columnas, completar cálculos, tomar decisiones, crear gráficos, desarrollar informes y publicar datos en la Web.

¿Qué es Excel?

Excel es un programa que nos permite manipular datos numéricos. Con este software podemos crear tablas, realizar cálculos y analizar la información. A este tipo de software se le conoce como hoja de cálculo.

Excel y Office

Excel es un software creado por la empresa Microsoft y forma parte de un producto llamado Office el cual incluye otra serie de productos como Word y PowerPoint. Por esta razón encontrarás frecuentemente que Excel también es conocido como Microsoft Office Excel o simplemente Microsoft Excel.

Versiones de Excel

Me he encontrado con que algunas personas no están seguras de la versión de Excel que están utilizando. Esto sucede cuando eres un usuario nuevo de Excel, pero no te preocupes ya que descubrir la versión es muy sencillo.

En primer lugar tenemos la ventana “splash” que es la que se muestra justamente al iniciar Excel pero que en ocasiones desaparece tan rápido que no alcanzamos a reconocer la versión de Excel que tenemos en nuestro equipo.

Otra manera de descubrirlo pulsar la tecla F1 lo cual abrirá la ventana de ayuda y podremos identificar la versión de Excel para la cual se está mostrando dicha ayuda.


5

Una tercera opción es ir a la ficha Archivo y seleccionar la opción Ayuda donde podrás observar todos los detalles de la versión de Excel.

Es importante conocer la versión de Excel que estás utilizando porque si tienes una versión actual y quieres compartir la información con otra persona pero tu archivo hace uso de funcionalidades exclusivas de las últimas versiones de Excel, entonces la otra persona podría tener dificultades para trabajar con el archivo. O peor aún, si has guardado el archivo en el formato de las versiones 2007 o 2010 entonces una persona con una versión de Excel 2003 (o anterior) no podrá siquiera abrir el archivo.

Historia de las versiones de Excel

Para tener una mejor idea de las versiones de Excel existentes en el mercado mencionaré todas las versiones del software y su año de lanzamiento.

Versión 1 – 1985. La primera versión de Excel fue creada para ser utilizada en la plataforma Macintosh de Apple.

Versión 2 – 1987. En este año se acababa de lanzar el sistema operativo Windows por lo tanto fue la primera versión de Excel para Windows sin


6

embargo fue etiquetada como la versión 2 para dar continuidad con la versión creada previamente para Mac.

Versión 3 – 1990. Para este año el sistema operativo Windows ya tenía una amplia aceptación en todo el mundo y Excel era el software estrella de Microsoft. En esta versión se introducen los gráficos 3-D.

Versión 4 – 1992. Durante más de dos años no hubo competencia para Excel en la plataforma Windows y se aprovechó para mejorar la herramienta.

Versión 5 – 1993. En esta versión finalmente Excel permite crear múltiples hojas dentro de un libro y agrega el soporte para el lenguaje de programación VBA.

Versión 7 [Excel 95] – 1995. Se omite la versión 6 de Excel para empatar todos los productos de Microsoft Office [Word, Excel y PowerPoint] y a partir de esta versión se comienza a conocer a Excel por el año de su lanzamiento. Excel 95 es la primera versión de la hoja de cálculo en correr en un sistema operativo de 32-bits (como Windows 95).

Versión 8 [Excel 97] – 1997. En Excel 97 se introduce el formato condicional y la validación de datos. Se realizan mejoras para los programadores de VBA al incluir un nuevo Editor de VBA e introducir los módulos de clase y los formularios de usuario.

Versión 9 [Excel 2000] – 1999. Se agrega el soporte para complementos COM y varias mejoras para las tablas dinámicas.

Versión 10 [Excel XP] – 2001. Existen mejoras poco significativas pero se prepara esta versión para formar parte de Office XP y ser lanzado en conjunto con Windows XP.

Versión 11 [Excel 2003] – 2003. Esta versión ofrece un soporte mejorado para XML y correcciones en algunas funciones estadísticas.

Versión 12 [Excel 2007] – 2007. Un cambio significativo para la herramienta ya que se introduce un nuevo formato de archivo, una nueva interfaz de usuario que incluye la cinta de opciones. También se aumenta la capacidad de una hoja de cálculo al permitir muchas más filas y columnas.

Versión 14 [Excel 2010] – 2010. Un tanto supersticioso, Microsoft omite la versión 13 y sigue con la versión 14 también conocida como Excel 2010. En esta versión se introducen los minigráficos, la vista Backstage y la segmentación de datos.

Versión 15 [Excel 2013] – 2013. La última versión de la herramienta introduce nuevas características como el relleno rápido de columnas que reconoce ciertos patrones en nuestros datos y hace sugerencias inmediatas. La nueva versión de Excel también analiza los datos para hacer sugerencias en cuanto a los gráficos a crear e inclusive nos hace sugerencias al momento de crear una tabla dinámica, además se introducen nuevas funciones en Excel 2013.


7

Componentes de la hoja de trabajo

Conceptos Básicos

Todo el trabajo que se realiza en Excel se guarda en un archivo, el cual también es conocido como libro y aquí es donde viene el concepto más importante: Cada libro de Excel está formado por una o más hojas y cada hoja a su vez está dividida en múltiples celdas.

Muchos usuarios nuevos de Excel se sienten intimidados al principio por estos términos, pero es de mucha importancia que los comprendas y que se familiaricen con ellos.


8

Celdas de una hoja

Finalmente observa que la parte central de la pantalla es una cuadrícula que forma pequeños recuadros. Cada uno de estos recuadros es conocido como celda. Por definición una celda es la intersección entre una columna y una fila, y se nombra por el nombre de la columna y la fila, ejemplo: celda b5, sería la intersección de la columna “b” y la fila “5”

Para moverte entre dichas celdas puedes utilizar las flechas del teclado ya sea para moverte hacia arriba o para moverte hacia abajo, hacia la derecha o hacia la izquierda. Así que no olvides este concepto básico de Excel: Un libro está formado por hojas y cada hoja está dividida en celdas.

Rango: Conjunto que forman una serie de celdas contiguas.


9

Salto de línea en una celda de Excel

Este es un consejo muy sencillo pero inmensamente útil cuando deseas insertar un salto de línea en una celda de Excel. Lo único que debes hacer es utilizar la combinación de teclas ALT + Entrar para insertar el salto de línea.

En la siguiente imagen puedes observar que comienzo por introducir el texto Primera línea seguido de un salto de línea el cual es introducido con la combinación de teclas ALT + Entrar y posteriormente el texto Segunda línea para terminar pulsando la tecla Entrar:

De esta manera puedes introducir tantos saltos de línea como necesites dentro de una misma celda.

Especificaciones y límites de las hojas de cálculo y de los libros

CARACTERÍSTICA LÍMITE MÁXIMO

Libros abiertos En función de la memoria disponible y los recursos del sistema

Tamaño de hoja 1.048.576 filas por 16.384 columnas

Ancho de columna 255 caracteres

Alto de fila 409 puntos

Saltos de página 1.026 horizontal y vertical

Número total de caracteres que

puede contener una celda

32.767 caracteres

Caracteres en un encabezado o

un pie de página

255


10

Hojas en un libro En función de la memoria disponible (el número

predeterminado es 3)

Colores en un libro 16 millones de colores (32 bits con acceso completo al espectro

de colores de 24 bits)

Vistas con nombre en un libro En función de la memoria disponible

Formatos o estilos de celdas

distintos

64.000

Estilos de relleno 32

Grosor y estilos de línea 16

Tipos de fuentes distintas 1.024 fuentes globales disponibles; 512 para cada libro

Formatos de número en un

libro

Entre 200 y 250, según el idioma de la versión de Excel

instalada

Escala de zoom del 10 % al 400 %

Funciones personalizadas En función de la memoria disponible

Criterios de ordenación 64 combinados en una única operación; ilimitado en

operaciones de ordenación secuenciales

Niveles de deshacer 100

Campos en un formulario 32

Especificaciones y límites de los cálculos


Precisión numérica 15 dígitos

Número negativo más bajo permitido -2,2251E-308

Número positivo más bajo permitido 2.2251E-308

Número positivo más alto permitido 9,99999999999999E+307

Número negativo más alto permitido -9.99999999999999E+307

Número positivo más alto permitido mediante fórmula

1.7976931348623158e+308

Número negativo más alto permitido mediante fórmula

-1.7976931348623158e+308

Longitud del contenido de una fórmula 8.192 caracteres

Longitud interna de la fórmula 16.384 bytes

Iteraciones 32.767

Matrices en una hoja En función de la memoria disponible

Rangos seleccionados 2.048

Argumentos en una función 255


11

Niveles anidados de funciones 64

Categorías de funciones definidas por el usuario

255

Número de funciones de hoja de cálculo disponibles

341

Tamaño de la pila de operandos 1.024

Dependencia entre hojas de cálculo 64.000 hojas de cálculo pueden hacer referencia a otras hojas

Dependencia de fórmulas de matriz entre hojas de cálculo

En función de la memoria disponible

Dependencia de áreas En función de la memoria disponible

Dependencia de áreas en cada hoja de cálculo


Dependencia en una sola celda 4.000 mil millones de fórmulas pueden depender de una sola celda

Longitud del contenido de celdas vinculadas de libros cerrados

32.767

Primera fecha permitida en un cálculo 1 de enero de 1900 (1 de enero de 1904, si se utiliza la fecha del sistema)

Última fecha permitida en un cálculo 31 de diciembre de 9999

Período de tiempo máximo que puede escribirse

9999:59:59

Especificaciones y límites de los gráficos


Gráficos vinculados a una hoja En función de la memoria disponible

Hojas a las que hace referencia un gráfico 255

Series de datos en un gráfico 255

Puntos de datos en una serie de datos para

gráficos 2D

32.000

Puntos de datos en una serie de datos para

gráficos 3D

4.000

Puntos de datos en todas las series de un

gráfico

256.000

Especificaciones y límites de los informes de tablas dinámicas y gráficos

dinámicos


Informes de tabla dinámica en una hoja En función de la memoria disponible


12

Número de elementos únicos por cada campo 1.048.576

Campos de fila o columna en un informe de tabla

dinámica


Filtros de informe en un informe de tabla dinámica 256 (puede estar en función de la

memoria disponible)

Campos de valores en un informe de tabla dinámica 256

Fórmulas de elementos calculados en un informe de

tabla dinámica


Filtros de informe en un informe de gráfico dinámico 256 (puede estar en función de la

memoria disponible)

Campos de valores en un informe de gráfico

dinámico

256

Fórmulas de elementos calculados en un informe de

gráfico dinámico


Longitud del nombre MDX para un elemento de tabla

dinámica

32.767

Longitud de una cadena de tabla dinámica relacional 32.767

La hoja de cálculo

La característica principal de Excel es que la pantalla principal muestra una matriz de dos dimensiones, es decir, 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 está conformada por la columna y la fila a la que pertenece. 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


13

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.

La cinta de opciones

A partir de Excel 2007 se introdujo un nuevo elemento a la interfaz de usuario de Excel y que es conocido como la cinta de opciones la cual nos da una nueva manera de organizar los comandos dentro del programa.

La cinta de opciones viene a reemplazar a los menús y barras de herramientas que se utilizaban anteriormente. Dentro de la cinta de opciones cada uno de los comandos pertenece a un grupo y que a su vez pertenece a una ficha.

Las fichas de Excel 2010

Las fichas que se muestran de manera predeterminada en la cinta de opciones son las siguientes:

1. La ficha Inicio tiene comandos que utilizamos frecuentemente, como Copiar y Pegar. También podemos dar formato a las fuentes, a los textos y a los números.

2. La ficha Insertar contiene comandos que permiten agregar algunos objetos a las hojas de trabajo, como las tablas, las imágenes y los gráficos.

3. La ficha Diseño de página nos permite seleccionar algunos temas para nuestras hojas de trabajo. También podemos configurar la página y prepararla para su impresión.

4. La ficha Fórmulas contiene la biblioteca de funciones que organiza adecuadamente todas las fórmulas de Excel y nos permite utilizarlas fácilmente para realizar cálculos.

5. La ficha Datos nos permite importar datos que provienen de fuentes externas como Access y archivos de texto. Así mismo podemos encontrar comandos que nos ayudan a ordenar y filtrar nuestros datos.


14

6. La ficha Revisar nos muestra comandos para verificar la ortografía de nuestros documentos y también nos permite insertar comentarios en las hojas de cálculo.

7. La ficha Vista contiene comandos para cambiar la apariencia de los datos en pantalla y nos permite hacer acercamientos para tener una mejor visualización.

Indicadores de cuadro de diálogo en Excel

Los indicadores de cuadro de diálogo en Excel, también conocidos como selectores de cuadro de diálogo, son un elemento de la interfaz de Excel que nos ayudan a mostrar comandos relacionados a un grupo de la cinta de opciones.

Ejemplos de indicadores de cuadro de diálogo

Los indicadores de cuadro de diálogo son los pequeños iconos que se muestran en la esquina inferior derecha de algunos grupos de comandos de la cinta de opciones. Por ejemplo, en la siguiente imagen puedes observar el indicador de cuadro de diálogo del grupo Número en la ficha Inicio.

Al colocar el puntero del ratón sobre el indicador de cuadro de diálogo (sin hacer clic) se mostrará una descripción de lo que sucederá al pulsar dicho indicador. Otro ejemplo de indicador de cuadro de diálogo lo encontramos en la ficha Insertar, dentro del grupo Gráficos.


15

Al pulsar en indicador de cuadro de diálogo anterior, se mostrará el cuadro de diálogo Insertar gráfico el cual muestra todas las opciones de gráficos de Excel que podemos utilizar.

Cada indicador de cuadro de diálogo mostrará un cuadro de diálogo que contendrá los comandos de Excel relacionados con su grupo. El nombre en inglés de los indicadores de cuadro de diálogo es Dialog Box Launchers.

¿Qué es la vista Backstage?

La vista Backstage de Excel es el lugar donde se gestionan los archivos y sus datos, es decir, es el lugar en Excel donde tienes acceso a las características del archivo y a las cosas que puedes hacer con él como guardarlo, cerrarlo, abrir uno nuevo, etcétera.

Para los usuarios de Excel 2007 la noticia es que la vista Backstage reemplaza al botón de Office y para aquellos que aún utilizaban la versión 2003 o anterior, la vista Backstage reemplaza al menú Archivo.

¿Dónde está la vista Backstage?

La vista Backstage se encuentra ubicada en la Ficha Archivo (de color verde), es decir, hacer clic en esta ficha equivale a abrir la vista Backstage. Es


16

importante que te familiarices con este término porque frecuentemente lo encontrarás en la literatura de Excel y de Office 2010 en general.

Si en este momento tienes un equipo con Excel 2010 instalado haz la prueba ahora mismo. Si no tienes acceso en este momento aquí te muestro una imagen de la vista Backstage.

La ficha Archivo en Excel 2010

La próxima vez que leas algo referente a la vista Backstage recuerda que es equivalente a hacer clic sobre la ficha Archivo que se encuentra junto a las fichas predeterminadas de Excel 2010.

Operaciones aritméticas

Excel nos permite realizar cálculos aritméticos con nuestros datos numéricos como la suma, la resta, la multiplicación y la división. La única condición es que debemos colocar un signo igual (=) precediendo a la fórmula de manera que Excel efectúe el cálculo correspondiente.


17

Funciones de Excel

Aunque podemos utilizar los operadores aritméticos para realizar muchos de nuestros cálculos, Excel provee de funciones las cuales son: procedimientos que nos ayudan a efectuar cálculos sobre nuestros datos. Podemos pensar en las funciones como si fueran nuestras asistentes de Excel a quienes les pasamos algunos datos numéricos y ellas se encargarán de realizar los cálculos y entregarnos el resultado correcto.

Un ejemplo es la función PROMEDIO a quien puedo pasarle una serie de números y me regresará el promedio de todos ellos.

Los formatos de archivo de Excel 2010

Por muchos años el formato de archivo de Excel fue el formato XLS, sin embargo a partir de la versión de Excel 2007 se introdujeron nuevos formatos que seguimos utilizando en Excel 2010.

A continuación enumero los nuevos formatos de archivo de Excel:

XLSX: Un libro de Excel que no contiene macros. XLSM: Un libro de Excel que contiene macros. XLTX: Una plantilla de Excel que no contiene macros. XLTM: Una plantilla de Excel que contiene macros. XLAM: Complemento de Excel habilitado para macros. XLSB: Archivo binario de Excel

Todos los archivos antes mencionados, a excepción del formato XLSB, tienen el formato open XML, el cual permite que otras aplicaciones abran fácilmente este tipo de archivos.

Explorando el formato open XML

Si tienes curiosidad de saber cómo está organizado en su interior un archivo de Excel puedes hacer la siguiente prueba. Cambia la extensión de cualquier archivo XLSX por ZIP. Esto permitirá abrir el archivo con la herramienta de descompresión de Windows. Al abrirlo observarás 3 carpetas:


18

Dentro de la carpeta xl encontrarás otra carpeta llamada worksheets la cual contendrá un documento XML para cada una de las hojas del libro. De esta manera los nuevos formatos de archivo de Excel cumplen con el estándar open XML que permite que otras aplicaciones puedan leerlos o inclusive crear archivos compatibles con Excel.

El módulo de compatibilidad de Office

Aquellos que tienen una versión de Excel anterior a 2007 no pueden abrir los nuevos formatos de archivo de Excel porque no pueden interpretar el formato open XML. Sin embargo, Microsoft creó el módulo de compatibilidad para Office XP y Office 2003.

Al instalar este módulo en Excel XP o Excel 2003 podrás abrir archivos que hayan sido creados con Excel 2007 o Excel 2010. Si aún utilizas alguna de estas versiones anteriores de Excel, puedes descargar el módulo de compatibilidad desde la siguiente página:

http://www.microsoft.com/es-es/download/details.aspx?id=3

Guardar un archivo como una versión anterior

Otra alternativa para intercambiar información con otra persona es guardar nuestro archivo como una versión anterior de Excel. Esta selección la hacemos en el cuadro de diálogo Guardar como que nos permitirá elegir alguna de las siguientes opciones:

Libro de Excel 97-2003: Utilizar esta opción si el archivo será abierto en Excel 97, Excel 2000, Excel XP o Excel 2003.

Libro de Microsoft Excel 5.0/95: Utilizar esta opción si el archivo será abierto en Excel 5 o Excel 95.

Cuando guardes el archivo se mostrará el Comprobador de compatibilidad el cual nos reportará cualquier problema potencial que exista con las versiones anteriores de Excel.

http://www.microsoft.com/es-es/download/details.aspx?id=3


19

Áreas de trabajo en Excel

Este consejo es para aquellos que utilizan Excel con múltiples libros abiertos al mismo tiempo y que todos los días toman tiempo para abrir todos los archivos para continuar con su trabajo. La solución está en crear un área de trabajo en Excel.

El primer paso es abrir todos los archivos que utilizas cotidianamente y arreglar cada una de las ventanas de la manera que mejor te acomode:

Una vez que tienes las ventanas en las posición adecuada debes ir a la ficha Vista y dentro del grupo Ventana pulsar el botón Guardar área de trabajo. Excel mostrará el cuadro de diálogo Guardar área de trabajo y solo deberás proporcionar un nombre antes de pulsar el botón Guardar.

El archivo se guardará con la extensión XLW la cual corresponde a un área de trabajo en Excel. Para volver a utilizar el área de trabajo debes ir a Archivo > Abrir y dentro del cuadro de diálogo Abrir seleccionar la opción Área de trabajo.


20

Un área de trabajo en Excel solamente contiene el nombre de los libros que abrirá y su posición dentro de la ventana pero no los datos de cada uno de los libros así que, guardar un área de trabajo no significa que se han guardado los cambios en cada uno de los libros.

Atajos de teclado en Excel

Si eres de las personas que ingresa mucha información con el teclado, entonces es indispensable que conozcas los atajos de teclado en Excel ya que mejorarás tu productividad al acceder mucho más rápido a los comandos de la herramienta desde el teclado.

¿Qué es un atajo de teclado?

Un atajo de teclado es una tecla o un grupo de teclas que debe pulsarse al mismo tiempo para ejecutar un comando específico. Los atajos de teclado en Excel son ampliamente utilizados porque ayudan a los usuarios a mejorar su productividad evitando levantar continuamente la mano para utilizar el ratón ahorrando así segundos que pueden llegar a convertirse en minutos y horas.

Teclas importantes para los atajos

En la gran mayoría de los casos, los atajos de teclado involucrarán el uso de las teclas Ctrl, Alt y Mayús. Las teclas Ctrl y Alt siempre están identificadas por su nombre, y la tecla Mayús está generalmente identificada con una flecha hacia arriba la cual también es conocida por su nombre en inglés: Shift. En la siguiente imagen podrás observar la ubicación de las teclas mencionadas:

Nomenclatura de los atajos

Ya que un atajo de teclado puede referirse a la combinación de varias teclas, notarás que en su nomenclatura se usa el símbolo + que nos sirve para indicar que deben pulsarse varias teclas al mismo tiempo, por ejemplo:

Ctrl+C

Este atajo de teclado indica que debemos pulsar la tecla Ctrl, y sin soltarla, pulsar la tecla C. El símbolo + no indica que debemos pulsar la tecla + sino que está presente para recordarnos que debemos mantener pulsadas ambas teclas. A continuación encontrarás el listado de atajos de Excel organizados por el tipo de


21

teclas pulsadas o de acuerdo a su funcionalidad. Es muy importante recordar que todos los atajos mostrados han sido probados en versiones de Excel en español.

Teclas de función en Excel

F1 Muestra la ayuda de Excel F2 Entra en modo de edición para la celda activa F3 En caso de existir un nombre definido, muestra el cuadro de diálogo

Pegar nombre. F4 Repite la última acción. Además, al escribir una fórmula permite cambiar

entre referencias relativas, absolutas y mixtas. F5 Muestra el cuadro de diálogo Ir a. F6 Moverse entre los paneles de un libro dividido. F7 Muestra el cuadro de diálogo Ortografía. F8 Activa el modo Ampliar selección que permite aumentar el rango

seleccionado utilizando las teclas de dirección. F9 Calcula el resultado de las fórmulas de todas las hojas de los libros

abiertos. F10 Activa la barra de menús. F11 Crea una hoja de gráfico con el rango de celdas seleccionado. F12 Muestra el cuadro de diálogo Guardar como.

Atajos de una sola tecla

Alt Activa la barra de menús. Avpág Desplazarse una pantalla abajo dentro de la hoja. Entrar Completa la entrada de una celda y selecciona la celda inferior. Esc Cancela la entrada de una celda. También cierra cualquier cuadro de

diálogo mostrado. Espacio Activa o desactiva una casilla de verificación dentro de un cuadro

de diálogo. Fin Activa o desactiva el Modo final. Al estar en Modo final se pueden

utilizar las teclas de dirección para moverse hacia la última celda del rango actual.

Inicio Moverse al inicio de la fila. Repág Desplazarse una pantalla arriba dentro de la hoja. Retroceso Elimina el contenido de una celda y entra en Modo de edición. Suprimir Elimina el contenido de una celda Tabulador Completa la entrada de una celda y selecciona la celda a la

derecha. Teclas de dirección Selecciona la celda superior, inferior, izquierda o

derecha de acuerdo a la tecla de dirección pulsada.

Atajos de teclado básicos en Excel

Ctrl+A Muestra el cuadro de diálogo Abrir. Ctrl+B Muestra el cuadro de diálogo Buscar. Ctrl+C Copia las celdas seleccionadas. Ctrl+D Copia una fórmula hacia la derecha sobre el rango seleccionado. Ctrl+E Selecciona todas las celdas de la hoja actual. Ctrl+G Guarda el libro de trabajo. Ctrl+I Muestra el cuadro de diálogo Ir a. Ctrl+J Copia una fórmula hacia abajo sobre el rango seleccionado. Ctrl+K Aplica formato de cursiva al texto seleccionado. Ctrl+L Muestra el cuadro de diálogo Reemplazar. Ctrl+N Aplica formato de negrita al texto seleccionado.


22

Ctrl+P Muestra el cuadro de diálogo Imprimir. Ctrl+R Cierra el libro de trabajo. Ctrl+S Subraya el texto seleccionado. Ctrl+T Muestra el cuadro de diálogo Crear tabla. Ctrl+U Nuevo libro de trabajo. Ctrl+V Pega el contenido del portapapeles. Ctrl+X Corta las celdas seleccionadas. Ctrl+Y Rehace la última acción deshecha. Ctrl+Z Deshace la última acción. Ctrl+1 Muestra el cuadro de diálogo Formato de celdas. Ctrl+2 Aplica formato de negrita al texto seleccionado. Ctrl+3 Aplica formato de cursiva al texto seleccionado. Ctrl+4 Subraya el texto seleccionado. Ctrl+5 Aplica el efecto de tachado al texto. Ctrl+6 Oculta o muestra los objetos de la hoja. Ctrl+8 Muestra los símbolos de esquema en la hoja. Ctrl+9 Oculta las filas seleccionadas. Ctrl+0 Oculta las columnas seleccionadas.

Atajos con Ctrl y teclas de función

Ctrl+F1 Oculta o muestra la Cinta de opciones. Ctrl+F2 Muestra el cuadro de diálogo Imprimir. Ctrl+F3 Muestra el Administrador de nombres. Ctrl+F4 Cierra la ventana del libro actual. Ctrl+F5 Restaura el tamaño de la ventana del libro actual. Ctrl+F6 Moverse al libro abierto siguiente. Ctrl+F7 Permite mover la ventana del libro cuando la ventana no está

maximizada. Ctrl+F8 Permite cambiar el tamaño de la ventana del libro cuando la

ventana no está maximizada. Ctrl+F9 Minimiza la ventana del libro actual. Ctrl+F10 Maximiza la ventana de un libro minimizado. Ctrl+F11 Inserta una hoja de Macros de Microsoft Excel 4.0 Ctrl+F12 Muestra el cuadro de diálogo Abrir.

Atajos con Mayús y teclas de función

Mayús+F2 Agrega o edita un comentario de celda. Mayús+F3 Muestra el cuadro de diálogo Insertar función. Al editar una

fórmula muestra el cuadro de diálogo Argumentos de función. Mayús+F4 Ejecuta el comando “Buscar siguiente” de acuerdo a los

términos de búsqueda indicados previamente. Mayús+F5 Muestra el cuadro de diálogo Buscar. Mayús+F6 Cambia el foco entre la hoja, la cinta de opciones, la barra de

estado y el panel de tareas (si está presente). Mayús+F7 Muestra el panel de tareas Referencia. Mayús+F8 Permite agregar otro rango de celdas a la selección. Mayús+F9 Calcula las fórmulas de la hoja actual. Mayús+F10 Muestra el menú de clic derecho para la selección. Mayús+F11 Inserta una nueva hoja. Mayús+F12 Muestra el cuadro de diálogo Guardar como.

Atajos con Alt y teclas de función

Alt+F1 Inserta un gráfico en la hoja actual.


23

Alt+F2 Muestra el cuadro de diálogo Guardar como. Alt+F4 Cierra Excel. Alt+F8 Abre el cuadro de diálogo Macro. Alt+F10 Muestra el panel “Selección y visibilidad” para formas. Alt+F11 Abre el Editor de Visual Basic para Aplicaciones.

Atajos con las teclas Ctrl+Mayús

Ctrl+Mayús+F3 Muestra el cuadro de diálogo Crear nombres a partir de la selección.

Ctrl+Mayús+F6 Moverse al libro abierto anterior. Ctrl+Mayús+F10 Activa la barra de menú. Ctrl+Mayús+F12 Muestra el cuadro de diálogo Imprimir. Ctrl+Mayús+F Muestra la pestaña Fuente del cuadro de diálogo Formato

de celdas. Ctrl+Mayús+L Activa o desactiva los filtros en un rango. Ctrl+Mayús+O Selecciona las celdas con comentarios. Ctrl+Mayús+U Expande la barra de fórmulas. Ctrl+Mayús+Entrar Ingresa una fórmula como una fórmula matricial. Ctrl+Mayús+Inicio Extiende la selección hasta el inicio de la hoja. Ctrl+Mayús+Fin Extiende la selección hasta la última celda utilizada en la

hoja. Ctrl+Mayús+Avpág Agrega la siguiente hoja a la selección de hojas. Ctrl+Mayús+Repág Agrega la hoja previa a la selección de hojas. Ctrl+Mayús+Tecla dirección Extiende la selección a la última celda no

vacía en la misma dirección de la tecla pulsada. Ctrl+Mayús+Espacio Selecciona el rango de celdas actual o la hoja

completa. Ctrl+Mayús+( Muestra las filas ocultas dentro del rango seleccionado. Ctrl+Mayús+) Muestra las columnas ocultas dentro del rango seleccionado. Ctrl+Mayús+! Aplica el formato Número con dos decimales. Ctrl+Mayús+# Aplica el formato Fecha en la forma dd-mmm-aa. Ctrl+Mayús+$ Aplica el formato Moneda con dos decimales. Ctrl+Mayús+% Aplica el formato Porcentaje sin decimales. Ctrl+Mayús+/ Aplica el formato de notación Científica. Ctrl+Mayús+: Aplica el formato de Hora. Ctrl+Mayús+& Aplica un borde a la celda. Ctrl+Mayús+- Remueve los bordes de la celda. Ctrl+Máyus+” (doble comilla) Copia el contenido de la celda superior.

Atajos de teclado para moverse en Excel

Alt+Avpág Moverse una pantalla a la derecha en la hoja. Alt+Repág Moverse una pantalla a la izquierda en la hoja. Ctrl+. Moverse a la siguiente esquina de un rango seleccionado. Ctrl+Avpág Moverse a la hoja siguiente. Ctrl+Repág Moverse a la hoja anterior. Ctrl+Inicio Moverse a la celda A1 o a la celda superior izquierda visible en

la hoja. Ctrl+Fin Moverse a la última celda utilizada del rango actual. Ctrl+Retroceso Desplaza la pantalla para mostrar la celda activa. Ctrl+Tabulador Moverse al siguiente libro abierto. Ctrl+Tecla dirección Moverse al extremo de la fila o columna actual de

acuerdo a la tecla de dirección pulsada.


24

Atajos de teclado para seleccionar datos

Ctrl+* Selecciona la región actual de celdas con datos. Ctrl+Espacio Selecciona la columna actual. Mayús+Avpág Extiende la selección hacia abajo por una pantalla. Mayús+Repág Extiende la selección hacia arriba por una pantalla. Mayús+Inicio Extiende la selección hasta el inicio de la fila. Mayús+Espacio Selecciona la fila actual. Mayús+Tecla dirección Extiende la selección una celda en la misma

dirección de la tecla pulsada.

Atajos para ingresar datos y fórmulas

Alt+= Inserta una Autosuma de las celdas adyacentes. Alt+Abajo Despliega las opciones de una lista de validación de datos. Alt+Entrar Inserta un salto de línea dentro de una celda. Ctrl+, (coma) Insertar la fecha actual. Ctrl+: Insertar la hora actual. Ctrl+” (comillas dobles) Copia el valor de la celda superior. Ctrl+’ (comilla simple) Copia la fórmula de la celda superior. Ctrl+Entrar Llena el rango de celdas seleccionado con la entrada actual. Ctrl+Suprimir Borra todo el texto hasta el final de la línea. Ctrl+Alt+K Insertar un hipervínculo. Mayús+Entrar Completa la entrada de celda y selecciona la celda superior. Mayús+Tabulador Completa la entrada de celda y selecciona la celda a la

izquierda.

Otros atajos de teclado en Excel

Alt+Espacio Abre el menú de control de la venta de Excel. Alt+’ (comilla simple) Muestra el cuadro de diálogo Estilo. Alt+Ctrl+Izquierda Se mueve hacia la izquierda entre las celdas no

adyacentes de un rango seleccionado. Alt+Ctrl+Derecha Se mueve hacia la derecha entre las celdas no

adyacentes de un rango seleccionado. Ctrl+- Muestra el cuadro de diálogo Eliminar celdas. Ctrl++ Muestra el cuadro de diálogo Insertar celdas. Ctrl+Alt+V Muestra el cuadro de diálogo Pegado especial.

Comentarios en celdas de Excel

Los comentarios en celdas de Excel nos permiten agregar notas textuales que serán de ayuda a cualquier persona que consulte nuestro libro de trabajo. Realmente es muy fácil agregar un comentario en Excel.


25

Si deseas cambiar la fuente y color de un solo comentario debes hacer clic derecho sobre el borde y seleccionar la opción Formato de comentario:

Se mostrará el cuadro de diálogo Formato de comentario y podrás cambiar el tipo de fuente y su tamaño desde la sección Fuente y para cambiar el color de fondo deberás ir a la sección Colores y líneas.

Cambiar el nombre de usuario del comentario

Todos los comentarios en celdas de Excel se insertan precedidos por el nombre del usuario que está realizando el comentario. Si deseas modificar este nombre debes ir a la ficha Archivo > Opciones > General y deberás editar el campo Nombre de usuario:


26

Es importante mencionar que este cambio afectará la configuración de Nombre de usuario para todas las aplicaciones de Office.

Copiar los comentarios a otras celdas

Si necesitas copiar los comentarios a otras celdas puedes utilizar la función de Pegado especial de Excel. El primer paso es seleccionar el rango de celdas que contiene los comentarios que deseas copiar y al momento de copiarlos selecciona la opción Pegado especial del botón Pegar en la ficha Inicio:

Agregar una imagen a un comentario

Como sabemos, los comentarios en celdas de Excel solo aceptan texto, pero es posible desplegar una imagen dentro del comentario. Ya que esta técnica implica colocar una imagen como el “fondo” del comentario, es muy recomendable eliminar todo el texto antes de empezar.

Una vez que tenemos un comentario sin texto, debes hacer clic derecho sobre el borde del comentario y seleccionar la opción Formato de comentario. Dentro de la sección Colores y líneas abre la lista de color de relleno y selecciona la opción Efectos de relleno.


27

Se mostrará un nuevo cuadro de diálogo que tendrá una sección llamada Imagen donde podrás seleccionar cualquier archivo de imagen que se encuentre en tu equipo. Al aceptar los cambios tendremos un comentario con una imagen de fondo:

El administrador de nombres de rangos

Un nombre en Excel es una abreviación que tendrá un significado especial dentro de nuestro libro ya que lo utilizaremos para hacer referencia a una celda, a un rango de celdas o a una tabla.

Tipos de nombres en Excel

Existen los nombres definidos para celdas o rangos de celdas y que pueden contener tanto constantes como fórmulas. Es decir, una celda que contiene una constante puede tener asignado un nombre así como una celda que contenga una fórmula puede también tener asignado un nombre.

El otro tipo de nombres que existen en Excel son los nombres de tablas. Cuando creamos una tabla, Excel le asigna un nombre predeterminado como Tabla1, Tabla2, etc., pero podremos editarlo desde el administrador de nombres para que sea más descriptivo.


28

Cómo crear un nombre en Excel

Para crear un nombre debemos seleccionar primero la celda (o rango de celdas) y posteriormente podemos utilizar dos métodos. El primero es asignar el nombre directamente en el cuadro de nombres de la barra de fórmulas:

El segundo método es utilizar el comando Asignar nombre que se encuentra en la ficha Fórmulas.

El administrador de nombres en Excel

Una vez que se han creado los nombres, ya sea de celdas o de tablas, podremos administrarlos con el Administrador de nombres que se encuentra dentro del grupo Nombres definidos de la ficha Fórmulas. Al pulsar el botón Administrador de nombres se mostrará el siguiente cuadro de diálogo:


29

Este cuadro de diálogo contendrá la lista de todos los nombres de nuestro libro y podremos también crear desde aquí nuevos nombres o editar los existentes. Así mismo podemos eliminar cualquier nombre que deseemos pulsando el botón Eliminar.

Cómo auditar nombres

Si llegas a tener una gran cantidad de nombres en tu libro de Excel y prefieres tener una lista de todos ellos será muy fácil generarla. En primer lugar debes posicionarte en la celda donde se insertará el listado y utilizar el comando Pegar nombres que se encuentra dentro del menú desplegable Utilizar en la fórmula que a su vez pertenece al grupo Nombres definidos de la ficha Fórmulas:

Al mostrarse el cuadro de diálogo Pegar nombres deberás pulsar el botón Pegar lista y Excel insertará en la hoja los nombres de los rangos y su definición:


30

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 #:


31

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:

Combinar texto y números en una celda

Si necesitas combinar texto y números en una misma celda, Excel nos permite hacerlo de tres maneras diferentes: Concatenando los valores,


32

utilizando la función TEXTO o utilizando un formato personalizado. Supongamos la siguiente tabla de datos:

Lo que deseamos hacer es agregar la palabra “días” a cada celda de manera que podamos leer: 76 días, 66 días, 75 días, etc. Podrías pensar que podemos agregar la palabra “días” en la columna de la derecha, pero no es eso lo que buscamos, sino que necesitamos dentro de una misma celda tanto el número como el texto.

Utilizar la concatenación

El método más simple para combinar texto y números en una celda es utilizar la concatenación. Para ello podemos utilizar el carácter “&” que nos ayuda a unir tanto el texto como el número. Observa el ejemplo que utiliza esta técnica en la columna B:

La desventaja de este método es que las celdas ya no pueden ser utilizadas con su valor numérico porque ahora son celdas que contienen un texto.

Utilizar la función TEXTO

Otra alternativa es utilizar la función TEXTO que nos permite convertir un número en texto y darle el formato que necesitamos. Observa la solución en la columna C:


33

El segundo parámetro de la función TEXTO me permite especificar el formato que deseo colocar al valor numérico y es donde puedo aprovechar para incluir la palabra “días”.

Esta alternativa deja el contenido de la celda como un texto, así que también nos vemos imposibilitados a utilizar el valor de la celda en algún cálculo numérico. Sin embargo la idea de trabajar con el formato de la celda es lo que nos lleva a la tercera y última opción que es la que quiero recomendar.

Utilizar el formato personalizado

Si queremos combinar texto y números en una misma celda sin perder la posibilidad de utilizar su valor en un cálculo numérico, entonces la mejor alternativa es aplicar un formato personalizado. El formato que utilizaré es el mismo del ejemplo con la función TEXTO, solo que en esta ocasión colocaré dicho formato personalizado en las propiedades de la celda de la siguiente manera:


34

Una vez que se aplica el formato personalizado a todas las celdas obtenemos el siguiente resultado:

Observa cómo la barra de fórmulas despliega un valor numérico para la celda indicando que sigue siendo un valor numérico a pesar de que en pantalla se muestra un texto dentro de la celda. Ahora observa lo que sucede al hacer una suma con los valores de la columna D:

Así que si deseas combinar texto y números en una sola celda, pero sin dejar de utilizar el valor numérico de una celda, te recomiendo utilizar un formato personalizado.

Cómo limitar el área de trabajo en Excel

En ocasiones necesitamos limitar el área de trabajo en Excel para no permitir que los usuarios visualicen las celdas de nuestra hoja que contienen fórmulas y cálculos indispensables.

Aunque es posible proteger las celdas de un libro de Excel para que no sean modificadas con el comando Proteger hoja de la ficha Revisar, las celdas quedarán visibles al usuario.


35

Con el método que mostraré a continuación podremos limitar el área de trabajo en Excel al rango A1:E15 de manera que las columnas y filas sobrantes sean invisibles y el área de desplazamiento de la hoja esté limitada.

Ocultar columnas y filas excedentes

El primer paso es ocultar las columnas y filas que no deseamos que sean vistas por los usuarios. Para ocultar todas las columnas selecciono la primera de ellas haciendo clic en el encabezado de columna:

Una vez que ha sido seleccionada la columna pulsa la combinación de teclas CTRL + Mayus + Flecha derecha para seleccionar todas las columnas y finalmente haz clic derecho sobre cualquier encabezado de columna y selecciona la opción Ocultar:


36

Para ocultar las filas seguiremos un procedimiento similar. Hago clic sobre el encabezado de la fila 17:

Después pulsamos la combinación de teclas CTRL + Mayus + Flecha abajo para seleccionar todas las filas restantes y de igual manera hacemos clic derecho sobre cualquier encabezado de fila para seleccionar la opción Ocultar.


37

Como resultado tendremos ocultas todas las columnas y filas excedentes a nuestro rango:

Comprobador de compatibilidad

Si necesitas guardar un libro para compartirlo con alguien que utiliza una versión de Excel 2003, o versiones previas, entonces debes guardar el archivo eligiendo la opción Libro de Excel 97-2003 dentro del cuadro de diálogo Guardar como.

Y justamente después de pulsar el botón Guardar se mostrará la ventana del Comprobador de compatibilidad el cual identifica problemas potenciales del libro en caso de ser utilizado por alguien con una versión de Excel 2003 o versiones previas.


38

El comprobador de compatibilidad revisa todo el libro en busca de características que no son soportadas por versiones anteriores de Excel. Algunas de esas características son las siguientes:

Las versiones anteriores a Excel 2003 soportan un máximo de 256 columnas y 65,536 filas por lo que si se exceden estos límites serán reportados.

Los minigráficos no son soportados. Algunos estilos de celdas y de tablas que no son soportados por las

versiones anteriores. Segmentación de datos en tablas dinámicas Los límites de las tablas dinámicas son diferentes, en versiones previas

solo se soportan hasta 32,500 elementos por cada campo mientras que en Excel 2010 se soportan hasta 1,046,576.

Estos son solamente algunos ejemplos de una amplia lista de características de Excel 2010 que no son soportadas por versiones igual o previa a Excel 2003. Sin embargo el comprobador de compatibilidad nos dará un reporte detallado de todas las incompatibilidades.

Pérdida menor de fidelidad

Cuando en el Resumen del comprobador de compatibilidad observas un mensaje diciendo Pérdida menor de fidelidad puedes continuar guardando el archivo sin mucha preocupación ya que lo único que sucederá es que Excel removerá aquellas funcionalidades que no son compatibles pero aun así los datos serán legibles en el nuevo archivo guardado.

Pérdida significativa de funcionalidad

Sin embargo, si observas un mensaje diciendo Pérdida significativa de funcionalidad, entonces no es una buena idea guardar el archivo porque se perderán datos o ciertas fórmulas dejarán de funcionar y en general se perderá la funcionalidad del libro.


39

Es probable que para algunos casos exista alguna alternativa, en cuyo caso se puede hacer clic sobre la liga de ayuda que se proporciona en el reporte para conocer alguna solución alterna. Sin embargo en algunos casos no existirá alternativa más que modificar el archivo original para poder lograr la compatibilidad deseada con las versiones anteriores.

Ejecutar el comprobador de compatibilidad

No es necesario esperar hasta que se guarde el archivo para ver los resultados de la prueba de compatibilidad. Puedes ejecutar el comprobador de compatibilidad desde la ficha Archivo dentro de Información y haciendo clic en el botón Comprobar si hay problemas y eligiendo la opción Comprobar compatibilidad.


40

Desactivar el comprobador de compatibilidad al guardar

Si deseas desactivar el comprobador de compatibilidad al momento de guardar un archivo debes de quitar la marca de la caja de selección llamada Comprobar la compatibilidad al guardar este libro que se encuentra en la parte inferior de la ventana del comprobador de compatibilidad.

Errores en Excel

Los errores en Excel son generalmente consecuencia de haber introducido algún dato de manera incorrecta o de utilizar inapropiadamente alguna fórmula. Excel detecta los errores y los clasifica de manera que se nos facilite su resolución.

Si conocemos los tipos de errores en Excel podremos resolverlos fácilmente ya que podremos identificar rápidamente la causa.

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.


41

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.


42

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.

Error ##########

Si observas una celda llena de símbolos # lo que Excel está tratando de decir es que el contenido de esa celda no cabe dentro de ella.

Solución: Ajusta el ancho de la columna y el mensaje desaparecerá de inmediato.

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.


43

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.


44

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.

Fichas Contextuales

Ya conoces la cinta de opciones y las fichas que aparecen de manera predeterminada en Excel: Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar y Vista. Es probable que tengas alguna ficha adicional a las mencionadas y que probablemente se han insertado como consecuencia de instalar algún software complementario a Excel.

Fichas contextuales en Excel 2010

Sin embargo, existen otras fichas de Excel que están “ocultas” y que solo se mostrarán cuando se insertan ciertos objetos en Excel. Por ejemplo, los comandos para editar un gráfico no son importantes sino hasta que aparece un gráfico en una hoja de Excel. Al momento de seleccionar el gráfico se agrega la ficha Herramientas de gráficos que a su vez se divide en tres:

Este tipo de fichas son conocidas como Fichas contextuales. Otro ejemplo de fichas contextuales son las que aparecen al insertar una forma geométrica, como un rectángulo o una elipse. Excel muestra la Ficha contextual Herramientas de Dibujo, la cual te permite trabajar con este tipo de objetos:

Las fichas contextuales nos muestran los comandos relacionados a la operación que vamos a realizar sin necesidad de tenerlos siempre presentes dentro de la cinta de opciones. La próxima vez que insertes algún objeto en la hoja de trabajo no olvides mirar la cinta de opciones, tal vez Excel haya insertado algunos comandos adicionales para tu ayuda.


45

Fichas contextuales Ejemplos

Las fichas contextuales agrupan comandos de Excel que son mostrados solamente cuando se selecciona o se inserta algún objeto en especial. De esta manera los comandos se activan solamente cuando son necesarios.

A continuación mostraré algunos ejemplos de fichas contextuales.

Fichas contextuales de Ilustraciones

Dentro de la ficha Insertar encontramos el grupo Ilustraciones que tiene los comandos Imagen, Formas y SmartArt. Al insertar cualquiera de estos objetos se habilitará una ficha contextual con los comandos relacionados con cada objeto.

Por ejemplo, si utilizamos el comando Insertar > Ilustraciones > Imagen para insertar una imagen almacenada en nuestro equipo, se mostrará la ficha contextual Herramientas de imagen.

Cuando insertamos una forma en Excel se mostrará la ficha contextual Herramientas de dibujo.


46

De igual manera si insertamos un objeto SmartArt se habilitarán los comando necesarios que nos permitirán trabajar con ese tipo de objetos dentro de la ficha contextual Herramientas de SmartArt.

Fichas contextuales de gráficos

Al insertar cualquier gráfico de Excel (Insertar > Gráficos) se habilitará la ficha contextual Herramientas de gráficos.

Y si queremos hacer uso de los minigráficos podremos tener los comandos necesarios dentro de la ficha contextual Herramientas para minigráfico.


47

Fichas contextuales de tablas

Al crear una tabla en Excel se mostrará la ficha contextual Herramientas de tabla.

Y si creamos una tabla dinámica podremos acceder a todos los comandos relacionados desde la ficha contextual Herramientas de tabla dinámica.

Estos han sido algunos ejemplos de fichas contextuales que nos encontraremos frecuentemente al trabajar en Excel. Recuerda que las fichas contextuales se habilitarán al seleccionar el objeto recién insertado y se ocultarán al quitar la selección.


48

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.


49

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.

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:


50

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 cómo 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”


51

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.

La barra de estado de Excel

La barra de estado de Excel se encuentra en la parte inferior de la ventana de la aplicación y nos mantiene informados sobre el modo actual de Excel. La barra de estado contiene varias secciones que explicaré a continuación:

El indicador de modo

La primera sección de la barra de estado de Excel es el indicador de modo el cual nos muestra el estado actual de Excel que puede ser Listo, Introducir, Modificar, etc.

El botón grabar macro

Justo al lado derecho del indicador de modo se encuentra el botón grabar macro el cual abrirá el cuadro de diálogo Grabar macro donde podremos configurar la información de la nueva macro.

Cuando se ha iniciado con la grabación este botón cambia de apariencia y mostrará un botón para detener la grabación.


52

El indicador de auto cálculo

Cuando hacemos una selección de celdas que contienen un valor numérico, se realizará un auto cálculo con dichos valores y se mostrará en la barra de estado de Excel tres operaciones básicas: Promedio, Recuento y Suma.

La selección de vista

La selección de vista nos permite cambiar entre tres tipos de vista diferente de nuestra hoja de Excel: Vista normal, diseño de página y vista previa de salto de página. Con solo pulsar cualquier de estos botones cambiaremos de inmediato la vista de nuestra hoja.

El botón de nivel de zoom

Al pulsar el botón nivel de zoom se mostrará el cuadro de diálogo Zoom en donde podremos elegir algún nivel ce acercamiento pre-establecido o simplemente indicar algún porcentaje de zoom personalizado.

El control deslizante de zoom

Con el control deslizante de zoom podemos ajustar rápidamente el nivel de acercamiento o alejamiento de nuestra hoja. Podemos pulsar repetidas ocasiones los botones +/- o también podemos arrastrar el control deslizante hacia la derecha o izquierda.

Personalizar la barra de estado de Excel

Podemos personalizar la barra de estado de Excel y desplegar u ocultar algunas opciones haciendo clic derecho sobre la barra de estado y seleccionado la opción que deseamos habilitar. Aquellas opciones que aparecen en el menú emergente con una marca de verificación son aquellas que están actualmente activas.


53

La barra de herramientas de acceso rápido

La barra de herramientas de acceso rápido en Excel 2010 nos da la oportunidad de tener los comandos que más utilizamos siempre a la vista. Estos comandos son independientes a los que se muestran en la cinta de opciones.

Cada usuario de Excel puede personalizar la barra de herramientas de acceso rápido agregando los botones de comando que más utilice. Agregar nuevos botones es muy sencillo ya que solamente debemos elegir en la cinta de opciones el comando que deseamos agregar y hacer clic con el botón secundario del ratón sobre el botón del comando para seleccionar la opción Agregar a la barra de herramientas de acceso rápido del menú contextual que se muestra.

Consideraciones especiales de la barra de herramientas

Es importante mencionar que no es posible modificar el tamaño de los botones que se muestran en la barra de herramientas de acceso rápido. Para algunas personas el tamaño de los botones es muy pequeño y desearían agrandarlos, pero no es posible. La única alternativa que se tiene es cambiar la resolución de pantalla del equipo para verlos más grandes.

Otra consideración especial es que la barra de herramientas de acceso rápido siempre ocupa una sola línea de botones. Nunca se acomodarán los botones en dos o más líneas.

Opciones de pegado en Excel

Microsoft ha modificado la funcionalidad de pegado en las diferentes aplicaciones Office 2010. Veamos las opciones de pegado que tenemos en Excel 2010:


54

Hagamos una revisión detallada de cada una de las opciones:

Pegar: Excel pega todo el contenido de las celdas seleccionadas incluyendo fórmulas y formato.

Fórmulas: Excel pega todo el texto, números y fórmulas pero sin formato.

Formato de fórmulas y números: Excel pega el formato de los números que tienen las celdas seleccionadas así como sus fórmulas.

Mantener formato de origen: Excel copia el formato de las celdas originales y lo pega en las celdas destino junto con el contenido.

Sin bordes: Excel pega todo el contenido de las celdas seleccionadas sin copiar los border que tengan las celdas.

Mantener ancho de columnas de origen: Excel modifica el ancho de las columnas destino para que tengan el mismo ancho de las columnas origen al momento de ser copiadas.

Transponer: Excel cambia la orientación de las celdas pegadas. Si las celdas originales están en varias filas de una sola columna, al transponerlas se pegarán como varias columnas de una sola fila.

Valores: Excel pega sólamente los resultados calculados de cualquier fórmula en las celdas origen, no copia las funciones.

Formato de valores y números: Excel pega los resultados calculados de cualquier fórmula así como el formato de los números.

Formato de valores y origen: Excel pega los resultados calculados de cualquier fórmula así como los formatos.

Formato: Excel pega solamente el formato y no el contenido de las celdas origen.


55

Pegar vínculo: Excel crea vínculos hacia las celdas origen de manera que cualquier cambio hecho en ellas se vea reflejado en las celdas destino.

Imagen: Excel pega solamente una imagen copiada de la celda origen.

Imagen vinculada: Excel paga una imagen que está vinculada a la celda origen.

Adicionalmente puedes tener acceso a estas opciones de pegado en Excel a través del cuadro de diálogo Pegado especial, que puedes abrir desde el comando Pegar que se encuentra en la ficha Inicio.

Pegado especial en Excel

Excel normalmente copia toda la información de una selección de celdas y pega los datos en las celdas especificadas, pero puedes utilizar el Pegado especial para indicar algunas opciones alternas en el pegado de la información.

El comando Pegado especial nos permite utilizar opciones diferentes a la manera tradicional de copiar y pegar como por el ejemplo: pegar el contenido de las celdas sin aplicar un formato o solamente pegar el formato de las celdas sin considerar su contenido.

Para utilizar estas opciones de pegado debes hacer clic en el menú desplegable del botón Pegar, en la ficha Inicio, y seleccionar la opción Pegado especial.


56

Se mostrará el cuadro de diálogo Pegado especial donde tendremos varias opciones de pegado disponibles.

Opciones de pegado en Excel

Analicemos cada una de las opciones de pegado.

Todo. Pega todo el contenido de las celdas incluyendo fórmulas y formato. Esto es lo que normalmente sucede al utilizar el comando Pegar.

Fórmulas. Pega el texto y las fórmulas sin el formato de las celdas. Valores. Pega los valores calculados de las fórmulas, es decir, remueve

la fórmula y solamente pega los resultados. Formatos. Pega solamente el formato sin el contenido de las celdas. Comentarios. Pega los comentarios de las celdas. Validación. Pega solamente las reglas de validación que se hayan

configurado con el comando Validación de datos.


57

Todo utilizando el tema de origen. Pega toda la información y además aplica el estilo de las celdas de origen.

Todo excepto bordes. Pega toda la información de las celdas sin copiar los bordes.

Ancho de las columnas. Aplica el ancho de las columnas de las celdas copiadas en las columnas destino.

Formatos de números y fórmulas. Convierte las fórmulas en sus valores calculados y pega esos valores incluyendo el formato asignado de las celdas origen.

Todos los formatos condicionales de combinación. Pega todas las reglas de formato condicional en las celdas destino.

Operaciones aritméticas al pegar

Al mismo tiempo que pegamos la información podemos especificar que se ejecuten algunos cálculos matemáticos entre las celdas que hemos copiado y los valores de las celdas en donde pegaremos la información. Observa el siguiente ejemplo donde se copia una lista de valores numéricos que serán pegados sobre otras celdas que ya tienen un valor. Al especificar la operación Suma se añadirá el valor de la celda original con al valor de la celda donde se pega la información:

De esta misma manera podemos especificar otras operaciones aritméticas básicas como la resta, la división y la multiplicación.

Opciones de pegado adicionales

En la parte inferior del cuadro de diálogo Pegado especial tenemos algunas opciones adicionales. La primera es Saltar blancos la cual evitará pegar las celdas de origen que estaban vacías. Observa el siguiente ejemplo, donde primera hago un pegado normal y después hago un pegado seleccionando la


58

opción Saltar blancos donde las celdas que tienen el texto un texto en la celdas destino permanecen igual:

La opción Transponer nos ayuda a intercambiar la orientación de las celdas pegadas, es decir, si las celdas originales eran las filas de una sola columna al momento de transponerlas serán las columnas de una sola fila. Para comprender mejor este concepto consulta el artículo Cambiar de filas a columnas.

Finalmente el botón Pegar vínculos establecerá un vínculo entre las celdas de origen y las celdas destino de manera que algún cambio en las celdas de origen actualizará automáticamente las celdas destino. Observa el siguiente ejemplo:

http://exceltotal.com/cambiar-de-filas-a-columnas/

http://exceltotal.com/cambiar-de-filas-a-columnas/


59

Referencias en Excel

Las referencias en Excel son parte fundamental de la hoja de cálculo ya que a través de ellas podemos identificar cada una de las celdas de un libro de trabajo y de esa manera acceder al valor contenido en cualquiera de ellas.

El objetivo de este artículo es aclarar la mayor cantidad de dudas sobre el tema de referencias en Excel y será útil para aquellas personas que comienzan a utilizar la herramienta pero también para aquellos que ya tienen un poco de tiempo utilizando la hoja de cálculo y no han escuchado sobre temas como las referencias 3D o el estilo de referencia F1C1. Los temas de los que hablaré en este artículo son los siguientes:

¿Qué son las referencias en Excel? Tipos de referencias en Excel Referencias relativas en Excel Referencias absolutas en Excel Referencias mixtas en Excel Cómo cambiar entre tipos de referencia Referencias a rangos de celdas Referencias a celdas en otra hoja Referencias a celdas en otro libro Referencias 3D El estilo de referencia F1C1

Ya que todos los valores de una hoja de cálculo están almacenados en las celdas, es de suma importancia para los usuarios de Excel conocer la manera en que deberán acceder a dichos valores utilizando las referencias. Sin más preámbulo, comenzaremos con este estudio de las referencias en Excel.


60

¿Qué son las referencias en Excel?

Todo usuario de Excel debe saber que todas las hojas están divididas en columnas y filas. Esta división se hace evidente en la interfaz gráfica de Excel de la siguiente manera:

Por debajo de la barra de fórmulas se encuentran los encabezados de columna identificados por una letra.

En el extremo izquierdo están los encabezados de fila que muestran un número.

En el área de trabajo tenemos una cuadrícula que delimita el área de cada celda de la hoja y donde se puede observar que cada una de ellas pertenece a una determinada columna y fila.

En la imagen anterior, la celda seleccionada (celda activa)es la celda que se encuentra bajo la columna B y en la línea 3 y por lo tanto su dirección dentro de la hoja será B3. La referencia de una celda siempre constará de dos partes: la primera parte indicará la letra (o letras) de la columna a la que pertenece y la segunda parte indicará su número de fila.

Excel siempre nos ayudará a conocer fácilmente la referencia de la celda activa. En primer lugar, el encabezado de la columna y fila de la celda seleccionada estarán resaltados en un color diferente al resto de los encabezados. Además el Cuadro de nombres siempre mostrará la referencia de la celda activa tal como lo puedes observar en la siguiente imagen:

En conclusión, la referencia de una celda nos indica su ubicación dentro de una hoja, es decir, nos ayuda a conocer rápidamente la columna y fila a las que pertenece.


61

Tipos de referencias en Excel

Una referencia siempre será la dirección de una celda dentro de una hoja y siempre estará formada por la columna y fila en donde se ubica la celda. Así que, cuando hablamos de tipos de referencias en Excel estamos hablando sobre los diferentes tipos de comportamiento que pueden tener las referencias al momento de ser copiadas a otras celdas.

Es muy fácil copiar fórmulas en Excel, pero ¿qué sucede con las referencias de dicha fórmula al momento de hacer la copia? Es ahí en donde su comportamiento dependerá del tipo de referencia que se haya utilizado y analizaremos las alternativas que tenemos en las próximas tres secciones.

Referencias relativas en Excel

De manera predeterminada, las referencias en Excel son relativas. El término relativo significa que al momento de copiar una fórmula, Excel modificará las referencias en relación a la nueva posición donde se está haciendo la copia de la fórmula. Por ejemplo, supongamos que en la celda C1 tenemos la fórmula =A1+B1. En la siguiente imagen puedes observar el resultado de esta operación si tenemos los valores 5 y 3 en dichas celdas:

Si ahora copio (Ctrl+C) la fórmula de la celda C1 y la pego (Ctrl+V) en la celda C3, obtendré el siguiente resultado:

La fórmula que ha sido copiada a la celda C3 nos devolverá como resultado el valor cero. Al revisar la fórmula de dicha celda nos daremos cuenta que eso se debe a que Excel ha modificado automáticamente las referencias de la fórmula. En lugar de la celda A1 ahora tenemos la celda A3, y en lugar de la celda C1 que teníamos originalmente, ahora tenemos la celda C3.

En este ejemplo hemos copiado la fórmula hacia una celda de la misma columna, pero dos filas por debajo y eso es lo que indicará a Excel el ajuste a realizar en las referencias. Ya que no hubo cambio en la columna, ésta permanecerá igual en la nueva fórmula, pero ya que copiamos una celda que está dos filas hacia abajo, Excel realizará dicho ajuste sumando la misma cantidad de filas a las referencias. Ahora observa lo que sucede si vuelvo a copiar la fórmula de la celda C1 y la pego en la celda F2.


62

En esta ocasión la nueva celda se encuentra una fila hacia abajo y tres columnas a la derecha de la celda original y por lo tanto las referencias se adecúan a tal movimiento añadiendo tres columnas y una fila a cada referencia de la fórmula copiada.

Las referencias relativas son muy útiles cuando necesitamos aplicar una misma fórmula sobre un rango de datos. Por ejemplo, en la siguiente imagen puedes ver una hoja donde las columnas A y B tienen valores numéricos y ahora necesito colocar la multiplicación de ambos valores en la columna C. Para hacerlo rápidamente, coloco la fórmula =A1*B1 en la celda C1 y posteriormente copio dicha fórmula hacia debajo:

De esta manera obtendremos automáticamente el resultado correcto para cada fila ya que Excel modifica las referencias en cada fórmula mientras es copiada hacia abajo.

Referencias absolutas en Excel

Hay ocasiones en las que necesitamos “fijar” la referencia a una celda de manera que permanezca igual aún después de ser copiada. Si queremos impedir que Excel modifique las referencias de una celda al momento de copiar la fórmula, entonces debemos convertir una referencia relativa en absoluta y eso lo podemos hacer anteponiendo el símbolo “$” a la letra de la columna y al número de la fila de la siguiente manera:


63

Si tomamos el primer ejemplo de la sección anterior y utilizamos la fórmula =$A$1+$B$1 en la celda C1 y posteriormente copiamos dicha fórmula a la celda C3, obtendremos el siguiente resultado:

En este caso la celda C3 devolverá el mismo resultado que la celda C1 porque ambas fórmulas hacen referencia a las mismas celdas aún después de haberla copiado. Tendremos el mismo efecto al copiar la fórmula de la celda C1 hacia la derecha:

Al convertir las referencias en absolutas, no importa a donde copiemos la fórmula, dichas referencias permanecerán siempre fijas. Para dejar en claro el uso de las referencias absolutas, supondremos una tabla de datos como la siguiente:

La celda C2 hace el cálculo del área para el primer círculo tomando en cuenta el valor de Pi que está almacenado en la celda F1. En este momento la referencia a la celda F1 es relativa y si copio la formula hacia abajo obtendré el siguiente resultado:


64

Al copiar la fórmula hacia abajo, Excel modifica la referencia a la celda F1 y le agrega una fila más para convertirla en una referencia a la celda F2 que es el comportamiento natural de las referencias relativas. Para hacer que nuestras fórmulas hagan siempre referencia a la celda F1 debemos transformar dicha referencia en absoluta:

Observa con detenimiento que la única referencia que he convertido en absoluta es la de la celda $F$1. La otra referencia a la celda B2 la he dejado como relativa porque necesito que sea modificada por Excel para tomar el valor del radio de cada círculo conforme se copie la fórmula hacia abajo. El resultado después de copiar la fórmula será el siguiente:


65

Todas las fórmulas harán referencia a la celda F1 porque está establecida como absoluta, pero Excel modificará correctamente las referencias a las celdas de la columna B para obtener el resultado correcto.

Con esto hemos cubierto los dos tipos de referencias en Excel más utilizados. Si quieres ver algunos ejemplos adicionales sobre estos dos tipos de referencias te recomiendo consultar el siguiente video tutorial: Referencias relativas y absolutas en Excel.

Referencias mixtas en Excel

En los ejemplos anteriores sobre referencias absolutas utilizamos el símbolo “$” para fijar tanto la columna como la fila. Sin embargo, es posible fijar solo la columna o fijar solo la fila y a estas variaciones se les conoce como referencias mixtas.

Si queremos fijar solamente la columna, anteponemos el símbolo “$” a la letra de la columna y dejamos la fila sin dicho símbolo. Si por el contrario deseamos fijar solamente la fila, entonces anteponemos el símbolo “$”al número de la fila. En la siguiente imagen podrás ver un resumen de los tipos de referencias que hemos revisado hasta ahora:

Si colocas la fórmula =$A1 dentro de la celda B1 y copias la fórmula hacia la derecha verás que la fórmula se mantiene igual en todas las columnas porque hemos indicado que deseamos tener la columna $A fija. Por el contrario, si copias la fórmula hacia abajo, Excel modificará el número de fila:

Sin embargo, si cambiamos el tipo de referencia mixta de la celda B1 por la fórmula =A$1 y copiamos hacia la derecha y hacia abajo, obtendremos un resultado diferente al anterior ya que habremos fijado solamente la fila:


66

Estos son los cuatro tipos de referencias de los cuales puedes elegir al momento de construir tus fórmulas. Recuerda que esta clasificación se basa en el comportamiento de la referencia al copiar una fórmula.

Cómo cambiar entre tipos de referencia

Un consejo muy útil para hacer más eficiente el tiempo invertido al ingresar cualquier referencia en una fórmula es el uso de la tecla F4 cuando nos encontramos en el modo de edición. Cuando el cursor de edición se encuentra sobre una referencia y pulsamos la tecla F4, Excel cambiará el tipo de referencia sin la necesidad de que ingresemos manualmente el símbolo “$”. Hagamos el siguiente ejemplo para conocer el funcionamiento de este atajo de teclado:

1. Selecciona la celda B1 e ingresa la fórmula =A1 pero no pulses la tecla Entrar.

2. El cursor de edición se encontrará parpadeando al final de la referencia y si pulsas la tecla F4 una vez, la referencia se transformará automáticamente en absoluta =$A$1.

3. Si vuelves a pulsar la tecla F4 tendrás una referencia mixta como =A$1. 4. Y si pulsas una cuarta vez la tecla F4, la referencia será cambiada por la

otra opción de referencia mixta =$A1. 5. Al pulsar una quinta vez la tecla F4 volverás a la referencia relativa original.

La tecla F4 solamente cambiará la referencia que está sobre el cursor de edición así que, si quieres aplicar este cambio en varias referencias de una misma fórmula, entonces deberás colocar el cursor sobre cada referencia que será cambiada y pulsar F4 cada vez.

Referencias a rangos de celdas

Hasta ahora hemos visto cómo hacer referencia a una sola celda, pero Excel nos permite hacer referencia a un conjunto de celdas adyacentes utilizando el operador de rango que está representado por los dos puntos (:) y que deberá colocarse entre dos referencias de celdas. Sin embargo, esas dos referencias de celdas no son al azar, sino que deben ser el extremo superior izquierdo y el extremo inferior derecho de las celdas adyacentes a las que deseamos referirnos.

Considera el siguiente grupo de celdas adyacentes donde cada una de ellas tiene un valor numérico. La referencia para este rango de celdas será B2:D4 y puedes ver que dicho rango es reconocido por Excel al momento de hacer la suma.


67

Esto definitivamente es una gran ventaja ya que de lo contrario tendríamos que indicar todas las referencias a las nueve celdas, pero con este método podemos referirnos fácilmente a todo el rango de celdas. Solo debes recordar que la primera referencia corresponde a la celda del extremo superior izquierdo y la segunda referencia a la celda inferior derecha.

En este ejemplo ambas referencias son relativas, pero nada impide que utilicemos referencias absolutas al crear una referencia a un rango, por ejemplo: $B$2:$D$4. Todas las reglas que explique anteriormente para las referencias relativas, absolutas y mixtas también aplicarán para las referencias a rangos de celdas.

Referencias a celdas en otra hoja

En todos los ejemplos mostrados hasta este punto he utilizado referencias dentro de la misma hoja pero en más de una ocasión nos veremos en la necesidad de hacer referencia a una celda que se encuentra en una hoja diferente.

La única regla que debemos seguir es utilizar el nombre de la hoja donde se encuentra la celda de nuestro interés e inmediatamente colocar el signo de exclamación (¡) seguido de la referencia a la celda. De esta manera, si queremos referirnos a la celda C5 de la Hoja2, debemos hacerlo de la siguiente manera:

Una práctica que es altamente recomendable en las referencias a celdas de otra hoja, es rodear el nombre de la hoja con comillas simples (‘) ya que eso evitará cualquier error en caso de que el nombre de la hoja tenga un espacio en blanco, por ejemplo:


68

Para saber más sobre este tipo de referencias te recomiendo consultar el artículo Hacer referencia a celdas de otras hojas en Excel.

Referencias a celdas en otro libro

Si la celda que necesitas acceder se encuentra en otro libro, entonces deberás indicar el nombre del libro encerrado entre corchetes [] antes del nombre de la hoja de la siguiente manera:

Este tipo de referencia funcionará solamente si el libro especificado está abierto y además está guardado en la misma ubicación que el libro actual. En caso contrario será necesario indicar la ruta completa donde se encuentra dicho libro:

Observa que en este último ejemplo el nombre del libro y la hoja, así como la ruta, están encerrados entre comillas sencillas lo cual es ampliamente recomendable en caso de tener espacios en blanco en los nombres de las carpetas de la ruta, o en el nombre del libro u hoja.

Referencias 3D

Una referencia nos ayuda a identificar una celda o rango de celdas y de esta manera podemos indicar a Excel el lugar exacto donde buscar los valores o los datos que deseamos utilizar. Hoy hablaré de manera especial de las referencias 3D en Excel.

Referencias de celdas tradicionales

Para comprender mejor el beneficio de las referencias 3D, hagamos un ejemplo utilizando referencias de celdas de la manera tradicional. Supongamos


69

que tengo un libro de Excel con 12 hojas, una para cada mes del año y dentro de cada hoja, en la celda B2, tengo el dato del total de ventas del mes.

Ahora deseo crear una nueva hoja que tendrá la suma de las celdas B2 de todas las hojas. Para obtener esta suma podría sumar cada una de las celdas B2 de todas las hojas de la siguiente manera:

Con esta fórmula obtendré la suma adecuada, sin embargo me tardaré algunos segundos en construir la formula y probablemente cometeré algún error al introducirla. Para evitar cualquier problema, podemos hacer esta misma suma de una manera mucho más rápida utilizando una referencia 3D.

Crear una referencia 3D en Excel

Cuando deseamos utilizar la misma celda de varias hojas podemos crear una referencia 3D. Para nuestro ejemplo la referencia 3D para las celdas B2 de todas las hojas de los meses la formamos de la siguiente manera:

ENE:DIC!B2

La referencia 3D indica a Excel que debe considerar todas las hojas de Excel comprendidas entre la hoja ENE y la hoja DIC y tomar el valor de la celda B2 de todas ellas. Al indicar esta referencia como el argumento a la función SUMA obtendremos el resultado deseado:


70

En este ejemplo he utilizado una referencia 3D para indicar una sola celda, sin embargo las referencias 3D también se pueden utilizar con rangos de celdas.

Es muy importante mencionar que no es posible utilizar este tipo de referencias con cualquier función de Excel. Las funciones matemáticas y estadísticas son las que permitirán su uso pero no podrás utilizar este tipo de referencias en funciones de búsqueda como BUSCARV o COINCIDIR.

Además de acceder una sola celda en varias hojas utilizando las Referencias 3D, también puedes crear una referencia a un rango de celdas de la siguiente manera:

=SUMA(Hoja1:Hoja3!A1:C25)

El estilo de referencia F1C1

Los ejemplos de este artículo han utilizado el estilo de referencia A1 que es el predeterminado en Excel y que indica que las columnas serán identificadas por una letra, o combinación de letras, y que las filas serán identificadas por un número. Existe otro estilo de referencia en Excel, que no es tan común, pero que es necesario que lo conozcas por si llegas a encontrar algún equipo donde Excel utiliza el estilo de referencia F1C1 el cual identifica a las columnas y a las filas por su número. Así que en lugar de utilizar letras para las columnas, este estilo de referencia utiliza números para todas ellas.

Ya que sería difícil distinguir el número de la columna del número de la fila en una misma referencia, se antepone la letra “F” al número de la fila y la letra “C” al número de la columna. Algunos ejemplos de equivalencias en ambos estilos de referencia son los siguientes:


71

Para que Excel utilice este tipo de referencia debes ir a Archivo > Opciones > Fórmulas > Trabajando con fórmulas > Estilo de referencia F1C1.

Al seleccionar esta opción y aceptar los cambios, notarás de inmediato un cambio en los encabezados de columna ya que dejarán de ser letras y se mostrarán como números. Además podrás observar el estilo de referencia F1C1 en el Cuadro de nombres y ya no estará permitido utilizar el estilo de referencia anterior.

SI tu libro tenía fórmulas previamente ingresadas entonces Excel se encargará de mostrar automáticamente las referencias con el nuevo estilo y no será necesaria tu intervención en el cambio. Si quieres regresar al estilo de referencia A1 será suficiente con desmarcar la opción Estilo de referencia F1C1 del cuadro de diálogo Opciones de Excel.

La verdad es poco probable que alguna vez te veas forzado a utilizar el estilo de referencia F1C1, pero el concepto de utilizar valores numéricos para referirse a las


72

columnas y filas es muy utilizado al programar en VBA ya que es mucho más fácil referirse programáticamente a las columnas por su número que por su letra.

Conclusión

Es importante que conozcas todos los aspectos de las referencias en Excel ya que juegan un papel fundamental en el uso de la hoja de cálculo y las fórmulas. Si comprendes correctamente su funcionamiento podrás evitar una gran cantidad de errores, y resultados incorrectos, que se generan en Excel por el uso incorrecto de las referencias.

Inmovilizar filas y columnas

Una hoja de Excel con muchos datos nos forzará inevitablemente a desplazarnos hacia abajo o a la derecha en la hoja. Al hacerlo, perderemos de vista los títulos de las columnas lo cual nos impide distinguir adecuadamente los datos.

La acción de inmovilizar filas o columnas también es conocida como inmovilización de paneles y no es más que hacer que dichas filas o columnas permanezcan siempre visibles al desplazarse por la hoja de cálculo. Los comandos de inmovilización se encuentran en la ficha Vista dentro del grupo Ventana al pulsar el botón Inmovilizar.

Existen tres opciones para bloquear filas o columnas. La opción Inmovilizar fila superior hará que la Fila 1 siempre quede visible mientras nos desplazamos. Por otro lado, la opción Inmovilizar primera columna dejará fija la Columna A.

Inmovilizar paneles

Estas dos opciones inmovilizan ya sea una fila o una columna, pero si deseas inmovilizar tanto una fila como una columna al mismo tiempo entonces deberás utilizar la opción Inmovilizar paneles. La única condición importante para esta opción es que antes de oprimir el botón Inmovilizar paneles debes seleccionar la celda adecuada.

La celda que deberás elegir será la celda que se encuentra por debajo de las filas a inmovilizar y a la derecha de las columnas a inmovilizar. Por ejemplo, si

http://exceltotal.com/inmovilizar-paneles/


73

quiero inmovilizar las primeras tres filas y las primeras tres columnas de una hoja entonces debo seleccionar la celda D4 antes de pulsar el botón Inmovilizar paneles.

Orden personalizado en Excel

Excel nos permite ordenar los datos en un rango por cualquiera de sus columnas utilizando el comando “Ordenar de A a Z” para aplicar un orden ascendente o el comando “Ordenar de Z a A” para un orden descendente pero existen ocasiones en las que necesitamos aplicar un orden personalizado en Excel.

El comando Orden personalizado en Excel

El comando Orden personalizado se encuentra en la ficha Inicio > Modificar > Ordenar y Filtrar.

Este comando abrirá el cuadro de diálogo Ordenar el cual nos permitirá ordenar los datos por múltiples columnas e inclusive ordenarlas por el color de las celdas. También podremos crear un orden personalizado para cualquiera de las columnas basándonos en una lista personalizada.

Ordenar por varias columnas en Excel

Cuando trabajamos en Excel es muy fácil ordenar una tabla de datos por cualquiera de sus columnas ya sea de manera ascendente o descendente, pero a veces necesitamos ordenar por varias columnas y la única manera de hacerlo es utilizando el cuadro de diálogo Ordenar.


74

Por ejemplo, considera un rango de datos con información sobre órdenes de compra donde queremos ordenar la información por el nombre de la ciudad y posteriormente por la fecha de la orden. Todo comienza por seleccionar cualquiera de las celdas de datos y pulsando el comando Inicio > Modificar > Ordenar y Filtrar > Orden personalizado.

Una vez que se abre el cuadro de diálogo deberás elegir las opciones para el primer nivel que son tres: Columna, Ordenar según y Criterio de ordenación. Para la opción Columna encontrarás una lista con todos los encabezados de columna de la tabla de datos y solo deberás elegir aquella por la cual se realizará el ordenamiento. Para nuestro ejemplo será la columna Ciudad.

La opción Ordenar según la dejaremos con su valor predeterminado que es Valores y la opción Criterio de ordenación como A a Z ya que deseamos aplicar un orden ascendente en la columna Ciudad. Para agregar un segundo nivel de ordenación debes pulsar el botón Agregar nivel lo cual nos permitirá indicar la segunda columna por la cual deseamos ordenar los datos.

Observa que al detectar que la columna Fecha orden es de tipo fecha, las opciones para el Criterio de ordenación cambian y podemos seleccionar la opción De más antiguos a más recientes que nos permite ordenar las fechas de manera ascendente. Para aplicar los dos niveles de ordenación definidos debemos pulsar el botón Aceptar y obtendremos un resultado como el siguiente:


75

Las ciudades han sido ordenadas alfabéticamente y en el caso de la ciudad de Monterrey se ha colocado primero la orden del mes siete y posteriormente la del mes ocho. De esta misma manera podrás agregar tantos niveles de ordenación como necesites y Excel aplicará cada uno de ellos en el orden indicado.

Gestionar los niveles de ordenación

Como ya hemos visto en la sección anterior, el botón Agregar nivel nos permite crear nuevos criterios de ordenación para poder ordenar los datos por varias columnas. Pero, si por alguna razón decidimos eliminar alguno de ellos será suficiente con hacer clic sobre dicho criterio para seleccionarlo y en seguida pulsar el botón Eliminar nivel.

Así mismo el cuadro de diálogo Ordenar muestra el botón llamado Copiar nivel el cual nos permite hacer una copia de cualquiera de los criterios previamente creados. Solo será necesario seleccionar el criterio deseado y pulsar el botón Copiar nivel para crear una copia y posteriormente modificar sus propiedades.

En el ejemplo mostrado previamente, los criterios de ordenación se aplicaron en el mismo orden en el que aparecen en el listado. Si por alguna razón deseas modificar ese orden podrás hacerlo utilizando los botones de flechas que están al lado del botón Copiar nivel que son conocidos como los botones Subir y Bajar.

En nuestro ejemplo ordenamos los datos por la columna Ciudad y posteriormente por la columna Fecha orden. Si queremos invertir dicho orden, será suficiente con hacer clic en el primer nivel y después hacer clic en el botón Bajar para invertir el orden en que serán aplicados los criterios de ordenación. Observa el resultado de esta acción:


76

Ahora los datos están ordenados primero por fecha y luego por ciudad. Puedes observar que para aquellas filas que tienen la misma fecha, las ciudades aparecen en orden ascendente (alfabético) por ejemplo, para el día 01/07/2010 aparece primero Barcelona y luego Monterrey o para el día 01/08/2010 se muestra Bogotá y después Monterrey.

Ordenar con lista personalizada en Excel

Otra de las posibilidades de ordenamiento que tenemos con el comando Orden personalizado en Excel es que podemos indicar un criterio basado en una lista personalizada. Con esta opción no estaremos limitados a ordenar siempre de manera ascendente o descendente sino que podremos definir nuestra propia secuencia de ordenamiento.

Si deseamos ordenar nuestros datos de ejemplo por su categoría, pero en un orden especial que no sea alfabético, entonces debemos hacer lo siguiente. Abrimos el cuadro de diálogo Ordenar e indicamos la columna Categoría y en Criterio de ordenación seleccionaremos la opción Lista personalizada.

Inmediatamente se mostrará el cuadro de diálogo Listas personalizadas donde podremos definir el orden específico en que deseamos ordenar la columna. En el panel izquierdo seleccionamos la opción “NUEVA LISTA” y a la derecha ingresaremos las entradas de lista de la siguiente manera:


77

Si observas con detenimiento verás que los elementos indicados no están en orden alfabético, ni ascendente ni descendente, así que la única manera de lograr este tipo de ordenamiento es utilizando la lista personalizada. Para terminar pulsamos el botón Agregar para hacer permanente la creación de la lista personalizada y finalmente pulsamos Aceptar para regresar al cuadro de diálogo Ordenar donde observarás que la nueva lista personalizada se muestra como el Criterio de ordenación. Al aplicar este ordenamiento los datos quedarán de la siguiente manera:

En este ejemplo he creado la lista personalizada ingresando cada uno de los elementos dentro del cuadro de texto de entradas de lista, pero si tienes una cantidad considerable de elementos por ingresar, entonces te convendrá crear la lista personalizada desde un rango de celdas.

Orden personalizado por color

Además de los beneficios mencionados anteriormente, el comando Orden personalizado nos permite ordenar nuestros datos por el color de relleno de la celda, por el color de la fuente e inclusive por el conjunto de iconos de formato condicional desplegado dentro de las celdas.

Para mostrar esta funcionalidad he creado una regla de formato condicional en la columna Total para resaltar aquellas celdas que tienen un valor superior a los $2000 y ahora deseo ordenar los datos para colocar dichas celdas en la parte superior de la tabla. Lo único que debo hacer es que al momento de crear el nivel de ordenación elegiré la opción Color de celda de la siguiente manera:

http://exceltotal.com/como-usar-el-formato-condicional-en-excel/


78

Excel detectará automáticamente los posibles colores para una misma columna y nos permitirá elegir entre ellos. Después de elegir el color deseado y pulsar el botón Aceptar obtendremos el siguiente resultado:

El funcionamiento será el mismo si deseas ordenar por el color de fuente o también si deseas implementar un ordenamiento por el ícono de la celda.

El orden personalizado en Excel será de mucha ayuda cuando necesites ordenar tus datos por varias columnas, ya sea alfabéticamente o utilizando una lista personalizada para aplicar un criterio de ordenación especial. También nos será de gran ayuda para ordenar los datos basados en los colores de las celdas o de la fuente.

Búsqueda avanzada en Excel

El cuadro de búsqueda en Excel es una característica muy utilizada por todos nosotros ya que nos permite encontrar fácilmente cualquier dato dentro de nuestra hoja. Sin embargo, en muchas ocasiones no hacemos uso de todo el potencial que nos brinda.

Búsquedas avanzadas en Excel

Pocos usuarios saben que es posible realizar búsquedas avanzadas en Excel utilizando el cuadro de diálogo Buscar y remplazar.


79

Al realizar una búsqueda avanzada podremos encontrar valores que cumplan con ciertos patrones y de esa manera seleccionar varias celdas al mismo tiempo sin necesidad de realizar múltiples búsquedas. Pero antes de continuar debo definir un término importante.

Caracteres comodines en Excel

Los caracteres comodines son caracteres especiales que son utilizados para tomar el lugar de otros caracteres. Existen dos caracteres comodines principales en Excel que explicaré a continuación:

Signo de interrogación (?): Representa un solo carácter. Por ejemplo, el texto “ca?a” se puede interpretar como “casa”, “cama”, “caja”, “cana”, “capa” siempre y cuando sean de cuatro letras ya que solamente estamos remplazando el signo de interrogación por otro carácter.

Asterisco (*): Este carácter representa uno o más caracteres. Si utilizamos el ejemplo anterior, pero utilizando asterisco, tendremos que el texto “ca*a” puede interpretarse con las mismas palabras de cuatro letras: “casa”, “cama”, “caja”, etc. Pero también podrá interpretarse como “carta”, “cascada”, “caseta”, “cabeza”, etc. En este caso la longitud de la palabra no importa mientras que comience con “ca” y termine con “a” podremos remplazar el asterisco con cualquier cantidad y combinación de caracteres. Además de esto, el asterisco puede representar la ausencia de carácter, así que, si tuviéramos la palabra “caa” también sería devuelta por esta búsqueda.

Ejemplos de búsqueda avanzada en Excel

Para mostrar el comportamiento de los caracteres comodines haré la prueba sobre una lista de 20 palabras que tengo en la columna A de mi hoja de Excel. Abriré el cuadro de diálogo Buscar y remplazar y colocaré el texto “ca?a” y pulsaré el botón Buscar todos. Observa el resultado:


80

Para que los caracteres comodines funcionen correctamente debemos tener marcada la caja de selección Coincidir con el contenido de toda la celda. Observa que Excel ha encontrado todas las palabras de 4 letras que inician con “ca” y que terminan en “a”.

Ahora probemos intercambiando el signo de interrogación por el asterisco. El resultado deberá incluir estas mismas palabras más todas aquellas mayores de 4 letras que también inicien con “ca” y terminen en “a”. Observa el resultado:


81

Como era de esperarse, utilizando el asterisco en esta búsqueda avanzada hemos podido encontrar múltiples palabras que cumplen con la misma condición. Ya que el asterisco remplaza cualquier cantidad de caracteres, es conveniente ser mucho más específico cuando utilizamos este carácter comodín.

Por ejemplo, utilizando la misma lista de palabras, realizaré una búsqueda avanzada en Excel utilizando el siguiente texto “c*ello”. Observa que el resultado regresará apenas un par de coincidencias de toda la lista:


82

Es importante dejar en claro que los caracteres comodines pueden ser utilizados más de una vez dentro del mismo texto. Por ejemplo, hagamos una búsqueda con el siguiente texto “c??ello”:

Lo miso podemos hacer con el asterisco si hacemos una búsqueda del texto “c*s*a”. Esta expresión nos traerá todas las palabras que inician con “c” y que terminan con “a” y que además tengan una “s” en medio de la palabra.


83

Otro ejemplo de búsqueda avanzada en Excel

Una pregunta que recibo frecuentemente es cómo encontrar dentro de una columna de nombres a todas las personas que tienen un apellido en común. Por ejemplo, si quisiera encontrar todas las personas que tienen el apellido Galindo podría hacer una búsqueda como la siguiente:

Observa que esta búsqueda trae también aquellos valores que terminan con “Galindo” ya que, como expliqué anteriormente, el asterisco también representa la ausencia de caracteres y por ello tenemos como resultado el nombre “María Mendoza Galindo”. Ahora observa lo que sucede si remuevo el último asterisco del criterio de búsqueda:

Excel solamente encontrará aquellas cadenas de texto que terminen con “Galindo” y que no tengan ningún otro carácter al final.

Existen muchas combinaciones que podemos hacer tanto con el asterisco como con el signo de interrogación pero es necesario que pruebes cada uno de ellos en tu próxima búsqueda y te des cuenta por ti mismo que es posible realizar fácilmente una búsqueda avanzada en Excel.


84

Condicionar celdas en Excel 2010.

En este tutorial aprenderemos algo muy interesante que es condicionar la celdas en Excel para que nos devele información de una manera más dinámica y resaltada gráficamente con distintos iconos y colores.

Nosotros lo haremos de dos formas interesantes, y necesitamos una tabla como a la siguiente:

Ahora demarcamos toda la columna edad y luego nos vamos a Formato Condicional->Barras de Datos y escogemos el color más nos guste. Como se muestra en la siguiente imagen: Ahora demarcamos la columna Calificación y hacemos clic en Formato condicional->nueva regla como muestra la siguiente imagen:


85

He inmediatamente nos saldrá una venta que para nuestro ejemplo la llenaremos como muestra nuestra siguiente imagen:

Como hemos visto ya tenemos idea de cómo hacer más cosas con las condiciones de celdas, hasta acá este ejemplo.


86

Mostrar un mensaje al seleccionar una celda en Excel

En ocasiones deseamos mostrar un mensaje al seleccionar una celda en Excel de manera que el usuario tenga más información sobre el tipo de dato que esperamos que sea ingresado o simplemente queremos dar un mensaje informativo y para ello podemos utilizar la opción de mensaje de entrada de Excel.

Uso de comentarios como mensajes

Es común hacer uso de los comentarios de Excel para mostrar un mensaje en una celda. El comentario se mostrará con solo colocar el cursor del ratón sobre la celda.

Una ventaja de utilizar este método es que la celda tendrá una pequeña flecha roja en la esquina superior derecha indicando que existe un comentario sobre dicha celda. Sin embargo, ¿qué pasaría si tenemos muchas celdas con un comentario? En ese caso se volverá complicado trabajar con nuestra hoja de Excel porque cada vez que movamos el cursor de Excel se mostrará un nuevo comentario.

El mensaje de entrada en Excel

Si no deseas que el mensaje se muestre al pasar el cursor sobre una celda sino que prefieres que el mensaje se muestre solamente cuando se hace clic sobre ella, entonces puedes utilizar el mensaje de entrada de Excel.

Para utilizar este tipo de mensajes debes seleccionar la celda que tendrá el mensaje e ir a la ficha Datos y pulsar el botón Validación de datos. Dentro del cuadro de diálogo Validación de datos debes ir a la sección Mensaje de entrada:


87

En la caja de texto Título colocaremos el título de nuestro mensaje el cual será mostrado en negritas. En la caja de texto Mensaje de entrada colocaremos nuestro mensaje. Debes asegurarte de marcar la caja de selección Mostrar mensaje de entrada al seleccionar la celda.

Al pulsar el botón Aceptar se creará el mensaje de entrada asociado a la celda el cual se mostrará solamente cuando se seleccione dicha celda:

Ahora ya conoces un método alterno, diferente a los comentarios, para mostrar un mensaje al seleccionar una celda en Excel.

La cámara fotográfica de Excel

La cámara fotográfica de Excel nos permite capturar una imagen de un rango de celdas para después poder insertarla en alguna otra parte de la hoja o del libro pero con la ventaja de que no serán imágenes estáticas sino “dinámicas”.

Encontrar la cámara fotográfica

De manera predeterminada Excel 2010 no muestra la cámara fotográfica así que debemos personalizar la barra de herramientas de acceso rápido para agregar dicho comando.

Una vez que hayas abierto el cuadro de diálogo Opciones de Excel, que se encuentra en la ficha Archivo, debes ir a la sección Barra de herramientas de acceso rápido y seleccionar la opción Comandos que no están en la cinta de opciones y desplazarte en la lista de comandos hasta encontrar el comando Cámara.


88

Al pulsar el botón Agregar se añadirá el comando Cámara a la Barra de herramientas de acceso rápido.

Usar la cámara fotográfica en Excel

Para ejemplificar el uso de la cámara fotográfica en Excel haremos el siguiente ejemplo. Tengo un rango de datos que quiero copiar, pero necesito que dicha copia se actualice automáticamente al realizar una modificación en los datos originales. El primer paso es seleccionar las celdas que contienen los datos.

El segundo paso será pulsar el comando Cámara, el cual tomará la fotografía de las celdas seleccionadas y nos permitirá pegar la imagen en cualquier otra área de la misma hoja o en otra hoja del libro con tan solo hacer clic. Observa cómo realizo esta acción:


89

Puedes notar que Excel trata este elemento como una imagen ya que nos permite rotarla e inclusive cambiarla de tamaño. También podrás observar que al seleccionarla se mostrará la ficha contextual Herramientas de imagen sin embargo no es cualquier imagen. Observa cómo al modificar los datos en la tabla original, se actualiza la información dentro de la imagen:

En este ejemplo he pegado la imagen de la cámara fotográfica en la misma hoja, sin embargo puedes pegarla en otra hoja distinta y obtener el mismo resultado en la actualización de los datos.

http://exceltotal.com/ejemplos-de-fichas-contextuales/


90

Proteger archivo Excel con contraseña

La información que guardamos en nuestros archivos es de mucha importancia y Excel nos permite proteger un libro de varias maneras de modo que solo aquellas personas autorizadas puedan tener acceso a los datos.

Existen diferentes tipos de seguridad que podemos aplicar a nuestros libros de Excel. El primero y más básico de todos es la protección de una hoja.

Proteger una hoja de un libro Excel

Para impedir que se hagan cambios en los datos de una hoja podemos protegerla con contraseña. Para ello debemos ir a la ficha Revisar y en el grupo Cambios pulsar el botón Proteger hoja.

Se mostrará el cuadro de diálogo Proteger hoja donde deberemos especificar una contraseña.

Las opciones que aparecen seleccionadas de manera predeterminada son Seleccionar celdas bloqueadas y Seleccionar celdas desbloqueadas. Lo que ambas opciones indican es que les estaremos dando permiso a los usuarios de seleccionar y copiar las celdas de la hoja. Si quitamos la marca de estas cajas de selección entonces nadie podrá seleccionar las celdas sino solamente ver la información en pantalla.

Al pulsar el botón Aceptar se solicitará confirmar la contraseña previamente especificada y entonces la hoja quedará protegida. Para poder modificar la hoja se deberá pulsar el botón Desproteger hoja en la ficha Revisar.


91

Excel solicitará la contraseña y una vez proporcionada podremos realizar las modificaciones necesarias a la hoja.

Proteger un libro de Excel

Otro tipo de protección que podemos aplicar es el de proteger un libro lo cual significa proteger su estructura, es decir, al proteger un libro nadie podrá insertar nuevas hoja o modificar las hojas ya existentes. Es importante notar que proteger un libro no impide modificar las celdas de una hoja. Para proteger las celdas tendríamos que aplicar adicionalmente el método anterior de protección de hojas. Para proteger un libro debemos seleccionar el comando Proteger libro ubicado en la ficha Revisar.

Excel mostrará el cuadro de diálogo Proteger estructura y ventanas.

Si marcamos la caja de selección Ventanas, entonces las ventanas de las hojas quedarán también fijas y no se podrán maximizar para ocupar toda el área de la ventana de Excel. Generalmente con dejar seleccionada la opción Estructura será suficiente para proteger el libro. Recuerda que al proteger la estructura de un libro impedimos eliminar o insertar nuevas hojas.

Una vez establecida la contraseña y pulsar el botón Aceptar se solicitará confirmar la contraseña y entonces la protección del libro quedará establecida. Para remover la protección debemos ir de nuevo a la ficha Revisar y pulsar el botón Proteger libro y proporcionar la contraseña.

Cifrar con contraseña

El último método que mostraré en esta ocasión es el de cifrar un libro con contraseña. Este método solicitará una contraseña al momento de intentar abrir


92

el archivo. Para utilizar esta protección en nuestro libro debemos ir a la ficha Archivo y seleccionar la opción Información donde observaremos un botón Proteger libro y al pulsarlo encontraremos la opción Cifrar con contraseña.

Excel solicitará la contraseña dos veces antes de realizar la protección del archivo. Una vez protegido, la próxima vez que se intente abrir el libro de Excel se solicitará la contraseña:

Si no se especifica la contraseña adecuada no se podrá abrir el libro y por lo tanto no se podrá ver el contenido del archivo. Para remover este tipo de protección debemos ir de nuevo a la ficha Archivo en la sección Información y pulsar el botón Proteger libro para seleccionar la opción Cifrar con contraseña y en esta ocasión será suficiente con remover la contraseña para quitar la protección del libro.

Proteger celdas con fórmulas

Para proteger una hoja de Excel utilizamos el comando Proteger hoja que se encuentra en la ficha Revisar dentro del grupo Cambios. Este comando protegerá el contenido de las celdas bloqueadas.


93

El comando Proteger hoja en Excel 2010

Después de hacer clic sobre el comando Proteger hoja se mostrará un cuadro de diálogo.

Pon atención a la sentencia mostrada que dice Proteger hoja y contenido de celdas bloqueadas. De manera predeterminada todas las celdas de una hoja están “bloqueadas”. Suena un tanto confuso, pero se refiere a las celdas que son susceptibles a ser bloqueadas. Por ahora cierra el cuadro de diálogo Proteger hoja y continúa leyendo para saber más al respecto.

De manera predeterminada todas las celdas de una hoja están configuradas para ser bloqueadas. Eso lo podemos saber fácilmente al hacer clic sobre el cuadro de selección que se encuentra en la esquina superior izquierda de cualquier hoja de Excel. Este cuadro selecciona todas las celdas de la hoja con un solo clic.


94

Una vez que están seleccionadas todas las celdas debemos abrir el cuadro de diálogo Formato de celdas. Lo puedes hacer haciendo clic derecho sobre cualquier celda y seleccionando la opción Formato de celdas. Una vez abierto el cuadro de diálogo debes ir a la sección Proteger.

Verás que de manera predeterminada aparece marcado el cuadro de selección Bloqueada. Esto indica que todas las celdas son susceptibles a ser bloqueadas. Ahora desmarca la caja de selección y acepta los cambios.

Proteger las celdas que contienen fórmulas

Para proteger las celdas que contienen una fórmula necesitamos seleccionarlas primero y para hacerlo fácilmente puedes abrir el cuadro de diálogo Ir a Especial desde el comando que se encuentra en el menú Buscar y seleccionar dentro de la ficha Inicio.

Dentro del cuadro de diálogo Ir a Especial asegúrate de seleccionar la opción Celdas con fórmulas y acepta los cambios.


95

Esto hará que solamente se seleccionen las celdas que contienen una fórmula.

Ahora que ya tenemos seleccionadas las celdas que queremos proteger debemos abrir de nuevo el cuadro de diálogo Formato de celdas y volver a marcar el cuadro de selección Bloqueada el cual aplicará los cambios solamente a las celdas seleccionadas. Esto querrá decir que solo las celdas que contienen una fórmula serán susceptibles a ser bloqueadas.

Solamente resta ejecutar el comando Bloquear hoja que mencioné al principio del artículo de manera que se haga efectivo el bloqueo de las celdas que contienen una fórmula. Al abrirse el cuadro de diálogo Proteger hoja debes dejar seleccionadas las opciones predeterminadas y aceptar los cambios. Después de ejecutar el comando podrás hacer una prueba e intentar modificar alguna celda que contenga una fórmula y obtendrás un mensaje similar al siguiente:

Excel no permitirá modificar las celdas que contengan una fórmula y en cambio podrás editar el contenido de cualquiera de las otras celdas.


96

Cómo proteger celdas en Excel

Cuando has trabajado arduamente en la creación de tus reportes en Excel, lo último que deseas es que otras personas modifiquen las fórmulas, los títulos de columnas o el formato de las celdas, así que es importante saber cómo proteger celdas en Excel para evitar cualquier accidente.

La propiedad Bloqueada de una celda

Antes de pasar al detalle de cómo proteger las celdas de una hoja, es necesario que sepas que todas las celdas tienen una propiedad conocida como Bloqueada. Para acceder a esta propiedad es necesario abrir el cuadro de diálogo Formato de celdas e ir a la pestaña Proteger.

Recuerda que este cuadro de diálogo lo podemos abrir con el atajo de teclado Ctrl+1 o haciendo clic derecho sobre la celda y seleccionando la opción Formato de celdas.

La verdad es que el nombre de esta propiedad es un poco confuso, porque pareciera que nos está indicando que la celda está bloqueada aun cuando estemos hablando de un libro completamente nuevo. Lo que esta propiedad quiere decir es que la celda es “candidata” a ser bloqueada por el comando Proteger hoja.

Si alguna celda no tiene marcada la propiedad Bloqueada, entonces no podremos proteger su contenido. Esto será de mucha utilidad para un ejemplo que realizaremos más adelante, pero por ahora solo es necesario que sepas que todas las celdas en Excel tienen esta propiedad.

Cómo proteger celdas en Excel

El comando que nos ayudará a proteger celdas en Excel será el comando Proteger hoja que se encuentra dentro de la ficha Revisar.

Al pulsar este botón se mostrará un cuadro de diálogo que nos permitirá establecer una contraseña para proteger el contenido de la hoja:


97

Quiero que pongas especial atención a la leyenda de la primera caja de selección que dice: “Proteger hoja y contenido de celdas bloqueadas”. Cuando Excel se refiere a celdas bloqueadas se está refiriendo a todas aquellas celdas que tienen habilitada la propiedad Bloqueada de la cual hablamos en la sección anterior. Como lo he dicho antes, de manera predeterminada todas las celdas de una hoja tienen habilitada esta propiedad, así que al utilizar el comando Proteger hoja estaremos bloqueando todas las celdas de la hoja.

Al hacer clic en el botón Aceptar se nos pedirá confirmar la contraseña y se mostrará un mensaje de advertencia para recordarnos que en caso de olvidar dicha contraseña no habrá manera de desproteger la hoja. Una vez hecha la protección, cualquier intento que hagamos por modificar una celda de la hoja resultará en un mensaje de advertencia.

Cómo desproteger celdas en Excel

Seguramente la protección de celdas la utilizarás al compartir el archivo de Excel con otras personas, pero si deseas hacer nuevas modificaciones a tu reporte, será necesario desproteger la hoja. Para hacerlo deberás ir de nuevo a la ficha Revisar y pulsar el botón Desproteger hoja que mostrará un cuadro de diálogo solicitando la contraseña.

Una vez desprotegidas las celdas podrás realizar las modificaciones necesarias y posteriormente volver a proteger la hoja si así lo deseas.


98

Proteger un rango de celdas

Si has seguido los pasos anteriores te darás cuenta que la protección se aplica a todas las celdas de una hoja. Eso sucede porque de manera predeterminada todas las celdas tienen habilitada la propiedad Bloqueada, pero si queremos proteger solamente un rango de celdas debemos realizar algunas configuraciones adicionales.

En primer lugar debemos deshabilitar la propiedad Bloqueada para todas las celdas la hoja. La manera más rápida de hacerlo es haciendo clic en el botón de selección de celdas que se encuentra a la izquierda de la columna A:

Al hacer clic se seleccionarán todas las celdas de la hoja y entonces podremos abrir el cuadro de diálogo Formato de celdas para desmarcar la caja de selección Bloqueada. Una vez hecho esto debemos seleccionar solamente el rango que deseamos proteger y volver a abrir el cuadro de diálogo Formato de celdas para activar la propiedad Bloqueada para ese rango.

De esta manera, al pulsar el botón Proteger hoja, se protegerá solamente el rango de celdas previamente seleccionado mientras que el resto de celdas de la hoja permanecerá editable.

Impedir la selección de celdas bloqueadas

Por último quiero hablar sobre un par de opciones de configuración que observamos en el cuadro de diálogo Proteger hoja. Una de estas opciones nos permite indicar si queremos que el usuario pueda Seleccionar celdas bloqueadas.


99

Si dejamos habilitada esta opción al momento de proteger la hoja, el usuario podrá activar las celdas bloqueadas pero no podrá editar su contenido. Sin embargo, si deshabilitamos esta opción, Excel impedirá inclusive la selección de las celdas bloqueadas, es decir, no podremos habilitar las celdas ni con el ratón ni con el teclado.

Por otro lado, la opción Seleccionar celdas desbloqueadas hará los mismo pero con las celdas desbloqueadas, es decir con todas aquellas celdas que tengan deshabilitada la propiedad Bloqueada.

La próxima vez que necesites proteger celdas en Excel debes recordar que, si quiere proteger todas las celdas de una hoja puedes utilizar el comando Proteger hoja ya que de manera predeterminada todas las celdas tienen habilitada la propiedad Bloqueada. Pero si quieres proteger un rango de celdas específico debes habilitar la propiedad Bloqueada solo para ese rango de celdas.

Exportar datos de Excel a un archivo de texto

Los archivos de texto son uno de los métodos de intercambio de datos más populares en la actualidad ya que pueden ser abiertos y consultados por diversas aplicaciones en diferentes sistemas operativos como Windows, Mac OS, Linux, iOS, Android, etc.

Los archivos de texto no dependen de ninguna herramienta de software específica, ni tienen problemas de compatibilidad entre versiones porque se han creado de la misma manera por décadas y por lo tanto cualquier herramienta de edición de documentos puede leer un archivo de texto. Por esta razón es muy probable que en más de una ocasión tengas la necesidad de exportar los datos de Excel a un archivo de texto de manera que tus datos puedan ser integrados o analizados desde otro sistema de información.


100

Formatos de archivo de texto

Existen dos formatos de archivo de texto que son ampliamente utilizados:

Archivos de texto delimitados por tabulaciones (TXT): Cada valor de la columna está separado por el carácter de tabulación. A simple vista tendrás la impresión de que existen varios espacios en blanco entre cada columna, pero en realidad es solo el carácter tabulador el que los separa.

Archivos de texto delimitados por comas (CSV): Cada campo en el archivo de texto está separado por una coma. Este formato es tan conocido y utilizado que es almacenado con su propia extensión de archivo .CSV aunque sigue siendo un archivo de texto plano. Las siglas CSV provienen de su nombre en inglés Comma-Separated Values.

A continuación veremos lo simple que es crear cada uno de estos dos tipos de archivos en Excel. La hoja de datos que exportaremos en nuestros ejemplos será la siguiente:

Crear un archivo de texto delimitado por tabulaciones

Para crear un archivo de texto delimitado por tabulaciones en Excel debemos pulsar la ficha Archivo y seleccionar la opción Guardar como. Se mostrará el cuadro de diálogo Guardar como y para la opción Tipo debemos elegir Texto (delimitado por tabulaciones):


101

Una vez que pulses el botón Guardar se mostrarán algunas advertencias. La primera de ellas nos advierte que el tipo de archivo seleccionado no es compatible con libros que tienen múltiples hojas y que por lo tanto solo se exportarán los datos de la hoja activa. Esto quiere decir que, si tienes un libro con datos en varias hojas, deberás exportar cada hoja a un archivo de texto diferente.

Será muy probable que también se muestre una segunda advertencia indicando que nuestro archivo contiene formatos que no son compatibles con los archivos de texto delimitados por tabulaciones. Solamente deberás pulsar el botón Sí para continuar con el proceso y como resultado obtendremos un archivo de texto como el siguiente:

Crear un archivo de texto delimitado por comas

El procedimiento para crear un archivo de texto delimitado por comas (CSV) es prácticamente similar al ejemplo anterior con la diferencia de que en el cuadro de diálogo Guardar como debemos seleccionar el tipo de archivo CSV (delimitado por comas):

Obtendremos las mismas advertencias que en el ejemplo anterior pero como resultado tendremos el siguiente archivo de texto:


102

Dependiendo del país donde te encuentres y la configuración regional de tu equipo, es probable que Excel genere este tipo de archivo utilizando el punto y coma (;) en lugar de la coma (,) pero esto no es un asunto de Excel sino de Windows y el carácter definido como el separador de listas. A continuación veremos cómo cambiar el carácter utilizado por Excel para generar los archivos de texto.

Carácter delimitador para archivos de texto

Por supuesto que para cambiar el carácter delimitador utilizado por Excel al crear el archivo de texto se podría hacer un simple remplazo con cualquier editor de texto, pero si ésta es una actividad que realizas frecuentemente tal vez sea conveniente hacer un cambio de fondo. El carácter utilizado por Excel como delimitador en un archivo de texto es el que se encuentra definido en Panel de Control > Reloj, idioma y región > Cambiar formato de fecha, hora o número y dentro del cuadro de diálogo mostrado pulsar el botón Configuración adicional:


103

En dicho cuadro de diálogo encontrarás la opción Separador de listas y el carácter especificado es el utilizado por Excel. Para mi configuración regional, que es Español (México), tengo la coma (,) como el separador de listas pero si hago el cambio hacia punto y coma, al crear de nuevo el archivo CSV obtendré el siguiente resultado:

Observa que en esta ocasión Excel ha utilizado el punto y coma como el separador entre cada uno de los campos.

Campos de longitud determinada

En algunas ocasiones me he encontrado con el requerimiento de generar un archivo de texto que tenga campos de longitud determinada es decir, en caso de que una cadena de texto no sea de la longitud máxima permitida, se deberá rellenar con espacios en blanco. La solución a este problema es tratar los datos dentro de Excel antes de generar el archivo de texto.

Supongamos que para nuestra hoja de datos de ejemplo necesitamos crear un archivo de texto que tenga un ancho máximo de 15 caracteres, tanto para la columna Nombre como para la columna Apellido, y una longitud máxima de 25 caracteres para el correo electrónico. Los datos los generaré en una hoja diferente y utilizaré la siguiente fórmula para la primera columna:

=CONCATENAR(Hoja1!A1,REPETIR(" ",15-LARGO(Hoja1!A1)))

La función principal de esta fórmula es la función CONCATENAR que como primer argumento tiene el texto original de la Hoja1. El segundo argumento de la función CONCATENAR es la función REPETIR que tendrá el objetivo de generar una cadena de espacios en blanco cuyo número estará determinado por la diferencia entre el número máximo de caracteres admitidos (15) menos el número de caracteres de la cadena de texto original el cual obtenemos con la función LARGO. Por ejemplo, para la celda con el nombre Carlos, la función REPETIR generará una cadena de 9 espacios en blanco que será concatenada a la derecha de la cadena de texto “Carlos”.

La fórmula anterior nos sirve para obtener los valores de las columnas Nombre y Apellido. Para la columna Correo electrónico solo es necesario modificar el segundo argumento de la función REPETIR reemplazando el número 15 por el número 25 que es el máximo número de caracteres permitido para dicha columna.


104

Una vez aplicada esta fórmula a nuestro rango de datos podremos realizar la exportación a un archivo de texto. Ya que en este caso no necesitamos ningún tipo de separador podemos guardar el archivo como Texto Unicode y el resultado será el siguiente:

Así como en este último ejemplo he dado un tratamiento especial a los datos antes de exportarlos, es posible hacer diferentes modificaciones para ajustarnos a los requerimientos del archivo de texto que vamos a exportar. Algunas modificaciones que se realizan con cierta frecuencia son las siguientes:

Colocar comillas simples o dobles alrededor de las cadenas de texto. Exportar valores numéricos con algún número específico de decimales Dar algún formato específico a una fecha por ejemplo, cambiar al formato

inglés que utiliza mm/dd/aa en lugar de dd/mm/aa.

Estos cambios los podemos hacer con funciones de Excel de manera que coloquemos los datos en una hoja tal como los necesitamos y posteriormente hacer la exportación hacia un archivo de texto.

Importar archivos de texto

Los archivos de texto son muy comunes al momento de intercambiar información entre diferentes tipos de sistemas. Anteriormente los sistemas de las grandes empresas no generaban archivos Excel sino archivos de texto.

Por esa razón Excel incorporó desde sus primeras versiones la funcionalidad de importar archivos de texto de manera que se pudiera analizar dicha información dentro de Excel. El día de hoy se sigue utilizando este tipo de archivos para intercambiar información ya que son archivos muy ligeros a pesar de la gran cantidad de datos que pueden llegar a contener.


105

Un archivo de texto que contiene información de una base de datos siempre hace uso de algún caracter especial para separar la información perteneciente a cada columna. Es precisamente ese caracter utilizado el que hace la diferencia entre un tipo de archivo y otro. Por ejemplo, aquellos archivos en donde cada cambio de columna está indicado por el signo de puntuación “,” (coma) es conocido como archivo CSV por sus siglas en inglés (Comma-Separated Values). Observa el siguiente ejemplo de un archivo CSV:

La diferencia entre un archivo CSV y uno de texto es solamente el tipo de separador utilizado. Un archivo de texto generalmente indica la separación entre columnas utilizando el tabulador:

Estos dos tipos de archivos son los más comunes, pero podrías encontrarte alguno diferente que tal vez utilice el signo “;” para separar las columnas, sin embargo cualquier tipo de archivo de texto podrá ser importado a Excel sin importar el caracter separador utilizado.

Importar archivos de texto en Excel

Para importar un archivo de texto debes ir a la ficha Datos y seleccionar el comando Desde texto que se encuentra dentro del grupo Obtener datos externos. Esto abrirá el cuadro de diálogo Importar archivo de texto que permitirá seleccionar el archivo a importar.


106

Después de seleccionar el archivo adecuado debes hacer clic en Importar. Eso mostrará el primer paso del Asistente para importar texto. Este primer paso es importante porque permite especificar el tipo de archivo a importar ya sea que utilice algún caracter específico como delimitador o que sea un archivo que utiliza un ancho fijo de columna.

De igual manera en este paso se puede indicar a Excel que comience a importar a partir de alguna fila específica del archivo. Al hacer clic en el botón Siguiente, Excel sugerirá el tipo de separador que ha descubierto dentro del archivo a importar y generalmente será acertado en su sugerencia. Aun así recomiendo validar que dentro de la vista previa efectivamente se muestra una separación adecuada de los datos.

En caso de que Excel se haya equivocado solamente deberás seleccionar el separador adecuado de la lista o especificar algún otro si es necesario. Haz clic en siguiente que nos llevará al último paso que permite especificar el tipo de datos de cada columna.


107

Solamente tienes 3 opciones: General, Texto y Fecha. Sugiero poner especial atención a las fechas de manera que después de la importación se facilite su manejo e interpretación dentro de Excel. Al hacer clic en el botón Finalizar, se preguntará por la ubicación dentro de la hoja de Excel donde se colocarán los datos importados.

Una vez indicada la celda destino deberás hacer clic en el botón Aceptar y los datos serán importados por Excel.


108

Importar datos con una consulta web

Una consulta web (Web Query) de Excel te permite importar información de un sitio web a una hoja de Excel. La consulta encontrará cualquier tabla de datos en la página web y te permitirá seleccionar la tabla que necesitas para poder importar los datos a tu hoja de Excel.

Vamos a trabajar en un ejemplo básico para tener una idea de cómo operan las consultas web. Para empezar debes seleccionar la celda donde quieres que se coloquen los datos y seleccionar la ficha Datos y el comando Desde Web que se encuentra en el grupo Obtener datos externos.

Seleccionar tabla de datos

Se abrirá el cuadro de diálogo Nueva consulta web y en el cuadro de Dirección colocaré lo siguiente: http://www.imdb.com/chart/top (esta dirección muestra las mejores 250 películas de acuerdo al sitio IMDB). Observa los íconos amarillos que aparecen indicando las tablas de datos que se pueden seleccionar. Haz clic sobre el ícono de la tabla deseada (el icono se tornará verde) y posteriormente haz clic en el botón Importar.


109

Inmediatamente se mostrará el cuadro de diálogo Importar datos que te preguntará la ubicación donde deseas colocar los datos. Una vez indicado el rango, haz clic en Aceptar.

Excel comenzará a importar los datos y al terminar los desplegará dentro de la hoja correspondiente.

Con esto hemos importado información contenida dentro de una página Web, pero Excel puede ir aún más lejos y permitirnos actualizar los datos sin tener que volver a visitar el sitio Web original.

Actualizar datos desde la Web

Una vez construida la consulta Web podrás refrescar los datos con tan solo seleccionar el comando Actualizar todo que se encuentra en el grupo Conexiones de la ficha Datos. Este comando ocasionará que Excel descargue una versión actualizada de los datos automáticamente.


110

Debes tomar en cuenta que los datos deben venir de una página de dominio público, es decir, no deberá solicitar usuario ni contraseña, de lo contrario Excel no podrá importar los datos. Y por supuesto, si la página de origen elimina los datos publicados, no habrá forma de realizar la actualización.

Las aplicaciones para las consultas web pueden ser muchas. Se podrían construir tableros de mando que actualicen la información automáticamente al abrir el archivo. Inclusive se podrían implementar consultas web para intercambiar información en una Intranet (LAN) donde alguna aplicación Web genera información en tablas con lo cual no habrá necesidad de copiar y pegar la información manualmente en Excel sino que se refrescará automáticamente a través de una consulta web.

Importar datos desde Access

Microsoft Access es un software de base de datos muy utilizado y puedes encontrarte en alguna ocasión con la necesidad de importar datos desde Access hacia Excel lo cual es una tarea muy sencilla.

Importar una base de datos Access a Excel

No necesitas conocer mucho sobre Access ya que Excel ha simplificado grandemente este proceso para los usuarios. Lo primero que debemos hacer es ir a la ficha Datos y pulsar el botón Desde Access.

Este comando mostrará el cuadro de dialogo Seleccionar archivos de origen de datos en donde deberás especificar el archivo Access que deseas abrir:


111

De inmediato Excel mostrará otro cuadro de diálogo que enlistará las tablas contenidas en el archivo Access.

Una tabla es el lugar en donde se almacenan los datos en Access. Podríamos pensar que una tabla de Access es similar a una hoja de Excel porque también está formada por columnas y filas. Y aunque el concepto de tabla es un poco más avanzado que una hoja de Excel, es suficiente la comparación por ahora para saber que ahí se encuentra almacenada la información de Access. Una vez seleccionada la tabla de Access que deseamos importar debemos pulsar el botón Aceptar y ahora Excel nos preguntará por algunas configuraciones de importación:


112

En primer lugar podemos elegir traer los datos desde Access como una Tabla de datos o también como un Informe de tabla dinámica. Así mismo podemos especificar la celda de nuestra hoja donde queremos que se inserten los datos o simplemente especificar que se inserten en una nueva hoja. Al pulsar el botón Aceptar Excel realizará la importación de los datos.

La información de la tabla de Access será mostrada dentro de la hoja de Excel en el mismo orden en que aparecen los datos en la tabla.

Importar datos desde dBASE

dBASE fue uno delos primeros programas de gestión de base de datos que existió para las computadoras personales y se convirtió en la plataforma de muchos programas de cómputo de mediados de los años 80’s.

Las bases de datos dBASE utilizan un archivo con extensión .dbf y es un método simple para guardar información por lo que es posible que aún hoy en día nos encontremos con alguno que otro archivo DBF que contenga información que deseamos utilizar en Excel.


113

Versiones de dBASE

Antes de realizar una importación de un archivo DBF es recomendable saber la versión con la que fue creada dicha base de datos. Excel 2010 nos permite importar archivos dBASE III, dBASE IV y dBASE 5. Para importar los datos sigue los siguientes pasos.

Importar un archivo dBASE a Excel

Dentro de la ficha Datos dentro del grupo Obtener datos externos pulsa el botón De otras fuentes y selecciona la opción Desde Microsoft Query.

Se mostrará el cuadro de diálogo Elegir origen de datos.

Crear origen de datos

Deja seleccionada la opción Nuevo origen de datos y pulsa el botón Aceptar. Se mostrará el cuadro de diálogo Crear nuevo origen de datos y deberás colocar un nombre para identificar esta conexión de datos.


114

En la lista de controladores selecciona el controlador llamado Microsoft Access dBASE Driver. Al hacer clic sobre el botón Conectar se mostrará otro cuadro de diálogo que será de mucha importancia porque es donde especificaremos nuestro archivo DBF y su versión.

En la primera lista podrás ver las versiones de dBASE disponibles así que deberás seleccionar la más adecuada para el archivo. Posteriormente debes pulsar el botón Seleccionar directorio para especificar la carpeta donde se encuentra el archivo DBF.

Si el botón Seleccionar directorio está deshabilitado es porque seguramente está marcada la caja de selección Usar directorio actual, así que solamente quita la marca y podrás elegir el directorio adecuado. Al pulsar el botón Aceptar regresaremos al cuadro de diálogo Crear nuevo origen de datos pero con la peculiaridad de que al desplegar la cuarta lista observaremos las tablas disponibles de nuestra base de datos dBASE:


115

Haz clic en el botón Aceptar y habremos terminado de configurar el origen de los datos.

El Asistente para consultas

Ahora solo nos resta conectarnos al origen de datos para poder importar el archivo dBASE. En este punto estará abierto solamente el cuadro de diálogo Elegir origen de datos, así que asegúrate de que el nuevo origen que acabamos de crear esté seleccionado y pulsa el botón Aceptar. Se mostrará la primera pantalla del Asistente para consultas.

En esta pantalla puedes oprimir los botones que se encuentran entre ambas listas para seleccionar las Tablas y columnas que se importarán a Excel y una vez que hayas hecho la elección oprime el botón Siguiente.


116

En la segunda pantalla podremos, de manera opcional, aplicar algún filtro a la importación de datos desde dBASE de manera que podamos elegir los registros de la base de datos adecuados. Pulsa el botón Siguiente y llegaremos a la tercera pantalla del Asistente para consultas.

Esta pantalla nos permite especificar algún orden en los datos de manera que sean importados a Excel en dicho orden. Pulsa el botón Siguiente para ir a la última pantalla en donde seleccionaremos la opción Devolver datos a Microsoft Excel y finalmente pulsaremos el botón Finalizar.


117

El último cuadro de diálogo que se muestra será el que nos permite especificar la ubicación de los datos a importar.

Al pulsar Aceptar la información será exportada a la hoja de Excel de acuerdo a las configuraciones especificas en elOrigen de datos y en el Asistente para consultas.

De esta manera hemos terminado de importar datos desde dBASE a Excel 2010.


118


119

BIBLIOGRAFÍA:

Manual de instrucción de Microsoft Excel 2010, Prof. Edna Pérez Escobar y Prof. Edwin E. Gonzalez Carril, Universidad de Puerto Rico en Aguadilla.

http://exceltotal.com/libros-hojas-y-celdas/ http://office.microsoft.com/es-es/excel-help/especificaciones-y-limites-de-excel-

HP010073849.aspx


120

Índice: Descripción de la Asignatura: .............................................................. 3

Objetivos Generales: ........................................................................ 3

Unidades de Aprendizaje: .................................................................. 3

Unidad 1: Introducción a la Planilla de Cálculo ......................................... 4

¿Qué es Excel? ................................................................................ 4

Excel y Office ................................................................................. 4

Versiones de Excel ........................................................................... 4

Historia de las versiones de Excel ...................................................... 5

Componentes de la hoja de trabajo ....................................................... 7

Conceptos Básicos ........................................................................... 7

Celdas de una hoja ........................................................................ 8

Salto de línea en una celda de Excel ..................................................... 9

Especificaciones y límites de las hojas de cálculo y de los libros .................. 9

Especificaciones y límites de los cálculos ........................................... 10

Especificaciones y límites de los gráficos ........................................... 11

Especificaciones y límites de los informes de tablas dinámicas y gráficos

dinámicos ................................................................................. 11

La hoja de cálculo ...................................................................... 12

La cinta de opciones....................................................................... 13

Las fichas de Excel 2010 ............................................................... 13

Indicadores de cuadro de diálogo en Excel ............................................ 14

Ejemplos de indicadores de cuadro de diálogo ..................................... 14

¿Qué es la vista Backstage? ............................................................... 15

¿Dónde está la vista Backstage? ....................................................... 15

La ficha Archivo en Excel 2010 ........................................................ 16

Operaciones aritméticas .................................................................. 16

Funciones de Excel ........................................................................ 17

Los formatos de archivo de Excel 2010 .............................................. 17

Explorando el formato open XML ..................................................... 17

El módulo de compatibilidad de Office .............................................. 18

Guardar un archivo como una versión anterior ..................................... 18

Áreas de trabajo en Excel ................................................................ 19

Atajos de teclado en Excel ............................................................... 20

¿Qué es un atajo de teclado? .......................................................... 20

Teclas importantes para los atajos ................................................... 20


121

Nomenclatura de los atajos ........................................................... 20

Teclas de función en Excel ............................................................ 21

Atajos de una sola tecla ................................................................ 21

Atajos de teclado básicos en Excel ................................................... 21

Atajos con Ctrl y teclas de función ................................................... 22

Atajos con Mayús y teclas de función ................................................ 22

Atajos con Alt y teclas de función .................................................... 22

Atajos con las teclas Ctrl+Mayús ...................................................... 23

Atajos de teclado para moverse en Excel ........................................... 23

Atajos de teclado para seleccionar datos ........................................... 24

Atajos para ingresar datos y fórmulas ............................................... 24

Otros atajos de teclado en Excel ..................................................... 24

Comentarios en celdas de Excel ......................................................... 24

Cambiar el nombre de usuario del comentario ..................................... 25

Copiar los comentarios a otras celdas ............................................... 26

Agregar una imagen a un comentario ................................................ 26

El administrador de nombres de rangos ................................................ 27

Tipos de nombres en Excel ............................................................ 27

Cómo crear un nombre en Excel ...................................................... 28

El administrador de nombres en Excel ............................................... 28

Cómo auditar nombres ................................................................. 29

Códigos de formato personalizado en Excel ........................................... 30

El código # ............................................................................... 30

El código ? ................................................................................ 31

El código 0 ............................................................................... 31

Combinar texto y números en una celda ............................................... 31

Utilizar la concatenación .............................................................. 32

Utilizar la función TEXTO .............................................................. 32

Utilizar el formato personalizado ..................................................... 33

Cómo limitar el área de trabajo en Excel .............................................. 34

Ocultar columnas y filas excedentes ................................................. 35

Comprobador de compatibilidad ........................................................ 37

Pérdida menor de fidelidad ............................................................ 38

Pérdida significativa de funcionalidad ............................................... 38

Ejecutar el comprobador de compatibilidad ........................................ 39

Desactivar el comprobador de compatibilidad al guardar ........................ 40

Errores en Excel ............................................................................ 40


122

Error #¿NOMBRE? ........................................................................ 40

Error #¡REF! .............................................................................. 41

Error #¡DIV/0! ............................................................................ 41

Error #¡VALOR! .......................................................................... 41

Error #¡NUM! ............................................................................. 41

Error #¡NULO! ............................................................................ 41

Error #N/A ................................................................................ 42

Error ########## ........................................................................ 42

Manejo de errores en Excel .............................................................. 42

Funciones que informan sobre errores ............................................... 42

Manejo de errores en Excel ............................................................ 43

La función SI.ERROR .................................................................... 43

Fichas Contextuales ....................................................................... 44

Fichas contextuales en Excel 2010 ................................................... 44

Fichas contextuales Ejemplos ......................................................... 45

Fichas contextuales de Ilustraciones .............................................. 45

Fichas contextuales de gráficos .................................................... 46

Fichas contextuales de tablas ...................................................... 47

Formato personalizado de celdas en Excel............................................. 48

Formato personalizado de una celda ................................................. 48

El formato personalizado en Excel ................................................... 48

Ejemplo de formato personalizado de una celda ................................... 49

Códigos de formato personalizado .................................................... 49

Formato personalizado a texto ........................................................ 50

La barra de estado de Excel .............................................................. 51

El indicador de modo ................................................................... 51

El botón grabar macro .................................................................. 51

El indicador de auto cálculo ........................................................... 52

La selección de vista .................................................................... 52

El botón de nivel de zoom ............................................................. 52

El control deslizante de zoom ......................................................... 52

Personalizar la barra de estado de Excel ............................................ 52

La barra de herramientas de acceso rápido ........................................... 53

Consideraciones especiales de la barra de herramientas ......................... 53

Opciones de pegado en Excel ............................................................ 53

Pegado especial en Excel ................................................................. 55

Opciones de pegado en Excel ......................................................... 56


123

Operaciones aritméticas al pegar ..................................................... 57

Opciones de pegado adicionales ...................................................... 57

Referencias en Excel ...................................................................... 59

¿Qué son las referencias en Excel?.................................................... 60

Tipos de referencias en Excel ......................................................... 61

Referencias relativas en Excel ........................................................ 61

Referencias absolutas en Excel ....................................................... 62

Referencias mixtas en Excel ........................................................... 65

Cómo cambiar entre tipos de referencia ............................................ 66

Referencias a rangos de celdas ....................................................... 66

Referencias a celdas en otra hoja .................................................... 67

Referencias a celdas en otro libro .................................................... 68

Referencias 3D ............................................................................. 68

Referencias de celdas tradicionales .................................................. 68

Crear una referencia 3D en Excel ..................................................... 69

El estilo de referencia F1C1 ........................................................... 70

Conclusión ................................................................................ 72

Inmovilizar filas y columnas .............................................................. 72

Inmovilizar paneles ..................................................................... 72

Orden personalizado en Excel............................................................ 73

El comando Orden personalizado en Excel .......................................... 73

Ordenar por varias columnas en Excel ............................................... 73

Gestionar los niveles de ordenación .................................................. 75

Ordenar con lista personalizada en Excel ........................................... 76

Orden personalizado por color ........................................................ 77

Búsqueda avanzada en Excel ............................................................. 78

Búsquedas avanzadas en Excel ........................................................ 78

Caracteres comodines en Excel ....................................................... 79

Ejemplos de búsqueda avanzada en Excel .......................................... 79

Otro ejemplo de búsqueda avanzada en Excel ..................................... 83

Condicionar celdas en Excel 2010. ...................................................... 84

Mostrar un mensaje al seleccionar una celda en Excel .............................. 86

Uso de comentarios como mensajes.................................................. 86

El mensaje de entrada en Excel ...................................................... 86

La cámara fotográfica de Excel .......................................................... 87

Encontrar la cámara fotográfica ...................................................... 87

Usar la cámara fotográfica en Excel ................................................. 88


124

Proteger archivo Excel con contraseña ................................................. 90

Proteger una hoja de un libro Excel .................................................. 90

Proteger un libro de Excel ............................................................. 91

Cifrar con contraseña ................................................................... 91

Proteger celdas con fórmulas ......................................................... 92

El comando Proteger hoja en Excel 2010 ............................................ 93

Proteger las celdas que contienen fórmulas ........................................ 94

Cómo proteger celdas en Excel ....................................................... 96

La propiedad Bloqueada de una celda ............................................... 96

Cómo proteger celdas en Excel ....................................................... 96

Cómo desproteger celdas en Excel ................................................... 97

Proteger un rango de celdas ........................................................... 98

Impedir la selección de celdas bloqueadas .......................................... 98

Exportar datos de Excel a un archivo de texto ........................................ 99

Formatos de archivo de texto ........................................................ 100

Crear un archivo de texto delimitado por tabulaciones .......................... 100

Crear un archivo de texto delimitado por comas .................................. 101

Carácter delimitador para archivos de texto ...................................... 102

Campos de longitud determinada .................................................... 103

Importar archivos de texto .............................................................. 104

Importar archivos de texto en Excel ................................................ 105

Importar datos con una consulta web ................................................. 108

Seleccionar tabla de datos ............................................................ 108

Actualizar datos desde la Web ....................................................... 109

Importar datos desde Access ............................................................ 110

Importar una base de datos Access a Excel ........................................ 110

Importar datos desde dBASE ............................................................ 112

Versiones de dBASE .................................................................... 113

Importar un archivo dBASE a Excel .................................................. 113

Crear origen de datos ................................................................. 113

El Asistente para consultas ........................................................... 115

BIBLIOGRAFÍA: ............................................................................. 119

Revisado el: 09 de agosto de 2015

01 unidad i - competencia aplicada

Documents