Общий порядок выполнения работ

EXCEL

ДЛЯ

ЮРИСТОВ

 

учебное пособие

 

 

Минск

Издательский центр БГУ

2010

УДК

ББК

 

 

Автор

И. Н. Оджаева

 

Рецензенты:

кандидат физико-математических наук,
заведующий кафедрой естественнонаучных дисциплин
Юридического колледжа БГУ В.П.Толстых;

 

кандидат физико-математических наук, доцент кафедры полупроводников и наноэлектроники
физического факультета БГУ И. А. Карпович

 

Рекомендовано

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

Юридического колледжа БГУ

От 21.05.2009, протокол № 7

 

Excel для юристов: учебное пособие/ И. Н. Оджаева. – Мн.: Изд. центр БГУ, 2010. - с.

 

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

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

Пособие предназначено для учащихся юридических специальностей.

ОГЛАВЛЕНИЕ

 

ВВЕДЕНИЕ.. 5

ОБЩИЙ ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТ.. 7

СОЗДАНИЕ РАБОЧЕЙ КНИГИ. ФАЙЛОВЫЕ ОПЕРАЦИИ. УПРАВЛЕНИЕ ЛИСТАМИ РАБОЧЕЙ КНИГИ 8

Определение и назначение табличного процессора Microsoft Excel 8

Интерфейс программы... 8

Структура таблицы Excel. 12

ЛАБОРАТОРНАЯ РАБОТА № 1. 14

ДОМАШНЕЕ ЗАДАНИЕ № 1. 15

СОЗДАНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ. АВТОЗАПОЛНЕНИЕ.. 16

Ввод данных в таблицу.. 16

Типы данных в Excel. 16

Операции выделения и копирования диапазонов. 17

ЛАБОРАТОРНАЯ РАБОТА № 2. 20

ДОМАШНЕЕ ЗАДАНИЕ № 2. 22

ФОРМАТИРОВАНИЕ ТАБЛИЦ. АВТОФОРМАТИРОВАНИЕ.. 23

Форматирование таблиц.. 23

Форматы данных.. 23

Шаблоны... 25

ЛАБОРАТОРНАЯ РАБОТА № 3. 26

ДОМАШНЕЕ ЗАДАНИЕ №3. 29

РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ.. 29

Операторы, используемые в формулах.. 30

Функции в Excel. 32

Способы адресации и ссылки.. 35

Сообщения об ошибках.. 36

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

ЛАБОРАТОРНАЯ РАБОТА № 4. 38

ДОМАШНЕЕ ЗАДАНИЕ № 4. 42

СОЗДАНИЕ ДИАГРАММ... 43

Диаграммы... 43

ЛАБОРАТОРНАЯ РАБОТА № 5. 46

ДОМАШНЕЕ ЗАДАНИЕ № 5. 50

ПАРАМЕТРЫ СТРАНИЦЫ. ОСОБЕННОСТИ ПЕЧАТИ ДОКУМЕНТОВ В EXCEL 51

Режимы отображения таблиц.. 51

Печать таблиц.. 51

ЛАБОРАТОРНАЯ РАБОТА № 6. 54

ДОМАШНЕЕ ЗАДАНИЕ № 6. 55

СОЗДАНИЕ И ЗАПУСК МАКРОСОВ.. 56

Понятие макроса.. 56

Создание макросов. 56

Удаление макроса.. 57

ЛАБОРАТОРНАЯ РАБОТА № 7. 58

ДОМАШНЕЕ ЗАДАНИЕ № 7. 59

УПРАВЛЕНИЕ БАЗАМИ ДАННЫХ В EXCEL.. 60

Базы данных в Excel. 60

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

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

Вставка итогов. 65

Сводные таблицы... 67

ЛАБОРАТОРНАЯ РАБОТА № 8. 69

ДОМАШНЕЕ ЗАДАНИЕ № 8. 72

СВЯЗЬ МЕЖДУ ПРИЛОЖЕНИЯМИ. ПРИМЕНЕНИЕ ФУНКЦИИ СЛИЯНИЕ 73

Связь между приложениями.. 73

Основной документ. 73

Источник данных.. 74

Функция Слияние. 74

ЛАБОРАТОРНАЯ РАБОТА № 9. 75

ДОМАШНЕЕ ЗАДАНИЕ № 9. 77

ЛИТЕРАТУРА.. 78

ВВЕДЕНИЕ

 

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

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

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

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

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

Учебное пособие предназначено для учащихся ССУЗов и ВУЗов юридических специальностей.

 

Интерфейс программы

После запуска программы Excel на экране появляется окно программы (рис.1), состоящее из следующих перечисленных элементов.

1. Верхней строкой окна программы MS Excel является строка заголовка (рис. 1). Здесь находится название табличного процессора - Microsoft Excel, имя файла – Книга1, пиктограмма системного меню  и три кнопки управления размерами окна (Свернуть, Развернуть, Закрыть) .

 

Рисунок 1. Рабочее окно MS Excel 2003

2. Под строкой заголовка располагается строка основного меню (рис.1). Каждый пункт меню (Файл, Правка, Вид, Вставка, Формат, Сервис, Данные, Окно, Справка) представляет собой объединенную группу команд, реализующих те или иные операции MS Excel. Команды, представленные черным цветом, доступны для использования, а выделенные серым – недоступны.

3. Ниже располагаются панели инструментов, состоящие из кнопок с рисунками.

 

Рисунок 2. Панели инструментов Стандартная и Форматирование

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

 

Рисунок 3. Диалоговое окно Настройка

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

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

 

Рисунок 4. Поле имени и строка формул

 

Поле имени содержит адрес текущей (активной) ячейки. Текущая ячейка (на рис. 1 - ячейка В6) помечена указателем. По умолчанию ввод данных, формул и многие другие действия относятся к текущей ячейке. Указатель ячейки (рамку вокруг ячейки) также называют табличным курсором. Строка формул используется для ввода и редактирования данных или формул в ячейках рабочего листа. В ней всегда отображается содержимое текущей ячейки.

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

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

8. И самая последняя строка окна электронной таблицы - это строка состояния. В ней всегда содержится краткая информация о текущем состоянии данной программы, а также о включении некоторых режимов (Num Lock, Caрs Lock, Scroll Lock). Перед началом работы в строке состояния написано Готово, в процессе работы при выполнении определенных команд здесь появляются краткие комментарии, подсказки или описания производимых операций.

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

Структура таблицы Excel

Документ Excelназываетсярабочей книгой. Рабочая книга - это основной документ, используемый MS Excel для хранения и обработки данных. Рабочая книга состоит из отдельных рабочих листов, каждый из которых представляет собой электронную таблицу. Каждый рабочий лист имеет свое имя, отображающееся на ярлычке листа. В рабочей книге может содержаться до 255 листов. По умолчанию для Microsoft Excel их три. Однако количество рабочих листов в рабочей книге может регулироваться по желанию пользователя. Для этого в диалоговом окне Параметры из меню Сервис на вкладке Общие нужно установить требуемое количество листов (рис. 5). При запуске программы автоматически создается файл Книга1.

 

Рисунок 5. Вкладка Общие диалогового окна Параметры

 

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

Каждая строка и столбец имеют свой заголовок. Существует два стиля ссылок: А1 и R1C1. По умолчанию Excel использует стиль ссылок A1, где столбцы нумеруются латинскими буквами: А, В, С, D,..., АА, АВ,..., а строки числами от 1 до 65536. Таким образом, каждая ячейка имеет свое обозначение, в котором указывается соответствующий столбец и строка и которое используется как адрес ячейки или, иначе говоря, как ссылка на ячейку, например, А1, В5, АА2. Также можно использовать стиль ссылок, в котором как строки, так и столбцы нумеруются числами. В стиле ссылок R1C1 Excel указывает положение ячейки буквой «R», за которой следует номер строки, и буквой «C», за которой идет номер столбца, например R5C3, R76C34. Для того чтобы выйти из режима R1C1, необходимо выполнить команду Сервис/ Параметры/ Общие / убрать флажок Стиль ссылок R1C1 (рис. 5).

ЛАБОРАТОРНАЯ РАБОТА № 1

 

ЦЕЛЬ: изучить интерфейс программы Microsoft Excel и принципы организации данных в табличном процессоре, освоить приемы запуска и завершения работы с табличным процессором, приемы работы по настройке параметров табличного процессора, научиться выполнять файловые операции и управлять листами рабочей книги.

 

СОДЕРЖАНИЕ РАБОТЫ

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Объясните назначение табличного процессора.

2. Что находится в строке заголовка программы?

3. Для чего предназначен маркер разбиения ярлычков?

4. Что означает стиль ссылок R1C1?

5. Как установить количество листов в новой книге 25?

6. Как установить панель инструментов Диаграмма?

7. Как изменить цвет линий сетки?

8. Как отключить строку состояния?

9. Как удалить лист из рабочей книги?

10. Как сохранить рабочую книгу?

ДОМАШНЕЕ ЗАДАНИЕ № 1

1. Создайте рабочую книгу Домашние задания, состоящую из 5 листов.

2. Переименуйте Лист1 в Сведения.

 

 

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

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

Excel позволяет вводить данные, ширина которых превышает ширину ячейки, но, возможно, что такие данные не будут выведены полностью. Длинные строки текста могут переходить на соседние ячейки, если они не заняты. В противном случае эти строки обрываются на границе ячейки. Если число оказывается шире ячейки, то оно выводится либо в показательной форме (например, 1.23Е+12) либо заменяется знаками фунта (#####). Но, даже если данные показываются лишь частично, Excel все равно хранит их и использует в вычислениях полностью. Кроме того, и текст, и числа будут выведены целиком, если увеличить ширину столбца до нужного размера.

Типы данных в Excel

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

Текстовая строка - это любая последовательность введенных в ячейку символов, которая не может быть интерпретирована MS Excel как число, формула, дата, время, логическое значение или значение ошибки. Как правило, текстовые записи состоят из алфавитно-цифровых символов, таких как буквы, цифры и специальные символы. В одну ячейку можно ввести текстовую строку, содержащую до 32000 символов. Если нужно ввести число как текст, например, почтовый индекс или телефон, впереди можно поставить апостроф. Например, 852 является числом, а '852 воспринимается как текст. Можно ввести апостроф и в том случае, если вы хотите ввести число, но не хотите, чтобы MS Excel интерпретировал его как значение, используемое при вычислениях. Текст при вводе выравнивается по левому краю.

Числовые данные (в том числе дата, время, денежные значения и т.д.)Числами в MS Excel считаются постоянные величины, состоящие только из цифр 1234567890 и символов -+/,Ее. Число при вводе выравнивается по правому краю. Можно вводить целые числа (например, 204), десятичные дроби (например, 123,45) и числа в экспоненциальной форме (например, 1,35Е+06 или 5,14Е-08, что равносильно выражениям 1,35*10⁶ или 5,14*10^-8 соответственно). Кроме текста и числа в ячейке рабочего листа может храниться дата или время. При вводе даты или времени MS Excel преобразует это значение в некоторое специальное числовое представление.

Третьим типом данных, которые могут быть введены в ячейки рабочего листа, являются формулы. MS Excel распознает в ячейке формулу, если значение в ячейке начинается со знака равенства (=), знака плюс (+) или знака минус (-). Формула - выражение, начинающееся со знака равенства (=), знака плюс (+) или знака минус  (-), включающее числовые константы, ссылки на ячейки, имена, функции, набор стандартных операторов и специальных символов.

 

ЛАБОРАТОРНАЯ РАБОТА № 2

ЦЕЛЬ: изучить приемы создания и редактирования таблиц с различными типами данных, научиться пользоваться маркером автозаполнения.

СОДЕРЖАНИЕ РАБОТЫ

Редактирование таблиц

3. Отработайте основные операции копирования и перемещения данных:

- переместите таблицу на три строки вниз при помощи мыши;

- верните таблицу в прежнее положение;

- переместите таблицу на один столбец вправо при помощи мыши;

- переместите таблицу в прежнее положение;

- удалите три последних строки таблицы. Отмените удаление;

- скопируйте таблицу на Лист 2 через буфер обмена;

- очистите Лист 2.

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

Образец 1

7. Перед столбцом А вставьте новый столбец. Используя средства автозаполнения, заполните ячейки А4:А9 числами от 1 до 6.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Перечислите типы данных, используемые в Excel.

2. Каким типом данных является дата?

3. Что означает знак ##### в ячейке?

4. Что такое буфер обмена?

5. Как добавить и удалить строки?

6. Как скопировать содержимое ячейки?

7. Что такое автозаполнение?

ДОМАШНЕЕ ЗАДАНИЕ № 2

1. В книге Домашние задания на листе Сведения создайте таблицу по приведенному ниже образцу:

 

 

Форматирование таблиц

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

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

 

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

Форматы данных

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

Microsoft Excel содержит множество встроенных числовых форматов. Чтобы просмотреть их список, нужно выбрать команду Ячейки в меню Формат и перейти на вкладку Число (рис. 8).

 

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

 

Категория Дополнительный формат включает в себя почтовые индексы и телефонные номера. Параметры каждой категории можно посмотреть справа от списка Категории. Форматы объединены в категории: Общий, Числовой, Денежный, Финансовый, Дата, Время, Процентный, Дробный, Текстовый и Экспоненциальный.

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

Шаблоны

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

 

Рисунок 9. Диалоговое окно Шаблоны

 

В Excel также существует возможность создавать и фиксировать в списке шаблонов собственные шаблоны книги и листа. Для этого нужно создать книгу со всеми необходимыми параметрами форматирования и при ее сохранении в диалоговом окне Сохранение документа в поле Тип файла выбрать вариант Шаблон.

ЛАБОРАТОРНАЯ РАБОТА № 3

 

ЦЕЛЬ: изучить различные приемы форматирования таблиц, научиться создавать и редактировать примечания.

СОДЕРЖАНИЕ РАБОТЫ

Вставка примечаний

10. С помощью команды Вставка/ Примечание для ячейки А1 создайте примечание Данные на первое мая 2007 года.

11. Измените размеры области примечания и переместите ее. Оформите примечание следующим образом: установите шрифт Times New Roman, размер шрифта – 12 пунктов, начертание – полужирный, цвет шрифта – красный, цвет заливки поля примечания – светло-коричневый.

12. Удалите примечание, затем отмените операцию удаления.

13. Выполнив команды: Сервис/ Параметры/ Вид переключитесь между режимами отображения примечания.

14. Сравните полученную таблицу с образцом 2.

15. Сохраните изменения в рабочей книге Статистика. xls.

 

 

Образец 2

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Как объединить ячейки?

2. Как установить фиксированную ширину столбцов и строк?

3. Что означает скопировать формат?

4. Как установить процентный формат числа?

5. Как отменить форматирование таблицы?

6. Что такое автоформат таблицы?

7. Что такое закрепление областей?

8. Как отобразить/ скрыть текст примечания?

 

ДОМАШНЕЕ ЗАДАНИЕ №3

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

2. В ячейку А1 добавьте примечание Данные на 01.09.2009.

3. Отформатируйте примечание с параметрами по выбору пользователя.

4. Сохраните изменения в рабочей книге.

 

Функции в Excel

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

Всего в MS Excel используется более 200 функций различных категорий:

- математические,

- тригонометрические,

- инженерные,

- логические,

- финансовые,

- информационные,

- статистические,

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

- даты и времени для обработки числовых значений даты и времени,

- функции ссылок и массивов,

- функции для работы с базами данных,

- функции проверки свойств и значений ячеек рабочего листа.

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

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

Итак, любая функция MS Excel всегда состоит из знака равенства, имени и списка аргументов.

Аргументами функций могут быть числовые и текстовые значения, логические значения, массивы, ссылки на ячейки, значения ошибок, или они могут вовсе отсутствовать для некоторых функций. Например, функция СУММ(А1:А10) суммирует значения диапазона ячеек А1:А10, а функция СЕГОДНЯ() вычисляет текущую дату. Аргументы могут быть как константами, так и формулами. В свою очередь эти формулы могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах MS Excel можно использовать до семи уровней вложенности функций.

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

- предшествующий ей знак равенства  =;

- круглые скобки, в которые заключаются аргументы функции, причем скобки нужны даже в том случае, если для функции не требуется задавать список аргументов, как для  функций СЕГОДНЯ() и ТДАТА();

- отсутствие пробела между именем функции и списком аргументов;

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

Ввод функций можно осуществить несколькими способами:

- набрав с клавиатуры;

- нажатием кнопки  в строке формул;

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

- при помощи инструмента Автосумма  на панели инструментов.

 

Рисунок 10. Диалоговое окно Мастер функций

Способы адресации и ссылки

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

1. Чаще всего в формулах используют относительные ссылки. Относительная ссылка на ячейку А1 выглядит так: А1. Относительные ссылки меняются при копировании формул. Например, если в ячейку A3 ввести формулу =А1+А2, то при копировании ее из ячейки A3 в ячейку ВЗ она преобразуется в формулу =В1+В2.

2. Абсолютные ссылки не меняются при копировании формул из одной ячейки в другую. Абсолютные ссылки на ячейки А1, D8, АА10 выглядят следующим образом: $А$1, $D$8, $АА$10. Например, если в ячейку A3 ввести формулу =А1+$А$2, то при копировании ее из ячейки A3 в ячейку ВЗ она преобразуется в формулу =В1+$А$2, т. е., делая абсолютную ссылку на ячейку, мы ее фиксируем.

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

4. Внутренняя ссылка на данные другого рабочего листа имеет вид ' Имя рабочего листа'!Имя ячейки, например, Лист1!А1 - ссылка на ячейку А1 листа1. Если рабочий лист переименован и его имя содержит пробелы, то в ссылках такое имя заключается в одинарные кавычки (апострофы), например, 'Отчет'!А1 - ссылка на ячейку А1 листа Отчет.

5. Внешняя ссылка на ячейку другой рабочей книги имеет вид '[Имя рабочей книги]Имя рабочего листа!'Абсолютная ссылка на ячейку, например, '[Отчеты.xls] Отчет_январь'!$А$1 - ссылка на ячейку А1 рабочего листа Отчет_январь рабочей книги Oтчеты.xls.

Сообщения об ошибках

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

- #### – ширина ячейки не позволяет отобразить число в заданном формате;

- #ИМЯ? – Excel не смог распознать имя, использованное в формуле;

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

- #ЧИСЛО! – нарушены правила задания операторов, принятые в математике;

- #Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;

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

- #ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;

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

 

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

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

Для добавления условного форматирования нужно выделить ячейку и в меню Формат выбрать команду Условное форматирование. Чтобы в качестве условия форматирования использовать значения ячеек, нужно выбрать параметр значение, выбрать операцию сравнения, а затем ввести заданное значение или формулу (рис. 11). Перед формулой нужно поставить знак равенства (=). Для использования формулы в качестве критерия форматирования, нужно выбрать параметр Формула, а затем ввести формулу (рис. 12).

 

Рисунок 11. Диалоговое окно Условное форматирование (одно условие)

 

Рисунок 12. Диалоговое окно Условное форматирование (два условия)

 

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

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

ЛАБОРАТОРНАЯ РАБОТА № 4

 

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

СОДЕРЖАНИЕ РАБОТЫ

 

Работа с именами диапазонов

10. Присвойте ячейке J 3 имя общее_число_преступлений.

11. Присвойте диапазону ячеек I 3: I 9 имя Преступления_зарегистрированные_в_г_Минске. Удалите имя диапазона.

12. В ячейке К4 рассчитайте значение, используя в формуле присвоенное ячейке J 3 имя.

Ссылки на другие рабочие листы в формулах

15. На Листе 2 создайте итоговую таблицу в соответствии с образцом 3. Поместите в ячейки столбца Итого за январь-апрель 2007 года ссылки на ячейки рабочего листа Статистика, содержащие соответствующие итоговые числа.

 

Итоговая таблица

Образец 3 Использование статистических и логических функций для анализа данных 16. Создайте новую рабочую книгу. 17. Переименуйте первый лист в Успеваемость. Создайте на листе Успеваемость таблицу по образцу 4:   Образец 4 18. Вычислите средний балл сессии для каждого учащегося. 19. При помощи логической функции ЕСЛИ, в новом столбце рассчитайте Повышающий коэффициент, исходя из следующих условий: если средний балл >= 9,0, то повышающий коэффициент. = 1,5; если средний балл >= 8,0, то повышающий коэффициент. = 1,3; если средний балл >= 7,0, то повышающий коэффициент. = 1,1; если средний балл >= 6,0, то повышающий коэффициент. = 1,0; если средний балл < 6,0, то стипендии нет. 20. Добавьте в конце таблицы еще один столбец и для каждого учащегося рассчитайте Кол-во неудовлетворительных оценок, используя статистическую функцию СЧЕТЕСЛИ.