Тема 8. Табличный процессор Microsoft Excel
Электронная таблица (ЭТ) – компьютерный эквивалент обычной таблицы, в ячейках которой записаны данные различных типов: тексты, даты, формулы, числа. Для управления ЭТ используется табличный процессор. Файл Excel называется рабочей книгой, состоящей из рабочих листов. Рабочая область ЭТ состоит из строк и столбцов, имеющих свои имена. Имена строк – это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы. Имена столбцов – это буквы латинского алфавита – сначала от А до Z, затем от АА до AZ, ВА до BZ и т. д.
Расчет по заданным формулам в ЭТ, в том числе MS Excel, выполняется автоматически. Изменение содержимого какой-либо ячейки приводит к пересчету значений всех ячеек, связанных с ней формульным выражением, и тем самым к изменению всей таблицы.
Применение электронных таблиц: проведение однотипных расчетов над большими наборами данных; автоматизация итоговых вычислений; обработка результатов эксперимента; построение диаграмм и графиков по имеющимся данным.
Основные понятия
Рабочее окно электронных таблиц MS Excel содержит следующие элементы управления: строка заголовка, строка меню, панели инструментов, строка формул, рабочее поле, строка состояния.
Пересечение строки и столбца образует ячейку таблицы, имеющую свой уникальный адрес. Для указания адресов ячеек в формулах используются ссылки (рис. 36).
Блок ячеек – группа последовательных ячеек, может состоять из одной ячейки, строки (или ее части), столбца (или его части), а также последовательности строк или столбцов (или их частей). Адрес блока ячеек задается указанием ссылок первой и последней его ячеек, между которыми ставится разделительный символ – двоеточие <:> (например: A1:H1).
Рис. 36. Окно программы Microsoft Excel.
Активной называется ячейка ЭТ, в которой в данный момент находится курсор.
Перемещение в рабочей книге
При открытии новой книги указатель автоматически устанавливается в ячейку А1. Ячейка, с которой работают в данный момент, называется активной. Пока информации на листе немного, для перемещения по нему достаточно щелкать мышью в соответствующих местах либо нажимать клавиши стрелок. Если информация на листе не помещается, то удобно использовать клавиатуру.
| Таблица 13 | |
| Клавиши | Назначение |
| Home | В начало строки |
| PgUp/PgDn | Вверх/вниз на одно окно |
| Ctrl+Home | В начало таблицы (только для заполненных таблиц) |
| Ctrl+End | В нижний правый угол диапазона, образуемого заполненными ячейками |
| Ctrl+PgDn | На следующий лист |
| Ctrl+PgUp | На предыдущий лист |
| F5 или Ctrl+G | К конкретной ячейке или выделенному диапазону |
| TAB | Направо или сверху вниз в выделенном диапазоне |
| Shift+Tab | Справа налево или снизу вверх в выделенном диапазоне |
Автозаполнение
При заполнении ячеек иногда удобно использовать автоматическое заполнение. Для автозаполнения используется специальный маркер (рис. 37).
Рис.37. Маркер автозаполнения.
Порядковые номера можно ввести такими способами:
1. Ввести число в первую ячейку диапазона и, ухватившись за маркер заполнения при нажатой клавише Ctrl, протащить по диапазону.
2. Ввести два последовательных числа в первые две ячейки диапазона, выделить их и протащить левой кнопкой мыши за маркер заполнения на весь диапазон.
3. Ввести в первую ячейку диапазона число, ухватиться за маркер заполнения и протащить по всему диапазону правой кнопкой мыши. Когда кнопку мыши отпустить, появится контекстное меню, в котором надо выбрать команду Прогрессия.
Можно создавать и собственный список для автозаполнения.
1 способ.
1. В строке меню выбрать команду Сервис ® Параметры.
2. В открывшемся диалоговом окне выбрать вкладку Списки.
3. В поле Списки выбрать опцию Новый список.
4. В поле Элементы списка ввести каждый элемент, нажимая клавишу Enter после каждого элемента.
5. Нажать кнопку Добавитьпосле ввода всех элементов списка, новый список попадет после этого в поле Списки. Нажать кнопку ОК.
2 способ.
1. Если на рабочем листе уже имеется список в диапазоне ячеек, выделить этот диапазон ячеек.
2. В строке меню выбрать команду Сервис ® Параметры.
3. В окне Параметры выбрать вкладку Списки. В поле ввода Импорт списка из ячеек появится адрес выделенного диапазона.
4. Нажать кнопку Импорт, а затем кнопку ОК.
Формат данных
Данные в таблице выводятся на экран в определенном формате. При этом формат данных будет определяться установленными по умолчанию значениями. Формат позволяет отображать числовые данные в том или ином виде (Формат ® Ячейки ®Число). Если данные не соответствуют заданному формату, то они неправильно отображаются. Краткие характеристики каждого типа:
общий подбирает подходящий формат числа либо с фиксированной запятой, либо в экспоненциальной форме;
числовой применяется для общего способа представления числовой информации с заданным количеством десятичных знаков после запятой;
денежный – отображение денежных величин. Отличается от числового тем, что после изображения числа может помещаться знак валюты;
финансовый – выравнивание денежных величин по разделителю целой и дробной части;
дата – отображение дат;
время – отображение времени;
процентный – значение ячеек умножается на 100 и выводится на экран с символом процент;
дробный – отображение дробей;
экспоненциальный – форматные коды, обеспечивающие нормализованное представление чисел;
текстовой – отображение информации в том виде, как она была введена;
дополнительный предназначен для работы с почтовыми индексами, телефонными номерами, адресами.
Адресация в MS Excel
Числовые данные преобразуются с помощью формул. При копировании или перемещении формулы в другое место таблицы необходимо организовать управление формированием адресов исходных данных. Поэтому в электронной таблице при написании формул используются понятия «относительная» и «абсолютная» ссылки.
Ссылки делятся на: относительны, абсолютные, смешанные.
Относительная ссылка – ссылка, которая изменяется автоматически при копировании и перемещении формулы в другую ячейку.
Пример. С5
Абсолютная ссылка – это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное.
Для указания абсолютной адресации вводится символ $ (F4).
Пример. $B$5
При абсолютной адресации копируемая формула не изменяется.
Смешанная ссылка указывается, если при копировании или перемещении не меняется номер строки или наименование столбца. При этом символ $ ставится перед номером строки, а во втором – перед наименованием столбца.
Пример. B$1; D$15.
Формулы
Любая формула начинается со знака равенства (=).Он служит тем признаком, по которому табличный процессор отличает формулы от текстовых и числовых данных, и вводится непосредственно в ячейку или в поле данных при нажатии кнопки (=) в строке формул. После знака равенства вводятся:
числа, соединенные математическими операторами сложения (+), вычитания (-), умножения (*), деления (/), возведения в степень (^), процента (%)или логическими операторами сравнения (=, <, <=, >, >=, <>);
текстовые строки, соединенные оператором конкатенации (&);
ссылки в виде адресов и имен ячеек, соединенных этими же операторами;
функции Excel, т.е. именованные формулы, заключающие в скобках аргументы в виде чисел или ссылок, соединенных операторами объединения (: ;) и пересечения (Space).
По завершении ввода формулы в выделенной ячейке отображается результат расчета, если введенные данные корректны, либо фраза #ИМЯ?,если введенные данные не подлежат обработке. Независимо от результата в строке формул всегда присутствует формула, внесенная в активную ячейку, и при желании ее можно переписать или отредактировать.
Функции
Преобразование числовой и текстовой информации в табличном процессоре наиболее эффективно выполняется под управлением Мастера функций.
После выделения ячейки, в которую предстоит поместить функцию, вызов Мастера функций функции может быть выполнен одним из следующих способов:
1. Кнопкой 
на панели инструментов.
2. Кнопкой в строке формул.
3. В строке меню Вставка ® Функция.
Все функции сгруппированы Мастером функций в несколько категорий: финансовые, математические, статистические и т. д.
Правильно сконструированная функция выводит на экран результат расчета, а неправильная – сообщение об ошибке.
Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов, стоящих в скобках после ее имени.
Функция имеет синтаксис написания: начинается с указания имени функции, затем вводится открывающаяся скобка, указываются аргументы, отделяющиеся «;», а затем – закрывающаяся скобка.
Рассмотрим синтаксис наиболее используемых функций.
1.Логические функции – предназначены для проверки выполнения условия или для проверки нескольких условий.
ЕСЛИ используется для условной проверки значений и формул.
Синтаксис:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)
Ивозвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Синтаксис: И (логическое_значение 1, логическое_значение 2,...)
ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис: ИЛИ (логическое_значение 1, логическое значение 2,...)
НЕ заменяет логическое значение аргумента на противоположное. Функция НЕ используется в тех случаях, когда необходимо иметь уверенность в том, что значение не равно некоторой конкретной величине.
Синтаксис: НЕ (логическое_значение)
2. Статистические функции – позволяют выполнять статистический анализ диапазонов данных.
СРЗНАЧ – среднее арифметическое значение своих аргументов.
Синтаксис: СРЗНАЧ (число 1; число 2;...)
СЧЕТ –подсчитывает количество чисел в заданной выборке.
Синтаксис: СЧЕТ (значение 1; значение 2;...)
МАКС – возвращает наибольшее значение из набора значений.
Синтаксис: МАКС (число 1; число 2;...)
МИН – возвращает наименьшее значение из набора значений.
Синтаксис: МИН (число 1; число 2;...)
СЧЕТЕСЛИ – подсчитывает количество значений при заданном условии.
Синтаксис: СЧЕТЕСЛИ (диапазон, условие)
3. Арифметические и тригонометрические функции.
Арифметические и тригонометрические функции позволяют производить простые и сложные математические вычисления.
СУММ – суммирует все числа в интервале ячеек.
Синтаксис: СУММ (число 1; число 2;...)
ABS – возвращает абсолютное значение числа или формулы.
Синтаксис: ABS (число)
LN – возвращает натуральный логарифм (аргумент >=0).
Синтаксис: LN(число)
ФАКТР – вычисляет факториал числа (аргумент >=0).
Синтаксис: ФАКТР (число)
ПРОИЗВЕД – возвращает произведение аргументов.
Синтаксис: ПРОИЗВЕД (число 1; число2...)
КОРЕНЬ – возвращает квадратный корень из числа.
Синтаксис: КОРЕНЬ (число)
Сортировка
Сортировка – это упорядочение записей в таблице. MS Excel позволяет сортировать ячейки таблицы по возрастанию и убыванию.
На панели инструментов Стандартная находятся две кнопки, позволяющие производить сортировку ячеек:
1) 
– сортировка ячеек по возрастанию;
2) 
– сортировка ячеек по убыванию.
В качестве образца (ключа сортировки) программа берет тот столбец, где стоит курсор, и в соответствии с ним меняет местами строки таблицы.
Больше возможностей дает команда в строке меню Данные ® Сортировка. В диалоговом окне Сортировка диапазона можно задать три ключа сортировки.
Прежде чем произвести сортировку, желательно сохранить информацию на диске. В случае неправильно проведенной сортировки можно восстановить первоначальное состояние справочника.
Форматирование таблиц
Для выделенных ячеек можно задавать различную гарнитуру, кегль и начертание с помощью кнопок на панели инструментов Форматирование (рис. 38).
Рис. 38. Панель инструментов Форматирование.
Средство Автоформат позволяет выбрать один из заданных форматов таблиц, которые представляют собой совокупность числовых форматов, образцов выравнивания ячеек, размеров столбцов и строк, шрифтов, рамок и других параметров форматирования для оформления внешнего вид таблицы.
Чтобы использовать средство Автоформат, необходимо:
1. Выделить диапазон, который нужно отформатировать.
2. Выбрать команду Формат ® Автоформат.
3. В появившемся диалоговом окне Автоформат выделить один из элементов и нажать кнопку ОК, чтобы применить выбранный формат.
Создание диаграмм
Диаграмма – графически представленная зависимость одной величины от другой. С помощью диаграмм взаимосвязь между данными становится более наглядной. Диаграмма строится на основании чисел, а ее данные подписываются с привлечением присутствующей в диапазоне текстовой информации.
Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.
Два способа создания диаграмм:
1. Вставка → Диаграмма
2. С помощью панели инструментов 
.
Задания для выполнения
1. Назвать Лист1 – Прайс_лист фирмы «Альфа». Заполнить на листе следующую таблицу.
| Прайс-лист | |||||
| Наименование товара | Цена за штуку, руб. | Количество, штук | Скидка | Стоимость без скидки | Стоимость со скидкой |
| Телевизор | 3% | ||||
| Магнитофон | 0% | ||||
| Компьютер | 0% | ||||
| Принтер | 2% | ||||
| Сканер | 0% | ||||
| Дискета | 5% | ||||
| Телефон Sony | 15% | ||||
| Монитор LG | 5% | ||||
| Итого: |
Выделить заголовки столбцов жирным шрифтом, наименование товара – шрифтом синего цвета.
Найти стоимость без скидки и стоимость со скидкой.
Выделить с помощью условного форматирования (Формат→ Условное форматирование) в таблице те товары, цена за штуку которых превышает 8000 рублей, установив стиль ячейки – Результат 2.
Создать прейскурант для пересчета стоимости товаров в рублях на доллары в соответствии с текущим курсом доллара.
| Прейскурант | ||
| Курс пересчета | 1 у.е. | |
| Наименование товара | Стоимость без скидки (руб.) | стоимость(у.е.) |
| Телевизор | ||
| Магнитофон | ||
| … |
2. Автозамена. Вызвать команду Сервис →Автозамена. Убедитесь, что отмечена галочкой опция «заменять при вводе». В поле Заменить: фА, а в поле На: фирма «Альфа». Чтобы получить результат, нужно выделить любую пустую ячейки, ввести фА, и нажать клавишу Enter.
3. Рабочий лист должен содержать сводную ведомость студенческих оценок по итогам сессии. Если экзамены сданы без троек, то соответствующая ячейка таблицы должна быть зелеными и, если у студента остались задолженности – красными.
Переименовать новый рабочий лист как «Ведомость» и создать на нем нижеприведенную таблицу.
| № | Ф.И.О. | Информатика | Экономика | Математика | Средний балл | Стипендия | Доплата |
| Петров Е.П. | |||||||
| … | … | ||||||
| Сидорова А.Н. |
Заполнить таблицу. Экзаменационные оценки должны изменяться от 2 до 5 баллов.
В ячейку G2 ввести значение фиксированной доплаты – 500 руб.
Найти: средний балл каждого студента; стипендию, если студент имеет средний балл больше или равный 4, то стипендия равна 2000 руб. плюс фиксированная доплата, иначе 500 руб.; количество 5, 4, 3, 2 по каждому предмету (функция СЧЕТЕСЛИ).
Вычислить средний балл группы.
Задать условное форматирование для студентов. Если оценка больше 3, то стиль ячейки – Результат 2; а если оценка меньше или равна 3, то стиль ячейки – Результат 1.
По данным ведомости построить гистограмму.
Для предмета «Информатика» построить круговую диаграмму.
4. Студенты сдают зачет, который предусматривает систему оценивания «зачет» и «незачет». Оценка «зачет» ставится, если из 10 вопросов они, верно ответили больше чем на половину вопросов, т.е. на 6, в противном случае ставится оценка «незачет». Надо автоматизировать процесс выставления зачета.
| A | B | … | K | L | M | |
| Фамилия | Вопрос 1 | … | Вопрос 10 | Общая сумма | Зачет/незачет | |
| Иванов К.И. | ||||||
| Петрова Е.Л. | ||||||
| Борисова Л.Ю. | ||||||
| Григорьева Е.К. | ||||||
| Сидоров В.Е. |
5. Дана таблица покупок за июль 2008 г.
| Таблица покупок за июль 2008 г. | |||||||
| Скидка | % | Дается при стоимости покупки свыше 500 руб. | |||||
| № | Товар | Дата | Цена | Количество | Стоимость | Скидка | Итоговая стоимость |
| Ракетка | 01.07.2008 | 500 руб. | руб. | руб. | руб. | ||
| … | |||||||
| Мяч | 31.07.2008 | 354 руб. | |||||
| Сумма | |||||||
| Среднее |
В поле «Скидка» используется функция «ЕСЛИ». Скидка дается, если стоимость покупки выше 500 руб., иначе скидка 0 руб. Изобразить график выручки в июле (поле «Итоговая стоимость», подписи «Дата»).
6. В таблице представлены данные об инфляции в 2008 г. Рассчитать среднегодовое значение инфляции. По вычисленной разнице текущего и среднего значения отмечается «понижение» или «повышение». Построить график изменения курса инфляции в течение года.
| Месяц | Инфляция, % | Разность | Итог |
| Январь | 1,2 | ||
| Февраль | 5,0 | ||
| Март | 3,4 | ||
| Апрель | |||
| Далее заполнить автозаполнением | |||
| Декабрь | 2,8 | ||
| Среднее значение |
7. Дан отчет продажи авиабилетов офиса авиакомпании. Найти значения полей «Минимум», «Максимум», «Среднее».
| 2002 г. | 2003 г. | 2004 г. | Среднее | |
| 1 квартал | 2 600 руб. | 4 400 руб. | 4 120 руб. | |
| 2 квартал | 3 400 руб. | 3 900 руб. | 3 800 руб. | |
| 3 квартал | 4 700 руб. | 5 600 руб. | 3 100 руб. | |
| 4 квартал | 3 500 руб. | 3 400 руб. | 4 800 руб. | |
| Минимум | ||||
| Максимум |
Построить новую матрицу, в которой все значения автоматически заменяются на «1» или «0». Если значения в диапазоне [средний минимум; средний максимум], то оно заменяется на «1» иначе «0».
| 2002 г. | 2003 г. | 2004 г. | |
| 1 квартал | |||
| 2 квартал | |||
| 3 квартал | |||
| 4 квартал |
8. Создать таблицу «Прием в секцию волейбола», заполнив ее не менее чем 10 записями. Вывести сообщение, в котором будет отображаться, принять учащегося в секцию или нет. Учащийся 1 курса экономического факультета ростом не менее 174 см будет принят в секцию; найти, сколько учащихся в итоге поступило в секцию.
| № | Фамилия | Курс | Возраст | Рост | Зачисление в секцию |
9. Составить таблицу (Товар, Цена, Скидка). Предусмотреть следующую обработку: Цена до 5000 руб. – скидка 3%, Цена от 5000 до 10000 руб. – скидка 5%, цена свыше 10000 – скидка 10%. Ввести не менее 10 записей. Определить итоговую стоимость товаров.
10. В ячейках A1, B1, C1 поместить три произвольных числа.
Если сумма этих чисел превышает их произведение, то в ячейку D1 вывести абсолютное значение разности содержимого этих ячеек, в противном случае – корень из их произведения.
Если модуль разности этих чисел превышает 20, то в ячейку D2 вывести их среднее арифметическое, в противном случае – их произведение.
Если модуль их суммы меньше либо равен их среднему арифметическому, то в ячейку D3 вывести факториал суммы этих чисел, в противном случае – разность этих чисел.
Если среднее арифметическое первых двух чисел больше третьего, то в ячейку D4 вывести слово «больше», иначе «меньше».
11.Построить точечную диаграмму функций:
в интервале от -4 до 4 с шагом 0,1;
в интервале от -3 до 3 с шагом 0,5;
в интервале от 0 до 4 с шагом 0,2;
в интервале от 0 до 6,3 с шагом 0,1;
в интервале от -10 до 10 с шагом 0,4;
в интервале от 0 до 
с шагом 0,1.
Контрольные вопросы
1. Структура рабочей книги. Структура окна Excel, окна рабочей книги.
2. Определения понятий: «рабочая книга», «рабочий лист», «диапазон ячеек», «ячейка».
3. Как выделить диапазон ячеек, строку, столбец?
4. Как добавить новую строку или столбец?
5. Как создать пользовательский список для автозаполнения?
6. Форматы данных.
7. Составные элементы формул. Правила записи формул.
8. Категории функций MS Excel.
9. Вызов мастера функции. Вставка функции в формулу с использованием мастера функций.
10. Абсолютная, относительная и смешанная адресация.
11. Создание диаграмм (встроенных и на отдельных листах).
12. Типы диаграмм Excel. Основные компоненты диаграмм. Редактирование отдельных элементов.
13. Как нужно изменить таблицу чтобы она имела вид базы данных?
14. Что можно сделать с помощью команды Автофильтр?
15. Какие параметры нужно установить чтобы данные выбирались при одновременном выполнении двух условий?
16. Что можно сделать с помощью команды Расширенныйфильтр?