Упражнение 3. Создание закладки.

Для создания закладки использовать следующий текст:

Сочетание клавиш Windows + E открывает Проводник; сочетание клавиш Windows + R открывает диалоговое окно "Выполнить"; сочетание клавиш Windows + Tab отображает последние использованные запущенные приложения и "Пуск".

1. При создании закладки следует выделить слово, затем использовать команду Закладка в меню Вставка.

2. В поле Имя закладки ей следует присвоить уникальное имя (не более 40 символов).

3. Быстрый переход к закладке осуществляется командой Перейти в диалоговом окне Закладка.

Упражнение 4. Оформить итоговую таблицу полученных научных результатов.

1. Создайте таблицу 4´4. Для этого в меню Вставка следует выбрать команду Таблица.

2. Введите текст, числа и формулы в соответствии с табл. 1.

	A	B	C	D
	Критерий управляемости/полноты	Количество критериев управляемости	Количество критериев полноты	Итого
	Локально			= SUM (LEFT)
	Глобально			= SUM (LEFT)
	Всего	= SUM (ABOVE)	= SUM (ABOVE)	= SUM (ABOVE)

3. Для подсчета значений в ячейке D2 следует установить курсор в эту ячейку и выбрать команду Формула в меню Макет. В диалоговом окне по умолчанию предлагается формула = SUM (LEFT). Далее следует скопировать формулу из ячейки D2 в ячейку D3, щелкнуть правой кнопкой мыши по ячейке D3 и выполнить команду Обновить поле. Аналогично подсчитываются значения в ячейках строки «Всего».

4. Отформатируйте таблицу с использованием меню Конструктор.

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

1. На школьном дворе разбивают 5 клумб треугольной формы. Первая клумба представляет собой равнобедренный треугольник с длинами сторон 5, 5 и 7 метров. Вторая клумба имеет форму прямоугольного треугольника, длины ее сторон – 3, 4 и 5 метров. Длины сторон третьей клумбы равны 4, 3 и 3 метра. Четвертая клумба представляет собой равносторонний треугольник, длина стороны которого равна 4 метрам. Длины сторон пятой клумбы равны 7, 5 и 7 метров. Сколько провода нужно для обозначения границ каждой из этих клумб? Хватит ли 50 м провода, чтобы обозначить на земле границы всех клумб?

2. Нарисовать блок-схему операторов цикла с помощью команды Фигуры меню Вставка.

Лабораторная работа № 4

«Программирование на рабочем листе Microsoft Excel: функции категорий текстовые, логические, просмотра и ссылок, дата и время»

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

Упражнение 1. Сформировать список сотрудников с инициалами:

Фамилия И.О.

из следующего списка:

Фамилия

Имя

Отчество

Для выполнения задания:

1. Введите в ячейки А4:С10 данные в соответствии с заданной конструкцией (рис. 1).

2. В ячейку Е4 поместите текст заголовка конструкции: Фамилия И.О.

Рис. 1 Формирование списка сотрудников Фамилия+Имя+Отчество

3. В ячейку Е5 введите формулу, используя Мастер функций (рис. 2):

=СЦЕПИТЬ (“ “;$A5;” “;ЛЕВСИМВ($B5);”.”;ЛЕВСИМВ($C5);”.“;” ”)

Рис. 2 Окно функции СЦЕПИТЬ() категории ТЕКСТОВЫЕ

 

4. Скопируйте формулу конструкции на диапазон ячеек Е6:Е10.

5. Отформатируйте данные. Результат представлен на рис. 3.

Рис. 3 Полученный результат

Упражнение 2. Дана следующая информация:

Фамилия

Имя

Отчество

Город

Улица

Дом

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

Фамилия И.О., г.Город, ул.Улица, д.Дом

Для выполнения задания:

1. В ячейки В2:G2 введите строку заголовка. Ячейки B3:G6 заполните данными (рис. 4).

 

Рис. 4 Формирование списка сотрудников с адресами

 

2. С помощью Мастера функций в ячейку B9 введите формулу (рис. 5):

=СЦЕПИТЬ (B3;” “;ЛЕВСИМВ(C3);”. ”;ЛЕВСИМВ(D3);” ., г. “;E3;”, ул. “;F3;”, д. “;G3)

3. Скопируйте формулу на диапазон B10:B12. Отформатируйте данные рабочего листа (рис. 6).

Рис. 5 Окно функции СЦЕПИТЬ() категории ТЕКСТОВЫЕ

 

Рис. 6 Конструкция Фамилия И.О., г.Город, ул.Улица, д.Дом

Упражнение 3. Определить для работающих сотрудников стаж работы.

Для выполнения задания:

1. Введите данные для вычисления стажа работы по столбцам: Фамилия, Дата рождения, Начало работы.

2. Введите формулы для расчёта (табл. 1).

Таблица 1

Формулы для расчёта стажа работы

Ячейка	Формула	Формат вывода	Описание
С4	=СЕГОДНЯ()-В4+1	ГГ	Возраст
Е4	=ГОД(СЕГОДНЯ()-D4)-1900 +МЕСЯЦ(СЕГОДНЯ()-D4)/12	#0,00	Стаж работы

3. Скопируйте формулы в соответствующие ячейки диапазона таблицы.

4. Отформатируйте данные и результаты расчёта (рис. 7).

Рис. 7 Определение стажа работы

 

Упражнение 4. Определить дневную нагрузку компьютерного класса: № класса, Начало занятий, Конец занятий, Отработанное время, Характеристика загруженности класса (менее 2 часов – практически пустой, от 2 до 6 часов – малая загрузка, от 6 до 16 – норма, свыше 16 часов – перегружен).

Для выполнения задания:

1. Введите данные для вычисления нагрузки по столбцам: № класса, Начало занятий, Конец занятий, Отработанное время, Характеристика загруженности класса.

2. Введите формулы для расчёта (табл. 2) с помощью Мастера функций (рис. 8).

3. Скопируйте формулы в соответствующие ячейки диапазона таблицы.

4. Отформатируйте данные и результаты расчёта (рис. 9).

Таблица 2

Формулы для расчёта нагрузки компьютерного класса

 

Ячейка	Формула	Формат вывода	Описание
D4	=ЧАС(C4-B4)+МИНУТЫ(C4-B4)/60	Числовой	Отработ.время
Е4	=ЕСЛИ(D4<2;"практически пустой";ЕСЛИ(И(D4>=2; D4<=6); "малая загрузка"; ЕСЛИ(И(D4>6; D4<=16);"норма";"перегружен")))		Хар-ка загруженности

Рис. 8 Окна функции ЕСЛИ() категории ЛОГИЧЕСКИЕ

Рис. 9 Определение нагрузки компьютерного класса

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

Для решения данной задачи:

1. Сформируйте ведомость и дополнительную таблицу, как показано на рис. 10.

2. В ячейку D2 введите формулу:

=C2*ВПР(B2;$A$13:$B$17;2),

где:

· В2 – искомое значение (стаж сотрудника), которое необходимо найти в крайнем левом столбце таблицы, определяемой диапазоном $A$13:$B$17;

· Индекс 2 определяет номер столбца таблицы, из которого будет возвращено значение (процент надбавки);

· С2 – оклад сотрудника.

3. В ячейку E2 введите формулу =C2+D2.

4. Скопируйте формулы в соответствующие диапазоны.

5. Отформатируйте таблицу (рис. 11).

Рис. 10 Исходные данные для ведомости зарплаты

Рис. 11 Ведомость заработной платы

Упражнение 6. Сформировать и заполнить отчётную ведомость по расчёту заработной платы работников научно-проектного отдела (рис. 12).

Для создания ведомости:

1. В ячейку А2 введите название ведомости – Расчёт заработной платы работников научно-проектного отдела. Выделите диапазон ячеек A1:K2, объедините ячейки кнопкой Объединить и поместить в центре на панели инструментов, соответствующей кнопкой на панели инструментов расположите надпись По правому краю .

2. В ячейки A3:H3 введите названия полей ведомости: № пп, Фамилия И.О., Должность, Тарифная ставка, Стаж, k, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата, отцентрируйте их командой Формат ячеек | Выравнивание (рис. 13).

Рис. 12 Ведомость по расчёту заработной платы работников научно-проектного отдела

Рис. 13 Команда меню Формат | Ячейки | вкладка Выравнивание

3. При расчётах учитывайте следующее: k, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата – вычисляются с помощью соответствующих формул, с использованием автозаполнения или копирования формулы.

4. Коэффициент присваивается из следующего расчёта: 0,1 – отработано до 5 лет включительно, 0,2 – от 5 до 10 лет включительно, 0,25 – от 10 до 15 лет включительно, 0,3 свыше 15 лет. Формула для ячейки F4 (рис. 14):

=ЕСЛИ(E4<=5;0,1;ЕСЛИ(И(E4>5;E4<=10);0,2;ЕСЛИ(И(E4>10;E4<=15);0,25;0,3)))

Рис. 14 Ввод формулы в ячейку F4

 

5. В ячейку G4 введите формулу:

=D4*F4

и укажите пользовательский формат # ##0,00р.; командой контекстного меню Формат ячеек, из списка Числовые форматы выберите Все форматы, в поле Тип введите указанный формат.

6. В ячейку H4 введите формулу:

=D4+G4

и укажите пользовательский формат # ##0,00р. Увеличьте размер столбца H, если тарифная ставка с учётом стажа не выводится.

7. Процент налога – учитывает, что: 2% – начисление (по Итого) составляет 7 000 р. включительно, 10% – более 7 000 р. до 10 000 р. включительно, 20% – более 10 000 р. до 25 000 р. включительно, 35% – превышающие 25 000 р. Формат числа для ячейки I4 – Процентный и формула:

=ЕСЛИ(H4<=7000;0,02;ЕСЛИ(И(H4>7000;H4<=10000);0,1;
ЕСЛИ(И(H4>10000;H4<=25000);0,25;0,35)))

8. Выплата – сумма к выдаче: Итого без Удержать.

9. Требования к столбцу Стаж: создайте пользовательский формат данных, учитывающий стаж работы: до 5 лет – данные представлены жёлтым цветом, от 5 до 10 – синим, от 10 до 15 – зелёным, свыше 15 – красным. Для этого воспользуйтесь командой Главная | Условное форматирование | Правила выделения ячеек (рис. 16).

Рис. 16 Использование условного форматирования для представления данных столбца Стаж

10. В случае ввода отрицательного числа лет в столбце Стаж должно появляться соответствующее окно (рис. 17). Для проверки ввода чисел используйте команду Данные | Проверка данных, вкладки Параметры (рис. 18), Сообщение об ошибке (рис. 19).

Рис. 17 Сообщение о неправильном вводе в поле Стаж

 

Рис. 18 Команда меню Данные | Проверка данных | вкладка Параметры

 

Рис. 19 Команда меню Данные | Проверка данных | вкладка Сообщение об ошибке

11. Для поля Тарифная ставка – выведите постоянное сообщение: Тарифная ставка. Будьте внимательны при вводе тарифной ставки. Для этого используйте команду Данные | Проверка данных, вкладка Сообщение для ввода.

12. В случае ввода отрицательных значений в столбце Тарифная ставка должно появляться соответствующее предупреждение: Тарифная ставка не может быть отрицательной (рис. 20), которое формируется через пользовательский формат: # ##0,00р.;[Красный] "Тарифная ставка не может быть отрицательной" (рис. 21).

Рис. 20 Сообщение при вводе отрицательной тарифной ставки

 

Рис. 21 Форматирование столбца Тарифная ставка

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

1. Студенты выполняют 5 тестов по информатике, за каждый из которых можно получить от 0 до 10 баллов. Если за тест № 3 получено более 6 баллов, то этот результат увеличивается на 20 %. Суммарное количество полученных при тестировании баллов меньше 20 соответствует оценке «2», оценке «3» соответствует количество баллов от 20 до 29, оценке «4» – от 30 до 39, оценке «5» – 40 баллов и выше.

Заполните электронную таблицу исходными данными в соответствии с рис. 22.

Введите формулы для расчета суммы баллов и оценки соответственно в столбцах G и H (используя функцию «ЕСЛИ»), среднего значения набранных баллов по каждому тесту в ячейках В15: F15.

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

Рис. 22 Задание «Результаты тестирования»

 

2. Оформить отчёт о продаже товаров:

Наименование товара, Дата поступления, Дата реализации, Срок реализации, Характеристика спроса на товар (до 30 — товар пользуется спросом, от 30 до 60 дней — замедленная реализация товара, свыше 60 дней — товар залежался).

3. Дана следующая информация:

Фамилия

Имя

Отчество

Улица

Дом

Квартира

Телефон

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

Фамилия И.О., Улица, Дом-Квартира, т.Телефон {формат #00-00-00#}

4. Разделить следующую информацию, находящуюся в одной ячейке:

Фамилия студента, Факультет, Курс, Группа

Результат получить в виде:

Фамилия студента

Факультет

Курс

Группа

5. Сформировать основную и вспомогательную таблицы. Использовать для вычислений данные вспомогательной таблицы.

№ пп	Заказчик	Дата заказа	Тип рекламы	Кол-во заказов	Стоимость одного заказа	Сумма
	СП «Парнас»	22 янв.	Буклет
	ИП «Альтаир»	25 янв.	Листовка
	СП «Зевс»	16 июл.	Буклет
	ИП «Малыш»	01 сен.	Буклет
	ИП «Рео»	04 окт.	Плакат

 

Тип рекламы	Стоимость заказа
Буклет
Листовка
Плакат

6. Сформировать основную и вспомогательную таблицы, используя для вычислений функцию ГПР().

Тип площади	Стоимость 1 кв.м.	Требуемая площадь	Итого за месяц	Кол-во месяцев	Итого
Офис
Производство
Стоянка
Склад

 

Тип площади	Офис	Производство	Склад	Стоянка
Стоимость 1 кв.м.	? 10	? 5	? 3	? 4

Лабораторная работа № 5

«Программирование на рабочем листе Microsoft Excel: функции категорий математические, логические, ссылки и массивы, статистические»

Упражнение 1. Для заданных целых чисел определить количество чисел, кратных 3 (рис. 1).

Для выполнения задания:

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

2. В ячейку В4 введите формулу:

=ЕСЛИ(ОСТАТ(A4;3)=0;1;0),

которая далее копируется на диапазон В5:В20.

3. В ячейку С4 (соответственно в диапазон С4:С20) введите формулу:

=ЕСЛИ(B4=1;"Данная точка делится без остатка на 3";).

Пользовательский формат для диапазона С4:С20:;;[Белый]

4. В ячейку С22 введите формулу:

=СУММ(B4:B20)

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

 

Упражнение 2. Удвоить числа, расположенные на нечётных местах одномерного массива, и определить их сумму (рис. 2).

Для выполнения задания:

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

2. В ячейку В4 введите формулу:

=ЕСЛИ(ОСТАТ(СТРОКА(A4);2)<>0;2*A4;0),

которая далее копируется на диапазон В5:В12.

3. В ячейку В15 введите формулу:

=СУММ(B4:B11).

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

Рис. 1 Количество точек, кратных 3

Рис. 2 Сумма удвоенных чисел, расположенных на нечётных местах одномерного массива

Упражнение 3. Подсчитать в заданном двумерном массиве количество отрицательных элементов (рис. 3).

Рис. 3 Подсчёт отрицательных элементов массива

 

Для выполнения задания:

1. Заполните ячейки A4:D6 необходимыми значениями.

2. В ячейку F4 введите формулу:

=СЧЁТЕСЛИ(A4:D6;"<0").

 

Упражнение 4. Заданы четыре матрицы A, B, C, D одинаковой размерности, содержащие по 3 строки и по 4 столбца. Найти (рис. 4).

Для выполнения задания:

1. На рабочий лист поместите данные соответствующих матриц A, B, C, D (соответственно в диапазоны A2:D4, A7:D9, A12:D14, A17:D19).

2. В ячейку F3 введите Результирующая матрица.

3. Используя команду Вставка | Объект | вкладка Новый | Тип объекта Microsoft Equation 3.0, вставьте формулу для наглядности.

4. В ячейку F7 введите формулу:

=5*A2:D4-COS(A7:D9)+СТЕПЕНЬ(A12:D14;2)-A17:D19

и нажмите клавишу < Enter >.

5. Выделите диапазон F7:I9, установите указатель мыши в строку формул и нажмите клавиши < Ctrl >+< Shift >+< Enter >.

 

Рис. 4 Вычисление результирующей матрицы