|
|
|
Учебник для 9 класса Информатика и ИКТ
§ 5.2. Организация вычислений в электронных таблицахКлючевые слова:
Основным назначением электронных таблиц является организация всевозможных вычислений. Вы уже знаете, что:
Рассмотрим вначале вопросы, касающиеся организации вычислений в электронных таблицах. 5.2.1. Относительные, абсолютные и смешанные ссылкиСсылка указывает на ячейку или диапазон ячеек, содержащих данные, которые требуется использовать в формуле. Ссылки позволяют:
Различают два основных типа ссылок:
Различие между относительными и абсолютными ссылками проявляется при копировании формулы из текущей ячейки в другие ячейки. Относительные ссылки. Присутствующая в формуле относительная ссылка фиксирует расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. Рассмотрим формулу =А1 При копировании формулы вдоль столбца и вдоль строки относительная ссылка автоматически корректируется так:
Например, при копировании формулы из ячейки А2 в ячейки В2, С2 и D2 относительная ссылка автоматически изменяется и формула приобретает вид: =В1
Рис. 5.4. Пример 1. Ранее мы уже рассматривали задачу о численности населения некоторого города, ежегодно увеличивающейся на 5% . Проведём в электронных таблицах расчёт предполагаемой численности населения города в ближайшие 5 лет, если в текущем году она составляет 40 000 человек. Внесём в таблицу исходные данные, в ячейку ВЗ введём формулу = В2+0,05*В2 с относительными ссылками; скопируем формулу из ячейки ВЗ в диапазон ячеек В4:В7 (рис. 5.5).
Рис. 5.5. Ежегодный расчёт численности населения мы (согласно условию задачи) осуществляли по одной и той же формуле, исходные данные для которой всегда находились в ячейке, расположенной в том же столбце, но на одну строку выше, чем расчётная формула. При копировании формулы, содержащей относительные ссылки, нужные нам изменения осуществлялись автоматически. Абсолютные ссылки. Абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в определённом (фиксированном) месте. В абсолютной ссылке перед каждой буквой и цифрой помещается знак $, например $А$1. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется (рис. 5.6).
Рис. 5.6. Пример 2. Некий гражданин открывает в банке счёт на сумму 10 000 рублей. Ему сообщили, что каждый месяц сумма вклада будет увеличиваться на 1,2%. Для того чтобы узнать возможную сумму и приращение суммы вклада через 1, 2,..., 6 месяцев, гражданин провёл следующие расчёты (рис. 5.7).
Рис. 5.7. Прокомментируйте формулы в таблице на рис. 5.7. Выполните аналогичные расчёты для начального вклада, равного 15 000 рублям. Смешанные ссылки. Смешанная ссылка содержит либо абсолютно адресуемый столбец и относительно адресуемую строку ($А1), либо относительно адресуемый столбец и абсолютно адресуемую строку (А$1). При изменении позиции ячейки, содержащей формулу, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется (рис. 5.8).
Рис. 5.8. Чтобы преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить её в строке ввода и нажать клавишу F4 (Microsoft Office Excel) или клавиши SHIFT+F4 (OpenOffice.org Calc). Если выделить относительную ссылку, такую как А1, то при первом нажатии этой клавиши (комбинации клавиш) и для строки, и для столбца установятся абсолютные ссылки ($А$1). При втором нажатии абсолютную ссылку получит только строка (А$1). При третьем нажатии абсолютную ссылку получит только столбец ($А1). Если нажать клавишу F4 (комбинацию клавиш Shift+F4) ещё раз, то для столбца и строки снова установятся относительные ссылки (А1). Пример 3. Требуется составить таблицу сложения чисел первого десятка, т. е. заполнить таблицу следующего вида:
При заполнении любой ячейки этой таблицы складываются соответствующие ей значения ячеек столбца А и строки 1. Иначе говоря, у первого слагаемого неизменным остаётся имя столбца (на него следует дать абсолютную ссылку), но изменяется номер строки (на неё следует дать относительную ссылку); у второго слагаемого изменяется номер столбца (относительная ссылка), но остаётся неизменным номер стоки (абсолютная ссылка). Внесите в ячейку В2 формулу =$A2-fB$l и скопируйте её на весь диапазон B2:J10. У вас должна получиться таблица сложения, знакомая каждому первокласснику.
5.2.2. Встроенные функцииПри обработке данных в электронных таблицах можно использовать встроенные функции — заранее определённые формулы. Функция возвращает результат выполнения действий над значениями, выступающими в качестве аргументов. Использование функций позволяет упростить формулы и сделать процесс вычислений более понятным. В электронных таблицах реализовано несколько сотен встроенных функций, подразделяющихся на: математические, статистические, логические, текстовые, финансовые и др. Каждая функция имеет уникальное имя, которое используется для её вызова. Имя, как правило, представляет собой сокращённое название функции на естественном языке. При выполнении табличных расчётов достаточно часто используются функции: СУММ (SUM) — суммирование аргументов; МИН (MIN) — определение наименьшего значения из списка аргументов; МАКС (МАХ) — определение наибольшего значения из списка аргументов Диалоговое окно Мастер функций позволяет упростить создание формул и свести к минимуму количество опечаток и синтаксических ошибок. При вводе функции в формулу диалоговое окно Мастер функций отображает имя функции, все её аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы. Пример 4. Правила судейства в международных соревнованиях по одному из видов спорта таковы:
Информация о соревнованиях представлена в электронной таблице:
Требуется подсчитать оценки всех участников соревнований и определить оценку победителя. Для этого:
Результат решения задачи:
5.2.3. Логические функцииПри изучении предшествующего материала вы неоднократно встречались с логическими операциями НЕ, И, ИЛИ (NOT, AND, OR). Построенные с их помощью логические выражения вы использовали при организации поиска в базах данных, при программировании различных вычислительных процессов. Реализованы логические операции и в электронных таблицах, но здесь они представлены как функции: сначала записывается имя логической операции, а затем в круглых скобках перечисляются логические операнды. Например, логическое выражение, соответствующее двойному неравенству 0<А1<10, в электронных таблицах будет записано как И(А1>0, А1<10). Вспомните, как аналогичное логическое выражение мы записывали при знакомстве с базами данных и языком программирования Паскаль. Пример 5. Вычислим в электронных таблицах значения логического выражения НЕ А И НЕ В при всех возможных значениях входящих в него логических переменных.
При решении этой задачи мы следовали известному вам алгоритму построения таблицы истинности для логического выражения. Вычисления в диапазонах ячеек СЗ:С6, D3:D6, ЕЗ:Е6 проводятся компьютером по заданным нами формулам. Для проверки условий при выполнении расчётов в электронных таблицах реализована логическая функция ЕСЛИ (IF), называемая условной функцией. Условная функция имеет следующую структуру: ЕСЛИ (<условие>; <действие1>; <действие2>) Здесь <условие> — логическое выражение, т. е. любое выражение, построенное с помощью операций отношения и логических операций, принимающее значения ИСТИНА или ЛОЖЬ. Если логическое выражение истинно, то значение ячейки, в которую записана условная функция, определяет <действие1>, если ложно - <действие2>1. 1 Действием может быть вычисление формулы, вывод числа или текста в ячейку. Что вам напоминает структура условной функции? Пример 6. Вернёмся к задаче о приёме в школьную баскетбольную команду: ученик может быть принят в эту команду, если его рост не менее 170 см. Данные о претендентах (фамилия, рост) представлены в электронной таблице.
Использование условной функции в диапазоне ячеек СЗ:С8 позволяет вынести решение (принят/не принят) по каждому претенденту. Функция COUNTIF (СЧЁТЕСЛИ) позволяет подсчитать количество ячеек в диапазоне, удовлетворяющих заданному условию. С помощью этой функции в ячейке С9 подсчитывается число претендентов, прошедших отбор в команду. В Единой коллекции цифровых образовательных ресурсов (http://school-collection.edu.ru/) размещён «Интерактивный задачник. Раздел "Логические формулы в электронных таблицах"». Попытайтесь самостоятельно выполнить имеющиеся в нём задания в режимах «Тренировка» и «Зачёт». Самое главноеДля организации вычислений в электронных таблицах используются формулы, которые могут включать в себя ссылки и функции. Различают относительные, абсолютные и смешанные ссылки. Относительная ссылка фиксирует расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. Абсолютная ссылка всегда ссылается на ячейку, расположенную в определённом месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. Смешанная ссылка содержит либо абсолютно адресуемый столбец и относительно адресуемую строку, либо относительно адресуемый столбец и абсолютно адресуемую строку. При изменении позиции ячейки, содержащей формулу, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется. Функции — это заранее определённые и встроенные в электронные таблицы формулы. Использование функций позволяет упростить формулы и сделать процесс вычислений более понятным. Вопросы и задания
|
2, записанную в ячейке А2. Она содержит относительную ссылку А1, которая воспринимается табличным процессором следующим образом: содержимое ячейки, находящееся на одну строку выше той, в которой находится формула, следует возвести в квадрат.
|
|