Лабораторная работа 3. лаб. 3 Антонов.М 1П-20. Лабораторная работа 3 Тема Использование формул и операций для проработки информации, поданной в таблице. Ссылки. Организация работы с несколькими листами одной рабочей книги в Excel

• Название: Лабораторная работа 3 Тема Использование формул и операций для проработки информации, поданной в таблице. Ссылки. Организация работы с несколькими листами одной рабочей книги в Excel
• Анкор: Лабораторная работа 3
• Дата: 11.01.2022
• Размер: 1.5 Mb.
• Имя файла: лаб. 3 Антонов.М 1П-20 .doc
• Тип: Лабораторная работа; #328742

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

Тема: Использование формул и операций для проработки информации, поданной в таблице. Ссылки. Организация работы с несколькими листами одной рабочей книги в Excel.

Цель работы:Ознакомление с понятием формулы. Определение ссылок. Использование ссылок при работе с формулами. Ознакомление с общими сведениями об управлении листами рабочей книги, удалении и переименовании листов; составление формул, содержащих адреса ячеек на разных листах рабочей книги; самостоятельное составление таблицы на основе данных из другой таблицы, расположенной на другом листе рабочей книги.

Ход работы

Задание (1.1)

1. 
   Создайте заготовки таблицы самостоятельно (см. образец на рис.1).
   
2. 
   Заголовок размещен в двух строках таблицы, применен полужирный стиль начертания шрифта, весь текст выровнен по центру, а «Налоги» – по центру выделения;
   
3. 
   Измените ширину столбца (в зависимости от объема вводимой информации) и обрамление таблицы. В данном случае использовано сложное обрамление, когда снята часть рамок. Важно по предложенному образцу определить реальное положение ячеек и выполнить соответствующее обрамление, выделяя различные блоки ячеек.
   
4. 
   Заполните заголовки столбцов таблицы по рис.1. В ячейку А1 наберите №, в В1 – «Фамилия, имя, отчество», в С1 – «Оклад», в D1 – «Налоги», в G1 – «Сумма к выдаче», в Н1 – «Число детей». Теперь выделите ячейки А1 и А2 и выполните команду ФорматЯчейки, затем во вкладке Выравнивание активизируйте переключатели Переносить по словам и Объединение ячеек и нажмите ОК. Проделайте точно такие же действия с ячейками (В1, В2), (С1,С2), (G1,G2), (Н1,Н2).
   
5. 
   Выделите ячейки D1:F1 и выполните команду ФорматЯчейки, затем во вкладке Выравнивание в группе выравнивание по горизонтали выберите по центру выделения и нажмите ОК.
   
6. 
   В ячейку D2 наберите «проф.», в ячейку E2 – «пенс.», в ячейку F2 – «подоход.».
   

7. 
   Заполните ячейки столбца А последовательностью чисел 1, 2, …,10.
   
8. 
   Задайте формат числа «денежный» для ячеек, содержащих суммы. Можно сделать это до ввода данных в таблицу: выделите соответствующие ячейки и установите для них формат числа «денежный».
   
9. 
   Теперь заполните столбцы D и E формулами для расчета профсоюзного и пенсионного налогов. Примем профсоюзный и пенсионный налоги, составляющими по 1% от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Самое важное – не забыть про абсолютные ссылки, так как профсоюзный и пенсионный налоги нужно брать от оклада, т.е. ссылаться только на столбец С «Оклад». Примерный вид формулы: =$С3*1% или =$С3*0,01 или =$С3*1/100. После ввода формулы в ячейку D3 ее нужно распространить вниз (протянув за маркер выделения) и затем вправо на один столбец. Теперь формулами уже заполнены столбцы D и E.
   
10. 
    Столбец F следует заполнить формулой для расчета подоходного налога. Подоходный налог вычисляется по формуле: 12% от Оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(С3-Е3-110)*12% или =(С3-Е3-110)*12/100 или =(С3-Е3-110)*0,12. После ввода формулы в ячейку F3, ее нужно распространить вниз.
    
11. 
    Для подсчета Суммы к выдаче примените формулу, вычисляющую разность оклада и налогов. Примерный вид формулы в ячейке G3: =С3-D3-E3-F3, которую следует распространить вниз столбца.
    
12. 
    Заполните столбцы «Фамилия, имя, отчество», «Оклад» и «Число детей» после того, как ввели все формулы. Результат будет вычисляться сразу же после ввода данных в ячейку. При желании можно воспользоваться режимом формы для заполнения таблицы. Заполнение таблицы можно производить следующим образом.
    
13. 
    Наберите любые несколько фамилий (сотрудников предприятия) в произвольном порядке или см. образец заполненной таблицы на рис.2.
    
14. 
    В столбце С наберите оклады сотрудников согласно штатному расписанию (надерите любые числа или см. образец заполненной таблицы на рис.2).
    
15. 
    В последнем столбце «Число детей» проставьте любые числа от 0 до 3.
    

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



Задание 1_2.

1. 
   Прейдите с помощью мыши на Лист 2. Переименуйте его в «Детские». На этом листе создайте ведомость на получение компенсации на детей на основе таблица начислений. Подготовьте пустую таблицу, состоящую из трех столбцов.
   
2. 
   Наберите заголовки столбцов: в ячейку А1 – «ФИО», в ячейку В1 – «Сумма», в С1 – «Подпись» (см. образец на рис.3). Выполните обрамление таблицы.
   
3. 
   Список фамилий внесите автоматически с помощью связи межу листами. Для этого выделите ячейку А2 листа «Детские» и введите в нею формулу: =Начисления!В3, где имя листа обозначается восклицательным знаком, а В3 – адрес ячейки, в которой размещена первая фамилия сотрудника на листе «Начисления». Эту формулу можно не набирать в ручную, а перейти на лист «Начисления», выделит мышью ячейку, содержащую первую фамилию списка и нажмите Enter. Вернитесь на лист «Детские» и распространите полученную формулу вниз столбца А.
   
4. 
   В ячейке В2 аналогичным образом поместите формулу: =Начисления!Н3* 53.10, где Н3 – адрес первой ячейки на листе «Начисления», содержащей число детей. Заполните этой формулой остальные ячейки столбца В. Примените к ним денежный формат числа.
   
5. 
   Для того, чтобы список в таблице на листе «Детские» состоял только из сотрудников, имеющих детей, установите фильтр по наличию детей. Для этого выполните команду ДанныеФильтрАвтофильтр и в раскрывающемся списке «Сумма» выберите критерий (Условие). В левом верхнем поле окна пользовательского автофильта выберите больше или равно, а в верхнем правом поле выберите минимальное ненулевое число из списка (в нашем случае это 53,13). Нажмите ОК.
   
6. 
   Вставьте в самом верхе дополнительную строку и наберите заголовок этого листа: «Ведомость на получение детской компенсации». В окончательном варианте отфильтрованная таблица должна иметь следующий вид:
   

Задание (1.3)

Самостоятельно оформите третий лист вашей рабочей книги. На этом листе создайте ведомость для выдачи заработной платы. Таблица должна содержать следующие столбцы: «ФИО», «Сумма к выдаче», «Подпись» (см. образец на рис. 4).



Задание 2

Создать таблицу, посчитать сумму единица измерения гривна.

3. Скопировать таблицу на Лист2 и назвать лист ИЗМЕНЕНИЕ РАСЧЁТОВ.

4. Произвести изменения как показано ниже. И посчитать результаты






Упорядочить таблицу в алфавитном порядке.



Задание 3. Подготовьте таблицу по образцу, поместив ее в ячейках A1 – E3.

В ячейке В3 находится цена за единицу товара.

В ячейке C3 - формула =B3 * C2 (цена за единицу товара умножить на количество).



Задание 4. Составить таблицу оплаты электроэнергии по образцу:

1. 
   Создайте новую таблицу и сохраните в папке со своей фамилией с именем ОПЛАТА.
   
2. 
   Внесите исходные данные и расчетные формулы
   
3. 
   Отформатируйте столбцы.
   
4. 
   Запишите формулы в отчет.
   

Правило для расчетов оплаты за месяц:

• 
  от очередного значения показания счетчика отнять предыдущее;
  
• 
  разницу умножить на стоимость 1 кВт (ссылка на ячейку должна быть абсолютной*).
  

*- абсолютные ссылки ссылаются всегда на одну и ту же ячейку ($).

*- относительные ссылки изменяются в соответствии с новым положением формулы.

5. 
   Обрамите таблицу так, как показано на образце.
   
6. 
   Установите денежный формат в столбце Оплата за месяц.
   
7. 
   Сохранить таблицу под именем Оплата.
   

Задание 5. Подготовьте таблицу для начисления пени в соответствии с образцом.


Задание 6. Расчёт семейного бюджета:

1. 
   Назвать лист Бюджет.
   
2. 
   Назвать книгу СЕМЬЯ.
   
3. 
   Первая строка пустая, высота 20.
   
4. 
   Создать строки: приход, питание, коммунальные платежи, телефон, проезд, одежда, предметы санитарии и гигиены, непредвиденные расходы, остаток (шрифт 12, высота 40).
   
5. 
   Создать столбцы (январь, февраль, март, апрель, май, июнь, июль, август, сентябрь).
   
6. 
   В приход внести значения по месяцам (2500 3000 2800 2950 3020 2800 2650 2500 2900руб).
   
7. 
   Расчёт питания произвести по формуле: 40% от заработанных денег.
   
8. 
   Ввести формулу в ячейку B3, а затем произвести копирование в ячейки C3 – J3.
   
9. 
   Ввести значения коммунальных платежей по формуле 15% от зарплаты в ячейку В4 и скопировать с относительной ссылкой в ячейки С4 – D4. Показать возможность копирования выделением диапазона с помощью мышки.
   
10. 
    Заполнить все ячейки оплаты за телефон значением 15 руб.
    
11. 
    Заполнить значения оплаты за проезд по формуле 24 раб. дня* 3 руб.+6(7) выходных*15 руб. (в зависимости от количества дней в месяце, считать в феврале 23 раб. дня и 5 выходных).
    
12. 
    Вычислить затраты на одежду в месяц по формуле (приход-питание- коммунальные платежи- телефон- проезд)*30%, предварительно назвать необходимые строки именами и скопировать формулу во все рабочие ячейки строки.
    
13. 
    Предметы санитарии и гигиены высчитать по формуле (приход-питание- коммунальные платежи- телефон- проезд)*10%, предварительно скопировать предыдущую формулу в ячейку В8, изменить и заполнить всю строку.
    
14. 
    Ячейки «непредвиденные расходы» не заполнять.
    
15. 
    Остаток за месяц заполнить по формуле приход-питание- коммунальные платежи- телефон- проезд – одежда-B8-B9.
    
16. 
    Назвать Лист2 «Одежда», где накапливать суммы выделенные на одежду ежеквартально.
    
17. 
    Создать таблицу, аналогичную таблице2.
    
18. 
    В B2 записать суммы значений строки одежда ( в сумме по 3 значения) листа Бюджет.
    
19. 
    Использовано заполнить по таблице.
    
20. 
    Остаток рассчитать по формуле Выделено – Использовано.
    
21. 
    Общий остаток рассчитать по формуле B4+C4+D4.
    
22. 
    Лист 3 назвать Остаток.
    
23. 
    Создать таблицу, записать формулы и значения (копировать с листов Бюджет и Одежда)
    

Лист Бюджет


Лист одежда


Лист остаток


Ответы на вопросы:

1. Как можно определить, какой из рабочих листов является активным?

Ответ: Щелкая по ярлычкам, можно переходить от листа к листу внутри рабочей книги. Ярлычок активного листа выделяется цветом

2. Каким образом осуществляется перемещение с одного листа на другой в пределах одной рабочей книги?

Ответ: Откройте книгу, в которую требуется переместить лист.

В меню Окно щелкните книгу, в которую входит перемещаемый лист. ...

В меню Правка выберите пункты Лист > Переместить или скопировать лист. В меню

В книгу выберите книгу, в которую требуется переместить лист.

3. С помощью какой последовательности команд можно вставить новый рабочий лист?

Ответ :  Excel можно легко вставлять, переименовывать и удалять листы книги. ... также на вкладке Главная нажать кнопку Вставить и выбрать команду Вставить лист.

4. Как удалить один или несколько рабочих листов?

Ответ: Вы можете быстро выбрать один или несколько листов, щелкнув по ярлычкам листов в нижней части окна Excel.
5. Опишите способ переименования рабочего листа?.

Ответ:

-Дважды щелкните ярлычок листа и введите новое имя.

-Щелкните ярлычок листа правой кнопкой мыши, выберите команду Переименовать и введите новое имя.

-Нажмите клавиши ALT+H,O,R и введите новое имя.

6. Какой вид имеет относительная ссылка на ячейку с другого рабочего листа?

Ответ: Когда формула будет иметь следующий вид: =Лист1!B2+Лист2!B2+Лист3!B2, нажмите Enter. Результат должен получиться такой же, как на рисунке.

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

Ответ: ссылка на ячейку указывает на ячейку или диапазон ячеек листа. Ссылки можно применять в формула, чтобы указать приложению Microsoft Office Excel 

8. Каким образом можно установить размеры и ориентацию листа?

Ответ: Откройте вкладку Макет страницы. В группе Параметры страницы в раскрывающемся меню Ориентация выберите вариант Книжная или Альбомная.

9. Какие форматы числа вы знаете? Перечислите их. Для чего они применяются и в чем их отличия?

Ответ: xls — это формат всех файлов версии Excel 2003 и раньше. Он имеет меньше возможностей, работает медленнее. Файл с расширением *. xlsx — это формат подавляющего большинства файлов Excel на настоящий момент, используется начиная с версии Microsoft Office 2007.

10. По какому принципу можно отсортировать текстовые записи или числовые данные? В каком пункте меню документа находится команда сортировки?

Ответ: Сортировка от А до Я). Чтобы быстро отсортировать значения по убыванию, нажмите Команда "От Я до А" в Excel позволяет сортировать текст по алфавиту и в обратном порядке

11. Каким образом вводятся формулы в ячейку?

Ответ: Выделить вычислимую ячейку, в которую предполагается ввести некоторую формулу

12. Какие виды ссылок вы знаете?

• 
  Ответ: Абсолютные ссылки. Они однозначно указывают адрес ячейки, на которую ссылаются. ...
  
• 
  Относительные ссылки. Такие ссылки запоминают свое положение относительно источника и при копировании так же, изменяют свои координаты. ...
  
• 
  Смешанные ссылки.
  

14) Назовите способы создания абсолютной ссылки.

Ответ: При создании формулы Вы можете нажать клавишу F4 на клавиатуре для переключения между относительными и абсолютными ссылками. Это самый простой и быстрый способ

Выполнил: Лысенко А. Г.