Файл: Информационные технологии-Багиева.docx

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

Механизм отслеживания зависимостей можно задать командой Сервис/Зависимости формул или через панель инструментов Панель зависимостей (рис. 21).

Рисунок 21. Панель зависимостей

Оформление рабочего листа

Параметры форматирования ячейки осуществляются командой Формат/Ячейки. Окно диалога состоит из 6 вкладок:

На вкладке Число выбирают один из встроенных или создают новый числовой формат.
На вкладке Выравнивание – определяют размещение данных внутри ячеек. Здесь возможно выравнивание содержимого ячеек по вертикали и горизонтали; если содержимое ячейки не вмещается в нее по ширине, можно разделить его на несколько строк опцией Переносить по словам (В этом случае расположение текста в ячейке будет зависеть от ширины столбца. Чтобы выполнить независимый, от ширины столбца, перенос слов достаточно установить курсор в месте разрыва текста и воспользоваться комбинацией клавиш [Alt+Enter]). При желании объединить ячейки выбирают опцию Объединить ячейки или кнопку Объединить и поместить в центре на панелиФорматирования. Возможности Ориентации позволяют изменить направление текста в ячейках.
На вкладке Шрифт выбирают вид, размер и др. параметры шрифта.
На вкладке Граница выполняют обрамление вокруг и внутри ячеек.
На вкладке Вид осуществляют вставку узоров и заливку ячейки цветом.
На вкладке Защита определяют защиту ячеек от преднамеренного или случайного изменения их значений. Для защиты ячеек флажок Защищаемая ячейка должен быть установлен. Защита будет установлена только после защиты листа (через опции Сервис/Защита/Защита листа). Модификация вновь будет возможна, если в опции Сервис/Защита выбрать подопцию Снять защиту листа.

Пользовательские числовые форматы

Excel содержит множество встроенных числовых форматов. Для просмотра полного списка форматов, используется команда Формат/Ячейки вкладка Число.

Чтобы установить числовой формат следует:

выделить ячейку(ячейки);
на вкладке Число окна Формат ячеек выбрать категорию устанавливаемого формата;
в списке справа определить дополнительные параметры формата.

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

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

При выборе формата для обозначения цифр используются специальные символы подстановки (шаблоны): #, ? и 0.

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

Правила создания пользовательского числового формата:

Создаваемый пользовательский формат может состоять не более чем из трех секций, в которых заданы шаблоны для отображения чисел и четвертой секции, содержащей шаблон для отображения текста.
Отдельные секции отделяются друг от друга точкой с запятой.
В первой секции следует задать шаблон для отображения положительных значений, во второй – для отрицательных, а в третьей для нулевых значений. В текстовую секцию помещается символ «@» в ту позицию, с которой должен быть введен пользовательский текст.
Если в секциях для отображения числовых значений после кода указывается в кавычках текст, то он будет всегда представлен в ячейке при вводе числового значения.
В зависимости от количества секций в шаблоне создаваемого формата Excel по-разному будет использовать заданные секции.

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

Например, при задании числового формата 0 000 число 9 отображается как 0 009, а в случае выбора формата # #00 - как 09.

Примеры собственных числовых форматов представлены в таблице 7.

Для отображения	Используйте шаблон
1234,59 в виде 1234,6	####,#
8,9 в виде 8,900	#,000
0,631 в виде 0,6	0,#
12 в виде 12,0 и 1234,568 в виде 1234,57	#,0#
44,398, 102,65 и 2,8 с выровненной десятичной запятой	???,???
5,25 в виде 5 ¼ и 5,3 в виде 5 3/10 с выровненными символами делениями	# ???/???

Таблица 7. Шаблоны числовых форматов

При формировании числового формата можно задавать цвет. Чтобы установить Цвет, следует в раздел формата ввести в квадратных скобках название нужного цвета. Код цвета должен быть первым элементом раздела. Если задан формат [Синий]0,00;[Красный]-0,00, положительные значения выделяются синим цветом, а отрицательные – красным. Также можно использовать операторы сравнения, заключенные в квадратные скобки. Например, формат [Синий][<=100];[Красный][>100] отображает числа меньше или равные 100 синим цветом, а числа большие 100 – красным.

В коде формата можно использовать символы-разделители и текст. Если код содержит дефис (00-00), то в данном формате значение 3456 имеет вид 34-56, а 3  00-03.в качестве символа разделения можно использовать косую черту или пробел, но поскольку эти символы являются символами кода, их следует указывать в кавычках, также как и текст (например, 00"/"00). Код #,##"С" нужен для представления значений температуры в градусах Цельсия.

Пользовательский формат, в котором числовые значения отображаются до сотых долей (в том числе и нулевые), отрицательные числа вводятся в скобках и отображаются красным цветом, а текстовая информация начинается со слова «Счет:», задается в виде: ###0,00;[Красный](###0,00);0,00;”Счет:”@

Функции

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

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

В качестве аргументов может использоваться:

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

Функцию можно ввести обычным путем с клавиатуры. Регистр при этом не учитывается. Целесообразней для вставки функции использовать мастер функций, который автоматически запускается при выборе команды Вставка/Функция [Shift+F3].

Мастер функций последовательно выводит два диалоговых окна. В первом выбирается функция (рис 23).

Рисунок 23. Мастер функций

Во втором, задаются аргументы функции (рис. 24).

Рисунок 24. Аргументы функции

Для удобства функции группируются по категориям, как показано в таблице 8. В списке Категория выбирается категория, к которой относится функция (если категорию определить затруднительно, следует использовать пункт Полный алфавитный перечень), а в списке Выберите функцию – конкретная функция данной категории. Пункт Поиск функции позволяет найти искомую функцию в списке «вручную», набрав ее с клавиатуры. После щелчка на кнопке Ok имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовыйкурсорустанавливается между скобками.

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

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

Повторное нажатие по этой кнопке разворачивает панель формул.

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

Категория	Назначение
Финансовые	Вычисляют процентные ставки, ежемесячные отчисления, амортизационные отчисления
Дата и Время	Отображают текущий час, день недели, время или дату
Математические	Вычисляют абсолютные величины, косинусы и логарифмы
Статистические	Вычисляют средние значения, наибольшее и наименьшее число в диапазоне, коэффициенты распределения Стьюдента, тестируют на независимость выборки
Ссылки и массивы	Вычисляют и возвращают значения из диапазона, создают гиперссылки для сетевых или Web-документов
Работа с базой данных	Вычисляют значения в таблицах баз данных Excel
Текстовые	Преобразуют регистры текста, образуют символа с правого или левого конца текстовой строки, объединяют текстовые строки
Логические	Вычисляют выражения и возвращают значения ИСТИНА или ЛОЖЬ, которые используются при выполнении другого действия или форматирования

Таблица 8. Категории функций в Excel

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

СУММ (число 1; число 2; …) – суммирует аргументы. Без использования мастера функций данную операцию можно выполнить кнопкой  Автосумма на панели инструментов Стандартная.
СУММЕСЛИ (диапазон; условие; диапазон_суммирования) – суммирует ячейки диапазона_суммирования, для которых в диапазоне выполняется условие.
СУММПРОИЗВ (массив 1; массив2; …) – возвращает сумму произведений элементов массивов
ОКРУГЛ (число; количество_цифр) – округляет число до указанного количества десятичных разрядов.
ЧАСТНОЕ (х;у) – возвращает целую часть от деления х/у.
SIN (x) – синус угла х (в радианах).
COS (x) - косинус угла х (в радианах).
TAN (x) – тангенс угла х (в радианах).
LN (x) – натуральный логарифм х.
LOG10 (x) – десятичный логарифм х.
LOG (x; Основание) - логарифм х по заданному основанию.
EXP (x) – экспонента от х.
СТЕПЕНЬ (х; а) – возведение в степень х^а.
СЛУЧМЕЖДУ (х; у) – генерация случайных чисел, равномерно распределенных на интервале [x; y].
СЛЧИС () – генерация случайных чисел, распределенных на интервале[0; 1]. Значения, возвращаемые функцией, изменяются при каждом пересчете листа. Функция доступна при установке Пакета анализа (Сервис/Надстройка).
ABS (x) – абсолютное значение.
ФАКТОР (х) – факториал.
КОРЕНЬ (х) – корень квадратный.
КОРЕНЬПИ (х) – квадратный корень из числа (х*).

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

МИН (число 1; число 2; …) - возвращает минимальное значение из списка аргументов
МАКС (число 1; число 2; …) - возвращает максимальное значение из списка аргументов
СРЗНАЧ (число 1; число 2; …) - возвращает среднее арифметическое своих аргументов
СЧЕТ (значение 1; значение2; …) - подсчитывает количество чисел в списке аргументов
СЧЕТЕСЛИ (диапазон; условие) - подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному условию.
СЧИТАТЬ ПУСТОТЫ (диапазон) - подсчитывает количество пустых ячеек в диапазоне
СЧЕТЗ (значение 1; значение2; …) - подсчитывает количество непустых значений в списке аргументов