Практическая работа 4

Вложенные функции

Открыть новый лист и составить таблицу для расчета стоимости телефонных разговоров. Столбцы таблицы: Номер по порядку, Дата, Время начала разговора, Время окончания разговора, Время разговора, День недели, Час суток, Тариф, Стоимость разговора.

Столбец Дата заполнить произвольно датами одного месяца.

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

Значения остальных столбцов рассчитываются:

• Стоимость разговора равна произведению времени разговора на тариф.

• Время разговора рассчитывается как разность между окончанием и началом разговора в минутах. Для определения нужного компонента времени (минут) использовать функцию МИНУТЫ.

Используется следующая система оплаты (тариф): в выходные дни − 0,15 долл. за 1 минуту разговора, в будни − 0,39 долл. с 8:00 до 21:00 и 0,10 долл. – в остальное время суток.

Для определения дня недели использовать функцию ДЕНЬНЕД.

Для определения часа суток использовать функцию ЧАС. Час суток определять по времени начала разговора.

Добавить итоговую строку, содержащую сумму столбца Стоимость разговора.

Листу с таблицей присвоить имя Телефон.

Отформатировать таблицу:

• ячейкам, содержащим денежные величины, назначить денежный формат;

• «шапку» отформатировать с помощью стиля «Шапка», созданного в предыдущей работе;

• итоговую строку отформатировать с помощью встроенного стиля «Итог»;

• расчертить таблицу линиями; внешнюю рамку оформить утолщенной линией; столбцы разделить тонкими черными линиями;

• добавить фон.

11. Формулы массивов

Массив – это набор элементов, которые могут обрабатываться как единая группа.

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

Массивы не обязательно должны храниться в диапазонах ячеек: их можно вводить непосредственно в формулу

Формулы массивов – особый вид вычислений, состоящий в автоматическом выполнении операций над группой значений.

Одна формула массива может выдавать один или несколько результатов.

Примеры:

Формула массива { = A1 : A3 + B1 : B3 } эквивалентна трем формулам: = A1 + B1, = A2 + B2 и = A3 + B3 ; результатом этой формулы будут три значения.

Формула { = СУММ (B2 : D2 * B3 : D3 ) } эквивалентна формуле = B2 * B3 + C2 * C3 + D2 * D3; ее результатом будет одно значение.

Формулы массива заключаются в фигурные скобки. С клавиатуры их вводить не надо: формула будет заключена в скобки автоматически при нажатии комбинации клавиш Ctrl + Shift + Enter.

Чтобы ввести формулу массива, следует:

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

Ввести формулу, выделяя интервалы ячеек в качестве ссылок.

Нажать комбинацию клавиш Ctrl + Shift + Enter.

Не допускается редактирование (вставка, удаление, перемещение, копирование) ячеек, которые составляют только часть массива.

Массивы констант

Подобно тому, как в обычную формулу вводят значение, в формулу массива можно ввести массив значений, называемый массивом констант. Массивы констант могут содержать числа, текст, логические значения.

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

• массив констант заключается в фигурные скобки;

• значения в одной строке разделяются точками с запятыми;

• строки разделяются двоеточиями;

• .текст в массивах заключается в кавычки;

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

Примеры записи массивов констант:

Запись { 10 ; 20 ; 30 ; 40 } соответствует массиву

10

20

30

40

Запись { 100 : 200 : 300 } – массиву

100

200

300

Запись { 1 ; 2 ; 3 : 4 ; 5 ; 6 } – массиву

1	2	3
4	5	6

Формула массива

{ = { A2 : B4 * { 1 ; 2 : 3 ; 4 : 5 ; 6 } } }

эквивалентна следующим формулам:

= A2 * 1 ; = B2 * 2 ;= A3 * 3 ; = B3 * 4 ; = A4 * 5 ; = B4 * 6 .

Чтобы ввести в диапазон ячеек массив констант, надо:

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

Ввести массив в соответствии с приведенными выше правилами, начиная со знака «=». В отличие от формул массива, при вводе массива констант фигурные скобки вводятся вручную.

Нажать комбинацию клавиш Ctrl + Shift + Enter.

Примеры использования формул массива

Пусть в диапазон ячеек A1:A10 введены числовые данные. Необходимо просуммировать только те ячейки из этого диапазона, значения которых превышают 5.

Для этого надо ввести формулу:

{=СУММ(ЕСЛИ(A1:A10>5;A1:A10))}.

Замечание: фигурные скобки не вводятся вручную, они будут вставлены автоматически после нажатия клавиш Ctrl + Shift + Enter.

Если значения ячеек, которые необходимо просуммировать, должны находиться в интервале от 3 до 7, формула должна иметь следующий вид:

{=СУММ(ЕСЛИ((A1:A10>3)*(A1:A10<7);A1:A10))}.

Здесь используется операция логического умножения.

Если надо просуммировать ячейки со значениями меньше 2 или больше 6, то следует ввести формулу:

{=СУММ(ЕСЛИ((A1:A10<2)+(A1:A10>6);A1:A10))}.

Здесь используется операция логического сложения.

Замечание: в формулах массива нельзя использовать непосредственно функции И или ИЛИ, поскольку эти функции возвращают отдельный результат, ИСТИНА или ЛОЖЬ, а для функций массива требуется массив результатов.

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

Пусть дана система линейных уравнений:

Эту систему в матричном виде можно записать следующим образом:

где

Решением этой системы будет

где – обратная матрица.

Другой способ решения – по формулам Крамера:

где − определитель матрицы – определитель матрицы , получаемой из матрицы заменой -го столбца (т. е. столбца коэффициентов при неизвестном x_i) вектором свободных членов B.

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

Функции для операций с матрицами

Для операций с матрицами Microsoft Excel располагает следующими функциями:

• МОПРЕД – вычисление определителя матрицы;

• МОБР – обращение матрицы;

• МУМНОЖ – перемножение матриц;

• ТРАНСП – транспонирование матрицы.

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

МОПРЕД ( массив )

Функция МОБР возвращает обратную матрицу для матрицы, хранящейся в массиве. Синтаксис функции:

МОБР ( массив )

Функция МУМНОЖ возвращает произведение матриц (матрицы хранятся в массивах). Синтаксис функции:

МУМНОЖ ( массив 1; массив 2 )

Результатом является массив с таким же числом строк, как массив 1, и с таким же числом столбцов, как массив 2.

Функция ТРАНСП меняет местами строки и столбцы массива. Синтаксис функции:

ТРАНСП ( массив )

Результатом является массив, представляющий собой транспонированную исходную матрицу.