Главная | Обратная связь | Поможем написать вашу работу!

График ожидаемых денежных поступлений

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

К выполнению лабораторной работы №5

«Создание электронных таблиц в Microsoft Office Excel 2007»

по дисциплине «Информатика»

для студентов дневной формы обучения

гуманитарных направлений подготовки

Севастополь

СОДЕРЖАНИЕ

1. Интерфейс Microsoft Excel 3

1.1. Книги и листы.. 3

1.1.1. Открытие новой пустой книги. 3

1.1.2. Создание новой книги на основе существующей. 3

1.1.3. Создание новой книги на основе шаблона. 4

1.1.4. Ввод данных в ячейкилиста. 5

1.1.5. Ввод формул в ячейки листа. 10

1.1.6. Использование функций Microsoft Excel 10

2. Контрольные вопросы.. 11

3. Постановка задач. 11

3.1. Задание 1. 11

3.2. Задание 2. 12

3.3. Задание 3. 12

3.4. Задание 4. 13

4. Форма отчетности. 15

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

1. Интерфейс Microsoft Excel

1.1. Книги и листы

Книга Microsoft Office Excel представляет собой файл, содержащий один или несколько листов, которые можно использовать для организации разнообразной взаимосвязанной информации. Лист - основной документ, используемый в Microsoft Excel для хранения данных и работы с ними. Он также называется электронной таблицей. Лист состоит из ячеек, упорядоченных в строки и столбцы. Листы всегда хранятся в книге. Чтобы создать новую книгу, можно открыть пустую. Создание новой книги возможно также на основе уже существующей книги или на основе шаблона, используемого по умолчанию.

1.1.1. Открытие новой пустой книги

Нажмите кнопку Microsoft Office, а затем выберите команду Создать.

Удостоверьтесь, что в списке Шаблоны установлен флажок Пустые и последние, и щелкните в правой области списка Пустые и последние пункт Новая книга.

1.1.2. Создание новой книги на основе существующей

Нажмите кнопку Microsoft Office, а затем выберите команду Создать.

В списке Шаблоны щелкните пункт Из существующего документа.

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

Выберите книгу и нажмите кнопку Создать.

1.1.3. Создание новой книги на основе шаблона

Нажмите кнопку Microsoft Office, а затем выберите команду Создать.

В списке Шаблоны щелкните пункт Установленные шаблон ы или Мои шаблоны.

Выполните одно из следующих действий.

Чтобы использовать установленный шаблон, в списке Установленные шаблоны щелкните нужный шаблон, а затем выберите команду Создать.

Чтобы использовать собственный шаблон, на вкладке Мои шаблоны дважды щелкните нужный шаблон.

На вкладке Мои шаблоны перечислены созданные пользователем шаблоны. Если нужный шаблон не отображается, проверьте, находится ли он в соответствующей папке. Пользовательские шаблоны обычно хранятся в папке Шаблоны, путь: C:Documents andSettingsимя_пользователяApplicationDataMicrosoftШаблоны в Microsoft Windows XP и C:Usersимя_пользователяApplication DataMicrosoftШаблоны в Microsoft Windows Vista.

Электронные таблицы, к классу которых относится программа Microsoft Excel, предназначены для выполнения любых периодических вычислений.

Запустите это приложение.

Его интерфейс чем-то похож на интерфейс Microsoft Word, однако вместо чистого листа (рабочего поля) мы видим таблицу, с которой пользователь и должен работать. Файл Microsoft Excel организован как книга, состоящая из таких таблиц (листов). Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи.

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

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

1.1.4. Ввод данных в ячейкилиста

Ввод чисел или текста

Выберите ячейку на листе.

Введите нужные числа или текст и нажмите клавишу ВВОД или TAB.

Чтобы начать ввод данных с новой строки в ячейке, задайте разрыв строки, нажав клавиши ALT+ВВОД.

По умолчанию нажатие клавиши ВВОД перемещает курсор на одну ячейку вниз, а нажатие клавиши TAB — на одну ячейку вправо. Нельзя изменить направление перемещения для клавиши TAB, но можно указать другое направление для клавиши ВВОД:

Щелкните значок Кнопка Microsoft Office , а затем щелкните Параметры Excel, в группе Дополнительно установите флажок Перемещать выделение после ввода и выберите нужное направление в списке Направление.

Если использовать клавишу TAB для ввода данных в нескольких ячейках строки, а затем нажать клавишу ВВОД в конце этой строки, курсор переместится к началу следующей строки.

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

Если необходимо изменить ширину столбца, выберите ячейку, для которой необходимо изменить ширину столбца, на вкладке Главная в группе Ячейки выберите команду Формат (Рисунок 1).

Рисунок 1. Группа Ячейки

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

- чтобы весь текст поместился в ячейке, выберите команду Автоподбор ширины столбца;

- чтобы увеличить ширину столбца, выберите команду Ширина колонки и введите нужное значение в поле Ширина колонки;

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

Перенос текста в ячейке

Выберите ячейку, в которой необходимо перенести текст.

На вкладке Главная в группе Выравнивание выберите команду Переносить по словам (Рисунок 2).

Рисунок 2. Группа Выравнивание

Если текст состоит из одного слова, оно не переносится. Чтобы текст уместился полностью, можно увеличить ширину столбца или уменьшить размер шрифта. Если после переноса виден не весь текст, возможно, потребуется изменить высоту строки. На вкладке Главная в группе Ячейки (Рисунок 1) выберите Формат, затем в группе Размер ячейки щелкните Автоподбор высоты.

В Microsoft Office Excel представление числа в ячейке отличается от сохраненного значения. В большинстве случаев, хотя число отображается округленным, оно округлено только на экране. В вычислениях используется точное значение, которое не отображается.

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

Изменение формата числа

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

На вкладке Главная в группе Число (Рисунок 3) наведите указатель на пункт Общий, а затем выберите в списке нужный формат.

Рисунок 3. Группа Число

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

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

Форматирование чисел в текстовом формате

Выберите пустую ячейку, на вкладке Главная в группе Число наведите указатель на пункт Общие, а затем выберите в списке пункт Текстовый. Введите нужные числа в отформатированной ячейке. Числа, которые были введены перед применением к ячейкам текстового формата, необходимо ввести заново в отформатированные ячейки. Чтобы ускорить повторный ввод чисел в текстовом формате, нажмите клавишу F2, а затем клавишу ВВОД.

Ввод одинаковых данных в несколько ячеек одновременно

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

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

Выбор ячеек диапазонов, строк или столбцов (Таблица 1)

Таблица 1

Чтобы выделить	Выполните следующее
Одну ячейку	Щелкните ячейку или перейдите в нее с помощью клавиш со стрелками.
Диапазон ячеек	Выберите первую ячейку диапазона, а затем, удерживая кнопку мыши, перетащите указатель до последней ячейки диапазона, либо выберите первую ячейку и, удерживая клавишу SHIFT, щелкните последнюю ячейку. Также можно, выбрав первую ячейку диапазона, нажать F8, чтобы увеличить область выделения с помощью клавиш со стрелками. Чтобы остановить выделение, повторно нажмите клавишу F8.
Большой диапазон ячеек	Щелкните первую ячейку диапазона, а затем, удерживая клавишу SHIFT, щелкните последнюю ячейку диапазона. Чтобы добраться до этой ячейки, воспользуйтесь прокруткой.

Все ячейки листа	Нажмите кнопкуВыделить все. Чтобы выделить все ячейки листа, можно также нажать сочетание клавиш CTRL+A. Примечание. Если лист содержит данные, CTRL+A выделяет текущую область. Повторное нажатие сочетания клавиш CTRL+A позволяет выделить все ячейки листа.
Несмежные ячейки или диапазоны ячеек	Выделите первую ячейку или диапазон ячеек и, удерживая клавишу CTRL, выделите остальные ячейки или диапазоны. Также можно, выбрав первую ячейку или диапазон ячеек, нажать сочетание клавиш SHIFT+F8, чтобы добавить другую несмежную ячейку или диапазон ячеек к области выделения. Чтобы остановить выделение, повторно нажмите сочетание клавиш SHIFT+F8. Примечание. Невозможно исключить отдельные ячейки или диапазоны ячеек из области выделения несмежных ячеек; можно лишь отменить все выделение целиком.
Всю строку или весь столбец	Щелкните заголовок строки или столбца. Заголовок строки Заголовок столбца Строку или столбец можно выделить, выбрав первую ячейку строки или столбца и нажав затем сочетание клавиш CTRL+SHIFT+КЛАВИША СО СТРЕЛКОЙ (СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО позволяет выделить всю строку, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ позволяет выделить весь столбец). Примечание. Если строка или столбец содержат данные, CTRL+SHIFT+КЛАВИША СО СТРЕЛКОЙ выделяет текущую область данных столбца или строки. Повторное нажатие сочетания клавиш CTRL+SHIFT+КЛАВИША СО СТРЕЛКОЙ позволяет выделить все ячейки столбца или строки.
Смежные строки или столбцы	Протащите указатель мыши по заголовкам строк или столбцов. Также можно выделить первую строку или первый столбец, а затем, удерживая нажатой клавишу SHIFT, выделить последнюю строку или последний столбец.
Несмежные строки или столбцы	Щелкните заголовок первой строки или первого столбца в области выделения; затем нажмите клавишу CTRL и, удерживая ее, последовательно выберите заголовки других столбцов или строк, которые необходимо добавить к области выделения.
Первую или последнюю ячейку в строке или столбце	Щелкните ячейку в строке или столбце, а затем нажмите сочетание клавиш CTRL+КЛАВИША СО СТРЕЛКОЙ (СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО позволяет перейти к последней или к первой ячейке строки, соответственно, а СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ позволяет перейти к первой или к последней ячейке столбца).

Первую или последнюю ячейку листа или всей электронной таблицы Microsoft Office Excel	Чтобы выделить первую ячейку листа или всей электронной таблицы Excel, которая содержит данные или форматирование, нажмите сочетание клавиш CTRL+HOME. Чтобы выделить последнюю ячейку листа или всей электронной таблицы Excel, которая содержит данные или форматирование, нажмите сочетание клавиш CTRL+END.
Все ячейки до последней использованной ячейки листа (в нижнем правом углу)	Щелкните первую ячейку, а затем нажмите сочетание клавиш CTRL+SHIFT+END, чтобы расширить область выделения до последней использованной ячейки листа (в нижнем правом углу).
Все ячейки до первой ячейки листа	Щелкните первую ячейку, а затем нажмите сочетание клавиш CTRL+SHIFT+HOME, чтобы расширить область выделения до первой ячейки листа.
Большее или меньшее количество ячеек по сравнению с текущим выделенным диапазоном	Удерживая нажатой клавишу SHIFT, щелкните последнюю ячейку, которую необходимо включить в новый диапазон. Прямоугольная область между активной ячейкой (Активная ячейка. Выделенная ячейка, в которую будет помещаться текст при вводе с клавиатуры. В каждый момент времени активной может быть только одна ячейка. Активная ячейка выделяется жирной границей.) и указанной ячейкой образует новый диапазон.

Чтобы сбросить выделение, щелкните любую ячейку листа.

Просмотр нескольких листов одновременно

(1) На вкладке Вид в группе Окно (Рисунок 4) щелкните Новое окно.

Рисунок 4. Группа Окно

(2) Перейдите к новому окну и щелкните ярлычок листа, который нужно просмотреть.

Повторите шаги (1) и (2) для каждого листа, который требуется просмотреть.

На вкладке Вид в группе Окно щелкните Упорядочить все.

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

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

1.1.5. Ввод формул в ячейки листа

Для работы с данными, содержащимися в ячейках, используются формулы и функции. Ввод любой формулы начинается со знака «=». Ячейки в электронной таблице обозначаются буквой (столбец) и цифрой (номер строки). Допустим, например, что у нас имеются два числа в ячейках А1 и В1, и нужно ввести формулу, складывающую их, в ячейку С1. Для этого нужно встать курсором на ячейку С1 и ввести знак «=». После этого, в принципе, можно было бы перейти в латинский шрифт и набить выражение: «А1+В1». После нажатия на Enter в ячейке с формулой появилось бы значение суммы. Однако в Excel существует более простой механизм ввода формул. После того, как набран знак «=», для ввода в формулу имени какой-либо ячейки нужно щелкнуть мышью по соответствующей ячейке. Формулы рекомендуется набирать только таким способом.

1.1.6. Использование функций Microsoft Excel

Для выполнения вычислений на листах книги и на листах макросов можно использовать стандартные функции Microsoft Excel. Величины, которые используются для вычисления значений функций, называются аргументами. Величины, которые являются результатом вычисления функций, называются возвращаемыми значениями. Последовательность, в которой должны располагаться аргументы функции, называется синтаксисом функции. Чтобы использовать функцию, ее нужно ввести как часть формулы в ячейку листа. Формула должна начинаться со знака равенства (=), за которым следует набор величин, операторов и функций. Если функция стоит в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

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

Например, рассмотрим синтаксис и порядок ввода функции сложения диапазона ячеек с G2 по G18. Для достижения нужного результата в любой ячейке (обычно используется ячейка непосредственно под складываемым столбцом, например, G18) введите строчку «=Сумм(G2:G18)». Порядок ввода следующий: вводим «=», затем вручную набираем «Сумм» и открываем скобку, щелкаем мышкой по ячейке G2, ставим двоеточие, щелкаем мышкой по ячейке G18, закрываем скобку, нажимаем «Enter». Именно функцию СУММ можно получить еще быстрее, но второй способ мы рассмотрим позже.

2. Контрольныевопросы

(1) Что представляет собой книга Microsoft Office Excel?

(2) Какие способы создания книги Microsoft Office Excel Вам известны?

(3) Какие возможности форматирования данных в ячейке предоставляет Microsoft Office Excel?

(4) Каким образом создаются формулы в Microsoft Office Excel?

(5) Каким образом создается функция СУММ() Microsoft Office Excel?

3. Постановка задач

3.1. Задание 1

Для получения начальных навыков ввода информации и формул в ячейки электронной таблицы поставьте два произвольных числа в ячейки А1 и В1, после чего введите четыре формулы для простейших арифметических действий («+», «-», «*» и «/») с этими числами. Расположить формулы можно в ячейках с С1 по С4.

3.2. Задание 2

Создайте электронную таблицу для расчета месячной заработной платы на предприятии с 10-ю работниками. Структура таблицы приведена ниже:

№ колонки	Название	Содержание
	№	Порядковые номера (1 - 10)
	ФИО	Вводится вручную
	Начислено	Вводится вручную
	Подоходный налог	Формула: =«начислено»*10%
	Пенсионный фонд	Формула: =«начислено»*1%
	Фонд занятости	Формула: =«начислено»*0,5%
	Всего на руки	Формула, в которой от «начислено» вычитаются все три отчисления.

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

3.3. Задание 3

Создайте на разных листах две электронные таблицы для расчета бюджета продаж и графика ожидаемых денежных поступлений компании NORTON. Организуйте границы таблиц, а также границы ячеек с названиями колонок жирными линиями и линии сетки - между рабочими ячейками таблицы.

Таблица 1. Компания NORTON. Бюджет продаж

	Квартал	Всего

Ожидаемый объем продаж, штуки
Цена реализации^*
Выручка

Примечания. ^*) Цена реализации равна 80 руб. и не изменяется в течение года.

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

График ожидаемых денежных поступлений

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

Таблица 2. Компания NORTON. График ожидаемых денежных поступлений

	Квартал	Всего

Счета к получению на 31.12.2011 г¹⁾.
Продажи первого квартала²⁾
Продажи второго квартала³⁾
Продажи третьего квартала
Продажи четвертого квартала
Всего денежных поступлений

Примечания.

1) Предполагается, что весь имеющийся согласно балансовому отчету объём дебиторской задолженности (17920 руб.) погашается в первом квартале;

2) 70% от объёма реализации оплачивается в квартале продажи;

3) 28% от объёма реализации оплачивается в квартале, следующем за кварталом продажи, оставшиеся 2% - списываются как безнадежные долги.

3.4. Задание 4

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

Таблица 3. Компания NORTON. Бюджет прямых материальных затрат

	Квартал	Всего

Планируемый объем производства
Потребность в основных материалах (первом/втором) для одного изделия, м¹	3/5
Потребность в первом материале¹
Планируемый запас первого материала на конец периода^1,2
Всего требуется¹первогоматериала
Вычесть запас на начало периода¹
Первогоматериала к закупке¹
Цена за единицу, руб.¹	2/5
Стоимость закупки по первому материалу¹

Примечания. ¹ Необходимо делать расчет для каждого вида основных материалов в отдельной таблице;

² Принять равным (5+а)% от потребности следующего квартала. Для 4-го квартала взять по 3-му кварталу, где а – параметр, который принять равным Вашему номеру в журнале группы.

Таблица 4. Компания NORTON. График ожидаемых денежных платежей

	Квартал	Всего

Счета к оплате на 31.12.2011г.³
Закупки первого квартала⁴
Закупки второго квартала
Закупки третьего квартала
Закупки четвертого квартала
Всего денежных платежей

Примечания. ³ Предполагается, что весь имеющийся согласно балансовому отчету объём кредиторской задолженности (2200 руб.) погашается в первом квартале;

⁴ 60% стоимости квартальных закупок оплачивается в квартале закупки, остаток – в следующем квартале.

4. Форма отчетности

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

(1) Отчет по установленной форме (с распечаткойсозданных в соответствии с заданиями 2, 3 и 4 таблиц).

(2) Созданные таблицы в электронном виде.

(3) Ответы на контрольные вопросы.

Воспользуйтесь поиском по сайту: