2

У меня есть ежемесячный список подсчетов случаев (например, месяц 1 = 4, месяц 2 = 3,7, месяц 3 = 4,1, месяц 4 = 4,0 и т.д.). Я хочу распределить эти числа ежемесячно по 3 столбцам по их значению и последовательности. Столбец 1 будет первым из 4 доступных в списке, Столбец 2 будет следующим 4, Столбец 3 будет следующим 3, затем Столбец 1 снова будет следующим 4, и последовательность будет зациклена. Может кто-нибудь помочь мне с функциями Excel для тех трех столбцов, которые удовлетворят это?

Вот как будет выглядеть лист:

1 ответ1

2

Редактировать -

  • Уменьшенная исходная формула до простейшей формы (или это?) .
  • Добавлены именованные диапазоны и нулевой предварительный интервал (еще короче) .

Очень крутая проблема! Вот решение:

=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

Редактировать - я понял две вещи после публикации оригинального решения:

  1. Формула может быть упрощена с помощью начального интервала, начинающегося с нуля.
  2. Именованные диапазоны могут быть относительными, и поэтому они могут "расти".

Соединение двух вместе для более чистой, более управляемой формулы:

=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", на втором изображении) должна быть пустой или 0 (ноль).
    • Это нулевая тара для обоих интервалов первой позиции, так что начало можно получить по сумме всех предыдущих интервалов, даже если позиция является первым интервалом и нет предыдущих интервалов. Теперь есть предыдущий интервал с размером ноль. (Начальную позицию больше не нужно рассчитывать как Start = End - Distance.)
  • Эта версия формулы использует именованные диапазоны.

    • Чтобы переместить эту формулу, "Именованные диапазоны" должны быть отредактированы.
    • Именованные Диапазоны являются "относительно"; Это означает, что они относятся к ячейке, выбранной перед редактированием относительного столбца или строки именованного диапазона.
    • Используйте Диалог имени менеджера второго изображения в качестве ссылки. Ячейка "B2" выбрана на изображении, поэтому все относительные значения в изображении диспетчера имен относятся к ячейке "B2".
    • При создании дополнений или изменений в Диспетчере имен:

      1. Выберите первую ячейку данных (верхняя левая ячейка формулы; "B2" на изображении).
      2. Откройте имя менеджера.
      3. Создайте те же отношения столбцов со значениями Max списка и
        Связи строк со значениями Blue Input.
      4. Чтобы определить фактические ссылки, отличные от знака доллара, используйте изображение, чтобы определить количество ячеек вверх и влево от "B2" до значения диапазона имен.
      5. Примените счетчик вверх и влево к текущей выбранной ячейке, чтобы определить фактическую запись, чтобы сделать для именованного диапазона новый элемент или редактировать.
    • Еще один положительный момент - «формула ... с диапазоном ... дополнительным ..." исчезает.

Просто для полноты, здесь приведена нулевая версия без именованных диапазонов, которую можно переместить, обрезав 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

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .