Сложные функции в excel

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

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

Ниже представлен список операторов сравнения Excel:

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

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

Функция ЕСЛИ – это функция, которая позволяет вывести в ячейку одно значение, если заданное пользователем условие выполняется и другое – если условие не выполняется.

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

Рассчитать стоимость товара с учетом того, что если количество превысило 5 шт., то магазин дает скидку 10%. Исходные данные приведены на рис. 26.

Для того чтобы рассчитать стоимость товара с учетом скидки необходимо:

Переместить курсор в ячейку D2. С помощью мастера функций выбрать из категории Логические функцию ЕСЛИ (рис. 27), а затем щелкнуть на кнопке ОК.

Диалоговое окно Аргументы функции (рис. 28) содержит три поля ввода. В поле Лог_выражение необходимо ввести условие, которое определяет, превышает ли проданное кол-во товара 5 шт., следовательно введем в это поле С2>5. В поле Значение_если_истина необходимо ввести формулу, которая вычисляет стоимость товара с учетом скидки, тогда введем в это поле B2*C2-B2*C2*0,1. В поле Значение_если_ложь необходимо ввести формулу, которая вычисляет стоимость товара без учета скидки (условие С2>5 — Ложь), в тогда введем в это поле B2*C2 (см. рис. 29). Теперь щелкнем на кнопке OK.

Скопировать полученную формулу в смежные ячейки. Результаты вычислений формул приведены на рис. 30.

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

На рис. 31 приведена таблица с данными. Необходимо:

Определить, на какую сумму продано товаров каждого вида.

Определить, на какую сумму продано товаров каждого вида с учетом скидки (сумма с учетом скидки = сумма — сумма * скидка). Скидка начисляется по следующему принципу: если продано товара на сумму более 2500 грн., то скидка составит 5%, если продано товара на сумму менее 1100 грн., то скидка составит 0%, в остальных случаях скидка составит 2%.

Для того чтобы выполнить первый пункт задания необходимо в ячейку D2 ввести формулу =C2*B2.

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

Переместим курсор в ячейку E2 и с помощью мастера функций введем следующую формулу (рис. 33 – 34). Результаты решения приведены на рис. 35.

Рис. 35

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

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

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

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

Предположим, что нужно вывести сообщение «Светофор», если содержимое ячейки B4 или «красный», или «зеленый», или «желтый». Если же она содержит любую другую информацию, то необходимо вывести сообщение «Это не светофор. ».

Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 (1 =1; B4 =4.5.

Тогда в ячейку С15 необходимо ввести формулу: =СЧЁТЕСЛИ(B11:B13;»>=4,5″). Результат приведен на рис. 40

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

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

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

диапазон_суммирования — фактические ячейки для суммирования.

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

В таблице на рис. 41 приведены объемы партий товара, полученные магазином. Необходимо просуммировать только объемы тех партий, значения которых превышают 20. Решение на рис. 42

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

Логические функции в Excel проверяют данные и возвращают результат «ИСТИНА», если условие выполняется, и «ЛОЖЬ», если нет.

Рассмотрим синтаксис логических функций и примеры применения их в процессе работы с программой Excel.

Использование логических функций в Excel

Логические функции в Excel и примеры решения задач

Задача 1. Необходимо переоценить товарные остатки. Если продукт хранится на складе дольше 8 месяцев, уменьшить его цену в 2 раза.

Сформируем таблицу с исходными параметрами:

Чтобы решить поставленную задачу, воспользуемся логической функцией ЕСЛИ. Формула будет выглядеть так: =ЕСЛИ(C2>=8;B2/2;B2).

Логическое выражение «С2>=8» построено с помощью операторов отношения «>» и «=». Результат его вычисления – логическая величина «ИСТИНА» или «ЛОЖЬ». В первом случае функция возвращает значение «В2/2». Во втором – «В2».

Усложним задачу – задействуем логическую функцию И. Теперь условие такое: если товар хранится дольше 8 месяцев, то его стоимость уменьшается в 2 раза. Если дольше 5 месяцев, но меньше 8 – в 1,5 раза.

Формула приобретает следующий вид: =8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))’ class=’formula’>.

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

Задача 2. Если стоимость товара на складе после уценки стала меньше 300 р. или продукт хранится дольше 10 месяцев, его списывают.

Для решения используем логические функции ЕСЛИ и ИЛИ: =10);»списан»;»»)’ class=’formula’>. Условие, записанное с помощью логической операции ИЛИ, расшифровывается так: товар списывается, если число в ячейке D2 = 10.

Читать еще:  Excel функция и

При невыполнении условия функция ЕСЛИ возвращает пустую ячейку.

В качестве аргументов можно использовать другие функции. К примеру, математические.

Задача 3. Ученики перед поступлением в гимназию сдают математику, русский и английский языки. Проходной балл – 12. По математике для поступления нужно получить не менее 4 баллов. Составить отчет о поступлении.

Составим таблицу с исходными данными:

Нужно общее количество баллов сравнить с проходным баллом. И проверить, чтобы по математике оценка была не ниже «4». В графе «Результат» поставить «принят» или «нет».

Введем формулу вида: =4;СУММ(B3:D3)>=$B$1);»принят»;»нет»)’ class=’formula’>. Логический оператор «И» заставляет функцию проверять истинность двух условий. Математическая функция «СУММ» используется для подсчета итогового балла.

Функция ЕСЛИ позволяет решать многочисленные задачи, поэтому используется чаще всего.

Статистические и логические функции в Excel

Задача 1. Проанализировать стоимость товарных остатков после уценки. Если цена продукта после переоценки ниже средних значений, то списать со склада этот продукт.

Работаем с таблицей из предыдущего раздела:

Для решения задачи используем формулу вида: . В логическом выражении «D2 . Первый аргумент – $B$2:$B$7 – диапазон ячеек для проверки. Второй аргумент – В9 – условие. Третий аргумент – $C$2:$C$7 – диапазон усреднения; числовые значения, которые берутся для расчета среднего арифметического.

Функция СРЗНАЧЕСЛИ сопоставляет значение ячейки В9 (№1) со значениями в диапазоне В2:В7 (номера магазинов в таблице продаж). Для совпадающих данных считает среднее арифметическое, используя числа из диапазона С2:С7.

Задача 3. Найти средние продажи в магазине №1 г. Москва.

Видоизменим таблицу из предыдущего примера:

Нужно выполнить два условия – воспользуемся функцией вида: .

Функция СРЗНАЧЕСЛИМН позволяет применять более одного условия. Первый аргумент – $D$2:$D$7 – диапазон усреднения (откуда берутся цифры для нахождения среднего арифметического). Второй аргумент – $B$2:$B$7 – диапазон для проверки первого условия.

Третий аргумент – В9 – первое условие. Четвертый и пятый аргумент – диапазон для проверки и второе условие, соответственно.

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

БЛОГ

Только качественные посты

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

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

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

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

( Для перехода к примерам нажмите на название функции. Все примеры — это ссылки на лучшие статьи уважаемых специалистов по Excel и наших партнеров)

1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN)[Базовые формулы Excel]
2. ВПР (VLOOKUP)[Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
3. ИНДЕКС (INDEX)[По индексу получает значение из ссылки или массива]
4. ПОИСКПОЗ (MATCH)[Ищет значения в ссылке или массиве]
5. СУММПРОИЗВ (SUMPRODUCT)[Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS)[Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
7. ЕСЛИ (IF)[Выполняет проверку условия]
8. И / ИЛИ / НЕ (AND / OR / NOT)[Логические условия, как правило для функции ЕСЛИ]
9. ЕСЛИОШИБКА (IFERROR)[Если формула возвращает ошибку то что]
10. СУММЕСЛИМН (SUMIFS)[Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
11. СРЗНАЧЕСЛИМН (AVERAGEIFS)[Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
12. СЧЁТЕСЛИМН (COUNTIFS)[Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS)[Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL)[Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
15. ДВССЫЛ (INDIRECT)[Определяет ссылку, заданную текстовым значением]
16. ВЫБОР (CHOOSE)[Выбирает значение из списка значений по индексу]
17. ПРОСМОТР (LOOKUP)[Ищет значения в массиве]
18. СМЕЩ (OFFSET)[Определяет смещение ссылки относительно заданной ссылки]
19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN)[Возвращает номер строки/столбца, на который указывает ссылка]
20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS)[Возвращает количество столбцов/строк в ссылке]
21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP)[Округляет число до указанного количества десятичных разрядов]
22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK)[Возвращает случайное число]
23. Ч (N)[Возвращает значение, преобразованное в число]
24. ЧАСТОТА (FREQUENCY)[Находит распределение частот в виде вертикального массива]
25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &)[Объединения двух или нескольких текстовых строк в одну]
26. ПСТР (MID)[Выдает определенное число знаков из строки текста, начиная с указанной позиции]
27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT)[Возвращает заданное количество символов текстовой строки слева / права]
28. ДЛСТР (LEN)[Определяет количество знаков в текстовой строке]
29. НАЙТИ / ПОИСК (FIND / SEARCH)[Поиск текста в ячейке с учетом / без учета регистр]
30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE)[Заменяет в текстовой строке старый текст новым]
31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER)[Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
32. ГИПЕРССЫЛКА (HYPERLINK)[Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
33. СЖПРОБЕЛЫ (TRIM)[Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
34. ПЕЧСИМВ (CLEAN)[Удаляет все непечатаемые знаки из текста]
35. СОВПАД (EXACT)[Проверяет идентичность двух текстов]
36. СИМВОЛ / ПОВТОР (CHAR / REPT)[Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
37. СЕГОДНЯ / ТДАТА (TODAY / NOW)[Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
38. МЕСЯЦ / ГОД (MONTH / YEAR)[Вычисляет год / месяц от заданной даты]
39. НОМНЕДЕЛИ (WEEKNUM)[Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
40. ДАТАЗНАЧ (DATEVALUE)[Преобразует дату из текстового формата в числовой]
41. РАЗНДАТ (DATEDIF)[Вычисляет количество дней, месяцев или лет между двумя датами]
42. РАБДЕНЬ (WORKDAY)[Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
43. ЯЧЕЙКА (CELL)[Возвращает сведения о формате, расположении или содержимом ячейки]
44. ТРАНСП (TRANSPOSE)[Выдает транспонированный массив]
45. ПРЕОБР (CONVERT)[Преобразует число из одной системы мер в другую]
46. ПРЕДСКАЗ (FORECAST)[Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
47. ТИП.ОШИБКИ (ERROR.TYPE)[Возвращает числовой код, соответствующий типу ошибки]
48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)[Возвращает данные, хранящиеся в сводной таблице]
49. БДСУММ (DSUM)[Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

Читать еще:  Впр в excel как расшифровывается

После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

  • Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
  • Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
  • Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

6 малоизвестных, но очень полезных функций Excel

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

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

Сводные таблицы

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

Как применять:

  1. Выберите Вставка > Рекомендуемые сводные таблицы.
  2. В диалоговом окне Рекомендуемые сводные таблицы щелкните любой макет сводной таблицы, чтобы увидеть его в режиме предварительного просмотра, а затем выберите тот из них, в котором данные отображаются нужным вам образом. Нажмите кнопку ОК.
  3. Excel добавит сводную таблицу на новый лист и отобразит список полей, с помощью которого можно упорядочить данные в таблице.

Подбор параметра

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

Как применять:

  1. Выберите Данные > Работа с данными > Анализ «что если» > Подбор параметра.
  2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула.
  3. В поле Значение введите нужный результат формулы.
  4. В поле Изменяя значениеячейки введите ссылку на ячейку, в которой находится корректируемое значение, и нажмите кнопку ОК.

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

Условное форматирование позволяет быстро выделить на листе важные сведения.

Как применять:

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите формулу, которая вам понадобится.

Например, если вам нужно выделить все значения меньше 100, выберите Правила выделения ячеек > Меньше, а затем наберите 100. Перед тем как нажать ОК, можно выбрать формат, который будет применяться для подходящих значений.

ИНДЕКС и ПОИСКПОЗ

Если ВПР помогает находить нужные данные только в первом столбце, то, благодаря функциям ИНДЕКС и ПОИСКПОЗ, можно искать информацию внутри таблицы.

Как применять:

  1. Убедитесь, что ячейки с данными образуют сетку, где есть заголовки и названия строк.
  2. Используйте функцию ПОИСКПОЗ: сначала, чтобы найти столбец, в котором расположен искомый элемент, и затем еще раз, чтобы перейти к строке с ответом.
  3. Вставьте ответы в ИНДЕКС, и Excel сможет указать на ячейку, где эти значения пересекаются.

Например: ИНДЕКС (array, ПОИСКПОЗ (lookup_value, lookup_array, 0), ПОИСКПОЗ (lookup_value, lookup_array, 0)).

Диаграмма «водопад»

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

Как применять:

В версии Excel 2016 необходимо выделить нужные данные и выбрать Вставка > Водопад или Диаграмма > Водопад.

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

Использование логических функций в Excel

Табличный редактор Эксель – очень гибкий и мощный инструмент для структурированной работы с данными. Среди большого количества его эффективных инструментов стоит отдельно выделить логические операторы, в основе использования которых лежит признание выражения истинным или ложным. Эти функции эффективно встроены в инструментарий формул и позволяют создавать гибкие условия для различных вычислений или заполнения ячеек на основе логических выражений. В данной статье будут описаны основные используемые функции, а также приведен практический пример использования логических операторов в 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. Все готово. Благодаря логическим операторам мы получили заполненные данные для столбца с премиями.

Заключение

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

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
×
×
Adblock
detector