Я уверен, что есть способы сделать это очень элегантно, но вот подход, который требует наименьшего количества клеток мозга. :- ) У вас уже есть решение, но проблема в том, что для его настройки и изменения в других случаях требуется много ручной работы. Я сосредоточился на шаблонном подходе, чтобы реализовать то, что вы сделали вручную. Это легко и быстро создать, и может быть повторно использован для других случаев без необходимости изменять его. Это сохраняет форму которая проста отрегулировать и устранить неисправность если ваш необходимо меняться, и объяснять другим.
Вы заметите несколько отличий от своего собственного листа.
есть некоторые скрытые столбцы. Я добавил несколько вспомогательных столбцов для помощи в вычислениях.
я поменял столбцы grade и credits, чтобы сделать другие последовательности столбцов согласованными. Это позволяет копировать формулы для построения шаблона без изменения их.
я вставил пустую строку под заголовками. Некоторые формулы ссылаются на предыдущую строку, поэтому формулам в первой строке данных требуется пустая ячейка выше, чтобы иметь одну простую формулу, которую можно использовать для всего столбца.
внутри групповых столбцов все имеет значение, потому что все используется в вычислениях (включая нули). Если вы предпочитаете пустые ячейки, а не нули, вы можете отформатировать ячейки так, чтобы нули не отображаются (но они все равно будут рассматриваться как нули).
я переместил средние в строку внизу, чтобы их можно было вычислить с помощью простой формулы (на самом деле, нет необходимости размещать их там, но более интуитивным является ее просмотр). Я оставил произвольное количество строк для дополнительных курсов. Сделайте это столько, сколько необходимо для максимального количества курсов, с которыми вы можете столкнуться.
здесь все колонны скрытых:
используя одни и те же формулы для каждой строки, настройка этого проста. Просто введите формулы в первой строке, а затем скопируйте строку по мере необходимости. Кроме того, существует несколько различных формул, поэтому их копирование для разных групп будет простым копированием и вставкой. Не волнуйтесь, если вы получаете некоторые значения ошибок временно, как вы строите первую строку; формулы могут ссылаться на ячейки, где вы еще не ввели формулу.
Формулы
1/4 Общую Сумму
Я застрял в C21, просто чтобы оставить произвольное количество дополнительных строк, чтобы добавить больше курсов. Он рассчитывается с использованием всего диапазона, так как пустые ячейки не влияют на результат, и это позволяет иметь любое количество курсов. Формула для ряда в диапазоне 3-20 потенциальных курсов:=SUM(C3:C20)/4
совокупный Кредиты
чтобы выяснить, где начинаются и заканчиваются кредиты для каждой группы, существуют вспомогательные столбцы для расчета совокупных кредитов (или нераспределенных кредитов). Это столбцы D, H и L (4-я группа содержит все, что осталось после 3-й группы, поэтому она сохраняет некоторые вычисления). Формулы, которые при необходимости можно перетащить или скопировать (заполнить потенциальные строки):
D3: =SUM(C:C3)
H3: =SUM(G:G3)
L3: =SUM(K: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,0,MIN(C3,$C-D2))
I3: =IF(H2>$C,0,MIN(G3,$C-H2))
M3: =IF(L2>$C,0,MIN(K3,$C-L2))
вы можете ввести формулу E3, а затем скопировать ее в I3 и M3.
весом
веса сравнить курс назначенных группе на 1/4 общего объема. Эти формулы таковы:
F3: =E3/$C
J3: =I3/$C
N3: =M3/$C
P3: =O3/$C
вы можете ввести формулу F3, а затем скопировать ее в J3, N3 и P3.
Средние Группы
группа средних функцию sumproduct ранга и соответствующие столбцы вес. Эти формулы таковы:
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.
после этого в родовое форма, вы можете использовать его как шаблон, без необходимости повторного ввода формул. Просто скопируйте шаблон на другой лист и введите новые данные курсы.
сделать его красивее
Как упоминалось ранее, вы можете скрыть вспомогательные столбцы и подавить нулевые значения. Можно также" скрыть " значения в строках, предварительно заполненных формулами, но не содержащих сведений о курсе. Это делается путем обертывания формул в Столбцах Group и Weight с помощью теста IF.
предполагая, что при добавлении строк для дополнительных курсов столбец Credits будет последним введенным значением, мы можем проверить, пуст ли он, и показать null ( "" ), если это так, в вычисляемых столбцах вместо приведенных выше формул. Оберните формулы следующим образом: '=IF (ISBLANK ($C3),"",formula_above). Так, например, столбцы группы 1 становятся:
E3: =IF(ISBLANK($C3),"",IF(D2>$C,0,MIN(C3,$C-D2)))
F3: =IF(ISBLANK($C3),"",E3/$C)
на скриншоте ниже, я предварительно заполненные строки 3-20 с формулами и подавить их, как показано выше (скрытый вспомогательные столбцы не нужно подавлять).
обратите внимание, что я добавил курс 10 (только название курса, класс и кредит). Вычисленные значения отображаются для строки, группы корректируются для дополнительных данных, а средние отражают дополнительный курс. Я сделал пример в LibreOffice Calc, который не имеет функции подавления нулевых значений (AFAIK), поэтому я не смог проиллюстрировать пример полностью gussied вверх.
Komp
3261
