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

8

EXCEL

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

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

Краткие теоретические сведения

Построение формул в Excel.

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

• формула всегда начинается со знака « = » ;

• формула активной ячейки отображается в строке формул;

• основными элементами формул могут быть: константы, ссылки на ячейки, имена, операции (арифметические, сравнения, операция с текстом &), функции.

Константы – числовые и текстовые величины. Текстовые заключаются в кавычки “Текст”.

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

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

По умолчанию ссылки в формулах рассматриваются как относительные. Это означает, что при копировании формул действует правило относительной ориентации ячеек, которое заключается в следующем: адреса в ссылках автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии. Пусть, например, в ячейке В2 имеется ссылка на ячейку А3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку D3 ссылка будет продолжать указывать на ячейку, расположенную левее и ниже, в данном случае на ячейку С4.

При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать кл F4. Элементы номеров ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательном нажатии кл. F4 номер ячейки А1 будет записываться как $А$1, А$1, $А1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный.

Внешние ссылки – это ссылки на ячейки других листов, Например, Лист3!С5.

Множественные ссылки разделяются точкой с запятой, например: A4; C5; C10:E20.

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

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

• при изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте – определении имени.

Назначение имени – Выделить ячейку или диапазон / дать команду Вставка /Имя/ Присвоить.

Вставка имени в формулу – Установить место вставки в формулу / дать команду Вставка/ Имя/ Вставить.

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

Операции.

Арифметические - +, -, - ( унарный минус), *, /, % (процент), ^ (возведение в степень). Результатом выполнения арифметической операции всегда будет число.

Сравнения - =, >, <, >=, <=, <> (не равно). Результатом выполнения операций сравнения является логическое значение – «ложь» или «истина».

Текстовый оператор & (амперсант) служит для объединения последовательностей символов в одну последовательность. Результатом выполнения выражения “Результата” & “ расчета” будет “результаты расчета”.

Функции.

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

имя (арг1, арг2, …).

Число аргументов может достигать 30. В качестве аргументов м.б. использованы числа, текст, логические значения (истина или ложь), массивы, значения ошибок (например, Н/Д), ссылки, выражения, другие функции.

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

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

Математические функции.

АВS(), LOG10(), TAN(), EXP(), LN(), SIN(), COS(), КОРЕНЬ(), ФАКТР(), СУММ(), СУММЕСЛИ(диапазон; критерий; диапазон_суммирования) – суммирует ячейки, заданные указанным условием, ГРАДУСЫ(радианы), ОРКРУГЛ(число, чосло_разрядов), ОСТАТ(число, делитель), ПРОИЗВЕД(), РАДИАНЫ (угол), СТЕПЕНЬ() и др.

Статистические функции

СЧЕТ (знач1; знач2; ….) – подсчитывает только числа в списке аргументов, хотя в качестве аргументов м.б. данных других типов, число аргументов м.б. до 30;

СЧЕТЕСЛИ (диапазон; критерий) – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию;

СЧИТАТЬПУСТОТЫ (диапазон) – подсчитывает количество пустых ячеек в диапазоне;

СРЗНАЧ() и многие другие.

Текстовые функции

ДЛСТР (текст) – возвращает количество знаков в текстовой строке;

КОДСИМВ (текст) – возвращает числовой код первого символа в строке;

ЛЕВСИМВ (текст; количество_символов) – возвращает указанное количество символов с начала строки;

ПРАВСИМВ (текст; количество_символов) – возвращает указанное количество символов с конца строки;

СИМВОЛ (число) – возвращает символ с заданным кодом;

СОВПАД (текст1; текст2) – проверяет идентичность двух строк текста, и возвращает значение ИСТИНА или ЛОЖЬ, прописные и строчные символы различаются;

СЦЕПИТЬ (текст1; текст2; …) – объединяет несколько текстовых строк в одну;

ТЕКСТ (значение; формат) – форматирует число и преобразует его в текст.

Логические функции

ЕСЛИ(лог_выражение; знач_если_истина; знач_если_ложь), ИСТИНА, ЛОЖЬ, И(), ИЛИ(), НЕ().

Отображение формул. Командой Сервис/Параметры/Вид/флажок «Формулы» можно включить режим, когда в ячейках будут отображаться не результаты вычисления по формулам, а сами формулы:

Управление вычислениями.

Для организации управления вычислениями в Excel используются логические функции. Функции И, ИЛИ, НЕ необходимы для записи сложных логических условий. А основой для организации вычислений является функция ЕСЛИ.

Задания к лабораторной работе №2.

1. Вычислить значения функций y = f(a, b) для заданных a и b. При этом вычисление y производить посредством не менее чем трех формул с получением промежуточных значений

№ варианта		Значения
		a	b
1		4	0,5
2		2	14,36
3		10	0,5
4		2	11,05
5		3	0,151
6		4	1,31
7		2	200
8		1	12б21
9		3	0,521
10		2	12,11
11		1	20,01
12		3	0,707
13		3	2,712
14		2	19,03
15		4	300,1

Используя Панель инструментов «Зависимости» (или команду Сервис/Зависимости), представить на экране связи между различными ячейками, например

2. Выделить диапазон ячеек A1:F6. Ввести в диапазон постоянное значение 1. Затем очистить ячейки A1, B5, F2, C6, D3. Подсчитать

• количество пустых ячеек;

• количество заполненных ячеек;

• среднеарифметическое диапазона.

3. Выделить диапазоны A1:F10 и D7:K20. Ввести в диапазоны постоянное значение 1. Подсчитать сумму значений, общих для выделенных интервалов (использовать знак адресной операции «пробел» и функцию СУММ()).

4. В ячейки A1:A4 ввести разные тексты. В ячейках Β1:B4 определить количество символов в каждом тексте.

5. В ячейки C1:C5 ввести фамилии студентов. В ячейках D1:D5 вывести только те фамилии, в которых первый символ совпадает с буквой «А», а последний символ заканчивается на «о». Подсчитать их количество.

6. Проверить результаты следующих логических выражений: И(2+2=4; 2+3=5), ИЛИ(1+1=1; 2+2=5).

7. Составить таблицу истинности для логических функций И, ИЛИ, НЕ.

8. Вывести на экран содержимое ячейки В4, если она содержит число строго между 1 и 100 и сообщение «Значение вне интервала» в противном случае.

9. Предположим, что рабочий лист по расходам содержит в ячейках В2:В4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки С2:С4 содержат данные по планируемым расходам за те же периоды: 900, 900 и 925. Написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений «Превышение бюджета» («ПБ»).

10. Вычислить

11. Вычислить значение функции f(t), при заданных а, b, n , если значение аргумента t изменяется от t_min = a до t_max = b с шагом t = (b-a)/(n-1).

Вопросы

1. Какой синтаксис используется при написании формул в Excel?

2. Перечислите и кратко охарактеризуйте основные элементы формул в Excel.

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

4. Поясните, что такое относительные и абсолютные ссылки в Excel?

5. Поясните что такое внешние (трехмерные) и множественные ссылки в Excel?

6. Что такое имя? Как присваиваются имена в Excel? Каковы преимущества использования имен? Как имена вставляются в формулы? Какой ссылкой по умолчанию является имя?

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

8. Перечислите из каждой категории известные вам встроенные функции и объясните их назначение.

9. С помощью какой команды можно представить связи между ячейками рабочего листа Excel?

10. Допускаются ли вложения функций в Excel и каков максимальный уровень вложенности функций допускается в Excel?

11. Как осуществляются управления вычислениями в Excel? Приведите пример.

12. По заданной преподавателем формуле осуществите вычисления в Excel.

13. Как можно посчитать количество пустых ячеек? Количество заполненных ячеек? Среднее арифметическое для значений из нескольких ячеек?

14. Покажите на примере, как используется адресная операция «пробел»?

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

16. Объясните работу логических функций «И», «ИЛИ», «НЕ». Приведите примеры.

17. Объясните работу логической функции «ЕСЛИ». Приведите пример.

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