|
|
ьо. Методические указания к лабораторным работам
Методические указания к лабораторным работам
Информационные технологии и сервисы.
Электронный документооборот проектной деятельности.
Лабораторная №4
Авторы: Дженакова Екатерина Всеволодовна, Шестеров Михаил Андреевич
Екатеринбург 2020
Microsoft Excel. Инструментарий.
Создание простейшей таблицы. Ввод и редактирование данных. Копирование и перемещение данных
Создайте и заполните таблицу, приведенную на Рисунок 1. (В ячейке С1 надпись «Дата рождения» необходимо написать в двух строках, для этого введите Дата, нажмите Alt + Enter и введите рождения. Нажмите Enter).
Рисунок 1 - Таблица
Работа ведётся с небольшой таблицей, поэтому масштаб можно изменить справа внизу, либо зажать ctrl и крутить колёсико мыши.
Для выравнивания текста по центру ячейки и по высоте, и по ширине можно использовать кнопки в верхнем меню. 
Добавьте столбец «Год поступления» между столбцами «Дата рождения» и «Оценки». Столбец будет добавлен слева от того в котором находится выделенная ячейка, поэтому выберите любую ячейку в столбце «Оценки», либо выделите весь столбец нажав на «D». На вкладке «Главная» с помощью подменю «Вставить» добавьте новый столбец.
Самостоятельно заполните значения пустых столбцов.
Вставьте строку (см. Пункт 3) перед таблицей с заголовком «Список группы №». Для объединения нескольких ячеек выделите их и нажмите  .
Отредактируйте текст заголовка таблицы, заменив слово «группы №» на «факультета» (Вход в режим редактирования — двойной щелчок мыши по ячейке или F2 или через строку формул).
Удалите содержимое столбца «Год поступления» из таблицы (нажмите на кнопку Очистить на закладке Главная группы Редактирование). Разберитесь, что очищают остальные пункты данной кнопки: «Очистить все», «Очистить форматы», «Очистить содержимое». (*Если навести на кнопку - отобразится описание*)
Восстановите содержимое столбца, отменив предыдущую операцию. Для этого можно использовать сочетание клавиш Ctrl + Z.
Проведите сортировку в данной таблице по столбцу с фамилиями в алфавитном порядке, сохранив при этом нумерацию от 1 до 6. Для этого выделите все ячейки кроме столбца «№ п/п». Во вкладке «Данные» выберите «Сортировка», либо нажав ПКМ и выбрав пункт «Сортировка» – «Настраиваемая сортировка…». Проверьте, что стоит галочка «Мои данные содержат заголовки». И отсортируйте по таблицу по ФИО. Рисунок 2
Рисунок 2 - Сортировка
Установите для данной таблицы фильтр. Отобразите только тех студентов, которые родились в марте и июне (или других, имеющихся в вашей таблице, месяцах). Для установки фильтра выделите строку заголовков и нажмите «Фильтр» в меню «Данные»  , либо сочетание клавиш Ctrl + Shift + L. Отмените фильтрацию.
Отобразите, с помощью автофильтра, только тех студентов, которые имеют оценку выше 4 баллов. Для этого нажмите на стрелку фильтра и выберите «Числовые фильтры» – «Больше или равно…» Рисунок 3
Рисунок 3 - Автофильтр
Перейдите на чистый лист, нажав внизу на панели листов  и создав «Лист 2». Используя автозаполнение, пронумеруйте ячейки столбца А от 0 до 100 с шагом 5, для этого в ячейку A1 введите значение «0», в ячейку A2 — «5» → Выделить обе ячейки → Пользуясь маркером заполнения, протянуть выделение до ячейки A21).
Начиная с адреса ячейки В1, введите названия всех месяцев года, используя встроенный список для автозаполнения. Для этого введите «Январь» и с помощью маркера заполнения растяните данные на 12 ячеек. Также можно делать и с днями недели и многим другим.
Скопируйте таблицу тремя способами на различные листы:
Ctrl + C для копирования, Ctrl + V для вставки на Лист 2;
Используя мышь – поместите мышь на границу выделенного фрагмента, указатель примет вид крестообразной стрелки. Нажмите клавиши Alt + Ctrl и, не отпуская их, перетащите указатель мыши с помощью левой (или правой) кнопки на ярлычок того рабочего листа, на который следует скопировать фрагмент;
Используя опцию «Специальная вставка». Скопируйте фрагмент, поставьте курсор, где должна будет находиться верхняя левая ячейка. Нажмите ПКМ «Специальная вставка» и выберите подходящий вариант. Рассмотрите, чем отличаются способы «Специальной вставки».
Сохраните книгу назвав её «ФИО_Книга1»
Защита данных в MS Excel
Включите защиту листа «Лист1», установив пароль «0000» для доступа. Ввод разрешить только в столбец «Оценка» (Выделить столбец «Оценка» → через контекстное меню Формат ячейки… → на закладке Защита → Сбросить флажок «Защищаемая ячейка» → OK, т.е. на столбец «Оценка» не установлена защита. Далее необходимо установить защиту на весь лист, для этого на вкладке Рецензирование, выбрать опцию Защитить лист → Установить пароль “0000” → Подтвердить пароль → OK).
Защитите файл рабочей книги с помощью пароля «0000» (Файл → Сохранить как… → Обзор → Сервис → Общие параметры → Задать пароль для открытия файла → OK → Подтвердить пароль → OK).
Закройте файл и откройте заново – проверьте, что документ доступен только с паролем.
Формулы и функции
Синтаксис функций можно узнать через меню справки или с помощью помощника «Что вы хотите сделать?». Также помощник подскажет функцию по описанию её действия.
Создайте новый лист для следующих задач и выполните их:
Задача. Создайте таблицу для расчета стоимости проката товара (Рисунок 4). Рассчитайте продолжительность проката и сумму к оплате используя формулы (формулы начинаются с символа - "=".)
Рисунок 4 – Таблица расчета стоимости проката товара
Задача. Пользуясь расписанием движения поездов (Рисунок 5), рассчитайте продолжительность поездки на каждом составе. Постарайтесь самостоятельно найти как учитывать, если время прибытия меньше времени отправления
Рисунок 5 - Таблица расписания движения поездов
Задача. Заполните таблицу (Рисунок 6), используя функции СЧЕТЕСЛИ и СЧЕТЗ
Рисунок 6 - Таблица оценок
Задача. Используя функцию ЕСЛИ заполнить столбец G: если затраты превышают 35000$, то предоставляется скидка 5%, в противном случае – скидки нет. Используя функцию СРЗНАЧЕСЛИ в ячейке С16 посчитать среднюю стоимость посылки. Используя функцию СРЗНАЧЕСЛИМН в ячейке Е16 посчитать среднюю стоимость международных писем. (Рисунок 7)
Рисунок 7 - Таблица расчетов
Проверка вводимых данных, функция «Форматировать как таблицу», присвоение имен диапазонам данных.
Функция «Формировать как таблицу», которая располагается на вкладке Главная, группа – Стили, позволяет быстро форматировать диапазон ячеек и преобразовать его в таблицу с помощью выбора определенного стиля таблицы. При этом Excel автоматически маркирует заголовки столбцов и создает автофильтр. После этого в группе контекстных инструментов Работа с таблицами отобразится вкладка Конструктор, в которой находятся инструменты для работы с таблицей.
Опция «Проверка данных»  , которая располагается на вкладке Данные, группы Работа с данными, позволяет защитить ячейку от ввода неверных данных. Например, можно запретить ввод чисел, превышающих 500. Можно также разрешить ввод значений только из предварительно определенного раскрывающегося списка. Для этого необходимо вызвать опцию «Проверка данных», в поле Тип данных выбрать Список, в появившемся поле Источник указать диапазон ячеек со списком. Если диапазон со списком находится на другом листе, то ему предварительно необходимо задать имя (выделяется диапазон и в поле Имя указывается имя). Тогда в поле Источник указывается имя диапазона.
Рисунок 8 – Проверка данных
Рисунок 9 – Проверка вводимых значений
Задача. С помощью инструмента «Проверка вводимых значений» и функции ВПР составить удобное средство поиска суммы по статье расхода (Рисунок 10). То есть, в ячейке А2 должен быть список, из которого выбирается статья расходов, после чего в ячейке В2 появляется соответствующая сумма расходов.
Рисунок 10 - Таблица расходов
Варианты заданий
Согласно списку группы, выполните свой вариант задания. Если у вас вариант больше 5, то идите по кругу. Студент с номером 9 делает 4 вариант, 13 студент делает 3 вариант и т.д.
Создайте новый лист «Вариант_Номер своего варианта».
Для объединения текста ячеек и отдельных строк используйте &. С помощью & можно объединить две строки “Привет.” & “Как дела?”, на выходе получим «Привет. Как дела?». Также можно объединять текст и значение ячеек. Объединение ячеек и текста можно комбинировать в любом порядке. Один из примеров представлен на Рисунок 11.
Рисунок 11 – Объединение ячеек
Для использования только первого символа из строки или ячейки можно использовать функции ЛЕВСИМВ.
При необходимости задать функции ЕСЛИ(ЛОГ.УСЛОВИЕ;ИСТИНА;ЛОЖЬ) более двух исходов, то в разделе ИСТИНА или ЛОЖЬ можно использовать повторно функцию ЕСЛИ. Такой приём можно выполнять много раз.
Стипендия
Табель
Круиз
Абитуриент
Трудовая книжка
Варианты взяты из Методических указаний к выполнению лабораторных работ Саблиной Н.Г. Екатеринбург – 2006. – 46с. |
|
|
Скачать 494.68 Kb.