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

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 2003 и до 255 в Excel 2007. В качестве аргументов м.б. использованы числа, текст, логические значения (истина или ложь), массивы, значения ошибок (например, Н/Д), ссылки, выражения, другие функции.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ЕСЛИ, ИСТИНА, ЛОЖЬ, И(), ИЛИ(), НЕ().

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

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

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

№ варианта	Значения
№ варианта	a	b
		0,5
		14,36
		0,5
		11,05
		0,151
		1,31

		12б21
		0,521
		12,11
		20,01
		0,707
		2,712
		19,03
		300,1

Используя команду Формулы/Зависимые ячейки или Влияющие ячейки, представить на экране связи между различными ячейками, например

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

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

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

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

Ввести в диапазоны A1:C5, B3:D6 целые числа. Подсчитать сумму чисел, общих для данных интервалов (использовать знак адресной операции «пробел» и функцию СУММ()).
В ячейки A1:A4 ввести разные тексты. В ячейках Β1:B4 определить количество символов в каждом тексте.
В ячейки С1:С4 ввести разные тексты. В ячейках D1:D4 для каждого текста из ячеек С1:С4 вывести по три символа слева, а в ячейках Е1:Е4 – по два символа справа.
Заполнить 6 любых ячеек числами. Подсчитать количество ячеек, в которых находится число 0.
Определите средствами Excel, какой код соответствует первому символу вашего имени?
В две ячейки введите два текста. Определите средствами Excel, совпадают эти тесты или нет?
Проверить результаты следующих логических выражений: И(2+2=4; 2+3=5), ИЛИ(1+1=1; 2+2=5).
Составить таблицу истинности для логических функций И, ИЛИ, НЕ.

Вопросы на защиту:

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

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

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

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

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

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

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

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

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

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

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

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

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

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