2 votos

Asignación de una lista de números mensuales aleatorios en 3 nuevas listas rotativas basadas en una cantidad máxima permitida por lista a la vez

Tengo una lista mensual de la cuenta de un suceso (por ejemplo, mes 1 = 4, mes 2 = 3.7, mes 3 = 4.1, mes 4 = 4.0, etc.). Quiero asignados a estos números, mensual, a 3 columnas por su valor y su secuencia. La columna 1 será el día 1 de 4 disponible en la lista, Columna 2 será el próximo 4, Columna 3, será el próximo 3, Columna 1 será el próximo 4 de nuevo y la secuencia de bucle. Puede alguien me ayuda con las funciones de Excel para los tres columnas que va a satisfacer esta?

Aquí es lo que la hoja de cálculo sería así:

2voto

john Puntos 248

Editar -

  • Reducción de Fórmula Original a la Forma más Simple (o es?).
  • Agregados los Rangos con Nombre y Cero Pre Intervalo (las más todavía).

Muy fresco problema! Aquí está la solución:



=IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) < B$1, IF( MOD( SUM($B$1:B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)) < $A2, MOD( SUM($B$1:B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)), $A2)) + IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0, IF( MOD( SUM($B$1:B$1,-B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)) < $A2, IF( MOD( SUM($A$2:$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) < B$1, MOD( SUM($A$2:$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)), B$1))) +INT($A2/SUM($B$1:$D$1))*B$1

Copiar y pegar en la Barra de Fórmula después de la selección de B2 Este es un multi fila formato de fórmula, tanto para mejorar la legibilidad y para el mantenimiento. Cambiar el tamaño de la barra de fórmulas arrastrando hacia abajo desde la barra del borde inferior. La copia directamente en una celda en la hoja más probable es que poner la fórmula en varias filas que no es bueno.

Pulse intro y, a continuación, seleccione la celda de fórmula. Copia de arrastrar la fórmula a través de hasta que esté en la última Lista de encabezado de columna. Mientras que todas las celdas están seleccionadas, copia arrastra hacia abajo la fila a la vez (sólo un acceso directo) para sin embargo, muchos de los insumos serán calculados.

Un par de puntos. Todo lo que sabía es que se necesita el módulo, consisten en intervalos y que el círculo era la mejor forma de visualizar el problema. Las columnas a sí mismos ya estaban módulo (el punto entero de la OP). Después de aplicar MODs para el principio y el final del rango de valores de cada fila de datos ( MOD(SUM(<blue column to current row>))), nada se desató.
Un viaje rápido a google "modular de matemáticas de rotación" y Fabián "de espalda" Giesen del papel en los Intervalos en Aritmética Modular apareció. Un gran agradecimiento "de espalda".

El documento explica mucho, y aunque no entrar en el cálculo de la superposición de rango cantidades, contenía dos muy esclarecedor piezas de información.

  • Esta Java fragmentos de código para probar si las regiones superpuestas:
    modN(c - a) <= modN(b - a) || modN(a - c) <= modN(d - c)
    Los lados de la O (||) se codifican directamente en la fórmula. Por un lado se está en el primer IF y a mitad de camino a través de la fórmula, el otro lado es el otro principal IF.
  • Y tan importante este poco sobre el módulo de la aritmética:
    La mayoría de los lenguajes de programación de uso de truncar la división en su lugar, lo que significa que el módulo tiene valor absoluto menor que N, pero puede ser negativa; es necesario tener esto en cuenta al giro de cualquiera de las ecuaciones aquí en código!

Una rápida Excel prueba confirmó que =MOD(-1,10) resultó en 9, como debe ser. Esto simplificó todo, garantizar los cálculos de distancia sería correcto sin un montón de complicada, pero necesaria IFs tuvo el resultado ha -1.

La razón por esto es tan importante es en mod10 la distancia de 4 a 5 es 1 (calculado Final - Inicio = Distancia). Entonces, ¿cuál es la distancia de 5 a 4? Desde el 5 es mayor que 4 y este es mod10 aritmética, primer viaje a la final y, a continuación, a partir de la final/principio, pasar a la 4. O (10-5=5) + 4 = 9.

El uso de Mod10(4-5) en Excel es MOD(4-5,10) que es MOD(-1,10) = 9. Perfecto! Sólo uso el MOD(Final - Inicio, el módulo) y ninguna otra IFs o parciales de sumas de dinero (o de pesadilla repetida cálculos) son necesarios.

Sobre el código

  • Usted puede agregar listas adicionales mediante la modificación de todas las apariciones de
    • $B$1:$D$1 cambio de la D a la última columna de la Lista de la carta.
    • Considere la posibilidad de usar el bloc de notas para buscar y reemplazar.
      Copiar y, a continuación, pegar la espalda a la barra de fórmulas.
  • No es la cantidad perfecta de absolutas, relativas y mixtas referencias de rango - no cambie estos.
  • Para mover la fórmula, seleccione el rango A1:D2, a continuación, cortar y pegar a la nueva ubicación. Esto debe mantener toda la Absoluta y la Relativa rango de referencia en relación a sus rangos de entrada.
  • El máximo de la columna de cantidades son utilizados por esta fórmula, el cambio de uno de estos va a cambiar todos los cálculos en la tabla.
    • Para preservar los ya asignados los valores, seleccione las celdas a conservar, copiar, pegar especial - solo los valores de nuevo en las mismas células de manera efectiva la sustitución de esas células fórmulas con lo que había sido la actualidad se calcula los valores.
    • Usted puede necesitar una fila ficticia, azul entrada de columna, para ajustar las asignaciones de ir adelante, ya que todos los cálculos (aunque independiente el uno del otro) son de forma acumulativa basada en el azul de la columna y los valores actuales de los max de la lista de asignaciones.
  • Para iniciar una nueva tabla que comienza en el viejo dejó:
    • El uso de un maniquí de contribución fecha de la primera fila y calcular el importe de la última asignado a la columna de la entrada(s).
    • Puede haber sido parciales múltiples entradas, a lo largo de varios filas, ya que la satisfacción de la columna anterior. Suma de todos ellos para la última columna asignada, pero sólo las entradas realizadas en la última columna desde la columna anterior recibió una asignación.
    • Si hay más de una columna de la última fila tiene un valor sólo la suma de la última columna asignada. Si hubo un parcial a la última columna y un parcial a la primera columna, luego la última columna se mostró satisfecho y la primera columna es la última columna asignada.
    • Agregar a la suma el máximo para cada columna anterior, independientemente de cuándo, o en qué fila se entró en, o si existen varias entradas para los demás columnas. Sólo el máximo de cada columna anterior. La primera columna no tiene columnas anteriores.
    • Esta suma (última columna contribuido a que los maxes antes) es el primer azul número de la columna a poner con el maniquí de la fecha.
  • Hay un guardia en el código con respecto a un rango de solapamiento cantidad se suman dos veces cuando una condición de contorno se produce. Parece que fue el único necesario, pero el uso de beta puede determinar lo contrario.
    • + IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0,
  • Para hacer el cero es desaparecer el uso de este Formato de Celda truco: seleccione Custom y entrar en este [=0]"";General

Una nota final: Hay un código de corrección para el módulo fórmula que permite valores para ser añadido mayor que el módulo de la gama. (Que se envuelve a sí misma). Esto significa que si la cantidad introducida es mayor que 11 de las Listas todavía será actualizado correctamente:

  • +INT($A10/SUM($B$1:$D$1))*D$1

Edit - me di cuenta de dos cosas después de la publicación de la solución original:

  1. La fórmula se puede simplificar mediante el uso de un cero de inicio a partir de intervalo.
  2. Los Rangos con nombre puede ser relativa, y por lo tanto pueden "Crecer".

Poner los dos juntos por una más limpia, más manejable fórmula:



=IF( MOD( SUM(I2c) - SUM(I1a), SUM(Modulus)) < I1b_a, IF( MOD( SUM(I1b) - SUM(I2c), SUM(Modulus)) < I2d_c, MOD( SUM(I1b) - SUM(I2c), SUM(Modulus)), I2d_c)) +IF( MOD( SUM(I2c) - SUM(I1a), SUM(Modulus)) <> 0, IF( MOD( SUM(I1a) - SUM(I2c), SUM(Modulus)) < I2d_c, IF( MOD( SUM(I2d) - SUM(I1a), SUM(Modulus)) < I1b_a, MOD( SUM(I2d) - SUM(I1a), SUM(Modulus)), I1b_a))) +INT(I2d_c/SUM(Modulus))*I1b_a

Cosas a tener en cuenta:

  • Esta es una "versión cero" de la fórmula y requiere un cero de la célula, en el principio de
    en ambos intervalos, las Listas y los Intervalos de los Valores de Entrada de los Intervalos (en la columna azul).
    • La Celda a la derecha por encima de la azul rango de entrada y justo a la izquierda de la Lista de valores máximos
      (Las células rojas "A1", en la segunda imagen), debe estar Vacío o 0 (Cero).
    • Este es el cero tara para ambos intervalos primera posición, así que de inicio puede ser obtenida mediante la suma de todos los anteriores intervalos, incluso cuando la posición es el primer intervalo y no hay ninguna anterior intervalos. Ahora hay un intervalo anterior con un tamaño de cero. (La posición de inicio ya no necesita ser calculado como Inicio = Fin - Distancia).
  • Esta versión de la fórmula utiliza Rangos con Nombre.

    • Para mover esta fórmula, el "Nombre de Rangos deben ser editado.
    • Los Rangos con Nombre son "relativos a"; Esto significa que son relativos a la celda seleccionada antes de editar el rango relativo de columna o de fila.
    • El uso de la segunda imagen del Administrador de nombres Diálogo como una referencia. La celda "B2" está seleccionado en la imagen, de modo que todos los valores relativos en el Nombre del Administrador de la imagen son relativos a la celda "B2".
    • A la hora de crear el Nombre del Administrador de adiciones o modificaciones:

      1. Seleccione la primera celda de datos (superior izquierda de la celda de fórmula; "B2" en la imagen).
      2. Abrir administrador de nombres.
      3. Crear la misma Columna relaciones con la Lista de valores máximos y la
        Fila relaciones con el Azul de los valores de Entrada.
      4. Para determinar el estado de no-signo de dólar referencias, el uso de la imagen para determinar el número de células arriba y a la izquierda de "B2" a la de la imagen con el nombre del valor del rango.
      5. Aplicar la izquierda y contar a la celda seleccionada actualmente para determinar la verdadera entrada a hacer para el rango con nombre nuevo elemento o editar.
    • Otro aspecto positivo, la advertencia de "fórmula ... con rango de ... adicional ..." desaparece.

Sólo para la integridad, aquí está el cero de la versión sin rangos con nombre que puede ser movido por la corte A1:D2. Y la no-cero de la versión con los rangos con nombre, sólo en caso de "A1" no puede estar vacío. (Sugerencias rápidas, combinar el cero de la célula con el uno a su izquierda y utilice la celda combinada para el texto. De esta manera, el cero de la celda se suma a 0.)

El cero de la Versión sin rangos con nombre:

=IF( MOD( SUM($A$1:$A1) - SUM($A$1:A$1), SUM($B$1:$D$1)) < B$1, IF( MOD( SUM($B$1:B$1) - SUM($A$1:$A1), SUM($B$1:$D$1)) < $A2, MOD( SUM($B$1:B$1) - SUM($A$1:$A1), SUM($B$1:$D$1)), $A2)) + IF( MOD( SUM($A$1:$A1) - SUM($A$1:A$1), SUM($B$1:$D$1)) <> 0, IF( MOD( SUM($A$1:A$1) - SUM($A$1:$A1), SUM($B$1:$D$1)) < $A2, IF( MOD( SUM($A$2:$A2) - SUM($A$1:A$1), SUM($B$1:$D$1)) < B$1, MOD( SUM($A$2:$A2) - SUM($A$1:A$1), SUM($B$1:$D$1)), B$1))) +INT($A2/SUM($B$1:$D$1))*B$1

Rango versión sin el cero de la célula:

=IF( MOD( SUM(I2d,-I2d_c) - SUM(I1b,-I1b_a), SUM(Modulus)) < I1b_a, IF( MOD( SUM(I1b) - SUM(I2d,-I2d_c), SUM(Modulus)) < I2d_c, MOD( SUM(I1b) - SUM(I2d,-I2d_c), SUM(Modulus)), I2d_c)) +IF( MOD( SUM(I2d,-I2d_c) - SUM(I1b,-I1b_a), SUM(Modulus)) <> 0, IF( MOD( SUM(I1b,-I1b_a) - SUM(I2d,-I2d_c), SUM(Modulus)) < I2d_c, IF( MOD( SUM(I2d) - SUM(I1b,-I1b_a), SUM(Modulus)) < I1b_a, MOD( SUM(I2d) - SUM(I1b,-I1b_a), SUM(Modulus)), I1b_a))) +INT(I2d_c/SUM(Modulus))*I1b_a

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: