Технологии обработки экономической информации на основе табличного процессора Excel

Общие сведения о табличном процессоре MS Excel

 

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

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

 

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

Запуск Excel:

• с панели Microsoft Office;

• Пуск — Программы — MS Excel',

• двойной щелчок по значку MS Excel на Рабочем столе.

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

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

Каждый лист состоит из набора строк и столбцов. Строки и столбцы идентифицируются. Идентификаторами строк листа являются числа в интервале от 1 до 65 536, а в качестве заголовков 256-ти столбцов обычно используются буквы латинского алфавита от А до Z. После столбца Z идет столбец АА, за которым следуют АВ, АС и т.д. После столбца AZ идут ВА, ВВ и так до самого последнего, который обозначается IV.

Области, находящиеся на пересечении строк и столбцов, называют ячейками. На одном листе располагаются более шестнадцати миллионов ячеек. Идентификаторы столбцов и строк однозначно определяют адрес любой из 16 777 216 ячеек.

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

Новые файлы создаются выбором команды меню Файл — Создать.

Новые файлы получают имена Книга1, Книга2, КнигаЗ... и расширение.xls. Вновь созданный файл можно переименовать на этапе сохранения. MS Excel сохраняет целиком рабочую книгу. Активный файл — это файл, с которым в данный момент работает программа. Имя активного файла отображается в зоне заголовка окна программы MS Excel.

В одном сеансе работы с программой можно открыть несколько рабочих книг. Выбрав пункт меню Окно, можно увидеть список открытых книг (список имен файлов).

С помощью команд меню Окно — Расположить можно отобразить на экране все открытые книги.

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

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

Действия с листами рабочей книги

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

 

Предлагаемое по умолчанию количество листов можно скорректировать командой Сервис — Параметры — Общие.

 

Листы можно добавлять и удалять. Чтобы добавить в рабочую книгу лист, можно выбрать команду Вставка — Лист. Чтобы удалить лист, нужно щелчком левой кнопки мыши по ярлычку листа сделать лист активным (ярлычок активного листа выводится на первый план), затем выбрать команду Правка — Удалить лист. Ярлыки рабочих листов расположены в порядке возрастания номеров. Листу можно присвоить имя, вызвав контекстное меню на ярлыке листа и выбрав команду Переименовать (не более 31 символа).

 

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

 

Электронная таблица состоит из столбцов и строк. Каждый столбец имеет уникальное имя, состоящее из букв латинского алфавита. Общее число столбцов на каждом рабочем листе равно 256. Имена столбцов отображаются в верхней части электронной таблицы прописными латинскими буквами. Имена строк отображаются слева от строки. Имена строк и столбцов нельзя изменить. Чтобы попасть в самую нижнюю слева ячейку листа, можно один раз или несколько раз (в зависимости от заполненности электронной таблицы) нажать одновременно две клавиши на клавиатуре: CTRL и «стрелка вниз». Чтобы попасть в самую нижнюю справа ячейку листа, можно один раз или несколько раз нажать одновременно две клавиши: CTRL и «стрелка вправо». Чтобы попасть в самую верхнюю слева ячейку листа А1, можно нажать комбинацию клавиш: CTRL + НОМЕ.

Ввод данных

 

С помощью MS Excel можно вводить и обрабатывать следующие данные.

 

ТЕКСТ

Текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например приведенные ниже записи интерпретируются как текст: 10АА109, 127AXY, 12-976, 208 4675. Введенный текст выравнивается в ячейке по левому краю.

 

ЧИСЛА

Вводимые в ячейку числа интерпретируются как константы.

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

 

Символы, которые рассматриваются как числа, зависят от установленных параметров в компоненте Язык и стандарты Панели управления.

Перед рациональной дробью следует вводить 0 (нуль), чтобы избежать ее интерпретации как формата даты; например, вводите 0 1/2.

Перед отрицательным числом необходимо вводить знак минус (-) или заключать его в круглые скобки ().

Введенные числа выравниваются в ячейке по правому краю.

 

ДАТЫ И ВРЕМЯ

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

— точка, например, 01.04.99 или 01.04.1999;

— дефис, например, 01-04-99 или 01-апр-1999;

— косая черта с наклоном вправо, например, 01/04/99 или 01/04/1999.

Введенная дата всегда интерпретируется MS Excel как число и выравнивается по правому нижнему краю ячейки.

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

Для отображения времени суток в 12-часовом формате следует вводить букву а или р, отделенную пробелом от значения времени, например 9:00 р. В противном случае время будет интерпретировано на основе 24-часового формата. С датами можно производить вычисления: складывать, вычитать, использовать в функциях. Правила составления формул и использования функций, оперирующих с числами в формате Дата, точно такие же, как при работе с обычными числами.

Часто при вычислениях приходится опираться на текущую дату. Чтобы не вводить ее каждый раз, рекомендуется использовать функцию СЕГОДНЯ. Особенность этой функции заключается в том, что у нее нет параметров. Эта функция выдает текущую дату по системному таймеру. Доступ к функции СЕГОДНЯ, а также к другим функциям из категории Дата и время.

 

ПОСЛЕДОВАТЕЛЬНОСТЬ ДАТ

Для создания ряда дат, в котором меняется день (например: 01.04.99, 02.04.99, 03.04.99), можно ввести с клавиатуры первый элемент ряда, в нашем примере это число 01.04.99, затем подвести мышь к маркеру заполнения и протащить его вниз на нужное число ячеек.

Для создания ряда дат, в котором меняется месяц или год (например: 01.04.99, 01.05.99, 01.06.99), можно ввести с клавиатуры два элемента ряда в разные ячейки, в нашем примере это числа 01.04.99 и 01.05.99. Затем выделить две ячейки с введенными датами, подвести мышь к маркеру заполнения и протащить его вниз на нужное число ячеек. Аналогичные действия нужно выполнить, чтобы создать ряд с датами, в которых изменяется год.

При протаскивании маркера заполнения вниз или вправо ряд с датами создается в возрастающем порядке, при протаскивании маркера вверх или влево ряд создается в убывающем порядке.

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

Для заполнения рядов чисел или дат, для создания арифметической или геометрической прогрессий можно также воспользоваться командами главного меню Правка — Заполнить —Прогрессия.

Время суток и даты можно складывать, вычитать и производить с ними другие вычисления-

В MS Excel используется система отсчета дат с начала столетия; в этой системе числу 1 соответствует дата 1 января 1900 г. В MS Excel для Macintosh используется система отсчета дат, начиная с 1904 года; в ней числу 1 соответствует 2 января 1904 г. Чтобы изменить систему отсчета дат, выбирается команда Сервис — Параметры — вкладка Вычисления. В группе Параметры книги установите флажок Система дат 1904. Установите систему дат 1904 в книге, используемой совместно с другими книгами, в которых эта система отсчета дат была установлена ранее.

 

АДРЕСАЦИЯ В Excel

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

 

Для задания адреса ячейки в Excel можно использовать два режима адресации ячеек: в формате R1C1 и в формате А1 (переключение с одного на другой осуществляется командой Сервис — Параметры — вкладка Общие — Стиль ссылок). Например, адреса ячеек в формате R1C1 выглядят так:

RC Текущая ячейка

RC[-1] Ячейка слева от текущей (относительная адресация)

R[1]C Ячейка под активной (относительная адресация)

R10C12 Ячейка в 10-й строке и в 12-м столбце (абсолютная адресация).

Обычно в Excel используются ссылки типа А1, где столбцы обозначаются буквами, а строки числами.

Для указания ссылки на ячейку вводится буква заголовка столбца и номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой.

Адресация может быть абсолютной, относительной и смешанной.

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

Для создания абсолютной ссылки на ячейку В11 ставится знак доллара ($) перед той частью ссылки, которая не должна изменяться, поэтому в данном примере абсолютная ссылка записывается: =В13*$В$11. Для установки знака доллара используется клавиша F4, которую нужно нажимать до тех пор, пока на экране не появится нужный вид ссылки. Таким образом, способ адресации влияет на адреса ячеек в формулах при их копировании из одной ячейки в другую.

Если знак доллара стоит в таких вариантах А$4 или $А4, то адресация называется смешанной. То измерение, у которого стоит знак доллара, при копировании данного адреса не изменяется, а то измерение, где нет доллара — настраивается на новое местоположение.

 

ЗАГОЛОВКИ И ИМЕНА В ФОРМУЛАХ

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

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

Если данные не имеют заголовков или размешены на другом листе книги, можно создать имя, описывающее ячейку или группу ячеек. Использование имен может упростить понимание формулы. Например, формула «=СУММ(Продано_в_первом_квартале)» проще для понимания, чем формула «=СУММ(Продажа!С20:С30)». В этом примере имя «Продано в первом квартале» представляет группу ячеек «С20:С30» на листе «Продажа».

Имена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек «А20:А30» в первом листе рабочей книге, то это имя можно применить в любом другом листе той же рабочей книги для ссылки на эту группу. Имя можно присвоить формуле или постоянному значению (константе). Например, имя «Процентная Ставка», которому присвоено значение 10%, можно использовать в любом месте для вычисления процентов. По умолчанию имена являются абсолютными ссылками.

 

ПРИСВАИВАНИЕ ИМЕНИ ЯЧЕЙКАМ РАБОЧЕЙ КНИГИ

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

 

Как присвоить ячейке имя?

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

2. Укажите поле имени, которое расположено слева в строке формул.

3. Введите имя ячеек.

4. Нажмите клавишу ENTER.

Или выберите команду Вставка — Имя — Присвоить.

Редактирование данных

 

ВЫДЕЛЕНИЕ РАЗЛИЧНЫХ ЧАСТЕЙ ТАБЛИЦЫ

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

Для выделения прямоугольного диапазона можно щелкнуть левой кнопкой мыши в левом верхнем углу диапазона ячеек, затем, удерживая нажатой клавишу SHIFT, щелкнуть в правом нижнем углу диапазона. Или очертить диапазон ячеек указателем мыши.

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

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

 

ИЗМЕНЕНИЕ ШИРИНЫ СТОЛБЦА И ВЫСОТЫ СТРОКИ

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

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

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

Для установки ширины столбца по умолчанию используется команда Формат — Столбец — Ширина.

 

МАНИПУЛЯЦИИ С ЯЧЕЙКАМИ

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

Команды Правка — Заполнить — Вниз/Влево/Вправо/Вверх по­зволяют скопировать содержимое первой из выделенных ячеек во все остальные в заданном направлении.

Для вставки скопированных или перемещенных ячеек между су­ществующими ячейками надо:

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

для перемещения выбранных символов выбирается команда Вырезать, а для копирования — команда Копировать-,указывается левый верхний угол диапазона, в который следует поместить вырезанные или скопированные ячейки;

Вставка — Вырезанные/Скопированные ячейки;

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

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

Вместо копирования содержимого ячеек целиком можно скопи­ровать только часть, например значение, формулу, примечание или формат ячейки, без копирования самой формулы, тогда используется команда Копировать. Затем указывается левый верхний угол области вставки и выполняется команда Правка — Специальная вставка, в блоке Вставить выбирается нужный параметр.

Для удаления содержимого ячеек используется клавиша DEL. Команда Правка — Удалить используется для удаления самих ячеек или строк/столбцов.

Для вставки новых ячеек (строк/столбцов) используется команда Вставка — Ячейки (Строки/Столбцы).

 

Форматирование данных

Формат числа, назначаемый ячейке, определяет способ просмотра числа на листе.

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

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

 

ПОЛЬЗОВАТЕЛЬСКИЙ ФОРМАТ

Можно создавать собственные пользовательские форматы: Формат — Ячейки — вкладка Число — Все команды — ввести нужную последовательность форматирующих символов. Примеры приведены в таблице ниже.

Пользовательский числовой формат может содержать до четырех частей (спецификаций), разделенных точками с запятой, каждая из которых определяет формат соответственно для положительных, отрицательных, нулевых чисел и текста:

Положительный; Отрицательный; Нулевой; Текстовый