Электронные таблицы
Пример 1: В MS Excel заполнить таблицу 1. Вычислить суммарное количество баллов для каждого абитуриента, подсчитать средние значения по каждому абитуриенту и экзамену. В столбце Сообщение о зачислении использовать функцию ЕСЛИ для сообщения «принять», если сумма баллов больше проходного (>181), и сообщение «отказать» в остальных случаях. Определить количество абитуриентов, принятых в вуз. Построить столбиковую гистограмму по общему баллу.
Таблица 1
Сведения о зачислении в университет
ФИО абитуриента | Мате-матика | Рус-ский язык | Инфор-матика | Общий балл | Ср. балл | Зачисле-ние |
Иванов И.И. | ||||||
Петров П.П. | ||||||
Буль В.В. | ||||||
Соркин Е.П. | ||||||
Мороз А.Л. | ||||||
Гусев Р.И. | ||||||
Мороз Д.Л. | ||||||
Гвоздев С.А. | ||||||
Козлов Н.Н. | ||||||
Средний балл за экзамен |
Решение. Откроем рабочую книгу MS Excel: Пуск → Все программы → Microsoft Office → Microsoft Excel.
Далее необходимо поменять название рабочего листа: щелкнем правой кнопкой мыши внизу по названию Лист1, выберем пункт Переименовать и введем с клавиатуры новое имя Пример1.
Введем в таблицу исходные данные:
1. Щелкнем по ячейке А1 и введем название таблицы: Сведения о зачислении в университет
2. В ячейки А3: G3 введем шапку таблицы 1.
3. В ячейки А4: А12 введем ФИО студентов.
4. В ячейки В4: D12 введем числа, которые представляют собой полученные на экзаменах индивидуальные баллы абитуриентов.
5. В ячейке Е4 необходимо посчитать общий балл абитуриента Иванова, который равен сумме баллов по всем дисциплинам, т.е. B4+C4+D4. Для этого выделим ячейку Е4 (один раз щелкнув по ней левой кнопкой мыши) и введем по-английски с клавиатуры следующую формулу: =B4+C4+D4 и нажмем Enter для расчета. Это ручной способ ввода формул.
6. Освоим автоматический способ ввода формулы. Выделим ячейку Е5, щелкнув на панели инструментов на значке Автосумма , получим следующую формулу =СУММ(B5:D5). Для ввода формулы нажмем Enter.
Далее таким же способом необходимо посчитать весь столбец общих баллов (Е6:Е12);
Для расчета среднего балла воспользуемся стандартной функцией MS Excel СРЗНАЧ (число1; число2;...), которая возвращает среднее (арифметическое) своих аргументов. Число1, число2,... – это от 1 до 30 аргументов, для которых вычисляется среднее. Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
1. Выделим ячейку F4. В меню Вставка → Функция выберем категорию Статистические ифункцию СРЗНАЧ.
2. В поле Число1 введем с клавиатуры или выделим мышью диапазон ячеек (B4:D4). В поле Аргументы функции сразу появится результат, нажмем Enter или Ок.
3. Аналогично посчитаем весь столбец Ср. балл. (см. рис. 1) для всех абитуриентов и средний балл по предмету.
Рис. 1. Результаты выполнения примера 1
Рассмотрим метод выдачи результата о зачислении абитуриента. Если сумма баллов превышает 181, то необходимо выдать сообщение «принять», в противном случае – «отказать».
Воспользуемся логической функцией ЕСЛИ,которая возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Синтаксис функции:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае – ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.
Значение_если_истина – это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь – это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Значение_если_ложь может быть формулой.
Для нашего примера в ячейку G4 необходимо либо ввести с клавиатуры, либо через меню Вставка → Функция следующую формулу:
=ЕСЛИ(E4>180;"принять";"отказать")
Microsoft Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для вычисления числа появлений текстовой строки или числа в диапазоне ячеек используйте функцию СЧЁТЕСЛИ. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используйте функцию СУММЕСЛИ.
Для расчета количества абитуриентов, принятых в ВУЗ воспользуемся функцией СЧЁТЕСЛИ,котораяподсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
СЧЁТЕСЛИ(диапазон;критерий),
где диапазон – диапазон, в котором нужно подсчитать ячейки; критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". Выделим ячейку G13 и введем с клавиатуры или через меню Вставка → Функция следующую статистическую функцию: =СЧЁТЕСЛИ(G4:G12;"принять")
Для расчета рейтинга (ранга) каждого абитуриента по общему баллу необходимо применить функцию РАНГ, которая возвращает ранг (рейтинг) каждого абитуриента в списке чисел. Ранг числа – это его величина относительно других значений в списке.
В MS Excel для вычисления ранга используется функция
РАНГ (число; ссылка; порядок), где
число – адрес на ячейку, для которой определяется ранг (E4);
ссылка – ссылка на массив общих баллов (Е4:Е12);
порядок – число, определяющее способ упорядочения. Если порядок равен 0 (нулю), или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если порядок – любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.
На следующем этапе необходимо оформить таблицу так, чтобы при распечатке она красиво выглядела на бумаге. В программе MS Excel предусмотрены разнообразные средства, с помощью которых вы можете по своему вкусу оформить таблицу – изменить шрифт, цвет, заливку и границы.