МИНОБРНАУКИ РОССИИ
–––––––——————————–––––––
Санкт-Петербургский государственный электротехнический университет «ЛЭТИ»
——————————————————
Обработка данных в среде Excel
Методические указания к лабораторным работам
Санкт-Петербург Издательство СПбГЭТУ «ЛЭТИ»
2011
УДК 330.47
Обработка данных в среде Excel: Методические указания к лабораторным работам / Сост.: И. Б. Никифоров, А. А. Безруков. СПб.: Изд-во СПбГЭТУ «ЛЭТИ», 2011. 28 с.
Содержат описания лабораторных работ по изучению табличного процессора EXCEL 2007 для решения экономических задач. Приведены основные методики для освоения возможностей пакета прикладных программ.
Предназначены для студентов всех направлений, профилей и специальностей факультета экономики и менеджмента.
Утверждено редакционно-издательским советом университета
в качестве методических указаний
© СПбГЭТУ «ЛЭТИ», 2011
В сборник вошли методические указания к выполнению десяти лабораторных работ в среде табличного процессора EXCEL 2007.
Выполнять данные лабораторные работы желательно в той последовательности, в которой они приведены в сборнике, так как именно в этом порядке сохраняется принцип преемственности в изложении материала. При подготовке к выполнению лабораторных работ и при непосредственной работе на персональном компьютере настоятельно рекомендуется использовать электронный информационный ресурс «Сборник методических указаний и основных приемов работы в среде табличного процессора Excel 2007 при решении экономических задач»: www.eltech.ru/faculty/fem/InfII.
Лабораторная работа 1
СОЗДАНИЕ И ОФОРМЛЕНИЕ ПРОСТЫХ ТАБЛИЦ НА ЛИСТАХ РАБОЧЕЙ КНИГИ ТАБЛИЧНОГО ПРОЦЕССОРА EXCEL
Цель работы: получение практических навыков создания таблиц:
ввод данных (констант, формул и функций) в таблицу, в том числе с использованием авто заполнения;
редактирование рабочего листа (копирование, перемещение, удаление и редактирование данных);
числовое и стилистическое форматирование рабочего листа, в том числе выравнивание, использование границ, цвета и узоров, изменение ширины столбцов;
работа с функциями рабочего листа;
формирование ссылок на ячейки других листов.
Основные сведения о построении формул
Формула в EXCEL – это такая комбинация констант (значений), ссылок на ячейки, имен, функций и операторов, по которой из заданных значений выводится новое. Начинаются формулы со знака равенства « = ». Выводимое формулой значение изменяется в зависимости от тех значений, которые задаются в рабочем листе. Чтобы вывести на экран рабочего листа формулу, надо в диалоговом окне, открывшемся после выбора «Формулы/Зависимости формул», установить флажок «Показать формулы».
В формулах используются следующие арифметические операторы:
3
«^» – возведение в степень, «*» – умножение, «/» – деление, «+» – сложение, «–» – вычитание;
Ссылки применяются для обозначения ячеек или групп ячеек рабочего листа. Для построения ссылок используются заголовки столбцов и строк рабочего листа. Существует три типа ссылок: относительные, абсолютные и смешанные.
Относительная ссылка (A1) – указывает, как найти другую ячейку, начиная поиск с ячейки, в которой расположена формула.
Абсолютная ссылка ($A$1) – указывает, как найти ячейку на основании её точного местоположения на рабочем листе.
Смешанная ссылка (A$1, $A1) – указывает, как найти другую ячейку на основе сочетания абсолютной ссылки на строку и относительной на столбец и наоборот.
Для изменения ссылки на ячейку в формуле или функции выделите эту ячейку и нажмите функциональную клавишу «F4».
Ссылки на ячейки других листов имеют формат, где «Лист №» – имя рабочего листа; знак «!» отделяет имя листа от ссылки на ячейку; «ячейка» – относительная или абсолютная ссылка.
Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки: «‘ ‘».
Функция – это специальная, заранее созданная формула, которая выполняет операции над заданным значением (значениями) и возвращает одно или несколько значений. Функция Excel 2007 представляет собой формулу, которая имеет один или несколько аргументов.
Аргумент – это данные, которые используются функцией для получения результата. У разных функций разное количество аргументов.
Для выполнения стандартных вычислений можно использовать функции рабочего листа. Рассмотрим некоторые из них:
СУММЕСЛИ. Функция суммирует ячейки, отвечающие заданному критерию:
= СУММЕСЛИ (Диапазон; Условие; Диапазон_суммирования), где «Диапазон» определяет интервал вычисляемых ячеек; «Условие» задает
критерий в форме числа, выражения, который определяет, какая ячейка будет суммироваться; «Диапазон_суммирования» задает фактические ячейки для суммирования. Суммируются те ячейки диапазона, которые удовлетворяют
4
условию. Если диапазон суммирования отсутствует, то суммируются ячейки аргумента «Диапазон».
СЧЕТЕСЛИ. Функция подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию.
= СЧЕТЕСЛИ (Диапазон; Критерий), где «Диапазон» определяет интервал, в котором подсчитывается количество
ячеек; «Критерий» задает критерий в форме числа, выражения, который определяет, какие ячейки следует подсчитывать.
ВПР. Функция ищет в таблице значение, затем перемещается в таблице
ксоответствующей ячейке и возвращает ее значение:
=ВПР (Искомое_значение; Табл_массив; Номер_столбца; Интервальный_просмотр),
где «Искомое_значение» – значение, которое должно быть найдено в первом столбце таблицы и может быть значением, ссылкой или текстовой строкой; «Табл_массив» – таблица с информацией, в которой ищутся данные; «Номер_столбца» – номер столбца в таблице, в котором должно быть найдено соответствующее значение; «Интервальный_просмотр» – логическое значение, которое определяет, нужно ли искать точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен и точное соответствие не найдено, то возвращается приблизительно соответствующее значение, а именно наибольшее значение, которое меньше, чем искомое значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
= ЕСЛИ. Функция возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение если ЛОЖЬ:
= ЕСЛИ (Логическое_выражение; Значение_если_истина; Значение_если_ложь),
здесь «Логическое выражение» – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. «Значение_если_истина» – это значение, которое возвращается, если логическое_выражение имеет значение ИСТИНА. Если логическое_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. «Значение_если_истина» может быть другой формулой. «Значение_если_ложь» – это значение, которое возвращается, если логическое_выражение имеет значение ЛОЖЬ. Если логическое_выражение имеет
5