Учебник. А. Ю. Босова Москва бином. Лаборатория знаний 2016 11 класс Базовый уровень Учебник
 Скачать 4.61 Mb.
|
|
Глава 1. Обработка информации в эт верхней левой ячейке), при объединении будут утеряны. Поэто- му рекомендуется сначала объединить ячейки, а затем вводить данные. Редактирование и форматирование объединённой ячейки и её содержимого выполняются так же, как и обычной ячейки. Отменить объединение ячеек можно, выбрав эту ячейку и вос- пользовавшись инструментом объединения повторно. Предложите ещё один способ объединения ячеек. Если заполнено много столбцов (строк) таблицы, причём не- которые из них временно не нужны для работы, то их можно скрыть. Для этого следует выделить такие столбцы (строки) и от- дать команду Скрыть контекстного меню выделенного диапазона. Для отображения скрытых объектов нужно выполнить команду Показать. Попробуйте временно скрыть один из листов книги. Как вы это сделали? Если заполненные данными ячейки не помещаются на экране, а некоторые из них необходимо постоянно держать «перед глазами», то можно установить режим закрепления областей. В этом режиме при прокрутке электронной таблицы некоторые столбцы (строки) не будут исчезать с экрана. Для этого нужно выделить определён ную часть таблицы (табл. 1.3) и воспользоваться инструментом Закрепить области (вкладка Вид, группа Окно). Таблица 1.3 Закрепление областей Объект выделения Область закрепления Столбец Вертикальная область левее выделенного столбца Строка Горизонтальная область над выделенной строкой Ячейка Область левее и выше выделенной ячейки Команды Закрепить верхнюю строку и Закрепить первый столбец позволяют закрепить указанные объекты таблицы без их выделения. Для отказа от закрепления областей предназначена команда Снять закрепление областей. СамОе ГлаВнОе Редактирование книги состоит в добавлении в неё новых ли- стов, удалении, перемещении или копировании существующих листов. 27 §2 редактирование и форматирование в тП Редактирование электронной таблицы — вставка или удаление строк, столбцов или диапазонов ячеек. Основными операциями форматирования являются: форма- тирование данных, форматирование ячеек, изменение ширины столб цов и высоты строк. При форматировании данных сами данные не изменяются, изменяется лишь их внешний вид. Реальное значение данных можно увидеть в строке формул, сделав соответствующую ячейку текущей. При форматировании ячеек электронной таблицы можно уста- навливать: • границы ячейки, их цвет, тип линий и др.; • цвет фона ячейки, цвет и стиль узора, способы заливки и др.; • защиту ячейки, режим скрытия формул; • формат числовых данных (числовой формат); • значения свойств символов в ячейке: шрифт, начертание, раз- мер, подчеркивание, горизонтальное и вертикальное выравни- вание, ориентацию и др. В некоторых случаях удобно несколько ячеек, образующих связный диапазон, объединить в одну ячейку. Если заполнено много столбцов (строк) таблицы, причём не- которые из них временно не нужны для работы, то их можно скрыть. Если заполненные данными ячейки не помещаются на экране, а некоторые из них необходимо постоянно держать «перед глаза- ми», то можно установить режим закрепления областей. Вопросы и задания 1. Какие операции можно отнести к операциям редактирова- ния данных? К операциям редактирования книги? К опера- циям редактирования электронной таблицы? 2. Перечислите основные операции, выполняемые с листами книги. 3. Как вставить в электронную таблицу пустые строки (столбцы)? 4. Как удалить из электронной таблицы строки (столбцы)? 5. Как можно изменить размеры ячеек, столбцов, строк элект- ронной таблицы? Назовите несколько способов. 6. Для чего предназначено скрытие строк (столбцов)? Как это можно сделать? Как отобразить скрытые объекты? 28 Глава 1. Обработка информации в эт 7. Какие способы вызова окна Формат ячеек вам известны? 8. Дайте краткую характеристику форматам Общий, Числовой, Денежный, Дата, Процентный, Текстовый. Как их можно установить? 9. Исследуйте вкладку Выравнивание окна Формат ячеек. Зна- чения каких свойств данных в ячейках можно установить с её помощью? Каким ещё способом можно это сделать? 10. Введите в электронную таблицу необходимые данные и оформите их по образцу: 11. Значения каких свойств символов в ячейках можно уста- новить на вкладке Шрифт окна Формат ячеек? Каким ещё способом можно это сделать? 12. Значения каких свойств ячеек можно установить на вкладке Границы окна Формат ячеек? Каким ещё способом можно это сделать? 13. Значения каких свойств ячеек можно установить на вкладке Заливка окна Формат ячеек? Каким ещё способом можно это сделать? 14. Введите в электронную таблицу необходимые данные и оформите их по образцу (название цвета определяет цвет его шрифта и цвет фона ячейки справа от него): 15. Значения каких свойств ячеек можно установить на вклад- ке Защита окна Формат ячеек? Каким ещё способом можно это сделать? 16. Как можно скопировать формат ячейки на другие ячейки? 29 Встроенные функции и их использование §3 § 3 Встроенные функции и их использование 3.1. Общие сведения о функциях В любом табличном процессоре используются встроенные функ ции. Встроенная функция — это заранее написанная процедура пре образования данных. Всё многообразие встроенных в табличные процессоры функ- ций принято делить на категории по их назначению, выделяя среди них математические, статистические, логические, тексто- вые, финансовые и другие типы функций. Каждая встроенная функция имеет имя — как правило, это сокращённое название производимого ею действия. Функции вы- зываются с некоторыми аргументами и возвращают единственное значение — результат обработки. Аргументом функции может быть число, текст, выражение, ссылка на ячейку или диапазон ячеек, результат другой функ ции. Можно выделить функции: • с одним аргументом, например КОРЕНЬ; • с несколькими аргументами, количество которых фиксирова- но, например ОКРУГЛ; • с нефиксированным количеством аргументов, например МАКС; • с некоторыми необязательными аргументами, например РАНГ; • без аргументов, например ТДАТА. При использовании функции в формуле сначала указывается её имя, а затем в скобках указывается список аргументов через точку с запятой (табл. 1.4). Таблица 1.4 Примеры записи функций в Microsoft Excel Функция Запись в Microsoft Excel Квадратный корень КОРЕНЬ(А1) Округление числа до заданного коли- чества десятичных разрядов ОКРУГЛ(G13;2) Среднее значение СРЗНАЧ(А3:В10) Максимальное значение МАКС(А3:В10; С8:С12; М6) Текущие дата и время ТДАТА() 30 Глава 1. Обработка информации в эт Назначение каждой функции, наличие аргументов, их количество и тип можно посмотреть в Справке или в комментариях при вводе функции в формулу. Вставить функцию в формулу можно несколькими способами: 1) использовать кнопки категорий функций в группе Библиотека функций вкладки Формулы на ленте; 2) воспользоваться инструментом Вставить функцию в группе Библиотека функций или в строке формул; 3) ввести функцию непосредственно в ячейку или в поле Строка формул. Рассмотрим более подробно второй способ. Если щёлкнуть на кнопке Вставить функцию строки формул, то откроется окно Мастер функций (рис. 1.5), а в текущую ячей- ку автоматически вставится знак «=» (если в этой ячейке ввод формулы ещё не начинался). В окне Мастер функций в списке поля Категория можно выбрать нужную категорию, после чего в списке поля Выберите функцию выбрать нужную функцию. После выбора имени функции в текущую ячейку автоматиче- ски вставляется имя функции и пара круглых скобок, а также рис. 1.5. Окно Мастер функций 31 Встроенные функции и их использование §3 открывается окно Аргументы функции с полями для ввода аргу- ментов этой функции (рис. 1.6). Если функция имеет фиксированное количество аргументов, то в окне Аргументы функции сразу отображается соответствую- щее количество полей для их ввода. Если функция имеет нефик- сированное количество аргументов, то в окне сначала появляется несколько полей, а следующие поля появляются уже в процессе ввода аргументов. Если аргументом является число или текст, то его нужно вводить в поле с клавиатуры. Если аргументом является ссылка на ячейки, то её также можно ввести с клавиатуры, но лучше выделить соответствующие ячейки с помощью мыши. Для этого: 1) выберите кнопку Свернуть соответствующего поля для вво- да аргумента функции (после этого окно Аргументы функции изменит свой вид: в нём кроме строки заголовка останется только это поле, а вместо кнопки Свернуть появится кнопка Развернуть ); рис. 1.6. Окно Аргументы функции функции СУММ 32 Глава 1. Обработка информации в эт 2) выделите нужные ячейки (ссылки на них автоматически вста- вятся в соответствующее поле и в формулу); 3) выберите кнопку Развернуть (после этого окно Аргументы функ ции примет свой предыдущий вид); 4) при необходимости повторите шаги 1–3 для других аргумен- тов функции; 5) после ввода в поля всех нужных аргументов функции выбе- рите кнопку OК. Для некоторых функций Microsoft Excel автоматически пред- лагает первый аргумент. Например, для функции СУММ пред- лагается найти сумму чисел диапазона ячеек, заполненных чи- словыми данными, которые находятся над ячейкой с формулой (см. рис. 1.6) или слева от неё, если верхний диапазон ячеек пуст. Это предложение можно принять (если оно соответствует плану проводимых вычислений) или ввести вместо автоматически предложенного аргумента другой. Два других способа вставки функции в формулу исследуйте са мостоятельно. Назовите их основные отличия друг от друга. Что у них общего? Какой из способов будете применять вы? В электронных таблицах используется большое количество встроенных функций. Познакомимся более подробно с теми из них, которые могут пригодиться вам в учебной и исследователь- ской деятельности, а также в повседневной жизни. 3.2. математические и статистические функции Для решения математических задач (решения уравнений, по- строения графиков функций) вам могут быть полезны функции, представленные в таблице 1.5. Здесь же представлены некоторые из статистических функций, позволяющих автоматизировать ста- тистическую обработку данных. С их помощью можно вычислить наименьшее значение, подсчитать количество ячеек, содержащих заданную информацию, и т. д. Рассмотрим более детально работу статистической функции РАНГ, имеющую формат: РАНГ(число; ссылка на список; [порядок]) Здесь: • число — это число, для которого определяется ранг (поря- док); 33 Встроенные функции и их использование §3 Таблица 1.5 н еко торые мат ематические и с татис тические функции Функция Количество аргументов Запись Результат ABS(число) 1 ABS(F5) Модуль (абсолютная величина) числа SIN(число) 1 SIN(D4) Синус числа (угла в радианах) РАДИАНЫ(число) 1 РАДИАНЫ(А10) Перевод из градусной меры угла в радианную ГРАДУСЫ(число) 1 ГРАДУСЫ(С6) Перевод радианной меры угла в градусы ПИ() 0 ПИ() Значение числа π СТЕПЕНЬ(число; степень) 2 СТЕПЕНЬ(А2; 5) Число, возведённое в степень 34 Глава 1. Обработка информации в эт Функция Количество аргументов Запись Результат СУММ(число1; [число2]; …) От 1 до 255; все, кроме первого, необязательные СУММ(А3:В10) Сумма чисел, указанных в скобках ОКРУГЛ(число; число_разрядов) 2 ОКРУГЛ(G13; 2) Число, округлённое до заданного количества десятичных разрядов СЧЁТ(значение1; [значение2]; …) От 1 до 255; все, кроме первого, необязательные СЧЁТ(А3:В10; G13) Количество чисел в указанных ячейках МИН(число1; [число2]; …) От 1 до 255; все, кроме первого, необязательные МИН(А3:В10; G13:G23) Наименьшее среди указанных в скобках чисел РАНГ(число; ссылка на список; [порядок]) 3; третий необязательный РАНГ(А1; $А$1:$А$5; 1) Ранг числа в списке чисел Окончание табл. 1.5 35 Встроенные функции и их использование §3 • ссылка на список — ссылка на список, которому принад- лежит число (нечисловые значения в ссылке игнорируются); • порядок — способ упорядочения значений списка: — 0 или отсутствие параметра — определяет ранг (пози- цию, место) числа в списке так, как если бы список был отсортирован в порядке убывания (т. е. максимальному значению присваивается ранг равный 1, чуть меньшему числу — ранг 2 и т. д.); — число, не равное 0, — определяет ранг числа так, как если бы список сортировался в порядке возрастания (т. е. минимальному числу присваивается ранг 1, чуть большему числу — ранг 2 и т. д.). Функция РАНГ присваивает повторяющимся числам одина- ковый ранг. При этом наличие повторяющихся чисел влияет на ранг последующих чисел. В ячейку В1 введена и скопирована в В2:В6 одна из двух следую щих формул: 1) =РАНГ(A1; $A$1:$A$6; 1); 2) =РАНГ(A1; $A$1:$A$6; 0). По какой из формул представлены результаты вычислений в столб це В? Как вы можете объяснить отсутствие числа 2 среди значений яче ек диапазона С1:С6, если это — результаты вычислений по другой из приведённых выше формул? A B C 1 45 5 1 2 12 4 3 3 8 3 4 4 45 5 1 5 6 2 5 6 3 1 6 3.3. логические функции Функция, результатом которой является ИСТИНА или ЛОЖЬ, назы вается логической. К категории логических относятся функции ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ. 36 Глава 1. Обработка информации в эт Функции И, ИЛИ, НЕ позволяют создавать составные логи- ческие выражения. Формат этих функций: И(логическое_значение1; [логическое_значение2]; ...) ИЛИ(логическое_значение1; [логическое_значение2]; ...) НЕ(логическое_значение) Аргументами функций И, ИЛИ, НЕ могут быть логические выражения или ссылки на ячейки, содержащие логические зна- чения. Функция ЕСЛИ имеет формат: ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) Значение этой функции определяется так: • если лог_выражение имеет значение ИСТИНА, то значе- ние функции равно значению выражения значение_если_ истина; • если лог_выражение имеет значение ЛОЖЬ, то значение функ ции равно значению выражения значение_если_ложь. Табличные процессоры имеют и такие функции, которые вы- числяют сумму, среднее арифметическое, количество не всех зна- чений из диапазонов ячеек, а только тех, которые удовлетворяют определённому условию: • функция СУММЕСЛИ вычисляет сумму тех чисел из указан- ного диапазона, которые удовлетворяют заданному условию; • функция СРЗНАЧЕСЛИ вычисляет среднее арифметическое тех чисел из указанного диапазона, которые удовлетворяют заданному условию; • функция СЧЁТЕСЛИ подсчитывает количество ячеек из ука- занного диапазона, содержимое которых удовлетворяет задан- ному условию. Пример 1. Выясним, сколько решений имеет логическое урав- нение ((x 1 →→ x 2 ) → (x 3 → x 4 )) = 1. Преобразуем исходное уравнение, выразив импликацию через инверсию и дизъюнкцию: ( ) ( ) ( ) x x x x x x x x 1 2 3 4 1 2 3 4 1 & Запишем формулу для вычисления логического выражения с помощью логических функций Microsoft Excel: =ИЛИ(И(X1;НЕ(X2)); НЕ(X3); X4). Внесём данные в таблицу и выполним расчёты — рис. 1.7. 37 Встроенные функции и их использование §3 рис. 1.7. Решение логического уравнения (пример 1) Итак, исходное уравнение имеет 13 решений — столько раз встречается значение ИСТИНА в диапазоне E2:E17. Для подсчёта этого значения можно воспользоваться функцией СЧЁТЕСЛИ. Вспомните другой способ решения этого уравнения. 3.4. финансовые функции Финансовые функции используются для вычисления размеров выплат при погашении кредитов, банковских процентов на вкла- ды, для определения процентной ставки и др. Рассмотрим несколько финансовых функций, которыми полез- но уметь пользоваться каждому человеку, планирующему взять в банке кредит 1) или сделать вклад 2) . Аргументами этих функций являются: 1) Кредит — это ссуда, предоставленная кредитором (в данном случае банком) заёмщику под определённые проценты за пользование деньгами. 2) Вклад — денежные средства, внесённые физическим или юридическим ли- цом в финансовое учреждение на хранение, в рост или для участия в по- лучении прибыли. 38 Глава 1. Обработка информации в эт • ставка — процентная ставка за период; • плт — выплата, производимая в каждый период (месяц, квар- тал, год и т. п.); • пс — приведённая (нынешняя) стоимость инвестиции; • кпер — общее число периодов платежей по кредиту; • бс — будущая стоимость инвестиции; • тип — число 0, если оплата в конце периода; число 1, если оплата в начале периода (по умолчанию — 0). Пример 2. Пусть ставка кредита в некотором банке составляет 18% годовых. Клиент хочет взять кредит на сумму 100 000 руб. и может выплачивать банку по 4000 руб. ежемесячно. Нужно определить, за сколько периодов клиент сможет погасить этот кредит. Функция КПЕР(ставка; плт; пс; [бс]; [тип]) возвращает коли- чество периодов платежей для инвестиции на основе периодиче- ских постоянных выплат и постоянной процентной ставки. Обязательные аргументы функции: • ставка — годовая ставка в процентах, разделённая на ко- личество периодов платежей за год (в нашем примере это 18%/12); • плт — сумма, которую клиент ежемесячно должен возвра- щать банку (в нашем примере это –4000, т. к. эти деньги отдаются); • пс — размер кредита (в нашем примере это 100 000). Формула для вычисления количества периодов выплат для погашения взятого кредита будет иметь вид: =КПЕР(18%/12; –4000; 100000). Получаем приблизительно 32 периода (месяца), т. е. более 2,5 лет. Пример 3. Выясним, на какую сумму клиент может взять кредит, если ставка 19% годовых, а выплачивать он может по 12 000 руб. на протяжении двух лет (24 периода). Функция ПС(ставка; кпер; плт; [бс]; [тип]) возвращает при- ведённую (к текущему моменту) стоимость инвестиции, представ- ляющую собой общую сумму, которая на данный момент равно- ценна ряду будущих выплат. Обязательные аргументы функции: • ставка (19%/12); • кпер — общее количество периодов выплаты платежей по кредиту (24); • плт (–12 000). 39 Встроенные функции и их использование § 3 Формула для вычисления размера кредита будет иметь вид: =ПС(19%/12; 24; –12000). Получаем приблизительно 238 054 руб. Пример 4. Пусть клиент хочет взять кредит 100 000 руб. на 2 года. При этом выплачивать он может по 5000 руб. ежемесяч- но. Может ли он воспользоваться предложением банка, ставка по кредитам в котором составляет 20%? Функция СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) вычисляет процентную ставку за период (а не за год). Обязательные аргументы функции: • кпер (24); • плт (–5000); • пс (100 000). Формула для вычисления ставки будет иметь вид: =СТАВКА(24; –5000; 100000). В результате вычислений получаем процентную ставку за ме- сяц 1,51308%. Соответственно, процентная ставка за год составит 18,157% (1,51308 ⋅ 12). Таким образом, клиенту не рекомендуется брать кредит в бан- ке, ставка по кредитам в котором составляет 20%. Пример 5. Клиент хочет сделать вклад на 3 года на сумму 300 000 руб. под 11% годовых с ежемесячным начислением про- центов. Выясним, какую сумму он получит по окончании срока вклада. Функция БС(ставка; кпер; плт; [пс]; [тип]) возвращает буду- щую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки. Иначе говоря, с её помощью можно вычислить сумму, которую выплатят клиенту за вклад под определённые проценты по окончании срока вклада. Аргументы функции: • ставка — годовая ставка в процентах, разделённая на количе- ство периодов начисления процентов за год (в нашем примере это 11%/12); • кпер — количество периодов начисления процентов (3 ⋅ 12 = 36); • плт — сумма, которая добавляется к вкладу каждый период времени: 0 или отрицательное число (в нашем примере это 0, т. к. пополнение вклада клиентом не предусмотрено); • пс — начальная сумма вклада (в нашем примере это 300 000). |