• Author / Uploaded
• Idelso Melgarejo Quispe

Citation preview

Expertos en Ofimática Microsoft Excel Intermedio

San Juan de Lurigancho: Av. Las Flores de Primavera 969 (Paradero 7 de las Flores)  376-2835 Miraflores: Av. Arequipa 5095 Stand 2118 (CompuPalace)  243-6591 San Juan de Miraflores: Av. San Juan 1066 (entre Banco Financiero y Caja Santa Rosa)  276-9077 Los Olivos: Av. Alfredo Mendiola 3571 (Frente a Mega Plaza) Psje. Ayarza 180 (Recta Curacao) – Aguajal  523-2042 Santa Anita: Jr. Los Pinos 514 Urb. Los Ficus – Parque Ecológico (IEP Alfonso Ugarte)  362-6720 Cajamarca: Jr. Belén 740 (ExSunat)  (076) 36-7582

www.computron.edu.pe

1

Expertos en Ofimática Microsoft Excel Intermedio

Presentación El Instituto Superior Tecnológico COMPUTRON te da la más cordial bienvenida e invita a unirte a esta aventura y gratificante experiencia de formar parte de nuestros líderes profesionales exitosos y triunfadores; así mismo agradecemos su participación y elección hacia nosotros. Esperamos que este nuevo voto de confianza se siga manteniendo así como se viene haciendo desde nuestros inicios. En COMPUTRON nos esmeramos día a día con el único propósito de mejorar nuestro servicio y este sea cada vez mejor. Contamos con equipos sofisticados tecnológicamente, además con un staff de profesionales de suma experiencia y gran conocimiento a la vanguardia, quienes vienen formando nuestros profesionales de éxito. Hoy en día la informática juega un rol preponderante en nuestra vida diaria, por ello COMPUTRON contribuye con la capacitación técnica en el área de la computación e informática y programas a fines, desenvolviéndonos con dedicación, eficiencia y responsabilidad; dichos programas están dirigidos a niños, jóvenes, adultos, profesionales, trabajadores y público en general que tengan el empeño de mantenerse actualizados en el desarrollo tecnológico; a la vez fomentamos al alumnado emprender su propia empresa y liderar en el mercado laboral. “Si no persigues lo que quieres, nunca lo tendrás. Si no vas hacia delante, siempre estarás en el mismo lugar. Las oportunidades no ocurren, las creas tú”. JBT.

Autor: Jhony Bacon Terrones  [email protected] Gerente General: Richar Bacon Terrones www.computron.edu.pe

2

Expertos en Ofimática Microsoft Excel Intermedio

Contenido del Curso Capítulo I Excel Intermedio 2013 1. 2. 3. 4.

Función si Anidada. Operadores Lógicos (conectores). Operador lógico Y. Operador lógico O.

Capítulo II Funciones de búsqueda 1. 2. 3. 4. 5.

Definición y uso de la función Buscarv. Definición y uso de la función Buscarh. Uso de función Buscar. Manejo de la función Coincidir. Aplicación de función índice.

Capítulo III Creación de gráficos estadísticos 1. 2. 3. 4. 5. 6. 7. 8. 9.

Concepto de gráficos. Tipos de gráficos. Gráficos variados. Elaborar gráficos recomendados. Generar un gráfico personalizado. Configuración del gráfico. Uso de nuevas opciones del gráfico. Aplicación de colores al gráfico. Agregar objetos sobre el grafico.

Capítulo IV Funciones Texto 1. 2. 3. 4. 5. 6.

Función izquierda. Función derecha. Función extrae. Función encontrar. Función largo. Función Texto.

www.computron.edu.pe

3

Expertos en Ofimática Microsoft Excel Intermedio 7. Función reemplazar. 8. Función concatenar.

Capítulo IV Funciones Fecha y hora 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.

Función hoy. Función año. Función ahora. Función fecha. Función fechanumero. Función fin.mes. Función dias360. Función sifecha. Función días.lab. Función dia.lab. Función dia. Función diasem. Función hora. Función horanumero. Función mes. Función minuto. Función nshora. Función segundo.

Capítulo VI Filtro básicos y avanzados 1. 2. 3. 4. 5. 6. 7. 8. 9.

Definición de filtrado de datos. Definición de campos y registros de una tabla. Como usar los filtro. Filtrar datos en varias columnas. Como anular un filtro. Filtrar buscando valores. Filtrar texto. Creación de filtros avanzados. Uso de criterios en filtros avanzados.

www.computron.edu.pe

4

Expertos en Ofimática Microsoft Excel Intermedio

Uso de la función Si Anidada y operadores lógico Y - O

EXCEL

www.computron.edu.pe

5

Expertos en Ofimática Microsoft Excel Intermedio

función SI anidada Una función SI anidada es cuando una segunda función SI se coloca dentro de la primera de manera que pruebe alguna condición adicional. Las funciones SI anidadas aumentan la flexibilidad de la función al ampliar el número de posibles resultados a probar. Supongamos que necesitamos hacer una equivalencia de una columna de letras a números de la siguiente manera: A = 5 y B = 10. Si la celda es igual a “A” entonces el valor será 5, pero si la celda es “B” entonces el valor será 10. Esto lo podemos resolver con la siguiente función SI anidada: =SI(celda = "A", 5, SI(celda = "B", 10)) El resultado será el siguiente:

¿Qué pasaría si ahora en lugar de dos letras tenemos tres? Es decir, que si encontramos una “C” debemos colocar el valor 20. La función SI anidada será la siguiente: =SI(celda = "A", 5, SI(celda = "B", 10, SI(celda = "C", 20))) Una función SI anidada puede ayudarte a encontrar una buena solución a tu problema pero no debes hacer un uso excesivo de esta funcionalidad. Excel 2010 soporta hasta 64 funciones SI anidadas, pero seguramente no llegarás ni a la mitad de esa cantidad antes de que se dificulte entender la lógica empleada en todas esas funciones. Mientras tanto disfruta de tus funciones SI anidadas.

www.computron.edu.pe

6

Expertos en Ofimática Microsoft Excel Intermedio Ejemplo 1:

Para el Valor viaje tenga en cuenta: Si los viajes son inferiores o iguales a 50, el valor será 5500; Si los viajes son inferiores o iguales a 90, el valor será 7500; Si los viajes son inferiores o iguales a 135, el valor será 9500; Si los viajes son superiores a 135, el valor será 10500.

Ejemplo 2:

Para la definitiva, tenga en cuenta: Si la nota es inferior a 2.0, debe aparecer pierde; si la nota está entre 2.0 y 2.9, debe aparecer habilita; si la nota es superior o igual a 3.0, debe aparecer gana.

www.computron.edu.pe

7

Expertos en Ofimática Microsoft Excel Intermedio



Ejemplo 3:

Función Si Con Conector Lógico Esta función está compuesta por dos conectores llamados: Conector Y, Conector O. Sintaxis: =SI(Conector Lógico (Valor Logico1, Valor Logico2,…), Valor si Verdadero, Valor si Falso

Para dar una respuesta en esta función se basa en las tablas de la verdad que son: Tabla de la verdad Y: Para seguir el camino verdadero se deben de cumplir todos los valores logicos; de lo contrario tomara el camino falso. Tabla del conector logico Y Conector Primera Y Verdadero Y Falso Y Verdadero Y Falso

Segunda Verdadero Verdadero Falso Falso

Respuesta Verdadero Falso Falso Falso

Tabla de la Verdad O: Para seguir el camino verdadero se debe de cumplir con cualquier valor logico; si no se cumple ninguno el camino será falso. www.computron.edu.pe

8

Expertos en Ofimática Microsoft Excel Intermedio

Tabla del conector logico O Conector Primera Y Verdadero Y Falso Y Verdadero Y Falso

Segunda Falso Verdadero Verdadero Falsa

Respuesta Verdadero Verdadero Falso Falso

Uso del conector Y

Ejemplo 1: De la siguiente tabla necesitamos que las personas mayores de 18 años y de sexo masculino Apliquen, de lo contrario No Aplican. 1. Entra a Excel y realizar la siguiente tabla.

2. En la celda E2. Realizaremos la Funcion SI con Conector Logico Nota: Si miramos el ejercicio nos piden que las personas mayores de 18 años y de sexo masculino Aplican, debemos de utilizar el conector logico Y, por que obligatoriamente se deben de cumplir las dos condiciones para dar un camino verdadero que en este caso seria APLICA. de lo contrario seria el camino falso NO APLICA.

www.computron.edu.pe

9

Expertos en Ofimática Microsoft Excel Intermedio 3. Coloque el signo igual la palabra SI abra parentesis, conector logico Y abra perentesis, en la celda E2 Observe la siguinete Imagen. =SI(Y(

4. Colocaremos el primer valor logico que en este ejemplo es la edad Si la edad es mayor a 18 entonces. observe la imagen =SI(Y(C2>=18

5. Colocaremos el segundo valor logico que en este ejemplo es el Sexo. Si sexo es igual a M entonces. Observe la imagen. =SI(Y(C2>=18;D2="M"

www.computron.edu.pe

10

Expertos en Ofimática Microsoft Excel Intermedio

6. Como no hay mas condiciones cerramos parentesis. Observe la imagen =SI(Y(C2>=18;D2="M")

7. Colocamos ; observe la imagen.

8. Sigue el Camino Verdadero. Para el ejemplo es "SI APLICA" =SI(Y(C2>=18;D2="M");"SI APLICA" Observe la imagen.

www.computron.edu.pe

11

Expertos en Ofimática Microsoft Excel Intermedio

9. Colocar ; Observe la imagen.

10. Sigue el Camino falso. que para el Ejemplo propuesto es: "NO APLICA" Observe la imagen =SI(Y(C2>=18;D2="M");"SI APLICA";"NO APLICA"

11. Cerra parentesis Observe la Imagen.

www.computron.edu.pe

12

Expertos en Ofimática Microsoft Excel Intermedio

12. Presionar Enter para finalizar la funcion. Observe la imagen. Si detallamos el resultado da NO APLICA por que no se cumple la condicion de Edad.

13. Copiar la formula para el resto del ejercicio Observe la imagen

Ejemplo 2: Una agencia de modelaje realizó una convocatoria para contratar modelos para un comercial. Para ser Contratado, debe cumplir las siguientes condiciones, de lo contrario será Descartado. Debe ser hombre y con una edad inferior o igual a 20 y con una estatura superior o igual a 1,75 y con un peso inferior o igual a 70 y los ojos deben ser color azul.

www.computron.edu.pe

13

Expertos en Ofimática Microsoft Excel Intermedio

 Función SI Anidado: Permite anidar 64 funciones SI y cada SI con 255 condiciones, para un total de 16.320 condiciones. =SI(Y(Condición1;Condición2...Condición255);Verdadero;SI(Y(Condición1; Condición2...Condición255);Verdadero;SI(Y(Condición1;Condición2...Condi ción255)...Verdadero;Falso))) Ejemplo 3: Para la Observación tenga en cuenta: Si la nota es mayor o igual a 0 y menor que 2,0, Pierde; Si la nota es mayor o igual que 2 y menor o igual que 2,9, habilita; Si la nota es mayor o igual que 3 y menor o igual que 5,0, gana; Si la nota es superior que 5,0 será un error de digitación.

www.computron.edu.pe

14

Expertos en Ofimática Microsoft Excel Intermedio Ejemplo 4:

IES: se aplica solo si no presento formulario 509 y además el monto excede de 1100. Se aplica al exceso.

Ejemplo 5:

www.computron.edu.pe

15

Expertos en Ofimática Microsoft Excel Intermedio Ejemplo 6:

Uso del conector O

Es un complemento de la Función SI. Permite evaluar varias condiciones, Se debe cumplir por lo menos 1 de las condiciones para que devuelva la respuesta por el verdadero, si no se cumple ninguna de las condiciones devuelve la respuesta por el falso. Función SI: Permite evaluar 255 Condiciones. =SI(O(Condición1; Condición2; Condición3; Condición4...Condición255); Verdadero; Falso) Ejemplo 1: Para el modelo, tenga en cuenta: Si la marca es Mazda o la marca es Toyota o la marca es Corsa, el modelo será 2003; de lo contrario el modelo será 2005.

www.computron.edu.pe

16

Expertos en Ofimática Microsoft Excel Intermedio

Función SI Anidado: Permite anidar 64 funciones SI y cada SI con 255 condiciones, para un total de 16.320 condiciones. =SI(O(Condición1;Condición2...Condición255);Verdadero;SI(O(Condición1; Condición2...Condición255);Verdadero;SI(O(Condición1;Condición2...Condi ción255)...Verdadero;Falso))). Ejemplo 2:

ASIGNACION: Equivale a 90 solo a los empleados que no tengan Hijos o que tengan 2 hijos a los demás 0.

Ejemplo 3: Para el Valor tenga en cuenta: Si el Destino es Cartagena o Santa Marta o el Hotel es Sol y Mar, el valor será 850.000; si el hotel es Luna Park o el Transporte es Aéreo, el valor será 620.000.

www.computron.edu.pe

17

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

18

Expertos en Ofimática Microsoft Excel Intermedio

Uso de la función Búsqueda

EXCEL

www.computron.edu.pe

19

Expertos en Ofimática Microsoft Excel Intermedio Las funciones de búsqueda y referencia son aquellas funciones que a partir de unos argumentos nos ayudan a localizar valores o datos dentro de rangos Excel. En múltiples ocasiones disponemos de tablas con datos e informaciones (listas de precios, tablas de salarios, de impuestos, grandes relaciones de elementos a modo de bases de datos, en las que buscar ciertos valores, mediante procedimientos que van desde los más sencillos y directos hasta otras más "rebuscados" e indirectos que nos pueden dar solución a necesidades concretas y de más difícil cálculo de no conocer estas posibilidades. Para ello, Excel dispone de una serie de herramientas y funciones que vamos a conocer y practicar mediante varios ejemplos prácticos.

BUSCARV. Esta función es muy importante en Excel. Es una forma avanzada de buscar valores en un rango de celdas ya que sustituye en muchas ocasiones a las posibilidades de trabajo de la función BUSCAR. Busca un valor en la primera columna de una tabla (referida mediante su rango o nombre), y devuelve el valor de la celda situada en la misma fila y en otra columna de la tabla cuyo número de columna dentro de la tabla se especifique. La tabla en la que buscar deberá tener un mínimo de 2 columnas y la primera la columna "de ataque" deberá estar ordenada ascendentemente por sus valores. Ejemplos de aplicación pueden ser que conocido un código de artículo deseemos saber su precio de compra (encontrándose este en la columna 12 de la tabla de artículos), dado un número de teléfono averiguar el nombre del contacto en una tabla de guía de teléfonos... En ocasiones tendremos que anidar una función BUSCARV dentro de otra función BUSCARV con lo que los planteamientos se endurecen a la vez que se hacen más potentes. =BUSCARV(valor buscado; Matriz; nº de columna; Ordenador) Los argumentos que necesita la función son:

www.computron.edu.pe

20

Expertos en Ofimática Microsoft Excel Intermedio El valor buscado: valor del dato o referencia a celda que contiene el dato con el que se desea extraer la información de la columna izquierda de la tabla en la que se quiere buscar. Matriz: rango de celdas que contienen todos los datos de la tabla (rango de la tabla). Si en la primera fila de la tabla existen unos títulos o rótulos, éstos, quedarán excluidos del rango de la tabla especificado en este segundo argumento. Si al rango le ha sido asignado previamente un nombre, se puede especificar como este argumento, dicho nombre. Indicador de columna: número de la columna, en relación a la tabla, en la que se encuentra el dato que se quiere extraer (si la tabla tiene 3 columnas son 1, 2 o bien 3). Se especifica el número, no la letra de la columna A, B, C... Ordenado (parámetro opcional pero interesante): permite especificar 'falso' o 'verdadero' para saber si el valor existe o no de forma exacta en la tabla. 1. Falso: Si no localiza el dato buscado por igual, es decir por el valor exacto, muestra #N/A como error. Este parámetro se utiliza para búsquedas por igual, es decir coincidentes plenamente (que el valor buscado exista en la columna "de ataque" de la tabla generalmente la primera) y no exige tener la tabla ordenada por los valores de la columna por la que se desea buscar. 2. Verdadero: Es la opción predeterminada, de no ser especificada. Si no localiza el dato buscado aporta el valor correspondiente al dato más parecido al mismo. El que corresponde al mayor valor menor. Ejemplo 1: Supongamos una tabla de artículos es muy frecuente que los elementos de una tabla se encuentre codificados, en la que cada elemento está identificado mediante un código, que en la tabla aparece en su primera columna. La función buscarV, buscará en una tabla de desarrollo Vertical (por eso se llama buscarV, ya que si la tabla estuviera dispuesta en horizontal, la función utilizada sería buscarH; cuya sintaxis y argumentos son los mismos). En el siguiente ejemplo, el valor buscado es el que está en la celda que contiene el código de marca que deseamos buscar en la tabla: A5. La matriz, es el rango que abarca la tabla (sin fila de títulos), en este caso: F3:I7, y el indicador de columna es el número de columna que contiene la información buscada. Para la www.computron.edu.pe

21

Expertos en Ofimática Microsoft Excel Intermedio marca es la 2, para el precio es la 3, para el índice de nicotina será la columna 4.

Podríamos comprobar, como si en la celda en la que se introduce el código, la A5, introducimos otro código válido de la tabla, aparecen a su derecha los datos que corresponden en la tabla con ese nuevo código. Ejemplo 2: En la siguiente imagen utilizamos la función BUSCARV para encontrar el nombre del cliente 203, podemos darnos cuenta que devolvió como resultado “FLETES DE OCCIDENTE SA DE CV”.

Para encontrar el Saldo del cliente 203, lo único que cambia es que ahora tiene que buscar la columna 3.

www.computron.edu.pe

22

Expertos en Ofimática Microsoft Excel Intermedio

BUSCARH. La función BUSCARH en Excel encuentra un valor en una tabla o matriz, tomando como punto de búsqueda la primera fila previamente ordenada, y en base a esa columna en donde encontró el valor puedes indicarle la fila que necesitas te dé como resultado. Puedo decir que realiza una búsqueda de forma horizontal a lo largo de la tabla o matriz. Es recomendable que utilices BUSCARH en Excel cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y necesitas encontrar información que se encuentre dentro de un número especificado de filas. También cuando los valores de comparación se encuentren en una columna a la izquierda o de los datos que quieres encontrar. =BUSCARH(valor buscado; matriz; nº de Fila; ordenado) Los argumentos que necesita la función son: Valor buscado: Es un argumento obligatorio y simboliza el valor que quieres buscar en la primera fila de la tabla. El argumento valor buscado puede ser un valor, una referencia o una cadena de texto. Matriz: Es un argumento obligatorio y representa una tabla de información en la que se buscan los datos. Puedes utilizar una referencia a un rango o el nombre de un rango o los valores de la primera fila del argumento matriz pueden ser texto, números o valores lógicos. www.computron.edu.pe

23

Expertos en Ofimática Microsoft Excel Intermedio Indicador filas: Es un argumento obligatorio y es el número de fila en matriz desde el cual debe devolverse el valor coincidente. Ordenado: Es un argumento opcional y representa un valor lógico que especifica si BUSCARH debe localizar una coincidencia exacta o aproximada. 1. Falso: Si no localiza el dato buscado por igual, es decir por el valor exacto, muestra #N/A como error. Este parámetro se utiliza para búsquedas por igual, es decir coincidentes plenamente (que el valor buscado exista en la columna "de ataque" de la tabla generalmente la primera) y no exige tener la tabla ordenada por los valores de la columna por la que se desea buscar. 2. Verdadero: Es la opción predeterminada, de no ser especificada. Si no localiza el dato buscado aporta el valor correspondiente al dato más parecido al mismo. El que corresponde al mayor valor menor. Ejemplo 1: Se desea capturar usando un código de Artículo la etiqueta y el precio.

Ejemplo 2: www.computron.edu.pe

24

Expertos en Ofimática Microsoft Excel Intermedio En este ejemplo vamos a basarnos en la siguiente tabla para comprender mejor cómo te puede ayudar la función BUSCARH en Excel.

BUSCAR La función BUSCAR en Excel nos permite buscar un valor dentro de un rango de celdas y como resultado nos devolverá el valor correspondiente del rango de resultados que especifiquemos. La función BUSCAR se puede utilizar en forma vectorial o en forma matricial. Forma vectorial de la función BUSCAR. Comenzaré explicando la forma vectorial de la función BUSCAR. Bajo esta forma podemos buscar un valor en un rango de celdas el cual debe ser una sola columna o una sola fila. La sintaxis para realizar la búsqueda es la siguiente: =BUSCAR(valor buscado; matriz de comparación; matriz resultado) Valor buscado: Es obligatorio y representa el valor que quieres buscar con la función BUSCAR en el primer vector. El valor buscado puede ser un número, texto, un valor lógico o un nombre de referencia. Matriz de comparación: Es obligatorio y es el rango de sólo una fila o una columna. Los valores del matriz de comparación pueden ser texto, números o valores lógicos. www.computron.edu.pe

25

Expertos en Ofimática Microsoft Excel Intermedio Es muy IMPORTANTE que los valores del matriz de comparación se coloquen en orden ascendente: …, -2, -1, 0, 1, 2,…, A-Z, FALSO, VERDADERO, pues de lo contrario, la función BUSCAR puede devolver un valor incorrecto.

El texto en mayúsculas y en minúsculas es equivalente. Matriz resultado: Es un argumento opcional y representa el rango que solamente incluye una fila o una columna. El argumento matriz resultado debe tener el mismo tamaño que matriz de comparación. Ejemplo1: Tenemos una tabla de datos relacionada a objetos que se encuentran en la oficina en donde queremos obtener el objeto asociado a un código en específico, pues en efecto la función que necesitamos para este caso es BUSCAR de forma vectorial, veamos mejor de forma visual la aplicación de este función para el ejemplo antes mencionado.

Ejemplo 2: Dada una tabla con cuatro columnas en las que se presentan los días del mes en la primera columna, en la segunda columna la temperatura máxima de cada día, en la tercera columna la temperatura máxima y en la cuarta las horas de sol, dado un día, calcular las horas de sol que hubo ese día. Esta respuesta se puede obtener mediante la función BUSCAR. www.computron.edu.pe

26

Expertos en Ofimática Microsoft Excel Intermedio

COINCIDIR La función COINCIDIR en Excel nos ayuda a localizar un elemento dentro de un rango de celdas y nos devuelve su posición. En otras palabras, la función COINCIDIR nos ayuda a obtener el número de fila que ocupa el elemento buscado. =COINCIDIR(valor buscado, matriz buscada, tipo de coincidencia) Valor buscado: Es un argumento obligatorio y representa el valor que necesitas buscar dentro de la lista (matriz) de valores. 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: Es un argumento obligatorio y es el rango de celdas donde vas a realizar la búsqueda del argumento valor buscado. Tipo de coincidencia: Es un argumento opcional y es un número entre -1, 0 ó 1. Este argumento determina cómo va a realizar la coincidencia del valor buscando por parte de Excel. El valor por default del argumento es 1 y se basa en la siguiente tabla:

Tipo de Comportamiento coincidencia www.computron.edu.pe

27

Expertos en Ofimática Microsoft Excel Intermedio COINCIDIR encuentra el mayor valor que es menor o igual que el valor buscado. Los valores del argumento matriz buscada se 1 u omitido deben colocar en orden ascendente, por ejemplo: …-2, -1, 0, 1, 2,…, A-Z, FALSO, VERDADERO. COINCIDIR encuentra el primer valor que es exactamente igual 0 que el valor buscado. Los valores del argumento matriz buscada pueden estar en cualquier orden. COINCIDIR encuentra el menor valor que es mayor o igual que el valor buscado. Los valores del argumento matriz buscada se -1 deben colocar en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, …2, 1, 0, -1, -2, …, etc. Ejemplo 1: Lista desordenada

Ejemplo 2: Capturar la posición según el nombre y apellido

INDICE www.computron.edu.pe

28

Expertos en Ofimática Microsoft Excel Intermedio La función INDICE en Excel nos ayuda a obtener el valor de una celda dentro de una matriz especificando el número de fila y columna. Esta función tiene dos formas de uso: de forma matricial y de forma de referencia. Forma matricial de la función INDICE. En su forma matricial, la función INDICE en Excel nos ayuda a obtener el valor de la celda que se encuentra justamente en el cruce de la fila y de la columna que hayamos especificado. La sintaxis de la función INDICE es la siguiente: =INDICE(matriz, núm. fila, núm. columna) Matriz: 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: Selecciona la fila de la matriz desde la cual devolverá un valor. Si omite núm. fila, se necesita el argumento núm. columna. Núm. columna: Selecciona la columna de la matriz desde la cual devolverá un valor. Si omite núm. columna, se necesita el argumento núm. fila. Ejemplo 1:

Aquí podemos identificar el rango B1:E5 (recuadrado en rojo) con una matriz de 4 filas por 4 columnas donde estas se numeran, desde arriba y a la izquierda empezando por 1, en forma creciente, con lo que por ejemplo el numero 567 correspondería a la intersección de la fila 3 con la columna 2, el número 23 con la intersección de la fila 1 con la columna 4 etc. Esto es lo que hace la función INDICE, devolver el número que está en la celda que es la intersección de una www.computron.edu.pe

29

Expertos en Ofimática Microsoft Excel Intermedio fila con una columna, aclaro que en este caso en la celda puede haber un número, una cadena de caracteres, un mensaje de error, una formula etc. Dicho esto se entenderá mejor la sintaxis de la función INDICE

Ejemplo 2:

Ejemplo 3:

www.computron.edu.pe

30

Expertos en Ofimática Microsoft Excel Intermedio

Creación de gráficos estadísticos

EXCEL

Creación de Grafico www.computron.edu.pe

31

Expertos en Ofimática Microsoft Excel Intermedio Un gráfico es la representación de datos, generalmente numéricos, mediante líneas, superficies o símbolos, para ver la relación que esos datos guardan entre sí y facilitar su interpretación. Un gráfico también puede ser un conjunto de puntos, que se plasman en coordenadas cartesianas, y sirven para analizar el comportamiento de un proceso, o un conjunto de elementos. La utilización de gráficos hace más sencilla e inmediata la interpretación de los datos. A menudo un gráfico nos dice mucho más que una serie de datos clasificados por filas y columnas. Tipos de gráficos 

Gráficos de columnas



Gráficos de líneas

Los datos que se organizan en columnas o filas en una hoja de cálculo se pueden trazar en un gráfico de columnas. Un gráfico de columnas muestra normalmente categorías a lo largo del eje horizontal (categoría) y valores a lo largo del eje (valor) vertical, como se muestra en este gráfico: Se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo en un gráfico de líneas. En un gráfico de líneas, los datos de categoría se distribuyen de forma uniforme a lo largo del eje horizontal y todos los datos de valores se distribuyen de forma uniforme en el eje vertical. Los gráficos lineales pueden mostrar datos continuos con el tiempo en un eje de escala regular y por tanto son idóneos para mostrar tendencias en datos a intervalos iguales, como meses, trimestres o ejercicios fiscales. www.computron.edu.pe

32

Expertos en Ofimática Microsoft Excel Intermedio 

Gráficos circulares



Gráficos de anillos



Gráficos barras

Los datos que se organizan en una columna o fila de una hoja de cálculo se pueden trazar en un gráfico circular. Los gráficos circulares muestran el tamaño de los elementos de una serie de datos, en proporción a la suma de los elementos. Los puntos de datos de un gráfico circular se muestran como porcentaje de todo el gráfico circular. En un gráfico de anillos se pueden representar datos organizados únicamente en columnas o en filas de una hoja de cálculo. Al igual que un gráfico circular, un gráfico de anillos muestra la relación de las partes con un todo pero puede contener más de una serie de datos. de

En un gráfico de barras se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo. Este tipo de gráfico muestra comparaciones entre elementos individuales. En un gráfico de barras, las categorías se organizan típicamente a lo largo del eje vertical y los valores a lo largo del eje horizontal. 

Gráficos de área

www.computron.edu.pe

33

Expertos en Ofimática Microsoft Excel Intermedio En un gráfico de área se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo. Los gráficos de área se pueden usar para trazar el cambio con el tiempo y para llamar la atención en el valor total en una tendencia. Al mostrar la suma de los valores trazados, un gráfico de área también muestra la relación de las partes con un todo. 

Gráficos de tipo XY (dispersión) y gráficos de burbujas

En un gráfico de tipo XY (dispersión) se pueden trazar datos organizados en columnas y filas de una hoja de cálculo. Coloque los valores X en una fila o columna y, a continuación, introduzca los valores y correspondientes en las filas o columnas adyacentes. Un gráfico de dispersión tiene dos ejes de valores, un eje horizontal (X) y otro en el eje vertical (Y). Combina estos valores en puntos de datos únicos y los muestra en intervalos irregulares o agrupaciones. Los gráficos de dispersión se utilizan por lo general para mostrar y comparar valores numéricos, como datos científicos, estadísticos y de ingeniería.



Gráficos de cotizaciones

www.computron.edu.pe

34

Expertos en Ofimática Microsoft Excel Intermedio En un gráfico de cotizaciones se pueden trazar datos que se organizan en columnas o filas en un orden específico en una hoja de cálculo. Como el nombre indica, los gráficos de cotizaciones pueden mostrar las fluctuaciones de los precios de las acciones. Sin embargo, este gráfico también se puede utilizar con datos científicos. 

Gráficos de superficie



Gráficos radiales



Gráficos combinados

En un gráfico de superficie se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo. Este gráfico es útil cuando busca combinaciones óptimas entre dos conjuntos de datos. Como en un mapa topográfico, los colores y las tramas indican áreas que están en el mismo rango de valores. Puede crear un gráfico de superficie cuando las categorías y series de datos son valores numéricos. En un gráfico radial se pueden representar datos organizados únicamente en columnas o en filas de una hoja de cálculo. Los gráficos radiales comparan los valores agregados de varias series de datos .

www.computron.edu.pe

35

Expertos en Ofimática Microsoft Excel Intermedio Los datos que se organizan en columnas y filas se pueden trazar en un gráfico combinado. Los gráficos combinados combinan dos tipos de gráficos para que los datos sean sencillos de comprender, especialmente cuando los datos varían en gran medida. Mostrado en un eje secundario, este gráfico es incluso más sencillo de leer. En este ejemplo, hemos usado un gráfico de columnas para mostrar el número de hogares vendidos entre enero y junio y, a continuación, hemos usado un gráfico de líneas para facilitar la identificación por parte de los lectores del precio promedio de venta al mes. Excel 2013: más "democrático" en cuanto a gráficos Muchísimas novedades trae consigo Excel 2013 en cuanto a gráficos se refiere. No podemos en un sólo artículo reseñar todo lo nuevo, pero podríamos resumirlo en una frase: La compañía de Redmond ha hecho asequible a todo el mundo la posibilidad de hacer gráficos avanzados, sin necesidad de introducirse en angostos cuadros de diálogo. Excel es un poco más "democrático". En este post resaltaremos las 5 novedades más relevantes en el apartado de gráficos: • Se rediseñan las fichas contextuales que se visualizaban cuando tenemos un gráfico seleccionado, desapareciendo la ficha "Presentación" y apareciendo ahora 3 botones flotantes junto al gráfico seleccionado. Las opciones de la antigua ficha "Presentación" se encuentran ahora en un botón dentro de la ficha contextual "Diseño". www.computron.edu.pe

36

Expertos en Ofimática Microsoft Excel Intermedio •

Desaparece el cuadro de diálogo

"Formato de...." y es sustituido por un panel que emerge a la derecha de la pantalla y que centraliza todas las acciones de dar formato a cualquier parte del gráfico...ya sean series, títulos, texto de los ejes, etc.

•

Aparece en Excel

2013

la

"Gráficos

herramienta recomendados",

con la que es posible elegir entre una galería de gráficos con

distintos

convertirlos

diseños en

y

gráficos

avanzados

(gráficos

que

combinan

columnas

con

áreas,

ejes

líneas

o

secundarios, etc...) y todo ello mediante una interfaz amigable y como decíamos al principio..."democrática": •

Nuevas etiquetas de datos con formas:

www.computron.edu.pe

37

Expertos en Ofimática Microsoft Excel Intermedio Al cambiar los datos de origen, el gráfico se recalcula (como siempre), pero ahora lo hace con una animación que hace la transición desde un valor a otro, lenta, más agradable y que enfatiza más la información que expresa. Crear un gráfico Recomendado La exploración de gráficos en Excel y averiguar que el que elige no funciona bien para sus datos es cosa del pasado. Pruebe el comando Gráficos recomendados de la pestaña Insertar para crear con rapidez un gráfico que sea justo el adecuado para sus datos. Este nuevo botón es nueva en esta versión así se puede crear más rápido un gráfico según los datos 1. Seleccione los datos para los que desea crear un gráfico. 2. Haga clic en Insertar > Gráficos recomendados.

3. En la pestaña Gráficos recomendados, desplácese por la lista de gráficos que Excel recomienda

para

sus

datos y haga clic en cualquier gráfico para ver

el

aspecto

que

tendrán sus datos. 4. Cuando encuentre el gráfico que desea, haga clic en él > Aceptar. 5. Use

los

botones

Elementos de gráfico, Estilos de gráfico y de filtros de gráfico que se

www.computron.edu.pe

38

Expertos en Ofimática Microsoft Excel Intermedio encuentran junto a la esquina superior derecha del gráfico para agregar elementos de gráfico como títulos de ejes o etiquetas de datos, personalizar el aspecto del gráfico o cambiar los datos que se muestran en el gráfico.

6. Para obtener acceso a las características de formato y diseño adicionales, haga clic en cualquier lugar del gráfico para agregar las Herramientas de gráfico a la cinta de gráfico y, a continuación, haga clic en las opciones que desea en las pestañas Diseño y Formato.

Creación de un Grafico Crear en forma inicial la tabla donde se ingresa el dato con el cual se crea el grafico

Botón de acceso rápido a opciones recomendadas por Excel

www.computron.edu.pe

39

Expertos en Ofimática Microsoft Excel Intermedio

Se va a escoger el grafico tipo pie 3D

Accesorios para la personalización del grafico

Personalización del grafico Una vez creado el grafico se puede usar las fichas www.computron.edu.pe

40

Expertos en Ofimática Microsoft Excel Intermedio

Dentro de la ficha DISEÑO encontramos:

Mediante el cual podemos escoger un modelo para agregar objetos dentro del grafico o alrededor como títulos, leyendas, entre otro cada uno presenta un modelo diferente Permite controlar el uso de colores hacia el grafico Cada uno de ellos almacena todo un conjunto de opciones o alternativas que agregan detalles del grafico

Cambia el tipo de visualización del grafico o transfiere el modo de mostrar los datos. • Permite modificar los datos seleccionados y por ello www.computron.edu.pe alterar la forma de presentación del gráfico. •

41

Expertos en Ofimática Microsoft Excel Intermedio

Dentro de la ficha formato se puede usar para cambiar colores del grafico

www.computron.edu.pe

42

Expertos en Ofimática Microsoft Excel Intermedio

Funciones de Texto

EXCEL

Las funciones de texto en Excel permiten concatenar cadenas de caracteres, remover los espacios en blanco, reemplazar ciertos caracteres por otros y www.computron.edu.pe

43

Expertos en Ofimática Microsoft Excel Intermedio muchas cosas más que te permitirán manipular las cadenas de texto para obtener los resultados deseados. Por lo general utilizamos Excel para procesar y evaluar datos numéricos, pero en ocasiones es necesario manipular o editar celdas que contienen texto, para ello podemos usar estas funciones: IZQUIERDA, DERECHA, EXTRAE, ENCONTRAR, LARGO, TEXTO y REEMPLAZAR, CONCATENAR. A continuación te mostramos descripciones y ejemplos de cada una. IZQUIERDA Devuelve las primeras letras a partir de la texto según estos parámetros: =IZQUIERDA(texto, núm. De caracteres)

izquierda

del

DERECHA Devuelve las primeras letras a partir de derecha a izquierda del texto según estos parámetros: =DERECHA(texto, núm. De caracteres)

EXTRAE Devuelve n letras de un texto a partir de la posición que indiquemos, los parámetros son: www.computron.edu.pe

44

Expertos en Ofimática Microsoft Excel Intermedio =EXTRAE(texto, posición inicial, núm. caracteres)

ENCONTRAR Esta función encuentra un texto dentro de otro texto y regresa la posición en la cual fue encontrado, los parámetros son: =ENCONTRAR(texto buscado, dentro del texto, núm. inicial) 1. Texto que estamos buscando 2. Texto en el que deseamos realizar la búsqueda 3. Posición por la cual deseamos realizar la búsqueda, si ingresamos 1, se buscara desde el inicio, si ingresamos 10, se buscara desde la décima letra, etc.

Nota: La búsqueda hace diferencia entre letras mayúsculas y minúsc ulas.

LARGO Devuelve la cantidad de letras de un texto, la función recibe como único parámetro el texto a evaluar. www.computron.edu.pe

45

Expertos en Ofimática Microsoft Excel Intermedio =LARGO(texto)

TEXTO Esta función se aplica a números y les da el formato que le indiquemos, esta función es muy útil para presentar números de una mejor forma, en este ejemplo le aplicamos un formato de número telefónico a un texto. =TEXTO(valor; formato)

Nota: Los números del texto van a reemplazar a los caracteres de numeral (#) dentro el formato indicado. REEMPLAZAR Esta función nos permite reemplazar o insertar textos dentro de otro texto. =REEMPLAZAR(texto original, núm. inicial, núm. de caracteres, texto nuevo)

CONCATENAR www.computron.edu.pe

46

Expertos en Ofimática Microsoft Excel Intermedio Concatenar es una palabra que se utilizar en programación para nombrar la acción de unir una serie de valores en un solo texto. Esta función en Excel nos permite unir en una celda uno o más valores. La función recibe de 1 a 30 parámetros cada uno con el valor que deseamos unir al texto final. =CONCATENAR(texto1, texto2,..., texto N)

www.computron.edu.pe

47

Expertos en Ofimática Microsoft Excel Intermedio

Funciones de Fecha y Hora

EXCEL

www.computron.edu.pe

48

Expertos en Ofimática Microsoft Excel Intermedio

Funciones de Fecha – Hora 

Hoy

Usa la función hoy() cuando requieras estar poniendo la fecha actual, por ejemplo al alimentar alguna plantilla de facturación o registro diario de ventas, para hacer cálculos con fechas posteriores y la actual, etc. Sintaxis: =Hoy() La función no requiere ningún argumento.



Año

La función AÑO devuelve el año de una celda que contiene una fecha. El uso de la función año es muy simple, solamente tenemos que indicar la celda que contiene la fecha y nos revolverá el año de dicha fecha Sintaxis: = AÑO(núm. de serie) •

núm. de serie: es un valor o celda de tipo fecha.

www.computron.edu.pe

49

Expertos en Ofimática Microsoft Excel Intermedio 

AHORA

La función devuelve la fecha, hora y minutos actuales, según el reloj interno controlado por tu ordenador. Excel maneja las fechas como números (depende de ti el formato que luego le apliques). El número de serie de una fecha Excel representa la cantidad de días transcurridos desde 1 de enero de 1900, hasta una fecha determinada. Sintaxis =AHORA() La función no requiere ningún argumento. 

FECHA

La función devuelve el número de serie secuencial que representa una fecha determinada. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Sintaxis =FECHA(año, mes, día) •

Año El argumento año puede tener de uno a cuatro dígitos. Microsoft Excel interpreta el argumento año según el sistema de fechas empleado. De forma predeterminada, Excel para Windows utiliza el sistema de fechas 1900 y Excel para Macintosh utiliza el sistema de fechas 1904.

•

Mes es un número que representa el mes del año. Si el mes es superior a 12, el mes agrega ese número de meses al primer mes del año especificado. Por ejemplo, FECHA(2008; 14; 2) devuelve el número de serie que representa la fecha 2 de febrero de 2009.

•

Día es un número que representa el día del mes. Si el día es superior al número de días del mes especificado, día agrega ese número de días al primer día del

www.computron.edu.pe

50

Expertos en Ofimática Microsoft Excel Intermedio mes. Por ejemplo, FECHA(2008; 1; 35) devuelve el número de serie que representa la fecha 4 de febrero de 2008.



FECHANUMERO

La función de Excel devuelve el número de serie de la fecha representada por texto_de_fecha. Use FECHANUMERO para convertir una fecha representada por texto en un número de serie.

Sintaxis

=Fechanumero(texto_de_fecha)

 FIN.MES La función devuelve una fecha de culminación de mes, anterior o posterior a la fecha indicada, según un valor numérico. Sintaxis =FIN.MES(fecha inicial, meses) •

Fecha inicial: es una fecha que se considera como punto de partida hacia el fin de mes de una fecha.

www.computron.edu.pe

51

Expertos en Ofimática Microsoft Excel Intermedio •

Meses: especifica el número de meses a partir de fecha inicial. Si meses es positivo, FIN.MES devolverá la fecha de fin de mes que es meses después de fecha inicial. Si meses es negativo, FIN.MES devolverá una fecha de fin de mes que es meses anterior a fecha inicial.

 DIAS360 La función de Excel calcula el número de días entre dos fechas basándose en un año de 360 días (doce meses de 30 días) que se utiliza en algunos cálculos contables. Use esta función para facilitar el cálculo de pagos si su sistema de contabilidad se basa en 12 meses de 30 días. Sintaxis

•

=DIAS360(fecha_inicial;fecha_final; método)

Fecha_inicial y fecha_final: son las dos fechas entre las que desea calcular el número de días. Si la fecha_inicial es posterior a la fecha_final, DIAS360 devuelve un número negativo. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008; 5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto.

Método Modo de cálculo •

FALSO u omitido Método US (NASD). Si la fecha inicial es el 31 del mes, se convierte en el 30 del mismo mes. Si la fecha final es el 31 del mes y la fecha inicial es anterior al 30, la fecha final se convierte en el 1 del mes siguiente; de lo contrario la fecha final se convierte en el 30 del mismo mes.

•

VERDADERO Método europeo. Las fechas iniciales o finales que corresponden al 31 del mes se convierten en el 30 del mismo mes.

www.computron.edu.pe

52

Expertos en Ofimática Microsoft Excel Intermedio



SIFECHA

La función de Excel es de gran ayuda cuando trabajamos con rangos de fechas. La función SIFECHA calcula la diferencia entre dos fechas puede mostrar el resultado en días, meses o años. Sintaxis

=SIFECHA (fecha inicial, fecha final, "intervalo")

fecha inicial: la fecha más antigua. • fecha final: la fecha más reciente. • "intervalo": calculo que desea hacer. Éstos son los intervalos disponibles. "d" Días entre las dos fechas. "m" Meses entre las dos fechas. "y" Años entre las dos fechas. "yd" Días entre las fechas, como si las fechas estaban en el mismo año. "ym" Meses entre las fechas, como si las fechas estaban en el mismo año. "md" Días entre las dos fechas, como si las fechas estaban en el mismo mes y año. •

www.computron.edu.pe

53

Expertos en Ofimática Microsoft Excel Intermedio 

DIAS.LAB

La Función devuelve el número de días laborables (omitiendo sábados y domingos) existentes entre dos fechas especificadas y los días festivos que se indiquen en una tabla. Sintaxis =DIAS.LAB(inicio; fin; rango feriados) • • •

inicio: es la fecha de inicio desde donde la función comienza a contar. fin: es la fecha final hasta donde la función cuenta. rango feriado: es un rango donde pueden indicarse otras fechas no laborables, como por ej. feriados.



DIA.LAB

La función devuelve una fecha laborable resultante de considerar una fecha de inicio y luego sumarle o restarle un cierto número de días laborables. Sintaxis

•

=DIA.LAB(fecha inicial; días lab; festivos)

fecha inicial: es una fecha o una función que devuelve una fecha.

www.computron.edu.pe

54

Expertos en Ofimática Microsoft Excel Intermedio • •

días lab: es la cantidad de días laborables que se adicionan o se substraen de fecha inicial. festivos: es un rango opcional que indica otras fechas que se omiten en el cálculo.



DIA

La función de Excel devuelve el día de una fecha, representada por un número de serie. El día se expresa como un número entero comprendido entre 1 y 31. Sintaxis

•

=DIA(núm_de_serie)

Núm_de_serie es la fecha del día que intenta buscar. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5; 23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto.



DIASEM

La función devuelve el número de día en la semana de una celda que contiene una fecha. www.computron.edu.pe

55

Expertos en Ofimática Microsoft Excel Intermedio Sintaxis

• •

= DIASEM(núm. de serie; [tipo])

núm. de serie: es un valor de tipo fecha. tipo: es un número que especifica el primer día de la semana. Sus valores son 1, 2 o 3. Si es 1: debemos interpretar 1 Dom - 7 Sáb, si es 2 interpretamos 1 Lun - 7 Dom, y si es 3 interpretamos 0 Lun - 6 Dom. Si este argumento se omite toma el tipo 1 por defecto.



HORA

La función devuelve un número que representa la hora de un valor hora determinado. Sintaxis

•

= HORA(núm. de serie)

núm de serie: es un valor que contiene una hora o un número que representa una fecha. También puede ser una función que devuelva estos valores.



HORANUMERO

La función devuelve un número decimal, que corresponde a una hora en formato de texto. Sintaxis www.computron.edu.pe

56

Expertos en Ofimática Microsoft Excel Intermedio = HORANUMERO(texto de hora) •

Texto de hora: es un texto que representa una hora. También puede ser una hora escrita entre comillas. Estos textos poseen forma de hora, pero en formato de texto.



MES

La función devuelve el número de mes de una celda que contiene una fecha. Sintaxis

•

= MES(núm. de serie)

núm. de serie: es un valor, celda o función que devuelve un valor de tipo fecha.



MINUTO

La función devuelve un número que representa los minutos de un valor hora determinado.

Sintaxis

•

= MINUTO(núm. de serie)

núm. de serie: es un valor que contiene una hora o un número que representa una fecha. También puede ser una función que devuelva estos valores.

www.computron.edu.pe

57

Expertos en Ofimática Microsoft Excel Intermedio



NSHORA

La función devuelve un número nulo o bien, positivo menor que uno. Este número decimal representa una determinada hora. Sintaxis = NSHORA(hora; minuto; segundo) • • •

hora: es un valor que se encuentra entre 0 y 23 e indicará la hora. minuto: es un valor que se encuentra entre 0 y 59 e indicará los minutos. segundo: es un valor que se encuentra entre 0 y 59 e indicará los segundos.



SEGUNDO

La función devuelve un número que representa los segundos de un valor hora determinado. Sintaxis •

= SEGUNDO(núm. de serie)

núm. de serie: es un valor que contiene una hora o un número que representa una fecha. También puede ser una función que devuelva estos valores.

www.computron.edu.pe

58

Expertos en Ofimática Microsoft Excel Intermedio

Creación de reportes mediante filtrados de datos

EXCEL

Filtros en Excel www.computron.edu.pe

59

Expertos en Ofimática Microsoft Excel Intermedio Los filtros en Excel nos permiten buscar un subconjunto de datos que cumpla con ciertos criterios. Generalmente todo comienza cuando tenemos un rango de celdas con información y queremos ver solamente aquellas filas que cumplen con ciertas condiciones. Por ejemplo, en la siguiente imagen se pueden ver los datos de ventas de una empresa. ¿Cómo puedo tener una vista con todas las filas que pertenecen a Hugo? Eso sería una tarea muy difícil de lograr si no tuviéramos la facilidad de crear filtros en Excel.

Cómo crear filtros en Excel Para crear un filtro podemos utilizar el comando Filtro que se encuentra en la ficha Datos dentro del grupo Ordenar y filtrar.

www.computron.edu.pe

60

Expertos en Ofimática Microsoft Excel Intermedio

Al pulsar el botón Filtro se colocarán flechas en el extremo derecho de cada uno de los encabezados de columna de nuestros datos indicando que podemos hacer uso de los filtros. El comando Filtro también podrás seleccionar desde Inicio > Modificar > Ordenar y filtrar > Filtro. Otra manera de crear un filtro es transformar nuestros datos en una tabla de Excel, lo cual insertará los filtros además de aplicar un formato especial a los datos. Cómo usar los filtros en Excel Para filtrar la información debemos elegir una columna y hacer clic en la flecha de filtro correspondiente para mostrar las opciones de filtrado. Todos los filtros, en la parte inferior, mostrarán una lista de valores únicos con una caja de selección a la izquierda de cada uno.

www.computron.edu.pe

61

Expertos en Ofimática Microsoft Excel Intermedio

Una opción que tenemos para filtrar los datos es elegir de manera individual aquellos valores que deseamos visualizar en pantalla. También podemos utilizar la opción (Seleccionar todo) para marcar o desmarcar todos los elementos de la lista. En la imagen anterior he elegido el nombre Hugo de manera que el filtro mostrará solamente las filas con dicho nombre.

www.computron.edu.pe

62

Expertos en Ofimática Microsoft Excel Intermedio Al pulsar el botón Aceptar se ocultarán las filas que no cumplen con el criterio de filtrado establecido. Observa que la flecha de filtro de la columna Vendedor ha cambiado para indicarnos que hemos aplicado un filtro. Además, los números de fila de Excel se muestran en un color diferente indicándonos que existen filas ocultas. Filtrar por varias columnas Si queremos segmentar aún más los datos mostrados en pantalla podemos filtrar por varias columnas. En el ejemplo anterior filtré las filas pertenecientes a Hugo, pero si además necesito saber las que pertenecen a la región Norte y Sur, entonces debo seleccionar dichas opciones dentro del filtro de la columna Región:

www.computron.edu.pe

63

Expertos en Ofimática Microsoft Excel Intermedio Al aceptar estos cambios se mostrarán solamente las filas que cumplen ambos criterios. Observa que ambas columnas habrán cambiado sus iconos para indicarnos que se ha aplicado un filtro en cada una de ellas.

Esto demuestra que es posible crear tantos filtros como columnas tengamos en nuestros datos y entre más criterios de filtrado apliquemos mucha mayor será la segmentación de datos que obtendremos. Cómo quitar un filtro en Excel Para quitar un filtro aplicado a una columna debemos hacer clic en la flecha del filtro y seleccionar la opción Borrar filtro de “Columna” donde Columna es el nombre de la columna que hemos elegido. Esta acción eliminará el filtro de una sola columna, pero si tenemos filtros aplicados a varias columnas y deseamos eliminarlos todos con una sola acción, entonces debemos pulsar el comando Borrar que se encuentra en la ficha Datos > Ordenar y filtrar.

Filtrar en Excel buscando valores Ya hemos visto que todos los filtros muestran una lista de valores únicos de la cual podemos seleccionar uno o varios de ellos y justo por arriba de dicha lista de valores se muestra un cuadro de texto que nos permite hacer una búsqueda. www.computron.edu.pe

64

Expertos en Ofimática Microsoft Excel Intermedio Por ejemplo, en la siguiente imagen he colocado la palabra “este” en el cuadro de búsqueda y como resultado se ha modificado la lista de valores mostrando solo aquellos donde se ha encontrado dicha palabra:

Cuando tenemos una lista muy grande de valores únicos y no podemos identificar fácilmente aquellos que deseamos seleccionar, podemos utilizar el cuadro de búsqueda para encontrar los valores que necesitamos. También es posible utilizar caracteres comodines como el asterisco (*) o el símbolo de interrogación (?) tal como si hiciéramos una búsqueda aproximada en Excel de manera que podamos ampliar los resultados de búsqueda. Filtros de texto en Excel Además de las opciones ya mencionadas para filtrar en Excel, cuando en una columna se detecta el tipo de dato texto, se mostrará una opción de menú llamada Filtros de texto como la siguiente:

www.computron.edu.pe

65

Expertos en Ofimática Microsoft Excel Intermedio

Al elegir cualquiera de estas opciones se mostrará un cuadro de diálogo que nos permitirá configurar cada uno de los criterios disponibles. Por ejemplo, al elegir la opción Comienza por se mostrará el siguiente cuadro de diálogo:

Si colocamos la letra “a” en el cuadro de texto junto a la opción “comienza por”, entonces Excel mostrará solamente los elementos de la columna Vendedor que comiencen por la letra “a”. Filtros de número en Excel www.computron.edu.pe

66

Expertos en Ofimática Microsoft Excel Intermedio De manera similar, si Excel detecta que una columna contiene valores numéricos, nos permitirá utilizar filtros específicos para dicho tipo de dato tal como lo puedes observar en la siguiente imagen:

A diferencia de los Filtros de texto, Excel nos permitirá utilizar los Filtros de número para mostrar valores que sean mayores o iguales que otro o simplemente aquellos que son superiores al promedio. Filtros de fecha en Excel Las fechas son el tipo de dato que más opciones de filtrado nos proporcionan, tal como lo muestra la siguiente imagen:

www.computron.edu.pe

67

Expertos en Ofimática Microsoft Excel Intermedio

Excel nos permitirá filtrar las fechas por días específicos como hoy, mañana o ayer e inclusive por períodos de tiempo más largos como semanas, meses, trimestres o años con tan solo seleccionar la opción adecuada. Filtrar por color en Excel No podíamos pasar por alto y dejar de hablar de la opción de Filtrar por color que nos ofrece Excel. Para que esta opción se habilite es necesario que las celdas tengan aplicado un color de relleno ya sea por una regla de formato condicional o modificando directamente el color de relleno con las herramientas de formato. En nuestro ejemplo he aplicado una regla de formato condicional para aquellas celdas que tengan un valor superior a $850 en la columna Total.

www.computron.edu.pe

68

Expertos en Ofimática Microsoft Excel Intermedio

Una vez que las celdas tienen un color de relleno, al hacer clic en el filtro de la columna Total se mostrará habilitada la opción Filtrar por color y dentro de ella podré elegir alguno de los colores presentes en la columna.

Filtro avanzado www.computron.edu.pe

69

Expertos en Ofimática Microsoft Excel Intermedio En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasión te mostraré cómo utilizarlo. Supongamos la siguiente tabla de datos.

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 coloqué los criterios por arriba de la tabla de datos aunque realmente su ubicación no es de importancia. www.computron.edu.pe

70

Expertos en Ofimática Microsoft Excel Intermedio Aplicar un filtro avanzado a los datos Antes de aplicar el filtro avanzado debo 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 he seleccionado el rango que contiene las condiciones del filtro avanzado necesarias. Solamente resta pulsar el botón Aceptar para aplicar el filtro.

www.computron.edu.pe

71

Expertos en Ofimática Microsoft Excel Intermedio

El resultado es precisamente lo que necesito, así que el filtrado avanzado ha sido exitoso. Pero ahora tengo un desafío mayor ¿Cómo podría hacer para especificar condiciones para una segunda columna? Supongamos que deseo 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 debo 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 www.computron.edu.pe

72

Expertos en Ofimática Microsoft Excel Intermedio que contienen el criterio para el Apellido. Al aceptar los cambios Excel aplicará el filtro avanzado adecuadamente.

De la misma manera puedes especificar un criterio para cada columna. Hasta ahora solamente he especificado una sola condición por columna, pero ahora deseo agregar a los resultados del filtro la información del departamento de Informática. Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.

www.computron.edu.pe

73

Expertos en Ofimática Microsoft Excel Intermedio 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.

Como podrás observar, es factible especificar una condición por cada fila del rango de criterios. De esta manera puedes crear un filtro avanzado en Excel.

www.computron.edu.pe

74

Expertos en Ofimática Microsoft Excel Intermedio

LABORATORIOS

Ejercicio 1 www.computron.edu.pe

75

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 2 www.computron.edu.pe

76

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 3

Ejercicio 4 www.computron.edu.pe

77

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 5

Ejercicio 6 www.computron.edu.pe

78

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 7

www.computron.edu.pe

79

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

80

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

81

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

82

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 10

Ejercicio 11

www.computron.edu.pe

83

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 12

Ejercicio 12

www.computron.edu.pe

84

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 13

Ejercicio 14

www.computron.edu.pe

85

Expertos en Ofimática Microsoft Excel Intermedio

Ejercicio 15

Ejercicio 16

www.computron.edu.pe

86

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

87

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

88

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

89

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

90

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

91

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

92

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

93

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

94

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

95

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

96

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

97

Expertos en Ofimática Microsoft Excel Intermedio

www.computron.edu.pe

98