Я уверен, что есть способы сделать это очень элегантно, но вот подход, который требует наименьшего количества клеток мозга. :-) У вас уже есть решение, но проблема в том, что для его настройки и изменения в других случаях требуется много ручной работы. Я сосредоточился на шаблонном подходе, чтобы реализовать то, что вы сделали вручную. Его легко и быстро создать, и его можно использовать в других случаях без необходимости его изменения. При этом сохраняется форма, которую легко настроить и устранить, если ваши потребности изменились, и объяснить другим.
Вы заметите несколько отличий от своего рабочего листа.
Есть несколько скрытых столбцов. Я добавил несколько вспомогательных столбцов, чтобы помочь в вычислениях.
Я изменил столбцы оценок и баллов, чтобы согласовать последовательности других столбцов. Это позволяет копировать формулы для построения шаблона без необходимости их изменения.
Я вставил пустую строку под заголовками. Некоторые из формул относятся к предыдущей строке, поэтому для формул в первой строке данных требуется пустая ячейка выше, чтобы иметь одну простую формулу, которую можно использовать для всего столбца.
В столбцах группы все имеет значение, потому что все используется в вычислениях (включая нули). Если вы предпочитаете пустые ячейки, а не нули, вы можете отформатировать ячейки так, чтобы нули не отображались (но они все равно будут обрабатываться как нули).
Я переместил средние значения в строку внизу, чтобы их можно было рассчитать по простой формуле (на самом деле их там не нужно размещать, но это более интуитивно понятно). Я оставил произвольное количество строк для дополнительных курсов. Сделайте столько, сколько нужно для максимального количества курсов, которые вы можете встретить.
Здесь все столбцы не видны:
Используя одни и те же формулы для каждой строки, это легко сделать. Просто введите формулы в первой строке, а затем скопируйте строку по мере необходимости. Существует также несколько различных формул, поэтому их копирование для разных групп будет простым копированием и вставкой. Не беспокойтесь, если вы временно получите некоторые значения ошибок при построении первой строки; формулы могут относиться к ячейкам, в которые вы еще не ввели формулу.
Формулы
1/4 Всего кредитов
Я записал это в C21, просто чтобы оставить произвольное количество дополнительных строк, чтобы можно было добавить больше курсов. Он рассчитывается с использованием всего диапазона, потому что пустые ячейки не влияют на результат, и это позволяет вам иметь любое количество курсов. Формула для ряда строк 3-20 потенциальных курсов: =SUM(C3:C20)/4
Накопительные кредиты
Чтобы выяснить, где кредиты для каждой группы начинаются и заканчиваются, существуют вспомогательные столбцы, которые рассчитывают совокупные кредиты (или оставшиеся неназначенные кредиты). Это столбцы D, H и L (4-я группа содержит все, что осталось после 3-й группы, поэтому она сохраняет некоторые вычисления). Формулы, которые можно перетаскивать или копировать по мере необходимости (заполнить потенциальные строки):
D3: =SUM(C$3:C3)
H3: =SUM(G$3:G3)
L3: =SUM(K$3:K3)
Вы можете ввести формулу в D3, а затем скопировать ее в H3 и L3.
Неназначенные кредиты
Эти вспомогательные столбцы рассчитывают количество кредитов, оставшихся после каждой группы. Это столбцы G и K. Это также, как кредиты для 4-й группы рассчитываются в столбце O. Формулы:
G3: =C3-E3
K3: =G3-I3
O3: =K3-M3
Опять же, вы можете ввести формулу в G3, а затем скопировать ее в K3 и O3.
Кредиты для групп
Кредиты для групп 1-3 рассчитываются следующим образом. Если (оставшиеся) совокупные кредиты за предыдущий курс составляли более 1/4 от общей суммы, это означает, что текущий курс должен быть в следующей группе, поэтому присваиваются нулевые кредиты. В противном случае присваивается минимум действительного кредита за курс или суммы, необходимой для достижения итоговой суммы в 1/4. Формулы:
E3: =IF(D2>$C$21,0,MIN(C3,$C$21-D2))
I3: =IF(H2>$C$21,0,MIN(G3,$C$21-H2))
M3: =IF(L2>$C$21,0,MIN(K3,$C$21-L2))
Вы можете ввести формулу E3, а затем скопировать ее в I3 и M3.
Веса
Веса сравнивают присвоенный группе кредит курса с итоговой суммой 1/4. Эти формулы:
F3: =E3/$C$21
J3: =I3/$C$21
N3: =M3/$C$21
P3: =O3/$C$21
Вы можете ввести формулу F3, а затем скопировать ее в J3, N3 и P3.
Средняя группа
Средние значения по группам - это ОБЩАЯ оценка класса и соответствующие весовые столбцы. Эти формулы:
E21: =SUMPRODUCT($B3:$B20,F3:F20)
I21: =SUMPRODUCT($B3:$B20,J3:J20)
M21: =SUMPRODUCT($B3:$B20,N3:N20)
O21: =SUMPRODUCT($B3:$B20,P3:P20)
Вы можете ввести формулу E21, а затем скопировать ее в I21, M21 и O21.
После того, как вы настроили это в общей форме, вы можете использовать его как шаблон без необходимости повторного ввода формул. Просто скопируйте шаблон на другой лист и введите новые данные курса.
Делая это красивее
Как упоминалось ранее, вы можете скрыть вспомогательные столбцы и подавить нулевые значения. Вы также можете "скрыть" значения в строках, которые были предварительно заполнены формулами, но не имеют информации о курсе. Для этого нужно обернуть формулы в столбцах "Группа" и "Вес" тестом IF.
Предполагая, что при добавлении строк для дополнительных курсов столбец «Кредиты» будет последним введенным значением, мы можем проверить, является ли оно пустым, и показать нулевой («»), если так, в вычисляемых столбцах вместо приведенных выше формул. , Оберните формулы следующим образом: `= IF (ISBLANK ($ C3)," ", Formula_above). Так, например, столбцы группы 1 становятся:
E3: =IF(ISBLANK($C3),"",IF(D2>$C$20,0,MIN(C3,$C$20-D2)))
F3: =IF(ISBLANK($C3),"",E3/$C$20)
На снимке экрана ниже я предварительно заполнил строки 3-20 формулами и подавил их, как указано выше (скрытые вспомогательные столбцы не нужно подавлять).
Обратите внимание, что я добавил курс 10 (только название курса, оценку и кредит). Рассчитанные значения отображаются для строки, группы корректируются для дополнительных данных, а средние значения отражают дополнительный курс. Я сделал пример в LibreOffice Calc, который не имеет функции подавления нулевых значений (AFAIK), поэтому я не смог проиллюстрировать полностью разобранный пример.
