MS Excel: решение задач ВПР

1.

MS Excel:
решение задач ВПР

2.

ЗАДАЧА 1
Дан
фрагмент
электронной
таблицы. В ячейку С1 введена формула
=A1+B1.
Какие формулы и какие значения будут в
ячейках С2:C7 после копирования в них
формулы из С1
Преобразование:
При копировании формулы по
столбцу у обоих аргументов
формулы будет изменяться
номер строки и номер столбца.
Строка увеличивается на 1,
формула примет вид: =A2+B2; С1> C3 – строка увеличивается на 2,
формула примет вид: =A3+B3

3.

ЗАДАЧА 2
Дан фрагмент электронной
таблицы.
В ячейку С1 введена формула
=A1+B1.
Какая
формула
и
какое
значения будет в ячейке D7
после копирования в нее
формулы из С1?
Преобразование: При копировании формулы из С1 в D7
столбец увеличивается на 1, строка увеличивается на
7. Исходная формула =A1+B1 преобразуется в =B7+C7.

4.

ЗАДАЧА 3
Дан фрагмент электронной таблицы. Из ячейки D2 в
ячейку E1 была скопирована формула. При копировании
адреса ячеек в формуле автоматически изменились.
Каким стало числовое значение формулы в ячейке E1?
1
2
A
1
2
B
10
20
C
100
200
D
1000
=$B2+C$3
20000
3
3
30
300
3000
30000
4
4
40
400
4000
40000
Преобразование:
Новая формула стала выглядеть так:
=$B1+D$3. что, в свою очередь, равно 3010.
E

5.

ЗАДАЧА 4
Дан фрагмент электронной таблицы. Чему станет
равным значение ячейки D1, если в неё скопировать
формулу из ячейки С2?
A
B
C
1
2
1
5
2
4
3
= $A$2 + B$3
3
6
7
= A3 + B3
D
Решение
$A$2: ничего не меняется.
B$3: столбец меняется, не меняется номер строки.
Номер столбца D больше номера столбца C на 1, значит, при
копировании из С2 в D1 столбец B в формуле станет столбцом C.
Окончательный вид =$A$2 + C$3, т. е. D1 = A2 + С3 = A2 + A3 + B3
Подставим имеющиеся значения: D1 = 5 + 6 + 7 = 18.

6.

ЗАДАЧА 5
В ячейки диапазона C3:F6
электронной таблицы записаны
числа (см. рисунок).
В
ячейке
А1
записали
формулу =E$5-$D4. После этого
ячейку А1 скопировали в ячейку В2.
Какое число будет показано в
ячейке В2?
A
1
2
3
4
5
6
B
C D E
F
1 2 3 4
11 13 15 17
21 24 27 30
31 35 39 43
Решение
Сначала запишем вид формулы после копирования в ячейку В2.
Запись $D4 означает, что столбец не меняется, а номер строки меняется.
Запись E$5 означает, что строка не меняется, а номер столбца меняется.
Номер строки 1 увеличился на 1, значит, при копировании в
ячейку B2 из A1 строка 4 станет строкой 5, а строка 5 останется строкой 5.
Номер столбца A увеличился на 1, значит, при копировании в ячейку B2 из
А1 столбец E станет столбцом F.
Окончательный вид: F$5-$D5.
Вычислим значение в ячейке В2, оно равно 30 − 24 = 6.

7.

Создание Кроссворда
в MS Excel

8.

Чтобы создать кроссворд надо
оформить 4(3) листа рабочей книги
1.
2.
3.
4.
Титульный лист
Лист с вопросами (кроссвордом)
Расчетный
Итоговый

9.

Вариант структурной схемы интерактивного кроссворда

10.

Титульный лист
На титульном листе можно расположить: рисунок, текст,
ячейки для в вода фамилии, группы, описание правил работы
с предложенным кроссвордом, указать на различие букв «е»,
«ё», «и», «й».
Чтобы оформить правила работы в виде примечания
необходимо выделить слово нажать правую кнопку мыши и
выбрать пункт добавить примечание. Затем в появившемся
окне набрать нужный текст.

11.

Кроссворд
1-й способ: На листе располагается «сетка» с пустыми
клетками для ответов и вопросы. Для каждого вопроса
указывается адрес ячейки, начиная с которой нужно писать
ответ на вопрос или номер вопроса

12.

Вопросы
2-ой способ: На поле размещается только «сетка» с
пустыми для ответов. К клетке, с которой начинается словоответ, делается примечание, в котором и пишется
формулировка вопроса. Для просмотра вопроса достаточно
указать мышь подвести к началу разгадываемого слова. Лист
с вопросами должен обязательно содержать гиперссылку на
итоговый лист.

13.

Расчетный лист
Для этого на расчетном листе для каждого слова-ответа
введем формулу, определяющую, совпадает ли ответ,
введенный учеником, с правильным ответом. Например, для
первого вопроса набираем формулу в ячейке А1, для второго
– в ячейке А2 и т.д. Используем функции ЕСЛИ и СЦЕПИТЬ
(функция объединяет несколько текстовых строк в одну; в
данном случае объединяются клеточки с буквами, которые и
образуют ответ).

14.

Расчетный лист
Вот так будет выглядеть формула проверки для слова
«принтер», расположенного по горизонтали, начиная с
ячейки М6 (лист с вопросами поименован «Кроссворд»)
=ЕСЛИ(СЦЕПИТЬ(Вопросы!M6;Вопросы!M7;Вопросы!M8;Во
просы!M9;Вопросы!M10;Вопросы!M11;Вопросы!M12;)=
"принтер";1;0).
Если ответ, введенный учеником,
совпадает с правильным ответом, то
значение выражения в ячейке расчета
пусть будит равно 1, иначе – 0. Так же
поступаем для каждого вопроса. Далее
суммируем значения всех ячеек и
подсчитываем
среднею
оценку.
Используем функцию ЕСЛИ.

15.

Итоговый лист
На итоговом листе выводится фамилия и
оценка, которую получил данный ученик.

16.

Пример алгоритма выставления оценки
в интерактивном кроссворде
Общее количество вопросов = 14

17.

Продолжаем работу с кроссвордом.
1. Защита ячеек
2. Проверка правильности работы кроссворда
3. Защита и скрытие листов
4. Защита кроссворда
5. Создания примечание
6. Использование WordArt

18.

Защита ячеек
Ячейки, в которые вводится информация (фамилия, класс,
ответы на вопросы), должны быть доступны. Все остальные
ячейки следует защитить от внесения каких-либо изменений.

19.

Снятие защиты
Для того чтобы снять защиту с ячеек, их нужно
выделить, зайти в пункт меню Главная/ Рецензирование/
Защита, на которой убирается флажок около поля
Блокировать ячейку

20.

Защита и скрытие листов
Все листы кроссворда необходимо защитить. Чтобы
защитить
лист
выбираем
пункт
меню
Главная/
Рецензирование/ Защита. Расчетный лист, прежде чем
защитить, необходимо выделить и присвоить шрифту белый
цвет, то есть расчетный лист будем казаться пустым. Далее
защищаем его, как описано выше, а затем скрываем. На
оставшихся листах следует убрать лишние элементы: строку
формул и строку состояния, сетку, ярлычки листов и т.д.

21.

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

22.

Создания примечание
Чтобы
создать
примечание
выбираем
вкладку
«Рецензирование»
Далее выделяем ячейку и нажимаем «Создать
Примечание». После этого появится окошко где и вписываем
свое примечание. Чтобы удалить примечание выделяем
ячейку где вы создали примечание и нажимаем «удалить»
1
2
3

23.

Использование WordArt
Чтобы создать надпись WordtArt выбираем вкладку
«Вставка» далее WordArt после чего выбираем стиль
надписи (по вкусу) после чего появится окошко «текст
надписи» где и вписываем любое слово.

24.

Защита кроссворда
Чтобы усилить защиту
кроссворда от неосторожного
изменения и умышленных
действий
файлу
можно
присвоить атрибут Только для
чтения

25.

Создание простого теста
в MS Excel

26.

Рассмотрим технологию составления
компьютерных тестов средствами Excel.
Можно выделить следующие этапы
создание теста.
1 этап. Продумать способ оформления
вопросов. Составить вопросы.
2 этап. Выбрать способ ввода ответа и
оформления ответа.
3 этап. Выбрать способ оценивания и
подведения итогов.

27.

1 этап.(например)
1. Сколько минут в 4 часах?
а) 400
б) 40
в) 60
г) 240
2. Решите уравнение 2х + 3х = 150
а) 30
б) 25
в) 750
г) 900
3. Найдите площадь прямоугольника со
сторонами 7 см и 5 см
а) 12
б) 24
в) 35
г) 37
4. Найдите периметр прямоугольника
со сторонами 7 см и 5 см
а) 12
б) 24
в) 35
г) 37
5. Записать в сантиметрах 3м 60 см
а) 360
б) 36
в) 3600
г) 3060
6. Найдите произведение чисел
32 и 4
а) 8
б) 38
в) 128
г) 28
7. Найдите восьмую часть от 4800
а) 700
б) 600
в) 60
г) 2400

28.

Оформление бланка вопросов
Предварительно можно переименовать лист, например ТЕСТ
Оформить бланк:
Рис. 1

29.

Объединить ячейки блока В5:F5.
Записать текст вопроса 1;

30.

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

31.

2 этап.
•В блок J5:J8 записать возможные варианты ответов на вопрос1;
•Объединить ячейки блока D6:F6 записать команду щелкнуть здесь
•Выделить ячейку G6;
•В командном меню выбрать Данные/Проверка…;
Рис. 3

32.

•В появившемся окне Проверка вводимых значений во вкладке
Параметры в поле Тип данных выбрать Список;
Рис. 4

33.

•В появившемся поле Источник: (рис.4) щелкнуть левой кнопкой
мыши и, выделив блок возможных ответов (J5:J8 ),
занести его в это поле (рис.5);
=$J$5:$J$8
Рис. 5

34.

•Во вкладке Сообщение для ввода в поле Сообщение:
ввести текст «выбрать ответ» (рис.6);
Рис. 6
щелкнуть левой кнопкой мыши по ОК

35.

В результате, при щелчке по G6, рядом с ячейкой появиться
значок списка ответов с подсказкой «выбрать ответ» (рис.7).
Щелкнув по
ответов (рис.8).
этому значку развернется список возможных
рис.7
рис.8

36.

•В ячейку N6 записать формулу определения верного ответа:
=ЕСЛИ(G6=240;1;0)
(если выбирается число 240, то 1 балл, иначе 0 баллов).
Рис.9

37.

Аналогично выполняется оформления последующих
вопросов теста;

38.

3 этап.
•В ячейку N40 записывается формула для подсчета всех
баллов;
=N5+N10+N15+N20+N25+N30+N35

39.

Для определения оценки в ячейку D40 записывается
формула =ЕСЛИ(N40=0;"";ЕСЛИ(И(N40>0;N40<4);2;ЕСЛИ
(И(N40>=4;N40<6);3;ЕСЛИ(И(N40>=6;N40<7);4;ЕСЛИ(И
(N40=7);5)))))

40.

(если количество набранных баллов 0, то в ячейке D40 оценка
не записывается,
если количество набранных баллов меньше 4, но больше 0,
то ставится оценка 2,
если количество набранных баллов больше или равно 4,
но меньше 6, то ставится оценка 3,
если количество набранных баллов больше или равно 6,
но меньше 7, то ставится оценка 4,
если количество набранных баллов равно 7, то ставится
оценка 5).

41.

Замечание 1. В предложенном варианте теста есть возможность
подсмотреть правильный вариант ответа. Чтобы исключить эту
возможность в файле теста можно на лист поставить защиту.
Скрыть данные блока
можно, если
выделить блок;

42.

с командного меню выбрать Формат/Столбец/Скрыть

43.

При этом данные блока не будут отражены на
экране. Для того, чтобы данные были доступны для
просмотра необходимо выделить столбцы I и K, с
командного меню выбрать
Формат/Столбец/Отобразить.

44.

Замечание 2. Кроме этого ученик может методом подбора
выбрать варианты ответов, поэтому выполнить команду Скрыть
столбцы. Защитить лист. В появившемся окне ввести пароль и
затем его подтвердить.

45.

46.

Замечание 3. Можно сделать ссылку на другой лист, по которой
будет открываться оценка, например Лист с именем Результат.
На этот лист копируем формулу с оценкой.
На листе Тест создаем гиперссылку, выполнив команду ВставкаГиперссылка или щелкнув по кнопке Добавить гиперссылку.