Практическая работа № 1 Основные приемы работы с электронными таблицами EXCEL

Практическая работа № 1

Основные приемы работы с электронными таблицами EXCEL

УПРАЖНЕНИЕ 1

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

Создайте и заполните таблицу (рис.1):

(рис.1)

Способ выполнения:

Выделите ячейку A1 и введите в нее заголовок таблицы “Расчет заработной платы”.В ячейку А2 введите “Фамилия И.О.”, в ячейку В2 – “Оклад”, в ячейку С2 - “Премия”, в ячейку D2 - “Итого начислено”, в ячейку Е2 - “Подоходный налог (12 %)”, в ячейку F2 – “Пенс. фонд (1%)”, в ячейку G2 - “Итого удержано”, в ячейку H2 -“Итого к выдаче”.

Заполните столбцы “Фамилия И.О.” (4 человека), “Оклад”, “Премия ” по своему усмотрению.

Подсчитайте сумму начислений для каждого сотрудника. Воспользуйтесь формулой. Ввод формулы начинается с символа =(знак равенства). В ячейку D3 поместите следующую формулу: =В3+С3 (рис 2). После ее ввода в ячейке D3 получим сумму оклада и премии для первого сотрудника.

(рис.2)

Чтобы вычислить сумму начислений для остальных сотрудников необходимо ввести соответствующие формулы в соответствующие ячейки. А можно выполнить следующее:

-выделите ячейку D3;

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

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

После указанной операции формула из ячейки D3 будет скопирована в ячейки D4, D5, D6. Причем при переносе формул EXCEL автоматически изменит адреса ячеек.

Вычислите сумму подоходного и пенсионного налогов. Это можно сделать, используя формулу =D3*12/100 в ячейке E3 и =D3*1/100 в ячейке F3.

В ячейке G3 подсчитайте сумму этих налогов по формуле =E3+F3. В ячейку H3 запишите выражение =D3-G3, определив тем самым сумму к выдаче. Итак, данные и формулы для расчета зарплаты первого сотрудника введены полностью.

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

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

В ячейку А7 введите “Всего:”. Выделите ячейку B7 и нажмите кнопку Автосуммирование на панели инструментов. EXCEL сам выделяет нужные ячейки от B3 до B6 пунктирной рамкой, а в строке формул появляется команда =CУMM (B3:B6) (рис.3). После нажатия клавиши [Enter] в ячейке B7 появится сумма окладов по всем сотрудникам. Аналогично подсчитайте итоговые результаты в остальных столбцах.

 

(рис.3)

Добавьте в вашу таблицу нового сотрудника и новый столбец с порядковыми номерами сотрудников. Чтобы вставить столбец или строку в уже готовую таблицу, воспользуйтесь соответствующей командой из меню Вставка. Пустая строка вставляется над выделенной ячейкой, а столбец – слева от нее. Чтобы заполнить столбец с порядковыми номерами сотрудников, в ячейку А3 введите число 1, в ячейку А4 - число 2. Выделите обе ячейки и протащите маркер вниз, чтобы заполнить столбец числами от 1 до 4. Вставленную строку заполните, описанным ранее, способом.

Чтобы иметь возможность снова обратиться к таблице, нужно ее сохранить. Выполните команду [Файл / Сохранить как..]. На экране появится диалоговое окно, где будет предложено указать имя документа и каталог, в котором он должен быть сохранен. Сохраните книгу под именем C: / Студент/№ группы/Фамилия/ Расчет заработной платы. Таблицы EXCEL хранятся на диске в виде файлов с расширением .XLS, поэтому расширение файла задавать не нужно.

 

УПРАЖНЕНИЕ 2

Закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: сортировка данных, типы выравнивания текста в ячейке, формат числа, оформление таблицы.

Отформатировать таблицу “Расчет заработной платы”

Способ выполнения:

Откройте таблицу, подготовленную на прошлом занятии.

- Для открытия ранее созданной и сохраненной таблицы на экран выберите в окне диалогового окна C: / Студент/№ группы/Фамилия/ Расчет заработной платы.

Выделите заголовок полужирным шрифтом.

- Выделите ячейку А1, в которую введен заголовок таблицы, а затем нажмите кнопку .

-Чтобы увеличить размер шрифта заголовка - , щелкните мышью на элементе и выберите новый размер.

Выровняйте заголовок таблицы “Расчет заработной платы”.

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

-выделите ячейки с А1 по I1;

-нажмите кнопку .

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

- выполните выделение столбца с порядковыми номерами сотрудников.

- cодержимое всех выделенных ячеек выровняйте по центру при помощи кнопки .

Представьте все суммы таблицы в денежном формате.

-выделите таблицу;

-нажмите правую кнопку мыши и в контекстном меню выберите команду [Формат ячеек…];

- в открывшемся диалоговом окне выберите вкладку Число иустановите Денежныйформат с двумя знаками после запятой без отображения денежной единицы.

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

Отсортируйте фамилии сотрудников по алфавиту.

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

- выполните команду [ Данные – Сортировка]или выберите на панели инструментов ;

-установите переключатель в положение «По возрастанию».

Оформите шапку вашей таблицы.

-выделите строку с номером 2;

-вызовите из меню Форматкоманду Ячейки…;

-в открывшемся диалоговом окне выберите вкладку Выравниваниеи установите флажок “Переносить по словам”.

Выделите шапку таблицы серым цветом - , а затем выполните ее обрамление - .

Выделите итоговые строки и столбцы полужирным шрифтом и курсивом.

В итоге у вас должна получиться таблица, как на рис.5.

(рис.5)

Задания

1) Создайте таблицу по образцу (рис. 6), используя в работе следующие приемы обработки данных в Excel:

- ввести данные с представлением в различных форматах;

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

-освоить операцию Автозаполнения ячеек формулами

-освоить вычисление итоговой суммы.

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

 

(рис. 6)

 

Создайте и заполните бланк товарного счета (рис. 7).

(рис.7)

 

 

Практическая работа №2

Создание формул с использованием абсолютной и относительной адресации ячеек

УПРАЖНЕНИЕ

Закрепление основных навыков работы с электронными таблицами: типы выравнивания текста в ячейке, формат числа, оформление таблицы. Использование в таблице абсолютной ссылки на диапазон ячеек.

Способ выполнения:

• Откройте таблицу “Расчет заработной платы”- команда Файл /Открыть.

• Удалите данные из столбца “Премия”.

 

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

• Вставьте пустую строку под номером 3. Для этого выделите любую ячейку во второй строке и выполните команду [Вставка – Строка].

• В ячейки B3 и С3 введите “Процент премии :”

• Для удобства установите в ячейке D3 формат процента и введите значение 20.

• Вставьте пустую строку под номером 4.

• Для сотрудника под номером 1 в ячейку D5 введите формулу расчета премии =C5*D3.

• Введите формулу расчета премии для второго сотрудника в ячейку D6. Поскольку премия для него рассчитывается по подобной формуле, то ее можно скопировать из предыдущей ячейки D5. Скопируйте ее любым способом.

 

Однако после этой операции в ячейке D6 получится ошибочный результат (рис. 1). Это неудивительно, поскольку при копировании формулы EXCEL изменил адрес ячейки D2 на D3, чего в нашем случае делать не следовало.

(рис.1)

 

• Удалите данные из ячейки D6.

• В формуле ячейки D5 замените относительный адрес D3 на абсолютный $D$3. Это можно сделать непосредственно вводом символа $ в строку формулы или установить указатель на ячейку D5 и дважды нажать кнопку мыши, затем выделите в ячейке ссылку D3 и нажимайте клавишу [F4] до тех пор, пока ссылка не примет нужный вид $D$3. Для завершения редактирования нажмите клавишу [Enter].

• Скопируйте формулу для всех сотрудников. Копирование формулы будет проходить корректно.

• Изменение премии привело к изменению данных в других столбцах: “Итого начислено”, “Подоходный налог (12%)” и т.д. Заполните данные в оставшихся столбцах, как это делалось на первом занятии (с помощью формул). Пересчитайте данные по строке “Всего”.

Задание

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

Подготовьте таблицу, состоящую из столбцов: ”Наименование товара”, “Эквивалент $US”, “Цена в р.”. Столбец ”Наименование товара” заполните текстовыми данными (перечень товаров по вашему усмотрению), а столбец “Эквивалент $US” – числами (цены в долл.).

Понятно, что в столбце “Цена в р.” должна разместиться формула “Эквивалент $US * Курс доллара”.

 

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

Примените разные типы выравнивания данных.

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

 

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

Практическая работа №3

Функции. Расчеты в EXCEL

Упражнение 1. Вставка функции при помощи Мастера функций.

Подсчитаем среднее значение окладов сотрудников.

Откройте таблицу ”Расчет заработной платы” – команда Файл/Открыть . В ячейку В9 введите “Cреднее значение”. Выделите ячейку С9.

Задание 3.

1. В столбце А вывести все числа от –3 до 3 с шагом 0,5.

2. Вывести в столбец В значения функции 2x-sin3x, используя в качестве переменных значения столбца А.

3. Найти максимальное значение и сумму положительных значений для столбца B, вывести полученный результат в ячейках D1 и D2.

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

Тема: Основные приемы работы

С электронными таблицами

Цель работы: введение основных понятий, связанных с работой электронных таблиц EXCEL. Применение основных приемов работы с EXCEL.

 

Содержание работы:

· Ввод данных в ячейку;

· Изменение ширины (высоты) ячейки;

· Вставка строки (столбца);

· Автозаполнение ячеек;

· Ввод формулы;

· Сохранение таблицы.

 

Задание 1. Составьте таблицу значений линейной функции y=kx+b, выбрав по своему усмотрению угловой коэффициент k и свободный член b. Воспользуйтесь образцом таблицы для функции y=3,5x-9,5.

Ключ к заданию:

- Для заполнения ячеек значениями x введите первые два значения, выделите обе ячейки и протяните маркер заполнения вправо.

- Для заполнения ячеек значениями у введите формулу в первую ячейку ряда и заполните вправо.

 

Задание 2

Известно, что в теле взрослого человека, массой около 70 кг содержится примерно 40 кг воды, 15 кг белка, 7 кг жира, 3 кг минеральных солей,0.7 кг углеводов.

Но организм – это не склад, где все заложенное хранится в неприкосновенном виде. В организме человека постоянно происходят процессы обмена, одни вещества сгорают, окисляются, выводятся, и взамен них должны поступать новые. Подсчитано, что в среднем за 70 лет жизни человек выпивает 50 т воды, съедает 2,5 т белка, 2,3 жира, свыше10 т углеводов, почти 300 кг поваренной соли.

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

Задача

Подсчитать количество белков, жиров, углеводов и энергетическую ценность суточного рациона по меню, составленному дома.

Ключ к заданию:

-Решая задачу, заполните столбцы А и В.

-Данные по столбцам С, D, E возьмите из таблицы.

 

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

Тема: Форматирование ячеек.

Оформление рабочих листов

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

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

Выполнение работы в 3 этапа:

Этап. Создание таблицы бланка счета.

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

Этап. Оформление бланка.

· Предварительно установить поля, размер и ориентацию бумаги (Файл / Параметры страницы); · Создайте таблицу по предлагаемому образцу с таким же числом строк и…

Заполнение таблицы, сортировка данных, использование различных форматов числа.

· Установите денежный формат числа в тех ячейках, в которых будут размещены суммы и установите требуемое число десятичных знаков, если они нужны: … · Введите формулу для подсчета суммы, которая заключается в умножении цены на… · Введите формулу в ячейку для итоговой суммы.

Задание 2

Подготовить смету доходов и расходов сотрудника компании Техноинвест за ноябрь месяц.

- Примените денежный формат числа.

- Введите соответствующие формулы в ячейки “Итого” и “Остаток”.

 

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

Цель: закрепление основных навыков работы с электронными таблицами, знакомство с понятиями: относительная ссылка, абсолютная ссылка, смешанная…   Содержание работы:

Составьте таблицу умножения чисел от 1 до 9.

Не забудьте про абсолютные ссылки в формулах!

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

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

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

Тема: Функции. Расчеты в EXCEL

Цель:Закрепление понятия “абсолютная ссылка”. Вставка функции при помощи Мастера функций.

Содержание работы:

· Копирование содержимого ячейки или блока ячеек;

· Форматирование числовых данных в ячейках;

· Абсолютная и относительная адресация ячеек;

· Ввод формулы в ячейку;

· Работа в окне диалога Мастера функций.

 

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

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

Подготовить традиционную таблицу квадратов двузначных чисел.

3. Вычислить значение функциидля всех Х на интервал [-2;2] c шагом 0, 2 при К=10. Решение должно быть получено в виде таблицы: Подсчитать :

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

Содержание работы: - построение диаграммы с помощью Мастера диаграмм на основе ряда данных; - построение диаграмм разных типов;