Редактировать -
- Уменьшенная исходная формула до простейшей формы (или это?) .
- Добавлены именованные диапазоны и нулевой предварительный интервал (еще короче) .
Очень крутая проблема! Вот решение:
=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
Скопируйте и вставьте в панель формул после выбора B2 Это формула с несколькими строками, как для удобства чтения, так и для обслуживания. Измените размер панели формул, потянув вниз от нижнего края панели. Копирование непосредственно в ячейку на листе, скорее всего, поместит формулу в несколько строк, что не годится.
Нажмите ввод, а затем выберите ячейку формулы. Скопируйте и перетащите формулу, пока она не окажется под заголовком последнего столбца списка. Пока все ячейки все еще выбраны, скопируйте и перетащите вниз всю строку сразу (просто ярлык), чтобы рассчитать сколько угодно входных данных.
Пара моментов. Все, что я знал, это то, что он должен быть модульным, состоять из интервалов и чтобы круг был лучшим способом визуализировать проблему. Сами столбцы уже были модульными (весь смысл ОП). После применения MOD для значений начала и конца диапазона для каждой строки данных (то есть MOD(SUM(<blue column to current row>))), ничего не возникло.
Появилось быстрое путешествие в Google "Модульное вращение математики" и статью Фабиана "Рыга" Гизена « Интервалы в модульной арифметике» . Большое спасибо "ryg".
В документе многое объясняется, и хотя в нем не учитывались суммы перекрытия диапазонов, в нем содержались две очень поучительные части информации.
- Этот фрагмент кода Java проверяет, перекрываются ли области:
modN(c - a) <= modN(b - a) || modN(a - c) <= modN(d - c)
Стороны ИЛИ (||) закодированы непосредственно в формулу. Одна сторона находится в первом первичном IF а половина - через формулу, а другая - в другом первичном IF .
- И, что не менее важно, этот бит о модульной арифметике:
В большинстве языков программирования вместо этого используется усеченное деление, что означает, что модуль имеет абсолютное значение меньше N, но может быть отрицательным; Вы должны учитывать это, превращая любое из приведенных здесь уравнений в код!
Быстрый тест Excel подтвердил, что =MOD(-1,10) 9, как и должно быть. Это все упростило, гарантируя, что расчеты расстояния будут правильными без запутанных, но необходимых IF если результат будет -1.
Причина, по которой это так важно, заключается в том, что в модуле mod10 расстояние от 4 до 5 равно 1 (рассчитывается как End - Start = Distance). Так каково расстояние от 5 до 4? Поскольку 5 больше 4 и это арифметика mod10, сначала переместимся в конец, а затем, начиная с конца / начала, перейдем к 4. Или (10-5 = 5) + 4 = 9.
Использование Mod10(4-5) в Excel - это MOD(4-5,10), которое является MOD(-1,10) = 9. Отлично! Просто используйте MOD(Конец - Начало, модуль), и никакие другие IF или частичные суммы (или кошмарные повторные вычисления) не нужны.
О коде
- Вы можете добавить дополнительные списки, изменив все вхождения
$B$1:$D$1 заменяет букву D на последнюю букву столбца списка.- Рассмотрите использование блокнота, чтобы найти и заменить.
Копирование, а затем вставка обратно в панель формул.
- Существует абсолютное количество ссылок на абсолютные, относительные и смешанные диапазоны - не меняйте их.
- Чтобы переместить формулу, выберите диапазон A1:D2, затем обрежьте и вставьте в новое место. Это должно поддерживать все задания абсолютного и относительного диапазонов относительно их входных диапазонов.
- Максимальное количество столбцов используется по этой формуле, изменение одного из них изменит все вычисления в таблице.
- Чтобы сохранить уже выделенные значения, выберите ячейки для сохранения, скопируйте, а затем вставьте специальные значения только обратно в те же ячейки, эффективно заменив формулы этих ячеек теми, которые были в настоящее время вычисленными значениями.
- Вам может понадобиться фиктивная строка, запись в синем столбце, чтобы скорректировать распределения в будущем, поскольку все вычисления (хотя и не зависят друг от друга) кумулятивно основаны на синем столбце и текущих значениях максимальных распределений списка.
- Чтобы начать новую таблицу, начиная с того места, где остановился старый:
- Используйте фиктивную дату вклада для первой строки и вычислите сумму, используя последние, выделенные для записей в столбце.
- Возможно, было несколько частичных записей в нескольких строках, так как они удовлетворяли предыдущему столбцу. Суммируйте их все для последнего выделенного столбца, но только те записи, которые были внесены в последний столбец с момента получения предыдущего столбца.
- Если более одного столбца последней строки имеет значение, то только сумма последнего столбца была выделена. Если был частичный для самого последнего столбца и частичный для первого столбца, то самый последний столбец был удовлетворен, а первый столбец - последний выделенный столбец.
- Добавьте к сумме MAX для каждого предыдущего столбца независимо от того, когда, в какую строку он был введен, или было ли несколько записей для этих других столбцов. Просто МАКС из каждого предыдущего столбца. В первом столбце нет предыдущих столбцов.
- Эта сумма (последний столбец, внесенный и максимальный перед ним) является первым синим номером столбца, который должен быть указан с фиктивной датой.
- В коде есть защита от того, что величина перекрытия диапазона суммируется дважды, когда возникает граничное условие. Кажется, это было единственное, что нужно, но бета-использование может определить иное.
+ IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0,
- Чтобы убрать нули, используйте этот трюк с форматированием ячеек : выберите
Custom и введите this [=0]"";General
И последнее замечание: есть закодированная коррекция для формулы модуля, которая позволяет добавлять значения, превышающие диапазон модуля. (Это оборачивается.) Это означает, что если введенная сумма больше 11, списки все равно будут корректно обновляться:
+INT($A10/SUM($B$1:$D$1))*D$1
Редактировать - я понял две вещи после публикации оригинального решения:
- Формула может быть упрощена с помощью начального интервала, начинающегося с нуля.
- Именованные диапазоны могут быть относительными, и поэтому они могут "расти".
Соединение двух вместе для более чистой, более управляемой формулы:
=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
Что следует отметить:
Просто для полноты, здесь приведена нулевая версия без именованных диапазонов, которую можно переместить, обрезав A1:D2. И ненулевая версия с именованными диапазонами, просто в случае "A1" не может быть пустой. (Подсказка, объедините нулевую ячейку с левой и используйте объединенную ячейку для текста. Таким образом, нулевая ячейка будет равна 0.)
Нулевая версия без именованных диапазонов:
=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
Версия Named Range без нулевой ячейки:
=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
