Задание 1. Формирование структуры таблицы и заполнение ее постоянными данными
Министерство образования и науки Российской Федерации
Федеральное государственное бюджетное образовательное
учреждение высшего образования
Пермский национальный исследовательский политехнический университет»
Электротехнический факультет
Кафедра конструирования и технологий в электротехнике
Направление 13.03.02 - «Электроэнергетика и электротехника»
Методические указания
по освоению Microsoft Excel и Visual Basic for Application
для студентов очной формы обучения
кафедры КТЭ
Пермь, 2016 г.
Содержание
Содержание. 2
1. Введение. 3
2. Начало работы.. 5
3. Практическая часть I. 6
4. Практическая часть II. 23
Приложение 1.. 30
Приложение 2.. 35
Приложение 3. 40
Приложение 4. 41
Приложение 5. 42
Приложение 6. 43
Приложение 7. 45
Введение
Microsoft Excel на сегодняшний день является одной из самых популярных программ для работы с электронными таблицами. Она имеет продуманную архитектуру, широкие возможности и относительно легка в освоении. Этим, а также тем, что она подходит как для выполнения простых расчетов, так и для решения сложнейших задач, связанных с анализом данных, можно объяснить столь большую популярность программы.
Данное пособие, поможет вам разобраться с тонкостями работы в Microsoft Excel. С ее помощью вы научитесь выполнять задания разной сложности и направленности, а также узнаете не известные вам ранее приемы работы.
|
|
|
Все приложения, которые входят в пакет Microsoft Office, имеют похожий интерфейс. Во всех программах есть рабочая область, панели инструментов, главное меню и область задач. Поэтому если вы уже знакомы с одной из программ пакета, то вам будет легко освоить основы работы в остальных. Если вы не знакомы с программами пакета Microsoft Office, то вам все равно будет довольно легко разобраться с интерфейсом Excel, так как многие элементы окна программы такие же, как в других приложениях Windows. Это и область заголовка, и главное меню, и строка состояния.
Ячейки являются основным элементом окна программы Microsoft Excel, её рабочей областью. Любая таблица (не обязательно электронная) разделена на столбцы и строки, которые образуют ячейки. В эти ячейки записывается различная информация: числа, формулы, текст и пр. Для ввода данных в ячейку достаточно щелкнуть на ней мышью и ввести информацию. Ячейка, на которой в данный момент установлен маркер, называется активной.
Если вы потеряли активную ячейку из поля зрения, просматривая длинную таблицу, то можете быстро вернуться к ней, воспользовавшись сочетанием клавиш Ctrl+Backspace. Можно также нажать одну из клавиш управления курсором, при выполнении данного действия активной станет соседняя ячейка относительно той, которая была активной ранее. Таким образом, вы вернетесь к требуемой части таблицы.
|
|
|
Содержимое ячеек легко изменить. При изменении числа, которое используется при вычислениях, автоматически изменяется результат. Этим электронные таблицы выгодно отличаются от обычных, приведенных на бумаге.
Microsoft Excel позволяет вносить в таблицу данные, ширина которых больше ширины ячейки, но такие данные, возможно, будут выведены не полностью. Длинные строки текста могут переходить на соседние ячейки, если они свободны. В противном случае эти строки прерываются на границе ячейки. Если число не помещается в ячейку, то оно или выводится в показательной форме (например, 3,14Е+9), или заменяется знаками решетки #.
Имейте в виду, что Microsoft Excel в вычислениях использует даже те данные, которые не помещаются в ячейке. Текст и числа будут выведены полностью, если увеличить ширину столбца до нужного размера.
В Microsoft Excel, как и в Word, встроена автоматическая проверка орфографии. Поэтому если текст, введенный в ячейку, содержит орфографическую ошибку, то программа подчеркнет ее красной волнистой линией. Столбцы и строки электронной таблицы имеют заголовки, которые расположены вдоль верхней (A, B,C….) и левой границы таблицы (1,2,3,…).
|
|
|
Заголовки столбцов обозначаются латинскими буквами А и далее, а заголовки строк – арабскими цифрами от 1 до 65 356. Таким образом, самая большая таблица, созданная в программе, теоретически может содержать более 16 млн. ячеек. Естественно, на практике такая огромная таблица неудобна и требовательна к ресурсам компьютера. Таким образом, каждая ячейка имеет свой уникальный адрес, который состоит из имени столбца и номера строки (например, А5, С65, Н73). Адрес активной ячейки отображается в поле Имя, которое расположено под панелями инструментов, слева от строки формул.
Одно из основных свойств электронных таблиц – способность хранить в ячейках не только статические данные, но и формулы, по которым программа может вычислять и выводить результат. Строка формул представляет собой поле, в котором отображается содержимое активной ячейки. Если в активную ячейку введена формула (выражение, которое начинается со знака равенства), то в строке формул вы увидите введенное в нее выражение. При этом в самой ячейке отображается не формула, а результат вычисления по ней.
Начало работы
1. Запустите программу Excel (Пуск → Программы → Microsoft Excel) и создайте новую рабочую книгу (команда Файл → Создать или кнопка «Создать» на стандартной панели инструментов).
|
|
|
2. Сохраните Книгу в Вашей папке (Файл → Сохранить как → папка Мои документы → папка с номером вашей группы (наприм. КТЭИ-14 → создайте папку с Вашей фамилией (рис. 2.1)). В течение работы периодически сохраняйте свою рабочую Книгу (Файл → Сохранить либо сочетание клавиш <Ctrl+S>).
Рис. 2.1 Создание папки при сохранении
Практическая часть I
Задание 1. Формирование структуры таблицы и заполнение ее постоянными данными
Подготовьте электронную экзаменационную ведомость, форма которой представлена на рис. 3.1.
Рис. 3.1. Форма экзаменационной ведомости
Технология работы:
1. Запустите программу Excel (Пуск → Программы → Microsoft Excel) и создайте новую рабочую книгу (команда Файл → Создать или кнопка Создать на стандартной панели инструментов).
2. Введите в указанные ячейки (табл. 3.1) тексты заголовка и шапки таблицы, после чего отформатируйте ячейки и данные.
Таблица 3.1
3. Заполните ячейки столбца B данными о студентах учебной группы (приблизительно 10-15 строк) и отформатируйте данные.
4. Присвойте каждому студенту порядковый номер: введите в ячейку A6 число 1; установите курсор в нижний правый угол ячейки A6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить. Либо в ячейке А6 число 1, в А7 2, выделить обе ячейки, установить курсор в нижний правый угол ячейки A7 так, чтобы указатель мыши приобрел изображение креста и, нажав левую кнопку мыши, протяните курсор на требуемый размер
5. После списка студентов в нижней части таблицы введите в ячейки столбца A текст итоговых строк согласно рис. 3.1.
6. Объедините две соседние ячейки для более удобного представления текста итоговых строк. Для этого выделите две ячейки; вызовите контекстное меню и выберите команду Формат ячеек; на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку ОК.
7. Сохраните рабочую книгу в своей рабочей папке (имя файла - Session).
Задание 2. Технология работы с формулами
В рабочей папке с именем Session рассчитайте:
- количество неявок и оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), полученных в данной группе;
- общее количество полученных оценок.
Предлагается следующий алгоритм для расчета.
1. Ввести дополнительно 5 столбцов.
2. В каждую ячейку ввести формулу по следующему правилу: для каждого студента в соответствующем столбце оценок ставится 1, а в остальных столбцах - 0.
3. В нижней части таблицы ввести формулы подсчета суммарного количества полученных оценок определенного вида и общее количество оценок.
4. Скопировать несколько раз (по числу экзаменов в сессию) этот шаблон на другие листы и провести коррекцию оценок по каждому предмету.
Технология работы
1. Загрузите рабочую книгу с именем Session.
2. Введите названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов.
3. Используя Мастер функций, введите в столбцы F5-J5 вспомогательные формулы. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:
- установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций;
- в 1-м диалоговом окне выберите вид функции (Категория - логические; Функция - ЕСЛИ) и щелкните по кнопке ОК;
- во втором диалоговом окне введите соответствующие операнды логической функции (Логическое выражение - D6 = 5; значение_если_ истина - 1; Значение_если_ ложно - 0) и щелкните по кнопке ОК.
4. С помощью Мастера функций введите формулы аналогичным способом в остальные ячейки данной строки.
5. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:
- выделите блок ячеек F6:J6;
- установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав левую кнопку мыши, перетащите ее до конца таблицы;
6. Определите имена блоков ячеек по каждому дополнительному столбцу.
Рассмотрим эту технологию на примере столбца F:
- выделите все значения дополнительного столбца и введите команду Вставка —› Имя —› Присвоить;
- в диалоговом окне в строке Имя введите слово ОТЛИЧНО и щелкните по кнопке Добавить;
- проводя аналогичные действия с остальными столбцами, создайте еще имена блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
7. Выделите столбцы F - J целиком и сделайте их скрытыми (команда Формат —› Столбец —› Скрыть).
8. Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Покажем эту технологию на примере подсчета отличных оценок:
- установите указатель мыши в ячейку С17 подсчета количества отличных оценок и щелкните по кнопке Мастер функций;
- в диалоговом окне выберите: Категория - Математические, функция - СУММ; щелкните по кнопке ОК;
- в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка → Имя → Вставить;
- в появившемся диалоговом окне выделите имя блока Отлично и щелкните по кнопке ОК;
- повторите аналогичные действия для подсчета количества других оценок в ячейках С18 - С21.
9. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:
- установите курсор в пустой ячейке С21 и щелкните по кнопке <S>;
- выделите блок ячеек, где подсчитываются суммы по всем видам оценок, и нажмите клавишу Enter.
10. Переименуйте текущий лист:
- установите курсор на имени текущего листа и вызовите контекстное меню;
- выберите параметр Переименовать и введите новое имя, например Экзамен 1.
11. Скопируйте несколько раз текущий лист Экзамен 1:
- установите курсор на имени текущего листа и вызовите контекстное меню;
- выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец; щелкните по кнопке ОК.
12. Сохраните рабочую книгу.
Задание 3. Подготовка ведомостей назначения студентов на стипендию по результатам экзаменационной сессии (рис. 3.2)
Рис. 3.2. Форма стипендиальной ведомости
Алгоритм действий по технологии выполнения задания:
1. Загрузите экзаменационную ведомость.
2. На новом листе создайте ведомость стипендии (рис. 3.2) и скопируйте в нее список группы из экзаменационной ведомости.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
4. Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:
- если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
- если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
- если средний балл меньше 3, стипендия не выплачивается.
5. Подсчитайте сумму стипендиального фонда всей группы.
Технология работы
1. Загрузите рабочую книгу с именем Session.
2. Создайте в этой книге новый лист − Стипендия, на который из столбцов A и B листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.
3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 3.2.
4. Укажите размер минимальной стипендии в ячейке D3.
5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия − Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.3.
Рис. 3.3. Электронная таблица Ведомость назначения на стипендию
6. Введите формулу начисления среднего балла для первого студента (ячейка С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).
7. Скопируйте формулу по всем ячейкам столбца С:
- установите курсор в ячейке С6;
- наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;
- нажмите левую кнопку мыши и протащите ее до конца этого столбца;
- просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.
8. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. Технология ввода аналогична п. 6 (только в диалоговом окне выберите параметр Имя - СЧЕТ).
9. Скопируйте формулу по всем ячейкам столбца D (аналогично п. 7).
10. Введите формулу для вычисления размера стипендии студента в ячейку E6. Эта формула должна иметь следующий вид:
=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)).
Технология ввода формулы аналогична описанной в п. 6.
Знак "$" является признаком абсолютной адресации , предшествующий имени строки (абсолютной адресации на строку) или столбца (абсолютной адресации на столбец). Например, $А10, А$10 и $А$10 задают абсолютную адресацию на столбец А, строку 10 и ячейку А10 соответственно.
11. Скопируйте эту формулу в другие ячейки столбца E (аналогично п. 7).
12. Проверьте работоспособность таблицы путем ввода других оценок в экзаменационную ведомость и изменения минимального размера стипендии.
13. Сохраните рабочую книгу.
Задание 4. Для таблицы на рис. 4 постройте два вида диаграмм - внедренную на лист с исходными данными и на отдельном листе
Рис. 3.4. Таблица успеваемости к заданию 4
Технология работы
1. Создайте новую рабочую книгу и сохраните ее под именем Diag.
2. Переименуйте Лист 1 на Успеваемость, используя контекстное меню.
3. Создайте таблицу согласно рис. 3.4, начиная с ячейки A1. В пустые ячейки с названием Факультет введите формулу вычисления среднего балла по факультету.
Для этого:
- вызовите Мастер функций, выберите категорию функций Статистическая, имя функции - СРЗНАЧ; щелкните по кнопке ОК;
- введите в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками (B2:B5), используя для этого мышь;
- скопируйте формулу в ячейку С6 столбца Высшая математика.
4. Для построения внедренной диаграммы нажмите кнопку Мастер диаграмм или выполните команду Вставка → Диаграмма.
Этап 1. Выбор типа и формата диаграммы: на вкладке Стандартные выберите тип диаграммы Гистограмма и вид диаграммы - номер 1; щелкните по кнопке Далее.
Этап 2. Выбор и указание диапазона данных для построения диаграммы:
o на вкладке Диапазон данных установите переключатель Ряды в столбцах и выделите диапазон данных A2:C6;
o в том же диалоговом окне щелкните по вкладке Ряд, в строке с названием Ряд1, установите курсор в строке Имя и щелкните в ячейке В1 с названием Информатика;
o в окне Ряд щелкните по названию Ряд2, установите курсор в строке Имя и щелкните в ячейке С1 с названием Высшая математика;
o для задания подписей по оси X щелкните в строке Подписи оси X, выделите данные первого столбца таблицы (диапазон A2:A6) и щелкните по кнопке <Далее>.
Этап 3. Задание параметров диаграммы:
на вкладке Заголовки введите названия в соответствующих строках:
| Название диаграммы: Сведения об успеваемости Ось X: Учебные группы Ось Y: Средний балл |
- на вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;
- щелкните по кнопке Далее.
Этап 4. Размещение диаграммы: установите переключатель Поместить диаграмму на имеющемся листе, выберите из списка лист Успеваемость и щелкните по кнопке Готово. В результате на рабочем листе будет создана внедренная диаграмма, представленная на рис. 3.5.
Сведения об успеваемости
Рис. 3.5. Диаграмма типа Гистограмма для задания 4
5. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п. 4, но на четвертом шаге установите переключатель На отдельном листе.
Дата добавления: 2021-11-30; просмотров: 38; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!