Календарно-тематический план

Кафедра биофизики и математики

ОрГМА

СОДЕРЖАНИЕ

1 Назначение и возможности электронных таблиц. 3

2 Окно Excel. интерактивная Справка. 3

3 Перемещение и выделение в рабочем листе. 4

4 Ввод и редактирование данных. 6

4.1 Общие сведения. 6

4.2 Ввод чисел. 7

4.3 Ввод текста. 7

4.4 Ввод даты и времени. 8

4.5 Ввод последовательностей чисел, дат, текстов или заголовков. 9

4.6 Очистка, удаление и вставка на рабочем листе. 10

5 Использование формул. 11

5.1 Ввод формул. 11

5.2. Использование ссылок. 11

5.3 Ссылки на другие листы той же книги и на листы других книг. 12

5.4 Замена формулы ее значением.. 13

5.5 Присваивание имен ячейкам.. 13

6 Перемещение, копирование данных и формул. 14

7 Массивы.. 15

8 Ошибочные значения. 15

9 Форматирование рабочих листов. 16

9.1 Форматирование чисел. 16

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

9.3 Изменение шрифта, размера, начертания и цвета символов. 19

9.4. Добавление цвета и узора, обрамления, защита листов. 19

9.5. Форматирование столбцов и строк. 19

9.6 Стиль. 20

9.7 Автоформат. 20

9.8 Файлы шаблонов. 21

9.9 Условное форматирование. 21

10 Оформление страницы документа. 22

10.1 Разметка страницы.. 22

10.2 Разрыв страницы.. 22

10.3 Печать документов. 22

11 Работа с базами данных и списками. 23

11.1 Основные понятия. 23

11.2 Фильтрация данных. 24

11.3 Сортировка данных. 28

11.4 Ввод новой информации в список и создание новых списков. 30

11.5 Группировка данных. 31

11.6 Формирование итогов в электронной таблице. 32

11.7 Работа с внешними данными. 33

12 Основные встроенные функции Excel 34

12.1 Основные понятия. 34

12.2 Ввод функций. 36

12.3 Математические функции. 37

12.4 Статистические функции. 39

12.5 Функции дат и времени. 40

12.6 Логические функции. 40

13 Подбор параметра и Поиск решения. 41

14 Диаграммы в Excel 42

14.1 Создание диаграммы.. 42

14.2 Настройка диаграмм.. 45

14.3 Добавление и удаление данных и рядов данных на диаграмму. 46

14.4 Применение линий тренда. 47

СПИСОК ИСПОЛЬЗОВАННОЙ ЛІТЕРАТУРЫ.. 48

1 Назначение и возможности
электронных таблиц

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

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

Документом (то есть объектом обработки) Excel является файл с произвольным именем и расширением.XLS. В терминах Excel такой файл называется рабочей книгой. В каждой рабочей книге может размещаться от 1 до 255 электронных таблиц, каждая из которых называется рабочим листом.

Электронная таблица Excel (книга) состоит из 65536 строк и 256 столбцов. Строки прону­мерованы натуральными числами от 1 до 65536, а столбцы обозначены буквами латин­ского алфавита А, В,..., Z, АВ,..., IV. На пересечении столбца и строки располагается основной структурный элемент таблицы — ячейка. В любую ячейку можно ввести ис­ходные данные – число или текст, – а также формулу для расчета производной информации. Для указания на конкретную ячейку таблицы мы используем адрес, который составляется из обозначения столбца и номера строки, на пересечении которых эта ячейка находится (например, А1, К12, АС6 и. т. д.).

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Для чего предназначены электронные таблицы?

2. Что является главным документом Excel?

3. Что такое ячейка? Из чего состоит ее адрес?

2 Окно Excel. интерактивная Справка

Общий вид окна приложения Excel (совместно с окном документа) показан на рис. 2.1. Окно содержит все стандартные элементы приложений Windows.

Рис. 2.1 – Общий вид окна

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

Ячейка таблицы, окаймленная серой рамкой, является выделенной (текущей). На рис. 2.1 это ячейка А1. Правее поля имени находится небольшая область (ограничен­ная справа знаком равенства), в которой на время ввода данных появляются кнопки управления процессом ввода.

Управление командами Excel осуществляется при помощи горизонтального меню, панелей инструментов, контекстного меню.

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

3 Перемещение и выделение в рабочем листе

Для выделения любой ячейки таблицы достаточно щелкнуть на ней мышью. Кур­сорную рамку также можно перемещать клавишами перемещения {¬}, {®}, {­}, {¯}, {Page Up}, {Page Down}. Для перемещения по рабочему листу можно воспользо­ваться полосами прокрутки. Чтобы мгновенно перейти к заданному элементу таблицы, можно выбрать команду Перейти... меню Правка.

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

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

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

Для выделения блока ячеек с помощью клавиатуры, нажимайте клавиши перемеще­ния, одновременно удерживая нажатой клавишу {Shift}. В качестве альтернативы удержанию клавиши {Shift}, можно нажать клавишу {F8}.

Для выделения группы диапазонов с помощью мыши следует держать нажатой кла­вишу {Ctrl}. Для добавления диапазона можно также использовать комбинацию клавиш {Shift+F8}.

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

· Целый столбец – щелкните мышью на соответствующем номере в заголовке столбца.

· Целую строку – щелкните мышью на соответствующем номере в заголовке стро­ки.

· Всю таблицу – щелкните мышью на левой пустой кнопке в заголовке столбца (рис. 3.1).

Блок – диапазон ячеек, окруженный со всех сторон пустыми ячейками или заго­ловками столбцов и строк. Например, на рис. 3.2 представлены следующие блоки:

АЗ:Е7, G3:H7, A9:E10, G9:H10.

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

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Способы выделения ячеек, диапазонов, блоков.
2. Как выделить столбцы, строки, всю таблицу?

4 Ввод и редактирование данных

4.1 Общие сведения

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

При введении в активную ячейку значения или формулы вводимые символы появ­ляются в самой ячейке и в строке формул.

Сразу после начала ввода данных в строке формул появляются две дополнительные кнопки (рис. 4.1).

Щелчок по кнопке Отмена отменяет ввод. Щелчок по кнопке Enter вводит набранные данные в активную ячейку. Всегда находящаяся в строке формул кнопка Изменить формулу дает возможность получить помощь при создании формул. При ее нажатии ниже строки формул появляется Палитра формул – для версии 2000/2003. На ней по мере ввода ссылок в формулу появляются результаты вычислений.

Завершив ввод данных, вы должны зафиксировать их в ячейке любым их трех спо­собов: нажать клавишу {Enter}, щелкнуть мышью на кнопке или щелкнуть мышью на другой ячейке.

Редактировать содержимое ячейки можно либо в строке формул, либо в самой ячейке.

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

Чтобы редактировать прямо в ячейке следует сделать двойной щелчок по ячейке или, выделив ее, нажать клавишу {F2}.

Для того чтобы Excel правильно различал категории постоянных значений необхо­димо соблюдать основные правила их ввода.

4.2 Ввод чисел

Числовые значения могут содержать только цифры от 0 до 9 и специальные симво­лы: "+","-", ", "Е", "е", "()", "%", "/". По умолчанию после фиксации числа Excel сдви­гает его к правой границе ячейки. Символ "Е" или "е" используется при вводе чисел в экспоненциальном (научном) представлении. Например, Excel интерпретирует 1Е6 как 1000000. Числовые значения, заключенные в круглые скобки. Excel интерпретирует как отрицательные. Для ввода дробных чисел используется по умолчанию десятичная за­пятая.

Хотя вы можете ввести до 255 символов, числовое значение отображается в ячейке не более чем с 15 цифрами. При числе с более чем 15 цифрами Excel использует экспо­ненциальное представление числа.

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

4.3 Ввод текста

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

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

Рис.4.3 – Способы представления текста в ячейках

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

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

Перенос текста в ячейке облегчает чтение длинных текстовых значений. Для пере­носа текста в ячейке следует воспользоваться комбинацией клавиш {Alt+Enter} или выбрать команду Ячейки меню Формат и на вкладке Выравнивание установить флажок Переносить по словам.

Для создания текстового значения, состоящего целиком из чисел, следует ввести перед значением апостроф " ' ".

4.4 Ввод даты и времени

Основной единицей измерения времени в Excel являются сутки. Они представляют­ся целыми числами от 1 до 65380. Базовая дата, представляемая значением 1, — это 1 января 1900 года. Максимальное значение даты 65380 представляет 31 декабря 2078 года. Когда вы вводите дату на рабочем листе, Excel сохраняет ее в виде целого значе­ния, которое равно количеству дней между базовой и заданной датой. Например, 1 октября 2007 года представляется значением 39356.

Время суток — это десятичнаядробь, которая представляет часть суток между их началом (00:00 часов) и заданнымвременем. Например, двенадцать часов дня пред­ставляется значением 0,5. И, такимобразом, абсолютное время 11:46:17 в день 1 октября 2007 года представляется десятичнымзначением 39356,49047.

Таким образом с датой и временем можно произ­водить такие же операции, как и с обычными числами. Хотя Excel хранит даты и время как значения, совсем не обязательно вводить их в таком виде.

Дату можно ввести любым из следующих способов:

1.04.07, 1/4/07, 1-4-07, l-Aпp-07, 1.04.07 17:45.

При вводе даты между 1920 и 2010 гг. можно указывать только две последние циф­ры года. Если же вы вводите дату вне этого диапазона, год нужно записывать полно­стью.

Значение даты отображается в ячейках в соответствующем формате, а в строке формул — в формате ДЦ.ММ.ГГГГ.

Время можно ввести любым из следующих способов:

17:45, 17:45:30, 1.04.07 17:45.

Значение времени отображается в ячейках в соответствующем формате, а в строке формул — в формате чч:мм:сс.

4.5 Ввод последовательностей чисел, дат, текстов или заголовков

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

Маркер заполнения — это маленький квадрат в правом нижнем углу выделения

Чтобы создать последовательность, возрастающую с постоянным шагом, следует ввести в две соседние ячейки первые два значения последовательности. Excel исполь­зует эти два значения для определения шага и исходного значения последовательности. Затем, выделив эти две ячейки, перетащить маркер заполнения вниз или направо до конца области, которую должна заполнить последовательность. Курсор мыши приоб­ретает при этом форму черного креста. Если нужно заполнить диапазон ячеек одинако­выми значениями, то следует удерживать клавишу {Ctrl} при перетаскивании маркера заполнения.

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

Второй способ использует команду Заполнить меню Правка и выбрать вкладку Прогрессия... и дает возможность создать много разных типов последовательностей (рис. 4.4). В этом случае заполняется первая ячейка и выделяется диапазон ячеек, ко­торый нужно заполнить.

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

На рис. 4.5 показаны примеры последовательностей, введенные отмеченными способами.

Рис. 4.5 – Примеры последовательностей

4.6 Очистка, удаление и вставка на рабочем листе

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

Самый быстрый способ очистки содержимого выделенной ячейки – нажатие кла­виши {Delete}. Удаляются только значения и формулы, а форматы и примечания оста­ются.

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

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

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Что может содержать ячейка таблицы?
2. Как ввести данные в ячейку, отредактировать, удалить?
3. Как вводятся числа, текст, дата и время?
4. Способы ввода последовательностей чисел, дат и т.д.
5. Очистка, удаление и вставка на рабочем листе.

5 Использование формул

5.1 Ввод формул

Формулу можно ввести как в строке формул, так и непосредственно в ячейке. Все формулы в Excel должны начинаться со знака равенства. Символы, введенные без знака равенства. Excel рассматривает как текст или как число. Например, если мы вве­дем в ячейку =10+5 и нажмем клавишу {Enter}, то в ячейке появится значение 15. Если же ввести в ячейку 10+5, то Excel рассмотрит это выражение как текст. Формула не должна содержать пробелов.

Формула может содержать: текст, числа, адреса ячеек, знаки арифметический операций и встроенные функции.

Для ввода формулы непосредственно в ячейку, следует сделать по ней двойной щелчок или нажать клавишу {F2}. Чтобы ввести формулу в строку формул необходимо ввести знак равенства =

По завершении ввода фор­мулы следует нажать клавишу {Enter} или щелкнуть по кнопке строке формул. Если нажать клавишу {Esc} или щелкнуть по кнопке в строке формул, то ввод фор­мулы будет отменен.

Обработка операций в Excel происходит по твердой системе правил:

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

· Возведение в степень ^ выполняется раньше умножения * и деления /, которые, в свою очередь, выполняются раньше сложения + и вычитания -.

· Операторы с одинаковым приоритетом выполняются слева направо.

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

5.2. Использование ссылок

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

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

По умолчанию для указания адресов ячеек в Excel применяются относительные ссылки (например, А1). Это означает, что ссылки на ячейки изменяются при копирова­нии формулы на новое место. Относительная ссылка указывает на ячейку (например, А1), основываясь на ее положении относительно ячейки, в которой находится формула (на­пример, A3). Это похоже на объяснение местонахождения ячейки, отталкиваясь от рас­положения активной ячейки.

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

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

Смешанная ссылка содержит как абсолютные, так и относительные ссылки. Напри­мер, ссылка $А1 предохраняет столбец от изменения, в то время как строка изменяется по отношению к новому копируемому положению.

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

Чтобы сослаться на диапазон ячеек, можно указать через двоеточие адреса началь­ной и конечной ячейки в диапазоне, например А7:Е7 или В2:В8. Диапазоном можно обозначить и блок ячеек. Например, для ссылки на блок, показанный на рис. 3.2, сле­дует написать В2:В7.

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

5.3 Ссылки на другие листы той же книги и на листы других книг

Можно ссылаться на другие листы той же рабочей книги. Например, чтобы ввести в ячейку A3 листа Лист1 ссылку на ячейку А1 листа Лист2, следует выполнить следую­щие действия:

· Выделить ячейку A3 на листе Лист1 и ввести знак равенства.

· Щелкнуть на ячейке А1 и нажать {Enter}. После нажатия {Enter} снова будет акти­визирован лист Лист1 и в ячейке A3 появится формула =Лист2!А1. Вводить знаки арифметических операций нужно при активизированном листе Лист2.

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

Аналогичным образом вы можете ссылаться на ячейки, находящиеся в другой книге. Такие ссылки называются внешними. Например, ссылка на ячейку А1 листа Лист1 скрытой книги Книга2 имеет следующий вид: =[Книга2]Лист1!$А$1. Если книга Книга2 будет закрыта, то в ссылке на ячейку, принадлежащую этой книге, будет указан полный путь к книге Книга2. По умолчанию при создании ссылки на ячейки, принадлежащие другой рабочей книге, Excel вставляет абсолютную ссылку.

5.4 Замена формулы ее значением

Чтобы заменить формулу ее значением нужно выделить ячейку с формулой, щелк­нуть в строке формул и нажать клавишу {F9} либо использовать команду Специальная вставка меню Правка. Для этого сначала следует выбрать команду Копировать меню Правка. Затем выбрать команду Специальная вставка меню Правка. В окне диалога установить переключатель Вставить в положение Значения и дважды нажать клавишу {Enter}.

5.5 Присваивание имен ячейкам

В Excel предусмотрен очень удобный способ ссылки на ячейку с помощью при­своения этой ячейки произвольного имени. Чтобы присвоить ячейке имя, следует вы­делить ее и щелкнуть по полю имени в строке формул или выбрать команды Имя, Присвоить... меню Вставка, (рис. 5.1). Ввести имя, нажать Добавить, затем ОК.

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

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

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Способы ввода формул. Что может содержать формула?
2. Какие бывают ссылки, как вставить ссылку в формулу?
3. Как присвоить имя ячейке?

6 Перемещение, копирование данных и формул

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

Для перемещения (копирования) выделенного фрагмента можно воспользоваться любым из указанных способов:

1. Передвиньте указатель мыши так, чтобы он располагался поверх выделения. Указатель мыши превратится в стрелку. Удерживая нажатой левую кнопку мыши, перетащите указатель мыши и серый контур выделенной области в новое положение. При копировании удерживайте нажатой клавишу {Ctrl}. При этом рядом с указателем мыши появится знак (+). Когда серый контур окажется в нужном месте, отпустите кнопку мыши.

2. Выберите любым способом команду Вырезать (для перемещения) или Копировать (для копирования) для запоминания выделения в буфер обмена. Выделите левую верхнюю ячейку диапазона для вставки вырезанных или копируемых ячеек. Затем вставьте из буфера обмена любым способом.

Команда Вставить вставляет из перемещаемых или копируемых ячеек все: значения, форматы и примечания. Чтобы вставить только определенные элементы, следует использовать команду Специальная вставка.

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

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

На рис. 6.1 показан рабочий лист оценки стоимости. Стандартная формула для исчисления результата перемножения столбцов А и В выглядит как =А2*В2. Она вводится в ячейку С2. Затем следует снова выделить ячейку С2 и установить указатель мыши на маркер заполнения. Указатель примет форму черного крестика. Далее нажимаем левую кнопку мыши и смещаем указатель вниз по вертикали так, чтобы диапазон ячеек СЗ:С7 был выделен пунктирной рамкой. Отпускаем кнопку мыши.

Excel скопирует формулу =А2*В2 в ячейки СЗ:С7, причем номера строк будут изменены автоматически на 3, 4, 5, 6 и 7. Например, в ячейке С7 мы получим формулу А7*В7.

Рис. 6.1 – Копирование формул маркером заполнения

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Способы копирования и перемещения данных.
2. Как скопировать готовую формулу в смежные ячейки. Что при этом происходит с разными адресами, входящих в формулу?

7 Массивы

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

На рис. 7.1 показан уже рассмотренный нами на рис. 6.1 рабочий лист оценки стоимости. Вместо ввода формулы (=А2*В2) и ее последующего распространения можно выделить диапазон, который будет заключать в себе формулу массива – С2:С7. Ввести формулу: =А2:А7*В2:В7. Чтобы ввести формулу как массив, следует нажать комбинацию клавиш {Ctrl+Shift+Enter}. Обратите внимание, что выделенному диапазону С2:С7 соответствует формула, показанная в строке формул. Она заключена в фигурные скобки.

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

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Что такое массив?
2. Как ввести формулу, как массив?

8 Ошибочные значения

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

Ошибочное значение

Описание

#ДЕЛ0 #ИМЯ? #ЗНАЧ! #ССЫЛКА #н/д #ЧИСЛО! #ПУСТО!

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

9 Форматирование рабочих листов

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

9.1 Форматирование чисел

Отображением чисел на экране управляют числовые различные форматы. Например, число 100 будет выглядеть как:

100,00 грн. — в денежном формате;

10000% — в процентном формате;

1,00Е+2 — в экспоненциальном формате.

Для форматирования чисел следует раскрыть вкладку Число (рис. 9.1).

Рис. 9.1 – Вкладка Число в диалоговом окне Формат ячеек

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

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

Также для форматирования чисел могут быть использованы соответствующие кнопки панели инструментов Форматирование: Денежный формат … Процентный формат % Формат с разделителями ... Увеличить разрядность ... Уменьшить разрядность

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

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

Для отображений значений времени, превышающих 24 часа, Excel предоставляет в категории Все форматы команды Ячейки... меню Формат встроенный формат [ч]:мм:сс. Этот формат находится также в категории Время и выглядит как 37:30:55. Предположим, вы хотите определить интервал времени между двумя датами и ввели следующие значения в ячейки Al, A2 и A3: 29.03.07 13:32; 31.03.07 23:59 и =А2-А1. Если применить встроенный формат [ч]: мм: ее к ячейке A3, то значением формулы в этой ячейке будет 58:27:00, то есть промежуток времени между двумя указанными моментами. А если применить к ячейке A3 стандартный формат ч:мм:сс, результат будет равен 10:27:00, то есть разности во времени без учета дат.

Для отображения интервалов времени между двумя датами можно использовать также формат Общий команды Ячейки... меню Формат. В этом случае значением разности будет хранимое десятичное значение в сутках. В рассмотренном выше примере значение формулы в ячейке A3 в формате Общий будет 2,435416667. Excel предоставляет возможность создать собственный пользовательский формат чисел (в окне Тип: категории Все форматы команды Ячейки,., меню Формат).

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

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

· Выравнивать значения или результаты вычисления формул по левому или правому краю или по центру ячеек.

· Выравнивать названия в выделенном диапазоне ячеек, что упрощает размещение заголовка по центру таблицы.

· Заполнить ячейки такими символами как пунктир или знаки равенства, чтобы по­строить из них линии на рабочем листе. Менять ориентацию текста внутри ячейки.

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

Для выполнения этих операций следует раскрыть вкладку Выравнивание ( рис. 9.2).

Также могут быть использованы кнопки панели инструментов Фо рматирование: – Выровнять по левому краю … Выровнять по центру … Выровнять по правому краю … и Объединить и поместить в центре …

9.3 Изменение шрифта, размера, начертания и цвета символов

Следует воспользоваться вкладкой Шрифт вдиалоговом окне Формат ячеек.

Также могут быть использованы кнопки панели инструментов Форматирование: Шрифт.., Размер …, Полужирный, Курсив, Подчеркнутый, Цвет шрифта.

9.4. Добавление цвета и узора, обрамления, защита листов

Следует воспользоваться вкладкой Вид и Граница окна Формат ячеек. Также могут быть использованы кнопки панели инструментов Форматирование: Цвет заливки .. ., Границы —

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

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

9.5. Форматирование столбцов и строк

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

Для изменения ширины столбца с помощью меню следует воспользоваться командой Столбец меню Формат (рис. 9.3).

Иногда при анализе больших рабочих книг необходимо скрыть один или несколько столбцов. Для выполнения этой операции следует выделить столбцы, которые нужно скрыть и воспользоваться командой Столбец, Скрыть меню Формат (рис. 9.3). Чтобы показать скрытый столбец, следует выделить диапазон по обе стороны скрытого столбца и воспользоваться командой Столбец, Отобразить меню Формат (рис. 9.3).

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

9.6 Стиль

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

9.7 Автоформат

Автоформат позволяет применять предустановленные форматы к таблицам. Чтобы изменить автоформат следует выделить диапазон, содержащий таблицу и выбрать команду Автоформат... меню Формат (рис. 9.5).

Рис. 9.5 – Диалоговое окно Автоформат меню Формат

9.8 Файлы шаблонов

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

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

Чтобы использовать файл шаблона, следует выбрать команду Создать меню Файл. Ехсе1 выведет только файлы шаблонов (с расширением.XLT) и откроет рабочую копию выбранного шаблона, оставляя оригинал на диске в неприкосновенности. В дальнейшем при сохранении файла вы можете назначить ему другое имя, к которому Excel автоматически добавит расширение.XLS.

9.9 Условное форматирование

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

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

Рис. 9.6 – Диалоговое окно Условное форматирование меню Формат

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

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Способы форматирования данных в ячейках, строках, столбцах.
2. Как можно задать шрифтовое, цветовое оформление ячеек?
3. Зачем применяется условное форматирование? Как его задать?

10 Оформление страницы документа

10.1 Разметка страницы

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

10.2 Разрыв страницы

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

10.3 Печать документов

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

11 Работа с базами данных и списками

11.1 Основные понятия

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

Изучение списков проводим на основе таблицы, содержащей информацию об основных водохранилищах Украины и показанной на рис. 11.1.

Область таблицы A2:F13 можно рассматривать как список. Столбцы А, В, С, D, Е, F этой таблицы называются полями, а строки 4-13 называются записями. Область А2:F3 содержит имена полей. На структуру списка накладываются следующие ограничения:

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

Рис. 11.1 – Таблица со списком водохранилищ Украины

Правила создания списков

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

2) Проектировать список следует так, что­бы все строки содержали однородные объекты в одном столбце.

3) Необходимо отделять список от других данных рабочего листа хотя бы одним незапол­ненным столбцом или строкой. Это поможет Excel автоматически выделить список при вы­полнении фильтрации или сортировки данных. Чтобы отделить подписи от данных, следует использовать не пустые строки, а границы ячеек.

4) Имена столбцов должны размещаться в первой строке списка. Excel использует эти имена при создании отчетов, поиске и сорти­ровке данных. Шрифт, выравнивание, формат, граница и формат прописных и строчных букв, установленные для заголовков столбцов списка, должны отличаться от формата, который уста­новлен рядам данных.

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

6) Дополнительные пропуски в начале и конце ячейки влияют на поиск и сортировку. Вместо ввода пропусков рекомендуется исполь­зовать сдвиг текста в середине ячейки.

Основная функция любой базы данных – поиск информации по определенным критериям. С увеличением количества записей поиск определенной информации затрудняется. Excel позволяет упростить этот процесс путем фильтрации данных.

11.2 Фильтрация данных

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

При фильтрации строки, которые не нужно отображать, временно скрываются.

Строки, которые отобраны при фильтрации в Microsoft Excel, можно редактировать, форма­тировать и выводить на печать, а также созда­вать на их основе диаграммы, не изменяя поря­док строк и не перемещая их. Microsoft Excel предлагает две команды для фильтрации спис­ков: автофильтр и расширенный фильтр.

Команды Фильтр меню Данные позволяют фильтровать (выделять) нужные записи. Фильтрация возможна через автоматический фильтр Автофильтр и через Расширений – ручной.

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

При использовании Автофильтра необходимо переместить курсор в область, содержащую бузу данных. Затем нужно выполнить команду Фильтр, Автофильтр меню Данные. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки можно задавать критерии (условия фильтрации) фильтрации (рис. 11.2, табл.11.1).

Рис.11.2 – Критерии фильтрации списка после выполнения
команды Автофильтр

В появившемся подменю пункт Первые 10... позволяет найти заданное число наибольших или наименьших элементов в списке. Пункт Все отключает фильтрацию, а пункт Условие... вызывает диалоговое окно, в котором можно установить параметры фильтрации. Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ (рис. 11.3).

Таблица 11.1

Условие отбора Автофильтра	Порядок выполнения
Отбор наименьшего или наибольшего значения (отбор наибольших или наименьших чисел в процентном соотношении)	1) В столбце, содержащем числа, нажать на кнопку со стрел­кой и выбрать вариант (Первые 10...) 2) В среднем поле выбрать вариант наибольших или наименьших. 3) В поле слева ввести количество. 4) В поле справа выбрать вариант элементов списка (% от коли­чества элементов)
Отбор строк списка, которые содержат определенный текст, по их началу или концу	1. В столбце, содержащем числа, нажать на кнопку со стрел­кой и выбрать вариант (Условие...). 2. В поле слева выбрать вариант равно, не равно, содержит или не содержит, начинается с, не начинается с, заканчивается на, не за­канчивается на. 3. В поле справа ввести искомый текст. 4. Чтобы найти текстовые строки, несколько последовательностей определенных знаков, необходимо использовать подстановоч­ные знаки. Чтобы создать дополнительное условие, следует выбрать ва­риант и или или и повторить предыдущий шаг
Отбор пустых ячеек или ячеек со значениями	В столбце, содержащем числа, нажать на кнопку со стрелкой и выбрать вариант (Пустые) или (Непустые)
Отбор чисел, которые больше или меньше, чем указанное значение, равны или не равны ему	1. В столбце, содержащем числа, нажать на кнопку со стрелкой и выбрать вариант (Условие...). 1. В поле слева выбрать условие больше, меньше, больше или равно или меньше или равно, равно или не равно. 2. В поле справа ввести значение. 3. Чтобы создать дополнительное условие, надо выбрать вариант и или или и повторить предыдущий шаг

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

Команда Расширенный фильтр, в отличие от команды Автофильтр, позволяет:

· Задавать условия, соединенные логическим оператором ИЛИ, для нескольких столбцов. Вы также можете использовать Расширенный фильтр для задания условий, соединенных логическим оператором И, но в этом случая проще дважды использовать команду Автофильтр.

· Задавать три и более условий для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ. Например, можно вывести на экран студентов, чьи фамилии начинаются с букв А, Д или П.

· Задавать вычисляемые условия.

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

При использовании Расширенного фильтра... необходимо в диалоговом меню оп­ределить (указать) три области (рис. 11.4 и 11.1):

· Исходный диапазон — область списка (A2:F13).

· Диапазон условий — область, в которой задаются критерии фильтрации (A15:F17). Диапазон условий можно поместить выше или ниже списка. Если вы предполагаете, что список со временем будет расширяться, то диапазон условий следует разместить выше списка. Диапазон условий должен содержать, по крайней мере, две строки. Вводите один или несколько заголовков столбцов в верхней строке, а условия отбора — во вторую и последующую строки. Заголовки в диапазоне условий должны точно совпадать с заголовками столбцов в списке. В диапазоне условий можно ввести любое количество условий. Excel интерпретирует их в соответствии со следующими правилами:

· условия на одной строке считаются соединенными логическим оператором И;

· условия на разных строках считаются соединенными логическим оператором ИЛИ.

· Поместить результат в диапазон — область, в которой будут появляться воз­можные результаты фильтрации (А19:А24). Чтобы задать этот диапазон, просто щелкните на пустой ячейке в рабочем листе, отметив тем самым левый верхний угол конечного диапазона.

· Флажок Только уникальные записи в окне диалога Расширенный фильтр скрывает теряющиеся строки.

Правила ввода для числовых условий очевидны. Правила для задания текстовых условий представлены ниже:

Единственная буква означает: "Найти все значения, которые начинаются с этой буквы".

Символ > или < означает: "Найти все значения, которые находятся по алфавиту соответственно после или до введенного текстового значения". Формула ="=текст" означает: "Найти значения, которые точно совпадают со строкой символов текст". Пример: задайте ="=Попов", чтобы найти строки, содержащие только фамилию Попов. Если задать Попов без формулы, Excel отберет строки, содержащие фамилии Попов, Попова, Попович и т. п.

Результаты фильтрации с использованием расширенного фильтра показаны на рис. 11.5.

Рuc. 11.5 – Результаты фильтрации с использованием
расширенного фильтра

11.3 Сортировка данных

Процесс упорядочения записей в базе дан­ных называется сортировкой.

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

Команды Сортировка... меню Данные позволяют упорядочивать (сортировать) список. Для выполнения сортировки предварительно необходимо выделить область списка или поместить в нее курсор. В результате выполнения команды Сортировка... появится диалоговое окно, показанное на рис. 11.6, в котором следует выбрать название поля, по которому нужно производить сортировку, и указать метод сортировки. Если мы хотим, чтобы в каждой отсортированной группе строки располагались в определенной последовательности, т.е. для сортировки строк по двум или трем полям, то в диалоговом окне Сортировка... следует указать столбцы сортировки в полях Сортировать по и затем воспользоваться раскрывающимися списками Затем по и В последнюю очередь, по.

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

Для сортировки строк по месяцам и дням недели необходимо выбрать ячейку, в меню Данные, Сортировка… указать столбец сортиров­ки в поле Сортировать по и нажать на кнопку Параметры (рис. 11.6). В поле Порядок сорти­ровки по первому ключу выбрать необходимый порядок.

Рис. 11.6

Для сортировки по столбцам в диалоговом окне Параметры в поле Сортировать выбрать пе­реключатель Столбцы диапазона Рис. 11.7.

Рис. 11.7

11.4 Ввод новой информации в список и создание новых списков

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

Для создания нового списка можно воспользоваться Мастером шаблонов... из меню Данные.

Заполнение базы данных данными

1)

После форматирования заголовков столбцов (полей) приступаем к заполнению 1-й строки (1-й записи). Вводим исходные данные и в вычисляемых полях записываем расчетные формулы.

2)

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

3) Щелкая на клавише «Добавить» в окне диалога и последовательно заполняя пустые поля исходными данными, создаем исходную базу данных.

11.5 Группировка данных

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

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

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

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

2. Выбрать команду Группа и структура в меню Данные, а затем команду Создание струк­туры.

Структура может иметь до восьми уровней детализации, каждый из которых предоставля­ет подробную информацию для предыдущего уровня. Для отображения только строк на опре­деленном уровне достаточно щелкнуть по номе­ру уровня, который нужно просмотреть. Если строки скрытые, можно щелкнуть по символу +1 для их отображения.

11.6 Формирование итогов в электронной таблице

Создание отчета сводной таблицы

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

1. Выбрать команду Сводная таблица в меню Данные.

2. Установить переключатель Вид создавае­мого отчета в положение Сводная таблица.

3. Указать диапазон, который содержит входные данные.

4. Нажать на кнопку Макет.

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

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

Поля, которые нужно использовать как поля страниц, перетащить в область Страница.

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

Чтобы удалить поле, следует перетащить его за пределы диаграммы.

Получив нужный макет, нужно нажать на кнопку OK, а затем на кнопку Готово.

Расчет промежуточных итогов

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

1. Установить курсор мыши в поле, по ко­торому необходимо рассчитать итоги, и нажать на кнопку Сортировать по возрастанию или Сортировать по убыванию стандартной панели инструментов.

2. В меню Данные выбрать команду Итоги.

3. В поле При каждом изменении в выбрать столбец для подведения итогов.

4. В поле Операция выбрать функцию для вычисления итогов.

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

6. Чтобы за каждым итогом шел разрыв страницы, надо установить флажок Конец стра­ницы между группами.

7. Чтобы итоги отображались над строками данных (а не под ними), следует снять флажок Итоги под данными.

Нажать на кнопку ОК

11.7 Работа с внешними данными

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

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

Единственным недостатком этой простой процедуры является то, что вы не можете фильтровать данные файла.DBF. Поэтому рабочий лист будет содержать все исходные записи. Чтобы импортировать только конкретные записи, а также для создания запроса, который можно выполнять много раз, вместо использования команды Открыть нужно запустить Microsoft Query.

Чтобы запустить Microsoft Query из Excel в меню Данные следует выбрать команды Внешние данные. Создать запрос.

ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ

1. Что такое поле и запись в базе данных в Excel?
2. Какие фильтры можно применить? Параметры фильтров.
3. Как выполняется сортировка данных?

12 Основные встроенные функции Excel

12.1 Основные понятия

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

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции — как, например, СУММ или СРЗНАЧ – описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. Например, в формуле =СУММ(СЗ:С5) СУММ — это имя функции, а СЗ:С5 — ее единственный аргумент. Эта формула суммирует числа в ячейках СЗ, С4 и С5.

Аргумент функции заключается в круглые скобки. Открывающая скобка отмечает начало аргумента и ставится сразу после имени функции без пробела. При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, формула =ПРОИЗВЕД(С1;С2;С5) указывает, что необходимо перемножить числа в ячейках C1, C2 и С5. В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, функция =CУMM(Al:A5;C2:C10;D3:D17) имеет три аргумента, но суммирует числа в 29 ячейках.

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

Комбинацию функций можно использовать для создания выражения, которое Excel сводит к единственному значению и интерпретирует его как аргумент. Например, в формуле
=CУMM(SIN(Al*ПИ());2*COS(A2*ПИ())— это выражения, которые вычисляются в качестве аргументов функции СУММ. В качестве аргументов можно использовать не только ссылки на ячейки и диапазоны, но также числовые, текстовые и логические значения, имена диапазонов и массивы.

12.2 Ввод функций

Функции в рабочем листе можно вводить прямо с клавиатуры, с помощью команды Функция меню Вставка или используя кнопкуна панели инструментов Стандартная. Если вы выделите ячейку и выберете команду Функция меню Вставка или нажмете на кнопку в строке формул, то Excel выведет окно диалога Мастер функций 1 из 2, показанное на рис. 12.1. Если вы хотите иметь под рукой Помощника, нажмите кнопку справки в нижнем левом углу этого окна диалога.