kontr_excel

Вформуле =ЕСЛИ(ABS(B2-B3)<$G$1;C3;"") использована абсолютная ссылка $G$1, указывающая на ячейку таблицы, в которой записано значение заданной точности вычисления суммы членов числового ряда (e=0.0001). Ее адрес не будет изменяться при копировании формулы.

Для выделения цветом ячейки, в которой найдено значение суммы числового ряда (в диапазоне ячеек D3:D30), вычисленное с заданной точностью (в нашем случае, как показано на рисунке 1, это ячейка D28), использована технология условного форматирования. Для условного форматирования воспользуемся меню «Формат/Условное форматирование».

Вячейке Е1 расположена формула, которая находит значение вычисленной с заданной точностью суммы в столбце D. Формула использует функцию поиска в диапазоне ячеек D3:D30 минимального значения: =МИН(D2:D30)

Погрешности вычисляются по формулам:

использованием процентного формата с шестью десятичными знаками.

В настройках Excel (меню «Сервис/Параметры») отключен режим отображения сетки.

Построим график, для чего выделим интервал ячеек B2:B30 и запустим мастер диаграмм. Выполняя шаги мастера, сформируем диаграмму, как показано на рисунке 1.

Используя редактор формул MS Equation, разместим на листе MS Excel формулу с условием решаемой задачи. Если в процессе решения задачи в Excel оформлялась и пояснительная записка в MS Word и в ней уже введена формула, то ее можно скопировать из пояснительной записки. ВНИМАНИЕ, в любом случае формула должна быть выполнена в MS Equation, а не вставлена как рисунок.

11

2.2 Задача № 2. Обработка табличных данных

Условие задачи

Составить таблицу, содержащую информацию о десяти абитуриентах, поступающих в вуз. Для каждого абитуриента известны результаты ЕГЭ (математика, физика или информатика), результаты сдачи экзамена по русскому языку («зачет» «незачет»).

Определить:

−сумму набранных баллов по результатам ЕГЭ, сумму баллов у допущенных к участию в конкурсе на основании результатов экзамена по русскому языку, процент набранных каждым участником баллов по отношению к максимальному значению набранных баллов. Построить гистограмму сумм набранных абитуриентами баллов по результатам сдачи ЕГЭ.

−средний балл, максимальный и минимальный баллы по предметам и по сумме набранных баллов по результатам ЕГЭ.

−количество абитуриентов, сдававших ЕГЭ по каждому предмету, количество имеющих «зачет» по русскому языку.

При решении задачи не допускается использование в MS Excel автофильтра и расширенного фильтра.

Решение

На листе 2 рабочей книги MS Excel составим таблицу, показанную на рисунке 2, предварительно переименовав лист и присвоив ему имя «Задача 2».

Рисунок 2 – Таблица вычисления суммы числового ряда

12

Вячейку A1 введем название таблицы – « Абитуриенты» и отформатируем его: объединим ячейки интервала A1:H1; выровним текст по центру; изменим размер и начертание шрифта.

Первый столбец таблицы (столбец A) – « Фамилия». Введем в ячейку A3 текст «Абитуриент 1», а затем с использованием технологию заполнения ячеек рядом значений заполним интервал ячеек

A3:A12.

Столбцы с заголовками «Математика», «Физика» и «Информатика» заполним числовыми данными, отражающими результаты сдачи абитуриентами ЕГЭ по предметам. В Столбце «Русский язык» разместим результаты сдачи экзамена по русскому языку («зачет» «незачет»).

Для расчета суммы набранных баллов по результатам ЕГЭ в ячейке F3 разместим формулу =СУММ(B3:D3). Скопируем эту формулу в интервал ячеек F4:F12.

Для расчета суммы баллов у допущенных к участию в конкурсе на основании результатов экзамена по русскому языку в ячейке G3 разместим формулу =ЕСЛИ(E3="зачет";F3;0). Скопируем эту формулу в интервал ячеек G4:G12.

Определим процент набранных каждым участником баллов по отношению к максимальному значению набранных баллов. Для этого

вячейку H3 поместим формулу =G3/$F$14 и скопируем ее в интервал ячеек H4:H12.

Вычислим средний балл, максимальный и минимальный баллы по предметам и по сумме набранных баллов по результатам ЕГЭ. Для этого выполним следующие действия.

Вячейке B13 разместим формулу =СРЗНАЧ(B3:B12) для вычисления среднего значения результатов ЕГЭ по математике. Скопируем эту формулу в ячейки C13, D13 и F13.

Вячейке B14 разместим формулу =МАКС(B3:B12) для вычисления максимального значения результатов ЕГЭ по математике. Скопируем эту формулу в ячейки C14, D14 и F14.

Вячейке B15 разместим формулу =МИН(B3:B12) для вычисления минимального значения результатов ЕГЭ по математике. Скопируем эту формулу в ячейки C15, D15 и F15.

Для расчета количества абитуриентов, сдававших ЕГЭ, используем функцию суммирования ячеек в соответствии с заданным усло-

вием. Для этого в ячейке B16 разместим формулу

13

=СЧЁТЕСЛИ(B3:B12;">0"). Скопируем эту формулу в ячейки C16,

D16.

Для расчета количества абитуриентов, имеющих «зачет» по русскому языку также, используем функцию суммирования ячеек в соответствии с заданным условием. Для этого в ячейке E16 разместим формулу =СЧЁТЕСЛИ(E3:E12;"зачет").

Отформатируем таблицу, используя различные типы линий для обозначения границ как показано на рисунке 2.

Построим на этом же листе (лист «Задача 2») гистограмму сумм набранных абитуриентами баллов по результатам сдачи ЕГЭ. Для построения гистограммы выделим в таблице два несвязанных интервала ячеек A3:A12 и F3:F12. Несвязанные интервалы можно выделить используя манипулятор «мышь» при нажатой клавише «CTRL». После выделения интервалов запустим мастер диаграмм и выполняя шаги мастера, сформируем диаграмму, как показано на рисунке 3.

Рисунок 3 – Гистограмма сумм набранных баллов Установим защиту на ячейки, содержащие формулы, название

таблицы, а также на ячейки заголовков таблиц (на рисунке 2 область исходных данных, в которой ячейки не должны быть защищены, выделена сплошной двойной линией). Выделим интервал ячеек A3:E12 и отменим блокировку ячеек (меню «Формат/ячейки, вкладка «Защита», убрать ключ «Защищаемая ячейка»). Защитим лист рабочей книги от изменений (меню «Сервис/Защита/Защитить лист»).

14

2.3 Задача № 3. Работа со списками

Пример решения задачи

Для демонстрации возможностей работы со списками в электронной таблице MS Excel необходимо подготовить примеры и разместить их на листе рабочей книги.

Например, “ Автокаталог”. ВНИМАНИЕ! Вы должны придумать свой оригинальный пример и подготовить текст, опи- сывающий технологию работы с таблицами в соответствии в Вашим примером. Пример, приведенный в методических указаниях,

не может быть использован при выполнении контрольной работы.

На рисунках 4-10 показаны фрагменты таблиц списка (база данных): на тему «Автокаталог», размещенных на листе "Задача 3" рабочей книги Excel.

Рисунок 4 – Таблица с исходным списком

Рисунок 5 – Таблица после сортировки

15

В пояснительной записке необходимо описать технологию работы со списками в электронной таблице MS Excel (создания списка, сортировки и фильтрации данных, подведения промежу- точных и общих итогов, создания структуры данных, создания сводной таблицы и диаграммы), включив в текст описания на- звания заголовков полей списка и рисунки экранных форм рабо- чего окна MS Excel с примерами подготовленных таблиц.

Фрагмент описания технологии работы со списками в MS Excel приведен ниже.

Предметная область списка (база данных): “ Автокаталог” .

Список представляет собой набор строк таблицы, содержащий связанные (по смыслу) данные. Список может выступать в качестве базы данных, если соблюдаются следующие условия: строки таблицы выступают в качестве записей, столбцы таблицы являются полями, заголовки столбцов, то есть первая строка списка, становятся именами полей базы данных [1].

В строке 4 листа рабочей книги Excel "Задача 3" введем заголовки полей списка (базы данных): «Номер»; «Марка»; «Цвет»; «Пробег»; «Год выпуска»; «Объем двигателя»; «Стоимость, тыс. руб.». Заполним таблицу данными как показано на рисунке 11. Всего заполнено 20 строк списка.

Рисунок 11 – Список (база данных) «Автокаталог»

18

Сортировка.

Отсортировать список можно в алфавитном, числовом и хронологическом порядке. При этом можно задать возрастающий, убывающий и пользовательский порядок сортировки [2].

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

При убывающем порядке сортировки все числа столбца сортируются от наибольшего положительного к наименьшему отрицательному; значения даты и времени располагаются в порядке от самого позднего к самому раннему; текстовые данные сортируются в обратном алфавитном порядке; список логических значений будет начинаться со значения ИСТИНА и заканчиваться значением ЛОЖЬ. Пустые ячейки будут сдвинуты в конец списка.

При применении пользовательского списка сортировки значения будут отсортированы в соответствии с четко зафиксированной последовательностью заданного списка [1].

Отсортируем список “ Автокаталог” по двум уровням - сначала по столбцу «Год выпуска», затем по столбцу «Стоимость». Сортировку выполняем по возрастанию. Для сортировки используем меню: «Данные\Сортировка». Окно выбора параметров сортировки показано на рисунке 12.

19