Uncategorized

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

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

¿Para que sirve el cuadro de nombres en Excel?

¿Para que sirve el cuadro de nombres en Excel? Conoce los 10 usos más prácticos que le puedes dar a al cuadro de nombres. ¡Es una herramienta increíble!

Este es el cuadro de nombres en Excel, arriba a la izquierda:

Este es el cuadro de Nombres en Excel

Siempre lo vemos y siempre lo ignoramos por qué, si aprendimos Excel por nuestra cuenta, por lo general no sabemos que es un nombre.

Hoy se acaba eso, hoy conoces 10 usos prácticos que le puedes dar al cuadro de nombres, y el mundo de posibilidades que se te abre con los rangos nombrados.

Nivel: Principiante

¿Para que sirve el cuadro de nombres? Guía en video de 10 cosas que puedes hacer con el cuadro de nombres

1.-Puedes Conocer la dirección de la celda seleccionada

Checa como al seleccionar una celda, el cuadro de nombres se prende diciéndote tal cual su dirección (ejemplo: C2) en la hoja actual.

Conocer la dirección de la celda en Excel

2.- Moverte rápidamente a una celda

Así como el cuadro de nombres te da la dirección, si tu escribes la dirección en el cuadro de nombres, te llevará directo a la celda que quieres. Bastante útil cuando tienes información en la celda AA2005, muy fuera de tu pantalla.

Moverte a una celda en Excel

3.- ¿Cuántas filas y columnas llevo seleccionadas?

El cuadro de nombres te dará la información exacta del número de filas y columnas que llevas seleccionadas. Al estar seleccionando un rango, el cuadro te dirá las dimensiones del cuadro en el formato Filas x Columnas (Ej.: 4F x 2C es 4 filas por 2 columnas)

Saber cuantas filas y columnas hay seleccionadas en Excel

4.- ¡Puedes activar filas y columnas! (de la selección actual)

Así es, si escribes la letra F para activar la fila, o la letra C para activar la columna. Por ejemplo, activa la columna:

Activar una Columna usando el cuadro de nombres en Excel

O la fila:

Activar fila usando el cuadro de Nombres en Excel

(esto es solo para presumir, los verdaderos machos del Excel usan atajos de teclado para esto)

5.- Activar un rango

Así como puedes activar una celda escribiéndola en el cuadro de nombres, puedes activar un rango entero. Solo es necesario escribir la celda inicial, dos puntos, y luego la celda final. (ej: B7:H10)

activar-un-rango

6.- Armar un rango nombrado

¿Has oído hablar de los rangos nombrados? Cuando estas enfadado de los nombres como B3, puedes ponerle nombres como TasadeImpuestos a tus rangos.

Solo es necesario seleccionar la celda/rango que quieres nombrar y cambiarle el nombre en el cuadro de nombres.

Por ejemplo:

nombrar-una-celda-en-excel

Ahora cada vez que escriba o seleccione TasadeImpuestos, va a hacer referencia a esta celda.

Puedes conocer más de rangos nombrados en Excel aquí.

7.-Selecciona tu rango nombrado

Si escribes el nombre de un rango nombrado en el cuadro de nombres de Excel, puedes moverte, desde donde sea que estés en tu libro, hacia el rango nombrado.

Solo escribe el nombre en el cuadro de nombres y Excel te llevará automáticamente hacia allá.

nombrar-un-rango-en-excel

 8.-  Muestra todos los rangos nombrados en tu libro

¿No te acuerdas de cómo se llamaba un rango nombrado? ¡Solo selección el desplegable de la caja de nombres y listo! Todos tus nombres a la vista.

listar-rangos-nombrados-en-excel

9.-  Abre una macro para editarla

Si tienes una macro activa en tu libro, puedes escribir su nombre en el cuadro de nombres de Excel y el editor de Visual Basic se abrirá en automático.

editar-una-macroen-excel

10.- Selecciona una tabla en Excel

Si tienes una Tabla nombrada, puedes llegar a ella directamente desde el cuadro de nombres en Excel. Solo tienes que, como abras adivinado, escribir su nombre y Excel te llevará directamente a ella.

seleccionar-una-tabla-en-excel

El cuadro de nombres en Excel es probablemente una de las herramientas menos usadas de Excel, pero por su capacidad para llevarte rápidamente a cualquier dirección en un libro y asignarles nombres a las celdas, es probablemente una de las más valiosas.

Read more...

Como ordenar datos en Excel – 6 maneras diferentes

Ordenar datos en Excel se vuelve sumamente importante cuando tenemos más y más información en una hoja de cálculo. Puedes organizar tu hoja de cálculo rápidamente al ordenar los datos, volviéndola más legible.

Por ejemplo, podrías organizar una lista de contactos por nombre (alfabéticamente) o una lista de precios del articulo más caro al más barato.

Guía en video para ordenar datos en Excel

Al momento de ordenar datos en Excel puedes ordenar una tabla entera (todas las filas):

o solo un rango en específico (donde solo una columna se ordena y las demás no se tocan):

Veremos ambos casos. El libro de trabajo en este caso lo puedes conseguir de aquí.

Como ordenar datos de manera sencilla en Excel

En este ejemplo, ordenaremos los proyectos por la fecha en la que fueron comenzados (afectando toda la tabla en el proceso). Para ordenar solo es necesario:

Ordenar Datos en Excel de manera sencilla

  1. Seleccionar cualquier celda en la columna de fechas
  2. Seleccionar la pestaña de datos
  3. Hacer clic en el botón de ordenar de la A-Z

¡Listo! Con esto ya tenemos nuestra tabla ordenada por fecha desde la más vieja hasta la más reciente.

Ordenar datos en Excel en solo un Rango

Ahora vamos a seleccionar otra tabla donde ordenaremos pedidos en base a la cantidad de artículos que nos compraron. Para hacerlo:

Ordena un rango en Excel usando el cuadro de dialogo

  1. Selecciona el rango en específico que quieres ordenar, en este caso es toda la tabla.
  2. Vete a la pestaña de datos
  3. Presiona el comando de Ordenar (botón grande)
  4. Decide la columna en base a cuál se va a ordenar (cantidad), y el tipo de orden que quieres (grande a pequeño)
  5. Presiona aceptar, checa como se ordenaron solo los datos que fueron seleccionados en el paso 1.

Si tus datos no se están ordenando correctamente, revisa que todo esté bien escrito (no haya espacios de más) y estén dados en el formato correcto (no como texto). Las fechas son especialmente propensas a no querer ordenarse correctamente.

Quiero ordenar datos a mi manera en Excel

Excel por default solo te da la opción de ordenar de mayor a menor o viceversa. Si quieres ordenar de alguna manera personalizada, necesitamos recurrir a la herramienta de lista personalizada.

En este ejemplo queremos ordenar una lista de ventas de camiseta por talla (chica, Mediana, Grande, Extra grande). Veremos cómo usar las listas personalizadas para ordenar.

Ordena usando una lista personalizada

  1. Selecciona cualquier celda en la columna de tallas
  2. Selecciona la pestaña de datos, vete al botón de ordenar
  3. En el cuadro que aparece, selecciona en Criterio de Ordenación que quieres ordenar por lista personalizada
  4. Selecciona nueva lista en el cuadro de dialogo que aparece

Ordena usando una lista personalizada en excel

  1. Escribe las tallas en el orden que deseas, en este caso Chico, mediano, Grande y Extra Grande. Presiona Enter en el teclado después de cada ítem.
  2. Presiona agregar y luego ok
  3. Al volver al cuadro de diálogo de ordenar, va a aparecer tu nueva lista en “Criterio de Ordenación”
  4. Presiona OK y aprecia como tu tabla ya está ordenada por tallas.

Ordenar por formato de celda en Excel

También se vale ordenar tus datos por formato (como color o estilo de fuente) en lugar de por los valores de los datos. Esto es bastante útil cuando estás trabajando con una tabla donde se usan colores para denotar datos (no lo hagan!)

En este ejemplo, usaremos el orden por color para determinar cuáles tiendas son de diseño (azules) cuales son de comida (rojas) y cuales son de ecología (verdes)

Ordena datos por color

  1. Selecciona cualquier celda de la columna por la cual quieres ordenar
  2. En la pestaña de datos, selecciona ordenar
  3. En el criterio de” ordenar según”, selecciona por color de celda
  4. Escoge un color por el cual ordenar el campo de orden, en este ejemplo comenzaremos con el azul
  5. Presiona OK, ve cómo se ordena todo por color de celda.

Ordenar por Niveles en Excel (ordenación avanzada)

A veces necesitas ordenar una tabla por más de un criterio a la vez. En esta situación puedes ordenar por niveles.

En este ejemplo ordenaremos los diferentes países por los resultados de sus corredores en tiempo. Si tienen tiempos iguales, entonces deberán ser ordenados por sus números de atleta

Ordena un rango en excel por nivel

  1. Selecciona una celda en la columna que por la cual quieras ordenar, en este caso la de tiempos de la 3ra ronda.
  2. Vete a la pestaña de datos
  3. Por default va a aparecer una línea donde ya estas ordenando los datos por tiempo, hay que irse al botón de agregar nivel
  4. En ese nuevo nivel, alecciona la columna por la cual quieres ordenar (en este caso número de corredor)
  5. Esta tabla se ordenará según el orden seleccionado. En este ejemplo, primero se ordenarán por tiempos y luego por número de corredor.

Y listo. Tenemos nuestros datos ordenados.

Más Recursos

Esto es parte de una serie de tips y trucos. Asegúrate que ya conoces todos los tips avanzados y los no tan avanzados.

Read more...

Opciones de Inserción en Excel (La Brochita)

Naces. Insertas una columna. Trae el mismo formato que la columna de al lado. Pierdes del resto de tu vida cambiando el formato para que se venga en blanco.

En las versiones más recientes de Excel (2007 en adelante), el programa decide que cada vez que insertas una columna/fila/celda vas a querer por default tener el formato de la izquierda/arriba/celda anterior.

Muchas veces esto es verdad, pero en ocasiones queremos que la nueva celda se inserte en blanco.

ajustar-ancho-2

Guía en Vídeo para la Brochita y otras opciones de Inserción

 

Como usar las Opciones de Inserción

Para eso usamos “la brochita”

labrochita

Es un botón que solo aparece justo después de insertar una columna/fila/celda. Solo hay que hacer click en ella y seleccionar “borrar formato”

Respetar formato al arrastrar (Opciones de Autorrelleno)

También sucede mucho que arrastramos datos en Excel y no trae el formato de la celda original que arrastramos.

Aunque muchas veces esto es beneficioso, también nos sucede que se trae bordes o otros formatos que no queremos.

autorrelleno

Para eso solo tenemos que presionar el botón de Opciones de Autorrelleno y seleccionar “Rellenar sin Formato”.

Solo Copiar Celdas al Arrastrar

También sucede que al arrastrar datos númericos, Excel decide que los números aumentarán de manera seriada (1,2,3,4,5) en lugar de copiar el dato original.

De nuevo, a veces nos puede molestar esto puesto que lo que queríamos era el valor original.

copiar-celdas

Para eso de nuevo solo seleccionamos el botón de Opciones de Autorrelleno al terminar el autorrellno. Seleccionamos “Copiar celdas” y listo, tenemos celdas rellenadas con el valor original.

Read more...

Listas Dependientes en Excel: Aprende a armarlas

¿Tienes una lista desplegable que quieres que dependa de otra lista desplegable? Necesitas saber armar listas dependientes en Excel. Aprende como, es fácil

En este post veremos cómo armar listas dependientes en Excel utilizando listas desplegables, nombres y la función indirecto. No es necesario que sepas como se usa ninguna de las tres. Aquí te enseño cómo funcionan.

Guía en Video para armar Listas Dependientes en Excel

El archivo en el que trabajaremos lo puedes encontrar en esta liga.

ajustar-ancho-2

Comenzamos con una sola lista desplegable

Para armar listas dependientes en Excel, necesitas una lista INdependiente y otra Dependiente. Ambas son listas desplegables en Excel. Si quieres saber más sobre listas desplegables, checa este árticulo. 

Por ejemplo, en este caso trabajaremos un reporte de restaurante en el que se tendrán 2 listas desplegables de Excel:

Listas Dependientes en Excel

  • En la primer lista (independiente) se selecciona si se quiere ver comida o bebida
  • En la segunda lista (dependiente) se selecciona el platillo o bebida que se quiera ver. La magia está en que si en la primera lista se selecciono comida, solo se muestren comidas. Si se seleccionó bebida en la primera lista, solo se muestran bebidas.

El primer paso es, entonces, armar la primer lista desplegable de Excel, la independiente. Es muy sencillo:

Primer listas desplegable en excel

  1. Selecciona la celda C10, que es donde estará la lista desplegable
  2. Vete a la pestaña de Datos
  3. Luego a “Validación de Datos”
  4. En permitir selecciona lista
  5. Y en origen selecciona el rango T20:U20 donde están escritos los elementos de tu lista.
  6. Presiona aceptar

Y listo, ya tenemos la primera lista desplegable de nuestras listas dependientes. Esta lista nos permitirá seleccionar entre Comida/Bebida

Como funciona la primer lista desplegable en excel

El cuadro de Nombres

El siguiente paso es donde damos una vuelta hacia las funciones poco conocidas de Excel. Existe tal cosa como los rangos nombrados en Excel: en lugar de que tus celdas se llamen C3 o A4, se pueden llamar “Comida”, “Bebida” o “Juan”. Lee más sobre el cuadro de nombres aquí. 

Caja de Nombres Excel

En este caso necesitamos nombrar los rangos que contienen los menús de comida y de bebida. Es desde aquí que nuestras listas dependientes de Excel se van a alimentar.

Tenemos 2 tareas que hacer. La primera, armar la lista de comida:

Nombrar rangos en Excel

  1. Selecciona el rango T23:T104
  2. Vete a la caja de nombres y escribe “Comida” presiona enter. Es IMPORTANTISIMO que el rango tenga el exacto mismo nombre que el elemento de la primer lista desplegable. No puede ser “Comidas”, “Alimento” o “Platillo”.

Y la segunda, armar la lista de bebida:

Nombrar rangos en Excel

  1. Selecciona el rango U23:U48
  2. Ve a la caja de nombres y escribe “Bebida”, presiona enter.

Perfecto, ya tenemos nuestros rangos nombrados, estamos listos para terminar nuestras listas dependientes en Excel

La función Indirecto

El último paso es armar una segunda lista desplegable justo abajo de la primera (por qué el reporte así nos lo pide, no es obligación ante Dios). Esta segunda lista va a tener un procedimiento especial. Checa los pasos y luego la explicación:

Caja de nombres en Excel

  1. Selecciona C11 (donde estará la lista)
  2. Vete a la pestaña de Datos -> Validación de Datos
  3. En permitir selecciona lista
  4. Y en origen escribe: =indirecto(C10)
  5. Presiona Aceptar

Con esto queda nuestra lista desplegable dependiente de Excel.

Listas desplegables dependientes en Excel funcionando

¿Qué hicimos? El momento clave está cuando seleccionamos el origen. En lugar de seleccionar un rango, introdujimos la fórmula INDIRECTO(C10). Esta fórmula existe para decirle a Excel “Lo que sea que este en la celda C10, tómalo como si fuera un Nombre de Rango y vete a seleccionar las celdas de ese nombre”

Entonces esto termina por amarrar todo lo que sabemos hasta ahora. Necesitábamos nombrar los rangos que contenían los menús para que la fórmula Indirecto pudiera acusarlos cuando nosotros se lo indicáramos desde la lista desplegable de Excel en C10.

ajustar-ancho-2

Listas Dependientes en Excel

Con esto ya tenemos nosotros nuestras listas desplegables dependientes en Excel (todo un trabalenguas) funcionando. Recuerda que son 3 grandes tareas a seguir:

  1. Armar la primer lista desplegable
  2. Nombrar los rangos que alimentarán la segunda lista desplegable
  3. Armar la segunda lista, utilizando indirecto cuando te pide el origen.

Más recursos

¿Te gustó? Aunque este es un truco súper avanzado (para expertos solamente), checa que ya te sepas los trucos avanzados 

Checa otro ejemplo sacado de ExcelTotal.com

Y revisa este ejemplo directo de Microsoft

Read more...
Bitnami