Лабораторная работа №3

Лабораторная работа № 3. Решение задач с использованием возможностей MS Excel

MS Excel

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

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

Основное назначение табличного процессора – автоматизация расчетов в табличной форме.

Табличные процессоры представляют собой удобное средство для проведения бухгалтерских и статистических расчетов. В состав процессора входят сотни встроенных математических функций и алгоритмов статистической обработки данных. Кроме того, имеются мощные средства для связи таблиц между собой, создания и редактирования электронных баз данных.

Комплекс решаемых задач

1.Выполнение вычислений в делопроизводстве: расчетные ведомости, сметы расходов; решение численными методами математических задач и т.д.

2.Математическое моделирование. Благодаря свойству: мгновенного пересчета формул при изменении значений входящих операндов табличные процессоры – удобный инструмент для организации численного эксперимента: подбор параметров, прогноз поведения моделируемой системы, анализ зависимостей, планирование, графическое представление данных.

3.Использование ЭТ в качестве реляционной базы данных. Поиск информации по заданным условиям, сортировка информации.

4.Графическое изображение результатов с помощью графиков и диаграмм.

Основные понятия MS Excel

Ячейка – элементарный объект электронной таблицы, расположенный на пересечении столбца и строки.

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

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

Документ Excel называется рабочей книгой.

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

Всего рабочий лист может содержать до 16 038 столбцов, пронумерованных латинскими буквами и их сочетаниями.

Строки последовательно нумеруются цифрами, от 1 до 1 048 576 (максимально допустимый номер строки).

Ячейки и их адресация

Пересечение конкретного столбца и строки образует ячейку, которая является минимальным элементом хранения данных. Местоположение ячейки задается адресом, образованным из имени столбца и номера строки, на пересечении которых находится эта ячейка, а также при необходимости в адресе указывается имя листа и имя книги. Например, А2, В25, С65.

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

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

Диапазон ячеек

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

Выделяется прямоугольный диапазон ячеек протягиванием указателя мыши от одной угловой ячейки до противоположной по диагонали. Рамка текущей ячейки при

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

Кнопка "Office"

В левом верхнем углу окна расположена Кнопка "Office". При нажатии кнопки отображается меню основных команд для работы с файлами, список последних документов, а также команда для настройки параметров приложения (например, Параметры Excel).

Типовые технологические операции с рабочими книгами и листами

Ввод, редактирование и форматирование данных

Типы данных

Отдельная ячейка может содержать данные: константы или формулы, а также оставаться пустой. Константы могут быть текстовыми, числовыми, логическими или ошибочными значениями. Тип данных, размещаемых в ячейках, определяется автоматически при вводе. Если данные можно интерпретировать как число, Exсel так и делает. В противном случае данные рассматриваются как текст. Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числа – по правому. Ввод формулы всегда начинается с символа =.

Ввод текста и чисел

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

Если начать ввод нажатием алфавитно-цифровых клавиш, данные из текущей ячейки заменяются вводимым текстом. Если щелкнуть на строке формул или дважды на текущей ячейке, старое содержимое ячейки не удаляется и появляется возможность его редактирования. Вводимые данные отображаются в любом случае, как в ячейке, так и в строке формул. Для завершения ввода данных в ячейку используют клавишу Enter, для отмены ввода данных в ячейку – Esc. Для очистки текущей ячейки или выделенного диапазона проще всего использовать клавишу Delete.

ЗАДАНИЕ 1

Создайте новую книгу и заполните ее как показано ниже:

Сохраните книгу в свою папку.

Форматирование содержимого ячеек

Числовые форматы. Для экономических расчетов применяют три разных формата записи чисел: обычный числовой, финансовый и формат даты.

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

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

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

Текстовые форматы. Используют для записи текстовых строк и цифровых нечисловых данных (например, почтовых индексов, номеров телефонов и т.п.).

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

Главная – Число (Шрифт, Выравнивание) – Формат ячеек. Вкладки открывающегося диалогового окна позволяют выбирать формат записи данных (количество знаков после запятой, указание денежной единицы, способ записи даты и прочее), задавать направление текста и метод его выравнивания, определять шрифт и начертание символов, управлять отображением и видом рамок, задавать фоновый цвет.

Автоматизация ввода

К средствам автоматизации ввода относятся автозавершение и автозаполнение. Автозавершение. В ходе ввода текста в очередную ячейку программа проверяет

соответствие введенных символов строкам, имеющимся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши Enter подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.

Автозаполнение константами. В правом нижнем углу рамки ячейки имеется черный квадратик – это маркер автозаполнения. При наведении на него курсор мыши вместо толстого белого креста принимает вид тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном и вертикальном направлении.

Если ячейка содержит число, то при перетаскивании маркера происходит либо копирование ячеек, либо их заполнение данными по закону арифметической прогрессии. Для выбора способа автозаполнения применяется специальное перетаскивание по правой кнопке мыши. Например, ячейка А1 содержит число 1. Протяните маркер заполнения с помощью правой кнопки мыши с ячейки А1 до ячейки А7 и отпустите кнопку. Если в появившемся меню выбрать пункт Копировать ячейки, то во всех ячейках появится 1, если пункт Заполнить, то в ячейках окажутся числа 1, 2, 3.

Использование прогрессии. Если ячейка содержит число, дату или период времени, который может являться частью ряда, то при копировании происходит приращение ее значения (получается арифметическая или геометрическая прогрессия, список дат). Чтобы задать прогрессию, нужно в ячейку ввести начальное значение, затем выбрать кнопку Заполнить панели Редактирование вкладки Главная и в появившемся диалоговом окне Прогрессия задать параметры для арифметической или геометрической прогрессии.

ЗАДАНИЕ 2

Продолжите заполнение созданной книги, используя маркер автозаполнения для ячеек C3:D3; C11:D11; C12:D12. Измените формат представления данных в ячейках

B5:D6; B10:D12 на денежный.

Сохраните книгу.

Копирование содержимого ячеек

Копирование и перемещение ячеек можно осуществлять методом перетаскивания или через буфер обмена.

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

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

ЗАДАНИЕ 3

Скопируйте диапазон ячеек A2:D15 методом перетаскивания, исправьте данные для второго квартала.

Сохраните книгу.

Вычисления в электронных таблицах. Формулы

Формулы

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

Ссылки на ячейки

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

•числовые значения;

•адреса ячеек (относительные, абсолютные и смешанные ссылки);

•операторы: математические (+, -, *, /, %, ^), сравнения (=, <, >, >=, <=, < >),

•текстовый оператор & (для объединения нескольких текстовых строк в одну),

•операторы отношения диапазонов (двоеточие (:) – диапазон, запятая (,) –для

•объединения диапазонов, пробел – пересечение диапазонов);

•функции.

Ввод формулы всегда начинается со знака «=». Результат формулы отображается в ячейке, а сама формула – в строке формул. Адреса ячеек в формуле могут вводиться вручную, а могут просто с помощью щелчка мыши по нужным ячейкам. После вычисления в ячейке отображается полученный результат, а в строке формул в окне ввода – созданная формула.

Вместо чисел в формуле использованы имена ячеек – ссылки на ячейки. Ссылки выполняют роль адресов ячеек, содержимое которых используется в вычислениях. Результат расчета зависит от того, какие числа находятся в ячейках. Т.е. ячейка, содержащая формулу, является зависимой, и подлежит пересчету всякий раз, когда изменяются значения в ячейках, на которые указывают ссылки, входящие в формулу.

Ссылку можно задать вручную или щелкнуть на нужной ячейке (выбрать нужный диапазон) указателем мыши.

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

Ссылки абсолютные и относительные

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