Лабораторная работа № 12. Основы работы в MS Excel
Для решения задач, которые можно представить в виде таблиц, разработаны специальные пакеты программ, называемые электронными таблицами или табличными процессорами. Электронные таблицы ориентированы, прежде всего, на решение экономических задач.
Программа MS Excel входит в пакет Microsoft Office и предназначена для подготовки и обработки электронных таблиц под управлением Windows. Документом MS Excel является файл с произвольным именем и расширением.XLS.
В терминах MS Excel файл называется рабочей книгой. В каждом файле может содержаться от 1 до 255 электронных таблиц. Электронная таблица называется рабочим листом.
Электронная таблица MS Excel состоит из 16384 строк и 256 столбцов. Строки нумеруются целыми числами от 1 до 16384, а столбцы – латинскими буквами A, B, C,…, AA, BB,… На пересечении столбца и строки располагается структурный элемент таблицы – ячейка.
В любую ячейку таблицы можно ввести исходные данные: число или текст, а также формулы для расчета производной информации. Для указания на конкретную ячейку используется адрес, который состоит из обозначения столбца и номера строки, на пересечении которых находится ячейка. Например А1, С4.
При вызове MS Excel на экране появится окно, показанное на рис. 1.
Рис. 1. Внешний вид окна Excel
Основными элементами окна являются: строка меню; панель инструментов; строка формул (в ней вводятся формулы и значения); строка состояния (здесь отображается информация о состоянии системы, а также контекстные подсказки); поле имени; лист рабочей книги (электронная таблица)- основное рабочее место; ярлычок листа; блок прокрутки листов; угловой элемент, предназначенный для выделения всех ячеек листа; полосы и стрелки прокрутки.
Ячейка- это контейнер, содержащий информацию, он никогда не бывает пустым. Незаполненная ячейка на экране выглядит пустой, на самом деле в ней, по умолчанию, хранится число 0. В качестве информации может выступать значение какого-либо типа (числовое, текстовое, логическое, дата), а также формула. Возможность ввода в ячейку вычисляемых формул, оперирующих другими ячейками, является главным удобством электронных таблиц вообще и MS Excel в частности, и придает им особую специфику.
Обычная математическая формула состоит из операндов - чисел, переменных, функций, над которыми выполняются операции - умножение, сложение и т.д. В MS Excel роль операндов выполняют ячейки, а точнее те значения, которые в них хранятся. Требуемое значение можно получить, указав адрес ячейки. При построении формулы можно ссылаться на ячейку, также содержащую формулу, при этом операции будут производиться с соответствующим значением формулы, на которую сослались.
Ввод и форматирование данных
Большинство работ, выполняемых с таблицей, включая ввод и форматирование данных, требуют, как правило, предварительного выделения ячеек. Ячейка, в которой находится курсор, является выделенной или активной. Чтобы выделить блок ячеек необходимо щелкнуть мышью в левом верхнем углу блока и, удерживая левую кнопку, потянуть курсор мыши в правый нижний угол блока, при этом выделенный блок будет выглядеть на экране черным. Иногда необходимо выделить сразу несколько блоков, для этого следует повторить вышеуказанные действия для одного (первого) блока, а затем, нажав и удерживая клавишу Ctrl, повторить то же самое для остальных.
Для того чтобы выделить сразу всю строку или весь столбец достаточно щелкнуть мышью по номеру строки или заголовку столбца. При необходимости можно выделить всю таблицу, щелкнув мышью на прямоугольнике (угловом элементе- см. рис. 1) в левом верхнем углу таблицы.
Отменить любое выделение можно щелкнув мышью в любом месте таблицы.
Ввод данных в ячейки таблицы осуществляется через строку формул (см. рис. 1). Данные, помещаемые в строку формул, располагаются в активной ячейке, т.е. в той, где находится курсор. Чтобы активизировать строку формул достаточно щелкнуть по ней мышью. Конец ввода отмечается нажатием клавиши Enter. Для редактирования введенных данных требуется навести курсор на соответствующую ячейку, при этом в строке формул появятся вся находящаяся в ней (ячейке) информация, затем щелкнуть мышью по строке формул.
Любая формула должна начинаться со знака =, иначе MS Excel воспринимает вводимое как текст. Практически всегда формула содержит ссылки на другие ячейки. Ссылки записываются буквами (только!) латинского алфавита и цифрами, при этом не имеет значение прописные буквы или строчные. Необходимо внимательно вводить ссылки (адреса) на ячейки и не допускать циклических ссылок, т.е. ссылок ячейки на саму себя: например, вводимая в ячейку А1 формула не должна содержать ссылку на А1 или ссылки на ячейки, в которых есть адрес А1.
Рассмотрим основные математические операторы (в порядке их приоритета): " ^ "- возведение в степень, " * " и " / "- умножение и деление (обладают одинаковым приоритетом), " + " и "–" - сложение и вычитание (обладают одинаковым приоритетом). Пользователь имеет возможность самостоятельно определять приоритеты вычислений, т.е. устанавливать порядок вычислений в формуле, для этого используются скобки. Например, в формуле A1*B1+C1 сначала будет выполнена операция умножения, затем сложения, чтобы поменять порядок вычислений необходимо записать A1*(B1+C1). Следует отметить, что количество открывающихся скобок в формуле должно совпадать с количеством закрывающихся, иначе MS Excel выдаст сообщение об ошибке.
В формуле допускается наличие пробелов - программа их просто не воспринимает; иногда это удобно, т.к. делает формулу более наглядной. К наглядности следует относиться серьезно, при вводе формулы нужно стараться свести количество логических ошибок (когда MS Excel не имеет претензий к синтаксису формулы, но вычисляет неправильно) к нулю и проверять корректность формулы сразу, т.к. разбираться в "многоэтажных" формулах по прошествии времени довольно трудно.
Закончить ввод формулы следует нажатием клавиши Enter.
Обычно после ввода формулы в ячейке отображается результат вычисления по ней, тогда как в строке формул всегда видна только формула. В этом смысле строка формул представляет как бы правую часть обычной формулы, а ячейка - левую.
Рассмотрим форматирование ячеек. Данную операцию можно осуществить с помощью Главная - Формат – Формат ячеек или правой кнопкой мыши – Формат ячеек (см. рис. 2).
Рис. 2. Окно форматирования ячеек
Форматирование ячеек предполагает определение типа данных, содержащихся в ячейке. Основными типами данных в MS Excel являются число, дата, время, текст. Существует возможность пользователю определять свой формат или выносить в эту категорию наиболее часто используемые форматы.
Диалоговое окно позволяет также с помощью ярлычка:
Выравнивание - располагать информацию в ячейках различным образом (в том числе и переносить текст в одной ячейке по словам);
Рис. 3. Вкладка выравнивания текста
Шрифт - задавать параметры шрифта (тип, размер и т.д.);
Рис. 4. Вкладка настройки шрифта
Граница (Рамка) - обрамлять выделенный блок ячеек;
Рис. 5. Вкладка изменения границ ячеек
Вид - заливать ячейки узорами и цветами;
Рис. 6. Вкладка изменения фона ячеек
Защита - предотвращать доступ к ячейкам рабочего листа.
Рис. 7. Вкладка настройки защиты ячеек
Непосредственно рабочее поле можно настраивать в соответствии со специфическими требованиями конкретной решаемой задачи. Данное замечание относится, в частности, к возможности форматирования ширины строк и столбцов, что позволяет сделать таблицу более наглядной. Можно осуществлять указанную операцию через меню Формат – Строка (Столбец), однако удобнее делать это мышью. Необходимо подвести курсор мыши к границе заголовка изменяемого столбца, при этом курсор превратится в перекрестие, горизонтальная палочка которого имеет вид двунаправленной стрелки, затем, нажав и удерживая левую кнопку мыши нужно тянуть границу в сторону, пока ширина столбца не достигнет нужной величины. Чтобы изменить размер сразу нескольких столбцов необходимо выделить их и проделать указанную процедуру для какого-либо столбца, при этом изменения затронут все выделенные столбцы.
Применение копирования существенно ускоряет работу с электронной таблицей, т.к. зачастую приходится вводить однотипные данные и формулы. В MS Excel имеется достаточно много различных процедур копирования.
Как и большинство операций, операцию копирования можно осуществлять через пункт меню Правка – Копировать (или с помощью панели инструментов специальными кнопками), а также с помощью мыши.
Чтобы скопировать ячейку или блок ячеек в другое место таблицы, необходимо их выделить, после чего курсор мыши подводится к границе блока, затем нажимается клавиша Ctrl, при этом к курсору мыши добавится знак "плюс". Теперь блок можно "тащить", нажав и удерживая левую кнопку мыши. На протяжении всей процедуры клавиша Ctrl должна быть нажата.
Удобным является копирование объектов с помощью маркера автозаполнения.
Рис. 8. Маркер автозаполнения
Копировать таким способом можно только по столбцу (вниз или вверх) или только по строке (вправо или влево). Для осуществления процедуры курсор, установленный в какой-либо ячейке ухватывают за маркер (при этом курсор превращается в крестик) и, при нажатой левой кнопке мыши, "протаскивают" по столбцу или строке, при этом данные, находящиеся в родительской ячейке, копируются в соответствующий, оказавшийся выделенным, блок ячеек.
Механизм копирования маркером автозаполнения обладает удобными возможностями, связанными с построением рядов данных. Например, в ячейке А1 находится число, если установить в нее курсор и протащить его по столбцу или строке, нажав и удерживая клавишу Ctrl, то число будет копироваться в каждую новую ячейку увеличенным на единицу относительно предыдущей ячейки. Пусть имеется блок из двух ячеек- А1 и А2, содержащих числа 10 и 20. Если этот блок выделить и протащить вниз по столбцу, то получим ряд, каждое число которого образуется прибавлением 10 к предыдущему. Если при этом удерживать клавишу Ctrl, то изменение чисел не происходит, числа не меняются и при копировании блока по строке.
При копировании маркером автозаполнения может осуществляться пересчет не только чисел, но и дат, времени и даже текста.
Перемещение (перенос) отличается от копирования тем, что переносимые данные в источнике не сохраняются. Перенос содержимого блока ячеек осуществляется также с помощью мыши. Необходимо выделить блок, затем навести курсор мыши на его границу и, удерживая левую кнопку, переносить информацию в нужное место таблицы.
При копировании формул, содержащих ссылки на другие ячейки, адреса ячеек в формуле будут изменяться. Если ячейку с такой формулой копируют по строке (горизонтально), то изменяется буквенная часть адресов (ссылок), если по столбцу- то числовая. Если ячейка копируется по диагонали, то изменяется и то и другое.
Например, в ячейке С1 содержится формула =А1+В1. При копировании ее в ячейку F1 формула в ячейке F1 будет выглядеть по другому: =D1+E1, при копировании в С5: =А5+В5, а при копировании в F5: =D5+E5. Ссылки в рассмотренных формулах называются относительными, т.к. они изменяются относительно тех ячеек, куда эти ссылки копируются. Относительные ссылки удобны при вводе однотипных формул.
Иногда необходимо, чтобы в формулах ссылки на ячейки не изменялись при копировании. В этом случае в ссылку включается символ "$", который является признаком постоянства. Перед какой частью ссылки он стоит, та и не будет изменяться.
Например, в формуле =$А$1+В1 ссылка на ячейку А1 не изменится при любом копировании, тогда как B1, будучи скопирована, преобразуется. Если $ убрать перед числом в ссылке (формула будет выглядеть: =$А1+В1), тогда имя столбца будет фиксировано, а номер строки - нет. Наоборот, оставим признак постоянства перед номером строки (формула будет выглядеть: =А$1+В1)- номер будет не изменен, а буква от копирования к копированию - будет меняться. Ссылки, в которых присутствует символ "$", называются абсолютными, т.к. при копировании всегда указывают на постоянный адрес.
Контрольные вопросы
1. Какой редактор называется табличным?
2. Какие типы данных использует табличный редактор Excel?
3. Как задаются имена ячейкам?
4. Какие виды ссылок существуют?
5. Что такое строка формул?
6. Что такое маркер автозаполнения?
7. Что такое циклические ссылки?
8. Перечислите все возможные способы копирования и перемещения данных в MS Excel.
9. Перечислите способы форматирования листов в MS Excel.
10. Какие типы данных может содержать ячейка в MS Excel?
11. Какими способами можно выделить ячейки (блок ячеек) в MS Excel?
12. Что такое формула?
13. Какие символы и знаки могут использоваться в формулах?
14. Как можно изменить обрамление ячейки (блока ячеек)?
15. Какими способами можно изменить ширину столбца (столбцов) и высоту строки (строк)?
Задание
- Введите в Excel следующие таблицы, заполнив расчетами необходимые поля
| С Ч Е Т | ||||
| КУРС ДОЛЛАРА | 28,5 | |||
| ТОВАР | ЦЕНА($) | КОЛ-ВО | СУММА($) | СУММА(РУБ) |
| 1.видеокамера TR-270 | ||||
| 2.видеокамера TR-350E | ||||
| 3.видеокамера TR-20СAE | ||||
| 4.видеокамера TR-202E | ||||
| 5.видеокамера TR-470E | ||||
| ИТОГО |
| кол-во предприятий | доход | |||
| (тыс) | (%) | (млрд.$) | (%) | |
| единоличные владения | ||||
| товарищества | ||||
| корпорации | ||||
| итого |
- Укажите соответствующие типы данных для столбцов, где указаны денежные величины.