Формулы. Копирование формул

Поволжский Государственный Университет Телекоммуникаций и Информатики

Кафедра «Информатика и вычислительная техника»

Лабораторная работа №2

«Microsoft Excel»

Выполнили студентки 1 курса группы ИСТ-22

Коробок Татьяна Олеговна

Быканова Алена Ивановна

Самара, 2012

Формулы и функции.

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

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

Вывод формул.

1. ОткрылиЭлектронная таблица.xls > Формулы.

2. Щелкнули на ячейке В10 и ввели формулу =(СЗ-ВЗ)/ВЗ*100.

Формулы. Копирование формул - student2.ru

Знак равенства указывает программе Excel, что ячейка содержит формулу. СЗ и ВЗ — это ссылки на содержимое соответствующих ячеек. Также, в ячейки можно вводить простые, арифметические выражения со знаками +, -, *, / и скобками, определяющими последовательность выполнения операций.

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

Примечание

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

4. Выделили строки с 3 по 7.

5. Щелчком на кнопке Вырезать панели инструментов Стандартная вырезали их содержимое.

6. Щелкнули на ячейке А2 правой кнопкой мыши и выбрали в контекстном меню команду Вставить. Данные таблицы переместятся вверх на одну строку.

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

Примечание

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

Функции.

1. Щелкнули на ячейке Н8 и ввели текст Максимум. Нажали клавишу Tab.

2. Щелкнули на кнопке Вставка функции панели инструментов Стандартная. Открылось окно диалога, показанное на рис. 10.2. Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции или же выбрать ее категорию в списке категорию: этого окна диалога, а затем пролистать список Выберите функцию. Excel делит все функции на следующие категории:

o Финансовые — функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей;

o Дата и время — операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в табл.1

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

Таблица 1.

· Математические — математические и тригонометрические функции, некоторые из них приведены в табл.2;

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

Функция	Описание
COS, SIN, TAN,	Тригонометрические функции
ACOS, ASIN, ATAN, ATAN2	Обратные тригонометрические функции
COSH, SINH, TANH	Гиперболические функции
ACOSH, ASINH, ATANH	Обратные гиперболические функции
LN, LOG, LOG10	Натуральный логарифм, логарифмы по основанию 2 и 10
EXP	Экспонента
НЕЧЕТ (EVEN), OKPyiTI(ROUND), ОКРУГЛВВЕРХ (ROUNDUP), ОКРУГЛВНИЗ (ROUNDDOWN), 4ETH(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.)	Сумма квадратов отклонении от среднего но выборке
CFFEOM (GEOMEAN)	Среднее геометрическое набора положительных чисел
МАКС (МАХ), МАКСА (МАХА)	Максимальное значение списка аргументов
МИН (M1N), МИНА (MINA)	Минимальное значение списка аргументов
ДИСП (STDEV), ДИСПА (STDEVA),	Функции расчета дисперсии
ДИСПР (STDEVP), ДИСПРА (STDEVPA) СТАНДОТ-КЛОН (VAR), СТАНДОТКЛОНА (VARA), СТАНДОТКЛОНП (VARP), СТАНДОТКЛОНПА (VARPA)	Стандартное отклонение по выборке

o Статистические — функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных характеристик. Некоторые статистические функции перечислены в табл. 10.3;

o Ссылки и массивы — операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel;

o Работа с базой данных — функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных;

o Текстовые — функции для работы с текстовыми строками. Часть этих функций перечислена в табл.-10.4

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

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

o Логические — шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими значение Истина или Ложь. Эти функции перечислены в табл. 10.5;

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

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

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

3. Выбрали в списке Категория окна диалога вставки функций (см. рис. 10.2) пункт Статистические.

4. Выбрали в списке Функция пункт МАКС.

5. Щелкнули на кнопке ОК. Открылось окно ввода диапазона ячеек

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

Примечание

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

6. Щелкнули на кнопке в правой части поля Число 1.

7. Выделили все числовые ячейки строки Фантом.

8. Щелкнули на кнопке строки аргумента.

9. Щелкнули на кнопке в правой части поля Число 2.

10. Выделили ячейки B4:G4 и снова щелкнули на кнопке строки ввода аргумента.

11. Ввели в поле Число 3, текст B6:G6. Это диапазон нужных ячеек строки Викинг.

12. Щелкнули на кнопке ОК.

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

Диапазон ячеек.

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

1. Щелкнули на ячейке Н9 и введите текст Сумма. Затем нажали клавишу Tab.

2. Ввели символы =СУММ () (=SUM ()). Эта формула вычисляет сумму всех величин, указанных в скобках в качестве аргумента.

3. Щелкнули в строке формул и поместили курсор между двумя скобками.

4. Ввели в скобки текст В: В.

5. Щелкнули на кнопке Ввод слева от строки формул.

6. Снова щелкнули в строке формул.

7. Протаскиванием мыши выделили ячейки B3:G3.

8. Нажали клавишу Ctrl и протащили указатель мыши по ячейкам B5:G5.

Таблица 10.6.Способы ссылки на группы ячеек

Обозначение	Группа ячеек
F3	Ячейка на пересечении столбца F и строки-3
Е10:Е20	Ячейки с 10-й по 20-ю в столбце Е
В15:Е15	Ячейки с В по Е в строке 15
5:5	Все ячейки строки 5
5:10	Все ячейки строк с 5-й по 10-ю
В:В	Все ячейки столбца В
B:J	Все ячейки столбцов c B no J
А10:Е20	Прямоугольная область пересечения строк с 10-й по 20-ю и столбцов с А по Е

10. Ввели в поле Имя слово Строки, как показано на рис. 10.8.

11. Нажали клавишу Enter.

12. Щелкнули на ячейке 19, ввели формулу =СУММ(Строки) (=SUМ(Строки)) и нажали клавишу Enter. В ячейке 19 появится сумма двенадцати ячеек строк З и 5.

13. Щелкнули на стрелке раскрывающегося списка Имя.

14. Выбрали пункт Строки. Окажутся выделенными ячейки диапазона Строки.

Формулы. Копирование формул.

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

1. Щелкнули на любой непустой ячейке в строке 10.

2. Выделили диапазон B10:F10.

3. Нажатием клавиш Ctrl+C скопировали ячейки.

4. Щелкнули на ячейке В11, а затем на кнопке Вставить панели инструментов Стандартная.

5. Щелкнули на ячейке В 12 и нажали клавиши Ctrl+V, чтобы вставить еще одну строку.

Примечание

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

6. Вставили тот же самый фрагмент в ячейки В13 и В14.

7. Скопировали заголовки строк из ячеек А2:А6 в ячейки А10:А14.

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

8. Выделили группу ячеек 12:16.

9. Щелкнули на кнопке Автосумма панели инструментов Стандартная.

10. Ввели в ячейку II заголовок Итого

Поиск ошибок.

5. Щелкнули на ячейке Ш и ввели формулу СРЗНАЧ B11:F11 (AVERAGE B11:F11).

6. Нажали клавишу Enter. В ячейке появится сообщение #ИМЯ, которое \ указывает на ошибку в имени функции. Слева от него находится маленький значок помощника, содержащий раскрывающийся список, в котором приводятся возможные способы устранения ошибки. . В табл. 10.7 приведен список сообщений об ошибках, которые можно встретить в ячейках при работе с формулами.

7. Чтобы исправить формулу, двойным щелчком на ячейке 111 перешли в режим редактирования формулы.

Формулы. Копирование формул - student2.ru

Таблица 10.7.Сообщения об ошибках в формулах

Сообщение	Описание ошибки
#####	Ширина ячейки недостаточна для отображения результата вычисления или отрицательный результат вычислении в ячейке, отформатированной как данные типа даты или времени
#ЗНАЧ (#VALUE!)	Неверный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число
#ДЕЛ/0(#01У/0!)	Деление на 0
#ИМЯ (#NAME!)	Excel не может распознать текст, введенный в формулу, например неверное имя функции
#Н/Д (#N/A)	Данные ячейки одного из аргументов формулы в данный момент недоступны
*#ССЫЛКА (#REF!)	Неверная ссылка на ячейку
#ЧИСЛО(#МиМ!)	Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейке
#ПУСТО (#NULL!)	Результат поиска пересечения двух непересекающихся областей, то есть неверная ссылка

5. Изменили содержимое ячейки так, чтобы получилась формула СРЗНАЧ (В 11:F11) (AVERAGE(B11:F11)).

Применение функций.

1 В листе Формулы выделили и скопировали ячейки Bl:Gl.

2 Развернули лист Лист З.

3 Щелкнули правой кнопкой мыши на ячейке А1 и выбрали в контекстном меню команду Вставить.

4 Ввели в ячейку A3 формулу =ЛЕВСИМВ(А1 ;3) (=LEFT(A1; 3)). Эта формула возвращает три левых символа ячейки А1.

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

6 Щелкнули на ячейке НЗ и ввели формулу =СЦЕПИТЬ(ВЗ ;D3) (CONCATENATE (ВЗ ; D3)). В ячейке НЗ появилось объединение строк Фев и Апр.

7 В ячейку А8 ввели формулу =ТДАТА () (=NOW ()) и нажали клавишу Enter. В ней тут же появятся текущие дата и время.

8 Ввели в ячейки с В8 по В13 формулы = ГОД (А8) (=YEAR(A8)), =МЕСЯЦ(А8) (=MONTH (А8)),=ДЕНЬ(А8)(=DAY(А8)),=ЧАС(А8)(=HOUR(A8)),=МИНУТЫ(А8) (=MINUTE(A8)) и =СЕКУНДЫ(А8) (=SECOND (A8)). В этих ячейках появились по отдельности все шесть компонентов текущих даты и времени.

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

10 Щелкнули на кнопке Ввод, расположенной слева от строки формул.

11 Ввели в ячейку А4 формулу =ЕСЛИ(АЗ="Фев"; "Да"; "Нет") (=IF(АЗ="Фев"; " Да "; " Н е т ")).

12 Растянули маркер ячейки А4 вправо, чтобы рамка охватила диапазон A4:F4

Знаки, которые используются в Excel для сравнения величин, перечислены в табл. 10.8.

Примечание

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

Таблица 10.8.Знаки сравнения