1.4 Стандартные функции exсel

Стандартные функции EXСEL разделяются на следующие типы: арифметические, статистические, логические, текстовые, функции даты и времени. Формат функции можно уточнить с помощью команды меню Вставка / Функция. В левом окошечке перечисляются типы функций, в правом – функции текущей группы. Можно вызвать функцию, указав в левом окошечке группу, или же выбрать пункт В алфавитном порядке, а в правом – конкретную функцию. Однако такой способ применим только в тех случаях, если содержимое ячейки представляет собой одну функцию, а не арифметическое выражение или вложенную функцию.

Рассмотрим наиболее употребительные стандартные функции.

К первой группе – арифметических функций – относятся ABS, SIN, COS, EXP, LOG, LOG10 и др. Они используются в основном для математических или технических расчетов.

К той же группе относятся следующие функции:

– функция суммы СУММ, имеющая вид СУММ(r1;r2;…). Здесь r1, r2, … – аргументы. В качестве аргументов могут использоваться имена полей или диапазоны ячеек. Например, результатом функции СУММ(А2:А10) будет сумма содержимого ячеек А2–А10. Если значение полученной суммы не требуется сохранить для дальнейшей работы, то можно поступить следующим образом: выделить ячейки А2–А10, при этом внизу экрана, в строке состояния, появится результат. Кроме суммы, в строке состояния может отображаться среднее арифметическое, количество ячеек в выделенном диапазоне, максимум или минимум в диапазоне. Для определения конкретной операции следует поместить указатель мыши в строке состояния, щелкнуть правую клавишу мыши и выбрать в открывшемся контекстном меню нужный пункт.

– функция СУММЕСЛИ, имеющая вид СУММЕСЛИ(интервал1; критерий;интервал2). Функция суммирует ячейки интервала2, которым соответствуют ячейки интервала1, удовлетворяющие указанному критерию.

Например,

=СУММЕСЛИ(А2:А10;">3,5";B2:B10)

суммирует те ячейки из диапазона В2–В10, которым соответствуют ячейки диапазона А2–А10, значения в которых больше 3,5. Критерий может включать в себя как числовые, так и текстовые значения. Например, студент может получать либо стипендию (СТ), либо социальное пособие (СП). Тогда стипендиальный фонд группы из 25 человек подсчитывается следующим образом:

=СУММЕСЛИ(А2:А26;"=СТ";В2:В26)

Здесь в столбце А указывается вид выплат, а в столбце В – сумма выплат каждого студента.

Другой способ вычисления частичных сумм – с помощью команды меню Сервис / Мастер / Частичная сумма. На первом шаге указывается диапазон ячеек вида $x$n:$z$m. Здесь х и n – координаты левой верхней ячейки диапазона данных, z и m – координаты правой нижней ячейки диапазона данных, после чего щелкнуть кнопку Далее. Появляется вкладка с надписями и окошечками параметров. Первая надпись имеет вид: Задайте столбец, который нужно суммировать, под ней в окошечке Суммировать указывается требуемое поле. Вторая надпись имеет вид: Задайте анализируемый столбец, операцию сравнения и значение, с которым будет сравниваться этот столбец. В соответствующих окошечках указывается имя поля, логическое отношение и значение, после чего щелкнуть кнопку Добавить условие. При необходимости можно добавлять другие условия, в том числе и накладываемые на другие поля; по окончании формирования набора условий щелкнуть кнопку Далее. На следующем шаге предлагается копирование только результата или всей формулы; обычно выбирают первое, после чего щелкнуть кнопку Далее. На последнем этапе определяется адрес ячейки для помещения результата и следует щелкнуть кнопку Готово.

Пример: пусть в первом столбце содержатся даты в течении месяца, во втором фамилии двух врачей – Иванов и Петров, а в третьем – количество пациентов, принятых конкретным врачом в соответствующий день. Подсчитать, сколько всего принял пациентов каждый из врачей. Переходим в указанные пункты меню, на вкладке указываем параметры Количество пациентов, Врач, =, Иванов, затем указываем флажок в пункте Копировать значение суммы и определяем адрес ячейки для общего количества пациентов врача Иванова. Затем те же действия повторяем для врача Петрова.

Пример: пусть в базе данных “Новорожденные” требуется подсчитать количество девочек с весом 2500–3000 г. Для этого выбираем в окошечке Суммировать поле Фамилия (при этом, естественно, суммируются не фамилии, а фактически происходит подсчет количества детей), затем в качестве анализируемого столбца – поле Пол, операцию сравнения – =, значение –Ж и щелкаем кнопку Добавить условие, затем в качестве анализируемого столбца – поле Вес, в качестве операции сравнения – >=, в качестве значения – 2500 и снова щелкаем кнопку Добавить условие, затем в качестве анализируемого столбца снова указываем Вес, в качестве операции сравнения – <=, а в качестве значения – 3000, после чего щелкаем кнопку Добавить условие. Таким образом, формируются три условия одновременно (рис. 1.7).

Из второй группы функций – статистических – наибольшее значение имеют следующие:

– МАКС(диапазон) – результатом является максимальное значение в указанном диапазоне. Например, если в ячейках А4–А7 расположены соответственно числа 1, 3, 5, 2, то результатом МАКС(А4:А7) будет число 5.

– МИН(диапазон) – результатом является минимальное значение в указанном диапазоне.

ВНИМАНИЕ! При применении функций МИН и МАКС к данным вида ДАТА, могут возникать ошибки. В этом случае следует перевести данные из типа ДАТА в числовой формат, а затем применить функции МИН или МАКС.

– СРЗНАЧ(диапазон) – вычисляет среднее арифметическое указанного диапазона. Так, для вычисления среднего балла студентов (см. пример выше), можно использовать функцию вида СРЗНАЧ(В2:D2), которую затем копируем в другие ячейки столбца. Быстрое вычисление среднего арифметического можно получить с помощью строки состояния (см. функцию СУММ).

Рис. 1.7.

– СЧИТАТЬПУСТОТЫ(диапазон) – подсчитывает количество пустых ячеек в диапазоне.

– СЧЕТЗ(диапазон) – напротив, подсчитывает количество непустых ячеек в диапазоне.

– СЧЕТЕСЛИ(диапазон;критерий) – подсчитывает количество ячеек в диапазоне, удовлетворяющих критерию. В качестве диапазона можно задавать имя поля. Например, в столбце С в ячейках с 2-ю по 100-ю указывается социальное положение пациентов поликлиники. Для того, чтобы подсчитать количество пенсионеров среди них, используем функцию

=СЧЕТЕСЛИ(С2:С100;"=пенсионер")

или

=СЧЕТЕСЛИ(С2:С100;"=пенс*")

или

=СЧЕТЕСЛИ(СОЦ.ПОЛ;"=пенсионер")

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

какому-либо одному условию. Так, если в столбце D указывается пол, а в столбце N – возраст пациентов поликлиники, то средний возраст женщин вычисляется так:

=СУММЕСЛИ(D2:D100;"=Ж";N2:N100)/СЧЕТЕСЛИ(D2:D100;"=Ж")

или

=СУММЕСЛИ(Пол;"=Ж";Возраст)/СЧЕТЕСЛИ(Пол;"=Ж")

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

Другой способ вычисления средней величины для данных, удовлетворяющих некоторому одному условию – с использованием команды меню Данные / Итоги. Вначале необходимо отсортировать данные по указанному критерию, затем перейти в меню Данные / Итоги. Появляется вкладка с надписями и окошечками параметров. Первая надпись на вкладке имеет вид: При каждом изменении в. Под ней в окошечке полей следует указать выбранное поле. Следующая надпись имеет вид: Операция, в соответствующем окошечке следует выбрать желаемую – сумма, количество, среднее максимум или минимум. Затем указать поле, для которого вычисляется результат, после чего установить флажок (знак ) только в окошечке Итоги под данными и щелкнуть клавишу ОК. Под каждой группой данных появится результат.

Пример тот же – вычисление среднего возраста женщин-пациентов. Сортируем по полю Пол, переходим в пункт меню Данные / Итоги, устанавливаем в окошечках параметры Пол, Среднее и Возраст. Под каждой группой – мужчин и женщин – появляется средний возраст.

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

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер функции;диапазон)

Третью группу функций – логических – представляют функции:

– ЕСЛИ(логическое выражение;значение1[;значение2])

Здесь знак [ ] обозначает необязательный параметр. Значение 1 – это значение функции в том случае, если логическое выражение имеет значение ИСТИНА, значение2 – если ЛОЖЬ. Логическое выражение может состоять только из логического отношения или включать в себя другие логические функции.

Пример:

=ЕСЛИ(А2>10;А2^2;0)

Значением функции будет квадрат значения ячейки А2, если оно больше 10, и 0 в противном случае.

Если функция ЕСЛИ содержит только логическое отношение, то удобно использовать команду меню Вставка / Функция / Логические / Если с указанием соответствующих параметров.

– И(логическое значение1;логическое значение2;…) – содержит от 1 до 30 логических выражений (чаще отношений). Функция имеет значение ИСТИНА, если все аргументы истинны и ЛОЖЬ, если хотя бы один имеет значение ЛОЖЬ.

Пример: пусть в ячейку А2 записано число 5, а в ячейку В3 – значение 10. Тогда функция =И(А2>3;B3<20) имеет значение ИСТИНА, а функция =И(А2>3;B3>12) – значение ЛОЖЬ.

– ИЛИ(логическое значение1;логическое значение2;…) также содержит до 30 значений. Она принимает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и ЛОЖЬ, если все аргументы ложны.

– НЕ(аргумент) – изменяет значение логического аргумента на противоположное (ЛОЖЬ на ИСТИНА и наоборот).

Функции И, ИЛИ, НЕ используются в основном как вложенные функции в функции ЕСЛИ.

Пример. Требуется подсчитать количество пациентов, у которых хотя бы один из показателей – гемоглобин, лейкоциты или лимфоциты ниже нормы. Предположим, что показатели гемоглобина расположены в ячейках D2–D10, лейкоцитов в E2–E10, лимфоцитов – в F2–F10. В любой свободный столбец, например К, в ячейку К2 вводим формулу

=ЕСЛИ(ИЛИ(D2<12;C2<4;F2<18);1;0)

и копируем ее в ячейки К3–К10. В результате в ячейках К2–К10 будут размещаться единицы (если хотя бы один из показателей конкретного пациента ниже нормы) или нули (если все показатели данного пациента больше нижней границы нормы). Выделяем ячейки К2–К10 и щелкаем пиктограмму Σ на панели инструментов. В результате в ячейке К11 будет число, равное сумме единиц в выделенных ячейках, дающее в результате количество интересующих нас пациентов.

Четвертая группа функций – функции даты и времени. К ним относятся:

– ВРЕМЗНАЧ(время) – преобразует время в обычном формате в число от 0 до 1. Например, 12:20 переходит в число 0,51.

– ВРЕМЯ(часы;минуты;секунды) – аналогичное действие.

– ГОД(дата) – выделяет из даты год в виде числа.

– МЕСЯЦ(дата) – выделяет из даты месяц в виде числа.

– ДЕНЬ(дата) – выделяет из даты день в виде числа.

– ДЕНЬНЕД(дата;тип) – переводит дату из числового или обычного формата в день недели (от 1 до 7). Для привычной для нас нумерации с понедельника следует указать в качестве типа число 2.

– ДАТАМЕС(дата) – определяет дату, отстоящую от указанной даты на заданное число месяцев.

– СЕГОДНЯ – функция без аргументов, определяет текущую дату.

– ТДАТА – без аргументов, действует аналогично предыдущей, но кроме даты определяет еще и время.