Методика преподавания информатики: Excel

Выполнение расчётов в Электронных таблицах Excel.

Цель урока:

На примере задачи рассмотреть уже известные учащимся режимы работы ЭТ, такие как формирование таблицы, редактирование, вычисление по формулам; показать возможности копирования и перемещения ячеек и блоков ячеек, а также особенности копирования формул, объяснить понятие ссылки на ячейку, указав особенности использования в формулах относительной и абсолютной ссылки, возможность использования при записи формул стандартных функций (СУММ), показать возможность сохранения

 

 

План урока:

Повторение пройденного материала:

Назначение ЭТ Excel;

Особенности экранного интерфейса программы;

Ввод данных в таблицу (текста, чисел, даты и времени, формул);

Редактирование вводимых в ячейки данных;

Выделение данных (различные способы);

Вставка ячеек, строк и столбцов;

Удаление ячеек, строк и столбцов.

Решение задачи:

Разбор условия задачи;

Решение задачи в общем виде (математическая модель задачи);

Оформление таблицы.

Формирование ЭТ и объяснение понятия ссылка на ячейку.

При заполнении таблицы показать различные способы копирования содержимого ячеек;

Выполнить копирование формул с использованием абсолютной ссылки;

При вводе формул показать возможность использования стандартных функций;

После завершения формирования таблицы используя рамки и обрамляющие линии нагляднее представить таблицу.

Получить копию таблицы с другими исходными данными. Продемонстрировать автоматический пересчёт вычисляемых данных при изменении исходных данных показав, что информационная структура ЭТ – динамическая.

На примере готовой таблицы продемонстрировать возможность отображения в ячейках не результатов вычислений, а формул.

Сохранить результаты работы на диске.

 

Ход урока

Вопросы для проверки:

Для чего предназначена ЭТ Excel;

Как обозначается имя ячейки;

Как определить адрес ячейки;

Как ввести данные в ячейку;

Как отредактировать данные;

Каким образом по умолчанию выравнивается в ячейке текст, а каким – число;

Где расположена строка формул;

Как выделить ячейку, блок ячеек;

Какой вид имеет указатель мыши в процессе выделения;

Как выделить целиком столбец, строку;

Как выделить несколько смежных столбцов, строк;

Как выделить целиком таблицу;

Как вставить столбец, строку.

 

Имеется бригада лиц из n лиц, между которыми требуется распределить полученную бригадой прибыль пропорционально коэффициентам трудового участия (КТУ) h(i). На членов Бригады были наложены денежные штрафы s(i), идущие в фонд прибыли бригады и увелиЧивающие сумму прибыли. Определить величину денежного вознаграждения для каждого Члена бригады. Расчеты произвести при разных значениях полученной прибыли, и двух Вариантов штрафов на членов бригады.Исходные данные величина полученного коллективом дохода - D руб.;-количество сотрудников в списке - n;-коэффициент трудового участия (КТУ) - h(i);- величина штрафа, наложенного на i-го сотрудника - s(i);Вычислим:1) Общую сумму штрафов:S = s(1) + s(2) +…+ s(n)2) Величину нового дохода:D1 = D + S 3) Общее значение КТУH = h(1) + h(2) +…+ h(n)4) "Цену" единицы общего зачения КТУ P = D1/H5) Причитающийся доход i- го сотрудника d(i) = P * h(i)

Фамилия, инициалы КТУ Величина штрафа, руб Причитающийся доход, руб
1. Соломин Н. Е. 1 100 =D43*C31
2. Титова Ю.А. 1 152 =D43*C32
3. Розин Г. Д. 0,9 160 =D43*C33
4. Чистов М. Ю. 1 130 =D43*C34
5. Петров С. А. 0,95 240 =D43*C35
6. Иванов П. М. 0,8 150 =D43*C36
7. Николаева М. Н. 0,5 190 =D43*C37
8. Морозов П.Г. 0,7 200 =D43*C38
9. Семенов Н. И. 1 120 =D43*C39
10. Белова Ж. Ю. 1 100 =D43*C40
Итого: =СУММ(C31:C40) =СУММ(D31:D40)  
Измененная прибыль:   =5000+D41 =СУММ(G31:G40)
"Цена" единицы КТУ:   =D42/C41 =D42/C41

 

Копирование и перемещение ячеек.

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

1 способ С помощью команд меню или кнопок панели инструментов Стандартная.

Правка (Edit)/Копировать (Copy)

Укажите ячейку, которая станет верхним левым углом вставленного блока

Правка / Вставить (Paste)

Аналогично для перемещения

Правка /Вырезать (Cut)

Указать ячейку, которая станет верхним левым углом вставленного блока

Правка/Вставить

 

2 способ С помощью мыши.

Выделить ячейку и указать на рамку выделенного блока так, чтобы указатель принял вид стрелки, направленной под углом вверх

Нажать Ctrl и не отпускать её и левую кнопку мыши, до тех пор, пока не закончите перетаскивание. (после нажатия Ctrl рядом с указателем появится знак +)

Аналогично для перемещения

Выделить ячейку и указать на рамку выделенного блока так, чтобы указатель принял вид стрелки, направленной под углом вверх

Удерживая нажатой левую кнопку мыши, перетащите ячейку или блок ячеек на новое место

 

3 способ С помощью клавиш

Копировать Ctrl+C или Ctrl+Insert

Вырезать Ctrl+X или Shift+Delete

Вставить Ctrl+V или Ctrl+Insert

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

Попробуйте скопировать формулу из ячейки G31 в диапазон ячеек G32:G40. (Если вместо цены единицы КТУ брать полученное в ячейке D43 значение, т.е. константу, то мы получим правильный результат, а если использовать адрес ячейки D43, то результат будет равен нулю.)

Почему это происходит мы сейчас и попытаемся выяснить.

Ссылки на ячейку.

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

Относительная ссылка используется в формуле для указания адреса ячейки, вычисляемого в относительной системе координат с началом в текущей ячейке. Относительные ссылки имеют вид: А1, В1 и т.п.

В нашей задаче при заполнении последнего столбца «Причитающийся доход», в формуле каждое значение КТУ умножается на одно и то же значение, вычисленное в строке «Цена единицы КТУ». В таких случаях, составляя формулу, применяют абсолютные ссылки. При перемещении или копировании формулы абсолютные ссылки не изменяются (ячейка фиксируется), в то время как относительные ссылки автоматически обновляются в зависимости от нового положения. Абсолютные ссылки имеют вид: $F$1, $C$45. Для фиксации координаты применяют знак $.

Следовательно, для того, чтобы получить верные результаты в нашем примере, вместо формулы C15*B3 должна быть формула $C$15*B3.

Абсолютная ссылка используется в формуле для указания адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки.

Измените формулу в вашей таблице и распространите её вниз на весь диапазон ячеек.

 

Вы наверное заметили, что Excel показывает в ячейках результаты вычислений, а не формулы, по которым выполнялись вычисления. Даже при копировании формул вы сразу видите результат, а не формулу. Рассмотрим каким же образом в ячейке можно увидеть формулу, по которой производились вычисления. Предварительно необходимо выделить ячейки с формулами. Затем выполнить следующие действия:

выбрать в меню Сервис (Tools) команду Параметры (Options);

откройте вкладку Вид (View);

установите флажок Формулы и нажмите кнопку Ok.

 

Посмотрите на формулы в вашей таблице.

В ячейках C41 и D41 записаны длинные формулы, позволяющие найти сумму всех значений, находящихся в диапазоне ячеек С31:C40 и D31:D40. Запись таких формул можно сделать короче если воспользоваться стандартными функциями Excel. Excel имеет обширный список функций, по которым выполняются автоматические вычисления. Функция вводится в ячейку листа как часть формулы. Если функция стоит в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле. Формулы, в свою очередь, могут содержать другие функции. Аргументы функции записываются в скобках. Список аргументов может состоять из чисел, текста, логических величин, массивов, значений ошибок или ссылок. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Константами являются даты, числа или текст. В качестве аргумента могут использоваться адреса ячеек. Аргументы разделяются точкой с запятой.

 

Чтобы выполнить расчёт, используя функцию, содержащуюся в списке, можно воспользоваться одним из следующих способов:

выбрать команду Функция (Function) в меню Вставка (Insert)

нажать кнопку Вставка функций (Paste Function) на панели инструментов стандартная

нажать сочетание клавиш Shift+F3

 

В окне Вставка функций представлены два списка. Левый список Категория содержит такие элементы, как:

функция управления базами данных и списками

функции даты и времени

финансовые функции

информационные функции

логические функции

функции просмотра и ссылок

математические и тригонометрические функции

статистические функции

текстовые функции

 

Выделив нужную категорию, вы увидите в правом списке Функция содержащиеся в ней функции. Полоса прокрутки в правом списке позволяет просмотреть невидимые в данный момент элементы.

 

Если вам нужна для работы какая-нибудь функция, выберите её в списке Функция одним из следующих способов:

дважды щелкните имя функции

выделите функцию и нажмите кнопку Ok

выделите функцию и нажмите клавишу Enter

 

Измените формулы находящиеся в ячейках C41 и D41 используя стандартную функцию СУММ, подсчитывающую сумму всех чисел, находящихся в диапазоне указанных ячеек.

На этом формирование нашей таблицы завершено. Остаётся только сделать таблицу более наглядной. Для этого мы воспользуемся линиями и рамками обрамляющими создаваемый документ.

Выделите заключаемые в рамку ячейки

В меню Формат выберите команду Ячейки

Откройте вкладку Граница

В группе Все можно отказаться от проведения рамки Нет или провести линии внешние, внутренние

В группе Отдельные щелчком мыши нажмите кнопки линий, которые вы хотите провести или снять

В группе Линия укажите необходимый вариант

В раскрывающемся списке Цвет выберите цвет линий рамки

Выбрать обрамление для выделенных ячеек позволяет кнопка Границы на панели инструментов Форматирования. Список возможных вариантов обрамления появляется на экране после щелчка кнопки со стрелкой рядом с кнопкой

 

Задание:

Сделайте копию таблицы и измените значение величины штрафа на 67000 руб.

Посмотрите, что произошло с результатами в ячейках таблицы

Сделайте вывод.

 

Вывод: при изменении исходных данных происходит автоматический пересчёт вычисляемых данных, это говорит о том, что информационная структура ЭТ-динамическая.

Данная таблица нам ещё потребуется на следующем уроке, поэтому сохраним её на диске для дальнейшей работы.

Сохранение документа

В меню Файл выберите команду Сохранить как

В поле Имя файла введите имя документа и нажмите кнопку Сохранить.

Опубликовано: Четверг, 23 июня 2016 13:10
Прочитали: 1942 раз