Информационные и компьютерные технологии. Мicrosoft Excel

ПРЕДИСЛОВИЕ Информационные и компьютерные технологии — широкий класс дисциплин и областей деятельности, относящихся к технологиям создания, управления и обработкиданных, в том числе с применением вычислительной техники. В последнее время под информационными технологиями чаще всего понимают компьютерные технологии. В частности, ИКТ имеют дело с использованием компьютеров и программного обеспечения для хранения, преобразования, защиты, обработки, передачи и получения информации. Целью курса является формирование знаний и практических навыков для самостоятельного решения на компьютере инженерных задач с использованием типового математического обеспечения, что позволит применять вычислительную технику в специальных дисциплинах и в инженерной практике. Курс «Информационные и компьютерные технологии» изучаются на втором курсе и относится к циклу естественно-научных дисциплин. В соответствии с государственным образовательным стандартом высшего образования обучение студентов всех форм подготовки, включая и заочную, осуществляется на основании государственных требований к минимуму содержания и уровню подготовки. РАБОТА В ПРИЛОЖЕНИИ МICROSOFTЕXCEL Приложение MicrosoftExcel предназначено для работы с электронными таблицами, позволяющими собирать, перерабатывать, анализировать и представлять как количественную и логическую, так и графическую информацию. С помощью Excel легко планировать, контролировать, а также решать множество повседневных задач, в том числе: технических, экономических, финансовых, и других. С помощью приложения Excel можно создавать самые разнообразные документы для сбора и анализа данных, а также представлять полученные данные и результаты расчетов в виде различных графиков и диаграмм. В версии Excel 2013 (рис. 1) используется контекстно-зависимая ленточная система. Слова (такие как Главная, Вставка, Разметка страницы и т.д.) представляют вкладки. Щелкнув на слове, представляющем вкладку, разворачивается лента, содержащие команды выбранной вкладки. Каждая команда имеет название, которое отображается рядом (или ниже) с пиктограммой, щелчком на которой выполняется данная команда. Команды собраны в группы, и каждая группа имеет собственное название, которое отображается внизу ленты. Работа с данными в приложении Excel 2013 осуществляется в окне приложения Excel. Рис.1. ОкноМicrosoftExcel 2013. Исходные данные и все результаты действий, выполняемых в Excel, хранятся в рабочей книге, которая открывается в отдельном окне. По умолчанию файлы рабочих книг имеют расширение xlsx. Каждая рабочая книга состоит из одного или нескольких рабочих листов, а рабочий лист, в свою очередь, состоит из отдельных ячеек. Столбцы маркируются буквами латинского алфавита. Если букв недостаточно, то используется двухбуквенная, далее трехбуквенная маркировка: от А до XFD(всего 16384 столбца). Строки маркируются целыми числами. Максимальное число строк, которое может иметь таблица – 1 048 576. Ячейки располагаются на пересечении столбцов и строк. Номер (адрес) ячейки включает в себя маркер столбца и маркер строки, записанные без пробела. Одна из ячеек на рабочем листе является активной, т.е. готова к приему информации или другим действиям. Рабочая ячейка обрамляется рамкой из жирных линий (рис.1). Ее адрес, т.е. буква столбца и номер строки, указывается в поле Имя. На рис.1 выделена ячейка с адресом D2. Кроме адреса отдельной ячейки можно отметить и адрес диапазона ячеек. Адрес диапазона ячеек состоит из адреса начальной ячейки и адреса конечной ячейки. Например, В2:Е10 или А2:А100. Ячейка может содержать три основных типа данных: текстовые, числовые данные и формулу. Текстовые данные представляют собой строку текста произвольной длины. Ячейка, содержащая текстовые данные, не может использоваться в вычислениях. Числовые данные – это отдельное число, введенное в ячейку. Ячейки, содержащие числовые данные, могут использоваться в вычислениях. Содержимое ячейки рассматривается как формула, если оно начинается со знака равенства (=). Если ячейка содержит формулу, это означает, что данная ячейка вычисляемая, то есть значение ее может зависеть от значений других ячеек таблицы. Как правило, все формулы (за исключением логических суждений) дают числовой результат. Строка формул отражает данные, содержащиеся в рабочей ячейке. Строка состояния отражает информацию о выбранных командах и процедурах (операциях). 1.1. Форматирование ячеек Информация, отражаемая в ячейке, должна быть представлена так, чтобы ее можно было эффективно, удобно и легко использовать для дальнейшей обработки. Этому способствует предварительное форматирование, как характера оформления ячейки, так и характера представления данных в ячейке. Форматирование ячеек выполняется из диалогового окна Формат ячеек (Рис. 2). Рис. 2. Вид диалогового окна Формат ячеек Его можно вызвать следующими способами: - нажать на кнопку , которые находятся в правом нижнем углу группы команд Шрифт, Выравнивание, Число (рис. 3); Рис. 3. Вид группы команд Шрифт, Выравнивание, Число. - выделить диапазон ячеек или ячейку, которые подлежат форматированию и кликнуть правой кнопкой мыши, вызвав контекстное меню (рис. 4). Рис. 4. Контекстное меню и панель инструментов Формат ячеек Форматирование ячеек можно выполнить, используя группы команд Шрифт, Выравнивание, Число (рис. 3) или панель инструментов, которая появляется при вызове контекстного меню (рис. 4) С помощью диалогового окнаФормат ячеек можно отформатировать: характер представления данных, характер расположения данных в поле ячейки, параметры применяемого шрифта, характер обрамления ячейки, характер оформления поля ячейки. При необходимости форматирование можно выполнять форматирование не только отдельной ячейки, но и группы (блока) выделенных ячеек. Выделение блока ячеек осуществляется протаскиванием курсора мыши при нажатой левой кнопке. Для выделения нескольких блоков ячеек,расположенных в различных местах рабочего поля таблицы приложения Excel необходимо после выделения первого блока отпустить левую кнопку мыши, а затем при нажатой клавише Ctrl выделить следующий блок и так далее. 1.1.1 Представление данных Характер представления данных выбирается из группы команд Число(рис. 3) в ленточной вкладки Главная или диалогового окнаФормат ячеек(рис. 2). При использовании форматов: числовой, денежный, финансовый, процентный, экспоненциальный можно задавать необходимое количество знаков после запятой, отражаемое в поле ячейки. Экспоненциальный формат рекомендуется принимать для отражения в поле ячейки очень больших или очень малых чисел. Денежный формат позволяет выбирать денежную единицу (рубль, доллар, евро, гривна и т.д.), при этом в ячейке, кроме числового значения, показывается и обозначение выбранной денежной единицы. Представляемое числовое значение отражается с группировкой по три знака, между группами дается пробел. Даты рекомендуется представлять в германском формате (дд.мм.гг). 1.1.2 Размещение данных Характер размещения данных можно осуществлять следующими двумя основными способами: • из вкладки Выравнивание диалогового окна Формат ячеек (рис. 5). Рис. 5. Выбор характера расположения данных - выбором команд из группы ВыравниваниевкладкиГлавная (рис. 3). С помощью вкладки Выравнивание можно обеспечить расположение данных практически в любом месте поля ячейки, под любым углом. Кроме того, можно обеспечить расположение текстовых данных с переносом по словам и объединение ячеек выделенного блока в одну. 1.1.3 Выбор параметров шрифта Выбор параметров шрифта отображения данных можно осуществлять следующими двумя основными способами: • из вкладки Шрифт диалогового окна Формат ячеек (рис. 6). Рис. 6. Выбор параметров шрифта - выбором команд из группы Шрифт вкладки Главная (рис. 3). Возможности вкладки Шрифт обеспечивают выбор необходимого вида шрифта, его начертания, размера и характера расположения текста в строке. 1.1.4 Оформление границ и поля ячейки Для придания таблице большей эстетической привлекательности можно использовать возможности вкладок Граница(рис. 7) и Вид(рис.8). На вкладке Граница можно отдельно выбрать характер и цвет как наружных, так и внутренних границ выбранного участка таблицы. На вкладке Вид можно выбрать для различных участков таблицы свой цвет поля ячеек, а также необходимый узор поля. Рис. 7. Выбор параметров обрамления ячеек Рис.8. Выбор параметров оформления поля ячейки Границы и оформление ячеек можно выбрать более быстрым способом используя группу команд Шрифт вкладки Главная(рис. 9,рис. 10). Рис. 9. Вид группы команд Шрифт (выбор оформления таблицы) Рис. 10. Рис. 9. Вид группы команд Шрифт (выбор заливки таблицы) 1.2 Расчеты в Excel Одной из основных функций табличного процессора Excel является выполнение расчетов различного характера. Расчеты в ячейке выполняются после ввода в нее формулы. Ввод формулы начинается с нажатия клавиши равняется (=) на клавиатуре или щелчка левой кнопки мыши по кнопке равняется в строке формул. В формулах допустимо использование следующих операторов: вычитание (-), деление(/), умножение (*), возведение в степень (^), меньше (<), больше (>), не равно (<>), больше или равно (=>), меньше или равно (<=) и так называемых операторов связи – диапазон (:), объединение (;) и объединение текстов (&). Формула может включать в себя числа и адресные ссылки на ячейки, данные из которых используются для расчетов, математические и специальные функции, используемые в расчете. Адресные ссылки, в зависимости от характера применения данных в расчетных формулах, могут быть трех видов: относительная – это изменяющийся при копировании формулы адрес ячейки, содержащий исходный параметр, используемый в формуле. В строке формул относительная адресная ссылка имеет вид – A15, D27. абсолютная – это не изменяющийся при копировании формулы адрес ячейки, содержащий исходный параметр в формуле. Для указания абсолютной адресации вводится символ $. Абсолютная ссылка на ячейку A1 записывается в виде $A$1, т.е. символом $ мы указываем, что в ссылке ни имя столбца, ни номер строки при копировании или перемещении изменяться не будут. Быстрый перевод ячейки в абсолютный адрес можно сделать нажатием на клавишу F4, при этом курсор должен стоять на адресе ячейки А1. смешанная – это частично изменяющийся при копировании формулы адрес ячейки, содержащей исходный параметр. Смешанные ссылки бывают двух видов: • с фиксацией адреса по столбцу, имеющие вид – $A1, • с фиксацией адреса по строке; имеющие вид – A$1. При копировании формул с такими ссылками не изменяется та ее часть, перед которой стоит символ $. Если символ $ стоит перед именем столбца, то координата столбца абсолютная, а строки – относительная. Если символ $ стоит перед номером строки, то, напротив, координата столбца относительная, а строки – абсолютная. Автозаполнение осуществляется следующим образом – курсор мыши переводится в нижний правый угол текущей ячейки (при этом он превращается в небольшой черный крестик). Затем при нажатой левой кнопке мыши выполняется перемещение курсора по всему диапазону данных, для которых выполняется расчет. Автозаполнением можно задавать последовательности данных, изменяющиеся с определенным шагом – арифметические прогрессии. Для этого необходимо в первую ячейку вводится первое число последовательности, во вторую – второе число, измененное на величину шага. Обе ячейки выделяются, курсор переводится в правый нижний угол выделенного блока (при этом курсор превращается в небольшой черный крестик), затем при нажатой левой кнопки мыши курсор перемещается до получения необходимого диапазона данных. Кроме того, автозаполнением можно задавать обиходные последовательности: месяцы, дни недели; а также нумерованные текстовые последовательности (рис. 11). Рис. 11. Варианты представления данных автозаполнением В формулах также можно использовать встроенные функции. Функция – это заранее определенная формула, которая по одному или нескольким аргументам, заключенным в скобки, вычисляет результат. Excel содержит огромное количество встроенных функций. К ним относятся как достаточно распространенные функции, например, СУММ, СРЗНАЧ и КОРЕНЬ, так и функции, специально предназначенные для определенных целей, например, статистические или инженерные. После имени функции следуют круглые скобки. Данные внутри скобок называются аргументами. В качестве аргумента может выступать адрес ячейки или диапазон, числовое значение, текстовая строка, выражение или другая функция. Если в функции несколько аргументов, то каждый из них отделяется точкой с запятой. Функции различаются по тому, как они используют аргументы: • функции без аргумента, например, ПИ( ), возвращающая число π, в этом случае ставятся просто пустые скобки; • функции с одним аргументом, например, SIN(A1), возвращающая значение синуса числа, расположенного в ячейке А1; • функции с фиксированным числом аргументов, например, ОКРУГЛ(B4; В2), реализующая округление числа из ячейки B4 до указанного в ячейке B2 числа десятичных разрядов; • функция с неопределенным числом аргументов, например, СУММ(A1:B1; J2:K3; J3:L4), реализующая суммирование данных из разных диапазонов ячеек; каждый диапазон – аргумент функции, их может быть от 1 до 255. В формулах, включающих элементы логических суждений, могут использоваться элементы текста, которые должны быть заключены в кавычки. Вставка функций и формулы Библиотека функций. Чтобы использовать прием «Автозаполнение формул», необходимо знать хотя бы первую букву названия функции. Другой способ вставки функции в формулу – это выбор этой функции из группы команд Библиотека функций, расположенной на ленточной вкладке Формулы. Этот способ полезен в том случае, когда неизвестно название функции. Если щелкнуть мышью на кнопке, представляющей одну из категорий функций (например, Финансовые, Математические и т.д.), откроется список функций выбранной категории (Рис. 12). Рис. 12. Список функций категории Логические Выбрав щелчком мыши нужную функцию, открываем вкладку функции – диалоговое окно Аргументы функции (рис. 13), в котором имеется возможность задать значения аргументов выбранной функции, а также дается краткое описание назначения функции и (при перемещении курсора по полям окна) назначение ее аргументов. На вкладке имеется ссылка Справка по этой функции, щелчок по которой переносит в справочную систему Excel с открытой статьей, посвященной данной функции. Рис. 13. Диалоговое окно Аргументы функции логической функции ИЛИ Математические и специальные функции можно вводить в формулы и используя диалоговое окно Вставка функции (рис. 14). Рис. 14. Общий вид диалогового окна Вставка функции Диалоговое окно Вставка функцииможно открыть одним из следующих способов: - выбрать команду Формулы =>Библиотека функций=> Вставить функцию (Рис. 15). Рис. 15. Вид ленточной вкладки Формулы с командой Вставить функцию - щелкнуть на кнопке Вставка функции , которая находится слева от строки формул (Рис. 16) Рис. 16. Фрагмент окна Excelcуказанием кнопки Вставка функции - нажать комбинацию клавиш Shift+F3. При вызове диалогового окна Вставка функциив тестовом поле будут отражены команды последних десяти использованных функций. Для выбора нужной функции выбирается необходимая категория(например, Математическая) и функция(например, SINH), которая вводится щелчком левой кнопки мыши (рис. 17). Рис. 17. Вид диалогового окна Вставка функции Появляется диалоговое окно вызываемой функции (рис. 18). Рис. 18. Окно ввода команды функции SINH – гиперболический синус числа После ввода данных в поля диалогового окна команды функции порядок действий может быть следующим: если вводимая функция является последней операцией формулы, то можно выполнять расчет формулы: нажатием кнопкиOk диалогового окна, либо щелчком левой кнопки мыши по команде Ввод на строке формул, либо нажатием клавиши Ввод (Enter) клавиатуры. В том случае, когда формула предполагает вычисления с диапазонами данных и в результате расчетов получается массив данных, ввод формулы в расчет выполняется комбинацией клавиш клавиатуры Ctrl+Shift+Enter. если вводимая функция является частью формулы, то необходимо перевести курсор в конец введенной части формулы на Строке формул и продолжить ввод формулы, если вводимая функция является вложенной в предыдущую функцию, то необходимо щелчком левой кнопки мыши по аббревиатуре предыдущей функции на Строке формул вызвать диалоговое окно предыдущей функции и в полях этой функции продолжить ввод недостающих данных. Преимущество этого способа перед использованием кнопок из Библиотеки функций состоит в возможности выбирать функции из Полного алфавитного перечня, если категория функции неизвестна. Более того, диалоговое окно Вставка функции помогает выбирать нужную функцию, даже если точно неизвестно, какую из них надо применить. Для этого можно ввести в поле Поиск функции краткое описание того, что должна делать функция, и щелкнуть на кнопке Найти. После этого Мастер функций выведет в поле Выберите функцию список подходящих функций, среди которых можно выбрать нужную. Пример 1. Определить значение функции в интервале изменения аргумента 0 ≤ x ≤ 900 с шагом изменения 150, С=3. Порядок расчетов: 1. Для ввода значений х принимаем столбец А: в ячейку А1 вводим текст «х», начиная с ячейки А2 будем вводить значения аргумента 0 , 15 , …, 90 ; в ячейку В1 вводим текст « у», начиная с ячейки В2 будем определять значения функции у ; в ячейку D1 вводим значение постояннойС– 3. 2. В ячейку А2 вводим начальное значение аргумента – 0 , в ячейку А3 вводим измененное на величину шага значение аргумента – 15 , блок ячеек А2:А3 выделяем. Автозаполнением вводим значения аргумента х для всего интервала его изменения. 3. Ячейку В2 подготавливаем для ввода формулы вычисления заданной функции. Для этого вводим знак равенства с клавиатуры или из строки формул. Ввод формулы начинаем с ввода постоянной 2,5 и знака умножения «*». 4. Вызываем функцию SIN( ) командой Вставка функций или из текстового поля, отражающего перечень последних десяти использованных в предыдущих расчетах функций. 5. В числовое поле окна функции SIN( ) вводим функцию ПИ( ). В числовом поле окна функции SIN( ) продолжаем ввод аргумента данной функции: вводим символ деления «/»; затем постоянную 4; затем знак «+»; далее постоянную 8; далее знак умножения «*»; так как функция SIN( ) определяется по радианной мере угла, то вводится функция РАДИАНЫ( ); щелчком по ячейке А2 осуществляем адресную ссылку на ячейку, из которой берется значение аргумента х. Так как необходимо определить значение функции у для всего интервала аргумент 6. Аргумент функции SIN( ) введен полностью, но расчетная формула введена не полностью, поэтому необходимо щелчком курсора в конце введенной части формулы на строке формул обеспечить возможность продолжения ввода формулы. Вводим знак «+», затем щелчком по ячейке D1 осуществляем адресную ссылку на значение константы 3, записанной в эту ячейку. Так как при автозаполнении значение константы в формуле не должно меняться, то адресную ссылку на данную ячейку выполняем абсолютной. Для этого после ввода ссылки на ячейку D1 нажимаем один раз функциональную клавишу F4 на клавиатуре. Выполнить расчет по введенной формуле. Для этого можно выполнить одно из следующих действий: выполнить щелчок курсором по команде Ok окна Значение, выполнить щелчок курсором по команде Ввод « » на строке формул, нажать клавишу Enter клавиатуры. В ячейке В2 отразится результат расчета, а на строке формул будет видна расчетная формула. 7. Автозаполнением выполняем расчеты для всего интервала значений аргумента х. При необходимости весь массив полученных значений функции у можно отформатировать для отражения его с необходимой точностью, шрифтом и характером выравнивания значений в поле ячеек. В нашем случае принято: точность отражения значений с восьмью знаками после запятой; шрифт Arial , полужирный, размером 12 пт; выравнивание по вертикали и горизонтали – в центре. 1.3 Расчеты с использованием специальных функций В своей практической деятельности инженерные работники, работники экономического профиля достаточно часто выполняют расчеты: статистической обработки данных, финансовые расчеты, расчеты с применением временных данных, а также решение логических суждений. Для этих целей приложение MicrosoftExcel располагает большим набором статистических, финансовых, логических функций и функций категории ДАТА. 1.3.1 Расчеты с использованием статистических функций В большом числе случаев при анализе числовых (количественных) данных, особенно представляющих массивы, возникает необходимость определения статистических характеристик. Наиболее часто оцениваемые статистические характеристики и соответствующие им функции приложения MicrosoftExcel приведены в таблице 1. Таблица 1. Специальные функции приложения MicrosoftExcel основных статистических характеристик Статистическая характеристика Расчетная функция Минимальное значение анализируемых числовых данных МИН( ) Максимальное значение анализируемых числовых данных. МАКС( ) Среднее значение анализируемых числовых данных (математическое ожидание). СРЗНАЧ( ) Подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию СЧЁТЕСЛИ( ) Пример 2. Создать ведомость выдачи заработной платы сотрудников отдела. Определить максимальную, минимальную и среднюю заработную плату сотрудником с учетом вычета подоходного налога и количество сотрудников получаемых зарплату более 18 тысяч рублей. Порядок расчетов: 1. Заполнить таблицу исходными данными: В ячейкуА1 введем – ФИО, В2-Должность, С1 – Начислено, D1-Подоходный налог, Е1-Выдано на руки. Блоки ячеек А2:А7, В2;В7, С2:С7 заполняем данными (см рис. ниже). 2. Данные колонки «Начислено» перевести в денежный формат. Для этого выделить ячейки С2:С9 и выбрать из списка форматов – Денежный. В ячейку G1выведем значениепроцента, вычитаемого на подоходный налог –13. 3. В ячейке D1 набрать формулу расчета подоходного налога (13% от начисленной суммы): =C2*G1/100. Поставив курсор на адрес ячейки G1переведем ее на абсолютный адрес, нажав на клавишу F4. В результате формула примет вид: = C2*$G$1/100. 4. В ячейке Е2 записать формулу расчета суммы к выдаче за вычетом подоходного налога: =С2-D2. 5. Подготовим ячейки для описания числовых значений статистических характеристик. Для этого объединим построчно (в строках 13 – 15 столбцы А – В). Введем в объединенные ячейки тестовое описание статистических функций: А13 – Максимальная зарплата, А14- Минимальная зарплата, А15 – Средняя зарплата, А17 – Более 18000 руб. 6. В ячейку С13 введем формулу определения максимальной суммы получаемой на руки. Окно функции МАКС( ) может включать до 30 числовых полей, в которые могут быть введены: отдельные числа, адреса отдельных ячеек, адреса блоков ячеек. То есть синтаксис статистической функции МАКС( ) следующий – МАКС(число 1, число 2,…, число 30 ). В нашем случае введен адрес блока ячеек Е2:Е9 выделением данного блока. 7. Аналогично в ячейках С14 – С17 определяем остальные искомые статистические функции. Синтаксис всех указанных в таблице 1 статистических функций аналогичен рассмотренной функции МАКС( ). Каждую расчетную ячейку отформатируем необходимым образом. 8. Для определения количества сотрудников получаемых зарплату более 18 тысяч рублей используем функцию СЧЁТЕСЛИ(), которая имеет следующий синтаксис: СЧЁТЕСЛИ(Диапазон; Критерий). 9. В ячейке С10 вычислим сумму начисленных сумм. Выделить значения начисленных сумм и выбрать команду в группе команд Редактирование ленточной вкладки Главная. В следующей ячейке внизу после последнего значения начисленной суммы появится суммарное итоговое значение. Рядом, слева от значения суммы, набрать слово «Итого». 10. Аналогичным образом вычислить суммы подоходных налогов и полученных сумм на руки. 11. Оформить таблицу. Поместить курсор в первую строку и вставить перед ней две пустые строки. Для этого выбрать из списка команд Вставить команду Вставить строки, которая находится в группе команд Ячейки ленточной вкладки Главная.на лист из групп 12. В первой строке набрать текст Ведомость выдачи заработной платы. Выделить все ячейки по ширине таблицы, вызвать диалоговое окно Формат ячеек во вкладке Выравниваниеустановить выравнивание по горизонтали – по центру выделения. Перейти во вкладку Шрифт и установить начертание – полужирный, размер – 14. Кликните мышкой на по кнопке ОК. Название растянется над всей таблицей. 13. Выделить с помощью мыши таблицу. Вызвать диалоговое окно Формат ячеек. Во вкладке Граница сделать внешнюю и внутреннюю рамку одинарной линией. Нажать на ОК. 14. Выделим с помощью мыши ячейки А15:С19 и используя группу команд Шрифт в ленточной вкладки Главная установим: тип шрифта – Сalibri, размер – 14, начертание – полужирный. В результате получим таблицу: 1.3.2 Расчеты, включающие логические суждения При выполнении расчетов для решения значительного числа задач различного плана необходимо предварительно обеспечить решение какоголибо логического суждения. Для формирования логических суждений в приложение MicrosoftExcel включена категория логических функций: ЕСЛИ( ), И( ), ИЛИ( ), НЕ( ). Основой любого логического суждения является условие, которое определяет порядок дальнейших расчетов в зависимости от характера выполнения условия. Условие может быть простым, включающим вложение дополнительных условий, или развернутым. Пример 3. Вычислить значение функции у = f (х) для значений х в области определения {0,5 ; 2,0}. Шаг изменения аргумента принять ∆х = 0,1. Функция у определяется уравнениями: Константа а = 0,9. Порядок расчетов: 1. Подготавливаем расчетную таблицу. В ячейку А1 вводим – х, в ячейку В1 – у. Для значения константы а выделяем ячейку D2. 2. В ячейку А2 ввести начальное значение х – 0,9.Снова поставить курсор на ячейку, в этом случае ячейка должна быть выделена рамкой. Заполнить столбец аргументов, используя командуЗаполнитьс вкладки Главная⇒Редактирование. - включить прокрутку команды Заполнитьи выбрать команду Прогрессия, после чего откроется диалоговое окно; - установить в полях окна необходимые параметры: расположение – по столбцам, шаг – 0,5(десятичная запятая) и предельное значение – 2; - после нажатия клавиши < OK> строки столбца Абудут заполнены необходимыми значениями. 3. В ячейку В2 введем формулу, включающую логическую функцию ЕСЛИ(). - вводим в ячейку B2 знак равенства и первую букву е функции ЕСЛИ, чтобы открыть список названий функций и имен диапазонов, которые начинаются с данной буквы. Средство Автозаполнение формул не чувствительно к регистру букв, поэтому можно вводить имена функций как строчными, так и прописными буквами, соответственно, в русской или латинской клавиатуре. - выбрать из списка функций функцию ЕСЛИ и кликнуть по ней мышкой. В результате в ячейке появится название функции с подсказкой синтаксиса. - кликнуть по кнопке вызвать диалоговое окно Аргументы функции - в поле Логическое_выражение вводим условие – А2> 1,в поле Значение_если_истина вводим уравнение для определения величины функции – $D$2*LOG10(A2)+КОРЕНЬ(A2), вполеЗначение_если_ложьвводимуравнение–2*$D$2*COS(A2)+3*СТЕПЕНЬ(A2;2). АдреснуюссылкунаячейкуD2, гдезаписанозначениеконстантыа, выполняемабсолютной. Кликнем по кнопке ОК. 4. После ввода формулы в ячейке появится число – результат расчета. Для ввода формул в оставшиеся строки столбца применим способ автозаполнения. Для этого: - выделим ячейку В2 - подведем курсор мыши к нижнему правому углу ячейки – курсор примет форму жирного черного креста; - нажать левую кнопку мыши и, не отпуская её, протащить ниже до требуемой последней ячейки диапазона – ячейка В17. Excelсам преобразует адреса формул в выделенном нами диапазоне по соответствующим адресам. 5. Выделим ячейки В2-В17 и уменьшим разрядность до трех знаков после запятой с помощью команды Уменьшить разрядность в группе команд Число вкладки Главная. 6. Оформим таблицу. Выделим всю таблицу с помощью мыши. И используя группы команд Выравнивание и Шрифт вкладки Главная, установим тип шрифта – Arial, Размер – 12, выравнивание – по центру, все границы – сплошной линией. 1.4 Графическое изображение результатов расчета Табличный процессор Excel обеспечивает возможность построения большого числа разнообразных графических изображений результатов расчетов.Диаграмма – это способ наглядного представления информации, заданный в виде таблицы чисел. Демонстрация данных с помощью хорошо продуманных диаграмм помогает лучше понять их и может существенно ускорить работу. Диаграммы создаются на основе данных, содержащихся на рабочем листе, и представляют объект, создаваемый Excel. Диаграммы Excel – динамические, т.е. связаны с данными рабочего листа, и если изменить данные, то автоматически произойдет изменение диаграммы в соответствии с новыми значениями. Создание диаграммы по рассчитанным данным реализуется следующими шагами. 1. Определить данные, по которым будет построена диаграмма. Желательно, чтобы выбранные данные включали заголовки строк и столбцов. Данные для диаграммы не обязательно должны быть расположены в одном смежном диапазоне. Чтобы выделить несколько диапазонов, нажмите клавишу и щелкните на нужных ячейках. Чтобы построить диаграмму на основе данных из разных рабочих листов, следует добавить дополнительные ряды после создания диаграммы. 2. Вставить диаграмму командой Вставка⇒Диаграммыивыбратьтипдиаграммы. Привыборетипаоткрываетсясписокподтипов, изкоторыхинеобходимовычленитнужные. Послевыбора типа и подтипа Excel построит на основе выделенных данных диаграмму выбранного типа. Рис. 3. С помощью команд контекстных вкладок Работа с диаграммамиможно изменить внешний вид диаграммы, ее структуру, добавить или удалить те или иные элементы диаграммы. Рис. Команды вкладок Excel и стандартные приемы редактирования Windows позволяют настраивать диаграммы, т.е. менять их содержимое и внешний вид. Для выполнения этих операций необходимо предварительно выделить диаграмму, щелкнув на ее поле. Копирование и перемещение диаграммы можно реализовать стандартными для Windows приемами: методом Drug&Drop (зацепи и тащи или просто – перетащи ) или командами работы с Буфером обмена. Удаление выделенной диаграммы реализуется клавишей . Добавить и удалить элементы диаграммы можно, используя команду Работа с диаграммами ⇒Макет. Командыэтойвкладки разбиты на группы, содержащие логически связанные команды. Перемещение элементов также возможно простым перетаскиванием элемента в нужное место. Форматирование элементов диаграммы возможно как с помощью команд ленточных вкладок, так и команд контекстного меню, открывающихся на выделенном (двойным щелчком) элементе диаграммы. Диалоговые окна Формат…, которые открываются при этом (рис. 16), имеют полные названия и редактируемого элемента, и настраиваемых полей. Пример 4. Построение графика реализуем на основе таблицы, построенной в примере 3. 1. Выделим столбецу: диапазон ячеек B1:В17. 2. Выберем типа диаграммы в группе Диаграммы ленточной вкладки Вставка: выберем тип График ⇒Графиксмаркерами. 3. Установимданныепогоризонтальнойоси. Работасдиаграммами⇒Конструктор⇒Данные⇒Выбратьданные. ОткроетсядиалоговоеокноВыбор источника данных. ВнемперейтинаправуюпанельПодписи горизонтальной оси. Нажать кнопку Изменить. В открывшемся диалоговом окне Подписи осиукажем диапазон А2:А12. Кликнем по кнопке ОК. 4. Вертикальная ось размечена числами с 3-мя знаками после запятой, что не очень удобно. Двойным кликом активизировать вертикальную ось в результате этого действия в правой части экрана появится окно Формат Оси. Кликнув по команде Число введем число 1 в строке Число десятичных знаков 5. Для более подробного вида графика изменим его используя макеты диаграмм. На вкладке Работа с диаграммами⇒Конструктор⇒Макеты диаграмм из выплывающего списка Экспресс-макетвыбираем макет № 10. Он добавил названия горизонтальной и вертикальной осей диаграммы. 6. Кликнем мышкой по названию диаграммы и измением на текст«Функция у(х)», дляосиабсциссназвание -x, дляординат–у. Изменим шрифт надписей на полужирный 14 пт. ПРАВИЛА ВЫПОЛНЕНИЯ И ОФОРМЛЕНИЯ КОНТРОЛЬНОЙ РАБОТЫ При выполнении контрольных работ необходимо придерживаться указанных ниже правил. Работы, выполненные без соблюдения этих правил, не зачитываются и возвращаются студенту для переработки. 1. Каждая контрольная работа должна быть выполнена в печатной форме в текстовом редакторе WORD (формат А4). На всех страницах, кроме титульного листа, необходимо оставлять поля шириной 4–5 см для замечаний рецензента. Пример оформления титульного листа показан в Приложении 1. 2. В работу должны быть включены все задачи, указанные в задании строго по положенному варианту. Контрольные работы, содержащие не все задачи задания, а также задачи не своего варианта, не зачитываются. 4. Решение задач надо располагать в порядке номеров, указанных в заданиях, сохраняя номера задач. 5. Перед решением каждой задачи надо полностью выписать условие. 6 Отчет должен содержать скан с экрана рабочего листа книги Excel (Приложение 2,3). В строке набора формулы должна отражаться формула. 7. Сдача контрольной работы проходит на лабораторной работе. ТАБЛИЦЫ ВАРИАНТОВ И ЗАДАНИЯ ДЛЯ ДЛЯ ВЫПОЛНЕНИЯ КОНТРОЛЬНОЙ РАБОТЫ Каждый студент выполняет один вариант контрольной работы, обозначенный двумя последними цифрами его шифра (номера в зачетной книжке). Задание 1. а) Рассчитать и заполнить таблицу значений функции F(x) при различных значениях аргумента x. Значения констант: A = 3 B = 0,5 C = 12 Значения аргумента x приведены в таблице: x 0,1 0,2 1,1 F(x) Строка F(x), состоящая из вычисленных значений функции, заполняется студентом. Рекомендуется числовые значения F(x) заносить в таблицу с точностью до пяти знаков. б) Построить график заданной функции. в) В ячейки D8иD9 вывести максимальное и минимальное значение функции. В ячейкуD10 вывести среднее значение функции. При выполнении расчетов использовать функции категории Статистические: МИН( ), МАКС( ), СРЗНАЧ( ). Варианты для задания 1 1. F(x) = + 2. F(x) = Ax2 + Bx3 + Cx+ – 3. F(x) = x + 3 + x3+ C + A – B + + 4. F(x) = 5. F(x) = A – B + + + C + 6. F(x) = + + C + A + B + 7. F(x) = ex+ 8. F(x) = A – Bx – Cx + lnx + – 9. F(x) = A – B + Cx – sinx – + 10. F(x) = sin2x – – A – B + Cx – сos(x) + 11. F(x) = ctgx + – 3Aх +Bх2 + С + 12. F(x) = sin2x – + A – B + Cx – 13. F(x) = + A – B + Cx – 14. F(x) = 2С++ 15. F(x) = +Ax – Bx2 – Cx3 + 16. F(x) = cos(x) + + 17. F(x) = 18. F(x) = +C + A – B + + 19. F(x) = + C + A + B + + 20. F(x) = + 8A + + 21. F(x) = + + С + A + B + 22. F(x) = ctgx + – Aх +B+10Сх + 23. F(x) = A – Bx – Cx + 10ln(x) + – 24.F(x) = A – B + Cx – 12sin(x) – + 25. F(x) = +3В+Aх + Сх3 + 26. F(x) = A – B – C –– – 27. F(x) = Aх2 + Bх + C + 28. F(x) = + A – B + Cx – 29.F(x) = A – 10B + Cx– + 30.F(x) = A – B + Cx – + 10,5 31. F(x) = sin2(x) – + A – B + Cx – 32. F(x) = sin2(x) – – A – 10B + Cx – cos(x) + 33. F(x) = A – Bx – Cx + + – 34. F(x) = A – B + + + 10С + 35. F(x) = sin(x) + + 36. F(x) = A – B + Cx – + 37. F(x) = A – B – C – – – 38. F(x) = A – Bx – Cx + + – 39. F(x) = + C + A + B + + 40. F(x) = Ax + Bx2 + Cx3 + – Задание 2. а) Вычислить значение функции y= f (x) для значений x. Область определения х, шаг изменения аргумента ∆х и значение константыАпредставлены в таблице 1. Функция у определяется уравнением: б) Построить график заданной функции. в) В ячейки D17иD18 вывести количество положительных и отрицательных значений функции. При выполнении расчетов использовать функцию СЧЁТЕСЛИ() Варианты для задания 2 Табл.1 № Варианта Область определения х ∆х A 1 {-86,74} 16 -7 2 {-71,29} 10 -5 3 {-90,29} 11 -3 4 {-34,11} 4,5 -2 5 {-19,59} 7,8 -7 6 {-58,18} 7,6 -15 7 {-51,86} 13,7 -10 8 {-30,76} 10,6 -11 9 {-78,98} 17,6 -8 10 {-20,35} 5,5 -3 11 {-23,28} 5,1 -8 12 {-75,82} 15,7 -6 13 {-94,98} 19,2 -12 14 {-70,73} 14,3 -4 15 {-67,43} 11 -16 16 {-70,71} 14,1 -3 17 {-97,20} 11,7 -5 18 {-91,20} 11,1 -6 19 {-61,40} 10,1 -8 20 {-80,54} 13,4 -9 21 {-98,69} 16,7 -10 22 {-49,69} 11,8 -14 23 {-94,13} 10,7 -2 24 {-55,17} 7,2 -3 25 {-88,100} 18,8 -4 26 {-94,47} 14,1 -5 27 {-97,28} 12,5 -11 28 {-43,60} 10,3 -9 29 {-96,36} 13,2 -8 30 {-61,19} 8 -10 31 {-15,59} 7,4 -4 32 {-30,83} 11,3 -6 33 {-43,46} 8,9 -9 34 {-57,51} 10,8 -6 35 {-49,26} 7,5 -7 36 {-86,40} 12,6 -11 37 {-62,51} 11,3 -15 38 {-55,40} 9,5 -18 39 {-19,89} 10,8 -12 40 {-23,93} 11,6 -9 ПРИЛОЖЕНИЕ 1 Образец оформления титульного листа КГЭУ МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ Федеральное государственное бюджетное образовательное учреждение высшего образования «КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ ЭНЕРГЕТИЧЕСКИЙ УНИВЕРСИТЕТ» (ФГБОУ ВО «КГЭУ») Кафедра «Информатика и информационно-управляющие системы» Контрольная работа по дисциплине «Информационные и компьютерные технологии» Студент Шкапин А.В. Группа ЗЭЭ-1-17 Шифр _______ Казань 2017 ПРИЛОЖЕНИЕ 2 Пример отчета задания №1. ПРИЛОЖЕНИЕ 3 Пример отчета задания №2. СОДЕРЖАНИЕ Предисловие ....................................................................................................... 3 1. Работа в приложении Microsoft Excel............................................................ 3 1.1 Форматирование ячеек............................................................................. 6 1.1.1 Представление данных...................................................................... 8 1.1.2 Размещение данных........................................................................... 8 1.1.3 Выбор параметров шрифта................................................................ 9 1.1.4 Оформление границ и поля ячейки................................................. 10 1.2 Расчеты в Excel.......................................................................................... 13 Вставка функций и формулы......................................................................... 16 1.3 Расчеты с использованием специальных функций.............................. 26 1.3.1 Расчеты с использованием статистических функций..................... 27 1.3.2 Расчеты, включающие логические суждения.................................. 33 1.4 Графическое изображение результатов расчета..................................... 39 Правила выполнения и оформления контрольной работы......................... 44 Таблицы вариантов и задания для выполнения контрольной работы……... 45 Приложение 1. Образец оформления титульного листа ................................. 53 Приложение 2. Пример отчета задания №1.................................................... 54 Приложение 3. Пример отчета задания №2............................... .................... 55 ––––––––––––––––––––––––––––––––– Учебно-методическое пособие Информационные и компьютерные технологии Для студентов заочной формы обучения технических направлений подготовки бакалавров. Направления: 13.03.02 «Электроэнергетика и электротехника» 13.03.01 «Теплоэнергетика и теплотехника» Составители: Петрова Наталья Константиновна, Бикеева Наталья Геннадьевна, Куценко Светлана Мунавировна Кафедра «Информатика и информационно-управляющие системы» КГЭУ Редактор редакционно-издательского отдела ___________ Компьютерная верстка _____________ Подписано в печать Формат 6084/16. Бумага ВХИ. Гарнитура «Times». Вид печати РОМ. Усл. печ. л. 1,86. Уч.-изд. л. 2,06. Тираж ______ экз. Заказ № Редакционно-издательский отдел КГЭУ, 420066, Казань, Красносельская, 51