Тема 5 Технологии и системы обработки табличной информации

Общая характеристика и функциональные возможности табличного процессора Microsoft Excel

2 Основные приёмы работы по созданию электронных таблиц и организации вычислений в таблицах средствами табличного процессора Microsoft Excel

Основные понятия по теме

Электронная таблица - это некоторая программа, которая используется для обработки данных, представленных в виде таблицы.

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

Рабочая область приложения Excel представляет собой книгу, состоящую из нескольких листов,

Лист представляет собой таблицу, состоящую из строк и столбцов. Элемент, стоящий на пересечении строки и столбца, называется ячейкой или клеткой.

Столбцы обозначаются латинскими буквами от А до Z и далее АА, АВ и т.д., строки - цифрами от 1 с шагом 1, адрес ячейки состоит из обозначения столбца и строки, на пересечении которых она состоит, например, на пересечении столбца А и строки 3 находится ячейка A3.

Элементы пользовательского интерфейса, которые появляются при запуске приложения Office, - меню, панели инструментов, строки состояния и окна - образуют так называемую рабочую область программы.

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

Под заголовком находится главное меню с командами, которые выполняют всю основную работу в программе.

Еще ниже располагается один или несколько наборов кнопок. Этим наборы называются панелями инструментов и предназначены для выполнения самых распространенных команд и действий Excel. Кнопки на панелях инструментов обычно предоставляют ускоренный доступ к командам меню.

Количество панелей можно регулировать в главном меню:

Вид→Панели инструментов.

Под панелями инструментов находится строка формул, в которой можно редактировать содержимое ячеек.

Ниже- рабочая область документа, который для Excel представляет собой лист.

Внизу находится строка состояния для отображения различных индикаторов.

Наконец, на экране есть горизонтальные и вертикальные полосы прокрутки для перемещения в окне документа.

В клетку электронной таблицы можно заносить информацию трех типов:

- числовую;

- символьную;

- формулу, но в одной ячейке могут быть данные только одного типа.

Числовая информация бывает формата даты, времени и собственно числовая.

Числа вводятся сразу с первой позиции, могут начинаться знаком «+» или «-» и содержать символы «%», «$», «/». Запятая интерпретируется как разделитель целой и дробной части. Числа в экспоненциальной форме содержат основание натурального логарифма «Е» или «е».

При вводе даты используется точка или дефис в качестве разделителя.

Для отображения времени суток в 12-ти часовом формате надо ввести букву а или р через пробел, например 9:30 р, в противном случае время будет интерпретировано в 24 часовом формате. Для ввода даты и времени в одну ячейку в качестве разделителя используется пробел.

Текстом в Microsoft Excel является любая последовательность, состоящая из цифр, пробелов и любых других символов. Если вводится число, дата или время, которые необходимо сохранить в текстовом формате, перед ним следует ввести апостроф «'».

Формулой называется выражение, которое вычисляет новое значение по уже существующим. В состав формул могут входить числа, математические операторы, ссылки на ячейки и встроенные выражения, называемые функциями. Все формулы Excel начинаются со знака равенства «=». Этот знак является признаком начала математической операции, он указывает Excel на необходимость сохранения следующего за ним выражения в виде формулы.

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

Сервис→Параметры→Вид→Параметры окна→Формулы.

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

Excel позволяет легко скопировать, или дублировать, формулу в соседние ячейки

Для выполнения более сложных операций по обработке числовой и текстовой информации Excel позволяет включать в текст формул стандартные операции, называемые функциями.

Функция -заранее определенное выражение, которое имеет одно или несколько аргументов и возвращает единственное значение.

В состав Excel входит свыше 250 функций из нескольких полезных категорий:

- Математические включают арифметические и тригонометрические функции. Позволяют производить простые и сложные вычисления, например вычисление суммы диапазона ячеек, вычисление суммы ячеек диапазона, удовлетворяющих указанному условию, округление чисел и прочее.

- С помощью финансовых функций осуществляются такие типичные финансовые расчеты, как вычисление суммы платежа по ссуде, объем периодической выплаты по вложению или ссуде, стоимость вложения или ссуды по завершении всех отложенных платежей.

- Функции работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Например, если требуется использовать в формуле текущую дату, воспользуйтесь функцией СЕГОДНЯ, возвращающей текущую дату по системных часов.

- Статистические функции позволяют выполнять статистический анализ диапазонов данных. Например, с помощью статистической функции можно провести прямую по группе значений, вычислить угол наклона и точку пересечения с осью Y и прочее.

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

- С помощью текстовых функций можно производить действия над строкам текста - например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну.

- Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий.

- Если необходимо осуществлять поиск в списках или таблицах или если необходимо найти ссылку к ячейке, воспользуйтесь функциями ссылки и массивы. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке - функцию ПОИСКПОЗ.

- Функции проверки свойств и значений связывают Excel c самим собой. Так, ЯЧЕЙКА сообщает информацию о заданной ячейке, а ЕОШИБКА сообщает, есть ли в ячейке ошибка.

При копировании формул содержащиеся в них ссылки автоматически заменяются новыми. Но это происходит только в том случае, если они являются относительными. По умолчанию в Excel применяются относительные ссылки. Они используются в формулах для указания адреса ячейки относительно позиции ячейки, содержащей формулу. При изменении позиции формулы относительные ссылки будут содержать адреса других ячеек. Если при перемещении или копировании формулы адреса ячеек не должны изменяться, следует использовать абсолютные ссылки. Абсолютную ссылку можно установить, задав имя или вставив символ $ в адрес ячейки (например, $A$1). Можно создать комбинацию абсолютной и относительной ссылок, например $А1. В этом случае при копировании формулы в ссылке изменяется только строка, а столбец все время остается тем же.

Функция ВПР (рис.2) ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных. Функция ГПР ищет значение в верхней строке таблицы или массива значений и возвращает значение в том же столбце из заданной строки таблицы или массива. Используется, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже.

Синтаксис функции ВПР:

 

ВПР (искомое_значение; табл_массив; номер_индекса_столбца; диапазон_просмотра)

 

«Искомое_значение» - это значение, которое должно быть найдено в первом столбце табл_массива. Искомое_значение может быть числовым, логическим значением, ссылкой или текстовой строкой.

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

«Номер_индекса_столбца» - это номер столбца в «табл_массиве», из которого должно быть возвращено соответствующее значение. Если «номер_индекса_столбца» равен 1, то возвращается значение из первого столбца аргумента «табл_массив»; если «номер_индекса_столбца» равен 2, то возвращается значение из второго столбца аргумента «табл_массива» и так далее. Если «номер_индекса_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «номер_индекса_столбца» больше, чем количество столбцов в аргументе «табл_массив», то функция ВПР возвращает значение ошибки #ССЫЛ!.

«Диапазон _просмотра» - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ (0), то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

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

 

Вопросы для самоконтроля

 

1 Для чего предназначен табличный процессор Microsoft Excel?

2 Опишите элементы рабочего окна программы.

3 Информацию каких типов можно заносить в ячейки табличного процессора Microsoft Excel?

4 Что в Excel понимается под формулой?

5 Опишите порядок создания формул.

6 Как просмотреть электронную таблицу в формате формул?

7 Какие категории функций входят в состав табличного процессора Microsoft Excel? Охарактеризуйте их.

8 Опишите работу с Мастером функций.

9 Что включает понятие Формат, как отформатировать данные в ячейках электронной таблицы?

10 Как отсортировать данные в таблице по заданному критерию?

11 Как выполнить условное форматирование данных?

12 В каких случаях необходимо использование команды «Специальная вставка»?

13 Какие типы диаграмм можно построить средствами табличного процессора Microsoft Excel?

14 Опишите порядок работы с Мастером диаграмм.

15 Назовите отличия в процессе распечатки документов Word и Excel.

Лабораторная работа 1 Создание, редактирование и форматирование электронной таблицы, работа с формулами и функциями

Цель: приобретение практических навыков работы с табличными документами в среде Microsoft Excel

Материалы и оборудование: ПК

 

Задание

 

1) Загрузите ТП Microsoft Excel и ознакомьтесь с элементами окна программы. Изучите главное меню и панели инструментов.

2) До создания таблиц установите параметры страницы.

3) Сформируйте таблицу 1 и заполните ее 10 записями:

 

Таблица 1 - Расчётная ведомость по заработной плате

Таб. №	ФИО	Начислено	Удержано	К выдаче
Оклад	Надбавка	Премия	ИТОГО начислено	Подоходный налог	Пенсионный фонд	ИТОГО удержано
Итого:

4) Сохраните таблицу под именем Расчётная ведомость в своей папке на сервере Math_server

5) Сформируйте и заполните таблицу 2:

 

Таблица 2 - Производство и реализация продукции растениеводства

Показатели	Урожайность, ц\га	Площадь, га	Валовой сбор, ц	Реализовано, ц	Уровень товарности, %
2005г.	2006г.	2005г.	2006г.	2005г	2006г.	2005г.	2006г.	2005г.	2006г
А
Зерновые, всего: в т.ч.
Озимые зерновые	28,3	22,6
Яровые зерновые	30,1	34,2
Зернобобовые	24,9	38,2
Картофель	128,2	158,9
Овощи открытого грунта		199,8
Рапс	11,5	22,2
Кормовые корнеплоды		418,4
Кукуруза на силос		235,6					-	-

4) Сохраните таблицу под именем Продукция растениеводства в своей папке на сервере Math_server;

5) Сформируйте и заполните таблицу 3:

Таблица 3 - Расчёт прибыли и уровня рентабельности по группам
и видам продукции.

№ п/п	Группа и вид продукции	Выручка, млн. руб.	Полная себестоимость млн. руб.	Прибыль, млн. руб	Уровень рентабельности, %
2005 г.	2006 г.	2005 г.	2006 г.	2005 г.	2006 г.	2005 г.	2006 г.
1.	Зерновые в т.ч.	148,21	912,83	103,28	766,19
1.1.	пшеница	87,5	375,5	65,9	324,12
1.2.	ячмень	37,9	124,6	22,6	101,5
1.3.	рожь	13,4	274,63	8,88	231,5
1.4.	овес	9,41	138,1	5,9	109,07
2.	Картофель	34,33	64,34	22,39	41,54
3.	Овощи	26,04	87,76	25,311	39,44
4.	Молоко	189,34	592,58	193,52	687,3
5.	Мед
6.	Прирост живой массы КРС	132,15	192,94	135,79	113,61
Всего
Среднее
Максимум
Минимум

6) Сохраните таблицу под именем Показатели по продукции растениеводства в своей папке на сервере Math_server;

7) Оформите и защитите отчёт по лабораторной работе.

 

Ход работы

Порядок работы пользователя по Установке параметров страницы, Сохранению таблицы на магнитном диске, Распечатке на принтере, Завершению работы с программой аналогичен порядку работы в текстовом процессоре Microsoft WORD.

Выделение диапазона ячеек таблицы:

1 способ:

Удерживая нажатой клавишу Shift и нажимая клавиши управления курсором

[ ­ ], [ ¯ ], [ ® ], [ ]

2 способ:

Нажать левую клавишу мыши и, не отпуская, перемещать курсор мыши по ячейкам.

Для того чтобы снять пометку, следует щелкнуть левой клавишей мыши вне пометки.

Значения таблицы 1 получите с использованием следующих рекомендаций:

Надбавка к окладу составляет 20% от оклада.

Премия – 17% от оклада

Подоходный налог = 9% от (Итого начислено – базовая величина)

Отчисления в пенсионный фонд = 1% от Итого начислено

В таблице 2 выполните следующие расчеты:

- Валовой сбор = Урожайность * Площадь;

- Уровень товарности = Реализовано / Валовой сбор * 100

В таблице 3 выполните следующие расчеты:

- Прибыль = Выручка – Себестоимость;

- Уровень рентабельности = Прибыль / Себестоимость * 100%

Создание формул

- Поместить курсор в ячейку, в которой будет находиться формула

- Ввести знак «=»

- Щелкнуть на ячейке, содержимое которой используется в формуле. Вокруг нее появится «муравьиная дорожка» и адрес ячейки поместится в формулу

- Ввести нужный арифметический знак

- Щелкнуть на другой ячейке, которая используется в формуле

- Нажать клавишу Enter

Форматирование ячеек

- выделить диапазон ячеек

- выбрать команду меню Формат ®Ячейки…

Также можно воспользоваться панелью инструментов ФОРМАТИРОВАНИЕ

Работа с мастером функций:

- Поместить курсор в ячейку, в которую нужно вставить функцию

- Выбрать команду Вставка-Функция или щелкнуть на кнопке на панели инструментов. На экране появится окно Мастера функций.

- Выбрать из списка категорию и имя функции.

- Ввести необходимые аргументы.

Наиболее часто используемые функции (минимум, максимум, среднее) можно выбрать на панели инструментов СТАНДАРТНАЯ.

Рисунок 3 – Меню кнопки Автосумма

 

Лабораторная работа 2 Создание, редактирование и форматирование электронных таблиц на разных рабочих листах книги Excel, работа со ссылками и функциями в формулах

Цель: приобретение практических навыков работы со ссылками и функциями в среде Microsoft Excel

Материалы и оборудование: ПК

 

Задание

1) Лист 1 рабочей книги переименуйте в «Исходные данные» и сформируйте таблицу предложенной формы (Таблица 1).

 

Таблица 1 - Исходные данные для расчета экономических показателей
Наименование хозяйства	Основные производственные фонды, млн. руб.	Площадь сельхозугодий, га	Валовая продукция, тыс. руб.	Полная себестоимость зерна, млн. руб.	Выручка за зерно, млн. руб.
А
Береговое
Бобовичи
Братское
Городня
Дружба
Звёздное
Красная гвоздика
Красная гора
Маяк
Озёрное

 

 

А
Октябрь
Отрадное
Рассвет
Речное
Светлый путь
Свиричи
Солнечное
Тепличное
Терешковичи
Урицкое

 

2) Второй рабочий лист книги EXCEL переименуйте в «Расчеты» и создайте Таблицу 2, которая должна формироваться на основании: а) ссылок на ячейки исходной таблицы 1, б) функции ВПР, в) формул расчета. В таблице 2 графы, которые должны иметь ссылки, выделены курсивом; графы, которые должны содержать функцию ВПР, выделены подчеркиванием. В таблице выполните все необходимые расчеты.

 

Таблица 2 - Экономические показатели эффективности работы предприятий
Наименование хозяйства	Уровень фондооснащенности, млн.руб.\га	Уровень производства валовой продукции, тыс. руб\га	Уровень рентабельности по зерну, %
А	гр.1 / гр.2 * 100	гр.3 / гр.2	(гр.5 - гр.4) / гр.4 * 100
В среднем
Минимум
Максимум

 

3) В таблице 2 отсортируйте хозяйства по возрастанию по уровню производства валовой продукции.

К данным таблицы примените условное форматирование: уровень рентабельности меньше 0 % выделите красным цветом; от 0-100% синим, свыше 100% - зеленым.

 

4) Переименуйте лист 3 в «Средние». Скопируйте из Таблицы 2 указанные ячейки и создайте таблицу 3:

 

 

Таблица 3 - Статистические показатели по совокупности хозяйств
Показатели	в среднем	максимум	минимум
Уровень фондооснащенности, млн.руб./га
Уровень производства валовой продукции, тыс. руб/га
Уровень рентабельности по зерну, %

 

5) Сохраните книгу Excel под именем «Экономические показатели» в своей папке на сервере Math_server.

6) Оформите и защитите отчёт по лабораторной работе.

Ход работы

Сортировка данных в таблице:

- выделить диапазон ячеек, подлежащих сортировке (только числовые данные);

- выбрать команду Данные → Сортировка, где указать в поле «Сортировать по» столбец, по которому будет проводиться сортировка и способ сортировки (по возрастанию или убыванию);

Рисунок 4 – Окно команды Сортировка диапазона

 

Условное форматирование:

- выделить столбец, для которого будет использовано форматирование;

- в меню выбрать команду Формат → Условное форматирование, и указать условия форматирования (см. пример). Имеется возможность указывать до трех условий, каждое новое условие появляется при нажатии кнопки А также>>. Параметры форматирования (Шрифт, Границы, Вид) указываются в диалоговом окне, которое появляется при нажатии кнопки Формат.

Рисунок 5 – Окно команды Условное форматирование