Usar la tabla de datos Y si … de Excel para generar un montón de resultados aleatorios. Incluso si tiene una fórmula complicada que es el resultado de varios pasos, la tabla de datos le permitirá generar cientos de respuestas al modelo sin repetir el modelo cientos de veces.
Ver video
- El objetivo es crear datos de muestra con producto; producto; producto; producto
- El objetivo es tener siempre 2 o más productos, hasta un máximo de 12
- Almacene una lista de productos en una lista personalizada para que pueda generar fácilmente una columna de productos individuales
- El uso de RANDBETWEEN () puede devolver elementos duplicados en la lista
- Utilice la función RAND () para decidir si este producto está incluido o no
- Use TEXTJOIN () para concatenar los no espacios en blanco con punto y coma en el medio
- Ahora que tiene un resultado, ¿cómo puede obtener muchos resultados?
- Es sorprendente que una copia y varios valores de pegado peguen el resultado actual de la fórmula
- Acelere los valores de pegado usando F4 para rehacer
- Pero, de una manera súper rápida: use herramientas Y si … y una tabla de datos con una celda vacía como celda de entrada de columna
- Gracias al profesor Simon Benninga por este método.
Transcripción del video
Learn Excel From, Episodio 2155: Genere varios resultados aleatorios a partir de una fórmula.
Oye. Bienvenido de nuevo al netcast. Soy Bill Jelen. Bueno, además del podcast y la escritura de libros, escribo un artículo mensual para la revista Strategic Finance. Estaba trabajando en el artículo del próximo mes en el que les mostraba cómo usar Power query para dividir una columna de; separé los datos en filas y necesitaba generar algunos datos falsos para eso, y por qué no abrí el archivo del episodio 2097, no tengo idea. Solo quería crear algunos datos falsos. Entonces, entre 2 y 12 productos en una celda y, en el proceso de hacerlo, utilicé muchos trucos del podcast: unión de texto; llenar MANZANA, PLÁTANO, CEREZO; Caminata aleatoria; F4 To Rehacer - y creo que a lo largo del camino descubrí algunas cosas interesantes sobre cómo acelerar este proceso.
Ahora, en primer lugar, hubiera sido genial si pudiera haber creado una fórmula de matriz enorme que hubiera generado esos datos. Podría haber copiado esa fórmula, pero no pude alcanzar mi copia de CTRL + MAYÚS + ENTRAR y solo estaba buscando algo más simple esa mañana. Soy un gran fan de RANDBETWEEN. Yo uso RANDBETWEEN todo el tiempo. Entonces, si tuviéramos una lista de 12 productos y luego, aquí, generamos una serie de respuestas usando RANDBETWEEN, entonces el índice de A1 a A12, pidiendo un número aleatorio del 1 al 12, bueno, cada vez que presiono F9, obtengo una lista diferente de productos, y luego quiero un número diferente de productos en cada uno, así que aquí, RANDBETWEEN de 2 a 7 o 2 a 12 o cualquiera que sea el límite superior o inferior, y luego usar TAXJOIN, esa nueva función increíble en Office 365, separado por;, ignore los espacios en blanco, y luego 'Si va desde E2 hasta cualquier lugar de E2 a E12, basado en el sexto valor en este caso, generará esa lista, ¿de acuerdo? Pero la razón por la que no me gusta esto es que seguí presionando F9, mira, empiezo a recibir repeticiones y, ya sabes, supuestamente se trata de pedidos provenientes de un sitio web y ¿por qué alguien pediría ELDEBERRY? ELDERBERRY simplemente no tiene sentido, ¿verdad? Entonces, no me gustó la posibilidad de obtener DATE DATE. Quería tener una lista única. Entonces, esto es lo que decidí que iba a hacer.supuestamente se trata de pedidos procedentes de un sitio web y ¿por qué alguien pediría ELDEBERRY? ELDERBERRY simplemente no tiene sentido, ¿verdad? Entonces, no me gustó la posibilidad de obtener DATE DATE. Quería tener una lista única. Entonces, esto es lo que decidí que iba a hacer.supuestamente se trata de pedidos procedentes de un sitio web y ¿por qué alguien pediría ELDEBERRY? ELDERBERRY simplemente no tiene sentido, ¿verdad? Entonces, no me gustó la posibilidad de obtener DATE DATE. Quería tener una lista única. Entonces, esto es lo que decidí que iba a hacer.
En primer lugar, iba a crear una lista de los 12 productos y tengo esto memorizado como una lista personalizada, así que puedo generar una buena lista alfabética de elementos, y luego quería entre 2, ya sabes, aproximadamente 7 de estos, así que lo que hice aquí es que digo = SI el ALEATORIO. RAND es una función impresionante que genera un decimal de 0 a 1 es <.6. Entonces, en otras palabras, en aproximadamente el 60% de los casos, quiero que traiga ese producto aquí a la columna B, de lo contrario no me dé nada “”. Lo copiaré. Lo que me va a hacer es generar una lista de productos. Nunca habrá repeticiones. No hay posibilidad de repeticiones, y cada vez que presiono F9, obtengo una lista diferente de productos y, sí, parece que estamos obteniendo, ya sabes, la cantidad correcta de productos cada vez. (= SI (ALEATORIO () <0.6, A1, “”))
A continuación, título del gráfico; nos ofrecen dos lugares diferentes para un gráfico: Arriba del gráfico a Muy bien. Entonces, ahora que tenemos eso, la nueva función en Excel en Office 365 es TEXTJOIN. Me encanta esto. El delimitador será un; y luego ignorar vacío. No importa. En realidad… sí, no, realmente no importa aquí. Eso es lo importante. Vamos a ignorar el vacío. VERDADERO, y luego aquí está nuestra lista de productos como ese. Bueno. Entonces, ahí tenemos nuestra lista de productos para la primera fila, pero necesito generar un montón de estos, y aquí es donde realmente llegamos al problema, el problema que estaba tratando de resolver en este caso particular. (= TEXTJOIN (“;”, TRUE, B1: B12))
Ahora, si simplemente copiara esa fórmula, está bien, si tomé esa fórmula original y vine aquí y edité esto, elija:, presione F4 para asegurarme de que tengo una referencia absoluta y la copie, usted Veré que termino con elementos idénticos hasta el final. No son datos falsos muy interesantes, ¿verdad? Entonces, eso no va a funcionar. Lo que tengo que hacer es tomar el resultado de esta fórmula y crear un montón de ellos, ¿de acuerdo? (= TEXTJOIN (";", VERDADERO, $ B $ 1: $ B $ 12))
Entonces, inicialmente hice esto. Hice CONTROL + C, y luego vine aquí y voy a pegar valores especiales, o supongo que eso es solo pegar, y PEGAR VALORES así, está bien, y lo que es fascinante para mí, y he hablé de esto una vez en el podcast antes y todos en los comentarios de YouTube estaban, bueno, por supuesto que va a funcionar; no, lo que me fascina es que copié la celda C14, por lo que pensaría que, cuando copié C14, el texto de C14 se habría copiado en el portapapeles, pero no es así. Está apuntando a C14, ¿verdad? Entonces, la primera vez que pegué, obtuve CHERRY, DATE, ELDERBERRY, pero ahora ves que C14, las hormigas que marchan, ha cambiado a APPLE, CHERRY, FIG, así que voy a venir aquí y yo ' Voy a PEGAR VALORES nuevamente, y siempre me sorprende que haya cambiado al nuevo valor.
Muy bien, entonces, si pudiera PEGAR VALORES, PEGAR VALORES, PEGAR VALORES, PEGAR VALORES, generaría una nueva respuesta cada vez. Esta vez cuando PASO VALORES, MANZANA, PLÁTANO, FECHA, HIGO, ICEBERG, JACKFRUIT, pero mira, es un lío agarrar el mouse y venir aquí y elegir PEGAR y elegir VALORES. Entonces, voy a usar la increíble función REDO, no UNDO, REDO, que es F4, entonces F4, pega el nuevo valor. Cuando presiono F4, obtendré pero BANANA, DATE, ELDERBERRY, LIME. Entonces, es simple. F4, FLECHA ABAJO, F4, FLECHA ABAJO, F4, FLECHA ABAJO, está bien, y la vida es genial. Ahí, tengo suficientes datos falsos para el artículo, está bien, pero incluso eso es una molestia, ¿de acuerdo?
Entonces, el método que aprendí de un buen amigo mío que ahora falleció, el profesor Simon Benninga me enseñó esto, si tenemos un modelo, y esto es esencialmente un modelo, es usar RAND o RANDBETWEEN y generar un resultado, lo que puede hacer es que sucedan múltiples versiones de ese resultado, de acuerdo, y tenemos que comenzar desde la celda a la izquierda del resultado de nuestro modelo, elegir esa celda y la celda que contiene su fórmula, y luego , sin importar la cantidad que desee, digamos que necesito 100 de estos o 132 de estos, simplemente copie o seleccione hasta el final, y vamos a ir aquí a la pestaña DATOS, la pestaña DATOS, Y SI ANÁLISIS, TABLA DE DATOS, ¿de acuerdo?
Ahora, utilizo esto todo el tiempo para mostrar cómo crear múltiples escenarios pero, en este caso, realmente no tenemos nada para ROW INPUT CELL. Para la CELDA DE ENTRADA DE COLUMNA, simplemente elija cualquier celda vacía, no importa qué celda sea, y esto ejecutará este modelo 132 veces, cada vez que esencialmente presione F9 para generar nuevos valores aleatorios como ese, haga clic en Aceptar y, bam, y funciona. Me encanta eso.
Ahora, esto está en vivo. Cada vez que presiono F9, obtengo un nuevo conjunto de 132 de estos. Entonces, solo copie - CONTROL + C - y venga aquí, PEGUE VALORES ESPECIALES, y tenemos nuestra lista falsa de productos, y estamos listos para hacer esencialmente lo que estaba en el episodio 2097: dividir una columna de x; x; x; a filas. Le recomiendo encarecidamente que consulte ese video, es un gran video, o la edición de noviembre, la edición de noviembre de 2017, de la revista Strategic Finance. Saldrá en línea a principios de noviembre.
Bien. Entonces, todos estos métodos están en el libro Power Excel With, edición 2017. Haga clic en la i en la esquina superior derecha para verlo.
Recap of what we talked about today: the goal is to create a sample data set with ; separated products; always two or more products up to a max of 12; so I stored a list of products in a custom list so you can easily generate a single column of products; using RANDBETWEEN, that would have worked but we have duplicates in the list so I'm using the RAND function to say is this product in or not; then the TEXTJOIN function to concatenate the non-blanks with ;s in between; but then the big question, now that you have one result, how do you make multiple results using the RAND function; well, that's surprising that one copy and then multiple paste values will give you different answers because it's always recalculating after each paste; and you could speed that using F4 to redo the paste values. Not a bad way to go, but the superfast way, thanks to Professor Benninga, use the WHAT-IF tools and a data table with an empty cell as the COLUMN INPUT CELLS and you'll be able to generate hundreds of random results very, very quickly. Alright, there you go.
Este hola. Quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2155.xlsm
