Настройка Excel

• Название: Настройка Excel
• Дата: 23.12.2021
• Размер: 1.21 Mb.
• Имя файла: MS Excel.docx
• Тип: Практическая работа; #315484
• страница: 2 из 4

Тема. Мастер функций в MS Excel.

Цель. Приобрести и закрепить практические навыки по применению функций категории Дата и время с использованием Мастера функций.

Задание. Создать таблицу, показанную на рисунке.

	А	В	С
1	Функции Дата и время
2	Начало работы
3	Системная (текущая) дата и время
4	Сегодня
5	Конец месяца
6	Конец года
7	Осталось до конца месяца
8	Осталось до конца года
9
10	День рождения
11	Прожил дней
12	Сегодня + 100 дней
13	Сегодня + 365 дней

Алгоритм выполнения задания.

1. 
   Записать указанный текст обозначений в столбец А.
   
2. 
   В ячейку В2 записать дату и время своей работы строго соблюдая формат, например, 15.01.07 10:15 (т.е. 15 января 2007 года 10 часов 15 минут)
   
3. 
   В ячейку В3 вставить текущую дату с помощью Мастера функций:
   

3.1. Выделить ячейку В3, щёлкнуть значок f_x на панели инструментов Стандартная или выполнить команду Вставка/Функция.

2. 
   В диалоговом окне Мастер функций в левом поле Категория выбрать Дата и время, в правом поле Функция найти и выбрать ТДАТА, нажать Ок и ОК.
   
3. 
   В ячейку В4 вставить текущую дату с помощью Мастера функций, выбрав функцию СЕГОДНЯ.
   
4. 
   В ячейки В5 и В6 записать даты конца месяца и конца года, например, 31.01.07 и 31.12.07.
   
5. 
   В ячейку В7 записать формулу =В5-В4 (получим разность в формате ДД.ММ.ГГ).
   
6. 
   В ячейку В8 записать формулу =В6-В4 (получим разность в формате ДД.ММ.ГГ).
   

Примечание. Программа некорректно обрабатывает количество месяцев, завышая его на единицу.

3. 
   В ячейку В10 записать дату своего дня рождения, например, 29.12.90.
   
4. 
   Вычислить число прожитого времени по формуле =В4-В10 (в формате ДД.ММ.ГГ и учётом примечания).
   
5. 
   Вычислить даты в ячейках В12 и В13, самостоятельно записав нужные формулы.
   
6. 
   Преобразовать дату в ячейке В13 в текстовый формат, для этого:
   

11.1. Выделить ячейку В13, выполнить команду Формат/Ячейки/Число.

11.2. В диалоговом окне в поле Числовые форматы выбрать Дата, в поле Тип выбрать формат вида «14 март, 2001», нажать ОК.

3. 
   Скопировать диапазон ячеек В4:В6 в диапазон С4:С6, для этого:
   

12.1. Выделить диапазон В4:В6.

12.2. Щелкнуть кнопку Копировать на панели инструментов Стандартная или выполнить команду Правка/Копировать.

12.3. Выделить ячейку С4, щёлкнуть кнопку Вставить на панели инструментов Стандартная или выполнить команду Правка/Вставить.

3. 
   Преобразовать формат даты в ячейке С6 в текстовый, выполнив команду Формат/Ячейки/Число и выбрав Тип «Март 2001».
   
4. 
   Преобразовать формат даты в ячейке С5 в текстовый, выполнив команду Формат/Ячейки/Число и выбрав Тип «14 мар».
   
5. 
   Преобразовать формат даты в ячейке С4 в текстовый, выполнив команду Формат/Ячейки/Число и выбрав Тип «14 мар 01».
   
6. 
   Установить в ячейке С3 отображение секундомера системных часов, для этого:
   

1. 
   Выделить ячейку С3, щёлкнуть значок f_x на панели инструментов Стандартная или выполнить команду Вставка/Функция.
   
2. 
   В диалоговом окне Мастер функций в левом поле Категория выбрать Дата и время, в поле Функция найти и СЕКУНДЫ, нажать ОК.
   
3. 
   В диалоговом окне СЕКУНДЫ ввести в поле Дата_как_число адрес В3, ОК.
   
4. 
   Значения секунд в ячейке С3 будут изменяться при нажатии клавиши F9.
   
16. 
    Вычислить длительность выполнения работы, для этого:
    
    1. 
       Выделить ячейку С2, записать формулу =В3-В2, нажать Enter, результат будет записан в формате ДД.ММ.ГГ ЧЧ:ММ.
       
    2. 
       Преобразовать значение в ячейке С2 в формат ЧЧ:ММ:СС, для этого:
       
       1. 
          Выделить ячейку С2, выполнить команду Формат/Ячейки/Число.
          
       2. 
          В поле Числовые форматы выбрать (все форматы).
          
       3. 
          В поле Тип выбрать [ч]:мм:сс, нажать ОК.
          
       4. 
          Значения секунд в ячейке С2 будут изменяться при нажатии клавишиF9.
          
17. 
    Сравнить вычисленные значения с показанием системных часов на Панели задач.
    

Контрольные вопросы

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

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

Тема. Мастер функций в MS Excel.

Цель. Приобрести и закрепить практические навыки по применению функций категории Логические с использованием Мастера функций.

Задание. Создать таблицу, показанную на рисунке.

	А	В	C L С	D	E
1	Ведомость начисления заработной платы
2	№ п/п	Фамилия	Оклад	Материальная помощь	Сумма к выдаче
3	1	Сидоров	1850
4	2	Петров	1000
5	3	Глухов	2300
6	4	Смирнов	950
7	5	Галкин	1100
8	6	Иванов	4500
9	7	Авдеев	3400
10	8	Горшков	2800
11		Всего:

Алгоритм выполнения задания.

1. 
   В ячейке А1 записать название таблицы.
   
2. 
   В ячейках А2:Е2 записать шапочки таблицы с предварительным форматированием ячеек, для этого:
   
   1. 
      Выделить диапазон ячеек А2:Е2.
      

Выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.

2. 
   Установить переключатель «переносить по словам».
   
3. 
   В поле «по горизонтали» выбрать «по центру».
   
4. 
   В поле «по вертикали» выбрать «по центру».
   
5. 
   Набрать тексты шапочек, подбирая по необходимости ширину столбцов вручную.
   
1. 
   Заполнить графы с порядковыми номерами, фамилиями, окладами.
   
2. 
   Рассчитать графу Материальная помощь, выдавая её тем сотрудникам, чей оклад меньше1500 руб., для этого:
   
   1. 
      Выделить ячейку D3, вызвать Мастер функций, в категории Логические выбрать функцию ЕСЛИ.
      
   2. 
      В диалоговом окне функции указать следующие значения:
      

Логическое выражение	С3<1500
Значение_если_истина	150
Значение_если_ложь	0

3. 
   Скопировать формулу для остальных сотрудников с помощью операции Автозаполнение.
   
1. 
   Вставить столбец Квалификационный разряд.
   
   1. 
      Выделить столбец Е, щёлкнув по его заголовку.
      
   2. 
      Выполнить команду Вставка/Столбцы.
      
   3. 
      Записать шапочку Квалификационный разряд.
      
   4. 
      Заполнить этот столбец разрядами от 7 до 14 произвольно так, чтобы были все промежуточные разряды.
      
2. 
   Вставить и рассчитать столбец Премия, используя логическую функцию ЕСЛИ, выдавая премию в размере 20% оклада тем сотрудникам чей разряд выше 10.
   

Логическое выражение	Е3>10
Значение_если_истина	С3*0,2
Значение_если_ложь	0

7. 
   Рассчитать графу Сумма к выдаче так, чтобы в сумму не вошёл Квалификационный разряд.
   
8. 
   Рассчитать итоговые значения по всем столбцам, кроме столбца Квалификационный разряд.
   
9. 
   Проверить автоматический перерасчёт таблицы при изменении значений:
   
   1. 
      Изменить оклады нескольким сотрудникам, проверить изменение таблицы.
      
   2. 
      Изменить квалификационные разряды нескольким сотрудникам.
      
10. 
    Изменить условие начисления премии: если Квалификационный разряд выше 12, то выдать Премию в размере 50% оклада.
    

Контрольные вопросы

1. 
   Для решения каких задач используется логическая функция ЕСЛИ?
   
2. 
   Как реализуются функции копирования и перемещения в Excel?
   
3. 
   Как можно вставить или удалить строку, столбец в Excel?
   

Практическая работа №6
Тема. Абсолютный адрес в MS Excel.

Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.

Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.

	A	B	C	D	E
1	Распределение доходов в зависимости от КТУ
2	Общий доход	10000
3	Фамилия	Время, ч	Квалификационнй разряд	КТУ	Сумма к выдаче
4	Сотрудник 1	5	10
5		10	12
6		12	18
7		8	5
8		15	10
9		7	8
10		20	9
11		10	6
12		8	15
13		16	10
14	Итого

Алгоритм выполнения задания.

1. 
   Записать исходные значения таблицы, указанные на рисунке.
   
2. 
   Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
   
3. 
   Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
   
4. 
   Подсчитать значение Итого с помощью операции Автосумма.
   
5. 
   Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
   
6. 
   При выполнении операции Автозаполнение в графе Сумма к выдаче появляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3*D5/D15.
   
7. 
   Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
   
   1. 
      Выделить ячейку Е4.
      
   2. 
      В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
      
   3. 
      Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
      
   4. 
      В ячейке Итого должна получиться сумма, равная Общему доходу.
      

Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.

8. 
   Денежный, установить в поле Обозначение тип р.
   
9. 
   Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
   
10. 
    Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
    

/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.

	A	B	C	D	E
1	Стоимость программного обеспечения
2	Наименование	Стоимость, $	Стоимость, р.	Стоимость, Евро	Доля в общей стоимости, %
3	OC Windows	18
4	Пакет MS Office	32
5	Редактор Corel Draw	15
6	Графический ускоритель 3D	22
7	Бухгалтерия 1С	50
8	Антивирус DR Web	20
9	Итого	157
10	Курс валюты (к рублю)	28		35

Алгоритм выполнения задания.

1. 
   Записать исходные текстовые и числовые данные.
   
2. 
   Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
   
3. 
   Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.
   
4. 
   Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
   
5. 
   Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения:
   
   1. 
      Выделить числовые значения этой графы.
      
   2. 
      Щёлкнуть по кнопке Процентный формат.
      
   3. 
      Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
      

Контрольные вопросы

1. 
   Для чего используются абсолютные и относительные адреса ячеек?
   
2. 
   В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?
   

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