Абсолютная и относительная адресация

Электронные таблицы

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

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

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

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

Появление электронных таблиц исторически совпадает с началом распространения ПК. Первая программа для работы с электронными таблицами – табличный процессор была создана в 1979 г., предназначалась для компьютеров типа Apple II и называлась VisiCalc. В 1982 г. появляется знаменитый табличный процессор Lotus 1-2-3, предназначенный для IBM PC. Lotus объединял в себе вычислительные возможности электронных таблиц, деловую графику и функции реляционной СУБД. Популярность табличных процессоров росла очень быстро. Появлялись новые программные продукты этого класса: Multiplan, Quattro Pro, SuperCalc и др. Одним из самых популярных табличных процессоров сегодня является MS Excel, входящий в состав пакета Microsoft Office.

Программный продукт MS Excel 2007, разработанный фирмой Microsoft, является популярным средством для работы с электронными таблицами. Функциональные возможности и вычислительные средства MS Excel 2007 позволяют решать многие инженерные и экономические задачи, представляя данные не только в табличном, но и в графическом виде.

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

MS Excel позволяет:

■ строить сложные формулы для расчетов;

■ проводить статистическую обработку данных;

■ создавать сводные таблицы;

■ моделировать бизнес-ситуации;

■ записывать макросы с помощью макрорекодера и редактировать их;

■ делать запросы к внешним БД.

Рабочая книга – это обычный документ или тип файла в Microsoft Excel for Windows (расширение файла – xls).

Каждая книга может содержать несколько листов. По умолчанию они называются: Лист 1, Лист 2 и т. д. Эти имена выводятся на ярлычках, расположенных в нижней части окна рабочей книги (рис. 12. 1).

Рис. 12. 1. Ярлыки листов

Рабочий лист представляет собой сетку из столбцов и строк (рис. 12. 2).

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

Рис. 12. 2. Пример рабочего листа

Ячейка – минимальная информационная единица (рис. 12. 3). Она может содержать разные типы данных (текст, числа, даты и т. п. ), но внутри одной ячейки могут храниться данные только одного типа. Ячейки могут находиться в состоянии: а) активная или выделенная ячейка (можно выделить блок ячеек); б) редактируемая ячейка (может быть только одна в определенный момент

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

Рис. 12. 3. Пример ячейки

для активных ячеек, выполнимы для ячеек в состоянии редактирования.

Лист диаграммы – лист, содержащий только диаграмму Microsoft Excel.

Диаграмма – это графическое представление данных рабочего листа (рис. 12. 4).

Рис. 12. 4. Пример диаграммы

Панели инструментов позволяют упорядочить команды Microsoft Excel так, чтобы их было легко найти и использовать. Панели инструментов можно настраивать: добавлять и удалять меню и кнопки, создавать новые панели инструментов, а также отображать, скрывать и перемещать существующие панели инструментов. Настройка главного меню осуществляется точно так же, как и любой другой встроенной панели инструментов, например в главное меню можно добавлять и удалять кнопки и меню. При выборе меню отображается список (рис. 12. 5).

Рис. 12. 5. Пример панели инструментов

После запуска MS Excel на экране появляется окно (рис. 12. 6), состоящее из нескольких элементов:

■ строки заголовка, содержащего имя программы, предлагаемого названия файла, стандартных кнопок Windows;

■ строки меню, содержащего пункты главного меню;

Рис. 12. 6. Окно MS Excel

■ панелей инструментов (пиктограмм), состав которых может определить сам пользователь с помощью пункта меню Вид/ Панели инструментов;

■ строки формул, отображающей вводимую информацию;

■ линеек прокрутки (вертикальной и горизонтальной), позволяющих работать с большими таблицами;

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

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

Контекстное меню активизируется нажатием правой кнопки мыши (или Shift + F10). Для выхода из контекстного меню нужно щелкнуть кнопкой мыши вне его или нажать Esc.

Типы данных, используемых в Excel

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

Рис. 12. 7. Панель инструментов Главная

Вкладка Число обеспечивает задание типа числового формата, определение числа десятичных знаков (рис. 12. 8).

Вкладка Выравнивание обеспечивает задание порядка выравнивания содержимого ячеек по горизонтали и вертикали, ориентации текста (поворот от +90 до -90°), разбивки текста на строки и переноса по словам, а также объединение ячеек и автоподбор ширины столбца (рис. 12. 9).

Вкладка Шрифт обеспечивает выбор типа и размера шрифта, стиля начертания и дополнительных текстовых эффектов; Граница – выбор

Рис. 12. 8. Примеры форматов чисел, дат и времени

Рис. 12. 9. Примеры выравнивания содержимого ячеек

цвета и типа линии, способа обрамления ячеек; Вид – выбор цвета и узора заливки ячеек; Защита – установку защиты ячеек, скрытие формул.

Формулы и функции

Ввод формулы всегда начинают со знака " равно" ( = ) или " плюс" ( + )•

Например: = 46 + 55; = 200 * В5; = А7/В4.

Примечания.

· 1. В ячейке, содержащей формулу (по умолчанию), виден только результат вычислений. Саму формулу можно увидеть в строке формул, когда данная ячейка станет активной.

· 2. Excel вычисляет формулу каждый раз, когда изменяется содержимое таблицы, ссылающееся на данную формулу.

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

· 4. При вводе десятичных чисел используется запятая (, ) в качестве десятичного разделителя.

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

Они сгруппированы по категориям и имеют одинаковый синтаксис: имя функции и ее аргументы.

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

Имя функции (< аргумент1; аргумент2; аргумент3 и т. д. )

Имя функции показывает, о каких вычислениях идет речь. Примеры имен функции – СУММ, СРЗНАЧ и др.

Аргументы – значения, которые функция использует, вычисляя результат.

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

Результат – значение, полученное при вычислении функции.

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

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

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

Функции в Excel подразделяются на следующие основные группы: математические, арифметические и тригонометрические; для работы с датами и временем; финансовые; логические; для работы со ссылками и массивами; для работы с БД; статистические; текстовые и др.

Абсолютная и относительная адресация

По умолчанию MS Excel использует в формулах относительные ссылки: = В2 * С2.

Если скопировать эту формулу в две ячейки, расположенные под ячейкой D2, то Excel вставит копии приведенной формулы, при этом видоизменит их следующим образом: в ячейку D3 будет скопирована формула = В3 * С3, а в ячейку D4 – формула = В4 * С4. Другими словами, Excel изменяет ссылки на ячейки в соответствии с новым положением формулы.

Если необходимо, чтобы адреса ячеек копировались без изменений, то используют абсолютные ссылки: = В2 * С2 * $В$6.

В ссылке на ячейку В6 перед буквой столбца и перед номером строки стоит знак доллара ($). Это означает, что данная ссылка является абсолютной. При копировании этой формулы в две ячейки снизу будут получены следующие формулы: в ячейку D3 будет скопирована формула = В3 * С3 * $В$6; ячейку D4 – формула = В4 * С4 * $В$6.

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

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

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

■ А1 – относительная ссылка;

■ $А$1 – абсолютная ссылка;

■ $А1 – смешанная ссылка (абсолютной является координата столбца);

■ А$1 – смешанная ссылка (абсолютной является координата строки).

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

12 Следующая ⇒