Методические указания
к выполнению лабораторных работ
по дисциплинам «Информатика», «Информационные технологии»
для студентов очной и заочной формы обучения направлений
13.03.01, 15.03.01, 15.03.04, 15.03.05, 20.03.01, 23.03.01, 23.03.02, 23.03.03, 23.05.01, 23.05.02, 27.03.01, 27.03.04, 39.03.01, 39.03.02, 40.03.01
Курган 2017
Кафедра: «Программное обеспечение автоматизированных систем».
Дисциплины: «Информатика», «Информационные технологии»
(направления 13.03.01, 15.03.01, 15.03.04, 15.03.05, 20.03.01, 23.03.01, 23.03.02, 23.03.03, 23.05.01, 23.05.02, 27.03.01, 27.03.04, 39.03.01, 39.03.02, 40.03.01).
Составили: ст. преподаватель В.Я.Котликова, ст. преподаватель Н.Н.Соколова.
Методические указания составлены на основе учебных программ по указанным курсам. Работа выполнена при равноценном участии авторов.
Утверждены на заседании кафедры «25» мая 2017 г.
Рекомендованы методическим советом университета «12» декабря 2016 г.
Введение
Microsoft Excel (Эксель) относится к программным продуктам, которые называются электронными таблицами. С помощью Excel в наглядной форме и можно посчитать и автоматизировать расчеты практически всего, что угодно, начиная с личного месячного бюджета и заканчивая сложными математическими и экономико-статистическими выкладками, содержащими большие объемы массивов данных.
Одной из ключевых особенностей электронных таблиц является возможность автоматического пересчета значения любых нужных ячеек при изменении содержимого одной из них. Для визуализации полученных данных на основе групп ячеек можно создавать различные виды диаграмм, сводные таблицы и карты. При этом электронные таблицы, созданные в Excel, можно вставлять в другие документы, а так же сохранять в отдельном файле для последующего их использования или редактирования. Рассмотрим основные элементы интерфейса и управления Excel, а так же поговорим о некоторых базовых понятиях этой программы.
Интерфейс и управление
Если вы уже знакомы с редактором Word, то разобраться в интерфейсе Excel не составит труда. Ведь в его основе лежит та же Лента (рисунок 1), но только с другим набором вкладок, групп и команд. При этом чтобы расширить рабочую область, некоторые группы вкладок выводятся на экран только в случае необходимости. Так же ленту можно свернуть вовсе, щелкнув по активной вкладке два раза левой кнопкой мыши или нажав сочетание клавиш Ctrl+F1. Возвращение ее на экран осуществляется таким же способом.
Рисунок 1 – Лента
Стоит отметить, что в Excel для одной и той же команды может быть предусмотрено сразу несколько способов ее вызова: через ленту, из контекстного меню или с помощью сочетания горячих клавиш. Знание и использование их может сильно ускорить работу в программе.
Контекстное меню является контекстно-зависимым, то есть его содержание зависит от того, что пользователь делает в данный момент. Контекстное меню вызывается нажатием правой кнопки мыши почти на любом объекте в MS Excel. Это позволяет экономить время, потому что в нем отображаются наиболее часто используемые команды к выбранному объекту.
Не смотря на такое разнообразие управления, разработчики пошли дальше и предоставили пользователям в Excel 2010 возможность вносить изменения во встроенные вкладки и даже создавать собственные с теми группами и командами, которые используются наиболее часто. Для этого необходимо кликнуть правой кнопкой мыши на любой вкладке и выбрать пункт Настройка ленты. В открывшемся окне (рисунок 2) в меню справа выберите нужную вкладку и щелкните по кнопке Создать вкладку или Создать группу, а в левом меню – нужную команду, после чего кликните кнопку Добавить. В этом же окне можно переименовать существующие вкладки и удалять их. Для отмены ошибочных действий существует кнопка Сброс, возвращающая настройки вкладок к начальным.
Рисунок 2 – Настройка ленты
Так же наиболее часто используемые команды можно добавить на Панель быстрого доступа, расположенную в верхнем левом углу окна программы. Сделать это можно, нажав на кнопку Настройка панели быстрого доступа, где достаточно выбрать нужную команду из списка, а в случае отсутствия команды в нем, щелкнуть на пункт Другие команды. Настройка панели быстрого доступа представлена на рисунке 3.
Ввод и редактирование данных
Создаваемые в Excel файлы называются рабочими книгами и имеют расширение «xls» или «xlsx». В свою очередь рабочая книга состоит из нескольких рабочих листов. Каждый рабочий лист – это отдельная электронная таблица. Рабочие листы при необходимости могут быть взаимосвязаны. Активной рабочей книгой является та, с которой в данный момент вы работаете, например, в которую вводите данные.
После запуска приложения автоматически создается новая книга с именем «Книга1». По умолчанию рабочая книга состоит из трех рабочих листов с именами от «Лист1» до «Лист3» (рисунок 4).
Рисунок 3 – Настройка панели быстрого доступа
Рисунок 4 – Рабочая книга MS Excel
Рабочее поле листа Excel поделено на множество прямоугольных ячеек. Объединённые по горизонтали ячейки составляют строки, а по вертикали – столбцы. Для возможности исследования большого объема данных каждый рабочий лист программы имеет 1 048 576 строк, пронумерованных числами, и 16 384 столбцов, обозначенных буквами латинского алфавита.
Таким образом, каждая ячейка – это место пересечения различных столбцов и строк на листе, формирующих ее собственный уникальный адрес, состоящий из буквы столбца и номера строки, которым она принадлежит. Например, имя первой ячейки – A1, так как она находится на пересечении столбца «A» и строки «1».
Если в приложении включена Строка формул, которая расположена сразу же под Лентой, то слева от нее находится Поле имени, где отображается имя текущей ячейки. Сюда же вы всегда сможете ввести имя искомой ячейки, для быстрого перехода к ней. Особенно эта возможность полезна в больших документах, содержащих тысячи строк и столбцов.
Так же для просмотра разных областей листа внизу и справа располагаются полосы прокрутки. Помимо этого перемещаться по рабочей области Excel можно с помощью клавиш со стрелками.
Рисунок 5 – Ввод данных
Чтобы начать вводить данные в нужную ячейку, ее необходимо выделить. Для перехода в нужную ячейку щелкните на ней левой кнопкой мыши, после чего она будет окружена черной рамкой, так называемым индикатором активной ячейки. Теперь просто начинайте печатать на клавиатуре, и вся вводимая информация будет оказываться в выбранной ячейке.
При вводе данных в ячейку можно так же использовать и строку формул (рисунок 5). Для этого выделите нужную клетку, а затем щелкните мышью на поле строки формул и начните печатать. При этом вводимая информация будет автоматически отображаться в выбранной ячейке.
Объем данных, который вы будете вводить в ячейку, не ограничивается ее видимой частью. То есть клетки рабочего поля программы могут содержать как одну цифру, так и несколько абзацев текста. Каждая ячейка Excel способна вместить до 32 767 числовых или текстовых символов.
Форматирование данных ячеек
Формат содержимого ячейки Excel определяет автоматически. Если при вводе формат определяется неверно (например, вместо числа показывается дата), то следует явно задать формат данных ячейки или столбца. Для явного указания формата столбца (например, столбца В) следует щелкнуть правой кнопкой мыши по заголовку столбца В (столбец при этом выделяется) и в появившемся меню выбрать Формат ячеек / Закладка «Число » /Числовые форматы: числовой /Число десятичных знаков: 2 / ОК. В результате во всех ячейках данного столбца будет числовой формат.
Задание 1
Необходимо создать электронную таблицу, предназначенную для автоматизации работы склада, на котором хранится компьютерная техника, предназначенная для реализации заказчикам.
После ввода названий строк и столбцов получаем таблицу такого вида (рисунок 6):
Рисунок 6 – Таблица заказов на компьютерную технику
Чтобы сделать табличный документ более аккуратным и красивым, часто приходится изменять размеры строк и столбцов, шрифт содержимого ячейки, ее фон, осуществлять выравнивание текста, добавлять границы, т. е. осуществлять форматирование документа.
Для начала давайте приведем в порядок левый столбец. Переведите курсор мыши на границу столбцов «A» и «B» в строку, где отображаются их названия. При изменении курсора мыши на характерный символ с двумя разнонаправленными стрелками, нажмите и, удерживая левую клавишу, тащите появившеюся пунктирную линию в нужном направлении для расширения столбца до тех пор, пока все названия не уместятся в рамках одной ячейки (рисунок 7).
Те же самые действия можно проделать и со строкой. Это является одним из самых легких способов изменения размера высоты и ширины ячеек.
Если необходимо задать точные размеры строк и столбцов, то для этого на вкладке Главная в группе Ячейки выберите пункт Формат. В открывшемся меню с помощью команд Высота строки и Ширина столбца можно задать эти параметры вручную.
Очень часто необходимо изменить параметры стразу нескольких ячеек и даже целого столбца или строки. Для того, что бы выделить целый столбец или строку, щелкните на его названии сверху или на ее номере слева соответственно.
Рисунок 7 – Изменение ширины столбца
Теперь, когда мы научились выделять и форматировать сразу несколько ячеек, давайте выровняем название наименования товаров в нашей таблице по центру. Различные команды выравнивания содержимого внутри клеток находятся на вкладке Главная в группе Выравнивание (рисунок 8). При этом для табличной ячейки это действие можно производить как относительно горизонтального направления, так и вертикального.
Рисунок 8 – Выравнивание наименований товаров по центру
Обведите ячейки с названием товаров в шапке таблицы и щёлкните на кнопке Выравнивание текста по центру.
В группе Шрифт на вкладке Главная можно изменить тип шрифта, его размер, цвет и начертание: жирный, курсивный, подчеркнутый. Здесь же размещены кнопки изменения границ ячейки и цвета ее заливки. Итак, для начала установим шрифт всех надписей Times New Roman и увеличим шрифт названия колонок и столбцов таблицы до 14 пунктов, сделаем его жирным. Изменим шрифт заголовка таблицы, задав его размер 16, выровняем заголовок по центру таблицы. Для этого следует отметить блок A1:G1 и на вкладке Выравнивание нажать кнопку Объединить и поместить в центре (рисунок 9).
Рис 9 – Результат форматирования
Теперь выделяем сначала верхнюю строчку таблицы и устанавливаем ей черный фон, а затем в левом столбце ячейкам с А5 по А10 – синий. Сделать это можно с помощью кнопки Цвет заливки. Устанавливаем белый цвет текста на чёрном фоне (рисунок 10).
Рисунок 10 – Заливка ячеек цветом
Далее с помощью уже знакомой команды Цвет заливки мы придали фону четных и нечетных строк с числами различный серый оттенок (рисунок 11).
Чтобы ячейки не сливались, определим им границы. Определение границ происходит только для выделенной области документа, и может быть сделано как для одной ячейки, так и для всей таблицы. В нашем случае выделите всю таблицу, после чего щелкните на стрелочку рядом с кнопкой Другие границы все в той же группе Шрифт.
Рисунок 11 – Выделение строк таблицы
В открывшемся меню выводится список быстрых команд, с помощью которых можно выбрать отображение нужных границ выделенной области: нижней, верхней, левой, правой, внешних, всех и прочее. Здесь же содержатся команды для рисования границ вручную. В самом низу списка находится пункт Другие границы, позволяющий более детально задать необходимые параметры границ ячеек, которым мы и воспользуемся. Также можно изменить границы с помощью контекстного меню. Достаточно нажать правой кнопкой мыши и выбрать в открывающемся списке команду Формат ячеек/Границы и заливка.
Посмотрим, как сформированная нами таблица будет отображаться на печатном листе формата А4. Для этого во вкладке файл открываем меню Печать и выбираем Предварительны просмотр. Таблица выходит за рамки книжной страницы. Назначим альбомную ориентацию: Меню/Печать/Предва-рительный просмотр/Параметры страницы – Альбомная (рисунок 12).
Ввод формул
Помимо создания таблиц Excel можно использовать для выполнения в них арифметических операций, таких как: сложение, вычитание, умножение и деление.
Рисунок 12 – Установка параметров страницы
Для выполнения расчетов в любой ячейке таблицы необходимо создать внутри простейшую формулу, которая всегда должна начинаться со знака равенства (=). Для указания математических операций внутри формулы используются обычные арифметические операторы (рисунок 13):
Рисунок 13 – Арифметические операторы
Например, давайте представим, что нам необходимо сложить два числа – «12 » и «7 ». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7 ». По окончании ввода нажмите клавишу «Enter » и в ячейке отобразится результат вычисления – «19 ».
Чтобы узнать, что же на самом деле содержит ячейка – формулу или число, необходимо ее выделить и посмотреть на строку формул – область, находящуюся сразу же над наименованиями столбцов. В нашем случае в ней как раз отображается формула, которую мы только что вводили.
Далее попробуйте самостоятельно в ячейках ниже получить разность этих чисел, их произведение и частное.
После проведения всех операций, обратите внимание на результат деления чисел 12 на 7, который получился не целым (1,714286) и содержит довольно много цифр после запятой. В большинстве случаев такая точность не требуется, да и столь длинные числа будут только загромождать таблицу. Чтобы это исправить, выделите ячейку с числом, у которого необходимо изменить количество десятичных знаков после запятой, и на вкладке Главная в группе Число выберите команду Уменьшить разрядность. Каждое нажатие на эту кнопку убирает один знак. Слева от команды Уменьшить разрядность находится кнопка, выполняющая обратную операцию: увеличивает число знаков после запятой для отображения более точных значений.
Теперь вернёмся к нашей таблице и посчитаем суммарный заказ. Для этого необходимо сложить данные в ячейках B5:B10. Можно сформировать формулу самостоятельно: =В5+В6+В7+В8+В9+В10. В данной таблице это возможно, но не рационально. В больших таблицах, содержащих большое количество строк, использовать такую формулу совсем нежелательно. Воспользуемся для подсчёта суммарного заказа кнопкой Автосумма на панели элементов (рисунок 14).
Рисунок 14 – Подсчёт суммарного заказа
Нажимаем Автосумму и выделяем блок ячеек В5:В10, завершаем ввод нажатием клавиши Enter. В ячейке В11, если всё верно, отобразится числовое значение Суммарного заказа, формулу можно видеть в строке формул: =СУММ (В5:В10).
Редактирование формул и система отслеживания ошибок
Все формулы, которые находятся в ячейках таблицы, можно отредактировать в любой момент. Для этого достаточно выделить ячейку с формулой и затем щелкнуть по строке формул над таблицей, где вы сможете сразу же внести необходимые изменения. Если вам удобнее редактировать содержимое непосредственно в самой ячейке, то кликните по ней два раза. После окончания редактирования нажмите клавиши Enter или Tab, после чего Excel выполнить перерасчет с учетом изменений и отобразит результат.
Довольно часто случается так, что вы ввели формулу неверно или после удаления (изменения) содержимого одной из ячеек, на которую ссылается формула, происходит ошибка в вычислениях. В таком случае Excel непременно известит вас об этом. Рядом с клеткой, где содержится ошибочное выражение, появится восклицательный знак в желтом ромбе. Во многих случаях приложение не только известит о наличие ошибки, но и укажет на то, что именно сделано не так.
Расшифровка ошибок в Excel:
##### – результатом выполнения формулы, использующей значения даты и времени, стало отрицательное число, или результат обработки не умещается в ячейке;
#ЗНАЧ! – используется недопустимый тип оператора или аргумента формулы. Одна из самых распространенных ошибок;
#ДЕЛ/0! – в формуле осуществляется попытка деления на ноль;
#ИМЯ? – используемое в формуле имя некорректно, Excel не может его распознать;
#Н/Д – неопределенные данные. Чаще всего эта ошибка возникает при неправильном определении аргумента функции;
#ССЫЛКА! – формула содержит недопустимую ссылку на ячейку, например, на ячейку, которая была удалена.
#ЧИСЛО! – результатом вычисления является число, которое слишком мало или слишком велико, чтобы его можно было использовать в MS Excel. Диапазон отображаемых чисел лежит в промежутке от -10307 до 10307.
#ПУСТО! – в формуле задано пересечение областей, которые на самом деле не имеют общих ячеек. Ошибки могут появляться не только из-за неправильных данных в формуле, но и вследствие содержания некорректной информации в ячейке, на которую она ссылается.
Относительная адресация и автозаполнение
Возможность копирования формул в Excel из одной ячейки в другую с автоматическим изменением адресов, содержащихся в них, существует благодаря концепции относительной адресации. Excel понимает адреса ячеек, введенных в формулу, не как ссылку на их реальное месторасположение, а как ссылку на их месторасположение относительно ячейки, в которой находится формула. Поясним на примере.
Ячейка A3 содержит формулу: «=A1+A2». Для Excel это выражение не означает, что нужно взять значение из ячейки A1 и прибавить к нему число из ячейки A2. Вместо этого он интерпретирует данную формулу, как «взять число из ячейки, расположенной в том же столбце, но на две строки выше, и сложить его со значением ячейки этого же столбца, расположенной выше на одну строку». При копировании данной формулы в другую ячейку, например D3, принцип определения адресов ячеек входящих в выражение остается тем же: «взять число из ячейки расположенной в том же столбце, но на две строки выше и сложить его с…». Таким образом, после копирования в D3 исходная формула автоматически примет вид «=D1+D2 ».
С одной стороны, такой тип ссылок дает пользователям прекрасную возможность просто копировать одинаковые формулы из ячейки в ячейку, избавляя от необходимости вводить их снова. А с другой стороны, в некоторых формулах необходимо постоянно использовать значение одной определенной ячейки, а это значит, что ссылка на нее не должна изменяться и зависеть от расположения формулы на листе.
Благодаря относительной адресации для расчета оставшихся сумм, воспользуемся особенностью программы Excel, которая заключается в возможности автоматизировать процесс заполнения ячеек систематизированными данными. Иногда в Excel приходится вводить похожие однотипные данные в определенной последовательности, например, дни недели, даты или порядковые номера строк. Установите курсор в ячейку В11, наведите курсор в правый нижний угол ячейки таким образом, чтобы был виден чёрный крестик. «Зацепите» курсором правый нижний угол и перетащите маркер вправо до ячейки G11. После того, как вы отпустите клавишу, приложение само скопирует формулы в отмеченные ячейки, при этом автоматически изменив адреса клеток, содержащихся в выражении, подставив правильные значения.
Запас на складе и Цену вводим в таблицу, как показано на рисунке 15.
Рисунок 15 – Ввод данных и формул в ячейки таблицы
Потребность в товаре рассчитаем, как показано на рисунке 15, формулу введите самостоятельно и с помощью автозаполнения скопируйте её для всех
товаров таблицы. Аналогично рассчитайте стоимость заказанной партии. Отформатируйте таблицу (рисунок 16).
Рисунок 16 – Таблица заказов на компьютерную технику
Абсолютная адресация
Представим, что в нашей таблице значения стоимости заказанной партии будут рассчитываться в долларах, исходя из рублёвых значений, умноженных на текущий курс, который записан всегда в ячейке В17. Это значит, что при копировании формулы ссылка на эту ячейку не должна изменяться. В этом случае следует применять не относительную, а абсолютную ссылку, которая всегда будет оставаться неизменной при копировании выражения из одной ячейки в другую.
С помощью абсолютных ссылок можно дать команду Excel при копировании формулы:
- сохранять ссылку на столбец постоянно, но при этом изменять ссылки на столбцы;
- изменять ссылки на строки, но сохранять ссылку на столбец;
- сохранять ссылки как на столбец, так и на строку.
Чтобы превратить относительную ссылку в абсолютную или смешанную, необходимо ввести знак доллара ($) перед той её частью, которая должна стать абсолютной.
$В$17 – ссылка всегда на ячейку В17 (абсолютная ссылка);
В$17 – ссылка всегда на строку 17, а путь к столбцу может изменяться (смешанная ссылка);
$В17 – ссылка всегда на столбец В, а путь к строке может изменяться (смешанная ссылка).
Для ввода абсолютных и смешанных ссылок используется клавиша «F4 ». Выделите ячейку для формулы, введите знак равенства = и кликните по клетке, на которую надо установить абсолютную ссылку. Затем нажмите клавишу F4, после чего перед буквой столбца и номером строки программа установит знаки доллара $. Повторные нажатия на F4 позволяют переходить от одного типа ссылок к другим. Например, ссылка на В17 будет циклично изменяться на $В$17, В$17, $В17, В17 и так далее. При желании знаки $ можно вводить вручную. Окончательный вид таблицы представлен на рисунке 17.
Рисунок 17 – Окончательный вид таблицы заказов на компьютерную технику
Функции
Функциями в Excel называют заранее определенные формулы, с помощью которых выполняются вычисления в указанном порядке по заданным величинам. При этом вычисления могут быть как простыми, так и сложными. Например, определение среднего значения пяти ячеек можно описать формулой: =(A1 + A2 + A3 + A4 + A5)/5, а можно специальной функцией СРЗНАЧ, которая сократит выражение до следующего вида: СРЗНАЧ(А1:А5). Как видите, что вместо ввода в формулу всех адресов ячеек можно использовать определенную функцию, указав ей в качестве аргумента их диапазон.
Для работы с функциями в Excel на ленте существует отдельная закладка Формулы, на которой располагаются все основные инструменты для работы с ними (рисунок 18).
Рисунок 18 – Закладка Формулы для работы с функциями
Программа содержит более двухсот функций, способных облегчить выполнение вычислений различной сложности. Поэтому все функции в Excel 2010 разделены на несколько категорий, группирующих их по типу решаемых задач. Какие именно эти задачи, становится ясно из названий категорий: Финансовые, Логические, Текстовые, Математические, Статистические, Аналитические и так далее.
Выбрать необходимую категорию можно на ленте в группе Библиотека функций во вкладке Формулы. После щелчка по стрелочке, располагающейся рядом с каждой из категорий, раскрывается список функций, а при наведении курсора на любую из них, появляется окно с ее описанием (рисунок 19).
Рисунок 19 – Выбор функции
Ввод функций, как и формул, начинается со знака равенства. После идет имя функции в виде аббревиатуры из больших букв, указывающей на ее значение. Затем в скобках указываются аргументы функции – данные, использующиеся для получения результата.
В качестве аргумента может выступать конкретное число, самостоятельная ссылка на ячейку, целая серия ссылок на значения или ячейки, а так же диапазон ячеек. При этом у одних функций аргументы – это текст или числа, у других – время и даты.
Многие функции могут иметь сразу несколько аргументов. В таком случае каждый из них отделяется от следующего точкой с запятой. Например, функция =ПРОИЗВЕД(7; A1; 6; B2) считает произведение четырёх разных чисел, указанных в скобках, и соответственно содержит четыре аргумента. При этом одни аргументы указаны явно, а другие являются значениями определенных ячеек.
Так же в качестве аргумента можно использовать другую функцию, которая в этом случае называется вложенной. Например, функция =СУММ(A1:А5; СРЗНАЧ(В5:В10)) суммирует значения ячеек находящихся в диапазоне от А1 до А5, а так же среднее значение чисел, размещенных в клетках В5, В6, В7, В8, В9 и В10.
У некоторых простых функций аргументов может не быть вовсе. Так, с помощью функции =ТДАТА() можно получить текущие время и дату, не используя никаких аргументов.
Далеко не все функции в Eхсel имеют простое определение, как функция СУММ, осуществляющая суммирование выбранных значений. Некоторые из них имеют сложное синтаксическое написание, а так же требуют много аргументов, которые должны быть правильных типов. Чем сложнее функция, тем сложнее ее правильное составление. И разработчики это учли, включив в свои электронные таблицы помощника по составлению функций для пользователей – Мастер функций.
Для того, чтобы начать вводить функцию с помощью Мастера функций, щелкните на значок Вставить функцию (fx), расположенный слева от Строки формул (рисунок 20).
Рисунок 20 – Вставка функции в ячейку
Так же кнопку Вставить функцию вы найдете на ленте сверху в группе Библиотека функций во вкладке Формулы. Еще одним способом вызова мастера функций является сочетание клавиш Shift+F3.
После открытия окна помощника, первое, что вам придется сделать – это выбрать категорию функции. Для этого можно воспользоваться полем поиска или ниспадающим списком (рисунок 21).
В середине окна отражается перечень функций выбранной категории, а ниже – краткое описание выделенной курсором функции и справка по ее аргументам. Назначение функции часто можно определить по ее названию.
Сделав необходимый выбор, щелкните по кнопке ОК, после чего появится окно Аргументы функции (рисунок 22).
В левом верхнем углу окна указывается имя выбранной функции, под которым находятся поля, служащие для ввода необходимых аргументов. Справа от них после знака равенства указываются текущие значения каждого аргумента. В нижней части окна размещается справочная информация, указывающая назначение функции и каждого аргумента, а так же текущий результат вычисления.
Ссылки на ячейки (или их диапазон) в поля для ввода аргументов можно вводить как вручную, так и используя мышь. Для этого просто щелкайте левой кнопкой по нужным клеткам на открытом листе или обведите их необходимый диапазон. Все значения будут автоматически подставлены в текущее поле ввода.
Рисунок 21 – Выбор категории функции
Рисунок 22 – Окно Аргументы функции
Рисунок 23 – Ввод данных в окно Аргументы функции
Если диалоговое окно Аргументы функции мешает вводу необходимых данных, перекрывая собой рабочий лист, его можно на время уменьшить, нажав на кнопку в правой части поля ввода аргументов (рисунок 23).
Повторное нажатие на нее же приведет к восстановлению обычного размера.
После ввода всех необходимых значений остается кликнуть по кнопке ОК, и в выбранной ячейке появится результат вычисления.
Построение диаграмм
Довольно часто данные в таблице, даже отсортированные должным образом, не позволяют составить полную картину по итогам вычислений. Чтобы получить наглядное представление результатов, в MS Excel существует возможность построения диаграмм различных типов. Это может быть как обычная гистограмма или график, так и лепестковая, круговая или экзотическая пузырьковая диаграмма. Более того, в программе существует возможность создавать комбинированные диаграммы из различных типов, сохраняя их в качестве шаблона для дальнейшего использования.
Диаграмму в Excel можно разместить либо на том же листе, где уже находится таблица, и в таком случае она называется «внедренной», либо на отдельном листе, который станет называться «лист диаграммы».
В качестве примера рассмотрим сравнительный анализ на гистограмме Потребность в товаре (рисунок 26). Для этого выберем вкладку Вставка, далее выбираем Гистограмма. Диаграмма готова. Данные будут располагаться в столбцах. Для того, чтобы диаграмма была наглядной, необходимо сделать подписи рядов. Для этого в контекстном меню выбираем пункт Выбор источника данных и на закладке Элементы легенды (ряды) нажимаем кнопку Изменить. Для каждого ряда необходимо подписать его значение, сделав абсолютную ссылку на название ряда в заголовке таблицы (рисунки 24, 25).
Отформатируем диаграмму, добавив заголовок и подписи к осям.
Заголовок: шрифт 16 Times New Roman цвет красный п/ж (полужирный).
Подписи осей: Times New Roman 12 (рисунок 26).
Рисунок 24 – Построение диаграммы
Рисунок 25 – Подпись рядов данных
Рисунок 26 – Диаграмма «Потребность в товаре »
Задание 2
Построить круговую диаграмму: «Стоимость заказанной партии».
Легенду поместить справа. Заголовок: шрифт 18 Times New Roman. Подписи данных: имена категорий и доли, добавить линии выноски.
Для подписи данных необходимо выбрать в контекстном меню: Формат подписей данных (рисунки 27, 28).
Рисунок 27 – Контекстное меню форматирования диаграммы
Рисунок 28 – Формат подписей данных
Задание 3
Применение функций и графических средств MS Excel для построения графиков основных тригонометрических функций (рисунки 29, 30, 31).
Необходимо построить графики тригонометрических функций: sin(x), cos(x), tg(x), ctg(x). График нужно построить на основе таблиц, содержащих значения функций sin(х), cos(х) для аргумента х в интервале [0˚ - 360˚] с шагом 10˚, ctg(х) – для аргумента х в интервале [10˚ – 170˚] с шагом 10˚, tg(х) – для аргумента х в интервале [-85˚ - +85˚] с шагом 5˚.
Указание к заполнению таблиц:
1) Значение угла в градусах заносить в ячейки с помощью механизма автозаполнения.
2) Значения формул копировать с помощью автозаполнения.
3) При занесении формул в ячейки использовать мастер формул. Значение в колонке ctg(х) вычисляется по формуле cos(x)/sin(x).
4) Колонка «Значение угла в радианах» нужна, поскольку аргументы тригонометрических функций в MS Excel должны быть заданы в радианах.
5) Таблицы MS Excel вместе с соответствующими диаграммами должны располагаться на двух листах одной рабочей книги.
Рисунок 29 – Построение графика функции tg(x)
Рисунок 30 – Задание диапазона, подписей к оси и подписей рядов данных к графику функции tg(x)
Рисунок 31 – Построение графиков функций sin(x), cos(x), ctg(x)
Логические функции
В состав встроенных функций Excel входят логические функции (рисунок 32), что позволяет использовать табличный процессор для решения логических задач.
1 Функция ЕСЛИ. Используется для проверки условий при вычислениях (рисунок 33).
Синтаксис функции:
=ЕСЛИ(логическое выражение; значение если истина; значение если ложь).
Рисунок 32 – Выбор логической функции
 |
Рисунок 33 – Проверка условия
Функция ЕСЛИ возвращает значение ИСТИНА, если логическое выражение имеет значение истина и возвращает значение ЛОЖЬ, если логическое выражение имеет значение ложь.
Пример 1. Решить задачу «Абитуриент» с применением функции ЕСЛИ. Если проходной балл больше или равен 18, то результат +, иначе ‒.
| Фамилия | математика | физика | литература | биология | результат |
| Иванов | + | ||||
| Петров | ‒ |
Решение. В столбце результат запишем формулу: =ЕСЛИ(F2>=18; +; ‒ ) Затем с помощью автозаполнения скопируем формулу для всех строк таблицы.
Функция И возвращает значение ИСТИНА, если все ее аргументы имеют значение истина.
Синтаксис функции: =И(аргумент1; аргумент 2; аргумент3…)
Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументы имеет значение истина.
Синтаксис функции: =ИЛИ(аргумент1; аргумент 2; аргумент3…)
Функция НЕ заменяет логическое значение аргумента противоположным.
Синтаксис функции: =НЕ(логическое выражение)
Пример 2. Решить задачу с применением функции ЕСЛИ. Если хотя бы по одному предмету оценка 3 (-), то студент не получает стипендию, в остальных случаях получает (+).
| История | Литература | Математика | Иностранный язык | Стипендия | |
| Иванов | + | ||||
| Петров | - |
Решение: в колонке Стипендия запишем формулу =ЕСЛИ(И(B2>3;C2>3;D2>3;E2>3; +; -).
Затем с помощью автозаполнения скопируем формулу для всех строк таблицы.
Задание 4
Создать электронную таблицу, которая бы автоматически определяла, какую стипендию должен получать студент в зависимости от его успеваемости по результатам сессии. Критерии получения стипендии следующие:
1) Если хотя бы одна тройка, студент не получает стипендию.
2) Если троек нет, но есть четвёрки – студент получает обычную стипендию.
3) Если оценки только отличные – студент получает повышенную стипендию.
Таблица должна иметь вид, приведенный ниже (таблица 1). Звёздочки в соответствующи