информатика. Игнатьева Елена Александровна, Измайлова Елена Ивановна. Информатика. Электронный ресурс методические указания
 Скачать 4.32 Mb.
|
|
Вставка в раз- деле Диаграммы (рис 13 а), в свою очередь после выбора типа диаграммы появляется контекстная вкладка Работа с диаграмма- ми (рис 13 б). 95 а) б) Рис. 13. Построение и работа с диаграммами Для построения диаграммы, показанной на рис 13 б), нужно. 1. Выделить диапазон ячеек, в которых содержатся необ- ходимые данные, в данном случае B3:C8. 2. Перейти на вкладку Вставка и в группе Диаграммы выбрать Точечная, и во всплывающем меню выбрать Точечная с гладкими кривыми и маркерами. 3. Сразу после выбора диаграмма будет построена. Да- лее, выбирая вкладки Конструктор, Макет, Формат, можно из- менять или добавлять данные, легенды и т.п. 96 3. СОДЕРЖАНИЕ И ПОРЯДОК ВЫПОЛНЕНИЯРАБОТЫ Создание электронной таблицы и заполнение формулами 3.1.1. Задание 1 1. Создайте таблицу следующего образца: Март Наименование товара Количество Закупочная цена Процентная надбавка Розничная цена Сумма закупки Сумма продажи Прибыль 2. Заполните исходную таблицу таким образом, чтобы в столбцах помимо наименования товара были соответствующие цифры а в ячейках Розничная цена, Сумма закупки, Сумма продажи , Прибыль – соответствующие формулы Розничная цена = Закуп. Цена ? Процент. Надбавка + За- куп.Цена Сумма закупки = Закуп. Цена ? Количество Сумма продажи = Розн. Цена ? Количество Прибыль = Сумма продажи - Сумма закупки Помните, что все данные в ячейки заносятся только через ссылки на ячейку, в которой они находятся, результаты покажите преподавателю. 97 Задание 2 1. Составьте электронную таблицу следующего вида: 2. Заполните исходную таблицу. Расчеты в таблице производятся по следующим формулам Еxcel со ссылками на ячейки: Израсходовано = Кол. На начало месяца Кол. На конец месяца; Сумма на конец месяца = Цена ? Кол. На конец месяца; Доля в общем объеме = (Итого Сумма на конец месяца Сумма на конец месяца) / 100; Результаты покажите преподавателю. 98 Задание 3 Составьте электронную таблицу, вычисляющую n-й член и сумму арифметической прогрессии по формулам (1), (2) 1 1 n d a a n ; (1) 2 1 n a a S n n , (2) где a 1 — первый член прогрессии, d — разность арифмети- ческий прогрессии. 1. В ячейку А1 и введите заголовок таблицы "Вычисление n- го члена и суммы арифметической прогрессии". Заголовок будет размещен в одну строчку и займет несколько ячеек правее А1. А В С D 1 Вычисление n-го члена и суммы арифметической прогрессии 2 d n а n S n 3 0,725 1 -2 -2 4 0,725 2 -1,275 -3,275 5 0,725 3 -0,55 -3,825 6 0,725 4 0,175 -3,65 7 0,725 5 0.9 2.75 8 0,725 6 1,625 -1,125 9 0,725 7 2,35 1,225 10 0,725 8 3.075 4,3 11 0,725 9 3,8 8,1 12 0,725 10 4.525 12,625 2. В ячейку A2 введите "d", в ячейку В2 — "n", в С2 — "а n ", в D2 — "S n ". Для набора нижних индексов воспользуйтесь командой "Формат""Ячейки"..., выберите вкладку "Шрифт" и включите нижний индекс. Выровняйте по центру и примените полужирный шрифт. 99 3. В ячейку А3 введите величину разности арифметиче- ской прогрессии. 4. Следующий столбик заполните последовательностью чисел от 1 до 10. 5. Введите в ячейку С3 значение первого члена арифмети- ческой прогрессии. 6. Выделите ячейку С4 и наберите в ней формулу =С3+А3, зафиксируйте ее нажатием Enter, в ячейке окажется результат вычисления по формуле, а в Строке формул – сама формула. 7. Заполните формулой, "протащив" маркер заполнения вниз, ряд ячеек ниже С4. Ссылки в формуле изменились относи- тельно смещения формулы. 8. Аналогично введите в ячейку D3 формулу =($С$3+С3)*В3/2 для подсчета суммы n первых членов арифме- тической прогрессии, заполните формулами нижние ячейки. 9. Выделите ячейку А1 и примените полужирное начерта- ние символов к содержимому ячейки. Задание для защиты: редактирование таблиц Задание 1 1. Откройте первую таблицу, внесите следующие изменения: уберите строку Процентная надбавка; вставьте месяцы январь и февраль; скопируйте в них нужные формулы и заполните (устано- вите формат денежный); вставьте перед первым столбцом столбец с нумерацией; вставьте строку со словами "Расчет рентабельности" и отформатируйте ее полужирным шрифтом изменив цвет шрифта, строку "Наименование товара" залить цветом, столбец от слов "Количетво" до "Процентная надбавка" стилем курсив; цифры, "Розничная цена" до "Прибыль" – стилем на ваш вкус. 2. Последовательность действий покажите преподавателю. 100 Задание 2 1. Откройте вторую таблицу. 2. Вставьте перед столбиком "Сумма на конец месяца" столбец с "Процентным количеством израсходованных материа- лов" от общего количества (использование абсолютной ссылки). 3. Отформатируйте текст различными стилями по вашему усмотрению. 4. Выровняйте содержимое диапазонов с цифрами по цен- тру. 5. Сохраните результат и покажите преподавателю. 4. КОНТРОЛЬНЫЕ ВОПРОСЫ 4.1. Какие виды работ позволяет выполнить табличный про- цессор Excel? 4.2. Что такое диапазон данных? 4.3. Как выделить несмежные диапазоны для совместного их форматирования? 4.4. Какие виды диаграмм можно построить в Excel? 4.5. Как Excel работает с датами? 4.6. Приведите примеры использования абсолютной и отно- сительной адресации. 4.7. Какое расширение имеют файлы, созданные с помощью Excel? 101 Лабораторная работа № 5 Работа со списками данных MS EXCEL. 1. ЦЕЛЬ РАБОТЫ Целью работы является изучение работы со списками дан- ных в Microsoft Excel (ME) и применение полученных знаний на практике. 2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ Общие сведения о списках Список – это таблица 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 102 Со списком можно выполнять следующие операции: сорти- ровку, фильтрацию, вычисление промежуточных итогов. Напри- мер, в списке на рис.1 все записи можно отсортировать по пер- вому полю, расположив фамилии сотрудников в алфавитном по- рядке, или по четвертому полю – например, в порядке убывания стажа работы. С помощью фильтра можно отобрать из списка записи, удовлетворяющие определенным условиям. Например, оставить в списке фамилии только тех сотрудников, день рождения кото- рых совпадает с текущей датой, или фамилии сотрудников со стажем работы не менее 10 лет и окладом выше 4000 рублей и так далее Для решения подобных задач пользователь имеет воз- можность применять два вида фильтров: автофильтр и расши- ренный фильтр. Примером вычисления промежуточных итогов в рассматриваемом списке может служить сумма премиальных, полученных сотрудниками подразделения за 2007 год и другие. Excel автоматически распознает списки. Для этого доста- точно, чтобы в качестве текущей была выбрана одна из ячеек списка. В качестве списка можно обрабатывать не всю таблицу, а только часть ее в виде соответствующего диапазона предвари- тельно выделенных ячеек. Если такому диапазону присвоить имя, то переход к нему в текущей книге значительно упростится. При этом содержимое каждой ячейки в первой строке диапазона будет восприниматься в качестве имени соответствующего поля. Для безошибочного выполнения операций над данными списка рекомендуется в рабочем листе создавать только один список. Таким образом, размер списка ограничен размерами ра- бочего листа. Записи списка могут создаваться и редактироваться обыч- ным образом – с помощью клавиатуры или с использованием диалогового окна, называемого формой данных. Создание и корректировка списка Для создания нового списка необходимо выполнить сле- дующие действия: 1. Поместить указатель мыши в левую верхнюю ячейку списка. Затем записать в нее имя первого поля. 103 2. Записать в соседние ячейки этой же строки имена всех остальных полей списка. 3. Изменить ширину столбцов таблицы, исходя из длины имени каждого поля. Если длина имени поля слишком велика в сравнении с данными, которые будут записываться в этот стол- бец, используйте комбинацию клавиш 4. Выделить и присвоить ячейкам, образующим поле дан- ных, нужные параметры форматирования (например, текстовый, денежный, числовой и др. форматы, в зависимости от назначения столбца списка). 5. Начиная со второй строки, ввести записи списка. Эле- менты записей могут содержать значения и формулы. Таблица, оформленная таким образом, автоматически рас- познается Excel как список. Если формат ячеек с именами полей отличается от формата ячеек, предназначенных для хранения са- мих данных, то первая строка такой таблицы автоматически вос- принимается в качестве имен полей. Для редактирования списка с помощью формы данных не- обходимо выполнить следующие действия: 1. Активизировать любую ячейку списка. 2. Выполнить команду "Форма" меню "Данные". На экране появится диалоговое окно, в котором будут представлены имена полей и их содержимое для первой записи списка. Последова- тельность имен полей в диалоговом окне формы данных соответ- ствует их последовательности в списке. В окне формы данных всегда отображается только одна запись списка, которая может содержать до 32 наименований полей. Для выбора поля в пределах записи и "пролистывания" за- писей списка используются следующие клавиши и их сочетания: , ( ) – переход к 10-й записи вперед (назад), начиная от текущей; , ( ) – переход к пер- вой (последней) записи списка; 104 Для управления окном формы данных можно использовать также расположенные в нем кнопки. Например, кнопка "Крите- рии" позволяет задавать условия, которым должны отвечать вы- водимые на экран записи. Все остальные записи списка в окне формы данных отображаться не будут. Такой процесс называется фильтрацией. Для изменения условий фильтрации необходимо нажать кнопку "Критерии" и внести изменения. Чтобы удалить заданные критерии и вернуться к отображению в окне формы данных всех записей списка необходимо нажать кнопку "Очи- стить", а затем – кнопку "Правка". Примечания: • форму данных можно использовать только в том случае, если первая строка списка содержит имена полей; • если перед выводом на экран формы данных предвари- тельно выделена часть списка, то в окне формы данных будут отображаться только поля и записи выделенной части списка. Сортировка списка Под сортировкой списка понимается упорядочение его за- писей в алфавитном, числовом, хронологическом или другом (заданном пользователем) порядке. Сортировка может произво- диться по содержимому одного, двух или трех полей одновре- менно. Причем в каждом поле может быть указан свой (убы- вающий или возрастающий) порядок сортировки. Если для вы- полнения операции выбрано содержимое трех полей, то алго- ритм сортировки следующий: 1. Вначале сортируются в заданном порядке записи по со- держимому первого поля. 2. Выполняется дополнительная сортировка записей, у ко- торых элементы по первому полю оказались одинаковыми (сор- тировка таких записей осуществляется в заданном для второго поля порядке). 3. В последнюю очередь происходит сортировка тех запи- сей, у которых элементы по первому и второму полю оказались одинаковыми (с применением своего порядка сортировки, задан- ного для третьего поля). 105 Если задан возрастающий порядок сортировки по опреде- ленному полю, то записи будут расположены в зависимости от содержимого этого поля: • числа сортируются от наименьшего отрицательного до наибольшего положительного; • значения даты и времени будут отсортированы в порядке от наиболее раннего до наиболее позднего времени; • текст – в алфавитном порядке от А до Z, затем от А до Я; • логические значения – сначала значения ЛОЖЬ (False), а затем значения ИСТИНА (True); • тексты сообщений об ошибках, возникших при вычисле- нии по формулам, будут отсортированы в порядке их следова- ния. Расположение записей при сортировке списка в убывающем порядке противоположно рассмотренному выше. Для сортировки списка необходимо выполнить следующие действия: 1. Активизировать любую ячейку списка. 2. Выполнить команду "Сортировка"меню "Данные". При этом Excel автоматически выделит диапазон из всех ячеек списка и на экране появится диалоговое окно "Сортировка диапазона". 3. Задать нужный порядок сортировки по одному, двум или трем полям списка, выбрать их имена в соответствующих полях диалогового окна. 4. Нажать "ОК". Примечания: • При выборе в любом порядке сортировки пустые ячейки поля размещаются в конце отсортированного списка. • При пользовательском порядке сортировки записи сор- тируются не по возрастанию или убыванию, а в соответствии с порядком, заданным в созданном заранее пользовательском ряде данных. Пользовательский порядок сортировки можно задавать только для столбца (поля), имя которого указано в первой строке диалогового окна "Сортировка диапазона". Чтобы применить пользовательский порядок сортировки для нескольких полей списка, необходимо последовательно выполнить несколько опе- раций, сортировки для каждого из полей. 106 • Сортировка приводит к автоматическому изменению от- носительных ссылок, содержащихся в формулах перемещаемых записей. Для исключения ошибок необходимо проверить, чтобы в записях не было относительных ссылок на ячейки других запи- сей списка. При наличии таких ссылок необходимо заменить от- носительные ссылки абсолютными ссылками до выполнения сортировки. • Перед сортировкой части списка необходимо убедиться, что выделены все поля списка, а не только те, которые видны на экране. Если выделение не захватит все поля, информация может оказаться перемешанной: выделенные поля отсортируются, а ос- тальные останутся на месте. • Для того чтобы после неоднократной сортировки или случайного перемешивания данных вернуться к первоначально- му виду таблицы, необходимо иметь ее копию. Фильтрация списка Фильтрация применяется в случаях, когда необходимо из общего списка выбрать и отобразить на экране только те записи, которые удовлетворяют заданным условиям отбора. Excel предоставляет пользователю два способа фильтрации: с помощью автофильтра и с помощью расширенного фильтра. Фильтрация с помощью автофильтра применяется в случа- ях, когда необходимо быстро отфильтровать данные с заданием одного или двух простых условий отбора. Эти условия наклады- ваются на содержимое ячеек отдельного столбца. Фильтрация с помощью расширенного фильтра рекоменду- ется использовать в следующих случаях: 1. когда условие отбора должно одновременно применяться к ячейкам двух и более столбцов; 2. когда к ячейкам одного столбца необходимо применить три и более условий отбора: 3. когда в условии отбора используется значение, получен- ное в результате вычисления заданной формулы. Чтобы обработать таблицу с помощью автофильтра, необ- ходимо вначале выбрать в качестве активной любую ячейку. По- сле этого в меню "Данные" \ "Фильтр" выполнить команду "Ав- тофильтр". Как только команда будет выполнена, в первой стро- 107 ке таблицы рядом с именем каждого поля появятся кнопки со стрелками. Нажатие любой из этих кнопок приводит к открытию соответствующего набора строк. Каждая строка набора пред- ставляет собой одно из неповторяющихся значений ячеек вы- бранного столбца. Дополнительно к значениям в каждом наборе будут присутствовать строки с именами [Все] ([All]), [Первые 10...] ([Тор 10...]) и [Условие...]). Чтобы отфильтровать список по одному из имеющихся зна- чений поля, достаточно выбрать это значение из развернувшего- ся набора строк. В списке остаются только те записи, которые содержат выбранное значение в данном столбце. Номера строк отфильтрованных таким образом записей и стрелка кнопки вы- бранного столбца будут высвечиваться синим цветом. Эти запи- си можно снова подвергнуть фильтрации по этому же или дру- гому полю и т. д. Если для фильтрации по определенному полю выбрать строку [Первые 10...] ([Тор 10...]), то на экране появится диало- говое окно "Наложение условия по списку", в котором пользова- тель имеет возможность изменить условие: выбрать от 1 до 500 наибольших или наименьших элементов этого поля. После этого на экране монитора отображается выбранное количество запи- сей, отвечающих заданному условию. Чтобы отфильтровать список по двум и более значениям, встречающимся в столбце, необходимо выбрать строку [Усло- вие...]. В появившимся диалоговом окне "Пользовательский ав- тофильтр" можно задать это условие отбора. Для этого исполь- зуются операторы сравнения (>, <, >=, <=, =) по отношению к каждому из двух выбранных значений поля и операторы логиче- ских функций И (And), ИЛИ (Or) для объединения результатов такого сравнения. Для того чтобы после выполненной "автофильтрации" вер- нуться к отображению на экране всех записей списка, можно ис- пользовать следующие приемы: 1. Последовательно нажимать кнопки с синими стрелками (т. е. кнопки полей, по которым была выполнена фильтрация) и в открывающихся при этом наборах строк выбирать строку [Все]. 2. Выполнить команду "Показать все" в меню "Данные" \ "Фильтр". 108 3. Отказаться от применения автофильтра. Для этого сле- дует еще раз выполнить команду "Автофильтр" в меню "Данные" \ "Фильтр". Все кнопки автофильтра, которые были в первой строке списка, исчезнут, и на экране высветится таблица в ее первоначальном виде. При использовании расширенного фильтра условие отбора задается в отдельном диапазоне ячеек текущего рабочего листа, который называется диапазоном критериев. Он может разме- щаться в любом месте рабочего листа. Рекомендуется отделять диапазон критериев от фильтруемого списка, по крайней мере, одной пустой строкой (столбцом). В этом случае исключается ошибочное восприятие программой ячеек диапазона критериев в качестве элементов списка. Диапазон критериев оформляется следующим образом: в первой строке записываются (или копируются) имена полей спи- ска, для которых задаются условия отбора, а во второй и после- дующих строках вводятся непосредственно сами условия отбора. С использованием рис. 1 рассмотрим примеры задания ус- ловий отбора (диапазонов критериев) расширенного фильтра. |