Логические задачи в эксель с примерами и решениями

Обновлено: 07.07.2024

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

Основные логические функции, используемые в Эксель

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

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

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

Операторы ИСТИНА и ЛОЖЬ

Функция ИСТИНА не имеет собственных аргументов и практически всегда используется, как структурный компонент для других логических выражений, так как принимает одно конкретно заданное значение.

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

Операторы И и ИЛИ

Синтаксис оператора И выглядит следующим образом:

=И(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

Оператор И используется в качестве элемента-связки для нескольких условий логического выражения. Важно, чтобы все аргументы оператора имели значение ИСТИНА (если в выражении их несколько), в противном случае вся логическая цепочка будет возвращать значение ЛОЖЬ.

Синтаксис оператора ИЛИ:

=ИЛИ(лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

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

Оператор НЕ

Синтаксис функции НЕ: =НЕ(лог_значение).

Количество аргументов оператора НЕ – всегда один. Соответственно, результат функции (ИСТИНА/ЛОЖЬ) полностью зависит только от значения аргумента.

Операторы ЕСЛИ и ЕСЛИОШИБКА

Общий синтаксис функции ЕСЛИ представляет собой конструкцию:

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

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

Синтаксис оператора ЕСЛИОШИБКА имеет вид:

=ЕСЛИОШИБКА(значение;значение_если_ошибка).

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

Операторы ЕОШИБКА и ЕПУСТО

Оператор ЕОШИБКА имеет следующую структуру:

=ЕОШИБКА(значение)

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

Примеры значений в некорректно заполненных ячейках:

Аргумент функции – адрес конкретной ячейки или ссылка на диапазон ячеек.

Формула функции ЕПУСТО выглядит следующим образом:

=ЕПУСТО(значение)

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

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

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

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

Нам необходимо произвести расчет премии. Ключевые условия, от которых зависит размер премии:

• величина обычной премии, которую получат все сотрудники без исключения – 3 000 руб.;
• сотрудницам женского пола положена повышенная премия – 7 000 руб.;
• молодым сотрудникам (младше 1984 г. рождения) положена повышенная премия – 7 000 руб.;

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

1. Встаем в первую ячейку столбца, в которой хотим посчитать размеры премий и щелкаем кнопку “Вставить функцию” (слева от сроки формул).
2. В открывшемся Мастере функций выбираем категорию “Логические”, затем в предложенном перечне операторов кликаем по строке “ЕСЛИ” и жмем OK.
3. Теперь нам нужно задать аргументы функции. Так как у нас не одно, а два условия получения повышенной премии, причем нужно, чтобы выполнялось хотя бы одно из них, чтобы задать логическое выражение, воспользуемся функцией ИЛИ. Находясь в поле для ввода значения аргумента “Лог_выражение” кликаем в основной рабочей области книги на небольшую стрелку вниз, расположенную в левой верхней части окна программы, где обычно отображается адрес ячейки. В открывшемся списке функций выбираем оператор ИЛИ, если он представлен в перечне (или можно кликнуть на пункт “Другие функции” и выбрать его в новом окне Мастера функций, как мы изначально сделали для выбора оператора ЕСЛИ).
4. Мы переключимся в окно аргументов функци ИЛИ. Здесь задаем наши условия получения премии в 7000 руб.:
   • год рождения позже 1984 года;
   • пол – женский;
5. Теперь обращаем внимание на строку формул. Кликаем в ней на название первоначального оператора ЕСЛИ, чтобы переключиться в аргументы этой функции.
6. Заполняем аргументы функции и щелкаем OK:
   • в значении “Истина” пишем цифру 7000;
   • в значении “Ложь” указываем цифру 3000;
7. Результат работы логических операторов отобразится в первой ячейке столбца, которую мы выбрали. Как мы можем видеть, окончательный вид формулы выглядит следующим образом:
   =ЕСЛИ(ИЛИ(C2>1984;D2="жен.");7000;3000) .
   Кстати, вместо использования Мастера функций можно было вручную составить и прописать данную формулу в требуемой ячейке.
8. Чтобы рассчитать премию для всех сотрудников, воспользуемся Маркером заполнения. Наведем курсор на правый нижний угол ячейки с формулой. После того, как курсор примет форму черного крестика (это и есть Маркер заполнения), зажимаем левую кнопку мыши и протягиваем выделение вниз, до последней ячейки столбца.
9. Все готово. Благодаря логическим операторам мы получили заполненные данные для столбца с премиями.

Заключение

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

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

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

Решение задач оптимизации в Excel

Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации используются следующие команды:

• Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
• Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
• Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

1. Количество изделий нам пока неизвестно. Это переменные.
2. В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
3. Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
4. Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

1. Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
2. Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
3. Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
4. Тип – 0.
5. БС – сумма, которую мы хотим получить в конце срока вклада.

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

Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.

Решение эконометрики в Excel

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

Дано 2 диапазона значений:

Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.

Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).

Решение логических задач в Excel

В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.

1. Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
2. Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
3. Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

1. Делаем таблицу со значениями матрицы А.
2. Выделяем на этом же листе область для обратной матрицы.
3. Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
4. В поле аргумента «Массив» вписываем диапазон матрицы А.
5. Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

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

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

Для начала вспомним, как вставить функцию в ячейку таблицы. Сделать это можно по-разному:

Независимо от выбранного варианта, откроется окно вставки функции, в котором требуется выбрать категорию “Финансовые”, определиться с нужным оператором (например, ДОХОД), после чего нажать кнопку OK.

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

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

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

Альтернативный способ

Находясь во вкладке “Формулы” можно нажать кнопку “Финансовые” в группе “Библиотека функций”. Раскроется список доступных вариантов, среди которых просто кликаем по нужному.

После этого сразу же откроется окно с аргументами функции для заполнения.

Популярные финансовые функции

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

Данный оператор применяется для вычисления будущей стоимости инвестиции исходя из периодических равных платежей (постоянных) и размера процентной ставки (постоянной).

Обязательными аргументами (параметрами) для заполнения являются:

• Ставка – процентная ставка за период;
• Кпер – общее количество периодов выплат;
• Плт – неизменная выплата за каждый период.

Необязательные аргументы:

• Пс – приведенная (нынешняя) стоимость. Если не заполнять, будет принято значение, равное “0”;
• Тип – здесь указывается:
  • 0 – выплата в конце периода;
  • 1 – выплата в начале периода
  • если поле оставить пустым, по умолчанию будет принято нулевое значение.

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

  Синтаксис функции:

  Результат в ячейке и выражение в строке формул:

  
  

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

  
  

  Обязательный аргумент всего один – “Значения”, в котором нужно указать массив или координаты диапазона ячеек с числовыми значениями (по крайней мере, одно отрицательное и одно положительное число), по которым будет выполняться расчет.

  Необязательный аргумент – “Предположение”. Здесь указывается предполагаемая величина, которая близка к результату ВСД. Если не заполнять данное поле, по умолчанию будет принято значение, равное 10% (или 0,1).

  Синтаксис функции:

  Результат в ячейке и выражение в строке формул:

  
  

  ДОХОД

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

  
  

  Обязательные аргументы:

  • Дата_согл – дата соглашения/расчета по ценным бумагам (далее – ц.б.);
  • Дата_вступл_в_силу – дата вступления в силу/погашения ц.б.;
  • Ставка – годовая купонная ставка ц.б.;
  • Цена – цена ц.б. за 100 рублей номинальной стоимости;
  • Погашение – суммы погашения или выкупная стоимость ц.б. за 100 руб. номинальной стоимости;
  • Частота – количество выплат за год.

  Аргумент “Базис” является необязательным, в нем задается способ вычисления дня:

  • 0 или не заполнен – армериканский (NASD) 30/360;
  • 1 – фактический/фактический;
  • 2 – фактический/360;
  • 3 – фактический/365;
  • 4 – европейский 30/360.

  Синтаксис функции:

  Результат в ячейке и выражение в строке формул:

  
  

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

  
  

  У функции только обязательные аргументы, к которым относятся:

  • Значения – указываются отрицательные (платежи) и положительные числа (поступления), представленные в виде массива или ссылок на ячейки. Соответственно, здесь должно быть указано, как минимум, одно положительное и одно отрицательное числовое значение;
  • Ставка_финанс – выплачиваемая процентная ставка за оборачиваемые средства;
  • Ставка _реинвест – процентная ставка при реинвестировании за оборачиваемые средства.

  Синтаксис функции:

  Результат в ячейке и выражение в строке формул:

  
  

  ИНОРМА

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

  
  

  Аргументы функции:

  • Дата_согл – дата расчета по ц.б.;
  • Дата_вступл_в_силу – дата погашения ц.б.;
  • Инвестиция – сумма, вложенная в ц.б.;
  • Погашение – сумма к получению при погашении ц.б.;
  • аргумент “Базис” как и для функции ДОХОД является необязательным.

  Синтаксис функции:

  Результат в ячейке и выражение в строке формул:

  
  

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

  
  

  Обязательные аргументы:

  • Ставка – процентная ставка за период займа;
  • Кпер – общее количество периодов выплат;
  • Пс – приведенная (нынешняя) стоимость.

  Необязательные аргументы:

  • Бс – будущая стоимость (баланс после последней выплаты). Если поле оставить незаполненным, по умолчанию будет принято значение, равное “0”.
  • Тип – здесь указывается, как будет производиться выплата:
    • “0” или не указано – в конце периода;
    • “1” – в начале периода.

    Синтаксис функции:

    Результат в ячейке и выражение в строке формул:

    
    

    ПОЛУЧЕНО

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

    
    

    Аргументы функции:

    • Дата_согл – дата расчета по ц.б.;
    • Дата_вступл_в_силу – дата погашения ц.б.;
    • Инвестиция – сумма, инвестированная в ц.б.;
    • Дисконт – ставка дисконтирования ц.б.;
    • “Базис” – необязательный аргумент (см. функцию ДОХОД).

    Синтаксис функции:

    Результат в ячейке и выражение в строке формул:

    
    

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

    
    

    Обязательные аргументы:

    • Ставка – процентная ставка за период;
    • Кпер – общее количество периодов выплат;
    • Плт – неизменная выплата за каждый период.

    Необязательные аргументы – такие же как и для функции “ПЛТ”:

    Синтаксис функции:

    Результат в ячейке и выражение в строке формул:

    
    

    СТАВКА

    Оператор поможет найти процентную ставку по аннуитету (финансовой ренте) за 1 период.

    
    

    Обязательные аргументы:

    • Кпер – общее количество периодов выплат;
    • Плт – неизменная выплата за каждый период;
    • Пс – приведенная стоимость.

    Необязательные аргументы:

    • Бс – будущая стоимость (см. функцию ПЛТ);
    • Тип (см. функцию ПЛТ);
    • Предположение – предполагаемая величина ставки. Если не указывать, будет принято значение по умолчанию – 10% (или 0,1).

    Синтаксис функции:

    Результат в ячейке и выражение в строке формул:

    
    

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

    
    

    Обязательные аргументы:

    • Дата_согл – дата расчета по ц.б.;
    • Дата_вступл_в_силу – дата погашения ц.б.;
    • Ставка – годовая купонная ставка ц.б.;
    • Доход – годовой доход по ц.б.;
    • Погашение – выкупная стоимость ц.б. за 100 руб. номинальной стоимости;
    • Частота – количество выплат за год.

    Аргумент “Базис” как и для оператора ДОХОД является необязательным.

    Синтаксис функции:

    Результат в ячейке и выражение в строке формул:

    
    

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

    
    

    Аргументы функции:

    • Ставка – ставка дисконтирования за 1 период;
    • Значение1 – здесь указываются выплаты (отрицательные значения) и поступления (положительные значения) в конце каждого периода. Поле может содержать до 254 значений.
    • Если лимит аргумента “Значение 1” исчерпан, можно перейти к заполнению следующих – “Значение2”, “Значение3” и т.д.

    Синтаксис функции:

    Результат в ячейке и выражение в строке формул:

    
    

    Заключение

    Категория “Финансовые” в программе Excel насчитывает свыше 50 различных функций, но многие из них специфичны и узконаправлены, из-за чего используются редко. Мы же рассмотрели 11 самых востребованных, по нашему мнению.

    =ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) - принимает одно из двух указанных значений, в зависимости от выполнения условия.

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

    Пример: Студенты сдали экзамен. Требуется сделать заключение: если оценка 5, 4 или 3, то экзамен сдан, иначе – переэкзаменовка.

    Запустить Excel и создать таблицу по образцу.

    
    

    В ячейку D2 ввести формулу =ЕСЛИ(С2>=3;”Экзамен сдан”;”Переэкзаменовка”)

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

    2. Логическая функция И

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

    Скопировать таблицу на Лист2 и удалить формулы в ячейках D 2: D 11. Изменить оценки и двум студентам вместо оценок ввести «неявка».

    
    

    В ячейку D2 ввести формулу =ЕСЛИ(И(С2>=2;С2<=5);”Оценка получена”;”Не пришел”)

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

    3. Логическая функция ИЛИ

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

    Скопировать таблицу на Лист3 и удалить формулы в ячейках D 2: D 11.

    В ячейку D2 ввести формулу =ЕСЛИ(ИЛИ(С2=3;С2=4;С2=5);”Оценка положительная”;”Очень плохо”)

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

    4. Статистическая функция СЧЕТЕСЛИ

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

    - Диапазон – блок ячеек, содержащий проверяемые значения;

    - Критерий – константа или условие проверки;

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

    Пример: Необходимо подсчитать количество различных оценок по результатам экзамена.

    Вставить Лист4, переместить его на место (за Лист3). Скопировать на этот лист диапазон ячеек А1:С11 с листа 1. Оформить ячейки столбца D по образцу.

    
    

    В ячейку Е2 ввести формулу =СЧЕТЕСЛИ(С2:С11;5). В ячейку Е3 ввести формулу, используя мастер функций.

    
    

    
    

    По аналогии ввести формулы в ячейки Е4 и Е5. При вводе формулы в ячейку Е6 в поле Критерий ввести неявка.

    
    

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

    5. Математическая функция СУММЕСЛИ

    =СУММЕСЛИ( Диапазон; Критерий [; Диапазон суммирования ] )

    - Диапазон – диапазон проверяемых значений;

    - Критерий – константа или условие проверки;

    - Диапазон суммирования – обрабатываемый диапазон. Если он отсутствует, то 1-й аргумент функции становится и проверяемым и обрабатываемым диапазоном;

    - [ ] – указание на то, что данный аргумент функции может отсутствовать.

    Вначале рассмотрим пример использования этой функции с двумя аргументами.

    Вставить Лист5. Разместить листы по порядку. Оформить таблицу по образцу.

    
    

    В ячейку А10 ввести текст Сумма, если >4000. В ячейку С10 ввести формулу =СУММЕСЛИ(С2:С8;">4000").

    
    

    Из примера видно, что суммироваться будут значения только тех ячеек диапазона С2:С8, которые отвечают условию «>4000». В данном случае диапазон C 2: C 8 и проверяется и обрабатывается.

    Рассмотрим использование функции с тремя аргументами.

    В ячейку А11 ввести текст Сумма для НР. В ячейку С11 ввести формулу =СУММЕСЛИ(А2:А8; "НР";С2:С8).

    
    

    В данном случае производится суммирование ячеек диапазона С2:С8 только в тех случаях, если в соседней ячейке диапазона А2:А8 находится запись «НР». Здесь диапазон А2:А8 проверяется, а диапазон С2:С8 обрабатываются.

    Показать результаты преподавателю, сохранить файл с именем Мат-ф-5.

    6. Вложенная функция ЕСЛИ

    Вставить Лист6. Разместить листы по порядку. Оформить таблицу по образцу.

    
    

    Предположим, что премия начисляется при следующих условиях:

    если средний балл меньше 3, то премия равна 0,

    если средний балл больше 3, но меньше 4.5, то премия равна 500р,

    если средний балл больше 4.5, то премия равна 800р.

    Поместить курсор в ячейку D 2 и вызвать Мастера функций.

    На первом шаге в левом окне выбрать категорию Логические. В правом окне выбрать функцию ЕСЛИ.

    
    

    Щелкнуть по кнопке ОК. Появится окно функции ЕСЛИ.

    
    

    На втором шаге Мастера функций в поле Логическое_выражение надо ввести условие, во втором поле - значение в случае Истина, а в третьем поле - значение в случае Ложь.

    Значение среднего балла находится в ячейке С2. Поэтому надо щелкнуть мышью по кнопке с красной стрелочкой в верхнем поле справа. Диалоговое окно свернется в строку.

    
    

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

    
    

    Теперь с помощью клавиатуры надо дополнить условие С2<3 . Можно было в первом поле ввести это условие с клавиатуры, не пользуясь кнопкой с красной стрелочкой (кнопкой свертки).

    Во второе поле ввести значение для случая, когда C 2<3, то есть, ввести 0.

    Нажать клавишу Tab или щелкнуть мышкой в третьем поле.

    
    

    В это поле надо поместить вложенную функцию ЕСЛИ. В строке формул слева имеется кнопка вызова функций.

    
    

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

    
    

    В первое поле нового окна ввести второе условие C 2<4,5, во второе поле ввести значение 500, а в третье поле ввести значение 800.

    
    

    Щелкнуть по кнопке ОК.

    
    

    В ячейку D2 будет помещен результат расчета, а в строке формул отображена расчетная формула с вложенной функцией ЕСЛИ. Скопировать эту формулу в ячейки D 3: D 5 и посмотреть результаты расчета.

    Показать преподавателю и сохранить файл с именем Если-влож.

    Определить премию в зависимости от среднего балла при следующих условиях:

    если >1, но <2, то премия равна 0,

    если >2, но <3, то премия равна 100,

    если >3, но <4, то премия равна 200,

    если >4, то премия равна 300.

    Получить решение, показать преподавателю и сохранить файл с именем Если-сам.

    Закрыть табличный процессор, убедиться, что строка активных задач пустая, закончить сеанс.

    Читайте также:

      
    • Adobe animate символ не двигается
      
    • Укажите программу с помощью которой можно архивировать файл windows winrar microsoft word wincar
      
    • Карточка счета в 1с как сформировать
      
    • Отражение для фотошопа png
      
    • Заливка в стиле шахматная доска эксель