учебник. Учебник по EXCEL. Практическая работа 1 первое знакомство с excel цели работы познакомиться с основными понятиями электронной таблицы
 Скачать 7.16 Mb.
|
|
Формат функций И(<логическое выражение1>;<логическое выражение 2>,....) ИЛИ(<логическое выражение 1>;<логическое выражение 2>,,...) В скобках может быть указано до пятидесяти логических выражений. Функция И принимает значение "Истина", если одновременно все логические выражения истинны. Функция ИЛИ принимает значение "Истина", если хотя бы одно из логических выражений истинно. Пример. Определить, входит ли в заданный диапазон (5;10) число, содержащееся в ячейке Н10. Ответ 1(если число принадлежит диапазону) и 0 ( если число не принадлежит диапазону) должен быть получен в ячейке Н12. В ячейку Н12 вводится формула: - ЕСЛИ (И (Н10>5; Н10<10); 1; 0) В ячейке H12 получится значение 1, если число принадлежит диапазону, и значение 0, если число вне диапазона. ЗАДАНИЕ 6. Заполните формулами таблицу для обработки одномерных массивов (данные по каждому году). 6.1. Ячейку G11 отведите для ввода года и присвойте ей имя год (команда Вставка –Имя - Присвоить) Именованная ячейка будет адресоваться абсолютно. При вводе в формулу имени ячейки необходимо выбрать это имя в списке и щелкнуть на нем. Excel вставит указанное имя в формулу. 6.2. В ячейку G12 с использованием Мастера функций введите формулу: =ЕСЛИ(год=1992;МАКС(B5:B16);ЕСЛИ(год=1993;МАКС(C5:C16);ЕСЛИ(год=1994;МАКС(D5:D16);"данные отсутствуют"))) Проанализируйте формулу. Несмотря на сложный синтаксис, смысл ее очевиден. В зависимости от года, который вводится в именованную ячейку год, определяется максимум в том или ином диапазоне таблицы 1. Диапазон В5:В16 - это одномерный массив данных за 1992 г.; С5:С16 - массив данных за 1993г; D5:D16 - зa 1995 г. 6.3.Замените в формуле в ячейке G11 относительную адресацию ячеек на абсолютную. Для выполнения следующих выборок эту формулу можно скопировать в ячейки G13 :G16 и отредактировать, заменив функцию МАКС на требуемые по смыслу. Но прежде необходимо заменить относительную адресацию ячеек на абсолютную, иначе копирование формулы будет производиться неправильно. =ЕСЛИ(год=1992;МАКС($В$5:$В$1б);ЕСЛИ(год=1993;МАКС($С$5:$С$16);ЕСЛИ(год=1 995;MAKC($D$5:$D$16); "данные отсутствуют"))) Внимание! Все массивы в формуле адресованы абсолютно, ячейка ввода года также адресована абсолютно. 6.4.Скопируйте формулу из ячейки G12 в ячейки G13:G16. 6.5.Отредактируйте формулы в ячейках G13:G16, заменив функцию МАКС на требуемые по смыслу. 6.6. Отредактируйте формулу в ячейке G16. Смените функцию МАКС на функцию СЧЕТЕСЛИ и добавьте критерий " <10 . После редакции функция должна иметь вид: =ЕСЛИ{год=1992;СЧЕТЕСЛИ($В$5.$В$16;"<Ю");ЕСЛИ(год=1993;СЧЕТ ЕСЛИ($С$5:$С$16;"<Ю");ЕСЛИ(год=1995;СЧЕТЕСЛИ($D$5:$D$16;"<10");" данные отсутствуют" ))) 6.7.Введите в ячейку G11 год: 1992. 6.8. Проверьте правильность заполнения таблицы 3 значениями ЗАДАНИЕ 7.Сохраните результаты работы под тем же именем work_5.xls в личном каталоге. ЗАДАНИЕ 8.Представьте данные таблицы 1 графически, расположив диаграмму на отдельном рабочем листе. 8.1.Выделите блок A5:D16 и выполните команду меню Вставка - Диаграмма - На новом листе. 8.2.Выберите тип диаграммы и элементы оформления по своему усмотрению. 8.3. Распечатайте диаграмму, указав о верхнем колонтитуле фамилию, а в нижнем — дату и время. ЗАДАНИЕ 9. Вернитесь к рабочему листу с таблицами. ЗАДАНИЕ 10. Подготовьте таблицу к печати, воспользовавшись предварительным просмотром печати. 10.1.Выберите альбомную ориентацию и подберите ширину полей так, чтобы все 3 таблица умещались на странице. 10.2. Уберите сетку. 10.3.Укажите в верхнем колонтитуле фамилию, а в нижнем — дату и время. ЗАДАНИЕ 11.Сохраните результаты роботы под тем же именин work5.xls в личном каталоге. ЗАДАНИЕ 12. Распечатайте результаты работы на принтере. ЗАДАНИЕ 13(дополнительное). Определите количество месяцев в каждом году с количеством осадков в пределах (>20 ;<80) мм и в пределах (< 10; >100) мм. 13.1. Создайте вспомогательную таблицу для определения месяцев с количеством осадков в пределах (>20;<80)  13.2. В ячейку В:21 занесите формулу: =ЕСЛИ(И(В5>20;В5<80)1:0). 13.3.Заполните этой формулой ячейки В22:В32. В ячейках, где условие выполняется, появляется 1. 13.4.В ячейке ВЗЗ подсчитайте сумму месяцев за 1992 г., удовлетворяющих этому условию. 13.5.Выделите ячейки В21:ВЗЗ и скопируйте формулы в область С21:D33. В ячейках СЗЗ и D33 получилось количество месяцев за 1993 и 1995 гг., удовлетворяющих условию (>20; <80). 13.6.Аналогично создайте вспомогательную таблицу для определения числа месяцев с количеством осадков в пределах (<10; >100)(формулу необходимо изменить в соответствии с условием). 13.7.В ячейку G17 занесите формулу: =ЕСЛИ(год=1992;В33;ЕСЛИ (год=1993;С33;ЕСЛИ(год=1995;D33; «данные отсутствуют»))). 13.8. Скопируйте эту формулу в ячейки G18 и отредактируйте. 13.9. Оформите на свой вкус вспомогательные таблицы и добавьте к ним заголовки и обозначения. ЗАДАНИЕ14. Сохраните результат работы под тем же именем work5.xls в личном каталоге. ЗАДАНИЕ 15. Подведите итоги. Проверьте: Знаете ли вы: Умеете ли вы: Логические функции ЕСЛИ И ИЛИ Использовать встроенные функции EXCEL для решения типовых задач обработки массивов. Предъявите преподавателю: краткий конспект; файл work5.xls на экране и на рабочем диске в личном каталоге. распечатанную таблицу и диаграмму. ПРАКТИЧЕСКАЯ РАБОТА №6 РАБОТА С ЭЛЕКТРОННОЙ ТАБЛИЦЕЙ КАК С БАЗОЙ ДАННЫХ. СОРТИРОВА И ФИЛЬТРАЦИЯ ДАННЫХ. Цели работы: познакомиться с использованием электронной таблицы как базы данных; научиться осуществлять поиск информации в базе данных по различным критериям; научиться производить сортировку информации. ЗАДАНИЕ 1. заполните таблицу, содержащую информацию о планетах солнечной системы, согласно рисунку и сохраните ее под именем work6.xls.  Единицы измерения, используемые в таблице: период обращения по орбите, в земных года среднее расстояние от Солнца, млн км; экваториальный диаметр, тыс. км; масса - «10г4 кг. Основные понятия баз данных Область таблицы А2:F12 можно рассматривать как базу данных. Столбцы А, В, С, D, E, F этой таблицы называются полями, а строки 3-12 называются записями. Область А2:F2 содержит имена полей. Существуют ограничения, накладываемые на структуру базы данных: первый ряд базы данных должен содержать неповторяющиеся имена полей остальные ряды базы данных содержат записи, которые не должны быть пустыми рядами; информация по полям (столбцам) должна быть однородной, т.е. только цифры или только текст. Основная функция любой базы данных - поиск информации по определенным критериям. С увеличением количества записей поиск определенной информации затрудняется. Excel позволяет упростить этот процесс путем фильтрации данных. Фильтрация данных Команды Данные - Фильтр позволяют выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр Автофильтр, так и через Расширенный фильтр. Автофильтр При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных или выделить ее. Затем нужно выполнить команды: Данные – Фильтр – Автофильтр. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт Все отключает фильтрацию, а пункт Условие вызывает диалоговое окно, в котором можно установить параметры фильтрации.  Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ. ЗАДАНИЕ 2. С использованием Автофильтра осуществить поиск планет, начинающихся на букву "С" или букву "Ю" с массой менее 600 -10" кг. 2.1.Выполните команды: Данные, Фильтр, Автофильтр. На полях появились кнопки. 2.2. Нажмите на кнопку на поле Планета. Выберите пункт Условие.. 2.3. В диалоговом окне Пользовательский автофильтр задайте критерии согласно рисунку и нажмите на кнопку <ОК>. Проверьте! В базе данных остались планеты: Солнце, Юпитер, Сатурн. 2.4. Нажмите на кнопку на поле Масса. Выберите пункт Условие. 2.5.В диалоговом окне задайте критерий: Масса < 600. Проверьте! Остался только Сатурн. 2.6.Выполните команды меню: Данные – Фильтр - Показать все. ЗАДАНИЕ 3. С использованием Автофильтра самостоятельно: осуществить поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу менее 4*1024 кг (Меркурий, Марс, Плутон) осуществить поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*1024 до 500*1024 кг, а так же не более 2 спутников (Венера, Земля, Нептун). Расширенный фильтр При использовании Расширенного фильтра необходимо сначала определить (создать) три области. Интервал списка – это область базы данных (А2:F12); Интервал критериев – это область, где задаются критерии фильтрации (А14:F15); Интервал извлечения – это та область, в которой будут появляться результаты фильтрации (А17:F21). Имена полей во всех интервалах должны точно совпадать. Для выполнения действий фильтрации необходимо воспользоваться командами: Данные – Фильтр – Расширенный фильтр. В диалоговом окне необходимо указать координаты интервалов. Если необходимо получать результаты фильтрации в интервале извлечения, нужно поставить (*) – скопировать результат в другое место. ЗАДАНИЕ 4. С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2. 4.1. Создайте интервал критериев и интервал извлечения как на рисунке. 4.2. Запишите критерий поиска в интервал критериев. 4.3.Поместите курсор в область данных. 4.4. Выполните команды: Данные – Фильтр – Расширенный фильтр.  4.5. В диалоговом окне Расширенный фильтр установить переключатель (*) - скопировать результат в другое место.  4.6. Проверьте правильность задания интервалов. Нажмите кнопку <ОК>. Проверьте! Найдены планеты Юпитер, Сатурн, Уран, Нептун. ЗАДАНИЕ 5. Сохраните результаты в файле planeta.xls. ЗАДАНИЕ 6. Распечатайте результаты работы, предварительно введя в строку 13 заголовок "Критерии поиска", а в строку 16 заголовок "Результаты поиска. ЗАДАНИЕ 7. С использованием Расширенного фильтра самостоятельно: 1) найдите планеты, имеющие период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км (Уран, Плутон); 2) осуществите поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км и имеющих 1 спутник (Сатурн, Уран, Нептун). Сортировка данных Команды Данные - Сортировка позволяют упорядочивать (сортировать) базу данных. Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команды: Данные - Сортировка. При этом появится диалоговое окно. Нужно установить (*) - Идентифицировать по подписям (первая с рока диапазона) и выбрать название поля, по которому нужно производить сортировку.  Кроме того, необходимо указать метод сортировки: по возрастанию или по убыванию и нажать кнопку <ОК>. После указанных действий база будет упорядочена. Символьные поля упорядочиваются в алфавитном порядке. ЗАДАНИЕ 8. Отсортируйте данные в таблице в порядке убывания количества спутников. ЗАДАНИЕ 9. Отсортируйте данные в таблице в алфавитном порядке названий планет. ЗАДАНИЕ 10. Отсортируйте данные в порядке возрастания их расстояния от Солнца. ЗАДАНИЕ 11. Подведите итоги. Проверьте: знаете ли вы, что такое: умеете ли вы: база данных; имя поля; поле; запись; требования к базе; критерии поиска. производить поиск информации в базе по различным критериям; производить сортировку информации. Предъявите преподавателю: файл woik6.xls; файл planeta.xls; распечатку файла planeta.xls. ПРАКТИЧЕСКАЯ РАБОТА № 7. РАБОЧАЯ КНИГА EXCEL. СВЯЗЬ ТАБЛИЦ, Цели работы: научиться использовать несколько листов рабочей книги научиться осуществлять связь между листами одной рабочей книги и связь между файлами. До сих пор вы работали только с одним листом рабочей книги. Часто бывает полезно использовать несколько рабочих листов. В нижней части экрана видны ярлычки листов. Если щелкнуть на ярлычке левой клавишей мыши, то указанный лист становится активным и перемещается наверх. Щелчок правой кнопкой на ярлычке вызовет контекстно-зависимое меню для таких действий с листом, как перемещение, удаление, переименование и т.д. В левом нижнем углу окна рабочей книги находятся кнопки прокрутки ярлычков, с помощью которых можно переходить от одного рабочего листа к другому. Щелкнув правой кнопкой мыши на кнопках прокрутки ярлычков, можно открыть контекстно-зависимое меню для выбора нужного рабочего листа. Постановка задачи. Необходимо создать классный журнал. Для простоты ограничимся тремя предметами: литература, алгебра, геометрия. Отельный лист рабочей книги отводится для каждого предмета. Он содержит список класса (ограничимся пятью учениками), текущие оценки и итоговую оценку за 1 четверть. На отдельном листе должна быть представлена ведомость итоговых оценок за 1 четверть, которая должна быть заполнена оценками с использованием ссылок на соответствующие листы по предметам. ХОД РАБОТЫ ЗАДАНИЕ 1. На <Листе 1> заполните и оформите таблицу согласно рисунку:  Для чисел в ячейках, содержащих даты проведения занятий, задайте формат Дата (Код: Д.ММ). Оценки за 1 четверть вычислите по формуле как среднее арифметическое текущих оценок, используя функцию СРЗНАЧ. ЗАДАНИЕ 2. Сохраните таблицу в личном каталоге рабочего диска под именем jurnal.xks. ЗАДАНИЕ 3. Создайте аналогичные листы для предметов алгебра и геометрия, для чего: 3.1.Скопируйте таблицу Литература на следующий лист, используя команды меню: Правка – Переместить/скопировать  После выполнения команды появится лист < Лист 1 [2]>. 3.2. Скопируйте таблицу еще раз, используя те же команды меню. После выполнения этой команды появится лист <Лист 1 [3]>. ЗАДАНИЕ 4. Переименуйте листы: <Лист 1> в <Литература>, <Лист 1 [2]> в <;Алгебра>, <Лист 1 [3]> в <Геометрия>. Для этого дважды щелкните на ярлычке соответствующего листа и задайте в диалоговом окне новое имя. Можно один раз щелкнуть по ярлычку правой кнопкой мыши и открыть контекстно-зависимое меню, в котором выбрать пункт Переименовать. ЗАДАНИЕ 5. На листах <Алгебра> и <Геометрия> в таблицах соответственно измените названия предметов, текущие оценки, даты. Связь рабочих листов Excel позволяет использовать в таблице данные с других листов и из других таблиц. Связь между двумя листами достигается за счет введения в один лист формулы связи со ссылкой на ячейку на другом листе. ЗАДАНИЕ 6. На <Листе 2> создайте таблицу – Ведомость итоговых оценок за 1 четверть, для чего: 6.1. Переименуйте <Лист 2> в Лист <1 четверть> 6.2.Заполните таблицу ссылками на соответствующие ячейки других листов: В ячейку А2 занесите формулу = Литература!А2 Литература! - ссылка на другой лист, символ ! обязателен А2 - адрес ячейки на листе <Литература>, используется относительная адресация. Размножьте формулу на последующие 5 ячеек столбца А и соответствующие ячейки столбца В. В ведомости заполнятся колонки № и Фамилия учащегося. В ячейку СЗ занесите формулу =Литература! L3 Размножьте формулу на последующие 4 ячейки столбца. Столбец заполнился оценками за i четверть по литературе. Таким образом будет установлена связь между листом <1 четверть> - и листом <Литература>.  |