Создание и заполнение таблицы постоянными формулами. Построение, редактирование и форматирование диаграмм

Выполнили: студенты группы МЛ-131

Волженцев А.В.

Попов Г.В.

Березовская К.С.

Проверил: Короткевич С.И.

Воронеж 2013

Задание № 1

excel число фильтрация диаграмма

Сформировать структуру таблицы и заполнить ее постоянными значениями -- числами, символами, текстом.

Ход работы.

1) Загружаю с жесткого диска созданный в работе 1 шаблон экзаменационной ведомости с именем Session:

выполняю команду Файл, Открыть;

в диалоговом окне установливаю следующие параметры:

Папка: МЛ-131 Волженцев

Имя файла: Session

Тип файла: Шаблоны

2) Ввожу в указанные в табл. 3.2 ячейки, тексты заголовка и шапки таблицы в соответствии с рис. 3.8 по следующей технологии:

-установливаю указатель мыши в ячейку В1, и щелкните левой кнопкой;

- ввожу текст (см. табл. 3.2) и нажимаю клавишу ввода <Enter>;

- перемещаю указатель мыши в следующую ячейку, например в ячейку А3 и щелкните левой кнопкой;

- ввожу текст, нажмите клавишу ввода <Enter> и т.д.

3) Форматирую А1:Е1:

выделяю блок ячеек, нажимаю правую кнопку мыши для вызова контекстного меню;

ввожу команду контекстного меню Формат ячеек;

на вкладке Выравнивание выбираю опции:

По горизонтали: по центру выделения

По вертикали: по верхнему краю

нажав кнопку <Размер>, выбираю размер шрифта, например 14 пт;

выделяю текст жирным шрифтом, нажав на панели инструментов кнопку

4) Проделываю подготовительную работу для формирования шапки таблицы, задав метры выравнивания вводимого текста:

выделяю блок ячеек A3:J5, где располагается шапка таблицы;

вызываю контекстное меню и выбираю команду Формат ячеек;

на вкладке Выравнивание задаю параметры:

По горизонтали: по значению

По вертикали: по верхнему краю

Переносить по словам: поставить флажок

Ориентация: горизонтальный текст (по умолчанию)

нажмаю кнопку <ОК>.

5) Устанавливаю ширину столбцов таблицы в соответствии с рис. 3.8. Для этого:

подвожу указатель мыши к правой черте клетки с именем столбца, например В, так, чтобы указатель изменил свое изображение

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

аналогичные действия проделываю со столбцами А, С, D, Е, F-J.

6) Заполняю ячейки столбца В данными о студентах учебной группы,13 строк.

7) Присвойте каждому студенту порядковый номер:

ввожу в ячейку А6 число 1;

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

8) После списка студентов в нижней части таблицы согласно рис. 3.8 ввожу в ячейки , столбца А текст итоговых строк; Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО.

9) Объединяю две соседние ячейки для более удобного представления текста итоговых строк. Технологию объединения двух ячеек столбцов А и В, в которых будет расположена надпись Отлично:

выделяю две ячейки;

вызываю контекстное меню и выбираю команду Формат ячеек;

на вкладке Выравнивание устанавливаю флажок Объединение ячеек и нажимаю кнопку <ОК>;

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

10) Сохраняю рабочую книгу, для которой файл будет иметь тип xls:

выполните команду Файл, Сохранить как;

в диалоговом окне установите следующие параметры:

Результат работы:

Задание №2

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

количество оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), неявок, полученных в данной группе;

общее количество полученных оценок.

Ход работы.

1. Загружаю с жесткого диска рабочую книгу с именем Session:

выполните команду Файл, Открыть;

Папка: МЛ-131 Волженцев

Имя файла: Session

Тип файла: Книга Microsoft Excel

2. Проделаю подготовительную работу, вводя названия (5, 4, 3, 2, неявки) соответственно в ячейки F3, G3, Н3, I3, J3 вспомогательных столбцов (см. рис. 3.9).

3. В эти столбцы F - J ввожу формулы.

Для ввода исходных формул пользуюсь Мастером функций. Рассмотрим технологию на примере ввода формулы в ячейку F5:

устанавливаю курсор в ячейку F5 и выбираю мышью на панели инструментов кнопку Мастера функций;

в 1-м диалоговом окне выбираю вид функции

Категория -- логические

Имя функции -- ЕСЛИ

щелкаю по кнопке<ОК>;

во 2-м диалоговом окне, устанавливая курсор в каждой строке, ввожу соответствующие операнды логической функции:

Логическое выражение -- D5

Значение, если истина, -- 1

Значение, если ложно, -- 0

щелкаю по кнопке <ОК>

4. С помощью Мастера функции ввожу формулы аналогичным способом в остальные ячейки данной строки.

5. Копирую эти формулы во все остальные ячейки дополнительных столбцов:

выделяю блок ячеек F6 ;J6;

устанавливаю курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протаскиваю ее до конца таблицы Экзаменационная ведомость;

выбираю в контекстном меню команду Заполнить значения.

6. Определяю имена блоков ячеек по каждому дополнительному столбцу. Рассмотрим это на примере дополнительного столбца F: .

выделяю все значения дополнительного столбца, например F6.: адрес ячейки в столбце, в которой находится последнее значение

ввожу команду Вставка, Имя, Присвоить;

в диалоговом окне в строке Имя ввожу слово ОТЛИЧНО:

щелкаю по кнопке <Добавить>;

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

7. Выделяю столбцы F - J целиком и делайте их скрытыми:

устанавливаю курсор на названии столбцов и выделяю столбцы F - J;

ввожу команду Формат, Столбец, Скрыть.

8. Ввожу формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Рассмотрим это на примере подсчета количества отличных оценок:

устанавливаю указатель мыши в ячейку С 13 подсчета количества отличных оценок;

щелкаю по кнопке Мастер функций,

в диалоговом окне Мастер функций выбираю: Категория -- Математические, функция -- СУММ; щелкаю по кнопке <ОК>;

в следующем диалоговом окне в строке Число 1 устанавливаю курсор и ввожу команду Вставка, Имя, Вставить;

в появившемся диалоговом окне выделяем имя блока ячеек Отлично, щелкаю по кнопке <ОК>;

повторяю аналогичные действия для подсчета количества других оценок в ячейках С14-С17.

9. Переименовываю текущий лист:

10. Копируйте несколько раз текущий лист Экзамен1:

11. Выполняю команду Сервис / Параметры, вкладка Вид и устанавливаю флажок Формулы. Сравните свой результат с рис. 3.10, а затем, повторно выполнив команду Сервис, Параметры и сняв флажок Формулы, сравниваю свои результаты с рис. 3.9.

12. Сохраните рабочую книгу с экзаменационными ведомостями

Результат работы.

Задание №3

Подготовьте для каждой группы ведомость назначения студентов на стипендию по результатам экзаменационной сессии.

Ход работы.

1. Загружаю с жесткого диска рабочую книгу с именем Session:

Папка: МЛ-131 Волженцев

Имя файла: Session

Тип файла: Книга Microsoft Excel

2. Создаю в этой книге новый лист-- Стипендия, на который из столбцов А и В листа Экзамен 1 скопирую фамилии и порядковые номера студентов.

3. Оформляю название и шапку ведомости назначения на стипендию согласно рис. 3.11.

4. Указываю размер минимальной стипендии в ячейке D3;

5. Вставляю два дополнительных столбца перед столбцом Стипендия и ввожу их названия -- Средний балл и Кол-во сданных экзаменов. Сверяю полученное изображение электронной таблицы с рис. 3.12.

6. Ввожу формулу вычисления среднего балла студента в ячейку С6 для первого студента, например Снегирева. Для этого:

устанавливаю курсор в ячейке С6;

щелкаю по кнопке <Мастер функций> на панели Стандартная и выбираю в диалоговом окне параметры:

Категория: Статистические

Имя: СРЗНАЧ

щелкаю по кнопке <ОК>, появляется панель ввода аргументов функции СРЗНАЧ;

устанавливаю курсор в 1-й строке панели ввода аргументов функции, щелкаю на названии листа Экзамен 1 и выбираю ячейку D6 с оценкой конкретного студента по первому экзамену;

устанавливаю курсор во 2-й строке,щелкаю на названии листа Экзамен 1(2) и выбираю ячейку D6 с оценкой того же студента по второму экзамену;

устанавливаю курсор в 3-й строке, щелкаю на названии листа Экзамен 1(3) и выбираю ячейку D6 с оценкой того же студента по второму экзамену;

щелкаю по кнопке <ОК>;

в ячейке С6 появится значение, рассчитанное по формуле

=СРЗНАЧ('Экзамен 1'!D6;'Экзамен 1(2)'!D6;'Экзамен 1(3)'!D6).

7. Копируйте формулу по всем ячейкам столбца С. Для этого:

устанавливаю курсор в ячейке С6;

навожу указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;

нажимаю левую кнопку мыши и протаскиваю ее до конца этого столбца;

8. Ввожу в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок.

9. Копируюформулу по всем ячейкам столбца D так же, как вы делали в п.6.

10. Ввожу формулу для вычисления размера стипендии студента в ячейку Е6. Формула имеет следующий вид:

=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1.5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0))

Результат работы.

Вывод:

В результате проделанной работы были получены практические навыки работы с программой «Microsoft Excel».

Задание №3

1. Проделали подготовительную работу:

создайте новую рабочую книгу командой Файл, Создать;

сохраните в выбранной папке созданную книгу под именем Diag командой Файл, Сохранить как;

переименуйте Лист1

установите курсор на Лист 1 и вызовите правой кнопкой мыши контекстное меню;

выполните команду Переименовать и введите новое имя -- Успеваемость.

2. Создали таблицу согласно рис. 3.27, начиная с ячейки А1. В пустые ячейки с названием Факультет ввели формулу вычисления среднего балла по факультету, например в ячейку В6 столбца Информатика:

вызовите Мастер функций, щелкнув по его кнопке на панели инструментов;

выберите категорию функций Статистическая, имя функции -- СРЗНАЧ, щелкните по кнопке <ОК>;

ввели в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками, используя для этого мышь, например В2:В5:

3. Построили внедренную диаграмму, выполнив следующие операции:

нажали кнопку Мастер диаграмм

Этап 1. Выбор типа и формата диаграммы:

на вкладке Стандартные выбрали тип диаграммы Гистограмма и вид диаграммы -- номер 1;

щелкнули по кнопке <Далее>.

Этап 2. Выбор и указание диапазона данных для построения диаграммы:

на вкладке Диапазон данных установили переключатель Ряды в столбцах;

выделили диапазон данных А2:С6;

в том же диалоговом окне щелкнули по вкладке Ряд;

в окне Ряд выделили строку с названием Ряд1, установили курсор в строке Имя и щелкнули в ячейке В1 с названием Информатика;

для создания подписей по оси Х щелкнули в строке Подписи ocu X выделили данные первого столбца таблицы, т.е. диапазон А2:А6;

· щелкнули по кнопке <Далее>.

Этап 3. Задание параметров диаграммы;

на вкладке Заголовки ввели названия в соответствующих строках:

Название диаграммы: Сведения об успеваемости

Ось X: Учебные группы

Ось Y: Средний балл

на вкладке Легенда поставили флажок Добавить легенду и переключатель Справа;

щелкнули по кнопке <Далее>.

Этап 4. Размещение диаграммы: установили переключатель Поместить диаграмму на имеющемся листе и выбрали из списка лист Успеваемость; щелкнули по кнопке <Готово>; в результате на рабочем листе будет создана внедренная диаграмма.

4. Построили диаграмму другого типа на отдельном листе. Для этого выполнили действия, аналогичные описанным в п. З, но на четвертом шаге установите переключатель На отдельном листе.

Согласно ходу работы пункта 3, внеся изменения установить переключатели на отдельном листе получим следующее: