Методические указания к лабораторным работам по курсу "Информационные технологии в машиностроении" для студентов специальностей 151001 "Технология машиностроения", 150202
 Скачать 244.92 Kb.
|
|
Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования "Кузбасский государственный технический университет" Кафедра информационных и автоматизированных производственных систем РАБОТА СО СПИСКАМИ ДАННЫХ Методические указания к лабораторным работам по курсу "Информационные технологии в машиностроении" для студентов специальностей 151001 "Технология машиностроения", 150202 "Оборудование и технология сварочного производства", 151002 "Металлорежущие станки и комплексы" Составители Е. И. Измайлова А. В. Матисов Г. А. Алексеева Утверждены на заседании кафедры Протокол № 5 от 18.12.2008 Рекомендованы к печати учебно-методической комиссией специальности 151001 Протокол № 6 от 06.02.2009 Электронная копия находится в библиотеке ГУ КузГТУ Кемерово 2009 1 1. ЦЕЛЬ РАБОТЫ Целью работы является изучение работы со списками дан- ных в Microsoft Excel (ME) и применение полученных знаний на практике. 2. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ 2.1. Общие сведения о списках Список – это таблица Excel, данные в которой расположе- ны, как в базе данных. В такой таблице каждый столбец имеет свой заголовок, который называется именем поля. Все имена по- лей располагаются в первой строке таблицы. На каждое имя от- водится одна ячейка. Отдельный столбец такой таблицы называ- ется полем данных, а каждая строка – записью. Запись состоит из элементов, число которых равно числу полей данных. Все записи имеют одинаковую структуру. На рис. 1 представлен пример таблицы Excel, оформленной в виде списка. Рис. 1. Пример списка А В С D Е F G 1 ФИО со- трудника Дата ро- ждения Долж- ность Стаж работы (лет) Оклад (руб.) Дата по- лучения премии Раз- мер пре- мии (руб.) 2 Петрова И.В. 3.06.1953 Ведущий инженер 20 6000 6.03.08 3000 3 Иванов С.А. 4.11.1974 Инженер 4 4000 22.02.07 2000 … … … … … … … 9 Сидоров А.Л. 21.06.1961 Инженер програм- мист 17 5000 6.03.08 3000 2 Со списком можно выполнять следующие операции: сорти- ровку, фильтрацию, вычисление промежуточных итогов. Напри- мер, в списке на рис.1 все записи можно отсортировать по пер- вому полю, расположив фамилии сотрудников в алфавитном по- рядке, или по четвертому полю – например, в порядке убывания стажа работы. С помощью фильтра можно отобрать из списка записи, удовлетворяющие определенным условиям. Например, оставить в списке фамилии только тех сотрудников, день рождения кото- рых совпадает с текущей датой, или фамилии сотрудников со стажем работы не менее 10 лет и окладом выше 4000 рублей и так далее. Для решения подобных задач пользователь имеет воз- можность применять два вида фильтров: автофильтр и расши- ренный фильтр. Примером вычисления промежуточных итогов в рассматриваемом списке может служить сумма премиальных, полученных сотрудниками подразделения за 2007 год и другие. Excel автоматически распознает списки. Для этого доста- точно, чтобы в качестве текущей была выбрана одна из ячеек списка. В качестве списка можно обрабатывать не всю таблицу, а только часть ее в виде соответствующего диапазона предвари- тельно выделенных ячеек. Если такому диапазону присвоить имя, то переход к нему в текущей книге значительно упростится. При этом содержимое каждой ячейки в первой строке диапазона будет восприниматься в качестве имени соответствующего поля. Для безошибочного выполнения операций над данными списка рекомендуется в рабочем листе создавать только один список. Таким образом, размер списка ограничен размерами ра- бочего листа. Записи списка могут создаваться и редактироваться обыч- ным образом – с помощью клавиатуры или с использованием диалогового окна, называемого формой данных. 2.2. Создание и корректировка списка Для создания нового списка необходимо выполнить сле- дующие действия: 3 1. Поместить указатель мыши в левую верхнюю ячейку списка. Затем записать в нее имя первого поля. 2. Записать в соседние ячейки этой же строки имена всех остальных полей списка. 3. Изменить ширину столбцов таблицы, исходя из длины имени каждого поля. Если длина имени поля слишком велика в сравнении с данными, которые будут записываться в этот стол- бец, используйте комбинацию клавиш 4. Выделить и присвоить ячейкам, образующим поле дан- ных, нужные параметры форматирования (например, текстовый, денежный, числовой и др. форматы, в зависимости от назначения столбца списка). 5. Начиная со второй строки, ввести записи списка. Эле- менты записей могут содержать значения и формулы. Таблица, оформленная таким образом, автоматически рас- познается Excel как список. Если формат ячеек с именами полей отличается от формата ячеек, предназначенных для хранения са- мих данных, то первая строка такой таблицы автоматически вос- принимается в качестве имен полей. Для редактирования списка с помощью формы данных не- обходимо выполнить следующие действия: 1. Активизировать любую ячейку списка. 2. Выполнить команду "Форма" меню "Данные". На экране появится диалоговое окно, в котором будут представлены имена полей и их содержимое для первой записи списка. Последова- тельность имен полей в диалоговом окне формы данных соответ- ствует их последовательности в списке. В окне формы данных всегда отображается только одна запись списка, которая может содержать до 32 наименований полей. Для выбора поля в пределах записи и "пролистывания" запи- сей списка используются следующие клавиши и их сочетания: , ( ) – переход к 10-й записи вперед (назад), начиная от текущей; , ( ) – переход к пер- вой (последней) записи списка; 4 Для управления окном формы данных можно использовать также расположенные в нем кнопки. Например, кнопка "Крите- рии" позволяет задавать условия, которым должны отвечать вы- водимые на экран записи. Все остальные записи списка в окне формы данных отображаться не будут. Такой процесс называется фильтрацией. Для изменения условий фильтрации необходимо нажать кнопку "Критерии" и внести изменения. Чтобы удалить заданные критерии и вернуться к отображению в окне формы данных всех записей списка необходимо нажать кнопку "Очи- стить", а затем – кнопку "Правка". Примечания: • форму данных можно использовать только в том случае, если первая строка списка содержит имена полей; • если перед выводом на экран формы данных предвари- тельно выделена часть списка, то в окне формы данных будут отображаться только поля и записи выделенной части списка. 2.3. Сортировка списка Под сортировкой списка понимается упорядочение его за- писей в алфавитном, числовом, хронологическом или другом (заданном пользователем) порядке. Сортировка может произво- диться по содержимому одного, двух или трех полей одновре- менно. Причем в каждом поле может быть указан свой (убы- вающий или возрастающий) порядок сортировки. Если для вы- полнения операции выбрано содержимое трех полей, то алго- ритм сортировки следующий: 1. Вначале сортируются в заданном порядке записи по со- держимому первого поля. 2. Выполняется дополнительная сортировка записей, у ко- торых элементы по первому полю оказались одинаковыми (сор- тировка таких записей осуществляется в заданном для второго поля порядке). 3. В последнюю очередь происходит сортировка тех запи- сей, у которых элементы по первому и второму полю оказались 5 одинаковыми (с применением своего порядка сортировки, задан- ного для третьего поля). Если задан возрастающий порядок сортировки по опреде- ленному полю, то записи будут расположены в зависимости от содержимого этого поля: • числа сортируются от наименьшего отрицательного до наибольшего положительного; • значения даты и времени будут отсортированы в порядке от наиболее раннего до наиболее позднего времени; • текст – в алфавитном порядке от А до Z, затем от А до Я; • логические значения – сначала значения ЛОЖЬ (False), а затем значения ИСТИНА (True); • тексты сообщений об ошибках, возникших при вычисле- нии по формулам, будут отсортированы в порядке их следова- ния. Расположение записей при сортировке списка в убывающем порядке противоположно рассмотренному выше. Для сортировки списка необходимо выполнить следующие действия: 1. Активизировать любую ячейку списка. 2. Выполнить команду "Сортировка" меню "Данные". При этом Excel автоматически выделит диапазон из всех ячеек списка и на экране появится диалоговое окно "Сортировка диапазона". 3. Задать нужный порядок сортировки по одному, двум или трем полям списка, выбрать их имена в соответствующих полях диалогового окна. 4. Нажать "ОК". Примечания: • При выборе в любом порядке сортировки пустые ячейки поля размещаются в конце отсортированного списка. • При пользовательском порядке сортировки записи сор- тируются не по возрастанию или убыванию, а в соответствии с порядком, заданным в созданном заранее пользовательском ряде данных. Пользовательский порядок сортировки можно задавать только для столбца (поля), имя которого указано в первой строке диалогового окна "Сортировка диапазона". Чтобы применить пользовательский порядок сортировки для нескольких полей 6 списка, необходимо последовательно выполнить несколько опе- раций, сортировки для каждого из полей. • Сортировка приводит к автоматическому изменению от- носительных ссылок, содержащихся в формулах перемещаемых записей. Для исключения ошибок необходимо проверить, чтобы в записях не было относительных ссылок на ячейки других запи- сей списка. При наличии таких ссылок необходимо заменить от- носительные ссылки абсолютными ссылками до выполнения сортировки. • Перед сортировкой части списка необходимо убедиться, что выделены все поля списка, а не только те, которые видны на экране. Если выделение не захватит все поля, информация может оказаться перемешанной: выделенные поля отсортируются, а ос- тальные останутся на месте. • Для того чтобы после неоднократной сортировки или случайного перемешивания данных вернуться к первоначально- му виду таблицы, необходимо иметь ее копию. 2.4. Фильтрация списка Фильтрация применяется в случаях, когда необходимо из общего списка выбрать и отобразить на экране только те записи, которые удовлетворяют заданным условиям отбора. Excel предоставляет пользователю два способа фильтрации: с помощью автофильтра и с помощью расширенного фильтра. Фильтрация с помощью автофильтра применяется в случа- ях, когда необходимо быстро отфильтровать данные с заданием одного или двух простых условий отбора. Эти условия наклады- ваются на содержимое ячеек отдельного столбца. Фильтрация с помощью расширенного фильтра рекоменду- ется использовать в следующих случаях: 1. когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов; 2. когда к ячейкам одного столбца необходимо применить три и более условий отбора: 3. когда в условии отбора используется значение, получен- ное в результате вычисления заданной формулы. 7 Чтобы обработать таблицу с помощью автофильтра, необ- ходимо вначале выбрать в качестве активной любую ячейку. По- сле этого в меню "Данные" \ "Фильтр" выполнить команду "Ав- тофильтр". Как только команда будет выполнена, в первой стро- ке таблицы рядом с именем каждого поля появятся кнопки со стрелками. Нажатие любой из этих кнопок приводит к открытию соответствующего набора строк. Каждая строка набора пред- ставляет собой одно из неповторяющихся значений ячеек вы- бранного столбца. Дополнительно к значениям в каждом наборе будут присутствовать строки с именами [Все] ([All]), [Первые 10...] ([Тор 10...]) и [Условие...]). Чтобы отфильтровать список по одному из имеющихся зна- чений поля, достаточно выбрать это значение из развернувшего- ся набора строк. В списке остаются только те записи, которые содержат выбранное значение в данном столбце. Номера строк отфильтрованных таким образом записей и стрелка кнопки вы- бранного столбца будут высвечиваться синим цветом. Эти запи- си можно снова подвергнуть фильтрации по этому же или дру- гому полю и т. д. Если для фильтрации по определенному полю выбрать строку [Первые 10...] ([Тор 10...]), то на экране появится диало- говое окно "Наложение условия по списку", в котором пользова- тель имеет возможность изменить условие: выбрать от 1 до 500 наибольших или наименьших элементов этого поля. После этого на экране монитора отображается выбранное количество запи- сей, отвечающих заданному условию. Чтобы отфильтровать список по двум и более значениям, встречающимся в столбце, необходимо выбрать строку [Усло- вие...]. В появившимся диалоговом окне "Пользовательский ав- тофильтр" можно задать это условие отбора. Для этого исполь- зуются операторы сравнения (>, <, >=, <=, =) по отношению к каждому из двух выбранных значений поля и операторы логиче- ских функций И (And), ИЛИ (Or) для объединения результатов такого сравнения. Для того чтобы после выполненной "автофильтрации" вер- нуться к отображению на экране всех записей списка, можно ис- пользовать следующие приемы: 8 1. Последовательно нажимать кнопки с синими стрелками (т. е. кнопки полей, по которым была выполнена фильтрация) и в открывающихся при этом наборах строк выбирать строку [Все]. 2. Выполнить команду "Показать все" в меню "Данные" \ "Фильтр". 3. Отказаться от применения автофильтра. Для этого сле- дует еще раз выполнить команду "Автофильтр" в меню "Данные" \ "Фильтр". Все кнопки автофильтра, которые были в первой строке списка, исчезнут, и на экране высветится таблица в ее первоначальном виде. При использовании расширенного фильтра условие отбора задается в отдельном диапазоне ячеек текущего рабочего листа, который называется диапазоном критериев. Он может разме- щаться в любом месте рабочего листа. Рекомендуется отделять диапазон критериев от фильтруемого списка, по крайней мере, одной пустой строкой (столбцом). В этом случае исключается ошибочное восприятие программой ячеек диапазона критериев в качестве элементов списка. Диапазон критериев оформляется следующим образом: в первой строке записываются (или копируются) имена полей спи- ска, для которых задаются условия отбора, а во второй и после- дующих строках вводятся непосредственно сами условия отбора. С использованием рис. 1 рассмотрим примеры задания ус- ловий отбора (диапазонов критериев) расширенного фильтра. Пример 1. Условие отбора накладывается на содержимое двух и более столбцов списка. Пусть необходимо из списка вы- делить записи, которые одновременно содержат в поле "Долж- ность" значение "Инженер-программист", в поле "ФИО сотруд- ника" – "Сидоров М.А.", а в поле "Дата получения премии" – все даты позже 15.02.07. В этом случае диапазон критериев будет выглядеть так, как изображено на рис. 2. Необходимо обратить внимание на следующее: 1. Диапазон критериев начинается с 55 строки (отделен от списка рис.1 одной пустой строкой). 2. Все условия отбора размещены в одной строке. Тем са- мым задается их взаимодействие по схеме "И" (требуется одно- временное их выполнение). 9 Рис. 2. Диапазон критериев при взаимодействии по схеме "И" Для соединения условий отбора по схеме "ИЛИ" необходи- мо каждое из них разместить в отдельной строке (рис. 3). Рис. 3. Диапазон критериев при взаимодействии по схеме "ИЛИ" При задании оформленного таким образом диапазона кри- териев из списка будут выбраны записи, содержащие либо зна- чение "Сидоров Л.А." в столбце "ФИО сотрудника", либо "Ин- женер-программист" в столбце "Должность", либо имеющие зна- чение ">15.02.97" в столбце "Дата получения премии". Так зада- ются разные условия отбора на несколько полей списка одно- временно (рис .3). Для задания сложного условия отбора необходимо вводить его составные части в отдельные строки диапазона критериев. Пример 2. Три и более условий отбора накладываются на содержимое одного столбца списка. Диапазон критериев в этом случае оформляется в виде столбца, в котором в смежных ячейках записаны условия отбора (рис. 4). А С F 55 ФИО сотрудника Должность Дата получения премии 56 Сидоров Л.А. Инженер- программист >15.02.07 А С F 59 ФИО сотрудника Должность Дата получения пре- мии 60 Сидоров Л.А. 61 Инженер- программист 62 >15.02.97 10 Рис. 4. Диапазон критериев при трех и более условиях отбора Например, для рассматриваемого списка следующий диапа- зон критериев задает отбор записей, содержащих в столбце "ФИО сотрудника" значения либо "Петрова И. В.", либо " Иванов С. А. ", либо " Сидоров Л. А. ". Пример 3. В условии отбора используется возвращаемое формулой значение. В этом случае диапазон критериев и указываемая в нем формула должны удовлетворять следующим требованиям: • формула может иметь в своем составе несколько функций и зависеть от нескольких полей списка, но должна обязательно иметь в качестве результата логическую величину ИСТИНА (True) или ЛОЖЬ (False); • ссылки в формуле могут указывать как на ячейки списка, так и на другие ячейки рабочего листа. При этом ссылки на ячейки списка должны быть относительными, а на ячейки вне списка – абсолютными; • вычисляемый критерий должен иметь имя поля, не совпа- дающее ни с одним из имен полей списка; • формула должна ссылаться хотя бы на одно поле списка; • при задании ссылки на все значения столбца необходимо указывать относительную ссылку на первую ячейку этого столб- ца. Оформленный таким образом критерий называется вычис- ляемым. В результате фильтрации из списка выбираются записи, для которых проверяемое условие истинно. Например, следующий вычисляемый критерий, заданный для списка на рис. 1, позволяет найти фамилии сотрудников, ко- торые имеют сумму оклада и премии более 8000 рублей (рис. 5). А 67 ФИО сотрудника 68 Петрова И.В. 69 Иванов С.А. 70 Сидоров Л. А. 11 Рис. 5. Вычисляемый критерий Обратите внимание, что формула помещена под именем по- ля ("Всего руб."), не совпадающим ни с одним из имен полей списка на рис. 1. В качестве имени поля можно использовать лю- бой уникальный текст. Приведем примеры задания формул в критериях для списка на рис. 1, выполняющих сравнение содержимого ячеек в преде- лах записи: =E2=G2 =E2 Более сложные критерии используют ссылки на ячейки не- скольких записей списка или на ячейки вне списка, например: =Е2=$В$72 – сравнение значений ячеек в поле "Оклад (руб.)" с значением внешней по отношению к списку ячейки $В$72. Логические функции И, ИЛИ, НЕ расширяют возможности вычисляемых критериев. Например, формула =И(С2="Инженер";Р2>1.02.08) в крите- рии задает отбор записей, содержащих в поле "Должность" зна- чение "Инженер" и в поле "Дата получения премии" – значение дат позже 1.02.08. После создания диапазона критериев можно непосредст- венно выполнить саму операцию фильтрации с использованием расширенного фильтра. Для этого необходимо выполнить сле- дующие действия: 1. Активизировать одну из ячеек списка и выполнить ко- манду "Расширенный фильтр" меню "Данные" \ "Фильтр". 2. В поле "Диапазон критериев" появившегося диалогового окна "Расширенный фильтр" указать ссылку на данный диапазон критериев и нажать на "ОК". А 72 Всего руб. 73 =E2+G2>8000 12 Замечания: |