КОНТРОЛЬНАЯ РАБОТА

КОНТРОЛЬНОЙ РАБОТЫ

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

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

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

Чтобы вывести на экран рабочего листа формулу надо выполнить следующие действия «Формулы/Зависимости формул» в диалоговом окне установить «Показать формулы».

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

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

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

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

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

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

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

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

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

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

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

  Если «Имя рабочего листа»содержит пробелы, то имя заключается в одинарные… Функция – это специальная, заранее созданная формула, которая выполняет операции над заданным значением (значениями) и…

ЕСЛИ

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

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

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

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

Значение_если_ложь – это значение, которое возвращается, если логическое_выражение имеет значение ЛОЖЬ. Если логичес­кое_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

«ЕНД»

Функция «ЕНД» проверяет значение ячейки.

= ЕНД ( значение )

Если значение ячейки ошибка #Н/Д, то функция возвращает значение ИСТИНА, в противном случае – ЛОЖЬ.

«СЕГОДНЯ»

Возвращает текущую дату в формате даты

«ДОЛЯГОДА»

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

«ОСТАТ»

Возвращает остаток от деления

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

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

· вставка диаграммы в лист непосредственно (внедренная диаграмма);

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

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

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

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

Excel позволяет создавать диаграммы 14 стандартных типов: гистограмма, линейчатая, график, круговая, точечная, с областями, кольцевая, лепестковая, поверхность, пузырьковая, биржевая, коническая, цилиндрическая, пирамидальная. Каждый тип может иметь несколько вариантов (подтипов). Кроме того, имеются 20 типов нестандартных диаграмм.

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

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

Параметры диаграммы:заголовки; оси; линии сетки; легенда; подписи данных; таблица данных.

Заголовки содержат названия различных элементов диаграммы:заголовок диаграммы; название оси категорий (ось Х);название оси значений (ось Y);

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

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

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

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

Область диаграммы – это вся диаграмма, вместе со всеми ее элементами.

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

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

Этапы построения диаграммы:выбор типа диаграммы; выбор источника данных; определение параметров диаграммы; выбор размещения диаграммы.

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

Технология фильтрации данных (автофильтр, расширенный фильтр):

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

Автофильтр

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

Расширенный фильтр

- для одной колонки: более двух условий, соединенных операторами И, ИЛИ; - для нескольких колонок: комбинации условий, соединенных оператором ИЛИ; - вычисляемые условия, в формулах (функциях) расчета которых присутствуют ссылки на исходный диапазон;

Клиент OLE (OLE-клиент, приложение-приемник или приложение-контейнер)-приложение, принимающее объект OLE, то есть программа, которая пользуется услугами OLE-сервера.

Различные приложения Windows в разной степени поддерживают технологию OLE. Некоторые программы, например Блокнот, вообще ее не поддерживают. Ряд программ WordArt, Microsoft Equation и др. – могут исполнять только роль OLE-сервера. Информационные системы, наоборот, чаще всего выполняют только роль приемника, то есть OLE-клиента.

Наиболее мощные приложения Windows, такие как Word? Excel, PowerPointи др., могут исполнять функции как OLE-сервера, так и OLE-клиента.

Пример выполнения контрольной работы приведен в ПРИЛОЖЕНИИ 1.

Приложение 1

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

 

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

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

 

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

 

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

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

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

 

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

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

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

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

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

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

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

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

где:

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

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

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

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

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

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

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

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

2. На листе « Таблица с расчетными данными » введите заголовок«Ведомость начисления заработной платы и премиальных».Для этоговыделите диапазон ячеек «A1:К1», и с помощью вкладки «Главная/Выравнивание» нажмите кнопку пиктографического меню «Объединить и поместить в центре».

Разлинуйте таблицу «Ведомость начисления заработной платы и премиальных".

Для этого:

· необходимо выделить диапазон ячеек « A2:К9 » иправой кнопкой мыши, в контекстном меню выбрать команду«Формат ячеек»в диалоговом окне команды на вкладке «Граница» выберите кнопку «Все границы».

· Установить для диапазона ячеек шапки таблицы «Ведомость начисления заработной платы и премиальных»режим переноса текста, выбрав вкладку «Главная».В наборе команд«Выравнивание»щелкните на стрелке, чтобы отобразить окно «Формат ячеек»; в диалоговом окне «Формат ячеек» в группе « Выравнивание » установите флажок в области «Отображение»/«Переносить по словам » и щелкнуть по кнопке «OK».

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

· введите «1001» в ячейку «А3»;

· установите указатель в ячейку «А3»на маркер в правом нижнем углу. Указатель мыши примет форму креста;

· удерживая клавишу «Ctrl» и левую клавишу мыши, протащите указатель по диапазону «А4:А9». Диапазон ячеек «А4:А9»заполнился числами от «1001-1007».

В случае ошибочно набранного текста нажмите функциональную клавишу « F2 » и исправьте ошибку.

Ввод в таблицу функций:

Ввод функции и формул начинается со знака равенства«=».

4. Для корректного ввода в таблицу функций используйте технологию «Мастера функций».

Для этого:

5. На рабочем листе «Таблица с расчетными данными» установите курсор в клетку « F3 » и введите с помощью «Мастера функций» функцию «ВПР» для заполнения «Количества льгот»используя данные таблицы «Справочник работников» листа «Справочники»(Рис.4).

Рис. 4Технология заполнения диапазона ячеек «Количество льгот» функцией «ВПР»

 

Знак $(абсолютная ссылка), которая фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются). Для ввода знаков $нажимайте «F4»после ввода каждого диапазона или ссылки, по окончании ввода функции нажмите «Enter»):

В случае возникновения ошибки определите источник возникновения ошибки. Для этого на вкладке «Формула/Зависимости формул/Проверка наличия ошибок/Источник ошибок»установите указатель в ячейку с формулой и выберите в меню команду «Сер­вис/Зависимо­сти/Источ­ник ошибки».

Скопируйте формулу определения « Количество льгот »в диапазон ячеек « F4:F9 »

6.Аналогично заполните диапазон ячеек «G3:G9»шапки «Должность».

7. Установите курсор в клетку «H3 »таблицы«Ведомость начисления заработной платы и премиальных»и с помощью «Мастера функций» введите формулу для расчета начислений по окладу (Рис.5).

ВПР (C3;Справочники!$А$3:$В$6;2;ложь)*E3 /$B$3

Рис. 5 Технология ввода формулы «Оклад» функцией «ВПР»   Скопируйте формулу начисления ЗП по окладу в диапазон «H4:H9».

Технология защиты данных.

Рис. Технология защиты листа · Скрыть столбец или строку: Для этого выполните следующую технологию (Рис.)