З адание 5 . Р абота с т аблицами и д и аг раммами.

 

1. Откройте файл Таблицы и д иаграммы.хlsх.

 

2. Перейдите на Лист 2. Присвойте листу 2 имя «Динамика»

 

3. Составьте таблицу по приведенному образцу:

 

Таблица. Динамика изменения численности населения Земли в ХХ веке.

 

	%		%		%

 

Регионы мира весь мир

и      1900

 

Млн. чел.       %

1950         1990         2000

 

Млн.             Млн.             Млн. чел.                 чел.               чел.

 

Россия, Зарубеж-  506               738           1062         1109 ная Европа, Се-

верная Америка

 

Африка,   Зару- 1 144             1776         4 204        5110 бежная Азия, Ла-

тинская Америка

 

Весь мир        1 656            2 527        5 292        6 252

 

4. Ячейкам последней строки (с числами) присвойте с обственные име-

 

на, которые используйте в качестве абсолютных ссылок при вычисле-

 

нии данных в процентах.

 

 

73

 

 

5. Выполните необходимые вычисления и заполните ячейки таблицы.

 

6. Отформатируйте данные в таблице.

 

7. Оформите таблицу рамками и заливкой.

 

8. Скройте в таблице н есмежные столбцы с численностью населения

 

(млн. чел.). Оставьте только столбцы с процентным составом.

 

9. Постройте диаграмму на основе созданной таблицы. Тип диа-

 

граммы выберите сами (пример на рис.5.7.).

 

 

Россия, Зарубежная

 

100%

 

80%

 

60%

 

40%

 

20%

Европа, Северная Америка

 

Зарубежная Азия, Африка, Латинская Америка

 

Весь мир

 

0%

1900  1950 1990  2000

 

 

Рис. 5.7. Динамика изменения численности населения Земли.

 

 

10.Проанализируйте содержание построенной диаграммы.

 

11.Измените тип диаграммы.

 

12.Исследуйте возможности редактирования созданной диаграммы

 

(создайте заголовок диаграммы, поэкспериментируйте с тенью,

 

объемом и окрашиванием элементов диаграммы).

 

13.Сохраните работу в своей папке.

 

74

 

Л а б ораторная работа 6. Текстовые функции, функции

 

Д аты и времени.

 

Цель работы:

 

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

 

для обработки текста: объединение текстовых данных разных ячеек,

 

извлечение символов, поиск нужных и т.д.

 

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

 

3. Выработать навыки решения задач, связанных с учетом даты или вре-

 

мени (например, вычислить стаж работы, определить число рабочих

 

дней на любом промежутке времени и т.д.).

 

Текстовые функции используют для преобразования и анализа тек-

 

стовых значений. Текстовые функции преобразуют числовые текстовые

 

значения в числ, числовые значения в строки символов (текстовые строки),

 

а также позволяют выполнять над строками символов различные операции.

 

Функция Т Е КСТ преобразует число в текстовую строку с заданным

 

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

 

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

 

ячейку. Аргумент формат определяет, в каком виде отображается возвра-

 

щаемая строка. Для задания необходимого формата можно использовать

 

любой из символов форматирования за исключением звездочки. Использо-

 

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

 

вращает текстовую строку 25,25: =ТЕКСТ(101/4;"0,00").

 

Функция РУ Б ЛЬ преобразует число в строку. Однако РУБЛЬ воз-

 

вращает строку в денежном формате с заданным числом десятичных зна-

 

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

 

 

75

 

 

При этом Excel при необходимости округляет число. Если аргумент чис-

 

ло_знаков опущен, Excel использует два десятичных знака, а если значение

 

этого аргумента отрицательное, то возвращаемое значение округляется

 

слева от десятичной запятой.

 

Функция ДЛСТР возвращает количество символов в текстовой

 

строке и имеет следующий синтаксис: =ДЛСТР(текст)

 

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

 

кавычки, или ссылкой на ячейку. Например, следующая формула возвра-

 

щает значение 7: =ДЛСТР("прибыль").

 

Функция ДЛСТР возвращает длину отображаемого текста или значения, а

 

не хранимого значения ячейки, при этом она игнорирует незначащие нули.

 

Часто начальные и конечные пробелы не позволяют правильно от-

 

сортировать значения в рабочем листе или базе данных. Если вы исполь-

 

зуете текстовые функции для работы с текстами рабочего листа, лишние

 

пробелы могут мешать правильной работе формул. Функция

 

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

 

ляя только по одному пробелу между словами. Синтаксис:

 

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

 

Функция ПЕЧСИМВ аналогична функции СЖПРОБЕЛЫ за исклю-

 

чением того, что она удаляет все непечатаемые символы. Функция

 

ПЕЧСИМВ особенно полезна при импорте данных из других программ,

 

поскольку некоторые импортированные значения могут содержать непеча-

 

таемые символы. Эти символы могут проявляться на рабочих листах в ви-

 

де небольших квадратов или вертикальных черточек. Функция ПЕЧСИМВ

 

позволяет удалить непечатаемые символы из таких данных. Синтаксис:

 

=ПЕЧСИМВ(текст)

 

В Excel имеются три функции, позволяющие изменять регистр букв

 

в текстовых строках: ПРОПИСН, С Т Р ОЧН и ПРОПНАЧ. Функция

 

 

76

 

 

ПРОПИСН преобразует все буквы текстовой строки в прописные, а

 

СТРОЧН - в строчные. Функция ПРОПНАЧ заменяет прописными первую

 

букву в каждом слове и все буквы, следующие непосредственно за симво-

 

лами, отличными от букв; все остальные буквы преобразуются в строчные.

 

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

 

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

 

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

 

=ПРОПНАЧ(текст)

 

При работе с уже существующими данными довольно часто возни-

 

кает ситуация, когда нужно модифицировать сами исходные значения, к

 

которым применяются текстовые функции. Можно ввести функцию в те

 

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

 

мулы заменят их. Но можно создать временные формулы с текстовой

 

функцией в свободных ячейках в той же самой строке и скопируйте ре-

 

зультат в буфер обмена. Чтобы заменить первоначальные значения моди-

 

фицированными, выделите исходные ячейки с текстом, в меню "Правка"

 

выберите команду "Специальная вставка", установите переключатель

 

"Значения" и нажмите кнопку ОК. После этого можно удалить временные

 

формулы.

 

Функция СОВПАД сравнивает две строки текста на полную иден-

 

тичность с учетом регистра букв. Различие в форматировании игнорирует-

 

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

 

Если аргументы т екст1 и т е к ст2 идентичны с учетом регистра букв,

 

функция возвращает значение ИСТИНА, в противном случае - ЛОЖЬ. Ар-

 

гументы т е к ст1 и т екст2 должны быть строками символов, заключен-

 

ными в двойные кавычки, или ссылками на ячейки, в которых содержится

 

текст.

 

 

77

 

 

В категории Текстовые есть функция СЦЕПИТЬ, которая соединяет

 

содержимое нескольких ячеек (до 255) в одно целое, позволяя комбиниро-

 

вать их с произвольным текстом. Синтаксис:

 

=СЦЕПИТЬ(текст1;текст2;…).

 

Вот, например, как можно собрать ФИО в одну ячейку из трех с до-

 

бавлением пробелов: = СЦЕПИТЬ(А1; ″″;B1; ″″;C1) (рис. 6.1.).

 

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

 

строк & (рис.6.1.).

 

Рис. 6.1. Пример использования оператора сцепления строк.

 

Если сочетать это с функцией извлечения из текста первых букв –

 

Л ЕВ СИМВ, то можно получить фамилию с инициалами одной формулой:

 

Рис. 6.2. Пример использования функции извлечения символов.

 

Функции ЛЕВСИМВ и ПРАВСИМВ имеют одинаковый синтаксис:

 

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

 

текстовой строки;

 

= ПРАВСИМВ(текст;число_знаков) – возвращает заданное число по-

 

следних знаков текстовой строки.

 

Функции З АМЕНИТЬ и ПОДСТАВИТЬ используются для замены

 

части знаков одной текстовой строки символами из другой тестовой стро-

 

ки. Синтаксис:

 

=ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст).

 

 

78

 

 

=ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

 

Например, результатом формулы =ЗАМЕНИТЬ(«Отчет за ян-

 

в арь»;10;6; «апрель») будет строка Отчетз аапрель.

 

Задача разделения столбца с данными на несколько отдельных столб-

 

цов решается с помощью М астера Те к с т о в.

 

Например, ФИО записаны в одном столбце (а надо в трех отдельных,

 

чтобы сортировать по имени), полное описание товара задано в одном

 

столбце (а надо отдельный столбец под фирму-изготовителя, отдельный

 

под модель и т.д.).

 

Порядок действий для того, чтобы разделить данные на несколько

 

столбцов:

 

Выделите ячейки, которые будем делить и выберите в меню Д анные

 

-Т екстпос т олбцам. Появится окно М астера т е к стов:

 

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

 

 

79

 

 

На первом шаге М астера выберите формат исходного текста: или

 

это текст, в котором какой-либо символ отделяет друг от друга со-

 

держимое наших будущих отдельных столбцов (с разделителями),

 

или в тексте с помощью пробелов имитируются столбцы одинаковой

 

ширины (фиксированная ширина).

 

На втором шаге М астера, если выбран формат с разделителями

 

(рис.6.4.), необходимо указать какой именно символ является разде-

 

лителем:

 

Рис. 6.4. Диалоговое окно М астера т екстов (выбор символа-разделителя).

 

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

 

их предварительно в окне Мастера, необходимо выбрать формат:

 

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

 

случаев;

 

─ д ата – необходимо выбирать для столбцов с датами, причем

 

формат даты (день-месяц-год, месяц-день-год и т.д.) уточняет-

 

ся в выпадающем списке;

 

 

80

 

 

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

 

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

 

Осталось нажать кнопку Г отово, утвердительно ответить на вопрос о

 

замене конечных ячеек. Получится следующий результат:

 

Рис. 6.6. Результат разделения данных на три столбца.

 

Функции дат и времени используют для преобразования форматов

 

даты и времени, вычислений промежутков времени, а также для вставки на

 

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

 

81

 

 

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

 

ет вводить дату разными способами:

 

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

 

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

 

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

 

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

 

Внешний вид даты в ячейке может быть очень разным (с годом или без,

 

месяц числом или словом и т.д.) и задается в меню Формат-Ячейки:

 

 

Рис. 6.7. Задание формата Дата.

 

Время вводится в ячейки с использованием двоеточия. Например, 16:45,

 

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

 

двоеточие: 16:45:30, можно указывать дату и время вместе через пробел:

 

82

 

 

Рис. 6.8. Пример ввода даты и времени в ячейку.

 

Любую дату Excel хранит и обрабатывает как число с целой и дроб-

 

ной частью. Это можно увидеть, если выделить ячейку с датой и устано-

 

вить для нее Общий формат (меню Формат-Ячейки – вкладка Ч и сло -

 

Общий), например, на рис.6.9.показано как выглядит 8.10.2006 15:42:

 

 

Рис.6.9. Внутренний формат представления времени в ячейке.

 

Целая часть числа (38998) – это количество дней, прошедших с 1 ян-

 

варя 1900 года (взято за точку отсчета) до текущей даты. А дробная часть

 

(0,65417), соответственно, доля от суток (1сутки = 1,0).

 

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

 

Excel не умеет работать (без дополнительных настроек) с датами ра-

 

нее 1 января 1900 года;

 

с датами и временем в Excel возможно выполнять любые математи-

 

ческие операции, именно потому, что на самом деле они – числа.

 

Можно вычислить, сколько дней прошло между двумя датами, на-

 

пример =″24/01/10″-″19/01/10″. Можно прибавлять к дате или вычитать из

 

нее определенное количество дней.

 

Для быстрого ввода номеров кварталов, названий дней и месяцев можно

 

воспользоваться автозаполнением и списками. Для ввода в ячейку текущей

 

даты как константы можно воспользоваться сочетанием клавиш Ctrl + Ж.

 

 

83

 

 

Если скопировать ячейку с датой (протянуть за правый нижний угол ячей-

 

ки), удерживая правую кнопку мыши, то можно выбрать, как именно ко-

 

пировать выделенную дату (рис. 6.10.):

 

Рис. 6.10.Выбор способа отображения даты.

 

Категория функций работы с датами и временем позволяет выпол-

 

нять разнообразные преобразования над соответствующими данными.

 

Всего в категории «Дата и время» имеется 20 функций

 

Таблица. Функции к атегории «Дата и время».

 

Ф у н кц и я                                         Описание

 

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

 

ДАТАЗНАЧ      Преобразует дату из текстового формата в числовой формат.

 

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

 

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

 

ДАТАМЕС       Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от началь-ной даты.

 

КОНМЕСЯЦА  Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

 

84

 

 

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

 

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

 

МИНУТЫ         Преобразует дату в числовом формате в минуты.

 

СЕКУНДЫ        Преобразует дату в числовом формате в секунды.

 

ЧИСТРАБДНИ Возвращает количество рабочих дней между двумя датами.

 

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

 

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

 

ВРЕМЗНАЧ      Преобразует время из текстового формата в числовой формат.

 

СЕГОДНЯ        Возвращает текущую дату в числовом формате.

 

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

 

НОМНЕДЕЛИ  Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указан-ная дата.

 

РАБДЕНЬ         Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.

 

ГОД                  Преобразует дату в числовом формате в год. ДОЛЯГОДА      Возвращает долю года, которую составляет количест-

во дней между начальной и конечной датой.

 

Если нужно, чтобы в ячейке всегда была автоматически обновляемая

 

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

 

ние в ячейке будет обновляться при открытии файла.

 

 

Рис. 6.11. Отображение текущего числа в ячейке.

 

 

85

 

 

Функцию СЕГОДНЯ можно использовать для вставки не только те-

 

кущей, но и вообще любой автоматически обновляемой даты. Для этого

 

надо после функции ввести со знаком плюс или минус соответствующее

 

число дней. Например, для вставки вчерашней даты достаточно ввести -1.

 

 

Рис. 6.12. Вычисление количества дней, прошедших от текущей даты

 

Для вставки текущей даты и времени можно использовать функцию

 

Т ДАТА(). Значение в ячейке будет обновляться при открытии файла, а

 

также после любых вычислений в книге или вводе данных на любой лист.

 

Этой функцией удобно пользоваться при подготовке и распечатке счетов.

 

Для вычисления дня недели любой произвольной даты можно ис-

 

пользовать функцию ДЕНЬНЕД(А;В), где A – дата, для которой опреде-

 

ляется день недели. Дату можно вводить обычным порядком;

 

В – тип отсчета дней недели: 1 – отсчет дней недели начинается с воскре-

 

сенья; 2 – отсчет дней недели начинается с понедельника.

 

Например, можно проверить при помощи функции ДЕНЬНЕД в какой

 

день недели Вы родились (рис.6.13):

 

 

Рис. 6.13. Два способа отображения дня недели.

 

Другой способ: выделить ячейку с датой, открыть меню Формат-

 

Ячейки, вкладка Ч и сло, далее Вс е форматы и ввести в строку Т и п новый

 

формат Д ДДД (рис. 6.13).

 

 

86

 

 

Узнать, каким днем неделя является текущее число, можно исполь-

 

зуя формулу: = ДЕНЬНЕД(СЕГОДНЯ()). Чтобы первого числа каждого

 

месяца в ячейке выводился текст Началомес яца и ничего не выводилось в

 

остальных случаях, можно составить формулу:

 

=ЕСЛИ(ДЕНЬ(СЕГОДНЯ())=1; ″Начало месяца″;″″).

 

Для построения более сложных условий можно использовать и дру-

 

гие логические функции. Например, если нужно выяснить, а не пятница ли

 

сегодня и не тринадцатое ли число, можно создать формулу:

 

=ЕСЛИ(И(ДЕНЬНЕД(СЕГОДНЯ())=6;ДЕНЬ(СЕГОДНЯ())=13);

 

″ Осторожно, се г о дн я п ят н и ц а 13-е!″;″Обычный день″).

 

Некоторые полезные функции категории «Дата и время» доступны,

 

если установить надстройку Пакет анализа. Для этого нужно нажать

 

кнопку «Office», зайти в Параметры E x cel и выбрать (поставить галочку)

 

приложение Пакет анализа.

 

Наиболее полезной из новых функций является функция

 

КОНМЕСЯЦА(начальная_дата; ч и с л о _месяцев). Положительное зна-

 

чение аргумента ч и сло_месяцев означает будущую дату, отрицательное

 

значение означает прошедшую дату. Функция возвращает последнюю дату

 

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

 

При вычислении количества рабочих дней между двумя датами не-

 

нужно учитывать субботы с воскресеньями и праздники. Для такого расче-

 

та лучше воспользоваться функцией Ч И СТРАБДНИ из надстройки Паке-

 

т а а н ализа. В качестве аргументов этой функции необходимо указать на-

 

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

 

 

87

 

 

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

 

Сдвиг даты на заданное количество рабочих дней осуществляет

 

функция Р АБДНИ из надстройки Пакет анализа. Она позволяет вычис-

 

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

 

нужное количество рабочих дней (с учетом выходных суббот и воскресе-

 

ний и государственных праздников). Использование этой функции полно-

 

стью аналогично применению функции Ч ИСТРАБДНИ описанной выше.

 

З адание 1.

 

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

 

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

 

ячейку В2 с использованием формулы введите дату и время послед-

 

него изменения данных на листе.

 

3. Занятие продолжается с 9:30 до 11:00 без перерыва. Какова продол-

 

жительность занятия в минутах и сколько академических часов (45

 

мин) оно продолжается?

 

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

 

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

 

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

 

конца недели.

 

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

 

конечной даты 31/12/2010.

 

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

 

 

88

 

 

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 используйте функцию ЕСЛИ.

 

 

89

 

 

5. Рассчитайте коэффициент распределения (К), как отношение всей

 

суммы премиальных средств N к сумме всех модифицированных ок-

 

ладов. Данный коэффициент показывает, сколько рублей премии

 

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

 

6. Вычислить премию каждого сотрудника путем умножения величины

 

модифицированного оклада на коэффициент распределения. Полу-

 

ченную премию следует округлить до целого. При расчете графы 7

 

используйте функцию ОКРУГЛ и абсолютную ссылку.

 

 

90