У сьогоднішньому епізоді я розповідав про простий додаток для книг в Excel. Хоча концепція проста, в заліковій книзі є кілька формул, які можуть вас спокусити. Зошит виглядає так:
Вертикальні заголовки
Заголовки в рядку 2 повертаються на бік за допомогою Формат - Клітинки - Вирівнювання. На вкладці Вирівнювання змініть орієнтацію на 90 градусів.
Окуляри Можливий ряд
Важливо, щоб у таблиці був рядок із переліком загальної кількості балів за кожне завдання. Не заповнюйте бали за завдання, поки не введете оцінки за це завдання. На першому малюнку вище можливі пункти знаходяться в рядку 17.
Загальна кількість формул у колонці H
Введіть функцію = SUM у H4 для підсумовування балів для першого студента. Скопіюйте цю формулу до стовпця H для кожного учня та для лінії можливих балів. Excel автоматично змінює відносні посилання у формулі, щоб вказувати на наступний рядок тощо.
Хитро: Середні формули в колонці I
Формула I4 може здатися простою. Ви можете спробувати ввести =H4/H17, але це призведе до проблеми.
Коли ви копіюєте цю формулу на кілька рядків, вона перестає працювати. Тут у рядку 6 формула змінена на =H6/H19. Правильно, що Excel змінив H4 на H6, але ви хочете, щоб Excel завжди абсолютно вказував на H17 як дільник у розрахунку.
Поверніться назад і відредагуйте формулу в I4. Ви можете вибрати I4 і натиснути F2, або просто двічі клацнути I4.
Коли точка вставки знаходиться відразу після H17, натисніть клавішу F4. Excel вставляє знаки долара, щоб змінити H17 на $ H $ 17.
Знак долара перед символом H говорить, що незалежно від того, в якому напрямку ви копіюєте формулу, ви завжди хочете вказати на стовпець H.
Знак долара перед символом 17 говорить, що незалежно від того, в якому напрямку ви копіюєте формулу, ви завжди хочете вказати на рядок 17.
Це називається абсолютним посиланням.
Коли ви копіюєте цю формулу для всіх студентів, Excel завжди правильно ділить на загальну кількість балів у рядку 17.
Перетворення відсотків у літерні класи
Ви будете використовувати функцію VLOOKUP для перетворення відсотків у літерний клас. Існує два способи використання функції VLOOKUP, і 90% часу на цьому веб-сайті я використовую версію, де Excel повинен знайти точну відповідність у таблиці пошуку. Однак ця версія використовує версію VLOOKUP, де таблиця містить діапазони даних.
У цій версії таблицю пошуку слід сортувати в порядку від низького до високого. Як ви можете бачити нижче, для цього потрібно взяти шкільну шкалу оцінок і перевернути її догори дном. У таблиці нижче рядок 4 говорить, що будь-які оцінки вище 0 отримують F. Рядок 5 приймає верх і говорить, що будь-які оцінки вище 65% отримують D. У рядку 6 будь-що з 69% або вище - це D + . Основна ідея полягає в тому, що Excel знайде число, яке дорівнює або менше оцінки студента.
- Щоб побудувати VLOOKUP в J4, почніть з
=VLOOKUP( - Перший аргумент - оцінка студента в
I4. - Наступним аргументом є діапазон з таблицею пошуку. У цьому прикладі таблиця знаходиться в
L4:M12. - Ви хочете, щоб формула завжди вказувала на цей самий діапазон, тому натисніть клавішу F4, щоб змінити її на
$L$4:$M$12. - Остаточний аргумент - це номер стовпця з вашої таблиці з літерною оцінкою. У цьому прикладі це a
2. - Формула така
=VLOOKUP(I4,$L$4:$M$12,2).
- Скопіюйте формулу для всіх учнів.
Версія, яку я використав на шоу, невелика - з кількома студентами і лише кількома завданнями. Ви можете завантажити цю меншу версію, щоб побачити, як працюють формули. Клацніть правою кнопкою миші CFHGrades.zip .
