Лабораторная работа 1 Основы работы в Excel Цель работы научить студента открывать существующие книги, сохранять книги на новом месте и под новыми именами, работать с листами книги,
 Скачать 1.3 Mb.
|
|
Delete? 4. Команда Удалить применительно к столбцу, строке, ячейке, интервалу. 5. Команда Вставить применительно к столбцу, строке, ячейке, интервалу. Куда вставляется строка/столбец? 6. Для чего применяется команда Специальная вставка Последовательность ее выполнения. 7. Как выполняется копирование формата Для чего оно необходимо 8. Заполнение интервала ячеек с формулами Как влияет адресация в формуле на заполнение 9. Заполнение интервала а) одинаковым текстом, б) одинаковыми числами, в) одинаковыми датами, г) числами с шагом 1, д) датами с шагом 1? 10. Заполнение интервала числами с заданным шагом. Лабораторная работа № 6 Использование функций Excel Цель работы научить студента использовать функции Excel для решения различных задач. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Расчет количества банок краски Необходимо покрасить некоторую поверхность. Вычислить сколько банок краски требуется для покраски заданной поверхности, и какую сумму необходимо заплатить, если известны - площадь окрашиваемой поверхности - расход краски на кв. м - вес одной банки краски - цена одной банки краски. Указания к выполнению. 1.1. Переименовать лист 1 в Покупка краски. 1.2. Создать таблицу, в которой a) занести исходные данные в ячейки А3:В6, b) требуемое количество банок краски вычислить как частное отделения веса краски, требуемого на покраску, навес краски в банке. Это значение будет необязательно целым числом, c) вычислить целое количество банок краски, округляя требуемое количество банок в большую сторону. Для этого используется функция ОКРУГЛВВЕРХ(число,0), 334 27 d) вычислить стоимость краски как произведение количества банок и цены банки. 1.3. Расширить таблицу, чтобы можно было при покупке выбрать наиболее подходящую тару (в магазине одна и та же краска может продаваться в различной упаковке, для чего в следующие столбцы занести вес банки краски и ее цену и скопировать формулы для вычисления количества банок и стоимость краски. 334 182 320 Задание 2 Расчет оценки спортивных состязаний В некоторых видах спортивных состязаний выступление каждого спортсмена независимо оценивается несколькими судьями, затем из всей совокупности оценок удаляются наибольшая и наименьшая оценки, а для оставшихся вычисляется среднее арифметическое, которое и идет в зачет спортсмену. Если наибольшую оценку выставили несколько судей, то из совокупности оценок удаляется только одна такая оценка аналогично поступают с наименьшими оценками. Определить оценку, которая пойдет в зачет спортсмену. Указания к выполнению. 2.1. Перейти на лист 2 и переименовать его в Оценки. 2.2. В строку Оценки судей занести оценки, выставленные судьями одному участнику соревнований. 2.3. Используя функции НАИБОЛЬШИЙ(диапазон;1) и НАИМЕНЬШИЙ(диапазон;1) вычислить наибольшую и наименьшую оценки. Второй параметр этих функций задать равным 1, так как требуются первое наибольшее и первое наименьшее значения. 2.4. Вычислить зачетную оценку как частное отделения суммы оценок, вошедших в зачет, на количество этих оценок. Сумму оценок, вошедших в зачет, вычислить как сумму всех оценок функция СУММ) без наибольшей и наименьшей оценок. Количество этих оценок равно количеству всех оценок, уменьшенному на 2. Для определения количества всех оценок использовать функцию СЧЕТ. Замечание В данной задаче вместо функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ можно было бы использовать функции МАКС и МИН, которые являются частным случаем функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ 28 Задание 3 Статистическая обработка результатов наблюдений 3.1. Переименовать лист 3 в Статистика. 3.2. Создать шапку таблицы статистической обработки результатов наблюдений, в которой оцениваются предел текучести и временное сопротивление. 3.3. Заполнить первые три колонки наблюдений, причем колонку Дата - через прогрессию, указывая рабочие дни недели. 3.4. Используя Мастер функций, вычислить математическое ожидание как среднее арифметическое предела текучести. 3.5. Заполнить следующие две колонки Отклонение текучести и Отклонение сопротивления как разницу между соответствующим ожиданием и наблюдаемым значением. 3.6. Используя Мастер функций, вычислить максимальное и минимальное отклонение текучести и сопротивления, а также размах как разницу между минимальными максимальным значением. 3.7. Для оценки изменчивости (разброса) служат стандартное отклонение, вычисляемое от исходных наблюдаемых величин – предела текучести и временного сопротивления. Функция СТАНДОТКЛОН, вычисляющая стандартное отклонение, находится в категории "Статистические. 3.8. Отформатировать таблицу, как на рисунке. Дата измерения Предел текучести Временное сопротивление Отклонение текучести Отклонение сопротивления 24,11 40,15 -0,297 0,263 01.09.1998 23,08 39,87 0,733 0,543 02.09.1998 23,80 40,30 0,013 0,113 03.09.1998 23,44 40,57 0,373 -0,157 04.09.1998 24,09 41,02 -0,277 -0,607 07.09.1998 23,91 40,29 -0,097 0,123 08.09.1998 24,20 41,21 -0,387 -0,797 09.09.1998 23,67 40,11 0,143 0,303 10.09.1998 23,96 40,29 -0,147 0,123 11.09.1998 23,87 40,32 -0,057 Математическое ожидание 23,8130 40,4130 Максимальное отклонение 0,7330 0,5430 Минимальное отклонение -0,3870 -0,7970 Размах 1,1200 1,3400 Стандартное отклонение 0,3409 0,4131 Оценка углеродистой стали, кГ/мм 2 Задание 4 Создание таблицы вычисления величины вкладов Переименовать лист 4 во Вклады. Вычислить, какой вклад будет на счете в банке к концу заданного срока, если известен первоначальный вклад и годовая процентная ставка. Результат оформить в виде таблицы, выполняя действия. 29 4.1. Ввести и отформатировать заголовок Расчет вкладов по процентам 4.2. Ввести величину начального вклада. 4.3. Ввести и отформатировать значения срока и процента. Замечание Заголовок срок\процент ввести в ячейку А в две строки, используя клавиши Alt+Enter и смещая пробелами слово процент. Диагональ провести, задавая соответствующую границу. 4.4. В ячейку B6 ввести функцию вычисления вклада БС, задавая в качестве первого аргумента помесячную процентную ставку (если известна годовая, второго аргумента - срок вклада, третий аргумент не задается, четвертого аргумента – величину начального вклада, взятую со знаком "минус, т.к. вкладчик отнимает у себя эту сумму. Правильно задать адресацию аргументов. 4.5. Отобразить на графике тенденцию изменения величины вклада для срока 12 мес. в зависимости от процентной ставки, используя мастер диаграмм a) выделить диапазон процентной ставки, нажать Ctrl и выделить значения вкладов для срока 12 мес. (без первого столбца, b) нажать кнопку на стандартной панели инструментов, c) на первом шаге выбрать точечный тип диаграмм, видна втором шаге перейти на вкладку Ряди в строку Имя ввести через 12 мес, e) на третьем шаге ввести заголовок диаграммы Суммы вкладов на счете, f) на четвертом шаге задать размещение диаграммы на отдельном листе. 30 Задание 5 Создание платежного поручения 5.1. Переименовать лист 5 в Платежное поручение. 5.2. Создать платежное поручение на отправку стальных труб по железной дороге a) присвоить имена ячейкам, содержащим оптовую цену, удельную плотность, тариф перевозки, ставку НДС b) вычислить объем трубы с учетом размерностей по формуле 6 2 2 10 L d D 4 V , где D – наружный диаметр трубы (см L – длина трубы (м, d – внутренний диаметр трубы ( t 2 D d , где t – толщина стенки трубы, см 31 c) вычислить вес трубы в тоннах с учетом размерностей как произведение объема трубы и удельной плотности d) вычислить стоимость перевозки труб как произведение веса трубы, количества труби оптовой цены e) вычислить тариф перевозки и НДС как соответствующий процент от стоимости f) вычислить всего как сумму стоимости, тарифа перевозки и НДС в тыс. руб. 5.3. Скрыть столбцы листа, в которых находятся Объем трубы и Вес трубы. 5.4. Выполнить объединение ячеек и отформатировать таблицу. Платежное поручение Грузоотправитель: Станкозавод Станция отправления: Москва Грузополучатель: ЧТЗ Договор-заказ № от Оптовая цена, т/руб. 500 Удел. плотность,г/см 3 Тариф перевозки 5% Ставка НДС 28% Отправка труб стальных бесшовных горячекатанных по ГОСТ 8732-78 из стали Наружный диаметр трубы, мм Толщина стенки трубы, мм Длина трубы, м Количество труб, шт Стоимость перевозки труб, руб. Тариф перевозки, руб. НДС, руб. Всего, тыс. руб 10 12 40 7 694,39 384,72 2 154,43 10,23354 114 8 6 50 3 136,94 156,85 878,34 4,17213 168 12 9 27 5 609,21 280,46 1 570,58 7,46025 203 16 6 10 2 213,62 110,68 619,81 Всего к оплате 654,16 932,71 5 223,16 24,81003 Руководитель М.П. Гл. бухгалтер ПОЛУЧИЛ ОТПРАВИЛ 32 Лабораторная работа № 7 Реализация разветвляющихся алгоритмов Цель работы научить студента редактировать таблицы. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Задание 1 Использование выражений сравнения в условиях 1.1. На листе 1 в ячейке B2 оформить вычисление значения выражения y1=ln(x 2 -10x). Ячейке A2 присвоить имя X, которое использовать в выражении. Если вычисление невозможно, то выдать сообщение "Вычисление невозможно. 1.2. В ячейке С вычислить значение выражения 7 x 1 2 y . Если вычисление невозможно, то выдать сообщение "Вычисление невозможно. 1.3. Подставляя в ячейку А различные значениях, х, х, вычислить значения у, у. а) б) в) Задание 2 Использование логических функций в условиях 2.1. На листе 2 оформить вычисление значения 7 x ) x 10 x ln( y 2 . Ячейке A2 присвоить имя Х, которое использовать в выражении. Если вычисление невозможно, то выдать сообщение "Вычисление невозможно. 2.2. Вычислить значение y при x=6, x=-7, x=20, изменяя значение х в ячейке. а) б) Задание 3 Вложенная функция ЕСЛИ 3.1. На листе 3 при вычислении значения y из задания 2 выдать одно из сообщений "Деление на ноль, "Отрицательное значение под логарифмом, Нулевое значение под логарифмом. 3.2. Вычислить значение y при x=6, x=18 , ха) б) Задание 4 Построение таблицы функции с ветвлениями На листе 4 построить таблицу функции на интервале [-3,11] с шагом 1. 4.1. Значения X занести в строку, используя автозаполнение. 7 , 17 x 2 ( , ) 4 x ( 9 3 1 , 4 x y 2 2 x если x если если. Для вычисления Y использовать вложенную функцию ЕСЛИ. 4.3. Отформатировать таблицу. 4.4. Построить график функции, используя Мастер диаграмм Задание 5 Функции СЧЕТЕСЛИ и СУММЕСЛИ На листе создать массив А по формуле а = cos ( + i), i:=1 10. Вычислить сумму и количество отрицательных элементов массива. 5.1. В строку 1 занести номера элементов от 1 до 10, используя автозаполнение. 5.2. В строку 2 занести формулы для вычисления элементов массива. 5.3. Вычислить сумму отрицательных элементов, используя функцию СУММЕСЛИ. 5.4. Вычислить количество отрицательных элементов, используя функцию СЧЁТЕСЛИ. 34 Задание 6 Расчет рейтингов 6.1. Открыть файл из лабораторной работы 4, лист Баллы. Скопировать таблицу Баллы студентов за контрольные точки по информатике" на лист 6 текущего документа. Закрыть файл из л/р 4. 6.2. Исправить исходные данные на баллы по бальной шкале. 6.3. Добавить два столбца Количество пропусков и Рейтинг. Занести в столбец Количество пропусков исходные данные. Вычислить значения в столбце Рейтинг следующим образом если у студента нет пропусков, то рейтинг равен среднему баллу, а если есть пропуски, то его рейтинг уменьшается на 5 баллов. 6.4. Добавить столбец Оценка, содержащая оценку по пятибалльной системе, исходя из рейтинга баллов – 2, 25-49 баллов – 3, 50-74 балла – 4, выше 75 баллов - 5. 6.5. Добавить столбец Зачет, в котором будет стоять значение "сдано" или "не сдано" в зависимости от значения оценки. 6.6. Под таблицей подсчитать a) средний рейтинг по группе, используя функцию СРЗНАЧ(ДИАПАЗОН РЕЙТИНГОВ, b) количество студентов в группе, используя функцию СЧЕТЗ(ДИАПАЗОН ФАМИЛИЙ, которая подсчитывает количество непустых значений в диапазоне, c) количество сдавших зачет, используя функцию СЧЕТЕСЛИ(ДИАПАЗОН ЗАЧЕТОВ, КРИТЕРИЙ, d) успеваемость, как отношение количества сдавших студентов к общему количеству, отформатировать в процентном виде, e) качество знаний, как отношение количества студентов, имеющих рейтинг выше 34 баллов (использовать функцию СЧЁТЕСЛИ), к общему количеству студентов, отформатировать в процентном виде. 6.7. Изменить баллы у студентов и проследить за изменением отчетных показателей. 35 Задание 7 Задача о вкладах Определить, какая сумма будет на счете, если начальный вклад 150 000 руб. размещен под 12% годовых на 3 года, а проценты начисляются каждые полгода. Определить, достаточно ли будет этой суммы для покупки дома стоимостью 250 000 руб. Замечание Для решения использовать функцию БC(процент, период,<пустой аргумент, начальный вклад Функция рассчитывает стоимость вклада на основе постоянной процентной ставки. Пересчитать процент и период для полугода. Начальный вклад задавать отрицательным. 7.1. Занести исходные данные. 7.2. В ячейку B4 занести функцию БС. 7.3. В ячейку B6 ввести формулу для вывода одного из сообщений Достаточно для покупки дома или Недостаточно для покупки дома. 7.4. В ячейку С занести одно из слов лет, год, года. Слово зависит от значения периода если период лежит в пределах от 10 доили остаток отделения периода на 10 равен 0 или лежит в пределах от 5 до 9, то заносится слово лет. Если остаток отделения периода на 10 равен 1, то заносится слово год, в остальных случаях заносится слово года Для вычисления остатка отделения на 10 используется функция ОСТАТ(число;10). Ответ 212 777.87 руб. – Недостаточно для покупки дома 7.5. Изменить начальный вклад на 180 000 руб. Проследить за изменением результатов. Задание 8 Вычисление корней квадратного уравнения 8.1. Перейти на новый лист и присвоить ему имя Корни. Создать таблицу для вычисления и вывода корней квадратного уравнения. Замечание Корни квадратного уравнения ax 2 +bx+c=0 вычисляются по формулам a D b x 2 2 , 1 , где D=b 2 -4ac 36 A B 1 2 a 2 3 b -4 4 c -8 5 Дискриминант x1 3.236068 8 Решение квадратного уравнения уравнение имеет два действительных корня Дискриминант Решение квадратного уравнения уравнение имеет два одинаковых корня Дискриминант -16 6 7 8 Решение квадратного уравнения уравнение не имеет действительных корней. Ввести заголовок и исходные данные в строки 1-4. Дать имена ячейкам, где содержатся коэффициенты квадратного уравнения. 8.3. В ячейку B5 занести формулу для вычисления дискриминанта, используя имена ячеек. Дать имя ячейке, где содержится дискриминант. 8.4. В строке 6, анализируя дискриминант, с помощью вложенной функции ЕСЛИ отобразить одно из сообщений "Уравнение не имеет действительных корней, "Уравнение имеет два действительных корня, Уравнение имеет два одинаковых корня. Замечание Уравнение имеет два действительных корня, если дискриминант положителен. Уравнение имеет два одинаковых корня, если дискриминант равен 0. Уравнение не имеет действительных корней, если дискриминант отрицателен. 8.5. В ячейке А, используя вложенную функцию ЕСЛИ для анализа дискриминанта, отобразить заголовки в виде a) х, если уравнение имеет два разных корнях, если уравнение имеет один корень, c) ничего не отображать, если уравнение не имеет действительных корней. 8.6. В ячейку B7, используя функцию ЕСЛИ, занести либо формулу вычисления большего (знак "+" перед дискриминантом) корня, либо ничего не отображать. 8.7. В ячейке А, используя функцию ЕСЛИ, отобразить текст a) х, если уравнение имеет два разных корня, b) ничего не отображать в остальных случаях. 8.8. В ячейку B8, используя функцию ЕСЛИ, занести либо формулу вычисления меньшего (знак "-" перед дискриминантом) корня, либо ничего не отображать. 8.9. Отформатировать таблицу. 8.10. Для контроля изменить значения на a) а, b=-4, c=4, b) a=2, b=-4, c=4 37 Задание 9 Обработка данных метеостанции 9.1. Открыть таблицу Осадки, расположенную в файле осадки Таблица содержит количество осадков в миллиметрах и построена на основе наблюдений метеостанции г. Екатеринбурга. Скопировать таблицу на новый лист текущей книги (содержимое таблицы приведено в Приложении В. 9.2. В ячейки F2:K7 занести таблицу , 9.3. Заполнить таблицу, используя функции МАКС, МИН, СРЗНАЧ, СУММ, СЧЁТЕСЛИ. Засушливыми считаются месяцы с количеством осадков ниже 10 мм. 9.4. В ячейки К занести таблицу для одного года в следующем порядке a) в ячейку К ввести год, b) в ячейки К10:К14 ввести функции ЕСЛИ, анализирующие введенный год и вызывающие функции МАКС, МИН, СРЗНАЧ, СУММ, СЧЁТЕСЛИ с разными аргументами. Если введен год, отсутствующий в таблице, выдать сообщение "Данные отсутствуют. 2.4 Последовательно менять в ячейке К значения года и наблюдать изменения в итоговых данных. Контрольные вопросы 1. Назначение функции ЕСЛИ. Количество параметров и их назначение. Примеры. 2. Структура условия функции ЕСЛИ. Что является значением условия 3. Выражения отношения операции, операнды, примеры. 4. Логические операции название, назначение параметров. Примеры. 5. Как вычисляются значения логических операций Примеры. 6. Вложенные условия. Варианты вложения. Примеры. 7. Отображение текстовых сообщений в функции ЕСЛИ. Примеры. 8. Подсчет количества ячеек внутри диапазона, удовлетворяющих заданному критерию. Примеры. 9. Суммирование ячеек, заданных критерием. Примеры. 38 Лабораторная работа № 8 Условное форматирование Цель работы научить студента форматировать таблицу в зависимости от некоторого условия, применять различные параметры условного формата значение и формула. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Порядок выполнения Условное форматирование с параметром "значение" Задание 1 Форматирование таблицы осадков 1.1. Открыть файл из л/р 7. Скопировать таблицу, содержащую осадки, в текущую книгу. Переименовать лист в Таблица. 1.2. Создать листы с именами Таблица, Таблица. Скопировать таблицу осадков на листы Таблица, Таблица. 1.3. На листе Таблица отформатировать таблицу следующим образом a) желтым фоном ячейки с количеством осадков менее 10 мм, b) синим фоном ячейки с количеством осадков более 100 мм. 1.4. На листе Таблица отформатировать таблицу для каждого года следующим образом a) выделить красными жирными символами ячейки с максимальным количеством осадков, b) выделить зеленым курсивом ячейки с количеством осадков больше среднего для контроля под каждым столбцом подсчитать среднее значение осадков, c) выделить синими жирными символами ячейки с минимальным количеством осадков. Задание 2 Форматирование температурных полей 2.1. Открыть книгу teplo.xls, расположенную в папке с заданиями. Книга содержит таблицу распределения температурных полей наружной стены здания содержимое файла приведено в Приложении В. 2.2. Скопировать таблицу на новый лист текущей книги. 2.3. Отформатировать таблицу следующим образом a) выделить отрицательные температуры – диапазоны |