41788

Приемы обработки числовой информации в среде Excel

Лабораторная работа

Информатика, кибернетика и программирование

Введите в ячейки А1 и А2 заголовок таблицы заполнив ячейки своими данными. Выполните объединение ячеек а 1:H1 и б 2:H2 Введите текстовые значения в ячейки А3:А8 и В3:H3. Скопируйте формулу из ячейки H4 в ячейки диапазона H5:H8. Для копирования ячейки перетащите выделение удерживая нажатой кнопку мыши в ячейки блока H5:H8.

Русский

2013-10-25

90.72 KB

20 чел.

Лабораторная работа№4

Приемы обработки числовой информации в среде Excel

Задание 1

  1. Запустите приложение MS Excel. В заголовке текущего окна появится название табличного документа «Книга1». Дайте ему имя  «Оценки», выполнив команду Файл /Сохранить как … (в свою рабочую папку).
  2. Введите в ячейки А1 и А2 заголовок таблицы, заполнив ячейки своими данными. Выполните объединение ячеек а) A1:H1 и б) A2:H2
  3. Введите текстовые значения в ячейки А3:А8 и В3:H3.
  4. Заполните оценками диапазон ячеек B4:G8.
  5. В ячейку Н4 введите формулу =МИН(F4;G4).
  6. Скопируйте формулу из ячейки H4 в ячейки диапазона H5:H8. Алгоритм копирования:
  7. Выберите ячейку H4, содержащую копируемую формулу.
  8. Установите указатель на рамку выделения и нажмите кнопку мыши.
  9. Для копирования ячейки перетащите выделение, удерживая нажатой кнопку мыши, в ячейки блока H5:H8.
  10. В ячейку F9 введите текст «Средний балл:», объедините ячейки блока F9:G9.
  11. В ячейку H9 введите формулу =СРЗНАЧ(H4:H8); установите формат ячейки Числовой / Число десятичных знаков возьмите равным 1.
  12. Выполните форматирование данных в ячейках и границ (см. образец).

 

A

B

C

D

E

F

G

H

1

Оценки

2

за 2006/2007 учебный год

3

Предмет

I

II

III

IV

За год

Экзамен

Итоговая

4

Русский язык

4

5

4

4

4

4

4

5

Литература

5

4

5

5

5

5

5

6

Алгебра

4

3

4

4

4

4

4

7

Геометрия

4

4

5

4

4

5

4

8

Информатика

4

4

5

5

5

5

5

9

Средний балл:

4,4

  1. Постройте диаграмму успеваемости по предметам
  2. Выделите ячейки A3:E8. 
  3. На панели инструментов щелкните по значку диаграммы
  4. Следуя указаниям мастера постройте гистограмму.
  5.  Постройте диаграмму успеваемости по предметам в первой и четвертой четверти:
  6. Удерживая CTRL  выделите столбцы А3:А8, В3:В8, Е3:Е8.
  7. С помощью инструмента   постройте гистограмму.
  8. Постройте круговую диаграмму итоговой успеваемости по предметам.

Задание 2

Относительные и абсолютные ссылки. Функции.

Если при изменении положения формулы (при копировании и распространении) автоматически меняются ссылки на ячейки относительно исходной, то такие ссылки называются относительными.

Относительная ссылка используется в формуле для указания адреса ячейки, вычисляемого в относительной системе координат с началом в текущей ячейке.

Относительные ссылки имеют следующий вид: А1, В1 и т.п..

Подготовьте таблицу по образцу, поместив ее в ячейках A1 – E3.

В ячейке В3 находится цена за единицу товара.  

В ячейке C3 - формула =B3 * C2 (цена за единицу товара умножить на количество).

A

B

C

D

E

1

Наименование товара

Количество

2

1

2

3

4

3

Мороженное

12,70

=B3 * C2

С помощью маркера заполнения распространите формулу вправо для получения стоимости товара за 3 и 4 единицы. Сравните свой результат с приведенным ниже.

A

B

C

D

E

1

Наименование товара

Количество

2

1

2

3

4

3

Мороженное

12,70

25,40

76,20

304,80

Можно заметить, что вычисленная по формуле стоимость товара за три единицы неверна. Если выделить ячейку D3, то в Строке формул появится формула C3*D2, а должна быть формула B3*D2.

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

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

Абсолютные ссылки имеют вид:  $F$9; $C$45. Для фиксации координат применяется знак $.

Следовательно, для того, чтобы получить верные результаты в нашем примере, в ячейке C3 - формула =$B$3 * C2.

Абсолютная ссылка используется для указания адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки.

Абсолютные ссылки имеют вид: $А$5, $F$5, $G$3  и т.п.

Примечание

Для того, чтобы относительную  ссылку преобразовать в абсолютную,  достаточно после ввода ссылки нажать клавишу F4 – и знаки доллара появятся автоматически.

Задание 3

Подготовьте таблицу для начисления пени в соответствии с образцом.

Оплата коммунальных услуг задержана на

дней

Вид оплаты

Начисленная сумма

Пени

Всего к оплате

Квартплата

Газ

Электричество

Телефон

Пени высчитывается по формуле – 1% от начисленной суммы за каждый задержанный день.

Всего к оплате считается как сумма начисления плюс пени.

Добавьте в таблицу строку для подсчета итоговых показателей: 

всего начислено, всего пени, всего к оплате.

Задание 4

  1. Выполните ввод исходных данных по образцу.
  2. Введите соответствующие смыслу формулы в ячейки E4 и F4 (для вычисления налога используйте абсолютную ссылку В$11).
  3. Скопируйте вниз формулы: из ячейки E4 в блок ячеек E5:E9;
    из ячейки F4 в блок ячеек F5:F9 (по стрелке).
  4. Введите по смыслу формулы в ячейки F13, E16, E19.
  5. Отформатируйте числа в столбце F, оставив две цифры в дроби. (Формат, Ячейки, Числовой …)
  6. Постройте диаграмму на отдельном листе для блока F4:F9.
  7. Выполните сортировку данных по столбцу F.

A

B

C

D

E

F

1

Прибыль по предприятиям

2

Название

предприятия

Доход (р.)

Налог (р.)

3

апрель

май

июнь

квартал

за квартал

4

ОТЗ

15000

12000

8400

5

Петрозаводскмаш

24446

19800

15500

6

Северянка

6380

4700

5200

7

БОП

38000

34900

28000

8

Славмо

14580

13800

15670

9

Хлебозавод

12300

11700

13400

10

11

Налог (в %)

13

12

Сумма налогов

за квартал (р.)

13

14

15

Максимальный до-

ход за квартал (р.)

16

17

18

Средний доход

за квартал (р.)

19

Задание 5. 

Создайте ЭТ “Доставка груза” по образцу:

A

B

C

D

E

F

1

НАРЯД-ЗАДАНИЕ НА ДОСТАВКУ ГРУЗА

2

Номер

заявки

Наименование

груза

Стоимость груза (р.)

Расстояние

км

N

этажа

Доставка

р.

3

4

1

Диван

4300

3

1

5

2

Книжный шкаф

3750

6

4

6

3

Холодильник

8500

10

5

7

4

Мягкая мебель

13200

5

8

8

5

Стол

1500

5

8

9

ВСЕГО

10

Наценка (%) зависит от стоимости груза

5

11

Наценка (р./км) зависит от расстояния

3

12

Наценка (р./этаж) зависит от N этажа

2,5

Пояснение.

Формула в ячейке F4 вводится с учетом того, что стоимость доставки груза складывается из следующих трех величин (наценок):

а) 5% от стоимости груза,

б) от расстояния (3 р./км),
в) номера этажа (2,5 р./этаж).

Задание 6 

Получите таблицу умножения (см. образец).

Примечание.
Для первой строки и столбца А используйте
Заполнение рядов чисел. Формулу, введенную  в ячейку В2, скопируйте вначале вниз по столбцу (по стрелке),  а затем, выделив этот столбец, скопируйте формулу из ячеек этого столбца вправо.

Таблица умножения

A

B

C

D

E

F

G

H

I

J

1

1

2

3

4

5

6

7

8

9

2

1

1

2

3

4

5

6

7

8

9

3

2

2

4

6

8

10

12

14

16

18

4

3

3

6

9

12

15

18

21

24

27

5

4

4

8

12

16

20

24

28

32

36

6

5

5

10

15

20

25

30

35

40

45

7

6

6

12

18

24

30

36

42

48

54

8

7

7

14

21

28

35

42

49

56

63

9

8

8

16

24

32

40

48

56

64

72

10

9

9

18

27

36

45

54

63

72

81

В ячейку В2 следует ввести формулу: =$A2*B$1

Из справочной системы MS Excel 2000

Заполнение рядов чисел, дат или других элементов

  1. Выделите первую ячейку диапазона, который требуется заполнить, и введите начальное значение. Чтобы задать для ряда конкретное приращение, выберите следующую ячейку диапазона и введите следующий элемент ряда. Величина приращения ряда определяется разностью значений двух начальных его элементов.
  2. Выделите ячейку или ячейки, содержащие начальные значения.
  3. Перетащите маркер заполнения через заполняемые ячейки.
    Для заполнения в возрастающем порядке перетащите маркер вниз или вправо.
    Для заполнения в убывающем порядке перетащите маркер вверх или влево.

Копирование данных внутри строки или столбца

  1. Выделите ячейки, содержащие данные, которые необходимо скопировать.
  2. Протащите маркер заполнения по заполняемым ячейкам.

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

Задание 7.  Работа с электронной таблицей как с базой данных

1. Заполните таблицу, содержащую информацию о планетах солнечной системы, согласно рис. 1 и сохраните ее под именем work6.xls.

Единицы измерения, используемые в таблице:

- период обращения по орбите, в земных годах;
- среднее расстояние от Солнца, млн. км;
- экваториальный диаметр, тыс. км;
- масса - 1024 кг.

 

Рис. 1

Основные понятия баз данных

Область таблицы А2:F12 можно рассматривать как базу данных. Столбцы A, B, C, D, E, F этой таблицы называются полями, а строки 3-12 называются записями. Область А2:F2 содержит имена полей.

Существуют ограничения, накладываемые на структуру базы данных:

- первый ряд базы данных должен содержать неповторяющиеся имена полей;
- остальные ряды базы данных содержат записи, которые не должны быть пустыми рядами;
- информация по полям (столбцам) должна быть однородной, т.е. только цифры или только текст.

Основная функция любой базы данных - поиск информации по определенным критериям. С увеличением количества записей поиск определенной информации затрудняется. Ехсеl позволяет упростить этот процесс путем фильтрации данных.

Фильтрация данных

Команды ДАННЫЕ, ФИЛЬТР позволяют выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр АВТОФИЛЬТР, так и через РАСШИРЕННЫЙ - ручной.

Автофильтр

При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных или выделить ее. Затем нужно выполнить команды: ДАННЫЕ, ФИЛЬТР, АВТОФИЛЬТР. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт ВСЕ отключает фильтрацию, а пункт УСЛОВИЕ вызывает диалоговое окно, в котором можно установить параметры фильтрации (рис. 2). Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ.

Рис 2

2. С использованием Автофильтра осуществить поиск планет, начинающихся на букву "С" или букву "Ю" с массой менее 600*1024 кг.

2.1. Выполните команды: Данные, Фильтр, Автофильтр. На полях появились кнопки.

2.2. Нажмите на кнопку на поле Планета. Выберите пункт УСЛОВИЕ.

2.3. В диалоговом окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР задайте критерии согласно рис.6.2 и нажмите на кнопку <ОК>.

Проверьте! В базе данных остались планеты: Солнце, Юпитер, Сатурн.

2.4. Нажмите на кнопку на поле Масса . Выберите пункт УСЛОВИЕ.

2.5. В диалоговом окне задайте критерий: Масса < 600.

Проверьте! Остался только Сатурн.

2.6. Выполните команды меню: ДАННЫЕ, ФИЛЬТР, ПОКАЗАТЬ ВСЕ.

3. С использованием Автофильтра самостоятельно:

1) осуществите поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу менее 4*1024кг (Меркурий, Марс, Плутон)'

2) осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*1024 до 500*1024 кг, а также не более 2 спутников (Венера, Земля, Нептун).

Расширенный фильтр

При использовании РАСШИРЕННОГО ФИЛЬТРА необходимо сначала определить (создать) три области (рис.3):

- исходный диапазон - это область базы данных (А2:F12);

- диапазон условий отбора (или интервал критериев) - это область, где задаются критерии фильтрации (А14:F15);

- диапазон, в который при желании пользователя Excel помещает результат выборки (интервал извлечения) - это та область, в которой будут появляться результаты фильтрации (А17:F21).

Рис..3

Имена полей во всех интервалах должны точно совпадать.

Для выполнения действий по фильтрации необходимо воспользоваться командами меню: ДАННЫЕ, ФИЛЬТР, РАСШИРЕННЫЙ ФИЛЬТР. В диалоговом окне необходимо указать координаты интервалов.

Если необходимо получать результаты фильтрации в интервале извлечения, нужно поставить [*] - СКОПИРОВАТЬ РЕЗУЛЬТАТ  В   ДРУГОЕ МЕСТО (рис. 4).

4. С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.

4.1. Создайте диапазон условий отбора в ячейках A14:F15 (см. рис. 6.3).

4.2. Запишите условия отбора в диапазон условий отбора (см. рис.6.3).

4.3. Создайте интервал извлечения, куда будут помещены результаты фильтрации в ячейки A17:F17

4.4. Поместите курсор в область базы данных (A2:F12)

4.5. Выполните команды: ДАННЫЕ, ФИЛЬТР, РАСШИРЕННЫЙ ФИЛЬТР.

4.6. Проверьте правильность задания интервалов (см.рис.6.4). Нажмите кнопку <ОК>,

Проверьте! Найдены планеты Юпитер, Сатурн, Уран, Нептун.

Рис  4

Сохраните результаты в файле planeta.xls

6.  С использованием Расширенного фильтра самостоятельно:

1) найдите  записи о планетах, имеющих период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км 

2) осуществите поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км и имеющих 1 спутник 

3). найдите записи о планетах Меркурий, Сатурн или Плутон

4). найдите записи о планете Меркурий или планетах, имеющие   более одного спутника 

5).  найдите записи о планетах, имеющие период обращения более 1 года и массу менее 100 * 1024 кг

6) найдите записи о планетах, которые расположены от Солнца на расстоянии более 1000 млн. км  с диаметром более 50 тыс. км.   или находящихся от Солнца на расстоянии менее 150 млн. км и имеющие диаметр более 10 тыс. км.

7). найдите записи о планетах у которых период обращения менее 10 лет и количество спутников не равно 0 или записи о планетах, у которых диаметр менее 10 тыс. км.

Сортировка данных

Команды ДАННЫЕ, СОРТИРОВКА позволяют упорядочивать (сортировать ) базу данных.

Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команды: ДАННЫЕ, СОРТИРОВКА. При этом появится диалоговое окно. Нужно установить флаг [*]- Идентифицировать поля по ПОДПИСЯМ (ПЕРВАЯ СТРОКА ДИАПАЗОНА) и выбрать название поля, по которому нужно производить сортировку (рис. 5).

Кроме того, необходимо указать метод сортировки: по возрастанию или по убыванию и нажать кнопку <ОК>.

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

Рис. 5

7. Отсортируйте данные в таблице в порядке убывания количества спутников.

8. Отсортируйте данные в таблице в алфавитном порядке названий планет.

9. Отсортируйте данные в порядке возрастания их расстояния от Солнца.

Задание 8

Условная функция в среде MS Excel.

Определите сумму, которую придется заплатить за потребленную электроэнергию. Выполните расчет, введя свои исходные данные (столбцы «Дата» и «Показания счетчика»).

Расход электроэнергии

Месяц

Дата

Показания

счетчика кВт/ч

Расход

кВт/ч

Сумма р.

26.12.2002

40582

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

Всего

Наличие электроплиты

Цена

1 кВт/ч

коп.

ДА

НЕТ

45

60


 

А также другие работы, которые могут Вас заинтересовать

30258. Цели и задачи теории литературы 45 KB
  Цели и задачи теории литературы Литературоведение наука о литературе. Она охватывает различные области изучения литературы и на современном этапе научного развития делится на такие самостоятельные научные дисциплины: теория литературы изучает социальную природу специфику закономерности развития и общественную роль художественной литературы и устанавливает принципы рассмотрения и оценки литературного материала история литературы исследует процесс литературного развития и определяет место и значение в этом процессе различных литературных...
30259. Взаимосвязь теории литературы с другими науками 34.5 KB
  Взаимосвязь теории литературы с другими науками Современное Л.: теория литературы история литературы и литературная критика. Теория литературы исследует общие законы структуры и развития литературы. Предметом истории литературы является прошлое литературы как процесс или как один из моментов этого процесса.
30260. Первый этап формирования литературоведческой науки: от античности до средневековья 125 KB
  Исторический взгляд на поэтику стал возможен после того как сложилось понятие всемирной литературы ввел Гэте. развития человечества которым в свою очередь обусловлено единство развития литературы. Далее у Чернец идет про литературную критику теорию и историю литературы т. региональная и национальная специфика литературы.
30261. Второй этап формирования литературоведческой науки: средневековье, схоластический 28 KB
  Для схоластики характерно использование философского мето да. Од нако такая общая оценка средневековой схоластики была бы оши бочной. Общая оценка схоластики часто испытывала влияние критики со стороны гуманизма и Реформации. Появлению и развитию схоластики в первую очередь способствовали два фактора: обновление церкви которое среди прочего выражалось в реформе монашества движение Клюни а также усилившаяся к тому времени взаимосвязь между философским образованием и богословием.
30262. Филология как наука. Вспомогательные литературоведческие дисциплины 25 KB
  Аристотель был первым кто попытался их систематизировать в своей книге первый дал теорию жанров и теорию родов литературы эпос драма лирика. Современное литературоведение состоит из: теории литературы истории литературы литературной критики. Теория литературы изучает общие закономерности литературного процесса литературу как форму общественного сознания литературные произведения как единое целое специфику взаимоотношений автора произведения и читателя. Теория литературы взаимодействует с другими литературоведческими дисциплинами а...
30263. Понятие об уровнях текста 23.5 KB
  Первый верхний уровень идейнообразный. Второй уровень средний стилистический. Третий уровень нижний фонический звуковой. Нижний звуковой уровень мы воспринимаем слухом: чтобы уловить в стихотворении хореический ритм или аллитерацию на р нет даже надобности знать язык на котором оно написано это и так слышно.
30264. Языковой уровень текста. Лингвистический анализ художественного текста 26 KB
  Языковой уровень текста. Лингвистический анализ художественного текста Лингвистический анализ текста как искусство постижения многогранности слова и проникновения в духовный мир произведения Изучение литературы нельзя считать процессом направленным только на получение специфических знаний воспитание души и расширение читательского кругозора это прежде всего проникновение в глубины и восхождение к высотам Языка “одного из самых великих творений человечестваâ€. Лингвистический анализ художественного текста это фундамент его...
30265. Образный уровень текста. Поэтическая лексика, её художественные функции 23 KB
  Поэтическая лексика её художественные функции Слова образность образный используются в стилистике в разных значениях. Образность в широком смысле этого слова как живость наглядность красочность изображения неотъемлемый признак всякого вида искусства форма осознания действительности с позиций какогото эстетического идеала образность речи частное ее проявление. Стилистика рассматривает образность речи как особую стилевую черту которая получает наиболее полное выражение в языке художественной литературы.
30266. Характерологический уровень текста. Способы психологической характеристики персонажа: портретная, речевая характеристика 23.5 KB
  Способы психологической характеристики персонажа: портретная речевая характеристика ПОРТРЕТ в литературе описание внешнего облика персонажа лица фигуры мимики одежды один из способов его характеристики. Место портрета в произведении равно как и способы его создания менялось. В фольклоре в античной и средневековой литературе где индивидуальное начало было выражено довольно слабо портретные характеристики либо вовсе отсутствовали либо сводились к предельно обобщённым описаниям и устойчивым эпитетам которые прямо соответствовали...