uzluga.ru
добавить свой файл
1.1             Обработка данных с помощью электронных таблиц

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

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

  • Автоматизации итоговых вычислений

  • Подготовки табличных документов

  • Ведения простых баз данных и их обработки

  • Построения диаграмм и графиков

  • Создания сводных таблиц

  • Совместной работы с экономическими и бухгалтерскими документами

Среди наиболее известных программных приложений, предназначенных для ведения электронных таблиц, могут быть названы SuperCalc, Lotus 1-2-3, Quattro Pro. Программа Microsoft Excel, входящая в состав офисного пакета Microsoft Office 2000, занимает лидирующее положение среди электронных таблиц, совмещая в себе преимущества как электронных таблиц со средствами финансового и статистического анализа, так и средств визуального программирования посредством встроенного языка программирования Visual Basic for Applications (VBA). VBA позволяет автоматизировать весь комплекс работ от сбора информации, её обработки, анализа до создания отчётной документации и её публикации как для офисного пользования, так и на Web-узлах.

1.2    Основные понятия электронных таблиц

Документ, созданный в MS Excel, называется рабочей книгой. Рабочая книга состоит из набора рабочих листов. Листов в книге Excel может быть до 255. Каждый рабочий лист имеет имя, по умолчанию Лист1, Лист2, Лист3. Названия листов отображаются в нижней части листа. С помощью ярлычков, на которых написаны имена листов, можно переключаться между рабочими листами, входящими в рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть на его ярлычке, либо нажать правой кнопкой мыши на ярлычке и выбрать пункт Переименовать. Листам можно давать произвольные имена длиною до 31-го символа, исключая символы * : / \  ? [ ] 

Заголовок книги

Панель форматирования

Главное меню



















http://sergun-2.narod.ru/metod_ex.files/image004.gif




скругленная прямоугольная выноска: главное меню















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

скругленная прямоугольная выноска: текущая ячейка

Имена столбцов

скругленная прямоугольная выноска: имена столбцов

Номера строк

скругленная прямоугольная выноска: номера строк

Стандартная панель

скругленная прямоугольная выноска: стандартная панель

Строка формул

скругленная прямоугольная выноска: строка формулhttp://sergun-2.narod.ru/metod_ex.files/image012.jpg

Рисунок 1

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

Если пользователю необходимо добавить новый рабочий лист, он может сделать это, выполнив команду Вставка / Лист. Для удаления листа следует выполнить команду Правка / Удалить лист, при этом необходимо помнить о том, что удалённый лист восстановить невозможно.

Пространство рабочего листа состоит из строк и столбцов. По умолчанию каждый рабочий лист имеет 256 столбцов, (каждый столбец имеет имя, озаглавленное латинской буквой) и 65536 строк (нумерация от 1 до 65536), таким образом, всего на листе имеется 16777216 ячеек.

На пересечении строк и столбцов образуются ячейки таблицы. Ячейка – это минимально адресуемый элемент рабочего листа.  Имя ячейки (адрес) состоит из имени столбца и номера строки, например, A10 или D23. Адресация ячеек используется при записи формул. Одна из ячеек всегда является активной, и в ней  производятся операции ввода и редактирования. Группа ячеек называется диапазоном. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах, например: А5:C20.

Выделение ячеек с помощью мыши

Таблица 1

Чтобы выделить:

Действия пользователя

Лист

Щёлкнуть мышью на сером прямоугольнике, расположенном на пересечении строки с номером 1 и столбца с именем А

Столбец

Щёлкнуть на заголовке столбца (на имени столбца)

Строку

Щёлкнуть на номере строки

Ячейку

Щёлкнуть на ячейке мышью

Диапазон несмежных ячеек

Выделить мышью первый диапазон. Удерживая клавишу CTRL, выделить другой диапазон и т. д.

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

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

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

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

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

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

*   Ввести данные с клавиатуры

*   Нажать клавишу Enter

или

*   Сделать двойной щелчок мышью на нужной ячейке

*   Ввести данные с клавиатуры

*   Нажать клавишу Enter

Редактировать данные можно также несколькими способами, например:

Для замены одних данных в ячейке другими следует сделать её активной и ввести новые данные.

или

*   Сделать активной ячейку

*   Выполнить двойной щелчок на редактируемой ячейке

или

*   Сделать активной ячейку

*   Нажать клавишу F2

или

*   Сделать активной ячейку

*   Сделать щелчок в строке формул

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

1.5 Выравнивание и изменение ориентации текста и чисел

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

http://sergun-2.narod.ru/metod_ex.files/image014.jpg

Рисунок 2

Чтобы выровнять содержимое ячейки или диапазона:

*   Выберите ячейку или диапазон

*   Выполните команду Формат / Ячейки. На экране отобразится диалоговое окно Формат ячеек (рис.3)

http://sergun-2.narod.ru/metod_ex.files/image016.jpg

Рисунок 3

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

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

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

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

*   Выполнить команду Формат / Ячейки. Откроется диалоговое окно Формат ячеек (рис.4)

*   Выберите вкладку Число этого диалогового окна и установите тот тип формата, который вам нужен

http://sergun-2.narod.ru/metod_ex.files/image018.jpg

Рисунок 4

1.6       Основные типы форматов

Таблица 2

Формат

Описание

Общий

Этот формат принят по умолчанию

Числовой

Число отображается с заданным количеством десятичных знаков после запятой

Денежный

После изображения числа может помещаться знак валюты

Финансовый

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

Дата

Эта категория позволяет вывести дату по одному из пятнадцати форматов

Время

Эта категория позволяет вывести время по одному из восьми форматов

Процентный

Этот формат позволяет выводить число в виде процентов со знаком % и определять количество выводимых знаков после десятичной точки

Дробный

Дробный формат используется для представления чисел в виде смешанного числа

Экспоненциальный

В этом формате число представляется в виде mE±p, где m – мантисса числа, Е – символ, обозначающий основание десятичной ССЧ, p – порядок числа, например число 5,34´1025 записывается как 5.34Е+25

Текстовый

Применение этого формата к числовому значению позволяет рассматривать его как текст

Дополнительный

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

Все форматы

Эта категория позволяет создавать пользовательские форматы

1.7       Вычисления в электронных таблицах

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

Примеры формул

Таблица 3

Формула

Описание

=В1+В2

Складывает содержимое ячеек В1 и В2

=А1^(1/3)

Возводит в степень (1/3) содержимое ячейки А1

=СУММ(А1:А5)

Возвращает сумму значений из диапазона А1:А5

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

1.8  Ошибки, возвращаемые формулами

Таблица 4

Ошибка

Описание

#Н/Д

Н/Д – является сокращением термина Неопределённые Данные. Помогает предотвратить использование ссылки на пустую ячейку

#ЧИСЛО!

Функция с числовым аргументом использует неприемлемый аргумент

#ИМЯ?

Ошибка в написании имени или используется несуществующее имя

#ССЫЛКА!

Используется ссылка на несуществующую ячейку

#ЗНАЧ!

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

#ДЕЛ/0!

В качестве делителя используется ссылка на ячейку, в которой содержится ненулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль)

#ПУСТО!

Используется ошибочная ссылка на ячейку

#############

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

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

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

*   Ввести адрес ячейки с клавиатуры

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

                                                                                                                           

http://sergun-2.narod.ru/metod_ex.files/image020.jpg

Рисунок 5

В Excel при работе с формулами используется два типа адресации ячеек: относительная и абсолютная.

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

 http://sergun-2.narod.ru/metod_ex.files/image021.gifhttp://sergun-2.narod.ru/metod_ex.files/image023.jpg

Рисунок 6

При абсолютной адресации адреса ячеек при копировании формулы не изменяются. На рис. 7 показан пример из которого видно, что если копировать формулу =В2*В4, которая находится в ячейке С4 обычным способом с помощью маркера автозаполнения, то результат будет неверным, потому что адресация будет меняться относительно исходной формулы. А нам нужно, чтобы адрес ячейки B2 не изменялся. Для того чтобы создать абсолютную ссылку на ячейку, достаточно перед именем строки и столбца поставит знак $. (рис.8)

http://sergun-2.narod.ru/metod_ex.files/image024.gifhttp://sergun-2.narod.ru/metod_ex.files/image026.jpg

Рисунок 7

http://sergun-2.narod.ru/metod_ex.files/image027.gifhttp://sergun-2.narod.ru/metod_ex.files/image028.gifhttp://sergun-2.narod.ru/metod_ex.files/image029.gifhttp://sergun-2.narod.ru/metod_ex.files/image031.jpg

Рисунок 8

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

*   Абсолютная ссылка на строку. В этом случае знак $ ставится только перед номером строки. Например, С$4 – это абсолютная ссылка на четвёртую строку.

*   Абсолютная ссылка на столбец. В этом случае знак $ ставится только перед именем столбца. Например, $С4 – это абсолютная ссылка на столбец С.

В качестве примера можно привести расчёт таблицы умножения (рис. 9). В ячейку В2 следует ввести формулу:   =$A2*B$1 и растянуть её за маркер заполнения на диапазон B2:J10.

http://sergun-2.narod.ru/metod_ex.files/image032.gifhttp://sergun-2.narod.ru/metod_ex.files/image034.jpg

Рисунок 9

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

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

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

7 команды Правка / Копировать и Правка / Вставить (копирование) или Правка / Вырезать и Правка / Вставить (перемещение).

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

http://sergun-2.narod.ru/metod_ex.files/image035.gif


http://sergun-2.narod.ru/metod_ex.files/image037.jpg

Рисунок 10

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

Копирование и перемещение данных через буфер обмена нужно выполнять следующим образом: необходимо выделить копируемый (вырезаемый) диапазон и дать команду на его помещение в буфер обмена: Правка / Копировать или Правка / Вырезать. Место вставки определяется путём указания ячейки, соответствующей верхнему левому углу диапазона, помещённого в буфер обмена. Вставка выполняется командой Правка / Вставить.

1.11  Автоматизация процесса ввода

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

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

Чтобы точно сформулировать условия заполнения ячеек следует выполнить команду Правка / Заполнить / Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке Ок Excel автоматически заполняет ячейки в соответствии с заданными критериями.

http://sergun-2.narod.ru/metod_ex.files/image039.jpg

Рисунок 11

1.12  Использование стандартных функций

Функции в Excel используются для выполнения стандартных вычислений. При вводе формулы обращение к встроенной функции можно осуществить с клавиатуры, либо вызвать Мастер функций. Для этого в Excel есть соответствующая кнопка на стандартной панели: Вставка функции, а также пункт меню Вставка / Функция. В появившемся окне Мастер функций - шаг 1 из 2 пользователь может обратиться к более 400 встроенным функциям, которые объединены в девять групп:

*   Финансовые

*   Дата и время

*   Математические

*   Статистические

*   Ссылки и массивы

*   Работа с базой данных

*   Текстовые

*   Логические

*   Проверка свойств и значений

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

http://sergun-2.narod.ru/metod_ex.files/image041.jpg

Рисунок 12

В таблице 5 приведён неполный список категории Математические.

 Таблица 5

Функция

Назначение

ABS

Возвращает модуль (абсолютную величину) числа

ACOS

Возвращает арккосинус числа. Если нужно преобразовать результат из радиан в градусы, то умножьте его на 180/ПИ()

ASIN

Возвращает арксинус числа

ATAN

Возвращает арктангенс числа

COS

Возвращает косинус заданного угла

EXP

Возвращает число е, возведённое в указанную степень

LN

Возвращает натуральный логарифм числа

LOG

Возвращает логарифм числа по заданному основанию. Если основание опущено, то оно полагается равным 10

LOG10

Возвращает десятичный логарифм числа

SIN

Возвращает синус заданного угла

TAN

Возвращает тангенс заданного угла

КОРЕНЬ

Возвращает положительное значение квадратного корня из неотрицательного числа

МОБР

Возвращает обратную матрицу

МОПРЕД

Возвращает определитель матрицы

МУМНОЖ

Возвращает произведение матриц

НЕЧЁТ

Возвращает число, округлённое до ближайшего нечётного целого

ОСТАТ

Возвращает остаток от деления

ОТБР

Усекает число до целого, отбрасывая дробную часть числа так, что остаётся целое число

ПИ()

Возвращает значение числа p. У этой функции пустой список аргументов

ПРОИЗВЕД

Возвращает произведение чисел, заданных в качестве аргументов

РАДИАНЫ

Преобразует градусы в радианы

СТЕПЕНЬ

Возвращает результат возведения числа в степень

СУММ

Возвращает сумму всех чисел, входящих в список аргументов

ФАКТР

Возвращает факториал числа

ЧЁТН

Возвращает число, округлённое до ближайшего чётного целого

В таблице 6 приведён неполный список категории Дата и время.

Таблица 6

Функция

Назначение

ГОД

Возвращает год, соответствующий указанной дате

ДАТА

Возвращает порядковый номер указанной даты

ДЕНЬ

Возвращает день месяца указанной даты

ДЕНЬНЕД

Возвращает номер дня недели указанной даты

ДНЕЙ360

Возвращает количество дней между двумя датами на основе 360-дневного года

МЕСЯЦ

Возвращает месяц, соответствующий указанной дате

МИНУТЫ

Возвращает минуты, соответствующие указанной дате

СЕГОДНЯ

Возвращает текущую дату в числовом формате

СЕКУНДЫ

Возвращает секунды, соответствующие указанной дате

ТДАТА

Возвращает текущую дату и время в числовом формате

ЧАС

Возвращает час, соответствующий указанной дате

В таблице 7 приведён список категории Логические функции.

Таблица 7

Функция

Назначение

ЕСЛИ

Возвращает первое значение, если логическое выражение при вычислении даёт значение ИСТИНА, и второе значение, если ЛОЖЬ

И

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ

ИЛИ

Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ

ИСТИНА

Возвращает логическое значение ИСТИНА

ЛОЖЬ

Возвращает логическое значение ЛОЖЬ

НЕ

Меняет на противоположное логическое значение аргумента

2.Применение электронных таблиц для экономических и бухгалтерских расчётов

2.1  Построение диаграмм и графиков

В программе Excel термин диаграмма используется для обозначения всех видов графического представления данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. Для построения диаграммы используют Мастер диаграмм, который запускается при щелчке на кнопке Мастер диаграмм на стандартной панели инструментов. Удобно заранее выделить область данных, на основе которых будет строиться диаграмма, но задать эту информацию можно и в ходе работы мастера. На первом шаге работы мастера выбирают форму диаграммы. Доступные формы перечислены в списке Тип на вкладке Стандартные. На вкладке Вид имеется несколько вариантов представления данных. На вкладке Нестандартные  отображается набор полностью сформированных типов диаграмм с готовым форматированием.

http://sergun-2.narod.ru/metod_ex.files/image043.jpg


http://sergun-2.narod.ru/metod_Ex.htm