Информатика - Борисов
.pdf15. Практическое закрепление навыковработыстаблицамивExcel
Например, на рисунке показано, что для фильтрации выбран столбец списка Имя. Условие фильтрации: отображать записи, у ко- торых Имя равно Иван ИЛИ не равно Петр. После выполнения фильтрации отобразятся записи, содержащие в поле для имени Иван и Сидор.
Команда Меню Данные→Фильтр→Отобразить все отображает все записи списка. Для отмены автофильтра нужно повторить команду Меню Данные→Фильтр→Автофильтр.
15.3. Построение столбчатых и круговых диаграмм
Для наглядного отображения табличных данных используются средства деловой графики (графики и диаграммы). Программа Excel предоставляет пользовательский интерфейс, позволяющий создавать диаграммы различного вида, строить на них линии тренда с выводом формул и оценок точности аппроксимации экспериментальных дан- ных функциями, а также размещать результаты в рабочей книге. Причем если диаграмма размещена на том же листе, что и таблица, то при внесении изменения в таблицу автоматически меняется гра- фическая форма отображения исследуемых свойств вводимых дан- ных.
Создание диаграмм и графиков реализовано с помощью специ- ального Мастера диаграмм, вызвать который можно командой Меню Вставка→Диаграмма…, или одноименной кнопкой па панели инст- рументов Стандартная. Мастер диаграмм подготавливает создание диаграммы и работает в несколько этапов. В начале нужно выбрать тип диаграммы. Затем указать, где расположены входные данные для графической интерпретации. Указать особенности оформления гра- фических результатов. В конце выбрать рабочий лист для размеще- ния готовой диаграммы. После команды Готово диаграмма или гра- фик создается на указанном листе.
Готовая диаграмма включает элементы, которые можно выделять и затем изменять (редактировать). В частности, например, если щелкнуть правой кнопкой мыши по точке графика, то можно вы- звать контекстное меню, с помощью которого добавить на график линию тренда и все, что с ней связано.
15.4. Решение математических задач средствами Excel
Решение математических задач рассмотрим на примерах изуче- ния геометрических объектов по их уравнениям. Программа Excel представляет широкие возможности визуализации решений задач
231
Раздел III. Современные информационные технологии
аналитической геометрии путем построения кривых на плоскости и поверхностей в пространстве.
Для описания точек геометрических объектов часто используется декартова система координат. В заданиях, приведенных ниже, рас- сматриваются примеры построения прямой линии и плоскости в пространстве, построения кривых второго порядка (эллипса и ги- перболы) на плоскости, а также эллипсоида в трехмерном простран- стве. И хоть сами по себе эти задачи не совсем просты, однако при- менив средства Мастера диаграмм программы Excel, можно сложные вопросы оставить за рамками рассмотрения, в тоже время решив за- дачу построения кривых и поверхностей геометрических объектов.
Задание 15.1. Самостоятельная работа «Обработка списков в Excel»
Создать таблицу участников соревнований, используя режимы манипулирования с табличными данными (сортировка, фильтрация, форма и список).
Сортировка
1.Создайте на первом листе таблицу Список участников соревнова- ний. Вначале нужно определить исходные данные задачи: фамилии и имена спортсменов (текст), вес (число), название региона (текст), на- бранные в ходе состязаний очки и место, занятое на соревновании (числа).
2.Введите заголовки таблицы и заполните ее данными, например, какпоказановтаблицениже.
3. Отсортируйте список участников, упорядочив его в порядке воз- растания весовых категорий. Для этого установите курсор в столбец С
232
15.Практическое закрепление навыковработыстаблицамивExcel
ищелкнитекнопкуСортировкаповозрастаниювпанели инструмен- тов Стандартная. После этого порядок размещения записей о спорт- сменахвтаблицеизменится.
4. Найдите участников соревнований изСамары. Для этого в меню Правкавыберите командуНайти, затемвокнеНайти и заменитьвпо- леНайтивведитеобразпоиска«Самара», щелкнувкнопкуПараметры,
задайте опции поиска: не учитывать регистр, просматривать по стро-
кам. Для начала поиска щелкните кнопкуНайти все. Курсор выделяет первую ячейку с найденным текстом. Кроме этого, выводится список всех найденных строк таблицы с возможностью быстрого перехода к выбраннойстроке. Закройтеокнопоиска.
Фильтры
5. Для использования фильтров необходимо выделить диапазон ячеек, например А2:F16. Затем выполнить команду Меню Дан- ные→Фильтр→Автофильтр. В результате рядом с заголовком каждого столбца появится кнопка вызова выпадающего списка атрибутов фильтрации. Нарисункенижепоказаныатрибутыдлястолбца«Вес».
233
Раздел III. Современные информационные технологии
6. Отфильтруйте список спортсменов и просмотрите участников состязаний в весовой категории от 100 до 130кг. Для этого в выпадаю- щем списке выберите подраздел (Условие…) и задайте атрибуты для фильтракакпоказанонарисункениже.
7.Врезультатевспискеостанутсятолькотристроки(3, 9 и11).
8.Можно применить фильтр к уже отфильтрованному списку. Щелкнувстрелкувстолбце«Имя», установите фильтр«Антон». После этого в таблице останется только запись о спортсмене из строки 11 (Вес=130 иИмя=«Антон»).
9.Удалите фильтр Имя=«Антон»; для этого нажмите кнопку со стрелкой в столбце «Имя», а затем выберите из развернувшегося спи- ска(Все).
234
15.Практическое закрепление навыковработыстаблицамивExcel
10.Чтобы удалить фильтры для всех столбцов списка, нужно вы- брать пункт Фильтр в меню Данные, а затем команду Отобразить все. Для удаления автофильтра выберите пункт Фильтр в меню Данные, а затемуберитегалочкуАвтофильтр.
Формы
11. Для просмотра записей таблицы с использованием формы вы- полните команду Меню Данные→Фильтр→Автофильтр, затем выбе- ритекомандуМенюДанные→Форма. Послеэтогонаэкранераскроет- сяформасотображениемпервойзаписитаблицы.
12.Вокне формы над кнопкой «Добавить» отображается информа- ция 1 из 14, т.е. в форме отображается текущая запись с номером 1, всегозаписейвтаблице14. Дляпросмотраследующейзаписищелкни- текнопку«Далее», предыдущейзаписи– «Назад».
13.Для того чтобызадать фильтр отбора данных, щелкните кнопку «Критерии», затемв полеРегионвведите значение «Самара» и нажмите клавишу Enter. После этого в форме будут отображаться только дан- ные об участниках из Самары. Для просмотра остальных записей, удовлетворяющих условиям отбора Регион=«Самара» используйте кнопки«Далее» и«Назад».
235
Раздел III. Современные информационные технологии
14.Для отмены фильтрации данных следует щелкнуть кнопку «Критерии» и очистить поле, в котором задано значение фильтра отбора данных (в данном примере в поле Регион удалить значе- ние «Самара»).
15.Спомощьюформылегкодобавитьновуюзаписьвтаблицу. Для этого щелкните кнопку «Добавить» и введите значения полей новой записи, используяклавишуTab дляперемещениякследующемуполю. Задайте следующие данные новой записи: Петров, Василий, 97, Тула, 5, 12. Завершите ввод данных, нажав клавишу Enter. Обратите внима- ние, что количество записей в таблице, отображаемое над кнопкой «Добавить», увеличилось на 1. Добавление записи производится в ко- нецтаблицы.
16.ИзменитезаписьобучастникеизУльяновска, длячегощелкнув кнопку Критерии, в поле Регион введите значение «Ульяновск» и на- жмите Enter, затем измените данные в записи (например, замените фамилию «Мкртчян» на «Зайцев»). После изменения данных нажми- теклавишуEnter дляобновлениятекущейзаписи.
17.Для удаления записи об участнике из Ульяновска установите ее текущей, а затем щелкните кнопку «Удалить». Подтвердите удаление записи, щелкнувкнопкуОК.
Списки
18. ОтменитеАвтофильтр. Затемвыделите заголовок таблицы ихо- тя бы одну запись и выполните команду Меню Данные → Список → Создатьсписок. КромеспискапоявитсяпанельинструментовСписок.
236
15.Практическое закрепление навыковработыстаблицамивExcel
19.Если первоначально были выделены не все строки таблицы, то нижнюю правую границу списка можно переместить, потянув мыш- койзамаркервправомнижнемуглустрокиссимволом*.
20.Выделитьв списке ячейку строкис фамилией «Ноздринов». За- тем на панели инструментов Список выбрать Вставить→Строка. Свер- ху от выделенной строки списка появиться не заполненная строка, которую можно заполнить либо непосредственно по ячейкам, либо с помощьюформы. ВведитеНепетров, Василий, 101, Подольск, 6, 12.
21.Для обратного преобразования списка в диапазон нажмите на панелиинструментовСписок→Преобразоватьвдиапазон.
22.ЗавершитеработуExcel.
РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.
Задание 15.2. Линия и плоскость в пространстве
Линия в пространстве
Уравнение прямой линии «в отрезках» – это линия первого по- рядка в двумерном декартовом пространстве прямоугольных коор-
динат, которая определяется уравнением |
x |
+ |
y |
=1, |
|
a |
b |
||||
|
|
|
где а – «отрезок» от начала системы координат до пересечения прямой линии с осью 0Х;
b – «отрезок» от начала системы координат до пересечения прямой линии с осью 0Y.
Для построения прямой линии в Excel уравнение необходимо разрешить относительно переменной Y.
Задание
Пусть a=3, b=2. Построить часть линии, лежащую в диапазоне переменной: x [−5;5].
Шаг перебора точек по X установить 0,5.
Решение
Сначала решим уравнение относительно переменной Y. y = b − ba x .
1.ВячейкиА1:D1 заносимтекст«X», «Y», «a» и «b» соответственно.
2.В ячейку С2 число 3 (коэффициент a), в ячейку D2 число 2 (ко- эффициентb).
3.ВведемзначенияпеременнойХвстолбецА:
•в А2 – левую границу диапазона (-5);
•в А3 – (начальная точка (-5) плюс шаг (0,5), т.е. =А2+0,5);
237
Раздел III. Современные информационные технологии
•выделив А3, с помощью автозаполнения вводим данные в блок А4:А22.
4. ЗначенияпеременнойY вводимвячейкистолбцаВ2:В22:
•в В2 – формулу для вычисления точки переменной Y для левой границы диапазона (-5), т.е. =$D$2-($D$2/$C$2)*A2;
•выделив В2, с помощью автозаполнения вводим данные в блок
B2:B22;
•для наглядности уменьшить число знаков после запятой до од-
ного.
5. Для построения диаграммы на панели инструментов нажать кнопкуМастердиаграмм.
Шаг1. ВыбратьтипдиаграммыГрафик, видГрафиксмаркерами. Шаг2.
•Выбрать входной Диапазон данных (В2:В22);
•ориентацию осей – положение в столбцах;
•на вкладке Ряд в поле Подписи оси Х (А2:А22).
Шаг 3. Ввести заголовок диаграммы «Прямая линия», названия Ось«Х», Ось«У». Шаг4. Готово.
6. Сравнитьрезультатсрисунком, которыйприведенниже.
7.При необходимости выровнять данные и добавить внешние и внутренниеграницывтаблице.
8.Изменить коэффициенты «a» и «b». Убедиться, что про- изойдет автоматический пересчет таблицы и поменяется график прямой линии.
РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.
238
15. Практическое закрепление навыковработыстаблицамивExcel
Плоскость в пространстве
Уравнение плоскости «в отрезках» – это поверхность первого по- рядка в трехмерном декартовом пространстве прямоугольных коор-
динат, которая определяется уравнением |
x |
+ |
y |
+ |
z |
=1, |
|
a |
b |
c |
|||||
|
|
|
|
где а – «отрезок» от начала системы координат до пересечения прямой линии с осью 0Х;
b – «отрезок» от начала системы координат до пересечения пря- мой линии с осью 0Y; с – «отрезок» от начала системы координат до пересечения прямой линии с осью 0Z.
Для построения поверхности плоскости в Excel уравнение необ- ходимо разрешить относительно переменной Z.
Задание
Пусть a=3, b=2, с=1. Построить часть линии, лежащую в диапа- зоне переменной: Шаг перебора точек по X и Y установим 0,5.
Решение
Сначала решим уравнение относительно переменной Z.
z= c(1 − ax − by ).
1.ВячейкуА1 заносимтекст«X\Y».
2.ВячейкиK1:M1 заносимтекст«a», «b» и «c», соответственно.
3.В ячейку K2 число 3 (коэффициент a), в ячейку L2 число 2 (ко- эффициентb) ивячейкуM2 число1 (коэффициент с).
4.ВведемзначенияпеременнойХвстолбецА:
•в А2 – левую границу диапазона (-3);
•в А3 – (начальная точка (-3) плюс шаг (0,5), т.е. =А2+0,5);
•выделив А3, с помощью автозаполнения вводим данные в блок А4:А14.
5. ЗначенияпеременнойY вводимвпервуюстрокуВ1:J1:
•в В1 – левую границу диапазона (-2);
•в С1 – (начальная точка (-2) плюс шаг (0,5), т.е. =В1+0,5);
•выделив С1, с помощью автозаполнения вводим данные в блок
D1:J1.
•для наглядности уменьшить число знаков после запятой до
двух.
239
Раздел III. Современные информационные технологии
6. ДалеевводимзначенияпеременнойZ:
• в В2 – вводим функцию $M$2*(1-$А2/$K$2-B$1/$L$2);
• копируем формулу в блок В2:J14 ;
• результаты вычислений свести в таблицу.
7. Для построения диаграммы на панели инструментов нажать
кнопку Мастердиаграмм.
Шаг1. ВыбратьтипдиаграммыПоверхность, видПроволочная. Шаг2.
•Выбрать входной Диапазон данных (В2:J14);
•ориентацию осей – положение в столбцах;
•на вкладке Ряд в поле Подписи оси Х (А2:А14);
•привязать ряды к точкам переменной Y в первой строке табли-
цы (Ряд1 – В1, Ряд2 – С1,…,Ряд9 – J 1,).
Шаг 3. Ввести заголовок диаграммы «Плоскость», названия Ось
«Х», Ось«У», Ось«Z». Шаг4. Готово.
8. Сравнитьрезультатсрисунком, которыйприведенниже.
9.При необходимости выровнять данные и добавить внешние и внутренниеграницывтаблице.
10.Изменить коэффициенты «a», «b» и «с». Убедиться, что про- изойдетавтоматический пересчет таблицы ипоменяется графикплос- кости.
РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.
240