лаба. Лабораторная работа 2 по дисциплине Информационные технологии
 Скачать 0.55 Mb.
|
|
Пояснения к выполнению. Образец для вычисления дискриминанта = B3^2-4*A3*C3. Образец для формулы корня: =ЕСЛИ(D3<0;"решения нет";(КОРЕНЬ(D3)-B3)/(2*A3)) или =ЕСЛИ(D3<0;"нет решения";ЕСЛИ(D3=0;-((B3)/(2*A3));(КОРЕНЬ(D3)-B3)/(2*A3))) Функция КОРЕНЬ находится в категории «Математические». Упражнение 4. «Инициалы» Задание. В таблице есть три отдельных столбца с фамилиями, именами и отчествами сотрудников. Создать ещё один столбец, в котором средствами мастера функций автоматически будут формироваться фамилии с инициалами (табл. 6). ПРИ ВЫПОЛНЕНИИ ЗАДАНИЯ ИСПОЛЬЗОВАТЬ СВОИ ДАННЫЕ !!! Пример решения: Таблица 6
Пояснения к выполнению. В этом упражнении следует использовать функцию обработки строковых величин ЛЕВСИМВ, выделяющую из записи заданное количество символов слева. Логика формирования записи в столбце ФИО следующая: =Фамилия &” “&ЛЕВСИМВ(Имя, 1)&”.“&ЛЕВСИМВ(Отчество, 1)&”.” т.е. = B2&" "&ЛЕВСИМВ(C2;1)&"."&ЛЕВСИМВ(D2;1)&"." Знак & (амперсанд) означает сложение строк. В кавычках записаны пробел и точки соответственно. Либо можно использовать формулу «СЦЕПИТЬ»: =СЦЕПИТЬ(В2;" ";ЛЕВСИМВ(С2;1);".";ЛЕВСИМВ(D2;1);".") Фильтры Фильтрация относится к процедурам анализа баз данных, т.е. позволяет из основной таблицы (базы данных) создавать вторичные таблицы, содержащие записи, удовлетворяющие заданным условиям. Чтобы войти в режим фильтрации в EXCEL, нужно выделить область заголовков, затем выбрать пункт меню ДАННЫЕ - ФИЛЬТР – АВТОФИЛЬТР. Следует иметь в виду, что если таблица имеет сложную многоуровневую шапку, то нужно выделять область нижних заголовков. Инструмент АВТОФИЛЬТР позволяет фильтровать только по одному столбцу (полю). При необходимости фильтровать по нескольким столбцам, следует использовать расширенный фильтр. Но всё-таки для сложного анализа баз данных лучше использовать специальную программу Access. Упражнение 5. «Аукцион». Задание. Создать таблицу по образцу рис. 42. (допустимо использовать свои данные) Выполнить следующие фильтрации : Создать фильтр, в котором отображался список только покупателей из Киева скопировать фильтр под основной таблицей. Дать заголовок новой таблице «Фильтр по городу Киев». Создать фильтр, в котором отображался список всех покупателей, принявших участие в торгах до 01.07, скопировать этот фильтр под предыдущим. Дать имя таблице «Фильтр по дате». Сделать сводку по продаже книг №3. Дать имя «Фильтр по книге №3». Создать сводку клиентов, сделавших самые большие закупки (Сумма>15 000) .Скопировать и дать имя «Фильтр по сумме». Оформить таблицу с помощью команды автоформат (стиль Объёмный 2). Отсортировать таблицу по алфавиту городов, а, в свою очередь, каждый город по дате. Создать условное форматирование столбца "Сумма" по принципу: если сумма>10000,то цвет шрифта красный, а цвет фона жёлтый.  Рис. 42 Чтобы задать режим фильтрации, нужно выделить заголовки таблицы (область А2:Н2) и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. В результате в заголовках появятся стрелочки с разворачивающимися списками (рис. 43). Следует иметь в виду, что если таблица имеет сложную шапку, то нужно выделять нижние заголовки.  Рис. 43 Чтобы выполнить фильтр по городу Киев, нужно развернуть список критериев в столбце «Город» и выбрать Киев. Скопировать новую таблицу ниже на листе под основной и дать имя «Фильтр по городу Киев». Для выполнения фильтра по дате следует развернуть список критериев в столбце «Дата» и выбрать строку УСЛОВИЕ. Заполнить бланк условия в соответствии с рис. 44.  Рис. 44 Скопировать фильтр ниже по листу. Назвать таблицу «Фильтр по дате». Фильтр по книге №3 выполнить аналогично фильтру по городу. Фильтр по сумме выполнить с помощью бланка условия : БОЛЬШЕ 15000 Скопировать фильтр ниже по листу. В результате получится список фильтров как на рис. 45. Закончить фильтрацию. Выделить область А2:Н2 и выбрать пункт меню ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР. При этом исчезнут галочки в шапке основной таблицы. Выделить область А2:Н15 и пойти в пункт меню ФОРМАТ – АВТОФОРМАТ. Выбрать стиль «Объёмный 2». Для выполнения сортировки выделить область А2:Н15 и выбрать пункт меню ДАННЫЕ – СОРТИРОВКА. Убедиться, что сортировка будет производиться по городу, а затем по дате. Так как выделена была вся таблица, перестроится не только столбец «Город» и «Дата», но и другие данные (рис. 46). Для выполнения условного форматирования нужно выделить область Н2:Н15 и выбрать пункт меню ФОРМАТ – УСЛОВНОЕ ФОРМАТИРОВАНИЕ. Заполнить бланк условного форматирования, щёлкнув по кнопке Формат… . В разделе «Шрифт» задать красный цвет шрифта, а в разделе «Вид» заказать желтую заливку (рис. 47):  Рис. 45  Рис. 46  Рис. 47 Упражнение 6. «Корни системы линейных уравнений». ПРИ ВЫПОЛНЕНИИ ЗАДАНИЯ ИСПОЛЬЗОВАТЬ СВОИ ДАННЫЕ !!! Задание. Найти корни системы линейных уравнений с помощью функции МОПРЕД мастера функций, вычисляющую определитель матрицы. x1 + 2x2 + 3x3 + 4x4 =30 -x1 + 2x2 - 3x3 + 4x4 =10 x2 - x3 + x4 =3 x1 + x2 + x3 + x4 =10 Пояснения к выполнению. Схема решения представлена на рис. 48. Для простоты набора определители обозначаются буквой D. Напоминаем формулу Крамера для решения корней системы линейных уравнений: Xi=Di/D  Результат вычислений Рис. 48 Подбор параметра. Поиск решения. Макросы 1. Подбор параметра В Excel есть возможность выполнять подбор значений в одной ячейке , являющейся параметром , так чтобы в целевой ячейке установилось значение, равное требуемому (рис. 53 ). Эту задачу можно выполнить через пункт меню СЕРВИС- ПОДБОР ПАРАМЕТРА… Подбор параметра осуществляется методом последовательных приближений. Адрес целевой ячейки  Требуемое значение в целевой ячейке Адрес параметра Рис. 53 При использовании функции подбора параметра необходимо, чтобы ячейка с целевым значением содержала ссылку на ячейку с изменяемым значением, т. е. эти ячейки должны быть связаны формулами. Решение может быть и не найдено, например, уравнение не имеет решений. В силу используемого метода решение может быть найдено приблизительно, т.е. значение в целевой ячейке после выполнения операции не совсем совпадает с требуемым. В этом случае можно поступить следующим образом: Увеличить количество итераций поиска (по умолчанию 100)- СЕРВИС – ПАРАМЕТРЫ- ВЫЧИСЛЕНИЯ – ПРЕДЕЛЬНОЕ ЧИСЛО ИТЕРАЦИЙ. Увеличить относительную погрешность (по умолчанию 0,001) – СЕРВИС – ПАРАМЕТРЫ – ВЫЧИСЛЕНИЯ – ОТНОСИТЕЛЬНАЯ ПОГРЕШНОСТЬ. Изменить начальное значение параметра. Если на параметр или на значение в целевой ячейке требуется наложить ограничения, или в задаче нужно использовать несколько параметров, то такая задача может быть решена средством «Поиск решения», приведенным в следующем разделе. Упражнение 7. «Проходной балл» Задание. По результатам экзаменов и заданному плану приёма абитуриентов (табл. 11) вычислить значение проходного балла, необходимого для обеспечения требуемого количества студентов. При выполнении задания использовать свои данные (Фамилии, Набрано баллов) Образец выполнения: Таблица 11
Порядок выполнения. Вывести выражение для вычисления значений в столбце «Принят, не принят». Для этого нужно составить Логическую конструкцию, в условии поставить адрес проходного балла. (рис. 54).  Рис. 54 При проходном балле 0 , естественно, все будут приняты. B ячейке D12 организовать подсчёт количества принятых с помощью функции СЧЁТЕСЛИ. Раз все приняты, то в ячейке D12 появится число 8. Выделить ячейку D13. Вызвать диалог «Подбор параметра». Данные - Работа с данными - Анализ "что-если" - Подбор параметра... Заполнить его как на рис. 55 , щёлкнуть ОК.  Рис. 55 В результате подсчета параметра значение проходного бала станет 35 и количество принятых станет равно плану приёма, (табл. 12 ) Таблица 12
|