Как нарисовать линейный график: дата против оценок?

Содержание

Вопрос или проблема
Ответ или решение
Шаги для создания линейного графика
Заключение

Вопрос или проблема

У меня есть процесс оценки, который выдает буквы на основе выполнения (A – лучший, E – худший). Я периодически оцениваю процесс и представляю результаты в виде буквы.

Теперь я хочу отслеживать прогресс во времени, используя буквенные оценки. В основном, я хочу получить линейный график, где по оси X будет дата, а по оси Y – буквенная оценка.

Когда я вставляю линейный график и выбираю данные в следующем формате:

ДАТА/МОДУЛЬ    01.06.2024    01.07.2024    01.08.2024    01.08.2024
Модуль 1           E             E             C             C
Модуль 2           D             D             B             A
Модуль 3           C             C             A             A

Я хочу видеть прогресс, достигнутый в каждом модуле в каждой точке измерения (дате)

Я пробовал способ, который использую для создания линейных графиков, когда буквы заменяются целыми числами, это работает нормально. Но как мне изменить это так, чтобы оно работало с буквенными оценками?

В графике Excel на оси значений (Y) должны быть числа. Ничего с этим не поделаешь.
Единственное, что можно сделать, по-моему, это использовать специальное форматирование, чтобы числа выглядели как буквы. Вы можете написать макрос на событие, который, после ввода буквы A..E, преобразует ее в число в диапазоне 4..0, но визуально оставит букву. Благодаря этому трюку Excel будет “видеть” числа, а человек – буквы, и график можно будет построить.

Код макроса должен быть размещен в модуле листа.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, j As Long, t As Range
    Application.EnableEvents = False
    For i = 1 To Target.Rows.Count
        For j = 1 To Target.Columns.Count
            Set t = Target(i, j)
            If t Like "[A-E]" Then
                t.NumberFormat = """" & t & """"
                t.Value = Asc("E") - Asc(t)
            ElseIf t.NumberFormat Like """[A-E]""" Then
                t.NumberFormat = "General"
            End If
    Next j, i
    Application.EnableEvents = True
End Sub

Ответ или решение

Создание линейного графика для отслеживания прогресса по оценкам (буквенные оценки) требует некоторого преобразования данных, так как Excel не может непосредственно отобразить текстовые значения на оси Y. Вам нужно будет использовать числовые эквиваленты буквенных оценок. В этом ответе я опишу, как это сделать, а также предоставлю макрос для автоматизации преобразования оценок в числа.

Шаги для создания линейного графика

Подготовка данных: Убедитесь, что ваши данные организованы в Excel так, как вы указали:

Модуль      01.06.2024    01.07.2024    01.08.2024
Модуль 1         E             E             C
Модуль 2         D             D             B
Модуль 3         C             C             A

Преобразование оценок в числа:
Чтобы Excel мог строить график, вам необходимо преобразовать буквенные оценки в числовые:
- A = 4
- B = 3
- C = 2
- D = 1
- E = 0
Мы будем использовать макрос, чтобы автоматически обрабатывать эти преобразования.

Создание макроса:
В Excel откройте редактор VBA (нажмите Alt + F11), затем выполните следующие действия:

Найдите свой рабочий лист в "Project Explorer" (обычно находится слева).
Дважды щелкните по нужному рабочему листу, чтобы открыть его модуль.
Вставьте следующий код в открывшееся окно:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i As Long, j As Long, t As Range
   Application.EnableEvents = False
   For i = 1 To Target.Rows.Count
       For j = 1 To Target.Columns.Count
           Set t = Target(i, j)
           If t Like "[A-E]" Then
               t.NumberFormat = """" & t & """"
               t.Value = Asc("E") - Asc(t)
           ElseIf t.NumberFormat Like """[A-E]""" Then
               t.NumberFormat = "General"
           End If
       Next j, i
   Next i
   Application.EnableEvents = True
End Sub

Сохранение и выход из редактора: После вставки кода закройте редактор VBA и вернитесь в Excel.
Ввод данных: Теперь, когда вы вводите буквы A, B, C, D или E в диапазон ваших оценок, макрос автоматически преобразует их в числа (A станет 4, B станет 3 и так далее). Значения будут по-прежнему отображаться как буквы, но внутреннее представление будет числовым.
Создание графика:
- Выделите ваши данные (включая даты и модули).
- Перейдите на вкладку "Вставка" и выберите "График" -> "Линейчатый график".
- Убедитесь, что ось X отображает даты, а ось Y – числовые значения, соответствующие оценкам.
Форматирование графика: Вы можете настроить визуальный вид графика, добавив названия, сетки или изменяя цвета.

Заключение

Теперь, используя данную методику, вы сможете отслеживать прогресс по оценкам для каждого модуля в течение времени. Благодаря макросу, преобразование оценок будет осуществляться автоматически, и визуально на графике будут отображаться буквы.