ЗАДАНИЯ+Excel+Лабораторная+5. Лабораторная работа 5 Ms Excel Обработка числовой информации на примерах задач по учету и планированию. Работа со связанными таблицами
 Скачать 60.35 Kb.
|
|
Министерство образования и науки Мурманской области ГАПОУ МО «Мурманский колледж экономики и информационных технологий» Специальность: «Информационные системы и программирование» Отделение: дневное Дисциплина: информатика КОМПЛЕКТ ЗАДАНИЙ ЛАБОРАТОРНЫХ РАБОТ вариант Преподаватель: Ена О.А. Лабораторная работа № 5 Ms Excel: Обработка числовой информации на примерах задач по учету и планированию. Работа со связанными таблицами. Деловая графика. Время выполнения 8 часов Цель работы: освоение приемов ввода, редактирования и форматирования данных в электронных таблицах. Освоение навыков вычислений с помощью функций, использование диаграмм для анализа табличных данных, работа с макросами. Освоение приемов использования списков для анализа табличных данных. Задачи лабораторной работы: после выполнения работы студент должен: уметь работать с интерфейсом программы MS Excel; освоить приемы автозаполнения ячеек; знать особенности ввода формул; освоить форматирование таблиц; уметь работать с диаграммами; правильно использовать оператор условия; применять функции для расчетов в электронных таблицах; научиться редактировать списки; уметь выполнять сортировку данных и вычисление итоговых данных в списках; уметь применить фильтры для поиска данных в списках. Перечень обеспечивающих средств: для обеспечения выполнения работы необходимо иметь методические указания по выполнению работы; программное обеспечение: ОС Widows; офисный пакет MS Office 2010 Общие теоретические сведения Для представления данных в удобном виде используют таблицы. Компьютер позволяет представить их в электронном виде, что дает возможность их обрабатывать. Такие таблицы называются электронными. Одним из самых распространенных средств работы с документами, имеющими табличную структуру, является программа MS Excel. Она входит в пакет Microsoft Office и предназначена для подготовки и обработки электронных таблиц. Файл MS Excel 2010 имеет расширение *.xlsx. В терминах MS Excel такой файл называется рабочая книга. В каждом файле могут размещаться от 1 до 255 таблиц, каждая из которых называется рабочим листом. Рабочий лист – документ, который состоит из 16384 строк и 256 столбцов. Строки пронумерованы от 1 до 16384, а столбцы A, B, C и т. д. На пересечении строки и столбца располагается основной структурный элемент таблицы – ячейка. Для указания на конкретную ячейку используется адрес, который составляется из обозначения столбца и номера строки (Al, C2, F6 и т. п.). Если мы хотим работать с группой смежных ячеек, то нужно указать через двоеточие начальную ячейку и конечную ячейку (А1:С2). Для указания номера листа можно записать адрес в виде Лист 8!А1:В2. В Excel есть удобный способ ссылки на ячейку с помощью присвоения этой ячейки произвольного имени. Имя можно задать на вкладке Формулы. Чтобы ввести данные в конкретную ячейку необходимо её выделить щелчком левой кнопки мыши, а затем ввести данные. Чтобы исправить введенные данные необходимо перейти в режим редактирования, дважды щелкнув левой кнопкой мыши по ячейке. В любую ячейку можно записать: число, формулу, текст. Числа автоматически сдвигаются к правой стороне. Число можно записать в стандартном виде: 257 или в экспоненциальном виде: 2.0Е-20. Если последовательность начинается со знака =, то электронная таблица считает её формулой (=А2+СЗ+В6). Если вводимая информация, не число и не формула, то Excel считает, что это текст. Если вводимый текст превысит по длине видимую ширину столбца, то возможны 2 случая: если следующие ячейки пустые, тогда визуально текст накроет эти ячейки; если в следующих ячейках есть данные, тогда правая часть текста скроется за этими ячейками. На вкладке Главная можно открыть вкладку Шрифт и откроется диалоговое окно, в котором можно задать верхний или нижний индекс, выбрать числовые форматы, задать размер шрифта и гарнитуру, выравнивание, границу, заливку, защиту. На вкладке Главная можно выбрать стили ячеек. Редактирование таблицы может быть выполнено с помощью контекстного меню: можно добавить или удалить лист, строки, столбцы, переименовать лист. Особенность электронных таблиц состоит в возможности применения формул для описания связи между значениями различных ячеек. Аргументами в формулах могут быть: числа, функции, ссылки. Ссылка определяет адрес ячейки, где находится нужное значение. Различают абсолютные, относительные и смешанные адреса. Абсолютный адрес ячейки позволяет определить местоположение нужного значения в данной ячейке, адрес которой остается фиксированным при любых операциях и манипуляциях с таблицей. В записи абсолютного адреса ячейки перед именем столбца и перед номером строки пишется знак “$”. Например, $B$19. Копирование формул MS Excel позволяет скопировать готовую формулу в смежные ячейки, причем адреса ячеек будут изменены автоматически. Для этого необходимо выделить ячейку, содержащую формулу, установить указатель мыши на черный квадратик в правом нижнем углу курсорной рамки (указатель примет форму черного крестика). После этого нажать левую кнопку мыши и, не отпуская, сместить указатель по горизонтали или вертикали в зависимости от того, куда распространяется формула. При копировании влево (вправо) смещение на одну ячейку по горизонтали уменьшает (увеличивает) каждый номер столбца в формуле на единицу. При копировании вверх (вниз) по вертикали уменьшает (увеличивает) каждый номер строки в формуле на единицу. Этим способом можно копировать в смежные ячейки числа и тексты. Деловая графика В табличном процессоре для анализа табличных данных можно использовать графическое их представление, списки и методы. В табличном процессоре MS Excel для представления данных в графической форме можно использовать почти два десятка различных типов диаграмм, причем каждый тип содержит несколько форматов. Каждый тип диаграмм служит для определенных целей. Основные типы диаграмм: графики, гистограммы, круговые, лепестковые, точечные диаграммы. Построение диаграмм и графиков можно выполнить с помощью мастера диаграмм, пиктограммы диаграмм можно видеть на вкладке Вставка. Последовательность построения задается мастером диаграмм. Тип диаграммы можно выбрать также с помощью пиктограмм вкладки Вставка. Основной объект диаграммы – ряд Данных. Ряд данных – это совокупность данных, содержащая количественные характеристики объекта. Эти данные содержатся в одном из векторов (в столбце или в строке), составляющих таблицу. В качестве имен рядов данных Excel использует заголовки столбцов или строк данных. Имена рядов отображаются в легенде диаграммы. Использование математических функций Большое место в MS Excel занимают функции. Для работы с функциями существует специальное средство – Мастер функций. Диалоговое окно Мастер функций можно выбрать во вкладке Вставка или кнопкой fx в строке формул. В открывшемся диалоговом окне выбрать нужную категорию функций и требуемую функцию. В следующем окне Аргументы функции задать данные для расчета (список аргументов). Среди математических функций значительное место занимают тригонометрические функции. В их число входят прямые и обратные тригонометрические, а также гиперболические функции. Для вычисления этих функций следует ввести только один аргумент – число. Для функций SIN(число), СОS(число) И ТАN(число) аргумент число – это угол в радианах, для которого определяется значение функции. Если угол задан в градусах, его следует преобразовать в радианы путем умножения его на ПИ()/180 или использования функции РАДИАНЫ. Работа со списками Табличный процессор MS Excel дает в руки пользователя мощные средства анализа данных, если таблица, где они хранятся, организована особым образом, называемым списком. Список – способ хранения данных в таблице, совокупность поименованных строк, содержащих однородные данные (набор строк таблицы, содержащий связанные данные). Существует ряд требований, которым должны отвечать списки: на листе рабочей книги может размещаться только один список; если на этом листе размещаются данные, не входящие в список, то их должны отделять от списка не менее одного пустого столбца и одной пустой строки; первая строка списка должна содержать заголовки столбцов; оформление заголовков столбцов должно отличаться от данных, для этого используется их выделение с помощью шрифта, выравнивания, форматов и рамок; во всех ячейках столбца размещаются однотипные данные, при этом используется один формат; перед содержимым ячейки не должно быть пробелов; Табличный процессор MS Excel позволяет производить сортировку по нескольким показателям (до трех). Очевидно, что в первую очередь сортировка производится по той категории, которая включает в себя наибольшее число записей, так как последующая сортировка осуществляется уже внутри нее. Для упорядочения данных в ячейках по значениям (без учета формата) в Microsoft Excel предусмотрен определенный порядок сортировки – по возрастанию или по убыванию, причем этот порядок зависит от типа данных. Фильтрация данных Фильтрация – это способ поиска подмножества данных в списке в соответствии с заданными условиями. В табличном процессоре MS Excel используется два способа фильтрации списков: Автофильтр для простых условий отбора и Расширенный фильтр для более сложных условий. В отличие от сортировки при фильтрации порядок записей в списке не изме-няется. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их. Понятие о списке (базе данных Excel) Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel и используют соответствующую терминологию: строка списка – запись базы данных; столбец списка – поле базы данных. Название столбца может занимать только одну ячейку и при работе с таблицей как с базой данных называется именем поля. Все ячейки строки с именами полей образуют область имен полей, которая занимает только одну строку. Данные всегда располагаются, начиная со следующей строки после области имен полей. Весь блок ячеек с данными называют областью данных. Индивидуальные задания к лабораторной работе № 5 Ms Excel: Обработка числовой информации на примерах задач по учету и планированию. Работа со связанными таблицами. Деловая графика. Примечание. Информацию о приемах ввода, редактирования, форматирования и других действий в Ехсеl получите из справочной литературы, а также из справочника, встроенногов табличный процессор. Задание 1. Создайте новую рабочую книгу. Сделайте настройку среды Ехсеl для рабочей книги, в которой будет вестись учет результатов экзаменационной сессии студентов: откройте закладку Файл → Параметры и в диалоговом окне выберите вкладку Общие, установив следующие параметры: Шрифт: Arial Размер: 10 Режим, используемый по умолчанию для новых листов: Обычный режим Число листов: 5 Введите имя пользователя. Переименуйте рабочие листы. Сохраните созданную рабочую книгу под именем Session.xls в своей папке. Задание 2. Откройте созданный вами шаблон экзаменационной ведомости с именем Session. Введите указанные в таблице 1 ячейки, тексты заголовка и шапки таблицы в соответствии с рис.1. ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ Группа № __________________________ Дисциплина ___________________________
«отлично» _____________________________________________________ «хорошо» ______________________________________________________ «удовлетворительно» ____________________________________________ «неудовлетровительно» __________________________________________ «неявки» _______________________________________________________ ИТОГО ________________________________________________________ Рис. 1 Форма для экзаменационной ведомости для задания 2. Таблица 1.
Отформатируйте ячейки А1:Е1: по горизонтали: по центру, по вертикали: по верхнему краю, размер шрифта 14пт, выделите текст жирным шрифтом. Проделайте подготовительную работу для формирования шапки таблицы, задав параметры выравнивания вводимого текста. Выделите блок ячеек A3:J3, вызовите контекстное меню вкладки Выравнивание → Формат ячеек → Выравнивание и задайте следующие параметры: по горизонтали: по значению; по вертикали: по верхнему краю; переносить по словам: поставить флажок; ориентация: 0 градусов (по умолчанию). Установите ширину столбцов таблицы в соответствии с рис.1. Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10-15 строк. Отформатируйте данные. Присвойте каждому студенту порядковый номер: введите в ячейку А6 число 1; установите курсор в нижний правый угол ячейки А6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить. После списка студентов в нижней части таблицы согласно рис. 1 введите в ячейки столбца А текст итоговых строк. Объедините две соседние ячейки, где хранятся названия итоговых ячеек, для более удобного представления текста итоговых строк. («отлично», «хорошо» и т.д.) Задание 3. Внимание! При выполнении задания 3 постоянно сравнивайте ваши результаты на экране с изображением Ведомость. Проделайте подготовительную работу, вводя названия (5,4,3,2,неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов (см. изображение Ведомость). В эти столбцы F-J введите вспомогательные формулы. Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1. Пример. Студент Снегирев получил оценку 5, тогда в ячейке F6 должна стоять 1, а в остальных вспомогательных столбцах G-J в данной строке 0. Для ввода исходных формул воспользуйтесь закладкой Формулы → Вставить функцию →Мастер функций: Шаг 1. Категория: - логические Имя функции – ЕСЛИ Щелкните по кнопке Шаг 2. Логическое выражение – D6 = 5 Значение _ если _ истина – 1 Значение _ если _ ложно – 0 Щелкните по кнопке С помощью Мастера формул введите формулы аналогичным способом в остальные ячейки данной строки. В результате в ячейках F6 – J6 должно быть: Адрес ячейки Формула F6 ЕСЛИ (D6 = 5;1;0) G6 ЕСЛИ (D6 = 4;1;0) Н6 ЕСЛИ (D6 = 3;1;0) I6 ЕСЛИ (D6 = 2;1;0) J6 ЕСЛИ (D6 = «н/я»;1;0) Скопируйте эти формулы во все остальные ячейки дополнительных столбцов: выделите блок ячеек F6:J6; установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протащите ее до конца таблицы Экзаменационная ведомость. Выберите в контекстном меню команду Заполнить значения. Определите имена блоков ячеек по каждому дополнительному столбцу. Например. Выделите все значения дополнительного столбца F6: адрес ячейки в столбце, в которой находится последнее значение; Выберите на ленте закладку Формулы → Определенные имена → Присвоить имя. В строке Имя введите слово ОТЛИЧНО. Щелкните по кнопке Проводя аналогичные действия с остальными столбцами, вы создадите еще несколько банков имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА. Выделите столбцы F –J целиком и сделайте их скрытыми. Установите курсор на название столбцов и выделите столбцы F – J. Наведите курсор на выделенную область и нажмите левую кнопку мыши, в контекстном меню выберите команду Скрыть. Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Например. Подсчитаем количество отличных оценок. Установите указатель мыши в ячейку С13 подсчета количества отличных оценок. Выберите на Ленте закладку Формулы → Вставить функцию → Мастер функций Шаг 1. Категория: - математические Имя функции – СУММ Щелкните по кнопке Шаг 2. В строке Число 1 установите курсор, выберите закладку Формулы → Использовать в формуле. В появившемся раскрывающемся списке выберите имя блока ячеек ОТЛИЧНО. Щелкните по кнопке Повторите аналогичные действия для подсчета количества других оценок в ячейках С14-С17. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом (см. изображение Ведомость). Установите курсор в пустой ячейке С18 (рядом с ИТОГО). Эта ячейка должна обязательно находиться под ячейками, где подсчитывались суммы по всем видам оценок. Щелкните по кнопке <∑ Автосумма> (закладка Формулы). Выделите блок ячеек где подсчитывались суммы по всем видам оценок, и нажмите клавишу Переименуйте текущий лист. (Экзамен 1). Скопируйте текущий лист Экзамен 1 два раза. Установите курсор на имени текущего листа и вызовите контекстное меню. Выберите параметр Переместить или скопировать, поставьте флажок Создать копию и параметр Переместить в конец, нажмите В листах Экзамен 1, Экзамен 1(2), Экзамен 1(3) впишите название дисциплины и проставьте оценки и неявки. Выполните команду Показать формулы (Закладка Формулы → Зависимости формул). Сравните ваш результат с изображением ЭТ в формулах, а затем повторно выполнив команду Показать формулы. Задание 4. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании формулам. ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа №___________ Минимальный размер стипендии –
Итого стипендиальный фонд по группе – Рис. 2. Форма стипендиальной ведомости Создайте в Книге Microsoft Excel (файл Session) новый лист – Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов. Оформите название и шапку ведомости назначения на стипендию согласно рис.2. Для этого введите название таблицы ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа №__________ и названия столбцов № п/п; Фамилия, имя, отчество; Стипендия, задайте шрифт и тип выделения – полужирный. Укажите размер минимальной стипендии в ячейке D3. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия – Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с изображением ЭТ ведомость стипендия. Скорректируйте расхождение. Введите формулу вычисления среднего балла студента в ячейку С6 для первого студента. Например. Снегирев. Установите курсор в ячейке С6.Введите значение функции: Категория: Статистические; Имя: СРЗНАЧ. Щелкните по кнопке установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену; установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену; установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по третьему экзамену, щелкните по кнопке в ячейке С6 появится значение, рассчитанное по формуле =СРЗНАЧ (‘Экзамен 1’!D6;’Экзамен 1(2)’!D6;’Экзамен 1(3)’!D6). Скопируйте формулу по всем ячейкам столбца С. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. Технология ввода будет аналогична описанной в п.5: Установите курсор в ячейке D6.Введите значение функции: Категория: Статистические; Имя: СЧЕТ. Щелкните по кнопке установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену; установите курсор во 2-й строке (имя Значение 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену; установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по третьему экзамену, щелкните по кнопке в ячейке D6 появится значение, рассчитанное по формуле =СЧЕТ (‘Экзамен 1’!D6;’Экзамен 1(2)’!D6;’Экзамен 1(3)’!D6). Скопируйте формулу по всем ячейкам столбца D. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид: =ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)) Технология ввода будет аналогична описанной в п.5 и7: Установите курсор в ячейке Е6.Введите значение функции: Категория: Логические; Имя: ЕСЛИ. Щелкните по кнопке Курсор будет находиться в 1-й строке (имя Логическое выражение) панели ввода аргументов функции, нажмите кнопку вызова функции в строке ввода fx, в строке ввода в раскрывающемся списке слева выберите категорию Другие функции и функцию И, нажмите кнопку появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое 1; щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5 установите курсор на второй строке Логическое выражение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере это число 3) D6=3 щелкните по кнопке =ЕСЛИ(И(С6>=4,5;D6=3) щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ; установите курсор во 2-й строке (имя – Значение _ если истина), щелкните в ячейке D3 и нажмите клавишу $D$3*1,5 установите курсор в 3-й строке (имя – Значение _ если ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(С6>=3;D6=3);$D$3;0) после окончания формирования формулы нажмите кнопку Скопируйте эту формулу в другие ячейки столбца Е. Сравните ваш результат работы с изображением ЭТ стипендия. Проверьте работоспособность таблицы: введите другие оценки в экзаменационные ведомости; измените минимальный размер стипендии. Сохраните рабочую книгу. Задание 5. Создайте рабочую книгу под именем Diag, переименуйте Лист 1 (Успеваемость). Создайте таблицу согласно изображению Успеваемость. В пустые ячейки с названием Факультет введите формулу вычисления среднего балла по факультету. Постройте внедренную диаграмму, выполнив следующие операции: Этап 1. Выбор типа и формата диаграммы: на ленте выберите закладку Вставка → Диаграммы → Гистограмма → Гистограмма с группировкой; Этап 2. Выбор и указание диапазона данных для построения диаграммы: на вкладке Работа с диаграммами → Конструктор → Выбрать данные. Курсор автоматически будет находиться в диалоговом окне Диапазон данных для диаграммы:. Выделите диапазон данных А2:С6. в окне Элементы легенды (ряды) выделите строку Ряд 1 и выберите команду Изменить; курсор автоматически будет находится в строке Имя ряда, щелкнитев ячейке В1 с названием Информатика; задайте аналогичным образом имя второго ряда; для создания подписей по оси Х в окне подписи горизонтальной оси выберите команду Изменить и в появившемся диалоговом окне введите данные первого столбца таблицы, т. е. А2:А6. Щелкните по кнопке Этап 3. Задание параметров диаграммы: на вкладке Работа с диаграммами выберите вкладку Макет → Подписи → Название диаграммы → Над диаграммой. Введите название диаграммы: Сведения об успеваемости; Подпишите оси: Ось Х: Учебные группы; Ось Y: Средний балл. На вкладке Легенда выберите команду Добавить легенду справа. Этап 4. Размещение диаграммы. на вкладке Работа с диаграммами выберите вкладку Конструктор → Расположение→ Переместить диаграмму установите переключатель на имеющемся листе и выберите из списка лист Успеваемость. Сравните результат с изображением Гистограмма. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п. 3, но на четвертом шаге установите переключатель На отдельном листе. Сравните результат с изображением Гистограмма 1. Задание 6. В рабочей книге Diag, на листе Успеваемость создайте копию диаграммы Сведения об успеваемости. Добавьте в исходную таблицу новый столбец Философия с различными оценками. Измените формат диаграммы, сделав ее объемной (Объемная гистограмма). Вставьте в диаграмму столбцы, отражающие успеваемость по философии. Измените параметры диаграммы: на вкладке Работа с диаграммами выберите вкладку Конструктор → Данные→ Строка/столбец. оставьте название диаграммы без изменений, добавьте подписи к осям: Ось Х: Дисциплины Ось Y: Учебные единицы Ось Z: Средний балл. Разместите диаграмму на отдельном листе. Сравните свой результат с изображением Гистограмма 2. Задание 7. Создайте новую рабочую книгу под именем Spisok, переименуйте Лист 1 на Список, Лист 2 на Сортировка. Сформируйте на листе Список шапку таблицы (см. изображение База данных). Имена столбцов шапки будут в дальнейшем играть роль имен полей базы данных (списка). Следует помнить, что имя каждого поля должно занимать одну ячейку. Для этого: выделите первую строку, вызовите контекстное меню и выберите команду Формат ячеек. Произведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание: По горизонтали: по значению По вертикали: по верхнему краю Переносить по словам: установить флажок. введите названия имен полей, заполните таблицу данными. Выделите список, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Сортировка. Выполните сортировку Таб. № препод. Для этого установите курсор в поле списка и введите команду Данные → Сортировка. При этом должна выделиться вся область списка. В диалоговом окне Сортировка установите: Сортировать по: поле Таб. № препод., от А до Я Затем по: поле Номер группы, по возрастанию Затем по: Код предмета, от А до Я. Выполните сортировку по другим полям. Задание 8. Переименуйте новый лист на Автофильтр (книга Spisok) и скопируйте на него исходную таблицу данных. Выберите из списка данные, используя критерий – для преподавателя – а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий – л. Для этого: установите курсор в область списка и выполните команду Данные → Фильтр. В каждом столбце появятся кнопки списка. сформируйте условия отбора записей: в столбце Таб. № препод. нажмите кнопку, из списка условий отбора выберите а1; в столбце Оценка нажмите кнопку, из списка условий отбора выберите Числовые фильтры → Больше 2; в столбце Вид занятия нажмите кнопку, из списка условий отбора выберите л. Отмените результат фильтрации, установив указатель мыши в список и выполнив команду Данные → Фильтр. Сравните результат с изображением Фильтрация. Задание 9. Переименуйте новый лист на Структура (книга Spisok) и скопируйте на него исходную таблицу данных. Отсортируйте строки списка по номеру учебной группы. Вкладка Данные → Сортировка. Вставьте пустые разделяющие строки между учебными группами с разными номерами. Создайте структурные части таблицы для учебных групп. (см. изображение Структура). Для этого: выделите блок строк, относящихся к первой группе. Выполите команду Данные → Структура → Группировать. аналогичные действия повторите для другой группы. Создайте структурную часть таблицы для столбцов Код предмета, Таб. № препод., Вид занятий. Закройте и откройте созданные структурные части таблицы, нажимая кнопки <Минус> или <Плюс>. Задание 10. Переименуйте новый лист на Зарплата (книга Spisok) и создайте таблицу расчета заработной платы (см. изображение Зарплата), в которой: в столбцы Фамилия, Зар. Плата, Надбавка, Премия надо ввести константы; введите формулы в ячейки второй строки столбцов, содержащих вычисляемые значения, а также ячейку В6:
скопируйте формулы в остальные ячейки соответствующих столбцов любым известным способом; проведите сортировку в списке по фамилиям. Создайте автоструктуру таблицы расчета заработной платы и сравните с изображением Автоструктура: Вкладка Данные → Структура → Настройка → Автоматические стили → Создать. Задание 11. Создайте новую рабочую книгу под именем Trend, переименуйте Лист 1 на Линейный, Лист 2 на Экспоненциальный, Лист 3 на Квадратичный. На Листе 1 введите табличные данные зависимости заболеваемости бронхиальной астмой от концентрации угарного газа в атмосфере:
Представьте зависимость в виде точечной диаграммы.  Получите три варианта регрессионных моделей (три графических тренда) зависимости заболеваемости бронхиальной астмой от концентрации угарного газа в атмосфере. Для этого: щелкните на поле диаграммы «Заболеваемость астмой»; выполните команду Работа с диаграммами → Макет → Линия тренда → Дополнительные параметры линии тренда; в открывшемся окне выберите тип Линейная, установите галочки на флажках: показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации Rˆ2; щелкните на кнопке <ОК>. Аналогичным образом получите экспоненциальный и квадратичный (полиномиальный) тренды на разных листах. Задание 12. Выполните прогнозирование заболеваемости бронхиальной астмой при концентрации угарного газа равной 3 мг/ м3 методом восстановления значения, воспользовавшись квадратичной зависимостью, полученной в предыдущем задании. Для этого: постройте на Листе 4, переименованном в Прогноз 1 следующую электронную таблицу:
в ячейку В2 ввести формулу квадратичной зависимости, полученную в предыдущем задании. Выполните прогнозирование заболеваемости бронхиальной астмой при концентрации угарного газа равной 6 мг/ м3 методом графической экстраполяции, воспользовавшись квадратичной зависимостью, полученной в предыдущем задании. Для этого: скопируйте на Лист 5, переименованный в Прогноз 2 таблицу из задания 11, постройте квадратичный тренд, установив на вкладке Параметры в области Прогноз в строке вперед на 2,0. оцените приблизительно на полученном графике значение функции при значении аргумента, равном 6. Задание 13. Рассчитайте платежи по процентам и платежи по основному долгу при выдаче кредита в сумме 40000 рублей на 5 лет под 6% годовых. Погашение кредита должно производиться равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Для этого: создайте новую рабочую книгу Kredit; создайте шапку таблицы в соответствии с изображением Кредит; заполните ячейки таблицы формулами в соответствии с изображением Кредит 2. |