Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

Задание для самостоятельного выполнения

Построить в одной системе координат:

1. При графики двух функций и .

2. При графики двух функций и .

Задание 4. Построение поверхности

Продемонстрируем технологию построения поверхностей на примере следующей функции, зависящей от двух аргументов: z = x²-y² при х-2; 2, у -1; 1.

Прежде чем воспользоваться мастером диаграмм, надо построить таблицу значений функции z по обоим ее аргументам, например, по аргументу x от –2 до 2 с шагом 0,2, а по y от –1 до 1 с шагом 0,2.

Для этого:

1. 
   Введите в ячейку А2 значение –2, а в ячейку А3 – значение –1,8. Выберите диапазон ячеек А2:А3. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон А4:А22. Таким образом, значения аргумента x протабулированы от –2 до 2 с шагом 0,2.
   
2. 
   Введите в ячейку В1 значение –1, а в ячейку С1 значение –0,8. Выберите диапазон ячеек В1:С1. Расположите указатель мыши на маркере заполнения этого диапазона и протяните его на диапазон D1:L1. Значения аргумента y протабулированы от –1 до 1 с шагом 0.2.
   
3. 
   В ячейку В2 введите формулу:
   

= $A2^2 – B$1^2

4. 
   Выберите ячейку В2, расположите указатель мыши на маркере ее заполнения и протяните его вниз на диапазон В2:L22.
   

На рабочем листе рис. 1 показан результат табуляции функции, зависящей от двух аргументов, а на правом – часть формул, которые были введены в ячейке при копировании формулы, введенной в ячейку В2, перемещением маркера заполнения на диапазон В2:

---

L22.



Рис. 1. Таблица значений функции, зависящей от двух аргументов

Примечание. Использование в формуле абсолютной ссылки на строку и столбец существенно. Напомним, что знак $ в имени ячейки, стоящий перед номером строки, создает абсолютную ссылку на строку, а перед именем столбца – абсолютную ссылку на столбец. Поэтому при буксировке формулы из ячейки В2 на диапазоны В2:L22, в ячейках этого диапазона будут найдены значения функции z соответствующих значениях аргументов х и у.

Перейдем к конструированию поверхности по результатам табуляции. Для этого:

1. Выберите команду Вставка / Диаграмма.

2. В появившемся диалоговом окне Мастер Диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартные в списке Тип выберите значение Поверхность, а в списке Вид укажите стандартную поверхность. Нажмите кнопку Далее.

3. В следующем диалоговом окне мастера диаграмм на вкладке Диапазон данных выберите переключатель Ряды в столбцах, т.к. данные располагаются в столбцах. В поле ввода Диапазон приведите ссылку на диапазон данных А1:L22, т.е. диапазон, который содержит как значения аргументов, так и значения функции. Нажмите кнопку Далее.

4. В появившемся диалоговом окне Мастер диаграмм (шаг 1 из 4): параметры диаграммы на вкладке Заголовки в поле Название диаграммы введите Поверхность, в поле Ось Х (категорий) укажите x, в поле Ось Y (рядов данных) задайте у, в поле Ось Z (значений) введите z. На вкладке Легенда сбросьте флажок Добавить легенду. Нажмите кнопку Готово.

Поверхность построена, как показано на рис. 2.

Р
ис. 2. Построенная поверхность и диалоговое окно Формат трехмерной проекции

---

Задания для самостоятельного выполнения

1) Построить поверхность при х, у -1; 1.

2) Построить поверхность при х -2; 2, у -1; 1.

3) Построить поверхность при х, у -1; 1.

Лабораторная работа № 5. Работа с электронной таблицей Excel как с базой данных

Цель работы:

• 
  Изучить основные понятия списков и базы данных в Excel.
  
• 
  Освоить технологию создания списков в Excel, поиска записей, сортировки и фильтрации.
  
• 
  Научиться анализировать списки, использую многокритериальные условия.
  

В Microsoft Excel в качестве базы данных можно использовать список.

Список – это набор строк таблицы, содержащий связанные данные, например база данных счетов или набор адресов и телефонов клиентов. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов.

При выполнении обычных операций с данными, например, при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы списков учитываются при организации данных.

• 
  Столбцы списков становятся полями базы данных.
  
• 
  Заголовки столбцов становятся именами полей базы данных.
  
• 
  Каждая строка списка преобразуется в запись данных.
  

Рекомендации по созданию списка на листе книги

В Microsoft Excel имеется набор функций, облегчающих обработку и анализ данных в списке. Чтобы использовать эти функции, введите данные в список в соответствии с приведенными ниже рекомендациями.

Размер и расположение списка

• 
  На листе не следует помещать более одного списка. Некоторые функции обработки списков, например, фильтры, не позволяют обрабатывать несколько списков одновременно.
  
• 
  Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец. Это позволяет Microsoft Excel быстрее обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений.
  
• 
  В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.
  
• 
  Важные данные не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.
  

Заголовки столбцов

• 
  Заголовки столбцов должны находиться в первом столбце списка. Они используются Microsoft Excel при составлении отчетов, поиске и организации данных.
  
• 
  Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных.
  
• 
  Для отделения заголовков от расположенных ниже данных следует использовать границы ячеек, а не пустые строки или прерывистые линии.
  

Содержание строк и столбцов

• 
  Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.
  
• 
  Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.
  
• 
  Не следует помещать пустую строку между заголовками и первой строкой данных.
  

Ввод списка

При вводе данные можно добавлять непосредственно в ячейки списка, а можно воспользоваться специальной формой ввода.

Чтобы заполнять данные в список при помощи формы ввода, поместите курсор в любое место списка и выберите команду Данные→Форма. На экране появится диалоговое окно, в котором будет отображено каждое поле списка. При этом поля, содержащие формулы, хотя и отображаются в форме ввода, их значения изменить нельзя. Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.

Чтобы ввести новую запись, щелкните по кнопке Добавить. Форма очистится, и Вы сможете ввести нужную информацию в соответствующие поля. После этого снова щелкните по кнопке Добавить, а если не хотите больше добавлять записи – по кнопке Закрыть.

Вновь введенные данные появятся в конце списка. Формулы, содержавшиеся в ячейках списка, автоматически будут распространены и на новую запись

Форму ввода можно использовать не только для ввода данных. Она позволяет просматривать существующие записи, редактировать их, удалять и выборочно отображать данные по определенному критерию.

Порядок сортировки, используемый по умолчанию

Для упорядочения ячеек по значениям (без учета формата) предусмотрен определенный порядок сортировки.

При сортировке текста, значения сравниваются посимвольно слева направо. Например, если в ячейке содержится текста “А100”, то после сортировки она будет находиться после ячейки, содержащей “A1” и перед ячейкой, содержащей “A11”.

При сортировке по возрастанию в Microsoft Excel используется следующий порядок (при сортировке по убыванию этот порядок заменяется на обратный за исключением пустых ячеек, которые всегда помещаются в конце списка):

• 
  Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.
  
• 
  Текст, в том числе содержащий числа, сортируется в следующем порядке:
  

0 1 2 3 4 5 6 7 8 9 ‘ – (пробел) ! “ # $ % & () * , . / : ; ? @ [ \ ] ^ _ ` { | } + < = >

---

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

• 
  Логическое значение ЛОЖЬ предшествует значению ИСТИНА.
  
• 
  Все ошибочные значения равны.
  
• 
  Пустые ячейки всегда помещаются в конец списка.
  

Фильтрация списка

При работе с большим объемом данных надо уметь быстро находить важную информацию или делать выборку данных, удовлетворяющих некоторому условию. Ограничения на вывод информации в списке можно осуществить с помощью фильтров.

Р ассмотрим применение Автофильтра. Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные→Фильтр→Автофильтр. При включении Автофильтра возле имен полей списка появляются кнопки со стрелками .

При щелчке по любой из этих кнопок раскрывается меню, содержащее команды и список значений данного поля. С помощью этого меню можно отобрать все записи с заданным значением поля.

Опции фильтрации: Все – чтобы отключить ранее заданный фильтр, отобразить все записи. Условие – создание своего фильтра. Первые 10 – отображение только первых десяти значений столбца.

Обратите внимание на цвет стрелок на кнопках Автофильтра, если Автофильтр включен, кнопки окрашиваются в синий цвет.

Закрепление заголовка списка

Microsoft Excel позволяет фиксировать столбцы и строки. Это удобно в тех случаях, когда список данных длинный и не умещается в видимой области окна. Например, закрепленный заголовок таблицы при просматривании данных будет оставаться на своем месте.

Работая с большими таблицами, нужно учитывать следующие особенности фиксации заголовков:

• 
  чтобы зафиксировать горизонтальные заголовки выделите строку ниже заголовка;
  
• 
  чтобы зафиксировать вертикальные заголовки выделите столбец справа от заголовка;
  
• 
  чтобы зафиксировать и вертикальные, и горизонтальные заголовки выделите ячейку, по которой хотите зафиксировать заголовки.
  

---

Затем выполните команду Окно → Закрепить области. Снять закрепление областей можно командой Окно → Снять закрепление областей.

Задание 1.

1. 
   Н
   азовите лист ПЛАНЕТЫ СОЛНЕЧНОЙ СИСТЕМЫ.
   
2. 
   Создайте следующий список и отформатируйте его согласно образцу:
   

О братите внимание, что единицы измерения в таблице следующие:

• 
  период обращения по орбите – в земных годах;
  
• 
  среднее расстояние от Солнца ­– в млн. км;
  
• 
  экваториальный диаметр – в тыс. км;
  
• 
  масса – в 10²⁴ кг.
  

3. 
   Выберите команды ДАННЫЕ → ФОРМА. В появившемся диалоговом окне нажмите кнопку ДОБАВИТЬ.
   
4. 
   Введите данные по планетам Нептун (период – 164,8; расстояние – 4496; диаметр – 50,2; масса – 103,88; спутники – 2) и Плутон (период – 247,7; расстояние – 5900; диаметр – 2,8; масса – 0,1; спутники – 1).
   
5. 
   После ввода данных по Нептуну и Плутону нажмите кнопку ЗАКРЫТЬ. Две новые записи появятся в списке.
   

• 
  Выделите ячейку А3.
  
• 
  В ыберите команды ДАННЫЕ →СОРТИРОВКА.
  
• 
  В диалоговом окне СОРТИРОВКА ДИАПАЗОНА выберите СОРТИРОВКУ по – Планета, ПОРЯДОК СОРТИРОВКИ – по возрастанию.
  
• 
  Нажмите кнопку ОК.
  

Ваш список отсортирован по названиям планет в порядке возрастания.

6. 
   Выберите команды ДАННЫЕ → ФИЛЬТР → АВТОФИЛЬТР.
   

Н а именах полей появились кнопки с изображением стрелок вниз.

6. 
   Нажмите на кнопку в ячейке ПЛАНЕТЫ. Выберите пункт УСЛОВИЕ.
   
7. 
   В диалоговом окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР задайте критерии, позволяющие осуществить поиск планет, начинающихся на букву «С» или «П».
   
8. 
   В вашей базе данных должно остаться только три записи – Солнце, Сатурн, Плутон.
   
9. 
   Н ажмите на кнопку в ячейке МАССА. Выберите пункт УСЛОВИЕ.
   
10. 
    В диалоговом окне ПОЛЬЗОВАТЕЛЬСКИЙ ФИЛЬТР задайте критерий: Масса <600.
    
11. 
    В вашей базе данных должно остаться всего 2 записи – Сатурн, Плутон.
    
12. 
    Для того чтобы убрать ФИЛЬТР выполните команды: ДАННЫЕ, ФИЛЬТР, ОТОБРАЗИТЬ ВСЕ.
    
13. 
    Выделите диапазон ячеек A3:F12.
    
14. 
    Выберите команды: ДАННЫЕ, СОРТИРОВКА.
    
15. 
    В диалоговом окне СОРТИРОВКА отсортируйте планеты в порядке убывания по количеству спутников.
    

---