Стандартные функции Excel

Вычисления и функции в EXСEL

На основе введенных данных в EXСEL можно производить разного рода вычисления. Для вычислений в соответствующую ячейку вводим формулу, т.е. выражение, состоящее из адресов ячеек (абсолютных и относительных), знаков арифметических действий (+, -, *, /, ^), стандартных функций (см.ниже) и начинающееся обязательно со знака =.

Так, например, имеется база данных вида:

A	B	C	D
Фамилия	Математика	Физика	Химия
Воронин
Аксенушкин
Кондратьев
Иванов

Требуется вычислить средний балл каждого студента. Эти вычисления можно проделать следующим образом:

1. выделить ячейку E2, ввести запись вида

=(B2+C2+D2)/3

и нажать клавишу ENTER. В ячейке Е2 появится значение среднего балла Воронина. Далее копируем содержимое ячейки Е2 в ячейки Е3–Е5. Копирование формул производится следующим образом: подводим курсор мыши к правому нижнему углу ячейки Е2 (он принимает вид тонкого черного крестика) и, нажав левую клавишу мыши и не отпуская ее, протаскиваем курсор мыши до ячейки Е5.

2. выделить ячейки Е2–Е5, затем в строку формул ввести формулу

=(Математика+Физика+Химия)/3

и нажать комбинацию клавиш CTRL + SHIFT + ENTER. Здесь формула включает в себя имена полей.

2. ввести в ячейку Е2 специальную функцию среднего арифметического (см. раздел “Функции EXСEL”) и скопировать ее в ячейки Е3–Е5.

Пример. Пусть в ячейках В2-В50 записаны даты получения студентом индивидуального задания, а в ячейках С2-С50 – даты представления задания преподавателю. Для определения периода выполнения задания в ячейку D2 вводим формулу

=С2-В2

и копируем ее вниз.

Пример. Пусть в ячейках В2-В50 записаны даты получения студентом индивидуального задания, нормативный срок выполнения составляет 10 дней. Требуется рассчитать контрольную дату представления задания.

В ячейку С2 вводим формулу

=В2+10

и копируем ее вниз.

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

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

К первой группе – арифметических функций – относятся 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.11).

В Excel 2007 наряду с мастером суммирования используется функция СЧЕТЕСЛИМН, имеющая формат

СЧЕТЕСЛИМН(диапазон1;условие1; диапазон2;условие2;…)

Так, если в предыдущем примере пол детей был записан в ячейках С2-С50, рост в ячейках D2-D50, а вес в ячейках Е2-Е50, то функция СЧЕТЕСЛИМН примет вид

=СЧЕТЕСЛИМН(С2:С50;”Ж”;D2:D50;”>=2500”;D2:D50;”<=3000”)

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

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

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

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

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

Рис. 1.11.

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

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

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

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

или

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

или

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

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

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

или

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

В Excel 2007 существует функция СРЗНАЧЕСЛИ, заменяющая указанную конструкцию. Она имеет формат

СРЗНАЧЕСЛИ(диапазон1;”условие”;диапазон2)

Так, для представленного примера функция СРЗНАЧЕСЛИ примет вид

=СРЗНАЧЕСЛИ(D2:D100;"=Ж";N2:N100)

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

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

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

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

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

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

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

– ЕСЛИ (логическое выражение;значение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 будет число, равное сумме единиц в выделенных ячейках, дающее в результате количество интересующих нас пациентов.

Пример. Отметить знаком «*» сотрудников коллектива, имеющих оклад выше среднего в коллективе.

Задача решается в два действия: сначала вычисляется средняя зарплата, для чего в любую свободную ячейку, например, в В30, вводим формулу

=СРЗНАЧ(В2:В25)

Затем в ячейку G2 вводим формулу

=ЕСЛИ(В2>$b$30;”*”;””)

и копируем ее вниз.

ВНИМАНИЕ! Если условие наложено на дату или время, то дата(время) должны иметь числовой формат!

Пример. Пусть в ячейках В2-В25 записаны даты рождения сотрудников. Отметить знаком «*» лиц, родившихся ранее 1960 года.

В любую свободную ячейку, например, В30, записываем дату 01.01.1960 и переводим ее в числовой формат. В результате в ячейке В30 будет записано число 21916, а формула примет вид

=ЕСЛИ(В2<21916;”*”;””)

Допустимо использование формата функции в виде

= ЕСЛИ(В2<$B$30;”*”;””)

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

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

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

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

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

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

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

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

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

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

Пример. Пусть в ячейках В2-В25 записаны даты рождения сотрудников, требуется вычислить возраст каждого из них. Формула принимает вид

=ГОД(СЕГОДНЯ())-ГОД(В2)

после чего формула копируется вниз.

Дата добавления: 2014-11-20