Изучаемые технические приемы

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

«Основы работы с электронными таблицами Microsoft Excel»

Цель работы: изучить принципы работы с электронными таблицами на примере работы с Microsoft Excel.

 

В настоящем учебном пособии изложены основные приемы работы с табличным процессором Microsoft Excel for Windows.

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

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

Авторы надеются, что учебное пособие будет одинаково полезно как пользователям, только приступающим к работе с Microsoft Excel, так и тем, чье первое знакомство c программой уже состоялось.

В тексте использованы следующие условные обозначения:

1. Основные термины выделены курсивом.

2. Команды меню, названия диалоговых полей, кнопок и прочих элементов диалога выделены полужирным шрифтом.

3. Команды, выполняемые последовательно, разделяются дефисом. Так, строка Файл — Сохранить предполагает следующую последовательность операций: открыть пункт меню Файл, затем выбрать команду Сохранить

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

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

Сведения об основных фондах предприятия в начале и в конце года представлены в столбцах 1—4 таблицы.1 «Анализ объема и состава основных средств предприятия» На основании имеющихся данных требуется заполнить таблицу недостающими расчетными данными и подготовить ее к печати.

Таблица 1.

Изучаемые технические приемы

· запуск Microsoft Excel;

· операции с листами активной рабочей книги;

· ввод данных в ячейки электронной таблицы;

· выделение диапазона ячеек;

· форматирование ячеек и диапазонов ячеек;

· изменение ширины столбца и высоты строки

· форматирование ячеек по образцу (копирование формата);

· ввод формул в ячейки, абсолютная и относительная адресация;

· присвоение ячейкам имен;

· автозаполнение (копирование формул);

· обрамление ячеек;

· сохранение рабочей книги на диске;

· подготовка к печати и печать документа.

 

Порядок выполнения работы.

Ввод исходных данных

 1. Прежде чем вводить данные в ячейки столбца B,присвоим ему текстовый формат. Это связано с тем, что коды строк представляют собой числа, начинающиеся с нулей. Если формат этих ячеек останется общим (по умолчанию Excel устанавливает именно такой формат), эти нули будут отброшены.

1.1. При помощи мыши выделите интервал ячеек B 5: B 10 (эту операцию мы неоднократно выполняли ранее).

1.2. Поместите указатель мыши на черный фон и щелкните правой кнопкой мыши. В открывшемся контекстном меню выберите команду Формат ячеек… и перейдите на вкладку Число. В поле Ч исловые форматы: щелчком мыши выберите Текстовый. (рис.24)

Рис.24.

1.3. Щелчком мыши перейдите на вкладку Выравнивание и щелчками мышью установите горизонтальное выравнивание по ц е нтру,  вертикальное по ц е нтру, также флажок переносит ь по словам и щелчком по командной кнопке ОК закройте окно. Снимите выделение, щелкнув по любой ячейке.

1.4. В ячейку B 5 введите:010

1.5. В ячейку B 6 введите:011

1.6. В ячейку B 7 введите:012

1.7. В ячейку B 8 введите:020

1.8. В ячейку B 9 введите:030

1.9. В ячейку B 10 введите:040

2. Заполните таблицу числовыми значениями согласно табл 2. Числа вводите без пробелов между цифрами!

 

Таблица 2

Ячейка…	Значение…
C5	8835018
C6	8270111
C7	7217195
C8	514005
C9	2285943
D5	9111297
D6	8713278
D7	7258065
D8	549203
D9	2375888

3. Выделите интервал ячеек C 5: D 10, вызовите контекстное меню, выберите команду Формат ячеек… и перейдите на вкладку Число и выберите следующие установки (рис.25).

Рис.25.

4. Щелчком мыши перейдите на вкладку Выравнивание и установите горизонтальное выравнивание по ц е нтру,  вертикальное по ц е нтру, затем щелчком по командной кнопке ОК закройте окно. Снимите выделение, щелкнув по любой ячейке.

Вид таблицы представлен на рис. 26.

Рис.26.

3.8.Вычисление расчетных значений: вставка и
редактирование формул

1. Рассчитаем сумму основных фондов на начало года:

1.1 Щелчком мыши перейдите в ячейку C 10.

1.2. Перейдите на английскую раскладку клавиатуры (левый Alt+ Shift).

1.3. Введите с клавиатуры следующую формулу: = C 5+ C 8+ C 9 Редактируемая формула отображается в строке формул (рис.27). Ввод формулы всегда завершается нажатием. В результате в ячейку C10 будет занесено значение, равное сумме значений ячеек C5, C8 и C9.

Другой способ ввода формулы: введите с клавиатуры =, щелкните по ячейке C5, далее введите с клавиатуры +, щелкните по ячейке C8, введите с клавиатуры +, щелкните по ячейке C9 и нажмитеклавишу Enter.

Следует помнить, что в ячейке отображается не формула, а результат вычислений по этой формуле. Формула текущей ячейки отображается в строке формул!

 

Рис. 27.

2. Рассчитаем сумму основных фондов на конец года. Для этого в ячейку D10 необходимо ввести формулу =D5+D8+D9. Сравним эту формулу с ранее введенной в ячейку C10. Сравнение показывает, что обозначения строк в этих двух формулах одинаковы, различны лишь обозначения столбцов. Этот факт позволяет применить операцию автозаполнения ячеек формулами (автоматического копирования формул). Для этого щелкните мышью по ячейке C10, подведите указатель мыши к маркеру автозаполнения и при нажатой левой кнопке мыши «протащите» его до ячейки D10, после чего кнопку отпустите (рис. 28).

Рис. 28.

3. Рассчитаем абсолютные изменения за год для всех строк (столбец E). Для этого необходимо ввести следующие формулы согласно табл. 3

Таблица 3

Ячейка…	ФОРМУЛА…
E5	=D5-C5
E6	=D6-C6
E7	=D7-C7
E8	=D8-C8
E9	=D9-C9
E10	=D10-C10

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

3.1. Введите в ячейку E 5 формулу = D 5- C 5

3.2. Щелкните мышью по ячейке, подведите указатель мыши к маркеру автозаполнения и при нажатой левой кнопке мыши "протащите" его до ячейки Е10, после чего кнопку отпустите (рис. 29).

Рис.29.

3.3. Не снимая выделения, выберите команду формат ячеек… из контексного меню интервала ячеек E5:E10 и установите финансовый формат на вкладке Число, а затем на вкладке Выравнивание вертикальное и горизонтальное выравнивание по центру, после чего снимите выделение. Вид таблицы показан на рис.30.

Рис.30.

4. Рассчитайте относительные изменения за год (столбец F). Для этого:

4.1. Используя автоматическое копирование формул, заполните ячейки формулами согласно табл.4:

Таблица 4

Ячейка…	ФОРМУЛА…
F5	=D5/C5
F6	=D6/C6
F7	=D7/C7
F8	=D8/C8
F9	=D9/C9
F10	=D10/C10

4.2. Не снимая выделения, выберите команду Формат ячеек… из контексного меню интервала ячеек F 5:F10 и установите процентный формат с двумя десятичными знаками после запятой на вкладке Число (рис.31), а затем на вкладке Выравнивание — вертикальное и горизонтальное выравнивание по центру, после чего снимите выделение.

Рис.31.

5. Рассчитаем структуру основных фондов на начало года.

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

Таблица 5.

Ячейка…	ФОРМУЛА…
G5	=C5/C10
G6	=C6/C10
G7	=C7/C10
G8	=C8/C10
G9	=C9/C10
G10	=C10/C10

Анализ этой таблицы показывает, что использовать автоматическое копирование формул в том виде, как это делалось ранее, нельзя, так как адрес ячейки С10 должен оставаться неизменным!

 Однако эту неприятность можно легко обойти двумя способами:

¾ используя абсолютную адресациюячеек

¾ используя имена ячеек.

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

5.1. В ячейку G 5 введите формулу = C 5/$ C $10 Знак доллара, помещенный перед обозначениями столбца и строки, делает адрес ячейки абсолютным.

  Абсолютные адреса, в отличие от относительных (например C5), не меняются при копировании формул! По умолчанию Excel использует относительную адресацию.

5.2. При помощи операции автоматическое копирование формул, подробно описанной ранее, заполните формулами остальные ячейки этого столбца, после чего, не снимая выделения, присвойте интервалу ячеек G 5: G 10 процентный формат с двумя десятичными знаками после запятой (вкладка Число), вертикальное и горизонтальное выравнивание по центру (вкладка Выравнивание), после чего снимите выделение. На экране Вы увидите примерно следующее:

Рис. 32.

6. Рассчитаем структуру основных фондов на конец года. Для этого необходимо ввести в ячейки следующие формулы:

Таблица 6

Ячейка…	ФОРМУЛА…
H5	=D5/D10
H6	=D6/D10
H7	=D7/D10
H8	=D8/D10
H9	=D9/D10
H10	=D10/D10

Чтобы не вводить все эти формулы по отдельности, присвоим ячейке D 10 имя Всего, а затемвоспользуемся автоматическим копированием формул.  Для этого выполните следующие операции:

6.1. Выделите ячейку D 10 и выполните команду меню Вст а вка— И мя— П рисвоить…

В открывшемся диалоговом окне Присвоение имени (рис. 33) щелкните мышью в поле И мя (там появится мигающий курсор), введите с клавиатуры Всего и щелкните кнопку ОК.

Рис. 33.

Обратите внимание, что имя ячейки отображается в левой части строки формул.

6.2. В ячейку H5 введите формулу =D5/Всего, причем имя Всего набирать на клавиатуре не обязательно, достаточно в процессе ввода формулы щелкнуть мышью по ячейке D 10.

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

Таблица 7

Ячейка…	ФОРМУЛА…
H5	=D5/ Всего
H6	=D6/Всего
H7	=D7/Всего
H8	=D8/Всего
H9	=D9/Всего
H10	=D10/Всего

6.4. Используя рассмотренную ранее операцию форматирования по образцу, присвойте интервалу ячеек H5:H10  процентный формат. В качестве образца выберите любую ячейку диапазона G5:G10. Окончательный вид таблицы представлен на рис. 34.

Рис.34.

3.9. Отслеживание взаимосвязи (трассировка) ячеек.
Проверка формул

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

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

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

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

Выполним трассировку ячейки C 10.

1. Выделите ячейку C 10.

2. С ервис—З а висимости— П анель зависимостей. (см. рис. 35.)

Рис. 35.

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

Рис. 36.

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

Рис.37.

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

Подготовка к печати и печать документа

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

Подготовим к печати созданную нами таблицу.

1. Выполните команду Ф айл— П ечать… На экране появится диалоговое окно Печать.

2. Щелкните кнопку П росмотр, в результате чего на экране появится Ваш документ в режиме предварительного просмотра (рис. 40.) В этом режиме документ отображается на экране в точности так, как будет напечатан.

Рис.40.

Внешний вид документа нас явно не устраивает. Для его исправления щелкните мышью по кнопке Страни ц а…, в результате на экране появится окно Параметры страницы. Установите параметры этого диалога, как показано на рис.41 и щелкните ОК.

Рис.41.

Окончательный вид подготовленного к печати документа представлен нарис.42.

Рис.42.

Щелчок по кнопке П ечать… начинает печать документа (если, конечно же, к вашему компьютеру подключен принтер).

Задания для самостоятельной работы

Задание 1. Создайте таблицу, представленную на рис. 43. Заполните ячейки недостающими расчетными значениями (методика расчета указана курсивом в заголовке каждой строки). Сохраните рабочую книгу в указанном преподавателем файле.

Рис. 43.

Задание 2. Создайте таблицу, представленную на рис. 44. Заполните ячейки недостающими расчетными значениями. Сохраните рабочую книгу в указанном преподавателем файле.

Рис. 44.

Методика расчета:

1) Для всех столбцов: Сумма (руб.) = Цена (руб)*Количество (шт.)

2) Остаток на 1.04.99=Остаток на 1.03.99+Поступление–Возврат брака–
 –Реализация.

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

1. Экономическая информатика и вычислительная техника. Учебник для студентов экономических специальностей вузов/под ред. В.П.Косарева, А.Ю. Королева - М., Финансы и статистика, 1996 г.

2. А.В. Потапкин. Операционная система WINDOWS 95: руководство к действию. М., ЭКОМ", 1996.

3. А.Э. Просвиров, Е.Г. Шведов. Под общей редакцией д.э.н., профессора Копылова В. И. Операционная система Windows 95. Учебное пособие. Часть 1. Волгоград-1997.

4.Комягин В.Б., Коцюбинский А.О. Excel 7 в примерах: Практ. пособ.— М.: Нолидж, 1996—432 с.: ил.

5. Шеремет А.Д., Сайфулин Р.С. Методика финансового анализа.— М.:ИНФРА-М, 1996—176 с.

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

«Основы работы с электронными таблицами Microsoft Excel»

Цель работы: изучить принципы работы с электронными таблицами на примере работы с Microsoft Excel.

 

В настоящем учебном пособии изложены основные приемы работы с табличным процессором Microsoft Excel for Windows.

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

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

Авторы надеются, что учебное пособие будет одинаково полезно как пользователям, только приступающим к работе с Microsoft Excel, так и тем, чье первое знакомство c программой уже состоялось.

В тексте использованы следующие условные обозначения:

1. Основные термины выделены курсивом.

2. Команды меню, названия диалоговых полей, кнопок и прочих элементов диалога выделены полужирным шрифтом.

3. Команды, выполняемые последовательно, разделяются дефисом. Так, строка Файл — Сохранить предполагает следующую последовательность операций: открыть пункт меню Файл, затем выбрать команду Сохранить

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

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

Сведения об основных фондах предприятия в начале и в конце года представлены в столбцах 1—4 таблицы.1 «Анализ объема и состава основных средств предприятия» На основании имеющихся данных требуется заполнить таблицу недостающими расчетными данными и подготовить ее к печати.

Таблица 1.

Изучаемые технические приемы

· запуск Microsoft Excel;

· операции с листами активной рабочей книги;

· ввод данных в ячейки электронной таблицы;

· выделение диапазона ячеек;

· форматирование ячеек и диапазонов ячеек;

· изменение ширины столбца и высоты строки

· форматирование ячеек по образцу (копирование формата);

· ввод формул в ячейки, абсолютная и относительная адресация;

· присвоение ячейкам имен;

· автозаполнение (копирование формул);

· обрамление ячеек;

· сохранение рабочей книги на диске;

· подготовка к печати и печать документа.

 

Порядок выполнения работы.