Размещено на http://www.allbest.ru/

Практическое занятие №6. Табличный процессор MS Excel. Ввод и редактирование данных. Адресация ячеек

Цель работы: повторить основные понятия табличного процессора Excel, сформировать умение вводить и редактировать данные, применять различного вида адресации при решении задач.

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

Класс программ, предназначенных для создания и обработки табличных данных в электронном виде, называется табличными процессорами или электронными таблицами. Документ Excel называется рабочей книгой, книга представляет собой набор рабочих листов, обозначаются они как Лист 1 (Sheet 1). Документ Excel сохраняется в виде файла с расширением *.xls. Строки нумеруются целыми числами от 1 до 65536, а столбцы или колонки обозначаются буквами латинского алфавита A, B, …, Z, AA, AB, …IV. На пересечении строки и столбца располагается основной структурный элемент таблицы - ячейка (cell). К содержимому ячейки можно обратиться по ее адресу (ссылке), например, A5.

В ячейках рабочего листа могут находиться данные: Текст, Константы и Формулы. В одну ячейку можно ввести до 32767 текстовых или числовых символов. excel диаграмма база access

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

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

Перемещение указателя ячейки (активной ячейки):

1. в начало строки (HOME);

2. в начало таблицы (CTRL+HOME);

3. в последнюю заполненную ячейку (CTRL+END);

Выделение различных диапазонов:

1. прямоугольного диапазона:

выделить первую ячейку/ перетаскивание мышью или выделить первую ячейку/ на последней ячейке SHIFT+ щелчок.

2. столбцов (строк):

щелчок на имени столбца (строки) / перетаскивание (для нескольких столбцов (строк).

3. всей таблицы:

щелчок на кнопке в верхнем левом углу таблицы.

4. несмежных диапазонов:

выделить первый диапазон/ выделить следующий при нажатой CTRL.

Обратить внимание на элементы выделенного диапазона. Найти маркер заполнения (квадратик в нижнем правом углу выделенного диапазона).

Ввод и редактирование данных:

Ввести в ячейки текст, число. Обратить внимание на представление данных в ячейках, строке формул. Выполнить редактирование данных можно двумя способами: в ячейке (двойной щелчок) и в строке формул.

Установка ширины столбцов:

Установить указатель мыши на границу столбцов/ перетаскивание мышью.

Для установки ширины столбцов равной 2 символам необходимо: Выделить столбец/ Контекстное Меню / Ширина столбца. Повторить изменение ширины столбцов можно с помощью команды меню Главная/Формат

Авто заполнение ячеек:

Первый способ:

ввести в две соседние ячейки два первых элемента прогрессии;

выделите блок, состоящий из двух заполненных ячеек;

Установите указатель мыши на правый нижний угол выделенного блока. Указатель мыши станет черным крестиком - это маркер заполнения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз.

Второй способ:

Выберите команду Главная > Редактирование > Заполнить > Прогрессия. В появившимся окне выберите тип расположение по столбцу, шаг, предельное значение.

Использование автозаполнения:

Ввести в ячейку A1 значение 1.

Ввести в ячейку A2 значение 2.

Выделить две ячейки A1 и A2. Установить указатель мыши на маркер автозаполнения и перетащить маркер автозаполнения вниз.

(текущая дата)	(текущее время)
СПИСОК СТУДЕНТОВ ГРУППЫ
№ п/п	Фамилия и.о.	Дата рождения	Средний балл
1.	Иванов И.И.	12.05.1982	7,0
2.	Петров П.П.	23.07.1981	8,0
3.	Сидоров С.С.	01.12.1982	7,5
Средний балл группы 7,5

Для ввода текущей даты необходимо нажать комбинацию клавиш Ctrl+;

Для ввода текущего времени необходимо нажать комбинацию клавиш Ctrl+Shift+;

Операции с листами:

1. переименование: двойной щелчок по названию листа на его ярлыке или п. Переименовать контекстного меню ярлыка или Главная > Ячейки > Формат > Переименовать лист; удаление: меню Главная > Ячейки > Удалить > Удалить лист;

2. перемещение или копирование: Пункт Переместить/скопировать лист контекстного меню ярлыка. Для копирования нужно установить флажок Создавать копию в окне Переместить или скопировать или Главная > Ячейки > Формат > Переместить или скопировать лист. Пример, установить указатель мыши на ярлык Лист2 / Перетаскивание на ярлык Лист3 (при нажатой CTRL)

3. добавление: щелкнуть по ярлыку листа, перед которым вставляется новый лист; в контекстном меню ярлыка выбрать п. Добавить; в окне диалога; в окне Вставка выбрать ярлык Лист; нажать кнопку ОК или Главная > Ячейки > Вставить > Вставить лист.

Ввод формул для ячеек смежного диапазона:

Пример, ввести формулу =срзнач(C4:F4) в ячейку G4:

1. ввести =срзнач( в ячейку G4;

2. выделить указанный диапазон C4:F4 ячеек в таблице.

3. набрать ).

Ввод формул для ячеек несмежного диапазона:

Пример, ввести в ячейку W4 формулу для подсчета среднего балла за год:

1. Ввести =срзнач( в ячейку W4.

2. Выделить ячейки G4,L4,R4,V4, удерживая клавишу CTRL.

3. Набрать )

Копирование формул на смежные ячейки:

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

Копирование формул на несмежные ячейки:

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

Защита данных листа с предварительным указанием диапазонов, не подлежащих блокировке:

Указать диапазоны, не подлежащие защите: Выделить диапазон/ Контекстное Меню / Формат ячеек /Защита/ снять флажок защиты.

Защитить весь лист: Рецензирование /группа Изменения / Защитить лист.

Адресация ячеек. В Excel различают 3 типа адресации ячеек: абсолютная, относительная и смешанная. При копировании формулы из одной ячейки в другую автоматически изменяются адреса ячеек, входящих в состав формул. Такая адресация ячеек называется относительной. Относительные ссылки используются в Excel по умолчанию. Но иногда необходимо не изменять адрес некоторой ячейки при копировании формулы. Такой адрес называется абсолютным и Абсолютная ссылка задается путем указания символа доллара перед номером строки и столбца, например $A$2 либо нажатием клавиши <F4>.

Смешанная ссылка представляет собой комбинацию абсолютной и относительной ссылок, когда для строки и столбца используются разные способы адресации, например, $A1, B$2. При копировании формулы абсолютная часть ссылки не изменяется.

Все формулы в Excel должны начинаться с символа “=”, заканчивается ввод формулы нажатием Enter

Примеры формул:= 2*5^ 3+4=A1+A2=A1+Cos (5,282)

Редактирование формул: Чтобы начать редактировать содержимое ячейки, нужно сначала выделить эту ячейку. Далее включить режим редактирования, нажав клавишу F2 или выполнив двойной щелчок мышью. Для редактирования формул можно нажать кнопку = в строке формул. В режиме редактирования активизируется строка формул, в которой виден текст формулы, а не результат ее вычисления.

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

Форматирование данных. Прежде всего, нужно выделить ячейки, в которых надо изменить формат данных. После этого можно либо открыть правой кнопкой мыши контекстное меню и вызвать в нем команду Формат ячеек, либо вызвать команду Главная > Ячейки > Формат > Формат ячеек. В любом случае на экране появится диалоговое окно Формат ячеек. Используя его можно установить форматы представления данных в ячейках: числовые форматы выравнивание, границы и вид ячеек, а также определить степень защиты данных.

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

1. Открыть программу MS EXCEL. Сохранить книгу под именем Лабораторные работы в свою папку.

2. Переименуйте текущий рабочий лист (Лист1) на Задание 1.

3. Ввести исходные данные таблицы1.

4. Оформить таблицу по образцу.

5. Для того чтобы объединить ячейки, необходимо выделить ячейки и нажать кнопку или в меню Формат Ячейки

6. Для того чтобы изменить ориентацию текста, необходимо

· Выделить ячейку, в которой находится текст, вызвать контекстное меню>Формат ячеек, во вкладке Выравнивание перейти в поле Ориентация (Главная > Выравнивание > Ориентация)

· Щелкните на красный ромб , удерживая нажатой левую кнопку мыши перетащите на 90^о.

7. Для того чтобы подсчитать сумму профессионального взноса составляющего 5 % от оклада, необходимо:

· Выделить ячейку D5 и ввести формулу =С5*5%

· Аналогично и для всех налогов и премии.

8. Вычислить сумму, получаемая на руки после вычислении всех налогов и начислении премии в ячейки Н5.

9. Подвести итог суммы всего оклада всех сотрудников и сумму зарплаты получаемая ими на руки. Для этого

· Выделить блок ячеек H5:H12.

·

Нажмите на кнопку Автосумма на стандартной панели инструментов(Главная > Редактирование > Автосумма или нажатием сочетания клавиш Alt+= ).

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

1. Перейдите на Лист2. Переименуйте его на Задание2.

2. Создайте таблицу, внесите в нее исходные данные задачи

3. Подсчитайте выручку от продажи в долларах и тенге.

· В ячейку Е6 введите следующую формулу: =С6*D6

· В ячейку F6 введите следующую формулу: =Е6*В3. В3 является абсолютной адресацией, нажмите на F4 , должно получится $В$3.



Упражнение 3: Составьте таблицу, вычисляющую n-й член и сумму арифметической прогрессии. Формула n-го члена арифметической прогрессии: a_n=a₁+d(n-l) и формула суммы п первых членов арифметической прогрессии:S_n=(a₁+a_n)*n/2, где a₁ - первый член прогрессии, a d -- разность арифметической прогрессии, используя абсолютную ссылку. Первый член, который равен -2, а разность равна 0,725.

1. Перейдите на Лист3. Переименуйте его на Задание3.

2. Самостоятельно запишите формулы.

Задания для самостоятельной работы:

№	Задание
Вариант 1	Продукцией городского молочного завода являются молоко, кефир и сметана. На производства 1т молока, кефира и сметаны требуется соответственно 1010, 1020 и 9450 кг молока. Прибыль от реализации 1т молока, кефира и сметаны соответственно равны 300, 220 и 1360 тенге. Было изготовлено молока 123т, кефира 342т, сметаны 256т. Требуется при помощи электронной таблицы рассчитать: прибыль от реализации каждого вида изделий, общую прибыль, долю (в процентах) прибыльности каждого вида изделий от общей суммы, расход молока (сырья);
Вариант 2	На книжную базу поступили 3 наименования книг: словари, книги по кулинарии и пособия по вязанию. Они были распределены по трем магазинам: «Книжный мир», «Дом книги» и «Глобус». В «Книжный мир» поступило словарей - 10400 экземпляров, кулинарных книг - 23650 экземпляров, пособий по вязанию - 1500 экземпляров; в «Дом книги» - 10300 словарей, 22950 кулинарных книг и 1990 пособий по вязанию; в «Глобус» соответственно 9100, 23320 и 2500 экземпляров. В первом магазине было продано словарей - 8945 экземпляров, кулинарных книг - 19865 экземпляров, пособий по вязанию - 873 экземпляра; во втором магазине было продано словарей - 9300 экземпляров, кулинарных книг - 21900 экземпляров, пособий по вязанию - 1020 экземпляров; в третьем магазине соответственно было продано 8530, 18100 и 2010 экземпляров. Требуется при помощи электронной таблицы рассчитать: общее количество книг каждого наименования поступивших на книжную базу; процент продажи каждого наименования книг в каждом магазине; количество книг, оставшихся после реализации.
Вариант 3	На предприятии работники имеют следующие оклады: начальник отдела - 1000 тенге., инженер 1 кат. - 860 тенге., инженер - 687 тенге., техник - 315 тенге., лаборант - 224 тенге. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% от оклада ежемесячной премии. Со всех работников удерживают 20% походный налог, 3% профсоюзный взнос и 1% в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15% районного коэффициента, работники филиала, расположенного в районе крайнего севера, имеют 70% районный коэффициент и 50% надбавки от начислений. Расчет заработной платы должен быть произведен для каждого филиала в отдельности. Результатом должны быть две таблицы. Требуется при помощи электронной таблицы рассчитать суммы получения каждой категории работников.
Вариант 4	Производственная единица изготавливает изделия трех видов: П1, П2 и П3. Затраты на изготовление единицы продукций П1, П2 и П3 составляют 7, 15 и 10 (тенге.) соответственно. Прибыль от реализации одного изделия данного вида соответственно равна 20, 16 и 25 (тенге.). План производства изделий П1 - 200482шт., П2 - 43292 шт., П3 - 1463012 шт. В январе было изготовлено П1 - 135672 шт., П2 - 60712 шт., П3 - 1456732 шт. Требуется при помощи электронной таблицы рассчитать в тенгах и долларах (курс доллара - величина изменяющаяся): плановые затраты на производство; прибыль от реализации каждого вида изделий; прибыль, полученную предприятием в январе; процент выполнения плана в январе по каждому виду изделия.
Вариант 5	Кондитерская фабрика для производства трех видов карамели А, В и С используют три вида сырья: сахарный песок, патоку и фруктовое пюре. Норма расхода сырья на 1т карамели соответственно равны (т): Вид сырья Карамель А В С Сахарный песок 0,6 0,5 06 Патока 0,2 0,4 0,3 Фруктовое пюре 0,2 0,1 0,1 Общее количество сырья каждого вида, которое может быть использовано фабрикой, соответственно равно 1500, 900 и 300 тонн. За месяц фабрика изготовила карамели вида А - 820, В - 900, С - 400 (т.) Требуется при помощи электронной таблицы рассчитать: расход сырья каждого вида; количество оставшегося сырья; количество карамели вида А, на производство которого хватить оставшегося сахара.

Контрольные вопросы

1. Что такое табличный процессор?

2. Какие способы автозаполнения вы знаете?

3. Как называется документ программы MS Excel?

4. Какие виды адресации вы знаете? Чем они отличаются друг от друга?

5. Каким образом можно объединить ячейки?

Практическое занятие №7. Вычисление значений функции. Мастер функции. Построение графиков и диаграмм. Логические функции. Построение графиков функции с двумя и тремя условиями

Цель работы: сформировать умения вычислять значения функций, строить графики функций.

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

Функции вводятся обычным набором с клавиатуры или более предпочтительным способом - с помощью Мастера функций.

Поскольку многие сложные и несложные вычисления встречаются очень часто, то программа Excel предлагает более 200 заранее запрограммированных функций. Функциями в Microsoft Excel называют вычислительную операцию по определенному алгоритму и формуле. Мастер функций предназначен для ввода готовых формул в вычисления. Кнопка Мастер функций на панели инструментов имеет вид f_x

Для быстрого поиска необходимой функции они разделены по категориям: 10 недавно использовавшихся, Полный алфавитный перечень, Финансовые, Дата и время, Математические, Статистические, Ссылки и массивы, Работа с базой данных, Текстовые, Логические и Проверка свойств. Встроенный Мастер функций помогает правильно применять функции. Он позволяет построить и вычислить большинство функций.

Функции в Microsoft Excel представляют собой формулы, которые имеют один или несколько аргументов. В качестве аргументов указываются числовые значения или адреса ячеек.

Например:=СУММ(А5:А9) - сумма ячеек А5, А6, А7, А8, А9;

=СРЗНАЧ(G4:G6) - среднее значение ячеек G4, G5, G6.

Функции могут быть вложенными одна в другой, например: =ОКРУГЛ(СРЗНАЧ(H4:H8);2).

Ввод аргументов функции. Если у функции есть аргументы, появится окно ввода аргументов, элементы которого показаны на рисунке.

1- имя функции, для которой вводятся аргументы;

2 - поля ввода аргументов;

3 - кнопка сворачивания окна ввода. Если окно свернуто, развернуть его можно повторным нажатием на эту кнопку;

4 - текущие значения аргументов и функции;

5 - область описания функции;

6 - кнопка вызова помощи.

Ввести аргументы функции можно следующим образом:

а) набрать вручную необходимые адреса или диапазоны ячеек;

б) отметить нужные ячейки или диапазоны ячеек на рабочем листе. Окно ввода аргументов при этом можно свернуть (кнопка 3) или перетащить в сторону.

Описание основных функций.

Категория Математические.

ABS (число) - модуль числа.

COS(число) - косинус заданного числа.

EXP(число) - возвращает число е, возведенное в указанную степень.

LN(число) - возвращает натуральный логарифм числа.

КОРЕНЬ (число) - возвращает положительное значение квадратного корня.

ПИ () - возвращает число с точностью до 15 цифр.

СТЕПЕНЬ (число; степень) - возвращает результат возведения в степень;

СУММ (число1; число2; ...) - возвращает сумму всех чисел, входящих в список аргументов;

Статистические функции.

МАКС(аргумент1; аргумент2;…) - ищет максимальный из аргументов;

МИН(аргумент1; аргумент2;…) - ищет минимальный из аргументов;

СРЗНАЧ(аргумент1; аргумент2;…) - вычисляет среднее своих аргументов;

СЧЕТЕСЛИ(диапазон; условие) - подсчитывает число аргументов в диапазоне, отвечающих условию

Арифметические операции:

Сложение	+
Вычитание	-
Умножение	*
Деление	/
Возведение в степень	^

Диаграммы - это графическое представление данных. Они используются для анализа и сравнения данных, представления их в наглядном виде.

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

Редактирование диаграмм выполняется как с помощью контекстного меню, так с помощью команд управляющего меню Диаграмма. К редактированию диаграммы относится:

Изменить тип и формат диаграммы;

Изменить исходные данные:

переопределить исходный интервал ячеек, на основании которых построена диаграмма;

переопределить ориентацию рядов и их название;

изменить данные, используемые для подписей оси Х;

изменить параметры диаграммы (заголовки, оси, линии сетки, легенду, подписи данных);

изменить размещение диаграммы.

Упражнение 1: Построение графика функции y=cos²(2x)sin(x)+e^-x¦x¦ для хс шагом 0,1.

Создайте на листе 1 следующую таблицу: значения переменной х занесите маркером заполнения.

В ячейку В3 введите формулу: =cos(2*A3)^2*sin(A3)+exp(-3)*ABS(A3), в остальные ячейки формулу скопируйте.

Выделите значения функции и запустите Мастер диаграмм.

Выберите тип диаграммы - График.

Во вкладке Ряд, в Подписях оси Х выделите диапазон значений Х. (во вкладке Выбрать данные, в Подписях оси, Изменить и, таким образом, выделить диапазон ячеек)

Установите свои параметры диаграммы.

Разместите диаграмму в данном листе и переименуйте лист 1 как График функции.

Упражнение 2: Построение диаграмм

Создайте на листе 2 следующую таблицу:

Введите в столбец Итого формулу Сумм, укажите диапазон ячеек и автозаполнением заполните оставшиеся ячейки.

Построите для этой таблицы диаграммы двух типов:

Круговую диаграмму, демонстрирующую премиальные выплаты Иванову по месяцам.

Выделите ячейки с числовыми данными и ячейки, которые берутся в качестве подписей к секторам

На панели инструментов или в меню Вставка выберите Диаграммы и тип диаграммы Круговая

В появляющихся диалоговых окнах задаем параметры диаграммы. Диаграммы поместите на данном листе.

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

Лист 2 переименуйте как Диаграммы.

Логические функции служат для выполнения вычислений в зависимости от выполнения некоторого условия. В условиях могут использоваться операции сравнения =, >, <, <>(не равно), >= (больше или равно), <= (меньше или равно).

Функция ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) результатом является значение1, если логическое_выражение истинно и значение2 в противном случае.

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

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

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

Пример: ЕCЛИ (логическое_выражение; значение1; значение2) - Пример: в ячейке A1 набрано число 30000, а в ячейке B1 формула =ЕСЛИ(A1<20000; 12; 15). Результатом будет число 15, т.к. условие не выполняется.

Функции И, ИЛИ служат для создания сложных условий:

И (логическое_выражение1; логическое_выражение2;....) - возвращает значение “ИСТИНА”, если все аргументы имеют значение “ИСТИНА”, а в противном случае -“ЛОЖЬ”.

ИЛИ (логическое_выражение1; логическое_выражение2;...) - возвращает значение “ИСТИНА”, если хотя бы один из аргументов имеет значение “ИСТИНА”, а противном случае - “ЛОЖЬ”.

П/ры: =ЕСЛИ (И(A1>=20000;A1<40000);15;18) - вычисленное значение равно 15 при величине A1 от 20000 до 40000 и равно 18 в противном случае.

Можно создавать сложные условия и вложением функций ЕСЛИ. Пример:

=ЕСЛИ (A1<20000; 12; ЕСЛИ (A1<40000; 15; 18)) - если величина A1 меньше 20000, вычисленное значение равно 12, иначе если она меньше 40000, то результат равен 15, а в противном случае (то есть, А1 больше 40000) ,формула вернет значение 18.

Упражнение 1. Задан рейтинг по какому-либо предмету в процентах. Если он не ниже 45%, то студент аттестован по данному предмету, в противном случае не аттестован.

1. Лист1 переименовать на Задание 1.

2. Заполнить таблицу по образцу.

3. Определить аттестован ли студент. Выделить ячейку С3. Выполнить следующую команду. Вставка>Функция (Формулы > Логические). В окне Мастера функции выбрать категорию Логические, имя функции ЕСЛИ.

4. В появившимся окне функции ЕСЛИ. В поле Логическое выражение устанавливаем условие, проверяем ячейку В3, является ли оно больше 45%, если да, то студент аттестован, если нет то не аттестован. Для этого:

В поле Логическое выражение записываем условие: В3>=45%

· В поле Значение если_истина: «аттестован»

· В поле Значение если_ложь: «не аттестован»

· Нажимаем на ОК.

5. Копируем формулу для остальных.

Упражнение 2. Аттестация проводилась по 3 предметам и надо определить аттестован ли студент по всем предметам.

1. Перейти на Лист2. Переименовать его на Задание2.

2. Заполнить таблицу по образцу.

3. Значения необходимо записать в процентах. Для этого

§ Выделить диапазон В3:D7. Вызвать контекстное меню>Формат ячеек. Перейти на вкладку Числа. Числовой формат выбрать Процентный, Число десятичных знаков установить 0.

4.

В ячейке Е3 вызвать функцию ЕСЛИ.

5. На уровне строки формулы с левой стороны щелкнуть на

6. В списке выбрать Другие функции, в категории Логические выбрать функцию И.

7. В поле Логическое_значение1: B3>=45%, Логическое_значение2: C3>=45%, Логическое_значение3: D3>=45%.

8. Щелкнуть в строке формул на ЕСЛИ.

9. В поле Значение если_истина набрать слова: «аттестован»

10. В поле Значение если_ложь набрать слова: «не аттестован»

11. Нажать на ОК

12. Результат протянуть и для остальных ячеек.

Упражнение 3: Построение графика функции с двумя условиями, шаг=0,2

Перейдите на Лист 3 переименуйте его на Задание 3.

В столбце А запишите все значения Х с шагом 0,2, начиная с ячейки А2.

В столбце В2 запишите формулу:

=Если(А2<=0; (1+2*A2^2-sin(A2)^2)^(1/2); (2+A2)/(2+EXP(-0.1*A2))^(1/3)) или с помощью Мастера функции выберите функцию Если, в появившемся диалоговом окне заполните условие, выражения 1 и выражения 2.

Автозаполнением скопируйте формулы в остальные ячейки.

Постройте график функции.

Задания для самостоятельной работы:

Задание 1. Постройте графики функций с шагом 0,2:

№ вар	При x	Задание 1	Задание 2
1			Найдите дискриминант D= b2-4ac квадратного уравнения ax2+bx+c=0 … и с помощью функции Если выведите на экран количество корней квадратного уравнения. ( Если D>0, то два корня,)
2			Если вес пушного зверька в возрасте от 6-ти до 8-ми месяцев превышает 7 кг, то необходимо снизить дневное потребление витаминного концентрата на 125 г. Количество зверьков, возраст и вес каждого известны. Выяснить на сколько килограммов в месяц снизится потребление витаминного концентрата.
3			Отдел работает над двумя проектами, причем каждый сотрудник (ввести фамилии 5 сотрудников на усмотрение) работает только над одним проектом. По каждому проекту начисляется премию (% от оклада). Проценты премий для каждого проекта различны. За 1-й проект - 0,25, за 2-ой проект - 0,35.
4			Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти товаров или стоимость покупки превышает 5000 тг. Составить ведомость, учитывающую скидки: покупатель, количество купленных товаров, стоимость покупки, стоимость покупки со скидкой. Выяснить сколько покупателей сделало покупки, стоимость которых превышает 5000 тг.
5			Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то - в 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

Контрольные вопросы

1. Какими способами можно ввести функции в табличном процессоре Excel?

2. Какие категории функций вы знаете?

3. Какие статистические функции вы знаете?

4. Что такое диаграмма? Как стоят диаграммы?

5. Какие действия относятся к редактированию диаграмм?

6. Какие логические функции вы знаете?

7. Какое выражение будет выполняться в функции Если при истинном значений логического выражения?

8. Когда функция И дает истину?

9. Когда функция ИЛИ дает истину?

10. Какие операции используются при построении логического выражения в функции Если?

Практическое занятие №8.«Сортировка и фильтрация данных. Сводные таблицы. Консолидация данных»

Цель работы: сформировать умение использовать табличный процессор в качестве простой базы данных, сортировать и фильтровать данные. Сформировать умение строить сводные таблицы, использовать консолидацию данных.

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

Электронные таблицы Excel можно использовать в качестве базы данных. В этом случае электронную таблицу называют списком или базой данных Excel и используют соответствующую терминологию: Строка списка - запись базы данных; Столбец списка - поле базы данных.

Сортировка это переупорядочивание строки в таблице по любому полю и выполняется командой ДанныеСортировка с установкой необходимых параметров. Целью сортировки является упорядочивание данных.

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

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

Автофильтрация

Команда ДанныеФильтр / Ctrl+Shift+L для каждого столбца строит список значений, который используется для задания условий фильтрации. В каждом столбце появляется кнопка списка, нажав которую можно ознакомиться со списком возможных критериев выбора.

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

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

1) Критерий сравнения; 2) Вычисляемый критерий.

Технология использования расширенного фильтра состоит из двух этапов:

Этап 1 - формирование области критериев поиска;

Этап 2 - фильтрация записей списка.

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

Сводная таблица нужна, например, для того чтобы узнать насколько хорошо продается тот или иной товар в каждом регионе. Сводную таблицу можно создать из нескольких источников. Чаще всего она создается на основе списка или базы данных Excel.

Упражнение 1: Сортировка данных.

В своей папке создайте рабочую книгу Лаб_10_Ex

Лист 1 переименуйте как Список, создайте таблицу приведенную ниже: Введите имена полей, а во второй строке должны ввести записи т.е. информацию. Ввод с помощью формы данных. Выберите из меню команду Данные/Форма. Появится сообщение Exсel. Ответьте ОК.

Номер группы	Номер зач. книжки	Код предмета	Таб. № препод.	Вид занятия	Дата	Оценка
133	11	П1	А1	Л.	12.12.04	4
133	12	П2	А2	Пр.	25.12.04	4
133	13	П1	А3	Л.	12.12.04	5
133	14	П2	А1	Пр.	20.12.04	2
133	15	П1	А2	Л.	12.12.04	3
133	16	П2	А1	Л.	25.12.04	4
133	17	П1	А2	Пр.	12.12.04	5
133	18	П1	А3	Пр.	25.12.04	5
134	19	П1	А3	Л.	7.12.04	4
134	20	П2	А1	Пр.	25.12.04	5
134	21	П1	А3	Л.	7.12.04	5
134	22	П2	А2	Пр.	25.12.04	2
134	23	П1	А2	Л.	12.12.04	4
134	24	П1	А1	Л.	25.12.04	5
134	25	П2	А3	Л.	7.12.04	3

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

§ Установите курсор в таблице, выполнить команду меню Данные> Сортировка;

§ В окне сортировка диапазона выбрать сортировку по столбцу “ Код предмета ” и установите опцию по возрастанию.

Выполнить сортировку данных по возрастанию по 3 столбцам: по номеру группы, по коду предмета., таб №преподавателя. Для этого следуют установить курсор в таблице, выполнить команду меню Данные> Сортировка и в диалоговом окне сортировка установить:

§ в строке Сортировать по - поле «по номеру группы» по возрастанию;

§ в строке Затем по - поле «по коду предмета», по возрастанию;

§ в строке Последнюю очередь, по поле «таб №преподавателя», по возрастанию.

Упражнение 2: Фильтрация данных.

Переименуйте Лист2 на Автофильтр и скопируйте на него исходную базу данных из листа Список.

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

§ Примените Автофильтрацию, установив курсор в область списка и выполните команду ДанныеФильтр / Ctrl+Shift+L

§ В столбце Таб. № преподавателя нажмите на кнопку и из списка условий отбора выберите А1;

§ Перейдите в столбец Вид занятия и из списка фильтра выберите Л., затем в столбце Оценка из списка условий отбора выберите (Условие…) и в диалоговом окне сформируйте условие отбора >3; т.е. выберите условие Больше, а слева наберите 3.

§ Посмотрите на результат.

Отмените результат автофильтрации. Для этого нажмите на в каждом поле где вы применили Автофильтр и выберите Все.

Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.

Отмените результат автофильтрации.

Упражнение 3: Расширенный фильтр

1. Переименуйте Лист3 на Расширенный фильтр и скопируйте на него исходную базу данных.

Этап 1. Формирование диапазона условий по типу Критерий сравнения

2. При использовании Расширенного фильтра необходимо с начало задать интервал критериев - это область, где задаются критерии фильтрации. Для этого:

§ Скопируйте имена полей (шапка) списка в другую область например в А18 на том же листе.

3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 134 по предмету П2 на оценки 2 или 5. Для этого:

§ Запишите критерии поиска в интервал критериев

Номер группы	Номер зач. книжки	Код предмета	Таб. № препод.	Вид занятия	Дата	Оценка
134		П2				2
134		П2				5

Этап 2. - фильтрация записей списка.

4. Произведите фильтрацию записей.

§ Поместите курсор в область базы данных

§ Выполните команду Данные>Дополнительно

§ В диалоговом окне Расширенный фильтр установите следующие параметры:

§ Установит переключатель скопировать результат в другое место

§ В поле Исходный диапазон укажите диапазон таблицы: А1:G16

§ В Диапазон условии укажите диапазон таблицы критерии: А18:G20.

§ В поле Поместить результат в диапазон укажите ячейку А21.

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

Упражнение 4. Создать сводную таблицу для отчетов по продаже компьютеров сети из трех магазинов подводящую сумму продаж по месяцам.

1. Создать заданную таблицу в Excel на Лист1 и переименуйте на Свод_табл. Для ускорения работы использовать копирование и автозаполнения данных.

2. Вызовите Мастер Сводных таблиц, выполнив команду ДанныеСводная таблица,

3. В появившимся окне установите переключатель в положение в списке или базе данных Microsoft Excel, щелкните по кнопке Далее.

4. Укажите диапазон, содержащий исходные данные (выделить всю таблицу с данными), щелкните по кнопке Далее, в следующим окне выберите переключатель в положение новый лист.

5. Кнопка Макет (для Excel 2000); в появившемся диалоговом окне, в котором создается структура сводной таблицы, выбрать поле, по которому будут подводиться итоги сводных таблиц, и перетащить кнопку, соответствующую этому полю, в область Данные (перетащить кнопку Стоимость в область Данные). Двойной щелчок по кнопке Стоимость в области Данные приведет к отображению диалогового окна Вычисление поля сводной таблицы, которое позволяет установить операцию, на основе которой будут подводиться итоги (выбрать операцию Сумма).

6. Выбрать поля, которые будут образовывать строки сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Строка. Перетащить кнопку Магазин в область Строка.

7. Выбрать поля, которые будут образовывать столбцы сводной таблицы, и перетащить кнопки, соответствующие этим полям, в область Столбец. Перетащить кнопку Месяц в область Столбец.

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

9. Для перерасчета сводной таблицы, если изменились данные, на основе которых строилась эта сводная таблица: выделить ее, команда Данные / Обновить данные.

10. Проиллюстрируйте данные полученной сводной таблицы на графике.

Упражнение 5. Создание консолидации данных по расположению и по категориям

1. Переидите на Лист2 и переименуйте на Консол

2. Создайте на листе Консол. таблицу расчета заработной платы (за январь и за февраль).

3. Вычислите сумму подоходного налога и сумму к выдаче

4. Выполните консолидацию по расположению, для этого: Установите курсор в первую ячейку области, где будет располагаться консолидированная таблица. (Например в А10)

· Выполните команду Данные, Консолидация.

· В диалоговом окне << Консолидация>> выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца,

· Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7.

· Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

· Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек F2:I7.

· Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

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

5. Скопируйте обе таблицы с листа Консол.распол. на лист Консол.категор. и измените вторую таблицу (за февраль): вставьте новый столбец Премия и заполнить его, добавьте строку с фамилией Дятлов.

6. Сделайте консолидацию данных по категориям:

· Установите курсор в первую ячейку области, где будет располагаться консолидированная таблица.

· Выполните команду Данные, Консолидация.

· В диалоговом окне << Консолидация>> выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца.

· Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек A2:D7.

· Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

· Установите курсор в окне << Ссылка>>, перейдите на лист с исходными таблицами и выделите блок ячеек F2:J8.

· Нажмите кнопку < Добавить>, в окне << Список диапазонов>> появится ссылка на выделенный диапазон.

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

Задания для самостоятельной работы:

Скопируйте исходную базу данных с листа Список на новый лист и отсортируйте Список1 …

Установите автофильтр и выберите данные по следующему условию: см. таблицу

Установите расширенный фильтр: см. таблицу

№	Задание 1	Задание 2	Задание 3
1	По номеру группы и коду пред	Записи с 7.12.04 по 12.12.04	Гр. 133, оценки 4 или 5, П1
2	По № зач.кн. и виду занятий	Предметы с кодом П1 и оценки 4	Гр. 134, оценки 2 или 4
3	По таб. № преподав. и № группы	№ зач.кн. с15 по 20	А2, оценки 4 или 5, Пр.
4	По виду занятий и дате	Таб. № преподав. А1 и А2»	Гр. 133, А1 или А2, Л.
5	По дате и оценке	Оценки 2 и 3	Гр. 134, № зач.>21 , Л.

Контрольные вопросы

1. Что такое база данных?

2. Что значит сортировать данные?

3. Какие бывают виды фильтрации?

4. Чем отличается сортировка от фильтрации?

5. Каким образом устанавливается расширенный фильтр?

6. Для чего служит сводная таблица?

7. Как создать сводную таблицу?

8. Что такое консолидация данных?

Практическое занятие №9: Матрицы (массивы) и матричные формулы. Решение систем линейных уравнений

Цель работы: сформировать умения использовать функции Excel для выполнения различных операции над матрицами (массивами), решать системы линейных уравнений методом обратной матрицы.

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

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

При вводе Ошибка! Недопустимый объект гиперссылки.

Этот тип формулы массива может упростить модель листа, заменив несколько отдельных формул одной формулой массива. Формулы массивов используют несколько множеств значений, которые называются массивами аргументов. Диапазон массива - это блок ячеек, который имеет общую формулу массива. Действия над массивами значительно отличаются от действий над отдельными ячейками.

Функция МУМНОЖ ( ) определяет результат произведения нескольких матриц. При использовании этой функции важно помнить, что можно перемножить не только 2 квадратные, но и 2 прямоугольные матрицы, но при этом количество строк второй матрицы должно быть равно количеству столбцов первой матрицы, а число столбцов второй числу строк первой.

Функция МОПРЕД ( ) вычисляет определитель квадратной матрицей. Результатом вычисления является число.

Функция МОБР ( ) возвращает обратную матрицу.

Упражнение 1. Сложение, вычитание и произведение массивов.

1. Переименуйте Книгу на Лаб_11_Матрицы, а Лист 1 на Задание 1

2. Оформите следующие таблицы:

1.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Пример 1		Пример 2		Пример 3
2		15	12	16			7	8	9			8	9	7
3	А=	18	5	16		В=	4	9	15		C=	15	80	21
4		19	2	25			2	6	9			31	25	63
5
6
7	А+В=					В-С=					А2=
8

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

§ Выделите диапазон ячеек, в который требуется ввести формулу массива, т.е B6:D8

§ Нажмите на знак =

§ Выделите диапазон матрицы А

§ Нажмите на знак +

§ Выделите диапазон матрицы В

§ Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

§ У вас в строке формулы должно получится следующая формула: = {B2:D4+G2:I4}

3. Аналогичные действия выполните и для примера 2 и примера 3

Упражнение 2. Умножение матриц А и В, нахождение обратной матрицы и определителя матриц

1. Переименуйте Лист 2 на Задание 2

2. Оформите следующие таблицы:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		2	6	4			-1	3,1	7
2	А =	3,1	7,1	1		В =	2,3	6	1		С=А*В=
3		-1,1	0	-0,2			0	2	3,22
4
5
6	А-1=					А*А-1=					Det(A)=
7
8

3. Вычислите произведение матриц А*В. Для этого:

§ Выделите область L1:N3

§ Вызовите Мастер функций, выберите категорию Математические и функцию МУМНОЖ, откроется окно Палитры формул.

§ Для ввода аргумента функции в поле Массив 1 выделите первую матрицу, затем перейдите в поле Массив 2 и выделите вторую матрицу, т.е матрицу В

§ Подтвердите ввод формулы массива <Ctrl>+<Shift>+<Enter>

4. Найдите обратную матрицу к матрице А. Для этого:

§ Выделите область, в которую хотите поместить результат, B5:D7

§ Введите формулу, для этого:

§ Вызовите мастер функции и в категории математические выберите функцию МОБР.

§ В поле Массив выделите матрицу А

§ Для подтверждения ввода формулы, содержащей массив, нажмите <Ctrl>+<Shift>+<Enter>

§ Проверьте результат, вычислив произведение исходной матрицы и обратной к ней

5. Вычислите определитель матрицы А. Для этого

§ Установите курсор в ячейку L6, вызовите функцию МОПРЕД, и выделите значения матрицы А.

Упражнение 3.Необходимо решить систему линейных уравнений

Для реконструкции 3 цехов завода выделены деньги. Для 1 цеха 510000, для второго 180000, для третьего 480000. Для всех цехов необходимо купить станки трех видов А, В, С. Причем для 1 цеха 4 станка А, 8 станков В и 1 станок С. Для 2 цеха 1 станок А, 2 станка В и 1 станок С и для 3 цеха 1 станок А, 5 станков В и 4 станка С. По какой максимальной цене можно покупать станки.

Обозначим максимальные цены . Тогда

Представим данные в виде матриц А, х, b,

где матрица А - матрица коэффициентов, х - матрица неизвестных и b-матрица свободных чисел

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

Наиболее простыми методами решения системы линейных уравнений является метод Крамера и метод обратной матрицы.

<...