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

Содержание

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

Г.В. Лейбниц

Программа для работы с электронными таблицами Microsoft Excel появилась в 1985 году и  до настоящего времени является наиболее широкоиспользуемой в мире для работы с электронными таблицами (уже существует новая версия этой программы — Excel 2000). Почти на каждом компьютере, эксплуатируемом в странах СНГ, установлена одна из версий данного программного продукта. Производитель этого продукта, фирма «Microsoft», стремится к совместимости последних версий этой программы с более ранними — для тех, кто знаком с программой Excel 5.0, не составит труда приступить к работе с более поздними версиями. Поэтому при написании данной книги за основу был взят Excel 5.0. Данный программный продукт обладает мощными встроенными возможностями для работы с различного типа данными, а также обеспечивает возможность написания собственных программ для обработки этих данных на языке высокого уровня Visual Basic for Application (VBA). Кроме того, в Excel встроена мощная справочная система.

1.1. Знания и навыки, которые необходимы для начала работы с программой Excel

Работа с данной программой пользователю необходимо иметь базовые знания и навыки по эксплуатации операционной системы Windows (3.11, 95, 98, 2000). Если вы уже работали с программой Microsoft Word, то вам легче будет освоить Excel, так как интерфейс пользователя в этих программах реализован с использованием единых подходов.

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

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

Примечание:

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

Терминология и обозначения

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

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

Вводимая информация — текст, числа, специальные знаки, вводимые пользователем с клавиатуры, выделенные курсивом. Это позволяет отличить ее от текста описания и комментариев. Кроме того, курсивом в тексте выделяются определения терминов и просто важные места.

[Клавиша] — обозначает отдельную клавишу (в том числе с литерой), если, например, в меню выбирается команда.

[Клавиша1 + Клавиша2] — обозначает комбинацию клавиш, то есть [Клавиша1] нажимается и удерживается нажатой, затем нажимается и отпускается [Клавиша2].

Указатель (ячейки) — рамка, заключающая в себе поле ячейки. Одним щелчком мыши можно установить (позиционировать) указатель ячейки на поле, на котором в данный момент расположен курсор мыши. Пользователь, предпочитающий работать с кла­виатурой, может перемещать указатель поля посредством клавиш управления движением курсора. Указатель поля иногда называют курсором. Текущая ячейка, это ячейка, которая выделена указателем ячейки на момент выполнения какой-либо операции.

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

Щелчок мышью (иногда просто щелчок). Это означает, что необходимо нажать и отпустить левую кнопку мыши. В тексте часто встречаются указания: «щелкнуть мышью на…», «зафиксировать указатель мыши на…» или «щелкните по…».

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

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

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

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

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

При запуске программного средства Microsoft Excel на дисплее появляется окно данной программы (см. рис. 1.1) и пустая рабочая электронная книга, состоящая из 16 листов (количество их можно увеличить до 256). Листы бывают 5-ти типов:

• рабочие листы электронной таблицы (Worksheet);
• листы диаграмм (Chart Sheet);
• листы модулей встроенного языка программирования Visual Basic (module);
• листы диалоговых окон (Dialog sheet);
• листы макросов Microsoft Excel 4 (Macro sheet).

После того как программа запущена, изображение, появившееся на экране, можно условно разделить на 5 областей: 1) окно рабочей книги; 2) строка (линейка) главного меню; 3) область панелей инструментов (могут присутствовать несколько панелей инструментов), 4) строка формул; 5) строка состояния. Рассмотрим каждую из этих областей в отдельности.

1.2.1. Окно рабочей книги

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

Рабочее пространство представлено в виде таблицы, состоящей из листов и строк. Каждому листу присвоено название в виде букв или буквосочетаний A, B … Z, AA, AB … AZ, BA, BB, … . Каждой строке присвоен номер от 1 до 16384. На пересечении строк и столбцов расположены ячейки электронной таблицы. Адресация к ячейкам осуществляется путем указания заголовка столбца и строки. Например, адрес ячейки, находящейся в столбце М и строке 15 будет выглядеть так: М15. Адрес текущей ячейки выводится в поле имени, которое располагается слева от строки формул.

1.2.2. Кнопки и панели инструментов

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

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

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

1.2.4. Строка состояния

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

Примечание:

• Строка состояния отображается на листе по умолчанию. Если же она вам не нужна, то щелкните по пункту Строка состояния (Status Bar) в меню Вид (View) и она исчезнет. Если же вы хотите восстановить ее — повторите команду еще раз.

1.3. Ввод и использование формул в электронных таблицах

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

1.3.1. Как создавать формулы

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

Для примера выберем пустую ячейку и напечатаем в ней «=2+3». После того, как вы нажмете кнопку Enter или щелкните мышью по кнопке ОК строки формул, в текущей ячейке появится результат — число «5». Причем в строке формул будет отображаться выражение формулы, а в ячейке — результат. Следует отметить, что в электронных таблицах имеется режим отображения при котором в ячейках электронной таблицы отображаются не результаты, а формулы. Для переключения в этот режим выберите в меню Сервис пункт Параметры…, а на диалоговой панели Параметры выберите ярлык закладки Вид и установите флажок напротив надписи Формулы, щелкнув по соответствующему полю мышью. Для возврата к режиму нормального просмотра уберите флажок напротив надписи Формулы.

Далее, в табл. 1.1 перечислены знаки, используемые в арифметических, текстовых и логических операциях.

Таблица 1.1

Знаки операций, используемые в формулах Microsoft Excel

Вычисления в формуле выполняются в определенном порядке. Последовательность (приоритетность) выполнения операций в формулах электронных таблиц Excel приведена в табл. 1.2. Операции, приведенные в ранее в табл. 1.1., в табл. 1.2. перечислены по старшинству и, операции, расположенные в таблице выше, имеют приоритет над операциями, расположенными ниже.

Таблица 1.2

Приоритет операций Excel

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

Примечание:

• Если у вас нет уверенности, что вычисления будут выполнятся в нужном порядке, используйте скобки (даже если это излишняя предосторожность, она не помешает).

1.3.2. Использование скобок

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

1.3.3. Адресация ячеек и ее использование в формулах

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

Простейший пример того, как работают формулы:

1. Введем в ячейку А4 число 5.
2. Введем в ячейку А5 число 7.
3. Введем в ячейку В2 формулу: =А4*А5–5.
4. В ячейке В2 появится результат: 30.

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

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

1.3.4. Как вводить ссылки при помощи мыши

Рассмотрим ввод ссылок при помощи мыши на примере. Введем уже знакомую нам формулу: =А4*А5–5 в ячейку В2. Для этого:

1. Выделите ячейку В2 и напечатайте знак равенства (=).
2. Щелкните по ячейке А4 и напечатайте знак умножения (*).
3. Щелкните по ячейке А5 и введите знак минус (–).
4. Введите с клавиатуры цифру 5 и нажмите клавишу Enter или щелкните мышью по кнопке ОК, находящейся в строке формул.

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

Примечание:

• Если при вводе ссылки вы оказались далеко от активной ячейки, то можете к ней вернуться, нажав комбинацию клавиш Ctrl+Backspace.

1.3.5. Виды ссылок: относительные, абсолютные и смешанные

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

Тип имеющейся ссылки можно быстро изменять при помощи клавиши F4. Рассмотрим это на примере:

1. Выделите ячейку А3 и напечатайте: =В1+В2.
2. Нажмите и отпустите клавишу F4. В результате изменится тип ближайшей к текстовому курсору ссылки. Например, если курсор находится ближе к ссылке В2, то формула примет вид: =В1+$В$2.
3. Еще раз нажмите F4. Теперь ссылка станет смешанной и формула примет вид: =В1+В$2 (относительный адрес столбца и абсолютный адрес строки).
4. Еще раз нажмите F4. Относительная ссылка преобразуется, формула примет вид: =В1+$В2 (абсолютный адрес столбца и относительный адрес строки).
5. Нажмите F4 в последний раз, чтобы вернуться к исходному относитель­ному типу адресации.

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

1.3.6. Ссылки на другие рабочие листы внутри рабочей книги

Ссылки на ячейки других рабочих листов активной рабочей книги вводятся аналогично ссылкам в пределах текущего рабочего листа. Рассмотрим это на примере. Введем ссылку на ячейку А5 из листа Лист2* (Sheet2) в ячейку В8, находящуюся на листе Лист1 (Sheet1), выполнив для этого следующие действия:

1. Сделайте активной ячейку В8 на листе Лист1 и напечатайте знак равенства (=).
2. Щелкните мышью по ярлычку Лист2 в окне рабочей книги Книга1 (Book1).
3. Щелкните по ячейке А5 и нажмите клавишу Enter или щелкните мышью по кнопке ОК, находящейся в строке формул.

Это позволит вам вернуться на Лист1, в ячейке В8 которого будет находится содержимое ячейки А5 листа Лист2, а в строке формул появится формула: =Лист2!А5.

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

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

1.3.7. Ввод трехмерных ссылок

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

Изменения в трехмерных ссылках при перемещении и копировании ячеек

Приведенные далее примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула =СУММ(Лист2:Лист6!A2:A5), суммирующая содержимое ячеек с A2 по A5 с Листа2 по Лист6 включительно.

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

1.3.8. Ссылки на рабочие листы из других рабочих книг

Существует возможность ссылаться на рабочие листы из других рабочих книг. Эти ссылки вводятся аналогично ссылкам на разные рабочие листы внутри одной книги. Ссылки такого рода называют внешними. Покажем на примере как ввести в ячейку В5 листа Лист1 книги Книга1 ссылку на ячейку А8 листа Лист2 книги Книга2:

1. Создайте новую рабочую книгу — Книга2, щелкнув по кнопке Создать книгу (New Workbook) на панели инструментов Стандартная (Standard toolbar). Это первая кнопка на панели слева (с чистым листом).
2. Добейтесь, чтобы обе рабочие книги были одновременно видны на экране. Для этого щелкните по пункту Расположить (Arrange) из меню Окно (Window). Появится диалоговое окно Расположение окон. В этом диалоговом окне выберите каким образом должны располагаться окна на экране, установив напротив соответствующей надписи флажок. После чего нажмите клавишу Enter или щелкните по кнопке ОК данного диалогового окна — обе рабочие книги расположатся рядом в соответствии с вашим заданием.
3. Выделите ячейку В5 на листе Лист1 в книге Книга1 и введите с клавиатуры знак равенства (=).
4. Щелкните мышью в области окна книги Книга2 (лучше по заголовку), чтобы ее активизировать или выберите активную книгу щелкнув по соответствующему пункту меню Окно (Window).
5. Щелкните по ярлычку Лист2 в окне книги Книга2.
6. Щелкните по ячейке А8 и нажмите клавишу Enter или щелкните мышью по кнопке ОК, находящейся в строке формул.

Это позволит вам возвратится на лист Лист1 книги Книга1, в ячейке В5 появится содержимое ячейки А8 с листа Лист2 книги Книга2, а в строке формулы появится формула =[Книга2]Лист2!$A$8.

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

1.3.9. Замечания относительно синтаксиса ссылок

В рассмотренных нами примерах, мы использовали имена рабочих книг и названия рабочих листов, которые Excel присваивает по умолчанию. При сохранении рабочей книги вы можете задать ей новое имя. В этом случае все созданные вами до этого формулы преобразуются соответствующим образом. Например, если вы сохраняете книгу Книга2 как Продажи.XLS, то ссылка вида =[Книга2]Лист2!$A$8 автоматически будет заменена на новую =[Продажи.XLS]Лист2!$A$8.

Аналогично будут преобразовываться ссылки на другие листы книги если вы будете их переименовывать. Например, если переименовывать Лист2 в книге Продажи.XLS на Март, то ссылка также автоматически изменится: =[Продажи.XLS]Январь!$A$8.

В том случае, если требуемая рабочая книга закрыта, то в ссылке на нее появится пол­ный путь к ее файлу, например: =’C:\EXCEL\[Продажи.XLS]Январь’!$A$9.

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

1.3.10. Ссылки вида R1C1

По умолчанию в Microsoft Excel используются ссылки типа A1, в которых столбцы обозначаются буквами от A до Z (256 столбцов максимально), а строки — числами от 1 до 16384.

Другой стиль ссылок, альтернативный A1, называется R1C1. В виде R1C1 пронумерованы и строки, и столбцы, поэтому он удобен для отображения относительных ссылок. После буквы R указывается номер строки ячейки, после буквы C — номер столбца. Например, абсолютная ссылка R1C1 эквивалентна абсолютной ссылке $A$1 для формата A1. Если ячейка A1 является текущей, то относительная ссылка R[1]C[1] указывает на ячейку, расположенную на одну строку ниже и на один столбец правее, то есть — на ячейку B2. В табл. 1.3 приведены примеры ссылок R1C1.

Таблица 1.3

Примеры ссылок R1C1

Примечание:

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

Для перехода к ссылкам вида R1C1 следует выбрать пункт Параметры… (Options…) из меню Сервис (Tools), щелкнуть по ярлычку вкладки Основные (General) и установить флажок напротив надписи R1C1. После выполнения этой операции все ссылки в формулах будут преобразованы в этот вид адресации. Например, ячейка М10 преобразуется в R10C13, а последняя ячейка на рабочем листе будет иметь адрес R16384C256 вместо IV16384.

При использовании ссылок R1C1 относительные ссылки выводятся в относительном виде, то есть адрес содержит смещение по строкам и столбцам от активной (текущей) ячейки с формулой. Это удобно, если для вас более важно относительное, а не абсолютное положение ячеек.

1.3.11. Внесение изменений в формулы

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

Любые изменения до завершения редактирования отменяются щелчком по кнопке Отменить (Cancel), расположенной в строке формул, или нажатием клавиши Esc. После завершения редактирования, но до выполнения какой-либо команды, отменить изменения можно выбрав пункт Отменить правку (Undo) из меню Правка (Edit) или нажав комбинацию клавиш Ctrl+Z, или щелкнув по кнопке Отменить (Undo) на панели инструментов Стандартная.

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

1.3.12. Копирование формул

Очень часто для автоматизации выполнения рассчетов в электронных таблицах возникает необходимость скопировать формулы. В Microsoft Excel существует очень удобная возможность согласованного копирования формул.Выполнение даной процедуры рассмотрим на примере. Допустим, у нас имеюется несколько столбцов числовых данных (см рис. 1.5.). Необходимо вычислить их средние значения. Для этого введем в ячейку В8 формулу суммы по по столбцу Х1 =СУММ(B4:B7)/4. После этого необходимо выделить эчейку В8, позиционировать курсор мыши на правом нижнем углу выделенной ячейки и, после того, как курсор превратится в крестик нажать левую кнопку умыши и удерживая ее протянуть под столбцами Х2 и Х3. В результате формула, находящаяся в ячейке В8 будет согласовано скопирована и будут вычислены средние значения для столбцов Х2 и Х3 (см. рис. 1.6). Таким образом можно значительно ускорить ввод формул.

1.4. Краткий обзор встроенных функций

Для выполнения вычислений на листах книги и на листах макросов можно использовать стандартные функции Microsoft Excel. Величины, используемые для вычисления значений функций, называются аргументами. Величины, являющиеся результатом вычисления функций, называются возвращаемыми значениями. Последовательность, в которой должны располагаться аргументы функции, называется синтаксисом функции. Чтобы использовать функцию, ее нужно ввести как часть формулы в ячейку листа. Формула должна начинаться со знака равенства (=), за которым следует набор величин, операторов и функций. Если функция стоит в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле. Например, функция суммирования содержимого ячеек А1, А2, А3, А4, помещенная в виде формулы в ячейку А5, будет выглядеть так: =СУММ(А1:А4) или  =SUM(А1:А4) для англоязычного варианта программы.

Скобки используются для обозначения начала и конца списка аргументов. Скобки должны быть парными, пробелы перед или после них не допускаются. Внутри скобок должны располагаться аргументы, которые отделяются друг от друга точкой с запятой (;). Список аргументов может состоять из чисел, текста, логических величин, массивов, значений ошибок или ссылок. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций.

1.4.1. Мастер функций

Мастер функций упрощает процесс вставки функций в формулу в строке формул. Для того, чтобы запустить Мастер функций, выберите команду Функция в меню Вставка.  Или щелкните мышью по кнопке Мастера функций на панели инструментов Стандартная или по аналогичной кнопке в строке формул. Появляется диалоговая панель Мастера функций (см. рис. 1.7). Функции перечислены по категориям, таким как Финансовые, Математические и тригонометрические, Статистические и др. Когда функция выбирается из списка, автоматически появляется определение функции и ее аргументов, а также правильно расположенные скобки и точки с запятой в строке формул.

1.4.2. Соглашения

• В строке описания синтаксиса функции обязательные аргументы выделяются полужирным шрифтом, а необязательные набраны обычным шрифтом.
• В тексте определения функции и ее аргументов все аргументы набраны обычным (не полужирным) шрифтом, независимо от того, является аргумент обязательным или нет. Таким образом, чтобы определить, является аргумент обязательным или нет, следует обратиться к строке описания синтаксиса функции. Более подробные сведения об обязательных и необязательных аргументах содержатся в разделе «Синтаксис функций».
• В именах аргументов часто используется символ подчеркивания (между словами): например, число_символов — это имя аргумента.
• В именах макросов и ячеек не используются знаки препинания между словами: например, Всего Продано — это имя ячейки.
• Имена функций приводятся без знака равенства (=). Следует не забывать вводить знак равенства в начале каждой формулы, но не перед именами функций во вложенных формулах. Например, ПРОИЗВЕД — имя вложенной функции в формуле =СУММ(3;(ПРОИЗВЕД(2;4))).

1.4.3. Синтаксис функций

Описание каждой функции содержит строку описания синтаксиса. Например, строка описания синтаксиса для функции ЯЧЕЙКА выглядит так:

ЯЧЕЙКА(тип_инфо; ссылка).

В строке описания синтаксиса обязательные аргументы выделены полужирным шрифтом, необязательные аргументы не выделены. Если обязательные аргументы для функции не указаны, то ее невозможно ввести в ячейку. В предыдущем примере аргумент тип_инфо выделен полужирным шрифтом, и это значит, что он обязательный. Аргумент «ссылка» не выделен, значит этот аргумент необязательный, поэтому допустимы такие варианты как, например:

ЯЧЕЙКА(«format»; B12)

ЯЧЕЙКА(«format»)

ЯЧЕЙКА() — недопустимы, потому что тип_инфо является обязательным аргументом.

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

МАКС(число1;число2; …).

Любая из следующих формул является допустимой:

МАКС(26)

МАКС(26;31)

МАКС(26;31;29)

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

1.4.4. Имена аргументов

Многие имена аргументов намекают на то, какую информацию следует задавать в качестве фактического значения аргумента. Если в имени аргумента использованы сокращения “чис”, “ссыл” или “лог”, это означает, что аргумент должен быть числом, ссылкой или логическим значением соответственно. Например, у функции ОКРУГЛ(число; число_раз­рядов), как первый так и второй аргумент должны быть числами.

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

1.4.5. Использование аргументов

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

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

• Значение, являющееся числом, например: СУММ(1;10;100).

Формула, которая своим результатом имеет число, например:

СУММ(0,5+0,5;СРЗНАЧ(5;5);10^2).

В этом примере функция СРЗНАЧ является аргументом функции СУММ, то есть — вложенной функцией.

• Ссылка на ячейку или на интервал ячеек, который содержат числа или формулы, доставляющие числа, например: СУММ(A1;A2) или СУММ(A1:A5).

Второй пример эквивалентен формуле СУММ(A1;A2;A3;A4;A5). Преимущество использования интервала в том, что аргумент A1:A5 считается одним аргументом, в то время как А1, А2, А3, А4, А5 — пятью аргументами. Если требуется сложить более 30 чисел, то придется использовать интервалы, потому что функция не может иметь более 30 аргументов.

• Имя, которое ссылается на значение, формулу, ячейку или интервал ячеек, содержащие числа или формулы, доставляющие числа, например: СУММ(Основание; Приращение).

1.4.6. Типы аргументов

Аргументами функций могут быть любые из следующих объектов:

• числа;
• тексты;
• логические значения;
• значения ошибки;
• ссылки;
• массивы.

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

Числа. В Microsoft Excel числа могут включать только следующие символы: 0 1 2 3 4 5 6 7 8 9 + — ( ) , / $ % . E e. Стоящие перед числом знаки плюс (+) игнорируются, а запятая интерпретируется как разделитель десятичных разрядов. Все другие сочетания клавиш, состоящие из цифр и нецифровых символов, рассматриваются как текст. Примеры чисел: 5,003, 0, 150,286 и -30,05. Числа без десятичной запятой называются целыми, например: 5, 0, 150, и -30. Числа могут иметь до 15 значащих цифр. При вводе отрицательного числа необходимо перед ним вводить знак минус (–) или заключать его в круглые скобки ().Символы, воспринимаемые как числа, зависят от выбранных параметров в свойствах «Языки и стандарты» на панели управления. Выбранные параметры также определяют стандартный формат для чисел — например, в системе с американским стандартом точка (.) используется как десятичный разделитель.

Текст. В Microsoft Excel текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведенные ниже записи интерпретируются как текст: 10AA109, 127AXY, 12-976, 208 4675. Введенный текст выравнивается в ячейке по левому краю. Чтобы изменить выравнивание, выберите команду Ячейки (вкладка Выравнивание) в меню Формат, а затем измените необходимые параметры. Чтобы просмотреть весь текст, занимающий в ячейке несколько строк, установите флажок Переносить по словам на вкладке Выравнивание. Чтобы начать в ячейке новую строку, нажмите клавиши ALT+ENTER.

Примеры текстов: «а», «Слово», «знак/пунктуации» и «» (текстовая константа, которая не содержит символов, называется “пустой текст”). Текстовые значения, используемые в формулах, должны быть заключены в двойные кавычки. Если сам текст содержит двойные кавычки, то их следует удваивать. Например, чтобы определить длину (в символах) текста «в «доброе» старое время», можно использовать формулу: ДЛСТР(«в «»доброе»» старое время»).

Текстовые значения могут быть длиной до 255 символов, считая двойные кавычки.

Примечание:

• Если текст, используемый в качестве аргумента, не заключен в двойные кавычки, то Microsoft Excel предполагает, что это имя и пытается подставить вместо него значение, на которое имя ссылается. Если текст без кавычек не является именем и, следовательно, не имеет значения, Microsoft Excel вернет значение ошибки #ИМЯ?.

Логические значения. Логическими значениями являются ИСТИНА и ЛОЖЬ. Логическими аргументами могут быть также выражения, для которых  может быть вычислено значение ИСТИНА или ЛОЖЬ, например, B10>20.

Значения ошибки. Значениями ошибки являются: #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.

Ссылки. Примеры ссылок: $А$10, A10, $A10, A$10, R1C1 или R[10]C[-10]. Ссылки могут указывать на отдельные ячейки, интервалы ячеек или множественные выделения ячеек, и бывают относительными, абсолютными или смешанными. Если ссылка используется в качестве аргумента, который должен быть числом, текстом, логическим значением или значением ошибки, то в качестве фактического аргумента используется содержимое ячейки, определяемой ссылкой.

Функции, возвращающие ссылки, показывают значения, на которые указывают ссылки, а не сами ссылки.

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

СУММ((E5:E8;E10:E18); СРЗНАЧ(A1:A5)).

Массивы. Массивы позволяют управлять процессом введения аргументов и функций в ячейки. Массивы можно использовать в качестве аргументов, а формулы могут быть введены как массивы формул, например: ={СУММ(B2:D2*B3:D3)}. Использование массивов позволяет упростить разработку некоторых формул рабочего листа и сэкономить память.

1.4.7. Использование точек с запятой в списке аргументов

Отдельные аргументы должны быть разделены точкой с запятой, но при этом нельзя допускать присутствия лишних этих знаков. Если точка с запятой используется только для того, чтобы отметить место аргумента, а сам аргумент не указывается, то Microsoft Excel подставляет значение по умолчанию для этого аргумента, если только он не является обязательным. Например, если ввести (;арг2;арг3) в качестве списка аргументов для функции рабочего листа с тремя аргументами, то Microsoft Excel подставит подходящее значение вместо арг1. Если ввести (арг1;;), то будут подставлены подходящие значения вместо арг2 и арг3.

Для функций рабочего листа, особенно для тех, которые считают количество аргументов до вычисления, лишние точки с запятой будут учитываться при подсчете числа аргументов и, следовательно, будут влиять на способ вычисления значения функции. Например, СРЗНАЧ(1;2;3;4;5) равно 3, но СРЗНАЧ(;;1;2;3;4;5) равно 2,14.

Для большинства аргументов, значение, подставляемое вместо опущенного аргумента, это 0, ЛОЖЬ или  «» (“пустой текст”), в зависимости от того, каков должен быть тип аргумента. Для опущенного ссылочного аргумента значением по умолчанию обычно является активная ячейка или выделение.

Например, если для функции ЯЧЕЙКА опустить второй аргумент и задать «format» в качестве первого аргумента, то текущая выделенная ячейка будет введена в качестве ссылки по умолчанию. Если в качестве значения по умолчанию для опускаемого аргумента используется другое значение, то это явно указывается при описании аргумента.

Примечание:

• Если в качестве аргумента выступает ссылка и в ней точка с запятой используется в качестве оператора объединения, то ссылку нужно заключить в скобки. Функция ОБЛАСТИ, например, имеет один аргумент — ссылку. Если попытаться ввести формулу ОБЛАСТИ(A1;C1), то Microsoft Excel будет интерпретировать A1 и C1 как два различных аргумента и выдаст сообщение “Слишком много аргументов”. Правильная форма выглядит так: ОБЛАСТИ((A1;C1)).

1.5. Построение диаграмм

Графическое представление данных очень важно для их анализа и интерпретации. Это связано с тем, что человек более 50% информации получает визуально, а данные, представленные в графическом виде (диаграммы, графики), существенно упрощают их восприятие и понимание. В Microsoft Excel имеются мощные встроенные возможности для графического представления и анализа данных. В вашем распоряжении — девять типов двумерных и шесть типов трехмерных диаграмм, каждая из которых имеет несколько разновидностей. Основные типы диаграмм представлены в окне Мастер диаграмм на рис. 1.8. Например, гистограммы можно построить в 10 форматах: с перекрытием (Overlapped), кластерные (Clustered), составные (Stacked), 100% составные (100 percent-stacked) и др. Все эти форматы приведены на рис. 1.7. Возможно также комбинирование основных типов диаграмм. Так, на одной диаграмме Вы можете наложить график изменения стоимости товара на гистограмму изменения объема его продажи. Возможно даже создание “диаграмм-картинок”, в которых для представления значений вместо обычных линий и столбцов используются самые разнообразные графические образы. Все это в сочетании с пользовательскими форматами дает практически неограниченные возможности для построения диаграмм.

Диаграммы могут строиться как непосредственно на рабочих листах с данными, так и на отдельных листах рабочей книги — листах диаграмм. Диаграммы, создаваемые на рабочих листах, называются внедренными (встроенными) диаграммами (embedded charts) и представляют собой графические объекты. Как и все графические объекты (геометрические фигуры, рисунки, кнопки и др.), внедренные диаграммы могут располагаться в любом месте рабочего листа, в том числе и над исходными данными. Вы можете легко изменить размеры, а также цветовое и графическое оформление диаграмм. Основные технические характеристики диаграмм приведены в табл. 1.3.

Таблица 1.3

Ограничения в построении диаграмм

*Зависит от марки принтера и его программного обеспечения.

1.5.1. Категории и ряды данных на диаграммах

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

Ряды данных (Data series) — наборы значений, изображаемые на диаграммах. Например, при построении диаграммы дохода компании за прошедшее десятилетие рядом данных является набор значений дохода за каждый год. Точно так же, при построении диаграммы распределения прибыли по отделам компании рядом данных является набор значений прибыли по каждому отделу. Аналогично, для построения диаграммы средней дневной температуры за июль нужен ряд данных — набор значений средней дневной температуры за все дни месяца. Каждый используемый в диаграмме ряд данных может иметь не более 4000 значений или точек данных (Data points). Еще раз отметим что на одной диаграмме может быть отображено до 255 рядов данных, но при этом общее число точек данных не может превышать 32000 (см. табл. 1.4).

Категории (Categories) предназначены для упорядочения значений в рядах данных. При рассмотрении дохода за прошедшие десять лет категориями являются годы, например: 1985, 1986, 1987, …, 1994. При построении диаграммы распределения прибыли по отделам компании категориями могут быть названия или коды отделов. Для графика средней дневной температуры за июль категориями являются дни месяца — 1 июля, 2 июля, 3 июля и т. д.

Примечание:

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

1.5.2. Создание диаграмм

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

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

Примечание:

• Чтобы быстро создать лист диаграммы, выделите необходимые данные, включая категории и названия рядов, затем нажмите клавишу F11. Если на основе сделанного выделения нельзя однозначно определить способ графического представления данных, то автоматически запускается Мастер диаграмм.

1.5.3. Как внедрить диаграмму на текущий лист

Значительно упрощает процесс создания внедренных диаграмм на текущем листе кнопка Мастер диаграмм (Chart Wizard), которая находится на панели инструментов Стандартной. Для этого достаточно выделить нужные данные и щелкнуть мышью по кнопке Мастер диаграмм (см. рис. 1.3). Далее необходимо, следуя подсказкам в виде последовательно открываемых диалоговых окон, указать место внедрения диаграммы, ее тип, формат и задать для нее желаемые параметры. Всего, процедура построения диаграмм состоит из пяти шагов. После выполнения каждого из них можно перейти к следующему, щелкнув кнопку Далее > (Next), или вернуться на шаг назад, щелкнув кнопку < Назад (Back). Щелкнув по кнопке Отмена (Cancel), в любое время можно отказаться от построения диаграммы, а щелкнув по кнопке Готово (Finish) — пропустить оставшиеся шаги и завершить построение диаграммы на основании уже введенной информации.

Примечание:

• Существует несколько альтернативных способов запуска Мастера диаграмм. Кнопка Мастер диаграмм имеется на панели инструментов Диаграмма (Chart). Запустить Мастера диаграмм можно также, выбрав пункт Диаграмма (Chart) из меню Вставка (Insert) и выполнив команду На этом листе (On This Sheet).

Рассмотрим процесс внедрения диаграммы на примере. Допустим, у нас имеются данные изменения параметра крови Ф_3 у больных в процессе курса их лечения. Эти данные будут использованы в дальнейшем для построения диаграммы. Они приведены на рис. 1.8. Для построения диаграммы необходимо выполнить следующие действия:

1. Выделите интервал ячеек, содержащих данные для построения диаграмм (диапазон В4:F7), и щелкните по кнопке Мастер диаграмм. Можно также выполнить это при помощи команд меню, как это было указано ранее. Как показано на рис. 1.11, вокруг выделенной области побежит пунктирная рамка, указатель мыши превратится в крестик с символом диаграммы, а в строке состояния появится сообщение: “Укажите курсором положение диаграммы” (Drag in document to create a chart). При выделении области желательно выделять не только данные, но и текст для подписей. В нашем случае это заголовки столбцов и строк таблицы.

2. Установите крестик указателя мыши в левом верхнем углу области для диаграммы (например, ячейка J8), нажмите на кнопку мыши и, не отпуская ее, переместите указатель к противоположному углу области (например, к ячейке L20). При движении указателя место для диаграммы охватывает прямоугольный контур. Нет необходимости точно указывать область, куда впоследствии будет помещена диаграмма, так как потом вы сможете ее изменить.
3. Отпустите кнопку мыши. На экране откроется диалоговое окно первого шага многошаговой процедуры Мастера диаграмм (см. рис. 1.12). Если вы согласны с выбранной областью данных, то для перехода к следующему шагу щелкните мышью по кнопке Далее. Если вы хотите строить диаграммы на основе другого интервала данных, введите его. Так как в нашем примере данные были выделены до вызова Мастера диаграмм, поле ввода Диапазон (Range) уже содержит ссылку на интервал ячеек с данными для диаграммы. Но, в общем случае, вы можете сначала вызвать Мастера диаграмм, а затем либо напечатать формулу ссылки на нужный диапазон ячеек в поле Диапазон, либо выделить его при помощи мыши. Если при выделении диапазона ячеек диалоговое окно Мастер диаграмм будет вам мешать, перетащите его в сторону при помощи мыши (позиционируйте указатель мыши над заголовком этого окна и, удерживая левую кнопку мыши нажатой, тяните его). При перемещении данного диалогового окна допускается, что его часть может оказаться за пределами экрана.
4. Диалоговое окно Мастер диаграмм второго шага содержит пятнадцать основных типов диаграмм, имеющихся в Excel. Выберите тип диаграммы, которую вы собираетесь построить (например Гистограммы), и щелкните мышью по кнопке Далее (см. рис. 1.13.).

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

6. На четвертом шаге процедуры пост­роения диаграмм в окне Мастер диаграмм (см. рис. 1.14) задаются параметры, которые определяют каким образом будут интерпретироваться данные. Здесь вы можете указать, где находятся ряды данных (в строках или в столбцах), установив флажок в соответствующей области. В нашем случае ряды данных расположены в столбца, так как в процессе решения нет смысла результаты анализов одного пациента сравнивать с результатами анализов другого (целесообразнее отследить изменения результатов анализов у одного больного). В зависимости от местонахождения флажка изменяется назначение расположенных ниже полей. Вы них предлагается задать: какой столбец считать метками по оси Х (горизонтальная ось) и какой по оси Y (вертикальная ось). Это имеет смысл если при задании области данных, на основе которой строится диаграмма, вы выделили также названия строк и столбцов. При изменении содержимого этих полей вы сразу увидите их результат в окне предварительного просмотра диаграммы. После внесения всех необходимых изменений щелкните мышью по кнопке Далее.

Примечание:

• Microsoft Excel пытается автоматически анализировать выделенные данные, определяя, что относится к рядам данных а что к категориям. Алгоритм анализа основан на том, что количество рядов должно быть меньше, чем категорий. На основе этого, определяется где расположены ряды данных — в столбцах или строках. Кроме того, выясняется, имеют ли они имена. Таким образом, если диаграмма строится для диапазона ячеек, в котором строк меньше чем столбцов (или количество строк и столбцов равно), то рядами данных считаются строки. Если же интервал ячеек имеет меньше столбцов, чем строк, то рядами данных считаются столбцы.
• Когда выделенный диапазон включает строки и столбцы с текстовыми данными (например, диапазон В4:F7 на рис. 1.11), то они используются для оформления диаграммы. Названия (Labels), связанные с рядами данных, будут считаться именами рядов данных и составят легенду диаграммы. Данные, интерпретируемые как категории, считаются названиями категорий и выводятся на диаграмме вдоль оси X (горизонтальной оси). Для этих целей применяются любые данные из верхней строки или левого столбца выделенного интервала ячеек. В качестве названий категорий может использоваться, например, столбец (или строка) числовых значений.
• Например, на рис. 1.11 в выделенном диапазоне В4:F7 ряды данных содержатся в строках, а категории — в столбцах. Текстовые данные в левом столбце диапазона — “Иванов”, “Петров” и “Сидоров” — интерпретируются в качестве названий рядов данных и появятся в легенде диаграммы. Текстовые данные в первой строке интервала — “1 сутки”, “2 сутки”, “15 сутки” — интерпретируются как названия категорий и будут выведены вдоль оси X.

При построении диаграммы для диапазона ячеек, не имеющего имен рядов данных, Microsoft Excel автоматически присвоит каждому ряду стандартное имя. Первый ряд данных будет назван Ряд1 (Series1), второй — Ряд2 (Scries2) и т.д. Впоследствии вы сможете изменить эти названия, внеся изменения в формулу РЯД (SERIES), которая используется с целью построения диаграммы.

7. На пятом шаге процедуры построения диаграмм в окне Мастера диаграмм предоставляется возможность добавить легенду (условные обозначения), название самой диаграммы, названия для вертикальной (Y) и горизонтальной (X) осей (см. рис. 1.16). Кроме того, для комбинированных типов диаграмм существует возможность добавить название второй вертикальной оси (Y). Все вносимые изменения сразу отображаются в окне предварительного просмотра диаграммы Образец. Добавленная легенда (условное обозначение), как показано в окне предварительного просмотра диаграммы Образец этого диалогового окна, помещается Мастером диаграмм справа от самой диаграммы. При необходимости, ее положение можно будет изменить. Для ввода какого-либо заголовка диаграммы напечатайте его в соответствующем поле ввода. Вводимый текст отображается на диаграмме заданным по умолчанию шрифтом. С помощью команд Редактирования и Форматирования впоследствии можно изменить содержание и внешний вид заголовка. Если вы хотите, чтобы заголовки изменялись вместе с изменением исходных данных, свяжите их с соответствующими ячейками после создания диаграммы, так как Мастер диаграммы не дает возможность установить такие связи. Для завершения работы с Мастером диаграмм и получения, наконец, построенной диаграммы, щелкните по кнопке Готово (Finish). Построенная и внедренная диаграмма приведена на рис. 1.17.

1.5.4. Операции, выполняемые над внедренными диаграммами

• Правильная прямоугольная область для размещения внедренной диаграммы выделяется при нажатой клавише Shift. При нажатой клавише Alt выделяемая область привязывается к сетке рабочего листа.
• Размеры внедренной диаграммы могут быть сколь угодно большими. Чтобы выделить соответствующую область за пределами экрана, протащите указатель мыши за рамки окна рабочего листа.
• Совсем необязательно помещать внедренную диаграмму на свободное место рабочего листа — ее можно поместить где угодно, даже поверх других графических объектов или данных. Поместите на диаграмму указатель мыши, нажмите левую кнопку и перетащите контур диаграммы на новое место.
• Для изменения размеров диаграммы выделите ее, а затем перетащите один из расположенных по ее периметру маркеров выделения в нужном направлении.
• Для удаления внедренной диаграммы выделите ее, а затем выполните команду Все (All) из подменю Очистить (Clear) меню Правка (Edit) или нажмите клавишу Del.

1.5.5. Как вставить диаграмму на листе диаграммы

1. Выделите строки или столбцы, содержащие данные и текст для подписей, из которых необходимо построить диаграмму.
2. В меню Вставка выберите пункт Диаграмма, затем команду На новом листе.
3. Следуйте инструкциям Мастера диаграмм, как было описано ранее.

Размер создаваемой на отдельном листе диаграммы можно задать так, чтобы она занимала все окно. Для этого установите в меню Вид (View) флажок По размеру окна (Sized With Window). Диаграмму, которая построена на отдельном листе, можно напечатать независимо от рабочего листа с исходными данными.

1.5.6. Как построить диаграмму для данных из несмежных диапазонов

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

1. Выделите первую группу ячеек, содержащих необходимые категории или ряды.
2. Удерживая нажатой клавишу Ctrl, выделите дополнительные необходимые группы ячеек.
3. В меню Вставка выберите пункт Диаграмма, затем команду На этом листе или команду На новом листе.

1.5.7. Как изменить внедренную диаграмму

Внести некоторые изменения в диаграмму можно при помощи кнопок панели инструментов Диаграмма. Если этой панели на экране нет, то выберите пункт Панели инструментов (Toolbars) из меню Вид (View). На экране появится диалоговое окно включения/выключения панелей инструментов. Установите флажок напротив надписи Диаграммы и щелкните по кнопке OK. Панель инструментов Диаграмма имеет вид, приведенный на рисунке 1.18.

Кроме того, изменить внедренную диаграмму можно войдя в режим ее изменения. Для этого необходимо дважды щелкнуть по ней мышью. После этого вы сможете получить доступ к ее элементам. Войти в данный режим также можно при помощи локального меню. Для этого необходимо щелкнуть по диаграмме правой кнопкой — на дисплее появится локальное меню. Щелкнув по пункту Редактировать объект, вы войдете в режим редактирования диаграммы. Далее можно получить доступ к возможным вариантам изменения диаграммы либо через главное меню (пункты подменю в нем изменятся в соответствии с текущим режимом), либо щелкая правой кнопкой то элементам, которые вы хотите редактировать, и выбирая из контекстно-чувствительного локального меню соответствующие пуны. Кроме того, при помощи мыши вы можете изменить размеры некоторых элементов диаграммы или перетянуть их на другое место в пределах диаграммы. Также у вас есть возможность изменить размер диаграммы и ее элементов путем их выделения и растягивания при помощи мыши за соответствующие маркеры в необходимом направлении. Здесь интерфейс пользователя довольно интуитивный и для выполнения данных действий необходимо иметь только базовые навыки по работе с мышью и объектами в среде Windows.

1.5.8. Изменение типа диаграммы

Проще всего изменить тип диаграммы можно щелкнув по стрелке на кнопке Тип диаграммы (Chart Туре) на панели инструментов Диаграмма (Chart) — раскроется палитра с различными типами диаграмм. В ней содержатся кнопки для всех основных типов диаграмм Microsoft Excel:

• Диаграмма с областями (Area),
• Линейчатая (Bar),
• Гистограмма (Column),
• График (Line),
• Круговая (Pie),
• Точечная [XY (Scatter)],
• Кольцевая (Doughnut),
• Объемная диаграмма с областями (3-D Area),
• Объемная линейчатая (3-D Bar),
• Объемная гистограмма (3-D Column),
• Объемный график (3-D Line),
• Объемная круговая (3-D Pie),
• Поверхностная диаграмма (3-D Surface)
• Лепестковая диаграмма (Radar).

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

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

1.5.9. Изменение других параметров диаграммы

Все диаграммы всегда связаны с исходными данными на рабочих листах посредством формул, построенных на основе функции РЯД() [SERIES()]. Поэтому, каждое изменение исходных данных приводит к изменению диаграммы.

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

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

Примечание:

• В качестве заголовков и названий можно использовать имеющиеся на рабочем листе текстовые данные. Для этого при выделенном поле названия на диаграмме напечатайте знак равенства (=) в строке формул, щелкните по ячейке с нужным текстом (можно выделить интервал ячеек) и по кнопке ОК строки формул (или нажмите кнопку Enter). Теперь, при изменении в указанной ячейке, будет автоматически меняться заголовок диаграммы.

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

Microsoft Excel имеет множество встроенных функций рабочего листа. Но среди них вы можете не найти подходящую функцию для решения конкретных задач. Например, вы в своих расчетах применяете специальную формулу или по своему опыту знаете, что функция, которая уже имеется в Microsoft Excel вас не устраивает. Например, авторов данных строк не устраивает как работает в электронных таблицах функция ранжирования РАНГ() (RANK()) (просто она работает неправильно) и, чтобы восполнить данный пробел, им пришлось написать на языке Visual Basic for Application* собственную функцию. Возникает вопрос: как же создать собственную функцию? В данном подразделе будут описаны основные подходы применяемые для создания собственных функций. Читателям, которые практически не знакомы с программированием, не следует пугаться и закрывать книгу. Излагаемый далее материал прост в усвоении, а все шаги, которые необходимо выполнить начинающему пользователю при его освоении и применении, будут детально проиллюстрированы.

Возможность создавать и применять пользовательские функции (custom) делает Microsoft Excel мощным и гибким инструментом для решения разнообразных задач. Эти функции представляют собой написанные на языке Visual Basic специальные подпрограммы, называемые также процедурами-функциями (Function procedures).

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

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

Для того, чтобы с пользовательскими функциями можно было обращаться как с обычными встроенными функциями, вам необходимо сохраните рабочую книгу со всеми нужными функциями пользователя в специальном формате дополнения (Add-in format) Microsoft Excel.

Следует отметить, что в Microsoft Excel 97 и Microsoft Excel 2000 процедура создания пользовательской функции отличается от описанной выше. Начиная с Microsoft Excel 97 был введен единого для всех, входящих в Microsoft Office продуктов (Word, Acsess, PowerPoint и др.) средства разработки Редактор Visual Basic (Visual Basic Editor).

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

*Visual Basic for Application (VBA) — объектно-ориентированный язык программирования, специально раз­работанный ранее для записи макросов и после этого дополненный возможностью создания процедур (функций, подпрограмм, свойств) в приложениях. Впервые он появился в Excel 5.0, затем его стали использовать все остальные приложения Microsoft Office.

1.6.1. Как создать собственную функцию

Процесс создания собственной функции пользователя состоит из двух этапов. На первом этапе необходимо создать (вставить) новый лист модуля, так как в Microsoft Excel макросы и функции пользователя хранятся в специальных листах — модулях. На втором этапе необходимо определить само тело функции с помощью операторов Visual Basic for Application. Функции пользователя могут не зависеть от определенного рабочего листа, это позволяет применять их при работе с несколькими листами. Кроме того, несколько функций пользователя можно объединить на одном листе модуля и использовать в качестве библиотеки.

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

Согласно теореме Пифагора, в прямоугольном треугольнике квадрат гипотенузы равен сумме квадратов катетов: a²+b² = c². Исходя из данной формулы мы можем вычислить длину любой стороны прямоугольника при условии что длины двух других сторон нам известны:
.

1. Вставьте новый лист модуля в открытую рабочую книгу, выполнив команду Модуль (Module) из подменю Макрос (Macro) меню Вставка (Insert). На экране появится чистый лист модуля и панель инструментов Visual Basic.

Лист модуля похож на лист текстового редактора. На нем вы можете выполнять те же действия, что и на листе тестового редактора: копировать, перемещать, вставлять и удалять введенные операторы Visual Basic, а также комментарии к ним. В строке меню появляется новый пункт Выполнить (Run).

2. Для удобства в работе присвойте модулю имя. Выберите команду Переименовать (Rename) из подменю Лист (Sheet) меню Правка (Edit). В появившемся диалоговом окне Переименовать лист (Rename Sheet) введите имя, которое, мнемонтически напоминало бы (желательно) о функции данного модуля. Например, для нашей цели модуль можно назвать так: Выч_стор_тр.
3. Введите текст пользовательской функции (см. ниже). Для нашего примера введите следующие операторы. Изображение листа модуля с набранной на нем функцией приведен на рис. 1.19.

Примечание:

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

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

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

Что же касается Microsoft Excel 97 и Microsoft Excel 2000 то перед тем, как вставлять новый модуль, необходимо сперва активизировать Редактор Visual Basic. Это можно сделать двумя способами: а) нажатием комбинации клавиш Alt+F11; б) войдя в меню Сервис, выбрать пункт Макрос, а потом в раскрывшемся подменю щелкнуть по пункту Редактор Visual Basic (см. рис 1.21).

После того, как Редактор Visual Basic будет активизирован, вставьте новый модуль, войдя в меню Insert и выбрав пункт Module (см. рис 1.22). Все действия в модуле выполняются аналогично описанным выше.

ЛИТЕРАТУРА, РЕКОМЕНДУЕМАЯ К ИЗУЧЕНИЮ

1. Додж М., Кината К., Стинсон К. Running Microsoft Ехсеl 5 для Windows: В 2 т. / Пер. с англ. — М.: Издательский отдел «Русская Редакция» ТОО «Channel Trading Ltd.», 1995. — Т 1. — 368 с.
2. Додж М., Кината К., Стинсон К. Running Microsoft Ехсеl 5 для ТУтботУз: В 2 т. / Пер. с англ. — М.: Издательский отдел «Русская Редакция» ТОО «Channel Trading Ltd.», 1995. — Т. 2. — 528 с.
3. Долженков В.А., Колесников Ю.В. Microsoft® Ехсеl 2000. — СПб.: БХВ -Санкт-Петербург, 1999. — 1088 с.
4. Курпцкнй Б.Я. Поиск оптимальных решений средствами Ехсеl 7.0. — СПб.: БХВ — Санкт-Петербург, 1997. — 384 с.
5. Уэллс Э., Хешбаргер С. Microsoft® Ехсеl97: разработка приложений / Пер. с англ. — СПб.: БХВ — Санкт-Петербург, 1998 — 624 с.