close

Вход

Забыли?

вход по аккаунту

?

Лаб Excel

код для вставкиСкачать

ЛАБОРАТОРНЫЙ ПРАКТИКУМ
ПО ИНФОРМАТИКЕ
Табличный процессор Excel
О Г Л А В Л Е Н И Е
Введение3
Раздел I. Базовые технологии6
Обработка данных6
Применение итоговых функций.8
Подготовка и форматирование прайс-листа10
Форматирование ведомости11
Определение имен, создание имен и применение имен12
Решение уравнений средствами программы Excel13
Применение средств автоматизации ввода13
Раздел II. Вставка объектов14
Построение диаграммы14
Построение графиков15
Построение графиков с условиями17
Раздел III. Базы данных и сводные таблицы18
Построение операции с базой данных18
Построение сводной таблицы19
Построение сводной диаграммы20
Настройка режима проверки вводимых данных21
Еще раз о сводных таблицах22
Работа с данными списка, обработка списков, поиск записей, сортировка списков23
Раздел IV. Вычисление итогов24
Вычисление итогов25
Консолидация данных27
Совместное использование Excel и Word28
Проверочная работа по Microsoft Excel29
Раздел V. Решение экономических задач32
Экономические расчеты32
Решение задач оптимизации40
Решение задачи оптимизации расходов предприятия42
Введение
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. Дайте команду ФайлСохранить как и сохраните рабочую книгу под именем Книга1_Номер группы.
4. Сделайте текущей ячейку A1и введите в нее заголовок Результаты измерений.
5. Введите произвольные числа в последовательные ячейки столбца А, начиная с ячейки A2 до А15.
6. Введите в ячейку B1 строку Удвоенное значение. 7. Введите в ячейку С1 строку Квадрат значения.
8. Введите в ячейку D1 строку Квадрат следующего числа.
9. Введите в ячейку В2 формулу =2*А2.
10. Введите в ячейку С2 формулу =А2*А2.
11. Введите в ячейку D2 формулу =В2+С2+1.
12. Выделите протягиванием ячейки В2, С2 и D2.
13. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах В, С и D, сколько имеется чисел в столбце А.
14. Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значением ячейки в столбце А текущей строки.
15. Измените одно из значений в столбце А и убедитесь, что соответствующие значения в столбцах В, С и D в этой строке были автоматически пересчитаны.
16. Введите в ячейку Е1 строку Масштабный множитель.
17. Введите в ячейку Е2 число 5.
18. Введите в ячейку F1 строку Масштабирование.
19. Введите в ячейку F2 формулу =А2*Е2.
20. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.
21. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес Е2 в формуле задан относительной ссылкой.
22. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$E$2, и нажмите клавишу ENTER.
24. Повторите заполнение столбца F формулой из ячейки F2.
25. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу .
Абсолютная адресация
Имеется список персонала и окладов. Каждому нужно начислить премию в размере 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 введите формулу, используя имя ячейки и скопируйте ее вниз. Чтобы в строку формул вставить имя ячейки выберите в меню пункт "Вставка/Имя/Вставить".
Лабораторное занятие № 2
Применение итоговых функций
Цель работы: Ознакомиться с некоторыми итоговыми функциями, научиться использовать итоговые функции для вычисления значений, характеризующих набор данных. Выяснить, как автоматически определяется диапазон значений, обрабатываемых функцией, и как изменить его вручную.
1. Запустите программу Excel и откройте рабочую книгу, созданную ранее.
2. Выберите рабочий лист Данные.
3. Сделайте текущей первую свободную ячейку в столбце А.
4. Щелкните на кнопке Автосумма на стандартной панели инструментов.
5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу Enter.
6. Сделайте текущей следующую свободную ячейку в столбце А.
7. Щелкните на кнопке Вставка функции на стандартной панели инструментов.
8. В списке Категория выберите пункт Статистические.
9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке Ok.
10. Переместите методом перетаскивания палитру формул, если она заслоняет нужные ячейки. Обратите внимание , что автоматически выбранный диапазон включает все ячейки с числовым содержимым, включая и ту, которая содержит сумму. Выделите правильный диапазон методом протягивания и нажмите клавишу Enter.
11. Используя порядок действий , описанный в пп.6-10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ).
12. Сохраните рабочую книгу.
Задание. Обработка данных метеостанции
Количество осадков ( в мм)199219931994январь37,234,58февраль11,451,31,2Март16,520,53,8Апрель19,526,911,9Май11,745,566,3Июнь129,171,560Июль57,1152,950,6август43,896,6145,2сентябрь85,774,879,9октябрь8614,574,9ноябрь12,52156,6декабрь21,222,39,4За три годаСуммарноМаксимумМинимумСреднемесячноСтандартное отклонение от среднегоКоличество засушливых месяцевОсадки в незасушливые месяцыОбработать данные, используя Мастер функций. Функцию СТАНДОТКЛОН - для подсчета стандартного отклонения, функцию СЧЕТЕСЛИ для подсчета количества засушливых месяцев, функцию СУММЕСЛИ для подсчета осадков, которые выпали в незасушливые месяцы.
Справка. Функция СЧЕТЕСЛИ (интервал, критерий) подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Вычислите количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков. В данном примере используется формула =СЧЕТЕСЛИ(В4:В15,"<10"). Функция СУММЕСЛИ (интервал, критерий, сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. В данном примере вводится формула =СУММЕСЛИ(В4:В15,">=10").
Задание. С помощью этой функции можно решить более трудную задачу: каково суммарное количество осадков было в 1993 г. в те месяцы, которые в 1994 г. были засушливыми. Напишите какой формулой дается решение и поместите результат в ячейку В24. Оформление таблиц
В диалоговом окне "Форматирование ячеек" есть вкладки "Выравнивание", "Шрифт", "Граница", "Вид", с помощью которых можно профессионально оформить заголовки таблиц. Используя эти средства, можно также подготовить бланки документов.
Задание. Для книжного магазина составляется ежедневная таблица продаж книг.
Оформите такую таблицу на рабочем листе.
Продано книг
Д
А
Т
А
В
С
Е
Г
Ов том числеН
А
У
Ч
Н
А
Я
Т Е Х
Н
И
Ч К
Е А
С ЯХ У Д В
О Е
Ж Н
Е Н
С А
Т Я15 март53115620017516 март14540510017 март475124140211
Лабораторное занятие № 3
Подготовка и форматирование прайс-листа
Цель работы: Научиться форматировать документ 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. Сохраните рабочую книгу.
Лабораторное занятие № 4
Форматирование ведомости
Цель работы: Научиться условно форматировать документ 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. Сохраните рабочую книгу. Лабораторное занятие № 5
Определение имен, создание имен и применение имен
Цель работы: Научиться задавать ячейкам и диапазону ячеек имена и использовать их в формулах.
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. Сохранить книгу Лабораторное занятие № 6
Решение уравнений средствами программы 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. Сохраните рабочую книгу.
Лабораторное занятие № 7
Применение средств автоматизации ввода
Цель работы: Научиться производить заполнение последовательностью чисел (дат), прогрессией, формулами. Знать, как при заполнении формулами учитывается относительная адресация.
1. Запустите программу Excel .
2. Откройте ранее созданную книгу.
3. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый ( ВставкаЛист). Дважды щелкните на ярлычке нового листа и переименуйте его как Дополнительные расходы по месяцам.
4. Сделайте текущей ячейку А1 и введите в неё текст: Месяцы.
5. Сделайте текущей ячейку В1 и введите в неё текст: Расходы.
6. Сделайте текущей ячейку А2. Введите в нее текст Январь 2001. Нажмите клавишу ENTER. Убедитесь, что текст был автоматически распознан как дата (изменился формат, а данные выровнялись по правому краю ячейки). Нажмите клавишу ВВЕРХ. При желании, дайте команду Формат Ячейки и укажите иной формат записи даты.
7. Установите указатель мыши на маркер заполнения в правом нижнем углу рамки текущей ячейки. Нажмите правую кнопку и протяните рамку так, чтобы она охватила все ячейки от А2 до А25.
8. Отпустите кнопку мыши. В открывшемся меню выберите Заполнить по месяцам. Убедитесь, что в столбце А появились обозначения для всех месяцев по декабрь 2002 года включительно.
9. В данном примере будем считать, что в первый месяц расходы составляли 10 рублей, а в каждом последующем возрастали на 10%.Сделайте текущей ячейку В2. Введите в нее число 10. Нажмите клавишу ENTER.
10. Щелкните на ячейке В2 правой кнопкой мыши и выберите в контекстном меню Формат ячеек. На вкладке Число выберите вариант Денежный и щелкните на кнопке ОК. Убедитесь, что число теперь записано как денежная сумма.
11. Щелкните правой кнопкой мыши на маркере заполнения и протяните рамку так, чтобы она охватила ячейки с В2 по В25.Отпустите кнопку мыши. В открывшемся меню выберите пункт Прогрессия.
12. На панели Тип установите переключатель Геометрическая, в поле Шаг задайте значение 1,1. Щелкните на ОК.
13. В ячейку С1 введите текст Нарастающий итог.
14. Сделайте текущей ячейку С2. Введите в нее текст =В2. Это формула, которая означает, что значение ячейки С2 равно значению ячейки В2. Эта простая формула гарантирует, что если значение в ячейке В2 будет изменено, то ячейка С2 все равно будет содержать верные данные.
15. Щелкните на ячейке С3. Вставить знак " = ", для ввода формулы. Щелкните на ячейке В3. Убедитесь, что ссылка на эту ячейку помещена в строку формул. Нажмите клавишу +. Щелкните на ячейке С2. Нажмите клавишу ENTER.
16. Снова сделайте ячейку С3 текущей. Наведите указатель мыши на маркер заполнения, нажмите левую кнопку и протяните рамку так, чтобы она охватывала ячейки с С3 по С25.
17. Щелкните на одной из ячеек столбца С и посмотрите, какая формула в ней записана. Убедитесь, что все формулы были скорректированы по принципу относительной адресации.
18. Сохраните рабочую книгу.
Раздел II. Вставка объектов
Лабораторное занятие № 8
Построение диаграммы
Цель работы. Научиться строить графики на основе данных, содержащихся на рабочем листе, настраивать формат диаграммы, задавать отображаемые данные и оформлять получающуюся диаграмму. Знать как можно изменить формат готовой диаграммы.
1. Запустите программу Excel и откройте ранее созданную рабочую книгу .
2. Откройте рабочий лист Дополнительные расходы по месяцам.
3. Методом протягивания выделите диапазон ячеек А2:С25.
4. Щелкните на значке Мастер диаграмм на стандартной панели инструментов.
5. В списке Тип выберите пункт Гистограмма (для отображения данных в виде столбчатой диаграммы). В палитре Вид выберите нижний пункт в первом столбце (трехмерная гистограмма). Щелкните на кнопке Далее.
6. Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно.
7. На вкладке Ряд выберите пункт Ряд1, щелкните в поле имя, а затем на ячейке В1. Аналогично, выберите пункт Ряд2 и щелкните сначала в поле Имя, а затем на ячейке С1. Щелкните на кнопке Далее.
8. Выберите вкладку Заголовки. Задайте заголовок диаграммы, введя в поле Название диаграммы текст Диаграмма расходов. Щелкните на кнопке Далее.
9. Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего лист. Щелкните на кнопке Готово.
10. Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее. Попробуйте навести указатель мыши на любой из элементов диаграммы. Убедитесь, что во всплывающем окне отображается точное значение данного элемента диаграммы.
11. Щелкните на одном из элементов ряда Нарастающий итог. Убедитесь, что весь ряд выделен.
12. Дайте команду Формат Выделенный ряд. Откройте вкладку Вид.
13. Щелкните на кнопке Способы заливки. Установите переключатель Заготовка, в раскрывающемся списке выберите пункт Океан, задайте тип штриховки диагональная 1. Щелкните на кнопке ОК и еще раз на кнопке ОК.
14. По своему усмотрению измените оформление ряда данных Расходы и других элементов диаграммы.
15. Сохраните рабочую книгу.
Лабораторное занятие № 9
Построение графиков
Цель работы: Научиться строить графики на основе данных, содержащихся на рабочем листе, настраивать формат диаграммы, задавать отображаемые данные и оформлять получающуюся диаграмму.
Построить график функции шаг 2.
1. Запустите программу Excel и откройте рабочую книгу, созданную ранее.
2. В столбец А, начиная с ячейки А1, введите набор значений независимой переменной х согласно заданному шагу (используя маркер заполнения, протянуть правой кнопкой мыши и в открывшемся меню выбрать пункт Прогрессия, где указать шаг).
3. В столбец В, начиная с ячейки В1, введите формулу согласно нашему значению функции, то есть =А1^2.
4. Методом протягивания выделите ячейки столбца В.
5. Щелкните на значке Мастер диаграмм на стандартной панели инструментов.
6. В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке Далее.
7. Так как диапазон ячеек был выделен заранее, мастер диаграмм автоматически определяет расположение рядов данных. Убедитесь, что данные на диаграмме выбраны правильно. На вкладке Ряд в поле Имя укажите Результаты измерений. Щелкните на кнопке Далее.
8. Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке Далее.
9. Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово.
10. Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее и щелкните на построенной кривой, чтобы выделить ряд данных.
11. Дайте команду Формат Выделенный ряд. Откройте вкладку Вид.
12. На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир.
13. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитрах Цвет и Фон выберите зеленый цвет.
14. Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите, как изменился вид графика.
15. Сохраните рабочую книгу.
Задание2. Построить графики функций:
1) шаг 1.
2) шаг 2.
3) шаг 2.
4) , .
Лабораторное занятие № 10
Построение графиков с условиями
А) с одним условием
Рассмотрим пример построения графика функции
при .
Для построения графика функции необходимо сначала построить таблицу ее значений при различных значениях аргумента, причем обычно аргумент изменяется с фиксированным шагом. Шаг выбирают небольшим, чтобы таблица значений функции отражала ее поведение на интервале табуляции. В нашем примере шаг изменения аргумента равен 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 ввести соответствующую формулу. Заполнить формулами диапазон (B2:L12), и на основе этих данных построить диаграмму (поверхность).
Раздел III. Базы данных и сводные таблицы
Лабораторное занятие № 11
Построение операции с базой данных
Цель работы: Научиться использовать электронную таблицу Excel в качестве базы данных, научиться выполнять операции сортировки и фильтрации по какому- либо признаку. 1. Запустите программу Excel и откройте новую рабочую книгу.
2. Присвойте листу имя Сведения о поставках.
3. Предполагается, что предприятие получает пять видов материалов :бумагу, фанеру, картон, полиэтилен и ткань- от пяти поставщиков, находящихся в Братске, Казани, Курске, Мурманске и Череповце. Каждый из поставщиков может поставлять любой вид материалов. Поставки производятся не чаще раза в месяц, единица измерения - тонна.
4. В ячейки А1-D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.
5. Введите несколько десятков записей, имеющих описанную выше структуру. Реальные "объемы поставки" значения не имеют.
6. Общая сортировка базы данных. Сделайте текущей любую ячейку базы данных и дайте команду Данные Сортировка. Убедитесь, что при этом выделяется вся (кроме заголовков полей) база данных.
7. В списке Сортировать по выберите пункт Месяц и режим по возрастанию.
8. В списке Затем по (вторичная сортировка) выберите пункт Поставщик и режим по возрастанию.
9. В списке В последнюю очередь, по выберите пункт Товар и режим по возрастанию. Щелкните на кнопке ОК.
10. Убедитесь, что база данных отсортирована по указанным категориям.
11. Последовательная сортировка базы данных. С помощью кнопки Отменить на панели инструментов восстановите прежний порядок записей базы данных. Того же порядка сортировки можно добиться другим способом.
12. Выберите любую ячейку в столбце Товар и щелкните на кнопке Сортировка по возрастанию на панели инструментов.
13. Выберите любую ячейку в столбце Поставщик и щелкните на кнопке Сортировка по возрастанию на панели инструментов.
14. Выберите любую ячейку в столбце Месяц и щелкните на кнопке Сортировка по возрастанию на панели инструментов. Убедитесь, что итоговый порядок сортировки тот же, что и в предыдущем случае. Обратите внимание , что в этом случае мы сначала провели третичную сортировку, затем вторичную и на последнем этапе первичную.
15. Фильтрация данных . Чтобы включить режим фильтрации , дайте команду ДанныеФильтрАвтофильтр. Обратите внимание на появление раскрывающих кнопок у заголовков полей базы данных.
16. Чтобы отобрать только записи, описывающие поставки из Братска, щелкните на раскрывающейся кнопке у поля Поставщик и выберите в списке пункт Братск. Обратите внимание на то, что раскрывающая кнопка действующего фильтра и номера отобранных строк отображаются синим цветом. Чтобы отменить текущий фильтр, еще раз щелкните на раскрывающей стрелке и выберите пункт Все.
17. Чтобы отобрать наиболее крупные разовые поставки , щелкните на раскрывающей стрелке у поля Объем и выберите в списке вариант Первые 10.
18. Выберите с помощью счетчика число 20 и далее пункт наибольших и вариант % от количества элементов. Щелкните на кнопке ОК. В результате будет отобрано 20 % записей, содержащих наибольшие значения объема поставок.
19. Чтобы отменить режим фильтрации записей, еще раз дайте команду Данные ФильтрАвтофильтр.
20. Сохраните рабочую книгу.
Лабораторное занятие № 12
Построение сводной таблицы
Цель работы: Научиться создавать сводные таблицы для получения итоговых сведений на основе содержимого базы данных. Научиться фильтровать данные сводной таблицы, динамически перестраивать сводную таблицу, а также изменять способ отображения области данных.
1. Запустите программу Excel. Откройте рабочую книгу, созданную ранее с рабочим листом Сведения о поставках.
2. Сделайте текущей ячейку в пределах базы данных. Дайте команду Данные Сводная таблица.
3. Убедитесь, что установлены переключатели в списке или базе данных Microsoft Excel и сводная таблица. Щелкните на кнопке Далее.
4. Убедитесь, что диапазон базы данных выбран правильно. Щелкните на кнопке Далее.
5. Убедитесь, что установлен переключатель Новый лист. Щелкните на кнопке Макет.
6. Перетащите кнопки в соответствующие области макета сводной таблицы: кнопку Месяц - в область Страница, кнопку Поставщик - в область Столбец, кнопку Товар - в область Строка, кнопку Объем - в область Данные.
7. Кнопка в области Данные будет иметь вид Сумма по полю Объем. Нас это устраивает. Щелкните на кнопке ОК.
8. Щелкните на кнопке Параметры. В поле Имя введите текст Сводная таблица поставок. Щелкните на кнопке ОК. Щелкните на кнопке Готово.
9. Переименуйте рабочий лист со сводной таблицей, дважды щелкнув на его корешке. Дайте ему имя Сводная таблица поставок.
10. Посмотрите на готовую сводную таблицу. Она показывает, сколько материалов определенного типа пришло от конкретного поставщика, независимо от времени поставки. Дважды щелкните на любой из ячеек сводной таблицы, чтобы увидеть на новом рабочем листе записи, на основе которых сформированы данные в этой ячейке.
11. Раскрывающие кнопки рядом с именами полей таблицы позволяют выполнить сортировку по соответствующему полю. Выберите конкретный месяц в раскрывающемся списке Месяц, чтобы увидеть данные, относящиеся к этому месяцу.
12. Перетащите кнопку Месяц в область Столбец, а кнопку Товар - в область Страница. Сводная таблица автоматически перестроится в соответствии с новой структурой.
13. Дважды щелкните на кнопке Сумма по полю объем. В открывшемся диалоговом окне Вычисление поля сводной таблицы щелкните на кнопке Дополнительно. В раскрывающемся списке Дополнительные вычисления выберите пункт Доля от суммы по строке. Щелкните на кнопке ОК.
14. Посмотрите на новый вид сводной таблицы. Среди прочего, мы определите, какую долю в общем потоке поставок имеет каждый из поставщиков.
15. Сохраните рабочую книгу.
Лабораторное занятие № 13
Построение сводной диаграммы
Цель работы: Научиться создавать сводные диаграммы на основе сводных таблиц. При прямом создании сводной диаграммы соответствующая сводная таблица создается в любом случае. При использовании сводной диаграммы нам доступны как операции, соответствующие сводной таблице, так и те, которые применяют при построении диаграмм. Иными словами, сводные диаграммы объединяют средства анализа и средства графического представления информации, имеющейся в базе данных.
1. Запустите программу Excel. Откройте рабочую книгу, созданную ранее с рабочим листом Сводная таблица поставок..
2. Щелкните на кнопке Мастер диаграмм на панели инструментов Сводная таблица. Сводная таблица строится автоматически на новом рабочем листе.
3. Переименуйте этот рабочий лист, дав ему имя Сводная диаграмма.
4. Поэкспериментируйте со сводной диаграммой, чтобы выяснить, какие ее параметры можно изменять.
5. Выполните фильтрацию отображаемых данных.
6. Измените величину, отображаемую на диаграмме.
7. Переместите поле базы данных в другую область диаграммы.
8. Измените тип диаграммы.
9. Измените формат отображения элементов диаграммы.
10. Сохранить результат работы.
Лабораторное занятие № 14
Настройка режима проверки вводимых данных
Цель работы: Научиться использовать средства автоматической проверки вводимых данных. 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. Сохраните рабочую книгу.
Лабораторное занятие № 15
Еще раз о сводных таблицах
Цель работы: Отработка навыков создания сводных таблиц
Задание 1
Сводными называются вспомогательные таблицы, которые содержат часть данных анализируемой таблицы. Эти данные отобраны так, чтобы наилучшим образом отображалась зависимость между ними.
Создать таблицу:
Автосалон "Счастливое колесо"ПродавецМаркаГод выпускаОборотДатаГуськовАуди100199219 000,00 26.сен.97ПетренкоПежо60519921 500,00 26.сен.97ДовгальБМВ-520199010 600,00 12.сен. 97ПетренкоБМВ325199210 999,00 12.сен.97ГуськовМерседес190199256 000,00 15.сен.97КосыгинВАЗ2110199815 666,00 26.окт.99ГуськовОпель Фронтера199225 000,00 12 сен 97ПетренкоРеноСафран199115 500,00 15.сен.98ПетренкоМерседес60019926 300,00 16.сен.98КосыгинВАЗ2110199815 678,00 30.окт.98ПетренкоФордМондео199312 999,00 13.сен.97ДовгальVWПассат199112 600,00 12 мар 97 Создание и обработка Сводных таблиц осуществляется с помощью специального мастера. Для его запуска предназначена команда Данные/Сводная таблица.
Открывается первое диалоговое окно - Мастер сводных таблиц-шаг 1 из 4. В нем указывается источник данных для сводной таблицы. (Так как наша таблица находится на рабочем листе Excel, то в области создать таблицу на основе данных, находящихся: активизировать в списке или базе данных Excel). Нажать кнопку Далее, чтобы перейти в следующее окно мастера.
Во втором окне Мастера сводных таблиц определяется диапазон ячеек, данные из которого будут включены в сводную таблицу. (В данном примере Лист 1!$A$3:$E$13). Нажмите кнопку Далее.
В третьем окне Мастера сводных таблиц определяется структура создаваемой таблицы. В центре этого диалогового окна расположены области строк, столбцов, страниц и данных. Все заголовки полей таблицы отображаются справа от областей. Каждый заголовок поля можно переместить с помощью мыши в любую область.
Переместите поле Продавец в область страниц. Таким образом, вы зададите размещение данных о каждом продавце на "отдельной странице".
Размещение других полей данных зависит от поставленной задачи. В области Данные разместите показатели оборота. Поля Дата и Марка поместите в области строк, Год выпуска в область столбцов. Нажмите кнопку Далее.
В четвертом диалоговом окне Мастера сводных таблиц определяется местоположение Сводной таблицы ( на новом или текущем листе). Нажмите кнопку Готово.
Для редактирования сводной таблицы предназначена Панель инструментов Сводные таблицы, которая открывается после создания сводной таблицы.
Проанализируйте результат своей работы и поработайте с данными сводной таблицы.
Задание 2.
Для предыдущей таблицы создать Сводную таблицу, где критериями при разбиении данных на "отдельные страницы" будут использоваться поля Продавец, Марка и Год выпуска.
Сохранить своё задание в отдельной книге.
Лабораторное занятие № 16
Работа с данными списка, обработка списков, поиск записей, сортировка списков
Цель работы: Научиться создавать списки и выполнять со списком различные операции.
При создании списка следует придерживаться следующих правил:
* Составление списка нужно начинать с создания заголовок столбцов (т.е. название полей должны быть в верхней строке и эту строку называют строкой заголовка). * Шрифт в строке заголовка должен быть другого цвета или иметь характерное начертание (курсив или жирное);
* Каждая строка должна представлять собой отдельную запись;
* Следует избегать пустых строк между записями, а также между заголовком и первой записью.
Создайте список:
№ФамилияИмяДолжностьДата РожденияАдресСтаж работы1) Гуськов АлексейМенеджер1-авг-75Озерная 5102) Хан ИринаИнспектор12-мар-78...53) БелкинаОльгаПродавец......24) ПрутковКозьмаИнспектор......15) ...............6) ............... Сохраните документ.
1. Активизируйте любую ячейку списка и выполните команду Данные |Форма. (откроется диалоговое окно формы данных). Окно формы данных содержит несколько полей ввода, названия которых соответствует заголовкам столбцов списка. В окне формы данных можно проводить поиск информации, вводить в список новые записи и удалять ненужные, а также изменять список.
Добавьте в свой список еще 5-7 записей. Для этого:
* Выполните команду Данные|Форма.
* Выполните щелчок на кнопке Добавить.
* Введите новые данные в пустые поля ввода.
* После завершения ввода данных нажать [Enter].
2. Произведите поиск записей. Предположим, что в списке сотрудников фирмы нужно просмотреть данные о сотрудниках в возрасте от 30 до 50 лет, у которых стаж работы на фирме составляет не менее 5 лет и фамилии начинаются на букву "К".
Для этого:
1. Выполните команду Данные/Форма, щелкните кнопку Критерий. 2. Перейдите в поле Фамилия. Введите букву к. Результат выбора не зависит от того, какую букву (прописную или строчную) вы ввели при задании критерия.
3. С помощью клавиши Tab перейдите в поле Дата рождения. Введите условие >01.01.51. Это условие для выбора записей о сотрудников, чей возраст не превышает 50 лет.
4. Перейдите в поле Возраст. Введите условие >30 для выбора записей о сотрудниках старше 30 лет.
5. Перейдите в поле Стаж работы. Введите условие >5 для выбора записей о сотрудниках, чей стаж работы превышает 5 лет.
Для перехода в режим просмотра данных нажмите клавишу Enter. С помощью клавиш Далее и Назад просмотрите все записи в таблице, которые удовлетворяют сложному критерию.
Задание. Выберите инспекторов, стаж которых меньше 5 лет.
3.Сортировка записей в списке.
Текстовые данные сортируют в алфавитном или в обратном алфавитном порядке, а числовые- либо в порядке убывания, либо в порядке возрастания.
Выполните команду Данные/Сортировка и установите следующие параметры для сортировки:
Фамилия: по возрастанию;
Дата рождения: по убыванию.
4. Сохранить документ.
Раздел IV. Вычисление итогов
Лабораторное занятие № 17
Вычисление итогов
Цель работы: Отработка навыков вычисления итогов. Задание 1
Создайте таблицу
Книга 2000 - Результаты аукционовГородДатаСуммаПокупателиПродано книгДонецк17 июл823 000,00р.9345Махачкала20 авг1 340 000,00р.18525Киев7 июл1 180 000,00р.205147Москва21 июл1 200 000,00р.21445Киев7 авг1 523 000,00р.14584Махачкала23 июл1 754 600,00р.14695Киев10 авг1 002 000,00р.15356Харьков30 июл745 000,00р.125102Москва17 июл1 724 000,00р.125121Махачкала1 авг82 600,00р.782132Киев15 июл932 000,00р.499151Москва30 авг256 000,00р.2525Харьков12 июл821 000,00р.385148Москва25 авг756 000,00р.125318 Донецк 21 авг945 000,00р. 358 125 1. Выбор шрифта . В пункте меню ФорматЯчейки вкладка Шрифт выбрать шрифт, чтобы выделить заглавие таблицы.
2. Рамки. Для повышения наглядности вставьте пустую строку между строкой с названием таблицы и строкой с заголовками столбцов. Затем выделите диапазон ячеек с заголовками столбцов (A3- E3) , откройте вкладку Граница диалогового окна Формат ячеек и выберите стиль рамки.
3. Выделите всю таблицу и активизируйте команду Автоформат из меню Формат, вследствие чего откроется одноименное диалоговое окно. Выберите формат в поле Список форматов.
4. С помощью функции автоматического вычисления итогов определить итог по трем последним столбцам.
5. Отсортируйте данные в таблице по названием городов. Для этого выполните команду ДанныеСортировка. 6. Выполните команду Данные Итоги. Откроется диалоговое окно Промежуточные итоги.
* В поле списка При каждом изменении в выберите заголовок столбца, для которого необходимо вычислить промежуточные итоги после каждого изменения данных на рабочем листе ( В данном примере Город).
* Для вычисления итоговой суммы в поле Операция установить функцию Сумма.
* В области Добавить итоги по активизировать опцию Покупатели и Продано книг, Сумма отключить опцию Дата.
В результате таблица будет дополнена строками, которые содержат итоговые значения по каждому городу отдельно и общий итог для всех городов.
При подведении промежуточных итогов автоматически происходит структуризация данных.
Сохраните рабочую книгу.
Задание 2
Создайте таблицу, содержащую данные о сбыте.
Автосалон "Счастливое колесо"ПродавецМаркаГод выпускаОборотДатаГуськовАуди100199219 000,0026.сен.97ПетренкоПежо60519921 500,0026.сен.97ДовгальБМВ-520199010 600,0012 сен 97ПетренкоБМВ325199210 999,0012.сен.97ГуськовМерседес190199256 000,0015.сен.97КосыгинВАЗ2110199815 666,0026.окт.99ГуськовОпель Фронтера199225 000,0012 сен 97ПетренкоРеноСафран199115 500,0015.сен.98ПетренкоМерседес60019926 300,0016.сен.98КосыгинВАЗ2110199815 678,0030.окт.98ПетренкоФордМондео199312 999,0013.сен.97ДовгальVWПассат199112 600,0012 мар97
Задание. С помощью функции автоматического вычисления итогов определить оборот каждого продавца.
В Excel имеется возможность комбинирования нескольких итогов ( применение вложенных итогов). Например, подсчитаем еще количество автомобилей, проданных каждым продавцом. В диалоговом окне Промежуточные итоги в поле При каждом изменении в установить элемент Продавец, в поле Операция - Количество значений, в области Добавить итоги активизировать опцию Марка. Для этого, чтобы в таблице отображались все итоги, следует выключить опцию Заменить текущие итоги.
Задание 3.
Создайте таблицу
ФирмаСумма в
счетеДатаСумма оплатыДолгЛандыш100р.12.01.01100р.Василек200р.20.01.01200р.Ромашка300р.23.01.01150р.Ландыш400р.30.01.01400р.Ромашка150р.02.02.01150р.Василек200р.02.03.01200р.Ландыш400р.18.02.01250р.Ромашка300р.25.05.01300р.Гвоздика400р.28.09.01300р.Получите промежуточные итоги по оплате счетов каждой фирмой.
Сохраните рабочую книгу.
Задание 4.
Вычисление итоговВычисление общего среднего бала для студентов 1 курса по итогам зимней и летней сессииСессияФамилияМатематикаИнформатикаБух/учетСредний
балзим.Белкина А.А.554лет.Белкина А.А.335лет.Гуськов Г.Г.444зим.Медведева М.М.334зим.Ласточкина Л.Л.555зим.Хан И.Г.333лет.Хан И.Г.453зим.Гуськов Г.Г.454лет.Медведева М.М.545лет.Ласточкина Л.Л.544
1. Создать таблицу и ввести данные.
2. Произвести сортировку по столбцу фамилиям по возрастанию.
3. Заполнить ячейки Средний балл при помощи клавиши Вставка функций, выбрав функцию СРЗНАЧ
4. Вычислить итоги среднего балла имеющихся студентов за год. Лабораторное занятие № 18
Консолидация данных
Цель работы: Научиться подводить итоги на основании данных расположенных в различных областях таблицы.
Консолидация выполняется тогда, когда надо подвести итоги для данных, расположенных в разных областях таблицы. Данные могут находиться на одном рабочем листе, на разных листах или разных книгах.
Создайте таблицы на разных листах:
АВАВАВ1. Автосалон (Москва)1.Автосалон (Москва)1.Автосалон (Москва)2. МаркаКол-во2.МаркаКол-во2.МаркаКол-во3. БМВ653.БМВ83.БМВ74. Форд474.Форд54.Форд25. Мерседес465.Мерседес105.Мерседес96. Пежо316.Пежо26.Пежо37. Рено227.Рено67.Рено18. 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р.Всего279,00р.Всего295,00р.Всего285,00р.
На Листе 4 используя консолидацию, подсчитайте Итоги за 3 месяца для данных, расположенных на трех разных листах.
Лабораторное занятие № 19
Совместное использование Excel и Word
1. Выделите таблицу предыдущей лабораторной работы и выполните команду Правка - Копировать. Сверните окно Excel .
2. Загрузите Word и выполните команду Правка - Специальная вставка. Укажите, что вы связываете Лист Microsoft Excel. 3. Дважды щелкнув по таблице запустите Excel и убедитесь в том, что изменяя таблицу в Excel меняется автоматически таблица и в Word. Проверочная работа по Microsoft Excel
Вариант 1.
Задание 1 . Создать таблицу
ОтделФамилияЗарплатаЭкономическийХанкин Г.Г.1500КадровГуськов Г.Г.2500Ценных бумагТулеев Т.Т.1200ОперчастьМашков А.Д.1000ЭкономическийКотик П.Л.800КадровМедведева П.Р.950Ценных бумагНоркин О.Ж.1350ОперчастьКоркин Е. З.2000КадровРябина Н.З.1500ЭкономическийБерезкина Л.Х.2500Ценных бумагЗубов А.В.1300ОперчастьБобров К.У.1550Скопировать таблицу на другой рабочий лист. На первом листе посчитать промежуточные итоги (фонд заработной платы) по каждому отделу. На втором создать сводную таблицу.
Задание 2. Выполнить консолидацию. На рабочих листах с именами "январь", "февраль", "март" приведены фамилии торговых агентов и количество сделок, которые они совершили в течение месяца. Построить сводку за первый квартал.
ЯнварьФевральМартФамилия СделкиОбъемФамилияОбъемСделкиФамилияСделкиОбъемИванов6250Сидоров2005Иванов12200Антонов12430Иванов2208Сидоров10300Медведев7180Антонов8150 Медведев6220
Задание 3. Создать таблицу "Наименование товаров на складе"
КатегорияНаименование товараОбщая стоимостьМолочные продуктытворог1562Молочные продуктымолоко8697Кондитерские изделияпеченье12562Мясные продуктыколбаса "Одесская"178954Рыбные продуктыконсервы12564Молочные продуктысырки154687Кондитерские изделияконфеты "Пташка"146522Мясные продуктыКотлеты14566Рыбные продуктыкрабовые палочки154477Посчитать промежуточные итоги по категориям.
Вариант 2.
Задание 1. Создать таблицу.
Сведения о сдаче сессии
ФакультетКурсКоличество
студентовИз них
"неудовл".Экономический120020Финансы и кредит215022Исторический310015Финансы и кредит49010Исторический5805Экономический210014Исторический115023Экономический5904Исторический21203Исторический41106Финансы и кредит120030Экономический48512Финансы и кредит5938Экономический315611Финансы и кредит312210Скопировать таблицу на другой рабочий лист. На первом листе сосчитать промежуточные итоги по каждому факультету, показывающие общее количество студентов и количество неудовлетворительных оценок. На другом создать сводную таблицу, показывающую количество неудовлетворительных оценок на факультетах.
Задание 2. Сведения о поступлении товаров на склад
На рабочих листах с именами "январь", "февраль", "март" приведены товары, которые поступили на склад. Построить сводку за первый квартал.
ЯнварьФевральМартТоварСколько
завезлиТоварСколько
завезлиТоварСколько
завезлиСтулья266Шкафы28Кресла56Столы112Жалюзи21Диваны14Шкафы27Стулья156Столы42 Жалюзи15
Задание 3. См. задание 3 варианта 1.
Раздел V. Решение экономических задач
Лабораторное занятие № 20
Экономические расчеты
Расчет единого налога с совокупного дохода
Создайте таблицу.
ПоказателиДанные1.Валовая выручка с учетом НДС4860,002.Затраты, исключаемые при определении Совокупного дохода - всегов том числе:а) стоимость использованных в процессе производства товаров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]1234567350010005000.........
Завести в графу 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)/Т
Н=Р*Т*К
Показатели эффективности использования основных фондов
Фо , Фв, Фё
Фо - фондоотдача
Фв - фондовооруженность Фё - фондоемкость Выручка
(тыс.руб.)Среднесписочная численность рабочих (чел.)Среднегодовая стоимость основных фондов (тыс.руб)ФоФвФё2600022584000.........
Для расчета использовать формулы:
Фо = В/ср.год.ст-сть ОФ
Фв = ср.год.ст-сть ОФ/ср.спис.числ.рабочих
Фё = 1/Фо
Определение годовой доходности акции
Покупная цена акции (руб.)Продажная цена акции (руб.)Затраты по приобретению акции (руб.)Дивиденды (руб.)Период
(дни)Годовой доход акции (руб.)22003500100300360
где
S - цена продажи В - цена покупки акции
D - дивиденты по акциям
С - затраты по приобретению (вознаграждения брокера, комиссия биржи)
t - период ( в днях), за который получен доход.
Расчет суммы амортизационных отчислений
[Б]Стоимость введенных основных фондов (руб.) [CBB]Стоимость выбывших основных фондов (руб.) [CB] [HA]ССРВВССРВЧ1Ч2Сумма амортиз. Отчислений 123456789101112123456789101112 250000 15000 5000 10 [Б] - балансовая стоимость основных фондов на начало года (руб.)
[HA] - норма амортизации (%)
[ССРВВ] - среднегодовая стоимость вводимых основных фондов (руб.)
[ССРВ] - среднегодовая стоимость выбывших ОФ (руб.)
[Ч1] - число полных месяцев после введения ОФ
[Ч2] - число полных месяцев после выбытия ОФ
А - сумма амортизационных отчислений (руб.)
ССРВВ=СВВ*Ч1/12
ССРВ=СВ*Ч2/12
А = (Б+ССРВВ-ССРВ)*НА/100
Лабораторная работа 21. Показатели производства
Создайте таблицу и заполните ее экспериментальными данными.
ABCDEFG1Показатели производства2 3 4 Месяцы План выпускаФактически выпущеноПроцент выполнения планаВыполнено в % к фактически выпущенному за год5131.01.99январь23402000 6228.02.99февраль32003200 7331.03.99март28003000 8430.04.99апрель30003100 9531.05.99май31003200 10630.06.99июнь25002400 11731.07.99июль26002800 12831.08.99август30003200 13930.09.99сентябрь32003200 141031.10.99октябрь30003100 151130.11.99ноябрь28003000 161231.12.99декабрь32003300 17 18Итого за год 19 20 Максимально за месяц 21Минимально за месяц 22 В среднем за месяц Этап 1. Расчет и анализ итогов работы предприятия.
1. Воспользовавшись кнопкой Автосумма, определите Итог за год для столбцов План выпуска, Фактически выпущено.
2. Процент выполнения плана определяется как Фактически выпущено поделить на План выпуска. Введите соответствующую формулу.
3. Для данных в столбце Процент выполнения плана установите процентный формат, используя кнопку на панели инструментов.
4. Итог за год для столбца Процент выполнения плана определяется как Фактически выпущено за год поделить на План выпуска за год. Введите соответствующую формулу.
В диапазоне ячеек G5:G16 предстоит вычислить для каждого месяца его долю (в процентах) в годовом выпуске, которая вычисляется как отношение выпущенного в каждом месяце к выпущенному за год. Так как во всех формулах диапазона в качестве делителя выступает одна и та же ячейка (сумма за год), то в формуле адрес этой ячейки должен быть задан в виде абсолютной ссылки, чтобы он не изменялся при копировании формулы. 5. Выделите ячейку G5 и выполните автоматическое заполнение формулами диапазона ячеек G5:G16, используя маркер заполнения. Установите процентный формат для диапазона G5:G16.
6. В ячейках E20, Е21 и Е22 с помощью Мастера функций постройте формулу.
7. Выделите диапазон ячеек Е20:Е22 и скопируйте его на диапазон F20:F22, используя маркер заполнения.
8. Для диапазона F20:F22 установите процентный формат.
9. Сохраните таблицу.
Этап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. Выполните остальные шаги мастера диаграмм. Отредактируйте диаграмму.
Лабораторная работа 22. Личный бюджет
Этап1. Личный бюджет за январь
Создайте таблицу и введите в нее экспериментальные данные.
1. В ячейке В8 постройте, используя кнопку Автосумма, формулу =СУММ(В5:В7).
2. В ячейке В15 постройте формулу =СУММ(В10:В14).
3. В ячейке В16 наберите формулу для вычисления конечного баланса =В3+В8-В15.
4. Переименуйте рабочий лист с данными за январь, присвоив ему имя Январь. Для этого дважды щелкните по ярлыку.
5. Сохраните документ.
Этап 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.
Для каждого месяца и в целом и за полгода постройте круговую (кольцевую) диаграмму:
a) Доля статей доходов в суммарном доходе.
b) Доля статей расходов в суммарном расходе.
Упражнение №2.
Решите задачу, разместив данные и результаты расчетов в таблице Личный бюджет за полгода на одном рабочем листе.
Упражнение №3.
Для таблицы упражнения №2 постройте:
a) график Баланс сбережений ежемесячных накоплений (конечного баланса);
b) гистограмму Баланс денежных средств с указанием для каждого месяца года имеющихся средств в начале и в конце месяца (начального и конечного баланса).
Лабораторная работа 23. Доходность акций
Вам необходимо подготовить отчет о работе АО "Пирамида". Для этого подготовьте следующую таблицу:
Пятый столбец таблицы содержит формулы для расчета чистой прибыли от продаж (ЧПП) акций. Для текущего года формула вычисления ЧПП выглядит так:
ЧПП=(ЦАТ-ЦАП+ПП)/ЦАП
где ЦАТ - цена акций в этом году, ЦАП - цена акций в прошлом году,
ПП - прибыль по акциям в прошлом году.
Заполните столбец Е соответствующими формулами.
Упражнение№1
Дополните таблицу итоговыми строками для вычисления среднего, минимального и максимального значения для каждого из показателей.
Упражнение№2
Постройте гистограмму для столбцов "Прибыль по акциям" и "Дивиденды".
Упражнение№3
Постройте круговую диаграмму для показателя "Дивиденды".
Упражнение№4
Постройте смешанную диаграмму для показателей "Прибыль по акциям" и ЧПП.
Лабораторное занятие № 24
Решение задач оптимизации
Цель работы: Знать, как использовать программу Excel для решения сложных задач оптимизации. Научиться формулировать условия задачи табличным образом, формировать ограничения, которым должно удовлетворять решение, и производить поиск оптимального набора переменных.
Задача. Завод производит электронные приборы трех видов(прибор А, прибор В и прибор С), используя при сборке микросхемы трех видов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей:
Прибор АПрибор ВПрибор СТип 1251Тип 2204Тип 3211
Стоимость изготовленных приборов одинакова.
Ежедневно на склад поступает 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. Сохраните рабочую книгу.
Лабораторное занятие № 25
Решение задачи оптимизации расходов предприятия
Цель работы. Научиться использовать программу Excel в задачах оптимального уравнения, а также формулировать условия задачи табличным образом, формировать ограничения, которым должно удовлетворять решение, выполнять поиск оптимального варианта. Мы поняли, что найти нужное решение подбором крайне сложно даже для самых простых задач.
Задача. Предприятие работает по недельному графику, требующему разного числа работников в разные дни недели. Необходимое число работников приведено в таблице.
ПонедельникВторникСредаЧетвергПятницаСубботаВоскресенье13141618222019 Можно использовать сотрудников с пятидневной рабочей неделей (выходные - любые два дня подряд, недельная заработная плата - 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.
По ней рассчитывается число работников, занятых в понедельник. Элементы абсолютной адресации использованы для того , чтобы формулу можно было копировать.
8. Методом заполнения скопируйте только что введенную формулу в ячейки диапазона Е12 - J12.
9. В ячейку В12 введите формулу =СУММ(В2:В8)+СУММ(В9:В10)*1,4. Для ввода имени функции используйте строку формул или Мастер формул. По этой формуле вычисляется "приведенное" число сотрудников с учетом увеличенной заработной платы при шестидневной рабочей недели.
10. В ячейку В16 введите формулу для исчисления итоговых расходов на заработную плату за неделю:=В15*В12. Именно это значение необходимо свести к минимуму.
11. Запустите надстройку Поиск решений (Сервис Поиск решения). Если пункт Поиск решения в меню отсутствует , значит , необходимо подключить эту надстройку, дав команду Сервис Надстройки и установив соответствующий флажок.
12. В поле Установить целевую ячейку выберите ячейку В16.
13. Для переключателя Равной выберите вариант минимальному значению.
14. Щелкните на поле Ограничения и затем- на кнопке Добавить .
15. Щелкните на поле Ссылка на ячейку и выберите диапазон В2:В10. В качестве ограничения выберите вариант цел. Щелкните на кнопке Добавить.
16. В поле Ссылка на ячейку выберите диапазон В2:В8. Выберите операцию >= и значение 1 в поле Ограничение. Аналогичным образом для диапазона В9:В10 выберите значение >=0.
17. В поле Ссылка на ячейку выберите диапазон D12:J12. Выберите операцию >=. В поле Ограничение выберите диапазон D13:J13. Этим задается ограничение на минимальное число работников . Щелкните на кнопке ОК. 18. Щелкните на кнопке Выполнить , чтобы провести поиск оптимального варианта. По окончании расчета щелкните на кнопке ОК, чтобы сохранить найденное решение.
19. Сохраните рабочую книгу.
2
43
Документ
Категория
Рефераты
Просмотров
1 364
Размер файла
708 Кб
Теги
excel, лаб
1/--страниц
Пожаловаться на содержимое документа