Добавить материал и получить бесплатное свидетельство о публикации в СМИ
Эл. №ФС77-60625 от 20.01.2015
Инфоурок / Информатика / Другие методич. материалы / Лабораторная работа "Встроенные функции MS Excel" по дисциплине "Информационные технологии в профессиональной деятельности"

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

  • Информатика

Поделитесь материалом с коллегами:

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

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


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


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

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

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

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

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


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

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

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

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

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

hello_html_m469c193a.png

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


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

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

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

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


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

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

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

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

hello_html_m50ca686.png

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


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

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


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


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

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


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

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


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

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

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


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

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


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

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

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

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

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

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


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

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

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

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

Требуется по заданному значению кода материала – ячейка В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

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

hello_html_21c0c305.png

hello_html_1e4baf1e.png

hello_html_eaa543f.png

hello_html_m4127b3c1.png

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

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

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

hello_html_m3ab37efa.png

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

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

hello_html_m5819e8b5.png

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

hello_html_8bdcfbc.png

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

hello_html_71e1ef7b.png


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

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


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

  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). Что определяет данная формула, опишите назначение каждой функции в этой формуле.

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

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

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

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

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


6


Автор
Дата добавления 29.01.2016
Раздел Информатика
Подраздел Другие методич. материалы
Просмотров196
Номер материала ДВ-392943
Получить свидетельство о публикации
Похожие материалы

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