Логические функии и графики в Excel. Практикум Для удобства проверки знаний материал разбит на блоки. В таблице приведен перечень тем лабораторных работ
 Скачать 1.95 Mb.
|
|
Лабораторный практикум Для удобства проверки знаний материал разбит на блоки. В таблице приведен перечень тем лабораторных работ
Лабораторная работа №1. Основы работы с таблицами MS EXEL М е т о д и ч е с к и е у к а з а н и я : 1. Структура таблицы для решения конкретной задачи состоит из трех основных частей: области заголовков, которая содержит информацию о цели и содержании таблицы; области констант (предположений), которая содержит данные, используемые многократно без изменений в таблице при создании формул, необходимых для расчетов; рабочей области таблицы (область расчетов), которая содержит заголовки строк и столбцов, независимые переменные и вычисляемые формулы. 2. Заполнение рабочей области таблицы производится следующим образом: сначала заполняют заголовки строк и столбцов, затем – независимые переменные и, наконец, – формулы. 3. При вводе формул целесообразно вводить адреса ячеек, выбирая их мышкой. 4. Ссылки на ячейки области констант, как правило, абсолютные. Для преобразования относительной ссылки в абсолютную используется клавиша F4, которая нажимается при необходимости после выбора соответствующей ячейки мышкой. 5. Ссылки на ячейки рабочей области таблицы, как правило, относительные (принятые по умолчанию). 6. Формулы расчетов вводятся только в самые верхние ячейки столбцов, а затем копируются в остальные при помощи мышки (метод протаскивания). Задание 1. Создание структуры задачи и выполнение первичных расчетов П о р я д о к р а б о т ы : 1. Создать таблицу по образцу 2. Ввести формулу расчета размера начисленной заработной платы, которая учитывает, что работнику выплачивается его оклад, деленный на количество рабочих дней в месяце и умноженный на количество фактически отработанных сотрудником дней. 3. Ввести формулу для расчета премии, приняв во внимание, что она вычисляется в проценте от начисленной суммы заработной платы. 4. Рассчитать величину подоходного налога, используя соответствующий процент. 5. Рассчитать денежную сумму к выдаче. 6. Отформатировать таблицу, применяя цветовое оформление заголовка; установить границы и денежный формат для соответствующих столбцов таблицы. 7. Подвести итог столбца «К выдаче».  Задание 2. Дополнительные вычисления и изменения в таблице. П о р я д о к р а б о т ы :
2. Вставить столбец «Кол-во иждивенцев» между столбцами «Оклад» и «Кол-во отраб. дн.». Заполнить его по своему усмотрению. 3. Между столбцами «Премия» и «Подоходный налог» вставить столбцы «Налоговые вычеты» и «Облагаемая налогом сумма». 4. Рассчитать налоговые вычеты, учитывая, что они составляют 400 руб. на работника и по 300 руб. на каждого его иждивенца. 5. Рассчитать сумму, облагаемую налогом, величину подоходного налога и сумму к выдаче Задание 3. Подведение итогов, применение трехмерных ссылок. П о р я д о к р а б о т ы : 1. Переименовать лист, дав ему название соответствующего месяца. 2. Скопировать информацию на лист 2, воспользовавшись методом копирования листов. 3. Внести исправления в заголовке – заменить январь на февраль. 4. Переименовать лист, дав ему название соответствующего месяца. 5. Изменить количество рабочих дней в феврале на 24 и величину премиального процента на 35%. Изменить количество отработанных каждым сотрудником дней. 6. Выполнить аналогичные действия с листом 3, переименовав его соответствующим образом и разместив на нем информацию о зарплате сотрудников в марте (рабочих дней – 23, процент премии – 40%). 7. На отдельном листе составить таблицу, содержащую итоговую информацию о работе и зарплате сотрудников фирмы за первый квартал 2003 года. Указание. Данная информация должна быть представлена в виде таблицы со следующими заголовками столбцов: «ФИО», «Должность», «Количество отработанных дней за квартал», «Подоходный налог за квартал», «К выдаче за квартал». В данных столбцах создать формулы, позволяющие суммировать соответствующие значения, содержащиеся на разных листах рабочей книги (трехмерные ссылки, включающие название листа). Узнать подробнее о трехмерных ссылках С помощью трехмерной ссылки можно суммировать расположенные на отдельных листах бюджетные ассигнования трех отделов (отдела сбыта, кадрового отдела и отдела маркетинга). Для этого используется следующая трехмерная ссылка: =СУММ(Продажи:Маркетинг!B3) Можно добавить новый лист, а затем поместить его в диапазон, на который ссылается формула. Например, чтобы добавить ссылку на ячейку B3 на листе Технический отдел, поместите лист Технический отдел между листами Отдел сбыта и Отдел кадров, как показано на следующем примере. При вычислениях учитываются все листы в этом диапазоне, потому что формула содержит трехмерную ссылку на диапазон, заданный именами крайних листов: Отдел сбыта:Отдел маркетинга!B3. К началу страницы Подробнее об изменениях трехмерных ссылок при перемещении, копировании, вставке и удалении листов На следующем примере показывается, что происходит при вставке, копировании, удалении или перемещении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой. Вставка или копирование При вставке или копировании листов между листом 2 и листом 6 (первый и последний листы в данном примере) Excel включает в вычисления все значения в ячейках с А2 по А5 на добавленных листах. Удаление При удалении листов между 2 и 6 значения ячеек на этих листах исключаются из вычислений. Перемещение При перемещении листов со 2 по 6 за пределы диапазона, на который производится ссылка, значения ячеек на этих листах удаляются из вычислений. Перемещение первого или последнего листа При перемещении листа 2 или листа 6 в другое место в той же книге Excel корректирует вычисления, включая новые листы между первым и последним, если порядок расположения первого и последнего листов не изменен. При изменении порядка расположения трехмерная ссылка изменяет одну из границ диапазона листов. Например, имеется ссылка на Лист2:Лист6. При помещении листа 2 за листом 6 в книге формула будет изменена и станет включать листы с 3 по 6. При помещении листа 6 перед листом 2 формула будет исправлена таким образом и станет включать Лист2:Лист5. Удаление первого или последнего листа При удалении листа 2 или листа 6 его значения удалятся из вычислений Excel. К началу страницы Создание трехмерной ссылки Укажите ячейку, в которую нужно ввести функцию. Введите = (знак равенства), имя функции, а затем — открывающую круглую скобку. В трехмерной ссылке могут быть использованы следующие функции: Функция Описание СУММ Вычисление суммы чисел СРЗНАЧ Вычисление среднего арифметического чисел СРЗНАЧА Вычисление среднего арифметического чисел с учетом логических значений и текстовых строк СЧЕТ Подсчет количества ячеек, содержащих числа СЧЕТЗ Подсчет количества непустых ячеек МАКС Поиск наибольшего значения из набора значений МАКСА Поиск наибольшего значения из набора значений с учетом логических значений и текстовых строк МИН Поиск наименьшего значения из набора значений МИНА Поиск наименьшего значения из набора значений с учетом логических значений и текстовых строк ПРОИЗВЕД Вычисление произведения чисел СТАНДОТКЛОН Вычисление стандартного отклонения по выборке СТАНДОТКЛОНА Вычисление стандартного отклонения по выборке с учетом логических значений и текстовых строк СТАНДОТКЛОНП Вычисление стандартного отклонения по генеральной совокупности СТАНДОТКЛОНПА Вычисление стандартного отклонения по генеральной совокупности с учетом логических значений и текстовых строк ДИСП Оценка дисперсии по выборке ДИСПА Оценка дисперсии по выборке с учетом логических значений и текстовых строк ДИСПР Вычисление дисперсии для генеральной совокупности ДИСПРА Вычисление дисперсии для генеральной совокупности с учетом логических значений и текстовых строк Щелкните ярлычок первого листа, на который нужно создать ссылку. Удерживая нажатой клавишу SHIFT, щелкните ярлычок последнего листа, на который необходимо создать ссылку. Выделите диапазон ячеек, на которые нужно создать ссылку. Завершите ввод формулы, а затем нажмите клавишу ВВОД. К началу страницы Создание имени трехмерной ссылки На вкладке Формулы в группе Присвоенные имена выберите команду Присвоить имя. В диалоговом окнеСоздание имени в поле Имя введите имя, которое требуется присвоить ссылке. Длина имени может составлять до 255 знаков. В поле Диапазон выделите знак равенства (=) и ссылку, а затем нажмите клавишу BACKSPACE. Щелкните вкладку первого листа из тех, на которые будет указывать ссылка. Удерживая клавишу SHIFT, щелкните ярлычок последнего листа, на который будет указывать ссылка. Выделите ячейку или диапазон ячеек, на которые будет указывать ссылка. Лабораторная работа № 2. Построение диаграмм и графиков функций Графическое представление помогает осмыслить закономерности, лежащие в основе больших объемов данных. Один взгляд на диаграмму или график иногда дает гораздо больше, чем длительное изучение длинных колонок чисел. MS Excel предлагает богатые возможности визуализации данных. Первое задание направлено на освоение приемов построения и модификации трех основных типов диаграмм: гистограмма, круговая диаграмма и график. Во втором задании приводится алгоритм построения графиков функций с помощью точечной диаграммы. Задание 1. Построение диаграмм. П о р я д о к р а б о т ы : 1. Создать таблицу по образцу (рис. 12). 2. Выделить значения столбцов Приход и Расход без заголовков. 3. Выполнить команду Вставка/Гистограмма, а затем, не снимая выделения с диаграммы, команду Конструктор/Выбрать данные. 4. В открывшемся диалоговом окне: a. В категории «Элементы легенды (ряды)» выделить Ряд 1, нажать «Изменить», выделить ячейку с заголовком «Приход», нажать ОК => новое имя ряда «Приход» появится в диалоговом окне и на диаграмме. По аналогии Ряд 2 переименовать в «Расход». b. В категории «Подписи горизонтальной оси (категории)» нажать «Изменить» и выделить диапазон ячеек со значениями годов, ОК, ОК (рис. 12). 5. Не снимая выделения с диаграммы, перейти в меню Формат и внести изменения в категориях Стили WordArt и Стили фигур, по одному из параметров диаграммы (по выбору) в каждой категории. Гистограмма готова. Снять выделение. 6. Выделить значения ряда «Приход» (без заголовка). 7. Выполнить команду Вставка/Круговая диаграмма, а затем, не снимая выделения с диаграммы, команду Конструктор/Выбрать данные. 8. В открывшемся диалоговом окне: a. В категории «Элементы легенды (ряды)» выделить Ряд 1, нажать «Изменить», выделить ячейку с заголовком «Приход», нажать «ОК», после чего новое имя ряда «Приход» появится в диалоговом окне и на диаграмме. b. В категории «Подписи горизонтальной оси (категории)» нажать «Изменить» и выделить диапазон ячеек со значениями годов, ОК, ОК. 9. Не снимая выделения, выполнить команду Конструктор/Макеты диаграмм и выбрать в перечне третий образец во втором ряду. Круговая диаграмма готова. Снять выделение (рис. 12). 10. Выделить значения ряда «Расход» (без заголовка). 11. Выполнить команду Вставка/График, а затем, не снимая выделения с диаграммы, команду Конструктор/Макеты диаграмм и выбрать первый образец в списке. 12. В получившейся диаграмме выделить надпись «Название диаграммы», удалить шаблонное название и написать «Расход». Затем выделить надпись «Название оси», удалить шаблонное название и написать «Млн. руб.». 13. Правой кнопкой мышки щелкнуть по подписям оси ОХ (вызов контекстного меню), выбрать пункт «Выбрать данные». 14. В диалоговом окне изменить название ряда «Ряд 1» на «Расход», а по горизонтальной оси сделать подписи соответствующих годов. 15. Правой кнопкой мыши щелкнуть по ряду данных на диаграмме и выбрать «Добавить подписи данных». 16. Правой кнопкой мыши щелкнуть по ряду данных на диаграмме и выбрать «Добавить линию тренда». Ничего не меняя в открывшемся окне, нажать «Закрыть». График с линией тренда построен. Снять выделение (рис.12). 17. Внесите изменения в построенную круговую диаграмму. Выделите один из секторов диаграммы, щелкните по выделенному сектору правой кнопкой мыши и выберите команду Формат точки данных/Заливка, поставьте переключатель «Сплошная заливка» и выберите новый цвет сектора. 18. Выделите гистограмму и скопируйте в Буфер Обмена. Выполните команду Вставить. 19. Внести изменения в копию гистограммы. Для этого правой кнопкой мыши щелкнуть по рядам данных на диаграмме и выбрать пункт Выбрать данные. 20. В категории «Элементы легенды (ряды)» нажать кнопку «Добавить», дать новому ряду имя «Приход фирмы» и выделить значения ряда «Приход» (без заголовка). Щелкнуть правой кнопкой мыши по новому ряду на диаграмме и выбрать «Изменить вид ряда данных» и выбрать «График с маркерами» первого вида. Добавить на новом ряду подписи данных. 21. Аналогичные действия проделайте с добавлением ряда «Расход фирмы» (рис.12). Задание 2. Построение графика функции.  П о р я д о к р а б о т ы : 1. Построим таблицу, состоящую из ряда значений аргумента Х, значений функции Y, начального значения (НЗ) и шага (рис. 13). Значения НЗ и шага вводятся с клавиатуры. При этом на рабочем листе необходимо создать три формулы: a) в ячейке А2: =С2 (т.е. первое значение в ряду Х равно начальному значению).  b) в ячейке А3: =А2+$D$2 и скопировать формулу вниз до достижения значения 1. c) в ячейке В2: =ABS(A2-3)*COS(ПИ()*A2^2) и скопировать формулу вниз по столбцу. 2. Выделить ряды X и Y вместе с заголовками и выполнить команду Вставка/Точечная, выбрать вид гладкой кривой без маркеров. 3. Изменить вид диаграммы, согласно образцу (рис. 13).  Построить графики следующих функций с шагом 0,1. Фрагменты графиков для проверки приводятся в таблице  Лабораторная работа №3. Построение поверхностей. Задание 1. Построение поверхности  П о р я д о к р а б о т ы : 1. На рабочем листе создать диапазоны изменения x и y от -20 до 20 по образцу на рис. 17 (приведен фрагментарно, строка – значения x, столбец – значения y):  Рис. 17. Фрагмент таблицы для построения поверхности 2. В ячейку В2 (на рис. 17 она выделена темным фоном) ввести формулу, ссылающуюся на ячейки В1 и А2, как на значения x и y, причем ссылки на данные ячейки смешанные. 3. Скопировать формулу вниз и вправо, затем выделить все полученные числовые данные, вызвать мастер диаграмм, выбрать тип диаграммы «Поверхность», вид – первый. Результат приведен фрагментарно на рис. 18.  Построить поверхности, приведенные в таблице 3. Таблица 3  Лабораторная работа № 4. Логические функции. Методические указания Рассмотрим наиболее часто используемые логические функции ЕСЛИ(), И(), ИЛИ(). Синтаксис функций: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) И(логическое_значение1; логическое_значение2; ...) ИЛИ(логическое_значение1;логическое_значение2; ...) Задание 1. Применение логических функций для решения расчетной задачи. В таблице приведен список деталей, изготовленных рабочим за смену, с указанием общего количества деталей, деталей с браком и себестоимости в рублях одной детали. Рассчитать сумму заработка рабочего за день, зная, что он получит 7% от итоговой суммы за вычетом штрафных удержаний. При расчете учесть, что рабочему начисляется штраф 5% от суммы по каждому виду изделия, если брак по нему составляет 10% и более. П о р я д о к р а б о т ы :  1. Создать таблицу по образцу (рис. 19). 2. Подсчитать Сумму по каждому виду изделия (количество*себестоимость). 3. Подсчитать % брака путем деления Брака на Количество и умножения на 100. 4. Используя функцию ЕСЛИ, подсчитать размер штрафа. При этом в пункте «логическое выражение» должно быть сравнение процента брака с 10%. Например, запишем здесь F5>=10 (в ячейке F5 содержится процент брака по шайбам). Тогда в пункте «значение_если_истина» мы должны записать формулу, по которой рассчитывается размер штрафа (т.е. сумма*5/100), а в пункте «значение_если_ложь» напишем 0 (брак в пределах нормы, и штраф в этом случае не будет взыскиваться). 5. Подсчитать итог путем вычитания штрафа из суммы. 6. Подсчитать «К выдаче», просуммировав «Итого» и взяв от этой суммы 7%. Для проверки К выдаче 451,66 Теперь усложним задачу. Допустим, при тех же исходных данных, процент штрафа начисляется иначе. Пусть при проценте брака от 10% до 20% штраф будет по-прежнему 5%, а при проценте брака более 20% штраф будет в размере 12% от суммы. Рассчитать сумму к выдаче при новых условиях. П о р я д о к р а б о т ы : 1. Скопировать основную расчетную таблицу на Лист 2 и затем на Лист 3. Удалить формулы из столбца Штраф. 2. Данную задачу можно решить двумя способами. На Листе 2. реализуем первый способ: - вызовем функцию ЕСЛИ и в пункте «логическое_выражение» укажем F5<10. Теперь в пункте «значение_если_истина» мы должны указать 0 (штраф не берется, т.к. процент брака менее 10%). А в пункте «значение_если_ложь» необходимо снова вызвать функцию ЕСЛИ (или просто написать от руки ее название прописными буквами русского алфавита без пробелов). - в новой вызванной функции также нужно заполнить три пункта. «Логическое_выражение» будет проверять на истинность условие, что процент брака более 20% (F5>20). Тогда «значение_если_истина» будет содержать формулу подсчета штрафа в размере 12% от суммы. «Значение_если_ложь» будет содержать формулу подсчета штрафа в размере 5% от суммы. - если все выполнено правильно, то К выдаче должно пересчитать автоматически: К выдаче 440,1 3. Реализуем второй способ решения задачи с помощью функции И () на Листе 3: - вызовем функцию ЕСЛИ и в пункте «логическое_выражение» укажем И(F5>=10;F5<20). Здесь будет проверяться на истинность условие, что процент брака составляет более 10% включительно, но менее 20%.Теперь в пункте «значение_если_истина» мы должны указать формулу подсчета штрафа в размере 5% от суммы; - в пункте «значение_если_ложь» необходимо снова вызвать функцию ЕСЛИ. В новой вызванной функции также нужно заполнить три пункта. «Логическое_выражение» будет проверять на истинность условие, что процент брака более 20% (F5>20). Тогда «значение_если_истина» будет содержать формулу подсчета штрафа в размере 12% от суммы. «Значение_если_ложь» будет содержать в этом случае 0. Задание 2. Построение графика функции  Рис. 20. Построение графика функции с использованием логических функций  *** Используя логические функции и правила построения графиков функций и поверхностей, построить график 
|