Инструментальные программные системы

 

Инструментальные программные системы - это интегрированные среды, содержащие комплекс программных средств, которые используются в ходе разработки и отладки других прикладных или системных программ. К инструментальным программам относят следующие:

· текстовые экранные редакторы для создания и редактирования текстов программ;

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

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

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

· библиотеки стандартных программ и функций;

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

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

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

Интерпретатор (interpreter - переводчик) - программа для непосредственного перевода и выполнения исходной программы строка за строкой. Программа, которая обрабатывается интерпретатором переводиться на машинный язык при каждом запуске исходной программы. Откомпилированные программы работают быстрее, но интерпретируемые проще исправлять и изменять. Для одного языка может иногда использоваться компилятор и интерпретатор.

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

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

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

· Версия RC (RC - Release Candidate) – кандидат на окончательную версию программы, прошедшую все предыдущие стадии, в ней исправлены основные ошибки;

· Релиз - окончательная версия программы.

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

 

4.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 заметно упростил работу с электронными таблицами и сделал ее удобной и понятной даже для неподготовленных пользователей.

 

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

 

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

 

 

Рис. 4.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 (рис.4.2).

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

 

 

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

 

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

· книга;

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

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

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

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

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

4.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 и т.д. Используя автозаполнение, можно вводить дни недели, месяцы и т.п. Для автоматизации заполнения столбца или строки также можно использовать команду Правка > Заполнить> Прогрессия (Рис.4.3).

 

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

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

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

 

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

 

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

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

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

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

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

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

 

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

 

 

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

 

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

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

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

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

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

 

 

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

 

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

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

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

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

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

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

 

 

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

 

4.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).

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

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

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

 

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

 

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

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

 

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

 

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

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

 

 

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

 

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

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

 

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

 

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

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

Суммирование. Для простейшего суммирования используют функцию СУММ, синтаксис которой СУММ(А), где: А - список от 1 до 30 аргументов. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула СУММ(В2:В5), указанная в ячейке В6 (рис. 4.13), соответствует формуле =В2+В3+В4+В5.

 

Рис. 4.13. Суммирование с использованием функции СУММ

 

Для выполнения выборочного суммирования столбцов чисел, например требуется сложить только числа в столбцах В и D, а столбец C исключить формула сложения будет иметь вид: =СУММ(В2:В5; D2:D5). Для выполнения суммирования ячеек диапазона, удовлетворяющих заданным условиям, следует использовать функцию СУММЕСЛИ, имеющую следующий синтаксис:

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования), где:

· диапазон - диапазон адресов вычисляемых ячеек;

· критерий - критерий в виде числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 24, ">22";

· диапазон_суммирования - фактические ячейки для суммирования.

Ячейки в области диапазон_суммирования суммируются, если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. Если диапазон_суммирования опущен, то суммируются ячейки в аргументе «диапазон». Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа появлений текстовой строки или числа в пределах диапазона ячеек, используйте функцию СЧЁТЕСЛИ. Для получения формулы, возвращающей в зависимости от выполнения условия одно из двух значений, например вознаграждение по указанному объему продаж, используйте функцию ЕСЛИ. На рис. 4.14 представлен пример суммирования ставок комиссионных закупаемого оборудования, значения которых превышают 12000.

 

 

Рис. 4.14.Суммирование с использованием функции СУММЕСЛИ

 

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

Возведение в степень. Для возведения в степень используют функцию СТЕПЕНЬ, синтаксис которой СТЕПЕНЬ(число; степень), где:

· число - основание, которое может быть любым вещественным числом;

· степень - показатель степени, в которую возводится основание.

Вместо функции СТЕПЕНЬ для возведения в степень можно также использовать оператор ^, например 7^2. Отрицательные числа можно возводить в степень, значение которой является целым числом, других ограничений нет.

 

 

Рис. 4.15. Выборочное суммирование с использованием функции

"СУММЕСЛИ"

Тригонометрические функции. В Excel можно выполнять как прямые, так и обратные тригонометрические функции. Синтаксис для прямых тригонометрических функций имеет единый вид и, например, для функции SIN синтаксис следующий: SIN(А), где: А - угол в радианах.

Синтаксис для всех обратных тригонометрических функций также имеет одинаковый вид и для функции АSIN синтаксис - АSIN(А), где:

А - число, равное синусу определяемого угла.

Следует обратить внимание, что все тригонометрические вычисления выполняются для углов, представленных в радианах. Для перевода в градусы необходимо использовать функции преобразования или самостоятельно переводить значения, используя функцию ПИ(). Функция ПИ() вставляет значение числа ПИ, функция не имеет аргументов, но скобки необходимо указывать. Например, для вычисления значения синуса угла в градусах, нужно его умножить на ПИ()/180. На рис.4.16. приведен пример вычисления функции SIN(A) для аргументов, заданных в градусах (от 0 до 90 градусов).

Расчет средних значений. В простом случае для расчета среднего арифметического значения используют функцию СРЗНАЧ, имеющую следующий синтаксис:

СРЗНАЧ(А), где:

А - список от 1 до 30 элементов, среднее значение которых требуется найти. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой.

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

МАКС(А), где:

А - список от 1 до 30 аргументов, среди которых требуется найти наибольшее значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Функция МИН имеет такой же синтаксис, что и функция МАКС. Функции МАКС и МИН определяют только крайние значения, но не показывают, в какой ячейке эти значения находятся.

 

 

Рис. 4.16. Вычисление функции SIN(А)

На рис. 4.17 показан пример определения максимального значения стоимости тура.

 

Рис. 4.17. Нахождение максимальной стоимости тура

 

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

СЧЕТ(А), где:

А - список аргументов от 1 до 30, среди которых требуется определить количество ячеек, содержащих числовые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. В таблице на рис.4.х приведен пример определение в таблице числовых значений в ячейках А2:А6.

 

Рис.4.18. Расчет количества ячеек, содержащих числа, с использованием функции "СЧЕТ"

 

Для определения количества ячеек, содержащих числовые, текстовые, логические значения, следует использовать функцию СЧЕТЗ, синтаксис которой СЧЕТЗ(А), где:

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

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

· #ЗНАЧ! - используется недопустимый тип аргумента;

· #ДЕЛ/0! - в формуле выполняется деление на ноль;

· #ИМЯ? - Excel не может определить используемое в формуле имя;

· #ССЫЛКА! - используется недопустимая ссылка на ячейку;

· #Н/Д - неопределенные данные, при некорректном определении аргументов функции;

· #ПУСТО! - задано пересечение двух областей, не имеющих общих ячеек.

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

 

4.3.4. Копирование данных, адресация ячеек

 

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

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

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

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

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

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