Табличный процессор Microsoft Excel

Табличный процессор Microsoft Excel – это программа, предназначенная для проектирования электронных таблиц и выполнения различных расчётов в них, решения математико-статистических и экономико-математических задач и др.

Загрузка (запуск) Excel и множество других операций выполняются также как в программе Word и других приложениях Windows. Поэтому в данном разделе будут приводиться алгоритмы выполнения только тех операций, которые не рассматривались при изучении Word.

После загрузки программы Excel на экране появляется окно табличного процессора, состоящее из стандартных элементов: строки заголовка, строки меню, панелей инструментов, рабочего поля. В отличие от Word, где рабочим полем является чистый лист, а файл по умолчанию носит имя «Документ1», в Excel рабочее поле представляет собой электронную таблицу, с именем файла «Книга1». Пользователь может изменить имя файла.

Электронная таблица – это двумерный массив – матрица, состоящая из столбцов и строк. Столбцы обозначаются буквами латинского алфавита A, B, C, D, …, а строки - цифрами 1, 2, 3, 4, … В Excel можно сформировать таблицу (матрицу) максимальным размером 256 столбцов X 16 384 строк.

В одной книге (файле) по умолчанию имеется 16 рабочих листов, имеющих имена: Лист1, Лист2, …, Лист16. Каждый лист содержит одну электронную таблицу. Вход в рабочие листы книги осуществляется нажатием на кнопки с их именами. Перелистывание книги осуществляется кнопками прокрутки, расположенными на одной линии с именами листов: «I◄», «◄», «►», «► I». Если щёлкнуть правой кнопкой мыши по кнопкам прокрутки, то появится список имён листов имеющихся в книге. Список позволяет войти в любой лист.

Алгоритм переименования листа: выделить старое имя листа двойным щелчком левой кнопки мыши à удалить старое имя клавишей «Delete» à ввести новое имя.

Алгоритм добавления листа: меню «Вст а вка» à команда «Л ист» à перед именем текущего рабочего листа появляется имя нового листа.

Алгоритм удаления текущего листа: меню «П равка» à команда «Удалить л ист». Команда «Удалить л ист», после её выполнения не отменяется.

Место пересечения столбца и строки электронной таблицы называется ячейкой. Каждая ячейка имеет свой адрес (или имя), состоящий из имени столбца и номера строки, например: A1, D3, F5 и т.д.

Группа рядом расположенных ячеек может объединяться в блок. Адрес (или имя) блока обозначается адресами двух угловых ячеек, записываемых через двоеточие. Например: блок B2: D3, состоит из шести ячеек: B2, C2, D2, B3, C3, D3.

Четырёхугольная рамка, при помощи которой выделяются ячейки, называется указателем ячейки (или табличным курсором). Перемещается указатель ячеек клавишами управления курсора или щелчком мыши после указания курсором на ячейку, в которую следует переместить указатель ячейки. Ячейка, выделенная указателем ячейки, становится активной ячейкой, то есть готовой для ввода данных или их редактирования, если они там находятся. Жирная точка, расположенная в правом нижнем углу указателя ячейки, называется маркером заполнения.

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

Правая часть отражает содержимое активной ячейки. Средняя часть содержит три кнопки:

«X» - отмена последнего действия (дублирование клавиши «Esc»;

«√» – ввод, выполняет функцию клавиши «Enter»;

«=» – вводит знак «=» в правую часть строки формул, одновременно в левой части строки формул появляются функции, которые можно использовать для создания формул.

Формула – это выражение служащее для выполнения математической и (или) логической операции. В состав формул входят: адреса ячеек, числовые константы, арифметические операции и функции.

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

Запись формулы начинается со знака «=». Примеры записи формул. Операции сложения и вычитания: =B5+C5+D5- E5. Операции умножения и деления: =B6*C6/D6. Операции с использованием скобок: =(B7+C7)/D7. Операция вычисления процента: =E5/D5%. Операция возведения в степень: =B5^C5, где: B5 – число, возводимое в степень, C5 – число, показатель степени.

Для вычисления итоговых сумм по столбцам и строкам применяется команда «Авто сумма». Алгоритм авто суммирования: выделяется столбец (или строка) вместе с ячейкой, в которой должна быть подсчитана сумма à кнопка «S» – «Авто сумма» à в ячейке появляется итоговая сумма.

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

#### - ширина ячейки не позволяет отобразить число в заданном формате.

#ДЕЛ/0! – выполнение деления на ноль.

#ИМЯ? – неверный набор имени ячейки. Возможно, для набора имён ошибочно использовалась кириллица, а не латиница.

#ЧИСЛО! – неверное применение математических формул. Например, извлечение квадратного корня из отрицательного числа.

#Н/Д – задана ссылка на пустую ячейку.

#ССЫЛКА! – задана ссылка на удалённую (несуществующую) ячейку.

#ЗНАЧ! – использован недопустимый тип аргумента. Например: привлечение ячейки, в которой находится текст, к выполнению арифметической операции.

#ПУСТО! – неверное указание пересечения двух областей, не имеющих общих ячеек.

Функция – это выражение позволяющее проводить сложные математические или логические вычисления. Синтаксически функция состоит из имени и аргумента, который записывается в скобки. Например, функция суммирования чисел ячеек B5+C5+D5+E5+G5 может быть записана так: СУММ(B5: G5). Если перед именем функции записать знак равенства «=», то функция превращается в формулу: = СУММ(B5: G5).

Для вставки функций в формулы используется кнопка f x – «Вставка функций». При нажатии на кнопку открывается окно «Мастер функций». В Excel используется более двухсот функций, которые подразделяются на следующие категории: финансовые, дата и время, математические, статистические, текстовые, логические и другие. Некоторые из этих функций будут рассмотрены ниже на примере выполняемых заданий.

Таблица состоит из следующих составных частей: заголовка, шапки и информационной части (входных и выходных данных). В соответствии с этим алгоритм создания таблицы в Excel включает следующие этапы: ввод заголовка таблицы; ввод имён показателей шапки таблицы; форматирование ячеек информационной части таблицы; ввод входных данных; ввод формул и функций для расчёта выходных данных; обрамление таблицы.

Задание 14. Создать таблицу:

Расчёт доплат водителям за класс и стаж работы

Алгоритм создания таблицы:

1. Ввод заголовка таблицы: установить указатель ячейки на ячейку В1 à ввести текст заголовка: «Расчёт доплат водителям за класс и стаж работы» à клавиша «Enter» (или щелчок мыши за пределами строки ввода).

2. Ввод имён показателей шапки таблицы: Расширить столбец А до размера, позволяющего вводить в графу «Фамилия И.О. водителя», соответствующие показатели. à Ввести текст в ячейки; А2 - «Фамилия И.О.», А3 - «водителя», В2 – «Оклад», С2 – «% доплаты за:», С3 – «класс», D3 – «стаж» и т.д.

3. Форматирование ячеек информационной части таблицы: графу «Фамилия И.О. водителя» не форматировать à в графе «Оклад» выполнить: выделить информационную часть графы à меню «Фор м ат» à команда «Я чейки …» à в окне «Формат ячеек» выделить пункт «Число» à в меню «Числовые форматы:» выбрать формат «Числовой» à «Число десятичных з наков:» установить 2 à клавиша ОК. Аналогично отформатировать ячейки остальных числовых граф.

4. Ввод входных данных.

5. Ввод формул расчёта выходных данных: в ячейку E4 вводится формула =B4*C4/100 à в ячейку F4 - формула = B4*D4/100 à в ячейку G4 - формула = B4+E4+F4. Алгоритм копирования формулы: установить указатель ячейки на ячейку с формулой à меню «П равка» à команда «К опировать» à выделить ячейки, в которые копируется формула à меню «П равка» à команда «В ставить». Итоги по графам таблицы подсчитать, используя команду «Авто сумма».

6. Обрамление таблицы: выделить обрамляемый блок ячеек à меню «Фор м ат» à команда «Я чейки …» à в окне «Формат ячеек» выделить пункт «Граница» à в диалоговом окне выбирается расположение обрамления, толщина линий и другие параметры. Если шапка таблицы сложная многоуровневая, то вышеуказанные операции могут повторяться в разных частях шапки многократно до достижения желаемого результата.

Для желаемого расположения заголовка, имён показателей шапки и данных информационной части таблицы используются кнопки выравнивания «По левому краю», «По центру», «По правому краю». Кроме того, в Excel для этих целей имеется кнопка «Объединить и поместить в центре».

Рассмотрим примеры записи некоторых функций. Для удобства функции записываются в виде формул. В приводимых примерах перед формулой в кавычках указывается адрес ячейки, в которую вводится формула и выводится результат вычисления, например – «В1».

«В1» =КОРЕНЬ(А1) – извлечение квадратного корня. Если в ячейку А1 ввести 123, то в ячейке В1 выведется результат 11, 09054.

«С1» =ОТБР(В1) – отбрасывание дробной части числа (выделение целой части числа). Если в ячейке В1 находится число 11, 09054, то в ячейке С1 выведется результат 11.

«С2» =ОСТАТ(А2; В2) – вычисление остатка, получаемого в результате деления числа ячейки А2 на число ячейки В2. Если ввести в ячейку А2 число 17, в ячейку В2 число 6, то в ячейке С2 выведется результат 5.

«В3» =РИМСКОЕ(А3) – преобразование числа из арабской формы записи в римскую. Если в ячейку А3 ввести число 1468, то в ячейке В3 выведется результат MCDLXVIII.

«С4» =СТЕПЕНЬ(А4; В4) – возведение числа ячейки А4 в степень ячейки В4. Если ввести в ячейку А4 число 5, в ячейку В4 число 3, то в ячейке С4 выведется результат 125.

«В13» =СУММЕСЛИ(А1: А10; 1; В1: В10) – вычисление суммы чисел блока ячеек В1: В10 по критерию 1, введённому в блок ячеек А1: А10 (см. рис. 3). В ячейке В13 накопится и выведется сумма чисел ячеек В1+В4+В7+В10 = 12+19+17+13 = 61.

Критерий – это число или оператор, определяющие адреса ячеек блока для суммирования. Если число записывается без кавычек, то оператор записывается в кавычках и отражает одну из операций отношения: “=”, “> ”, “< ”, “> =”, “< =”, “< > ”.

«В14» = СУММЕСЛИ(А1: А10; ”> 1”; В1: В10) – вычисление суммы чисел блока ячеек В1: В10 по критерию, значение которого больше 1, введённому в блок ячеек А1: А10 (см. рис. 3).В ячейке В14 накопится и выведется сумма чисел ячеек В2+В3+В5+В6+В8+В9 = 15+11+14+18+16+15 = 89.

«В15» =МАКС(В1: В10) – определение максимального числа в блоке ячеек В1: В10 (см. рис. 3). В ячейке В15 выведется число 19.

«В16» =МИН(В1: В10) – определение минимального числа в блоке ячеек В1: В10 (см. рис. 3). В ячейке В16 выведется число 11.

«В17» =ЕСЛИ(А1=1; В1+В4+В7+В10; В2+В5+В8) – вычисление одной из двух сумм в зависимости от условия. Если условие А1=1 выполняется, то в ячейке В17 (см. рис. 3) накопится и выведется сумма чисел ячеек В1+В4+В7+В10 = 12+19+17+13 = 61. Если условие А1=1 не выполняется, то в ячейке В17 накопится и выведется сумма чисел ячеек В2+В5+В8 = 15+14+16 = 45.

Функция «ЕСЛИ» позволяет создавать разветвления. В неё можно вкладывать функции «И» и «ИЛИ».

«В18» = ЕСЛИ(И(А2> А1; А3> А2); В1*В2; В3*В4) – здесь операция В1*В2 выполнится только в том случае если оба условия выполнимы, иначе выполнится операция В3*В4.

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

Сортирование можно провести по алгоритму: меню «Д анные» à команда «С о ртировка …» à в диалоговом окне «Сортировка диапазона» задать необходимые параметры.

Построение диаграмм, на основе использования данных таблиц, осуществляется при помощи кнопки «Мастер диаграмм» или по алгоритму: меню «В ид» à команда «Панели и нструментов 4» à пункт «Диаграммы» à на появившейся панели инструментов «Диаграммы» задать необходимые параметры.

Алгоритм печати таблицы: меню «Ф айл» à команда «П ечать…» à в окне «Печать» задать необходимые параметры à кнопка ОК.

Если таблица превышает размеры листа бумаги, то перед печатью её необходимо разбить на страницы по алгоритму: меню «С ервис» à команда «П а раметры…» à в окне «Параметры» отметить галочкой пункт «а вто разбиение на страницы» à границы страниц отметятся пунктирными линиями.

Чтобы увидеть, как будет выглядеть таблица перед выводом на печать, нажимается кнопка «Предварительный просмотр». Если таблица разбита на множество страниц, то для их просмотра используются кнопки «Далее» и «Назад». Для выхода из режима просмотра нажимается кнопка «Закрыть».

Задание 15. Создать таблицу и построить диаграмму. Коэффициент сезонности определяется как отношение максимального среднемесячного к среднегодовому числу работников. Вычисляется с точностью до двух десятичных знаков. Данные таблицы рассортировать по возрастанию по показателю «В среднем за период» и построить вторую диаграмму.

Динамика показателей сезонности использования рабочей силы

Задание 16. Сформировать документ «Ведомость расчёта и выдачи стипендии». Входные данные: Фамилии И.О. студентов, оценки за четыре экзамена. Выходные данные: средний балл, сумма начисленной стипендии. Сумма начисленной стипендии зависит от среднего балла (СБ). При СБ равном 5, 0 – 500 руб.; при СБ большем или равном 4, 5, но меньшем 5, 0 – 400 руб.; при СБ большем или равном 3, 5, но меньшем 4, 5 – 300 руб.; при СБ меньшем 3, 5 – стипендия не начисляется.

Ведомость должна иметь не менее 10 строк и итоговую строку.

Задание 17. Создать таблицу «Справка о работе звена комбайнёров на уборке зерновых культур». В строку «Max» вывести Фамилию И.О. комбайнёра, намолотившего наибольшее количество зерна. В строку «Min» - Фамилию И.О. комбайнёра, намолотившего наименьшее количество зерна. В этих же строках вывести и количества намолоченного ими зерна.

Справка о работе звена комбайнёров на уборке зерновых культур

Задание выполнить несколькими способами.

1. Используя функцию «ЕСЛИ».
2. Используя функцию «ПРОСМОТР».
3. Используя функции «ИНДЕКС» и «ПОИСКПОЗ».
4. Попытайтесь найти свой вариант выполнения задания.

Задание 18. Дан список сотрудников СХПК «Колос» на 01.01.15. Определить возраст и стаж работы каждого сотрудника, средний возраст и средний стаж работы сотрудников, количество сотрудников имеющих возраст ниже среднего, количество сотрудников имеющих стаж работы ниже среднего.

Задание 19. Сформировать документ:

Ведомость итогов экзаменационной сессии