Практическая работа по Excel. Практическая работа_35. Практическая работа 35 Организация вычислений в динамических (электронных) таблиц. Использование ссылок. Вставка функций
 Скачать 441.19 Kb.
|
|
Практическая работа № 35 «Организация вычислений в динамических (электронных) таблиц. Использование ссылок. Вставка функций» Цель работы: овладеть понятиями и знаниями о возможностях электронных таблиц, простыми навыками работы с таблицами, ссылками, разными типами функций. Теоретическая часть Иметь дело с обширными таблицами, содержащими большие объемы данных, приходится во многих областях жизни. В частности, это относится ко всем видам финансовой и учетной деятельности. В докомпьютерную эпоху подобного рода таблицы приходилось вести в ручную. Ситуация изменилась в 1979 году, когда Дэниэл Бриклин совместно с Робертом Фрэнкстоном разработали первую в мире электронную таблицу VisiCalc 93 (Визуальный калькулятор). Программа VisiCalc для компьютера Apple II стала очень популярной, превратив персональный компьютер в массовый инструмент для бизнеса. Автоматизация табличных расчетов во много раз повысила эффективность и качество работы. Способность электронных таблиц быстро и точно производить автоматические вычисления используют не только бухгалтеры и кладовщики. Без электронных таблиц не обходятся участники бирж, руководители брокерских контор, банков и другие финансовые менеджеры. С помощью электронных таблиц можно моделировать реальные ситуации и оценивать получающиеся результаты. На сегодняшний день электронные таблицы являются мощным средством обработки больших массивов числовых данных. При этом электронные таблицы обеспечивают проведение динамических вычислений, т. е. пересчет по формулам при введении новых чисел. Появилась возможность наглядного представления данных и выявления между ними определенных зависимостей с помощью графиков и диаграмм. Электронные таблицы - это работающее в диалоговом режиме приложение, хранящее и обрабатывающее данные в прямоугольных таблицах. По умолчанию файлы в электронных таблицах называются Книгами. Книги состоят из рабочих листов, с которыми можно производить действия копирования, вставки новых листов, удаления, переименования. Рабочее поле электронной таблицы состоит из столбцов и строк. Заголовки столбцов обозначаются латинскими буквами или сочетаниями букв (А, С, АВ и т. п.), заголовки строк - числами (1, 2, 3 и т. д.). На пересечении столбца и строки находится ячейка, которая имеет индивидуальный адрес. Адрес ячейки составляется из заголовка столбца и заголовка строки, например Al, В5, ЕЗ. Ячейка, с которой производятся какие-то действия, выделяется рамкой и называется активной. В процессе работы с электронными таблицами достаточно часто требуется выделить несколько ячеек - диапазон ячеек. Диапазон задается адресами ячеек верхней и нижней границ диапазона, разделенными двоеточием (например, диапазон С3:Е4).  Кнопка «Вставить лист» Строка состояния Ярлык рабочего листа Активная ячейка Строка заголовка Строка формул Поле имени Рабочая область окна Лента В работе с электронными таблицами можно выделить три основных типа данных: числа, текст и формулы. Для представления чисел могут использоваться несколько различных форматов (числовой, экспоненциальный, дробный и процентный). Существуют специальные форматы для хранения дат и времени, а также финансовый и денежный форматы. Текстом в электронных таблицах является последовательность символов, состоящая из букв, цифр и пробелов. Формула должна начинаться со знака равенства и может включать в себя числа, имена ячеек, функции и знаки математических операций. Однако в формулу не может входить текст. В процессе ввода формулы она отображается как в самой ячейке, так и в строке формул. В формулах могут использоваться ссылки на адреса ячеек. Существуют два основных типа ссылок: относительные и абсолютные. При перемещении или копировании формулы из активной ячейки относительные ссылки автоматически изменяются в зависимости от положения ячейки, в которую скопирована формула. Абсолютные ссылки в формулах используются для указания фиксированного адреса ячейки. При перемещении или копировании формулы абсолютные ссылки не изменяются. В абсолютных ссылках перед неизменяемыми именем столбца и номером строки ставится знак доллара (например, $А$1). В формуле можно использовать смешанные ссылки, в которых координата столбца относительная, а строки - абсолютная (например, А$1), или, наоборот, координата столбца абсолютная, а строки - относительная (например, $В1). 94 Формулы могут включать в себя не только адреса ячеек и знаки арифметических операций, но и функции. Электронные таблицы имеют несколько сотен встроенных функций, которые подразделяются на категории: Математические, Статистические, Финансовые, Дата и время и т. д. Практическая часть Задание 1. Знакомство с интерфейсом табличного процессора MS Excel. Запустите табличный процессор MS Excel. Ознакомьтесь с тем, как организовано окно табличного процессора. Сохраните пустую рабочую книгу с именем Ваша_фамилия.xlsx. Задание 2. Работа с формулами. Относительные ссылки в MS Excel. Все вычисления в Excel называются формулы, и все они начинаются со знака «равно» (  ). Пусть необходимо посчитать в ячейке сумму  . Если нажать на любую ячейку и внутри напечатать , а затем нажать клавишу Enter на клавиатуре, то ничего не посчитается – в ячейке будет написано . Табличный процессор воспримет введённое просто как текст.А если напечатать в ячейке  и нажать клавишу Enter, то табличный процессор произведёт вычисления и в ячейке будет показан результат  . В Excel можно использовать и другие знаки для счета (-, *, /, ^возведение в степень, %) Пусть пользователю надо спланировать посещение продуктового магазина. Составим таблицу для расчёта необходимой суммы денег. При выполнении занятия на каждом шаге делайте скриншоты.  Если есть необходимость, можно отформатировать таблицу с помощью различных кнопок на вкладке Главная:  Например, можно отформатировать таблицу следующим образом:
Чтобы получить сумму по каждой товарной позиции, необходимо умножить количество на цену каждого продукта, то есть умножить значения в столбце B на значения в столбце C в строках 2, 3, 4, 5. Для этого вводим «=» в ячейке D2. Далее щёлкаем на ячейку B2 и знак умножения «*» (будем умножать количество):  Затем нажмите на ячейку C2 (умножаем на цену), получим:  Далее нажимаем клавишу Enter на клавиатуре для фиксации результата. В ячейке D2 получим результат расчётов:  При выделении ячейки D2 в строке формул будет показана формула, по которой ведётся расчёт:   Для того, чтобы быстро рассчитать суммы по остальным строкам, выделяем ячейку с формулой, которую необходимо «размножить», наводим курсор на маркер автозаполнения (он при этом меняет вид, становится тонким чёрным крестиком) и «протягиваем» формулу на необходимые ячейки (в нашем случае D3:D5):   В итоге в столбце «Сумма» получим следующие формулы:  Как можно видеть, в зависимости от того, в какой строке находится формула, меняются адреса перемножаемых ячеек. Меняются относительно местоположения ячейки, в которой находится формула. Такие ссылки называются относительными. Относительная ссылка – это обычная ссылка, которая содержит в себе букву (столбец) и номер (строка), например, D14, G5, A3 и т.п. Основная особенность таких ссылок заключается в том, что при копировании (заполнении) ячеек в электронной таблице, формулы, которые в них находятся, меняют адрес ячеек относительно нового места. По умолчанию все ссылки в Excel являются относительными ссылками Для того, чтобы подсчитать общую сумму, которую придётся потратить на продукты, изменим таблицу, добавив итоговую строку:  Для того, чтобы суммировать все значения в столбце D, выделяем все ячейки D2:D6:  А затем нажимаем кнопку автосуммирования на вкладке Главная или на вкладке Формулы:     В ячейке D6 получим результат суммирования. При выделении этой ячейки в строке формул будет отражена формула, которую Excel автоматически добавил при нажатии кнопки автосуммирования:   При суммировании использовалась встроенная функции СУММ(). Таких функций в Excel очень много, их можно найти на вкладке Формулы, они сгруппированы по темам:  Либо для вставки функции можно вызвать Мастер функций, нажав на соответствующую кнопку в строке формул:  После нажатия откроется диалоговое окно Мастера функций:  Задание 3. Абсолютные ссылки в MS Excel Пусть нам необходимо рассчитать ту же сумму покупки продуктов, но, например, в долларах США. Изменим нашу таблицу, добавив ещё один столбец и денежные единицы измерения. Также добавим ячейку с сегодняшним курсом доллара США:  В ячейку E2 введём формулу для пересчёта суммы в рублях в долларовый эквивалент:  Получим следующий результат:  Если теперь мы попробуем скопировать формулы из ячейки E2 в ячейки E3:E6 так, как это было сделано в предыдущей задаче. Однако в ячейках будет отображено сообщение об ошибке #ДЕЛ/0!: Такая ошибка возникает в Microsoft Excel, когда производится деление на ноль.  Если посмотреть на полученные формулы, можно увидеть, что деление производится на ячейку, в которой значения не введены:  Это произошло из-за использования относительных ссылок: вследствие копирования формулы номер строки изменяется как для делителя, так и для делителя. Для наших целей должно быть иначе: в делителе номер строки должен меняться, а делить мы должны всегда на одно и тоже значение – текущий курс доллара. Для фиксирования номера строки используем абсолютную ссылку. Абсолютные ссылки используются в случае, когда ссылка на ячейку должна остаться неизменной при заполнении или копировании ячеек. Абсолютная ссылка обозначается знаком $ в координатах строки и столбца, например, $A$3. Знак доллара фиксирует ссылку на данную ячейку, так что она остается неизменной независимо от того, куда смещается формула. Другими словами, использование $ в ссылках ячейках позволяет скопировать формулу в Excel без изменения ссылок. Поставим знак $ перед номером строки в формуле в ячейке E2:  А затем «протягиваем» эту формулу на нижележащие ячейки:  В ячейках E2:E6 находятся теперь следующие формулы:  Для того, чтобы наша таблица выглядела более профессионально, отформатируем в ней числовые значения. Сначала применим денежный формат к значениям в столбцах C и D. Выделим нужные ячейки и вызываем контекстное меню, в котором выбираем пункт Формат ячеек. Откроется диалоговое окно, в котором на вкладке Число выберем формат Денежный:  После подтверждения сделанных изменений ячейки примут следующий вид:  Аналогично форматируем и значения в столбце Е, только в списке Обозначение выбираем значок необходимой нам валюты – американского доллара:   В итоге получим отформатированную таблицу:  Задание 4. Арифметические функции в Excel. В таблицу собраны данные о крупнейших озерах мира. Найти глубину самого мелкого озера, площадь самого обширного озера и среднюю высоту озер над уровнем моря. Создайте таблицу по образцу:
В ячейку А8 введите – Минимальная глубина В ячейку А9 введите – Максимальная площадь В ячейку А10 введите – Средняя высота Выделите ячейку В8 и выполните команду Вставка-Функция…В открывшемся окне выберите категорию статистические, а в окне Функция – МИН →ОК. В окне число1 запишите C1:C7 нажмите ОК. Выделите ячейку В9 и выполните команду Вставка-Функция…В открывшемся окне выберите категорию статистические, а в окне Функция – MAX →ОК. В окне число1 запишите В1:В7 нажмите ОК. Выделите ячейку В10 и выполните команду Вставка-Функция…В открывшемся окне выберите категорию статистические, а в окне Функция – СРЗНАЧ →ОК. В окне число1 запишите D1:D7 нажмите ОК. Отформатируйте таблицу. Задание 5. Табулирование функций. А. На отрезке [0;2] вычислить значения функции f(x) = cosx+x с шагом 0,2. Заполните таблицу по образцу:
В ячейку А4 введите формулу А3+$В$1. Используя маркер заполнения, заполните блок ячеек А4:А13. В ячейку В3 введите формулу = COS(А3)+A3. Используя маркер заполнения, заполните блок ячеек В3:В13. Отформатируйте таблицу. Б. Составьте таблицу значений функции у =(х-5)2 на отрезке [-3; 3]. Таблица значений функции у =(х-5)2
Для составления формулы воспользуйтесь Мастером функций. Выделите ячейку, в которую нужно вставить первое значение функции. Введите знак равенства и выполните команду [Вставка-Функция] или выберите кнопку fх В окне диалога <Мастер функций> в категории «Математические» выберите функцию «Степень». Введите значение аргумента и значение показателя степени. Заполните ряд функций. Для того чтобы в заголовке ввести показатель степени, используйте опцию верхний индекс ([Формат - Ячейки], вкладка Шрифт). Задание 6. Создайте таблицу по образцу. Вычислите средние показатели территории и численности населения по Москве, Примените функции для определения минимальных и максимальных значений по каждому показателю.
Для вычисления средних значений в столбце, примените функцию «СРЗНАЧ» из категории Статистические. Для определения минимальных (максимальных) значений в столбце, примените функцию «МИН» («МАКС») из категории Статистические. Условия выполнения задания 1. Место (время) выполнения задания: задание выполняется в аудитории во время занятия 2. Максимальное время выполнения задания: 45 мин. 3. Вы можете воспользоваться конспектом лекций, учебником. |