6

Практические работы по Microsoft Excel (Часть 2)

Занятие 4 Использование стандартных функций в таблицах Excel

Цель работы: Изучить использование стандартных функций для выполнения вычислений в таблицах Excel, освоить работу с Мастером функций

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

1. Категории стандартных функций Excel. Синтаксис функций (имя, аргументы, тип результата). Особенности использования тригонометрических функций.

2. Правила ввода функций в формулу Excel. Использование Мастера функций и палитры функций.

3. Понятие вложенной функции. Сколько уровней вложения допускает Excel?

4. Использование имен диапазонов в качестве аргументов функций. Как присвоить/применить имя диапазона?

5. Как заменить формулы в ячейках на их значения? Как отобразить в ячейках формулы вместо значений?

6. Каковы наиболее распространенные коды ошибок и причины их возникновения?

7. Порядок использования команды Сервис/Зависимости для выявления источника ошибок.

8. Порядок использования команды Данные/Проверка для контроля вводимых данных.

Задания на практическую работу:

1. Загрузить Excel. Создать новую рабочую книгу из 6 листов: Матрицы, Математические функции, День рождения, Амортизация, Выбор значений, Ведомость.

2. На листе Матрица расположить матрицу A(3х4) (в ячейках B1:E3):

Используя математические функции для работы с матрицами, найти:

1. матрицу В=А*k, где k=3 (использовать формулу массива: выделить диапазон для матрицы В - B7:Е9, ввести формулу = B1:Е3*3, нажать Ctrl+Shift+Enter);

2. матрицу С=А+В (использовать формулу массива: выделить диапазон для матрицы С - B11:Е13, ввести формулу = B1:Е3+ B7:Е9, нажать Ctrl+Shift+Enter);

3. матрицу D=А^Т (использовать формулу массива: выделить диапазон для матрицы D - B15: D18, ввести формулу =ТРАНСП(B1:E3) нажать Ctrl+Shift+Enter);

4. матрицу E=A*D; 5) E^-1 (обратная матрица); 6)определитель матрицы Е.

Отобразить на листе формулы в ячейках (Сервис/Параметры/Вид, флажок Формулы). Оценить результат. Восстановить режим отображения значений.

Присвоить имена ячейкам с элементами матриц (выделить, Вставка/Имя, Присвоить А - B1:Е3).

Заменить в формулах ссылки диапазонов на имена (Вставка/Имя, Применить).

3. На листе Математические функции построить:

^- таблицу значений функции y=2sin(x-) +cos(x+/2), в диапазоне от 30 до 360, с шагом 10

- таблицу для расчета значения функции при начальных значениях x=25, y=3, z=8.:

Результат вывести с точностью 3 знака после запятой.

- таблицу Округление, содержащую число 73,26, округленное до десятых по правилам округления, до целого, до десятков в меньшую сторону, до десятков в большую сторону, до сотен (использовать функции ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ).

4. На листе День рождения построить таблицу, в которой выделить именинников на текущую дату, указав сообщение "День рождения сегодня". Использовать функции СЕГОДНЯ, ЕСЛИ, ДЕНЬ, МЕСЯЦ, И.

Ф.И.О.	Дата рождения	Именинник
Иванов И.И.	18.11.2000	=?
…	…	…

5. На листе Амортизация построить таблицу для расчета годовой амортизации станка стоимостью 45000$, если срок эксплуатации составляет 20 лет, а остаточная стоимость - 4800$. Формула:

_,

где A – годовая сумма амортизационных отчислений, S – балансовая стоимость, C – остаточная стоимость, T – срок эксплуатации.

5. На листе Выбор значений построить таблицу, позволяющую определить лидера по объему продаж и месяц, на который приходится максимальный объем продаж сотрудника. Использовать функции ИНДЕКС, ПОИСКПОЗ, МАКС.

Ф.И.О.	Январь	Февраль	Март	Лучший месяц
Иванов	50	80	70	=?
Петров	110	30		=?
Сидоров	90		200	=?
Лидер месяца	=?	=?	=?

Под таблицей сформировать по 3 текстовые строки вида (использовать &):

- Лидер за месяц - Фио.

- Фио - лучший месяц работы месяц.

5. На листе Ведомость построить таблицу для расчета среднего балла и суммы стипендии.

№	Фамилия И.О.	Оценки				Средний
		Математика	Физика	информатика	иностранный язык.	балл
1.	Иванов И.И.	5	4	4	5
2.	Новиков С.П.			3	4
3.	Петров П.П.	3	4	4	5
4.	Сидоров С.С.		4	4	5
	Сдали экзамен
	Не сдали экзамен
	Средний балл
	Сдали экзамен на:
	- отлично
	- хорошо
	- удовлетворительно

Для анализа успеваемости студентов группы использовать функции:

• =СЧЕТЕСЛИ(Интервал;”>=3”) - число студентов, сдавших экзамен по каждому предмету

• =СЧЕТЕСЛИ(Интервал;””) - число студентов, не сдавших экзамен по каждому предмету

• =СУММ(Интервал)/СЧЕТЗ(Интервал) - средний балл по каждому предмету;

• =СЧЕТЕСЛИ(Интервал; Оценка) - число студентов, сдавших предмет на "оценку" (3,4,5);

• средний балл каждого студента определить, используя функцию СРЗНАЧ и считая, что средний балл студентов, не сдавших хотя бы один экзамен, равен 0:

=ЕСЛИ(ИЛИ(C3=””;D3=””;E3=””;F3=””);0;СРЗНАЧ(C3:F3)),

где С3:F3 - диапазон с оценками по всем предметам 1 студента.

5. Для интервала с оценками задать ограничение на ввод данных (Данные/Проверка…)

6. Не отображать нулевые значения (Сервис/Параметры, Вид, снять флажок нулевые значения).

7. Выполнить проверку работоспособности формулы, изменяя исходные данные (оценки).

8. Продемонстрировать работу преподавателю.