Пример 7.13. Постановка задачи: Число 2 требуется возвести в степень каждого из четырех чисел, записанных в ячейках а41:а44.

Результат:

Вопрос: Почему в формуле массива 2^А41:А44 диапазон А41:А44 необязательно помещать внутрь круглых скобок? (Подсказка: Вспомните понятие приоритета операторов).

Пример 7.14. Цель: Провести сравнение двух способов решения одной и той же задачи: основанного на использовании формул массивов и обычных «ячеечных» формул.

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

Решение:

Результат в отображаемых значениях:

Используемые в вычислениях алгебраические формулы:

1. <Начислено по видам работ> = <Почасовая оплата>*<Всего отработано часов> *<% по видам работ>.

2. <Удержано по видам работ> = <Начислено по видам работ>*<Процент удержаний>.

3. <Премиальные к выплате по видам работ> = (<Начислено по видам работ> - <Удержано по видам работ>)*<Процент премиальных по видам работ>.

4. <Удержание с премиальных> = <Сумма премиальных к выплате по обеим видам работ>*<Процент удержаний>.

5. <Сумма к выплате > = <Всего начислено> - <Удержано с начисления> + <Всего премиальных> - <Удержано с премиальных>.

6. <Из кассы> = <Сумма к выплате> - <Долг>.

Результат в формулах массивов:

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

Проведем сравнение с ранее рассмотренным случаем ввода формул в отдельные ячейки (т.н. «ячеечных» формул). Ниже приведены сами эти формулы и показана схема их оптимального ввода:

Вопрос: О какой подправке формулы идет речь на третьем шаге заполнения таблицы формулами?

Анализ последних двух рисунков показывает, что для решения задачи с помощью обычных «ячеечных» формул требуется выполнить как минимум 11 действий, не говоря уже о том, что на каждом шаге надо думать над форматом применяемых ссылок. Поэтому там, где это возможно, часто (но далеко не всегда!) имеет смысл использовать формулы массивов. Заметим, что некоторые встроенные функции «не умеют» работать с массивами «должным образом», т.е. так хотелось бы или как представляется пользователю. Поэтому во всех «подозрительных» случаях надо проводить «эксперименты» на небольшом числе данных, а также обращаться к справке Ехсеl (<F1>) по поводу синтаксиса используемых функций.

Пример 7.15. Цель: Рассмотреть возможность совместного использования формул массивов и обычных «ячеечных» формул.

Постановка задачи: Имеются данные об объемах реализации одного и того же товара в трех магазинах за второй квартал года по определенной цене (числовые значения см. ниже по тексту). Требуется найти:

1. Ежемесячную выручку каждого магазина.

2. Итоговую выручку всех магазинов за каждый месяц и в целом за квартал, а также каждого магазина в целом за квартал.

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

4. Среднюю суммарную выручку по всем магазинам за квартал.

5. Среднюю ежемесячную выручку по всем магазинам за квартал.

6. Максимальную и минимальную выручку по всем магазинам за каждый месяц.

7. Максимальную и минимальную выручку каждого магазина за квартал.

8. Процент месячной выручки всех магазинов в общем итоге их работы за квартал.

9. Процент суммарной выручки данного магазина за квартал от общего квартального итога работы всех магазинов.

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

Входные данные:

Результат достигается введением в соответствующие ячейки таблицы

формул, позволяющих найти ответы на поставленные в задаче вопросы. Эти формулы вместе с адресами ячеек, в которые они введены, а также направление перемещения маркера заполнения в процессе процедуры ПиО_ЛКМ приведены ниже (границы «протяжки» маркера ясны из предшествующей таблицы):

Как видно, здесь используются как формулы массивов, так и «ячеечные» формулы.

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

Справка

Функция СРЗНАЧ возвращает среднее арифметическое своих аргументов. Синтаксис: =СРЗНАЧ(число1; число2; ...), где число1, число2, ... - это от 1 до 30 аргументов, для которых вычисляется среднее. При этом аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

Функция МАКС возвращает наибольшее значение из набора значений. Синтаксис: МАКС(число1;число2; ...), где число1, число2, ... - это от 1 до 30 чисел, среди которых ищется максимальное значение.

Смысл функции МИН и ее синтаксис очевидны.

Ответ на последний из поставленных в задаче вопросов содержится в следующих таблицах

,

.

В верхней таблице указано распределение мест магазинов по выручке в каждом конкретном месяце, в нижней – приведено два варианта решения задачи с помощью «ячеечной» функции РАНГ и с помощью этой функции как формулы массива.

Справка

Функция РАНГ возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией). Синтаксис: РАНГ(Число; Ссылка; Порядок), где Число - это значение (или ссылка, или массив), для которого определяется ранг. Ссылка - это массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются. Порядок - это число, определяющее способ упорядочения. Если Порядок равен 0 (нулю) или опущен, то Ехсеl определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если Порядок - это любое ненулевое число, то Ехсеl определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Функция РАНГ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся чисел влияет на ранг последующих чисел. Например, если Порядок - ненулевое число и в списке целых чисел число 10 повторяется дважды и имеет ранг 5, а следующим за 10 в порядке возрастания является число 11, то оно будет иметь ранг 7 (и никакое число не будет иметь ранг 6). Здесь предполагается, что числовая последовательность сортируется по возрастанию, т.е. элемент Порядок функции РАНГ является ненулевым числом. Другой пример: если ячейки A1:A5 содержат числа 7, 3,5, 3,5, 1, 2 соответственно, то функция =РАНГ(A2;A1:A5;1) возвращает значение 3 (предварительно список 7, 3,5, 3,5, 1, 2 нужно упорядочить по возрастанию, т.е. преобразовать в список 1, 2, 3,5, 3,5, 7; тогда элемент 1 занимает первое место, элемент 2 – второе, элемент 3,5 – третье и элемент 7 – пятое место, четвертого места вообще нет), а функции =РАНГ(A1;A1:A5;0) или =РАНГ(A1;A1:A5) возвращают значение 1 (предварительно список 7, 3,5, 3,5, 1, 2 нужно упорядочить по убыванию, т.е. преобразовать в список 7, 3,5, 3,5, 2, 1; тогда элемент 7 занимает первое место, элемент 3,5 – второе, элемент 2 – четвертое и элемент 1 – пятое место, третьего места вообще нет).

Используя функцию РАНГ, можно найти как места, занимаемые магазинами по квартальной выручке

,

так и соответствующее распределение мест всех магазинов по всем месяцам квартала

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

В Ехсеl имеются две логические константы ИСТИНА и ЛОЖЬ и шесть логических функций ИСТИНА(), ЛОЖЬ(), И(…;…;…), НЕ(…), ИЛИ(…;…;…) и ЕСЛИ(…;…;…). Вместо многоточий в четырех из них И(…;…;…), НЕ(…), ИЛИ(…;…;…) и ЕСЛИ(…;…;…) проставляются аргументы, которые могут быть константами ИСТИНА или ЛОЖЬ, или вложенными функциями, возвращающими значения этих констант. Первые две функции ИСТИНА() и ЛОЖЬ() вообще не содержат аргументов (круглые скобки без аргументов вводить обязательно!) и всегда возвращают значения логических констант ИСТИНА и ЛОЖЬ соответственно. Эти функции используются редко (в основном для совместимости различных электронных таблиц) и почти всегда могут быть заменены соответствующими логическими значениями. Функции И(…;… ;…) и ИЛИ(…;…;…) могут зависеть от одного до 30 аргументов, которые должны принимать только логические значения, включая и возвращаемые другими логическими функциями. Функция НЕ(…) зависит только от одного логического аргумента (который также может возвращаться другими логическими функциями). Эти три функции при правильном вводе аргументов всегда возвращают одно из двух возможных логических значений ИСТИНА или ЛОЖЬ. Если же аргументы введены неверно, например, вместо логических значений введен текст, то может быть возвращено значение ошибки #ЗНАЧ! или такой аргумент может быть проигнорирован. Во всех логических функциях вместо логических констант можно использовать их числовые представления: 0 отвечает константе ЛОЖЬ, а иные числа (чаще всего 1) – константе ИСТИНА. Логическая функция ЕСЛИ(…;…;…) зависит от трех аргументов, причем первый (считая слева направо) должен принимать только логические значения, включая и возможность их возвращения другими логическими функциями, а два других аргумента могут принимать любые значения, допустимые в Ехсеl. Первый аргумент является обязательным и (при правильном вводе или при правильной ссылке) принимает одно из двух значений ИСТИНА или ЛОЖЬ. Из двух других аргументов хотя бы один является обязательным, а второй может отсутствовать. В этом случае синтаксис функции ЕСЛИ выглядит следующим образом: =ЕСЛИ(…;…) или =ЕСЛИ(…;…;), или =ЕСЛИ(…;;…) (обратите внимание на расстановку точек с запятыми).

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

Определения и иллюстративные примеры

Функция И

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

• Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения.

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

• Если указанный интервал вообще не содержит логических значений, то возвращается значение ошибки #ЗНАЧ!.

Иллюстративные примеры

1. =И(ИСТИНА; ИСТИНА) возвращает значение ИСТИНА.

2. =И(ИСТИНА; ЛОЖЬ) возвращает значение ЛОЖЬ.

3. =И(2+2=4; 2+3=5) возвращает значение ИСТИНА.

4. =И(2+2=4; 2+2=5) возвращает значение ЛОЖЬ.

5. Если интервал B1:B3 содержит значения ИСТИНА, ЛОЖЬ, и ИСТИНА соответственно, то функция =И(B1:B3) возвращает значение ЛОЖЬ.

6. Если ячейка B4 содержит число между 1 и 100, то функция =И(1<B4; B4<100) возвращает значение ИСТИНА.

Пример 7.16. Постановка задачи: В ячейках А1, В1 и С1 находятся три числа. Проверить, что третье число не меньше первого и не больше второго. Если это действительно так, вывести значение логической константы ИСТИНА или числовое значение 1, в противном случае вывести значение логической константы ЛОЖЬ или числовое значение 0.

Решение

Комментарии:

1. Сравним формулы в ячейках В3 и В4, соответствующие вариантам 1 и 2. В 1-ом варианте формула возвращает значение логической константы ИСТИНА, поскольку действительно 2<=3<=5. Первый множитель И(C1>=B2;C1<=B1) формулы 2-ого варианта также возвращает значение ИСТИНА, но т.к. речь идет об арифметической операции умножения Excel преобразует его в соответствующее числовое значение 1. В результате формула =И(C1>=B2;C1<=B1)*1 возвращает значение, равное 1*1=1. Аналогичная ситуация имеет место для 4-ого варианта. Здесь уже две логические константы ИСТИНА преобразуются в единицы из-за того, что между ними стоит знак умножения.

2. Если в исходных данных, находящихся в диапазоне А1:С1, изменить какие-либо значения, то результат может оказаться противоположным, например,

^.

Функция ИЛИ

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

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

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

• Если заданный интервал не содержит логических значений, то функция ИЛИ возвращает значение ошибки #ЗНАЧ!.

Иллюстративные примеры

1. Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА, то функция =ИЛИ(A1:A3) возвращает значение ИСТИНА.

2. =ИЛИ(ИСТИНА) возвращает значение ИСТИНА.

3. =ИЛИ(1+1=1;2+2=5) возвращает значение ИСТИНА.

4. =ИЛИ(1+1=3;2+2=5) возвращает значение ЛОЖЬ.

5. Если ячейка B4 содержит число 100, то функция =ИЛИ(1<B4; B4<100) возвращает значение ИСТИНА. (Подумайте, существует ли хотя бы одно число, для которого предыдущая формула могла бы возвратить значение ЛОЖЬ?).

6. Если ячейка B4 содержит число 99, то функция =ИЛИ(1>B4; B4>100) возвращает значение ЛОЖЬ.