Guía y Quinto      Taller 
 de Contabilización de Operaciones 
 Comerciales y Financieras.   Ficha 2329080

ORDENAR Y FILTRAR DATOS EN EXCEL 

Cada encabezado de columna de una tabla en Excel tiene un botón de flecha en su extremo derecho el cual es conocido como el botón de filtro y cuando hacemos clic sobre él se muestran diferentes opciones para ordenar y filtrar datos en Excel.

ORDENAR UNA TABLA EN EXCEL

Al pulsar el botón de filtro en el encabezado de una columna veremos algunas opciones de ordenación de datos. Al aplicar un criterio de ordenación a una tabla de Excel las filas se reorganizarán de acuerdo al contenido de la columna por la cual estamos ordenando. Si la columna contiene datos de tipo texto, entonces podremos ordenar los valores de A a Z (alfabéticamente) o de Z a A.

Por el contrario, si la columna contiene datos numéricos, entonces podremos ordenar de menor a mayor o también podremos hacerlo de mayor a menor. En el caso de que tengamos fechas podremos ordenar de más antiguo a más reciente o viceversa.

Otra opción de ordenación que nos da Excel es la de ordenar por color y eso hará que las celdas se ordenen de acuerdo al color de fondo de las celdas. Esta opción es relevante solamente cuando hemos aplicado algún formato condicional a las celdas para modificar su color de fondo o el color de la fuente.

MÚLTIPLES CRITERIOS DE ORDENACIÓN

Es posible ordenar una tabla indicando diferentes criterios de ordenación. Por ejemplo, es posible ordenar los datos primero por fecha, después por país y posteriormente por región. Para lograr este tipo de ordenación haremos uso del comando Inicio > Ordenar y filtrar > Orden personalizado lo cual mostrará el siguiente cuadro de diálogo:

La parte superior del cuadro de diálogo Ordenar contiene botones que nos permiten establecer múltiples niveles de ordenación. A continuación explico cada uno de esos botones:

• Agregar nivel: Nos permite agregar un nuevo nivel de ordenación.
• Eliminar nivel: Si ya no deseamos que se aplique un criterio de ordenación podemos eliminarlo de la lista.
• Copiar nivel: Hace una copia del nivel seleccionado.
• Flechas arriba y abajo: Nos permiten mover los niveles de ordenación hacia arriba o hacia abajo para establecer un orden preferido.
• Opciones: Con este botón podemos indicar si la ordenación de datos va a distinguir entre mayúsculas y minúsculas.

La ordenación de los datos se hará comenzando por el nivel superior y hacia abajo tomando en cuenta tanto la columna como el criterio de ordenación especificado en cada nivel.

FILTRAR DATOS EN UNA TABLA DE EXCEL

Cuando hablamos de filtrar datos en una tabla de Excel nos referimos a desplegar solamente aquellas filas que cumplen con los criterios especificados. Los datos de la tabla no son eliminados ni alterados sino que solamente se ocultan a la vista. Las opciones de filtrado se muestran también al pulsar el botón de flecha que se encuentra al lado de los encabezados de columna.

En la imagen anterior observamos las opciones de filtrado que Excel provee cuando la columna tiene datos de tipo texto. La opción Filtros de texto muestra varias opciones a elegir como Es igual a, Comienza por, Contiene, etc. Cualquiera de estas opciones mostrará el cuadro de diálogo Autofiltro personalizado que nos permitirá indicar el detalle del filtro que deseamos aplicar.

En este cuadro de diálogo podemos especificar dos condiciones para una misma columna y elegir si deseamos que se cumplan ambas o solamente una de ellas. Al hacer clic en el botón Aceptar se aplicará el filtro a los datos.

En la parte inferior del menú de filtrado observamos una lista de los valores únicos de la columna y cada opción tiene a su lado una caja de selección que podemos marcar o desmarcar indicando si deseamos ver u ocultar aquellas filas que contienen dicho valor. Si la lista tiene tantos valores únicos que se nos dificulta encontrar la opción que necesitamos, entonces podemos utilizar el cuadro de búsqueda de manera que podamos encontrar los valores adecuados y seleccionarlos.

FILTROS DE NÚMEROS Y FECHA

En la sección anterior revisamos los Filtros de texto que provee Excel, pero las opciones que se muestran cuando el tipo de datos es un número o una fecha serán diferentes. Por ejemplo, en la siguiente imagen puedes observar las opciones de Filtros de fecha.

Excel reconoce el tipo de dato contenido en una columna y muestra los criterios de filtrado correspondientes. En la siguiente imagen puedes observar las opciones de Filtros de número:

Algunas de las opciones de filtros mostrarán el cuadro de diálogo Autofiltro personalizado para permitirnos indicar el detalle del criterio de filtrado a aplicar, pero otras opciones como el filtro de fecha Hoy se aplicará de inmediato sobre los datos. Lo mismo sucederá con el filtro de número Diez mejores que no necesita de ningún parámetro adicional y se aplicará de inmediato sobre la columna.

En el siguiente link hay un ejemplo:

https://www.youtube.com/watch?v=ElWveAQUyps

Taller

Seleccionamos, copiamos la siguiente base de datos y la pegamos en Excel

Nombre	Empleo	Departamento	Sección	Salario	Fecha  comienzo
Hazel	Auxiliar Contable	Contabilidad	Copiadoras	$ 27.598	3/02/1986
Liza	Ing. Mecánico	Ingeniería	Impresoras	$ 43.394	26/01/1986
Robert	Aux. Admin. Unidad	I + D	Impresoras	$ 28.044	16/04/1992
Howard	Aux. Diseño	Diseño	Copiadoras	$ 25.176	16/04/1991
Maxine	Aux. Admin. Unidad	Mercado	Copiadoras	$ 26.041	8/04/1991
Joe	Director Unidad	Administración	Copiadoras	$ 116.511	25/10/1979
Gail	Especialista Diseño	Diseño	Fax	$ 36.940	20/09/1987
Alyssa	Ing. Mecánico	Ingeniería	Impresoras	$ 47.883	12/09/1987
Sheryl	Aux. Diseño	Diseño	Impresoras	$ 23.239	7/08/1992
Brad	Ingeniero Jefe	Ingeniería	Copiadoras	$ 105.753	30/07/1979
Kendrick	Aux. Administrativo	Mercado	Impresoras	$ 29.983	1/04/1986
Ellen	Aux. Administrativo	Administración	Impresoras	$ 29.983	24/03/1986
Felix	Investigador	I + D	Impresoras	$ 64.738	29/10/1988
Mark	Auxiliar Contable	Contabilidad	Impresoras	$ 26.646	21/01/1990
Allen	Aux. Admin. Unidad	Contabilidad	Impresoras	$ 28.044	13/01/1990
Ari	Ing. Software	Ingeniería	Copiadoras	$ 56.177	7/07/1987
Sam	Representante	Mercado	Copiadoras	$ 31.914	18/04/1991
Toni	Aux. Admin. Unidad	Ingeniería	Fax	$ 23.036	10/04/1991
Jeremy	Ing. Software	Ingeniería	Copiadoras	$ 34.002	13/10/1992
Frank	Respon. Producto	Mercado	Fax	$ 46.486	4/01/1985
Ralph	Group Mgr.	Mercado	Impresoras	$ 77.179	27/12/1984
Linda	Aux. Administrativo	Administración	Fax	$ 26.114	3/01/1985
Burt	Aux. Administrativo	Administración	Impresoras	$ 35.786	26/12/1984
Matt	Representante	Mercado	Impresoras	$ 51.340	20/11/1987
Brent	Técnico	Ingeniería	Impresoras	$ 40.897	14/09/1984
Robert	Ing. Mecánico	Ingeniería	Fax	$ 58.358	6/09/1984
Sandrae	Aux. Técnico	Ingeniería	Copiadoras	$ 24.854	21/12/1989
Phillip	Contable	Contabilidad	Impresoras	$ 30.452	13/12/1989
Randy	Aux. Administrativo	Mercado	Fax	$ 27.081	6/08/1984
Ariel	Ing. Técnico	Ingeniería	Impresoras	$ 58.326	9/01/1986
Bobby	Director Ingeniero	Ingeniería	Fax	$ 79.280	29/07/1984
Donald	Ing. Software	Ingeniería	Impresoras	$ 56.177	3/03/1988
Ellen	Técnico	Ingeniería	Fax	$ 30.411	4/10/1986
Nate	Respon. Producto	Mercado	Impresoras	$ 43.487	26/09/1986
Tuome	Técnico	Ingeniería	Impresoras	$ 30.411	22/02/1989
Anna	Contable	Contabilidad	Copiadoras	$ 31.539	14/02/1989
Mary	Ing. Software	Ingeniería	Impresoras	$ 42.872	11/05/1989
Tadeuz	Técnico	Ingeniería	Copiadoras	$ 29.362	16/04/1990
Midori	Aux. Admin. Unidad	I + D	Fax	$ 28.044	8/04/1990
Lisa	Aux. Técnico	Ingeniería	Impresoras	$ 31.068	18/09/1982
Bill	Investigador Jefe	I + D	Impresoras	$ 59.455	10/09/1982
Susan	Ing. Técnico	Ingeniería	Copiadoras	$ 69.070	6/05/1986
Sara	Respon. Producto	Mercado	Impresoras	$ 49.485	9/08/1990
Alice	Aux. Admin. Unidad	Ingeniería	Copiadoras	$ 33.051	1/08/1988
Cindy	Técnico	I + D	Impresoras	$ 34.605	22/07/1988
Tammy	Aux. Administrativo	Administración	Fax	$ 32.884	11/03/1983
Sandra	Investigador Jefe	I + D	Fax	$ 57.756	3/03/1983
Everett	Investigador	I + D	Impresoras	$ 53.685	27/11/1987
Pete	Representante	Mercado	Fax	$ 34.689	8/09/1977
David	Investigador Jefe	I + D	Copiadoras	$ 59.455	31/08/1977
Jules	Ing. Técnico	Ingeniería	Impresoras	$ 53.721	12/11/1986
Pam	Aux. Administrativo	Ingeniería	Fax	$ 29.983	19/06/1985
Jessica	Ing. Mecánico	Ingeniería	Copiadoras	$ 46.387	11/06/1985
Tom	Auxiliar Contable	Contabilidad	Impresoras	$ 28.550	6/03/1985
Karen	Director Ingeniero	Ingeniería	Impresoras	$ 62.590	26/02/1985
Leslie	Ing. Software	Ingeniería	Impresoras	$ 44.351	18/06/1990
Tommie	Aux. Administrativo	Ingeniería	Impresoras	$ 25.147	28/01/1987
Iain	Aux. Administrativo	Administración	Impresoras	$ 25.147	20/01/1991
Alexandra	Investigador	I + D	Copiadoras	$ 41.053	11/05/1986
Karen	Técnico	Ingeniería	Impresoras	$ 29.362	26/11/1981
Lisa	Aux. Administrativo	Administración	Copiadoras	$ 27.081	18/11/1981
Evelyn	Respon. Producto	Mercado	Copiadoras	$ 41.987	16/10/1982
Lise-Anne	Técnico	Ingeniería	Impresoras	$ 34.605	15/07/1988
Bob	Contable	Contabilidad	Fax	$ 35.889	7/07/1988
Sam	Ing. Técnico	Ingeniería	Fax	$ 50.651	9/02/1986
Theo	Aux. Administrativo	Mercado	Copiadoras	$ 29.015	14/12/1987
Rose	Contable	Contabilidad	Impresoras	$ 32.627	6/12/1987
Dennis	Ing. Software	Ingeniería	Copiadoras	$ 44.351	6/06/1988
Sherrie	Aux. Administrativo	Ingeniería	Copiadoras	$ 29.983	16/04/1990
Dave	Aux. Admin. Unidad	I + D	Copiadoras	$ 31.048	8/04/1990
Frank	Auxiliar Contable	Contabilidad	Fax	$ 27.598	25/03/1992
Janet	Representante	Mercado	Copiadoras	$ 33.301	26/10/1981
Jaime	Aux. Admin. Unidad	Contabilidad	Fax	$ 29.045	17/03/1992
Jason	Aux. Administrativo	Administración	Copiadoras	$ 23.212	18/10/1981
Eric	Investigador	I + D	Fax	$ 37.896	6/11/1987
Ed	Especialista Diseño	Diseño	Fax	$ 38.094	20/10/1984
Cathy	Ing. Mecánico	Ingeniería	Copiadoras	$ 79.307	12/10/1984
Isolde	Técnico	Ingeniería	Fax	$ 29.362	6/08/1990
Steven	Aux. Admin. Unidad	Mercado	Impresoras	$ 28.044	29/07/1990
Mark	Respon. Producto	Mercado	Impresoras	$ 58.482	10/02/1978
Sam	Director Unidad	Mercado	Copiadoras	$ 97.096	2/02/1978
Alex	Director Unidad	Administración	Impresoras	$ 79.061	12/02/1978
Melia	Especialista Diseño	Diseño	Impresoras	$ 35.785	18/03/1982
Stephanie	Ingeniero Jefe	Ingeniería	Impresoras	$ 61.856	10/03/1982
Eileen	Especialista Diseño	Diseño	Copiadoras	$ 28.859	24/12/1990
Aaron	Aux. Administrativo	Administración	Fax	$ 24.180	16/12/1990
Malcolm	Respon. Producto	Mercado	Fax	$ 37.489	18/12/1984
Daoud	Aux. Técnico	Ingeniería	Fax	$ 21.304	4/03/1989
Caroline	Director Ingeniero	Ingeniería	Copiadoras	$ 70.935	25/02/1980
Michael	Ing. Software	Ingeniería	Fax	$ 35.480	8/07/1990
Rowena	Aux. Administrativo	Mercado	Impresoras	$ 31.917	28/09/1989
Megan	Aux. Admin. Unidad	Mercado	Impresoras	$ 33.051	20/09/1989
Melissa	Director Unidad	Administración	Fax	$ 72.820	15/05/1984
Barbara	Aux. Técnico	Ingeniería	Impresoras	$ 28.405	5/12/1986
Lisa	Respon. Producto	Mercado	Fax	$ 47.986	27/11/1986
Ursula	Contable	Contabilidad	Copiadoras	$ 26.101	20/06/1989
Ellen	Contable	Contabilidad	Fax	$ 47.853	12/06/1989
Miguel	Ing. Técnico	Ingeniería	Fax	$ 67.535	8/01/1984
Carla	Respon. Producto	Mercado	Copiadoras	$ 62.981	12/10/1981
Rich	Group Mgr.	Mercado	Fax	$ 104.565	4/10/1981
Jean	Aux. Admin. Unidad	Contabilidad	Copiadoras	$ 28.044	13/11/1986
Kris	Aux. Administrativo	Administración	Fax	$ 27.081	5/11/1986
Hilda	Representante	Mercado	Impresoras	$ 66.603	22/10/1987
Rob	Aux. Diseño	Diseño	Fax	$ 23.239	17/11/1991
Felice	Aux. Administrativo	Administración	Copiadoras	$ 23.212	9/11/1991
Charles	Representante	Mercado	Copiadoras	$ 58.278	13/04/1982
Sara	Respon. Producto	Mercado	Copiadoras	$ 35.989	1/01/1993
Erika	Office Manager	Administración	Impresoras	$ 65.822	5/04/1982
Cara	Representante	Mercado	Impresoras	$ 58.278	14/04/1989
Larry	Auxiliar Contable	Contabilidad	Copiadoras	$ 21.888	7/05/1991
Robin	Aux. Admin. Unidad	Ingeniería	Impresoras	$ 23.036	29/04/1991
Mollie	Especialista Diseño	Diseño	Copiadoras	$ 30.014	13/06/1980
Wes	Ing. Software	Ingeniería	Fax	$ 48.786	9/09/1991
Jay	Ingeniero Jefe	Ingeniería	Fax	$ 51.879	5/06/1980

Encontrar:

 

Guía y Tercer      Taller 
 de Contabilización de Operaciones 
 Comerciales y Financieras.   Ficha 2329080

Referencias relativas

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo,  la fórmula :

=C1*2

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

=C2*2

=C3*2

=C4*2

Cuando copiamos fórmulas Excel utiliza el tipo de referencia relativa que es el comportamiento predeterminado. Este tipo de referencia se adapta al contexto de la nueva celda cambiando las referencias de acuerdo a la nueva ubicación. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda específica sin importar que copiemos la fórmula a otra ubicación y para ello necesitamos utilizar las referencias absolutas.

 

Referencias mixtas

Las referencias mixtas son aquellas donde solamente se fija la columna ($A1) o solamente se fija la fila(A$1).

Atajo para crear referencias absolutas y mixtas

Una manera muy sencilla de crear una referencia absoluta es editar la fórmula y posicionar el cursor sobre la referencia relativa que deseamos convertir en referencia absoluta. Posteriormente debemos presionar la tecla F4 lo cual insertará el signo $ tanto para la columna como para el número de fila.

Si pulsas dos veces la tecla F4 observarás que Excel  solamente inserta el signo $ para la fila y si pulsas la tecla F4 tres veces se creará una referencia mixta con la columna fija. Al pulsar una cuarta vez la tecla F4 se removerán todos los signos $ y tendremos de nuevo una referencia relativa. 

Referencia	Ejemplo	Descripción
Relativa	=A1	La columna y la fila pueden cambiar al momento de copiar la fórmula.
Absoluta	=$A$1	Ni la columna ni la fila pueden cambiar.
Mixta	=$A1	La columna no cambia, solamente la fila puede cambiar.
Mixta	=A$1	La fila no cambia, solamente la columna puede cambiar.

REFERENCIA   ABSOLUTA

Cuando se trabaja algún tipo de informe en Excel encontramos que necesitamos hacer una formula que se repite en varias celdas, con el objetivo de ahorrar tiempo y precisión al momento de ejecutar un cálculo.

Excel nos ofrece la posibilidad de fijar celdas:

Para trabajar con referencias absolutas se debe especificar escribiendo el signo $ delante de la letra de la columna y del número de fila.

Por ejemplo $A$3 indica que siempre será la celda A3 y, al aplicar llenados -hacia abajo o hacia la derecha-, u operaciones de copiar y pegar, las referencias que tengan el signo $ delante no serán modificadas. 

Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Digitar el siguiente ejercicio

TALLER

Ejercicios con  referencia relativa  absoluta y mixta

Encontrar la venta total de los siguientes productos:

1)

2)

 

TALLER  2

Taller Ferias

2) Encontrar la rentabilidad ganada por la venta del novillo. 

Se compro el novillo, se crió y se vendió. Encontrar el valor de la compra, gasto en crianza y la venta total del novillo.   Valor Total = Cantidad * Precio  Unitario Subtotales = sumas de Valores Totales Parciales Total de Costos =  Suma de Subtotales Ingreso Neto = Venta del Novillo - Total de Costos Rentabilidad = Ingreso neto/Total de Costos *100

 

Guía y Segundo      Taller 
 de Contabilización de Operaciones 
 Comerciales y Financieras.   Ficha 2329080

Funciones Básicas

En Excel existen gran variedad de funciones que puede aplicar a sus tablas o datos. Una función puede ser utilizada únicamente para obtener un resultado o juntarlo con una fórmula y obtener mayor capacidad de operación.

Existen funciones básicas en Excel para obtener resultados simples y sencillos y será un buen punto de partida para comenzar a entrar al mundo de las funciones.

LA FUNCIÓN SUMA

Como su nombre lo dice, esta función permite sumar valores de las celdas, se utiliza mejor cuando se suman rangos. La sintaxis de la función SUMA es la siguiente:

1. =SUMA(Número1:[Número2])

Con la nueva fórmula  se digita la palabra Suma, se abre el paréntesis, se digita la primera celda (es B4), se agregan los dos puntos, se digita la última celda (f4   Excel automáticamente selecciona todo el rango)  y se cierra el paréntesis. 

Queda de esta forma:  =Suma(b4:f4)

 El Resultado es el mismo, pero la fórmula es mas corta.

LA FUNCIÓN PROMEDIO

La función Promedio en Excel sirve para obtener la media aritmética entre un grupo de valores que le pasemos como parámetros. Básicamente, lo que hace es sumar todos los valores que le pasemos y los divide entre el conteo de los mismos.

=PROMEDIO(Número1:Número2)

Retomando el ejercicio vamos a encontrar el promedio de la venta de autos de cada día en la semana. Quedaría:

Ejercicio sumando las celdas y lo dividimos en la cantidad de celdas sumadas

 El resultado

Da el mismo resultado

LA FUNCIÓN Máxima

Nos permite  obtener el valor máximo de un conjunto de valores, Ejemplo la venta máximo, la nota máxima, el sueldo máximo...

Sintaxis:

1. =MAX(Número1:Número2)

Ahora mostramos la máxima venta de autos en el día y la semana

Resultado

LA FUNCIÓN Mínima

 La función MIN nos devuelve el valor mínimo de una lista de valores. Es lo contrario de la función máxima.

Sintaxis:

=MIN(Número1:Número2)

Ahora mostramos la venta mínima del día y la semana.

Resultado mínimo

LA FUNCIÓN CONTAR

La función contar cuenta la cantidad de celdas seleccionadas

Sintaxis:

=CONTAR(Número1;[Número2])

Ahora vamos a contar la cantidad de ventas del siguiente ejercicio

Resultado

                 

                                           TALLER

Resolver los siguientes ejercicios en Excel

1) Encontrar la nota final de los siguientes aprendices, junto con el promedio de notas, nota máxima,  mínima y la cantidad de notas.

2) Encontrar el total de ventas de autos en el presente año