Реакцией на неправильный ввод формул является сообщение об ошибке, начинающееся со знака #
| Значение | Описание |
| #### | Наиболее частое значение ошибки, которое появляется, когда ширина ячейки недостаточна для размещения в ней числа; чтобы устранить ошибку, нужно расширить ячейку или изменить формат числа |
| Значение | Описание |
| #ДЕЛ/0! | Попытка деления на 0 |
| #ИМЯ? | В формуле используется несуществующее имя (или строка символов не заключена в кавычки). Возникает, когда неправильно указано имя объекта или имеется ссылка на имя, которое было удалено или когда при записи адресов вместо латыни использование кириллица. |
| #ССЫЛКА! | Означает наличие проблемы интерпретации ссылок, имеющихся в формуле. Возможно отсутствуют ячейки, адреса которых используются в формуле или формула содержит ссылку на ячейку, в которую скопировано содержимое других ячеек. |
| #ЗНАЧ! | Попытка некорректного использования функции, несоответствие данных установленному формату, например, вместо числа стоит текст. |
| #Н/Д | Сокращения от «неопределенные данные». Нет данных для вычислений. |
| #ЧИСЛО! | Обозначает проблему, связанную с представлением и использованием чисел. Возможно в функции с числовым аргументом использован аргумент нечислового формата либо введена формула, возвращающая слишком большое значение по модулю (больше 1037). |
| #ПУСТО! | В формуле используется пересечение диапазонов, не имеющих общих ячеек, например, поставили в выражение типа (А3:А6 С4:Е7) вместо запятой пробел. |
Выполнение работы.
Для выполнения работы требуется разработать алгоритм, в соответствии с которым будет производиться расчет. Предлагается следующий алгоритм:
1. Ввести дополнительное количество столбцов, по одному на каждый вид оценки (всего пять столбцов)
2. В каждую ячейку столбца ввести формулу. Суть формулы состоит в том, что напротив фамилии студента в ячейке соответствующего вспомогательного столбца вид полученной им оценки отмечался как 1. В остальных ячейках этой строки в других дополнительных столбцах будет стоять 0. Таким образом, полученная оценка в каждом столбце будет отмечаться по следующему условию:
в столбце пятерок – если студент получил 5, то отображается 1, иначе 0;
в столбце четверок – если студент получил 4, то отображается 1, иначе 0;
в столбце троек – если студент получил 3, то отображается 1, иначе 0;
в столбце двоек – если студент получил 2, то отображается 1, иначе 0;
в столбце неявок – если студент не явился на экзамен, то отображается 1, иначе 0.
3. В нижней части таблицы ввести формулы подсчета суммарного количества, полученных оценок определенного вида, и общего количества оценок.
4. Сверить полученный общий вид таблицы, результаты и структуры формул с тем, что показано на рисунке 1 (в режиме отображения значений) и на рисунке 2 (в режиме отображения формул).
5. Скопировать несколько раз (по числу экзаменов в сессии) этот шаблон на другие листы и провести коррекцию оценок по каждой дисциплине.
Внимание! При выполнении п.2 постоянно сравнивайте ваши результаты на экране с изображением на рисунке 1.
| A | B | C | D | E | F | G | H | I | J | ||||
| Экзаменационная ведомость | |||||||||||||
| Группа ______ | Дисциплина ____________________ | ||||||||||||
| № п/п | Фамилия, имя, отчество | № зачетной книжки | Оценка | Подпись преподавателя | неявки | ||||||||
| Аненков В.М. | |||||||||||||
| Брагин П.Н. | |||||||||||||
| Воробьев К.А. | |||||||||||||
| Голубкина Н.М. | |||||||||||||
| Дятлов Н.А. | |||||||||||||
| Кукушкин А.А. | |||||||||||||
| Отлично | |||||||||||||
| Хорошо | |||||||||||||
| Удовлетворительно | |||||||||||||
| Неудовлетворительно | |||||||||||||
| Неявки | |||||||||||||
| Итого | |||||||||||||
Рисунок 1 – Электронная таблица в режиме отображения значений
Технология работы:
1. Загрузить рабочую книгу с именем Session1 (Пр11).
2. Проделать подготовительную работу, вводя названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов (см. рисунок1).
3. В столбцы F-J ввести вспомогательные формулы (см. ниже). Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1. Для ввода формул воспользуйтесь Библиотекой функций. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:
· установите курсор в ячейку F6 и выберите вставку Формулы, в группе инструментов Библиотека функций нажмите кнопку Логические;
· выберите вид функции: ЕСЛИ
· в диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операции логической функции:
| Логическое выражение – D6=5 Значение, если истина – 1 Значение, если ложно − 0 |
· щелкните по кнопке ОК
4. Ввести формулы аналогичным способом в остальные ячейки данной строки. В результате в ячейках F6-J6 должно быть:
| Адрес ячейки | Формула |
| F6 | ЕСЛИ(D6=5;1;0) |
| G6 | ЕСЛИ(D6=4;1;0) |
| H6 | ЕСЛИ(D6=3;1;0) |
| I6 | ЕСЛИ(D6=2;1;0) |
| J6 | ЕСЛИ(D6=”н/я”;1;0) |
5. Скопировать эти формулы во все остальные ячейки дополнительных столбцов:
· выделите блок ячеек F6:J6;
· установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протащите её до конца таблицы Экзаменационная ведомость;
6. Определить имена блоков ячеек по каждому дополнительному столбцу. Рассмотрим это на примере дополнительного столбца F.
· выделите все значения дополнительного столбца F;
· введите команду Формула ð Определенные имена ð Присвоить имя;
· в диалоговом окне в строке Имя введите слово ОТЛИЧНО;
· щелкните на кнопке ОК;
· проведя аналогичные действия с остальными столбцами, вы создадите еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
7. Сделать столбцы F-J скрытыми:
· установите курсор на названии столбцов и выделите F-J;
· выполните команду Главная ðгруппа инструментов Ячейки ðФормат, выберите Скрыть или отобразить ð Скрыть столбцы.
8. Ввести формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек. Покажем это на примере подсчета оценок ОТЛИЧНО:
· установите указатель мыши в ячейку С13 подсчета количества отличных оценок;
· щелкните по кнопке Математические в Библиотеке функций вкладки Формулы;
· выберите функцию СУММ:
· в диалоговом окне в строке Число 1 установите курсор и введите слово ОТЛИЧНО;
· щелкните по кнопке ОК;
· повторите аналогичные действия для подсчета количества других оценок в ячейках С14-С17
9. Подсчитать общее количество (ИТОГО) всех полученных оценок другим способом:
· установите курсор в пустой ячейке С18 (рядом с ИТОГО). Эта ячейка должна обязательно находиться под ячейками, где подсчитывалась сумма по всем видам оценок;
· щелкните по кнопке ∑ на вкладке Главная в группе инструментов Редактирование;
· щелкните блок ячеек, где подсчитывалась сумма по всем видам оценок, и нажмите клавишу Enter.
10.Переименовать текущий лист
· установите курсор на имени текущего листа и вызовите контекстное меню;
· выберите параметр Переименовать и введите новое имя Экзамен 1
11.Скопировать текущий лист Экзамен 1 в конец книги:
· установите курсор на имени текущего листа и вызовите контекстное меню;
· выберите параметр Переместить/Скопировать, поставить флажок Создавать копию и параметр Переместить в конец, нажмите ОК, обратите внимание на автоматическое наименование ярлыков новых листов.
12.В скопированном листе заменить значения в ячейках формулами:
· выполните команду Файл, Параметры (или для Excel 2007 Office, Параметры Excel), группа команд Дополнительно;
· найти Показать параметры для следующего листа (в окошке видим имя необходимого нам листа Экзамен 1(2)) и установите флажок напротив Показывать формулы, а не их значение. Сравните результат с рисунком 2.
13.Сохранить и закрыть книгу с экзаменационными ведомостями под именем Session.
Контрольные вопросы:
1. Что представляет собой формула?
2. Что отображается в ячейке, содержащей формулу?
3. Для чего предназначены встроенные функции? Приведите примеры таких функций.
4. Для чего предназначена кнопка Автосуммирования? Как ею пользоваться?
5. Как присвоить определенное имя блоку ячеек? Для чего это необходимо?
6. Какая ссылка называется относительной? Почему? Приведите примеры?
7. Какая ссылка называется абсолютной? Чем она отличается от относительной?
8. Когда следует использовать относительные ссылки, а когда абсолютные? Приведите примеры.
Содержание отчета
Отчет о выполнении практической работы выполняется в Microsoft Word (или в тетради) и должен содержать:
1. Номер практической работы.
2. Тему работы.
3. Цель работы.
4. Ответы на контрольные вопросы.
5. Вывод по работе.
Отчет (в виде документа Word или фотографий тетрадных страниц) и результат выполнения заданий (документ Excel Session или ПР№12 с выполненным заданием) отправить по электронной почте на адрес vita.bondarenko.62@mail.ru или сообщением Вконтакте
| A | B | C | D | E | F | G | H | I | J | ||||||
| Экзаменационная ведомость | |||||||||||||||
| Группа ________ | Дисциплина ____________________ | ||||||||||||||
| № п/п | Фамилия, имя, отчество | № зачетной книжки | Оценка | Подпись преподавателя | неявки | ||||||||||
| Аненков В.М. | =ЕСЛИ(D6=5;1;0) | =ЕСЛИ(D6=4;1;0) | =ЕСЛИ(D6=3;1;0) | =ЕСЛИ(D6=2;1;0) | =ЕСЛИ(D6=н/я;1;0) | ||||||||||
| Брагин П.Н. | =ЕСЛИ(D7=5;1;0) | =ЕСЛИ(D7=4;1;0) | =ЕСЛИ(D7=3;1;0) | =ЕСЛИ(D7=2;1;0) | =ЕСЛИ(D7=н/я;1;0) | ||||||||||
| Воробьев К.А. | =ЕСЛИ(D8=5;1;0) | =ЕСЛИ(D8=4;1;0) | =ЕСЛИ(D8=3;1;0) | =ЕСЛИ(D8=2;1;0) | =ЕСЛИ(D8=н/я;1;0) | ||||||||||
| Голубкина Н.М. | =ЕСЛИ(D9=5;1;0) | =ЕСЛИ(D9=4;1;0) | =ЕСЛИ(D9=3;1;0) | =ЕСЛИ(D9=2;1;0) | =ЕСЛИ(D9=н/я;1;0) | ||||||||||
| Дятлов Н.А. | =ЕСЛИ(D10=5;1;0) | =ЕСЛИ(D10=4;1;0) | =ЕСЛИ(D10=3;1;0) | =ЕСЛИ(D10=2;1;0) | =ЕСЛИ(D10=н/я;1;0) | ||||||||||
| Кукушкин А.А. | =ЕСЛИ(D11=5;1;0) | =ЕСЛИ(D11=4;1;0) | =ЕСЛИ(D11=3;1;0) | =ЕСЛИ(D11=2;1;0) | =ЕСЛИ(D11=н/я;1;0) | ||||||||||
| Отлично | =СУММ(ОТЛИЧНО) | ||||||||||||||
| Хорошо | =СУММ(ХОРОШО) | ||||||||||||||
| Удовлетворительно | =СУММ(УДОВЛЕТВ) | ||||||||||||||
| Неудовлетворительно | =СУММ(НЕУДОВЛ) | ||||||||||||||
| Неявки | =СУММ(НЕЯВКИ) | ||||||||||||||
| Итого | =СУММ(С13:С17) | ||||||||||||||
Рисунок 2 – Электронная таблица Экзаменационная ведомость в режиме отображения формул