Практические работы по использованию функций в Excel (11 класс)

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

Использование абсолютных адресов

стоимость программного обеспечения

наименование

стоимость, $

стоимость, руб.

стоимость, евро

доля в общей стоимости, %

ОС windows

1180,00

пакет MS Office

320,00

редактор Corel Draw

150,00

графический ускоритель 3D

220,00

1С бухгалтерия

500,00

Антивирус DR Web

200,00

Пакет OpenOffice

350,00

ОС Linux

1 350,00

Редактор Adobe Photoshop

780,00

Антивирус eset NOD32

320,00

Антивирус Avast

410,00

1C преприятие

870,00

1С склад

890,00

Налогоплательщик ЮЛ

410,00

Свод СМАРТ Бюджет

250,00

Киностудия Windows

120,00

Microsoft Security

130,00

FB Reader

320,00

итого

курс валюты (к рублю)

65

79

Задание:

1. Записать исходные текстовые и числовые данные, оформить таблицу согласно образцу, приведенному выше

2. Рассчитать графи «Стоимость, руб.» используя курс доллара как абсолютный адрес

Абсолютный адрес ячейки в Excel - это такой адрес, который не изменяется при переносе формулы или ссылки на ячейку в другое место текущего листа книги Excel.

Для этого перед индексами столбца и строки целевой ячейки необходимо поставить знак доллара «$».

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

$A$1 - при копировании не изменяется весь адрес

$A1 - при копировании не изменяется имя столбца

A$1 - при копировании не изменяется номер строки

3. Рассчитать графу «Стоимость, евро», используя стоимость в рублях

4. Рассчитать графу «Итого»

5. Рассчитать графу «Доля в общей стоимости», используя итоговую стоимость в рублях

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

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

Дана последовательность чисел (но некоторые ячейки данного диапазона пустые), для которой необходимо определить:

• Общее количество чисел;

• Максимальное число;

• Минимальное число;

• Среднее значение;

• Сумму всех чисел.

• Для расчета количества положительных/отрицательных чисел использовать функцию СЧЕТЕСЛИ

• Для расчета суммы положительных/отрицательных чисел использовать функцию СУММЕСЛИ

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

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

Результаты сдачи выпускных экзаменов по алгебре, русскому языку, физике и информатике учащимися 9 класса некоторого города были занесены в электронную таблицу. В столбце А электронной таблицы записана фамилия учащегося, в столбцах B, C, D и E - оценки учащегося по алгебре, русскому языку, физике и информатике. Оценки могут принимать значения от 2 до 5 и пустая ячейка, если выпускник не сдавал экзамен по уважительной причине.

На основании данных, содержащихся в этой таблице выполнить задание:

• Вычислить сумму баллов по каждому ученику (F2:F31);

• Определить средний балл по каждому ученику (G2:G31);

• Определить наибольший (G33) и наименьший (G34) средний балл

• Вычислить средний балл по каждому предмету (B32:E32);

• Определить количество выпускников писавших экзамен по алгебре, русскому, физике и информатике (B35:E35);

• Определить по каждому предмету количество сдавших на "5" (B36:E36) и на "2" (B37:E37).

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

Использование встроенных функций и формул в MS Excel

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

Функция ЕСЛИ

Предназначены для проверки выполнения условия или проверки нескольких условий.

Функция ЕСЛИ позволяет определить. Выполняется ли указанное условие. Если условие истинно, то значением ячейки будет выражение1, в противном случае - выражение2

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

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Пример: Вывести в ячейку сообщение «тепло» если значение ячейки B2>20, иначе вывести «холодно»

=ЕСЛИ(B2>20;"тепло";"холодно")

Пример: вывести сообщение «выиграет» если значение ячеек Е4<3 и Н98>=13 (т.е. одновременно выполняются условия), иначе вывести «проиграет»

=ЕСЛИ(И(E4<3;H98>=13);"выиграет";"проиграет")

Задание1

1. Заполнить таблицу и отформатировать по образцу

2. Заполните формулой Сумма диапазон ячеек F4:F10

3. В ячейках диапазона G4:G10 должно быть выведено сообщение о зачислении абитуриента. Абитуриент зачислен в институт, если сумма баллов больше или равна проходному баллу и оценка по математике 4 или 5, в противном случае - не зачислен.

4. 

5. Сохранить документ в своей папке под именем Абитуриенты

Функции И, ИЛИ, НЕ

В электронных таблицах Excel для составления логических высказываний используют функции из категории "Логические": И(); ИЛИ(); НЕ(); ИСТИНА(); ЛОЖЬ()

Синтаксис функций

=И( логич_знач1; логич_знач2; ... ; логич_знач30 )

=ИЛИ( логич_знач1; логич_знач2; ... ; логич_знач30 )

=НЕ( логич_знач1; логич_знач2; ... ; логич_знач30 )

Эти логические функции используются для решения логических задач, а также для построения таблиц истинности.

Задания выполняются в одном документе, на разных листах! Имя документа «Логика»

Задание 1.

1. Лист назвать задание1

2. Подготовьте таблицу

3. В ячейку С2 введите формулу =НЕ(A2)

4. В ячейку D2 введите формулу =И(A2;B2)

5. В ячейку Е2 введите формулу =ИЛИ(A2;B2)

6. Заполните оставшиеся ячейки формулами.

Задание 2.

1. Лист назвать задание2

2. Построить таблицу истинности для конъюнкции (И), дизъюнкции (ИЛИ) и инверсии (НЕ) для четырех переменных (А, В, С, D).

Задание 3.

1. Лист назвать задание3

2. Построить таблицу истинности для высказывания

3. Подготовить таблицу значений для трех высказываний

4. Определить приоритет операций (отрицаний, конъюнкция, дизъюнкция)

5. В ячейку D2 ввести формулу =НЕ(C2), заполнить диапазон D2:D9

6. В ячейку Е2 ввести формулу =И(B2;D2), заполнить диапазон Е2:Е9

7. В ячейку F2 ввести формулу =ИЛИ(A2;E2), заполнить диапазон F2:F9

Рисунок Таблица истинности для высказывания F

Задание 4.

1. Лист назвать задание4

2. Построить таблицы истинности для высказываний

Задание 5.

1. Лист назвать задание5

2. Построить таблицу истинности для операции Следования

Подготовить таблицу (для написания знака следования Вставка - Символ - Шрифт «обычный текст», Набор «стрелки» → )

В ячейку C2 ввести формулу

=ЕСЛИ(ИЛИ(И(A2=0;B2=0);(И(A2=0;B2=1));(И(A2=1;B2=1)));1;0)

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

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

Если любое из трех высказываний выполняется, то значение высказывания «1», в другом случае «0»

И(A2=0;B2=0) - ложны оба высказывания

И(A2=1;B2=1) - истины оба высказывания

И(A2=0;B2=1) - из истины следует ложь

Задание 6.

1. Лист назвать задание6

2. Построить таблицу истинности для операции Сложение по модулю 2, Эквивалентность

А

В

А⊕В

0

0

0

0

1

1

1

0

1

1

1

0

А

В

А ~В

0

0

1

0

1

0

1

0

0

1

1

1

Задание 7.

1. Лист назвать задание7

3. Построить таблицы истинности для высказываний

Функция Дата и время

1. Переименовать Лист1 на «Пример»

Подготовить таблицу по образцу

В ячейке В2 определить текущую дату. Для этого ввести формулу =СЕГОДНЯ()

В ячейке В3 посчитать количество дней, оставшихся до нового года (=B1-B2)

В ячейке В4 определить день года Для этого ввести формулу =B2-ДАТА(ГОД(B2);1;0)

В ячейке В5 определить порядковый номер дня недели. Для этого ввести формулу =ДЕНЬНЕД(B2;2)

В ячейке В6 определить название дня недели. Для этого ввести формулу =ТЕКСТ(B2;"дддд")

2. Переименовать Лист2 на «Дни»

Подготовить таблицу по образцу

ВЫПОЛНИТЬ ЗАДАНИЯ С ПОМОЩЬЮ ФОРМУЛ!

В ячейке В3 определить текущую дату

В диапазоне ячеек В4:G4 определить сколько осталось дней до сдачи экзамена

В диапазоне ячеек В5:G5 определить день года (какой по счёту), когда будет сдаваться экзамен

В диапазоне ячеек В6:G6 определить порядковый номер дня недели, когда будет сдаваться экзамен

В диапазоне ячеек В7:G7 определить название дня недели, когда будет сдаваться экзамен

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

Использование математических, логических и статистических функций при решении задач

Подготовить таблицу как указано на рисунке

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

1. Чему равна наибольшая сумма баллов по двум предметам среди учащихся округа «Северный»?

2. Сколько процентов от общего числа участников составили ученики, получившие по физике больше 60 баллов?

3. Чему равна наименьшая сумма баллов по двум предметам среди учащихся округа «Центральный»?

4. Сколько процентов от общего числа участников составили ученики, получившие по физике меньше 70 баллов?

5. Чему равна средняя сумма баллов по двум предметам среди учащихся школ округа «Южный»?

6. Сколько процентов от общего числа участников составили ученики школ округа «Западный»?

7. Чему равна наибольшая сумма баллов по двум предметам среди учащихся Восточного округа?

8. Сколько процентов от общего числа участников составили ученики, получившие по информатике не менее 80 баллов?

9. Чему равна наименьшая сумма баллов по двум предметам среди учащихся Северного округа?

10. Сколько процентов от общего числа участников составили ученики, получившие по физике не менее 65 баллов?

11. Чему равно количество человек из южного округа, набравших более 60 баллов по физике

12. Чему равна сумма баллов по информатике всех учеников, набравших по физике больше 50 баллов.

Сохранить работу в своей папке под именем «Баллы»

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

Формат ячеек. Построение графиков

Запустить табличный процессор MS Office Excel

Оформить таблицу согласно представленному ниже образцу

Выделить диапазон ячеек В3:G11. По выделенному диапазону нажимаем 1 раз ПКМ. Выбираем пункт меню Формат ячеек на вкладке Число выбираем пункт Денежный -> ОК

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

В ячейку G3 ввести формулу, которая будет рассчитывать заработок Алексея за 5 месяцев

(использовать встроенную формулу СУММА)

Диапазон ячеек G4:G10 заполняется с помощью процедуры автозаполнения.

В ячейку B11 ввести формулу, которая будет рассчитывать сколько в январе было получено всеми сотрудниками (использовать встроенную формулу СУММА).

Диапазон ячеек В11:G11 заполняется с помощью процедуры автозаполнения.

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

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

Для этого необходимо выделить диапазон А3:В10

Вкладка «Вставка»,

группа инструментов «Диаграмма»,

Круговая

После выполнения действия результат:

Далее необходимо написать имя диаграммы: выделяем диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», название диаграммы

Выбираем «Над диаграммой». Вводим в появившейся рамке на диаграмме «заработная плата за январь».

Результат:

Необходимо подписать данные (т.е. каждая часть диаграммы должна отражать сколько именно в рублях получил сотрудник).

Далее необходимо подписать данные : выделяем диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», «Подписи данных»

Выбираем «У вершины снаружи»

Результат:

Далее необходимо изменить местоположение легенды : выделяем диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», «Легенда»

Выбираем «Добавить легенду снизу»

Результат:

Необходимо построить круговую диаграмму, отражающую зарплату Алексея за 5 месяцев

Для этого выделяем диапазон ячеек B2:F2 Вкладка «Вставка», группа инструментов «Диаграмма», Круговая

После выполнения действия результат:

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

Чтобы подписать данные в процентах необходимо выделить диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», «Подписи данных», «Дополнительные параметры подписи данных».

Ставим галочку «Доли», снимаем галочку «Значения». Нажать «Закрыть»

Результат:

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

Для этого выделяем диапазон ячеек B2:F2 зажимаем клавишу CTRL НЕ ОТПУСКАЯ КЛАВИШУ выделяем диапазон B11:F11 Вкладка «Вставка», группа инструментов «Диаграмма», Гистограмма

Результат:

Необходимо задать имя диаграммы, удалить легенду, подписать данные в значениях

Результат:

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

Для этого выделяем диапазон ячеек А3:А10 зажимаем клавишу CTRL НЕ ОТПУСКАЯ КЛАВИШУ выделяем диапазон G3:G10 Вкладка «Вставка», группа инструментов «Диаграмма», Круговая

Результат:

Необходимо задать имя диаграммы, подписать данные в долях

Результат:

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

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

должность

оклад

стаж работы

надбавка за стаж

итого месячная зарплата

директор

8230

15

заместитель директора

7450

12

ведущий экономист

6214

6

экономист

3838

1

экономист

3838

0

экономист

3838

5

экономист

3838

4

экономист

3838

1

экономист

3838

2

экономист

3838

7

бухгалтер

3200

3

бухгалтер

3200

2

бухгалтер

3200

1

бухгалтер

3200

1

бухгалтер

3200

2

бухгалтер

3200

4

бухгалтер

3200

6

бухгалтер

3200

2

программист

3500

4

секретарь

3000

3

итого по учреждению

средняя зарплата по учреждению

минимальная зарплата

максимальная зарплата

справочная таблица

стаж работы

коэффициент

от 0 до 10 лет

0,7

от 10 лет

1

Задание:

1. Записать исходные текстовые и числовые данные, оформить таблицу согласно образцу, приведенному выше

2. Рассчитать графу «Надбавка за стаж», используя встроенную функцию «ЕСЛИ», а также ссылку на абсолютный адрес ячейки, в которой записан коэффициент. (ЕСЛИ стаж <10 лет, то оклад*0,7, иначе оклад *1)

3. Рассчитать месячную зарплату каждого специалиста (оклад + надбавка за стаж)

4. Рассчитать заработную плату по учреждению

5. Найти среднюю, минимальную и максимальную заработную плату по учреждению

6. Построить круговую диаграмму, которая будет отражать сведения о заработной плате каждого специалиста (подпись данных в процентах/долях)