Как сделать таблицу успеваемости в excel
В соответствии с федеральным государственным образовательным стандартом среднего профессионального образования для аттестации обучающихся на соответствие их профессиональных достижений поэтапным требованиям соответствующей основной профессиональной образовательной программе по специальности (ОПОП) создаются фонды оценочных средств, позволяющие оценить знания, умения и освоенные компетенции. Оценочные средства текущего контроля и промежуточной аттестации разделяются на контрольные задания, показатели выполнения, критерии оценки.
В ходе реализации стандартов третьего поколения для системы СПО на модульно-компетентностной основе особые требования предъявляются к обработке результатов выполнения студентами контрольных заданий. При проведении анализа контрольных работ преподавателю окажет огромную помощь MS Excel.
Рассмотрим несколько, наиболее часто встречающихся случаев.
1. Оценочное средство состоит только из тестовых заданий
Это самый простой случай. Предположим, что при выполнении данной работы проверяются 4 умения (У1, У2, У3) и 3 знания (З1,З2,З3). Всего заданий в работе 20. Выполнение каждого задания оценивается дихотомической оценкой: 1 – (правильно / да), 0 – (неправильно /нет). В MS Excel строится следующая таблица: (Рисунок 1)
Затем вычисляем коэффициент выполнения по каждому умению и знанию в строках 13 и 14. Объединяем ячейки В13 и С13 и ищем в них сумму баллов, набранных студентами за выполнение заданий, проверяющих У1 (=B10+C10), объединяем ячейки В14 и С14 и находим коэффициент выполнения (=B13/(A4*2), Ф4 – число студентов, на 2 делим т. к. заданий 2). Аналогично делаем для каждого умения и знания. Из полученных расчетов уже видно, что студенты хорошо овладели У3, З2, З3, хуже У4.
Теперь представим полученную информацию графически. (Рисунок 2, Рисунок 3, Рисунок 4)
В ячейки В37:В51 переносим результаты из ячеек В11:Р11 и строим график. В строках 56-58 находим, как распределяются задания в зависимости от коэффициента решаемости. Видно, что коэффициент решаемости 0 – 0,4 – это 1 задание, 0,4-0,7 – 3 задания и 11 заданий имеют коэффициент решаемости 0,7-1. Для этого подсчёта в ячейку В56 заносим формулу: =СЧЁТЕСЛИ(B37:B54;"<=0,4"), в ячейку В57: =СЧЁТЕСЛИ(B37:B54;"<=0,7")-B56, в ячейку В58: =СЧЁТЕСЛИ(B37:B54;"<=1")-B56-B57. (Рисунок 3)
Аналогично строим карту коэффициентов освоения умений и знаний. (Рисунок 4) (Приложение 1, Лист 1)
2. Оценочное средство состоит только из тестовых заданий, но расположение их не упорядочено
Если умение У1 проверяется заданиями 5,6, а умение У2 – заданиями 8,10 и т.д., то рассмотренная выше таблица будет не очень удобной, т. к усложнит процесс введения данных. Для того, чтобы облегчит эту задачу лучше все умения и навыки вынести вправо, а слева оставить таблицу по выполнению заданий. (Рисунок 5)
В ячейку Q5 вводится формула: =F5+G5, т.к. умение У1 проверяют теперь задания 5 и 6 , а результаты их выполнения находятся в этих ячейках, аналогично для всех других умений и знаний. Всё остальное аналогично (Приложение 1, Лист 2).
3. Оценочное средство состоит из различных заданий
Оценочное средство может содержать простейшие тестовые задания с дихотомической оценкой и более сложные задания, предполагающие политомическую оценку. Политомическая оценка предполагает выставление балльной оценки (по возрастающей) за каждый промежуточный правильный ответ решения или правильно выполненное действие. При составлении оценочного средства преподаватель заранее предполагает: при выполнении скольких шагов решения он будет считать, что задание выполнено. Пусть, например, 10 заданий тестовые, 5 предполагают решение, состоящее из 5 шагов и составители решили, что при выполнении 3 из 5 шагов задание будет считаться выполненным. Таблица будет выглядеть следующим образом (Рисунок 6)
В ячейке Т5 вводится формула: =H5+ЕСЛИ(M5>=3;1;0) т.к задание 12 (результат выполнения которого занесён в ячейку М5) предполагает 5 шагов в решении, а при выполнении 3 шагов задание будет считаться выполненным. Аналогично заполнены ячейки U5 (=J5+ЕСЛИ(L5>=3;1;0)), V5 (=B5+ЕСЛИ(N5>=3;1;0)), W5(=ЕСЛИ(O5>=3;1;0)+ЕСЛИ(P5>=3;1;0)). Суммировать, тогда необходимо также ячейки Q5:W5 и от них вычислять процент выполнения. При вычислении коэффициентов выполнения необходимо так же внести исправления. В ячейке L11 будет стоять формула: =L10/($A$4*5), т.к. максимальное число баллов за это задание 5. Всё остальное аналогично (Приложение 1, Лист 3).
При составлении экзаменационных заданий возможно использование заданий трёх уровней: самые простые, тестовые задания; более сложные задания предполагающие несколько шагов при их выполнении; задания повышенного уровня. Таблица результатов выполнения такой работы будет выглядеть так (Рисунок 7). В приведённом примере задания второй части оцениваются 5 баллами, а третьей части 10 баллами. Для каждого задания третьей части определено, что при выполнении 6 шагов из 10 задание считается выполненным. В ячейке Т5 задана формула: =ЕСЛИ(H5>=3;1;0)+ЕСЛИ(M5>=6;1;0), т.к умение У4 проверяется заданием 7 второй части и заданием 12 третьей части.
Графическое представление результатов такой работы можно дополнить. Для каждой части можно построить графики, показывающие сколько процентов учащихся выполнили конкретное задание, выполнили его частично или не выполнили (Рисунок 8, Рисунок 9).
В ячейке С82 введена формула =СЧЁТЕСЛИ(B5:B9;"=1"), чтобы подсчитать число студентов выполнивших первое задание верно, в ячейках D82 и E82 вычисляются соответствующие проценты (D82: =C82/$A$4, E82: =100%-D82). В ячейке Р82 вводится формула для вычисления числа студентов верно выполнивших шестое задание: =СЧЁТЕСЛИ(G5:G9;"=5"). Частично выполнили это задание студенты, получившие за него 4 или 3 балла, поэтому в ячейке R2 записана формула: =(СЧЁТЕСЛИ(G5:G9;"3")+СЧЁТЕСЛИ(G5:G9;"4"))/K$4. Все остальные, задание не выполнили: =100%-Q82-R82.
Аналогично строится гистограмма по результатам выполнения третьей части. (Приложение 1, Лист 4).
Если число заданий в работе большое можно провести обработку на двух листах: на первом просто вводятся результаты, а на втором выполняется перевод выполнено/не выполнено и выводы об уровне усвоения умений и знаний. (Приложение 1, Лист 1-ПКС, 1-ПКС ит). Мы проводим такой подробный анализ для входного контроля и для экзаменационных работ. Это позволяет быстро оценить, что усвоили студенты, а что усвоено плохо и организовать коррекционную работу. Проведение такого анализа позволяет сравнить разные группы и оценить насколько успешно поработали студенты и преподаватель в течение семестра, т.е провести мониторинг.
Такой анализ требует много времени на первоначальном этапе при создании шаблонов. В дальнейшей работе обработка результатов каждой группы займёт 20-30 минут.
Литература:
1. Пермяков О.Е., Менькова С.В. Диагностика формирования профессиональных компетенций. Москва, 2010г.
2. Рекомендации по формированию оценочных средств для государственной (итоговой) аттестации выпускников учреждений СПО и НПО.
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
Лабораторная работа 7.
I. Создание электронного журнала успеваемости в MS Excel
Цель работы: создать таблицу для подсчета статистики успеваемости каждого ученика (студента). Для работы вам потребуется один документ с тремя рабочими листами. Обратите внимание, что в ходе выполнения заданий вы должны:
- отработать некоторые приемы работы с комбинированными, сложными функциями, массивами;
- научиться строить связанные графики.
Рекомендуем для заполнения формул использовать Мастер функций.
Задание 1. Заполнение Листа 1
Создать список учащихся (студентов) из десяти произвольных фамилий, включая свою. После выполнения действий п. 1-5 у вас должна получиться таблица, аналогичная приведенной на рисунке 1.
Рисунок 1. Список студентов группы
1. На Листе1 создайте надпись «Список студентов». Оформление выберите на свое усмотрение. Заполните строку 3 (шапку таблицы). Вместо графы «Телефон» можете вписать любой другой пункт, например, адрес электронной почты, адрес проживания и т.д.
2. Заполните столбец А (порядковый номер No ), с помощью команды автозаполнение. В графе «Факультет» укажите название своего факультета (если название длинное, можно вписать аббревиатуру), а в графе «Группа» - номер своей группы: 126 - цифра 1 – номер курса, цифра 2 – номер потока, цифра 6 – номер группы на потоке. Скопируйте данные на весь столбик E и F (10 позиций). Произвольными данными заполните столбец «Телефон».
3. В ячейках B 20: B 30 создайте список студентов (10 человек), причем, в одной ячейке, например, B 20, должны быть написаны и фамилия и имя. Отсортируйте полученный список по алфавиту (Данные – Сортировка).
4. Затем выполните разделение списка на два столбца. Для этого: Данные – Текст по столбцам. В диалоговом окне разделения текста оставьте формат данных с разделителем. На втором шаге поставьте галочку в поле «Пробел». На третьем шаге в поле «Поместить в» мышью выделите ячейки C 4: D 13 . Нажмите OK .
5. Заполните данные в столбце «Идентификатор студента». Для этого в ячейку B 4 введите формулу =СЦЕПИТЬ( F 4;"-"; A 4). В результате этих действий соединяются текстовые данные из ячейки «Номер группы» и «Порядковый номер». В качестве разделителя мы указали дефис. Вы можете выбрать свой символ разделителя, например, нижнее подчеркивание или «&» или др. Скопируйте формулу на весь список.
6. В ячейке H 4 вы снова совместите фамилию и имя студента используя формулу =СЦЕПИТЬ( C 4;" "; D 4). Обратите внимание, что в кавычках указан один пробел. Скопируйте формулу на весь список.
Задание 2. Заполнение Листа 2
2. Заполните шапку таблицы. Цветовое и шрифтовое оформление выберите на ваш вкус. Заполните столбец « No п/п», используя функцию автозаполнения.
3. Заполните ячейки "дата проведения занятий" ( D 3 - H 3 . ):
- установите формат ячеек D 3 - H 3 - категория - "дата", формат "31 дек.99" (или свой формат)
- В ячейках D 3 и E 3 введите две даты с интервалом в одну неделю, например, D 3 - 01.09.13; E 3 - 07.09.13.
- с помощью команды автозаполнения заполните все остальные ячейки на любые ДВА месяца. В нашем примере указан только один месяц.
- измените формат всех этих ячеек ( D 3 - H 3): разверните текст на 90 градусов и установите выравнивание по середине и по горизонтали и по вертикали (Формат – Ячейка - Выравнивание)
- отформатируйте ширину столбцов: MS Excel : Формат – Столбец – Автоподбор ширины.
5. Вернитесь на Лист 2. В столбце “Идентификатор студента» создайте выпадающие списки с номером студента. Для этого:- выделите диапазон B 3 – B 12, затем: Данные – Проверка данных.
MS Excel 2003: обратите внимание, что данные для Источника должны быть на одном листе с выбранной ячейкой. Поэтому рекомендуется продублировать на листе 2 в любом свободном месте столбец с идентификаторами студентов. В более старших версиях MS Excel можно данные брать с разных листов.
После этого рядом со всеми выделенными ячейками появится кнопка выбора варианта.
7. В ячейке C3 должна появляться фамилия студента в соответсвии с его личным номером. Используйте формулу Поиск по вертикали: категория Ссылки и массивы –ВПР
В первом поле введите адрес ячейки B3 (Лист 2). Во втором поле укажите диапазон всей таблицы с Листа 1 (ячейки B4 - H13). В третьем поле диалогового окна функции укажите номер столбца из выделенного вами диапазона, откуда необходимо выбрать данные. В нашем примере мы должны поместить Фамилию и имя из столбца H. Порядковый номер этого столца в нашем выделении 7. Это число и нужно указать в поле Номер столбца.
Скопируйте формулу на весь необходимый диапазон, используя автозаполнение ячеек. 8. В ячейке L3 подсчитайте средний балл по тесту, выбрав функцию СРЗНАЧ и выделив диапазон числовых данных по тесту. В нашем примере =СРЗНАЧ(I3:K3) (категория Статистические) или =AVERAGE(I3:K3). Скопируйте формулу на весь необходимый диапазон, используя автозаполнение ячеек.
9. В ячейке L7 подсчитайте, сколько осталось написать тестов студенту, используя условие, что ячейки с результатами теста не должны содержать «0», «н», « »:
В категории Статистические находится функция , которая позволяет сосчитать число значений внутри диапазона, удовлетворяющих заданному критерию. Синтаксис данной функции: = СЧЁТЕСЛИ (диапазон;критерий) Где диапазон - это диапазон ячеек, в котором нужно сосчитать число значений, удовлетворяющих заданному критерию; критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например: Функция = СЧЁТЕСЛИ (A1:A7;32) - подсчитывает число значений равных 32 в диапазоне ячеек A1-A7. В кавычки надо заключать текст (например, = СЧЁТЕСЛИ(A1:A7;"яблоки") - будут сосчитаны все ячейки, содержащие слово - яблоки).
10.Для подсчета суммарного балла используйте функцию автосуммирования по строке.
11. Рассчитайте ранг студента в общем списке.
Функция РАНГ() (RANK) категория Статистические вычисляет ранг значения в выборке (распределения участников по местам). Функция РАНГ() имеет три аргумента. Первый – число, место (ранг) которого определяется. Второй аргумент ссылка – диапазон, в котором происходит распределение по местам. В нашем примере это столбец с суммарно набранным баллом. Диапазон должен быть неизменным, следовательно, его нужно указать с помощью абсолютной адресаций. Третий аргумент - Порядок – указатель порядка сортировки. Если третий аргумент 0 или не указан, места распределяются по убыванию значений (т.е. чем больше – тем лучше, 1-е место – максимальное значение). Если же поставить 1, то места будут распределяться по возрастанию (т.е. чем меньше, тем лучше ).
Логическая функция условие: ЕСЛИ() (IF)
Для формирования условий в формулах используется функция ЕСЛИ(). Она имеет три аргумента. Первый аргумент тест – условие, второй аргумент тогда значение – действия которое совершается при выполнении условия, третий аргумент иначе значение – действия при не выполнении условия.Пусть, например, ячейка D5 содержит формулу "=ЕСЛИ (A1
11. Ниже таблицы в ячейки D13 - К13 введите предполагаемое максимальное количество баллов за каждый вид заданий. В ячейке N13 выполните автосуммирование этих максимумов. Решите для себя, при каких условиях студент получит зачет. Например, зачет получает если набрал не менее 75% от общего количества баллов и сдал все тесты. В нашем примере формула будет следующей:
В электронных таблицах возможно использование более сложных логических конструкций с использованием вложенных функций ЕСЛИ(), когда ЕСЛИ() используется в качестве аргумента другой функции ЕСЛИ(). Например, сложная функция =ЕСЛИ(A1<100,"утро",ЕСЛИ(A1=100,"вечер",C1))
выполняет следующие действия: если значение в ячейке A1 меньше 100, то выводится текстовое значение "утро". В противном случае проверяется условие вложенной функции ЕСЛИ(). Если значение в ячейке A1 равно 100 выводится текстовое значение "вечер", иначе выводится значение из ячейки C1. Toт же результат может быть получен с помощью выражения:
При создании сложных логических конструкций, особенно с большим количеством вложенных функций ЕСЛИ(), нередко возникают ошибки, связанные с неправильным синтаксисом логического выражения. Если в ячейке, содержащей формулу, вызвать "Мастер функций", то будет показана структура формулы. Структура формулы помогает найти ошибки при большом количестве вложенных функций.
12. Выполните условное форматирование столбцов «Тесты» и «Зачет», которое позволит в автоматическом режиме изменять цвет ячейки в зависимости от задаваемого правила. Например, если тест написан на 0 баллов, ячейка приобретает красный оттенок. Для этого создайте свои правила: MS Excel 2003: Формат – Условное форматирование – Условие.
MS Excel 2010-2013: Главная – Условное форматирование – Правила выделения ячеек.
Задание 3. Подсчет статистики данных
12. Подсчитайте частоту появления результатов по тестам (0, 1, 2, 3), используя функцию ЧАСТОТА (категория Статистические) .
Функция ЧАСТОТА()(категория Статистические) служит для подсчета количества значений в массиве данных, соответствующих определенному классу. Функцией ЧАСТОТА() можно воспользоваться, например, для подсчета количества учащихся получивших - 5; 4; 3 и 2.
Ниже своей таблицы создайте фрагмент, аналогичный нижеприведенному:
1) выделить весь диапазон ячеек, в которых будет располагаться результат подсчёта частот, т.е. I17 - I20.
2) Не снимая выделения вызвать вставку функции Частота.
3) В поле Массив данных (Классы) указать диапазон всех ячеек, содержащих результаты тестирования. В поле Массив интервалов (Классы) ввести диапазон, содержащий возможные варианты оценки тестирования в нашем случае H17 - H20.
4) нажать сочетание клавиш Ctrl+Shift+Enter, чтобы вывелся массив чисел. Если этого не сделать, то будет выведен только один первый результат.
5) Добавьте условное форматирование к этому диапазону, выбрав опцию «Гистограмма»
Задание 4. Построение графика успеваемости
Постройте график успеваемости по столбцу БАЛЛ. Выделите столбец Фамилия и, удерживая клавишу Ctrl, столбец Балл. Вызовите мастер диаграмм и заполните ВСЕ вкладки и поля диалогового окна. Диаграмма должна быть ПОЛНОСТЬЮ оформлена (название диаграммы, подписи под осями, размерность осей и т.д.).
Задание 5. Заполнение листа 3
На Листе 3 сделайте свой вариант оформления шапки таблицы, например, похожий на приведенный ниже:
5.1. Объедините ячейки С1-W1, выровняйте содержимое ячейки по середине.
5.2. Объедините ячейки X1 и X2, Y1 и Y2. Введите в X - "средняя оценка", в Y - "итоговая оценка", разверните текст на 90 градусов, выровняйте по середине.
5.3. Разделите фамилию и имя в разные столбцы. Для этого выделите столбец B, далее Данные – Текст по столбцам. Заполните все поля диалогового окна.
5.4. Оформите таблицу, произвольным образом выбирая цвета ячеек, обрамление и т.д.
II. Создание электронного журнала успеваемости в проекте «SmileS.Школьная карта»
2. Открыв полученное письмо перейдите по ссылке, выбрав одну из ролей (учитель, директор и т.д.), ознакомьтесь с возможностями проекта.
3. Оформите электронный журнал экспортируя данные с сайта в MS Word и MS Excel . Сформировать отчеты.
Вам поручили вести табель учета, но по какой-то причине руководство не может обеспечить ваше рабочее место специализированным программным обеспечением. Это не значит, что никак нельзя облегчить ваш труд. Форму табеля Т-12 легко найти в интернете. Расчертить таблицу по образцу в Excel вполне посильно, не имея большого опыта. А составить формулы и автоматизировать работу вам поможет эта статья.
Не имеет смысла рассматривать здесь текстовую часть документа с перечислением кодов, которыми заполняется табель. Формулы нужны непосредственно в учетной части. О ней и поговорим. Но вначале проверьте, чтобы в параметрах Excel был включен автоматический режим вычислений (Файл — Параметры — Формулы — Вычисления в книге), иначе вам придется без конца нажимать клавишу F.
Формой табеля предусмотрено указание начала и окончания отчетного периода. Оформите этот раздел в верхней части документа. Затем нужно пронумеровать дни и выделить нерабочие. Это достаточно сложно, поэтому работайте поэтапно. Для начала отформатируйте ячейки, в которых будут видны даты:
- выделите ячейки за первую половину месяца, нажмите клавишу CTRL и выделите оставшиеся ячейки с датами;
- щелкните по значку открытия окна у группы Число и в открывшемся окне на вкладке Число выберите сначала Дата, затем Все форматы, в строке Тип введите ДД (это значит, что под номер дня отводится два символа, а месяц и год будут скрыты);
- подтвердите свои действия (ОК или ENTER).
Теперь в ячейку, отведенную под первую дату месяца, введите ссылку на ячейку с началом периода: =В2.
Во вторую ячейку с датой введите формулу, увеличивающую предыдущую на единицу: =D4+1.
Скопируйте эту формулу в оставшиеся ячейки первой половины месяца. Для второй половины месяца отличие будет только в формуле для начальной даты (=R4+1), в остальных ячейках каждая предыдущая должна увеличиваться на единицу.
Отчетный период может быть разным: с 1 по 30, 31, 28 или 29. Начиная с 29 числа, дат может не быть, поэтому отредактируйте формулу в ячейке, соответствующей этой дате (=ЕСЛИ(AF4
Табель учета рабочего времени – основной документ, содержащий информацию о числе явок и неявок на работу каждого сотрудника фирмы. Он передается в бухгалтерию. И на основании данных рассчитывается и начисляется заработная плата.
Законом предусмотрено 2 унифицированные формы табеля: Т-12 – для заполнения вручную; Т-13 – для автоматического контроля фактически отработанного времени (через турникет).
Данные вносятся каждый рабочий день. В конце месяца подсчитывается итог по явкам и неявкам каждого работника. Формирование отчета можно упростить, автоматизировав заполнение некоторых ячеек средствами Excel. Посмотрим, как.
Заполняем вводные данные функциями Excel
Формы Т-12 и Т-13 имеют практически одинаковый состав реквизитов.
В шапке 2 страницы формы (на примере Т-13) заполняем наименование организации и структурного подразделения. Так, как в учредительных документах.
Прописываем номер документа ручным методом. В графе «Дата составления» устанавливаем функцию СЕГОДНЯ. Для этого выделяем ячейку. В списке функций находим нужную и нажимаем 2 раза ОК.
В графе «Отчетный период» указываем первое и последнее число отчетного месяца.
Отводим поле за пределами табеля. Здесь мы и будем работать. Это поле ОПЕРАТОРА. Сначала сделаем свой календарик отчетного месяца.
Красное поле – даты. На зеленом поле проставляет единички, если день выходной. В ячейке Т2 ставим единицу, если табель составляется за полный месяц.
Теперь определим, сколько рабочих дней в месяце. Делаем это на оперативном поле. В нужную ячейку вставляем формулу =СЧЁТЕСЛИ(D3:R4;»»). Функция «СЧЁТЕСЛИ» подсчитывает количество непустых ячеек в том диапазоне, который задан в скобках.
Вводим вручную порядковый номер, ФИО и специальность сотрудников организации. Плюс табельный номер. Информацию берем из личных карточек работников.
Автоматизация табеля с помощью формул
Первый лист формы содержит условные обозначения для учета рабочего времени, цифровые и буквенные. Смысл автоматизации средствами Excel в том, чтобы при внесении обозначения отображалось количество часов.
Для примера возьмем такие варианты:
- В – выходной;
- Я – явка (рабочий день);
- ОТ – отпуск;
- К – командировка;
- Б – больничный.
Сначала воспользуемся функцией «Выбор». Она позволит установить нужное значение в ячейку. На этом этапе нам понадобится календарь, который составляли в Поле Оператора. Если на какую-то дату приходится выходной, в табеле появляется «В». Рабочий – «Я». Пример: =ВЫБОР(D$3+1;»Я»;»В»). Формулу достаточно занести в одну ячейку. Потом «зацепить» ее за правый нижний угол и провести по всей строке. Получается так:
Теперь сделаем так, чтобы в явочные дни у людей стояли «восьмерки». Воспользуемся функцией «Если». Выделяем первую ячейку в ряду под условными обозначениями. «Вставить функцию» – «Если». Аргументы функции: логическое выражение – адрес преобразуемой ячейки (ячейка выше) = «В». «Если истина» — «» или «0». Если в этот день действительно выходной – 0 рабочих часов. «Если ложь» – 8 (без кавычек). Пример: =ЕСЛИ(AW24=»В»;»»;8). «Цепляем» нижний правый угол ячейки с формулой и размножаем ее по всему ряду. Получается так:
Нужно проделать такую же работу для второй половины месяца. Достаточно скопировать формулы и поменять те ячейки, на которые они ссылаются. Результат работы:
Теперь подведем итоги: подсчитаем количество явок каждого работника. Поможет формула «СЧЁТЕСЛИ». Диапазон для анализа – весь ряд, по которому мы хотим получить результат. Критерий – наличие в ячейках буквы «Я» (явка) или «К» (командировка). Пример: . В результате мы получаем число рабочих для конкретного сотрудника дней.
Посчитаем количество рабочих часов. Есть два способа. С помощью функции «Сумма» — простой, но недостаточно эффективный. Посложнее, но надежнее – задействовав функцию «СЧЁТЕСЛИ». Пример формулы:. Где AW25:DA25 – диапазон, первая и последняя ячейки ряда с количеством часов. Критерий для рабочего дня («Я»)– «=8». Для командировки – «=К» (в нашем примере оплачивается 10 часов). Результат после введения формулы:
Все формулы копируем и вставляем в соответствующие ячейки по всему списку сотрудников. При заполнении такого табеля нужно будет корректировать условные обозначения по работникам.
Если изменяется календарь, то меняются выходные и явки. Вручную проставляем прогулы, отгулы и т.п. Все остальное посчитается автоматически.
24.11.2016 табель был обновлен до версии 2.0 с учетом всех Ваших пожеланий и замечаний. Скачивайте, пользуйтесь, оставляйте свои комментарии под статьей.
Здравствуйте, дорогие друзья!
Сегодня я представлю Вашему вниманию свой вариант реализации одной из насущных проблем – табель учета рабочего времени в excel. В данной статье Вы найдете описание основных концепций построения табеля, инструкцию по его ведению, а также сможете скачать программу с помощью ссылок в начале и в конце статьи. Скачивайте, пользуйтесь, автоматизируйте свой ручной труд =).
Построение табеля учета рабочего времени в excel
Скачав и открыв табель, Вы попадаете на главную страницу построения «Настройки» (Рисунок 1).
Начиная со второй строки, необходимо заполнить список сотрудников, по которым Вы будете вести табель. Укажите уникальный номер сотрудника, ФИО, должность(не обязательно), норму рабочего дня в часах(не обязательно). Норма рабочего дня используется при расчете переработок и недоработок сотрудников в течении месяца, если Вы не заполнили это поле вначале, его можно будет отредактировать в построенном табеле. В ячейках «F2» и «F3» с помощью выпадающего списка выберите год и месяц для построения. Выпадающий список появляется при выборе ячейки.
После того как все данные введены нажмите кнопку «Создать табель» и программа сгенерирует табель учета рабочего времени за указанный месяц и год (Рисунок 2).
Табель учета рабочего времени в excel состоит из следующих областей:
- Легенда таблицы – строка 1, в которой указан месяц и год построения, а также условные обозначения табеля.
- Список сотрудников (колонка «А»), заполненный нами на листе «Настройки».
- Шапка таблицы с проставленными числами и днями недели, выходные дни (Сб и Вс) для наглядности выделены светло-зеленым цветом.
- Область управления, в которой проставляются условные обозначения.
- Расчетные столбцы – столбцы формул для учета основных показателей.
Ведение табеля учета рабочего времени в excel
Табель учета рабочего времени необходимо вести используя стандартные условные обозначения:
- Рабочий день отмечается цифрой от 1 до 24, которая обозначает количество часов, отработанных сотрудником за день. Отмеченный день автоматически подкрашивается светло-синим цветом с помощью правил условного форматирования.
- День отпуска отмечается сокращением оп. Автоматически подкрашивается светло-розовым цветом.
- Праздничный день отмечается сокращением пр. Автоматически подкрашивается салатовым цветом.
- Прогул отмечается обозначением пг. Автоматически подкрашивается серым цветом.
- Отгул обозначается сокращением ог. Автоматически подкрашивается бледно-оранжевым цветом.
- Отпуск без содержания обозначается сокращением обс. Автоматически подкрашивается сиреневым цветом.
- Учебный отпуск обозначается буквой у. Обозначение подкрашивается желтым цветом.
- Командировка обозначается буквой к. Подкрашивается зеленым цветом.
- Больничный отмечается маленькой буквой б. Автоматически подкрашивается светло-желтым цветом.
Пример заполненного табеля учета рабочего времени в excel можно увидеть на Рисунке 3 ниже:
Формульный блок расчетных показателей (столбцы «AG:AJ») добавлен для удобства подведения месячных итогов. Данный блок состоит из четырех столбцов:
- Рабочих дней – количество дней, отработанных сотрудником за отчетный месяц (количество синих ячеек).
- Рабочих часов – количество часов, отработанных сотрудником в отчетном месяце (сумма значений синих ячеек).
- Дней отпуска – количество дней отпуска, использованных сотрудником за отчетный месяц (количество ячеек с обозначением оп).
- Дней больнич.– количество дней, которые сотрудник провел на больничном (количество ячеек с обозначением б).
- Перераб. часов — кол-о часов переработки. Считает часы, которые превышают норму дневной выработки, указанной в столбце «раб. день (ч.)»
- Недораб. часов — кол-о часов, которых не достает до нормы дневной выработки. Пример расчета: норма 8ч, стоит 6ч, недоработка — 2ч; норма 8ч, стоит 12ч, переработка — 4ч; норма 8ч, стоит 1й день 6ч, 2й день 10ч, переработка — 0ч, недоработка — 0ч.
- Отгулов — количество дней,в которые сотрудник брал отгулы (количество ячеек ог).
- Празд. дней — кол-о праздничных дней в месяце. Праздничные дни отмечаются сокращением пр.
- Прогулов дней — кол-о прогулов, отмечается сокращением пг.
- Отп. без содержан. — кол-о дней, которые сотрудник провел в отпуске без содержания. Обозначение — обс.
- Учебного отпуска — кол-о дней учебного отпуска, обозначение у.
- Дней командир. — кол-о дней, которые сотрудник провел в командировке. Обозначение к.
Послесловие
24.11.2016 табель учета рабочего времени в excel был обновлен до версии 2.0 с учетом всех Ваших пожеланий и замечаний. Скачивайте, пользуйтесь, оставляйте свои комментарии под статьей.
Обучающее видео
Практическая работа 3. «Создание таблицы результатов успеваемости класса с использованием встроенных функций. Диаграмма успеваемости»
Указания для выполнения. Создайте в Microsoft Excel таблицу результатов успеваемости класса по предложенному образцу (см. рисунок ниже) с использованием расчетных формул.
Порядок выполнения.
Запустите Microsoft Excel. В созданной вновь книге введите данные согласно образцу ( по столбцам «фамилия», «имя», 1, 2, …12, «контрольная»). Данные в строку «среднее» и столбцы «среднее», «тематическая», «сдал/не сдал» не вводите. Отформатируйте таблицу согласно образцу. Измените направление текста в названии столбцов O, P, Q, R. Используя функцию СРЗНАЧ ( ) заполните ячейки строки 10 «среднее» средними значениями по столбцам от С до N. Образец формулы для вычисления в столбце С виден в строке формул на рисунке. В каждом столбце, соответственно будет меняться имя столбца. С помощью этой же функции записать формулы для вычисления в столбце Р «среднее». Используя функцию округления, заполните столбец Q «тематическая». Используя функцию ЕСЛИ, заполните столбец R «сдал/не сдал».
Практические рекомендации.
П.5. Вычисление среднего значения.
Выделите ячейку, в которую должны вставить формулу (например, С10). Далее в меню Вставка щелкните Функция – откроется окно Мастер функций. (Можно щелкнуть на значке fx слева от строки формул). В шаге первом выберите функцию СРЗНАЧ из списка. Если в списке такая функция отсутствует, в окне категория выбирите «Полный алфавитный перечень» и найдите ниже в списке. После ОК, откроется окно шага 2.
В поле «Число1» введите адреса ячеек, по значению которых вычисляется среднее значение.
Примечание: в строке формул автоматически появилась функция вычисления среднего значения данных в ячейках интервала с С3 по С9. Выглядит она таким образом
Эту формулу можно набирать непосредственно в ячейке С10, не используя мастер функций. Результат будет такой же.
Введите любым из этих способов формулы для вычисления средних значений в соответствующие ячейки.
Выбор формата данных в ячейках.
Выделите те ячейки в таблице, в которых вам необходимо, чтобы число (результат вычислений) имело 2 знака после запятой. Это те ячейки, в которые были вставлены формулы. (По умолчанию для всех ячеек в рабочем листе используется Общий формат.)
Выберите в меню Формат – Ячейки (или правой клавишей мыши щелкнуть на ячейке – в контекстном меню выбрать Формат ячеек..).
Перейдите на вкладку Число. В списке Числовые форматы выберите Числовой. В поле Число десятичных знаков, которое необходимо отображать после запятой (в нашем примере 2).
Не забудьте нажать ОК.
Копирование формул в другие ячейки.
Используя функцию Автозаполнение, скопируйте созданную формулу в другие ячейки таблицы:
1. выделите ячейку с веденной в нее формулой. 2. подведите курсор к правому нижнему углу ячейки, он примет вид + . 3. Нажмите левую кнопку мыши, не отпуская ее, переместите указатель до конца диапазона ячеек, в которые вы хотите скопировать формулу. Так как в формуле использовались относительные ссылки, сама программа автоматически изменит адреса ячеек (посмотрите в строку формул, выделив любую ячейку диапазона, и убедитесь.) 4. Аналогичным способом скопируйте формулы в столбцах O, P, Q, R и строке 10.
Функция ОКРУГЛение
Функция ОКРУГЛ округляет число до заданного кол-ва десятичных разрядов. В нашем задании ее нужно применить в столбце Q (ТематическаяВыделите первую ячейку в столбце (Q Запустите мастер функций. 3. Выберите функцию ОКРУГЛ. 4. В самой таблице щелкните на ячейку конца диапазона (Р 4), и в диалоговом окне в ячейке Число появится выбранный вами адрес ячейки, а в следующее поле Количество цифр введите 0. Не забудьте ОК.
Логическая функция ЕСЛИ
Функция ЕСЛИ устанавливает одно значение, если заданное условие истинно, и другое — если оно ложно. Например, в нашем задании в столбце R, если тематическая оценка больше 3, то ученик считается сдавшим тему, если нет, то несдавшим.
1. В ячейки диапазона R4: R10 введите формулу =ЕСЛИ(Q4>3;»Сдал»;»Не сдал»), меняя индекс строки в формуле, т. е Q5, Q6 …Q10. Можно использовать Копирование, Автозаполнение.
Методические материалы по применению табличного процессора Excel для мониторинга успеваемости учащихся. Описанный способ позволяет определять рейтинг успеваемости в классе по среднему баллу за четверть и проводить анализ успеваемости по четвертям более наглядно. Систему применяю в течение 5 лет.
Вложение | Размер |
---|---|
excel_klruk.rar | 416.66 КБ |
Предварительный просмотр:
По теме: методические разработки, презентации и конспекты
Внутришкольный мониторинг успеваемости учащихся
Формулы для определения показателей успеваемости учащихся и итогов успеваемости и качества знаний (для просмотра формул лучше скачать файл, а не предварительный просмотр).
Мониторинг успеваемости по предметам
Данная таблица поможет учителю и зам. директора по УВР быстро составить анализ работы за четверть, год по всем предметам: успеваемость, качество, обученность средний балл.
Мониторинг успеваемости
Мониторинг (график в процентах) успеваемости по четвертям, за 2011-2012 учебный год, по предмету "Физическая культура".
Мониторинг успеваемости класса за1 и 2 четверть.
Материал для подготовки и проведения классного часа "Анализ нашей учебы за 2 четверть" и вопроса на родительском собрании "Анализ успеваемости класса в 1 и 2 четверти". Более подробно мето.
Мониторинг как способ повышения успеваемости учащихся при изучении иностранного языка.
Оценка уровня успеваемости учащихся с применением информационных технологий.
Каждый учитель обязательно проводит анализ своей работы. В этом нам помогает, утвержденная в школе, оценочная пятибалльная система. Установлены четкие периоды подведения итогов – четверти, год, итог. .
Мониторинг успеваемости учащихся по калмыцкому языку
Класс ПредметДанные по учебным годам 2016-2017 201702018 Кач. знУсп.Кач. Зн.Усп.3 классКалмыцкий язык 100 100 4 класс Калмыцкий язык.
Большинство обязанностей распределяется в школе между несколькими завучами так, чтобы не происходило дублирования одних и тех же управленческих функций и оставалось время для аналитической работы. При этом остается целый ряд задач, занимающих много времени, но не являющихся чисто управленческими. К таким задачам можно отнести: учет успеваемости по классам, по предмету или у отдельных педагогов; подведение итогов за год; результаты экзаменов; а также выявление динамики этих числовых показателей успешности работы образовательного учреждения. Всю это работу удобно проводить с помощью компьютера, ведь здесь главное – быстро и правильно провести расчеты, часто требуется строить диаграммы для сравнения по годам, по предметам и т.д. Замечательно, если в Вашей школе установлена и действует одна из компьютерных систем информатизации образовательного пространства, например, программы от фирмы 1С. В этом случае материалы, подобные предложенным в этой статье, уже имеются в Вашем распоряжении. К сожалению, пока еще во многих школах нет специального программного обеспечения. Учителя и администрация придумывают удобные способы обработки и оформления числовых данных посредством офисных программ, а некоторые ведут обычные записи в тетрадях, проводят вычисления с помощью калькулятора.
Используя свой многолетний опыт работы с компьютером, на основе программ Microsoft Office я составила целый ряд различных удобных “заготовок”, в значительной мере упрощающих и ускоряющих подготовку школьной документации, основанной на расчетных таблицах. Так, в приложении Microsoft Excel я решаю многие задачи: составляю школьное расписание, вношу в него изменения по необходимости, готовлю итоговые отчеты по успеваемости (по классам, по предметам, по результатам экзаменов), оформляю все календарные планы своей работы, делаю различные таблицы для удобного отображения результатов внутришкольного контроля. Благодаря широким возможностям электронных таблиц в значительной мере экономится время, упрощается проверка данных, улучшается качество оформления документов для печати, появляется возможность быстро решать проблемы, которые “вручную” решать сложно и долго.
Предлагаю коллегам воспользоваться готовыми таблицами для подведения итогов экзаменов - файл Приложение1.xls. Надеюсь, коллегам пригодятся также мои рекомендации к этим таблицам.
Рекомендации по использованию таблиц и этапы работы с ними
Вообще, работа с предложенным файлом заключается в том, чтобы ввести списки классов и экзаменационные отметки. В результате автоматически заполняются итоговые таблицы по каждому проведенному экзамену и можно быстро построить диаграммы для сравнения разных показателей. Кроме того, промежуточные результаты помогут Вам при составлении расписания экзаменов по выбору и при контроле сессии. Так как количество 9-х и 11-х классов в школе различается в разные годы, то в случае уменьшения можно скрыть в таблицах лишние строки, а в случае увеличения – путем копирования добавить новые. В данном случае таблица была составлена для шести 9-х и трёх 11-х классов, взято среднее число учащихся – 25 человек в классе.
Для большей наглядности я оставила в таблицах со списками некоторое количество отметок, но убрала все фамилии, также остались пустые строки в таблицах итогов (значит, этот экзамен не выбирали), остались даты – как образец. Все ячейки имеют нужный формат, то есть таблицы подготовлены для работы.
Чтобы не пришлось восстанавливать формулы, не очищайте содержимое ячеек с формулами в списках классов (они выделены светло-коричневым цветом) и в итоговых таблицах.
1. Листы: Списки 9 классов, Списки 11 классов. Вводим списки учащихся по классам – выполняем это перед составлением расписания экзаменов: лучше списки скопировать из школьной базы данных учащихся, но можно набрать вручную.
На этом же этапе вводим в итоговые таблицы фамилии учителей, преподающих в 9-11 классах – листы: итоги 9 кл, итоги 11 кл.
2. Указываем для каждого учащегося выбранные им экзамены на основании списков, сданных классными руководителями. (См. список 9а класса)
Код 1 в ячейке означает выбор данного экзамена. По обязательным экзаменам у всех учащихся коды = 1.
После занесения данных о выборе экзаменов просчитываются контрольные суммы:
Количество экзаменов по каждому учащемуся – справа в столбце “всего”.
Количество учащихся в классе и в школе, которые должны сдавать данный экзамен (строка “всего”)
На основании последних сумм составляем расписание экзаменов по выбору: если устный экзамен сдают более 15 человек, то рекомендуется поставить его в расписании в два дня. Кроме того, желательно продублировать в расписании те предметы, которые выбраны учащимися попарно. Например, 10 человек выбрали пару: обществознание и русский язык устно, ставим оба этих предмета на два дня.
3. На основании приказа о допуске к экзаменам указать количество допущенных к экзаменам учащихся – на листах итоги 9 кл, итоги 11 кл. (один раз вводим, затем копируем во все таблички по предметам).
4. Вносим экзаменационные отметки и даты проведения экзаменов – из экзаменационных протоколов. В результате заполнятся по формулам ячейки, выделенные цветной заливкой.
С помощью контрольных цифр сразу обнаруживаем, если кто-либо из учащихся ещё не сдал экзамен или результат не был внесен в протокол, или по ошибке внесен в протокол лишний экзамен. По своему опыту могу сказать, что возможна любая из этих ситуаций. В первый год работы над результатами экзаменов я ещё не использовала поимённые таблицы, а делала лишь общий подсчет отметок по протоколам, с определением процента качества – такие сведения были нужны для отчёта. Когда одна из учениц не явилась на экзамен, а классному руководителю сказала, что сдала его, то обнаружить обман было очень сложно, ведь не сходилась только конечная сумма по всем классам, а где был просчёт – непонятно. Пришлось мне пересмотреть все протоколы снова. Считая количество экзаменов у каждого ученика, я потратила на это целый день, но нашла причину неверного результата. Именно тогда я решила перенести весь учет в электронные таблицы. В таблице подобная ситуация обнаружилась бы сразу, но, к счастью, таких случаев больше не было.
- По каждому учащемуся – количество отметок соответствует выбору экзаменов (так как письменный экзамен по русскому языку оценивается двумя отметками, то у 9-х классов всего должно быть 5 отметок в сессии, а у 11-х классов – 6 отметок)
- Общее количество отметок по классу - соответствует числу учеников, умноженному на ту же норму (5 или 6).
- Если учащийся сдает экзамены в щадящем режиме, то норма для него отличается. Я выделяю соответствующую строку в списке желтым цветом, добавляю примечание, например, “индив. обучение” и при контроле учитываю разницу.
- Листы со списками на печать не выводим – они нужны для контроля, но при необходимости можно их распечатать для классных руководителей. Тогда выбираем из таблицы диапазон с одним классом и предметами, не выводя на печать столбцы контроля (команды Файл – Печать – Выделенный диапазон)
- Так как учащиеся выбирают экзамены, то в таблицах с итогами есть пустые строки, их при печати нужно скрыть.
- Итоговые таблицы по предметам выводим на печать выборочно - выделить нужную таблицу или несколько таблиц, напечатать диапазон. Не забываем просмотреть перед печатью, настроить поля.
6. При желании можно напечатать диаграммы – сравнить результаты по классам или по предметам. Я привожу (на листе Диаграммы) несколько примеров диаграмм на основе имеющихся таблиц.
Поведение итогов успеваемости за год
1) Копируем листы: Списки 9 кл, Итоги 9 кл. Переименовываем листы на 8 кл.
2) Выполняем автоматическую замену на новых листах: Правка – Найти, указываем 9 – Заменить, указываем 8. При этом во всех данных, заголовках и формулах получаем 8 вместо 9.
3) Далее нужно на листах Списки 8 кл и Итоги 8 кл. заменить названия некоторых предметов, изучаемых в 9 классе, на предметы 8 класса.
Читайте также: