Florida 439 piso 4 oficina 2 CABA · Manual Excel Intermedio 4 Sección 1: Gráficas Tipos de...

Manual Intermedio GD Capacitación

www.gdc.com.ar

Florida 439 piso 4 oficina 2 CABA

Microsoft Excel

Manual Excel Intermedio www.gdc.com.ar

1

Contenido

Sección 1: Gráficas ............................................................................................................. 4

Tipos de gráficos ..................................................................................................................................................................... 4 Gráfico de columnas ...................................................................................................................................................................................... 4 Gráfico de líneas ............................................................................................................................................................................................ 5 Gráficos circulares y de anillos ...................................................................................................................................................................... 6 Gráficos de anillos ......................................................................................................................................................................................... 7 Gráfico de barras ........................................................................................................................................................................................... 8 Gráfico de área .............................................................................................................................................................................................. 9 Gráfico de burbujas y de tipo XY (Dispersión) ............................................................................................................................................. 10 Gráfico de burbujas ..................................................................................................................................................................................... 11 Gráfico de cotizaciones ............................................................................................................................................................................... 11 Gráfico de superficie ................................................................................................................................................................................... 13 Gráficos radiales .......................................................................................................................................................................................... 14 Gráfico de TreeMap (Office 2016 y versiones posteriores solo) ................................................................................................................. 14 Gráfico de proyección solar (Office 2016 y versiones posteriores solo) ..................................................................................................... 15 Gráficos de histograma (Office 2016 y versiones posteriores solo) ............................................................................................................ 15 Gráfico de cajas y bigotes (Office 2016 y versiones posteriores solo)......................................................................................................... 16 Gráficos de cascada (Office 2016 y versiones posteriores solo) .................................................................................................................. 17 Gráficos de Embudo (Office 2016 y versiones posteriores solo) ................................................................................................................. 19 Gráficos combinados (Office 2013 y versiones posteriores solo) ................................................................................................................ 20 Gráfico de mapa (Excel solo) ....................................................................................................................................................................... 21

Cambiar un tipo de gráfico .................................................................................................................................................... 22

Cambiar el formato de los elementos del gráfico .................................................................................................................. 22 Aplicar formato a gráficos con el panel de tareas formato ......................................................................................................................... 22 Aplicar formato a gráficos con la cinta de opciones .................................................................................................................................... 24

Usar mini gráficos para mostrar tendencias de datos ............................................................................................................ 24 Agregar un mini gráfico ............................................................................................................................................................................... 24 Dar formato a un mini gráfico ..................................................................................................................................................................... 26

Sección 2: Bases de Datos ................................................................................................ 28

Base de datos en Excel .......................................................................................................................................................... 28

Tips para crear una base de datos en Excel ........................................................................................................................... 28 Paso 1: Identificar las tablas que ocuparemos para organizar la información. ........................................................................................... 28 Paso 2: Determinar los campos de las tablas .............................................................................................................................................. 28 Paso 3: Identificar la llave primaria de cada tabla ....................................................................................................................................... 29 Paso 4: Identificar las relaciones entre tablas ............................................................................................................................................. 29 Paso 5: Identificar datos repetidos en tablas .............................................................................................................................................. 30 Diseño Final de la Base de Datos ................................................................................................................................................................. 32

Crear la base de datos en Excel ............................................................................................................................................. 32

Descripción general de las tablas de Excel ............................................................................................................................. 33 Fila de encabezado ...................................................................................................................................................................................... 33 Filas con bandas .......................................................................................................................................................................................... 33 Columnas calculadas ................................................................................................................................................................................... 34 Fila de totales .............................................................................................................................................................................................. 34 Controlador de tamaño ............................................................................................................................................................................... 34

Administración de datos en una tabla de Excel ..................................................................................................................... 35 Ordenar y filtrar .......................................................................................................................................................................................... 35 Datos de la tabla de formato ....................................................................................................................................................................... 35 Insertar y eliminar filas y columnas de una tabla ........................................................................................................................................ 35 Usar una columna calculada........................................................................................................................................................................ 35 Mostrar los datos de la tabla y calcular totales ........................................................................................................................................... 35


2

Usar referencias estructuradas ................................................................................................................................................................... 35 Garantizar la integridad de datos ................................................................................................................................................................ 35 Exportar a una lista de SharePoint .............................................................................................................................................................. 35

Consultar la base de datos en Excel ....................................................................................................................................... 36

Ordenar datos en un rango o tabla........................................................................................................................................ 36 Ordenar texto .............................................................................................................................................................................................. 37 Ordenar números ........................................................................................................................................................................................ 37 Ordenar fechas u horas ............................................................................................................................................................................... 38 Ordenar por más de una columna o fila ...................................................................................................................................................... 38 Ordenar por color de celda, color de fuente o icono .................................................................................................................................. 40 Ordenar por una lista personalizada ........................................................................................................................................................... 41 Ordenar distinguiendo mayúsculas de minúsculas ..................................................................................................................................... 42 Ordenar de izquierda a derecha .................................................................................................................................................................. 43 Ordenar por un valor parcial en una columna ............................................................................................................................................ 45 Ordenar un rango dentro de un rango mayor ............................................................................................................................................. 45

Filtrar utilizando criterios avanzados ..................................................................................................................................... 46 Datos de ejemplo ........................................................................................................................................................................................ 46 Operadores de comparación ....................................................................................................................................................................... 46 Utilizar el signo igual para escribir texto o un valor .................................................................................................................................... 46 Considerar la distinción entre mayúsculas y minúsculas ............................................................................................................................ 47 Utilizar nombres predefinidos ..................................................................................................................................................................... 47 Crear criterios utilizando una fórmula ........................................................................................................................................................ 47 Varios criterios, una columna, cualquier criterio verdadero ....................................................................................................................... 47 Varios criterios, varias columnas, todos los criterios verdaderos ............................................................................................................... 48 Varios criterios, varias columnas, cualquier criterio verdadero .................................................................................................................. 49 Varios conjuntos de criterios, una columna en todos los conjuntos ........................................................................................................... 50 Varios conjuntos de criterios, varias columnas en cada conjunto ............................................................................................................... 50 Criterios de carácter comodín ..................................................................................................................................................................... 51

Insertar subtotales en una lista de datos de una hoja de cálculo ........................................................................................... 52 Al insertar subtotales: ................................................................................................................................................................................. 52 Insertar subtotales ...................................................................................................................................................................................... 53 Insertar un nivel de subtotales .................................................................................................................................................................... 53 Insertar niveles anidados de subtotales ...................................................................................................................................................... 54 Cómo insertar los subtotales externos ........................................................................................................................................................ 54 Cómo insertar los subtotales anidados ....................................................................................................................................................... 55 Eliminar subtotales ...................................................................................................................................................................................... 55

Aplicar la validación de datos a celdas .................................................................................................................................. 55

Procedimiento recomendado: opciones de la lista que pueden presentar cambios .............................................................. 56 Eliminar validación de datos ....................................................................................................................................................................... 58

Agregar otros tipos de validación de datos ........................................................................................................................... 58

Restringir la entrada de datos a números enteros dentro de límites. .................................................................................... 58 Restringir la entrada de datos a un número decimal dentro de límites. ..................................................................................................... 59 Restringir la entrada de datos a una fecha dentro de un intervalo de fechas. ........................................................................................... 59 Restringir la entrada de datos a una hora dentro de un período de tiempo. ............................................................................................. 60 Restringir la entrada de datos a texto de una longitud específica. ............................................................................................................. 60 Calcular una validación según el contenido de otra celda. ......................................................................................................................... 61

Fórmulas en validación de datos ........................................................................................................................................... 62 La celda que contiene un id. de producto (C2) comienza siempre con el prefijo estándar de “ID”, y tiene al menos 10 (superior a 9) caracteres de longitud. ................................................................................................................................................................................ 62 La celda que contiene un nombre de producto (D2) solo contiene texto. .................................................................................................. 62 La celda que contiene un cumpleaños (B6) debe ser mayor que el número de años que establezca en la celda B4. ................................ 63 Todos los datos del rango de celdas A2:A10 contienen valores únicos. ..................................................................................................... 63

Sección 3: Funciones de Bases de Datos ........................................................................... 65


3

BDSUMA ............................................................................................................................................................................... 65 Sintaxis de la Función BDSUMA ................................................................................................................................................................... 65 Ejemplo de la Función BDSUMA .................................................................................................................................................................. 65 BDSUMA con Múltiples Criterios ................................................................................................................................................................. 66

BDPROMEDIO ....................................................................................................................................................................... 66 Sintaxis ........................................................................................................................................................................................................ 67 Ejemplo ....................................................................................................................................................................................................... 67

BDCONTAR ............................................................................................................................................................................ 67 Sintaxis de la función BDCONTAR ................................................................................................................................................................ 67 Ejemplo de la función BDCONTAR ............................................................................................................................................................... 68 Criterios para varias columnas .................................................................................................................................................................... 69

BDMAX ................................................................................................................................................................................. 70 Sintaxis ........................................................................................................................................................................................................ 70 Ejemplos ...................................................................................................................................................................................................... 71

BDMIN .................................................................................................................................................................................. 71 Ejemplo con la función BDMIN .................................................................................................................................................................... 71 El rango de criterios de la función BDMIN .................................................................................................................................................. 73 Varias filas de criterios en la función BDMIN .............................................................................................................................................. 75


4

Sección 1: Gráficas

Tipos de gráficos

Al crear un gráfico en una hoja de cálculo de Excel, en un documento de Word o en una presentación de PowerPoint, tiene muchas opciones. Ya sea que vaya a usar un gráfico recomendado para sus datos o uno que elegirá en la lista completa de gráficos, puede resultarle útil conocer algo más acerca de cada tipo de gráfico.

Gráfico de columnas Los datos que se organizan en columnas o filas en una hoja de cálculo se pueden trazar en un gráfico de columnas. Un gráfico de columnas muestra normalmente categorías a lo largo del eje horizontal (categoría) y valores a lo largo del eje (valor) vertical, como se muestra en este gráfico:

Tipos de gráficos de columnas

Columnas agrupadas y columnas agrupadas en 3D

Un gráfico de columnas agrupadas muestra valores en columnas 2D. Un gráfico de columnas agrupadas 3D. Un gráfico de columnas agrupadas 3D muestra columnas en formato 3D pero no usa un tercer eje de valor (eje de profundidad). Use este gráfico cuando tenga categorías que representen:

• Rangos de valores (por ejemplo, recuentos de elementos).

• Disposiciones de escala específicas (por ejemplo, una escala de Likert con entradas, como totalmente de

acuerdo, de acuerdo, neutral, en desacuerdo, totalmente en desacuerdo).

• Nombres que no se encuentran en ningún orden específico (por ejemplo, nombres de artículos, nombres

geográficos o los nombres de personas).

Columnas apiladas y columnas apiladas en 3-D Una columna apilada muestra valores en columnas apiladas 2D. Un gráfico de columnas apiladas 3D muestra las columnas apiladas en formato 3D pero no usa un eje de profundidad. Use este gráfico cuando tenga varias series de datos y desea destacar el total.


5

Columnas 100% apiladas y columnas 100% apiladas en 3D Un gráfico de columnas 100% apiladas muestra valores en columnas 2D que están apiladas para representar el 100%. Un gráfico de columnas 100% apiladas en 3D muestra las columnas en formato 3D pero no usa un eje de profundidad. Use este gráfico cuando tenga dos o más series de datos y desee destacar las participaciones de cada serie dentro de su categoría.

Columnas 3D Los gráficos de columnas 3D utilizan tres ejes que se pueden cambiar (un eje horizontal, un eje vertical y un eje de profundidad) y comparan puntos de datos en los ejes horizontal y de profundidad. Use este gráfico cuando desee comparar datos en ambas categorías y series de datos.

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

Tipos de gráficos de línea

Línea y línea con marcadores Mostrados con o sin marcadores para indicar valores de datos individuales, los gráficos de líneas pueden mostrar tendencias a lo largo del tiempo o categorías equidistantes, especialmente cuando hay muchos puntos de datos y el orden en el que se presentan es importante. Si hay muchas categorías o los valores son aproximados, use un gráfico de líneas sin marcadores.


6

Línea apilada y línea apilada con marcadores

Tanto si se muestran con o sin marcadores para indicar valores de datos individuales, los gráficos de líneas apiladas permiten mostrar la tendencia de la contribución que hace cada valor a lo largo del tiempo o categorías distribuidas de forma uniforme.

Línea 100% apilada y línea 100% apilada con marcadores

Tanto si se muestran con o sin marcadores para indicar valores de datos individuales, los gráficos de líneas apiladas permiten mostrar la tendencia del porcentaje en que contribuye cada valor a lo largo del tiempo o categorías distribuidas de forma uniforme. Si hay muchas categorías o los valores son aproximados, use un gráfico de líneas 100% apiladas sin marcadores.

Líneas 3D

Los gráficos de líneas 3D muestran cada fila o columna de datos como una cinta de opciones 3D. Un gráfico de líneas 3D tiene ejes horizontal, vertical y de profundidad que puede cambiar.

Notas: Los gráficos de línea funcionan mejor cuando tienen varias series de datos en su gráfico (si solo tiene una serie de datos, piense en usar un gráfico de dispersión en su lugar).

Gráficos de líneas apiladas suman los datos, que pueden no ser el resultado deseado. Es posible que no sea fácil de ver que las líneas apiladas, así que considere la posibilidad de usar un tipo de gráfico de línea diferente o en un gráfico de áreas apiladas en su lugar.

Gráficos circulares y de anillos Los datos que se organizan en una columna o fila de una hoja de cálculo se pueden trazar en un gráfico circular. Los gráficos circulares muestran el tamaño de los elementos de una serie de datos, en proporción a la suma de los elementos. Los puntos de datos de un gráfico circular se muestran como porcentaje de todo el gráfico circular.


7

Piense en utilizar un gráfico circular cuando: Solo haya una serie de datos.

• Ninguno de los valores es negativo.

• Casi ninguno de los valores de sus datos son valores cero.

• No tiene más de siete categorías, representando todas ellas partes de todo el gráfico circular.

Tipos de gráficos circulares

Circular y circular en 3D Los gráficos circulares muestran la contribución de cada valor a un total con un formato 2D o 3D. Puede extraer manualmente sectores de un gráfico circular para destacarlos.

Circular con subgráfico circular y circular con subgráfico de barras Los gráficos circulares con subgráfico circular o subgráfico de barras muestran gráficos circulares con valores menores que se extraen en un gráfico secundario o gráfico de barras apiladas, lo cual permite que se distingan más fácilmente.

Gráficos de anillos En un gráfico de anillos se pueden representar datos organizados únicamente en columnas o en filas de una hoja de cálculo. Al igual que un gráfico circular, un gráfico de anillos muestra la relación de las partes con un todo pero puede contener más de una serie de datos.


8

Tipos de gráficos de anillos

Anillos Los gráficos de anillos muestran datos en anillos, donde cada anillo representa una serie de datos. Si los porcentajes se muestran en etiquetas de datos, cada anillo totalizará el 100%.

Nota: Gráficos de anillos no son fáciles de leer. Puede que desee utilizar los gráficos de columnas apiladas o gráfico de barras apiladas en su lugar.

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

Piense en utilizar un gráfico de barras cuando:

• Las etiquetas de eje son largas.

• Los valores que se muestran son duraciones.

Tipos de gráficos de barras

Barra agrupada y barra agrupada en 3D Un gráfico de barras agrupadas muestra barras en formato 2D. Un gráfico de barras agrupadas en 3D muestra barras en formato 3D; no usa un eje de profundidad.

Barra apilada y barra apilada en 3D Los gráficos de barras apiladas muestran la relación de elementos individuales con el conjunto en barras 2D. Un gráfico de barras apiladas en 3D muestra barras en formato 3D; no usa un eje de profundidad.


9

Barras 100% apiladas y barras 100% apiladas en 3D Una barra 100% apilada muestra barras 2D que comparan el porcentaje con que cada valor contribuye a un total entre categorías. Un gráfico de barras apiladas en 3D muestra barras en formato 3D; no usa un eje de profundidad.

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

Tipos de gráficos de área

Áreas y áreas en 3D Ya se presenten en 2D o en 3D, los gráficos de áreas muestran la tendencia de los valores en el tiempo u otros datos de categoría. Los gráficos de áreas 3D usan tres ejes (horizontal, vertical y profundidad) que se pueden cambiar. Como norma, considere la posibilidad de usar un gráfico de líneas en lugar de un gráfico de áreas no apilado, ya que los datos de una serie pueden quedar ocultos por los de otra.

Áreas apiladas y áreas apiladas en 3D Los gráficos de áreas apiladas muestran la tendencia de la contribución de cada valor a lo largo del tiempo u otros datos de categoría en formato 2D. Un gráfico de áreas apiladas en 3D se presenta de la misma forma, pero muestra áreas en formato 3D sin usar un eje de profundidad.

https://support.office.com/es-es/article/gr%C3%A1fico-de-l%C3%ADneas-a019c053-ba7f-4c46-a09a-82e17f3ee5be#__line_charts


10

Áreas 100% apiladas y áreas 100% apiladas en 3D Los gráficos de áreas 100% apiladas muestran la tendencia del porcentaje en que cada valor contribuye a lo largo del tiempo u otros datos de categoría. Un gráfico de áreas 100% apiladas en 3D hace lo mismo pero muestra áreas en formato 3D sin usar un eje de profundidad.

Gráfico de burbujas y de tipo XY (Dispersión) En un gráfico de tipo XY (dispersión) se pueden trazar datos organizados en columnas y filas de una hoja de cálculo.

1. Coloque los valores X en una fila o columna y, a continuación, introduzca los valores y correspondientes en las

filas o columnas adyacentes.

2. Un gráfico de dispersión tiene dos ejes de valores, un eje horizontal (X) y otro en el eje vertical (Y). Combina

estos valores en puntos de datos únicos y los muestra en intervalos irregulares o agrupaciones. Los gráficos de

dispersión se utilizan por lo general para mostrar y comparar valores numéricos, como datos científicos,

estadísticos y de ingeniería.

Piense en utilizar un gráfico de dispersión cuando:

• Desea cambiar la escala del eje horizontal.

• Desea convertir dicho eje en una escala logarítmica.

• Los espacios entre los valores del eje horizontal no son uniformes.

• Hay muchos puntos de datos en el eje horizontal.

• Desea ajustar las escalas de ejes independientes de un gráfico de dispersión para revelar más información acerca

de los datos que incluyen pares o conjuntos agrupados de valores.

• Desea mostrar similitudes entre grandes conjuntos de datos en lugar de diferencias entre puntos de datos.

• Desea comparar muchos puntos de datos sin tener en cuenta el tiempo:cuantos más datos incluya en un gráfico

de dispersión, mejores serán las comparaciones que podrá realizar.

Tipos de gráficos de dispersión

Dispersión Este gráfico muestra puntos de datos sin conectar líneas para comparar pares de valores.


11

Dispersión con líneas suavizadas y marcadores y dispersión con líneas suavizadas Este gráfico muestra una curva suavizada que conecta los puntos de datos. Las líneas suavizadas se pueden mostrar con o sin marcadores. Use una línea suavizada sin marcadores si hay muchos puntos de datos.

Dispersión con líneas rectas y marcadores y dispersión con líneas rectas Este diagrama muestra líneas rectas de conexión entre puntos de datos. Pueden mostrar líneas rectas con o sin marcadores.

Gráfico de burbujas De manera muy similar a un gráfico de dispersión, un gráfico de burbujas agrega una tercera columna para especificar el tamaño de la burbuja que muestra para representar los puntos de datos en la serie de datos.

Tipo de gráficos de burbujas

Burbujas o burbujas con efecto 3D Ambos tipos de gráficos de burbujas comparan conjuntos de tres valores en lugar de dos, mostrando burbujas en formato 2D o 3D (sin usar un eje de profundidad). El tercer valor especifica el tamaño del marcador de burbuja.

Gráfico de cotizaciones En un gráfico de cotizaciones se pueden trazar datos que se organizan en columnas o filas en un orden específico en una hoja de cálculo. Como el nombre indica, los gráficos de cotizaciones pueden mostrar las fluctuaciones de los precios de las acciones. Sin embargo, este gráfico también se puede utilizar con datos científicos. Por ejemplo, podría utilizar un gráfico


12

de cotizaciones para indicar la fluctuación de las temperaturas diarias o anuales. Debe organizar los datos en el orden correcto para crear gráficos de cotizaciones. Por ejemplo, para crear un gráfico sencillo de cotizaciones de máximos, mínimos y cierre, organice los datos en columnas, con Máximos, Mínimos y Cierre como encabezados de columnas en ese orden.

Tipos de gráficos de cotizaciones

Máximos, mínimos y cierre Este gráfico de cotizaciones usa tres series de valores en el siguiente orden: altos, bajos y cerrados.

Apertura, máximos, mínimos y cierre Este gráfico de cotizaciones usa cuatro series de valores en el siguiente orden: abiertos, altos, bajos y cerrados.

Volumen, máximos, mínimos y cierre Este gráfico de cotizaciones usa cuatro series de valores en el orden siguiente (volumen, altos, bajos y cerrados). Mide el volumen mediante dos ejes de valores: uno para las columnas que miden el volumen y otro para el precio de los valores.

Volumen, apertura, máximos, mínimos y cierre Este gráfico de cotizaciones usa cinco series de valores en el siguiente orden: volumen, apertura, altos, bajos y cerrados.


13

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

Tipos de gráficos de superficie

Superficie 3D Este gráfico muestra una vista en 3D de los datos, que se puede imaginar como una hoja de goma estirada sobre un gráfico de columnas 3D. Se suele utilizar para mostrar relaciones entre grandes cantidades de datos que, de otra forma, podrían resultar difíciles de ver. Las bandas de colores de un gráfico de superficie no representan la serie de datos; indican la diferencia entre los valores.

Trama de superficie 3D Si se muestra sin color en la superficie, un gráfico de superficie 3D se denomina gráfico de trama de superficie 3D. Este gráfico solo muestra las líneas. Un gráfico de trama de superficie 3D no es fácil de leer pero puede trazar grandes conjuntos de datos con una rapidez mucho mayor que un gráfico de superficie 3D.

Contorno Los gráficos de contorno son gráficos de superficie vistos desde arriba, algo parecido a los mapas topográficos 2D. En un gráfico de contorno, las bandas de color representan rangos concretos de valores. Las líneas de un gráfico de contorno conectan puntos interpolados de igual valor.


14

Contorno reticular Los gráficos de contorno reticular también son gráficos de superficie vistos desde arriba. No tienen bandas de color en la superficie y solo muestran las líneas. No son fáciles de leer. Puede que desee usar un gráfico de superficie 3D en su lugar.

Gráficos radiales En un gráfico radial se pueden representar datos organizados únicamente en columnas o en filas de una hoja de cálculo. Los gráficos radiales comparan los valores agregados de varias series de datos.

Tipo de gráficos radiales

Radial y radial con marcadores Con o sin marcadores para puntos de datos individuales, los gráficos radiales muestran cambios en valores relativos a un punto central.

Radial relleno En un gráfico radial relleno, el área cubierta con una serie de datos se rellena con un color.

Gráfico de TreeMap (Office 2016 y versiones posteriores solo) El gráfico de treemap proporciona una vista jerárquica de los datos y una forma sencilla para comparar distintos niveles de clasificación. El gráfico de treemap muestra categorías de color y proximidad y puede mostrar fácilmente una gran cantidad de datos que serían difíciles con otros tipos de gráfico. Se pueden representar el gráfico de treemap cuando hay celdas vacías (en blanco) dentro de la estructura jerárquica y los gráficos de rectángulos para comparar las proporciones dentro de la jerarquía.


15

Nota: No hay ningún subtipo de gráfico para los gráficos de rectángulos.

Gráfico de proyección solar (Office 2016 y versiones posteriores solo) Tipo de gráfico es ideal para mostrar datos jerárquicos y se pueden representar cuando hay celdas vacías (en blanco) dentro de la estructura jerárquica. Cada nivel de la jerarquía está representado por una llamada o un círculo con un círculo oculto como la parte superior de la jerarquía. Un gráfico de proyección solar sin datos jerárquicos (un nivel de categorías), es similar a un gráfico de anillos. Sin embargo, un gráfico de proyección solar con varios niveles de categorías muestra cómo se relacionan los anillos exterior los anillos internas. Tipo de gráfico es más efectiva en que muestra cómo un timbre se divide en sus partes colaboradores.

Nota: No hay ningún subtipo de gráfico para los gráficos de proyección solar.

Gráficos de histograma (Office 2016 y versiones posteriores solo) Los datos trazados en un gráfico de histograma muestran las frecuencias dentro de una distribución. Cada columna del gráfico se denomina clase, que se puede modificar para analizar con mayor detalle los datos.


16

Tipos de gráficos de histograma

Histograma El gráfico de histograma muestra la distribución de los datos agrupados en clases de frecuencia.

Diagrama de pareto Un diagrama de pareto es un gráfico de histograma ordenado que contiene tanto columnas ordenadas en orden descendiente como una línea que representa el porcentaje total acumulado.

Gráfico de cajas y bigotes (Office 2016 y versiones posteriores solo) Un gráfico de cajas y bigotes muestra la distribución de datos en cuartiles, resaltando el promedio y los valores atípicos. Las cajas podrán tener líneas que se extienden verticalmente llamadas “bigotes”. Estas líneas indican la variabilidad fuera de los cuartiles superior e inferior y cualquier punto fuera de esas líneas o bigotes se considera un valor atípico. Los gráficos de cajas y bigotes se usan con más frecuencia en el análisis estadístico. Por ejemplo, podría usar un gráfico de cajas y bigotes para comparar los resultados de un ensayo clínico o las puntuaciones de un examen de un profesor.


17

Nota: No hay ningún subtipo de gráfico para los gráficos de cajas y bigotes.

Gráficos de cascada (Office 2016 y versiones posteriores solo) Un gráfico de cascada muestra un total acumulativo con valores que pueden sumar(positivos) y valores que pueden restar (negativos). Permite comprender el efecto acumulativo al introducir valores positivos o negativos de manera secuencial.

.

Nota: No hay ningún subtipo de gráfico para los gráficos de cascada.

Crear un gráfico de cascada

1. Seleccione los datos.


18

Datos utilizados para crear el gráfico de cascada de ejemplo anterior

2. Haga clic en Insertar, luego, en Insertar gráfico de cascada o gráfico cotizaciones y, por último, Cascada.

También puede usar la pestaña Todos los gráficos de Gráficos recomendados para crear un gráfico de cascada.

Sugerencia: Use las pestañas Diseño y formato para personalizar el aspecto del gráfico. Si no ve estas pestañas, haga clic en cualquier parte del gráfico de cascada para agregar las Herramientas de gráficos a la cinta de opciones.

Iniciar los subtotales o totales desde el eje horizontal

Si los datos incluyen valores que se consideren subtotales o totales, como los ingresos netos, puede establecerlos para que inicien en el eje horizontal en cero y no "floten".

• Haga doble clic en un punto de datos para abrir el panel de tareas Formato de punto de datos y Active la casilla establecer como total.


19

Nota: Si hace clic en la columna una sola vez, seleccionará la serie de datos y no el punto de datos.

Para que la columna flote de nuevo, desactive la casilla Definir como total.

Sugerencia: También puede establecer totales haciendo clic con el botón secundario en un punto de datos y eligiendo Establecer como total en el menú contextual.

Mostrar u ocultar las líneas conectoras

Las líneas conectoras conectan el final de cada columna con el principio de la columna siguiente, lo que ayuda a mostrar el flujo de los datos en el gráfico.

• Para ocultar las líneas conectoras, haga clic en una serie de datos para abrir el panel de tareas Formato de serie de datos y a continuación, desactive la casilla Mostrar líneas conectoras.

Para mostrar las líneas nuevamente, marque la casilla Mostrar líneas conectoras.

Sugerencia: La leyenda de gráfico agrupa los diferentes tipos de puntos de datos en el gráfico: aumentar, reducir y Total. Haga clic en una entrada de leyenda resalta todas las columnas que componen ese grupo en el gráfico.

Gráficos de Embudo (Office 2016 y versiones posteriores solo) Los gráficos de embudo muestran valores a través de varias fases de un proceso.


20

Normalmente, los valores disminuyen de forma gradual, lo que permite que las barras se asemejen a un embudo.

Gráficos combinados (Office 2013 y versiones posteriores solo) Los datos que se organizan en columnas y filas se pueden trazar en un gráfico combinado. Los gráficos combinados combinan dos o más tipos de gráficos para que los datos sean sencillos de comprender, especialmente cuando los datos varían en gran medida. Mostrado en un eje secundario, este gráfico es incluso más sencillo de leer. En este ejemplo, hemos usado un gráfico de columnas para mostrar el número de hogares vendidos entre enero y junio y, a continuación, hemos usado un gráfico de líneas para facilitar la identificación por parte de los lectores del precio promedio de venta al mes.

Tipo de gráficos combinados

Columna agrupada – columna agrupada y de líneas – línea en eje secundario Con o sin un eje secundario, este gráfico combina un gráfico de líneas y columnas agrupadas, en el que se muestran algunas series de datos como columnas y otras como líneas en el mismo gráfico.

Área apilada – columna agrupada Este gráfico combina un gráfico de columnas agrupadas y áreas apiladas, en el que se muestran algunas series de datos como áreas apiladas y otras como columnas en el mismo gráfico.


21

Combinación personalizada Este gráfico le permite combinar los gráficos que desea mostrar en el mismo gráfico.

Gráfico de mapa (Excel solo) Puede utilizar un gráfico de mapa para comparar valores y mostrar categorías por regiones geográficas. Utilícelo cuando tiene regiones geográficas en los datos, como países o regiones, Estados, ciudades o códigos postales. Por ejemplo, Países por población usa valores. Los valores representan la población total de cada país y cada uno de ellos se representa mediante un espectro degradado de dos colores. El color de cada región lo determina dónde se encuentra su valor en el espectro con respecto a los demás.

En el ejemplo siguiente, Países por categoría, las categorías se muestran con una leyenda estándar para mostrar grupos o afiliaciones. Cada punto de datos se representa con un color totalmente diferente.


22

Cambiar un tipo de gráfico

Si ya tiene un gráfico, pero solo desea cambiar su tipo: Seleccione el gráfico, haga clic en la pestaña Diseño y, a continuación, haga clic en Cambiar tipo de gráfico.

Elija un nuevo tipo de gráfico en el cuadro Cambiar tipo de gráfico.

Cambiar el formato de los elementos del gráfico

Puede cambiar el formato de los distintos elementos del gráfico, como área del gráfico, área de trazado, serie de datos, ejes, títulos, etiquetas de datos o leyenda. Existen dos conjuntos de herramientas para dar formato a los elementos del gráfico: el panel de tareas Formato y la Cinta de opciones de herramientas de gráfico. Para el mayor control, utilice las opciones en el panel de tareas formato.

Aplicar formato a gráficos con el panel de tareas formato Seleccione el elemento de gráfico (por ejemplo, series de datos, ejes o títulos), haga clic en él y haga clic en Formato > elemento de gráfico <. Aparece el panel formato con las opciones que están diseñadas para el elemento de gráfico seleccionado.

https://support.office.com/es-es/article/cambiar-el-formato-de-los-elementos-del-gr%C3%A1fico-b6c787d5-f90a-41d2-a901-9d3ed9f0dbf0#taskpane

https://support.office.com/es-es/article/cambiar-el-formato-de-los-elementos-del-gr%C3%A1fico-b6c787d5-f90a-41d2-a901-9d3ed9f0dbf0#ribbon

https://support.office.com/es-es/article/cambiar-el-formato-de-los-elementos-del-gr%C3%A1fico-b6c787d5-f90a-41d2-a901-9d3ed9f0dbf0#ribbon


23

Haga clic en los iconos pequeños en la parte superior del panel, se mueve a otros elementos del panel con más opciones. Si hace clic en un elemento de gráfico diferente, verá que el panel de tareas se actualiza automáticamente al nuevo elemento de gráfico.

Por ejemplo, para dar formato a un eje: 1. Haga clic con el botón secundario en el eje del gráfico y haga clic en Formato de ejes.

2. En el panel de tareas Formato de ejes, realice los cambios que desee. Puede mover o cambiar el tamaño del panel de tareas para que sea más fácil trabajar con él. Haga clic en las comillas angulares de la esquina superior derecha.


24

o Seleccione Mover y arrastre el panel a una nueva ubicación. o Seleccione Tamaño y arrastre el borde del panel para cambiar su tamaño.

Aplicar formato a gráficos con la cinta de opciones 1. En el gráfico, haga clic para seleccionar el elemento de gráfico que desea dar formato. 2. En la ficha formato en Herramientas de gráfico, siga uno de estos procedimientos:

o Haga clic en Relleno de forma para aplicar un color de relleno diferente o un degradado, imagen o textura al elemento de gráfico.

o Haga clic en Contorno de forma para cambiar el color, grosor o estilo del elemento de gráfico.

o Haga clic en Efectos de formas para aplicar efectos visuales especiales para el elemento de gráfico, como sombras, biseles y giro 3D.

o Para aplicar un estilo de forma predefinida, en la ficha formato, en el grupo Estilos de forma, haga clic en el

estilo que desee. Para ver todos los estilos disponibles, haga clic en el botón más .

o Para cambiar el formato de texto del gráfico, seleccione el texto y, a continuación, elija una opción en la mini barra de herramientas que aparece. O bien, en la ficha Inicio, en el grupo fuente, seleccione el formato que desee usar.

o Para usar estilos de WordArt para dar formato al texto, seleccione el texto y, a continuación, en la ficha formato, en el grupo Estilos de WordArt, elija un estilo de WordArt para aplicarlo. Para ver todos los estilos

disponibles, haga clic en el botón más .

Usar mini gráficos para mostrar tendencias de datos

Un mini gráfico es un gráfico muy pequeño situado en una celda de hoja de cálculo que proporciona una representación visual de datos. Use mini gráficos para reflejar las tendencias de una serie de valores, como aumentos o reducciones periódicos y ciclos económicos, o para resaltar valores mínimos y máximos. Coloque el mini gráfico cerca de los datos correspondientes para un mayor impacto visual.

Agregar un mini gráfico 1. Seleccione una celda en blanco al final de una fila de datos.


25

2. Seleccione Insertar y elija el tipo de mini gráfico, como Línea o Columna. 3. Seleccione las celdas de la fila y Aceptar en el menú. 4. ¿Más filas de datos? Arrastre el controlador para agregar un mini gráfico para cada fila.


26

Dar formato a un mini gráfico 1. Seleccione el mini gráfico. 2. Seleccione Diseño y, después, una opción:


27

o Seleccione Línea, Columna o Pérdidas y ganancias para cambiar el tipo de gráfico. o Active Marcadores para resaltar valores individuales en el mini gráfico. o Seleccione un Estilo para el mini gráfico. o Seleccione Color de mini gráfico y el color. o Seleccione Color de mini gráfico > Grosor para seleccionar el ancho del mini gráfico. o Seleccione Color de marcador para cambiar el color de los marcadores. o Si los datos tienen valores positivos y negativos, seleccione Eje para mostrar el eje.


28

Sección 2: Bases de Datos

Base de datos en Excel

Una Base de Datos en Excel, también denominada lista, es un conjunto de datos organizados en filas o registros, en los cuales la primera fila contiene los títulos de las columnas (nombres de los campos), y las demás filas contienen los datos almacenados.

Si observas con detenimiento la imagen anterior, notarás que una tabla se asemeja mucho a una hoja de Excel la cual también está dividida en columnas y filas. Por esa razón Excel ha sido utilizado por mucho tiempo, y por muchas personas, como un repositorio de datos.

Tips para crear una base de datos en Excel

Como ejemplo analizaremos el caso de una pequeña librería que desea crear un catálogo de sus libros en Excel, y crear una lista de sus clientes y otra lista de órdenes de compra. Antes de crear la base de datos en Excel debe crear el diseño siguiendo estos pasos:

Paso 1: Identificar las tablas que ocuparemos para organizar la información. Para nuestro ejemplo de la librería podemos mencionar las siguientes tablas:

Paso 2: Determinar los campos de las tablas Una vez que hemos identificado las tablas debemos indicar el nombre de cada una de sus columnas (campos). El nombre del campo deberá ser descriptivo y nos ayudará a identificar cada una de las propiedades de un elemento de la tabla. Por ejemplo, para cada libro en venta en la librería guardaremos su código ISBN, el título, el autor y el precio por lo que dicha tabla quedará definida de la siguiente manera:


29

Los campos de cada tabla dependerán de la información que está a tu disposición para ser ingresada en la base de datos. Por ejemplo, algunas empresas o negocios tendrán interés en capturar más información sobre sus clientes lo cual hará que su tabla de Clientes tenga más campos que los mostrados en el ejemplo anterior.

Paso 3: Identificar la llave primaria de cada tabla Una llave primaria nos permitirá identificar de manera única cada uno de los registros de las tablas. En el caso de la tabla Libros, el ISBN será la llave primaria ya que es un código único internacional para cada libro publicado por lo que dicho número jamás se repetirá.

En el caso de las tablas para Órdenes y Clientes crearemos un número consecutivo que identificará de manera única cada una de las filas.

Paso 4: Identificar las relaciones entre tablas Este paso es de utilidad para asegurarnos que podremos vincular la información de las tablas a través de la relación que existe entre dos de sus campos. Por ejemplo, si para una determinada orden de compra quiero saber el título del libro que ha sido vendido, bastará con relacionar la columna ISBN de la tabla Órdenes con la columna ISBN de la tabla Libros para conocer el título.


30

De manera similar, si deseo conocer el cliente para una determinada orden de compra, solo deberé seguir la relación indicada por el campo IDCliente en ambas tablas.

Paso 5: Identificar datos repetidos en tablas Es importante hacer un breve ejercicio con algunos datos reales para identificar la posible existencia de datos repetidos y tomar las decisiones adecuadas para evitarlos. Por ejemplo, considera el caso de la tabla Libros donde podríamos tener uno o más títulos escritos por el mismo autor. En ese caso, estaríamos repitiendo el nombre y apellido del autor en varios registros de la tabla.

Para evitar la repetición de datos lo recomendable es crear una nueva tabla que almacene la información de los autores y hacer referencia a dicha tabla a través de su campo llave tal como se muestra en la siguiente imagen:


31

De esta manera evitaremos la repetición en el nombre de los autores y también se evitará la captura de un sin número de variantes de este. Para seguir con nuestro análisis, haremos una captura de datos de prueba de una orden, por ejemplo:

Estos son los datos de la orden de compra número 1 donde el cliente ha comprado 3 libros, siendo dos de esas copias del mismo título. El problema con estos datos es que se repite el número de orden y eso no sería posible ya que esa es nuestra llave primaria en la tabla y no puede repetirse. Además, para cada registro se repite la Fecha, así como las columnas IDCliente y TipoPago. Para resolver este problema debemos crear una tabla adicional que almacenará los libros de cada orden de compra de la siguiente manera:

Es así como el haber identificado datos repetidos en las tablas nos ha hecho tomar la decisión de agregar tablas adicionales a nuestro diseño para minimizar el espacio utilizado por nuestra base de datos que será de gran importancia al momento de que los datos crezcan.


32

Diseño Final de la Base de Datos De esta manera nuestro diseño final queda de la siguiente manera:

Crear la base de datos en Excel

Una vez que tenemos el diseño de la base de datos podemos confeccionarla más fácilmente en Excel siguiendo dos recomendaciones muy importantes:

• Cada tabla del diseño será una tabla de Excel.

• Colocar cada tabla en su propia hoja de Excel.

El motivo para utilizar tablas de Excel es que existen múltiples beneficios de utilizar tablas entre los cuales están los filtros y el reconocimiento automático de nuevas filas y columnas. Por otro lado, la razón por la cual colocar cada tabla en su

https://exceltotal.com/tablas-en-excel/


33

propia hoja de Excel es para facilitar el crecimiento de los datos ya que si colocas varias tablas en una misma hoja probablemente tendrás que moverlas o redefinirlas cada vez que alguna de ellas aumente de tamaño y se sobrepongan entre sí.

Descripción general de las tablas de Excel

Para que la administración y el análisis de un grupo de datos relacionados sea más fácil, puede activar un intervalo de celdas en una tabla de Microsoft Office Excel (antes llamadas listas de Excel). Una tabla normalmente contiene datos relacionados en una serie de filas y columnas de hoja de cálculo a las que se ha aplicado un formato de tabla. Con las características de una tabla, puede administrar los datos de las filas y columnas de la tabla de forma independiente a los datos de otras filas y columnas de la hoja de cálculo.

Una tabla puede incluir los elementos siguientes:

Fila de encabezado De forma predeterminada, una tabla tiene una fila de encabezado. Cada columna de la tabla tiene activado el filtrado de la fila de encabezado para que pueda filtrar u ordenar los datos de la tabla rápidamente.

Filas con bandas De forma predeterminada, se ha aplicado un sombreado alterno (o bandas) a las filas de una tabla para distinguir mejor los datos.


34

Columnas calculadas Al introducir una fórmula en una celda de una columna de tabla, puede crear una columna calculada en la que esa fórmula se aplica de forma inmediata a todas las otras celdas de esa columna de tabla.

Fila de totales Puede agregar una fila de totales a la tabla que proporciona acceso a las funciones de resumen (como las funciones PROMEDIO, CONTAR o SUMA). Aparece una lista desplegable en cada celda de la fila de totales, para que pueda calcular rápidamente los totales que quiera.

Controlador de tamaño

Un controlador de tamaño en la esquina inferior derecha de la tabla le permite arrastrar la tabla hasta el tamaño que desee.

https://support.office.com/es-es/article/funci%C3%B3n-promedio-047bac88-d466-426c-a32b-8f33eb960cf6

https://support.office.com/es-es/article/contar-funci%C3%B3n-contar-a59cd7fc-b623-4d93-87a4-d23bf411294c

https://support.office.com/es-es/article/funci%C3%B3n-suma-043e1c7d-7726-4e80-8f32-07b23e057f89


35

Administración de datos en una tabla de Excel

Si tiene acceso a un sitio de Microsoft Windows SharePoint Services y permisos de creación, puede compartir una tabla con otros usuarios. Al exportar los datos de la tabla a una lista de SharePoint, otros usuarios pueden ver, editar y actualizar los datos de la tabla en la lista de SharePoint. Puede crear una conexión unidireccional a la lista de SharePoint para poder actualizar los datos de la tabla de la hoja de cálculo y así incorporar los cambios realizados en los datos de la lista de SharePoint. Ya no se puede actualizar una lista de SharePoint con los cambios que se realicen en los datos de la tabla en Excel. Después de exportar los datos de la tabla a una lista de SharePoint, puede abrir una lista de SharePoint en Excel como de solo lectura: únicamente podrá realizar cambios en los datos del sitio de SharePoint.

Nota: Si el libro es un libro compartido, no se pueden crear tablas.

Ordenar y filtrar Se añaden automáticamente listas desplegables de filtros en la fila de encabezado de una tabla. Puede ordenar las tablas en orden ascendente o descendente, o bien por color, o puede crear un orden personalizado. Puede filtrar tablas para mostrar solo los datos que cumplen con los criterios que especifique, o puede filtrarlas por color. Para más información sobre cómo para filtrar u ordenar los datos, vea Filtrar datos u Ordenar los datos.

Datos de la tabla de formato Puede dar formato a una tabla rápidamente si aplica un estilo de tabla predefinido o personalizado. También puede elegir las opciones de estilos de tablas para mostrar una tabla con o sin filas de encabezado y totales, aplicar bandas a las filas o columnas para que sea más sencilla de leer o distinguir la primera o la última columna del resto de la tabla. Para obtener más información sobre cómo dar formato a los datos de la tabla, consulte Aplicar formato a una tabla de Excel.

Insertar y eliminar filas y columnas de una tabla Existen varias formas de agregar filas y columnas a una tabla. Puede agregar rápidamente una fila en blanco al final de la tabla, incluir filas o columnas de hojas de cálculo adyacentes a la tabla o insertar filas y columnas en cualquier parte de la tabla. Puede eliminar filas y columnas según sea necesario. También puede quitar rápidamente las filas que contienen datos duplicados de una tabla. Para obtener más información sobre cómo agregar y eliminar filas y columnas de una tabla, consulte Agregar o quitar filas y columnas de una tabla de Excel.

Usar una columna calculada Puede crear una columna calculada para usar una fórmula única que se ajusta a cada fila de una tabla. Una columna calculada se amplía de forma automática para incluir filas adicionales de forma que la fórmula se propaga inmediatamente a esas filas. Para más información sobre cómo crear una columna calculada, vea Usar columnas calculadas en una tabla de Excel.

Mostrar los datos de la tabla y calcular totales Puede mostrar el resultado total de los datos de una tabla con una fila de totales al final de la misma y usar las funciones que se proporcionan en las listas desplegables para cada celda de la misma. Para obtener más información sobre cómo mostrar y calcular los datos totales de la tabla, consulte Calcular los datos totales en una tabla de Excel.

Usar referencias estructuradas En lugar de usar referencias de celda, como A1 y R1C1, puede usar en una fórmula referencias estructuradas que hacen referencia a los nombres de tabla.

Garantizar la integridad de datos Para las tablas que no están vinculadas a listas de SharePoint, puede usar las características de validación de datos integradas en Excel. Por ejemplo, puede permitir únicamente números o fechas en una columna de una tabla. Para obtener más información sobre cómo garantizar la integridad de datos, vea aplicar validación de datos a las celdas.

Exportar a una lista de SharePoint Puede exportar una tabla a una lista de SharePoint para que otras personas puedan ver, editar y actualizar los datos de la tabla.

https://support.office.com/es-es/article/acerca-de-la-caracter%C3%ADstica-de-libro-compartido-49b833c0-873b-48d8-8bf2-c1c59a628534

https://support.office.com/es-es/article/filtrar-los-datos-de-una-tabla-o-un-rango-01832226-31b5-4568-8806-38c37dcc180e

https://support.office.com/es-es/article/ordenar-datos-en-un-rango-o-tabla-62d0b95d-2a90-4610-a6ae-2e545c4a4654

https://support.office.com/es-es/article/aplicar-formato-a-una-tabla-de-excel-6789619f-c889-495c-99c2-2f971c0e2370

https://support.office.com/es-es/article/cambiar-el-tama%C3%B1o-de-una-tabla-agregando-o-quitando-filas-y-columnas-e65ae4bb-e44b-43f4-ad8b-7d68784f1165

https://support.office.com/es-es/article/usar-columnas-calculadas-en-una-tabla-de-excel-873fbac6-7110-4300-8f6f-aafa2ea11ce8

https://support.office.com/es-es/article/usar-columnas-calculadas-en-una-tabla-de-excel-873fbac6-7110-4300-8f6f-aafa2ea11ce8

https://support.office.com/es-es/article/total-de-datos-de-una-tabla-de-excel-6944378f-a222-4449-93d8-474386b11f20

https://support.office.com/es-es/article/aplicar-la-validaci%C3%B3n-de-datos-a-celdas-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249


36

Consultar la base de datos en Excel

Una vez que se ha creado la base de datos en Excel y comiencen a crecer los datos, seguramente te encontrarás con la necesidad de relacionar la información de diferentes tablas para obtener datos complementarios. Por ejemplo, considera las siguientes dos tablas:

La Hoja. Órdenes contiene una tabla llamada Ordenes y la Hoja. Clientes contiene la tabla Clientes. Si al consultar la tabla Ordenes deseo conocer el Email del cliente que ha efectuado dicha compra, puedo utilizar funciones de Excel para obtener dicha información. Ya que estoy utilizando tablas de Excel, puedo hacer uso de las referencias estructuradas, por ejemplo:

=BUSCARV(Ordenes[IDCliente], Clientes, 5,FALSO)

Esta fórmula buscará el valor de la columna IDCliente, que pertenece a la tabla Ordenes, dentro de la tabla Clientes y devolverá la columna 5 de esta última tabla. Observa el resultado de la fórmula:

El resultado de la fórmula es correcto ya que la orden número 1 fue hecha por el cliente con un IDCliente igual a 5 y que corresponde a Luis cuyo Email es el mostrado en la celda F2. De esta manera puedes relacionar la información de las diferentes tablas en tu base de datos en Excel, ya sea utilizando la función BUSCARV o la combinación de funciones INDICE/COINCIDIR ya que ambas nos permite crear fórmulas para buscar datos.

Ordenar datos en un rango o tabla

Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por

https://exceltotal.com/referencias-estructuradas/

https://exceltotal.com/formula-para-buscar-datos-en-excel/


37

iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces. Puede ordenar datos por texto (de la A a la Z o de la Z a la A), números (de menor a mayor o de mayor a menor) y fechas y horas (de más antiguos a más recientes y de más recientes a más antiguos) en una o varias columnas. También puede ordenar por una lista personalizada que cree (como Grande, Medio y Pequeño) o por formato, incluido el color de celda, el color de fuente o el conjunto de iconos.

Notas:

Para identificar los valores superiores o inferiores de un rango de celdas o de una tabla (por ejemplo, las diez mejores calificaciones o los cinco peores importes de ventas), use un filtro automático o el formato condicional.

Para obtener más información, vea Filtrar datos en una tabla de Excel o un intervalo, y Aplicar formato condicional en Excel.

Ordenar texto 1. Seleccione una celda de la columna que quiere ordenar. 2. En la ficha Datos, en el grupo Ordenar y filtrar, siga uno de estos procedimientos:

o Para ordenar rápido en orden ascendente, haga clic en (Ordenar de A a Z).

o Para ordenar rápido en orden descendente, haga clic en (Ordenar de Z a A).

Notas: Posibles problemas

Comprobar si todos los datos están almacenados como texto Si la columna que quiere ordenar contiene números almacenados como números y números almacenados como texto, será necesario que les aplique formato de número o de texto a todos. Si no lo hace, los números almacenados como números se ordenarán antes que los números almacenados como texto. Para aplicar formato de texto a todos los datos seleccionados, presione Ctrl+1 para abrir el cuadro de diálogo Formato de celdas, haga clic en la pestaña Número y, después, en Categoría, haga clic en General, Número o Texto.

Quitar los espacios iniciales En algunos casos, los datos que se hayan importado de otra aplicación pueden tener espacios iniciales insertados delante de los mismos. Antes de ordenar los datos, quite estos espacios. Puede hacerlo manualmente o mediante la función RECORTAR.

Ordenar números

1. Seleccione una celda de la columna que quiere ordenar.

2. En la ficha Datos, en el grupo Ordenar y filtrar, siga uno de estos procedimientos:

o Para ordenar de menor a mayor, haga clic en (Ordenar de menor a mayor).

o Para ordenar de mayor a menor, haga clic en (Ordenar de mayor a menor).

Notas: Posible problema

Comprobar que todos los números se almacenan como números Si los resultados no son los que esperaba, es posible que la columna contenga números almacenados como texto y no como números. Por ejemplo, los números negativos importados de algunos sistemas de contabilidad o los números con un apóstrofo inicial (') se almacenan como texto. Para

https://support.office.com/es-es/article/filtrar-los-datos-de-una-tabla-o-un-rango-01832226-31b5-4568-8806-38c37dcc180e

https://support.office.com/es-es/article/aplicar-formato-condicional-en-excel-34402f91-c7e7-4060-944c-65d913033d18

https://support.office.com/es-es/article/espacios-funci%C3%B3n-espacios-410388fa-c5df-49c6-b16c-9e5630b479f9


38

obtener más información, vea Corregir números con formato de texto aplicando un formato de número.

Ordenar fechas u horas

1. Seleccione una celda de la columna que quiere ordenar.

2. En la ficha Datos, en el grupo Ordenar y filtrar, siga uno de estos procedimientos.

o Para ordenar de la fecha u hora más antigua a la más reciente, haga clic en (Ordenar de más antiguo a más reciente).

o Para ordenar de una hora o fecha posterior a una anterior, haga clic en (Ordenar de más reciente a más antiguo).

Notas: Posible problema

Comprobar que las fechas y horas se almacenan como valores de fecha y hora Si los resultados obtenidos no son los que esperaba, es posible que la columna contenga fechas u horas almacenadas como texto y no como valores de fecha u hora. Para que Excel las ordene correctamente, todos los valores de fecha y hora de una columna tienen que estar almacenados como números de serie de fecha u hora. Si Excel no puede reconocer un valor como fecha u hora, significa que la fecha u hora se almacenó como texto. Para obtener más información, vea Convertir fechas almacenadas como texto en fechas.

Si quiere ordenar por días de la semana, aplique formato a las celdas para mostrar el día de la semana. Si quiere ordenar por el día de la semana independientemente de la fecha que sea, conviértalos en texto mediante la función TEXTO. Pero la función TEXTO devuelve un valor de texto y, por tanto, la operación de ordenación estará basada en datos alfanuméricos. Para obtener más información, vea Mostrar la fecha como día de la semana.

Ordenar por más de una columna o fila Es posible que quiera ordenar por más de una columna o fila si tiene datos que desea agrupar por el mismo valor en una columna o fila y, además, ordenar otra columna o fila dentro de ese grupo de valores iguales. Por ejemplo, si tiene una columna Departamento y otra Empleado, puede querer ordenar primero por departamento (para agrupar todos los empleados del mismo departamento), y a continuación, ordenar por nombre de empleado (para poner los nombres en orden alfabético dentro de cada departamento). Puede ordenar por un máximo de 64 columnas.

Nota: Para obtener resultados óptimos, el rango de celdas que se ordene debe tener encabezados de columna.

1. Seleccione cualquier celda del rango de datos.

https://support.office.com/es-es/article/corregir-n%C3%BAmeros-con-formato-de-texto-aplicando-un-formato-de-n%C3%BAmero-6599c03a-954d-4d83-b78a-23af2c8845d0

https://support.office.com/es-es/article/corregir-n%C3%BAmeros-con-formato-de-texto-aplicando-un-formato-de-n%C3%BAmero-6599c03a-954d-4d83-b78a-23af2c8845d0

https://support.office.com/es-es/article/convertir-fechas-almacenadas-como-texto-en-fechas-8df7663e-98e6-4295-96e4-32a67ec0a680

https://support.office.com/es-es/article/texto-funci%C3%B3n-texto-20d5ac4d-7b94-49fd-bb38-93d29371225c

https://support.office.com/es-es/article/mostrar-la-fecha-como-d%C3%ADa-de-la-semana-14f02f1d-8e3d-45a5-bdf8-3d962bf15327

https://support.office.com/es-es/article/mostrar-la-fecha-como-d%C3%ADa-de-la-semana-14f02f1d-8e3d-45a5-bdf8-3d962bf15327


39

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

3. En el cuadro de diálogo Ordenar, en el cuadro Ordenar por que aparece bajo Columna, seleccione la primera columna que quiere ordenar.

4. En Ordenar según, seleccione el tipo de ordenación. Siga uno de los procedimientos siguientes: o Para ordenar por texto, número, o fecha y hora, seleccione Valores. o Para ordenar por formato, seleccione Color de celda, Color de fuente o Icono de celda.

5. En Orden, seleccione cómo desea ordenar. Siga uno de estos procedimientos:


40

o Para valores de texto, seleccione De la A a la Z o De la Z a la A. o Para valores numéricos, seleccione De menor a mayor o De mayor a menor. o Para valores de fecha u hora, seleccione De más antiguo a más reciente o De más reciente a más antiguo. o Para ordenar según una lista personalizada, seleccione Lista personalizada.

6. Para agregar otra columna por la que ordenar, haga clic en Agregar nivel y, a continuación, repita los pasos del tres al cinco.

7. Para copiar una columna por la que ordenar, seleccione la entrada y, a continuación, haga clic en Copiar nivel. 8. Para eliminar una columna según la que ordenar, seleccione la entrada y a continuación, haga clic en Eliminar Nivel.

Nota: Debe conservar al menos una entrada en la lista. 9. Para cambiar el orden en el que se clasifican las columnas, seleccione una entrada y haga clic en la flecha arriba o abajo

situada junto al botón Opciones para cambiarlo. 10. Las entradas más arriba en la lista se ordenan antes que las que están más abajo.

Ordenar por color de celda, color de fuente o icono Si ha aplicado formato manual o condicionalmente a un rango de celdas o a una columna de tabla por color de celda o color de fuente, también puede ordenar por estos colores. Además, puede ordenar por un conjunto de iconos creado con formato condicional.

1. Seleccione una celda de la columna que quiere ordenar. 2. En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Ordenar.

3. En el cuadro de diálogo Ordenar, en el cuadro Ordenar por que aparece bajo Columna, seleccione la columna que quiere ordenar.


41

4. En Ordenar según, seleccione Color de celda, Color de fuente o Icono de celda.

5. En Criterio de ordenación, haga clic en la flecha situada junto al botón y, a continuación, dependiendo del tipo de formato, seleccione un color de celda, un color de fuente o un icono de celda.

6. Después, seleccione el modo en que quiere ordenar. Siga uno de los procedimientos siguientes: o Para mover el color de celda, el color de fuente o el icono a la parte superior o a la izquierda, seleccione En la

parte superior para una ordenación de columnas y A la izquierda para una ordenación de filas. o Para mover el color de celda, el color de fuente o el icono en la parte inferior o a la derecha, seleccione En la

parte inferior al ordenar por columna, y A la derecha para ordenar por fila.

Nota: No hay un criterio predeterminado de color de celda, color de fuente o icono. Debe definir el orden para cada operación de ordenación.

7. Para especificar el siguiente color de celda, color de fuente o icono de la ordenación, haga clic en Agregar nivel y a continuación, repita los pasos tres a cinco.

Asegúrese de seleccionar la misma columna en el cuadro Luego por y haga la misma selección en Orden. Repita el procedimiento por cada color de celda, color de fuente o icono adicionales que quiere incluir en la ordenación.

Ordenar por una lista personalizada Puede utilizar una lista personalizada para ordenar según un orden definido por el usuario. Por ejemplo, una columna puede contener valores por los que desea ordenar, como Alto, Mediano y Bajo. ¿Cómo puede ordenar de modo que las filas que contienen Alto aparezcan primero, seguidas por Mediano y luego Bajo? Si fuera a ordenar alfabéticamente, una ordenación “A a Z” pondría Alto primero, pero Bajo vendría antes que Mediano. Y si ordenara de “Z a A”, Mediano aparecería primero, con Bajo en el medio. Independientemente del orden, siempre desea “Mediano” en el medio. Al crear su propia lista personalizada, puede evitar este problema.

1. Opcionalmente, cree una lista personalizada: a. En un rango de celdas, escriba los valores por los que va a ordenar, en el orden que quiera, de arriba abajo

como en este ejemplo.


42

b. Seleccione el rango que acaba de especificar. En el ejemplo anterior, seleccione las celdas A1:A3. c. Vaya a Archivo > Opciones > Avanzadas > General > Modificar listas personalizadas. Después, en el cuadro de

diálogo Listas personalizadas, haga clic en Importar y haga clic en Aceptar dos veces.

Notas:

Una lista personalizada solo puede crearse basada en un valor (texto, número y fecha u hora). No puede crear una lista personalizada basada en un formato (color de celda, color de fuente o icono).

La longitud máxima para una lista personalizada es de 255 caracteres y el primer carácter puede no empezar por un número.

2. Seleccione una celda de la columna que quiere ordenar. 3. En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Ordenar.

4. En el cuadro de diálogo Ordenar, en Columna, en el cuadro Ordenar por o en Luego por, seleccione la columna que

quiere ordenar según una lista personalizada. 5. En Ordenar, seleccione Lista personalizada. 6. En el cuadro de diálogo Listas personalizadas, seleccione la lista que desee. Utilizando la lista personalizada que creó en

el ejemplo anterior, haga clic en Alto, Medio, Bajo. 7. Haga clic en Aceptar.

Ordenar distinguiendo mayúsculas de minúsculas En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Ordenar.

1. En el cuadro de diálogo Ordenar, haga clic en Opciones.


43

2. En el cuadro de diálogo Opciones de ordenación, seleccione Distinguir mayúsculas de minúsculas.

3. Haga clic dos veces en Aceptar.

Ordenar de izquierda a derecha Es más común ordenar de arriba a abajo, pero también puede ordenar de izquierda a derecha.

Nota: Las tablas no admiten la ordenación de izquierda a derecha. Para hacerlo, primero debe convertir la tabla en un rango. Para ello, seleccione una celda de la tabla y haga clic en Herramientas de tabla > Convertir en rango.

1. Seleccione cualquier celda del rango que quiera ordenar. 2. En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Ordenar.

3. En el cuadro de diálogo Ordenar, haga clic en Opciones.


44

4. En el cuadro de diálogo Opciones de ordenación, en Orientación, haga clic en Ordenar de izquierda a derecha y, a continuación, en Aceptar.

5. En Fila, en el cuadro Ordenar por, seleccione la fila que quiere ordenar. Por lo general, será la fila 1 si quiere ordenar por la fila de encabezado.

Sugerencia: Si la fila de encabezado es texto, pero quiere ordenar las columnas por números, puede agregar una nueva fila sobre el rango de datos y agregar números según el orden que quiera.

6. Para ordenar por valor, seleccione una de las opciones de la lista desplegable Orden: a. Para los valores de texto, seleccione A a Z o Z a A. b. Para valores numéricos, seleccione De menor a mayor o De mayor a menor. c. Para valores de fecha u hora, seleccione De más antiguo a más reciente o De más reciente a más antiguo.

7. Para ordenar por color de celda, color de fuente o icono de celda, haga lo siguiente: a. En Ordenar según, seleccione Color de celda, Color de fuente o Icono de celda.


45

b. En Orden, seleccione un color de celda, un color de fuente o un icono de celda y, después, seleccione A la izquierda o A la derecha.

Nota: Al ordenar filas que forman parte de un esquema de hoja de cálculo, Excel ordenará los grupos de nivel más alto (nivel 1) de modo que las filas o las columnas de detalle permanezcan juntas, aunque estén ocultas.

Ordenar por un valor parcial en una columna Para ordenar por la parte de un valor de una columna, como códigos de números de pieza (789-WDG-34), apellidos (Carlota Melgar) o nombres (Melgar, Carlota), primero necesita dividir la columna en dos o más columnas para que el valor por el que quiera ordenar esté en su propia columna. Para hacerlo, puede usar funciones de texto para separar las partes de las celdas, o bien usar el Asistente para convertir texto en columnas. Para obtener más información y ejemplos, vea Dividir texto en varias celdas y Dividir texto en columnas con funciones.

Ordenar un rango dentro de un rango mayor Advertencia: Es posible ordenar un rango dentro de un rango, aunque no se recomienda, ya que el resultado desasocia el rango ordenado de sus datos originales. Si ordenara los datos siguientes como se muestra, los empleados seleccionados se asociarían con departamentos diferentes.

Afortunadamente, Excel se lo notificará si detecta que va a intentar hacerlo:

Si no quería ordenar de este modo, presione la opción Ampliar la selección. En caso contrario, seleccione Continuar con la selección actual.

Si el resultado no es el que quiere, haga clic en Deshacer .

https://support.office.com/es-es/article/dividir-texto-en-diferentes-columnas-con-el-asistente-para-convertir-texto-en-columnas-30b14928-5550-41f5-97ca-7a3e9c363ed7

https://support.office.com/es-es/article/dividir-texto-en-columnas-diferentes-mediante-funciones-49ec57f9-3d5a-44b2-82da-50dded6e4a68


46

Filtrar utilizando criterios avanzados

Si los datos que desea filtrar requieren criterios complejos (como Tipo = "Alimentos" O Vendedor = "Davolio"), puede usar el cuadro de diálogo Filtro avanzado. Para abrir el cuadro de diálogo de Filtro avanzado haga clic en Datos > Avanzadas.

El comando Avanzadas funciona de forma diferente del comando Filtrar en varios aspectos importantes.

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

Datos de ejemplo Los siguientes datos de ejemplo se usan para todos los procedimientos en este artículo. Los datos incluyen cuatro filas en blanco encima del rango de la lista que se utilizará como un rango de criterios (A1:C4) y un rango de la lista (A6:C10). El rango de criterios tenga etiquetas de columna e incluye al menos una fila en blanco entre los valores de criterios y el rango de la lista. Para trabajar con estos datos, seleccione en la tabla siguiente, cópielo y péguelo en la celda A1 de una nueva hoja de cálculo de Excel.

Tipo Vendedor Ventas


Bebidas Suyama 5122 $

Carnes Davolio 450 $

Alimentos Buchanan 6328 $

Alimentos Davolio 6544 $

Operadores de comparación Puede comparar dos valores con los siguientes operadores. Cuando se comparan dos valores usando estos operadores, el resultado es un valor lógico: ya sea VERDADERO o FALSO.

Operador de comparación Significado Ejemplo

= (signo igual) Igual a A1=B1

> (signo mayor que) Mayor que A1>B1

< (signo menor que) Menor que A1<B1

>= (signo mayor o igual que) Mayor o igual que A1>=B1

<= (signo menor o igual que) Menor o igual que A1<=B1

<> (signo distinto de) Distinto de A1<>B1

Utilizar el signo igual para escribir texto o un valor Dado que el signo igual (=) se usa 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 quiere buscar. Por ejemplo:


47

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, pero puede usar una fórmula para realizar una búsqueda que distinga entre mayúsculas y minúsculas.

Utilizar nombres predefinidos Se puede asignar a un rango el nombre Criterios, y la referencia del rango aparecerá automáticamente en el cuadro Rango de criterios. También puede definir el nombre Base de datos para el rango de datos que quiera filtrar y definir el nombre Extraer para el área donde quiere 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 usar como criterio un valor calculado que sea el resultado de una fórmula. Tenga en cuenta los siguientes puntos importantes:

• 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 ''

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

• Si esta utilizando una TABLA como rango de la lista, escriba la formula utilizando referencia aceldas y no utilice el lenguaje DAX

Si en la fórmula usa una etiqueta de columna en lugar de una referencia de celda relativa o un nombre de rango, Excel mostrará un valor de error (como #¿NOMBRE? o #¡VALOR!) en la celda que contiene el criterio. Puede ignorar este error, ya que no afecta a la manera en que se filtra el rango de lista.

• La fórmula que utilice con el fin de generar los criterios debe utilizar una referencia relativa para hacer referencia a la celda correspondiente de la primera fila.

• Todas las demás referencias usadas en la fórmula tienen que ser referencias absolutas.

Varios criterios, una columna, cualquier criterio verdadero Lógica booleana: (Vendedor = "Davolio" O Vendedor = "Buchanan")

1. Inserte como mínimo tres filas en blanco encima del rango que puede usarse como rango de criterios. Es necesario que el rango de criterios tenga etiquetas de columna. Compruebe que existe como mínimo una fila en blanco entre los valores de criterios y el rango de lista.

2. 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. Siguiendo el ejemplo, escriba:


="=Davolio"

=Buchanan

3. Haga clic en una celda del rango de la lista. Siguiendo el ejemplo, haga clic en cualquier celda del rango, A6:C10. 4. En el grupo Ordenar y filtrar de la pestaña Datos, haga clic en Avanzadas.

5. Siga uno de los procedimientos siguientes: o Para filtrar el rango de lista ocultando las filas que no cumplen los criterios, haga clic en Filtrar la lista sin

moverla a otro lugar.


48

o Para filtrar el rango de lista copiando las filas que cumplen los criterios a otra área de la hoja de cálculo, haga clic en Copiar a otro lugar, haga clic en la casilla Copiar a y, después, haga clic en la esquina superior izquierda del área donde quiere pegar las filas.

Sugerencia Al copiar las filas filtradas a otra ubicación, puede especificar qué columnas incluirá en la operación de copia. Antes de aplicar el filtro, copie las etiquetas de columna para las columnas que desea en la primera fila del área donde pegará las filas filtradas. Al aplicar el filtro, introduzca una referencia a las etiquetas de columna copiadas en el cuadro Copiar a. Las filas copiadas, por tanto, incluirán solo las columnas para las que ha copiado las etiquetas.

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

Para mover el cuadro de diálogo Filtro avanzado de forma temporal mientras selecciona el rango de criterios, haga clic en

Contraer diálogo . 7. Siguiendo este ejemplo, el resultado filtrado para el rango de la lista es:





Varios criterios, varias columnas, todos los criterios verdaderos Lógica booleana: (Tipo = "Alimentos" Y Ventas > 1000)


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


Alimentos

>1000

3. Haga clic en una celda del rango de la lista. Siguiendo el ejemplo, haga clic en cualquier celda del rango, A6:C10. 4. En el grupo Ordenar y filtrar de la pestaña Datos, haga clic en Avanzadas.


moverla a otro lugar. o Para filtrar el rango de lista copiando las filas que cumplen los criterios a otra área de la hoja de cálculo, haga

clic en Copiar a otro lugar, haga clic en la casilla Copiar a y, después, haga clic en la esquina superior izquierda del área donde quiere pegar las filas.



49

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




alimentos Buchanan 6328 $


Varios criterios, varias columnas, cualquier criterio verdadero Lógica booleana: (Tipo = "Alimentos" O Vendedor = "Buchanan")


2. Para buscar filas que cumplen varios criterios en varias columnas en las que cualquier criterio puede ser verdadero, escriba los criterios en las diferentes columnas y filas del rango de criterios. Siguiendo el ejemplo, escriba:


Alimentos

=Buchanan

3. Haga clic en una celda del rango de la lista. Siguiendo el ejemplo, haga clic en cualquier celda del rango de la lista, A6:C10.

4. En el grupo Ordenar y filtrar de la pestaña Datos, haga clic en Avanzadas.




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.

6. En el cuadro Rango de criterios, escriba la referencia, incluidos los rótulos de criterios. Siguiendo el ejemplo, escriba $A$1:$B$3.







50

Varios conjuntos de criterios, una columna en todos los conjuntos Lógica booleana: ( (Ventas > 6000 Y Ventas < 6500 ) O (Ventas < 500) )


2. Para buscar las filas que cumplen varios conjuntos de criterios, en los que cada conjunto incluye criterios para una columna, incluya varias columnas con el mismo encabezado de columna.

Tipo Vendedor Ventas Ventas

>6000 <6500

<500







6. En el cuadro Rango de criterios escriba la referencia, incluidos los rótulos de criterios. Siguiendo el ejemplo, escriba $A$1:$D$3.






Varios conjuntos de criterios, varias columnas en cada conjunto Lógica booleana: ( (Vendedor = "Davolio" Y Ventas >3000) O (Vendedor = "Buchanan" Y Ventas > 1500) )


2. Para buscar las filas que cumplen varios conjuntos de criterios, en los que cada conjunto incluye criterios para varias columnas, escriba cada conjunto de criterios en columnas y filas separadas. Siguiendo el ejemplo, escriba:


="=Davolio" >3000


51

=Buchanan >1500







6. En el cuadro rango de criterios, especifique la referencia de rango de criterios, incluidos los rótulos de criterios. En el ejemplo, escriba $A$1:$C$3.To mover el cuadro de diálogo Filtro avanzado de forma temporal mientras selecciona el

rango de criterios, haga clic en Contraer diálogo . 7. Siguiendo el ejemplo, el resultado filtrado para el rango de la lista es:




Criterios de carácter comodín Lógica booleana: Vendedor = un nombre con la letra "u" en segundo lugar

1. Para buscar valores de texto que compartan algunos caracteres pero no otros, siga uno o varios de estos procedimientos:

o Escriba uno o más caracteres sin un signo igual (=) para buscar las filas que tengan un valor de texto en una columna que comienza con esos caracteres. Por ejemplo, si escribe el texto Dav como criterio, Excel encontrará "Davolio", "David" y "Davis".

o Use un carácter comodín.

Utilice Para buscar

? (signo de interrogación) Cualquier carácter Por ejemplo, sm?th buscará "gracia" y "Grecia"

* (asterisco) Cualquier número de caracteres Por ejemplo, * este buscará "Nordeste" y "Sudeste"

~ (tilde) seguida de ?, *, o ~ Un signo de interrogación, un asterisco o una tilde ¿Por ejemplo, af91 ~? busca "fy91?"


3. En las filas situadas bajo los rótulos de columna, escriba los criterios que desea buscar.


52


Ca*

="=?u*"



6. Siga uno de los procedimientos siguientes: o Para filtrar el rango de la lista ocultando las filas que no cumplen los criterios, haga clic en Filtrar la lista sin




7. En el cuadro Rango de criterios, escriba la referencia, incluidos los rótulos de criterios. Siguiendo el ejemplo, escriba $A$1:$B$3.




Bebidas Suyama 5122 $



Insertar subtotales en una lista de datos de una hoja de cálculo

Puede calcular subtotales y totales generales en una lista de una columna de forma automática mediante en comando Subtotal.

Importante: Los subtotales no son compatibles con las tablas de Excel. El comando Subtotal aparecerá atenuado si está trabajando con una tabla de Excel. Para agregar subtotales en una tabla, debe convertir primero la tabla a un rango normal de datos y, a continuación, agregar el subtotal. Tenga en cuenta que al hacerlo se quitará de los datos toda la funcionalidad de la tabla, excepto el formato de tabla. También puede crear una tabla dinámica.

Al insertar subtotales: • Los subtotales se calculan con una función de resumen, como Suma o Promedio, con la función SUBTOTAL. Puede

mostrar más de un tipo de función de resumen de cada columna.

• Los totales generales se derivan de datos de detalle, no de los valores de los subtotales. Por ejemplo, si usa la función de resumen Promedio, la fila total general muestra un promedio de todas las filas de detalle de la lista, no un promedio de los valores de las filas de subtotales.

https://support.office.com/es-es/article/descripci%C3%B3n-general-de-las-tablas-de-excel-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

https://support.office.com/es-es/article/crear-una-tabla-din%C3%A1mica-para-analizar-datos-de-una-hoja-de-c%C3%A1lculo-a9a84538-bfe9-40a9-a8e9-f99134456576


53

Si el libro se establece para calcular fórmulas automáticamente, el comando Subtotal vuelve a calcular los valores del subtotal y del total general a medida que modifica los datos. El comando Subtotal también esquematiza la lista de modo que pueda mostrar u ocultar las filas de detalle de cada subtotal.

Insertar subtotales

Nota: Si filtra datos que contiene los subtotales, los subtotales pueden aparecer ocultos. Para mostrarlos de nuevo, borre todos los filtros. Para obtener más información sobre cómo aplicar filtros, vea Inicio rápido: Filtrar datos con un filtro automático.

1. Asegúrese de que todas las columnas de un intervalo de datos para el que desea calcular los subtotales tienen una etiqueta en la primera fila, contienen hechos similares y, asimismo, que el rango no incluye filas o columnas en blanco.

2. Seleccione una celda del rango. 3. Siga uno de los procedimientos siguientes:

Insertar un nivel de subtotales Puede insertar un nivel de subtotales para un grupo de datos, como se muestra en el ejemplo siguiente.

1. En cada cambio en la columna Deporte... 2. …calcula el subtotal de la columna Ventas.

a. Para ordenar la columna que contiene los datos que desea agrupar, seleccione dicha columna y, en la ficha Datos, en el grupo Ordenar y filtrar, haga clic en Ordenar de A a Z o en Ordenar de Z a A.

b. En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

https://support.office.com/es-es/article/inicio-r%C3%A1pido-filtrar-datos-con-un-filtro-autom%C3%A1tico-08647e19-11d1-42f6-b376-27b932e186e0


54

Se mostrará el cuadro de diálogo Subtotales. c. En el cuadro Para cada cambio en, haga clic en la columna cuyos subtotales desee calcular. Por ejemplo, en el

ejemplo anterior seleccionaría Deporte. d. En el cuadro Usar función, haga clic en la función de resumen que desee usar para calcular los subtotales. Por

ejemplo, en el ejemplo anterior seleccionaría Suma. e. En el cuadro Agregar subtotal a, active la casilla correspondiente a cada columna que contenga valores cuyos

subtotales desee calcular. Por ejemplo, en el ejemplo anterior seleccionaría Ventas. f. Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de

página entre grupos. g. Para especificar una fila de resumen encima de la fila de detalles, desactive la casilla Resumen debajo de los

datos. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla Resumen debajo de los datos. Por ejemplo, en el ejemplo anterior esta casilla se desactivaría.

h. O bien, puede utilizar de nuevo el comando Subtotales repitiendo los pasos del uno al siete para agregar más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de verificación Reemplazar subtotales actuales.

Insertar niveles anidados de subtotales Puede insertar subtotales para grupos internos, anidados dentro de sus grupos externos correspondientes, como se muestra en el ejemplo siguiente.

1. En cada cambio en la columna externa Región... 2. …calcula el subtotal de las Ventas para esa región y en cada cambio en la columna interna Deporte.

i. Para ordenar la columna que contiene los datos que desea agrupar, seleccione dicha columna y, en la ficha Datos, en el grupo Ordenar y filtrar, haga clic en Ordenar de A a Z o en Ordenar de Z a A.

j. Inserte los subtotales externos.

Cómo insertar los subtotales externos i. En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

Se mostrará el cuadro de diálogo Subtotales. ii. En el cuadro Para cada cambio en, haga clic en la columna de los subtotales externos. En el ejemplo

anterior, haría clic en Región.


55

iii. En el cuadro Usar función, haga clic en la función de resumen que desea usar para calcular los subtotales. Por ejemplo, en el ejemplo anterior seleccionaría Suma.

iv. En el cuadro Agregar subtotal a, active la casilla de verificación correspondiente a cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, seleccionaría Ventas.

v. Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.

vi. Para especificar una fila de resumen encima de la fila de detalles, desactive la casilla Resumen debajo de los datos. Para especificar una fila de resumen debajo de la fila de detalles, active la casilla Resumen debajo de los datos. Por ejemplo, en el ejemplo anterior esta casilla se desactivaría.

vii. O bien, puede utilizar de nuevo el comando Subtotales repitiendo los pasos del uno al seis para agregar más subtotales con diferentes funciones de resumen. Para evitar que se sobrescriban los subtotales existentes, desactive la casilla de verificación Reemplazar subtotales actuales.

k. Inserte los subtotales anidados.

Cómo insertar los subtotales anidados i. En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

Se mostrará el cuadro de diálogo Subtotales. ii. En el cuadro Para cada cambio en, haga clic en la columna del subtotal anidado. En el ejemplo

anterior, seleccionaría Deporte. iii. En el cuadro Usar función, haga clic en la función de resumen que desea usar para calcular los

subtotales. Por ejemplo, en el ejemplo anterior seleccionaría Suma. Seleccione las demás opciones que desee.

iv. Desactive la casilla de verificación Reemplazar subtotales actuales. l. Repita el paso anterior para más subtotales anidados, empezando desde los más externos.

Sugerencia: Para mostrar un resumen solamente de los subtotales y de los totales generales, haga clic en los símbolos de esquema que aparecen junto a los números de fila. Utilice los símbolos y para mostrar u ocultar las filas de detalle de los subtotales individuales.

Eliminar subtotales Seleccione una celda del rango que contiene los subtotales.

1. En el grupo Esquema de la ficha Datos, haga clic en Subtotal.

2. En el cuadro de diálogo Subtotal, haga clic en Quitar todos.

Aplicar la validación de datos a celdas

Puede usar la validación de datos para restringir el tipo de datos o los valores que los usuarios escriben en una celda. Uno de los usos más comunes de la validación de datos es Crear una lista desplegable.

1. Seleccione una o más celdas para validar. 2. En la pestaña Datos, del grupo Herramientas de datos, haga clic en Validación de datos.

https://support.office.com/es-es/article/crear-una-lista-desplegable-7693307a-59ef-400a-b769-c5402dce407b


56

3. En la pestaña Configuración, en el cuadro Permitir, seleccione Lista.

4. En el cuadro Origen, escriba los valores de la lista, separados por comas. Por ejemplo: a. Para limitar una respuesta a dos opciones (por ejemplo "¿Tiene hijos?"), escriba Sí,No. b. Para limitar la reputación de la calidad de un proveedor a tres clasificaciones, escriba Baja, Media, Alta.

Nota: Generalmente, estos pasos solo se recomiendan para los elementos de lista que probablemente nunca cambien. Si tiene una lista que pueda cambiar, o si necesita agregar o quitar elementos de vez en cuando, le irá mejor si sigue el paso del siguiente procedimiento recomendado.

Procedimiento recomendado: opciones de la lista que pueden presentar cambios

También puede crear entradas de la lista mediante referencia a un rango de celdas ubicadas en otra parte del libro. La manera más eficaz es crear la lista, después aplicar el formato como una tabla de Excel (desde la pestaña Inicio, seleccione Estilos > Dar formato como tabla > elija el estilo de tabla que sea mejor para usted). Después, seleccione el rango de cuerpo de datos de la tabla, que es la parte de la tabla que solo tiene la lista, no el encabezado de la tabla (en este caso, Departamento), y proporciónele un nombre significativo en el cuadro Nombre encima de la columna A.


57

1. En lugar de escribir los valores de la lista en el cuadro Origen de la validación de datos, agregue el nombre que acaba de definir precedido de un signo igual (=) o presione la tecla de función F3; dicha tecla de función muestra uncuadro con todos los nombres de rango que contiene el libro actual, seleccione el nombre de rango que asignó a la lista, este nombre aparecerá en el cuadro Origen: precedido de un signo =


58

2. Lo mejor sobre usar una tabla es que a medida que agrega o quita elementos de la lista, su lista de validación de datos

se actualizará automáticamente.

3. Nota: Es mejor colocar sus listas en una hoja de cálculo independiente (oculta si fuera necesario) de forma que nadie pueda editarlas.

4. Asegúrese de que esté activada la casilla Celda con lista desplegable.

5. Para especificar cómo quiere administrar los valores en blanco (nulos), active o desactive la casilla Omitir blancos.

Nota:

Si los valores permitidos se basan en un rango de celdas que tiene un nombre definido y existe una celda en blanco en cualquier lugar de ese rango, al seleccionar la casilla Omitir blancos permite que cualquier valor se escriba en la celda validada. Esto también se cumple para cualquier celda a la que se haga referencia mediante fórmulas de validación: si cualquier celda a la que se hace referencia está en blanco, al seleccionar la casilla Omitir blancos permite que cualquier valor se escriba en la celda validada.

6. Pruebe la validación de datos para asegurarse de que funciona correctamente. Trate de escribir datos válidos y no

válidos en las celdas para asegurarse de que la configuración funciona como pretende y que los mensajes están

apareciendo como espera.

Notas:

Tras crear su lista desplegable, asegúrese de que funciona de la manera que quiera. Por ejemplo, puede que quiera comprobar si la celda es lo bastante ancha para mostrar todas sus entradas.

Si la lista de entradas para lista desplegable está en otra hoja de cálculo y desea evitar que los usuarios la vean o realicen cambios, piense en ocultar y proteger dicha hoja de cálculo.

Eliminar validación de datos seleccione la celda o celdas que contienen la validación que desea eliminar, luego vaya a Datos > Validación de datos y en el diálogo de validación de datos, presione el botón Borrar todo y después Aceptar.

Agregar otros tipos de validación de datos

Restringir la entrada de datos a números enteros dentro de límites.

1. En la ficha datos acceda al comando Validación Datos. 2. En la lista Permitir, seleccione Número entero. 3. En el cuadro Datos, seleccione el tipo de restricción que desee. Por ejemplo, para definir los límites superior e inferior,

seleccione entre.

https://support.office.com/es-es/article/la-celda-es-lo-bastante-ancha-db30658d-0c0b-44ad-825f-55f1cb4d9957


59

4. Escriba el valor mínimo, máximo o específico que se va a permitir.

También puede escribir una fórmula que devuelva un valor de número. Por ejemplo, supongamos que está validando datos en la celda F1. Para establecer un límite mínimo de deducciones en dos veces el número de secundarios en dicha celda, seleccione mayor o igual que en el cuadro Datos y escriba la fórmula =2*F1, en el cuadro Mínimo.

Restringir la entrada de datos a un número decimal dentro de límites. 1. En la ficha datos acceda al comando Validación Datos. 2. En el cuadro Permitir, seleccione Decimal. 3. En el cuadro Datos, seleccione el tipo de restricción que desee. Por ejemplo, para definir los límites superior e inferior,

seleccione entre. 4. Escriba el valor mínimo, máximo o específico que se va a permitir.

También puede escribir una fórmula que devuelva un valor de número. Por ejemplo, para definir un límite máximo de comisiones y bonificaciones del 6 % del sueldo de un vendedor en la celda E1, seleccione menor que o igual a en el cuadro Datos y escriba la fórmula, =E1*6%, en el cuadro Máximo.

Nota: Para permitir a un usuario que especifique porcentajes, por ejemplo, 20%, seleccione Decimal en el cuadro Permitir, seleccione el tipo de restricción que desea en el cuadro Datos, especifique el valor mínimo, máximo y, a continuación, visualice la celda de validación de datos como porcentaje seleccionando la celda y haciendo clic en Estilo

porcentual en el grupo Número de la pestaña Inicio.

Restringir la entrada de datos a una fecha dentro de un intervalo de fechas. 1. En la ficha datos acceda al comando Validación Datos. 2. En el cuadro Permitir, seleccione Fecha. 3. En el cuadro Datos, seleccione el tipo de restricción que desee. Por ejemplo, para permitir las fechas posteriores a un

día determinado, seleccione mayor que. 4. Escriba la fecha de inicio, de finalización o específica que se va a permitir.

También puede escribir una fórmula que devuelva una fecha. Por ejemplo, para establecer un período de tiempo entre la fecha de hoy y 3 días desde la fecha actual, seleccione entre en el cuadro Datos, escriba =HOY() en el cuadro Fecha de inicio y escriba =HOY()+3 en el cuadro Fecha de finalización.


60

Restringir la entrada de datos a una hora dentro de un período de tiempo. 1. En la ficha datos acceda al comando Validación Datos. 2. En el cuadro Permitir, seleccione Hora. 3. En el cuadro Datos, seleccione el tipo de restricción que desee. Por ejemplo, para permitir horas antes de una hora

determinada del día, seleccione menor que. 4. Escriba la hora de inicio, de finalización o la hora específica que desee permitir. Si desea especificar horas específicas,

use el formato de hora hh:mm. Por ejemplo, supongamos que tiene la celda E2 configurada con una hora de inicio (8:00) y la celda F2 con una hora de finalización (17:00) y quiere limitar las horas de la reunión entre esas horas. Seleccione entre en el cuadro Datos, escriba =E2 en el cuadro Hora de inicio y escriba =F2 en el cuadro Hora de finalización.

Restringir la entrada de datos a texto de una longitud específica. 1. En la ficha datos acceda al comando Validación Datos. 2. En el cuadro Permitir, seleccione Longitud del texto. 3. En el cuadro Datos, seleccione el tipo de restricción que desee. Por ejemplo, para permitir un número máximo de

caracteres, seleccione menor que o igual a. 4. En este caso queremos limitar la entrada a 25 caracteres, así que seleccione menor o igual a en el cuadro Datos y

escriba 25 en el cuadro Máximo.


61

Calcular una validación según el contenido de otra celda. 1. En la ficha datos acceda al comando Validación Datos. 2. tipo de datos que quiera. 3. En el cuadro Datos, seleccione el tipo de restricción que desee. 4. En el cuadro o los cuadros situados debajo del cuadro Datos, haga clic en la celda que desee usar para especificar qué se

permite. Por ejemplo, para permitir entradas para una cuenta solo si el resultado no superará el presupuesto de la celda E1, seleccione Permitir > Número completo, datos, menor que o igual a, y Máximo >= =E1.


62

Fórmulas en validación de datos

Nota: Los ejemplos siguientes usan la opción Personalizado donde escribe fórmulas para configurar sus condiciones. No tiene por qué preocuparse de lo que muestre el cuadro Datos, ya que está deshabilitado con la opción Personalizado.

Para asegurarse de que Escriba esta fórmula

La celda que contiene un id. de producto (C2) comienza siempre con el prefijo estándar de “ID”, y tiene al menos 10 (superior a 9) caracteres de longitud.

=Y(IZQUIERDA(C2, 3) ="ID-",LEN(C2) > 9)

La celda que contiene un nombre de producto (D2) solo contiene texto.

=ESTEXTO(D2)


63


La celda que contiene un cumpleaños (B6) debe ser mayor que el número de años que establezca en la celda B4.

=SI(B6<=(HOY()-(365*B4)),VERDADERO,FALSO)

Todos los datos del rango de celdas A2:A10 contienen valores únicos.

=CONTAR.SI($A$2:$A$10,A2)=1

Nota: Debe escribir la fórmula de validación de datos para la celda A2 primero, después copiar A2 a A3:A10 de forma que el segundo argumento de CONTAR.SI coincida con la celda actual. Es decir, la parte A2)=1 cambiará a A3)=1, A4)=1 y así sucesivamente. Para obtener más información


64


Asegúrese de que las entradas de direcciones de correo electrónico en la celda B4 contienen el símbolo @.

=ESNUMERO(BUSCAR("@",B4)


65

Sección 3: Funciones de Bases de Datos

BDSUMA

La Función BDSUMA de Excel se usa para sumar todos los valores de una columna, que cumplan con uno o mas criterios aplicados en otras columnas.

Sintaxis de la Función BDSUMA La función BDSUMA usa tres argumentos obligatorios:

Base de datos: comprende la selección de toda la tabla, incluyendo los rótulos o etiquetas de las columnas. Nombre de campo: este elemento indica la columna sobre la que se aplica función, donde se realizará la suma, este nombre se escribe entre comillas, de forma alternativa se puede indicar el número de columna relativa (respecto de la base de datos seleccionada) . Criterios: se especifican las condiciones o criterios que se emplearán para determinar cuales de los elementos de la base de datos deben ser tomados en consideración para la ejecución de la suma. Los criterios deben incluir por lo menos un rótulo y una condición debajo de este rótulo.

Ejemplo de la Función BDSUMA Asumamos que se dispone de una base de datos como la mostrada en la siguiente imagen, en la que muestra la lista de personal, con sus salarios correspondientes.


66

Se desea determinar salario total del personal de VENTAS, para ello necesitamos sumar solo aquellos valores correspondientes al equipo de ventas.

Para resolver el caso emplearemos la siguiente función: =BDSUMA(B2:F13,F2,B16:F17) En esta función el primer parámetro es el rango B2:F13 el cual corresponde a toda la base de datos empleada. En el segundo parámetro se uso F2 el cual corresponde al SALARIO, en lugar de emplear esta celda se pudo usar “Salario“ o el número 5, ya que en la base de datos la quinta columna corresponde al salario. En el tercer y último parámetro se encuentran los campos y los filtros que se emplearán, en este caso se usa el rango B16:F17, los cuales contienen un encabezado similar al de la base de datos, no es necesario emplear todos los rangos, podríamos emplear una fórmula como: =BDSUMA(B2:F13,5,E16:F17)

BDSUMA con Múltiples Criterios En donde solo se usan dos campos de toda la base de datos, se obtiene el mismo resultado que la función BDSUMA empleada originalmente. Claro esta que al emplear la función BDSUMA, se pueden emplear varios criterios al mismo tiempo, a fin de que se apliquen varios criterios a la vez, tal como se muestra en el siguiente ejemplo, en el que se busca determinar el sueldo total de los que trabajan en VENTAS, son del género M (Masculino) y tienen un salario superior a 1500.

BDPROMEDIO

La función BDPROMEDIO de excel es de gran ayuda cuando es necesario obtener promedios sobre distintos valores de una base de datos, a diferencia de la función PROMEDIO, BDPROMEDIO puede trabajar con los nombres de campo de la base de datos y así obtener la información requerida de la misma. usen BDPROMEDIO cuando trabajen con bases extensas en cuanto al número de campos de esta y que sea necesario promediar varios de estos datos… la función BDPROMEDIO devuelve el promedio de los valores de una columna de una lista o base de datos que coinciden con las condiciones especificadas.


67

Sintaxis BDPROMEDIO(base_de_datos;nombre_de_campo;criterios) Base_de_datos es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna. Nombre_de_campo indica qué columna se utiliza en la función. Nombre_de_campo puede ser texto con el rótulo encerrado entre dobles comillas, como por ejemplo “Edad” o “Campo”, o como un número que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. Criterios es el rango de celdas que contiene las condiciones especificadas. Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un rótulo de columna y por lo menos una celda debajo del rótulo de columna que especifique una condición de columna.

Ejemplo vean un ejemplo de esta función..

vean como se usa el nombre del campo de marca para indicar que se va a promediar una marca y se usa el nombre de una marca para indicar cual de todas se promedia, además se indica el nombre del campo que contiene las cantidades a promediar, de esta manera queda una formula muy sencilla con la que sacamos el costo promedio de rayo solar.. =BDPROMEDIO(B3:I19,F3,E23:E24)

BDCONTAR

La función BDCONTAR en Excel cuenta los registros dentro de una columna numérica que permanecen después de aplicar un grupo de criterios sobre los datos. La función se puede utilizar sobre cualquier rango de celdas siempre y cuando se incluya un nombre para cada columna.

Sintaxis de la función BDCONTAR La función BDCONTAR tiene tres argumentos obligatorios:

http://soluexcel.blogspot.com/2009/11/todas-las-funciones-de-excel.html


68

• Base_de_Datos (obligatorio): El rango de celdas que compone la base de datos en donde haremos la consulta. La primera fila del rango contiene los nombres de cada columna.

• Nombre_de_campo (obligatorio): La columna, que contiene datos numéricos y sobre la cual se hará la cuenta de registros. Es posible escribir el nombre de la columna o el número que represente la posición de la columna dentro del rango.

• Criterios (obligatorio): El rango de celdas donde están especificadas las condiciones que deben cumplirse. El rango de criterios debe tener en la primera fila el nombre de la columna a la cual se aplicará el criterio.

Ejemplo de la función BDCONTAR En la siguiente imagen podrás observar una base de datos en el rango A4:D9 que contiene información de productos. Deseo saber la cantidad de registros de la tabla que tienen un IDProducto mayor a 25032.

La condición a cumplir está indicada en el rango A1:A2 el cual tiene un título de columna idéntico al título de la tabla donde están los datos. La función que he colocado en la celda G2 es la siguiente: =BDCONTAR(A4:D9, "IDProducto", A1:A2) El primer argumento es precisamente el rango de celdas que contiene los datos. El segundo argumento es el nombre de la columna que tiene los datos numéricos que serán contados. El tercer argumento especifica las celdas donde se encuentra la condición que se debe cumplir. La función DBCONTAR de la celda G1 devuelve el resultado correcto porque solamente existen dos registros que tienen un IDProducto mayor a 25032 que son las filas 8 y 9.


69

Criterios para varias columnas En el ejemplo anterior solamente usé un criterio para la columna IDProducto, sin embargo es posible indicar a la función BDCONTAR un criterio para más de una columna. En el siguiente ejemplo obtendré la cuenta de los registros que tienen un IDPRODUCTO diferente a 25032 y que además tengan una cantidad de 5:

La diferencia más importante con el primer ejemplo es que he ampliado el rango de criterios para incluir la celda B2. =BDCONTAR(A4:D9, "IDProducto", A1:B2) Solamente la fila 6 cumple con la condición de tener un IDProducto diferente a 25032 y además tener una cantidad igual a 5. Como te puedes dar cuenta, es posible indicar un criterio para cada columna y la función BDCONTAR contará los registros que cumplan con todas la condiciones. En el siguiente ejemplo puedes observar que he indicado un criterio adicional para la columna Costo Total:

El único cambio es de nuevo el tercer argumento de la función BDCONTAR para indicar el rango correcto donde se encuentran todos los criterios a cumplir. Bajo estas condiciones, son las filas 8 y 9 que cumplen con todos los criterios. Múltiples criterios para una misma columna También es posible indicar a la función BDCONTAR varios criterios para una misma columna y lo único que debemos hacer es colocar el nuevo criterio en una nueva fila dentro del rango de criterios. Observa el siguiente ejemplo:


70

La función BDCONTAR contará los registros que tengan una cantidad igual a 2 e igual a 5 que incluye a los registros en las filas 6, 7, y 8. Ahora observa el siguiente ejemplo:

En este caso la función BDCONTAR contará los registros que tienen una cantidad igual a 2 y además los registros que tienen una cantidad igual a 5 que además tienen un costo unitario mayor a 5. En este ejemplo solo las filas 6 y 7 cumplen con los criterios especificados. Así que ahora ya sabes que al trabajar con bases de datos, la función BDCONTAR nos ayudará a contar aquellos registros que cumplan con los criterios que especifiquemos.

Nota importante: Si tus datos no tienen un título de columna como lo requiere la función BDCONTAR, entonces puedes utilizar la función CONTAR.SI.CONJUNTO y podrás obtener resultados similares.

BDMAX

Obtiene el valor máximo de una columna de la base de datos considerando solo los registros que cumplen con los criterios establecidos.

Sintaxis BDMAX(base_de_datos, nombre_de_campo, criterios)

• base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna

https://exceltotal.com/la-funcion-contar-si-conjunto-en-excel/


71

• nombre_de_campo (obligatorio): La columna de la cual se obtendrá el valor máximo.

• criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir.

Ejemplos BDMAX(A4:E20, “Edad”, A1:E2) = Devuelve el valor máximo de la columna “Edad” que cumple con las condiciones específicadas en A1:E2

BDMIN

La función BDMIN pertenece a la categoría de funciones de base de datos de Excel y es utilizada para obtener el número más pequeño dentro de una columna tomando en cuenta un criterio previamente establecido. La función BDMIN tiene tres argumentos que describiré a continuación:

• Base_de_datos: el rango que contiene los datos a los cuales se aplicará el criterio.

• Nombre_de_campo: La columna que contiene los datos numéricos que serán evaluados para obtener el valor mínimo.

• Criterios: El rango de celdas que contiene los criterios. Los tres argumentos de la función BDMIN son obligatorios y deben cumplir ciertos requisitos. El primer argumento de la función indica el rango de datos y debe incluir los encabezados de columna. La función BDMIN no funcionará correctamente si los datos no tienen encabezados ya que son utilizados para relacionar los datos y los criterios. El segundo argumento es el nombre de la columna donde se obtendrá el valor mínimo. Lo más común es ingresar el nombre de la columna encerrada entre comillas dobles, pero también es posible indicar el número de la columna. Finalmente, el tercer argumento es un rango de celdas donde se especifican los criterios a utilizar en la búsqueda del valor mínimo. Los criterios deberán estar identificados por el nombre de la columna a la cual serán aplicados.

Ejemplo con la función BDMIN En la siguiente imagen observas un rango de datos que comienza en la fila 5 y que tienes los encabezados de columna en color negro.

El rango de datos comienza en la celda B5 y termina hasta la celda D27 por lo que nuestro rango de datos tiene la dirección B5:D27. Los encabezados que observas en color azul en la fila 2 de la hoja, serán utilizados para los criterios y lo comprenderás mejor en unos instantes al momento de crear la fórmula la cual ingresaré en la celda G2.

1. En la celda G2 ingresaré el nombre de la función BDMIN y el primer argumento que es el rango que contiene los datos. =BDMIN(B5:D27,

2. Como segundo argumento ingresaré el nombre de la columna que contiene los valores numéricos que serán evaluados para obtener el mínimo. =BDMIN(B5:D27, "Ventas",

3. El último argumento de la función es el rango que contiene los criterios y que en este ejemplo es el rango B2:D3. =BDMIN(B5:D27, "Ventas", B2:D3)


72

El rango de criterios incluye los encabezados de columna que están resaltados en color azul, y que se encuentran en la fila 2 de la hoja, y además incluye las celdas que están por debajo de dichos encabezados. En este momento, las celdas de la fila 3 están vacías y es como si no tuviéramos criterios para la función así que al pulsar la tecla Entrar tendremos el siguiente resultado.

Ya que no hemos indicado criterios todavía, obtenemos como resultado el valor mínimo en toda la columna “Ventas”. A continuación, en la celda C3 que está debajo del encabezado “Zona”, ingresaré la palabra Nortepara indicarle a la función BDMIN que quiero obtener el valor mínimo de las ventas en la zona norte. El resultado será el siguiente:


73

Observa que la fórmula no cambió en lo absoluto, pero el resultado de la celda G3 se ha actualizado para mostrarnos el valor mínimo considerando solo aquellas filas que pertenecen a la zona Norte. Ahora observa cómo cambia el resultado si ingreso el texto Oeste en la celda C3:

En este ejemplo, la función BDMIN nos devuelve el valor mínimo de aquellos valores que pertenecen a la zona Oeste. Como puedes observar, solo hemos utilizado el criterio para la columna Zona y las otras celdas de criterios (B3 y D3) han permanecido vacías. Es común utilizar un arreglo de cedas como el que observas en el ejemplo anterior, donde el rango de criterios incluye la misma cantidad de columnas que los datos, pero eso no es obligatorio, sino que es ampliamente utilizado de esta manera porque es muy práctico ya que nos permite indicar fácilmente un criterio para cada columna. En los próximos ejemplos entenderás claramente los beneficios a los que me refiero.

El rango de criterios de la función BDMIN El rango de criterios de la función BDMIN puede estar en cualquier ubicación del libro y no necesariamente por arriba de los datos como en el ejemplo anterior. El único requisito es que dicho rango debe contener al menos el nombre de una columna de los datos. En la siguiente imagen puedes observar que solo tengo el nombre de la columna Zona en la celda E1 y por debajo, en la celda E2, el nombre de la zona de la cual quiero obtener el valor mínimo y que es la zona Oeste.


74

La fórmula para obtener el valor mínimo con dicho criterio es la siguiente: =BDMIN(A1:C23, "Ventas", E1:E2) El rango de datos tiene la misma cantidad de filas que los ejemplos anteriores, pero ahora comienza en la celda A1 y por lo tanto coloco la referencia A1:C23. El segundo argumento es la columna que contiene los valores numéricos y el tercer argumento es el rango que contiene los criterios y que en este caso solo incluye el criterio para la columna Zona que está indicado en el rango E1:E2. El resultado es el siguiente:

Como has observado, no es obligatorio que el rango de criterios de la función BDMIN tenga las mismas columnas de los datos. Solo es importante que tengan el mismo nombre en el encabezado ya que esa es la manera en que la función puede asociar adecuadamente el criterio con la columna de los datos. Al ejemplo anterior, agregaré un criterio para la columna Ventas donde indicaré a la función BDMIN que quiero obtener el valor mínimo que sea mayor a 2500. Dicho criterio lo colocaré en la columna F como lo muestra la siguiente imagen:


75

El único cambio que debo hacer a la fórmula es el rango de criterios para que la función considere el nuevo valor de la columna F: =BDMIN(A1:C23, "Ventas", E1:F2) De esta manera, la función solamente incluirá en la evaluación aquellas filas que sean de la zona Oeste y que sean mayores a 2500. El resultado es el siguiente:

Varias filas de criterios en la función BDMIN Ya hemos visto que es posible indicar criterios para cada una de las columnas de los datos, pero también es posible indicar más de un criterio para una sola columna y lo único que debemos hacer es aumentar las filas en el rango de criterios. En la siguiente imagen puedes observar que he colocado dos celdas por debajo del criterio de Zona y eso le pedirá a la función BDMIN que devuelva el valor mínimo entre las filas que pertenezcan a la zona Norte o Sur.


76

Observa la fórmula de la celda F1 y verás que el tercer argumento hace referencia al rango A1:C3 que incluye las primeras 3 filas de la hoja. El resultado es el valor mínimo después de combinar los valores de las dos zonas. También puedo pedir que la función BDMIN considere valores mayores a 2500 y eso me devolverá un resultado diferente tal como se observa en la siguiente imagen.

Al utilizar múltiples líneas para los criterios de la función BDMIN deberás ser lo más específico posible para tener la certeza de que la función tomará solamente las filas adecuadas para hacer la evaluación.

Florida 439 piso 4 oficina 2 CABA · Manual Excel Intermedio 4 Sección 1: Gráficas Tipos de...

Documents

Transcript of Florida 439 piso 4 oficina 2 CABA · Manual Excel Intermedio 4 Sección 1: Gráficas Tipos de...