Лабораторные по Excel / MS_Office_97_2000_Система_электронных_таблиц_Excel
.pdfнится при копировании формулы в пределах одного столбца; ссылка $D3 не изменится при копировании формулы в пределах одной строки.
При вводе или редактировании формулы для изменения типа ссылки в ней надо выделить эту ссылку в строке формул и, нажимая клавишу F4, вы- брать нужный тип ссылки.
Содержание работы
1.В рабочей книге, созданной на предыдущей лабораторной работе, на рабочем листе Лист 4 (Sheet 4) выполните следующие действия:
для двух любых ячеек создайте примечания;
попробуйте варианты вставки пустых ячеек;
вставленные пустые ячейки удалите, используя различные спосо- бы удаления ячеек;
выполните различные варианты очистки ячеек (используйте ко- манду Отменить (Undo) из меню Правка (Edit) или соответ- ствующую кнопку на панели инструментов Стандартная (Standard) для возврата к исходному состоянию рабочего листа после выпол- нения каждого варианта очистки).
2.На рабочем листе Лист 5 (Sheet 5) выполните следующие действия:
в ячейку A1 скопируйте формат ячейки, содержащей заголовок «Примеры стилей и числовых форматов» из рабочего листа Лист 4 (Sheet 4);
в ячейку A1 введите заголовок «Способы перемещения и копи- рования ячеек»;
скопируйте из рабочего листа Лист 4 (Sheet 4) любой интервал непустых ячеек;
опробуйте разные варианты перемещения и копирования ячеек.
3.На рабочем листе Лист 1 (Sheet 1) выполните следующие действия:
для ячеек, содержащих проходной балл и заголовок графы «От- клонение», создайте примечания, поясняющие их смысл;
заполните графу «Отклонение», кроме последней ячейки, путем копирования формулы этой графы с помощью мыши;
в последнюю ячейку графы «Отклонение» скопируйте формулу через буфер обмена.
41
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
4.На рабочем листе Лист 5 (Sheet 5) исследуйте варианты копирования ячеек с использованием автозаполнения (мышью и с помощью ко-
манды Заполнить (Fill) из меню Правка (Edit)).
5.На рабочем листе Лист 2 (Sheet 2) заполните графу «Общая сумма баллов» копированием формулы, применяя автозаполнение.
6.На рабочем листе Лист 6 (Sheet 6) выполните следующие действия:
в ячейку A1 введите заголовок «Создание рядов»;
в различных столбцах наберите представленные ниже образцы по- следовательностей значений ячеек и выполните автозаполнение ячеек, примыкающих к ним:
1, 2, 3;
3, 5, 7;
2, 4, 8;
Янв, Фев;
9:00, 9:10;
10.Янв, 10.Мар;
12.01.97, 12.02.97 (выполните автозаполнение в ячейки, распо- ложенные над этой последовательностью);
Заказ 1, Заказ 2;
выполните линейную и экспоненциальную экстраполяцию чисел
1, 4, 5;
опробуйте создание рядов дат с помощью контекстного меню ав- тозаполнения;
ознакомьтесь с возможностями команды Прогрессия (Series);
создайте пользовательский список, включающий следующие эле- менты: А, Б, В, Г, Д, и выполните автозаполнение интервала ячеек, используя этот список.
7.Просмотрите изменение ссылок на ячейки при копировании формул графы «Отклонение» на рабочем листе Лист 1 (Sheet 1) и графы «Общая сумма баллов» на рабочем листе Лист 2 (Sheet 2).
8.На рабочем листе Лист 2 (Sheet 2) выполните следующие действия:
заполните графу «С учетом аттестата» копированием ячейки, ис- пользующей имя формулы;
объясните, почему при копировании возникает неверный резуль- тат, и исправить ошибку.
42
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Лабораторная работа № 5
ПАКЕТ MS OFFICE: РЕАЛИЗАЦИЯ АНАЛИТИЧЕСКИ- ЧИСЛЕННЫХ МЕТОДОВ РЕШЕНИЯ ЗАДАЧ В EXCEL
Цель работы:
∙ознакомление с возможностями табличного процессора Excel for Windows по организации списков и анализу данных в них, вклю- чая сортировку и фильтрацию данных, автоматическое подведе- ние итогов и способы ведения списков с помощью форм данных;
∙получение практических навыков работы со списками.
5.1.Понятие списка
Система электронных таблиц Excel имеет набор команд, позволяющих работать со списками. Список – это последовательность строк рабочего лис- та, содержащих в одинаковых столбцах данные, подобные по типу. Список может быть использован в качестве базы данных, где строки являются запи- сями, а столбцы – полями записей.
В Excel не надо ничего предпринимать, чтобы использовать список в качестве базы данных. При выполнении характерных для баз данных опера- ций, таких как поиск, сортировка, подведение итогов, Excel автоматически рассматривает список данных как базу данных.
5.2. Сортировка данных
Сортировка данных позволяет переупорядочить строки в списке в соот- ветствии с содержимым определенных столбцов. Чтобы отсортировать спи- сок целиком, нужно выделить одну ячейку из списка и выбрать команду Сортировка (Sort) из меню Данные (Data). Сортировку можно производить также и для выделенных строк или столбцов.
Excel использует следующий порядок для сортировки по возрастанию:
числа;
текст и текст, включающий числа (например, почтовые индексы);
логические значения;
значения ошибок;
пустые ячейки.
Сортировка по убыванию обращает указанный порядок во всем, за ис- ключением пустых ячеек, которые всегда располагаются в конце списка.
43
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Для получения правильного результата сортировки все ячейки в столбце должны содержать данные одного типа.
Команда Сортировка (Sort) позволяет задать параметры сортировки:
Сортировать по (Sort By) – определяет столбец, по которому произ- водится сортировка, и порядок сортировки (по возрастанию или по убыванию);
Затем по (Then By) – определяет столбец вторичной сортировки и ее по- рядок для строк, имеющих совпадающие значения в сортируемом столбце;
В последнюю очередь, по (Last By) – определяет последний столбец процедуры сортировки и ее порядок (по возрастанию или по убыванию);
Идентифицировать поля по (My List Has) – определяет, имеется ли в сор-
тируемомспискестроказаголовков, которую надоисключитьизсортировки. Кнопка Параметры (Options) позволяет определить дополнительные
параметры сортировки:
Сортировка по первому ключу (First Key Sort Order) – определяет пользовательский порядок сортировки для столбца, указанного в окне Сортировать по (Sort By) (можно выбрать один из встроенных пользовательских порядков сортировки или создать свой собствен- ный, используя вкладку Списки (Custom Lists) команды Парамет-
ры (Options) из меню Сервис (Tools));
Учитывать регистр (Case Sensitive) – указывает необходимость учета регистра символов при сортировке;
Сортировать (Sort Orientation) – используется только для сводных таблиц, указывая сортировать строки диапазона (Sort Top to Bottom) или столбцы диапазона (Sort Left to Right).
Хотя список в Excel может иметь до 256 столбцов, команда Сортиров- ка (Sort) может сортировать его не более, чем по 3 столбцам одновременно. Чтобы отсортировать список по 4 или более столбцам, надо последовательно выполнить несколько сортировок.
Панель инструментов Стандартная (Standard) имеет две кнопки для сортировки: Сортировка по возрастанию (Sort Ascending) и Сортировка по убыванию (Sort Descending). Эти кнопки позволяют выполнить соответ- ствующую сортировку для столбца, в котором находится активная ячейка, или для выделенных столбцов и строк списка. При этом будут применяться все параметры сортировки, использованные в предыдущей сортировке, вклю- чая учет регистра и пользовательский порядок сортировки.
44
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
5.3. Фильтрация данных
Фильтрация списка позволяет находить и отбирать для обработки неко- торое подмножество записей в списке. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или от- вечают определенным критериям, при этом остальные строки оказываются скрыты.
Для фильтрации списка надо выделить ячейку списка и выполнить ко- манду Фильтр (Filter) из меню Данные (Data). Эта команда имеет следую- щие подкоманды:
Автофильтр (AutoFilter) – позволяет задать значения и несложные критерии, которым должны удовлетворять строки отфильтрованного списка;
Отобразить все (Show All) – выводит все строки предварительно от- фильтрованного списка;
Расширенный фильтр (Advanced Filter) – позволяет выполнить
фильтрацию списка с использованием сложных критериев.
Команда Автофильтр (AutoFilter) выводит в строке заголовков списка раскрывающееся меню, позволяющее выбрать определенные значения для каждого столбца списка. Значение Все (All) отменяет фильтрацию по столб- цу. Значение Условие (Custom) позволяет определить один или два критерия сравнения для одного и того же столбца. При задании критерия можно ис- пользовать операторы сравнения, символы шаблона и операторы «И» (And), «ИЛИ» (Or).
Чтобы убрать раскрывающееся меню в строке заголовков списка, следу- ет повторно выполнить команду Автофильтр (AutoFilter).
После фильтрации списка командой Автофильтр (AutoFilter) получен- ное подмножество данных можно с помощью буфера обмена скопировать в другое место для дальнейшего анализа данных.
Команда Расширенный фильтр (Advanced Filter) позволяет фильтро- вать данные, используя интервал критериев. Интервал критериев – это ин- тервал ячеек вне фильтруемого списка, содержащий набор условий поиска и состоящий из одной строки меток критериев и расположенной под ней хотя бы одной строки условий поиска.
Условия поиска, заданные в одной строке интервала критериев, соеди- няются связкой «И» (And), а заданные в разных строках – связкой «ИЛИ» (Or).
45
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Условия поиска задаются с помощью критериев сравнения и вычисляемых критериев.
Критерием сравнения может быть последовательность символов (на- пример, Петров) или выражение (>100). Метка критерия сравнения должна быть идентична заголовку столбца, для которого определяется критерий.
Вычисляемый критерий является результатом формулы (например, вы- числяемый критерий
=D5>СРЗНАЧ($F$6:$F$10) (=D5>AVERAGE($F$6:$F$10))
выводит на экран строки, имеющие в столбце D значение большее, чем сред- нее значение величин в ячейках F6:F10). Формула в вычисляемых критериях должна возвращать значение «ИСТИНА» (TRUE) или «ЛОЖЬ» (FALSE). При фильтрации на экран выводятся только те строки, значения которых придают формуле значение «ИСТИНА» (TRUE). Формула должна ссылаться хотя бы на один столбец из фильтруемого списка. При этом в формуле долж- на быть относительная ссылка на ячейку в списке, находящуюся в первой строке этого столбца (D5). Относительная ссылка необходима, поскольку только такая ссылка будет изменяться при вычислении формулы для каждой строки фильтруемого списка.
В случае использования вычисляемого критерия его метка должна от- личаться от заголовков столбцов списка.
Чтобы установить более одного критерия для одного столбца или задать диапазон значений в некотором столбце, надо ввести заголовок этого столбца в интервал критериев несколько раз.
После создания интервала критериев надо выделить любую ячейку в фильтруемом списке и выбрать команду Расширенный фильтр (Advanced Filter), в которой дополнительно указать следующие параметры фильтрации:
Обработка (Action) – определяет, куда поместить отфильтрованный список:
фильтровать список на месте (Filter the List, in-place) – скры-
вать строки, неудовлетворяющие указанному критерию,
скопировать результат в другое место (Copy to Another Location) – копировать в пределах текущего рабочего листа или на другой рабочий лист;
Исходный диапазон (List Range) – определяет интервал, содержа- щий список для фильтрации;
46
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Диапазон условий (Criteria Range) – определяет интервал ячеек, со- держащий заданные пользователем критерии;
Поместить результат в диапазон (Copy to) – определяет интервал ячеек, в который копируются строки, удовлетворяющие заданным критериям;
Только уникальные записи (Unique Records Only) – указывает не-
обходимость вывода строк, удовлетворяющих критерию и не содер- жащих повторяющихся значений.
5.4.Автоматическое подведение итогов
Одним из способов обработки и анализа списка является автоматиче- ское подведение различных итогов. Excel позволяет вставить в список строки итогов, содержащие промежуточные итоги для столбцов списка. Excel авто- матически в конце списка помещает строку общих итогов.
Для подведения итогов необходимо определить:
группы для подведения промежуточных итогов по ним (для отраже- ния итогов по годам надо выбрать столбец с указанием года);
функцию для подведения итогов (для вычисления суммы общих про- даж за год надо выбрать функцию Сумм (Sum));
данные, по которым подводятся итоги (для вывода суммы продаж за
год надо выбрать столбец, содержащий значения продаж).
Перед подведением итогов список должен быть отсортирован по столб- цу, содержащему группы.
Автоматическое подведение итогов для списка выполняется командой Итоги (Subtotals) из меню Данные (Data), которая предлагает определить следующие параметры подведения итогов:
При каждом изменении в (At Each Change in) – выбор столбца, со-
держащего группы, для которых необходимо вычислить промежу- точные итоги;
Операция (Use Function) – выбор функции, по которой вычисляется итог (по умолчанию используется функция Сумм (Sum) для числовых значений и функция Счет (Count) для текстовых значений, опреде- ляющая число непустых значений);
Добавить итоги по (Add Subtotal to) – выбор столбца, в котором должны подводиться итоги (можно выбрать более одного столбца);
47
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Заменить текущие итоги (Replace Current Subtotals) – определение необходимости замены всех итогов в списке новыми итогами (этот флажок надо снять для сохранения текущих итогов и вставки новых);
Конец страницы между группами (Page Break Between Groups) –
определение необходимости печати каждой группы данных на новой странице;
Итог под данными (Summary Below Data) – определение положе-
ния строк итогов и общих итогов над данными или под ними. Кнопка Убрать все (Remove All) позволяет удалить все итоги из актив-
ного списка.
При выводе итогов слева от таблицы возникают символы структуры, позволяющие скрывать и показывать детальные данные в списке.
5.5. Ведение списков с помощью формы данных
Форма данных позволяет легко просматривать, изменять, добавлять и удалять записи из списка, а также отыскивать конкретные записи по опреде- ляемому пользователем критерию.
Для получения формы данных текущего списка надо выбрать команду Форма (Form) из меню Данные (Data). Одновременно в форме могут нахо- диться данные только одной записи. Ввод или изменение данных в полях формы изменяет содержимое соответствующих ячеек в списке.
Имеющиеся в форме кнопки позволяют просматривать и редактировать данные. Кнопка Критерии (Criteria) позволяет задать в полях формы крите- рии сравнения для поиска нужного подмножества записей.
Содержание работы
1.На рабочем листе Лист 1 (Sheet 1) выполните следующие варианты сортировки с помощью команды Сортировка (Sort) из меню Данные (Data), каждый раз возвращаясь к исходному состоянию таблицы:
отсортируйте таблицу по возрастанию значений графы «ФИО»;
отсортировать таблицу по убыванию номеров школ, определив в качестве столбца для вторичной сортировки графу «ФИО» (пред- варительно обеспечив, чтобы несколько абитуриентов были из одной школы);
отсортируйте только первые две графы таблицы.
48
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
2.С помощью кнопок панели инструментов Стандартная (Standard) отсортируйте по фамилиям таблицы рабочих листов Лист 1 (Sheet 1)
и Лист 2 (Sheet 2).
3.На рабочем листе Лист 1 (Sheet 1) выполните с помощью команды Автофильтр (AutoFilter) следующие варианты фильтрации данных,
каждый раз возвращаясь к исходному состоянию таблицы:
выведите сведения об абитуриентах, окончивших одну и ту же школу;
выведите сведения об абитуриентах, средний балл которых боль- ше 3,5, но меньше 4;
выведите сведения об абитуриентах, фамилии которых начинают- ся с букв А или П.
4.На рабочем листе Лист 2 (Sheet 2) выполните следующие варианты фильтрации данных, каждый раз возвращаясь к исходному состоя- нию таблицы (создаваемые интервалы критериев на рабочем листе сохранять):
определите абитуриентов, получивших на всех экзаменах оценку не меньше 4;
определите абитуриентов, получивших хотя бы на одном экзамене оценку 3;
определите абитуриентов, получивших только одну оценку 3 на экзаменах;
определите абитуриентов, общая сумма баллов которых не мень- ше средней суммы баллов абитуриентов.
5.На рабочем листе Лист 1 (Sheet 1) выполните следующие действия:
отсортируйте таблицу по значениям графы «Школа»;
используя автоматическое подведение итогов, определите средний балл аттестатов абитуриентов, окончивших одну школу, и среднее отклонение их аттестатов от проходного балла;
с помощью символов структуры выполните показ и скрытие де- тальных данных списка;
используя автоматическое подведение итогов, добавьте к сущест- вующим итогам сведения о количестве абитуриентов, окончивших одну школу.
6.На рабочем листе Лист 2 (Sheet 2) рассмотрите варианты просмотра и редактирования таблицы с помощью формы данных этой таблицы.
49
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com
Лабораторная работа № 6
ПАКЕТ MS OFFICE: РЕАЛИЗАЦИЯ АНАЛИТИЧЕСКИ- ЧИСЛЕННЫХ МЕТОДОВ РЕШЕНИЯ ЗАДАЧ В EXCEL.
ПОИСК РЕШЕНИЙ И КОНСОЛИДАЦИЯ ДАННЫХ
Цель работы:
∙ознакомление с возможностями табличного процессора Excel по анализу данных с помощью средств поиска решений и обобщения данных;
∙получение соответствующих практических навыков.
6.1.Подбор параметра
Часто известен желаемый результат формулы, но не известны входные значения, которые нужны для его достижения. Для решения такой задачи можно использовать средство Excel, называемое подбором параметра, что позволит избежать больших временных затрат на повторение испытаний и анализ ошибок при нахождении решения.
При подборе параметра Excel варьирует значения в указанной входной ячейке до тех пор, пока формула, зависящая от этой ячейки, не возвратит ожидаемый результат.
Для использования этого средства надо выбрать команду Подбор пара- метра (Goal Seek) в меню Сервис (Tools) и задать следующее:
Установить в ячейке (Set cell) – задание ссылки или имени ячейки, содержащей формулу, для которой ищется решение;
Значение (To value) – задание желаемого значения в данной ячейке;
Изменяя значение ячейки (By changing cell) – задание ссылки на ячейку, содержащую значение (не формулу), которое будет меняться
иот которого прямо или косвенно зависит исходная формула. Появившееся диалоговое окно Результат подбора параметра (Goal
Seek Status) кнопкой Пауза (Pause) позволяет временно остановить процесс подбора параметра (с последующим его возобновлением), кнопкой Шаг (Step) – выполнить процесс подбора пошагово. Если требуемое решение не может быть достигнуто, то будет отображено соответствующее сообщение.
По завершении процесса подбора параметра Excel выведет на рабочем листе результаты, с которыми можно согласиться, оставив их на рабочем листе, или отказаться от них, восстановив исходные значения.
50
PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com