Краткие теоретические сведения

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

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

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

· исследование влияния различных факторов на данные;

· решение задач оптимизации, линейного программирования;

· получение выборки данных, удовлетворяющих определенным критериям;

· построение графиков и диаграмм;

· статистический анализ данных;

· работа с базами данных

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

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

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

Книги, созданные в среде Excel представляют собой набор листов, следующих типов:

? рабочие листы;

? листы диаграмм;

? листы макросов;

? листы модулей, написанных языком Visual Basic;

? листы диалога.

Рабочие листы или просто листы– это электронные таблицы, которые состоят из столбцов (колонок) и строк. Каждый лист имеет ярлычок с именем. Столбцы обозначаются латинскими буквами (A, B, C,…), строки – цифрами (1, 2, 3,…).

Область пересечения строки и столбца называется ячейкой. Обозначение (адрес) ячеек состоит из обозначения колонки и строки. Например, А4 или BZ15.

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

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

Чтобы ввести информацию в таблицу, надо просто выбрать ячейку, в которой нужно вводить данные, и начать печатать. Как только информация начнет вводиться, все введенные символы станут появляться в двух местах: в активной ячейке и на панели формул. Завершив набор содержимого клетки, нужно нажать Enter. При этом курсор переходит на соседнюю клетку. Каждый раз, когда курсор будет установлен на конкретную клетку, ее содержимое появляется на панели формул. Если курсор ошибочно был установлен не на ту клетку и начался ошибочный ввод информации до завершения ввода нажать Esc.

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

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

- чтобы отредактировать формулу прямо в ячейке, нужно щелкнуть по клетке дважды или поставить на нее табличный курсор и нажать F2 - курсор будет помещен в конец отображаемой строки;

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

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

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

· постоянные значения,

· ссылки на ячейки,

· имена,

· функции или операторы.

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

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

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

Ссылки бывают абсолютные и относительные. По умолчанию MS Excel создает относительные ссылки.

При копировании или перемещении формулы с относительными ссылками MS Excel изменяет ссылки на ячейки в соответствии с новым расположением формулы.

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

Кроме абсолютной ссылки на ячейку, имеются еще два типа абсолютных ссылок:

· Абсолютная ссылка на строку.В этом случае знак $ размещается только перед номером строки. Например, В$3 – это абсолютная ссылка на третью строку.

· Абсолютная ссылка на столбец.В этом случае знак $ размещается только перед именем столбца. Например, $В3 - это абсолютная ссылка на столбец В.

Замечание. Клавиша F4 позволяет ускорить ввод абсолютной ссылки в формулу. Выделите в формуле ссылку на ячейку или диапазон, а затем нажмите клавишу F4. Знаки $ будут добавлены в формулу перед номером строки и именем столбца автоматически. Повторное нажатие F4 уберет знак $ перед именем столбца и обеспечит абсолютную ссылку только на строку. Если нажать на F4 третий раз, то знак $ останется только перед именем столбца.

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

Excel выводит в ячейку сообщение об ошибке, если формула в этой ячейке не может быть правильно вычислена. Ошибки, которые могут быть возвращены формулами рабочего листа, даны в приложении 4.

Порядок проведения работы:

Создание книги

1. Откройте новую книгу MS Excel.

1.1. Проверьте, что установлены на вкладке ВИД:режим просмотра книги – ОБЫЧНЫЙ;

1.2. масштаб – 100%

или в правом нижнем углу окна Excel- Сохраните эту книгу под именем ОПЛАТА ПОСТАВОК в папку МОИ ДОКУМЕНТЫ – кнопка OFFICE/СОХРАНИТЬ КАК.

Формирование списков

1. Переименуйте Лист1[1] в КЛИЕНТЫ, Лист2 в ДОГОВОР – контекстное меню для соответствующего ярлыка листа/ПЕРЕИМЕНОВАТЬ.

2. На листе КЛИЕНТЫ создайте список клиентов, начиная от ячейки А1[2] (Рис. 1). Для полного отображения информации в ячейках увеличивайте ширину колонок (Рис. 2).

Если после ввода значения в ячейку в ней отобразились #####, значит надо увеличить ширину этой колонки.

Рис. 1

Рис. 2

3. На листе ДОГОВОР создайте список договоров, начиная от ячейки А1 (Рис. 5). При формировании шапки таблицы сделайте по горизонтали выравнивание по центру, по вертикали – выравнивание по верхнему краю, переносить по словам:

3.1.  для блока ячеек A1:D1 воспользуйтесь соответствующими кнопками на вкладке ГЛАВНАЯ/группа ВЫРАВНИВАНИЕ или установите в диалоговом окне ФОРМАТ ЯЧЕЕК на вкладке ВЫРАВНИВАНИЕ (Рис. 3);

Рис. 3

3.2. установите текстовый формат для блока ячеек А2:А4– вкладка ГЛАВНАЯ/группа ЧИСЛО/список ЧИСЛОВОЙ ФОРМАТ;

3.3. даты проще вводить – 14.1.11 и 15.1.11

3.4. для правильности ввода названий фирм (т.е. таких же, как на листе КЛИЕНТЫ) воспользуйтесь проверкой данных:

3.4.1. но предварительно: для вывода в списке клиентов от А до Я отсортируйте таблицу на листе КЛИЕНТЫ по возрастанию алфавита по графе НАЗВАНИЕ ФИРМЫ – сделайте активной значимую ячейку из графы НАЗВАНИЕ ФИРМЫ и активизируйте вкладку ГЛАВНАЯ/группа РЕДАКТИРОВАНИЕ/список СОРТИРОВКА И ФИЛЬТР/СОРТИРОВКА ОТ МИНИМАЛЬНОГО ДО МАКСИМАЛЬНОГО ( ) или вкладку ДАННЫЕ/группа СОРТИРОВКА И ФИЛЬТР;

3.4.2. блоку ячеек А2:А4 (основа списка) на листе КЛИЕНТЫ присвойте имя НАЗВАНИЕ_ФИРМЫ – вкладка ФОРМУЛЫ/группа ОПРЕДЕЛЕННЫЕ ИМЕНА/кнопка ПРИСВОИТЬ ИМЯ или введите имя блока в область ИМЯ (Рис. 4). Корректировка имен – кнопка ДИСПЕТЧЕР ИМЕН;

Рис. 4

3.4.3. теперь можно сформировать проверку данных - на листе ДОГОВОР для блока ячеек С2:С4 (применение списка) активизируйте кнопку ПРОВЕРКА ДАННЫХ на вкладке ДАННЫЕ/группа РАБОТА С ДАННЫМИ. Установите в окне ПРОВЕРКА ВВОДИМЫХ ЗНАЧЕНИЙ тип данных СПИСОК, источник (нажмите клавишу F3 – вставка имени) НАЗВАНИЕ_ФИРМЫ. В этом же окне удаление списка для блока;

3.4.4. воспользуйтесь списком клиентов при вводе данных в блок ячеек С2:С4 на листе ДОГОВОР.

3.5. создайте примечание для ячейки D3 на листе ДОГОВОР, в котором укажите обоснование большого срока оплаты – вкладка РЕЦЕНЗИРОВАНИЕ/группа ПРИМЕЧАНИЯ (все работы с примечаниями). Текст примечания – НОВЫЙ КЛИЕНТ (Рис. 5);

Рис. 5

4. Скопируйте форматирование с блока ячеек A1:D4 листа ДОГОВОР на блок ячеек A1:С4 листа КЛИЕНТЫ:

4.1. для блока ячеек A1:D1 листа ДОГОВОР активизируйте кнопку ФОРМАТ ПО ОБРАЗЦУ - вкладка ГЛАВНАЯ/группа буфера обмена;

4.2. выделите блок ячеек A1:С4 листа КЛИЕНТЫ. Результат - Рис. 6.

Удаление форматирования – вкладка ГЛАВНАЯ/группа РЕДАКТИРОВАНИЕ/список ОЧИСТИТЬ/ОЧИСТИТЬ ФОРМАТЫ.

Рис. 6

5. Сохраните сделанные в книге изменения – кнопка OFFICE/СОХРАНИТЬ или кнопка СОХРАНИТЬ на панели быстрого доступа.

Периодически по умолчанию работает автосохранение - кнопка OFFICE/кнопка ПАРАМЕТРЫ EXCEL/группа СОХРАНЕНИЕ/группа СОХРАНЕНИЕ КНИГ.