Главная страницаОбратная связьКарта сайта

Microsoft Excel 2010:Математические логические и статистические функции.


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

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

Таблица 24.1 Математические функции




Диапазон суммирования - диапазон СЗ:С8, ячейки которого требуется просуммировать. 4. Для завершения нажимаем кнопку ОК.

В результате в строке формул отобразится функция СУММЕСЛИ q указанными аргументами (рие. 24.26), а в ячейке Е19 - результат вычисления (рис. 24.2в).



Рис. 24.2. Использование функции СУММЕСЛИ

Если требуется ограничить диапазон суммирования несколькими критериями, следует использовать функцию СУММЕСЛИМН, которая позволяет проверить до 127 условий.

Например, исходя из данных таблицы, приведенной на рис. 24.3, необходимо вычислить, сколько было продано моделей телевизоров Rolsen филиалом Юпитер.



Рис 24.3. Подсчет суммы ячеек, удовлетворяющих нескольким условиям

Чтобы получить искомое значение, в ячейку следует вставить функцию СУММЕСЛИМН и задать параметры, как показано на рис. 24.4.

Примечание. При использовании функций СУММЕСЛИ и СУММЕСЛИМН нужно помнить о различиях в порядке ввода аргументов: в функции СУММЕСЛИ интервал суммирования вводится последним аргументом, а в функции СУММЕСЛИМН он вводится первым.

Таким образом, данная функция будет искать в столбце Филиал (диапазон D3:D12) - значение Юпитер, а в столбце Модель (диапазон АЗ:А12) - значение, содержащее текст Rolsen. В случае совпадения будут просуммированы ячейки столбца Продано, шт. (диапазон СЗ: С12) (рис. 24.5).




Рис. 24.S. Результат работы функции СУММЕСЛИМН

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

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

Таблица 24.2 Логические функции


Название    Описание




Особенностью логических функций является то, что они оперируют с логическим форматом данных, аргументы которого могут принимать только два значения: ИСТИНА или ЛОЖЬ. Значение ИСТИНА соответствует ситуации, когда проверяемое условие выполняется, и, наоборот, ЛОЖЬ свидетельствует, что условие не выполняется. Например, если значение ячейки А1 равно 2, а А2 равно 5, то формула =АКА2 вернет значение ИСТИНА, а формула =А1 >А2 вернет значение ЛОЖЬ.

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

Фирма предоставляет на товар скидку 3%, если количество единиц товара больше 10 (рис. 24.6). Требуется, исходя из количества заказанного товара, рассчитать его цену с учетом скидки.

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

1.   Переходим в ячейку F6 и нажимаем кнопку J щ Вставить функцию.

2.   В окне мастера функций указываем категорию Логические и выбираем функцию ЕСЛИ.

3.   Задаем аргументы функции следующим образом (рис. 24.7а):

 

■    Логическое выражение (проверяемое условие): Еб>10;

■    Значениееслиистина {значение, которое будет возвращать функция в случае выполнения условия, - цена с учетом скидки 3%): C6*(1-3);


Рис. 24.6. Определение цены с учетом скидки

■ Значение_если_ложь (значение, которое будет возвращать функция в случае невыполнения условия) - С6, т.е. исходная цена указанно го товара без скидки. 4. Нажимаем кнопку ОК- в ячейке F6 получим формулу:

=EOW(E6>I0;C6*(l-3);C6)

Если теперь мы распространим эту формулу на остальные ячейки таблицы Заказ, получим результат во всех ячейках столбца Цена со скидкой, руб., даже в тех, для которых соответствующее значение в столбце Кол-во отсутствует (рис. 24.76). Это связано с тем, что Excel считает пустую ячейку нулевой, а поскольку 0<5 (т.е. заданное условие не выполняется), то функция возвращает результат Значение__если_ложь. Чтобы это исправить, необходимо добавить в формулу проверку еще одного условия - является ли ячейка пустой или нет. Для этого можно в функцию ЕСЛИ вложить еще одну путем изменения соответствующего аргумента исходной функции.

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

1. Выделить ячейку с формулой (F6).

2.        Щелкнуть мышью в строке формул на той функции, параметры которой необходимо изменить - ЕСЛИ (а), и нажать кнопку

Вставить функцию.



Рис» 24.7. Применение логической функции ЕСЛИ

3.       В появившемся диалоговом окне Аргументы функции отредактировать параметры: установить курсор в поле Значе-Huejecjtujioacb, удалить его «одержимое и вставить функцию ЕСЛИ, выбрав ее л поле имени (б). В результате откроется окно для ввода параметров вложенной функции ЕСЛИ.

4.       Задать аргумента! для вложенной функции (в): Логическое выражение.   E6=MN; Значение__если_истина:    **;

Значение еслиложь:       Сб.

5.       Нажать кнопку ОК9 после чего в ячейке F6 получим формулу (г): =ЕСЛИ(Е6>10;Сб*(1-$Р$3);ЕСЛИ(Еб^;п;С6))

6.       Копируем формулу в остальные ячейки столбца. В результате напротив пустых ячеек столбца Кол-во в столбце Цена со скидкой, руб. будут располагаться также пустые ячейки (д).

Таким образом, данная формула сначала проверяет условие Кол-во>10, если оно выполняется - возвращается новая цена с учетом скидки. Если условие не выполняется (здесь возможны два варианта: либо в ячейке содержится значение <=10, либо она пустая) - проверяется второе условие: Кол-во=п (является ли ячейка пустой). Если оно выполняется и ячейка пустая, то в качестве результата работы формулы получаем также пустую ячейку,
а если оно не вьшолняется (в ячейке содержится значение <=10), то возвращается исходная цена товара.



Рис. 24.8. Вложение функций ЕСЛИ для проверки двух условий

Статистические функции

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

Таблица 24.3 Статистические функции




Рассмотрим, как можно использовать статистические функции на примере функции СЧЕТЕСЛИМН, которая подсчитывает количество соответствий заданному набору условий. Например, подсчитаем количество человек в отделе продаж, у которых стаж работы менее 3-х лет (рис. 24.9). Для этого:

1.          Устанавливаем курсор в ячейку Е16 и нажимаем кнопку Ш Вставить функцию.

2.    В  категории  Статистические выбираем  функцию СЧЕТ-ЕСЛИМН.

В окне Аргументы функции задаем такие параметры (рис. 24.10):

■  Диапазон_условия!\ D3.D14 (диапазон условий, по которо­
му проверяется Условие!);

 Условие!: Отдел продаж;

■  Диапазон_условия2: D3-.D14 (диапазон условий, по которо­
му проверяется Условие2);

 Условие2:<Зп.



Рис 24.10. Параметры функции СЧЕТЕСЛИМН

Примечание. Диапазоны условий обязательно должны иметь одинаковые размеры.

4. Нажимаем кнопку ОК. Таким образом, получаем формулу для подсчета искомого значения:

=СЧЁТЕСЛИМН(03:014;Отдел продаж;ЕЗ:Е14;<3) Ее результатом является значение 4 (рис. 24.11).

Сколько человек в отделе продаж со стажем менее 3лет ?   
Рис 24.11. Использование функции СЧЕТЕСЛИМН


Обсудить статью на форуме


Если прочитаная статья из нашей обширной энциклопедия компьютера - "Microsoft Excel 2010:Математические логические и статистические функции.", оказалась полезной или интересной, Вы можете поставить закладку в социальной сети или в своём блоге на данную страницу:

Так же Вы можете задать вопрос по статье через форму обратной связи, в сообщение обязательно указывайте название или ссылку на статью!
   


Copyright © 2008 - 2024 Дискета.info