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

Функции

Обратите внимание

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

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

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

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

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

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

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

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

Рисунок 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:В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 то формула

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

МЕСЯЦ (А1)

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

возвратит значение 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.

СЧЁТЕСЛИ (Е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;С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.

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

Поскольку функций в Excel более 300, их выбор является сложной задачей. Для облегчения работы программистами Microsoft создан Мастер функций. Его можно вызвать кнопкойfx , расположенной на одном уровне со строкой ввода данных, или одновременным нажатием клавиш SHIFT и F 3 . В нем пользователю предлагается сузить область поиска за счет выбора категории, затем выбрать из списка функций, упорядоченных по алфавиту и относящихся именно к этой категории. При выборе имени функции внизу окна появляется подсказка о том, что она вычисляет (возвращает).

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

Кажется, все просто, но есть нюансы. Рассмотрим их на примерах. Во-первых, у функции может не быть аргументов. Пример на рисунке.

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

вызовите Мастер функций ;

среди функций выберите СРЗНАЧ ;

выделите все ячейки, среди которых определяется среднее значение (обратите внимание, что в поле они оказались перечисленными через двоеточие);

подтвердите действия кнопкой ОК .

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

В-третьих, аргументов может быть больше одного. Можно рассмотреть пример перемножения двух массивов:

щелкните по ячейке с будущим результатом;

вызовите Мастер функций ;

из имен функций выберите СУММПРОИЗВ ;

для поля первого массива укажите мышкой ячейки с числовыми значениями первой таблицы, для поля второго массива - ячейки с числовыми значениями второй таблицы (обратите внимание на то, что в строке ввода аргументы разделены точкой с запятой);

подтвердите действия кнопкой ОК .

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

выделите все ячейки, в которых должны отобразиться места;

вызовите Мастер функций ;

из имен функций выберите РАНГ.РВ ;

подтвердите свои действия нажатием клавиш CTRL , SHIFT и ENTER одновременно (после этого формула в строке ввода данных будет заключена в фигурные скобки).

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

ВВЕДЕНИЕ

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

В настоящее время наиболее популярным программным продуктом среди электронных таблиц является MS Excel, входящий в пакет Microsoft Office. Цель данного учебного пособия – помочь студенту получить навыки самостоятельного решения экономических задач различного уровня сложности.

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

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

В результате изучения курса студент должен

иметь:

· представление об электронных таблицах как универсальном инструменте ведения больших таблиц с расчетами,

знать:

· основы работы электронных таблиц Excel;

· возможности решения задач в электронных таблицах Excel,

уметь:

· создавать, редактировать и форматировать таблицы;

· использовать встроенные функции Excel;

· строить и редактировать диаграммы;

· работать с надстройкой Подбор параметра и Поиск решения .

Пособие состоит из двух частей.

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

Отчет по каждой лабораторной работе должен содержать:

1) титульный лист;

2) задания для выполнения лабораторной работы;

3) пояснительную записку, включающую необходимые формулы и выводы по работе.

Порядок выполнения лабораторных работ:

1) получение индивидуального задания для выполнения лабораторной работы (номер варианта индивидуального задания соответствует порядковому номеру студента в списке группы);

2) теоретическое освоение материала в объеме изученных разделов настоящего пособия;

3) выполнение задания на персональном компьютере;

4) оформление отчета.

I. КРАТКИЕ ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

ИНТЕРФЕЙС СРЕДЫ EXCEL

Структура Excel

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

Книга является синонимом файла с расширением .xls . Книга содержит рабочие листы.

Рабочий лист является основным рабочим пространством для пользователя. В одной книге может быть до 255 листов. По умолчанию в книге создается три листа (настроить количество листов можно через меню СЕРВИС , пункт ПАРАМЕТРЫ , вкладка ОБЩИЕ) . Один лист имеет 256 колонок и 65536 строк, которые образуют ячейки Общее количество ячеек на одном листе 16777216. Рабочим листам можно присваивать имена длиной до 31 символа. В именах запрещается использовать знаки * : ; / \ ? . По умолчанию листам присваиваются имена Лист1, Лист2 и т д.

Строка – элемент, адрес которого выражается числом от 1 до 65536.

Столбец – элемент, адрес которого выражается буквами латинского алфавита и их сочетаниями от А до Z.

Ячейка – элемент, образующийся на пересечении строки и столбца. Адрес ячейки формируется из буквенного адреса строки и числового адреса столбца, например: С8 . Такая запись адреса называется относительной адресацией. Кроме такой адресации есть адресация R1C1 (номер строки и номер столбца). Также ячейке может быть присвоено имя, отражающее тот или иной аспект решаемой задачи. Например, вместо имени А4 ячейку можно назвать ПРОЧНОСТЬ , если в этой ячейке находятся данные прочности.

Диапазонячеек имеет вид прямоугольной области из смежных клеток. Адрес диапазона задается адресами левой верхней ячейки и нижней правой, разделенных двоеточием, например: A1:C5 .

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

Рис. 1.1. Полное имя ячейки

Элементы интерфейса

На рис. 1.2 приведена схема основного экрана Excel с поясняющими выносками , .

Рис. 1.2. Схема основного экрана Excel

Рассмотрим элементы интерфейса подробнее.

Заголовок окна. Здесь приведены названия приложения (Microsoft Excel) и текущего документа (Книга 1). В заголовке также размещены стандартные кнопки управления окном в операционной системе Windows.

Главное меню и панели инструментов. Некоторые функции главного меню доступны через многочисленные подчиненные меню и вкладки. Для удобства работы многие функции главного меню продублированы кнопками-пиктограммами, частично расположенными на панелях инструментов, частично скрытыми.

Строка формул. Строка формул предназначена для ввода данных и формул. На рис. 1.3 представлена структура строки формул.

Рис. 1.3. Структура строки формул

Поле имени выполняет следующие функции.

1. Отображение адреса текущей ячейки.

2. Отображение числа захватываемых строк и столбцов в процессе выделения диапазона.

3. Поиск и выделение ячейки по ее имени.

4. Переименование ячеек и диапазонов.

Кнопка просмотра списка имен служит для просмотра имен ячеек и диапазонов.

Кнопка отмены ввода аналогична клавише Esc .

Кнопка подтверждения ввода аналогична клавише Enter .

Кнопка вызова мастера функций вызывает диалоговое окно МАСТЕР ФУНКЦИИ , облегчающее правильный ввод функции.

Поле ввода данных используется для ввода данных и формул.

Рабочий лист – основное пространство для работы. Лист обрамлен бордюром, в клетках которого указаны имена строк и столбцов. Клетки бордюра выполняют роль кнопок, щелкая по которым мышью можно выделять строки или столбцы целиком (при наведении курсора на клетку бордюра курсор принимает вид черной стрелки). На пересечении строк и столбцов в верхнем левом углу рабочего листа расположена кнопка ВЫДЕЛИТЬ ВСЕ (рис. 1.2).

Ярлычки рабочих листов и кнопки прокрутки ярлычков служат для быстрого перехода на нужный лист.

Фиксаторы разбивки листа – это две кнопки, расположенные рядом с полосами прокрутки листа (рис. 1.2). Если перетащить кнопку, расположенную у вертикальной полосы прокрутки, вниз, то лист разделится на две части по высоте, а если кнопку, расположенную у горизонтальной полосы прокрутки, перетащить влево, то лист разделится на две части по ширине.

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

Различают следующие типы текущего режима.

Готово – возможен доступ к меню и пиктограммам и перемещение по ячейкам листа.

Ввод – режим ввода информации в ячейку, при котором большая часть пунктов меню недоступна.

Правка – режим редактирования уже введенных данных в ячейке (переход в режим происходит после двойного щелчка на непустой ячейке).

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

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

Курсор –вид курсора указывает на возможные действия и показывает местоположение пользователя в интерфейсе Excel. Например:

Управлять внешним видом рабочего листа можно в меню СЕРВИС (СЕРВИС – ПАРАМЕТРЫ – вкладка ВИД) (рис. 1.4).

Рис. 1.4. Настройка внешнего вида рабочего листа

Контрольные вопросы к разделу 1

1. Что такое электронная таблица?

2. Что такое табличный процессор?

3. Какое главное достоинство электронных таблиц?

4. Каковы возможности электронных таблиц?

5. Что представляет собой имя строки?

6. Что представляет собой имя столбца?

7. Что такое ячейка?

8. Что представляет собой адрес ячейки?

9. Что такое блок ячеек?

10. Как задается адрес блока ячеек?

11. Какая ячейка называется активной (текущей)?

12. Что такое рабочая книга?

13. Перечислите основные элементы окна Excel.

ВВОД ДАННЫХ

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

Установками, принимаемыми по умолчанию на уровне всех ячеек таблицы, как правило, являются:

Ширина ячейки – 9 разрядов;

Левое выравнивание для текстовых (символьных) данных;

Основной формат для цифровых данных с выравниванием вправо.

Типы данных Excel

Символьные (текстовые) данные – это любая последовательность символов. В качестве их первого символа часто используется апостроф, а иногда – кавычки. Так, например, запись "653 распознается как набор символов, имеющих вид цифр 653, которые не обозначают количества и не могут использоваться в арифметических операциях. Данные текстового типа используются для заголовков таблиц, заголовков строк и столбцов, а также для комментариев. Например: Ведомость по начислению премии; Группа № 115 и т. д.

В одну ячейку возможен ввод до 32 000 символов. Чтобы настроить автоматический перенос символов на следующую строку внутри ячейки, необходимо выполнить следующее.

С помощью меню

1. В меню ФОРМАТ выберите пункт ЯЧЕЙКА.

2. В появившемся диалоговом окне ФОРМАТ ЯЧЕЕК установите флажок ПЕРЕНОСИТЬ ПО СЛОВАМ (рис. 2.1).

Рис. 2.1. Диалоговое окно «Формат ячеек»

С помощью клавиатуры

1. В нужном месте строки нажмите одновременно две клавиши Alt и Enter, создастся новая строка.

Числовые данные. Вводимые числовые данные хранятся в памяти с точностью, с которой они были введены. Результаты вычислений могут быть представлены с любой требуемой точностью в пределах до 15 значимых цифр. Дробная часть отделяется от целой запятой (или точкой), например: -20; 256,28; ,251.

Дата и время. Особым типом входных данных являютсядаты и время. Для представления дат и времени специального типа данных в Excel не предусмотрено. Для этого используются числовые данные, но представленные в формате ДАТА и Время . Этот тип данных обеспечивает выполнение таких функций, как добавление к дате числа (пересчет даты вперед и назад) или вычисление разности двух дат (длительности периода). Даты имеют внутренний (например, дата может выражаться количеством дней от начала 1900 года или порядковым номером дня по Юлианскому календарю) и внешний формат. Внешний формат используется для ввода и отображения дат, например:

- ДД-ММ-ГГ(04-10-11);

- ДД.ММ.ГГ (04.10.11);

Формулы – это выражения, состоящие из числовых величин, адресов ячеек и функций, соединенных знаками арифметических операций. При написании формулы должны начинаться со знака "=" (равно).

Арифметические операции:

^ – возведение в степень;

* – умножение;

/ – деление;

+ – сложение;

- – вычитание.

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

Основные правила записи формул .

1. Формулы записываются в строку (двухэтажные формулы с числителем и знаменателем недопустимы).

2. Все знаки арифметических действий должны присутствовать в явном виде (особенно это касается часто подразумеваемого и опускаемого знака умножить).

3. Количество правых и левых скобок должно быть одинаковым.

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

Логические операции

< - меньше;

> - больше;

<> - не равно;

<= - меньше равно;

>= - больше равно.

Логические операции могут применяться к числовым и символьным данным, при этом допускается сравнение символа с числом (буква всегда «больше» любого числа). Результатом логической операции являются слова ИСТИНА или ЛОЖЬ . В арифметических операциях логическое значение ЛОЖЬ интерпретируется как 0, а ИСТИНА – как 1. Воспользуемся этим для организации проверки знака чисел.

1. Введите в ячейки некоторого диапазона, например: С1:С4 , любые числовые данные.

2. Запишите в ячейку С5 следующее выражение

=(C1>0)*(C2>0)*(C3>0)*(C4>0) (2.1)

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

4. Если числа в ячейках С1:С4 неотрицательны, то есть каждое из неравенств дает в результате ИСТИНА (или 1), то выражение (2.1) равносильно следующему:

т. е. равно 1. Если хотя бы одно из чисел отрицательно или равно нулю, то в ячейке С5 появится 0.

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

Примеры записи формул.

Пример записи формулы приведен на рисунке 2.2.

На рис. 2.2 константы хранятся в ячейках С1:С5 , а результат вычислений =(С1^С2+С3)/С4*4 показан в ячейке А1 .

Рис. 2.2. Пример записи формулы

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

Чтобы при копировании и перемещении формул ссылки на ячейки не изменялись, следует использовать абсолютную адресацию. Для этого надо перед координатами в адресе поставить знаки доллара, например: $A$1 . Для набора знака $ можно после ввода адреса ячейки нажать функциональную клавишу F4.

Если требуется зафиксировать не весь адрес, а только номер строки или только букву столбца, то знак доллара следует поставить только перед фиксируемой координатой, например: $СЗ , A$5 и такая адресация называется смешанная . Для этих целей также можно использовать функциональную клавишу F4. Только нажать ее надо несколько раз, следя за последовательным изменением порядка расстановки знаков доллара.

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

1. Введите два числовых массива: первый массив в диапазон А1:А4 , второй в диапазон В1:В4 . Требуется сложить эти массивы поэлементно и результат вывести в ячейки С1:С4 .

2. Выделите ячейки диапазона С1:С4 .

3. В ячейку С1 в строке формул запишите формулу

A1:A4+B1:B4 (2.3)

4. Нажмите одновременно три клавиши Ctrl, Shift и Enter.

5. Убедитесь, что в ячейках С1:С4 появились результаты сложения элементов массивов, в строке формул – фигурные скобки, как признак операции обработки массивов (рис. 2.3).

Рис. 2.3. Результат сложения массивов

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

Примеры функций:

СУММ (B2:B6, B9) – функция вычисления суммы значений чисел, находящихся в ячейках В2 , ВЗ , В4 , В5 , В6 и В9 .

МАХ (А1:А8) – функция нахождения максимального значения в диапазоне ячеек от А1
до А8 .

Ввести в ячейку формулу, содержащую функции, можно с клавиатуры или с помощью мастера функций, который можно вызвать, выбрав левой кнопкой мыши кнопку f х в строке формул или на панели инструментов, а также в основном меню ВСТАВКА пункт Функция (рис. 2.4).

Рис. 2.4. Мастер функций

Мастер функций за два шага позволит записать любую функцию:

1) на первом шаге выбирается функция; непосредственно под списком выводится краткое описание действия выбранной функции и правила записи аргументов, а слева внизу диалогового окна имеется ссылка на справку с подробным описанием выбранной функции;

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

Рассмотрим некоторые из них подробно.

Использование встроенных функций EXCEL

1. СУММ(число1, число2,…) – суммирует аргументы. В качестве аргументов может быть задан диапазон.

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

Рис. 2.5. Вычисление суммы

Для подсчета суммы в примере можно использовать также функцию Автосуммирование . Для этого необходимо выделить диапазон ВЗ:В8 и на Панели инструментов нажать кнопку автосуммирования S (рис.2.5). В ячейке В9 появится формула СУММ(ВЗ:В8) .

Для того чтобы посмотреть какие формулы записаны во всей таблице в ячейках расчета, можно настроить отображение в таблице нечисловых результатов (рис. 2.6). Для этого войдите в меню СЕРВИС , выберите пункт Параметры , в диалоговом окне войдите на вкладку Вид , в группе Параметры окна поставьте флажок Формулы и щелкните по кнопке ОК.

Рис. 2.6. Вывод формул

2. СЧЁТ (значение1; значение2;...) – функция подсчитывает количество чисел в списке аргументов. Например: СЧЁТ(А1:А9) .

3. СРЗНАЧ (число1; число2;…). Возвращает среднее (арифметическое) своих аргументов, которые могут быть числами или именами, массивами или ссылками на ячейки с числами. Например: СРЗНАЧ(A3:А10) .

4. КОРЕНЬ (число). Функция вычисляет квадратный корень из положительного числа. Если число отрицательное (ожидается, что может быть отрицательным в процессе расчетов), то следует использовать функцию модуля числа.

5. АВS (число) – функция возвращает модуль числа (рис. 2.7)

Рис. 2.7. Примеры использования функции КОРЕНЬ()

6. ЕХР (число) – возвращает экспоненту числа. Функция вычисляет результат возведения в степень основания натурального логарифма (е=2,71878…) Степень указывается в скобках как аргумент функции. Например: EXP(3)=e 3 .

7. LN (число) – в ычисляет натуральный логарифм числа. Данная функция является обратной по отношению к EXP().

8. LOG (число; основание) – в ычисляет логарифм числа по заданному основанию. Например: LOG(23,3).

9. МАКС (диапазон) – функция служит для поиска максимального элемента в диапазоне, указанном в качестве аргумента.

10. МИН (диапазон) – функция служит для поиска минимального элемента в диапазоне, указанном в качестве аргумента.

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

Рис. 2.8. Логическое действие в виде блок-схемы

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

ЕСЛИ().

Пример 2.1. В зависимости от значений переменных F и R переменная У должна принимать следующие символьные значения:

(2.1)

Пусть переменной F соответствует ячейка А1 , переменной R – ячейка А2 , а переменной У – ячейка A3 . В этом случае задачу можно сформулировать так: сравнить числовые значения, записанные в ячейках А1 и А2 , и вывести полученный результат сравнения в ячейку A3 в виде символьных констант «F>R» и «F<=R».

1. Введите в ячейки А1 и А2 любые числовые константы, например 7 и 4 (рис. 2.9).

2. В ячейку A3 запишите формулу, основанную на логической функции ЕСЛИ() .

3. Подтвердите ввод и убедитесь в правильности полученного результата (рис. 2.9).

Рис. 2.9. Пример решения с использование функции ЕСЛИ()

12. Логическая функция И () – ф ункция также называется функцией логического умножения и имеет следующий синтаксис:

И(условие 1;условие 2; ...)

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

13. Логическая функция ИЛИ () – ф ункция также называется функцией логического сложения и имеет следующий синтаксис:

ИЛИ(условие 1; условие 2;...)

Функция возвращает значение ИСТИНА , если хотя бы одно из условий истинно. Функции И() и ИЛИ() часто применяются совместно с функцией ЕСЛИ() в качестве вложенных функций.

Пример 2.2. Определим наибольшее из трех чисел.

1. Запишите числа, подлежащие сравнению, в ячейки A1 , A2 , A3 .

2. Запишите в ячейку А4 формулу (рис. 2.10), содержащую вложение функций ЕСЛИ() и И().

Рис. 2.10. Пример записи функций ЕСЛИ() и И()

Вложение функции И() позволяет сформировать проверку одновременного выполнения двух условий для определения, что А1 больше А2 и A3 , Если оба условия истинны, то в ячейку А4 выводится текст А1 наибольшее. Если нет, то управление передается второй функции ЕСЛИ() с вложенной функцией И() и проверяется одновременное выполнение двух условий для определения, что A2 больше А1 и A3 . Если оба условия истинны, то в ячейку А4 выводится текст А2 наибольшее. Если нет, то в ячейку выводится текст A3 наибольшее.

Пример 2.3. Необходимо выяснить, есть ли среди трех чисел отрицательные.

1. Введите исходные числа в ячейки A1 , A2 , A3 .

2. В ячейку А4 запишите формулу с вложенной логической функцией ИЛИ() (рис. 2.11).

Рис. 2.11. Пример записи формулы с вложенной функцией ИЛИ()

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

Пример 2.4. Составить формулу, которая выдавала бы текст приветствия на вводимое время следующим образом:

от 0 до 7 часов – «Доброй ночи»

от 7 до 11 часов – «Доброе утро»

от 11 до 18 часов – «Добрый день»

от 18 до 24 часов – «Добрый вечер»

Предусмотреть защиту от неправильного ввода: время суток не может выражаться отрицательными числами и числами больше 24.

1. Формализуйте задачу. Представьте условия задачи как диапазон числовых значений, каждому из которых надо поставить в соответствие символьную константу: «Доброй ночи», «Доброе утро» и т. д.

2. Составьте исходную таблицу (рис. 2.12). Текущее время будет вводиться в ячейку В2 , рабочая формула будет введена в ячейку ВЗ и там же будет выводиться текст приветствия.

3. Используя логические функции ЕСЛИ(), И(), ИЛИ(), составьте и запишите в ячейку ВЗ рабочую формулу (рис. 2.12).

Рис. 2.12. Пример записи и решения задачи 2.4

Формула составлена по принципу вложения: четыре вложения функции ЕСЛИ() в саму себя, четыре вложения функции И() в функцию ЕСЛИ(), одно вложение функции ИЛИ() в ЕСЛИ(). Формула имеет следующую структуру:

=ЕСЛИ(И(условия);«сообщение»;ЕСЛИ(И(условия);«сообщение»;

ЕСЛИ(И(условия);«сообщение»;ЕСЛИ(И(условия);«сообщение»;

ЕСЛИ(ИЛИ(условия);«сообщение»))))).

Результат решения задачи представлен на рисунке 2.13.

Рис. 2.13. Полученный результат решения задачи 4

14. РАНГ (Число; Ссылка; Порядок) – функция в озвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке. Первые два аргумента являются обязательными. Третий аргумент определяет способ упорядочения. Если он равен нулю или опущен, то наилучшим считается наибольшее число и ему присваивается ранг, равный 1. Если третий аргумент равен любому ненулевому числу, то наилучшим считается наименьшее число и ему присваивается ранг, равный 1. Например:=РАНГ(A2;$A$2:$A$6).Наибольшему числу будет присвоен ранг 1.

Рассмотрим пример использования функции РАНГ.

Пример 2.5. Ячейки A2:A6 содержат числовые данные (рис. 2.14). Присвоить числам ранг при условии, что наилучшим считается наибольшее число, и поместить результат в ячейках B2:B6 .

Для решения такой задачи используется статистическая функция =РАНГ(Число; Ссылка; Порядок) . Первые два аргумента являются обязательными. Третий аргумент определяет способ упорядочения. Если он равен нулю или опущен, то наилучшим считается наибольшее число, и ему присваивается ранг, равный 1. Если третий аргумент равен любому ненулевому числу, то наилучшим считается наименьшее число, и ему присваивается ранг, равный 1.

Таким образом, в ячейку B2 нужно ввести формулу: =РАНГ(A2;$A$2:$A$6) и скопировать ее в ячейки B3:B6 (рис. 2.14).

Рис. 2.14. Определение ранга

16. СУММЕСЛИ(диапазон; критерий; диапазон суммирования) – функция суммирует ячейки, заданные указанным условием. Например: =СУММЕСЛИ (A1:A6;">160000";B1:B6).

17. ВПР(искомое значение; таблица массив; номер столбца; параметр) – функция называется «вертикальный поисковый ряд» . Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце. По умолчанию таблица должна быть отсортирована по возрастанию. Например: =ВПР(C3;$G$3:$H$8;2). Функция ВПР C3 в первом столбце диапазона $G$3:$H$8

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

Пример 2.6. Два цеха производят продукцию трех видов – прибор «Альфа» артикулов А1 и А2 , прибор «Бета» артикулов Б1 и Б2 , прибор «Гамма» артикулов В1 и В2 . Цех 1 выпускает приборы «Альфа» и «Бета». Цех 2 выпускает приборы «Бета» и «Гамма». Имеются данные о количестве приборов, произведенных каждым цехом за январь месяц. Известны также издержки, приходящиеся на единицу продукции каждого артикула.

Определить общие издержки каждого цеха по приборам каждого артикула в январе. Определить количество приборов «Бета», изготовленных первым цехом и вторым цехом в январе.

В ячейках A1:E10 и G1:H8 (рис.2.15) содержатся исходные данные задачи. Результат решения приведен в ячейках E3:E10 . С математической точки зрения задача решается элементарно. Для расчета общих издержек в ячейку E3 вводится формула: =ВПР(C3;$G$3:$H$8;2)*D3 и размножается на ячейки диапазона E3:E10 . Функция ВПР находит точно такое же значение ячейки C3 в первом столбце диапазона $G$3:$H$8 и возвращает соответствующее значение второго столбца этого диапазона, так как третий аргумент равен двум.

Рис. 2.15. Результат решения задачи 2.6.

Для определения количества приборов «Бета», изготовленных первым цехом и вторым цехом в январе, введенные текстовые данные для задач требуют объединения ячеек: A12:C12 , A13:C13 , A14:C14 и A15:C15 . Для расчета количества приборов «Бета», изготовленных первым цехом и вторым цехом в январе, нужно ввести в ячейки D12 и D13 следующие формулы:

=СУММЕСЛИ(B3:B6;B5;D3:D6)

=СУММЕСЛИ (B7:B10;B7;D7:D10).

Для расчета количества приборов «Бета», изготовленных обоими цехами в январе, нужно ввести в ячейку D14 следующую формулу: =СУММЕСЛИ(B3:B10;B5;D3:D10).

Для расчета количества приборов «Бета» артикула Б1 , изготовленных обоими цехами в январе, нужно ввести в ячейку D15 следующую формулу: =СУММЕСЛИ(C3:C10;C5;D3:D10).

18. ГПР(искомое значение; таблица; номер строки; параметр) – функция называется «горизонтальный поисковый ряд» , так как производит поиск параметра искомое значение в самой верхней строке диапазона поиска. Счет номеров строк для параметра номер строки начинается с единицы. Таким образом, функция ищет значение в верхней строке таблицы и возвращает значение ячейки, находящейся в указанной строке того же столбца. Параметр определяет тип поиска: 0 (или ЛОЖЬ) – точный поиск, 1 (или ИСТИНА) – приблизительный.

Рассмотрим пример применения функции ГПР.

Пример 2.7. Имеется обширная электронная таблица, в которой записаны индексы товаров и их цены (рис. 2.18). Приведен небольшой фрагмент этой большой таблицы. Менеджеру по продажам нужно срочно узнать цены ряда товаров по их индексам. Поскольку таблица большая, поиск «вручную» неэффективен. Что следует сделать менеджеру?

1. Менеджеру следует составить таблицу Запрос (рис. 2.18) с двумя рабочими ячейками Индекс товара и Цена товара.

2. В ячейку Индекс товара менеджер должен ввести индекс интересующего его товара, а в ячейку Цена товара записать формулу для поиска и чтения содержимого ячейки. В формуле следует использовать функцию ГПР(), так как поиск входа в диапазон возможен только по значению индекса, который записан в верхней строке диапазона (рис. 2.17).

Рис. 2.17. Запись функции ГПР

3. Введите в ячейку А9 индекс СК . Подтвердите ввод и убедитесь, что в ячейке А9 получено значение цены товара с индексом СК .

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

Рис. 2.18. Результат решения примера 2.7

19 СЧЁТЕСЛИ(диапазон; критерий) – функция подсчитывает количество непустых ячеек в удовлетворяющих заданному условию.Например: =СЧЁТЕСЛИ(A1:A6;">10000") – подсчитывает в диапазоне A1:A6 количество значений, которые больше 10000.

Рассмотрим пример применения функции СЧЁТЕСЛИ.

Пример 2.8. Для таблицы рисунка 2.19 определить число поездок, сделанных Петровым.

Создайте вспомогательную мини-таблицу Число ездок и запишите
в ячейку В13 функцию СЧЕТЕСЛИ() (рис. 2.19) В этом случае вы указываете адрес ячейки, в которой записана фамилия интересующего вас водителя (А4 – это Петров) Можно поступить иначе. Создайте вспомогательную таблицу с двумя полями Фамилия и Число поездок. В ячейку А14 будете вводить фамилию водителя. В функции СЧЕТЕСЛИ() на месте параметра критерий стоит адрес этой ячейки. Или же можно указывать фамилию, заключенную в кавычки, прямо в функции в позиции критерий.

Рис. 2.19. Результат решения примера 2.8

Контрольные вопросы к разделу 2

1. Перечислите типы данных Excel.

2. Как выравниваются числовые данные в ячейке таблицы по умолчанию?

3. Как выравниваются текстовые данные в ячейке таблицы по умолчанию?

4. Какие существуют способы записи формул в ячейки?

5. Для чего предназначены абсолютные и относительные ссылки?

6. Какими способами можно вычислить сумму?

7. Какова ширина ячейки по умолчанию?

8. Как работает функция ЕСЛИ ?

9. Каково назначение функции РАНГ ?

10. Каково назначение функции ВПР и ГПР ?

РЕДАКТИРОВАНИЕ ДАННЫХ

Выделение ячеек

Выделение ячеек является важнейшим техническим приемом при работе с Excel. Преобразование данных производится только над выделенными объектами, т. е. вначале надо выделить ячейки, а затем указать, что с ними делать. ,

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

Рис. 3.1. Выделение одиночной ячейки

Выделение строки или столбца можно производить мышью или с помощью клавиатуры.

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

С помощью клавиатуры для выделения столбца нажать две клавиши Ctrl и Пробел, а для строки –Shift и Пробел .

Рис. 3.2. Выделение строки

Выделение нескольких смежных ячеек, образующих непрерывную прямоугольную область можно выполнить:

С помощью мыши

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

Способ 2: (для выделения больших прямоугольных областей): щелкнуть мышью на первой угловой ячейке, нажать и удерживать клавишу Shift и щелкнуть на ячейке, расположенной в противоположенном углу области. Выделится вся область.

С помощью клавиатуры

Способ 1 : установить курсор на любой из угловых ячеек, удерживая нажатой клавишу Shift , с помощью клавиш со стрелками выделить остальную часть области.

Способ 2 : установить курсор на исходной ячейке, нажать и отпустить клавишу F8 , клавишами со стрелками выделить прямоугольную область, нажать и отпустить клавишу F8 .

Выделение несмежных ячеек и прямоугольных областей – щелкнуть мышью на первой ячейке (или выделить первую прямоугольную область); удерживая нажатой клавишу Ctrl , выделить вторую ячейку (или прямоугольную область) и т.д.

Выделение всего листа

Способ 1 : щелкнуть мышью на кнопке ВЫДЕЛИТЬ ВСЕ.

Способ 2: нажать три кнопки Ctrl+Shift+Пробел.

Способ 3 : нажать Ctrl+A (А латинская).

Похожая информация.

Теги: Встраиваемые функции, inline, __inline, __forceinline, always_inline

Встраиваемые функции

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

Для объявления встраиваемой функции используется ключевое слово inline (или __inline, __forceinline в зависимости от компилятора)

#include inline int fun (int a, int b) __attribute__((always_inline)); int main() { int result = fun(2, 3); printf("%d", result); getchar(); return 0; } inline int fun(int a, int b) { return a + b; }

Здесь, для тестирования, использованы атрибуты компилятора gcc, которые форсируют встраивание. Рассмотрим код, который компилируется при использовании inline

0004016f0 <_main>: 4016f0: 55 push %ebp 4016f1: 89 e5 mov %esp,%ebp 4016f3: 83 e4 f0 and $0xfffffff0,%esp 4016f6: 83 ec 20 sub $0x20,%esp 4016f9: e8 c2 00 00 00 call 4017c0 <___main> 4016fe: c7 44 24 18 02 00 00 movl $0x2,0x18(%esp) 401705: 00 401706: c7 44 24 14 03 00 00 movl $0x3,0x14(%esp) 40170d: 00 40170e: 8b 54 24 18 mov 0x18(%esp),%edx 401712: 8b 44 24 14 mov 0x14(%esp),%eax 401716: 01 d0 add %edx,%eax 401718: 89 44 24 1c mov %eax,0x1c(%esp) 40171c: 8b 44 24 1c mov 0x1c(%esp),%eax 401720: 89 44 24 04 mov %eax,0x4(%esp) 401724: c7 04 24 64 50 40 00 movl $0x405064,(%esp) 40172b: e8 a8 1f 00 00 call 4036d8 <_printf> 401730: e8 cb 1f 00 00 call 403700 <_getchar> 401735: b8 00 00 00 00 mov $0x0,%eax 40173a: c9 leave 40173b: c3 ret И без использования (видим вызов функции CALL в строке 10) 004016f0 <_main>: 4016f0: 55 push %ebp 4016f1: 89 e5 mov %esp,%ebp 4016f3: 83 e4 f0 and $0xfffffff0,%esp 4016f6: 83 ec 20 sub $0x20,%esp 4016f9: e8 d2 00 00 00 call 4017d0 <___main> 4016fe: c7 44 24 04 03 00 00 movl $0x3,0x4(%esp) 401705: 00 401706: c7 04 24 02 00 00 00 movl $0x2,(%esp) 40170d: e8 24 00 00 00 call 401736 <_fun> 401712: 89 44 24 1c mov %eax,0x1c(%esp) 401716: 8b 44 24 1c mov 0x1c(%esp),%eax 40171a: 89 44 24 04 mov %eax,0x4(%esp) 40171e: c7 04 24 64 50 40 00 movl $0x405064,(%esp) 401725: e8 be 1f 00 00 call 4036e8 <_printf> 40172a: e8 e1 1f 00 00 call 403710 <_getchar> 40172f: b8 00 00 00 00 mov $0x0,%eax 401734: c9 leave 401735: c3 ret 00401736 <_fun>: 401736: 55 push %ebp 401737: 89 e5 mov %esp,%ebp 401739: 8b 55 08 mov 0x8(%ebp),%edx 40173c: 8b 45 0c mov 0xc(%ebp),%eax 40173f: 01 d0 add %edx,%eax 401741: 5d pop %ebp 401742: c3 ret 401743: 90 nop

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

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

В-третьих, это даёт достаточно малый прирост производительности, но усложняет процесс сборки, оптимизации и увеличивает время компиляции. Во время внешнего связывания (external linkage) также могут возникнуть проблемы, если функция не была объявлена inline во всех компилируемых модулях. Поэтому часто встраиваемые функции объявляют также статическими.

данные по строке “Производственная прибыль” рассчитайте как разность между валовой прибылью и общими затратами;

данные по строке “Удельная валовая прибыль” рассчитайте как результат деления производственной прибыли на торговые доходы;

данные в колонке “Итого за год” рассчитайте суммированием квартальных данных.

8. Задайте для строки “Удельная валовая прибыль” Процентный формат, а для всех остальных строк – Формат с разделителями.

9. На строке 1 (при необходимости вставьте строку) введите заголовок, задав для него более крупный жирный шрифт и расположение по центру всей таблицы.

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

11.Добавьте на диаграмму данные по 4 кварталу.

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

13.Измените числовое значение за 4 квартал по Торговым расходам фирмы. Проследите зависимость графических данных в диаграммах от числовых в таблице.

14. На отдельном листе постройте нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу Итого за год).

Тема III. Основные правила работы со встроенными функциями

1. Использование встроенных функций Excel

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

ИМЯ_ФУНКЦИИ(список параметров)

Список параметров может задаваться адресными ссылками или ссылками посредством имен на ячейки или диапазоны, в виде имен функций, а также литералами, отделенными друг от друга символом ; (точка с запятой).

Примеры функций:

СУММ(А2:A10) – суммируются значения всех ячеек диапазона. СУММ(СуммаКредита) – суммируются значения всех ячеек диапазона с

именем СуммаКредита

СУММ(12; 10; 13) – суммируются значения, заданные списком числовых литералов.

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

Для задания функции используют следующие правила .

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

нажать клавиши -;

задать команду ФУНКЦИЯ из меню ВСТАВКА ;

нажать кнопку ВСТАВКА ФУНКЦИИ [ f x ] на стандартной панели.

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

Примечание : При использовании в качестве аргумента вводимой функции другой какой-либо функции следует выбрать имя требуемой функции из списка функций в строке формул слева и произвести требуемые шаги Мастера функций. После завершения ввода аргументов вложенной функции щелкните указателем мыши в строке формул (в конце вводимой формулы).

1.1. Суммирование ячеек, удовлетворяющих определенному критерию

СУММЕСЛИ(ДИАПАЗОН; УСЛОВИЕ; ДИАПАЗОН_ СУММИРОВАНИЯ) - группа

математических функций.

Функция предназначена для суммирования только ячеек, удовлетворяющих некому критерию.

условие - указывается в форме числа, выражения или текста;

диапазон_суммирования - это диапазон суммируемых ячеек.

Пример III.1. В ячейки A1,A2,A3,A4,A5 введена информация:

A1=1000, A2=2000, A3=900, A4=800, A5=1500.

СУММЕСЛИ (А1:А5;’’>=1000’’).

В ячейке А6 получится число 4500.

Измените значение ячейки А3 на любое большее 1000. Проследите изменение результата вычислений.

1.2. Подсчет количества значений в диапазоне

Для подсчета количества числовых значений в диапазоне:

СЧЕТ (ЗНАЧЕНИЕ 1; ЗНАЧЕНИЕ 2;…) - группа статистических функций.

Пример III.2. В ячейки В1,В2,В3,В4,В5 введена информация:

В1=’’Текст’’, В2=2000, В3= , В4=800, В5=1500.

Пошаговыми действиями Мастера функций в ячейку В6 следует ввести формулу:

СЧЕТ (В1:В5)

В ячейке В6 получится число 3.

Для подсчета количества всех значений в списке аргументов и непустых ячеек: СЧЕТЗ (ЗНАЧЕНИЕ 1; ЗНАЧЕНИЕ 2;…) - группа статистических функций.

Если эта функция будет введена в примере 2 в ячейку В7, то результат в ячейке В7 будет равен 4.

1.3. Подсчет количества пустых ячеек в диапазоне

Пример III.3. В ячейки С1,С2,С3,С4,С5 скопируйте информацию из соответствующих ячеек столбца А, оставив пустой ячейку С3:

С1=1000, С2=2000, С3= , С4=800, С5=1500.

Пошаговыми действиями Мастера функций в ячейку С6 следует ввести формулу:

В ячейке С6 получится число 1.

1.4. Подсчет количества непустых ячеек в диапазоне, удовлетворяющих

заданному условию

СЧЕТЕСЛИ (ДИАПАЗОН ; УСЛОВИЕ ) - группа статистических функций.

диапазон - это диапазон, в котором определяется критерий;

условие - указывается в виде числа, выражения или текста и определяет какие ячейки надо подсчитывать.

Результат должен быть получен в ячейке С7. Пошаговыми действиями Мастера функций в ячейку С7 следует ввести формулу:

СЧЕТЕСЛИ (С1:С5;’’>=1000’’).

В ячейке С7 получится число 3.

Измените значение ячейки С1 на любое, меньшее 1000. Проследите изменение результата вычислений.

1.5. Расчет среднего значения

СРЗНАЧ (ДИАПАЗОН 1; ДИАПАЗОН 2;...) - группа статистических функций.

В текущую ячейку возвращается среднее значение для чисел указанного диапазона.

Пример III.5. В диапазоне ячеек A1:A5 из примера 1 определить среднее значение.

Результат должен быть получен в ячейке А7.

Пошаговыми действиями Мастера функций в ячейку А7 следует ввести формулу:

СРЗНАЧ (А1:А5).

1.6. Определение максимального значения

МАКС (ДИАПАЗОН 1; ДИАПАЗОН 2;...) - группа статистических функций.

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

Пример III.6. В диапазоне ячеек A1:A5 из примера 1 определить максимальное значение.

Результат должен быть получен в ячейке А8.

Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу:

МАКС (А1:А5).

В ячейке А8 получится число 2000.

1.7. Определение минимального значения

МИН (ДИАПАЗОН 1; ДИАПАЗОН 2;...) - группа статистических функций.

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

Пример III.7. В диапазоне ячеек В1:В5 из примера 2 определить минимальное значение.

Результат должен быть получен в ячейке В8.

Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу:

МИН (В1:В5).

В ячейке В8 получится число 800.

1.8. Генерация случайного числа

СЛЧИС () - группа математических функций.

В текущую ячейку возвращается равномерно распределенное случайное число большее либо равное 0 и меньшее 1. Чтобы получить случайное вещественное число между a и b, можно использовать следующую формулу: СЛЧИС()*(b-a)+a.

Новое случайное число возвращается каждый раз, когда рабочий лист вычисляется повторно. Чтобы число, полученное случайным образом не менялось в дальнейшем, можно ввести =СЛЧИС() в строку формул и нажать F9.

Пример III.8. Для генерации случайного числа большего или равного 0, но меньшего 50 можно использовать формулу: СЛЧИС()*50.

Самостоятельно получите случайным образом последовательность чисел от 100 до 200.

1.9. Функции прогнозирования

ТЕНДЕНЦИЯ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ_ Х; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ_ У; НОВОЕ

ЗНАЧЕНИЕ _ У ) - группа статистических функций В текущую ячейку возвращается новое значение_Х, рассчитанное на основании

известных значений.

Пример III.9. Ввести таблицу, начиная с ячейки G1:

Годы Цена

Результат прогнозирования должен быть получен в ячейке H5. Пошаговыми действиями Мастера функций в ячейку H5 вводим формулу:

ТЕНДЕНЦИЯ (H2:H4;G2:G4;G5).

В ячейке H5 получится число 626.6667 1 .

Самостоятельно рассчитайте тенденцию для 2004 и 2005 годов.

1.10. Определение ранга числа

РАНГ (АДРЕС ЯЧЕЙКИ ; ДИАПАЗОН ) - группа статистических функций.

В текущую ячейку возвращается величина, соответствующая положению (рангу) числа, заданного адресом ячейки, в указанном диапазоне.

Пример III.10. В ячейки D1,D2,D3,D4,D5 скопируйте информацию из соответствующих ячеек столбца А. Для каждой ячейки из диапазона D1:D5 определить ранг числа.

Результат должен быть получен в ячейках E1:E5. Функция ранга вводится сначала в ячейку E1, затем копируется для всех ячеек до E5.

Пошаговыми действиями Мастера функций в ячейку E1 вводим формулу:

1 Результат прогнозирования может быть получен и другим способом: выделить диапазон ячеек с известными значениями цен и растянуть этот диапазон за правый нижний угол на одну ячейку вниз.