информатика. Литература по теме Тема Алгоритмы и программы Вопрос Понятие алгоритма
 Скачать 7.84 Mb.
|
|
Тема 11. Технологии использования электронных таблиц Цель и задачи: ознакомиться с возможностями табличного процессора, освоить навыки проведения расчетных операций. Вопросы темы: 1. Электронные таблицы. 2. Структура таблицы MS Excel. 3. Ввод данных в таблицу, копирование и перемещение. 4. Проведение математических вычислений в таблицах MS Excel. 5. Примеры решения вычислительных задач. 6. Логические переменные, функции и выражения. 7. Использование диаграмм и графиков в MS Exce. Вопрос 1. Электронные таблицы. В практике обработки информации часто возникает необходимость представления данных в виде таблиц. Для работы с подобными данными созданы специальные компьютерные программы, которые называются электронными таблицами или табличными процессорами. Электронные таблицы – это класс компьютерных программ, позволяющих хранить данные в табличном виде и проводить математические, логические, финансовые и другие операции над этими данными. Существует целое множество подобных компьютерных программ различных производителей, например, SuperCalc, Abacus, Lotus 1-2-3, OpenOffice.org, Quattro Pro, MS Excel и др. Последняя из упомянутых программ, входящая в пакет MS Office, получила наибольшее распространение в нашей стране. Поэтому далее рассмотрим именно эту программу. Интерфейс табличного процессора MS Excel 2016. Запуск программы MS Excel из операционной системы MS Windows производится традиционным путем: или с помощью ярлыка на рабочем столе (при его наличии), или из меню Пуск (соответствующий значок в разделе Microsoft Office). В обоих случаях нужно сделать двойной щелчок курсором по найденному значку. Откроется стартовое окно программы (рис. 47), из которого можно перейти к просмотру имеющегося документа или созданию нового.  Рис. 47. Стартовое окно программы MS Excel 2016 Как и все программы пакета MS Office, табличный процессор MS Excel имеет унифицированный интерфейс (рис. 48).  Рис. 48. Вид главного окна программы MS Excel 2016 Интерфейс MS Excel включает следующие разделы: 1. Строка заголовка содержит (в центре) имя открытого файла и название программы. В левой части строки находится Панель быстрого доступа для вызова часто используемых команд, обозначенных своими пиктограммами (Сохранить, Отменить, Создать и пр.). В правой части строки расположены три кнопки управления окном (Свернуть, Развернуть, Закрыть). 2. Кнопка Файл открывает меню работы с файлом: Создать, Открыть, Сохранить, Печать, Экспорт и пр. Кроме того, меню открывает доступ и к другим разделам программы: Сведения (о файле), Учетная запись, Параметры. 3. Лента – это панель инструментов, состав которых определяется выбранной вкладкой (Главная, Вставка, Дизайн, …, Вид). 4. Cтрока формул, в левой части которой находится Адресноеполе. 5. Рабочая область (Окно документа) занимает основную часть экрана. В ней создаются, просматриваются и редактируются табличные документы. 6. Строка состояния (самая нижняя) показывает состояние процесса создания документа (ввод, готово). В правой части строки находятся кнопки режимов просмотра документа (обычный, разметка страницы, страничный), а также ползунок плавного изменения масштаба показа документа (рис. 49). Кнопки со знаком «минус» и «плюс» позволяют изменить масштаб ступенчато (по 10 %).  Рис. 49. Кнопки режимов просмотра и средства изменения масштаба Файл программы MS Excel называется книгой. Она содержит целый ряд отдельных листов, на которых располагаются таблицы, диаграммы, графики и рисунки. В каждый момент времени активным является лишь один лист, на котором проводятся операции с данными таблиц. Переключение между листами осуществляется с помощью ярлычков, находящихся внизу рабочего поля (рис. 50). Кнопкой со знаком «плюс» можно добавить новый лист в книге.  Рис. 50. Ярлычки переключения листов и строка состояния Вопрос 2. Структура таблицы MS Excel. Таблица MS Excel состоит из строк и столбцов, на пересечении которых располагаются ячейки. В них заносятся данные различного типа, а также помещаются результаты их обработки. Строки таблицы пронумерованы от 1 до 1048576 , а столбцы обозначены одной, двумя или тремя латинскими буквами: A, B, C, …, Z, AA, AB, AC, …,AZ, AAA, AAB, AAC,…, ZXF (всего 16384 столбца). Каждая ячейка имеет свой адрес, состоящий из имени столбца и номера строки, например, А1 или В55. Адрес ячейки отображается в адресном поле в строке формул. Выделенная группа соседних ячеек называется диапазоном. Он обозначается парой имен ячеек, находящихся в противоположных углах группы, например А1:С6. Диапазону ячеек можно присвоить имя (состоящее из букв и чисел), которое также будет показано в адресном поле при выделении диапазона (рис. 51).  Рис. 51. Диапазон ячеек А1:С6 Каждой ячейке (или группе ячеек) таблицы пользователем назначается определенный формат в соответствии с типом хранимых данных: текстовый, числовой, денежный и т.д. По умолчанию всем ячейкам таблицы предварительно установлен формат Общий, который позволяет вводить в ячейки текстовые и числовые данные. При этом программа сама определяет тип вводимых данных и выравнивает их в ячейке по-разному: текстовые по левому краю, а числовые по правому. Перечень возможных форматов ячеек можно увидеть (и установить) в списке, раскрывающемся из поля Формат в группе Число вкладки Главная (рис. 52).  Рис. 52. Список доступных форматов ячеек Чтобы назначить ячейке определенный формат, ее нужно выделить. Для этого следует щелкнуть в ней курсором. Ячейка становится активной: она выделена рамкой, ее адрес показан в адресном поле, а номер ее строки и имя столбца подсвечены. После этого следует раскрыть список форматов (см. рис. 52) и щелкнуть курсором нужный формат. Можно назначить определенный формат сразу целой группе ячеек (диапазону), если предварительно выделить эту группу. Для выделения групп ячеек используются следующие приемы: 1. Для группы ячеек в одной строке или столбце – провести по ним с нажатой левой кнопкой мыши. 2. Для выделения диапазона – щелкнуть курсором любую угловую ячейку диапазона и, не отпуская кнопки мыши, вести курсор по диагонали до противоположного угла диапазона. Затем отпустить кнопку мыши. Можно также сначала щелкнуть одну угловую ячейку, а затем щелкнуть ячейку в противоположном углу диапазона, удерживая нажатой клавишу Shift. 3. Чтобы выделить не соседние ячейки, расположенные в одной строке или одном столбце, по ним надо щелкнуть, удерживая клавишу Ctrl. 4. Для выделения целой строки нужно щелкнуть ее номер (слева от таблицы). 5. Для выделения целого столбца – щелкнуть его имя (вверху таблицы). 6. Для выделения всего листа – щелкнуть кнопку на пересечении столбца с номерами строк и строки с именами столбцов (на рис. 53 кнопка помечена красным цветом).  Рис. 53. Кнопка выделения всего листа Структуру созданной таблицы можно изменять, добавляя новые или удаляя имеющиеся строки, столбцы и ячейки. Для добавления новых элементов таблицы используется кнопка Вставить из группы Ячейки (рис. 54). При выделенном столбце действие кнопки Вставить приводит к добавлению нового столбца слева от выделенного. А при выделенной строке добавится новая строка сверху от выделенной.  Рис. 54. Группа кнопок Ячейки Кнопкой Удалить из той же группы Ячейки можно удалять выделенные строки, столбцы и ячейки. Кнопка Формат раскрывает меню установки параметров ячейки (размеры, видимость, защита). Изменить ширину столбца или высоту строки можно курсором, щелкнув на границе в строке имен столбца или в столбце номеров строк, и с нажатой кнопкой мыши «подвинуть» границу на необходимую ширину или высоту. В программе имеются инструменты, позволяющие объединить соседние ячейки. Для этого следует выделить объединяемую группу ячеек и использовать кнопку Объединить и поместить в центре из группы Выравнивание вкладки Главная (рис. 55).  Рис. 55. Кнопка Объединить и поместить в центре Вопрос 3. Ввод данных в таблицу, копирование и перемещение. Ввод текстовых, числовых и временных данных в ячейки таблицы можно произвести с клавиатуры или путем вставки через буфер обмена. Кроме того, целый ряд объектов (рисунки, диаграммы, графики, гиперссылки и пр.) вставляются средствами, представленными на вкладке Вставка. Ввод данных с клавиатуры производится в следующем порядке: 1. Встать в нужную ячейку (т.е. щелкнуть в ней курсором). 2. Набрать на клавиатуре подлежащие вводу данные (текстовые, числовые, временные). 3. Завершить ввод данных одним из следующих способов: а) Нажать клавишу Enter. Курсор при этом переместится на одну строку вниз. б) Нажать клавишу Табуляция (Tab). Курсор переместится на один столбец вправо. в) Нажать клавишу-стрелку. Курсор передвинется на одну ячейку по направлению стрелки. г) Щелкнуть курсором в любой другой ячейке листа. д) Щелкнуть курсором кнопку Ввод («галочка») в строке формул. Курсор останется на месте. е) Щелкнуть черную квадратную точку в правом нижнем углу ячейки (маркер заполнения). Курсор останется на месте. Если при вводе данные не умещаются в пределах ячейки, они занимают следующую ячейку. Но при заполнении данными следующей ячейки не уместившаяся часть первой ячейки не будет показываться. Чтобы показывать данные целиком, надо просто увеличить ширину первой ячейки описанным выше способом. Можно также сделать двойной щелчок курсором на границе столбцов указанных ячеек. Для копирования содержимого ячейки ее следует выделить и воспользоваться кнопкой Копировать в группе Буфер обмена вкладки Главная. Можно также воспользоваться известной комбинацией клавиш Ctrl+C. Скопированные данные можно вставить в любую другую ячейку, если выделить ее курсором и щелкнуть кнопку Вставить из группы Буфер обмена или воспользоваться клавишной комбинацией Ctrl+V. Причем операцию вставки можно проводить многократно в различные ячейки и даже на другие листы. Существует способ копирования данных в соседние ячейки с помощью мыши. Для этого следует выделить исходную ячейку (с данными), «подцепить» курсором маркер заполнения (черную квадратную точку в правом нижнем углу ячейки) и не отпуская нажатой кнопки мыши, «протянуть» курсор по направлению копирования (вниз, вверх, направо или налево) на любое число ячеек (рис. 56).  Рис. 56. Копирование содержимого ячейки с помощью маркера заполнения Для перемещения данных из одной ячейки в другую следует выделить исходную ячейку, щелкнуть кнопку Вырезать (пиктограмма Ножницы), а затем, встав курсором в новую ячейку, щелкнуть кнопку Вставить. В отличие от операции копирования, вырезанные данные можно вставить в ячейку лишь один раз. Для удаления данных из выделенной ячейки достаточно нажать клавишу Delete. Также можно воспользоваться кнопкой Очистить из группы Редактирование вкладки Главная. Следует заметить, что практически все описанные в данном разделе команды представлены также и в контекстных меню (вызываемых правой кнопкой мыши). Практические приемы ввода данных в ячейки таблицы. Программа MS Excel имеет инструменты для оперативного ввода в ячейки повторяющихся данных. Рассмотрим их на практических примерах. Пример 1. 1. Введем в произвольную ячейку число 1, а в соседнюю в той же строке – число 2. 2. Выделим одновременно обе ячейки. 3. Подцепим курсором маркер заполнения и протащим курсор на соседние ячейки строки. 4. После отпускания кнопки мыши ячейки будут заполнены числовым рядом: 3, 4, 5 и т.д. 5. Можно также далее щелкнув появившуюся кнопку с пиктограммой раскрыть меню и выбрать требуемое действие: Копировать ячейки, Заполнить, Заполнить только форматы и Заполнить только значения (рис. 57). Описанная операция работает и при вертикальном расположении соседних ячеек (вдоль столбца).  Рис. 57. Заполнение ячеек вдоль строки Начальное значение в первой ячейке может быть любым. Так же, как и разность между величинами в первой и второй ячейках. Программа определяет разницу значений и формирует арифметическую прогрессию (рис. 58).  Рис. 58. Формирование арифметической прогрессии Пример 2. 1. Введем в произвольную ячейку слово «январь». 2. Подцепим курсором маркер заполнения и протащим его вдоль строки или вдоль столбца. Получим последовательность месяцев (рис. 59).  Рис. 59. Автозаполнение ячеек строки месяцами года 3. Введем в произвольную ячейку слово «Понедельник». 4. Повторим действие, описанное в п. 2. Получим последовательность дней недели (рис. 60).  Рис. 60. Формирование последовательности дней недели Практический пример создания таблицы. Создадим простую таблицу, содержащую сведения о кадровом составе некоторой организации. Каждый столбец создаваемой таблицы должен иметь заголовок: №, Фамилия И.О., Дата рождения и т.д. При этом всем ячейкам одного столбца, в которых хранятся вводимые данные, следует задать определенный формат: для порядкового номера – числовой; для Фамилии – текстовый; для даты рождения – краткая дата; для оклада – денежный; телефон – текстовый; E-mail – общий. 1. На листе Excel заполним фрагмент создаваемой таблицы в соответствии с рис. 61.  Рис. 61. Таблица кадрового состава на листе MS Excel 2. Выделим первый столбец таблицы и установим выравнивание По центру одноименной кнопкой в группе Выравнивание вкладки Главная. 3. Выделим первую строку и установим начертание шрифта Полужирный одноименной кнопкой в группе Шрифт. 4. Выделим целиком столбец Е и кнопкой Вставить добавим новый столбец. Назовем его «Дата приема на работу». Заполним новые данные для всех сотрудников. 5. Выделим целиком строку 4 и кнопкой Вставить добавим новую строку. Введем данные на нового сотрудника. 6. Откорректируем нумерацию в столбце №. 7. В строке 9 выделим столбцы A, B, C, D, Е и объединим ячейки кнопкой Объединить и поместить в центре. 8. В объединенной группе ячеек введем текст «Фонд заработной платы». 9. Поместим курсор в ячейку F9 и щелкнем кнопку Сумма в группе Редактирование (вкладка Главная). Завершим операцию суммирования кнопкой Ввод («галочка») в строке формул. В ячейке появится значение суммы окладов всех сотрудников – фонд заработной платы. Окончательный вид таблицы представлен на рис. 62.  Рис. 62. Окончательный вид таблицы Вопрос 4. Проведение математических вычислений в таблицах MS Excel. Табличный процессор MS Excel позволяет проводить математические операции над данными, хранящимися в таблицах. При этом роль переменных в математических выражениях (a, b, c,.., x, y, z и т.д.) выполняют адреса ячеек, в которых хранятся значения этих переменных. Например, если в ячейке А1 хранится значение переменной x, а в ячейке В1 хранится значение переменной y, то выражение для вычисления суммы z = x+y в Excel, будет выглядеть так: =A1+B1. А результат вычисления будет помещен в ту ячейку, в которой записана приведенная формула. Заметим, что любое математическое (а также логическое) выражение начинается со знака равно. Вводить математические и логические выражения можно прямо в ячейку или в строку формул, т.к. вводимые в ячейку выражения (и данные) дублируются в строке формул. Проделаем упражнение по проведению в таблице Excel элементарных арифметических операций. 1. Создадим таблицу по образцу рис. 63.  Рис. 63. Таблица арифметической операции Сложение 2. Заполним последующие строки таблицы произвольными значениями переменных х и у для вычитания, умножения, деления и возведения в степень. В ячейки столбца D введем соответствующие формулы арифметических действий. 3. Результат показан на рис. 64.  Рис. 64. Таблица арифметических операций Обратите внимание на содержание строки формул. Если в активной ячейке хранятся числовые или текстовые значения, они дублируются в строке формул. А если в активной ячейке хранится математическое выражение (начинающееся со знака равно), то в строке формул показано это выражение, а в самой ячейке (не обязательно активной) показан результат его вычисления. Копирование формул. В таблицах MS Excel можно копировать не только данные, но и хранящиеся в ячейках формулы. Действия по копированию формул такие же, как и по копированию данных (см.: Тема 11, Вопрос 3): либо кнопками Копировать – Вставить из группы Буфер обмена, либо движением курсора, подцепив маркер заполнения. Однако при копировании и переносе формул в другие ячейки имеется одна особенность: в процессе данной операции Excel самостоятельно (без участия пользователя) изменяет в формулах адреса исходных ячеек в соответствии с величиной сдвига по строкам и столбцам. Например, если в приведенном выше примере (см. рис. 63) скопировать формулу сложения из ячейки D2 и вставить в ячейку D3 (сдвиг на одну строку), получим вместо исходного выражения =A2+C2 новую формулу =A3+C3. То есть Excel самостоятельно изменил номер строки во вставляемой формуле. И это оправданно: для вычисления суммы использовались данные из новой строки. Иногда такая особенность поведения очень полезна, например, если нужно в таблице (рис. 62) посчитать возраст сотрудников. Тогда формулу вычисления возраста достаточно вставить лишь в строку первого сотрудника, а затем скопировать ее для всех остальных. При этом в каждой следующей строке формула будет использовать дату рождения следующего сотрудника. Аналогичное явление можно наблюдать и при сдвиге по столбцам. Например, вставим ту же скопированную формулу в ячейку Е3. Новое выражение будет иметь вид =В3+D3. То есть изменились и имена исходных столбцов, и номер исходной строки в соответствии со сдвигом. И в этом случае смысл вычисления потерян, поскольку в измененных адресах ячеек хранятся совсем другие данные. Поэтому в ряде случаев необходимо зафиксировать исходные адреса ячеек в копируемых и переносимых формулах. Например, для создания таблицы умножения следует зафиксировать имя столбца (левого), содержащего значения первого сомножителя, и номер строки (верхней), содержащей значения второго сомножителя. Для фиксации адреса в формулу вставляется знак $ перед именем того столбца или номером той строки, которые в формуле не должны изменяться. Так, для создаваемой таблицы умножения формула для первой (результатной) ячейки таблицы (1х1) будет выглядеть так: =$A2*B$1 (рис. 65).  Рис. 65. Фиксация адресов ячеек в формуле таблицы умножения Далее следует завершить ввод формулы и протянуть курсором маркер заполнения по всем ячейкам таблицы умножения вниз и вправо. Адреса ячеек, содержащие знак $, называются абсолютными ссылками в отличие от обычных адресов, которые называются относительными ссылками. Встроенные или стандартные функции MS Excel. Табличный процессор MS Excel имеет огромное количество готовых стандартных функций (математических, логических, финансовых и др.), которые можно использовать при проведении вычислений. Чтобы вызвать необходимую стандартную функцию следует установить курсор в ту ячейку, куда будет помещен результат вычисления, и щелкнуть кнопку fx (Вставить функцию) в строке формул. При этом открывается диалоговое окно Вставка функции (рис. 66).  Рис. 66. Диалоговое окно Вставка функции Все встроенные функции распределены по категориям: математические, статистические, финансовые и т.д. (см. рис. 66). После выбора категории в одноименном поле окна Вставка функции (в рассматриваемом примере выбрали категорию Математические), в разделе Выберите функцию открывается список имеющихся функций выбранной категории. После выбора требуемой функции (например, десятичный логарифм) нажатие ОК открывает окно Аргументы функции, в котором следует ввести необходимые значения аргументов (рис. 67).  Рис. 67. Диалоговое окно Аргументы функции После ввода значений аргументов функции результат вычисления показывается в окне Аргументы функции (см. рис. 67), а после закрытия окна кнопкой ОК получаем результат вычисления в активной ячейке. Исчисление временных интервалов в Excel. Вычисление значений временных интервалов в Excel имеет некоторые особенности. Так, даты хранятся в виде последовательных номеров. Например, день 1 января 1900 г. имеет номер 1, а 1 января 2017 г. – 42736, интервал между этими датами составляет 42736 дней. При вводе в ячейку таблицы значения некоторой даты (в формате даты), Excel переводит ее в число (дней), равное разности между вводимой датой и 01.01.1900 г. То есть вычисляет число дней, прошедших с 01.01.1900 г. до вводимой даты, и хранит в памяти именно это число. Его можно увидеть, если ячейке, содержащей дату, присвоить числовой формат. Чтобы вычислить возраст человека (в днях) в Excel надо из сегодняшней даты вычесть дату рождения. Если полученный результат поделить на число дней в году, получим значение возраста в годах. Точно также вычисляют стаж работы и другие временные интервалы. Значение сегодняшней даты дает встроенная функция Сегодня из категории Дата и время в диалоговом окне Вставка функции (рис. 68).  Рис. 68. Функция Сегодня в категории Дата и время Продемонстрируем использование функции Сегодня на примере вычисления возраста сотрудников во вновь созданной таблице, содержащей следующие столбцы: №, ФИО (Фамилия и инициалы), Дата рождения, Возраст. Ячейкам столбца Возраст назначим формат Числовой. Выполним следующие действия: 1. Щелкнем курсором в ячейке первого сотрудника в столбце «Возраст», чтобы сделать ее активной. 2. Щелкнем кнопку fx (Вставить функцию) в строке формул. 3. В открывшемся диалоговом окне Вставка функции (см. рис. 68) выберем категорию Дата и время, а затем функцию Сегодня и щелкнем кнопку ОК. 4. Заметим, что функция Сегодня не имеет аргументов, что подтверждается содержимым диалогового окна Аргументы функции, в котором нужно просто подтвердить ввод, щелкнув ОК (рис. 69).  Рис. 69. Подтверждение ввода функции Сегодня в диалоговом окне «Аргументы функции» 5. В активной ячейке, а также в строке формул появится запись выражения =Сегодня(). Щелкнем курсором в строке формул после скобок. Дальнейшие действия по созданию окончательного математического выражения будем производить в строке формул. 6. Вставим знак «минус» с клавиатуры и щелкнем в ячейке, соответствующей дате рождения первого сотрудника. В строке формул появится следующая запись выражения: =Сегодня()-С2. Это выражение дает возраст сотрудника в днях. 7. Полученный результат надо поделить на число дней в году (365,25 с учетом високосного года). Окончательное математическое выражение должно иметь следующий вид: =(Сегодня()-С2)/365,25. 8. Завершим ввод щелкнув кнопку Ввод (галочка) в строке формул. В ячейке D2 появится окончательный результат – возраст сотрудника в годах. Убедитесь, что ячейкам столбца Возраст назначен формат Числовой. 9. Чтобы заполнить все ячейки столбца Возраст надо скопировать формулу вычисления возраста из активной ячейки D2 и вставить ее во все остальные ячейки столбца. Для этого следует подцепить курсором и протащить вниз до конца столбца маркер заполнения активной ячейки D2 (рис. 70).  Рис. 70. Таблица сотрудников с вычислением возраста Точно таким же способом вычисляется и стаж работы сотрудника, исходя из даты поступления на работу. Вопрос 5. Примеры решения вычислительных задач. Расчет заработной платы. Рассмотрим метод расчета заработной платы при сдельной системе оплаты труда. Пусть R работников выполняют одинаковую работу по изготовлению продукции. Плановое задание – Р изделий в месяц. За эту работу выплачивается базовая ставка зарплаты В рублей. Конечная оплата Z производится пропорционально фактической выработке V изделий за месяц, т.е. Z = B*V/P. Подоходный налог N составляет 13 % от начисленной зарплаты, поэтому на руки работник получает (Z – N) рублей. 1. Создадим «заготовку» таблицы на листе Excel, указав произвольные исходные данные: Р = 300 шт., В = 30000 р. (рис. 71).  Рис. 71. Таблица расчета зарплаты 2. В активной ячейке D4 введем формулу для вычисления зарплаты: =$D$1*C4/$B$1 и завершим ввод кнопкой-галочкой в строке формул. Следует заметить, что в формуле использован знак $ для фиксации адресов ячеек, которые не должны изменяться при копировании. 3. Подцепим курсором маркер заполнения активной ячейки и протянем его вниз до конца столбца таблицы. 4. Поместим курсор в ячейку Е4 и введем формулу вычисления подоходного налога =D4*13 %. Завершим ввод. 5. Маркером заполнения скопируем введенную формулу в остальные ячейки столбца. 6. Поместим курсор в ячейку F4 и введем формулу вычисления суммы к выдаче: =D4–E4. Завершим ввод и протянем маркер заполнения до конца столбца. Округление числовых значений. Рассмотрим пример использования встроенных математических функций, позволяющих округлять числовые значения до ближайших величин вверх или вниз. Обратимся еще раз к созданной ранее таблице сотрудников и произведем округление вниз возраста. Заметим, что величина возраста в таблице выражена нецелым числом, полученным в результате расчета. Иногда в некоторых организациях (например, в медицинских учреждениях) учитывается возраст «полных лет». Для его нахождения мы и воспользуемся встроенной функцией. 1. Добавим новый столбец в ранее созданную таблицу (см. рис. 70, вставив его после столбца Возраст и назовем Полных лет (формат числовой). 2. Установим курсор в ячейку первого сотрудника в новом столбце. 3. Щелкнем курсором кнопку fx (Вставить функцию) в строке формул. 4. В открывшемся диалоговом окне Вставка функции выберем категорию Математические, а в ней функцию ОкруглВниз и щелкнем кнопку ОК. 5. В диалоговом окне Аргументы функции курсор находится в поле Число. Чтобы ввести округляемое число, щелкнем курсором в ячейке таблицы, содержащей возраст первого сотрудника. 6. Перенесем курсор в поле Число разрядов и введем 0 (ноль). Тем самым мы округляем до ближайшего целого числа. 7. Щелкнем кнопку ОК, чтобы закрыть диалоговое окно. В активной ячейке таблицы появится результат округления (рис. 72).  Рис. 72. Результат округления дробного числа до целого 8. Подцепим курсором маркер заполнения активной ячейки и протянем его вниз до конца столбца таблицы. Расчет платежей по кредиту. Часто возникает задача рассчитать сумму возврата и ежемесячных платежей по взятому в банке кредиту, исходя из годовой процентной ставки. Рассмотрим эту задачу сначала на основе формулы простых процентов. Если обозначить S сумму кредита, Pr – годовая процентная ставка (годовой процент), Sвоз – сумму возврата и T – срок кредита, то можно написать следующее очевидное равенство Sвоз= S + S*Pr*T, где * означает знак умножения. Другими словами, сумма возврата равна сумме кредита плюс накопленные проценты с этой суммы за каждый год. 1. Создадим таблицу Excel с такими столбцами: Сумма кредита, Срок (лет), Годовой процент, Сумма возврата, Ежемесячный платеж. 2. Установим ячейкам столбцов форматы (денежный, числовой, процентный). 3. Введем произвольные числовые значения, например, S = 1000000 р., Т = 5 лет, Pr = 15 %. 4. В активную ячейку D2 вставим выражение =A2+A2*B2*C2. Завершим ввод, например, клавишей Tab. 5. В активной ячейке Е2 произведем деление найденного значения суммы возврата на количество месяцев: =D2/(12*Т) и получим значение ежемесячного платежа (рис. 73).  Рис. 73. Таблица расчета платежей по кредиту (формула простых процентов) Существует и другой способ вычисления платежей по кредиту на основе формулы сложных процентов, учитывающей тот факт, что в процессе погашения долга его сумма уменьшается, следовательно, и проценты за пользование кредитом берутся с меньшей суммы. Для расчета платежей по кредиту в этом случае имеется специальная встроенная функция ПЛТ в категории Финансовые (рис. 74).  Рис. 74. Выбор функции ПЛТ в диалоговом окне Вставка функции Аргументами этой функции являются: Ставка – процентная ставка, приведенная к одному периоду между платежами. Например, при годовой ставке 12 % и ежемесячном погашении долга, значение аргумента Ставка равно 12 % / 12 = 1 %. Кпер – общее число выплат по кредиту. ПС – приведенная сумма, т.е. сумма кредита. Дополнительные аргументы БС и Тип можно для простоты не учитывать. Будем использовать ту же таблицу и те же данные, что и в предыдущем примере. Только заполним исходные данные в следующей строке. 1. Вычисления начнем, установив курсор в ячейке ежемесячного платежа Е3. 2. Щелкнем кнопку fx (Вставить функцию) в строке формул. 3. В диалоговом окне Вставка функции выберем категорию Финансовые и функцию ПЛТ (см. рис. 74). Щелкнем кнопку ОК. 4. В открывшемся окне Аргументы функции заполним: в поле Ставка: С3/12; в поле Кпер: B3*12; в поле ПС: А3. 5. Щелкнем ОК, чтобы закрыть окно. 6. В активной ячейке Е3 появится результат расчета ежемесячного платежа. Знак «минус» означает, что это наши расходы. 7. Поставим курсор в ячейке D3 и рассчитаем сумму возврата путем умножения ежемесячного платежа на общее число платежей: =Е3*В3*12. Окончательный вид таблицы представлен на рис. 75.  Рис. 75. Таблица расчета платежей по кредиту Вопрос 6. Логические переменные, функции и выражения. Логические переменные (A, B, C, …X, Y, Z) могут принимать только одно из двух возможных значений – ИСТИНА или ЛОЖЬ. Логические функции имеют в качестве аргументов логические переменные и их выражения и тоже могут принимать значения ИСТИНА или ЛОЖЬ. Значения логических функций задаются таблицами истинности. Для вычисления значений логических функций в Excel имеются встроенные функции, объединенные в категорию Логические. Логическое выражение – это выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае – значение ЛОЖЬ. Функция ИЛИ (логическое сложение). Функция ИЛИ принимает значение ИСТИНА, если хотя бы один из аргументов («слагаемых») имеет значение ИСТИНА, и только в единственном случае, когда все «слагаемые» имеют значение ЛОЖЬ, функция ИЛИ принимает значение ЛОЖЬ (см. таблицу 3). Таблица 3.
Создадим таблицу истинности для случая двух аргументов функции ИЛИ на листе Excel. 1. Заполним ячейки диапазона А1:В5 так же, как на приведенной выше таблице 3. 2. «Встанем» курсором в ячейку С3, куда будет помещен результат для первой строки исходных данных. 3. Щелкнем кнопку fx (Вставить функцию) в строке формул. 4. В диалоговом окне Вставка функций выберем категорию Логические, а в ней функцию ИЛИ. Закроем окно кнопкой ОК. 5. В открывшемся окне Аргументы функции заполним поле Логическое_значение1, щелкнув курсором в ячейке А2. 6. Переместим курсор в поле Логическое_значение2 ищелкнем курсором в ячейке В2. Закроем окно кнопкой ОК. 7. В активной ячейке результата С2 появится значение функции ИЛИ (ИСТИНА). 8. Зацепив маркер заполнения активной ячейки, протянем его вниз до конца таблицы (рис. 76).  Рис. 76. Таблица истинности функции ИЛИ Функция И (логическое умножение) и функция НЕ (логическое отрицание). Функция И принимает значение ИСТИНА в единственном случае, когда все аргументы («сомножители») имеют значение ИСТИНА, а во всех остальных случаях принимает значение ЛОЖЬ. Таблица истинности функции И для случая двух аргументов выглядит следующим образом.
Таблица истинности функции И создается в Excel аналогично описанному выше, только в диалоговом окне Вставка функции выбирается функция И. Следует заметить, что функции ИЛИ и И могут иметь любое число аргументов. Функция НЕ (логическое отрицание) имеет всего один аргумент. И если аргумент имеет значение ИСТИНА, функция НЕ принимает значение ЛОЖЬ. Логическая функция ЕСЛИ. Функция ЕСЛИ принимает одно из двух заданных пользователем значений (числовых, текстовых или логических) в зависимости от истинности аргумента, который обычно представляет собой логическую переменную или целое выражение (математическое или логическое). Например, если возраст человека больше 18 лет, он совершеннолетний, в противном случае несовершеннолетний. Создадим в качестве упражнения новую таблицу для записи данных 6 человек разного возраста, причем величина возраста рассчитывается по формуле, приведенной в разделе Исчисление временных интервалов (Тема 11, Вопрос 4) (рис. 77).  Рис. 77. Таблица для проверки совершеннолетия Для проверки на совершеннолетие проделаем следующие действия. 1. Поместим курсор в ячейку Е2 и щелкнем кнопку fx (Вставить функцию) в строке формул. 2. В диалоговом окне Вставка функций выберем категорию Логические, а в ней функцию ЕСЛИ. Закроем окно кнопкой ОК. 3. В открывшемся окне Аргументы функции введем в поле Логическое выражение условие D2>18. 4. В поле Значение, если истина введем Да, в поле Значение, если ложь введем Нет. Закроем окно кнопкой ОК. 5. В активной ячейке таблицы появится результат проверки на совершеннолетие первого человека в списке. 6. Зацепив маркер заполнения активной ячейки, протянем его вниз до конца столбца. Вопрос 7. Использование диаграмм и графиков в MS Excel. На основе данных, хранящихся в таблицах Excel, можно строить диаграммы и графики, которые улучшают восприятие числовых характеристик различных зависимостей и процессов. Чтобы создать на листе Excel диаграмму, надо в таблице, содержащей данные, просто выделить эти данные и выбрать нужный тип диаграммы на вкладке Вставка в группе Диаграммы (рис. 78).  Рис. 78. Группа Диаграммы на вкладке Вставка Рассмотрим простой пример. 1. Составим таблицу продаж легковых автомобилей в 2018 г. (числовые данные – условные).  Рис. 79. Таблица продаж автомобилей 2. Выделим курсором в таблице диапазон А3:F4. 3. Щелкнем кнопку Гистограмма, чтобы открыть пиктографическое меню видов гистограмм, и выберем первый вид из списка (рис. 80).  Рис. 80. Пиктографическое меню видов пиктограмм 4. После выбора вида гистограмма появится на листе (рис. 81).  Рис. 81. Гистограмма на листе Excel 5. Чтобы ввести название диаграммы, следует щелкнуть курсором внутри соответствующего поля в верхней части диаграммы и ввести текст названия. 6. Можно изменить тип диаграммы, если щелкнуть внутри нее правой кнопкой мыши, вызвав контекстное меню, и выбрать команду Изменить тип диаграммы. В открывшемся диалоговом окне Изменение типа диаграммы следует выбрать новый тип и закрыть окно кнопкой ОК (рис. 82).  Рис. 82. Изменение типа диаграммы на круговой График зависимости функции от исходных данных строится так же, как и диаграмма. Рассмотрим вычисление функции y=x2+3x-4 и построение графика этой функции y =f(х): 1. Сначала создадим таблицу на листе Excel. В первой строке введем значения х от 1 до 6 с шагом 1, во второй строке вставим формулу =B1^2+3*B1-4 и скопируем ее на все остальные ячейкистроки таблицы (рис. 83).  Рис. 83. Таблица вычисления функции 2. Выделим строку, содержащую вычисленные значения y (вместе с заголовком строки). 3. А на вкладке Вставка щелкнем кнопку График и в открывшемся пиктографическом меню выберем первую кнопку. 4. На листе появится построенный график вычисленной функции y = f(х) (рис. 84).  Рис. 84. График вычисленной функции В заключение следует заметить, что между данными таблицы и диаграммой (а также и графиком) существует связь: любые изменения данных в таблице мгновенно отображаются на диаграмме или графике. Вопросы для самопроверки: 1. Какое назначение у электронных таблиц? 2. Опишите состав интерфейса табличного процессора MS Excel 2013. 3. Какие типы данных хранятся в ячейках таблицы Excel? 4. Как формируется адрес ячейки таблицы Excel? 5. Что такое диапазон ячеек? Как он обозначается? 6. Каково назначение адресного поля? Что в нем отображается? 7. Что отображается в строке состояния? 8. Как добавить новый лист в книгу MS Excel? 9. Как переименовать лист MS Excel? 10. В чем заключается основной принцип проведения вычислений в таблицах Excel? 11. С чего начинается ввод формул в ячейки таблицы? 12. Какова роль и назначение строки формул? 13. Что такое активная ячейка? Как она выглядит? 14. Перечислите способы завершения ввода данных в ячейки таблицы. 15. Перечислите способы выделения фрагментов таблицы: ячеек, диапазонов, строк, столбцов, листов. 16. Какое назначение у диаграмм и графиков? 17. Как связаны изменения данных в таблице с диаграммой или графиком? Литература по теме: 1. Гуриков С.Р. Информатика: учеб. – М.: Форум, Инфра-М, 2017 г. – 464 с. 2. Сергеева И.И., Музалевская А.А., Тарасова Н.В. Информатика. – 2 изд. – М.: Инфра-М, Форум, 2017. – 384 с. Интернет-ресурсы: 1. Официальный сайт Microsoft Office. – Режим доступа: http://office.microsoft.com 2. Справочные материалы по MS Excel. – Режим доступа: https://support.office.com/ru-ru/excel |