excel avanzado 2013

133
MICROSOFT EXCEL 2013 AVANZADO Ing. Oscar Pérez/ Ing. Aracely Torres UNIVERSIDAD NACIONAL DE INGENIERIA PROGRAMA DE ADMINISTRACIÓN DE UNIDADES DE SERVICIOS UNI - PAUS

Upload: flixpicado

Post on 13-Jun-2015

1.972 views

Category:

Education


6 download

DESCRIPTION

Pequeño manual para excel avanzado

TRANSCRIPT

Page 1: Excel avanzado 2013

MICROSOFT EXCEL 2013

AVANZADO

Ing. Oscar Pérez/ Ing. Aracely Torres

UNIVERSIDAD NACIONAL DE INGENIERIA PROGRAMA DE ADMINISTRACIÓN DE UNIDADES DE SERVICIOS

UNI - PAUS

Page 2: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 1

Contenido

Presentación ................................................................................................................................. 3

Capítulo 1: Introducción a Excel 2013 ........................................................................................... 5

1.1 Historia Y Versiones .......................................................................................................... 6

1.2 Especificaciones, Límites y Problemas ............................................................................. 8

1.3 Las novedades de Excel 2013 ........................................................................................... 9

Inicio Rápido .......................................................................................................................... 9

Análisis de Datos Instantáneos ............................................................................................ 10

Rellenar una columna completa de datos en un abrir y cerrar de ojos ................................ 10

Crear un gráfico adecuado para los datos ........................................................................... 11

Filtrar datos de tabla usando segmentaciones de datos ...................................................... 11

Un libro, una ventana ........................................................................................................... 11

Nuevas funciones de Excel .................................................................................................. 11

Capítulo 2: Segmentación de Datos y Filtros .............................................................................. 12

2.1 Segmentación de datos ..................................................................................................... 14

2.2 Filtros ................................................................................................................................. 16

Capítulo 3: Estructura de Datos en Ms Excel y Funciones ......................................................... 24

3.1 Entorno y estructura de Ms Excel ...................................................................................... 24

3.2 Administrador de nombres ................................................................................................. 25

3.3 Generalidades de las funciones ........................................................................................ 30

3.4 Referencias ........................................................................................................................ 31

Referencias relativas ............................................................................................................ 31

Referencias absolutas .......................................................................................................... 32

Referencias mixtas ............................................................................................................... 32

Referencias Externas ........................................................................................................... 33

Referencias en Macros ........................................................................................................ 34

3.5 Funciones .......................................................................................................................... 34

Page 3: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 2

Funciones Lógicas ............................................................................................................... 35

Funciones de Búsqueda y Referencia ................................................................................. 45

Combinación de Fórmulas y Funciones ............................................................................... 69

Funciones Matriciales .......................................................................................................... 74

3.6 Errores de Excel ................................................................................................................ 80

Capítulo 4: Restricción de Datos y Formatos condicionados ...................................................... 84

4.1 Formato Condicional .......................................................................................................... 84

Aplicar Formato a todas las celdas según sus valores: ....................................................... 85

Aplicar Formato únicamente a las celdas que contengan: .................................................. 88

Aplicar Formato a los valores con rango inferior o superior: ................................................ 88

Aplicar Formato a los valores que estén por encima o por debajo del promedio: ............... 88

Aplicar Formato a los valores únicos o duplicados: ............................................................. 88

Utilice una fórmula que determine las celdas para aplicar formato: .................................... 89

4.2 Validación de Datos ........................................................................................................... 94

Capítulo 5: Tablas Dinámicas ................................................................................................... 101

Capítulo 6: Importación de Datos .............................................................................................. 106

6.1 Importación desde Texto ................................................................................................. 107

6.2 Importación desde la Web ............................................................................................. 109

6.3 Importación desde Access ............................................................................................. 111

6.4 Microsoft Query ............................................................................................................. 113

Capítulo 7: Macros .................................................................................................................... 126

7.1 Excel y Visual Basic para Aplicaciones ........................................................................... 126

7.2 Ventajas de VBA .............................................................................................................. 127

7.3 Desventajas de VBA ........................................................................................................ 128

7.4 La Grabadora de Macros ................................................................................................. 128

Page 4: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 3

Presentación

El presente manual de Excel Avanzado ha sido diseñado para usarse en cursos de

Microsoft Office Excel 2013 impartidos en el Programa de Administración de Unidades

de Servicios de la Universidad Nacional de Ingeniería para usuarios con conocimientos

previos de Excel.

Microsoft® Excel® 2013 ofrece características enriquecidas, nuevas y mejoradas para

el conjunto de aplicaciones de productividad más popular del mundo. Excel 2013

permite analizar, administrar y compartir información de más maneras que nunca y le

ayuda a tomar decisiones mejores y más inteligentes. Con las herramientas de análisis

y visualización de datos, junto con las tecnologías de inteligencia empresarial con

características de autoservicio administradas, y las nuevas herramientas de trabajo

skydrive para compartir información y guardar documentos en la red más grande del

mundo, puede crear perspectivas de negocios e información eficaces que realizan un

seguimiento de los datos importantes, resaltan las tendencias y comunican los

resultados mediante gráficos de gran calidad.

El texto empieza con una Introducción a Excel y sus versiones anteriores, además de

hacer una comparación con sus antecesores y analizar las novedades principales de la

versión 2013. Los acápites siguientes se enfocan en el uso de ciertos comandos

considerados necesarios para el uso y la administración de datos en un libro de cálculo,

reducción de errores, resaltar información en dependencia de los requerimientos del

usuario, complementos de trabajo para inteligencia de negocios, importación de datos

desde diferentes medio y automatización básica de procesos cotidianos a través de

macros que contribuyan a la reducción de tiempo en procesos monótonos de trabajo.

Aunque Excel fue diseñado como un programa para operaciones financieras y

contables hoy por hoy este programa es usado además por Estadistas, Ingenieros,

Administradores de proyectos, matemáticos, investigadores, médicos e incluso

abogados e historiadores, puesto que por su gran gama de comandos y funciones de

todo tipo ha sido de gran utilidad en el desarrollo de hojas de cálculo que contribuyen a

Page 5: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 4

reducir la carga de trabajo y aumentar la rapidez en el desarrollo de informes

especializados.

En el moderno mundo de hoy en día son muchas las empresas que adquieren un

software especializado para la administración de información y la ejecución de procesos

que facilitan en gran cantidad el trabajo diario, sin embargo muchos de estos están

diseñados bajo un marco fijo de trabajo y no están abiertos a posibles cambios en los

procesos, ya sea para mejorar o por factores externos (como los legales), es ahí donde

nos vemos en la necesidad de hacer uso de aquellas herramientas que ya poseemos

pero que no explotamos, y que podrán hasta cierto punto resolver muchas limitaciones

que los software más caros no pueden.

El curso y manual de Excel avanzado está estructurado para poder aprovechar al

máximo las ventajas que ofrece una de las herramientas más poderosas en el mundo

de las Tecnologías de Información, facilitando las operaciones a través de comandos y

funciones poco conocidos y de gran utilidad.

Page 6: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 5

Capítulo 1:

Introducción a Excel 2013

Microsoft Excel (Ms Excel) es un software diseñado para el manejo de hojas de cálculo,

desarrollado y distribuido por Microsoft, y utilizado normalmente en tareas financieras y

contables, aunque hoy por hoy las características que ofrece han superado estos

límites y barreras.

Ms Excel es una herramienta ofimática perteneciente al conjunto de programas

denominados hoja de cálculo electrónica, en la cual se puede escribir, almacenar,

manipular, calcular y organizar todo tipo de información numérico o de texto.

Ms Excel se encuentra dentro del paquete de herramientas o programas ofimáticos

llamados Office, el programa ofimático Excel es la hoja de cálculo electrónica más

extendida y usada a nivel global, siendo en la actualidad la herramienta complementaria

en las diferentes especialidades, ingeniería, finanzas, matemática, física o contabilidad.

Una hoja de cálculo electrónica se define como un programa informático compuesto por

columnas, filas y celdas, donde la intersección de las columnas y las filas son las

celdas, en el interior de cada celda es el sitio donde podemos escribir cualquier tipo de

información que posteriormente será tratada, siendo cada celda única en toda la hoja

de cálculo.

La principal ventaja del uso de las hojas de cálculo electrónicas reside en que es

posible interconectar unas celdas con otras mediante el uso de funciones o reglas, de

tal forma que si cambia el valor de una celda, automáticamente la hoja de cálculo

electrónica recalculará y actualizará los valores de las otras celdas. Esta ventaja fue el

origen y la base para impulsar el desarrollo de las hojas de cálculo electrónicas, debido

a que antiguamente al no disponer de dicha herramienta informática, el trabajo de

recalcular las diferentes hipótesis de un modelo matemático, físico o financiero

representaba un tiempo y esfuerzo enorme, además del riesgo de caer en algún error

durante el cálculo. Este problema fue solucionado mediante el desarrollo y uso de las

Page 7: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 6

hojas de cálculo electrónicas las cuales al cambiar cualquier dato del modelo en menos

de 1 segundo y sin error recalcula todo el modelo aportando un resultado totalmente

fiable.

La principal ventaja del Ms Excel es la versatilidad y funcionalidad que presenta a la

hora de realizar cualquier tipo de modelo, con Excel podemos generar hojas para el

diseño y cálculo de estructuras civiles, gestión de riesgos, control contable de una

empresa, gestión y control de los stocks de un almacén, diseños de modelos

matemáticos, gestión de bases de datos, generación de presupuestos, planificación de

proyectos, etc…. Un amplio abanico de posibilidades se puede cubrir con el uso del

programa Ms Excel.

1.1 Historia Y Versiones

Microsoft comercializó originalmente un programa de Hoja de cálculo llamado Multiplan

en 1982, que fue muy popular en los sistemas CP/M, pero en los sistemas MS-DOS

perdió popularidad frente al Lotus 1-2-3. Posteriormente Microsoft publicó la primera

versión de Excel para Mac en 1985, y la primera versión de Windows (numeradas 2.05

en línea con el Mac y con un paquete de tiempo de ejecución de entorno de Windows)

en noviembre de 1987. Lotus fue lent | a al llevar 1-2-3 para Windows y esto ayudó a

Microsoft a alcanzar la posición de los principales desarrolladores de software para hoja

de cálculo de PC solidificándolo como un competidor válido y mostró su futuro como

desarrollador de software GUI. Microsoft empujó su ventaja competitiva lanzando al

mercado nuevas versiones de Excel, por lo general cada dos años. La versión actual es

Excel 15.0, también denominada Microsoft Excel 2013.

La lista de versiones de Excel que han sido lanzadas al mercado para Microsoft

Windows es:

Page 8: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 7

Excel fue la primera hoja de cálculo que permite al usuario definir la apariencia (las

fuentes, atributos de carácter y celdas). También introdujo computación inteligente de

datos, donde existen celdas dependientes de otras celdas se actualizan al instante

(programas de hoja de cálculo anterior recalculaban la totalidad de los datos cada cierto

tiempo o esperaban para un comando específico del usuario). Excel tiene una amplia

capacidad gráfica, y permite a los usuarios realizar, entre otras muchas aplicaciones,

listados usados en combinación de correspondencia con Microsoft Word.

Desde 1993, Excel ha incluido Visual Basic para Aplicaciones (VBA), un lenguaje de

programación basado en Visual Basic, que añade la capacidad para automatizar tareas

en Excel y para proporcionar funciones definidas por el usuario para su uso en las hojas

de trabajo. VBA es una poderosa anexión a la aplicación que, en versiones posteriores,

incluye un completo entorno de desarrollo integrado (IDE). La grabación de macros

puede producir código (VBA) para repetir las acciones del usuario, lo que permite la

automatización de simples tareas. (VBA) permite la creación de formularios y controles

en la hoja de trabajo para comunicarse con el usuario. Admite el uso del lenguaje (pero

no la creación) de las DLL de ActiveX (COM); versiones posteriores añadieron soporte

para los módulos de clase permitiendo el uso de técnicas de programación básicas

orientadas a objetos.

La funcionalidad de la automatización proporcionada por (VBA) originó que Excel se

convirtiera en un objetivo para virus en macros. Este fue un grave problema en el

Page 9: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 8

mundo corporativo hasta que los productos antivirus comenzaron a detectar estos virus.

Microsoft tomó medidas tardíamente para prevenir el uso indebido de Excel mediante la

adición de la capacidad para deshabilitar la ejecución automática de las macros al abrir

un archivo.

1.2 Especificaciones, Límites y Problemas

Las características, especificaciones y límites de Excel han variado considerablemente

de versión en versión, exhibiendo cambios en su interfaz operativa y capacidades

desde el lanzamiento de su versión 12.0 mejor conocida como Excel 2007. Se puede

destacar que mejoró su límite de columnas ampliando la cantidad máxima de columnas

por hoja de cálculo de 256 a 16.384 columnas. De la misma forma fue ampliado el límite

máximo de filas por hoja de cálculo de 65.536 a 1.048.576 filas por hoja. Otras

características también fueron ampliadas, tales como el número máximo de hojas de

cálculo que es posible crear por libro que pasó de 256 a 1.024 o la cantidad de memoria

del PC que es posible emplear que creció de 1 GB a 2 GB soportando además la

posibilidad de usar procesadores de varios núcleos.

Uno de los problemas conocidos y relevantes de esta hoja de cálculo, es el hecho de su

incapacidad para manejar fechas anteriores a 1900 (incluyendo versiones para Mac OS

X), es decir, no puede manejar campos en formato de fecha anteriores a dicho año

(como acontecimientos históricos). Este problema se ha venido presentando desde

versiones más antiguas de 16 bits, persistiendo aún en la versión actual.

El 22 de septiembre del 2007 se informó que la hoja de cálculo Excel 2007 mostraba

resultados erróneos bajo ciertas condiciones. Particularmente para algunas parejas de

números, para los que el producto sea 65535 (tales como 850 y 77.1), Excel muestra

como resultado de la operación 100000. Esto ocurre con alrededor del 14.5% de tales

pares. Además, si se suma uno a este resultado Excel lo calcula como 100001. No

obstante, si se resta uno al resultado original, entonces muestra el valor correcto 65534

(también si se multiplica o divide por 2, muestra los valores correctos 131070 y 32767.5,

respectivamente).

Page 10: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 9

Dada la importancia que posee el estudio de las finanzas en ámbito académico, toma

especial interés el manejo de dichos conceptos de forma ágil, para conseguir rapidez y

precisión en el análisis. La forma más rápida de conseguir dicho objetivo es a través de

la aplicación de finanzas en una hoja de cálculo, para el caso, Excel. Las hojas de

cálculo proporcionan una solución rápida y sencilla a las nuevas exigencias que se

presentan en el mundo actual. Excel a través de un ambiente gráfico permite realizar

operaciones matemáticas básicas y complejas y desarrollar diferentes funciones

(financieras, lógicas, estadísticas, matemáticas, etc.).

Microsoft Excel 2013 permite analizar, administrar y compartir información de más

formas que nunca, lo que le ayuda a tomar decisiones mejores y más inteligentes. Las

nuevas herramientas de análisis y visualización le ayudan a realizar un seguimiento y

resaltar importantes tendencias de datos. Obtenga acceso fácilmente a datos

importantes dondequiera que vaya desde prácticamente cualquier explorador web o

Smartphone. Incluso puede cargar sus archivos en Internet y trabajar simultáneamente

con otras personas en línea. Tanto como si produce informes financieros o administra

sus gastos personales, Excel 2013 brinda más eficiencia y flexibilidad para lograr sus

objetivos.

1.3 Las novedades de Excel 2013

Lo primero que verá cuando abra Excel es un aspecto totalmente nuevo. Es más limpio

pero también está diseñado para ayudarle a lograr resultados profesionales con

rapidez. Encontrará muchas características nuevas que le permiten olvidarse de

barreras de números y dibujar imágenes más persuasivas de sus datos, guiándole

hacia decisiones mejores y más fundamentadas.

Inicio Rápido

Las plantillas realizan la mayoría del trabajo de diseño y configuración por usted, por lo

que se puede centrar en sus datos. Al abrir Excel 2013, verá plantillas para

presupuestos, calendarios, formularios, informes. etc.

Page 11: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 10

Análisis de Datos Instantáneos

La nueva herramienta Análisis rápido permite convertir los datos en un gráfico o tabla

en dos pasos o menos. Obtenga una vista previa de los datos con formato condicional,

minigráficos o gráficos, y haga que su elección se mantenga con solo un clic del ratón.

Solía requerir algo de trabajo analizar sus datos pero ahora solo son necesarios unos

pasos. Puede crear al instante diferentes tipos de gráficos, incluidos: gráficos de líneas

y columnas, o agregar gráficos en miniatura (denominados minigráficos). También

puede aplicar un estilo de tabla, crear tablas dinámicas, insertar totales con rapidez y

aplicar formato condicional.

Rellenar una columna completa de datos en un abrir y cerrar de ojos

Relleno rápido es como un asistente de datos que termina el trabajo por usted. Tan

pronto como detecta lo que desea hacer, Relleno rápido introduce el resto de los datos

de una sola vez, siguiendo el patrón que reconoce en los datos. Para ver cuándo esta

característica resulta especialmente útil.

Page 12: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 11

Crear un gráfico adecuado para los datos

Con las Recomendaciones de gráficos, Excel recomienda los gráficos más adecuados

para los datos. Eche un vistazo rápido para ver el aspecto de los datos en los distintos

gráficos y elija aquel en el que se muestren mejor las ideas que desea presentar.

Filtrar datos de tabla usando segmentaciones de datos

Introducida por primera vez en Excel 2010 como una manera interactiva de filtrar datos

de tabla dinámica, la segmentación de datos puede filtrar datos en tablas de Excel,

tablas de consultas y otras tablas de datos. Más sencilla de configurar y de usar, la

segmentación de datos muestra el filtro actual para que pueda saber exactamente qué

datos está mirando.

Un libro, una ventana

En Excel 2013 cada libro tiene dentro su propia ventana, facilitando el trabajo en dos

libros a la vez. Hace la vida más sencilla cuando trabaja en dos monitores.

Nuevas funciones de Excel

Encontrará varias funciones nuevas en las categorías de función de matemáticas,

trigonometría, estadística, ingeniería, fecha y hora, búsqueda y referencia, lógica y

texto. También son novedades algunas funciones de servicio web para hacer referencia

a servicios web compatibles con la Transferencia de estado de representación (REST)

existente.

Page 13: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 12

Capítulo 2:

Segmentación de Datos y Filtros

Como todos conoceremos, cuando un usuario de Excel utiliza la herramienta de Filtros,

se colocan de forma automática los controles de filtrado en los encabezados de tabla,

tal como se muestra en la siguiente pantalla:

A manera de repaso, recordemos que:

Para un filtrado rápido:

1. Haga clic en la flecha del encabezado de tabla de

la columna que desea filtrar.

2. En la lista de texto o números, desactive la casilla

(Seleccionar todo) de la parte superior de la lista y, a

continuación, active las casillas de los elementos que

desea mostrar en su tabla.

Observación: Para ver más elementos en la lista,

arrastre el controlador de la esquina inferior derecha de

la galería de filtros para ampliarla.

3. Haga clic en Aceptar.

Page 14: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 13

La flecha de filtrado del encabezado de tabla cambia a este icono para indicar que

hay un filtro aplicado. Haga clic en el filtro para cambiarlo o borrarlo.

Filtrar por texto o números específicos

1. Haga clic en la flecha del encabezado de tabla de la columna que desea

filtrar.

2. Si la columna tiene números, haga clic en Filtros de número. Si la columna

tiene entradas de texto, haga clic en Filtros de texto.

3. Elija la opción de filtrado que desee y, a continuación, introduzca sus condiciones

de filtrado.

Por ejemplo, para mostrar números por encima de una cantidad determinada, elija

Mayor o igual que y, a continuación, introduzca el número que está pensando en el

cuadro adyacente.

Page 15: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 14

Para filtrar por dos condiciones, introduzca las condiciones de filtrado en ambos

conjuntos de cuadros y elija Y para que ambos sean verdadero y O para que cualquiera

de las condiciones sea verdadera.

Filtrar elementos por color

Si ha aplicado diferentes colores de celda o de fuente o un formato condicional, puede

filtrar por los colores o los iconos que se muestran en la tabla.

1. Haga clic en la flecha del

encabezado de tabla de la

columna que tiene formato de

color o formato condicional

aplicado.

2. Haga clic en Filtrar por color y,

a continuación, elija el color de

celda, el color de fuente o el

icono por el que desea filtrar.

Los tipos de opciones de color que

tendrá dependerán de los tipos de

formato que haya aplicado.

2.1 Segmentación de datos

Microsoft Excel 2010 incorporó segmentaciones de datos como una nueva manera de

filtrar datos de tabla dinámica. En Excel 2013, también se puede crear segmentaciones

de datos para filtrar sus datos de tabla. Una segmentación de datos es realmente útil,

porque indica claramente qué datos se muestran en la tabla tras filtrar los datos.

Page 16: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 15

Esta es la manera en que puede crear una para filtrar sus datos:

1. Haga clic en cualquier lugar de la tabla para mostrar Herramientas de tabla de

la cinta de opciones.

2. Haga clic en Diseño > Insertar Segmentación de datos.

3. En el cuadro de diálogo Insertar Segmentación de datos, active las casillas

para las que desea crear segmentaciones de datos.

4. Haga clic en Aceptar.

Aparecerá una segmentación de datos para cada encabezado de tabla que ha

activado en el cuadro de diálogo Insertar Segmentación de datos.

Page 17: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 16

5. En cada segmentación de datos, haga clic en los elementos que desea mostrar

en su tabla.

Para elegir más de un elemento, mantenga presionada la tecla Ctrl y, a

continuación, elija los elementos que desea mostrar.

Nota: Para cambiar el aspecto de las segmentaciones de datos, haga clic en la

segmentación de datos para mostrar las Herramientas de segmentación de datos

en la cinta de opciones y, a continuación, aplique un estilo de segmentación de

datos o cambie la configuración de la pestaña Opciones.

2.2 Filtros

Filtrar una lista no es ni más ni menos que de todos los registros almacenados en una

tabla, seleccionar aquellos que correspondan con algún criterio determinado por el

usuario.

Excel ofrece dos formas de filtrar información:

- Utilizando el Filtro (autofiltro).

- Utilizando filtros avanzados.

Los filtros avanzados son útiles, en especial, cuando estamos trabajando con bases de

datos en Excel; si bien conviene recordar que Excel es una Hoja de cálculo y no un

gestor de Bases de datos, como lo es Ms Access. Podemos suponer que tenemos una

Base de datos en una hoja de cálculo, y que pretendemos aplicar filtros avanzados,

para encontrar algunos registros; ello en aquellas ocasiones en que los Autofiltros se

quedan “cortos” en función de lo que se pretende obtener; los Filtros Avanzados

desaparecen las restricciones o limitaciones que pudiese tener el Autofiltro.

En primer lugar recordaremos desde donde activamos esta herramienta avanzada; si

utilizamos Ms Excel 2003 navegaremos por el Menú Datos > Filtro > Filtro avanzado; si

nos decidimos por versiones posteriores incluyendo la versión 2013 iremos al Menú

Datos > Ordenar y Filtrar >Avanzadas.

Page 18: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 17

Para poder aplicar esta herramienta avanzada debemos saber que además de nuestra

base de datos, necesitamos un rango de celdas a parte en donde indicar cuáles son los

criterios de filtro, es decir, dos rangos:

1. Rango de la lista, y

2. Rango de criterios.

En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando

los criterios que deseas aplicar a la información, en la figura que se muestra a

continuación se visualiza la ventana que se habilita para que el usuario determine las

propiedades del filtro avanzado:

A continuación se muestra su uso, en el escenario de copiar los datos filtrados en otro

lugar:

Supongamos la siguiente tabla de datos.

Page 19: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 18

Los criterios por los cuales se realizará el filtrado deben especificarse dentro de celdas

de la misma hoja. Supongamos que deseo filtrar los registros del departamento de

Finanzas.

Para este ejemplo se colocaron los criterios por arriba de la tabla de datos aunque

realmente su ubicación no es de suma importancia.

Page 20: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 19

Antes de aplicar el filtro avanzado se debe seleccionar la tabla de datos (A4:D13) y

posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el

grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.

Observa cómo en el cuadro de texto para Rango de criterios se ha seleccionado el

rango que contiene las condiciones del filtro avanzado necesarias. Solamente resta

pulsar el botón Aceptar para aplicar el filtro.

El resultado es precisamente lo que se requiere, así que el filtrado avanzado ha sido

exitoso. Pero ahora ha lugar a un desafío mayor ¿Cómo podría hacer para

Page 21: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 20

especificar condiciones para una segunda columna? Supongamos que se requiere

filtrar la información de las personas que tienen apellido Hernández y que además

pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios se

debe agregar dichas condiciones en otra celda.

Observa cómo el Rango de la lista es el mismo que en el caso anterior ya que son los

mismos datos, pero el Rango de criterios ahora abarca también las celdas que

contienen el criterio para el Apellido. Al aceptar los cambios Excel aplicará el filtro

avanzado adecuadamente.

Page 22: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 21

De la misma manera puedes especificar un criterio para cada columna. Hasta ahora

solamente se ha especificado una sola condición por columna, pero igual se puede

agregar a los resultados del filtro la información del departamento de Informática. Para

ello solamente se agregará una fila adicional al rango de criterios de la siguiente

manera.

Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con

apellido Hernández y además mostrará la información de los empleados del

departamento de Informática sin importar el apellido que tengan.

Page 23: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 22

En la opción de filtros avanzados, el usuario siempre tiene dos opciones:

Filtrar el rango de la lista ocultando las filas que no cumplen los criterios, para

ello haga clic en Filtrar la lista sin moverla a otro lugar.

Filtrarlo copiando las filas que cumplen los criterios a otra área de la hoja de

cálculo, haga clic en Copiar a otro lugar, después en la casilla Copiar a y, por

último, en la esquina superior izquierda del área donde desea pegar las filas.

Observación: Al copiar filas filtradas en otra ubicación, se pueden especificar las

columnas que se deben incluir en la operación de copia. Antes de filtrar, copie las

etiquetas de columna de las columnas deseadas en la primera fila del área donde va a

pegar las filas filtradas. Cuando filtre, escriba una referencia a las etiquetas de columna

copiadas en el cuadro Copiar en. De este modo, las filas copiadas incluirán solo las

columnas cuyas etiquetas se hayan copiado.

Nota El comando Avanzadas funciona de forma diferente del comando Filtrar en

varios aspectos importantes.

- Muestra el cuadro de diálogo Filtro avanzado en vez del menú de filtro

automático.

- Los criterios avanzados se escriben en un rango de criterios

independiente en la hoja de cálculo y sobre el rango de celdas o la tabla que

desee filtrar. Microsoft Office Excel usa el rango de criterios independiente del

cuadro de diálogo Filtro avanzado como el origen de los criterios avanzados.

Page 24: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 23

Considerar la distinción entre mayúsculas y minúsculas

Cuando filtra datos de texto, Excel no distingue entre mayúsculas y minúsculas. Sin

embargo, puede usar una fórmula para realizar una búsqueda que distinga entre

mayúsculas y minúsculas.

Utilizar nombres predefinidos

Se puede asignar a un rango el nombre Criterios y la referencia del rango aparecerá

automáticamente en el cuadro Rango de criterios. También se puede definir el

nombre Base de datos para el rango de la lista que se debe filtrar y definir el

nombre Extraer para el área donde desea pegar las filas, y estos rangos aparecerán

automáticamente en los cuadros Rango de la lista y Copiar a, respectivamente.

Page 25: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 24

Capítulo 3:

Estructura de Datos en Ms Excel y Funciones

3.1 Entorno y estructura de Ms Excel

El entorno de trabajo básico de Microsoft Excel es un archivo de libro de trabajo que

puede contener una o más hojas de cálculo. Una hoja de cálculo es similar a un libro de

contabilidad, con números, texto y resultados de cálculos alineados en columnas y filas.

Pero a diferencia de un libro de contabilidad, cuando introduce los números en

Microsoft Excel, el programa es el que ejecuta los cálculos en su lugar y de forma

electrónica.

Con Microsoft Excel, es fácil la introducción de información dentro de una hoja de

cálculo y el poder cambiar, suprimir o añadir datos a dicha información. No tiene que

preocuparse si inicialmente sus datos no han quedado perfectamente introducidos o no

lo han hecho de forma completa. Siempre podrá cambiarlos o terminar de introducirlos

más tarde. Puede ordenar varias hojas dentro de un libro de trabajo (por ejemplo puede

colocar en un libro todas las hojas que pertenezcan a un solo cliente o a un único

proyecto) y a continuación póngales un nombre para poder localizar rápidamente la

información que necesita.

En esta lección, aprenderá cómo tiene que trabajar con las hojas y con los libros de

trabajo; a trabajar de forma más eficaz si utiliza las prestaciones de AutoCompletar y

AutoCorrección que le ayudarán a realizar parte de su trabajo y a introducir fórmulas

complejas y formulas y funciones anidadas.

Las fórmulas son ecuaciones que pueden realizar cálculos, devolver información,

manipular el contenido de otras celdas, comprobar condiciones, etc. Una fórmula

siempre comienza con el signo igual (=).

A continuación, se ofrece una muestra de los tipos de fórmulas que se pueden escribir

en una hoja de cálculo.

=5+2*3 Suma 5 al resultado de multiplicar 2 por 3.

=A1+A2+A3 Suma los valores de las celdas A1, A2 y A3.

Page 26: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 25

=RAIZ (A1) Usa la función RAIZ para devolver la raíz cuadrada del valor

contenido en A1.

=HOY () Devuelve la fecha actual.

=MAYUSC ("hola") Convierte el texto "hola" en "HOLA" mediante la función de

hoja de cálculo MAYUSC.

=A1>0 Comprueba si la celda A1 contiene un valor mayor que 0.

Una fórmula también puede contener funciones, referencias, operadores y constantes.

Los operadores especifican el tipo de cálculo que desea ejecutar en los elementos de

una fórmula. Existe un orden predeterminado en el que tienen lugar los cálculos (que

sigue las reglas matemáticas generales), pero puede cambiar este orden utilizando

paréntesis.

3.2 Administrador de nombres

Los rangos en Ms Excel se pueden definir mediante las referencias de su ubicación o

mediante un nombre que se la asigna a un determinado rango.

Por ejemplo, un rango pueden ser las celdas que se encuentran en la columna A desde

la fila 1 hasta la fila 20. El rango sería A1:A20. O las celdas que están desde la columna

A hasta la columna C y desde la fila 2 hasta a fila 80. El rango sería A2:C80.

Pero tal rango también lo podríamos identificar con un nombre alusivo al tipo de datos

que contiene.

Tipos de nombres

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,

Page 27: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 26

etc., pero podremos editarlo desde el administrador de nombres para que sea más

descriptivo.

Por ejemplo, si la lista de empleados está en el rango A2:A10, podemos asignarle el

nombre de “Empleados” a ese rango.

Para ello en MS Excel 2013 seleccionamos el rango de datos y nos vamos a la opción

de Fórmulas -> Nombres Definidos -> Asignar nombre y tenemos el siguiente

cuadro:

Automáticamente se despliega el siguiente cuadro de diálogo:

Page 28: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 27

Como se puede apreciar en la figura anterior, no es condición requerida que antes de ir

a la opción de Asignar nombre se haya elegido el rango, ya que en el cuadro de diálogo

respectivo se pueden modificar los rangos conforme a los requerimientos dados.

Un punto muy importante es que si hay diferentes hojas de trabajo se indique a qué

hoja pertenece cada rango definido. Cuando se selecciona al rango de datos antes de

crear el nombre de rango, Excel agrega automáticamente el nombre de la hoja a la que

pertenece el rango. Asimismo, es importante aclarar que cuando se crea directamente

el nombre del rango, hay que indicarle a Excel a qué hoja pertenece ese rango, ya que

todas las hojas tienen los mismos rangos, refiérase a la misma nomenclatura de las

filas y columnas.

Administrar nombres

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á un cuadro de diálogo:

Page 29: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 28

Este cuadro de diálogo mostrará la lista completa de nombres de nuestro libro y

podremos también crear desde ahí nuevos nombres o editar los existentes. Asimismo el

usuario podrá eliminar cualquier nombre que deseemos pulsando el botón Eliminar.

El siguiente ejemplo se muestra un libro en donde hay 4 definiciones de nombres a

objetos, como se podrá apreciar las opciones: “Nuevo”, “Editar”, “Eliminar”, se

encuentran activas, dando al usuario la oportunidad de ejecutar cualquiera de las tres

opciones.

Page 30: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 29

Eliminar uno o más nombres

1. En el grupo Nombres definidos de la ficha Fórmulas, haga clic en

Administrador de nombres.

2. En el cuadro de diálogo Administrador de nombres, haga clic en el nombre que

desee cambiar.

3. Seleccione uno o más nombres mediante uno de los siguientes procedimientos:

Para seleccionar un nombre, haga clic en él.

Para seleccionar más de un nombre en un grupo contiguo, haga clic y

arrastre los nombres, o bien presione Mayús y haga clic con el botón del

mouse en cada nombre del grupo.

Para seleccionar más de un nombre en un grupo no contiguo, presione

CTRL y haga clic con el botón del mouse en cada nombre del grupo.

4. Haga clic en Eliminar. También puede presionar SUPR.

5. Haga clic en Aceptar para confirmar la eliminación.

¿Para qué sirve asignar un nombre a un conjunto de celdas?

Una vez creado el nombre del rango, en lugar de escribir la referencia de celdas en las

fórmulas se escribe el nombre del rango. Por ejemplo:

En lugar de escribir

=BUSCARV(D1;Hoja1!A2:A10;1;0) se escribe

=BUSCARV(D1;Empleados;3;0)

Page 31: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 30

En este sentido, si el usuario utiliza nombres, sus fórmulas serán mucho más fáciles de

entender y mantener. Puede definir un nombre para un rango de celdas, una función,

una constante o una tabla. Una vez que haya adoptado la práctica de utilizar nombres

en su libro, podrá actualizar, auditar y administrar esos nombres con facilidad.

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 nombre se deberá pulsar el botón Pegar lista

y Excel insertará en la hoja los nombres de los rangos así como su definición:

3.3 Generalidades de las funciones

Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores

específicos, denominados argumentos, en un determinado orden o estructura. Las

funciones pueden utilizarse para ejecutar operaciones simples o complejas.

Page 32: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 31

Cuando cree una fórmula que contenga una función, puede usar el cuadro de diálogo

Insertar función como ayuda para especificar las funciones de la hoja de cálculo. A

medida que se escriba una función en la fórmula, el cuadro de diálogo Insertar función

irá mostrando el nombre de la función, cada uno de sus argumentos, una descripción

de la función y de cada argumento, el resultado actual de la función y el resultado actual

de toda la fórmula.

Para facilitar la creación y edición de fórmulas, y minimizar los errores de escritura y

sintaxis, utilice la característica Fórmula Autocompletar. Después de escribir un =

(signo igual) y las letras iniciales o un desencadenador de visualización, Excel muestra

debajo de la celda una lista desplegable dinámica de funciones, argumentos y nombres

válidos que coinciden con las letras o con el desencadenador de visualización. A

continuación, puede insertar un elemento de la lista desplegable en la fórmula.

En algunos casos, puede que deba utilizar una función como uno de los argumentos

(argumento: valores que utiliza una función para llevar a cabo operaciones o cálculos.

El tipo de argumento que utiliza una función es específico de esa función. Los

argumentos más comunes que se utilizan en las funciones son números, texto,

referencias de celda y nombres de otra función.

3.4 Referencias

Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica

a Excel dónde debe buscar los valores o los datos que desea utilizar en una fórmula.

Las referencias permiten utilizar datos de distintas partes de una hoja de cálculo en una

fórmula, o bien utilizar el valor de una celda en varias fórmulas. También puede hacerse

referencia a las celdas de otras hojas en el mismo libro y de otros libros. Las referencias

a celdas de otros libros se denominan vínculos o referencias externas (referencia

externa: referencia a una celda o a un rango en una hoja de otro libro de Excel o una

referencia a un nombre definido en otro libro.)

Referencias relativas

Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la

celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la

Page 33: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 32

posición de la celda que contiene la fórmula, cambia la referencia. Si se copia o se

rellena la fórmula en filas o columnas, la referencia se ajusta automáticamente. De

forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si

copia o rellena una referencia relativa de la celda B2 en la celda B3, se ajusta

automáticamente de =A1 a =A2.

Referencias absolutas

Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia

a una celda en una ubicación específica. Si cambia la posición de la celda que contiene

la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o

columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas

fórmulas utilizan referencias relativas, de modo que puede resultar necesario

cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la

celda B2 en la celda B3, permanece invariable en ambas celdas: =$A$1.

Referencias mixtas

Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y

una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1,

etc. Una referencia de fila absoluta adopta la forma A$1, B$1, etc. Si cambia la posición

de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia

absoluta permanece invariable. Si se copia o rellena la fórmula en filas o columnas, la

referencia relativa se ajusta automáticamente y la referencia absoluta no se ajusta. Por

ejemplo, si se copia o rellena una referencia mixta de la celda A2 en B3, se ajusta de

=A$1 a =B$1.

Page 34: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 33

Referencias Externas

Una referencia externa (también denominada vínculo) es una referencia a una celda o a un

rango de celdas de una hoja de cálculo de otro libro de Excel, o una referencia a un nombre

definido en otro libro. Puede hacer referencia a rangos de celdas específicas, a un nombre

específico de los rangos de celdas, o bien definir un nombre para la referencia externa.

Si utiliza una referencia de celda para crear una referencia externa, también puede aplicar

fórmulas a la información. Al cambiar entre los distintos tipos de referencias de celda, puede

controlar además las celdas con las que se establecen vínculos si mueve la referencia

externa. Por ejemplo, si utiliza una referencia relativa, al mover la referencia externa, la

celda o celdas con las que se establece el vínculo cambian para reflejar su nueva posición

en la hoja de cálculo.

Cuando se crea una referencia externa de un libro a otro, debe utilizar un nombre para

hacer referencia a las celdas con las que se establece el vínculo. Puede crear una

referencia externa utilizando un nombre que ya se haya definido, o puede definir el nombre

cuando cree la referencia externa. Este nombre permite recordar más fácilmente el

contenido de las celdas con las que se establece el vínculo. Las referencias externas que

utilizan nombres definidos no cambian cuando se mueven, ya que un nombre hace

referencia a una celda o rango de celdas específico. Si desea utilizar una referencia externa

con un nombre definido que cambie al moverse, puede cambiar el nombre que se utiliza en

la referencia externa o puede cambiar las celdas a las que hace referencia el nombre.

Las fórmulas con referencias externas a otros libros se muestran de dos maneras, en

función de si el libro de origen, el que proporciona datos a la fórmula, se encuentra abierto o

cerrado. Cuando el libro de origen está abierto, la referencia externa incluye el nombre del

libro entre corchetes ([ ]), seguido del nombre de la hoja de cálculo, un signo de

exclamación (!) y las celdas de las que depende la fórmula. Por ejemplo, la siguiente

fórmula suma las celdas C10:C25 del libro Presupuesto.xls.

=SUMA([Presupuesto.xlsx]Anual!C10:C25)

Si el libro de origen está cerrado, la referencia externa incluye toda la ruta de acceso.

Page 35: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 34

=SUMA('C:\Informes\[Presupuesto.xlsx]Anual'!C10:C25)

Referencias en Macros

También puede utilizarse un estilo de referencia en el que se numeren tanto las filas

como las columnas de la hoja de cálculo. El estilo de referencia F1C1 es útil para

calcular las posiciones de fila y columna en macros (macro: acción o conjunto de

acciones utilizados para automatizar tareas. Las macros se graban en el lenguaje de

programación de Visual Basic para Aplicaciones.). En el estilo F1C1, Excel indica la

ubicación de una celda con una "F" seguida de un número de fila y una "C" seguida de

un número de columna.

Referencia Significado

F[-2]C Referencia relativa a la celda situada dos filas por encima, en

la misma columna.

F[2]C[2] Referencia relativa a la celda situada dos filas hacia abajo y

dos columnas hacia la derecha.

F2C2 Referencia absoluta a la celda de la segunda fila y la segunda

columna.

F[-1] Referencia relativa a toda la fila situada sobre la celda activa

F Referencia absoluta a la fila actual

Al grabar una macro, Excel registra algunos comandos utilizando el estilo de

referencia F1C1. Por ejemplo, si se registra un comando como hacer clic en el

botón Autosuma para insertar una fórmula que suma un rango de celdas, Excel

registra la fórmula utilizando referencias del estilo F1C1 y no del estilo A1.

3.5 Funciones

Funciones de complementos y automatización

Funciones de cubo

Funciones de base de datos

Funciones de fecha y hora

Funciones de ingeniería

Page 36: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 35

Funciones financieras

Funciones de información

Funciones lógicas

Funciones de búsqueda y referencia

Funciones matemáticas y trigonométricas

Funciones estadísticas

Funciones de texto

Funciones Definidas por el usuario

Funciones Lógicas

Función Lógica Si

La función Si es una de las más poderosas que tiene EXCEL, devuelve un valor si

una condición especificada se evalúa como VERDADERO y otro valor si se evalúa

como FALSO. . La sintaxis de la función es:

=SI (prueba_logica, valor_verdadero, valor_falso)

En donde;

Prueba_logica: Cualquier valor o expresión que pueda evaluarse como

VERDADERO o FALSO.

Valor_verdadero: El valor que desea que se devuelva si el argumento

prueba_lógica se evalúa como VERDADERO.

Valor_falso: El valor que desea que se devuelva si el argumento prueba_lógica se

evalúa como FALSO.

Por ejemplo, la fórmula siguiente devuelve "Más de 10" si A1 es mayor que 10 y "10

o menos" si A1 es menor o igual que 10.

=SI(A1>10,"Más de 10","10 o menos")

Cada función SI, deberá contener al menos un operador lógico dentro de su prueba

lógica para poder retornar un valor verdadero o falso, según corresponda la

Page 37: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 36

evaluación de la prueba. Por ejemplo, en una tienda de ropa se ha decidido dar el

15% de descuento a todos los clientes que paguen de contado.

A B

1 Forma de Pago Contado

2 Venta 15’000

3 Precio unitario C$ 15°°

4 Descuento =SI (B1=“Contado”,15%*B2*B3,0)

5 Total =(B2*B3) – (B2*B3*B4)

Es posible utilizar el resultado de otra función como la prueba lógica que necesita la

función SI siempre y cuando esa otra función regrese como resultado

VERDADERO o FALSO. Un ejemplo de este tipo de función es la función

ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor

VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero

desplegar la leyenda “SI” en caso de que la celda de la columna A efectivamente

tenga un número, de lo contrario se mostrará la leyenda “NO”.

A B C

1 VALOR ¿ES NÚMERO? RESPUES

TA

2 Uno =SI

(ESNUMERO(A2),”SI”,”NO”)

NO

3 2 =SI

(ESNUMERO(A3),”SI”,”NO”)

SI

4 3 =SI

(ESNUMERO(A4),”SI”,”NO”)

SI

5 cuatro =SI

(ESNUMERO(A5),”SI”,”NO”)

NO

Page 38: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 37

Función Lógica Si Anidada

El anidamiento está referido al uso de una función dentro de una fórmula, es decir

escribir una función dentro de otra. Las funciones si anidadas aumentan la cantidad

de resultados posibles, para lo que se debe reemplazar los argumentos

VERDADERO o FALSO, en dependencia de la evaluación y las respuestas

deseadas. A continuación se muestran la sintaxis para poder obtener más de dos

respuestas diferentes con esta función.

=SI (prueba_logica, valor_verdadero, SI (prueba_logica, valor_verdadero,

valor_falso))

=SI (prueba_logica, SI (prueba_logica, valor_verdadero, valor_falso), valor_falso)

=SI (prueba_logica, valor_verdadero, valor_falso)

Por ejemplo, necesito conocer la comisión de un vendedor de acuerdo a las ventas

realizadas, si son mayores a C$20’000 su comisión será de 5%, si es mayor a

C$10’000 será de 3% si es menor a C$10’000 será de 1%:

Vendedor Ventas Función Respuesta

Carlos

Cárcamo

C$

15’000

=SI(B2>=20000,5%,SI(B2>=10000,3%,1

%)) 3%

Camilo

Conrado

C$ 9’000 =SI(B3>=20000,5%,SI(B3>=10000,3%,1

%)) 1%

Casilda

Castro

C$

21’000

=SI(B4>=20000,5%,SI(B4>=10000,3%,1

%)) 5%

Celia

Castillo

C$ 5’000 =SI(B5>=20000,5%,SI(B5>=10000,3%,1

%)) 1%

Celina

Conde

C$

18’000

=SI(B6>=20000,5%,SI(B6>=10000,3%,1

%)) 3%

Page 39: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 38

Aunque la función SI permite agregar gran cantidad de funciones anidadas, es

recomendable no complicar demasiado la misma puesto que para ellos existen

funciones adicionales que puedan ayudarle, tal es el caso de la función

CONSULTAV o la función BUSCARV.

Con el diseño de la función Si en Microsoft Excel, se han derivado muchas otras

funciones que parten de esta, tal es el caso de:

SUMAR.SI: Suma las celdas en el rango que coinciden con el argumento criterio.

=SUMAR.SI (rango,criterio,rango_suma)

En donde;

Rango: Es el Rango de celdas que desea evaluar.

Criterio: es el criterio en forma de número, expresión o texto, que determina las

celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32,

"32" ">32", "manzanas".

Rango_Suma: Son las celdas que se van a sumar.

Por ejemplo, de una lista de Ventas es deseable sumar únicamente aquellas que

corresponden a la Sucursal Sur Oriente.

Page 40: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 39

=SUMAR.SI(A2:A6,”SUR ORIENTE”,B2:B6)

SUMAR.SI.CONJUNTO: Agrega las celdas de un rango que cumpla con varios

criterios. Por ejemplo, si desea sumar los números del rango A1:A20 sólo si los

números de B1:B20 correspondientes son mayores que cero (0) y los números de

C1:C20 correspondientes son menores que 10.

=SUMAR.SI.CONJUNTO

(rango_suma,rango_criterios1,criterios1,rango_criterios2,criterios2,...)

En donde;

RANGO_SUMA: Una o más celdas para sumar, incluidos números o nombres,

rangos o referencias de celda que contengan números. Se omiten los valores en

blanco o de texto.

RANGO_CRITERIOS1: El primer rango en el que se evalúan los criterios asociados.

CRITERIOS1: Los criterios en forma de número, expresión, referencia de celda o

texto que define qué celdas del argumento rango_criterios1 se agregarán.

RANGO_CRITERIOS2, CRITERIOS2… Rangos adicionales y sus criterios

asociados. Se permiten hasta 127 pares de rangos/criterios.

Sucursal (A)

Ventas

(B)

NORTE

C$

15’000

NOR ESTE

C$

19’000

SUR

ORIENTE

C$

14’000

SUR

OCCIDENTE

C$

25’000

SUR

ORIENTE

C$

8’000

Page 41: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 40

Por ejemplo, de una lista de Ventas se requiere conocer la cantidad de Manzanas

vendidas por Abel.

=SUMAR.SI.CONJUNTO(A2,A9;B2:B9,"Manzanas",C2:C9,"ABEL")

PROMEDIO.SI: Devuelve el promedio (media aritmética) de todas las celdas de un

rango que cumplen unos criterios determinados.

=PROMEDIO.SI (rango,criterio,rango_suma)

En donde;

Rango: Es el Rango de celdas que desea evaluar.

Criterio: es el criterio en forma de número, expresión o texto, que determina las

celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32,

"32" ">32", "manzanas".

Rango_Suma: Son las celdas que se van a sumar.

Por ejemplo, se necesita conocer el promedio de las notas de una lista de

estudiantes cuya asistencia al curso fue mayor a 60%.

Cantidad

vendida Producto Vendedor

5 Manzanas ABEL

4 Manzanas ALBERTO

15 Alcachofas ABEL

3 Alcachofas ALBERTO

22 Bananas CARMEN

Page 42: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 41

=PROMEDIO.SI

(C2:C6;">60%";B2:B6)

PROMEDIO.SI.CONJUNTO: Devuelve el promedio (media aritmética) de todas las

celdas que cumplen múltiples criterios.

=PROMEDIO.SI.CONJUNTO(rango_promedio,rango_criterio1,criterio1,rango_criteri

o2,criterio2,..)

En donde;

RANGO_PROMEDIO: Una o más celdas cuyo promedio se desea obtener que

incluyan números, o nombres, matrices o referencias que contengan números.

RANGO_CRITERIOS1: El primer rango en el que se evalúan los criterios asociados.

CRITERIOS1: Los criterios en forma de número, expresión, referencia de celda o

texto que define qué celdas del argumento rango_criterios1 se agregarán.

RANGO_CRITERIOS2, CRITERIOS2… Rangos adicionales y sus criterios

asociados. Se permiten hasta 127 pares de rangos/criterios.

Por ejemplo, de acuerdo a lista de estudiantes del ejemplo anterior se requiere

obtener el promedio de las notas de aquellos estudiantes cuya asistencia al curso

fue mayor a 60% y que pertenezcan al grupo A.

ALUMNO

(A)

NOTA

(B)

ASISTENCIA

(C)

MARIO

MIRANDA

20 20%

NESTOR

NUÑEZ

85 100%

ODILIA

OBANDO

15 40%

PEDRO

PEREZ

50 80%

Page 43: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 42

=PROMEDIO.SI.CONJUNTO(C

2:C6, B2:B6,"A", D2:D6,">60%")

CONTAR.SI: La función CONTAR.SI cuenta el número de celdas dentro de un

rango que cumplen un solo criterio especificado por el usuario. Por ejemplo,

suponga que tiene una hoja de cálculo que contiene una lista de tareas en la

columna A y el primer nombre de la persona asignada a cada tarea en la columna

B. Puede usar la función CONTAR.SI para contar cuántas veces aparece el nombre

de una persona en la columna B y, de esa forma, determinar cuántas tareas tiene

asignadas.

=CONTAR.SI(rango,criterios)

En donde;

Rango: Una o más celdas que se van a contar, incluidos números o nombres,

matrices o referencias que contengan números. Los valores en blanco y los de texto

no se tienen en cuenta.

Criterio: es el criterio en forma de número, expresión o texto, que determina las

celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32,

"32" ">32", "manzanas".

Por ejemplo, se desea contar la cantidad de Productos que se venden en la

sucursal GRANADA.

ALUMNO GRUPO NOTA ASISTENCIA

MARIO

MIRANDA

A 20 20%

NESTOR

NUÑEZ

A 85 100%

ODILIA

OBANDO

B 15 40%

PEDRO

PEREZ

A 50 80%

ROSA

RAUDEZ

B 90 90%

Page 44: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 43

=CONTAR.SI

(A2:A6;"GRANADA")

CONTAR.SI.CONJUNTO: Aplica criterios a las celdas en varios rangos y cuenta

cuántas veces se cumplen dichos criterios.

=CONTAR.SI.CONJUNTO (rango_criterio1,criterio1,rango_criterio2,criterio2,...)

En donde;

RANGO_CRITERIOS1: El primer rango en el que se evalúan los criterios asociados.

CRITERIOS1: Los criterios en forma de número, expresión, referencia de celda o

texto que define qué celdas del argumento rango_criterios1 se agregarán.

RANGO_CRITERIOS2, CRITERIOS2… Rangos adicionales y sus criterios

asociados. Se permiten hasta 127 pares de rangos/criterios.

Por ejemplo, se desea contar la cantidad de Productos que se venden en la

sucursal GRANADA.

SUCURSAL

(A)

PRODUCTO

(B)

SUCURSAL

(C)

GRANADA A GRANADA

RIVAS B RIVAS

DIRIAMBA C DIRIAMBA

GRANADA D GRANADA

GRANADA E GRANADA

Page 45: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 44

=CONTAR.SI.CONJUNTO (B2:D2,"SI”, B3:C3,”SI”)

SI.ERROR: Devuelve un valor que se especifica si una fórmula se evalúa como un error; de

lo contrario, devuelve el resultado de la fórmula. Utilice la función SIERROR para

interceptar y controlar errores en una fórmula.

=SI.ERROR(valor, valor_si_error)

En donde;

Valor: El argumento en el que se busca un error.

Valor_si_error: El valor que se devuelve si la fórmula se evalúa como un error. Se

evalúan los tipos siguientes de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!,

#¿NOMBRE? o #¡NULO!

Por ejemplo, se estarán distribuyendo canastas alimenticias en diferentes ciudades, a

través de una división simple se conocerá cuantas canastas deberán distribuirse por sector

en cada ciudad.

= SI.ERROR(A2/B2;"ERROR DE CALCULO")

= SI.ERROR(A3/B3;"No existen personas en el sector")

= SI.ERROR(A4/B4;"ERROR DE CALCULO")

VENDEDOR CUOTA

ENERO

SUPERADA

CUOTA

FEBRERO

SUPERADA

CUOTA

MARZO

SUPERADA

GABRIEL

GARCIA

SI NO SI

NOEL

NAVARRO

SI SI SI

CANASTAS

SECTORES

POR

CIUDAD

210 35

55 0

0 23

Page 46: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 45

=HOY()

Funciones de Texto

=LARGO(Texto)

=Izquierda(texto, número de caracteres)

=Derecha(texto, número de caracteres)

=Extrae(texto, posición inicial, núm_caracteres)

Funciones de Búsqueda y Referencia

Este tipo de funciones permiten al usuario encontrar valores dentro de nuestra hoja de

acuerdo a los criterios establecidos en la búsqueda. También facilitan obtener

información de referencia de las celdas.

Y es que es importante tener presente que un libro de Excel puede llegar a ser

inmenso. Sólo una hoja tiene en Excel-2007/10 más de 17 mil millones de celdas

(1.048.576 filas x 16.384 columnas) y en Excel 2003 más de 16 millones de celdas

(65.536 filas x 256 columnas) que tampoco son pocas sin olvidar, además, que un libro

de Excel no está limitado en el número de hojas que puede contener, salvo el impuesto

por la memoria del ordenador que estemos utilizando.

La cantidad de datos (números, texto, fechas,…) que podemos llegar a tener en una

sola hoja es tan grande que es necesario manejar con soltura algunas funciones de

búsqueda y referencia, esto nos facilitará enormemente el trabajo, ya que buscar en

una tabla es una de las tareas más habituales cuando gestionamos muchos datos para

obtener información.

Page 47: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 46

FUNCIÓN INGLÉS DESCRIPCIÓN

ÁREAS AREAS Devuelve el número de áreas de

una referencia. Un área es un

rango de celdas contiguas o una

única celda.

BUSCAR LOOKUP Busca valores de un rango de

una columna o una fila o desde

una matriz.

BUSCARH HLOOKUP Busca en la primera fila de una

tabla o matriz de valores y

devuelve el valor en la misma

columna desde una fila

especificada.

BUSCARV VLOOKUP Busca un valor en la primera

columna de la izquierda de una

tabla y luego devuelve un valor

en la misma fila desde una

columna especificada. De forma

predeterminada, la tabla se

ordena de forma ascendente.

COINCIDIR MATCH Devuelve la posición relativa de

un elemento en una matriz, que

coincide con un valor dado en un

orden especificado.

COLUMNA COLUMN Devuelve el número de columna

de una referencia.

COLUMNAS COLUMNS Devuelve el número de columnas

en una matriz o referencia.

DESREF OFFSET Devuelve una referencia a un

rango que es un número

especificado de filas y columnas

Page 48: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 47

de una referencia dada.

DIRECCIÓN ADDRESS Crea una referencia de celda en

forma de texto una vez

especificados los números de fila

y columna.

ELEGIR CHOOSE Elige un valor o una acción de

una lista de valores a partir de un

número de índice.

FILA ROW Devuelve el número de fila de

una referencia.

FILAS ROWS Devuelve el número de filas de

una referencia o matriz.

HIPERVÍNCULO HYPERLINK Crea un acceso directo o salto

que abre un documento guardado

en el disco duro, en un servidor

de red o en Internet.

IMPORTARDATOSDINAMICOS GETPIVOTDATA Extrae datos almacenados en

una tabla dinámica.

ÍNDICE INDEX Devuelve un valor o referencia de

la celda en la intersección de una

fila y columna en particular, en un

rango especificado.

INDIRECTO INDIRECT Devuelve una referencia

especificada por un valor de

texto.

RDTR RTD Recupera datos en tiempo real de

un programa compatible con

automatizaciones COM.

TRANSPONER TRANSPOSE Devuelve un rango vertical de

celdas como un rango horizontal,

o viceversa.

Page 49: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 48

Con el desarrollo de las siguiente 6 funciones del grupo de fórmulas de búsqueda y

referencia, un poco de creatividad y asignando "Nombres" para los rangos podemos

lograr mucho.

BUSCAR

BUSCARV (en Excel 2010 se llama CONSULTAV, 2003/2007 y 2013 BUSCARV)

COINCIDIR

INDICE

ELEGIR

DESREF

Así pues, en este aprendizaje nos centraremos en conocer más sobre estas funciones.

BUSCAR

La función BUSCAR devuelve un valor de un rango de una fila o una columna o de una

matriz. La función BUSCAR tiene dos formas de sintaxis: la forma vectorial y la

matricial.

Sintaxis

= BUSCAR(valor_buscado; vector_de_comparación; vector_resultado)

La sintaxis de la forma vectorial de la función BUSCAR tiene los siguientes argumentos:

Valor_buscado (Obligatorio). Valor que busca la función BUSCAR en el primer

vector. Valor_buscado puede ser un número, texto, un valor lógico o un nombre

de referencia que se refiere a un valor.

Page 50: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 49

Vector_de_comparación (Obligatorio). Rango que sólo contiene una fila o una

columna. Los valores del vector_de_comparación pueden ser texto, números o

valores lógicos.

Importante: Los valores del vector_de_comparación se deben colocar en

orden ascendente: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo

contrario, BUSCAR puede devolver un valor incorrecto. El texto en

mayúsculas y en minúsculas es equivalente.

Vector_resultado (Obligatorio). Rango que sólo incluye una fila o una columna.

El argumento vector_resultado debe tener el mismo tamaño que

vector_de_comparación.

Nota Si la función BUSCAR no puede encontrar el valor_buscado, la función

muestra el valor más grande en vector_de_comparación que es menor o igual al

valor_buscado.

Si el valor_buscado es menor que el menor valor del vector_de_comparación,

BUSCAR devuelve el valor de error #N/A.

Uso de la formula BUSCARV de forma matricial

La forma matricial de BUSCAR busca el valor especificado en la primera fila o columna

de una matriz y devuelve un valor de la misma posición en la última fila o columna de la

matriz. Use esta forma de BUSCAR cuando los valores que desea buscar están en la

primera fila o columna de la matriz. Use la otra forma de BUSCAR cuando desea

especificar la ubicación de la columna o fila.

Nota En general, es mejor usar las funciones BUSCARH o BUSCARV en lugar de

la forma matricial de BUSCAR. Esta forma de BUSCAR se proporciona por

cuestiones de compatibilidad con otros programas de hojas de cálculo.

Page 51: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 50

Sintaxis

= BUSCAR(valor_buscado; matriz)

La sintaxis de la forma matricial de la función BUSCAR tiene estos argumentos:

Valor_buscado (Obligatorio). Valor que busca la función BUSCAR en una

matriz. El argumento valor_buscado puede ser un número, texto, un valor lógico

o un nombre de referencia que se refiere a un valor.

Si BUSCAR no puede encontrar el valor de valor_buscado, usa el mayor valor

de la matriz que es menor o igual que el valor_buscado.

Si el valor de valor_buscado es menor que el menor valor de la primera fila o

columna (según las dimensiones de la matriz), BUSCAR devuelve el valor de

error #N/A.

Matriz (Obligatorio). Rango de celdas que contiene texto, números o valores

lógicos que desea comparar con valor_buscado.

La forma matricial de BUSCAR es muy similar a las funciones BUSCARH y BUSCARV.

La diferencia es que BUSCARH busca el valor de valor_buscado en la primera

fila, BUSCARV busca en la primera columna y BUSCAR realiza la búsqueda según las

dimensiones de la matriz.

Si la matriz cubre un área que es más ancha que alta (más columnas que

filas), BUSCAR busca el valor de valor_buscado en la primera fila.

Si una matriz es cuadrada o más alta que ancha (tiene más filas que

columnas), BUSCAR busca en la primera columna.

Con las funciones BUSCARH y BUSCARV, puede especificar un índice en

vertical o en horizontal, pero BUSCAR siempre selecciona el último valor de la

fila o columna.

Page 52: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 51

Nota: Los valores de la matriz se deben colocar en orden ascendente: ..., -2, -1, 0,

1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCAR puede devolver un

valor incorrecto. El texto en mayúsculas y en minúsculas es equivalente.

BUSCARV

La función BUSCARV permite una búsqueda vertical en una tabla. Existe otra

función denominada BUSCARH que permite una búsqueda horizontal en una tabla.

BUSCARV permite búsquedas por intervalos o búsquedas exactas.

La tabla que constituye la base de datos requiere que la primera columna sea

sobre la que luego se buscará, y todas las demás columnas deben estar a su

derecha.

En la búsqueda por intervalos la primera columna debe estar ordenada de menor

a mayor. Admite tanto valores numéricos como texto, en este caso el orden de

menor a mayor supone orden alfabético. En la búsqueda exacta no es necesario

este tipo de ordenaciones.

Sintaxis

=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenad

o)

Valor_buscado Valor que se va a buscar en la primera columna de la matriz

de tabla. Valor_buscado puede ser un valor o una referencia. Si valor_buscado

es inferior al menor de los valores de la primera columna de matriz_buscar_en,

BUSCARV devuelve al valor de error #N/A.

Matriz_buscar_en Dos o más columnas de datos. Use una referencia a un

rango o un nombre de rango. Los valores de la primera columna de

matriz_buscar_en son los valores que busca valor_buscado. Estos valores

pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del

texto son equivalentes.

Page 53: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 52

Indicador_columnas Número de columna de matriz_buscar_en desde la cual

debe devolverse el valor coincidente. Si el argumento indicador_columnas es

igual a 1, la función devuelve el valor de la primera columna del argumento

matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el

valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si

indicador_columnas es:

Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE!

Si es superior al número de columnas de matriz_buscar_en, BUSCARV

devuelve el valor de error #REF!

Ordenado Valor lógico que especifica si BUSCARV va a buscar una

coincidencia exacta o aproximada:

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o

aproximada. Si no localiza ninguna coincidencia exacta, devolverá el

siguiente valor más alto inferior a valor_buscado.

Los valores de la primera columna de matriz_buscar_en deben estar clasificados según

un criterio de ordenación ascendente; en caso contrario, es posible que BUSCARV no

devuelva el valor correcto.

Si es FALSO, BUSCARV sólo buscará una coincidencia exacta. En este

caso, no es necesario ordenar los valores de la primera columna de

matriz_buscar_en. Si hay dos o más valores en la primera columna de

matriz_buscar_en, se utilizará el primer valor encontrado. Si no se

encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

Nota

- Al buscar valores de texto en la primera columna de matriz_buscar_en,

asegúrese de que los datos de ésta no tienen espacios al principio ni al final, de

que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ‘ o

Page 54: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 53

“), y de que no haya caracteres no imprimibles. En estos casos, BUSCARV puede

devolver un valor inesperado o incorrecto.

- Al buscar valores de fechas o números, asegúrese de que los datos de la

primera columna de matriz_buscar_en no se almacenen como valores de texto, ya

que, en ese caso, BUSCARV puede devolver un valor incorrecto o inesperado.

- Si Ordenado es FALSO y valor_buscado es un valor de texto, se pueden

utilizar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el

argumento valor_buscado. El signo de interrogación corresponde a un solo

carácter cualquiera y el asterisco equivale a cualquier secuencia de cara de

caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco,

escriba una tilde (~) antes del carácter.

COINCIDIR

La función COINCIDIR busca un elemento especificado en un rango de celdas y, a

continuación, devuelve la posición relativa de ese elemento en el rango. Por ejemplo, si

el rango A1:A3 contiene los valores 5, 25 y 38, la fórmula

=COINCIDIR(25;A1:A3;0) devuelve el número 2, porque 25 es el segundo elemento en

el rango.

Use COINCIDIR en lugar de una de las funciones BUSCAR cuando necesite conocer la

posición de un elemento en un rango en lugar del elemento en sí. Por ejemplo, puede

usar la función COINCIDIR para proporcionar un valor para el argumento fila de la

función INDICE.

Sintaxis

=COINCIDIR(valor_buscado;matriz_buscada; [tipo_de_coincidencia])

La sintaxis de la función COINCIDIR tiene los siguientes argumentos:

Valor_buscado (Obligatorio). Valor que desea buscar en matriz_buscada. Por

ejemplo, cuando busca un número en la guía telefónica, usa el nombre de la

Page 55: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 54

persona como valor de búsqueda, pero el valor que desea es el número de

teléfono.El argumento valor_buscado puede ser un valor (número, texto o valor

lógico) o una referencia de celda a un número, texto o valor lógico.

Matriz_buscada (Obligatorio). Rango de celdas en el que se realiza la

búsqueda.

Tipo_de_coincidencia (Opcional). Puede ser el número -1, 0 o 1. El

argumento tipo_de_coincidencia especifica cómo Excel hace coincidir

el valor_buscado con los valores de matriz_buscada. El valor predeterminado

de este argumento es 1.

En la siguiente tabla se describe cómo la función busca valores según la configuración

del argumento tipo_de_coincidencia.

TIPO_DE_COINCIDENCIA COMPORTAMIENTO

1 u omitido COINCIDIR encuentra el mayor valor que es menor o

igual que el valor_buscado.

Los valores del argumento matriz_buscada se

deben colocar en orden ascendente, por ejemplo: ...-

2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO.

0 COINCIDIR encuentra el primer valor que es

exactamente igual que el valor_buscado.

Los valores del argumento matriz_buscada pueden

estar en cualquier orden.

-1 COINCIDIR encuentra el menor valor que es mayor o

igual que el valor_buscado.

Los valores del argumento matriz_buscada se

deben colocar en orden descendente, por ejemplo:

Page 56: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 55

VERDADERO, FALSO, Z-A, ...2, 1, 0, -1, -2, ..., etc.

Notas COINCIDIR devuelve la posición del valor coincidente dentro de

la matriz_buscada, no el valor en sí.

Por ejemplo, COINCIDIR("b";{"a";"b";"c"};0) devuelve 2, la posición relativa de "b"

dentro de la matriz {"a";"b";"c"}.

COINCIDIR no distingue entre mayúsculas y minúsculas cuando busca valores de

texto.

Si COINCIDIR no puede encontrar una coincidencia, devuelve el valor de error

#N/A.

Si el tipo_de_coincidencia es 0 y el valor_buscado es una cadena de texto, puede

usar los caracteres comodín: el signo de interrogación (?) y el asterisco (*), en el

argumento valor_buscado. Un signo de interrogación coincide con cualquier carácter

individual; un asterisco coincide con cualquier secuencia de caracteres. Si desea buscar

un signo de interrogación o un asterisco real, escriba una tilde (~) antes del carácter.

La función COINCIDIR presentada por sí sola quizás no llame mucho la atención, así

que a continuación se presenta un pequeño ejemplo muy elemental en el que se puede

apreciar parte de su utilidad de esta fórmula.

Para ello combinaremos la función COINCIDIR con la función MAX (que busca el mayor

valor de un rango). Se trata de averiguar en qué posición o puesto de la base de datos

se encuentra el mayor valor de la misma.

Supongamos una base de datos con los empleados y sus respectivos sueldos, y

necesitamos saber en qué puesto o posición de la base de datos está el empleado con

el sueldo más alto.

Nuestra base de datos:

Page 57: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 56

F/C A B

1 Alberto 750,000

2 Alfonso 650,000

3 Timoteo 1,200,000

4 Humberto 900,000

5 Ernesto 900,000

6 Carlos 600,000

7 Clara 1,700,000

8 Erika 550,000

9 Tomas 1,000,000

10 Marta 600,000

El asunto consiste en averiguar en qué posición dentro de la base de datos está el

sueldo más alto. La fórmula será:

=COINCIDIR(MAX(B2:B11);B2:B11;0)

Esta fórmula nos arrojará la posición 7, donde se encuentra el sueldo más alto que es

1.700.00 correspondiente a Clara.

Le hemos dicho a Excel que identifique el mayor valor que hay en el rango B2:B11 y

luego, con la función COINCIDIR, indique la posición en que se encuentra dentro del

rango B2:B11.

Esto es apenas una muestra de lo que se puede hacer con la combinación de

diferentes funciones, que vistas individualmente no parecen tener gran utilidad.

Luego iremos complicando más los ejemplos para dimensionar el potencial que tiene

Ms Excel.

Page 58: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 57

Nota: Si se desea determinar la posición del menor valor, se debe cambiar la

función MAX por MIN.

INDICE

La función índice en Excel es muy poco conocida pero de mucha ayuda en algunos

casos, aunque por sí sola no luzca muy útil, combinada con otras fórmulas permite

automatizar un importante número de tareas en Excel.

La función INDICE permite extraer un valor ubicado en una celda determinada de un

rango de datos o base de datos. Para ello debemos indicarle a Excel el número de la

columna y el número de la fila, y Excel traerá el valor que se encuentra en la

intersección de las dos.

La función INDICE presenta dos formas: matricial y de referencia.

Forma de matriz

Sintaxis

=INDICE(matriz, núm_fila, [núm_columna])

La sintaxis de la función INDICE tiene los siguientes argumentos:

Matriz (Obligatorio). Es un rango de celdas o una constante de matriz.

Si matriz contiene solo una fila o columna, el argumento núm_fila o

núm_columna correspondiente es opcional.

Si matriz tiene varias filas y columnas, y solo usa núm_fila o núm_columna,

INDICE devuelve una matriz de dicha fila o columna completa.

Núm_fila (Obligatorio). Selecciona la fila de la matriz desde la cual devolverá un

valor. Si omite núm_fila, se necesita el argumento núm_columna.

Page 59: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 58

Núm_columna (Opcional). Selecciona la columna de la matriz desde la cual

devolverá un valor. Si omite núm_columna, se necesita el argumento núm_fila.

Nota Si usa los argumentos núm_fila y núm_columna, INDICE devuelve el valor de

la celda donde se produce la intersección de los argumentos.

Si define núm_fila o núm_columna como 0 (cero), INDICE devuelve la matriz de

valores de toda la columna o fila, respectivamente. Para usar valores devueltos

como una matriz, escriba la función INDICE como una fórmula de matriz en un

rango horizontal de celdas para una fila y en un rango vertical de celdas para una

columna. Para escribir una fórmula de matriz, presione CTRL+MAYÚS+ENTRAR.

Los argumentos núm_fila y núm_columna deben indicar una celda incluida en

matriz; de lo contrario, INDICE devuelve el valor de error #¡REF!.

Forma de referencia

Sintaxis

=INDICE(ref, núm_fila, [núm_columna], [núm_área])

La sintaxis de la función INDICE tiene los siguientes argumentos:

Ref (Obligatorio). Es una referencia a uno o varios rangos de celdas.

Si especifica un rango no adyacente como argumento ref, escríbalo entre

paréntesis.

Si cada área del argumento ref contiene una sola fila o columna, los

argumentos núm_fila o núm_columna serán opcionales respectivamente.

Por ejemplo, use INDICE(ref,,núm_columna) para un argumento ref con

una sola fila.

Núm_fila (Obligatorio). Es el número de la fila en el argumento ref desde la que

se devolverá una referencia.

Page 60: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 59

Núm_columna Opcional. Es el número de la columna en el argumento ref

desde la que se devolverá una referencia.

Núm_área Opcional. Selecciona un rango en el argumento ref desde el cual se

devolverá la intersección de núm_fila y núm_columna. La primera área

seleccionada o especificada se numera con 1, la segunda con 2 y así

sucesivamente. Si omite núm_área, INDICE usa el área 1.

Por ejemplo, si ref describe las celdas (A1:B4;D1:E4;G1:H4), entonces núm_área 1 es

el rango A1:B4, núm_área 2 es el rango D1:E4 y núm_área 3 es el rango G1:H4.

Notas

Después de que ref y núm_área hayan seleccionado un rango determinado,

núm_fila y núm_columna seleccionan una celda específica: núm_fila 1 es la

primera fila del rango, núm_columna 1 es la primera columna y así

sucesivamente. La referencia devuelta por INDICE es la intersección entre

núm_fila y núm_columna.

Si define núm_fila o núm_columna como 0 (cero), INDICE devuelve la referencia

de toda la fila o columna, según corresponda.

Núm_fila, núm_columna y núm_área deben dirigirse a una celda en ref, de lo

contrario la función INDICE devuelve el valor de error #¡REF!. Si omite núm_fila y

núm_columna, INDICE devuelve el área del argumento ref definido por núm_área.

El resultado de la función INDICE es una referencia y será interpretado como tal

por otras fórmulas. El valor devuelto por la función INDICE se puede usar como

una referencia o como un valor, dependiendo de la fórmula. Por ejemplo, la

fórmula CELDA("ancho";INDICE(A1:B2,1,2)) es igual a CELDA("ancho",B1). La

función CELDA usa el valor devuelto por INDICE como referencia a una celda. Por

otra parte, una fórmula como 2*INDICE(A1:B2,1,2) traduce el valor devuelto por

INDICE al número de la celda B1

Page 61: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 60

Ejemplos:

Escriba los datos siguientes en una hoja de cálculo de Excel en blanco. Utilizará estos

datos para la fórmula de ejemplo en este artículo.

A B C

1 Nombre Depart. Edad

2 Henry 501 28

3 Stan 201 19

4 María 101 22

5 Larry 301 29

6

7 Harry 401 21

8 Joe 101 23

9 Lynn 301 30

Forma de referencia de índice

1. Escriba la siguiente fórmula en la celda E2 (o en cualquier celda en blanco

disponible):

=INDICE(A2:C5,A7:C9),2,3,2)

(A2:C5, a7: C9) son los intervalos donde se encontrará el valor que desee.

2 es el número de fila en el intervalo donde está el valor.

3 es el número de columna de los rangos donde el valor es. Como hay tres

columnas (comenzando por la columna A), la tercera columna es la columna C.

2 es el área, A2:C5 o a7: C9, donde el valor es. Porque hay dos áreas

especificadas para el intervalo, el segundo rango es a7: C9.

2. Presione ENTRAR.

Page 62: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 61

En la fórmula del ejemplo, la función Índice devuelve un valor en la intersección de la

segunda fila (2) y la tercera columna (C) del rango a7: C9. El valor de la celda C8

es 23. Por lo tanto, la fórmula =INDEX((A2:C5,A7:C9),2,3,2) devolverá el valor 23.

Forma de referencia de índice

1. Escriba la siguiente fórmula en la celda E3 (o en cualquier celda en blanco

disponible):

=INDICE(A2:C5,2,3)

A2:C5 es el rango donde se encontrará el valor que desee.

2 es el número de fila en el intervalo donde está el valor.

3 es el número de la columna del rango, en el cual se encuentra el valor Como

hay tres columnas (comenzando por la columna A), la tercera columna es la

columna C.

2. Presione CTRL + MAYÚS + ENTRAR para escribir la fórmula como una fórmula

de matriz.

En la fórmula del ejemplo, la función Indice devuelve un valor en la intersección de la

segunda fila (2) y la tercera columna (C).El valor en la celda C2 es 19. Por lo tanto, la

fórmula:

=INDICE(A2:C5,2,3) devolverá el valor 19.

Supongamos la siguiente base de datos

F/C D E

1 Nombre Sueldo

2 Alfonso

1,500,000

Page 63: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 62

3 Timoteo

1,200,000

4 Humberto

900,000

5 Ernesto

900,000

6 Carlos

600,000

7 Clara

700,000

8 Erika

550,000

9 Tomas

1,000,000

10 Marta

600,000

Otro ejemplo:

Tenemos una base de datos entre los rangos D2 y E10 (D2:E10).

Necesitamos ahora traer el valor que se encuentre entre la fila 7 y la columna 2 de la

base de datos. La fórmula sería:

=INDICE(D2:E10;7;2)

El resultado debe ser 550.000 que corresponde a Erika, puesto que ese valor se

encuentra en la intersección de la fila 7 de la base de datos con la columna 2 de la base

de datos.

Nótese que no hablamos de la columna A, B, C, etc., sino del número de la

columna que conforman la base de datos. En este ejemplo la base de datos está

conformada por 2 columnas, y no importa en qué columnas de Excel se encuentre la

base de datos. Igual sucede con las filas, pues no se cuentan las filas de la hoja de

Excel, sino las filas que componen la base de datos. Obsérvese que aunque Erika se

Page 64: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 63

encuentra en la fila 8 de Excel, está realmente en la fila 7 de la base de datos como tal.

Y aunque está en la columna E, dentro de la base de datos está en la columna 2.

La primera parte de la fórmula indica el rango donde está la base de datos. El primer

criterio corresponde al número de la fila y el segundo criterio corresponde al número de

la columna.

ELEGIR

La función elegir utiliza el argumento núm_índice para devolver un valor de una lista de

argumentos de valores. El usuario podrá utilizar ELEGIR cuando requiera seleccionar

uno de los 254 valores posibles a partir del rango del argumento índice.

Por ejemplo, si valor 1 a valor 7 son los días de la semana, ELEGIR devuelve uno de

los días cuando se utiliza un número entre 1 y 7 como argumento núm_índice.

Sintaxis

=ELEGIR(núm_índice;valor1;valor2;...)

En dónde;

Núm_índice (Obligatorio): especifica el argumento de valor que se selecciona. El

argumento núm_índice debe ser un número entre 1 y 254, o bien, una fórmula o

referencia a una celda que contenga un número entre 1 y 254.

Valor1,valor2,... (Obligatorio): Son de 1 a 254 argumentos de valores entre los

cuales la función ELEGIR selecciona un valor o acción que se ejecuta basándose

en el argumento núm_índice. Los argumentos pueden ser números, referencias a

celdas, nombres definidos, fórmulas, funciones o texto.

Notas

Page 65: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 64

- Si núm_índice es 1, ELEGIR devuelve valor1; si es 2, ELEGIR devuelve

valor2 y así sucesivamente.

- Si núm_índice es menor que 1 o mayor que el número del último valor de

la lista, ELEGIR devuelve el valor de error #¡VALOR!.

- Si núm_índice es una fracción, se trunca al entero inferior antes de ser

utilizada.

Observaciones

Si núm_índice es una matriz, cada valor se evaluará cuando se evalúe ELEGIR.

Los argumentos de valor para ELEGIR pueden ser referencias de rango, así

como valores individuales.

Por ejemplo, la fórmula:

=SUMA(ELEGIR(2;A1:A10;B1:B10;C1:C10))

Evalúa:

=SUMA(B1:B10)

… después devuelve un valor basado en los valores del rango B1:B10.

La función ELEGIR se evalúa primero, devolviendo la referencia B1:B10. La función

SUMA se evalúa a continuación usando B1:B10, que es el resultado de la función

ELEGIR, como su argumento.

Ejemplo

A continuación mostraré un ejemplo muy sencillo de la función ELEGIR. Como valores

pasaré el nombre de los números 1, 2 y 3 para después indicar el número que deseo

mostrar:

=ELEGIR(1, “uno”, “dos”, “tres”)

Page 66: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 65

Como resultado de esta fórmula obtendremos el valor “uno” porque el primer argumento

de la función indica que necesitamos la posición número 1. Ahora observa lo que

sucede cuando hacemos que el primer argumento de la función ELEGIR sea una

referencia a una celda:

Cada vez que cambie el valor de la celda B1 por alguno de los números válidos: 1, 2, o

3, la función ELEGIR mostrará el elemento correspondiente en la celda B2.

Otro ejemplo de la función ELEGIR

Ya vimos un ejemplo muy sencillo de la función ELEGIR colocando el primer

argumento de la función como una referencia a otra celda. También podemos hacer

que los valores proporcionados a la función provengan de otras celdas.

En la siguiente imagen puedes observar una tabla de equivalencias de calificaciones y

una segunda tabla con los nombres de estudiantes y su calificación.

Page 67: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 66

El primer argumento de la función ELEGIR es la calificación del estudiante que a su

vez es la posición que deseo buscar dentro del listado de valores proporcionado a la

función.

DESREF

La función DESREF devuelve una referencia a un rango que es un número de filas y

de columnas de una celda o rango de celdas. La referencia devuelta puede ser una

celda o un rango de celdas. Puede especificar el número de filas y el número de

columnas a devolver

Sintaxis

=DESREF(ref;filas;columnas;alto;ancho)

En donde;

Ref: es el punto de partida. Es la celda referencia desde donde empezamos el

desplazamiento por filas y columnas según indiquemos en los otros argumentos

de la función, es decir, es la referencia en la que se desea basar la desviación. Ref

debe referirse a una celda o rango de celdas adyacentes; en caso contrario,

DESREF devuelve el valor de error #¡VALOR!

Filas: es el número de filas, hacia arriba o hacia abajo, al que se desea que haga

referencia la celda superior izquierda. Si el argumento filas es 5, la celda superior

izquierda de la referencia pasa a estar cinco filas más abajo que la referencia.

Filas puede ser positivo (lo que significa que está por debajo de la referencia de

inicio) o negativo (por encima).

Columnas: es el número de columnas, hacia la derecha o izquierda, al que se

desea que haga referencia la celda superior izquierda del resultado. Si el

argumento columnas es 5, la celda superior izquierda de la referencia pasa a estar

cinco columnas hacia la derecha de la referencia. Columnas puede ser positivo (lo

que significa a la derecha de la referencia de inicio) o negativo (a la izquierda).

Page 68: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 67

Alto: es el alto, en número de filas, que se desea que tenga la referencia devuelta.

El alto debe ser un número positivo.

Ancho: es el ancho, en número de columnas, que se desea que tenga la

referencia devuelta. El argumento ancho debe ser un número positivo.

Observaciones

Si los argumentos filas y columnas colocan la referencia más allá del borde

de la hoja de cálculo, DESREF devuelve el valor de error #¡REF!

Si los argumentos alto o ancho se omiten, los valores predeterminados serán

los del argumento ref.

DESREF en realidad no desplaza celdas ni modifica la selección,

simplemente devuelve una referencia. Se puede utilizar la función DESREF

con cualquier función que necesite una referencia como argumento. Por

ejemplo, la fórmula SUMA(DESREF(C2;1;2;3;1)) calcula el valor total de un

rango de tres filas por una columna que se encuentra por debajo una fila y

dos columnas a la derecha de la celda C2.

A modo de ejemplo, introduzca los datos siguientes en una hoja de cálculo de Excel en

blanco. Utilizará estos datos para todas las fórmulas de ejemplo de este artículo.

A1: Nombre B1: Dept. C1: Edad

A2: Diego B2: 501 C2: 28

A3: Antonio B3: 201 C3: 19

A4: María B4: 101 C4: 22

A5: Arturo B5: 301 C5: 29

Escriba las fórmulas siguientes en la celda E2 (o en cualquier celda en blanco

disponible):

=DESREF(C2,2,-1,1,1)

Page 69: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 68

C2: la celda a la que se hace referencia.

2: indica el número de filas que se va a mover. Los números positivos indican

movimiento hacia abajo y los números negativos movimiento hacia arriba.

-1: indica el número de columnas que se va a mover. Los números positivos

indican movimiento hacia la derecha y los números negativos movimiento hacia

la izquierda.

1 (penúltimo valor): (opcional) Indica cuántas filas de datos hay que devolver.

Este número debe ser un número positivo.

1 (último valor): (opcional.) Indica cuántas columnas de datos hay que

devolver. Este número debe ser un número positivo.

Cuando utiliza esta fórmula, la función DESREF devuelve el valor de la celda situada

dos filas hacia abajo (2) y 1 fila a la izquierda (-1) de la celda C2 (que es la celda B4). El

valor de la celda B4 es "101". Por tanto, la fórmula devuelve "101".

La función DESREF en Excel, como tantas otras, luce bastante abstracta, difícil de

comprender, pero de gran utilidad para gestionar base de datos e información de todo

tipo.

En su forma más elemental, DESREF se refiere o se puede referir a una celda o a un

rango de celdas, según la descripción oficial de Microsoft, “devuelve el valor de una

celda que está a un número especificado de filas y columnas de una celda o un rango

de celdas al que hizo referencia en un rango adyacente”.

Otro ejemplo:

=DESREF(A1;3;2). Le estamos diciendo a Excel que tomando como base la celda A1

se desplace 3 filas abajo y dos columnas a la derecha:

Page 70: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 69

Como se observa, el resultado traído es el 15, que corresponde a la segunda columna a

la derecha de la A1 y a la tercera fila por debajo de A1.

A la anterior fórmula no le agregamos los dos últimos criterios (alto y ancho), ya que

estos argumentos se utilizan generalmente cuando la función DESREF se utiliza en

combinación con otras fórmulas.

Vemos cómo queda combinada con la función suma:

Le hemos dicho a Excel que se ubique en la celda A1, desde allí avance 2 fila abajo y

una columna a la derecha, posicionándose así en la celda B2, marcada en rojo en la

imagen; luego, en alto y ancho (4;1), le decimos Excel que sume los valores que están

dese B3 hasta 3 filas fila más abajo, avanzando 1 columna a la derecha desde A1, que

no es otra cosa que indicarle a Excel que sume el rango B3:B6. Si le diéramos a la

función un ancho de 2, nos sumaría el rango B3:C6.

Combinación de Fórmulas y Funciones

Combinar INDICE y COINCIDIR: Ventajas sobre BUSCARV

Page 71: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 70

A diferencia de la función BUSCARV, la fórmula con INDICE y COINCIDIR no nos

obliga a realizar la búsqueda en la primera columna de un rango y para demostrarlo

resolveremos el caso donde deseamos encontrar el nombre de una persona a partir de

su apellido.

La diferencia entre esta fórmula y la del ejemplo anterior son los rangos de búsqueda.

La función COINCIDIR hace la búsqueda sobre la columna Apellido y devuelve la

posición correcta para que la función INDICE devuelva la misma posición pero bajo la

columna Nombre. Este tipo de búsqueda no es posible de realizar con la función

BUSCARV porque ya hemos visto que dicha función siempre hace la búsqueda sobre la

primera columna del rango.

La flexibilidad que nos ofrece la función INDICE nos permite utilizarla en otros

escenarios para buscar un valor y regresar múltiples resultados. Así pues, la función

COINCIDIR combinada con la función BUSCARV es una excelente alternativa cuando

deseamos hacer una búsqueda sobre una tabla y elegir la columna que necesitamos

como resultado a partir de una lista desplegable.

Nota: Ahora ya conoces dos alternativas para crear una fórmula para buscar

datos en Excel, ya sea utilizando la función BUSCARV o la combinación de las

funciones INDICE y COINCIDIR. Experimenta con ambas opciones y elige la que

mejor se adapte a tus necesidades de acuerdo a los beneficios que ofrece cada

una de ellas.

Page 72: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 71

Combinar BUSCARV y COINCIDIR

La función BUSCARV es de gran utilidad para buscar valores dentro de una tabla, a

continuación se muestra cómo se puede mejorar la búsqueda utilizando la función

COINCIDIR misma que permite asociar una lista de validación de datos a la función

BUSCARV.

Primeramente se define una tabla de datos que tendrá en la columna A, la lista de libros

de Excel que están a la venta y los montos para cada mes en las columnas

subsecuentes.

Para conocer las ventas del libro “Excel en 24 Horas” para el mes de Marzo se puede

utilizar la siguiente fórmula:

=BUSCARV("Excel en 24 horas", A5:F14, 4, FALSO)

Al colocar esta fórmula en la celda E1 obtengo el resultado esperado:

Page 73: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 72

La fórmula da el resultado correcto, pero ¿Qué sucede si se necesita conocer las

ventas del mes de Enero? ¿Y después las ventas del mes de Febrero?

Para ello se puede optar por crear una lista de validación

Para evitar la edición de la función BUSCARV cada vez que necesitamos un dato

diferente se puede crear una lista de validación con las posibles opciones para los

meses.

Miremos como se visualizaría:

Page 74: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 73

Para este ejemplo se ha colocado la lista de validación en la celda B1. Para poder

utilizar esta lista dentro de la función BUSCARV se debe utilizar la función

COINCIDIR la cual buscará el valor seleccionado en la lista y lo comparará con la matriz

A4:F4.

Se puede observar que se ha remplazado el tercer argumento de la función

BUSCARV con la función COINCIDIR de la siguiente manera: COINCIDIR(B1, A4:F4,

0).

Page 75: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 74

El resultado de la función COINCIDIR dará el número de columna que corresponde al

mes seleccionado en la lista de la celda B1.

Adicional a lo antes expuesto, se puede hacer una pequeña mejora y es agregar otra

lista de validación con los nombres de los libros y utilizar dicho valor en el primer

argumento de la función BUSCARV.

La fórmula final se visualizaría así:

Funciones Matriciales

Una formula matricial en Excel es una fórmula que trabaja con matrices de datos.

Las fórmulas matriciales no trabajan con un solo valor sino con una serie de datos.

Una fórmula de matriz puede ejecutar varias operaciones y devolver un único resultado

o varios resultados, estas actúan en dos o más conjuntos de valores denominados

argumentos matriciales en donde cada argumento matricial debe tener el mismo

número de filas y de columnas. Estas fórmulas se crean del mismo modo que las

demás fórmulas, excepto que se debe presionar la combinación de teclas

CTRL+SHIFT+ENTER. Algunas de las funciones integradas son fórmulas de matriz y

se deben escribir como matrices para obtener los resultados correctos.

Se pueden identificar dos tipos de fórmulas matriciales en Excel.

Page 76: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 75

El primer tipo son aquellas fórmulas que trabajan con una serie de datos, realizan una

operación sobre ellos y como resultado se regresa un solo valor. Generalmente este

tipo de fórmulas hace una suma, un promedio o una cuenta de los elementos de las

serie de datos y arroja un único resultado en una sola celda.

El segundo tipo de fórmulas matriciales de igual manera trabaja con series de datos

pero el resultado es colocado en dos o más celdas.

Las constantes matriciales se pueden utilizar en lugar de las referencias cuando no se

desea especificar el valor de cada constante en una celda independiente de la hoja de

cálculo.

¿Por qué utilizar fórmulas de matriz?

Si tiene experiencia en el uso de fórmulas de Excel, sabrá que es posible realizar

algunas operaciones bastante complejas. Por ejemplo, es posible calcular el costo total

de un préstamo a lo largo de un número concreto de años. Sin embargo, si realmente

desea dominar las fórmulas de Excel, tiene que aprender a utilizar fórmulas de matriz.

Éstas se pueden emplear para realizar tareas complejas como:

Contar el número de caracteres incluidos en un rango de celdas.

Sumar únicamente aquellos números que cumplan ciertas condiciones, como los

valores más bajos de un rango o los números comprendidos entre un límite

superior e inferior.

Sumar cada º valor de un rango de valores.

Las fórmulas de matriz también ofrecen estas ventajas:

Coherencia Si hace clic en cualquiera de las celdas de la E2 hacia abajo verá

la misma fórmula. Esa coherencia garantiza una mayor precisión.

Seguridad No es posible sobrescribir un componente de una fórmula de matriz

de varias celdas. Por ejemplo, haga clic en la celda E3 y presione SUPR. Tendrá

que seleccionar todo el rango de celdas (de E2 a E11) y modificar la fórmula de

la matriz completa o dejar la matriz como está. Como medida de seguridad

Page 77: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 76

adicional, tiene que presionar CTRL+MAYÚS+ENTRAR para confirmar la

modificación de la fórmula.

Tamaños de archivo más pequeños A menudo puede usar una fórmula de

matriz sencilla en lugar de varias fórmulas intermedias. Por ejemplo, el libro que

ha creado para este ejercicio emplea una fórmula de matriz para calcular los

resultados de la columna E. Si hubiera utilizado fórmulas estándar (como

=C2*D2), habría usado 11 fórmulas distintas para calcular los mismos resultados.

Reglas para especificar y modificar fórmulas de matriz

La regla principal para crear una fórmula de matriz merece repetirse: presione

CTRL+SHIFT+ENTER siempre que sea necesario para especificar o modificar una

fórmula de matriz. Esa regla se aplica tanto a las fórmulas de una celda como de varias.

Siempre que trabaje con fórmulas de varias celdas, también tendrá que seguir estas

reglas:

Tiene que seleccionar el rango de celdas en el que va a incluir los resultados

antes de especificar la fórmula.

No puede modificar el contenido de una celda individual de una fórmula de

matriz.

Puede mover o eliminar una fórmula de matriz completa, pero no parte de la

misma. En otras palabras, para reducir una fórmula de matriz, primero debe

eliminar la fórmula existente y comenzar de nuevo.

No puede insertar celdas en blanco en una fórmula de matriz de varias celdas ni

eliminar celdas de la misma.

Las fórmulas de matriz pueden parecer mágicas, pero también tienen algunas

desventajas:

A veces uno puede olvidarse de presionar CTRL+SHIFT+ENTER. Acuérdese de

presionar esta combinación de teclas siempre que especifique o modifique una

fórmula de matriz.

Page 78: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 77

Es posible que otros usuarios no entiendan las fórmulas. En la práctica,

normalmente existe poca documentación sobre las fórmulas de matriz en una

hoja de cálculo, así que si otros usuarios necesitan modificar los libros, debería

evitarlas o asegurarse de que esos usuarios entiendan cómo modificarlas.

Según la velocidad de procesamiento y la memoria del equipo, las fórmulas de

matriz de gran tamaño pueden ralentizar los cálculos.

Utilizar una fórmula de matriz para calcular resultados únicos y múltiples

Cuando se especifica una fórmula de matriz Excel inserta de forma automática la

fórmula entre llaves {}.

Calcular un solo resultado Este tipo de fórmula de matriz permite simplificar un

modelo de hoja de cálculo remplazando varias fórmulas distintas por una sola fórmula

de matriz. Por ejemplo, la siguiente calcula el valor total de una matriz de Ventas y

precios de dos productos sin utilizar una fila de celdas para calcular y mostrar los

valores individuales de cada acción.

Producto A Producto B

Ventas 500 300

Precios 10 15

Valor Total {=suma(B2:C2*B3:C3)}

Cuando se escribe la fórmula {=SUMA (B2:D2*B3:D3)} como fórmula de matriz,

multiplica el número de acciones y el precio correspondiente a cada acción, y luego

suma los resultados de dichos cálculos.

En general, las fórmulas de matriz usan sintaxis de fórmula estándar. Todas comienzan

con un signo igual (=) y se pueden usar todas las funciones integradas de Excel en las

fórmulas de matriz. La principal diferencia es que al utilizar una fórmula de matriz es

necesario presionar CTRL+MAYÚS+ENTRAR para especificarla. Al hacer esto, Excel

incluye la fórmula de matriz entre llaves; si escribe las llaves manualmente, la fórmula

se convertirá en una cadena de texto y no funcionará.

Page 79: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 78

Lo siguiente que tiene que entender es que las funciones de matriz son una forma de

método abreviado. La fórmula de una celda de la celda B4 condensa todas las

operaciones de multiplicación, más la aritmética necesaria para agregar esos

subtotales.

Calcular varios resultados Algunas funciones de la hoja de cálculo devuelve matrices

de valores o requieren una matriz de valores como argumento. Para calcular varios

resultados con una fórmula de matriz, se deberá especificar la matriz en un rango de

celdas que tenga el mismo número de filas y columnas que los argumentos matriciales.

Por ejemplo, dada las ventas y precios de tres productos se calcula en tres celdas

separadas el monto correspondiente a las ventas por producto con una sola formula.

Producto A Producto B Producto C

Ventas 500 300 400

Precios 10 15 13

Total Ventas {=B2:C2*B3:C3} {=B2:C2*B3:C3} {=B2:C2*B3:C3}

Al especificar la fórmula como fórmula de matriz, generará tres resultados separados

(5000, 4500 y 5200) basados en las tres cifras de ventas y en los tres Precios.

Utilizar constantes matriciales

En una fórmula normal se puede escribir una referencia a una celda que contenga un

valor o el valor propiamente dicho, también denominado constante. Igualmente, en una

fórmula de matriz se puede escribir una referencia a una matriz o la propia matriz de

valores contenidos en las celdas, lo que también se denomina constante matricial. Las

fórmulas de matriz aceptan constantes del mismo modo que las fórmulas que no son de

matriz, pero las constantes matriciales se deben especificar con un formato

determinado.

Las constantes matriciales pueden contener números, texto, valores lógicos como

VERDADERO o FALSO o valores de error como #N/A. En la misma constante matricial

Page 80: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 79

puede haber distintos tipos de valores, por ejemplo, {1; 3;

4\VERDADERO;FALSO;VERDADERO}. Los números de las constantes matriciales

pueden tener formato entero, decimal o científico. El texto debe incluirse entre comillas,

por ejemplo, "martes".

Las constantes matriciales no pueden contener referencias a celdas, columnas ni filas

de longitud desigual, fórmulas ni los caracteres especiales $ (símbolo de dólar),

paréntesis o % (símbolo de porcentaje).

Cuando dé formato a constantes matriciales, asegúrese de:

Incluirlas entre llaves ( { } ).

Separar los valores situados en columnas diferentes mediante punto y coma (;). Por

ejemplo, para representar los valores 10, 20, 30 y 40, escriba {10;20;30;40}. Esta

constante matricial se denomina matriz de 1 por 4, y equivale a una referencia a 1 fila

por 4 columnas.

Separar los valores situados en filas diferentes mediante barras inversas (\). Por

ejemplo, para representar los valores 10, 20, 30 y 40 de una fila y los valores 50, 60, 70

y 80 de la inmediatamente inferior, se escribiría una constante matricial de 2 por 4:

{10;20;30;40\50;60;70;80}.

K.ESIMO.MAYOR & K.ESIMO.MENOR

Dos de las funciones mas conocidas en Excel son aquellas que sirven para calcular el

valor mas alto de una lista de números o para calcular el valor menor de un conjunto de

valores, estas son las ya conocidas MAX y MIN. Sin embargo, estas funciones están

limitadas a regresar un solo valor.

En cambio con el uso de las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR, es

posible indicarle a Excel que retorne los 3 valores mas altos de una lista, el 5 valor

menor o el 2°, el 5°, el 7° y el 10° menor. En cada función se utiliza una constante de

matriz que garantiza la respuesta deseada, por lo cual debe recordar las reglas para

especificar formulas matriciales. La sintaxis de ambas funciones es:

=K.ESIMO.MAYOR (matriz, k)

Page 81: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 80

=K.ESIMO.MENOR (matriz, k)

En dónde;

Matriz: Es el rango de datos a evaluar.

k: Constante matricial que indica los valores a retornar.

89 K.ESIMO.MAYOR(A1:A10,{1,2,3})

37

76 K.ESIMO.MAYOR(A1:A10,{3;2;1})

83

70

49 K.ESIMO.MENOR(A1:A10,{2,6,9})

96

100 K.ESIMO.MENOR(A1:A10,{8;5})

44

43

También puede usar funciones adicionales con está fórmula, por ejemplo SUMA o

PROMEDIO. Por ejemplo:

=SUMA(K.ESIMO.MENOR(A1:A10,{1;2;3}))

=PROMEDIO(K.ESIMO.MENOR(A1:A10,{1;2;3}))

3.6 Errores de Excel

Durante la creación y desarrollo de modelos o plantillas realizadas en Excel es posible

que introduzcamos algún dato o fórmula de manera erróneo, de tal forma que sea

imposible realizar el cálculo, con objeto de identificar donde se encuentra el error la

aplicación Excel es capaz de detectar la celda así como clasificar el tipo de error

producido, facilitando la tarea de detección y resolución de errores.

Excel dispone de 4 métodos o técnicas para la identificación de errores s:

Retorna el valor más alto, el 2° mayor y el

3° mayor en 3 celdas horizontales

Retorna el 3° valor más alto, el 2° mayor

y el mayor en 3 celdas verticales

Retorna el 2° valor menor, el 6° menor y

el 9° menor en 3 celdas horizontales

Retorna el 8° valor menor y el 5° menor

en 2 celdas verticales

Page 82: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 81

Cuadro de dialogo

Símbolo parte superior izquierda de la celda

Valor en la celda

Error al ejecutar una macro

Los 3 primeros métodos de identificación de errores hacen referencia cuando se

introduce una formula la cual es incapaz de calcular el resultado, ya sea porque no

existe la función o porque los datos que aportamos a la función son incorrectos:

Cuadro de dialogo – Generalmente cuando cometemos un error en la introducción de

algún dato en una fórmula aparece el cuadro de dialogo, el cual nos hace una

propuesta para corregir el error.

Símbolo parte superior izquierda de la celda – Este signo aparece cuando Excel

identifica un posible error como una fórmula incoherente, un valor en formato de texto

cuando la celda contigua es un valor numérico, etc…. Cuando hacemos clic al símbolo

rojo automáticamente aparecerá un desplegable donde nos indica el posible error

cometido así como el modo a proceder para corregir dicho error.

Valor en la celda – Es posible que nos equivoquemos al introducir los valores

requeridos de una fórmula o función siendo imposible realizar el cálculo, ante estos

casos Excel puede mostrar algunos de los siguientes valores en la celda:

#¿NOMBRE?

#¡VALOR!

#¡NUM!

#¡DIV/0!

#¡REF!

#¡NULO!

#N/A

#####

El conocimiento de cada uno de estos errores nos permitirá conocer su origen y

solucionarlo.

Page 83: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 82

#¿NOMBRE? – Este error se produce cuando Excel no reconoce el texto de la

fórmula introducida en la celda, bien sea porque no está bien escrita la fórmula o

porque no existe.

Ejemplo - si queremos calcular la potencia de 3,4 elevado a 15 deberemos

introducir la siguiente fórmula o función en la celda correspondiente

"=POTENCIA(3,4;15)" en el caso que escribiésemos "=POTEN(3,4;15)" Excel nos

mostraría el error #¿NOMBRE?, dado a que no tiene registrado la función POTEN.

#¡VALOR! – Este tipo de error se produce cuando Excel detecta que se está

realizando cálculos con tipos de datos distintos entre sí, como datos numéricos,

texto o fecha y hora.

Ejemplo - La potencia de 4 elevado a una letra o texto "=POTENCIA(4;A)" dará

como resultado el error #¡VALOR!, puesto que no es posible ni tiene sentido la

realización de dicho cálculo.

#¡NUM! – Este error se produce cuando Excel detecta cuando una fórmula o

función requiere un dato numérico y se ha introducido un dato no numérico como

una letra o una fecha.

También puede ser que el resultado del cálculo resulta tan grande o pequeño que

Excel no pueda mostrarlo.

Ejemplo – El cálculo de la potencia de 1000 elevado a 103 da como resultado el

error #¡NUM! dado a que Excel no puede mostrar el valor tan elevado de dicho

cálculo.

#¡DIV/0! – Este error se produce cuando Excel detecta que se ha realizado un

cálculo de un número dividido por 0 o por una celda que no contiene ningún valor.

Ejemplo – El cálculo de una celda que contenga la siguiente fórmula "=5/0" dará

como resultado el error #¡DIV/0!

Page 84: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 83

#¡REF! – Este error se produce cuando Excel detecta el uso de una función o de un

cálculo con referencias de celdas no válidas.

Ejemplo – Este error se produce generalmente cuando se borran columnas o filas

que contienen datos vinculados a unas fórmulas, al desaparecer dichos datos las

fórmulas no pueden calcular y aparece el error #¡REF!

#¡NULO! – Este error se genera cuando queremos relacionar mediante algún

cálculo o función rangos de datos o celdas que no interseccionan.

Ejemplo – La suma de 2 rangos de datos separados, tal y como muestra la imagen

da como resultado el error #¡NULO!

#N/A –Este error se genera en las hojas de cálculo de Excel cuando se utilizan

funciones de búsqueda o coincidencia de datos los cuales no se existen en el rango

de búsqueda especificado.

Ejemplo – usar la función BuscarV para encontrar un valor que no existe en el

rango de búsqueda especificado.

##### - Este error se muestra cuando:

El valor introducido o calculado en la celda es más grande que el ancho de la

columna

Se ha calculado o introducido un valor negativo de una fecha.

Ejemplo – multiplicar una fecha por el valor de -2.

Page 85: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 84

Capítulo 4:

Restricción de Datos y Formatos condicionados

4.1 Formato Condicional

Como su nombre lo indica el formato condicional permite poder establecer el formato

(estilo, color, fondo, etc.) basado en una restricción o condición dada. Se puede aplicar

formato condicional a un rango de celdas, a una tabla de Microsoft Excel o a un informe

de tabla dinámica.

El formato condicional en Excel es una funcionalidad de gran utilidad al momento de

realizar el análisis de datos ya que puedes dar un formato especial a un grupo de

celdas en base al valor de otra celda. Esto te permitirá aplicar un tipo de fuente

específico o un color de relleno diferente para aquellas celdas que cumplan con ciertas

reglas y así poder identificarlas fácilmente en pantalla.

Razón para utilizar el formato condicional

Siempre tienes la opción de aplicar manualmente un formato a cada una de las celdas

que cumplen con una condición, pero eso se puede convertir en un proceso largo y

repetitivo, especialmente si tienes una tabla de datos muy grande y que cambia

frecuentemente. Es por eso que el formato condicional puede hacer más fácil la tarea

de cambiar automáticamente el formato de la celda que cumple con ciertos criterios.

Agregar Formato Condicional

1. Seleccione las celdas alas que desea agregar el Formato.

2. En el menú Inicio seleccione la opción “Formato Condicional”.

3. En el menú desplegado, seleccione cualquiera de las reglas previamente definidas

por Excel o puede seleccionar la opción “Nueva Regla…”, para poder personalizar sus

propias restricciones:

Page 86: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 85

Aplicar Formato a todas las celdas según sus valores:

Con el uso de escalas de colores, relleno de celdas e iconos, esta regla permite al

usuario clasificar sus datos numéricos de acuerdo a valores mínimos y máximos. Estas

escalas de colores son guías visuales que ayudan a comprender la variación y la

distribución de datos.

gradación de dos colores. El tono del color representa los valores superiores o

inferiores. Por ejemplo, en una escala de colores verde y rojo, se puede especificar que

las celdas de valor superior tengan un color más verde y las celdas de valor inferior

tengan un color más rojo.

Page 87: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 86

Una escala de tres colores permite comparar un rango de celdas utilizando una

gradación de tres colores. El tono de color representa los valores superiores, medios o

inferiores.

Una barra de datos le ayuda a ver el valor de una celda con relación a las demás. La

longitud de la barra de datos representa el valor de la celda. Una barra más grande

representa un valor más alto y una barra más corta representa un valor más bajo. Las

barras de datos son útiles para encontrar números más altos y más bajos

especialmente con grandes cantidades de datos, como las mayores y menores ventas

de juguetes en un informe de ventas.

Page 88: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 87

Utilice un conjunto de iconos para comentar y clasificar los datos en tres y hasta cinco

categorías separadas por un valor de umbral. Cada icono representa un rango de

valores. Por ejemplo, en el conjunto de iconos de 3 flechas, la flecha verde hacia arriba

representa los valores más altos, la flecha amarilla hacia los costados representa

valores medios y la flecha roja hacia abajo representa los valores más bajos.

Page 89: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 88

Aplicar Formato únicamente a las celdas que contengan:

Con esta regla usted podrá encontrar más fácilmente celdas específicas dentro de un

rango de celdas que contengan valores numéricos, texto, Fechas, Horas, errores,

celdas en blanco, etc., basándose en un operador de comparación. Por ejemplo, en una

hoja de cálculo de inventario ordenada según categorías, puede resaltar los productos

con menos de 10 artículos disponibles en amarillo o bien, en una hoja de cálculo de

resumen de almacén al por menor, puede identificar todos los almacenes con

beneficios superiores al 10%, volúmenes de ventas menores de USD 100.000, y región

igual a "Sudeste".

Aplicar Formato a los valores con rango inferior o superior:

Puede buscar los valores más altos y más bajos en un rango de celdas según un valor

de corte que especifique. Por ejemplo, puede buscar los 5 productos más vendidos en

un informe regional, el 15% de los productos del final de una encuesta al cliente o los

25 mejores salarios de un análisis de personal de departamento.

Aplicar Formato a los valores que estén por encima o por debajo del promedio:

Puede buscar valores por encima o por debajo del promedio o desviación estándar en

un rango de celdas. Por ejemplo, puede buscar los ejecutores medios anteriores en una

evaluación del rendimiento anual o puede buscar materiales fabricados que se

encuentran por debajo de dos desviaciones estándar de una calificación de calidad.

Aplicar Formato a los valores únicos o duplicados:

Esta regla le proporciona una manera fácil de resaltar valores (numéricos, texto, fechas

u horas) únicos en un rango de datos seleccionado.

Page 90: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 89

Utilice una fórmula que determine las celdas para aplicar formato:

Si el formato condicional tiene que ser más complejo, puede usar una fórmula lógica

(debe contener operadores lógicos que garanticen una respuesta de Verdadero o

Falso) para especificar los criterios de formato. Por ejemplo, puede que desee

comparar valores con un resultado devuelto por una función o evaluar datos de celdas

que se encuentran fuera del rango seleccionado, que pueden estar en otra hoja de

cálculo del mismo libro.

Ventajas del Formato Condicional

Cuando se analizan datos, es frecuente que surjan preguntas como:

¿Dónde están las excepciones en un resumen de beneficios de los últimos cinco

años?

¿Cuáles son las tendencias en una encuesta de opinión de marketing durante los

dos últimos años?

¿Quién ha vendido más de 50.000 dólares este mes?

¿Cuál es la distribución de antigüedad general de los empleados?

¿De qué productos han aumentado los ingresos más del 10% de año en año?

¿Cuáles son los estudiantes con mejores resultados y cuáles los de peores

resultados en la clase de 4º de secundaria?

Page 91: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 90

El formato condicional ayuda a responder estas preguntas porque facilita el proceso de

resaltar celdas o rangos de celdas interesantes, de destacar valores inusuales y de ver

datos empleando barras de datos, escalas de colores y conjuntos de iconos. Un formato

condicional cambia el aspecto de un rango de celdas en función de condiciones (o

criterios). Si la condición es verdadera, el rango de celdas basa el formato en dicha

condición; si la condición es falsa, el rango de celdas no tiene formato basado en dicha

condición.

Buscar valores usando formato condicional

Seguramente utilizas las hojas de Excel para almacenar grandes cantidades de datos,

como el catálogo de clientes de la empresa, el registro de las ventas de productos, etc.

Con esa gran cantidad de información es indispensable encontrar los datos precisos en

todo momento.

Buscar en Excel con formato condicional

En esta ocasión te mostraré cómo utilizar el formato condicional para buscar dentro de

una hoja de Excel y resaltar las celdas coincidentes. Observa la siguiente imagen:

Page 92: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 91

Para este ejemplo utilizaré los datos de la columna B y utilizaré la celda B2 como el

cuadro de búsqueda, es decir, la celda donde colocaré el término/texto que deseo encontrar

en los datos. Ahora debemos crear la regla que aplique el formato condicional y para eso se

deben seleccionar las celdas con los datos e ir al comando Formato condicional y seleccionar

la opción Nueva regla:

Page 93: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 92

Se mostrará el cuadro de diálogo Nueva regla de formato:

Fórmula para encontrar valores

Selecciona la opción Utilice una fórmula que determine las celdas para aplicar formato.

En la caja de texto coloca la siguiente fórmula, que explicaré en un instante:

=Y($B$2<>"",ENCONTRAR(MINUSC($B$2), MINUSC(B4)))

También haz clic en el botón Formato… para seleccionar el formato que se aplicará a

las celdas que cumplan la regla.

Page 94: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 93

La fórmula que acabas de escribir compara el texto de la celda de búsqueda ($B$2) con

las celdas que contienen los datos. La comparación se hace a través de la función

ENCONTRAR que compara ambos valores. Puedes observar también que para ambos

valores utilicé la función MINUSC que los convierte en minúsculas antes de hacer la

comparación de manera que la búsqueda no sea sensible a mayúsculas y minúsculas.

Finalmente, la intención de la función Y es evitar que el formato se aplique a todas las

celdas cuando la celda $B$2 esté vacía.

Ahora haz clic en el botón Aceptar y podrás comenzar a encontrar los valores que

introduzcas en la celda de búsqueda:

Page 95: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 94

4.2 Validación de Datos

La validación de datos es una función de Excel que permite establecer restricciones

respecto a los datos que se pueden o se deben escribir en una celda. La validación de

datos puede configurarse para impedir que los usuarios escriban datos no válidos. Si lo

prefiere, puede permitir que los usuarios escriban datos no válidos en una celda y

advertirles cuando intenten hacerlo. También puede proporcionar mensajes para indicar

qué tipo de entradas se esperan en una celda, así como instrucciones para ayudar a los

usuarios a corregir los errores.

Por ejemplo, en un libro de estadísticas, puede configurar una celda para permitir

únicamente números de cuenta de tres caracteres, o para seleccionar entre Hombre y

Mujer.

En otras palabras, la Validación de Datos prepara su hoja de cálculo para condicionar el

tipo de información que debe ingresarse en una celda determinada. Para efectuarla

asegúrese de realizar los siguientes pasos:

1. Seleccionar la(s) celda(s) a las que aplicara la validación de Datos. Seleccione la

opción “Validación de Datos” que se encuentra en el Menú Datos.

La Ventana de Validación de Datos está compuesta por 3 Pestañas:

Configuración.

Mensaje de Entrada.

Mensaje de Error.

2. En la Ventana “Configuración”, deberá seleccionar la Regla o restricción que

limitara el ingreso de los Datos a través del Criterio de Validación.

Page 96: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 95

a. Cualquier Valor: No existen

Limitaciones, el usuario de la

hoja podrá escribir cualquier

tipo de Dato.

b. Número Entero: Con este

criterio asegurara que las

personas únicamente

ingresen Números Enteros

(Negativos, Positivos o Cero) en las celdas seleccionadas, sin embargo,

usted deberá de seleccionar el rango posible para dichos números.

Ejemplo: Edades, Cantidad de Libros vendidos, etc.

c. Decimal: Este criterio es similar al criterio de Números Enteros, a diferencia

que con este criterio el usuarios podrá escribir números Decimales.

Ejemplo: Salario, Distancias, Libras.

d. Lista: El criterio Lista guarda un conjunto de ítems, que son visibles a través

del uso de una Lista Desplegable posicionada sobre la celda seleccionada.

Usted deberá escribir cada uno de los elementos de la Lista separados por

coma.

Ejemplo: Departamentos del País, Sexo, Escolaridad.

e. Fecha: Con este criterio asegurara que las personas únicamente ingresen

Fechas específicas ubicadas en un determinado rango.

Page 97: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 96

f. Hora: Las personas únicamente pueden agregar Horas especificas ubicadas

en un determinado rango.

g. Longitud de Texto: Este criterio de Validación determina la cantidad de

caracteres que podrá escribir en la celda seleccionada, cabe señalar que

usted podrá agregar cualquier tipo de carácter siempre y cuando cumpla con

la longitud establecida.

h. Personalizada: Esta opción permite a usted, crear sus propias reglas de

Validación a través del uso de una función o de una formula.

3. En la ventana “Mensaje de Entrada”, usted será libre de escribir un mensaje al

usuario, apenas este toque la celda con el cursor y que servirá como aviso para

notificar lo que se deberá escribir.

4. Los mensajes de Error, surgen cuando un usuario a ingresado información “NO

VALIDA” o “NO PERMITIDA” a la celda seleccionada; es decir, no está

cumpliendo el criterio de Validación escrito en “Configuración”.

Existen 3 tipos de mensa de Error:

Page 98: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 97

Icono Tipo Se Usa Para

Detener Evitar que los usuarios escriban datos no válidos en una celda.

Un mensaje de alerta Detener tiene dos opciones: Reintentar o

Cancelar

Advertencia Advertir a los usuarios que los datos que han escrito no son

válidos, pero no les impide escribirlos. Cuando aparece un

mensaje de alerta Advertencia, los usuarios pueden hacer clic en

Sí para aceptar la entrada no válida, en No para editarla o en

Cancelar para quitarla

Información Informar a los usuarios que los datos que han escrito no son

válidos, pero no les impide escribirlos. Este tipo de mensaje de

error es el más flexible. Cuando aparece un mensaje de alerta

Información, los usuarios pueden hacer clic en Aceptar para

aceptar el valor no válido o en Cancelar para rechazarlo

6. Clic en Aceptar. Si todo ha ido bien, en las celdas previamente seleccionadas

podrá únicamente ingresar los datos que apliquen el criterio de validación

definido en la pestaña Configuración.

Page 99: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 98

¿Cuándo es útil la validación de datos?

La validación de datos es sumamente útil cuando desea compartir un libro con otros

miembros de la organización y desea que los datos que se escriban en él sean exactos

y coherentes. Puede usar la validación de datos para lo siguiente, entre otras

aplicaciones:

Restringir los datos a elementos predefinidos de una lista. Por ejemplo, puede

limitar los tipos de departamentos a Ventas, Finanzas, Investigación y desarrollo

y TI.

Restringir los números que se encuentren fuera de un intervalo específico. Por

ejemplo, puede especificar un salario mínimo para el cálculo de deducciones de

un empleado de su empresa.

Restringir las fechas que se encuentren fuera de un período de tiempo

específico. Por ejemplo, puede especificar un período de tiempo entre la fecha

actual y los 3 días siguientes.

Restringir las horas que se encuentren fuera de un período de tiempo específico.

Por ejemplo, puede especificar un período de tiempo para servir el desayuno

entre la hora en que abre el restaurante y cinco horas después.

Limitar la cantidad de caracteres de texto. Por ejemplo, puede limitar el texto

permitido en una celda a 10 caracteres o menos.

Validar datos según fórmulas o valores de otras celdas. Por ejemplo, puede usar

la validación de datos para establecer un límite máximo para comisiones y

bonificaciones de 3.600 dólares, según el valor de nómina proyectado general. Si

los usuarios escriben un valor de más de 3.600 dólares en la celda, aparecerá un

mensaje de validación.

Sugerencias para trabajar con la validación de datos

En la siguiente lista, encontrará sugerencias para trabajar con la validación de datos en

Excel.

Si tiene previsto proteger la hoja de cálculo o el libro, hágalo después de haber

terminado de configurar la validación. Asegúrese de desbloquear cualquier celda

Page 100: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 99

validada antes de proteger la hoja de cálculo. De lo contrario, los usuarios no

podrán escribir en las celdas.

Si tiene previsto compartir el libro, hágalo únicamente después de haber

configurado la validación y la protección de datos. Después de compartir un libro,

no podrá cambiar la configuración de validación a menos que deje de compartirlo

pero Excel continuará validando las celdas que haya designado mientras el libro

esté compartido.

Puede aplicar la validación de datos a celdas

en las que ya se han escrito datos. No

obstante, Excel no le notificará

automáticamente que las celdas existentes

contienen datos no válidos. En este escenario,

puede resaltar los datos no válidos indicando a Excel que los marque con un

círculo en la hoja de cálculo.

Para quitar rápidamente la validación de datos de una

celda, seleccione la celda y a continuación abra el cuadro

de diálogo Validación de datos (ficha Datos, grupo

Herramientas de datos). En la ficha Configuración, haga

clic en Borrar todos.

Para buscar las celdas de la hoja de cálculo que tienen

validación de datos, en la ficha Inicio en el grupo Modificar,

haga clic en Buscar y seleccionar y a continuación en Validación de datos. Una

vez que haya encontrado las celdas que tienen validación de datos, puede

cambiar, copiar o quitar la configuración de validación.

Si la validación de datos no funciona, asegúrese de que los usuarios no están copiando

datos ni rellenando celdas. La validación de datos está diseñada para mostrar mensajes

Page 101: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 100

y evitar entradas no válidas sólo cuando los usuarios escriben los datos directamente

en una celda. Cuando se copian datos o se rellenan celdas, no aparecen mensajes.

Para impedir que los usuarios copien datos y rellenen celdas mediante la

operación de arrastrar y colocar, desactive la casilla de verificación Permitir

arrastrar y colocar el controlador de relleno y las celdas, en la categoría

Avanzadas del cuadro de diálogo Opciones de Excel (pestaña Archivo, comando

Opciones) y, a continuación, proteja la hoja de cálculo.

Page 102: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 101

Capítulo 5:

Tablas Dinámicas

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a

varios criterios de agrupación, representado como una tabla de doble entrada que nos

facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo

diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando

o no los datos origen, etc...

Con un informe de tabla dinámica puede resumir, analizar, explorar y presentar un

resumen de los datos de la hoja de cálculo o un origen de datos externos (datos

externos: datos que se almacenan fuera de Excel; por ejemplo, bases de datos creadas

en Access, , SQL Server o en un servidor Web.). Un informe de tabla dinámica es

especialmente útil cuando tiene una larga lista de cifras para sumar y los datos

agregados o subtotales podrían servir para mirar los datos desde perspectivas

diferentes y comparar las cifras de datos similares.

El diseño de una Tabla Dinámica radica en conocer su estructura e identificar como

queremos mostrar la información de interés, para lo cual podemos hacer uso de 3

preguntas clave ¿Qué queremos ver?, ¿Cómo lo queremos ver?, ¿Cuáles queremos

ver?.

Las Tablas Dinámicas se conforman de 3 partes:

Filtros: Como todo Filtro, se encarga de Restringir la información, de mostrar

solamente los datos de interés. Fácilmente podemos identificar si necesitamos

un Filtro a través de la pregunta ¿Cuáles queremos ver?

Encabezados de Fila/ Columna: Conforman el marco bajo el cual se creara la

Tabla Dinámica, separan la información de acuerdo a ¿Cómo queremos

visualizarla?

Page 103: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 102

Valores: Es la información de interés que será colocada en las celdas centrales

de la Tabla Dinámica; es decir los datos numéricos que realizan el resumen de

nuestra Tabla de Datos y que conforman nuestro reporte.

Insertar una Tabla Dinámica

1. Seleccione las celdas que desea Resumir con Tabla Dinámica; estas celdas

deben contener información y todos sus encabezados.

2. En el menú Insertar seleccione la opción Tabla Dinámica.

3. En la ventana que aparece, servirá para:

a. Confirmar que las celdas seleccionadas son las que

desea convertir en Tabla o utilizar una fuente de datos diferente a la hoja

de cálculo actual.

b. Indicar a Excel donde deberá aparecer el resumen de la Tabla Dinámica,

en una Hoja de cálculo nueva o en un lugar especifica que usted

seleccionara con la ayuda del botón. Clic en Aceptar.

FILTRO ¿Cuáles…?

EN

CA

BEZ

AD

O D

E F

ILA

¿C

om

o…

?

ENCABEZADO DE COLUMNA ¿Como…?

VALORES

¿Qué…?

Page 104: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 103

4. En el lado derecho de la Hoja de Cálculo se mostrara 1

panel que contiene todos los encabezados de su Tabla de

información, y en la parte inferior identificara 4 paneles

rotuladas con las diferentes partes que conforman la Tabla

Dinámica: Filtros, Encabezado de Columna, encabezado

de Fila y Valores.

5. Arrastre a cada uno de los paneles inferiores, los

encabezados de su Tabla de Información y lograra ver

como se forma la Tabla Dinámica.

Una vez que la Tabla Dinámica ha sido creada, usted podrá fácilmente ver un resumen

de los datos de interés intercambiando la forma de esta, de manera rápida y sencilla.

Ubicándose en cualquiera de las celdas de una Tabla Dinámica, observara el

despliegue de 2 nuevas pestañas en el menú: Opciones y Diseño.

A través del menú de Opciones, la Tabla Dinámica podrá cambiar los valores

presentados en función de operaciones matemáticas mostradas, calcular campos que

no existen en la tabla original, insertar Gráficos, etc.

En el menú Diseño, fácilmente podrá cambiar el formato de la Tabla, así como los

campos de Totales y subtotales de la misma.

Page 105: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 104

Configuración de Campo

La configuración de Campo de Valor, disponible dentro del menú Opciones de una

Tabla Dinámica, le permite cambiar el tipo de operación matemática a realizar en el

campo de Valores de su Tabla Resumen:

Además de esto, existe la posibilidad de Mostrar los Valores como Porcentaje, tomando

de Referencia uno de los valores existentes en su Tabla de Datos Original o en relación

a un valor especifico de Filas o Columnas de la Tabla Pivote.

Campos Calculados

Los Campos Calculados permiten crear campos adicionales a los existentes a la Tabla

de Datos original, a partir de operaciones matemáticas o funciones creadas a partir de

los valores existentes en el origen de la información.

Page 106: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 105

El Diseño de una Tabla Dinámica

Conforme se agregan encabezados de Filas o Encabezados de Columna en su Tabla

de Resumen, encontrara que cada vez podrá hacerse más complicada la presentación

de los datos, sin embargo Excel le permite poder cambiar el estilo de Diseño de los

encabezados de Columnas agregando subtotales, totales, esquematizando los campos,

compactando la información e incluso con la inserción de bandas de colores que

permiten separar los datos.

Page 107: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 106

Capítulo 6:

Importación de Datos

En el mundo empresarial existen muchas herramientas informáticas corporativas que

son vendidas a granel o incluso existen herramientas de software diseñadas al gusto y

medida de las necesidades. Sin embargo, muchas de estas herramientas están

enmarcadas en un conjunto de datos ya establecidos o una serie de reportes fijos, que

un usuario del sistema no podría cambiar con facilidad y para lo cual es necesaria la

ayuda de un programador.

En este aspecto Microsoft Excel ha desarrollado u conjunto de herramientas y técnicas

que permiten conectarse con bases de datos empresariales que han sido diseñadas en

otros sistemas de información y que exitosamente pueden ser llevadas a Excel. En

esencia para importar datos externos a Excel disponemos básicamente de dos

opciones:

Utilizar el portapapeles de Windows, es decir, copiar los datos de la aplicación

externa y pegarlos en una hoja de Excel.

Importar datos de otro archivo que no tiene que ser necesariamente del formato

Excel.

La primera opción es la más directa, pero tiene el contrapunto de ser más laboriosa y

tediosa, puesto que en ocasiones pueden existir conflictos con los formatos y signos

decimales.

El problema fundamental de la importación de datos externos, es debido al formato de

los propios archivos, esto quiere decir que cada aplicación genera un archivo con un

formato propio para identificar mejor el contenido de los datos, por ejemplo Excel al

generar un archivo .xls o .xlsx no solamente guarda los datos que hemos introducido

sino que lo guarda con un formato especial para interpretar el documento en su

plenitud, de esta manera sabe exactamente dónde están las fórmulas, qué formato

estético tiene el documento, etc.

Page 108: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 107

NOTA Al importar datos de una aplicación es posible que algunas de las

fórmulas no se ajusten correctamente, que el formato no se ajuste al original o

incluso que fórmulas y funciones que no entiende no las copie.

Pese a todos estos contratiempos, siempre es mejor intentar realizar una importación y

después comprobar si todo ha salido correctamente.

6.1 Importación desde Texto

Hay dos formas de importar datos de un archivo de texto con Microsoft Excel: puede

abrir el archivo de texto en Excel o puede importarlo como un rango de datos externos.

Son dos los formatos de archivo de texto que se usan habitualmente:

1. Archivos de texto delimitado (.txt), en los que el carácter de tabulación (el código

de carácter ASCII 009) separa normalmente cada campo de texto.

2. Archivos de texto de valores separados por comas (.csv), en los que el carácter

de coma (,) separa normalmente cada campo de texto.

Puede cambiar el carácter separador que se utiliza tanto en los archivos de texto

delimitados como en los .csv. Esto puede ser necesario para asegurarse de que la

operación de importación o de exportación se realizará de la manera deseada

Un archivo de texto creado con otro programa se puede abrir como un libro de Excel

con el comando Abrir. Al abrir un archivo de texto en Excel, no cambia su formato:

puede verlo en la barra de título de Excel, donde el nombre del archivo conserva la

extensión del nombre del archivo de texto (por ejemplo, .txt o .csv).

I. Haga clic en la pestaña Archivo y, a continuación, haga clic en Abrir.

Aparece el cuadro de diálogo Abrir.

II. En la lista, seleccione Archivos de texto.

III. Busque el archivo de texto que desee abrir y haga doble clic en él.

Importar un archivo de texto conectándose a él

Puede importar datos de un archivo de texto en una hoja de cálculo existente como un

rango de datos externos.

Page 109: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 108

A. Haga clic en la celda en la que desea

colocar los datos del archivo de texto.

B. En la ficha Datos, en el grupo Obtener

datos externos, haga clic en Desde texto.

C. Busque el archivo de texto que desee importar y haga doble clic en él.

D. Elija el tipo de archivo que describa los datos con mayor precisión:

a. Delimitados: Le indica que los datos de cada columna están siendo

separados por un carácter especial (coma, punto y coma, tabulación, etc.)

b. De ancho fijo: No existen separadores entre una columna y otra,

simplemente cada columna mide un valor determinado y cada valor de la

columna no es más grande que el anterior.

E. Establezca los separadores de columnas:

Page 110: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 109

F. Establecer el formato de los datos.

G. Clic en Finalizar.

6.2 Importación desde la Web

En el amplio mundo del internet existen una serie de páginas o sitios web que han sido

diseñadas para poder visualizar información desde cualquier lugar bajo un esquema de

diseñado que se ha basado en Tablas; es decir que todo el contenido de un sitio web

está hecho con Tablas.

Delimitado De ancho fijo

Page 111: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 110

Microsoft Excel cuenta con una herramienta especial para poder traer datos

directamente desde internet, a través de un navegador especial que forma parte de las

herramientas del mismo; este navegador web tiene la particularidad de visualizar todas

las tablas existentes en un sitio web a partir de “check boxes” que determinan el inicio

de una tabla en el sitio web.

Importar datos desde un sitio web

Puede importar datos desde internet en una hoja de cálculo siguiendo los pasos que a

continuación se detallan:

A. Haga clic en la celda en la que desea

colocar los datos.

B. En la ficha Datos, en el grupo Obtener

datos externos, haga clic en Desde Web.

C. Automáticamente se abrirá el navegador de Microsoft Excel, en donde en la

parte superior deberá colocar la dirección del sitio desde el cual extraerá la

información.

Page 112: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 111

NOTA La importación desde web únicamente trabaja con páginas web, usted no

podrá extraer información con archivos .pdf, aunque estos estén alojados en un

sitio web.

D. Una vez que el sitio ha sido cargado completamente (el sitio se ha cargado

completamente cuando logra observar flechas como esta “ ”) debe seleccionar

la información que es de su interés a través de la selección de las tablas

correspondientes (la información estará seleccionada correctamente cuando

observe que la flecha ha cambiado por un símbolo similar a este “ ”)

E. Clic en el botón Importar.

6.3 Importación desde Access

Microsoft Access es parte de la suite de herramientas de Office en la administración de

datos, diseñado para la administración de bases de datos empresariales o personales.

Al ser parte de las herramientas de Office, es normal suponer que Excel cuente con un

enlace directo para extraer datos desde una base de datos de Access.

Page 113: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 112

NOTA Una Base de Datos esta compuesta por varias tablas. Con la importación

de datos desde Access usted podrá extraer únicamente los datos de una tabla y

forzosamente tendrá que visualizar todos los registros de la misma.

Importar datos desde una base de datos de Access

A. Haga clic en la celda en la que desea

colocar los datos.

B. En la ficha Datos, en el grupo Obtener

datos externos, haga clic en Desde Access.

C. Busque la base de datos que desea que desee importar y haga doble clic en él.

D. Seleccione la Tabla de registros con la que desee trabajar y clic en Aceptar.

E. En la ventana que aparece seleccione ¿Cómo desea ver los datos? Y el lugar

donde los desea ubicar. Posteriormente clic en

Aceptar.

Page 114: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 113

NOTA El botón de Propiedades, establece características avanzadas para la

Conexión de la Base de datos en cuanto a su Uso y Definición. El uso está

orientado a establecer el control de actualizaciones, idiomas, etc. En la definición

usted podrá ver el código fuente generado automáticamente para establecer la

conexión entre Excel y la base de datos.

6.4

6.5 Microsoft Query

Microsoft Query es un programa que permite incorporar datos de orígenes externos a

otros programas de Microsoft Office, especialmente a Microsoft Excel. Si utiliza Query

para recuperar datos de las bases de datos de los archivos corporativos, no es

necesario que vuelva a escribir en Excel los datos que desee analizar. También puede

actualizar los informes y resúmenes de Excel automáticamente de la base de datos de

origen inicial siempre que la base de datos se actualice con información nueva.

Una vez establecido un origen de datos para una

base de datos determinada, lo podrá utilizar siempre

que desee crear una consulta para seleccionar y

Page 115: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 114

recuperar los datos de esa base de datos, sin tener que volver a escribir toda la

información de conexión. Query utiliza el origen de datos para conectarse con la base

de datos externa y mostrar los datos que están disponibles. Después de crear la

consulta y devolver los datos a Excel, Query recupera los datos y proporciona al libro de

Excel la información de la consulta y de los datos de modo que pueda volverse a

conectar con la base de datos cuando desee actualizar los datos.

La Base de Datos

Una Base de Datos es una herramienta que almacena información o datos,

permitiéndole crear, leer, editar y borrar información de alguna manera.

Todo Software o programa utilizado a nivel empresarial para la manipulación de datos

(elaboración de facturas, inventarios, administración de recursos humanos, etc.)

únicamente manipula información que se encuentra almacenada en una base de datos;

es decir, el sistema se alimenta de la base de datos para poder mostrar la información

de manera ordenada al usuario, en ningún momento el sistema guarda información si

no existe una base de datos.

Para poder importar datos a Microsoft Excel desde una Base de Datos, es necesario

primeramente tener una Base de datos desde donde se puedan extraer los datos. Estas

pueden encontrarse en diferentes formatos y en diferentes softwares administradores

de BD:

Microsoft Access.

Sql Server.

MySql.

Oracle.

Microsoft Excel (En Excel también pueden almacenarse datos).

Archivos de texto plano (CSV o diseñados en bloc de notas que han sido

delimitados).

Otros.

La incorporación de datos a Excel a partir de otros programas es posible siempre y

cuando los datos estén almacenados en Tablas, lo que hace fácil poder extraer

Page 116: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 115

información desde una Base de Datos, ya que estas se componen de una o más Tablas

que contienen información relacionada entre sí.

NOTA A diferencia de la Importación de datos desde Access, en donde

solamente incorporábamos datos de una Tabla y todos los datos de la misma,

con Microsoft Query es posible combinar varias tablas para almacenar

información más completa, además de permitir filtrar la información que

contendrá.

Microsoft Query trabaja las diferentes de la combinación de diferentes Tablas. Una

combinación es una conexión entre dos tablas o mas donde estas se combinan según

un campo que tienen en común, creando una nueva tabla virtual (que se puede guardar

como una tabla real). Por Ejemplo:

Tabla de Nombres

Nombre Edad Cod.

Departamento

Adán Acuña 28 años 2

Bertha Báez 35 años 3

Camilo Castro 18 años 1

Danilo Duarte 16 años 5

Tabla de Departamentos

Cod.

Departamento

Departamento

1 Managua

2 Estelí

3 Madriz

4 Nueva Segovia

Page 117: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 116

Tabla Combinada

Nombre Edad Cod.

Departamento

Departamento

Adán Acuña 28 años 2 Estelí

Bertha Báez 35 años 3 Madriz

Camilo Castro 18 años 1 Managua

El Archivo de Origen de Datos

El origen de datos externos (en este caso la Base de Datos) se conecta al libro a través

de una conexión de datos, que es un conjunto de información que describe cómo

localizar, iniciar una sesión, crear una consulta y tener acceso al origen de datos

externo.

Cuando se conecta a un origen de datos externos, también puede realizar una

operación de actualización para recuperar los datos actualizados. Cada vez que

actualice los datos, verá la versión más reciente de los datos, incluidos los cambios

realizados desde la última actualización; en otras palabras, el archivo de conexión de

datos permite mantener un enlace entre Excel y la Base de Datos externa permitiendo

actualizar los datos en Excel una vez que cambien los datos en la Base de Datos

original.

NOTA A través de un archivo de conexión de datos el usuario evita la

importación continua de los datos a Excel cada vez que la base de datos es

actualizada, puesto que los datos en Excel son actualizados con la Base de

Datos, sin embargo si usted realiza cambios en los datos de la Hoja de Excel, esto

no afectara los registros de su Base de Datos original.

Para lograr la Importación de Datos con Microsoft Query es necesario contar con un

archivo de conexión de datos lo cual realizara con los pasos siguientes:

Page 118: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 117

1. Haga clic en la celda en

la que desea colocar los

datos.

2. En la ficha Datos, en el

grupo Obtener datos

externos, haga clic en

De otras fuentes. De la

Lista de opciones que

se ha desplegado,

seleccione la opción denominada “Desde Microsoft Query”.

3. Elija el origen de Datos para establecer la conexión, seleccionando la opción

<Nuevo Origen de Datos> y clic en Aceptar.

4. Escribir un nombre para el origen de Datos, el cual puede ser cualquiera de su

elección y que le ayude a identificar que datos son los que extrae de a BD.

Page 119: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 118

5. En dependencia del tipo de Base de Datos con la que se desea conectar usted

deberá seleccionar el controlador o extensión de la Base de Datos.

6. Seleccione el botón Conectar, en donde deberá seleccionar la ubicación de la

Base de Datos con la que se desea conectar. Aparecerá una ventana que

cambiara en dependencia del tipo de controlador que ha elegido. La imagen

situada a su izquierda corresponde a la ventana para una Base de Datos de

Access y a la derecha una ventana para una Base de Datos de Sql Server.

Page 120: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 119

En cualquier ventana que aparezca debe seleccionar la ubicación de la Base de Datos

correspondiente del lugar correspondiente.

7. Clic en Aceptar.

Podrá Observar que su nuevo archivo de conexión de datos ha sido creado, puesto que

será visible en la ventana de Elección del Origen de Datos.

Microsoft Query

Para poder entrar a Microsoft Query debe cerciorarse en deshabilitar la opción de “Usar

el Asistente para consultas para crear o modificar consultas”, de la ventana de Elección

del Origen de Datos. Una vez deshabilitada seleccione el origen de Datos y clic en

Aceptar.

Una vez en Microsoft Query usted deberá cumplir con 4 pasos fundamentales para

poder extraer los datos de manera exitosa:

Page 121: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 120

I. Insertar Tablas de la Base de Datos.

II. Combinar Tablas.

III. Agregar Registros.

IV. Establecer los criterios.

A continuación se detallan los procesos de cada uno de los anteriores:

I. Insertar las Tablas:

Una vez seleccionado el origen de datos, aparecerá una ventana que contendrá las

diferentes Tablas existentes en la Base de Datos. Seleccione las Tablas a partir de las

cuales desea incorporar los datos presionando doble clic sobre cada una de ellas o

posicionando el cursor sobre la Tabla de Interés y

presionando el botón Agregar.

Cada una de las tablas que seleccione contiene

información en particular de su interés, no es

necesario agregar tablas que no contienen

información relevante para usted, esto hace que la

información contenida en su consulta sea

exclusiva para el trabajo que la necesite.

Una vez que agrego todas las Tablas de interés seleccione el botón cerrar. Podrá

observar como cada una de las Tablas se han agregado al entorno de Microsoft Query

una a una.

En caso de haber incluido una Tabla no

deseada podrá eliminarla seleccionando la

misma y dando en clic en el menú Tabla ---

Quitar Tabla.

Así mismo si ha olvidado alguna Tabla

seleccione el menú Tabla --- Agregar Tablas...,

esto desplegara nuevamente la ventana para

Page 122: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 121

Agregar Tablas.

II. Combinar Tablas:

Como se mencionó anteriormente, la combinación de Tablas es la creación de una

nueva Tabla virtual a través de la combinación de dos o más Tablas reales que tienen

campos en común. En el ejemplo de la imagen anterior se observa que al incorporar las

tablas Productos y Grupos, automáticamente existe una línea que une a ambas.

Esto es posible cuando la Microsoft Query identifica que amabas tablas tienen campos

que se llaman de la misma manera y que contienen el mismo tipo de información.

NOTA: El que dos tablas posean campos con el mismo nombre, no implica que

sean campos en común. Por Ejemplo:

Tabla de Empleados

Cod Empleado Nombre Cargo

FI1495 Marcela Mora Gerente

Financiero

CO3256 Casimiro Colon Contador

VT8965 Rubén Raudez Vendedor

VT3625 Zaida Zeledón Vendedor

Page 123: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 122

Tabla de Ventas

Factura Monto C$ Vendedor

15689 4’500ºº VT8965

15690 8’900ºº VT3625

15691 1’500ºº VT0645

15692 12’400ºº VT8965

Las Tablas anteriores forman parte de una Base de Datos corporativa, la primera es

utilizada por el departamento de Recursos Humanos y en la cual se registran todos los

empleados de la corporación. La segunda es usada por el área de Ventas y en donde

se registra cada venta realizada. Note que en la segunda tabla existe un campo llamado

Vendedor que registra el código del vendedor que realiza la venta, en esta tabla no es

necesario escribir el nombre del empleado puesto que este ya existe en la Tabla de

Empleados.

Para lograr unir dos tablas basta con arrastrar los campos en común de una tabla a la

otra o bien auxiliarse con el menú Tablas --- Uniones, en donde podrá observar 3

diferentes tipos de uniones:

i. Incluir solo los registros de las tablas en donde los campos en común sean

iguales.

Factura Monto C$ Vendedor Nombre Cargo

15689 4’500ºº VT8965 Rubén Raudez Vendedor

15690 8’900ºº VT3625 Zaida Zeledón Vendedor

15692 12’400ºº VT8965 Rubén Raudez Vendedor

ii. Incluir todos los registros de la primera Tabla y solo los registros de la segunda

tabla en donde los campos en común sean iguales.

Page 124: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 123

Factura Monto C$ Vendedor Nombre Cargo

15689 4’500ºº VT8965 Rubén Raudez Vendedor

15690 8’900ºº VT3625 Zaida Zeledón Vendedor

15692 12’400ºº VT8965 Rubén Raudez Vendedor

- - FI1495 Marcela Mora Gerente

Financiero

- - CO3256 Casimiro Colon Contador

iii. Incluir todos los registros de la segunda Tabla y solo los registros de la primera

tabla en donde los campos en común sean iguales.

Factura Monto C$ Vendedor Nombre Cargo

15689 4’500ºº VT8965 Rubén Raudez Vendedor

15690 8’900ºº VT3625 Zaida Zeledón Vendedor

15691 1’500ºº VT0645 - -

15692 12’400ºº VT8965 Rubén Raudez Vendedor

III. Insertar las Tablas:

Las Tablas unidas ayudaran a

conformar lo registros de la nueva

tabla virtual que será utilizada en los

reportes de interés. En el menú

Registros --- Agregar Columnas,

seleccione todos los campos que

poseerá su nueva Tabla.

Cada vez que Micosoft Query

accede a un campo de una Tabla determinada, este coloca primeramente el nombre de

la Tabla, seguido por el nombre del campo (por ejemplo Empleados.Nombre, indica que

se trabajara con la columna Nombre de la Tabla Empleados). En caso de seleccionar *

Page 125: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 124

antecedido por el nombre de una Tabla, indica que se agregaran todas las columnas de

dicha Tabla.

El campo Titulo de la columna, hace referencia a un alias o apodo que podrá colocar al

encabezado de la tabla, en caso que el nombre inicia de la columna contenido en la

tabla no refleje el contenido de la información bajo la misma (por ejemplo si el campo es

llamado Grupo.NombreGrupo, un buen Titulo de columna seria Categoría).

Igualmente es posible agregar campos que contengan operaciones matemáticas que

consoliden información de un registro.

IV. Insertar Criterios:

A través los criterios es posible establecer restricciones a los registros que deseamos

enviar a Excel, los cuales están basados en la información de las tablas que han sido

incorporadas en la creación de su nueva tabla. Por ejemplo usted desea ver

únicamente las facturas de un mes o un año determinado.

Los criterios son establecidos con ayuda del menú

criterios --- Agregar Criterios, en la ventana que

aparece se ingresara el nombre del campo que

servirá como restricción, un operador lógico que

establezca la comparación con el valor

deseado (igual, menor, mayor, diferente, etc.) y

el valor que debe cumplir el campo de interés. Cuando agrega el primer criterio,

Microsoft Query deshabilita los botones de opción Y & O que se encuentran en la parte

superior izquierda, sin embargo después de haber completado el primer criterio, estos

botones estarán habilitados para su selección en donde el botón Y exigirá que todos los

criterio sean cumplidos, y la opción O solamente indicara que se cumpla uno de todos

los criterios.

El botón de Valores facilitara el trabajo al momento de establecer el valor de restricción,

puesto que con él se visualizaran los diferentes valores del campo establecido,

reduciendo los errores de digitación. Una vez que se han agregado todos los criterios

finalice Cierre la ventana de Criterios.

Page 126: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 125

Regresar los Datos a Excel

Uno de los principales objetivos de Microsoft Query es

realizar consultas a una base de datos corporativa

permitiendo llevar los resultados de la misma a Excel, en

donde se realizaran os análisis correspondientes de la

información recopilada a través de comandos, funciones

y herramientas de Excel.

Para devolver los datos a Excel seleccione la opción “Devolver datos a Microsoft Excel”

del menú Archivo. En el libro de Excel aparece el cuadro de dialogo de importación de

datos en donde seleccionara la forma y el lugar en donde se colocaran los datos

extraídos desde Microsoft Query.

Page 127: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 126

Capítulo 7:

Macros

7.1 Excel y Visual Basic para Aplicaciones

VBA, siglas de Visual Basic para Aplicaciones, es un lenguaje de programación

desarrollado por Microsoft. Excel, junto con los demás miembros de Microsoft Office,

incluye el lenguaje VBA (sin cargo extra). En pocas palabras, VBA es la herramienta

que la gente utiliza para desarrollar programas de control de Excel.

Imagínese un robot inteligente que sabe todo acerca de Excel. Este robot puede leer las

instrucciones, y también puede operar Excel muy rápido y con precisión. Si desea que

el robot haga algo en Excel, usted escribe un conjunto de instrucciones de robots

mediante códigos especiales.

Inserción de texto

Si a menudo tiene que introducir su nombre de compañía, dirección y número de

teléfono en las hojas de cálculo, puede crear una macro que haga la escritura para

usted.

La automatización de una tarea que realiza con frecuencia

Suponga que usted es un gerente de ventas y necesita preparar un informe de ventas

de fin de mes. Si la tarea es muy sencilla, se puede desarrollar un programa de VBA

que lo haga por usted.

Automatizar operaciones repetitivas

Si tiene que realizar la misma acción en, por ejemplo, 12 diferentes libros de Excel,

puede grabar una macro mientras realiza la tarea en el primer libro y luego dejar que la

macro repite su acción en los otros libros.

Creación de un comando personalizado

¿A menudo se emite la misma secuencia de comandos de menú de Excel? Si es así,

ahorrarse unos segundos mediante el desarrollo de una macro que combina estos

Page 128: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 127

comandos en un solo comando personalizado, que se puede ejecutar con una sola

tecla o clic de botón. Probablemente no va a ahorrar mucho tiempo, pero es probable

que sea más precisa.

El desarrollo de nuevas funciones de hoja

Aunque Excel incluye numerosas funciones incorporadas (como SUMA y PROMEDIO),

puede crear funciones en hojas que pueden simplificar en gran medida sus fórmulas.

Creación de aplicaciones completas, macro impulsadas

Igualmente puede utilizar VBA para crear aplicaciones a gran escala con una ficha de la

cinta de menu, cuadros de diálogo, ayuda en pantalla, y muchos otros pertrechos.

Crear complementos personalizados para Excel

Probablemente usted está familiarizado con algunos de los complementos que se

incluyen con Excel. Por ejemplo, las Herramientas para análisis es un popular

complemento. Puede utilizar VBA para desarrollar su propio propósito especial

complementos.

7.2 Ventajas de VBA

Usted puede automatizar casi cualquier cosa que hagas en Excel. Para ello, escriba las

instrucciones que Excel realizara. La automatización de una tarea utilizando VBA ofrece

varias ventajas:

Excel siempre ejecuta la tarea exactamente de la misma manera. (En la mayoría

de los casos, la consistencia es una buena).

Excel realiza la tarea mucho más rápido de lo que puede hacerlo de forma

manual.

Si eres un buen programador de macro, Excel siempre realiza la tarea sin

errores.

Si configura las cosas de manera adecuada, alguien que no sabe nada acerca

de Excel puede realizar la tarea.

Page 129: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 128

7.3 Desventajas de VBA

Usted tiene que saber cómo escribir programas en VBA, sin embargo no es tan

difícil como se podría esperar.

A veces, las cosas van mal. En otras palabras, no se puede asumir ciegamente

que su programa VBA siempre funciona correctamente en todas las

circunstancias.

VBA es un blanco móvil. Como ustedes saben, Microsoft está continuamente

actualizando Excel. A pesar de que Microsoft pone gran esfuerzo en la

compatibilidad entre versiones, es posible que descubra que el código VBA que

has escrito no funciona correctamente con versiones anteriores o con una futura

versión de Excel.

7.4 La Grabadora de Macros

La grabadora de macros almacena cada acción que se realiza en Excel, similar a una

grabadora de videos, por eso es conveniente planear con antelación los pasos a seguir

de manera que no se realicen acciones innecesarias mientras se realiza la grabación.

Antes de iniciar en el desarrollo de aplicaciones será necesario agregar la pestaña

programador al Menú de Excel. Al hacer clic en la ficha Programador, la cinta muestra

la información que es de interés para los programadores. La figura muestra el aspecto

de la cinta de opciones cuando se selecciona la ficha Programador.

La ficha Programador no está visible en las opciones por defecto de Excel, por lo cual

deberá habilitarse; esta acción se realiza una sola vez:

Haga clic en cualquier parte del menú de la cinta de opciones y seleccione Personalizar

la cinta.

Page 130: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 129

En la pestaña Personalizar cinta de opciones del cuadro de diálogo Opciones de Excel,

busque Programador en la segunda columna.

Ponga una marca de verificación junto a Programador.

Haga clic en Aceptar, y ya está de nuevo a Excel con un nuevo tab: Programador.

A continuación realizaremos una macro con la ayuda de la grabadora, para adentrarnos

al código agregado en VBA (efectivamente, la grabadora de macros nos ayuda a crear

el código sin necesidad de escribir una sola línea del mismo). La macro que está a

punto de crear escribira su nombre en una celda, introducira la fecha y la hora actuales

en la celda de abajo, dara formato a ambas celdas para mostrarlas en negrita y

cambiara el tamaño de fuente de las céldas, a 16 puntos.

1. Seleccione una celda.

2. Elija Programador ➪ Macro ➪ Grabar macro, o haga clic en la grabación macro

botón en la barra de estado.

3. Aparecerá el cuadro de diálogo Grabar macro, como se muestra en la Figura 2.

4. Introduzca un nombre para la macro.

5. Excel proporciona un nombre predeterminado, pero es mejor usar un nombre

más descriptivo, sin espacios.

6. Haga clic en el cuadro Tecla de método abreviado, especificación de una tecla

de acceso directo es opcional

7. Asegúrese de que el lugar de almacenamiento de la Macro en este Libro.

8. Puede introducir un texto en el cuadro Descripción, sin embargo esto es

opcional.

9. Haga clic en Aceptar.

10. El cuadro de diálogo se cierra y grabadora de macros de Excel está encendido.

Desde este punto, Excel graba todo lo que haces y lo convierte en código VBA.

11. Escriba su nombre en una celda.

12. Mueva el puntero de celda a la celda de abajo e introduzca la siguiente fórmula:

= NOW () (la fórmula muestra la fecha y la hora actuales).

Page 131: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 130

13. Seleccione la celda de la fórmula y pulse Ctrl + C para copiar esa celda al

Portapapeles.

14. Seleccione Inicio ➪ Pegar ➪ Valores. Este comando convierte la fórmula en su

valor.

15. Con la celda seleccionado, pulse Shift + flecha hacia arriba para seleccionar la

celda actual y la de arriba (la que incluye su nombre).

16. Cambie el formato de las celdas.

17. Detenga la macro.

Aunque esto es una gran ventaja sobre cualquier otro programa diseñador de

aplicaciones, solo se puede grabar las cosas que se pueden hacer directamente en

Excel, algunas tareas como el mostrar un cuadro de mensaje o diseñar un formulario

son cosas meramente de VBA, y no podrán realizarse en Excel (entiéndase sin la

ayuda de código VBA).

De forma predeterminada, excel coloca la macro grabada en un módulo en el libro

activo. Si lo prefiere, puede grabar en un nuevo libro (excel abre un libro en blanco) o

en el libro de macros personal.

Su libro de macros personal es un libro oculto que se abre automáticamente cuando se

inicia excel. Este es un buen lugar para almacenar macros que se va a utilizar con

varios libros. Este archivo no existe hasta que se especifica como la ubicación de una

macro grabada. Si ha realizado cambios en el archivo, excel le pide que guarde al salir.

La macro grabada ha sido guardada en este libro y lo que se ha realizado es crear

código de vba de una manera sencilla, pero es necesario para activar el editor de visual

basic para ver dicho código:

1. Elija Programador ➪ Visual Basic (o presione Alt + F11). Aparecerá la ventana

del programa Visual Basic Editor, como se muestra en la Figura. Esta ventana es

muy personalizable, por lo que la ventana VBE puede parecer un poco diferente.

Page 132: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 131

Barra de Menú

Barra de Herramientas

Ventana de Código

Ventana de Propiedades

Ventana de Proyectos

2. En la ventana de VBE, busque la ventana llamada Proyecto. La ventana de

proyecto (también conocido como la ventana Explorador de proyectos) contiene

una lista de todos los libros y los complementos que están actualmente abiertos.

Cada proyecto está organizado como un árbol y se puede ampliar (para mostrar

más información).

3. Seleccione el proyecto que corresponde al libro en el que se grabó la macro. Si

no ha guardado el libro, el proyecto probablemente se llama VBAProject (Libro1).

4. Haga clic en el signo más (+) a la izquierda de la carpeta llamada módulos. El

árbol se expande para mostrar el módulo 1, que es el único módulo en el

proyecto.

5. Haga doble clic en Module1.

Page 133: Excel avanzado 2013

Microsoft Excel 2013 - Avanzado

Ing. Oscar Pérez/ Ing. Aracely Torres 132

La grabadora de macros es la más adecuada para, macros sencillas y directas. La

grabadora de macros no puede generar código para cualquiera de las siguientes tareas

(las cuales se describen más adelante):

La realización de cualquier tipo de bucle repetitivo

La realización de cualquier tipo de acciones condicionales (utilizando una

instrucción if-then)

Asignación de valores a las variables

Especificación de tipos de datos

Visualización de mensajes pop-up

La Capacidad limitada del grabador de macros sin duda no disminuye su importancia.