Дата проведения урока –

21.04.2020г.

Дата проведения урока –

23.04.2020г.

Класс – 10 «б» Класс – 10 «а»

Тема урока – «Организация вычислений в электронных таблицах.»

Основное содержание урока:

Для изучения данной темы вы можете ознакомиться в

Видеоуроке: Организация вычислений в электронных таблицах.

Домашнее задание – находится в низу теоретического материала.

Все задания Выполняются в одном файле, на разных страницах, все

листы книги переименовываются согласно заданий. Задания

выполнить до 28.04.2020г.

Основным назначением электронных таблиц является организация всевозможных

вычислений. Вы уже знаете, что:

• вычисление — это процесс расчёта по формулам;

• формула начинается со знака равенства и может включать в себя знаки операций,

числа, ссылки и встроенные функции.

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

требуется использовать в формуле. Ссылки позволяют:

• использовать в одной формуле данные, находящиеся в разных частях электронной

таблицы;

• использовать в нескольких формулах значение одной ячейки.

Различают два основных типа ссылок: 1) относительные — зависящие от положения формулы;

2) абсолютные — не зависящие от положения формулы.

Различие между относительными и абсолютными ссылками проявляется при

копировании формулы из текущей ячейки в другие ячейки.

Относительные ссылки. Присутствующая в формуле относительная ссылка

определяет расположение ячейки с данными относительно ячейки, в которой записана

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

Рассмотрим формулу =А1ˆ2, записанную в ячейке А2. Она содержит относительную

ссылку А1, которая воспринимается табличным процессором следующим образом:

содержимое ячейки, находящееся на одну строку выше той, в которой находится

формула, следует возвести в квадрат.

При копировании формулы вдоль столбца и вдоль строки относительная ссылка

автоматически корректируется так:

• смещение на один столбец приводит к изменению в ссылке одной буквы в имени

столбца;

• смещение на одну строку приводит к изменению в ссылке номера строки на единицу.

Например, при копировании формулы из ячейки А2 в ячейки В2, С2 и D2

относительная ссылка автоматически изменяется и рассмотренная выше формула

приобретает вид: =В1ˆ2, =С1ˆ2, =D1ˆ2. При копировании этой же формулы в ячейки

АЗ и А4 получим, соответственно, =А2ˆ2, =А3ˆ2 (рис. 4).

Рис. 4. Скопированная формула с относительной ссылкой

Пример 1. В 8 классе мы рассматривали задачу о численности населения

некоторого города, ежегодно увеличивающейся на 5%. Проведём в электронных

таблицах расчёт предполагаемой численности населения города в ближайшие 5 лет,

если в текущем году она составляет 40 ООО человек.

Внесём в таблицу исходные данные, в ячейку ВЗ введём формулу =В2+0,05*В2 с

относительными ссылками; скопируем формулу из ячейки ВЗ в диапазон ячеек В4:В7

(рис. 5).

Рис. 5. Вид таблицы расчёта численности населения в режиме отображения

формул и режиме отображения значений Ежегодный расчёт численности населения мы (согласно условию задачи)

осуществляли по одной и той же формуле, исходные данные для которой всегда

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

расчётная формула. При копировании формулы, содержащей относительные ссылки,

нужные нам изменения осуществлялись автоматически.

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

расположенную в определённом (фиксированном) месте. В абсолютной ссылке перед

каждой буквой и цифрой помещается знак $, например $А$1. При изменении позиции

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

формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется (рис. 6).

Рис. 6. Скопированная формула с абсолютной ссылкой

Пример 2. Некий гражданин открывает в банке счёт на сумму 10 ООО рублей. Ему

сообщили, что каждый месяц сумма вклада будет увеличиваться на 1,2%. Для того

чтобы узнать возможную сумму и приращение суммы вклада через 1, 2,..., 6 месяцев,

гражданин провёл следующие расчёты (рис. 7).

Рис. 7. Расчёт приращения суммы вклада

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

Выполните аналогичные расчёты для начального вклада, равного 15 ООО рублям.

Смешанные ссылки. Смешанная ссылка содержит либо абсолютно адресуемый

столбец и относительно адресуемую строку ($А1), либо относительно адресуемый

столбец и абсолютно адресуемую строку (А$1). При изменении позиции ячейки,

содержащей формулу, относительная часть адреса изменяется, а абсолютная часть

адреса не изменяется.

При копировании или заполнении формулы вдоль строк и вдоль столбцов

относительная часть ссылки автоматически корректируется, а абсолютная часть

ссылки не корректируется (рис. 8).

Рис. 8. Скопированная формула со смешанной ссылкой

Чтобы преобразовать ссылку из относительной в абсолютную и наоборот, можно

выделить её в строке ввода и нажать клавишу F4 (Microsoft Office Excel) или

комбинацию клавиш Shift+F4 (OpenOffice Calc). Если выделить относительную

ссылку, такую как А1, то при первом нажатии этой клавиши (комбинации клавиш) и

для строки, и для столбца установятся абсолютные ссылки ($А$1). При втором

нажатии абсолютную ссылку получит только строка (А$1). При третьем нажатии

абсолютную ссылку получит только столбец ($А1). Если нажать клавишу F4

(комбинацию клавиш Shift+F4) ещё раз, то для столбца и строки снова установятся

относительные ссылки (А1).

Пример 3. Требуется составить таблицу сложения чисел первого десятка, т. е.

заполнить таблицу следующего вида:

При заполнении любой ячейки этой таблицы складываются соответствующие ей

значения ячеек столбца А и строки 1. Иначе говоря, у первого слагаемого неизменным

остаётся имя столбца (на него следует дать абсолютную ссылку), но изменяется номер

строки (на неё следует дать относительную ссылку); у второго слагаемого изменяется

номер столбца (относительная ссылка), но остаётся неизменным номер строки

(абсолютная ссылка).

Внесите в ячейку В2 формулу =$А2+В$1 и скопируйте её на весь диапазон B2:J10.

У вас должна получиться таблица сложения, знакомая каждому первокласснику.

Встроенные функции

При обработке данных в электронных таблицах можно использовать встроенные

функции — заранее определённые формулы. Функция возвращает результат

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

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

более понятным.

В электронных таблицах реализовано несколько сотен встроенных функций,

подразделяющихся на: математические, статистические, логические, текстовые,

финансовые и др.

Каждая функция имеет уникальное имя, которое используется для её вызова. Имя,

как правило, представляет собой сокращённое название функции на естественном

языке. При выполнении табличных расчётов достаточно часто используются функции:

СУММ (SUM) — суммирование аргументов;

МИН (MIN) — определение наименьшего значения из списка аргументов;

МАКС (МАХ) — определение наибольшего значения из списка аргументов.

Диалоговое окно Мастер функций позволяет упростить создание формул и свести

к минимуму количество опечаток и синтаксических ошибок. При вводе функции в

формулу диалоговое окно Мастер функций отображает имя функции, все её

аргументы, описание функции и каждого из аргументов, текущий результат функции и

всей формулы.

Пример 4. Правила судейства в международных соревнованиях по одному из видов

спорта таковы:

1) выступление каждого спортсмена оценивают N судей;

2) максимальная и минимальная оценки (по одной, если их несколько) каждого

спортсмена отбрасываются;

3) в зачёт спортсмену идёт среднее арифметическое оставшихся оценок.

Информация о соревнованиях представлена в электронной таблице:

Требуется подсчитать оценки всех участников соревнований и определить оценку

победителя. Для этого:

1) в ячейки А10, All, А12 и А14 заносим тексты «Максимальная оценка»,

«Минимальная оценка», «Итоговая оценка», «Оценка победителя»;

2) в ячейку В10 заносим формулу =МАКС(ВЗ:В8); копируем содержимое ячейки В10

в ячейки C10:F10;

3) в ячейку В11 заносим формулу =МИН(ВЗ:В8); копируем содержимое ячейки В10 в

ячейки C11:F11;

4) в ячейку В12 заносим формулу =(СУММ(ВЗ:В8)-В10-В11)/4; копируем

содержимое ячейки В12 в ячейки C12:F12;

5) в ячейку В14 заносим формулу =MAKC(B12:F12).

Результат решения задачи:

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

При изучении предшествующего материала вы неоднократно встречались с

логическими операциями НЕ, И, ИЛИ (not, and, or). Построенные с их помощью

логические выражения вы использовали при организации поиска в базах данных, при

программировании различных вычислительных процессов.

Реализованы логические операции и в электронных таблицах, но здесь они

представлены как функции: сначала записывается имя логической операции, а затем в

круглых скобках перечисляются логические операнды.

Например, логическое выражение, соответствующее двойному неравенству

0<А1<10, в электронных таблицах будет записано как И(А1>0;А1<10)

(AND(A1>0;A1<10)).

Вспомните, как аналогичное логическое выражение мы записывали при знакомстве

с базами данных и языком программирования Паскаль.

Пример 5. Вычислим в электронных таблицах значения логического выражения НЕ

А И НЕ В при всех возможных значениях входящих в него логических переменных.

При решении этой задачи мы следовали известному вам алгоритму построения

таблицы истинности для логического выражения. Вычисления в диапазонах ячеек

С3:С6, D3:D6, Е3:Е6 проводятся компьютером по заданным нами формулам.

Для проверки условий при выполнении расчётов в электронных таблицах

реализована логическая функция ЕСЛИ (IF), называемая условной функцией.

Условная функция имеет следующую структуру:

ЕСЛИ (<условие>; <действие1>; <действие2)

Здесь <условие> — логическое выражение, т. е. любое выражение, построенное с

помощью операций отношения и логических операций, принимающее значение

ИСТИНА или ЛОЖЬ.

Если логическое выражение истинно, то значение ячейки, в которую записана

условная функция, определяет <действие1>, если ложно — <действие2>.

Что вам напоминает структура условной функции?

Пример 6. Рассмотрим задачу о приёме в школьную баскетбольную команду:

ученик может быть принят в эту команду, если его рост не менее 170 см.

Данные о претендентах (фамилия, рост) представлены в электронной таблице.

Использование условной функции в диапазоне ячеек С3:С8 позволяет вынести

решение (принят/не принят) по каждому претенденту.

Функция СЧЁТЕСЛИ (COUNTIF) позволяет подсчитать количество ячеек в

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

С9 подсчитывается число претендентов, прошедших отбор в команду.

Функция СУММЕСЛИ (SUMMIF) суммирует значения диапазона,

удовлетворяющие заданному условию.

Практическая работа №1. Форматирование таблиц. Формулы.

ЗАДАНИЕ 1.

1. Создайте таблицу по образцу.

2. Объедините ячейки В2:В3 и С2:J2.

Для этого:

Выделите необходимые ячейки

На вкладке

ГЛАВНАЯ нажмите

инструмент

3. Чтобы сделать ячейки шире

необходимо встать между номерами ячеек и протащить вниз.

4. Занесите данные в таблицу.

5. Чтобы расположить данные в ячейках С3:J3 по центру необходимо:

Выделить диапазон ячеек С3:J3.

На вкладке ГЛАВНАЯ нажмите инструменты

6. Сделайте границы таблицы. Для этого:

Выделите диапазон ячеек В2:J7

На вкладке ГЛАВНАЯ выберите инструмент

6. Для того, чтобы заполнить итоговые ячейки необходимо:

Встать в ячейку J4.

На вкладке ГЛАВНАЯ нажмите инструмент

Выделите диапазон ячеек С4:I4

Нажмите клавишу ENTER

7. Встаньте в ячейку J4 и автозаполните вниз.

8. Для того, чтобы заполнить итоговые ячейки необходимо:

Встать в ячейку С7.

На вкладке ГЛАВНАЯ нажмите инструмент

Выделите диапазон ячеек С4:С6

Нажмите клавишу ENTER

9. Встаньте в ячейку С7 и автозаполните вправо.

10. Переименуйте лист РАСХОДЫ

ЗАДАНИЕ 2.

1. Оформите таблицу следующего вида.

2. Объедините следующие ячейки А1:Е1, С3:Е4, А1:В4,

С5:С9 (На вкладке ГЛАВНАЯ инструмент

3. Занесите данные в таблицу.

4. Для того, чтобы в ячейках данные разнеслось на две

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

панели ГЛАВНАЯ нажать инструмент

5. ФОРМУЛЫ

Рост = (( длина ступни - 15)*100)/15,8 =((В2-

15)*100).15,8

Длина шага = длина ступни * 3 =В2*3

Ширина ступни = Рост / 18 =В3/18

Длина пятки = Рост / 27 =В3/27

Голова = Рост / 8 =В3/8

Плечо = (Рост - 73,6) / 2,97 =(В3-73,6)/2,97

Предплечье = (Рост - 80,4) / 3,65 =(В3-80,4)/3,65

Бедро = (Рост - 69,1) / 2,24 =(В3-69,1)/2,24

Голень = (Рост - 72,6) / 2,53 =(В3-72,6)/2,53

Идеальная масса = ((Рост * 3) / 10 - 450 + Возраст) * 0,25 + 45 =((В3*3)/10-450+Е2)

6. Переименуйте лист СЛЕД

ЗАДАНИЕ 3.

1. Объедините следующие диапазоны ячеек A1:L1, A2:A3, B2:B3, C2:C3, D2:F2, G2:G3, H2:J2,

K2:K3, L2:L3.

2. Внесите данные в ячейки

3. Расположите данные по центру. Инструменты

4. Для того, чтобы в ячейках данные разнеслось на две строки, необходимо выделить

необходимую ячейку и на панели ГЛАВНАЯ нажать инструмент

5. Ввод расчётных формул (ПРЕМИЯ 10 %)

Установить курсор в ячейку D4

Введите с клавиатуры знак =

Щёлкните по ячейкеe C4

Введите с клавиатуры знак *

Введите с клавиатуры 0,1

Зафиксируйте формулу нажатием клавиши ENTER

Автозаполните

6. СЕВЕРНЫЕ НАДБАВКИ=ОКЛАД*0,8

7. РАЙОННЫЙ КОЭФФИЦИЕНТ=ОКЛАД*0,8

8. ИТОГО НАЧИСЛЕННО=ПРЕМИЯ+СЕВЕРНЫЕ НАДБАВКИ+РАЙОННЫЙ

КОЭФФИЦИЕНТ

9. АВАНС ЗАНЕСИТЕ САМИ.

10. ПОДОХОДНЫЙ НАЛОГ=ИТОГО НАЧИСЛЕННО*0,13

11. ПРОФСОЮЗНЫЕ ВЗНОСЫ=ИТОГО НАЧИСЛЕННО*0,02

12. ИТОГО УДЕРЖАННО=АВАНС+ПОДОХОДНЫЙ НАЛОГ+ПРОФСОЮЗНЫЕ

ВЗНОСЫ

13. ВСЕГО К ВЫДАЧЕ=ИТОГО НАЧИСЛЕННО-ИТОГО УДЕРЖАННО

14. Просуммируйте итог.

Абсолютная и относительная адресация

Задание2.

1. Откройте лист ПОКАЗАНИЯ СЧЁТЧИКА

2. Для того, чтобы рассчитать значения в

диапазоне ячеек В4:В15 необходимо:

Набрать формулу =В4+30

Автозаполните

3. Занесите произвольные данные в столбец

ПОКАЗАНИЯ СЧЁТЧИКА.

4. Для того чтобы рассчитать Расход надо из

предыдущего месяца вычесть текущий месяц, т.е

=С3-С4 и Автозаполните.

5. Ввод расчётных формул в столбец СУММА с

учётом того, что летом не работает

электроплита.

Установите курсор D4

0

100

200

300

400

500

600

700

1926 г 1939 г 1970 г 1989 г 2000 г

ГОДА

Численность населения Мурманск Архангельск Вологда

Северодвинск Нарьян-Мар

Введите формулу = D4*$B$20

Автозаполните до МАЯ

Установите курсор в E9

Введите формулу =Е9*$С$20

Автозаполните до августа

Установите курсор в E12

Введите формулу =Е12*$В$20

Автозаполните до августа

6. Просчитайте итоговую сумму в ячейке Е16.

ПОСТРОЕНИЕ ДИАГРАММ 1. Создайте следующую таблицу.

Построить гистограмму, сравнивающую численность населения в городах по всем годам. Для

этого:

Выделите диапазон ячеек А2:F2

Перейдите на вкладку ВСТАВКА-ГИСТОГРАМА и выберите ти .

ОФОРМИМ ДИАГРАМУ. Для этого выделим её.

Перейдём на кладку КОНСТРУКТОР и выберем СТИЛЬ ДАГРАММЫ.

Перейдём на кладку МАКЕТ.

Выберем инструмент НАЗВАНИЕ

ДИАГРАММЫ-НАД ДИАГРАММОЙ. И

введём название численность населения

Выберем инструмент НАЗВАНИЕ

ОСЕЙ-НАЗВАНИЕ ОСНОВНОЙ

ГОРИЗОНТАЛЬНОЙ ОСИ. Введи название

ГОДА.

Выберем инструмент ЛЕГЕНДА-

ДОБАВИТЬ ЛЕГЕНДУ СВЕРХУ.

Выберем инструмент ПОДПИСИ

ДАННЫХ-ПОКАЗАТЬ.

Сравните диаграмму с образцом.

Фильтрация и сортировка данных.

1.Откройте базу данных БОЛЬНЫЕ.

2. В ячейке С1 вычислите сегодняшнюю дату.

ФОРМУЛЫ-ВСТАВИТЬ ФУНКЦИЮ из

категории ДАТА И ВРЕМЯ выбрать

СЕГОДНЯ() и нажать ОК.

3. Вычислить возраст больных участка. Для этого:

Встанем в ячейку F3

ФОРМУЛЫ-ВСТАВИТЬ ФУНКЦИЮ из категории ДАТА И ВРЕМЯ выбрать ДНЕЙ 360

В появившемся окне в окно НАЧ_дата выбираем ДАТУ РОЖДЕНИЯ Е3

В окно Кон_дата выбираем сегодняшнюю дату С1.

Нажимаем ОК.

Встаём в строку формул и делим на 360. У вас должна получится формула

=ДНЕЙ360(E3;C1)/360

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

=ДНЕЙ360(E3;$C$1)/360

4. Выберем инструмент ФОРМА.

5. Просмотрите данные таблицы используя полосы прокрутки или кнопку ДАЛЕЕ.

6. Добавьте новую запись в таблицу. Для этого:

Встаньте в любую ячейку таблицы.

Выберите инструмент ФОРМА

Нажмите кнопку ДОБАВИТЬ

Введите в поля новые данные.

7. Отберите записи из списка, удовлетворяющие условию 3 участок. Для этого:

Выберите инструмент ФОРМА

Нажмите кнопку КРИТЕРИЙ

В поле № УЧАСТКА введите 3

Нажмите кнопку ДАЛЕЕ

Нажимайте кнопку ДАЛЕЕ пока не просмотрите все данные удовлетворяющие данному

условию.

Нажмите кнопку ЗАКРЫТЬ.

8. Отберите записи из списка удовлетворяющие следующим критериям:

Работающие в ЗАО СГД

Больные 4 участка

Женщины проболевшие ОРЗ

9. Отсортируйте данные в алфавитном порядке. Для этого:

Перейдите на вкладку ДАННЫЕ.

Установите курсор в ячейку с фамилиями

Выберите инструмент сортировка

10. Отсортируйте данные

По дате рождения

По номерам участка

По месту работы

По полу

11. Отфильтруем данные таблицы. Для этого:

Перейдём на вкладку ДАННЫЕ и выберем команду ФИЛЬТР. (справа от названия полей

списка возникают раскрывающаяся кнопка со стрелкой, которая содержит перечень значений

для данного ряда)

Раскроем список в столбце ПОЛ

Уберём галочку с поля ВЫДЕЛИТЬ ВСЁ

Поставим галочку в поле Ж.

В результате у вас появится список женщин.

Выделите получившеюся таблицу и скопируйте её на новый лист, назвав его ЖЕНЩИНЫ.

Для того, чтобы вернутся к прежней таблице, в списке справа от названия поля выбираем

вариант ВЫДЕЛИТЬ ВСЕ.

12. Отберём больных с 1937 г по 1953 г. Для

этого:

Раскроем список в поле ДАТА

РОЖДЕНИЯ.

Выберем ФИЛЬТР ПО ДАТЕ-МЕЖДУ.

В диалоговом окне укажите следующие

данные

Скопируйте получившиеся данные на новый лист и переименуйте его С 1937 по 1953.

Вернитесь к прежней таблице.

13. Создайте следующие фильтры:

Определите больных старше 25 лет с 3 участка

Определите перечень больных работающих на конкретном предприятии

Определите всех женщин на втором участке.

Определите перечень больных с 1974 г рождения по 1986.

КАЖДЫЙ ФИЛЬТР СОХРАНЯЙТЕ НА ОТДЕЛЬНОМ ЛИСТЕ.

ЗАДАНИЕ 2. (дополнительно)

Прибыль магазинов г. Нарьян-Мара

Магазин Выручка за

день

Количество

дней

Выручка за

месяц

Налог (12

%)

Пенсионный

фонд(1%)

Прибыль

ХОРОШИЙ 34563 30

БЕЛЫЕ

НОЧИ

76753 27

МАСТЕР 23897 31

ЗОЛУШКА 98762 58

Налог 12%

Пенсионный

фонд

1%

1. Заполните ячейки таблицы

2. Постройте гистограмму показывающую прибыль магазинов.

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

Построить диаграмму сравнивающую прибыль двух магазинов Х