Este consejo fue escrito por Valentin, con quien estoy agradecido.
Copiando datos importados (de columna a fila) de otra hoja usando un arreglo de fórmula.
Una breve introducción a arreglos de fórmulas.
Un arreglo de fórmula puede realizar multiples cálculos ya sea de un solo resultado o de varios. Los arreglos de fórmulas actúan en dos o más conjuntos de valores conocidos como argumentos. Cada arreglo de argumento debe tener el mismo número de filas y de columnas. Puedes crear un arreglo de fórmulas del mismo modo que otras fórmulas, excepto que debes presionar CTRL + SHIFT + ENTER para ingresar la fórmula.
El tema del arreglo de fórmulas es tan vasto que puedes escribir todo un libro sobre esto.
Copiaremos los números y estrellas para el sorteo desde la hoja de “Resultados” con un arreglo de fórmula a la hoja de “Control”.
En la hoja de “Control”, selecciona las celdas C4 a G4, haz click en “Bordes”, selecciona “Todos los Bordes”, con las celdas seleccionadas, escribe = TRANSPONER (haz click en la hoja de “Resultados”, selecciona las celdas B9 a B13) y presiona Ctrl + Shift + Enter.
En la hoja de “Control”, selecciona las celdas I4 y J4, haz click en “Bordes”, selecciona “Todos los Bordes”, con las celdas aún seleccionadas, escribe = TRANSPONER (haz click en la hoja de “Resultados, selecciona las celdas B14 y B15) y presiona Ctrl + Shift + Enter.
En la hoja de “Control”, selecciona las celdas C7 a G7, Presiona el botón Ctrly selecciona las celdas I7 y J7, suelta la tecla Ctrl, haz click en “Bordes” y selecciona “Todos los Bordes”.
Selecciona las celdas L3 y M3, Selecciona “Combinar y Centrar” haz click en “Bordes”, selecciona “Bordes Exteriores”, escribe los números correctos y presiona Enter.
Selecciona las celdas L4 y M4, haz click en “Bordes”, selecciona “Todos los Bordes”, escribe “Numeros” en la celda L4 y “Estrellas” en la celda M4 y presiona Enter.
Compara datos usando un arreglo de fórmula.
Compararé nuestros números con los números del sorteo. Selecciona las celdas L7 y M7, haz click en “Bordes”, selecciona “Todos los Bordes”. Haz click en la celda L7 y escribe la fórmula = SUMA (CONTAR.SI (C7: G7, C $ 4: G $ 4)) (Usamos la referencia de celda absoluta para la celdas C4 y G4 por que podríamos luego necesitar copiar la fórmula a las celdas de abajo) presiona Ctrl+Shift+Enter.La fórmula ahora cambia a un arreglo de fórmula {= SUMA (CONTAR.SI (C7: G7, C $ 4: G $ 4))}.
Si deseamos obtener el mismo resultado con una fórmula simple que se vería así: (todo escrito consecutivamente).
= CONTAR.SI (C7: G7, C $ 4) + CONTAR.SI (C7: G7, D $ 4) +
CONTAR.SI (C7: G7, E $ 4) + CONTAR.SI (C7: G7, F $ 4) + CONTAR.SI (C7: G7, G $ 4).
Haz click en la celda M7 y escribe la fórmula = SUMA (CONTAR.SI (I7: J7; I $ 4: J $ 4)) (Aquí convertimos las celdas I4 y J4 en absolutas) y presiona Ctrl + Shift + Enter..
La fórmula ahora ha cambiado a un arreglo de fórmula {= SUM (CONTAR.SI (I7: J7; I $ 4: J $ 4))}
Obviamente esto arroja 0, ya que no hay ningún numero en las celdas C7 a G7, I7 y J7
Búsqueda de ganancia a travéz de un arreglo de fórmula.
Usaremos un arreglo de fórmula para buscar las cantidades que hemos ganado.
Usamos la función = INDICE y COINCIDIR =.
En la hoja de “Control” selecciona la celda O7, Haz click en “Bordes”, selecciona “Todos los Bordes”, Escribe = INDICE (haz click en la hoja de “Resultados”, selecciona las celdas F20 a F31 y vuelvelas absolutas (Presiona F4), escribecoma y COINCIDIR (haz click en la hoja de “Control”, selecciona la celda L7 , escribe “& “, selecciona la celda M7 y escribe coma “,”. Haz click en la hoja de “Resultados”, selecciona las celdas B20 a B31, vuelvelas absolutas (Presiona F4), escribe “&”, selecciona las celdasC20 a C31, vuelvelas absolutas (Presiona F4), escribe coma y 0)) presiona Ctrl + Shift + Enter.
La fórmula ahora cambia a un arreglo de fórmula. (Aquí también escribe todo consecutivamente).
{= INDICE(Resultados! $ F $ 20: $ F $ 31, COINCIDIR (Control! L7 & Control! M7,
Resultados! $ B $ 20: $ B $ 31 & Resultados! $ C $ 20: $ C $ 31, 0)))}
Las celdas L7 y M7 de la hoja de “control” están unidas con el signo (&), esto hace que lo tome como una sola celda. Tambien podemos hacer esto en los contenidos de las celdas B20: B31 y C20: C31 de la hoja de “Resultados”, fila por fila.
Como puedes ver por el resultado obtenemos un error, # N / A , este error indica que el valor no esta disponible para una función o fórmula.
La fórmula con las combinaciones 0 & 0, 0 & 1, 1 & 0, 0 & 2, 2 & 0 no se encuentran en el área de celda de Resultados! $ B $ 20: $ B $ 31 & Resultados! $ C $ 20: $ C $ 31
Nosotros por lo tanto aplicamos la fórmula como sigue, haz click en la hoja de “Control”, coloca el cursor en la celda O7, escribe SI ((L7 + M7)<3,””, (en la barra de fórmula luego de ingresar el signo de igual), si la suma de las celdas L7 y M7 es menor que 3, no te da nada (dos comillas en una fila significan vacío o nada). Coloca el cursor luego del último cierre de paréntesis de la fórmula y agrega un cierre de paréntesis extra, y luego presiona CTRL + SHIFT + ENTER
La fórmula es (todo seguido)
{= IF ((L7 + M7) <3, “”, INDICE (Resultados! $ F $ 20: $ F $ 31, COINCIDIR (Control! L7 & Control! M7;
Resultados! $ B $ 20: $ B $ 31 & Resultados! $ C $ 20: $ C $ 31, 0)))}
Convierte la celda O7 en tipo de cambio.
Selecciona las celdas C7 a O7 y arrastra la manija de llenado de la celda O7 tantas filas hacia abajo como el número de combinaciones que tengas.
Coloca el cursor en una celda (no importa donde), presiona CTRL + A(Seleccionar Todo), presiona Ctrl + B (Todo en Negritas) haz click en Centrado, y luego de regreso a la celda, ahora todo esta centrado y en negritas.
Haz click derecho en la hoja de “Resultados” y selecciona “Ocultar”.
Haz click derecho en la “Hoja3” y selecciona “Eliminar”.
En la pestaña “Diseño de Página” en la cinta, selecciona los recuadros ‘ver’ bajo “Líneas de cuadrícula” y “Encabezados” (1).
Actualizar Datos
Naturalmente queremos saber cuanto ganamos en cada sorteo, asi que actualizamos nuestros datos.
Podemos usar 3 métodos, actualizar manualmente, auto actualizar o actualizar usando un macro, no hablaremos sobre el método de macro aquí.
Método 1 actualizar manualmente:
Ve a la pestaña “Datos” en la “Cinta” y haz click en el icono sobre “Actualizar Todo” (2), y los datos son actualizados.
Método 2 Actualizar automáticamente:
Ve a la pestaña “Datos” en la Cinta y haz click en “Conexiones” (1).
Solo hay una conexion en la ventana “Conexiones del Libro”, haz click en “Propiedades” (1).
En las “Propiedades de Conexion”, cambia el nombre de “conexion” a uno más apropiado como “Control EuroMillions” (1) y haz click en el cuadro junto a “Actualizar datos al abrir un archivo” (2), el resto lo dejamos como esta y hacemos click en Aceptar, luego en “Cerrar”.
Ahora cada vez que abras el archivo los datos serás actualizados.
En las celdas C7 a G7, I7 y J7 y las celdas subyacentes, puedes llenar tus propias combinaciones.
Guarda el archivo von un nombre apropiado como “Control EuroMillions” y buena suerte con los sorteos.
Funciones Usadas:
Transponer
Indice Coincidir
Para tu información, Swotster.com piensa en sus estudiantes, así que recuerda Swotster.com
