Логические функции в Excel. Запись логических выражений. Логические функции НЕ, И, ИЛИ, ЕСЛИ. Вложенные функции

Табличный процессор Excel – назначение. Элементы окна программы: меню, панели инструментов, строка формул. Файлы табличного процессора Excel. Рабочая книга и рабочие листы Excel. Работа с листами: вставка, удаление, перемещение, переименование.. Элементы таблицы: ячейки, строки, столбцы, диапазоны. Приемы выделения строк, столбцов, диапазонов.

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

Табличный процессор - программное средство для проектирования электронных таблиц. Они позволяют не только создавать таблицы, но и автоматизировать обработку табличных данных. С помощью электронных таблиц можно выполнять различные экономические, бухгалтерские и инженерные расчеты, а также строить разного рода диаграммы, проводить сложный экономический анализ, моделировать и оптимизировать решение различных хозяйственных ситуаций и т. д.

Верхняя её часть - строка меню - очень похоже на меню программы Word и содержит команды, которые сгруппированы по их функциям (Главная Вставка, Разметка страницы, Формулы и т.д.).

Панели инструментов находятся под строкой меню и представляют собой ряд кнопок.

Под панелями инструментов находится строка формул - длинная пустая строка, куда вводятся данные или формулы. Слева от неё находится область с названием строки таблицы, с которой в данный момент мы работаем.

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

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

Верхние ячейки таблицы с буквами А, В, С, D и т.д. - это столбцы ячеек.

Вертикальный столбец с цифрами в левой части таблицы - это строки ячеек. То есть нашу таблицу можно поделить на строки с цифрами и столбцы с буквами, следовательно, каждая строка в таблице имеет свое название (А4, Bl, D5 и т. д.).

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

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

Добавить новый лист можно, одновременно нажав клавиши Shift + F11.

Чтобы переименовать новый лист, нужно щелкнуть правой кнопкой мыши по ярлычку Лист 1 (Лист 2 и т. д.) ив выпадающем меню выбрать Переименовать.

Минимальным элементом электронной таблицы, над которым можно выполнять те или иные операции, является такая клетка, которую чаще называют ячейкой. Каждая ячейка имеет уникальное имя (идентификатор), которое составляется из номеров столбца и строки, на пересечении которых располагается ячейка. Нумерация столбцов обычно осуществляется с помощью латинских букв (поскольку их всего 26, а столбцов значительно больше, то далее идёт такая нумерация — AA, AB,..., AZ, BA, BB, BC,...), а строк — с помощью десятичных чисел, начиная с единицы. Таким образом, возможны имена (или адреса) ячеек B2, C265, AD11 и т.д.

Следующий объект в таблице — диапазон ячеек. Его можно выделить из подряд идущих ячеек в строке, столбце или прямоугольнике. При задании диапазона указывают его начальную и конечную ячейки, в прямоугольном диапазоне — ячейки левого верхнего и правого нижнего углов. Наибольший диапазон представляет вся таблица, наименьший — ячейка. Примеры диапазонов — A1:A100; B12:AZ12; B2:K40.

 

Выделение строк, столбцов, диапазонов:

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

Выделить одну ячейку можно следующими способами: щелкнуть левой клавишей мыши в нужном месте или ввести адрес нужной ячейки в поле имени.

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

Для выделения несмежных ячеек следует выделить первую группу смежных ячеек и, удерживая клавишу Ctrl, продолжить выделение следующих фрагментов.

Для выделения всей строки или столбца следует щелкнуть по имени строки или столбца.

Для выделения всех ячеек рабочего листа следует нажать на кнопку Выделить все, находящуюся на пересечении заголовков строк и столбцов.

 

Оформление таблицы в Excel, Границы и заливка. Выравнивание в ячейке. Объединение ячеек. Оформление заголовка и размещение текста в центре ячейки. Основные действия с ячейками, строками, столбцами и диапазонами. Копирование, перемещение, вставка, удаление, очистка. Работа с буфером обмена.

Сначала выделите всю таблицу, как на втором рисунке. Кликнув правой кнопкой по выделенным ячейкам выберите пункт "Формат ячеек" и в появившемся окне закладку "Граница" и закладка "Заливка".

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

 

По умолчанию цвет линии границы является черным, если на вкладке "Вид" окна диалога "Параметры" в поле "Цвет" установлено значение "Авто". Чтобы выбрать цвет, отличный от черного, щелкните на стрелке справа от поля "Цвет". Раскроется текущая 56-цветная палитра, в которой можно использовать один из имеющихся цветов или определить новый. Обратите внимание, что для выбора цвета границы нужно использовать список "Цвет" на вкладке "Граница".

После выбора типа и цвета линии нужно указать положение границы. При нажатии кнопки "Внешние" в области "Все" граница размещается по периметру текущего выделения, будь то одна ячейка или блок ячеек.

Заливка ячеек Заливка ячеек цветным фоном используется для разделения данных на листе. В этом случае устанавливают заливку всего столбца или всей строки листа. Можно выполнить заливку и отдельных ячеек. Для установки цвета заливки ячеек в большинстве случаев достаточно раскрывающейся кнопки Цвет заливки панели инструментов Форматирование. Необходимо выделить заливаемые ячейки, щелкнуть по стрелке в правой части кнопки Цвет заливки и выбрать цвет.

Объединение ячеек:

На панели находим кнопку "Объединить и поместить в центре" и щелкаем по ней левой кнопкой мыши.

На вкладке Начальная страница в группе Выравнивание выберите команду Объединить и поместить в центре.

Копирование и перемещение

 

Чтобы скопировать данные из ячейки/строки/столбца, нужно выделить необходимый элемент и вызвав контекстное меню по нажатию правой кнопки мыши выбрать пункт Копировать, а затем Вставить, переместив курсор и выделив нужное для вставки место. Также можно воспользоваться сочетаниями клавиш Ctrl+Insert или Ctrl+C(для копирования) и Shift+Insert или Ctrl+V(для вставки), либо с помощью левой кнопки мыши с нажатой одновременно клавишей Ctrl«перетащить» элемент в нужное место для получения там его копии, либо воспользоваться соответствующими кнопками группы Буфер обмена вкладки Главная.

 

Чтобы переместить данные из ячейки/строки/столбца, нужно выделить необходимый элемент и по контекстному меню по нажатию правой кнопки мыши выбрать пункт Вырезать, затем Вставить, переместив курсор и выделив нужное для вставки место. Также можно воспользоваться сочетаниями клавиш Shift+Deleteили Ctrl+X(для вырезания) и Shift+Insertили Ctrl+V(для вставки), либо просто перетащить на новое место элемент левой кнопкой мыши, либо воспользоваться соответствующими кнопками группы Буфер обменавкладки Главная.

 

Добавление и удаление.

 

Чтобы добавить новую ячейку на лист, нужно выделить место вставки новой ячейки, в контекстном меню выбрать команду Вставитьи в появившемся окне Добавление ячееквыбрать нужный вариант.

 

Чтобы добавить новую строку/столбец, нужно выделить строку/столбец, перед которой будет вставлена новая/новый, и в контекстном меню командой Вставитьосуществить вставку элемента, либо использовать команду Главная→Ячейки →Вставить→Вставить строки на лист/Вставить столбцы на лист.

 

Чтобы удалить строку/столбец, нужно выделить данный элемент, и по контекстному меню командой Удалить, выполнить удаление, либо применить команду Главная →Ячейки→Удалить→Удалить строки с листа/ Удалить столбцы с листа. При удалении строки произойдет сдвиг вверх, при удалении столбца – сдвиг влево.

 

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

 

Для удаления данных из ячеек можновоспользоваться командой контекстного меню Очистить содержимое.Также в группе Редактирование вкладки Главнаясуществует кнопка Очистить, позволяющая выбрать, что именно вы хотите очистить в ячейке (все, формат, содержимое, примечания).

 

Бу́фер обме́на (англ. clipboard) — промежуточное хранилище данных, предоставляемое программным обеспечением и предназначенное для переноса или копирования между приложениями или частями одного приложения через операции вырезать, скопировать, вставить.

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

Текст - представляет собой строку, произвольной длины. Ячейка, содержащая текстовые данные, не может использоваться в вычислениях. Если Excel не может интерпретировать данные в ячейке как число или формулу программа считает, что это текстовые данные. Для ввода числа в формате текста (случае ввода телефонных кодов) следует ввести перед числом символ апострофа,'01481. данные теперь рассматриваются Excel как текст. Арифметические операции работать не будут. Число будет либо проигнорировано, или появится сообщение об ошибке

Числовые данные - отдельное число. Это последовательность цифр со знаком или без, дробная часть отделяется ",".Как числа рассматривают данные определяющие дату или денежные суммы 257; -145,2; 15$. Элементы даты можно отделять друг от друга символом (/) или (-), либо использовать текст, например 10 окт 03. Excel распознает множество форматов даты. Элементы времени можно отделять символом двоеточие, например 10:43:45.

Формулы. Ячейка с формулой служит для проведения вычислений над ячейками с числами (вычисляемая ячейка). Формула начинается со знака "=" (например = A2*B2+C3)

Форматы числовых данных:

процентный формат: число (от 0 до 1) в ячейке умножается на 100%, округляется до целого и записывается со знаком %;

Денежный формат — данный формат сродни числовому, за исключением того, что разделитель групп разрядов используется всегда, и вы можете использовать вывод около числа знака валюты (например, $).

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

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

Автозаполнение и автосуммирование:

Кнопка “Автосуммирование” панели инструментов позволяет определять сумму для любого количества смежных ячеек. Для этого курсор мыши помещается в ячейку, в которой нужно поместить результат суммирования. Нажимается кнопка “автосуммирование” панели инструментов (рис. 5.5), программа Excel высвечивает функцию сумма с диапазоном адресов ячеек. Одновременно на экране вокруг ячеек, данные которых будут суммироваться, возникает «бегущая рамка». Если диапазон выделен правильно нажимается клавиша Enter и в ячейке высвечивается результат.

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

В правом нижнем углу текущей ячейки имеется черный квадратик – маркер заполнения.

При наведении указателя мыши на этот маркер, он приобретает форму тонкого черного крестика.

Протягивание левой кнопкой мыши маркера ячейки, содержащей число, скопирует это число в последующие ячейки. Если при протягивании маркера удерживать клавишу <Ctrl>, то ячейки будут заполнены последовательными числами /

41.Математические вычисления в Excel. Правила записи формул. Использование адреса ячеек (ссылки) в формулах. Автоматический пересчет ссылок при копировании формул. Относительные и абсолютные ссылки.

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства "=". В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным. Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

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

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Очень полезно овладеть приемом копирования формул. Если планируется расположить новую формулу рядом с уже существующей, то достаточно выделить формулу, предназначенную для копирования, и, аналогично приему автопродолжения, протянуть выделение в нужную сторону. Формулы будут автоматически подправлены в соответствии с направлением копирования. Предположим, что требуется скопировать описанную выше формулу из ячейки C1 в ячейку C2. Тогда, после копирования, формула будет изменена на = A2*B2.

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

=А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 — =В1+С1.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.

Функции в Excel. Классификация функций. Вставка функций. Математические и статистические функции. Вычисление минимального, максимального и среднего значений. Синтаксис функций. Функции СЧЁТ, СЧЁТ3 и СЧЁТЕСЛИ.

Функция — это специальная, заранее подготовленная формула, которая выполняет операции над заданными значениями и возвращает результат. Значения, над которыми функция выполняет операции, называются аргументами. В качестве аргументов могут выступать числа, текст, логические значения, ссылки. Аргументы могут быть представлены константами или формулами. Формулы в свою очередь могут содержать другие функции, т. е. аргументы могут быть представлены функциями. Функция, которая используется в качестве аргумента, является вложенной функцией. Excel допускает до семи уровней вложения функций в одной формуле.

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

1. Арифметические и тригонометрические.

2. Инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).

3. Информационные, предназначенные для определения типа данных, хранимых в ячейках.

4. Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).

5. Статистические, предназначенные для выполнения статистического анализа данных.

6. Финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.

7. Функции баз данных, предназначенные для анализа данных из списков или баз данных.

8. Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т. д.).

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

10. Нестандартные функции. Это функции, созданные пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basic

Вставка функции:

Существуют следующие правила ввода функций:

1. Имя функции всегда вводится после знака «=».

2. Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.

3. Между именем функции и знаком «(» пробел не ставится.

4. Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

Для ввода функций можно использовать Мастер функций, вызываемый нажатием кнопки Вставка функции на панели инструментов. Мастер функций позволяет выбрать нужную функцию из списка и выводит для нее панель формул. На панели формул отображаются имя и описание функции, количество и тип аргументов, поле ввода для формирования списка аргументов, возвращаемое значение.

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

Функция СЧЁТ

 

Подсчитывает количество числовых значений в диапазоне.

 

Синтаксис: =СЧЁТ(значение1; [значение2]; …), где значение1 – обязательный аргумент, принимающий значение, ссылку на ячейку, диапазон ячеек или массив. Аргументы от значение2 до значение255 являются необязательными и аналогичными значение1.

 

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

 

Функция СЧЁТЕСЛИ

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

 

Синтаксис: =СЧЁТЕСЛИ(диапазон; критерий), где

 

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

критерий – обязательный аргумент. Критерий проверки, содержащий значение либо условия типа больше, меньше, которые необходимо заключать в кавычки. Для текстовых значений можно использовать подстановочные символы (* и?).

 

Функция СЧЁТЗ

 

Подсчитывает непустые ячейки в указанном диапазоне.

 

Синтаксис: =СЧЁТЗ(значение1; [значение2]; …), где значение1 является обязательным аргумент, все последующие аргументы до значение255 необязательны. В качестве значения может содержаться ссылка на ячейку или диапазон ячеек.

 

Ячейки, содержащие пустые строки (=""), засчитываются как НЕпустые.

 

Логические функции в Excel. Запись логических выражений. Логические функции НЕ, И, ИЛИ, ЕСЛИ. Вложенные функции.

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

=     Равно

>     Больше

<     Меньше

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

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

<>         Не равно

 

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

Функция ЕСЛИ

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

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

Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:

=ЕСЛИ(А1>3;10;20)

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например:

=ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал")

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0.

 

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

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

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

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

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

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

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

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

 

=ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")

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

 

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