Лабораторная работа №9
«Создание и форматирование таблиц. Использование логических и математических функций в табличных вычислениях»
Цели работы:
1) научиться применять логические и математические функции для обработки данных;
2) научиться применять процентный формат к данным;
3) научиться выполнять сортировку данных;
4) закрепить навыки форматирования таблицы.
Задание:
1) изучите п.1 «Учебный материал»;
2) выполните задания, приведенные в п.2;
3) ответьте на контрольные вопросы (п.3).
Учебный материал
Использование логических функций необходимо, когда для выбора правильного решения нужно проверить выполнение одного или нескольких условий. Наиболее часто используемые функции этой категории – ЕСЛИ, ИЛИ, И, НЕ.
1. Функция ЕСЛИ
ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)
Эта функция позволяет осуществить выбор варианта вычислений. Если значение логического выражения истинно, то выбирается значение_истина; если ложно – выбирается значение_ложь.
2. Функция И
И (логическое значение1; логическое значение 2;…)
Результат функции – истина, если значения всех составных логических выражений истинны; в противном случае – ложь)
3. Функция ИЛИ
ИЛИ (логическое значение 1; логическое значение 2;…)
Результат функции – ложь только в том случае, если значения всех составляющих ложны; в остальных случаях – истина.
4. Функция НЕ
И (логическое значение)
Изменяет значение ЛОЖЬ на ИСТИНА и ИСТИНА на ЛОЖЬ.
Таблица истинности функций И, ИЛИ, НЕ представлена в таблице 3.
Таблица 3
Таблица истинности функций И, ИЛИ, НЕ
| x | y | И(x,y) | ИЛИ(x,y) | НЕ(х) |
| ЛОЖЬ | ЛОЖЬ | ЛОЖЬ | ЛОЖЬ | ИСТИНА |
| ЛОЖЬ | ИСТИНА | ЛОЖЬ | ИСТИНА | ИСТИНА |
| ИСТИНА | ЛОЖЬ | ЛОЖЬ | ИСТИНА | ЛОЖЬ |
| ИСТИНА | ИСТИНА | ИСТИНА | ИСТИНА | ЛОЖЬ |
|
|
Пример 1. Допустим в ячейке А1 имеется число 5, в ячейке А2 – 10, в ячейке А3 – 8. Выясним результаты вычислений некоторых функций (табл.4).
Таблица 4
Результаты вычислений логических функций
| № | Вид функции | Результат |
| =И(A1<2;А2>5) | Ложь | |
| =ИЛИ(A1<2;А2>5) | Истина | |
| =ЕСЛИ(A1<2;A2;A3) | ||
| =ЕСЛИ(И(A1<2;A2>5);A2;A3) | ||
| =ЕСЛИ(ИЛИ(A1<2;A2>5);A2;A3) | ||
| =ЕСЛИ(A1<2;A2;ЕСЛИ(A1>2;A3;A3+8)) |
Пример 2. Даны три положительных числа. Выяснить, образуют ли они треугольник (т.е. являются ли они сторонами треугольника).
 |
Занесем исходные данные задачи, как показано на рис.36. В ячейку D3 внесем формулу:
=ЕСЛИ(И(A3+B3>C3;B3+C3>A3;A3+C3>B3);"образуют треугольник"; "не образуют треугольник")
и скопируем в ячейки D4 и D5.
Рис.36. Исходные данные задачи и результат вычисления функции
Заполнение диалогового окна функции ЕСЛИ показано на рисунке 37, функции И – на рисунке 38.
Рис.37. Диалоговое окно функции ЕСЛИ
Рис.38. Диалоговое окно функции И
Другие примеры использования логических функций показаны в лабораторной работе №11.
Задание к лабораторной работе
1. Заполните таблицу по образцу в соответствии с вариантом задания, используя соответствующее форматирование ячеек:
а) выделите ячейки A1:L1 и выберите команду Объединить и поместить в центре. Введите заголовок таблицы (размер шрифта 9, начертание полужирный, тип шрифта Arial);
|
|
б) заголовки столбцов (строка 2) отформатируйте по следующим параметрам: размер шрифта 9, начертание полужирный, тип шрифта Times New Roman, выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам;
в) выделите границы ячеек A1:L13.
2. В диапазон ячеек F3:F12 внесите количество проданного товара (на свое усмотрение).
3. Вычислите, используя при необходимости стандартные функции:
а) общую стоимость представленного товара (E3:E12);
б) суммарную стоимость товара (E13);
в) остаток каждого товара после продажи (G3:G12);
г) общую стоимость остатка каждого наименования товара (H3:H12);
д) суммарную стоимость остатка товара в магазине (H13);
е) процент продаж от первоначального количества товара (установить процентныйформат одним из способов:
способ 1: вкладка Главная →группа Ячейки →команда Формат → Формат ячеек… →вкладка Число → Числовые форматы: Процентный;
способ 2: контекстное меню→ Формат ячеек… →вкладка Число → Числовые форматы: Процентный;
способ 3: кнопка Процентный формат 
в группе Число вкладки Главная;
способ 4: вкладка Главная →группа Стили →команда Стили ячеек → Числовой формат → Процентный);
ж) процент остатка от первоначального количества товара (установить процентныйформат);
з) новую цену товара за 1 шт. следующим образом: если % остатка больше % продаж, то цена товара за 1 шт. уменьшается на 20%, иначе цена остается прежней (K3:K12) (функция ЕСЛИ);
и) новую стоимость остатка каждого наименования товара (L3:L12);
к) общую стоимость остатка товара после изменения цены (L13);
|
|
л) суммарную стоимость товара, остаток которого меньше 50% (L14) (функция СУММЕСЛИ);
м) суммарную стоимость товара, остаток которого больше или равен 50% (L15) (функция СУММЕСЛИ);
4. Выполните проверку правильности расчетов, сделанных в ячейках L14 и L15: их сумма должна быть равна значению ячейки L13.
Вариант 1
Вариант 2
Вариант 3
5. Отсортируйте таблицу в порядке возрастания новой стоимости остатка (по столбцу L). Для этого установите курсор в любую ячейку диапазона L3:L12 и нажмите кнопку Сортировка от минимального к максимальному 
, находящуюся в группе Сортировка и фильтр вкладки Данные. Обратите внимание, что в столбце А изменились порядковые номера товаров.
6. В связи с произошедшими изменениями в таблице установите в столбце A нумерацию от 1 до 10 одним из способов, описанных в разделе «Построение рядов данных».
7. Сохраните файл на диске для его использования в следующих лабораторных работах.
3. Контрольные вопросы
1. Как объединить ячейки?
2. Как установить процентный формат числа (назовите 2-3 способа)?
3. Каково назначение функции СУММЕСЛИ?