Вычисление в электронных таблицах Microsoft Excell 7(10)

Вычисления в электронных  таблицах

Вычисление в электронных  таблицах Microsoft Excell 7(10)

Вычисления  в Excel

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

Ввод формул

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

1. Откройте файл Электронная таблица.xls. В этой рабочей книге есть лист Формулы. Раскройте его. Первые семь строк листа должны выглядеть так, как показано па рис. 10.1, остальные ячейки будут пусты. Если это не соответствует действительности, отредактируйте лист.
2. Щелкните на ячейке В10 и введите формулу =(С3-В3)/В3*100.

Знак равенства  указывает программе Excel, что ячейка содержит формулу. С3 и В3 – это ссылки на содержимое соответствующих ячеек. Если предположить, что таблица содержит объем продаж продукции различным клиентам, то (С3-В3) – это прирост продаж для фирмы Фантом с января по февраль. Разделив полученную величину на В3 (продажи за январь), получаем относительный прирост продаж за месяц. Результат умножается на 100, что дает прирост в процентах. Как видите, в ячейки можно вводить простые, арифметические выражения со знаками +, –, *, / и скобками, определяющими последовательность выполнения операций.

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

 

1. Щелкните на ячейке В10 и перетащите угловой маркер выделения вправо, чтобы оказались охваченными ячейки с В10 по F10.

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

На первый взгляд кажется, что в ячейки C10:F10 должна дублироваться  формула ячейки В 10, но это не так. Excel поддерживает механизм относительных ссылок на ячейки. Когда формула переносится на одну клетку вправо, точно так же смещаются все ссылки этой формулы (это верно и для смещения формулы в любых других направлениях на любое число клеток). То есть в ячейке С11 появится формула =(D3-C3)/C3*100, в ячейке D10 – формула =(E3-D3)/D3*100 и т. д. Чтобы проверить это, щелкните на каждой ячейке и изучите содержимое строки формул. В итоге ячейки строки 10 будут содержать относительный прирост продаж компании Фантом по месяцам.

 

2. Выделите строки с третьей по седьмую.
3. Щелчком на кнопке Вырезать (Cut) панели инструментов Стандартная (Standard) вырежьте их содержимое.
4. Щелкните на ячейке А2 правой кнопкой мыши и выберите в контекстном меню команду Вставить. Данные таблицы переместятся вверх на одну строку. Теперь снова изучите формулы ячеек строки 10.

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

Примечание 
Если необходимо, чтобы знак равенства в начале текста ячейки не воспринимался как индикатор формулы, а интерпретировался как символ, поставьте перед ним одинарную кавычку (').

Функции

Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы. С тремя из них – СУММ (SUM), СРЗНАЧ (AVERAGE) и СЧЕТ (COUNT) – вы познакомились на предыдущем занятии. Чтобы научиться работать с другими функциями, выполните следующие шаги.

1. Щелкните на ячейке Н8 и введите текст Максимум. Нажмите клавишу Tab.
2. Щелкните на кнопке Вставка функции (Paste Function) панели инструментов Стандартная. Откроется окно диалога, показанное на рис. 10.2. Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции (Search for a function) или же выбрать ее категорию в списке категорий (Select a Category) этого окна диалога, а затем пролистать список Выберите функцию (Select a Function Name). Выделив имя одной из функций, вы увидите ее описание в нижней части окна диалога.

 

Excel делит все функции на следующие категории:

• Финансовые (Financial) – функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей;
• Дата и время (Date & Time) – операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в табл. 10.1;
• Математические (Math & Trig) – математические и тригонометрические функции, некоторые из них приведены в табл. 10.2;
• Статистические (Statistical) – функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных характеристик. Некоторые статистические функции перечислены в табл. 10.3;
• Ссылки и массивы (Lookup & Reference) – операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel;
• Работа с базой данных (Database) – функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных;
• Текстовые (Text) – функции для работы с текстовыми строками. Часть этих функций перечислена в табл. 10.4.
• Логические (Logical) – шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими значение Истина или Ложь. Эти функции перечислены в табл. 10.5;
• Проверка свойств и значений (Information) – функции проверки типа данных аргумента, режима форматирования ячейки, типа сгенерированной ошибки и других специальных условий.

 

 

 

 

Таблица 10.1. Функции обработки даты и времени

Функция	Описание
ДАТА (DATE)	Возвращает дату в формате  Excel
ДАТАЗНАЧ (DATEVALUE)	Преобразует дату из текстового формата в код Excel
ДЕНЬ (DAY)	День месяца заданной даты
ДНЕЙ360 (DAYS360)	Вычисляет количество дней между двумя датами на основе З60-дневного года
ЧАС (HOUR)	Час времени, заданного аргументом
МИНУТЫ (MINUTE)	Минута времени, заданного  аргументом
МЕСЯЦ (MONTH)	Номер месяца заданной даты
J-ДАТА (NOW)	Текущая дата и время в  числовом формате
СЕКУНДЫ (SECOND)	Секунда времени, заданного  аргументом
ВРЕМЯ (TIME)	Преобразует время дня  в дробное число от 0 до 1
ВРЕМЗНАЧ (TIMEVALUE)	Преобразует время из текстового формата в дату в числовом формате
ДЕНЬ (TODAY)	Текущая дата в числовом формате
ДЕНЬНЕД (WEEKDAY)	Номер дня недели заданной даты
ГОД(YEAR)	Год заданной даты

 

 

Таблица 10.2. Математические и тригонометрические функции

Функция	Описание
COS, SIN, TAN,	Тригонометрические функции
ACOS, ASIN, ATAN, ATAN2	Обратные тригонометрические функции
COSH, SINH, TANH	Гиперболические функции
ACOSH, ASINH, ATANH	Обратные гиперболические  функции
LN, LOG, LOG10	Натуральный логарифм, логарифмы  по основанию 2 и 10
EXP	Экспонента
НЕЧЕТ (EVEN), OKPУГЛ (ROUND), ОКРУГЛВВЕРХ (ROUNDUP), ОКРУГЛВНИЗ (ROUNDDOWN), ЧETH (ODD)	Функции округления
ABS	Модуль (абсолютное значение) числа
ГРАДУСЫ (DEGREES),	Преобразование радиан в  градусы
РАДИАНЫ (RADIANS)	Преобразование градусов в радианы
ЦЕЛОЕ (INT)	Целая часть числа
ОСТАТ (MOD)	Остаток от деления
ПИ (Р1)	Число пи
СТЕПЕНЬ (POWER)	Возведение в степень
ПРОИЗВЕЛ (PRODUCT)	Произведение ряда чисел
СЛЧИС (RAND)	Возвращает случайное  число
РИМСКОЕ (ROMAN)	Преобразование арабского  числа в римское
КОРЕНЬ (SQRT)	Квадратный корень
СУММ (SUM)	Сумма ряда чисел
СУММ КВ (SUMSQ)	Сумма квадратов ряда чисел
ОТБР (TRUNC)	Отбрасывает дробную часть

 

Таблица 10.3. Статистические функции

Функция	Описание
СРОТКЛ (AVEDEV)	Среднее абсолютное значение отклонения от среднего
СРЗНАЧ (AVERAGE), СРЗНАЧА (AVERAGEA)	Среднее арифметическое аргументов
СЧЕТ (COUNT), СЧЕТЗ (COUNTA), СЧИТАТЬ  ПУСТОТЫ (COUNT-BLANK), СЧЕТЕСЛИ (COUNTIF)	Количество чисел в  списке аргументов
КБЛДРОТКЛ (DEVSQ.)	Сумма квадратов отклонений от среднего но выборке
CРГEOM (GEOMEAN)	Среднее геометрическое набора положительных чисел
МАКС (МАХ), МАКСА (МАХА)	Максимальное значение списка аргументов
МИН (M1N), МИНА (MINA)	Минимальное значение списка аргументов
ДИСП (STDEV), ДИСПА (STDEVA),	Функции расчета дисперсии
ДИСПР (STDEVP), ДИСПРА (STDEVPA) СТАНДОТКЛОН (VAR), СТАНДОТКЛОНА (VARA), СТАНДОТКЛОНП (VARP), СТАНДОТКЛОНПА (VARPA)	Стандартное отклонение по выборке

 

Таблица 10.4. Текстовые функции

Функция	Описание
СИМВОЛ (CHAR)	Возвращает символ с заданным кодом
ПЕЧСЙМВ (CLEAN)	Удаляет из строки все непечатаемые символы
КОДСИМВ (CODE)	Код первого символа строки
СЦЕПИТЬ (CONCATENATE)	Объединяет две текстовые  строки
СОВПАД (EXACT)	Проверяет идентичность двух строк
ЛЕВСИМВ (LEFT)	Возвращает несколько  левых символов строки
ДЛСТР (LEN)	Количество символов в  строке
СТРОЧН (LOWER)	Делает все буквы текста строчными
ПРОПНАЧ (PROPER)	Делает первую букву прописной, а остальные – строчными
ПОВТОР(REPT)	Повторяет текст заданное число раз
ПРАВСИМВ (RIGHT)	Возвращает несколько  Правых символов и роки
Т, ТЕКСТ(TEXT), ФИКСИРОВАННЫЙ (FIXED)	Преобразует число в текст
СЖПРОБЕЛЫ (TRIM)	Удаляет лишние (двойные) пробелы
ПРОПИСН (UPPER)	Делает все буквы прописными
ЗНАЧЕН (VALUE)	Преобразует текстовый аргумент в число

 

Таблица 10.5. Логические функции

Функция	Описание
И (AND)	Логическое умножение
ЛОЖЬ (FALSE)	Возвращает ложное значение
ЕСЛИ (IF)	Возвращает одно значение, если условие истинно, и другое, если условие ложно
НЕ (NOT)	Логическое отрицание
ИЛИ (OR)	Логическое сложение
ИСТИНА (TRUE)	Возвращает истинное значение

 

В списке Категория (Function Category) есть также пункты Рекомендуемый перечень (Recommended), Полный алфавитный перечень (All) и 10 недавно использовавшихся (Most Recently Used), которые выводят соответственно список всех функций и 10 функций, применявшихся последними.

1. Выберите в списке Категория окна диалога вставки функций (см. рис. 10.2) пункт Статистические.
2. Выберите в списке Функция (Function Name) пункт МАКС.
3. Щелкните на кнопке ОК. Откроется окно ввода диапазона ячеек, показанное на рис. 10.3.

1. Рис. 10.3. Окно ввода аргументов

Некоторые функции, например SIN, имеют только один аргумент, поскольку нельзя вычислить синус  сразу двух чисел. Однако многие функции, подобные МАКС, способны обрабатывать практически неограниченные массивы  данных. Такие функции могут воспринимать до 30 аргументов, каждый из которых  является числом или ссылкой на одну или несколько ячеек.

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

С помощью функции  МАКС вы сейчас найдете максимальный уровень продаж за один месяц для  компаний Фантом, РИФ и Викинг.

Рис. 10.4. Выбор ячеек для первого аргумента

1. Щелкните на кнопке в правой части поля Число 1 (Number 1). Окно диалога свернется в строку, открывая доступ к ячейкам листа.
2. Выделите все числовые ячейки строки Фантом. Обозначение соответствующего диапазона ячеек появится в строке свернутого окна диалога ввода аргументов (рис. 10.4).
3. Щелкните на кнопке строки аргумента. На экране снова развернется окно ввода аргументов.
4. Щелкните на кнопке в правой части поля Число 2 (Number 2).
5. Выделите ячейки B4:G4 и снова щелкните на кнопке строки ввода аргумента. Поскольку вы ввели уже два аргумента, Excel автоматически добавит поле ввода третьего.
6. Введите в поле Чиcло 3 (Number 3), как показано на рис. 10.5, текст B6:G6. Это диапазон нужных ячеек строки Викинг. В окне диалога правее полей с аргументами демонстрируются их реальные значения. Ниже списка чисел отображается результат вычислений.