Методичка по СИТу (лабораторные работы для 1 курса). Методичка. Информационные технологии Лабораторный практикум
 Скачать 2.57 Mb.
|
|
Задание 3. Применение абсолютных ссылок. Построение круговой диаграммы Скопируйте созданную таблицу на новый лист и измените ее так, чтобы она отражала стоимость хранимых на складах товаров: Цены в условных единицах занесите в ячейки H5:J5. Формула в ячейке F3: =$H$5*B3+$I$5*C3+$J$5*D3. Адреса ячеек со знаком $ не будут изменяться при автозаполнении. Такая ссылка называется абсолютной, знак $ будет вставлен автоматически, если после ввода адреса ячейки нажать клавишу [F4]. Для построения диаграммы Стоимость мониторов выделите два несмежных диапазона (произведите выделение с нажатой клавишей [Ctrl]) A2:A7 и E2:E7 и выберите тип диа- граммы Круговая объёмная. На вкладке Формат подписей данных установите галочки в имя категории, доля и линии выноски. Положение подписи установите по ширине. Назовите новый лист "Таблица 2". Задание 4. Работа со ссылками на смежные листы Для последней таблицы введите столбец стоимости оборудования в рублях. Курс доллара разместите на новом листе (назовите лист, например, Курс_доллара). Тогда ссылки на курс доллара должны содержать дополнительно номер или имя листа, например, для раз- мера курса доллара, занесенного в ячейку C3 листа Курс_доллара, может быть сделана ссылка: =F2*Курс_доллара!$С$3. Если присвоить ячейке С3 имя, например, курс, то ссылки на номер листа можно не делать, и формула примет вид: =F2*курс. Задание 5. Действия с датами Перейдите на новый лист (при необходимости вставьте лист) и дайте ему название "Дата". Подсчитайте количество прожитых Вами к данному моменту дней, оформите расчеты в виде таблицы, содержащей дату рождения, текущую дату и формулу, соответствующую 33 разности текущая дата – дата рождения. К результату примените числовой формат ячеек Общий. Текущую дату Excel вставит в ячейку при вызове функции СЕГОДНЯ(). Функции работы с датами представлены в категории Дата и время вкладки Формат ячеек. Определите день недели для любой памятной Вам даты (например, для дня Вашего рож- дения), пользуясь функцией ДЕНЬНЕД(). В качестве аргумента функции укажите дату, заключив ее в кавычки, например ДЕНЬНЕД(“12.03.86”) или укажите адрес ячейки, со- держащей дату. Подсчитайте количество прожитых Вами часов. Задание 6. На новом листе постройте одну из таблиц: календарь на текущий год, таблицу годового дохода сотрудника фирмы с указанием процента ежемесячного дохода от общей суммы, таблицу в соответствии с заданием преподавателя. Задание для самостоятельной работы С помощью настройки параметров ( Файл → Параметры ) выполните следующие действия: перейдите от просмотра значений формул к просмотру самих формул (вкладка Вид). "Проявить"/убрать все формулы на листе можно и сочетанием клавиш [CTRL+`]. Введите/отмените пароль для открытия Вашего файла (вкладка Сведения). Установив защиту файла, не забудьте пароль! Переименуйте получившиеся листы в соответствии с содержанием. Сохраните книгу. Контрольные вопросы 1. Какие виды работ позволяет выполнить табличный процессор Excel? 2. Что такое диапазон данных? 3. Как выделить несмежные диапазоны для совместного их форматирования? 4. Какие виды диаграмм можно построить в Excel? 5. Как Excel работает с датами? 6. Какие преимущества дает использование имен ячеек? 7. Какое расширение имеют файлы, созданные с помощью Excel? 8. Какие параметры работы Excel может настроить пользователь? 9. Как защитить свой файл от постороннего доступа? 10. Какие последовательности поддерживает Excel при автозаполнении? 11. Как ввести собственный список (последовательность) для автозаполнения? 34 Лабораторная работа № 7 MS Excel. Расчеты с условиями. Работа сосписками Цель работы: - ознакомиться с возможностями Excel для выполнения расчетов с условиями; - освоить вычисления с помощью условных функций и с помощью функции услов- ного суммирования для массивов; - освоить применение формулы массива для выполнения операций с диапазоном ячеек; - освоить прием условного форматирования для автоматического выделения диапа- зона ячеек; - освоить приемы работы с базами данных и списками. Общие сведения Расчеты с условиями Для выполнения расчетов, требующих проверки условий, в Excel имеется ряд функций. Функция ЕСЛИ() Синтаксис: ЕСЛИ(<условие>;<выражение1>;<выражение2>) Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. <выражение1> и <выражение2> могут быть числами, формулами или текстами. Текст должен быть заключен в кавычки. Выполнение: если условие истинно, значение ячейки определяет <выражение1>, в про- тивном случае – <выражение2>. Логическое выражение состоит из констант, адресов или имен ячеек, знаков операций от- ношений (<, >, =, <=, >=,<>) и логических операций И, ИЛИ, НЕ. Логические операции в Excel используются как логические функции, при их вызове запи- сывается знак операции, затем в круглых скобках перечисляются логические операнды, разделяемые точкой с запятой, например:ИЛИ(A4>2;A4<-2). Пример записи формул, содержащих условную функцию: Пусть в таблице хранится информация о зачислении слушателей на курсы: A B C D 1 Список слушателей 2 ФИО Баллы Учебное заведение Информация о зачислении 3 Петров Н.Г 11 лицей №1 не зачислен 4 Алексеев А.Л. 13 школа №12 зачислен 5 Андреева Е.Н. 12 лицей №1 зачислен 6 Тихонов К.П. 13 лицей №1 зачислен 7 Ветрова В.В. 12 лицей №4 зачислен Тогда при условии зачисления тех, кто набрал не менее 12 баллов, в ячейку D3 введена формула =ЕСЛИ(B3>=12;"зачислен";"не зачислен"), 35 а при условии зачисления тех, кто набрал не менее 12 баллов и учится в лицее №1, в ячейку D3 должна быть введена формула =ЕСЛИ(И(B3>=12;C3="лицей №1");"зачислен";"не зачислен"). Функции СЧЁТЕСЛИ() и СУММЕСЛИ() Функция СЧЁТЕСЛИ(). подсчитывает количество ячеек внутри интервала, удовлетворя- ющих заданному критерию. Синтаксис: СЧЁТЕСЛИ(интервал;критерий) Критерий - критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать в заданном интервале. Например, критерий может быть запи- сан следующим образом: 32, "32", ">32", "яблоки". Пример: Пусть имеется таблица, содержащая информацию о сборе фруктов: А В С 1 Сбор фруктов 2 Фамилия Вид Вес, кг 3 Иванов яблоки 32 4 Круглов апельсины 57 5 Пономарев персики 75 6 Алексеев яблоки 86 Тогда функция СЧЁТЕСЛИ(A3:С6;"яблоки") возвращает значение 2 (количество сборщи- ков яблок), а СЧЁТЕСЛИ(A3:C6;">55") возвращает значение 3 (количество сборщиков, собравших более 55 кг фруктов). Точно такие же результаты дадут функции СЧЁТЕСЛИ(B3:B6;"яблоки") и СЧЁТЕСЛИ(C3:C6;">55"). Обратите внимание на то, что условие, содержащее знаки операций отношений, должно заключаться в кавычки. Функция СУММЕСЛИ() действует аналогично функции СЧЕТЕСЛИ(), но вычисляет сумму содержимого ячеек заданного диапазона. Синтаксис: СУММЕСЛИ(интервал выбора; критерий; интервал суммиро- вания). При выполнении этой функции суммируются значения только тех ячеек из интервала суммирования , для которых соответствующие значения в интервале выбора удовле- творяют критерию. Например, для приведенной выше таблицы с данными о сборе фруктов по формуле =СУММЕСЛИ(B3:B6;"яблоки";C3:C6) вычисляется вес всех собранных яблок. Если аргумент интервал суммирования опущен, то суммируются значения ячеек из диапазона интервал выбора. Например, для той же таблицы значение функции СУММЕСЛИ(С3:С6;">50") равно 218. Функция условного суммирования для массивов При создании условий для выбора из нескольких диапазонов удобно пользоваться форму- лами для работы с массивами. Массив – прямоугольные диапазоны формул или ячеек, которые Excel обрабатывает как единую группу. Например, для подсчета количества сборщиков, собравших яблок больше 50 кг можно за- писать формулу {=СУММ((B3:B6="яблоки")*(C3:C6>50))}. Фигурные скобки здесь обозначают операцию с массивами и получаются, если при вводе формулы нажать не клавишу [Enter], а одновременно три клавиши [Ctrl+Shift+Enter]. В данном применении функции СУММ знак * соответствует логической операции И, а 36 знак + - операции ИЛИ. Например, для таблицы "Мониторы. Учет" (см. задание 1 преды- дущей лабораторной работы) при подсчете количества складов, имеющих мониторов лю- бого вида меньше 5 шт, следует применить формулу {=СУММ((B3:B7<5)+(C3:C7<5)+(D3:D7<5))}. Результатом будет значение 1. Для подсчета количества учащихся лицея №1, набравших не менее 12 баллов (по данным таблицы "Список слушателей") можно применить формулу {=СУММ((B3:B7>=12)*(C3:C7="лицей №1"))}. Результатом будет значение 2. Работа с массивом должна быть выполнена и для функции ЕСЛИ(), когда в качестве усло- вия проверяются значения ячеек из диапазона. Условное форматирование В Excel возможно применение разных форматов к ячейке в зависимости от хранимых в ней данных. Для задания условного формата используется команда Главная→Условное форматирова- ние Например, для того, чтобы ячейки со словом зачислен были залиты красным цветом, следует выделить исходный диапазон ячеек, затем, выполняя команду условного форма- тирования, в диалоговом окне Условное форматирование сформировать условие: значе- ние равно зачислен. Работа с базами данных и списками Excel предоставляет возможность работать с базами данных, которые здесь называются списками. Список – таблица, построенная по принципу базы данных: множество одно- типных строк-записей, разделенных на поля. Одна или две верхних строки списка должны содержать заголовки столбцов (имена полей). Приведенная ниже таблица, содержащая данные о сотрудниках, представляет собой список: Сотрудники ФИО Возраст Должность Стаж Оклад, руб Алексеев Н.Г 22 программист 1 2000 Пронина Е.Е. 35 бухгалтер 15 1800 Воронин Н.П. 41 гл. бухгалтер 19 4500 Алексеева А.Г. 19 лаборант 2 1200 Имена полей выделены серым цветом. Запись хранит данные об одном сотруднике. Основные функции при работе с любой базой данных – поиск информации по определен- ным критериям и сортировка данных. В Excel все операции по работе с данными содержатся в меню Данные. Сортировка данных Команда Данные→Сортировка позволяет упорядочивать данные в списке. Курсор должен находиться в области списка. В диалоговом окне Сортировка следует выбрать последова- тельность полей сортировки. Например, при выборе в поле Сортировать по: возраст, а в поле Затем по: ФИО для приведенного выше списка получим: Сотрудники ФИО Возраст Должность Стаж Оклад, руб Алексеева А.Г. 19 лаборант 2 1200 Алексеев Н.Г 22 программист 1 2000 Пронина Е.Е. 35 бухгалтер 15 1800 37 Воронин Н.П. 41 гл. бухгалтер 19 4500 Фильтрация данных Команда Данные→Фильтр позволяет выделять нужные записи. Фильтрация возможна че- рез автоматический фильтр Автофильтр (для простых условий отбора) и чере з Расширен- ный – ручной (для более сложных условий отбора). Курсор должен находится в области списка. В отфильтрованном списке отображаются только строки, отвечающие условиям отбора, заданным для столбца. При использовании Автофильтра на именах полей появятся кнопки, нажимая на которые можно задавать критерии фильтрации. В появившемся подменю пункт Первые 10 оставля- ет 10 наибольших или наименьших элементов в списке, пункт Все оставляет все элементы списка, а пункт Условие… вызывает диалоговое окно, в котором можно установить пара- метры фильтрации. Команда Расширенный фильтр позволяет: задавать условия, соединенные логическим оператором ИЛИ для нескольких столбцов (для объединения условий с помощью логического оператора И проще два- жды использовать команду Автофильтр ); задавать три и более условий для конкретного столбца с использованием, по край- ней мере, одного логического оператора ИЛИ. Например, можно оставить на экране записи о сотрудниках, чьи фамилии начинаются с букв А, В или Л; задавать вычисляемые условия; извлекать строки из списка и вставлять эти копии в другую часть текущего листа. При извлечении строк с помощью Автофильтра копирование и вставку автоматически выполнить невозможно. При использовании расширенного фильтра необходимо в диалоговом меню указать три области: Исходный диапазон – диапазон данных из списка, для которого необходимо выпол- нить фильтрацию; Диапазон условий – диапазон ячеек рабочего листа, в котором указаны критерии от- бора; Поместить результат в диапазон – диапазон рабочего листа, предназначенный для вывода отобранных записей. Установка диапазона возврата результатов становится доступной только при выбранном переключателе скопировать результат в другое ме- сто. В противном случае список отфильтруется на месте исходного диапазона. Правила для задания текстовых условий: единственная буква – поиск всех значений, начинающихся с этой буквы; символы < или > - поиск всех значений, которые находятся по алфавиту до или после введенного значения; формула =”=текст” - поиск всех значений, которые точно совпадают со строкой символов текст. Например, при задании =”=Алексеев”, будут найдены все строки, содержащие фамилию Алексеев, при задании фамилии Алексеев без формулы выберутся также строки с фамилиями Алексеева, Алексеевич и т. д. Пример результата фильтрации с использованием фильтра для выбора всех сотрудников младше 20 лет, имеющих стаж работы меньше 5 лет, или старше 40 лет со стажем работы меньше 20 лет: Сотрудники ФИО Возраст Должность Стаж Оклад, руб Алексеева А.Г. 19 лаборант 2 1200 Алексеев Н.Г 22 программист 1 2000 Исходный диапазон 38 Пронина Е.Е. 35 бухгалтер 15 1800 Воронин Н.П. 41 гл. бухгалтер 19 4500 Возраст Стаж < 20 <10 >40 <20 ФИО Возраст Должность Стаж Оклад, руб Алексеева А.Г. 19 лаборант 2 1200 Воронин Н.П. 41 гл. бухгалтер 19 4500 Отменить фильтрацию можно командой Данные→Фильтр→Очистить Содержание работы Задания выполняйте на отдельных листах в книге с предыдущей лабораторной работой. Задание 1 Постройте таблицу "Список слушателей", приведенную в описании лабораторной работы, и выполните для нее все приведенные в описании примеры вычислений с применением условных функций. Добавьте в эту таблицу еще три столбца с оценками по дисциплинам Информатика, Ма- тематика, Английский язык перед столбцом Баллы (оценки выставляются по пятибалль- ной системе), подсчитайте значение среднего балла в столбце Баллы. Добавьте еще не- сколько строк. Заполните столбец с информацией о зачислении следующим образом: за- численными считаются все, набравшие не менее 13 баллов либо набравшие 12 баллов, но имеющие по математике оценку 5. Ячейки столбца о зачислении должны иметь значение зачислен, остальные ячейки должны остаться пустыми. Зачисленных слушателей выделите полужирным шрифтом с помощью условного форма- тирования. Набравших не менее 11 баллов и не более 12 баллов выделите в столбце Баллы заливкой зеленого цвета. Выполните сортировку: по алфавиту, по среднему баллу (по убыванию). Подсчитайте общее количество зачисленных, количество зачисленных учащихся школы № 12, количество набравших не менее 11 и не более 12 баллов с помощью функции СЧЕТЕСЛИ и функции суммирования для массивов. Выполните фильтрацию данных: с помощью команды Автофильтр, оставив видимыми только тех, кто зачислен на курсы и учится в лицее №1; с помощью команд Автофильтр и Расширенный фильтр, выделить всех зачислен- ных, обучающихся в лицее № 1 или в школе № 12, с помощью команды Расширенный фильтр, выделив всех учащихся лицея № 1, по- лучивших пятерку по математике и всех учащихся лицея № 4, получивших пятерку по информатике. Задание 2 Постройте таблицу с данными о сборе фруктов, добавьте в нее несколько новых строк (не вводите новые виды фруктов). Выполните все приведенные в описании примеры расчетов. Вычислите вес каждого вида собранных фруктов с помощью функции СУММЕСЛИ. Вычислите вес собранных фруктов в английских фунтах (1 фунт = 0,4536 кг) с помощью формулы для массивов: введите новую ячейку с текстом Вес,фунты, Диапазон условий Диапазон результата 39 выделите диапазон значений для размещения результата (столбец Вес,фунты), наберите нужную формулу, сделав ссылку на весь диапазон исходных данных (Вес, кг), завершите ввод формулы нажатием [Ctrl+Shift+Enter]. Задание 3 Постройте таблицу по заданию преподавателя, и выполните необходимые расчеты с ис- пользованием условных функций, Задание 4 Постройте базу данных в соответствии с заданием преподавателя. Данные вводите с по- мощью формы. Выполнить сортировку и фильтрацию данных в списке по заданию преподавателя. Контрольные вопросы 1. Какие логические функции имеются в Excel? 2. Что такое массив, как выполняется работа с массивами в Excel? 3. В чем заключается различие между функцией СУММЕСЛИ() и функцией условно- го суммирования для массивов? 4. Для чего используется условное форматирование? 5. Дайте определение базы данных. 6. Как называется база данных в Excel? 7. Как должен быть оформлен список в Excel? 8. Какие действия с базами данных выполняются в Excel? 9. В чем заключаются различия между сортировкой и фильтрацией? 10. В каких случаях используется расширенный фильтр? |