ftd

Функция возвращает число рабочих дней, включая начальную и конечную даты. Если это одна и та же дата, результат - единица.

ЧИСТРАБДНИ("01.09.99";"01.09.99") =1.

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

=ЧИСТРАБДНИ("1.5.98";ДАТАМЕС("1.5.98";1);{"1.5.98";"2.5.98"})

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

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

КОНМЕСЯЦА(<начальная дата>;<число месяцев>)

Возвращает дату последнего дня месяца, отстоящего на указанное <число месяцев> <от начальной даты>. Функция используется для вычисления даты вступления в силу договора или даты платежа, которая приходится на конец месяца. Параметр Число месяцев может быть отрицательным. В этом случае отсчет ведется назад.

Пример: КОНМЕСЯЦА (“18.09.97”;5)=28.02.98 КОНМЕСЯЦА (“18.09.97”;-5)=30.04.97.

ДАТАМЕС(<начальная дата>;<число месяцев>) – возвращает дату, отстоящую на указанное число месяцев от начальной даты. Функция используется для вычисления даты вступления в силу договора или даты платежа. Параметр Число месяцев может быть отрицательным. В этом случае отсчет ведется назад.

Пример. ДАТАМЕС(”18.09.97”;-5)=18.04.97 ДАТАМЕС(”18.09.97”;5)=18.02.98

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

ДАТАМЕС(”30.01.97”;1)=28.02.97 (в феврале нет 30-го числа)

ТДАТА() возвращает текущую системную дату и время.

Пример. ТДАТА()=26.04.01 11.02

ДОЛЯГОДА( <начальная дата>;< конечная дата>;1) – вычисляет число лет, прошедших между конечной и начальными датами. Дробная часть дат отбрасывается, т.е. время при дате (если есть) в вычислениях не участвуют.

Пример. Положим А1=01.06.56 и А2=01.12.98, тогда ДОЛЯГОДА(А1;А2;1) равняется 42,5 года.

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

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

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

=ЕСЛИ(ТЕКСТ(В1;"ДД.ММ")= ТЕКСТ(Е4;"ДД.ММ");"Сегодня день рождения";"")

Здесь используется функция преобразования даты в текстовую форму с включением только дня и месяца (ТЕКСТ(...; "ДД.ММ")). Положим, В1=10.16.80, а Е4=10.16.99. Тогда наша функция проделает следующие вычисления

=ЕСЛИ(ТЕКСТ("10.16.1980";"ДД.ММ")=ТЕКСТ("10.16.1999";"ДД.ММ");"Сегодня день рождения";"")

или =ЕСЛИ("10.16"="10.16;"Сегодня день рождения";"").

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

следует от текущей даты (ячейка В1) отнять последнюю дату (т.е. 31 декабря) предыдущего года. Для этого из текущей даты выделяется текущий год, из которого отнимается 1 год (ГОД(В1)-1). Далее результат превращается в текст в формат "0000" (например, "1998") и присоединяется к последнему дню года "31.12". Полученный текст снова превращается в формат даты (31.12.1998) и вычитается из текущей даты.

=В1-ДАТАЗНАЧ("31.12."&ТЕКСТ(ГОД(В1)-1;"0000")). Положим, В1 =25.6.1999.

Тогда (выполним последовательные преобразования данных) =25.6.1999 - ДАТАЗНАЧ("31.12."&ТЕКСТ(ГОД(25.6.1999)-1 ;"0000")), =25.6.1999-ДАТАЗНАЧ("31.12."&ТЕКСТ(1998;"0000")), =25.6.1999-ДАТАЗНАЧ("31.12.1998"), =25.6.1999-31.12.1998=176 дней.

7.2. ТАБЛИЧНЫЙ ПРОЦЕССОР. Построение, редактирование и форматирование диаграмм

Диаграмма — это графическое представление данных.

Диаграммы используются для анализа и сравнения данных, представления их в наглядном виде.

Вдиаграмме необходимо уяснить следующие понятия:

•область диаграммы,

•область построения диаграммы,

•ось категорий (ось Х),

•ось значений (ось У),

•ряд,

•легенда,

•заголовок диаграммы,

•название оси Х и У,

•маркер,

•линии сетки (основные и промежуточные),

•подписи данных (рис.7.21)

Диаграмма состоит из элементов: линий, столбиков, секторов, точек и т.п. Каждому элементу диаграммы соответствует число в таблице. Числа и элементы диаграммы связаны между собой таким образом, что при изменении чисел автоматически изменяется изображение элементов диаграммы и наоборот. Различают два вида диаграмм:

•внедренные диаграммы — сохраняются на рабочем листе вместе с данными;

•диаграммные листы — диаграмма в формате полного экрана на новом листе.

Диаграмма создается с помощью Мастера диаграмм, вызываемого

кнопкой на панели Стандартная либо командой меню Вставка-Диаграмма. Мастер диаграмм позволяет строить диаграммы 14 стандартных типов

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

Рис. 7.22. Некоторые типы диаграмм

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

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

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

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

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

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

Блок ячеек может содержать несмежные ячейки одного рабочего листа. В этом случае выделенные блоки должны иметь одинаковую конфигурацию.

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

Этап 3 . Задание параметров диаграммы. Задание параметров диаграммы осуществляется в окнах вкладок Мастера диаграмм:

Заголовки – вводятся поочередно на соответствующую строку название диаграммы, название оси X, название оси У, название оси Z.

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

Линии сетки – устанавливаются переключатели отображения сетки на диаграмме.

Легенда – указывается место расположения легенды.

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

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

Этап 4 . Размещение диаграммы. Созданную диаграмму можно разместить на том листе, где находится таблица с исходными данными, либо на отдельном

листе. В диалоговом окне Мастер диаграмм на этапе 4 для этого надо установить соответствующий переключатель и нажать кнопку Готово.

Редактирование диаграмм

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

Пункты меню 1-5 предоставляют пользователю возможность вернуться к любому из четырех этапов создания диаграммы и осуществить необходимые изменения, т.е.:

•изменить тип и формат диаграммы;

•изменить исходные данные:

−переопределить исходный интервал ячеек, на основании которых построена диаграмма;

−переопределить ориентацию рядов и их название;

−изменить данные, используемые для подписей оси X;

−изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);

−изменить размещение диаграммы.

Форматирование типов диаграмм

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

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

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

Форматирование элементов диаграммы

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

Существует два пути форматирования компонентов диаграммы:

1.Указать курсором мыши на любой компонент диаграммы (ряд, ось, подписи, легенду ...), щелкнуть ее правой кнопкой и в появившемся контекстном меню выполнить команду Формат ... (... = конкретный компонент).

2.В окне «Элементы диаграммы» панели инструментов Диаграмма выбрать необходимый компонент и нажать соседний с окном значок Формат.

Форматирование области диаграммы и области построения диаграммы

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

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

Форматирование рядов данных

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

При выделении отдельного ряда в поле имен появляется стандартное обозначение Р (номер) — номер ряда данных, в строке формул выводится формула ряда, например:

=РЯД(Лист1 !$С$1;Лист1 !$А$2:$А$6;Лист1 !$С$2:$С$6;2),

Лист1!$С$1 — абсолютная ссылка на ячейку, содержащую название ряда, которое используется при построении легенды;

Лист1!$А$2:$А$6 — блок ячеек, содержащий метки (категории) значений оси

X:

Лист 1 !$С$2:$С$6 — блок ячеек, содержащий элементы значений ряда; 2 — порядковый номер ряда.

Выделенный ряд можно удалить.

Форматирование рядов диаграмм осуществляется при помощи диалогового окна Формат ряда данных,

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

На вкладке Вид задаются установки внешнего вида элементов ряда.

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

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

Вкладка Параметры позволяет задать важнейшие характеристики диаграмм. Состав настраиваемых параметров зависит от типа диаграммы:

− параметр Перекрытие определяет процент наложения изображений маркеров элементов данных рядов друг на друга, указывается для групп плоских линейчатых диаграмм и гистограмм:

0 — маркеры различных рядов находятся рядом, -100 — маркеры рядов отстоят друг от друга на максимальном расстоянии; 100 — маркеры рядов сливаются вместе.

−параметр Ширина зазора устанавливает величину интервала между кластерами (группой элементов данных различных рядов, отнесенной к одной и той же категории оси X), изменяется в пределах от 0 до 500. Доступна для групп линейчатых диаграмм, графиков, гистограмм, объемных линейчатых диаграмм и объемных гистограмм.

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

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

−параметр Линии проекции обеспечивает установку перпендикуляров, опущенных от каждого маркера на ось X. Доступна для групп диаграмм с областями и графиков.

На вкладке Ось задается связь ряда данных с основной или вспомогательной осью Y диаграммы. Элементы ряда имеют привязку к двум осям:

Х - порядковый номер элемента или определенная категория; Y— значение элемента в соответствии с его единицей измерения.

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

На вкладке Y-погрешности определяется тип планок погрешностей для элементов ряда согласно его статистическим характеристикам.

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

Форматирование осей диаграммы

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

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

Объемная диаграмма стоит на основании и окружена стенками, которые можно самостоятельно форматировать. При форматировании осей активизируется вкладки:

−Вид — выбирается внешнее оформление оси (линии), указывается наличие и расположение основных и вспомогательных засечек и их меток;

−Шрифт — выбирается вид и размер шрифта меток засечек;

−Шкала — задается масштаб значений элементов данных ряда на оси и разметка оси (расстояние между засечками и линиями сетки), значение точки пересечения с осью Х (для плоских) или с основанием (для объемных) диаграмм; выбирается при необходимости логарифмическая шкала и обратный порядок значений (для оси категорий — изменение порядка следования меток слева направо, для оси значений — изменение значения от меньшего к большему);

−Число — задается формат меток засечек или самих числовых значений;

−Выравнивание — изменяется ориентация текста меток засечек.

Форматирование сетки

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

Форматирование легенды

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

Легенда может создаваться в автоматическом режиме, если при построении диаграммы интервал ячеек включал названия рядов. Если имена рядов явно не заданы, то легенда использует стандартное имя Ряд (номер),

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

Тренды

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

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

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

•тренд в анализируемый период должен развиваться эволюционно;

•процесс, представленный диаграммой, должен обладать определенной инерционностью.

Тренд можно строить для диаграмм типа:

•линейчатый график;

•гистограмма;

•диаграмма с областями;

•ХУ-точечная диаграмма.

Построение тренда осуществляется по следующей технологии:

•построить диаграмму для одного ряда данных;

•выделить эту диаграмму, щелкнув по ней левой кнопкой мыши. На диаграмме должны появиться маркеры;

•вызвать контекстное меню и выполнить команду Добавить линию тренда;

•в диалоговом окне «Линия тренда» на вкладке Тип выбрать тип тренда, а на вкладке Параметры установить параметры: Количество периодов прогноза. Показывать уравнение на диаграмме. Поместить на диаграмму величину достоверности аппроксимации;

•нажать кнопку <ОК>.