Лабораторная работа Встроенные функции MS Excel по дисциплине Информационные технологии в профессиональной деятельностиВстроенные функции

Лабораторная работа №17
Встроенные функции Microsoft Excel
Цель: Изучить информационную технологию использования встроенных функций MS Excel для финансового анализа категорий «Ссылки и массивы», «Математические», «Дата и время» и «Текстовые».
Теоретическая часть
Ссылка соответствует адресу ячейки или диапазону ячеек электронной таблицы. Ссылка на ячейки других рабочих книг или приложений носит название внешней или удаленной ссылки. В MicrosoftExcel используются ссылки различного стиля:
номер строки, номер столбца–R1C1;
имя столбца, номер строки – А1.
Ссылка на диапазон ячеек задается как ссылка на верхний левый yгoл диапазона, далее ставится знак двоеточия (:), указывается ссылка на правый нижний угол диапазона.
Практическая часть
Создайте новую рабочую книгу.
Выполните переименование Листа1на Ссылки и массивы.
Заполните значения ячеек в диапазоне С2:Е5 (рис.1).
Создайте именованный блок для диапазона ячеек С2:Е5 с именем Блок.

Рис. 1. Исходные данные для Задания1Функция АДРЕС– адрес ячеек или диапазонов ячеек.
Адрес ссылки выдаётся в виде текста в двойных кавычках. Ссылка представляется в виде номера строки и столбца, на пересечении которых находится ячейка. Ссылка может быть относительной илиабсолютной в определённом стиле (А1 или R1C1), включать имя листа рабочей книги.
Активизируйте ячейку А1, вызовите Мастера функций: Категория – Ссылки и массивы, функции; АДРЕС и введите: Номер строки -4; Номер столбца -5; Тип ссылки-1; А1- 1; Имя листа–Ссылки и массивы.
Формула в ячейке А1: =АДРЕС(4;5;1;"Ссылки и массивы")даёт ссылку на ячейку 'Ссылки и массивы'!$Е$4. В формуле использованы параметры (слева направо): 4 – номер строки, 5 – номер столбца, 1 – абсолютная ссылка, 2 – формат ссылки А1, Ссылки и массивы – имя листа.
Функция ДВССЫЛ– значение из ссылки.
Ссылка задаётся в виде текстовой строки.
Активизируйте ячейку А2, вызовите Мастера функций: Категория – Ссылки и массивы, выберите функцию ДВССЫЛ, в появившемся окне выберите функцию АДРЕС и введите: Номер строки – 4; Номер столбца – 5; Тип ссылки – 1; А1 – 1 (Рис. 2).
Эта формула даёт результат– значение из ячейки Е4(если лист не указан, используете текущий).

Рис. 2. Работа с функцией ДВССЫЛ и вложенной для неё функцией АДРЕС
Функция ЧСТРОК– определение числа строк в заданном диапазоне ячеек.
Активизируйте ячейку A3 и введите формулу вида =ЧСТРОК(С2:Е5), которая даёт в этой ячейке значение 4. Для этого вызовите Мастер функций, из категории Ссылки и массивы выберите функцию ЧСТРОК и в поле Массив укажите нужный диапазон ячеек.
Функция ЧИСЛСТОЛБ – определение числа столбцов в заданном диапазоне ячеек. Активизируйте ячейку А4 и аналогично введите формулу вида =ЧИСЛСТОЛБ(С2:Е5), которая даёт значение 3.Функция СТОЛБЕЦ – определение начального номера столбца ссылки (диапазона ячеек или именованного блока).
Аналогично, используя мастер функций, и, выбирая категорию «Ссылки и массивы», введите в ячейку А5 формулу вида =СТОЛБЕЦ(С2:Е5), которая даёт значение 3.Функция СТРОКА – определение начального номера строки ссылки (диапазона ячеек или именованного блока).
Введите в ячейку А6 формулу вида =СТРОКА(С2:Е5), которая даёт значение 2.Формулы этой категории можно успешно комбинировать друг с другом. Например, для именованного блока Блок, которому соответствует диапазон ячеек С2:Е5 определите значения начальной и конечной ячеек блока (в данном случае – ячеек С2 и Е5). Результат запишите в ячейки В1 и В2 соответственно.
Для этого активизируйте ячейку В1. Вызовите Мастер функций, категория – Ссылки и массивы, функция – ДВССЫЛ, в появившемся окне выберите функцию АДРЕС, являющуюся встроенной для данной функции (кнопка «перевёрнутый треугольник») и введите: Номер строк – СТРОКА(БЛОК): Номер столбца– СТОЛБЕЦ(БЛОК); Тип ссылки–4; Al–1. Таким образом, ячейка В1содержит формулу
=ДВССЫЛ(АДРЕС(СТРОКА(Блок);СТОЛБЕЦ(Блок);4;1).
Формула определяет начальные координаты блока – номер строки и номер столбца с помощью функций СТРОКА, СТОЛБЕЦ. С помощью функции АДРЕС строится адрес начальной ячейки блока. С помощью функции ДВССЫЛ определяется содержимое начальной ячейки блока Блок – ячейки С2.
В данном случае результат вычисления – число 125.
Активизируйте ячейку В2 и аналогично введите: Номер строки СТРОКА(БЛОК)+ЧСТРОК(БЛОК)–1; Номер столбца – СТОЛБЕЦ(БЛОК)+ЧИСЛСТОЛБ(БЛОК)–1; Тип ссылки– 4; Al - 1. Таким образом, ячейка В2 содержит формулу:=ДВССЫЛ(АДРЕС(СТРОКА(Блок)+ЧСТРОК(Блок)-1;СТОЛБЕЦ(Блок)+ЧИСЛСТОЛБ(Блок)-1;4;1)
Формула вычисляет начальный номер строки блока – функция СТРОКА, число строк в блоке функция ЧСТРОК для определения номера последней строки блока. Вычисляет начальный номер столбца блока – функция СТОЛБЕЦ, число столбцов в блоке – функция ЧИСЛСТОЛБ для определения номера последнего столбца в блоке.
С помощью функции АДРЕС строится адрес последней ячейки блока. С помощью функции ДВССЫЛ определяется содержимое этой ячейки – ячейки Е5. Результат вычисления– число 450.
Функция ВЫБОР – выбор по заданному номеру (индексу) объекта перечисления (диапазона ячеек, блоков или значений из указанного списка констант).
В ячейке ВЗ вычислите число строк в диапазоне ячеек: Блок и А2:А6, для этого в ячейку ВЗ введите формулу: =ЧСТРОК(ВЫБОР(2;Блок;А2:А6)). Мастер функций – Ссылки и массивы–ЧСТРОК – ВЫБОР (если этой функции нет, то с помощью кнопки перевёрнутого треугольника выберите Другие функции – ВЫБОР). Затем: Номер индекса – 2; Значение1 – Блок; Значение2 – А2:А6.
Результат вычисления – 5.
Функция ИНДЕКС – получение значения из области ссылки по относительному номеру. Область ссылки может быть одномерной, двумерной, содержать несколько диапазонов ячеек. Относительный номер строки и столбца в указанном диапазоне является индексом ссылки. Например первую ячейку диапазона ячеек Блок можно определить как ИНДЕКС(Блок;1;1), последнюю ячейку диапазона ячеек Блок можно определить как ИНДЕКС(Блок;3;4).
По аналогии работы с функциями введите в ячейку В4формулу: =ИНДЕКС(Блок;1;1), результат формулы – значение ячейки С2.
Функция ПОИСКПОЗ – определение позиции искомого значения в одномерном диапазоне ячеек.
Учитывается тип сопоставления:
1 - поиск небольшого значения, которое не превосходит искомое (массив значений упорядочен по возрастанию);
0– поиск первого равного искомому значения (массив значений в произвольном порядке);
-1 -поиск наименьшего значения, которое превосходит искомое (массив значений упорядочен по убыванию).
В ячейку В5 введите формулу: =ПОИСКПОЗ(1000;Е2:Е5;0), которая определяет позицию заданного числа – 1000 в диапазоне ячеек– Е2:Е5. Результат поиска – номер позиции 3.
Функция ПРОСМОТР – просмотр данных в блоках ячеек.
Функция ПРОСМОТР обеспечивает различные режимы поиска:
проверка наличия искомого значения в массиве (если значение существует, выводится само значение, в противном случае – сообщение об ошибке #Н/Д– нет данных);
поиск искомого значения в векторе просмотра и вывод соответствующего ему значения и вектора результата.
Требуется по заданному значению кода материала – ячейка В1 определить соответствующее этому коду название материала. Коды материалов представлены в ячейках С2:С5, названия – в ячейка D2:D5.
Для этого в ячейку В6введите формулу: =ПРОСМОТР(В1;С2:С5;D2:D5). Искомое_значение - В1, Вектор_просмотра – С2:С5, Вектор_результата – D2:D5. Эта функция возвращает значение из вектора результата (наименование материала) для найденного в векторе просмотра (код материала) значения, в данном случае – Асбест.
Категория «Текстовые функции»
Функции данной категории обеспечиваю работу с текстом, находящимся в ячейках таблицы или вводимым в виде текстовых констант в формулы.
Практическая часть
На новом листе Вашей рабочей книги, которому дайте имя «Текстовые», заполните ячейки А1:А4, начиная с А1, значениями:
0,234567
0,234567
17/06/2010
17/06/10
3897630327025В ячейки В1:В4, начиная с В1, введите формулы для преобразования числа или даты в текст (рис. 3).




Рис. 3. Функции категории Текстовые
В ячейку С1 введите формулу преобразования текста в число: =ЗНАЧЕН(0,23). Формула даёт результат 0,23. Это обратное преобразование функции ТЕКСТ/
В ячейку С2 ввести формулу сцепления текстовых строк:

В результате в ячейке С2 выводится строка текста «ПРИМЕР СЦЕПЛЕНИЯ СТРОК ТЕКСТА ДЛЯ ВЫВОДА В ОДНОЙ ЯЧЕЙКЕ».
В ячейку С3 введите формулу для определения длины текста в ячейке С2:

В ячейку С4 ввести формулу для определения первого вхождения в строку текста в ячейке С2 сочетания букв «СТ» с учётом регистра, поиск вести с начала строки текста:

При поиске без учёта регистра используется функция ПОИСК (введите формулу в ячейку С5):

Категория «Математические»
Это наиболее популярная категория встроенных функций, обеспечивающая разнообразные вычисления.
Практическая часть
Вставьте новый лист, если необходимо и переименуйте его на Математические.
Введите в столбец А, начиная с ячейки А1, формулы, вызвав Мастер функций и, указав, категорию Математические:
А1: определение знака выражения: =ЗНАК(-124) даёт -1, так кА число отрицательное;
А2: округление числа до ближайшего целого нечётного числа: =НЕЧЕТ(166,666667) даёт 167;
А3: округление числа до ближайшего целого чётного числа: =ЧЕТН(166,666667) даёт 168;
А4: округление числа до ближайшего меньшего целого числа: =ЦЕЛОЕ(166,666667) даёт 166;
А5: отбрасывание дробной части числа: =ОТБР(166,666667) даёт 166;
А6: округление числа до ближайшего целого или до ближайшего кратного указанному значению: =ОКРВВЕРХ(166,666667;10) вычисляет 170;
А7: округление числа до ближайшего меньшего по модулю целого числа: =ОКРВНИЗ(20/20*1000;10) вычисляет 160;
А8: округление числа до указанного количества десятичных разрядов: =ОКРУГЛ(166,666667;3) вычисляет 166,667;
А9: округление числа до ближайшего по модулю большего целого: =ОКРУГЛВВЕРХ(166,666667;3) вычисляет 166,667;
А10: округление числа до ближайшего меньшего по модулю целого: =ОКРУГЛВНИЗ(166,66667;3) вычисляет 166,666.
Категория «Дата и время»
Даты и время могут представляться в числовом или текстовом формате. Функции данной категории обеспечивают работу с датой и временем, находящимися в ячейках таблицы или вводимыми в виде констант в формулы.
Практическая часть
Новый лист Вашей рабочей книги переименуйте его наДата и время.
Введите в столбец А, начиная с ячейки А1, формулы для вычисления:
текущей даты: =СЕГОДНЯ();
текущей даты и времени: =ТДАТА();
даты в числовом формате, аргументы функции задаются по частям (год, месяц, день): =ДАТА(2009;6;17);
перевод даты из текстового формата в числовой формат, аргумент задаётся как строка текста: =ДАТАЗНАЧ(“17.06.2009”);
вычисление даты, отстоящей от указанной даты на определённое количество месяцев: =ДАТАМЕС(“17.06.2009”;-6). Результат вычисления возвращается в числовом формате, например, как значение 39981.
вычисление последней даты месяца, отстоящей от заданной даты на указанное число месяцев: = КОНМЕСЯЦА(ДАТА(2009;6;17). Возвращает значение 39813.
определение номера года, месяца и дня для даты, заданной в числовом формате:
=ГОД(39813) даёт год 2009,
=МЕСЯЦ(39813) даёт месяц 6,
=ДЕНЬ(39813) даёт число 17.
Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы.
Контрольные вопросы:
Перечислите известные Вам встроенные функции Категории «Ссылки и массивы».
Назначение функции АДРЕС.
В ячейке находится формула:
=ДВССЫЛ(АДРЕС(СТРОКА(Блок);СТОЛБЕЦ(Блок);4;1). Что определяет данная формула, опишите назначение каждой функции в этой формуле.
Назначение функции ПРОСМОТР. Какие режимы поиска обеспечивает функция ПРОСМОТР?
Для какой работы предназначены встроенные функции категории «Текстовые»?
Для каких целей используют функции категории «Математические»?
В каком формате могут представляться дата и время?
Для каких целей используют функции категории «Дата и время»?