Встроенные функции excel практическая работа номер 5

Практическая работа

Тема: «Разработка электронной таблицы с использованием встроенных функций»

Цель: получить навыки работы с электронной таблицей по использованию стандартных функций.

Теоретическая часть.

При записи формул в таблицах  можно использовать стандартные (встроенные) функции. Все множество встроенных функций табличного процессора делится на несколько групп:

• математические,
•  статистические,
•  функции даты и времени и т.д.

К математическим функциям относятся такие известные из курса школьной математики функции, как SIN() — синус, COS() — косинус, TAN() — тангенс, LN() — натуральный логарифм, KOPEHЬ() (SQRT) — квадратный корень числа и т.д. В круглых скобках (сразу за именем функции) записывается ее аргумент. При использовании тригонометрических функций следует считывать, что аргумент должен быть задан в радианной мере. В качестве аргумента функции может выступать числовая константа, адрес клетки табличного процессора или диапазон (блок) клеток.

Наиболее часто используемой в табличных вычислениях математической функцией является функция суммирования аргументов СУММ (). Аргументами этой функции являются либо диапазон клеток, либо несколько диапазонов клеток, перечисленные через запятую в некоторых табличных процессорах в качестве разделителя аргументов используется «;», адреса клеток, числовые константы.

Наиболее часто используемыми статистическими функциями являются:

• CPЗНAЧ ()(AVERAGE) — вычисление среднего арифметического аргументов,
•  МИН() (MIN) и МАКС() (МАХ) — вычисление минимального и максимального значении среди аргументов. Аргументы этих функций выбираются так же, как и у функции суммирования.

Для того, чтобы воспользоваться этими возможностями существует Мастер функций для запуска которого на панели инструментов используют кнопку fх или команду Вставка-Функция…

Практическая часть

Упражнение №1

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

1. Создайте таблицу по образцу:

1. В ячейку А8 введите – Минимальная глубина
2. В ячейку А9 введите – Максимальная площадь
3. В ячейку А10 введите – Средняя высота
4. Выделите  ячейку В8 и выполните команду Вставка-Функция…В  открывшемся окне выберите категорию статистические, а в окне Функция – МИН →ОК. В окне число1 запишите C1:C7 нажмите ОК.
5. Выделите  ячейку В9 и выполните команду Вставка-Функция…В  открывшемся окне выберите категорию статистические, а в окне Функция – MAX →ОК. В окне число1 запишите В1:В7 нажмите ОК.
6. Выделите  ячейку В10 и выполните команду Вставка-Функция…В  открывшемся окне выберите категорию статистические, а в окне Функция – СРЗНАЧ →ОК. В окне число1 запишите D1:D7 нажмите ОК.
7. Отформатируйте таблицу.

Упражнение №2

На отрезке [0;2] вычислить значения функции f(x) = cosx+x с шагом 0,2.

1. Заполните таблицу по образцу:

1. В ячейку А4 введите формулу А3+$В$1. Используя маркер заполнения, заполните блок ячеек А4:А13.
2. В ячейку В3 введите формулу  = COS(А3)+A3. Используя маркер заполнения, заполните блок ячеек В3:В13.
3. Отформатируйте таблицу.

Упражнение №3

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

Ключ к заданию

• Для вычисления средних значений в столбце, примените функцию
  «СРЗНАЧ» из категории Статистические.
• Для определения минимальных (максимальных) значений в столбце, примените функцию «МИН» («МАКС») из категории Статистические.

Упражнение №4

Составьте таблицу значений функции у =(х-5)2 на отрезке [-3; 3].

Таблица значений функции у =(х-5)2

Ключ к заданию

Для составления формулы воспользуйтесь Мастером функций.

• Выделите ячейку, в которую нужно вставить первое значение функции.
• Введите знак равенства и выполните команду  [Вставка-Функция]
  или выберите кнопку fх
• В окне диалога <Мастер функций> в категории «Математические» выберите функцию «Степень».
• Введите значение аргумента и значение показателя степени. Заполните ряд функций.
•   Для того чтобы в заголовке ввести показатель степени, используйте
  опцию верхний  индекс ([Формат — Ячейки], вкладка Шрифт).

Упражнение №4

Подготовьте таблицу квадратов двузначных чисел. Примените абсолютные ссылки. Вставьте функцию «Степень» при помощи Мастера
функций.

Ключ к заданию

• В ячейку A3 введите число 1, в ячейку А4 — число 2, выделите обе
  ячейки и протащите маркер выделения вниз, чтобы заполнить столбец
  числами от 1 до 9.
• Аналогично заполните ячейки В2 — К2 числами от 0 до 9.
• Для столбцов от  А до К задайте ширину, равную 5 (Формат-
  Столбец-Ширина…).
• В ячейку ВЗ нужно поместить формулу, которая возводит в квадрат
  число, составленное из десятков, указанных в столбце А и единиц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке В3 можно задать формулой =А3*10+В2 (число десятков, умноженное на десять плюс число единиц). Остается возвести это число в квадрат. Возводить в степень с помощью Мастера функций вы научились при выполнении предыдущих упражнений.
• В        ячейке ВЗ будет размещена формула =СТЕПЕНЬ(АЗ*1(В2;2). Формула, размещенная в выделенной ячейке, отображается в Строке формул. Такая формула верно вычислит значения для ячейки ВЗ, но ее нельзя распространять на другие ячейки диапазона, так как Относительные ссылки приведут к неверному результату. Во
  всех формулах необходимо ссылаться на ячейки строки 2 и столбца А. Следовательно, в этой формуле должны быть применены абсолютные ссылки. Приведите формулу к виду =СТЕПЕНЪ($АЗ*10+В$2;2), чтобы ее можно было распространить (скопировать с помощью маркера заполнения) на остальные ячейки диапазона. Сверьте результат с образцом.
• Введите в ячейку А1 заголовок, отцентрируйте его по выделению,
  выполните обрамление таблицы и заполнение фоном отдельные ячейки.

Темы практических работ:

• Практическая работа №1. Ввод данных в ячейки,
  редактирование данных, изменение ширины столбца,
  вставка строки (столбца)
• Практическая работа №2. Ввод формул
• Практическая работа №3. Форматирование таблицы
• Практическая работа №4. Абсолютная и
  относительная адресация ячеек
• Практическая работа №5. Встроенные функции
• Практическая работа №6. Логические функции
• Практическая работа №7. Связывание рабочих
  листов
• Практическая работа №8. Обработка данных с
  помощью ЭТ
• Практическая работа №9. Решение задач с помощью
  ЭТ
• Практическая работа №10. Формализация и
  компьютерное моделирование

Практическая работа №1. Ввод данных в
ячейки, редактирование данных, изменение ширины
столбца, вставка строки (столбца)

Редактирование данных:

1) выбрать нужную ячейку;
2) щелкнуть мышью в строке формул или дважды
щелкнуть левой кнопкой мыши внутри ячейки;
3) отредактировать содержимое ячейки;
4) нажать Enter или щелкнуть мышью в другой ячейке.

Изменение ширины
столбца (высоты строки):

1) подвести курсор мыши к границе столбца
(строки), курсор примет вид  двойной стрелки;
2) передвигать границу до нужного размера, не
отпуская левой кнопки мыши;
3) отпустить левую кнопку мыши.

Вставка строки (столбца)

1) выделить строку (столбец), перед (слева)
которой нужно вставить новую строку (столбец);
2) выбрать Вставка, Строки (Столбцы)

Задание.

1) Введите данные следующей таблицы:

Подберите ширину столбцов так, чтобы были видны
все записи.

2) Вставьте новый столбец перед столбцом А. В
ячейку А1 введите № п/п, пронумеруйте
ячейки А2:А7, используя автозаполнение, для
этого в ячейку А2 введите 1, в ячейку А3
введите 2, выделите эти ячейки, потяните за маркер
Автозаполнения вниз до строки 7.

3) Вставьте строку для названия таблицы. В
ячейку А1 введите название таблицы
Индивидуальные вклады коммерческого банка.

4) Сохраните таблицу в своей папке под именем
банк.xls

Практическая работа №2. Ввод формул

Запись формулы начинается со знака «=». Формулы
содержат числа, имена ячеек, знаки операций,
круглые скобки, имена функций. Вся формула
пишется в строку, символы выстраиваются
последовательно друг за другом.

Задание.

1) Откройте файл банк.xls, созданный на
прошлом уроке. Скопируйте на «Лист 2»
таблицу с «Лист 1».

2) В ячейку С9 введите формулу для
нахождения общей суммы =С3+С4+С5+С6+С7+С8, затем
нажмите Enter.

3) В ячейку D3 введите формулу для
нахождения доли от общего вклада, =С3/C9*100,
затем нажмите Enter.

4) Аналогично находим долю от общего вклада для
ячеек D4, D5, D6, D7, D8

5) Для группы ячеек С3:С9 установите Разделитель
тысяч и разрядность Две цифры после запятой,
используя следующие кнопки , , .
6) Для группы ячеек D3:D8 установите
разрядность Целое число, используя кнопку
7) Добавьте две строки после названия таблицы.
Введите в ячейку А2 текст Дата, в ячейку В2 –
сегодняшнюю дату (например, 10.09.2008), в ячейку А3
текст Время, в ячейку В3 – текущее время
(например, 10:08). Выберите формат даты и времени в
соответствующих ячейках по своему желанию.
В результате выполнения задания получим
таблицу

9) Сохраните документ под тем же именем.

Практическая работа №3.
Форматирование таблицы

1) Для изменения формата ячеек необходимо:

• выделить ячейку (группу ячеек);
• выбрать Формат, Ячейки;
• в появившемся диалоговом окне выбрать нужную
  вкладку (Число, Выравнивание, Шрифт, Граница);
• выбрать нужную категорию;
• нажать ОК.

2) Для объединения ячеек можно воспользоваться
кнопкой  Объединить
и поместить в центре на панели инструментов

Задание. 1) Откройте файл банк.xls,
созданный на прошлом уроке.

2) Объедините ячейки A1:D1.

3) Для ячеек В5:Е5 установите Формат,
Ячейки, Выравнивание, Переносить по словам,
предварительно уменьшив размеры полей, для
ячейки В4 установите Формат, Ячейки,
Выравнивание, Ориентация — 450, для ячейки С4
установите Формат, Ячейки, Выравнивание, по
горизонтали и по вертикали – по центру

4) С помощью команды Формат, Ячейки, Граница установить
необходимые границы
5) Выполните форматирование таблицы по образцу в
конце задания.

9) Сохраните документ под тем же именем.

Практическая работа №4. Абсолютная и
относительная адресация ячеек

1) Формула должна начинаться со знака «=».
2) Каждая ячейка имеет свой адрес, состоящий из
имени столбца и номера строки, например: В3, $A$10, F$7.
3) Адреса бывают относительные (А3, Н7, В9),
абсолютные ($A$8, $F$12 – фиксируются и столбец и
строка) и смешанные ($A7 – фиксируется только
столбец, С$12 – фиксируется только строка). F4
– клавиша для установки в строке формул
абсолютного или смешанного адреса.
4) Относительный адрес ячейки изменяется при
копировании формулы, абсолютный адрес не
изменяется при копировании формулы
5) Для нахождения суммы можно воспользоваться
кнопкой Автосуммирование , которая находится на панели
инструментов

Задание.

1) Откройте файл банк.xls, созданный на
прошлом уроке. Скопируйте на «Лист 3»
таблицу с «Лист 1».
2) В ячейку С9 введите формулу для нахождения
общей суммы, для этого выделите ячейку С9,
нажмите кнопку  Автосуммирование,
выделите группу ячеек С3:С8, затем нажмите Enter.

3) В ячейку D3 введите формулу для
нахождения доли от общего вклада, используя
абсолютную ссылку на ячейку С9:         
=С3/$C$9*100.

4) Скопируйте данную формулу для группы ячеек D4:D8
любым способом.
5) Добавьте две строки после названия таблицы.
Введите в ячейку А2 текст Дата, в ячейку В2 –
сегодняшнюю дату (например, 10.09.2008), в ячейку А3
текст Время, в ячейку В3 – текущее время
(например, 10:08). Выберите формат даты и времени в
соответствующих ячейках по своему желанию.
6) Сравните полученную таблицу с таблицей,
созданной на прошлом уроке.
7) Добавьте строку после третьей строки. Введите в
ячейку В4 текст Курс доллара, в ячейку С4
– число 23,20, в ячейку Е5 введите текст Сумма
вклада, руб.
Используя абсолютную ссылку, в ячейках Е6:Е11
найдите значения суммы вклада в рублях.

9) Сохраните документ под тем же именем.

Практическая работа №5. Встроенные
функции

Excel содержит более 400 встроенных функций для
выполнения стандартных функций для выполнения
стандартных вычислений.
Ввод функции начинается со знака = (равно). После
имени функции в круглых скобках указывается
список аргументов, разделенных точкой с запятой.
Для вставки функции необходимо выделить ячейку,
в которой будет вводиться формула, ввести с
клавиатуры знак =, нажать кнопку Мастера функций  на строке формул. В
появившемся диалоговом окне

выбрать необходимую категорию (математические,
статистические, текстовые и т.д.), в этой
категории выбрать необходимую функцию. Функции СУММ,
СУММЕСЛИ находятся в категории Математические,
функции СЧЕТ, СЧЕТЕСЛИ, МАКС,
МИН находятся в категории Статистические.
Задание. Дана последовательность чисел:
25, –61, 0, –82, 18, –11, 0, 30, 15, –31, 0, –58, 22. В
ячейку А1 введите текущую дату. Числа
вводите в ячейки третьей строки. Заполните
ячейки К5:К14 соответствующими формулами.

Отформатируйте таблицу по образцу:

Лист 1 переименуйте в Числа, остальные
листы удалите. Результат сохраните в своей папке
под именем Числа.xls.

Практическая работа №6. Связывание
рабочих листов

В формулах можно ссылаться не только на данные
в пределах одного листа, но и на данные,
расположенные в ячейках других листов данной
рабочей книги и даже в другой рабочей книге.
Ссылка на ячейку другого листа состоит из имени
листа и имени ячейки (между именами ставится
восклицательный знак!).
Задание. На первом листе создать
таблицу «Заработная плата за январь»

На втором листе создать таблицу «Заработная
плата за февраль»

Переименуйте листы рабочей книги: вместо Лист 1
введите Зарплата за январь, вместо
Лист 2 введите Зарплата за февраль,
вместо Лист 3 введите Всего начислено.
Заполните лист Всего начислено
исходными данными.

Заполните пустые ячейки, для этого введите в
ячейку С9 формулу , в
ячейку D9 введите формулу ,
в остальные ячейки введите соответствующие
формулы.

Сохраните документ под именем зарплата.

Практическая работа №7. Логические
функции

Логические функции предназначены для проверки
выполнения условия или для проверки нескольких
условий.
Функция ЕСЛИ позволяет определить,
выполняется ли указанное условие. Если условие
истинно, то значением ячейки будет выражение 1,
в противном случае – выражение 2.
=ЕСЛИ (условие; выражение 1; выражение 2)
Например, =ЕСЛИ (В2>20; «тепло»; «холодно»)
Если значение в ячейке В2>20, то выводится
сообщение тепло, в противном случае – холодно.

Задание 1.

1) Заполните таблицу и отформатируйте ее по
образцу:

2) Заполните формулами пустые ячейки.
Абитуриент зачислен в институт, если оценка по
математике 4 или 5, в противном случае – нет.
3) Сохраните документ под именем студент.
Совместно с функцией ЕСЛИ используются
логические функции И, ИЛИ, НЕ.
Например, =ЕСЛИ(И(Е4<3; Н8>=3); «выиграет»;
«проиграет»). Если значение в ячейке Е4<3 и Н8>=3,
то выводится сообщение выиграет, в противном
случае – проиграет.

Задание 2.

1) Откройте файл «Студент».
2) Скопируйте таблицу на Лист 2.
3) После названия таблицы добавьте пустую строку.
Введите в ячейку В2 Проходной балл, в ячейку С2
число 13. Изменим условие зачисления абитуриента:
абитуриент зачислен в институт, если сумма
баллов больше или равна проходному баллу и
оценка по математике 4 или 5, в противном случае –
нет.

4) Сохраните полученный документ.

Практическая работа № 8. Обработка
данных с помощью ЭТ

1. Заполните таблицы.
2. Заполните формулами пустые ячейки. Засушливым
считается месяц, в котором количество выпавших
осадков меньше 15 мм (воспользуйтесь формулой
СЧЕТЕСЛИ).
3. Заполните столбец Прогноз:

• засуха, если количество осадков < 15 мм;
• дождливо, если количество осадков >70 мм;
• нормально (в остальных случаях).

4. Представьте данные таблицы Количество
осадков (мм) графически, расположив диаграмму
на Листе 2. Выберите тип диаграммы и элементы
оформления по своему усмотрению.
5. Переименуйте Лист 1 в Метео, Лист 2
в Диаграмма. Удалите лишние листы рабочей
книги.

6) Установите ориентацию листа – альбомная,
укажите в верхнем колонтитуле (Вид,
Колонтитулы) свою фамилию, а в нижнем – дату
выполнения работы.
7) Сохраните таблицу под именем метео.

Практическая работа № 9. Решение задач
с помощью ЭТ

Задача 1. Представьте себя одним из
членов жюри игры «Формула удачи». Вам поручено
отслеживать количество очков, набранных каждым
игроком, и вычислять суммарный выигрыш в рублях в
соответствии с текущим курсом валюты, а также по
результатам игры объявлять победителя. Каждое
набранное в игре очко соответствует 1 доллару.
1. Заготовьте таблицу по образцу:

2. В ячейки Е7:Е9 введите формулы для
расчета Суммарного выигрыша за игру (руб.) каждого
участника, в ячейки В10:D10 введите
формулы для подсчета общего количества очков за
раунд.
3. В ячейку В12 введите логическую
функцию для определения победителя игры
(победителем игры считается тот участник игры, у
которого суммарный выигрыш за игру наибольший)
4. Проверьте, что при изменении  курса валюты и
количества очков участников изменяется
содержимое ячеек, в которых заданы формулы.
5. Сохраните документ под именем Формула
удачи.

Дополнительное задание.

Выполните одну из предлагаемых ниже задач.

1. Для обменного пункта валюты создайте
таблицу, в которой оператор, вводя число
(количество обмениваемых долларов) немедленно
получал бы ответ в виде суммы в рублях.
Текущий курс доллара отразите в отдельной
ячейке. Переименуйте Лист 1 в Обменный
пункт. Сохраните документ под именем Обменный
пункт.

2. В парке высадили молодые деревья: 68 берез, 70
осин и 57 тополей. Подсчитайте общее количество
высаженных деревьев, их процентное соотношение.
Постройте объемный вариант круговой диаграммы.

Сохраните документ под именем Парк.

Практическая работа №10. Формализация
и компьютерное моделирование

При решении конкретной задачи необходимо
формализовать изложенную в ней информацию, а
затем на основе формализации построить
математическую модель задачи, а при решении
задачи на компьютере необходимо построить
компьютерную модель задачи.

Пример 1. Каждый день по радио передают
температуру воздуха, влажность и атмосферное
давление. Определите, в какие дни недели
атмосферное давление было нормальным,
повышенным или пониженным – эта информация
очень важна для метеочувствительных людей.

Формализация задачи – атмосферное давление
считается:

• нормальным, если находится в пределах от 755 до 765
  мм рт.ст.;
• пониженным – в пределах 720-754 мм рт.ст.;
• повышенным – до 780 мм рт.ст.

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

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

Дополнительное задание.

В 1228 г. итальянский математик Фибоначчи
сформулировал задачу: «Некто поместил пару
кроликов в некоем месте, огороженном со всех
сторон стеной. Сколько пар кроликов родится при
этом в течение года, если природа кроликов
такова, что каждый месяц, начиная с третьего
месяца после своего рождения, пара кроликов
производит на свет другую пару?»

Формализация задачи:

Эта задача сводится к последовательности
чисел, в дальнейшем получившей название
«Последовательность Фибоначчи»: 1, 1, 2, 3, 5, 8, …,
Где два первых члена последовательности равны 1,
а каждый следующий член последовательности
равен сумме двух предыдущих.
Выполните компьютерное моделирование задачи
Фибоначчи.

Практическая работа «Встроенные функции в Microsoft Excel»

Вариант 1

Теоретическое задание

1. 1. В ячейке В5 записана формула =$D$2+Е8. Какой вид будет иметь формула, если ячейку В5 скопировать в ячейку А1?

2. Дан фрагмент электронной таблицы:

Чему станет равным значение ячейки С2, если в нее скопировать формулу из ячейки С1?

3. В электронной таблице значение формулы =СУММ(C3:E3) равно 15. Чему равно значение формулы =СРЗНАЧ(C3:F3), если значение ячейки F3 равно 5?

Практическое задание 1 (Лист 1)

Создать таблицу значений функции у = 3х²-5x+1 на участке х[-4, 4] с шагом 0,5.

Образец

Практическое задание 2 (Лист 2)

1. Заполнить таблицу.

2. Вычислить значения в столбце «Средний балл», применив функцию СРЗНАЧ.

Практическая работа «Встроенные функции в Microsoft Excel»

Вариант 2

Теоретическое задание

1. В ячейке СЗ электронной таблицы записана формуле =$А$1+В1. Какой вид будет иметь формула, если ячейку СЗ скопировать в ячейку F7?

2. Дан фрагмент электронной таблицы.

Чему станет равным значение ячейки D1, если в неё скопировать формулу из ячейки С2?

3. В электронной таблице значение формулы =СРЗНАЧ(B5:E5) равно 100. Чему равно значение формулы =СУММ(B5:F5), если значение ячейки F5 равно 10?

Практическое задание 1 (Лист 1)

1. Создать таблицу значений функции у = 1,5х⁴ +2х³ — х² + 5x на участке х[-1, 1] с шагом 0,2.

Образец

Практическое задание 2 (Лист 2)

1. Заполнить таблицу.

2. Вычислить значения в столбце «Стоимость».

Урок №4

Практическая
работа №2

«Использование встроенных
функций и операций ЭТ»

Цель:

—      
создавать условия для
повышения мотивации к использованию электронных таблиц как универсального
инструмента для решения учебных и реальных задач, особенно эффективных при
многовариантных вычислениях;

—       создать условия для формирования понятия
использования встроенных функций в электронных таблицах.

Задачи:

—       сформировать навык применения знаний о
разветвляющихся алгоритмах и логических высказываниях для решения задач с помощью
электронных таблиц Excel;

—       развить умения применять встроенные функции МАКС,
МИН, СРЗНАЧ, ЕСЛИ для решения задач с помощью электронных таблиц Excel;

—       способствовать развитию творческого подхода и
логического мышления.

Задание №1 «Ведомость начисления зарплаты»

В некой гипотетической фирме работает 11 сотрудников.
Причем 9 из них штатные сотрудники, а 2 совместители. Разработать электронную
таблицу расчета заработной платы для предполагаемой фирмы. В таблице должны
быть приведены следующие данные о каждом из сотрудников фирмы:

—
фамилия, имя и отчество;

—
признак сотрудничества
(например, цифра 1 для штатного сотрудника и 0 или пуста ячейка  – для
совместителя);

—
количество иждивенцев
(детей до 18 лет);

—
оклад;

—
количество отработанных
дней.

Кроме того, таблица должна выполнять следующие
расчеты:

—
вычисление районного
коэффициента от суммы начислений (15%);

—
общую заработную плату
сотрудника (сумма оклада и районного коэффициента);

—
величину льготы по
подоходному налогу, которая определяется:

—
для штатных работников это
400 рублей на самого работника и по 300 рублей на каждого ребенка;

—
для совместителей льготы
по подоходному налогу нет;

—
величину налогооблагаемой
базы  (налогооблагаемая  база  определяется как разница между общей зарплатой, льготой
по подоходному налогу и отчислениями в пенсионный фонд);

—
величину подоходного
налога, составляющего 13% от налогооблагаемой базы;

—
величину вычетов из
заработной платы (пенсионный налог и подоходный налог);

—
и величину зарплаты
причитающейся к выдаче на руки.

Примерный
вид таблицы показан на рисунке ниже.

1.    
Создать таблицу.

2.    
В ячейки, оформленные
заливкой, ввести исходные данные.

3.    
Установить для расчетных
ячеек денежный формат.

4.    
По предложенным формулам
провести расчеты, заполнить таблицу, используя маркер заполнения и основные
функции ЕСЛИ, МАКС, МИН, СРЗНАЧ.

5.    
Формулы для  заполнения
таблицы:   

—      
Цена дня = Оклад/раб.дней              

—      
Начислено = Цена дня *
Отработано

—      
Ур. коэфф. = Начислено *
Ур. к

—      
Итого = Начислено + Ур.
Коэф

—      
Вычеты из СД = ЕСЛИ (Штат:совм=1;
(Дети до 18 л+1)*Мин. з.п; 0)

—      
1% Пенс. фонд = Итого*1%     

—      
Под. налог = (Итого —
Вычеты из СД )*13%

—      
К выдаче = Итого — 1%
Пенс. фонд — Под. налог — Квартплата    

6.                     
В строке ИТОГО: вычислить
суммы значений, размещенных в столбцах Начислено, Ур.коэф. и т.д до столбца К
выдаче.

7.                     
Определить максимальную,
минимальную и среднюю з/плату по предприятию (столбец Итого).

Результат выполнения задания:

Задание №2 «Результаты диагностической контрольной
работы»

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

1.    
Ввести в таблицу данные
тестирования.

2.    
Для выполнения расчетов
использовать основные функции СУММ, СРЗНАЧ, СЧЕТЕСЛИ.

3.    
Вычислить средний балл для
каждого ученика.

4.    
Вычислить средний балл
после выполнения учениками всех заданий.

5.    
Вычислить количество
оценок каждого “веса”, в диапазоне указываем адреса ячеек С4:Н11, но делаем
абсолютными номера строк.

6. 
Вычислить количество заданий,
выполненных каждым учеником на 4 и 5.

7. 
Вычислить количество
учащихся выполнивших все задания на 4 и 5.

8. 
Установить нужные параметры
шрифта на панели форматирования.

9. 
При необходимости
устанавливаем количество знаков после запятой. Для этого: Меню Формат→ячейки→числовой→число
знаков.

10.   
В ячейках для вычисления
абсолютной и качественной успеваемости  устанавливаем формат процентный. Для
этого: Меню Формат→ячейки→процентный→число знаков.

11.   
Для оформления таблицы
используем заливку ячеек и границы.

Результат выполнения задания:

Задание №3 «Расчет стоимости билетов на самолет»

Создать таблицу по
образцу, выполнить расчеты.

1.  При выполнении расчетов Цена билета для детей
равна 50% цены  билета для взрослых.

2.  Цена  билета для пенсионеров равна цене 
билета для взрослых минус скидка для пенсионеров

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

—
минимальная и максимальная
цена билета для детей, руб.

—
минимальная и максимальная
цена билета для взрослых, руб.

—
минимальная и максимальная
цена билета для пенсионеров, руб.

—
средняя цена билетов для
детей,  руб.

—
средняя цена билета для
взрослых,  руб.

Результат выполнения задания может выглядеть следующим
образом:

Функции
– это формулы (встроенные подпрограммы
для выполнения тех или иных операций),
определенные заранее и зависящие от
величин, называемых аргументами. Функции
позволяют выполнять как простые, так и
сложные вычисления. Функция начинается
со знака «=» равенства, за которым следует
ее имя, открывающая скобка, список
аргументов, разделенных запятыми,
закрывающая скобка.

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

Любую
функцию можно вызвать тремя способами:

• написав
  в строку ввода после знака «=» имя
  функции и список аргументов (список
  аргументов заключается в круглые
  скобки);

• с
  помощью кнопки мастера функций
  
  в начале строки ввода;

• командой
  меню Вставка/Функция,
  которая также вызывает мастер функций.

Математические
функции
Excel
условно можно подразделить на: простейшие,
функции округления, специфические,
функции для операций с матрицами.

Статистические
функции.
Excel
содержит около 70 статистических функций.
Основными из них являются: простейшие
статистические функции, функции для
дисперсионного и корреляционного
анализа, статистические функции для
целей прогнозирования.

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

Задача
1.

Рассчитайте
выручку от проката машин на 4 часа, 1
день, одну неделю.

1. Составьте
   таблицу следующего вида. Введите в
   таблицу заголовок и сохраните в папке
   Мои
   документы
   под именем Функции.xls.

1. Выделите
   ячейки B5:G5.

2. Выполните
   щелчок на кнопке Сумма
   на ПИ Стандартная.

3. Скопируйте
   формулу из ячейки G5
   в ячейки G6:G7.

4. Рассчитайте
   выручку от проката машин за май.

5. Скопируйте
   формулу из ячейки B8
   в ячейки C8:G8.

6. Рассчитайте
   выручку от проката машин в процентах.
   Для этого выполните:

• установите
  курсор на ячейку H5;

• наберите
  формулу =G5/G8
  и, не нажимая клавишу Enter,
  нажмите клавишу F4.
  Формула примет вид =G5/$G$8,
  то есть относительная ссылка на ячейку
  G8
  заменится на абсолютную $G$8
  (не меняющуюся при копировании формулы
  в другие ячейки);

• нажмите
  клавишу Enter;

• скопируйте
  эту формулу до ячейки H8.

Внимание!
Формулы можно копировать в другие
ячейки. При этом в зависимости от типа
ссылок, входящих в копируемую формулу,
осуществляется их настройка: автоматическая
(для относительных ссылок) или
полуавтоматическая (для частично
абсолютных ссылок). Различают следующие
типы ссылок:

• Относительные
  ссылки,
  например А2 или С2З, которые всегда
  изменяются при копировании или
  перемещении формулы в соответствии с
  её новым местоположением (при копировании
  в строку в ссылке на ячейку изменяется
  имя колонки, при копировании в столбец
  — номер строки, при копировании по
  диагонали – и имя колонки и номер
  строки). При копировании формулы в новую
  книгу и лист перед ссылкой, входящей в
  скопированную формулу, появляется имя
  книги и лист, откуда производилось
  копирование (STAR! лист 5!А4).

• Абсолютные
  ссылки,
  которые перед именем столбца и номером
  строки содержат символ $. Назначение
  абсолютной ссылки производится следующим
  образом: в строке ввода перед ссылкой
  устанавливается курсор и нажимается
  клавиша <F4>, например $А$4. Можно
  сделать то же самое, вводя символ $ с
  клавиатуры. При копировании абсолютные
  ссылки остаются неизменными.

• Частично
  абсолютные (смешанные) ссылки,
  которые при копировании корректируются
  частично. Символ $ стоит или перед именем
  столбца, или перед номером строки ($R2,
  F$5). Например, при копировании формулы,
  содержащей частично абсолютную ссылку
  $F5, сохраняется имя столбца F, а номер
  строки 5 будет изменён.

1. Графу
   Процент
   представьте в процентах, используя
   диалог Формат
   ячейки,
   вкладку Число.

2. Рабочему
   листу, на котором находится таблица,
   присвойте имя
   Задача1
   с помощью команды меню Формат/Лист/Переименовать
   или найдите команду Переименовать
   в контекстном меню ярлычка листа. Когда
   имя ярлычка окажется выделенным цветом,
   можно будет ввести поверх старого новое
   имя.

Задача
2.

Определите
выручку от продажи товаров на лотках и
тенденцию роста доходов.

1. Составьте
   таблицу следующей формы на новом листе
   файла Функции.xls.
   

2. Рассчитайте
   сумму дохода за 2006, 2005 и 2004гг. Для этого:

• щелкните
  на ячейке E6,
  и в строку формул введите формулу для
  расчета суммы дохода за 2006г. =СУММ(D4:D6);

Сумму
дохода за 2005г. найдите с помощью мастера
функций. Для этого:

• установите
  курсор на ячейку E9;

• введите
  формулу используя Мастер
  функций:
  щелкните на кнопке Вставка
  функции
  на панели инструментов Стандартная
  (или выберите меню Вставка/Функция
  укажите искомую функцию), в поле Функция
  щелкните на имени функции СУММ,
  нажмите кнопку <OK>,
  в появившемся диалогом окне в поле
  Число1
  введите диапазон суммируемых чисел
  D7:D9,
  щелкните по кнопке <OK>;

• сумму
  дохода за 2004г. самостоятельно подсчитайте
  в ячейке E12
  любым способом.

Примечание!
Ячейка, содержащая формулу, называется
зависимой, если ее значение зависит от
записей в других ячейках.

1. Рассчитайте
   среднегодовой доход, максимальные и
   минимальные доходы. Можно ввести в поле
   ячейки формулу с описанием операции,
   которую требуется выполнить
   (СРЗНАЧ(E6;E9;E12); МАКС(D4:D12); МИН(D4:D12)), или
   использовать окно мастера функций,
   выбрав соответствующую функцию в меню
   Вставка/Функция.

2. Определите
   ранг (номер) элемента в общей совокупности.
   Для этого в ячейку C4 введите формулу
   =РАНГ($D4;$D$4:$D$12), где D4 – содержит число,
   для которого определяется ранг, а D4:D12
   – массив чисел, среди которых определяется
   ранг.

3. Скопируйте
   формулу в ячейки C5:C12.
   Сохраните таблицу.

4. Рабочему
   листу на котором находится таблица,
   присвойте имя
   Задача2.

Задача
3.

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

1. Составьте
   таблицу следующей формы.

2. В
   ячейку B9
   введите формулу =ОКРУГЛ(СРЗНАЧ(B5:B7);0).

3. В
   ячейку C5
   введите формулу =ABS(B5-$B$9).
   Скопируйте формулу из ячейки C5
   в ячейки C6:C7.

4. В
   ячейку D5
   введите формулу =СТЕПЕНЬ(С5;2). Скопируйте
   формулу из ячейки D5
   в ячейки D6:D7.

5. В
   ячейку D10
   введите формулу =ОКРУГЛ(СРЗНАЧ(D5:D7);0).

6. В
   ячейку D11
   введите формулу =ОКРУГЛ(КОРЕНЬ(D10);0).

7. Очистите
   ячейки D10
   и D11,
   чтобы произвести расчет дисперсии и
   стандартного отклонения, используя
   соответствующие статистические
   функции.

8. В
   ячейку D10
   введите формулу =ДИСПР(B5:B7).

9. В
   ячейку D11 введите формулу =СТАНДОТКЛОНП(B5:B7).

10. Сохраните
    таблицу. Рабочему листу, на котором
    находится таблица, присвойте имя
    Задача3.

Задача
4. (Простая
функция ЕСЛИ)

Найдите
в списке сотрудников тех, у кого имеется
задолженность по потребительскому
кредиту, и удержите с них в счет погашения
кредита 10% от начисленной им суммы.

1. Разработайте
   таблицу Задолженность
   по кредиту
   на новом листе рабочей книги Функции.xls
   в соответствие с рисунком (при
   необходимости добавьте в книгу новый
   лист командой Вставка/Лист).

2. В
   ячейку E3
   введите логическую функцию, которая
   будет иметь следующий вид:

=
ЕСЛИ (С3 > 0; В3 * 0,1; »
—
«)

Если
задолженность по потребительскому
кредиту больше нуля, то необходимо
удержать 10% (0,1) с начисленной суммы, в
противном случае в ячейке вывести
прочерк.

1. Скопируйте
   формулу на ячейки E4:E8.

2. Проанализируйте
   результаты в диапазоне E3:E8.

3. Сохраните
   лист с таблицей под именем Задача4.

Задача
5.
(Логическая функция И)

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

1. Скопируйте
   таблицу Задолженность
   по кредиту с
   листа Задача4
   на новый лист текущей книги.

2. Очистите
   диапазон ячеек Е3:Е8.

3. В
   ячейке Е3 рассчитайте удержания по
   кредитам по формуле:

=
ЕСЛИ (И (С3 > 0; D3
>0);
В3 * 0,2; »
—
«)

Если
одновременно задолженности по
потребительскому кредиту и кредиту на
жилищное строительство больше нуля, то
необходимо удержать 20% (0,2) с начисленной
суммы, в противном случае вывести
прочерки.

1. Скопируйте
   формулу на ячейки E4:E8.

2. Проанализируйте
   результаты в колонке Удержано.

3. Сохраните
   лист с таблицей под именем Задача5.

Задача
6.
(Логическая функция ИЛИ)

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

1. Скопируйте
   таблицу Задолженность
   по кредиту с
   листа Задача4
   или с листа Задача5
   на новый лист текущей книги.

2. Очистите
   диапазон ячеек Е3:Е8.

3. В
   ячейке Е3 рассчитайте удержания по
   кредитам по формуле:

=
ЕСЛИ (ИЛИ (С3 > 0; D3
>0); В3 * 0,1; » — «)

Если
одновременно задолженности по
потребительскому кредиту и кредиту на
жилищное строительство больше нуля, то
необходимо удержать 20% (0,2) с начисленной
суммы, в противном случае вывести
прочерки.

1. Скопируйте
   формулу на ячейки E4:E8.

2. Проанализируйте
   результаты в колонке Удержано.

3. Сохраните
   лист с таблицей под именем Задача6.

Задача
7.
(Вложенные логические функции ЕСЛИ)

Найдите
в списке сотрудников, у которых имеются
одновременно задолженности по обоим
видам кредита, и удержите от начисленной
суммы 20% в счет погашения кредитов. С
остальных работников, имеющих задолженность
по какому-либо одному виду кредита,
удержать 10% от начисленной суммы.
Работникам, не имеющим задолженности
по кредиту, в графе Удержано
проставьте «нет».

1. Скопируйте
   таблицу Задолженность
   по кредиту с
   листа Задача4,
   Задача5
   или Задача6
   на новый лист текущей книги.

2. Очистите
   диапазон ячеек Е3:Е8.

3. В
   ячейке Е3 рассчитайте удержания по
   кредитам по формуле:

=
ЕСЛИ (И (С3 > 0; D3
>0); В3 * 0,2; ЕСЛИ (И (С3 = 0; D3
=0); «нет»; В3 * 0,1))

Если
одновременно задолженности по
потребительскому кредиту и кредиту на
жилищное строительство больше нуля, то
необходимо удержать 20% от начисленной
суммы, если обе задолженности одновременно
равны нулю, то необходимо вывести «нет»,
в противном случае удержать 10% от
начисленной суммы.

1. Скопируйте
   формулу на ячейки E4:E8.

2. Проанализируйте
   результаты в колонке Удержано.

3. Сохраните
   лист с таблицей под именем Задача7.

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

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

Задача
8.

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

1. Добавьте
   в файл Функции.xls
   новый лист.

2. Составьте
   таблицу следующего вида.

1. Столбец
   Порядковый
   номер
   заполните числами с 1 по 14, используя
   автозаполнение.

2. В
   ячейках D5:D18
   создайте формулу для вычисления возраста
   студентов, используя функцию СЕГОДНЯ
   и математическую функцию ЦЕЛОЕ.
   

Чтобы
вычислить возраст, достаточно вычесть
из текущей даты дату рождения и затем
полученный результат разделить на число
365 (число дней в году). Деление на 365
необходимо, чтобы перевести результат
в годы. Для того, чтобы округлить возраст
до целых чисел, рекомендуется использовать
в качестве внешней функции функцию
ЦЕЛОЕ
из категории математические. Другими
словами, в ячейке D5
нужно создать следующую формулу:

=ЦЕЛОЕ
((СЕГОДНЯ () — C5)
/ 365)

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

1. Скопируйте
   формулу из ячейки D5
   в ячейки D6:D18.

2. В
   ячейках столбца Юбилей
   должен содержаться либо текст «юбилей»,
   либо символ «–», в зависимости от того,
   какое число содержится в соседней
   ячейке слева. Если в ячейках D5:D18
   содержится число кратное 5,
   будем считать возраст юбилейным.

Чтобы
создать формулу, определяющую юбилейный
возраст, нужно воспользоваться логической
функцией ЕСЛИ.
Правильно созданная формула должна
иметь следующей вид:

=
ЕСЛИ (ОСТАТ (D5;
5) = 0; «юбилей»;
»
–
«)

В
логическом выражении приведенной
формулы проверяется условие кратности
5 числа в ячейке D5.
Для этого используется математическая
функция ОСТАТ.

1. Скопируйте
   формулу из ячейки E5
   в ячейки E6:E18.

2. С
   помощью условного форматирования
   ячейки с текстом «юбилей» оформите
   шрифтом — курсив
   полужирный,
   синего
   цвета.

3. Заполните
   ячейки F5:F18
   формулами для расчета премии юбилярам.
   Сумма премии равна 50$. Для решения этой
   задачи используйте функцию ЕСЛИ.
   Правильно созданная формула в ячейке
   F5
   должна иметь следующий вид:

=
ЕСЛИ (E5
= «юбилей»;
50; »
–
«).

1. Примените
   любой из финансовых долларовых форматов
   для оформления ячеек F5:F18.

2. Вычислите,
   сколько сотрудников-юбиляров в вашей
   таблице. Используйте функцию СЧЕТЕСЛИ.

3. Вычислите,
   сколько сотрудников в вашей таблице
   моложе 24 лет. Используйте функцию
   СЧЕТЕСЛИ.

4. Вычислите,
   сколько сотрудников в вашей таблице
   старше 25 лет. Используйте функцию
   СЧЕТЕСЛИ.

5. Проанализируйте
   полученные результаты.

6. Отформатируйте
   таблицу: добавьте заголовок, внешние
   и внутренние границы к ячейкам, заливку.

7. Сохраните
   таблицу, рабочему листу присвойте имя
   Дата-Время.

Функции
для расчета постоянных рент.
Последовательность регулярно повторяющихся
платежей называется постоянной
рентой.
Ренты характеризуются величиной платежа,
периодом, процентной ставкой за период
ренты, количеством периодов и тем, когда
вносятся платежи — в начале периода или
в конце.

Задача
9.

Определите
величину ежемесячной выплаты займа в
100 000руб., если он взят на 36 месяцев
при процентной ставке, равной 30%.

1. На
   новом листе рабочей книги Функции.xls
   разработайте следующую таблицу. В
   ячейку B4
   введите значение 0,3 и установите
   Процентный
   формат, с помощью кнопки Процентный
   формат
   ПИ Форматирование. В ячейку B5
   введите – 36.

2. Изучите
   по Справке
   назначение и формат функции ПЛТ.

3. В
   ячейку B6
   введите формулу: =ПЛТ (B4/12;
   B5;
   -B3)
   для расчета ежемесячного платежа при
   сроке ссуды в 36 месяцев (количество
   ежемесячных выплат равно 36).

4. Сохраните
   таблицу, присвоив рабочему листу имя
   Платеж.

Оптимизация
решений в Excel.
Excel
располагает группой средств решения
задач поиска оптимальных решений под
общим названием анализ
«что-если»,
который позволяет увидеть, как изменится
результат, если будут изменены исходные
данные. Одним из инструментов этой
группы является средство Подбор
параметра. Данное
средство удобно применять в ситуациях,
когда известен желаемый результат
одиночной формулы, но неизвестно, какие
исходные значения для него нужны. При
подборе программа изменяет значения в
одной конкретной ячейке до тех пор, пока
формула, зависящая от этой ячейки, не
возвращает искомый результат.

Задача
10.

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

1. Перейдите
   на лист Платеж
   рабочей
   книги Функции.xls.

2. С
   помощью команды Сервис/Подбор
   параметра
   осуществите подбор такого количества
   ежемесячных выплат, при котором каждая
   выплата составит 2800руб.:

• выделите
  ячейку B6;

• Сервис/Подбор
  параметра
  – появится диалоговое окно;

• в
  поле Установить
  в ячейке
  введите $B$6;

• в
  поле Значение
  введите 2800;

• в
  поле Изменяя
  ячейку
  введите $B$5.

• нажав
  <ОК>, получите в ячейке B5
  значение нового срока ссуды.

1. Проанализируйте
   результат.

2. Сохраните
   таблицу. Листу с таблицей задайте имя
   ПодборПараметра.

3. Сдайте
   работу преподавателю.

Задания
для самостоятельной работы.

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

если
налогооблагаемая (н/о) сумма —

• до
  12 000 руб – налог 12%;

• от
  12 000 руб до 24 000 руб – 144 руб + 20%
  от н/о суммы;

• от
  24 000 руб до 36 000 руб – 384 руб + 25%
  от н/о суммы;

• от
  36 000 руб до 48 000 руб – 684 руб + 30%
  от н/о суммы;

• свыше
  48 000 руб – 1044руб + 35% от н/о
  суммы;

проанализируйте
полученные результаты.

1. Используя
   логические функции, определите, кому
   из студентов начислить стипендию, и в
   каком размере. Разработайте таблицу,
   содержащую результаты сдачи трех
   экзаменов группой студентов (10 человек).
   Используйте следующую зависимость:

• если
  сессия сдана на все «5» — повышенная
  стипендия;

• на
  «4» и «5», а также на все «4» — обычная
  стипендия;

• на
  «3» и ниже – стипендия не начисляется.

1. Рассчитайте
   размер платежа по кредиту в 3 000 000
   руб., выданный на 5 лет под 15% годовых,
   который погашается ежемесячными
   платежами. Определите количество
   ежемесячных выплат по кредиту, если
   сумма платежа составит 80 000 руб.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Материалом можно пользоваться как учителю — для демонстрации возможностей, так и ученику — для выполнения работы (в таком случае учитель должен будет удалить Лист 2). Описание выполнения будет ниже в формате docx

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