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


Скачать 202.29 Kb.
НазваниеЛабораторная работа
Дата публикации23.03.2013
Размер202.29 Kb.
ТипЛабораторная работа
userdocs.ru > Математика > Лабораторная работа
Тема 5
Лабораторная работа 14.

Текстовые функции, функции даты и времени.
Цель работы:

1. Дать представление о текстовых функциях и описать их возможности для обработки текста: объединение текстовых данных разных ячеек, извлечение символов, поиск нужных и т.д.

2. Указать особенности измерения времени в финансовых расчетах.

3. Выработать навыки решения задач, связанных с учетом даты или времени (например, вычислить стаж работы, определить число рабочих дней на любом промежутке времени и т.д.).
Текстовые функции используют для преобразования и анализа текстовых значений. Текстовые функции преобразуют числовые текстовые значения в числе, числовые значения в строки символов (текстовые строки), а также позволяют выполнять над строками символов различные операции.
Функция ^ ТЕКСТ преобразует число в текстовую строку с заданным форматом.

Синтаксис: =ТЕКСТ(значение;формат).

Аргумент значение может быть любым числом, формулой или ссылкой на ячейку.

Аргумент формат определяет, в каком виде отображается возвращаемая строка. Для задания необходимого формата можно использовать любой из символов форматирования за исключением звездочки. Использование формата Общий не допускается.

Пример1 Следующая формула возвращает текстовую строку 25,25: =ТЕКСТ(101/4;"0,00").

Пример2



Функция РУБЛЬ преобразует число в строку. Однако РУБЛЬ возвращает строку в денежном формате с заданным числом десятичных знаков.

Синтаксис: =РУБЛЬ(число;число_знаков)

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

число — число, ссылка на ячейку, содержащую число, или формула, вычисление которой дает число.

число_знаков — число цифр справа от десятичной запятой. Если аргумент «число_знаков» отрицательный, то число округляется слева от десятичной запятой. Если аргумент «число_знаков» опущен, то он полагается равным 2.

Замечания

Наиболее существенное различие между форматированием ячейки, содержащей число, с помощью команды (на вкладке Главная, в группе Число щелкните стрелку рядом с полем Числовой формат и выберите значение Числовой) и форматированием числа непосредственно с помощью функции РУБЛЬ состоит в том, что функция РУБЛЬ преобразует свой результат в текст. Число, отформатированное с помощью команды Формат ячеек, по-прежнему остается числом. Однако числа, отформатированные функцией РУБЛЬ, можно продолжать использовать в формулах, поскольку в Microsoft Excel числа, введенные как текст, в процессе вычислений преобразуются в числовые значения.

Пример3

 

1

2

3

4

5




A

Данные

1 234,567

-1 234,567

-0,123

99,888

Формула

Описание (результат)

^ =РУБЛЬ(A2; 2)

Отображает первое число в денежном формате, 2 цифры справа от десятичной запятой (1234,57р.)

=РУБЛЬ(A2; -2)

Отображает первое число в денежном формате, 2 цифры слева от десятичной запятой (1200р.)

^ =РУБЛЬ(A3; -2)

Отображает второе число в денежном формате, 2 цифры слева от десятичной запятой ((1200р.))

=РУБЛЬ(A4; 4)

Отображает третье число в денежном формате, 4 цифры справа от десятичной запятой((0,1230р.))

=РУБЛЬ(A5)

Отображает четвертое число в денежном формате, 2 цифры слева от десятичной запятой (99,89р.)






Функция ^ ДЛСТР возвращает количество символов в текстовой строке и имеет следующий синтаксис: =ДЛСТР(текст) Аргумент текст должен быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку.

Функция ДЛСТР возвращает длину отображаемого текста или значения, а не хранимого значения ячейки, при этом она игнорирует незначащие нули. Часто начальные и конечные пробелы не позволяют правильно отсортировать значения в рабочем листе или базе данных. Если вы используете текстовые функции для работы с текстами рабочего листа, лишние пробелы могут мешать правильной работе формул.
Пример4

Следующая формула возвращает значение 7: =ДЛСТР("прибыль").
Пример 5.

 

1

2

3

4



A

Данные

Владивосток

 

   Один  

Формула

Описание (результат)

=ДЛСТР(A2)

Длина первой строки (11)

=ДЛСТР(A3)

Длина второй строки (0)

=ДЛСТР(A4)

Длина третьей строки, которая включает 5 пробелов (9)




Функция СЖПРОБЕЛЫ удаляет начальные и конечные пробелы из строки, оставляя только по одному пробелу между словами.

Синтаксис: =СЖПРОБЕЛЫ(текст)

текст — текст, из которого удаляются пробелы.

Пример6

 

1

2



А

B

Формула

Описание (результат)

=СЖПРОБЕЛЫ(" Доход за первый квартал ")

Удаляет начальные и конечные пробелы из текста в формуле (Доход за первый квартал)




Функция ПЕЧСИМВ аналогична функции СЖПРОБЕЛЫ за исключением того, что она удаляет все непечатаемые символы. Функция ПЕЧСИМВ особенно полезна при импорте данных из других программ, поскольку некоторые импортированные значения могут содержать непечатаемые символы. Эти символы могут проявляться на рабочих листах в виде небольших квадратов или вертикальных черточек. Функция ПЕЧСИМВ позволяет удалить непечатаемые символы из таких данных.

Синтаксис: =ПЕЧСИМВ(текст)

текст — любая информация на рабочем листе, из которой удаляются непечатаемые знаки.

Пример7

 

1

2



A

Данные

=СИМВОЛ(7)&"текст"&СИМВОЛ(7)

Формула

Описание (результат)

=ПЕЧСИМВ(A2)

Возвращает непечатаемый знак СИМВОЛ(7) из верхней строки (текст)





В Excel имеются три функции, позволяющие изменять регистр букв в текстовых строках: ^ ПРОПИСН, СТРОЧН и ПРОПНАЧ.

Функция ПРОПИСН преобразует все буквы текстовой строки в прописные, а СТРОЧН - в строчные. Функция ПРОПНАЧ заменяет прописными первую букву в каждом слове и все буквы, следующие непосредственно за символами, отличными от букв; все остальные буквы преобразуются в строчные.

Функции имеют следующий синтаксис:

=ПРОПИСН(текст)

=СТРОЧН(текст)

=ПРОПНАЧ(текст)
текст — текст, преобразуемый в верхний регистр. Этот аргумент может быть ссылкой на текст или текстовой строкой.

Пример8

 

1

2

3



А

Данные

итоги

Урожай

Формула

Описание (результат)

=ПРОПИСН(A2)

Верхний регистр первой строки (ИТОГИ)

=ПРОПИСН(A3)

Верхний регистр второй строки (УРОЖАЙ)



Проделайте подобные операции для произвольно набранного слова с функциями ^ СТРОЧН и ПРОПНАЧ.
При работе с уже существующими данными довольно часто возникает ситуация, когда нужно модифицировать сами исходные значения, к которым применяются текстовые функции. Можно ввести функцию в те же самые ячейки, где находятся эти значения, поскольку введенные формулы заменят их. Но можно создать временные формулы с текстовой функцией в свободных ячейках в той же самой строке и скопировать результат в буфер обмена. Чтобы заменить первоначальные значения модифицированными, выделите исходные ячейки с текстом, в меню "Правка" выберите команду "Специальная вставка", установите переключатель "Значения" и нажмите кнопку ОК. После этого можно удалить временные формулы.
Функция СОВПАД сравнивает две строки текста на полную идентичность с учетом регистра букв. Различие в форматировании игнорируется.

Синтаксис: =СОВПАД(текст1;текст2).

Если аргументы текст1 и текст2 идентичны с учетом регистра букв, функция возвращает значение ИСТИНА, в противном случае - ЛОЖЬ. Аргументы текст1 и текст2 должны быть строками символов, заключенными в двойные кавычки, или ссылками на ячейки, в которых содержится текст.

Текст1 — первая текстовая строка.

Текст2 — вторая текстовая строка.

Замечания

Для сравнения совпадений вместо функции СОВПАД можно также использовать оператор в виде двойного знака равенства (==). Например, функция =A1==B1 возвращает то же значение, что и функция =СОВПАД(A1;B1).

Пример 9

 

1

2

3

4



A

B

Первая строка

Вторая строка

слово

слово

Слово

слово

сл ово

слово

Формула

Описание (результат)

=СОВПАД(A2;B2)

Проверяет, совпадает ли текст в первой строке таблицы (ИСТИНА)

=СОВПАД(A3;B3)

Проверяет, совпадает ли текст во второй строке таблицы (ЛОЖЬ)

=СОВПАД(A4;B4)

Проверяет, совпадает ли текст в третьей строке таблицы (ЛОЖЬ)




Функция СЦЕПИТЬ, которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом.

Синтаксис: =СЦЕПИТЬ(текст1;текст2;…).
Пример10

Необходимо собрать ФИО в одну ячейку из трех с добавлением пробелов:

= СЦЕПИТЬ(А1; ″″;B1; ″″;C1) .



Полным аналогом данной функции является оператор сцепления строк & (рис.1.).

Рис. 1. Пример использования оператора сцепления строк.
Если сочетать это с функцией извлечения из текста первых букв – ЛЕВСИМВ, то можно получить фамилию с инициалами одной формулой:


Рис. 2. Пример использования функции извлечения символов.
Функции ЛЕВСИМВ и ПРАВСИМВ имеют одинаковый синтаксис:

= ЛЕВСИМВ(текст;число_знаков) – возвращает первые начальные знаки текстовой строки;

= ПРАВСИМВ(текст;число_знаков) – возвращает заданное число последних знаков текстовой строки.
Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ используются для замены части знаков одной текстовой строки символами из другой тестовой строки. Синтаксис: =ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст).

нач_поз—начальная позиция для замены ( кключая пробелы)

число_знаков—число знаков подлежащих замене.
Пример 11

Результатом формулы =ЗАМЕНИТЬ(«Отчет за январь»;10;6; «апрель») будет строка Отчет за апрель.
=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

Текст— текст или ссылка на ячейку, содержащую текст, в котором подставляются знаки.

Стар_текст— заменяемый текст.

Нов_текст— текст, на который заменяется стар_текст.

Номер_вхождения— определяет, какое вхождение фрагмента «стар_текст» нужно заменить фрагментом «нов_текст». Если этот аргумент определен, то заменяется только заданное вхождение фрагмента «стар_текст». В противном случае все вхождения фрагмента «стар_текст» в текстовой строке заменяются фрагментом «нов_текст».
Пример12



^ Разделение столбца с данными на несколько отдельных
Задача разделения столбца с данными на несколько отдельных столбцов решается с помощью Мастера Текстов.
Пример 13

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

  • Выделите ячейки, которые будем делить и выберите в меню Данные - Текст по столбцам. Появится окно Мастера текстов:



Рис. 3. Окно Мастера текстов. 80


  • На первом шаге Мастера выберите формат исходного текста: или это текст, в котором какой-либо символ отделяет друг от друга со-держимое наших будущих отдельных столбцов (с разделителями), или в тексте с помощью пробелов имитируются столбцы одинаковой ширины (фиксированная ширина).

  • На втором шаге Мастера, если выбран формат с разделителями (рис.6.4.), необходимо указать какой именно символ является разделителем:



Рис. 4. Диалоговое окно Мастера текстов

(выбор символа-разделителя).


  • На третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:

общий – оставит данные как есть, подходит в большинстве случаев;

дата – необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняет-ся в выпадающем списке;

текстовый – для чисто текстовой информации.



5. Диалоговое окно Мастера текстов (выбор формата данных).


  • Осталось нажать кнопку Готово, утвердительно ответить на вопрос о замене конечных ячеек.

Получится следующий результат:



Рис. 6. Результат разделения данных на три столбца.
^ Функции дат и времени

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

Excel 2007 (учитывая российские региональные настройки) позволяет вводить дату разными способами:

  • "Классическая" форма 3.04.2010;

  • сокращенная форма 3.04.10;

  • с использованием дефисов 3-04-10;

  • с использованием дроби 3/04/10.


Внешний вид даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается в меню Формат - Ячейки:



Рис. 7. Задание формата Дата.
Время вводится в ячейки с использованием двоеточия. Например, 16:45, можно дополнительно уточнить количество секунд, вводя их также через двоеточие:16:45:30, можно указывать дату и время вместе через пробел:


Рис. 8. Пример ввода даты и времени в ячейку.
Любую дату Excel хранит и обрабатывает как число с целой и дробной частью. Это можно увидеть, если выделить ячейку с датой и установить для нее Общий формат (меню Формат - Ячейки – вкладка Число - Общий), например, на рис.9.показано как выглядит 8.10.2006 15:42:


Рис.9. Внутренний формат представления времени в ячейке.
Целая часть числа (38998) – это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0).

Из этих фактов следуют два чисто практических вывода:

  • Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года;

  • с датами и временем в Excel возможно выполнять любые математические операции, именно потому, что на самом деле они – числа.


Можно вычислить, сколько дней прошло между двумя датами, например =″24/01/10″-″19/01/10″. Можно прибавлять к дате или вычитать из нее определенное количество дней. Для быстрого ввода номеров кварталов, названий дней и месяцев можно воспользоваться автозаполнением и списками. Для ввода в ячейку текущей даты как константы можно воспользоваться сочетанием клавиш Ctrl + Ж.

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


Рис. 10.Выбор способа отображения даты.
Категория функций работы с датами и временем позволяет выпол-нять разнообразные преобразования над соответствующими данными. Всего в категории «Дата и время» имеется 20 функций.

Таблица.

^ Функции категории «Дата и время»

. Функция

Описание

ДАТА

Возвращает заданную дату в числовом формате.

ДАТАЗНАЧ

Преобразует дату из текстового формата в числовой формат.

ДЕНЬ

Преобразует дату в числовом формате в день месяца.

ДНЕЙ360

Вычисляет количество дней между двумя датами на основе 360-дневного года.

ДАТАМЕС

Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.

КОНМЕСЯЦА

Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

МЕСЯЦ

Преобразует дату в числовом формате в месяцы.

ЧАС

Преобразует дату в числовом формате в часы.

МИНУТЫ

Преобразует дату в числовом формате в минуты.

СЕКУНДЫ

Преобразует дату в числовом формате в секунды.

ЧИСТРАБДНИ

Возвращает количество рабочих дней между двумя датами.

ТДАТА

Возвращает текущую дату и время в числовом формате.

ВРЕМЯ

Возвращает заданное время в числовом формате.

ВРЕМЗНАЧ

Преобразует время из текстового формата в числовой формат.

СЕГОДНЯ

Возвращает текущую дату в числовом формате.

ДЕНЬНЕД

Преобразует дату в числовом формате в день недели.

НОМНЕДЕЛИ

Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указан-ная дата.

РАБДЕНЬ

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

ГОД

Преобразует дату в числовом формате в год.

ДОЛЯГОДА

Возвращает долю года, которую составляет количество дней между начальной и конечной датой.


Если нужно, чтобы в ячейке всегда была автоматически обновляемая сегодняшняя дата нужно воспользоваться функцией СЕГОДНЯ(). Значение в ячейке будет обновляться при открытии файла.


Рис. 11. Отображение текущего числа в ячейке.
Функцию СЕГОДНЯ можно использовать для вставки не только текущей, но и вообще любой автоматически обновляемой даты. Для этого надо после функции ввести со знаком плюс или минус соответствующее число дней. Например, для вставки вчерашней даты достаточно ввести -1.

Рис. 12. Вычисление количества дней, прошедших от текущей даты.
Для вставки текущей даты и времени можно использовать функцию ТДАТА(). Значение в ячейке будет обновляться при открытии файла, а также после любых вычислений в книге или вводе данных на любой лист. Этой функцией удобно пользоваться при подготовке и распечатке счетов. Для вычисления дня недели любой произвольной даты можно использовать функцию ДЕНЬНЕД(А;В), где A – дата, для которой определяется день недели. Дату можно вводить обычным порядком; В – тип отсчета дней недели: 1 – отсчет дней недели начинается с воскресенья; 2 – отсчет дней недели начинается с понедельника. Например, можно проверить при помощи функции ДЕНЬНЕД в какой день недели Вы родились (рис.13):


Рис. 13. Два способа отображения дня недели.
Другой способ: выделить ячейку с датой, открыть меню Формат - Ячейки, вкладка Число, далее Все форматы и ввести в строку Тип новый формат ДДДД (рис. 13).

Узнать, каким днем неделя является текущее число, можно используя формулу: =ДЕНЬНЕД(СЕГОДНЯ()). Чтобы первого числа каждого месяца в ячейке выводился текст Начало месяца и ничего не выводилось в остальных случаях, можно составить формулу: =ЕСЛИ(ДЕНЬ(СЕГОДНЯ())=1; ″Начало месяца″;″″). Для построения более сложных условий можно использовать и другие логические функции. Например, если нужно выяснить, а не пятница ли сегодня и не тринадцатое ли число, можно создать формулу: =ЕСЛИ(И(ДЕНЬНЕД(СЕГОДНЯ())=6;ДЕНЬ(СЕГОДНЯ())=13); ″Осторожно, сегодня пятница 13-е!″;″Обычный день″). Некоторые полезные функции категории «Дата и время» доступны, если установить надстройку Пакет анализа. Для этого нужно нажать кнопку «Office», зайти в Параметры Excel и выбрать (поставить галочку) приложение Пакет анализа. Наиболее полезной из новых функций является функция КОНМЕСЯЦА(начальная_дата; число_месяцев). Положительное значение аргумента число_месяцев означает будущую дату, отрицательное значение означает прошедшую дату. Функция возвращает последнюю дату месяца, который отстоит на заданное число месяцев от текущей даты. При вычислении количества рабочих дней между двумя датами ненужно учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ из надстройки Пакета анализа. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (праздников):

Сдвиг даты на заданное количество рабочих дней осуществляет функция РАБДНИ из надстройки Пакет анализа. Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздников). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ описанной выше.
Задание 1.

1. Откройте новую книгу в Excel.

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

3. Если Занятие продолжается с 9:30 до 11:00 без перерыва. Какова продолжительность занятия в минутах и сколько академических часов (45 мин) оно продолжается?

4. Для текущей даты вычислить:

a) Порядковый номер дня с начала года;

b) Сколько осталось дней до конца недели, до конца месяца, до конца недели.

5. Получите даты с шагом два месяца для начальной даты 31/12/2009 и конечной даты 31/12/2010.

6. Получите рабочие дни для июня 2010 г.

7. Сосчитайте количество рабочих дней в июне 2010 г. Проверьте по календарю.

8. Введите в столбец А даты от 1 марта до 30 апреля 2010 года. Наложите на диапазон условный формат, чтобы даты, на которые приходятся суббота и воскресенье, отображались полужирным красным шрифтом. Указание: Выделить блок с датами. На вкладке Условное форматирование выбрать Создание правила форматирования - Использовать формулу для форматирования выделенных ячеек, ввести формулу =ИЛИ(ДЕНЬНЕД(A1;2)=6;ДЕНЬНЕД(A1;2)=7).
Задание 2.

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

1. Требуется создать таблицу, состоящую из граф: «№ п/п» (1), “ФИО сотрудника» (2), «Дата приема на работу» (3), «Стаж работы» (4), «Оклад сотрудника» (5), «Модифицированный оклад» (6), «Премия сотрудника» (7).

2. Данные граф 1, 2, 3, 5 задайте самостоятельно.

3. Значение графы 4 рассчитайте с помощью функций даты и времени ДНЕЙ360 или ГОД и СЕГОДНЯ.

4. Для приведения сотрудников к одному знаменателю рассчитывается промежуточный показатель – модифицированный оклад, зависящий от стажа работы сотрудника (если стаж работы не менее пяти лет, то размер модифицированного оклада равен двойному окладу, в противном случае модифицированный оклад равен окладу). При расчете графы 6 используйте функцию ЕСЛИ.

5. Рассчитайте коэффициент распределения (К), как отношение всей суммы премиальных средств N к сумме всех модифицированных окладов. Данный коэффициент показывает, сколько рублей премии приходится на рубль модифицированного оклада.

6. Вычислить премию каждого сотрудника путем умножения величины модифицированного оклада на коэффициент распределения. Полученную премию следует округлить до целого. При расчете графы 7 используйте функцию ОКРУГЛ и абсолютную ссылку

Похожие:

Лабораторная работа iconЛабораторная работа №3
Цель занятия: Работа в программе Проводник. Работа в системе окон Мой компьютер; быстрый поиск объектов; настройки пользовательского...
Лабораторная работа iconЛабораторная работа №1
Работа в интегрированной среде borland pascal на примере программ линейной структуры
Лабораторная работа iconЛабораторная работа № Работа с массивами и записями
Получить представление о том, что такое массив и научиться разрабатывать алгоритмы решения задач с использованием массивов в среде...
Лабораторная работа iconЛабораторная работа Работа с почтовым клиентом
Майкрософт. Office Outlook 2010 помогает пользователям лучше распоряжаться временем и информацией, устанавливать любые контакты,...
Лабораторная работа iconЛабораторная работа №6 Работа с отчетами
Получить практические навыки работы с отчетами в бд microsoft Office Access 2003, научиться создавать отчеты и задавать параметры...
Лабораторная работа iconЛабораторная работа

Лабораторная работа iconЛабораторная работа №3 Работа с данными в таблицах
Получить практические навыки работы с данными в бд microsoft Office Access 2003, научиться применять фильтры для отбора необходимых...
Лабораторная работа iconЛабораторная работа №1 по рцб: «Практические основы Интернет-трейдинга»

Лабораторная работа iconЛабораторная работа №1 «Анализ полной стоимости в логистике» По дисциплине: «Логистика»

Лабораторная работа iconЛабораторная работа №74
Технология получения отверстия в заготовке электроэрозионной (электроискровой) обработкой
Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2020
контакты
userdocs.ru
Главная страница