Электронные таблицы MS EXCEL

 

Задание 3.1: Общее знакомство с MS EXCEL

Описать характеристику табличного процессора MS EXCEL.

 

Приложение к заданию 3.1.

 

Общее знакомство с MS EXCEL

 

Программа Ms Excel – это мощный и достаточно простой в использовании пакет обработки электронных таблиц, предназначенных для решения широкого круга планово- экономических, учётно- статистических, научно- технических и других задач, в которых числовая, текстовая или графическая информация с некоторой регулярной, повторяющейся структурой представлена в табличной виде.

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

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

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

1. проведение однотипных расчётов над большими наборами данных;

2. автоматизация итоговых вычислений;

3. решение задач путём подбора значений параметров, табулирования формул;

4. обработки результатов экспериментов;

5. проведение поиска оптимальных значений параметров;

6. подготовки табличных документов;

7. построения диаграмм и графиков по имеющимся данным.

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

Создание электронных таблиц MS Excel.

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

 

Основные понятия электронных таблиц.

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до V. Строки последовательно нумеруются цифрами, от 1 до 65536 (максимально допустимый номер строки).

 

Вычисления в электронных таблицах.

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

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

 

Применение электронных таблиц для расчетов.

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

 

Основнйе возможности MS Excel.

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

Документ MS Excel записываются в файлы, имеющие расширение.xls. Кроме того, MS Excel может работать с электронными таблицами и диаграммами, созданными в других распространенных пакетах (например, Lotus 1 – 2 – 3), а также преобразовывать создаваемые им файлы для использования их другими программами.

 

Основные возможности и инструменты программы MS Excel.

1. Широкие возможности создания и изменения таблиц произвольной структуры;

2. Авто заполнение ячеек таблицы;

3. Богатый набор возможностей форматирования таблиц;

4. Богатый набор разнообразных функций для выполнения вычислений;

5. Автоматизация построения диаграмм различного типа;

6. Мощные механизмы создания и обработки списков (баз данных): сортировка, поиск, фильтрация;

7. Механизмы автоматизации создания отчетов.

 

Совместные работы программ Microsoft Office.

Вставка текстового фрагмента Word в документ Excel.

Вставка текстового фрагмента Word в документ Excel происходит при помощи операции буфера обмена (копировать, вырезать, вставить).

Эта операция редко используется на практике. Чаще пользователь сталкивается с задачей вставки таблицы Excel в документ Word.

Вставка таблицы Excel в документ Word.

Таблицу созданную в Excel можно вставить в документ Word:

1. Запустить программу MS Word

2. Запустить программу MS Excel

3. В электронной таблице выделить блок ячеек, которые хотим внести в Word.

4. Выполнить команду Меню Правка – Копировать.

5. Перейти в окно программы MS Word.

6. Выполнить команду Меню Правка – Специальная вставка.

7. В появившемся окне выбрать пункт: Связать или Вставить

Если выберете пункт, Вставить, то таблица Excel добавится в документ Word и при именении данных таблиц Excel данные будут оставаться в документе Word неизменёнными.

Если же выберете пункт Связать, то изменения источника (табл. Excel) повлечёт за собой изменения данных в документе Word.

 

Задание 3.2. Работа с электронными таблицами MS Excel. Формулы. Расчеты.

 

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

 

Таблица 1.

Учет приобретения материальных ценностей

Номенклатурн номер мат.цен.	Учётн.оценка 1 ед.	Кол - во ед.	Фактич. ст - ть приобр. мат. цен.	Учётн.ст - ть. приобр. мат.цен.	Отклонен. в ст - ти мат.цен.
Итого

Примечание:

1. Учётная стоимость приобретённых материальных ценностей определяется умножением учётной оценки 1 ед. материальных ценностей на количество материальных ценностей.

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

 

Задание 3.3. Работа с электронными таблицами MS Excel и расчёты в них. Построение диаграмм

 

1. Используя данные табл. 2 произвести в MS Excel расчёты полной себестоимости, прибыли от реализации продукции и валовой (общей) прибыли предприятия в период 2009-2011 г.г

2. Построить диаграмму по полной себестоимости, прибыли от реализации, валовой прибыли в период 2009-2011 г.г.

3. Используя показатели валовой прибыли по табл.2, сделать расчёты распределения прибыли согласно табл.3.

4. Построить диаграмму распределения чистой прибыли по каждому году.

 

Таблица 2

Показатели производственной деятельности предприятия за 2009-2011 гг

 

показатели	2009г.	2010г.	2011г.	всего
1.производственная себестоимость гот. прод-ции
2.коммерческие расходы
3.полная себестоимость гот. прод-ции
4.выручка от реализации гот. прод-ции
5.прибыль от реализации гот. прод-ции
6.внереализационные доходы
7.внереализационные расходы
8.валовая прибыль за год

 

Примечание:

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

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

3. Валовая прибыль равна: прибыль от реализации продукции + внереализационные доходы – внереализационные расходы.

 

Таблица 3.

Распределение прибыли предприятия

Показатели	2009г.	2010г.	2011г.	Всего
1. Валовая прибыль
2. Налог на прибыль (20%)
3. Чистая прибыль
4. 5% чистой прибыли направлено в резервный капитал
5. 20% направлено на выплату дивидендов
6. Направлено на погашение задолженности по облигациям
7. Нераспределённая прибыль

Примечание:

Чистая прибыль = валовая прибыль – налог на прибыль

Нераспределенная прибыль – это части чистой прибыли, оставшаяся после распределения

Задание 3.4. Работа с простейшими базами данных в MS Excel. Фильтры.

 

1.Составить алфавитную книгу группы, отражая в ней следующие данные (графы):

- Фамилия;

- Имя;

- Отчество;

- Дата рождения;

- Пол (муж., жен.);

- Адрес проживания;

- Группа;

- Успеваемость (ср.балл).

 

2.Определить средний балл группы

 

3.Используя фильтры показать:

- Количество студентов мужского (или женского) пола;

- Количество отличников;

- Количество хорошистов;

- Количество студентов с успеваемостью «удовл.»

 

4.В составленной алфавитной книге сделать примечание на свою фамилию («Это я»).

 

5.Оформить таблицу, используя различные границы и заливки.

 

Задание 3.5. Анализ данных и подведение итогов с помощью таблиц в MS Excel.

 

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

 

Ø Доплата за вредность 15% от оклада программиста, кассира, инженера

Ø Доплата за сложность 20% от оклада гл.бухгалтера, бухгалтера, юриста

Ø Доплата за стаж 10% от оклада Вдовина, Машкова, Никитиной, Федотова

Ø Удержан аванс в размере 40% от начисленной суммы

Ø Удержан НДФЛ в размере 13% от начисленной суммы

Ø Удержан профсоюзный взнос в размере 1% от начисленной суммы

 

Таблица 4.

Расчетно-платежная ведомость ООО «Маяк»

 

ФИО	должность	оклад	надбавки	итого начисл	удержания	итого	к выдаче
вред	слож	стаж	аванс	НДФЛ	проф.вз	удерж
Вдовин ЕГ	директор
Машков О.Д.	зам.дир
Никитина ЕА	гл.бухг
Петров Н.П.	нач отдела
Смирнова ВВ	нач.отдела
Уткина ГИ	экономист
Федотов КА	юрист
Авдеев ПО	программист
Новикова ИЛ	референт
Игнатова ЗД	бухгалтер
Большева ВМ	бухгалтер
Яковлева МС	кассир
Куликова НП	инженер
Итого:

 

 

Задание 3.6. Работа с электронными таблицами и листами в MS Excel.

 

1. Используя данные к заданию создать в MS Excel лицевые счета работников на отдельном листе (листы 1, 2, 3).

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

3. Создать 4-й лист в MS Excel как платежную ведомость по зарплате за январь 2011г. Заполнить таблицу путем связи таблиц и листов MS Excel.

 

Данные к листу 1 «Лицевой счет Воробьева А.С.»:

Воробьев Александр Степанович 1960 г.р.

Стаж работы на предприятии 11 лет (с 25.12.1999).

Должность: начальник цеха.

Зарплата за январь 2011г:

Начислено: повременно (оклад) 22000 руб., премия 25% от оклада.

Удержано: ранее выданный аванс 40% от оклада, НДФЛ 13%.(стандартный налоговый вычет 400 руб)

 

Данные к листу 2 «Лицевой счет Степановой Н.В.»:

Степанова Надежда Васильевна 1969 г.р.

Стаж работы на предприятии 7 лет (с 11.11.03).

Должность: нормировщик цеха.

Зарплата за январь 2011г:

Начислено: повременно (оклад) 5300 руб., за часы сверхурочно 710 руб., пособие по временной нетрудоспособности 1356 руб., премия 10% от оклада.

Удержано: ранее выданный аванс 40% от оклада, НДФЛ 13% руб. %.(стандартный налоговый вычет 1400 руб)

 

Данные к листу 32 «Лицевой счет Терехова Б.В.»:

Терехов Борис Викторович 1975 г.р.

Стаж работы на предприятии 3 года (с 3.12.07).

Должность: токарь.

Зарплата за январь 2011г:

Начислено: сдельно (по тарифу) 8400 руб., премия 15% от оклада, доплата из фонда мастера 1000 руб.

Удержано: ранее выданный аванс 40% от тарифа, НДФЛ 13% %.(стандартный налоговый вычет 400 руб)