Практическая работа 4. Формат ячейки. Границы и заливка. Числовой, процентный, денежный формат. Формат Дата и Время. Применение логических функций, ЕСЛИ, И, ИЛИ, НЕ в расчетах.

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

Для установки в ячейках необходимого обрамления (границ ячеек) и заливки цветом (затенения) можно использовать Главная + Шрифт + Границ, Заливки (цвета темы, авто) (При этом необходимо предварительно выделить ячейки).

Для установки любых параметров границ ячеек и необходимо выбрать Шрифт, и щелкнуть вкладку «Граница». Затем с помощью соответствующих кнопок установить необходимые параметры границ, в том числе тип линии, цвет и с каких сторон ячеек будут установлены границы.

Для установки любых параметров заливки надо выделить необходимые ячейки, а затем выбрать Шрифт, и щелкнуть вкладку «Заливки» (цвета темы, авто). На этой вкладке можно установить цвет фона и узор для выделенных ячеек.

Автоформат и кнопка «Формат по образцу»

Автоформат предоставляет 16 форматов таблиц, которые можно применить к диапазону ячеек. Для применения автоформата необходимо:

1. Выделить диапазон ячеек.
2. Выбрать Главная, Стили.
3. Необходимо отметить нужный формат в списке.
4. Указать расположение данных таблицы
5. Нажать Ok.

Очень удобно для копирования форматов использовать кнопку «Формат по образцу» (в виде «кисточки») на панели стандартной инструментов:

1. Выделить ячейку (или ячейки) с форматом, который необходимо скопировать и вставить на новом месте.
2. Щелкнуть кнопку «Формат по образцу». Указатель мыши примет вид «кисточки» с расположенным рядом с ней знаком плюс.
3. Перетащить курсор мыши через ячейки, к которым необходимо применить скопированный формат.

Можно копировать формат одновременно в несколько мест. Для этого надо щелкнуть кнопку «Формат по образцу» дважды. После этого курсор мыши будет иметь вид кисточки до тех пор, пока не будет нажата клавиша ESC.

Условное форматирование

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

1. Выделить ячейки, которые необходимо отформатировать.
2. Выбрать Главная + Стили + Условное форматирование.
3. В появившемся окне необходимо сформировать условие, согласно которому будут отбираться ячейки для применения условного формата и, нажав кнопку «Формат», установить сам формат, которым будут отмечены ячейки, удовлетворяющие условию.
4. Нажимая кнопку «А также», можно установить до 3-х условий и соответствующих им форматов. В условиях кроме значений, можно указывать формулы, возвращающие значение ИСТИНА либо ЛОЖЬ (см. лекцию 4). Для удаления условий следует использовать кнопку «Удалить».
5. После сформирования всех необходимых условий и форматов к ним следует нажать кнопку Ok.

Условные форматы можно копировать при помощи кнопки «Формат по образцу».

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

Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение если условие истинно, и другое  если оно ложно.

К логическим функциям относятся ЕСЛИ, И, ИЛИ, НЕ.

Функция ЕСЛИ. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется для условной проверки значений и формул.

Синтаксис:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_лож)

Лог_выражение − это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина − это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА. Значение_если_истина может быть другой формулой.

Значение_если_ложь − это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ. Значение_если_ложь может быть другой формулой.

Замечание: До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь, чтобы конструировать более сложные проверки, см. последний из приведенных ниже примеров.

И(логическое_значение1; логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ... − это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения.

Если аргумент, который является ссылкой или массивом, содержит тексты или пустые ячейки, то такие значения игнорируются.

Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!.

Функция ИЛИ. Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

ИЛИ(логическое_значение1;логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ... - это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Задание

1. Запустите программу Microsoft Excel. Сохраните этот файл с именем Урок4 Excel _ФИ в вашей папке.
2. На Листе 1 в ячейку А1 введите свою Фамилию и Имя, в ячейку А3 – текст «Дата рождения», в ячейку А4 – текст «Сегодня», в ячейку А5 – текст «Возраст», в ячейку А6 – текст «Количество прожитых дней». Данные в ячейках А1, А3 и А6 перенесите по словам.
3. В ячейку В3 введите дату своего рождения, а в ячейку В4 – функцию СЕГОДНЯ. В ячейке В5 просчитайте свой возраст (от функции СЕГОДНЯ необходимо отнять дату рождения и полученное число разделить на 365 дней в году). На данную ячейку наложить пользовательский формат «лет». В ячейке В6 просчитать количество прожитых дней за всю жизнь. На данную ячейку наложить пользовательский формат «дней».
4. Переименуйте 4 листа в вашей рабочей книге, назовите их:
1. Лист 1 —«Экономика»
2. Лист 2 —«Философия»
3. Лист 3 — «Культурология»
4. Лист 4 — «Начисление стипендии»

Листы Экономика (рисунок 2.6), Философия, Культурология, аналогичны друг другу и представляют собой экзаменационные ведомости, имеющие следующую структуру (Названия дисциплин соответствуют названиям листов):



Рисунок 2.6 − Лист Экономика

Лист Начисление стипендии (рисунок 2.7), представляет собой ведомость начисления стипендии в зависимости от среднего балла и имеет следующий вид:



Рисунок 2.7 − Лист Начисление стипендии

Рассчитайте начисление стипендии по следующему правилу:

Если 4<= Ср.балл студента<=5, то студент получает минимальную стипендию и к минимальной стипендии добавляется еще 50% от минимальной стипендии;

Если 3<Ср.балл студента <4, то студент получает минимальную стипендию и к минимальной стипендии добавляется еще 30% от минимальной стипендии;

Если Ср.балл студента <= 3, то студент получает только минимальную стипендию.

Примечание: В данной лабораторной работе следует использовать функции СРЗНАЧ для вычисления среднего балла по трем дисциплинам, а также логические функции ЕСЛИ и И — для вычисления стипендии

5. Сохраните книгу и закройте Microsoft Excel.
6. Вставьте в книгу дополнительный лист и назовите его Графики математических функций.
7. Построить график функции y=cos²(x) при x∈[0,1] с шагом 0,1. (Выполните пункты 11 - 14).
8. Посредством авто заполнения введем в ячейки A1:A11 значения 0; 0,1; 0,2; 0,3 … 1.
9. В ячейку B1 введите формулу = cos (A1)².
10. Скопируйте эту формулу в ячейки B2:B11.
11. При помощи мастера диаграмм постройте следующий график



12. Постройте график функции на промежутке x∈[0,1] с шагом 0,1.



Рисунок 2.8 - График функции y=cos²(x)

13. В ячейки A20:A30 вводим значения 0; 0,1; 0,2; ….; 1.
14. В ячейку B20 вводим формулу:
=ЕСЛИ(A20<0,5;(1+ABS(0,2-A20))/(1+A20+A20^2);A20^(1/3))
15. Скопируйте эту формулу в ячейки B21:B30.
16. При помощи мастера диаграмм постройте следующий график.



Рисунок 2.9 - График функции

17. Построить в одной системе координат графики двух функций: Y=2sin(x) и z=3cos(2x)-sin(x) при x∈[-3;0] Подсказка:
18. В столбец A, начиная с ячейки A40, внесем числа: -3; -2,8; -2,6 … 0.
В ячейках A39, B39, C39 внесем подписи: X, Y,Z.В ячейку B40 введем формулу: =2*SIN(A40).
19. В ячейку C40 введем формулу: =3*COS(2*A40)-SIN(A40). Построим график.
20. Сохраните книгу и закройте Microsoft Excel.

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

1. Какие существуют способы ввода формул в ячейку?
2. Как в ячейку ввести функцию?
3. Как задать автоформат выделенному диапазону ячеек?
4. Какие существуют способы форматирования ячеек?
5. Как наложить пользовательский формат на ячейку?
6. Как задать условное форматирование для выделенного диапазона ячеек?
7. Как в формуле сделать ссылку на другой лист?