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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Адрес ссылки выдаётся в виде текста в двойных кавычках. Ссылка представляется в виде номера строки и столбца, на пересечении которых находится ячейка. Ссылка может быть относительной илиабсолютной в определённом стиле (А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. Работа с функцией ДВССЫЛ и вложенной для неё функцией АДРЕС

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Требуется по заданному значению кода материала – ячейка В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.   Для каких целей используют функции категории «Дата и время»?