Author - Dan Contreras

Tips para hacer impresiones increibles en Excel

Tienes tu reporte en Excel ya todo chulo. Está bien organizado, actualizado y muy bien formateado, y decides imprimirlo… el problema es que cuando lo haces, parece un desastre.

Las hojas de cálculo de Excel no están diseñadas para caber en una hoja de papel, están diseñadas para ser trabajada en la computadora. Tus datos simplemente no caben bien dentro del papel que todos conocemos.
Sin embargo, esto no significa que es imposible hacer que una hoja de cálculo de Excel se vea bien en papel. De hecho, no es tan difícil. A continuación, te presento tips para imprimir en Excel.

1.-Vista previa de la hoja de trabajo

Puedes ver exactamente como tu hoja de trabajo se verá en la hoja impresa usando el modo de Diseño de Página. En términos de ahorrarte tiempo y papel, Diseño de página es tu herramienta más valiosa. Incluso puedes hacer ciertos cambios con ella, como arrastrar los márgenes para hacerlos más anchos, ponerle encabezados y pies de página

2.-Decide que vas a imprimir

Si solo necesitas ver cierto segmento de tus datos, no te molestes con imprimir todo el libro – solo imprime datos en específico. Puedes imprimir solo la hoja de trabajo que estás viendo, o puedes imprimir todo el libro para imprimir el libro entero. También puedes imprimir un pequeño segmento de tus datos seleccionando los datos, y luego escogiendo Imprimir selección en la opción de impresiones.

3.-Maximiza tu espacio

Estas limitado por el tamaño del papel en el que estas intentando imprimir, pero hay maneras de hacer lo máximo de este espacio. Intenta cambiar la orientación de la página, la orientación que viene por default es bueno para datos con más filas que columnas, pero si tu hoja de trabajo es más ancha que alta (sin albur) cambia la orientación a paisaje.

Si necesitas más espacio todavía, puedes cambiar los márgenes para que se vayan a la orilla de tu papel. Y si necesitas más espacio todavía, puedes jugar con las opciones de la escala de papel para que todo quepa en una sola hoja.

4.-Usar títulos de impresión

Una vez que tu hoja d Excel mida más de una hoja, entender que estás viendo puede ser confuso. El comando de imprimir título te permite incluir una fila título o columna en cada página de tu hoja de cálculo. Las columnas o filas que seleccionas van a aparecer en cada página de tu impresión lo que hará leer tus datos mucho más fácil.

5.-Usar saltos de pagina

Si tu hoja de trabajo usa más de una hoja de papel, considera usar saltos de página para decidir exactamente qué datos deberían de estar en cual página. Cuando insertas un salto de página en tu hoja de trabajo, todo debajo del salto se mueve a una diferente página que todo arriba de esta. Esto es útil porque te permite romper tus datos exactamente como tú los quieres

Seguir estos tips te va a ayudar mucho a hacer tus hojas impresas más fáciles de leer.

Read more...

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...

Como armar una medida en DAX que te de las ventas de hoy

El caso es muy sencillo: necesitas una fórmula de Power Pivot en Excel que te entregue las ventas del día de hoy. O tal vez que las compare contra el mismo día de hoy hace un mes. ¿O qué tal el día de hoy hace un año?

En un primer approach, esta medida suena un poco difícil de armar, pero veremos que en realidad esta expresión es bastante sencilla echar a andar en Power Pivot en Excel (o Power BI en su defecto).

El caso

El archivo que veremos está aqui.

Tenemos una tabla de ventas (hechos) y una tabla de fechas (catálogo). Nuestra tabla de tiempo se llama Tiempo y la columna principal de fecha, imaginativamente, se llama Fecha.

Tabla 1

Armamos una medida básica que nos de las ventas totales sin nada de filtros activos ni ningún modificador.

Ventas totales :=
SUM ( Ventas[Ventas] )

Conseguir las ventas del día de Hoy

Queremos que la medida de DAX nos de las ventas del día de hoy y que su valor cambie conforme va avanzando el tiempo (es decir, mañana nos dará las ventas que se hayan acumulado hasta mañana).

Para esto debemos aplicarle un filtro a nuestra medida de Ventas Totales, que solo nos traiga lo que se ha gastado el día de hoy. Es momento de recurrir a nuestra confiable fórmula de Calculate.

Ventas Hoy :=
CALCULATE ( [Ventas Totales], Fechas[Fecha] = TODAY () )

Y listo, tenemos nuestra medida que nos trae las ventas solo de hoy.

¿Y? Eso fue bastante sencillo. Ahora es momento de conseguir medidas más atrás en el tiempo. Checamos que los resultados son correctos en nuestra tabla dinámica:

Tabla 4

Ventas del mismo día de hoy, pero hace un mes.

Este artículo se escribe el 12 de marzo de 2017. El mismo día hace un mes sería el 12 de febrero del 2017. ¿Cómo podemos armar una medida que nos de las ventas del mismo día de hoy hace un mes?

Sencillo. De nuevo, debemos de recurrir a nuestra fórmula de calculate, pero también agregaremos DateAdd() para poder movernos un mes atrás en el tiempo:

Ventas de Hoy hace un mes :=
CALCULATE ( [Ventas Totales], DATEADD ( Fechas[Fecha] = TODAY ()-1MONTH ) )

Como pueden ver, la clave está en que, cuando DateAdd nos pide el argumento de fecha, nosotros le decimos que la fecha se encuentra en la tabla Fechas[Fecha] pero es igual a Today().

De esa manera podemos cumplir con el requisito de que DateAdd requiere una columna de fechas como su primer argumento, mientras que al mismo tiempo nos limitamos al día de hoy.

Podemos ver en la tabla dinámica que los resultados son correctos:

Tabla 2

Ventas de Hoy pero hace un año

Bueno, habiendo hecho el mes, las ventas de hoy pero hace un año son sumamente sencillas, puesto que solo es necesario hacer ajustes muy ligeros a nuestra fórmula.

Ventas de Hoy hace un Año :=
CALCULATE ( [Ventas Totales], DATEADD ( Fechas[Fecha] = TODAY ()-1YEAR ) )

Si se fijan es exactamente la misma fórmula pero ahora con el modificador de que nos vamos 1 año para atrás, en lugar de un mes. Aquí la comprobación de resultados en nuestra tabla dinámica:

Tabla 3

Conclusión

Estas medidas son particularmente útiles en negocios de retail y líneas de producción, donde se buscar estar viendo día a día como las métricas van mejorando con respecto a periodos pasados.

Una gran limitante es que comparar el mismo día del mes/año pasado te puede llevar a que estés comparando un lunes contra un domingo, cosa que te arrojaría un resultado sin sentido. Los lunes son días de mucha actividad y los domingos, por definición, está muerto el negocio.

Read more...

Validación de Datos

En Excel como en la vida, los errores son inevitables. Por suerte la validación de datos nos permite impedir cierto tipo de errores al momento de capturar datos.

Por definción, nosotros podemos escribir lo que se nos pegue la gana en cualquier celda de Excel, independientemente de las expectativas del autor original de la hoja de cálculo. Por ejemplo:

ejemplo-no-validado

La celda dice “Teléfono” pero podemos escribir lo que queramos.

La validación de datos nos permite imponer restricciones sobre los valores que podemos insertar en una celda determinada

Activar la validación de datos

Para prender la activación de datos es sumamente sencillo, solo es necesario:

validaciondedatosactivar

  1. Seleccionar la celda a proteger
  2. Irse a la pestaña de datos
  3. Seleccionar validación de datos

Y listo… ahora viene lo complicado, decidir como vamos a proteger nuestros datos.

ajustar-ancho-2

Validación de valores

Ahora sigue escoger como vamos a proteger nuestro datos. En el menú de “permitir” por default esta seleccionado cualquier valor, pero podemos seleccionar cualquier otra cosa.

permitir-valor

  • Cualquier Valor: La celda admite cualquier valor que el usuario quiera poner.
  • Número Entero: La celda solo admite números enteros. Podemos determinar el límite inferior y superior de ese número.
  • Decimal: Igual, solo admite números decimales, puedes poner límite inferior y superior.
  • Lista: Datos seleccionados de una lista. Esta es todo un caso especial que podemos ver en esta entrada del blog.
  • Fecha: Te limita a un valor en formato de fecha.
  • Hora: Te limita a un valor en formato de Hora.
  • Longitud de Texto: Te limita a un número de caracteres determinado.
  • Personalizada: Te limita a un valor que cumpla con una fórmula que tu determinas. Esta es más avanzada y dificil de utilizar ya que requiere que uses las mismas fórmulas de Excel.

Ejemplo: Teléfono de 10 dígitos

Por ejemplo, tenemos una celda donde necesitamos un teléfono de 10 dígitos. Para esto necesitamos activar la validación de datos para longitud de texto.

10digitos

  1. Seleccionar la celda donde se introducira el teléfono
  2. Seleccionar la pestaña de Datos – > Validación de datos
  3. En la lista de permitir seleccionar Longitud del Texto
  4. En datos seleccionar Igual A
  5. Introducir el Número 10 y luego aceptar.

Y listo, esta celda solo aceptará teléfonos de 10 dígitos.

ajustar-ancho-2

Extra: Mensaje de Entrada y de error

Al activar una validación de datos, es buena costumbre avisarle al usuario; para esto se nos permite dar mensajes de entrad y de error.

El mensaje de Entrada se puede personalizar desde la validación de datos, y luego en la pestaña “Mensaje de Entrada”

10digitosentrada

El mensaje de entrada se mostrará como un comentario. que se prende al seleccionar la celda en cuestión.

Lo mismo para el error, desde la ventana de validación de datos, en la pestaña de “Mensaje de Error”.

10digitoserror

El mensaje de error aparece al introducir un valor que no cumple con la validación.

Read more...

Subtotales en Excel: como calcularlos y agrupar datos

Una herramienta que no muchos conocen: los Subtotales en Excel. Te enseño como sacar subtotales, armar un esquema, y de pasada, también agrupar datos.

Guía en Video para sacar subtotales en Excel

Comenzaremos con lo más básico: la agrupación de filas y columnas en Excel. Esto es neceario para poder pasar a lo importante: sacar subtotales en Excel.

ajustar-ancho-2

Sacar Subtotales en Excel

A veces necesitamos organizar nuestros datos de manera rápida y poder conocer los subtotales. Para eso nos sirven las herramientas de “Agrupar” datos en Excel.

Para este ejemplo, usaremos este archivo que contiene información de ventas.

Archivo para trabajar agrupacion y desagrupacion

Nuesta meta es mostrar los subtotales de ventas por mes y el total de ventas general.

(más…)

Read more...

¿Qué es Power Pivot?

Power Pivot / Power BI es el ingrediente que hacía falta en tu relación con los datos.

En mi experiencia, ninguna tecnología que haya sido liberada en los últimos 10 años si quiera se le acerca al poder que Power Pivot le puede entregar al usuario normal. Power Pivot por fin, por fin le da a todo mundo una herramienta sencilla y fácil de usar para analizar los datos como los grandes.

¿Qué es Power Pivot? Dependiendo de tu experiencia, puedes entender Power Pivot como:

  1. Lo mejor que le ha pasado a Excel en 20 años… y el futuro de Excel.
  2. El corazón de la nueva suite Power BI de Microsoft, que fundamentalmente cambiará la manera en que trabajas.
  3. Una manera mucho más ágil y efectiva de trabajar con Business Intelligence.

Lo mejor que le ha pasado a Excel en 20 años

Bill Jelen, el mismísimo Mr. Excel, declaró esto cuando Power Pivot fue liberado hace 6 años en el 2010:

1.-Power Pivot es lo mejor que le ha pasado a Excel en 20 años

Power Pivot es parte de Excel, está armado por Microsoft y es gratis para todos los usuarios de Office. Cualquiera que use tablas dinámicas y/o vlookup es la audiencia objetivo de Power Pivot. Esa gente que ya es clave para el análisis de datos en una organización hoy en día. Power Pivot te da súper poderes de datos.

Y lo mejor, se integra directamente a Excel como si siempre hubiera estado ahi.

Que es Power Pivot

Checa como instalar Power Pivot en tu Excel.

Power Pivot está diseñado para que te sea sumamente fácil aprenderlo si ya sabes usar Excel, en lugar de comenzar desde 0.

Power Pivot sigue siendo tablas dinámicas, sigue siendo fórmulas.

¡Una tabla dinámica hecha con power pivot... es lo mismo!

¡Una tabla dinámica hecha con power pivot… es lo mismo!

Pero agrega un número importante de nuevas capacidades que para siempre cambian tu habilidad de convertir datos en conclusiones importantes.

Combina tablas y datos automáticamente

  • Nunca vuelvas a escribir un BuscarV
  • Liga tablas enteras en segundos
  • Produce vistas integradas de tu negocio que no eran factibles antes
  • Resuelve problemas complejos como “Presupuesto vs Actuales” en segundos

Fórmulas Inteligentes: Rápidas y Poderosas

  • Cientos de nuevas funciones que hacen que las viejas fórmulas sean obsoletas
  • Todas las nuevas fórmulas funcionan sobre tablas dinámicas, independientemente de si cambian de tamaño o forma
  • Solo hay que escribir una fórmula una vez… y no volver a reescribirla

Capacidad ilimitada de análisis de Datos

  • Analiza datos de casi cualquier tamaño
  • Cálculo rápido, ya no esperes a que Excel resuelva el problema de Calculando con 4 Procesadores.

Esas capacidades son absolutamente transformadoras en la práctica. Análisis que antes tomaban días en Excel (y Access) ahora son reducidos a menos de una hora con Power Pivot. Pero incluso más importante, los ajustes ahora toman segundos.

Como cualquiera que haya trabajado con una hoja de cálculo sabe, ningún análisis o reporte está completo a la primera iteración. Es un simple hecho humano que nadie nunca sabe que es lo que necesitan hasta que ven lo que habían pedido, y se les da la oportunidad de hacer preguntas adicionales.

La habilidad de ajustarse sobre la marcha, a nuevas preguntas y cambios en el negocio, es algo que tienes que experimentar para poder comprender del todo. Literalmente va a cambiar la manera que tu negocio piensa, a todos los niveles, de una manera confiable.

Cuando juntamos Power Pivot con la otra herramienta, Power Bi, Power Pivot obtiene 2 beneficios adicionales:

  1. Refresh automático
  2. Alcance en la web

Power Pivot ahora también está incluido en Excel 2013 (en las versiones profesional plus y normal) y viene integrado en Excel 2016. El nuevo “Modelo de Datos” de Excel es Power Pivot, y desde aquí se pueden hacer cosas increíbles con los datos.

2.- El Corazón de la suite de Power BI

Tal vez ya has visto un diagrama similar de la nueva familia Power, pero esta vez se ve a escala, con la excepción de Power Pivot, todos los componentes de Power BI son opcionales. La nueva suite tiene 4 opciones de visualización, y eres libre de usarlas todas. Asimismo, puedes usar Power Query para ayudarte con el descubrimiento de datos, limpieza e importación, pero no lo requiere.

Todo en Power BI, sin embargo, gira alrededor de Power Pivot. Los datos se cargan en Power Pivot y mejorados, (vía formulas inteligentes y relaciones de tablas) para formar modelos de datos, que puedes pensar como “máquinas de respuestas”. Adaptado a las necesidades de tu negocio, sus metas y nimiedades, estos modelos de datos traen una claridad casi mágica a preguntas que antes no se podían contestar.

Las herramientas de Power BI se conectan a Power Pivot como una manera de hacer preguntas, y también de ver las respuestas. Asi que mientras Power Pivot puede verse como que esta detrás de las cámaras, es mucho el motor que maneja el sistema entero, y es esencial.

La mejor manera de hacer Buisness Intelligence

La industria entera de Business Intelligence es disfuncional. Los proyectos de BI son masivos y tienen precios exorbitantes. Esos mismos proyectos requieren tiempos enormes de ejecución medidos en meses, y años a veces. Lo peor de todo, los resultados son menos de lo que se esperaban.

Ya sea que seas parte de “Negocios” o “TI”, la historia es familiar para ti. Pero al final no es culpa de nadie que muchos de los proyectos de BI sean tan malos. El problema no es la gente, son las herramientas y metodologías.

El escenario de no ganar de ayer: paga un costo altamente prohibitivo por una solución de BI.

Y hoy podemos operar en este mundo:

A dos semanas de implementar Power Pivot en la empresa, logramos echar a andar un Scorecard de toda la empresa que iba a tomar 1.5 años de trabajo desarrollar usando nuestras herramientas tradicionales, y obtuvimos mejor calidad en resultados de lo que hubiéramos tenido de una herramienta tradicional de Inteligencia de Negocios

Read more...

Power Query: La herramienta de datos más poderosa de Excel

Resumen: Conoce como esta nueva herramienta de Excel GRATIS de Microsoft llamada PowerQuery te va a ayudar a transformar tus datos a un formato que está listo para Tablas Dinámicas, Gráficas y mucho, mucho más.

Y es para principiantes!

La Fabrica de datos de Power Query

En este tutorial veremos una explicación introductoria Power Query. Te platicaré de por qué es mi nueva herramienta de Excel favorita para limpiar datos, y como te puede ayudar a automatizar procesos y ahorrarte tiempo. Bastante tiempo.

Power Query: Una Máquina de datos

Se han puesto a ver como se produce la comida en masa (con máquinas)? Es bastante sencillo:

  1. Le agregas ingredientes a la máquina
  2. Haces algunos ajustes
  3. Y automáticamente se pone a generar comida.

Una vez que la máquina esta configurada, puede generar la misma comida de alta calidad todas las veces que sea necesario.

Power Query trabja de manera muy similar!

Transforma tus datos con Power Query

  1. Agregas datos en lugar de ingredientes (de donde vengan, bases de datos, sistema de la emrpesa, páginas web, etc)
  2. Transformas los datos con Power Query
  3. Obitnees datos a tu hoja de cálculo o que está listo para reportear.

Power Query es como una máquina automática por que una vez que tienes un Query configurado, el proceso se puede repetir todas las veces necesarias (por ejemplo, para reportes mensuales)

Adios Macros super complicadas que arreglan datos. Adiós estar copiando y pegando para dejar los datos bien puestos.

Haciendo fácil la limpieza rutinaria de Datos

Trabajas con datos que han sido exportados desde un sistema o base de datos?

Si ese es el caso, probablemente pasas un buen rato transformando tus datos para dejarlos listos para crear reportes adicionales. De hecho, el 80% del tiempo de un analista con malos datos se desperdicia aquí.

 

Por ejemplo, puedes estar perdiendo tu tiempo en:

  1. Quitar Filas y Columnas que vinieron en blanco
  2. Convertir datos a otro tipo de datos
  3. Separar Columnas que vienen pegadas
  4. Filtrar y organizar columnas
  5. Agregar columnas Calculadas
  6. Reemplazar texto
  7. Normalizar tablas

Si repites algunos o todos estos pasos cada vez que te llegan datos nuevos, Power Query es para ti.

Antes de su existencia, mucha gente gastaba grandes porciones de su carrera haciendo estas tareas e intentando figurarse maneras más rápidas de lograrlo.

Afortunadamente PowerQuery automatiza todos estos pasos!

¿Y que hace Power Query exactamente?

Si tienes Excel 2010 Profesional Plus, o Excel 2013, hay que instalar Power Query antes de poder usarlo. Checa este post para ver como se instala. Hecho eso, te aparece un botón en la barra de herramientas.

Pestaña de Power Query

 

Para los que tienen Excel 2016, ya viene integrado en Excel debajo de la pestaña de Datos. (obtener y transformar)

Checa donde esta Power Query en Excel 2016

Pueden usar los botones de Power Query para decirle de donde vienen los datos (bases de datos, otros archivos de Excel, archivos CSV, páginas web, etc). Una vez que importaron sus datos, Power Query abre el editor para que lospuedan trasnformar.

Barra de Transformar en Power Query

Los botones del menú de arriba de Power Query automatizan todo tipo de procesos rutinarios. Solo tienes que jugar con ellos (o checar este tutorial de como usar power query para limpiar datos).

Tras terminar de limpiar tus datos, puedes sacar la tabla resultante a Excel con el botón de Cerrar y cargar.

Cerrar y Cargar datos en Power Query

Algunos ejemplos de que puedes hacer con power query.

Normalizar Tablas

Lo más común en Power Query es poder normalizar tablas: esta técnica es útil para conseguir que tus datos estén listos para una tabla dinámica.

Puedes empezar con datos que se ven asi:

datos-sin-pivotear

Y al final, con power Query vas a tener algo asi:

Datos normales listos para tablas dinámicas

PowerQuery puede hacer esto con unos cuantos clicks, y preparar tus datos para usarlos en una tabla dinámica.

Puedes revisar este tutorial de como Normalizar tus datos con Power Query.

Y puedes revisar este artículo de qué es una tabla normal y por qué necesitas estructurar tus datos tienen que verse así para una tabla dinámica.

Anexar Tablas (para hacer una mega tabla)

A veces tenemos la mala suerte de trabajar con datos que vienen en tablas separadas (pero son lo mismo!!). Power Query puede anexar estas tablas (poner una encima de otra) para crear una gran tabla. Puede hacer esto con tablas múltiples en un archivo, o puede jalar datos desde diferentes fuentes.

Digamos que tienes una tabla con datos para cada mes. Si metes todos esos datos en Power Query, te va a devolver una bonita tabla que puedes usar para crear tablas dinámicas y gráficas.

Usa Power Query para combinar archivos

Una vez que esta todo listo, Power Query puede estarse actualizando automáticamente conforme entran datos nuevos, solo hay que picar el botón de refrescar.

Combinar Tablas (reemplazar BuscarV)

Anexar y Combinar son cosas diferentes. En ese caso, combinar es relacionar tablas que tienen campos idénticos .

Power Query Junta Archivos

Por ejemplo si tenemos un menú con los precios de diferentes productos, y una lista de productos vendidos sin precio, usaríamos un BuscarV para relacionar esta tabla.

l problema con el BuscarV es que a veces eso implica agregar miles de fórmulas a un archivo sobre cargado.

Power Query puede hacer esa relación por ti sin necesidad de pasar por BuscarV. Checa este ejemplo de como combinar tablas.

Puedes Programarla incluso!

PowerQuery no necesita conocimiento de programación. Pero si sabes programar y hay algo que PowerQuery no puede hacer por si mismo, puedes inventarte tus propias funciones.

editor-avanzado

El lenguaje que usa se llama M, y la mayoría de las funciones son muy similares a escribir fómulas en Excel. Esto también lo hace mas amigable al usuario y más facíl de conocer el código

Se Automatizan Procesos

PowerQuery graba todos los pasos que haces al momento de limpiar datos, para que luego lo pueda hacer en automático. Esto te ahorra mucho tiempo si estás limpiando el mismo tipo de datos cada mes.

grabacion

También hace muy buena chamba de manejar errores. Si la estructura de tus datos cambia, PowerQuery te va a avisar dondé exactamente falló para que puedas arreglarlo.

Puedes usar PowerQuery para preparar tus datos listos para usarlos en Tablas dinámicas, gráficas y dashboards. Este es un paso crítico en el proceso de resumir y analizar datos.

El primer paso de la familia Power de Microsoft

Power Query es uno de los 5 productos Power de Microsoft, que ya vienen integrados, o gratuitos con Excel.

La idea de Microsoft es tener una linea de ensamblado de analytics y datos. Power Query Prepara los datos, PowerPivot los cocina y luego con Power View y Power BI se presentan.

Es un futuro muy brillante para Excel por un lado por que todos estos avances nos van a facilitar la producción de análisis de datos.

Peeeeero por otro lado, la cantidad de analistas necesaria se va a ir para abajo. El trabajo que antes sacaban 5 personas en Excel ahora lo van a sacar 2 usando las herramientas Power y Excel.

Transfórmate en un profeta del Excel

Toma nuestro curso de Excel en Guadalajara

Tel: (33) 3673 6112
Whatsapp: 331-422-8039


Como la instalo?

Power Query es gratis para Excel 2010 y 2013. Viene ya integrada en Excel 2016.

Como instalar Power Query

Si necesitas instalarla, te recomiendo que vayas a esta página donde te detallo, paso a paso como, disponer de Power Query.

Recursos Adicionales

Este árticulo es solo un repaso de la genialidad que es Power Query para que entiendas que és y con que se come. Hay mucho que aprender de Power Query y si en realidad quieres entrenarte, puedes empezar por aqui:

Como normalizar datos en Power Query

Tutorial de como limpiar datos en Power Query

Como Combinar tablas en PowerQuery

Como juntar tablas en Power Query.

¿Qué opinas?

¿Ya estas usando Power Query? Deja un comentario comentando como la usas. Si no, ¿crees que sería utíl para ti? ¿Estas haciendo alguna de las tareas mencionadas manualmente ahora?

Me interesa saber que problemas tienes para saber de que más escribir.

Read more...
Bitnami