Excel — расчет нескольких ячеек

Я учитель и пытаюсь создать оценочный лист, проблема заключается в том, что задания оцениваются с использованием разных схем (например, % [1–100], уровень [1–4], буквенная оценка [F–A]). У меня проблема в том, что я хочу отчитываться только по одной схеме маркировки, и мне нужно преобразовать или рассчитать другие схемы маркировки на основе схемы, по которой она отмечена.

Я сделал рабочий лист excel, который содержит имена учеников и задания. Затем на листе есть 3 других столбца, один из которых представляет каждую схему «Буква», «Уровень», столбец «Процент» для каждой схемы. Теперь в любой момент времени в одном из столбцов «схемы» будет значение — мне нужно, чтобы excel автоматически или с помощью макроса вычислял соответствующее значение в других ячейках.

Например, на одном листе есть три задания, каждое из которых помечено разной схемой.

Assignment 1 - %
Assignment 2 - Level 
Assignment 3 - Letter Grade

Как я могу заставить excel проверить, какое значение отметки существует, а затем заполнить/вычислить два других соответствующих значения.

Майк получил 80% (%) по заданию 1, но я хочу, чтобы Excel заполнил столбец уровня и столбец букв соответствующими значениями одновременно. Уровень 3 в задании 2 excel для расчета соответствующего процента и буквы и в то же время для Буква А в Задании 3 следует рассчитать соответствующий процент и уровень.

Не знаю, возможно ли это или имеет ли смысл, но я попробовал ВПР и не преуспел, потому что мне приходится делать все это вручную - я стремлюсь к автоматизированному процессу.

Извините, если это неясно или запутанно ... но я безуспешно занимался этим два дня и даже не уверен, что то, что я делаю, возможно.


excel formula vba calculation
person Syngh    schedule 11.01.2017    source источник

Ответы (2)


arrow_upward
0
arrow_downward

конечно, сначала следует оценить перевод одной рейтинговой системы в другую, а затем предположить, что эти алгоритмы переключаются между типами оценок.

один из возможных подходов может быть следующим:

Option Explicit

Sub main()
    Dim grades As Variant, levels As Variant
    Dim cell As Range
    Dim index As Double

    grades = Array("F", "E", "D", "C", "B", "A")
    levels = Array(1, 2, 3, 4)

    For Each cell In Intersect(Range("A:C"), ActiveSheet.UsedRange).Offset(1).SpecialCells(xlCellTypeConstants)
        Select Case cell.Column
            Case 1 '%
                index = cell.value / 100
                cell.Offset(, 1).value = GetRate(index, levels)
                cell.Offset(, 2).value = GetRate(index, grades)
            Case 2 ' Level
                index = InStr(Join(levels, ""), cell.value) / Len(Join(levels, ""))
                cell.Offset(, -1).value = index * 100
                cell.Offset(, 1).value = GetRate(index, grades)
            Case 3
                index = InStr(Join(grades, ""), cell.value) / Len(Join(grades, ""))
                cell.Offset(, -2).value = index * 100
                cell.Offset(, -1).value = GetRate(index, levels)
        End Select
    Next
End Sub

Function GetRate(index As Double, rates As Variant)
    GetRate = rates(WorksheetFunction.Max(Round(index * (UBound(rates) - LBound(rates) + 1), 0) - 1, 0))
End Function

из-за простоты алгоритма перевода он не обеспечивает биективной связи между системами оценивания с разными интервалами, но может приблизить вас к вашей цели.

person user3598756    schedule 11.01.2017
comment
здесь вы можете черпать вдохновение для алгоритмов перевода - person user3598756; 12.01.2017
comment
Спасибо за направление, попробую посмотреть, что получится. - person Syngh; 13.01.2017

arrow_upward
0
arrow_downward

Создайте подобную таблицу (вы можете добавить больше строк, если вам нужно определить +/- баллы, такие как «B+» и т. д.). Ключевым моментом здесь является сортировка таблицы по возрастанию в %, где каждая строка является минимальным баллом, необходимым для соответствующего уровня/буквенной оценки.

введите здесь описание изображения

Затем вы можете использовать формулу VLOOKUP для этой таблицы, например, чтобы получить уровень:

=VLOOKUP(F2,$A$1:$C$7,2,TRUE)

И так, чтобы получить Letter Grade:

=VLOOKUP(F2,$A$1:$C$7,3,TRUE)

введите здесь описание изображения

При этом используется параметр True в функции ВПР, который возвращает приблизительное совпадение (и предполагает, что данные отсортированы по возрастанию). Таким образом, когда вы вводите такое значение, как "81%", возвращается ближайшая строка, которая не превышает 81 %, поэтому будут возвращены данные из строки 5. Аналогичным образом, 12 % вернет данные из ряд 2, 75% от ряда 4 и т. д.

И ваши результаты:

введите здесь описание изображения

Возможно, вам придется использовать две таблицы, если вы не можете 1:1 сопоставить уровень/буквенную оценку со строкой процентов, но идея будет той же.

person David Zemens    schedule 11.01.2017
comment
Спасибо за ответ, продолжаю искать ответы в гугле и пробовать разные вещи. Я знаю, что есть способ сделать это в Excel, и я полон решимости сделать это. В конце концов, я думаю, что это может пойти по пути базы данных доступа, но я хочу, чтобы все было просто, поскольку мои навыки кодирования не самые лучшие. - person Syngh; 13.01.2017
comment
@Syngh, описанный выше подход не работает для вас? Если нет, не могли бы вы уточнить (пересмотреть исходный вопрос с новой/актуальной информацией)? Это должно работать и не требует какого-либо программирования VBA... - person David Zemens; 13.01.2017
comment
спасибо за код, Дэвид, моя первоначальная проблема, которую я пытаюсь решить, заключается в следующем (и еще раз извините, если я не очень хорошо ее сформулировал). Мне нужно, чтобы excel автоматически просматривал ячейки в 3 разных столбцах - как только он увидит «метку» в одной из этих ячеек, мне нужно, чтобы он автоматически заполнил соответствующее значение в двух других ячейках. VLookup работает, но мне нужно вставить формулу в правильные строки, я надеюсь сделать это на более высоком уровне, где мне не нужен VLookup как fx в ячейке, а просто запустить скрипт для выполнения полученные результаты. - person Syngh; 24.01.2017
comment
вы можете рассмотреть возможность пересмотра своего вопроса со следующим: лучшее объяснение того, что вы пытаетесь сделать, снимок экрана или предпочтительно вставленный текстовый пример ваших входов, а также то же самое для ожидаемых выходов/ результаты. в большинстве случаев также очень полезно, если вы продемонстрируете, что уже пробовали, объяснив их проблемы или недостатки (это поможет другим избежать тех же ошибок, помогая вам). - person David Zemens; 24.01.2017