Работа с формулами даты и времени. Работа с текстовыми формулами. Работа с формулами подсчёта и преобразования

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

Тема: Работа с формулами даты и времени. Работа с текстовыми формулами. Работа с формулами подсчёта и преобразования.

Цель: - научить использовать при работе с таблицами формулы даты и времени, текстовые формулы и формулы подсчёта и преобразования.

Вид работы: фронтальный

Время выполнения: 2 часа

Задания к практической работе

Задание 1:

1.     Запустите программу MS Excel.

2.     Оформите фрагмент листа, который при предварительном просмотре будет иметь (а, следовательно, и на бумаге) будет иметь вид:

▲ Первый столбец получен посредством объединения столбцов A, B и C в соответствующих номеров строк.

▲ Второй столбец получен посредством объединения столбцов D, E, F, G, H и I.

3.     В ячейку A2 введите формулу: =СЕГОДНЯ();

▲ Введя данную формулу, Вами определена текущая дата.

4.     В ячейку D2 введите формулу:

=ДАТА(ГОД(A2)+8;МЕСЯЦ(A2)+3;ДЕНЬ(A2)+20);

5.     В ячейку A4 введите формулу: =ТДАТА();

▲ Введя данную формулу, Вами определено текущее время.

6.     В ячейку D4 введите формулу: =A4+ВРЕМЯ(5;45;10);

7.     В ячейку A6 введите формулу: =ДАТА(ГОД(A2);6;23);

▲ Введя данную формулу, Вами определён конец учебного года.

8.     В ячейку D6 введите формулу: =ЧИСТРАБДНИ(A6;A2);

▲ Введя данную формулу, Вами определёно количество дней до конца учебного года.

9.     В ячейку D8 введите формулу: =ТЕКСТ(D4-A4;"ч:мм:сс").

▲ Введя данную формулу, Вами определена разница во времени, определённых в ячейках D4 и A4.

10.      В ячейке A10 введите дату своего рождения, используя ранее изученную функцию ДАТА;

11.      В ячейку D10 введите формулу: =СЕГОДНЯ()-A10. После нажатия клавиши Enterизмените в числовой формат на Общий: ГлавнаяЧисло  Числовой формат  Общий;

▲ Введя данную формулу, Вами определёно количество дней до Вашего дня рождения.

12.      В ячейку D11 введите формулу: =ТЕКСТ(A2;"дддд").

▲ Введя данную формулу, Вами определён день текущей недели.

13.      С учётом произвольного ввода данных и соответствующих текущих дат результатом послужит следующее:

14.      Измените регистр текста таблицы:

 В ячейку A1 введите формулу: =ПРОПИСН("Сегодняшняя дата");

▲ Введя данную формулу, Вами выполнено преобразование всех букв в прописные.

 В ячейку A3 введите формулу: =СТРОЧН("Текущее время");

▲ Введя данную формулу, Вами выполнено преобразование всех букв в строчные.

 В ячейку D5 введите формулу: =ПРОПНАЧ("Количество дней до конца учебного года");

▲ Введя данную формулу, Вами выполнено преобразование первых букв в прописные, а всех остальных – в строчные.

15.      Выполните объединение текста и чисел, введя в ячейку A12, которая получена после объединения диапазона ячеек A12:I13, посредством введения в неё следующей формулы: =СЦЕПИТЬ(D6;" стула");

16.      Осуществите пятикратное повторение знака «*» в ячейке A14, введя в неё следующую формулу: =ПОВТОР("*";5);

17.      Объедините диапазон ячеек B14:D14;

18.      В полученной ячейке получите строку, которая представляет собой выражение, в которой удалены шесть последних знаков из выражения определённого в ячейке D7, введя следующую формулу: =ЛЕВСИМВ(D7;ДЛСТР(D7)-6);

19.      Объедините диапазон ячеек E14:I14;

20.      В полученной ячейке получите строку, которая представляет собой выражение, в которой удалены восемь первых знаков из выражения определённого в ячейке D9, введя следующую формулу: =ПРАВСИМВ(D9;ДЛСТР(D9)-8);

21.      В ячейке A16 осуществите подсчёт количества ячеек, содержащих числа, предварительно введя следующую формулу: =СЧЁТ(A1:I15);

▲ Подсчёт осуществляется непосредственно в пределах области созданной Вами таблицы.

22.      В ячейке B16 осуществите подсчёт непустых ячеек, предварительно введя следующую формулу: =СЧЁТЗ(A1:I15);

▲ Подсчёт осуществляется непосредственно в пределах области созданной Вами таблицы.

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

=ЧСТРОК(A1:I15)*ЧИСЛСТОЛБ(A1:I15);

▲ Подсчёт осуществляется непосредственно в пределах области созданной Вами таблицы.

24.      Введите данные: в ячейку D16-10, в ячейку E16-5,а в ячейку F16-2;

25.      Осуществите преобразование времени:

 Преобразуйте указанное в ячейке D16 количество дней в часы, введя формулу:

=ПРЕОБР(10;"day";"hr");

 Преобразуйте указанное в ячейке E16 количество часов в минуты, введя формулу: =ПРЕОБР(5;"hr";"mn");

 Преобразуйте указанное в ячейке F16 количество лет в дни, введя формулу:

=ПРЕОБР(2;"yr";"day");

26.      Введите данные: в ячейку G16-120, в ячейку H16-132;

27.      Выполните преобразование величин:

 В ячейке G17 выполните преобразование, указанное в ячейке G16 количество градусов Цельсия в градусы Фаренгейта, введя формулу: =ПРЕОБР(G16;"C";"F");

 В ячейке H17 выполните преобразование, указанное в ячейке H16 количество километров в мили, введя формулу: =ПРЕОБР(H16;"km";"mi");

28.      В ячейку I16 введите число 16, а в ячейке I17 выполните преобразование этого числа в римское, введя в неё следующую формулу: =РИМСКОЕ(I16);

29.      После выполнения пунктов 14 – 28 результатом должно послужить следующее:

Задание 2. Создайте таблицу Результаты тестирования, рассчитайте средний показатель  тестирования для каждого сотрудника.

1.     Создайте таблицу, содержащую следующие поля:

2.     Заполните таблицу данными. Поля № п/п и Фамилия заполните, воспользовавшись функцией автозаполнения. Для заполнения фамилий MS Excel будет использовать список, созданный вами в 1 лабораторной работе. Проставьте результаты тестирования (рис. 1).

Рисунок 1 - Таблица результаты тестирования

3.     Рассчитайте Средний показатель тестирования каждого сотрудника. Для этого:

§ Выделите пустую ячейку в поле Средний показатель напротив фамилии первого сотрудника.

§ Нажмите кнопку Вставить функцию  в строке ввода или на панели Формулы.

§ В окне диалога Мастер функций выберите Статистические в списке Категория. Выберите СРЗНАЧ в списке Функций. Нажмите кнопку ОК (рис. 2).

Рисунок 2 - Диалоговое окно Мастер функций

§ В появившемся диалоговом окне укажите диапазон ячеек (в нашем примере: диапазон – C2:F2), среди которых надо найти среднее значение и нажмите ОК.

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

При помощи логической функции Если определите прошли или не прошли сотрудники тестирование. Если средний показатель за тестирование меньше 60 % сотрудник тестирование не прошел.

4.      Добавьте к таблице столбец с заголовком Прошел/Не прошел тестирование.

5.     Установите курсор в пустую ячейку напротив первого сотрудника. Вызовите Мастер функций: в списке Категория выберите Логические, в списке Функций функцию Если (или выберите категорию Логические непосредственно на панели Формул).

6.     В диалоговом окне Аргументы функции (рис. 3) установите: Лог_ выражение: G2>60%; Значение_если_истина: тест прошел; Значение_если_ложь:  тест не прошел. Нажмите ОК.

Рисунок 3 - Диалоговое окно Аргументы функции

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

Рассчитайте размер премии для каждого сотрудника, исходя из коэффициента и расчетной суммы премии в 1 000 рублей.

Коэффициент проставляется в зависимости от среднего показателя за тестирование: если средний показатель до 60 % (т.е. сотрудник не сдал тест), то коэффициент 0, от 60% до 70% коэффициент 1, от 70% до 80% коэффициент 1,2, выше 80 % - 1,5.

8.     Добавьте к ранее созданной таблице поле Коэффициент. Проставьте коэффициенты для каждого сотрудника, исходя из результатов тестирования.

9.     Добавьте к таблице поле Премия. Отступив одну ячейку от таблицы запишите  Расчетная сумма для премии и в пустую ячейку впишите сумму, например 1000.

10. Для первого сотрудника в таблице рассчитайте премию по формуле расчетная сумма * коэффициент, используя абсолютные и относительные ссылки на ячейки. Относительная ссылка задается адресом ячейки, абсолютная ссылка содержит в записи адреса значок  $ перед буквой столбца и цифрой строки. Например: относительная ссылка – A2, D4; абсолютная ссылка - $A$2; $D$4. В формуле для расчета премии относительная ссылка задается для ячейки, содержащей коэффициент, а абсолютная – для адреса ячейки с расчетной суммой. Для нашего примера установите курсор в ячейку J2 и наберите: =$L$2*I2 (рис. 4).

Рисунок 4 - Таблица тестирования сотрудников

11. Воспользовавшись функцией автозаполнения,  скопируйте полученную формулу  для расчета премии остальных сотрудников. Обратите внимание, что ссылка на ячейку с абсолютной адресацией при копировании не изменяется.

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

§ Выделите пустую ячейку под последним значением столбца Премия

§ Нажмите на кнопку Автосуммирование  на панели Главная или Формулы.

§ Проверьте, правильно ли выделен диапазон ячеек для суммирования или выделите нужный диапазон мышью

§ Нажмите Enter.

13. Сохраните результаты проделанной работы в своей папке под названием Практическая работа  2.