Использование функций excel лабораторная работа

Лабораторная работа №14. Функции в excel

Цель работы: освоение основных приемов работы с функциями в Excel.

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

Мастер функций упрощает процесс вставки функции в формулу. Для того, чтобы запустить Мастер функций, можно выбрать команду Функция в меню Вставка или воспользоваться пиктограммой панели инструментов Стандартная. Функции сгруппированы по категориям, таким как «Финансовые», «Математические и тригонометрические», «Статистические», «Логические» и др. В нижней части окна мастера функций отображается синтаксис, а также определение выделенной функции.

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

После выбора функции из списка появляется диалоговое окно выбранной функции, которое содержит поля для ввода аргументов. В нижней части окна функции отображается описание того аргумента, в поле ввода которого стоит курсор. Аргументы могут быть числами, текстами, логическими величинами или ссылками. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций.

Функции даты и времени. Следуя описанным выше правилам использования встроенных функций Excel, выполнить следующие действия:

В ячейку А1 вставить функцию СЕГОДНЯ, которая возвращает текущую дату.

В ячейку В1 вставить функцию ДЕНЬНЕД. В открывшемся окне в качестве аргумента указать адрес А1. Выбрать подходящее значение аргумента Тип. Что возвращает эта функция?

Определить в какой день недели вы родились?

Текстовые функции. Выполнить следующие операции с текстовой информацией:

В ячейку А3 ввести текст «годовой отчет». В ячейку А4 вставить функцию ПОДСТАВИТЬ. В качестве первого аргумента указать адрес ячейки А3, в качестве второго — «годовой», третьего — «недельный».

В ячейке А6, используя функцию ПОВТОР, получить текст, состоящий из 50 символов «#».

Источник

Дистанционные курсы для педагогов

650 курсов повышения квалификации от 590 руб.

253 курса профессиональной переподготовки от 2190 руб.

Входим в перечень Министерства просвещения Российской Федерации

Разговоры о важном

Рекомендуем всем учителям 1-11 классов

Дистанционные курсы Повышения квалификации и профессиональной переподготовки

900 курсов от 770 рублей

Войти с помощью:

Лабораторная работа «Функции Excel»

Тема : Функции Excel

1. Познакомиться с различными классами функций;
2. Научиться использовать Мастер функций;
3. Научиться использовать вложенные функции в работе с таблицами.

Функция – это зависимая переменная величина, значение которой вычисляется по определенным правилам на основании значений других величин – аргументов функции. Excel предлагает большой (несколько сотен) набор стандартных (встроенных) функций, которые можно использовать в формулах, например:

Функция — от латинского Functio – исполнение.

За именем функции в круглых скобках следует через точку с запятой список аргументов. Список аргументов может состоять из чисел, текста, логических величин (ИСТИНА или ЛОЖЬ), ссылок, формул, вложенных функций. Если формула начинается с функции, перед именем функции вводится знак «=».

По характеру аргументов встроенные функции можно разделить на три типа:

С перечислением аргументов (максимум – 30 аргументов): СРЗНАЧ (А2:С23;Е6;200;3) – возвращает среднее значение аргументов

С фиксированными аргументами: СТЕПЕНЬ (6,23;4): возводит первый аргумент (6,24) в степень второго аргумента (4)

Без аргументов : СЕГОДНЯ (): возвращает текущую дату.

Последовательность ввода функции в формулу:

2. Открывающаяся круглая скобка;

3. Перечень аргументов через точку с запятой;

4. Закрывающаяся круглая скобка.

Ввод функции можно осуществить несколькими способами:

Функции и панель формул

Если вводится не вручную, аргументы указываются с помощью Панели формул:

Обязательный аргумент выделен полужирным шрифтом – без него функция не может выполнить обработку;

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

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

Панель формул можно перемещать по экрану, перетаскивая её мышью.

Результат вычисления функции может быть использован в качестве аргумента другой функции. Функция, используемая в качестве одного из аргументов другой функции, называется вложенной. Excel поддерживает до 7 уровней вложенности функций.

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

Содержимое ячейки можно представлять как совокупность четырёх слоёв информации: формула, значение, формат и примечание. Excel позволяет выполнять раздельное копирование каждого слоя. Информация помещается в буфер как обычно (команда Копировать), а вставляется с помощью команды Правка \ Специальная вставка…

Для копирования форматов, также как и других приложениях Office , используется инструмент стандартной панели – Формат по образцу . (Практическая работа «Прогноз погоды»).

1. При помощи функции заполнить блок А1:А5 случайными числами в диапазоне [-10,10];

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

3. Скопируйте полученную формулу в блок В2:В5;

4. Эту же последовательность операций применить к функциям и блокам соответственно:

Вычисление остатка при делении на 2 – F 1: F 5;

5. В клетку А7 написать формулу суммы элементов первой колонки (А1:А5)

В клетке В7 – среднее арифметическое по (В1:В5)

С7 – максимальный элемент из (С1:С6)

D 7 – минимальный элемент ( D 1: D 6)

E 7 – количество элементов (Е1:Е6)

F 7 – дисперсию значений ( F 1: F 6)

Диапазон I 1: I 6 заполнить значениями тригонометрических функций:

6. В строке 10 вести заголовки полей:

Фамилия\Имя Дата рождения Количество дней

Подкорректируйте ширину колонок и произведите отцентровку заголовков;

7. В блоке А12:А17 ввести фамилии или имена ваших друзей, знакомых. В блоке В12:В17 – их даты рождения. Дату вводить в европейском формате;

8. В клетке С9 ввести текущую дату;

9. В клетку С12 формулу для расчёта количества дней, прожитых человеком для текущей даты;

10. Между колонками Дата рождения и Количество дней вставить колонку День недели;

11. В первую клетку колонки вписать функцию вычисления дня недели по дате рождения. Скопировать полученную формулу во все клетки колонки;

12. В колонке F напротив каждой фамилии написать «Молодой» или «Старый», используя логическую функцию ЕСЛИ. Функцию введите, используя, Мастер функций (ЕСЛИ Количество дней

13. Сохраните полученную таблицу на диске в личной папке (Наименование группы).

1. Способы ввода формул в ячейки;

2. Панель формул;

3. Обязательный и необязательный аргументы в формулах;

4. Процедура выполнения вложенных функций в Microsoft Excel ;

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Активизируйте ячейку А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 содержит формулу

Формула определяет начальные координаты блока – номер строки и номер столбца с помощью функций СТРОКА, СТОЛБЕЦ. С помощью функции АДРЕС строится адрес начальной ячейки блока. С помощью функции ДВССЫЛ определяется содержимое начальной ячейки блока Блок – ячейки С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, названия – в ячейка D 2: D 5.

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

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

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

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

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

Источник