Полная версия

Главная arrow Информатика arrow Базовые и прикладные информационные технологии

  • Увеличить шрифт
  • Уменьшить шрифт


<<   СОДЕРЖАНИЕ ПОСМОТРЕТЬ ОРИГИНАЛ   >>

Логические операции в Excel

При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов, в зависимости от выполнения одного или нескольких условий. Тогда следует применить логические функции, нажав Формулы —> Библиотека функций —> Логические. Далее из списка (рис. 2.20) следует выбрать необходимое условие.

Список логических функций

Рис. 2.20. Список логических функций

Например, дана задача: продавец бакалейной палатки ежедневно составляет отчет о продажах, для чего он использует таблицу, подготовленную в Excel (рис. 2.21).

Предположим, что товар отпускается килограммами и цена его задана в руб/кг. На самом деле торговая точка может продавать товар и килограммами, и тоннами, и штуками (пачками), а цена может задаваться и в руб/кг, и в руб/шт, и в других единицах. Рассмотрим эту ситуацию.

Пример 1. Предположим сначала, что количество товара в таблице задается либо в килограммах, либо тоннах, а цена — в рублях за 1 кг. Для правильного расчета стоимости в этом случае необходимо установить, в каких единицах она задана, и в зависимости от результата

Отчет о продажах в таблице Excel 2007

Рис. 2.21. Отчет о продажах в таблице Excel 2007

использовать ту или иную формулу. Для решения таких задач применяют условную функцию ЕСЛИ. Эта функция имеет формат:

ЕСЛИ (Логическое выражение^ <выражение1>, <выражение 2>)

Первый аргумент функции ЕСЛИ — логическое выражение (в частном случае — условное выражение), которое принимает одно из двух значений: «истина» или «ложь» (1 или 0). В первом случае ЕСЛИ возвращает значение <выражения1>, а во втором — значение выражения 2>. В качестве <выражения1> или <выражения 2> можно записать не только арифметическое выражение, но и вложенную функцию ЕСЛИ, а также строку символов. Таким образом, ЕСЛИ может возвратить не только число, но и строку символов.

Продолжим рассмотрение примера 1. Если количество задано в килограммах, цена — в рублях, то стоимость (выручка) С = Q * Ц, где Q — количество (кг), Ц — цена (руб./кг). Если цена задана в долларах, стоимость рассчитывается по формуле С = Q * Ц/30 (1$ = 30 руб.).

Пусть в ячейке Е2 помещается код единицы измерения количества продукции, который принимает значения: 1 — (руб); 2 — ($).

В ячейке С2 помещается количество продукта, в ячейке В2 — цена (руб./кг). В ячейку D2 необходимо поместить стоимость товара. Тогда в эту ячейку мы можем записать функцию: =ЕСЛИ (E2=l, В2* С2, В2/30*С2).

Здесь логическое выражение Е2=1 (в данном случае это просто условное выражение). Если в Е2 записана 1, условие выполнено и значение этого выражения равно «истине». Поэтому функция ЕСЛИ, записанная в ячейке D2, принимает значение В2*С2. Если значение логического выражения — «ложь» (Е2 не равно 1), функция ЕСЛИ возвратит значение третьего аргумента, т. е. В2/30*С2. Для вычислений используем цепочку Формулы —» Логические —» ЕСЛИ и далее диалоговое окно (рис. 2.22—2.24).

Здесь мы намеренно допустили небрежность, подменив условие задачи: цена будет делиться на 30 не при Е2 = 2, а при Е2 не равно 1. Если пользователь ошибается и запишет в ячейке Е2 не 2, а 3 или 11, Excel все равно будет считать, что цена товара рассчитывается в $.

Окно аргументов логической функции

Рис. 2.22. Окно аргументов логической функции

Расчет выручки в рублях

Рис. 2.23. Расчет выручки в рублях

Расчет выручки в долларах

Рис. 2.24. Расчет выручки в долларах

Поэтому на место третьего аргумента надо записать вложенную функцию ЕСЛИ:

В этом случае, если Е2 <> 1, функция вернет значение вложенной функции ЕСЛИ, которое в свою очередь зависит от выполнения условия Е2=2. Если это условие выполнено (т. е. единица измерения — $), значением функции будет В2/30*С2, если нет — значением функции будет нуль. Иными словами, нулевая стоимость товара означает, что в Е2 неправильно указан код единицы измерения (он не равен ни 1, ни 2).

Если условий много, записывать вложенные функции ЕСЛИ становится неудобно. В этом случае на месте логического выражения в ЕСЛИ можно указать одну из двух логических функций: И (AND) или ИЛИ (OR). Формат функций одинаков:

Функция И принимает значение «истина», если одновременно истинны все выражения, указанные в качестве аргументов этой функции. В остальных случаях значение И — «ложь».

Функция ИЛИ принимает значение «истина», если истинно хотя бы одно из логических выражений указанных в качестве аргументов этой функции. В остальных случаях значение ИЛИ — «ложь».

Пример 2. Усложним постановку задачи из примера 1. Пусть в торговой точке продают товары, количество которых измеряется в килограммах, тоннах и штуках (пачках), а цена указывается в руб./кг или руб./шт. Чтобы правильно вычислять стоимость, добавим в Е2 еще один код: 3 (штуки), а в ячейке G2 укажем код единицы измерения цены: 1 — (руб./кг); 2 — (руб./шт.)

Тогда в ячейку F5 можно записать следующую функцию:

Нулевая стоимость товара означает, что либо неправильно указан хотя бы один из кодов (например, Е2=4), либо указано недопустимое сочетание кодов (например, товар в килограммах, а цена в руб./шт.).

Подобным же образом можно использовать и функцию ИЛИ.

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

Использование различных инструментов

Имеются специальные средства, расширяющие возможности программы Excel 2007. Доступ к ним осуществляется при помощи обычных команд вкладок Ленты. Например, открыв вкладку Данные, можно выполнить консолидацию, группировку, проверку данных, анализ и т. д. Команды открывают специальные диалоговые окна, с помощью которых можно выполнить указанные операции. Например, с помощью инструмента Подбор параметра можно решать задачи оптимизации, уравнения и др.

Пример. Решение уравнений с помощью инструмента Подбор параметра.

Задача: найти решение уравнения х3Зх2 + х = -1.

Решение:

  • 1. Запустите Excel и откройте рабочую книгу, созданную ранее.
  • 2. Создайте новый рабочий лист (например, Лист 2), дважды щелкните на его ярлычке и присвойте ему имя Уравнение.
  • 3. Занесите в ячейку А1 значение 0.

4. Занесите в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1. Соответствующая формула может, например, иметь вид:

  • 5. Дайте команду: Данные —> Анализ "что-если" —> Подбор параметра. Появится диалоговое окно.
  • 6. В поле Установить в ячейке укажите В1, в поле Значение задайте —1, в поле Изменяя значение ячейки укажите А1.
  • 7. Щелкните на ОК и посмотрите результат подбора, отображающий в диалоговом окне Результат подбора параметра. Щелкните на ОК, чтобы сохранить полученные значения ячеек.
  • 8. Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия? (При наличии нескольких корней результат решения уравнения зависит от того, какое число было выбрано в качестве начального приближения.).
  • 9. Сохраните рабочую книгу.
 
<<   СОДЕРЖАНИЕ ПОСМОТРЕТЬ ОРИГИНАЛ   >>