X внедрить шрифты TrueType

¨ Далее >

¤ включить PowerPoint Viewer

¨ Далее >

¨ Готово

Использовать упакованную презентацию очень просто. Запустите ее и укажите папку, в которую нужно распаковать презентацию.

Глава 2 Электронные таблицы Excel

2.1. Назначение электронных таблиц

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

· выполнение однотипных расчетов над большими наборами данных;

· автоматизация итоговых вычислений;

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

· обработка результатов экспериментов;

· проведение поиска оптимальных значений параметров;

· подготовка табличных документов;

· построение диаграмм и графиков по имеющимся данным и другие.

Идею электронных таблиц впервые сформулировал американский ученый Р. Маттессич, опубликовав в 1961 году исследование под названием Budgeting Models and System Simulation. Общепризнанным родоначальником электронных таблиц как отдельного класса программного обеспечения является Д. Бриклин, разработавший совместно с Б.Фрэнкстоном в 1979 г известную программу VisiCalc. Данный табличный процессор для компьютеров фирмы Apple стал эффективной программой, превратившей персональный компьютер в мощный инструмент для решения экономических задач. Впоследствии на рынке появились многочисленные программы данного класса : SuperCalc, Microsoft MultiPlan, Lotus 1-2-3, Microsoft Excel и другие.

Сегодня одной из самых популярных подобных программ в мире является Microsoft Excel - программа для работы с электронными таблицами, созданная корпорацией Microsoft. Первая версия Excel для Windows была выпущена в 1987 года, текущая версия для Windows - Excel 2007. Начиная с 1993 года, в состав Excel входит язык программирования Visual Basic для приложений (VBA), позволяющий автоматизировать задачи Excel. VBA является мощным дополнением к приложению, которое в более поздних версиях стало включать полную интегрированную среду разработки. Можно создать VBA-код, позволяя автоматизировать простые задачи, также VBA позволяет создавать формы для общения с пользователем. Более поздние версии позволяют использовать основы объектно-ориентированного программирования.

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

2.2. Интерфейс пользователя в Excel

После запуска Excel на экране появляется окно программы, в котором имеется группа основных областей, обеспечивающих пользовательский интерфейс (Рис. 2.1).

Рис. 2.1. Пользовательский интерфейс Excel

Рассмотрим кратко пользовательский оконный интерфейс:

· строка заголовка окна Excel – книга 1;

· строка главного меню содержит имена основных режимов работы в электронной таблице;

· строка панелей инструментов с кнопками команд;

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

Х - кнопка отмены ввода до его фиксации, тот же результат получается нажатием клавиши <Esc>;

V - кнопка фиксации ввода, тот же результат получается нажатием клавиши <Enter>;

f_x - кнопка “изменить формулу”.

· рабочая область окна;

· строка переходов по листам рабочей книги;

· горизонтальная и вертикальная строки прокрутки окна;

· строка состояния.

Рассмотрим понятия Книга, Лист, Ячейка и Диапазон ячеек в Excel.

Идея Книги состоит в том, что несколько электронных листов объединяют в группу, называемую книгой, и хранят в виде одного файла, имеющего расширение .xls . По умолчанию Excel присваивает книгам имена: Книга1, Книга2 и т.д. Пользователь может присваивать книгам другие имена, также любые имена можно присваивать страницам книги. При запуске Excel открывается первая книга с именем Книга1. Если пользователь создает новую книгу в то время, когда открыта другая, то окно новой книги выводится поверх окна предыдущей и новой книге будет присвоено имя Книга2 и т.д.

Лист - это электронная таблица, состоящая из строк и столбцов, на пересечении которых находятся ячейки. Каждая ячейка имеет адрес, образованный из имени столбца и номера. Имена строк – их порядковые номера от 1 до 16535, именами столбцов являются буквы латинского алфавита. Таким образом, размер одной таблицы составляет 256 столбцов * 16535 строк. Количество листов в книге пользователь может изменять по своему желанию, но так как каждый пустой лист занимает на диске место для экономии лучше удалять из книг пустые листы и добавлять новые по мере необходимости. Имена листов, составляющих книгу, видны в нижней строке окна книги, где находятся ярлычки листов. Их можно переименовать, чтобы проще с ними работать.

Ячейка таблицы, диапазон ячеек. Текущей или активной ячейкой называется ячейка, в которой находится курсор. При этом адрес текущей ячейки и ее содержимое выводятся в строке ввода формул. Перемещение курсора осуществляется клавишами управления курсором. Диапазоном называется группа ячеек, образующих столбец, строку или прямоугольную область и состоящую из нескольких строк и столбцов. Диапазон ячеек обозначается с помощью адреса ячейки в левом верхнем углу прямоугольника и адреса ячейки в правом нижнем углу прямоугольника. Например, обозначение D4:E7 описывает диапазон ячеек, находящихся на пересечении строк с номерами 4, 5, 6, 7 и столбцов D, E (рис.2.2).

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

Рис.2.2. Диапазон ячеек D4:E7

В Excel можно работать с 4-мя основными типами объектов (документов):

· книга;

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

· диаграмма –позволяетграфически отображать результаты ;

· макрос – это объединенная в одну большую команду последовательность команд, которые приходится очень часто выполнять пользователю.

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

2.3. Основы работы в Excel

2.3.1. Ввод данных в ячейки электронной таблицы

В ячейки можно вводить два типа данных: константы и формулы. Константа - это постоянное (не вычисляемое) значение. Например, число 120 и текст «Отдел кадров» являются константами. Выражение и результат вычисления по формуле константами не являются. Константы делятся на следующие основные категории: числа, текст, даты и время, логические значения и ошибочные значения.

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

· общий формат – обеспечивает запись числовых данных в том виде, как данные были введены ;

· числовой формат с фиксированным числом десятичных знаков после запятой, например формат с 3-мя десятичными знаками 0,123 (в качестве разделителя целой и дробной частей числа используется «,»;

· процентный формат - использует умножение значения ячейки на 100 и отображения результата с символом процента., например число 0.153 в процентном формате имеет вид 15.3%;

· экспоненциальный формат, в котором количество знаков после запятой равно двум, например число 1234567890 отобразится как: 1,23E+9, то есть 1,23*10^-9. Имеется возможность определить используемое количество десятичных знаков.

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

Если возникает необходимость ввода числа как текстового значения, то перед числом надо поставить знак апострофа, либо заключить число в кавычки - “2937” Числовое или текстовое значение в ячейке определяется видом выравнивания: текст выравнивается по левому краю ячейки, а числа - по правому.

Дата - может быть представлена в разных форматах, с ней можно выполнять различные арифметические и логические операции. Наиболее часто используются следующие форматы дат: дд-ммм-гг (04-янв.-95), ммм-дд-гг (янв.-04-95), дд-ммм (04-янв.) и т.п..

Автозаполнение. Процесс набора данных одного типа в смежные ячейки таблицы можно автоматизировать, используя режим автозаполнения, основанный на копировании данных. Для заполнения данных в ячейки столбца В1:В20, например от 1 до 20 не нужно вводить последовательно каждое из них, а достаточно набрать два первых числа и затем, выделив их, копировать в остальные ячейки столбца. Программа автоматически распознает заданный закон арифметической прогрессии в исходных данных и загрузит остальные числа в ячейки столбца В. Аналогично автозаполнение можно использовать и для заполнения строки данными, а также и для ввода дат, так как даты представляют собой числа, представленные в определенном формате. Реализовать закон арифметической прогрессии можно с различным шагом изменения вводимых данных. Например, если в ячейках В1 и В2 введены числа 5 и 10, то после использования автозаполнения в ячейках В3:В10 будут введены числа 15, 20,25 и т.д. Используя автозаполнение, можно вводить дни недели, месяцы и т.п. Для автоматизации заполнения столбца или строки также можно использовать команду Правка >Заполнить> Прогрессия (Рис.2.3).

Рис.2.3. Окно Прогрессия

Рис. 2.3. иллюстрирует использование команды Заполнить числа в строку, используя закон арифметической прогрессии, начиная с 3 до 15 с заданным шагом 3 . Из приведенного окна Прогрессия наглядно виден смысл опций, доступных пользователю. При выборе в качестве Тип Даты становится активным опция Единицы,котораяпозволяет задать требуемый вид последовательности: день, рабочий день, месяц, год.

Понятно, что ручной ввод данных в таблицы Excel является трудоемкой работой для таблиц большого объема. Поэтому для этого можно использовать системы управления базами данных (СУБД), такие как Access, SQL Server. Для получения внешних данных можно использовать программу MS Query, позволяющую переносить данные из внешних источников в программы Microsoft Office. Используя программу Query для извлечения данных из общей базы данных и файлов, нет необходимости в повторном вводе данных для анализа в Excel. Также можно автоматически обновить отчеты и документы Excel данными из исходной базы данных, при их изменении. Извлечение данных из базы данных производится путем создания запроса данных, хранящихся во внешней базе данных. Использование технологии Query для извлечения данных представлено на рис 2.4.

Рис.2.4. Технология ввода данных

Перенос внешних данных в Excel с помощью MS Query состоит из трех шагов:

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

· затем используется мастер запросов для выбора данных ;

· данные передаются в Excel, где они могут быть отформатированы, обобщены и по ним могут быть построены отчеты.

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

2.3.2. Выравнивание содержимого ячеек

Для выравнивания содержимого ячеек используется меню Формат> Формат ячеек>Выравнивание. Вкладка "Выравнивание" окна "Формат ячеек" определяет расположение чисел и текста в ячейках, создание многострочных надписей в ячейке, изменение ориентации текста и т.п. (рис.2.5).

Рис.2.5. Использование вкладки Выравнивание

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

Выравнивание по вертикали и ориентация текста. Excel поддерживает четыре формата выравнивания текста по вертикали: по верхнему краю, по нижнему краю, по высоте и по центру.

Поле "Ориентация" позволяет размещать содержимое ячеек вертикально сверху вниз или наклонно под углом по часовой или против часовой стрелки до 90 градусов. Высота строки при вертикальной ориентации автоматически настраивается в том случае, если она не установлена предварительно вручную. Флажок "Автоподбор ширины" уменьшает размер символов в выделенной ячейке таким образом, чтобы ее содержимое размещалось в ячейке столбца полностью. Это целесообразно использовать при работе с таблицей, когда настройка ширины ячейки столбца по наиболее длинному значению влияет на другие данные.

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

щелчком мыши по стрелке рядом с этой кнопкой.

Рис. 2.6.Границы и заливка ячеек

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

· ввод данных в таблицу;

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

· выбрать команду "Автоформат" в меню "Формат";

· в окне «Автоформат» нажать кнопку «Параметры», для отображения области «Изменить» (рис.2.7).

· выбрать требуемый формат и нажать кнопку «Ок».

Рис.2.7.Диалоговое окно Автоформат

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

Формулапредставляет собой выражение, по которому выполняется вычисление на листе Excel, формула начинается с символа равенства «=». Примеры формул: =5+2*3 ; =A1+B1*3 ; =(A1 > 0) OR (C3 > 1), где OR-логическая функцияИЛИ.Набор формулы осуществляется в строке ввода или в текущей ячейке и после нажатия Enter в строке ввода отображается введенная формула, а в текущей ячейке – результат вычисления по данной формуле. Таким образом, в общем случае формула – это выражение, состоящее из чисел, ссылок на ячейки (адресов ячеек), функций, арифметических и логических операций. Различают арифметические и логические формулы, результат вычисления арифметической формулы является числом, а логические формулы принимают только два значения: "Истина" или "Ложь".

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

Арифметические операторы служат для выполнения арифметических операций: +, -, *( умножение), /(деление), ^ (возведение в степень).

Операторы сравнения применятся для сравнения двух значений, а результат сравнения - логическое значение: «Истина» или «Ложь». Примерами данных операторов являются: =, >, <, >=,<=,<>(не равно).

Текстовый оператор конкатенации (амперсанд &) используется для объединения текстовых строк в одну, например ("Северный"&"ветер").

Логические операторы используются, когда математическая формула содержит условие, например: y = ax+b, если x =>0 или y=cx2, если х<0.

Логические формулы кроме операторов сравнения могут содержать специальные логические операторы: NOT - "НЕ", AND - "И", OR - "ИЛИ".

Функции. Под функцией понимаютзаранее определенную стандартную формулу, которая выполняет вычисления над заданными аргументами в указанном порядке и возвращает результат. Функции позволяют выполнять как простые, так и сложные вычисления. Например функция =СУММ(А1:А5) соответствует записи =А1+А2+А3+А4+А5 и состоит из имени и аргументов: СУММ - это имя функции, а А1:А5 – аргументы, заключаемые в круглые скобки. Функция ОКРУГЛ(В9;3) округляет число в ячейке В9, а параметр 3 определяет количество цифр округляемого числа. В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в формуле ЕСЛИ(СРЗНАЧ(А3:А9)>36;СУММ(В3:В9);0) функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких чисел с числом 36.

В Excel имеются следующие основные виды функций:

· математические (тригонометрические, логарифмические и другие);

· статистические (среднее значение, стандартное отклонение и т.п.);

· текстовые (вычисление длины строки, преобразование заглавных букв в строчные и другие);

· логические (для вычисления логических выражений);

· финансовые;

· функции даты и времени и др.

Все функции имеют одинаковый формат записи, включающий имя функции и аргументы, например, формула для суммирования чисел из ячеек блока B5:E5имеет вид: =СУММ(B5:E5).

Выбор подходящей функции осуществляется в Excel с помощью специальной программы Мастер функций. Для наглядности анализа результатов вычислений в таблице имеется возможность отображать связи между аргументами и результатами вычислений. Например, если ячейка В5 содержит формулу =А2+А5, то эти две ячейки являются влияющими на результат формулы в ячейке В5. Excel позволяет наглядно отображать подобные связи при использовании панели инструментов «Зависимости» в меню Вид (рис.4.8).

Создание формул с использованием Мастера функций.Для создания формул с использованием функций используют Мастер функций, вызываемый

Рис. 2.8. Использование функции влияния ячеек

нажатием на кнопкуf_x в строке формул. В диалоговом окне Мастер функций- шаг 1 из 2 в раскрывающемся списке Категория необходимо выбрать категорию функции, затем в списке функций нужно выбрать функцию и нажать кнопку ОК( рис. 2.9).

Рис.2.9. Выбор категории и типа функции

Например, для выполнения умножения группы чисел в ячейках (В2:Е2) необходимо выбрать категорию Математические и функцию ПРОИЗВЕД. После выбора функции появляется диалоговое окно Аргументы функции (рис. 2.10).

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

Рис.2.10. Определение аргументов функции ПРОИЗВЕД

выделение диапазона ячеек мышью, числа и логические выражения в качестве аргументов, как правило, вводят с клавиатуры. В нашем примере диапазон адресов чисел (В2:Е2), после определения аргументов функции следует нажать кнопку ОК. Следует отметить, что некоторые функции могут не иметь аргументов.

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

Рис.2.11.Меню функций кнопки Автосумма

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

Для определения максимального значения в ячейках В2:Е6 выбираем ячейку G2, в которой нужно сформировать результат, указываем диапазон ячеек В2:Е2 и получаем результат в G2 после нажатия Enter (Рис.2.12).

Рис.2.12. Определение максимального числа

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