Geum.ru

Запуск программы Двойной щелчок мыши в области пиктограммы excel

Вид материалаКнига

Содержание


Лабораторная работа №3
Лабораторная работа №4
F2 (редактирование), поставить кур­сор в крайнюю левую позицию (клавиша Ноmе)
Дополнительную информацию по используемым функциям можно полу­чить, используя справочную систему.
Лабораторная работа №5
Заготовка для расчетов на рабочем листе приведена в таблице.
Использование имен ячеек в формулах
Форматирование ячеек
Формат — Ячейки
Дополнение таблицы
Исследование зависимостей
Сообщения об ошибочных данных
Скрытие строк (столбцов)
Защита листа
Ограничение ввода
Подбор параметра
Установить в ячейке
Лабораторная работа №6
Ао "ливиз"
Ао "ливиз"
...
Полное содержание
Подобный материал:
1   2   3
если непосредственно поставить #Н/Д.

В EXCEL есть средства, которые не позволяют вводить недопустимые числа. Для подключения такой проверки необходимо использовать следующую процедуру: выбрать в меню Данные — Проверка. Появится окно с тремя вкладками: Пара­метры, где нужно выбрать тип данных, значение; Сообщение для ввода, где нуж­но поставить (или убрать) флажок «Отображать ...», дать заголовок и текст сооб­щения; Сообщение об ошибке, где нужно установить флажок «Выводить...», ука­зать тип действий при ошибке, ввести заголовок и текст сообщения.

Задача 4. Экзаменатор проверяет письменную работу, состоящую из пяти за­дач. За каждую задачу он проставляет оценку - целое число в диапазоне от 0 до 4. Иногда (в виде исключения) он может поставить нецелое число, например, 3,5. Введите в ячейки А4:Е4 порядковые номера задач, а в ячейку F4 - строку «Сум­ма». Экзаменатор вводит оценки в ячейки А5:Е5. В F5 автоматически должна вы­числяться сумма оценок. При переходе к ячейке подсказка не выводится, при не­верном вводе выводится предупреждение.

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

ЛАБОРАТОРНАЯ РАБОТА №3

Тема: Создание пользовательских форматов и применение их к ячейкам

Лабораторная работа предполагает создание рабочей книги с именем Лаб_3 и количеством листов, соответствующих количеству предлагаемых к решению задач. Процедура создания пользовательского формата начинается с выбора в меню: Формат — Ячейки — вкладка. Число - выбрать в окне Числовые форматы строку Все форматы - в текстовом поле Тип ввести нужную форматную строку.

Задача 1. В ячейке должна выводиться сумма в рублях (копеек заведомо нет). Отрицательные суммы выводятся красным цветом и со знаком минус. К числу до­бавляется символ денежной единицы р., отделенный от числа пробелом, размер которого равен ширине знака минус. Имеется разделитель тысяч.

Задача 2. В ячейку введено значение скорости 60 километров в час. Скон­струируйте пользовательский формат, чтобы указанное значение выводилось как 60 км/час.

Задача 3. В ячейке содержится число. Выводите его с двумя цифрами после десятичной точки (запятой) и с разделителем тысяч. Если число положительное, оно должно предваряться словом «Приход:», а если отрицательное - словом «Рас­ход:», если число равно нулю, то должно выводиться слово «Нет».

Задача 4. Какой пользовательский формат Вы предложите, чтобы отобра­жать числа с двумя цифрами после десятичной точки (запятой), а нуль не отобра­жать?

ЛАБОРАТОРНАЯ РАБОТА №4

Тема: Операции над текстовыми строками и операции с датами

Лабораторная работа предполагает создание рабочей книги с именем Лаб_4 и ко­личеством листов, соответствующих количеству предлагаемых к решению задач.

В ячейку можно ввести число, формулу, текст. Число и формулу можно пре­вратить в текстовую строку: нажать клавишу F2 (редактирование), поставить кур­сор в крайнюю левую позицию (клавиша Ноmе) и ввести одиночную кавычку. Если в ячейки All, А12, А13 ввести соответственно Иванов, Иван, Иванович, а в В11 - формулу =А11&" "&А12&" "&А13, то в ячейке В11 получим Иванов Иван Иванович.

Задача 1. Разнести содержимое ячейки в три разные - задача довольно сложная. Ниже приводится процедура ее решения по частям, а затем рекоменду­ется собрать эти части в одну формулу.



На рисунке приведен фрагмент книги с соответствующими формулами для разделения фамилии, имени и отчества. В используемых формулах видна после­довательность действий, поэтому дополнительного описания процедуры не требу­ется. В ячейки D17:D19 запишите формулы с использованием вложенных функций (не привлекая промежуточных ячеек, в примере это В13 :В15).

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

Задача 2. Используя данные предыдущей задачи, разместите в ячейке В20 формулу, которая выдаст результат: Иванов И.И.

Указание. Вместо функции ПСТР рекомендуется использовать функцию ЛЕВСИМВ.

Для работы с датами в ячейках excel рассмотрим несколько функций:

ДЕНЬНЕД(дата_в_числовом_формате;тип) - возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (Воскресенье) до 7 (Суббота).

МЕСЯЦ(дата_в_ числовом формате) — возвращает месяц, соответствующий аргументу дата_в_числовом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).

СЕГОДНЯ( ) - возвращает текущую дату в числовом формате. Числовой формат даты - это код дата-время, используемый в Microsoft Excel для вычисле­ний с датами и периодами времени.

Задача 3. Выполнить следующие действия, используя функции дат. До­полнительную информацию о функциях получить, используя Справку.

1. Ввести в произвольную ячейку произвольную дату и определить, каким днём недели она является.

2. Ввести в ячейку произвольную дату и определить номер месяца у введённой даты.

3. В ячейку ввести сегодняшнюю дату.

4. Определить, какой сегодня день недели.

5. В произвольной ячейке определить, в какой день недели Вы родились.

6. Вычислить свой возраст в днях и неделях.

Задача 4. Введите в столбец А даты от 1 марта 2002 года до 30 апреля 2002 года. Наложите на диапазон формат, чтобы даты, на которые приходятся суб­бота и воскресенье, отображались полужирным красным шрифтом.

ЛАБОРАТОРНАЯ РАБОТА №5

Тема: Пример проектирования расчетов на рабочем листе

Вычисление элементов треугольника. Даны три стороны треугольника а,Ь,с. Требуется вычислить его площадь по формуле Герона

S= , где p — полупериметр, p=


а также радиус вписанной r= окружности и радиус описанной R= окружности.

Решение. Три ячейки отведем для ввода сторон треугольника, отдельно вы­числим полупериметр, на его основе — площадь, а затем радиусы вписанной и описанной окружностей. Можно выбрать произвольные ячейки, можно придержи­ваться адресов и имен, используемых в пособии. Рекомендуется столбец или строку отвести под обозначения величин. Переименуйте рабочий лист, дайте ему имя Тре­угольник (правая кнопка мыши на ярлыке листа) и выберите в меню пункт Пере­именовать.

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

В

С

1

Стороны треугольника

2

а

3



3

b

4



4

с

5



5







6

Р

6



7







8

S





В ячейку В6 ввести формулу расчета полупериметра =(В2+ВЗ+В4)/2.

Использование имен ячеек в формулах

В ячейку В8 нужно ввести формулу Герона. Чтобы упростить ввод, дадим имена ячейкам В2, ВЗ, В4 и В6. Для этого выделим блок А2:В6 и выберем меню Вставка — Имя — Создать. Excel предложит вариант «в столбце слева», т.е. взять в каче­стве имен для ячеек В2:В6 текстовые строки, хранящиеся в ячейках А2:А6. На­жмите ОК. Теперь, выделяя ячейку В2, можно увидеть не адрес, а имя ячейки. Об­ратите внимание, что для ячейки В4 имя не с, а с_. Это связано с тем, что в Excel имена с и r зарезервированы для колонки и ряда соответственно. Поэтому Excel ввел в имя символ подчеркивания. Введите в ячейку форму­лу =корень(р*(р-а)*(р-b)*(р-с_)). Если не использовать имена ячеек, формула выглядела бы следующим образом:

=корень(В6*(В6-В2)*(В6-ВЗ)*(В6-В4)), что намного труднее для воспри­ятия.

Форматирование ячеек

Эта процедура придает таблице более читабельный вид. Для ее выполнения воспользуемся командными кнопками панели инструментов. Выровнять названия величин по правому краю. Введите длину стороны а равную 2. Тогда значение S = 3,799671. Допустим, нам нужна точность три знака после точки, для этого есть следующие способы:

1. Выделить ячейку В8 и нажать командную кнопку «Уменьшить разряд­ность» до тех пор, пока число не приобретет нужный формат (3,800).

2. Выбрать в меню Формат — Ячейки, затем вкладку Число и задайте число­вой формат с нужным количеством разрядов после запятой.

ПРИМЕЧАНИЕ. «Внутренние» вычисления выполняются с прежней точностью, но число, отображаемое в ячейке, округлено до заданного количества разрядов.

Дополнение таблицы

Дополним таблицу вычислением радиусов вписанной и описанной окружно­стей. Дадим ячейке В8 имя S. Поскольку мы создаем имя для одной ячейки, можно воспользоваться более простым способом, чем описано выше.

Создание имени:
  • выделить ячейку В8;
  • в поле имен строки ввода ввести имя S;
  • нажать ENTER.

В ячейки D10 и F10 ввести г и R, а в Е10и G10 соответствующие формулы. Наложите на эти ячейки такие же форматы, как и в предыдущей лабораторной работе. Для этого воспользуйтесь командной кнопкой Формат по образцу на панели инструментов.



Результат показан в таблице.

Исследование зависимостей

Выделите ячейку G10 и выберите в меню Сервис — Зависимости — Влияю­щие ячейки. На экране протянутся стрелки от ячеек, содержащих данные для формулы в ячейке G10.

Исследуйте аналогичным образом зависимости для других ячеек. Уберите стрелки соответствующей командой меню. Удобно работать с помощью панели кнопок «Зависимости». Выведите на экран панель, изучите работу кнопок этой па­нели на рассмотренном примере.

Задайте длину стороны а равную 10. В ячейке с результатом появится сооб­щение об ошибке #ЧИСЛО! Дело в том, что стороны 10, 4, 5 не образуют тре­угольника. При вычислении площади под корнем получается отрицательное число. Выделите ячейку G10 и выберите в меню Сервис — Зависимости — Источник ошибки (или нажмите соответствующую кнопку панели «Зависимости»). Вы на­глядно увидите, за счет каких влияющих ячеек получен неверный результат. Убери­те стрелки и закройте панель «Зависимости».

Сообщения об ошибочных данных

Таблицу нужно переделать для того, чтобы пользователь мог получать со­общения, почему не могут быть вычислены S, r, R, а в ячейках с результатами вы­числений г, R, ничего не должно выводиться. Будем вычислять отдельно значение подкоренного выражения и определять его знак. Если оно положительно, будем вычислять S, r, R, если нет, то в ячейке В8 выведем текстовую строку «Это не тре­угольник!», а в ячейках ЕЮ и G10 выведем пустые строки.

Для этого выполним следующие действия: перетащим содержимое ячейки В8 в ячейку В7. Отредактируем формулу в В7, убрав КОРЕНЬ. В ячейке останется формула =р*(р-а)*(р-Ь)*(р-с). Теперь имя S имеет ячейка В7. Вновь дадим имя S ячейке В8 (обратите внимание, что ссылка на ячейку должна быть абсолютной ($В$8)). В В8 разместим формулу: =Если(В7>0;КОРЕНЬ(В7);"Это не треуголь­ник"), а в E10 разместим формулу =Если(В7>0;S/р;""). Аналогично измените формулу в G10.

Скрытие строк (столбцов)

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

Строки можно скрыть, используя следующий прием: выделить в левой ад­ресной полосе (содержащей номера строк) строки 6 и 7 и в контекстном меню вы­брать Скрыть. Если Вы захотите показать строки, нужно выделить 5-ую и 8-ую строки и в контекстном меню выбрать Отобразить. Аналогично можно скрывать и показывать столбцы.

Защита листа

Чтобы предохранить таблицу от непреднамеренной порчи неопытным поль­зователем, нужно защитить рабочий лист. Предварительно нужно «объявить без­защитными» ячейки с исходными данными. Выделите ячейки В2:В4, выберите в меню (или нажмите клавиши Ctrl+1) Формат — Ячейки — вкладка Защита и снимите флажок «Защищаемая ячейка». Выберите в меню команду Сервис -Защита — Защитить лист. Попробуйте теперь ввести данные в ячейки листа вне диапазона В2:В4. После этого снимите защиту листа.

Ограничение ввода

Разрешите пользователю вводить только положительные длины сторон тре­угольника. Для этого выделите диапазон ячеек и выберите в меню Данные— Проверка — вкладка сообщение при вводе — ввести текст сообщения, напри­мер, «введите положительное число».

Подбор параметра

Итак, вычислен радиус описанной окружности по трем сторонам треугольника. Excel позволяет решить и обратную задачу: по заданному R вычислить а. Формул для этого на рабочем листе достаточно. Например, хотим определить величину а при R=3.

Выделим ячейку G10 и выберем в меню Сервис — Подбор параметра. В выведенном диалоговом окне поле Установить в ячейке уже содержит адрес G10. В поле Значение введем 3, в поле Изменяя значение ячейки введем В2 (сторона а). После чего нажимаем ОК. Выводится новое окно Результаты подбора параметра. С содержимым этого окна разобраться самостоятельно.

Постарайтесь определить величину а еще точнее. Выберите в меню Сервис __Параметры — вкладку Вычисления. На вкладке имеется поле ввода Относи­тельная погрешность. Задайте это значение равным 0,00001. Повторите подбор па­раметра а для R=3. Единственное ли значение а соответствует R=3? На этот во­прос изложенный метод подбора параметра ответа не дает.

Задание для самостоятельной работы

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

1. Скопируйте лист Треугольник в новую рабочую книгу и дайте ей новое имя.

2. Расположите ячейки с величинами радиусов друг под другом и дайте им общий заголовок.

3. Вычислите утлы треугольника, например, для угла А:

A=arccos, и т.д. (по теореме косинусов),

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

4. Поместите блоки ячеек для радиусов и углов в рамки.

5. Посмотрите зависимости.

6. Исправьте таблицу. Пусть надпись «Это не треугольник» выводится в соответствующей ячейке (в примере С4) крупным шрифтом и красным цветом, а в ячейке В8 в этом случае ничего не выводится.

Зачетное задание

Дан прямоугольный параллелепипед со сторонами а, в, с. Вычислить:
  • объем V=abc;
  • площадь поверхности S=2(ab+bc+ac);
  • длину диагонали d =;
  • угол между диагональю и плоскостью основания  = arctg;
  • угол между диагональю и боковым ребром  = -;
  • объем шара, диаметром которого является диагональ Vш = ;


Задание выполнить по образцу рассмотренного примера (по лабораторным работам) на вычисление элементов треугольника: области входных и выходных данных должны располагаться на рабочем листе отдельно, все входные и выход­ные элементы должны иметь названия, расположенные в соседних ячейках. На весь рабочий лист, кроме входных данных, наложить защиту.

ЛАБОРАТОРНАЯ РАБОТА №6

Тема: Формирование склада товаров от поставщиков и учет прибыли от реализации
  1. Оприходовать товароматериальные ценности, указанные в таблице, от поставщиков, перечисленных в таблице, в количестве и по цене, указанной в таблице. Дату поступления товара ставить с разницей 1 -2 дня от текущей даты.
  2. Ввести наценку: на табачные изделия — 25%, на вино-водочные изде­лия (и пиво) — 35%, на остальные продукты — 20%.
  3. Подсчитать общую сумму закупок от каждого поставщика и оценить графически долю каждого поставщика в общем объеме товаров (по количеству и по стоимости).
  4. Подсчитать общую стоимость имеющегося товара (по цене реализа­ции), предполагаемую прибыль (как разницу стоимости реализации товара и стои­мости поступившего товара). При оценке прибыли учесть ситуацию с реализацией товара, т.е., если прибыль есть (положительная разница), она должна быть синего цвета, если есть убыток (разница отрицательная), значение должно быть красным и без знака минус. Для ситуации, когда нет ни прибыли, ни убытка (разница равна ну­лю) - значение должно быть равно «О» обычного черного цвета.
  5. Реализовать товар со склада, используя два способа:
    • следуя условной схеме: за три дня (или меньше) с момента поступления то­вара реализовано 30% товара; за семь дней (или меньше) — 70%, а более 7 дней — весь товар реализован;
    • оформив заявку на любой товар в любом количестве, используя шаблон За­каз, выставив счет, используя шаблон Счет и по результатам оплаты учесть коли­чество проданного товара на листе Склад.
  6. Подсчитать общую прибыль от реализации на конкретный день (возможно, не весь товар будет реализован до этого дня) и чистую прибыль от реализации, которая составляет 25% от общей прибыли.
  7. Оценить графически долю чистой прибыли от реализации вино-водочных изделий.




При­знак
Поставщик
Наименование товара
Един. измер.
Количество
Цена за един.

НАЛ

АО "Вина Грузии"

Вино "Алазанская долина"

Шт
60
49,50

НАЛ

АО "ЛИВИЗ"

Водка "Синопская"

шт
80
76,00

ПР

АОЗТ "Система"

Молоко сухое, весовое

кг
100
26,00

ПР

АОЗТ "Система"

Мука пшеничная

кг
500
22,00

ПР

АО "Комбинат"

Маргарин

шт
100
7,70

НАЛ

АО "Балтика"

Пиво "Балтика-3"

шт
200
14,80

ТАБ

АО им. Урицкого

Сигареты "Космос"

шт
50
12,00

ПР

АО "Комбинат"

Чай зеленый, весовой

кг
30
40,00

ПР

АО "Комбинат"

Чай черный, весовой

кг
30
52,00

ПР

АО "Комбинат"

Шоколад "Тройка"

шт
300
15,40

НАЛ

АО "Вина Грузии"

Вино "Ркацители"

шт
80
52,50

НАЛ

АО "Вина Грузии"

Вино "Хванчкара"

шт
60
71,50

НАЛ

АО "ЛИВИЗ"

Шампанское "Советское"

шт
240
77,00

ПР

АОЗТ " Система"

Изделия макаронные

кг
200
13,40

ПР

ф-ка POT ФРОНТ

Конфеты "Желейные"

кг
240
73,00

ПР

ф-ка РОТ ФРОНТ

Какао фасованное

шт
72
47,50

ПР

АО "Комбинат"

Лосось консервированный

шт
160
14,60

ПР

АО "Комбинат"

Масло подс.рафин. фасов.

шт
80
27,00

ПР

АО "Комбинат"

Масло оливковое, фасованное

шт
50
42,00

ТАБ

АО им. Урицкого

Папиросы "Беломорканал"

шт
1000
11,10

ПР

АОЗТ " Система"

Сахар весовой

кг
500
13,20

ПР

АО "Комбинат"

Чай гранулированный, фасов.

шт
200
12,80

НАЛ

АО "ЛИВИЗ"

Водка "Сибирская"

шт
400
64,00

НАЛ

АО "Вена"

Пиво "Невское" баночное

шт
700
13,90