Методика и порядок проведения работы

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

Вычисления в электронных таблицах в MS Excel

(2 часа)

1. Цель и содержание: научиться производить экономические расчёты с рабочими листам в MS Excel с помощью встроенных функций.

Теоретическое обоснование

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

· возможность вставки в таблицы сложных формул и функций.

· гибкая система ссылок;

· возможность организации сценариев и итераций.

Вставка формул в таблицу

Простейший способ вставить в ячейку Excel формулу – набрать ее в строке формул. Если формула набрана и интерпретирована правильно, ее значение вычисляется. В строке формул тогда выводится текст формулы, а в ячейке – вычисленное значение.

Операторы . При наборе формулы важно знать, что формула обязательно должна начинаться со знака равенства (=). Если вы не введете этот символ, то вся остальная последовательность символов будет воспринята Excel как текст и, соответственно, отображена в ячейке. В формулах Excel применяется ограниченный и малочисленный набор операторов, которые можно объединить в четыре основные группы: арифметические операторы; операторы сравнения; текстовый оператор; операторы ссылок.

Арифметические операторы. Оператор сложения (+) складывает операнды, между которыми он находится; Оператор вычитания () вычитает второй операнд из первого; Оператор умножения (*) перемножает операнды, между которыми находится; Оператор деления (/) делит первый операнд на второй; Оператор процента (%) делит операнд на 100; Оператор возведения в степень (^) возводит операнд в степень; Среди операторов нет знака извлечения корня, но нужно помнить, что извлекать корни любой степени можно путем возведения в степень.

Операторы сравнения позволяют выполнить сравнение двух операндов, результатом которого является логическое значение ИСТИНА или ЛОЖЬ.

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

Текстовый оператор в Excel всего один — это & (амперсанд). Он служит для объединения (конкатенации) нескольких текстовых значений в одно

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

Операторов ссылок в Excel два – это оператор диапазона (:) и оператор объединения (;).

Относительные и абсолютные ссылки. Те ссылки, которые указывают на определенную ячейку по ее адресу (C6, D9), называются относительными. Если вы скопируете такую ссылку в другую ячейку, то адрес изменится. Он фиксирован относительно ячейки, в которой находится формула, и всегда будет указывать выше или ниже, правее или левее на одно и то же количество столбцов и строк.

Абсолютная ссылка — это ссылка, которая всегда указывает на одну и ту же фиксированную ячейку независимо от того, куда и каким образом ее копируют или перемещают. Если нужно, чтобы ссылка была абсолютной, то нужно при вводе ссылки в строку формул указать это при помощи знака доллара ($) перед каждой из координат ссылки, например, $A$1. Кроме того, можно применить этот знак только к одной из координат ссылки. Тогда часть ссылки станет относительной, а часть останется абсолютной, например, $A1 или A$1. В случае, когда делают абсолютной одну из координат ссылки, ссылка будет всегда указывать на один и тот же фиксированный столбец или же на одну и ту же фиксированную строку.

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

Ссылку в Excel можно установить не только на ячейку, но и на диапазон ячеек и на несколько диапазонов ячеек одновременно. Для этого используются специальные операторы – диапазона (:) и объединения (;).

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

· В таблице выделите ячейку (или диапазон ячеек, или несколько.

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

Ссылки на ячейки на других листах и в других книгах. Для того чтобы вставить ссылку на ячейку, которая находится в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде: «Имя_листа!Адрес_ячейки»

2.2 Функции Мастер функций.

В функциях заключена сила и простота вычислений в Excel. Вставка функций в лист Excel производится выбором в раскрывающемся списке Функции, который появляется после ввода знака равенства (=) в строке имен (рис. 1).

Рисунок 1. Всплывающий список функций.

Все функции, несмотря на их разнообразие, имеют одинаковый стандартный формат: имя функции и находящийся в круглых скобках перечень аргументов, разделенных точками с запятой. Например: =ОКРУГЛ(A2:A6;0).

Функцию можно ввести обычным путем, как и любое содержимое ячейки – с клавиатуры. Регистр при вводе функции не учитывается. Excel автоматически запишет имя функции прописными буквами. Для вставки функции Excel предоставляет в распоряжение пользователя Мастера функций., позволяющий вводить их в полуавтоматическом режиме и практически без ошибок.

Вызвать Мастера функций можно одним их следующих способов: выбрав команду Формулы кнопкой Вставка функции . М астер функций последовательно выводит два диалоговых окна. В первом выбирается функция (рис. 2), а во втором задаются аргументы (рис. 3).

Рисунок 2. Первое окно Мастера функций.

Рисунок 3. Второе окно Мастера функций

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

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

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

Копируются формулы так же, как и другие данные: выделить ячейку, выполнить команду ПРАВКА>Копировать (или комбинация клавиш Ctrl+C); выделить целевую ячейку (ячейки), то есть область, куда надо поместить копируемые данные; выполнить команду ПРАВКА>Вставить (или Ctrl+V). Важным при копировании и перемещении формул является преобразование содержащихся в них ссылок на другие ячейки. В определенных случаях адреса должны оставаться неизменными, а в других ситуациях необходимо, чтобы их пересчитали с учетом относительного изменения местоположения. В электронных таблицах поддерживается система относительных и абсолютных ссылок.

Значение ошибки. Если в ячейке содержится формула, результат которой программа не может правильно определить, то в ячейке будет отображено значение ошибки (табл. 1.).

Таблица 1. ЗНАЧЕНИЕ ОШИБКИ В РАБОЧЕМ ЛИСТЕ

Значение ошибки	Причина
#ДЕЛ/0!	Задано деление на ноль
#ЗНАЧ!	Указан неправильный аргумент или неправильный оператор
#ИМЯ?	Указано недопустимое имя
#Н/Д	Значение не указано
#ПУСТО!	Задана область пересечения двух диапазонов, которые не пересекаются
#ССЫЛКА!	Указана некорректная ссылка
#ЧИСЛО!	Ошибка при использовании/получении числа
######	Результат не помещается в ячейке, ширину ячейки необходимо увеличить

Методика и порядок проведения работы

3.1 Задание 1. Сформировать таблицу Ведомость (Лаб.раб.3) с использованием функций и выполните расчеты на листе Ведомость.

Задание. Вычисления по формулам. Автосумма . Выполните расчеты на листе Ведомость:

1. Выделите ячейки B4:G4 и нажмите на пиктограмму Σ (Автосумма) на панели инструментов. В ячейке H4 появится результат формулы. Установите курсор на ячейку с результатом. В строке формул вверху рабочей области окна должна быть видна формула =СУММ(B4:G4), по которой производился расчет.

2. Курсором черный крестик в правом нижнем углу ячейки Н4 произведите автозаполнение формулами ячеек H5:H7.

3. Аналогично заполните строку Итого.

4. В ячейку К4 столбца Заработано введите формулу =H4*J4 (Заработано =Всего часов*Стоимость часа).

Замечание 1. При вводе адресов ячеек в формулу не пользуйтесь клавиатурой, а щелкните левой клавишей мыши на нужную ячейку. Замечание 2. Появление символов ####### в ячейке означает, что результат формулы не помещается в ячейку и необходимо увеличить ширину столбца (2ЛМ на границе между буквенными обозначениями столбцов).

5. Произведите автозаполнение формулами ячеек K5:K7.

6. Введите в ячейку M4 столбца Премия за переработку формулу = I4*J4 для оплаты сверхурочных часов (Премия за переработку =Отработано сверх*Стоимость часа) и черным крестиком заполните формулами ячейки M5:M7.

7. В ячейку N4 столбца К выдаче введите формулу=K4-L4+M4( К выдаче =Заработано-Налоги+Премия за переработку).

8. Курсором черный крестик произведите автозаполнение формулами ячеек N5:N7.

9. Введите данные в ячейки J4:J7 столбца Стоимость часа, вставьте их из листа Список, для этого:

– установите курсор на J4 и нажмите на клавишу =,

– щелкните левой клавишей мыши по листу Список,

– щелкните на листе Список по ячейке D2,

– завершите ввод формулы, щелкнув левой клавишей мыши на галочку в строке формул. В ячейке J4 появится формула = Список!D2 ,

– произведите автозаполнение формулами ячеек J5:J7.

Задание Относительные, абсолютные ссылки. Имена ячеек. Прочитайте следующее пояснение:

Относительные адреса (в формулах – ссылки), например A1, B2, C3, изменяются при копировании и перемещении формул. Абсолютные адреса, например $A$1, $D$12, не изменяются при копировании и перемещении формул. Если ячейке присвоено имя, то при копировании и перемещении формул имя не изменяется. Сделать адрес абсолютным можно тремя способами: 1) установить курсор в строке формул на адрес ячейки и нажать функциональную клавишу F4 – появятся значки $; 2) впечатать значки $ с клавиатуры; 3) дать ячейке имя по команде Вставка – Имя – Присвоить.

Выполните следующие действия:

1. Задайте имена ячейкам B13 и B14: – установите курсор на ячейку B13,

– щелкните мышью на адрес ячейки в поле Имя (см. рис. 1, Лаб. Раб 8), – введите имя Подоходный, – нажмите Enter, – аналогично задайте имя Норма ячейке B14.

Замечание. Имя ячейки не должно содержать пробелов, ввод имени завершается нажатием на клавишу Enter.

2. Введите формулу в ячейки L4:L7 столбца Налоги: – установите курсор на ячейку L4, – наберите знак =, – щелкните мышью на ячейку K4, – наберите знак *, – щелкните мышью на ячейку B13, – завершите ввод формулы, щелкнув левой клавишей мыши на галочку в строке формул, в ячейке L4 появится формула =K4*Подоходный, – произведите автозаполнение ячеек L5:L7.

3. Присвойте диапазону ячеек K4:K7 имя Заработано_всего: – выделите диапазон ячеек K4:K7, – в поле Имя в строке формул напечатайте Заработано_всего, – нажмите Enter. – В ячейке K8 подсчитайте сумму:– установите курсор на ячейку K8, – нажмите на панели инструментов на пиктограмму Σ (Автосумма), – завершите ввод формулы, щелкнув левой клавишей мыши на галочку в строке формул, появится формула = СУММ (Заработано_всего).

Задание . Статистические функции СРЗНАЧ, МАКС, МИН Вычислите среднее значение часов, отработанных в январе. Для этого:

1. Щелкните мышкой по ячейке B9.

2. Выполните команду Вставка – Функция или щелкните на пиктограмму f _x . В появившемся окне Мастер функций выберите Категория – Статистические, найдите функцию СРЗНАЧ и нажмите ОК.

3. Выделите мышью диапазон ячеек B4:B7. В поле Число1 окна Мастер функций появится нужный диапазон. Снова нажмите ОК.

4. Курсором мыши Черный крестик (+) произведите автозаполнение ячеек C9:G9, чтобы вычислить среднее значение нагрузки преподавателей за февраль – июнь.

Аналогично вычислите максимум и минимум часов по месяцам.

Задание . Логическая функция ЕСЛИ

Допустим, что почасовик должен отработать норму – 400 часов. Переработка оплачивается дополнительно. Заполните столбец Отработано сверх с помощью логической функции ЕСЛИ.

Замечание. Функция ЕСЛИ имеет три аргумента. Первый аргумент функции – заданное логическое условие. Если оно при вычислении дает значение ИСТИНА, то функция возвращает значение, равное второму аргументу; если заданное логическое условие при вычислении дает значение ЛОЖЬ, то функция возвращает значение, равное третьему аргументу.

1. Установите курсор на I4.

2. Щелкните на f _x .

3. В появившемся окне Мастер функций выберите Категория – Логические, найдите функцию ЕСЛИ и нажмите ОК.

4. В поле Логическое выражение окна Мастер функций введите H4>=Норма (щелкайте мышью на нужные ячейки).

5. В поле Значение_если_истина введите H4–Норма.

6. В поле Значение_если_ложь введите цифру 0.

7. Нажмите OK, в строке формул в ячейке I4 увидите формулу =ЕСЛИ(H4>=Норма;H4–Норма;0).

8. Произведите автозаполнение формулами ячеек I5:I7.

Сделайте Предварительный просмотр листов Список и Ведомость. Если Ведомость не поместилась на одной странице, измените поля книги и ориентацию листов на Альбомную, снова сделайте Предварительный просмотр.

Измените Норму часов на 450 и посмотрите изменения в столбцах Отработано сверх и Премия.

На листе Список введите дополнительные данные (рис. 4)

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

Рисунок 4. Ставка.

Для этого:

1. Ячейкам G2, G3, G4 задайте соответственно имена Ставка_асс , Ставка_доц, Ставка_проф.

2. В ячейку D2 введите вложенные функции ЕСЛИ: =ЕСЛИ(C2<>"профессор";ЕСЛИ(C2<>"доцент";Ставка_асс;Ставка_доц);Ставка_проф)

Замечание. Значки < > означают не равно; >= означают больше или равно. Адреса и имена ячеек (C2, Ставка_асс и т. д.) не печатайте с клавиатуры, а 1ЛМ на соответствующую ячейку.

3. Переведите Зингеля в профессоры и посмотрите результат.

4. Измените ставку профессора в табличке Ставка – исходные данные и посмотрите изменения в столбце Ставка.

5. Закрасьте ячейки в диапазоне D2:D5 желтым цветом.

6. К ячейке D5 добавьте Примечание (Вставка – Примечание) и сделайте примечание видимым (Сервис – Параметры – Вид).

Замечание. Ячейка с примечанием имеет красный индикатор в правом верхнем углу.

7. Сравните полученный результат с образцом (см. рис. 5)

Рисунок 5. Автозаполнение таблицы.

3.2 Задание 1. Сформировать таблицу ОЦЕНКА КАЧЕСТВА ТОВАРОВ с использованием функций: ОКРУГЛ(математическая); ЕСЛИ(логическая); МИН, МАКС, СРЗНАЧ (статистические).

3.2.1 Рекомендации по выполнению:

1. Откройте программу MS Excel.

2. Создайте таблицу и заполните её исходными данными (рис. 6).

	A	B	C	D	E	F	G	H
1	Оценка качества товара
2	№	Наименование товара	Выдержка	Цвет	Запах	Стоимость	Средний бал	Градуиро-ванная оценка
3	1	Коньяк "Юбилейный"	96	89	99	99
4	2	Коньяк "Виктория"	92	87	88	86
5	3	Коньяк "Дачиа"	55	75	68	90
6	4	Коньяк "Солнечный"	68	95	99	62
7	5	Коньяк "Сюрпризный"	98	42	43	88
8	6	Коньяк "Молдова"	80	78	64	75
9	7	Коньяк "Белый аист"	48	30	79	50
10		Низшая оценка
11		Средняя оценка
12		Высшая оценка

Рисунок 6 Таблица ОЦЕНКА КАЧЕСТВА ТОВАРОВ

3. В первую ячейку столбца Средний бал введите формулу =ОКРУГЛ(СРЗНАЧ(C3:F3);0). С помощью метода перетаскивания размножьте формулу по столбцу ( G 3: G 9).

4. В первую ячейку столбца Градуированная оценка введите формулу

=ЕСЛИ(G3<60;1;ЕСЛИ(G3<70;2;ЕСЛИ(G3<85;3;ЕСЛИ(G3<94;4;5)))). С помощью метода перетаскивания размножьте формулу по столбцу (Н3:Н9);

5. В первую ячейку строки Низшая оценка введите формулу: =ОКРУГЛ(МИН(C3:C9);0) и размножьте по строке в диапазоне D 10:Н10.

6. В первую ячейку строки Средняя оценка введите формулу: =ОКРУГЛ( C РЗНАЧ(C3:C9);0) и размножьте по строке в диапазоне D 11:Н11.

7. В первую ячейку строки Высшая оценка введите формулу: =ОКРУГЛ(МАКС(C3:C9);0) и размножьте по строке в диапазоне D 12:Н12.

8. В результате получиться таблица изображенная на рисунке 7.

9. Сохраните таблицу.

Оценка качества товара
№	Наименование товара	Выдержка	Цвет	Запах	Стоимость	Средний бал	Градуированная оценка
1	Коньяк "Юбилейный"	96	89	99	99	96	5
2	Коньяк "Виктория"	92	87	88	86	88	4
3	Коньяк "Дачиа"	55	75	68	90	72	3
4	Коньяк "Солнечный"	68	95	99	62	81	3
5	Коньяк "Сюрпризный"	98	42	43	88	68	2
6	Коньяк "Молдова"	80	78	64	75	74	3
7	Коньяк "Белый аист"	48	30	79	50	52	1
	Низшая оценка	48	30	43	50	52	1
	Средняя оценка	77	71	77	79	76	3
	Высшая оценка	98	95	99	99	96	5

Рис. 7. Результирующая таблица ОЦЕНКА КАЧЕСТВА ТОВАРОВ

4 Аппаратура и материалы: IBM PC, табличный процессор MS Excel.

5. Содержание отчета и его форма: Форма отчёта письменная. Тема, цель лабораторной работы Описание выполнения работы. Продемонстрировать электронный вариант таблиц. Отчет предоставить в тетради или в виде распечатки.

Контрольные вопросы:

1. Способы ввода функции и категории функций в Excel.

2. Назначение и способы запуска инструмента Мастер функций.

3. Опишите создание вложенной функции.

4. Абсолютные и относительные ссылки на ячейки.

5. Перечислите числовые форматы ячеек и способы их установки.

6. Значение ошибки на рабочем листе. Причины и способы устранения.

7. Как задать имена ячеек?

8. Как провести вычисления с помощью статистические функции СРЗНАЧ, МАКС, МИН?

9. Как провести вычисления с помощью функции ОКРУГЛ (математическая); ЕСЛИ(логическая)?

Дата добавления: 2020-12-22; просмотров: 110; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!