Добавим к таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая: =(A2="Копейка")*(B2="Григорьев") Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, в результате умножения двух выражений, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:

Рисунок 3.3 Выборочное суммирование по двум критериям

Задание 4.

1. Создать таблицу по образцу: Продажи холодильников

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

3. Подсчитать количество холодильников, произведенных каждой страной. (Данные оформить в виде новой таблицы).

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

5. Если оборот больше 10000 $, то написать сообщение о предоставляемой скидке 2%. 6. Подсчитать число проданных холодильников, вес которых больше их среднего веса.

7. Добавьте столбцы «Новая цена» и «Новая сумма». В столбце «Новая цена» рассчитайте новую цену, используя функцию ЕСЛИ. Известно, что производители России и Южной Кореи снизили цены на 10%, а производители Франции и Испании подняли цены на 16% (единая формула вводится в первую ячейку столбца «Новая цена»).

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

9. Используя условный фильтр, отметьте в столбце «Вес» ячейки, значение которых больше 70, желтым фоном и жирным красным шрифтом, а ячейки, значение которых меньше или равно 60, синим фоном и жирным желтым шрифтом.

10. Сохраните файл под именем «Холодильники.xlsx».

Лабораторная работа 4 Работа с диаграммами

Цели работы:

1. Освоение навыков практической работы по созданию и редактированию диаграмм.

2. Изучение возможностей изменения и замены источника данных, добавления и удаления элементов диаграммы, изменения типа и размеров диаграммы и ее расположения на листе и в книге.

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

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

Диаграмма может располагаться как графический объект на листе с данными (не обязательно на том же, где находятся данные, взятые для построения диаграммы). На одном листе с данными может находиться несколько диаграмм. Диаграмма может располагаться на отдельном специальном листе.

Диаграмма постоянно связана с данными, на основе которых она создана, и обновляется автоматически при изменении исходных данных. Более того, изменение положения или размера элементов данных на диаграмме может привести к изменению данных на листе.

В Excel 2010 можно создавать различные диаграммы. Всего существует 11 типов встроенных диаграмм, каждый из которых имеют еще множество разновидностей (видов). Выбор типа диаграммы определяется задачами, решаемыми при ее создании. Обычно стандартной (используется по умолчанию) диаграммой является плоская гистограмма.

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

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

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

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

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

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



Рисунок 4.1 Инструменты панели Диаграмма вкладки Вставка.

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

Рисунок 4.2 Выбор типа и вида создаваемой диаграммы

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

Круговая диаграмма служит для сравнения нескольких величин. Особенно наглядна, если сумма значений величин составляет нечто целое, например, 100%. Например, при продаже канцелярских товаров, имеем следующую таблицу:

1. Необходимо показать долю реализации каждого товара за день, ис-пользуя круговую диаграмму.

Порядок действий:

1) Выделите фрагмент таблицы (блок ячеек А1:В3), содержащий данные для построения диаграммы.

2) На вкладке Вставка в группе Диаграммы щелкните по кнопке с нужным типом диаграмм и в галерее выберите конкретный вид круговой диаграммы.

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

Рисунок 4.3 Созданная диаграмма

Диаграмма, созданная на листе с данными, первоначально располо-жена по умолчанию примерно в центре видимой части листа. Диаграмму, размещенную на листе с данными, можно переместить на отдельный лист:

1. Нажмите кнопкуПереместить диаграмму в группе Расположение вкладки Работа с диаграммами/Конструктор.

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

После вставки диаграммы в окне Excel 2010 появляется контекстный инструмент Работа с диаграммами, содержащий три ленты Конструктор, Макет, Формат, с помощью которых можно ее отредактировать.

Для изменения какого-либо элемента диаграммы следует его выделить, щелкнув по нему мышью. Признаком выделения являются рамка и маркеры элемента. Линейные элементы (оси, линии тренда и т.п.) рамки не имеют. Количество маркеров может быть различным для разных элементов диаграмм. Одновременно может быть выделен только один элемент диаграммы.

Для выделения отдельных элементов диаграммы можно также использовать раскрывающийся список Элементы диаграммы группы Текущий фрагмент контекстной вкладки Работа с диаграммами/Макет (рис. 4.4.).

Рисунок 4.4 Выделение элементов диаграммы

Можно добавлять и удалять отдельные элементы созданной диаграммы (название, легенду, подписи данных, сетку, т.д.). Для этого используют элементы группы Подписи вкладки Работа с диаграммами/Макет.

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

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

Для профессионального оформления диаграммы можно воспользоваться готовыми макетами диаграмм и стилями диаграмм (вкладка Работа с диаграммами/Конструктор) либо вручную задать форматирование любых элементов диаграммы, например осей, заголовков и других подписей (используют элементы вкладки Работа с диаграммами/Формат). В Excel 2010 доступны такие визуальные эффекты, как трехмерность изображения, плавное затенение и сглаживание, что помогает выделять ключевые тенденции и создавать более привлекательное графическое отображение данных. Для установки параметров оформления элементов можно использовать диалоговые окна. Для отображения окна нужно выделить элемент диаграммы и в группе Текущий фрагмент вкладки Работа с диаграммами/Формат нажать кнопку Формат выделенного элемента.

Задание 1.При продаже канцелярских товаров в течение недели наблюдалась следующая динамика:



1. Создайте диаграмму типа "гистограмма" для данной таблицы.

2. Используя элементы группы Подписи вкладки Работа с диаграммами/Макет, добавьте на диаграмму название диаграммы, линии сетки, легенду и подписи данных. В названии диаграммы укажите «Динамика продаж за неделю».

3. Добавьте название горизонтальной оси «Дни недели», вертикальной оси «Количество».

4. Переместите диаграмму к левому краю листа. Увеличьте размер диаграммы по горизонтали примерно в 1,5 раза.

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

6. Переместите диаграмму на отдельный лист. Листу присвойте имя Продажи за неделю.

7. Для области диаграммы выберите один из стилей Слабый эффект. Для области построения установите заливку бледно-синим цветом. Для легенды установите темно-синий цвет шрифта и размер шрифта 12 пт. Для горизонтальной оси установите толщину линии 2,25 пт. Для горизонтальной оси установите синий цвет линии. Для вертикальной оси установите максимальное значение 100.

8. Удалите диаграмму.

9. Сохраните файл именем «Гистограмма.xlsx»

Изменение диапазона данных

После создания диаграммы можно изменить диапазон данных, представленных на диаграмме:

1. В группе Диапазон вкладки Работа с диаграммами/Конструктор нажмите кнопкуВыбрать данные.

2. Для взаимной замены данных на осях надо воспользоваться кнопкой "Строка/Столбец

3. Для задания нового диапазона данных в окне Выбор источника данных очистите поле Диапазон данных для диаграммы, а затем выделите на листе нужный диапазон данных.

Рисунок 4.5 Изменение диапазона источника данных

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

1. В группе Диапазон вкладки Работа с диаграммами/Конструктор нажмите кнопкуВыбрать данные.

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

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

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

Задание 2. Известно поквартальное изменение цен основных продуктов:

1. Создайте диаграмму типа "гистограмма" для всей таблицы.

2. Добавьте название диаграммы и подписи данных. Измените размер и стиль диаграммы.

3. Скопируйте данные на Лист 2.

4. Создайте диаграмму типа "гистограмма" для цен за третий и четвертый кварталы.

5. Измените подписи горизонтальной оси. Указание: В группе Подписи горизонтальной оси вкладки Работа с диаграммами/ Конструктор нажмите кнопкуИзменить, затем в окне Диапазон подписей осей укажите адреса ячеек с названиями продуктов.

4. Измените источник данных диаграммы: вместо цен 3-го квартала добавьте данные первого квартала. Указание: Для изменения диапазона данных в окне Выбор источника данныхвыделив название ряда 3-й квартал, нажмите кнопкуИзменить, в открывшемся диалоговом окне укажите новое имя ряда (в таблице щелкните мышью по ячейке с названием 1 кв-л) и в таблице выделите нужный диапазон данных.

5. Измените источник данных диаграммы: добавьте данные второго и третьего кварталов

6. Скопируйте данные на Лист 3.

7. Создайте круговую диаграмму для цен за первый квартал.

8. Добавьте на диаграмму название диаграммы «Цены за 1 квартал».

9. Измените источник данных диаграммы: вместо первого квартала второй квартал.

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

11. Перейдите к Листу 4..

12. Создайте график изменения цен продуктов по кварталам.

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

14. Удалите с диаграммы подписи данных, линии сетки, название горизонтальной оси.

15. Измените тип диаграммы на гистограмму.

16. Удалите данные 3-его квартала.

17. Переместите диаграмму на отдельный лист.

18. Отформатируйте элементы диаграммы, например оси, заголовки и другие подписи (вкладка Работа с диаграммами/Формат).

19. Сохраните файл именем Изменение_ цен.xlsx.

Задание 3. Анализ динамики продаж товаров.

1. Создайте диаграмму типа «график» для всей таблицы.

2. Добавьте на диаграмму название «Динамика продаж за неделю».

3. Переместите диаграмму на отдельный лист, названный Продажи.

4. Измените тип диаграммы на «гистограмму».

5. Отформатируйте элементы диаграммы.

6. Скопируйте исходные данные на новый лист.

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

8. Отформатируйте диаграммы.

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

Задание 4. Создание таблицы, выполнение вычислений и построение диаграмм.

1. Откройте новый файл. Присвойте листу 1 имя «Вычисления».

2. Создайте следующую таблицу «Реализация изделий и доход»:

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

4. Рассчитайте суммарную выручку и доход фирмы.

5. На основе проведенных расчетов создайте новую таблицу по приведенному ниже образцу и постройте объемную гистограмму (рис. 4.6)



Рисунок 4.6 Доходы фирмы «Юпитер»

6. Проанализируйте полученные результаты.

7. Сохраните работу в своей папке в виде файла диаграммы.хlsх.

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

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

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

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

Таблица

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

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

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

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

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

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

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

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

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

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

12. Исследуйте возможности редактирования созданной диаграммы (создайте заголовок диаграммы, поэкспериментируйте с тенью, объемом и окрашиванием элементов диаграммы).

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

Лабораторная работа 5 Текстовые функции, функции даты и времени

Цель работы:

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

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

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

Текстовые функции используют для преобразования и анализа текстовых значений. Текстовые функции преобразуют числовые текстовые значения в числ, числовые значения в строки символов (текстовые строки), а также позволяют выполнять над строками символов различные операции. Функция ТЕКСТ преобразует число в текстовую строку с заданным форматом. Синтаксис: =ТЕКСТ(значение;формат). Аргумент значение может быть любым числом, формулой или ссылкой на ячейку. Аргумент формат определяет, в каком виде отображается возвращаемая строка. Для задания необходимого формата можно использовать любой из символов форматирования за исключением звездочки. Использование формата Общийне допускается. Например, следующая формула возвращает текстовую строку 25,25: =ТЕКСТ(101/4;"0,00").

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

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

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

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

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

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

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

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

При работе с уже существующими данными довольно часто возникает ситуация, когда нужно модифицировать сами исходные значения, к которым применяются текстовые функции. Можно ввести функцию в те же самые ячейки, где находятся эти значения, поскольку введенные формулы заменят их. Но можно создать временные формулы с текстовой функцией в свободных ячейках в той же самой строке и скопируйте результат в буфер обмена. Чтобы заменить первоначальные значения модифицированными, выделите исходные ячейки с текстом, в меню "Правка" выберите команду "Специальная вставка", установите переключатель "Значения" и нажмите кнопку ОК. После этого можно удалить временные формулы. Функция СОВПАД сравнивает две строки текста на полную идентичность с учетом регистра букв. Различие в форматировании игнорируется. Синтаксис: =СОВПАД(текст1;текст2). Если аргументы текст1и текст2идентичны с учетом регистра букв, функция возвращает значение ИСТИНА, в противном случае - ЛОЖЬ. Аргументы текст1и текст2должны быть строками символов, заключенными в двойные кавычки, или ссылками на ячейки, в которых содержится текст.

В категории Текстовые есть функцияСЦЕПИТЬ, которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Синтаксис: =СЦЕПИТЬ(текст1;текст2;…). Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов: = СЦЕПИТЬ(А1; ??;B1; ??;C1) (рис. 6.1.).

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

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

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

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

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

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

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

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

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

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

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

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

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



Рисунок 5.3 Окно Мастера текстов

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

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

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

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

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

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

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

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

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

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

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

Рисунок 5.7 Задание формата Дата

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

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

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

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

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

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

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

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

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

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

Рисунок 5.10 Выбор способа отображения даты

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

Таблица

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

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

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

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

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

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

Рисунок 5.13 Два способа отображения дня недели

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

Узнать, каким днем неделя является текущее число, можно используя формулу: =ДЕНЬНЕД(СЕГОДНЯ()). Чтобы первого числа каждого месяца в ячейке выводился текст Начало месяца и ничего не выводилось в остальных случаях, можно составить формулу: =ЕСЛИ(ДЕНЬ(СЕГОДНЯ())=1; ?Начало месяца?;??).

Для построения более сложных условий можно использовать и другие логические функции. Например, если нужно выяснить, а не пятница ли сегодня и не тринадцатое ли число, можно создать формулу: =ЕСЛИ(И(ДЕНЬНЕД(СЕГОДНЯ())=6;ДЕНЬ(СЕГОДНЯ())=13); ?Осторожно, сегодня пятница 13-е!?;?Обычный день?).

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

Наиболее полезной из новых функций является функция КОНМЕСЯЦА(начальная_дата; число_месяцев). Положительное значение аргумента число_месяцевозначает будущую дату, отрицательное значение означает прошедшую дату. Функция возвращает последнюю дату месяца, который отстоит на заданное число месяцев от текущей даты.

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

Рисунок 5.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 г.

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 используйте функцию ОКРУГЛ и абсолютную ссылку.

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

Цель работы:

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

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

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

Финансовые функции используют в планово-экономических расчетах. Всего в категории "Финансовые" имеется 53 функции. Среди полного перечня финансовых функций выделяется группа функций, используемая для анализа инвестиций и расчета операций по кредитам, ссудам и займам.

Таблица 2

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

табличный excel процессор ячейка

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

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

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

Рассмотрим примеры решения следующих задач, связанных с расчетом операций по кредитам, ссудам и займам:

· определение наращенной стоимости (будущей стоимости);

· определение начального значения (текущей стоимости);

· определение срока платежа и процентной ставки;

· расчет периодических платежей, связанных с погашением займов.

Пример1. 1) Необходимо рассчитать будущую сумму вклада в размере 1000 руб., внесенного на 10 лет с ежегодным начислением 10% (рис. 6.1.), и будущую сумму вклада при тех же условиях, но с ежегодным внесением 1000 руб. (рис.6.2.).

2) Требуется рассчитать, какую сумму можно занять на 8 лет под 6% годовых, если есть возможность выплачивать ежемесячно по 200 руб.

Решение.

Рисунок 6.1 Расчет величины вклада с начальным взносом



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

Результат вычисления: в первом случае - 2593,74 руб., во втором - 18531,17руб.

Эту же функцию БС можно использовать и для расчета величины возможного займа.

Рисунок 6.3 Расчет величины возможного займа при помощи функции БС

Пример 2. Необходимо:

1) рассчитать величину вложения под 10 % годовых, которое будет ежегодно в течение 10 лет приносить доход 1000 руб.

2) рассчитать величину вложения под 10 % годовых, которое через 10 лет принесет доход 10000 руб.

Решение:1)

Рисунок 6.4 Расчет стоимости инвестиции

Результат вычисления получается отрицательным (-6 144,57 руб.), поскольку эту сумму необходимо заплатить. 2)

Рисунок 6.5 Расчет стоимости инвестиции.

Результат вычисления получается отрицательным (-3855,43 руб.), поскольку эту сумму необходимо заплатить.

Пример3. Необходимо рассчитать величину ежемесячного вложения под 6 % годовых, которое через 12 лет составит сумму вклада 50000 руб.

Решение.



Рисунок 6.6 Расчет процентных платежей

Результат вычисления получается отрицательным (-237,95 руб.), поскольку эту сумму необходимо выплачивать.

При тех же условиях, но с начальным вкладом 10000 руб.

Рисунок 6.7 Расчет процентных платежей

Результат вычисления получается отрицательным (-335,51 руб.), поскольку эту сумму необходимо выплачивать.

Эту же формулу (рис. 6.7.) можно использовать и при расчете платежей по займу. Например, необходимо рассчитать величину ежемесячной выплаты по займу в 50000 руб. под 6 % годовых на 12 лет. Результат будет тот же самый -237,95 руб.

Пример 4.Необходимо рассчитать количество ежемесячных платежей для погашения займа в 10000 руб., полученного под 10 % годовых, при условии ежемесячной выплаты 200 руб.

Решение.

Рисунок 6.14 Расчет количества платежей с использованием функции КПЕР. Результат вычисления - 42 ежемесячные выплаты.

Варианты заданий для контрольной работы

Таблица соответствия заданий и последней цифры шифра зачетки студента

Последняя цифра шифра зачетки	0	1	2	3	4	5	6	7	8	9
Номер варианта контрольной работы	0	1	2	3	4	5	6	7	8	9

Задания для контрольной работы по лабораторной работе № 3

Решить задачи путем построения электронной таблицы. Исходные данные для заполнения таблицы подобрать самостоятельно (не менее 10 строк).

Задание 0. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10000, то 2%; если выше 10000, то 1,5%. Введите в ячейку А10 текст «Объем сделки», в ячейку А11 - «Размер вознаграждения». В ячейку В10 введите объем сделки, а в В11 - формулу, вычисляющую размер вознаграждения.

Задание 1. В доме проживает 10 жильцов. Подсчитать, сколько каждый из них должен платить за электроэнергию и определить суммарную плату для всех жильцов. Известно, что 1 кВт/ч электроэнергии стоит m рублей, а некоторые жильцы имеют 50% скидку при оплате.

Задание 2. Торговый склад производит уценку хранящейся на складе продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то - 1,5 раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

Задание 3. В сельскохозяйственном кооперативе на сборе урожая помидоров работают 10 сезонных рабочих. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет k килограммов. Сбор 1 кг помидоров стоит m рублей. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?

Задание 4. Автоматизировать начисление стипендии студентам (группа - 10 человек) по итогам летней сессии. Количество экзаменов - 5, баллы - от 2,5 до 5. Стипендия начисляется в размере МРОТ (600 руб.), если все экзамены сданы с оценкой не ниже 3. При сдаче всех экзаменов с оценками не ниже 4 баллов выплачивается надбавка 20%, не ниже 5 баллов - надбавка 50%.

Задание 5. Имеется заказы на хозяйственные товарысостоящая из товара, количества, цены, стоимости. Необходимо выбрать заказы отдельно на «ведро» и посчитать его стоимость и отдельно на «столы» и тоже указывать их стоимость, остальные товары 0.

Задание 6. Предположим, нам надо сделать отбор наших дебиторов, которые задолжали нам за 10месяцев, если сумма больше 100 000 рублей, в этом случае формула должна выводить критерий «злостный задолжник, а если сумма от 50000 до 100000, выводим «срочно погасить», а если меньше 50000, то «ожидание».

Задание 7.Рассчитатьскидки в зависимости от нескольких уровней объема продаж. При имеющейся матрице скидок:До 100 000 рублей - 0%

От 100 001 до 300 000 рублей - 3%

От 300 001 до 500 000 рублей - 5%

Свыше 500 001 - 7%.

Задание 8.Пусть у ряда работников имеется задолженность по потребительскому кредиту. Таблица состоит (10 строк) из следующих столбцов: Ф.И.О; Начислено з/пл (от 12 до 40 тыс. руб.); Задолженность по кредиту; Удержано. Нужно найти в списке таких работников и удержать с них в счет погашения кредита 10% от начисленной им суммы, используя функцию «Если».

Задание 9. Составьте логическую функцию для решения следующей задачи. Конкретные данные для решения задачи подобрать самостоятельно (не менее 10 строк). Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10 месяцев, то -- в 1,5 раза. Рассчитать ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.

Задания для контрольной работы по лабораторной работе № 6

Вариант I

Задание 0. На банковский счет под 11,5% годовых внесли 37000 руб. Определить размер вклада по истечении трех лет, если проценты начисляются каждые полгода.

Задание 1. Определить сколько денег окажется на банковском счете, если ежегодно в течение пяти лет под 17% годовых вносится 20000 руб. Взносы осуществляются в начале каждого года.

Задание 2. Достаточно ли положить на счет 85000 руб. для приобретения через пять лет легкового автомобиля стоимостью 160 000 руб.? Банк начисляет проценты ежеквартально, годовая ставка 12%. Определить необходимую сумму (с помощью «подбора параметра»).

Задание 3. По облигации номиналом 50000 руб., выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год - 10%, в следующие два года - 20%, в оставшиеся три года - 25%. Определить будущую стоимость облигации с учетом переменной процентной ставки.

Задание 4.По облигации, выпущенной на шесть лет, предусмотрен порядок начисления процентов, приведенный в задаче 4. Рассчитать номинал облигации, если известно, что ее будущая стоимость составила 216562,50 руб.

Задание 5. Фирме требуется 500 000 руб. через три года. Определить, какую сумму необходимо внести фирме сейчас, чтобы к концу третьего года вклад увеличился до 500 000 руб., если процентная ставка составляет 12% годовых.

Задание 6. Клиент заключает с банком договор о выплате ему в течение пяти лет ежегодной ренты в размере 5000 руб. в конце каждого года. Какую сумму необходимо внести клиенту в начале первого года, чтобы обеспечить эту ренту, исходя из годовой процентной ставки 20%?

Задание 7. Определите чистую текущую стоимость по проекту на 05.04.2014 при ставке дисконтирования 8%, если затраты по нему на 05.08.2014 со-ставят 90 млн. руб., а ожидаемые доходы в течение следующих месяцев будут: 10 млн. руб. на 10.01.2015; 10 млн. руб. на 10.01.2015; 20 млн. руб. на 01.03.2015; 30 млн. руб. на 15.04.2015; 40 млн. руб. на 25.07.2006.

Задание 8. Рассчитать через сколько лет вклад размером 100 000 руб. достигнет 1 000 000 руб., если годовая процентная ставка по вкладу 13,5% и начисление процентов производится ежеквартально.

Задание 9. Для покрытия будущих расходов фирма создает фонд. Средства в фонд поступают в виде годовой ренты в конце года. Сумма разового пла-тежа 16 000 руб. На поступившие взносы начисляются 11,2% годовых. Необходимо определить, когда величина фонда будет равна 100 000 руб.

Задание 10. Предположим, что для получения через два года суммы в 1 000 000 руб. предприятие готово вложить 250 000 руб. сразу и затем каждый месяц по 25 000 руб. Определить годовую процентную ставку.

Вариант II

Задание 0. Выдан кредит 500 000 руб. на 2,5 года. Проценты начисляются раз в полгода. Определить величину процентной ставки за период, если известно, что возврат составит 700 000 руб.

Задание 1. Клиент банка осуществляет заем в размере 5000 руб. под 6% годовых на 6 месяцев. Определите ежемесячные платежи клиента. Платежи осуществляются в конце месяца.

Задание 2. Клиенту банка необходимо накопить 200 000 руб. за два года. Клиент обязуется вносить в начале каждого месяца постоянную сумму под 9% годовых. Какой должна быть эта сумма?

Задание 3. Определите платежи по процентам за первый месяц от трехгодичного займа в 100 000 руб. из расчета 10% годовых.

Задание 4. Клиент ежегодно в течение пяти лет вносил деньги на свой счет в банке и накопил 40000 руб. Определите, какой доход получил клиент банка за последний год, если годовая ставка составила 13,5%.

Задание 5. Определите значение основного платежа для первого месяца двухгодичного займа в 60000 руб. под 12% годовых.

Задание 6. Организация взяла ссуду в банке в размере 500 000 руб. на 10 лет под 10,5% годовых, проценты начисляются ежемесячно. Определите сумму выплат по процентам за первый месяц и за третий год периода.

Задание 7. Ссуда размером 1 000 000 руб. выдана под 13% годовых сроком на три года; проценты начисляются ежеквартально. Определите величину общих выплат по займу за второй год.

Задание 8. Рассчитать сумму процентов, начисленных на вклад в 750 000 тыс. руб. за два года, если банк начисляет проценты ежеквартально из расчета 28% годовых. Какова должна быть годовая депозитная ставка, если за два года необходимо удвоить первоначальный вклад.

Задание 9. Потребитель получает заем на покупку автомобиля 20 000$ под 8% годовых сроком на три года при ежемесячных выплатах. Какова будет сумма по процентам и основной платеж за первый и последний месяцы выплат?

Размещено на Allbest.ru