Основные сведения о построении формул: Формула в EXCEL – это такая комбинация констант (значений), ссылок на ячейки, имен, функций и операторов, по которой из заданных значений выводится новое.

Приложение 1

ПРИМЕР ВЫПОЛНЕНИЯ КОНТРОЛЬНОГО ЗАДАНИЯ

Создать книгу «Заработная плата», состоящую из трех листов: «Таблица с расчетными данными», «Справочники», «Дополнительная информация».

Все расчеты необходимо оформить на первом листе «Таблица с расчетными данными» следующего вида (Рис. 1).

Рис. 1 Лист таблицыТаблица с расчетными данными

На втором листе «Справочники» поместить две таблицы следующего вида (Рис.2):

Рис. 2 Лист таблицы: Справочники

На третьем листе «Дополнительная информация» поместить две таблицы следующего вида (Рис.3):

Рис.3 Лист таблицы Дополнительная информация

Для расчета использовать следующие формулы:

· начисленной зарплаты ЗП = ЗП_окл + ПР;

· начисленной зарплаты по окладу ЗП_окл = ОКЛ * ФТ/Т;

· размера премии ПР = ЗП окл * %ПР;

· удержаний из зарплаты У = У_пн + У_пф + У _ил;

· удержания подоходного налога У_пн = (ЗП - МЗП * Л) * 0,12;

· зарплаты к выдаче ЗПВ = ЗП – У

где:

ОКЛ – оклад работника в соответствии с его разрядом;

ФT – фактически отработанное время в расчетном месяце (дн.);

Т – количество рабочих дней в месяце;

%ПР – процент премии в расчетном месяце;

МЗП – минимальная зарплата;

Л – количество льгот;

Технология выполнения контрольного задания:

Построение диаграммы Ганта

23. На новом листе «График Ганта» постройте простую диаграмму Ганта, отображающую во времени начала работ проекта и их продолжительность. Исходные данные содержатся в приведенной ниже таблице.

Для построения диаграммы выполните следующие действия:

· выберите данные, которые нужно показать на диаграмме Ганта (A2:D8);

· выполните команду Вставка / Диаграммы / Линейчатая/Линейчатая с накоплением;

· добавьте на диаграмму данные о продолжительности работ. Для этого выполните команду Конструктор/Данные/Вы­брать данные и в окне Выбор источника данных нажмите кнопку Добавить. В диалоговом окне Изменение ряда в поле Имя ряда щелчком мыши введите ссылку на ячейку с именем «Продолжительность в днях», а в поле Значения введите мышью ссылку на диапазон ячеек С3:С8 с данными о продолжительности работ. Нажмите ОК;

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

· два раза щелкните по кнопке ОК;

· на диаграмме щелкните по ряду данных «Начало работы»;

· выполните команду Формат / Текущий фрагмент / Формат выделенного фрагмента;

· в группе Заливка выберите вариант Нет заливки, щелкните по кнопке Закрыть;

· выделите вертикальную ось (категорий) или щелкните по диаграмме и выберите ее в списке элементов диаграммы по команде Формат/Текущий фрагмент/Область диаграммы /Вер­тикальная ось (категорий);

· на вкладке Формат в группе Текущий фрагмент щелкните Формат выделенного фрагмента;

· в группе Параметры оси установите флажок обратный порядок категорий, а в группе Горизонтальная ось пересекает – в максимальной категории. Нажмите кнопку Закрыть;

· выделите ряд «Начало работы», вызовите контекстное меню и в левой части окна Формат ряда данных выберите Заливка, а в правой – Нет заливки;

· вызовите контекстное меню легенды и удалите ее;

· вызовите контекстное меню горизонтальной оси (значений). В диалоговом окне Форматоси в группе Параметры оси измените минимальное значение с «авто» на «фиксированное», введя дату 01.02.09 (или число 39845); максимальное значение с «авто» на «фиксированное», введя дату 02.04.09 (или число 39905); цену основных делений введите 10, а цену промежуточных делений – 2.

· введите название диаграммы по команде Макет/Подписи/Название диаграммы/Над диаграммой;

· отредактируйте размеры шрифтов отдельных элементов и размеры области диаграммы.

Трендовый анализ

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

Трендовый анализ – это тенденция развития процесса, позволяющая в вероятностном аспекте прогнозировать его дальнейшее поведение.

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

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

Для оценки близости значений линии тренда к фактическим данным принято использовать коэффициент детерминации R ². Этот коэффициент изменяется в пределах от 0 до 1. Чем ближе к 1 значение R ², тем лучше качество подгонки.

При подборе линии тренда к данным Excel автоматически рассчитывает значение R ². Можно отобразить это значение на диаграмме.

24. Вставьте новый лист Рабочей книги Excel и переименуйте его в «Линия тренда».

25. Введите приведенную ниже таблицу.

26. Для этой таблицы сначала постройте диаграмму График. Для этого выделите диапазон ячеек A3:B9 и выполните команду Вставка/Диаграммы/График/График.

27. Для этого графика постройте линию тренда. Для этого:

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

· в диалоговом окне Параметры линии тренда выберите вид линии тренда: полиномиаль­ная 4-й степени;

· в этом же окне установите: прогноз вперед на 1 период и поместить на диаграмму величину достоверности аппроксимации;

· нажмите кнопку Закрыть;

· покажитена диаграмме линии проекции по команде Макет/Анализ/Линии/Линии;

· удалите легенду;

· с помощью контекстного меню Формат линии тренда установите цвет линии – красный;

· введите название диаграммы «Динамика спроса », заливка текста произвольная;

· нажмите кнопку ОК.

Технология интеграции приложений:

Вставка и внедрение диаграмм:

26. Выделите и скопируйте диаграмму «Объемная гистограмма с группировкой» и поместите ее в буфер обмена.

27. С помощью вкладки «Главная/Вставить/Специальная вставка» вставьте диаграмму в созданный вами самостоятельно текстовый документ Word:

· вставить как «Метафайл Windows (EMF)»;

· как Диаграмму MicrosoftExcel (объект) при активизированном переключателе «Связать».

28. Сделайте двойной щелчок мышью поочередно на каждой из диаграмм.

29. Проанализируйте результаты работы вставки первым и вторым способами.

Технология установки связи диаграммы с исходной таблицей: Для создания диаграммы, связанной с исходной таблицей выполните следующие действия:

· Выделите диаграмму и скопируйте ее в текстовый документ Word. С помощью вкладки Главная/ в наборе команд Буфер обмена /кнопка Вставить/Специальная ставка и установите «Связь» для изменения исходных данных в исходной таблице.

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

· Измените числовые данные в исходной таблице и проследите за изменением вида диаграммы.

· Удалите все данные в исходной таблице. Что произошло и диаграммой? Проанализируйте результат.

Технология фильтрации табличных данных:

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

Технология автофильтра:

Введите в таблицу Ведомость начисления заработной платы и премиальных информацию только по окладу более 5 217 р. Для этого:

· выделите область столбца Цена с данными и заголовком;

· выполните команду Данные/Фильтр;

· нажмите стрелку списка в заголовке столбца Цена, выберите Числовые фильтры/больше;

· введите 5217, нажмите OК.

28. Отмените фильтр, выполнив команду Данные/Сортировка и фильтр/Очистить. Чтобы убрать значок автофильтра из заголовка столбца, повторно нажмите Фильтр на вкладке Данные в группе Сортировка и фильтр.

Технология расширенного фильтра:

«^» возведение в степень, «*» умножение, «/» деление, «+» сложение, «-» вычитание;

Ссылки применяются для обозначения ячеек или групп ячеек рабочего листа.

Для построения ссылок используются заголовки столбцов и строк рабочего листа.

Существует три типа ссылок: относительные, абсолютные и смешанные.

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

Абсолютная ссылка ($A$1) – указывает, как найти ячейку на основании её точного местоположения на рабочем листе.

Смешанная ссылка (A$1, $A1) – указывает, как найти другую ячейку на основе сочетания абсолютной ссылки на строку и относительной на столбец и наоборот.

Для изменения ссылки на ячейку в формуле или функции выделите эту ячейку и нажмите функциональную клавишу «F4».

Ссылки на ячейки других листов имеет следующий формат:

Л и с т N! я ч е й к а

Относительная или абсолютная ссылка на ячейку

Имя рабочего листа

Отделяет имя листа от ссылки на ячейку

Если «Имя рабочего листа» содержит пробелы, то имя заключается в одинарные кавычки «‘ ‘».

Функция – это специальная, заранее созданная формула, которая выполняет операции над заданным значением (значениями) и возвращает одно или несколько значений.

Функция Excel 2007 представляет собой формулу, которая имеет один или несколько аргументов.

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

Для работы с функциями применить технологию «Мастера функций». Для выполнения вычислений в работе можно использовать следующие функции рабочего листа. Рассмотрим некоторые из них:

«ВПР»

Функция «ВПР» ищет в таблице искомое значение, затем перемещается в таблице к соответствующей ячейке и возвращает ее значение

=ВПР (Искомое_значение; Табл_массив; Номер_столбца; Интерваль­ный_просмотр)

Искомое_значение – это значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Табл_массив – это таблица с информацией, в которой ищутся данные.

Номер_столбца – это номер столбца в таблице, в котором должно быть найдено соответствующее значение.

Интервальный_просмотр – это логическое значение, которое определяет, нужно ли искать точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен и точное соответствие не найдено, то возвращается приблизительно соответствующее значение, а именно: наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

«СУММЕСЛИ»

Функция «СУММЕСЛИ» суммирует ячейки, отвечающие заданному критерию.

= СУММЕСЛИ (Диапазон; Условие; Диапазон_суммирования)

Диапазон – определяет интервал вычисляемых ячеек.

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

Диапазон_суммирования – фактические ячейки для суммирования. Суммируются те ячейки диапазона, которые удовлетворяют условию. Если диапазон суммирования отсутствует, то суммируются ячейки аргумента «диапазон».

«СЧЕТЕСЛИ»

Функция «СЧЕТЕСЛИ» подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному критерию.

= СЧЕТЕСЛИ (Диапазон; Критерий)

Диапазон – опеределяет интервал, в котором подсчитывается количество ячеек.

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

= ЕСЛИ

Функция «ЕСЛИ» возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

=ЕСЛИ (Логическое_выражение; Значение_если_истина; Значение_если_ложь)

Логическое_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина – это значение, которое возвращается, если логическое_выражение имеет значение ИСТИНА. Если логическое_выражение имеет значени