Объекты табличного процессора и их свойства

Конспект урока

Информатика,

10 -  11 класс. Тема — Обработка информации в электронных таблицах

Перечень вопросов, рассматриваемых в теме: структура электронной таблицы и типы данных; встроенные функции; деловая графика; построение графиков функций; применение сортировки и фильтров; поиск решения и подбор параметра.

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

Основная литература по теме урока:

Л. Л. Босова, А. Ю. Босова. Информатика. Базовый уровень: учебник для 11 класса. — М.: БИНОМ. Лаборатория знаний, 2017 (с.5—62)

Открытые электронные ресурсы по теме:

http://lbz.ru/metodist/authors/informatika/3/eor11.php

http://kpolyakov.spb.ru/school/ege.htm

Теоретический материал для самостоятельного изучения

Объекты табличного процессора и их свойства

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

Наиболее распространёнными табличными процессорами являются Microsoft Excel и OpenOffice Calc.

После запуска программы Microsoft Excel на экране открываются два окна: окно табличного процессора и окно созданного в нём документа. Документ, создаваемый в табличном процессоре, называется рабочей книгой и по умолчанию получает имя Книга1. Вновь созданная рабочая книга состоит из трёх листов с именами Лист1, Лист2 и Лист3. Имена листов указываются на ярлычках. Пользователь может переименовать листы по своему усмотрению, добавить к книге новые листы или удалить ненужные. В окне рабочей книги отображается содержимое текущего листа. Рабочая область листа с электронной таблицей столбцами и строками разбита на ячейки. Столбцы обозначены буквами латинского алфавита, строки пронумерованы. Адрес ячейки образуется из имени столбца и номера строки, на пересечении которых она находится.

Ячейка — это наименьшая структурная единица электронной таблицы, которая образуется на пересечении столбца и строки.

Две и более ячейки листа электронной таблицы образуют диапазон ячеек. При задании адреса связного диапазона указывают его начальную и конечную ячейки — ячейки левого верхнего и правого нижнего углов (например, А1:А10). Чтобы указать адрес несвязного диапазона ячеек, надо через точку с запятой указать адреса его связных частей.

В таблице приведены объекты табличного процессора, а также их основные свойства, которые далее будут рассмотрены более подробно.

Пример 3.

Все 5-буквенные слова, составленные из букв К, Л, Р записаны в алфавитном порядке и пронумерованы. Вот начало списка:

1. ККККК

2. ККККЛ

3. ККККР

4. КККЛК

5. КККЛЛ

……

Под каким номером стоит слово ЛКРКЛ?

По условию задачи важно то, что используется набор из трех разных символов, для которых задан алфавитный порядок, поэтому для вычислений можно использовать три любые символа, например, цифры 0, 1 и 2 по возрастанию.

Заменим буквы на цифры: К — 0, Л — 1, Р — 2.

Выпишем начало списка, заменив буквы на цифры:

1. 00000
2. 00001
3. 00002
4. 00010

…

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

Слово ЛКРКЛ запишется в новом коде так: 10201₃. Переводим это число в десятичную систему:

10201₃ = 13⁴ + 03³ + 23² + 03¹+13⁰ = 81+18+1 = 100

Так как нумерация элементов списка начинается с 1, а числа в троичной системе — с нуля, к полученному результату нужно прибавить 1.

Получаем, что слово ЛКРКЛ стоит под номером 101.

Аналогичные действия можно выполнить и в среде Microsoft Excel 2010, используя математическую функцию ДЕС (число; основание):

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

Пример 4.

Школьник составляет 5-буквенные слова, в которых встречаются только буквы А, Б, В, Г, Д, Е, Ж. Причём буква А появляется ровно один раз. Каждая из других допустимых букв может встречаться в слове не более одного раза. Словом считается любая допустимая последовательность букв, не обязательно осмысленная. Сколько существует таких слов, которые может написать школьник?

Для решения этой задачи применим статистическую функцию ПЕРЕСТ (число; число выбранных) — она возвращает количество перестановок заданного числа объектов, которые выбираются из общего числа объектов.

Получаем, что школьник может написать 1800 слов.

Функция, результатом которой является ИСТИНА или ЛОЖЬ, называется логической. К категории логических относятся функции ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

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

— функция СУММЕСЛИ вычисляет сумму тех чисел из указанного диапазона, которые удовлетворяют заданному условию;

— функция СРЗНАЧЕСЛИ вычисляет среднее арифметическое тех чисел из указанного диапазона, которые удовлетворяют заданному условию;

— функция СЧЁТЕСЛИ подсчитывает количество ячеек из указанного диапазона, которые удовлетворяют заданному условию.

Пример 5.

Выясним, сколько решений имеет логическое уравнение .

Преобразуем исходное уравнение, выразив импликацию через инверсию и дизъюнкцию:

Запишем формулу для вычисления логического выражения с помощью логических функций Microsoft Excel:

=ИЛИ(И(Х1;НЕ(Х2)); НЕ(Х3);Х4).

Внесём данные в таблицу и выполним расчёты:

Итак, исходное уравнение имеет 13 решений — столько раз встречается значение ИСТИНА в диапазоне Е2:Е17. Для полсчёта этого значения можно воспользоваться функцией СЧЁТЕСЛИ.

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

Рассмотрим несколько финансовых функций, которыми полезно уметь пользоваться каждому человеку, планирующему взять в банке кредит^[1] или сделать вклад^[2]. Аргументами этих функций являются:

1. Ставка — процентная ставка за период.
2. ПЛТ — выплата, производимая в каждый период (месяц, квартал, год и т.п.).
3. ПС — приведённая (нынешняя) стоимость инвестиции.
4. КПЕР — общее число периодов платежей по кредиту.
5. БС — будущая стоимость инвестиции.
6. Тип — число 0, если оплата в конце периода; число 1, если оплата в начале периода (по умолчанию — 0).

Пример 6.

Пусть ставка кредита в некотором банке составляет 18% годовых. Клиент хочет взять кредит на сумму 100 000 руб. и может выплачивать банку по 4000 руб. ежемесячно. Нужно определить, за сколько периодов клиент сможет погасить этот кредит.

Функция КПЕР (ставка; плт; пс; [бс]; [тип]) возвращает количество периодов платежей для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

Обязательные аргументы функции:

1. Ставка — годовая ставка в процентах, разделённая на количество периодов платежей за год (в нашем примере это 18% ̸ 12).
2. ПЛТ — сумма, которую клиент ежемесячно должен возвращать банку (в нашем примере это –4000, т. к. эти деньги отдаются).
3. ПС — размер кредита (в нашем примере это 100 000).

Формула для вычисления количества периодов выплат для погашения взятого кредита будет иметь вид:

=КПЕР(18% ̸ 12; –4000; 100000).

Получаем приблизительно 32 периода (месяца), т. е. более 2,5 лет.

Пример 7.

Выясним, на какую сумму клиент может взять кредит, если ставка 19% годовых, а выплачивать он может по 12 000 руб. на протяжении двух лет (24 периода).

Функция ПС (ставка; кпер; плт; [бс]; [тип]) возвращает приведённую (к текущему моменту) стоимость инвестиции, представляющую собой общую сумму, которая на данный момент равноценна ряду будущих выплат.

Обязательные аргументы функции:

1. Ставка — годовая ставка в процентах, разделённая на количество периодов платежей за год (в нашем примере это 19% ̸ 12).
2. КПЕР — общее количество периодов выплаты платежей по кредиту (24).
3. ПЛТ — сумма, которую клиент ежемесячно должен возвращать банку (в нашем примере это –12 000, т. к. эти деньги отдаются).

Формула для вычисления размера кредита будет иметь вид:

=ПС(19% ̸ 12; 24; –12000).

Получаем приблизительно 238 054 руб.

Пример 8.

Клиент хочет сделать вклад на 3 года на сумму 300 000 руб. под 11% годовых с ежемесячным начислением процентов. Выясним, какую сумму он получит по окончании срока вклада.

Функция БС (ставка; кпер; плт; [пс]; [тип]) возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки. Иначе говоря, с её помощью можно вычислить сумму, которую выплатят клиенту за вклад под определённые проценты по окончании срока вклада.

Обязательные аргументы функции:

1. Ставка — годовая ставка в процентах, разделённая на количество периодов начисления процентов за год (в нашем примере это 11% ̸ 12)
2. КПЕР — количество периодов начисления процентов (36).
3. ПЛТ — сумма, которая добавляется к вкладу каждый период времени (в нашем примере это 0, т. к. пополнение вклада клиентом не предусмотрено).
4. ПС — начальная сумма вклада (300 000).

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

=БС(11% ̸ 12; 36; 0; –300000).

В результате вычислений получаем 416 663, 58 руб.

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

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

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

Рассмотрим примеры некоторых текстовых функций Microsoft Excel.

Функция СТРОЧН преобразует все буквы обрабатываемого текста в строчные, а функция ПРОПИСН, наоборот, — в прописные. Функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные буквы — строчными. Функция СОВПАД позволяет сравнить две текстовые строки в Microsoft Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае — ЛОЖЬ (функция учитывает регистр, но игнорирует различие в форматировании). Назначение функций СЖПРОБЕЛЫ, ПЕЧСИМВ, СЦЕПИТЬ, ПОВТОР, НАЙТИ и многих других можно посмотреть в Справке или в комментариях при вводе функции.

Пример 9.

Найдём на отрезке [0; 1,2] корень уравнения , построив в табличном процессоре графики функций, соответствующих левой и правой частям равенства. Для этого:

— используя стандартные функции COS и КОРЕНЬ, построим таблицу значений функций для x, изменяющегося с шагом 0,1:

— по значениям диапазона A1:N3 строим графики функций, выбирая пункт меню Вставка, тип диаграммы Точечная, вид — Точечная с гладкими кривыми, добавим промежуточные линии сетки:

В результате построения графиков функций видно, что корень уравнения приблизительно равен 0,64.

Конспект урока

Информатика,

10 -  11 класс. Тема — Обработка информации в электронных таблицах

Перечень вопросов, рассматриваемых в теме: структура электронной таблицы и типы данных; встроенные функции; деловая графика; построение графиков функций; применение сортировки и фильтров; поиск решения и подбор параметра.

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

Основная литература по теме урока:

Л. Л. Босова, А. Ю. Босова. Информатика. Базовый уровень: учебник для 11 класса. — М.: БИНОМ. Лаборатория знаний, 2017 (с.5—62)

Открытые электронные ресурсы по теме:

http://lbz.ru/metodist/authors/informatika/3/eor11.php

http://kpolyakov.spb.ru/school/ege.htm

Теоретический материал для самостоятельного изучения

Объекты табличного процессора и их свойства

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

Наиболее распространёнными табличными процессорами являются Microsoft Excel и OpenOffice Calc.

После запуска программы Microsoft Excel на экране открываются два окна: окно табличного процессора и окно созданного в нём документа. Документ, создаваемый в табличном процессоре, называется рабочей книгой и по умолчанию получает имя Книга1. Вновь созданная рабочая книга состоит из трёх листов с именами Лист1, Лист2 и Лист3. Имена листов указываются на ярлычках. Пользователь может переименовать листы по своему усмотрению, добавить к книге новые листы или удалить ненужные. В окне рабочей книги отображается содержимое текущего листа. Рабочая область листа с электронной таблицей столбцами и строками разбита на ячейки. Столбцы обозначены буквами латинского алфавита, строки пронумерованы. Адрес ячейки образуется из имени столбца и номера строки, на пересечении которых она находится.

Ячейка — это наименьшая структурная единица электронной таблицы, которая образуется на пересечении столбца и строки.

Две и более ячейки листа электронной таблицы образуют диапазон ячеек. При задании адреса связного диапазона указывают его начальную и конечную ячейки — ячейки левого верхнего и правого нижнего углов (например, А1:А10). Чтобы указать адрес несвязного диапазона ячеек, надо через точку с запятой указать адреса его связных частей.

В таблице приведены объекты табличного процессора, а также их основные свойства, которые далее будут рассмотрены более подробно.