Excel

Aprende a usar la función sumaproducto en Excel con este práctico ejemplo

Usa sumaproducto para calcular promedios ponderados

En Excel, es increíblemente sencillo calcular el promedio de varias celdas: solo usa la función promedio. ¿Que pasa si algunos de los valores tienen más peso que otros? Por ejemplo, en muchas clases los exámenes valen más que las tareas. Para estas situaciones necesitamos un promedio ponderado.

Aunque Excel no tiene una función de promedio ponderado, si tiene una función que hace la mayoría de la chamba por ti: SumaProducto. Incluso si nunca has usado sumaproducto antes, serás capaz de usarla como un profesional para el final de este artículo. El método que estamos usando funciona con todas las versiones de Excel.

Puedes bajar el ejemplo en esta liga.

SumaProducto

Armando la hoja de calculo en Excel

Para calcular el promedio ponderado, necesitarás al menos 2 columnas. La primera columna (columna B en nuestro ejemplo) contiene las calificaciones para cada test asignado. La segunda columna (Columna C) contiene los pesos. Un peso más alto causará que la tarea o el examen tenga un efecto mayor en la calificación final.

Puedes pensar en el peso como el porcentaje de la calificación final. Pero en este caso, los pesos si suman más de 100%. Eso está bien porque nuestra fórmula aun así funcionará sin importar cuanto sumen nuestros pesos.

Escribiendo la fórmula sumaproducto en Excel

Ahora que ya tenemos nuestra hoja de cálculo lista, agregaremos la fórmula a la celda B10 (cualquier celda vacía debería de funcionar). Como con cualquier fórmula, comenzamos con el signo de igual.

La primera parte de nuestra fórmula será la función sumaproducto. Por qué los argumentos van en paréntesis, hay que abrir paréntesis, siempre:

=sumaproducto(

Ahora agregaremos los argumentos a la función. Sumaproducto puede tener cualquier número de argumentos, peor por lo general tendrá 2. En nuestro ejemplo, el primer argumento será el rango B2:B9 – las celdas con las calificaciones:

=sumaproducto(B10:B17

El segundo argumento será el rango de celda C2:C9 – las celdas que contienen los pesos. Necesitarás usar una coma para separar los dos argumentos. Cuando estés listo, cierra el paréntesis.

=sumaproducto(B10:B17, C10:C17)

Ahora agregaremos la segunda parte de nuestra fórmula: esta parte dividirá SUMAPRODUCTO por la suma de los pesos. Después, hablaremos de por qué esto es importante.

Comienza escribiendo el signo de división / y luego escribe la función suma:

=sumaproducto(B10:B17, C10:C17)/Suma(

Ahora solo necesitamos un argumento para la función suma: el rango C10:C17. Recuerda cerrar los paréntesis después del argumento:

=SumaProducto(B10:B17, C10:C17)/Suma(C10:C17)

Listo, Excel calculará el promedio ponderado.

¿Cómo funciona sumaproducto en Excel?

Analicemos cada parte de esta fórmula para ver como fusiona. Sumaproducto multiplica cada una de las calificaciones por el peso que le corresponde a la tarea o examen, y luego suma todos esos productos. Así que hace lo que su nombre dice: suma los productos. Para la tarea 1, multiplica 85 por 5, y para el examen multiplica 83 por 25.

¿Por qué tenemos que multiplicar los valores? Básicamente tómalo como que las tareas y exámenes con mayor valor deben de ser contadas más veces. Por ejemplo, la tarea 2 es contada 5 veces, pero el examen final cuenta por 45. Por eso el examen final tiene mayor impacto en la calificación final.

Un promedio “regular” le asignaría el mismo peso a cada tarea y examen.

Por sí mismo, sumaproducto nos dará un número enorme. Por eso es necesario traer la segunda parte, la suma, a nuestra fórmula. Al dividir entre el resultado de la suma de la columna c, nuestra respuesta vuelve a una calificación sobre 100.

La segunda parte de la fórmula nos sirve porque nos permite corregir los cálculos. Si recuerdas, las sumas de todas las calificaciones no suman 100. Esta fórmula se encarga de arreglar esto. Por ejemplo, si aumentamos el peso de alguna tarea, la segunda parte de la formula simplemente divide por un número más alto, trayéndola de regreso a la respuesta correcta.

Más información

¿Te gustó la sumaproducto? Ahora conoce una función esencial para Excel: El buscarV.

 

Read more...

Aprende que son las referencias en Excel con este sencillo tutorial.

Introducción

Hay dos tipos de referencias de celdas en Excel: relativas y absolutas. Las referencias relativas y absolutas se comportan diferente cuando se copian y rellenan hacia otras celdas. Las referencias relativas cambian cuando una fórmula se mueve a otra celda. Las referencias absolutas siempre son constantes, sin importar a donde se copian.

Si quieres, puedes bajar el archivo de trabajo para este post.

Referencias Relativas en Excel

Por default, todas las referencias de celdas que usamos son referencias relativas. Cuando las copiamos a celdas múltiples, cambian basándose en la posición relativa de filas y columnas. Por ejemplo, si copias la fórmula =A1+B1 a de la fila 1 a la fila dos, la fórmula se convertirá en =A2+B2. Las referencias relativas son especialmente convenientes cuando tienes que repetir el mismo cálculo en muchas columnas o filas.

Como crear y copiar una fórmula usando referencias relativas

En el siguiente ejemplo, queremos crear una fórmula que multiplicará el precio de cada producto por la cantidad vendida. En lugar de crear una fórmula nueva para cada fila, podemos crear una sola fórmula en la celda D2 y luego copiarla a las otras filas. Usaremos las referencias relativas para que la fórmula calcule el total para cada producto correctamente:

  • Selecciona la celda que tiene la fórmula

  • Escribe la fórmula para calcular el valor deseado, en este caso: =F6*G6

  • Presiona Enter, la fórmula se va a calcular y el resultado se va a mostrar en la celda

  • Encuentra el botón de relleno abajo a la derecha. En nuestro ejemplo, encontraremos el botón de relleno para D4

  • Doble clic sobre las celdas que queremos rellenar. Excel solito capta hasta donde se debe de hacer el relleno.

Podríamos hacer doble clic en cada celda (o presionar F2) para checar las fórmulas y asegurarnos que estén bien. Como podemos ver, las referencias relativas deberían de ser diferentes para cada celda, según sus filas.

Referencias Absolutas en Excel

Hay muchas veces que no queremos que una referencia de celda cambie cuando se copia a otras celdas. Al contrario de referencias relativas, las referencias absolutas no cambian cuando son copiadas o rellenadas. Puedes usar una referencia absoluta para mantener una fila o columna constante.

Ubicamos una referencia absoluta en una fórmula por la adición del signo de dólares ($). Puede preceder la referencia de columna, la referencia de fila, o ambas.

Generalmente usamos el formato de $A$2 cuando creamos fórmulas que contienen referencias absolutas. Las otras referencias son usadas menos frecuentemente.

Cuando escribimos una fórmula, puedes presionar la tecla F4 en tu teclado para cambiar entre referencias absolutas y relativas. Esta es una manera fácil de rápidamente insertar una referencia absoluta.

Para crear y copiar una fórmula usando una referencia absoluta en Excel

En el ejemplo de abajo, vamos a usar la tasa de impuestos en la celda L6 para calcular el IVA para cada producto en la columna I. Para asegurarnos que la referencia al impuesto se mantiene constante – incluso cuando la fórmula es copiada y llevada a otras celdas -necesitaremos hacer que la celda $D$6 sea una referencia absoluta.

  • Selecciona la celda que contendrá la fórmula, en este caso la celda I6.

  • Escribe la fórmula que necesitamos. En este ejemplo, escribiremos =H6*$L$6 haciendo a L6 nuestra referencia absoluta.

  • Presiona Enter, la fórmula se calculará y el resultado se mostrará en nuestra celda.

  • Rellena la fórmula para toda la columna I que deba tener datos con un doble clic en el cuadrito de abajo a la derecha.

  • Puedes revisar cada una de las fórmulas para ver si están bien o no.

Read more...

Aprende a usar funciones en Excel con estos trucos sencillos

Una función es una fórmula predefinida que realiza un cálculo usando valores especificas en algún orden en particular. Excel incluye muchas funciones comunes que pueden ser utilizadas para rápidamente encontrar la suma, promedio, cuenta, máximo y mínimo para algún rango de celdas. Para usar las funciones correctamente, necesitarás entender las diferentes partes de una función y como crear argumento para calcular valores y referencias de celda.

Baja el libro de práctica para esta actividad aquí.

Las partes de una función en Excel

Para trabajar correctamente, una función debe de estar escrita de cierta manera, que se llama sintaxis. La sintaxis básica para una función es el signo de (=), el nombre de la función (por ejemplo, Suma), y uno o más argumentos. Los argumentos contienen, siempre, la información que quieres calcular. La función en el ejemplo de abajo suma los valores del rango A1:A20.

Trabajando con Argumentos

Los argumentos se pueden referir a celdas individuales y a rangos de celdas que pueden estar cerrados con paréntesis. Puedes incluir un argumento o argumentos múltiples, dependiendo de la sitnaxis requerida para la función.

Por ejemplo, la función =Promedio(B1:B9) calcularía el promedio de los valores del rango B1:B9. Esta función contiene solo un argumento.

Argumentos múltiples deben de estar separados por una coma. Por ejemplo, la función =Suma(A1:A3,C1:C2,E1) sumarán los valores de todas las celdas en los tres argumentos.

Creando una función

Hay una variedad de funciones disponibles en Excel. Aquí hay algunas de las funciones más comunes que usarás:

  • Suma: esta suma los valores de las celdas en el argumento
  • Promedio: Esta determina el promedio de los valores incluidos en el argumento.
  • Contar: Esta función cuenta el número de celdas con valor numérico en el argumento. Esta función es útil para rápidamente contar objetos en un rango de celdas.
  • Max: Esta función determina el valor de celda más alto incluido en el argumento
  • Min: Esta función determina el más bajo valor de celda incluido en el argumento.

Para crear una función utilizando el comando de Auto Suma

El comando de AutoSuma te permite automáticamente insertar las funciones más comunes en tu formula, incluyendo Suma, Promedio, Cuenta, Min, y Max. En el ejemplo de abajo, usaremos la función suma para calcular el costo total para una lista de objetos recién comprados.

  • Selecciona la celda que contendrá la función, en este caso seleccionaremos la celda D13

  • En el grupo de Edición, en la pestaña de inicio, presión la flecha al lado del comando de autosuma. Luego selecciona la función deseada desde el menú que aparece. En nuestro ejemplo, seleccionaremos suma

  • Excel colocará la función en la celda y automáticamente seleccionara un rango de celdas para el argumento. En nuestro ejemplo, las celdas D3:D12 fueron seleccionadas automáticamente; sus valores serán sumados para calcular el costo total. Si Excel selecciona el rango incorrecto, puedes manualmente insertar las celdas deseadas en el argumento.

  • Presiona enter en tu teclado. La función será calculada, y el resultado aparecerá en la celda. En nuestro ejemplo, la suma es:

Para insertar una función manualmente

Si ya conoces el nombre de la función, fácilmente lo puedes escribir tu mismo. En el ejemplo de abajo, usaremos la función promedio para calcular el número promedio de unidades vendidas por mes.

  • Selecciona la celda que contendrá la función, en este caso:

  • Escribe el singo de (=) y escribe la función deseada. También puedes seleccionar la función deseada de una lista de funcione que aparecen debajo de la celda mientras escribes. En nuestro ejemplo, escribiremos =Promedio

  • Escribe el rango de celda para el argumento dentro de los paréntesis. En nuestro ejemplo, escribiremos (C3:C9). Esta fórmula agregará los valores de las celdas C3:C9. Entonces dividiremos el valor por el número total de valores en el rango.

  • Presiona enter en el teclado. La función será calculada y el resultado parecerá en la celda.

 

La biblioteca de funciones

Aunque hay cientos de funciones en Excel, las que más necesitas dependen del tipo de datos que tus libros de trabajo contienen. No hay necesidad de aprenderse cada función, pero explorar algunos diferentes tipos de funciones te ayudaran cuando creas nuevos proyectos. Incluso puedes usar la biblioteca de funciones en la pestaña de fórmulas para encontrar nuevas funciones que te sirvan.

Para ir a la biblioteca de funciones, selecciona la pestaña de fórmulas en el listón. Busca el grupo de la biblioteca de funciones.

Insertar una función desde la biblioteca

En el ejemplo de abajo, usaremos la función de CONTARA para contar el número total de objetos en la columna de productos. Al contrario de Contar, ContarA puede ser utilizado para contar celdas que contienen cualquier tipo de datos.

  • Selecciona la celda que contendrá la función.

  • Vete a la pestaña de fórmulas para tener acceso a la biblioteca de funciones.

  • Desde el grupo de biblioteca de funciones, selecciona la categoría de función deseada. En nuestro ejemplo, escogeremos Más funciones, y luego dejar el mouse sobre estadística.
  • Seleccionaremos ContarA, que contará el número de celdas en nuestros productos que no están vacíos.

  • Aparecerá un cuadro de dialogo de argumentos de función. Selecciona el campo de valor 1 y luego escribe o selecciona las celdas deseadas a contar. Podrías agregar argumentos en el campo de Valor2, pero en este caso no es necesario.

  • Presiona OK
  • La función será calculada y el resultado aparecerá en la celda.

El comando de insertar función

Aunque la biblioteca de funciones es un excelente lugar para ojear funciones, a veces preferirías buscar una. Puedes hacer esto usando el comando de insertar función.

Para usar el comando de insertar función

En el siguiente ejemplo queremos usar una función que calcule el número de días de negocio que tomó recibir Ítems después de que fueron ordenadas. Usaremos las fechas en las columnas E y F para calcular el tiempo de entrega en la columna G.

  • Selecciona la celda que contiene la función, en nuestro ejemplo, seleccionaremos G3.

  • Haz clic en el botón de fórmulas en el listón.

  • Aparecerá el cuadro de dialogo de insertar función
  • Escribe algo relacionado con la función que quieres, luego presiona Ir. En nuestro ejemplo, escribiremos contar días.

  • Repasa los resultados para encontrar la función deseada, en nuestro caso usaremos Dias.Lab, que cuenta el número de días laborales entre la fecha ordenada y la fecha de recibido.

  • Se abrirá el cuadro de diálogo de argumentos de función. Desde aquí puedes seleccionar los argumentos que harán nuestra función

  • Cuando termines presiona OK, nuestra función será calculada y el resultado aparecerá en la celda. En nuestro ejemplo, el resultado muestra que tomo 4 días laborales recibir la orden.

Aprende Más

Si ya estas cómodo con las funciones básicas, tal vez te gustaría intentar usar una función un poquito más avanzada pero esencial, que es BuscarV. Revisa nuestro artículo de cómo usar BuscarV. Apara aprender más sobre trabajar con funciones y referencias, checa nuestro artículo de referencias absolutas y relativas.

Read more...
Aprende a usar BuscarV con estos sencillos ejemplos

Aprende fácil como usar la función BuscarV en Excel

BuscarV es probablemente la función más popular en Excel. BuscarV es una herramienta extremadamente útil en Excel, y aprender a usarla es más fácil de lo que crees.

Antes de comenzar, ya deberías de estar familiarizado con los básicos de fórmulas en Excel. Checa nuestra lección de funciones de nuestro tutorial de fórmulas en Excel. BuscarV funciona igual en todas las versiones de Excel, y funciona incluso en otras hojas de cálculo como Google Sheets. Puedes bajar este ejemplo si quieres trabajar junto con el artículo.

¿Qué es Buscarv en Excel?

Básicamente, BuscarV te permite buscar información específica en tu hoja de cálculo. Por ejemplo, si tienes una lista de productos con precios, podrías buscar el precio de un producto en específico.

Tutorial sencillo de como aprender BuscarV en Excel 1

Vamos a usar BuscarV para encontrar el precio de la pizza Margarita. En este ejemplo bien podrías haber buscado a Mano. Una vez que comiences a aprender a usar BuscarV, verás que, al usarlo con hojas de cálculo de Excel más grandes y complejas, se vuelve una herramienta indispensable.

Agregaremos nuestra fórmula a G10. Como con cualquier fórmula, comenzamos con el signo de igual (=). Después escribimos el nombre de la fórmula. Nuestros argumentos deben de estar entre paréntesis, así que escribe un paréntesis abierto. Hasta ahorita debería de verse así:

=buscarv(

O si lo vemos en nuestra pantalla:

Tutorial sencillo de como aprender BuscarV en Excel 1

Agregando los argumentos al BuscarV

Ahora agregaremos nuestros argumentos. Los argumentos le dirán al BuscarV que buscar y donde buscarlo.
El primer argumento es el nombre del producto que estamos buscando, que en este caso es la margarita. El producto se encuentra escrito en F10. Por lo mismo, solo hacemos una simple referencia, lo que nos deja con:

=buscarv(F10

El segundo argumento es el rango que contiene los datos donde se hará la búsqueda. En este ejemplo, nuestros datos están en A7:D115. Como con cualquier función, hay que usar la coma para separar cada argumento:

=buscarv(F10,A7:D115

Nota: Es importante saber que BuscarV siempre va a buscar la primera columna del rango que le demos en este argumento. En este ejemplo, buscará la “Margarita” en la columna A. En algunos casos, tal vez tengas que mover las columnas alrededor para que la primera columna contenga los datos correctos.

El tercer argumento es el número de columna. Es muy sencillo: la primera columna de nuestra tabla de datos es 1, la segunda es el 2, y así. En este caso, estamos intentando encontrar el precio del artículo, y los precios que están contenidos en la tercera columna. Esto significa que el argumento será 3:

=buscarv(F10,A7:D115,3

El cuarto argumento le dice a BuscarV si queremos una búsqueda aproximada, y puede ser verdadero o falso (1 o 0). Si es verdad (1), buscara coincidencias aproximadas. Esto es un tema más avanzado que cubriremos después. Por lo pronto solo hay que saber esto: si hay texto involucrado en nuestra búsqueda, siempre hay que seleccionar Falso (0). Este es nuestro último argumento, asi que se la fórmula se verá así:

=buscarv(F10,A7:D115,3,Falso)

En nuestra pantalla se verá asi:

Tutorial sencillo de como aprender BuscarV en Excel 2

¡Listo! Cuando presiones enter te debería de dar un resultado, que es $105.00

Tutorial sencillo de como aprender BuscarV en Excel 2

¿Cómo funciona BuscarV en Excel?

Vamos a pensar un poco sobre cómo funciona esta función. Primero busca verticalmente hacia abajo en la primera columna (BuscarV significa Búsqueda Vertical). Cuando encuentra la palabra “Margarita”, se mueve a la tercera columna para encontrar el precio.

Tutorial sencillo de como aprender BuscarV en Excel 3

Si queremos encontrar el precio de otro producto, solo cambiamos lo que dice en la celda F10.

Relacionar Tablas con BuscarV en Excel

El principal uso que le dan los avanzados a BuscarV es relacionar tablas. Relacionar tablas, o cruzar tablas con BuscarV, significa que agarras información de una tabla y la pasas a otra.

Es más sencillo entenderlo con un ejemplo. Veamos a continuación tenemos la misma lista de productos y precios en naranja (celdas I:L), pero también tenemos una lista de productos consumidos en el restaurante… sin precio.

Tutorial sencillo de como aprender BuscarV en Excel 4

La instrucción en este caso es obtener los precios de todos los productos vendidos para al final poder sumarlos y calcular los ingresos del restaurante para este periodo.

Tomemos por ejemplo la primera línea de la tabla, en la fila 6. Se vendió una Tisana. Si nosotros quisiéramos encontrar su precio, podríamos ir a nuestra tabla naranja, y buscar a mano la tisana (fila 91). Encontrar que el precio es de $25, y volver a la celda F6 a escribir ese precio. Habría que repetir ese proceso para los 842 platillos vendidos, lo que nos tomaría horas.

O podríamos aplicar un BuscarV y arrastrarlo, que nos daría el mismo resultado en cuestión de segundos.

Vamos escribiendo nuestro primer BuscarV en la celda F6. Nuestro primer argumento será el producto cuyo precio nosotros queremos encontrar en la tabla de precios. El producto se nos da en D6. Así que nuestra fórmula comienza viéndose así:

=buscarv(D6

Que es lo mismo en nuestra pantalla:

Tutorial sencillo de como aprender BuscarV en Excel 5

Ahora debemos de introducir los demás argumentos. Recordamos que el segundo argumento es el rango donde se encuentra nuestra tabla de búsqueda, en este caso es la tabla naranja de I5:L113. Ya que el BuscarV lo vamos a usar para toda la tabla azul, debemos de fijar este dato presionando F4 para que todos los BuscarV hagan referencia a nuestro menú naranja.

=buscarv(D6,$I$5:$L$113

Y, por último, introducimos el argumento de número de columna, que nos dice en que columna se encuentra el precio del producto (columna 3 de la tabla naranja) y le decimos que es FALSO que queremos una búsqueda aproximada. Recuerden que siempre que haya texto involucrado en nuestra búsqueda, este último argumento siempre es FALSO.

=buscarv(D6,I5:L113,3,FALSO)

En nuestra pantalla:

Tutorial sencillo de como aprender BuscarV en Excel 6

¡Y listo! Ahora ya tenemos una búsqueda que nos trae el precio de la tisana. Ahora solo necesitamos arrastrar para toda la tabla y tendremos el precio de todos los productos que se vendieron.

Tutorial sencillo de como aprender BuscarV en Excel 3

Solo nos queda calcular los ingresos totales sumando los precios de todos los productos. Recuerda de utilizar Alt + Shift + 0. Checa nuestro artículo sobre las autosumas si tr quedan dudas.

Tutorial sencillo de como aprender BuscarV en Excel 4

Inténtalo tu mismo.

En el archivo, ahora calcula tu el costo total de todas las ventas de este restaurante usando BuscarV.

Ahora ya conoces los ´básicos de BuscarV. Aunque los usuarios avanzados usan BuscarV de diferentes maneras, puedes hacer mucho con las técnicas que hemos cubierto. Por ejemplo, si tienes un alista de contactos podrías usar el nombre de alguien para encontrar su número telefónico. Las posibilidades son infinitas.

Read more...
Bitnami