Основные функции – ЧАСТЬ 1

В этой главе мы рассмотрим наиболее полезные встроенные функции  Excel. Причем, чтобы не перегружать книгу подробным описанием их  всех, сфокусируемся только на основных  и  используемых  чаще   прочих.  Соответственно,  данную  главу  (а  также последующие  три)  ни  в  коем  случае  нельзя  считать  полноценным  справочником  по означенной теме. Полный перечень всех функций Excel с базовой информацией о них и ссылками на разделы этой книги приведен в приложении В, «Встроенные функции Excel». Кроме  того,  сведения  о  функциях,  оставленных  нами  без  внимания,  представлены  в справочной  системе Excel: в разделе «Справка по функциям» либо в  диалоговом окне Мастера функций.

СМОТРИ ТАКЖЕ

Функциям Excel также посвящены главы 15 («Даты и время»), 16 («Финансовый анализ») и

17 («Статистический анализ»).

Математические функции

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

Функция СУММ

Функция СУММ (SUM) предназначена для суммирования ряда чисел и имеет следующий синтаксис:

=СУММ (число1; число2;…)

Аргументов  число1,  число2  и  т.  д.  может  быть  до  30,  в  их  качестве  допустимо  использовать  числа,  формулы,  диапазоны,  ссылки  на  ячейки,  возвращающие  численные значения. Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения.

СОВЕТ

Условные выражения также применимы как аргументы функции суммирования. Подробнее об этом рассказывается в разделе «Условные выражения» главы 12.

Кнопка Автосумма

Поскольку   функция   суммирования   является   одной   из   самых    востребуемых,   на

«стандартную» панель инструментов Excel помещена специальная  кнопка Автосумма (AutoSum).  Если  выделить  ячейку  и  нажать  кнопку  Автосумма,  программа  создаст формулу  суммирования  в   виде   -СУММ()  и  предложит  указать  диапазон  исходных значений.    Поэтому   удобнее   выделять   диапазон   аргументов   до   нажатия    кнопки Автосумма,

Автоматическое расширение диапазона

Довольно типичный пример неправильной работы электронной таблицы  — ошибка при суммировании, которая возникает при добавлении новых  ячеек в нижний или правый конец диапазона суммирования, на который ссылается формула. Например, если в ячейку А5 ввести формулу =СУММ(А1:А4), а затем вставить перед строкой 5 новую строку, то формула сместится вниз, в ячейку А6. В предыдущих версиях программы (до Excel 2000) значения  в  новой  строке  уже  не  включались  в   формулу  суммирования,  но  теперь разработчики учли пожелания, и как только в пустых ячейках появятся новые значения, Excel немедленно обновит результат.

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

Наиболее полезные математические функции

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

Функции ПРОИЗВЕД и СУММПРОИЗВ

Функция ПРОИЗВЕД (PRODUCT) перемножает все значения,  задаваемые в качестве аргументов,  количество  которых  ограничивается  30.  Учитываются  как  числа,  так  и логические  значения  и  текстовые  представления  чисел.  Аргументы,  ссылающиеся  йа пустые ячейки, игнорируются.

Функция СУММПРОИЗВ (SUMPR0DUCT) возвращает сумму произведений соответствующих элементов массивов или диапазонов. В  качестве аргументов задаются массивы или диапазоны одного размера и формы, их количество не должно превышать 30. (При этом все  нечисловые значения рассматриваются как нулевые.) Например,  приведенные ниже формулы возвращают один и тот же результат:

=СУММПР0ИЗВ(А1:А4;В1:В4)

{=СУММ(А1:А4*В1:В4)}

Единственная  разница  заключается  в  том,  что  в  данном  случае  формула  с  функцией СУММ  должна  быть  с  помощью  комбинации  клавиш  Ctrl+Shift+  Enter  заключена  в фигурные скобки, обозначающие ее применение к массиву.

СМОТРИ ТАКЖЕ  

Массивы подробно рассматриваются в разделе «Массивы» главы 12.

Функция ОСТАТ

Функция  ОСТАТ  (MOD)  возвращает  остаток  от  целочисленного   деления  и  имеет синтаксис

=ОСТАТ(число;делитель)

Результат ее действия — остаток, получаемый от деления аргумента  число на аргумент делитель. Например, формула =ОСТАТ(9;4) возвращает значение 1, то есть остаток от деления 9 на 4.

Рассмотрим работу с функцией ОСТАТ на практике:

1.   Выделите  ячейку  и  выберите  команду  Формат  ?  Условное  форматирование

(Format ? Conditional Formatting).

2.   В первом раскрывающемся списке выберите вариант Формула, а в  поле справа от списка введите формулу =ОСТАТ(СТРОКА();2)=0.

3.   Щелкните на кнопке Формат (Format) и выберите цвет на вкладке Вид (Patterns). Им будет выделяться каждая ячейка, расположенная в удовлетворяющей условию строке. Учтите, что если на первом шаге была выделена ячейка в строке с нечетным номером, ничего не произойдет, но если применить форматирование к ячейкам в четных строках, результат немедленно появится на экране.

В этой формуле происходит вызов функции СТРОКА (ROW),  передающей программе номер текущей строки, который затем делится на 2, а остаток от деления запоминается. Если он не равен нулю (для строк  с нечетными номерами), все выражение принимает значение   ЛОЖЬ,    поскольку   формула   содержит   условие   =0.   Именно   поэтому форматируются только те строки, для которых формула возвращает значение ИСТИНА, то есть четные.

СМОТРИ ТАКЖЕ

Условное   форматирование   более   подробно   рассматривается   в   разделе   «Условное форматирование» главы 9.

Функция ЧИСЛКОМБ

Функция  ЧИСЛКОМБ  (CОMBIN)  определяет  число  возможных   комбинаторных  со-

четаний для заданного количества элементов. Она имеет следующий синтаксис:

=ЧИСЛКОМБ(число;число_выбранных)

где  аргумент  число  —  это  общее  количество  элементов,  а  число_выбраииых  —  их количество в каждой комбинации. Так, чтобы определить, сколько команд по 12 игроков можно  образовать  из  17   человек,  введите  формулу  =ЧИСЛКОМБ(17;12).  Согласно полученному в ячейке значению 6188, очевидно, что при формировании команды лучше не отталкиваться от чистой статистики.

СОВЕТ

Функция ЧИСЛКОМБ полезна чаще, чем вы можете подумать. Она в  состоянии помочь выяснить, насколько призрачны шансы получить при  раздаче в покер комбинацию флешрояль на бубновой масти,  начинающуюся с туза, без учета джокеров. Введите формулу

=ЧИСЛКОМБ(52;5), и вы получите результат 2 598 960. То есть вы имеете всего один шанс из двух с половиной миллионов; и это не так уж и мало  — ведь вероятность выиграть в лотерею 6 из 49 еще меньше. Как показывает формула =ЧИСЛКОМБ(49;6), это всего один

вариант из 13 983 816. Мораль: экономьте свое время и деньги.

Функции СЛЧИС и СЛУЧМЕЖДУ

Функция СЛЧИС (RAND) генерирует псевдослучайные числа в диапазоне от 0 до 1. Эта одна из редких функций Excel, которая не требует аргумента, тем не менее не забывайте ставить  после  ее  имени  круглые  скобки.  Результат,  выдаваемый  функцией  СЛЧИС, изменяется  при  каждом  пересчете  листа.  Но,  если  установлен  автоматический  режим обновлений,  это  изменение  будет  происходить  при  вводе  на  лист   каждого  нового значения.

Функция  СЛУЧМЕЖДУ (RANDBETWEEN) доступна,  только  если  установлен  Пакет анализа (Analysis ToolPak).  Она  предоставляет  больше  возможностей,  чем  функция СЛЧИС, например: способна  случайным образом выбирать целочисленные значения из заданного интервала.

СМОТРИ ТАКЖЕ

Информацию о надстройке Пакет анализа см. в разделе «Установка пакета анализа» главы

Функция имеет следующий синтаксис:

СЛУЧМЕЖДУ(начало;конец)

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

=СЛУЧМЕЖДУ(123;45б) возвращается любое целое число из интервала от 123 по 456

включительно.

Функции округления

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

ПРИМЕЧАНИЕ

Не путайте функции округления и применение к ячейкам числовых  фиксированных форматов, таких как 0 или 0,00, устанавливаемых на вкладке Число (Number) диалогового окна Формат   ячеек   (Format   Cells).    При   подобном   форматировании   округляется   только отображаемое, а не хранимое в ячейке значение, что не влияет на результат — выполняя вычисления, Excel всегда обращается только к хранимым, а не к показываемым значениям. Функции округления, в отличие от операции форматирования, изменяют хранимые в ячейках числовые данные.

Функции ОКРУГЛ, ОКРУГЛВНИЗ и ОКРУГЛВВЕРХ

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий