Практические работы по Microsoft Excel (Часть 2)
Занятие 4 Использование стандартных функций в таблицах Excel
Цель работы: Изучить использование стандартных функций для выполнения вычислений в таблицах Excel, освоить работу с Мастером функций
Контрольные вопросы:
Категории стандартных функций Excel. Синтаксис функций (имя, аргументы, тип результата). Особенности использования тригонометрических функций.
Правила ввода функций в формулу Excel. Использование Мастера функций и палитры функций.
Понятие вложенной функции. Сколько уровней вложения допускает Excel?
Использование имен диапазонов в качестве аргументов функций. Как присвоить/применить имя диапазона?
Как заменить формулы в ячейках на их значения? Как отобразить в ячейках формулы вместо значений?
Каковы наиболее распространенные коды ошибок и причины их возникновения?
Порядок использования команды Сервис/Зависимости для выявления источника ошибок.
Порядок использования команды Данные/Проверка для контроля вводимых данных.
Задания на практическую работу:
Загрузить Excel. Создать новую рабочую книгу из 6 листов: Матрицы, Математические функции, День рождения, Амортизация, Выбор значений, Ведомость.
На листе Матрица расположить матрицу A(3х4) (в ячейках B1:E3):
Используя математические функции для работы с матрицами, найти:
матрицу В=А*k, где k=3 (использовать формулу массива: выделить диапазон для матрицы В - B7:Е9, ввести формулу = B1:Е3*3, нажать Ctrl+Shift+Enter);
матрицу С=А+В (использовать формулу массива: выделить диапазон для матрицы С - B11:Е13, ввести формулу = B1:Е3+ B7:Е9, нажать Ctrl+Shift+Enter);
матрицу D=АТ (использовать формулу массива: выделить диапазон для матрицы D - B15: D18, ввести формулу =ТРАНСП(B1:E3) нажать Ctrl+Shift+Enter);
матрицу E=A*D; 5) E-1 (обратная матрица); 6)определитель матрицы Е.
Отобразить на листе формулы в ячейках (Сервис/Параметры/Вид, флажок Формулы). Оценить результат. Восстановить режим отображения значений.
Присвоить имена ячейкам с элементами матриц (выделить, Вставка/Имя, Присвоить А - B1:Е3).
Заменить в формулах ссылки диапазонов на имена (Вставка/Имя, Применить).
На листе Математические функции построить:
- таблицу значений функции y=2sin(x-) +cos(x+/2), в диапазоне от 30 до 360, с шагом 10
- таблицу для расчета значения функции при начальных значениях x=25, y=3, z=8.:
Результат вывести с точностью 3 знака после запятой.
- таблицу Округление, содержащую число 73,26, округленное до десятых по правилам округления, до целого, до десятков в меньшую сторону, до десятков в большую сторону, до сотен (использовать функции ОКРУГЛ, ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ).
На листе День рождения построить таблицу, в которой выделить именинников на текущую дату, указав сообщение "День рождения сегодня". Использовать функции СЕГОДНЯ, ЕСЛИ, ДЕНЬ, МЕСЯЦ, И.
Ф.И.О. |
Дата рождения |
Именинник |
Иванов И.И. |
18.11.2000 |
=? |
… |
… |
… |
На листе Амортизация построить таблицу для расчета годовой амортизации станка стоимостью 45000$, если срок эксплуатации составляет 20 лет, а остаточная стоимость - 4800$. Формула:
,
где A – годовая сумма амортизационных отчислений, S – балансовая стоимость, C – остаточная стоимость, T – срок эксплуатации.
На листе Выбор значений построить таблицу, позволяющую определить лидера по объему продаж и месяц, на который приходится максимальный объем продаж сотрудника. Использовать функции ИНДЕКС, ПОИСКПОЗ, МАКС.
Ф.И.О. |
Январь |
Февраль |
Март |
Лучший месяц |
Иванов |
50 |
80 |
70 |
=? |
Петров |
110 |
30 |
|
=? |
Сидоров |
90 |
|
200 |
=? |
Лидер месяца |
=? |
=? |
=? |
|
Под таблицей сформировать по 3 текстовые строки вида (использовать &):
- Лидер за месяц - Фио.
- Фио - лучший месяц работы месяц.
На листе Ведомость построить таблицу для расчета среднего балла и суммы стипендии.
№ |
Фамилия И.О. |
Оценки |
Средний |
|||
|
|
Математика |
Физика |
информатика |
иностранный язык. |
балл |
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 студента.
Для интервала с оценками задать ограничение на ввод данных (Данные/Проверка…)
Не отображать нулевые значения (Сервис/Параметры, Вид, снять флажок нулевые значения).
Выполнить проверку работоспособности формулы, изменяя исходные данные (оценки).
Продемонстрировать работу преподавателю.