16383

ЛОГИЧЕСКИЕ ФУНКЦИИ EXCEL

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторная работа № 5. ЛОГИЧЕСКИЕ ФУНКЦИИ EXCEL Логические выражения используются для записи условий в которых сравниваются числа функции формулы текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения...

Русский

2013-06-20

454.5 KB

49 чел.

Лабораторная работа № 5. ЛОГИЧЕСКИЕ ФУНКЦИИ EXCEL

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

=  Равно

>  Больше

<  Меньше

>=  Больше или равно

<=  Меньше или равно

<>  Не равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ  имеет следующий синтаксис:

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

Формула =ЕСЛИ(А1>3;10;20) возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например: =ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал").

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0. Например: =ЕСЛИ(СУММ(А1:А3)=30;А10;"").

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например: =ЕСЛИ(А1="Динамо";10;290). Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

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

Функция НЕ имеет только один аргумент и следующий синтаксис:

=НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст "Прошел", если студент имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид: =ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")

Несмотря на то, что функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Так, если в предыдущей формуле заменить функцию И на ИЛИ, то ученик будет проходить, если выполняется хотя бы одно из условий (средний балл более 4 или пропуски занятий менее 3). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.

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

Предположим, что студент сдал сессию в случае, если средний экзаменационный балл больше 3 (ячейка A2) и число пропусков занятий меньше 10% (ячейка B2). В противном случае студент сессию не сдал. Функция ЕСЛИ, реализующая этот алгоритм, будет выглядеть следующим образом: ЕСЛИ(И(А2>3;В2<=10%);”СДАЛ”;”НЕ СДАЛ”).

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

Вложенные функции ЕСЛИ

Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ.

Предположим, что в ячейку А1 могут быть занесены три текстовые константы: «красный», «желтый», «зеленый». В зависимости от значения текстовой константы необходимо реализовать функцию ЕСЛИ, выдающую соответствующие рекомендации: «стойте», «ждите», «идите». Функция будет выглядеть следующим образом:
ЕСЛИ(А1=”красный”;”стойте”;ЕСЛИ(А1=”желтый”;”ждите”;”идите”))

Например, в следующей формуле используются три функции ЕСЛИ: =ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";

ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА()
=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратит значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА: 

=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")

В противном случае формула возвратит "Стоп".

Вложенные функции

Вложенные функции

В некоторых случаях может потребоваться использование функции в качестве одного из аргументов (Аргумент. Значения, используемые функцией для выполнения операций или вычислений. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.) другой функции. Например, в следующей формуле применяется вложенная функция СРЗНАЧ и сравнение результата со значением 50.

 Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Допустимые типы вычисляемых значений   Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, т.е. иметь значение ИСТИНА либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна возвращать логическое значение либо ИСТИНА, либо ЛОЖЬ. В противном случае Microsoft Excel выдаст ошибку «#ЗНАЧ!».

Предельное количество уровней вложенности функций   В формулах можно использовать до семи уровней вложенных функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции СРЗНАЧ и СУММ считаются функциями второго уровня, потому что обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня и так далее.

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 -- до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию -- специальная кнопка на панели "Строка формул":

После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

 Переключение на другую функцию в формуле

Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее.

Использование имен

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

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

  •  имя может начинаться с буквы, обратного слеша (\) или символа подчеркивания;
    •  в имени могут использоваться только буквы, цифры, обратный слеш и символ подчеркивания;
    •  нельзя использовать имена, которые трактуются как ссылки на ячейки и одиночные буквы R и С;
    •  для определения имени необходимо выделить ячейку или диапазон и использовать команду меню ВставкаИмяПрисвоить, а затем в поле «Имя» ввести выбранное пользователем имя;
    •  для вставки имени в формулу используется команда меню ВставкаИмяВставить и затем из списка выбирается соответствующее имя.

Присваивание имен

Имя можно присвоить ячейке или диапазону ячеек.

  1.  Выделите ячейку или диапазон ячеек.
  2.  В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить имя.
  3.  В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 6.18).

Рис. 6.18.  Присвоение имени ячейке

  1.  Для задания области действия имени в поле со списком Область выберите Книга или имя листа в книге.
  2.  При желании в поле Примечание можно ввести примечание к имени, которое затем будет отображаться в окне Диспетчера имен.

Для удобства работы рекомендуется создавать имена короткие и хорошо запоминающиеся. Первый знак в имени должен быть буквой или знаком подчеркивания. Остальные знаки имени могут быть буквами, числами, точками и знаками подчеркивания. Пробелы не допускаются. Также не допускаются имена, которые имеют такой же вид, как и ссылки на ячейки, например Z$100 или R1C1. В имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2007 или Год.2007. Имя может содержать до 255 знаков. Имя может состоять из строчных и прописных букв, но Excel их не различает.

При работе в Excel можно ссылаться на другие листы той же рабочей книги. Например, чтобы ввести в ячейку A3 листа Лист1 ссылку на ячейку A1 листа Лист2, следует выполнить следующую последовательность действий:

  •  выделить ячейку A3 на листе Лист1 и ввести знак равенства;
  •  щелкнуть на ярлыке Лист2 внизу окна книги;
  •  щелкнуть на ячейке A1 и нажать клавишу <Enter>. После этого снова будет активизирован лист Лист1 и в ячейке A3 появится формула  =Лист2!A1.

По умолчанию при создании ссылки на другие листы Excel вставляет относительную ссылку.

2.11. Ссылки на ячейки других листов и книг

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

При каждом переходе на другой лист, его имя автоматически добавляется к ссылке на ячейку. Имя листа и адрес ячейки разделены служебным символом ! (восклицательный знак).

Например, в формуле в ячейке D2 в таблице на рис. 6.13 использована ячейка А4 листа Курс текущей книги.

Рис. 6.13.  Ссылка на ячейку другого листа текущей книги

Аналогичным образом можно ссылаться на ячейки, находящиеся в другой книге. Например, ссылка на ячейку A1 листа Лист1 книги Книга2 имеет следующий вид: =[Книга2]Лист1!$A$1. По умолчанию при создании ссылки на ячейки, принадлежащие другой рабочей книге, Excel вставляет абсолютную ссылку.

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

Excel 2007 предоставляет еще более мощные и удобные инструменты условного форматирования.

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

Для применения условного форматирования служит кнопка "Условное форматирование" на панели "Стили" ленты "Главная".

Чтобы лучше понять, как работает условное форматирование, выделите группу ячеек с уже введенными данными, нажмите кнопку "Условное форматирование" и посмотрите различные варианты форматирования.

По умолчанию программа автоматически определяет минимальное и максимальное значение в выделенном диапазоне и затем форматирует в равных процентных соотношениях.

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

".

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


 

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

33897. Сельское хозяйство СССР в 50-х гг 27.5 KB
  Еще на XIX съезде заявили что продовольственная проблема решена но это было ложью. Вопрос о насыщении с х техникой и снабжении кадрами для этой техники проблема кадров инженернотехнического профиля. В январе 1955 на пленуме Хрущев поставил задачу подъема животноводства проблема кормов. Проблема раскрестьянивания одна из главных в нашей историографии.
33898. Попытки перестройки системы управления народным хозяйством в 50-х – первой половине 60-х гг 38 KB
  Попытки перестройки системы управления народным хозяйством в 50х первой половине 60х гг. Попытка усовершенствования структуры управления рычага АКС: признано что главный порок экономики чрезмерная централизация управления многоступенчатость управления до 6 звеньев огромное количество чиновников отрыв аппарата от управления производством. Вопросы реформирования управления промышленность ставились на XX съезде. С 1957 началась реформа управления промышленностью.
33899. Развитие искусства в период «оттепели». Международные культурные связи 30 KB
  Развитие искусства в период оттепели. проявилась ограниченность развитие связей с заграницей международный конкурс Чайковского в 1958 с 1956 проводиться в Москве кинофестиваль в 1956 выставка Дрезденской галереи в Москве 1957 фестиваль молодежи в Москве новые произведения антисталинсткой направленности Солженицын. качественные изменения в материальной базе культуры радиофикация электрификация развитие телевидения. развитие альтернативного искусства в литературе.
33900. Внешнеполитическая деятельность СССР в 50-х – начале 60-х гг.: отношения с социалистическими странами 34 KB
  Внешнеполитическая деятельность СССР в 50х начале 60х гг. СССр послал в Югославию комиссии для изучения того что сделано за годы разрыва. Официальный разрыв в 1960 когда КПК обвинила СССР в ревизионизме. Отказавшись от курса на мировую революцию СССР продолжал занимать руководящие позиции в лагере социалистических стран.
33901. Внешнеполитическая деятельность СССР в 50-х – начале 60-х гг.: отношения с развивающимися государствами 36.5 KB
  Внешнеполитическая деятельность СССР в 50х начале 60х гг. СССР оказывает экономическую помощь странам 3го мира. успех СССР в мире Женевская конференция в 1954 и 1955. СССР оказывает экономическую помощь странам 3го мира.
33902. Относительные величины, используемые в статистической практике 23.61 KB
  Относительная величина структуры ОВС характеризует структуру совокупности определяет долю удельный вес части в общем объеме совокупности. ОВС рассчитывают как отношение объема части совокупности к абсолютной величине всей совокупности определяя тем самым удельный вес части в общем объеме совокупности : Относительная величина координации ОВК отношение одной части совокупности к другой ее части; показываетсколько единиц части стоящих в числителе формулы приходится на единицу другой части находящейся в знаменателе....
33903. Общее представление о статистических таблицах 12.38 KB
  По внешнему виду статистическая таблица представляет собой ряд пересекающихся горизонтальных и вертикальных линий образующих по горизонтали строки а по вертикали графы столбцы колонки которые в совокупности составляют как бы скелет таблицы. В образовавшиеся внутри таблицы клетки записывается информация. Составленную таблицу принято называть макетом таблицы. Подлежащее таблицы показывает о каком явлении идет речь в таблице и представляет собой группы и подгруппы которые характеризуются рядом показателей.
33904. Виды статистических таблиц. (Статистические таблицы. Виды таблиц. Подлежащее и сказуемое в таблицах) 11.49 KB
  Статистические таблицы. Основными элементами статистической таблицы являются подлежащее и сказуемое таблицы. Подлежащее таблицы это объект статистического изучения то есть отдельные единицы совокупности их группы или вся совокупность в целом. Сказуемое таблицы это статистические показатели характеризующие изучаемый объект.
33905. Основные правила построения статистических таблиц 11.5 KB
  Таблицы состоят из элементов: 1Нумерационный заголовок 2Тематические заголовки 3Заголовки и подзаголовки граф. Основными элементами статистической таблицы являются подлежащее и сказуемое таблицы. Подлежащее таблицы это объект статистического изучения то есть отдельные единицы совокупности их группы или вся совокупность в целом. Сказуемое таблицы это статистические показатели характеризующие изучаемый объект.