Формулы и функции в MS Excel 2007
Лабораторная работа №4
Создание простейшей таблицы. Ввод и редактирование данных. Формулы и функции в MS Excel 2007
Копирование и перемещение данных
Задание 1.
1. Создайте и заполните таблицу, аналогичную приведенной на рисунке 4.1. (В ячейке С1 надпись «Дата рождения» необходимо написать в двух строках, для этого введите Дата, нажмите Alt + Enter и введите рождения. Нажмите Enter). Количество строк должно быть не меньше десяти. Названия столбцов должны быть произвольными у каждого отдельного студента.
Рис. 4.1. Таблица
2. Добавьте столбец, например, «Год поступления» между столбцами «Дата рождения» и «Оценка». Значения пустых столбцов задать самостоятельно.
3. Вставьте строку перед таблицей с заголовком «Список группы №».
4. Отредактируйте текст заголовка таблицы, заменив слово «группы №» на подходящее слово по смыслу (Вход в режим редактирования — двойной щелчок мыши по ячейке или F2 или через строку формул).
5. Удалите содержимое столбца «Год поступления» из таблицы (нажмите на кнопку Очиститьна закладке Главнаягруппы Редактирование). Разберитесь, что очищают остальные пункты данной кнопки: Все, Форматы, Примечания.
6. Восстановите содержимое столбца, отменив предыдущую операцию.
7. Проведите сортировку в данной таблице по столбцу с фамилиями в алфавитном порядке.
8. Установите для данной таблицы фильтр. Отобразите только тех студентов, которые родились, например, в марте и июне. Отмените фильтрацию.
9. Отобразите, с помощью автофильтра, только тех студентов, которые имеют оценку выше 4 баллов.
10. Перейдите на чистый лист. Используя автозаполнение, пронумеруйте ячейки столбца А от 0 до 100 с шагом 5, для этого в ячейку A1 введите значение «0», в ячейку A2 — «5» → Выделить обе ячейки → Пользуясь маркером заполнения, протянуть выделение до ячейки A21).
11. Начиная с адреса ячейки В1, введите названия всех месяцев года, используя встроенный список для автозаполнения.
12. Создайте список цветов, включив в него 6 элементов (Кнопка Office → Параметры Excel →Основные → Изменить списки…→ Ввести элементы u1089 списка → OK). Заполните значениями этого списка столбец и строку, начиная с ячейки D2.
13. Скопируйте таблицу тремя способами на различные листы:
a) используя мышь – поместите мышь на границу выделенного фрагмента, указатель примет вид крестообразной стрелки. Нажмите клавиши Alt + Ctrlи, не отпуская их, перетащите указатель мыши с помощью левой (или правой) кнопки на ярлычок того рабочего листа, на который следует скопировать фрагмент;
b) используя контекстное меню;
c) используя опцию «Специальная вставка» - скопируйте таблицу → на закладке Главнаяв группе Буфер обменавыберите команду Специальная вставка → в открывшемся окне щелкнуть по кнопке Вставить связь.
14. Сохранить рабочую книгу под именем «Книга1».
Задание 2.
Формулы и функции в MS Excel 2007
Вариант 1.Создайте таблицу для расчета стоимости проката товара (см. рис.4.2). Содержимое ячеек – произвольное.
Рис. 4.2. Таблица расчета стоимости проката товара
Вариант 2.Пользуясь расписанием движения поездов (см. рис. 4.3), рассчитайте продолжительность поездки на каждом составе.
Вариант 3.Заполните таблицу (см. рис. 4.4), используя функции СЧЕТЕСЛИ и СЧЕТЗ.
Рис. 4.3. Таблица расписания движения поездов
Рис. 4.4. Таблица оценок
Вариант 4. Используя функцию ЕСЛИ заполнить столбец G: если затраты превышают 35000$, то предоставляется скидка 5%, в противном случае – скидки нет. Используя функцию СРЗНАЧЕСЛИ в ячейке С16, посчитать среднюю стоимость посылки. Используя функцию СРЗНАЧЕСЛИМН в ячейке Е16, посчитать среднюю стоимость международных писем (см. рис. 4.5).
Рис. 4.5. Таблица расчетов
Сохранить рабочую книгу под именем «Книга2».
Вариант 5. Создайте таблицу для складского учета и воспользуйтесь необходимыми формулами для подсчета единиц товаров, находящихся на складе на настоящий момент.
Вариант 6. Разработайте бланк ведомости для Вашей группы по дисциплине «Информатика» с подсчетом количества оценок «3», «4», «5» и неудовлетворительно с учетом того, что в течение семестра подводится три рейтинга и выставляется итоговая оценка. Воспользуйтесь необходимыми формулами для подведения итоговой оценки.
Вариант 7. Создайте таблицу для учета учебно-методических изданий кафедры ВТ и воспользуйтесь необходимыми формулами для подсчета единиц изданий различных категорий по определенным учебным годам. Категории: учебники, учебные пособия, учебно-методические пособия, сборники лабораторных работ, методические указания для студентов ФБФО, методические указания для студентов, обучающихся по дистанционной форме обучения и др. Для каждого издания необходимы сведения: авторы, название, наименование категории, год издания, количество страниц.
Вариант 8. Создайте таблицу для учета научных изданий кафедры ВТ и воспользуйтесь необходимыми формулами для подсчета единиц изданий различных категорий по определенным учебным годам. Категории: монографии, статьи в центральной печати, статьи в рецензируемых журналах, статьи в зарубежных журналах, статьи в сборниках ЮФУ, ТТИ ЮФУ, тезисы докладов НТК международного уровня, России, ЮФУ, ТТИ ЮФУ, патенты на изобретения и др. Для каждого издания необходимы сведения: авторы, название, наименование категории, год издания, количество страниц.
Вариант 9. Создайте таблицу для учета выступлений участников различных студенческих НТК и воспользуйтесь необходимыми формулами для подсчета количества участников (среди студентов кафедры ВТ) по годам. Данные: авторы, руководители, название доклада, год доклада, место, которое заняли докладчики на конференции.
Вариант 10. Создайте таблицу для учета выступлений участников различных НТК (сотрудников кафедры ВТ) и воспользуйтесь необходимыми формулами для подсчета количества участников по годам и видам конференций. Данные: авторы, ученая степень и звание автора, название доклада, год доклада. Виды конференций: НТК международного уровня, России, ЮФУ, ТТИ ЮФУ.
Вариант 11. Создайте таблицу для учета материально-технического обеспечения кафедры ВТ и воспользуйтесь необходимыми формулами для подсчета единиц оборудования, находящихся на балансе кафедры на настоящий момент. Категории оборудования: персональные компьютеры, мониторы, клавиатуры, мыши, принтеры, сканеры, плоттеры, серверы, лабораторные стенды, учебные роботы, осциллографы и др. Сведения об оборудовании: год принятия на баланс, стоимость, дата списания.
Вариант 12. Создайте таблицу для учета материально-технического обеспечения кафедры ВТ и воспользуйтесь необходимыми формулами для подсчета единиц предметов, находящихся на балансе кафедры на настоящий момент. Категории: столы для лабораторий, столы для компьютеров, письменные столы, стулья деревянные, кресла крутящиеся, полки подвесные, стеллажи для книг, тумбочки для бумаг и др. Сведения об материально-техническом обеспечении: год принятия на баланс, стоимость, срок службы. Определить дату списания предметов.
Вариант 13. Создайте таблицу для учета сведений студентов своей группы, содержащую: Ф.И.О. дата рождения, гражданство, адрес проживания, тел. студента, адрес родителей, дом. телефон, Ф.И.О. матери, тел. матери, Ф.И.О. отца, тел. отца, предшествующее образовательное учреждение и дата окончания его и др. Провести сортировку по алфавиту. Организовать возможность подсчета по различным критериям, например по адресам проживания (г. Таганрог или другие регионы, иностранные граждане и т.д.).
Вариант 14. Создайте таблицу для учета успеваемости студентов Вашей группы по отдельным дисциплинам текущего семестра. Воспользуйтесь необходимыми формулами для подсчета количества задолженностей по каждому отдельному студенту за текущий семестр.
Вариант 15. Создайте таблицу для учета успеваемости отдельного студента 4-го курса по всем дисциплинам учебного плана. Воспользуйтесь необходимыми формулами для подсчета количества задолженностей по отдельному студенту по семестрам и за весь период обучения. Если же задолженности отсутствуют, то необходимо подсчитать средний балл успеваемости за весь период обучения (четыре учебных года).
Вариант 16. Создайте таблицу для учета посещаемости студентами Вашей группы лекций по дисциплине «Информатика» текущего семестра. Воспользуйтесь необходимыми формулами для подсчета количества пропущенных часов лекций по каждому отдельному студенту. Выдайте рекомендации по предполагаемой успеваемости студентов на основе посещаемости лекций, например посещаемость 100-90% - оценка «отлично», 89-80% - оценка «хорошо», 79-70% – оценка «удовлетворительно», менее 70% - неудовлетворительно.
Вариант 17. Необходимо создать таблицу по получению значений функций двух или более аргументов. Функции: среднее арифметическое, сумма по модулю два, нахождение максимума и минимума.
Вариант 18. Необходимо создать таблицу по получению решения системы уравнений от трех переменных. Система уравнений задается студентом самостоятельно.
Вариант19. Необходимо создать таблицу по получению значений функций от трех переменных (x1, x2, x3). Количество значений каждой переменной 10 шт. Функция F(x1, x2, x3).= (4x1-2x2)2+3 x3.
Вариант 20. Создайте фрагмент учебного плана по образцу, представленному на рис. 4.6. Зеленые ячейки с числовыми значениями проставляются пользователем, а серые ячейки заполняются автоматически по соответствующим формулам (ауд.=лекции+практики+лаборат. р.; КСР=сумма КСР по семестрам; всего с эк=ауд+КСР+сам. работа+экзамен). Нужно знать, что на экзамен выделяется 36 часов, на зачет не выделяются часы.
Рис. 4.6. Фрагмент учебного плана