Создание гистограммы с помощью мастера диаграмм

В рассматриваемом примере в виде диаграммы представим столбиковую гистограмму по общему баллу.

Для создания такой диаграммы нужно:

выделить столбцы А4:А12 и E4:E12 нашей таблицы (для выделения ячеек в разных местах таблицы нужно удерживать нажатой клавишу Ctrl);
затем щелкнуть на кнопке Мастер диаграмм на стандартной панели инструментов;
на первом шаге из 4 выберите Тип диаграммы Гистограмма, а затем щелкните на кнопке Далее;
на шаге 2 в окне Источник данных диаграммы должна быть нажата кнопка Столбцах, на вкладке Ряд введите имя Общий балл, а затем Далее;
третье окно мастера диаграмм состоит из нескольких разделов (вкладок), предназначенных для задания различных параметров диаграммы. В окне Параметры диаграммы на вкладке Заголовки введите название диаграммы Общий балл, отмените легенду на вкладке Легенда, а затем Далее;
в последнем окне Размещение диаграммы выберите на имеющемся щелкните на кнопке Готово.

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

Редактирование любых диаграмм и графиков осуществляется при помощи выделения «мышью» любых объектов и нажатия правой ее кнопки. При этом в появившемся контекстном меню необходимо выбрать Формат объекта.

Задание 4. Составим таблицу для подсчета баллов студентов за семестр. Итоговый лист должен выглядеть следующим образом, обведенные жирными квадратами ячейки содержат формулы и должны в результате заполниться автоматически, поэтому НЕ ЗАПОЛНЯЙТЕ данными ячейки, взятые в жирные рамки:

Создание таблиы

1. Заголовок – выделим первую строку: левая кнопка мыши по номеру строки 1, размер шрифта 20. В ячейку А1 вносим сведения о факультете, курсе, группе, дисциплине и т.д.

2. Шапка таблицы. Установить курсор между нумерацией строк 1 и 2 до появления знака: нажать левую кнопку мыши и растянуть строку. Правая кнопка мыши (п.к.м) по номеру строки 2 → Формат ячеек …→ Выравнивание: по горизонтали – по центру; по вертикали – по центру; Отображение: переносить по словам. В ячейки вносим: А2 – № п/п (ячейку сужаем); B2 Фамилия Имя (расширяем); T2 – зачет; U2 – оценка. Выделяем ячейки от С2 до S2: (п.к.м) по выделенному → Формат ячеек …→ Выравнивание: Ориентация 90º. Уменьшим ширину ячеек до 30 пикселей, используя команду Копировать формат , выделим ячейки с D2 до S2. В ячейку С2 вносим текст семинар 1, ставим курсор мыши в нижний правый угол с квадратиком, до появления черного тонкого креста ┼, нажмем левую кнопку и протащим мышь до семинар 12 – это называется автозаполнение.

3. Заполнение таблицы. Столбец А заполняем номерами по порядку – в А3: 1, в А4: 2, выделим номера и автозаполнением протягиваем до 12. Вносим фамилии. Строка Баллы максимум – в ячейку O16 внесем формулу суммы: команда автосумма , и выделить диапазон ячеек (C16:N16) и в S16 – аналогично формулу =СУММ(O16:R16). Подбираем баллы таким образом, чтобы в ячейке S16стало 100. Копируем формулу из ячейки O16 в ячейку O3 и автозаполнением копируем сумму для всех студентов. Поступаем аналогично с формулой в ячейке S16.

4. Перевод набранных баллов в оценку. «Зачтено» ставится при условии, что студенты набрали не менее 40 баллов. Для выставления оценки можно использовать формулу:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

- лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ;

- значение_если_истина – это значение, которое возвращается, если лог_выражение равно ИСТИНА;

- значение_если_ложь – это значение, которое возвращается, если лог_выражение равно ЛОЖЬ; значение_если_ложь может быть формулой или функцией.

5. В ячейку Т3 запишем формулу: =ЕСЛИ(S3>40;"зачтено";"не зачтено"), ячейку S3 можно указать мышкой. Необходимо учесть случай, когда студенты по каким-либо причинам не явились на зачет, для этого редактируем функцию:

- Установить курсор мыши строку формул –

- Отредактировать формулу:

=ЕСЛИ(Q3="";"н/я";ЕСЛИ(S3>40;"зачтено";"не зачтено"))

- Автозаполнением копируем формулу для всех студентов

- В ячейку U3 запишем формулу на случай дифференцированного зачета:

ЕСЛИ(S3>79;"5(отл)";ЕСЛИ(S3>59;"4(хор)";ЕСЛИ(S3>39;"3(удов)";"2 (неудов)")));

- автозаполнением копируем формулу для всех студентов.

6. Обработка результатов группы. Подсчитаем количество студентов получивших «зачтено», «не зачтено» и не явившихся на зачет. Используем формулу СЧЁТЕСЛИ(диапазон;критерий):

Диапазон – диапазон, в котором нужно подсчитать ячейки.

Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

Установить курсор в ячейку T19

Вставка → Функция … → Категория: Статистические; Выберите функцию: СЧЁТЕСЛИ. ОК

Диапазон: T3:T14 с отметками о зачете, диапазон не должен меняться при подсчетах остальных отметок, поэтому ссылка должна быть абсолютной, необходимо нажать клавишу F4, в результате получиться: $T$3:$T$14;

Критерий: ячейка R19, в которой должно быть внесено «зачтено». ОК

Автозаполнением копируем формулу на все виды отметок.

Аналогично поступая, заполняем подсчет оценок в ячейках U23 … U26.

Рейтинг студентов. Переименуем Лист1 в «список»: кликните правой кнопкой мыши по названию листа Лист1 в меню выбрать переименовать и дать новое имя: список. Скопируем полученный лист «список»: правая кнопка мыши по названию листа Список → Переместить/скопировать→Создать копию→(Перед листом: Лист 2) →Оk; Переименовать лист список(2) в лист «рейтинг». На листе «рейтинг» выделим ячейки с фамилиями студентов и их результатами: В3:U14. Отсортируем список по общему набранному количеству баллов: Данные → Сортировка … → Сортировать по: Итого за семестр; по убыванию; Затем по: Фамилия Имя; по возрастанию → ОК

7. Построение диаграмм:

выделим столбцы Т19:Т21;

щелкнуть по образу Мастер диаграмм на стандартной панели инструментов;

на первом шаге из 4 выберите Тип диаграммы Круговая, Объемная, кнопка Далее;

на шаге 2 в окне Источник данных диаграммы должно быть выбрано Ряды в: столбцах, на вкладке Ряд – Подписи категорий: выделите на листе список значения отметок о зачете, в результате получиться =список!$R$19:$S$21, кнопка Далее;

третье окно мастера диаграмм состоит из нескольких разделов (вкладок), предназначенных для задания различных параметров диаграммы. На вкладке Легенда, размещение: вверху, Подписи данных: доли; кнопка Далее;

в окне Размещение диаграммы Поместить диаграмму на листе: отдельном щелкните на кнопке Готово;

для печати залить диаграмму, используя только черно-белую гамму цветов. Выделить долю – щелчок левой и еще раз щелчок (с расстановкой), затем щелчок правой кнопкой мыши, выбрать: Формат точки данных …→ кнопка Способы заливки → вкладка Узор → Штриховка: черная, Фон: белый; выберем тип узора. Аналогично, остальные доли. Шрифт легенды и подписей не менее 24 пт. (Рис. 1)

Самостоятельно постройте диаграмму по оценкам (Рис.2).