Виды ошибок, которые распознает Excel.

 

В ячейке появляется:

#### – число не вмещается в ячейку, чтобы убрать это сообщение, нужно увеличить ширину столбца;

#ДЕЛ/0! – попытка деления на ноль. Возможно, делитель ссылается на пустую ячейку;

#ИМЯ? – Excel не понимает, что за имя или адрес вы использовали в формуле, появляется, когда адрес задан русскими буквами или неверно набрано имя функции;

#ЗНАЧ! – в формуле имеется ссылка на текстовое значение, которое Excel не может преобразовать в числовое;

#ССЫЛКА! – ячейка, к которой обращается формула, была удалена (содержимое неверно);

#ЧИСЛО! – неверно значение аргумента (например, извлекается корень из отрицательного числа);

#Н/Д – значение недопустимо (значение недоступно функции или формуле);

#ПУСТО! – диапазоны не имеют общих ячеек.

 

Табличные формулы.

 

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

 

Ввод табличной формулы.

 

Рассмотрим пример.

 

	А	В	С	D
	Год	Приход	Расход	Прибыль
				{=B2:B5-C2:C5}

 

 

Выделим блок D2:D5. В этом блоке активной ячейкой является D2. Наберем знак равенства =. Выделим блок В2:В5, наберем «-», выделим блок С2:С5, нажмем сочетание клавиш Ctrl+Shift+Enter. В ячейках D2:D5 появятся значения, а в строке формул отразится формула единая для всех ячеек: {=B2:B5-C2:C5}.

 

Изменение табличной формулы.

 

При увеличении блока. Добавим дополнительную строку 1996 I 240 I 200

Просто скопировать формулу, как при работе с ячейками не получится. Нужно проделать следующее:

Выделим D2:D6, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В2:В5 и выделим блок B2:B6, также поступим с блоком C2:C5, либо просто заменим в адресах блоков цифру 5 на цифру 6. Нажимаем сочетание клавиш Ctrl+Shift+Enter.

При уменьшении блока. Удалить из таблицы строку 1996. Выделяем блок с табличной формулой, нажимаем F2 и добавляем в самое начало формулы апостроф (расположен на клавише с буквой Э). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Ctrl+Shift+Enter.

 

Понятие функции.

 

Функция – это переменная величина, значение которой зависит от значения других величин (аргументов).

Функция имеет имя, например СУММ и, как правило, аргументы, которые записываются в круглых скобках, следом за именем функции. Скобки – обязательная принадлежность функции, даже если у нее нет аргументов.

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

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

Например, если в ячейке G6 записана формула с функцией возведения в степень = СТЕПЕНЬ (А4;2), значением этой ячейки будет значение А4, возведенное в степень 2.

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

Для упрощения ввода функций в Excel предусмотрен специальный Мастер Функций, который можно вызвать нажатием кнопки f_x на панели инструментов «Стандартная». Предварительно необходимо выделить ячейку, куда будете вводить формулу.

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

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

 

Например: Найти максимальное и минимальное значение числового ряда.

 

	А

 

Выделяем ячейку А6

Мастер функций–Статистические–МАКС

Окно для аргумента Число1 Вводим блок А1:А5–Ок

В ячейке А6 появляется значение, а в строке формул: =МАКС(А1:А5)

 

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

 

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

Пример.

Обработать результаты экзамена и рассчитать начисление стипендии.

 

	А	В	С	D	E	F	G
	Ф.И.О.	Оценки за экзамен		Начисл. стипендии
		Информ.	Экон.	История	Математ.	Балл
	1.Иванов					=СУММ18(В3;F3)
	2.Зверев
	3.Калачев
	4.Синицына
	5.Писарев

 

Рассчитать стипендию, используя логическую функцию ЕСЛИ, и исходя из условия, что если суммарный балл 16, то стипендия будет 200 руб., иначе – 400 руб.

Сначала находим суммарный балл. Делаем активной ячейку F3. Воспользуемся Мастером функций. Выбираем Математические–Сумм. Аргумент-блок В3:Е3-Ок. Копируем полученную формулу с помощью маркера заполнения в оставшиеся ячейки. Получим результат. Для оценки среднего балла воспользуемся логической функцией ЕСЛИ. Мастер функций–Логические–ЕСЛИ.

Формат функции ЕСЛИ.

ЕСЛИ (<логическое выражение>, <выражение1>, <выражение2>).

Логическое выражение – правило для вычисления (логического) значения.

Если логическое выражение истинно, т.е. условие выполняется, тогда выполняется <выражение1>, иначе (когда условие не выполняется) выполняется <выражение2>.

=ЕСЛИ(F3 16; 400; 200)

Функция ЕСЛИ может быть вложенной, если суммарный балл 16, то стипендия будет 400 руб., если =16, то 300 руб., иначе 200 руб.

Если выполняется условие F3>16, то выражение примет значение 400. Если не выполняется, то будет проверяться следующее условное выражение.

Число вложенных функций ЕСЛИ не должно превышать 7.

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

Функция «и» принимает значение «истина», если одновременно истинны все логические выражения, указанные в качестве аргументов этой функции, иначе значение функции «и» – ложь.

Формат функции «и» =и(<логич.выражение1>, <логич.выражение2>, ….)

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

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

Формат функции «или» =или(<логич.выражение1>, <логич.выражение2>, …)

Пример1. Определить, существует ли треугольник со сторонами a,b,c. Условия существования треугольника: сумма двух любых сторон больше третьей.

=ЕСЛИ (и(а + в>с; а + с>b; в + с>a); существует, не существует)

Пример2. Назначить стипендию, если нет троек за сессию.

=ЕСЛИ(ИЛИ(В3 3; С3 3; D3 3; Е3 3); нет стипендии, стипендия)

Пример3. Построить график функции на интервале [-2;2], шаг 0,2

 

Поместим х в ячейку А3

B3=ЕСЛИ (А3 0; 5*А3+3; 3*А3/(5*А3+6))

 

Пример 4. Построить график функции на интервале [-2;2], шаг 0,2

 

 

Числовую прямую можно разделить на 3 отрезка:

 

 

С3 = ЕСЛИ (И(А3>= -1; A3<= 1); А3 + 2; А3 – 2)

 

Пример 5. Торговый агент получает процент от суммы совершенной сделки. Если объем сделки до 3000, то 5%; если объем до 10000, то 2%; если выше 10000, то 1,5%. Вычислить размер вознаграждения.

В3 =ЕСЛИ(A3<3000;5%*A3;ЕСЛИ(A3<10000;2%*A3;1,5%*A3))

 

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

 

Сумма – СУММ (интервал) – вычисляет сумму значений в заданном интервале СУММ(А1:А5).

СУММЕСЛИ – суммирует ячейки, заданные критерием

Синтаксис.

СУММЕСЛИ(диапазон; критерий; диапазон-суммирование:

- Диапазон – диапазон вычисляемых ячеек.

- Критерий – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, «32», «<32», яблоки.

Диапазон–суммирование - фактические ячейки для суммирования.

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

Если «диапазон-суммирование» опущен, то суммируются ячейки в аргументе «диапазон».

Пример.

	А	В
	Стоимость имущества	Комиссионные
	100 000
	200 000
	300 000
	400 000

 

Найти сумму комиссионных для стоимости имущества более 160000.

=СУММЕСЛИ (А2:А5; «>160000»; В2:В5)

Ответ: 63000

КОРЕНЬ (число) – возвращает значение квадратного корня.

СТЕПЕНЬ (число; степень) – возвращает результат возведения в степень. Показатель степени может быть дробным, т.е. корнем любой степени.

ПИ() – возвращает округленное до 15 знаков после запятой число Пи.

 

Статистические функции.

 

СРЗНАЧ (диапазон) – возвращает среднее значение чисел, содержащихся в ячейках заданного диапазона.

А

1 15

2 13

3 18

4 16

5 = СРЗНАЧ(А1:А4)

 

СЧЕТЕСЛИ – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию

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

Диапазон – диапазон, в котором нужно подсчитать ячейки.

Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

«>50» – выражение

«яблоко» – слово

Пример: Подсчитать количество «5»

 

А

1 4

2 5

3 5

4 4

5 3

6 = СЧЕТЕСЛИ (А1; А5; 5)

 

Ответ: 2

РАНГ – возвращает ранг числа в списке чисел. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией).

РАНГ (число; ссылка; порядок)

Число – число, для которого определяется ранг.

Ссылка – массив или ссылка на список чисел. Нечисловые значения игнорируются.

Порядок – число, определяющее способ упорядочения.

- если порядок =0 или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания.

- если порядок – любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Замечания. РАНГ присваивает повторяющимся числам одинаковый ранг. При этом наличие повторяющихся чисел влияет на ранг последующих чисел.

Например, если в списке целых чисел дважды встречается число 10, имеющие ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранг 6).

Пример:

 

А

1 Данные

2 7

3 3,5

4 3,5

5 1

6 2

 

Формула:

=РАНГ(А3; А2; А6; 1). Ранг числа 3,5 в приведенном списке (3)

=Ранг (А2; А2; А6; 1). Ранг числа 7 в приведенном списке (5)

ЧАСТОТА – вычисляет распределение значений по интервалам и возвращает вертикальный массив, содержащий на 1 элемент больше, чем массив интервалов.

ЧАСТОТА – вычисляет частоту появления значений в интервале значений и возвращает массив цифр.

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

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

ЧАСТОТА (массив_данных; массив_интервалов)

Массив данных – массив или ссылка на множество данных, для которых вычисляются частоты. Если массив данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.

Массив_интервалов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массива_данных. Если массив_интервалов не содержим значений, функция ЧАСТОТА возвращает количество элементов в аргументное частота.

Замечания.

- ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения.

- количество элементов в возвращаемом массиве на 1 больше числа элементов в массиве массив_интервалов.

Дополнительный элемент в возвращаемом массиве содержит количество значений, больших чем максимальное значение в интервалах.

Например, при подсчете 3-х диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка возвращает число значений в массив_данных, больших чем значение границы 3-го интервала.

- ЧАСТОА игнорирует пустые ячейки и тексты.

- формулы, которые возвращают массивы, должны быть введены как формулы массива.

 

ПРИМЕР.

 

	А	В
	Баллы	Интервалы

 

Формула

= ЧАСТОТА (А2:А10; В2:В5)

Описание (результат)

- число баллов в интервале в номером 70 и меньше (1)

- число баллов в интервале 71-79 (2)

- число баллов в интервале 80-89 (4)

- число баллов в интервалах с номером 90 и больше (2)

 

Примечание. Формулу в этом примере необходимо ввести как формулу массива. Выделите диапазон, начиная с ячейки, содержащей формулу, где будет подсчитана частота (число новое + 1). Нажмите клавишу F2, а затем нажмите клавиши Ctrl+Shift+Enter. Если формула не будет введена, как формула массива, единственное значение будет равно 1.