Формирование последовательностей на основе двух значений можно, строго говоря, использовать для построения любого, из рассмотренных выше, рядов за исключением ряда типа Геометрическая прогрессия. Это объясняется тем, что с помощью двух значений нельзя задать информацию о типе прогрессии (по умолчанию Excel использует арифметическую прогрессию).
Последовательность действий при таком способе формирования рядов следующая:
1. В некоторые смежные ячейки вводятся первый и второй элемент ряда.
2. Выделяются заполненные ячейки и с помощью Автозаполнителя второй ячейки формируется ряд.
Это средство удобно использовать в том случае, когда требуется построение одновременно несколько рядов различного типа. Например, пусть требуется построить следующие ряды:
| Пн | Вт | Ср | Чт | Пт | Сб | Вс |
| Список 1 | Список 3 | Список 5 | Список 7 | Список 9 | Список 11 | Список 12 |
| 12.04.00 | 12.05.00 | 12.06.00 | 12.07.00 | 12.08.00 | 12.09.00 | 12.10.00 |
| 0 1/2 | 1 | 1 1/2 | 2 | 2 1/2 | 3 | 3 1/2 |
| 10% | 30% | 50% | 70% | 90% | 110% | 130% |
| 1-й день | 5-й день | 9-й день | 13-й день | 17-й день | 21-й день | 25-й день |
| №1 | №3 | №5 | №7 | №9 | №11 | №13 |
Введем два значения каждого ряда и выделим их так, как показано на рисунке ниже.
| A | B | C | D | E | |
| 1 | Пн | Вт | |||
| 2 | Список 1 | Список 3 | |||
| 3 | 12.04.00 | 12.05.00 | |||
| 4 | 0 ½ | 1 | |||
| 5 | 10% | 30% | |||
| 6 | 1-й день | 5-й день | |||
| 7 | №1 | №3 |
 |
Рис 2.1.
Затем с помощью, показанного на Рис 2.1., автозаполнителя построим необходимые ряды, протащив его влево на соответствующее количество столбцов.
Копирование рядов. Естественно, что ряды можно строить путем операции копирования уже существующего ряда. Однако если требуется построить ряд на основе существующего значения и при этом транспонировав его, обычные операции копирования и вставки здесь не подойдут. Для вставки в этом случае необходимо использовать команду Специальная вставка, входящую в меню Правка, установив для нее опцию транспонировать.
2.8. Ввод данных типа "формула"
Под формулой в Excel понимается последовательность символов (слово) языка формул, начинающаяся со знака "=" (равно). К алфавиту языка формул относятся имена функций, ссылки на ячейки, постоянные значения допустимых в Excel типов данных, имена ячеек и интервалов ячеек, а также операторы. В ячейке, содержащей формулу, выводится результат вычисления по этой формуле. Однако фактическим содержимым ячейки является формула, которая каждый раз будет отображаться в строке формул активной ячейки, а также в самой ячейке в режиме редактирования содержимого ячейки (см. Рис. 2).
| Е2 | u | = | =3*имя_ячейки_С1 |
| ||||||||||||
| A | B | C | D | E | ||||||||||||
| 1 | 3 | 4 | 5 | 6 | 7 | |||||||||||
| 2 | 7 | 25 | 20 | 15 | ||||||||||||
| 3 |
|
|
| |||||||||||||
4 |
|
|
| |||||||||||||
Рис. 2.2
Для задания формулы можно использовать различные технические приемы, значительно упрощающие процедуру построения формулы и гарантирующие в некоторой степени ее правильность. Например, для ввода в формулу ссылок на ячейки рекомендуется использовать указатель мыши: щелкнуть мышью на ячейке, которая должна быть указана в ссылке — данная ячейка будет обрамлена пунктирной бегущей рамкой, а ее адрес появится в формуле. Для изменения типа ссылок следует использовать функциональную клавишу F4: позиционировать указатель мыши на ссылке и нажимать клавишу F4 до тех пор, пока она не станет требуемой. Для ввода функций необходимо использовать средство Мастер функций.
Операторы
Табличный процессор Excel использует четыре категории операторов: арифметические, текстовые, адресные и операторы сравнения.
Арифметические операторы. В общем случае существует восемь операторов этого типа (см. табл. 2.2). Однако оператор "\" (косая черта) используемый для деления целых чисел доступен только в VBA (встроенный язык программирования).
Таблица 2.2
| Символ оператора | Название оператора |
| + (плюс) | Сложение |
| - (минус) | Вычитание |
| - (минус) | Отрицание |
| * (звездочка) | Умножение |
| / (косая черта) | Деление |
| % (процент) | Процент |
| ^ (крыша) | Возведение в степень |
Порядок выполнения операторов:
1. Возведение в степень
2. Отрицание
3. Умножение и Деление
4. Сложение и Вычитание
Текстовые операторы. В Excel используется один текстовый оператор — амперсант &, для того чтобы иметь возможность соединять текст из различных ячеек.
Операторы сравнения. В Excel доступны шесть операторов сравнения (см. табл. 2.3).
Таблица 2.3
| Символ оператора | Название оператора |
| = | Равно |
| < | Меньше) |
| <= | Меньше или равно |
| > | Больше |
| >= | Больше или равно |
| <> | Не равно |
Адресные операторы. Табличный процессор Excel использует три адресных оператора (см. табл. 2.4).
Таблица 2.4
| Символ оператора | Интерпретация | Использование |
| : (двоеточие) | Диапазон ячеек | В5:В10 |
| , (запятая) | Отдельная ячейка | В4,В5 (две ячейки) |
| (пробел) | Пересечение ячеек | В1:В5 А3:С3 (ячейка В3) |
Ссылки
По умолчанию в Excel используются стиль ссылок A1, в котором столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536. При этом ссылка на ячейку образуется путем сочетания идентификатора столба и номера строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой. В некоторых, рассмотренных ниже случаях, идентификатор столбца и/или номер строки предваряются символом доллара ($).
Таким образом, ссылки на ячейки бывают трех видов:
§ относительные — А4,
§ абсолютные — $F$3,
§ смешанные — $F5 или А$3.
Относительные ссылки устанавливаются по умолчанию, абсолютные и смешанные ссылки устанавливает пользователь.
Имена
Назначив имена отдельным ячейкам (можно использовать русский язык) или интервалам ячеек, можно вставлять их в формулы вместо использования соответствующих ссылок. Для вставки имен можно использовать последовательность действий: меню Вставка Þ команда Имя Þ параметр Вставить; или поле имен строки формул.
Использование формул массива
В Excel существует особый класс формул, называемых "формула массива". В общем случае, формула массива возвращает не одно значение как обычная формула, а несколько. Таким образом, для нескольких ячеек, которые составляют интервал массива (массив-интервал), может быть задана одна общая формула — формула массива.
Формула массива создается так же, как и обычная формула: выделяется ячейка или группа ячеек, в которых необходимо создать формулу, вводится формула, а затем нажимаются клавиши Ctrl + Shift + Enter. После этого формула автоматически заключается в фигурные скобки "{ }".
Ячейки массива обрабатываются программой как единое целое. На попытку изменить одну из ячеек массива Excel отреагирует сообщением о недопустимости выполнения этой операции. Изменить формулу массива можно путем включения режима редактирования для любой ячейки, содержащей формулу, и внесения необходимых изменений. Для подтверждения изменений в формуле следует использовать клавиатурную комбинацию Ctrl+Shift+Enter.
Массив констант. В любую формулу массива, где используется ссылка на интервал ячеек, можно ввести массив значений, содержащихся в этом интервале. Вводимый массив значений называется массивом констант. Чтобы создать массив констант, необходимо ввести значения, образующие массив констант, непосредственно в формулу, заключив их в фигурные скобки "{ }". При этом значения разделяются с помощью точки с запятой, а строки разделяются двоеточием. Например, {1; 2; 3 : 1; 2; 3}, {1,3; "Среда";ИСТИНА}.
Элементами массива констант могут быть:
§ числа — целые, дробные с десятичной точкой и в экспоненциальной форме;
§ текст — должен быть взят в двойные кавычки;
§ логические значения — ИСТИНА или ЛОЖЬ;
§ значения ошибок.
Массив констант может состоять из элементов разного типа, например, {1;2;3:ИСТИНА;ЛОЖЬ;"Январь"}.
В качестве элементов массива констант нельзя использовать: формулы, знаки доллара "$", круглые скобки, символы процента "%", ссылки.
Массив констант не может иметь столбцы или строки разного размера.
Функции
В Excel существует более 200 встроенных функций, которые разбиты на соответствующие категории. Кроме того, имеется возможность создавать пользовательские функции с помощью языка VBA. Рассмотрим использование наиболее употребляемых функций.
Математические
Алгебраические. Функция СУММ — суммирует числа, определяемые аргументами функции.
Синтаксис функции: СУММ(число1;число2; ... ; число30), где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, для которых требуется определить сумму.
При использовании функции необходимо иметь в виду следующее:
1. В суммировании участвуют числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. Например, функция СУММ("5"; 4; ИСТИНА) возвращает значение 10, так как текстовое значение "5" преобразуются в число 5, а логическое значение ИСТИНА преобразуется в число 1;
2. В том случае, если аргументом является массив констант или ссылка на ячейку (интервал ячеек), то учитываются только числа, расположенные в массиве или в соответствующей ячейке (интервале ячеек). Пустые ячейки, логические значения, тексты и значения ошибок игнорируются. Например, функция СУММ(A1; А2; А3) возвращает значение 3, если ячейка A1 содержит текстовое значение "3", ячейка А2 — значение ИСТИНА, а ячейка А3 — число 3. То же самое значение возвратит и функция СУММ(A1:А3);
3. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.
Функция ПРОИЗВЕД — перемножает числа, заданные в качестве аргументов и возвращает их произведение.
Синтаксис функции: ПРОИЗВЕД(число1;число2; ... ; число30), где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, для которых требуется определить произведение
Для этой функции справедливы те же замечания, что и для функции СУММ.
Функция СТЕПЕНЬ — возвращает результат возведения числа в степень.
Синтаксис функции: СТЕПЕНЬ(число;степень), где число — это основание степени, степень — показатель степени, в которую возводится основание. Оно может быть любым вещественным числом.
Примеры использования функции: СТЕПЕНЬ(5;2) ~ 52; СТЕПЕНЬ(4;5/4) ~ 45/4; СТЕПЕНЬ(98,6;3,2) ~ 98,63,2, где символ ~ означает эквивалентность, в данном случае это означает, что запись в языке формул Excel эквивалентна записи в математическом языке.
Следует заметить, что вместо функции СТЕПЕНЬ для возведения в степень можно использовать операцию "^", например 5^2 ~ 52.
Функция КОРЕНЬ — возвращает арифметическое значение квадратного корня.
Синтаксис функции: КОРЕНЬ(число), где число — это число, для которого вычисляется квадратный корень. Если число отрицательно, то функция КОРЕНЬ возвращает значение ошибки #ЧИСЛО!
Примеры использования функции: КОРЕНЬ(16); КОРЕНЬ(-16) равняется #ЧИСЛО!; КОРЕНЬ(ABS(-16)).
Функция EXP — возвращает число e (основание натурального логарифма), возведенное в указанную степень, является обратной к функции LN.
Синтаксис функции: EXP(число), где число — это число, для которого вычисляется экспоненциальная функция с основанием e.
Для того, чтобы вычислить показательную функцию с другим основанием, необходимо использовать операцию возведения в степень (^) или функцию СТЕПЕНЬ.
Примеры использования функции: EXP(2) ~ e2, EXP(1) ~ e.
Функция LN — возвращает натуральный логарифм числа, является обратной к функции EXP.
Синтаксис функции: LN(число), где число — это положительное вещественное число, для которого вычисляется натуральный логарифм.
Примеры использования функции: LN(86); LN(2,7182818); LN(Х*EXP(3)) ~ 3Х.
Функция L OG — возвращает логарифм числа по заданному основанию.
Синтаксис функции: L OG (число; основание), где число — это положительное вещественное число, для которого вычисляется логарифм; основание — положительное не равное единице основание логарифма. Если основание не задано оно принимается равным 10.
Примеры использования функции: L OG (86;2) ~ log2(86); L OG (2,7) ~ lg(2,7).
Функция ФАКТР — возвращает факториал числа.
Синтаксис функции: ФАКТР(число), где число — это положительное целое число, для которого вычисляется факториал.
Примеры использования функции: ФАКТР(4) ~ 1·2·3·4 = 24; ФАКТР(5) ~ 1·2·3·4·5 = 120.
Тригонометрические. Функция SIN возвращает синус числа.
Синтаксис функции: SI N(число), где число — это угол, заданный в радианах, для которого вычисляется синус.
Примеры использования функции: SIN (86) ~ sin(86); SIN (-6) ~ sin(-6).
Функция COS возвращает косинус числа..
Синтаксис функции: COS (число), где число — это угол, заданный в радианах, для которого вычисляется косинус.
Примеры использования функции: COS (6) ~ cos(6); COS (-7) ~ cos(-7).
Функция TAN возвращает тангенс числа..
Синтаксис функции: TAN (число), где число — это угол, заданный в радианах, для которого вычисляется тангенс.
Примеры использования функции: TAN (6) ~ tg(6); TAN (-7) ~ tg(-7).
Функция ПИ возвращает, округленное до 15 знаков после запятой число π (пи).
Синтаксис функции: ПИ() — функция не имеет аргументов.
Примеры использования функции: SIN (ПИ()/2) ~ sin(π/2) = 1; COS (ПИ()) ~ cos(π) = 0.
Функция РАДИАНЫ преобразует градусы в радианы.
Синтаксис функции: РАДИАНЫ(число) — где число — это угол, заданный в градусах, который преобразуется в радианы.
Примеры использования функции: РАДИАНЫ(90) ~ π/2, РАДИАНЫ(180) ~ π.
Функция ГРАДУСЫ преобразует радианы в градусы.
Синтаксис функции: ГРАДУСЫ(число) — где число — это угол, заданный в радианах, который преобразуется в градусы.
Примеры использования функции: ГРАДУСЫ(π/2) ~ 900, ГРАДУСЫ(π) ~ 1800.
Следует заметить, что тригонометрической функции ctg(x) нет, и ее следует выражать через другие тригонометрические функции.
Обратные Тригонометрические. Функция ASIN возвращает арксинус числа в радианах.
Синтаксис функции: ASI N(число), где число — это число в пределах от –1 до 1, для которого вычисляется арксинус.
Примеры использования функции: ASIN (0,5) ~ arcsin(0,5); ASIN (6) равняется #ЧИСЛО!
Функция ACOS возвращает арккосинус числа в радианах.
Синтаксис функции: ACOS (число), где число — это число в пределах от –1 до 1, для которого вычисляется арккосинус.
Примеры использования функции: ACOS (0,5) ~ arccos(0,5); ACOS (6) равняется #ЧИСЛО!
Функция ATAN возвращает арктангенс числа в радианах.
Синтаксис функции: ATAN (число), где число — это число, для которого вычисляется арктангенс.
Примеры использования функции: ATAN (0,5) ~ arctg(0,5); ATAN (6) ~ arctg(6).
Следует заметить, что обратной тригонометрической функции arcctg(x) нет, и ее следует выражать через другие обратные тригонометрические функции.
Логические
Функция ЕСЛИ — возвращает одно из двух значений в зависимости от значения логического выражения.
Синтаксис функции:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь),
где лог_выражение — это логическое выражение, принимающее значение ИСТИНА или ЛОЖЬ;
значение_если_истина — это значение, которое возвращается, если первый аргумент лог_выражение имеет значение ИСТИНА.
значение_если_ложь — это значение, которое возвращается, если первый аргумент лог_выражение имеет значение ЛОЖЬ.
Например, функция ЕСЛИ(A1=10;СУММ(B1:B10);"не определена") возвращает сумму чисел, расположенных в интервале B1:B10, если в ячейке A1 содержится число 10, и текст "не определена" в противном случае.
При использовании функции следует иметь в виду следующее:
1. Если лог_выражение имеет значение ИСТИНА и значение_если_истина опущено, то возвращается значение ИСТИНА; если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ
2. Аргументы значение_если_истина и значение_если_ложь могут представлять собой формулу (знак равно при этом не набирается);
3. Если какой-либо аргумент функции ЕСЛИ является массивом, то при выполнении вычисляется каждый элемент массива.
Для того, чтобы конструировать более сложные проверки, можно использовать до 7 функций ЕСЛИ, вложенных друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь.
Статистические
Функция МАКС — возвращает максимальное значение из списка, задаваемого аргументами.
Синтаксис функции: МАКС (число1;число2; ... ; число30), где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, определяющие список, из которого требуется выделить максимальное значение.
При использовании функции следует иметь в виду следующее:
1.В качестве аргументов можно использовать числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. Например, функция МАКС("6"; 4; ИСТИНА) возвращает значение 6, так как текстовое значение "5" преобразуются в число 5, а логическое значение ИСТИНА преобразуется в число 1;
2.В том случае, если аргументом является массив констант или ссылка на ячейку (интервал ячеек), то учитываются только числа, расположенные в массиве или в соответствующей ячейке (интервале ячеек). Пустые ячейки, логические значения, тексты и значения ошибок игнорируются. Например, функция МАКС(A1; А2; А3) возвращает значение 3, если ячейка A1 содержит текстовое значение "5", ячейка А2 — значение ИСТИНА, а ячейка А3 — число 3. То же самое значение возвратит и функция МАКС(A1:А3). Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА;
3.Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.
4.Если аргументы не содержат чисел, функция МАКС возвращает значение 0.
Функция МИН — возвращает минимальное значение из списка, задаваемого аргументами.
Синтаксис функции: МИН (число1;число2; ... ; число30), где число1, число2, ... ;число30 — аргументы (от 1 до 30) функции, определяющие список, из которого требуется выделить минимальное значение.
При использовании функции следует иметь в виду то же, что и для функции МАКС.
Вложенные функции
Функции могут использоваться как аргументы в других функциях. Если функция используется в качестве аргумента, т.е. является вложенной функцией, то она должна возвращать аргументу значение того же типа. Если функция возвращает значение другого типа, отобразится ошибка #ЗНАЧ! Например, следующая формула =ЕСЛИ(СРЗНАЧ(А1:А5)>60;СУММ(В1:В5);СУММ(В1:В5)) использует вложенную функцию СРЗНАЧ для сравнения ее значения со значением 60 — результат сравнения является логической величиной (ИСТИНА или ЛОЖЬ). Это требуемый тип для первого аргумента функции ЕСЛИ. Кроме того, в формуле имеется вложенная функция СУММ, возвращающая значение суммы числовых данных, расположенных в соответствующих интервалах. Это также требуемый тип для второго и третьего аргументов функции ЕСЛИ.
Другой пример, рассмотрим формулу =ЕСЛИ(СРЗНАЧ(А1:А5)>60;СУММ(В1:В5)<4;СУММ(В1:В5)). На первый взгляд тип второго аргумента не соответствует требуемому типу. Однако это не так, результат, возвращаемый сравнением СУММ(В1:В5)<4 является тоже значением, но только значением логического типа (ИСТИНА, ЛОЖЬ), который допустим для второго аргумента функции ЕСЛИ.
Однако если формула имеет вид =ЕСЛИ(СРЗНАЧ(А1:А5)>60;В1:В5;В5)), то при условии, что среднее значение данных, расположенных в ячейках А1:А5, меньше 60, в ячейке, где расположена формула, отобразится ошибка #ЗНАЧ! Это объясняется тем, что адресный оператор ":" (двоеточие) не формирует результат какого либо типа данных, допустимого в Excel.
В формулах можно использовать до семи уровней вложения функций. Когда функция F 2 является аргументом функции F 1, то F 2 считается вторым уровнем вложения. Если в функции F 2 содержится в качестве аргумента функция F 3, то она будет считаться третьим уровнем вложения функций и т.д.
Чтобы использовать функцию в качестве аргумента, необходимо воспользоваться панелью формул. Например, нажимая стрелку списка формул панели формул, можно вставить функцию ПРОИЗВЕД в качестве аргумента функции СУММ. Для продолжения ввода аргументов в функцию СУММ требуется один раз щелкнуть указателем мыши на ее имени в строке формул.
Мастер функций
Для удобства работы с функциями в Excel предусмотрен Мастер функций, запускаемый кнопкой
— Вставка функций, расположенной на панели инструментов
Стандартная или кнопкой
| = | — Изменить функцию, расположенной в строке формул. |
Дата: 2018-11-18, просмотров: 247.
