Зданием персональных компьютеров и наряду с другими программами офисного назначения, в частности с текстовыми процессорами, способствовали росту популярности пк
| Вид материала | Программа |
- 5. Персональный компьютер и его устройство Урок История и архитектура персональных, 153.53kb.
- Оболочка Norton Commander. Windows и программа, 26.31kb.
- Автономность эксплуатации без специальных требований к условиям окружающей среды, 258.87kb.
- Слово «компьютер» означает «вычислитель». Потребность в автоматизации обработки данных,, 256.3kb.
- Правила работы и защиты персональных компьютеров и информационных ресурсов локальной, 134.07kb.
- Бучения программированию как систематической дисциплине, скоро стал широко использоваться, 12.21kb.
- Лекция №3-4, 400.38kb.
- Программа дисциплины «Основы офисного программирования», 173.49kb.
- Тема Предмет, задачи и содержание курса. Аппаратное и программное обеспечение персональных, 76.1kb.
- Реферат программное обеспечение персональных компьютеров исполнитель: Фролова, 251.54kb.
Табличные процессоры (электронные таблицы)
Введение
Программы для обработки табличных данных появились одновременно с созданием персональных компьютеров и наряду с другими программами офисного назначения, в частности с текстовыми процессорами, способствовали росту популярности ПК. Эти программы называются табличными процессорами или электронными таблицами.
Наиболее известными табличными процессорами были Quattro Pro фирмы Novell и Lotus 1-2-3 фирмы Lotus Development Inc. (для MS DOS и Windows).
В настоящее время наиболее распространенным табличным процессором, обладающим развитыми функциональными возможностями, безусловно, является MS Excel, входящий в состав пакета MS Office.
1. Табличный процессор MS Excel — универсальная система обработки данных
Второй продукт пакета Microsoft Office, который мы начинаем изучать на этой лекции — Microsoft Excel — является, пожалуй, самой существенной частью пакета. Обычно на Excel ссылаются как на систему электронных таблиц, но этого явно недостаточно для характеристики многообразных возможностей Excel. Поэтому иногда применяют другое обобщающее название для этого программного продукта: Microsoft Excel — универсальная система обработки данных.
2. Составные части Excel
Собственно Excel состоит из трех частей:
1) Средства для работы с таблицами
2) Средства для работы с диаграммами
3) Средства для работы с базами данных (со списками)
Вместе с Excel поставляются также надстройки.
Надстройка – это некоторая дополнительная программа, которая расширяет функциональные возможности основного приложения, в данном случае Excel. Надстройки разрабатываются или самой фирмой Microsoft, или другими фирмами. В совокупности надстройки Excel сравнимы по объему и разнообразию средств с самим Excel. Наиболее важными являются надстройки «Поиск решения» и «Пакет анализа», которые часто используются при проведении диссертационного исследования.
3. Окно Excel и окна рабочих книг
Excel, как и любое другое приложение, исполняемое в среде Windows 9Х, имеет свое окно (рис. 1) со стандартными элементами: строка заголовка; строка основного меню; панели инструментов (пиктографическое меню); рабочая область, где располагаются обрабатываемые программой объекты (документы), в данном случае – рабочие книги; строка состояния, где отображается текущее состояние программы.
Кроме этих стандартных элементов окна приложения Windows (они есть, например, и у Word), в Excel имеется дополнительный элемент – строка формул. В левой части строки формул (раскрывающийся список – элемент GUI), называемой полем имен, указывается адрес текущей ячейки текущей рабочей книги (С2). В правой части строки формул указывается содержимое ячейки, указанной в поле имен. В средней части строки формул появляются три кнопки только тогда, когда производится ввод или редактирование данных в ячейке, указанной в поле имен.
4. Структура окна рабочей книги
Рабочая книга состоит из рабочих листов. Их может быть до 255 (в Excel 2000 максимально возможное число листов ограничивается только объемом доступной памяти). В рабочей книге Книга1.xls, открываемой при запуске Excel, обычно бывает 3 или 16 листов. Манипуляции с рабочими листами производятся с помощью ярлычков рабочих листов и четырех кнопок
, расположенных внизу окна рабочей книги.
Рабочий лист — это множество элементарных ячеек, каждая из которых принадлежит некоторому столбцу и одновременно — некоторой строке.
Рабочий лист имеет 256 столбцов с именами от A до IV и 16384 (214) строки с номерами от 1 до 16384 (в Excel 97 и 2000 — 65536 (216) строк). Имя столбца и номер строки в совокупности однозначно идентифицируют ячейку, которая им (одновременно) принадлежит. Этот идентификатор называется адресом ячейки или ссылкой на ячейку.
5. Ячейка, ее адрес и содержимое
Ячейка называется активной или текущей, если она выделена табличным курсором — жирной рамкой. Выделение ячейки производится щелчком по ячейке указателем мыши, имеющим форму жирного белого креста (см. ниже).
Квадратное утолщение в правом нижнем углу табличного курсора называется маркером заполнения.
Стиль ссылок «имя столбца — номер строки» (мы будем его использовать) в Excel называется «А1».
Excel поддерживает и другой стиль ссылок, когда нумеруются как строки, так и столбцы. Такой стиль ссылок иногда бывает удобен и называется «R1C1» (от Row — строка, Column — столбец).
Активная ячейка на рис. 1 имеет адрес R2C3 (что означает «вторая строка, третий столбец») в стиле R1C1 и адрес С2 в стиле А1 (точнее было бы $C$2, но об этом позже).
Примечание. Ячейкам можно также присваивать собственные имена и использовать эти имена для ссылок на ячейки наряду с адресами.
Каждая ячейка характеризуется содержимым, форматом и примечанием.
Содержимое ячейки – это набор до 255 символов, которые вводятся в ячейку печатью на клавиатуре, копированием содержимого других ячеек или так называемым автозаполнением. Этот набор символов (литер) Excel трактует либо как литеральное значение, либо как формулу. Признаком формулы является первый символ «=».
Литеральное значение бывает трех типов: текстовое, числовое, логическое (примеры литеральных значений см. рис. 2). Дата и время представляют собой значения числового типа.
Формула вырабатывает значение одного из этих трех типов (или ошибку).
Формат ячейки используется для придания содержимому ячейки требуемого вида изображения на экране.
Примечание к ячейке создается пользователем и обычно представляет собой некий поясняющий текст к содержимому ячейки.
Примечание. В версии Excel 7.0 предполагалось, что в качестве примечания может использоваться аудиофайл и даже видеоклип. Но в Excel 2000 об этом уже ничего не сказано.
Н
а рис. 2 приведены примеры с различными характеристиками ячеек.
Рис. 2. Примеры содержимого, формата и примечания ячейки
6. Виды указателя мыши
- толстый белый крест. Появляется при перемещении указателя по клеткам текущей рабочей книги. Используется для выделения ячейки, а также диапазона ячеек.
I – Появляется при щелчке в строке формул, двойном щелчке над ячейкой или нажатии на клавишу F2 с целью редактирования содержимого ячейки. Определяет место в ячейке или в строке формул, где будет проводиться редактирование.
– Появляется при установке указателя на маркере заполнения (толстый белый крест «худеет и чернеет»). Используется при копировании содержимого активной ячейки и при автозаполнении последовательности ячеек.
– Появляется при установке указателя мыши на одном из краев табличного курсора (но не на маркере заполнения!), а также вне ячеек рабочего листа. На рабочем листе используется для копирования и перемещения выделенных ячеек методом drag&drop.
Вешки расположены в верхней части вертикальной и в правой части горизонтальной полос прокрутки. Ярлычки листов и горизонтальная полоса прокрутки также разделяются вешкой.
– Появляются при установке указателя на вешку. Используются для разделения рабочего листа по вертикали и/или по горизонтали на две области. Такое разделение полезно при работе с большими таблицами (примеры использования см. рис. 3).
Рис. 3. Пример использования вешек при работе с большими таблицами
7. Диапазоны ячеек
Кроме понятия ячейки, используется понятие блока ячеек — прямоугольной области смежных ячеек. Блок задается указанием адреса верхней левой ячейки и правой нижней ячейки, разделенных символом : (двоеточие).
Блок выделяется указателем мыши, имеющим форму толстого белого креста:
1) операцией drag&drop от левого верхнего угла к нижнему правому углу;
2) после щелчка по ячейке в левом верхнем углу производится щелчок по ячейке в нижнем правом углу при нажатой клавише Shift.
Блок, состоящий из нескольких смежных целых столбцов, обозначается именами крайнего левого и крайнего правого столбцов, разделенными двоеточием. Например, запись B:G обозначает не Бориса Гребенщикова, а все ячейки в столбцах B, C, D, E, F и G.
Можно догадаться, что обозначает запись 6:9.
Все ячейки рабочего листа выделяются щелчком по кнопке Выделить все, находящейся на пересечении заголовков строк и столбцов рабочего листа.
Часто вместо термина блок применяют термин диапазон (или интервал). Понятие диапазон шире, чем блок: диапазон может состоять из нескольких блоков (возможно пересекающихся !).
Примечание. Ячейка является частным случаем блока. Ячейка и блок являются частными случаями диапазона.
Диапазон ячеек, состоящий из нескольких блоков, выделяется указателем мыши, имеющим форму толстого белого креста:
1) первый блок выделяется как обычно;
2) остальные блоки добавляются к диапазону путем выделения этих блоков операцией drag&drop от левого верхнего угла к нижнему правому углу при нажатой клавише Ctrl.
У выделенного блока, как и у выделенной ячейки, в правом нижнем углу имеется маркер заполнения, который может использоваться для копировании и автозаполнения последовательности ячеек. У диапазона, состоящего из нескольких блоков, маркера заполнения нет.
8. Ввод и редактирование данных
Вводить и редактировать информацию в рабочей таблице можно только в текущей ячейке. При первоначальном вводе информации с клавиатуры она появляется и в текущей ячейке и в строке формул.
Для изменения содержимого ячейки необходимо либо дважды щелкнуть на ячейке, либо при выделенной ячейке щелкнуть на строке формул или нажать клавишу F2. В точке ввода появляется мигающий курсор «|», а в середине строки формул – три кнопки fx (или =) .
Завершение ввода осуществляется :
- • щелчком по кнопке ;
- • щелчком по кнопке , нажатием на клавишу Enter (или Enter+Shift), нажатием на клавишу Tab (или Tab +Shift).
В первом случае содержимое ячейки остается неизменным, во втором случае внесенные изменения фиксируются в ячейке.
При завершении ввода щелчком по или активная ячейка остается прежней.
При завершении ввода нажатием на клавишу Enter активной становится ячейка, находящаяся под прежней активной. При завершении ввода нажатием на клавиши Enter+Shift активной становится ячейка, находящаяся над прежней активной.
При завершении ввода нажатием на клавишу Tab активной становится ячейка, находящаяся правее прежней активной. При завершении ввода нажатием на клавиши Tab+Shift активной становится ячейка, находящаяся левее прежней активной.
Если в ячейку вводилась НЕ ФОРМУЛА, то завершить ввод можно щелчком по любой другой ячейке. В этом случае изменение фиксируется в ячейке и активной становится ячейка, на которую щелкнули.
При вводе информации Excel распознает тип данных и по умолчанию сдвигает числовые данные вправо, текстовые – влево. Логическое значение выравнивается по центру ячейки.
8.1. Автозаполнение
Автозаполнение ряда последовательных ячеек осуществляется применением операции drag&drop к маркеру заполнения выделенной ячейки (или блока!) в трех случаях:
- •при вводе встроенных в Excel последовательностей (списков), в том числе последовательностей типа арифметической прогрессии;
- •при вводе списков, созданных самим пользователем;
- •при копировании.
С
оответствующие примеры приведены на рис. 4.
Рис. 4. Примеры автозаполнения
9. Основная идея электронных таблиц
Ячейки рабочего листа предназначены для того, чтобы хранить различные значения. Таким образом, ячейка может играть такую же роль, как переменная в математике: она имеет обозначение (имя или адрес) и может иметь и менять значение. Всякое вычисление состоит в том, что по значениям одних переменных вычисляются значения других переменных. Обычно способ вычисления описывается с помощью формулы, содержащей математические операции и функции. Но сама формула также хранится в ячейке и вырабатывает значение.
В этом и состоит основная идея электронных таблиц: одни ячейки рабочего листа используются как независимые переменные (в Excel они называются — влияющие ячейки), которым должны быть приданы значения извне, а другие ячейки используются как зависимые переменные (они так и называются в Excel — зависимые ячейки), которые содержат формулы, ссылающиеся на независимые переменные. Пользователь вводит исходные данные во влияющие ячейки, автоматически производятся вычисления по формулам, находящимся в зависимых ячейках, и пользователь видит готовый результат вычислений в зависимых ячейках.
Несмотря на внешнюю простоту этой основной идеи, она является чрезвычайно плодотворной. Для подтверждения сказанного приведем несколько следствий из основной идеи.
9.1. Следствия из основной идеи электронных таблиц
1.Влияющая ячейка может влиять на множество зависимых ячеек: сколь угодно много формул в разных зависимых ячейках могут ссылаться на одну и ту же влияющую ячейку (рис. 5). Автоматическое вычисление может быть сколь угодно широким (состоять из многих параллельных шагов).
2.Цепочка зависимостей может быть сколь угодно длинной: одна ячейка зависит от другой ячейки, которая в свою очередь зависит от третьей, и т. д (рис. 5). Автоматическое вычисление может быть сколь угодно длинным (состоять из многих последовательных шагов).
3.Формула в зависимой ячейке может носить условный характер: если некоторое условие выполняется, то нужно производить вычисления по одной формуле, а если не выполняется, то по другой (рис. 6). Автоматическое вычисление может быть сколь угодно многовариантным (состоять из многих альтернативных шагов).
4.Формула может содержать сколь угодно сложные функции, а не только арифметические операции (рис. 6). Автоматическое вычисление может быть сколь угодно сложным (состоять из многих сложных шагов).
5.Ссылка зависимой ячейки на влияющие ячейки может быть относительной: например, зависимая ячейка может содержать формулу типа "сумма двух ячеек, расположенных непосредственно над данной ячейкой". Такая относительная схема вычислений может быть перенесена или скопирована в другую часть рабочего листа или в другой рабочий лист и использована для вычислений с другими исходными данными (рис. 6).
6.Если имеется несколько рядов значений в ячейках (например, несколько строк или столбцов), то их можно проинтерпретировать как наборы значений нескольких функций для одного и того же набора значений аргумента. В таком случае ряды чисел на рабочем листе можно автоматически представить в графической форме, в виде диаграмм различного типа (рис. 6).
7.Формула в зависимой ячейке может ссылаться не только на отдельные ячейки, но и на целые диапазоны ячеек (например, при суммировании: рис. 6).
8.Формула может возвращать не один результат в одной ячейке, а сразу несколько результатов в диапазоне ячеек (такие формулы называются формулами массива – будут использоваться в курсе ЭММ). Таким образом, небольшим количеством формул удается задать весьма объемные вычисления.
9.В Excel легко производить вычисления не только на плоскости (на основе данных диапазона рабочего листа), но и в трехмерном пространстве (на основе значений в стопке диапазонов). Для реализации таких вычислений используются так называемые трехмерные ссылки.
10. В ячейках могут храниться не только числа, но и данные других типов: тексты, даты, логические значения. Над такими данными можно выполнять присущие им операции, например, форматирование текстов. Рабочий лист может не только содержать сухие столбики чисел, но все нужные элементы оформления: надписи, картинки и т. д.
10. Проектирование электронных таблиц
При разработке электронных таблиц следует придерживаться следующей технологии:
1.Проектирование выходных документов. Исходя из условий задачи и возможностей используемого табличного процессора анализируется содержание документов и определяются их составные части, а также алгоритмы получения расчетных данных.
2.Формулировка задачи в терминах конечного пользователя.
3.Разработка электронной формы документа: формирование на экране выходного документа средствами табличного процессора, включающее все составляющие документа (шапка, табличная часть, формулы для вычисляемых данных и т. д.).
4.Выполнение расчетов: ввод исходных данных и получение расчетных значений.
5.Сохранение таблицы на внешнем носителе.
6.Получение твердой копии документа: вывод на печать.
10.1. Правила организации рабочей таблицы
1.По мере возможности стараться организовать информацию в виде блоков в которых данные размещены в соседних строках и столбцах. Начинать заполнение таблицы надо с левого верхнего угла вниз и вправо. Для удобства можно отделять блоки друг от друга пропуская по одной строке и одному столбцу.
2.Внутри блока не пропускать строки и столбцы только для того, чтобы разделять информацию. Помещать сколько угодно свободного пространства между строками и столбцами можно с помощью расширения столбцов и увеличения высоты строк или способов выравнивания данных.
3.Зарезервировать слева один столбец для заголовков строк.
4.Зарезервировать сверху одну строку для заголовков столбцов.
5.Если для блока нужна шапка, то ввести ее в требуемое количество строк, находящихся над заголовками столбцов, и в столбец (или несколько столбцов) над заголовками строк.
П
римерный вид расположения блоков рабочей таблицы показан на рис. 7.
Рис. 5, 6, 7
1. Формулы
Как было сказано ранее, в ячейках кроме литеральных значений (числа, текст, логические) могут находиться и формулы. Формулы в Microsoft Excel подчиняются определенному синтаксису, или порядку, согласно которому в формулу включается знак равенства (=), за которым идут вычисляемые элементы (операнды), разделенные операторами.
Пример формулы:
Операнды
Операторы
= Оклад + ЕСЛИ(B5>=10;10%;0) * C5 - Налог + 100
Имена
Функция
Адрес ячейки
Константа
В качестве операндов могут выступать постоянные значения (константы), ссылки на ячейки или диапазоны ячеек, имена или функции.
В Excel действия выполняются слева направо — с учетом приоритета операторов — начиная от знака равенства (=). Порядком вычисления можно управлять с помощью скобок, группируя действия, которые должны выполняться в первую очередь.
Например, результатом формулы =5+2*3 будет число 11, поскольку Excel выполняет умножение до сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.
Если же с помощью скобок изменить синтаксис =(5+2)*3, Excel сложит 5 и 2, а затем умножит результат на 3; результатом этих действий будет число 21.
1.1. Применение операторов в формулах
Операторами обозначаются операции, которые следует выполнить над операндами формулы. В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Арифметические операторы. Служат для выполнения арифметических операций, таких как сложение, вычитание, умножение. Операции выполняются над операндами, имеющими значение числового типа. Используются следующие арифметические операторы.
| Арифметический оператор | Значение | Пример формулы | Результат формулы |
| + (знак плюс) | Сложение | =3+3 | 6 |
| – (знак минус) | Вычитание Унарный минус | =3-1 = -1 | 2 -1 |
| * (звездочка) | Умножение | =3*3 | 9 |
| / (косая черта) | Деление | =3/3 | 1 |
| % (знак процента) | Процент | =20% | 0.2 |
| (крышка) | Возведение в степень | =32 | 9 |
Операторы сравнения. Используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
| Оператор сравнения | Значение | Пример |
| = (знак равенства) | Равно | A1=B1 |
| > (знак больше) | Больше | A1>B1 |
| < (знак меньше) | Меньше | A1 |
| >= (знак больше и знак равенства) | Больше или равно | A1>=B1 |
| <= (знак меньше и знак равенства) | Меньше или равно | A1<=B1 |
| <> (знак «не равно») | Не равно | A1<>B1 |
Текстовый оператор конкатенации (сцепления). Амперсанд (&) используется для сцепления нескольких текстовых строк в одну строку.
| Текстовый оператор | Значение | Пример |
| & (амперсанд) | Сцепление последовательностей символов в одну последовательность | Выражение "Северный " & " ветер" эквивалентно строке "Северный ветер" |
Оператор ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы.
| Оператор ссылки | Значение | Пример |
| : (двоеточие) | Оператор диапазона используется для ссылки на все ячейки между крайними ячейками диапазона, включая эти ячейки. | B5:B15 |
| ; (точка с запятой) | Оператор объединения объединяет несколько ссылок в одну ссылку. | СУММ(B5:B15;D5:D15) |
1.2. Адреса ячеек (ссылки на ячейки) в формулах
Основная идея электронных таблиц реализуется благодаря наличию в формулах адресов ячеек.
1.2.1. Ввод адресов в формулы
Как только вы ввели в ячейку первый символ =(равно), MS Excel переходит в режим ввода формулы. В этом режиме уже нельзя завершить ввод информации в ячейку щелчком на какую либо другую ячейку. Дело в том, что в режиме ввода формулы щелчок по любой ячейке рабочего листа трактуется как попытка ввести в формулу адрес этой ячейки. Это полезно по двум причинам:
- легче щелкнуть по ячейке, чем набирать на клавиатуре ее адрес;
- при вводе адресов с помощью мыши в корне пресекаются часто встречающиеся у начинающих пользователей ошибки, когда адрес ячейки они набирают в кириллице, а не в латинице: имеются 12 букв в кириллице (АВЕКМНОРСТУХ), которые имеют сходное начертание с буквами латиницы. (Кирилл с Мефодием более 1000 лет назад совершили большую диверсию, введя кириллицу :-).
1.2.2. Относительные, абсолютные и смешанные адреса
Вводимые в формулу с помощью мыши адреса ячеек того же листа, в одной из ячеек которого находится сама формула, трактуются MS Excel как относительные адреса. Само слово относительный говорит, что относительный адрес адресуется относительно другой ячейки. Этим другим адресом является адрес ячейки, где находится формула.
Вопрос. Относительно чего адресуется относительный адрес?
Или: Относительно чего ссылается относительная ссылка?
Ответ. Относительный адрес адресуется относительно ячейки, в которой находится формула, содержащая этот относительный адрес.
Или: Относительная ссылка ссылается относительно ячейки, в которой находится формула, содержащая эту относительную ссылку.
Например при вводе в ячейку С2 формулы "=В1", ячейка В1трактуется, как ячейка, расположенная на один столбец левее и на одну строку выше ячейки С2. И никак иначе!
Относительный смысл адресов проявляется при копировании ячеек с формулами, в которых есть относительные адреса. В нашем примере, если мы скопируем ячейку С2 в ячейку С3, то заметим, что там будет уже находиться формула "=В2". И это правильно, потому что ячейка В2 расположена на один столбец левее и на одну строку выше ячейки С2. Обратите внимание, что при копировании ячейки С2 в ячейку С1 MS Excel выдаст предупреждение об ошибке =#ССЫЛКА! Действительно, из ячейки С1 невозможно сослаться на ячейку, находящуюся выше на одну строку: такой строки нет.
Абсолютные ссылки. Если требуется, чтобы ссылки не изменялись при копировании формулы в другую ячейку, необходимо использовать абсолютные ссылки. У абсолютной ссылки перед названием столбца и номером строки ставится знак доллара ($). Например, в формуле =A5*$C$1 A5— относительная ссылка, $C$1 — абсолютная ссылка.
Смешанные ссылки. Если строка абсолютная, а столбец относительный или, наоборот, строка относительная, а столбец абсолютный, то ссылка — смешанная.
Переключение между относительными, смешанными и абсолютными ссылками проще всего осуществлять с помощью клавиши F4. В строке формул выделите ссылку, которую необходимо изменить, и нажмите F4. Каждое нажатие F4 переключает тип ссылки в следующей последовательности: абсолютный столбец и абсолютная строка (например, $C$1); относительный столбец и абсолютная строка (C$1); абсолютный столбец и относительная строка ($C1); относительный столбец и относительная строка (C1). Например, если в формуле выбран адрес $A$1 и нажата F4, ссылка становится A$1. Нажав F4 еще раз, получаем $A1 и т. п.
Относительные и смешанные ссылки встречаются только в формулах (которые находятся в ячейках).
Имена ячеек в формулах при копировании ведут себя как абсолютные ссылки.
2. Функции
В Excel имеется 12 категорий функций (математические, статистические, дата и время, финансовые, логические (если!), ссылки и массивы, текстовые, мат. и тригонометрия,...). Кроме того, имеются категории 10 недавно использовавшихся и Полный алфавитный перечень.
Функция записывается так: <имя функции>(аргумент1;аргумент2;…).
Аргументы должны иметь допустимый для данной функции тип. Например, аргументом математической функции м. б. только число, а для функции работы с текстом необходимо подавать аргумент-текст.
Аргументом функции м.б. также функция или любое выражение, вырабатывающее значение допустимого типа.
Привести пример функции «ЕСЛИ»:
ЕСЛИ(2>1;«да»; «нет») (= «да»)
ЕСЛИ(1>2;«да»; «нет») (= «нет»)
ЕСЛИ(А1>1;1;А1) (=1 или содержимому ячейки А1 в зависимости от содержимого ячейки А1)
В случае аргумента-функции имеем дело с вложенными функциями. Допускается до 7 уровней вложенности функций.
Проще всего функции вводить с помощью мастера функций. Он вызывается нажатием по кнопке fx (или по команде Вставка|Функция...). Его работа состоит из двух шагов. На первом шаге необходимо указать мастеру какая функция будет вводиться (здесь возможен поиск в списках по категориям или в общем списке).
На втором шаге необходимо указать аргументы функции. Аргументами функции, вообще говоря, являются выражения, такие же, как и в формуле. Поэтому набор аргументов функции схож со вводом формулы.
(Привести пример с надбавкой за стаж — вложенные функции ЕСЛИ, и/или с «рублевыми» падежами — разнообразные вложенные функции)
1. Форматирование данных, обрамление
Задание форматов обеспечивается командой Формат|Ячейки…, которая вызывает диалоговое окно “Формат ячеек” с 6 вкладками “Число”, “Выравнивание”, “Шрифт”, “Рамка”, “Вид”, “Защита”.
Во вкладке “Число” можно задать выделенным ячейкам один из форматов, которые перечислены в поле Числовые форматы, а затем установите необходимые параметры. В поле Образец отображается вид выделенных ячеек с учетом выбранных параметров форматирования. Чтобы создать собственный числовой формат, выберите значение (все форматы).
Вкладка “Выравнивание” используется для выравнивания содержимого ячейки по горизонтали и по вертикали, для изменения ориентации текста на вертикальную (в Excel 97 и 2000 текст можно располагать под произвольным углом к горизонтали). Важное значение имеет флажок “Переносить по словам”. Установка этого флажка позволяет переносить текст на новую строку при достижении правого края ячейки. Количество строк зависит от ширины столбца и длины содержимого ячейки.
Вкладка “Шрифт” позволяет выбрать гарнитуру, начертание и размер, цвет и другие параметры шрифта для выделенного фрагмента текста. В частности, можно выбрать установку верхнего и нижнего индекса.
Вкладка “Рамка” позволяет задать параметры обрамления для выделенного диапазона ячеек: тип, толщину и цвет линий обрамления.
Вкладка “Вид” позволяет задать параметры заливки для выделенного диапазона ячеек: цвет и узор фона.
Вкладка “Защита” позволяет запретить изменение, перемещение и удаление выбранных ячеек. Защита ячеек не действует, если не включена защита листа. Чтобы защитить лист, используйте команду “Защита” из меню “Сервис”, затем выберите команду “Защитить лист” и установите флажок “содержимое”.
Для форматирования целиком таблицы можно выполнить команду Формат|Автоформат…и в диалоговом окне “Автоформат” выбрать подходящий образец для форматирования таблицы.
2. Сохранение рабочей книги на диске, печать
Сохранение рабочей книги на диске производится так же как и в Wordе, только расширение к имени файла будет xls.
Особенностью настройки в Excel параметров страницы является то, что во вкладке “Лист” необходимо установить флажки “сетка” и “заголовки строк и столбцов”, если мы хотим увидеть на распечатанном рабочем листе координаты ячеек.
2.1. Задание параметров страницы
Настроить параметры, влияющие на внешний вид документа Excel, позволяет команда Файл|Параметры страницы…. Появляющееся по этой команде диалоговое окно Параметры страницы имеет четыре вкладки Страница, Поля, Колонтитулы, Лист. Используя элементы пользовательского графического интерфейса (командные кнопки, флажки, переключатели, счетчики, раскрывающиеся списки и т. д.), имеющиеся на этих вкладках, можно установить размер бумаги (А4, А5 и др.), ориентацию страницы (книжная или альбомная), размер полей (левое, правое, верхнее, нижнее), сформировать верхний и нижний колонтитулы, центрировать данные на странице по горизонтали и по вертикали и т. д.
Чтобы увидеть участок страницы на рабочем листе Excel, после установки параметров страницы выполните команду Сервис|Параметры... и в появившемся диалоговом окне Параметры во вкладке Вид установите флажок Авторазбиение на страницы.
3. ДИАГРАММЫ
Для построения диаграмм в Excel имеется мастер диаграмм. Мастеру необходимо подать следующую информацию:
- о типе диаграммы;
- об источнике данных для диаграммы;
- о параметрах диаграммы;
- о месте размещения диаграммы.
В Excel 2000 эти 4 вида требуемой информации подаются мастеру диаграмм на соответствующих четырех шагах (диалоговых окнах) работы мастера диаграмм.
В Excel 7.0 информация о месте размещения диаграммы определяется перед вызовом мастера диаграмм, информация об источнике данных для диаграммы подается на первом шаге, информация о типе диаграммы подается на втором и третьем шагах, информация о параметрах диаграммы подается на четвертом и пятом шагах работы мастера.
В большинстве случаев, при правильном использовании мастера диаграмм, полученную с ее помощью диаграмму не нужно редактировать, т. е. диаграмма сразу получается такой, какая требуется. При этом важно, чтобы была правильно указана информация об источнике данных для диаграммы.
Примечание. Здесь предполагается, что диаграмма строится на основе данных, располагающихся на одном рабочем листе в одной книге. В общем случае данные для диаграммы могут выбираться из разных рабочих листов и даже из разных рабочих книг.
Обычно диапазон ячеек таблицы, который содержит исходные данные для диаграммы и подается мастеру диаграмм, состоит из трех частей:
- Ряды числовых данных, располагающиеся вдоль строк или столбцов. Рядов данных может быть один или несколько. Если их несколько, то все они должны содержать одинаковое количество элементов (чисел). Ряды данных могут располагаться смежно и в этом случае они располагаются в прямоугольном блоке ячеек. Если ряды данных располагаются несмежно, то при их мысленно параллельном переносе так, чтобы они стали смежными, эти ряды также имеют прямоугольную форму.
- Заголовки строк и столбцов прямоугольного блока ячеек с рядами данных. Если ряд данных один, и он располагается вдоль столбца, то будет один заголовок столбца и столько заголовков строк, сколько чисел содержится в этом единственном ряде данных.
- Угловая ячейка, в которой пересекаются заголовки строк и столбцов рядов данных.
Исходные данные для диаграммы обязательно должны содержать числовые ряды данных: нет чисел — нет диаграммы!
На рис. 1 приведена простая таблица с данными о продажах изделий А и Б четырьмя подразделениями некоторой фирмы. В этой таблице серым цветом выделен диапазон ячеек А3:С7, являющийся источником данных для объемной и плоской гистограмм, приведенных на рис. 4 и 5. Здесь два ряда данных В4:В7 и С4:С7, располагающиеся вдоль столбцов. Они выделены темно-серым цветом. Заголовками строк являются ячейки диапазона А4:А7, заголовками столбцов — В3:С3. В данном случае заголовки столбцов являются наименованиями рядов данных и называются легендами. Заголовки строк в этом случае называются метками категорий.
В диапазон исходных данных включена также угловая ячейка А3. Ее содержимое, вообще говоря, безразлично. Она включается в диапазон исходных данных для того, чтобы этот диапазон представлял собой прямоугольный блок (после возможного перемещения его частей вертикально и горизонтально).
На рис. 2 в таблице выделен диапазон ячеек (А3:А7; D3:D7), являющийся источником данных для круговой диаграммы, приведенной на рис. 6. В круговых диаграммах отображается один ряд данных (здесь это ряд данных D4:D7).
На рис. 3 в таблице выделен диапазон ячеек (А3:С7; F3:F7), являющийся источником данных для смешанной диаграммы, приведенной на рис. 7. Здесь три ряда данных В4:В7, С4:С7 и F4:F7, располагающиеся вдоль столбцов. В виде смешанных диаграмм удобно отображать ряды данных, числовые значения которых отличаются слишком значительно (на порядок и более) и/или имеют разную размерность. В данном случае все числа из ряда данных F4:F7 отличаются от рядов В4:В7 и С4:С7 и по значению, и по размерности.
Общий метод редактирования диаграмм заключается в том, что щелчком правой кнопки мыши на требующем редактирования элементе диаграммы вызывается контекстное меню, в котором всегда имеется команда вида «Формат <название элемента диаграммы>…». После выбора этой команды (щелчком по ней левой кнопкой мыши) появляется диалоговое окно (признаком того, что появится диалоговое окно, является многоточие после названия команды), которое позволяет отредактировать (отформатировать) выбранный элемент диаграммы.
Переместить различные надписи (заголовок диаграммы, легенды, названия осей и т. д.), область построения диаграммы, а также диаграмму целиком можно выделив щелчком мыши соответствующий элемент (объект) и применив операцию буксировки (drag&drop).
| | A | B | C | D | E | F |
| 1 | Продажи изделий подразделениями фирмы | |||||
| 2 | | Объемы продаж (млн. руб.) | | | ||
| 3 | Подразделение | Изделие А | Изделие Б | Всего | Доля изделия А в подразделении, % | Доля подразделения в фирме, % |
| 4 | Север | 100 | 200 | 300 | 33,3 | 21,4 |
| 5 | Юг | 250 | 150 | 400 | 62,5 | 28,6 |
| 6 | Запад | 200 | 50 | 250 | 80,0 | 17,9 |
| 7 | Восток | 150 | 300 | 450 | 33,3 | 32,1 |
| 8 | Итого | 700 | 700 | 1400 | 50,0 | 100,0 |
Рис. 1. Исходные данные для объемной (рис. 4) и плоской (рис. 5) гистограмм
| | A | B | C | D | E | F |
| 1 | Продажи изделий подразделениями фирмы | |||||
| 2 | | Объемы продаж (млн. руб.) | | | ||
| 3 | Подразделение | Изделие А | Изделие Б | Всего | Доля изделия А в подразделении, % | Доля подразделения в фирме, % |
| 4 | Север | 100 | 200 | 300 | 33,3 | 21,4 |
| 5 | Юг | 250 | 150 | 400 | 62,5 | 28,6 |
| 6 | Запад | 200 | 50 | 250 | 80,0 | 17,9 |
| 7 | Восток | 150 | 300 | 450 | 33,3 | 32,1 |
| 8 | Итого | 700 | 700 | 1400 | 50,0 | 100,0 |
Рис. 2. Исходные данные для круговой (рис. 6) диаграммы
| | A | B | C | D | E | F |
| 1 | Продажи изделий подразделениями фирмы | |||||
| 2 | | Объемы продаж (млн. руб.) | | | ||
| 3 | Подразделение | Изделие А | Изделие Б | Всего | Доля изделия А в подразделении, % | Доля подразделения в фирме, % |
| 4 | Север | 100 | 200 | 300 | 33,3 | 21,4 |
| 5 | Юг | 250 | 150 | 400 | 62,5 | 28,6 |
| 6 | Запад | 200 | 50 | 250 | 80,0 | 17,9 |
| 7 | Восток | 150 | 300 | 450 | 33,3 | 32,1 |
| 8 | Итого | 700 | 700 | 1400 | 50,0 | 100,0 |
Рис. 3. Исходные данные для смешанной (рис. 7) диаграммы
 |  |
| Рис. 4. Объемная гистограмма | Рис. 5. Плоская гистограмма |
 |  |
| Рис. 6. Круговая диаграмма | Рис. 7. Смешанная диаграмма |
4. Работа с таблицей базы данных (сортировка, промежуточные итоги, фильтрация, сводные таблицы)
Использовать Microsoft Excel для создания БД целесообразно потому, что структура в виде строк и столбцов позволяет вводить и хранить информацию так, как она обычно вводится и хранится в так называемых реляционных базах данных.
Реляционные БД в настоящее время наиболее распространены и фактически являются промышленным стандартом. Теория реляционных БД была разработана в начале 70-х годов Коддом на основе математической теории отношений (широко известна его статья: Codd E.F. A Relational Model for Data for Large Shared Data Banks. Communications of the ACM, 13, 1970. –p.377-378).
В реляционной БД все данные хранятся в виде прямоугольных таблиц, каждая из которых содержит, как правило, список – список товаров, деталей, поставщиков и т.д. Все операции над БД сводятся к манипулированию этими таблицами.
Здесь необходимы некоторые терминологические уточнения. Дело в том, что в Excel применяют термин база данных для реляционных БД, которые состоят только из одной таблицы. Обычно же реляционные БД состоят из нескольких взаимосвязанных таблиц. Структура этих связей представляется так называемой схемой данных, разработка которой на этапе проектирования БД является достаточно сложной задачей. Точнее было бы поэтому вместо база данных использовать термин таблица базы данных. Отметим, что в Excel часто используется также термин список для обозначения этой БД, состоящей из единственной таблицы. Мы будем использовать любой из перечисленных трех терминов (база данных, таблица базы данных, список) для обозначения одного и того же объекта.
Элемент таблицы базы данных называется записью и занимает отдельную строку. Часть записи, вводимая в отдельный столбец, называется полем.
Иногда вместо запись употребляют строка ТБД, вместо поле – столбец ТБД.
Таким образом, существует прямая связь между строками и столбцами таблицы Excel, с одной стороны, и записями и полями базы данных – с другой. В первой строке любой таблицы БД должны быть указаны имена полей, представляющие собой заголовки столбцов таблицы БД.
Один рабочий лист Excel может содержать несколько таблиц БД, но в этом случае необходимо эти ТБД разделять пустой строкой или столбцом, чтобы Excel автоматически распознавал отдельные ТБД.
Максимальный размер таблицы БД, которую можно создать в Excel 7.0, - 256 полей и 16383 (в Excel 97 – 65535) записей (одна строка отводится под имена полей).
Имена полей в таблице базы данных д.б. уникальными (ими не м.б. числа, логические значения, формулы, пустые и ошибочные значения).
В Excel имеются следующие средства для манипулирования таблицами БД:
- сортировки;
- промежуточных итогов (использование этого средства, как правило, предваряется сортировкой ТБД);
- фильтрации (автофильтр и расширенный фильтр);
- сводных таблиц (считается наиболее мощным средством манипулирования ТБД в Excel).
Сводная таблица - это плоская или объемная (состоящая из нескольких слоев или страниц) прямоугольная таблица, позволяющая просуммировать или иным образом подытожить большие объемы данных из расположенной в другом месте таблицы базы.