Форматирование и редактирование данных

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

Обработка числовых данных в электронных таблицах

Цель работы:Изучение процесса создания электронных таблиц: ввод заголовка, «шапки» и информационной части (данных, расположенных в ячейках). Изучение способов выполнения расчетов по формулам, форматирования ЭТ, подготовки к печати вExcel, консолидации данных.

Теоретическое обоснование

Электронная таблица (ЭТ) – это диалоговая система обработки данных, представленных в виде таблицы, состоящей из строк и столбцов. Программные средства для проектирования ЭТ называют табличными процессорами. Наиболее распространенными являются SuperCalc, QufttroPro, Microsoft Excel. Они позволяют автоматизировать обработку табличных данных и создавать таблицы, которые являются динамическими, содержащими вычисляемые поля, значения которых автоматически пересчитываются по заданным формулам при изменении значений исходных данных, содержащихся в других полях. При этом допускается копирование и перенос содержимого ячеек, получение усредненных значений, возможна работа с несколькими таблицами, их анализ по единому признаку. В ЭТ используют следующие типы данных: текст (любая последовательность символов), число (числовая константа), дата (значение даты, которая может представляться в различных форматах), формула (выражение, состоящее из числовых величин и арифметических операций), функции (запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений).

После загрузки Excel на экране появится окно стандартного для Windows вида. Рабочая книга – основной документ Excel, который состоит из отдельных рабочих листов. Рабочие листы состоят из столбцов и строк. Столбцы обозначаются буквами латинского алфавита ( А, В, С, ...), расположенными в заголовочной части таблицы. Строки – цифрами (1,2,3 ...), находящимися в первой колонке. Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и номера строки, например, АВ14, С27. В ЭТ можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок. Имена ячеек в блоках указываются диагональные и разделяются двоеточием (:), например, блок А1:В2 состоит из ячеек А1, А2, В1, В2. Вычисления в Excel выполняются при помощи формул, которые могут содержать числовые константы, ссылки на ячейки и функции, соединенные знаками математических операций. Ссылки могут быть: относительными (при копировании формулы изменяются автоматически, например А1), абсолютными (при копировании не изменяются, например $А$1) и смешанными(сочетают в себе относительные и абсолютные, например А$1, $А1).

Аппаратура и материалы: персональный компьютер не ниже Pentium-4, ОС семейства Windows, табличный процессор Microsoft Excel 2007 или выше.

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

Задание1

Создать рабочий лист с ЭТ, анализирующей заработную плату некоторого отдела в соответствии с приведенной таблицей 1.

Методика выполнения задания 1

Формирование заголовка и шапки таблицы.

Щелкните левой кнопкой мыши на ячейке А1, ячейка станет активной. На клавиатуре наберите «Ведомость начисления заработной платы отдела №4 за октябрь 2011 г.» без кавычек и нажмите клавишу ввода. Выделите диапазон ячеек A1:I1, правой кнопкой вызовите контекстное меню выберите Формат ячеек> Выравнивание и выберите: по горизонтали – по центру и отображение – объединение ячеек (смотри таблицу 1). Эту же операцию можно сделать выбрав на панели инструментов вкладки Главная>Выравнивание щелкните кнопку Объединить и поместить в центре.

Таблица 1 – Ведомость начисления заработной платы отдела №4 за октябрь 2011 г.

Ведомость начисления заработной платы за октябрь отдела №4 2011 г.

№

ФИО

Стаж

Начислено

Удержано

К выдаче

Оклад

Премия

Подо-ходный налог

Пенси-онный фонд

Аванс

Андреев И. И.

10000,00р.

Воробьёв К. И.

13800,00р.

Иванов И.И.

14410,00р

Ковалёв А. Л.

10020,00р.

Лучкин С.Ю.

10000,00р.

Сидоров С.С.

13098,00р.

Петров П.Н.

12459,00р.

Токарев И.Н.

15300,00р.

Ушаков И. И.

15300,00р.

Федоров Ф.А.

14355,00р.

Средняя заработная плата

Всего к выдаче

Максимальная заработная плата

Объедините ячейки А2:A3, используя любой известный способ, затем щёлкните по получившейся ячейке. Она станет активной, введите «№». Аналогично объедините ячейки В2:B3, введите «ФИО», ячейки С2:С3 и введите «Стаж», ячейки D2:E2 и введите «Начислено», ячейки F2:H2 и введите в них «Удержано». Объедините ячейки I2:I3, введите в них «К выдаче» и выполните операцию Перенос по словам: щелкните левой кнопкой мыши на ячейке С2:С3, затем правой кнопкой мыши вызовите контекстное меню. В котором выполните команды Формат ячеек> выравнивание>отображение>переносить по словам> ОК. В ячейку D3 введите «Оклад», в ячейку E3 «Премия», в ячейку F3 «Подоходный налог» и выполните операцию перенос по словам, в ячейку G3 «Пенсионный фонд» и выполните операцию перенос по словам, в ячейку H3 «Аванс». Аналогично в соответствии с таблицей 1 заполните ячейки A4:D13, где необходимо выполните перенос по словам. Объедините диапазон ячеек A14:H14 и введите в получившуюся ячейку «Средняя заработная плата». Объедините диапазон ячеек A15:H15 и введите в получившуюся ячейку «Всего к выдаче». Объедините диапазон ячеек A16:H16 и введите в получившуюся ячейку «Максимальная заработная плата».

Заполнение таблицы данными.

Знаки вопроса (?), указанные в некоторых графах, означают, что данные значения необходимо рассчитать в процессе выполнения задания. В ячейки C4:D13 введите приведенные исходные данные из таблицы 1. Если какие либо данные не помещаются в ячейку необходимо выполнить следующие действия: установите курсор мыши на границу между ячейками, при этом курсор мыши получит форму крестика с двунаправленной горизонтальной стрелкой. Нажмите левую кнопку мыши и, удерживая ее, протащите вправо, расширяя столбик, до тех пор, пока в нем целиком не появится необходимые данные, после чего отпустите кнопку мыши. Так, например, поместите курсор между ячейками B и C,протащите курсор вправо, пока данные по фамилии не поместятся в ячейке.

В ячейках D4: I16 установите денежный формат. Для этого выделите необходимый диапазон, правой кнопкой вызовите контекстное меню, выберете Формат ячеек>Число>Денежный число десятичных знаков 2 и нажмите Ок.

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

Рассчитаем столбец E, т.е. Премия которая вычисляется как процент от оклада, в зависимости от стажа (20% от оклада, если проработано менее 10 лет, 25% если более 10). Введем формулу в ячейку E4 для этого: щелкните на ячейке и наберите =ЕСЛИ(C4<10;D4*0,2;D4*0,25), затем нажмите клавишу ввода. После этого в ячейке появится результат вычисления 2000,00р. Данная формула является логической. Она проверяет, выполняется ли условие и возвращает одно значение, если оно выполняется, другое значение, если не выполняется. Общий вид функции:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь))

Щелкните на ячейке E4, проследите, что в строке ввода появляется формула, находящаяся в данной ячейке. В случае необходимости ее можно редактировать. Скопируем формулу из ячейки E4 вниз на ячейки E5:E13. Для этого: установите курсор мыши в нижний правый угол ячейки E4, при этом курсор изменит свою форму на крестик. Нажмите левую кнопку мыши и, удерживая ее, протащите вниз до ячейки E13 включительно. Формула скопируется и в столбике E появятся результаты расчетов. Снимите выделение. При копировании Excel автоматически корректирует адреса ячеек. Убедитесь в этом, для чего переведите курсор к ячейке E5 и посмотрите на строку ввода. Там сейчас находится формула: =ЕСЛИ(C5<10;D5*0,2;D5*0,25). В ячейке E6 – формула = ЕСЛИ(C6<10;D6*0,2;D6*0,25) и так далее.

Аналогично введите формулы в графу «Подоходный налог» в столбец F, учитывая, что подоходный налог составляет 13% от начисленной суммы, т.е. в ячейку F4 введите =(D4+E4)*0,13. Скопируйте ее вниз по ячейку F13. Аналогично введите формулы в графу «Пенсионный фонд» – столбец G , в ячейку G4 введите =(D4+E4)*0,01. Осуществите её ввод и скопируйте вниз по ячейку H13.

В ячейку H4, введите формулу, вычисляющую «Аванс» =((D4+E4)-(F4+G4))*0,3. Осуществите её ввод и скопируйте вниз по ячейку H13.

В ячейку I4, введите формулу, вычисляющую «К выдаче» =(D4+E4)-(F4+G4+H4). Осуществите её ввод и скопируйте вниз по ячейку H13.

Рассчитаем «Среднюю заработную плату». Для этого в ячейку I14 введите формулу =ОКРУГЛ(СРЗНАЧ(I4:I13);0). Теперь рассчитаем «Всего к выдаче». Для этого в ячейку I15 введите формулу =СУММ(I4:I13). Найдём значение «Максимальной заработной платы ». Для этого в ячейку I16 введите =МАКС(I4:I13).

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

Рассмотрите панель инструментов раздела Библиотека функций. Команда автосуммирование позволит найти необходимые итоговые суммы. Для этого выделите тот блок ячеек, сумму которых надо найти, на вкладке Формулы в Библиотеке функций щелкните кнопку Автосумма. В ячейке итоговой суммы появится результат суммирования. Второй способ: щелкните в ячейке итоговой суммы и дважды щелкните кнопку Автосумма. Третий способ – через Мастер функций: щелкните в ячейке итоговой суммы, в строке функций выше Рабочего листа выберите f_x или на панели инструментов щелкните кнопку Вставить функцию f_x. Появится окно Мастера функций. В первом диалоговом окне Мастера выберите Категорию – математическая, Имя функции – СУММ, щелкните «ОК». Во втором диалоговом окне мастера введите аргументы – диапазон ячеек сумму которых надо найти, щелкните «ОК». Аналогично, применяя формулу вычисления среднеарифметического СРЗНАЧ из Категории Статистических функций, рассчитайте среднее значение и т. д.

Форматирование и редактирование данных.

Перейдите на вкладку Главная, раздел Шрифт. Выделите блок ячеек А3:I16. На панели инструментов щелкните на стрелке у кнопки «Размер шрифта». В ниспадающем списке выберите размер 12. Столбцы, для которых это необходимо слегка раздвиньте так, чтобы весь текст был виден. В случае необходимости установите масштаб просмотра 75%, перейдя на вкладку Вид>Масштаб> 75%, Ок. Выделите блок ячеек А1:I1, на вкладке Главная выберите и установите шрифт 18. Отцентрируйте данные в ячейках A2:I3, выделите их и на панели Главная>Выравнивание> нажмите кнопку по центру, затем выберете шрифт 16 и щелкните кнопку Курсив. Три последних строки в таблице сделайте жирными.

Вставьте пустую строку перед словами «Средняя заработная плата» для этого щелкните в ячейке «Средняя заработная плата». Щелкните правой кнопкой мыши, в появившемся контекстном окне выберите команду Вставка…>строка. Щелкните в ячейке D3, выполните команду Вставка…> столбец. Появится дополнительный пустой столбец, а все данные сдвинутся вправо. В шапку этого столбика в С3 введите «Должность», отформатируйте содержимое этой ячейки, объединив её с ячейкой С2. Заполните получившиеся столбец и строку по своему усмотрению (в столбец введите название должностей, а в строку ещё одну фамилию работника). Проследите, как изменились итоговые значения.

Выделите всю таблицу, вызовите контекстное меню, Формат ячеек>Граница выберите внешние и внутренние и нажмите Ок. Появятся границы таблицы. Выделите ячейки А 1: J1, на панели выберите Главная> Цвет заливки, установите цвет – бледно-желтый. Затем щелкните на стрелке у кнопки Цвет шрифта и установите синий шрифт. Раскрасьте любые части таблицы по своему усмотрению.

Вставьте дополнительные три строки в середину таблицы. При этом фамилии и исходные данные введите по своему усмотрению. Для этого щелкните по заголовку строки 8 и протащите указатель мыши вниз, захватив 9 и 10-ю строки. Затем в контекстном меню выберите команду Вставить. После этого введите новые данные. Скопируйте формулы в появившиеся ячейки G8:G10, H8:H10, I8:I10 и J8:J10. Удалите 9-ю строку через контекстное меню по команде Удалить. Перенумеруйте строки первого столбца. В ячейке А4 остается 1. Наведите курсор мыши в правый нижний угол этой ячейки, нажмите правую кнопку мыши и протащите вниз до ячейки А16. Отпустите кнопку мыши, появится контекстное меню. В нем выполните команду Заполнить, строки прономеруются автоматически. Сохраните таблицу в своей папке по команде Файл> Cохранить как: Заработная плата отдела №4 за 4 квартал.

Дата добавления: 2018-04-05; просмотров: 133; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!