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

Пример 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%")