Как преобразовать буквенную оценку в число в Excel?
. Урок 2 .
Выставляем оценки, или Формулы
Запустите электронную таблицу и сохраните документ под именем «Statement.xls». Заполните ячейки электронной таблицы:
- В А1 занесите текст «Сводная ведомость итоговых оценок учащихся 10 класса».
- В ячейки А3:J3 запишите заголовки столбцов таблицы (не озадачивайтесь пока тем, что текст не помещается в ячейки, мы это поправим):
- Фамилия, имя
- Русский язык
- Литература
- Алгебра
- Геометрия
- Физика
- Информатика
- Средний балл
- Школьные результаты
2. Настройка одинаковой ширины столбцов
- Уменьшите ширину столбца А и увеличьте ширину столбца В.
- Выделите столбцы С:J (чтобы выделить весь столбец, нужно щёлкнуть мышью по его заголовку, а чтобы выделить несколько столбцов подряд, нажмите на заголовке первого и протяните мышью до заголовка последнего).
- Выполните: Формат → Столбец → Ширина… .
- В открывшемся окне установите ширину столбца 4 (ширина столбца в Excel измеряется в символах) и нажмите OK:
3. Настройка выравнивания и ориентации текста
( Справка: Выравнивание текста и его правильная ориентация) Отформатируйте текст в ячейке так, чтобы его можно было читать:
- Выделите диапазон А3:J3.
- Выполните Формат → Ячейки… .
- В окне Формат ячеек перейдите на вкладку Выравнивание.
- В списках: по горизонтали и по вертикали выберите по центру.
- Установите флажок переносить по словам и нажмите OK:
Теперь весь текст виден, но он плохо расположен в ячейках. Это можно исправить, отрегулировав направление текста по вертикали:
- Выделите диапазон С3:J3.
- Откройте окно Формат ячеек.
- На вкладке Выравнивание в поле Ориентация установите значение 90 градусов:
- Нажмите OK и посмотрите, что получилось:
- Заполните столбцы А и В номерами и фамилиями учащихся вашего класса, а в ячейки столбцов С:Н запишите оценки по указанным предметам.
4. Формула
Для ячейки I4 вычислим средний балл по следующей формуле:
- Чтобы подсчитать средний балл, нужно сложить все оценки в ряду и разделить полученную сумму на количество оценок. Эту формулу можно записать так: =(C4+D4+E4+F4+G4+H4)/6 но если количество предметов будет достаточно велико, эту формулу будет слишком долго набирать, да и ошибиться легко.
- Для подсчёта суммы нескольких чисел в Excel есть функция суммирования, которой мы уже пользовались: =сумм(C4:H4)/6
- Запишите эту формулу в ячейку I4 — при этом удобно воспользоваться кнопкой Автосумма:
- выделите ячейку I4;
- нажмите кнопку Автосумма:
- убедитесь, что Excel правильно «угадал» диапазон;
- щёлкните мышью в строке формул, чтобы там замигал курсор, и мы смогли продолжить формулу;
- допишите формулу, нажмите Enter:
5. Добавление столбцов
- Выделите столбцы G:H.
- Выполните Вставка → Столбцы — в таблицу левее выделенных добавилось ровно столько столбцов, сколько было выделено.
- Запишите предметы «биология» и «физкультура» и заполните оценки:
6. Использование констант
Обратите внимание на средний балл — все стали не просто отличниками, оценки зашкаливают. Почему так получилось? Конечно, вы уже поняли, что произошло это из-за того, что мы добавили два предмета, и теперь их не 6, а 8. А делим сумму оценок мы по-прежнему на 6. Можно исправить формулу в столбце средний балл, заменив 6 на 8. А затем ещё и ещё раз, если вдруг захочется добавить или убрать предметы… А если нашей таблицей будет пользоваться тот, кто вообще не умеет исправлять формулы.
Почему бы нам не сделать все по-другому?! В формуле следует избегать использования чисел (констант). Каждая константа записывается в ячейку, на которую ссылается в формуле. Именно так мы и поступим.
Предположим, что в нашем списке, безусловно, 13 студентов. Затем список заканчивается на строке 16. Спуститесь на одну строку вниз и окажетесь на строке 18. Если у вас больше или меньше учеников, работайте вниз по строке:
- Запишите в ячейку В18 слово «предметов».
- В ячейку С18 запишите число «8»:
- Исправьте формулу в ячейке К4:
- Выберите эту ячейку;
- Нажмите на строку формул;
- Удалите число «6»;
- Нажмите на ячейку с количеством объектов;
- У вас должна получиться следующая формула: =SUM(C4:J4)/C18
- Нажмите клавишу Enter и растяните формулу вниз:
7. Абсолютная адресация
(справка: Абсолютная и относительная адресация) Получился странный результат: вместо средних оценок какой-то «заборчик». Он появляется тогда, когда ячейка слишком узка для отображения результата. Увеличьте ширину столбца К:
И теперь ничуть не лучше — совсем непонятная запись. Таким нехитрым способом Excel даёт нам понять, что в формуле содержится ошибка, в данном случае деление на 0 (#ДЕЛение/0!). Откуда эта ошибка появилась? Поищем:
- Выделите ячейку К4.
- Щёлкните мышкой в строке формул — адреса в ней стали цветными и рамками такого же цвета обведены соответствующие им ячейки:
Таким способом можно быстро увидеть, какие ячейки участвуют в формуле.
- А теперь выделите любую ячейку с ошибкой, например, К6, и посмотрите, на какие ячейки ссылается эта формула:
Вместо C18 в формуле присутствует C20. Это неудачно, потому что мы уже заметили, что растяжение формулы вниз увеличивает все числовые направления.
Что делать, если мы не хотим, чтобы адрес был изменен? Затем вам нужно зафиксировать его, то есть заморозить (в Excel это называется абсолютным адресом):
- Выделите ячейку К4.
- В строке формул перед числом «18» поставьте знак «$» (этот знак и замораживает адрес, который следует за ним): =СУММ(C4:J4)/C$18
- Растяните формулу вниз — теперь всё в порядке?
8. Пример формулы с относительной адресацией
Подумайте о другой колонке — школьных оценках. Предположим, в нашей школе десятибалльная система оценок. Как же нам перевести в нее пятибалльные оценки? Да, умножить их на два. Но поскольку мы соблюдаем правило хороших манер №1 (не вводите числа в формулу), мы создадим еще одну ячейку для константы:
- В ячейку В20 запишите «переводной к-т», а в С20 — число «2».
- В ячейку L4 запишите формулу… а, впрочем, вы это сможете сделать самостоятельно:
- Не забудьте сохранить документ (если помните, в начале урока вы дали ему имя «Ведомость.xls»).
9. Смешанная адресация
При работе с электронными таблицами умение правильно расставить символы $ часто помогает значительно сократить работу. Рассмотрим это на примере простенькой таблицы — таблицы умножения:
- Запишите название таблицы, заполните строчку и столбец с множителями:
- В ячейку B3 запишите формулу (=A3*B2)
- Растяните её вправо и сразу, не снимая выделения, вниз:
- Наверное, не стоит говорить, какую ошибку мы допустили, — достаточно заглянуть за «заборчик» или «раскрасить» одну из формул:
- Посему возвращаемся в ячейку с начальной формулой (B3) и анализируем формулу =A3*B2:
- при растягивании этой формулы вправо должна остаться неизменной ссылка на столбец A: =$A3*B2
- при растягивании формулы вниз необходимо зафиксировать ссылку на строку 2: =$A3*B$2
Обратите внимание — именно столбец А и строка 2 содержат константы (на рисунке они выделены).
Коротко о главном
Если в формуле участвует константа, в большинстве случаев следует поместить её в отдельную ячейку, а в формулу вставить адрес этой ячейки — в этом случае константу будет легко изменить, не затрагивая формул.
Чтобы при «растягивании» формулы адрес константы не изменялся, его надо зафиксировать, поставив перед ним символ $. При этом фиксируется не весь адрес, а только та его составляющая, перед которой стоит знак $. Например, при растягивании формулы вниз или вверх фиксируется числовая часть адреса G$6, а при растягивании вправо или влево — буквенная $G6.
Вытягивание буквенной оценки из массива таблиц Excel
Интересно, существует ли более простой способ извлечения буквы из массива, чем текущий метод, который я использую с кучей вложенных операторов If?
Моя идея заключается в том, что если средний балл (G1-G5) находится между B1:C12, то возвращается A1-A12. На рисунке 1 показаны исходные данные, на рисунке 2 — желаемый результат.
1 ответ
- Извлечение данных из нескольких файлов Excel в единый файл Excel для Excel 2007
Есть ли способ объединить данные из разных файлов Excel в один файл Excel? Например, извлеките данные из файлов excel (1) и excel (2) в новый файл excel(1_2).
Начинающий java-программист, не знакомый с массивами, работает над следующей задачей: Напишите программу, которая показывает распределение оценок по тесту. Баллы вводятся по одному, и цикл прерывается, когда вводится 0 (ноль) баллов. Результатом будет буква * для каждого балла.
Вам не нужно использовать столбец «выше», просто используйте столбец «меньшее значение» и столбец буквенного класса. Отсортируйте их в порядке возрастания так, чтобы они были A1:B12.
Таким образом, если ваша оценка находится в ячейке D3, вы можете изменить ее на буквенную оценку в ячейке E3.
Для первого числа, большего или равного первому столбцу, возвращается значение во втором столбце.
Похожие вопросы:
Есть несколько таблиц с индивидуальными оценками для каждого пользователя. Я хотел бы создать триггер, который суммирует все эти оценки для каждого пользователя и помещает их.
Как быстрее всего сделать следующее: У меня есть файл pdf с несколькими таблицами. Я хочу фильтровать таблицы по определенным заголовкам. Эти отфильтрованные таблицы имеют одно и то же.
Используя to_xls, я попытался экспортировать данные, но не могу сделать это правильно из двух таблиц. Рассмотрим следующий пример; у меня есть таблица пользователей и таблица сведений о пользователях.
Как собрать данные из разных файлов Excel в один? Например, извлечение данных из файлов excel (1) и excel (2) в один новый файл excel(1_2)
Начинающий java-программист, новичок в работе с массивами, работает над следующим заданием: Напишите программу, которая строит график распределения оценок по тесту. Оценки вводятся по одной за раз, и.
При выполнении задания я хочу вернуть оценку за письменную работу, основанную на среднем балле студента и шкале оценок. Моя попытка создать функцию для вычисления и возврата среднего балла студента успешна, но я еще не закончил.
Я новичок в Python и хочу научиться наиболее оптимизированным методам программирования. Я сталкиваюсь с классической проблемой введения соответствия между буквенными и цифровыми оценками; я уже знаю простой способ.
Я выполняю ряд преобразований данных в R. Мой начальник хочет, чтобы результатом был экспорт в Excel со сводной таблицей Excel со всеми возможностями, которые предлагает сводная таблица Excel (например, двойной щелчок.
Я искал в интернете библиотеку, которая может вычислять выражения, похожие на формулы excel, в формулах java, которые не встроены в excel. Предположим, что значений нет в excel, или, скажем, 3 переменные a .