Использование функций

Общий список имеющихся в Excel функций очень велик. Некоторые из них достаточно очевидны и используются многими пользователями, в то время как другие (например инженерные, финансовые и статистические функции) могут использо ваться более ограниченным кругом пользователей. Для использования подобных функций может потребоваться специальная подготовка и знания в соответствую щей области. Однако некоторые операции  настолько  часто повторяются в работе пользователей, что необходимо остановиться на них подробнее.

Суммирование

Данная операция очень часто встречается в бухгалтерских бланках и финансовых расчетах. В Excel существует несколько вариантов  использования этой функции.  Можно просто перечислить конкретные ячейки, написав, например, такую формулу =A1+A2+A3+A4, или написать формулу в таком виде =СУММ(A1;A2;A3;A4). В этом случае суммируются значения четырех ячеек, но используются только эти конкретные ячейки. Ячейки  расположены рядом  и составляют  столбец  некоторой таблицы, однако при добавлении  строки между ними ничего не происходит,

суммируются по-прежнему только четыре ячейки, хотя адреса ячеек автоматически изменяются. Можно использовать указание диапазона ячеек. В этом случае формула  будет записана в виде =СУММ (A1:A4). Здесь по-прежнему  используются четыре ячейки. В этом случае при добавлении  строки между ячейками  происходит  автоматическое изменение диапазона, и суммирование будет происходить по формуле

=СУММ(A1:A5), то есть в операции будут участвовать уже пять ячеек. Эту полезную возможность по-разному указывать адреса ячеек можно использовать при создании определенных бухгалтерских бланков.

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

Существует  также более мощный механизм  автоматизации подобных вычисле ний — мастер суммирования.  Он реализован в виде надстройки. По умолчанию  эта надстройка  не установлена,  и поэтому мастер недоступен. После подключе ния  надстройки данный  мастер появляется в группе Решения вкладки  Формулы в виде кнопки Частичная сумма. Подробнее о надстройках и их подключении будет рассказано  далее. Из  названия мастера  ясно,  что он позволяет  создать  вызов функции суммирования  диапазона ячеек,  учитывающий некоторые  условия.  На  рис. 2.10  показан  пример  применения мастера  суммирования. В ячейках A1:A10 находятся  числа  от 1 до 10, в ячейку  A15 помещена сформированная мастером формула {=СУММ(ЕСЛИ($A$2:$A$10>5; $A$2:$A$10; 0))}. Условие заключается в том, что для суммирования используются только значения,  большие 5.

Этой  же цели можно достичь  другим  способом. Для  частичного суммирования содержимого  ячеек предназначено несколько функций.  Эти функции позволяют  суммировать ячейки некоторого диапазона с учетом дополнительных условий. Например, в операции суммирования, как и при использовании мастера, не будут участвовать ячейки, в которых находятся числа, меньшие заданного. В данном примере можно применить функцию СУММЕСЛИ, которая предполагает использование одного критерия.  Ячейка A13 содержит формулу  =СУММЕСЛИ(A1:A10;">5";A1:A10). Как видно из ее текста, критерий  суммирования используется тот же самый, но вид формулы  иной. Для более сложных  случаев одновременного использования нескольких  критериев предназначена функция СУММЕСЛИМН.

Во многих случаях вместо функции СУММ можно применять функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;ссылка2;…). Номер_функции — число от 1 до 11 (с включением значений скрытых ячеек) или от 101 до 111 (с исключением скрытых значений), которое указывает, какую функцию использовать при вычислении итогов внутри списка. В качестве функций используются вычисление среднего значения, количества  значений, произведение, сумма и т. д.

На  рис. 2.11 показан  пример  применения такой  функции.  Ячейки  D1:D8 содержат  числовые  значения,  причем  в ячейке D4 находится  функция =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;D1:D3),  а  в ячейке  D9 — формула  =ПРОМЕЖУТОЧНЫЕ. ИТОГИ(9;D1:D8). Вторая строка скрыта. В этом случае значение  первого аргумента функции задает выполнение суммирования. Таким  образом, в ячейке  D4

вычисляется промежуточное значение  суммы. В соседней ячейке E9 находится  формула  обычного суммирования всех ячеек с использованием функции СУММ. Как видно на этом примере, при использовании функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ значение ячейки D4 не участвует в общем суммировании. В ячейке G4 находится  формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;G1:G3), ячейке  G9 — соответственно формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;G1:G8). Отличие формул заключается в различных значениях первого аргумента. Во втором случае значение  скрытых ячеек не учитывается.

Рис. 2.10. Пример частичного суммирования

В ячейках  J9 и K9 находятся  формулы =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;D1:D8) и =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(102;D1:D8). По этим формулам вычисляется количество значений  в указанных  диапазонах ячеек.

Рис. 2.11. Пример использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ


Поиск

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

Функция ВПР имеет несколько аргументов:

✓                    искомое значение (адрес ячейки);

✓                    таблица (диапазон ячеек, в которых следует производить поиск и из которых

будет возвращено значение);

✓                    номер столбца  (номер  столбца  в указанной таблице,  в котором содержится

возвращаемое значение);

✓                    интервальный просмотр (логическое значение, определяющее, точно или при-

ближенно должен производиться поиск).

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

На рис. 2.12 показан пример подготовки массива данных, в которых будет выполняться поиск. Результат поиска показан на рис. 2.13. Ячейки A4, B4, C4 заполняются результатами поиска с использованием функции ВПР.

Рис. 2.12. Массив данных для поиска

Рис. 2.13. Использование функции ВПР

Функция ГПР действует аналогично, но поиск производится не по вертикали, а по горизонтали.

Рассмотрим еще несколько полезных функций, которые могут использоваться при поиске данных в ячейках рабочего листа Excel:

✓                    ПОИСКПОЗ (возвращает относительное положение элемента, который соответ-

ствует указанному  значению в указанном  порядке);

✓                    СЧЁТЕСЛИ (подсчитывает внутри диапазона количество ячеек, удовлетворяю-

щих заданному критерию);

✓                    ИНДЕКС (возвращает значение  или ссылку на него из таблицы  или диапа-

зона).

Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив.

Все эти функции полезны  при автоматизации заполнения бухгалтерских бланков. Рис.  2.14 иллюстрирует применение функции ИНДЕКС и других  функций.  В ячейке  D1 находится вызов  функции =СЧЁТЕСЛИ(A1:A8; "Грачев"), а в ячейке D2 — функции =СЧЁТЕСЛИ(B1:B8;"< 20 000"). В первом случае так вычисляется количество  повторов  в диапазоне  ячеек A1:A8 значения  Грачев. В этом примере  не преобразовываются строчные  и прописные  символы,  поэтому происходит  точное сравнение  строки  символов.  Например, если задать для поиска  строку  грачев, то она не будет найдена. Во втором  случае  среди ячеек B1:B8 вычисляется количество  значений,  меньших  20 000. В ячейке  F1 по формуле =ВПР(C1;A1:B7;2;ЛОЖЬ) по содержимому  ячейки C1 в диапазоне  A1:B7 ищется соответствующая строка, и функция возвращает  значение из второго столбца  указанного  диапазона.  Ячейка  H1 содержит  функцию  =ПОИСКПОЗ(F1;B1:B7;0). Так, в диапазоне  ячеек B1:B7 ищется позиция  ячейки,  содержащей значение ячейки F1. В ячейке J1 происходит вычисление по формуле

=ЕСЛИ(ИНДЕКС(A1:B7; H1; 1) = C1; ИНДЕКС(A1:B7; H1; 2); ""). Первый вызов функции ИНДЕКС по содержимому ячейки $H$1 выполняет поиск в диапазоне  A1:B7. Если при этом возвращается значение, равное содержимому ячейки C1 (с ней сравнивается значение первого столбца указанного  диапазона),  то в ячейку  J1 записывается значение  из второго  столбца  найденной  строки. В противном  случае в ячейку J1 вводится  пустая строка.

Рис. 2.14. Использование функции ИНДЕКС


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

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

И(логическое_значение1;логическое_значение2; …), ИЛИ(логическое_значение1;логическое_значение2;…), ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь), НЕ(логическое_значение).

Источник: Трусов А. Ф. Excel 2007 для менеджеров и экономистов: логистические,  производственные и оптимизационные расчеты (+CD). — СПб.: Питер, 2009. — 256 с.: ил.

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

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

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