14
Aplicar orden alfabético formulas matemáticas y de texto para solución de problemas. 9 “G” Integrantes: Emilse Maldonado JheniferLema Marcela Arroyo Zaraí Camacho Kevin Lisintuña Alexander Maigua Anderson Neto

Ordenar los datos de una lista alfabéticamente

Embed Size (px)

Citation preview

Page 1: Ordenar los datos de una lista alfabéticamente

Aplicar orden alfabético formulas matemáticas y de

texto para solución de problemas.

9 “G”

Integrantes:

Emilse Maldonado

JheniferLema

Marcela Arroyo

Zaraí Camacho

Kevin Lisintuña

Alexander Maigua

Anderson Neto

Page 2: Ordenar los datos de una lista alfabéticamente

Ordenar los datos de una lista alfabéticamente

A veces puede ser muy útil ordenar alfabéticamente, o de mayor a menor los datos de

un listado.

Situaremos el cursor sobre cualquier celda que vayamos a ordenar. (Sólo en una

casilla)

A continuación, pulsaremos alguno de estos dos botones:

El primero ordenará de forma ascendente los datos, y el segundo lo hará de

forma descendente.

Como podrás comprobar, además de esa columna, los datos de las adyacentes también

se han movido, para que las filas no pierdan relación.

Para resolver problemas tan comunes como este, nos dirigiremos al menú Datos y la

opción Ordenar. Aparecerá una ventana como la siguiente:

Tenemos tres casillas para establecer diferentes criterios de ordenación alfabética.

Junto a cada uno de ellos, podemos determinar que sea ascendente o descendente.

Page 3: Ordenar los datos de una lista alfabéticamente

Fórmula para ordenar datos en Excel

Excel nos permite ordenar la información rápidamente utilizando el comando Ordenar

y filtrar, pero en ocasiones necesitamos una fórmula para ordenar datos en Excel y

en esta ocasión te mostraré una alternativa para hacerlo.

Los datos que utilizaremos en nuestro ejemplo tienen una columna de texto y otra

columna de números. En el primer ejemplo ordenaremos los datos por los datos

numéricos y en el segundo ejemplo ordenaremos por los valores de texto.

Fórmula para ordenar datos numéricos

Para nuestro primer ejemplo haremos un ordenamiento descendente de la columna

Calificación. La función que nos permitirá hacer esto de una manera sencilla será

la función JERARQUIA.EQV la cual obtiene la posición de un número dentro de un

rango. Es importante mencionar que esta función está disponible a partir de Excel

2010, para versiones anteriores debes utilizar la función JERARQUIA. Ahora

considera la siguiente fórmula:

=JERARQUIA.EQV(B2,$B$2:$B$9)

Esta fórmula obtiene la posición del valor en B2 dentro del rango B2:B9 que para

nuestro ejemplo será la posición 3, observa el resultado:

Page 4: Ordenar los datos de una lista alfabéticamente

La columna Posición asigna el número 1 al valor más alto dentro del rango y a partir de

ahí se enumeran todos los registros. Puedes ver que la posición 8 corresponde a la

calificación 61 la cual es la más baja de todas las calificaciones. Ahora que ya tenemos

las posiciones podemos ordenar los datos utilizando como referencia la columna

posición y utilizando la función INDICEy la función COINCIDIR de la siguiente

manera:

=INDICE($A$2:$A$9,COINCIDIR(E2,$C$2:$C$9,0))

La función INDICE obtiene un valor dentro de un rango con solo especificar su fila.

Con esta fórmula estamos buscando dentro del rango A2:A9 y la fila la obtenemos al

hacer coincidir el número 1 de la columna E con el número 1 de la columna C (Posición)

que para nuestro ejemplo es Roberto:

Page 5: Ordenar los datos de una lista alfabéticamente

Al copiar la fórmula hacia abajo obtenemos automáticamente el nombre de cada

alumno de acuerdo a su posición. Para obtener el dato de la calificación usamos una

fórmula idéntica a la anterior con la diferencia de que la función INDICE hará la

búsqueda sobre el rango B2:B9:

Con esto hemos ordenado los datos de manera descendente utilizando una columna

numérica. Si queremos hacer un ordenamiento ascendente debemos hacer un solo

cambio en la función JERARQUIA.EQV de la columna Posición colocando un número 1

como el tercer argumento de la función lo cual hará que los datos se ordenen de

manera ascendente. Observa la fórmula de la celda C2 y el resultado del ordenamiento

en los datos:

En este caso los datos se ordenan comenzando por la calificación de menor valor hasta

la de mayor valor. Así que con solo modificar el tercer argumento de la función

JERARQUIA.EQV podrás controlar el tipo de ordenamiento de los datos.

Page 6: Ordenar los datos de una lista alfabéticamente

Desempate de valores numéricos

Nuestro ejemplo anterior funcionó a la perfección porque no tenemos calificaciones

repetidas entre los alumnos pero observa lo que sucede si Ignacio y Brenda tienen la

misma calificación:

Al tener dos datos numéricos del mismo valor, la función JERARQUIA.EQV determina

un empate entre ambos y les otorga la misma posición, que en nuestro ejemplo es la

posición 5, pero la siguiente posición otorgada será la posición 7 saltándose la posición

6 y por lo tanto generando un error en nuestra tabla ordenada.

Para solucionar este problema utilizaré una columna auxiliar donde sumaré a cada

calificación el número de fila donde se encuentran los datos. De esta manera, la

calificación 77 de Sonia en la columna auxiliar tendrá un valor de 77.000002 porque se

encuentra en la fila 2 de la hoja de Excel, la calificación de 72 de Ignacio tendrá un

valor de 72.000003. La fórmula utilizada en la columna auxiliar será la siguiente:

=B2+0.000001*FILA()

Otro cambio que debemos hacer es modificar la fórmula de la columna Posición para

que la función JERARQUIA.EQV haga el ordenamiento utilizando la columna auxiliar.

El resultado es el siguiente:

Page 7: Ordenar los datos de una lista alfabéticamente

Observa que nuestra tabla de datos ordenada ya no muestra el error ocasionado por la

repetición de calificaciones de Ignacio y Brenda.

Fórmula para ordenar texto en Excel

Para ordenar texto en Excel utilizando fórmulas es importante recordar la manera en

que la herramienta trabaja con texto especialmente al utilizar operadores de

comparación. Observa la siguiente tabla de datos:

Al utilizar el operador Mayor que (>) para comparar dos textos, Excel nos responderá

si el primero de ellos tiene una posición mayor dentro del alfabeto. Para la celda C2 el

resultado es falso porque la letra A no está después de la letra B. Sin embargo, para

la celda C3 el resultado es verdadero porque la letra Z está efectivamente después de

la letra A.

Lo mejor de todo es que esta comparación funciona correctamente para palabras y lo

puedes ver con los ejemplos de las últimas dos filas. El hecho de que Excel nos

devuelva este resultado al comparar dos textos nos facilitará mucho el ordenamiento

con fórmulas. Para llegar a la solución de ordenamiento utilizaremos la función

CONTAR.SI la cual nos ayudará a contar la cantidad de cadenas de texto que se

encuentran ubicadas alfabéticamente antes de otra. Considera la siguiente fórmula:

Page 8: Ordenar los datos de una lista alfabéticamente

=CONTAR.SI($A$2:$A$9,"<="&A2)

La función CONTAR.SI contará todos los elementos del rango A2:A9 que sean

menores o iguales que el valor de la celda A2 y de esta manera sabremos la cantidad

de valores que le anteceden alfabéticamente. Observa el resultado de esta fórmula:

En nuestro ejemplo Brenda tiene la posición 1 porque la función CONTAR.SI encuentra

que solo el texto de la misma celda A8 es menor o igual a Brenda. La posición 2 es para

Ignacio porque Brenda e Ignacio son los valores ubicados en una posición alfabética

menor o igual dentro del rango. Para generar la tabla con los datos ordenados

utilizamos la misa técnica que en el ejemplo anterior con la función INDICE y

COINCIDIR:

De esta manera hemos ordenado los datos de manera ascendente por la columna

Nombre. Si queremos ordenarlos de manera descendente será suficiente con cambiar

el operador de comparación utilizado en la condición de la función CONTAR.SI por un

símbolo Mayor qué (>). En la siguiente imagen puedes ver el cambio en la fórmula lo

cual ocasiona un cambio en la posición y ordenamiento de los datos:

Page 9: Ordenar los datos de una lista alfabéticamente

Desempate de posiciones en valores de texto

Al igual que con los números, si tenemos dos celdas con el mismo texto tendremos un

error en los datos ordenados así que debemos utilizar un criterio de desempate

similar al explicado anteriormente utilizando una columna auxiliar con la siguiente

fórmula:

=CONTAR.SI($A$2:$A$9,"<="&A2) + 0.000001*FILA()

Como ya vimos en el ejemplo anterior, la función CONTAR.SI nos devuelve la cantidad

de elementos que preceden alfabéticamente a la celda A2 y a ese resultado le

sumaremos el número de fila para asegurarnos de que sea un valor único dentro de la

columna auxiliar. Para obtener la posición final utilizaremos la función

JERARQUIA.EQV de la siguiente manera:

=JERARQUIA.EQV(C2,$C$2:$C$9,1)

Finalmente modificamos las fórmulas en la tabla de datos para que la función

COINCIDIR consulte el resultado de la función JERARQUIA.EQV. Observa cómo se

ordenan los datos de manera ascendente por la columna de texto y las dos celdas con

el mismo valor aparecen juntas:

Page 10: Ordenar los datos de una lista alfabéticamente

Fórmulas Matemáticas Y Estadísticas En Excel Funciones Matemáticas y Estadísticas en Excel

Funciones básicas

Suma =(B4+E4)

Resta =(B4-E4)

Multiplicación =(B4*E4)

División =(B4/E4)

Promedio

Corresponde a la media aritmética de la muestra

=average(B4:E4) ó =promedio(B4:E4)

Contar Celdas

Para contar los valores numéricos existentes en toda la matriz

=CONTAR(B1:B7)

Para contar las celdas que están VACÍAS en toda la matriz

=CONTAR.BLANCO(C8:E11)

Para contar las celdas que NO ESTÁN VACÍAS en toda la matriz

=CONTARA(A1:A7)

Para contar las celdas que -POR EJEMPLO- mayores que cero

=CONTAR.SI( C8:E11 ; ">0" )

Contar celdas con valores tipo texto

=CONTAR.SI( C8:E11 ; "ok" )

Máximo

Corresponde al mayor valor del rango

=MAX( C42:E45 )

Corresponde al menor valor del rango

Mínimo

=MIN( C42:E45 )

Moda

Corresponde al valor que más veces se repite del rango

Page 11: Ordenar los datos de una lista alfabéticamente

=MODA( C42:E45 )

Mediana

Corresponde al valor central de la muestra o rango

=MEDIANA( C42:E45 )

Sumas de rangos y sumas condicionadas

Para suma de rangos es importante delimitar cada uno de ellos en la función

separándolos con punto y coma, como cualquier otro argumento de función.

=SUMA( D98 ; C99:E99 ; D100:E100 )

La suma condicionada permite sumar en función de una condición marcada.

=SUMAR.SI( C104:C108 ; ">4" ; D104:D108 )

Valor absoluto

El valor absoluto de un número se define como el valor de un número sin considerar su

signo, por ejemplo el valor absoluto de 4 es 4 y el valor absoluto de -4 es también 4.

=ABS(B1)

Valor entero

En Excel la fórmula para el valor entero de un número da como resultado el entero

menor más próximo al número. Esta fórmula no es un redondeo, simplemente la parte

entera del número. Por ejemplo:

=ENTERO(9.3) El resultado es 9

=ENTERO(9.9) El resultado también es 9

Número Entero

Esta función me permite obtener la parte ENTERA de un número

(redondeo a cero decimales)

=ENTERO( S )

Page 12: Ordenar los datos de una lista alfabéticamente

Solución de problemas al guardar y cerrar archivos

Al guardar un archivo, se guarda con una extensión adicional de nombre de archivo,

.xls.

Al guardar un archivo con una extensión distinta de la extensión predeterminada para

ese tipo de archivo, el programa de Microsoft Office agrega la extensión

predeterminada al nombre del archivo. Por ejemplo, un nombre de archivo para una

presentación de Microsoft PowerPoint podría aparecer como Presupuesto.abc.ppt.

Para guardar un archivo con una extensión distinta de la extensión predeterminada,

escriba el nombre completo del archivo entre comillas: por ejemplo,"Presupuesto.abc".

Al guardar un archivo, en su nombre aparecen dos puntos (..)

En Microsoft Windows, los nombres de archivo pueden contener ciertos caracteres de

puntuación, como comas o puntos. Al guardar un archivo con un nombre acabado en

punto, como por ejemplo, Ventas., los programas de Microsoft Office agregan otro

punto y la extensión predeterminada al nombre del archivo. Por ejemplo, un documento

de Microsoft Word guardado como Ventas. aparecerá como Ventas.doc. Si guarda un

archivo con la extensión predeterminada, no es necesario escribir un punto detrás del

nombre del archivo. Los programas de Office agregan el punto automáticamente al

agregar la extensión.

El formato de archivo que se busca no está en la lista del cuadro de diálogo Guardar

como.

Asegúrese de que el formato de archivo que desea está incluido en Microsoft Excel y

está instalado en su equipo.

Al intentar guardar cambios, aparece un mensaje indicando que el libro es de sólo

lectura

No se puede cambiar un archivo de sólo lectura. Para guardar los cambios, asigne un

nombre nuevo al archivo mediante el comando Guardar como, en el menú Archivo.

Puede utilizar el mismo nombre si guarda el archivo en una carpeta nueva o distinta.

Aunque se ha especificado el nombre de usuario y la contraseña no se puede tener

acceso a una unidad de red.

Asegúrese de que introduce el nombre de usuario y la contraseña correctos y que la

tecla BLOQ MAYÚS no está activada. Si todavía no tiene acceso al servidor de red,

consulte al administrador de la red.

Page 13: Ordenar los datos de una lista alfabéticamente

Al guardar en formato dBASE, se pierden parte de los datos.

NOTA Si guarda una hoja de cálculo de Microsoft Excel en formato dBASE (DB2,

DB3 o DB4) y la hoja de cálculo contiene un rango denominado "Base de datos",

solamente se guardarán en el archivo dBASE los datos comprendidos en el rango. Si se

agregan nuevos registros después de asignar un nombre al rango, antes de guardar la

hoja en formato dBase, deberá definirse otra vez el rango para que incluya los nuevos

registros.

Si no existe ningún rango denominado "Base de datos" en la hoja, únicamente se

convertirán los datos de laregión actual. Si la primera fila de datos contiene texto,

Excel lo utilizará como fila de encabezado para definir los nombres de los campos. Si

la primera fila contiene únicamente números, Excel creará nombres de campos como

N1, N2 y así sucesivamente.

Asegúrese de que las cadenas de caracteres son más cortas que el ancho de las

columnas Si se guardan datos en formato dBASE, Excel asignará un tipo de datos

a cada campo (columna de datos) basado en los datos del campo en el primer registro

del rango de la base de datos o de la región actual.

Si un campo en el primer registro contiene texto, se asignará a ese campo el tipo de

datos de caracteres y todos los números que contenga el campo en otras filas se

convertirán en cadenas de caracteres. El ancho de columna del campo determina la

longitud de la cadena de caracteres; las cadenas de caracteres con una longitud mayor

que el ancho de columna se cortarán en dBASE. Para impedir que se pierdan los datos,

seleccione el rango que desee convertir en Excel y aplique una fuente de un solo

espacio como Courier. Para adaptar el tamaño de las columnas para que muestren

todos los datos, elija Columnas en el menú Formatoy, a continuación, haga clic

en Autoajustar a la selección. Los campos de datos numéricos no pueden contener

texto; cualquier texto que aparezca en un campo de datos numéricos será nulo.

Utilice un formato de número que no sea General Si los números decimales

tienen el formato de número General, los decimales se cortarán en dBASE. Antes de

guardar los datos en formato dBase, hay que aplicar otro formato de número a todos

los datos de ese campo. En el menú Formato, haga clic en Celdas y después en la

ficha Número. En el cuadro Categoría, haga clic en Número o Científica y, a

continuación, especifique el número de decimales que desee.

Cambie los datos de tiempo a datos de texto Los campos que contienen datos

de tiempo no se pueden convertir. Antes de guardar los datos en formato dBASE,

pueden cambiarse los datos de tiempo utilizando la función de la hoja de cálculo

Page 14: Ordenar los datos de una lista alfabéticamente

TEXTO. Por ejemplo, para cambiar la hora 12:34 a datos de texto, utilice la función

=TEXTO("12:34", "hh:mm a.m./p.m.").

Al intentar guardar el libro, aparecen errores, advertencias y mensajes acerca

de volver a publicar automáticamente.

El libro que guarda está configurado para volver a publicar automáticamente

elementos en una página Web. Se muestra un mensaje para informarle de que vuelve a

publicar automáticamente en una página Web, ofreciéndole la oportunidad de

continuar o deshabilitar volver a publicar automáticamente. Para obtener más

información, haga clic en Ayuda en el mensaje.

También puede aparecer un mensaje alertándole sobre advertencias y errores

asociados con los datos que vuelve a publicar; por ejemplo, si intenta volver a publicar

un rango que incluye referencias externas, se le advertirá de que se volverán a

publicar los valores (y no las referencias). Para obtener más información sobre

advertencias y errores específicos, haga clic en Ayuda en el mensaje.