Поиск нужного диапазона на основе пользовательского ввода
По глупым причинам нам нужно преобразовать нашу систему оценок от 1 до 4 в оценки от 1 до 100.
Мой главный вопрос здесь: есть ли лучший/быстрый/более эффективный подход, чем то, что я делаю? B3 в таблице ниже показывает мой подход.
Моя цель состоит в том, чтобы учитель мог ввести, какую часть стандарта он изучил и чему учащийся соответствует, и чтобы лист автоматически смотрел на правильную таблицу преобразования, чтобы получить оценку.
Итак, если учитель научил стандарту до 4, а ребенок получил 4, это 100. Если учитель научил 2, а ребенок получил 2, это все еще 100. Тогда как, если учитель научил 4, а ребенок получил 2, это все равно 100. только 78.
В B3 вы можете увидеть направление, в котором я иду. Длинный, длинный набор ifs с vlookups. Как видите, мне нужно сделать это для каждой возможности для каждого задания. Это похоже на то, что можно сказать компьютеру, поскольку он повторяет снова и снова с небольшими изменениями.
Спасибо!
Ответы 2
Я сделал ответ в скрипте Google Apps, так как его проще и проще реализовать.
Вот пример данных.
Вот пример вывода.
Я помещаю конвертированную оценку непосредственно под баллом за каждое задание. (Это можно удалить, если хотите)
Столбец B содержит среднее значение конвертированных оценок.
Среднее значение – это сумма конвертированных оценок по количеству ячеек в первой строке, в значении которой указано "Задание". (В этом примере их 10)
ПРИМЕЧАНИЕ:
Я добавил некоторые значения в другие столбцы стандартного обучения, так как это необходимо для расчета конверсии.
Вот код с пошаговым описанием:
function calculateGrade() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Gets data from the sheet named 'IN PROGRESS:Standard Taught'
var data = spreadsheet.getSheetByName('IN PROGRESS:Standard Taught');
var dataValues = data.getDataRange().getValues();
// Gets conversion data from the sheet named 'Conversion Chart'
var conversion = spreadsheet.getSheetByName('Conversion Chart');
var conversionValues = conversion.getRange('B3:I13').getValues();
// Get second row as our standard taught values
var standardTaught = dataValues[1];
// Number of first row cells containing 'Assignment'
var numAssignments = dataValues[0].filter(function(item){
if (item.match(/Assignment/)){
return item;
}
}).length;
var totalScore = 0;
// Process scores to grade
dataValues.forEach(function(row, rowIndex){
// Skip first two rows and succeeding rows that their first columns are blank
if (rowIndex > 1 && row[0]){
row.forEach(function(score, colIndex){
// Skip first two columns since they are not scores
if (colIndex > 1){
// Find what conversion table should be the scores are based (4, 3 or 2)
conversionValues[0].forEach(function(item, index){
// If standard taught for the column matches the first row of original grade column, that's the conversion table we are using
if (item == standardTaught[colIndex]){
// Find in the conversion table we are using the grade value of the score
for(var i = 0; i < conversionValues.length; i++){
for(var j = index; j < index + 1; j++){
if (conversionValues[i][j] === score){
var convertedGrade = conversionValues[i][j + 1];
// Set directly below cell of the score to the converted grade value
// You can remove the row below if you don't want to see the converted grade below the score
data.getRange(rowIndex + 2, colIndex + 1).setValue(convertedGrade);
// Skip cells that are not valid numbers/scores
if (!isNaN(parseFloat(convertedGrade))){
// Add all converted grades for the average
totalScore += parseFloat(convertedGrade);
}
}
}
}
}
});
}
});
// Set row's average score based on the total of converted grades over number of assignments
data.getRange(rowIndex + 1, 2).setValue(totalScore / numAssignments);
// Reset counter for total score for the next rows
totalScore = 0;
}
});
}
скрипты не нужны.
удалите все в столбце B и используйте в B3:
=ARRAYFORMULA(IF(A3:A = "",, QUERY(TRANSPOSE(QUERY(TRANSPOSE(
IF(A3:A = "", 0,
IF(C2:AA2=2, IFNA(VLOOKUP(C3:AA, 'Conversion Chart'!H3:I, 2, 0)),
IF(C2:AA2=3, IFNA(VLOOKUP(C3:AA, 'Conversion Chart'!E3:F, 2, 0)),
IFNA(VLOOKUP(C3:AA, 'Conversion Chart'!B3:C, 2, 0)))))),
"select "&TEXTJOIN(",", 1,
"avg(Col"&ROW(A3:A)-ROW(A3)+1&")")&"")),
"select Col2")))