• Author / Uploaded
• Ams Herrera

• Categories
• Hoja de cálculo
• Microsoft Excel
• Fórmula
• Función (Matemáticas)
• Matriz (Matemáticas)

Citation preview

EXCEL 2013 OPTIMIZACIÓN CÓN FUNCIÓNES, GRA FICÓS Y ANA LISIS DE DATÓS.

ING. JACP

Excel 2013 Formulas avanzadas ............................................................................................................................ 1 Adjuntar comentarios ..................................................................................................................... 1 Usar una fórmula para consolidar datos ......................................................................................... 2 Asignando nombres a rangos en Excel ............................................................................................ 3 Reglas de Sintaxis ........................................................................................................................ 4 Listas dependientes......................................................................................................................... 5 Funciones ............................................................................................................................................ 7 Función CONTAR ............................................................................................................................. 7 Función CONTARA ........................................................................................................................... 9 FUNCION CONTAR.BLANCO ............................................................................................................ 9 MAX ................................................................................................................................................. 9 MIN ................................................................................................................................................ 10 Funciones de Texto ....................................................................................................................... 11 IZQUIERDA ................................................................................................................................. 11 DERECHA ................................................................................................................................... 12 CONCATENAR ............................................................................................................................ 13 EXTRAE ...................................................................................................................................... 13 Dividir nombres con el Asistente para convertir texto en columnas ........................................ 15 Función Si ...................................................................................................................................... 15 Función SI anidada .................................................................................................................... 16 FUNCIÓN Y(valor_lógico 1;valor_lógico_2) ............................................................................... 17 FUNCIÓN O(valor_lógico 1;valor_lógico_2) .............................................................................. 17 Fechas............................................................................................................................................ 20 HOY ............................................................................................................................................ 20 Función AHORA ......................................................................................................................... 21 Funciones Excel DIA MES y AÑO ............................................................................................... 21 Función DIA ............................................................................................................................... 22 Función MES .............................................................................................................................. 22 Función AÑO.............................................................................................................................. 23 Búsqueda ....................................................................................................................................... 24 Función BUSCAR ........................................................................................................................ 24

i

ING. JACP Función COINCIDIR .................................................................................................................... 24 Función BUSCARV...................................................................................................................... 25 Nuevas funciones Excel 2013: SI.ND combinada con BUSCARV ............................................. 30 Función BUSCARH ..................................................................................................................... 30 Analizar los datos al instante ............................................................................................................ 32 ¿Qué característica de análisis debo usar? ................................................................................... 33 Formato Condicional ..................................................................................................................... 36 Formato condicional en números ............................................................................................. 36 Formato condicional en texto ................................................................................................... 38 Formato condicional en fechas ................................................................................................. 39 Resaltar duplicados con Formato condicional .......................................................................... 40 Validacion de Datos....................................................................................................................... 42 Qué opciones tengo para validar .............................................................................................. 42 ¿Cómo comienzo? ..................................................................................................................... 42 Aplicar validación sobre datos ya introducidos ........................................................................ 44 Organizar los datos de una hoja de trabajo con tablas ..................................................................... 45 Filtrar datos en una tabla Excel ..................................................................................................... 48 Mostrar los totales de los números .............................................................................................. 49 Filtrar por texto o números específicos ........................................................................................ 50 Filtrar elementos por color ........................................................................................................... 50 Crear una segmentación de datos para filtrar sus datos de tabla ................................................ 51 Filtros avanzados ........................................................................................................................... 53 Utilizar el signo igual para escribir texto o un valor .................................................................. 54 Considerar la distinción entre mayúsculas y minúsculas .......................................................... 54 Utilizar nombres predefinidos................................................................................................... 54 Subtotales...................................................................................................................................... 58 Funciones Base de Datos en Excel ................................................................................................ 60 Visualización de datos con graficas ................................................................................................... 63 Gráficos recomendados en Excel 2013 ..................................................................................... 65 Ficha Herramientas de gráfico .................................................................................................. 68 Crear hojas de gráficos en Excel ................................................................................................ 68 Análisis de datos................................................................................................................................ 69

ii

ING. JACP Minigráficos en Excel 2013............................................................................................................ 69 Tipos de minigráficos en Excel .................................................................................................. 69 Escenarios...................................................................................................................................... 72 Buscar objetivo .............................................................................................................................. 76 Importar o exportar archivos de texto .......................................................................................... 78 Auditoria De Hojas De Cálculo .......................................................................................................... 82 Mostrar las relaciones entre las fórmulas y las celdas.................................................................. 82 Fórmulas de seguimiento que hacen referencia a una celda en particular (dependientes) ........ 83 Ver todas las relaciones en una hoja de cálculo ........................................................................... 84 Esquematizar (agrupar) datos en una hoja de cálculo .................................................................. 85 Nuevas funciones 2013 ..................................................................................................................... 86

iii

Formulas avanzadas Adjuntar comentarios Como hemos visto anteriormente a una celda se le puede insertar un texto, un número o una fórmula, pero independientemente del tipo de dato que insertemos también se le puede adjuntar un comentario. Los comentarios son útiles para el usuario como avisos propios, por ejemplo para anotar como funciona una fórmula compleja o para informar de su contenido a otros usuarios. 1. Los pasos para adjuntar un comentario son: 2. Situarse en la celda en la cual queremos adjuntar el comentario. 3. Ir al menú Insertar/Comentario o bien mediante el menú contextual (hacer clic con el botón derecho del ratón) seleccionar comentario

Se abre un cuadro como el de la figura anterior indicando el usuario que hace el comentario. 4. Escribir en el cuadro el comentario 5. Para terminar hacer clic fuera del cuadro. Información: El nombre del usuario que aparece en el comentario, Excel lo toma de un panel en el que aparece el nombre del usuario desde el momento en que el programa se instaló en nuestro ordenador. Este dato se puede observar y/o modificar desde Menú de Herramientas -> Opciones... (abajo del todo) -> Solapa General: Nombre de usuario. Una celda que tiene un comentario se distingue de las demás porque tiene un triángulo rojo en su parte superior derecha y al situar el cursor sobre ella se despliega el comentario.

Para modificar un comentario: 1. Nos situamos en la celda que lo contiene.

1

ING. JACP 2. Ir al menú Insertar/Modificar Comentario o bien mediante el menú contextual seleccionar Modificar comentario 3. Se abre el cuadro del comentario de forma que permite modificar su contenido 4. Una vez que hemos terminado hacer clic fuera del cuadro. Para eliminar un comentario: 1. Nos situamos en la celda que lo contiene. 2. Ir al menú Insertar/Modificar Comentario seleccionar el cuadro y hacer clic en la tecla SUPR o bien mediante el menú contextual seleccionar Eliminar comentario En definitiva, es bueno añadir comentarios en nuestros trabajos de cara a futuras necesidades de aclaración. Me recuerda a los papelitos amarillos Post-It que solemos tener pegados por doquier.

Usar una fórmula para consolidar datos 1. En la hoja de cálculo maestra, copie o escriba los rótulos de columna o fila que desee para los datos de consolidación. 2. Haga clic en la celda en que desea incluir los datos de consolidación. 3. Escriba una fórmula que incluya una referencia de celda a las celdas de origen de cada hoja de cálculo o una referencia 3D que contenga los datos que desea consolidar. En cuanto a las referencias de celda, siga uno de los procedimientos siguientes: Si los datos que se van a consolidar están en celdas diferentes de otras hojas de cálculo 

Escriba una fórmula con referencias de celda a las otras hojas de cálculo, una por cada hoja de cálculo independiente. Por ejemplo, para consolidar datos de hojas de cálculo denominadas Ventas (en la celda B4), HR (en la celda F5) y Marketing (en la celda B9), en la celda A2 de la hoja de cálculo maestra, tendría que escribir lo siguiente:

Sugerencia Para especificar una referencia de celda como Ventas3!B4 en una fórmula sin escribir, escriba la fórmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de cálculo y, a continuación, haga clic en la celda. Si los datos que se van a consolidar están en las mismas celdas de otras hojas de cálculo Escriba una fórmula con una referencia 3D que use una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar los datos de las celdas A2 desde Ventas hasta

2

ING. JACP Marketing (ambos incluidos), tendría que escribir lo siguiente en la celda A2 de la hoja de cálculo maestra:

NOTA Si el libro está configurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes.

Asignando nombres a rangos en Excel

Hasta ahora siempre que hemos empleado las referencias, ya sean absolutas, relativas o mixtas, en una fórmula ha sido denotando el rango de por su celda inicial y la celda final, separadas por dos puntos. En el caso de las referencias absolutas, existe otra forma, mucho más cómoda de referirse a un rango: asignándole un nombre. Es decir, podemos poner una etiqueta a un rango de celdas y luego referirnos a ese rango mediante su nombre, mucho más corto y fácil. Por ejemplo, puedo nombrar al rango B3:D8 como artículos y luego emplearlo en una fórmula con la función suma: =suma(articulos), para calcular la suma de todas las celdas del rango B3:D8 que hemos llamado artículos. Para asignarle un nombre a un rango operaremos de la siguiente manera: 

Seleccionamos el rango de celdas al que le queremos asignar el nombre. Este rango puede ser de celdas contiguas o no. Si no lo son, para seleccionarlo, usaremos la tecla [Ctrl].



En el extremo más a la izquierda de la barra de fórmulas vemos un desplegable, denominado Cuadro de nombre. Hacemos click en él, escribimos el nombre y pulsamos Enter.

Otra manera para asignar un nombre a un rango de celdas es desde el menú fórmulas. Una vez seleccionado el rango, hacemos Fórmulas > Asignar nombre a un rango > Definir nombre... Usando esta opción podremos añadir un comentario a modo de recordatorio al nombre asignado. Así mismo, podemos variar el ámbito de validez del nombre. Por defecto, el nombre está definido para todo el libro, pero podemos hacer que se circunscriba únicamente a una o varias de las hojas del libro.

3

ING. JACP

Pero no hemos acabado, existe un tercer método de nombrar rangos. Si en el rango al que queremos nombrar existe una celda con el nombre, a modo de título o encabezado, por ejemplo, podemos asignar el nombreseleccionando el rango (incluído la celda que contiene el nombre), y luego seleccionando la opción Crear desde la Selección del menú Fórmulas.

Acabamos recordando que el uso de nombres para referirse a rangos de celdas supone la utilización de ese rango en Referencias Absolutas.

Reglas de Sintaxis Los nombres tienen que cumplir con una serie de reglas para que Excel los interprete correctamente. Varias de las consultas que he recibido relacionadas con que no les funciona determinado truco, se deben a que los nombres especificados NO cumplen con alguna de estas condiciones.

4

ING. JACP Las reglas que deben cumplir los nombres son:



Caracteres válidos: El primer carácter de un nombre debe ser una letra, un carácter de subrayado (_) o una barra invertida (\). El resto de los caracteres del nombre pueden ser letras, números, puntos y caracteres de subrayado.



No puede haber nombres de una letra sola que sean “c” o “r”. Tanto mayúsculas como minúsculas.



No pueden tener nombres iguales a referencias de celdas. Ejemplo Z$100 o R1C1.



No están permitidos los espacios. Se puede utilizar carácter de subrayado (_) y el punto (.) como separadores de palabra, por ejemplo Impuesto_Ventas o Primer.Trimestre.



Longitud de nombre: Máximo 255 caracteres.



Mayúsulas y minúsculas: Excel no distingue entre caracteres en mayúscula y minúscula en los nombres. Por ejemplo, si has creado el nombre Ventas y luego crea otro nombre VENTAS en el mismo libro, Excel pedirá que seleccione un nombre único.

Si sigues estas reglas, no tendrás problema para utilizas nombres y podrás sacarle mucho más provecho a esta propiedad del Excel. Se pueden usar nombres en fórmulas siempre y cuando ambas tengan el mismo ancho o alto por ejemplo: =Población/Superficie O usar en funciones por ejemplo: =Suma(Superficie)

Listas dependientes En esta ocasión mostraré una técnica para crear listas dependientes en Excel de la selección de otra lista. Empezaremos por crear una lista de países del mundo. En base a la selección que se realice en dicha lista tendré una segunda columna que mostrará las ciudades más importantes del país seleccionado.

En una segunda hoja de Excel he creado también las listas de ciudades que se desplegarán para cada país.

5

ING. JACP

El siguiente paso es muy importante porque a cada rango de ciudades le pondré el nombre del país al que pertenecen.

Es importante que el nombre del rango tenga el mismo nombre tal y como aparece en la lista de países. Una vez nombrados todos los rangos de acuerdo al país al que pertenecen puedo crear las listas de validación de datos para las ciudades. Selecciona las celdas que contendrán las listas de ciudades y ve al comando Validación de datos en donde elegiremos la opción Lista y en el recuadro Origen especificaremos la siguiente fórmula.

6

ING. JACP Al aceptar los cambios Excel mostrará un mensaje de advertencia diciendo que el origen evalúa un error lo cual quiere decir que en ese momento no hay selección alguna de un país y por lo tanto no se puede hacer una selección apropiada de ciudades. Todo está listo. Cuando selecciones un país de la primera columna, automáticamente se mostrará la lista de ciudades correspondientes a dicho país.

Las listas dependientes en Excel son de gran utilidad para validar adecuadamente los datos que son ingresados en una hoja. Recuerda que entre mejor calidad tengas en los datos mejor será el análisis y evaluación que podrás hacer de ellos.

Funciones Función CONTAR En este artículo se describen la sintaxis de la fórmula y el uso de la función CONTAR de Microsoft Office Excel. Descripción La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los números dentro de la lista de argumentos. Use la función CONTAR para obtener la cantidad de entradas en un campo de número de un rango o matriz de números. Por ejemplo, puede escribir la siguiente fórmula para contar los números en el rango A1:A20: =CONTAR(A1:A20) En este ejemplo, si cinco de las celdas del rango contienen números, el resultado es 5. Sintaxis CONTAR(valor1; [valor2],...) La sintaxis de la función CONTAR tiene los siguientes argumentos: valor1 Obligatorio. Primer elemento, referencia de celda o rango en el que desea contar números.

7

ING. JACP valor2, ... Opcional. Hasta 255 elementos, celdas de referencia o rangos adicionales en los que desea contar números. NOTA Los argumentos pueden contener o hacer referencia a una variedad de diferentes tipos de datos, pero sólo se cuentan los números. Observaciones 

Se cuentan argumentos que son números, fechas o una representación de texto de los números (por ejemplo, un número entre comillas, como "1").



Se tienen en cuenta los valores lógicos y las representaciones textuales de números escritos directamente en la lista de argumentos.



No se cuentan los argumentos que sean valores de error o texto que no se puedan traducir a números.



Si un argumento es una matriz o una referencia, sólo se considerarán los números de esa matriz o referencia. No se cuentan celdas vacías, valores lógicos, texto o valores de error de la matriz o de la referencia.



Si desea contar valores lógicos, texto o valores de error, use la funciónCONTARA.



Si desea contar sólo números que cumplan con determinados criterios, use la función CONTAR.SI o la función CONTAR.SI.CONJUNTO.

Ejemplo El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

A 1 2

B

C

Datos Ventas

3 8/12/2008 4 5 6

19

7

22,24

8

VERDADERO

9 #¡DIV/0!

8

ING. JACP

Fórmula

Descripción

Resultado

=CONTAR(A2:A8)

Cuenta la cantidad de celdas que contienen números en las celdas de A2 a A8.

3

=CONTAR(A5:A8)

Cuenta la cantidad de celdas que contienen números en las celdas de A5 a A8.

2

=CONTAR(A2:A8;2)

Cuenta la cantidad de celdas que contienen números en las celdas de A2 a A8 y el valor 2

4

10

11

12

Función CONTARA Como se mencionó al principio de esta nota, esta función es mucho más amplia: cuenta todas las celdas no-vacías dentro del rango. Esto es, que tengan cualquier cosa: número, texto, error o valor lógico. Es por esto que, el resultado de la función CONTARA(E3:E10), el cual contiene 8 celdas, da como resultado 7, lo que nos indica que hay 7 celdas ocupadas (ya que la que se encuentra en tercera posición está vacía).

FUNCION CONTAR.BLANCO Cuenta los espacios en blanco que hay en un rango

Como se ve la función CONTAR.BLANCO que está en la celda C9 cuenta las celdas en blanco que están en el rango C1:D7 que son 3.

MAX

9

ING. JACP La función MAX sirve cuando tenemos un conjunto de números y deseamos saber cuál es el mayor. Puede tener como argumento una serie de celdas individuales, un rango de celdas o una combinación de ambas. Esta función ignorará aquellos valores que sean de tipo texto o lógico. Ejemplo de operación: Utilizando la función MAX obtener cual es el número más grande de los siguientes números: 10, 86, 87,15, 1, 16, 54, 68, 90, 62. Suponiendo que acomodamos todos los numero es la columna A como en aprecia en la siguiente imagen:

Aplicaríamos la función de la siguiente forma: =MAX(A1:A10)

Y en este caso el número mayor es: 90

MIN Devuelve el valor mínimo de un conjunto de valores. Sintaxis MIN(número1;número2; ...) Número1, número2, ...

son entre 1 y 30 números de los que desea encontrar el valor mínimo.

Observaciones

10

ING. JACP 

Puede especificar los argumentos que sean números, celdas vacías, valores lógicos o representaciones numéricas en texto. Los argumentos que sean valores de error o texto que no se pueda traducir a números causarán errores.



Si un argumento es una matriz o referencia, sólo se usan los números de esa matriz o referencia. Las celdas vacías, valores lógicos o texto que se encuentren dentro de la matriz o referencia se pasan por alto. Si los valores lógicos o el texto no deben pasarse por alto, utilice la función MINA.



Si los argumentos no contienen números, MIN devuelve 0.

Ejemplo El ejemplo puede resultar más fácil de entender si lo copia en una hoja de cálculo en blanco.

A 1

Datos

2

10

3 7

4 5

9

6

27 2 Fórmula

Descripción (Resultado)

=MIN(A2:A6)

El menor de los números anteriores (2)

=MIN(A2:A6;0)

El menor de los números anteriores y 0 (0)

Funciones de Texto IZQUIERDA IZQUIERDA devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario. Sintaxis IZQUIERDA(texto; [núm_de_caracteres])

La sintaxis de las funciones tiene los siguientes argumentos: 

Texto

Obligatorio. La cadena de texto que contiene los caracteres que se desea extraer.

11

ING. JACP 

Núm_de_caracteres Opcional. Especifica el número de caracteres que se desea extraer con la función IZQUIERDA.



Núm_de_caracteres debe ser mayor o igual a cero.



Si núm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolverá todo el texto.



Si núm_de_caracteres se omite, se calculará como 1.

Ejemplo EJEMPLO 1: IZQUIERDA El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

A 1

B

Datos

2

Precio de venta

3 Suecia 4 5 6

Fórmula

Descripción (resultado)

=IZQUIERDA(A2;4)

Primeros cuatro caracteres de la cadena de texto (Prec)

=IZQUIERDA(A3)

Primer carácter de la segunda cadena (S)

DERECHA DERECHA devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres que el usuario especifica. Sintaxis DERECHA(texto;núm_de_caracteres) Texto

es la cadena de texto que contiene los caracteres que desea extraer.

Núm_de_caracteres

especifica el número de caracteres que desea extraer con DERECHA.

Observaciones 

Núm_de_caracteres debe ser mayor o igual que cero.

12

ING. JACP 

Si núm_de_caracteres es mayor que la longitud del texto, DERECHA devolverá todo el texto.



Si núm_de_caracteres se omite, se calculará como 1.

Ejemplo (DERECHA) El ejemplo puede resultar más fácil de entender si lo copia en una hoja de cálculo en blanco.

A 1 2

Datos Precio de venta

3 Número de acción Fórmula

Descripción (Resultado)

=DERECHA(A2;5)

Los últimos 5 caracteres de la primera cadena (Precio)

=DERECHA(A3)

El último carácter de la segunda cadena (n)

CONCATENAR Ejemplo de la función Si en la celda A1 tenemos el texto “Juan”, en la celda A2 “Rodriguez” y queremos que en la celda A3 aparezca el nombre y apellidos en la misma celda deberíamos escribir la siguiente función: =CONCATENAR(A1;” “;A2) Observa que el segundo argumento es “ “ de esta forma hacemos que el nombre y el apellido salgan separados por un espacio.

Otra forma de obtener el mismo resultado que utilizando la función Concatenar sería utilizando & de la siguiente forma: =A1&” “&A2

EXTRAE EXTRAE devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique y en función del número de caracteres que Sintaxis EXTRAE(texto;posición_inicial;núm_de_caracteres) Texto

es la cadena de texto que contiene los caracteres que desea extraer.

Posición_inicial es la posición del primer carácter que desea extraer de texto. La posición_inicial del primer carácter de texto es 1 y así sucesivamente.

13

ING. JACP Observaciones 1. Si posición_inicial es mayor que la longitud de texto, EXTRAE devuelve "" (texto vacío). 2. Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, EXTRAE devuelve los caracteres hasta el final de texto. 3. Si posición_inicial es menor que 1, EXTRAE devuelve el valor de error #¡VALOR! 4. Si núm_de_caracteres es negativo, EXTRAE devuelve el valor de error #¡VALOR! Ejemplo (EXTRAE) El ejemplo puede resultar más fácil de entender si lo copia en una hoja de cálculo en blanco. 

Cree un libro o una hoja de cálculo en blanco.



Seleccione el ejemplo en el tema de Ayuda. No seleccione los encabezados de fila o de columna.

 

Seleccionar un ejemplo de la Ayuda



Presione CTRL+C.



En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.



Para alternar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione CTRL+` (acento grave) o, en el menú Herramientas, elija Auditoría de fórmulas y, a continuación, haga clic en Modo de auditoría de fórmulas. A

1 2

Datos Flujo de líquido Fórmula

Descripción (Resultado)

=EXTRAE(A2;1;5)

Cinco caracteres de la cadena anterior, a partir del primero (Flujo)

=EXTRAE(A2;7;20)

Veinte caracteres de la cadena anterior, a partir del séptimo (de líquido)

=EXTRAE(A2;20;5)

Como el punto inicial es mayor que la longitud de la cadena, se devuelve texto vacío ()

En 2013 esta función cambia de nombre por MED

14

ING. JACP

Dividir nombres con el Asistente para convertir texto en columnas Utilice el Asistente para convertir texto en columnas para separar el contenido de celdas simples, como nombres y apellidos, en columnas distintas. NOMBRE COMPLETO

NOMBRE

APELLIDOS

Antonio Bermejo

Antonio

Bermejo

Almudena Benito

Almudena

Benito

Cecilia Cornejo

Cecilia

Cornejo

Nuria González

Nuria

González

Según cómo se organicen los datos, puede dividir el contenido de las celdas en función de un delimitador, como un espacio o un carácter (como una coma, un punto o un punto y coma) o puede dividirlo en función de la ubicación específica de un salto de columna en los datos.

Función Si Descripción: La función SI sirve para cuando requerimos evaluar una condición y dependiendo de si se cumple o no tener uno u otro resultado, es decir, si se cumple la condición tendríamos un resultado y si no se cumple el resultado sería otro. Esta función requiere de tres (3) argumentos: Prueba_Lógica: En la expresión que queremos evaluar. Valor_si_verdadero: Como su nombre lo indica, es el resultado si la prueba lógica resulta verdadera Valor_si_falso: Indica es el resultado si la prueba lógica resulta falsa. Ejemplo de operación: Si el número que tenemos en la columna A es mayor que 5 la columna B debe decir “MAYOR”, de lo contrario en B debe decir “NO ES MAYOR” La función quedaría de la siguiente forma: =SI(A1>5,"MAYOR","NO ES MAYOR") Como se aprecia en la imagen.

Posteriormente la función se debe repetir para cada valor que tengamos en “A” como se muestra en la imagen.

15

ING. JACP

Finalmente tenemos el resultado.

Función SI anidada Se dice que una función está anidada cuando se utiliza una función dentro de si misma. Haciendo un poco más complicado el ejemplo anterior, queremos saber si superó el objetivo y si la diferencia fue mayor de $5.000. Esto es, tenemos que hacer DOS comparaciones: Si supera el objetivo y si lo supera por más o menos de $5.000 Empezamos con la función: =SI(Ventas “No Cumplió” De lo contrario (caso FALSO) =SI(Ventas-Objetivo “Pasó menos de $5.000″ De lo contrario (caso FALSO) => “Pasó más de 5.000″. La fórmula completa quedaría para el ejemplo anterior: =SI(B22) devuelve VERDADERO Y(22) devuelve FALSO Y(22) devuelve VERDADERO O(22) devuelve VERDADERO O(2 Objetivo, entonces corresponde 15%; de lo contrario, SI supera el objetivo de venta O el de precio, corresponde 5%. SI no cumple ninguno, entonces la comisión es 0%. Vemos que no solo tenemos las funciones Y y O, sino distintas comparaciones usando SI (para ver una descripción de la función SI, ir acá). Sin embargo, vemos que tenemos varios SI que dependientes entre sí. Esto se llamanSI anidados. Recordemos la estructura de la función SI: SI(Prueba_logica;Valor_si_verdadero;Valor_si_falso) En la columna D, debemos ingresar la fórmula que nos ayude a determinar la comisión que corresponda.

18

ING. JACP

Para esto, vamos a armar la armar la fórmula para la celda D7 siguiendo el esquema de arriba. Lo primero que debemos resolver es si se cumplen las DOS condiciones (el primer cuadro azul del diagrama) 

Y(B7>$C$1;C7>$C$2)



B7: Ventas del vendedor AAAAA



$C$1: Objetivos de ventas (referencias fijas porque no cambiapara los distintos vendedores)



C7: Precio promedio vendedor.



$C$2: Objetivos de precio.

Ya tenemos la primera parte de la función: SI(se cumplen ambas condiciones; 15% de comisión(valor_si_verdadero); $C$1;C7>$C$2);$C$4;) [1] Ahora debemos armar la parte de la derecha del diagrama, cuando NO se cumplen ambas condiciones. O(B7>$C$1;C7>$C$2) Si la función da verdadero, corresponde el segundo nivel de comisión (que definimos en la celda C3). La única forma en que la evaluación da FALSO, es si ninguna de las condiciones se cumple. Por lo tanto, corresponde 0% de comisión. =SI(O(B7>$C$1;C7>$C$2);$C$3;0%) [2] Al incluir esta última fórmula en la fórmula [1], tenemos para la celda D7: =SI(Y(B7>$C$1;C7>$C$2);$C$4;SI(O(B7>$C$1;C7>$C$2);$C$3;0%))

19

ING. JACP

Fechas HOY Descripción Devuelve el número de serie de la fecha actual. El número de serie es el código de fecha-hora que Excel usa para los cálculos de fecha y hora. Si el formato de celda era General antes de especificar la función, Excel cambiará el formato de celda a Fecha. Si desea ver el número de serie, debe cambiar el formato de celda a General o Número. La función HOY es útil cuando necesita que se muestre la fecha actual en una hoja de cálculo, independientemente de cuándo se abre el libro. Además es útil para calcular los intervalos. Por ejemplo, si sabe que alguien nació en 1963, puede usar la siguiente fórmula para buscar la edad de esa persona a partir de este año de nacimiento: =AÑO(HOY())-1963 Esta fórmula usa la función HOY como argumento para la función AÑO para obtener la fecha actual y, a continuación, resta 1963 y devuelve la edad de la persona. NOTA Si la función HOY no actualiza la fecha según lo previsto, es posible que tenga que cambiar la configuración que controla cuándo se recalcula el libro o la hoja de cálculo. En la ficha Archivo, haga clic en Opciones y, a continuación, en la categoría Fórmulas en Opciones de cálculo, asegúrese de que la opciónAutomático esté seleccionada. Sintaxis HOY() La sintaxis de la función HOY no tiene argumentos. NOTA Excel almacena las fechas como números de serie secuenciales para que puedan usarse en los cálculos. De manera predeterminada, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.447 días posterior al 1 de enero de 1900. Microsoft Excel para Macintosh usa un sistema de fechas predeterminado diferente. Ejemplo El ejemplo será más fácil de entender si lo copia en una hoja de cálculo en blanco.

1 2

3

A

B

Fórmula

Descripción

=HOY()

Devuelve la fecha actual.

=HOY()+5

Devuelve la fecha actual más 5 días. Por ejemplo, si la fecha actual es 1/1/2008, está fórmula devuelve 6/1/2008.

20

ING. JACP

4

5

=VALFECHA("1/1/2030")HOY()

Devuelve el número de días entre la fecha actual y 1/1/2030. Tenga en cuenta que la celda A4 tiene que tener el formato de General oNúmero para que el resultado se muestre correctamente.

=DÍA(HOY())

Devuelve el día actual del mes (1 - 31).

=MES(HOY())

Devuelve el mes actual del año (1 - 12). Por ejemplo, si el mes actual es Mayo, esta fórmula devuelve 5.

6

Función AHORA La función AHORA devuelve el número de serie de la fecha y hora actuales. Si el formato de celda era General antes de especificar la función, Excel cambia el formato de celda al mismo formato de fecha y hora de la configuración regional de fecha y hora especificada en el Panel de control. Puede cambiar el formato de fecha y hora para la celda mediante los comandos en el grupo Número de la ficha Inicio de la cinta de opciones. La función AHORA es útil cuando se requiere mostrar la fecha y hora actuales en una hoja de cálculo o calcular un valor basándose en la fecha y hora actuales, y que ese valor se actualice cada vez que se abra la hoja de cálculo. La sintaxis de esta función es la siguiente: AHORA() La sintaxis de la función AHORA no posee argumentos. Para considerar: 

En los números de serie, los dígitos a la derecha del separador decimal representan la hora; los números a la izquierda representan la fecha. Por ejemplo, el número de serie 0,5 representa la hora 12:00 del mediodía.



Los resultados de la función AHORA sólo cambian cuando se realiza un cálculo en la hoja de cálculo o cuando se ejecuta una macro que contiene la función. No se actualiza constantemente.

Funciones Excel DIA MES y AÑO En muchísimas oportunidades nos topamos con la situación de requerir datos relacionados con fechas, para ello MS Excel dispone de tres interesantes y útiles funciones que nos permiten obtener resultados asociados por ejemplo a un día, mes o año en específico, en tal sentido en la siguiente publicación les estaremos explicando el uso de las funciones DIA, MES y AÑO. Las funciones DIA, MES y AÑO las podemos hallar en la Biblioteca de funciones dentro de la categoría Fecha y hora como se aprecia en la siguiente imagen:

21

ING. JACP

Para considerar Microsoft Excel almacena las fechas como números de serie secuenciales para que se puedan utilizar en cálculos. De manera predeterminada, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.448 días posterior al 1 de enero de 1900. Para Macintosh, Excel utiliza un sistema de de fechas predeterminado diferente. Vamos a comenzar explicando la función DIA.

Función DIA 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. Veamos la sintaxis mediante la siguiente imagen:

Núm_de_serie: es la fecha del día que intenta buscar. Las fechas deben especificarse utilizando la función FECHA o como resultado de otras fórmulas o funciones.

Función MES Devuelve el mes de una fecha representada por un número de serie. El mes se expresa como número entero comprendido entre 1 (enero) y 12 (Diciembre). Veamos la sintaxis de esta función mediante la siguiente imagen:

22

ING. JACP Núm_de_serie: es la fecha del mes que intenta buscar. Las fechas deben introducirse mediante la función FECHA o como resultado de otras fórmulas o funciones.

Función AÑO Devuelve el año correspondiente a una fecha. El año se devuelve como número entero comprendido entre 1900 y 9999. Veamos la sintaxis de esta función mediante la siguiente imagen:

Núm_de_serie: es la fecha del año que se desea buscar. Las fechas deben especificarse utilizando la función FECHA o como resultado de otras fórmulas o funciones. Es importante destacar que los valores que devuelven las funciones AÑO, MES Y DIA serán valores gregorianos independientemente del formato de presentación del valor de fecha suministrado. Una vez revisado estos puntos vamos a plantearle un sencillo ejemplo, dentro de una tabla tenemos un grupo de datos asociados a fechas específicas y a partir de esas fechas vamos a determinar el día, mes y año de las celdas que seleccionemos, veamos mediante la siguiente imagen la solución al ejemplo propuesto:

Como se aprecia en la imagen tenemos tres resultados diferentes para cada una de las celdas ya que al emplear cada una de las funciones descomponemos la fecha inicial para obtener el valor que necesitamos. Las funciones DIA, MES y AÑO son de mucha utilidad e importancia para realizar los cálculos que estén asociados con fechas ya que de una manera muy simple nos arrojan información que nos permiten obtener datos para simplificar las tareas que estemos realizando. Estas funciones puedes ser anidadas con una variada gama de funciones de MS Excel que nos ofrecen la posibilidad de crear nuevas fórmulas para así poder obtener los resultados deseados.

23

ING. JACP

Búsqueda Función BUSCAR Este artículo describe la sintaxis de la fórmula y el uso de la función BUSCAR en Microsoft Excel. Descripción La función BUSCAR devuelve un valor de un rango de una fila o una columna o de una matriz. La función BUSCARtiene dos formas de sintaxis: la forma vectorial y la de matriz. SI DESEA

VEA

USO

Buscar en un rango de una fila o una columna (denominado vector) un valor y devolver un valor desde la misma posición en un segundo rango.

Forma vectorial

Use la forma vectorial cuando disponga de una amplia lista de valores en los que buscar o cuando es posible que los valores cambien con el tiempo.

Buscar el valor especificado en la primera fila o columna de una matriz y devolver un valor desde la misma posición en la última fila o columna de la matriz.

Forma de matriz

Use esta forma cuando disponga de una pequeña lista de valores y estos permanezcan siempre constantes.





También puede usar la función BUSCAR como alternativa a la función SI para crear pruebas o realizar pruebas que excedan el límite de funciones anidadas. Vea los ejemplos de la forma de matriz. Para que la función BUSCAR funcione correctamente, debe colocar los datos en los que se realiza la búsqueda en orden ascendente. Si esto no es posible, considere la posibilidad de usar las funciones BUSCARV , BUSCARH o COINCIDIR.

Función COINCIDIR Este artículo describe la sintaxis de la fórmula y el uso de la función COINCIDIR en Microsoft Excel. Descripción La función COINCIDIR busca un elemento especificado en un rango de celdas y 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)

24

ING. JACP

devuelve el número 2, porque 25 es el segundo elemento en el rango. Use COINCIDIR en lugar de las funciones BUSCAR para 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 argumentofila 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. Es el 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 persona como valor de búsqueda, pero el valor que desea es el número de teléfono. El argumento de 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. Es el rango de celdas en 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. La siguiente tabla describe la manera en que la función encuentra valores basados en 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 elvalor_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. Debe colocar los valores del argumento matriz_buscada en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, ...2, 1, 0, -1, -2, ..., etc.

Función BUSCARV Este artículo describe la sintaxis de la fórmula y el uso de la función BUSCARV en Microsoft Excel.

25

ING. JACP Descripción Puede usar la función BUSCARV para buscar la primera columna de un rango de celdas y devolver un valor de cualquier celda de la misma fila del rango. Por ejemplo, si tiene una lista de empleados contenida en el rango A2:C10, los números de identificación de los empleados se almacenan en la primera columna del rango, como muestra la siguiente ilustración.

Si conoce el número de identificación del empleado, puede usar la función BUSCARV para devolver el departamento o el nombre de dicho empleado. Para obtener el nombre del empleado número 38, puede usar la fórmula =BUSCARV(38, A2:C10, 3, FALSO). Esta fórmula busca el valor 38 en la primera columna del rango A2:C10 y después devuelve el valor contenido en la tercera columna del rango y en la misma fila que el valor buscado ("Juan Carlos Rivas"). La V de BUSCARV significa vertical. Use BUSCARV en lugar de BUSCARH si los valores de comparación se encuentran en una columna situada a la izquierda de los datos que desea buscar. Sintaxis BUSCARV(valor_buscado, matriz_buscar_en, indicador_columnas, [ordenado]) La sintaxis de la función BUSCARV tiene los siguientes argumentos: 1. Valor_buscado Obligatorio. Es el valor que se va a buscar en la primera columna de la tabla o rango. El argumento valor_buscado puede ser un valor o una referencia. Si el valor que proporcione para el argumentovalor_buscado es inferior al menor valor de la primera columna del argumento matriz_buscar_en, BUSCARVdevuelve al valor de error #N/A. 2. Matriz_buscar_en Obligatorio. Es el rango de celdas que contiene los datos. Puede usar una referencia a un rango (por ejemplo, A2:D8) 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. 3. Indicador_columnas Obligatorio. Es un número de columna del argumento 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 el argumento indicador_columnas es:

26

ING. JACP    

Inferior a 1, BUSCARV devuelve al valor de error #¡VALOR!. Superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #¡REF!. Ordenado Opcional. Es un valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada: Si omite ordenado o es VERDADERO, devolverá una coincidencia exacta o aproximada. Si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

IMPORTANTE Si omite ordenado o es VERDADERO, los valores de la primera columna de matriz_buscar_endeben aparecer en orden ascendente; en caso contrario, es posible que BUSCARV no devuelva el valor correcto. Para más información, vea Ordenar datos en un rango o tabla. Si ordenado es FALSO, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. 

Si el argumento ordenado es FALSO, BUSCARV solo buscará una coincidencia exacta. Si hay dos o más valores en la primera columna de matriz_buscar_en que coinciden con el argumento valor_buscado, se usará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A. Observaciones 1.

2.

3.

Al buscar valores de texto en la primera columna de matriz_buscar_en, asegúrese de que los datos de la primera columna de matriz_buscar_en no tienen espacios al principio ni al final, de que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ‘ o “) y de que no hay 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_enno 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, puede usar 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 caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del carácter.

Ejemplo Copie los datos de ejemplo en la tabla siguiente y péguelos en la celda A1 de la nueva hoja de datos de Excel. Para que las fórmulas muestren resultados, selecciónelas, presione F2 y, después, presione Entrar. Si es necesario, puede ajustar los anchos de columna para ver todos los datos. DENSIDAD

VISCOSIDAD

TEMPERATURA

0,457

3,55

500

27

ING. JACP 0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Fórmula

Descripción

Resultado

=BUSCARV(1,A2:C10,2)

Usando una coincidencia aproximada, busca el valor 1 en la columna A, busca el mayor de los valores que sea inferior o igual a 1 en la columna A, que es 0,946, y después devuelve el valor de la columna B en la misma fila.

2,17

=BUSCARV(1,A2:C10,3, VERDADERO)

Usando una coincidencia aproximada, busca el valor 1 en la columna A, busca el mayor de los valores que sea inferior o igual a 1 en la columna A, que es 0,946, y después devuelve el valor de la columna c en la misma fila.

100

=BUSCARV(0.7,A2:C10,

Usando una coincidencia

#N/A

28

ING. JACP 3,FALSO)

aproximada, busca el valor 0,7 en la columna A. Como en la columna A no hay ninguna coincidencia exacta, devuelve un error.

=BUSCARV(0.1,A2:C10, 2,VERDADERO)

Usando una coincidencia aproximada, busca el valor 0,1 en la columna A. Como 0,1 es inferior al menor de los valores de la columna A, devuelve un error.

#N/A

=BUSCARV(2,A2:C10,2, VERDADERO)

Usando una coincidencia aproximada, busca el valor 2 en la columna A, busca el mayor de los valores que sea inferior o igual a 2 en la columna A, que es 1,29, y después devuelve el valor de la columna B en la misma fila.

1,71

29

ING. JACP

Nuevas funciones Excel 2013: SI.ND combinada con BUSCARV La función SI.ERROR: Excel 2010 introdujo la función SI.ERROR para devolver un valor especificado, en caso de que la fórmula devuelva #N/A, #¡DIV/0!, #¿NOMBRE?, pero la cuestión es que arrojará un resultado sin detectar cuál fue el error devuelto. Cómo funciona SI.ND con BUSCARV: La función con la cual se combinará es con la famosa BUSCARV. Dicha función devuelve precisamente #N/A cuando un valor no se encuentra en la lista, lo cual se vuelve de mucha utilidad sin tomamos en cuenta que dicha función es una de las más utilizadas entre los usuarios de Excel.

Función BUSCARH Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o de los datos que desee encontrar. La H de BUSCARH significa "Horizontal". Sintaxis BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado) Valor_buscado

es el valor que se busca en la primera fila de la tabla. Valor_buscado puede ser

un valor, una referencia o una cadena de texto. Matriz_buscar_en

es una tabla de información en la que se buscan los datos. Utilice una

referencia a un rango o el nombre de un rango.  

Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos. Si ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_en deben colocarse en orden ascendente: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo

30

ING. JACP

 

contrario, BUSCARH puede devolver un valor incorrecto. Si ordenado es FALSO, no es necesario ordenar matriz_buscar_en. El texto en mayúsculas y en minúsculas es equivalente. Ordena los valores en orden ascendente, de izquierda a derecha. Para obtener más información, vea Ordenar datos.

Indicador_filas

es el número de fila en matriz_buscar_en desde el cual debe devolverse el valor

coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor que 1, BUSCARH devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #¡REF! Ordenado

es un valor lógico que especifica si BUSCARH debe localizar una coincidencia exacta

o aproximada. Si es VERDADERO o se omite, devolverá una coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor mayor que sea inferior a valor_buscado. Si es FALSO, BUSCARH encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error #N/A. Observaciones   

Si BUSCARH no logra encontrar valor_buscado, utiliza el mayor valor que sea menor que valor_buscado. Si valor_buscado es menor que el menor valor de la primera fila de matriz_buscar_en, BUSCARH devuelve el valor de error #N/A. Si Ordenado es FALSO y valor_buscado es un valor de texto, se pueden usar 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 caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del carácter.

Ejemplo El ejemplo será más fácil de entender si lo copia a una hoja de cálculo en blanco .

1 2

A

B

C

Ejes

Cojinetes

Pernos

4

4

9

5

7

10

6

8

11

3 4

31

ING. JACP

Fórmula

Descripción (resultado)

=BUSCARH("Ejes";A1:C4;2;VERDADERO)

Busca Ejes en la fila 1 y devuelve el valor de la fila 2 que está en la misma columna (4)

=BUSCARH("Cojinetes";A1:C4;3;FALSO)

Busca Cojinetes en la fila 1 y devuelve el valor de la fila 3 que está en la misma columna (7)

=BUSCARH("B";A1:C4;3;VERDADERO)

Busca B en la fila 1, y devuelve el valor de la fila 3 que está en la misma columna. Debido a que B no es una coincidencia exacta, se utiliza el siguiente valor menor que B: Ejes. (5)

=BUSCARH("Pernos";A1:C4;4)

Busca Pernos en la fila 1 y devuelve el valor de la fila 4 que está en la misma columna (11)

=BUSCARH(3;{1;2;3\"a";"b";"c"\"d";"e";"f"};2;VERDADERO)

Busca 3 en la primera fila de la constante matricial y devuelve el valor de la fila 2 en la misma columna (c)

Analizar los datos al instante Solía requerir algo de trabajo analizar sus datos pero ahora solo son necesarios uno 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. 

Seleccione las celdas que contiene los datos que desea analizar.



Haga clic en el botón Análisis rápido

en la parte inferior derecha de los datos

seleccionados (o presione Ctrl + Q).

32

ING. JACP



1. En la galería Análisis rápido, seleccione la pestaña que desee. Por ejemplo, elija Gráficos para ver los datos en un gráfico.



Elija una opción o simplemente señale a cada una para obtener una vista previa.

Puede que observe que las opciones entre las que puede elegir no son siempre las mismas. Eso se debe a que las opciones cambian en función del tipo de datos que ha seleccionado en el libro.

¿Qué característica de análisis debo usar? Si no está seguro de qué opción de análisis elegir, aquí tiene una introducción rápida. Formato permite resaltar parte de sus datos agregando aspectos como colores y barras de datos, lo cual le permite ver con rapidez valores altos y bajos, entre otras cuestiones.

33

ING. JACP

Gráficos Excel recomienda diferentes gráficos, en función del tipo de datos que ha seleccionado. Si no ve el gráfico que desea, haga clic en Más gráficos.

Totales permite calcular los números en columnas y filas. Por ejemplo, Total inserta un total que aumenta a medida que agrega elementos a sus datos. Haga clic en las pequeñas flechas negras a la derecha y a la izquierda para ver más opciones.

34

ING. JACP

Tablas facilita el filtrado y la ordenación de sus datos. Si no ve el estilo de tabla que desea, haga clic en Más.

Minigráficos son similares a gráficos diminutos que puede mostrar junto con sus datos. Proporcionan una manera rápida de ver tendencias.

35

ING. JACP

Formato Condicional

Formato condicional en números Cuando los datos de nuestras celdas son valores numéricos, Excel provee de varias opciones para aplicar un formato condicional rápidamente. Las primeras alternativas que analizaremos serán las que se encuentran en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas.

Sabiendo que nuestros datos son valores numéricos, podemos utilizar las siguientes opciones:    

Es mayor que: Se aplicará el formato a todas las celdas con un valor mayor al especificado. En menor que: El formato será aplicado a las celdas con un valor menor que el indicado. Entre: Excel evaluará las celdas para saber aquellas que tengan un valor dentro del rango indicado y se les aplicará el formato. Es igual a: Solo las celdas que sean iguales al valor indicado tendrán el formato.

Una vez que seleccionamos la opción que deseamos utilizar, Excel mostrará un cuadro de diálogo que nos permitirá indicar los valores numéricos con los cuales se realizará la comparación y también podremos proporcionar todo el detalle del formato a aplicar. En la siguiente imagen puedes notar la aplicación de cada una de las reglas mencionadas anteriormente sobre nuestros datos numéricos:

36

ING. JACP

Otras reglas de formato condicional que podemos aplicar rápidamente a celdas con valores numéricos son las que se encuentran bajo la opción de menú Reglas superiores e inferiores:

37

ING. JACP A continuación describo brevemente cada una de estas opciones de formato condicional.      

10 superiores: Se aplicará el formato exactamente a las 10 celdas que tengan los valores más altos. Es posible modificar la cantidad de celdas superiores a las que se aplicará el formato. 10% de valores superiores: Excel aplicará el formato al 10% de las celdas que contengan los valores más altos. También es posible indicar un porcentaje diferente al 10%. 10 inferiores: El formato se aplica a las 10 celdas con los valores más bajos. 10% de valores inferiores: El formato es aplicado al 10% de las celdas con los valores más bajos dentro del rango. Por encima del promedio: Excel obtiene el promedio de todos los valores numéricos del rango y aplica el formato a las celdas que tengan un valor por encima de dicho promedio. Por debajo del promedio: Después de obtener el promedio, el formato será aplicado en las celdas que tengan un valor inferior.

En la siguiente imagen vemos aplicada cada una de las reglas de formato condicional anteriores:

Con estos ejemplos de formato condicional podemos ver lo fácil que es utilizar esta funcionalidad para resaltar los valores numéricos de nuestro interés.

Formato condicional en texto Si nuestras celdas contienen texto podemos utilizar algunas opciones de formato condicional para resaltar nuestras celdas. La primera opción que podemos utilizar es la regla que nos ayuda a

38

ING. JACP saber si un valor es igual a otro, me refiero a la opción que se encuentra en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Es igual a.

Esta opción comparará el valor de cada celda con la cadena de texto especificada y en caso de ser iguales se aplicará el formato. Es importante mencionar que esta regla no es sensible a mayúsculas y minúsculas. Otra regla de formato condicional para texto que podemos utilizar es la opción Texto que contiene que se encuentra en el mismo menú que la opción anterior, solo que en este caso, Excel buscará las celdas que contengan la cadena de texto especificada. Observa un ejemplo de ambas reglas:

Formato condicional en fechas Si los datos que tenemos en nuestra hoja son fechas, entonces existe una opción especialmente diseñada para este tipo de datos. Podemos aplicar formato condicional a celdas que contienen

39

ING. JACP fechas desde Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Una fecha. Al hacer clic sobre esta opción se mostrará el siguiente cuadro de diálogo:

Esta opción nos permitirá resaltar fácilmente las celdas que contengan una fecha que cumpla con el criterio seleccionado: Hoy, Ayer, Mañana, En los últimos 7 días, Semana pasada, etc.

Resaltar duplicados con Formato condicional También es posible utilizar el formato condicional para resaltar duplicados en nuestros datos. Esta opción funciona para cualquier tipo de dato que tengamos en las celdas, ya sean números, fechas e inclusive texto. La opción que tenemos que elegir para resaltar valores duplicados se encuentra en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Duplicar valores:

40

ING. JACP Esto mostrará un cuadro de diálogo que nos permitirá elegir si queremos resaltar los valores que están duplicados o los valores únicos. A continuación puedes ver un ejemplo de estas opciones sobre nuestros datos:

La regla Duplicados se aplicará sobre todos los elementos que aparecen más de una vez y la regla Únicos será para las celdas que solamente aparecen una vez dentro del rango. Con estos ejemplos de formato condicional en Excel 2013 podemos darnos cuenta de los beneficios que tiene esta funcionalidad para ayudarnos a resaltar rápidamente celdas que cumplen con algún criterio establecido. Además de las opciones revisadas hasta ahora existen otros tipos de formato condicional que revisaremos en lecciones posteriores.

41

ING. JACP

Validación de Datos Qué opciones tengo para validar Excel provee de varios tipos de validaciones, las cuales podemos adecuarlas a nuestros formatos. En la siguiente lista vemos qué tipos de datos podemos validar.

      

Número entero. Entendamos por número entero, todos aquellos que no cuenten con decimales. Decimal.Este tipo se puede usar sobretodo si deseamos usar porcentajes en las celdas. Si deseamos introducir 20%, en la celda pondremos .2 y damos formato de porcentaje. Lista. Definimos una lista de valores que se encuentren en un rango de cualquier hoja. Fecha. Aquí los datos deben tener formato de fecha. Hora. Se validará que el dato introducido sea hora. Longitud de texto. Se validará si el texto introducido cumple con la longitud asignada. Personalizada. Podremos personalizar nuetra validación mediante una fórmula, por ejemplo podemos permitir que en la celda sólo se inserten fórmulas, para lo cual definiremos la siguiente fórmula como validación en la celda =ESFORMULA(D9).

La validación de datos numéricos, fecha y hora tendrán los siguientes criterios de validación que podremos usar: 

Entre.



No está entre.



Igual a.



No igual a.



Mayor que.



Menor que.



Mayor o igual que.



Menor o igual que.

¿Cómo comienzo? Se podrán validar celdas únicas o rangos adyacentes o no adyacentes. Sólo debemos elegir la o las celdas a validar y nos vamos a la pestaña Datos > Validación de datos. Para mostrar un ejemplo, vamos a permitir sólo el ingreso de datos que se encuentren en una lista mediante un ComboBox en una celda.

42

ING. JACP

En la pestaña de Mensaje de entrada, especificaremos un mensaje como ayuda visual para que el usuario sepa qué datos debe ingresar.

Podemos definir un mensaje con un aviso al cliente de que el valor ingresado no coincide con la validación definida. En la pestaña Mensaje de error definimos el tipo de aviso y el texto.

43

ING. JACP

En la siguiente tabla vemos los distintos tipos de avisos que podemos utilizar según convenga.

Aplicar validación sobre datos ya introducidos La validación se genera al momento de introducir datos, pero qué sucede cuando ya tenemos nuestros datos y deseamos saber si alguno no cumple con las reglas que le queremos definir. En el botón de Validación de datos, tenemos al opción de Redondear con un círculo los datos no válidos. En la siguiente imagen definimos que sólo deben permitirse fechas que pertenezcan al año 2013, pero como ya las tenía antes de la validación, las voy a marcar para saber cuáles no cumplen.

44

ING. JACP

Organizar los datos de una hoja de trabajo con tablas Un modo sencillo de acceder a gran parte del potencial de Excel es poner los datos en una tabla. Esto permite filtrar u ordenar rápidamente los datos para empezar. 1. Seleccione los datos haciendo clic en la primera celda y arrastrándola a la última celda de los datos. 2. Para usar el teclado, mantenga pulsada la tecla Mayús a la vez que presiona las teclas de flecha para seleccionar los datos. 3. Haga clic en el botón Análisis rápido

de la esquina inferior derecha de la selección.

45

ING. JACP



Haga clic en Tablas, mueva el cursor al botón Tabla para poder ver el aspecto que tendrán los datos. Si le gusta, haga clic en el botón.



Ahora puede jugar con los datos: fíltrelos para ver solo los datos que desee u ordénelos para que vayan, por ejemplo, del mayor al menor. Haga clic en la flecha

del

encabezado de tabla de una columna. 

Para filtrar los datos, desactive el cuadro Seleccionar todo para borrar todas las marcas de verificación y active los cuadros de los datos que desee mostrar en la tabla.

46

ING. JACP



Para ordenar todos los datos, haga clic en Ordenar de la A a Z u Ordenar de la Z a la A.

47

ING. JACP

Filtrar datos en una tabla Excel Cuando coloque sus datos en una tabla, los controles de filtrado se agregarán a los encabezados de tabla automáticamente.

Para un filtrado rápido, haga lo siguiente: 

Haga clic en la flecha



En la lista de texto o números, desactive la casilla (Seleccionar todo) de la parte superior

del encabezado de tabla de la columna que desea filtrar.

de la lista y, a continuación, active las casillas de los elementos que desea mostrar en su tabla.

48

ING. JACP

SUGERENCIA

Para ver más elementos en la lista, arrastre el controlador de la esquina inferior

derecha de la galería de filtros para ampliarla. 

Haga clic en Aceptar.

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.

Mostrar los totales de los números Las herramientas de análisis rápido permiten obtener el total de los números rápidamente. Tanto si se trata de una suma, un promedio o un recuento, Excel muestra los resultados del cálculo debajo o junto a los números. 1.

Seleccione las celdas que contienen los números que desea agregar o contar.

2.

Haga clic en el botón Análisis rápido

3.

Haga clic en Totales, mueva el cursor por los botones para ver los resultados del cálculo

de la esquina inferior derecha de la selección.

de los datos y haga clic en el botón para aplicar los totales.

49

ING. JACP

Filtrar por texto o números específicos 

Haga clic en la flecha

del encabezado de tabla de la columna que desea filtrar.



Si la columna tiene números, haga clic en Filtros de número. Si la columna tiene entradas de texto, haga clic enFiltros de texto.



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.

Para filtrar por dos condiciones, introduzca las condiciones de filtrado en ambos conjuntos de cuadros y elija Ypara que ambos sean verdadero y O para que cualquiera de las condiciones sea verdadera.

Filtrar elementos por color

50

ING. JACP

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.

Crear una segmentación de datos para filtrar sus datos de tabla En Excel 2010, se han incorporado segmentaciones de datos como una nueva manera de filtrar datos de tabla dinámica. En Excel 2013, también 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.

51

ING. JACP

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.

1.

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

1.

En el cuadro de diálogo Insertar Segmentación de datos, active las casillas para las que desea crear segmentaciones de datos.

2.

Haga clic en Aceptar.

3.

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.

4.

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

5.

Para elegir más de un elemento, mantenga presionada la tecla Ctrl y, a continuación, elija los elementos que desea mostrar.

52

ING. JACP

SUGERENCIA

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.

Filtros avanzados El comando Avanzadas funciona de forma diferente del comando Filtrar en varios aspectos importantes. 1. 2.

Muestra el cuadro de diálogo Filtro avanzado en vez del menú de Autofiltro. 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 utiliza el rango de criterios independiente del cuadro de diálogo Filtro avanzado como el origen de los criterios avanzados.

Ejemplo: rango de criterios (A1:C4) y rango de datos (A6:C10) que se usan en los siguientes procedimientos Posiblemente sea más sencillo comprender el ejemplo si lo copia en una hoja de cálculo en blanco.

1

A

B

C

Tipo

Vendedor

Ventas

Tipo

Vendedor

Ventas

Bebidas

Suyama

5122 $

Carnes

Davolio

450 $

Alimentos

Buchanan

6328 $

Alimentos

Davolio

6544 $

2 3 4 5 6 7 8 9 10

53

ING. JACP Utilizar el signo igual para escribir texto o un valor Dado que el signo igual (=) se utiliza para indicar una fórmula cuando se escribe texto o un valor en una celda, Excel evalúa lo escrito; no obstante, esto puede provocar resultados de filtro inesperados. Para indicar un operador de comparación de igualdad para texto o un valor, escriba los criterios como expresiones de cadena en las celdas apropiadas del rango de criterios: =''=entrada'' En este caso, entrada es el texto o el valor que se desea buscar. Por ejemplo: EN LA CELDA SE ESCRIBE

EXCEL EVALÚA Y MUESTRA

="=Davolio"

=Davolio

="=3000"

=3000

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 cuadroRango de criterios. También se puede definir el nombre Base de datos para el rango de datos que se deben 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. Crear criterios utilizando una fórmula Puede utilizar como criterio valores calculados que sean el resultado de una fórmula. Tenga en cuenta los puntos importantes siguientes: 1. 2.

La fórmula se debe evaluar como VERDADERO o FALSO. Puesto que está utilizando una fórmula, escriba la fórmula como lo haría normalmente, pero no la escriba de la forma siguiente:

=''=entrada'' 1.

No utilice rótulos de columnas para los rótulos de los criterios; deje los rótulos de criterios en blanco o utilice uno que no sea un rótulo de columna incluido en el rango (en los ejemplos siguientes, Promedio calculado y Coincidencia exacta).

2.

Si en la fórmula utiliza un rótulo de columna en lugar de una referencia relativa a celda o un nombre de rango, Excel presenta un valor de error, como por ejemplo #¿NOMBRE? o #¡VALOR! en

54

ING. JACP la celda que contiene el criterio. Puede pasar por alto este error, ya que no afecta a la manera en 3.

4.

que se filtra el rango. La fórmula que se utiliza para los criterios debe usar una referencia relativa para hacer referencia a la celda correspondiente de la primera fila. En el ejemplo, Filtrar utilizando una fórmula para valores mayores que el promedio de todos los valores en el rango de datos, se utilizaría C7, y en el ejemplo, Filtrar utilizando una fórmula para texto en una búsqueda en la que se distinga entre mayúsculas y minúsculas, se utilizaría A7). Todas las demás referencias usadas en la fórmula deben ser referencias absolutas.

Filtrar utilizando varios criterios en una columna en la que puede cumplirse cualquier criterio Lógica booleana: 1.

(Vendedor = "Davolio" O Vendedor = "Buchanan")

Inserte al menos tres filas vacías sobre el rango que puede utilizarse como rango de criterios. El rango de criterios debe tener rótulos de columna. Compruebe que existe al menos una fila vacía entre los valores de criterios y el rango.

Posiblemente sea más sencillo comprender el ejemplo si lo copia en una hoja de cálculo en blanco.

1

A

B

C

Tipo

Vendedor

Ventas

Tipo

Vendedor

Ventas

Bebidas

Suyama

5122 $

Carnes

Davolio

450 $

Alimentos

Buchanan

6328 $

Alimentos

Davolio

6544 $

2 3 4 5 6 7 8 9 10

1.

Para buscar filas que cumplan varios criterios para una columna, escriba los criterios directamente debajo de cada uno en filas independientes del rango de criterios. En el ejemplo, escribiría:

55

ING. JACP

1

A

B

C

Tipo

Vendedor

Ventas

2

="=Davolio"

3

="=Buchanan"

1.

Haga clic en una celda del rango. En el ejemplo, haría clic en cualquier celda del rango, A6:C10.

2.

En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Opciones avanzadas.

Siga uno de los siguientes procedimientos: 1. 2.

Para filtrar el rango ocultando las filas que no cumplen los criterios, haga clic en Filtrar la lista sin moverla a otro lugar. Para filtrarlo copiando las filas que cumplen los criterios a otra área de la hoja de cálculo, haga clic enCopiar 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.

SUGERENCIA Al copiar filas filtradas a otra ubicación, se pueden especificar las columnas que deben incluirse en la operación de copia. Antes de filtrar, copie los rótulos de columna de las columnas deseadas a la primera fila del área donde va a pegar las filas filtradas. Cuando filtre, escriba una referencia a los rótulos de columna copiados en el cuadro Copiar a. De este modo, las filas copiadas incluirán sólo las columnas cuyos rótulos se hayan copiado. 1.

En el cuadro Rango de criterios, escriba la referencia, incluidos los rótulos de criterios. En el ejemplo, escribiría $A$1:$C$3.

2.

Para ocultar temporalmente el cuadro de diálogo Filtro avanzado mientras selecciona el rango de criterios, haga clic en Contraer diálogo

3.

.

En este ejemplo, el resultado filtrado para el rango de datos sería: A

B

C

6

Tipo

Vendedor

Ventas

8

Carnes

Davolio

450 $

9

Alimentos

Buchanan

6328 $

10

Alimentos

Davolio

6544 $

Filtrar utilizando varios criterios en varias columnas en las que deben cumplirse todos los criterios

56

ING. JACP

Lógica booleana:

(Tipo = "Alimentos" Y Ventas > 1000)

Inserte al menos tres filas vacías sobre el rango que puede utilizarse como rango de criterios. El rango de criterios debe tener rótulos de columna. Compruebe que existe al menos una fila vacía entre los valores de criterios y el rango. Posiblemente sea más sencillo comprender el ejemplo si lo copia en una hoja de cálculo en blanco.

1

A

B

C

Tipo

Vendedor

Ventas

Tipo

Vendedor

Ventas

Bebidas

Suyama

5122 $

Carnes

Davolio

450 $

Alimentos

Buchanan

6328 $

Alimentos

Davolio

6544 $

2 3 4 5 6 7 8 9 10

Para buscar las filas que cumplen varios criterios en varias columnas, escriba todos los criterios en la misma fila del rango de criterios. En el ejemplo, escribiría:

A

B

C

1

Tipo

Vendedor

Ventas

2

="=Alimentos"

>1000

Haga clic en una celda del rango. En el ejemplo, haría clic en cualquier celda del rango, A6:C10.

57

ING. JACP

En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Opciones avanzadas. Siga uno de los siguientes procedimientos: 1. 2.

Para filtrar el rango ocultando las filas que no cumplen los criterios, haga clic en Filtrar la lista sin moverla a otro lugar. Para filtrarlo copiando las filas que cumplen los criterios a otra área de la hoja de cálculo, haga clic enCopiar 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.

SUGERENCIA Al copiar filas filtradas a otra ubicación, se pueden especificar las columnas que deben incluirse en la operación de copia. Antes de filtrar, copie los rótulos de columna de las columnas deseadas a la primera fila del área donde va a pegar las filas filtradas. Cuando filtre, escriba una referencia a los rótulos de columna copiados en el cuadro Copiar a. De este modo, las filas copiadas incluirán sólo las columnas cuyos rótulos se hayan copiado. 1.

En el cuadro Rango de criterios, escriba la referencia, incluidos los rótulos de criterios. En el ejemplo, escribiría $A$1:$C$2.

2.

Para ocultar temporalmente el cuadro de diálogo Filtro avanzado mientras selecciona el rango de criterios, haga clic en Contraer diálogo

3.

.

En este ejemplo, el resultado filtrado para el rango de datos sería: A

B

C

6

Tipo

Vendedor

Ventas

9

Alimentos

Buchanan

6328 $

10

Alimentos

Davolio

6544 $

Subtotales Con Excel es posible calcular subtotales de los diferentes elementos o componentes de una base de datos, de manera tal que podemos agrupar elementos para así identificar la suma de sus valores. Supongamos un listado de vendedores, de productos y sus respectivas ventas. Podemos agrupar por vendedores y por productos, de manera que Excel puede calcular cuánto vendió cada trabajador, ya sea de un producto determinado o de todos los productos. Igualmente agrupar por productos de tal forma que Excel pueda calcular cuánto se vendió de cada producto y cuánto vendió cada empleado.

Supongamos la siguiente tabla: Vendedor

Producto

Venta

Juan Carlos

Producto 3

2,187,000

Pedro

Producto 1

3,050,000

Alirio

Producto 1

1,180,000

58

ING. JACP

Pedro

Producto 3

1,450,000

Alirio

Producto 2

2,789,000

Juan Carlos

Producto 1

3,200,000

Pedro

Producto 2

2,100,000

Alirio

Producto 1

1,956,000

El primer paso es ordenar los datos según el criterio que queremos subtotalizar. Si queremos agrupar y determinar los subtotales de los vendedores ordenaremos la columna de los vendedores, o la de productos si es la que queremos agrupar y subtotalizar. En nuestro caso queremos averiguar las ventas de cada empleado, por lo que ordenaremos de menor a mayor la columna de los vendedores. Una vez ordenados los datos, seleccionamos nuestra tabla y en la opción “Datos de la barra de tarea de Excel, ubicamos la opción “Subtotal”: Allí seleccionamos la opción Vendedor y Ventas como aparece en la siguiente imagen:

Si quisiéramos agrupar y subtotalizar los productos, ordenamos la columna de producto y en las opciones de subtotales en Excel aparecerán marcadas por defecto las opciones productos y ventas, y tendríamos lo siguiente:

59

ING. JACP

Este un ejemplo que muestra la bondades de los subtotales en Excel, ya que nos presenta un esquema gráfico de fácil comprensión. Los subtotales también se pueden lograr con tablas dinámicas y con funciones como SUMAR.SI o BDSUMA.

Funciones Base de Datos en Excel Escribiendo las notas sobre Filtro Avanzado me acordé de un tema que parece haber caído en desuso en Excel: las funciones Base de Datos (Database Functions, DBase Functions). Estas funciones permiten analizar y realizar cálculos en base a listas. En Excel "lista" es una tabla de datos que en su primera fila tiene encabezamientos.

Tal vez uno de los motivos de la pérdida de popularidad de estas funciones sea la alternativa de usar tablas dinámicas (pivot tables) y fórmulas matriciales (array formulas). Sin embargo en la mayoría de los casos, las funciones base de datos son preferibles a las fórmulas matriciales. Las funciones matriciales son muy poderosas pero tienen un gran inconveniente: producen problemas de recálculo. El uso intensivo de fórmulas matriciales hace que los archivos tarden en recalcularse. En este terreno, las funciones base de datos son la mejor alternativa. Las funciones base de datos comienzan todas con BD y tienen una sintaxis en común: Función BD(base_de_datos, campo ,criterios). El argumento base_de_datos es la tabla/lista que contiene los datos; campo es la columna sobre cuyos datos queremos realizar el cálculo y criterios es el rango que contiene los criterios para filtrar la base de datos. Existen 13 funciones base de datos (XL2003), entre ellas BSUMA, BCONTAR y BPROMEDIO. Consideremos esta lista

60

ING. JACP

El rango A5:E20 está asociado al nombre "alumnos"

Si queremos calcular la cantidad de alumnos que cumplen dos condiciones simultáneamente: han recibido por lo menos 80 puntos en matemática y por lo menos 75 puntos en historia, hacemos lo siguiente

61

ING. JACP Nótese que en la primer fila de la hoja hemos replicado los encabezamientos de las columnas de la tabla de datos.

En la celda E22 hemos puesto la fórmula =BDCONTARA(alumnos;1;B1:D2) donde: alumnos =Hoja1!$A$5:$E$20 es el rango de la tabla de datos 1 indica que queremos contar las miembros de la primer columna de la tabla B1:D2 es el rango de criterios con los cuales queremos

"filtrar" la tabla En lugar de usar el número de columna, podemos usar el encabezamiento, con lo cual la fórmula se vuelve totalmente legible =BDCONTARA(alumnos;"nombre";B1:D2) Al poner las definiciones de los criterios en la misma fila, estamos indicando el uso del operador Y, es decir, todas las condiciones deben cumplirse simultáneamente. Para usar el operador O, ponemos las condiciones en filas distintas. Por ejemplo, si queremos contar cuantos alumnos hay en la lista que recibieron por lo menos 80 puntos en matemática o 75 en historia, arreglamos la hoja de la siguiente manera

Por supuesto, también hemos modificado el rango de los criterios en la fórmula para que incluya todas las filas

=BDCONTARA(alumnos;"nombre";B1:C3) También podemos combinar los operadores Y y O en el rango de criterios. Por ejemplo, si queremos saber cuantos alumnos hay que recibieron por lo menos 80 puntos en matemática Y 75 en historia O más de 85 de promedio, usamos el modelo

62

ING. JACP

También aquí hemos modificado la fórmula para que incluya todos los criterios =BDCONTARA(alumnos;"nombre";B1:E3) Las funciones base de datos son fáciles de usar, una vez que hemos entendido la sintaxis básica. Son muy flexibles y permiten hacer cálculos que con más facilidad y velocidad que las funciones matriciales La lista completa de funciones base de datos es: BDPROMEDIO Devuelve el promedio de las entradas seleccionadas de la base de datos BDCONTAR Cuenta las celdas que contienen números en una base de datos BDCONTARA Cuenta las celdas que no están en blanco en una base de datos BDEXTRAER Extrae de la base de datos un único registro que coincida con los criterios especificados BDMAX Devuelve el valor máximo de las entradas seleccionadas de la base de datos BDMIN Devuelve el valor mínimo de las entradas seleccionadas de la base de datos

Visualización de datos con graficas Un gráfico en Excel es una representación de valores numéricos que mejora la comprensión de los datos que se encuentran en nuestra hoja. Los gráficos son una excelente herramienta para resumir la información e identificar fácilmente cualquier tendencia en los datos. ¿Qué es un gráfico de Excel? Los gráficos en Excel 2013 son objetos que podemos crear en el momento que lo necesitemos y que representan visualmente una o varias series de datos numéricos. Dependiendo el tipo de

63

ING. JACP gráfico que utilicemos será la apariencia de cada una de las series. En la siguiente imagen puedes observar un gráfico de líneas con dos series de datos:

La información de cada serie está almacenada en una columna diferente. La línea azul representa los datos de la columna Ingresos y la línea roja los datos de la columna Egresos. Además, cada pequeño círculo sobre la línea representa el valor de cada una de las celdas en los datos. Es así como un gráfico está vinculado a la información contenida en una hoja de Excel y si modificamos los datos, entonces el gráfico se actualizará automáticamente para reflejar los cambios. Pasos para crear un gráfico en Excel Crear un gráfico en Excel es tan sencillo como seguir los siguientes dos pasos: 1.

2.

Selecciona una celda que pertenezca al rango donde se encuentran los valores numéricos. Nota que he mencionado que es suficiente hacer clic sobre una sola celda y no es necesario seleccionar todo el rango porque Excel incluirá automáticamente los datos de las celdas adyacentes. Una vez hecha la selección, ve a la ficha Insertar > Gráficos y haz clic sobre el botón del tipo de gráfico que deseas insertar y se mostrará un menú donde deberás seleccionar el gráfico deseado.

Por ejemplo, para crear el gráfico de líneas mostrado en la sección anterior pulsé el botón Gráfico de líneas y posteriormente seleccioné la opción Línea con marcadores tal como lo ves en la siguiente imagen:

64

ING. JACP

Con estos simples pasos habrás aprendido cómo hacer gráficos en Excel 2013. Tal vez la parte más complicada del proceso es saber elegir el gráfico adecuado para nuestros datos pero si te encuentras en esta situación, Excel 2013 tiene una nueva funcionalidad que nos ayudará en el proceso de selección de un gráfico.

Gráficos recomendados en Excel 2013 Cuando tienes duda sobre el gráfico que debes elegir para tus datos, puedes hacer uso del comando Gráficos recomendados de Excel 2013 el cual hará un análisis rápido de los datos para hacer una recomendación. Para utilizar este comando debes hacer clic sobre una celda dentro del rango de datos y posteriormente ir a Insertar > Gráficos > Gráficos recomendados y se mostrará un cuadro de diálogo con las recomendaciones de Excel:

65

ING. JACP

En el panel izquierdo tendrás el listado de los gráficos recomendados y al seleccionar alguno de ello se mostrará una vista previa en el panel derecho. Una vez que hagas tu elección deberás pulsar el botón Aceptar para crear efectivamente el gráfico. Selección de datos para crear un gráfico En los ejemplos anteriores he mencionado que es suficiente con seleccionar una sola celda del rango de datos que deseamos graficar lo cual funciona perfectamente, siempre y cuando deseamos graficar todos los datos de las celdas adyacentes. Si nuestros datos se componen de varias columnas de las cuales solo queremos graficar una sola de ellas, entonces será necesario hacer la selección explícita de todos los datos a graficar. En la siguiente imagen puedes notar que he hecho la selección solo de la columna Mes y de la columna Ingresos:

66

ING. JACP

Después de hacer la selección anterior pulsaré el comando Insertar > Gráficos > Gráfico de líneas > Línea con marcadores y el resultado será el siguiente:

Como puedes observar solamente se ha graficado la columna Ingresos que fue la selección realizada previamente. Es importante mencionar que la primera columna será considerada como las categorías de los datos siempre y cuando dicha columna tenga datos de tipo texto. Estas categorías las puedes ver reflejadas en las etiquetas del eje horizontal. Si en lugar de graficar la columna Ingresos queremos graficar la columna Egresos, entonces debemos seleccionar ambas columnas utilizando la tecla Ctrl la cual nos permite elegir múltiples rangos que no son adyacentes. Después de seleccionar la primera columna pulsa la tecla Ctrl y haz la selección de la segunda columna:

67

ING. JACP El resultado de la selección de datos anterior es una gráfica de la columna Egresos:

Es así como la selección de datos adecuada nos permite graficar la información deseada. Recuerda que si deseas graficar todo el rango de datos entonces es suficiente con seleccionar una sola celda dentro del rango.

Ficha Herramientas de gráfico Una vez que hemos creado un gráfico y hacemos clic sobre él se mostrará una ficha contextual llamada Herramientas de gráfico la cual contendrá comandos específicos para trabajar con los gráficos creados.

Los comandos están agrupados en las fichas Diseño y Formato que contienen comandos para cambiar el estilo del gráfico y su diseño así como el comando necesario para cambiar el tipo de gráfico entre otros comandos más que utilizaremos en lecciones posteriores. Como cualquier otra ficha contextual, al momento de remover la selección del gráfico, la ficha Herramientas de gráfico se ocultará.

Crear hojas de gráficos en Excel De manera predeterminada los gráficos son colocados dentro de la hoja donde se encuentran los datos y decimos que son gráficos incrustados los cuales flotan sobre la hoja. Pero existe otra alternativa para los gráficos de Excel y es que podemos colocarlos en su propia hoja donde no existirá nada más que el gráfico mismo. Una razón para crear una hoja de gráfico es porque

68

ING. JACP deseamos imprimir los gráficos de manera independiente y ocupando la totalidad de la hoja. Además, si tenemos múltiples gráficos en nuestro libro será buena idea colocar un gráfico en una hoja diferente para ubicarlos rápidamente. Para crear una hoja de gráfico debes seleccionar el gráfico deseado y posteriormente ir a Herramientas de gráficos > Diseño > Ubicación > Mover gráfico. Al pulsar este botón se mostrará un cuadro de diálogo que nos permitirá crear una nueva hoja de gráfico permitiéndonos colocar el nombre que deseemos:

Al pulsar el botón Aceptar notarás que se inserta una nueva hoja en el libro y lo único que existirá en dicha hoja será el gráfico de Excel. Si quieres regresar al gráfico incrustado en la hoja debes seguir el procedimiento inverso seleccionando de nuevo el comando Mover gráfico pero en esta ocasión seleccionando la opción Objeto en Hoja. Aprender cómo hacer gráficos en Excel es de suma importancia porque los gráficos nos ayudan a comprender de una mejor manera los datos contenidos en las celdas y darles el significado e interpretación correctos.

Análisis de datos Minigráficos en Excel 2013 Los minigráficos de Excel son gráficos muy pequeños que caben dentro de una celda y nos permiten mostrar el comportamiento de los datos a través del tiempo o representar las variaciones que existen en la información. Es importante saber que, a diferencia de los gráficos de Excel, los minigráficos solo pueden representar una serie de datos a la vez. Los comandos que nos permiten crear los minigráficos se encuentran en la ficha Insertar y dentro del grupo Minigráficos.

Tipos de minigráficos en Excel Existen tres tipos diferentes de minigráficos en Excel 2013 y son los siguientes: 1. 2. 3.

Línea: Similar a un gráfico de línea Columna: Similar al gráfico de columnas. Ganancia o pérdida: Este tipo de minigráfico no tiene parecido con ningún gráfico de Excel ya que solo representa dos tipos de valores: ganancias o pérdidas. Un cambio

69

ING. JACP positivo en los datos será representado como ganancia mientras que un cambio negativo como pérdida. En la siguiente imagen puedes observar cada uno de los tipos de minigráficos en el mismo orden en que fueron mencionados:

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

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

70

ING. JACP es seleccionando uno de ellos y Excel colocará un borde y un color de fondo especial sobre todos los minigráficos del mismo grupo.

Una ventaja de tener los minigráficos agrupados es al momento de editarlos ya que cualquier cambio en el estilo será aplicado de manera automática a todo el grupo. Sin embargo, si quieres desagrupar los minigráficos para tratarlos de manera individual, puedes hacerlo con el comando Herramientas para minigráfico > Diseño > Agrupar > Desagrupar. Si después de crear los minigráficos decides que no son del tipo adecuado, no es necesario eliminarlos y volver a crearlos, sino simplemente cambiar su tipo. Este cambio lo podemos hacer seleccionando el grupo de minigráficos y en Herramientas para minigráfico, dentro del grupo Tipo, elegir el nuevo tipo.

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

71

ING. JACP Además de poder elegir un nuevo estilo, también podemos mostrar puntos específicos dentro del minigráfico como los siguientes: 1. 2. 3. 4. 5. 6.

Punto alto: Aplica un color diferente al dato de mayor magnitud. Punto bajo: Aplica un color diferente al dato de menor magnitud. Puntos negativos: Aplica un color diferente a todos los valores negativos. Primer punto: Resalta el primer punto de la serie. Último punto: Resalta el último punto de la serie. Marcadores: Sólo disponible para los minigráficos de línea que coloca marcadores para todos los puntos de la serie.

Cada uno de estos puntos puede mostrase u ocultarse a través de las cajas de selección ubicadas en la ficha Herramientas para minigráfico. Un tema importante de mencionar al hablar de la edición de un minigráfico es la modificación de su tamaño. La manera de aumentar o disminuir el tamaño de un minigráfico es modificando el ancho o el alto de la celda a la que pertenece ya que el minigráfico se ajustará para ocupar la totalidad de la celda. Por otro lado, ya que es posible colocar un minigráfico dentro de celdas combinadas, podemos aumentar el tamaño de un minigráfico combinando varias celdas evitando así la modificación del ancho o alto de celdas individuales. Un minigráfico no puede ser eliminado con solo seleccionarlo y pulsando la tecla Suprimir. Es necesario seleccionarlo e ir a la ficha Herramientas de minigráfico y pulsar el botón Borrar.

El botón Borrar nos permite eliminar solo el minigráfico seleccionado, que es la opción predeterminada, o también podemos borrar todo el grupo de minigráficos. Estas mismas opciones de borrado las encontramos al hacer clic derecho sobre un minigráfico y seleccionando la opción de menú Minigráficos.

Escenarios Los escenarios nos permiten analizar un resultado generado en base a un conjunto de celdas variables. Los escenarios en Excel permiten un máximo de 32 variables, pero podemos crear tantos escenarios como sea necesario. Para este ejemplo utilizaré el caso de un préstamo

72

ING. JACP personal en donde me interesa conocer la cantidad que debo pagar dependiendo el plazo elegido. Para ello utilizo la función PAGO y en las celdas superiores he colocado los argumentos de dicha función de la siguiente manera:

Ahora me interesa saber cómo cambia la mensualidad si cambio el plazo de pago. Para iniciar con la creación de escenarios debo pulsar el botón Análisis Y si que se encuentra en la ficha Datos y dentro de las opciones mostradas seleccionar Administrador de escenarios.

Al seleccionar esta opción se mostrará el cuadro de diálogo Administrador de escenarios y lo primero que debemos hacer es pulsar el botón Agregar para mostrar el cuadro de diálogo Agregar escenario.

73

ING. JACP

En este cuadro de diálogo comenzaré por asignar un nombre a mi escenario, que en este caso será 12 Meses. En el cuadro de texto Celdas cambiantes debo seleccionar aquellas celdas que afectan el resultado de la fórmula PAGO, que es el rango $B$1:$B:$3. Al oprimir el botón Aceptar se mostrará un nuevo cuadro de diálogo llamado Valores del escenario que me permitirá ingresar los valores específicos del escenario recién creado. Es posible cambiar todos los valores, pero para este ejemplo solamente modificaré el plazo que tendrá el valor 12:

Ya que voy a agregar escenarios adicionales oprimiré el botón Agregar y Excel mostrará de nueva cuenta el cuadro de diálogo Agregar escenario y volveré a repetir estos mismos pasos para crear nuevos escenarios para los plazos de 18, 36 y 48 meses. Una vez que he terminado de crear el último escenario debes pulsar el botón Aceptar para regresar al Administrador de escenariosdonde podrás ver una lista de todos los escenarios creados:

74

ING. JACP

Ya con los escenarios creados será suficiente seleccionar alguno de ellos y oprimir el botón Mostrar para aplicar los valores del escenario al resultado de la fórmula PAGO. Observa cómo va cambiando el resultado de la celda B4 de acuerdo al escenario elegido:

75

ING. JACP

Para este ejemplo solamente he modificado los valores del plazo en cada escenario, sin embargo se podrían crear escenarios donde cambie tanto el plazo como la tasa de interés. Las combinaciones posibles son muchas y solamente estarán restringidas a tus necesidades de análisis. Una desventaja de los escenarios es que tienes que crear manualmente cada uno de ellos y podría llegar a ser un trabajo muy laborioso. En caso de que tuvieras que realizar un análisis de dos variables te recomiendo considerar la opción de Tablas de datos ya que evitará la creación manual de escenarios.

Buscar objetivo

76

ING. JACP En la mayoría de las hojas de cálculo comenzamos introduciendo datos, para luego realizar cálculos con ellos. Esos cálculos dan lugar a los resultados. En algunas ocasiones, este proceso se invierte: conocemos el objetivo pero queremos llegar a la fuente o fuentes originales de ese cálculo. Un ejemplo bien claro lo podemos encontrar en el departamento comercial de una empresa, cuando al comenzar el año, se fijan los objetivos a conseguir (conocemos el beneficio que deseamos obtener y necesitamos calcular las ventas que deben hacer los comerciales para conseguir ese beneficio). Otro ejemplo lo encontramos en el mundo financiero: Deseamos obtener una cantidad de rentabilidad y necesitamos calcular capitales, intereses o tiempos para conseguirla. Buscar objetivo es la herramienta ideal para resolver las cuestiones anteriores. Para utilizarla debemos tener en cuenta los siguientes aspectos: Se busca un solo resultado en una celda y esta celda contiene una fórmula. Solamente existe una variable de celda. Esta variable es el dato que Excel ajusta para poder ofrecer una solución. Existe una solución válida: Si deseamos calcular los años en los que pagaremos 100.000 € a razón de 0,25 € al mes, Excel termina encontrando una respuesta (será un número negativo de años, ya que el interés que debemos abonar será mayor al de la cuota mensual). Para comprender mejor el funcionamiento de Buscar objetivo, pensemos en el siguiente ejemplo:

Contamos con una hoja de cálculo como la del dibujo. En ella aparecen los alumnos de un curso, la puntuación de 2 exámenes y la puntuación de un trabajo práctico que deben realizar. La columna de la derecha muestra el porcentaje del curso superado. Yolanda Muñoz desea saber qué puntuación debe obtener para superar el curso en un 80% sabiendo que el trabajo práctico supone el 50% de la nota final. Para utilizar Buscar objetivo en este escenario debemos seguir los siguientes pasos: Seleccionamos la ficha Datos, el grupo Herramientas de datos, Análisis Y si, Buscar objetivo. Se abrirá el siguiente cuadro de diálogo:

77

ING. JACP

En el cuadro de texto Definir la celda, introducimos la celda donde introduciremos el objetivo. En el cuadro de texto Con el valor, introduciremos el valor al que queremos llegar. En este caso es 80% ó 0,80. En el cuadro de texto Para cambiar celda, debemos seleccionar la celda donde irá el dato que queremos calcular. En nuestro caso se trata de la celda D3. Pulsamos en Aceptar. El cuadro de diálogo Estado de la búsqueda de objetivo aparece en pantalla y Excel comienza a probar series de valores, incrementando el valor de Trabajo práctico y calculando el resultado. Si Excel no logra encontrar una respuesta después de una serie de intentos, El cuadro de diálogo Estado de la búsqueda de objetivo nos dará la opción de seguir intentándolo o detener la operación. Si Excel se atasca en un largo proceso, podemos pulsar el botón Pausa, aunque raramente se da este caso. En la mayoría de las ocasiones la operación suele terminar en el acto. En un instante, Excel averigua que una puntuación de 8,84 en el Trabajo práctico, hará que Yolanda Muñoz obtenga un 80% del curso superado. Cuando aparezca una respuesta, pulsamos Aceptar para confirmar el resultado o Cancelar para volver a los datos originales.

Importar o exportar archivos de 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 (rango de datos externos: rango de datos que se incorpora a una hoja de cálculo pero que se origina fuera de Excel, como en una base de datos o un archivo de texto. En Excel, puede dar formato a los datos o utilizarlos en cálculos como haría con otros datos.). Para exportar datos de Excel a un archivo de texto, use el comando Guardar como. Son dos los formatos de archivo de texto que se usan habitualmente: 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.

78

ING. JACP 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. Notas Puede importar o exportar hasta 1.048.576 filas y 16.384 columnas.

Importar un archivo de texto abriéndolo en Excel 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). Haga clic en la pestaña Archivo Aparece el cuadro de diálogo Abrir.

y,

a

continuación,

haga

clic

en

Abrir.

En la lista, seleccione Archivos de texto. Busque el archivo de texto que desee abrir y haga doble clic en él. Si el archivo es un archivo de texto (.txt), Excel inicia el Asistente para importación de texto. Haga clic en Ayuda en cualquier página del Asistente para importación de texto para obtener más información sobre el uso del asistente. Cuando termine de realizar los pasos del Asistente, haga clic en Finalizar para completar la operación de importación. Si el archivo es un archivo .csv, Excel abre automáticamente el archivo de texto y muestra los datos en un libro nuevo. Nota Cuando Excel abre un archivo .csv, usa la configuración de formato de datos predeterminada actual para interpretar el modo en que debe importar cada columna de datos. Puede usar el Asistente para importación de texto si desea disponer de mayor flexibilidad para convertir las columnas a los distintos formatos de datos. Por ejemplo, es posible que el formato de una columna de datos del archivo .csv sea MDA, pero el formato de datos predeterminado de Excel sea AMD, o puede que desee convertir en texto una columna de números precedida de ceros para conservar dichos ceros. Para forzar a Excel que ejecute el Asistente para importación de texto, puede cambiar la extensión .csv del nombre del archivo a .txt antes de abrirlo, o bien puede importar un archivo de texto conectándose a é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 (rango de datos externos: rango de datos que se incorpora a una hoja de cálculo

79

ING. JACP pero que se origina fuera de Excel, como en una base de datos o un archivo de texto. En Excel, puede dar formato a los datos o utilizarlos en cálculos como haría con otros datos.). Haga clic en la celda en la que desea colocar los datos del archivo de texto. En la ficha Datos, en el grupo Obtener datos externos, haga clic en Desde texto.

Busque el archivo de texto que desee importar y haga doble clic en él. Siga las instrucciones del Asistente para importación de texto. Haga clic en Ayuda en cualquier página del Asistente para importación de texto para obtener más información sobre el uso del asistente. Cuando termine de realizar los pasos del Asistente, haga clic en Finalizar para completar la operación de importación. En el cuadro de diálogo Importar datos, haga lo siguiente: Si lo desea, haga clic en Propiedades para establecer las opciones de actualización, formato y diseño de los datos importados. En ¿Dónde desea situar los datos?, realice uno de estos procedimientos: Para devolver los datos en la ubicación seleccionada, haga clic en Hoja de cálculo existente. Para devolver los datos en la esquina superior izquierda de una nueva hoja de cálculo, haga clic en Hoja de cálculo nueva. Haga clic en Aceptar. Excel coloca el rango de datos externos en la ubicación especificada. Si Excel no convierte una columna de datos al formato que desea, puede convertir los datos después de importarlos. Para obtener más información, vea los temas sobre cómo convertir números almacenados como texto en números y cómo convertir fechas almacenadas como texto en fechas.

Exportar datos a un archivo de texto guardándolo Puede convertir una hoja de cálculo de Excel en un archivo de texto con el comando Guardar como. Haga clic en la pestaña Archivo y, a continuación, haga clic en Guardar como.

80

ING. JACP Se abrirá el cuadro de diálogo Guardar como. En el cuadro Guardar como tipo, elija el formato de archivo de texto para la hoja de cálculo. Por ejemplo, haga clic en Texto (delimitado por tabulaciones) o CSV (delimitado por comas). Nota Cada formato admite conjuntos de características diferentes. Para obtener más información sobre los conjuntos de características admitidos por los diferentes formatos de archivo de texto, vea el tema sobre los formatos de archivo admitidos en Excel. Busque la ubicación en la que desee guardar el nuevo archivo de texto y, a continuación, haga clic en Guardar. Aparecerá un cuadro de diálogo en el que se informa de que solo la hoja de cálculo actual se va a guardar en el nuevo archivo. Si está seguro de que la hoja de cálculo actual es la que desea guardar como archivo de texto, haga clic en Aceptar. Puede guardar otras hojas de cálculo como archivos de texto distintos repitiendo este procedimiento para cada hoja. Aparecerá un segundo cuadro de diálogo en el que se indica que la hoja de cálculo puede contener características no compatibles con los formatos de archivo de texto. Si solo está interesado en guardar los datos de la hoja de cálculo en el nuevo archivo de texto, haga clic en Sí. Si no está seguro y desea obtener más información sobre las características de Excel incompatibles con los formatos de archivo de texto, haga clic en Ayuda para obtener información. Para obtener más información sobre cómo guardar archivos con otros formatos, vea el tema sobre cómo guardar un libro con otro formato de archivo.

Cambiar el delimitador que se usa en un archivo de texto Si usa el Asistente para importar texto para importar un archivo de texto, puede cambiar el delimitador usado por un archivo de texto delimitado de un carácter de tabulación a cualquier otro carácter en el paso 2 del Asistente para importar texto. En este paso, puede cambiar también el modo en que se tratan los delimitadores consecutivos, como las comillas consecutivas. Para obtener más información sobre el uso del Asistente para importación de texto, vea Importar un archivo de texto abriéndolo en Excel.

Cambiar el separador de todos los archivos de texto .csv En Microsoft Windows, haga clic en el botón Inicio y, a continuación, haga clic en Panel de control. Abra el cuadro de diálogo para cambiar la configuración regional y de idioma. Escriba un nuevo separador en el cuadro Separador de listas. Haga dos veces clic en Aceptar.

81

ING. JACP Nota Una vez cambiado el carácter separador de listas del equipo, todos los programas usarán el nuevo carácter como separador de listas. Puede volver a cambiar al carácter predeterminado

mediante el mismo procedimiento.

Auditoria De Hojas De Cálculo

Mostrar las relaciones entre las fórmulas y las celdas A veces, la comprobación de las fórmulas para determinar la precisión o encontrar el origen de un error puede resultar difícil cuando la fórmula usa celdas precedentes o dependientes: celdas precedentes Las celdas precedentes son celdas a las que se hace referencia mediante una fórmula en otra celda. Por ejemplo, si la celda D10 contiene la fórmula =B5, la celda B5 es la celda precedente a D10. celdas dependientes Las celdas dependientes contienen fórmulas que hacen referencia a otras celdas. Por ejemplo, si la celda D10 contiene la fórmula =B5, la celda D10 depende de la celda B5. Para ayudarle con la comprobación de las fórmulas, puede usar los comandos Seguimiento de precedentes y Seguimiento de dependientes para mostrar de forma gráfica o siga paso a paso las relaciones entre estas celdas y las fórmulas con flechas de seguimiento. Haga clic en la pestaña Archivo, después en Opciones y, a continuación, en la categoría Avanzadas. En la sección Mostrar opciones para este libro, seleccione el libro que desea y luego asegúrese de que la opción Todos esté seleccionada en Para objetos, mostrar:. Si las fórmulas hacen referencia a las celdas de otro libro, abra ese libro. Excel no puede ir a una celda de un libro que no está abierto. Siga uno de los procedimientos que se describen a continuación. Celdas de seguimiento que proporcionan datos a una fórmula (precedentes)

82

ING. JACP

Seleccione la celda que contiene la fórmula para la que desea buscar celdas precedentes. Para mostrar una flecha de seguimiento a cada celda que proporciona directamente datos a la celda activa, haga clic en Seguimiento de precedentes, en el grupo Auditoría de fórmula de la ficha Fórmulas . Las flechas azules muestran celdas sin errores. Las flechas rojas muestran celdas que causan errores. Si otra celda en otra hoja de cálculo o en otro libro hace referencia a la celda seleccionada, una flecha negra señala un icono de hoja de cálculo desde la celda seleccionada . El otro libro deberá estar abierto antes de que Excel pueda seguir paso a paso estas dependencias. Si el otro libro no está abierto, es posible que Excel le solicite que lo busque y lo abra. Para identificar el siguiente nivel de las celdas que proporcionan datos a la celda activa, vuelva a hacer clic en Seguimiento de precedentes

.

Para quitar las flechas de seguimiento de un nivel cada vez, empezando por la celda precedente más alejada de la celda activa, en el grupo Auditoría de fórmulas de la ficha Fórmulas, haga clic en la flecha que se encuentra junto a Quitar flechas y, a continuación, en Quitar un nivel de precedentes seguimiento, haga clic nuevamente en el botón.

. Para quitar otro nivel de flechas de

Fórmulas de seguimiento que hacen referencia a una celda en particular (dependientes) Seleccione la celda para la que desea identificar las celdas dependientes. Para mostrar una flecha de seguimiento a cada celda dependiente de la celda activa, en la ficha Fórmulas en el grupo Auditoría de fórmulas, haga clic en Seguimiento de dependientes

.

Las flechas azules muestran celdas sin errores. Las flechas rojas muestran celdas que causan errores. Si otra celda en otra hoja de cálculo o en otro libro hace referencia a la celda seleccionada, una flecha negra señala un icono de hoja de cálculo desde la celda seleccionada . El otro libro deberá estar abierto antes de que Excel pueda seguir paso a paso estas dependencias. Si el otro libro no está abierto, es posible que Excel le solicite que lo busque y lo abra. Para quitar las flechas de seguimiento de un nivel cada vez, empezando por la celda dependiente más alejada de la celda activa, en el grupo Auditoría de fórmulas de la ficha Fórmulas, haga clic en la flecha que se encuentra junto a Quitar flechas y luego en Quitar un nivel de dependientes haga clic nuevamente en el botón.

. Para quitar otro nivel de flechas de seguimiento,

83

ING. JACP

Ver todas las relaciones en una hoja de cálculo En una celda vacía, escriba = (signo igual). Haga clic en el botón Seleccionar todo.

Seleccione la celda y en el grupo Auditoría de fórmulas de la ficha Fórmulas, haga doble clic en Seguimiento de precedentes

.

Problema: Excel emite un pitido cuando hago clic en el comando Seguimiento de dependientes o en el comando Seguimiento de precedentes. Si Excel emite un pitido cuando hace clic en Seguimiento de dependientes o en Seguimiento de precedentes, Excel ya ha realizado un seguimiento en todos los niveles de la fórmula o se está intentando realizar un seguimiento de un elemento que no se puede seguir. Los siguientes elementos en las hojas de cálculo a los que se puede hacer referencia mediante fórmulas no se pueden seguir con las herramientas de auditoría: Referencias a los cuadros de texto, gráficos incrustados o imágenes en las hojas de cálculo Referencias a constantes con nombre Fórmulas ubicadas en otro libro que hacen referencia a la celda activa si el otro libro se cierra Para quitar todas las flechas de seguimiento en la hoja de cálculo, en el grupo Auditoría de fórmulas de la ficha Fórmulas, haga clic en Quitar flechas

.

NOTAS Para ver las celdas precedentes codificadas por colores para los argumentos en una fórmula, seleccione una celda y presione F2. Para seleccionar la celda en el otro extremo de una flecha, haga doble clic en la flecha. Si la celda está en otra hoja de cálculo u otro libro, haga doble clic en la flecha negra para mostrar el cuadro de diálogo Ir a y luego haga doble clic en la referencia que desee en la lista Ir a.

84

ING. JACP Todas las flechas de seguimiento desaparecen si cambia la fórmula a la que apuntan las flechas, inserta o elimina columnas o filas, o elimina o mueve celdas. Para restaurar las flechas de seguimiento después de realizar cualquiera de estos cambios, debe volver a usar los comandos de auditoría en la hoja de cálculo. Para realizar un seguimiento de las flechas de seguimiento originales, imprima la hoja de cálculo con las flechas de seguimiento visibles antes de aplicar los cambios.

Esquematizar (agrupar) datos en una hoja de cálculo Si tiene una lista de datos que desea agrupar y resumir, puede crear un esquema de hasta ocho niveles, uno para cada grupo. Cada nivel interno, representado por un número superior de símbolos de esquema, muestra datos de detalle del nivel externo anterior, representado por un número inferior de símbolos de esquema. Use un esquema para mostrar rápidamente filas o columnas de resumen, o bien para mostrar los datos de detalle de cada grupo. Puede crear un esquema de filas (como se ilustra en el ejemplo siguiente), un esquema de columnas o un esquema de filas y columnas.

Para mostrar filas para un nivel, haga clic en

los

símbolos

de

esquema

adecuados.

El nivel 1 contiene las ventas totales de todas las filas de detalle.

El nivel 2 contiene las ventas totales para cada mes en cada región.

El nivel 3 contiene las filas de detalle (sólo las filas de detalle 11 a 13 están actualmente Se muestra una fila de datos de ventas agrupados por regiones geográficas y meses

visibles).

con varias filas de resumen y detalles.

Para expandir o contraer datos en el esquema, haga clic en los símbolos de esquema

85

y

.

ING. JACP

Nuevas funciones 2013 Los resultados calculados de las fórmulas y algunas funciones de hoja de cálculo de Excel pueden diferir entre un PC de Windows con arquitectura x86 o x86-64 y un PC de Windows RT con arquitectura ARM. Haga clic en cualquier nombre de función en la siguiente lista para ver ayuda detallada acerca de esa función. NOMBRE DE LA FUNCIÓN

TIPO Y DESCRIPCIÓN

Función ACOT

Matemáticas y trigonometría: Devuelve la arco cotangente de un número.

Función ACOTH

Matemáticas y trigonometría: Devuelve la arcotangente hiperbólica inversa de un número.

Función NUMERO.ARABE

Matemáticas y trigonometría: Convierte un número romano en arábigo.

Función BASE

Matemáticas y trigonometría: Convierte un número en una representación de texto con la base dada.

Función DISTR.BINOM.SERIE

Estadística: Devuelve la probabilidad de un resultado de prueba siguiendo una distribución binomial.

Función BIT.Y

Ingeniería: Devuelve un Y bit a bit de dos números.

Función BIT.DESPLIZQDA

Ingeniería: Devuelve un valor numérico desplazado hacia la izquierda por los bits de cant_desplazada.

Función BITOR

Ingeniería: Devuelve un O bit a bit de dos números.

Función BITRSHIFT

Ingeniería: Devuelve un valor numérico desplazado hacia la derecha por los bits de cant_desplazada.

Función BIT.XO

Ingeniería: Devuelve un O exclusivo bit a bit de dos números.

Función CEILING.MATH

Matemáticas y trigonometría: Redondea un número hacia arriba al entero más próximo o al múltiplo significativo más cercano.

Función COMBINA

Matemáticas y trigonometría:

86

ING. JACP Devuelve la cantidad de combinaciones con repeticiones de una cantidad determinada de elementos. Función COT

Matemáticas y trigonometría: Devuelve el coseno hiperbólico de un número.

Función COTH

Matemáticas y trigonometría: Devuelve la cotangente de un ángulo.

Función CSC

Matemáticas y trigonometría: Devuelve la cosecante de un ángulo.

Función CSCH

Matemáticas y trigonometría: Devuelve la cosecante hiperbólica de un ángulo.

Función DIAS

Fecha y hora: Devuelve la cantidad de días entre dos fechas.

Función CONV.DECIMAL

Matemáticas y trigonometría: Convierte una representación de texto de un número con una base dada en un número decimal.

Función ENCODEURL

Web: Devuelve una cadena de URL codificada

Función FILTERXML

Web: Devuelve datos específicos del contenido XML usando el XPath especificado.

Función MULTIPLO.INFERIOR.MAT

Matemáticas y trigonometría: Redondea un número hacia abajo al entero más próximo o al múltiplo significativo más cercano.

Función FORMULATEXT

Búsqueda y referencia: Devuelve la fórmula en la referencia dada como texto.

Función GAMMA

Estadística: Devuelve el valor de la función Gamma.

Función GAUSS

Estadística: Devuelve un 0,5 menos que la distribución acumulativa normal estándar.

Función SI.ND

Lógica: Devuelve el valor que se especifica, si la expresión se convierte en #N/A; de lo contrario, devuelve el resultado de la expresión.

Función IM.COSH

Ingeniería: complejo.

Devuelve el coseno hiperbólico de un número

87

ING. JACP Función IMCOT

Ingeniería: Devuelve la cotangente de un número complejo.

Función IM.CSC

Ingeniería: Devuelve la cosecante de un número complejo.

Función IM.CSCH

Ingeniería: Devuelve la cosecante hiperbólica de un número complejo.

Función IM.SEC

Ingeniería: Devuelve la secante de un número complejo.

Función IM.SECH

Ingeniería: Devuelve la secante hiperbólica de un número complejo.

Función IM.SENOH

Ingeniería: Devuelve el seno hiperbólico de un número complejo.

Función IM.TAN

Ingeniería: Devuelve la tangente de un número complejo.

Función ESFORMULA

Información: Devuelve VERDADERO si existe una referencia a una celda que contiene una fórmula.

Función ISO.NUM.DE.SEMANA

Fecha y hora: Devuelve el número de semana ISO del año para una fecha determinada.

Función MUNIT

Matemáticas y trigonometría: Devuelve la matriz de la unidad o la dimensión especificada.

Función VALOR.NUMERO

Texto: Convierte texto a número de manera independiente a la configuración regional.

Función P.DURACION

Finanzas: Devuelve la cantidad de períodos necesarios para que una inversión alcance un valor especificado.

Función PERMUTACIONES.A

Estadística: Devuelve la cantidad de permutaciones de una cantidad determinada de objetos (con repeticiones) que pueden seleccionarse del total de objetos.

Función FI

Estadística: Devuelve el valor de la función de densidad para una distribución normal estándar.

Función RRI

Finanzas: Devuelve una tasa de interés equivalente para el crecimiento de una inversión.

Función SEC

Matemáticas y trigonometría: Devuelve la secante de un ángulo.

88

ING. JACP Función SECH

Matemáticas y trigonometría: Devuelve la secante hiperbólica de un ángulo.

Función HOJA

Información: Devuelve el número de la hoja a la que se hace referencia.

Función HOJAS

Información: Devuelve la cantidad de hojas en una referencia.

Función COEFICIENTE.ASIMETRIA.P

Estadística: Devuelve la asimetría de una distribución basada en una población: una caracterización del grado de asimetría de una distribución alrededor de su media.

Función UNICAR

Texto: Devuelve el carácter Unicode al que hace referencia el valor numérico dado.

Función UNICODE

Texto: Devuelve el número (punto de código) que corresponde al primer carácter del texto.

Función WEBSERVICE

Web: Devuelve datos de un servicio web.

Función XO

Lógica: Devuelve un O exclusivo lógico de todos los argumentos.

89