Практическое задание 2
Применить к числовым данным первой таблицы различные числовые форматы и операции:
- изменить (затем восстановить) число десятичных знаков;
- изменить знак для одного-двух чисел и установить для этих ячеек денежный формат, для отрицательных чисел красный цвет и восстановить прежние значения командой Отменить.
- убрать (и восстановить) линии сетки таблицы;
- скрыть (и затем восстановить) столбец Оклад;
- создать для первой таблицы обрамление и скопировать оформление на Листы 2 и 3;
- вставить во вторую таблицу две пустые строки после строки с итоговыми данными;
- во всех трех таблицах отсортировать данные по фамилии.
Для всех четырех таблиц построить диаграммы и поместить их под таблицами:
- для первой -гистограмму, представляющую значения окладов для каждого сотрудника,
- по данным второй таблицы -круговую диаграмму,
- по данным третьей и четвертой -графики изменения уровня выплат и значений функции;
- для графика последней функции применить изменение масштаба (растянуть его по вертикали для более точного определения точек пересечения кривой с осью Х или заменить диапазон исходных данных);
- отредактировать отдельные элементы всех диаграмм - оформить цветом, ввести заливку.
Выполнить просмотр данных рабочей книги:
- предварительный просмотр страниц рабочей книги,
- четвёртого листа с закреплением строки заголовка.
___________________
3. Функции и вычисления ( логические, табличные, матричные функции )
Excelимеет более 500 встроенных функций. Любую из них можно вводить с клавиатуры непосредственно в строке формул окнаExcelили используяМастер функций (Вставка-Функция или значокfx). Функция вExcel- этоимяфункциииаргументыв круглых скобках, которые разделяются точкой с запятой. Аргументами в функциях могут быть константы и адреса переменных (ссылки).
Функция СУММ (А1; В5; С10)выдаст сумму чисел, заданных в аргументах-ссылках;МИН(С2:Е4)найдёт минимальное среди чисел указанного в аргументе диапазона ячеек. В качестве аргументов могут применяться другие (вложенные) функции; например,СУММ (МИН (C2:E4)); A1; B5; C10).
Если в процессе набора формулы нужна вложенная функция, её имя вставляют из открывающегося списка функций слева в строке формулы, при этом появляется новое окно. После ввода аргументов вложеннойфункции в её окне вместо ОК делают щелчок в поле вводаосновнойфункции в строке формулы, чтобы вернуться в её окно и закончить набор.
Автосуммирование. Вызывается кнопкой (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на массив В (с применением функциии МУМНОЖ).