Будь умным!

У вас вопросы?
У нас ответы:) SamZan.net

Практикум ОГЛАВЛЕНИЕ

Работа добавлена на сайт samzan.net:

Поможем написать учебную работу

Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.

Предоплата всего

от 25%

Подписываем

договор

Выберите тип работы:

Скидка 25% при заказе до 25.11.2024

 

Табличный  процессор MS Excel

Практикум

 


ОГЛАВЛЕНИЕ

 

[1] ОГЛАВЛЕНИЕ

[2]  

[2.1]
Знакомство с MS Excel

[3] Раздел I. Базовые  технологии

[3.1] Обработка данных

[3.2] Применение итоговых функций

[3.3] Абсолютная адресация

[3.4] Подготовка и форматирование прайс-листа

[3.5] Форматирование ведомости

[3.6] Определение имен, создание имен и применение имен

[3.7] Решение уравнений средствами программы Excel

[3.8] Проверочная работа по MS Excel

[4]
Раздел II.  Вставка объектов

[4.1] Применение средств автоматизации ввода

[4.2] Построение диаграммы

[4.3] Построение графиков

[4.4] Построение графиков с условиями

[4.4.1] Рассмотрим пример построения графика функции

[5] Раздел III. Базы данных и сводные таблицы

[5.1] Построение операции с базой данных

[5.2] Построение сводной таблицы

[5.3] Построение сводной диаграммы

[5.4] Настройка режима проверки вводимых данных

[5.5] Еще раз о сводных таблицах

[5.6] Работа с данными списка, обработка списков, поиск записей, сортировка списков

[6] Раздел IV. Вычисление итогов

[6.1] Вычисление итогов

[6.2] Консолидация данных

[6.3] Совместное использование Excel  и Word

[7] Проверочная работа по Microsoft Excel

[7.1] Итоговая работа по  1 семестру для 1  курса

[8] Раздел V. Условные функции

[8.1] Условная функция и логические выражения

[8.2] Использование функций ЕСЛИ, СЧЕТЕСЛИ

[9] Раздел VI. Решение экономических задач

[9.1] Экономические расчеты

[9.2] Решение задач оптимизации

[9.3] Решение задачи оптимизации расходов предприятия

[10]
Задачи на сообразительность

[11] Тесты по теме: «Электронные таблицы Excel»


Знакомство с MS Excel

Microsoft Excel относится к электронным таблицам или более современное название табличные процессоры. Электронная таблица - это программа, состоящая из набора строк и столбцов, изображенных на экране в специальном окне (рис 1).

Для запуска программы Microsoft Excel можно использовать несколько способов:

  •  Пуск/Программы/ Microsoft Excel;
  •  Двойной щелчок левой кнопкой мыши по ярлыку программы Microsoft Excel;

Рис. 1

Область, находящаяся на пересечении строки и столбца, называется ячейкой.

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

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

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

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

Ввод чисел

  •  Вводимые в ячейку числа интерпретируются как константы.
  •  В Microsoft Excel число может состоять только из следующих символов: 1  2  3  4  5  6  7  8  9  +  -  (  )  ,  /  $  %  .  E  e
  •  Стоящие перед числом знаки плюс (+) игнорируются, а запятая интерпретируется как разделитель десятичных разрядов. Все другие сочетания клавиш, состоящие из цифр и нецифровых символов, рассматриваются как текст.
  •  Символы, которые рассматриваются как числа, зависят от установленных параметров в компоненте Язык и стандарты Панели управления.
  •  Перед рациональной дробью следует вводить 0 (нуль), чтобы избежать ее интерпретации как формата даты; например, вводите 0 1/2
  •  Перед отрицательным числом необходимо вводить знак минус (-) или заключать его в круглые скобки ( ).
  •  Введенные числа выравниваются в ячейке по правому краю. Чтобы изменить выравнивание, выделите ячейки, выберите команду Ячейки в меню Формат, а затем на вкладке Выравнивание установите необходимые параметры.
  •  Формат числа, назначаемый ячейке, определяет способ просмотра числа на листе. Если число вводится в ячейку, имеющую общий числовой формат, то может быть использован другой формат. Например, если вводится 14,73р., то будет использован денежный формат. Чтобы изменить числовой формат, выделите ячейки, которые содержат числа, выберите команду Ячейки в меню Формат, а затем на вкладке Число выберите необходимый формат.
  •  В ячейках, имеющих общий числовой формат, отображаются целые числа (265), десятичные дроби (2,56) или числа, представленные в экспоненциальной форме (1,29E+08), если число длиннее ширины ячейки. Используя общий формат можно просматривать до 11 разрядов, включая десятичные запятые и такие символы, как "E" и "+." Чтобы использовать числа с большим количеством разрядов, используйте встроенный экспоненциальный числовой (экспоненциальное представление числа) или пользовательский формат.
  •  Независимо от количества отображаемых разрядов числа хранятся с точностью до 15 разрядов. Если число имеет больше 15  знаков, то разряды после 15-го преобразуются в нули (0).

         Ввод текста

  •   В Microsoft Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведенные ниже записи интерпретируются как текст: 10AA109, 127AXY, 12-976, 208 4675.
  •  Введенный текст выравнивается в ячейке по левому краю. Чтобы изменить выравнивание, выберите команду Ячейки (вкладка Выравнивание) в меню Формат, а затем измените необходимые параметры.
  •  Чтобы просмотреть весь текст, занимающий в ячейке несколько строк, установите флажок Переносить по словам на вкладке Выравнивание.
  •  Чтобы начать в ячейке новую строку, нажмите клавиши ALT+ENTER.

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

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

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

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

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

Раздел I. Базовые  технологии

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

Обработка данных

Цель работы: Научиться вводить текстовые и числовые данные в электронные таблицы, знать как производится ввод и вычисление формул и в каких случаях следует использовать относительные и абсолютные ссылки.

  1.  Запустите программу Excel (ПускПрограммыMicrosoft Excel).
  2.  Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов).
  3.  Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя  Данные.
  4.  Дайте команду ФайлСохранить как и сохраните рабочую книгу под именем Книга1_Номер группы.
  5.  Сделайте текущей ячейку A1и введите в нее заголовок Результаты измерений.
  6.  Введите произвольные числа в последовательные ячейки столбца А, начиная с ячейки A2 до А15.
  7.  Введите в ячейку B1 строку Удвоенное значение. 
  8.  Введите в ячейку С1 строку Квадрат значения.
  9.  Введите в ячейку D1 строку Квадрат следующего числа.
  10.  Введите в ячейку В2 формулу =2*А2.
  11.  Введите в ячейку С2 формулу =А2*А2.
  12.  Введите в ячейку D2 формулу =В2+С2+1.
  13.  Выделите протягиванием ячейки В2, С2 и D2.
  14.  Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах В, С и D, сколько имеется чисел в столбце А.
  15.  Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значением ячейки в столбце А текущей строки.
  16.  Измените одно из значений в столбце А и убедитесь, что соответствующие значения в столбцах В, С и D в этой строке были автоматически пересчитаны.
  17.  Введите в ячейку Е1 строку Масштабный множитель.
  18.  Введите в ячейку Е2 число 5.
  19.  Введите в ячейку F1 строку Масштабирование.
  20.  Введите в ячейку F2 формулу =А2*Е2.
  21.  Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.
  22.  Убедитесь, что результат масштабирования оказался неверным. Это связано  с тем, что адрес Е2 в формуле задан относительной ссылкой.
  23.  Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$E$2, и нажмите клавишу ENTER.
    1.    Повторите заполнение столбца F формулой из ячейки F2.
    2.    Убедитесь, что благодаря использованию абсолютной адресации значения   ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу.

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

Применение итоговых функций

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

  1.  Запустите программу Excel и откройте рабочую книгу, созданную ранее.
  2.  Выберите рабочий лист Данные.
  3.  Сделайте текущей первую свободную ячейку в столбце А.
  4.  Щелкните на кнопке Автосумма на стандартной панели инструментов.
  5.  Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу Enter.
  6.  Сделайте текущей следующую свободную ячейку в столбце А.
  7.  Щелкните на кнопке Вставка функции на стандартной панели инструментов.
  8.  В списке Категория выберите пункт Статистические.
  9.  В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке Ok.
  10.  Переместите методом перетаскивания палитру формул, если она заслоняет нужные ячейки. Обратите внимание , что автоматически выбранный диапазон включает все ячейки с числовым содержимым, включая и ту, которая содержит сумму. Выделите правильный диапазон  методом протягивания и нажмите клавишу Enter.
  11.  Используя порядок действий, описанный в пп.6-10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ).
  12.   Сохраните рабочую книгу.

Задание 1.

Для ячеек столбцов Доход и Расход установите денежный формат (Формат-Ячейки-Число (денежный)).

A

B

C

D

ООО «Аленький цветочек»

Отчет по прибыли за 2003 год

Главный бухгалтер ФИО

Месяц

Доход

Расход

Прибыль

Январь

12 000р.

7 800р.

Февраль

25 000р.

13 500р.

Март

51 000р.

37 000р.

Апрель

17 000р.

7 000р.

Май

23 000р.

25 000р.

Июнь

42 000р.

40 000р.

Июль

20 000р.

18 000р.

Август

48 000р.

20 000р.

Сентябрь

35 000р.

8 000р.

Октябрь

96 000р.

7 500р.

Ноябрь

37 000р.

12 200р.

Декабрь

21 000р.

15 000р.

Итого

Среднее значение

Максимальное значение

Минимальное значение

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

Стандартные функции

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

       К математическим функциям относятся такие известные из курса школьной математики функции, как SIN() – синус, COS() – косинус, TAN() – тангенс, LN() – натуральный логарифм, КОРЕНЬ() (SQRT) – квадратный корень числа и т.д. В круглых скобках (сразу за именем функции) записывается нее аргумент. При использовании тригонометрических функций следует учитывать, что аргумент должен быть задан в радианной мере. В качестве аргумента функции может выступать числовая константа, адрес клетки табличного процессора или диапазон (блок) клеток.

Наибольший интерес представляют функции, аргументом которых является не одна ячейка, а диапазон ячеек. Наиболее часто используемой в табличных вычислениях математической функцией является функция суммирования аргументов СУММА().

      Одной из целей разработки табличных процессоров была автоматизация статистической обработки данных. Этим объясняется довольно многочисленная группа статистических функций. Наиболее часто используемыми статистическими функциями являются:  СРЗНАЧ()(AVERAGE) -  вычисление среднего арифметического аргументов, МИН()(MIN) и МАКС()(MAX) – вычисление минимального и максимального значений среди аргументов.

Задание 2.

На отрезке [0;1] вычислить значения функции  с шагом 0,2.

Задание 3.

В таблицу собраны данные о крупнейших озерах мира.

Название озера

Площадь

(тыс.кв.м.)

Глубина (м)

Высота над уровнем моря (м)

Байкал

31,5

1520

456

Таньганьика

34

1470

773

Виктория

68

80

1134

Гурон

59,6

288

177

Аральское море

51,1

61

53

Мичиган

58

281

177

Минимальная

глубина

Максимальная

площадь

Средняя высота

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

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

Абсолютная адресация

Имеется список персонала и окладов. Каждому нужно начислить премию в размере 20% оклада, имея в виду, что процент премии может измениться, и тогда потребуется перерасчет.

Решение. Дайте имя листу «Премия». Сначала введем в диапазон А1:В5 приведенную таблицу.

Фамилия И.О.

Оклад

Гуськов В.В.

3200

Хан И.Г.

4500

Прутков К.К.

1800

Мицубиси И.И.

2700

Вставка строк и столбцов. В таблице нужно разместить процент премии. Вставим перед списком персонала две пустые строки. Для этого выделим мышью на вертикальной адресной полосе номера строк 1 и 2. Не уводя курсор с адресной полосы, нажимаем правую кнопку мыши, появляется контекстное меню. Щелкаем по пункту «Добавить ячейки». Теперь список располагается в диапазоне А3:В7.

       Разместим в списке персонала слева от фамилий порядковые номера. Вставим для них новый столбец, для этого выделяем на горизонтальной адресной полосе столбец А, нажимаем правую кнопку мыши и в контекстном меню выбираем тот же пункт «Добавить ячейки».  В ячейку А4 вводим число 1, производим автозаполнение диапазона А5:А7 порядковыми номерами.

Разместим в первой строке процент премии. В ячейке В1 так и напишем: «%премии». В ячейку С1 введем число 20%. В ячейку D3 введем заголовок  Премия.

Ввод и копирование формулы.   В ячейку D4 ввести формулу, скопировать ее на все остальные ячейки и рассчитать процент премии всем сотрудникам.

Замечание: В формуле надо использовать абсолютную адресацию.

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

Очистите D4:D7. Выделите ячейку С1. Выберите в меню пункт «Вставка/Имя/Присвоить». В диалоговом окне внизу видим абсолютный адрес выделенной ячейки (на это указывают знаки доллара), вверху вводим имя p. Закроем окно. В D4 введите формулу, используя имя ячейки и скопируйте ее вниз. Чтобы в строку формул вставить имя ячейки выберите в меню пункт «Вставка/Имя/Вставить».

Задание 1.

  1.  Создайте таблицу следующего вида.

А

B

C

D

E

F

G

1.

ООО «Аленький цветочек»

2.

Прайс-лист на

12.10.04

3.

Курс $=

29,5

4.

№

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

товара

Цена($)

Цена (руб.)

Ед.изм

Кол-во

Стоимость (руб.)

5.

1

Палочка волш.

300

шт.

3

6.

2

Зелье-приворотное

100

литр

10

7.

3

Вода живая

50

литр

0,5

8.

4

Вода мертвая

400

литр

0,7

9.

5

Ковер-самолет

1200

шт.

1

10.

6

Скатерть-самобранка

700

шт.

8

11.

7

Сапоги-схороходы

200

пар

12

12.

8

Лягушка-царевна

5000

шт.

6

ИТОГО

  1.  Напишите, используя абсолютную адресацию, в ячейке D5 формулу для вычисления цены товара в рублях.
  2.  Напишите, используя относительную адресацию, в ячейке G5 формулу для стоимости.
  3.  Используя автосуммирование, вычислите ИТОГО.

Задание 2.

  1.  Немецкий физик Г.Фаренгейт в 1724 году предложил температурную шкалу, названную его именем. Температура по шкале Фаренгейта связана с температурой по шкале Цельсия соотношением: . Составить таблицу, переводящую температуру воздуха, измеренную по шкале Цельсия, в температуру по шкале Фаренгейта.
  2.  Немецкий физик Г.Фаренгейт в 1724 году предложил температурную шкалу, названную его именем. Температура по шкале Фаренгейта связана с температурой по шкале Цельсия соотношением: . Составить таблицу, переводящую температуру воздуха, измеренную по шкале Фаренгейта, в температуру по шкале Цельсия.
  3.  Постройте таблицу умножения целого числа N на множители от 1 до 10. Сделайте так, чтобы таблицу можно было перестроить на новое значение N путем изменения содержимого всего одной ячейки.

Задание 3.

Оформление таблиц

В диалоговом окне «Форматирование ячеек» есть вкладки «Выравнивание», «Шрифт», «Граница», «Вид», с помощью которых можно профессионально оформить заголовки таблиц. Используя эти средства, можно также подготовить бланки документов.

Задание. Для книжного магазина составляется ежедневная таблица продаж книг.

Оформите такую таблицу на рабочем листе.


 

Продано книг

Д

А

Т

А

В

С

Е

Г

О

в том числе

Н

А

У

Ч

Н

А

Я

Т  

Е   

Х

Н

И

Ч   К

Е   А

С   Я

Х

У

Д   В

О   Е

Ж  Н

Е   Н

С   А

Т   Я

15 март

531

156

200

175

16 март

145

40

5

100

17 март

475

124

140

211

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

Подготовка и форматирование прайс-листа

Цель работы: Научиться форматировать документ Excel. Знать, как изменять ширину столбцов, объединять ячейки.

  1.  Запустите программу Excel и откройте рабочую книгу Книга1_Номер группы.
  2.  Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый  ( Вставка Лист) . Дважды щелкните на ярлычке нового листа и переименуйте его как   Прейскурант.
  3.  В ячейку А1 введите текст Прейскурант .
  4.  В ячейку А2 введите текст Курс пересчета.. В ячейку В2 введите текст 1у.е.=  В ячейку С2 введите  текущий курс пересчета (то есть текущий курс доллара).
  5.  В ячейку А3 введите текст Наименование товара. В ячейку В3 введите текст Цена(у.е.). В ячейку С3 введите текст Цена (руб.) .
  6.  В последующие ячейки столбца А введите названия товаров, включенных в прейскурант.
  7.  В соответствующие ячейки столбца В введите цены товаров в условных единицах.
  8.  В ячейку С4 введите формулу =В4*$С$2, которая используется для пересчета цены из условных единиц в рубли.
  9.  Методом автозаполнения скопируйте формулы во все ячейки столбца С, которым соответствуют заполненные ячейки столбцов А и В. Почему при таком копировании получатся верные формулы ?
  10.  Измените курс пересчета в ячейке С2. Обратите внимание , что все цены в рублях при этом обновляются автоматически.
  11.  Выделите методом протягивания диапазон А1:С1 и дайте команду Формат Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру и установите флажок Объединение ячеек.
  12.  На вкладке Шрифт задайте размер шрифта в 14 пунктов и в списке Начертание выберите вариант Полужирный. Щелкните на кнопке Ok.
  13.  Щелкните правой кнопкой мыши на ячейке В2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По правому краю и щелкните на кнопке Ok.
  14.  Щелкните правой кнопкой мыши на ячейке С2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому краю и щелкните на кнопке Ok.
  15.  Выделите методом протягивания диапазон В2:С2. Щелкните на раскрывающей кнопке рядом с кнопкой Границы на панели инструментов Форматирование и задайте для этих ячеек широкую внешнюю рамку( кнопка в правом нижнем углу открывшейся палитры).
  16.  Дважды щелкните на границе между заголовками столбцов А и В, В и С, С и D. Обратите внимание , как при этом изменяется ширина столбцов А, В и С.
  17.  Посмотрите, устраивает ли вас полученный формат таблицы. Щелкните на кнопке Предварительный просмотр на стандартной панели инструментов, чтобы увидеть, как документ будет выглядеть при печати.
  18.   Сохраните рабочую книгу.

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

Форматирование ведомости

Цель работы: Научиться условно форматировать документ Excel. При условном форматировании оформление ячеек зависит от их содержания. Знать, как  распространять условное форматирование одной ячейки на целый диапазон.

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

  1.  Запустите программу Excel и  откройте рабочую книгу, созданную ранее.
  2.  Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый ( ВставкаЛист) . Дважды щелкните на ярлычке листа и переименуйте его как   Ведомость.
  3.  В первую строку рабочего листа , начиная с ячейки В1, введите названия экзаменов.
  4.  В первый столбец рабочего листа , начиная с ячейки А2, введите фамилии студентов.
  5.  Заполните таблицу экзаменационными оценками по своему усмотрению. Оценки должны изменяться от 2 до 5 баллов.
  6.  Выделите ячейку А2 и дайте команду Формат Условное форматирование. 
  7.  В раскрывающемся списке на панели Условие 1 выберите вариант формула. 
  8.  В поле для  формулы введите следующую формулу: =МИН($В2:$Е2)>3. Обратите внимание на способ использования абсолютных ссылок в формуле , так как ее планируется распространить на всю  таблицу. Эта формула рассчитана на четыре экзамена, при другом их числе выбранный диапазон несколько изменится.
  9.  Щелкните на кнопке Формат. В открывшемся диалоговом окне Формат ячеек выберите вкладке Вид и щелкните на светло-зеленом цвете для использования в качестве фона ячеек. Щелкните на кнопке ОК.
  10.  Щелкните на кнопке А также, чтобы задать второе условие форматирования. Далее действуйте аналогично тому, как указано в п. 8. Нужная формула должна иметь вид: =МИН($В2:$Е2)<3.
  11.  Подобно тому, как указано в п. 9, задайте светло-розовый фон для ячеек.
  12.  Щелкните на кнопке ОК. Фон ячейки А2 должен измениться, если соответствующий студент не имеет троек или, напротив, имеет задолженность.
  13.  Выделите весь диапазон ячеек ведомости и дайте команду ФорматУсловное форматирование. Диалоговое окно Условное форматирование должно содержать настройки, подготовленные для ячейки А2.
  14.  Щелкните на кнопке ОК. Условное форматирование распространится на всю выделенную область с автоматической коррекцией относительных ссылок. Убедитесь, что формат ведомости соответствует тому, что требовалось.
  15.  Сохраните рабочую книгу.

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

Определение имен, создание имен и применение имен

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

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

   Создайте таблицу и заполните ее данными.

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

              Цена       

Разница в цене

Изменение цены в %

 Апрель

Октябрь

1.

…

2.

…

..

15

Всего

I. Присвоение имен диапазонам.

   Сначала присвоим имя диапазону С3:С14.

      1). Выделите диапазон С3:С14.

       2). Выполнить команду [Вставка- Имя- Присвоить ]        Цены_ апр

       3). Нажмите [Enter]

       4). Аналогично присвойте имя диапазону с данными о ценах товаров в октябре. (Присвойте имя Цены_ окт )

II. Имя , присвоенное диапазону ячеек , можно использовать в формулах.

    Рассмотрим использование имен , чтобы определить изменение цены в % .

      1). Выделите диапазон ячеек F3:F14

      2).Присвойте данным ячейкам процентный стиль.

      3). Введите формулу = Цены_окт/Цены_ апр и нажмите [Ctrl+Shift+Enter]. Чтобы в строку формул вставить имя ячейки выберите в меню пункт «Вставка/Имя/Вставить».

III. Вычислите в столбце Е разницу в цене.

IV.  В 15-й строке подведите итоги.   

V. Сохранить книгу   

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

Решение уравнений средствами программы Excel

Цель работы: Научиться численно решать с помощью программы Excel уравнения, содержащие одно неизвестное и задаваемые формулой. Уяснить для себя, что при наличии нескольких корней результат решения уравнения зависит от того, какое число было выбрано в качестве начального приближения.

  Задача: Найти решение уравнений  х3-3х2+х= -1

  1.  Запустите программу Excel и откройте рабочую книгу, созданную ранее.
  2.  Создайте новый рабочий лист (Вставка Лист), дважды щелкните на его ярлычке и присвойте ему имя Уравнение.
  3.  Занесите в ячейку А1 значение 0.
  4.  Занесите в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула может, например, иметь вид =А1^3-3*A1^2+A1.
  5.  Дайте команду Сервис Подбор параметра.
  6.  В поле Установить в ячейке укажите В1, в поле Значение задайте –1, в поле Изменяя значение ячейки укажите А1.
  7.  Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.
  8.  Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия?
  9.  Сохраните рабочую книгу.

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

Проверочная работа по MS Excel

Задание 1.

  1.  Создать таблицу
  2.  Рассчитать средний балл.
  3.  Подсчитать общий балл студента, минимальный рост и максимальный возраст.

Оценки

Фамилия студента

Физика

Матема

тика

Химия

Средний балл

Возраст

Рост

Гуськов В.

5

4

5

 

19

152

Солдаткина М.

4

5

3

 

20

163

Канарейкин П.

2

3

5

 

18

180

Ягудин Р.

3

2

5

 

17

175

Моторский Я.

4

4

4

 

21

189

 

 

 

 

 

 

 

Общий балл по предметам

 

 

 

 

 

 

Минимальный рост

 

 

 

 

 

 

Максимальный возраст

 

 

 

 

 

 

Задание 2.

  1.  Создать таблицу

 

Магазин "Фрукты-овощи"

Товары

Объем продажи за январь

Объем продажи за февраль

Объем продажи за март

Цена за 1 кг

Средний объем продаж за 3 месяца

Общая стоимость

Яблоки

25

35

15

25

 

 

Апельсины

4

12

8

40

 

 

Груши

12

15

18

35

 

 

Виноград

5

9

10

30

 

 

 

 

 

 

 

 

 

Итого

 

 

 

 

 

 

Максимальное значение

 

 

 

 

 

 

Минимальное значение

 

 

 

 

 

 
  1.  Рассчитать Средний объем продаж за январь, февраль и март.
  2.  Рассчитать Общую стоимость (цена за 1 кг *средний объем).
  3.  Подсчитать Итого, максимальные и минимальные значения.

Задание 3.

  1.  Создать таблицу

Кинотеатр "Конус"

 

 

 

 

 

 

Цена билета

50р.

Сеанс фильма

Название
фильма

Кол-во
проданных
билетов

Выручка
от сеанса

15.30  

Волкодав

50

 

20.30 (зал 1)

Титаник

58

 

18.30

Парфюмер

68

 

20.30 (зал 2)

Кочевник

75

 

22.30

Дом летающих кинжалов

 56

 

 

 

 

 

Итого

 

 

 

  1.  Подсчитать выручку от каждого сеанса.
  2.  Подсчитать итоговую сумму за день.


Раздел II.  Вставка объектов

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

Применение средств автоматизации ввода

Цель работы: Научиться производить заполнение последовательностью чисел (дат), прогрессией, формулами. Знать, как при заполнении формулами учитывается относительная адресация.

  1.  Запустите программу Excel .
  2.  Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый ( ВставкаЛист). Дважды щелкните на ярлычке нового листа и переименуйте его как Допол_расходы по месяцам.
  3.  Сделайте текущей ячейку А1 и введите в неё текст: Месяцы.
  4.  Сделайте текущей ячейку В1 и введите в неё текст: Расходы.
  5.  Сделайте текущей ячейку А2. Введите в нее текст Январь 2001. Нажмите клавишу ENTER. Убедитесь, что текст был автоматически распознан как дата (изменился формат, а данные выровнялись по правому краю ячейки). Нажмите клавишу ВВЕРХ. При желании, дайте команду Формат Ячейки и укажите иной формат записи даты.
  6.  Установите указатель мыши на маркер заполнения в правом нижнем углу рамки текущей ячейки. Нажмите правую кнопку и протяните рамку так, чтобы она охватила все ячейки от А2 до А25.
  7.  Отпустите кнопку мыши. В открывшемся меню выберите Заполнить по месяцам. Убедитесь, что в столбце А появились обозначения для всех месяцев по декабрь 2002 года включительно.
  8.  В данном примере будем считать, что в первый месяц расходы составляли 10 рублей, а в каждом последующем возрастали на 10%.Сделайте текущей ячейку В2. Введите в нее число 10. Нажмите клавишу ENTER.
  9.   Щелкните на ячейке В2 правой кнопкой мыши и выберите в контекстном меню Формат ячеек. На вкладке Число выберите вариант Денежный и щелкните на кнопке ОК. Убедитесь, что число теперь записано как денежная сумма.
  10.  Щелкните правой кнопкой мыши на маркере заполнения и протяните  рамку так, чтобы она охватила ячейки с В2 по В25.Отпустите кнопку мыши. В открывшемся меню выберите пункт Прогрессия.
  11.  На панели Тип установите переключатель Геометрическая, в поле Шаг задайте значение 1,1. Щелкните на ОК.
  12.  В ячейку С1 введите текст Нарастающий итог.
  13.  Сделайте текущей ячейку С2. Введите в нее текст =В2. Это формула, которая означает, что значение ячейки С2 равно значению ячейки В2. Эта простая формула гарантирует, что если значение в ячейке В2 будет изменено, то ячейка С2 все равно будет содержать верные данные.
  14.  Щелкните на ячейке С3. Введите формулу =В3+С2. Нажмите клавишу ENTER.
  15.  Снова сделайте ячейку С3 текущей. Наведите указатель мыши на маркер заполнения, нажмите левую кнопку и протяните рамку так, чтобы она охватывала ячейки с С3 по С25.
  16.  Щелкните на одной из ячеек столбца С и посмотрите, какая формула в ней записана. Убедитесь, что все формулы были скорректированы по принципу относительной адресации.
  17.  Сохраните рабочую книгу.

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

Построение диаграммы

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

  1.  Запустите программу Excel  и откройте ранее созданную рабочую книгу.
  2.  Откройте рабочий лист Допол_расходы по месяцам.
  3.  Методом протягивания выделите диапазон ячеек А2:С25.
  4.  Щелкните на значке Мастер диаграмм на стандартной панели инструментов.
  5.  В списке Тип выберите пункт Гистограмма (для отображения данных в виде столбчатой диаграммы). В палитре Вид выберите нижний пункт в первом столбце (трехмерная гистограмма). Щелкните на кнопке Далее.
  6.  Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно.
  7.  На вкладке Ряд выберите пункт Ряд1, щелкните в поле имя, а затем на ячейке В1. Аналогично, выберите  пункт Ряд2 и щелкните сначала в поле Имя, а затем на ячейке С1. Щелкните на кнопке Далее.
  8.  Выберите вкладку Заголовки. Задайте заголовок диаграммы, введя в поле Название диаграммы текст Диаграмма расходов. Щелкните на кнопке Далее.
  9.  Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего лист. Щелкните на кнопке Готово.
  10.  Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее. Попробуйте навести указатель мыши на любой из элементов диаграммы. Убедитесь, что во всплывающем окне отображается точное значение данного элемента диаграммы.
  11.  Щелкните на одном из элементов ряда Нарастающий итог. Убедитесь, что весь ряд выделен.
  12.  Дайте команду Формат Выделенный ряд. Откройте вкладку Вид.
  13.  Щелкните на кнопке Способы заливки. Установите переключатель Заготовка, в раскрывающемся списке выберите пункт Океан, задайте тип штриховки диагональная 1. Щелкните на кнопке ОК и еще раз на кнопке ОК.
  14.   По своему усмотрению измените оформление ряда данных Расходы и других элементов диаграммы.
  15.   Сохраните рабочую книгу.

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

Построение графиков

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

    Построить график функции  шаг 2.

  1.  Запустите программу Excel и откройте рабочую книгу, созданную ранее.
  2.  В столбец А, начиная с ячейки А1, введите набор значений независимой переменной х согласно заданному шагу (используя маркер заполнения, протянуть правой кнопкой мыши и в открывшемся меню выбрать пункт Прогрессия, где указать шаг).
  3.  В столбец В, начиная с ячейки В1, введите формулу согласно нашему значению функции, то есть =А1^2. Используя маркер заполнения протяните формулу в ячейке В1 до конца столбца.

А

В

1

-20

=А1^2

2

-18

3

-16

4

-14

5

-12

….

….

21

20

  1.  Методом протягивания выделите  ячейки столбца А и столбца В.
  2.  Щелкните на значке Мастер диаграмм на стандартной панели инструментов.
  3.  В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке Далее.
  4.  Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно. На вкладке Ряд в поле Имя укажите Результаты измерений. Щелкните на кнопке Далее.
  5.  Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке Далее.
  6.   Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово.
  7.   Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее и щелкните на построенной кривой, чтобы выделить ряд данных.
  8.   Дайте команду Формат Выделенный ряд. Откройте вкладку Вид.
  9.   На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир.
  10.   На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитрах Цвет и Фон выберите зеленый цвет.
  11.   Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите, как изменился вид графика.
  12.   Сохраните рабочую книгу.

Задание2. Построить графики функций:

1)    шаг 0,1.

2)   шаг 2.

3)   шаг 2.

4)  ,  .

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

Построение графиков с условиями

А) с одним условием

Рассмотрим пример построения графика функции

при .

Для построения графика функции необходимо сначала построить таблицу ее значений при различных значениях аргумента, причем обычно аргумент изменяется с фиксированным шагом. Шаг выбирают небольшим, чтобы таблица значений функции отражала ее поведение на интервале табуляции. В нашем примере шаг изменения аргумента равен 0,1.  Заполнить диапазон ячеек А1:А11 согласно этому шагу.

В ячейку В1 введите формулу:

=ЕСЛИ(А1<0.5;(1+ABS(0.2-A1))/(1+A1+A1^2);A1^(1/3))

Построить график нашей функции.

Б) с двумя условиями

Построить график функции

при .

Построение двух графиков в одной системе координат

при

В диапазон ячеек А2:А17 вводим значения переменной х от –3 до 0 с шагом 0,2. В ячейки В1 и С1 вводим  y и z соответственно. Далее вводим формулы и строим график.

Построение поверхности

Построить поверхность

при  

В диапазон ячеек B1:L1 введем последовательность значений:-1,-0.8,…,1 переменной х, а в диапазон ячеек А2:А12 – последовательность значений :-1, -0.8,…,1 переменной y. В ячейку В2 ввести соответствующую формулу.

Раздел III. Базы данных и сводные таблицы

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

Построение операции с базой данных

Цель работы: Научиться использовать электронную таблицу Excel в качестве базы данных, научиться выполнять операции  сортировки и фильтрации по какому- либо признаку.

  1.  Запустите программу Excel  и откройте новую рабочую книгу.
  2.  Присвойте листу имя Сведения о поставках.
  3.  Предполагается, что предприятие получает пять видов материалов: камень, фанеру, картон, полиэтилен и ткань- от пяти поставщиков, находящихся в Братске, Казани, Курске, Махачкале и Дербенте. Каждый из поставщиков может поставлять любой вид материалов. Поставки производятся не чаще раза в месяц, единица измерения – тонна.
  4.  В ячейки А1-D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.
  5.  Введите несколько десятков записей, имеющих описанную выше структуру. Реальные «объемы поставки» значения не имеют. Образец таблицы приведен ниже.

Месяц

Поставщик

Товар

Объем

январь

Братск

ткань

150

март

Казань

картон

200

февраль

Курск

полиэтилен

236

ноябрь

Махачкала

фанера

456

апрель

Дербент

камень

25

июль

Махачкала

ткань

56

июнь

Курск

картон

89

май

Казань

полиэтилен

12

март

Братск

фанера

256

декабрь

Дербент

камень

122

  1.  Общая сортировка базы данных. Сделайте текущей любую ячейку базы данных и дайте команду Данные Сортировка. Убедитесь, что при этом выделяется вся (кроме заголовков полей) база данных.
  2.  В списке Сортировать по  выберите пункт Месяц и режим по возрастанию.
  3.  В списке Затем по (вторичная сортировка) выберите пункт Поставщик и режим по возрастанию.
  4.  В списке В последнюю очередь, по выберите пункт Товар и режим по возрастанию. Щелкните на кнопке ОК.
  5.   Убедитесь, что база данных отсортирована по указанным категориям.
  6.  Последовательная сортировка базы данных.  С помощью кнопки Отменить на панели инструментов восстановите прежний порядок записей базы данных. Того же порядка сортировки можно добиться другим способом.
  7.  Выберите любую ячейку в столбце Товар и щелкните на кнопке Сортировка по возрастанию на панели инструментов.
  8.  Выберите любую ячейку в столбце Поставщик и щелкните на кнопке Сортировка по возрастанию на панели инструментов.
  9.   Выберите любую ячейку в столбце Месяц и щелкните на кнопке Сортировка по возрастанию на панели инструментов. Убедитесь, что итоговый порядок сортировки тот же, что и в предыдущем случае. Обратите внимание , что в этом случае мы сначала провели  третичную сортировку, затем вторичную и на последнем этапе первичную.
  10.  Фильтрация данных . Чтобы включить режим фильтрации , дайте команду ДанныеФильтрАвтофильтр. Обратите внимание на появление раскрывающих кнопок у заголовков полей  базы данных.
  11.  Чтобы отобрать только записи, описывающие поставки из Братска, щелкните на раскрывающейся кнопке у поля Поставщик и выберите в списке пункт Братск. Обратите внимание на то, что раскрывающая кнопка действующего фильтра и номера отобранных строк отображаются синим цветом. Чтобы отменить текущий фильтр, еще раз щелкните на раскрывающей стрелке и выберите пункт Все.
  12.  Чтобы отобрать  наиболее крупные разовые поставки  , щелкните на раскрывающей стрелке у поля Объем и выберите в списке вариант Первые 10.
  13.  Выберите с помощью счетчика число 20 и далее пункт наибольших и вариант % от количества элементов. Щелкните на кнопке ОК. В результате будет отобрано 20 %  записей, содержащих наибольшие значения объема поставок.
  14.  Чтобы отменить режим фильтрации записей, еще раз дайте команду Данные ФильтрАвтофильтр.
  15.   Сохраните рабочую книгу.

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

Построение сводной таблицы

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

  1.  Запустите программу Excel. Откройте рабочую книгу, созданную ранее с рабочим листом Сведения о поставках.
  2.  Сделайте текущей ячейку в пределах базы данных. Дайте команду Данные Сводная таблица.
  3.  Убедитесь, что установлены переключатели в списке или базе данных Microsoft Excel и сводная таблица. Щелкните на кнопке Далее.
  4.  Убедитесь, что диапазон базы данных выбран правильно. Щелкните на кнопке Далее.
  5.  Убедитесь, что установлен переключатель Новый лист. Щелкните на кнопке Макет.
  6.  Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц – в область Страница, кнопку Поставщик – в область Столбец, кнопку Товар – в область Строка, кнопку Объем – в область Данные.
  7.  Кнопка в области Данные будет иметь вид Сумма по полю Объем. Нас это устраивает. Щелкните на кнопке ОК.
  8.  Щелкните на кнопке Параметры. В поле Имя введите текст Сводная таблица поставок. Щелкните на кнопке ОК. Щелкните на кнопке Готово.
  9.  Переименуйте рабочий лист со сводной таблицей, дважды щелкнув на его корешке. Дайте ему имя Сводная таблица поставок.
  10.  Посмотрите на готовую сводную таблицу. Она показывает, сколько материалов определенного типа пришло от конкретного поставщика, независимо от времени поставки. Дважды щелкните на любой из ячеек сводной таблицы, чтобы увидеть на новом рабочем листе записи, на основе которых сформированы данные в этой ячейке.
  11.  Раскрывающие кнопки рядом с именами полей таблицы позволяют выполнить сортировку по соответствующему полю. Выберите конкретный месяц в раскрывающемся списке Месяц, чтобы увидеть данные, относящиеся к этому месяцу.
  12.   Перетащите кнопку Месяц в область Столбец, а кнопку Товар – в область Страница. Сводная таблица автоматически перестроится в соответствии с новой структурой.
  13.  Дважды щелкните на кнопке Сумма по полю объем. В открывшемся диалоговом окне Вычисление поля сводной таблицы щелкните на кнопке Дополнительно. В раскрывающемся списке Дополнительные вычисления выберите пункт Доля от суммы по строке. Щелкните на кнопке ОК.
  14.  Посмотрите на новый вид сводной таблицы. Среди прочего, мы определите, какую долю в общем потоке поставок имеет каждый из поставщиков.
  15.  Сохраните рабочую книгу.

                            

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

Построение сводной диаграммы

Цель работы: Научиться создавать сводные диаграммы на основе сводных таблиц. При прямом создании сводной диаграммы соответствующая сводная таблица создается в любом случае. При использовании сводной  диаграммы нам доступны как операции, соответствующие сводной таблице, так и те, которые применяют при  построении диаграмм. Иными словами, сводные диаграммы объединяют средства анализа и средства графического представления информации, имеющейся в базе данных.

  1.  Запустите программу Excel. Откройте рабочую книгу, созданную ранее с рабочим листом Сводная таблица поставок.
  2.  Щелкните на кнопке Мастер диаграмм на панели инструментов Сводная таблица. Сводная таблица строится автоматически на новом рабочем листе.
  3.  Переименуйте этот рабочий лист, дав ему имя Сводная диаграмма.
  4.  Поэкспериментируйте со сводной диаграммой, чтобы выяснить, какие ее параметры можно изменять.
  5.  Выполните фильтрацию отображаемых данных.
  6.  Измените величину, отображаемую на диаграмме.
  7.  Переместите поле базы данных в другую область диаграммы.
  8.  Измените тип диаграммы.
  9.  Измените формат отображения элементов диаграммы.
  10.  Сохранить результат работы.

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

Настройка режима проверки вводимых данных

Цель работы:  Научиться использовать средства автоматической проверки вводимых данных.

  1.  Запустите программу Excel. Откройте рабочую книгу, созданную ранее с рабочим листом Сведения о поставках.
  2.  Предполагается, что у нас имеется фиксированный список поставщиков и товаров, так что ручной ввод этих величин необязателен. Также будем считать, что разовая поставка любого материала не может превосходить 10 тонн.
  3.  Для последующих экспериментов внесите произвольные искажения в базу данных: задайте в одной-двух записях неверное имя поставщика, в одной-двух записях используйте неверное наименование материала, в одной двух записях укажите завышенный объем поставки (более 10 тонн).
  4.  За пределами базы данных, например в столбце F, в ячейках F2-F6 укажите имена поставщиков по одному в ячейке.
  5.  Аналогичным образом в ячейках G2-G6 укажите правильные наименования товаров.
  6.  Выделите все ячейки базы данных в столбце В (Поставщик), кроме заголовка столбца. Дайте команду Данные Проверка.
  7.  В раскрывающемся списке Тип данных выберите вариант Список. Переключитесь на поле Источник и введите (или выберите) диапазон F2:F6. Если диапазон выбран, ссылка автоматически берется как абсолютная. После этого щелкните на кнопке Ок.
  8.  Аналогичным образом выберите допустимые значения для поля Товар. В поле Источник необходимо указать диапазон G2:G6.
  9.  Выберите все ячейки в столбце D. Дайте команду Данные Проверка. В поле Тип данных выберите вариант Действительное. В полях Минимум и Максимум укажите, соответственно, значения 0 и 10. Щелкните на кнопке ОК.
  10.  Сделайте текущей любую ячейку в столбце В. Убедитесь, что ввести в нее произвольное значение, отсутствующее в списке, теперь невозможно. Щелкните на раскрывающейся кнопке, чтобы выбрать допустимое значение из списка.
  11.  Аналогичным образом, попробуйте ввести недопустимое значение ( текстовое или выходящее за пределы заданного интервала) в ячейку столбца D. Убедитесь, что это также невозможно.
  12.  Так как данные вводились в базу до включения проверки, они могут содержать ошибки. Дайте команду Сервис Зависимости Панель зависимостей.
  13.  Щелкните на кнопке Обвести неверные данные. Убедитесь, что ячейки с ошибками (созданные в п.4) обнаружены и обведены красным цветом.
  14.  Исправьте ошибки и еще раз щелкните на кнопке Обвести неверные данные. Убедитесь, что пометки исчезли.
  15.  Сохраните рабочую книгу.

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

Еще раз о сводных таблицах

Цель работы: Отработка навыков создания сводных таблиц

Задание 1

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

Создать таблицу:

Автосалон "Счастливое колесо"

Продавец

Марка

Год выпуска

Оборот

Дата

Гуськов

Ауди100

1992

19 000,00  

26.сен.97

Петренко

Пежо605

1992

1 500,00  

26.сен.97

Довгаль

БМВ-520

1990

10 600,00  

12.сен. 97

Петренко

БМВ325

1992

10 999,00  

12.сен.97

Гуськов

Мерседес190

1992

56 000,00  

15.сен.97

Косыгин

ВАЗ2110

1998

15 666,00  

26.окт.99

Гуськов

Опель Фронтера

1992

25 000,00  

12 сен 97

Петренко

РеноСафран

1991

15 500,00  

15.сен.98

Петренко

Мерседес600

1992

6 300,00  

16.сен.98

Косыгин

ВАЗ2110

1998

15 678,00  

30.окт.98

Петренко

ФордМондео

1993

12 999,00  

13.сен.97

Довгаль

VWПассат

1991

12 600,00  

12 мар 97

Создание и обработка Сводных таблиц осуществляется с помощью специального мастера. Для его запуска предназначена команда Данные/Сводная таблица.

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

Во втором окне Мастера сводных таблиц определяется диапазон ячеек, данные из которого будут включены в сводную таблицу. (В данном примере Лист 1!$A$3:$E$13). Нажмите кнопку Далее.

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

Переместите поле Продавец в область страниц. Таким образом, вы зададите размещение данных о каждом продавце на «отдельной странице».

Размещение других полей данных зависит от поставленной задачи. В области Данные разместите показатели оборота. Поля Дата и Марка поместите в области строк, Год выпуска в область столбцов. Нажмите кнопку Далее.

В четвертом диалоговом окне Мастера сводных таблиц определяется местоположение Сводной таблицы ( на новом или текущем листе). Нажмите кнопку Готово.

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

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

Задание 2.

Для предыдущей таблицы создать Сводную таблицу, где критериями при разбиении данных на «отдельные страницы» будут использоваться поля Продавец, Марка и Год выпуска.

Сохранить своё задание в отдельной книге.

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

Работа с данными списка, обработка списков, поиск записей, сортировка списков

Цель работы: Научиться создавать списки и выполнять со списком различные операции.

При создании списка следует придерживаться следующих правил:

Составление списка нужно начинать с создания заголовок столбцов (т.е. название полей должны быть в верхней строке и эту строку называют строкой заголовка).

Шрифт в строке заголовка должен быть другого цвета или иметь характерное начертание (курсив или жирное);

Каждая строка должна представлять собой отдельную запись;

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

Создайте список:

№

Фамилия

Имя

Должность

Дата

Рождения

Адрес

Стаж работы

Гуськов

Алексей

Менеджер

1-авг-75

Озерная 5

10

Хан

Ирина

Инспектор

12-мар-78

Маячная 3

5

Белкина

Ольга

Продавец

01-янв-68

Лесная 6

6

Прутков

Козьма

Инспектор

25-янв-1965

Мясная 3

1

Бобров

Вася

Продавец

01-фев-1975

Молочная3

8

…

…

             Сохраните документ.

1. Активизируйте любую ячейку списка и выполните команду Данные |Форма. (откроется диалоговое окно формы данных). Окно формы данных содержит несколько полей ввода, названия которых соответствует заголовкам столбцов списка.    В окне формы данных можно проводить поиск информации, вводить в список новые записи и удалять ненужные, а также изменять список.

Добавьте в свой список еще 5-7 записей. Для этого:

Выполните команду Данные|Форма.

Выполните щелчок на кнопке Добавить.

Введите новые данные в пустые поля ввода.

После завершения ввода данных нажать [Enter].

2. Произведите поиск записей. Предположим, что в списке сотрудников фирмы нужно просмотреть данные о сотрудниках в возрасте от 30 до 50 лет, у которых стаж работы на фирме составляет не менее 5 лет и фамилии начинаются на букву «Б».

Для этого:

  1.  Выполните команду Данные/Форма, щелкните кнопку Критерий. 
  2.  Перейдите в поле Фамилия. Введите букву б. Результат выбора не зависит от того,  какую букву (прописную или строчную) вы ввели при задании критерия.
  3.  С помощью клавиши Tab перейдите в поле Дата рождения. Введите условие >01.01.51.  Это условие для выбора записей о сотрудников, чей возраст не превышает 50 лет.
  4.  Перейдите в поле Стаж работы. Введите условие >5 для выбора записей о сотрудниках, чей стаж работы превышает 5 лет.

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

Задание. Выберите инспекторов, стаж которых меньше 5 лет.

         3.Сортировка записей в списке.

Текстовые данные сортируют в алфавитном или в обратном алфавитном порядке, а числовые- либо в порядке убывания, либо в порядке возрастания.

Выполните команду Данные/Сортировка и установите следующие параметры для сортировки:

Фамилия: по возрастанию;

Дата рождения: по убыванию.

4. Сохранить документ.

Раздел IV. Вычисление итогов

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

Вычисление итогов

Цель работы: Отработка навыков вычисления итогов.

Задание 1

Создайте таблицу

Книга 2000 - Результаты аукционов

Город

Дата

Сумма

Покупатели

Продано книг

Донецк

17 июл

823 000,00р.

93

45

Махачкала

20 авг

1 340 000,00р.

185

25

Киев

7 июл

1 180 000,00р.

205

147

Москва

21 июл

1 200 000,00р.

214

45

Киев

7 авг

1 523 000,00р.

145

84

Махачкала

23 июл

1 754 600,00р.

146

95

Киев

10 авг

1 002 000,00р.

153

56

Харьков

30 июл

745 000,00р.

125

102

Москва

17 июл

1 724 000,00р.

125

121

Махачкала

1 авг

82 600,00р.

782

132

Киев

15 июл

932 000,00р.

499

151

Москва

30 авг

256 000,00р.

25

25

Харьков

12 июл

821 000,00р.

385

148

Москва

25 авг

756 000,00р.

125

318

Донецк

21 авг

945 000,00р.

      358   

       125   

  1.  Выбор шрифта. В пункте меню ФорматЯчейки вкладка Шрифт выбрать шрифт, чтобы выделить заглавие таблицы.
  2.  Рамки. Для повышения наглядности вставьте пустую строку между строкой с названием таблицы и строкой с заголовками столбцов. Затем выделите диапазон ячеек с заголовками столбцов (A3- E3) , откройте вкладку Граница диалогового окна Формат ячеек и  выберите стиль рамки.
  3.  Выделите всю таблицу и активизируйте команду Автоформат из меню Формат, вследствие чего откроется одноименное диалоговое окно. Выберите формат в поле Список форматов.
  4.  С помощью функции автоматического вычисления итогов определить итог по трем последним столбцам.
  5.  Отсортируйте данные в таблице по названием городов. Для этого выполните команду ДанныеСортировка.
  6.  Выполните команду Данные Итоги. Откроется диалоговое окно Промежуточные итоги.
    •  В поле списка При каждом изменении в выберите заголовок столбца, для которого необходимо вычислить промежуточные итоги после каждого изменения данных на рабочем листе ( В данном примере Город).
    •  Для вычисления итоговой суммы в поле Операция установить функцию Сумма.
    •  В области Добавить итоги по активизировать опцию Покупатели и Продано книг, Сумма отключить опцию Дата.

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

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

Сохраните рабочую книгу.

Задание 2

Создайте таблицу, содержащую данные о сбыте.

                 

Автосалон "Счастливое колесо"

Продавец

Марка

Год выпуска

Оборот

Дата

Гуськов

Ауди100

1992

19 000,00

26.сен.97

Петренко

Пежо605

1992

1 500,00

26.сен.97

Довгаль

БМВ-520

1990

10 600,00

12 сен 97

Петренко

БМВ325

1992

10 999,00

12.сен.97

Гуськов

Мерседес190

1992

56 000,00

15.сен.97

Косыгин

ВАЗ2110

1998

15 666,00

26.окт.99

Гуськов

Опель Фронтера

1992

25 000,00

12 сен 97

Петренко

РеноСафран

1991

15 500,00

15.сен.98

Петренко

Мерседес600

1992

6 300,00

16.сен.98

Косыгин

ВАЗ2110

1998

15 678,00

30.окт.98

Петренко

ФордМондео

1993

12 999,00

13.сен.97

Довгаль

VWПассат

1991

12 600,00

12 мар97

Задание. С помощью функции автоматического вычисления итогов определить оборот каждого продавца.

В Excel имеется возможность комбинирования нескольких итогов      ( применение вложенных итогов). Например, подсчитаем еще количество автомобилей, проданных каждым продавцом. В диалоговом окне Промежуточные итоги в поле При каждом изменении в установить элемент Продавец, в поле Операция – Количество значений, в области Добавить итоги активизировать опцию Марка. Для этого, чтобы в таблице отображались все итоги, следует выключить опцию  Заменить текущие итоги.

Задание 3.

Создайте таблицу

Фирма

Сумма в

счете

Дата

Сумма оплаты

Долг

Ландыш

100р.

12.01.01

100р.

Василек

200р.

20.01.01

200р.

Ромашка

300р.

23.01.01

150р.

Ландыш

400р.

30.01.01

400р.

Ромашка

150р.

02.02.01

150р.

Василек

200р.

02.03.01

200р.

Ландыш

400р.

18.02.01

250р.

Ромашка

300р.

25.05.01

300р.

Гвоздика

400р.

28.09.01

300р.

Получите промежуточные итоги по оплате счетов каждой фирмой.

Сохраните рабочую книгу.

Задание 4.

Вычисление итогов

Вычисление общего среднего балла для студентов 1 курса по итогам зимней и летней сессии

Сессия

Фамилия

Математика

Информатика

Бух/учет

Средний

балл

зим.

Белкина А.А.

5

5

4

лет.

Белкина А.А.

3

3

5

лет.

Гуськов Г.Г.

4

4

4

зим.

Медведева М.М.

3

3

4

зим.

Ласточкина Л.Л.

5

5

5

зим.

Хан И.Г.

3

3

3

лет.

Хан И.Г.

4

5

3

зим.

Гуськов Г.Г.

4

5

4

лет.

Медведева М.М.

5

4

5

лет.

Ласточкина Л.Л.

5

4

4

  1.  Создать таблицу и ввести данные.
  2.  Произвести сортировку по столбцу фамилия по возрастанию.
  3.  Заполнить ячейки Средний балл при помощи клавиши Вставка функций, выбрав функцию СРЗНАЧ
  4.  Вычислить итоги среднего балла имеющихся студентов за год.

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

Консолидация данных

Цель работы: Научиться подводить итоги на основании данных расположенных в различных областях таблицы.

Консолидация выполняется тогда, когда надо подвести итоги для данных, расположенных в разных областях таблицы. Данные могут находиться на одном рабочем листе, на разных листах или разных книгах.

Создайте таблицы на разных листах:

А

В

А

В

А

В

Автосалон (Москва)

1.

Автосалон (Москва)

1.

Автосалон (Москва)

Марка

Кол-во

2.

Марка

Кол-во

2.

Марка

Кол-во

БМВ

65

3.

БМВ

8

3.

БМВ

7

Форд

47

4.

Форд

5

4.

Форд

2

Мерседес

46

5.

Мерседес

10

5.

Мерседес

9

Пежо

31

6.

Пежо

2

6.

Пежо

3

Рено

22

7.

Рено

6

7.

Рено

1

8.

8.

  1.  Активизируйте ячейку А1 на Листе 4.
  2.  Выполните команду Данные/Консолидация. В появившемся диалоговом окне Консолидация в списке Функция выберите Сумма.
  3.  В поле ссылка введите первый диапазон ячеек с данными, подлежащие консолидации при помощи мыши или вручную (Лист1!$A$2:$B$7). Для добавления диапазона нажмите кнопку Добавить. Аналогично добавить .по очереди адреса консолидируемых диапазонов: Лист2!$A$2:$B$7, Лист3!$A$2:$B$7. Для добавления диапазона к списку диапазонов использовать кнопку Добавить.
  4.   Установите все три флажка в нижней части диалогового окна Консолидация (Подписи верхней строки, Значение левого столбца и Создавать связи с исходными данными) и нажать кнопку ОК.
  5.   Сохраните рабочую книгу.

Задание 1.

                     Подсчет расходов на коммунальные расходы при помощи       консолидации за 3 месяца

Создайте таблицы на разных листах:

Коммунальные расходы за

январь

Кол-во

Коммунальные расходы за

февраль

Кол-во

Коммунальные расходы за март

Кол-во

Вода

5р.

Вода

6р.

Вода

7р.

Газ

55р.

Газ

60р.

Газ

55р.

Гор.вода

12р.

Гор.вода

14р.

Гор.вода

14р.

Эл. Энергия

43р.

Эл. Энергия

50р.

Эл. Энергия

43р.

Кварт.плата

120р.

Кварт.плата

120р.

Кварт.плата

120р.

Канализация

15р

Канализация

17р.

Канализация

17р.

Отопление

29р.

Отопление

29р.

Отопление

29р.

Всего

Всего

Всего

На Листе 4 используя консолидацию, подсчитайте Итоги за 3 месяца  для данных, расположенных на трех разных листах.

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

Совместное использование Excel  и Word

  1.  Выделите таблицу предыдущей лабораторной работы  и выполните команду Правка – Копировать. Сверните окно Excel .
  2.  Загрузите Word  и выполните команду Правка – Специальная вставка. Укажите, что вы связываете Лист Microsoft Excel.

                       

  1.  Таблица вставится в Word. Дважды щелкнув по  таблице запустите Excel и убедитесь в том, что изменяя таблицу в Excel меняется автоматически таблица и в Word.  То есть измените в каком-нибудь столбце цифры в таблице Excel, откройте документ Word и увидите, что там тоже изменились эти же цифры.

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

Проверочная работа по Microsoft Excel

Вариант  1.

Задание 1 . Создать таблицу

Отдел

Фамилия

Зарплата

Экономический

Ханкин Г.Г.

1500

Кадров

Гуськов Г.Г.

2500

Ценных бумаг

Тулеев Т.Т.

1200

Оперчасть

Машков А.Д.

1000

Экономический

Котик П.Л.

800

Кадров

Медведева П.Р.

950

Ценных бумаг

Норкин О.Ж.

1350

Оперчасть

Коркин Е. З.

2000

Кадров

Рябина Н.З.

1500

Экономический

Березкина Л.Х.

2500

Ценных бумаг

Зубов А.В.

1300

Оперчасть

Бобров К.У.

1550

Скопировать таблицу на другой рабочий лист. На первом листе посчитать промежуточные итоги (фонд заработной платы) по каждому отделу. На втором создать сводную таблицу.

Задание 2. Выполнить консолидацию.

На рабочих листах с именами «январь», «февраль», «март» приведены фамилии торговых агентов и количество сделок, которые они совершили в течение месяца. Построить сводку за первый квартал.

Январь

Февраль

Март

Фамилия

Сделки

Объем

Фамилия

Объем

Сделки

Фамилия

Сделки

Объем

Иванов

6

250

Сидоров

200

5

Иванов

12

200

Антонов

12

430

Иванов

220

8

Сидоров

10

300

Медведев

7

180

Антонов

8

150

Медведев

6

220

Задание 3.  Создать таблицу «Наименование товаров на складе»

Категория

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

Общая стоимость

Молочные продукты

творог

1562

Молочные продукты

молоко

8697

Кондитерские изделия

печенье

12562

Мясные продукты

колбаса «Одесская»

178954

Рыбные продукты

консервы

12564

Молочные продукты

сырки

154687

Кондитерские изделия

конфеты «Пташка»

146522

Мясные продукты

Котлеты

14566

Рыбные продукты

крабовые палочки

154477

Посчитать промежуточные итоги по категориям.

Вариант 2.

Задание 1. Создать таблицу.

Сведения о сдаче сессии

Факультет

Курс

Количество

студентов

Из них

«неудовл».

Экономический

1

200

20

Финансы и кредит

2

150

22

Исторический

3

100

15

Финансы и кредит

4

90

10

Исторический

5

80

5

Экономический

2

100

14

Исторический

1

150

23

Экономический

5

90

4

Исторический

2

120

3

Исторический

4

110

6

Финансы и кредит

1

200

30

Экономический

4

85

12

Финансы и кредит

5

93

8

Экономический

3

156

11

Финансы и кредит

3

122

10

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

Задание 2.      Сведения о поступлении товаров на склад

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

Январь

Февраль

Март

Товар

Сколько

завезли

Товар

Сколько

завезли

Товар

Сколько

завезли

Стулья

266

Шкафы

28

Кресла

56

Столы

112

Жалюзи

21

Диваны

14

Шкафы

27

Стулья

156

Столы

42

Жалюзи

15

Задание 3. См. задание 3 варианта 1.

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

Итоговая работа по  1 семестру для 1  курса 

  1.  Создать папку с номером группы в папке Мои документы.
  2.  Создать таблицу в MS Word и заполнить ее.

Товар

Цена в 2003г.

(в у.е.)

Цена в 2004г.

(в у.е.)

Разница в цене

2004-2003

Компьютер-486

528

324

Принтер матричный

232

152

Итого

Сохранить документ в своей папке. Свернуть окно MS Word.

  1.  Создать таблицу в MS Excel.

Продавец

Продукция

Год

Оборот

Тулеев

Доски

2000

1525000р.

Ханкин

Трубы

2000

663000р.

Белов

Металл

2000

345000р.

Построить диаграмму, показывающую оборот каждого продавца.

  1.  Вставить таблицу  Excel  в документ MS Word, созданный ранее.

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

Подписка

Наименование издания

Цена одного экземпляра

Стоимость годовой подписки

Количество экземпляров

Общая стоимость

КомпьюТерра

50

23

КомпьютерПресс

35

56

Сети

55

78

Перенести каждую таблицу на новую страницу. Вставить номера страниц. Создать  колонтитулы. Сохранить документ под новым именем в папке. Закрыть окно MS Word.

  1.  Открыть документ Excel, созданный ранее. Построить графики функций в MS Excel.

  шаг 1

   шаг 2.

       На новом листе  рабочей книги  MS Excel создать таблицу.

Расходные

статьи

Месяцы

Итого

Март

Апрель

Май

Налоги

10240

11027

11500

Арендная плата

2032

1503

1001

Услуги

5525

4325

3456

Итого

Подсчитать итоги по каждому столбцу и строке.

Сохранить документ под новым именем в папке. Закрыть окно MS Excel.

  1.  Создать папку со своим именем в папке Мои документы. Там создать текстовый файл. В MS Word создать таблицу

 

Товары

Объем продаж

Май

Июнь

Июль

Итого

Столы

45666

569988

255699

Стулья

112233

778899

154488

Кресла

445566

990011

466888

Итого

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

 Добавить в таблицу три строки и вычислить значения.

Среднее значение

Максимальное значение

Минимальное значение

Сделать заливку столбца Май – желтой, столбца Июнь –голубой, столбца Июль – зеленой.

  1.  В этом же документе вставить фигуры и сгруппировать их.

  1.  Сохранить файл в папке с вашим именем. Закрыть его. Скопировать его  в папку  с именем группы.


Раздел V. Условные функции

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

Условная функция и логические выражения

Общий вид условной функции следующий:

ЕСЛИ (<условие>, <выражение1>, <выражение2>)

Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ, <выражение1> и <выражение2> могут быть числами, формулами или текстами.

Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определит <выражение1>, в противном случае - <выражение2>.

Логические выражения. Логические выражения строятся с помощью операций отношения (<,>, <=(меньше или равно), >=(больше или равно), =, <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические величины ИСТИНА или ЛОЖЬ.

Существуют особенности записи логических операций в табличных процессорах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.

Задание 1.  

А) Создать таблицу спортсменов, участвовавших в соревнованиях по боксу.

Фамилия
спортсмена

1 раунд

2 раунд

3 раунд

Общее
количество
баллов

Перешел на следующий
этап

Магомедов М.

5

10

4

 

 

Курамагомедов М.

6

15

3

 

 

Таибов М.

8

7

1

 

 

Гусейнов Р.

12

3

2

 

 

Гуськов Г.

8

3

9

 

 

Мышкин М.

5

7

2

 

 

Мицубиси Р.

5

12

5

 

 

Б) Рассчитать общее количество баллов во всех раундах.

В) Определить с помощью функции ЕСЛИ кто перешел на следующий этап соревнования, то есть те, у кого общее количество баллов больше 20. В ячейку F2 вставить функцию ЕСЛИ

                             

Задание 2.

  1.  Создать таблицу, содержащую данные об учениках школы: фамилия, возраст и рост ученика. Условия о зачислении в баскетбольную секцию: рост не менее 160 см, возраст не должен превышать 13 лет.  

 

Фамилия

Возраст

Рост

Зачислен

Гуськов Г.

12

162

 

Мышкина М.

13

159

 

Кошкина К.

15

176

 

Листопад О.

16

180

 

Снегурочкина М.

14

160

 

  1.  Используя функцию ЕСЛИ рассчитать, кто будет принят в баскетбольную секцию.

  =ЕСЛИ (И(В2<=13;C2>=160);”ДА”;”НЕТ”)

Задание 3. Разработаем таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому и иностранному языкам, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике – 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае – нет.

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

А

В

С

D

E

F

1

Проходной  балл=

13

2

Фамилия

Математика

Русский

Иностранный

Сумма

Зачислен

3

Антонов

4

3

5

4

Кузьмина

5

4

5

5

Гуськов

3

5

3

6

Ханкин

4

4

5

7

Птичкин

3

4

5

  1.  В ячейке Е3 вычислить сумму баллов за три экзамена.
  2.  Используя функцию ЕСЛИ рассчитать, кто будет зачислен, то есть те, кто имеет сценку по математике 4 или 5 и имеет общий балл больше проходного.

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

Создайте базу данных из 10-15 записей. В базе данных предусмотрите вычисление возраста и начисление стипендии по итогам сессии.

Стипендия вычисляется по алгоритму:

Если средний балл <=3, то стипендия = средний балл*мин.оклад,

а иначе = ср. балл*мин.оклад+100.

Для этого занесите в таблицу данные:

           

A

B

C

D

E

F

G

H

I

J

K

Мин.оклад

85

Список студентов 1 курса

№

ФИО

Группа

Год рожд.

Возраст

Мате

матика

Инфор

матика

Геог

рафия

Исто

рия

Средний балл

Стипендия

1

Гуськов

1

1986

5

5

3

5

2

Хан

1

1987

4

4

4

4

3

Мунг

2

1986

3

5

5

2

4

Мустанг

2

1987

4

3

4

4

5

Иванов

3

1988

5

2

3

5

6

Сидоров

3

1989

4

3

5

2

7

Петров

4

1989

5

5

4

4

8

…

9

…

10

…

Так как записи содержат расчетные поля – возраст и стипендия, занесите соответствующие формулы:

Для вычисления возраста:  

=Год(сегодня())-D4                           ,где D4 – Год рождения

Для вычисления среднего балла:   Занесите формулу самостоятельно.

Для вычисления стипендии:

=Если(J4>3;J4*$G$1+100;J4*$G$1)

Задание 1.

Составить таблицу

Расчет премии сотрудникам фирмы «Лошарик»

Коэффициент 1  (от 1 до 10 лет)-

2

Коэффициент 2  (от 10 до 15 лет)-

3

№

Фамилия, имя

Оклад

Дата начала работы

Стаж работы

Премия

1.

Гуськов

3500

1986

2.

Птичкин

4520

1999

3.

Канарейкина

1500

1998

4.

Мышкин

6000

1992

5.

Лебедев

2500

2000

1)Занесите формулу для подсчета стажа работы.

2) Рассчитайте премию, используя алгоритм: если стаж работы >10 лет, то премия рассчитывается: оклад*коэффициент2 , а иначе: оклад*коэффициент1.

Обработка данных метеостанции

Количество осадков ( в мм)

1992

1993

1994

январь

37,2

34,5

8

февраль

11,4

51,3

1,2

Март

16,5

20,5

3,8

Апрель

19,5

26,9

11,9

Май

11,7

45,5

66,3

Июнь

129,1

71,5

60

Июль

57,1

152,9

50,6

август

43,8

96,6

145,2

сентябрь

85,7

74,8

79,9

октябрь

86

14,5

74,9

ноябрь

12,5

21

56,6

декабрь

21,2

22,3

9,4

За три

года

Суммарно

Максимум

Минимум

Среднемесячно

Стандартное отклонение от среднего

Количество засушливых месяцев

Осадки в незасушливые месяцы

Обработать данные, используя Мастер функций. Функцию СТАНДОТКЛОН  - для подсчета стандартного отклонения, функцию  СЧЕТЕСЛИ  для подсчета количества засушливых месяцев, функцию СУММЕСЛИ для подсчета осадков, которые выпали в незасушливые месяцы.

Справка.

Функция СЧЕТЕСЛИ (интервал, критерий)  подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Вычислите количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков. В данном примере используется формула =СЧЕТЕСЛИ(В4:В15,”<10”).

Функция СУММЕСЛИ (интервал, критерий, сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. В данном примере вводится формула =СУММЕСЛИ(В4:В15,”>=10”).

Задание. С помощью этой функции можно решить более трудную задачу: каково суммарное количество осадков было в 1993 г. в те месяцы, которые в 1994 г. были засушливыми. Напишите какой формулой дается решение и поместите результат в ячейку В24.

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

Использование функций ЕСЛИ, СЧЕТЕСЛИ

Предположим, вам предлагают стать директором кинотеатра «Аврора», но, сколько денег вы будете зарабатывать, говорить не спешат. Тогда вы решаете провести свое маркетинговое исследование.

                     

Кинотеатр "АВРОРА"

Сеанс

Цена

С

О

Л

курс $=

31,50

Сказ про Федота-стрельца

10,40

30

70

200

Властелин колец

12,30

50

70

200

Всего зрителей

ф4

Властелин колец

15,40

70

100

300

Из них:

13 привидений

18,50

100

120

350

со скидкой

ф1

Властелин колец

20,30

150

150

400

обычных

ф2

LoveSeat

ф3

Рассадка зала

2

1

1

1

2

4

4

1

4

3

1

2

1

2

1

2

1

Выручка:

2

1

2

3

4

2

1

1

1

1

2

4

2

1

1

1

1

сеанс 10.40

ф5

3

2

4

3

1

4

3

2

4

1

1

1

4

3

2

4

1

сеанс 12.30

ф6

4

1

4

3

3

4

2

1

2

4

3

3

4

2

1

2

4

сеанс 15.40

ф7

1

2

1

3

4

2

1

2

3

4

4

4

2

1

2

3

4

сеанс 18.50

ф8

2

1

2

3

4

2

1

1

1

1

2

4

2

1

1

1

1

сеанс 20.30

ф9

3

2

4

3

1

4

3

2

4

1

1

1

4

3

2

4

1

Итого за день

ф10

4

1

4

3

3

4

2

1

2

4

3

3

4

2

1

2

4

Итого за месяц

ф11

1

2

1

3

4

2

1

2

3

4

4

4

2

1

2

3

4

Из них:

%

в руб.

в $

5

5

5

5

5

5

5

5

5

5

5

5

5

5

5

5

5

амортизация

12

ф12

ф18

налоги

22

ф13

ф19

з/п сотрудникам

16

ф14

ф20

Согласны ли, Вы, Гамзатов стать директором кинотеатра?

охранные структуры

25

ф15

ф21

ф24

новые фильмы

22

ф16

ф22

директор

3

ф17

ф23

  1.         Заполните зал ( 10 рядов по 17 мест в каждом) случайными цифрами  от 1 до 4, кроме последнего ряда, который заполните цифрами 5. Цифры 1 и 2 будут обозначать посетителей, на которых распространяются скидки (С). Цифры 3 и 4 – обычных посетителей (О), а цифры 5 – «места для поцелуев» (Л, от англ.Loveseat),
  2.  Формулы ф1 – ф3 при помощи функции СЧЕТЕСЛИ подсчитывают в зале:
  •  количество посетителей 1 и 2, т.е. количество льготников;
  •  количество 3 и 4, т.е. обычных посетителей;
  •  количество 5, т.е. влюбленных.
  1.  Формула 4  суммирует полученные числа, чтобы получить контрольную цифру 170.
  2.  Формулы ф5 – ф9, исходя из известной стоимости билетов и подсчитанного количества зрителей, вычисляют выручку соответствующего сеанса. Предполагаем, что рассадка зала на всех сеансах одинакова.
  3.  Исходя из выручки в рублях за месяц и процентов расходов по статьям, формулы ф12- ф17 вычисляют денежные расходы на соответствующие статьи затрат.
  4.  Формулы ф18 – ф23 показывают, сколько эти суммы будут значить в долларах при известном заранее курсе.
  5.  Формула ф24 определяет, что если директор в результате получает больше 3000 долларов в месяц, то мы согласны быть директором, т.е. в ячейке В24 должно появиться слово «Да» или «Нет». Это и будет итогом работы.

Раздел VI. Решение экономических задач

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

Экономические расчеты

 

Расчет единого налога с совокупного дохода

Создайте таблицу.

Показатели

Данные

1.

Валовая выручка с учетом НДС

4860,00

2.

Затраты, исключаемые при определении совокупного дохода - всего

в том числе:

а) стоимость использованных в процессе производства товаров

55,5

(работ, услуг), сырья, приобретенных товаров, топлива

б) стоимость эксплуатационных расходов

3

в) стоимость текущего ремонта

г) затраты на аренду помещений, используемых для производственной  

и коммерческой деятельности

960

д) затраты на аренду транспортных средств

48

е) расходы на уплату процентов за пользование

кредитными ресурсами банков

849,11

ж) стоимость оказанных организации услуг

50

з) НДС, уплаченный поставщиком

278,4

и) налог на приобретение автотранспортных средств

20

к) отчисления в гос. Социальные внебюджетные фонды

103,95

л) уплаченные таможенные платежи, гос.пошлины и лицензионные.сборы

3.

Совокупный доход

4.

Ставка единого налога - всего

в том числе:

а) зачисляемого в федеральный бюджет

10,00%

б) зачисляемого в бюджет субъектов РФ

в) зачисляемого в местный бюджет

20,00%

5.

Сумма единого налога - всего

в том числе:

а) зачисляемого в федеральный бюджет

б) зачисляемого в бюджет субъектов РФ

в) зачисляемого в местный бюджет

6.

Остаточный доход

7.

Итого налогов, сборов, отчислений

8.

Удельный вес налогов, сборов, отчислений в валовой выручке с НДС

Заполните ячейки формулами, если:

  1.  Совокупный доход рассчитывается  как разность валовой выручки и затрат
  2.  Сумма единого налога – совокупный доход умножается на ставку единого налога
  3.  Рассчитать суммы единого налога, зачисляемые в федеральный и местный бюджеты, учитывая ставки единого налога для каждого вида бюджетов
  4.  Остаточный доход – от совокупного дохода вычесть сумму единого налога
  5.  Пункт 7 рассчитать как Сумма единого налога + Все налоги, отчисления и сборы из пункта 2
  6.  Пункт 8 находим  - Общая сумма налогов  делится на валовую выручку и умножается на 100%.

Определение рентабельности продукции

Себестоимость единицы продукции (тыс.руб)

    [ CЕП]

Объем производства

(тыс.шт.)

 [Vпр]

Выручка от реализации продукции

(млн.руб.)

   [B]

Себестоимость продукции

(тыс.руб.)

       [СП]

Прибыль

(млн.руб.)

    [П]

Рентабельность продукции

      [P]

1

2

3

4

5

6

7

3500

1000

5000

…

…

…

Завести в  графу 1 любую продукцию и рассчитать ее рентабельность по формулам:

СП=СЕП* Vпр

П=В - СП    

Р=П/СП

Определение стоимости находящихся в производстве изделий

(т.е. определение норматива оборотных средств в незавершенном производстве)

Создайте электронную таблицу, столбцы которой имеют такие названия:

  •  расходы на производство продукции по смете на 4 квартал (тыс.руб.)  (1)  - 630
  •  время обработки изделия (дни) (2)  - 4
  •  время передачи изделия от одного рабочего места к другому и на склад (дни)  (3)  - 2
  •  время пребывания изделия между операциями (дни) (4) - 1
  •  страховой запас (дни)  (5) - 2
  •  коэффициент нарастания затрат [K]  (6) - 0,7
  •  длительность производственного цикла (дни) [T]  (7)
  •  однодневные затраты на производство продукции (тыс.руб.) [P] (8)
  •  норматив оборотных средств в незавершенном производстве [H]  (9)

Рассчитать норматив по формулам:

Т=(2,3,4,5)

Р=(1)/Т

Н=Р*Т*К

Показатели эффективности использования основных фондов

Фо , Фв, Фё

Фо – фондоотдача

Фв – фондовооруженность

Фё  – фондоемкость   

Выручка

(тыс.руб.)

Среднесписочная численность рабочих (чел.)

Среднегодовая стоимость основных фондов (тыс.руб)

Фо

Фв

Фё

26000

225

84000

…

…

…

Для расчета использовать формулы:

Фо = Выручка/ср.год.ст-сть ОФ

Фв = ср.год.ст-сть ОФ/ср.спис.числ.рабочих

Фё = 1/Фо

Определение годовой доходности акции

Покупная цена акции (руб.)

Продажная цена акции (руб.)

Затраты по приобретению акции (руб.)

Дивиденды  (руб.)

Период

(дни)

Годовой доход акции (руб.)

2200

3500

100

300

120

где

S – цена продажи                                

В – цена покупки акции

d – дивиденты по акциям

С – затраты по приобретению (вознаграждения брокера, комиссия биржи)

t – период ( в днях), за который получен доход.

Расчет суммы амортизационных отчислений

[Б]

Стоимость введенных основных фондов (руб.) [CBB]

Стоимость выбывших основных фондов (руб.) [CB]

                                          [HA]

ССРВВ

ССРВ

Ч1

Ч2

Сумма амортиз. Отчислений

 

1

2

3

4

5

6

7

8

9

10

11

12

1

2

3

4

5

6

7

8

9

10

11

12

 

 

 

 

 

 

250000

 

 

 

 

 

 

 

15000

 

 

 

 

 

 

 

 

 

5000

 

 

 

 

 

 

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[Б] – балансовая стоимость основных фондов на начало года (руб.)

[HA] – норма амортизации (%)

[ССРВВ] – среднегодовая стоимость вводимых основных фондов (руб.)

[ССРВ] – среднегодовая стоимость выбывших ОФ (руб.)

[Ч1] – число полных месяцев после введения ОФ

[Ч2] – число полных месяцев после выбытия ОФ

А – сумма амортизационных отчислений (руб.)

ССРВВ=СВВ*Ч1/12

ССРВ=СВ*Ч2/12

А = (Б+ССРВВ-ССРВ)*НА/100

Лабораторная работа 28. Показатели производства

Создайте таблицу и заполните ее экспериментальными данными.

 

A

B

C

D

E

F

G

1

Показатели производства

2

 

3

 

 

 

 

 

 

 

4

 

 Месяцы

План выпуска

Фактически выпущено

Процент выполнения плана

Выполнено в % к фактически выпущенному за год

5

1

31.01.99

январь

2340

2000

 

 

6

2

28.02.99

февраль

3200

3200

 

 

7

3

31.03.99

март

2800

3000

 

 

8

4

30.04.99

апрель

3000

3100

 

 

9

5

31.05.99

май

3100

3200

 

 

10

6

30.06.99

июнь

2500

2400

 

 

11

7

31.07.99

июль

2600

2800

 

 

12

8

31.08.99

август

3000

3200

 

 

13

9

30.09.99

сентябрь

3200

3200

 

 

14

10

31.10.99

октябрь

3000

3100

 

 

15

11

30.11.99

ноябрь

2800

3000

 

 

16

12

31.12.99

декабрь

3200

3300

 

 

17

Итого за год

 

 

 

18

 

Максимально за месяц

 

 

 

19

Минимально за месяц

 

 

 

20

 

В среднем за месяц

 

 

 

Этап 1. Расчет и анализ итогов работы предприятия.

  1.  Воспользовавшись кнопкой Автосумма, определите  Итог за год для столбцов План выпуска, Фактически выпущено.
  2.   Процент выполнения плана определяется как Фактически выпущено поделить на План выпуска. Введите  соответствующую формулу.
  3.  Для данных в столбце Процент выполнения плана установите процентный формат, используя кнопку на панели инструментов или Формат–Ячейки–Число (процентный).
  4.  Итог за год для  столбца Процент выполнения плана определяется как Фактически выпущено за год поделить на План выпуска за год. Введите  соответствующую формулу.
  5.   В диапазоне ячеек G5:G16 предстоит вычислить для каждого месяца его долю (в процентах) в годовом выпуске, которая вычисляется как   отношение выпущенного в  каждом месяце к  выпущенному за год. Так как во всех формулах диапазона в качестве делителя выступает одна и та же ячейка (сумма за  год), то  в формуле адрес этой ячейки должен быть задан в виде абсолютной ссылки, чтобы он не изменялся при копировании формулы.  То есть в ячейку G5 вставить формулу E5/$E$17.
  6.  Выделите ячейку G5 и выполните автоматическое  заполнение формулами диапазона ячеек G5:G16, используя маркер заполнения. Установите процентный формат для диапазона G5:G16.
  7.  В ячейках  D18, D19 и D20 с помощью Мастера функций постройте формулу.
  8.  Выделите диапазон ячеек D18:D20 и скопируйте его на диапазон E18:E20 и F18:F20, используя маркер заполнения.
  9.  Для диапазона F18:F20 установите процентный формат.
  10.  Сохраните таблицу.

Этап 2. Графическое представление данных.

Построение гистограммы

  1.  Выделите диапазон ячеек С4:Е16, содержащий два ряда числовых данных: план выпуска и фактический выпуск по месяцам, а также ряды с названиями строк и заголовками столбцов. Названия в строках будут использоваться в качестве обозначений на оси Х (категорий), а заголовки столбцов в качестве легенды.
  2.  Вызовите Мастер диаграмм, используя соответствующую кнопку на панели инструментов.
  3.  В диалоговом окне первого шага Мастера диаграмм во вкладке Стандартные в окне Тип выберите и выделите тип Гистограмма, а в окне Вид – первую картинку в первом ряду (обычная плоская гистограмма). Нажмите кнопку Далее.
  4.  На втором шаге Мастер диаграмм выводит образец диаграммы. Нажмите Далее.
  5.  На третьем шаге во вкладке Заголовки определите название диаграммы и названия для осей: в поле ввода Название диаграммы введите текст Показатели производства, в поле Ось Х (Категорий)- Месяцы, в поле Ось Y(Значений)- Количество. Нажмите Далее.
  6.  На последнем шаге выберите место размещения диаграммы (лучше на имеющемся листе). Нажмите кнопку Готово.

Построение круговой диаграммы

  1.  Выделите блок ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих соответственно названия месяцев и фактический выпуск по месяцам. Несмежные столбцы выделяются при нажатой клавише Ctrl.
  2.  Вызовите Мастер диаграмм.
  3.  На первом шаге Мастера диаграмм во вкладке Стандартные в окне Тип выберите и выделите тип Круговая, а в окне Вид – первую картинку во втором ряду. Нажмите кнопку Далее.
  4.  Второй шаг соответствует размещению данных, ничего не изменяйте. Нажмите Далее.
  5.  На третьем шаге во вкладке Заголовок введите название диаграммы. Во вкладке Подписи данных выберите переключатель доля (в этом случае около каждого сектора диаграммы будет выводиться доля в процентах) или категория и доля (тогда будут выводится названия месяцев и доля в процентах). Переключатель Линии выноски отключите. Нажмите Далее.
  6.  Выберите место расположения диаграммы на имеющемся листе, нажмите Готово.

Построение графика

  1.  Выделите блок ячеек, состоящий из двух несмежных столбцов С4:С16 и Е4:Е16, содержащих соответственно названия месяцев и фактический выпуск по месяцам. Несмежные столбцы выделяются при нажатой клавише Ctrl.
  2.  Вызовите Мастер диаграмм.
  3.  На первом шаге Мастера диаграмм во вкладке Стандартные в окне Тип выберите и выделите тип График, а в окне Вид – первую картинку во втором ряду. Перейдите к следующему шагу.
  4.  Выполните следующие шаги Мастера диаграмм.
  5.  Отредактируйте диаграмму, изменяя размеры и шрифты для лучшего представления данных.

Построение смешанной диаграммы

  1.  Выделите диапазон ячеек С4:F16, содержащий заголовки строк, столбцов и две группы разнотипных рядов данных: одна группа- значения планового и фактического выпуска, другая- процент выполнения плана по месяцам.
  2.  Вызовите Мастер диаграмм.
  3.  На первом шаге Мастера диаграмм выберите вкладку Нестандартные, а в ней тип График/Гистограмма 2. Этот тип обеспечивает использование гистограммы для одного ряда данных, графика - для другого и наличие вспомогательной оси.
  4.  Выполните остальные шаги мастера диаграмм. Отредактируйте диаграмму.

Лабораторная работа 29. Личный бюджет

Этап 1. Личный бюджет за январь

Создайте таблицу и введите в нее экспериментальные данные.

  1.  В ячейке В8 и В15 посчитать общую сумму доходов и расходов.
  2.   В ячейке В16 наберите формулу для вычисления конечного баланса   =В3+В8-В15.
  3.  Переименуйте рабочий лист с данными за январь, присвоив ему имя Январь. Для этого дважды щелкните по ярлыку.
  4.  Сохраните документ.

Этап 2. Личный бюджет за остальные месяцы

По умолчанию рабочая книга состоит из 3 рабочих листов. Для  решения нашей задачи требуется 7 листов (для каждого месяца и за полгода), поэтому необходимо вставить недостающие листы и для каждого листа выбрать имя, например, Январь, Февраль,…, Июнь, За полгода.

  1.  Выполните копирование листа Январь. Для этого:
  2.  Выполните команду Правка/Переместить/скопировать лист.
  3.  В открывшемся диалоговом окне выберите лист,  перед которым должна быть вставлена копия листа Январь.
  4.  Включите флажок Создавать копию в нижней части окна и нажмите ОК.
  5.  Скопированный лист будет иметь то же имя Январь с добавлением порядкового номера в скобках.
  6.  Переименуйте скопированный  лист, присвоив ему имя Февраль.
  7.  В листе Февраль удалите столбец, содержащий данные за январь.
  8.  Выполните копирование листа Февраль для вставки в документ его копий (смотри пункт 1).
  9.  Переименуйте скопированные листы,  присвоив новым листам документа имена Март, Апрель,…,  За полгода.
  10.  Внесите в листы Февраль-Июнь экспериментальные данные  и формулы для итоговых строк. Внимание! Начальный баланс за текущий месяц, например  за Февраль, равен конечному  балансу за предыдущий месяц, например за  Январь.

Этап 3. Личный бюджет за полгода.

  1.  Перейдите на рабочий лист За  полгода.
  2.  Вставьте в ячейку В3 начальный баланс за  год, который равен начальному балансу за январь.
  3.  Для получения суммарного оклада за полгода  выполните следующее:
  4.  Наберите в ячейке В5 знак  «=»
  5.  Перейдите в рабочий лист Январь  и щелкните мышью на ячейке В5
  6.  Введите  в строке формул с клавиатуры  знак сложения  «+»
  7.  Включите аналогично в формулу ссылки на ячейки В5 остальных листов (Февраль-Июнь) и нажмите клавишу  Enter.
  8.  Скопируйте формулу из ячейки В5 в ячейки В6:В7, используя маркер заполнения.
  9.  Скопируйте формулу из ячейки В5 в ячейку В10, используя кнопки Копировать и Вставить  на панели инструментов.
  10.  Скопируйте формулу из ячейки В10 в ячейки В11:В14, используя  маркер заполнения.
  11.  Введите в ячейки В8, В15  и В16 формулы для  суммирования доходов и расходов за полгода и вычисления конечного баланса.
  12.  Сохраните документ.

Упражнение№1.

Для каждого  месяца и в целом и  за полгода постройте круговую (кольцевую)  диаграмму:

  1.  Доля статей  доходов в суммарном доходе.
  2.  Доля статей  расходов в суммарном расходе.

Упражнение №2.

Решите задачу, разместив  данные и результаты расчетов  в  таблице  Личный бюджет за полгода на одном рабочем листе.

Упражнение №3.

Для таблицы упражнения №2 постройте:

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

Лабораторная работа 30. Доходность акций

Вам необходимо подготовить отчет о  работе АО  «Пирамида». Для  этого подготовьте следующую таблицу:

Пятый столбец таблицы  содержит  формулы  для расчета чистой прибыли от продаж (ЧПП) акций. Для текущего  года формула вычисления ЧПП выглядит так:

ЧПП=(ЦАТ-ЦАП+ПП)/ЦАП

где ЦАТ  - цена  акций в этом  году,

     ЦАП  -  цена акций в  прошлом  году,

     ПП - прибыль по  акциям в прошлом году.

То есть в ячейку Е4 занести формулу: =(В4-В3+С3)/В3.

Заполните столбец Е соответствующими формулами.

Упражнение№1

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

Упражнение№2

Постройте гистограмму для столбцов «Прибыль по акциям» и  «Дивиденды».

Упражнение№3

Постройте круговую диаграмму для показателя «Дивиденды».

Упражнение№4

Постройте смешанную диаграмму для показателей «Прибыль по акциям» и  ЧПП.

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

Решение задач оптимизации

Цель работы: Знать, как использовать программу Excel для решения сложных задач оптимизации. Научиться формулировать условия задачи табличным образом, формировать ограничения, которым должно удовлетворять решение, и производить поиск оптимального набора переменных.

Задача. Завод производит электронные приборы трех видов(прибор А, прибор В и прибор С), используя при сборке микросхемы трех видов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей:

Прибор А

Прибор В

Прибор С

Тип 1

2

5

1

Тип 2

2

0

4

Тип 3

2

1

1

Стоимость изготовленных приборов одинакова.

Ежедневно на склад поступает 500 микросхем типа 1 и по 400 микросхем типов 2    и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?

  1.  Запустите программу Excel и откройте новую рабочую книгу .
  2.  Дважды щелкните на ярлычке листа и присвойте ему имя Организация производства.
  3.  В ячейке А2, А3 и А4 занесите дневной запас комплектующих – числа 500, 400 и 400, соответственно.
  4.  В ячейке C1, D1 и Е1 занесите нули – в дальнейшем значения этих ячеек будут подобраны автоматически.
  5.  В ячейках диапазона С2:Е4 разместите таблицу расхода комплектующих.
  6.  В ячейках В2:В4 нужно указать формулы для расчета расхода комплектующих по типам. В ячейке В2 формула будет иметь вид = $C$1*C2+$D$1*D2+$E$1*E2, а остальные формулы можно получить методом автозаполнения (обратите внимание на использование абсолютных и относительных ссылок).
  7.  В ячейку F1 занесите формулу, вычисляющую общее число произведенных приборов: для этого выделите диапазон С1:Е1 и щелкните на кнопке Автосумма на стандартной панели инструментов.
  8.  Дайте команду Сервис Поиск решения – откроется диалоговое окно Поиск решения.
  9.  В поле Установить целевую укажите ячейку, содержащую оптимизируемое значение (F1). Установите переключатель Равной максимальному значению (требуется максимальный объем производства).
  10.  В поле Изменяя ячейки задайте диапазон подбираемых параметров – С1:Е1.
  11.  Чтобы определить набор ограничений, щелкните на кнопке Добавить. В диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите диапазон В2:В4. В качестве условия задайте <=. В поле Ограничение задайте диапазон А2:А4. Это условие указывает, что дневной расход комплектующих не должен превосходить запасов. Щелкните на кнопке Ок.
  12.  Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон С1:Е1. В качестве условия задайте >=. В поле Ограничение задайте число 0. Щелкните на кнопке Ок.
  13.  Снова щелкните на кнопке Добавить. В поле Ссылка на ячейку укажите диапазон С1:Е1. В качестве условия выберите пункт цел. Это условие не позволяет производить доли приборов. Щелкните на кнопке Ок.
  14.  Щелкните на кнопке Выполнить. По завершении оптимизации откроется диалоговое окно Результаты поиска решения.
  15.  Установите переключатель Сохранить найденное решение, после чего щелкните на кнопке Ок.
  16.  Проанализируйте полученное решение. Кажется ли оно очевидным? Проверьте его оптимальность, экспериментируя со значениями ячеек С1:Е1. Чтобы восстановить оптимальные значения, можно в любой момент повторить операцию поиска решений.
  17.  Сохраните рабочую книгу.

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

Решение задачи оптимизации расходов предприятия

Цель работы. Научиться использовать программу Excel в задачах оптимального уравнения, а также формулировать условия задачи табличным образом, формировать ограничения, которым должно удовлетворять решение, выполнять поиск оптимального варианта. Мы поняли, что найти нужное решение подбором крайне сложно даже для самых простых задач.

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

Понедельник

Вторник

Среда

Четверг

Пятница

Суббота

Воскресенье

13

14

16

18

22

20

19

Можно использовать сотрудников с пятидневной рабочей неделей (выходные – любые два дня подряд, недельная заработная плата – 500 рублей) и с шестидневной рабочей неделей (выходной – суббота или воскресенье, недельная заработная плата – 700 рублей, то есть шестой рабочий день оплачивается по двойной ставке). При этом требуется, чтобы использовались все варианты расписания работы с двумя выходными ( это позволяет при болезни одного из работников привлечь на замену человека, который только что имел свободный день).

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

  1.  Запустите программу Excel и откройте рабочую книгу. Присвойте имя новому рабочему листу Заработная плата.
  2.  В первую строку рабочего листа введите заголовки столбцов: в ячейку А1 – Выходные дни, в ячейку В1 – Работники, в ячейки D1-J1 – дни недели (Пн, Вт, Ср, Чт, Пт, Сб, Вс).
  3.  В столбец А введите заголовки строк. В ячейках А2-А8 введите разрешенные пары выходных ( от Понедельник, вторник до Воскресенье, понедельник). В ячейки А9 и А10 введите одиночные выходные (Суббота и Воскресенье). В ячейке А12 укажите заголовок Всего. Ячейка А15 должна содержать фразу Недельная оплата работника, а ячейка А16 – текст Общий недельный расход.
  4.  Введите фиксированные данные. В ячейке В13 напишите Всего требуется и введите в ячейках D13-J13 требования к минимальному числу работников согласно заданной таблице. В ячейку В15 введите фиксированную недельную оплату – 500 рублей.
  5.  В ячейках диапазона D2:J10 укажите 1. если для данного расписания день является рабочим, и 0 – если выходным.
  6.  В ячейки В2-В10 введите нулевые (или иные произвольные) значения. Предполагается, что в дальнейшем эти значения будут вычислены автоматически.
  7.  В ячейку D12 введите следующую формулу:

=D8*$B8+D2*$B2+D3*$B3+D4*$B4+D5*$B5+D6*$B6+D7*$B7+D10*$B10+D9*$B9.

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

  1.  Методом заполнения скопируйте только что введенную формулу в ячейки диапазона Е12 – J12.
  2.  В ячейку В12 введите формулу =СУММ(В2:В8)+СУММ(В9:В10)*1,4. Для ввода имени функции используйте строку формул или Мастер формул. По этой формуле вычисляется «приведенное» число сотрудников с учетом увеличенной заработной платы при шестидневной рабочей недели.
  3.  В ячейку В16 введите формулу для исчисления итоговых расходов на заработную плату за неделю:=В15*В12. Именно это значение необходимо свести к минимуму.
  4.  Запустите надстройку Поиск решений (Сервис Поиск решения). Если пункт Поиск решения в меню отсутствует , значит , необходимо подключить эту надстройку, дав команду Сервис Надстройки и установив соответствующий флажок.
  5.  В поле Установить целевую ячейку выберите ячейку В16.
  6.   Для переключателя Равной выберите вариант минимальному значению.
  7.  Щелкните на поле Ограничения и затем- на кнопке Добавить .
  8.  Щелкните на поле Ссылка на ячейку и выберите диапазон В2:В10. В качестве ограничения выберите вариант цел. Щелкните на кнопке Добавить.
  9.  В поле Ссылка на ячейку выберите диапазон В2:В8. Выберите операцию >= и значение 1 в поле Ограничение. Аналогичным образом для диапазона В9:В10 выберите значение >=0.
  10.  В поле Ссылка на ячейку выберите диапазон D12:J12. Выберите операцию >=. В поле Ограничение выберите диапазон D13:J13. Этим задается ограничение на минимальное число работников. Щелкните на кнопке ОК.
  11.  Щелкните на кнопке Выполнить, чтобы провести поиск оптимального варианта. По окончании расчета щелкните на кнопке ОК, чтобы сохранить найденное решение.
  12.  Сохраните рабочую книгу.


Задачи на сообразительность

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

Вычисления в таблицах типа «объект-объект»

Трое студентов летом занялись выращиванием овощей. Когда  собрали урожай, оказалось, что первокурсник вырастил 40 кг капусты, 15 кг моркови, 10 кг капусты и 18 кг лука. Второкурсник вырастил 50 кг капусты, 25 кг моркови, 12 кг огурцов и 2 кг лука. Третьекурсник вырастил 30 кг капусты, 30 кг моркови, 20 кг огурцов и 5 кг лука. Вопросы: Сколько всего овощей вырастил каждый из студентов? Какое общее количество овощей одного вида вырастили все три студента вместе? И, наконец, сколько всего овощей было собрано?

Решение.

Первый этап решения задачи: занести всю исходную информацию в таблицу

Студенты

Капуста

Морковь

Огурцы

Лук

Всего

Первокурсник

Второкурсник

Третьекурсник

Всего

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

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

Такое контрольное вычисление иногда называют «проверка уголком».

Задание 1. Воздушный шар

Есть возможность отправиться в путешествие на воздушном шаре. Каждый аэронавт должен взять с собой вещи и продукты питания. Сформируйте из своих друзей экипаж шара (5 человек). Про каждого аэронавта нужно знать его вес (для каждого – свой), вес взятых им вещей и продуктов (пусть все веса будут разные). Составьте таблицу, по которой можно посчитать вес каждого аэронавта вместе с его вещами и продуктами, вес всех аэронавтов, всех вещей и всех продуктов, а также суммарный вес, который должен поднять шар. Проверьте последнюю величину, вычислив ее «уголком».


Задание 2.  Оборудование школы

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

Для кабинета информатики нужно 11 компьютеров. Для кабинета начальных классов нужно 40 одноместных парт. Для кабинета математики нужна 1 доска. Для кабинета биологии нужен 21 стол. Для кабинета физики нужно 2 доски. Для кабинета информатики нужно 30 столов. Для кабинета биологии нужен 1 компьютер. Для кабинета информатики нужна 1 урна. Для кабинета начальных классов нужен 1 аквариум. Для кабинета биологии нужен 1 экран. Для кабинета физики нужна 1 урна. Для кабинета математики нужен 21 стол. Для кабинета начальных классов нужна 1 урна. Для кабинета биологии нужен 1 кодоскоп. Для кабинета начальных классов нужен 1 стул. Для кабинета физики нужно 23 стола. Для кабинета биологии нужно 3 шкафа. Для кабинета начальных классов нужно 4  шкафа. Для кабинета математики нужен 21 стул. Для кабинета физики нужен 21 стул. Для кабинета начальных классов нужен 1 кодоскоп. Для кабинета биологии нужен 21 стул. Для кабинета физики нужно 3 шкафа. Для кабинета информатики нужно 2 шкафа. Для кабинета физики нужно 2 компьютера. Для кабинета математики нужен 1 шкаф. Для кабинета биологии нужна 1 урна. Для кабинета начальных классов нужен 1 экран. Для кабинета информатики нужна 1 доска. Для кабинета физики нужен 1 экран. Для кабинета физики нужен 1 кодоскоп. Для кабинета биологии нужно 3 аквариума. Для кабинета начальных классов нужно 2 доски. Для кабинета математики нужна 1 урна. Для кабинета информатики нужно 30 стульев. Для кабинета начальных классов нужен 1 стол. Для кабинета биологии нужна 1 доска.

Вычисления в таблицах типа «объекты-свойства-объекты»

Составим таблицу «Поход за покупками» с информацией о покупке в течение не одного дня, а трех (понедельника, вторника и среды). Какой вид получит такая таблица?

Решение. Во-первых, определим тип таблицы. Здесь можно выделить объекты двух классов: товары и дни недели. Существуют свойства, которые относятся к паре объектов «товар-день недели» (например, количество товара, купленное в этот день), а есть свойство, которое относится только к товару, но не относится к дню недели – цена товара. Значит в данном случае мы имеем дело с таблицей типа ОСО – «объекты-свойства-объекты». Проведенные рассуждения позволяют построить таблицу следующего вида:

          

Товар

Цена

руб.

Понедельник

Вторник

Среда

Всего

Кол-во

Ст-ть,

руб.

Кол-во

Ст-ть,

руб.

Кол-во

Ст-ть,

руб.

Кол-во

Ст-ть,

руб.

Хлеб

Масло

Сыр

Яблоки

Кефир

Творог

Всего

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

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

Задание 1. Торты.

Студентка 1 курса решила испечь на свой день рождения 3 торта: яблочный, ореховый и шоколадный. Для приготовления одного яблочного торта требуется 200 г сливочного масла, 200 г муки, 2 яйца, 300 г сахара и 8 яблок. Для приготовления одного орехового торта надо 200 г орехов, 400 г муки, 300 г сахара, 300 г масла и 3 яйца. На один шоколадный торт тратится 3 шоколадки, 2 яйца, 300 г муки, 200 г масла и 100 г сахара. Масло в Цветочном городе стоит 100 монет за кг, сахар – 20 монет за кг, яйца – 20 монет за десяток, мука – 30 монет за кг, орехи – 100 монет за кг, яблоки – 2 монеты за штуку, шоколадки – 10 монет за штуку.

Сколько будет стоить каждый торт? Сколько продуктов каждого вида Студентка должна купить и сколько это будет стоить? Сколько всего денег она должна взять с собой в магазин, отправляясь за продуктами?

Задание 2. Продажа газет

Во время каникул студент решил поработать продавцом газет и проработал целую неделю. За каждый проданный экземпляр газеты «Известия лесов» он получал 10 рублей. За каждый экземпляр «Технической газеты» - 7 рублей. За каждый экземпляр «Новостей с Интернета» - 15 рублей.

В понедельник он продал 8 экземпляров Известий, 7 экземпляров Технической газеты и 5 экземпляров Новостей. Во вторник было продано 13 экземпляров Известий, 4 экземпляра Технической газеты и 8 экземпляров Новостей. В среду – 10 Известий, 10 Технических и 15 Новостей. В пятницу – 10 штук Известий, 5 штук Технических и 8 штук Новостей. В субботу – 9 Известий, 13 Технических газет и 8 Новостей. В воскресенье – 5 экземпляров Известий, 6 экземпляров Технической газеты и 9 экземпляров Новостей.

Сколько экземпляров каждой газеты Студент продавал за каждый день недели и сколько – за   всю неделю; сколько денег он зарабатывал за каждый день недели и сколько – за всю неделю; сколько денег он получал за продажу каждой газеты за всю неделю?  

Задание 3. Напитки

Собираясь на пляж, друзья решили запастись питьем. Максим взял с собой 2 литра кваса и литр газировки, Максуд – литр газировки и 3 литра малинового сиропа, Васька взял 3 литра кваса и 2 литра газировки, Шарап – 3 литра газировки, Гапиз – 1 литр минералки и 1 литр вишневого сока. 1 литр кваса стоит 15 рублей, 1 литр газировки – 25 рублей, 1 литр малинового сиропа и вишневого сока – 26 рублей, литр минеральной воды – 13 рублей.

Сколько всего напитков взял с собой каждый из друзей и сколько ему это стоило; какое количество напитка каждого вида взяли все друзья вместе и сколько это стоило; сколько всего было взято жидкости и сколько потрачено денег всеми друзьями вместе?

Задание 4. Почта дяде Федору.

Дядя Федор, кот Матроскин и пес Шарик летом жили в Простоквашино, а папа с мамой слали им письма, посылки, телеграммы и бандероли, которые доставлял почтальон Печкин. Каждое письмо весило в среднем 100 г, каждая посылка – 5 кг, каждая телеграмма – 50 г, каждая бандероль – 500 г.

Дядя Федор получил 10 писем, 2 посылки, 10 телеграмм и 1 бандероль. Кот Матроскин получил 4 письма, 1 посылку, 2 телеграммы и 1 бандероль. Пес Шарик не получил ни одного письма, ни одной телеграммы, зато получил 4 посылки и 2 бандероли.

  •  Сколько  и какой почты получил каждый из трех жителей Простоквашино?

 Сколько килограммов почты получил каждый из трех простоквашинцев?

 Сколько весила все доставленная Печкиным почта одного вида?

  •  Какой общий груз пришлось перенести почтальону Печкину?

Проверьте последнее число «уголком».

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

Тесты по теме: «Электронные таблицы Excel»

  1.  Относительная ссылка это:

а) когда адрес, на который ссылается формула, изменяется при копировании формулы;

б) когда адрес, на который ссылается формула, при копировании не изменяется;

в) ссылка, полученная в результате копирования формулы;

г) ссылка не в Сибирь, а в Швейцарию.

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

а) А1\В3;

б) А1+В3;

в) А1:В3;

г) А1–В3.

3. После ввода числа в ячейку вы наблюдаете ####### вместо результата. В чем причина такой ситуации?

а) не хватает ширины клетки, чтобы показать введенное число;

б) число введено с ошибкой;

в) число введено в защищенную ячейку;

г) получилось отрицательное число.

  1.  Вы построили диаграмму по ряду данных из таблицы, а через некоторое время изменили эти данные. Как перестроить диаграмму для новых данных?

а) достаточно один раз щелкнуть мышью на диаграмме;

б) достаточно дважды щелкнуть мышью на диаграмме;

в) пересчет диаграммы в стандартном режиме произойдет автоматически;

г) заново построить диаграмму.

5. После ввода числа в ячейку вы наблюдаете ####### вместо результата. Как исправить ошибку?

а) изменить ширину столбца;

б) изменить высоту строки;

в) исправить неточности в формуле;

г) снять защиту с ячейки.

6. Для обозначения адреса ячейки электронной таблицы используются:

а) буквы латинского алфавита и цифры;

б) буквы русского и латинского алфавита;

в) только русские буквы и цифры;

г) специальные символы.

7. При изменении исходных данных все результаты:

а) автоматически пересчитываются;

б) заносятся в память компьютера;

в) сохраняются на диске;

г) попадают в буфер обмена.

8. Абсолютная ссылка это:

а) когда адрес, на который ссылается формула, изменяется при копировании формулы;

б) когда адрес, на который ссылается формула, при копировании не изменяется;

в) ссылка, полученная в результате копирования формулы;

г) ссылка в Сибирь.

9. Требуется расположить текст (данные) в ячейке таким образом, чтобы разные слова переходили на следующую строку. Ваши действия:

а) изменить ширину столбца так, чтобы текст переходил на следующую строку;

б) набрать данные в разных строках;

в) выделить, выбрать команду меню Формат| Ячейка | Выравнивание | Переносить по словам.

г) нажать Enter.

10. В электронной таблице строки именуются так:
а) A, B, C, ..Z, AA..;

б) 1, 2, 3, ...;

в) произвольно;

г) справа налево;

11. В электронной таблице знак "$"  перед номером строки в обозначении ячейки указывает на:
а) денежный формат;

б) начало формулы;

в) абсолютную адресацию;
г) начало выделения блока ячеек.

12. В электронных таблицах нельзя удалить…

а) столбец,

б) строку,

в) адрес ячейки,

г) содержимое ячейки. 

 


EMBED Excel.Sheet.8  

EMBED Excel.Sheet.8  




1. і Розрізняють гостру і хронічну дизентерію
2. ПРАВОВИХ ДИСЦИПЛІН Д І З Н А Н Н Я ПЛАНИ ПРАКТИЧНИХ ЗАНЯТЬ ТА САМОСТІЙНОЇ РОБОТИ
3. Тема- Порядок ценообразования и ценовая политика организации
4. сомножителей которая при дифференцировании упрощается а за dv выбирается та часть подынтегрального выражен
5. осенняя А также ~ повседневная модельная домашняя спортивная и др
6. Два основных понимания всемирной истории- унитарно-стадиальное и плюрально-циклическое
7. Тема 17 Химические свойства и биологическая роль s и рэлементов Для элементов IАподгруппы общая электронна
8. Бехистунской надписи относятся первое по датировке сохранившегося оригинала упоминание Ахура Мазды дре
9.  60ті рр ХІХ століття виявилися переломним періодом в історії педагогічної думки та школи
10. Реферат- Місцеві позики і їх розвиток в Україні
11. Реферат- Гемоцианин
12. вариант 0003 КАЗАХСКИЙ ЯЗЫК 1
13. Достоинство государства зависит в конечном счёте от достоинства образующих его личностей Дж
14. Nt0N0 Это означает что темп прироста численности популяции постоянен
15. Реферат- Проектирование кулачковых механизмов
16. Курс лекций по дисциплине «Здоровый человек и его окружение»
17. на тему- Антимонопольная политика государства в современных условиях Реферат АНТИМОНОПОЛЬНАЯ
18. Фашистский политический режим
19. Тема- Складносурядне речення
20. а. Стиль викладу- есе властиві науковість емоційність експресивність художність

Материалы собраны группой SamZan и находятся в свободном доступе