guia 8 excel avanzado 2 2013.pdf

Excel Avanzado Parte 2 Página 1 de 42

UNIVERSIDAD DE EL SALVADOR

FACULTAD DE INGENIERIA Y ARQUITECTURA

ESCUELA DE INGENIERIA DE SISTEMAS INFORMATICOS

MANEJO DE SOFTWARE PARA MICROCOMPUTADORAS

CICLO II-2013

GUIA DE LABORATORIO 8

Excel Avanzado – Parte 2

Objetivo: Aplicar sobre los datos almacenados en Excel las diferentes opciones que nos permitan trasformar los datos en información.

Macros

1. Crear un libro en Excel con el nombre de Macros. Para poder crear una macro debemos habilitar la Vista Programador en Excel. Para ello nos dirigimos a Archivo->Opciones. Al hacer esto nos aparecerá la siguiente pantalla:


Nos dirigimos hacia la opción Personalizar cinta de opciones, al hacer esto nos parecerá en la pantalla lo siguiente:

Y verificamos la opción Programador en la perta de Fichas Principales. Al hacer esto clic en aceptar y nos aparecerá en el menú una nueva ficha como se muestra en la figura:

2. Digite los siguientes datos:

Clic sobre esta

opción.

Esta es la nueva

pestaña que se nos

activa.


3. Nos dirigimos a la ficha Programador (Habilitada en el paso 1), Grabar nueva macro como se muestra en la siguiente figura:

4. Luego nos aparecerá la siguiente ventana:

5. Cambiaremos el nombre de la macro por Prueba, luego clic en Aceptar, después realizar los siguientes pasos:

a) Seleccionar tabla b) Abrir la paleta de bordes y colocar un borde exterior c) Abrir la paleta de color de fondo y seleccionar un color d) Abrir la paleta de color de texto y seleccionar un color e) Seleccionar la primera fila f) Pulsar un clic en el botón del formato negrita g) Pulsar un clic en el botón del formato cursiva h) Dar clic fuera de la tabla.

6. Para detener la grabación puede hacerlo de dos formas:

a) Dando clic sobre la barra inferior en el botón como lo muestra la siguiente figura:

Esta es la opción

para grabar la

macro


b) Ficha Programador/Detener Macro:

7. Para probar que la macro funciona, debemos dejar la tabla como estaba antes de aplicar los cambios del paso 5. Para eso Deshacer (Ctrl+Z) hasta dejar la tabla en su forma original.

8. Para correr la macro, debemos de ir a Ficha Programador/Macro como se muestra en la figura:

Al hacer esto nos aparece la siguiente ventana:


9. Nos aparecerán todas la macros que tengamos en nuestro libro, seleccionamos la macro que queremos ejecutar, y presionamos el botón Ejecutar al hacer esto se realizaran los cambios que hayamos hechos en las macro, quedara como la siguiente figura:

10. Cuando queramos guardar el archivos, nos aparecerá la siguiente ventana:

Presionamos que NO, y nos pedirá guardar el archivo como Libro de Excel habilitado para macros como lo muestra la siguiente figura:

Tipo de archivo con

el que debemos

guardar el archivo

con macros


Buscar Objetivo

1. Para este ejercicio, debemos abrir el archivo Tablas1 creado en los apartados

anteriores; y creamos una nueva hoja con el nombre de BuscarObjetivo.

2. En esa hoja debemos digitar la siguiente información:

NOTA: la celda E5=C5*D5 3. Para aplicar la opción Buscar Objetivo se procede a realizar los siguiente pasos:

a. Identificar la celda que contiene el cálculo (en este caso es la celda E5) y la

celda que se va a actualizar cuando se cambia el valor (en este caso la celda C5).

b. Luego nos dirigimos a la ficha Datos/Análisis Y si/Buscar objetivo …

c. Al dar clic sobre la opción nos aparecerá la siguiente ventana de dialogo:


c. Al haber decidido el valor a cambiar procedemos a llevar la ventana de dialogo; el valor será de $100.00 y en la opción Para cambiar la celda se colocara $C$5 como se muestra en la siguiente imagen.

d. Luego de que la ventana quede como la figura anterior procedemos a dar clic en Aceptar.

Al hacer esto nos aparecerá la siguiente ventana y los datos de la tabla cambiaran de la siguiente manera:

e. El valor de la celda Cantidad nos esta indicando que con $100.00 a un precio de $7.50 podemos generar 13.33 camisas.

f. Esta opción nos servirá para poder realizar proyecciones en base a un

valor que puede ser cambiante.

g. Si estamos de acuerdo con el valor damos clic en Aceptar, si deseamos cambiar el valor damos clic en Cancelar y repetimos el paso f.


Lista de Datos

1. En el archivo Tablas1 debemos abrir una nueva hoja y debemos lograrla

ListaDatos.

2. Ahora procedemos a digitar la siguiente información en la hoja:

3. Ahora debemos seleccionar los datos de la tabla y clic en Insertar/Tabla como se muestra en la figura


4. Al hacer esto nos aparecerá la siguiente ventana de dialogo:

5. Debemos colocar la opción de encabezado al hacer esto damos clic en Aceptar

6. Como podemos ver en la figura se agrega una nueva ficha llamada Herramientas de tabla aquí, debemos de seleccionar la opción Fila de totales deberá quedar de la siguiente manera:


7. Al hacer esto la tabla quedara de la siguiente manera:

8. En la fila de totales, se puede cambiar la operación que hace sobre todas las filas.

Para poder cambiar debemos dar clic sobre la celda y nos aparecerán las siguientes opciones:


9. Para agregar nuevos valores a la lista, debemos abrir la opción Formularios. Para ello debemos activar esta opción ya que Excel no la activa por defecto. SI EN SU MAQUINA YA LE APARECE FORMULARIO EN LA PESTAÑA DE DISEÑO NO HAGA EL SIGUIENTE PROCEDIMIENTO. a. Para Activar la opción Formulario debemos dirigirnos: Archivo/Opciones se

mostrara la siguiente ventana:

b. Clic sobre la opción: Personalizar cinta de opciones. Aparecerá en el centro de la ventana la opción Comandos disponibles en el combobox debe de seleccionar la opción Comandos que no están disponibles en la cinta de opciones. Al hacer esto deberá buscar la opción Formulario… como se muestra en la figura


c. En la parte de Personalizar la cinta de opciones deberá agregar a la ficha DATOS un nuevo grupo con el botón NUEVA GRUPO y deberá cambiarle en nombre por Formulario deberá quedar como se muestra en la siguiente figura:

d. Para poder agregar la opción, clic en el botón Agregar al haber seleccionado la

opción Formulario deberá quedarle de la siguiente manera:


e. Al terminar esto damos clic en Aceptar y nos aparecerá en la ficha datos el formulario como se muestra en la siguiente imagen:

10. Para agregar el formulario debemos colocarnos en una celda de la tabla y presionamos el botón Formulario, nos aparecerá la siguiente pantalla:

11. Para agregar un nuevo valor damos clic en Nuevo y deberemos llenar todos los

valores como se muestra en la siguiente imagen:


12. Al terminar de digitar la devolución solo damos enter o presionamos Nuevo y se agregara a la tabla el nuevo valor como se muestra en la siguiente figura:

13. Cerramos la ventana ListaDatos.

14. Para mostrar solo los elementos asociados al Almacén debemos aplicar lo aprendido en la sección Filtros deberá quedar como la siguiente figura:


15. En una nueva hoja llamada Subtotales debe de digitar la siguiente información:

16. Debe de seleccionar toda la información y dar clic en subtotales de la ficha datos:

17. Al darle clic en Subtotales le aparecerá la siguiente pantalla


18. Queremos mostrar los totales de las devoluciones por cada mes, para eso debemos seleccionar en Para cada cambio en: Mes y determinar la función que queremos en este caso será suma, las opciones deberán de quedar como se muestra a continuación:

19. Al dar clic en aceptar aparecerá los siguiente:


20. Si queremos mostrar los totales solo debemos dar clic en el icono - que le aparecere en el lado izquierdo de la tabla y quedara de la siguiente forma:

21. Creamos una grafica representando los totales de cada mes, el resultado sera el

siguiente:

22. Al desplegar las opciones de la tabla, la grafica se actualizara de la siguiente manera


Formularios en Visual Basic para Aplicaciones

La Barra de Formularios en Excel proporciona una serie de controles que es posible colocar y configurar en una Hoja de Cálculo de manera que:

Ejecuten una Macro almacenada.

Actualicen el contenido de una Celda que le fue asociada previamente al control.

Muestren un conjunto de valores que están definidos en un rango de celdas (aplicable para algunos controles), los cuales serán accesibles desde el control.

Los controles disponibles son los que se muestran activos en la ficha Programador opción Insertar como se muestra en la siguiente figura:

Controles de formulario: Primera Fila de izq. a der: Botón, Cuadro Combinado, Casilla de verificación, control de número, cuadro de lista, botón de opción. Segunda Fila de izq. a der: Cuadro de grupo, etiqueta, barra de desplazamiento, cuadro de texto, Cuadro combinado de lista – Editar, cuadro combinado desplegable –editar

Controles ActiveX:

Para agregar un control de Formularios, se debe:

Estar situado en la hoja donde se quiere agregar el control.

Nos dirigimos a la ficha Programado->Insertar y damos un clic en el control que se quiere agregar.

Dibujar el controlador en la hoja en que se agregara el control.

Realizar uno de los siguientes procedimientos según sea el caso: 1. Asignar una macro existente:

1.1 Si se agrega un botón, seleccionar la macro que debe ejecutar el botón en el cuadro Nombre de la macro.

1.2 Si se agrega un control que no sea un botón, hacer clic con el botón secundario en el control y, a continuación, elegir Asignar macro en el menú contextual. Seleccionar la macro que se desee.

2. Grabar o escribir una nueva macro:

2.1 Si la macro no existe, hacer clic en Grabar en el cuadro de diálogo Asignar macro para grabar una nueva macro o hacer clic en Nueva para abrir un módulo Visual Basic para Aplicaciones (VBA) de Microsoft en el que se puede escribir la macro.

Hacer clic con el botón secundario en el control, elegir Modificar texto y agregar el texto para el control. Cuando se haya terminado, hacer clic con el botón secundario en el control y, a continuación, hacer clic en Salir de Modificar texto.


Establecer las propiedades del control: hacer clic con el botón secundario en el control, elegir Formato de control y, a continuación, hacer clic en la ficha Control. Los rótulos y los botones no tienen propiedades. Las propiedades pueden ser diferentes para cada control.

Procederemos a realizar un Formulario que permita introducir dos números positivos menores a 100 para luego seleccionar la operación a realizar con ellos y al final presente el resultado en una hoja de Excel. Este ejercicio lo realizaremos en un nuevo libro llamado EjercicioFormulario. Paso 1: En la Hoja1 diseñar la siguiente tabla:

Paso 2: Diseñar el formulario Procedemos a acceder al formulario desde la Hoja1 en la Ficha Programado-> Visual Basic como se muestra en la siguiente figura:

Y nos aparecerá el editor de texto de Visual Basic para Aplicaciones que es una ventana como se muestra a continuación:


Paso 3: Procederemos a insertar un formulario, para ello podemos hacerlo de dos formas: 1. Desde la barra de herramientas en Insertar->User Form como lo muestra la siguiente figura:

2. Desde la barra de herramientas estándar en el Insertar User Form como se muestra en la siguiente figura

Al hacer cualquiera de las opciones anteriores aparecerán las siguientes pantallas en el área de trabajo:

Paso 4: Para crear el formulario solo arrastramos del cuadro de herramientas los controles que deseamos agregar a nuestro formulario. El formulario es el que se llama UserForm1. Si deseamos cambiar las propiedades de los controles nos dirigimos a Ver->Ventana de Propiedades o presionamos F4 y nos aparecerá una ventana al lado izquierdo como se muestra a continuación:


Se debe de dibujar un formulario como se muestra a continuación:

Los botones deben de quedar con las siguientes propiedades

No Control Caption Name Font

1 Label Resultado de Operación lbresoper Calibri, Negrita, 20

1 Label Número 1 lbnum1 Calibri, Normal, 12

1 Label Número 2 lbnum2 Calibri, Normal, 12

1 Label SUMA lbsuma Calibri, Normal, 12

1 Label RESTA lbresta Calibri, Normal, 12

1 Label MULTIPLICACIÓN lbmulti Calibri, Normal, 12

2 Caja de Texto (A) txnum1

2 Caja de Texto (B) txnum2

2 Caja de Texto (C) txsuma

2 Caja de Texto (D) txresta

2 Caja de Texto (E) txmulti

3 Botón INICIO Btninicio Calibri, Normal, 12

3 Botón ADICIONAR Btnadicionar Calibri, Normal, 12

3 Botón SALIR Btnsalir Calibri, Normal, 12

4 Barra desplazamiento scnum2

5 Botón de numero sbnum1


Paso 5: Digitar el código Dar doble clic al Botón de Comando INICIO y aparecerá la pantalla del Editor:

Ahora procedemos a escribir el siguiente código para cada uno de los botones como se muestra en el siguiente cuadro:

Control Evento Codigo

btninicio btninicio_Click()

sbnum1.Enabled = True scnum2.Enabled = True txnum1.Enabled = True txnum2.Enabled = True txsuma.Enabled = True txresta.Enabled = True txmulti.Enabled = True btnadicionar.Enabled = True

btnadicionar btnadicionar_Click()

Selection.EntireRow.Insert txnum1 = Empty txnum2 = Empty txsuma = Empty txresta = Empty txmulti = Empty

btnsalir btnsalir_Click() End



scnum2 scnum2_Change() scnum2.Max = 99 scnum2.Min = 1 txnum2.Value = scnum2.Value

sbnum1 sbnum1_Change() sbnum1.Max = 99 sbnum1.Min = 1 txnum1.Value = sbnum1.Value

txnum1 txnum1_Change() Range("A3").Select ActiveCell.FormulaR1C1 = txnum1

txnum2 txnum2_Change()

Range("B3").Select ActiveCell.FormulaR1C1 = txnum2 txsuma = Val(txnum1) + Val(txnum2) txresta = Val(txnum1) - Val(txnum2) txmulti = Val(txnum1) * Val(txnum2)

txsuma txsuma_Change() Range("C3").Select ActiveCell.FormulaR1C1 = txsuma

txresta txresta_Change() Range("D3").Select ActiveCell.FormulaR1C1 = txresta

txmulti txmulti_Change() Range("E3").Select ActiveCell.FormulaR1C1 = txmulti

Paso 6: probar que funcione. Para ello procedemos a presionar el boton Ejecutar

Sub/UserForm en la barra de herramienta o presionamos F5. Al hacer esto debera realizar la siguiente secuencia:

1


PRESIONAMOS ADICIONAR

2

3


PRESIONAMOS SALIR Con el siguiente codigo deshabilitaremos las opciones, hasta que presionemos Inicio; para ello seleccionamos en la barra superior del codigo las siguientes opciones:

Y digitamos el siguiente código: Private Sub UserForm_Initialize()

sbnum1.Enabled = False scnum2.Enabled = False txnum1.Enabled = False txnum2.Enabled = False txsuma.Enabled = False txresta.Enabled = False txmulti.Enabled = False btnadicionar.Enabled = False

End Sub

4


Procedemos a ejecutarlo nuevamente, al hacer esto aparecerá como se muestra a continuación:

Como se puede ver la opción ADICIONAR y los demás botones apareceren deshabilitados, y serán activados hasta que demos clic a inicio. Paso 7: Ahora debemos crear un botón desde la hoja para llamar al formulario. Dibujamos el botón según como se explico al inicio de este apartado. Al hacer esto aparecerá la siguiente ventana


Presionamos Nuevo al hacer esto debemos pegar el siguiente código: Sub CALCULAR() Load UserForm1 UserForm1.Show End Sub Guardamos los cambios y salimos del VB. Damos clic derecho sobre el botón y presionamos Asignar Macro como lo muestra la siguiente figura:

Seleccionamos la Macro Calcular que hemos creado anteriormente.


Para cambiar el nombre damos clic derecho-> Modificar Texto como se muestra en la siguiente figura:

Y quedara como se muestra a continuación:

Ejemplo 2

1. Renombre la Hoja2 por Alumnos-MSM115 y digite a siguiente información:

Carnet Nombre Apellido Promedio

TS96001 Stuart Michael Terrell Schroeder 8.53

LL97002 Chloe Gregory Lambert Long 8.82

DC98003 Martena Duffy Craig 6.13

BB99004 Fuller Raven Blanchard 0.68

DN00005 Kyra Deborah Donovan Noble 5.89

BG01006 Kendall Berger 8.78

DC02007 Desiree David Castillo 9.72

HO03008 Chaim Gil Hines Ortiz 2.03

VL04009 Dora Galvin Vinson 4.57

GD05010 Cailin Simon Gates 4.12

CP06011 Jerry Raven Cotton 1.30

WN07012 Lionel Destiny Wilkinson 3.99

SR08013 Keaton Velma Snider Randolph 1.30

HG09014 Troy Valentine Horne Giles 7.40

WE10015 Gareth Lucy Wells Erickson 6.28



ZS96016 Justina Hu Zamora Sharpe 1.91

HC97017 Wesley Fatima Hicks Castillo 9.86

FS98018 Suki Preston Fox Slater 5.04

GR99019 Ursula Hamish Greer Robinson 7.36

BT00020 Jacob Nehru Beck Terry 4.48

MM01021 Miriam Dane Morris Montoya 5.42

VR02022 Rana Burton Vaughan Robertson 6.08

FW03023 Knox Ori Fitzpatrick Willis 0.26

MM04024 Fritz Shelly Moses Mooney 6.44

BH05025 Austin Kylynn Boone Hopper 9.02

BE06026 Cole Tarik Barr 2.43

BP07027 Aubrey Wallace Bailey 3.13

PH08028 Kelsie Julian Peters 7.42

AW09029 Nehru Aidan Alvarado Woods 9.79

SM10030 Teegan Ferris Simpson Macdonald 1.68

LM96031 Kirestin Lara Mooney 1.06

WT97032 Solomon Wiley 4.09

BR98033 Priscilla Yolanda Berg 7.09

CG99034 Martena Forrest Cooke Grimes 4.43

RK00035 Jesse Aretha Rowland Kennedy 1.62

GM01036 Reed Helen Guerrero Morales 0.64

MS02037 Flynn Cheryl Mcclure Simon 3.47

OM03038 Mechelle Noelani

Oconnor Miranda 8.48

MS04039 Rhiannon Heather Mcguire Spence 0.13

KK05040 Emerald Kamal Knox Kent 7.83

WB06041 Emerald Yen Walters Bradshaw 8.81

SG07042 Zelenia Tallulah Stanton Gray 8.35

PH08043 Rudyard Murphy Perkins Hays 0.74

DD09044 Kim Dodson Day 2.62

BH10045 Simone Bonner Hull 1.32

HR96046 Dexter Phillip Head Rivers 1.12



DD97047 Eagan Asher Davidson Daniels 5.28

BM98048 Neil Cody Beach Martin 0.40

DF99049 Laurel Harriet Duke Flynn 5.15

SB00050 Raymond Shafira Sims Baldwin 6.67

BM01051 Kenneth Tanner Britt Merritt 0.77

BH02052 Zeus Tallulah Brock 3.41

PM03053 Giacomo Mona Pope Mays 9.65

BW04054 Tatiana Doris Buckley Weaver 1.40

DW05055 Moses David Walter 8.26

NS06056 Adria Nolan 6.62

MS07057 Penelope Herman Moss Sloan 1.18

TB08058 Abel Sharon Tucker Brown 1.49

VF09059 Lana Jamalia Vinson Fowler 4.63

FW10060 Sasha Claire Frederick Waters 4.10

OH96061 Calvin Odonnell 4.95

FC97062 Kalia Haviva Foley Cochran 1.28

MP98063 Anthony Velma Morton Patton 0.42

MM99064 Mia Noelle Montgomery Mays 5.83

FS00065 Sylvester Calista Farrell Sandoval 4.92

MM01066 Felicia Mueller 0.20

DK02067 Rina Davis Kaufman 3.32

LB03068 Reuben Lott Branch 8.00

BH04069 Reed Buckner Hoover 9.34

ML05070 Tatiana Mcbride 0.69

SN06071 Helen Shaw Nunez 5.86

SW07072 Elaine Zeph Stokes Walters 5.03

HP08073 Jemima Paul Hays Padilla 7.30

DB09074 Kuame Joseph Delacruz Bright 8.49

RL10075 Hu Tanya Ramirez 4.75

HC96076 Luke Imani Horne Collins 3.81

HW97077 Brooke Len Hall Wilcox 8.11

DJ98078 Louis Delilah Dunn Joyner 6.78

WF99079 Sybill Willow Walton Ferrell 0.34

CH00080 Celeste Serena Carson 0.01

SK01081 Pandora Ryder Shepard Kaufman 8.78



RM02082 Castor Bert Roberts Mendez 9.13

SB03083 Paloma Penelope Stone Burgess 4.93

WH04084 Caesar Oleg Wilkins 4.68

CB05085 Casey Eaton Chapman Bryant 0.67

HW06086 Russell Elaine Hendricks Wiley 9.77

LC07087 Germaine Noelle Lang Collins 8.09

FM08088 Stacy Graham Foster Morton 1.90

PJ09089 Leslie Hayden Pickett Jarvis 9.39

DH10090 Isabelle Lyle Decker 6.50

FM96091 Irene Leigh Floyd Mclaughlin 1.90

WE97092 Simone Iliana Wolf England 1.90

BC98093 Moana Macy Browning Conrad 1.50

CM99094 Eve Aristotle Christensen 0.79

BS00095 Callie Clarke Britt Stevenson 3.66

FS01096 Lucius Ferdinand Fisher Sheppard 8.31

FM02097 Erica Dustin Fulton Mckee 1.50

FY03098 Aidan Jana Franklin 7.71

GS04099 Nicholas Mira Gonzalez Sampson 1.53

WG05100 Carl Waller Gates 7.19

2. Habrá el Microsoft Visual Basic para Aplicaciones. Luego de clic en el botón UserForm (como lo muestra el paso 3 del apartado anterior) para insertar un nuevo formulario. Le aparecera el nuevo formulario como se muestra a continuación.


3. Diseñe el formulario, para que quede como el que se le muestra a continuación:

Con los nombres de botones como se muestra en la siguiente tabla

Control Caption Name Propiedades Extras

Label Digite el Carnet a buscar lbbuscar

Caja de Texto txbuscar

Label Nombre Alumno lbnombre

Caja de Texto txnombre Locked=true

Label Promedio lbpromedio

Caja de Texto txpromedio Locked=true

Botón Buscar btnbuscar

Botón Cerrar btncerrar

4. El codigo de los botones es el siguiente:


btnbuscar btnbuscar_Click()

Dim Res As Variant Res = Application.VLookup(txbuscar.Value, Range("B2:E101"), 4, False) If IsError(Res) = False Then txnombre.Value = Application.VLookup(txbuscar.Value, Range("B2:E101"), 2, False) + " " + Application.VLookup(txbuscar.Value, Range("B2:E101"), 3, False) txpromedio.Value = Application.VLookup(txbuscar.Value, Range("B2:E101"), 4, False) Else txnombre.Value = "NO SE ENCONTRO CARNET" txpromedio.Value = " " End If

btncerrar btncerrar_Click() End


5. Ahora procederemos a realizar el boton para llamarlo desde la Hoja. Realizamos los mismos pasos que el item anterior y le pegamos este codigo:

Sub abrir() Load UserForm2 UserForm2.Show End Sub

6. Quedara como se muestra a continuacion:

1

2

3


4


Algunas Formulas Avanzadas para Excel

CONSULTAV Si se tiene un conjunto de datos y se necesita extraer algún valor basándote sólo en un campo de referencia se puedes utilizar la función de CONSULTAV. La sintaxis es la siguiente:

=CONSULTARV(valor_buscado;matriz_buscar_en;indicar_columna;ordenado)

Valor_buscado: es el criterio a buscar en la primera columna de la matriz de tabla. Puede ser un valor o una referencia. Si el valor buscado es inferior al menor de los valores de la primera columna de la matriz devuelve al valor de error #N/A.

Matriz_buscar_en: Dos o más columnas de datos. Usa una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores en que busca valor_buscado. Es importante resaltar que al momento de seleccionar la matriz donde buscaremos la información, el valor referencial debe estar en la primera columna.

Indicador_columnas: Es el valor del 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.

Ordenado: Es el valor lógico que especifica si la función Excel 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.

Si es FALSO 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.

CONTAR.SI Función que se utiliza para contar un rango de valores que cumplen una restricción. Consta de dos parámetros:

1. Rango: es el rango cuyas celdas se desean contar. 2. Criterio: determina que celda o valor será contada o no.

SI Función que se utiliza para realizar operaciones lógicas. Contiene 3 parametros:

1. Pruea_lógica: es cualquier valor o expresión que pueda evaluarse como Verdadero o Falso


2. Valor_si_verdadero: es el valor que devolverá si la prueba lógica es VERDADERA

3. Valor_si_falso: es el valor que devolverá si la prueba lógica es FALSO Operadores Lógicos

Operador Símbolo

Igual =

Diferente <>

Mayor que >

Menor que <

Mayor o Igual que >=

Menor o Igual que <=

IZQUIERDA Función que se utiliza para extraer una cantidad determinada de caracteres iniciales de una cadena. Contiene los siguientes parámetros:

1. Texto: cadena a extraer el valor. 2. Núm_de_caracteres: es la cantidad de caracteres a extraer.

DERECHA Función que se utiliza para extraer una cantidad determinada de caracteres finales de una cadena. Contiene los siguientes parámetros:

1. Texto: cadena a extraer el valor. 2. Núm_de_caracteres: es la cantidad de caracteres a extraer.

CONCATENAR Función que se utiliza para unir varios elementos en una sola cadena. Los parametros son cada elemento separados por coma. (CONCATENAR(text1,texto2))


Ejemplo: Se tiene el listado de 100 alumnos de MSM115 del año 2011, junto con sus promedios finales y se quiere saber cuántos alumnos están Aprobados, cuántos Reprobados y cuántos alumnos se van a examen de suficiencia.

Paso 1: Copie el listado de alumnos de manejo de software del apartado anterior en una nueva hoja denominada ListadosAlumnos-MSM115 del libro Tabla1 que hemos utilizado en los ejercicios anteriores.

Paso 2: Definimos la tabla de referencia para la calificación como lo muestra la figura:

Paso 3: Definimos una nueva columna denominada Resultado y aquí colocaremos la formula ConsultaV para poder saber en qué clasificación se encuentra el promedio del alumno. Para esto, nos ubicamos en la celda F3 junto al primer alumno, luego

presionamos el botón de formula al hacer esto nos aparecerá el siguiente cuadro


Escribimos el nombre de la función ConsultaV dentro del cuadro que se muestra enmarcado con rojo. Luego presionamos IR y la ventana quedara de la siguiente manera:

Seleccionamos la opción CONSULTAV presionamos Aceptar. Al hacer esto aparecerá la siguiente pantalla:


Paso 4: Presionamos el icono del Valor_buscado y seleccionamos la celda E3 (que es el promedio del alumno) como se muestra la figura:

Paso 5: presionamos el icono de Matriz_buscar_en y realizamos la selección de la tabla de búsqueda como se muestra en la figura:

Le colocamos el signo de $ porque la referencia no debe de ser distinta para los alumnos.


Paso 6: En el Indicador_columnas colocamos el numero 3 porque deseamos mostrar la tercera columna de la Matriz_buscar_en que es la columna donde se encuentra la clasificación. Quedará como se muestra la figura:

Paso 7: en el campo Ordenado colocamos VERDADERO ya que no necesitamos que la coincidencia sea exacta sino que aproximada. Y quedara como se muestra en la figura:

Paso 8: Presionamos aceptar y la formula quedara de la siguiente manera:


Paso 9: Deberemos copiar lo que se encuentra en la Celda F3 a todas las demás celdas en que se encuentren los alumnos registrados.

Al hacer esto la tabla quedara como se muestra a continuación:

Paso 10: procederemos a contar cuantos alumnos son reprobados, aprobados y cuantos van a examen de suficiencia. Para esto debemos Realizamos la siguiente tabla:


En la celda I7 presionaremos el botón de agregar formula y deberemos buscar la función CONTAR.SI, deberá quedar como se muestra en la siguiente ventana:

La tabla quedara como se muestra a continuación:

Al finalizar comprima su archivo y súbalo en la opción:

guia 8 excel avanzado 2 2013.pdf

Documents