Финансовые функции. Функции даты и времени.

Группа С-11, 2025 года

Занятие по рабочей программе №08

Дисциплина: ОП.03 «Информационные технологии в профессиональной деятельности»

Раздел 3. Технология обработки числовой информации.

Тема: Финансовые функции. Функции даты и времени.

1. Цель занятия:

   1. Дидактическая:

• сформулировать представление студентов о понятии Технология обработки информации;

• выяснить основные понятия MS EXCEL;

• раскрыть способы ввода, перемещения и копирование формул в таблицу;

• выяснить принципы и методы использования Финансовых функций;

• систематизировать и обобщить знания по работе с Функциями даты и времени;

• углубить и закрепить знания по дисциплине «Информационные технологии в профессиональной деятельности».

Воспитательная:

• развивать коммуникативные способности;

• развивать аналитические способности;

• развивать творческий подход к процессу обучения.

• воспитывать самостоятельность, дисциплинированность;

• стимулировать студентов к изучению дисциплины;

• побуждать к формированию активной жизненной позиции;

• прививать уважение и любовь к будущей профессии.

Вид занятия: лекция.

Тип занятия: обобщение и систематизация знаний.

Форма проведения занятия: репродуктивная и эвристическая беседа.

Междисциплинарные связи:

Обеспечивающие СГ.01 История России, СГ.04 Физическая культура, ОП.04 Бережливое производство.

Обеспечиваемые ОП.11 Экономика, организация и планирование производства, ОП.12 Охрана труда, ПП.03 Производственная практика.

Методическое обеспечение: опорный конспект.

Литература:

1. Михеева, Е.В. Практикум по информационным технологиям в профессиональной деятельности : учебное пособие. - М. : Академия, 2008. - 8 – е изд. - 256 с.- (Среднее профессиональное образование), стр. 175 - 179

2. Практикум по MS Excel - http://informatika.delayu.ru/Zadanija/Exceldannye

ХОД ЗАНЯТИЯ

1. Ознакомление с темой, целью и планом занятия.

Тема: Финансовые функции. Функции даты и времени.

ПЛАН

1. Функция FV.

2. Функция создает дату.

3. Текущие функции даты и времени.

4. Функция извлекает дату и компоненты даты.

5. Функция вычисляет разницу даты.

6. Функция расчета рабочих дней.

7. Функции времени в Excel.

8. Функция подсчета и суммирования ячеек по цвету (пользовательская функция).

1. Изложение и изучение нового материала.

ЛИТЕРАТУРА: [2], стр. 175 - 179

Полные финансовые функции в Excel, которые вы должны знать

В Excel финансовые функции для расчета прибыльности, расчета рабочих дней или сроков важны в рабочем процессе почти всех предприятий. 

1. Функция FV

Использование: используется для расчета будущей стоимости инвестиции на основе фиксированной процентной ставки.

Формул: = FV (ставка, кол-во, вып., [Pv], [тип])

В котором:

• Ставка (обязательно): Процентная ставка по сроку.

• Nper (обязательно): Общее количество условий оплаты.

• Pmt (обязательно): оплата за каждый период; Этот пункт остается без изменений. Обычно PMT содержит основную сумму и проценты, но не другие сборы и налоги. Если pmt опущен, необходимо включить аргумент pv.

• Pv (необязательно): текущая стоимость или текущий одноразовый платеж, равный серии будущих платежей. Если pv опущено, предполагается, что он равен 0, и вы должны включить аргумент pmt.

• Тип (необязательно): Число 0 или 1 указывает, когда должен быть произведен платеж. Если тип не указан, предполагается, что он равен 0.

Пример: годовая процентная ставка составляет 6%, общее количество платежей - 10, сумма к оплате - 200, текущая стоимость - 500, платеж в начале периода. Ввод формулы = FV (0,06 / 12,10, -200, -500,1) (1) даст 2581,40 доллара (2).

2. Функция создает дату

Функция ДАТА

Использование: возвращает указанную дату.

Формула: = ДАТА (год, месяц, день)

Пример: В этом примере нам нужно суммировать даты вашего рождения, но в настоящее время ячейки разделены, мы используем функцию ДАТА для суммирования ваших дат. Год в ячейке D2, месяц в ячейке C2, день в ячейке B2. Ввод формулы ДАТА (D2, C2, B2) (1) дает результат 12 января 2002 г. (2).

Функция ДАТАЗНАЧ

Использование: преобразует текстовую строку с формой даты в значение даты, которое можно вычислить.

Формула: = ДАТАЗНАЧ (текст_даты)

Пример: используйте функцию DATEVALUE, чтобы преобразовать даты в порядковые номера, затем вычтите выходные дни из работы и количество дней, в течение которых сотрудник работал, даст результат (4).

3. Текущие функции даты и времени.

Функция СЕГОДНЯ

Использование: возвращает текущую дату. Часто СЕГОДНЯ используется для быстрого расчета времени.

Формул : = СЕГОДНЯ ()

Пример. Допустим, сегодня 26 августа 2020 г. Ввод формулы = СЕГОДНЯ () (1) дает результат 26.08.2020 (2).

Функция СЕЙЧАС

Использование: возвращает текущую дату и время.

Формула: = СЕЙЧАС ()

Пример. Предположим, 26.08.2020 сейчас 20:44. Ввод формулы = СЕЙЧАС () (1) приведет к (2).

4. Функция извлекает дату и компоненты даты.

Функция ДЕНЬ

Использование: возвращает день месяца.

Формула: = ДЕНЬ (серийный_номер)

В том числе: Serial_number (обязательно): дата, которую вы пытаетесь найти. Даты следует вводить с помощью функции ДАТА или как результат других формул или функций.

Пример: используйте ДЕНЬ, чтобы указать дату 22 мая 2019 г. При вводе формулы = ДЕНЬ ("22/5/2019") (1) результат вернет 22 (2).

Функция МЕСЯЦ

Утилита: возвращает месяц указанной даты.

Формула: = МЕСЯЦ (серийный_номер)

В том числе: Serial_number (обязательно): число месяца, в котором вы пытаетесь найти. Даты следует вводить с помощью функции ДАТА или результатов других формул или функций.

Пример. Используйте функцию МЕСЯЦ, чтобы указать месяц с 22 мая 2019 г. При вводе формулы = МЕСЯЦ («22 мая 2019 г.») (1) результатом будет май (2).

Функция ГОД

Использование: возвращает год указанной даты.

Формула: = ГОД (серийный_номер)

В том числе: Serial_number (обязательно): число месяца, в котором вы пытаетесь найти. Даты следует вводить с помощью функции ДАТА или результатов других формул или функций.

Пример: используйте функцию ГОД, чтобы извлечь год с 22 мая 2019 года. Ввод формулы = ГОД («22 мая 2019 г.») (1) вернет значение 2019.

Функция EOMONTH

Использование: возвращает последний день месяца.

Формула: = EOMONTH (начальная_дата, месяцы)

В котором:

• Start_date (обязательно): Дата представляет собой дату начала. Даты следует вводить с помощью функции ДАТА или результатов других формул или функций.

• Месяцы (обязательно): количество месяцев до или после start_date. Положительное значение аргумента месяцев дает дату в будущем; Отрицательные значения создают дату в прошлом.

Пример: чтобы найти конец месяца 8, 9, 10, 11, 12 месяца 2020 года, мы используем функцию EOMONTH . Дата начала - 26 августа 2020 г., то есть B2. Месяцы - это количество месяцев подряд с Start_date, в нашем примере 0, 1, 2, 3, 4. При вводе формулы = EOMONTH ($ B $ 2, C2) (1) результатом является последняя дата. Месяцев из них месяцев (2).

ДЕНЬНЕД.

Использование: возвращает день недели, соответствующий дате.

Формула: = ДЕНЬ НЕДЕЛИ (серийный_номер; [return_type])

В котором:

• Serial_number (обязательно): серийный номер, представляющий дату, которую вы ищете. Даты следует вводить с помощью функции ДАТА или вводить как результат других формул или функций.

• Return_type (необязательно): число, определяющее тип возвращаемого значения.

Например: когда нам нужно узнать, какой день недели, 26 августа 2020 года, мы используем функцию WEEKDAY . Где serial_number - 26/8/2020, return_type - 2, чтобы рассчитать время с понедельника по воскресенье. При вводе формулы = ДЕНЬ НЕДЕЛИ («26/8/2020») результат будет 3, т. Е. 26/8/2020 (2) - это третий день недели, т.е. среда.

НЕДЕЛ.

Утилита: возвращает номер недели в году для указанной даты.

Примечание:

Для этой функции используются две системы:

Система 1: неделя с 1 января является первой неделей года и нумеруется неделей 1.

Система 2: Неделя с первым четвергом года является первой неделей года и нумеруется неделей 1. Эта система представляет собой метод, определенный в ISO 8601, который обычно называют системой нумерации Европейская неделя.

Формула: = WEEKNUM (серийный_номер; [return_type])

В котором:

• Serial_number (обязательно): день недели. Даты следует вводить с помощью функции ДАТА или вводить как результат других формул или функций.

• Return_type (необязательно): число, указывающее дату начала недели. По умолчанию 1.

Например: когда нам нужно узнать, какая неделя является неделей 26 августа 2020 года, мы используем функцию WEEKNUM . Где serial_number - 26/8/2020, return_type - 2 для расчета времени с понедельника. При вводе формулы = WEEKNUM («26/8/2020», 2) результат будет 35, то есть неделя, содержащая дату 26 августа 2020 года, будет 35-й неделей в году.

5. Функция вычисляет разницу даты.

Функция EDATE

Утилита: возвращает дату в пределах указанного месяца, которая может предшествовать или после даты начала. Используйте функцию EDATE для расчета сроков или сроков исполнения, которые совпадают с выпуском месяца.

Формула: ДАТА (начальная_дата, месяцы)

В котором:

• Start_date (обязательно) Дата представляет собой дату начала. Даты следует вводить с помощью функции ДАТА или результатов других формул или функций.

• Месяцы (обязательно): количество месяцев до или после start_date. Положительное значение аргумента месяцев дает дату в будущем; Отрицательные значения создают дату в прошлом.

Пример: чтобы узнать дату следующего месяца 4 февраля 2019 г., используйте функцию EDATE с Start_date как 4 февраля 2019 г., Months равно 1. При вводе формулы = EDATE (A2, B2) (1) вернется 4 марта. , 2019 (2).

Функция ГОД

Использование: функция ГОДА вычисляет часть года, представленную количеством полных дней между двумя датами (start_date и end_date).

Формула: = YEARFRAC (начальная_дата, конечная_дата, [основание])

В котором:

• Start_date (обязательно): дата начала.

• End_date (обязательно): дата окончания.

• Основа (необязательно): Тип используемой основы даты.

Например, мы используем функцию YEARFRAC, чтобы вычислить, сколько лет между 31 декабря 2019 и 1 января 2019. Star_date - 1 января 2019 г., End_date - 31 декабря 2019 г. Ввод формулы = YEARFRAC (A2, B2) (1) вернет 1 (2), потому что между этими двумя периодами проходит 1 год.

6. Функция расчета рабочих дней

Функция РАБДЕНЬ

Утилита: возвращает порядковый номер, представляющий количество рабочих дней до или после даты начала работы, и вычитает выходные и праздничные дни (если они есть) в этот период времени.

Формула: РАБДЕНЬ (дата начала, дни, [праздники])

В котором:

• Start_date: дата начала, обязательный параметр.

• Дни: дата не в выходные и праздничные дни до или после Start_date является обязательным параметром.

Праздники: дни, которые должны быть исключены из рабочих дней, которые не являются фиксированной датой выходных.

Пример: чтобы быстро определить дату окончания проекта, мы будем использовать функцию РАБДЕНЬ . Start_date - 1 марта 2019 года, дни - 45 дней, например, работа без выходных. Ввод формулы = РАБДЕНЬ (B2, C2) (1) вернет 5/5/2019 (2).

Функция РАБДЕНЬ.INTL

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

Формула: РАБДЕНЬ.INTL (начальная_дата, дни, [выходные], [праздники])

В котором:

• Start_date (обязательно): дата начала.

• Дни (обязательно): количество рабочих дней до или после start_date. Положительное значение приводит к будущей дате; Отрицательные значения приводят к тому, что дата находится в прошлом; значение 0 возвращает start_date.

• Выходные (необязательно): указывает, какие дни недели являются выходными и не считаются рабочими днями. Выходные - это число или строка выходных, указывающая, когда наступают выходные.

Например: нам нужно указать дату начала и дату окончания работы с указанием выходных, как вы хотите, мы будем использовать функцию WORKDAY.INLT . Start_date - 1 марта 2019 года, дни - 45 дней, выходные - воскресенье по умолчанию. При вводе формулы = РАБДЕНЬ.INTL (B2, C2,11) (1) даст результат 23 апреля 2019 г. (2).

Функция ЧИСТРАБДНИ

Использование: возвращает количество полных рабочих дней от start_date до end_date. Рабочие дни не включают выходные и праздничные дни. Используйте функцию ЧИСТРАБДНИ для расчета начисленных вознаграждений сотрудникам на основе количества отработанных дней за определенный период.

Формула: = ЧИСТРАБДНИ (начальная_дата, конечная_дата, [праздники])

В котором:

• Start_date (обязательно): дата начала.

• End_date (обязательно): дата окончания.

Праздники (необязательно): необязательный диапазон из одного или нескольких дней, которые нужно исключить из рабочего календаря, например, федеральные праздники, государственные праздники и неустановленные праздничные дни.

Пример: использование функции ЧИСТРАБДНИ для расчета количества рабочих дней. Start_date: 1 марта 2019 г., End_date: 2 апреля 2019 г. Если ввести формулу = ЧИСТРАБДЕНЬ (B2, C2) (1), получится 23 (2), что займет 23 дня. Обратите внимание: по умолчанию Excel не работает по субботам и воскресеньям.

Функция ЧИСТРАБДНИ.INTL

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

Формула: = NETWORKDAYS.INTL (начальная_дата, конечная_дата, [выходные], [праздничные дни])

В котором:

• Start_date и end_date (обязательно): количество дней, необходимое для расчета расстояния между ними. Start_date может быть раньше, таким же или позже end_date.

• Выходные (необязательно): указывает, какие дни являются выходными днями и не засчитываются в число полных рабочих дней от start_date до end_date. Выходные могут быть количеством выходных или строкой, указывающей, когда были выходные.

Пример: используя функцию ЧИСТРАБДНИ.ИНТЛ для расчета количества рабочих дней, определите выходной день по своему усмотрению . Start_date - 1 марта 2019 года, End_date - 2 апреля 2019 года, выходные - воскресенье по умолчанию 11. При вводе формулы = NETWORKDAYS.INTL (B2, C2,11) (1) будет возвращено 28 (2), что займет 23 дня на планирование.

7. Функции времени в Excel

Функция ВРЕМЯ

Использование: возвращает время в виде серийного номера.

Формула: = ВРЕМЯ (час, минута, секунда)

В котором:

• Час (обязательно): число от 0 до 32767, обозначающее часы. Любые значения больше 23 будут разделены на 24, а остальные будут считаться значениями часов.

• Минуты (обязательно): число от 0 до 32767, представляющее минуты. Любое значение больше 59 будет преобразовано в часы и минуты.

• Секунда (обязательно): число от 0 до 32767, представляющее секунды. Любое значение больше 59 будет преобразовано в часы, минуты и секунды.

Пример: Чтобы указать 9:46 AM, мы вводим время как 9, Minute - 45, Second - 34. При вводе формулы TIME (0,45,34) (1) будет 9:45 AM (2).

Функция ВРЕМЕННОЕ ЗНАЧЕНИЕ

Использование: возвращает десятичное число времени, представленное текстовой строкой.

Формула: = ВРЕМЯЗНАЧ (время_текст)

В том числе: Time_text (обязательно): текстовая строка, представляющая время в одном из форматов времени Microsoft Excel; например, текстовые строки «18:45» и «18:45» заключаются в кавычки, обозначающие время.

Пример: когда вам нужно найти соответствующее десятичное значение 4:45:34, введите формулу = ВРЕМЯЗНАЧ ("4:45:34") (1), которая выведет 0,198310185 (2).

Функция ЧАС

Использование: Преобразует число в час серийного номера.

Формула: = ЧАС (серийный_номер)

Из них: Serial_number (обязательно): время, в котором указан час, который вы хотите найти. Время можно ввести в виде текстовой строки, заключенной в кавычки.

Например: когда нам нужно указать часы в определенное время, мы используем функцию ЧАС. Ввод формулы = ЧАС ("4:45:34") (1) дает 4 (2).

Функция МИНУТА

Использование: возвращает минуты значения времени. Минуты возвращаются как целое число в диапазоне от 0 до 59.

Формула: = МИНУТА (серийный_номер)

Из них: Serial_number (обязательно): время, в котором указана минута, которую вы хотите найти. Время можно ввести в виде текстовой строки, заключенной в кавычки.

Пример: когда нам нужно указать минуты в определенное время, мы используем функцию МИНУТЫ. При вводе формулы = МИНУТА («4:45:34») (1). даст 45 (2).

8. Функция подсчета и суммирования ячеек по цвету (пользовательская функция)

Для выполнения этих функций нам нужно сначала запрограммировать VBA. VBA - это язык программирования Excel, мы используем VBA, чтобы позволить строкам / операторам автоматически выполнять действия, которые мы хотим делать в Excel.

Ниже приведены шаги по настройке VBA для выполнения команд:

Шаг 1. Откройте электронную таблицу Excel и нажмите Alt + F11, чтобы открыть редактор Visual Basic (VBE).

Шаг 2. Щелкните правой кнопкой мыши Sheet1 Select Insert  Select Module .

Шаг 3. Введите код выберите файл  выберите закрыть и вернитесь в Microsoft Excel .

Код функции GetCellColor

Функция GetCellColor (xlRange As Range)

Dim indRow, indColumn As Long

Dim arResults ()

Application.Volatile

Если xlRange - ничего, тогда

Установите xlRange = Application.ThisCell

Конец, если

Если xlRange.Count 1, то

ReDim arResults (1 в xlRange.Rows.Count, 1 в xlRange.Columns.Count)

Для indRow = 1 до xlRange.Rows.Count

Для indColumn = 1 до xlRange.Columns.Count

arResults (indRow, indColumn) = xlRange (indRow, indColumn) .Interior.Color

следующий

следующий

GetCellColor = arResults

Еще

GetCellColor = xlRange.Interior.Color

Конец, если

Конечная функция

Код функции GetCellFontColor

Функция GetCellFontColor (xlRange As Range)

Dim indRow, indColumn As Long

Dim arResults ()

Application.Volatile

Если xlRange - ничего, тогда

Установите xlRange = Application.ThisCell

Конец, если

Если xlRange.Count 1, то

ReDim arResults (1 в xlRange.Rows.Count, 1 в xlRange.Columns.Count)

Для indRow = 1 до xlRange.Rows.Count

Для indColumn = 1 до xlRange.Columns.Count

arResults (indRow, indColumn) = xlRange (indRow, indColumn) .Font.Color

следующий

следующий

GetCellFontColor = arResults

Еще

GetCellFontColor = xlRange.Font.Color

Конец, если

Конечная функция

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

- GetCellColor

Использование: возвращает цветовой код цвета фона указанной ячейки.

Формула: GetCellColor (ячейка для получения цветового кода)

Пример: когда нам нужно получить цвет фона красной ячейки, мы используем функцию GetCellColor. При вводе формулы = GETCELLCOLOR (C2) (1) мы получим 255 (2).

- Функция GetCellFontColor

Использование: возвращает цветовой код цвета шрифта указанной ячейки.

Формула: GetCellFontColor (ячейка для получения цветового кода)

Пример: когда нам нужно получить цветовой код красного шрифта, мы используем GetCellFontColor.При вводе формулы GETCELLFONTCOLOR (C2) (1) мы получим 255 (2).

- Функция CountCellsByColor

Использование: подсчитывает ячейки с указанным цветом фона.

Формула: CountCellsByColor (Площадь для подсчета, Цветовой код для подсчета)

Пример: когда нам нужно подсчитать количество красных ячеек в выделении от A1 до A15, мы используем функцию CountCellsByColor. Когда вы вводите формулу CountCellsByColor ($ A $ 1: $ A $ 15, C2) (1), мы получим 4 (2).

- Функция CountCellsByFontColor

Использование: подсчет ячеек с указанным цветом шрифта.

Формула: CountCellsByFontColor (Площадь для подсчета, Цветовой код шрифта для подсчета)

Пример: когда нам нужно подсчитать количество ячеек с красным шрифтом в выделении от A1 до A15, мы используем функцию CountCellsByFontColor. При вводе формулы CountCellsByFontColor ($ A $ 1: $ A $ 15, C2) (1) мы получим 6 (2).

- Функция SumCellsByColor

Использование: суммируйте ячейки с определенным цветом фона.

Формула: SumCellsByColor (Подсчитываемая площадь, Цветовой код подсчитываемой ячейки)

Пример: когда нам нужно просуммировать значения ячеек одного и того же красного цвета в диапазоне от A1 до A15, мы используем функцию SumCellsByColor. Когда вы вводите формулу SumCellsByColor ($ A $ 1: $ A $ 15, C2) (1), вы получите 121 (2).

- Функция SumCellsByFontColor

Использование: возвращает сумму ячеек с определенным цветом шрифта.

Формула: SumCellsByFontColor (Площадь для подсчета, Цветовой код шрифта для подсчета)

Пример: когда нам нужно суммировать значения ячеек с одинаковым красным шрифтом в диапазоне от A1 до A15, мы используем функцию SumCellsByFontColor. При вводе формулы SumCellsByFontColor ($ A $ 1: $ A $ 15, C2) (1) мы получим 274 (2).

- Функция WbkCountCellsByColor (ячейка)

Использование: подсчитывает ячейки с указанным цветом фона на всем листе.

Формула: WbkCountCellsByColor (ячейка для подсчета)

Пример: когда нам нужно подсчитать ячейки с одинаковым красным фоном на всем листе, мы используем функцию WbkCountCellsByColor. При вводе формулы WbkCountCellsByColor (C2) (1) мы получим 8 (2).

- Функция WbkSumCellsByColor (ячейка)

Использование: Суммирование ячеек с указанным цветом фона на всем листе.

Формула: WbkSumCellsByColor (ячейка с цветовой кодировкой)

Пример: когда нам нужно подсчитать ячейки с одним и тем же красным шрифтом на всем листе, мы используем функцию WbkCountCellsByColor. При вводе формулы WbkCountCellsByColor (C2) (1) мы получим 98 (2).

ДОМАШНЕЕ ЗАДАНИЕ

1. Изучить материал занятия.

2. Составить конспект лекции.

Перечень рекомендуемых учебных изданий, Интернет-ресурсов, дополнительной литературы.

Основные источники:

1. Гохберг, Г.С. Информационные технологии: учебник для студ. сред. проф. Образования / Гохберг, Г.С, Зафиевский, А.В., Короткин, А.А. - 5 -е изд., стер. – М. : Издательский центр «Академия», 2010. – 208 с.

2. Михеева, Е.В. Практикум по информационным технологиям в профессиональной деятельности: учебное пособие. - М. : Академия, 2008. - 8 – е изд. - 256 с.- (Среднее профессиональное образование).

3. Филимонова, Е.В. Информационные технологии в профессиональной деятельности : учебник. – Изд-е 2-е, доп. и перераб. – Ростов н/Д : Феникс, 2008. – 381. – (СПО).

Интернет - источники:

1. Электронный учебник по информатике и информационным технологиям - http://www.ctc.msiu.ru/

2. Тесты по информатике - http://www.ege.ru/

3. Каталог образовательных Интернет-ресурсов: учебно-методические пособия - www.edu/ru/modules.php

4. Дидактические материалы по информатике - http://comp-science.narod.ru/

5. Портал электронного обучения - http://www.e-learning.by/

6. Электронный учебник «Работа с MS Excel» - http://avanta.vvsu.ru/met_supply/381/Index.htm

7. Практикум по MS Excel - http://informatika.delayu.ru/Zadanija/Exceldannye

Преподаватель: Владимир Александрович Волков