Практическое задание 2

1. Применить к числовым данным первой таблицы различные числовые форматы и операции:

- изменить (затем восстановить) число десятичных знаков;

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

- убрать (и восстановить) линии сетки таблицы;

- скрыть (и затем восстановить) столбец Оклад;

- создать для первой таблицы обрамление и скопировать оформление на Листы 2 и 3;

- вставить во вторую таблицу две пустые строки после строки с итоговыми данными;

- во всех трех таблицах отсортировать данные по фамилии.

2. Для всех четырех таблиц построить диаграммы и поместить их под таблицами:

- для первой -гистограмму, представляющую значения окладов для каждого сотрудника,

- по данным второй таблицы -круговую диаграмму,

- по данным третьей и четвертой -графики изменения уровня выплат и значений функции;

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

- отредактировать отдельные элементы всех диаграмм - оформить цветом, ввести заливку.

3. Выполнить просмотр данных рабочей книги:

- предварительный просмотр страниц рабочей книги,

- четвёртого листа с закреплением строки заголовка.

___________________

3. Функции и вычисления ( логические, табличные, матричные функции )

Excelимеет более 500 встроенных функций. Любую из них можно вводить с клавиатуры непосредственно в строке формул окнаExcelили используяМастер функций (Вставка-Функция или значокf_x). Функция вExcel- этоимяфункциииаргументыв круглых скобках, которые разде­ляются точкой с запятой. Аргументами в функциях могут быть константы и адреса переменных (ссылки).

Функция СУММ (А1; В5; С10)выдаст сумму чисел, заданных в аргу­ментах-ссылках;МИН(С2:Е4)на­йдёт минимальное среди чисел указан­ного в аргументе диапазона ячеек. В качестве аргументов могут применяться другие (вложенные) функ­ции; например,СУММ (МИН (C2:E4)); A1; B5; C10).

Все функции разделены на группы. Самую большую группу составляютвычислительныефункции Они также разделены на подгруппы. К ним относятсяматемати­че­ские,тригонометрические,статисти­ческиеи др. Функциитекстовойгруппы выполняют преоб­ра­зо­вания чисел вASCII-коды (Американский стандартный код обмена информацией) и обратно, определяют длину данных, выделяют из строк подстроки, объединяют подстроки, удаляют пробелы.Информационныефункции определяют состояние яче­ек, выдают типы ошибок.Финан­совыеобрабатывают платежи, инвестиции, процентные ставки. Подробная информация обо всех функциях есть вСправкедиалоговых оконМастера функций.Там же можно получить информацию по син­таксису выбранной функции и прототипам ее аргу­мен­тов.Мастер функцийприме­няют также при наборе сложных формул с большим числом аргументов; он упро­щает на­бор функций, так как разбивает эту операцию на отдель­ные шаги, выдает подсказки, отобра­жает ре­зультат каждого шага на экране и берет на себя часть работы, добавляя в формулу поля для ввода аргументов, скобки, точки с запятой. Его окно можно перемещать.

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

Автосуммирование. Вызывается кнопкой (S) на стандартной панели инструментов и предна­значена для быстрого вычисления суммы зна­чений ячеек, расположенных в последова­тель­ных строках или столбцах. Если выделить интервалC3:F5 в представленном ниже фраг­мен­те таблицы и нажать кнопкуАвтосумма, то все пустые клетки заполнятся суммар­ными значениями.

Чтобы увидеть последовательность обработки чи­сел по заданным формулам, приме­няют Трассировку вычисленийвыделяют ячейку с результатом, затемЗа­висимости-Влияющие ячейки и появляются линии, указывающие порядок использования аргументов при вычислении. А если выделить ячейки-аргументы, задатьЗа­ви­симости-За­висимые ячейки, стрелки укажут результаты.

		C	D			E		F
3		56	78			35
4		67	35			49
5
	C			D	E		F
3	56			78	35		169
4	67			35	49		151
5	123			113	84		320

Логическая функция ЕСЛИ имеет 3 аргумента и позволяет по условию выбирать раз­ные решения. Её синтаксис:ЕСЛИ ( лог_выраж; знач.1; знач2 ). Семантику (смысл, действие) данной функции можно пояснить следующим образом:

если лог.выраж.(1-й аргумент) -истина, то результат - 2-й аргумент, иначе - 3-й..

Любое логическое выражение (“высказывание”) может иметь одно из двух значений: TRUE(истина) илиFALSE (ложь).

В качестве логических выражений используются: - логические отношения-два арифметич. выражения, соединенные символом операции отношения, например,x>k-1; это простое логическое выражение;

- логические одночлены- два или более логич.отношений, соединенные логической опера­цией "И":x > 10 И x < 15 ( "х лежит между 10 и 15"); эту операцию называют также логическим умножением; вЕxcelэти выражения записывают по-другому в префиксной форме-символ логической операции находитсяперед аргумента­ми):И (x > 10; x < 15);

- логические многочлены - два или более логических одночлена, соединенные операцией"ИЛИ" (логическое сложение):ИЛИ ( x=2; y=2; z=2 )- т.е. "хотя бы одна из трех переменных -x,y,z- равна2" .Пример использования функцииЕСЛИ в одном из вариантов расчёта подоход­ного налога:=ЕСЛИ ( C4>100000; 20%*C4; 12%*С4) ; здесь функцияЕСЛИвыдаёт два возмож­ных ре­зультата в зависимости от оклада. Если в качестве 3-го аргумента функцииЕСЛИ использо­вать эту же - вложенную - функ­цию, то можно получить3решения: = ЕСЛИ ( Х>10; формула1; ЕСЛИ (X<5; формула2; форм.3)).

Функция ЕСЛИнеявно применяется в функцияхСУММЕСЛИ и СЧЁТЕСЛИ, вычисляющихсуммуиколичествотех значений, которые удовлетворяют заданному условию.

Табличныефункции предназначены для обработки данных одной или нескольких таб­лиц. В данном разделе рассматриваются функцииВПР, ГПР, ЧАСТОТАиРАНГ.

Функции ВПР(верти­кальный просмотр таблиц-по столбцам) и ГПР(горизонтальный-по строкам) позволяют сопоставить данные двух таблиц-основной и вспомогательной (справоч­ной). Справочные таблицы содержат граничные значения диапазонов, а основ­ные-произво­ль­­ные значения, каждое из которых должно быть отнесено к одному из диапазонов.

Рассмотрим пример обработки результатов тестирования группы студентов по 3 предметам.

Необходимо для каждого студента определить общую оцен­­ку в десятибальной системе по результатам Табл.1с ис­поль­зованием справочной таблицыТабл.2. Справочная таблица содержит оцен­ки, соответствующие сумме баллов. Если сумма находится в пределах от 1 до 7, то оценка – 2; если от 8 до 14, то – 3 и т.д. Данные в этой таблице должны быть упорядоче­ны.

Для решения этой задачи можно было бы применить функцию ЕСЛИ с самовложением,но коли­чество её вложений ограничено семью, поэтому применим функциюВПР. Она имеет 4 аргумента:

Рез-ты тестирования Табл.1

Фамилия	Тест1	Тест2	Тест3
Атоян	20	21	22
Витов	9	7	10
Фокина	15	17	14
Ким	19	24	23
Троль	13	18	21
Зотова	22	15	19
Веткин	24	17	17
Шубина	10	10	10
Сомова	13	12	13
Жук	25	23	23
Ромм	13	20	17

1) адрес 1-го значения в столбце массива исходных данных;

2) абсолютную ссылку на массив всех значений справочной таблицы;

3) номер столбца, содержащего искомые данные;

4) пусто или истина, что означает соответственно приближённое или точное совпадение исходного значения 1-й таблицы и значения из 1-го столбца справочной таблицы.

Ниже представлено решение этой задачи.

Оценки Табл.2
Сумма баллов	Оценки
0	1
7	2
14	3
21	4
28	5
35	6
42	7
49	8
56	9
63	10

Функция РАНГ возвращает ранг значений в списке значений (их порядковые номера относи­тельно других значений). Например, тестируемых можно распределить по местам, задав 1 тому, кто набрал наибольшее количество баллов, 2 – для второго места и т.д. Функция имеет 3 аргумента:*число в первой ячейке столбца исходных данных;*ссылка на весь исходный столбец в абсолютной адресации;*порядок (0-по возрастанию ранга, 1-по убыванию).

Функция ЧАСТОТА (исходный массив; массив карманов) считает, сколько значений из исходного массива попадают в диапазоны значений, представленные в массиве кар­манов. Еслимассив кармановсодержит числа {a,b,c}, то числаисходного массивараспределя­ются по интервалам: (-¥,a], (a,b], (b,c], (c,¥). Таким образом, количество промежутков на 1 больше элемен­тов вмассиве карманов. Прежде чем использовать функциюЧАСТОТА, выделяют свобод­ный массив ячеек, на единицу больший чем массив карманов, и вводят функциюЧАСТОТА. За исходный массив можно взять массив ячеек в любом столбце таблицыТабл1, за массив карманов – значения из столбцаСумма балловсправочной таблицыТабл2 (в абсолютных адресах). Затем щел­чок в строке формул и- завершение операции одновременным нажатиемCtrl+Shift+Enter(неОК).

Матричныефункции предназначены для обработки двумерных массивов данных-матриц, которые применяются для решения математических задач. ВExcelэтоМатематические функции:МОПРЕД– вычис­ление определителя матрицы,МОБР– вычис­ление обратной матрицы,МУМНОЖ– перемно­жение матриц, и в категорииСсылки и массивы ТРАНСП– транспонирование матрицы. ФункцияМОПРЕДвыдает число, поэтому вводится как обычная формула. Осталь­ные функции и операции поэлементной обработки матриц формируют блок ячеек, поэтому они вводятся какмассивы. При обработке матриц удобно вводить имена для массивов данных. Так, если две матрицы введены в блокиА1:С2иЕ1:G2и этим блокам присвоены именаМиN, то формула поэлементного сло­жения матриц будет иметь простой и понятный вид: =M+N(набор формулы завершается групповой операцией ввода).

Формула =5*М–3*Nвыполнит поэлементное умножение каждой матрицы на постоянное число, вычитание13 -15 23 и возвратит результирующую матрицу -11 34 15

Для решения системы уравнений её представляют в матричном виде: АХ=В, где А -матрица коэффици­ентов при неизвестных, Х-массив неизвестных, В-массив свободных членов. Если определитель матрицы не равен нулю, то решение системы можно получить путём перемноже­ния обратной матрицыA^-1на массив В (с применением функциии МУМНОЖ).