Логические и статистические функции

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

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

И — возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

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

ИЛИ — возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

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

СРЗНАЧ — возвращает среднее арифметическое своих аргументов.

Синтаксис: СРЗНАЧ(число1; число2;...)

МИН и МАКС — возвращают соответственно наименьшее и наибольшее значение из набора значений.

Синтаксис: МИН(число1;число2;...); МАКС(число1;число2;...)

СЧЁТ — подсчитывает количество чисел в списке аргументов.

Синтаксис: СЧЁТ(значение1; значение2;...)

СЧЁТЗ — подсчитывает количество непустых значений в списке аргументов.

Синтаксис: СЧЁТЗ(значение1; значение2;...)

СЧЁТЕСЛИ — подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию.

Синтаксис: СЧЁТЕСЛИ(интервал; критерий)

СЧИТАТЬПУСТОТЫ — подсчитывает количество пустых ячеек в заданном интервале.

Текстовые функции. Функции ссылок и массивов

СЦЕПИТЬ — объединяет несколько текстовых строк в одну. Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор &.

Синтаксис: СЦЕПИТЬ(текст1; текст2;...)

ЗАМЕНИТЬ — заменяет часть текстовой строки на другую текстовую строку.

Синтаксис: ЗАМЕНИТЬ(старый_текст; нач_ном; число_литер; новый_текст)

ПОДСТАВИТЬ — подставляет текст нов_текст вместо текста стар_текст в текстовой строке. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке, а функция ЗАМЕНИТЬ — когда нужно заменить любой текст, начиная с определенной позиции.

Синтаксис: ПОДСТАВИТЬ(текст; стар_текст; нов_текст; номер_вхождения)

ПРОПИСН — делает все буквы в тексте прописными.

Синтаксис: ПРОПИСН(текст)

ЛЕВСИМВ и ПРАВСИМВ — возвращают соответственно первые (самые левые) и последние (самые правые) символы текстовой строки.

Синтаксис: ЛЕВСИМВ(текст; число_литер); ПРАВСИМВ(текст; число_литер)

ПСТР — возвращает заданное число символов из строки текста, начиная с указанной позиции.

Синтаксис: ПСТР(текст; нач_ном; число_литер)

ПОИСК — возвращает позицию первого вхождения символа или текстовой строки при поиске слева направо, используется для поиска вхождения символа или строки текста в другую строку текста, с тем чтобы применить функции ПСТР или ЗАМЕНИТЬ для изменения текста.

Синтаксис: ПОИСК(искомый_текст; текст_для_поиска; нач_позиция)

ДЛСТР — возвращает количество символов в текстовой строке.

Синтаксис: ДЛСТР(текст)

ПРОСМОТР имеет две синтаксические формы: вектор и массив.

Вектор — это диапазон, который содержит только одну строку или один столбец. Векторная форма функции ПРОСМОТР просматривает диапазон вектор_просмотра в поисках искомого значения и возвращает значение из диапазона вектор_результата.

Синтаксис: ПРОСМОТР(искомое_значение; вектор_просмотра; вектор_результата)

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

Синтаксис: ВПР(искомое_значение; табл_массив; номер_столбца; диапазон_просмотра)

ПОИСКПОЗ — возвращает позицию (порядковый номер) элемента в массиве.

Синтаксис: ПОИСКПОЗ(искомое_значение; просматриваемый_массив; тип_сопоставления)

Примеры

Пример 4.1. Фирма начала выполнение крупного заказа 35 рабочих дней назад. Определить сколько рабочих дней и сколько календарных дней займет работа над заказом, если планируется его закончить в последний день года.

Выполнение:

1. По функциям СЕГОДНЯ, ГОД и ДАТА в ячейках С1:С10 получены текущая дата, последняя дата и даты праздников текущего года. Данную задачу необходимо решить в лабораторной работе, поэтому формулы для нее не приводятся.

2. Дата начала работ определяется по функции РАБДЕНЬ, в которой начальной датой является текущая, количество дней à 35 со знаком минус, так как выполнение заказа началось 35 рабочих дней назад. Необходимо также указать диапазон праздников, созданный в С2:С9. Таким образом, формула =РАБДЕНЬ(C1;‑35;C2:C9), созданная в Е1 вернет результат à 38806, который нужно отформатировать как дату.

3. Количество календарных дней определяется разностью между датами окончания и начала работ à =C10–E1. Результат в Е2 нужно отформатировать как число.

4. Количество рабочих дней в Е3 определяется по функции ЧИСТРАБДНИ, в которой нужно указать даты начала и окончания работ, а также даты праздников: =ЧИСТРАБДНИ (E1;C10;C2:C9)

Пример 4.2. Рассчитать премию сотрудников по данным таблицы в А1:D9, если она составляет 40% от оклада при отсутствии опозданий. За каждое опоздание процент премии снижается на 10%, а 4-е опоздание лишает сотрудника всяческой премии. Процент премии, оставшийся после вычетов за опоздания, может увеличиваться на 10% за 6—10 сверхурочных часов, на 20% — за 11—15, на 30% — за 16 и выше.

Выполнение:

1. Для вычисления премии нужно оклад умножить на процент премии (=В2*В11). Так как процент премии зависит от ряда факторов (количество опозданий сотрудника и сверхурочные часы), то вместо базового процента в В11 для определения фактического процента следует использовать функцию ЕСЛИ. Чтобы не запутаться в условиях, удобно составить блок-схему:

 

1. Составленную блок-схему несложно применить для заполнения аргументов функции ЕСЛИ. Количество ромбов (à) в схеме соответствует количеству функций ЕСЛИ в формуле, условие в ромбе — это логическое выражение в функции ЕСЛИ, выражение или формула, которая идет по стрелке «да» соответствует аргументу «Значение_если_истина», по стрелке «нет» — аргументу «Значение_если_ложь».

3. Формула разрабатывается в Е2, для первого сотрудника, а затем протягивается в Е3:Е9 à =B2* ЕСЛИ (C2>=4;0; ЕСЛИ (D2<6;$B$11–$B$12*C2;

ЕСЛИ (D2<11;$B$11–$B$12*C2+$B$12; ЕСЛИ (D2<16;

$B$11–$B$12*C2+$B$12*2;$B$11–$B$12*C2+$B$12*3))))

Пример 4.3. Получить в столбце В название улицы из адреса в столбце А.

Выполнение:

1. Для получения части текста из ячейки используется функция ПСТР, в которой аргумент «Текст» — это ячейка, содержащая весь текст, аргумент «Начальная_позиция» — это номер символа, следующего за первым пробелом (V), аргумент «Количество знаков» — это разность между номером второго пробела и начальной позицией.
2. Для нахождения номера символа используется функция ПОИСК. Чтобы найти первый пробел, поиск нужно начинать с первого символа à ПОИСК(" V ";A1;1). Прибавив к этой формуле единицу, получим номер символа, следующего за первым пробелом.
3. Чтобы найти второй пробел, поиск нужно начинать с символа, следующего за первым пробелом à ПОИСК(" V ";A1;ПОИСК(" V ";A1;1)+1)
4. Подставив полученные формулы поиска первого и второго пробела в функцию ПСТР, в ячейке В1 получим формулу:

= ПСТР (A1; ПОИСК (" V ";A1;1)+1; ПОИСК (" V ";A1; ПОИСК (" V ";A1;1)+1)–(ПОИСК (" V ";A1;1)+1))

Пример 4.4. Разработать формулу, которая будет по данным таблицы в A1:G4 возвращать текст в виде «Процент надбавки составляет ХХ%» в зависимости от введенных в С6 категории и в С7 стажа работы.

Выполнение:

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

1 способ:

1.1. Рассмотрим таблицу А2:G4, в первом столбце которой расположены категории.

1.2. Используя функцию ВПР и указав в аргументе «Искомое значение» à С6, можно извлечь процент надбавки из любого столбца этой таблицы для категории введенной в С6.

1.3. Столбец, из которого нужно извлечь процент надбавки, зависит от стажа, введенного в С7, поэтому для его нахождения нужно использовать формулу à ПОИСКПОЗ(C7;А1:G1;0)

1.4. Таким образом, функция ВПР должна быть следующей à ВПР(C6;A2:G4; ПОИСКПОЗ(C7;А1:G1;0);0)

1.5. Результат, возвращаемый функцией ВПР, нужно отформатировать с помощью функции ТЕКСТ и сцепить с текстовой строкой:

="Процент надбавки составляет "&ТЕКСТ(ВПР(C6;A2:G4;ПОИСКПОЗ(C7;А1:G1;0);0);"0%")

 

2 способ:

2.1. Рассмотрим таблицу В1:G4, в первой строке которой находится стаж.

2.2. Используя функцию ГПР и указав в аргументе «Искомое значение» à С7, можно извлечь процент надбавки из любой строки этой таблицы для стажа введенного в С7.

2.3. Строка, из которой нужно извлечь процент надбавки, зависит от категории, введенной в С6, поэтому для ее нахождения нужно использовать формулу à ПОИСКПОЗ(C6;А1:А4;0)

2.4. Таким образом, функция ГПР должна быть следующей à =ГПР(C7;B1:G4; ПОИСКПОЗ(C6;A1:A4;0);0)

2.5. Результат, возвращаемый функцией ГПР, нужно отформатировать с помощью функции ТЕКСТ и сцепить с текстовой строкой:

="Процент надбавки составляет "&ТЕКСТ(ГПР(C7;B1:G4;ПОИСКПОЗ(C6;A1:A4;0);0);"0%")

 

Практические задания

На оглавление

Лабораторная работа № 4 (4 часа)

Цель работы: научиться применять встроенные функции при решении задач.

Вариант 1

Загрузить шаблонExcel_Lab_4_1.xltx

I. Функции даты и времени

1.1. Получить даты праздников 1 января, 7 января, 8 марта, 1 мая, 9 мая, 3 июля, 7 ноября, 25 декабря для текущего года:

Текущая дата:
Текущий год:
День	Месяц	Дата

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

 

Последний день полугодия:
Число рабочих дней:

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

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

 

Фамилия	Дата поступления	Стаж (полных лет)
Гончаров	03.05.78
Новицкая	15.08.85
Петренко	01.03.99
Шевцов	30.10.95
Федорова	08.01.67