Федеральное государственное
Бюджетное образовательное
Учреждение высшего профессионального
Образования
«Казанский национальный технический университет им. – КАИ»
(КНИТУ_КАИ)
Методические указания к лабораторным работам в программе EXCEL
для студентом 1 курса
Казань 2013
Работа в Microsoft Exсel
Exсel – это электронные таблицы. Они предназначены для выполнения объемных однотипных расчетов, при периодическом заполнении различных отчетных форм. При выполнении вычислений, которые определяются меняющимися исходными данными, требованиями и ограничениями
Документ в электронной таблице называется книгой. Книга может состоять из нескольких рабочих листов. Каждый лист имеет ярлык, который содержит имя листа. Щелкая по ярлыкам, можно переходить на другие листы книги. Каждый лист книги состоит из ячеек, максимальное число столбцов на листе равняется 256, максимальное число строк – 16384. Все столбцы и строки имеют заголовки. Строки обозначаются арабскими цифрами, а столбцы – буквами латинского алфавита.
 |
Основные понятия
Строка формул – содержит
· Поле адреса текущей ячейки
· Кнопки работы с формулами
· Поле ввода формул
Основные объекты это: столбец, строка, ячейка. С каждым из этих объектов можно выполнить следующие операции: выделение, удаление, вставка, форматирование, обрамление
Ячейка
Каждая ячейка таблицы имеет относительный и абсолютный адрес. Относительный адрес ячейки – это буква и цифра, соответствующие столбцу и строке, на пересечении которых находится ячейка, например A3. Абсолютный адрес ячейки записывается с помощью знака $, например $A$3. Абсолютные адреса не меняются при перемещении, а относительные адреса меняются.
В любую ячейку можно внести
· Текст
· Числовое значение
· Формулу для вычисления конкретных значений
Ввод информации в ячейку
· Установить курсор на нужной ячейке
· Ввести нужную информацию. Вводимая в ячейку информация отображается в ячейке и в поле ввода строки формул.
· Для завершения нажать клавишу Enter, или щелкнуть на кнопке, или щелкнуть мышью в любом месте рабочего листа.
Ввод формул
· Установить курсор на нужной ячейке
· Начать ввод формулы. Для этого надо
¨ Либо щелкнуть на значке на панели инструментов
¨ Либо щелкнуть на значке в строке формул.
¨ Либо ввести со знак равенства =. Наличие знака "=" говорит о том, что вслед за ним будет введена формула для вычислений. После ввода этого знака в строке формул появляются кнопки для работы с формулами. Формула, вводимая в ячейку, высвечивается в строке формул в соответствующем поле.
Редактирование информации в ячейке
Первый способ:
Дважды щелкнуть на ячейке, чтобы начать редактирование формулы
Второй способ:
Выбрать ячейку и нажать клавишу F2
Третий способ:
Выбрать ячейку и щелкнуть мышью в строке формул
Пример:
1. Ввести в ячейку А1 число 5
2. .В ячейку C2 ввести формулу для вычисления квадрата значения из ячейки А1.
 |
Обычно в ячейках отображаются результаты вычислений. Установить режим отображения формул можно при помощи следующей процедуры:
· Дать команду Сервис/Параметры.
· На вкладке Вид в опциях Параметры окна установить режим Формулы. При этом в ячейках отображаются не значения, а формулы.
В Excel относительный адрес какой-либо ячейки в формуле представляет собой ссылку на эту ячейку относительно ячейки, содержащей формулу. Это легко можно увидеть, установив режим ссылок при помощи следующей процедуры:
· Дать команду Сервис/параметры
· На вкладке Общие установить опцию Стиль ссылок R1C1. При этом
¨ имена столбцов заменяются их порядковыми номерами.
¨ В поле адреса строки формул отображается адрес текущей ячейки в форме RnCm, где R – обозначение строки (от Row -), n – номер строки, C обозначение столбца (Column) , m –номер столбца.
¨ Адреса в формулах отображаются в виде R[n]C[m], где n и m это числа со знаком, показывающие расстояние от текущей ячейки до целевой.
Пример
Заполнение ячеек
Первый способ
· Выделить ячейки
· Дать команду Правка/Заполнить/
· Выбрать направление заполнения (Вправо, влево, вверх, вниз) Информация из первой выделенной ячейки скопируется в остальные.
Второй способ
· Выделить ячейку, содержащую исходный текст
· Установить указатель мыши на черный квадрат в правом нижнем углу, при этом курсор примет вид черного крестика
· Нажать левую клавишу мыши и растянуть рамку так, чтобы захватить те ячейки, которые требуется заполнить.
При заполнении в ячейки, охваченные рамкой, заносится содержимое выделенной ячейки. Но, в силу того, что адреса в Excel относительные, формулы в заполняемых ячейках содержат ссылки уже на другие ячейки.
Пример
1. Ввести в ячейку А1 значение 1
2. Ввести в ячейку В1 значение из А1, увеличенное на 1, в ячейку С1 - значение из В1, увеличенное на 1 и т. д. до ячейки Е1. Так как формула для изменения содержимого ячеек одинаковая, мы введем эту формулу в ячейку В1, а затем распространим ее на остальные ячейки путем заполнения.
После ввода формулы в В1
То же в режиме представления формул:
 |
После заполнения (стиль ссылок)
То же (обычный стиль, режим представления формул)
То же (обычный стиль, режим представления результатов)
Использование абсолютных адресов
Чтобы избежать изменения адресов при перемещении содержимого ячеек, используют абсолютные адреса.
Преобразование относительного адреса в абсолютный
· Выбрать ячейку
· Дважды щелкнуть в строке формул на адресе ячейке, чтобы выделить его
· Нажать клавишу F4
С помощью символа абсолютной адресации можно гибко изменять способ адресации
$A10 – меняется только номер строки
A$4 – меняется только номер столбца
Вместо абсолютных адресов можно использовать имена ячеек и блоков ячеек (диапазонов).
Назначение имен для абсолютной адресации
· Выделить ячейку
· Дать команду Вставка/Имя/Определить
· В диалоговом окне ввести имя ячейки и нажать ОК.
Аналогично можно дать имя блоку(диапазону) ячеек.
Имена можно вставлять в формулы во время редактирования.
В Excel верхняя ячейка столбца или левая ячейка строки с данными может рассматриваться как имя соответствующего диапазона. Это имя может использоваться при вводе формул, если включена соответствующая опция при помощи следующей процедуры:
· Дать команду Сервис/параметры
· На вкладке Вычисления в опциях Параметры книги установить режим Допускать названия диапазонов.
Выделение ячеек, блоков, строк и столбцов
1. Выделение ячейки :
Щелкнуть мышью на ячейке или перейти к ней, используя клавиши перемещения.
2. Выделение всей строки
Щелкнуть по заголовку строки.
3. Выделение всего столбца
Щелкнуть по заголовку столбца.
4. Выделение блока ячеек
· Поставить курсор в левый верхний угол блока
· Нажать и удерживать нажатой левую клавишу мыши
· Передвинуть курсор по диагонали к правой нижней ячейке блока.
5. Выделение всех ячеек листа
Щелкнуть на кнопке Выделить все.
6. Выделение несмежных блоков
· Выделить первый блок ячеек
· удерживая нажатой клавишу CTRL, выделить остальные блоки ячеек.
Вставка строк и столбцов
· Установить курсор в ячейку, над (слева) которой надо вставить строки (столбцы)
· Дать команду Вставка/Строки(Столбцы)
Вставка ячеек
· Установить курсор в ячейку
· Дать команду Вставка/Ячейки
· Выбрать направление вставки.
Удаление ячеек, строк и столбцов
· Выделить ячейки, строки и столбцы, которые следует удалить.
· Дать команду Правка/Удалить. Соседние ячейки сместятся, чтобы заполнить освободившееся место.
Форматирование и обрамление ячеек
Обрамление:
· Выделить ячейки
· Дать команду Формат/Ячейка
· Выбрать вкладку Граница и установить нужные границы.
Установка формата числа
· Выделить ячейки
· Дать команду Формат/Ячейка
· Выбрать вкладку Число и установить нужный формат.
Установка переноса по словам в ячейке (несколько строк в ячейке)
· Выделить ячейки
· Дать команду Формат/Ячейка
· Выбрать вкладку Выравнивание
· Установить опцию Переносить по словам.
Построение диаграмм
1 Выделить данные, на основе которых будет строиться диаграмма.
2 Вызвать Мастер диаграмм. Для этого либо дать команду Вставка/Диаграмма, либо щелкнуть мышью на пиктограмме Мастера диаграмм на панели инструментов, либо щелкнуть правой клавишей мыши и выбрать пункт меню Диаграмма.
3 Выбрать тип диаграммы
4 Выбрать вариант диаграммы
5 Задать расположение исходных данных для построения диаграммы в строках или столбцах
6 Определить, какие нужны комментарии, и определить их с помощью легенды
7 Ввести заголовок диаграммы и подписи к ней.
8 Отформатировать диаграмму.
Мастер диаграмм весь рисунок диаграммы разбивает на самостоятельные объекты. Чтобы выполнить форматирование, либо исправление этих объектов, надо указать на нужный объект и сделать два щелчка мышью. После этого можно вызвать контекстное меню щелчком правой клавиши мыши и выбрать в нем необходимую операцию.
ЗАДАНИЕ 1
1. Протабулировать следующие функции f1,f2,f3,f4 по вариантам
2. На рис.1 построить график для f1.
3. На рис.2 построить график для f2.
4. На рис.3 построить два графика для f1 и f2 на интервале от –0,5 до 0,5.
5. На рис.4 построить два графика для f3 и f4 на интервале от -3 до 0
6. Посмотреть, как меняются значения в таблице при изменении параметра а:
7. На рисунках 1, 2, 3, 4 сделать надписи на осях и под рисунками.
NN | x для F1 и F2 | F1 | F2 | F3 | F4 |
1 | х=-1¸2 шаг 0,1 а=1,2,5 |
|
|
|
|
2 | х=-1,5 ¸1, шаг 0,1а=1,2,4 |
|
|
|
|
3 | х=-1¸1, шаг 0,1а=1,3,5 |
|
|
|
|
4 | х=-1¸2, шаг 0,1а=1,2,3 |
|
|
|
|
5 | х=-1,5 ¸ 2, шаг 0,1а=1,2,4 |
|
|
|
|
6 | х=-1,5 ¸ 1,5, шаг 0,1а=1,3,5 |
|
|
|
|
ЗАДАНИЕ 2
1. Выполните таблицу, приведенную в задании. Заполните ячейки, где стоят знаки вопроса, путем автосуммирования, с помощью других автоматических кнопок или путем введения соответствующих формул.
2. После заполнения таблицы заполните ее с помощью прямоугольных и круговых диаграмм (двумерных и трехмерных). Каждый рисунок, содержащий диаграмму, должен включать легенду и соответствующие надписи на осях. Постройте следующие диаграммы с соответствующими заголовками. Рис 1 - Поступления в местный бюджет в 1 квартале. Рис 2 - Поступления в местный бюджет за счет налога соц. страхования по кварталам. Рис 3 - Суммы поступлений в местный бюджет (по всем статьям дохода и кварталам). Рис 4 - Итоговое распределение поступлений в местный бюджет по кварталам.
Суммы поступлений в местный бюджет. | ||||||
квартал | налог соц страхования | налог за строения | налог за дороги | штрафы | всего | процент |
квартал1 | 67,3 | 99,1 | 79,1 | 25,2 | ? | ? |
квартал2 | 98,36 | 87,2 | 85,1 | 24,9 | ? | ? |
квартал3 | 66,1 | 101,2 | 56,2 | 37,3 | ? | ? |
квартал4 | 72,1 | 95,2 | 31,95 | 11,95 | ? | ? |
Итого | ? | ? | ? | ? | ? | ? |
Задание 2
1. Выполните таблицу, приведенную в задании. Заполните ячейки, где стоят знаки вопроса, путем автосуммирования, с помощью других автоматических кнопок или путем введения соответствующих формул.
8. После заполнения таблицы проиллюстрируйте ее с помощью прямоугольных и круговых диаграмм(двумерных и трехмерных). Каждый рисунок, содержащий диаграмму, должен включать легенду и соответствующие надписи на осях. Постройте диаграммы с соответствующими заголовками. Рис1 - Объем производства завода 1 по годам. Рис 2 - Объем производства в 1988 г. заводов 1 -4. Рис 3 - Объем производства заводов 1 -4 в 1988, 1989 г. Рис 4 - Изменение объема производства заводов 1 -4 в 1989 г. по сравнению с 1988 г.
Объем производства заводов | ||||
Заводы | Объем пр. 1988 | Объем пр. 1989 | 1988 по отношению к 1989 в % отношении |
|
Завод 1 | 2908,5 | 2117,37 | ? |
|
Завод 2 | 1706,47 | 1552,5 | ? |
|
Завод 3 | 4001,83 | 4228,90 | ? |
|
Завод 4 | 1556,89 | 1551,90 | ? |
|
Итого | ? | ? | ? |
|
Задание 3
1. Выполните таблицу, приведенную в задании. Заполните ячейки, где стоят знаки вопроса, путем автосуммирования, с помощью других автоматических кнопок или путем введения соответствующих формул.
2. После заполнения таблицы проиллюстрируйте ее с помощью прямоугольных и круговых диаграмм(двумерных и трехмерных). Каждый рисунок, содержащий диаграмму, должен включать легенду и соответствующие надписи на осях. Постройте диаграммы с соответствующими заголовками. Рис1 - Распределение цен материалов. Рис 2 - Распределение количества приобретенных материалов. Рис 3 - Распределение цен и количества приобретенных материалов. Рис 4 - Распределение общих затраченных средств на приобретенные материалы.
Затраты по видам материалов | |||
Материал | Цена | Количество | Общая стоимость |
Ткани (инд1) | 70,65 | 20,80 | ? |
Кожа | 190,43 | 103,78 | ? |
Ткани (инд2) | 55,5 | 80,5 | ? |
Клеенка | 13,65 | 135,5 | ? |
Итого | ? | ? | ? |
Задание 4
1. Выполните таблицу, приведенную в задании. Заполните ячейки, где стоят знаки вопроса, путем автосуммирования, с помощью других автоматических кнопок или путем введения соответствующих формул.
2. После заполнения таблицы проиллюстрируйте ее с помощью прямоугольных и круговых диаграмм(двумерных и трехмерных). Каждый рисунок, содержащий диаграмму, должен включать легенду и соответствующие надписи на осях. Постройте диаграммы с соответствующими заголовками. Рис1 - Плановое отчисление в бюджет отраслями промышленностями. Рис 2 - Плановое и фактическое отчисление в бюджет отраслью Пищепром. Рис 3 - Плановые и фактические отчисления в бюджет отраслями промышленности. Рис 4 - Процент выполнения плана по отчислению в местный бюджет отраслями промышленностями.
Отчисления в местный бюджет | |||
Отрасль промышленности | План | Факт | Процентное отношение |
Пищепрм | 11907,00 | 10803,90 | ? |
Легпром | 27042,18 | 28011,3 | ? |
Агропром | 42564 | 42000 | ? |
Итого | ? | ? | ? |
Задание 5
1. Выполните таблицу, приведенную в задании. Заполните ячейки, где стоят знаки вопроса, путем автосуммирования, с помощью других автоматических кнопок или путем введения соответствующих формул.
2. После заполнения таблицы проиллюстрируйте ее с помощью прямоугольных и круговых диаграмм(двумерных и трехмерных). Каждый рисунок, содержащий диаграмму, должен включать легенду и соответствующие надписи на осях. Постройте диаграммы с соответствующими заголовками. Рис1 - Суммы отчислений из зарплаты табельного номера 1010. Рис2 - Отчисления табельных номеров 1в виде налога. Рис 3 - Суммы отчислений по различным статьям таб. номеров 1Рис 4 - Суммарные отчисления таб. номеров 1
Суммы отчислений из зарплаты | ||||
Таб. номер | Налоги | Штрафы | Кредит | Всего |
1010 | 26,8 | - | 14,4 | ? |
1011 | 15,8 | 9,00 | - | ? |
1012 | 13,3 | 8,50 | 50,80 | ? |
1013 | 21,4 | 3,00 | 17,8 | ? |
Итого | ? | ? | ? | ? |
Задание 6
1. Выполните таблицу, приведенную в задании. Заполните ячейки, где стоят знаки вопроса, путем автосуммирования, с помощью других автоматических кнопок или путем введения соответствующих формул.
2. После заполнения таблицы проиллюстрируйте ее с помощью прямоугольных и круговых диаграмм(двумерных и трехмерных). Каждый рисунок, содержащий диаграмму, должен включать легенду и соответствующие надписи на осях. Постройте диаграммы с соответствующими заголовками. Рис1 - Плановое распределение средств. Рис 2 - Плановые и фактические затраты на строительство школ. Рис 3 - Плановые и фактические затраты на строительство объектов. Рис 4 - Затраты на объекты в процентном отношении.
| |||||||
| Объекты | План | Факт | Процент |
| ||
| Школы | 1248 | 1107 | ? | |||
| Детские сады | 1144,8 | 1140,5 | ? |
| ||
| Магазины | 942,8 | 941,8 | ? |
| ||
| Бытовые услуги | 504,5 | 407,3 | ? |
| ||
| Итого | ? | ? | ? |
| ||
ЗАДАНИЕ
Начисление заработной платы
1. Создать таблицу Начисление заработной платы. Внести в нее данные на 10 сотрудников, количество детей которых колеблется от нуля до трех.
N | ФИО | Оклад | Профсоюзн. взнос | Пенсион. взнос | Подоход. налог | Кол-во детей | Сумма к выдаче |
Под таблицей сделать надпись: Размер детского пособия равен 82 р. (формат числа – денежный).
Профсоюзный и пенсионный взносы составляют 1% от оклада. Подоходный налог равен 12% от оклада. В случае, если имеются дети, то подоходный налог исчисляется в размере 12% от разницы
Оклад-Количество детей * Размер детского пособия - это формула для вычисления подоходного налога
Сумма к выдаче равна
(Оклад - налоговые вычеты) + пособия на всех детей.
2. Построить на отдельном листе диаграмму, определяющую долю зарплаты каждого сотрудника. Лист переименовать в Диаграмма (для этого дважды щелкнуть мышью на наименовании листа Лист2).
3. В таблицу добавить данные на двоих новых сотрудников. Посмотреть, меняется ли диаграмма.
4. Переименовать первый лист в Начисление.
5. На новом листе создать таблицу, содержащую столбцы
N ФИО Размер детского пособия
6. В столбце ФИО ввести формулу:
= щелкнуть на ячейке с первой фамилией на листе Начисление и нажать Enter.
7. Скопировать формулу во все ячейки новой таблицы.
8. Вычислить размер детского пособия для каждого сотрудника.
9. Изменить название листа на Детские.
10. На новом листе создать таблицу Сумма к выдаче
ФИО Сумма к выдаче
Все данные ввести в таблицу с листа Начисление
11. Отсортировать данные в алфавитном порядке фамилий (выделяя всю таблицу).
12. Изменить название листа на Ведомость.
13. На лист Ведомость перенести таблицы с листа Начисление, производя фильтрацию данных по количеству детей (0,1,2,3 ребенка).
Фильтрация данных производится следующим образом:
Выделить нужный столбец и выполнить команду Данные – Фильтр – Автофильтр. Щелкнуть на появившейся стрелке, дать команду Условие и задать условие, например количество детей равно 0.
14. Все таблицы на всех листах расчертить и соответствующим образом озаглавить.
