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

Лабораторная работа №17

Встроенные функции Microsoft Excel

Цель: Изучить информационную технологию использования встроенных функций MS Excel для финансового анализа категорий «Ссылки и массивы», «Математические», «Дата и время» и «Текстовые».

Теоретическая часть

Ссылка соответствует адресу ячейки или диапазону ячеек электронной таблицы. Ссылка на ячейки других рабочих книг или приложений носит название внешней или удаленной ссылки. В MicrosoftExcel используются ссылки различного стиля:

• номер строки, номер столбца-R1C1;

• имя столбца, номер строки - А1.

Ссылка на диапазон ячеек задается как ссылка на верхний левый yгoл диапазона, далее ставится знак двоеточия (:), указывается ссылка на правый нижний угол диапазона.

Практическая часть

1. Создайте новую рабочую книгу.

2. Выполните переименование Листа1на Ссылки и массивы.

3. Заполните значения ячеек в диапазоне С2:Е5 (рис.1).

4. Создайте именованный блок для диапазона ячеек С2:Е5 с именем Блок.

Рис. 1. Исходные данные для Задания1

1. Функция АДРЕС- адрес ячеек или диапазонов ячеек.

Адрес ссылки выдаётся в виде текста в двойных кавычках. Ссылка представляется в виде номера строки и столбца, на пересечении которых находится ячейка. Ссылка может быть относительной илиабсолютной в определённом стиле (А1 или R1C1), включать имя листа рабочей книги.

Активизируйте ячейку А1, вызовите Мастера функций: Категория - Ссылки и массивы, функции; АДРЕС и введите: Номер строки -4; Номер столбца -5; Тип ссылки-1; А1- 1; Имя листа-Ссылки и массивы.

Формула в ячейке А1: =АДРЕС(4;5;1;"Ссылки и массивы")даёт ссылку на ячейку 'Ссылки и массивы'!$Е$4. В формуле использованы параметры (слева направо): 4 - номер строки, 5 - номер столбца, 1 - абсолютная ссылка, 2 - формат ссылки А1, Ссылки и массивы - имя листа.

6. Функция ДВССЫЛ- значение из ссылки.

Ссылка задаётся в виде текстовой строки.

Активизируйте ячейку А2, вызовите Мастера функций: Категория - Ссылки и массивы, выберите функцию ДВССЫЛ, в появившемся окне выберите функцию АДРЕС и введите: Номер строки - 4; Номер столбца - 5; Тип ссылки - 1; А1 - 1 (Рис. 2).

Эта формула даёт результат- значение из ячейки Е4(если лист не указан, используете текущий).

Рис. 2. Работа с функцией ДВССЫЛ и вложенной для неё функцией АДРЕС

6. Функция ЧСТРОК- определение числа строк в заданном диапазоне ячеек.

Активизируйте ячейку A3 и введите формулу вида =ЧСТРОК(С2:Е5), которая даёт в этой ячейке значение 4. Для этого вызовите Мастер функций, из категории Ссылки и массивы выберите функцию ЧСТРОК и в поле Массив укажите нужный диапазон ячеек.

6. Функция ЧИСЛСТОЛБ - определение числа столбцов в заданном диапазоне ячеек. Активизируйте ячейку А4 и аналогично введите формулу вида =ЧИСЛСТОЛБ(С2:Е5), которая даёт значение 3.

6. Функция СТОЛБЕЦ - определение начального номера столбца ссылки (диапазона ячеек или именованного блока).

Аналогично, используя мастер функций, и, выбирая категорию «Ссылки и массивы», введите в ячейку А5 формулу вида =СТОЛБЕЦ(С2:Е5), которая даёт значение 3.

6. Функция СТРОКА - определение начального номера строки ссылки (диапазона ячеек или именованного блока).

Введите в ячейку А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.

6. Функция ВЫБОР - выбор по заданному номеру (индексу) объекта перечисления (диапазона ячеек, блоков или значений из указанного списка констант).

В ячейке ВЗ вычислите число строк в диапазоне ячеек: Блок и А2:А6, для этого в ячейку ВЗ введите формулу: =ЧСТРОК(ВЫБОР(2;Блок;А2:А6)). Мастер функций - Ссылки и массивы-ЧСТРОК - ВЫБОР (если этой функции нет, то с помощью кнопки перевёрнутого треугольника выберите Другие функции - ВЫБОР). Затем: Номер индекса - 2; Значение1 - Блок; Значение2 - А2:А6.

Результат вычисления - 5.

6. Функция ИНДЕКС - получение значения из области ссылки по относительному номеру. Область ссылки может быть одномерной, двумерной, содержать несколько диапазонов ячеек. Относительный номер строки и столбца в указанном диапазоне является индексом ссылки. Например первую ячейку диапазона ячеек Блок можно определить как ИНДЕКС(Блок;1;1), последнюю ячейку диапазона ячеек Блок можно определить как ИНДЕКС(Блок;3;4).

По аналогии работы с функциями введите в ячейку В4формулу: =ИНДЕКС(Блок;1;1), результат формулы - значение ячейки С2.

6. Функция ПОИСКПОЗ - определение позиции искомого значения в одномерном диапазоне ячеек.

Учитывается тип сопоставления:

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

• 0- поиск первого равного искомому значения (массив значений в произвольном порядке);

• -1 -поиск наименьшего значения, которое превосходит искомое (массив значений упорядочен по убыванию).

В ячейку В5 введите формулу: =ПОИСКПОЗ(1000;Е2:Е5;0), которая определяет позицию заданного числа - 1000 в диапазоне ячеек- Е2:Е5. Результат поиска - номер позиции 3.

6. Функция ПРОСМОТР - просмотр данных в блоках ячеек.

Функция ПРОСМОТР обеспечивает различные режимы поиска:

• проверка наличия искомого значения в массиве (если значение существует, выводится само значение, в противном случае - сообщение об ошибке #Н/Д- нет данных);

• поиск искомого значения в векторе просмотра и вывод соответствующего ему значения и вектора результата.

Требуется по заданному значению кода материала - ячейка В1 определить соответствующее этому коду название материала. Коды материалов представлены в ячейках С2:С5, названия - в ячейка D2:D5.

Для этого в ячейку В6введите формулу: =ПРОСМОТР(В1;С2:С5;D2:D5). Искомое_значение - В1, Вектор_просмотра - С2:С5, Вектор_результата - D2:D5. Эта функция возвращает значение из вектора результата (наименование материала) для найденного в векторе просмотра (код материала) значения, в данном случае - Асбест.

Категория «Текстовые функции»

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

Практическая часть

1. На новом листе Вашей рабочей книги, которому дайте имя «Текстовые», заполните ячейки А1:А4, начиная с А1, значениями:

• 0,234567

• 0,234567

• 17/06/2010

• 17/06/10

2. В ячейки В1:В4, начиная с В1, введите формулы для преобразования числа или даты в текст (рис. 3).

Рис. 3. Функции категории Текстовые

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

4. В ячейку С2 ввести формулу сцепления текстовых строк:

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

5. В ячейку С3 введите формулу для определения длины текста в ячейке С2:

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

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

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

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

Практическая часть

1. Вставьте новый лист, если необходимо и переименуйте его на Математические.

2. Введите в столбец А, начиная с ячейки А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. Новый лист Вашей рабочей книги переименуйте его наДата и время.

2. Введите в столбец А, начиная с ячейки А1, формулы для вычисления:

• текущей даты: =СЕГОДНЯ();

• текущей даты и времени: =ТДАТА();

• даты в числовом формате, аргументы функции задаются по частям (год, месяц, день): =ДАТА(2009;6;17);

• перевод даты из текстового формата в числовой формат, аргумент задаётся как строка текста: =ДАТАЗНАЧ("17.06.2009");

• вычисление даты, отстоящей от указанной даты на определённое количество месяцев: =ДАТАМЕС("17.06.2009";-6). Результат вычисления возвращается в числовом формате, например, как значение 39981.

• вычисление последней даты месяца, отстоящей от заданной даты на указанное число месяцев: = КОНМЕСЯЦА(ДАТА(2009;6;17). Возвращает значение 39813.

• определение номера года, месяца и дня для даты, заданной в числовом формате:

=ГОД(39813) даёт год 2009,

=МЕСЯЦ(39813) даёт месяц 6,

=ДЕНЬ(39813) даёт число 17.

Отчитайтесь по проделанной работе преподавателю и оформите отчёт по данной лабораторной работе, ответив письменно на контрольные вопросы.

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

1. Перечислите известные Вам встроенные функции Категории «Ссылки и массивы».

2. Назначение функции АДРЕС.

3. В ячейке находится формула:

=ДВССЫЛ(АДРЕС(СТРОКА(Блок);СТОЛБЕЦ(Блок);4;1). Что определяет данная формула, опишите назначение каждой функции в этой формуле.

4. Назначение функции ПРОСМОТР. Какие режимы поиска обеспечивает функция ПРОСМОТР?

5. Для какой работы предназначены встроенные функции категории «Текстовые»?

6. Для каких целей используют функции категории «Математические»?

7. В каком формате могут представляться дата и время?

8. Для каких целей используют функции категории «Дата и время»?

6