Основные встроенные функции Microsoft Excel

Основные встроенные функции Microsoft Excel

 

Cинтаксис функций

Функции — это стандартные формулы, которые обеспечивают выполнение определенного набора операций над заданным диапазоном величин.

Каждая функция состоит из следующих трех элементов:

v Знак равенства (=), который показывает, что далее следует функция (или формула).

v Название функции (например, СУММ) определяет операции, которые будут выполняться.

v Аргумент, например (A1:H1) показывает адреса ячеек тех величин, с которыми будет работать функция.

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции - как, например, СУММ (SUM) или СРЗНАЧ (AVERAGE) - описывает операцию, которую эта функция выполняет. Аргумен­ты задают значения или ячейки, используемые функцией. Например, в формуле =СУММ(СЗ:С5) СУММ - это имя функции, а СЗ:С5 - ее единственный аргумент. Эта формула суммирует числа в ячейках СЗ, С4 и С5.

Знак равенства (=) в начале формулы означает, что введена именно формула, а не текст (например, комментарий или заголовок таблицы). Если вы опустите знак равенства. Excel воспримет ваш ввод просто как текст СУММ (СЗ:С5).

Обратите внимание, что аргумент функции заключен в круглые скобки. Откры­вающая скобка отмечает начало аргумента и ставится сразу после имени функции. Если вы введете между именем и открывающей скобкой пробел или другие символы, то в ячейке будет отображено ошибочное значение #ИМЯ? (#NAME?).

Некоторые функции, такие как ПИ (Р1) и ИСТИНА (TRUE), не имеют аргументов. (Как вы увидите позже, эти функции обычно используются в формулах или в других функциях). Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки, как в следующем примере:

=А1*ПИ()

Использование аргументов

 

При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, формула

=ПРОИЗВЕД (С1;С2;С5) указывает Excel, что необходимо перемножить числа в ячейках С1, С2 и С5. В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, функция =СУММ (А1:A5;C2:CIO;D3:DI7) имеет три аргумента, но суммирует числа в 29 ячейках. (Первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т. д.) Указанные в ссылке ячейки в свою очередь могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций.

 

Выражения как аргументы

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

=СУММ(Sin(А1*ПИ());2*СО5(А2*ПИ()));

SIN(AI*ПИ()) и 2*COS(A2* ПИ())

- это выражения, которые вычисляются и используются в качестве аргументов функции СУММ.

 

Типы аргументов

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

 

Числовые значения

Аргументы функции могут быть числовыми. Например, функция СУММ в формуле =СУММ(327;209;176) суммирует числа 327, 209 и 176. Обычно вы вводите в ячейки листа числа, которые хотите использовать, и затем применяете ссылки на эти ячейки в качестве аргументов в функциях.

Текстовые значения

В качестве аргумента функции могут использоваться текстовые значения. На­пример, в формуле

=ТЕКСТ(ТДАТА();''Д МММ ГГГГ")

второй аргумент функции ТЕКСТ, "Д МММ ГГГГ", является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА, в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.

 

Логические значения

Аргументы ряда функций могут принимать только логические значения ИСТИ­НА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в формуле

=ЕСЛИ(А3=ИСТИНА; "Новая";" Старая")&"цена")

является логическим выражением, которое использует значение в ячейке А3. Если это значение ИСТИНА, то выражение A3=ИCTИHA возвращает ИСТИ­НА, и функция ЕСЛИ возвращает Новая, а формула в целом возвращает текстовое значение Новая цена. См.Рисунок 1.

 

 

Рисунок 1.

Функция ABS

Функция ABS (ABS) возвращает абсолютное значение числа или формулы и имеет следующий синтаксис:

=АВS(число)

Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Например, если ячейка А1 содержит число -75, формула =ABS(AI) возвратит значение 75. Если число, задаваемое аргументом, положительное, ABS возвращает это число неизмененным. Рисунок 5.

 

Рисунок 5.

Функция ЗНАК

 

Функция ЗНАК определяет, является ли аргумент отрицательным, положительным или нулевым значением и имеет следующий синтаксис:

=ЗНАК(число)

Аргумент число может быть числом, ссылкой на ячейку, в которой содержится число, или формулой, возвращающей числовое значение. Если число положи­тельное, функция ЗНАК возвращает значение 1; если число отрицательное, ЗНАК возвращает значение -1; если число равно О, ЗНАК возвращает О. Например, предположим, что ячейки от А1 до АЗ содержат числа 10, -20 и -5. Формула

=ЗНАК (СУММ(А1:АЗ))

складывает три числа (сумма равна -15) и возвращает значение - 1. Рисунок 6.

Рисунок 6

Функция ФАКТР

 

Функция ФАКТР (FACT) вычисляет факториал числа. (Факториал числа - это произведение всех положительных целых чисел начиная от 1 до заданного числа. Например, 3 факториал обозначается как 3! и равняется 1х2хЗ, то есть 6. Эта функция имеет следующий синтаксис:

=ФАКТР (число)

Аргумент число должен быть положительным целым числом. Например, ФАКТР(1) возвращает 1, но ФАКТР(-1) возвращает ошибочное значение #ЧИСЛО! Если число нецелое, ФАКТР отбрасывает десятичные знаки без округления для создания целого числа перед вычислением факториала.

Например, для вычисления 101 используйте формулу =ФАКТР(10). Эта формула возвращает значение 3628800.

 

Функция ПРОИЗВЕД

 

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

=ПРОИЗВЕД(число 1;число2;….)

Функция ПРОИЗВЕД может иметь до 30 аргументов. Excel игнорирует любые пустые ячейки, текстовые и логические значения.

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

 

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

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

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

Если число меньше, чем делитель, то значение функции равно аргументу число. Например, функция =OCTAT(5;11)

возвратит значение 5. Если число точно делится на делитель, функция возвра­щает О. Если делительравен О, ОСТАТ возвращает ошибочное значение #ДЕЛ/О!. Cм. Рис.8-10.

 

Рисунок 8.

 

 

Рисунок 9.

 

Рисунок 10.

 

 

Функция КОРЕНЬ

Функция КОРЕНЬ (SORT) возвращает положительный квадратный корень из числа и имеет следующий синтаксис:

=КОРЕНЬ(число)

Аргумент число должен быть положительным числом. Например, функция =КОРЕНЬ(4) возвратит значение 2. Если число отрицательное, функция КОРЕНЬ возвращает ошибочное значение # число!.См. Рисунок 11.

 

Рисунок 11.

Функция ЕЧИСЛО

Функция ЕЧИСЛО определяет, является ли значение числом, и имеет следующий синтаксис:

=ЕЧИСЛО (значение)

Предположим, что вы хотите узнать, является ли значение в ячейке АЗ числом. Формула =ЕЧИСЛО(А5) возвратит значение ИСТИНА, если ячейка А5 содержит число или формулу, возвращающую число; в противном случае она возвратит ЛОЖЬ. См. Рисунок 12.

 

 

Рисунок 12.

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

Excel поддерживает пять встроенных логарифмических функций: LOG10, LOG, LN, ЕХР и СТЕПЕНЬ (POWER). Надстройка Пакет анализа предоставляет еще несколько дополнительных и более сложных логарифмических функций.

Функция LOC10

Функция LOG1O возвращает логарифм заданного значения по основанию 10 и имеет следующий синтаксис:

Аргумент число должен быть положительным числом. Если число отрицатель­ное, функция возвращает ошибочное значение #ЧИСЛО!. Например, формула LOG10(100) возвратит значение 2.

 

Функция LOG

Функция LOG возвращает логарифм положительного числа по заданному основанию. Эта функция имеет следующий синтаксис:

= LOG(число; снование)

Например, формула =LOG(5;2) возвратит значение 2,321928095, то есть логарифм 5 по основанию 2. Если вы укажете аргумент основание, Excel примет его равным 10.

Функция LN

Функция LN возвращает натуральный (по основанию е) логарифм положительного числа, указанного в качестве аргумента. Эта функция имеет следующий синтаксис:

= LN(число )

Например, формула =LN(2) возвратит значение 0,693147.

 

 

 

Рисунок 13.

Функция ЕХР

Функция ЕХР вычисляет значение константы е (приблизительно 2,71828183), возведенной в заданную степень. Эта функция имеет следующий синтаксис:

=ЕХР(число)

Например, формула =ЕХР(2) возвратит значение 7,389056099 (2,718281828х2,718281828). Функция ЕХР является обратной по отношению к LN. Например, если ячейка А1 содержит формулу LN(8) то формула =ЕХР(А1) возвратит значение 8. (См. Рисунок 14. Рисунок 15.)

 

 

Рисунок 14.

 

 

Рисунок 15.

 

Функция СТЕПЕНЬ

 

Функция СТЕПЕНЬ возводит число в заданную степень и имеет следующий синтаксис:

=СТЕПЕНЬ(число; степень)

Например, вычислить выражение. (См. Рисунок 16.)

 

 

Рисунок 16.

Текстовые функции

Текстовые функции преобразуют числовые текстовые значения в числа и числовые значения в строки символов (текстовые строки), а также позволяют выполнять над строками символов различные операции.

Функция ЗНАЧЕН

 

Если вы ввели в ячейки числа в текстовом формате (заключив их в кавычки), то для преобразования их в числовые значения можно использовать функцию ЗНАЧЕН. Эта функция имеет следующий синтаксис:

=ЗНАЧЕН (текст)

Аргумент текстможет быть строкой, заключенной в двойные кавычки, или ссылкой на ячейку, в которой содержится текст. Преобразуемая текстовая строка может быть в любом допустимом формате, в том числе и в пользователь­ском. Если текст не удовлетворяет ни одному из этих форматов, то Excel возвращает значение ошибки #ЗНАЧ!.

Например, формула =ЗНАЧЕН("40205") возвратит числовое значение 40205. Если ячейка А10 содержит формулу ="40205" (которая возвращает текстовое значение), то формула ЗНАЧЕН(А10) также возвратит 40205.

Кроме того, функция ЗНАЧЕН может преобразовывать текстовые значения даты и времени в числовые значения. Например, формула=ЗНАЧЕН("1.01.87") возвратит десятичное значение даты 31778.Поскольку Excel при вычислениях преобразует числовые тексты в числа, обычно нет необходимости в использовании функции ЗНАЧЕН для чисел, введенных в формулы в текстовом формате.

.

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

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

Логические выражения

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

=А1>А2

=5-3<5*2

=СРЗНАЧ(В1:В6)=СУММ(6;7;8) =С2="Среднее”

=СЧЁТ(А1:А10)=СЧЁ'Г(B1:В10)

=ДЛСТР(А1)=10

Любое логическое выражение должно содержать, по крайней мере один оператор сравнения, который определяет отношение между элементами логического вы­ражения. Например, в логическом выражении А1>А2 оператор больше(>) сравнивает значения в ячейках А1 и А2.

Результатом логического выражения является или логическое значение ИСТИНА(1), или логическое значение ЛОЖЬ (О). Например, логическое выражение A1=10 возвратит значение ИСТИНА, если значение в ячейке A1равно 10, и ЛОЖЬ, если значение в ячейке A1содержит любое другое значение. (См. Рисунок 17.)

 

Рисунок 17.

Функция ЕСЛИ

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

= Если (логическое выражение; значение - если истина; значение – если ложь )

Например, формула =ЕСЛИ (А1<22;5;10) возвращает число 5, если значение в ячейке А6 меньше 22. В противном случае она возвращает 10. См. Рисунок 18.

 

Рисунок 18.

В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, формула =ЕСЛИ (СУММ (А1:А4)>0;СУММ (А1:А4);0) возвратит сумму значений в ячейках от А1 до А10, если эта сумма положительна. В противном случае формула возвратит О. См. Рисунок 19.

 

 

Рисунок 19.

 

В функции ЕСЛИ можно также использовать текстовые аргументы. Например, лист, представленный на Рисунок 20, содержит результаты экзаменов для группы студентов.

Формула в ячейке B5 =ЕСЛИ(B1>80%;"Сдал"; "Не сдал")

 

 

 

Рисунок 20.

проверяет средний балл, содержащийся в ячейке B1. Если он оказывается больше 80%, функция возвращает текст Сдал,если же средний балл меньше или равен 80%, функция возвращает текст Не сдал.

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

=ЕСЛИ (СУММ (А1:А10)>0;СУММ (А1:А10);"")

возвратит, пустую строку (“”), если логическое выражение имеет значение ЛОЖЬ. Аргумент логическое - выражениефункции ЕСЛИ может содержать текстовое значение. Например, формула

=ЕСЛИ (А1="Тест";100;200)

возвратит значение 100, если ячейка А1 содержит строку Тест,и 200, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

 

Функции И, ИЛИ и НЕ

 

Три дополнительные функции - И (AND), ИЛИ (OR) и НЕ (NOT) - позво­ляют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, >, <, >=, <= и<>. Функции И и ИЛИ, допускающие до 30 логических аргументов, имеет следующий синтаксис:

=И (логическое значение 1; логическое значение2;... логическое значение З0)

Функция И возвращает логическое значение ИСТИНА, если все логические выражения истинны.

=ИЛИ (логическое значеиие1; логическое значение2;..;логическое значение З0)

Функ­ция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одноиз логических выражений истинно.

Функция НЕ имеет только один аргумент и следующий синтаксис:

= НЕ (логическое значение)

Функция НЕ меняет значение своего аргумента на противоположное логическое значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значение ИСТИНА.

Аргументы функций И, ИЛИ и НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Предположим, вы хотите, чтобы Excel возвратил текст Сдал,если студент имеет средний балл больше 80 и меньше 5 пропусков занятий без уважительных причин. В листе, представленном на Рисунок 21, мы использовали для этого формулу

=ЕСЛИ(И(B2<5;C2>80%);"Сдал"; "Не сдал")

 

 

 

Рисунок 21.

 

Хотя функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Например, формула

=ЕСЛИ(ИЛИ(B2<5;C2>80);"Сдал"; "Не сдал")

 

Рисунок 22.

возвратит текст Сдал, если средний балл больше 80 или если студент имеет меньше 5 пропусков занятий без уважительных причин. См. Рисунок 22.

Формула

=ЕСЛИ (НЕ(А1=2);"Прошел"; "Не прошел")

возвращает текст Прошел,если значение в ячейке А1 не равно 2. См. Рисунок 23.

 

Рисунок 23.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предлагают альтернативный способ представления логических условий ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

= ИСТИНА()

=ЛОЖЬ()

Например, предположим, что ячейка В5 Формула

=ЕСЛИ(В3=ЛОЖЬ();"Внимание!"; "ОК")

возвратит Внимание! если логическое выражение в ячейке В3 имеет значение ЛОЖЬ, а иначе она возвратит ОК. См. Рисунок 24.

Рисунок 24.

 

Вложенные функции ЕСЛИ

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

=ЕСЛИ(А1=100;''Всегда";ЕСЛИ(И(А1>=80;А1<100);''Обычно"; ЕСЛИ(И (А1>=60;А1<80);"Иногда"; "Увы!")))

использует три функции ЕСЛИ. Если значение в ячейке А1 является целым числом, формула читается следующим образом: «Если значение в ячейке А1 равно 100, возвратить строку Всегда.В противном случае, если значение в ячейке А1 находится между 80 и 100 (точнее, от 80 до 99 включительно), возвратить строку Обычно. В противном случае, если значение в ячейке А1 находится между 60 и 80 (от 60 до 79 включительно), возвратить строку Иногда.И наконец, если ни одно из этих условий не выполняется, возвратить строку Увы!См.Рис.26.

 

Рисунок 26.

 

Всего можно использовать до семи уровней вложения функций ЕСЛИ, не выходя при этом, конечно, за пределы максимальной длины значения в ячейке (255 символов).

 

Функции дат и времени

 

Функции дат и времени Excel позволяют выполнять вычисления в рабочем листе быстро и точно. Например, если рабочий лист используется для ведения ежемесячной платежной ведомости фирмы, вы можете использовать функцию ЧАС (HOUR) для определения числа рабочих часов и функцию ДЕНЬНЕД для определения ставки оплаты: стандартной (с понедельника по пятницу) или повышенной (с учетом суббот и воскресений).

 

Функция ДАТА

 

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

= ДАТА (год, месяц, день)

Аргументы в функции ДАТА располагаются по убыванию значимости: год, месяц, день.

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

Например, если использовать формулу

=ДАТА(2003;03;05)

для ввода даты 3 марта 2003г., результатом функции будет десятичное значение даты 37685, которое Excel выведет как 05.03.03.

Excel «творчески» интерпретирует аргументы функции ДАТА. Если вы введете функцию ДАТА с аргументом День, превосходящим число дней в указанном месяце. Excel просто продолжит счет дней дальше в следующие месяцы.

Например, при вводе формулы =ДАТА(03;03;50) Excel сохранит десятичное значение даты для 19 апреля 2003 г. Такая гибкость бывает очень удобной при выполнении вычислений с датами.

Аргумент деньможет быть любым, если он не превосходит максимальное десятичное значение даты 65380. Подобным образом аргумент месяц может быть больше 12. В этом случае при интерпретации аргументов функции ДАТА Excel считает месяцы дальше в последующие годы.

При использовании 0 в качестве значения аргумента деньфункции ДАТА Excel интерпретирует это значение как последний день предыдущего месяца.

Напри­мер, при вводе =ДАТА(03;3;0) Excel возвращает десятичное значение для отображаемой даты 28 февраля 2003 г. Подобным образом при вводе 0 в качестве значения аргумента месяц отображается декабрь предыдущего года.

Например, при вводе =ДАТА(03;0;0) Excel сдвигает назад значение месяца и дня и сохраняет десятичное значение даты для 30 ноября 2002 г.

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

Например, при вводе =ДАТА(2003;8;-6) Excel сохраняет десятичное значение даты для 25 июля 2003 г.

Excel может также вычислять значения аргументов функции ДАТА. Например, чтобы вывести дату за 26 дней до 3 сентября 2003 г., можно использовать формулу

=ДАТА(2003;9;3-26) Результат будет равен 37476, или 08.08.03.

 

 

Функция СЕГОДНЯ

 

Функция СЕГОДНЯ является специальной формой функции ДАТА. В то время как ДАТА возвращает десятичное значение любой даты, СЕГОДНЯ всегда возвращает десятичное значение текущей даты. Функция СЕГОДНЯ имеет следующий синтаксис:

=СЕГОДНЯ()

Хотя эта функция не имеет аргументов, не забудьте включить пустые круглые скобки. Используйте эту функцию, если хотите, чтобы в ячейке рабочего листа посто­янно отображалась текущая дата.

 

Функция ДЕНЬНЕД

 

Функция ДЕНЬНЕД возвращает день недели для заданной даты и имеет следующий синтаксис:

= ДЕНЬНЕД(десятичная дата; тип)

См. Рисунок 27.

 

Рисунок 27.

Аргумент десятичная - датаможет быть десятичным значением даты, ссылкой на ячейку, которая содержит функцию даты или десятичное значение даты, либо такой текст, как 25.03.03или 25 марта 2003.Если вы используете текст, обязательно заключите его в кавычки.

Функция ДЕНЬНЕД возвращает число, представляющее собой день недели для заданной даты. Необязательный аргумент типопределяет тип представления результата. Если тип равен 1 или опущен, функция возвращает число от 1 до 7, где 1 - воскресенье и 7 - суббота. Если тип равен 2, функция возвращает число от 1 до 7, где 1 - понедельник и 7 - воскресенье. Если типравен 3, функция возвращает число от 0 до 6, где 0 - понедельник и 6 - воскресенье.

 

Функции ГОД, МЕСЯЦ и ДЕНЬ

Функции ГОД, МЕСЯЦ и ДЕНЬ возвращают год, месяц и день для заданного десятичного значения даты/времени. Эти функции имеют следующий синтаксис:

= ГОД (десятичная дата)

= МЕСЯЦ (десятичная дата)

= ДЕНЬ (десятичная дата)

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

Результатом этих функций является значение соответствующей части даты, заданной аргументом десятичная дата. Например, если ячейка A1содержит дату 25.03.2003 то формула

=ГОД(А1)

возвратит значение 2003.

формула

=МЕСЯЦ (А1)

возвратит значение 3.

формула

=ДЕНЬ(А1)

возвратит значение 25.

 

 

Функции ДАТАЗНАЧ и ВРЕМЗНАЧ

 

Функция ДАТАЗНАЧ преобразует дату в десятичное значение даты. Она аналогична функции ДАТА за исключением того, что использует текстовый аргумент. Функция ДАТАЗНАЧ имеет следующий син­таксис:

=ДАТАЗНАЧ(текст даты)

Аргумент текст даты представляет собой любую дату между 1 января 1900 г. и 31 декабря 2078 г. в любом из встроенных форматов даты Excel. (Текст необходимо заключать в кавычки.) Например, формула

=ДАТАЗНАЧ("25.03.03")

возвратит десятичное значение даты 37705. См. Рисунок 28.

 

Рисунок 28.

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

Функция ВРЕМЗНАЧ преобразует время в десятичное значение. Она аналогична функции ВРЕМЯ за исключением того, что вы должны ввести текстовый аргумент. Функция ВРЕМЗНАЧ имеет следующий синтаксис:

=ВРЕМЗНАЧ (текст времени)

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

=ВРЕМЗНАЧ("4:30 РМ")

функция возвратит десятичное значение времени 0,6875.

Функция ДНЕЙЗ60

Некоторые вычисления с датами, являющиеся обыденными при обращении ценных бумаг, используют “искусственны” 360-дневный год, который содер­жит двенадцать месяцев по тридцать дней. Если нужно выполнить такие вычисления, вместо вычитания одной даты из другой используйте функцию ДНЕЙЗ6О. Эта функция имеет следующий синтаксис:

= ДНЕЙЗ60 (начальная дата; конечная дата; метод )

Например, чтобы определить число дней между 24марта 2003 г. и 1июня 2003 основываясь на 360-дневном годе, используйте формулу

=ДНЕЙЗ60("24.03.03";"01.06.03").См. Рисунок 29.

Рисунок 29.

 

которая возвращает 67. Обратите внимание, что если в качестве аргументов начальная датаи конечная датавместо десятичных значений дат использу­ются даты, введенные “в формате”, вы должны заключить их в кавычки. В качестве альтернативы можно использовать ссылки на ячейки, которые содержат начальную и конечную даты. Например, если ячейка А1 содержит значение 24.03.03 и в ячейке А2 записано 01.06.03, можно определить интервал между этими датами, используя формулу

=ДНЕЙЗ60(А1;А2). См. Рисунок 30.

Рисунок 30.

Необязательный аргумент методможет принимать значение 1 (ИСТИНА) или 0 (ЛОЖЬ). Если вы введете 1, то используется европейский метод вычисления. Это значит, что если число месяца начальной даты равно 31, то вместо него используется 30, а если число месяца конечной даты равно 31, используется первый день следующего месяца (за исключением случая, когда число месяца начальной даты равно 30 ). При этом вместо 28 или 29 февраля всегда исполь­зуется 30 февраля.

 

ФУНКЦИИ СЧЕТ И СЧЁТЗ

 

Функция СЧЕТ определяет количество ячеек в заданном диапазоне, которые содержат числа, в том числе - даты и формулы, возвращающие числа. Эта функция имеет следующий синтаксис:

= СЧЕТ(значение;значение2;...)

Например, в листе, представленном на Рисунок 31.

 

 

Рисунок 31.

 

=СЧЁТ(A1:A9) возвратит значение 5 - число ячеек в диапазоне (A1:A9) содержащих числа и дату. Функция СЧЕТ игнорирует ячейки, которые не содержат чисел. Функция СЧЕТ учитывает только числовые значения в диапазоне и игнорирует пустые ячейки и ячейки, содержащие текстовые, логические или ошибочные значения.

Для определения количества непустых ячеек (независимо от их содержимого) используется Ф ункция СЧЁТЗ Эта функция имеет следующий синтаксис:

= СЧЕТЗ ( значение1;значение2;...)

Формула = СЧЁТЗ(A1:A9) возвратит значение 9, так как диапазон (A1:A9) содержит пять и четыре текстовых значения.

 

Функции СУММЕСЛИ и СЧЁТЕСЛИ

 

Функция СУММЕСЛИ аналогична функции СУММ (SUM), но про­веряет каждую ячейку в диапазоне, прежде чем добавить ее к итогу. Эта функция имеет следующий синтаксис:

= СУММЕСЛИ(диапазон; условие; диапазон суммирования)

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

 

 

Рисунок 32.

Например, в листе, представленном на рис.8 найдено суммарная стипендия

для студентов, имеющих количество пропусков >3.

Для вычисления общих затрат на выплату заработной платы сотрудникам младше 35 лет можно использовать формулу

=СУММЕСЛИ (G2:G19;"<35";F2:F19)

Подобным образом функция СЧЕТЕСЛИ определяет количество ячеек, которые удовлетворяют заданному критерию. Функция имеет следующий синтаксис:

= СЧЕТЕСЛИ (диапазон; критерий)

Рисунок 33.

На рис.33 найдено количество студентов, которые имеют количество пропусков >3.

Например, чтобы подсчитать количество сотрудниц в списке, представленном в примере см. Рисунок 34. Таблица пример. можно использовать формулу:

=СЧЁТЕСЛИ (Е2:Е24;"Ж"). А для подсчета числа сотрудников в возрасте 45 лет или старше, можно использовать формулу:

= СЧЁТЕСЛИ(G2:G19;''>==45'')

 

Основные встроенные функции Microsoft Excel

 

Cинтаксис функций

Функции — это стандартные формулы, которые обеспечивают выполнение определенного набора операций над заданным диапазоном величин.

Каждая функция состоит из следующих трех элементов:

v Знак равенства (=), который показывает, что далее следует функция (или формула).

v Название функции (например, СУММ) определяет операции, которые будут выполняться.

v Аргумент, например (A1:H1) показывает адреса ячеек тех величин, с которыми будет работать функция.

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции - как, например, СУММ (SUM) или СРЗНАЧ (AVERAGE) - описывает операцию, которую эта функция выполняет. Аргумен­ты задают значения или ячейки, используемые функцией. Например, в формуле =СУММ(СЗ:С5) СУММ - это имя функции, а СЗ:С5 - ее единственный аргумент. Эта формула суммирует числа в ячейках СЗ, С4 и С5.

Знак равенства (=) в начале формулы означает, что введена именно формула, а не текст (например, комментарий или заголовок таблицы). Если вы опустите знак равенства. Excel воспримет ваш ввод просто как текст СУММ (СЗ:С5).

Обратите внимание, что аргумент функции заключен в круглые скобки. Откры­вающая скобка отмечает начало аргумента и ставится сразу после имени функции. Если вы введете между именем и открывающей скобкой пробел или другие символы, то в ячейке будет отображено ошибочное значение #ИМЯ? (#NAME?).

Некоторые функции, такие как ПИ (Р1) и ИСТИНА (TRUE), не имеют аргументов. (Как вы увидите позже, эти функции обычно используются в формулах или в других функциях). Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки, как в следующем примере:

=А1*ПИ()

Использование аргументов

 

При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой. Например, формула

=ПРОИЗВЕД (С1;С2;С5) указывает Excel, что необходимо перемножить числа в ячейках С1, С2 и С5. В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, функция =СУММ (А1:A5;C2:CIO;D3:DI7) имеет три аргумента, но суммирует числа в 29 ячейках. (Первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т. д.) Указанные в ссылке ячейки в свою очередь могут содержать формулы, которые ссылаются на другие ячейки или диапазоны. Используя аргументы, можно легко создавать длинные цепочки формул для выполнения сложных операций.

 

Выражения как аргументы

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

=СУММ(Sin(А1*ПИ());2*СО5(А2*ПИ()));

SIN(AI*ПИ()) и 2*COS(A2* ПИ())

- это выражения, которые вычисляются и используются в качестве аргументов функции СУММ.

 

Типы аргументов

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

 

Числовые значения

Аргументы функции могут быть числовыми. Например, функция СУММ в формуле =СУММ(327;209;176) суммирует числа 327, 209 и 176. Обычно вы вводите в ячейки листа числа, которые хотите использовать, и затем применяете ссылки на эти ячейки в качестве аргументов в функциях.

Текстовые значения

В качестве аргумента функции могут использоваться текстовые значения. На­пример, в формуле

=ТЕКСТ(ТДАТА();''Д МММ ГГГГ")

второй аргумент функции ТЕКСТ, "Д МММ ГГГГ", является текстовым и задает шаблон для преобразования десятичного значения даты, возвращаемого функцией ТДАТА, в строку символов. Текстовый аргумент может быть строкой символов, заключенной в двойные кавычки, или ссылкой на ячейку, которая содержит текст.

 

Логические значения

Аргументы ряда функций могут принимать только логические значения ИСТИ­НА (TRUE) или ЛОЖЬ (FALSE). Логическое выражение возвращает значение ИСТИНА или ЛОЖЬ в ячейку или формулу, содержащую это выражение. Например, первый аргумент функции ЕСЛИ (IF) в формуле

=ЕСЛИ(А3=ИСТИНА; "Новая";" Старая")&"цена")

является логическим выражением, которое использует значение в ячейке А3. Если это значение ИСТИНА, то выражение A3=ИCTИHA возвращает ИСТИ­НА, и функция ЕСЛИ возвращает Новая, а формула в целом возвращает текстовое значение Новая цена. См.Рисунок 1.

 

 

Рисунок 1.