Cómo generar datos aleatorios (y que sumen exactamente 100%)

Generar datos aleatorios

Este fin de semana, revisando entre mis correos recibidos (que cada vez son más y más) me encontré con una consulta sobre cómo generar datos aleatorios que me resultó interesante y divertida.

Resulta que Alan trabaja en un laboratorio y necesitaba generar números al azar para sus pruebas y que, al ser sumados, completen exactamente el 100%.

¿Cómo hacer para controlar que los datos sumen exactamente el 100%, si son aleatorios?

Sigue leyendo que ahora te lo cuento.

  • 1

    Primero lo primero: Generar datos aleatorios

Obviamente, lo primero que haremos será generar nuestros números aleatorios, por lo que preparamos nuestra hoja de Excel y colocamos en ellos la función =ALEATORIO() y la copiamos en todo el rango que ocupará, según el número de registros que deseamos generar.

Generar datos aleatorios

Con esto, vamos a tener una serie de números generados aleatoriamente, es decir, generados al azar y que cambiaran cada vez que refrescamos la hoja con la tecla F9.

Si observas con atención la imagen siguiente, vas a darte cuenta de que estoy utilizando una columna auxiliar. Ya verás por qué.

Generar datos aleatorios

  • 2

    Una celda para sumarlos a todos.

Para calcular el porcentaje de cada celda, necesitaremos saber el total sumado por todas ellas. Lógico, ¿verdad? Entonces lo siguiente en la lista es sumar todo ese rango de números aleatorios que generamos en el paso anterior y lo colocaremos en la celda que dice “Total”, tal y como te muestro en la imagen.

Generar datos aleatorios

  • 3

    La verdadera columna .

Una vez realizados estos pasos, ya podemos pasar a utilizar la verdadera columna de números aleatorios que aprovecharemos en nuestros cálculos científicos.  😀

Simplemente, vas a dividir cada una de las celdas de la columna auxiliar, para el total de la suma de ellos.

Y copias esta fórmula para cada una de las celdas del rango. Quedándote de la siguiente manera:

 

Precaución

No olvides utilizar el ancla de referencia en esta última fórmula para evitar que se desplace la celda del total, al copiar hacia abajo. Es un error muy común que ocasiona errores que luego no puedes detectar. =B2/$C$2

Has terminado. Tus datos aleatorios que respetan el 100%

Si ahora le das unos últimos toques, como formatearlo a dos decimales y sumas con una fórmula el total de la columna “Dato”, verás que siempre dará 100%. No importa cuántas veces hagas lo de generar datos aleatorios. Siempre respetará el límite del 100%.

¿Quieres descargar el archivo terminado?

Si quieres revisar el archivo terminado y revisarlo para disipar cualquier duda que tengas, aquí te dejo un botón para que lo descargues.

Descargar el archivo terminado.

¿En qué estás trabajando?

Me gustaría saber en qué estás utilizando este tipo de trucos o en qué piensas utilizarlo. Cuéntame en los comentarios y así inspiramos a las personas que recién comienzan con Excel, dándoles ideas frescas 😀

Comparte esta entrada en las redes sociales

Si la entrada te ha gustado, por favor compártela con tus amigos en Facebook y Twitter. Eso me ayudaría para darle mayor difusión al blog para llegar a más personas que están deseosas de aprender a utilizar Excel.

¡Nos vemos!

Raymundo

Créditos imagen de portada: Freeimages

Cómo insertar imágenes en los comentarios de Excel

Insertar Imágenes en los Comentarios de Excel

A veces las necesidades más complejas en apariencia, tienen las soluciones más sencillas. No siempre debes hacerte un quebradero de cabeza con Excel. Por ejemplo, existen personas que les interesa tener un catálogo de productos y que además aparezca la imagen junto a su código.

Es más, les gustaría que solo aparezca la imagen cuando se pasa el ratón sobre el código del producto.

¡Esto no se puede hacer en Excel!

Pues déjame decirte que sí  se puede y, gracias a mi querida amiga, @albarrason que me recordó la existencia de este sencillo pero fabuloso truco, hoy te traigo los pasos para realizarlo.

Insertar imágenes en los comentarios de Excel: Paso a paso.

  • 1

    Inserta un comentario normalmente

Clic derecho sobre la celda con el código de producto y clic en “Insertar comentario”

  • 1

    Dale formato al comentario

Coloca el puntero del ratón justo en uno de los bordes de la caja de comentario y haz un clic derecho. Elige la opción “Formato de comentario…”

Deberá de aparecerte un cuadro como éste. Si no es así, seguramente es porque no pusiste el puntero justo sobre el borde.

Seleccionas la pestaña “Colores y líneas” que es donde vamos a trabajar.

Para realizar el “truco” vas a elegir la lista desplegable “Color” en la que vas a elegir la opción “Efectos de relleno…”

Se abrirá oootro cuadro en el cual vas a elegir la pestaña “Imagen” y luego harás un clic sobre el botón “Seleccionar imagen…”

 Se abrirá un cuadro en el que simplemente debes buscar y seleccionar la imagen deseada.

Yo voy a buscarme la figura de una lamparita 😀

¡Y listo!

Con esos sencillos pasos, ya tendrás en funcionamiento un catálogo de productos con un cierto aire de interactivo  😛

Haz la prueba, pasa el puntero del ratón sobre la celda donde insertaste el comentario y verás como aparece la imagen asociada y desaparece cuando quitas el puntero.

¡Perfecto para esa idea que tenías!

¿O no?

Ahora, ¿qué otra cosa podrías hacer para mejorar este sistemita?

Podría ser, por ejemplo, aplicar una macro que busque automáticamente la imagen. ¿Sería posible?

Tú, ¿cómo lo harías?

Espero tu respuesta en los comentarios.

¡Nos vemos!

Raymundo

Cómo concatenar un rango, sin usar macros.

Sin usar macros

Realmente es muy común encontrarme con personas que buscan saber más sobre macros; pero también hay muchas otras que desean saber cómo solucionar problemas sin usar macros.

Por ejemplo, el típico caso: tenemos un rango de datos y queremos concatenarlo. Como ya sabes, la función concatenar requiere que le pases las celdas que se van a unir, una por una, además de separarlas con una coma.

¡Pero es que esto es demasiado fastidioso!

Y qué te puedo decir. Es verdad.

Por eso, existen soluciones para concatenar un rango utilizando macros a través de una función en VBA.

Entonces, si eres de los que no les gusta complicarse con las macros o quieres una solución que puedas aplicar aquí y ahora, te voy a mostrar un truco sencillo que no es un secreto; pero es poco conocido:

  • 1

    Utilizar la función TRANSPONER

Sí. Vamos a utilizar la función transponer, para utilizar un truco sencillo que nos permitirá conseguir el objetivo deseado.

Para no complicar mucho el ejemplo, voy a imaginar que tengo una lista con números del 1 al 20, la cual quiero concatenar en una celda.

Así, utilizaremos la función transponer escribiéndola seguida del rango en el que se encuentra nuestro listado:

Sin usar macros

  • 2

    Aprovechar la tecla F9

Ahora, colócate sobre la celda donde colocaste la función en el paso anterior y haz clic en la barra de fórmulas. Acto seguido, presiona la tecla F9

Sin usar macros

La tecla F9, cuando estás dentro de una fórmula, sirve para depurarla y lo que ves en la imagen anterior, es una representación de la matriz que conforma el rango que le acabas de pasar a la función, mostrando los valores reales en lugar de su referencia a las celdas.

Ya te vas imaginando cómo nos podemos aprovechar de esto, ¿verdad?

  • 3

    Editar la fórmula

 Como ves, ya tienes todos los valores concatenados, el trabajo está prácticamente hecho. Lo que necesitas ahora, es quitarle las llaves que están al principio y al final, así como el signo de igual que está junto a la primera llave.

Sin usar macros

 

¡Y ahora está listo!

Una vez realizado esto, tendrás concatenados todos los valores dentro del rango sin usar macros y una vez que lo hagas la primera vez, te darás cuenta de que es más fácil de lo que parece.

Sin usar macros

¿Qué me dices ahora, te animas a probarlo?

Seguro te podrá sacar de algún apuro (que siempre aparece alguno por ahí)

¡Nos vemos!

Raymundo

Calcular el IVA incluido y el porcentaje de una cantidad

Foto cortesía de Freeimages

¿Te has preguntado cómo calcular el IVA cuando ya está incluido en algún precio?

O tal vez desees calcular el valor del IVA de una base imponible. Incluso, necesitamos representar el incremento del IVA y si se puede hacer sin dar muchas vueltas, pues mucho mejor.

Si trabajas con productos en los cuales tienes este dilema, la solución es muy sencilla. Sigue leyendo, porque aquí te muestro las formas de lograr cada uno de estos objetivos.

  • 1

    Calcular el IVA incluido

Para lograr averiguar el valor de la base imponible de un artículo que ya tiene el valor del IVA incorporado en su costo, o lo que es lo mismo, calcular el IVA incluido de una artículo, lo único que tendrías que hacer, es dividir el valor total, entre 1.12 (asumiendo que el porcentaje del IVA es de 12)

Calcular el IVA incluido

 

Hacer esto, te dará el siguiente resultado:

Calcular el IVA incluido

¿Y cómo se si está bien?

Sencillo. Al valor que te apareció, súmale el 12% . Verás que te dan los $1,562.00 originales.

Para porcentajes distintos, solo debes cambiar el valor que viene después del 1. Es decir, si vives por ejemplo en España, creo que el IVA es del 21%. Entonces la fórmula anterior sería: =A2/1.21

¿Lo tienes?

¡Perfecto!

  • 2

    Obtener el IVA de un precio, directamente.

Sencillo. Únicamente debes multiplicar el precio base por el cero y punto más el valor del porcentaje. Es decir, para el IVA del 12%, debes multiplicar por 0.12

Aquí un ejemplo:

Calcular el IVA incluido

Lo que te dará el siguiente resultado:

Calcular el IVA incluido

Ahora, si te fijas, sumando los 167.36 más los 1,394.64 originales, obtendrás los 1,562.00 del ejemplo anterior. Con lo que queda comprobado que la operación inicial está correcta.

  • 3

    Obtener el precio base más el IVA directamente.

En lugar de andar multiplicando y sumando en una fórmula innecesariamente larga, lo único que debes hacer para obtener el precio base con su respectivo IVA sumado, es multiplicarlo por 1.12 (o el porcentaje de tu país), así:

 

Calcular el IVA incluido

 

Lo que te dará el siguiente resultado (¡Sí! Adivinaste)

Calcular el IVA incluido

 

¿Te has fijado?

Una vez más obtenemos el valor inicial de 1,562.00 (el cual es el precio con el IVA incluido)

Esto es así, porque ya habíamos calculado la base imponible en el primer ejercicio.

¡A practicar!

Ahora que ya tienes un nuevo truco para tu arsenal, anda, ve y ponlo en práctica. Mejora tus fórmulas en ese modelo de Excel que tienes y recuerda, que los pequeños trucos suman para lograr grandes soluciones.

¡Nos vemos!

Raymundo

 

 

 

Cómo convertir un texto a fecha, desde el formato AAAAMMDD (año, mes día)

Convertir un Texto a Fecha

Es cosa común encontrarnos con fechas que “bajamos” de algún sistema y vienen en forma de texto. Generalmente, estas fechas en texto vienen con el típico formato AAAAMMDD (año, mes, día). Pero, al tener tantas ventajas en Excel si trabajamos en forma de fecha, no es de extrañar que ahora mismo estés aquí buscando la forma de convertir un texto a fecha.

No te preocupes, aquí te muestro cómo hacerlo.

En artículos como este, surge esta pregunta, ya que es realmente un tema muy conocido y nos lo encontramos frecuentemente cuando trabajamos con datos que vienen del “sistema aquel”.

Para lograr realizar este pequeño truco, debes tener claro qué es la opción “Texto a Columna”. Si aún no sabes bien de qué te estoy hablando, entonces primero pásate por esta entrada.

¿Ahora sí?

Bien. Empecemos.

Convertir un texto a fecha (todas las celdas a la vez)

  • 1

    Selecciona la columna con las fechas

Obviamente, debes seleccionar o “sombrear” la columna donde están las fechas.  Esto es importante si no quieres hacerlo de a uno por uno 😀

Convertir un Texto a Fecha

  • 2

    Elige la opción “Texto a Columna” que se encuentra en la ficha “Datos”.

Sin perder la selección (es decir, no toques nada más) ve hacia las pestañas y busca la que tiene el nombre  “Datos”. Un poco más a la derecha, verás la opción “Texto a columnas”. Solo haz un clic sobre este botón.

Convertir un Texto a Fecha

  • 3

    Configura las opciones del convertidor.

Una vez hecho el paso anterior, se abrirá un cuadro de diálogo en el que tendrás que realizar una pequeña configuración para que el convertidor haga su trabajo correctamente.

Primero, debes seleccionar la opción “De ancho fijo” en la primera pantalla y presiona el botón “Siguiente”.

Convertir un Texto a Fecha

 

Luego,  aparecerá una vista previa de cómo se ven los datos actualmente para el convertidor. Nada que hacer aquí. Presiona el botón “Siguiente”.

Convertir un Texto a Fecha

 

Un último retoque. Debes elegir el formato “Fecha” (por que estamos trabajando en realidad con una fecha ¿o no?)

Y a continuación, seleccionas de la lista desplegable el formato en el que viene. Yo he seleccionado el formato AMD, porque en mi columna primero está el año, luego el mes y finalmente el día.

 

Convertir un Texto a Fecha

 

Presionas el botón “Finalizar” y…

Convertir un Texto a Fecha

 

Automágicamente, se ha transformado todas las celdas que estaban como texto al formato fecha común y silvestre que Excel puede interpretar como tal. Ahora solo te queda comenzar a trabajar y disfrutar del tratamiento que Excel le da a las fechas para obtener una segmentación de datos por mes, sía, semana, cuarto, etc.

Descarga el archivo de ejemplo.

Y practica esta técnica para que no se te olvide.

Descargar.

¿Qué opinas de esta técnica?

¿Te ha servido alguna vez? ¿Qué tan seguido te encuentras con la necesidad de convertir fechas como en el ejemplo?

Te espero en los comentarios.

¡Nos vemos!

Raymundo

7 Atajos de Ratón para Excel que deberías conocer

Atajos de Ratón

Imagen cortesía de Freeimages

¿Te he mencionado alguna vez que los Tips que ayudan a mejorar mi Productividad son mis favoritos?

Los atajos de teclado son muy efectivos para incrementar nuestra velocidad de trabajo con Excel y si los combinamos con atajos de ratón, la cosa pasa a otro nivel. Por eso no te pierdas estos consejos que comparto contigo a continuación.

#1 Cerrar el Archivo Actual, con un doble clic en el Botón o Logo de Office.

Así de simple. Si no has guardado el trabajo actual, Excel te preguntará si deseas hacerlo antes de cerrar. Para cerrar el archivo actual, suele ser más rápido que ‘apuntarle’ con el ratón a la segunda X de la esquina superior derecha de la ventana.

Esto equivale a presionar:

Atajo de Teclado:

CTRL + F4

Ajustar el Ancho de varias columnas a la vez, con un doble clic justo entre ellas.

¿Nunca te ha pasado que necesitas ajustar el ancho de las columnas para que se adapte al contenido? Y en muchas ocasiones hemos terminado haciéndolo columna por columna.
Pues si seleccionas todas las columnas y haces un doble clic, justo en la parte donde se unen dos columnas, conseguirás que todas se ajusten automáticamente a su contenido.

Ajustando el ancho de varias columnas a la vez.

#2 Rellenar Automáticamente una serie, con un doble clic.

Puedes indicarle a Excel que complete automáticamente una serie, si seleccionas lo que has escrito hasta el momento, y haces un doble clic sobre la esquina inferior derecha (el puntero se transformará en una pequeña cruz)
Rellenar una serie con un doble clic

#4 Salta a la última columna o fila con un doble clic.

Si quieres desplazarte hasta la última fila o columna, puedes hacerlo sin necesidad de soltar el ratón. Basta con que hagas un doble clic sobre uno de los lados de la celda activa y Excel automáticamente se desplazará hasta la última celda con contenido, en la misma dirección que elegiste. Mira la siguiente animación para quedar más claros.

Esto equivale a presionar:

Atajo de Teclado:

CTRL + ↓

o

CTRL + →

o

CTRL + ↑

o

CTRL + ←

o

CTRL + ↓

 

Y verás como la celda activa “salta” hasta la última celda con datos en la dirección de la flecha que presionaste:
Salta a la última fila o columna con un doble clic

#5 Gana más espacio en tu área de trabajo haciendo que la cinta de Excel se esconda con un doble clic.

¿Quisieras tener más espacio para trabajar? ¡Entonces quitemos la cinta de Excel!
O bueno, basta con esconderla. 🙂

Para hacerlo, debes hacer doble clic sobre cualquiera de las pestañas que marcan las diferentes secciones de la cinta, tal y como lo muestra la siguiente animación.

Esconde la Cinta de Excel 2007 con un doble clic

Esto equivale a presionar:

Atajo de Teclado:

CTRL + F1

#6 Desplazarte por las secciones de la cinta de Excel, usando la ruedita del ratón.

¿Quieres desplazarte más rápido por las opciones de la cinta de Excel? Pincha sobre una ficha y entonces utiliza la ruedita del ratón. ¡Del ratón no desaprovechamos nada!
Desplazarte por las secciones de la cinta de Excel

#7 Reutilizar un formato cuantas veces necesites, con un doble clic.

Dime: ¿Cuántas veces quisiste dejar una celda exactamente igual a otra, pero no recordabas todos los pasos que hiciste al momento de darle formato? O tal vez eran muchos pasos.

Ayudaría mucho si usaras una forma de copiar esos pasos de una sola ves. ¿No lo crees?

Para lograrlo podrías hacer un pegado especial, o podrías usar la Brocha de Formato. El inconveniente con la brocha, es que tendrías que copiar el formato dando clic sobre este icono y luego sobre la celda destino, repitiendo estos dos pasos por cada celda que necesites formatear.

Hay un truco muy sencillo que consiste en hacer doble clic sobre el icono de la brocha y esta quedará ‘anclada’, de manera que el formato no se perderá y podrás aplicarlo tantas veces como desees, hasta que lo ‘liberes’ con un clic nuevamente sobre el icono.

Reutilizar un formato varias veces con un doble clic

Ahora es momento de probar tus Superpoderes

Bueno, ni tanto 🙂 Lo cierto es que si utilizas estos tips y los complementas con tus atajos de teclado favoritos, te convertirás en una máquina de productividad al momento de trabajar con Excel.

Que no se quede en tu memoria nada más. Busca la forma de aplicarlos en tu día a día. Seguramente más de uno te servirá 😉

¿Tienes un atajo de ratón favorito?

Me gustaría saber cuál es tu atajo de ratón favorito. ¿Conoces alguno que te gustaría mencionar? ¿Tienes tu propia lista de atajos de ratón? Cuéntame sobre ello.

Si te ha gustado este artículo o crees que a alguien puede serle de utilidad, no dudes en compartirlo en Twitter o Facebook usando los botones de abajo. Te lo agradezco =)

¡Nos vemos!

Raymundo